Oracle Apps Techniques: Supplier Conversion in Oracle APPS

Supplier Conversion in Oracle APPS.

Suppliers in Oracle Application are imported in Three steps

1. Suppliers

2. Supplier Sites

3. Supplier Site Contacts

These three entities can be migrated together or migrated separately. however the migration may be it has to be in the below mentioned order.

First the suppliers need to be created then the supplier sites and then the supplier contacts. supplier contacts is always created at the site level.

Oracle provides two options to import the supplier information into oracle

1.Open Interfaces

2. API's

Open interfaces are preferred when the conversion data is less and the conversion logic is straight forward.

API's are used when we have a complex conversion logic.

Here I am going to explain the supplier conversion using Open Interfaces.

Open interfaces are nothing but oracle provided tables which is used as an interface between the staging table (which holds the raw legacy data) and the oracle base tables (which holds the supplier information).

Supplier Conversion :

Before beginning to write the code for supplier conversion there are few pre-requisites which needs to be setup in the application.

1. Currency Codes

2. Payment Terms

3. Set of Books

4. Vendor Types etc.,

Open interface table for supplier conversion is "AP_SUPPLIERS_INT".

First the data is obtained in the desired format form the legacy system, It may be in the following formats

.csv

.dat

.txt

.csv is the most desirable format, and sometimes the file can also be a pipe delimited file where there are commas present in the data.

Once we get our flat file , we load the data from the flat files into staging tables.

staging tables are nothing but custom tables created by the developers to hold the legacy data before validating and moving it to the interface tables.

Data Loading

Loading the data from the flat file into staging tables. This can be done is different ways. Few examples are

1. SQLLDR - Utility which is used to load bulk data into oracle tables

2. APEX - We can also load the data through APEX (Oracle Application Express) if available.

we can always GOOGLE to find how these tools are used to load the data into oracle.

Data Validation

Now that we have loaded the data into staging table, Now we have to write the program to validate the records in the staging table and insert into Interface tables.

It's always recommended to write the validation package/procedure separately and call it within the main procedure. This is because if a client wants a custom check/translation later we can add it directly in the validation package and not change the main package.

Here is a sample code which I used to convert the supplier data from legacy system into oracle.

-- Package Specification---

CREATE OR REPLACE PACKAGE "XXCONV_SUPPLIER_CONV_PKG"

-- ======== Global Variable Declaration ===========

g_user_id NUMBER (15) := fnd_global.user_id; -- get the user Id --

g_login_id NUMBER (15) := fnd_global.login_id; -- get the login Id --

-- ======== Main Procedure for Supplier Conversion Concurrent Program===========

PROCEDURE xxconv_ap_supp_cv_main (

x_errbuf OUT NOCOPY VARCHAR2,

x_retcode OUT NOCOPY NUMBER,

p_batch_id IN VARCHAR2,

p_run_mode IN VARCHAR2

);

END XXCONV_SUPPLIER_CONV_PKG;

-- Package Body---

CREATE OR REPLACE PACKAGE BODY APPS.xxconv_supplier_conv_pkg

-- Global Constants to set null

ap_null_num CONSTANT NUMBER := fnd_api.g_null_num;

ap_null_char CONSTANT VARCHAR2 (1) := fnd_api.g_null_char;

ap_null_date CONSTANT DATE := fnd_api.g_null_date;

-- Validate Currency Code

PROCEDURE validate_currency_code (

p_currency_code IN VARCHAR2,

x_valid OUT NOCOPY BOOLEAN

)

l_count NUMBER := 0;

BEGIN

x_valid := TRUE;

IF p_currency_code IS NOT NULL

THEN

SELECT COUNT (*)

INTO l_count

FROM fnd_currencies

WHERE UPPER (currency_code) = UPPER (p_currency_code)

AND enabled_flag = 'Y'

AND currency_flag = 'Y'

AND TRUNC (NVL (start_date_active, SYSDATE)) <= TRUNC (SYSDATE)

AND TRUNC (NVL (end_date_active, SYSDATE)) >= TRUNC (SYSDATE);

IF l_count < 1

THEN

x_valid := FALSE;

END IF;

END validate_currency_code;

-- Validate Payment Terms

PROCEDURE validate_terms_name (

p_terms_id IN OUT NOCOPY NUMBER,

p_terms_name IN VARCHAR2,

x_valid OUT NOCOPY BOOLEAN

)

l_terms_id ap_terms_tl.term_id%TYPE;

l_terms_name ap_terms_tl.NAME%TYPE;

BEGIN

x_valid := TRUE;

IF (p_terms_name IS NOT NULL)

THEN

BEGIN

SELECT term_id

INTO p_terms_id

FROM ap_terms_tl

WHERE UPPER (NAME) = UPPER (p_terms_name)

AND LANGUAGE = USERENV ('LANG')

