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(+)