Pre-Requisites for Po Interface:
- Suppliers,Sites and contact details should be defined.
- Organization Structure Should Be defined.
- Ship_to and bill_to locations should be defined.
- Set of books, items, item categories, UOM(Unit of measures) Defined.
- Employee Creation and Buyer Setup should be done.
Interface Tables:
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE
Base Tables:
PO_HEADERS_ALL PO_LINES_ALL PO_LINE_LOCATIONS_ALL PO_DISTRIBUTIONS_ALL
Interface Program:
Import Standard Interface Program
Parameters to be passed during the program:
Default = NULL
Create or Updated Items = No
PO Status = Appproved(Approved/Incomplete/Initial Approved)
Batch_id = We can get from interface header table
Error Table:
PO_INTERFACE_ERRORS
Main Columns in PO_HEADERS_INTERFACE:
VENDOR_ID VENDOR_SITE_ID VENDOR_CONTACT_ID
AGENT_NAME ORG_ID DOCUMENT_TYPE_CODE
CREATION_DATE CURRENCY_CODE SHIP_TO_LOCATION
BILL_TO_LOCATION
Main Columns in PO_LINES_INTERFACE:
LINE_NUM LINE_TYPE ITEM_ID ITEM
ITEM_DESCRIPTION CATEGORY_ID QUNATITY UNIT_OF_MEASURE
UNIT_PRICE NEED_BY_DATE PROMISE_DATE
SHIP_TO_ORGANIZATION_ID ORG_ID SHIP_TO_LOCATION_IDTables To Be Used For Validation: PO_VENDORS PO_VENDOR_SITES_ALL PO_VENDOR_CONTACTS PER_ALL_PEOPLE_F
HR_OPERATING_UNITS FND_CURRENCIES HR_LOCATIONS
PO_LINE_TYPES MTL_SYSTEM_ITEMS_B MTL_CATEGORIES MTL_UNITS_OF_MEASURE ORG_ORGANIZATION_DEFINITIONS
Control file for Headers:
options(skip=2)
load data
infile '/apps/aptest/visappl/xxcus/11.5.0/bin/po_head.csv'
truncate into table po_headers_temp
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
INTERFACE_SOURCE_CODE ,
PROCESS_CODE ,
ACTION ,
DOCUMENT_TYPE_CODE ,
DOCUMENT_SUBTYPE ,
DOCUMENT_NUM ,
AGENT_NAME ,
VENDOR_NAME ,
VENDOR_SITE_CODE "REPLACE(:VENDOR_SITE_CODE,CHR(13),'')"
)
For Lines:-
options(skip=2)
load data
infile '/apps/aptest/visappl/xxcus/11.5.0/bin/po_line.csv'
truncate into table po_lines_temp
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
ACTION ,
LINE_NUM,
SHIPMENT_NUM ,
SHIPMENT_TYPE ,
DOCUMENT_NUM ,
LINE_TYPE ,
ITEM ,
CATEGORY ,
ITEM_DESCRIPTION,
QUANTITY ,
UNIT_PRICE ,
SHIP_TO_ORGANIZATION_CODE ,
NEED_BY_DATE
)
For Distributions:-
options(skip=1)
load data
infile '/apps/aptest/visappl/xxcus/11.5.0/bin/po_dist.csv'
truncate into table po_distributions_temp
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
QUANTITY_ORDERED ,
QUANTITY_DELIVERED ,
QUANTITY_BILLED ,
QUANTITY_CANCELLED ,
DISTRIBUTION_NUM ,
LINE_NUM ,
SHIPMENT_NUM ,
DOCUMENT_NUM ,
DESTINATION_TYPE,
DESTINATION_ORGANIZATION ,
CHARGE_ACCOUNT "REPLACE(:CHARGE_ACCOUNT,CHR(13),'')"
)
Common Error Table creation and Error Procedure:
create table po.all_common_err_tab
(interface_name varchar2(100),
table_name varchar2(100),
column_name varchar2(100),
error_message varchar2(1000),
created_by varchar2(100),
creation_date date,
last_updated_by varchar2(100),
last_update_date date,
request_id number(10)
);
create synonym all_common_err_tab for po.all_common_err_tab
create or replace procedure ALL_COMMON_ERROR_PROC
(
p_interface_name varchar2,
p_table_name varchar2,
p_column_name varchar2,
p_error_message varchar2
)
is
begin
insert into all_common_err_tab
values
(
p_interface_name,
p_table_name,
p_column_name,
p_error_message,
sysdate,
fnd_profile.value('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
FND_PROFILE.VALUE('CONC_REQUEST_ID')
);
Total Package Body for Po Interface With Validations:
is
g_request_id number:=fnd_profile.value('CONC_REQUEST_ID');
g_org_id number:=fnd_profile.value('ORG_ID');
procedure dis_log(p_msg in varchar2)
is
begin
fnd_file.put_line(fnd_file.log,p_msg);
end;
procedure details is
v_tot_rec number(10);
v_succ_rec number(10);
v_err_rec number(10);
begin
select count(*) into v_succ_rec from po_headers_temp where process_flag='S';
select count(*) into v_err_rec from po_headers_temp where process_flag='E';
v_tot_rec:=v_succ_rec+v_err_rec;
fnd_file.put_line(fnd_file.OUTPUT,'total records:'||v_tot_rec);
fnd_file.put_line(fnd_file.OUTPUT,'total sucess record:'||v_succ_rec);
fnd_file.put_line(fnd_file.OUTPUT,'total error records:'||v_err_rec);
end;
procedure arch is
begin
insert into po_headers_arch select *from po_headers_temp;
insert into po_lines_arch select *from po_lines_temp;
insert into po_distributions_arch select *from po_distributions_temp;
delete from po_headers_temp;
delete from po_lines_temp;
delete from po_distributions_temp;
commit;
end;
procedure main(x_errbuf out varchar2,x_retcode out varchar2)
is
cursor cur_hea is select * from po_headers_temp;
cursor cur_lin(p_doc_num varchar2) is
select *from po_lines_temp
where document_num=p_doc_num;
cursor cur_dis(p_doc_num varchar2,p_line_num varchar2,p_shipment_num varchar2)
is select *from po_distributions_temp
where document_num=p_doc_num
and line_num=p_line_num
and shipment_num=p_shipment_num;
v_rec_hea po_headers_interface%rowtype;
v_rec_lin po_lines_interface%rowtype;
v_rec_dis po_distributions_interface%rowtype;
v_process_flag varchar2(10);
v_error_message varchar2(100);
v_tot_err_msg varchar2(1000);
v_head varchar2(100):='po_headers_temp';
v_line varchar2(100):='po_lines_temp';
v_dist varchar2(100):='po_distributions_temp';
v_conc_name varchar2(100);
v_count number(10);
po_num_exists exception;
qty_null exception;
begin
dis_log('before entering into header loop1');
select fcp.USER_CONCURRENT_PROGRAM_NAME INTO v_conc_name
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;
dis_log('before entering into header loop');
for rec_hea in cur_hea loop
begin
v_process_flag:='S';
v_error_message:=null;
v_tot_err_msg:=null;
dis_log('after entering into header loop');
select PO_HEADERS_INTERFACE_S.nextval into v_rec_hea.INTERFACE_HEADER_ID from dual;
--document type code validation
begin
select plc.LOOKUP_CODE into v_rec_hea.DOCUMENT_TYPE_CODE
from po_lookup_codes plc,
po_lookup_codes plt
where plc.lookup_type='PO TYPE'
and plt.lookup_type='BB8 PO TYPE MAPPING'
and plc.DISPLAYED_FIELD=plt.DESCRIPTION
and plt.DISPLAYED_FIELD=rec_hea.DOCUMENT_TYPE_CODE;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='no such document type exists with:'||rec_hea.DOCUMENT_TYPE_CODE;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_head,
p_column_name=>'DOCUMENT TYPE CODE',
p_error_message=>v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='exception at document type code:'||rec_hea.DOCUMENT_TYPE_CODE;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_head,
p_column_name=>'DOCUMENT TYPE CODE',
p_error_message=>v_error_message
);
end;
--vendor name validation
begin
select vendor_id into v_rec_hea.vendor_id
from po_vendors
where vendor_name=rec_hea.vendor_name;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='no such vendor name exists with:'||rec_hea.vendor_name;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_head,
p_column_name=>'vendor name',
p_error_message=>v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='exception at document type code:'||rec_hea.DOCUMENT_TYPE_CODE;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>'h',--v_conc_name,
p_table_name=>v_head,
p_column_name=>'vendor name',
p_error_message=>v_error_message
);
end;
--vendor site validation
begin
select vendor_site_id into v_rec_hea.vendor_site_id
from po_vendor_sites_all
where vendor_site_code=rec_hea.vendor_site_code
and vendor_id=v_rec_hea.vendor_id
and org_id=g_org_id;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='no such vendor site exists with:'||rec_hea.vendor_site_code;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_head,
p_column_name=>'vendor site code',
p_error_message=>v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='exception at document type code:'||rec_hea.vendor_site_code;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_head,
p_column_name=>'vendor site code',
p_error_message=>v_error_message
);
end;
--po number validation
select count(1) into v_count
from po_headers_all
where segment1=rec_hea.document_num
and type_lookup_code=v_rec_hea.DOCUMENT_TYPE_CODE
and org_id=g_org_id;
if v_count>0 then
raise po_num_exists;
end if;
--agent name validation
begin
select poa.AGENT_ID into v_rec_hea.AGENT_ID
from po_agents poa,
per_all_people_f papf
where poa.AGENT_ID=papf.PERSON_ID
and papf.FULL_NAME=rec_hea.AGENT_NAME
and sysdate between papf.effective_start_date and papf.effective_end_date;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='no such agent exists with:'||rec_hea.AGENT_NAME;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_head,
p_column_name=>'agent',
p_error_message=>v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='exception at agent name:'||rec_hea.AGENT_NAME;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_head,
p_column_name=>'agent name',
p_error_message=>v_error_message
);
end;
v_rec_hea.INTERFACE_SOURCE_CODE :=rec_hea.INTERFACE_SOURCE_CODE;
v_rec_hea.PROCESS_CODE :=rec_hea.PROCESS_CODE ;
v_rec_hea.ACTION :=rec_hea.ACTION ;
--v_rec_hea.DOCUMENT_TYPE_CODE :=rec_hea.DOCUMENT_TYPE_CODE;
v_rec_hea.DOCUMENT_SUBTYPE :=rec_hea.DOCUMENT_SUBTYPE;
v_rec_hea.DOCUMENT_NUM :=rec_hea.DOCUMENT_NUM ;
--v_rec_hea.AGENT_NAME :=rec_hea.AGENT_NAME ;
--v_rec_hea.VENDOR_NAME :=rec_hea.VENDOR_NAME ;
--v_rec_hea.VENDOR_SITE_CODE :=rec_hea.VENDOR_SITE_CODE;
dis_log('before entering into line loop');
for rec_lin in cur_lin(rec_hea.document_num) loop
dis_log('after entering into line loop');
select PO_LINES_INTERFACE_S.nextval into v_rec_lin.INTERFACE_LINE_ID from dual;
--line type validation
begin
select line_type_id into v_rec_lin.LINE_TYPE_ID
from po_line_types
where line_type=rec_lin.line_type;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='no such line type exists with:'||rec_lin.line_type;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_line,
p_column_name=>'line type',
p_error_message=>v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='exception at agent name:'||rec_lin.line_type;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_line,
p_column_name=>'line type',
p_error_message=>v_error_message
);
end;
--SHIP_TO_ORGANIZATION_CODE validation
begin
select organization_id into v_rec_lin.ship_to_organization_id
from org_organization_definitions
where ORGANIZATION_CODE=rec_lin.SHIP_TO_ORGANIZATION_CODE;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='no such ship to organization code exists with:'||rec_lin.SHIP_TO_ORGANIZATION_CODE;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_line,
p_column_name=>'SHIP_TO_ORGANIZATION_CODE',
p_error_message=>v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='exception at agent name:'||rec_lin.SHIP_TO_ORGANIZATION_CODE;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_line,
p_column_name=>'SHIP_TO_ORGANIZATION_CODE',
p_error_message=>v_error_message
);
end;
--item,category,uom,description
begin
select msi.inventory_item_id
,mic.category_id
,msi.primary_uom_code
,msi.description
into v_rec_lin.item_id
,v_rec_lin.category_id
,v_rec_lin.uom_code
,v_rec_lin.item_description
from mtl_system_items_b msi,
mtl_item_categories mic,
mtl_category_sets mcs
where msi.organization_id = v_rec_lin.ship_to_organization_id
and msi.segment1 = rec_lin.item
and mic.inventory_item_id = msi.inventory_item_id
and mic.organization_id = msi.organization_id
and mic.category_set_id = mcs.category_set_id
and mcs.CATEGORY_SET_NAME = 'Purchasing';
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='no such item or ctgry or uom exists with:'||rec_lin.item;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_line,
p_column_name=>'item',
p_error_message=>v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='exception at item or ctgry or uom:'||rec_lin.item;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_line,
p_column_name=>'item',
p_error_message=>v_error_message
);
end;
--quantity or unit price validation
begin
if nvl(rec_lin.QUANTITY,0) = 0 or rec_lin.UNIT_PRICE is null
or rec_lin.NEED_BY_DATE is null
then
v_process_flag := 'E';
v_error_message := 'Qty or unit price or need by date is null';
v_tot_err_msg := v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_line,
p_column_name=>'qty or price',
p_error_message=>v_error_message
);
dis_log(v_error_message);
end if;
end;
v_rec_lin.INTERFACE_HEADER_ID:=v_rec_hea.INTERFACE_HEADER_ID;
v_rec_lin.ACTION :=rec_lin.ACTION;
v_rec_lin.LINE_NUM :=rec_lin.LINE_NUM;
v_rec_lin.SHIPMENT_NUM :=rec_lin.SHIPMENT_NUM;
v_rec_lin.SHIPMENT_TYPE :=rec_lin.SHIPMENT_TYPE;
v_rec_lin.DOCUMENT_NUM :=rec_lin.DOCUMENT_NUM;
--v_rec_lin.LINE_TYPE :=rec_lin.LINE_TYPE;
--v_rec_lin.SHIP_TO_ORGANIZATION_CODE:=rec_lin.SHIP_TO_ORGANIZATION_CODE;
--v_rec_lin.ITEM :=rec_lin.ITEM;
--v_rec_lin.CATEGORY :=rec_lin.CATEGORY;
--v_rec_lin.ITEM_DESCRIPTION:=rec_lin.ITEM_DESCRIPTION;
-- v_rec_lin.QUANTITY :=rec_lin.QUANTITY ;
-- v_rec_lin.UNIT_PRICE :=rec_lin.UNIT_PRICE;
--v_rec_lin.NEED_BY_DATE :=rec_lin.need_by_date;
dis_log('before entering into distribution loop');
for rec_dis in cur_dis(p_doc_num=>rec_lin.document_num,
p_line_num=>rec_lin.line_num,
p_shipment_num=>rec_lin.shipment_num
)
loop
dis_log('after entering into distribution loop');
select PO_DISTRIBUTIONS_INTERFACE_S.nextval into v_rec_dis.INTERFACE_DISTRIBUTION_ID
from dual;
--destination type validation
begin
select lookup_code INTO v_rec_dis.DESTINATION_TYPE_CODE
from po_lookup_codes
where lookup_type='DESTINATION TYPE'
and displayed_field=rec_dis.DESTINATION_TYPE;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='no such destination type exists with:'||rec_dis.DESTINATION_TYPE;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_line,
p_column_name=>'destination type',
p_error_message=>v_error_message
);
when others then
v_process_flag:='E';
v_error_message:='exception at destination type validation:'||rec_dis.DESTINATION_TYPE;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_dist,
p_column_name=>'destination type',
p_error_message=>v_error_message
);
end;
--qty ordered valiidation
begin
if rec_dis.QUANTITY_ORDERED<=0 THEN
V_PROCESS_FLAG:='E';
V_ERROR_MESSAGE:='QUANTITY ORDRED IS NULL:'||rec_dis.QUANTITY_ORDERED;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_dist,
p_column_name=>'QTY ORDERED',
p_error_message=>v_error_message
);
end if;
end;
--CHARGE ACCOUNT VALIDATION
begin
select gcc.CODE_COMBINATION_ID INTO v_rec_dis.CHARGE_ACCOUNT_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_dis.CHARGE_ACCOUNT;
exception
when no_data_found then
v_process_flag:='E';
v_error_message:='no such charge account with:'||rec_dis.CHARGE_ACCOUNT;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_line,
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_dis.CHARGE_ACCOUNT;
v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;
ALL_COMMON_ERROR_PROC(p_interface_name=>v_conc_name,
p_table_name=>v_dist,
p_column_name=>'charge account',
p_error_message=>v_error_message
);
end;
v_rec_dis.INTERFACE_HEADER_ID :=v_rec_lin.INTERFACE_HEADER_ID;
v_rec_dis.INTERFACE_LINE_ID :=v_rec_lin.INTERFACE_LINE_ID;
v_rec_dis.QUANTITY_ORDERED :=rec_dis.QUANTITY_ORDERED;
v_rec_dis.QUANTITY_DELIVERED :=rec_dis.QUANTITY_DELIVERED;
v_rec_dis.QUANTITY_BILLED :=rec_dis.QUANTITY_BILLED;
v_rec_dis.QUANTITY_CANCELLED :=rec_dis.QUANTITY_CANCELLED;
v_rec_dis.DISTRIBUTION_NUM :=rec_dis.DISTRIBUTION_NUM;
--v_rec_dis.DESTINATION_TYPE :=rec_dis.DESTINATION_TYPE ;
v_rec_dis.DESTINATION_ORGANIZATION :=rec_dis.DESTINATION_ORGANIZATION;
--v_rec_dis.CHARGE_ACCOUNT :=rec_dis.CHARGE_ACCOUNT;
if v_process_flag='S' then
insert into po_distributions_interface values v_rec_dis;
end if;
end loop;
dis_log('coming out from distribution loop');
if v_process_flag='S' then
insert into po_lines_interface values v_rec_lin;
end if;
end loop;
dis_log('coming out from line loop');
if v_process_flag='S' then
insert into po_headers_interface values v_rec_hea;
end if;
update po_headers_temp set process_flag=v_process_flag,
error_message=v_tot_err_msg
where document_num=rec_hea.document_num;
exception
when po_num_exists then
v_process_flag:='E';
v_error_message:='po number already exists with:'||rec_hea.document_num;
update po_headers_temp set process_flag=v_process_flag,
error_message=v_tot_err_msg
where document_num=rec_hea.document_num;
fnd_file.put_line(fnd_file.log,v_tot_err_msg);
dis_log('after po exception');
end;
end loop;
details;
arch;
dis_log('coming out from header loop');
exception
when others then
dis_log('exception in main loop');
end main;
end po_int;
Hi,
ReplyDeleteIs this code same for PO Quotation Conversions also??
Interface Program:
ReplyDeleteImport Standard Interface Program
Its not a right program for po interface plzz correct
Correct Program is :
Interface Program:
Import Standard purchase order