Supplier Interface Tables in Oracle Apps r12
Hi friends, we are going to discuss about the Supplier Interface Tables in Oracle Apps r12. We will share the complete list of supplier interface tables which helps to import the supplier in oracle apps. In oracle apps, we have the standard interface tables which helps to upload the supplier from external system/application into the oracle apps r12. In this post , We will provide you these supplier interface tables using this we can upload supplier interface tables into oracle apps. We will also share the custom procedure which helps to import the suppliers using interface tables into oracle apps. Please find below the complete detail about Supplier Interface Tables in Oracle Apps r12.
 |
| Supplier Interface Tables in Oracle Apps r12 |
Interface Tables for Supplier Conversion in oracle apps r12
1.ap_suppliers_int
2.ap_supplier_sites_int
|
| Supplier Interface Tables in Oracle Apps r12 |
To find out the Supplier Interface Interface error records in Oracle Apps
There are two Important Columns in the ap_suppliers_int interface table which helps to find out the supplier interface error records in oracle apps.
1.REJECT_CODE
2.STATUS
Interface Procedure Using Supplier Interface Tables in Oracle Apps r12
In this script , we have the two procedures which helps to import the supplier using supplier interface tables in oracle apps.
In First Procedure (xx_supplier_vailidate), We will first Validate the Supplier Data in the staging table which will be inserted into Supplier Interface tables.
In Second Procedure(xx_supplier_detl) , the validated data from staging table will be inserted into the Supplier Interface Tables.
Here below is the sql query which helps to extract the Supplier header interface error or rejected records.
select reject_code,status from apps.ap_suppliers_int
where status='REJECTED'
select REJECT_CODE,status from apps.ap_supplier_sites_int
where status='REJECTED'
PROCEDURE xx_supplier_vailidate
IS
-----Main Cursor ---------------
CURSOR cur_supplier_detl
IS
SELECT
FROM xx_supp_stag_all
WHERE NVL(VAILIDATE_FLAG_PROCESS,'NULL')='NULL'
AND NVL(FLAG_PROCESS,'E') <>'P';
-------Cursor for Operating Unit------------
CURSOR cur_operating_unit (v_org_code VARCHAR2)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (SELECT NULL
FROM hr_operating_units
WHERE UPPER (NAME) = UPPER (v_org_code));
-------Cursor for Vailid Vendor-------------
CURSOR cur_vailid_vendor (v_vendor_name VARCHAR2)
IS
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT vendor_id
FROM po_vendors
WHERE TRIM (UPPER (vendor_name)) =
TRIM (UPPER (v_vendor_name)));
------Cursor for Vendor Site ------------
CURSOR cur_vailid_site (
v_vendor_name VARCHAR2,
v_site_name VARCHAR2,
v_org_id NUMBER
)
IS
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT vendor_site_code
FROM po_vendor_sites_all a, po_vendors b
WHERE org_id = v_org_id
AND UPPER (vendor_site_code) = UPPER (v_site_name)
AND a.vendor_id = b.vendor_id
AND UPPER (b.vendor_name) = UPPER (v_vendor_name));
-------------CURSOR FOR VAILID COUNTRY ----------
CURSOR cur_vailid_country (v_country_name VARCHAR2)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (
SELECT territory_code
FROM fnd_territories_tl
WHERE UPPER (territory_short_name) =
UPPER (v_country_name));
-------------CURSOR FOR VAILID VENDOR TYPE ----------
CURSOR cur_vailid_vendor_type (v_type VARCHAR2)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (
SELECT lookup_code
FROM po_lookup_codes
WHERE lookup_type(+) = 'VENDOR TYPE'
AND UPPER (lookup_code) = UPPER (v_type));
------------Cursor for vailid payment term------------
CURSOR cur_vailid_payment_term (v_payment VARCHAR2)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (SELECT term_id
FROM ap_terms
WHERE UPPER (NAME) = UPPER (v_payment));
-----------Cursor for vailid Location_code --------
CURSOR cur_vailid_location (v_location VARCHAR2)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (SELECT location_id
FROM hr_locations
WHERE location_code = v_location);
----------Cursor for vailid Supplier Currency ------
CURSOR cur_currency_code (v_currency_code VARCHAR2)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (
SELECT currency_code
FROM fnd_currencies
WHERE UPPER (currency_code) =
UPPER (v_currency_code));
-----------Cursor for vailid GL combination id --------
CURSOR cur_combination_id (v_combination_id NUMBER)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (SELECT code_combination_id
FROM gl_code_combinations
WHERE code_combination_id = v_combination_id);
-----------Cursor for vailid TERMS DATE BASIS --------
CURSOR cur_vailid_look_code (v_look_code VARCHAR2)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (
SELECT lookup_code
FROM fnd_lookup_values
WHERE fnd_lookup_values.lookup_type = 'TERMS DATE BASIS'
AND UPPER (lookup_code) = UPPER (v_look_code));
v_operating_unit_null VARCHAR2 (200);
v_vendor_null VARCHAR2 (200);
v_country_null VARCHAR2 (200);
v_vendor_type_null VARCHAR2 (200);
payment_term_null VARCHAR2 (200);
currency_null VARCHAR2 (200);
gl_liability_ac_null VARCHAR2 (200);
gl_advance_supplier_ac_null VARCHAR2 (200);
term_date_basis_null VARCHAR2 (200);
location_null VARCHAR2 (200);
v_error_message VARCHAR2 (32767);
BEGIN
<<supp_detl>>
FOR i IN cur_supplier_detl
LOOP
-----------------Refresh vairiable--------------
v_operating_unit_null := NULL;
v_vendor_null := NULL;
v_country_null := NULL;
v_vendor_type_null := NULL;
payment_term_null := NULL;
currency_null := NULL;
gl_liability_ac_null := NULL;
gl_advance_supplier_ac_null := NULL;
term_date_basis_null := NULL;
location_null := NULL;
v_error_message := NULL;
----------------For Operating Unit---------------
IF i.org_code IS NOT NULL
THEN
FOR cur_j IN cur_operating_unit (i.org_code)
LOOP
v_operating_unit_null := 'invailid Operating Unit.... ';
END LOOP;
ELSE
v_operating_unit_null := 'Operating Unit Can not Be null.... ';
END IF;
----------------CUR_VAILID_VENDOR --------
IF i.supplier_name IS NOT NULL
THEN
FOR cur_j IN cur_vailid_vendor (i.supplier_name)
LOOP
v_vendor_null := 'Invailid Supplier Name.... ';
END LOOP;
ELSE
v_vendor_null := 'Supplier Name can not be null.. ';
END IF;
-----------------Country-----------------
IF i.country IS NOT NULL
THEN
FOR cur_j IN cur_vailid_country (i.country)
LOOP
v_country_null := 'Invailid country.... ';
END LOOP;
END IF;
-----------------vendor type-----------------
IF i.supplier_classification IS NOT NULL
THEN
FOR cur_j IN cur_vailid_vendor_type (i.supplier_classification)
LOOP
v_vendor_type_null := 'Invailid vendor type.... ';
END LOOP;
END IF;
-------------------vailid_payment_term --------------
IF i.payment_term IS NOT NULL
THEN
FOR cur_j IN cur_vailid_payment_term (i.payment_term)
LOOP
payment_term_null := 'Invailid PAYMENT type.... ';
END LOOP;
END IF;
-------------vailid Location_code---------------------
IF i.location_code IS NOT NULL
THEN
FOR cur_j IN cur_vailid_location (i.location_code)
LOOP
location_null := 'Invailid location code.... ';
END LOOP;
END IF;
--------------currency code--------------------
IF i.invoice_ccy_code IS NOT NULL
THEN
FOR cur_j IN cur_currency_code (i.invoice_ccy_code)
LOOP
currency_null := 'Invailid currency code.... ';
END LOOP;
END IF;
---------------vailid GL combination id ---------
IF i.gl_liability_ac IS NOT NULL
THEN
FOR cur_j IN cur_combination_id (i.gl_liability_ac)
LOOP
gl_liability_ac_null := 'Invailid combination id.... ';
END LOOP;
END IF;
IF i.gl_advance_supplier_ac IS NOT NULL
THEN
FOR cur_j IN cur_combination_id (i.gl_advance_supplier_ac)
LOOP
gl_advance_supplier_ac_null := 'Invailid combination id.... ';
END LOOP;
END IF;
-------------------TERM_DATE_BASIS-----------
IF i.term_date_basis IS NOT NULL
THEN
FOR cur_j IN cur_vailid_look_code (i.term_date_basis)
LOOP
term_date_basis_null := 'Invailid TERM_DATE_BASIS.... ';
END LOOP;
END IF;
IF v_operating_unit_null IS NOT NULL
OR v_vendor_null IS NOT NULL
OR v_country_null IS NOT NULL
OR v_vendor_type_null IS NOT NULL
OR payment_term_null IS NOT NULL
OR currency_null IS NOT NULL
OR gl_liability_ac_null IS NOT NULL
OR gl_advance_supplier_ac_null IS NOT NULL
OR term_date_basis_null IS NOT NULL
OR location_null IS NOT NULL
THEN
v_error_message :=
v_operating_unit_null
|| ' ;'
|| v_vendor_null
|| ' ;'
|| v_country_null
|| ' ;'
|| v_vendor_type_null
|| ' ;'
|| payment_term_null
|| ' ;'
|| currency_null
|| ' ;'
|| gl_liability_ac_null
|| ' ;'
|| gl_advance_supplier_ac_null
|| ' ;'
|| term_date_basis_null
|| ' ;'
|| location_null;
UPDATE xx_supp_stag_all
SET vailidate_flag_process = 'E',
vailidate_error_message = v_error_message
WHERE entry_id = i.entry_id;
GOTO next_rec;
ELSE
UPDATE xx_supp_stag_all
SET vailidate_flag_process = 'P'
WHERE entry_id = i.entry_id;
GOTO next_rec;
END IF;
COMMIT;
END LOOP supp_detl;
END xx_supplier_vailidate;
Supplier Conversion in oracle apps r12
Supplier Conversion in oracle apps r12
------------------------------------------Second Procedure ------------------------------------------------
Supplier Conversion in oracle apps r12
Supplier Conversion in oracle apps r12
PROCEDURE xx_supplier_detl
IS
CURSOR cur_supplier
IS
SELECT org_code, supplier_name, invoice_ccy_code,
supplier_classification, location_code
FROM xx_supp_stag_all
WHERE NVL (vailidate_flag_process, 'E') = 'P'
AND NVL(FLAG_PROCESS,'NULL') ='NULL'
GROUP BY org_code, supplier_name, invoice_ccy_code,
supplier_classification, location_code;
CURSOR cur_supplier_site (v_org_code VARCHAR2, v_supp_name VARCHAR2)
IS
SELECT * FROM xx_supp_stag_all
WHERE NVL (vailidate_flag_process, 'E') = 'P'
AND NVL (flag_process ,'NULL') = 'NULL'
AND SUPPLIER_NAME = V_SUPP_NAME
AND ORG_CODE = V_ORG_CODE ;
-------------Operating Unit id ------
CURSOR operating_org_id (v_operating_unit VARCHAR2)
IS
SELECT organization_id
FROM hr_operating_units
WHERE UPPER (NAME) = UPPER (v_operating_unit);
------------Location Id---------------
CURSOR cur_loaction_id (v_location_code VARCHAR2)
IS
SELECT location_id
FROM hr_locations
WHERE location_code = v_location_code;
----------------Combination Code------
CURSOR cur_combination_id (v_combination_code VARCHAR2)
IS
SELECT code_combination_id
FROM gl_code_combinations
WHERE code_combination_id = TO_NUMBER (v_combination_code);
--WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5=V_COMBINATION_CODE;
--------------Payment Term Id ---------
CURSOR cur_term_id (v_term_code VARCHAR2)
IS
SELECT term_id
FROM ap_terms
WHERE UPPER (NAME) = UPPER (TRIM (v_term_code));
vendor_id_num NUMBER;
insert_success_flag VARCHAR2 (2);
v_sql_errm VARCHAR2 (2000);
site_vendor_id_num NUMBER;
p_organization_id NUMBER;
l_pay_code_combination_id NUMBER (10);
l_prepay_code_combination_id NUMBER (10);
l_location_id NUMBER (10);
l_term_id NUMBER (10);
v_country VARCHAR2 (200);
v_terms_date_basis VARCHAR2 (200);
BEGIN
<<head_supp_info>>
FOR i IN cur_supplier
LOOP
----------------Refresh vairiable ------------
vendor_id_num := NULL;
insert_success_flag := NULL;
v_sql_errm := NULL;
site_vendor_id_num := NULL;
p_organization_id := NULL;
l_pay_code_combination_id := NULL;
l_prepay_code_combination_id := NULL;
l_location_id := NULL;
l_term_id := NULL;
v_country := NULL;
v_terms_date_basis := NULL;
vendor_id_num := ap_suppliers_int_s.NEXTVAL;
BEGIN
SAVEPOINT header_insert;
-----------------Location Id------------
IF i.location_code IS NOT NULL
THEN
OPEN cur_loaction_id (i.location_code);
FETCH cur_loaction_id
INTO l_location_id;
IF cur_loaction_id%NOTFOUND
THEN
l_location_id := NULL;
CLOSE cur_loaction_id;
END IF;
CLOSE cur_loaction_id;
END IF;
INSERT INTO ap_suppliers_int
(vendor_interface_id, vendor_name, vendor_name_alt,
enabled_flag, attribute10,
vendor_type_lookup_code,
invoice_currency_code,
payment_currency_code, create_debit_memo_flag,
payment_method_lookup_code, bill_to_location_id,
ship_to_location_id
)
VALUES (vendor_id_num, i.supplier_name, i.supplier_name,
'Y', 'TEST DATA LOADING',
UPPER (i.supplier_classification),
NVL (i.invoice_ccy_code, 'INR'),
NVL (i.invoice_ccy_code, 'INR'), 'Y',
'CHECK', l_location_id,
l_location_id
);
insert_success_flag := 'Y';
EXCEPTION
WHEN OTHERS
THEN
insert_success_flag := 'N';
v_sql_errm := SQLERRM;
UPDATE xx_supp_stag_all
SET flag_process = 'E',
error_message = v_sql_errm
WHERE supplier_name = i.supplier_name
AND org_code = i.org_code
AND vailidate_flag_process = 'P';
GOTO next_supp;
END;
IF insert_success_flag = 'Y'
THEN
<<site_supp_info>>
FOR supp_site IN cur_supplier_site (i.org_code, i.supplier_name)
LOOP
---------------Operating Org Id ---------------
BEGIN
OPEN operating_org_id (i.org_code);
FETCH operating_org_id
INTO p_organization_id;
IF operating_org_id%NOTFOUND
THEN
p_organization_id := NULL;
CLOSE operating_org_id;
END IF;
CLOSE operating_org_id;
BEGIN
SELECT territory_code
INTO v_country
FROM fnd_territories
WHERE UPPER (nls_territory) = UPPER (supp_site.country);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_country := NULL;
WHEN OTHERS
THEN
v_country := NULL;
END;
------------Combination Code-----------
IF supp_site.gl_liability_ac IS NOT NULL
THEN
OPEN cur_combination_id (supp_site.gl_liability_ac);
FETCH cur_combination_id
INTO l_pay_code_combination_id;
IF cur_combination_id%NOTFOUND
THEN
l_pay_code_combination_id := NULL;
CLOSE cur_combination_id;
END IF;
CLOSE cur_combination_id;
END IF;
-------------
IF supp_site.gl_advance_supplier_ac IS NOT NULL
THEN
OPEN cur_combination_id
(supp_site.gl_advance_supplier_ac);
FETCH cur_combination_id
INTO l_prepay_code_combination_id;
IF cur_combination_id%NOTFOUND
THEN
l_pay_code_combination_id := NULL;
CLOSE cur_combination_id;
END IF;
CLOSE cur_combination_id;
END IF;
---------------Term_id -----------------
IF supp_site.payment_term IS NOT NULL
THEN
OPEN cur_term_id (supp_site.payment_term);
FETCH cur_term_id
INTO l_term_id;
IF cur_term_id%NOTFOUND
THEN
l_term_id := NULL;
CLOSE cur_term_id;
END IF;
CLOSE cur_term_id;
END IF;
---------------Term data basis-----------
BEGIN
SELECT lookup_code
INTO v_terms_date_basis
FROM fnd_lookup_values
WHERE fnd_lookup_values.lookup_type = 'TERMS DATE BASIS'
AND UPPER (lookup_code) =
UPPER (supp_site.term_date_basis);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_terms_date_basis := NULL;
WHEN OTHERS
THEN
v_terms_date_basis := NULL;
END;
INSERT INTO ap_supplier_sites_int
(vendor_interface_id, vendor_site_interface_id,
vendor_site_code,
vendor_site_code_alt,
payment_method_lookup_code, terms_id,
address_line1,
address_line2,
address_line3, org_id,
city, province,
area_code, state,
country,
phone,
fax_area_code,
fax,
zip,
email_address, bill_to_location_id,
ship_to_location_id, match_option,
terms_date_basis, purchasing_site_flag,
pay_site_flag, create_debit_memo_flag,
accts_pay_code_combination_id,
prepay_code_combination_id,
attribute10,
attribute9,
attribute6, attribute7
)
VALUES (vendor_id_num, ap_suppliers_int_s.NEXTVAL,
NVL (supp_site.supplier_site_name, 'OFFICE'),
NVL (supp_site.supplier_site_name, 'OFFICE'),
'CHECK', l_term_id,
NVL (supp_site.address_line1, 'Not Specified'),
supp_site.address_line2,
supp_site.address_line3, p_organization_id,
supp_site.city, supp_site.city,
NVL (supp_site.std_code, 00), supp_site.state,
v_country,
SUBSTR (supp_site.phone_number, 1, 15),
NVL (supp_site.std_code, 00),
SUBSTR (supp_site.fax_number, 1, 15),
supp_site.postal_code,
supp_site.email_address, l_location_id,
l_location_id, 'R',
-- upper(NVL(V_TERMS_DATE_BASIS,'Invoice')),
NVL (v_terms_date_basis, 'Invoice'), 'Y',
'Y', 'Y',
l_pay_code_combination_id,
l_prepay_code_combination_id,
'TEST DATA LOADING',,
supp_site.term_date_basis,
supp_site.phone_number, supp_site.fax_number
);
UPDATE xx_supp_stag_all
SET flag_process = 'P'
WHERE entry_id = supp_site.entry_id;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO SAVEPOINT header_insert;
insert_success_flag := 'N';
v_sql_errm := SQLERRM;
UPDATE xx_supp_stag_all
SET flag_process = 'E',
error_message = v_sql_errm
WHERE supplier_name = i.supplier_name
AND org_code = i.org_code
AND vailidate_flag_process = 'P';
GOTO next_supp;
END;
END LOOP site_supp_info;
END IF;
COMMIT;
END LOOP head_supp_info;
END xx_supplier_detl;
|
| Supplier Interface Tables in Oracle Apps r12 |
0 comments:
Post a Comment