Sample Script to Submit Bank Statement Loader Program

sample procedure to submit a bank statement loader program and capture the status of programs submitted as part of Bank Statement Import

Flowchart:

Source Code:

CREATE OR REPLACE PROCEDURE XXX_REUSB_BANK_SUB_CAP (
   piv_file_name     IN   VARCHAR2,
   pin_request_id    IN   NUMBER,
   piv_file_path     IN   VARCHAR2,
   piv_load_option   IN   VARCHAR2,
   piv_map_temp      IN   VARCHAR2,
   piv_bank_br_num   IN   VARCHAR2,
   piv_accnt_num     IN   VARCHAR2,
   piv_gl_date       IN   VARCHAR2
)
AS
   ln_req_id             NUMBER         := 0;
   ln_user_id            NUMBER         := 0;
   ln_resp_id            NUMBER         := 0;
   ln_appl_id            NUMBER         := 0;
   lb_result             BOOLEAN;
   lv_phase              VARCHAR2 (100);
   lv_status             VARCHAR2 (100);
   lv_dev_phase          VARCHAR2 (100);
   lv_dev_status         VARCHAR2 (100);
   lv_message            VARCHAR2 (100);
   lv_srequest_status    VARCHAR2 (10);
   lv_statuscode         VARCHAR2 (50);
   ln_req_id2            NUMBER         := 0;
   lv_submission_error   VARCHAR2 (500);
   lv_conc_prog          VARCHAR2 (300);
   lv_load_status        VARCHAR2 (20) DEFAULT 'SUCCESS';
BEGIN
   DBMS_OUTPUT.put_line ('STATEMENT FILENAME :' || piv_file_name);
   DBMS_OUTPUT.put_line
             ('  ****** START OF BANK STATEMENT LOADER PROGRAM STATUS ******');

   BEGIN
      SELECT requested_by, responsibility_application_id, responsibility_id
        INTO ln_user_id, ln_appl_id, ln_resp_id
        FROM fnd_concurrent_requests
       WHERE request_id = pin_request_id;

      fnd_global.apps_initialize (ln_user_id,           
                                  ln_resp_id,                                             
                                  ln_appl_id );  
      COMMIT;
   END;

   BEGIN
      DBMS_OUTPUT.put_line(
                      'Submit the concurrent progam "Bank Statement Loader".'
                     );
    
      ln_req_id :=
         fnd_request.submit_request (application      => 'CE',
                                     program          => 'CESQLLDR',
                                     description      => NULL,
                                     start_time       => NULL,
                                     sub_request      => FALSE,
                                     argument1        => piv_load_option,
                                     argument2        => piv_map_temp,
                                     argument3        => piv_file_name,
                                     argument4        => piv_file_path,
                                     argument5        => piv_bank_br_num,
                                     argument6        => piv_accnt_num,
                                     argument7        => piv_gl_date,
                                     argument8        => NULL,
                                     argument9        => NULL,
                                     argument10       => NULL,
                                     argument11       => 'N',
                                     argument12       => NULL,
                                     argument13       => NULL
                                    );

      IF ln_req_id = 0
      THEN
         lv_submission_error := fnd_message.get;
         DBMS_OUTPUT.put_line
              (   '  ERROR: Bank Statement Loader not submitted "'
               || lv_submission_error
               || '"'
              );
         lv_load_status      :='FAILED';      
      ELSE
         COMMIT;
         DBMS_OUTPUT.put_line
                           (   '  Request Submitted : '
                            || ln_req_id
                           );

         LOOP
            lb_result :=
               fnd_concurrent.wait_for_request (ln_req_id,
                                                10,
                                                10,
                                                lv_phase,
                                                lv_status,
                                                lv_dev_phase,
                                                lv_dev_status,
                                                lv_message
                                               );

            IF lb_result AND lv_dev_phase = 'COMPLETE'
            THEN
               EXIT;
            END IF;
         END LOOP;
      END IF;
   END;

   IF lv_dev_phase = 'COMPLETE' 
   THEN
      DBMS_OUTPUT.put_line
                 (   '  Bank Statement Loader Request Completed with status '
                  || lv_dev_status
                 );
      IF lv_dev_status IN ('ERROR') 
      THEN    
        lv_load_status      :='FAILED';
      END IF;

      FOR cur_req IN (SELECT   a.request_id, a.logfile_name, a.outfile_name,
                               a.concurrent_program_id,
                               b.user_concurrent_program_name conc_prog
                          FROM fnd_concurrent_requests a,
                               fnd_concurrent_programs_tl b
                         WHERE a.parent_request_id = ln_req_id
                           AND a.concurrent_program_id =
                                                       b.concurrent_program_id
                           AND b.LANGUAGE = 'US'
                      ORDER BY a.request_id)
      LOOP
         DBMS_OUTPUT.put_line (   '  '
                               || cur_req.conc_prog
                               || ' -> REQUEST SUBMITTED '
                               || cur_req.request_id
                              );

         LOOP
            lb_result :=
               fnd_concurrent.wait_for_request (cur_req.request_id,
                                                10,
                                                10,
                                                lv_phase,
                                                lv_status,
                                                lv_dev_phase,
                                                lv_dev_status,
                                                lv_message
                                               );

            IF lb_result
            THEN
               IF lv_dev_phase = 'COMPLETE'
               THEN
                  DBMS_OUTPUT.put_line (   '  '
                                        || cur_req.conc_prog
                                        || ' has completed successfully'
                                       );
                  EXIT;
               END IF;
            END IF;
         END LOOP;

         IF UPPER (lv_dev_status) IN ('ERROR', 'WARNING')
         THEN
            DBMS_OUTPUT.put_line (   '  '
                                  || cur_req.conc_prog
                                  || ' has completed with status '
                                  || UPPER (lv_dev_status)
                                 );
            lv_load_status      :='FAILED';                                  
         ELSIF     UPPER (lv_dev_status) IN ('NORMAL')
               AND cur_req.conc_prog = 'Load Bank Statement Data'
         THEN
            FOR cur_req_in IN
               (SELECT   a.request_id, a.logfile_name, a.outfile_name,
                         a.concurrent_program_id,
                         b.user_concurrent_program_name conc_prog
                    FROM fnd_concurrent_requests a,
                         fnd_concurrent_programs_tl b
                   WHERE a.parent_request_id = cur_req.request_id
                     AND a.concurrent_program_id = b.concurrent_program_id
                     AND b.LANGUAGE = 'US'
                ORDER BY a.request_id)
            LOOP
               DBMS_OUTPUT.put_line (   '  '
                                     || cur_req_in.conc_prog
                                     || ' -> REQUEST SUBMITTED '
                                     || cur_req_in.request_id
                                    );

               LOOP
                  lb_result :=
                     fnd_concurrent.wait_for_request (cur_req_in.request_id,
                                                      10,
                                                      10,
                                                      lv_phase,
                                                      lv_status,
                                                      lv_dev_phase,
                                                      lv_dev_status,
                                                      lv_message
                                                     );

                  IF lb_result
                  THEN
                     IF lv_dev_phase = 'COMPLETE'
                     THEN
                        DBMS_OUTPUT.put_line
                                            (   '  '
                                             || cur_req_in.conc_prog
                                             || ' has completed with status '
                                             || UPPER (lv_dev_status)
                                            );
                        EXIT;
                     END IF;
                  END IF;
               END LOOP;

               IF UPPER (lv_dev_status) IN ('ERROR', 'WARNING') 
                  AND cur_req_in.conc_prog LIKE
                                    'Bank Statement Import%AutoReconciliation'
               THEN
                  lv_load_status      :='FAILED';
               ELSIF     UPPER (lv_dev_status) IN ('NORMAL')
                     AND cur_req_in.conc_prog LIKE
                                    'Bank Statement Import%AutoReconciliation'
               THEN
                  BEGIN
                     SELECT a.request_id,
                            b.user_concurrent_program_name conc_prog
                       INTO ln_req_id2,
                            lv_conc_prog
                       FROM fnd_concurrent_requests a,
                            fnd_concurrent_programs_tl b
                      WHERE a.parent_request_id = cur_req_in.request_id
                        AND a.concurrent_program_id = b.concurrent_program_id
                        AND b.LANGUAGE = 'US';
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        DBMS_OUTPUT.put_line
                           (   '  AutoReconcilation Execution report not called for:'
                            || cur_req_in.request_id
                           );
                     WHEN OTHERS
                     THEN
                        DBMS_OUTPUT.put_line
                           (   '  AutoReconcilation Execution report not called for:'
                            || cur_req_in.request_id
                           );
                  END;

                  DBMS_OUTPUT.put_line (   '  '
                                        || lv_conc_prog
                                        || ' -> REQUEST SUBMITTED '
                                        || ln_req_id2
                                       );

                  LOOP
                     lb_result :=
                        fnd_concurrent.wait_for_request (ln_req_id2,
                                                         10,
                                                         10,
                                                         lv_phase,
                                                         lv_status,
                                                         lv_dev_phase,
                                                         lv_dev_status,
                                                         lv_message
                                                        );

                     IF lb_result
                     THEN
                        IF lv_dev_phase = 'COMPLETE'
                        THEN
                           DBMS_OUTPUT.put_line
                                            (   '  '
                                             || lv_conc_prog
                                             || ' has completed with status '
                                             || UPPER (lv_dev_status)
                                            );
                           EXIT;
                        END IF;
                     END IF;
                  END LOOP;
               END IF;
            END LOOP;
         END IF;
      END LOOP;
   END IF;
   
   
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('  ' || 'OTHERS EXCEPTION' || SQLERRM (SQLCODE));
END XXX_REUSB_BANK_SUB_CAP;