       IDENTIFICATION DIVISION.                                         
       PROGRAM-ID.    CSR02370.                                         
       DATE-WRITTEN. 08/31/98.                                          
       DATE-COMPILED.                                                   
                                                                        
CVT000******************************************************************00000100
CVT000*     - - - - - >   R P C   C O N V E R S I O N   < - - - - -    *00000200
CVT000*         S370 CONVERTED TO DB2 SP CSR02370 ON 12/06/2006        *00000300
CVT000*             -NOTE: AUTOMATED CONVERSION PROGRAM USED           *00000400
CVT000*                                                                *00000500
CVT000*                    CVT000 - CONVERTED CODE                     *00000501
CVT000*                    CVT004 - NEEDED REVIEW                      *00000502
CVT000*                    CVT999 - COMMENTED OUT CODE THAT CAN        *00000503
CVT000*                             POTENTIALLY BE DELETED             *00000510
CVT000*                                                                *00000520
CVT000******************************************************************00000600
      ******************************************************************00060000
      *                                                                *00070000
      *                SOUTH CAROLINA ELECTRIC & GAS                   *00080000
      *                                                                *00090000
      *  THIS PROGRAM IS EXECUTED VIA A REMOTE PROCEDURE CALL (RPC).   *00100000
      *                                                                *00110000
      *  TRANID:        S370                                           *00120000
      *  PROGRAM:       S370                                           *00130000
      *  CALLING SP:    PA_S370                                        *00140000
      *                                                                *00150000
      ******************************************************************00160000
      *                 P R O G R A M  S U M M A R Y                   *00170000
      *                                                                *00180000
      *  THIS PROCEDURE IS USED TO DETERMINE IF A GIVEN ACCOUNT HAS    *00190000
      *  CANCEL/REBILL TRANSACTION HISTORY WITHIN THE LAST TWO MONTHS. *00200000
      *  IT WILL BE CALLED FROM THE IVR.                               *00200000
      *                                                                *00210000
      ******************************************************************00220000
      *                                                                *00230000
      *                     PROGRAM MODIFICATION LOG                   *00240000
      *                                                                *00250000
      *    DATE    INITIALS   COMMENTS                                 *00260000
      *  --------  --------   ---------------------------------------  *00270000
      *  08/31/98    FB       PROCEDURE ORIGINALLY CODED.              *00280000
CBSI  *  01/04/98    CBSI     ABEND LOG MODIFIED TO INCLUDE ALL THE    *        
CBSI  *              MADRAS   ABEND PARAMETERS                         *        
C36250*  09/25/07    JC91900  ADDED SMART SCREEN APPL ID TO QUERY.     *        
C03082*  10/26/12    DB41297  OPTIMIZE WHERE QUERY.                    *        
A04527*  06/06/13    MC95456  REMOVED UNUSED COPYBOOK CWS00056         *        
      ******************************************************************00220000
                                                                        
       ENVIRONMENT DIVISION.                                            
       DATA DIVISION.                                                   
       WORKING-STORAGE SECTION.                                         

MSQ002  01 MFA-CSRERLOG.
MSQ002    05 ARG-5 PIC X(255).
MSQ002    05 ARG-6 PIC X(255).
MSQ002    05 ARG-7 PIC X(447).

