7 Most Important Procurement Tables in Oracle Fusion

Procurement Tables in Oracle Fusion

Hi friends , we are going to discuss about the Procurement Tables in Oracle Fusion. We will share the list of most important oracle fusion procurement tables which helps you to store the Purchase order , requisitions , Suppliers and PO receiving data in oracle fusion. If you need to extract the Purchase order data , PO requisition data and suppliers and PO receiving data in oracle fusion , you need to know the name of Procurement tables. In this post , we will try to share these procurement tables using this you can develop the complete sql queries in oracle fusion. We will also try to share some of the important sql queries too related to procurement module in oracle fusion. Please find below the complete detail about Procurement Tables in Oracle Fusion.

7 Most Important Procurement Tables in Oracle Fusion

7 Most Important Procurement Tables in Oracle Fusion

Here below is the list of some important tables which stores the oracle procurement data in oracle fusion. We can use these tables to develop the procurement module related sql queries in oracle fusion.

1.PO_HEADERS_ALL

2.PO_LINES_ALL

3.PO_DISTRIBUTIONS_ALL

4.POZ_SUPPLIERS

5.POZ_SUPPLIER_SITES_ALL_M

6.POR_REQ_DISTRIBUTIONS_ALL

7.POR_REQUISITION_LINES_ALL

8.POR_REQUISITION_HEADERS_ALL

Detail SQL Query to extract the procurement data in oracle fusion 

Here below is the list of the some important queries about Procurement in Oracle Fusion

SELECT POH.PO_HEADER_ID "PO ID",

       POH.SEGMENT1 " PO Number"  ,

       PRHA.REQUISITION_NUMBER,

PRHA.REQUISITION_HEADER_ID " PO Requisition ID"

FROM

       PO_HEADERS_ALL POH,

       PO_DISTRIBUTIONS_ALL PDA ,

       POR_REQ_DISTRIBUTIONS_ALL PRDA ,

       POR_REQUISITION_LINES_ALL PRLA ,

       POR_REQUISITION_HEADERS_ALL PRHA

WHERE  1=1

   AND POH.PO_HEADER_ID = PDA.PO_HEADER_ID

   AND PRHA.REQUISITION_NUMBER = '445566'

   AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID

   AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID

   AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID

7 Most Important Procurement Tables in Oracle Fusion

SELECT DISTINCT

POHA.SEGMENT1,
HP.PARTY_NAME as VENDOR_NAME,
HPS.PARTY_SITE_NAME,
PSS.VENDOR_SITE_CODE,
APT.NAME,
PPNF.LAST_NAME||', '||PPNF.FIRST_NAME "Buyer Name",
POHA.DOCUMENT_STATUS,
PSS.VENDOR_SITE_CODE,
HAOT.NAME as Procurement_BU,
XEP.NAME as SOLDTO_LE_ID,
HRLA1.LOCATION_NAME as BILL_TO_LOCATION_ID,
HRLA.LOCATION_NAME as SHIP_TO_LOCATION_ID
FROM
PO_HEADERS_ALL POHA,
HR_ORGANIZATION_UNITS_F_TL HAOT,
XLE_ENTITY_PROFILES XEP,
HR_LOCATIONS_ALL HRLA,
HR_LOCATIONS_ALL HRLA1,
POZ_SUPPLIERS PS,
HZ_PARTIES HP,
HZ_PARTY_SITES HPS,
AP_TERMS_TL APT,
PER_PERSON_NAMES_F PPNF,
POZ_SUPPLIER_SITES_ALL_M PSS
WHERE HAOT.ORGANIZATION_ID = POHA.BILLTO_BU_ID
AND HAOT.ORGANIZATION_ID = POHA.PRC_BU_ID
AND HAOT.ORGANIZATION_ID = POHA.REQ_BU_ID
AND XEP.LEGAL_ENTITY_ID = POHA.SOLDTO_LE_ID
AND HRLA.LOCATION_ID=POHA.SHIP_TO_LOCATION_ID
AND HRLA1.LOCATION_ID=POHA.BILL_TO_LOCATION_ID
AND PS.VENDOR_ID=POHA.VENDOR_ID
AND PS.PARTY_ID=HP.PARTY_ID
AND HP.PARTY_ID=HPS.PARTY_ID
AND APT.TERM_ID=POHA.TERMS_ID
AND PPNF.PERSON_ID=POHA.AGENT_ID
AND PSS.VENDOR_SITE_ID=POHA.VENDOR_SITE_ID
ORDER BY POHA.SEGMENT1

7 Most Important Procurement Tables in Oracle Fusion