SELECT

TO_CHAR (PRLA.REQUISITION_LINE_ID) AS PURCH_REQ_KEY,

TO CHAR (PRLA.DELIVER TO LOCATION ID) AS DELIVER TO LOCATION KEY,
TO_CHAR (PRHA.APPROVED_DATE, 'MM-DD-YYYY HH24:MI:SS') AS APPROVED ON DATE,
POLLA.AMOUNT CANCELLED AS CANCELLED LINE AMT,
PRLA.QUANTITY_CANCELLED AS CANCELLED_LINE_QTY,
PHA.CURRENCY_CODE AS DOC_CURR_CODE,
TO_CHAR (DECODE (PHA.AGENT_ID, NULL, PRLA.ASSIGNED_BUYER_ID)) AS BUYER_KEY,
TO CHAR (PRLA.REQ BU ID) AS BU KEY,
TO_CHAR (PRLA.DESTINATION_ORGANIZATION_ID) AS ORG_ID,
TO_CHAR (PLA.CATEGORY_ID) AS CATEGORY_ID,
TO_CHAR (PRLA.PO_LINE_ID) AS PO_LINE_ID,
NVL (TO CHAR (PRLA.DELIVER_TO_CUST_ID), '0') AS DELIVER TO_CUST ID,
PRLA.DESTINATION_TYPE_CODE AS DESTINATION_TYPE_CODE,
PRLA.ITEM SOURCE AS ITEM SOURCE,
NVL (PRLA.SUGGESTED_SUPPLIER_ITEM_NUMBER, 0) AS SUGGESTED_SUPPLIER_ITEM_NUM,
NVL (PRLA.SUGGESTED_VENDOR_NAME, '') AS SUGGESTED_VENDOR_NAME,
PRLA.BUYER_PROCESSING_FLAG AS BUYER_PROCESSING_FLG,
TO_CHAR (PRLA.REQUESTED_SHIP_DATE, 'MM-DD-YYYY HH24:MI:SS') AS REQUESTED_SHIP_DATE,
TO_CHAR (PRLA.LAST_APPROVAL_DATE, 'MM-DD-YYYY HH24:MI:SS') AS LAST_APPROVAL_DATE,
TO CHAR (PRLA.CANCEL DATE, 'MM-DD-YYYY HH24:MI:SS') AS CANCEL DATE,
TO_CHAR (PRLA.LAST_SUBMITTED_DATE, 'MM-DD-YYYY HH24:MI:SS') AS LAST_SUBMITTED_DATE,
PRLA.ITEM_DESCRIPTION AS LINE_TEXT,
PRLA.RATE AS LOC_EXCHANGE_RATE,
TO CHAR (NVL (POLLA.PROMISED_DATE,
NVL (POLLA.NEED_BY_DATE,PRLA.NEED_BY_DATE)), 'MM-DD-YYYY HH24:MI:SS') AS
NEEDED_BY_DATE,
TO_CHAR (PHA.CREATION_DATE, 'MM-DD-YYYY HH24:MI:SS') AS ORDERED_ON_DATE,
TO_CHAR (DECODE (PHA.APPROVED_FLAG, 'Y', PHA.APPROVED_DATE,NULL), 'MM-DD-YYYY
HH24:MI:SS') AS PO_APPROVED_ON_DATE,
PHA.REVISION_NUM AS PO_REVISIONS,
TO_CHAR (PHA.SUBMIT_DATE, 'MM-DD-YYYY HH24:MI:SS') AS PO_SUBMIT_ON_DATE,
PRLA.UOM_CODE AS PRIMARY_UOM_CODE,
PRLA.LINE_NUMBER AS PURCH_LINE_NUM,
PRHA.REQUISITION_NUMBER AS PURCH_RQSTN_NUM,
TO CHAR (ADD_MONTHS (SYSDATE, -200), 'MM-DD-YYYY HH24:MI:SS') AS RECEIVED ON DATE,
PRLA.QUANTITY AS REQUESTED QTY,
PLA.UOM_CODE AS STANDARD_UOM_CODE,
NVL (PRLA.CURRENCY_UNIT_PRICE, PRLA.UNIT_PRICE) AS UNIT_PRICE,
PRLA.SECONDARY UOM CODE AS UOM CODE,
PRLA.URGENT_FLAG AS URGENT_FLG,
PRHA.DOCUMENT STATUS AS DOCUMENT STATUS,
TO_NUMBER (TO_CHAR (PRHA.APPROVED_DATE, 'YYYYMMDD')) AS APPROVED_ON_DT_KEY,
TO NUMBER (TO_CHAR (POLLA.PROMISED_DATE, 'YYYYMMDD')) AS DUE_ON_DT_KEY,
TO_CHAR (PHA.DEFAULT_PO_TRADE_ORG_ID) AS INVENTORY_ORG_KEY,
TO NUMBER (TO_CHAR (PRLA.NEED_BY_DATE, 'YYYYMMDD')) AS NEEDED_BY_DATE_KEY,
TO_CHAR (PRLA.DESTINATION_ORGANIZATION_ID) AS OPERATING_UNIT_ORG_KEY,
TO NUMBER (TO_CHAR (PRHA.CREATION_DATE, 'YYYYMMDD')) AS ORDERED ON DT KEY,
TO NUMBER (TO CHAR (DECODE (PHA.APPROVED FLAG, 'Y', PHA.APPROVED_DATE, NULL),
'YYYYMMDD ')) AS PO_APPROVED_ON_DT_KEY,
TO_CHAR (PHA.DOCUMENT_CREATION_METHOD) AS PO_CREATION_METHOD_KEY,
TO_NUMBER (TO CHAR (PHA.SUBMIT_DATE, 'YYYYMMDD')) AS PO_SUBMIT_ON_DT_KEY,
TO_CHAR (PRLA.ITEM_ID) AS ITEM_KEY,
TO NUMBER (NULL) AS RECEIVED_ON_DT_KEY,
TO_CHAR (PRLA.REQUESTER_ID) AS REQUESTOR_KEY,
TO_CHAR (PRLA.DESTINATION_ORGANIZATION_ID) AS RQSTN_ORG_KEY,
TO CHAR (PRLA.VENDOR SITE ID) AS SUPPLIER ACCOUNT KEY,
TO CHAR (NVL (POV.PARTY_ID, PRLA.VENDOR_ID)) AS SUPPLIER_KEY,
TO_CHAR (PRLA.ITEM_ID) AS SUPPLIER_PROD_KEY,
PURCHASE REQUISITION' || '~' || PRLA.ORDER_TYPE_LOOKUP_CODE AS REQ_TYPE_KEY,
TO_CHAR (PRHA.CREATION_DATE, 'MM-DD-YYYY HH24:MI:SS') AS REQ_LINE_CREATION_DATE,
PRLA.QUANTITY AS ORDERED QTY,
PDAR.QUANTITY_DELIVERED AS QUANTITY_DELIVERED,
(PRLA.UNIT_PRICE * PRLA.QUANTITY) AS AMOUNT_ORDERED,
(PRLA.UNIT_PRICE * PDAR.QUANTITY_DELIVERED) AS AMOUNT_DELIVERED,
PDA.DISTRIBUTION_AMOUNT AS DISTRIBUTION_AMT,
NVL (PDA.DISTRIBUTION_QUANTITY, 0) AS DISTRIBUTION_QTY,
PDA.NONRECOVERABLE_TAX AS NONRECOVERABLE_TAX,
PDA.RECOVERABLE_TAX AS RECOVERABLE_TAX,
TO CHAR (PHA.SEGMENT1) AS PO NUM,
TO_CHAR (PLA.LINE_NUM) AS PO_LINE_NUM,
TO_CHAR (PRLA.LAST_UPDATE_DATE, 'MM-DD-YYYY HH24:MI:SS') AS
REQ_LINE_LAST_UPDATE_DATE,
TO_CHAR (PRHA.LAST_UPDATE_DATE, 'MM-DD-YYYY HH24:MI:SS') AS REQ HEADER LAST UPDATE
DATE,
TO_CHAR (POLLA.LAST_UPDATE_DATE, 'MM-DD-YYYY HH24:MI:SS') AS
PO_LINE_LOC_LAST_UPDATE_DATE,
TO_CHAR (PHA.LAST_UPDATE_DATE, 'MM-DD-YYYY HH24:MI:SS') AS
PO_HEADER_LAST_UPDATE_DATE,
TO CHAR (PLA.LAST_UPDATE_DATE, 'MM-DD-YYYY HH24:MI:SS') AS
PO_LINES_LAST_UPDATE_DATE,
TO_CHAR (PDAR.LAST_UPDATE_DATE, 'MM-DD-YYYY HH24:MI:SS') AS
PO_DISTR_LAST_UPDATE_DATE,
FND.USERNAME AS LINE_LAST_UPDATED_BY,
FND_C.USERNAME AS LINE_CREATED_BY,
TO CHAR (PHA.CREATION_DATE, 'MM-DD-YYYY HH24:MI:SS') AS PO_HEADER_CREATION_DATE,
TO_CHAR (PLA.CREATION_DATE, 'MM-DD-YYYY HH24:MI:SS') AS PO_LINE_CREATION_DATE,
TO CHAR (PRLA.CREATION DATE, 'MM-DD-YYYY HH24:MI:SS') AS PO REQ 1 CREATION DATE,
TO CHAR (PRHA.CREATION DATE, 'MM-DD-YYYY HH24:MI:SS') AS PO REQ H CREATION DATE,
TO CHAR (POLLA.CREATION DATE, 'MM-DD-YYYY HH24:MI:SS') AS
PO_LINE_LOC_CREATION_DATE,
TO CHAR (PDAR.CREATION DATE, 'MM-DD-YYYY HH24:MI:SS') AS PO_DISTR_CREATION_DATE,
TO_CHAR (PRHA.SUBMISSION_DATE, 'MM-DD-YYYY HH24:MI:SS') AS HEADER SUBMISSION DATE,
'PO_LINE_TYPE' || '-' || PRLA.LINE_TYPE_ID AS LINE_TYPE_KEY,
CASE
WHEN (PRHA.DOCUMENT_STATUS IS NULL OR PRHA.DOCUMENT_STATUS = '')
THEN 'PO DOC_STATUS INCOMPLETE'
ELSE PO DOC STATUS || '~' || PRHA.DOCUMENT STATUS
END AS APPROVAL_STATUS_KEY,
PRHA.REQUISITION_HEADER_ID||'~'||PRLA.REQUISITION_LINE_ID || '~'|| PDA.DISTRIBUTION
ID AS INTEGRATION_ID

FROM
POR_REQUISITION_HEADERS_ALL PRHA,
POR_REQUISITION_LINES_ALL PRLA,
PO_LINE_LOCATIONS_ALL POLLA,
PO_HEADERS_ALL PHA, PO_LINES_ALL PLA,
PO_DISTRIBUTIONS_ALL PDAR,
GL_CODE_COMBINATIONS GLCC,
POR_REQ_DISTRIBUTIONS_ALL PDA,
PER USERS FND,
PER_USERS FND_C,
POZ_SUPPLIERS POV

WHERE
PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID(+)
AND PRLA.REQUISITION_LINE_ID(+) PDA.REQUISITION_LINE_ID
AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID(+)
AND TO CHAR (FND.USER_ID(+)) = PRLA.LAST_UPDATED_BY
AND PDAR.PO_HEADER_ID = PHA.PO_HEADER_ID(+)
AND PDAR.PO_LINE_ID = PLA.PO_LINE_ID(+)
AND PDAR.LINE_LOCATION_ID = POLLA.LINE_LOCATION_ID (+)
AND POLLA.PO_LINE_ID (+) PLA.PO_LINE_ID
AND POLLA.PO HEADER ID (+) = PHA.PO HEADER ID
AND PDA.CODE COMBINATION ID GLCC.CODE COMBINATION ID
AND PDAR.REQ_DISTRIBUTION_ID(+) = PDA.DISTRIBUTION_ID
AND TO CHAR (FND_C.USER_ID(+)) = PRLA.CREATED_BY
AND POV.VENDOR_ID (+) PRLA.VENDOR_ID
ORDER BY PDAR.PO_DISTRIBUTION_ID