MSQ017 01 MFA-PROGRAM-ID PIC X(8) VALUE 'CSR02370'.
MSQ017     COPY MFASQLM.
                                                                        
           EXEC SQL                                                     01790000
              INCLUDE SQLCA                                             01800000
           END-EXEC.                                                    01810000
                                                                        
           EXEC SQL                                                     02980000
              INCLUDE TBMNHIST                                          02990000
           END-EXEC.                                                    03000000
                                                                        
      ******************************************************************03140000
      *    COMMON COPYBOOKS                                            *03150000
      ******************************************************************03160000
                                                                        
           COPY CCA00001.                                               03080000
           COPY CWS00027.                                               03100000
           COPY CWS00303.                                               03120000
                                                                        
      ******************************************************************03140000
      *    WORK AREAS                                                  *03150000
      ******************************************************************03160000
                                                                        
       01  GW-LIB-MISC-FIELDS.                                          
           05  GWL-STATUS-SUBCODE           PIC S9(9) COMP.             
                                                                        
       01  MESSAGE-FIELDS.                                              
           05  MSG-TYPE                     PIC S9(9) COMP.             
           05  MSG-SEVERITY                 PIC S9(9) COMP VALUE 11.    
           05  MSG-NR                       PIC S9(9) COMP VALUE  2.    
           05  MSG-RPC                      PIC X(4) VALUE 'S370'.      
           05  MSG-RPC-L                    PIC S9(9) COMP.             
           05  MSG-TEXT                     PIC X(100).                 
           05  MSG-TEXT-L                   PIC S9(9) COMP.             
           05  MSG-SQL-ERROR.                                           
               10  FILLER PIC X(10) VALUE 'SQLCODE = '.                 
               10  MSG-SQL-ERROR-C          PIC -9(3) DISPLAY.          
               10  FILLER PIC X(16) VALUE ', ERROR TOKENS: '.           
               10  MSG-SQL-ERROR-K          PIC X(70).                  
               10  MSG-SQL-ERROR-K-CHARS                                
                                            REDEFINES MSG-SQL-ERROR-K   
                                            OCCURS 70 TIMES             
                                            PIC X.                      
           05  MSG-SQL-ERROR-SS             PIC S9(4) COMP.             
                                                                        
      ******************************************************************03500000
      *    INPUT PARAMETERS                                            *03510000
      ******************************************************************03520000
                                                                        
       01  PARM-FIELDS.                                                 
           05  PARM-L                       PIC S9(9) COMP.             
           05  PARM-ACCOUNT                 PIC X(13).                  
                                                                        
       77  WS-BLO-LOT-SPACES                PIC X(05) VALUE SPACES.     
       01  SNA-FIELDS.                                                  
           05  SNA-SUBC                     PIC S9(9) COMP.             
                                                                        
       01  WORK-FIELDS.                                                 
           05  WRK-DONE-STATUS              PIC S9(9) COMP.             
                                                                        
       01  MISC.                                                        
           05  PROGRAM-NAME                 PIC X(8) VALUE 'S370'.      
           05  WS-NULL-IND                  PIC S9(4) COMP VALUE 0.     
           05  CTR-ROWS                     PIC S9(9) COMP VALUE 0.     
           05  WS-SUB                       PIC S9(8) COMP VALUE ZERO.  
           05  WS-TODAYS-DATE               PIC X(10) VALUE SPACES.     
           05  WS-TWO-MONTHS-AGO            PIC X(10) VALUE SPACES.     
           05  WS-CANCEL-REBILL-COUNT       PIC S9(4) COMP  VALUE 0.    
                                                                        
      ******************************************************************04050000
      *    RESULT SET FIELDS                                           *04060000
      ******************************************************************04070000
                                                                        
       01  GTT-RETURN-FIELDS.                                           
           05  RS-RETURN-CODE               PIC S9(9) COMP.             
           05  RS-CR-FLAG                   PIC X.                      
       01  RS-ERROR-DESC                PIC X(40) VALUE SPACES.         
                                                                        
      ******************************************************************04470000
      *    RESULT SET COLUMN NAMES                                     *04480000
      ******************************************************************04490000
                                                                        
       01  SWITCHES.                                                    
           05  ALL-DONE-SW                  PIC X VALUE 'N'.            
               88  NOT-ALL-DONE             VALUE 'N'.                  
               88  ALL-DONE                 VALUE 'Y'.                  
           05  SEND-DONE-SW                 PIC X VALUE 'Y'.            
               88  SEND-DONE-ERROR          VALUE 'N'.                  
               88  SEND-DONE-OK             VALUE 'Y'.                  
                                                                        
