Purchase Order can be considered the very basic block on which P2P cycle is based upon. In this post, we will talk about the query to fetch the Purchase Order Details like shipment number, receipt number, buyer, Variance Default Account, Accrual Default Account, Inventory Expense Account, shipped quantity etc.
(By the way, I was helped by this book a great deal, just in case you want to refer. No no.. I am neither the author nor the publisher of this book. )
But before that let’s see the list of important tables underlying a PO:
1. PO_HEADERS_ALL –> To store the Purchase Order header info.
2. PO_LINES_ALL –> Captures the line level details
3. PO_LINE_LOCATIONS_ALL –> To keep the shipment related details
4. PO_DISTRIBUTIONS_ALL –> Mainly to keep the account related info and most probably to find the connection between a PO and another entity such as Requisition.
Since you are interested in Purchase Order, most probably getting the supplier details will also be in your mind. Check out this post.
Supplier Details Query in Oracle Fusion – POZ_SUPPLIERS
Also no one likes to depart with the money, but having a supplier means you will end up paying him. So this payment details query can be useful.
Query is:
SELECT iop.organization_code,
po_hdr.segment1 po_number,
po_hdr.comments ,
hp.party_name supplier,
pvsa.vendor_site_code supplier_site,
po_hdr.document_status order_status,
po_hdr.CREATION_DATE order_creation_date,
po_hdr.revision_num,
po_line.line_num,
esib.item_number,
ESIB.ITEM_TYPE,
(SELECT SEGMENT1 || ‘.’ ||
SEGMENT2 || ‘.’ ||
SEGMENT3 || ‘.’ ||
SEGMENT4 || ‘.’ ||
SEGMENT5 || ‘.’ ||
SEGMENT6 || ‘.’ ||
SEGMENT7
FROM Gl_CODE_COMBINATIONS gcc,
PO_DISTRIBUTIONS_ALL pda
WHERE 1 = 1
and pda.po_line_id = po_line.po_line_id
and gcc.CODE_COMBINATION_ID = pda.CODE_COMBINATION_ID
and rownum = 1) INV_EXP_DEFATUL_ACCT,
(SELECT SEGMENT1 || ‘.’ ||
SEGMENT2 || ‘.’ ||
SEGMENT3 || ‘.’ ||
SEGMENT4 || ‘.’ ||
SEGMENT5 || ‘.’ ||
SEGMENT6 || ‘.’ ||
SEGMENT7
FROM Gl_CODE_COMBINATIONS gcc,
PO_DISTRIBUTIONS_ALL pda
WHERE 1 = 1
and pda.po_line_id = po_line.po_line_id
and gcc.CODE_COMBINATION_ID = pda.ACCRUAL_ACCOUNT_ID
and rownum = 1) ACCRUAL_DEFAULT_ACCT,
(SELECT SEGMENT1 || ‘.’ ||
SEGMENT2 || ‘.’ ||
SEGMENT3 || ‘.’ ||
SEGMENT4 || ‘.’ ||
SEGMENT5 || ‘.’ ||
SEGMENT6 || ‘.’ ||
SEGMENT7
FROM Gl_CODE_COMBINATIONS gcc,
PO_DISTRIBUTIONS_ALL pda
WHERE 1 = 1
and pda.po_line_id = po_line.po_line_id
and gcc.CODE_COMBINATION_ID = pda.VARIANCE_ACCOUNT_ID
and rownum = 1) VARIANCE_DEFAULT_ACCT,
po_line.item_description,
po_line.quantity order_qty,
po_line.unit_price,
line_loc.requested_ship_date,
line_loc.need_by_date,
po_line.line_status,
po_hdr.agent_id buyer_id,
(select ppf.full_name
from per_person_names_f ppf
where ppf.person_name_id = po_hdr.agent_id
and TRUNC(SYSDATE) BETWEEN trunc(ppf.effective_start_date)
AND trunc(ppf.effective_end_date) and rownum=1) buyer,
rsh.SHIPMENT_NUM SHIPMENT_NUM,
rsh.receipt_num receipt_number,
rsl.quantity_received quantity_received,
rsl.quantity_delivered quantity_delivered, rsl.quantity_shipped quantity_shipped,
rsh.creation_date receipt_date,
po_hdr.created_by,
line_loc.destination_type_code destination_type,
line_loc.sales_order_number customer_sales_order,
(SELECT prha.requisition_number
FROM por_req_distributions_all prda
,por_requisition_lines_all prla
,por_requisition_headers_all prha
,po_distributions_all pda
WHERE prda.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id
AND prda.distribution_id = pda.req_distribution_id
AND pda.line_location_id = line_loc.line_location_id
AND rownum = 1) requisition,
rsl.shipment_line_status_code shipment_status_code
FROM po_headers_all po_hdr,
poz_suppliers pv,
hz_parties hp,
poz_supplier_sites_all_m pvsa,
po_lines_all po_line,
po_line_locations_all line_loc,
egp_system_items_b esib,
inv_org_parameters iop,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
hr_operating_units hou
WHERE po_hdr.po_header_id = po_line.po_header_id
AND pv.vendor_id = po_hdr.vendor_id
AND pv.party_id = hp.party_id
AND pvsa.vendor_site_id = po_hdr.vendor_site_id
AND po_line.po_line_id = line_loc.po_line_id
AND po_line.item_id = esib.inventory_item_id
AND line_loc.ship_to_organization_id = esib.organization_id
AND iop.organization_id = line_loc.ship_to_organization_id
AND esib.organization_id = iop.organization_id
AND rsh.shipment_header_id(+) = rsl.shipment_header_id
AND line_loc.po_line_id = rsl.po_line_id(+)
AND line_loc.po_header_id = rsl.po_header_id(+)
AND rsl.po_line_location_id = line_loc.line_location_id
AND rsl.source_document_code = ‘PO’
AND hou.organization_id = po_hdr.PRC_BU_ID
Related Posts
Supplier Details Query in Oracle Fusion – POZ_SUPPLIERS
Invoices Table in Oracle Fusion – AP_INVOICES_ALL
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
Check out the Amazon Deals of the day based on your interests..
