Wednesday, May 9, 2018

How to Use an API to Delete An Order Level Modifier From a Sales Order


How to Use an API to Delete An Order Level Modifier From a Sales Order (Doc ID 2191611.1)


SET SERVEROUTPUT ON;

DECLARE
   /* Initialize the proper Context */
   l_org_id                                     NUMBER := 204; --ENTER VALUE
   l_application_id                             NUMBER := 660;
   l_responsibility_id                          NUMBER := 21623; --ENTER VALUE
   l_user_id                                    NUMBER := 1013419; --ENTER VALUE

   /* Initialize the record to G_MISS to enable defaulting */
   l_header_rec                                 oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;
   l_old_header_rec                             oe_order_pub.header_rec_type;

   l_header_adj_tbl                             oe_order_pub.header_adj_tbl_type;

   l_line_tbl                                   oe_order_pub.line_tbl_type;
   l_old_line_tbl                               oe_order_pub.line_tbl_type;

   l_action_request_tbl                         oe_order_pub.request_tbl_type;

   x_header_rec                                 oe_order_pub.header_rec_type;
   x_header_val_rec                             oe_order_pub.header_val_rec_type;
   x_header_adj_tbl                             oe_order_pub.header_adj_tbl_type;
   x_header_adj_val_tbl                         oe_order_pub.header_adj_val_tbl_type;
   x_header_price_att_tbl                       oe_order_pub.header_price_att_tbl_type;
   x_header_adj_att_tbl                         oe_order_pub.header_adj_att_tbl_type;
   x_header_adj_assoc_tbl                       oe_order_pub.header_adj_assoc_tbl_type;
   x_header_scredit_tbl                         oe_order_pub.header_scredit_tbl_type;
   x_header_scredit_val_tbl                     oe_order_pub.header_scredit_val_tbl_type;
   x_line_tbl                                   oe_order_pub.line_tbl_type;
   x_line_val_tbl                               oe_order_pub.line_val_tbl_type;
   x_line_adj_tbl                               oe_order_pub.line_adj_tbl_type;
   x_line_adj_val_tbl                           oe_order_pub.line_adj_val_tbl_type;
   x_line_price_att_tbl                         oe_order_pub.line_price_att_tbl_type;
   x_line_adj_att_tbl                           oe_order_pub.line_adj_att_tbl_type;
   x_line_adj_assoc_tbl                         oe_order_pub.line_adj_assoc_tbl_type;
   x_line_scredit_tbl                           oe_order_pub.line_scredit_tbl_type;
   x_line_scredit_val_tbl                       oe_order_pub.line_scredit_val_tbl_type;
   x_lot_serial_tbl                             oe_order_pub.lot_serial_tbl_type;
   x_lot_serial_val_tbl                         oe_order_pub.lot_serial_val_tbl_type;
   x_action_request_tbl                         oe_order_pub.request_tbl_type;

   l_return_status                              VARCHAR2(2000);
   l_msg_count                                  NUMBER;
   l_msg_data                                   VARCHAR2(2000);

   l_line_cnt                                   NUMBER := 0;
   l_top_model_line_index                       NUMBER;
   l_link_to_line_index                         NUMBER;
   x_debug_file                                 VARCHAR2(100);