CVT000     EXEC SQL                                                     00000010
CVT000         INCLUDE CWSX0010                                         00000020
CVT000     END-EXEC.                                                    00000030
CVT000                                                                  
CVT000 01  CSRERLOG-P.                                                  
CVT000     10  S-SP-NAME                 PIC X(18) VALUE SPACES.        
CVT000     10  S-SQLCODE                 PIC S9(9) COMP VALUE 0.        
CVT000     10  S-SQLSTATE                PIC X(5)  VALUE ' '.           
CVT000     10  S-TABLE-NAME              PIC X(18) VALUE SPACES.        
CVT000     10  S-HOST-VARIABLES.                                        
CVT000         49  S-HOST-VARIABLES-L    PIC S9(4) USAGE COMP.          
CVT000         49  S-HOST-VARIABLES-V    PIC X(255).                    
CVT000     10  S-SQL-STATEMENT.                                         
CVT000         49  S-SQL-STATEMENT-L     PIC S9(4) USAGE COMP.          
CVT000         49  S-SQL-STATEMENT-V     PIC X(255).                    
CVT000     10  S-SQL-DESCRIPTION.                                       
CVT000         49  S-SQL-DESCRIPTION-L   PIC S9(4) USAGE COMP.          
CVT000         49  S-SQL-DESCRIPTION-V   PIC X(255).                    
CVT000     10  WS-ABEND-SQLERRMC.                                       
CVT000         49  WS-ABEND-SQLERRMC-L   PIC S9(4) USAGE COMP.          
CVT000         49  WS-ABEND-SQLERRMC-V   PIC X(255).                    
CVT000     10  S-RETURN-CODE             PIC S9(9) COMP VALUE 0.        
CVT000     10  WS-SQLSTATE               PIC X(05) VALUE SPACES.        
CVT000                                                                  
CVT000 LINKAGE SECTION.                                                 
CVT000 01  LINK-ACCOUNT             PIC X(13)                .          
                                                                        
CVT000 PROCEDURE DIVISION USING                                         
CVT000          LINK-ACCOUNT                                            
CVT000         .                                                        
CVT000     EXEC SQL                                                     
CVT000         WHENEVER SQLWARNING                                      
CVT000             CONTINUE                                             
CVT000     END-EXEC.                                                    
CVT000                                                                  
CVT000     EXEC SQL                                                     
CVT000         WHENEVER SQLERROR                                        
CVT000             CONTINUE                                             
CVT000     END-EXEC.                                                    
CVT000                                                                  
CVT000     EXEC SQL                                                     
CVT000         WHENEVER NOT FOUND                                       
CVT000             CONTINUE                                             
CVT000     END-EXEC.                                                    
                                                                        
       0000-MAINLINE.                                                   
                                                                        
           PERFORM 0100-INITIALIZE     THRU 0100-EXIT.                  
           PERFORM 2000-PROCESS-OUTPUT THRU 2000-EXIT.                  
           PERFORM 9999-END-PROGRAM    THRU 9999-EXIT.                  
                                                                        
       0000-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************11940000
      * 0100-INITIALIZE                                                *11950000
      *                                                                *11960000
      *     1. RESET DB2 ERROR HANDLERS                                *11970000
      *     2. ESTABLISH GATEWAY ENVIRONMENT (TDINIT)                  *11980000
      *     3. ACCEPT CLIENT REQUEST/ESTABLISH SNA HANDLE (TDACCEPT)   *11990000
      *     4. DETERMINE IF CLIENT HAS SENT AN RPC PARAMETER (TDRESULT)*12000000
      *                                                                *12010000
      ******************************************************************12020000
                                                                        
       0100-INITIALIZE.                                                 
                                                                        
CVT000     EXEC SQL                                                     
CVT000       DECLARE C1 CURSOR  FOR                          
CVT000       SELECT                                                     
CVT000        :RS-RETURN-CODE              AS    RETURN_CODE            
CVT000       ,:RS-CR-FLAG                  AS    CR_FLAG                
CVT000          FROM CIS.SYSDUMMY1                                   
CVT000     END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ027
MFA-TR*    EXEC SQL                                                             
MFA-TR*      DECLARE C1 CURSOR WITH RETURN FOR                                  
MFA-TR*      SELECT                                                             
MFA-TR*       :RS-RETURN-CODE              AS    RETURN_CODE                    
MFA-TR*      ,:RS-CR-FLAG                  AS    CR_FLAG                        
MFA-TR*         FROM SYSIBM.SYSDUMMY1                                           
MFA-TR*    END-EXEC.                                                            
CVT000     MOVE LINK-ACCOUNT              TO PARM-ACCOUNT             . 
       0100-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************16250000
      * 2000-PROCESS-OUTPUT                                            *16260000
      *                                                                *16270000
      *     DESCRIBE EACH COLUMN IN THE RESULT SET.                    *16280000
      *     BUILD THE RESULT SET.                                      *16290000
      *                                                                *16300000
      ******************************************************************16310000
                                                                        
       2000-PROCESS-OUTPUT.                                             
                                                                        
           IF PARM-ACCOUNT NUMERIC                                      
               MOVE PARM-ACCOUNT TO MH-ACCOUNT-NO                       
           ELSE                                                         
               MOVE 200 TO RS-RETURN-CODE                               
               PERFORM 8100-SEND-RESULT THRU 8100-EXIT                  
               GO TO 2000-EXIT
           END-IF.                                         
                                                                        
           EXEC SQL                                                     
              SELECT
              CAST(SYSDATETIMEOFFSET() AS DATE)
            INTO
              :WS-TODAYS-DATE                        
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ006
MFA-TR* MSQ028
MFA-TR*    EXEC SQL                                                             
MFA-TR*       SET :WS-TODAYS-DATE = CURRENT DATE                                
MFA-TR*    END-EXEC.                                                            

