Oracle Forums Community of Oracle Professionals including Fusion/Cloud Application Consultants, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to OraERP.com Social Community, a friendly and active community of Oracle Technology Professionals who believe that technology can ‘make the world a better place’. By joining Oracle ERP Community you will have the ability to Post Topics, Receive our Newsletter, subscribe to threads and access many other special features. Registration is Quick and Simple.
Get unlimited access to Oracle Tutorials, Articles, eBooks, Tools and Tips by upgrading to "Premium Membership".
Oracle Training

Thread Rating:
  • 24 Vote(s) - 3.33 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Query to extract Oracle Requisitions Details
#1
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'
Reply
Thanks given by:


Possibly Related Threads...
Thread Author Replies Views Last Post
  Oracle E-business Suite Implementation Operational Analysis Questionnaire Kashif Manzoor 2 17,008 12-13-2017, 09:09 PM
Last Post: dilkhan
  Oracle Implementation Project Plan admin 22 46,687 11-18-2017, 02:30 AM
Last Post: Luz Navarrete
  Oracle Cloud Fusion Financials Interview Questions and Answers M Irfan 0 1,546 10-08-2017, 08:48 PM
Last Post: M Irfan
  Oracle AIM - BR100 Documents admin 33 127,209 10-06-2017, 02:13 PM
Last Post: adroit.ramesh@gmail.com
  Oracle AIM - BR100 Documents Inv PO admin 2 14,728 10-06-2017, 02:10 PM
Last Post: adroit.ramesh@gmail.com
  Oracle Fusion Financials General Ledger Essentials M Irfan 0 819 10-01-2017, 09:36 PM
Last Post: M Irfan
  Oracle HRMS BR100 document admin 4 6,655 09-19-2017, 01:18 AM
Last Post: ahmad.ora
  Oracle Fusion Cloud Application Logical and Relational Data Model Diagrams Hassanmalik 1 1,024 08-24-2017, 07:01 PM
Last Post: sammy
  Oracle ERP Implementation Project Schedule based on OUM Kashif Manzoor 4 12,720 08-20-2017, 04:33 AM
Last Post: streetlife
  AIM Vs OUM - Application Implementation Methodology vs Oracle Unified Methodology Kashif Manzoor 6 23,231 08-10-2017, 01:13 PM
Last Post: hclim586



Users browsing this thread: 2 Guest(s)
Get Premium Membership Get Oracle Tutorials, Tips! Post a Question!