BEGIN
   /* Turn on DBMS Output */
   DBMS_OUTPUT.enable(1000000);

   DBMS_OUTPUT.put_line('Executing Process Order API for R12');

   mo_global.init('ONT');
   mo_global.set_policy_context('S'
                              , l_org_id);
   fnd_global.apps_initialize(l_user_id
                            , l_responsibility_id
                            , l_application_id
                            , NULL);

   oe_msg_pub.initialize;
   oe_debug_pub.initialize;
   oe_debug_pub.setdebuglevel(5);
   x_debug_file                                                                 := oe_debug_pub.set_debug_mode('FILE');
   DBMS_OUTPUT.put_line('START OF NEW DEBUG');

   l_header_rec                                                                 := oe_order_pub.g_miss_header_rec;
   l_header_rec.header_id                                                       := 431776; --ENTER VALUE
   l_header_rec.operation                                                       := oe_globals.g_opr_update;
   l_header_adj_tbl(1)                                                          := oe_order_pub.g_miss_header_adj_rec;
   l_header_adj_tbl(1).operation                                                := oe_globals.g_opr_delete;
   l_header_adj_tbl(1).header_id                                                := 431776; --ENTER VALUE
   l_header_adj_tbl(1).price_adjustment_id                                      := 3994447; --ENTER VALUE

   /* Call the Process Order API with Header Rec and Line Tbl */
   oe_order_pub.process_order(p_api_version_number                      => 1
                            , p_org_id                                  => l_org_id
                            , -- For R12
                             p_init_msg_list                            => fnd_api.g_true
                            , p_return_values                           => fnd_api.g_true
                            , p_action_commit                           => fnd_api.g_true
                            , x_return_status                           => l_return_status
                            , x_msg_count                               => l_msg_count
                            , x_msg_data                                => l_msg_data
                            , p_action_request_tbl                      => l_action_request_tbl
                            , p_header_rec                              => l_header_rec
                            , p_old_header_rec                          => l_old_header_rec
                            , p_header_adj_tbl                          => l_header_adj_tbl
                            , p_line_tbl                                => l_line_tbl
                            , p_old_line_tbl                            => l_old_line_tbl
                            , 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_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_lot_serial_tbl                          => x_lot_serial_tbl
                            , x_lot_serial_val_tbl                      => x_lot_serial_val_tbl
                            , x_action_request_tbl                      => x_action_request_tbl);
   DBMS_OUTPUT.put_line(   'OM Debug file:'
                        || oe_debug_pub.g_dir
                        || '/'
                        || oe_debug_pub.g_file);

   IF l_return_status = 'S'
   THEN
      DBMS_OUTPUT.put_line('CONGRATULATIONS! !');
      DBMS_OUTPUT.put_line('Commit Issued ... !');
      COMMIT;
   ELSE
      DBMS_OUTPUT.put_line('Hard Luck! Error(s) while attempting to delete Order Level Modifier');
   END IF;

   FOR i IN 1 .. oe_msg_pub.g_msg_tbl.COUNT
   LOOP
      DBMS_OUTPUT.put_line(apps.oe_msg_pub.get(i
                                             , 'F'));
   END LOOP;

   oe_msg_pub.g_msg_tbl.delete;

   /* Turn off OM Debug */
   oe_debug_pub.debug_off;
END;
/

How Can One Create a Price Adjustment for More than One Line on a Return Order Using Process Order API

Metalink Ref: How Can One Create a Price Adjustment for More than One Line on a Return Order Using Process Order API? (Doc ID 2113054.1)

SET SERVEROUTPUT ON

DECLARE
   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;
   l_header_adj_tbl                             oe_order_pub.header_adj_tbl_type;
   l_line_adj_tbl                               oe_order_pub.line_adj_tbl_type;
   l_header_scr_tbl                             oe_order_pub.header_scredit_tbl_type;
   l_line_scredit_tbl                           oe_order_pub.line_scredit_tbl_type;
   l_request_rec                                oe_order_pub.request_rec_type;
   l_return_status                              VARCHAR2(1000);
   l_msg_count                                  NUMBER;
   l_msg_data                                   VARCHAR2(1000);
   p_api_version_number                         NUMBER := 1.0;
   p_init_msg_list                              VARCHAR2(10) := fnd_api.g_false;
   p_return_values                              VARCHAR2(10) := fnd_api.g_false;
   p_action_commit                              VARCHAR2(10) := fnd_api.g_false;
   x_return_status                              VARCHAR2(1);
   x_msg_count                                  NUMBER;
   x_msg_data                                   VARCHAR2(100);
   p_header_rec                                 oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;
   p_old_header_rec                             oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;
   p_header_val_rec                             oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;
   p_old_header_val_rec                         oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;
   p_header_adj_tbl                             oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;
   p_old_header_adj_tbl                         oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;
   p_header_adj_val_tbl                         oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;
   p_old_header_adj_val_tbl                     oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;
   p_header_price_att_tbl                       oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;
   p_old_header_price_att_tbl                   oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;
   p_header_adj_att_tbl                         oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;
   p_old_header_adj_att_tbl                     oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;
   p_header_adj_assoc_tbl                       oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;
   p_old_header_adj_assoc_tbl                   oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;
   p_header_scredit_tbl                         oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;
   p_old_header_scredit_tbl                     oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;
   p_header_scredit_val_tbl                     oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;
   p_old_header_scredit_val_tbl                 oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;
   p_line_tbl                                   oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;
   p_old_line_tbl                               oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;
   p_line_val_tbl                               oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;
   p_old_line_val_tbl                           oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;
   p_line_adj_tbl                               oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;
   p_old_line_adj_tbl                           oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;
   p_line_adj_val_tbl                           oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;
   p_old_line_adj_val_tbl                       oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;
   p_line_price_att_tbl                         oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;
   p_old_line_price_att_tbl                     oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;
   p_line_adj_att_tbl                           oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;
   p_old_line_adj_att_tbl                       oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;
   p_line_adj_assoc_tbl                         oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;
   p_old_line_adj_assoc_tbl                     oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;
   p_line_scredit_tbl                           oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;
   p_old_line_scredit_tbl                       oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;
   p_line_scredit_val_tbl                       oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;
   p_old_line_scredit_val_tbl                   oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;
   p_lot_serial_tbl                             oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;
   p_old_lot_serial_tbl                         oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;
   p_lot_serial_val_tbl                         oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;
   p_old_lot_serial_val_tbl                     oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;
   p_action_request_tbl                         oe_order_pub.request_tbl_type := oe_order_pub.g_miss_request_tbl;
   x_header_val_rec                             oe_order_pub.header_val_rec_type;
   x_header_adj_tbl                             oe_order_pub.header_adj_tbl_type;
   x_header_adj_val_tbl                         oe_order_pub.header_adj_val_tbl_type;
   x_header_price_att_tbl                       oe_order_pub.header_price_att_tbl_type;
   x_header_adj_att_tbl                         oe_order_pub.header_adj_att_tbl_type;
   x_header_adj_assoc_tbl                       oe_order_pub.header_adj_assoc_tbl_type;
   x_header_scredit_tbl                         oe_order_pub.header_scredit_tbl_type;
   x_header_scredit_val_tbl                     oe_order_pub.header_scredit_val_tbl_type;
   x_line_val_tbl                               oe_order_pub.line_val_tbl_type;
   x_line_adj_tbl                               oe_order_pub.line_adj_tbl_type;
   x_line_adj_val_tbl                           oe_order_pub.line_adj_val_tbl_type;
   x_line_price_att_tbl                         oe_order_pub.line_price_att_tbl_type;
   x_line_adj_att_tbl                           oe_order_pub.line_adj_att_tbl_type;
   x_line_adj_assoc_tbl                         oe_order_pub.line_adj_assoc_tbl_type;
   x_line_scredit_tbl                           oe_order_pub.line_scredit_tbl_type;
   x_line_scredit_val_tbl                       oe_order_pub.line_scredit_val_tbl_type;
   x_lot_serial_tbl                             oe_order_pub.lot_serial_tbl_type;
   x_lot_serial_val_tbl                         oe_order_pub.lot_serial_val_tbl_type;
   x_action_request_tbl                         oe_order_pub.request_tbl_type;
   x_debug_file                                 VARCHAR2(500);
   l_line_tbl_index                             NUMBER;
   l_msg_index_out                              NUMBER(10);
