Wednesday, December 14, 2011

Payment Batch Stuck at Formatting Stage

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 Lee Gunderson 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. So thanks to Lee Gunderson.

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

6 comments:

  1. Hi Sanjai, We have set the PPP with processing type as Electronic and payment completion point is set to 'Manual Setting only'. Is there any API to mark these payments as complete? Please advise?

    Thanks,
    Kalyan

    ReplyDelete
    Replies
    1. You could call iby_disburse_ui_api_pub_pkg.mark_all_pmts_complete (p_instr_id => , x_return_status => ).

      Delete
  2. hello Sanjai

    Is it possible to only Void the Payment ?

    ReplyDelete
    Replies
    1. Hello Again

      thanks for the Quick Response . I have tried using the package AP_PMT_CALLOUT_PKG.payment_voided

      but its error''s out on a 20001.

      Do we have to initiate a payment stop and then Void or just void ?

      Not sure if there is a Sequence before firing the package "Payment_void "or if this is not the Right package to use.

      thanks

      Delete
    2. Sorry, I did not understand your question earlier.
      In order to void a payment, I had to call
      1. Payment_stop_initiated
      2. Update iby_payments_all
      3. Call iby_disburse_ui_api_pub_pkg.stop_payment

      I do not remember why I did not use AP_PMT_CALLOUT_PKG.payment_voided but above sequence worked.

      Delete