Query to Find the Price list name for Item in Oracle Apps R12

SELECT qph.name

, msi.segment1

, qpl.operand

, qpl.product_precedence

FROM  qp_list_headers qph,

apps.qp_list_lines_v qpl,

inv.mtl_system_items_b msi

WHERE  qph.list_header_id = qpl.list_header_id

and qpl.product_attr_value = to_char(msi.inventory_item_id)

and msi.ORGANIZATION_ID =4

and msi.segment1 ='RED0126MCO';

Query to Find out the Requisition and PO number from Order Number in B2B Order

 SELECT    OOHA.ORDER_NUMBER,
            OOHL.LINE_NUMBER,
            PRHA.SEGMENT1 REQUISITION_NUMBER,
            PRLA.LINE_NUM REQUISITION_LINE_NUMBER,
            PHA.SEGMENT1 PO_NUMBER,
            PLA.LINE_NUM PO_LINE_NUMBER
  FROM   OE_ORDER_HEADERS_ALL OOHA,
         OE_ORDER_LINES_ALL OOHL,
         PO_REQUISITION_HEADERS_ALL PRHA,
         PO_REQUISITION_LINES_ALL PRLA,
         PO_REQ_DISTRIBUTIONS_ALL PRDA,
         PO_DISTRIBUTIONS_ALL PDA,
         PO_HEADERS_ALL PHA,
         PO_LINES_ALL PLA
 WHERE   OOHA.HEADER_ID = OOHL.HEADER_ID
         AND PRHA.INTERFACE_SOURCE_LINE_ID = OOHL.LINE_ID
         AND PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
         AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID
         AND PRDA.DISTRIBUTION_ID = PDA.REQ_DISTRIBUTION_ID
         AND PDA.PO_HEADER_ID = PHA.PO_HEADER_ID
         AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID;

Query to Find Order Management Join Query in Oracle Apps R12

SELECT   ooh.order_number,
         msib.segment1 item_number,
         ool.line_id,
         mr.reservation_quantity,
         mr.reservation_id
FROM     oe_order_headers_all ooh,
         oe_order_lines_all ool,
         mtl_reservations mr,
         mtl_system_items_b msib
WHERE    ooh.header_id = ool.header_id
         AND mr.demand_source_line_id = ool.line_id
         AND ool.ship_from_org_id = msib.organization_id
         AND mr.inventory_item_id = msib.inventory_item_id
         AND ORDER_NUMBER = 2076061;

Query to Find Order Management QUERY in Oracle Apps R12

SELECT   oh.order_number, ol.ordered_item, org.organization_name,
         ol.ordered_quantity, ol.shipped_quantity,         
         rsv.reservation_quantity,
         lkup.meaning pick_status, oh.org_id
FROM     oe_order_headers_all oh,
         oe_order_lines_all ol,
         oe_transaction_types_tl typ,
         wsh_delivery_details wdd,
         mtl_reservations rsv,
         org_organization_definitions org,
         fnd_lookup_values lkup
WHERE oh.header_id = ol.header_id
     AND oh.order_type_id = typ.transaction_type_id
     AND typ.NAME NOT LIKE '%Internal%Order%'
     AND wdd.source_header_id = oh.header_id
     AND wdd.source_line_id = ol.line_id
     AND wdd.released_status = lkup.lookup_code
     AND lkup.lookup_type = 'PICK_STATUS'
     AND org.organization_id = ol.ship_from_org_id
     AND rsv.demand_source_header_id(+) = ol.header_id
     AND rsv.demand_source_line_id(+) = ol.line_id
     AND ol.ordered_item LIKE 'X%'
     AND ol.flow_status_code NOT IN ('CLOSED', 'CANCELLED')
ORDER BY order_number;



SELECT   rh.segment1 requisition_number, hrl.location_code requesting_org,
         oh.order_number, org.organization_name fulfillment_org,
         it.segment1 item_number, rl.quantity
 FROM po_requisition_headers_all rh,
         po_requisition_lines_all rl,
         hr_locations hrl,
         mtl_system_items_b it,
         oe_order_headers_all oh,
         oe_order_lines_all ol,
         org_organization_definitions org
WHERE rh.requisition_header_id = rl.requisition_header_id
     AND rl.deliver_to_location_id = hrl.location_id
     AND it.inventory_item_id = rl.item_id
     AND it.organization_id = rl.source_organization_id
     AND rl.requisition_header_id = oh.source_document_id
     AND ol.ship_from_org_id = org.organization_id
     AND oh.header_id = ol.header_id
     AND ol.inventory_item_id = rl.item_id
     AND it.segment1 LIKE 'X%'
     AND rh.authorization_status = 'APPROVED'
     AND ol.flow_status_code NOT IN ('CLOSED', 'CANCELLED')
ORDER BY rh.segment1;

Query to Join Order Management Tables with Oracle Quoting tables

SELECT ooh.header_id
         , ooh.order_number
         , ool.line_Id
         , ool.ship_from_org_id organization_id
         , ool.inventory_item_id
         , ool.ordered_quantity
         , ool.order_quantity_uom
         , ool.line_type_id  xx_line_type_id
         , qtl.order_line_type_id
         , ool.request_date
         , ool.creation_date
         , ool.ship_to_org_id
         , ool.sold_to_org_id
         , ool.subinventory

    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
    WHERE  qte.quote_header_Id = 382
    AND    qtl.quote_header_id = qte.quote_header_id
    AND    shp.quote_header_id = qtl.quote_header_id
    AND    shp.quote_line_id = qtl.quote_line_id
    AND    ooh.orig_sys_document_ref like qte.quote_number||':%'
    AND    ooh.source_document_id = qte.quote_header_id
    AND    ool.header_id = ooh.header_id
    AND    ool.source_document_line_Id = shp.shipment_id ;