--Supplier Invoice Code
CREATE OR REPLACE PROCEDURE
SUPP_AP_INVOICES_PKG(
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2)
AS
CURSOR c1
IS
SELECT
org_id,
invoice_type_lookup_code,
vendor_num, vendor_name,
vendor_site_code,
invoice_num,
invoice_date,
invoice_currency_code,
invoice_amount,
gl_date,
payment_currency_code,
terms_date,
terms_name,
payment_method_lookup_code,
description,
exchange_rate,
exchange_rate_type,
exchange_date,
doc_category_code,
voucher_num,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
SOURCE
FROM SUPP_INVOICE_HEADER_STG;
cursor c2(p_invoice_num IN VARCHAR2)
IS
SELECT
a.org_id,
a.vendor_num,
a.invoice_num,
a.po_number,
a.line_number,
a.line_type,
a.amount,
a.gl_date,
a.description,
a.gl_account_code,
a.ROWID row_id
FROM supp_invoice_lins_stg a
WHERE invoice_num = p_invoice_num;
l_status varchar2(4) default 'A';
l_error_message varchar2(4000);
l_org_id number(6);
l_invoice_type_lookup_code varchar2(100);
l_vendor_num number(15);
l_vendor_site_code varchar2(200);
l_invoice_num number(15);
l_invoice_date date;
l_invoice_currency_code varchar2(100);
l_invoice-amount number(15);
l_gl_date date;
l_source varchar2(150);
l_line_number number(15);
l_line_type varchar2(150);
l_amount number(15);
l_gl_account_code varchar2(150);
l_vendor_id number;
l_lookup_code varchar2(25);
l_line_status varchar2(1);
l_one_rec_failed varchar2(1);
l_gl_ccid number;
l_invoice_id number;
l_invoice_line_id number;
begin
l_error_message:=NULL;
FOR X1 IN C1
LOOP
l_error_message:=NULL;
--====org_id validations====
begin
select organization_id
into l_org_id
from hr_operating_units
where organization_id = x1.org_id;
exception
when others then
l_status := 'E';
l_error_message := 'org id is not in system';
fnd_file.put_line(fnd_file.log, 'Error
occured ' || l_error_message);
end;
------- invoice_type_lookup_code validation -------
begin
select lookup_code
into l_invoice_type_lookup_code
from fnd_lookup_values
where lookup_type = 'INVOICE TYPE'
and upper(lookup_code) = upper(x1.invoice_type_lookup_code)
and language = userenv('LANG');
EXCEPTION
when others then
l_status := 'E';
l_error_message :=
l_error_message ||
' invoice type lookup code is not in system';
fnd_file.put_line(fnd_file.log, 'error
occured ' || l_error_message);
end;
---------- vendor number validation -----------------------
begin
select segment1, vendor_id
into l_vendor_num, l_vendor_id
from po_vendors
where segment1 = x1.vendor_num;
exception
when other then
l_status := 'E';
l_error_message :=
l_error_message || ' Vendor number is not in
system';
fnd_file.put_line(fnd_file.log, 'error
occured ' || l_error_message);
end;
--------------- vendor site code validation --------------
begin
select vendor_site_code
into l_vendor_site_code
from po_vendor_sites_all
where vendor_site_code =
x1.vendor_site_code;
and vendor_id = l_vendor_id;
exception
when others then
l_status := 'E';
l_error_message :=
l_error_message ||
' vendor site code is not in system ';
fnd_file.put_line(fnd_file.log, 'error
occured ' || l_error_message);
end;
--------------- invoice number validation
------------------------
begin
select 'Y'
into l_invoice_num
from ap_invoices_all
where invoice_num = x1.invoice_num
and vendor_id = l_vendor_id;
if invoice_num = 'Y' then
l_status := 'E';
l_error_message :=
l_error_message ||
'invoice number already exist for the supplier';
fnd_file.put_line(fnd_file.log, 'error
occured ' || l_error_message);
end if;
exception
when others then
null;
end;
------------------ invoice currency code validation
------------------------
begin
select currency_code
into l_invoice_currency_code
from fnd_currencies
where ltrim(rtrim(currency_code))
= x1.invoice_currency_code
and enable_flag = 'Y';
exception
when others then
l_status := 'E';
l_error_message :=
l_error_message ||
' invalid invoice currency code given ';
fnd_file.put_line(fnd_file.log, 'error
occured ' || l_error_message);
end;
----------------- gl date validation
----------------------------
begin
select 'Y'
into l_gl_date
from gl_period_statuses
where x1.gl_date between start_date and end_date
and closing_status = 'O'
and set_of_books_id =
fnd_profile.value('GL_SET_OF_BKS_ID')
and application_id = 101;
exception
when others then
l_status := 'E' l_error_message :=
l_error_message ||
' gl_date given is not open';
fnd_file.put_line(fnd_file.log, 'error
occured ' || l_error_message);
end;
----------------- source validation --------------------
/* BEGIN
SELECT 'Y'
INTO
l_source
FROM
ap_lookup_codes
WHERE
lookup_type = 'SOURCE'
AND
lookup_code = x1.SOURCE;
EXCEPTION
WHEN OTHERS
THEN
l_status
:= 'E';
l_error_message :=
l_error_message || 'source not exists in the system';
fnd_file.put_line (fnd_file.LOG,
'Error Occured'
|| l_error_message
);
END;*/
l_one_rec_failed:='N';
select
ap_invoices_interfaces_s.nextval into l_invoice_id from dual;
FOR X2 in C2(x1.invoice_num)
loop
l_line_status:='A';
BEGIN
/* SELECT 'Y'
INTO
l_lookup_code
FROM
ap_lookup_codes
WHERE lookup_type
= 'INVOICE LINE TYPE'
AND
displayed_field = x2.line_type; */
select lookup_code
into l_lookup_code
from ap_lookup_codes
where lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND upper(displayed_field)
= upper(x2.line_type);
exception
when other then
l_status := 'E';
l_error_message := 'invalid line type in system';
fnd_file.put_line(fnd_file.log, 'Error
occured' || l_error_message);
l_one_rec_failed := 'Y';
end;
begin
if x2.gl_accouant_code is not null then
select code_combination_id
into l_gl_ccid
from gl_code_combinations
where segment1 || '-' || segment2 || '-' || segment3 || '-' ||
segment4 || '-' || segment5 || '-' || segment6 || '-' ||
segment7 || '-' || segment8 =
x2.gl_account_code
and enabled_flag = 'Y';
end if;
exception
when others then
l_status := 'E';
l_error_message := 'Error occured while validating account_code';
fnd_file.put_line(fnd_file.log, 'error
occured' || l_error_message);
l_one_rec_failed := 'Y';
end;
--- updating staging table with retreived ccid.
begin
update supp_invoice_lins_stg
set ccid = l_gl_ccid
where rowid = x2.row_id;
exception
when others then
null;
end;
select
ap_invoice_lines_interface_s.nextval
into l_invoice_line_id
from dual;
---- updating invoice_line_id for the successful lines.
begin
update supp_invoice_lins_stg
set invoice_id = l_invoice_id,
invoice_line_id = l_invoice_line_id
where rowid = x2.row_id;
exception
when others then
null;
end;
end loop;
--------------
inserting data into ap_invoice_interface--------------
if l_status!='E' and l_one_rec_failed='N' THEN
insert into ap_invoice_inteface
(invoice_id, org_id,
invoice_type_lookup_code,
vendor_num, vendor_name,
vendor_site_code,
invoice_num, invoice_date,
invoice_currency_code,
invoice_amount,
gl_date, payment_currency_code,
terms_date,
terms_name,
payment_method_lookup_code,
description, exchange_rate,
exchange_rate_type, exchange_date,
doc_category_code, voucher_num,
attribute_category, attribute1, attribute2,
attribute3,
attribute4, attribute5,
attribute6,
attribute7, attribute8,
attribute9, SOURCE
)
values
((l_invoice_id, x1.org_id,
x1.invoice_type_lookup_code,
x1.vendor_num, x1.vendor_name, x1.vendor_site_code,
x1.invoice_num, x1.invoice_date,
x1.invoice_currency_code, x1.invoice_amount,
x1.gl_date,
x1.payment_currency_code, x1.terms_date,
x1.terms_name, x1.payment_method_lookup_code,
x1.description, x1.exchange_rate,
x1.exchange_rate_type, x1.exchange_date,
x1.doc_category_code, x1.voucher_num,
x1.attribute_category, x1.attribute1, x1.attribute2,
x1.attribute3, x1.attribute4, x1.attribute5,
x1.attribute6, x1.attribute7, x1.attribute8,
x1.attribute9, x1.SOURCE
);
INSERT INTO ap_invoice_lines_interface
(org_id,
po_number,
line_number,
line_type_lookup_code,
amount,
description,
dist_code_combination_id,
invoice_id,
invoice_line_id)
select org_id,
po_number,
line_number,
line_type,
amount,
description,
ccid,
invoice_id,
invoice_line_id
from supp_invoice_lins_stg
where invoice_num =
x1.invoice_num
and vendor_num = x1.vendor_num;
update
SUPP_INVOICE_HEADER_STG
set status = 'SUCCESS'
where invoice_num =
x1.invoice_num
and vendor_num = x1.vendor_num;
ELSE
update
SUPP_INVOICE_HEADER_STG
set status='Rejected',
error_message=l_error_message
where
invoice_num=x1.invoice_num
and vendor_num=x1.vendor_num;
END IF;
commit;
end loop;
exception
when others then
fnd_file.put_line(fnd_file.log,'Error
occured in main procedure '||l_error_message);
end SUPP_AP_INVOICES_PKG;
