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;