Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Uvaraja Sadhana
    BIP Report to get the Payment pending for ApprovalAnswered
    Topic posted April 15, 2019 by Uvaraja SadhanaRed Ribbon: 250+ Points, tagged BI Publisher, Fusion Financial reporting, Reports 
    85 Views, 2 Comments
    Title:
    BIP Report to get the Payment pending for Approval
    Summary:
    BIP Report to get the Payment pending for user Approval
    Content:

    Hi All,

    We have enabled a Workflow approval for Payments in ERP Cloud.

    We have a created a Specific user for final level approval for all payments we make. Now we need to build a BIP Report to fetch all the payments which are pending to approval by the Specific user.

    Is it possible to write query to get the above details from BPM  in BIP ?

     

    Regards,

    Uva

     

    Best Comment

    Madhu Babu Vitta

    Hi Uva,

    Try below logic

    1) Using BPM tables 

    Below we are using for PO Requisitions. Check for similar compositename for Payments
    CASE 
         WHEN pr.document_status = 'PENDING APPROVAL'  THEN
                (
                  SELECT wf.assigneesdisplayname 
                  FROM   FA_FUSION_SOAINFRA.WFTASKHISTORY wf
                  WHERE  wf.compositeinstanceid = pr.approval_instance_id 
                         AND wf.compositename = 'PrcPorManageReqApprovalComposite' 
                         AND wf.state = 'ASSIGNED' 
    AND wf.assignees IS NOT NULL
    AND wf.workflowpattern NOT IN ('AGGREGATION', 'FYI')
                  ORDER  BY 
             wf.assigneddate DESC
            ,wf.tasknumber DESC
            ,wf.createddate DESC
    ,wf.version DESC
                  FETCH FIRST 1 ROW ONLY
    )
    2) Also check below tables
    fnd_bpm_task_vl
    fnd_bpm_task_assignee
    FND_BPM_TASK_HISTORY_B
     
    Thanks,
    Madhu
     

    Comment

     

    • Madhu Babu Vitta

      Hi Uva,

      Try below logic

      1) Using BPM tables 

      Below we are using for PO Requisitions. Check for similar compositename for Payments
      CASE 
           WHEN pr.document_status = 'PENDING APPROVAL'  THEN
                  (
                    SELECT wf.assigneesdisplayname 
                    FROM   FA_FUSION_SOAINFRA.WFTASKHISTORY wf
                    WHERE  wf.compositeinstanceid = pr.approval_instance_id 
                           AND wf.compositename = 'PrcPorManageReqApprovalComposite' 
                           AND wf.state = 'ASSIGNED' 
      AND wf.assignees IS NOT NULL
      AND wf.workflowpattern NOT IN ('AGGREGATION', 'FYI')
                    ORDER  BY 
               wf.assigneddate DESC
              ,wf.tasknumber DESC
              ,wf.createddate DESC
      ,wf.version DESC
                    FETCH FIRST 1 ROW ONLY
      )
      2) Also check below tables
      fnd_bpm_task_vl
      fnd_bpm_task_assignee
      FND_BPM_TASK_HISTORY_B
       
      Thanks,
      Madhu
       
      • Uvaraja Sadhana

        Thanks Madu,

        How to identify whether workflow is completed or not, i.e all the approvers are approved or not.

        Regards,

        Uva