Search This Blog

Saturday, January 22, 2011

Updating Price List Lines - API Approach

Script for Updating Price List Lines :-

Issue :- Need to update unit price of items which are in given Price List ans having  unit price as $9999999.

Process Followed :- First end date all the lines in QP_LIST_LINES which falls under above conditions and then insert new records in QP_LIST_LINES.

API Used :- qp_price_list_pub.process_price_list(Both for Insert and Update)

Steps Followed :-
1. Create a staging table to store Item_Id, New Unit_Price, Transaction_ID, Status and Errror_Message.
2. Created a synonym, sequence, trigger and index on staging table.
3. Validated Items Ids given (For Duplicate Records, Null Records and existence of item ids) in the  data file).
4. Send List_Header_Id, List_Line_Id, Pricing_Attribute_Id and field that has to be updated (In this example, end_date_active) and operation should be qp_globals.g_opr_update.
===========================================================================
Staging Table , Synonym, Sequence, Trigger Creation

SET serverout on size 1000000
CL SCR;

PROMPT +------------------------------------------------------------------------+
PROMPT   Execution of script starts
PROMPT +------------------------------------------------------------------------+

--drop table lss_unit_price_stg;

CREATE TABLE lss_unit_price_stg(
transaction_id_stg   NUMBER NOT NULL
,status_stg    VARCHAR2(2)
,item_id NUMBER
,unit_price NUMBER
,error_message  VARCHAR2(2000)
);

--drop synonym lss_unit_price_stg;

CREATE SYNONYM lss_unit_price_stg FOR lss.lss_unit_price_stg;

--drop index lss_unit_price_stg_ui;

CREATE  UNIQUE INDEX lss_unit_price_stg_ui ON lss_unit_price_stg(transaction_id_stg);

--drop sequence lss_unit_price_stg_s1;

CREATE SEQUENCE lss_unit_price_stg_s1
START WITH 1
INCREMENT BY 1;

-- Creation Of Trigger

CREATE OR REPLACE TRIGGER lss_unit_price_stg_trg
   BEFORE INSERT
   ON lss_unit_price_stg
   FOR EACH ROW
BEGIN
   IF :NEW.transaction_id_stg IS NULL
   THEN
      SELECT lss_unit_price_stg_s1.NEXTVAL, 'N'
        INTO :NEW.transaction_id_stg, :NEW.status_stg
        FROM DUAL;
   END IF;
END;
/
============================
Package Specification

CREATE OR REPLACE PACKAGE lss_unit_price_update_pkg
IS
   PROCEDURE unit_price_validate (p_list_header_id IN NUMBER);

   PROCEDURE unit_price_load (p_list_header_id IN NUMBER);
END lss_unit_price_update_pkg;
/
===============================
Package Body


CREATE OR REPLACE PACKAGE BODY lss_unit_price_update_pkg
IS
   PROCEDURE unit_price_validate (p_list_header_id IN NUMBER)
   IS
      l_error_msg   VARCHAR2 (3000);
      l_item_id     NUMBER;

      CURSOR item_stg_cur
      IS
         SELECT item_id, transaction_id_stg
           FROM lss_unit_price_stg
          WHERE status_stg IN ('N', 'VE');

      CURSOR dup_item_cur
      IS
         SELECT   item_id
             FROM lss_unit_price_stg
         GROUP BY item_id
           HAVING COUNT (*) > 1;

      CURSOR item_nul_cur
      IS
         SELECT transaction_id_stg
           FROM lss_unit_price_stg
          WHERE item_id IS NULL;
   BEGIN
