Oracle EBs Apps Blog of Mahfuz, OPM and OBIEE Consultant

Saturday 12 November 2016

AP Invoice Opening Balance Upload

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;
/


SET DEFINE OFF;
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
   ('Export OU', NULL, 'PREPAYMENT', 'IOU - Hafijur Rahman PO Export', '2120569',
    NULL, 'BDT', 1, 'EXPORT/PR/OPB/00081', TO_DATE('05/31/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    18524.66, 'EXPORT OU Prepayment Opening Balance@June-2016', TO_DATE('05/31/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CHECK', 1,
    'Item', 18524.66, '128-000-000-0000-00000-124701-000-000-00000-00000', NULL, 'Y',
    NULL, 'Y', 18524.66, 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
   ('Export OU', NULL, 'PREPAYMENT', 'IOU - Shahid Ali Reza (ID# 29126) ', '2120623',
    NULL, 'BDT', 1, 'EXPORT/PR/OPB/00082', TO_DATE('05/31/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    16362, 'EXPORT OU Prepayment Opening Balance@June-2016', TO_DATE('05/31/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'CHECK', 1,
    'Item', 16362, '128-000-000-0000-00000-124701-000-000-00000-00000', NULL, 'Y',
    NULL, 'Y', 16362, 0);
COMMIT;

 =========================================

CREATE TABLE APPS.MPFL_AP_INVOICES_STG_XTBL
(
  OU_NAME              VARCHAR2(250 BYTE),
  ORG_ID               NUMBER,
  INVOICE_TYPE         VARCHAR2(30 BYTE),
  VENDOR_NAME          VARCHAR2(150 BYTE),
  VENDOR_NUMBER        VARCHAR2(30 BYTE),
  VENDOR_SITE          VARCHAR2(150 BYTE),
  INVOICE_CURRENCY     VARCHAR2(10 BYTE),
  EXCHANGE_RATE        NUMBER(10,2),
  INVOICE_NO           VARCHAR2(30 BYTE),
  INVOICE_DATE         DATE,
  INVOICE_AMOUNT       NUMBER(20,2),
  DESCRIPTION          VARCHAR2(200 BYTE),
  GL_DATE              DATE,
  PAYMENT_METHOD_CODE  VARCHAR2(200 BYTE),
  LINE_NUMBER          NUMBER(3),
  LINE_TYPE            VARCHAR2(50 BYTE),
  LINE_AMOUNT          NUMBER(20,2),
  CODE_COMBINATION     VARCHAR2(60 BYTE),
  H_ERROR_MESSAGE      VARCHAR2(2000 BYTE),
  H_VERIFY_FLAG        CHAR(1 BYTE),
  L_ERROR_MESSAGE      VARCHAR2(2000 BYTE),
  L_VERIFY_FLAG        CHAR(1 BYTE),
  INVOICE_AMOUNT2      NUMBER(20,2),
  INVOICE_AMOUNT3      NUMBER(20,2)
)

No comments:

Post a Comment

Internal location is Already assigned to another Ship-to Address

      SELECT *   FROM hz_cust_accounts  WHERE ACCOUNT_NUMBER = 'R08'; -- CUST_ACCOUNT_ID =51401 , PARTY_ID= 235048    SELECT *   FRO...