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;

1 Responses to “OE_ORDER_PUB.GET_ORDER -- API to collect existing sales order data in R12”

Unknown said...
May 15, 2014 at 5:00 AM

Thanks for the scripts and can you please post same way for LOCK_ORDER
-Sudhakar


Post a Comment

Subscribe to: Post Comments (Atom)

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.


All Rights Reserved Oracle Apps Knowledge Sharing | Home by Team Search