Sunday, 28 April 2013

PRICE LIST UPLOAD THE DATA

/* Formatted on 2012/05/05 17:16 (Formatter Plus v4.8.8) */
DECLARE
   CURSOR c1
   IS
      (SELECT xpl.new_price, f.segment1 item_code, f.inventory_item_id,
              f.primary_uom_code, a.NAME, a.list_header_id, b.list_line_id
         FROM qp_list_headers_tl a,
              qp_list_lines b,
              qp_pricing_attributes c,
              mtl_system_items_b f,
              xxhmi_price_xll xpl
        WHERE a.list_header_id = b.list_header_id
          AND b.list_line_id = c.list_line_id
          AND c.product_attr_value = TO_CHAR (f.inventory_item_id)
          AND f.segment1 = xpl.item_code
          AND xpl.price_header = a.NAME
          AND f.organization_id = 86
--          AND TRUNC (b.start_date_active) = TRUNC (SYSDATE)
--          AND xpl.price_header = '2W AS DLP Price List'
          AND b.start_date_active =
                 (SELECT MAX (k.start_date_active)
                    FROM qp_list_lines k, qp_pricing_attributes g
                   WHERE k.list_line_id = g.list_line_id
                     AND g.product_attr_value = c.product_attr_value
                     AND k.list_header_id = a.list_header_id));

--                  AND ROWNUM <= 1000;
   gpr_return_status             VARCHAR2 (1)                          := 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;
   k                             NUMBER                                   := 1;
   j                             NUMBER                                   := 1;
   x_debug_file                  VARCHAR2 (100);
BEGIN
   --   oe_msg_pub.initialize;
   --   oe_debug_pub.initialize;
   --   X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode ('FILE');
   --   oe_debug_pub.SetDebugLevel (5);
   FOR i IN c1
   LOOP
      gpr_price_list_rec.list_header_id := i.list_header_id;
      --- 10015 RTLP --11015 MRP  ---10049 DLP
      --gpr_price_list_rec.name := 'MRP Price List';
      gpr_price_list_rec.list_type_code := 'PRL';
      gpr_price_list_rec.description := NULL;
      gpr_price_list_rec.operation := qp_globals.g_opr_update;
      k := 1;
      gpr_price_list_line_tbl (k).list_line_id := i.list_line_id;
      gpr_price_list_line_tbl (k).operation := qp_globals.g_opr_update;
      gpr_price_list_line_tbl (k).end_date_active := SYSDATE - 5;
      k := 2;
      -- create the price list line rec
      gpr_price_list_line_tbl (k).list_header_id := i.list_header_id;
      ---11015; DLP ---10049 DLP 10049;
      gpr_price_list_line_tbl (k).list_line_id := fnd_api.g_miss_num;
      gpr_price_list_line_tbl (k).list_line_type_code := 'PLL';
      gpr_price_list_line_tbl (k).operation := qp_globals.g_opr_create;
      gpr_price_list_line_tbl (k).operand := i.new_price;
      --77.00;      -- value of unit selling
      gpr_price_list_line_tbl (k).arithmetic_operator := 'UNIT_PRICE';
      gpr_price_list_line_tbl (k).start_date_active := SYSDATE - 4;
      gpr_price_list_line_tbl (k).end_date_active := '31-MAR-2099';
      gpr_price_list_line_tbl (k).modifier_level_code := 'LINE';
      gpr_price_list_line_tbl (k).automatic_flag := 'Y';
      gpr_price_list_line_tbl (k).reprice_flag := 'Y';
      j := 1;
      gpr_pricing_attr_tbl (j).pricing_attribute_id := fnd_api.g_miss_num;
      gpr_pricing_attr_tbl (j).list_line_id := fnd_api.g_miss_num;
      gpr_pricing_attr_tbl (j).product_attribute_context := 'ITEM';
      gpr_pricing_attr_tbl (j).product_attribute := 'PRICING_ATTRIBUTE1';
      gpr_pricing_attr_tbl (j).product_attr_value := i.inventory_item_id;
      gpr_pricing_attr_tbl (j).product_uom_code := i.primary_uom_code;
      --- item UOM
      gpr_pricing_attr_tbl (j).excluder_flag := 'N';
      --gpr_pricing_attr_tbl (J).ATTRIBUTE_GROUPING_NO := 1;
      gpr_pricing_attr_tbl (j).price_list_line_index := 2;
      gpr_pricing_attr_tbl (j).operation := qp_globals.g_opr_create;
      DBMS_OUTPUT.put_line ('before process price list ');
      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_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
                   );
      COMMIT;

