OE_ORDER_PUB.GET_ORDER --procedure--API to collect the existing sales 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;