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;
/

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;

Advanced Pricing : Public API to create all 7 different types of Formula lines QP_PRICE_FORMULA_PUB.Process_Price_Formula

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


 
SET DOC OFF
SET VERIFY OFF

/* $Header: QPPFXMP1.sql 120.0 2005/06/02 01:07:59 appldev noship $*/
--set serveroutput on

/***************************************************************************
   Sample script which inserts a Pricing Formula with 7 price formula lines,
   each of a different type. This is to demonstrate the 7 formula line types
   that are supported in Pricing Formulas. They are Price List Line (PLL),
   Function (FUNC), List Price(LP), Numeric Constant (NUM), Pricing Attribute
   (PRA), Factor List(ML) and Modifier Value(MV). Basic Pricing only supports 3
   formula line types - Pricing Attribute(PRA), Numeric Constant(NUM) and
   Factor List(ML). All 7 are supported in Advanced Pricing.

   A pricing formula header record and 7 price formula lines are created.
   For the formula line of type 'Factor List', the list_header_id of an already
   existing Factor List is used in this sample script.
  
   This script must be modified by the user such that the column
      gpr_formula_lines_tbl(K).price_modifier_list_id
   is populated with a valid list_header_id of an existing Factor List and
   the column
      gpr_formula_lines_tbl(K).price_list_line_id
   is populated with a valid list_line_id of an existing Price List Line from
   the instance where this script is run.

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

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

REM FILETYPE NOEXEC
REM dbdrv command
REM dbdrv: none

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

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_val_rec                          qp_price_formula_pub.formula_val_rec_type;
   gpr_formula_lines_tbl                        qp_price_formula_pub.formula_lines_tbl_type;
   gpr_formula_lines_val_tbl                    qp_price_formula_pub.formula_lines_val_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;

   k                                            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               := 'Sample1-PF 1025-1';
   gpr_formula_rec.description        := 'Sample Pricing Formula';
   gpr_formula_rec.formula            := 'SQRT(1)*2-NVL(3,4)/5+6';
--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;


   /* Create price formula line 1 of type 'List Price'(LP) */
   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  := 'LP';
gpr_formula_lines_tbl(k).step_number  := 1;
gpr_formula_lines_tbl(k).operation:= qp_globals.g_opr_create;


   /* Create price formula line 2 of type 'Price List Line'(PLL) */
   k  := 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:= 'PLL';
gpr_formula_lines_tbl(k).step_number  := 2;
gpr_formula_lines_tbl(k).price_list_line_id := 293195;
--Corresponds to the list_line_id of the item 'dw01' on the Price --List          
-- 'Testing 1023'.   gpr_formula_lines_tbl(k).operation := qp_globals.g_opr_create;


  /* Create price formula line 3 of type 'Pricing Attribute'(PRA) */
   k    := 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 := 'PRA';
gpr_formula_lines_tbl(k).step_number     := 3;
gpr_formula_lines_tbl(k).pricing_attribute_context:= 'PRICING ATTRIBUTE';
gpr_formula_lines_tbl(k).pricing_attribute:= 'PRICING_ATTRIBUTE12';
 -- Corresponds to the Pricing Attribute 'Insurance Cost'
gpr_formula_lines_tbl(k).operation := qp_globals.g_opr_create;

   /* Create price formula line 4 of type 'Numeric Constant'(NUM) */
   k    := 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 := 'NUM';
gpr_formula_lines_tbl(k).step_number    := 4;
gpr_formula_lines_tbl(k).numeric_constant := 1000;
gpr_formula_lines_tbl(k).operation := qp_globals.g_opr_create;


   /* Create price formula line 5 of type 'Function'(FUNC) */   -
-- User must customize the QP_CUSTOM.Get_Custom_Price function
-- to return a numeric value and also set the profile option
-- 'QP: Get Custom Price Customized' to 'Yes' at the Site Level to
--successfully use this formula line type (FUNC) in their formulas.
   k := 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 := 'FUNC';
gpr_formula_lines_tbl(k).step_number := 5;
gpr_formula_lines_tbl(k).operation := qp_globals.g_opr_create;


   /* Create price formula line 6 of type 'Factor List'(ML) */

   k  := 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   := 6;
gpr_formula_lines_tbl(k).price_modifier_list_id := 50174;
   -- Corresponds to the list_header_id of an existing Factor List
   -- 'ABC'
 gpr_formula_lines_tbl(k).operation := qp_globals.g_opr_create;


   /* Create price formula line 7 of type 'List Price'(MV) */
   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 := 'MV';
   gpr_formula_lines_tbl(k).step_number  := 7;
   gpr_formula_lines_tbl(k).operation   := qp_globals.g_opr_create;

   --dbms_output.put_line('before process price formula ');

   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;
