Internal requisition query in oracle apps
In this post, We will discuss about the Internal requisition query 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 Internal requisition query in oracle apps.
Example of Internal requisition query in oracle apps
SELECT hou.name "Operating unit"
,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
0 comments:
Post a Comment