Sunday, December 12, 2021

API to Create and Update sales order price list in Oracle Apps

 CREATE OR REPLACE PROCEDURE APPS.xx_pricelist (

   p_list_header_id       IN   NUMBER,
   p_operand              IN   NUMBER,
   p_product_attr_value   IN   VARCHAR2,
   p_uom                  IN   VARCHAR2
)
IS
   v_return_status               VARCHAR2 (1)                         := NULL;
   v_msg_count                   NUMBER                                  := 0;
   v_msg_data                    VARCHAR2 (2000);
   v_price_list_rec              qp_price_list_pub.price_list_rec_type;
   v_price_list_val_rec          qp_price_list_pub.price_list_val_rec_type;
   v_price_list_line_tbl         qp_price_list_pub.price_list_line_tbl_type;
   v_price_list_line_val_tbl     qp_price_list_pub.price_list_line_val_tbl_type;
   v_qualifiers_tbl              qp_qualifier_rules_pub.qualifiers_tbl_type;
   v_qualifiers_val_tbl          qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   v_pricing_attr_tbl            qp_price_list_pub.pricing_attr_tbl_type;
   v_pricing_attr_val_tbl        qp_price_list_pub.pricing_attr_val_tbl_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;
   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;
   ppr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;
   ppr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_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;
BEGIN
   v_price_list_rec.list_header_id := p_list_header_id;
   v_price_list_rec.list_type_code := 'PRL';
   v_price_list_rec.operation := qp_globals.g_opr_update;
   v_price_list_line_tbl (1).list_header_id := p_list_header_id;
   v_price_list_line_tbl (1).list_line_id := fnd_api.g_miss_num;
   v_price_list_line_tbl (1).list_line_type_code := 'PLL';
   v_price_list_line_tbl (1).operation := qp_globals.g_opr_create;
   v_price_list_line_tbl (1).operand := p_operand;
   v_price_list_line_tbl (1).arithmetic_operator :=  'UNIT_PRICE';
 --  v_price_list_line_tbl (1).start_date_active := '05-OCT-2011';
 --  v_price_list_line_tbl (1).start_date_active := '05-OCT-2011';
  -- v_price_list_line_tbl (1).organization_id := NULL;
   v_pricing_attr_tbl (1).pricing_attribute_id := fnd_api.g_miss_num;
   v_pricing_attr_tbl (1).list_line_id := fnd_api.g_miss_num;
  ---- v_pricing_attr_tbl (1).product_attribute_context := 'Item';
  -- v_pricing_attr_tbl (1).product_attribute := 'Item Number';
  v_pricing_attr_tbl (1).product_attribute_context := 'ITEM';
v_pricing_attr_tbl (1).product_attribute := 'PRICING_ATTRIBUTE1';
   v_pricing_attr_tbl (1).product_attr_value := p_product_attr_value;
   v_pricing_attr_tbl (1).product_uom_code := p_uom;
   v_pricing_attr_tbl (1).excluder_flag := 'N';
   v_pricing_attr_tbl (1).attribute_grouping_no := 1;
   v_pricing_attr_tbl (1).price_list_line_index := 1;
   v_pricing_attr_tbl (1).operation := qp_globals.g_opr_create;
   DBMS_OUTPUT.put_line ('Calling API to insert Price List');
   qp_price_list_pub.process_price_list
                    (p_api_version_number           => 1,
                     p_init_msg_list                => fnd_api.g_true,
                     p_return_values                => fnd_api.g_false,
                     p_commit                       => fnd_api.g_false,
                     x_return_status                => v_return_status,
                     x_msg_count                    => v_msg_count,


                     x_msg_data                     => v_msg_data,
                     p_price_list_rec               => v_price_list_rec,
                     p_price_list_line_tbl          => v_price_list_line_tbl,
                     p_pricing_attr_tbl             => v_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_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,
                     x_price_list_line_val_tbl      => ppr_price_list_line_val_tbl
                    );
   IF v_return_status = fnd_api.g_ret_sts_success
   THEN
      COMMIT;
      DBMS_OUTPUT.put_line
                        ('The Item loading into the price list is Sucessfull');
   ELSE
      DBMS_OUTPUT.put_line ('The Item loading into the price list Failed');
      ROLLBACK;
      FOR i IN 1 .. v_msg_count
      
LOOP
         v_msg_data := oe_msg_pub.get (p_msg_index => i, p_encoded => 'F');
         DBMS_OUTPUT.put_line (i || ') ' || v_msg_data);
      END LOOP;
   END IF;
END;
/


After this you need to create one custom table


CREATE TABLE APPS.XXPRICE_ROHIT
(
  ORG_ID      NUMBER,
  PRICE_LIST  VARCHAR2(400 BYTE),
  ITEM_CODE   VARCHAR2(400 BYTE),
  ATTR_VALUE  NUMBER,
  STATUS      VARCHAR2(40 BYTE),
  ERR_MSG     VARCHAR2(400 BYTE),
  ENTRY_ID    NUMBER
)

This table will store all information of Sale order item and which price list will be assigned and what rate will be provided to this item. So upload all the price list data in this staging table.

Then you need to create this final procedure to load the price list data from staging table to oracle standard price list base 
tables.

CREATE OR REPLACE PROCEDURE APPS.xx_rohit_plist
IS
   CURSOR plist_info_stg
   IS
      SELECT *
        FROM xxprice_rohit
       WHERE NVL (status, 'N') <> 'P';
   v_status             VARCHAR2 (1);
   v_err_msg            VARCHAR2 (100);
   v_item_id            NUMBER;
   v_primary_uom_code   VARCHAR2 (3);
   v_list_header_id     NUMBER;
BEGIN
   FOR z1 IN plist_info_stg
   LOOP
   v_status:= 'P';
   v_err_msg := null;
      BEGIN
         SELECT inventory_item_id, primary_uom_code
           INTO v_item_id, v_primary_uom_code
           FROM mtl_system_items_b msi
          WHERE segment1||'.'||segment2||'.'||segment3||'.'||segment4||'.'||segment5 = z1.item_code
            AND msi.organization_id = z1.org_id;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_status := 'E';
            v_err_msg := 'Inventory Item ID not found';
      END;
      BEGIN
         SELECT list_header_id
           INTO v_list_header_id
           FROM qp_list_headers_tl
          WHERE NAME = z1.price_list;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_status := 'E';
            v_err_msg := 'Price list ID not found';
      END;
      IF     v_item_id IS NOT NULL
         AND v_primary_uom_code IS NOT NULL
         AND v_list_header_id IS NOT NULL
      THEN
         xx_pricelist (v_list_header_id,
                             to_number(z1.attr_value),
                             v_item_id,
                             v_primary_uom_code
                            );
      END IF;
      UPDATE xxprice_rohit
         SET status= v_status,
             err_msg = v_err_msg
       WHERE entry_id = z1.entry_id;
      COMMIT;
   END LOOP;
END;
/

No comments:

Post a Comment

SQL Important Queries

  How to delete rows with no where clause The following example deletes  all rows  from the  Person.Person  the table in the AdventureWork...