BEGIN
   DBMS_OUTPUT.enable(1000000);
   fnd_global.apps_initialize(1013419
                            , 21623
                            , 660); -- user_id , resp_id,resp_appl_id
   mo_global.init('ONT'); --Muti-org context setting
   mo_global.set_policy_context('S'
                              , 204); -- org_id
   --
   oe_debug_pub.g_file                                                          := '';
   oe_debug_pub.debug_on;
   oe_debug_pub.initialize;
   x_debug_file                                                                 := oe_debug_pub.set_debug_mode('FILE');
   oe_debug_pub.setdebuglevel(5); -- Use 5 for the most debugging output
   oe_debug_pub.add('START OF NEW DEBUG');
   --
   --This is to UPDATE order line
   l_line_tbl_index                                                             := 1;
   --
   -- Changed attributes Header
   l_header_rec                                                                 := oe_order_pub.g_miss_header_rec;
   l_header_rec.header_id                                                       := 398596; -- pass header_id of the Sales Order
   l_header_rec.operation                                                       := oe_globals.g_opr_update;
   --
   -- Changed attributes Lines
   l_line_tbl(l_line_tbl_index)                                                 := oe_order_pub.g_miss_line_rec;
   l_line_tbl(l_line_tbl_index).operation                                       := oe_globals.g_opr_update;
   l_line_tbl(l_line_tbl_index).header_id                                       := 398596; -- pass header id of sales order
   l_line_tbl(l_line_tbl_index).line_id                                         := 694738; -- pass line_id of the Sales Order Line
   --
   -- Adjustments attributes for Line
   l_line_adj_tbl(1)                                                            := oe_order_pub.g_miss_line_adj_rec;
   l_line_adj_tbl(1).header_id                                                  := 398596; -- header_id of the Sales Order
   l_line_adj_tbl(1).line_id                                                    := 694738; -- line_id of the Sales Order Line
   l_line_adj_tbl(1).list_header_id                                             := 238373; -- list_header_id of adjustment
   l_line_adj_tbl(1).list_line_id                                               := 304906; -- list_line_id of adjustment
   l_line_adj_tbl(1).automatic_flag                                             := 'N';
   l_line_adj_tbl(1).list_line_type_code                                        := 'DIS';
   l_line_adj_tbl(1).arithmetic_operator                                        := 'NEWPRICE';
   l_line_adj_tbl(1).operand                                                    := -50.00;
   l_line_adj_tbl(1).applied_flag                                               := 'Y';
   l_line_adj_tbl(1).updated_flag                                               := 'Y';
   l_line_adj_tbl(1).line_index                                                 := 1;
   --l_line_adj_tbl(1).charge_type_code := <>;
   l_line_adj_tbl(1).modifier_level_code                                        := 'LINE';
   l_line_adj_tbl(1).operation                                                  := oe_globals.g_opr_create;
   --

   l_line_tbl_index                                                             := 2;
   --
   -- Changed attributes Header
   l_header_rec                                                                 := oe_order_pub.g_miss_header_rec;
   l_header_rec.header_id                                                       := 398596; -- pass header_id of the Sales Order
   l_header_rec.operation                                                       := oe_globals.g_opr_update;
   --
   -- Changed attributes Lines
   l_line_tbl(l_line_tbl_index)                                                 := oe_order_pub.g_miss_line_rec;
   l_line_tbl(l_line_tbl_index).operation                                       := oe_globals.g_opr_update;
   l_line_tbl(l_line_tbl_index).header_id                                       := 398596; -- pass header_id of sales order
   l_line_tbl(l_line_tbl_index).line_id                                         := 694739; -- pass line_id of the Sales Order Line
   --
   -- Adjustments attributes for Line
   l_line_adj_tbl(2)                                                            := oe_order_pub.g_miss_line_adj_rec;
   l_line_adj_tbl(2).header_id                                                  := 398596; -- header_id of the Sales Order
   l_line_adj_tbl(2).line_id                                                    := 694739; -- line_id of the Sales Order Line
   l_line_adj_tbl(2).list_header_id                                             := 238373; -- list_header_id of adjustment
   l_line_adj_tbl(2).list_line_id                                               := 304906; -- list_line_id of adjustment
   l_line_adj_tbl(2).automatic_flag                                             := 'N';
   l_line_adj_tbl(2).list_line_type_code                                        := 'DIS';
   l_line_adj_tbl(2).arithmetic_operator                                        := 'NEWPRICE';
   l_line_adj_tbl(2).operand                                                    := 50.00;
   l_line_adj_tbl(2).applied_flag                                               := 'Y';
   l_line_adj_tbl(2).updated_flag                                               := 'Y';
   l_line_adj_tbl(2).line_index                                                 := 1;
   --l_line_adj_tbl(2).charge_type_code := <>;
   l_line_adj_tbl(2).modifier_level_code                                        := 'LINE';
   l_line_adj_tbl(2).operation                                                  := oe_globals.g_opr_create;
   --
   --
   -- CALL TO PROCESS ORDER
   --
   oe_order_pub.process_order(p_api_version_number                      => 1.0
                            , p_init_msg_list                           => fnd_api.g_true
                            , p_return_values                           => fnd_api.g_true
                            , p_action_commit                           => fnd_api.g_false
                            , x_return_status                           => l_return_status
                            , x_msg_count                               => l_msg_count
                            , x_msg_data                                => l_msg_data
                            , p_header_rec                              => l_header_rec
                            , p_line_tbl                                => l_line_tbl
                            , p_line_adj_tbl                            => l_line_adj_tbl
                            , p_action_request_tbl                      => l_action_request_tbl
                            -- OUT PARAMETERS
                            , x_header_rec                              => p_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_line_tbl                                => p_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_lot_serial_tbl                          => x_lot_serial_tbl
                            , x_lot_serial_val_tbl                      => x_lot_serial_val_tbl
                            , x_action_request_tbl                      => p_action_request_tbl);
   DBMS_OUTPUT.put_line(   'OM Debug file: '
                        || oe_debug_pub.g_dir
                        || '/'
                        || oe_debug_pub.g_file);

   -- Retrieve messages
   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_msg_data
                   , p_msg_index_out                           => l_msg_index_out);
      DBMS_OUTPUT.put_line(   'message is: '
                           || l_msg_data);
      DBMS_OUTPUT.put_line(   'message index is: '
                           || l_msg_index_out);
   END LOOP;

   -- Check the return status
   IF l_return_status = fnd_api.g_ret_sts_success
   THEN
      DBMS_OUTPUT.put_line('Unit Selling Price Update Successful');
   ELSE
      DBMS_OUTPUT.put_line('Unit Selling Price Failed');
   END IF;