AND SYSDATE < NVL (end_date_active, SYSDATE + 1);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

x_valid := FALSE;

WHEN OTHERS

THEN

x_valid := FALSE;

END;

END IF;

END validate_terms_name;

-- Validate Set of Books

PROCEDURE validate_set_of_books (

p_sob_id IN OUT NUMBER,

p_sob_name IN VARCHAR2,

x_valid OUT NOCOPY BOOLEAN

)

l_dummy gl_sets_of_books.set_of_books_id%TYPE;

BEGIN

x_valid := TRUE;

SELECT set_of_books_id

INTO p_sob_id

FROM gl_sets_of_books

WHERE UPPER (NAME) = UPPER (p_sob_name);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

x_valid := FALSE;

WHEN OTHERS

THEN

x_valid := FALSE;

END validate_set_of_books;

-- Validate Match Option

PROCEDURE validate_match_option (

p_match_option IN VARCHAR2,

x_valid OUT NOCOPY BOOLEAN

)

BEGIN

x_valid := TRUE;

IF p_match_option NOT IN ('P', 'R')

THEN

x_valid := FALSE;

END IF;

END validate_match_option;

-- Validate Employee Number

PROCEDURE check_valid_employee (

p_employee_id IN OUT NUMBER,

p_employee_num IN VARCHAR2,

x_valid OUT NOCOPY BOOLEAN

)

l_dummy hr_employees_current_v.employee_id%TYPE;

BEGIN

x_valid := TRUE;

SELECT employee_id

INTO p_employee_id

FROM hr_current_emp hre

WHERE hre.employee_num = p_employee_num;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

x_valid := FALSE;

WHEN OTHERS

THEN

x_valid := FALSE;

END check_valid_employee;

-- Validate Employee Type Checks

PROCEDURE employee_type_checks (

p_vendor_type IN VARCHAR2,

p_employee_num IN VARCHAR2,

x_valid OUT NOCOPY BOOLEAN

)

BEGIN

x_valid := TRUE;

