Supplier Opening Balance Upload( Invoice/Prepayment) in Ebs R12

delete from ap_invoice_lines_interface where org_id='148' and description ='IOU Opening Balance upto 31-OCT-2016';
delete from ap_invoices_interface  where org_id='148' and INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT';
delete from ap_interface_rejections;
--delete from pran_ap_invoices_stg;
commit;


-- balance upload

insert into MPFL_ap_invoices_stg   (OU_NAME, ORG_ID, INVOICE_TYPE, VENDOR_NAME, VENDOR_NUMBER, VENDOR_SITE, INVOICE_CURRENCY, EXCHANGE_RATE, INVOICE_NO, INVOICE_DATE, INVOICE_AMOUNT, DESCRIPTION, GL_DATE, PAYMENT_METHOD_CODE, LINE_NUMBER,
    LINE_TYPE, LINE_AMOUNT, CODE_COMBINATION, H_ERROR_MESSAGE, H_VERIFY_FLAG,     L_ERROR_MESSAGE, L_VERIFY_FLAG, INVOICE_AMOUNT2, INVOICE_AMOUNT3) Values('Selling OU',null,'PREPAYMENT','IOU : Md.Shafiqul Islam Khan (12309)','12912309',Null,'BDT',1,'SA/OPBAL/12912309','31-OCT-2016',6382600,'IOU Opening Balance upto 31-OCT-2016','31-OCT-2016','CHECK',1,'Item', 6382600, '129-000-000-0000-00000-124701-000-000-00000-00000', NULL, null, NULL,null, 6382600, 0);
insert into MPFL_ap_invoices_stg   (OU_NAME, ORG_ID, INVOICE_TYPE, VENDOR_NAME, VENDOR_NUMBER, VENDOR_SITE, INVOICE_CURRENCY, EXCHANGE_RATE, INVOICE_NO, INVOICE_DATE, INVOICE_AMOUNT, DESCRIPTION, GL_DATE, PAYMENT_METHOD_CODE, LINE_NUMBER,
    LINE_TYPE, LINE_AMOUNT, CODE_COMBINATION, H_ERROR_MESSAGE, H_VERIFY_FLAG,     L_ERROR_MESSAGE, L_VERIFY_FLAG, INVOICE_AMOUNT2, INVOICE_AMOUNT3) Values('Selling OU',null,'PREPAYMENT','IOU : Khandoker Abdul Wadud (15438)','12915438',Null,'BDT',1,'SA/OPBAL/12915438','31-OCT-2016',399481,'IOU Opening Balance upto 31-OCT-2016','31-OCT-2016','CHECK',1,'Item', 399481, '129-000-000-0000-00000-124701-000-000-00000-00000', NULL, null, NULL,null, 399481, 0);



exec APPS.MPFL_ap_invoice_api();

update ap_invoices_interface set TERMS_ID = 10000, PAYMENT_METHOD_LOOKUP_CODE = 'CHECK',
TERMS_DATE='31-OCT-2016',PAYMENT_METHOD_CODE='CHECK' where org_id='148' and INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT';


commit;


CREATE OR REPLACE procedure APPS.MPFL_ap_invoice_api
as

l_verify_flag char(1);
l_org_id hr_operating_units.organization_id%type;
l_error_message varchar2(3000);
l_invoice_type ap_lookup_codes.lookup_code%type;
l_vendor_id po_vendors.vendor_id%type ;
l_vendor_site_id po_vendor_sites_all.vendor_site_id%type;
l_line_type ap_invoice_lines_interface.line_type_lookup_code%type;
l_currency_code fnd_currencies.currency_code%type ;
l_code_combination_id number(20);


cursor h_invoice is
select distinct
ou_name,
invoice_type,
invoice_no,
vendor_number,
vendor_name,
vendor_site,
invoice_currency,
invoice_date,
invoice_amount,
description,
gl_date,
exchange_rate
from MPFL_ap_invoices_stg
where nvl(h_verify_flag,'N') = 'N';


cursor l_invoice (p_invoice_no varchar2) is
select distinct invoice_no,
description,
line_type,
code_combination,
line_number,
line_amount
from MPFL_ap_invoices_stg
where invoice_no = p_invoice_no
and nvl(l_verify_flag,'N') = 'N';



begin


for h1 in h_invoice loop

l_verify_flag := 'Y';
l_error_message := null;



begin
select organization_id
into l_org_id
from hr_operating_units
where upper(name) = upper(trim(h1.ou_name));

exception
when others then
l_verify_flag := 'N';
l_error_message := 'Invalid Operating Unit...';
end;


begin

select lookup_code
into l_invoice_type
from ap_lookup_codes
where lookup_type(+) = 'INVOICE TYPE'
and upper(displayed_field) = upper(trim(h1.invoice_type));

