CREATE OR REPLACE PACKAGE BODY APPS.XX_CN_CM_APPLICATION_PKG
IS
PROCEDURE MAIN(errbuf OUT VARCHAR2,retcode OUT NUMBER,PR_GL_DATE IN VARCHAR2)
IS
v_count number(20):=0;
v_date date :=fnd_date.canonical_to_date(PR_GL_DATE);
v_end_date date;
v_period_status varchar2(20);
BEGIN
Begin
SELECT ps.end_date "Period End Date" ,
DECODE(ps.closing_status, 'O','Open' ,
'N','Never Opened' ,
'F','Future Enterable' ,
'C','Closed' ,'Unknown') "Period Status"
into v_end_date,v_period_status
from gl_period_statuses ps ,
gl_sets_of_books sob ,
fnd_application_vl fnd
where ps.application_id IN (101)
and sob.set_of_books_id = ps.set_of_books_id
and fnd.application_id = ps.application_id
and ps.adjustment_period_flag = 'N'
and sob.set_of_books_id =2021
and v_date BETWEEN TRUNC(ps.start_date) AND TRUNC(ps.end_date);
Exception
when no_data_found then
printlog('No data found while validating gl date passed in parameter');
end;
--printlog('date passed b '||v_date);
if v_period_status ='Open' then
v_date:=v_end_date;
else
v_date:=SYSDATE;
end if;
select count(1)
into v_count
from xx_ar_interface_test sai
where invoice_amount is not null;
IF
v_count>0
THEN
WITH_AMOUNT(v_date);
ELSE
WITHOUT_AMOUNT(v_date);
END IF;
END;
PROCEDURE WITHOUT_AMOUNT(PR_GL_DATE IN varchar2)
IS
cursor C1 is
select *
from xx_ar_interface_test sai;
--in Variables----
v_cm_payment_schedule number; -- := 101502; -- Payment Schedule ID of Credit Memo from APPS.AR_PAYMENT_SCHEDULES_ALL
v_inv_payment_schedule number; -- := 101500; -- Payment Schedule ID of Invoice from APPS.AR_PAYMENT_SCHEDULES_ALL
v_amount_applied number; -- := 1000; -- Amount of credit memo to apply to invoice
v_apply_date date :=PR_GL_DATE;---fnd_date.canonical_to_date(PR_GL_DATE);--'23-NOV-2017';
v_gl_date date :=PR_GL_DATE;---fnd_date.canonical_to_date(PR_GL_DATE);--'23-NOV-2017';
v_ussgl_transaction_code varchar2(1024); -- null, but check AR_RECEIVABLE_APPLICATIONS_ALL
v_null_flex varchar2(1024); -- null, unless you have flexfield segments to define
v_customer_trx_line_id number; -- null, but check AR_RECEIVABLE_APPLICATIONS_ALL
v_comments varchar2(240) := 'Applied automatically';
v_module_name varchar2(128) := 'AR'; -- If null, validation won 't occur
v_module_version varchar2(128) := ' 1 '; -- If null, validation won' t occur
--Out parameters----
v_out_rec_application_id number;
v_acctd_amount_applied_from number;
v_acctd_amount_applied_to number;
----Local Variables---
v_cm_due_amount number;
v_inv_due_amount number;
v_inv_count number;
v_cm_count number;
l_org NUMBER := 101;
x_err_flag varchar(1);
x_err_msg varchar2(1000);
x_applied_chk number;
cm_due_amount number;
inv_due_amount number;
begin
--DBMS_APPLICATION_INFO.set_client_info(l_org);
begin
mo_global.set_policy_context('S',l_org);
end;
-- printlog('Date passed '||v_apply_date);
FOR I IN C1 LOOP
x_err_flag := 'N';
x_err_msg := NULL;
v_inv_due_amount:=0;
v_cm_due_amount :=0;
v_inv_count :=0;
v_cm_count :=0;
cm_due_amount :=0;
inv_due_amount :=0;
-------------------------------------------------------------------------------
------Validation to check invoice due amount remaining is not eqal to zero-----
-------------------------------------------------------------------------------
Begin
SELECT aps.amount_due_remaining
into inv_due_amount
from ar_payment_schedules_all aps, hz_cust_accounts hca
where 1 = 1
and hca.account_number = i.bill_customer_number
and aps.customer_id = hca.cust_account_id
and aps.trx_number = i.reference
and aps.status ='OP';
Exception
when no_data_found then
x_err_flag :='Y';
x_err_msg := ('No data found while validating invoice due amount for reference number='||i.reference);
printlog('No data found while validating invoice due amount for reference number='||i.reference||'invoice number='||i.invoice_number);
when too_many_rows then
x_err_flag :='Y';
x_err_msg := ('Found too many rows while validating invoice due amount for reference number='||i.reference);
printlog('Found too many rows while validating invoice due amount for reference number='||i.reference||'invoice number='||i.invoice_number);
end;
-----------------------------------------------------------------------------------
------Validation to check credit memo due amount remaining is not eqal to zero-----
-----------------------------------------------------------------------------------
Begin
SELECT -aps.amount_due_remaining
into cm_due_amount
from ar_payment_schedules_all aps, hz_cust_accounts hca
where 1 = 1
and hca.account_number = i.bill_customer_number
and aps.customer_id = hca.cust_account_id
and aps.trx_number = i.invoice_number
and aps.status ='OP';
Exception
when No_data_found then
x_err_flag :='Y';
x_err_msg := ('No data found while validating credit memo due amount for invoice number='||i.invoice_number);
printlog('No data found while validating credit memo due amount for invoice number='||i.invoice_number||'reference number='||i.reference);
when too_many_rows then
x_err_flag :='Y';
x_err_msg := ('Found too many rows while validating creit memo due amount for invoice number='||i.invoice_number);
printlog('Found too many rows while validating credit memo due amount for invoice number='||i.invoice_number||'reference number='||i.reference);
end;
if inv_due_amount >0 and cm_due_amount >0 then
SELECT count(1)
into v_cm_count
from ar_payment_schedules_all aps
where aps.trx_number = i.invoice_number;
SELECT count(1)
into v_inv_count
from ar_payment_schedules_all aps, hz_cust_accounts hca
where 1 = 1
and hca.account_number = i.bill_customer_number
and aps.customer_id = hca.cust_account_id
and aps.trx_number = i.reference;
SELECT count(1)
into x_applied_chk
FROM ra_customer_trx_all rcta1,
ra_customer_trx_all rcta2,
ar_receivable_applications_all araa,
ar_payment_schedules_all aps,
hz_cust_accounts hca
WHERE 1 = 1
and aps.customer_id = hca.cust_account_id
and rcta2.customer_trx_id = aps.customer_trx_id
AND rcta1.customer_trx_id = araa.customer_trx_id
AND rcta2.customer_trx_id = araa.applied_customer_trx_id
and rcta1.org_id = l_org
and rcta1.trx_number = i.invoice_number
and rcta2.trx_number = i.reference;
if x_applied_chk = 0 then
if v_cm_count > 0 and v_inv_count > 0 then
---------------------------------------------------
------Get credit memo due amount remaining -------
---------------------------------------------------
Begin
SELECT -aps.amount_due_remaining, aps.payment_schedule_id
into v_cm_due_amount, v_cm_payment_schedule
from ar_payment_schedules_all aps, hz_cust_accounts hca
where 1 = 1
and hca.account_number = i.bill_customer_number
and aps.customer_id = hca.cust_account_id
and aps.trx_number = i.invoice_number
and aps.status = 'OP';
exception
when no_data_found then
x_err_flag := 'Y';
x_err_msg := ('NO1- Matching Records for CM due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
printlog('NO1- Matching Records for CM due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
when too_many_rows then
x_err_flag := 'Y';
x_err_msg := ('TOO1- More than one matching record for CM due amount for the credit memo='||i.invoice_number||'and invoice='||i.reference);
printlog('TOO1- More than one matching record for CM due amount for the credit memo='||i.invoice_number||'and invoice='||i.reference);
end;
---------------------------------------------------
------Get invoice due amount remaining -----------
---------------------------------------------------
Begin
SELECT aps.amount_due_remaining, aps.payment_schedule_id
into v_inv_due_amount, v_inv_payment_schedule
from ar_payment_schedules_all aps, hz_cust_accounts hca
where 1 = 1
and hca.account_number = i.bill_customer_number
and aps.customer_id = hca.cust_account_id
and aps.trx_number = i.reference
and aps.status = 'OP';
Exception
when no_data_found then
x_err_flag := 'Y';
x_err_msg := ('NO2- Matching Records for invoice due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
printlog('NO2- Matching Records for invoice due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
when too_many_rows then
x_err_flag := 'Y';
x_err_msg := ('TOO2- More than one matching record for invoice due amount found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
printlog('TOO2- More than one matching record for invoice due amount found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
end;
if v_cm_due_amount > v_inv_due_amount then
v_amount_applied := v_inv_due_amount;
else
v_amount_applied := v_cm_due_amount;
end if;
-- v_amount_applied := i.invoice_amount;
else
printlog('Skiped out Invoices => ' || i.invoice_number);
x_err_flag :='Y';
x_err_msg :=('Either credit memo'||i.invoice_number||' or invoice '||i.reference||' is not found in the database');
end if;
---------------------------------------------------
------Applying Credit memos to the invoice -------
---------------------------------------------------
if x_err_flag = 'N' then
arp_process_application.cm_application(p_cm_ps_id => v_cm_payment_schedule,
p_invoice_ps_id => v_inv_payment_schedule,
p_amount_applied => v_amount_applied,
p_apply_date => v_apply_date,
p_gl_date => v_gl_date,
p_ussgl_transaction_code => null,
p_attribute_category => null,
p_attribute1 => null,
p_attribute2 => null,
p_attribute3 => null,
p_attribute4 => null,
p_attribute5 => null,
p_attribute6 => null,
p_attribute7 => null,
p_attribute8 => null,
p_attribute9 => null,
p_attribute10 => null,
p_attribute11 => null,
p_attribute12 => null,
p_attribute13 => null,
p_attribute14 => null,
p_attribute15 => null,
p_global_attribute_category => null,
p_global_attribute1 => null,
p_global_attribute2 => null,
p_global_attribute3 => null,
p_global_attribute4 => null,
p_global_attribute5 => null,
p_global_attribute6 => null,
p_global_attribute7 => null,
p_global_attribute8 => null,
p_global_attribute9 => null,
p_global_attribute10 => null,
p_global_attribute11 => null,
p_global_attribute12 => null,
p_global_attribute13 => null,
p_global_attribute14 => null,
p_global_attribute15 => null,
p_global_attribute16 => null,
p_global_attribute17 => null,
p_global_attribute18 => null,
p_global_attribute19 => null,
p_global_attribute20 => null,
p_customer_trx_line_id => v_customer_trx_line_id,
p_comments => v_comments,
p_module_name => v_module_name,
p_module_version => v_module_version,
p_out_rec_application_id => v_out_rec_application_id,
p_acctd_amount_applied_from => v_acctd_amount_applied_from,
p_acctd_amount_applied_to => v_acctd_amount_applied_to);
if v_out_rec_application_id is not null then
commit;
printlog('Credit memo='||i.invoice_Number ||','|| 'Invoice=' || i.reference ||','|| 'CM amount=' ||
v_cm_due_amount ||','|| 'Invoice amount=' || v_inv_due_amount ||','|| 'Amount applied=' ||
v_amount_applied);
else
rollback;
end if;
else
printlog('API Execution is not processed due to validation error');
end if;
else
x_err_flag:='Y';
x_err_msg :=('Credit memo '||i.invoice_number||'already applied to Same Invoice number : '|| i.reference);
printlog('Credit memo '||i.invoice_number||'already applied to Same Invoice number : '|| i.reference);
end if;
else
x_err_flag:='Y';
x_err_msg :=('Please check that either credit memo amount or invoice due amount is zero or program ran into exception
for either invoice or credit memo--->Please check log');
end if;
update xx_ar_interface_test
set error_flag =x_err_flag,
error_msg =x_err_msg
where invoice_number =i.invoice_number
and reference =i.reference ;
commit;
end loop;
exception
when others then
-- dbms_output.put_line(sqlerrm);
printlog(sqlerrm);
end;
PROCEDURE WITH_AMOUNT(PR_GL_DATE IN varchar2)
IS
cursor C1 is
select *
from xx_ar_interface_test sai;
----local Variables Declaration-----
v_cm_payment_schedule number; -- := 101502; -- Payment Schedule ID of Credit Memo from APPS.AR_PAYMENT_SCHEDULES_ALL
v_inv_payment_schedule number; -- := 101500; -- Payment Schedule ID of Invoice from APPS.AR_PAYMENT_SCHEDULES_ALL
v_amount_applied number; -- := 1000; -- Amount of credit memo to apply to invoice
v_apply_date date :=PR_GL_DATE;---fnd_date.canonical_to_date(PR_GL_DATE);--'23-NOV-2017';
v_gl_date date :=PR_GL_DATE;--fnd_date.canonical_to_date(PR_GL_DATE);--'23-NOV-2017';
v_ussgl_transaction_code varchar2(1024); -- null, but check AR_RECEIVABLE_APPLICATIONS_ALL
v_null_flex varchar2(1024); -- null, unless you have flexfield segments to define
v_customer_trx_line_id number; -- null, but check AR_RECEIVABLE_APPLICATIONS_ALL
v_comments varchar2(240) := 'Applied automatically';
v_module_name varchar2(128) := 'AR'; -- If null, validation won 't occur
v_module_version varchar2(128) := ' 1 '; -- If null, validation won' t occur
-----Out parameters--------
v_out_rec_application_id number;
v_acctd_amount_applied_from number;
v_acctd_amount_applied_to number;
------Local Variables-----
v_cm_due_amount number;
v_inv_due_amount number;
v_inv_count number;
v_cm_count number;
l_org number := 101;
x_err_flag varchar(1);
x_err_msg varchar2(1000);
cm_due_amount number;
begin
---DBMS_APPLICATION_INFO.set_client_info(l_org);
begin
mo_global.set_policy_context('S',l_org);
end;
FOR I IN C1 LOOP
x_err_flag := 'N';
x_err_msg := NULL;
v_inv_due_amount:=0;
v_cm_due_amount :=0;
v_inv_count :=0;
v_cm_count :=0;
cm_due_amount :=0;
----------------------------------------------------------------------------------
------Validation to check credit memo due amount remaining is not eqal to zero-----
-----------------------------------------------------------------------------------
Begin
SELECT -aps.amount_due_remaining
into cm_due_amount
from ar_payment_schedules_all aps, hz_cust_accounts hca
where 1 = 1
and hca.account_number = i.bill_customer_number
and aps.customer_id = hca.cust_account_id
and aps.trx_number = i.invoice_number
and aps.status ='OP';
Exception
when no_data_found then
x_err_flag :='Y';
x_err_msg := ('No data found while validating creit memo due amount for invoice number='||i.invoice_number);
printlog('No data found while validating credit memo due amount for invoice number='||i.invoice_number||'Reference='||i.reference);
when too_many_rows then
x_err_flag :='Y';
x_err_msg := ('Found too many rows while validating credit memo due amount for invoice number='||i.invoice_number);
printlog('Found too many rows while validating credit memo due amount for invoice number='||i.invoice_number||'Reference='||i.reference);
end;
if i.invoice_amount >0 and cm_due_amount>0 then
SELECT count(1)
into v_cm_count
from ar_payment_schedules_all aps
where aps.trx_number = i.invoice_number;
SELECT count(1)
into v_inv_count
from ar_payment_schedules_all aps, hz_cust_accounts hca
where 1 = 1
and hca.account_number = i.bill_customer_number
and aps.customer_id = hca.cust_account_id
and aps.trx_number = i.reference;
if v_cm_count > 0 and v_inv_count > 0 then
---------------------------------------------------
------Get credit memo due amount remaining -------
---------------------------------------------------
Begin
SELECT -aps.amount_due_remaining, aps.payment_schedule_id
into v_cm_due_amount, v_cm_payment_schedule
from ar_payment_schedules_all aps, hz_cust_accounts hca
where 1 = 1
and hca.account_number = i.bill_customer_number
and aps.customer_id = hca.cust_account_id
and aps.trx_number = i.invoice_number
and aps.status ='OP';
Exception
when no_data_found then
x_err_flag := 'Y';
x_err_msg := ('NO1- Matching Records for CM due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
printlog('NO1- Matching Records for CM due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
when too_many_rows then
x_err_flag := 'Y';
x_err_msg := ('TOO1- More than one matching record for CM due amount found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
printlog('TOO1- More than one matching record for CM due amount found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
end;
---------------------------------------------------
------Get Incoice due amount remaining -------
---------------------------------------------------
Begin
SELECT aps.amount_due_remaining, aps.payment_schedule_id
into v_inv_due_amount, v_inv_payment_schedule
from ar_payment_schedules_all aps, hz_cust_accounts hca
where 1 = 1
and hca.account_number = i.bill_customer_number
and aps.customer_id = hca.cust_account_id
and aps.trx_number = i.reference
and aps.status ='OP';
Exception
when no_data_found then
x_err_flag := 'Y';
x_err_msg := ('NO2- Matching Records for invoice due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
printlog('NO2- Matching Records for invoice due amount Not found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
when too_many_rows then
x_err_flag := 'Y';
x_err_msg := ('TOO2- More than one matching record for invoice due amount found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
printlog('TOO2- More than one matching record for invoice due amount found for the credit memo='||i.invoice_number||'and invoice='||i.reference);
end;
/* if v_cm_due_amount > v_inv_due_amount then
v_amount_applied := v_inv_due_amount;
else
v_amount_applied := v_cm_due_amount;
end if;*/
if i.invoice_amount>v_inv_due_amount then
x_err_flag :='Y';
x_err_msg :='Invoice amount(CM amount) given is greater than the invoice due amount';
else
v_amount_applied := i.invoice_amount;
end if;
else
printlog('Skipped out Invoices => ' || i.invoice_number);
x_err_flag :='Y';
x_err_msg :=('Either credit memo'||i.invoice_number||' or invoice '||i.reference||' is not found in the database');
end if;
---------------------------------------------------
------Applying credit memos to the invoice---------
---------------------------------------------------
if x_err_flag = 'N' then
arp_process_application.cm_application(p_cm_ps_id => v_cm_payment_schedule,
p_invoice_ps_id => v_inv_payment_schedule,
p_amount_applied => v_amount_applied,
p_apply_date => v_apply_date,
p_gl_date => v_gl_date,
p_ussgl_transaction_code => null,
p_attribute_category => null,
p_attribute1 => null,
p_attribute2 => null,
p_attribute3 => null,
p_attribute4 => null,
p_attribute5 => null,
p_attribute6 => null,
p_attribute7 => null,
p_attribute8 => null,
p_attribute9 => null,
p_attribute10 => null,
p_attribute11 => null,
p_attribute12 => null,
p_attribute13 => null,
p_attribute14 => null,
p_attribute15 => null,
p_global_attribute_category => null,
p_global_attribute1 => null,
p_global_attribute2 => null,
p_global_attribute3 => null,
p_global_attribute4 => null,
p_global_attribute5 => null,
p_global_attribute6 => null,
p_global_attribute7 => null,
p_global_attribute8 => null,
p_global_attribute9 => null,
p_global_attribute10 => null,
p_global_attribute11 => null,
p_global_attribute12 => null,
p_global_attribute13 => null,
p_global_attribute14 => null,
p_global_attribute15 => null,
p_global_attribute16 => null,
p_global_attribute17 => null,
p_global_attribute18 => null,
p_global_attribute19 => null,
p_global_attribute20 => null,
p_customer_trx_line_id => v_customer_trx_line_id,
p_comments => v_comments,
p_module_name => v_module_name,
p_module_version => v_module_version,
p_out_rec_application_id => v_out_rec_application_id,
p_acctd_amount_applied_from => v_acctd_amount_applied_from,
p_acctd_amount_applied_to => v_acctd_amount_applied_to);
if v_out_rec_application_id is not null then
-- dbms_output.put_line('Committing.');
commit;
printlog('Credit memo='||i.invoice_Number ||','|| 'Invoice=' || i.reference ||','|| 'CM amount=' ||
v_cm_due_amount ||','|| 'Invoice amount=' || v_inv_due_amount ||','|| 'Amount applied=' ||
v_amount_applied);
else
rollback;
end if;
else
printlog('API Execution is not processed due to validation error');
end if;
else
x_err_flag:='Y';
x_err_msg :=('Please check that either credit memo amount or invoice due amount is zero or program ran into exception
for either invoice or credit memo--->Please check log');
end if;
update xx_ar_interface_test
set error_flag =x_err_flag,
error_msg =x_err_msg
where invoice_number =i.invoice_number
and reference =i.reference ;
commit;
end loop;
exception
when others then
printlog(sqlerrm);
end;
PROCEDURE printlog (p_text_mesg VARCHAR2)
IS
BEGIN
fnd_file.put_line (fnd_file.LOG, p_text_mesg);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error in "printlog" procedure');
END;
END XX_CN_CM_APPLICATION_PKG;
/
No comments:
Post a Comment