API to Create Online Accounting for AP Payment in Oracle APPS R12 (ap_drilldown_pub_pkg.payment_online_accounting)

    PROCEDURE xx_payment_accounting(errbuf OUT VARCHAR2

   ,retcode           OUT VARCHAR2

   ,p_invoice_id    IN  NUMBER

   )

IS

CURSOR c1 

IS

SELECT DISTINCT aca.check_id, aca.check_number        

              FROM ap_invoices_all xlpi

      ,ap_invoice_payments_all aipa

  ,ap_checks_all aca

             WHERE xlpi.invoice_id   = aipa.invoice_id

               AND xlpi.invoice_id   = pin_invoice_id

               AND aipa.check_id     = aca.check_id

               AND ap_checks_pkg.get_posting_status(aca.check_id) = 'N';

   -- This condition is to pick invoices which are paid and unaccounted

   ;  

ln_processed_cnt    NUMBER := 0;

ln_failed_cnt       NUMBER := 0;  

lv_error_buf        VARCHAR2(4000);

ln_retcode          NUMBER; 

BEGIN

FOR rec_create_pmt_accounting IN c1

LOOP

BEGIN

fnd_file.put_line(fnd_file.log,'Check ID: '||rec_create_pmt_accounting.check_id);

ln_retcode   := NULL;

lv_error_buf := NULL;

ap_drilldown_pub_pkg.payment_online_accounting(p_check_id           => rec_create_pmt_accounting.check_id

  ,p_accounting_mode    => 'P'      

  ,p_errbuf             => lv_error_buf

  ,p_retcode            => ln_retcode

  ,p_calling_sequence   => 'XX_PAYMENT_ACCOUNTING'

  );

IF ln_retcode = 0 

THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id 

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounted Sucessfully'

);

ln_processed_cnt := ln_processed_cnt+1;

ELSIF ln_retcode = 1 THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounting ended in WARNING. Errbuf : '

||lv_error_buf

);

ln_processed_cnt := ln_processed_cnt+1;

ELSIF ln_retcode = 2 THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id 

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounting ended in ERROR. Errbuf : '

||lv_error_buf

);       

ln_failed_cnt := ln_failed_cnt +1;          

ELSE

fnd_file.put_line(fnd_file.log,'Invoice Check ID: '

||rec_create_pmt_accounting.check_id 

||'. Payment ('

||rec_create_pmt_accounting.check_number

||') Accounting ended in ERROR. Errbuf : '

||lv_error_buf

||' Retcode: '

||ln_retcode

);

ln_failed_cnt := ln_failed_cnt +1;

END IF; 

COMMIT;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line(fnd_file.log,'Invoice Check ID: ' 

||rec_create_pmt_accounting.check_id

||'. Payment Accounting failed with Unhandled Exception. Error Message:' 

||SQLERRM);

ln_failed_cnt := ln_failed_cnt + 1;

END;

END LOOP;

fnd_file.put_line ( fnd_file.log,'Processed Record Count: '

||ln_processed_cnt

||' Failed Record Count: '

||ln_failed_cnt);

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line ( fnd_file.log,'Main Error in xx_payment_accounting. Error Message: '||SQLERRM);

END xx_payment_accounting;

ap_checks_pkg.get_posting_status(check_id) to pick AP Invoice Payments which are not acconted.

Pass check_id from ap_checks_all (aca) to ap_checks_pkg.get_posting_status to pick invoice payments which are not accounted.

ap_checks_pkg.get_posting_status(aca.check_id)

INVALID_PATH: File location or filename was invalid(UTL_FILE) in Oracle APPS

1. Create Directory using below command

CREATE OR REPLACE directory XXDIR AS '/home/erp';

--Here change directory path(/home/erp) according to your path.

Give the required permissions to above path.

2. Connect to WinSCP/Putty and Create directory.

3. Now use the directory name in utl_file 

UTL_FILE.FOPEN('XXDIR ','file_name','R');