Purchasing

Get Involved. Join the Conversation.

Topic

    Deepak Padhy
    Query to See the Approver Name on Whom a PO is Pending For...
    Topic posted August 24, 2018 by Deepak PadhySilver Medal: 2,000+ Points, tagged Approvals, BI Publisher Reports, Business Intelligence, How-To, Notifications 
    2535 Views, 4 Comments
    Title:
    Query to See the Approver Name on Whom a PO is Pending For Approval
    Summary:
    Query to See the Approver Name on Whom a PO is Pending For Approval
    Content:

    Most of the time we get an SR with requirement to see or extract on which approval level a PO is pending currently for approval. And customer wants a Query for this. Below is the Query we have prepared which will give on whom the PO is currently pending for approval. You can prepare an OTBI report to get the extract of all open POs and you can modify the query as per what column you need.

     

    SELECT poh.segment1
    ,approval_instance_id, assignees, wf.outcome, wf.state ,wf.workflowpattern, wf.title, wf.componentname, wf.compositename, pov.change_order_status
    FROM
    fusion.po_versions pov, fusion.po_headers_all poh  , fa_fusion_soainfra.wftask wf
    WHERE pov.po_header_id = poh.po_header_id
    and pov.change_order_status = 'PENDING APPROVAL'
    and pov.approval_instance_id=wf.compositeinstanceid
    and poh.segment1=  '1004752' -- PO Number
    and state='ASSIGNED'
    and assignees is not null
    and workflowpattern not in ('AGGREGATION', 'FYI')
    and componentname='DocumentApproval'

     

    Thanks

    Deepak.

    Comment

     

    • Ramesh Radhakrishnan

      Thanks Deepak. This is very helpfull for OTBI reporting!!

      - Ramesh

      • Bindu Duppalapudi

        Hi Ramesh,

        Can you please tell how did you fetch the field - Approver field on whom the PO is pending in OTBI report.

        Thanks,

        Bindu

    • Sunny Todkari

      Thanks for sharing this.

      Also, search 'Purchasing Documents Pending Approval' post on cloud connect posted by Ashok Srinivasaraghavan.
      It also has really useful information.

    • Manavalan Ethirajan

      Hi,

      I believe that this information should be possible to get out of the box as well. In OTBI, Procurement Purchasing real time subject-area, under document history folder, you can find 'Performed By' (Approver Name), 'Action Performed' (Action Status) and 'Action Date' (Date Approved).

      You can use Action Performed with 'Pending For Approval' as a filter and check the report.

      You can also refer this post for screenshots https://cloudcustomerconnect.oracle.com/posts/090fdd6465

      Thanks,

      Manav