Step by Step :Using Receipt API ->Create Cash Receipt
Functional Importance of Receipt API's
Oracle receipt APIs provide an extension to existing functionality of creating and manipulating receipts through standard AR Receipts forms and lockboxes.
Most of the public receipt API caters to the following basic functionality via different API calls:
- Creating a cash receipt.
- Applying a cash receipt to a debit item.
- Creating a cash receipt and applying it to a debit item in one pass.
- On-account application.
- Unapplying the on-account application.
- Unapplying the receipt application to a particular transaction.
- Reversing the receipt.
- Activity application, such as Receipt Write-off.
- Creating a miscellaneous receipt.
- Other account application, such as Claim Investigation.
- Receipt-to-receipt application.
- Creating a cash receipt and an on-account application in one pass.
These API potentially used when you are using two major products like AR and Trade managment.
Which API's is not supported
- Creating a Batch Receipt
Why Receipts API are in demand- For Migration in Transformation/re-implemenation project , company want to bring old receipts to newer system. Except Lockbox, there is no other way other than API.
- Integration :Some sectors like retail, healthcare , Telco need direct Integration with third party POS based system like (WINCOR etc)
- Need for excel based upload interface : Majority of collection for companies are in third and fourth week of the month , and all entry should go into system thus need for excel based receipt upload cann't be denied. The business need for having receipt WEBADI is still not supported by Oracle.
What is senariosThe senario which going to discuss here is "Create Cash Receipt "
In Reality a cash receipts may be created as identified (with a customer/Transaction) or as unidentified (without a customer).This routine is called to create cash receipts for the payment received in the form of a check or cash.
The key is
- When you tag customer/transaction it is identified
- Where there is no customer attached to receipt it is unidentified.
Moreover Receipt that you are going to create must have a Payment method , which have underline account details for identified and unidentified account.
Which API is being usedBy using the following procedure Ar_receipt_api_pub.Create_cash you can create a single cash receipt, as in the case of manually created cash receipts.
Step to use the APIYou have to perform these steps in order to get API executed
Step 1 : Identification of some mandatory and key column of APIOnce you identify the key and mandatory column , you have 50% done. What is recomended for you to check the API version in irep with your EBS version.Here is the example.
Step 2 : Idetify and the mandatory column and must do pre-requsite setup for receipt.Your Payment method , underline bank, respective accounting details must be pre-requiste step up for creating a receipt in Oracle.The mandatory requirement is Customer master with valid bill to and a valid transaction that must be open.Step 3: You need to initialize the apps_initialize pacakgeFor this you have to follow these steps:Run the query 1SELECT USER_ID FROM FND_USER WHERE USER_NAME='USER_NAME';Run the query 2SELECT application_id FROM fnd_application WHERE application_short_name LIKE 'APPL_SHRT_NAME'; --'AR';Run the query 3SELECT RESPONSIBILITY_ID FROM FND_RESPONSIBILITY WHERE APPLICATION_ID=222;Run the query 4Run thisscript ,
--- this script you can use to set the environment. --- this is tested in 11i --- you need to pass the details from query 1, 2 and 3 DECLARE p_user_id NUMBER; p_resp_id NUMBER; p_resp_appl_id NUMBER; BEGIN p_user_id := 1011 -- pass the value of query 1 p_resp_id := 50556; -- pass the value of query 2 p_resp_appl_id := 222; -- pass the value of query 3 fnd_global.apps_initialize ( user_id => p_user_id, resp_id => p_resp_id, resp_appl_id => p_resp_appl_id );END;
this will set the apps environment.Alternatively you can use fnd_global.apps_initialize(1290,51118,222) before calling API.Step 4: run the belowscript in Toad or sql*plus and once the procedure executed correctly Oracle will issue a receipt id.
DECLARE
p_api_version NUMBER;
p_init_msg_list VARCHAR2(240);
p_commit VARCHAR2(240);
p_validation_level NUMBER;
p_usr_currency_code VARCHAR2(240);
p_usr_exchange_rate_type VARCHAR2(240);
p_exchange_rate_type VARCHAR2(240);
p_exchange_rate NUMBER;
p_exchange_rate_date DATE;
p_factor_discount_amount NUMBER;
p_receipt_date DATE;
p_postmark_date DATE;
p_customer_number VARCHAR2(240);
p_customer_bank_account_id NUMBER;
p_customer_bank_account_num VARCHAR2(240);
p_customer_bank_account_name VARCHAR2(240);
p_location VARCHAR2(240);
p_customer_receipt_reference VARCHAR2(240);
p_remittance_bank_account_num VARCHAR2(240);
p_remittance_bank_account_name VARCHAR2(240);
p_receipt_method_name VARCHAR2(240);
p_doc_sequence_value NUMBER;
p_ussgl_transaction_code VARCHAR2(240);
p_anticipated_clearing_date DATE;
p_called_from VARCHAR2(240);
p_comments VARCHAR2(240);
p_issuer_name VARCHAR2(240);
p_issue_date DATE;
p_issuer_bank_branch_id NUMBER;
p_amount NUMBER;
p_receipt_number VARCHAR2(240);
p_receipt_method_id NUMBER;
p_customer_name VARCHAR2(240);
p_customer_id NUMBER;
p_currency_code VARCHAR2(10);
p_gl_date DATE;
p_deposit_date DATE;
p_customer_site_use_id NUMBER;
p_override_remit_account_flag VARCHAR2(1);
p_remittance_bank_account_id NUMBER;
p_maturity_date DATE;
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(240);
p_cr_id NUMBER;
p_global_attribute_rec AR_RECEIPT_API_PUB.global_attribute_rec_type;
p_attribute_rec AR_RECEIPT_API_PUB.attribute_rec_type;
BEGIN
p_receipt_number := 'HUB-TEST1'; --rECEIPT NUMBER
p_receipt_method_id := 14011;
p_customer_name := 'JOHNSON & JOHNSON (S) PTE LTD';
p_amount := 1200;
p_remittance_bank_account_id := '14560'; -- JUST PASS THE BANK DETAILS
p_currency_code := 'SGD';
p_receipt_date := SYSDATE;
p_gl_date := SYSDATE;
p_deposit_date := SYSDATE;
p_override_remit_account_flag := 'Y';
p_maturity_date := SYSDATE+60;
p_comments := 'POS Cash Collection';
fnd_global.apps_initialize(1290,51118,222);
AR_RECEIPT_API_PUB.Create_cash (
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_usr_currency_code => p_usr_currency_code
,p_currency_code => p_currency_code
,p_usr_exchange_rate_type => p_usr_exchange_rate_type
,p_exchange_rate_type => p_exchange_rate_type
,p_exchange_rate => p_exchange_rate
,p_exchange_rate_date => p_exchange_rate_date
,p_amount => p_amount
,p_factor_discount_amount => p_factor_discount_amount
,p_receipt_number => p_receipt_number
,p_receipt_date => p_receipt_date
,p_gl_date => p_gl_date
,p_maturity_date => p_maturity_date
,p_postmark_date => p_postmark_date
,p_customer_id => p_customer_id
,p_customer_name => p_customer_name
,p_customer_number => p_customer_number
,p_customer_bank_account_id => p_customer_bank_account_id
,p_customer_bank_account_num => p_customer_bank_account_num
,p_customer_bank_account_name => p_customer_bank_account_name
,p_location => p_location
,p_customer_site_use_id => p_customer_site_use_id
,p_customer_receipt_reference => p_customer_receipt_reference
,p_override_remit_account_flag => p_override_remit_account_flag
,p_remittance_bank_account_id => p_remittance_bank_account_id
,p_remittance_bank_account_num => p_remittance_bank_account_num
,p_remittance_bank_account_name => p_remittance_bank_account_name
,p_deposit_date => p_deposit_date
,p_receipt_method_id => p_receipt_method_id
,p_receipt_method_name => p_receipt_method_name
,p_doc_sequence_value => p_doc_sequence_value
,p_ussgl_transaction_code => p_ussgl_transaction_code
,p_anticipated_clearing_date => p_anticipated_clearing_date
,p_called_from => p_called_from
,p_global_attribute_rec => p_global_attribute_rec
,p_attribute_rec => p_attribute_rec
,p_comments => p_comments
,p_issuer_name => p_issuer_name
,p_issue_date => p_issue_date
,p_issuer_bank_branch_id => p_issuer_bank_branch_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_cr_id => p_cr_id
);
IF (x_return_status = 'S') THEN
COMMIT;
dbms_output.put_line('SUCCESS');
dbms_output.put_line('Return Status = '|| SUBSTR (x_return_status,1,255));
dbms_output.put_line('p_cr_id = '||p_cr_id);
ELSE
ROLLBACK;
dbms_output.put_line('Return Status = '|| SUBSTR (x_return_status,1,255));
dbms_output.put_line('Message Count = '|| TO_CHAR(x_msg_count ));
dbms_output.put_line('Message Data = '|| SUBSTR (x_msg_data,1,255));
dbms_output.put_line(APPS.FND_MSG_PUB.Get ( p_msg_index => APPS.FND_MSG_PUB.G_LAST,
p_encoded => APPS.FND_API.G_FALSE));
IF x_msg_count >=0 THEN
FOR I IN 1..10 LOOP
dbms_output.put_line(I||'. '|| SUBSTR (FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception :'||sqlerrm);
END;This is been tested in 11i . With little modification you can use in R12. Here is api code in text file.
Finally VerificationMake sure you API executed correctly , once done you can check the receipt creation from Receipt workbench screen. What you have to do, just search the Receipt number and verify the details are correctly been populated or not.Navigate to ReceivablesReceipts : Receipts.Query for Receipt Number 'HUB-TEST1'(As Example discussed above). The below screen shows the created cash receipt
We will some other API usage in another post.
