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:
  • 10 Vote(s) - 4.1 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 R-12 - Oracle Payable User Manual admin 1 10,600 07-08-2017, 12:12 AM
Last Post: Spartan117
  Oracle E-Business Suite on Oracle Cloud admin 0 356 06-03-2017, 05:38 AM
Last Post: admin
  Oracle E-Business Suite on Oracle Cloud Kashif Manzoor 0 2,445 04-07-2016, 10:20 PM
Last Post: Kashif Manzoor
  Query- How to check the Oracle database version Zishan Ali 0 1,478 08-16-2015, 11:35 PM
Last Post: Zishan Ali
  SQL Query to get the username in Fusion Applications Zishan Ali 0 1,182 08-06-2015, 05:11 PM
Last Post: Zishan Ali
  Query Oracle iRecruitment candidates basic information Ahmad Mujeeb 0 1,305 07-02-2015, 08:21 PM
Last Post: Ahmad Mujeeb
  Query to Check Tablespace Size and frees pace in Oracle Database Ahmad Mujeeb 0 1,866 06-25-2015, 07:07 PM
Last Post: Ahmad Mujeeb
  Step By Step: Install and setup Oracle 11g R2 RAC on Oracle Enterprise Linux 5.5 (32 abdulrehman 0 30,762 01-23-2012, 03:59 PM
Last Post: abdulrehman



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