--dbms_output.put_line('after process price formula ');

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);
      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;

Script to get the Inventory transaction types/Source/Action Name

Script to get the Inventory transaction types/Source/Action Name

  SELECT a.transaction_type_id
       , a.transaction_type_name
       , a.transaction_source_type_id
       , b.transaction_source_type_name
       , a.transaction_action_id
       , c.meaning
    FROM mtl_transaction_types a
       , mtl_txn_source_types b
       , mfg_lookups c
   WHERE a.transaction_source_type_id = b.transaction_source_type_id
     AND a.transaction_action_id = c.lookup_code
     AND c.lookup_type = 'MTL_TRANSACTION_ACTION'
ORDER BY transaction_type_id;

Testing Integrated SOA Gateway's Service Invocation Framework (SIF)

By: Guest Author

Overview

Integrated SOA Gateway's Service Invocation Framework id based on the Business Event System capability to subscribe to java function Invoke Web Service (WebServiceInvokerSubscription.java) in order to invoke a web service.

 This blog goes over the series of steps on how to deploy one of the standard database packages (FND_ USER_PKG) as a web service so that it can be consumed by the subscription to Invoke Web Service, and perform the invocation from the Business Event System administrator page.

Note: This content applies to EBS versions 12.1.3 and 12.2. 

1. Enable and deploy the interface to be published as web service

Connect to EBS with the responsibility Integrated SOA Gateway and go to the Repository. There, expand Application Technology and then Application Object Library. Select User.

Now the list of interfaces for User are displayed. Click on the one with internal name FND_USER_PKG:

From the list of PLSQL interfaces select 'Test User Name' and hit generate WSDL

Select the box of Test User Name and click on Generate and then Deploy. After the Generation is complete a link to the WSDL file is enabled and it is to be used to consume the service. Copy the WSDL link or simply click on it:

2. Test the invocation from outside EBS

SOAP UI is a nice tool to quickly test the services proper deployment, and it only requires the URL to the WSDL generated above. 

Notice that upon invocation the response of the Web Service can be seen on the panel on the right.

3. Define a business event with a subscription to Invoke Web Service

Now test the invocation from within EBS by using the Business Event System itself. Simply create a business event and create a subscription to Web Service Invocation. Then follow the train of configuration. 

Select the service, port and operation to be invoked by this subscription:

and then

4. Test the invocation from Business Event System

Now that the event and the subscription are created, query the event and click on the Test event icon/link

On these form three parameters are required:

- Event Key, which can be anything

- Event parameter WFBES_INPUT_HEADER set to the following:

<wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" 
  env:mustUnderstand="1" xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
  <wsse:UsernameToken xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" 
    wsu:Id="UsernameToken-12423818"> 
    <wsse:Username>sysadmin</wsse:Username> 
    <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">sysadmin</wsse:Password> 
  </wsse:UsernameToken> 
</wsse:Security>

- Event payload (XML)

<tes:InputParameters xmlns:tes="http://xmlns.oracle.com/apps/fnd/soaprovider/plsql/fnd_user_pkg/testusername/"> 
  <tes:X_USER_NAME>SYSADMIN</tes:X_USER_NAME> 
</tes:InputParameters>

By clicking on Raise on Java the subscription is executed in the same session and the response is provided immediately:

Notice the response is the same as the one seen in SOAP UI

Conclusion

Oracle Business Event System provides the capability to invoke SOAP web services and also the means to test them by using its own features.

Reference : Oracle Blogs

Asynchronous Business Event Subscriptions

Asynchronous Business Event Subscriptions - Troubleshooting Tips

By: Guest Author

Overview 

This blog is intended as an example to help trace an asynchronous business event through it's execution within the Workflow Business Event System. This blog should also help clarify the myth among Oracle Workflow developers and administrators that all business events can be traced through Business Event System AQs.

As a reader of this blog post, it is expected that you understand following steps already.

• Create a business event using Workflow Administrator Web Applications responsibility
• Create subscriptions to business events
• Synchronous subscriptions with phase <= 99
• Asynchronous subscriptions with phase >100
• Understand "On Error" subscription attributes such as "Stop and Rollback" and "Skip to Next"

It is strongly recommended that all business events and/or groups that has at least one LOCAL or EXTERNAL subscription should also have one ERROR subscription that launches following seeded error workflow process.

