Supplier and Supplier Sites Interfaces - ap_suppliers_int, ap_supplier_sites_int

Supplier and Supplier Sites Interfaces

SUPPLIER AND SUPPLIER SITES INTERFACES
************************************************
RDBMS : 9.2.0.8.0
Oracle Applications : 11.5.10.2
Interface Tables :-
a) ap_suppliers_int
b) ap_supplier_sites_int
Import Program :-
1) Supplier Open Interface Import
2) Supplier Sites Open Interface Import

Steps
1) Preapare the data template :-
LEGACY_SUPP_CODE, VENDOR_NAME,VENDOR_TYPE,
PAYMENT_TERMS,INVOICE_CURRENCY,PAYMENT_CURRENCY,
ACCTS_PAY_CODE_COMBINATION,PREPAY_CODE_COMBINATION,
VENDOR_SITE_CODE,ADDRESS1,ADDRESS2 ,ADDRESS3 ,ADDRESS4 ,
CITY,STATE ,COUNTRY,ZIP,CONTACTS_NAME,PHONE ,EMAIL ,
SHIP_TO_LOC,BILL_TO_LOC
Here all are not mandatory fields.
Pls customize the template according to your requriements.

2) Now create the staging table :-
CREATE TABLE XXX_AP_SUPPLIERS_STG
(LEGACY_SUPP_CODE NUMBER(10),
VENDOR_NAME VARCHAR2(100),
VENDOR_TYPE VARCHAR2(30),
PAYMENT_TERMS VARCHAR2(100),
INVOICE_CURRENCY VARCHAR2(10),
PAYMENT_CURRENCY VARCHAR2(10),
ACCTS_PAY_CODE_COMBINATION VARCHAR2(50),
PREPAY_CODE_COMBINATION VARCHAR2(50),
VENDOR_SITE_CODE VARCHAR2(50),
ADDRESS1 VARCHAR2(100),
ADDRESS2 VARCHAR2(100),
ADDRESS3 VARCHAR2(100),
ADDRESS4 VARCHAR2(100),
CITY VARCHAR2(50),
STATE VARCHAR2(50),
COUNTRY VARCHAR2(30),
ZIP VARCHAR2(10),
CONTACTS_NAME VARCHAR2(30),
PHONE VARCHAR2(50),
EMAIL VARCHAR2(100),
SHIP_TO_LOC VARCHAR2(100),
BILL_TO_LOC VARCHAR2(100),
PAN_NO VARCHAR2(30),
VERIFY_FLAG CHAR(1),
L_VERIFY_FLAG CHAR(1),
ERROR_MESSAGE VARCHAR2(2500))

3) Import data from data template ie. excel file to staging table
You can import data through different ways.
a) By using control file and sqlloader
b) Through Toad.(Version 8.5 and above)

4) Execute the following Procedure in Toad
->Open the procedure editior
-> Paste the following procedure
->Make changes according to your requirement
-> Press Ctl+Enter.
Now the procdure created.
-> Go to schema browser
-> Procedure
-> XXX_create_supplier_api
->right button
-> execute procedure.
The below procedure will do the basic validation required
and upload data into interface tables. The importance of this script
is you will get your customized error message in stagingtable and
you can easily identify and rectify the errors.

CREATE OR REPLACE PROCEDURE xxx_create_supplier_api
AS
l_vendor_type varchar2(30);
l_verify_flag varchar(1);
l_error_message varchar2(2500);
l_invoice_currency varchar2(10);
l_payment_currency varchar2(10);
l_term_id number(10);
l_pay_code_combination_id number(10);
l_prepay_code_combination_id number(10);
l_org_id number(10);
l_territory_code varchar2(10);
l_cnt number(3);
l_location_id number(10);
l_vendor_name varchar2(150);l
_vendor_site_code varchar2(100);

CURSOR c_supp IS
SELECT distinct vendor_type
,payment_terms
,vendor_name
FROM xxx_ap_suppliers_stg
where nvl(verify_flag,'N') = 'N';

CURSOR c_supp_site (p_supp_name varchar2) IS
SELECT *
FROM xxx_ap_suppliers_stg
WHERE vendor_name = p_supp_name;

BEGIN

FOR H1 IN c_supp
LOOP
l_verify_flag := 'Y' ;
l_error_message := NULL ;
l_cnt := 0 ;
l_vendor_name := NULL;

BEGIN
select count(*)
into l_cnt
from po_vendors
where trim(upper(vendor_name)) = trim(upper(H1.vendor_name));
IF l_cnt > 0 then
l_verify_flag := 'N';
l_error_message:= 'Vendor is already existing';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

BEGIN
select organization_id
into l_org_id
from hr_operating_units
where name = 'xxx Operating Unit';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Operating Unit is Invalid';
END;

BEGIN
select location_id
into l_location_id
from hr_locations
where location_code = 'xxx Main Store Location';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Location is Not Valid';
END;

BEGIN
SELECT lookup_code
INTO l_vendor_type
FROM po_lookup_codes
WHERE lookup_type(+) = 'VENDOR TYPE'
AND UPPER(lookup_code) = UPPER(TRIM(H1.vendor_type));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Vendor Type Lookup Code not existing';
END;

BEGIN
select currency_code
into l_invoice_currency
from fnd_currencies
where currency_code = trim(H1.invoice_currency);
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Invoice Currency Code is not Valid';
END;

