Example of Receipt Reversal API ar_receipt_api_pub.reverse and Invoice Adjustment API ar_adjust_pub.create_adjustment

I was working on a custom program to apply receipts, credit memos to invoices and debit memos. I was creating good amount of data. Before starting a new set of tests, I wanted to close transactions and payments created in prior run. It was little cumbersome to adjust invoices, Debit memos, credit memos and reverse receipts using forms. So I created this small script and run it using a simple concurrent program. This script can adjust hundreds or invoices and receipts in no time.

DECLARE l_adjust_and_reverse varchar2(30) := '&1'; CURSOR c_receipts_or_invoices IS SELECT acct.account_number , party.party_name , aps.trx_number , decode(aps.class,'PMT', cash_receipt_id, payment_schedule_id) Id , aps.class , aps.amount_due_original , aps.amount_due_remaining FROM ar_payment_schedules aps , hz_cust_accounts acct , hz_parties party WHERE aps.amount_due_remaining <> 0 AND aps.customer_id = acct.cust_account_id AND acct.party_id = party.party_id AND party.party_name like 'SANJAI%' ORDER BY acct.account_number , aps.trx_number ; PROCEDURE get_error_msg IS i number; l_msg_data varchar2(4000); BEGIN 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 ); fnd_file.put_line( fnd_file.log, 'Error: ' || j || ':' || l_msg_data); END LOOP; END IF; END get_error_msg; -- -- PROCEDURE reverse_receipt ( p_cash_receipt_id number ) IS l_ret_status VARCHAR2(1); l_msg_count NUMBER; l_msg_data VARCHAR2(32000); BEGIN ar_receipt_api_pub.reverse( -- Standard API parameters. 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_ret_status , x_msg_count => l_msg_count , x_msg_data => l_msg_data -- Receipt reversal related parameters , p_cash_receipt_id => p_cash_receipt_id , p_receipt_number => NULL , p_reversal_category_code => 'NSF' , p_reversal_category_name => NULL , p_reversal_gl_date => NULL , p_reversal_date => NULL , p_reversal_reason_code => 'NSF' , p_reversal_reason_name => NULL , p_reversal_comments => NULL , p_called_from => NULL , p_attribute_rec => AR_RECEIPT_API_PUB.attribute_rec_const , p_global_attribute_rec => AR_RECEIPT_API_PUB.global_attribute_rec_const , p_cancel_claims_flag => 'Y' , p_org_id => fnd_global.org_id ) ; get_error_msg; FND_FILE.PUT_LINE(FND_FILE.LOG, 'Return Status for receipt id (' || p_cash_receipt_id || ') = ' || l_ret_status); FND_FILE.PUT_LINE(FND_FILE.LOG, ' '); END; -- -- PROCEDURE adjust_transaction ( p_payment_schedule_id NUMBER ) IS l_msg_count NUMBER; l_msg_data VARCHAR2(32000); l_ret_status VARCHAR2(1); l_adj_rec ar_adjustments_all % ROWTYPE ; l_adj_num ar_adjustments_all.adjustment_number % TYPE; l_adj_id ar_adjustments_all.adjustment_id % TYPE; l_old_adj_id ar_adjustments_all.adjustment_id % TYPE; BEGIN l_adj_rec.type := 'INVOICE'; l_adj_rec.payment_schedule_id := p_payment_schedule_id; l_adj_rec.receivables_trx_id := 3026; l_adj_rec.apply_date := trunc(sysdate); l_adj_rec.gl_date := trunc(sysdate); l_adj_rec.created_from := 'SKM_SCRIPT'; ar_adjust_pub.create_adjustment ( p_api_name => 'AR_ADJUST_PUB' , p_api_version => 1.0 , p_init_msg_list => FND_API.G_TRUE , p_commit_flag => FND_API.G_FALSE , p_validation_level => FND_API.G_VALID_LEVEL_FULL , p_msg_count => l_msg_count , p_msg_data => l_msg_data , p_return_status => l_ret_status , p_adj_rec => l_adj_rec , p_chk_approval_limits => FND_API.G_TRUE , p_check_amount => FND_API.G_TRUE , p_move_deferred_tax => NULL , p_new_adjust_number => l_adj_num , p_new_adjust_id => l_adj_id , p_called_from => 'SKM_TEST_SCRIPT' , p_old_adjust_id => l_old_adj_id , p_org_id => fnd_global.org_id ) ; get_error_msg; FND_FILE.PUT_LINE(FND_FILE.LOG, 'Return Status = ' || l_ret_status || ', Adjustment ID = ' || l_adj_id); FND_FILE.PUT_LINE(FND_FILE.LOG, ' '); END adjust_transaction; -- -- BEGIN -- create_ar_invoice; FND_FILE.PUT_LINE(FND_FILE.LOG, 'Following Transactions will be Adjusted or Reversed'); FND_FILE.PUT_LINE(FND_FILE.LOG,''); FND_FILE.PUT_LINE(FND_FILE.LOG, 'Account number Party Name Trx Number ID Class Amt Due Orig Amt'); FND_FILE.PUT_LINE(FND_FILE.LOG, '====================================================================================================================='); FOR l_rec IN c_receipts_or_invoices LOOP FND_FILE.PUT_LINE(FND_FILE.LOG, rpad(l_rec.account_number , 20,' ') || ' ' || rpad(substr(l_rec.party_name,1,30), 30,' ') || ' ' || rpad(substr(l_rec.trx_number,1,20), 20,' ') || ' ' || to_char(l_rec.id,'999999999') || ' ' || rpad(substr(l_rec.class ,1, 5), 5,' ') || ' ' || to_char(l_rec.amount_due_remaining,'999999999.99') || ' ' || to_char(l_rec.amount_due_original ,'999999999.99') ); IF l_adjust_and_reverse = 'Y' THEN IF l_rec.class = 'PMT' THEN reverse_receipt(l_rec.id); ELSE adjust_transaction(l_rec.id); END IF; END IF; END LOOP; END; /