END;
/

How to Re-Price an Existing Order Using Process Order API

Ref: How to Re-Price an Existing Order Using Process Order API (Doc ID 2212209.1)

SET SERVEROUTPUT ON

DECLARE
   /* Initialize the proper Context */
   l_org_id                                     NUMBER := 204;
   l_application_id                             NUMBER := 660;
   l_responsibility_id                          NUMBER := 21623;
   l_user_id                                    NUMBER := 1013618;

   /* Initialize the record to G_MISS to enable defaulting */
 l_header_rec                                 oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;
 l_old_header_rec            oe_order_pub.header_rec_type;
 l_line_tbl                  oe_order_pub.line_tbl_type;
 l_old_line_tbl              oe_order_pub.line_tbl_type;
 l_action_request_tbl        oe_order_pub.request_tbl_type;

 x_header_rec                oe_order_pub.header_rec_type;
 x_header_val_rec            oe_order_pub.header_val_rec_type;
 x_header_adj_tbl            oe_order_pub.header_adj_tbl_type;
 x_header_adj_val_tbl        oe_order_pub.header_adj_val_tbl_type;
 x_header_price_att_tbl      oe_order_pub.header_price_att_tbl_type;
 x_header_adj_att_tbl        oe_order_pub.header_adj_att_tbl_type;
 x_header_adj_assoc_tbl      oe_order_pub.header_adj_assoc_tbl_type;
 x_header_scredit_tbl        oe_order_pub.header_scredit_tbl_type;
 x_header_scredit_val_tbl  oe_order_pub.header_scredit_val_tbl_type;
 x_line_tbl                  oe_order_pub.line_tbl_type;
 x_line_val_tbl              oe_order_pub.line_val_tbl_type;
 x_line_adj_tbl              oe_order_pub.line_adj_tbl_type;
 x_line_adj_val_tbl          oe_order_pub.line_adj_val_tbl_type;
 x_line_price_att_tbl        oe_order_pub.line_price_att_tbl_type;
 x_line_adj_att_tbl          oe_order_pub.line_adj_att_tbl_type;
 x_line_adj_assoc_tbl        oe_order_pub.line_adj_assoc_tbl_type;
 x_line_scredit_tbl          oe_order_pub.line_scredit_tbl_type;
 x_line_scredit_val_tbl      oe_order_pub.line_scredit_val_tbl_type;
 x_lot_serial_tbl            oe_order_pub.lot_serial_tbl_type;
 x_lot_serial_val_tbl        oe_order_pub.lot_serial_val_tbl_type;
 x_action_request_tbl        oe_order_pub.request_tbl_type;

   l_return_status                              VARCHAR2(2000);
   l_msg_count                                  NUMBER;
   l_msg_data                                   VARCHAR2(2000);
   l_file_val                                   VARCHAR2(1000);
   l_msg_index_out                              NUMBER(10); --Added --this statement

   l_line_cnt                                   NUMBER := 0;
   l_top_model_line_index                       NUMBER;
   l_link_to_line_index                         NUMBER;