MSQ017     CALL "MFASQLCA"
MSQ017       USING SQLCA, BY VALUE MFA-PROGRAM-ID, "0", "0", "0",
MSQ017       BY REFERENCE MFSQLMESSAGETEXT
MSQ017     END-CALL

           MOVE SQLCODE                       TO WS-ACTIVE-RETURN-CODE. 
           IF WS-ACTIVE-RETURN-CODE EQUAL     TO SUCCESSFUL-CALL        
              NEXT SENTENCE                                             
           ELSE                                                         
              MOVE WS-ACTIVE-RETURN-CODE      TO RS-RETURN-CODE         
              MOVE PROGRAM-NAME               TO ABEND-PROGRAM          
CBSI          MOVE '2000'                     TO ACTIVE-PARAGRAPH       
              MOVE 'SELECT'                   TO ABEND-FUNCTION         
              MOVE SPACES                     TO ABEND-SQL-PREDICATES   
                                                 ABEND-TABLES           
              MOVE 'SYSTABLE     '            TO TABLE-1                
              MOVE 'ACCOUNT_NO'               TO TABLE-ELEMENT-1        
              MOVE MH-ACCOUNT-NO              TO HOSTVAR-ELEMENT-1      
              PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT            
              PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT            
           END-IF.                                                      
                                                                        
           EXEC SQL                                                     
              SELECT
              DATEADD( MONTH, -2, CAST(SYSDATETIMEOFFSET() AS DATE) )
            INTO
              :WS-TWO-MONTHS-AGO          
           END-EXEC                                                     

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ006
MFA-TR* MSQ028
MFA-TR* MSQ044
MFA-TR*    EXEC SQL                                                             
MFA-TR*       SET :WS-TWO-MONTHS-AGO = CURRENT DATE - 2 MONTHS                  
MFA-TR*    END-EXEC                                                             

MSQ017     CALL "MFASQLCA"
MSQ017       USING SQLCA, BY VALUE MFA-PROGRAM-ID, "0", "0", "0",
MSQ017       BY REFERENCE MFSQLMESSAGETEXT
MSQ017     END-CALL

                                                                        
           MOVE SQLCODE TO WS-ACTIVE-RETURN-CODE                        
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
              NEXT SENTENCE                                             
           ELSE                                                         
              MOVE WS-ACTIVE-RETURN-CODE      TO RS-RETURN-CODE         
              MOVE PROGRAM-NAME               TO ABEND-PROGRAM          
              MOVE 'SELECT'                   TO ABEND-FUNCTION         
CBSI          MOVE '2000'                     TO ACTIVE-PARAGRAPH       
              MOVE SPACES                     TO ABEND-SQL-PREDICATES   
                                                 ABEND-TABLES           
              MOVE 'SYSTABLE     '            TO TABLE-1                
              MOVE 'ACCOUNT_NO'               TO TABLE-ELEMENT-1        
              MOVE MH-ACCOUNT-NO              TO HOSTVAR-ELEMENT-1      
              PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT            
              PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT            
           END-IF.                                                      
                                                                        
           EXEC SQL                                                     
             SELECT COUNT(*)                                            
               INTO :WS-CANCEL-REBILL-COUNT                             
             FROM CSS_MNT_TRANS_HIST WITH(READUNCOMMITTED)                      
             WHERE ACCOUNT_NO = :MH-ACCOUNT-NO                          
              AND APPL_PROGRAM_ID = 'PANEL321'                          
