SELECT

TO_CHAR (PRHA.APPROVED_DATE, 'MM-DD-YYYY HH24:MI:SS') AS

APPROVED_ON_DATE,
PRLA.QUANTITY_CANCELLED AS CANCELLED_LINE_QTY,
TO_CHAR (PRLA.REQ_BU_ID) AS BU_KEY,
TO_CHAR (PRLA.DESTINATION_ORGANIZATION_ID) AS ORG_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.LINE_STATUS AS LINE_STATUS,
(CASE
WHEN PRLA.MATCHING_BASIS = 'AMOUNT' THEM PRLA.AMOUNT
ELSE PRLA.QUANTITY * PRLA.UNIT_PRICE
END ) AS LINE_AMOUNT,
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.CANCLE_DATE,'MM-DD-YYYY HH24:MM:SS') AS CANCLE_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,
PRLA.UOM_CODE AS PRIMARY_UOM_CODE,
PRLA.LINE_NUMBER AS PURCH_LINE_NUM,
PRLA.REQUISITION_NUMBER AS PURCH_RQSTN_NUM,
PRLA.QUANTITY AS REQUESTED_QTY,
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.CREATION_DATE, 'YYYYMMDD')) AS
APPROVED_ON_DT_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_DATE_KEY,
TO_CHAR (PRLA.ITEM_ID) AS ITEM KEY
TO_NUMBER (NULL) AS RECIEVED_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_QUANTITY,
(PRLA.UNIT_PRICE * PRLA.QUANTITY) AS AMOUNT_ORDERED,
PDA.DISTRIBUTION_AMOUNT AS DISTRIBUTION_AMOUNT,
NVL (PDA.DISTRIBUTION_QUANTITY, 0) AS DISTRIBUTION_QTY,
PDA.NONRECOVERABLE_TAX AS NONRECOVERABLE_TAX,
PDA.RECOVERABLE_TAX AS RECOVERABLE_TAX,
 FND.USERNAME AS LINE_LAST_UPDATED_BY,
FND_C.USERNAME AS LINE_CREATED_BY,
TO_CHAR (PRHA.SUBMISSION_DATE, 'MM-DD-YYYY HH24:MI:SS') AS
HEADER_SUBMISSION_DATE,

FROM
POR_REQUISITION_HEADERS_ALL PRHA,
POR_REQUISITION_LINES_ALL PRLA,
PO_LINE_LOCATIONS_ALL POLLA,
GL_CODE_COMBINATIONS GLCC,
POR_REQ_DISTRIBUTIONS_ALL PDA,
PER_USERS FND,
PER_USERS FND_C,
POZ_SUPPLIERS POV

WHERE 1=1
AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID(+)
AND PRLA.REQUISITION_LINE_ID(+) = PDA.REQUISITION_LINE_ID
AND PDA.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
AND TO_CHAR (FND.USER_ID(+)) = PRLA.LAST_UPDATED_BY
AND TO_CHAR (FND_C.USER_ID(+)) = PRLA.CREAYED_BY
AND POV.VENDOR_ID(+) = PRLA.VENDOR_ID
AND PRHA.REQUISITION_NUMBER = '204142'