BEGIN
   /* Set the appropriate context */
   fnd_global.apps_initialize(l_user_id
                            , l_responsibility_id
                            , l_application_id
                            , NULL);
   mo_global.init('ONT'); --Muti-org context setting
   mo_global.set_policy_context('S'
                              , 204); -- org_id

   /* Turn on OM Debug */
   oe_debug_pub.debug_on;
   oe_debug_pub.initialize;
   oe_debug_pub.setdebuglevel(5);

   l_file_val    := oe_debug_pub.set_debug_mode('FILE');
   DBMS_OUTPUT.put_line(   'Debug log is stored at: '
                        || l_file_val);

   oe_debug_pub.add('START OF NEW DEBUG'); --Added this statement

   /* Populate the required Header Fields */
   l_header_rec.operation  := oe_globals.g_opr_update;
   l_header_rec.header_id:= 262917; -- header_id of order to reprice
 

   /* Populate the Actions Table for Header */
   l_action_request_tbl(1).request_type := oe_globals.g_price_order;
   l_action_request_tbl(1).entity_code:= oe_globals.g_entity_header;
   l_action_request_tbl(1).entity_id:= l_header_rec.header_id;

   /* Call the Process Order API with Header Rec and Line Tbl */
   oe_order_pub.process_order(

   p_api_version_number         => 1.0
 , p_org_id                     => l_org_id     -- For R12
 , p_init_msg_list              => fnd_api.g_true
 , p_return_values              => fnd_api.g_true
 , p_action_commit              => fnd_api.g_true
 , x_return_status              => l_return_status
 , x_msg_count                  => l_msg_count
 , x_msg_data                   => l_msg_data
 , p_action_request_tbl         => l_action_request_tbl
 , p_header_rec                 => l_header_rec
 , p_old_header_rec             => l_old_header_rec
 , p_line_tbl                   => l_line_tbl
 , p_old_line_tbl               => l_old_line_tbl
 , 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_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_lot_serial_tbl             => x_lot_serial_tbl
 , x_lot_serial_val_tbl         => x_lot_serial_val_tbl
 , x_action_request_tbl         => x_action_request_tbl);

   -- Retrieve messages
   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_msg_data
   , p_msg_index_out                           => l_msg_index_out);
      DBMS_OUTPUT.put_line(   'message is: '
                           || l_msg_data);
      DBMS_OUTPUT.put_line(   'message index is: '
                           || l_msg_index_out);
   END LOOP;

   -- Check the return status
   /*added statements above */
   /* Display the status and Order Number if successfully created */
   IF l_return_status = 'S'
   THEN
      DBMS_OUTPUT.put_line(   'Order Number : '
                           || x_header_rec.order_number
                           || '; Header Id : '
                           || x_header_rec.header_id);
   ELSE
      DBMS_OUTPUT.put_line('Error(s) while repricing Order.');
      DBMS_OUTPUT.put_line(   l_msg_count
                           || ';'
                           || l_msg_data);
   END IF;

   /* Display the messages in the message stack */
   FOR i IN 1 .. oe_msg_pub.g_msg_tbl.COUNT
   LOOP
      DBMS_OUTPUT.put_line(apps.oe_msg_pub.get(i
                                             , 'F'));
   END LOOP;

   /* Turn off OM Debug */
   oe_debug_pub.debug_off;
   oe_msg_pub.g_msg_tbl.delete;
