Prev
Next
Forums S3 Systems Administration

Auditing "Active" Requesters in RQ04

Sort:
You are not authorized to post a reply.
Author
Messages
TBonney
Veteran Member
Posts: 277
Veteran Member
4/12/2011 2:12 PM

    We've noticed on our Lawson maintenance contract that Procurement Self Service is billed based on number of users. I assume this correlates to the number of RQ04 records (requesters) we have set up.

    We're billed at 1000 "users", but only have about 600 requesters defined in RQ04. Of those, I imagine many have not created a requistion in a long time. Therefore, I'd like to audit this and delete any RQ04 records for those who haven't created a requisition in the last 12 months.

    Has anyone been able to generate a SQL statement (or any other methodology) to identify requesters like this? I have built a query, but have not been able to remove duplicate records from it in order to narrow down the results.

    Thank you in advance!

    Jimmy Chiu
    Veteran Member
    Posts: 641
    Veteran Member
    4/14/2011 1:00 PM
      Are you using RSS?
      TBonney
      Veteran Member
      Posts: 277
      Veteran Member
      4/14/2011 4:38 PM
        Jimmy,

        Yes we do. Most requesters create their requisitions from within RSS, but some of the more experienced users also also generate them directly, right in RQ10.
        Jimmy Chiu
        Veteran Member
        Posts: 641
        Veteran Member
        4/15/2011 4:31 PM
          Last time Lawson checked my RSS users count, they requested an dump of LDIF from my ldap server. They were counting users with PRODLINE_REQUESTER identity instead of RQ04 records. Just FYI.
          John Henley
          Senior Member
          Posts: 3348
          Senior Member
          4/15/2011 8:29 PM
            You could also query against the REQHEADER table for a date range, and group.sum by requester. Then inactivate any requester identities who are using the system.
            Thanks for using the LawsonGuru.com forums!
            John
            John Henley
            Senior Member
            Posts: 3348
            Senior Member
            4/19/2011 1:07 PM
              SELECT RQH.REQUESTER, COUNT(RQH.REQ_NUMBER) AS REQ_COUNT
              FROM lawson.REQHEADER RQH
              WHERE YEAR(RQH.CREATION_DATE) = 2010
              GROUP BY RQH.REQUESTER
              Thanks for using the LawsonGuru.com forums!
              John
              TBonney
              Veteran Member
              Posts: 277
              Veteran Member
              4/19/2011 3:21 PM
                Thank you John, I'll give this a try!
                You are not authorized to post a reply.