A00231        AND CAST(TRANS_HIST_SEQ_NO AS DATE) BETWEEN 
              IIF(TRY_CONVERT(DATE, :WS-TWO-MONTHS-AGO
              ) IS NULL OR (PATINDEX('%.%', :WS-TWO-MONTHS-AGO
              ) <> 0) OR (LEN(:WS-TWO-MONTHS-AGO
              ) <> 10), CIS.CHAR2DATE(:WS-TWO-MONTHS-AGO
              ), CONVERT(DATE, :WS-TWO-MONTHS-AGO) )    
A00231        AND CAST(SYSDATETIMEOFFSET() AS DATE)                             
C36250                                                           
C36250                                                      
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ022
MFA-TR* MSQ028
MFA-TR* MSQ029
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*      SELECT COUNT(*)                                                    
MFA-TR*        INTO :WS-CANCEL-REBILL-COUNT                                     
MFA-TR*      FROM CSS_MNT_TRANS_HIST                                            
MFA-TR*      WHERE ACCOUNT_NO = :MH-ACCOUNT-NO                                  
MFA-TR*       AND APPL_PROGRAM_ID = 'PANEL321'                                  
MFA-TR*       AND DATE(TRANS_HIST_SEQ_NO) BETWEEN :WS-TWO-MONTHS-AGO            
MFA-TR*       AND CURRENT DATE                                                  
MFA-TR*       WITH UR                                                           
MFA-TR*       QUERYNO 2000                                                      
MFA-TR*    END-EXEC.                                                            

MSQ017     CALL "MFASQLCA"
MSQ017       USING SQLCA, BY VALUE MFA-PROGRAM-ID, "0", "0", "0",
MSQ017       BY REFERENCE MFSQLMESSAGETEXT
MSQ017     END-CALL

                                                                        
           MOVE SQLCODE TO WS-ACTIVE-RETURN-CODE                        
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
              NEXT SENTENCE                                             
           ELSE                                                         
              MOVE WS-ACTIVE-RETURN-CODE      TO RS-RETURN-CODE         
              MOVE PROGRAM-NAME               TO ABEND-PROGRAM          
              MOVE 'SELECT'                   TO ABEND-FUNCTION         
CBSI          MOVE '2000'                     TO ACTIVE-PARAGRAPH       
              MOVE SPACES                     TO ABEND-SQL-PREDICATES   
                                                 ABEND-TABLES           
              MOVE 'CSS_MNT_TRANS_HIST'       TO TABLE-1                
              MOVE 'ACCOUNT_NO'               TO TABLE-ELEMENT-1        
CBSI          MOVE 'DATE_TRANS'               TO TABLE-ELEMENT-2        
CBSI          MOVE 'DATE_TRANS'               TO TABLE-ELEMENT-3        
              MOVE MH-ACCOUNT-NO              TO HOSTVAR-ELEMENT-1      
CBSI          MOVE WS-TWO-MONTHS-AGO          TO HOSTVAR-ELEMENT-2      
CBSI          MOVE WS-TODAYS-DATE             TO HOSTVAR-ELEMENT-3      
              PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT            
              PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT            
           END-IF.                                                      
                                                                        
           INITIALIZE GTT-RETURN-FIELDS.                                
                                                                        
           MOVE 0 TO RS-RETURN-CODE.                                    
           IF WS-CANCEL-REBILL-COUNT > 0                                
               MOVE 'Y' TO RS-CR-FLAG                                   
           ELSE                                                         
               MOVE 'N' TO RS-CR-FLAG
           END-IF.                                  
                                                                        
           PERFORM 8100-SEND-RESULT THRU 8100-EXIT.                     
                                                                        
       2000-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************57510000
      * 9900- JOURNALING / ERROR HANDLING INCLUDE *                     57520000
      ******************************************************************57530000
           EXEC SQL                                                     57540000
              INCLUDE CPDSP300                                          57550000
           END-EXEC.                                                    57560000
                                                                        
      ******************************************************************57580000
      *       END PROGRAM COPYLIB                                      *57590000
      ******************************************************************57600000
CVT000 8100-SEND-RESULT.                                                
CVT000       ADD 1 TO CTR-ROWS.                                         
CVT000 8100-EXIT.                                                       
CVT000        EXIT.                                                     
CVT000     EXEC SQL                                                     00000100
CVT000         INCLUDE CPD00321                                         00000200
CVT000     END-EXEC.                                                    00000300