END;
/

Tuesday, May 8, 2018

Advanced Pricing : Specific for Factor Lists and it uses the Public API - QP_MODIFIERS_PUB.Process_Modifiers to create Factor Lists


Metalink Ref: How to create Pricing Formulas with Factor Lists using Public API's ? (Doc ID 454089.1)

NOTE:1963675.1 - R12: Advanced Pricing API
How to Set Profile Options for Use With Advanced Pricing (Doc ID 553399.1)

SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

/*$Header: QPPFXMP2.sql 120.0 2005/06/02 00:18:46 appldev noship $*/
--set serveroutput on

/***************************************************************************
   Sample script which inserts a Pricing Formula and 1 price formula line
   of type Factor List(ML) A new Factor List is created in this sample script.
  
   Factor Lists can be created/modified only in the Pricing Formulas Form in
   the Factors window if using the application. A factor list once created
   in one formula may be used in other formulas as well. Any modification to
   a factor list's factors will be reflected in all the formulas using the
   factor list.
  
   However, while using API's to create Factor Lists, we use the Modifier API.
   A factor list, its factors and pricing attributes use the
   same tables as a Modifier List, Modifiers and Pricing Pricing Attributes. 
   A factor List is a modifier list with a list_type_code of 'PML' and a factor
   is a Modifier with a list_line_type_code of 'PMR'.

   A pricing formula header record and  1 price formula line of type factor
   list('ML') are created. Corresponding to the formula line of type factor
   list, 1 Factor List record (Modifier List) and 1 factor record (Modifier)
   are created. In this script, for the factor record, a base pricing attribute
   record and an associated pricing attribute record are created. The
   Modifiers API is used to create the factor list, factor and their
   pricing attributes.

   Please read the Oracle Pricing User Guide (Appendix A & B) to understand
   the flex fields and seed data.

****************************************************************************/

REM FILETYPE NOEXEC
REM dbdrv command
REM dbdrv: none

DECLARE
gpr_return_status                            VARCHAR2(1) := NULL;
gpr_msg_count                                NUMBER := 0;
gpr_msg_data                                 VARCHAR2(2000);
gpr_formula_rec         qp_price_formula_pub.formula_rec_type;
gpr_formula_lines_tbl   qp_price_formula_pub.formula_lines_tbl_type;


ppr_formula_rec         qp_price_formula_pub.formula_rec_type;
ppr_formula_val_rec     qp_price_formula_pub.formula_val_rec_type;
ppr_formula_lines_tbl   qp_price_formula_pub.formula_lines_tbl_type;
ppr_formula_lines_val_tbl qp_price_formula_pub.formula_lines_val_tbl_type;   gpr_modifier_list_rec   qp_modifiers_pub.modifier_list_rec_type;
gpr_modifiers_tbl       qp_modifiers_pub.modifiers_tbl_type;
gpr_pricing_attr_tbl    qp_modifiers_pub.pricing_attr_tbl_type;
ppr_modifier_list_rec   qp_modifiers_pub.modifier_list_rec_type;
ppr_modifier_list_val_rec qp_modifiers_pub.modifier_list_val_rec_type;
ppr_modifiers_tbl       qp_modifiers_pub.modifiers_tbl_type;
ppr_modifiers_val_tbl   qp_modifiers_pub.modifiers_val_tbl_type;
ppr_pricing_attr_tbl    qp_modifiers_pub.pricing_attr_tbl_type;   ppr_pricing_attr_val_tbl qp_modifiers_pub.pricing_attr_val_tbl_type;
ppr_qualifiers_tbl      qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   k                                            NUMBER := 1;
   j                                            NUMBER := 1;
   i                                            NUMBER := 1;