IF ( ( p_vendor_type <> 'EMPLOYEE'

AND ( p_employee_num IS NOT NULL

AND p_employee_num <> ap_null_char

)

OR ( p_vendor_type = 'EMPLOYEE'

AND (p_employee_num IS NULL OR p_employee_num = ap_null_char)

)

THEN

x_valid := FALSE;

END IF;

END employee_type_checks;

-- Validate Lookups

PROCEDURE validate_lookups (

p_column_name IN VARCHAR2,

p_column_value IN VARCHAR2,

p_lookup_type IN VARCHAR2,

p_lookup_table IN VARCHAR2,

x_valid OUT NOCOPY BOOLEAN

)

l_dummy_lookup VARCHAR2 (30);

BEGIN

x_valid := TRUE;

IF p_lookup_table = 'AP_LOOKUP_CODES'

THEN

BEGIN

SELECT lookup_code

INTO l_dummy_lookup

FROM ap_lookup_codes

WHERE lookup_type = p_lookup_type

AND UPPER (lookup_code) = UPPER (p_column_value)

AND enabled_flag = 'Y'

AND NVL (inactive_date, SYSDATE + 1) > SYSDATE;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

x_valid := FALSE;

WHEN OTHERS

THEN

x_valid := FALSE;

END;

ELSIF p_lookup_table = 'PO_LOOKUP_CODES'

THEN

BEGIN

SELECT lookup_code

INTO l_dummy_lookup

FROM po_lookup_codes

WHERE lookup_type = p_lookup_type

AND UPPER (lookup_code) = UPPER (p_column_value)

AND enabled_flag = 'Y'

AND NVL (inactive_date, SYSDATE + 1) > SYSDATE;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

x_valid := FALSE;

WHEN OTHERS

THEN

x_valid := FALSE;

END;

ELSIF p_lookup_table = 'FND_LOOKUP_VALUES'

THEN

BEGIN

SELECT lookup_code

INTO l_dummy_lookup

FROM fnd_lookups

WHERE lookup_type = p_lookup_type

AND UPPER (lookup_code) = UPPER (p_column_value)

AND enabled_flag = 'Y'

AND NVL (end_date_active, SYSDATE + 1) > SYSDATE;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

x_valid := FALSE;

WHEN OTHERS

THEN

x_valid := FALSE;

END;

END IF;

END validate_lookups;

-- Validate vendor Name

PROCEDURE validate_vendor (

p_ven_name IN VARCHAR2,

x_valid OUT NOCOPY BOOLEAN

)

l_count NUMBER := 0;

BEGIN

x_valid := TRUE;

SELECT COUNT (*)

INTO l_count

FROM ap_suppliers

WHERE UPPER (TRIM (vendor_name)) = UPPER (TRIM (p_ven_name));

IF l_count > 0

THEN

x_valid := FALSE;

END IF;

END validate_vendor;

-- Validate Vendor Number

PROCEDURE validate_vendor_num (

p_ven_num IN VARCHAR2,

x_valid OUT NOCOPY BOOLEAN

)

l_count NUMBER := 0;

BEGIN

x_valid := TRUE;

SELECT COUNT (*)

INTO l_count

FROM ap_suppliers

WHERE UPPER (TRIM (segment1)) = UPPER (TRIM (p_ven_num));

IF l_count > 0

THEN

x_valid := FALSE;

END IF;

END validate_vendor_num;

-- Validate Payment Priority

PROCEDURE validate_payment_priority (

p_payment_priority IN NUMBER,

x_valid OUT NOCOPY BOOLEAN

)

BEGIN

x_valid := TRUE;

IF ( (p_payment_priority < 1)

OR (p_payment_priority > 99)

OR (p_payment_priority <> TRUNC (p_payment_priority))

)

THEN

x_valid := FALSE;

END IF;

END validate_payment_priority;

-- Validate Payment Priority

PROCEDURE check_valid_ship_via (

p_ship_via_lookup_code IN VARCHAR2,

x_valid OUT NOCOPY BOOLEAN

)

l_dummy NUMBER;

BEGIN

x_valid := TRUE;

SELECT NVL (COUNT (freight_code), 0)

INTO l_dummy

FROM org_freight

WHERE freight_code = p_ship_via_lookup_code

AND NVL (disable_date, SYSDATE + 1) > SYSDATE;

IF l_dummy < 1

THEN

x_valid := FALSE;

END IF;

END check_valid_ship_via;

-- Validate Supplier Notification Method

PROCEDURE validate_sup_notif_method (

p_sup_notif_method IN VARCHAR2,

x_valid OUT NOCOPY BOOLEAN

)

l_dummy_lookup VARCHAR2 (30);

BEGIN

x_valid := TRUE;

SELECT lookup_code

INTO l_dummy_lookup

FROM fnd_lookup_values_vl

WHERE lookup_type = 'DOCUMENT_COMMUNICATION_METHOD'

AND UPPER (lookup_code) = UPPER (p_sup_notif_method)

AND enabled_flag = 'Y'

AND NVL (end_date_active, SYSDATE + 1) > SYSDATE;

EXCEPTION

WHEN OTHERS

THEN

x_valid := FALSE;

END validate_sup_notif_method;

-- check_valid_1099_type

PROCEDURE check_valid_1099_type (

p_1099_type IN VARCHAR2,

p_federal_flag IN VARCHAR2,

x_valid OUT NOCOPY BOOLEAN

)

BEGIN

x_valid := TRUE;

IF (NVL (p_federal_flag, 'NO') = 'NO' AND p_1099_type IS NOT NULL)

THEN

x_valid := FALSE;

END IF;

END check_valid_1099_type;

-- === Main Procedure to Validate Supplier Records from Staging Table ========

PROCEDURE xxconv_ap_supp_cv_validate (p_batch_id IN VARCHAR2, p_retcode OUT NUMBER)

-- Declaring Error Variables --

lv_record_status VARCHAR2 (50);

lv_process_flag VARCHAR2 (10);

lv_error_code VARCHAR2 (240);

lv_error_message VARCHAR2 (5000);

lv_vendor_number VARCHAR2 (240);

lv_vendor_name VARCHAR2 (240);

ln_supp_preload_int NUMBER;

ln_err_count NUMBER := 0;

ln_record_count NUMBER := 0;

ln_tot_count NUMBER := 0;

ln_terms_id NUMBER;

ln_sob_id NUMBER;

ln_employee_id NUMBER;

lv_supplier_type VARCHAR2 (240);

ln_accts_ccid NUMBER;

ln_prepay_ccid NUMBER;

ln_ship_to_location_id NUMBER;

ln_bill_to_location_id NUMBER;

ln_dup_count NUMBER;

x_valid BOOLEAN;

ln_insert_er NUMBER := 0;

lv_federal_reportable_flag VARCHAR2(1);

CURSOR c_sup_stage

SELECT *

FROM xxconv_supp_stg_inbound

WHERE batch_id = p_batch_id

AND UPPER (record_status) = 'INITIAL LOAD';

BEGIN

-- Get the Total Record Count --

BEGIN

SELECT COUNT (*)

INTO ln_tot_count

FROM xxconv_supp_stg_inbound

WHERE batch_id = p_batch_id

AND UPPER (record_status) = 'INITIAL LOAD';

END;

-- == Open the Cursor for Validation ===

FOR c_rec IN c_sup_stage

LOOP

-- Initializing variables ----

lv_record_status := NULL;

lv_process_flag := NULL;

lv_error_code := NULL;

lv_error_message := NULL;

-- Seq --

ln_supp_preload_int := NULL;

-- Other variables --

ln_terms_id := NULL;

ln_sob_id := NULL;

ln_employee_id := NULL;

ln_accts_ccid := NULL;

ln_prepay_ccid := NULL;

ln_ship_to_location_id := NULL;

ln_bill_to_location_id := NULL;

ln_dup_count := NULL;

ln_insert_er := 0;

lv_federal_reportable_flag := NULL;

---- Start of Validations ---

-- ======= Call to Validate Vendor Name - PR01 ===========

IF c_rec.vendor_name IS NOT NULL

THEN

validate_vendor (c_rec.vendor_name, x_valid);

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR01' || ';';

lv_error_message := lv_error_message

|| ' - '

|| c_rec.vendor_name

|| ' - '

|| 'Supplier name already exists';

lv_error_message := lv_error_message || ' ; ';

END IF;

ELSIF c_rec.vendor_name IS NULL

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR01' || ';';

lv_error_message := lv_error_message

|| ' - '

|| c_rec.vendor_name

|| ' - '

|| 'Supplier name cannot be blank';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate Duplicate Vendor Name in the staging table - PR16 ===========

IF c_rec.vendor_name IS NOT NULL

THEN

SELECT COUNT (vendor_name) -- Changed from * to column name by Dinesh

INTO ln_dup_count

FROM xxconv_supp_stg_inbound

WHERE batch_id = p_batch_id

AND UPPER (vendor_name) = UPPER (c_rec.vendor_name);

IF ln_dup_count > 1

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR16' || ';';

lv_error_message := lv_error_message

|| ' - '

|| c_rec.vendor_name

|| ' - '

|| 'Duplicate Vendor Name in the batch- please correct';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate Vendor Number - PR02 ===========

IF c_rec.segment1 IS NOT NULL

THEN

validate_vendor_num (c_rec.segment1, x_valid);

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR02' || ';';

lv_error_message := lv_error_message

|| ' - '

|| c_rec.vendor_name

|| ' - '

|| 'Supplier Number already exists';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate Vendor Type - PR03 ===========

IF c_rec.vendor_type_lookup_code IS NOT NULL

THEN

validate_lookups ('VENDOR_TYPE_LOOKUP_CODE',

UPPER (c_rec.vendor_type_lookup_code),

'VENDOR TYPE',

'PO_LOOKUP_CODES',

x_valid

);

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR03' || ';';

lv_error_message := lv_error_message

|| ' - '

|| c_rec.vendor_type_lookup_code

|| ' - '

|| 'is not a Valid Vendor type';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate Summary_flag - PR04 ===========

IF c_rec.summary_flag IS NOT NULL

THEN

validate_lookups ('SUMMARY_FLAG',

UPPER (c_rec.summary_flag),

'YES/NO',

'PO_LOOKUP_CODES',

x_valid

);

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR04' || ';';

lv_error_message := lv_error_message

|| ' - '

|| c_rec.summary_flag

|| ' - '

|| 'Summary flag value should be either Y or N';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate Enabled_flag - PR05 ===========

IF c_rec.enabled_flag IS NOT NULL

THEN

validate_lookups ('ENABLED_FLAG',

UPPER (c_rec.enabled_flag),

'YES/NO',

'PO_LOOKUP_CODES',

x_valid

);

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR05' || ';';

lv_error_message := lv_error_message

|| ' - '

|| c_rec.enabled_flag

|| ' - '

|| 'Enabled flag value should be either Y or N';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate Payment Currency - PR06 ===========

IF c_rec.payment_currency_code IS NOT NULL

THEN

validate_currency_code (UPPER (c_rec.payment_currency_code),

x_valid

);

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR06' || ';';

lv_error_message :=

lv_error_message

|| ' - '

|| c_rec.payment_currency_code

|| ' - '

|| 'Payment Currency is Invalid';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate Payment Terms- PR07===========

IF c_rec.terms_name IS NOT NULL

THEN

validate_terms_name (ln_terms_id,

UPPER (c_rec.terms_name),

x_valid

);

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR07' || ';';

lv_error_message :=

lv_error_message

|| ' - '

|| c_rec.terms_name

|| ' - '

|| 'Payment Terms is Invalid';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate Pay Group- PR08 ===========

IF c_rec.pay_group_lookup_code IS NOT NULL

THEN

validate_lookups ('PAY_GROUP_LOOKUP_CODE',

c_rec.pay_group_lookup_code,

'PAY GROUP',

'PO_LOOKUP_CODES',

x_valid

);

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR08' || ';';

lv_error_message :=

lv_error_message

|| ' - '

|| c_rec.pay_group_lookup_code

|| ' - '

|| 'is not a Valid Pay Group';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate Payment terms date basis- PR09 ===========

IF c_rec.terms_date_basis IS NOT NULL

THEN

validate_lookups ('TERMS_DATE_BASIS',

c_rec.terms_date_basis,

'TERMS DATE BASIS',

'AP_LOOKUP_CODES',

x_valid

);

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR09' || ';';

lv_error_message :=

lv_error_message

|| ' - '

|| c_rec.terms_date_basis

|| ' - '

|| 'Please enter a valid Terms Date Basis';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate Set Of Books- PR10 ===========

IF c_rec.set_of_books IS NOT NULL

THEN

validate_set_of_books (ln_sob_id, c_rec.set_of_books, x_valid);

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR10' || ';';

lv_error_message :=

lv_error_message

|| ' - '

|| c_rec.set_of_books

|| ' - '

|| 'Set of Books entered is Invalid';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate Match Option- PR11 ===========

IF c_rec.match_option IS NOT NULL

THEN

validate_match_option (c_rec.match_option, x_valid);

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR11' || ';';

lv_error_message :=

lv_error_message

|| ' - '

|| c_rec.match_option

|| ' - '

|| 'is not a Valid Match option';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate Employee Type Check- PR13 ===========

IF c_rec.vendor_type_lookup_code IS NOT NULL

THEN

employee_type_checks (c_rec.vendor_type_lookup_code,

c_rec.employee_number,

x_valid

);

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR13' || ';';

lv_error_message :=

lv_error_message

|| ' - '

|| c_rec.vendor_type_lookup_code

|| ' - '

|| c_rec.employee_number

|| ' - '

|| 'Either Employee number should be null for vendor type VENDOR or Employee number should not be null for vendor type EMPLOYEE';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate Organization Type- PR12 ===========

IF c_rec.organization_type_lookup_code IS NOT NULL

THEN

validate_lookups ('ORGANIZATION_TYPE_LOOKUP_CODE',

c_rec.organization_type_lookup_code,

'ORGANIZATION TYPE',

'PO_LOOKUP_CODES',

x_valid

);

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR12' || ';';

lv_error_message :=

lv_error_message

|| ' - '

|| c_rec.organization_type_lookup_code

|| ' - '

|| 'Organization Type is Not Valid';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate Employee Number if Vendor Type is EMPLOYEE - PR14 ===========

IF ( c_rec.employee_number IS NOT NULL

AND UPPER (c_rec.vendor_type_lookup_code) = 'EMPLOYEE'

)

THEN

check_valid_employee (ln_employee_id,

c_rec.employee_number,

x_valid

);

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR14' || ';';

lv_error_message :=

lv_error_message

|| ' - '

|| c_rec.employee_number

|| ' - '

|| 'Please enter a Valid Employee Number';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate Payment Priority - PR15 ===========

IF c_rec.payment_priority IS NOT NULL

THEN

validate_payment_priority (c_rec.payment_priority, x_valid);

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR15' || ';';

lv_error_message :=

lv_error_message

|| ' - '

|| c_rec.payment_priority

|| ' - '

|| 'Payment Priority should lie between 1 to 99';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Call to Validate the 1099_type value- PR16 ===========

IF c_rec.type_1099 IS NOT NULL

THEN

Check_Valid_1099_type(c_rec.type_1099,

c_rec.federal_reportable_flag,

x_valid

);

lv_federal_reportable_flag := 'Y';

IF NOT x_valid

THEN

lv_record_status := 'Failed';

lv_process_flag := 'E';

lv_error_code := lv_error_code || 'Error-PR16' || ';';

lv_error_message :=

lv_error_message

|| ' - '

|| c_rec.type_1099

|| ' - '

|| '1099 Type is Inconsistent - Check federal reportable flag is set to Y ';

lv_error_message := lv_error_message || ' ; ';

END IF;

-- ======= Insert into PRE-LOAD Tables ===========

BEGIN

SELECT xxconv.xxconv_supplier_int_s.NEXTVAL

INTO ln_supp_preload_int

FROM DUAL;

END;

BEGIN

INSERT INTO xxconv_supplier_preload

(vendor_interface_id,

vendor_name,

vendor_name_alt,

segment1,

summary_flag,

enabled_flag,

vendor_type_lookup_code,

ship_to_location_id,

bill_to_location_id,

terms_name,

terms_id,

set_of_books,

set_of_books_id,

always_take_disc_flag,

pay_group_lookup_code,

payment_priority,

payment_method_lookup_code,

terms_date_basis,

qty_rcv_tolerance,

qty_rcv_exception_code,

days_early_receipt_allowed,

days_late_receipt_allowed,

receipt_days_exception_code,

allow_substitute_receipts_flag,

allow_unordered_receipts_flag,

match_option,

ap_tax_rounding_rule,

accts_pay_code_combination,

accts_pay_code_combination_id,

create_debit_memo_flag,

customer_num,

employee_number,

employee_id,

end_date_active,

federal_reportable_flag,

fob_lookup_code,

freight_terms_lookup_code,

num_1099,

organization_type_lookup_code,

prepay_code_combination,

prepay_code_combination_id,

purchasing_hold_reason,

ship_to_location_code,

ship_via_lookup_code,

bill_to_location_code,

start_date_active,

supplier_notif_method,

email_address,

party_orig_system_reference,

party_orig_system,

type_1099,

payment_currency_code,

payment_format_code,

attribute_category,

attribute1,

attribute2,

attribute3,

attribute4,

attribute5,

status,

created_by,

creation_date,

batch_id,

record_status,

process_flag,

ERROR_CODE,

error_message

)

VALUES (ln_supp_preload_int, TRIM (c_rec.vendor_name),

TRIM (c_rec.vendor_name_alt),

TRIM (c_rec.segment1),

UPPER (TRIM (c_rec.summary_flag)),

UPPER (TRIM (c_rec.enabled_flag)),

UPPER (TRIM (c_rec.vendor_type_lookup_code)),

ln_ship_to_location_id, ln_bill_to_location_id,

TRIM(c_rec.terms_name),

ln_terms_id,

TRIM(c_rec.set_of_books),

ln_sob_id,

UPPER (TRIM (c_rec.always_take_disc_flag)),

UPPER (TRIM (c_rec.pay_group_lookup_code)),

TO_NUMBER (c_rec.payment_priority),

UPPER (TRIM (c_rec.payment_method_code)),

TRIM (c_rec.terms_date_basis),

TO_NUMBER (c_rec.qty_rcv_tolerance),

TRIM (c_rec.qty_rcv_exception_code),

TO_NUMBER (c_rec.days_early_receipt_allowed),

TO_NUMBER (c_rec.days_late_receipt_allowed),

TRIM (c_rec.receipt_days_exception_code),

UPPER (TRIM (c_rec.allow_substitute_receipts_flag)),

UPPER (TRIM (c_rec.allow_unordered_receipts_flag)),

UPPER (TRIM (c_rec.match_option)),

UPPER (TRIM (c_rec.ap_tax_rounding_rule)),

TRIM(c_rec.accts_pay_code_combination),

ln_accts_ccid,

UPPER (TRIM (c_rec.create_debit_memo_flag)),

TRIM (c_rec.customer_num),

TRIM(c_rec.employee_number),

ln_employee_id,

TO_DATE (c_rec.end_date_active, 'DD-MON-RRRR'),

lv_federal_reportable_flag,--UPPER(TRIM(c_rec.federal_reportable_flag)),

TRIM (c_rec.fob_lookup_code),

UPPER (TRIM (c_rec.freight_terms_lookup_code)),

TRIM (c_rec.num_1099),

UPPER (TRIM (c_rec.organization_type_lookup_code)),

TRIM(c_rec.prepay_code_combination),

ln_prepay_ccid,

TRIM (c_rec.purchasing_hold_reason),

TRIM(c_rec.ship_to_location_code),

TRIM (c_rec.ship_via_lookup_code),

TRIM (c_rec.bill_to_location_code),

TO_DATE (c_rec.start_date_active, 'DD-MON-RRRR'),

UPPER (TRIM (c_rec.supplier_notif_method)),

TRIM (c_rec.email_address),

TRIM (c_rec.party_orig_system_reference),

TRIM (c_rec.party_orig_system),

TRIM (c_rec.type_1099),

UPPER (TRIM (c_rec.payment_currency_code)),

TRIM (c_rec.payment_format_code),

TRIM(c_rec.attribute_category),

TRIM(c_rec.attribute1),

TRIM(c_rec.attribute2),

TRIM(c_rec.attribute3),

TRIM(c_rec.attribute4),

TRIM(c_rec.attribute5),

TRIM(c_rec.status),

g_user_id,

SYSDATE,

TO_NUMBER (c_rec.batch_id),

DECODE (lv_record_status,

'Failed', 'Failed',

'Validated'

DECODE (lv_process_flag, 'E', 'E', 'V'),

lv_error_code,

lv_error_message

);

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line

(fnd_file.LOG,

'Error in Inserting records into PRE-LOAD Table for Vendor '||c_rec.vendor_name

|| ' : '

|| SQLERRM

);

ln_insert_er := 1;

lv_process_flag := 'E';

END;

-- ============Update Staging Table with Record Status ===========

IF ln_insert_er = 0

THEN

BEGIN

UPDATE xxconv_supp_stg_inbound

SET record_status = 'Processed'

WHERE batch_id = c_rec.batch_id

AND UPPER (record_status) = 'INITIAL LOAD';

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line

(fnd_file.LOG,

'Error in Updating record status in staging Table '

|| ' : '

|| SQLERRM

);

END;

END IF;

---- Take the number of recorde processed and no of records errored

IF lv_process_flag = 'E'

THEN

ln_err_count := ln_err_count + 1;

ELSIF lv_process_flag <> 'E'

OR lv_process_flag IS NULL

OR lv_process_flag = 'V'

THEN

ln_record_count := ln_record_count + 1;

END IF;

END LOOP;

-- ==Complete the program in warning =====

IF ln_err_count > 0

THEN

p_retcode := 1;

END IF;

-- ==Commit the reords =====

COMMIT;

-- === Log Results =====

fnd_file.put_line

(fnd_file.LOG,

'********************** Summary **************************'

);

fnd_file.put_line (fnd_file.LOG,

'Total No of Records ' || ' : ' || ln_tot_count

);

fnd_file.put_line (fnd_file.LOG,

'No of records Processed' || ' : ' || ln_record_count

);

fnd_file.put_line (fnd_file.LOG,

'No of records Errored' || ' : ' || ln_err_count

);

fnd_file.put_line

(fnd_file.LOG,

'***********************************************************'

);

END xxconv_ap_supp_cv_validate;

PROCEDURE xxconv_ap_supp_cv_load (p_batch_id IN NUMBER)

CURSOR c_supp_int

SELECT *

FROM xxconv_supplier_preload

WHERE process_flag = 'V'

AND batch_id = p_batch_id

AND ERROR_CODE IS NULL

AND error_message IS NULL;

-- Variable Declaration

lv_error_code VARCHAR2 (1000);

lv_sqlerrm VARCHAR2 (2000);

lv_error_message VARCHAR2 (1000);

ln_sup_int_id NUMBER;

l_user_id NUMBER

:= TO_NUMBER (apps.fnd_profile.VALUE ('USER_ID'));

ld_sysdate DATE := SYSDATE;

ln_record_count NUMBER := 0;

ln_err_count NUMBER := 0;

ln_tot_count NUMBER;

BEGIN

-- === Get the total count of Validated Records for this Batch =======

BEGIN

SELECT COUNT (*)

INTO ln_tot_count

FROM xxconv_supplier_preload

WHERE process_flag = 'V'

AND batch_id = p_batch_id

AND ERROR_CODE IS NULL

AND error_message IS NULL;

END;

FOR c_sup_rec IN c_supp_int

LOOP

BEGIN

SELECT apps.ap_suppliers_int_s.NEXTVAL

INTO ln_sup_int_id

FROM DUAL;

/*+--------------------------------------------------------------------------------------+

-- =============== Inserting records into Supplier Interface Table ===========

+--------------------------------------------------------------------------------------+ */

INSERT INTO ap_suppliers_int

(vendor_interface_id,

vendor_name,

segment1,

allow_substitute_receipts_flag,

allow_unordered_receipts_flag,

always_take_disc_flag,

ap_tax_rounding_rule,

accts_pay_code_combination_id,

create_debit_memo_flag,

customer_num,

days_early_receipt_allowed,

days_late_receipt_allowed,

end_date_active,

federal_reportable_flag,

fob_lookup_code,

freight_terms_lookup_code,

match_option,

num_1099,

organization_type_lookup_code,

payment_method_code,

payment_priority,

pay_group_lookup_code,

prepay_code_combination_id,

purchasing_hold_reason,

qty_rcv_exception_code,

qty_rcv_tolerance,

receipt_days_exception_code,

summary_flag,

enabled_flag,

set_of_books_id,

ship_to_location_code,

ship_via_lookup_code,

start_date_active,

bill_to_location_code,

supplier_notif_method,

email_address,

party_orig_system,

terms_date_basis,

terms_id,

type_1099,

payment_currency_code,

payment_format_code,

vendor_name_alt,

vendor_type_lookup_code,

employee_id,

status,

last_update_date,

last_updated_by,

creation_date,

created_by,

last_update_login

)

VALUES (ln_sup_int_id,

c_sup_rec.vendor_name,

c_sup_rec.segment1,

c_sup_rec.allow_substitute_receipts_flag,

c_sup_rec.allow_unordered_receipts_flag,

c_sup_rec.always_take_disc_flag,

c_sup_rec.ap_tax_rounding_rule,

c_sup_rec.accts_pay_code_combination_id,

c_sup_rec.create_debit_memo_flag,

c_sup_rec.customer_num,

c_sup_rec.days_early_receipt_allowed,

c_sup_rec.days_late_receipt_allowed,

c_sup_rec.end_date_active,

c_sup_rec.federal_reportable_flag,

c_sup_rec.fob_lookup_code,

c_sup_rec.freight_terms_lookup_code,

c_sup_rec.match_option,

c_sup_rec.num_1099,

c_sup_rec.organization_type_lookup_code,

c_sup_rec.payment_method_code,

c_sup_rec.payment_priority,

c_sup_rec.pay_group_lookup_code,

c_sup_rec.prepay_code_combination_id,

c_sup_rec.purchasing_hold_reason,

c_sup_rec.qty_rcv_exception_code,

c_sup_rec.qty_rcv_tolerance,

c_sup_rec.receipt_days_exception_code,

c_sup_rec.summary_flag,

c_sup_rec.enabled_flag,

c_sup_rec.set_of_books_id,

c_sup_rec.ship_to_location_code,

c_sup_rec.ship_via_lookup_code,

c_sup_rec.start_date_active,

c_sup_rec.bill_to_location_code,

c_sup_rec.supplier_notif_method,

c_sup_rec.email_address,

c_sup_rec.party_orig_system,

c_sup_rec.terms_date_basis,

c_sup_rec.terms_id,

c_sup_rec.type_1099,

c_sup_rec.payment_currency_code,

c_sup_rec.payment_format_code,

c_sup_rec.vendor_name_alt,

c_sup_rec.vendor_type_lookup_code,

c_sup_rec.employee_id,

'NEW',

ld_sysdate,

g_user_id,

ld_sysdate,

g_user_id,

g_login_id

);

EXCEPTION

WHEN OTHERS

THEN

lv_sqlerrm := SQLERRM;

lv_error_code := 'Error Inserting';

lv_error_message :=

c_sup_rec.segment1

|| ' '

|| c_sup_rec.vendor_name

|| ' - '

|| 'Error Inserting Into Iface table';

--Update staging table with errors

UPDATE xxconv_supplier_preload

SET process_flag = 'E',

record_status = 'Error Inserting Iface table',

ERROR_CODE = lv_error_code,

error_message = lv_error_message || ' - ' || lv_sqlerrm

WHERE segment1 = c_sup_rec.segment1

AND batch_id = c_sup_rec.batch_id;

ln_err_count := ln_err_count + 1;

END;

--Update staging table with loaded records --

UPDATE xxconv_supplier_preload

SET process_flag = 'L',

record_status = 'Loaded',

error_message = NULL,

ERROR_CODE = NULL

WHERE segment1 = c_sup_rec.segment1

AND batch_id = c_sup_rec.batch_id;

ln_record_count := ln_record_count + 1;

END LOOP;

COMMIT;

fnd_file.put_line

(fnd_file.LOG,

'******************** Interface Loading Summary ********************'

);

fnd_file.put_line (fnd_file.LOG,

'Total No of Records' || ' : ' || ln_tot_count

);

fnd_file.put_line (fnd_file.LOG,

'No of records Processed' || ' : ' || ln_record_count

);

fnd_file.put_line (fnd_file.LOG,

'No of records Errored' || ' : ' || ln_err_count

);

fnd_file.put_line

(fnd_file.LOG,

'***********************************************************'

);

END xxconv_ap_supp_cv_load;

PROCEDURE xxconv_ap_supp_cv_main (

x_errbuf OUT NOCOPY VARCHAR2,

x_retcode OUT NOCOPY NUMBER,

p_batch_id IN VARCHAR2,

p_run_mode IN VARCHAR2

)

lv_retcode1 NUMBER;

lv_retcode2 NUMBER;

BEGIN

-- Call validate procedure to validate the Supplier information and load into PRE-LOAD Table

IF UPPER (p_run_mode) = 'VALIDATE'

THEN

xxconv_ap_supp_cv_validate (p_batch_id,lv_retcode1);

IF lv_retcode1 = 1--'1'

THEN

x_retcode:= 1;

END IF;

ELSE

NULL;

END IF;

-- Call Loaded procedure to load the data from PRE-LOAD table to Supplier interface table

IF UPPER (p_run_mode) = 'FINAL'

THEN

xxconv_ap_supp_cv_load (p_batch_id); --xxconv_ap_supp_cv_load;

IF lv_retcode2 = 1--'1'

THEN

x_retcode:= 1;

END IF;

ELSE

NULL;

END IF;

END xxconv_ap_supp_cv_main;

END xxconv_supplier_conv_pkg;

This is the code used to validate the records in the staging table and move it to the interface table. Here in this code I have used another table called preload because I uploaded the records through APEX and there was no validation performed at the time of upload.

Now the records are validated and valid records are inserted into the interface table. Now all we have to do is to run the seeded import program and it will take care of the rest.

standard Import program is "Supplier Open Interface Import", Run this program with Import Options as "ALL" this will take all the records from the interface tables.

One this program is completed you can see in the output the number of suppliers imported.

You can find the rejected records and the reason in the "AP_SUPPLIER_INT_REJECTIONS" table.

Friday 20 February 2015

Supplier Conversion in Oracle APPS

5 comments: