PO details query in oracle apps R12
SELECT HOU.SHORT_CODE OU,
PHA.CREATION_DATE PO_CREATION_DATE,
PHA.APPROVED_DATE PO_APPROVED_DATE,
NVL(PRH.INTERFACE_SOURCE_CODE, PRH.APPS_SOURCE_CODE)
REQUISITION_SOURCE,
PHA.TYPE_LOOKUP_CODE PO_TYPE,
APS.VENDOR_NAME SUPPLIER_NAME,
APS.SEGMENT1 SUPPLIER_NUMBER,
REPLACE(PHA.COMMENTS, CHR(10), ' ') PO_HEADER_DESCRIPTION,
NVL(PRH.SEGMENT1, pda.req_header_reference_num) PR_NUMBER,
PHA.SEGMENT1 PO_NUMBER,
PLA.LINE_NUM PO_LINE_NUMBER,
(SELECT SEGMENT1
FROM apps.MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = PLA.ITEM_ID
AND ORGANIZATION_ID = PLLA.SHIP_TO_ORGANIZATION_ID) ITEM,
pda.destination_type_code DESTINATION_TYPE,
REPLACE(REPLACE(PLA.ITEM_DESCRIPTION, CHR(13), ' '),
CHR(10),
' ') PO_LINE_DESCRIPTION,
TO_CHAR(NVL(PLA.UNIT_PRICE * PLA.QUANTITY, '0.00'),
'999,999,999,999.99') PO_LINE_AMOUNT,
PO_INQ_SV.GET_PO_TOTAL(PHA.TYPE_LOOKUP_CODE,
PHA.PO_HEADER_ID,
NULL) PO_TOTAL_AMOUNT,
PHA.CURRENCY_CODE PO_CURRENCY,
PLA.PURCHASE_BASIS PO_LINE_TYPE,
MCK.CONCATENATED_SEGMENTS HERMES_CATEGORY,
PLLA.PROMISED_DATE PROMISED_DATE,
PLLA.NEED_BY_DATE NEED_BY_DATE --LPAD(TO_CHAR(PLLA.NEED_BY_DATE,'MM/DD/YYYY '),20) "NEED_BY_DATE",
PPX1.FULL_NAME REQUESTER,
PPX2.FULL_NAME PREPARER,
PPF2.AGENT_NAME BUYER,
HRL.LOCATION_CODE SHIP_TO_LOC,
GCCK.CONCATENATED_SEGMENTS GL_ACCOUNT,
PLLA.SHIPMENT_NUM SHIPMENT_NUMBER,
PLA.UNIT_PRICE UNIT_PRICE,
(PRL.unit_price * PRL.quantity)Req_line_amt,
PLLA.QUANTITY QTY_ORDERED,
PLLA.QUANTITY_BILLED QTY_BILLED,
PLLA.QUANTITY_RECEIVED QTY_RECD,
(PLLA.QUANTITY_BILLED * PLA.UNIT_PRICE) AMOUNT_BILLED,
(PLLA.QUANTITY_RECEIVED * PLA.UNIT_PRICE) AMOUNT_RECD,
RCD.RCV_CREATION_DATE RECEIVING_CREATION_DATE,
PA.segment1 PROJECT,
pa.project_type PROJECT_TYPE,
AT.NAME PAYMENT_TERMS,
DECODE(PLLA.INSPECTION_REQUIRED_FLAG ||
PLLA.RECEIPT_REQUIRED_FLAG,
'NN',
'2 WAY',
'NY',
'3 WAY',
'YY',
'4 WAY',
'Not Specified') MATCHING_TYPE,
PDA.ACCRUE_ON_RECEIPT_FLAG ACCRUE_AT_RECEIPT,
PHA.AUTHORIZATION_STATUS PO_STATUS,
PLLA.CLOSED_CODE SHIPMENT_STATUS,
(SELECT ORGANIZATION_CODE
FROM apps.MTL_PARAMETERS
WHERE ORGANIZATION_ID = PLLA.SHIP_TO_ORGANIZATION_ID) SHIP_TO_ORG,
PRH.description Description_Headers,
PRL.LINE_NUM Line#,
TO_CHAR(TRUNC(PRL.CREATION_DATE), 'DD-MON-YYYY') Creation_date_of_Req_line,
(SELECT distinct papf.full_name
FROM apps.po.po_action_history pah,
applsys.fnd_user fu,
hr.per_all_people_f papf
WHERE object_id = prh.requisition_header_id
AND pah.employee_id = fu.employee_id
AND fu.employee_id = papf.person_id
AND SYSDATE BETWEEN papf.effective_start_date AND
papf.effective_end_date
AND pah.object_type_code = 'REQUISITION'
-- AND pah.action_code = 'APPROVE'
AND pah.sequence_num =
(SELECT MAX(sequence_num)
FROM po.po_action_history pah1
WHERE pah1.object_id = pah.object_id
AND pah1.object_type_code = 'REQUISITION'
-- AND pah1.action_code = 'APPROVE'
)) Performed_By,
PRH.CREATION_DATE PR_CREATION_DATE,
(SELECT distinct pah.ACTION_DATE
FROM apps.po.po_action_history pah,
applsys.fnd_user fu,
hr.per_all_people_f papf
WHERE object_id = prh.requisition_header_id
AND pah.employee_id = fu.employee_id
AND fu.employee_id = papf.person_id
AND SYSDATE BETWEEN papf.effective_start_date AND
papf.effective_end_date
AND pah.object_type_code = 'REQUISITION'
-- AND pah.action_code = 'APPROVE'
AND pah.sequence_num =
(SELECT MAX(sequence_num)
FROM po.po_action_history pah1
WHERE pah1.object_id = pah.object_id
AND pah1.object_type_code = 'REQUISITION'
-- AND pah1.action_code = 'APPROVE'
)) Approval_Date,
prl.line_num Line,
(SELECT distinct PRIMARY_UNIT_OF_MEASURE
FROM apps.mtl_system_items_b
where INVENTORY_ITEM_ID(+) = PLA.item_id) UOM,
(SELECT distinct DESCRIPTION
FROM apps.hr_locations
where location_id = pha.BILL_TO_LOCATION_ID) BILL_TO,
(SELECT displayed_field
FROM APPS.po_lookup_codes fob
WHERE fob.lookup_type = 'FREIGHT TERMS'
AND fob.lookup_code = pha.freight_terms_lookup_code) FOB_Terms,
PDA.ATTRIBUTE2 SIngle_Bid,
PDA.ATTRIBUTE9 Emergency_PO,
PDA.ATTRIBUTE5 After_The_fact,
PDA.ATTRIBUTE6 Expedited_PO,
PDA.ATTRIBUTE7 O2_Cleaning,
PDA.ATTRIBUTE10 Critical_Item,
PDA.ATTRIBUTE3 OEM_SOurced,
PLA.CANCEL_DATE Cancelled_DATE,
PRH.description Line_Description,
PRL.CURRENCY_CODE PR_CURRENCY,
PDA.DESTINATION_ORGANIZATION_ID deliver_to_organization,
PRL.CURRENCY_UNIT_PRICE PR_Unit_Price,
PHA.PO_HEADER_ID PO_HEADER,
PRL.CURRENCY_UNIT_PRICE Currancy_Price,
por_view_reqs_pkg.get_note_to_agent
(prh.requisition_header_id)
Note_to_Buyer
FROM APPS.PO_HEADERS_ALL PHA,
APPS.PO_LINES_ALL PLA,
APPS.PO_LINE_LOCATIONS_ALL PLLA,
APPS.HR_LOCATIONS_ALL HRL,
APPS.PO_DISTRIBUTIONS_ALL PDA,
(SELECT DISTINCT AGENT_ID, AGENT_NAME from APPS.PO_AGENTS_V) PPF2,
APPS.AP_SUPPLIERS APS,
APPS.MTL_CATEGORIES_KFV MCK,
APPS.PO_REQUISITION_HEADERS_ALL PRH,
APPS.PO_REQUISITION_LINES_ALL PRL,
APPS.PO_REQ_DISTRIBUTIONS_ALL PRD,
APPS.HR_OPERATING_UNITS HOU,
APPS.PER_PEOPLE_X PPX1,
APPS.PER_PEOPLE_X PPX2,
APPS.GL_CODE_COMBINATIONS_KFV GCCK,
APPS.PO_LINE_TYPES PLT,
APPS.GL_LEDGERS GL,
APPS.PA_PROJECTS_ALL PA,
APPS.AP_TERMS AT,
(SELECT MAX(RSL.CREATION_DATE) RCV_CREATION_DATE,
pla.po_line_id
FROM APPS.RCV_SHIPMENT_LINES RSL,
APPS.PO_HEADERS_ALL PHA,
APPS.PO_LINES_ALL PLA
WHERE 1 = 1
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND RSL.PO_line_ID(+) = PlA.PO_line_ID
GROUP BY pla.po_line_id) RCD
WHERE 1 = 1
AND RCD.PO_LINE_ID(+) = PLA.PO_LINE_ID
AND PHA.TERMS_ID = AT.TERM_ID
AND PHA.VENDOR_ID = APS.VENDOR_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PLLA.SHIP_TO_LOCATION_ID = HRL.LOCATION_ID
AND nvl(PLLA.CANCEL_FLAG, 'N') = 'N'
AND PHA.AGENT_ID = PPF2.AGENT_ID(+)
AND PLA.CATEGORY_ID = MCK.CATEGORY_ID
AND PHA.PO_HEADER_ID = PDA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PDA.PO_LINE_ID
AND PA.PROJECT_ID(+) = PDA.PROJECT_ID
AND PRH.REQUISITION_HEADER_ID(+) = PRL.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID(+) = PRD.REQUISITION_LINE_ID
AND PDA.REQ_DISTRIBUTION_ID = PRD.DISTRIBUTION_ID(+)
AND PHA.ORG_ID = HOU.ORGANIZATION_ID
AND PDA.DELIVER_TO_PERSON_ID = PPX1.PERSON_ID(+)
AND PRH.PREPARER_ID = PPX2.PERSON_ID(+)
AND PDA.CODE_COMBINATION_ID = GCCK.CODE_COMBINATION_ID(+)
AND PLT.LINE_TYPE_ID = PLA.LINE_TYPE_ID(+)
-- AND PHA.AUTHORIZATION_STATUS = 'APPROVED'
AND PRH.AUTHORIZATION_STATUS= 'APPROVED'
AND HOU.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND GL.LEDGER_ID = NVL(P_SET_OF_BOOK_ID, GL.LEDGER_ID)
AND HOU.ORGANIZATION_ID =
NVL(P_ORGANIZATION_ID, HOU.ORGANIZATION_ID)
AND PHA.AGENT_ID = NVL(P_BUYER_ID, PHA.AGENT_ID)
AND HRL.LOCATION_ID = NVL(P_LOCATION_ID, HRL.LOCATION_ID)
AND ((P_REQUESTOR_ID is null and 1 = 1) OR
(P_REQUESTOR_ID is not null and
PPX1.PERSON_ID = P_REQUESTOR_ID))
AND TRUNC(PHA.APPROVED_DATE) BETWEEN
NVL(TRUNC(fnd_date.canonical_to_date(PO_APPR_DATE_FROM)),
TRUNC(PHA.APPROVED_DATE)) AND
NVL(TRUNC(fnd_date.canonical_to_date(PO_APPR_DATE_TO)),
TRUNC(PHA.APPROVED_DATE))
ORDER BY PHA.SEGMENT1,
PLA.LINE_NUM,
PLLA.QUANTITY,
GCCK.CODE_COMBINATION_ID;
