Thursday 2 May 2013

To run more the one concurrent in pl/sql package ....

 CREATE OR REPLACE PACKAGE APPS.AP_GTREASURY_TMS_PROC_PKG AS
 Procedure ap_reconciled_process_proc (
   errbuf out  VARCHAR2,
   retcode  out VARCHAR2);
 End;

      ===============   **********************  =====================

CREATE OR REPLACE PACKAGE BODY APPS.AP_GTREASURY_TMS_PROC_PKG
IS
 PROCEDURE ap_reconciled_process_proc (
      errbuf           OUT      VARCHAR2,
      retcode          OUT      VARCHAR2    
        )
   IS
      --
      lv_prog_name        VARCHAR2 (30)  := 'AP_GTREASURY_TMS'; 
      lv_prog_name1       VARCHAR2 (30)  := 'GTREASURY_TMS_TBL_CON';
      lv_app_short_name   VARCHAR2 (30)  := 'ELAAPP'; 
      custom_exception    EXCEPTION;
      lv_phase            VARCHAR2 (100);
      lv_status           VARCHAR2 (100);
      lv_dev_phase        VARCHAR2 (100);
      lv_dev_status       VARCHAR2 (100);
      lv_message          VARCHAR2 (200);
      lv_req_done         BOOLEAN;
      lv_req_done1        BOOLEAN;
      lv_request_id       NUMBER;
      lv_request_id1      NUMBER;
      lv_record_loaded    NUMBER;            
   --
   BEGIN
      --
      DBMS_OUTPUT.put_line ('Inside ap_checks_call'); 
      fnd_file.put_line (fnd_file.LOG, 'Inside ap_checks_call'); 
     

----------------------------------------------------
-- Submit Payables MasTec GTREASURY AP Loader
----------------------------------------------------
      fnd_file.put_line (fnd_file.LOG,
                         'Submit GTREASURY AP Loader'  --
                        );

                      
      lv_request_id1 :=
         fnd_request.submit_request
                        (lv_app_short_name,
                         lv_prog_name1,
                         'GTREASURY AP Loader'
                         ,''                                  
                         ,FALSE
                        );

      --
      IF lv_request_id1 = 0
      THEN                                                           
         --
         DBMS_OUTPUT.put_line ('Request not successfull');
         fnd_file.put_line (fnd_file.LOG, 'Request not successfull');
         RAISE custom_exception;
      --
      ELSE
         COMMIT;
         DBMS_OUTPUT.put_line (   'Request sucessful and the id is '
                               || TO_CHAR (lv_request_id1)
                              );
         fnd_file.put_line (fnd_file.LOG,
                               'Request sucessful and the id is '
                            || TO_CHAR (lv_request_id1)
                           );
         --
         lv_req_done1 :=
            fnd_concurrent.wait_for_request
                                      (lv_request_id1,
                                       3,
                                       (1000 * 60),
                                       lv_phase,
                                       lv_status,
                                       lv_dev_phase,
                                       lv_dev_status,
                                       lv_message
                                      );   
      END IF;                                                     
 
   
      SELECT COUNT (CHECK_NUMBER)
        INTO lv_record_loaded
        FROM GTREASURY_TMS_TBL
       WHERE PROCESS_FLAG = 'N';
    
      DBMS_OUTPUT.put_line (   'Total number of records loaded :- '
                            || lv_record_loaded
                           );
      fnd_file.put_line (fnd_file.LOG,
                            'Total number of records loaded :- '
                         || lv_record_loaded
                        );
                       
                   
        
        --
      IF NVL (lv_record_loaded, 0) != 0
      THEN                      
-----------------------------------------------------
-- Submit Payables MasTec GTREASURY AP Reconciled
-----------------------------------------------------
         DBMS_OUTPUT.put_line ('Submit GTREASURY AP Reconciled.');
         fnd_file.put_line (fnd_file.LOG,
                            'Submit GTREASURY AP Reconciled.'
                           );
         --     
        
           
       
         lv_request_id :=
            fnd_request.submit_request
                          (lv_app_short_name,
                           lv_prog_name,
                          'GTREASURY AP Reconciled',
                          '',
                          FALSE
                          );
                         
         --
         IF lv_request_id = 0
         THEN                                                          -- IF#2
            --
            DBMS_OUTPUT.put_line ('Request not successfull');
            fnd_file.put_line (fnd_file.LOG, 'Request not successfull');
            RAISE custom_exception;
         --
         ELSE
            COMMIT;
            DBMS_OUTPUT.put_line (   'Request sucessful and the id is '
                                  || TO_CHAR (lv_request_id)
                                 );
            fnd_file.put_line (fnd_file.LOG,
                                  'Request sucessful and the id is '
                               || TO_CHAR (lv_request_id)
                              );
            --
            lv_req_done :=
               fnd_concurrent.wait_for_request
                                      (lv_request_id,
                                       3,
                                       (1000 * 60),
                                       lv_phase,
                                       lv_status,
                                       lv_dev_phase,
                                       lv_dev_status,
                                       lv_message
                                      );
         --
         END IF;                                                  
      --
      --
      END IF;                   

      DBMS_OUTPUT.put_line (   'Request id for GTREASURY AP Reconciled :-'
                            || lv_request_id
                           );
      fnd_file.put_line (fnd_file.LOG,
                            'Request id for AP GTREASURY AP Reconciled :-'
                         || lv_request_id
                        );
      DBMS_OUTPUT.put_line ('End ap_checks_call');
      fnd_file.put_line (fnd_file.LOG, 'End ap_checks_call');
   --
   EXCEPTION
      WHEN custom_exception
      THEN
         DBMS_OUTPUT.put_line
            ('ERROR! Custom error In ap_checks_call of GTREASURY AP Reconciled : '
            );
         fnd_file.put_line
            (fnd_file.LOG,
             'ERROR! Custom error In ap_checks_call of  GTREASURY AP Reconciled : '
            );
      WHEN OTHERS
      THEN
         IF SQLCODE = -29913
         THEN
            --
            retcode := SQLCODE;
            DBMS_OUTPUT.put_line ('Data File Not found, ' || retcode);
            fnd_file.put_line (fnd_file.LOG,
                               'Data File Not found, ' || retcode
                              );
         --
         ELSE
            --
            DBMS_OUTPUT.put_line
                 (   'ERROR! In ap_checks_call of GTREASURY AP Reconciled : '
                  || SQLERRM
                 );
            fnd_file.put_line
                  (fnd_file.LOG,
                      'ERROR! In ap_checks_call of GTREASURY AP Reconciled : '
                   || SQLERRM
                  );
            retcode := 1;                                           -- Warning
         --
         END IF;

   END ap_reconciled_process_proc;
   END AP_GTREASURY_TMS_PROC_PKG;

No comments:

Post a Comment