BEGIN
 /* Set the price_formula_id to g_miss_num to Create the Price Formula Record(Header)*/   


gpr_formula_rec.price_formula_id    := fnd_api.g_miss_num;
   gpr_formula_rec.name             := 'Sample2-PF 1025-6';
   gpr_formula_rec.description      := 'Sample Pricing Formula';
   gpr_formula_rec.formula          := '1';
   --Any valid Mathematical Expression including built-in database functions.
   --Every operand must correspond to a step_number in a price formula line.
   gpr_formula_rec.operation        := qp_globals.g_opr_create;


   /* Prior to creating the formula line of type 'Factor List', we first create
      a Factor List, Factors and pricing attributes using the Modifiers API.
     This is because the list_header_id of the Factor_List(Modifier_List) must
     be populated in the price_modifier_list_id column of the Formula Line
     Record  which is a mandatory column when formula_line_type_code is 'ML'. */


   /* Create Factor List (Modifier List) record */
   gpr_modifier_list_rec.list_header_id := fnd_api.g_miss_num;
   gpr_modifier_list_rec.name  := 'SAMPLE FACTOR LIST 6';
   gpr_modifier_list_rec.currency_code   := 'USD';
   gpr_modifier_list_rec.list_type_code  := 'PML';
   --For Factor Lists the Modifier List Type is 'PML'.
   gpr_modifier_list_rec.operation := qp_globals.g_opr_create;


   /* Create Factor (Modifier) record 1 */
   j      := 1;
   gpr_modifiers_tbl(j).list_header_id  := fnd_api.g_miss_num;
   gpr_modifiers_tbl(j).list_line_id    := fnd_api.g_miss_num;
   gpr_modifiers_tbl(j).list_line_type_code := 'PMR';
   --For Factors the Modifier Type is 'PMR'.
   gpr_modifiers_tbl(j).operand         := 0.8;
   --Corresponds to the Adjustment Factor
   gpr_modifiers_tbl(j).arithmetic_operator := 'UNIT_PRICE';
   gpr_modifiers_tbl(j).modifier_level_code := 'NULL';
   gpr_modifiers_tbl(j).operation       := qp_globals.g_opr_create;


--Any number of Pricing Attributes may be created for a Factor. But
--only the first pricing attribute record is considered as the Base
--Pricing Attribute and all subsequent Pricing Attributes for the
--same Factor will be considered Associated Pricing Attributes. All
--pricing attributes for a factor are treated as 'AND' conditions.
--Factors are treated as 'OR' conditions. User does not have to make
--any special changes or considerations as far as setup goes.
/* Create Pricing Attribute 1 (Base Pricing Attribute) for Factor 1.*/
   i := 1;
gpr_pricing_attr_tbl(i).list_line_id:= fnd_api.g_miss_num;
gpr_pricing_attr_tbl(i).pricing_attribute_id:= fnd_api.g_miss_num;
gpr_pricing_attr_tbl(i).modifiers_index  := 1;
--Corresponds to the Factor Number. In this case it is 1.
gpr_pricing_attr_tbl(i).pricing_attribute_context:= 'PRICING ATTRIBUTE';
gpr_pricing_attr_tbl(i).pricing_attribute:= 'PRICING ATTRIBUTE12';
 --Corresponds to the Pricing Attribute 'Insurance Cost'
gpr_pricing_attr_tbl(i).pricing_attr_value_from  := '100';
gpr_pricing_attr_tbl(i).pricing_attr_value_to   := '120';
gpr_pricing_attr_tbl(i).comparison_operator_code := 'BETWEEN';
gpr_pricing_attr_tbl(i).operation := qp_globals.g_opr_create;

/*Create Pricing Attribute 2(Associated Pricing Attribute)for Factor 1.*/
   i  := i + 1;
gpr_pricing_attr_tbl(i).list_line_id := fnd_api.g_miss_num;
gpr_pricing_attr_tbl(i).pricing_attribute_id:= fnd_api.g_miss_num;
gpr_pricing_attr_tbl(i).modifiers_index  := 1;
--Corresponds to the Factor Number. In this case it is 1.
gpr_pricing_attr_tbl(i).pricing_attribute_context:= 'PRICING ATTRIBUTE';
 gpr_pricing_attr_tbl(i).pricing_attribute:= 'PRICING ATTRIBUTE16';
