Thursday, April 18, 2019

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)
      

Best Blogger TipsGet Flower Effect