Payable Invoice Interface

Payable Invoice Interfaces
***************************
Interface Tables are :-
----------------------
ap_invoices_interface
ap_invoice_lines_interface


Create staging table :-
----------------------
CREATE TABLE XXX_AP_INVOICES_STG
(
INVOICE_TYPE VARCHAR2(30),
VENDOR_NAME VARCHAR2(150),
VENDOR_SITE VARCHAR2(150),
INVOICE_CURRENCY VARCHAR2(10),
EXCHANGE_RATE NUMBER(10,2),
INVOICE_NO VARCHAR2(30),
INVOICE_DATE DATE,
INVOICE_AMOUNT NUMBER(20,2),
DESCRIPTION VARCHAR2(200),
GL_DATE DATE,
LINE_NUMBER NUMBER(3),
LINE_TYPE VARCHAR2(50),
LINE_AMOUNT NUMBER(20,2),
CODE_COMBINATION VARCHAR2(50),
H_ERROR_MESSAGE VARCHAR2(2000),
H_VERIFY_FLAG CHAR(1),
L_ERROR_MESSAGE VARCHAR2(2000),
L_VERIFY_FLAG CHAR(1)
)

Run the following script to upload data from staging table to interface table


CREATE OR REPLACE procedure xxx_ap_invoice_api
as

l_verify_flag char(1);
l_org_id hr_operating_units.organization_id%type;
l_error_message varchar2(3000);
l_invoice_type ap_lookup_codes.lookup_code%type;
l_vendor_id po_vendors.vendor_id%type ;
l_vendor_site_id po_vendor_sites_all.vendor_site_id%type;
l_line_type ap_invoice_lines_interface.line_type_lookup_code%type;
l_currency_code fnd_currencies.currency_code%type ;
l_code_combination_id number(20);


cursor h_invoice is
select distinct invoice_type,
invoice_no,
vendor_name,
vendor_site,
invoice_currency,
invoice_date,
invoice_amount,
description,
gl_date,
exchange_rate
from xxx_ap_invoices_stg
where nvl(h_verify_flag,'N') = 'N';


cursor l_invoice (p_invoice_no varchar2) is
select distinct invoice_no,
description,
line_type,
code_combination,
line_number,
line_amount
from xxx_ap_invoices_stg
where invoice_no = p_invoice_no
and nvl(l_verify_flag,'N') = 'N';



begin


for h1 in h_invoice loop

l_verify_flag := 'Y';
l_error_message := null;



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 := 'Invalid Operating Unit...';
end;


begin

select lookup_code
into l_invoice_type
from ap_lookup_codes
where lookup_type(+) = 'INVOICE TYPE'
and upper(displayed_field) = upper(trim(h1.invoice_type));

exception
when others then
l_verify_flag := 'N';
l_error_message := 'Invalid Invoice Type...';
end;


begin

select pv.vendor_id,
pvs.vendor_site_id
into l_vendor_id,
l_vendor_site_id
from po_vendors pv,
po_vendor_sites_all pvs
where upper(pv.vendor_name) = upper(trim(h1.vendor_name))
and pv.vendor_id = pvs.vendor_id
and pvs.vendor_site_code = h1.vendor_site;

Exception
when no_data_found then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Vendor or Vendor Site not found...' ;

when too_many_rows then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Vendor or Vendor Site is more than one...' ;

when others then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Vendor or Vendor Site has other errors' ;

end;


begin

select currency_code
into l_currency_code
from fnd_currencies
where currency_code = h1.invoice_currency;

Exception
when no_data_found then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Currency Code not found...' ;

when too_many_rows then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Currency Code is more than one...' ;

when others then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Currency code has other errors' ;
end;



If (h1.invoice_no is null or h1.invoice_date is null or h1.invoice_amount is null or h1.gl_date is null) then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Invoice No or Invoice Date or Invoice Amount or GL Date is Missing...' ;
end if;



if l_verify_flag <> 'N' then
--
-- inserting into ap_invoices_interface table
--
begin

insert into
ap_invoices_interface( invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
description,
source,
gl_date,
org_id,
exchange_rate,
goods_received_date,
group_id ---To restrict the import
) values ( ap_invoices_interface_s.nextval,
h1.Invoice_No,
l_invoice_type,
h1.invoice_date,
l_vendor_id,
l_vendor_site_id,
h1.invoice_amount,
l_currency_code,
h1.description,
'LEGACY',
h1.gl_date,
l_org_id,
h1.exchange_rate,
h1.invoice_date-10,
'MIGRATE' );

update xxx_ap_invoices_stg
set h_verify_flag = 'Y'
where invoice_no = h1.invoice_no;
commit;

