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
  3. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete