已存在销售订单上插入销售订单行



在已存在销售订单上插入销售订单行

--This is to add a line to an existing order

DECLARE

  l_api_version_number   NUMBER   :=   1   ;

  l_return_status        VARCHAR2 ( 2000   );

  l_msg_count            NUMBER ;

  l_msg_data             VARCHAR2 ( 2000   );

    /*****************INPUT VARIABLES FOR PROCESS_ORDER API*************************/

  l_header_rec         oe_order_pub.header_rec_type;

  l_line_tbl           oe_order_pub.line_tbl_type;

  l_action_request_tbl oe_order_pub.request_tbl_type;

    /*****************OUT VARIABLES FOR PROCESS_ORDER API***************************/

  l_header_rec_out             oe_order_pub.header_rec_type;

  l_header_val_rec_out         oe_order_pub.header_val_rec_type;

  l_header_adj_tbl_out         oe_order_pub.header_adj_tbl_type;

  l_header_adj_val_tbl_out     oe_order_pub.header_adj_val_tbl_type;

  l_header_price_att_tbl_out   oe_order_pub.header_price_att_tbl_type;

  l_header_adj_att_tbl_out     oe_order_pub.header_adj_att_tbl_type;

  l_header_adj_assoc_tbl_out   oe_order_pub.header_adj_assoc_tbl_type;

  l_header_scredit_tbl_out     oe_order_pub.header_scredit_tbl_type;

  l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;

  l_line_tbl_out               oe_order_pub.line_tbl_type;

  l_line_val_tbl_out           oe_order_pub.line_val_tbl_type;

  l_line_adj_tbl_out           oe_order_pub.line_adj_tbl_type;

  l_line_adj_val_tbl_out       oe_order_pub.line_adj_val_tbl_type;

  l_line_price_att_tbl_out     oe_order_pub.line_price_att_tbl_type;

  l_line_adj_att_tbl_out       oe_order_pub.line_adj_att_tbl_type;

  l_line_adj_assoc_tbl_out     oe_order_pub.line_adj_assoc_tbl_type;

  l_line_scredit_tbl_out       oe_order_pub.line_scredit_tbl_type;

  l_line_scredit_val_tbl_out   oe_order_pub.line_scredit_val_tbl_type;

  l_lot_serial_tbl_out         oe_order_pub.lot_serial_tbl_type;

  l_lot_serial_val_tbl_out     oe_order_pub.lot_serial_val_tbl_type;

  l_action_request_tbl_out     oe_order_pub.request_tbl_type;

  l_msg_index    NUMBER ;

  l_data         VARCHAR2 ( 2000   );

  l_loop_count   NUMBER ;

  l_debug_file   VARCHAR2 ( 200   );

