OE_ORDER_PUB.PROCESS_ORDER to Apply hold on a sales order

PURPOSE:

This post is to provide a sample script to Apply hold on a 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 );

--THIS IS TO APPLY HOLD AN ORDER HEADER

v_action_request_tbl ( 1 )               := oe_order_pub . g_miss_request_rec ;

v_action_request_tbl ( 1 ). entity_id    := 6012 ;

v_action_request_tbl ( 1 ). entity_code  := OE_GLOBALS . G_ENTITY_HEADER ;

v_action_request_tbl ( 1 ). request_type := OE_GLOBALS . G_APPLY_HOLD ;

v_action_request_tbl ( 1 ). param1       := 50 ;     -- hold_id 

v_action_request_tbl ( 1 ). param2       := 'O' ;    -- indicator that it is an order hold

v_action_request_tbl ( 1 ). param3       := 6012 ;   -- Header ID of the order

DBMS_OUTPUT . PUT_LINE ( 'Starting of API' );

-- CALLING THE API TO APPLY HOLD ON 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 ( 'Applying Hold on Sales Order is Success ' );

ELSE

    DBMS_OUTPUT . put_line ( 'Applying Hold on Sales 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;

  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;

/