--Corresponds to the Pricing Attribute 'Freight Cost'
 gpr_pricing_attr_tbl(i).pricing_attr_value_from   := '11';
 gpr_pricing_attr_tbl(i).comparison_operator_code := '=';
 gpr_pricing_attr_tbl(i).operation := qp_globals.g_opr_create;


   qp_modifiers_pub.process_modifiers(

   p_api_version_number    => 1.0
 , p_init_msg_list         => fnd_api.g_false
 , p_return_values         => fnd_api.g_false
 , p_commit                => fnd_api.g_false
 , x_return_status         => gpr_return_status
 , x_msg_count             => gpr_msg_count
 , x_msg_data              => gpr_msg_data
 , p_modifier_list_rec     => gpr_modifier_list_rec
 , p_modifiers_tbl         => gpr_modifiers_tbl
 , p_pricing_attr_tbl      => gpr_pricing_attr_tbl
 , x_modifier_list_rec     => ppr_modifier_list_rec
 , x_modifier_list_val_rec => ppr_modifier_list_val_rec

 , x_modifiers_tbl         => ppr_modifiers_tbl
 , x_modifiers_val_tbl     => ppr_modifiers_val_tbl
 , x_qualifiers_tbl        => ppr_qualifiers_tbl
 , x_qualifiers_val_tbl    => ppr_qualifiers_val_tbl
 , x_pricing_attr_tbl      => ppr_pricing_attr_tbl
 , x_pricing_attr_val_tbl  => ppr_pricing_attr_val_tbl);

   IF gpr_return_status <> fnd_api.g_ret_sts_success
   THEN
      RAISE fnd_api.g_exc_unexpected_error;
   END IF;


   /* Create price formula line 1 of type 'Factor List'(ML) */
   k        := 1;
gpr_formula_lines_tbl(k).price_formula_id  := fnd_api.g_miss_num;
gpr_formula_lines_tbl(k).price_formula_line_id:= fnd_api.g_miss_num;
gpr_formula_lines_tbl(k).formula_line_type_code := 'ML';

gpr_formula_lines_tbl(k).step_number  := 1;
gpr_formula_lines_tbl(k).price_modifier_list_id                             := ppr_modifier_list_rec.list_header_id;
   -- Corresponds to the list_header_id of the new Factor List
   -- created above.
   gpr_formula_lines_tbl(k).operation := qp_globals.g_opr_create;



   qp_price_formula_pub.process_price_formula(

   p_api_version_number      => 1.0
 , p_init_msg_list           => fnd_api.g_false
 , p_return_values           => fnd_api.g_false
 , p_commit                  => fnd_api.g_false
 , x_return_status           => gpr_return_status
 , x_msg_count               => gpr_msg_count
 , x_msg_data                => gpr_msg_data
 , p_formula_rec             => gpr_formula_rec
 , p_formula_lines_tbl       => gpr_formula_lines_tbl
 , x_formula_rec             => ppr_formula_rec
 , x_formula_val_rec         => ppr_formula_val_rec
 , x_formula_lines_tbl       => ppr_formula_lines_tbl
 , x_formula_lines_val_tbl   => ppr_formula_lines_val_tbl);

   IF gpr_return_status <> fnd_api.g_ret_sts_success
   THEN
      RAISE fnd_api.g_exc_unexpected_error;
   END IF;
EXCEPTION
   WHEN fnd_api.g_exc_error
   THEN
      gpr_return_status := fnd_api.g_ret_sts_error;
-- Get message count and data
-- dbms_output.put_line('err msg 1 is : ' || gpr_msg_data);

      ROLLBACK;
   WHEN fnd_api.g_exc_unexpected_error
   THEN
      gpr_return_status  := fnd_api.g_ret_sts_unexp_error;

      --dbms_output.put_line(' msg count 2 is : ' || gpr_msg_count);

      FOR k IN 1 .. gpr_msg_count
      LOOP
         gpr_msg_data  := oe_msg_pub.get(p_msg_index  => k
                                       , p_encoded    => 'F');
         /*
          oe_msg_pub.Count_And_Get
            (   p_count                       => gpr_msg_count
               ,p_data                        => gpr_msg_data
                  );
           */
-- Get message count and data
-- dbms_output.put_line('err msg ' || k ||'is:  ' || gpr_msg_data);
         NULL;
      END LOOP;

      ROLLBACK;
   WHEN OTHERS
   THEN
     gpr_return_status       := fnd_api.g_ret_sts_unexp_error;      -- Get message count and data      

-- dbms_output.put_line('err msg 3 is : ' || gpr_msg_data);

      ROLLBACK;
END;
/

COMMIT;
EXIT;