In this post , I will share you the PLSQL code for Supplier Interface.
I am using xx_supp_stag_all staging table where I have uploaded Supplier data through SQL loader. I am sharing you two procedures.
In the First procedure , I will share you the validation script so that you can validate the Data from your Staging table like the values you are uploading for Supplier is available in Oracle apps or not.
In the Second Procedure I will share your the Execution script to upload validated date in Supplier Interface Table.
Second Procedure (Execution Script)
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;
<<next_supp>>
COMMIT;
END LOOP head_supp_info;
END xx_supplier_detl;
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;
<<next_supp>>
COMMIT;
END LOOP head_supp_info;
END xx_supplier_detl;
3 comments:
Good Blog Thanks For Sharing this informative airtrl/
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad
Thanks for sharing This informative article. It would be helpful to all ,Keep sharing.
Oracle Fusion HCM Online Training
Good Blog thanks for sharing this informative article. It would be helpful for improving their knowledge.
Oracle Fusion HCM Online Training
Post a Comment