--      DBMS_OUTPUT.put_line ('after process price list ');
--      DBMS_OUTPUT.put_line ('return Status ' || gpr_return_status);
--      DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_data);

      --   oe_debug_pub.add ('yusaito after calling process_price_list API');
      --   DBMS_OUTPUT.put_line (
      --      'OM Debug file: ' || oe_debug_pub.G_DIR || '/' || oe_debug_pub.G_FILE
      --   );
      IF gpr_return_status <> fnd_api.g_ret_sts_success
      THEN
         RAISE fnd_api.g_exc_unexpected_error;
      END IF;
   END LOOP;
--   DBMS_OUTPUT.put_line ('after process price list ');
--   DBMS_OUTPUT.put_line ('return Status ' || gpr_return_status);
--   DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_count);
--   DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_data);
EXCEPTION
   WHEN fnd_api.g_exc_error
   THEN
--      DBMS_OUTPUT.put_line ('after process price list ');
--      DBMS_OUTPUT.put_line ('return Status ' || gpr_return_status);
--      DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_count);
--      DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_data);
--      gpr_return_status := fnd_api.g_ret_sts_error;
      ROLLBACK;
   -- Get message count and data

   --dbms_output.put_line('err msg 1 is : ' || gpr_msg_data);
   WHEN fnd_api.g_exc_unexpected_error
   THEN
      gpr_return_status := fnd_api.g_ret_sts_unexp_error;
      ROLLBACK;

      --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);
         NULL;
      END LOOP;
   WHEN OTHERS
   THEN
      gpr_return_status := fnd_api.g_ret_sts_unexp_error;
      ROLLBACK;
-- Get message count and data
--dbms_output.put_line('err msg 3 is : ' || gpr_msg_data);
END;



---------------------------------------------------------------------------------------------------------

2nd SCRIPT

/* Formatted on 2012/05/04 16:20 (Formatter Plus v4.8.8) */
DECLARE
   CURSOR c1
   IS
      SELECT DISTINCT xx.new_price, mtl.segment1 item_code,
                      mtl.inventory_item_id, mtl.primary_uom_code,
                      (SELECT x1.list_header_id
                         FROM qp_list_headers_v x1
                        WHERE x1.NAME = xx.price_header) list_header_id
                 FROM mtl_system_items_b mtl, xxhmi_price_xll xx
                WHERE mtl.segment1 = xx.item_code
                  AND mtl.organization_id = 86
                  AND mtl.inventory_item_id NOT IN (
                         SELECT DISTINCT msi.inventory_item_id
                                    FROM mtl_system_items_b msi,
                                         xxhmi_price_xll xx1,
                                         qp_list_lines b,
                                         qp_pricing_attributes c
                                   WHERE msi.segment1 = xx1.item_code
                                     AND msi.organization_id = 86
                                     AND b.list_line_id = c.list_line_id
                                     AND xx.price_header = xx1.price_header
                                     AND b.list_header_id IN (
                                              SELECT x1.list_header_id
                                                FROM qp_list_headers_tl x1
                                               WHERE x1.NAME =
                                                              xx1.price_header)
                                     ---=10015  --- 10015 RTLP --11015 MRP  ---10049 DLP
                                     AND c.product_attribute_context = 'ITEM'
                                     AND c.product_attribute =
                                                          'PRICING_ATTRIBUTE1'
                                     AND c.product_attr_value =
                                                         msi.inventory_item_id);

   --AND mtl.segment1 IN ('72710TG1T01', '72711TG2K01');
   gpr_return_status             VARCHAR2 (1)                          := 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;
   k                             NUMBER                                   := 1;
   j                             NUMBER                                   := 1;
   x_debug_file                  VARCHAR2 (100);
