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 ;

BLANKET SALES AGREEMENT IN ORACLE APPS API

Create or Replace procedure mutl_blanket_update
is
 
  -- Input variables

   l_hdr_rec             OE_Blanket_PUB.header_rec_type;
   l_hdr_val_rec         OE_Blanket_PUB.Header_Val_Rec_Type;
   l_line_tbl               OE_Blanket_PUB.line_tbl_Type;
   l_line_val_tbl           OE_Blanket_PUB.line_Val_tbl_Type;
   l_line_rec               OE_Blanket_PUB.line_rec_Type;
   l_line_val_rec           OE_Blanket_PUB.line_val_rec_Type;
   l_control_rec            OE_Blanket_PUB.Control_rec_type;
 
 -- Output Variables

   x_header_rec             OE_Blanket_PUB.header_rec_type;
   x_line_tbl                OE_Blanket_PUB.line_tbl_Type;
   x_return_status          VARCHAR2(1000);
   x_msg_count              NUMBER;
   x_msg_data               VARCHAR2(4000);
 
   l_msg_index_out      NUMBER;
   l_error_message      VARCHAR2 (100);

begin
   
    l_line_rec.order_number:='351015';
    l_line_rec.header_id :=5903558;
    l_line_rec.attribute9:='TEST';
 
    OE_Blanket_PUB.Process_Blanket(

p_org_id             => 1

,p_operating_unit     => NULL

,p_api_version_number => 1.0

,x_return_status      => x_return_status

,x_msg_count          => x_msg_count

,x_msg_data           => x_msg_data

,p_header_rec         => l_hdr_rec

,p_header_val_rec     => l_hdr_val_rec

,p_line_tbl           => l_line_tbl

,p_line_val_tbl       => l_line_val_tbl

,p_control_rec        => l_control_rec

,x_header_rec         => x_header_rec

,x_line_tbl           => x_line_tbl
);

IF x_return_status = fnd_api.g_ret_sts_success
   THEN
      DBMS_OUTPUT.put_line ('success');
      DBMS_OUTPUT.put_line ('x_msg_count = ' || x_msg_count);
      DBMS_OUTPUT.put_line ('x_return_status = ' || x_return_status);
   ELSE
    IF x_msg_count > 0
      THEN
         FOR i IN 1 .. x_msg_count
         LOOP
            apps.fnd_msg_pub.get (p_msg_index          => i,
                                  p_encoded            => fnd_api.g_false,
                                  p_data               => x_msg_data,
                                  p_msg_index_out      => l_msg_index_out
                                 );
         END LOOP;

         IF l_error_message IS NULL
         THEN
            l_error_message := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message :=
                       l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;

         DBMS_OUTPUT.put_line ('*****************************************');
         DBMS_OUTPUT.put_line ('API Error: ' || l_error_message);
         DBMS_OUTPUT.put_line ('*****************************************');
      END IF;
   END IF;
END;