Supplier Conversion in R12 (Tables and Required Columns)

 Interface Tables 

• AP_SUPPLIERS_INT
• AP_SUPPLIER_SITES_INT
• AP_SUP_SITE_CONTACT_INT

AP_SUPPLIERS_INT:

Mandatory Columns:

• VENDOR_INTERFACE_ID (ap_suppliers_int_s.NEXTVAL)- Supplier interface record unique identifier
• VENDOR_NAME – Supplier name

Other important columns:

• SEGMENT1 – Supplier Number
• VENDOR_TYPE_LOOKUP_CODE – Supplier type
• SHIP_TO_LOCATION_CODE – Default ship-to-location name
• BILL_TO_LOCATION_CODE – Default bill-to-location name
• TERMS_NAME – Payment terms name
• TAX_VERIFICATION_DATE – Tax verification date(1099)
• VAT_REGISTRATION_NUM – Tax registration number
• ATTRIBUTE1 -15 – Descriptive Flexfield Segments
• PAY_GROUP_LOOKUP_CODE – Payment group type
• INVOICE_CURRENCY_CODE – Default currency unique identifier
• PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
• NUM_1099 – Tax identification number (1099)
• VAT_CODE – Default invoice tax code
• HOLD_FLAG – Indicates whether or not the supplier is on purchasing hold
• SUMMARY_FLAG – Key flexfield summary flag
• ENABLED_FLAG – Key flexfield enable flag
• EMPLOYEE_ID – Employee unique identifier if supplier is an employee

Interface program:

     Supplier Open Interface Import

AP_SUPPLIER_SITES_INT:

Mandatory Columns:

• VENDOR_SITE_INTERFACE_ID (ap_supplier_sites_int_s.NEXTVAL) – Supplier Site interface record unique identifier
• VENDOR_SITE_CODE – Supplier Site name

Other important columns:

• ADDRESS_LINE1 – First line of supplier address
• ADDRESS_LINE2 – Second line of supplier address
• ADDRESS_LINE3 – Third line of supplier address
• CITY – City name
• STATE – State name or abbreviation
• ZIP – Postal code
• COUNTRY – Country name
• PHONE – Phone number
• FAX – Supplier site facsimile number
• SHIP_TO_LOCATION_CODE – Default ship-to-location name
• BILL_TO_LOCATION_CODE – Default bill-to-location name
• PAYMENT_METHOD_LOOKUP_CODE – Default payment method type
• VAT_CODE – Invoice default tax code
• PAY_GROUP_LOOKUP_CODE – Payment group type
• TERMS_NAME – Payment terms name
• INVOICE_CURRENCY_CODE – Default currency unique identifier
• PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
• EMAIL_ADDRESS – E-mail address of the supplier contact
• PURCHASING_SITE_FLAG – Indicates whether purchasing is allowed from this site
• AUTO_TAX_CALC_FLAG – Level of automatic tax calculation for supplier
• HOLD_ALL_PAYMENTS_FLAG – Indicates if Oracle Payables should place payments for this supplier on hold        
• Interface program:
• Supplier Sites Open Interface Import

AP_SUP_SITE_CONTACT_INT:

Mandatory Columns:

• VENDOR_INTERFACE_ID – Supplier interface record unique identifier
• VENDOR_CONTACT_INTERFACE_ID (AP_SUP_SITE_CONTACT_INT_S.NEXTVAL) – Vendor Contact Interface Identifier
• VENDOR_SITE_CODE – Supplier Site name

Other important columns:

• FIRST_NAME – Contact First name
• LAST_NAME – Contact last name
• AREA_CODE – Area code of contact phone number
• PHONE – Contact phone number
• FIRST_NAME_ALT – Alternate Supplier contact first name
• LAST_NAME_ALT – Alternate Supplier contact last name
• EMAIL_ADDRESS – Email address for the Supplier Site contact
• FAX – Facsimile number for the Supplier Site contact
• VENDOR_ID – Supplier unique identifier

Interface program:

Supplier Site Contacts Open Interface Import

Supplier Conversion in Oracle Apps R12