exception
when others then
l_verify_flag := 'N';
l_error_message := 'Invalid Invoice Type...';
end;


begin

select
pv.vendor_id,
pvs.vendor_site_id
into l_vendor_id,
l_vendor_site_id
from po_vendors pv,
po_vendor_sites_all pvs
where --upper(pv.vendor_name) = upper(trim(h1.vendor_name)) SEGMENT1
upper(pv.segment1) = upper(trim(h1.vendor_number))
and pv.vendor_id = pvs.vendor_id
--and pvs.vendor_site_code = h1.vendor_site
and pvs.ORG_ID = l_org_id; --h1.org_id;

Exception
when no_data_found then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Vendor or Vendor Site not found...' ;

when too_many_rows then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Vendor or Vendor Site is more than one...' ;

when others then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Vendor or Vendor Site has other errors' ;

end;


begin

select currency_code
into l_currency_code
from fnd_currencies
where currency_code = h1.invoice_currency;

Exception
when no_data_found then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Currency Code not found...' ;

when too_many_rows then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Currency Code is more than one...' ;

when others then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Currency code has other errors' ;
end;



If (h1.invoice_no is null or h1.invoice_date is null or h1.invoice_amount is null or h1.gl_date is null) then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Invoice No or Invoice Date or Invoice Amount or GL Date is Missing...' ;
end if;



if l_verify_flag <> 'N' then
--
-- inserting into ap_invoices_interface table
--
begin

insert into
ap_invoices_interface( invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
description,
source,
gl_date,
org_id,
exchange_rate,
goods_received_date,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
group_id ---To restrict the import
) values ( ap_invoices_interface_s.nextval,
h1.Invoice_No,
l_invoice_type,
h1.invoice_date,
l_vendor_id,
l_vendor_site_id,
h1.invoice_amount,
l_currency_code,
h1.description,
'MANUAL INVOICE ENTRY',
h1.gl_date,
l_org_id,
h1.exchange_rate,
h1.invoice_date-2,
sysdate,
1110,
sysdate,
1110,
'MIGRATE' );

update MPFL_ap_invoices_stg
set h_verify_flag = 'Y'
where invoice_no = h1.invoice_no;
commit;

exception
when others then
l_verify_flag := 'N';
l_error_message := l_error_message ||'Data not migrated into ap_invoices_interface table...' ;
update MPFL_ap_invoices_stg
set h_verify_flag = 'N',
h_error_message = l_error_message
where invoice_no = h1.invoice_no;
end;


--
-- lines data inserting into ap_invoice_lines_interface
--

for l1 in l_invoice (h1.invoice_no)
loop


begin

select lookup_code
into l_line_type
from ap_lookup_codes
where lookup_type(+) = 'INVOICE DISTRIBUTION TYPE'
and displayed_field = l1.line_type;

exception
when others then
l_verify_flag := 'N';
l_error_message := 'Line type is not valid...';

end;


begin

select code_combination_id
into l_code_combination_id
from gl_code_combinations_v
where segment1||'-'||segment2||'-'||segment3||'-'||segment4||'-'||segment5||'-'||segment6||'-'||segment7||'-'||segment8||'-'||segment9||'-'||segment10 = l1.code_combination;

exception
when others then
l_verify_flag := 'N';
l_error_message := 'Code Combination is not valid...';
end;

if l_verify_flag <> 'N' then

begin

insert into
ap_invoice_lines_interface ( invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
dist_code_combination_id,
org_id,
description
)values( ap_invoices_interface_s.currval,
ap_invoice_lines_interface_s.nextval,
nvl(l1.line_number,1),
l_line_type,
l1.line_amount,
l_code_combination_id,
l_org_id,
l1.description);

update MPFL_ap_invoices_stg
set l_verify_flag = 'Y'
where invoice_no = l1.invoice_no
and line_number = l1.line_number;


exception

when others then
l_error_message := l_error_message || sqlerrm;
l_verify_flag := 'N';
update MPFL_ap_invoices_stg
set l_verify_flag = 'N',
l_error_message = l_error_message
where invoice_no = l1.invoice_no
and line_number = l1.line_number;

commit;

end;

else

update MPFL_ap_invoices_stg
set l_error_message = l_error_message,
l_verify_flag = 'N'
where invoice_no = l1.invoice_no
and line_number = l1.line_number;

end if;

end loop; ---end of line loop



else

update MPFL_ap_invoices_stg
set h_error_message = l_error_message,
h_verify_flag = 'N'
where invoice_no = h1.invoice_no;

end if;


end loop; ---end of header loop

commit;

end MPFL_ap_invoice_api;
/