exception
when others then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Data not migrated into ap_invoices_interface table...' ;
update xxx_ap_invoices_stg
set h_verify_flag = 'N',
h_error_message = l_error_message
where invoice_no = h1.invoice_no;
end;


--
-- lines data inserting into ap_invoice_lines_interface
--

for l1 in l_invoice (h1.invoice_no)
loop


begin

select lookup_code
into l_line_type
from ap_lookup_codes
where lookup_type(+) = 'INVOICE DISTRIBUTION TYPE'
and displayed_field = l1.line_type;

exception
when others then
l_verify_flag := 'N';
l_error_message := 'Line type is not valid...';

end;


begin

select code_combination_id
into l_code_combination_id
from gl_code_combinations_v
where segment1||'-'||segment2||'-'||segment3||'-'||segment4||'-'||segment5||'-'||segment6 = l1.code_combination;

exception
when others then
l_verify_flag := 'N';
l_error_message := 'Code Combination is not valid...';
end;

if l_verify_flag <> 'N' then

begin

insert into
ap_invoice_lines_interface ( invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
dist_code_combination_id,
org_id,
description
)values( ap_invoices_interface_s.currval,
ap_invoice_lines_interface_s.nextval,
nvl(l1.line_number,1),
l_line_type,
l1.line_amount,
l_code_combination_id,
l_org_id,
l1.description);

update xxx_ap_invoices_stg
set l_verify_flag = 'Y'
where invoice_no = l1.invoice_no
and line_number = l1.line_number;


exception

when others then
l_error_message := l_error_message || sqlerrm;
l_verify_flag := 'N';
update xxx_ap_invoices_stg
set l_verify_flag = 'N',
l_error_message = l_error_message
where invoice_no = l1.invoice_no
and line_number = l1.line_number;

commit;

end;

else

update xxx_ap_invoices_stg
set l_error_message = l_error_message,
l_verify_flag = 'N'
where invoice_no = l1.invoice_no
and line_number = l1.line_number;

end if;

end loop; ---end of line loop



else

update xxx_ap_invoices_stg
set h_error_message = l_error_message,
h_verify_flag = 'N'
where invoice_no = h1.invoice_no;

end if;


end loop; ---end of header loop

commit;

end xxx_ap_invoice_api;
/

Item Inteface

ITEM INTERFACE
*********************

Item Interface Table :-
------------------------
MTL_SYSTEM_ITEMS_INTERFACE

Import Program
----------------
-> Go to Inventory Responsibility
-> Items
-> Import
-> Import Items

Two things you have to be careful
1. You have to select the item master organization or sub organization
for which you have to import the items.
2. If you are updating the existing item, instead of CREATE you have
to insert as UPDATE and select 2 in the Create or Update Items in the
parameter of Import Items.

Data Template :-

SEGMENT1
SEGMENT2
SEGMENT3
SEGMENT4
DESCRIPTION
PRIMARY_UOM_CODE
ORGANIZATION_CODE
TEMPLATE_NAME
ATTRIBUTE_CATEGORY
ATTRIBUTE1
ATTRIBUTE2
ATTRIBUTE3
ATTRIBUTE4
ATTRIBUTE8
ATTRIBUTE9

Create staging table :-

CREATE TABLE XXX_ITEM_MASTER_STG
(
SEGMENT1 VARCHAR2(40),
SEGMENT2 VARCHAR2(40),
SEGMENT3 VARCHAR2(40),
SEGMENT4 VARCHAR2(40),
DESCRIPTION VARCHAR2(240),
PRIMARY_UNIT_OF_MEASURE VARCHAR2(25),
ORGANIZATION_CODE VARCHAR2(3),
TEMPLATE_NAME VARCHAR2(30),
ATTRIBUTE_CATEGORY VARCHAR2(30),
ATTRIBUTE1 VARCHAR2(240),
ATTRIBUTE2 VARCHAR2(240),
ATTRIBUTE3 VARCHAR2(240),
ATTRIBUTE4 VARCHAR2(240),
ATTRIBUTE8 VARCHAR2(240),
ATTRIBUTE9 VARCHAR2(240),
VERIFY_FLAG CHAR(1),
ERROR_MESSAGE VARCHAR2(3000)
)


Run the following script to upload data from staging table to interface table
Then run the Import Items.



CREATE OR REPLACE PROCEDURE xxx_Item_Master_Org_Api
(errbuf out varchar2, rectcode out varchar2)
AS