/* Formatted on 2016/03/03 21:41 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PACKAGE aad_sup_1
IS
   PROCEDURE PRINT (p_message VARCHAR2);

   PROCEDURE main1 (errbuf OUT VARCHAR2, retcode OUT VARCHAR2);
END;

CREATE OR REPLACE PACKAGE BODY aad_sup_1
IS
   PROCEDURE PRINT (p_message VARCHAR2)
   IS
   BEGIN
      fnd_file.put_line (fnd_file.LOG, p_message);
   END;

   PROCEDURE main1 (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
   IS
      CURSOR c1
      IS
         (SELECT vendor_name, vendor_type_lookup_code, invoice_currency_code,
                 payment_currency_code, start_date_active, terms_name,
                 ROWID row_id
            FROM aad_supplier_stg
           WHERE process_flag = 'N');

      lv_vendor_name               NUMBER;
      ln_err_cnt                   NUMBER;
      ln_err_msg                   VARCHAR2 (4000);
      ln_vendor_interface_id       NUMBER;
      lv_vendor_type_lookup_code   NUMBER;
      lv_invoice_currency_code     NUMBER;
      lv_payment_currency_code     NUMBER;
      lv_terms_name                NUMBER;
   BEGIN
      FOR i IN c1
      LOOP
         ln_err_cnt := 0;
         ln_err_msg := ' ';

-- Vendor name validation -----------
         BEGIN
            SELECT 1
              INTO lv_vendor_name
              FROM ap_suppliers aps
             WHERE aps.vendor_name = i.vendor_name;

            IF lv_vendor_name = 1
            THEN
               ln_err_cnt := 1;
               ln_err_msg :=
                      'Given vendor name ' || i.vendor_name || 'already exit';
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               NULL;
            WHEN OTHERS
            THEN
               ln_err_cnt := 1;
               ln_err_msg := 'Err_msg: ' || SQLERRM;
         END;

-- Vendor code validation -----------
         BEGIN
            SELECT 1
              INTO lv_vendor_type_lookup_code
              FROM fnd_lookup_values
             WHERE lookup_type = 'VENDOR TYPE'
               AND enabled_flag = 'Y'
               AND LANGUAGE = USERENV ('LANG')
               AND lookup_code = i.vendor_type_lookup_code;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               ln_err_cnt := 1;
               ln_err_msg :=
                     ln_err_msg
                  || ','
                  || 'Given vendor site code invalid- '
                  || i.vendor_type_lookup_code;
            WHEN OTHERS
            THEN
               ln_err_cnt := 1;
               ln_err_msg :=
                     ln_err_msg
                  || ','
                  || 'Error While validating Vendor Type: '
                  || SQLERRM;
         END;

-- Invoice currency code validation -----------
         BEGIN
            SELECT 1
              INTO lv_invoice_currency_code
              FROM fnd_currencies
             WHERE currency_code = i.invoice_currency_code
               AND enabled_flag = 'Y';
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               ln_err_cnt := 1;
               ln_err_msg :=
                     ln_err_msg
                  || ','
                  || 'Given invoice currency code invalid- '
                  || i.invoice_currency_code;
            WHEN OTHERS
            THEN
               ln_err_cnt := 1;
               ln_err_msg :=
                     ln_err_msg
                  || ','
                  || 'Error While validating invoice currency code: '
                  || SQLERRM;
         END;

-- Payment currency code validation -----------
         BEGIN
            SELECT 1
              INTO lv_payment_currency_code
              FROM fnd_currencies
             WHERE currency_code = i.payment_currency_code
               AND enabled_flag = 'Y';
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               ln_err_cnt := 1;
               ln_err_msg :=
                     ln_err_msg
                  || ','
                  || 'Given Payment currency code invalid- '
                  || i.payment_currency_code;
            WHEN OTHERS
            THEN
               ln_err_cnt := 1;
               ln_err_msg :=
                     ln_err_msg
                  || ','
                  || 'Error While validating Payment currency code: '
                  || SQLERRM;
         END;

-- Terms name validation -----------
         BEGIN
            SELECT 1
              INTO lv_terms_name
              FROM ap_terms_tl
             WHERE NAME = i.terms_name AND LANGUAGE = USERENV ('LANG');
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               ln_err_cnt := 1;
               ln_err_msg :=
                     ln_err_msg
                  || ','
                  || 'Given Term name invalid- '
                  || i.terms_name;
            WHEN OTHERS
            THEN
               ln_err_cnt := 1;
               ln_err_msg :=
                     ln_err_msg
                  || ','
                  || 'Error While validating Terms name: '
                  || SQLERRM;
         END;

         IF ln_err_cnt = 0
         THEN
            SELECT ap_suppliers_int_s.NEXTVAL
              INTO ln_vendor_interface_id
              FROM DUAL;

            INSERT INTO ap_suppliers_int
                        (vendor_interface_id, vendor_name,
                         vendor_type_lookup_code, invoice_currency_code,
                         payment_currency_code, terms_name,
                         start_date_active, created_by, creation_date,
                         last_updated_by, last_update_date
                        )
                 VALUES (ln_vendor_interface_id, i.vendor_name,
                         i.vendor_type_lookup_code, i.invoice_currency_code,
                         i.payment_currency_code, i.terms_name,
                         i.start_date_active, fnd_global.user_id, SYSDATE,
                         fnd_global.user_id, SYSDATE
                        );

            UPDATE aad_supplier_stg
               SET process_flag = 'S'
             WHERE ROWID = i.row_id;
         ELSE
            UPDATE aad_supplier_stg
               SET process_flag = 'E',
                   error_msg = ln_err_msg
             WHERE ROWID = i.row_id;
         END IF;
      END LOOP;

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         PRINT ('Err_msg:- ' || SQLERRM);
   END;
END;