Purchase order PO table details in Oracle Apps R12, PO_HEADERS_ALL table plays an important role in PO Purchasing module, all PO module tables are linked with PO header table which stores PO header related information. List of Important Purchase Order (PO) Module tables in Oracle Apps R12 PO_HEADERS_ALL PO_LINES_ALL PO_LINE_LOCATIONS_ALL PO_DISTRIBUTIONS_ALL PO_VENDORS_ALL PO_VENDOR_SITES_ALL PO_RELEASES_ALL PO_VENDOR_CONTACTS
PO - Orapoint
Query to Get Open Purchase Order PO in Oracle Apps R12
This query is used to get open purchase order PO in Oracle Apps R12, it will display complete information of open purchase order which are linked with Requisitions, open purchase order means invoicing or billing is not yet done against purchase order.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
SELECT ph.segment1 po_num, ph.creation_date, hou.NAME "Operating Unit", ppx.full_name "Buyer Name", ph.type_lookup_code "PO Type", plc.displayed_field "PO Status", ph.comments, pl.line_num, plt.order_type_lookup_code "Line Type", msi.segment1 "Item Code", pl.item_description, pl.unit_meas_lookup_code "UOM", pl.base_unit_price, pl.unit_price, pl.quantity, ood.organization_code "Shipment Org Code", ood.organization_name "Shipment Org Name", pv.vendor_name supplier, pvs.vendor_site_code, (pl.unit_price * pl.quantity) "Line Amount", prh.segment1 req_num, prh.type_lookup_code req_method, ppx1.full_name "Requisition requestor" FROM po_headers_all ph, po_lines_all pl, po_distributions_all pda, po_vendors pv, po_vendor_sites_all pvs, po_distributions_all pd, po_req_distributions_all prd, po_requisition_lines_all prl, po_requisition_headers_all prh, hr_operating_units hou, per_people_x ppx, mtl_system_items_b msi, po_line_types_b plt, org_organization_definitions ood, per_people_x ppx1, po_lookup_codes plc WHERE 1 = 1 AND ph.vendor_id = pv.vendor_id AND PH.ORG_ID=:P_ORG_ID AND ph.po_header_id = pl.po_header_id AND ph.vendor_site_id = pvs.vendor_site_id AND ph.po_header_id = pd.po_header_id AND pl.po_line_id = pd.po_line_id AND pd.req_distribution_id = prd.distribution_id(+) AND prd.requisition_line_id = prl.requisition_line_id(+) AND prl.requisition_header_id = prh.requisition_header_id(+) AND hou.organization_id = ph.org_id AND ph.agent_id = ppx.person_id AND pda.po_header_id = ph.po_header_id AND pda.po_line_id = pl.po_line_id AND ph.closed_code = 'OPEN' AND pl.line_type_id = plt.line_type_id AND ood.organization_id = pda.destination_organization_id AND ppx1.person_id(+) = prh.preparer_id AND pda.destination_organization_id = msi.organization_id(+) AND msi.inventory_item_id = NVL (pl.item_id, msi.inventory_item_id) AND plc.lookup_type = 'DOCUMENT STATE' AND plc.lookup_code = ph.closed_code |
Table Descriptions po_headers_all : This table stores PO header related data po_lines_all