L_VERIFY_FLAG VARCHAR2(3);
L_ERROR_MESSAGE VARCHAR2(2500);
L_COUNT NUMBER(2);
L_CATEGORY_SET_ID NUMBER(20);
L_CATEGORY_ID NUMBER(20);
L_ORG_ID NUMBER(4);
L_TEMPLATE_NAME VARCHAR2(250);
L_UOM VARCHAR2(20);
L_ITEM_TYPE VARCHAR2(20);
L_ORGANIZATION_ID NUMBER(10);
L_ORGANIZATION_CODE VARCHAR2(10);


CURSOR C1 IS
SELECT *
FROM
xxx_ITEM_MASTER_STG;

BEGIN

FOR C_REC IN C1 LOOP


L_VERIFY_FLAG:='Y';
L_ERROR_MESSAGE:= NULL;
L_COUNT := 0;


BEGIN
SELECT ORGANIZATION_ID,ORGANIZATION_CODE
INTO L_ORGANIZATION_ID,L_ORGANIZATION_CODE
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = TRIM(UPPER(C_REC.ORGANIZATION_CODE));
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := 'INVALID ORGANIZATION' ;
END ;



BEGIN
SELECT COUNT(*)
INTO L_COUNT
FROM MTL_SYSTEM_ITEMS_B
WHERE SEGMENT1||'.'||SEGMENT2
||'.'||SEGMENT3||'.'||SEGMENT4 =
C_REC.SEGMENT1||'.'||C_REC.SEGMENT2
||'.'||C_REC.SEGMENT3||'.'||C_REC.SEGMENT4
AND ORGANIZATION_ID = L_ORGANIZATION_ID;
IF L_COUNT > 0 THEN
L_VERIFY_FLAG:= 'N';
L_ERROR_MESSAGE:=L_ERROR_MESSAGE|| 'ITEM ALREADY EXISTING' ;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;


IF ((TRIM(C_REC.SEGMENT1) IS NULL) OR
(TRIM(C_REC.SEGMENT2) IS NULL) OR
(TRIM(C_REC.SEGMENT3) IS NULL) OR
(TRIM(C_REC.SEGMENT4) IS NULL)) THEN
L_VERIFY_FLAG:= 'N';
L_ERROR_MESSAGE:= L_ERROR_MESSAGE|| 'ITEM SEGMENT SHOULD NOT BE NULL';
END IF;


IF TRIM(C_REC.DESCRIPTION) IS NULL THEN
L_VERIFY_FLAG:= 'N';
L_ERROR_MESSAGE:= L_ERROR_MESSAGE|| 'INVALID DESCRIPTION';
END IF;


BEGIN
SELECT TEMPLATE_NAME
INTO L_TEMPLATE_NAME
FROM MTL_ITEM_TEMPLATES
WHERE UPPER(TRIM(TEMPLATE_NAME)) = UPPER(TRIM(C_REC.TEMPLATE_NAME));
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG:= 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'INVALID TEMPLATE NAME';
END ;


BEGIN
SELECT UNIT_OF_MEASURE
INTO L_UOM
FROM MTL_UNITS_OF_MEASURE
WHERE UPPER(TRIM(UOM_CODE)) = UPPER(TRIM(C_REC.PRIMARY_UNIT_OF_MEASURE));
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG:= 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'INVALID UOM';
END;



IF L_VERIFY_FLAG <> 'N' THEN

BEGIN

INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
(
PROCESS_FLAG
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, DESCRIPTION
, PRIMARY_UNIT_OF_MEASURE
, SET_PROCESS_ID
, TEMPLATE_NAME
, ORGANIZATION_ID
, ORGANIZATION_CODE
, TRANSACTION_TYPE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE8
, ATTRIBUTE9
)
VALUES
(
1
, TRIM(C_REC.SEGMENT1)
, TRIM(C_REC.SEGMENT2)
, TRIM(C_REC.SEGMENT3)
, TRIM(C_REC.SEGMENT4)
, TRIM(C_REC.DESCRIPTION)
, L_UOM
, 1
, L_TEMPLATE_NAME
, L_ORGANIZATION_ID
, L_ORGANIZATION_CODE
, 'CREATE'
, C_REC.ATTRIBUTE_CATEGORY
, C_REC.ATTRIBUTE1
, C_REC.ATTRIBUTE2
, C_REC.ATTRIBUTE3
, C_REC.ATTRIBUTE4
, C_REC.ATTRIBUTE8
, C_REC.ATTRIBUTE9
);


UPDATE xxx_ITEM_MASTER_STG
SET VERIFY_FLAG = 'Y'
WHERE SEGMENT1||'.'||SEGMENT2
||'.'||SEGMENT3||'.'||SEGMENT4 =
C_REC.SEGMENT1||'.'||C_REC.SEGMENT2
||'.'||C_REC.SEGMENT3||'.'||C_REC.SEGMENT4;

EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:= SQLERRM;
UPDATE xxx_ITEM_MASTER_STG
SET VERIFY_FLAG = 'N',
ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE SEGMENT1||'.'||SEGMENT2
||'.'||SEGMENT3||'.'||SEGMENT4 =
C_REC.SEGMENT1||'.'||C_REC.SEGMENT2||'.'||
C_REC.SEGMENT3||'.'||C_REC.SEGMENT4;
END;

COMMIT;

ELSE

UPDATE xxx_ITEM_MASTER_STG
SET VERIFY_FLAG = 'N'
,ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE SEGMENT1||'.'||SEGMENT2||'.'||
SEGMENT3||'.'||SEGMENT4 =
C_REC.SEGMENT1||'.'||C_REC.SEGMENT2
||'.'||C_REC.SEGMENT3||'.'||C_REC.SEGMENT4;

COMMIT;

END IF;



END LOOP;

COMMIT;

END xxx_Item_Master_Org_Api;
/



UPDATE ITEMS
*****************

CREATE OR REPLACE PROCEDURE xxx_item_update_api
AS

l_inventory_item_id number(10);
l_verify_flag varchar2(1) := 'N';
l_error_message varchar2(2500);
l_organization_id number(5);
l_organization_code varchar2(5);

CURSOR C_ITEM
IS
select segment1,
segment2,
segment3,
segment4,
min_minmax_qty
from xxx_ITEM_MASTER_STG ;

BEGIN

FOR c1 in c_item
loop

l_verify_flag := 'Y';
l_error_message := null;

BEGIN
select organization_id,organization_code
into l_organization_id,l_organization_code
from org_organization_definitions
where operating_unit = 284
and organization_code = 'SPT';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Organization not Valid';
END;


BEGIN
Select inventory_item_id
into l_inventory_item_id
from mtl_system_items_b
where organization_id = l_organization_id
and trim(segment1) = trim(c1.segment1)
and trim(segment2) = trim(c1.segment2)
and trim(segment3) = trim(c1.segment3)
and trim(segment4) = trim(c1.segment4);
Exception
when others then
l_verify_flag := 'N';
l_error_message := 'Item Not Valid';
END;

IF l_verify_flag <> 'N' THEN

BEGIN
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
( INVENTORY_ITEM_ID
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, PROCESS_FLAG
, SET_PROCESS_ID
, ORGANIZATION_ID
, ORGANIZATION_CODE
, TRANSACTION_TYPE
, SOURCE_TYPE
, MIN_MINMAX_QUANTITY
)
VALUES
( l_inventory_item_id
, trim(c1.SEGMENT1)
, trim(c1.SEGMENT2)
, trim(c1.SEGMENT3)
, trim(c1.SEGMENT4)
, 1
, 1
, l_organization_id
, l_organization_code
, 'UPDATE'
, 2
, trim(c1.min_minmax_qty)
);

UPDATE xxx_ITEM_MASTER_STG
SET VERIFY_FLAG ='Y'
WHERE trim(segment1) = trim(c1.segment1)
and trim(segment2) = trim(c1.segment2)
and trim(segment3) = trim(c1.segment3)
and trim(segment4) = trim(c1.segment4);
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
UPDATE xxx_ITEM_MASTER_STG
SET VERIFY_FLAG ='N',
error_message = l_error_message
WHERE trim(segment1) = trim(c1.segment1)
and trim(segment2) = trim(c1.segment2)
and trim(segment3) = trim(c1.segment3)
and trim(segment4) = trim(c1.segment4);
END;

ELSE

UPDATE xxx_ITEM_MASTER_STG
SET VERIFY_FLAG ='N',
error_message = l_error_message
WHERE trim(segment1) = trim(c1.segment1)
and trim(segment2) = trim(c1.segment2)
and trim(segment3) = trim(c1.segment3)
and trim(segment4) = trim(c1.segment4) ;

END IF;
COMMIT;
END LOOP ;


END xxx_item_update_api;
/

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.

Oracle Reports

Oralce Reports Development
**************************
Step by step i am describing how to register a report in Oracle Apps.
1. Develop the report in Reportbuilder.
Note :- Whenever saving the report in your local machine pls be
careful not to give '.RDF' but to give '.rdf' '.RDf' will make error
while you are running the report in Apps.
2. FTP the rdf file from your local machine to corresponding TOP or custom_TOP
3. Register the report in executable
a) Login to Apps -> System Administrator/Application Developer - > Concurrent : Program/Concurrent -> Executable ->Execution File Name :- Same name as rdf fileShort Name :- This name will refer to Program
4. Register the report in Program
5. Assign the Program Name to required request group.
Now the report is ready to run.