Oracle eBS經驗分享

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

失效很久沒有使用的Price List : Disable not-used price list

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

DECLARE
   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_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_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;
   ppr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;
   ppr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_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;
  ------------=------------------------------==-------------------------
  -- Batch Inactive Price List which was not used from 01-JAN-2010
  -- Condition : Currency in (USD and TWD)
  ------------=------------------------------==-------------------------
   CURSOR cur_tmp
   IS
        SELECT t.list_header_id,
               t.NAME,
               b.active_flag,
               b.START_DATE_ACTIVE,
               b.END_DATE_ACTIVE,
               (SELECT user_name
                  FROM fnd_user
                 WHERE user_id = b.created_by)
                  created_by,
               b.creation_date
          FROM qp_list_headers_tl t, qp_list_headers_b b
         WHERE b.list_header_id = t.list_header_id
           AND t.LANGUAGE = 'US'
           --AND t.NAME NOT LIKE '%sample%'
           AND b.active_flag = 'Y'
           AND b.currency_code IN ('USD', 'TWD')
           AND b.list_type_code = 'PRL'
           AND NVL (b.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
           AND NOT EXISTS
                      (SELECT 1
                         FROM oe_order_lines_all
                        WHERE price_list_id = b.list_header_id
                          AND creation_date >= TO_DATE ('20100101', 'yyyymmdd')
                          AND cancelled_flag = 'N')
           AND t.creation_date <= TO_DATE ('20100101', 'yyyymmdd')
      ORDER BY b.creation_date;
BEGIN
   DBMS_OUTPUT.put_line (
      'Start to batch inactive price list book on '
      || TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:mi:ss'));

   FOR rec_tmp IN cur_tmp
   LOOP
      gpr_return_status := NULL;
      gpr_price_list_rec.operation := qp_globals.g_opr_update;
      gpr_price_list_rec.list_header_id := rec_tmp.list_header_id;
      gpr_price_list_rec.active_flag := 'N';
      gpr_price_list_rec.end_date_active := TRUNC (SYSDATE);

      BEGIN
         -- DBMS_OUTPUT.put_line ('process_price_list');
         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);
      EXCEPTION                                           --2009/02/10 Tinalee
         WHEN OTHERS
         THEN
            --   DBMS_OUTPUT.put_line ('  3*] Process Exception =>' || SQLERRM);
            gpr_return_status := 'Error';
      END;

      IF gpr_return_status <> fnd_api.g_ret_sts_success
      THEN
         ROLLBACK;                 -- must rollback insert data ... no mark !!
         gpr_msg_data :=
            oe_msg_pub.get (p_msg_index => gpr_msg_count, p_encoded => 'F');
         DBMS_OUTPUT.put_line (
               '   Error Message::=>id='
            || rec_tmp.list_header_id
            || '-'
            || SUBSTR (gpr_msg_data, 1, 239));
      ELSE
         --  DBMS_OUTPUT.put_line ('Success');
         COMMIT;
      END IF;
   END LOOP;
END;