Infolinks

Sunday, 24 June 2012

API is to collect complete details about a sales order in R12 OE_ORDER_PUB.GET_ORDER

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;

No comments:

Post a Comment