• Workflow Type - WFERROR
• Workflow Process - DEFAULT_EVENT_ERROR

If an error subscription is not created event subscription failures may go unnoticed. Create the error subscription as follows.

Sample Local Subscription

For testing purposes, let us assume following PLSQL rule function (in package XXX_PACKAGE) used in a LOCAL event subscription with phase > 99. This will cause the event to be enqueued to WF_DEFERRED queue and Workflow Agent Listener process to execute it in the background. This function inserts records into a temporary table.

    create table temp_table (key_value varchar2(50), result varchar2(20));
    create or replace package xxx_package is
      function subscription1 (p_subscription_guid in raw,
                              p_event in out nocopy wf_event_t) return varchar2;
    end xxx_package;
    /
    create or replace package body xxx_package is
      function subscription1(p_subscription_guid in raw,
                           p_event in out nocopy wf_event_t) return varchar2 is
        l_result varchar2(20);   
      begin
        --processing...
        l_result := p_event.GetValueForParameter('OUTCOME');
        if l_result='GOOD' then
          insert into temp_table values (p_event.getEventKey(), l_result);
          return 'SUCCESS';
        else
          insert into temp_table values (p_event.getEventKey(), l_result);
          wf_core.context('xxx_package','function subscription1', p_event.getEventName(), p_event.getEventKey());
          wf_event.setErrorInfo(p_event, 'ERROR');
          return 'ERROR';
        end if;
      exception
        when others then
          wf_core.context('xxx_package','function subscription1', p_event.getEventName(), p_event.getEventKey());
          wf_event.setErrorInfo(p_event, 'ERROR');
          return 'ERROR';
      end subscription1;
    end xxx_package;
    /

IMPORTANT NOTE: Return value should be in UPPERCASE, otherwise the Business Event System will not recognize the result.

Test the business event

Write a PLSQL procedure to create and set the event parameters and then raise it. 

     declare
      l_event_name varchar2(50) := 'oracle.apps.fnd.wfds.user.userUpdated';
      l_event_key varchar2(50) := to_char(sysdate, 'DD-MM-RRRR HH:MI:SS');
      l_attributes wf_parameter_list_t;
    begin
      --Add the logic to be executed when the event occurs. In this case the
      -- creation of a user was signaled.
      -- ...
      --Add all the parameters to the list:
      --WF_EVENT.AddParameterToList('OUTCOME', 'WRONG', l_attributes);
      WF_EVENT.AddParameterToList('OUTCOME', 'GOOD', l_attributes);
      -- Raise the event
      WF_EVENT.Raise(p_event_name => l_event_name,
                     p_event_key => l_event_key,
                     p_parameters => l_attributes);
      commit;
    end;
    /

When this block runs with parameter OUTCOME set to 'GOOD', a record is inserted into TEMP_TABLE by the LOCAL subscription's rule function. When a different value is used the function returns ERROR and a record is insert into the temporary table. But since the result is ERROR, the transaction is rolled back and error subscription is executed. The Business Event System uses the error subscription to launch WFERROR:DEFAULT_EVENT_ERROR process to send error notification to SYSADMIN with the details of the exception. These error details shown on the notification are as a result of the calls to WF_EVENT.SetErrorInfo and WF_CORE.Context in the subscription rule function.

Troubleshooting Tips

• Only Busienss Events with asynchronous subscriptions are enqueued to WF_DEFERRED or WF_JAVA_DeFERRED queues based on whether the subscription is PLSQL based or Java based. All events with synchronous subscriptions are executed in the same thread they are raised in and not enqueued to any AQs.
  NOTE: The event's synchronous subscriptions themselves may enqueue the events to some Out Agents such as WF_JMS_OUT or WF_WS_JMS_OUT and so on which is purely specific to subscription's implementation and not related to core Business Event processing.
• From Workflow Manager, ensure the Workflow Deferred Agent Listener and the Workflow Java Deferred Agent Listener components are running
• Ensure an ERROR subscription is created for the business event
• To trace a business event with Asynchronous Subscription from Raise to Dispatch to Error
• Ensure an error subscription was created as in the sample above
• Stop Workflow Deferred Agent Listener and Workflow Error Agent Listener
• Raise the event
• Verify the event is enqueued to WF_DEFERRED using below SQL. Assuming the event key is unique across all occurences, it should return 1
  select count(1) from applsys.aq$wf_deferred a where a.user_data.event_name = '&eventName'and a.user_data.event_key = '&eventKey' and a.msg_state = 'READY';