BEGIN
   --   oe_msg_pub.initialize;
   --   oe_debug_pub.initialize;
   --   X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode ('FILE');
   --   oe_debug_pub.SetDebugLevel (5);
   FOR i IN c1
   LOOP
      gpr_price_list_rec.list_header_id := i.list_header_id;
                                     --- 10015 RTLP --11015 MRP  ---10049 DLP
      --gpr_price_list_rec.name := 'MRP Price List';
      gpr_price_list_rec.list_type_code := 'PRL';
      gpr_price_list_rec.description := NULL;
      gpr_price_list_rec.operation := qp_globals.g_opr_update;
      k := 1;
      -- create the price list line rec
      gpr_price_list_line_tbl (k).list_header_id := i.list_header_id;
      ---11015; DLP ---10049 DLP 10049;
      gpr_price_list_line_tbl (k).list_line_id := fnd_api.g_miss_num;
      gpr_price_list_line_tbl (k).list_line_type_code := 'PLL';
      gpr_price_list_line_tbl (k).operation := qp_globals.g_opr_create;
      gpr_price_list_line_tbl (k).operand := i.new_price;
      --77.00;      -- value of unit selling
      gpr_price_list_line_tbl (k).arithmetic_operator := 'UNIT_PRICE';
      gpr_price_list_line_tbl (k).start_date_active := SYSDATE - 3;
      gpr_price_list_line_tbl (k).end_date_active := '31-MAR-2099';
      gpr_price_list_line_tbl (k).modifier_level_code := 'LINE';
      gpr_price_list_line_tbl (k).automatic_flag := 'Y';
      gpr_price_list_line_tbl (k).reprice_flag := 'Y';
      j := 1;
      gpr_pricing_attr_tbl (j).pricing_attribute_id := fnd_api.g_miss_num;
      gpr_pricing_attr_tbl (j).list_line_id := fnd_api.g_miss_num;
      gpr_pricing_attr_tbl (j).product_attribute_context := 'ITEM';
      gpr_pricing_attr_tbl (j).product_attribute := 'PRICING_ATTRIBUTE1';
      gpr_pricing_attr_tbl (j).product_attr_value := i.inventory_item_id;
                                                                   --'92916';
      -- inventory item id
      gpr_pricing_attr_tbl (j).product_uom_code := i.primary_uom_code;
      --- item UOM
      gpr_pricing_attr_tbl (j).excluder_flag := 'N';
      --gpr_pricing_attr_tbl (J).ATTRIBUTE_GROUPING_NO := 1;
      gpr_pricing_attr_tbl (j).price_list_line_index := 1;
      gpr_pricing_attr_tbl (j).operation := qp_globals.g_opr_create;
      DBMS_OUTPUT.put_line ('before process price list ');
      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_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
                   );
      COMMIT;
      DBMS_OUTPUT.put_line ('after process price list ');
      DBMS_OUTPUT.put_line ('return Status ' || gpr_return_status);
      DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_data);

      --   oe_debug_pub.add ('yusaito after calling process_price_list API');
      --   DBMS_OUTPUT.put_line (
      --      'OM Debug file: ' || oe_debug_pub.G_DIR || '/' || oe_debug_pub.G_FILE
      --   );
      IF gpr_return_status <> fnd_api.g_ret_sts_success
      THEN
         RAISE fnd_api.g_exc_unexpected_error;
      END IF;
   END LOOP;

   DBMS_OUTPUT.put_line ('after process price list ');
   DBMS_OUTPUT.put_line ('return Status ' || gpr_return_status);
   DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_count);
   DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_data);
EXCEPTION
   WHEN fnd_api.g_exc_error
   THEN
      DBMS_OUTPUT.put_line ('after process price list ');
      DBMS_OUTPUT.put_line ('return Status ' || gpr_return_status);
      DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_count);
      DBMS_OUTPUT.put_line ('after process price list ' || gpr_msg_data);
      gpr_return_status := fnd_api.g_ret_sts_error;
      ROLLBACK;
   -- Get message count and data

   --dbms_output.put_line('err msg 1 is : ' || gpr_msg_data);
   WHEN fnd_api.g_exc_unexpected_error
   THEN
      gpr_return_status := fnd_api.g_ret_sts_unexp_error;
      ROLLBACK;

      --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);
         NULL;
      END LOOP;
   WHEN OTHERS
   THEN
      gpr_return_status := fnd_api.g_ret_sts_unexp_error;
      ROLLBACK;
-- Get message count and data
--dbms_output.put_line('err msg 3 is : ' || gpr_msg_data);
END;
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)