iProcurement Bulk Inactivation/Deletion of Catalog Items

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

To delete items from iProcurement Catalog/Category tables we can use the following APIs

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

--API to delete items from Catalog

    ICX_POR_DELETE_CATALOG.delete_items_in_catalog (p_catalog_name        IN VARCHAR2);

   

    --API to delete items from Category

    ICX_POR_DELETE_CATALOG.delete_items_in_category (

                                    errbuf            OUT NOCOPY VARCHAR2,

                                    retcode           OUT NOCOPY VARCHAR2,

                                    p_rt_category_id   IN NUMBER,

                                    p_category_key     IN VARCHAR2);

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

To delete Item-Category assignment use -  INV_ITEM_CATEGORY_PUB.Delete_Category_Assignment standard API. Following is the sample code:-

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

DECLARE

   l_return_status       VARCHAR2 (80);

   l_error_code          NUMBER;

   l_msg_count           NUMBER;

   l_msg_data            VARCHAR2 (80);

   l_category_id         NUMBER;

   l_category_set_id     NUMBER;

   l_inventory_item_id   NUMBER;

   l_organization_id     NUMBER;

BEGIN

   SELECT mcs_tl.category_set_id

     INTO l_category_set_id

     FROM mtl_category_sets_tl mcs_tl

    WHERE mcs_tl.category_set_name = 'INV_COLORS_SET';

   SELECT mcb.category_id

     INTO l_category_id

     FROM mtl_categories_b mcb

    WHERE mcb.segment1 = 'RED'

      AND mcb.structure_id =

             (SELECT mcs_b.structure_id

                FROM mtl_category_sets_b mcs_b

               WHERE mcs_b.category_set_id =

                                 (SELECT mcs_tl.category_set_id

                                    FROM mtl_category_sets_tl mcs_tl

                                   WHERE category_set_name = 'INV_COLORS_SET'));

   SELECT DISTINCT (inventory_item_id)

              INTO l_inventory_item_id

              FROM mtl_system_items_kfv msik

             WHERE msik.concatenated_segments = 'Category_Item';

   SELECT organization_id

     INTO l_organization_id

     FROM mtl_parameters

    WHERE organization_code = &P_ORG_CODE;

   inv_item_category_pub.delete_category_assignment

                                  (p_api_version            => 1.0,

                                   p_init_msg_list          => fnd_api.g_false,

                                   p_commit                 => fnd_api.g_true,

                                   x_return_status          => l_return_status,

                                   x_errorcode              => l_error_code,

                                   x_msg_count              => l_msg_count,

                                   x_msg_data               => l_msg_data,

                                   p_category_id            => l_category_id,

                                   p_category_set_id        => l_category_set_id,

                                   p_inventory_item_id      => l_inventory_item_id,

                                   p_organization_id        => l_organization_id

                                  );

END;

How to create a Quote using ASO_QUOTE_PUB

DECLARE
   -- pragma autonomous_transaction;
   p_api_version_number        NUMBER;
   p_init_msg_list                    VARCHAR2 (32767);
   p_commit                             VARCHAR2 (32767);
   p_control_rec                       aso_quote_pub.control_rec_type;
   p_qte_header_rec                 aso_quote_pub.qte_header_rec_type;
   p_hd_price_attributes_tbl    aso_quote_pub.price_attributes_tbl_type;
   p_hd_payment_tbl               aso_quote_pub.payment_tbl_type;
   p_hd_shipment_rec             aso_quote_pub.shipment_rec_type;
   p_hd_freight_charge_tbl     aso_quote_pub.freight_charge_tbl_type;
   p_hd_tax_detail_tbl            aso_quote_pub.tax_detail_tbl_type;
   p_qte_line_tbl                     aso_quote_pub.qte_line_tbl_type;
   p_qte_line_dtl_tbl               aso_quote_pub.qte_line_dtl_tbl_type;
   p_line_attr_ext_tbl              aso_quote_pub.line_attribs_ext_tbl_type;
   p_line_rltship_tbl                aso_quote_pub.line_rltship_tbl_type;
   p_price_adjustment_tbl       aso_quote_pub.price_adj_tbl_type;
   p_price_adj_attr_tbl            aso_quote_pub.price_adj_attr_tbl_type;
   p_price_adj_rltship_tbl       aso_quote_pub.price_adj_rltship_tbl_type;
   p_ln_price_attributes_tbl    aso_quote_pub.price_attributes_tbl_type;
   p_ln_payment_tbl               aso_quote_pub.payment_tbl_type;
   p_ln_shipment_tbl              aso_quote_pub.shipment_tbl_type;
   p_ln_freight_charge_tbl     aso_quote_pub.freight_charge_tbl_type;
   p_ln_tax_detail_tbl            aso_quote_pub.tax_detail_tbl_type;
   x_qte_header_rec              aso_quote_pub.qte_header_rec_type;
   x_qte_line_tbl                   aso_quote_pub.qte_line_tbl_type;
   x_qte_line_dtl_tbl             aso_quote_pub.qte_line_dtl_tbl_type;
   x_hd_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
   x_hd_payment_tbl            aso_quote_pub.payment_tbl_type;
   x_hd_shipment_rec           aso_quote_pub.shipment_rec_type;
   x_hd_freight_charge_tbl   aso_quote_pub.freight_charge_tbl_type;
   x_hd_tax_detail_tbl          aso_quote_pub.tax_detail_tbl_type;
   x_line_attr_ext_tbl           aso_quote_pub.line_attribs_ext_tbl_type;
   x_line_rltship_tbl             aso_quote_pub.line_rltship_tbl_type;
   x_price_adjustment_tbl    aso_quote_pub.price_adj_tbl_type;
   x_price_adj_attr_tbl         aso_quote_pub.price_adj_attr_tbl_type;
   x_price_adj_rltship_tbl    aso_quote_pub.price_adj_rltship_tbl_type;
   x_ln_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
   x_ln_payment_tbl            aso_quote_pub.payment_tbl_type;
   x_ln_shipment_tbl           aso_quote_pub.shipment_tbl_type;
   x_ln_freight_charge_tbl  aso_quote_pub.freight_charge_tbl_type;
   x_ln_tax_detail_tbl         aso_quote_pub.tax_detail_tbl_type;
   v_party_id                      NUMBER;
   v_account_id                  NUMBER;
   v_user_id                        NUMBER;
   v_org_id                         NUMBER;
   v_party_site_id              NUMBER;
   v_order_type_id            NUMBER;
   v_quote_name               VARCHAR2 (200);
   v_currency                    VARCHAR2 (10);
   x_return_status             VARCHAR2 (1);
   x_msg_data                  VARCHAR2 (4000);
   x_msg_count                NUMBER;
   x_cost_quote_header_id      VARCHAR2 (1000);
   p_quote_header_id               NUMBER := &p_quote_hdr_id;
   v_pricelist_id                        NUMBER;
   v_item_num                          VARCHAR2 (30) := &p_item_num;
   v_inv_org_code                    VARCHAR2 (50) := &p_invorg_code;
   v_item_id                             NUMBER;
   v_inv_org_id                        NUMBER;
   v_resp_id                              NUMBER := fnd_profile.value('RESP_ID');
   v_appl_id                              NUMBER := fnd_profile.value('APPL_ID');
