Wednesday 14 October 2015

Sourcing Rule Conversion


Sourcing Rule:

  1. Sourcing Rule Provides list of approved suppliers where we can rank the suppliers on a percentage basis. This is useful for the buyers/requestors at the time of making purchase for sourcing rule item.Purchasing picks the name of the supplier on the rank basis for the purchase agreement and quotation to any requisition for this item.
     2. Sourcing Rule Can be Local or  Global(All Org).

Replenishment Methods:
  
1.  Make At          : It will make any items associated with the rule at receiving Organization.
2.  Buy From        : It will buy any items associated with the rule from the supplier.
3.  Transfer From : It specifies the different Organization with in the enterprise as source of items associated with this rule.


Possible Validations:

Allocation Percent must have Value.
Rank must have Value.
Effective Date should be equal to sysdate or greater than sysdate.
Source Type Must Have Value.
If Source type is “BUY FROM", Supplier is mandatory .
If Source type is “TRANSFER FROM”, Organization is must.


Code for reference:

PROCEDURE create_sourcing_rules
   IS
      lc_return_status          VARCHAR2 (1);
      ln_msg_count              NUMBER                                   := 0;
      lc_msg_data               VARCHAR2 (1000);
      lc_msg_data_temp          VARCHAR2 (1000);
      ln_msg_index_out          NUMBER;
      ln_count                  NUMBER;
      ln_err_count              NUMBER                                   := 0;
      l_sourcing_rule_rec       mrp_sourcing_rule_pub.sourcing_rule_rec_type;
      l_sourcing_rule_val_rec   mrp_sourcing_rule_pub.sourcing_rule_val_rec_type;
      l_receiving_org_tbl       mrp_sourcing_rule_pub.receiving_org_tbl_type;
      l_receiving_org_val_tbl   mrp_sourcing_rule_pub.receiving_org_val_tbl_type;
      l_shipping_org_tbl        mrp_sourcing_rule_pub.shipping_org_tbl_type;
      l_shipping_org_val_tbl    mrp_sourcing_rule_pub.shipping_org_val_tbl_type;
      o_sourcing_rule_rec       mrp_sourcing_rule_pub.sourcing_rule_rec_type;
      o_sourcing_rule_val_rec   mrp_sourcing_rule_pub.sourcing_rule_val_rec_type;
      o_receiving_org_tbl       mrp_sourcing_rule_pub.receiving_org_tbl_type;
      o_receiving_org_val_tbl   mrp_sourcing_rule_pub.receiving_org_val_tbl_type;
      o_shipping_org_tbl        mrp_sourcing_rule_pub.shipping_org_tbl_type;
      o_shipping_org_val_tbl    mrp_sourcing_rule_pub.shipping_org_val_tbl_type;
      ln_org_id                 NUMBER;
      ln_vendor_id              NUMBER;
      ln_ship_org               NUMBER;
      ln_source                 NUMBER;
      ln_rule_id                NUMBER;
      ln_receipt                NUMBER;
      ln_site_id                NUMBER;
   BEGIN
      fnd_file.put_line (fnd_file.LOG, 'create_sourcing_rules');
      fnd_global.apps_initialize (fnd_profile.VALUE ('USER_ID'),
                                  fnd_profile.VALUE ('RESP_ID'),
                                  fnd_profile.VALUE ('RESP_APPL_ID'),
                                  NULL,
                                  NULL
                                 );

      FOR i IN (SELECT *
                  FROM XX_STG_TBL
                 WHERE process_flag = 'V')
      LOOP
         ln_org_id := NULL;
         ln_vendor_id := NULL;
         ln_ship_org := NULL;
         ln_source := NULL;
         ln_rule_id := NULL;
         ln_receipt := NULL;
         ln_site_id := NULL;

         BEGIN
            SELECT organization_id
              INTO ln_org_id
              FROM org_organization_definitions
             WHERE organization_code = i.org_code;
         EXCEPTION
            WHEN OTHERS
            THEN
               ln_org_id := NULL;
               DBMS_OUTPUT.put_line ('No Org Exists' || i.org_code);
               fnd_file.put_line (fnd_file.LOG,
                                  'No Org Exists' || i.org_code);
         END;

         BEGIN
            SELECT lookup_code
              INTO ln_source
              FROM mfg_lookups
             WHERE lookup_type = 'MRP_SOURCE_TYPE'
               AND UPPER (meaning) = UPPER (TRIM (i.source_type));
         EXCEPTION
            WHEN OTHERS
            THEN
               ln_ship_org := NULL;
               DBMS_OUTPUT.put_line ('No shipping Org Exists'
                                     || i.shipping_org
                                    );
               fnd_file.put_line (fnd_file.LOG,
                                     'Error in Selecting Lookup Code'
                                  || i.source_type
                                 );
         END;

         BEGIN
            SELECT organization_id
              INTO ln_ship_org
              FROM org_organization_definitions
             WHERE organization_code = i.shipping_org;
         EXCEPTION
            WHEN OTHERS
            THEN
               ln_ship_org := NULL;
               DBMS_OUTPUT.put_line ('No shipping Org Exists'
                                     || i.shipping_org
                                    );
               fnd_file.put_line (fnd_file.LOG,
                                  'No shipping Org Exists' || i.shipping_org
                                 );
         END;

         BEGIN
            SELECT vendor_id
              INTO ln_vendor_id
              FROM ap_suppliers
             WHERE TRIM (vendor_name) = i.supplier_name;
         EXCEPTION
            WHEN OTHERS
            THEN
               fnd_file.put_line (fnd_file.LOG,
                                     'Error in Selecting Vendor id'
                                  || i.supplier_name
                                 );
         END;

         BEGIN
            SELECT vendor_site_id
              INTO ln_site_id
              FROM ap_supplier_sites_all
             WHERE vendor_id = ln_vendor_id
               AND TRIM (vendor_site_code) = i.vendor_site_code;
         EXCEPTION
            WHEN OTHERS
            THEN
               fnd_file.put_line (fnd_file.LOG,
                                     'Error in Selecting Vendor Site'
                                  || ln_vendor_id
                                 );
         END;

         l_sourcing_rule_rec := mrp_sourcing_rule_pub.g_miss_sourcing_rule_rec;
         l_receiving_org_tbl := mrp_sourcing_rule_pub.g_miss_receiving_org_tbl;
         l_shipping_org_tbl := mrp_sourcing_rule_pub.g_miss_shipping_org_tbl;

         BEGIN
            SELECT sourcing_rule_id
              INTO ln_rule_id
              FROM mrp_sourcing_rules
             WHERE sourcing_rule_name = i.sourcing_rule_name
               AND NVL (organization_id, 0) = NVL (ln_org_id, 0);
         EXCEPTION
            WHEN OTHERS
            THEN
               ln_rule_id := NULL;
         END;

         IF ln_rule_id IS NULL
         THEN
            l_sourcing_rule_rec.operation := 'CREATE';
            l_receiving_org_tbl (1).operation := 'CREATE';
         ELSE
            SELECT sr_receipt_id
              INTO ln_receipt
              FROM mrp_sr_receipt_org
             WHERE sourcing_rule_id = ln_rule_id;

            l_sourcing_rule_rec.sourcing_rule_id := ln_rule_id;
            l_receiving_org_tbl (1).operation := 'UPDATE';
            l_receiving_org_tbl (1).sr_receipt_id := ln_receipt;
            l_shipping_org_tbl (1).sr_receipt_id := ln_receipt;
         END IF;

         l_sourcing_rule_rec.sourcing_rule_name := i.sourcing_rule_name;
         l_sourcing_rule_rec.description := i.description;
         l_sourcing_rule_rec.organization_id := ln_org_id;
         l_sourcing_rule_rec.planning_active := 1;
         l_sourcing_rule_rec.status := 1;
         l_sourcing_rule_rec.sourcing_rule_type := 1;
           --l_sourcing_rule_rec.operation := 'CREATE';
         --  l_receiving_org_tbl := mrp_sourcing_rule_pub.g_miss_receiving_org_tbl;
          -- l_shipping_org_tbl := mrp_sourcing_rule_pub.g_miss_shipping_org_tbl;
         l_receiving_org_tbl (1).effective_date := i.from_date;
         l_receiving_org_tbl (1).disable_date := i.TO_DATE;
         l_receiving_org_tbl (1).receipt_organization_id := ln_org_id;
         --l_receiving_org_tbl (1).sr_receipt_id := 21008;
         l_shipping_org_tbl (1).RANK := i.RANK;
         l_shipping_org_tbl (1).allocation_percent := i.allocation_percent;
         l_shipping_org_tbl (1).source_type := ln_source;
         l_shipping_org_tbl (1).receiving_org_index := 1;
         l_shipping_org_tbl (1).operation := 'CREATE';

         IF UPPER (i.source_type) LIKE 'BUY%'
         THEN
            l_shipping_org_tbl (1).vendor_id := ln_vendor_id;
            l_shipping_org_tbl (1).source_organization_id := NULL;
            l_shipping_org_tbl (1).vendor_site_id := ln_site_id;
         ELSE
            l_shipping_org_tbl (1).source_organization_id := ln_ship_org;
            mrp_sourcing_rule_pub.process_sourcing_rule
                          (p_api_version_number         => 1.0,
                           p_init_msg_list              => fnd_api.g_true,
                           p_commit                     => fnd_api.g_true,
                           x_return_status              => lc_return_status,
                           x_msg_count                  => ln_msg_count,
                           x_msg_data                   => lc_msg_data,
                           p_sourcing_rule_rec          => l_sourcing_rule_rec,
                           p_sourcing_rule_val_rec      => l_sourcing_rule_val_rec,
                           p_receiving_org_tbl          => l_receiving_org_tbl,
                           p_receiving_org_val_tbl      => l_receiving_org_val_tbl,
                           p_shipping_org_tbl           => l_shipping_org_tbl,
                           p_shipping_org_val_tbl       => l_shipping_org_val_tbl,
                           x_sourcing_rule_rec          => o_sourcing_rule_rec,
                           x_sourcing_rule_val_rec      => o_sourcing_rule_val_rec,
                           x_receiving_org_tbl          => o_receiving_org_tbl,
                           x_receiving_org_val_tbl      => o_receiving_org_val_tbl,
                           x_shipping_org_tbl           => o_shipping_org_tbl,
                           x_shipping_org_val_tbl       => o_shipping_org_val_tbl
                          );

         IF lc_return_status = fnd_api.g_ret_sts_success
         THEN
            DBMS_OUTPUT.put_line ('Success!');

            UPDATE XX_STG_TBL
               SET process_flag = 'S',
                   sourcing_rule_id = o_sourcing_rule_rec.sourcing_rule_id
             WHERE sourcing_rule_name = i.sourcing_rule_name
               AND record_id = i.record_id;

            fnd_file.put_line (fnd_file.LOG, 'Success');
         ELSE
            DBMS_OUTPUT.put_line ('count:' || ln_msg_count);

            IF ln_msg_count > 0
            THEN
               FOR l_index IN 1 .. ln_msg_count
               LOOP
                  lc_msg_data := lc_msg_data || CHR (13) || lc_msg_data_temp;
                  lc_msg_data_temp :=
                     fnd_msg_pub.get (p_msg_index      => l_index,
                                      p_encoded        => fnd_api.g_false
                                     );
                  DBMS_OUTPUT.put_line (SUBSTR (lc_msg_data, 1, 250));

                  BEGIN
                     UPDATE xx_mrp_stg
                        SET process_flag = 'IE',
                            error_message = lc_msg_data_temp
                      WHERE sourcing_rule_name = i.sourcing_rule_name
                        AND record_id = i.record_id;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        DBMS_OUTPUT.put_line ('Error in Update');
                        fnd_file.put_line (fnd_file.LOG,
                                           'Error in Update' || SQLERRM
                                          );
                  END;

                  COMMIT;
               END LOOP;

               DBMS_OUTPUT.put_line (   'MSG:'
                                     || o_sourcing_rule_rec.return_status
                                    );
            END IF;

            DBMS_OUTPUT.put_line ('Failure!');
            fnd_file.put_line (fnd_file.LOG, 'Failure');
         END IF;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'Error in Calling Api ' || SQLERRM);
   END;

  
Base Tables :

MRP_SOURCING_RULES
MRP_SR_SOURCE_ORG
MRP_SR_RECEIPT_ORG


1 comment:

  1. Can you please provide the columns fro the staging tables

    ReplyDelete