Pending Approval For Purchase order and requisitions Sql In fusion

Fusion SCM

Please Refer Below SQL

Both Requisition and Purchasing..............

/* Formatted on 24-09-2018 18:07:28  By Vinayak Walse  */

SELECT 'Requisition' TYPE,

       prha.DOCUMENT_STATUS Approval_Status,

       TO_CHAR (prha.creation_date, 'DD-MM-YYYY') po_date,

       prha.REQUISITION_NUMBER Requisition_Number,

       REPLACE (REPLACE (prha.DESCRIPTION, CHR (13), ''), CHR (10), '')

          PO_Desc,

       (SELECT SUM ( (NVL (UNIT_PRICE, AMOUNT) * NVL (QUANTITY, 1)))

          FROM por_requisition_lines_all

         WHERE requisition_header_id = prha.requisition_header_id)

          PO_Amount,

       (SELECT PRLA.CURRENCY_CODE

          FROM por_requisition_lines_all prla

         WHERE prla.requisition_header_id = prha.requisition_header_id

               AND ROWNUM = 1)

          CURRENCY,

       prha.requisition_header_id,

       NULL Supplier_Name,

       wf.ASSIGNEESDISPLAYNAME pending_with_whom,

       TO_CHAR (wf.assigneddate, 'DD-MM-YYYY') submit_for_appr_date,

       EMAIL.*

  FROM po_action_history pah,

       por_requisition_headers_all prha,

       FA_fusion_soainfra.wftask wf,

       -- FA_FUSION_SOAINFRA.WFASSIGNEE_VIEW WASSS_V,

       hr_operating_units d,

       (SELECT panf.full_name, fu.username, PEA.EMAIL_ADDRESS

          FROM PER_USERS fu,

               per_all_people_f papf,

               PER_PERSON_NAMES_F panf,

               PER_EMAIL_ADDRESSES PEA

         WHERE fu.person_id = papf.person_id

               AND SYSDATE BETWEEN papf.effective_start_date

                               AND papf.effective_end_date

               AND panf.person_id = papf.person_id

               AND SYSDATE BETWEEN panf.effective_start_date

                               AND panf.effective_end_date

               AND panf.NAME_TYPE = 'GLOBAL'

               AND PEA.person_id = papf.person_id

               AND SYSDATE BETWEEN NVL (PEA.DATE_FROM, SYSDATE - 1)

                               AND NVL (PEA.DATE_TO, SYSDATE + 1)) EMAIL

 WHERE     pah.object_id = prha.requisition_header_id

       AND prha.REQ_BU_ID = d.organization_id

       AND pah.object_type_code = 'REQ'

       AND prha.DOCUMENT_STATUS IN ('PENDING APPROVAL')

       AND wf.COMPOSITEINSTANCEID = prha.APPROVAL_INSTANCE_ID

       AND wf.ASSIGNEES IS NOT NULL

       AND wf.TASKNUMBER =

              (SELECT MAX (TASKNUMBER)

                 FROM FA_fusion_soainfra.wftask wf1

                WHERE wf.CORRELATIONID = wf1.CORRELATIONID -- and wf1.state = 'ASSIGNED'

                      AND wf1.COMPOSITEINSTANCEID = prha.approval_instance_id)

       -- AND WASSS_V.taskid(+) = wf.taskid

       AND EMAIL.username(+) =

              upper(SUBSTR (wf.ASSIGNEES, 1, INSTR (wf.ASSIGNEES, ',') - 1)) ----WASSS_V.ASSIGNEE

       AND pah.sequence_num =

              (SELECT MAX (pah1.sequence_num)

                 FROM po_action_history pah1

                WHERE     pah1.object_id = pah.object_id           -----rEQ---

                      AND pah1.object_type_code = 'REQ'

                      AND prha.DOCUMENT_STATUS IN ('PENDING APPROVAL') --  AND pah1.action_code = nvl(:p_status,pah1.action_code)

                                                                      )

       ---Parameters

       AND prha.requisition_header_id =

              NVL (:POR_Number, prha.requisition_header_id)

       AND DECODE (:p_bu, NULL, 1, d.organization_id) = NVL (:p_bu, 1)

-- AND NVL (:PO_Number,1) = 1

UNION ALL

SELECT 'Purchase Order',

       pv.change_order_status Approval_Status,

       TO_CHAR (ph.creation_date, 'DD-MM-YYYY') po_date,

       --------------

       ph.segment1 PO_Number,

       REPLACE (REPLACE (PH.COMMENTS, CHR (13), ''), CHR (10), '') PO_Desc,

       (SELECT SUM (

                  NVL (pl1.UNIT_PRICE, pl1.AMOUNT)

                  * DECODE (PL1.PURCHASE_BASIS, 'SERVICES', 1, pl1.QUANTITY))

          FROM po_lines_all pl1

         WHERE pl1.po_header_id = ph.po_header_id)

          PO_Amount,

       PH.CURRENCY_CODE CURRENCY,

       PH.PO_HEADER_ID,

       pv.vendor_name Supplier_Name,

       wf.ASSIGNEESDISPLAYNAME pending_with_whom,

       TO_CHAR (wf.assigneddate, 'DD-MM-YYYY') submit_for_appr_date,

       EMAIL.*

  FROM po_action_history poh,

       FA_fusion_soainfra.wftask wf,

       --  FA_FUSION_SOAINFRA.WFASSIGNEE_VIEW WASSS_V,

       po_headers_all ph,

       po_versions pv,

       poz_suppliers_v pv,

       hr_operating_units d,

       (SELECT panf.full_name, fu.username, PEA.EMAIL_ADDRESS

          FROM PER_USERS fu,

               per_all_people_f papf,

               PER_PERSON_NAMES_F panf,

               PER_EMAIL_ADDRESSES PEA

         WHERE fu.person_id = papf.person_id

               AND SYSDATE BETWEEN papf.effective_start_date

                               AND papf.effective_end_date

               AND panf.person_id = papf.person_id

               AND SYSDATE BETWEEN panf.effective_start_date

                               AND panf.effective_end_date

               AND panf.NAME_TYPE = 'GLOBAL'

               AND PEA.person_id = papf.person_id

               AND SYSDATE BETWEEN NVL (PEA.DATE_FROM, SYSDATE - 1)

                               AND NVL (PEA.DATE_TO, SYSDATE + 1)) EMAIL

 WHERE     poh.CORRELATION_ID = wf.CORRELATIONID

       AND poh.object_id = ph.po_header_id

       AND ph.po_header_id = pv.po_header_id

       AND wf.COMPOSITEINSTANCEID = pv.approval_instance_id

       --AND wf.state = 'ASSIGNED'

       AND assignees IS NOT NULL

       -- AND ph.segment1 = 'ISG0000048'

       AND poh.object_type_code = 'PO'

       --  AND poh.ACTION_CODE = 'SUBMIT'

      -- AND ph.DOCUMENT_STATUS IN ('PENDING APPROVAL')

       -- ('WITHDRAWN', 'REJECTED', 'PENDING APPROVAL')

   and pv.change_order_status = 'PENDING APPROVAL'

       AND ph.vendor_id = pv.vendor_id

       AND ph.prc_bu_id = d.organization_id

       AND wf.TASKNUMBER =

              (SELECT MAX (TASKNUMBER)

                 FROM FA_fusion_soainfra.wftask wf1

                WHERE wf.CORRELATIONID = wf1.CORRELATIONID -- 

and wf1.state = 'ASSIGNED'

                      AND wf1.COMPOSITEINSTANCEID = pv.approval_instance_id)

       -- AND WASSS_V.taskid(+) = wf.taskid

       AND EMAIL.username(+) =

              upper(SUBSTR (wf.ASSIGNEES, 1, INSTR (wf.ASSIGNEES, ',') - 1))

       AND poh.sequence_num =

              (SELECT MAX (pah1.sequence_num)

                 FROM po_action_history pah1

                WHERE pah1.object_id = poh.object_id         -----Po_header_id

                      AND pah1.object_type_code = 'PO' --  AND pah1.action_code = nvl(:p_status,pah1.action_code)

                                                      )

       ---Parameters

       AND ph.po_header_id = NVL (:PO_Number, ph.po_header_id)

       AND DECODE (:p_bu, NULL, 1, ph.prc_bu_id) = NVL (:p_bu, 1)

-- AND NVL (:POR_Number,1) =1

------Short Sql for Purchase

select poh.segment1, approval_instance_id, assignees, wf.outcome, wf.state ,

wf.workflowpattern, wf.title, wf.componentname, wf.compositename

from

po_versions pov, 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= 'ISAL0000022' --- skip this if you need all pending PO's

and state='ASSIGNED'

and assignees is not null

and workflowpattern not in ('AGGREGATION', 'FYI')

and componentname='DocumentApproval'

How to handle NVL and IF Condition @RTF Level

NVL function In RTF level

Please use Below code for same in RTF its very Useful to use Conditions in RTF level itself.



<?xdofx: if SERIAL_NUMBER_CONTROL_CODE='1' and XX_LOT_CONTROL_CODE='1'   then 'NA' else if SERIAL_NUMBER_CONTROL_CODE!=1 and XX_SERIAL_EXIST='Y' then  nvl(SERIAL_NUMBER,'_____')else if XX_LOT_EXIST= 'Y' then nvl(LOT_NUMBER,'_________') else '_________'  end if?>

if required more details of XDOFX click_here

Its same like case function in SQL ....
Thank you.