BEGIN
   fnd_global.APPS_INITIALIZE (v_user_id, v_resp_id, v_appl_id);
   p_api_version_number := 1.0;
   p_init_msg_list := fnd_api.g_true;
   p_commit := fnd_api.g_false;

   -- P_CONTROL_REC := NULL;  Modify the code to initialize this parameter

   SELECT cust_party_id,
          order_type_id,
          SUBSTR (quote_name, 1, 200),
          org_id,
          created_by,
          currency_code,
          quote_source_code,
          resource_id,
          resource_grp_id
     INTO v_party_id,
          v_order_type_id,
          v_quote_name,
          v_org_id,
          v_user_id,
          v_currency,
          p_qte_header_rec.quote_source_code,
          p_qte_header_rec.resource_id,
          p_qte_header_rec.resource_grp_id
     FROM aso_quote_headers_all
    WHERE quote_header_id = p_quote_header_id;

   DBMS_OUTPUT.put_line ('after party id' || v_party_id);

   -- Find the cust account primary bill site's party site id
   BEGIN
      SELECT hca.cust_account_id, cas.party_site_id
        INTO v_account_id, v_party_site_id
        FROM hz_parties party,
             hz_cust_accounts hca,
             hz_cust_acct_sites_all cas,
             hz_cust_site_uses_all csu
       WHERE     party.party_id = v_party_id
             AND hca.party_id = party.party_id
             AND hca.cust_account_id = v_account_id
             AND cas.cust_account_id = hca.cust_account_id
             AND cas.status = 'A'
             AND cas.cust_acct_site_id = csu.cust_acct_site_id
             AND csu.site_use_code = 'BILL_TO'
             AND csu.primary_flag = 'Y'
             AND csu.status = 'A'
             AND csu.org_id = v_org_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         BEGIN
            SELECT ps.party_site_id
              INTO v_party_site_id
              FROM hz_parties party,
                   hz_party_sites ps,
                   hz_party_site_uses psu
             WHERE     party.party_id = v_party_id
                   AND party.party_id = ps.party_id
                   AND ps.party_site_id = psu.party_site_id
                   AND ps.status = 'A'
                   AND psu.site_use_type = 'BILL_TO'
                   AND psu.primary_per_type = 'Y'
                   AND psu.status = 'A';
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               SELECT ps.party_site_id
                 INTO v_party_site_id
                 FROM hz_parties party, hz_party_sites ps
                WHERE     party.party_id = v_party_id
                      AND party.party_id = ps.party_id
                      AND ps.status = 'A'
                      AND ROWNUM < 2;
         END;
   END;

   -- p_qte_header_rec := NULL;  Modify the code to initialize this parameter
   -----------------------------------------------------
   -- build the header and shipment rec to pass to Oracle's API
   -----------------------------------------------------
   v_user_id := fnd_global.user_id;
   v_org_id := fnd_global.org_id;
   p_qte_header_rec.created_by := v_user_id;
   p_qte_header_rec.last_updated_by := v_user_id;
   p_qte_header_rec.org_id := v_org_id;
   p_qte_header_rec.quote_source_code := 'Order Capture Quotes';

   p_qte_header_rec.cust_party_id := v_party_id;
   p_qte_header_rec.cust_account_id := v_account_id;
   p_qte_header_rec.invoice_to_cust_account_id := v_account_id;
   p_qte_header_rec.invoice_to_party_site_id := v_party_site_id;
   p_qte_header_rec.invoice_to_cust_party_id := v_party_id;
   p_qte_header_rec.quote_name := SUBSTR ('Test Quote', 1, 80);
   p_qte_header_rec.currency_code := v_currency;
   p_qte_header_rec.order_type_id := v_order_type_id;


   --p_qte_header_rec.quote_status_id;
   --COST ESTIMATION
   SELECT quote_status_id
     INTO p_qte_header_rec.quote_status_id
     FROM aso_quote_statuses_vl
    WHERE status_code = 'DRAFT';

   --p_qte_header_rec.price_list_id ;
   --cost price list
   SELECT list_header_id
     INTO p_qte_header_rec.price_list_id
     FROM qp_list_headers_vl
    WHERE list_header_id = v_pricelist_id;

   --AND list_type_code = 'PRL';

   p_hd_shipment_rec.operation_code := 'CREATE';
   p_hd_shipment_rec.ship_to_party_site_id := v_party_site_id;


   SELECT inventory_item_id, organization_id
     INTO v_item_id, v_inv_org_id
     FROM mtl_system_items_b msi, mtl_parameters mp
    WHERE     msi.segment1 = v_item_num
          AND msi.organization_id = mp.organization_id
          AND mp.organization_code = v_inv_org_code;

   ----------------- LINES ---------------------------------
   P_Qte_Line_Tbl (1).organization_id := v_inv_org_id;
   P_Qte_Line_Tbl (1).operation_code := 'CREATE';
   P_Qte_Line_Tbl (1).inventory_item_id := v_item_id;
   P_Qte_Line_Tbl (1).quantity := 1;
   P_Qte_Line_Tbl (1).uom_code := 'EA';
   P_Qte_Line_Tbl (1).line_category_code := 'ORDER';

   x_return_status := NULL;
   x_msg_count := NULL;
   x_msg_data := NULL;

   DBMS_OUTPUT.put_line ('before creating quote');

   apps.aso_quote_pub.create_quote (p_api_version_number,
                                    p_init_msg_list,
                                    p_commit,
                                    p_control_rec,
                                    p_qte_header_rec,
                                    p_hd_price_attributes_tbl,
                                    p_hd_payment_tbl,
                                    p_hd_shipment_rec,
                                    p_hd_freight_charge_tbl,
                                    p_hd_tax_detail_tbl,
                                    p_qte_line_tbl,
                                    p_qte_line_dtl_tbl,
                                    p_line_attr_ext_tbl,
                                    p_line_rltship_tbl,
                                    p_price_adjustment_tbl,
                                    p_price_adj_attr_tbl,
                                    p_price_adj_rltship_tbl,
                                    p_ln_price_attributes_tbl,
                                    p_ln_payment_tbl,
                                    p_ln_shipment_tbl,
                                    p_ln_freight_charge_tbl,
                                    p_ln_tax_detail_tbl,
                                    x_qte_header_rec,
                                    x_qte_line_tbl,
                                    x_qte_line_dtl_tbl,
                                    x_hd_price_attributes_tbl,
                                    x_hd_payment_tbl,
                                    x_hd_shipment_rec,
                                    x_hd_freight_charge_tbl,
                                    x_hd_tax_detail_tbl,
                                    x_line_attr_ext_tbl,
                                    x_line_rltship_tbl,
                                    x_price_adjustment_tbl,
                                    x_price_adj_attr_tbl,
                                    x_price_adj_rltship_tbl,
                                    x_ln_price_attributes_tbl,
                                    x_ln_payment_tbl,
                                    x_ln_shipment_tbl,
                                    x_ln_freight_charge_tbl,
                                    x_ln_tax_detail_tbl,
                                    x_return_status,
                                    x_msg_count,
                                    x_msg_data);
   DBMS_OUTPUT.put_line ('x_qte_header_rec.quote_header_id = '
                                                || x_qte_header_rec.quote_header_id);
   DBMS_OUTPUT.put_line ('x_return_status = ' || x_return_status);
   DBMS_OUTPUT.put_line ('x_msg_count = ' || x_msg_count);
   DBMS_OUTPUT.put_line ('x_msg_data = ' || x_msg_data);

   IF x_qte_header_rec.quote_header_id IS NOT NULL
   THEN
      x_cost_quote_header_id := x_qte_header_rec.quote_header_id;
   END IF;

   FOR i IN 1 .. x_msg_count
   LOOP
      DBMS_OUTPUT.put_line ('Error ' || fnd_msg_pub.get (i, 'F'));
   END LOOP;

   DBMS_OUTPUT.put_line ('end cost api');
END;
/