Thursday, April 26, 2012

Script to Submit Bank Statement Loader Program and Capture Status of Submitted Programs

In this part, let us see the sample procedure to submit a bank statement loader program and capture the status of programs submitted as part of Bank Statement Import

Flowchart:

Sample Bank Statement Submission and capture 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;

 

Part1: Introduction and Graphical Representation of Bank Statement Import Process

Part 2: Step to implement Bank Statement Open Interface - Setups

Part 3: Table Details related to Open Interface Bank Statement

Part 4: Sample Script to submit Bank Statement loader program and capture the status of the concurrent programs

1 Responses to “Script to Submit Bank Statement Loader Program and Capture Status of Submitted Programs”

Amit said...
January 3, 2013 at 7:35 AM

what is "pin_request_id" as parameter, please explain , and also explain execution of script


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.