Tuesday, August 24, 2010

OE_ORDER_PUB.GET_ORDER -- API to collect existing sales order data in R12

In this post, I have discussed an API named OE_ORDER_PUB.GET_ORDER. The purpose of this API is to collect complete details about a sales order in R12.

Test Environment: R12.1.1

Script:

SET serveroutput ON;
DECLARE
  P_API_VERSION_NUMBER     NUMBER;
  P_INIT_MSG_LIST          VARCHAR2(200);
  P_RETURN_VALUES          VARCHAR2(200);
  X_RETURN_STATUS          VARCHAR2(200);
  X_MSG_COUNT              NUMBER;
  X_MSG_DATA               VARCHAR2(200);
  P_HEADER_ID              NUMBER;
  P_HEADER                 VARCHAR2(200);
  P_ORG_ID                 NUMBER;
  P_OPERATING_UNIT         VARCHAR2(200);
  X_HEADER_REC             APPS.OE_ORDER_PUB.HEADER_REC_TYPE;
  X_HEADER_VAL_REC         APPS.OE_ORDER_PUB.HEADER_VAL_REC_TYPE;
  X_HEADER_ADJ_TBL         APPS.OE_ORDER_PUB.HEADER_ADJ_TBL_TYPE;
  X_HEADER_ADJ_VAL_TBL     APPS.OE_ORDER_PUB.HEADER_ADJ_VAL_TBL_TYPE;
  X_HEADER_PRICE_ATT_TBL   APPS.OE_ORDER_PUB.HEADER_PRICE_ATT_TBL_TYPE;
  X_HEADER_ADJ_ATT_TBL     APPS.OE_ORDER_PUB.HEADER_ADJ_ATT_TBL_TYPE;
  X_HEADER_ADJ_ASSOC_TBL   APPS.OE_ORDER_PUB.HEADER_ADJ_ASSOC_TBL_TYPE;
  X_HEADER_SCREDIT_TBL     APPS.OE_ORDER_PUB.HEADER_SCREDIT_TBL_TYPE;
  X_HEADER_SCREDIT_VAL_TBL APPS.OE_ORDER_PUB.HEADER_SCREDIT_VAL_TBL_TYPE;
  X_HEADER_PAYMENT_TBL     APPS.OE_ORDER_PUB.HEADER_PAYMENT_TBL_TYPE;
  X_HEADER_PAYMENT_VAL_TBL APPS.OE_ORDER_PUB.HEADER_PAYMENT_VAL_TBL_TYPE;
  X_LINE_TBL               APPS.OE_ORDER_PUB.LINE_TBL_TYPE;
  X_LINE_VAL_TBL           APPS.OE_ORDER_PUB.LINE_VAL_TBL_TYPE;
  X_LINE_ADJ_TBL           APPS.OE_ORDER_PUB.LINE_ADJ_TBL_TYPE;
  X_LINE_ADJ_VAL_TBL       APPS.OE_ORDER_PUB.LINE_ADJ_VAL_TBL_TYPE;
  X_LINE_PRICE_ATT_TBL     APPS.OE_ORDER_PUB.LINE_PRICE_ATT_TBL_TYPE;
  X_LINE_ADJ_ATT_TBL       APPS.OE_ORDER_PUB.LINE_ADJ_ATT_TBL_TYPE;
  X_LINE_ADJ_ASSOC_TBL     APPS.OE_ORDER_PUB.LINE_ADJ_ASSOC_TBL_TYPE;
  X_LINE_SCREDIT_TBL       APPS.OE_ORDER_PUB.LINE_SCREDIT_TBL_TYPE;
  X_LINE_SCREDIT_VAL_TBL   APPS.OE_ORDER_PUB.LINE_SCREDIT_VAL_TBL_TYPE;
  X_LINE_PAYMENT_TBL       APPS.OE_ORDER_PUB.LINE_PAYMENT_TBL_TYPE;
  X_LINE_PAYMENT_VAL_TBL   APPS.OE_ORDER_PUB.LINE_PAYMENT_VAL_TBL_TYPE;
  X_LOT_SERIAL_TBL         APPS.OE_ORDER_PUB.LOT_SERIAL_TBL_TYPE;
  X_LOT_SERIAL_VAL_TBL     APPS.OE_ORDER_PUB.LOT_SERIAL_VAL_TBL_TYPE;
 
BEGIN
-- Initialize the environment --

fnd_global.apps_initialize ( user_id      => 2585
                            ,resp_id      => 50864
                            ,resp_appl_id => 660);
mo_global.set_policy_context('S',83);
mo_global.init('ONT');

  -- Api Parameters --

  P_API_VERSION_NUMBER := 1;
  P_INIT_MSG_LIST := FND_API.G_FALSE;
  P_RETURN_VALUES := NULL;
  P_HEADER_ID := 6220;
  P_HEADER := NULL;
  P_ORG_ID := 83;
  P_OPERATING_UNIT := NULL;

  OE_ORDER_PUB.GET_ORDER(
    P_API_VERSION_NUMBER     => P_API_VERSION_NUMBER,
    P_INIT_MSG_LIST          => P_INIT_MSG_LIST,
    P_RETURN_VALUES          => P_RETURN_VALUES,
    X_RETURN_STATUS          => X_RETURN_STATUS,
    X_MSG_COUNT              => X_MSG_COUNT,
    X_MSG_DATA               => X_MSG_DATA,
    P_HEADER_ID              => P_HEADER_ID,
    P_ORG_ID                 => P_ORG_ID,
    P_OPERATING_UNIT         => P_OPERATING_UNIT,
    X_HEADER_REC             => X_HEADER_REC,
    X_HEADER_VAL_REC         => X_HEADER_VAL_REC,
    X_HEADER_ADJ_TBL         => X_HEADER_ADJ_TBL,
    X_HEADER_ADJ_VAL_TBL     => X_HEADER_ADJ_VAL_TBL,
    X_HEADER_PRICE_ATT_TBL   => X_HEADER_PRICE_ATT_TBL,
    X_HEADER_ADJ_ATT_TBL     => X_HEADER_ADJ_ATT_TBL,
    X_HEADER_ADJ_ASSOC_TBL   => X_HEADER_ADJ_ASSOC_TBL,
    X_HEADER_SCREDIT_TBL     => X_HEADER_SCREDIT_TBL,
    X_HEADER_SCREDIT_VAL_TBL => X_HEADER_SCREDIT_VAL_TBL,
    X_HEADER_PAYMENT_TBL     => X_HEADER_PAYMENT_TBL,
    X_HEADER_PAYMENT_VAL_TBL => X_HEADER_PAYMENT_VAL_TBL,
    X_LINE_TBL               => X_LINE_TBL,
    X_LINE_VAL_TBL           => X_LINE_VAL_TBL,
    X_LINE_ADJ_TBL           => X_LINE_ADJ_TBL,
    X_LINE_ADJ_VAL_TBL       => X_LINE_ADJ_VAL_TBL,
    X_LINE_PRICE_ATT_TBL     => X_LINE_PRICE_ATT_TBL,
    X_LINE_ADJ_ATT_TBL       => X_LINE_ADJ_ATT_TBL,
    X_LINE_ADJ_ASSOC_TBL     => X_LINE_ADJ_ASSOC_TBL,
    X_LINE_SCREDIT_TBL       => X_LINE_SCREDIT_TBL,
    X_LINE_SCREDIT_VAL_TBL   => X_LINE_SCREDIT_VAL_TBL,
    X_LINE_PAYMENT_TBL       => X_LINE_PAYMENT_TBL,
    X_LINE_PAYMENT_VAL_TBL   => X_LINE_PAYMENT_VAL_TBL,
    X_LOT_SERIAL_TBL         => X_LOT_SERIAL_TBL,
    X_LOT_SERIAL_VAL_TBL     => X_LOT_SERIAL_VAL_TBL
  );
  DBMS_OUTPUT.PUT_LINE('X_RETURN_STATUS = ' || X_RETURN_STATUS);
  DBMS_OUTPUT.PUT_LINE('X_MSG_COUNT = ' || X_MSG_COUNT);
  DBMS_OUTPUT.PUT_LINE('X_MSG_DATA = ' || X_MSG_DATA);
  DBMS_OUTPUT.PUT_LINE('Order number = ' || X_HEADER_REC.order_number);
  DBMS_OUTPUT.PUT_LINE('Ordered Quantity = ' || X_LINE_TBL(1).ordered_quantity);
  DBMS_OUTPUT.PUT_LINE('Shipped Quantity = ' || X_LINE_TBL(1).shipped_quantity);
 
 IF x_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Sucessfull : ');
 ELSE
    DBMS_OUTPUT.put_line ('Failed with the error :');
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
        x_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
        dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
 END IF;
 
 
