Query to get rfq Details in Oracle Apps

Query to get rfq Details in Oracle Apps

Hi friends, we are going to discuss about the query to get rfq details in Oracle Apps. We will share the complete sql query which helps to extract the complete rfq quotation details. Using this sql query , we can find out the rfq related details from data base tables in oracle apps. In Oracle apps, once rfq details stores in multiple tables , so this query is developed using these multiple tables to fetch the complete rfq related informations in oracle apps. Using this query, we can develop the custom rfq reports in oracle apps. We will also share the important tables too which stores the rfq informations in oracle apps. Please find below the complete details about Query to get rfq Details in Oracle Apps.

Query to get rfq Details in Oracle Apps

Important tables to get rfq Details in Oracle Apps

1 PO RFQ Tables

1.PO_RFQ_VENDORS

3 Sales Order RFQ Tables

1.ASO_QUOTE_HEADERS_ALL

2.ASO_QUOTE_LINES_ALL

3.ASO_SHIPMENTS

4 I-Sourcing RFQ Tables

1.PON_AUCTION_HEADERS_ALL

2.PON_BID_HEADERS

3.PON_AUCTION_ITEM_PRICES_ALL

4,PON_BID_ITEM_PRICES

Detail SQL Query to PO rfq Details in Oracle Apps

Here below is the detail sql query to extract the purchase order rfq details in oracle apps.

SELECT   msi.segment1 "Purchasing Item",

         msi.description "Purchasing Item Description", msi.primary_uom_code "Item UOM",

poll.quantity "Ordr Quantity",

         poh.attribute1

         || '/'

         || poh.segment1 "RFQ #",

         TO_CHAR (TRUNC (poh.creation_date),'DD-MON-YYYY') po_creation_date, aps.vendor_name "Supplier",

         apss.address_line1||','||apss.address_line2||','||apss.address_line3||','||apss.city||','||apss.zip "Supplier Address"

    FROM po_headers_all poh,

         po_lines_all pol,

         po_line_locations_all poll,

         mtl_system_items_b msi,

         hr_operating_units hou,

         po_rfq_vendors prv,

         ap_suppliers aps,

         ap_supplier_sites_all apss

   WHERE pol.item_id = msi.inventory_item_id

     AND aps.vendor_id = prv.vendor_id

     AND apss.vendor_site_id = prv.vendor_site_id

     AND poh.org_id = hou.organization_id

     AND prv.po_header_id = poh.po_header_id

     ANDpoh.type_lookup_code = 'RFQ'

     AND poh.po_header_id = pol.po_header_id

     AND pol.po_line_id = poll.po_line_id

     AND poll.ship_to_organization_id = msi.organization_id

Detail SQL Query to Sales Order rfq Details in Oracle Apps

Here below is the detail sql query to extract the sales order rfq details in oracle apps.

SELECT   ooh.order_number "Sales Order No"

         ,qte.quote_number

         ,qte.quote_name

         ,qtl.line_number "Quote Line No"

         , msb.segment1 "Item Code"

         , ool.ordered_quantity "Item Order Quantity"

         , ool.order_quantity_uom "UOM"

    FROM   aso.aso_quote_headers_all qte

         , aso.aso_quote_lines_all qtl

         , aso.aso_shipments shp

         , ont.oe_order_headers_all ooh

         , ont.oe_order_lines_all ool msb

         , mtl_system_items_b 

    WHERE   ool.header_id = ooh.header_id

    AND     ool.source_document_line_Id = shp.shipment_id ;

    AND qtl.quote_header_id = qte.quote_header_id

AND ool.inventory_item_id=MSB.inventory_item_id

AND msi.organization_id=:inv_org_id

    AND    shp.quote_header_id = qtl.quote_header_id

    AND    shp.quote_line_id = qtl.quote_line_id

    AND    qte.quote_number=:p_quote_number

    AND    ooh.source_document_id = qte.quote_header_id

Query to get rfq Details in Oracle Apps