BEGIN
select currency_codeinto
l_payment_currency
from fnd_currencies
where currency_code = trim(H1.payment_currency);
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message:=l_error_message'Payament Currency Cocde is not valid';
END;

BEGIN
select term_id
into l_term_id
from ap_terms
where upper(name) = upper(trim(H1.payment_terms)) ;
EXCEPTION
when others then
l_verify_flag := 'N';
l_error_message := l_error_message'Payment Term is not valid';
END;

BEGIN
select code_combination_id
into l_pay_code_combination_id
from gl_code_combinations
where segment1'.'segment2'.'
egment3'.'segment4'.'
segment5'.'segment6= H1.accts_pay_code_combination ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message
'Accounts Pay CodeCombination is Not Valid';
END;

BEGIN
select code_combination_id
into l_prepay_code_combination_id
from gl_code_combinations
where segment1'.'segment2'.'segment3'.'
segment4'.'segment5'.' segment6=
H1.prepay_code_combination;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Pre-Pay Code Combination is Not Valid';
END;

IF H1.vendor_name is null then
l_verify_flag := 'N';
l_error_message := l_error_message'Vendor Name is not existing';
end if;

savepoint A;
IF l_verify_flag <> 'N' THEN
BEGIN

INSERT INTO
ap.ap_suppliers_int
(
vendor_interface_id,
vendor_name,
vendor_name_alt,
vendor_type_lookup_code,
invoice_currency_code,
payment_currency_code,
terms_id,
accts_pay_code_combination_id,
prepay_code_combination_id,
bill_to_location_id,
ship_to_location_id,
receiving_routing_id,
inspection_required_flag,
receipt_required_flag
)
VALUES
(
ap_suppliers_int_s.nextval,
trim(H1.vendor_name),
trim(H1.vendor_name),
l_vendor_type,
l_invoice_currency,
l_payment_currency,
l_term_id,
l_pay_code_combination_id,
l_prepay_code_combination_id,
l_location_id,
l_location_id,1,'N','Y'
) ;

UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'Y'
WHERE vendor_name = H1.vendor_name;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'N',
error_message = l_error_message
WHERE vendor_name = H1.vendor_name;
GOTO next_supp;
END;

FOR L1 IN c_supp_site(H1.vendor_name)
LOOP
l_vendor_site_code := NULL;
BEGIN
select territory_code
into l_territory_code
from fnd_territories
where nls_territory = trim(L1.country) ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message 'Invalid Country';
NULL;
END;

BEGIN
select vendor_site_code
into l_vendor_site_code
from po_vendor_sites_all a,
po_vendors b
where org_id = l_org_id
and upper(vendor_site_code) = trim(upper(L1.vendor_site_code))
and a.vendor_id = b.vendor_id
and upper(b.vendor_name) = trim(upper(L1.vendor_name)) ;
IF l_vendor_site_code is not null then
l_verify_flag := 'N';
l_error_message := l_error_message 'Vendor Site is already existing';
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;
IF l_verify_flag <> 'N' then
BEGIN
INSERT INTO
ap.ap_supplier_sites_int
(
vendor_interface_id,
vendor_site_code,
vendor_site_code_alt,
address_line1,
address_line2,
address_line3,
city,
state,
country,
zip,
phone,
accts_pay_code_combination_id ,
prepay_code_combination_id ,
org_id,
attribute_category,
attribute10,
ship_to_location_id,
bill_to_location_id,
hold_unmatched_invoices_flag
)
VALUES
(
ap_suppliers_int_s.CURRVAL,
trim(L1.vendor_site_code),
trim(L1.vendor_site_code),
trim(L1.address1),
trim(L1.address2),
trim(L1.address3),
trim(L1.city),
trim(L1.state),
l_territory_code,
trim(L1.zip),
trim(L1.phone),
l_pay_code_combination_id,
l_prepay_code_combination_id ,
l_org_id,
Legacy Supplier Code',
trim(L1.Baan_Supp_code),
l_location_id,
l_location_id,
'N'
) ;

UPDATE xxx_ap_suppliers_stg
SET l_verify_flag = 'Y'
WHERE vendor_name = L1.vendor_name
AND vendor_site_code = L1.vendor_site_code;
EXCEPTION
WHEN OTHERS THEN
rollback to savepoint A;
l_error_message := SQLERRM;
UPDATE xxx_ap_suppliers_stg
SET l_verify_flag = 'N',
error_message = l_error_message
WHERE vendor_name = L1.vendor_name
AND vendor_site_code = L1.vendor_site_code;
UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'N'
WHERE vendor_name = L1.vendor_name;
GOTO next_supp;
END;

ELSE
rollback to savepoint A;
UPDATE xxx_ap_suppliers_stg
SET l_verify_flag = 'N',
error_message = l_error_message
WHERE vendor_name = L1.vendor_name
AND vendor_site_code = L1.vendor_site_code;
UPDATE xxx_ap_suppliers_stg
SET verify_flag = 'N'
WHERE vendor_name = L1.vendor_name;
GOTO next_supp;
END IF;

END LOOP ;

ELSE
UPDATE xxx_ap_suppliers_stg
SET error_message = l_error_message,
verify_flag = 'N'
WHERE vendor_name = H1.vendor_name;
END IF;

<<>>
Commit;
END LOOP ;

END xxx_create_supplier_api;

Go to payable responsibility and run the following concurrent programs
1) Supplier Open Interface Import
2) Sites Open Interface Import
After completing the first concurrent pgm, run the second one.
And you can see the imported no of records and rejected no of records in View output.