PO Requisition Query in Oracle Fusion

PO Requisition Query in Oracle Fusion

Hi friends , we are going to discuss about the PO Requisition Query in Oracle Fusion. We will share the detail sql query in oracle fusion which will help to extract the PO requisition information's in oracle fusion. We will share also share some of the most important important PO requisition tables which do stores most of the requisition information's in oracle fusion. These requisition tables are very important , if you need to extract the Purchase order requisition data from oracle fusion application. Using these queries , We can develop the Custom Oracle Fusion BIP reports which helps us to extract the requisition information's in oracle fusion. Please find below the complete detail about the PO Requisition Query in Oracle Fusion.

5 Important tables in PO Requisition Query in Oracle Fusion

Here below is the list of some important PO requisition tables which we are using in the po requisition query in oracle fusion. There are many other PO requisitions tables too which helps to store the requisition detail but in this post , we are sharing most important po requisition tables only which stores some of the important po requisition information's.

1.POR_REQUISITION_HEADERS_ALL

2.POR_REQ_DISTRIBUTIONS_ALL

3.POR_REQUISITION_LINES_ALL

5.POZ_SUPPLIERS

6.POZ_SUPPLIER_SITES_ALL_M

Detail SQL Query to Extract PO Requisitions details Oracle Fusion

Here below is the complete set of query to find out the po requisition details in oracle fusion. We can use these queries to develop the custom fusion BIP reports.

SELECT     POH.SEGMENT1 " PO Number"  ,

       PRHA.REQUISITION_NUMBER

FROM

       POR_REQUISITION_LINES_ALL PRLA ,

       POR_REQUISITION_HEADERS_ALL PRHA,

       PO_HEADERS_ALL POH,

       PO_DISTRIBUTIONS_ALL PDA ,

       POR_REQ_DISTRIBUTIONS_ALL PRDA 

WHERE  1=1

   AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID

   AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID

   AND POH.PO_HEADER_ID = PDA.PO_HEADER_ID

   AND PRHA.REQUISITION_NUMBER = '88994466'

   AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID

PO Requisition Query in Oracle Fusion

Query 2:-

Here below is the second query which we can use for purchasing module in oracle fusion to extract the purchasing module related data.

Here below is the one of the important purchasing query. You can use this query for purchasing and requisition information's in oracle fusion.

SELECT DISTINCT

HP.PARTY_NAME as VENDOR_NAME,

PPNF.LAST_NAME||', '||PPNF.FIRST_NAME "Buyer Name",

POHA.SEGMENT1,

APT.NAME,

HPS.PARTY_SITE_NAME,
PSS.VENDOR_SITE_CODE,
POHA.DOCUMENT_STATUS,
PSS.VENDOR_SITE_CODE
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 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
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
ORDER BY POHA.SEGMENT1

PO Requisition Query in Oracle Fusion