-- Validating Item_Id
      FOR item_stg_rec IN item_stg_cur
      LOOP
         BEGIN
            SELECT DISTINCT inventory_item_id
                       INTO l_item_id
                       FROM qp_list_lines
                      WHERE 1 = 1
                        AND list_header_id = p_list_header_id
                        --AND operand = 9999999
                        --AND end_date_active IS NULL
                        AND inventory_item_id = item_stg_rec.item_id;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error_msg :=
                             'Inventory Item Id Not Found' || SQLERRM || ';;';

               UPDATE lss_unit_price_stg
                  SET status_stg = 'VE',
                      error_message = error_message || l_error_msg
                WHERE 1 = 1 AND item_id = item_stg_rec.item_id;

               COMMIT;
         END;
      END LOOP;

      DBMS_OUTPUT.put_line ('Vallidating ItemID');

      -- Validating for Duplicate records
      FOR dup_item_rec IN dup_item_cur
      LOOP
         BEGIN
            l_error_msg := 'Duplicate Item Id ;;';

            UPDATE lss_unit_price_stg
               SET status_stg = 'VE',
                   error_message = error_message || l_error_msg
             WHERE item_id = dup_item_rec.item_id;

            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line
                  (   'Error While updating Staging Table for the Item(Duplicate)'
                   || dup_item_rec.item_id
                  );
         END;
      END LOOP;

      DBMS_OUTPUT.put_line ('Vallidating Duplicate Records');

      -- Validating Null Item Ids
      FOR item_nul_rec IN item_nul_cur
      LOOP
         BEGIN
            l_error_msg := 'Given Item Id is Null ;;';

            UPDATE lss_unit_price_stg
               SET status_stg = 'VE',
                   error_message = error_message || l_error_msg
             WHERE transaction_id_stg = item_nul_rec.transaction_id_stg;

            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line
                  (   'Error While updating Staging Table for the Transaction(NULL)'
                   || item_nul_rec.transaction_id_stg
                  );
         END;
      END LOOP;

      DBMS_OUTPUT.put_line ('Vallidating D');

      BEGIN
         UPDATE lss_unit_price_stg
            SET status_stg = 'V'
          WHERE status_stg = 'N' AND error_message IS NULL;

         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
               ('Exception while updating staging table for successfully calidated records'
               );
      END;
   END unit_price_validate;

   PROCEDURE unit_price_load (p_list_header_id IN NUMBER)
   AS
      pl_code                       VARCHAR2 (2000);
      l_price                       NUMBER;
      l_count                       NUMBER;
      l_msg_dummy                   VARCHAR2 (2000);
      l_output                      VARCHAR2 (2000);
      l_msg_data                    VARCHAR2 (2000);
      l_msg_count                   NUMBER;
      l_list_line_id                NUMBER;
      l_pricing_attribute_id        NUMBER;
      l_item_id                     NUMBER;
      gpr_return_status             VARCHAR2 (10)                     := NULL;
      gpr_msg_count                 NUMBER                               := 0;
      gpr_msg_data                  VARCHAR2 (2000);
      gpr_price_list_rec            qp_price_list_pub.price_list_rec_type;
      gpr_price_list_val_rec        qp_price_list_pub.price_list_val_rec_type;
      gpr_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;
      gpr_price_list_line_val_tbl   qp_price_list_pub.price_list_line_val_tbl_type;
      gpr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;
      gpr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;
      gpr_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;
      gpr_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;
      i                             NUMBER                               := 1;
      k                             NUMBER                               := 0;
      j                             NUMBER                               := 0;
      v_userid                      NUMBER;
      v_applid                      NUMBER;
      v_respid                      NUMBER;
      v_loginid                     NUMBER;

      CURSOR item_stg_cur
      IS
         SELECT item_id, transaction_id_stg
           FROM lss_unit_price_stg
          WHERE status_stg = 'V';
   BEGIN
      DBMS_OUTPUT.put_line (p_list_header_id);

      BEGIN
         fnd_client_info.set_org_context (141);
      END;

      BEGIN
         SELECT user_id
           INTO v_userid
           FROM fnd_user
          WHERE user_name LIKE 'XXXX';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_userid := NULL;
      END;

      BEGIN
         SELECT MAX (login_id)
           INTO v_loginid
           FROM fnd_logins
          WHERE 1 = 1 AND user_id = v_userid;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_loginid := NULL;
      END;

      BEGIN
         SELECT application_id, responsibility_id
           INTO v_applid, v_respid
           FROM fnd_responsibility_tl
          WHERE responsibility_name LIKE 'Oracle Pricing Manager';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_applid := NULL;
            v_respid := NULL;
      END;

      fnd_global.apps_initialize (v_userid, v_respid, v_applid);       -- Prod

      FOR item_stg_rec IN item_stg_cur
      LOOP
         BEGIN
            DBMS_OUTPUT.put_line ('Entered Loop');

            SELECT qpll.list_line_id, qppa.pricing_attribute_id,
                   qpll.inventory_item_id
              INTO l_list_line_id, l_pricing_attribute_id,
                   l_item_id
              FROM qp_list_lines qpll, qp_pricing_attributes qppa
             WHERE qpll.list_line_id = qppa.list_line_id
               AND qpll.list_header_id = p_list_header_id
               AND qpll.inventory_item_id = item_stg_rec.item_id;

            DBMS_OUTPUT.put_line ('Retrieving The Price List');
            gpr_price_list_rec.list_header_id := p_list_header_id;
            gpr_price_list_rec.list_type_code := 'PRL';
            gpr_price_list_rec.NAME := 'Test_Price_List_API_990';
            gpr_price_list_rec.description := 'Test_Price_List_API_990';
            gpr_price_list_rec.currency_code := 'USD';
            pl_code := NULL;
            gpr_price_list_rec.operation := qp_globals.g_opr_update;
            gpr_pricing_attr_tbl.DELETE;
            k := 1;
            gpr_price_list_line_tbl (k).list_line_id := l_list_line_id;
            gpr_price_list_line_tbl (k).operation := qp_globals.g_opr_update;
            gpr_price_list_line_tbl (k).end_date_active := NULL;
            gpr_price_list_line_tbl (k).last_updated_by := v_userid;
            gpr_price_list_line_tbl (k).last_update_login := v_loginid;
            gpr_pricing_attr_tbl (k).pricing_attribute_id :=
                                                        l_pricing_attribute_id;
            gpr_pricing_attr_tbl (k).list_line_id := l_list_line_id;
            gpr_pricing_attr_tbl (k).product_attr_value := TO_CHAR (l_item_id);
            gpr_pricing_attr_tbl (k).operation := qp_globals.g_opr_update;

            BEGIN
               qp_price_list_pub.process_price_list
                   (p_api_version_number           => 1,
                    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_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
                   );
               DBMS_OUTPUT.put_line ('The Price List Line' || k
                                     || 'is inserted'
                                    );
               DBMS_OUTPUT.put_line
                                   (   'Price List line : '
                                    || gpr_pricing_attr_tbl (k).product_attr_value
                                   );
               COMMIT;

               BEGIN
                  UPDATE lss_unit_price_stg
                     SET status_stg = 'L'
                   WHERE transaction_id_stg = item_stg_rec.transaction_id_stg;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     DBMS_OUTPUT.put_line
                        (   'Excpetion when updating Staging table for Item(Updated Item)'
                         || l_item_id
                        );
               END;

               IF gpr_return_status <> 'S'
               THEN
                  oe_msg_pub.get (k,
                                  fnd_api.g_false,
                                  gpr_msg_data,
                                  l_msg_dummy
                                 );
                  l_output := (TO_CHAR (k) || ': ' || l_msg_data);
                  DBMS_OUTPUT.put_line (l_output);

                  BEGIN
                     UPDATE lss_unit_price_stg
                        SET status_stg = 'LE',
                            error_message = error_message || l_output || ';;'
                      WHERE transaction_id_stg =
                                               item_stg_rec.transaction_id_stg;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        DBMS_OUTPUT.put_line
                           (   'Excpetion when updating Staging table for Item(Update Failed)'
                            || l_item_id
                           );
                  END;
               END IF;
            EXCEPTION
               WHEN OTHERS
               THEN
                  BEGIN
                     UPDATE lss_unit_price_stg
                        SET status_stg = 'LE',
                            error_message =
                                       error_message || 'Unknown Exception ;;'
                      WHERE transaction_id_stg =
                                               item_stg_rec.transaction_id_stg;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        DBMS_OUTPUT.put_line
                           (   'Excpetion when updating Staging table for Item(Update Failed)'
                            || l_item_id
                           );
                  END;

                  DBMS_OUTPUT.put_line ('Error Processing The Price List...');
                  DBMS_OUTPUT.put_line (   'Price List Name : '
                                        || gpr_price_list_rec.NAME
                                       );
                  DBMS_OUTPUT.put_line (SQLERRM);
            END;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (SQLERRM);
         END;
      END LOOP;
   END unit_price_load;
END lss_unit_price_update_pkg;
/

3 comments:

  1. Hi,
    Dis is my Requirement,
    Should update ‘Pricelist ’ with standard cost.
    • If item not in ‘Pricelist’ , program should automatically add new item, cost & other required information.
    • Able to update column “Static Formula” with “Cost + 15 %”(Will change in future & need flexibility to change in program/definition of Quick code suggested).

    can u Please send me code for this requirement

    ReplyDelete
  2. Small bits of content which are explained in details,helps me understand the topic, thank you!

    GetDistributionSet

    ReplyDelete