Cash Receipt creation (ar_receipt_api_pub.create_cash)

These simple scripts could be used to create receipt for various testings such as bank account reconciliation, match receipts to customer invoices. Please perform following steps:1. Create a sequence using create sequence skm_receipt_number_s start with 1;2. Create the following package. This package has receipt data too. Actually this should have been separated but due to laziness, i put it inside he package because i needed to verify a set data. You might need to modify RCT procedure and add list of receipts to be created.create or replace package skm_pkg as TYPE receipt_rec IS RECORD ( amount NUMBER , customer_number VARCHAR2(30) , receipt_method_name VARCHAR2(90) , credit_card_code VARCHAR2(30) , cust_id NUMBER , cust_bank_id NUMBER , cust_site_id NUMBER , receipt_method_id NUMBER , remit_bank_id NUMBER ) ; TYPE receipt_table IS TABLE OF receipt_rec index by binary_integer; procedure rct ( retcode varchar2 , errbuf varchar2 ) ;end;/show errorscreate or replace package body skm_pkg asPROCEDURE get_user_id ( p_user_name VARCHAR2 , p_user_id IN OUT NUMBER , p_err_msg IN OUT VARCHAR2 ) ISBEGIN p_err_msg := null; SELECT user_id INTO p_user_id FROM fnd_user WHERE user_name = p_user_name; -- -- EXCEPTION WHEN OTHERS THEN p_user_id := -1; p_err_msg := 'procedure Get_user_Id error:'sqlerrm;END;PROCEDURE get_resp_id ( p_resp_key VARCHAR2 , p_resp_id IN OUT NUMBER , p_resp_appl_id IN OUT NUMBER , p_err_msg IN OUT VARCHAR2 ) ISBEGIN SELECT responsibility_id , application_id INTO p_resp_id , p_resp_appl_id FROM fnd_responsibility WHERE responsibility_key = p_resp_key; -- -- EXCEPTION WHEN OTHERS THEN p_resp_id := -1; p_resp_appl_id := -1; p_err_msg := 'procedure Get_resp_Id error:'sqlerrm;END;PROCEDURE init( p_user_name VARCHAR2, p_resp_key VARCHAR2, px_err_msg IN OUT VARCHAR2) Is l_user_id NUMBER; l_resp_id NUMBER; l_appl_id NUMBER;BEGIN px_err_msg := null; get_user_id(p_user_name, l_user_id, px_err_msg); IF px_err_msg IS NOT NULL THEN RETURN; END IF; -- get_resp_id(p_resp_key, l_resp_id, l_appl_id, px_err_msg); IF px_err_msg IS NOT NULL THEN RETURN; END IF; -- -- FND_GLOBAL.apps_initialize(l_user_id, l_resp_id, l_appl_id); dbms_output.put_line(l_user_id ',' l_resp_id ',' l_appl_id);END;PROCEDURE add_receipt ( p_rct_tbl IN OUT SKM_PKG.receipt_table , amount NUMBER DEFAULT 10.39 , credit_card_code VARCHAR2 DEFAULT 'VI' , customer_number VARCHAR2 DEFAULT '100000' , receipt_method_name VARCHAR2 DEFAULT 'PAYPAL' , cust_id NUMBER DEFAULT NULL , cust_bank_id NUMBER DEFAULT NULL , cust_site_id NUMBER DEFAULT NULL , receipt_method_id NUMBER DEFAULT NULL , remit_bank_id NUMBER DEFAULT 5701983 ) IS l_count NUMBER;BEGIN l_count := p_rct_tbl.COUNT + 1; p_rct_tbl(l_count).amount := amount; p_rct_tbl(l_count).customer_number := customer_number; p_rct_tbl(l_count).receipt_method_name := receipt_method_name; p_rct_tbl(l_count).credit_card_code := credit_card_code; p_rct_tbl(l_count).cust_id := cust_id; p_rct_tbl(l_count).cust_bank_id := cust_bank_id; p_rct_tbl(l_count).cust_site_id := cust_site_id; p_rct_tbl(l_count).receipt_method_id := receipt_method_id; p_rct_tbl(l_count).remit_bank_id := remit_bank_id;END;---------PROCEDURE get_customer_info( p_rct_rec IN OUT receipt_rec, p_return_msg OUT VARCHAR2) ISBEGIN -- -- Get Customer Id -- p_return_msg := 'Cust ID:'; IF p_rct_rec.cust_id IS NULL THEN dbms_output.put_line('Cust Number:' p_rct_rec.customer_number ':'); SELECT cust_account_id INTO p_rct_rec.cust_id FROM hz_cust_accounts WHERE account_number = NVL(p_rct_rec.customer_number,'8266552'); END IF; -- -- Get Cust Site Id and Bank -- dbms_output.put_line('Cust Id:' p_rct_rec.cust_id ':'); p_return_msg := 'Bank ID:'; IF p_rct_rec.cust_site_id IS NULL OR p_rct_rec.cust_bank_id IS NULL THEN select b.site_use_id , c.bank_account_id INTO p_rct_rec.cust_site_id , p_rct_rec.cust_bank_id from hz_cust_acct_sites_all a , hz_cust_site_uses_all b , ap_bank_accounts_all c , ap_bank_account_uses_all d where (p_rct_rec.cust_site_id IS NULL OR p_rct_rec.cust_site_id = b.site_use_id) AND a.cust_account_id = p_rct_rec.cust_id and a.cust_acct_site_id = b.cust_acct_site_id and a.status = 'A' and b.status = 'A' and b.site_use_code = 'BILL_TO' and b.site_use_id = d.customer_site_use_id and d.external_bank_account_id = c.bank_account_id and (p_rct_rec.credit_card_code IS NULL OR p_rct_rec.credit_card_code = vvps_transactions.get_cc_type(c.bank_account_num) ) and rownum = 1; END IF; -- -- Get Receipt Method -- p_return_msg := 'Receipt Method ID:'; IF p_rct_rec.receipt_method_id IS NULL THEN SELECT receipt_method_id INTO p_rct_rec.receipt_method_id FROM ar_receipt_methods WHERE UPPER(name) = NVL(p_rct_rec.receipt_method_name,'CREDIT CARD') AND rownum = 1; END IF; p_return_msg := NULL; -- -- Get Remittance bank -- SELECT bank_account_id INTO p_rct_rec.remit_bank_id FROM ar_receipt_method_accounts_all WHERE receipt_method_id = p_rct_rec.receipt_method_id AND SYSDATE BETWEEN NVL(start_date,SYSDATE-1) AND NVL(end_date ,SYSDATE+1) ; -- -- EXCEPTION WHEN OTHERS THEN p_return_msg := p_return_msg sqlerrm; dbms_output.put_line('Error:' p_return_msg);END;------procedure rct ( retcode varchar2, errbuf varchar2) IS l_cr_id number; l_return_status VARCHAR2(30); l_msg_count NUMBER; l_msg_data VARCHAR2(2000); i number; l_loop_indx NUMBER; l_rct_tbl SKM_PKG.receipt_table; l_attribute_rec AR_RECEIPT_API_PUB.attribute_rec_type; l_receipt_number VARCHAR2(30); l_msg VARCHAR2(2000); l_receipt_num_min VARCHAR2(60); l_receipt_num_max VARCHAR2(60); l_ret_status BOOLEAN;BEGIN l_receipt_num_min := null; init('SMISRA','RECEIVABLES_MANAGER', l_msg_data); dbms_lock.sleep(5); arp_global.functional_currency := 'USD'; arp_global.set_of_books_id := 2; dbms_lock.sleep(5); l_attribute_rec.attribute_category := 'No'; l_attribute_rec.attribute1 := 'N'; add_receipt(l_rct_tbl, 12.42,null); dbms_output.put_line('Total Recs:' l_rct_tbl.count ':'); FOR l_loop_indx in l_rct_tbl.FIRST..l_rct_tbl.LAST LOOP IF l_loop_indx <> 10 THEN SELECT 'SKM-' skm_receipt_number_s.nextval INTO l_receipt_number FROM dual; END IF; IF l_receipt_number is NULL THEN l_receipt_number := 'SKM-' to_char(sysdate,'MMDD') '-01'; END IF; IF l_receipt_num_min IS NULL THEN l_receipt_num_min := l_receipt_number; END IF; l_receipt_num_max := l_receipt_number; dbms_output.put_line('Loop Index :' l_loop_indx ':'); get_customer_info(l_rct_tbl(l_loop_indx), l_msg); dbms_output.put_line('Receipt Method:' l_rct_tbl(l_loop_indx).receipt_method_id ':' ); dbms_output.put_line('Bank:' l_rct_tbl(l_loop_indx).cust_bank_id ':' ); ar_receipt_api_pub.Create_cash( p_api_version => 1.0, 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 => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_usr_currency_code => NULL, --the translated currency code p_currency_code => 'USD', p_usr_exchange_rate_type => NULL, p_exchange_rate_type => NULL, p_exchange_rate => NULL, p_exchange_rate_date => NULL, p_amount => l_rct_tbl(l_loop_indx).amount, p_factor_discount_amount => NULL, p_receipt_number => l_receipt_number, p_receipt_date => sysdate, p_gl_date => TRUNC(SYSDATE), p_maturity_date => NULL, p_postmark_date => NULL, p_customer_id => l_rct_tbl(l_loop_indx).cust_id, p_customer_name => NULL, p_customer_number => NULL, p_customer_bank_account_id => l_rct_tbl(l_loop_indx).cust_bank_id, p_customer_bank_account_num => NULL, p_customer_bank_account_name => NULL, p_location => NULL, p_customer_site_use_id => l_rct_tbl(l_loop_indx).cust_site_id, p_customer_receipt_reference => 'SKM-API', p_override_remit_account_flag => NULL, p_remittance_bank_account_id => l_rct_tbl(l_loop_indx).remit_bank_id, p_remittance_bank_account_num => NULL, p_remittance_bank_account_name => NULL, p_deposit_date => sysdate, p_receipt_method_id => l_rct_tbl(l_loop_indx).receipt_method_id, p_receipt_method_name => NULL, p_doc_sequence_value => NULL, p_ussgl_transaction_code => NULL, p_anticipated_clearing_date => NULL, p_called_from => 'pl/sql Script', p_attribute_rec => l_attribute_rec, -- p_global_attribute_rec IN global_attribute_rec_type DEFAULT global_attribute_rec_const, p_comments => 'Created for testing bank statement matching', p_issuer_name => NULL, p_issue_date => NULL, p_issuer_bank_branch_id => NULL, p_cr_id => l_cr_id ); dbms_output.put_line('Status:' l_return_status); dbms_output.put_line( 'CR Id:' l_cr_id); IF fnd_msg_pub.count_msg > 0 THEN FOR j in 1..FND_MSG_PUB.count_msg LOOP FND_MSG_PUB.get ( p_msg_index => j , p_encoded => 'F' , p_data => l_msg_data , p_msg_index_out => i ); dbms_output.put_line( 'Error: ' j ':' l_msg_data); END LOOP; END IF; END LOOP;end;end;/show errors3. Now run the above package to create receiptsset serveroutput on size 10000declare l_retcode VARCHAR2(30); l_errbuf VARCHAR2(2000);begin skm_pkg.rct(l_retcode, l_errbuf);end;