Pre-requisites:
a) Vendors and their Sites Should Be Setup
b) Currency and exchange Rates Should Be setup
c) Source and invoice type lookup code must be defined
d) Account Payables Account and Distribution Account must be defined.
e) Payment Terms should be defines and GL Period must be in opened status.
Interface Tables:
AP_INVOICES_INTERFACE
AP_INVOCE_LINES_INTERFACE
BASE TABLES :
AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
AP_PAYMENT_SCHEDULES
INTERFACE PROGRAM:
Payables Open Interface Impor
Parameters to be passed during the program:
Source = External
group_id = As Specified in the data file
Batch Name = N/A
Hold Name = null
Hold Reason = null
gl date = null
Purge = No
Summarize = No
Error Tables:
AP_INTERFACE_REJECTIONS
AP_INTERFACE_CONTROLS
Main Columns In AP_INVOICES_INTERFACE:
INVOICE_ID, INVOICE_NUM, INVOICE_DATE ,INVOICE_AMOUNT
VENDOR_ID , VENDOR_NUM, VENDOR_NAME ,VENDOR_SITE_CODE, VENDOR_SITE_ID
TERMS_NAME, TERM_ID, TERM_DATE, SOURCE, PAYMENT_METHOD_LOOKUP_CODE,
GL_DATE, PO_NUMBER, INVOICE_CURRENCE_CODE, EXCHANGE_RATE_TYPE
VOUCHER_NUMBER, PAYMENT_CURRENCY_CODE, DOC_CATEGPRY_CODE
Main Columns In AP_INVOICE_LINES_INTERFACE:
INVOICE_ID , INVOICE_LINE_ID , LINE_NUM, LINE_TYPE_LOOKUP_CODE
AMOUNR , DIST_CODE_COMBINATION_ID, DIST_CODE_CONCATENATED
PO_HEADER_ID, PO_LINE_ID, PO_LINE_NUMBER
Tables To Be Used For Validation:
PO_VENDORS AP_TERMS ORG_ORGANIZATION_DEFINITIONS
FND_CURRNECIES AP_INVOICES_ALL GL_DAILY_CONVERSION_TYPES
GL_CODE_COMBINATIONS AP_LOOKUP_CODES GL_PERIOD_STATUSES
Control file for Headers:-
OPTIONS(SKIP=1)
LOAD DATA
INFILE '/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_temp.csv'
truncate into table ap_int_temp
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
INVOICE_NUM ,
INVOICE_DATE ,
INVOICE_TYPE_LOOKUP_CODE ,
VENDOR_NAME ,
VENDOR_SITE_CODE ,
INVOICE_AMOUNT ,
INVOICE_CURRENCY_CODE ,
DESCRIPTION ,
SOURCE ,
PAYMENT_METHOD_LOOKUP_CODE ,
PAY_GROUP_LOOKUP_CODE ,
OU_NAME "REPLACE(:OU_NAME,CHR(13),'')"
)
For Lines:-
OPTIONS(SKIP=1)
LOAD DATA
INFILE '/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_line_temp.csv'
truncate into table ap_int_line_temp
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
INVOICE_NUM ,
OU_NAME ,
LINE_NUMBER ,
LINE_TYPE_LOOKUP_CODE ,
AMOUNT ,
DESCRIPTION ,
DIST_CODE_CONCATENATED "REPLACE(:DIST_CODE_CONCATENATED,CHR(13),'')"
)
Shell Script:-
sqlldr apps/apps control='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_temp.ctl' log='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_temp.log'
sqlldr apps/apps control='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_line_temp.ctl' log='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_line_temp.log'
exit 0
Common error table and procedure:-
create table ap_common_err_tab
(
interface_name varchar2(50),
table_name varchar2(50),
column_name varchar2(50),
error_message varchar2(100),
created_by varchar2(10),
creation_date date,
last_updated_by varchar2(10),
last_update_date date,
request_id varchar2(10)
);
create or replace procedure ap_common_err_proc
(
p_interface_name varchar2,
p_table_name varchar2,
p_column_name varchar2,
p_error_message varchar2
)
is
begin
insert into ap_common_err_tab
values
(
p_interface_name,
p_table_name,
p_column_name,
p_error_message,
fnd_profile.value('USER_ID'),
SYSDATE,
fnd_profile.value('USER_ID'),
SYSDATE,
fnd_profile.value('CONC_REQUEST_ID')
);
Total Package With Validation:-
CREATE OR REPLACE package body APPS.ee_ap_inv_pkg
is
--for declaring global variables
g_user_id number(10):=fnd_profile.value('USER_ID');
g_date date:=sysdate;
procedure ap_record_details
is
cursor c1 is select * from ap_inv_head
where process_flag='S';
cursor c2 is select * from ap_inv_head
where process_flag='E';
v_tot_rec number;
v_sus_rec number;
v_err_rec number;
begin
--for total records
select count(*) into v_tot_rec
from ap_inv_head;
-- for success records
select count(*) into v_sus_rec
from ap_inv_head
where process_flag='S';
--for error records
select count(*) into v_err_rec
from ap_inv_head
where process_flag='E';
for info in c1
loop
fnd_file.put_line(fnd_file.output,'---------------------------');
fnd_file.put_line(fnd_file.output,'FOR SUSCESS INFORMATION');
fnd_file.put_line(fnd_file.output,'---------------------------');
fnd_file.put_line(fnd_file.output,'INVOICE NUM =>'||info.invoice_num);
fnd_file.put_line(fnd_file.output,'VENDOR NAME =>'||info.vendor_name);
fnd_file.put_line(fnd_file.output,'TOTAL RECORDS =>'||v_tot_rec);
fnd_file.put_line(fnd_file.output,'TOTAL SUCESS RECORDS =>'||v_sus_rec);
fnd_file.put_line(fnd_file.output,'TOTAL ERROR RECORDS =>'||v_err_rec);
end loop;
for info1 in c2
loop
fnd_file.put_line(fnd_file.output,'---------------------------');
fnd_file.put_line(fnd_file.output,'FOR ERROR INFORMATION');
fnd_file.put_line(fnd_file.output,'---------------------------');
fnd_file.put_line(fnd_file.output,'INVOICE NUM =>'||info1.invoice_num);
fnd_file.put_line(fnd_file.output,'VENDOR NAME =>'||info1.vendor_name);
fnd_file.put_line(fnd_file.output,'TOTAL RECORDS =>'||v_tot_rec);
fnd_file.put_line(fnd_file.output,'TOTAL SUCESS RECORDS =>'||v_sus_rec);
fnd_file.put_line(fnd_file.output,'TOTAL ERROR RECORDS =>'||v_err_rec);
fnd_file.put_line(fnd_file.output,'ERROR MESSAGE =>'||info1.error_message);
end loop;
end ap_record_details;
---arch table information
procedure arch_proc
is
begin
insert into ap_inv_head_arch select * from ap_inv_head;
insert into ap_inv_line_arch select * from ap_inv_line;
delete from ap_inv_head;
delete from ap_inv_line;
commit;
end;
procedure dis_log(p_msg in varchar2)
is
begin
fnd_file.put_line(fnd_file.log,p_msg);
end dis_log;
procedure main(errbuf out varchar2,
retcode out varchar2
)
is
cursor head is select * from ap_inv_head;
cursor line(p_inv_num in varchar2) is select * from ap_inv_line
where invoice_num=p_inv_num;
v_head ap_invoices_interface%rowtype;
v_line ap_invoice_lines_interface%rowtype;
v_head_tab varchar2(100):='AP_INV_HEAD';
v_line_tab varchar2(100):='AP_INV_LINE';
v_conc_program varchar2(100);
v_process_flag varchar2(1);
v_error_message varchar2(100);
v_tot_err_msg varchar2(1000);
v_count number;
v_inv_num_exists exception;
v_amount number;
begin
---concurrent program------
select fcp.user_concurrent_program_name
into v_conc_program
from fnd_concurrent_programs_vl fcp,
fnd_concurrent_requests fcr
where fcp.concurrent_program_id=fcr.concurrent_program_id
and fcr.request_id=g_request_id;
---end of concurrent program------
for rec_head in head
loop
begin
v_process_flag:='S';
v_error_message:=null;
v_tot_err_msg:=null;
--*****invoice type lookyp code****--
begin
select lookup_code into v_head.invoice_type_lookup_code
from ap_lookup_codes
where lookup_type='INVOICE TYPE'
and displayed_field=rec_head.invoice_type_lookup_code;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID INVOICE TYPE LOOKUP CODE =>'||rec_head.invoice_type_lookup_code;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'INVOICE TYPE LOOKYP CODE',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT INVOICE TYPE LOOKUP CODE VALIDATION =>'||rec_head.invoice_type_lookup_code||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'INVOICE TYPE LOOKYP CODE',
p_error_message => v_error_message
);
end;
--*****vendor name validation *****---
begin
select vendor_id into v_head.vendor_id
from po_vendors
where vendor_name=rec_head.vendor_name;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID VENDOR NAME =>'||rec_head.vendor_name;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'VENDOR_NAME',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT VENDOR_NAME VALIDATION =>'||rec_head.vendor_name||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'VENDOR_NAME',
p_error_message => v_error_message
);
end;
---*****organiozation name validation ********---
begin
select organization_id into v_head.org_id
from org_organization_definitions
where organization_name=rec_head.ou_name;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID ORG NAME =>'||rec_head.ou_name;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'ORGANIZATION NAME',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT ORG_NAME VALIDATION =>'||rec_head.ou_name||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'ORGANIZATION NAME',
p_error_message => v_error_message
);
end;
---*****vendor site code validation *******------
begin
select vendor_site_id into v_head.vendor_site_id
from po_vendor_sites_all
where vendor_site_code=rec_head.vendor_site_code
and vendor_id=v_head.vendor_id
and org_id=v_head.org_id;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID VENDOR SITE CODE =>'||rec_head.vendor_site_code;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'VENDOR_SITE_CODE',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT ORG_NAME VALIDATION =>'||rec_head.vendor_site_code||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'VENDOR_SITE_CODE',
p_error_message => v_error_message
);
end;
--*******invoice num validation****---
select count(1) into v_count
from ap_invoices_all
where invoice_num=rec_head.invoice_num
and vendor_id=v_head.vendor_id
and org_id=v_head.org_id;
if v_count > 0
then
raise v_inv_num_exists;
end if;
-----****source validation****-----
begin
select lookup_code into v_head.source
from ap_lookup_codes
where lookup_type='SOURCE'
and displayed_field=rec_head.source;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID SOURCE =>'||rec_head.source;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'SOURCE',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT SOURCE VALIDATION =>'||rec_head.source||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'SOURCE',
p_error_message => v_error_message
);
end;
---***terms validation ***----
begin
select term_id into v_head.terms_id
from ap_terms
where name=rec_head.terms_name;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID TERMS NAME =>'||rec_head.terms_name;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'TERMS_NAME',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT TERMS NAME VALIDATION =>'||rec_head.terms_name||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'TERMS_NAME',
p_error_message => v_error_message
);
end;
---**** invoice amount validation ****----
begin
select sum(amount) into v_amount
from ap_inv_line
where invoice_num=rec_head.invoice_num;
if rec_head.invoice_amount <> v_amount then
v_process_flag:='E';
v_error_message:='INVOICE AMOUNT DOES NOT MATCH YOUR LINE AMOUNT =>'||rec_head.invoice_amount;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'INVOICE AMOUNT',
p_error_message => v_error_message
);
end if;
end;
select ap_invoices_interface_s.nextval into v_head.invoice_id from dual;
v_head.invoice_num := rec_head.invoice_num;
-- v_head.invoice_type_lookup_code := rec_head.invoice_type_lookup_code;
v_head.vendor_name := rec_head.vendor_name;
v_head.vendor_site_code := rec_head.vendor_site_code;
v_head.invoice_amount := rec_head.invoice_amount;
v_head.terms_name := rec_head.terms_name;
v_head.created_by := g_user_id;
v_head.creation_date := g_date;
v_head.last_updated_by := g_user_id;
v_head.last_update_date := g_date;
for rec_line in line(rec_head.invoice_num)
loop
---*** line type validation ***----
begin
select lookup_code into v_line.line_type_lookup_code
from ap_lookup_codes
where lookup_type ='INVOICE DISTRIBUTION TYPE'
and displayed_field=rec_line.line_type_lookup_code;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID LINE TYPE LOOKUP CODE =>'||rec_line.line_type_lookup_code;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_line_tab ,
p_column_name => 'LINE TYPE LOOKUP CODE',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT LINE TYPE LOOKUP CODE VALIDATION =>'||rec_line.line_type_lookup_code||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_line_tab ,
p_column_name => 'LINE TYPE LOOKUP CODE',
p_error_message => v_error_message
);
end;
---*** charge account valiation ***------
begin
select gcc.code_combination_id into v_line.dist_code_combination_id
from gl_code_combinations_kfv gcc,
gl_sets_of_books gsb
where gcc.chart_of_accounts_id=gsb.chart_of_accounts_id
and gsb.set_of_books_id=fnd_profile.value('GL_SET_OF_BKS_ID')
and gcc.concatenated_segments=rec_line.dist_code_concatenated;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='INVALID CHARGE ACCOUNT =>'||rec_line.dist_code_concatenated;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_line_tab ,
p_column_name => 'CHARGE ACCOUNT',
p_error_message => v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='EXCEPTION AT CHARGE ACCOUNT VALIDATION =>'||rec_line.dist_code_concatenated||' '||sqlerrm;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_line_tab ,
p_column_name => 'CHARGE ACCOUNT',
p_error_message => v_error_message
);
end;
select ap_invoice_lines_interface_s.nextval into v_line.invoice_line_id from dual;
v_line.invoice_id := v_head.invoice_id;
v_line.line_number := rec_line.line_number;
--v_line.line_type_lookup_code := rec_line.line_type_lookup_code;
v_line.amount := rec_line.amount;
v_line.dist_code_concatenated := rec_line.dist_code_concatenated;
v_line.created_by := g_user_id;
v_line.creation_date := g_date;
v_line.last_updated_by := g_user_id;
v_line.last_update_date := g_date;
v_line.org_id := v_head.org_id;
dis_log('PROCESS_FLAG =>'||v_process_flag);
if v_process_flag = 'S' then
insert into ap_invoice_lines_interface values v_line;
end if;
end loop;
dis_log('PROCESS_FLAG =>'||v_process_flag);
if v_process_flag = 'S' then
insert into ap_invoices_interface values v_head;
end if;
update ap_inv_head set process_flag=v_process_flag,
error_message=v_tot_err_msg
where invoice_num=rec_head.invoice_num;
exception
when v_inv_num_exists then
v_process_flag:='E';
dis_log('PROCESS_FLAG =>'||v_process_flag);
v_error_message:='INVOICE NUM ALREADY EXISTS =>'||rec_head.invoice_num;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ee_ap_comm_err_procedure(p_interface_name => v_conc_program,
p_table_name => v_head_tab,
p_column_name => 'INVOICE NUM',
p_error_message => v_error_message
);
update ap_inv_head set process_flag=v_process_flag,
error_message=v_tot_err_msg
where invoice_num=rec_head.invoice_num;
end;
end loop;
ap_record_details; --for records information
arch_proc ; --for arch tables information
exception
when others then
dis_log('EXCEPTION AT PACKAGE =>'||sqlerrm);
end main;
end ee_ap_inv_pkg;
/
No comments:
Post a Comment