Supplier Conversion in R12 Oracle apps.

Supplier Conversion in R12 Oracle apps.

While doing supplier conversion in R12, below columns in staging table are mandatory or the related setups. I will mention those and their validation.


Note: c_rec.<column_name> columns are from the staging table

Record types variables for creating supplier and supplier site.

  l_vendor_rec                 ap_vendor_pub_pkg.r_vendor_rec_type;
  l_vendor_site_rec             ap_vendor_pub_pkg.r_vendor_site_rec_type;


  x_vendor_id                   NUMBER;
  x_party_id                    NUMBER;
  x_vendor_site_id              NUMBER;
  x_party_site_id               NUMBER;
  x_location_id                 NUMBER;

---------------------------------------------------
-->> Currency Code <<--
---------------------------------------------------

SELECT currency_code
INTO l_invoice_currency
FROM fnd_currencies
WHERE UPPER(currency_code)=UPPER(TRIM(c_rec.currency)) -- Value from the staging table



---------------------------------------------------
-->> Payment Term <<--
---------------------------------------------------
SELECT ap_terms.term_id
INTO l_payment_terms
FROM ap_terms
WHERE UPPER(ap_terms.name)=UPPER(TRIM(c_rec.payment_terms))
AND enabled_flag ='Y';


---------------------------------------------------
-->> Country Name Validation <<--
---------------------------------------------------

SELECT lookup_code
       INTO l_country_code
       FROM fnd_lookup_values
       WHERE lookup_type='GHR_US_CNTRY_WRLD_CTZN'
      AND meaning        =trim(c_rec.country)
      AND ENABLED_FLAG   ='Y'
      AND LANGUAGE       ='US';

Assign the values to the record type variable before passing to the API.

        l_vendor_rec.vendor_type_lookup_code := c_rec.supplier_type;
        l_vendor_rec.summary_flag         := 'N';
        l_vendor_rec.enabled_flag         := 'Y';
        l_vendor_rec.invoice_currency_code:=TRIM(l_invoice_currency);
        l_vendor_rec.terms_id:=l_payment_terms;


Now call the ap_vendor_pub_pkg.create_vendor for creating the supplier.

          ap_vendor_pub_pkg.create_vendor( p_api_version => 1,
                                            x_return_status => x_return_status,
                                            x_msg_count => x_msg_count,
                                            x_msg_data => x_msg_data,    
                                            p_vendor_rec => l_vendor_rec,
                                            x_vendor_id => x_vendor_id,
                                            x_party_id => x_party_id );



Now for creating the supplier site assign the values to the supplier site record type variable.



                   l_vendor_site_rec.vendor_id             := x_vendor_id;
            l_vendor_site_rec.org_id                := cur_rec.organization_id;
            l_vendor_site_rec.shipping_control      := v_shipping_control;--'BUYER';
            l_vendor_site_rec.purchasing_site_flag  := 'Y';
            l_vendor_site_rec.pay_site_flag         := 'Y';
            l_vendor_site_rec.accts_pay_code_combination_id := v_lib_acc;
            l_vendor_site_rec.prepay_code_combination_id := v_prepay_acc;
            l_vendor_site_rec.pay_group_lookup_code := c_rec.pay_group;
            l_vendor_site_rec.ext_payee_rec.exclusive_pay_flag   := 'N';
            l_vendor_site_rec.ext_payee_rec.default_pmt_method   := 'CHECK';--c_rec.payment_method;
            l_vendor_site_rec.city                  :=TRIM(c_rec.city);
            l_vendor_site_rec.country               :=l_country_code;
            l_vendor_site_rec.address_line1         :=TRIM(c_rec.alt_supplier_site_name);
            l_vendor_site_rec.vendor_site_code      :=c_rec.address_name;
            l_vendor_site_rec.country_of_origin_code:=l_country_code;




Now call the API for supplier site creation.

          ap_vendor_pub_pkg.create_vendor_site( p_api_version => 1,
                                                x_return_status => x_return_status,
                                                x_msg_count => x_msg_count,
                                                x_msg_data => x_msg_data,   
                                                p_vendor_site_rec => l_vendor_site_rec,
                                                x_vendor_site_id => x_vendor_site_id,
                                                x_party_site_id => x_party_site_id,
                                                x_location_id => x_location_id );


Staging table record status has to be updated accordingly based on the API result.

Variable x_return_status gives the status of the API call.

Also for clear understanding, Stats can be printed in the output of the concurrent program, for immediate check.