Price List Conversion

Business requirement:

 Convert price list data from 11i to R12.2.7 instance as a part of consolidation project.

Basic check and prerequisite:

1)      Price list header’s end date should not be null.

2)     If price list header’s end date is not null but that price list line may use in sale order line so we need bring that line/header as well in R12.2.7.

3)      Price list header should be Active in 11i instance.

Environment:

Oracle E-Business Suite comes with a set of open interfaces and APIs to convert data into ERP once data is placed into those interface table. In data conversion we convert data form one format to another format in the different version of the same system.

Example: When we upgrade from Oracle 11i to Oracle R12 we do a data conversion from 11i to R12.

Mapping points with data selection criteria:

Flow: 

Approach (interface/API):

Below are some Price List API’s:

•  QP_Price_formula_PUB.Get_Price_Formula (Formula Calculation API): The Formula Calculation package consists of entities to calculate the value of a formula.  
• QP_Price_formula_PUB.Process_Price_Formula (Update Formula Prices API): The Update Formula Prices package consists of entities to update formula prices.  
• QP_CUSTOM.Get_Custom_Price (Get Custom Price API): You may add custom code to this customizable function. The pricing engine while evaluating a formula that contains a formula line (step) of type "function" calls this API.  
• QP_PREQ_GRP.Price_Request (Price Request API): The Price Request Application Program Interface (API) is a public API that allows you to get a base price and to apply price adjustments, other benefits, and charges to a transaction.
•  QP_MODIFIERS_PUB.Process_Modifiers (Business Object for Modifier Setup API): The Business Object for Modifier Setup package consists of entities to set up modifiers.  
• QP_QUALIFIER_RULES_PUB.Process_Qualifier_Rules (Qualifiers API): The Qualifiers package consists of entities to set up qualifiers.  
• QP_ATTR_MAPPING_PUB. Build_Contexts (Attribute Mapping API): The Attribute Mapping package consists of entities to map attributes.  
• QP_Price_List_PUB.Process_Price_List (Price List Setup API): The Price List Setup package consists of entities to set up price lists.

     Setups:

Before start price list conversion in R12.2.7 instance there are few pre requisites which is mandatory for    price list.

1)       Item

2)      Item Attributes

3)      Organization setup

4)      Operating unit set up

Another part include in setup is which needs to set profile options as below.

1)       MO OPERATING UNIT: This profile option only provides access to one operating unit and this is used as the default operating unit during transaction entry.

2)       QP ITEM VALIDATION: Set this profile, by site or responsibility, to an organization at the level in your organization hierarchy at which you set prices for items.

3)      QP: Continuous Price Breaks: Unable to create Volume offer when the QP: Continuous Price Break is set No.

Table Details:

·       QP_LIST_HEADERS_B

·       QP_LIST_LINES_V

·       QP_LIST_HEADERS_TL

·       QP_PRICING_ATTRIBUTES

              ·       QP_PRICE_BREAKS_V

Key columns observations specific issue:

For price breaks value from and value to in 11i allows user below scenario

But in R12.2.7 instance will not be allowed to put above value from and value to

It should be like this.

If user tried to insert values like 11i then it will give below error.

 Fetch data from source instance to staging table:

  Extraction query

Price List Extraction Query

Working example:

-- Cursor to fetch header level records from staging table.

   CURSOR c_hdr_details (p_prl_name IN VARCHAR2, p_currency_code IN VARCHAR2)

   IS

      SELECT DISTINCT global_flag, description, effective_start_date,

                   effective_end_date, comments, secondary_price_list,

                   precedence, prl_action_code, operating_unit, ROWID

      FROM xxc_stg1_table

      WHERE prl_name = p_prl_name

      AND currency_code = p_currency_code

      AND xxc_status IS NULL;

-- Cursor to fetch line level records from staging table.

CURSOR c_lines (p_prl_name IN VARCHAR2, p_line_type IN VARCHAR2)

   IS

      SELECT   UPPER (item_category) item_category,

               UPPER (item_cat_number) item_cat_number, uom_code,

               prl_action_code, VALUE, start_date, end_date, line_type,

               UPPER (price_break_type) price_break_type, value_from,

               value_to, comments, description, global_flag,

               effective_start_date, secondary_price_list, precedence,

               line_precedence, effective_end_date, attribute1, attribute2,

               attribute3, attribute4, attribute5, attribute6, attribute7,

               attribute8, attribute9, attribute10, attribute11, attribute12,

               attribute13, attribute14, attribute15, ROWID

      FROM xxc_stg1_table

      WHERE prl_name = p_prl_name

      AND line_type = p_line_type

      AND xxc_status IS NULL

      ORDER BY item_category,

               item_cat_number,

               start_date,

               end_date,

               price_break_type,

               value_from;

--Validation

