Client had over 400 bank accounts into which cash could be received.Some of these cash transactions were to be reconciled & accounted from Oracle Receivables. For this purpose "Receipt Handling Process" needed a mechanism to load bank receipts into Oracle AR in realtime. For this requirement, I developed an API based process which made possible the integration of "Receipt Handling Process" with Oracle Receivables. This technique was preferred over lockbox approach. The requirement was quite simple, receipts from a third party system were being dumped into a couple of tables. Those receipts were to be validated and transferred into Oracle Receivables. Doing a bit of research on Metalink I found that AR now has a receipt creation API. Please find the steps below for implementing a Receipt creation API in Oracle AR.
Step 1. Validate the following:-
a. Ensure that exchange Rate exists in the system if the receipt being interfaced is a foreign currency receipt.
b. Validate that the receipt belongs to an Open or Future enterable period in GL.
This check can be done by using below function within a package
FUNCTION validate_gl_accounting_date (
p_accounting_date IN DATE
,p_sob_id IN NUMBER
)
RETURN BOOLEAN
IS
v_count NUMBER := 0;
BEGIN
SELECT COUNT ( * )
INTO v_count
FROM gl_period_statuses gps
WHERE gps.application_id = g_gl_application_id
AND gps.set_of_books_id = p_sob_id
AND gps.closing_status IN ( 'O', 'F' )
AND p_accounting_date BETWEEN NVL ( gps.start_date, p_accounting_date )
AND NVL ( gps.end_date, p_accounting_date );
IF v_count > 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END validate_gl_accounting_date ;
Step 2. After successful validation, call the AR Receipt API ar_receipt_api_pub.create_cash. If the receipt is a foreign currency receipt, then parameters p_exchange_rate_type & p_exchange_rate_date must be supplied with a value. You may pass a value of fnd_api.g_false to parameter p_commit , to facilitate rollback if an errored is encountered in any other part of the functionality. If OUT parameter x_return_status is returned with a value of E, it implies error.
IF p_foriegn_currency THEN
ar_receipt_api_pub.create_cash (
p_api_version => g_api_version
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_currency_code => p_rhp_receipt.originating_currency
,p_amount => p_rhp_receipt_routing.routed_amount
,p_receipt_number => g_process_name || '-' ||
p_rhp_receipt.receipt_id ||
'-' ||
p_rhp_receipt_routing.receipt_routing_id
,p_receipt_date => p_rhp_receipt.receipt_date
,p_cr_id => v_cr_id
,p_receipt_method_name => g_receipt_method_name
,p_customer_number => p_rhp_receipt_routing.ar_customer_number
,p_comments => p_rhp_receipt.originating_customer
,p_customer_receipt_reference => SUBSTR(p_rhp_receipt.sender_to_receiver_info,1,30)
,p_remittance_bank_account_id => p_rhp_receipt.bank_account_id
,p_exchange_rate_type => xxdhi_util_pkg.g_conversion_type_code
,p_exchange_rate_date => p_rhp_receipt.receipt_date
);
ELSE
ar_receipt_api_pub.create_cash (
p_api_version => g_api_version
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_currency_code => p_rhp_receipt.originating_currency
,p_amount => p_rhp_receipt_routing.routed_amount
,p_receipt_number => g_process_name || '-' ||
p_rhp_receipt.receipt_id ||
'-' ||
p_rhp_receipt_routing.receipt_routing_id
,p_receipt_date => p_rhp_receipt.receipt_date
,p_cr_id => v_cr_id
,p_receipt_method_name => g_receipt_method_name
,p_customer_number => p_rhp_receipt_routing.ar_customer_number
,p_comments => p_rhp_receipt.originating_customer
,p_customer_receipt_reference => SUBSTR(p_rhp_receipt.sender_to_receiver_info,1,30)
,p_remittance_bank_account_id => p_rhp_receipt.bank_account_id
);
END IF ;
Step 3. If the requirement is To reverse an existing receipt, then use API ar_receipt_api_pub.REVERSE. Once again, prefer setting the validation level to fnd_api.g_valid_level_full
.
Following validation must be done at the time of reversal. This is required because Oracle wouldn't let you Reverse an applied receipt straightaway.
FUNCTION validate_reversal_flag_valid(p_cash_receipt_id OUT INTEGER)
RETURN BOOLEAN IS
CURSOR c_check IS
SELECT cash_receipt_id
,status
FROM ar_cash_receipts
WHERE receipt_number =
g_process_name || '-' || p_rhp_receipt.receipt_id || '-' ||
p_rhp_receipt_routing.receipt_routing_id;
p_check c_check%ROWTYPE;
no_existing_receipt EXCEPTION;
receipt_already_applied EXCEPTION;
receipt_already_reversed EXCEPTION;
BEGIN
OPEN c_check;
FETCH c_check
INTO p_check;
CLOSE c_check;
IF p_check.cash_receipt_id IS NULL
THEN
RAISE no_existing_receipt;
END IF;
IF p_check.status = 'APP'
THEN
RAISE receipt_already_applied;
END IF;
IF p_check.status = 'REV'
THEN
RAISE receipt_already_reversed;
END IF;
p_cash_receipt_id := p_check.cash_receipt_id;
RETURN TRUE;
EXCEPTION
WHEN no_existing_receipt THEN
fnd_message.set_name('AR', 'AR_RAPI_CASH_RCPT_ID_INVALID');
RETURN FALSE;
WHEN receipt_already_applied THEN
fnd_message.set_name('AR', 'GENERIC_MESSAGE');
fnd_message.set_token('GENERIC_TEXT'
,'This receipt has been applied to transaction in Delphi Oracle Receivables.' ||
chr(10) ||
'Please unapply the receipt before reversing');
RETURN FALSE;
WHEN receipt_already_reversed THEN
fnd_message.set_name('AR', 'GENERIC_MESSAGE');
fnd_message.set_token('GENERIC_TEXT'
,'This receipt has already been reversed in Delphi Oracle Receivables.');
RETURN FALSE;
END validate_reversal_flag_valid;
For any given receipt, the Step 2 & Step 3 is mutually exclusive.
To refer to the source code of the package body, please click on AR Receipts API.pdf
Apps2Fusion Articles 
Comments
The articles in your website is excellent.It gives an indepth knowledge to anyone.The presentation of the articles is also excellent.
I am looking forward for some more articles like this.
Regard s,
Rajesh
The articles in your website is excellent.It gives an indepth knowledge to anyone.The presentation of the articles is also excellent.
I am looking forward for some more articles like this.
Regard s,
Rajesh
In receivable i got following error,Please help me..
message : ARGLTP: Error from argopn
When I try to running the Program: General Ledger Transfer Program
The Program Completed with Error - message: ARGLTP: Error from argopn.
Thanks,
Mu rali
We have problem in developing the Customer AR Statement of account between GL Dates, to tally with AGING 4 or Aging 7 buckets report...
I have done some wonders in applications but this is a great failure for me...
Please help...
Re gards,
Ram
+9 71 50 7840842
Say we have 6 receipts lines in a file, 5 for invoices which are of $100 each and one for the credit memo -$20. The net receipts are $480.
The standard Oracle functionality will process the 5 invoices and error out the credit memo receipt line since it’s less than $0.
Now, we want to write off the invoices and also the credit memo. Can this be done in Oracle automatically?
I am using oracle apps 11.5.10.2.
When ever I run customer interface in oracle receivables manager responsibility then after completion of the import from interface tables to base tables there are some fields which are not populated in base tables. I tried to find them from front end of oracle apps but those fields are not populated. Can you suggest me the reason for that.
Thanks
V ishalaksha
If anybody can help me out to figure out rights to be granted to a user so that it can execute this API?
Thanks
I am working in 11.5.9 instance. In this I tried to create a cash receipt using above said api, but I am geting the following errors
Message1 ---Currency code took a precedence over the user currency code.
Message2 ---Exchange rate should not be entered.
Messag e3 ---Invalid exchange rate type.
Message4 ---Exchange rate date should not be entered.
Can You please suggest me what can I do to use these exchange rate parameters in the API, as I have to handle the creation od receipts for both forign currency and also with local currency.
I request you to please send replay to this mail id kkgrandhi
Regards
Kalyan
Amazing Article... A Job Well Done.
Regards,
Kasi, Vijayakumar
I am trying to invoke this API and i have got this error....
ORA- 01086: savepoint 'ACTIVITY_APP_P VT' never established
ORA -06512: at "APPS.AR_RECEIP T_API_PUB", line 7279
Some how this is a no commit or rollback error.
I have tried to call this API in a bulk processing environment.
When i call and pass in single transaction it works fine. But if i pass in as bulk it will always retrun this error.
This is the parameter i pass in -->
AR_RECE IPT_API_PUB.ACT IVITY_APPLICATI ON
( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => X_DUMMY_CHAR,
P_COMMIT => X_DUMMY_CHAR,
P_VALIDATION_LE VEL => X_DUMMY_NUM,
X_RETURN_STATUS => X_STATUS,
X_MSG_COUNT => X_COUNT,
X_MSG_DATA => X_DATA,
P_RECEIPT_NUMBE R => RECEIPT_REC.REC EIPT_NUMBER,
P_APPLIED_PAYME NT_SCHEDULE_ID => X_PMT_SDL,
P_RECEIVABLES_T RX_ID => X_REC_TRX,
P_APPLICATION_R EF_TYPE => X_DUMMY_CHAR,
P_APPLICATION_R EF_ID => X_DUMMY_NUM,
P_APPLICATION_R EF_NUM => X_DUMMY_NUM,
P_SECONDARY_APP LICATION_REF_ID => X_DUMMY_NUM,
P_RECEIVABLE_AP PLICATION_ID => X_DUMMY_NUM,
P_PAYMENT_METHO D_CODE => X_RFD_MTD,
P_ORG_ID => X_ORG_ID,
P_PAY_GROUP_LOO KUP_CODE => X_PAY_GROUP
);
Really need some help here.
Can any existing Receivables API be used to do the application of receipts that are already created? How this post creation clean-up scenario be handled? Need your suggestions. Thanks, Vivek
RSS feed for comments to this post