• Start Workflow Deferred Agent Listener and after few minutes verify the event is processed successfully using above SQL. The count should be 0
• If the event was not dispatched successfully, check if the event is enqueued to WF_ERROR queue using SQL. If the event errored, it should be in error queue. If this SQL returns 0, the subscription was executed successfully. select count(1) from applsys.aq$wf_error a where a.user_data.event_name = '&eventName'and a.user_data.event_key = '&eventkey'and a.msg_state = 'READY'
• Start Workflow Error Agent Listener. If there was a record in WF_ERROR queue, after few minutes verify the event is processed successfully using above SQL
• Now check SYSADMIN's worklist for error notification with details of the error.
• To obtain E-Business Debug Log messages for business event processing
• Enable Log profile options for the E-Business Suite user whose session triggers the business event.
  FND: Debug Log Enabled=YES
  FND: Debug Log Level=STATEMENT
  FND: Debug Log Module=wf.plsql%
• In the case where the event is raised from a PLSQL block you can add the following at begining of the procedure to identify the session and to activate the debug profile options.
  declare
  ...
  begin
    fnd_global.apps_initialize(user_id=>0, resp_id=>24240, resp_appl_id=>1);
    --...
  end;
  fnd_global.apps_initialize will enable the profile options for this PLSQL block for user SYSADMIN, application 'System Administration' and responsibility 'System Administrator'
• Then query table APPLSYS.FND_LOG_MESSAGES where column MODULE like 'wf.plsql%' to see the debug messages
  sqlplus apps/***** @$FND_TOP/patch/115/sql/wfdbgprnt wf.plsql%

References

Oracle Workflow API Reference, section 'Event Subscription Rule Function APIs'

Oracle Workflow Developer's Guide, section 'Error Handling for Event Subscription Processing'

Reference Form : Oracle Blogs

Business event does not raise - event raises and does nothing

By: Guest Author

Frequently, new functionality is created using the Business Event System or existing functionality is changed by adding new subscriptions, changing subscription phases, changing underlying java or PLSQL code, etc, and inexplicably it is found that raising the business event does not produce the desired result, as if the event was not raised.

The execution of an event consists basically of two parts: raising the event and do all required validation, and then finding the subscriptions to that event and execute them. The first one barely ever fails while the second one is more pron to issues and it is the one that we are mostly interested in as it has the code/behavior we want to see.

The following steps will allow to identify the actual cause of the issue by making use of the FND debugging options provided in EBS.

1. Adjust the subscription phase

Since subscriptions with phase higher than 100 are deferred you would need to raise the event on one session and then go to another session and debug it. It is easier if you can set the phase to a value lower than 100, say 99, and then raise the event in the session. This way you will be able to see everything the happens related to this event subscription, a nothing else.

Remember to ensure that the business event has an error subscription so that if something happens it does not go silent. By adding an ERROR subscription the system administrator would be able to see as notification explaining any failures. See here for more on error subscription definitions. 

2. Raise the event 

Do so either from the application or from java or PLSQL. Here is an example from PLSQL.

Note: you can make use of wf_log_pkg.init to enable the FND: Debug option for this session only, so there is no need to change the corresponding profile options. The last parameter ('wf%') means the debug is enabled for the WF code, which the BES belongs to.

declare
  l_parameters wf_parameter_list_t := wf_parameter_list_t();
begin
  --Optional:
  --execute immediate 'truncate table applsys.fnd_log_messages';
  --Add your parameters here
  wf_log_pkg.init(1, null, 1, 'wf%');
  wf_event.addparametertolist(p_name          => 'NOTIFICATION_ID',
                              p_value         => '123',
                              p_parameterlist => l_parameters);
  wf_event.raise(p_event_name => 'oracle.apps.wf.notification.send',
                 p_event_key  => '123',
                 p_event_data => NULL,
                 p_parameters => l_parameters,
                 p_send_date  => sysdate);
  commit;
end;
/

anonymous block completed 

3. Find the logging details 

Now, within the same session check the log messages generated by the Business Event System:

SELECT 'Level: '||log_level||
       ' Module: '||module||
       ' Time: '||to_char(timestamp, 'DD-MON-RR HH24:MI:SS')||
       wf_core.newline||
       '>>> Message: '||message_text
FROM   fnd_log_messages
WHERE  audsid  =  sys_context('userenv', 'SESSIONID')
ORDER BY log_sequence
/ 

 

The final lines found in the query above and the error notifications sent to the system administrator will point out to the error causing the event to look as it did not fire.

Reference : Oracle Blogs