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

  • October 14, 2015 | 15 views