INVOICE OPEN INTERFACE (VALIDATIONS)

INVOICE VALIDATION _1
====================
create or replace procedure xx_ap_inv_proc(errbuf out varchar2,retcode out varchar2) is
ln_rec_cnt number;
v_vendor_id number;
v_vendor_site_id number;
v_organization_id number;
l_error_flag varchar2(400);
l_error_details varchar2(400);
l_success_cnt number;
l_failure_cnt number;
l_user_id number:=fnd_global.user_id;
l_resp_id number:= fnd_global.resp_id;

l_appl_id number:= fnd_global.resp_appl_id;

cursor c1 is select * from xx_ap_invoices;
cursor c2 is select * from xx_ap_invoice_lines;
begin
delete from ap_invoices_interface;
delete from ap_invoice_lines_interface;
commit;
fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);
for i in c1 loop
--for i1 in c2 loop
ln_rec_cnt:= ln_rec_cnt+1;
l_error_flag:=null;
l_error_details:=null;
begin
select count(*) into v_vendor_id
from po_vendors pv
where vendor_id=i.vendor_id;
exception when others then
dbms_output.put_line('Vendor id is invalid');
if v_vendor_id=0 then
l_error_flag:='Y';
l_error_details:= l_error_details||'/'||'Vendor Error'||'/'||sqlerrm;
end if;
end;
begin
select count(*) into v_vendor_site_id
from po_vendor_sites_all
where vendor_site_id=i.vendor_site_id;
exception when others then
dbms_output.put_line('Vendor site id is invalid');
if v_vendor_site_id=0 then
l_error_flag:='Y';
l_error_details:=l_error_details||'/'||'Vendor Site Error'||'/'||sqlerrm;
end if;
end;
if(l_error_flag='Y') then
l_failure_cnt:= l_failure_c
nt+1;
else
INSERT INTO ap_invoices_interface
(invoice_id, invoice_num, invoice_type_lookup_code,
invoice_date, vendor_id, vendor_name, vendor_num,
vendor_site_id, invoice_amount,
invoice_currency_code, payment_method_lookup_code,
payment_currency_code, terms_id, SOURCE, org_id,
gl_date, terms_date
)
VALUES (AP_INVOICES_INTERFACE_S.NEXTVAL,
i.invoice_num, i.invoice_type_lookup_code,
i.invoice_date, i.vendor_id, i.vendor_name, i.vendor_num,
i.vendor_site_id, i.invoice_amount,
i.invoice_currency_code, i.payment_method_lookup_code,
i.payment_currency_code, i.terms_id, i.SOURCE, i.org_id,
i.gl_date, i.terms_date
);
end if;
end loop;
commit;
for i1 in c2 loop
begin
select count(*) into v_organization_id
from org_organization_definitions
where organization_id=i1.org_id;
dbms_output.put_line(i1.org_id);
exception when others then
dbms_output.put_line('Org id is invalid');
if v_organization_id=0 then
l_error_flag:='Y';
l_error_details:=l_error_details||'/'||'Organization Error'||'/'||sqlerrm;
end if;
end;
if(l_error_flag='Y') then
l_failure_cnt:=l_failure_cnt+1;
else
INSERT INTO ap_invoice_lines_interface


(invoice_id, line_number, line_type_lookup_code,


amount, unit_of_meas_lookup_code, unit_price,


dist_code_combination_id, invoice_line_id,description,quantity_invoiced,org_id


)


VALUES (AP_INVOICES_INTERFACE_S.NEXTVAL-10,


i1.line_number, i1.line_type_lookup_code,


i1.amount, i1.unit_of_meas_lookup_code, i1.unit_price,


i1.dist_code_combination_id, i1.invoice_line_id,i1.description,i1.quantity,i1.org_id


);
end if;
end loop;
commit;
end;


INVOICE VALIDATION _2


======================
CREATE OR REPLACE PROCEDURE xx_ap_inv_proc(errbuf out varchar2,retcode out varchar2
IS
ven_name varchar2(15);
ven_site varchar2(15);
ven_org_id varchar2(15);
CURSOR data_load
IS
SELECT *
FROM xx_ap_invoices;
CURSOR data_load1
IS
SELECT *
FROM xx_ap_invoice_lines;
BEGIN
DELETE FROM ap_invoices_interface;
DELETE FROM ap_invoice_lines_interface
COMMIT;
FOR i IN data_load
LOOP
BEGI
SELECT vendor_name into ven_name FROM po_vendors where vendor_id=i.vendor_id;
dbms_output.put_line('Vendor_name'|| ven_name);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Vendor_Id is Invalid');
END;
BEGIN
SELECT vendor_site_id into ven_site FROM AP_SUPPLIER_SITES_ALL
where vendor_site_id=i.vendor_site_id
dbms_output.put_line('vendor_site_id'|| ven_site );
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('vendor_site_id is Invalid');
END;
INSERT INTO ap_invoices_interface
(invoice_id, invoice_num, invoice_type_lookup_code,
invoice_date, vendor_id, vendor_name, vendor_num,
vendor_site_id, invoice_amount,
invoice_currency_code, payment_method_lookup_code,
payment_currency_code, terms_id, SOURCE, org_id,
gl_date, terms_date
)
VALUES (i.invoice_id, i.invoice_num, i.invoice_type_lookup_code,
i.invoice_date, i.vendor_id, i.vendor_name, i.vendor_num,
i.vendor_site_id, i.invoice_amount,
i.invoice_currency_code, i.payment_method_lookup_code,
i.payment_currency_code, i.terms_id, i.SOURCE, i.org_id,
i.gl_date, i.terms_dat
);
END LOOP;
FOR i1 IN data_load1
LOOP
BEGIN
select ORGANIZATION_ID into ven_org_id from org_organization_definitions
where ORGANIZATION_ID = i1.org_id;
dbms_output.put_line ('ORGANIZATION_ID' || org_id );
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('org_id is Invalid');
END;
INSERT INTO ap_invoice_lines_interface
(invoice_id, line_number, line_type_lookup_code,
amount, unit_of_meas_lookup_code, unit_price,
dist_code_combination_id, invoice_line_id,description,quantity_invoiced,org_id
)
VALUES (i1.invoice_id, i1.line_number, i1.line_type_lookup_code,
i1.amount, i1.unit_of_meas_lookup_code, i1.unit_price,
i1.dist_code_combination_id, i1.invoice_line_id,i1.description,i1.quantity,i1.org_id
);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
--FND_FILE.PUT_LINE(FND_FILE.LOG,'Records not loaded into the interface table');
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END;