At our site, client does not use Oracle standard requests "Submit Single Payment Process Request" or "Schedule Payment Process Request" to process or schedule payments. Client uses a custom shell script and using CONCSUB submits Autoselect, Build and Record Print status programs and few other custom concurrent programs in between to process daily payments. Build program automatically creates payment instructions and submits “Format Payment Instructions” program. Build program submits “Format Payment Instruction” program and exits. My custom shell script did not wait for “Format Payment Instructions” to finish and as soon as Build was done, it submitted “Record Print Status” Program. In Development environment, most of the times number of invoices in a payment batch were small and programs were little faster, so when “Record Print Status” program started, “Format Payment Instruction” had always completed with success. In our system integration testing environment, performance was poor and users created a payment batch for around 70,000 invoices. For that payment batch, “Record Print status” started running when “Format Payment Instructions” was not complete. It screwed up payment batch. We could not cancel payment batch or run “Record Print Status” program again. We logged a SR with Oracle but Oracle did not help because we were using custom scripts etc. and skipped some validation performed by Payment Submission Program. We noticed that We could query individual payment on “Payments Dashboard” -> Payments Tab and for each and individual payment, we could click “Initiate Stop” and then “Confirm Stop”. It fixed our issue for that invoice and we could pay the invoice. But there were too many payments in the problem batch. So Vipul Maheshwari opened up OAF page for payment form, found few APIs that are used by “Initiate Stop” and “Confirm Stop” icons. We created following script to cancel all such payments.
set serveroutput on
spool z.lis
DECLARE
i number;
CURSOR c_pmt is
SELECT payment_id
, payee_name
, paper_document_number
, payment_amount
from apps.iby_payments_all
where payment_service_request_id = &1 /* checkrun_id */
and payment_status = 'INSTRUCTION_CREATED'
;
l_ret_status1 VARCHAR2(80);
l_msg_count1 NUMBER;
l_msg_data1 VARCHAR2(2000);
l_ret_status2 VARCHAR2(80);
l_msg_count2 NUMBER;
l_msg_data2 VARCHAR2(2000);
/*
This procedure is helpful if iby_disburse_ui_api_pub_pkg.stop_payment fails
due to some reason
*/
procedure msg(p_msg varchar2) Is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
/*
BIV_DEBUG is a Oracle table. Feel free to insert into / delete from this table.
It is used for Debugging purpose only and nothing else.
*/
insert into biv_debug(report_id, message) values ('xxap_skm', p_msg);
commit;
end;
/*
Start of main script to void all payments of payment processing request
*/
BEGIN
msg('Start');
msg('User :' || fnd_global.user_id);
msg('resp :' || fnd_global.resp_id);
msg('resp appl id :' || fnd_global.resp_appl_id);
msg('App short name:' ||fnd_global.application_short_name);
dbms_output.enable(1000000);
dbms_output.put_line('start xxap_skm');
fnd_global.APPS_INITIALIZE
( user_id => 7043
, resp_id => 20639
, resp_appl_id => 200
) ;
/*
These initialization were not needed
mo_global.init('SQLAP');
mo_global.set_policy_context('S','81');
*/
FOR l_pmt_rec in c_pmt
LOOP
AP_PMT_CALLOUT_PKG.Payment_Stop_Initiated
( p_payment_id => l_pmt_rec.payment_id
, p_stopped_date => sysdate
, p_stopped_by => 7043
, x_return_status => l_ret_status1
, x_msg_count => l_msg_count1
, x_msg_data => l_msg_data1
) ;
IF l_ret_status1 = 'S'
THEN
UPDATE iby_payments_all
SET payment_status = 'REMOVED_PAYMENT_STOPPED'
, stop_confirmed_flag = 'Y'
, stop_confirm_date = SYSDATE
, stop_confirmed_by = 7043 /* userid */
, last_update_date = sysdate
, last_updated_by = 7043
WHERE payment_id = l_pmt_rec.payment_id
;
iby_disburse_ui_api_pub_pkg.stop_payment
( p_pmt_id => l_pmt_rec.payment_id
, p_pmt_status => NULL
, x_return_status => l_ret_status2
) ;
null;
END IF;
/*
Get all error messages
*/
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_data2
, p_msg_index_out => i
);
msg('Error: ' || j || ':' || l_msg_data2);
END LOOP;
/*
Details of payment that has been voided
*/
msg( 'Payment Info- Id,' || l_pmt_rec.payment_id ||
' Payee, ' || l_pmt_rec.payee_name ||
' Amt, ' || l_pmt_rec.payment_amount ||
' Doc No, ' || l_pmt_rec.paper_document_number ||
' Stop Status, ' || l_ret_status1 ||
' Void Status, ' || l_ret_status2
) ;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
msg(sqlerrm);
END;
/
spool off
No comments:
Post a Comment