Requisition Query in oracle apps r12

Requisition Query in oracle apps r12

In this post , We will be discuss about the Requisition Query in oracle apps r12. This Query helps to extract the Complete Purchase requisitions created in oracle apps. We will get the both Internal and External types of Requisitions using this SQL query. We will get the detail level requisition informations having the Requester Details , requisition Item details with Price and Quantity. This Requisition query helps to extract the Oracle Purchase requisitions from back end.  Here below i will share this Detail Requisition Query in oracle apps r12 with Important Oracle apps requisition tables.

Important Tabled Used by Requisition Query in oracle apps r12

1.PO_REQUISITION_HEADERS_ALL

2.PO_REQUISITION_LINES_ALL
3.PER_ALL_PEOPLE_F
4.PER_ALL_PEOPLE_F
5.MTL_SYSTEM_ITEMS_B
6.hr_locations_all
7.hr_all_organization_units_tl
8.mtl_categories

9.hr_operating_units

Detail Requisition Query in oracle apps r12

Here below is the complete Requisition Query having complete detail level purchase requisition information's in oracle apps.

SELECT hou.name "Operating unit"

,PRH2.SEGMENT1 "Requisition Number"

,prh2.Description
,MSI2.SEGMENT1 ITEM_CODE
,PRL2.LINE_NUM PR_LINE_NUM
,PRL2.ITEM_DESCRIPTION REQ_LINE_DESC
,PRL2.UNIT_MEAS_LOOKUP_CODE UOM

,PRH2.AUTHORIZATION_STATUS REQ_STATUS
,PPF.FULL_NAME Preparer
,prh2.TYPE_LOOKUP_CODE "Requisition_type"

,hout.NAME Organization
,hla.LOCATION_CODE Location
,ppf2.full_name Requisiter
,PRL2.QUANTITY
,prl2.unit_price "Unit cost"
FROM
 APPS.PO_REQUISITION_HEADERS_ALL PRH2
,APPS.PO_REQUISITION_LINES_ALL PRL2
,APPS.PER_ALL_PEOPLE_F PPF
,APPS.PER_ALL_PEOPLE_F PPF2
,APPS.MTL_SYSTEM_ITEMS_B MSI2
,apps.hr_locations_all hla
,apps.hr_all_organization_units_tl hout
,apps.mtl_categories mic
,apps.hr_operating_units hou
WHERE 1=1
and hou.organization_id=prh2.org_id
and NVL(prh2.cancel_flag,'N')<>'Y'
and NVL(prL2.cancel_flag,'N')<>'Y'
AND hout.organization_id(+) = prl2.destination_organization_id
AND hout.LANGUAGE(+) = USERENV ('LANG')
and ppf2.person_id(+)=prl2.TO_PERSON_ID
AND PRH2.ORG_ID=:P_ORG_ID
and hla.location_id(+) = prl2.deliver_to_location_id
and mic.CATEGORY_ID(+)=prl2.CATEGORY_ID
AND MSI2.INVENTORY_ITEM_ID=PRL2.ITEM_ID
AND msi2.inventory_item_id(+) = prl2.item_id
AND msi2.organization_id(+) = prl2.destination_organization_id
AND PRL2.REQUISITION_HEADER_ID=PRH2.REQUISITION_HEADER_ID
AND PPF.PERSON_ID=PRH2.PREPARER_ID
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE

AND TRUNC(SYSDATE) BETWEEN PPF2.EFFECTIVE_START_DATE(+) AND PPF2.EFFECTIVE_END_DATE(+)

Query to find internal requisition in oracle apps

Query to find internal requisition in oracle apps : requisition query in oracle apps r12

In this post, We will discuss about the Query to find internal requisition in oracle apps . I will share the SQL query which helps to extract the Complete requisition Information in Oracle Apps. You can refer this query for both type of Requisitions (Internal & External) . You can differentiate the Requisition Type with this Sql query column "Requisition_type". Please find below the detail Query to find internal requisition in oracle apps.

Example of Query to find internal requisition in oracle apps

SELECT hou.name "Operating unit"

,PRH2.SEGMENT1 "Requisition Number"
,prh2.Description
,PRH2.AUTHORIZATION_STATUS REQ_STATUS
,PPF.FULL_NAME Preparer
,prh2.TYPE_LOOKUP_CODE "Requisition_type"
,MSI2.SEGMENT1 ITEM_CODE
,PRL2.LINE_NUM PR_LINE_NUM
,PRL2.ITEM_DESCRIPTION REQ_LINE_DESC
,PRL2.UNIT_MEAS_LOOKUP_CODE UOM
,PRL2.QUANTITY
,prl2.unit_price "Unit cost"
,hout.NAME Organization
,hla.LOCATION_CODE Location
,ppf2.full_name Requisiter
FROM
 APPS.PO_REQUISITION_HEADERS_ALL PRH2
,APPS.PO_REQUISITION_LINES_ALL PRL2
,APPS.PER_ALL_PEOPLE_F PPF
,APPS.PER_ALL_PEOPLE_F PPF2
,APPS.MTL_SYSTEM_ITEMS_B MSI2
,apps.hr_locations_all hla
,apps.hr_all_organization_units_tl hout
,apps.mtl_categories mic
,apps.hr_operating_units hou
WHERE 1=1
and hou.organization_id=prh2.org_id
and NVL(prh2.cancel_flag,'N')<>'Y'
and NVL(prL2.cancel_flag,'N')<>'Y'
AND hout.organization_id(+) = prl2.destination_organization_id
AND hout.LANGUAGE(+) = USERENV ('LANG')
and ppf2.person_id(+)=prl2.TO_PERSON_ID
AND PRH2.ORG_ID=:P_ORG_ID
and hla.location_id(+) = prl2.deliver_to_location_id
and mic.CATEGORY_ID(+)=prl2.CATEGORY_ID
AND MSI2.INVENTORY_ITEM_ID=PRL2.ITEM_ID
AND msi2.inventory_item_id(+) = prl2.item_id
AND msi2.organization_id(+) = prl2.destination_organization_id
AND PRL2.REQUISITION_HEADER_ID=PRH2.REQUISITION_HEADER_ID
AND PPF.PERSON_ID=PRH2.PREPARER_ID
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE

AND TRUNC(SYSDATE) BETWEEN PPF2.EFFECTIVE_START_DATE(+) AND PPF2.EFFECTIVE_END_DATE(+)