Wednesday, June 30, 2010

API to Cancel an Order Line (OE_ORDER_PUB.PROCESS_ORDER)

PURPOSE:

This post is to provide a sample script to cancel a line in an existing sales order using an API OE_ORDER_PUB.PROCESS_ORDER.

TEST INSTANCE:  R12.1.1

SCRIPT:


SET SERVEROUTPUT ON;
DECLARE
v_api_version_number           NUMBER  := 1;
v_return_status                VARCHAR2 (2000);
v_msg_count                    NUMBER;
v_msg_data                     VARCHAR2 (2000);

-- IN Variables --
v_header_rec                   oe_order_pub.header_rec_type;
v_line_tbl                     oe_order_pub.line_tbl_type;
v_action_request_tbl           oe_order_pub.request_tbl_type;
v_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;

-- OUT Variables --
v_header_rec_out               oe_order_pub.header_rec_type;
v_header_val_rec_out           oe_order_pub.header_val_rec_type;
v_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
v_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
v_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
v_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
v_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
v_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
v_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
v_line_tbl_out                 oe_order_pub.line_tbl_type;
v_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
v_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
v_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
v_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
v_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
v_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
v_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
v_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
v_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
v_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
v_action_request_tbl_out       oe_order_pub.request_tbl_type;

BEGIN

DBMS_OUTPUT.PUT_LINE('Starting of script');

-- Setting the Enviroment --

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

v_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;

-- Cancel a Line Record --
v_line_tbl (1)                      := oe_order_pub.g_miss_line_rec;
v_line_tbl (1).operation            := OE_GLOBALS.G_OPR_UPDATE;
v_line_tbl (1).header_id            := 6006;
v_line_tbl (1).line_id              := 4697;
v_line_tbl (1).ordered_quantity     := 0;
v_line_tbl (1).cancelled_flag       := 'Y';
v_line_tbl (1).change_reason        := 'Not Provided';

DBMS_OUTPUT.PUT_LINE('Starting of API');

-- Calling the API to cancel a line from an Existing Order --

OE_ORDER_PUB.PROCESS_ORDER (
p_api_version_number            => v_api_version_number
, p_header_rec                  => v_header_rec
, p_line_tbl                    => v_line_tbl
, p_action_request_tbl          => v_action_request_tbl
, p_line_adj_tbl                => v_line_adj_tbl
-- OUT variables
, x_header_rec                  => v_header_rec_out
, x_header_val_rec              => v_header_val_rec_out
, x_header_adj_tbl              => v_header_adj_tbl_out
, x_header_adj_val_tbl          => v_header_adj_val_tbl_out
, x_header_price_att_tbl        => v_header_price_att_tbl_out
, x_header_adj_att_tbl          => v_header_adj_att_tbl_out
, x_header_adj_assoc_tbl        => v_header_adj_assoc_tbl_out
, x_header_scredit_tbl          => v_header_scredit_tbl_out
, x_header_scredit_val_tbl      => v_header_scredit_val_tbl_out
, x_line_tbl                    => v_line_tbl_out
, x_line_val_tbl                => v_line_val_tbl_out
, x_line_adj_tbl                => v_line_adj_tbl_out
, x_line_adj_val_tbl            => v_line_adj_val_tbl_out
, x_line_price_att_tbl          => v_line_price_att_tbl_out
, x_line_adj_att_tbl            => v_line_adj_att_tbl_out
, x_line_adj_assoc_tbl          => v_line_adj_assoc_tbl_out
, x_line_scredit_tbl            => v_line_scredit_tbl_out
, x_line_scredit_val_tbl        => v_line_scredit_val_tbl_out
, x_lot_serial_tbl              => v_lot_serial_tbl_out
, x_lot_serial_val_tbl          => v_lot_serial_val_tbl_out
, x_action_request_tbl          => v_action_request_tbl_out
, x_return_status               => v_return_status
, x_msg_count                   => v_msg_count
, x_msg_data                    => v_msg_data
);

DBMS_OUTPUT.PUT_LINE('Completion of API');


IF v_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Line Cancelation in Existing Order is Success ');
ELSE
    DBMS_OUTPUT.put_line ('Line Cancelation in Existing Order failed:'||v_msg_data);
    ROLLBACK;
    FOR i IN 1 .. v_msg_count
    LOOP
      v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| v_msg_data);
    END LOOP;
END IF;
END;
/

5 Responses to “API to Cancel an Order Line (OE_ORDER_PUB.PROCESS_ORDER)”

Anonymous said...
February 1, 2011 at 2:05 AM

It's working...
Thanks for saving our time.


Anonymous said...
July 25, 2013 at 12:45 PM

Perfect script


Ajay Jain said...
July 25, 2013 at 12:49 PM

As Shell Script : Some add on to above written script

APPS_PASS=apps/xxxxx
USER_NAME='AJAIN'
OUTFILE=/tmp/1.txt

rm -f $OUTFILE


#### STEP #1 CANCEL LINE ORDER
sqlplus -s ${APPS_PASS} >> ${OUTFILE} < v_api_version_number
,p_header_rec => v_header_rec
,p_line_tbl => v_line_tbl
,p_action_request_tbl => v_action_request_tbl
,p_line_adj_tbl => v_line_adj_tbl
-- OUT variables
,x_header_rec => v_header_rec_out
,x_header_val_rec => v_header_val_rec_out
,x_header_adj_tbl => v_header_adj_tbl_out
,x_header_adj_val_tbl => v_header_adj_val_tbl_out
,x_header_price_att_tbl => v_header_price_att_tbl_out
,x_header_adj_att_tbl => v_header_adj_att_tbl_out
,x_header_adj_assoc_tbl => v_header_adj_assoc_tbl_out
,x_header_scredit_tbl => v_header_scredit_tbl_out
,x_header_scredit_val_tbl => v_header_scredit_val_tbl_out
,x_line_tbl => v_line_tbl_out
,x_line_val_tbl => v_line_val_tbl_out
,x_line_adj_tbl => v_line_adj_tbl_out
,x_line_adj_val_tbl => v_line_adj_val_tbl_out
,x_line_price_att_tbl => v_line_price_att_tbl_out
,x_line_adj_att_tbl => v_line_adj_att_tbl_out
,x_line_adj_assoc_tbl => v_line_adj_assoc_tbl_out
,x_line_scredit_tbl => v_line_scredit_tbl_out
,x_line_scredit_val_tbl => v_line_scredit_val_tbl_out
,x_lot_serial_tbl => v_lot_serial_tbl_out
,x_lot_serial_val_tbl => v_lot_serial_val_tbl_out
,x_action_request_tbl => v_action_request_tbl_out
,x_return_status => v_return_status
, x_msg_count => v_msg_count
, x_msg_data => v_msg_data
);

DBMS_OUTPUT.PUT_LINE('Completion of API');

IF v_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('Line Cancelation in Existing Order is Success ');
ELSE
DBMS_OUTPUT.put_line ('Line Cancelation in Existing Order failed:'||v_msg_data);
ROLLBACK;
FOR i IN 1 .. v_msg_count
LOOP
v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| v_msg_data);
END LOOP;
END IF;

END;
/
EOF


Anonymous said...
September 17, 2013 at 9:34 AM

tks a lot....


Team search said...
January 12, 2014 at 8:32 AM

Hi Ajay Jain, Thank you for sharing your knowledge with us.


Post a Comment

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.