BEGIN

    /*****************INITIALIZE DEBUG INFO*************************************/

  l_debug_file := oe_debug_pub.set_debug_mode(   'FILE' );

  oe_debug_pub.initialize;

  oe_debug_pub.setdebuglevel(   5 );

  oe_msg_pub.initialize;

    /*****************INITIALIZE ENVIRONMENT*************************************/

  fnd_global.apps_initialize(   2250 ,   50786   ,   20003 );   -- pass in user_id, responsibility_id, and application_id

    /*****************INITIALIZE HEADER RECORD******************************/

  l_header_rec := oe_order_pub.g_miss_header_rec;

    /*****************POPULATE REQUIRED ATTRIBUTES **********************************/

    --l_header_rec.operation := oe_globals.g_opr_create;

    --l_header_rec.order_type_id := ;

    --l_header_rec.sold_to_org_id := ;

    --l_header_rec.price_list_id := ;

    /*****************INITIALIZE ACTION REQUEST RECORD*************************************/

  l_action_request_tbl(   1 ) := oe_order_pub.g_miss_request_rec;

  l_action_request_tbl(   1 ).request_type := oe_globals.g_book_order;

    /*****************INITIALIZE LINE RECORD********************************/

    --Adding a new line to an existing order

    --This is to add a line to an existing order

    -- Initialize record to missing

  l_line_tbl( 1 ) := oe_order_pub.g_miss_line_rec;

    -- Line attributes

    -- Required Line Attributes

  l_line_tbl( 1 ).header_id :=   101452 ;

  l_line_tbl( 1 ).ordered_quantity :=   0.5 ;

  l_line_tbl( 1 ).inventory_item_id :=   1019 ;

    -- Other line attributes

  l_line_tbl( 1 ).ship_from_org_id :=   92 ;

    --l_line_tbl(1).subinventory := &subinventory code;

    --Operation set to Create

  l_line_tbl( 1 ).operation := oe_globals.g_opr_create;

    /*****************CALLTO PROCESS ORDER API*********************************/

  oe_order_pub.process_order(p_api_version_number => l_api_version_number,

                             p_header_rec         => l_header_rec,

                             p_line_tbl           => l_line_tbl,

                             p_action_request_tbl => l_action_request_tbl,

                               -- OUT variables

                             x_header_rec             => l_header_rec_out,

                             x_header_val_rec         => l_header_val_rec_out,

                             x_header_adj_tbl         => l_header_adj_tbl_out,

                             x_header_adj_val_tbl     => l_header_adj_val_tbl_out,

                             x_header_price_att_tbl   => l_header_price_att_tbl_out,

                             x_header_adj_att_tbl     => l_header_adj_att_tbl_out,

                             x_header_adj_assoc_tbl   => l_header_adj_assoc_tbl_out,

                             x_header_scredit_tbl     => l_header_scredit_tbl_out,

                             x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,

                             x_line_tbl               => l_line_tbl_out,

                             x_line_val_tbl           => l_line_val_tbl_out,

                             x_line_adj_tbl           => l_line_adj_tbl_out,

                             x_line_adj_val_tbl       => l_line_adj_val_tbl_out,

                             x_line_price_att_tbl     => l_line_price_att_tbl_out,

                             x_line_adj_att_tbl       => l_line_adj_att_tbl_out,

                             x_line_adj_assoc_tbl     => l_line_adj_assoc_tbl_out,

                             x_line_scredit_tbl       => l_line_scredit_tbl_out,

                             x_line_scredit_val_tbl   => l_line_scredit_val_tbl_out,

                             x_lot_serial_tbl         => l_lot_serial_tbl_out,

                             x_lot_serial_val_tbl     => l_lot_serial_val_tbl_out,

                             x_action_request_tbl     => l_action_request_tbl_out,

                             x_return_status          => l_return_status,

                             x_msg_count              => l_msg_count,

                             x_msg_data               => l_msg_data);

    /*****************CHECK RETURN STATUS***********************************/

    IF   l_return_status = fnd_api.g_ret_sts_success   THEN

    dbms_output.put_line(   'success' );

      COMMIT ;

    ELSE

    dbms_output.put_line(   'failure' );

      ROLLBACK ;

    END   IF ;

    /*****************DISPLAY RETURN STATUS FLAGS******************************

 

 

  DBMS_OUTPUT.PUT_LINE('process ORDER ret status IS: ' || l_return_status);

  DBMS_OUTPUT.PUT_LINE('process ORDER msg data IS: ' || l_msg_data);

  DBMS_OUTPUT.PUT_LINE('process ORDER msg COUNT IS: ' || l_msg_count);

  DBMS_OUTPUT.PUT_LINE('header.order_number IS: ' || to_char(l_header_rec_out.order_number));

  DBMS_OUTPUT.PUT_LINE('header.return_status IS: ' || l_header_rec_out.return_status);

  DBMS_OUTPUT.PUT_LINE('header.booked_flag IS: ' || l_header_rec_out.booked_flag);

  DBMS_OUTPUT.PUT_LINE('header.header_id IS: ' || l_header_rec_out.header_id);

  DBMS_OUTPUT.PUT_LINE('header.order_source_id IS: ' || l_header_rec_out.order_source_id);

  DBMS_OUTPUT.PUT_LINE('header.flow_status_code IS: ' || l_header_rec_out.flow_status_code);

 

  /*****************DISPLAY ERROR MSGS*************************************/

    FOR   i   IN   1   .. l_msg_count   LOOP

    oe_msg_pub.get(p_msg_index     => i,

                   p_encoded       => fnd_api.g_false,

                   p_data          => l_data,

                   p_msg_index_out => l_msg_index);

 

    dbms_output.put_line(   'message is: '   || l_data);

    dbms_output.put_line(   'message index is: '   || l_msg_index);

 

    END   LOOP ;

  dbms_output.put_line(   'Debug = '   || oe_debug_pub.g_debug);

  dbms_output.put_line(   'Debug Level = '   ||

                       to_char(oe_debug_pub.g_debug_level));

  dbms_output.put_line(   'Debug File = '   || oe_debug_pub.g_dir ||   '/'   ||

                       oe_debug_pub.g_file);

  dbms_output.put_line( '****************************************************'   );

  oe_debug_pub.debug_off;

END ;