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;