1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Iprocurement report

Discussion in 'Oracle Financials' started by clency.ngary, Jul 18, 2013.

  1. clency.ngary

    clency.ngary Active Member

    Messages:
    9
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Cape Town
    HI guys

    we would like to run a report that list all the requisition not yet approved with detail on the supplier, cost centre, with whom the requisition(approver) is at that stage, etc.

    Can you possibly assist in that sense?

    Regards

    Clency Ngary
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Clency,

    Do you got anything on this ?
     
  3. clency.ngary

    clency.ngary Active Member

    Messages:
    9
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Cape Town
    Hi,

    we still haven't got a a solution we were thinking a developer might be able to help and you.

    Regards


    Clency
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Clency,

    Please let us know with what all the information you want in a report? By that we can develop a query for you.
     
  5. clency.ngary

    clency.ngary Active Member

    Messages:
    9
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Cape Town
    HI Bharat,

    what we need is a report that list all the requisition not yet approved with detail on the supplier, cost centre, which approver it lies is at that stage... we want to be able to remind each approver at month end to complete their approval. Basically what we really need its requisition details and which approver its lying with....

    Regards

    Clency N.
     
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Clency,

    In general, Approvers may be many for either requisitions or Purchase Orders or any. And if approver1 didn't approved means which approver you want to display in the report. All the approvers or the current approver who needs to approve ?
     
  7. clency.ngary

    clency.ngary Active Member

    Messages:
    9
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Cape Town
    Hi Bharat

    In general we will only need the current approver not the whole approval chain

    REgards

    Clency
     
    Bharat likes this.
  8. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Clency,

    Please go through the following query which is developed based on the requirement. Please pass the requisition number as parameter to the following query.

    Code (Text):

    SELECT
          PRHA.SEGMENT1                                         "Requisition No#"
         ,TO_CHAR(PRLA.NEED_BY_DATE,'DD-Mon-YYYY')              "Need by Date"

         ,ASP.VENDOR_NAME                                       "Supplier Name"
         ,ASSA.VENDOR_SITE_CODE                                 "Supplier Site"
         ,RTRIM(ASSA.ADDRESS_LINE1,',')||' , '||ASSA.COUNTRY    "Supplier Address"
         ,RTRIM(ASSA.AREA_CODE||' - '||ASSA.PHONE,' - ')        "Supplier Contact"
         ,MSIB.SEGMENT1                                         "Item"
         ,PRLA.ITEM_DESCRIPTION                                 "Item Description"
         ,PRLA.QUANTITY                                         "Quantity"
         ,PRLA.UNIT_MEAS_LOOKUP_CODE                            "UOM"
         ,PRLA.UNIT_PRICE                                       "Unit Price"
         ,PAPF.FULL_NAME                                        "Approver to Approve"

    FROM PO_REQUISITION_HEADERS_ALL PRHA
        ,PO_REQUISITION_LINES_ALL PRLA
        ,PO_APPROVAL_LIST_HEADERS PALH
        ,PO_APPROVAL_LIST_LINES PALL
        ,AP_SUPPLIERS ASP
        ,AP_SUPPLIER_SITES_ALL ASSA
        ,MTL_SYSTEM_ITEMS_B MSIB
        ,PER_ALL_PEOPLE_F PAPF

    WHERE PRHA.SEGMENT1 in( '&requisition_number')
      AND PRHA.AUTHORIZATION_STATUS = 'IN PROCESS'
      AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID
      AND PALH.DOCUMENT_ID = PRHA.REQUISITION_HEADER_ID
      AND PALL.APPROVAL_LIST_LINE_ID IN
                            (SELECT MIN(APPROVAL_LIST_LINE_ID) FROM PO_APPROVAL_LIST_LINES WHERE APPROVAL_LIST_HEADER_ID = PALH.APPROVAL_LIST_HEADER_ID
                                AND STATUS IS NULL)
      AND ASP.VENDOR_ID = PRLA.VENDOR_ID
      AND ASSA.VENDOR_SITE_ID = PRLA.VENDOR_SITE_ID
      AND MSIB.INVENTORY_ITEM_ID (+) = PRLA.ITEM_ID
      AND MSIB.ORGANIZATION_ID (+) = PRLA.ORG_ID
      AND PAPF.PERSON_ID = PALL.APPROVER_ID
      AND PAPF.CURRENT_EMP_OR_APL_FLAG = 'Y'
      AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE);

     
    Please let us know if any additional information needed.
     
  9. clency.ngary

    clency.ngary Active Member

    Messages:
    9
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Cape Town
    Hi Bharat,

    this script is one step in advance, because its at the PO tables level, but we want one at workflow tables level. Also, this seem to be required a requisition to be able to run it? but we would like to see all in process requisition and their approvers

    Regards

    Clency
     
  10. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Clency,

    We can make it to run for all requisitions by removing that requisition number parameter line. By that we can get all requisitions which are not approved. I think PO_APPROVAL_LIST_HEADERS and PO_APPROVAL_LIST_LINES tables contains workflow key as well. So we can use these tables to get approver.
     
  11. clency.ngary

    clency.ngary Active Member

    Messages:
    9
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Cape Town
    Hi Bharat,

    either way that script does not return any value to me, whether i remove the requisition number parameter, is there something else missing?

    Regards

    Clency
     
  12. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    No Clency, we dont need to do any modifications to the query except removing that requisition number field parameter one. Anyhow please refer to this query which I have removed that. Let us know if any needed.

    Code (Text):

    SELECT
          PRHA.SEGMENT1                                         "Requisition No#"
         ,TO_CHAR(PRLA.NEED_BY_DATE,'DD-Mon-YYYY')              "Need by Date"

         ,ASP.VENDOR_NAME                                       "Supplier Name"
         ,ASSA.VENDOR_SITE_CODE                                 "Supplier Site"
         ,RTRIM(ASSA.ADDRESS_LINE1,',')||' , '||ASSA.COUNTRY    "Supplier Address"
         ,RTRIM(ASSA.AREA_CODE||' - '||ASSA.PHONE,' - ')        "Supplier Contact"
         ,MSIB.SEGMENT1                                         "Item"
         ,PRLA.ITEM_DESCRIPTION                                 "Item Description"
         ,PRLA.QUANTITY                                         "Quantity"
         ,PRLA.UNIT_MEAS_LOOKUP_CODE                            "UOM"
         ,PRLA.UNIT_PRICE                                       "Unit Price"
         ,PAPF.FULL_NAME                                        "Approver to Approve"

    FROM PO_REQUISITION_HEADERS_ALL PRHA
        ,PO_REQUISITION_LINES_ALL PRLA
        ,PO_APPROVAL_LIST_HEADERS PALH
        ,PO_APPROVAL_LIST_LINES PALL
        ,AP_SUPPLIERS ASP
        ,AP_SUPPLIER_SITES_ALL ASSA
        ,MTL_SYSTEM_ITEMS_B MSIB
        ,PER_ALL_PEOPLE_F PAPF

    WHERE 1 = 1
      AND PRHA.AUTHORIZATION_STATUS = 'IN PROCESS'
      AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID
      AND PALH.DOCUMENT_ID = PRHA.REQUISITION_HEADER_ID
      AND PALH.LATEST_REVISION = 'Y'
      AND PALL.APPROVAL_LIST_LINE_ID IN
                            (SELECT MIN(APPROVAL_LIST_LINE_ID) FROM PO_APPROVAL_LIST_LINES WHERE APPROVAL_LIST_HEADER_ID = PALH.APPROVAL_LIST_HEADER_ID
                                AND STATUS IS NULL)
      AND ASP.VENDOR_ID = PRLA.VENDOR_ID
      AND ASSA.VENDOR_SITE_ID = PRLA.VENDOR_SITE_ID
      AND MSIB.INVENTORY_ITEM_ID (+) = PRLA.ITEM_ID
      AND MSIB.ORGANIZATION_ID (+) = PRLA.ORG_ID
      AND PAPF.PERSON_ID = PALL.APPROVER_ID
      AND PAPF.CURRENT_EMP_OR_APL_FLAG = 'Y'
      AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE);