PO - Script to get PO Requisition and Purchase Order Details

--------------MSR &FRQ & PO----------------------

SELECT PRL.REQUISITION_HEADER_ID Header_id,

       prl.REQUISITION_LINE_ID,

       PRL.ITEM_ID MSR_ITEM_ID,

       ABCD_LOGISTICS_PKG.GET_ITEM_CODE (prl.ITEM_ID) REQ_ITEM_CODE,

       PRL.ITEM_DESCRIPTION MSR_ITEM_DESC,

       PRL.QUANTITY,

       PRL.LINE_NUM,

       PRL.UNIT_MEAS_LOOKUP_CODE MSR_UOM,

       PRL.UNIT_PRICE,

       XX_ABCD_HCM_PKG.GET_EMP_NAME (PRL.TO_PERSON_ID, SYSDATE) REQUESTOR,

       PRH.SEGMENT1 REQ_NO,

       pRH.AUTHORIZATION_STATUS REQ_STATUS,

       ABCD_PROJECT_PKG.GET_TASK_NAME (prd.TASK_ID) msr_TASK,

       ABCD_PROJECT_PKG.GET_PROJECT_NAME (prd.project_id) msr_proj,

       pha.SEGMENT1 PO_NUMBER,

       RFQ.SEGMENT1 RFQ_NUM,

       QUOT.SEGMENT1 quot,

       pha.PO_HEADER_ID,

       pha.AUTHORIZATION_STATUS PO_STATUS,

       TO_CHAR (pha.APPROVED_DATE, 'DD/MM/YYYY') PO_APPROVED_DATE,

       PLA.UNIT_MEAS_LOOKUP_CODE PO_UOM,

       pla.PO_LINE_ID,

       pla.LINE_NUM LINE_NUMBER,

       pla.ITEM_ID PO_ITEM_ID,

       ABCD_LOGISTICS_PKG.GET_ITEM_CODE (pla.ITEM_ID) PO_ITEM_CODE,

       Pla.ITEM_DESCRIPTION PO_ITEM_DESC,

       pda.PROJECT_ID po_PROJECT_ID,

       ABCD_PROJECT_PKG.GET_PROJECT_NAME (pda.PROJECT_ID) PO_PROJECT,

       --  ABCD_PROJECT_PKG.GET_PROJECT_NAME_BYPO (pha.PO_HEADER_ID) PO_PROJECT2,

       ABCD_PROJECT_PKG.GET_TASK_NAME (pda.TASK_ID) PO_TASK,

       plla.QUANTITY_RECEIVED,

       plla.QUANTITY_BILLED,

       CASE

          WHEN pha.APPROVED_DATE IS NOT NULL

               AND pha.AUTHORIZATION_STATUS = 'INPROCESS'

          THEN

             (SELECT xx_values_tmp.XX_GET_EMP_AR_NAME (EMPLOYEE_ID)

                FROM PO_ACTION_HISTORY pah

               WHERE OBJECT_TYPE_CODE = 'PO' AND OBJECT_ID = pha.PO_HEADER_ID

                     AND SEQUENCE_NUM IN

                            (SELECT MAX (SEQUENCE_NUM)

                               FROM PO_ACTION_HISTORY hist

                              WHERE OBJECT_TYPE_CODE = 'PO'

                                    AND hist.OBJECT_ID = pah.OBJECT_ID))

          WHEN pha.AUTHORIZATION_STATUS IN ('REJECTED', 'INCOMPLETE')

          THEN

             xx_values_tmp.XX_GET_EMP_AR_NAME (pha.AGENT_ID)        --AGENT_ID

          ELSE

             NULL

       END

          PO_approval_Pending,

       CASE

          WHEN pha.APPROVED_DATE IS NULL

               AND pha.AUTHORIZATION_STATUS <> 'APPROVED'

          THEN

             TRUNC (SYSDATE - prh.APPROVED_DATE)

       END

          PO_Approve_Time_Lag,

       'MSR & RFQ & PO' remarks

  FROM PO_REQUISITION_HEADERS_ALL PRH,

       PO_REQUISITION_LINES_ALL PRL,

       po_req_distributions_all PRD,

       po_headers_all pha,

       PO_LINES_ALL PLA,

       PO_LINE_LOCATIONS_ALL plla,

       po_distributions_all pda,

       PO_HEADERS_ALL RFQ,

       PO_LINES_ALL RFQL,

       PO_HEADERS_ALL QUOT

 WHERE     prh.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID

       AND prd.requisition_line_id = prl.requisition_line_id

       AND pha.po_header_id = pda.po_header_id

       AND pha.PO_HEADER_ID = pla.PO_HEADER_ID

       AND pda.PO_LINE_ID = pla.PO_LINE_ID

       AND plla.PO_LINE_ID = pla.PO_LINE_ID

       AND plla.Line_Location_Id = pda.line_location_id

       AND pda.req_distribution_id = prd.distribution_id

       AND RFQ.PO_HEADER_ID = QUOT.FROM_HEADER_ID

       AND QUOT.FROM_HEADER_ID = RFQL.PO_HEADER_ID

       AND quot.type_lookup_code = 'QUOTATION'

       AND RFQL.PO_HEADER_ID = RFQ.PO_HEADER_ID

       AND PRl.ITEM_ID = RFQL.ITEM_ID

       AND PRl.ON_RFQ_FLAG = 'Y'

       --    AND prh.segment1 = :P_REQ_NO

       AND prh.creation_date BETWEEN :p_from_date AND :p_to_date

       AND (prl.ORG_ID = :P_ORG_id OR :P_ORG_id IS NULL)

--------------------------MSR & RFQ without PO----------------

UNION

SELECT PRL.REQUISITION_HEADER_ID Header_id,

       prl.REQUISITION_LINE_ID,

       PRL.ITEM_ID MSR_ITEM_ID,

       ABCD_LOGISTICS_PKG.GET_ITEM_CODE (prl.ITEM_ID) REQ_ITEM_CODE,

       PRL.ITEM_DESCRIPTION MSR_ITEM_DESC,

       PRL.QUANTITY,

       PRL.LINE_NUM,

       PRL.UNIT_MEAS_LOOKUP_CODE MSR_UOM,

       PRL.UNIT_PRICE,

       XX_ABCD_HCM_PKG.GET_EMP_NAME (PRL.TO_PERSON_ID, SYSDATE) REQUESTOR,

       PRH.SEGMENT1 REQ_NO,

       pRH.AUTHORIZATION_STATUS REQ_STATUS,

       ABCD_PROJECT_PKG.GET_TASK_NAME (prd.TASK_ID) msr_TASK,

       ABCD_PROJECT_PKG.GET_PROJECT_NAME (prd.project_id) msr_proj,

       '' PO_NUMBER,

       RFQ.SEGMENT1 RFQ_NUM,

       QUOT.SEGMENT1 quot,

       0 PO_HEADER_ID,

       '' PO_STATUS,

       '' PO_APPROVED_DATE,

       '' PO_UOM,

       0 PO_LINE_ID,

       0 LINE_NUMBER,

       0 PO_ITEM_ID,

       '' PO_ITEM_CODE,

       '' PO_ITEM_DESC,

       0 po_PROJECT_ID,

       '' PO_PROJECT,

       -- '' PO_PROJECT2,

       '' PO_TASK,

       0 QUANTITY_RECEIVED,

       0 QUANTITY_BILLED,

       '' PO_approval_Pending,

       0 PO_Approve_Time_Lag,

       'MSR & RFQ without PO' remarks

  FROM PO_REQUISITION_HEADERS_ALL PRH,

       PO_REQUISITION_LINES_ALL PRL,

       po_req_distributions_all PRD,

       PO_HEADERS_ALL RFQ,

       PO_LINES_ALL RFQL,

       PO_HEADERS_ALL QUOT

 WHERE     prh.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID

       AND prd.requisition_line_id = prl.requisition_line_id

       AND RFQ.PO_HEADER_ID = QUOT.FROM_HEADER_ID

       AND QUOT.FROM_HEADER_ID = RFQL.PO_HEADER_ID

       AND quot.type_lookup_code = 'QUOTATION'

       AND RFQL.PO_HEADER_ID = RFQ.PO_HEADER_ID

       AND PRl.ITEM_ID = RFQL.ITEM_ID

       --  AND prh.segment1 = :P_REQ_NO

       --   AND PRl.ON_RFQ_FLAG ='Y'

       AND (SELECT DISTINCT pda.req_distribution_id

              FROM po_distributions_all pda

             WHERE pda.req_distribution_id = prd.distribution_id)

              IS NULL

       AND prh.creation_date BETWEEN :p_from_date AND :p_to_date

       AND (prl.ORG_ID = :P_ORG_id OR :P_ORG_id IS NULL)

UNION                                  ----------------MSR Only---------------

SELECT PRL.REQUISITION_HEADER_ID Header_id,

       prl.REQUISITION_LINE_ID,

       PRL.ITEM_ID MSR_ITEM_ID,

       ABCD_LOGISTICS_PKG.GET_ITEM_CODE (prl.ITEM_ID) REQ_ITEM_CODE,

       PRL.ITEM_DESCRIPTION MSR_ITEM_DESC,

       PRL.QUANTITY,

       PRL.LINE_NUM,

       PRL.UNIT_MEAS_LOOKUP_CODE MSR_UOM,

       PRL.UNIT_PRICE,

       XX_ABCD_HCM_PKG.GET_EMP_NAME (PRL.TO_PERSON_ID, SYSDATE) REQUESTOR,

       PRH.SEGMENT1 REQ_NO,

       pRH.AUTHORIZATION_STATUS REQ_STATUS,

       ABCD_PROJECT_PKG.GET_TASK_NAME (prd.TASK_ID) msr_TASK,

       ABCD_PROJECT_PKG.GET_PROJECT_NAME (prd.project_id) msr_proj,

       '' PO_NUMBER,

       '' RFQ_NUM,

       '' quot,

       0 PO_HEADER_ID,

       '' PO_STATUS,

       '' PO_APPROVED_DATE,

       '' PO_UOM,

       0 PO_LINE_ID,

       0 LINE_NUMBER,

       0 PO_ITEM_ID,

       '' PO_ITEM_CODE,

       '' PO_ITEM_DESC,

       0 po_PROJECT_ID,

       '' PO_PROJECT,

       -- '' PO_PROJECT2,

       '' PO_TASK,

       0 QUANTITY_RECEIVED,

       0 QUANTITY_BILLED,

       '' PO_approval_Pending,

       0 PO_Approve_Time_Lag,

       'MSR Only' remarks

  FROM PO_REQUISITION_HEADERS_ALL PRH,

       PO_REQUISITION_LINES_ALL PRL,

       po_req_distributions_all PRD

 --   PO_HEADERS_ALL RFQ,

 --   PO_LINES_ALL RFQL,

 --   PO_HEADERS_ALL QUOT

 WHERE     prh.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID

       AND prd.requisition_line_id = prl.requisition_line_id

       --  AND prh.segment1 = :P_REQ_NO

       --   AND PRl.ON_RFQ_FLAG <>'Y'

       AND (SELECT DISTINCT pda.req_distribution_id

              FROM po_distributions_all pda

             WHERE pda.req_distribution_id = prd.distribution_id)

              IS NULL

       AND prh.creation_date BETWEEN :p_from_date AND :p_to_date

       AND (prl.ORG_ID = :P_ORG_id OR :P_ORG_id IS NULL)

UNION                 --------------MSR & PO without RFQ----------------------

SELECT PRL.REQUISITION_HEADER_ID Header_id,

       prl.REQUISITION_LINE_ID,

       PRL.ITEM_ID MSR_ITEM_ID,

       ABCD_LOGISTICS_PKG.GET_ITEM_CODE (prl.ITEM_ID) REQ_ITEM_CODE,

       PRL.ITEM_DESCRIPTION MSR_ITEM_DESC,

       PRL.QUANTITY,

       PRL.LINE_NUM,

       PRL.UNIT_MEAS_LOOKUP_CODE MSR_UOM,

       PRL.UNIT_PRICE,

       XX_ABCD_HCM_PKG.GET_EMP_NAME (PRL.TO_PERSON_ID, SYSDATE) REQUESTOR,

       PRH.SEGMENT1 REQ_NO,

       pRH.AUTHORIZATION_STATUS REQ_STATUS,

       ABCD_PROJECT_PKG.GET_TASK_NAME (prd.TASK_ID) msr_TASK,

       ABCD_PROJECT_PKG.GET_PROJECT_NAME (prd.project_id) msr_proj,

       pha.SEGMENT1 PO_NUMBER,

       '' RFQ_NUM,

       '' quot,

       pha.PO_HEADER_ID,

       pha.AUTHORIZATION_STATUS PO_STATUS,

       TO_CHAR (pha.APPROVED_DATE, 'DD/MM/YYYY') PO_APPROVED_DATE,

       PLA.UNIT_MEAS_LOOKUP_CODE PO_UOM,

       pla.PO_LINE_ID,

       pla.LINE_NUM LINE_NUMBER,

       pla.ITEM_ID PO_ITEM_ID,

       ABCD_LOGISTICS_PKG.GET_ITEM_CODE (pla.ITEM_ID) PO_ITEM_CODE,

       Pla.ITEM_DESCRIPTION PO_ITEM_DESC,

       pda.PROJECT_ID po_PROJECT_ID,

       ABCD_PROJECT_PKG.GET_PROJECT_NAME (pda.PROJECT_ID) PO_PROJECT,

       --   ABCD_PROJECT_PKG.GET_PROJECT_NAME_BYPO (pha.PO_HEADER_ID) PO_PROJECT2,

       ABCD_PROJECT_PKG.GET_TASK_NAME (pda.TASK_ID) PO_TASK,

       plla.QUANTITY_RECEIVED,

       plla.QUANTITY_BILLED,

       CASE

          WHEN pha.APPROVED_DATE IS NOT NULL

               AND pha.AUTHORIZATION_STATUS = 'INPROCESS'

          THEN

             (SELECT xx_values_tmp.XX_GET_EMP_AR_NAME (EMPLOYEE_ID)

                FROM PO_ACTION_HISTORY pah

               WHERE OBJECT_TYPE_CODE = 'PO' AND OBJECT_ID = pha.PO_HEADER_ID

                     AND SEQUENCE_NUM IN

                            (SELECT MAX (SEQUENCE_NUM)

                               FROM PO_ACTION_HISTORY hist

                              WHERE OBJECT_TYPE_CODE = 'PO'

                                    AND hist.OBJECT_ID = pah.OBJECT_ID))

          WHEN pha.AUTHORIZATION_STATUS IN ('REJECTED', 'INCOMPLETE')

          THEN

             xx_values_tmp.XX_GET_EMP_AR_NAME (pha.AGENT_ID)        --AGENT_ID

          ELSE

             NULL

       END

          PO_approval_Pending,

       CASE

          WHEN pha.APPROVED_DATE IS NULL

               AND pha.AUTHORIZATION_STATUS <> 'APPROVED'

          THEN

             (SYSDATE - prh.APPROVED_DATE)

       END

          PO_Approve_Time_Lag,

       'MSR & PO without RFQ' remarks

  FROM PO_REQUISITION_HEADERS_ALL PRH,

       PO_REQUISITION_LINES_ALL PRL,

       po_req_distributions_all PRD,

       po_headers_all pha,

       PO_LINES_ALL PLA,

       PO_LINE_LOCATIONS_ALL plla,

       po_distributions_all pda

 --    PO_HEADERS_ALL RFQ,

 --    PO_LINES_ALL RFQL,

 --    PO_HEADERS_ALL QUOT

 WHERE     prh.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID

       AND prd.requisition_line_id = prl.requisition_line_id

       AND pha.po_header_id = pda.po_header_id

       AND pha.PO_HEADER_ID = pla.PO_HEADER_ID

       AND pda.PO_LINE_ID = pla.PO_LINE_ID

       AND plla.PO_LINE_ID = pla.PO_LINE_ID

       AND plla.Line_Location_Id = pda.line_location_id

       AND pda.req_distribution_id = prd.distribution_id

       --              AND RFQ.PO_HEADER_ID = QUOT.FROM_HEADER_ID

       --              AND QUOT.FROM_HEADER_ID = RFQL.PO_HEADER_ID

       --              AND quot.type_lookup_code='QUOTATION'

       --              AND RFQL.PO_HEADER_ID = RFQ.PO_HEADER_ID

       --              AND PRl.ITEM_ID = RFQL.ITEM_ID

       AND PRl.ON_RFQ_FLAG <> 'Y'

       --    AND prh.segment1 = :P_REQ_NO

       AND prh.creation_date BETWEEN :p_from_date AND :p_to_date

       AND (prl.ORG_ID = :P_ORG_id OR :P_ORG_id IS NULL)

-----------------PO only -------------------------------

UNION

SELECT 0 Header_id,

       0 REQUISITION_LINE_ID,

       0 MSR_ITEM_ID,

       '' REQ_ITEM_CODE,

       '' MSR_ITEM_DESC,

       0 QUANTITY,

       0 LINE_NUM,

       '' MSR_UOM,

       0 UNIT_PRICE,

       '' REQUESTOR,

       '' REQ_NO,

       '' REQ_STATUS,

       '' msr_TASK,

       '' msr_proj,

       pha.SEGMENT1 PO_NUMBER,

       '' RFQ_NUM,

       '' quot,

       pha.PO_HEADER_ID,

       pha.AUTHORIZATION_STATUS PO_STATUS,

       TO_CHAR (pha.APPROVED_DATE, 'DD/MM/YYYY') PO_APPROVED_DATE,

       PLA.UNIT_MEAS_LOOKUP_CODE PO_UOM,

       pla.PO_LINE_ID,

       pla.LINE_NUM LINE_NUMBER,

       pla.ITEM_ID PO_ITEM_ID,

       ABCD_LOGISTICS_PKG.GET_ITEM_CODE (pla.ITEM_ID) PO_ITEM_CODE,

       Pla.ITEM_DESCRIPTION PO_ITEM_DESC,

       pda.PROJECT_ID po_PROJECT_ID,

       ABCD_PROJECT_PKG.GET_PROJECT_NAME (pda.PROJECT_ID) PO_PROJECT,

       ABCD_PROJECT_PKG.GET_TASK_NAME (pda.TASK_ID) PO_TASK,

       plla.QUANTITY_RECEIVED,

       plla.QUANTITY_BILLED,

       CASE

          WHEN pha.APPROVED_DATE IS NOT NULL

               AND pha.AUTHORIZATION_STATUS = 'INPROCESS'

          THEN

             (SELECT xx_values_tmp.XX_GET_EMP_AR_NAME (EMPLOYEE_ID)

                FROM PO_ACTION_HISTORY pah

               WHERE OBJECT_TYPE_CODE = 'PO' AND OBJECT_ID = pha.PO_HEADER_ID

                     AND SEQUENCE_NUM IN

                            (SELECT MAX (SEQUENCE_NUM)

                               FROM PO_ACTION_HISTORY hist

                              WHERE OBJECT_TYPE_CODE = 'PO'

                                    AND hist.OBJECT_ID = pah.OBJECT_ID))

          WHEN pha.AUTHORIZATION_STATUS IN ('REJECTED', 'INCOMPLETE')

          THEN

             xx_values_tmp.XX_GET_EMP_AR_NAME (pha.AGENT_ID)        --AGENT_ID

          ELSE

             NULL

       END

          PO_approval_Pending,

       CASE

          WHEN pha.APPROVED_DATE IS NULL

               AND pha.AUTHORIZATION_STATUS <> 'APPROVED'

          THEN

             TRUNC (SYSDATE - pha.Creation_DATE)

       END

          PO_Approve_Time_Lag,

       'PO only' remarks

  FROM po_headers_all pha,

       PO_LINES_ALL PLA,

       PO_LINE_LOCATIONS_ALL plla,

       po_distributions_all pda

 WHERE     pha.po_header_id = pda.po_header_id

       AND pha.PO_HEADER_ID = pla.PO_HEADER_ID

       AND pda.PO_LINE_ID = pla.PO_LINE_ID

       AND plla.PO_LINE_ID = pla.PO_LINE_ID

       AND plla.Line_Location_Id = pda.line_location_id

       AND (SELECT DISTINCT prd.distribution_id

              FROM po_req_distributions_all prd

             WHERE prd.distribution_id = pda.req_distribution_id)

              IS NULL

       AND pha.creation_date BETWEEN :p_from_date AND :p_to_date

       -- and  pha.SEGMENT1='15175/P1150/A/15'

       AND (pla.ORG_ID = :P_ORG_id OR :P_ORG_id IS NULL)