1)     Validation for Customer order flag filed:

      SELECT msi.inventory_item_id, msi.customer_order_flag

        INTO l_inventory_item_id, l_customer_order_flag

        FROM mtl_system_items msi, oe_system_parameters_all osp

        WHERE msi.organization_id = osp.master_organization_id

        AND msi. segment1 = p_item_cat_number

        AND msi.organization_id = fnd_profile.VALUE ('ORG_ID');

2)     Validation of Item Category:

        SELECT mc.category_id

        INTO l_category_id

        FROM mtl_category_sets mcs, mtl_categories mc

        WHERE mcs.category_set_name = 'INVENTORY'

        AND mc.structure_id = mcs.structure_id

        AND mc.segment1 = p_item_cat_number;

3)     Validation for UOM code :

        SELECT uom_code

        INTO l_uom_code

        FROM mtl_item_uoms_view miu, oe_system_parameters_all osp

        WHERE inventory_item_id = p_inventory_item_id

        AND organization_id = osp.master_organization_id

        AND uom_code = p_uom_code

        AND miu.organization_id = fnd_profile.VALUE ('ORG_ID');

4)     Validation of UOM_CODE for Category

       SELECT DISTINCT uom_code

       INTO l_uom_code

       FROM mtl_item_uoms_view miu, oe_system_parameters osp

       WHERE organization_id = osp.master_organization_id

       AND uom_code = p_uom_code

       AND inventory_item_id IN (

                                            SELECT inventory_item_id

                                            FROM mtl_item_categories, oe_system_parameters osp

                                            WHERE category_id = p_category_id

                                             AND organization_id = osp.master_organization_id

        );

5)     Validation for Org:

      SELECT organization_id

      INTO ln_org_id

      FROM hr_operating_units

      WHERE NAME = c_hdr_details_rec.operating_unit;

Record type should be taken as below:

-- Header

      gpr_price_list_rec            qp_price_list_pub.price_list_rec_type;

      ppr_price_list_rec            qp_price_list_pub.price_list_rec_type;

      ppr_price_list_val_rec        qp_price_list_pub.price_list_val_rec_type;

      -- Line

      gpr_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;

      ppr_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;

      ppr_price_list_line_val_tbl   qp_price_list_pub.price_list_line_val_tbl_type;

    

 -- Qualifiers

      gpr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;

      ppr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;

      gpr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;

      ppr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;

      gpr_qualifier_rules_rec       qp_qualifier_rules_pub.qualifier_rules_rec_type;

      ppr_qualifier_rules_rec       qp_qualifier_rules_pub.qualifier_rules_rec_type;

      gpr_qualifier_rules_val_rec   qp_qualifier_rules_pub.qualifier_rules_val_rec_type;

      ppr_qualifier_rules_val_rec   qp_qualifier_rules_pub.qualifier_rules_val_rec_type;

      -- Attribute

      gpr_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;

      ppr_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;

      ppr_pricing_attr_val_tbl      qp_price_list_pub.pricing_attr_val_tbl_type;

Calling API:

qp_price_list_pub.process_price_list

                    (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_true,

                     x_return_status                => gpr_return_status,

                     x_msg_count                    => gpr_msg_count,

                     x_msg_data                     => gpr_msg_data,

                     p_price_list_rec               => gpr_price_list_rec,

                     p_price_list_line_tbl          => gpr_price_list_line_tbl,

                     p_pricing_attr_tbl             => gpr_pricing_attr_tbl,

                     x_price_list_rec               => ppr_price_list_rec,

                     x_price_list_val_rec           => ppr_price_list_val_rec,

                     x_price_list_line_tbl          => ppr_price_list_line_tbl,

                     x_price_list_line_val_tbl      => ppr_price_list_line_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,

                     p_check_duplicate_lines        => 'N'

                    );

Run below program to load data form staging to base table using API.

Error:

1)     Line level start date is earlier that header level start date. For this we added date validate on staging table.by using below query.

-- Added below additional code to validate the date Overlaps

         SELECT COUNT (1)

         INTO l_count_stg

         FROM xxc_stg1_table xls

         WHERE xls.item_cat_number = c_lines_rec.item_cat_number

         AND xls.uom_code = c_lines_rec.uom_code

         AND xls.prl_name = p_prl_name

         AND xls.line_type = c_lines_rec.line_type

         AND NVL (xls.value_from, -9) = NVL (c_lines_rec.value_from, -9)

         AND NVL (xls.value_to, -9) = NVL (c_lines_rec.value_to, -9);

2)     Line level end date is greater than header level end date.

Eg:

For this we need to do data clean up activity in 11i instance. So that we can bring valid records in R12 system.

After Data clean-up activity:

3)     Item not available against given organization.

        This is most typical error will get when loading price lines.

If QP ITEM VALIDATION is set to XXA Operating Unit and that item is not assigned to that XXA Operating Unit then this Item not available against given organization error will occurred.