Auto Apply given receipts to Invoices in oracle apps
Introduction
This Post illustrates the steps required for Auto Apply given receipts to Invoices
Script to Auto Apply given receipts to invoices.
CREATE OR REPLACE PACKAGE BODY APPS.XX_RECEIPT_APPLICATION_PKG
IS
PROCEDURE MAIN(
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
PR_GL_DATE IN VARCHAR2)
IS
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_end_date);
if v_period_status =’Open’ then
v_date:=v_end_date;
else
v_date:=SYSDATE;
end if;
RECEIPT_APPLICATION(v_date);
END;
PROCEDURE RECEIPT_APPLICATION(PR_GL_DATE VARCHAR2)
IS
CURSOR C1 IS
SELECT *
FROM xx_AR_RECEIPT_TEST;
— WHERE invoice_number = ‘INDOPD0000301341’;
l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(240);
p_count number := 0;
v_invoice_ps_id number;
x_cash_receipt_id number;
v_apply_date date:=PR_GL_DATE;
v_gl_date date:=PR_GL_DATE;
x_inv_due_amount number;
x_rec_due_amount number;
x_err_flag varchar(1);
x_err_msg varchar2(1024);
BEGIN
—————————————–
— —- Set the applications context—–
—————————————–
begin
mo_global.set_policy_context(‘S’,gn_org_id);
end;
fnd_global.apps_initialize (gn_user_id, gn_resp_id, gn_resp_appl_id);
printlog(‘USER_ID=’||gn_user_id ||’,’||’RESP_ID=’ ||gn_resp_id||’,’||
‘APPLICATION_ID=’||gn_resp_appl_id);
printlog(‘Date Passed =’||v_apply_date);
FOR I IN C1 LOOP
x_err_flag := ‘N’;
x_err_msg := null;
v_invoice_ps_id := null;
x_cash_receipt_id := null;
x_inv_due_amount :=0;
x_rec_due_amount :=0;
———————————————————————————–
——Validation to check invoice due amount remaining is not eqal to zero—–
———————————————————————————–
begin
SELECT aps.amount_due_remaining
into x_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.invoice_number
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 invoice number=’||i.invoice_number);
printlog(‘No data found while validating invoice due amount for 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 invoice number=’||i.invoice_number);
printlog(‘Found too many rows while validating invoice due amount for invoice number=’||i.invoice_number);
end;
——————————————————————–
——Validation to check receipt amount is not eqal to zero—–
———————————————————————
begin
SELECT -aps.amount_due_remaining
into x_rec_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.receipt_number
and aps.status = ‘OP’;
Exception
when NO_DATA_FOUND then
x_err_flag :=’Y’;
x_err_msg := (‘No data found while validating receipt due amount for receipt number=’||i.receipt_number);
printlog(‘No data found while validating receipt due amount for receipt number=’||i.receipt_number);
when TOO_MANY_ROWS then
x_err_flag :=’Y’;
x_err_msg := (‘Found too many rows while validating receipt due amount for receipt number=’||i.receipt_number);
printlog(‘Found too many rows while validating receipt due amount for receipt number=’||i.receipt_number);
end;
IF i.apply_amount>0 and x_inv_due_amount >0 and x_rec_due_amount>0 then
———————————————————————-
——Get payment schedule id for applying receipt to the invoice—–
———————————————————————-
begin
SELECT APS.Payment_Schedule_Id
into v_invoice_ps_id
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- While Validating Invoice matching records not found for the customer number=’||i.bill_customer_number
||’,’||’Invoice number=’||i.invoice_number||’,’||’Receipt number=’||i.receipt_number);
printlog(‘NO1- While Validating Invoice matching records not found for the customer number=’||i.bill_customer_number
||’,’||’Invoice number=’||i.invoice_number||’,’||’Receipt number=’||i.receipt_number);
when TOO_MANY_ROWS then
x_err_flag := ‘Y’;
x_err_msg :=(‘TOO1- While Validating Invoice found Duplicate records for the customer number=’||i.bill_customer_number||’,’||’Invoice_number=’
||i.invoice_number||’,’||’Receipt number=’||i.receipt_number);
printlog(‘TOO1- While Validating Invoice found Duplicate records for the customer number=’||i.bill_customer_number||’,’||’Invoice_number=’
||i.invoice_number||’,’||’Receipt number=’||i.receipt_number);
when others then
x_err_flag := ‘Y’;
x_err_msg :=(‘OT1- While Validating Invoice some error occured for the customer number=’||i.bill_customer_number||’,’||’Invoice_number=’
||i.invoice_number||’,’||’Receipt number=’||i.receipt_number);
printlog(‘OT1- While Validating Invoice some error occured for the customer number=’||i.bill_customer_number||’,’||’Invoice_number=’
||i.invoice_number||’,’||’Receipt number=’||i.receipt_number);
end;
———————————————————————-
——Get cash receipt id for applying receipt to the invoice——–
———————————————————————-
begin
SELECT aps.cash_receipt_id
into x_cash_receipt_id
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.receipt_number
and aps.status=’OP’;
exception
when NO_DATA_FOUND then
x_err_flag := ‘Y’;
x_err_msg :=(‘NO2- While Validating Receipt matching records not found for the customer number=’||i.bill_customer_number
||’,’||’Invoice number=’||i.invoice_number||’,’||’Receipt number=’||i.receipt_number);
printlog(‘NO2- While Validating Receipt matching records not found for the customer number=’||i.bill_customer_number
||’,’||’Invoice number=’||i.invoice_number||’,’||’Receipt number=’||i.receipt_number);
when TOO_MANY_ROWS then
x_err_flag := ‘Y’;
x_err_msg :=(‘TOO2- While Validating Receipt found Duplicate records for the customer number=’||i.bill_customer_number||’,’||’Invoice_number=’
||i.invoice_number||’,’||’Receipt number=’||i.receipt_number);
printlog(‘TOO2- While Validating Receipt found Duplicate records for the customer number=’||i.bill_customer_number||’,’||’Invoice_number=’
||i.invoice_number||’,’||’Receipt number=’||i.receipt_number);
when others then
x_err_flag := ‘Y’;
x_err_msg :=(‘OT2- While Validating Receipt some error occured for the customer number=’||i.bill_customer_number||’,’||’Invoice_number=’
||i.invoice_number||’,’||’Receipt number=’||i.receipt_number);
printlog(‘OT2 – While Validating Receipt some error occured for the customer number=’||i.bill_customer_number||’,’||’Invoice_number=’
||i.invoice_number||’,’||’Receipt number=’||i.receipt_number);
end;
——————————————
——applying receipt to the invoice—–
——————————————
IF x_err_flag = ‘N’ THEN
AR_RECEIPT_API_PUB.APPLY(p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_cash_receipt_id => x_cash_receipt_id,
p_apply_date => v_apply_date,
p_apply_gl_date => v_gl_date,
p_applied_payment_schedule_id => v_invoice_ps_id,
p_org_id => gn_org_id,
p_amount_applied => i.apply_amount,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
printlog(‘ API Status =’ || l_return_status);
printlog(‘Error Count =’ || l_msg_count);
if l_msg_count = 1 Then
printlog(‘l_msg_data ‘ || l_msg_data);
x_err_flag:=’Y’;
x_err_msg := l_msg_data;
elsif l_msg_count > 1 Then
x_err_flag:=’Y’;
x_err_msg :=’Multiple errors during API execution, Please check the log for the errors’;
loop
p_count := p_count + 1;
l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);
if l_msg_data is NULL Then
exit;
end if;
printlog(‘Message ‘ || p_count || ‘. ‘ || l_msg_data);
end loop;
else
printlog(‘Receipt Number=’||i.receipt_number||’,’||’Invoice Number=’||i.invoice_number||’,’||’apply amount=’||i.apply_amount);
end if;
END IF;
else
x_err_flag :=’Y’;
x_err_msg :=(‘Apply amount given is 0 for the invoice number ‘||i.invoice_number||’ or remaining due amount is
0 or program ran into exception for either invoice or receipt—>Please check log’);
end if;
update xx_ar_receipt_test
set error_flag =x_err_flag,
error_msg =x_err_msg
where receipt_number=i.receipt_number
and invoice_number=i.invoice_number;
commit;
END LOOP;
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_RECEIPT_APPLICATION_PKG;
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
