Payable Invoice Interface

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;
/