END;

Tuesday, August 24, 2010 by Team search · 1

Script to get the Quantity reserved against an sales order line in oracle apps R12 (INV_RESERVATION_PUB.QUERY_RESERVATION_OM_HDR_LINE)

In this post, I have discussed an API named INV_RESERVATION_PUB.QUERY_RESERVATION_OM_HDR_LINE. The purpose of this API is to get the quantity reserved against the odrer line.

Script:

SET serveroutput ON;

DECLARE

l_open_quantity         NUMBER := 0;
l_reserved_quantity     NUMBER := 0;
l_mtl_sales_order_id    NUMBER;
l_return_status         VARCHAR2(1);
l_msg_count             NUMBER;
l_msg_data              VARCHAR2(240);

l_rsv_rec               inv_reservation_global.mtl_reservation_rec_type;
l_rsv_tbl               inv_reservation_global.mtl_reservation_tbl_type;
l_count                 NUMBER;
l_x_error_code          NUMBER;
l_lock_records          VARCHAR2(1);
l_sort_by_req_date      NUMBER;
l_converted_qty         NUMBER;
l_inventory_item_id     NUMBER;
l_order_quantity_uom    VARCHAR2(30);

P_HEADER_ID             NUMBER := 6220;
P_LINE_ID               NUMBER := 4850;

BEGIN

   l_mtl_sales_order_id := OE_HEADER_UTIL.Get_Mtl_Sales_Order_Id
                                      (p_header_id=>p_header_id);
                         
   l_rsv_rec.demand_source_header_id     := l_mtl_sales_order_id;
   l_rsv_rec.demand_source_line_id       := p_line_id;
   l_rsv_rec.organization_id             := NULL;                         

   INV_RESERVATION_PUB.QUERY_RESERVATION_OM_HDR_LINE
   (  p_api_version_number        => 1.0
    , p_init_msg_lst              => fnd_api.g_true
    , x_return_status             => l_return_status
    , x_msg_count                 => l_msg_count
    , x_msg_data                  => l_msg_data
    , p_query_input               => l_rsv_rec
    , x_mtl_reservation_tbl       => l_rsv_tbl
    , x_mtl_reservation_tbl_count => l_count
    , x_error_code                => l_x_error_code
    , p_lock_records              => l_lock_records
    , p_sort_by_req_date          => l_sort_by_req_date
   );
     
   BEGIN

        SELECT order_quantity_uom, inventory_item_id
        INTO   l_order_quantity_uom, l_inventory_item_id
        FROM   oe_order_lines_all
        WHERE  line_id = p_line_id;

   EXCEPTION
    WHEN OTHERS THEN

     l_order_quantity_uom := NULL;
   END;
  
   FOR I IN 1..l_rsv_tbl.COUNT LOOP

      l_rsv_rec := l_rsv_tbl(I);

      IF NVL(l_order_quantity_uom,l_rsv_rec.reservation_uom_code)
                                <> l_rsv_rec.reservation_uom_code THEN

        l_converted_qty :=
        Oe_Order_Misc_Util.convert_uom( l_inventory_item_id,
                                        l_rsv_rec.reservation_uom_code,
                                        l_order_quantity_uom,
                                        l_rsv_rec.reservation_quantity);

        l_reserved_quantity := l_reserved_quantity + l_converted_qty;
      ELSE

      
        l_reserved_quantity := l_reserved_quantity + l_rsv_rec.reservation_quantity;
      END IF;

   END LOOP; 
  
   DBMS_OUTPUT.PUT_LINE( 'RESERVED QUANTITY :' ||l_reserved_quantity);

EXCEPTION

WHEN NO_DATA_FOUND THEN
     NULL; 
END; 

by Team search · 0

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.