Query to extract Oracle Requisitions Details

[Ahmad Mujeeb]

Requisitions part of Oracle Purchasing, you can centralize your purchasing department, source your requisitions with the best suppliers, and ensure that you obtain the appropriate management approval before creating purchase orders from requisitions.
Use the Requisitions window to create requisitions. You must choose the requisition type (internal or purchase). You can also provide a description, unlimited notes, and defaults for requisition lines. For each requisition line, you choose the item you want to order along with the quantity and delivery location. You can get sourced pricing from catalog quotations or open blanket purchase agreements. You can also choose a price from a list of historical purchase order prices. In the Distributions window, you can charge the item to the appropriate accounts, or you can let the Account Generator create the accounts for you.
Below is the query to get following Requisition details:
Operating unit
Number
Description
Type                                                                                                                           
Status
Preparer
Item
Category
Description
UOM
Quantity
Unit cost
Organization
Location
Department
Requister

Oracle EBS Version 12.1.3


SELECT
                                         hou.name "Operating unit"
                                        ,PRH2.SEGMENT1 "Requisition Number"
                                        ,prh2.Description
                                        ,prh2.TYPE_LOOKUP_CODE req_type
                                        ,PRH2.AUTHORIZATION_STATUS REQ_STATUS
                                        ,PPF.FULL_NAME Preparer
                                        ,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"
                                        ,mic.segment1||'.'||mic.segment2||'.'||mic.segment3||'.'||mic.segment4 Category                                       
                                        ,hout.NAME Organization
                                        ,hla.LOCATION_CODE Location
                                        ,prl2.DESTINATION_SUBINVENTORY Department
                                        ,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 hout.organization_id(+) = prl2.destination_organization_id
                                        AND hout.LANGUAGE(+) = USERENV ('LANG')
                                        and ppf2.person_id(+)=prl2.TO_PERSON_ID
                                        AND TRUNC(SYSDATE) BETWEEN PPF2.EFFECTIVE_START_DATE(+) AND PPF2.EFFECTIVE_END_DATE(+)
--                                        and hla.LOCATION_ID=prl.
                                        and hla.location_id(+) = prl2.deliver_to_location_id
--                                        and prl2.DESTINATION_ORGANIZATION_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 NVL(prh2.cancel_flag,'N')<>'Y'
                                        and NVL(prL2.cancel_flag,'N')<>'Y'
                                        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 PRH2.ORG_ID=102
                                        and prh2.segment1='xx'