Internal requisition tables in oracle apps

Internal requisition tables in oracle apps

In this post , We will be discuss about the Internal requisition tables in oracle apps. Internal requisition in oracle apps is the process of requesting material or items inside the organization. For Internal requisition ,We don't create the purchase orders. In Internal requisition , We do create the material or item requests from some specific Location or Inventory stores inside the organizations. This is the basic difference between the Internal Requisition and the external requisition for the External suppliers using the purchase order. Here below i will share the detail table list for Internal requisition tables in oracle apps.

8 Important Internal requisition tables in oracle apps

There is not difference in the Tables for Internal requisition and in External Requisitions. Both Types of Requisitions have same tables in oracle apps. TYPE_LOOKUP_CODE column in PO_REQUISITION_HEADERS_ALL stores the Requisition type information’s in oracle apps. With this Column we can differentiate the Internal and External requisitions in oracle apps.

1.PO_REQUISITION_HEADERS_ALL

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

8.hr_operating_units

Detail SQL Query using Internal requisition tables in oracle apps

Here below is the Internal Requisition tables sql query in oracle apps. This sql query helps to extract both Internal and External requisitions 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(+)

Internal requisition tables in oracle apps