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.
