       IDENTIFICATION DIVISION.                                         
       PROGRAM-ID. CSR04087.                                            
       DATE-WRITTEN. 01/11/10.                                          
       DATE-COMPILED.                                                   
                                                                        
      ******************************************************************        
      *                                                                *        
      *                SOUTH CAROLINA ELECTRIC & GAS                   *        
      *                                                                *        
      *                                                                *        
      ******************************************************************        
      *                 P R O G R A M  S U M M A R Y                   *        
      *                                                                *        
      *  THIS PROCEDURE GETS ALL THE THE PREMISES THAT HAVE A SERVICE  *        
      *  AGREEMENT.                                                    *        
      ******************************************************************        
      *                                                                *        
      *                     PROGRAM MODIFICATION LOG                   *        
      *                                                                *        
      *    DATE    INITIALS   COMMENTS                                 *        
      *  --------  --------   ---------------------------------------  *        
PRJ104*  01/11/10    PK       PROCEDURE ORIGINALLY CODED.              *        
A2382 *  04/01/10    PK       CHANGES ADDED TO IMPROVE PERFORMANCE     *        
A2382 *                       IN SERV_AGREE_CUR                        *        
      ******************************************************************        
      ******************************************************************        
      *                                                                *        
      *                ---- BASIC SEQUENCE STRUCTURE ----              *        
      *                                                                *        
      *  0000 - 0999  MAIN CONTROL PATH AND INITIALIZATION             *        
      *  1000 - 1999  INPUT PROCESSING CONTROL PATH                    *        
      *  2000 - 2999  OUTPUT PROCESSING CONTROL PATH                   *        
      *  3000 - 4999  NOT USED                                         *        
      *  5000 - 5999  COMMON PROGRAM MODULES                           *        
      *  6000 - 6999  COMMON SYSTEM MODULES                            *        
      *  7000 - 7999  INPUT MODULES                                    *        
      *  8000 - 8999  OUTPUT MODULES                                   *        
      *  9000 - 9999  TERMINATION,ABEND, MESSAGING MODULES             *        
      *                                                                *        
      ******************************************************************        
                                                                        
       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 'CSR04087'.
MSQ017     COPY MFASQLM.
                                                                        
           EXEC SQL                                                             
              INCLUDE SQLCA                                                     
           END-EXEC.                                                            
                                                                        
      ******************************************************************        
      *    DQ - CSS_NAME                                               *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBNAME                                                    
           END-EXEC.                                                            
      ******************************************************************        
      *    DY - CSS_ADDR_FORMATTED                                     *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBADRFMT                                                  
           END-EXEC.                                                            
      ******************************************************************        
      *    PR - CSS_PREMISE                                            *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBPREM                                                    
           END-EXEC.                                                            
      ******************************************************************        
      *    AT - CSS_ACCOUNT                                            *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBACCT                                                    
           END-EXEC.                                                            
      ******************************************************************        
      *    CU - CSS_CUSTOMER                                           *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBCUST                                                    
           END-EXEC.                                                            
      ******************************************************************        
      *    Y0 - CSS_PREM_GAS_LINE                                      *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBPREMLN                                                  
           END-EXEC.                                                            
      ******************************************************************        
      *    Y0 - CSS_GAS_SERV_LINE                                      *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBGASSRV                                                  
           END-EXEC.                                                            
      ******************************************************************        
      *    DZ - CSS_ADDR_FREEFORM                                      *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBADRFRE                                                  
           END-EXEC.                                                            
      ******************************************************************        
      *    A4 - CSS_ZIP_CODE                                           *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBZIPCD                                                   
           END-EXEC.                                                            
      ******************************************************************        
      *    VO - CSS_SO_DATA                                            *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBSODAT                                                   
           END-EXEC.                                                            
      ******************************************************************        
      *    YV - HST_SO_DATA                                            *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBHSTDAT                                                  
           END-EXEC.                                                            
      ******************************************************************        
      *  VI   - CSS_SO_ORDER_INST                                      *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBSOODR                                                   
           END-EXEC.                                                            
      ******************************************************************        
      *  ZG   - HST_SO_ORDER_INST                                      *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBHSTODR                                                  
           END-EXEC.                                                            
      ******************************************************************        
      *    B1 - CSS_LOCAL_OFFICE                                       *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBLOCOFC                                                  
           END-EXEC.                                                            
      ******************************************************************        
      *    CT - CSS_CONTRACT                                           *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBCNTRCT                                                  
           END-EXEC.                                                            
      ******************************************************************        
      *    PF - CSS_USER_PROFILE                                       *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBUSRPRF                                                  
           END-EXEC.                                                            
      ******************************************************************        
      *    COMMON COPYLIBS                                             *        
      ******************************************************************        
                                                                        
           COPY CCA00001.                                                       
           COPY CWS00027.                                                       
           COPY CWS00303.                                                       
           COPY CWS00132.                                                       
                                                                        
           EXEC SQL                                                             
              INCLUDE CWSX0010                                                  
           END-EXEC.                                                            
                                                                        
      ******************************************************************        
      *    WORK AREAS                                                  *        
      ******************************************************************        
                                                                        
                                                                        
       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(8)  VALUE 'CSR04087'. 
           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.             
                                                                        
      ******************************************************************        
      *    INPUT PARAMETERS                                            *        
      ******************************************************************        
                                                                        
       01  PARM-FIELDS.                                                 
           05  PARM-L                       PIC S9(9) COMP.             
           05  PARM-ID1                     PIC S9(9) COMP VALUE 1.     
*********  05  PARM-PREMISE-NO              PIC S9(10)V USAGE COMP-3.           
                                                                        
       01  SNA-FIELDS.                                                  
           05  SNA-SUBC                     PIC S9(9) COMP.             
           05  SNA-CONNECTION-NAME          PIC X(8)  VALUE SPACE.      
                                                                        
       01  COUNTER-FIELDS.                                              
           05  CTR-COLUMN                   PIC S9(9) COMP VALUE 1.     
           05  CTR-ROWS                     PIC S9(9) COMP VALUE 0.     
                                                                        
       01  WS-SUB                           PIC S9(8) COMP VALUE ZERO.  
                                                                        
       01  WORK-FIELDS.                                                 
           05  MAX-LENGTH-PARM              PIC S9(9) COMP.             
           05  WRKLEN1                      PIC S9(9) COMP.             
           05  WRKLEN2                      PIC S9(9) COMP.             
           05  WRK-DONE-STATUS              PIC S9(9) COMP.             
                                                                        
      ******************************************************************        
      *    RESULT SET FIELDS                                           *        
      ******************************************************************        
                                                                        
       01  GTT-RETURN-FIELDS.                                           
           05 RS-RETURN-CODE               PIC S9(9) COMP VALUE 0.      
           05 RS-PREMISE-NO                PIC S9(10)V USAGE COMP-3     
                                           VALUE 0.                     
           05 RS-SERVICE-NO                PIC S9(10)V USAGE COMP-3     
                                           VALUE 0.                     
           05 RS-SERV-AGREE-AMT            PIC S9(9)V99 USAGE COMP-3    
                                           VALUE 0.                     
           05 RS-SERV-AGREE-DT             PIC X(10) VALUE SPACES.      
           05 RS-SERV-AGREE-STATUS         PIC X(1) VALUE SPACES.       
           05 RS-CUSTOMER-NAME             PIC X(50) VALUE SPACES.      
           05 RS-MKT-REP-NAME              PIC X(50) VALUE SPACES.      
           05 RS-ADDRESS                   PIC X(100) VALUE SPACES.     
           05 RS-ADDRESS-TITLE             PIC X(80) VALUE SPACES.      
           05 RS-LOCAL-OFFICE              PIC X(03) VALUE SPACES.      
           05 RS-LOCAL-OFFICE-DESC         PIC X(22) VALUE SPACES.      
           05 RS-SERV-AGREE-ACCT-NO        PIC S9(13)V USAGE COMP-3     
                                           VALUE 0.                     
           05 RS-SET-ORDER-STATE           PIC X(1)  VALUE SPACES.      
           05 RS-CNT-STATUS-CD             PIC X(1) VALUE SPACES.       
           05 RS-CNT-ITEM-ID               PIC S9(9) USAGE COMP         
                                               VALUE 0.                 
           05 RS-CNT-DATE                  PIC X(10) VALUE SPACES.      
           05 RS-SERV-AGREE-COMMENT-TEXT   PIC X(255).                  
           05 RS-SERV-AGREE-STAT-DESC      PIC X(15).                   
           05 RS-CNT-STAT-DESC             PIC X(15).                   
                                                                        
                                                                        
                                                                        
                                                                        
       01  CSRERLOG-P.                                                  
            10  S-SP-NAME               PIC X(18)      VALUE SPACES.    
            10  S-SQLCODE               PIC S9(9)      COMP VALUE 0.    
            10  S-SQLSTATE              PIC X(5)       VALUE ' '.       
            10  S-TABLE-NAME            PIC X(18)      VALUE SPACES.    
            10  S-HOST-VARIABLES.                                       
                49  S-HOST-VARIABLES-L  PIC S9(4)      USAGE COMP.      
                49  S-HOST-VARIABLES-V  PIC X(255).                     
            10  S-SQL-STATEMENT.                                        
                49  S-SQL-STATEMENT-L   PIC S9(4)      USAGE COMP.      
                49  S-SQL-STATEMENT-V   PIC X(255).                     
            10  S-SQL-DESCRIPTION.                                      
                49  S-SQL-DESCRIPTION-L PIC S9(4)      USAGE COMP.      
                49  S-SQL-DESCRIPTION-V PIC X(255).                     
                                                                        
       01  WS-MISC-FIELDS.                                              
           05  WS-PREMISE-NO           PIC S9(10)V USAGE COMP-3         
                                       VALUE 0.                         
           05  WS-ADDRESS              PIC X(100) VALUE SPACES.         
           05  WS-ADDRESS-TITLE        PIC X(80) VALUE SPACES.          
           05  WS-CUSTOMER-NAME        PIC X(50) VALUE SPACES.          
           05  WS-MKT-REP-NAME         PIC X(50) VALUE SPACES.          
           05  WS-FULL-NAME            PIC X(50) VALUE SPACES.          
           05  WS-NAME                 PIC X(50) VALUE SPACES.          
           05  WS-START-AGR-STATUS     PIC X(01) VALUE SPACES.          
           05  WS-END-AGR-STATUS       PIC X(01) VALUE 'Z'.             
           05  WS-START-AGR-DT         PIC X(10) VALUE SPACES.          
           05  WS-END-AGR-DT           PIC X(10) VALUE SPACES.          
           05  WS-COMPANY-NO           PIC X(02) VALUE SPACES.          
           05  WS-START-LOC-OFF        PIC X(03) VALUE '000'.           
           05  WS-END-LOC-OFF          PIC X(03) VALUE '999'.           
           05  WS-SQLSTATE             PIC X(05) VALUE SPACES.          
           05  PROGRAM-NAME            PIC X(08) VALUE 'CSR04087'.      
           05  WS-CNT-DATE             PIC X(10) VALUE SPACES.          
           05  WS-SERV-AGREE-DT        PIC X(10) VALUE SPACES.          
           05  S-RETURN-CODE           PIC S9(9) COMP.                  
           05  WS-CUSTOMER-NO-NI       PIC S9(04) COMP VALUE 0.         
           05  WS-SERV-AGREE-AMT-NI    PIC S9(04) COMP VALUE 0.         
           05  WS-SERV-AGREE-DT-NI     PIC S9(04) COMP VALUE 0.         
           05  WS-IS-SET-PENDING       PIC X(01) VALUE 'N'.             
           05  WS-CNT-STAT-DESC        PIC X(15) VALUE SPACES.          
           05  WS-SERV-AGREE-STAT-DESC PIC X(15) VALUE SPACES.          
           05  WS-SERV-AGREE-COMMENT.                                   
              49 WS-SERV-AGREE-COMMENT-LEN   PIC S9(4) USAGE COMP.      
              49 WS-SERV-AGREE-COMMENT-TEXT  PIC X(255).                
                                                                        
       LINKAGE SECTION.                                                 
COB305 01 PARM-ACCOUNT-NO        PIC S9(13)V USAGE COMP-3 VALUE 0.     
       01       PARM-COMPANY-NO           PIC X(02).                    
       01       PARM-LOCAL-OFFICE         PIC X(03).                    
       01       PARM-SERV-AGREE-STATUS    PIC X(1).                     
       01       PARM-START-DATE           PIC X(10).                    
       01       PARM-END-DATE             PIC X(10).                    
       01       PARM-SET-PENDING-FL       PIC X(01).                    
       01       PARM-CNT-STATUS-CD        PIC X(01).                    
      ******************************************************************        
      *    SERV_LINE_CUR IS USED TO SEARCH SERVICE LINE DETAILS        *        
      ******************************************************************        
                                                                        
           EXEC SQL DECLARE SERV_AGREE_CUR CURSOR FOR                   
            SELECT Y0.PREMISE_NO                                        
                  ,Y0.SERVICE_NO                                        
                  ,Y0.CUSTOMER_NO                                       
                  ,Y0.SALES_REP_ID                                      
                  ,Y0.SERV_AGREE_AMT                                    
                  ,Y0.SERV_AGREE_DT                                     
                  ,Y0.SERV_AGREE_STAT_CD                                
                  ,Y0.SERV_AGREE_ACCT_NO                                
                  ,Y0.SERV_AGREE_ITEM_ID                                
                  ,Y0.SERV_AGREE_COMMENT                                
                  ,PR.LOCAL_OFFICE                                      
                  ,B1.LOCAL_OFFICE_DESC                                 
                  ,PR.ADDRESS_ID                                        
            FROM                                                        
                 CSS_PREM_GAS_LINE  Y0 WITH(READUNCOMMITTED)                    
                ,CSS_PREMISE        PR WITH(READUNCOMMITTED)                    
                ,CSS_LOCAL_OFFICE   B1 WITH(READUNCOMMITTED)                    
            WHERE                                                       
               Y0.SERV_AGREE_STAT_CD >= :WS-START-AGR-STATUS  AND       
               Y0.SERV_AGREE_STAT_CD <= :WS-END-AGR-STATUS    AND       
               Y0.SERV_AGREE_DT      >= IIF(TRY_CONVERT(DATE, 
                                                       :WS-START-AGR-DT
              ) IS NULL OR (PATINDEX('%.%', :WS-START-AGR-DT
              ) <> 0) OR (LEN(:WS-START-AGR-DT) <> 10), CIS.CHAR2DATE(
                                                       :WS-START-AGR-DT
              ), CONVERT(DATE, :WS-START-AGR-DT) ) AND      
               Y0.SERV_AGREE_DT      <= IIF(TRY_CONVERT(DATE, 
                                                         :WS-END-AGR-DT
              ) IS NULL OR (PATINDEX('%.%', :WS-END-AGR-DT
              ) <> 0) OR (LEN(:WS-END-AGR-DT) <> 10), CIS.CHAR2DATE(
                                                         :WS-END-AGR-DT
              ), CONVERT(DATE, :WS-END-AGR-DT) )  AND       
               PR.PREMISE_NO          = Y0.PREMISE_NO        AND        
               B1.COMPANY_NO          = :WS-COMPANY-NO        AND       
               PR.LOCAL_OFFICE       >= :WS-START-LOC-OFF     AND       
               PR.LOCAL_OFFICE       <= :WS-END-LOC-OFF       AND       
               B1.LOCAL_OFFICE        = PR.LOCAL_OFFICE      AND        
A2382          Y0.SERV_AGREE_DT       > IIF(TRY_CONVERT(DATE, 
              '1900-01-01') IS NULL OR (PATINDEX('%.%', '1900-01-01'
              ) <> 0) OR (LEN('1900-01-01') <> 10), CIS.CHAR2DATE(
              '1900-01-01'), CONVERT(DATE, '1900-01-01') )                    
            FOR READ ONLY                                       
         END-EXEC.                                                      

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ004
MFA-TR* MSQ022
MFA-TR* MSQ029
MFA-TR* MSQ054
MFA-TR*    EXEC SQL DECLARE SERV_AGREE_CUR CURSOR FOR                           
MFA-TR*     SELECT Y0.PREMISE_NO                                                
MFA-TR*           ,Y0.SERVICE_NO                                                
MFA-TR*           ,Y0.CUSTOMER_NO                                               
MFA-TR*           ,Y0.SALES_REP_ID                                              
MFA-TR*           ,Y0.SERV_AGREE_AMT                                            
MFA-TR*           ,Y0.SERV_AGREE_DT                                             
MFA-TR*           ,Y0.SERV_AGREE_STAT_CD                                        
MFA-TR*           ,Y0.SERV_AGREE_ACCT_NO                                        
MFA-TR*           ,Y0.SERV_AGREE_ITEM_ID                                        
MFA-TR*           ,Y0.SERV_AGREE_COMMENT                                        
MFA-TR*           ,PR.LOCAL_OFFICE                                              
MFA-TR*           ,B1.LOCAL_OFFICE_DESC                                         
MFA-TR*           ,PR.ADDRESS_ID                                                
MFA-TR*     FROM                                                                
MFA-TR*          CSS_PREM_GAS_LINE  Y0                                          
MFA-TR*         ,CSS_PREMISE        PR                                          
MFA-TR*         ,CSS_LOCAL_OFFICE   B1                                          
MFA-TR*     WHERE                                                               
MFA-TR*        Y0.SERV_AGREE_STAT_CD >= :WS-START-AGR-STATUS  AND               
MFA-TR*        Y0.SERV_AGREE_STAT_CD <= :WS-END-AGR-STATUS    AND               
MFA-TR*        Y0.SERV_AGREE_DT      >= DATE(:WS-START-AGR-DT) AND              
MFA-TR*        Y0.SERV_AGREE_DT      <= DATE(:WS-END-AGR-DT)  AND               
MFA-TR*        PR.PREMISE_NO          = Y0.PREMISE_NO        AND                
MFA-TR*        B1.COMPANY_NO          = :WS-COMPANY-NO        AND               
MFA-TR*        PR.LOCAL_OFFICE       >= :WS-START-LOC-OFF     AND               
MFA-TR*        PR.LOCAL_OFFICE       <= :WS-END-LOC-OFF       AND               
MFA-TR*        B1.LOCAL_OFFICE        = PR.LOCAL_OFFICE      AND                
MFA-TR*        Y0.SERV_AGREE_DT       > '1900-01-01'                            
MFA-TR*     FOR FETCH ONLY WITH UR                                              
MFA-TR*  END-EXEC.                                                              
                                                                        
      ******************************************************************        
      *    SERV_LINE_CUR IS USED TO SEARCH SERVICE LINE DETAILS        *        
      ******************************************************************        
                                                                        
           EXEC SQL DECLARE SERV_AGREE_ACCT_CUR CURSOR FOR              
             SELECT Y0.PREMISE_NO                                       
                   ,Y0.SERVICE_NO                                       
                   ,Y0.CUSTOMER_NO                                      
                   ,Y0.SALES_REP_ID                                     
                   ,Y0.SERV_AGREE_AMT                                   
                   ,Y0.SERV_AGREE_DT                                    
                   ,Y0.SERV_AGREE_STAT_CD                               
                   ,Y0.SERV_AGREE_ACCT_NO                               
                   ,Y0.SERV_AGREE_ITEM_ID                               
                   ,Y0.SERV_AGREE_COMMENT                               
                   ,PR.LOCAL_OFFICE                                     
                   ,B1.LOCAL_OFFICE_DESC                                
                   ,PR.ADDRESS_ID                                       
             FROM                                                       
                   CSS_PREM_GAS_LINE  Y0 WITH(READUNCOMMITTED)                  
                  ,CSS_PREMISE        PR WITH(READUNCOMMITTED)                  
                  ,CSS_LOCAL_OFFICE   B1 WITH(READUNCOMMITTED)                  
             WHERE                                                      
                 Y0.SERV_AGREE_ACCT_NO  = :Y0-SERV-AGREE-ACCT-NO AND    
                 PR.PREMISE_NO          = Y0.PREMISE_NO          AND    
                 B1.LOCAL_OFFICE        = PR.LOCAL_OFFICE        AND    
                 B1.COMPANY_NO          = PR.COMPANY_NO                 
              FOR READ ONLY                                     
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ004
MFA-TR* MSQ022
MFA-TR*    EXEC SQL DECLARE SERV_AGREE_ACCT_CUR CURSOR FOR                      
MFA-TR*      SELECT Y0.PREMISE_NO                                               
MFA-TR*            ,Y0.SERVICE_NO                                               
MFA-TR*            ,Y0.CUSTOMER_NO                                              
MFA-TR*            ,Y0.SALES_REP_ID                                             
MFA-TR*            ,Y0.SERV_AGREE_AMT                                           
MFA-TR*            ,Y0.SERV_AGREE_DT                                            
MFA-TR*            ,Y0.SERV_AGREE_STAT_CD                                       
MFA-TR*            ,Y0.SERV_AGREE_ACCT_NO                                       
MFA-TR*            ,Y0.SERV_AGREE_ITEM_ID                                       
MFA-TR*            ,Y0.SERV_AGREE_COMMENT                                       
MFA-TR*            ,PR.LOCAL_OFFICE                                             
MFA-TR*            ,B1.LOCAL_OFFICE_DESC                                        
MFA-TR*            ,PR.ADDRESS_ID                                               
MFA-TR*      FROM                                                               
MFA-TR*            CSS_PREM_GAS_LINE  Y0                                        
MFA-TR*           ,CSS_PREMISE        PR                                        
MFA-TR*           ,CSS_LOCAL_OFFICE   B1                                        
MFA-TR*      WHERE                                                              
MFA-TR*          Y0.SERV_AGREE_ACCT_NO  = :Y0-SERV-AGREE-ACCT-NO AND            
MFA-TR*          PR.PREMISE_NO          = Y0.PREMISE_NO          AND            
MFA-TR*          B1.LOCAL_OFFICE        = PR.LOCAL_OFFICE        AND            
MFA-TR*          B1.COMPANY_NO          = PR.COMPANY_NO                         
MFA-TR*       FOR FETCH ONLY WITH UR                                            
MFA-TR*     END-EXEC.                                                           
                                                                        
       PROCEDURE DIVISION USING  PARM-ACCOUNT-NO                        
                                 PARM-COMPANY-NO                        
                                 PARM-LOCAL-OFFICE                      
                                 PARM-SERV-AGREE-STATUS                 
                                 PARM-START-DATE                        
                                 PARM-END-DATE                          
                                 PARM-SET-PENDING-FL                    
                                 PARM-CNT-STATUS-CD.                    
                                                                        
      ******************************************************************        
      * 0000-MAINLINE                                                  *        
      *     CONTROLS THE MAIN PATH OF THE PROGRAM                      *        
      ******************************************************************        
                                                                        
       0000-MAINLINE.                                                   
                                                                        
           PERFORM 0100-INITIALIZE     THRU 0100-EXIT.                  
           PERFORM 1000-PROCESS-INPUT  THRU 1000-EXIT.                  
           PERFORM 2000-PROCESS-OUTPUT THRU 2000-EXIT.                  
           PERFORM 9999-END-PROGRAM    THRU 9999-EXIT.                  
                                                                        
       0000-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 0100-INITIALIZE                                                *        
      *     1. RESET DB2 ERROR HANDLERS                                *        
      *     2. ESTABLISH GATEWAY ENVIRONMENT (TDINIT)                  *        
      *     3. ACCEPT CLIENT REQUEST/ESTABLISH SNA HANDLE (TDACCEPT)   *        
      *     4. DETERMINE IF CLIENT HAS SENT AN RPC PARAMETER (TDRESULT)*        
      ******************************************************************        
                                                                        
       0100-INITIALIZE.                                                 
                                                                        
           EXEC SQL WHENEVER SQLWARNING CONTINUE END-EXEC.              
           EXEC SQL WHENEVER SQLERROR   CONTINUE END-EXEC.              
           EXEC SQL WHENEVER NOT FOUND  CONTINUE END-EXEC.              
                                                                        
           PERFORM 0100A-DECLARE-GTT  THRU 0100A-EXIT.                  
                                                                        
               EXEC SQL                                                 
                   DECLARE C1 CURSOR  FOR                    
                   SELECT                                               
                 RETURN_CODE             AS     RETURN_CODE             
                ,PREMISE_NO              AS     PREMISE_NO              
                ,SERVICE_NO              AS     SERVICE_NO              
                ,LTRIM(RTRIM(MKT_REP_NAME))     AS     MKT_REP_NAME            
                ,SERV_AGREE_AMT          AS     SERV_AGREE_AMT          
                ,LTRIM(RTRIM(SERV_AGREE_DT))    AS     SERV_AGREE_DT           
                ,SERV_AGREE_STATUS       AS     SERV_AGREE_STATUS       
                ,RTRIM(CUSTOMER_NAME)    AS     CUSTOMER_NAME           
                ,RTRIM(ADDRESS)          AS     ADDRESS                 
                ,RTRIM(ADDRESS_TITLE)    AS     ADDRESS_TITLE           
                ,LOCAL_OFFICE            AS     LOCAL_OFFICE            
                ,LOCAL_OFFICE_DESC       AS     LOCAL_OFFICE_DESC       
                ,SERV_AGREE_ACCT_NO      AS     SERV_AGREE_ACCT_NO      
                ,CASE (LTRIM(RTRIM(SET_ORDER_STATE)))                          
                     WHEN 'C' THEN ' '                                  
                     ELSE LTRIM(RTRIM(SET_ORDER_STATE)) END                    
                                         AS     SET_ORDER_STATE         
                ,CNT_STATUS_CD           AS     CNT_STATUS_CD           
                ,CNT_ITEM_ID             AS     CNT_ITEM_ID             
                ,LTRIM(RTRIM(CNT_DATE))         AS     CNT_DATE                
                ,SERV_AGREE_COMMENT      AS     SERV_AGREE_COMMENT      
                ,SERV_AGREE_STAT_DESC    AS     SERV_AGREE_STAT_DESC    
                ,CNT_STAT_DESC           AS     CNT_STAT_DESC           
                   FROM                                                 
                       #CSR04087_R1                              
                       ORDER BY LOCAL_OFFICE_DESC ASC                   
               END-EXEC.                                                

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ029
MFA-TR* MSQ049
MFA-TR*        EXEC SQL                                                         
MFA-TR*            DECLARE C1 CURSOR WITH RETURN FOR                            
MFA-TR*            SELECT                                                       
MFA-TR*          RETURN_CODE             AS     RETURN_CODE                     
MFA-TR*         ,PREMISE_NO              AS     PREMISE_NO                      
MFA-TR*         ,SERVICE_NO              AS     SERVICE_NO                      
MFA-TR*         ,STRIP(MKT_REP_NAME)     AS     MKT_REP_NAME                    
MFA-TR*         ,SERV_AGREE_AMT          AS     SERV_AGREE_AMT                  
MFA-TR*         ,STRIP(SERV_AGREE_DT)    AS     SERV_AGREE_DT                   
MFA-TR*         ,SERV_AGREE_STATUS       AS     SERV_AGREE_STATUS               
MFA-TR*         ,RTRIM(CUSTOMER_NAME)    AS     CUSTOMER_NAME                   
MFA-TR*         ,RTRIM(ADDRESS)          AS     ADDRESS                         
MFA-TR*         ,RTRIM(ADDRESS_TITLE)    AS     ADDRESS_TITLE                   
MFA-TR*         ,LOCAL_OFFICE            AS     LOCAL_OFFICE                    
MFA-TR*         ,LOCAL_OFFICE_DESC       AS     LOCAL_OFFICE_DESC               
MFA-TR*         ,SERV_AGREE_ACCT_NO      AS     SERV_AGREE_ACCT_NO              
MFA-TR*         ,CASE (STRIP(SET_ORDER_STATE))                                  
MFA-TR*              WHEN 'C' THEN ' '                                          
MFA-TR*              ELSE STRIP(SET_ORDER_STATE) END                            
MFA-TR*                                  AS     SET_ORDER_STATE                 
MFA-TR*         ,CNT_STATUS_CD           AS     CNT_STATUS_CD                   
MFA-TR*         ,CNT_ITEM_ID             AS     CNT_ITEM_ID                     
MFA-TR*         ,STRIP(CNT_DATE)         AS     CNT_DATE                        
MFA-TR*         ,SERV_AGREE_COMMENT      AS     SERV_AGREE_COMMENT              
MFA-TR*         ,SERV_AGREE_STAT_DESC    AS     SERV_AGREE_STAT_DESC            
MFA-TR*         ,CNT_STAT_DESC           AS     CNT_STAT_DESC                   
MFA-TR*            FROM                                                         
MFA-TR*                SESSION.CSR04087_R1                                      
MFA-TR*                ORDER BY LOCAL_OFFICE_DESC ASC                           
MFA-TR*        END-EXEC.                                                        
       0100-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      *0100A-DECLARE-GTT                                               *        
      ******************************************************************        
       0100A-DECLARE-GTT.                                               
      *                                                                         
            MOVE 'DECLARE GLOBAL TEMPORARY TABLE CSR04087_R1'           
                                          TO S-SQL-STATEMENT-V.         
            EXEC SQL
              CALL CIS.DROP_TEMP_TABLE('#CSR04087_R1')
            END-EXEC
            EXEC SQL
              CREATE TABLE #CSR04087_R1
               (                                                       
                 RETURN_CODE               INT                      
                ,PREMISE_NO                DECIMAL(10,0)                
                ,SERVICE_NO                DECIMAL(10,0)                
                ,MKT_REP_NAME CHAR(50)  COLLATE LATIN1_GENERAL_100_BIN2         
                ,SERV_AGREE_AMT            DECIMAL(11,2)                
                ,SERV_AGREE_DT CHAR(10)  COLLATE LATIN1_GENERAL_100_BIN2        
                ,SERV_AGREE_STATUS CHAR(1)  COLLATE 
                               LATIN1_GENERAL_100_BIN2                      
                ,CUSTOMER_NAME CHAR(50)  COLLATE LATIN1_GENERAL_100_BIN2        
                ,ADDRESS CHAR(100)  COLLATE LATIN1_GENERAL_100_BIN2             
                ,ADDRESS_TITLE CHAR(80)  COLLATE LATIN1_GENERAL_100_BIN2        
                ,LOCAL_OFFICE CHAR(03)  COLLATE LATIN1_GENERAL_100_BIN2         
                ,LOCAL_OFFICE_DESC CHAR(22)  COLLATE 
                               LATIN1_GENERAL_100_BIN2                     
                ,SERV_AGREE_ACCT_NO        DECIMAL(13,0)                
                ,SET_ORDER_STATE CHAR(1)  COLLATE 
                               LATIN1_GENERAL_100_BIN2                      
                ,CNT_STATUS_CD CHAR(1)  COLLATE LATIN1_GENERAL_100_BIN2         
                ,CNT_ITEM_ID               INT                      
                ,CNT_DATE CHAR(10)  COLLATE LATIN1_GENERAL_100_BIN2             
                ,SERV_AGREE_COMMENT CHAR(255)  COLLATE 
                               LATIN1_GENERAL_100_BIN2                    
                ,SERV_AGREE_STAT_DESC CHAR(15)  COLLATE 
                               LATIN1_GENERAL_100_BIN2                     
                ,CNT_STAT_DESC CHAR(15)  COLLATE LATIN1_GENERAL_100_BIN2        
                )
            END-EXEC.                                                   

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

 *                                                                      
            MOVE SQLSTATE                 TO WS-SQLSTATE.               
            MOVE SQLCODE                  TO WS-ACTIVE-RETURN-CODE.     
 *                                                                      
            IF WS-SQLSTATE = '42710'                                    
               PERFORM 8000A-DELETE-GTT-ROWS                            
                                          THRU 8000A-EXIT               
            ELSE                                                        
               IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL               
                 NEXT SENTENCE                                          
              ELSE                                                      
                 MOVE PROGRAM-NAME       TO ABEND-PROGRAM               
                 MOVE SQLCODE            TO ABEND-SQLCODE               
                 MOVE SQLSTATE           TO ABEND-SQLSTATE              
                 MOVE '0100A'            TO ACTIVE-PARAGRAPH            
                 MOVE 'DECLARE GTT'      TO ABEND-FUNCTION              
                 MOVE SPACES             TO ABEND-SQL-PREDICATES        
                                            ABEND-TABLES                
                 MOVE 'CSR04087_R1'      TO TABLE-1                     
                 MOVE SPACES             TO TABLE-ELEMENT-1             
                 MOVE SPACES             TO HOSTVAR-ELEMENT-1           
                 PERFORM 9900-SQL-ERROR-ROUTINE                         
                                         THRU  9900-EXIT                
              END-IF                                                    
           END-IF.                                                      
                                                                        
       0100A-EXIT.                                                      
            EXIT.                                                       
      ******************************************************************        
      * 1000-PROCESS-INPUT                                             *        
      *     1. RECEIVE EACH PARAMETER FROM THE REMOTE PROCEDURE        *        
      *     2. SPLIT OUT PARM STRING INFORMATION                       *        
      ******************************************************************        
                                                                        
       1000-PROCESS-INPUT.                                              
           IF PARM-ACCOUNT-NO  = 0.0 THEN                               
             IF PARM-LOCAL-OFFICE NOT EQUAL SPACES THEN                 
                MOVE PARM-LOCAL-OFFICE TO WS-START-LOC-OFF              
                MOVE PARM-LOCAL-OFFICE TO WS-END-LOC-OFF                
             END-IF                                                     
             IF PARM-SERV-AGREE-STATUS NOT EQUAL SPACES THEN            
                MOVE PARM-SERV-AGREE-STATUS TO WS-START-AGR-STATUS      
                MOVE PARM-SERV-AGREE-STATUS TO WS-END-AGR-STATUS        
             END-IF                                                     
           END-IF                                                       
           MOVE PARM-ACCOUNT-NO  TO Y0-SERV-AGREE-ACCT-NO.              
           MOVE PARM-COMPANY-NO  TO WS-COMPANY-NO.                      
           MOVE PARM-START-DATE  TO WS-START-AGR-DT.                    
           MOVE PARM-END-DATE    TO WS-END-AGR-DT.                      
                                                                        
       1000-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 2000-PROCESS-OUTPUT                                            *        
      *                                                                *        
      *                                                                *        
      ******************************************************************        
                                                                        
       2000-PROCESS-OUTPUT.                                             
           IF PARM-ACCOUNT-NO = 0.0 THEN                                
              PERFORM 7000-OPEN-SERV-AGREE-CUR     THRU 7000-EXIT       
              PERFORM 7050-FETCH-SERV-AGREE-CUR    THRU 7050-EXIT       
           ELSE                                                         
              PERFORM 7025-OPEN-SERV-AGREE-ACCT-CUR     THRU 7025-EXIT  
              PERFORM 7100-FETCH-SERV-AGREE-ACCT-CUR    THRU 7100-EXIT  
           END-IF                                                       
                                                                        
           PERFORM                                                      
               UNTIL WS-ACTIVE-RETURN-CODE = NOT-FOUND                  
                                                                        
               MOVE Y0-PREMISE-NO            TO PR-PREMISE-NO           
               MOVE Y0-PREMISE-NO            TO VO-PREMISE-NO           
               MOVE Y0-PREMISE-NO            TO YV-PREMISE-NO           
                                                                        
               PERFORM 2500-PROCESS-ROW                 THRU 2500-EXIT  
               IF PARM-ACCOUNT-NO = 0.0 THEN                            
                  PERFORM 7050-FETCH-SERV-AGREE-CUR THRU 7050-EXIT      
               ELSE                                                     
                  PERFORM 7100-FETCH-SERV-AGREE-ACCT-CUR THRU 7100-EXIT 
               END-IF                                                   
           END-PERFORM.                                                 
           IF PARM-ACCOUNT-NO = 0.0 THEN                                
              PERFORM 7200-CLOSE-SERV-AGREE-CUR    THRU 7200-EXIT       
           ELSE                                                         
              PERFORM 7225-CLOSE-SERV-AGREE-ACCT-CUR    THRU 7225-EXIT  
           END-IF                                                       
           IF CTR-ROWS = 0 THEN                                         
              MOVE    100                       TO RS-RETURN-CODE       
              PERFORM 8100-SEND-RESULT          THRU 8100-EXIT          
           END-IF.                                                      
       2000-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 2500-PROCESS-ROW                                               *        
      *     GET ANY MISSING DATA NOT FOUND IN THE ORIGINAL CURSORS.    *        
      *     FORMAT RESULT SET AND SEND DATA.                           *        
      ******************************************************************        
                                                                        
       2500-PROCESS-ROW.                                                
                                                                        
           MOVE SPACES TO RS-SERV-AGREE-STAT-DESC.                      
           IF Y0-SERV-AGREE-STAT-CD NOT EQUAL SPACES                    
              PERFORM 7700-GET-SERV-AGREE-STAT-DESC THRU 7700-EXIT      
              MOVE WS-SERV-AGREE-STAT-DESC  TO RS-SERV-AGREE-STAT-DESC  
           END-IF                                                       
                                                                        
           IF Y0-CUSTOMER-NO NOT NUMERIC                                
              MOVE 0 TO Y0-CUSTOMER-NO                                  
           END-IF                                                       
           MOVE SPACES TO RS-CUSTOMER-NAME                              
           IF Y0-CUSTOMER-NO > 0  THEN                                  
              MOVE Y0-CUSTOMER-NO       TO CU-CUSTOMER-NO               
              PERFORM 7300-GET-NAME     THRU 7300-EXIT                  
              MOVE WS-CUSTOMER-NAME     TO RS-CUSTOMER-NAME             
           END-IF                                                       
                                                                        
           MOVE SPACES TO RS-MKT-REP-NAME                               
           IF Y0-SALES-REP-ID NOT EQUAL SPACES THEN                     
              MOVE Y0-SALES-REP-ID      TO PF-USER-ID                   
              PERFORM 7350-GET-MKT-REP-NAME THRU 7350-EXIT              
              MOVE WS-MKT-REP-NAME      TO RS-MKT-REP-NAME              
           END-IF                                                       
                                                                        
           IF PR-ADDRESS-ID NOT NUMERIC THEN                            
              MOVE 0 TO PR-ADDRESS-ID                                   
           END-IF                                                       
           MOVE SPACES TO RS-ADDRESS                                    
           IF PR-ADDRESS-ID > 0                                         
              MOVE PR-ADDRESS-ID TO DY-ADDRESS-ID                       
              PERFORM 7400-GET-ADDRESS THRU 7400-EXIT                   
              MOVE WS-ADDRESS          TO RS-ADDRESS                    
           END-IF                                                       
                                                                        
           MOVE SPACES TO RS-ADDRESS-TITLE                              
           IF PR-ADDRESS-ID > 0                                         
              MOVE PR-ADDRESS-ID TO DY-ADDRESS-ID                       
              PERFORM 7420-GET-ADDRESS-TITLE THRU 7420-EXIT             
              MOVE WS-ADDRESS-TITLE     TO RS-ADDRESS-TITLE             
           END-IF                                                       
                                                                        
           MOVE SPACES TO RS-SET-ORDER-STATE                            
           PERFORM 7650-SELECT-SET-INFO THRU 7650-EXIT                  
           MOVE VO-ORDER-STATE-CD TO RS-SET-ORDER-STATE                 
           IF WS-ACTIVE-RETURN-CODE = NOT-FOUND THEN                    
              PERFORM 7655-SELECT-SET-INFO-HST THRU 7655-EXIT           
              MOVE YV-ORDER-STATE-CD TO RS-SET-ORDER-STATE              
           END-IF                                                       
                                                                        
           IF Y0-SERV-AGREE-ACCT-NO IS NOT NUMERIC                      
              MOVE 0 TO Y0-SERV-AGREE-ACCT-NO                           
           END-IF                                                       
           MOVE 0 TO RS-SERV-AGREE-ACCT-NO                              
           MOVE 0 TO RS-CNT-ITEM-ID                                     
           MOVE SPACES TO RS-CNT-DATE WS-CNT-DATE                       
           MOVE SPACES TO RS-CNT-STATUS-CD                              
           MOVE SPACES TO RS-CNT-STAT-DESC                              
           IF Y0-SERV-AGREE-ACCT-NO > 0                                 
              MOVE Y0-SERV-AGREE-ACCT-NO TO CT-ACCOUNT-NO               
              MOVE Y0-SERV-AGREE-ITEM-ID TO CT-CNT-ITEM-ID              
              PERFORM 7660-SELECT-CNT-INFO THRU 7660-EXIT               
              MOVE CT-ACCOUNT-NO       TO RS-SERV-AGREE-ACCT-NO         
              MOVE CT-CNT-ITEM-ID      TO RS-CNT-ITEM-ID                
              IF CT-DATE-CONTRACT NOT EQUAL SPACES THEN                 
                 MOVE CT-DATE-CONTRACT    TO WS-CNT-DATE                
                 STRING WS-CNT-DATE(6:2) '/'                            
                        WS-CNT-DATE(9:2) '/'                            
                        WS-CNT-DATE(1:4)                                
                        DELIMITED BY SIZE INTO RS-CNT-DATE              
              END-IF                                                    
              MOVE CT-CNT-STATUS-CD    TO RS-CNT-STATUS-CD              
              IF CT-CNT-STATUS-CD NOT EQUAL SPACES THEN                 
                 MOVE SPACES           TO WS-CNT-STAT-DESC              
                 PERFORM 5100-GET-CNT-STAT-DESC THRU 5100-EXIT          
                 MOVE WS-CNT-STAT-DESC TO RS-CNT-STAT-DESC              
              END-IF                                                    
           END-IF                                                       
           MOVE   SPACE                   TO WS-SERV-AGREE-COMMENT      
           MOVE   SPACE                   TO RS-SERV-AGREE-COMMENT-TEXT 
           MOVE   Y0-PREMISE-NO           TO RS-PREMISE-NO              
           MOVE   Y0-SERVICE-NO           TO RS-SERVICE-NO              
           MOVE   Y0-SERV-AGREE-AMT       TO RS-SERV-AGREE-AMT          
           MOVE   Y0-SERV-AGREE-DT        TO WS-SERV-AGREE-DT           
           STRING WS-SERV-AGREE-DT(6:2) '/'                             
                  WS-SERV-AGREE-DT(9:2) '/'                             
                  WS-SERV-AGREE-DT(1:4)                                 
                  DELIMITED BY SIZE INTO RS-SERV-AGREE-DT               
           MOVE   Y0-SERV-AGREE-STAT-CD   TO RS-SERV-AGREE-STATUS       
           MOVE   Y0-SERV-AGREE-COMMENT   TO WS-SERV-AGREE-COMMENT      
           MOVE   WS-SERV-AGREE-COMMENT-TEXT                            
                                          TO RS-SERV-AGREE-COMMENT-TEXT 
           MOVE   PR-LOCAL-OFFICE         TO RS-LOCAL-OFFICE            
           MOVE   B1-LOCAL-OFFICE-DESC    TO RS-LOCAL-OFFICE-DESC       
                                                                        
           PERFORM 8100-SEND-RESULT          THRU 8100-EXIT.            
                                                                        
           MOVE SPACE TO Y0-SERV-AGREE-COMMENT-TEXT.                    
                                                                        
       2500-EXIT.                                                       
           EXIT.                                                        
                                                                        
                                                                        
      ******************************************************************        
      * 5100-GET-CNT-STAT-DESC.                                        *        
      ******************************************************************        
                                                                        
       5100-GET-CNT-STAT-DESC.                                          
                                                                        
             EVALUATE CT-CNT-STATUS-CD                                  
                WHEN 'A'                                                
                   MOVE 'PENDING'     TO WS-CNT-STAT-DESC               
                WHEN 'B'                                                
                   MOVE 'ACTIVE'      TO WS-CNT-STAT-DESC               
                WHEN 'C'                                                
                   MOVE 'PAID'        TO WS-CNT-STAT-DESC               
                WHEN 'D'                                                
                   MOVE 'CANCELED'    TO WS-CNT-STAT-DESC               
                WHEN 'E'                                                
                   MOVE 'CLOSED'      TO WS-CNT-STAT-DESC               
                WHEN 'R'                                                
                   MOVE 'TRANSFERRED' TO WS-CNT-STAT-DESC               
                WHEN 'P'                                                
                   MOVE 'REPOSSED'    TO WS-CNT-STAT-DESC               
                WHEN 'F'                                                
                   MOVE 'REFINANCED'  TO WS-CNT-STAT-DESC               
              END-EVALUATE.                                             
                                                                        
       5100-EXIT.                                                       
           EXIT.                                                        
      ******************************************************************        
      * 6995-CPD99-ERROR-ROUTINE.                                      *        
      ******************************************************************        
                                                                        
       6995-CPD99-ERROR-ROUTINE.                                        
                                                                        
           MOVE PROGRAM-NAME               TO ABEND-PROGRAM.            
           PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT.              
           PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT.              
                                                                        
       6995-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7000-OPEN-SERV-AGREE-CUR.                                      *        
      ******************************************************************        
                                                                        
       7000-OPEN-SERV-AGREE-CUR.                                        
                                                                        
           EXEC SQL                                                     
              OPEN SERV_AGREE_CUR                                       
           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                   
               NEXT SENTENCE                                            
           ELSE                                                         
               MOVE PROGRAM-NAME              TO ABEND-PROGRAM          
               MOVE '7000'                    TO ACTIVE-PARAGRAPH       
               MOVE 'SELECT'                  TO ABEND-FUNCTION         
               MOVE 'OPEN SERV_AGREE_CUR'     TO TABLE-1                
               MOVE 'PREMISE_NO'              TO TABLE-ELEMENT-1        
               MOVE Y0-PREMISE-NO             TO HOSTVAR-ELEMENT-1      
               PERFORM 9000-SEND-ERROR-RESULT THRU 9000-EXIT            
               PERFORM 9900-SQL-ERROR-ROUTINE THRU 9900-EXIT            
           END-IF.                                                      
                                                                        
       7000-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7025-OPEN-SERV-AGREE-ACCT-CUR                                  *        
      ******************************************************************        
                                                                        
       7025-OPEN-SERV-AGREE-ACCT-CUR.                                   
                                                                        
           EXEC SQL                                                     
              OPEN SERV_AGREE_ACCT_CUR                                  
           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                   
               NEXT SENTENCE                                            
           ELSE                                                         
               MOVE PROGRAM-NAME               TO ABEND-PROGRAM         
               MOVE '7025'                     TO ACTIVE-PARAGRAPH      
               MOVE 'SELECT'                   TO ABEND-FUNCTION        
               MOVE 'OPEN SERV_AGREE_ACCT_CUR' TO TABLE-1               
               MOVE 'PREMISE_NO'               TO TABLE-ELEMENT-1       
               MOVE Y0-PREMISE-NO              TO HOSTVAR-ELEMENT-1     
               PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT           
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
           END-IF.                                                      
                                                                        
       7025-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7050-FETCH-SERV-AGREE-CUR.                                              
      ******************************************************************        
                                                                        
       7050-FETCH-SERV-AGREE-CUR.                                       
                                                                        
           MOVE SQLCODE TO WS-ACTIVE-RETURN-CODE.                       
           EXEC SQL                                                     
               FETCH SERV_AGREE_CUR                                     
                INTO  :Y0-PREMISE-NO                                    
                     ,:Y0-SERVICE-NO                                    
                     ,:Y0-CUSTOMER-NO :WS-CUSTOMER-NO-NI         
                     ,:Y0-SALES-REP-ID                                  
                     ,:Y0-SERV-AGREE-AMT :WS-SERV-AGREE-AMT-NI      
                     ,:Y0-SERV-AGREE-DT :WS-SERV-AGREE-DT-NI       
                     ,:Y0-SERV-AGREE-STAT-CD                            
                     ,:Y0-SERV-AGREE-ACCT-NO                            
                     ,:Y0-SERV-AGREE-ITEM-ID                            
                     ,:Y0-SERV-AGREE-COMMENT                            
                     ,:PR-LOCAL-OFFICE                                  
                     ,:B1-LOCAL-OFFICE-DESC                             
                     ,:PR-ADDRESS-ID                                    
           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 PROGRAM-NAME          TO ABEND-PROGRAM              
               MOVE '7050'                TO ACTIVE-PARAGRAPH           
               MOVE 'SELECT'              TO ABEND-FUNCTION             
               MOVE 'CSS_PREM_GAS_LINE'   TO TABLE-1                    
               MOVE 'PREMISE_NO'          TO TABLE-ELEMENT-1            
               MOVE Y0-PREMISE-NO         TO HOSTVAR-ELEMENT-1          
               PERFORM 9000-SEND-ERROR-RESULT THRU 9000-EXIT            
               PERFORM 9900-SQL-ERROR-ROUTINE THRU 9900-EXIT            
           END-IF.                                                      
                                                                        
       7050-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7100-FETCH-SERV-AGREE-ACCT-CUR.                                         
      ******************************************************************        
                                                                        
       7100-FETCH-SERV-AGREE-ACCT-CUR.                                  
                                                                        
           MOVE SQLCODE TO WS-ACTIVE-RETURN-CODE.                       
           EXEC SQL                                                     
               FETCH SERV_AGREE_ACCT_CUR                                
                INTO  :Y0-PREMISE-NO                                    
                     ,:Y0-SERVICE-NO                                    
                     ,:Y0-CUSTOMER-NO :WS-CUSTOMER-NO-NI         
                     ,:Y0-SALES-REP-ID                                  
                     ,:Y0-SERV-AGREE-AMT :WS-SERV-AGREE-AMT-NI      
                     ,:Y0-SERV-AGREE-DT :WS-SERV-AGREE-DT-NI       
                     ,:Y0-SERV-AGREE-STAT-CD                            
                     ,:Y0-SERV-AGREE-ACCT-NO                            
                     ,:Y0-SERV-AGREE-ITEM-ID                            
                     ,:Y0-SERV-AGREE-COMMENT                            
                     ,:PR-LOCAL-OFFICE                                  
                     ,:B1-LOCAL-OFFICE-DESC                             
                     ,:PR-ADDRESS-ID                                    
           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 PROGRAM-NAME          TO ABEND-PROGRAM              
               MOVE '7100'                TO ACTIVE-PARAGRAPH           
               MOVE 'SELECT'              TO ABEND-FUNCTION             
               MOVE 'CSS_PREM_GAS_LINE'   TO TABLE-1                    
               MOVE 'PREMISE_NO'          TO TABLE-ELEMENT-1            
               MOVE Y0-PREMISE-NO         TO HOSTVAR-ELEMENT-1          
               PERFORM 9000-SEND-ERROR-RESULT THRU 9000-EXIT            
               PERFORM 9900-SQL-ERROR-ROUTINE THRU 9900-EXIT            
           END-IF.                                                      
                                                                        
       7100-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7200-CLOSE-SERV-AGREE-CUR.                                              
      ******************************************************************        
                                                                        
       7200-CLOSE-SERV-AGREE-CUR.                                       
                                                                        
           EXEC SQL                                                     
              CLOSE SERV_AGREE_CUR                                      
           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                   
               NEXT SENTENCE                                            
           ELSE                                                         
               MOVE PROGRAM-NAME              TO ABEND-PROGRAM          
               MOVE '7200'                    TO ACTIVE-PARAGRAPH       
               MOVE 'CLOSE '                  TO ABEND-FUNCTION         
               MOVE 'SERV_AGREE_CUR'          TO TABLE-1                
               PERFORM 9000-SEND-ERROR-RESULT THRU 9000-EXIT            
               PERFORM 9900-SQL-ERROR-ROUTINE THRU 9900-EXIT            
           END-IF.                                                      
                                                                        
       7200-EXIT.                                                       
           EXIT.                                                        
                                                                        
                                                                        
      ******************************************************************        
      * 7225-CLOSE-SERV-AGREE-ACCT-CUR.                                         
      ******************************************************************        
                                                                        
       7225-CLOSE-SERV-AGREE-ACCT-CUR.                                  
                                                                        
           EXEC SQL                                                     
              CLOSE SERV_AGREE_ACCT_CUR                                 
           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                   
               NEXT SENTENCE                                            
           ELSE                                                         
               MOVE PROGRAM-NAME              TO ABEND-PROGRAM          
               MOVE '7225'                    TO ACTIVE-PARAGRAPH       
               MOVE 'CLOSE '                  TO ABEND-FUNCTION         
               MOVE 'SERV_AGREE_ACCT_CUR'     TO TABLE-1                
               PERFORM 9000-SEND-ERROR-RESULT THRU 9000-EXIT            
               PERFORM 9900-SQL-ERROR-ROUTINE THRU 9900-EXIT            
           END-IF.                                                      
                                                                        
       7225-EXIT.                                                       
           EXIT.                                                        
                                                                        
                                                                        
      ******************************************************************        
      * 7300-GET-NAME.                                                          
      ******************************************************************        
                                                                        
       7300-GET-NAME.                                                   
                                                                        
           EXEC SQL                                                     
               SELECT TOP(1) RTRIM(LTRIM(RTRIM(LAST_NAME)) + ', ' + 
           LTRIM(RTRIM(FIRST_NAME)) + ' ' + LTRIM(RTRIM(MIDDLE_NAME
           )) + ' ' + LTRIM(RTRIM(TITLE_SUFFIX_1))),
              FULL_NAME                                         
                 INTO :WS-CUSTOMER-NAME                                 
                     ,:WS-FULL-NAME                                     
                 FROM CSS_CUSTOMER CU                                   
                     ,CSS_NAME DQ                                       
                 WHERE CU.CUSTOMER_NO = :CU-CUSTOMER-NO                 
                   AND CU.NAME_ID     = DQ.NAME_ID                      
                                                 
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ020
MFA-TR* MSQ029
MFA-TR* MSQ031
MFA-TR*    EXEC SQL                                                             
MFA-TR*        SELECT RTRIM( STRIP(LAST_NAME)   || ', ' ||                      
MFA-TR*                      STRIP(FIRST_NAME)  || ' '  ||                      
MFA-TR*                      STRIP(MIDDLE_NAME) || ' '  ||                      
MFA-TR*                      STRIP(TITLE_SUFFIX_1))                             
MFA-TR*              ,FULL_NAME                                                 
MFA-TR*          INTO :WS-CUSTOMER-NAME                                         
MFA-TR*              ,:WS-FULL-NAME                                             
MFA-TR*          FROM CSS_CUSTOMER CU                                           
MFA-TR*              ,CSS_NAME DQ                                               
MFA-TR*          WHERE CU.CUSTOMER_NO = :CU-CUSTOMER-NO                         
MFA-TR*            AND CU.NAME_ID     = DQ.NAME_ID                              
MFA-TR*         FETCH FIRST 1 ROWS ONLY                                         
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                   
               IF WS-CUSTOMER-NAME = ','                                
                  MOVE WS-FULL-NAME TO WS-CUSTOMER-NAME                 
               END-IF                                                   
           ELSE                                                         
                    MOVE PROGRAM-NAME         TO ABEND-PROGRAM          
                    MOVE '7300'               TO ACTIVE-PARAGRAPH       
                    MOVE 'SELECT'             TO ABEND-FUNCTION         
                    MOVE 'CSS_CUSTOMER   '    TO TABLE-1                
                    MOVE 'CUSTOMER_NO'        TO TABLE-ELEMENT-1        
                    MOVE CU-CUSTOMER-NO       TO HOSTVAR-ELEMENT-1      
                    PERFORM 9000-SEND-ERROR-RESULT THRU 9000-EXIT       
                    PERFORM 9900-SQL-ERROR-ROUTINE THRU 9900-EXIT       
           END-IF.                                                      
                                                                        
       7300-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7350-GET-MKT-REP-NAME.                                                  
      ******************************************************************        
                                                                        
       7350-GET-MKT-REP-NAME.                                           
                                                                        
           EXEC SQL                                                     
               SELECT TOP(1) RTRIM(PF.LAST_NAME) + ', ' + RTRIM(
           PF.FIRST_NAME) + ' ' + RTRIM(PF.MIDDLE_NAME)                        
                 INTO :WS-MKT-REP-NAME                                  
                 FROM CSS_USER_PROFILE  PF                              
                 WHERE PF.USER_ID     = :PF-USER-ID                     
                                                 
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ020
MFA-TR* MSQ029
MFA-TR* MSQ031
MFA-TR*    EXEC SQL                                                             
MFA-TR*        SELECT  RTRIM(PF.LAST_NAME) || ', ' ||                           
MFA-TR*                RTRIM(PF.FIRST_NAME) || ' ' ||                           
MFA-TR*                RTRIM(PF.MIDDLE_NAME)                                    
MFA-TR*          INTO :WS-MKT-REP-NAME                                          
MFA-TR*          FROM CSS_USER_PROFILE  PF                                      
MFA-TR*          WHERE PF.USER_ID     = :PF-USER-ID                             
MFA-TR*         FETCH FIRST 1 ROWS ONLY                                         
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                   
               NEXT SENTENCE                                            
           ELSE                                                         
                    MOVE PROGRAM-NAME         TO ABEND-PROGRAM          
                    MOVE '7350'               TO ACTIVE-PARAGRAPH       
                    MOVE 'SELECT'             TO ABEND-FUNCTION         
                    MOVE 'CSS_PROFILE    '    TO TABLE-1                
                    MOVE 'USER_ID  '          TO TABLE-ELEMENT-1        
                    MOVE PF-USER-ID           TO HOSTVAR-ELEMENT-1      
                    PERFORM 9000-SEND-ERROR-RESULT THRU 9000-EXIT       
                    PERFORM 9900-SQL-ERROR-ROUTINE THRU 9900-EXIT       
           END-IF.                                                      
                                                                        
       7350-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7400-GET-ADDRESS                                                        
      ******************************************************************        
                                                                        
       7400-GET-ADDRESS.                                                
                                                                        
           EXEC SQL                                                     
               SELECT TOP(1) RTRIM(A.HOUSE_NO) + ' ' + RTRIM(
           A.ADDR_PREFIX_1) + ' ' + RTRIM(A.ADDR_PREFIX_2) + ' ' + 
           RTRIM(A.STREET_NAME) + ' ' + RTRIM(A.STREET_SUFFIX) + ' ' + 
           RTRIM(A.STREET_LOCATION_1) + ' ' + RTRIM(A.STREET_LOCATION_2
           ) + ' ' + RTRIM(A.ADDR_SUFFIX) + ' ' + RTRIM(Z.TOWN) + 
           ' ' + RTRIM(Z.STATE) + ' ' + RTRIM(Z.ZIP_CODE) + '-' + 
           RTRIM(A.ZIP_CODE_PLUS_FOUR)                       
                 INTO :WS-ADDRESS                                       
                 FROM CSS_ADDR_FORMATTED  A,                            
                      CSS_ZIP_CODE        Z                             
                 WHERE A.ADDRESS_ID      = :DY-ADDRESS-ID   AND         
                       Z.ZIP_CODE        = A.ZIP_CODE       AND         
                       Z.ZIP_CODE_TOKEN  = A.ZIP_CODE_TOKEN             
                                                 
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ020
MFA-TR* MSQ029
MFA-TR* MSQ031
MFA-TR*    EXEC SQL                                                             
MFA-TR*        SELECT RTRIM(A.HOUSE_NO) || ' ' ||                               
MFA-TR*               RTRIM(A.ADDR_PREFIX_1) || ' ' ||                          
MFA-TR*               RTRIM(A.ADDR_PREFIX_2) || ' ' ||                          
MFA-TR*               RTRIM(A.STREET_NAME) ||  ' ' ||                           
MFA-TR*               RTRIM(A.STREET_SUFFIX) || ' ' ||                          
MFA-TR*               RTRIM(A.STREET_LOCATION_1) || ' ' ||                      
MFA-TR*               RTRIM(A.STREET_LOCATION_2) || ' ' ||                      
MFA-TR*               RTRIM(A.ADDR_SUFFIX) || ' ' ||                            
MFA-TR*               RTRIM(Z.TOWN) || ' ' ||                                   
MFA-TR*               RTRIM(Z.STATE) || ' ' ||                                  
MFA-TR*               RTRIM(Z.ZIP_CODE) || '-' ||                               
MFA-TR*               RTRIM(A.ZIP_CODE_PLUS_FOUR)                               
MFA-TR*          INTO :WS-ADDRESS                                               
MFA-TR*          FROM CSS_ADDR_FORMATTED  A,                                    
MFA-TR*               CSS_ZIP_CODE        Z                                     
MFA-TR*          WHERE A.ADDRESS_ID      = :DY-ADDRESS-ID   AND                 
MFA-TR*                Z.ZIP_CODE        = A.ZIP_CODE       AND                 
MFA-TR*                Z.ZIP_CODE_TOKEN  = A.ZIP_CODE_TOKEN                     
MFA-TR*         FETCH FIRST 1 ROWS ONLY                                         
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 PROGRAM-NAME         TO ABEND-PROGRAM               
               MOVE '7400'               TO ACTIVE-PARAGRAPH            
               MOVE 'SELECT'             TO ABEND-FUNCTION              
               MOVE 'CSS_ADDR_FORMATTED' TO TABLE-1                     
               MOVE 'ADDRESS_ID'         TO TABLE-ELEMENT-1             
               MOVE DY-ADDRESS-ID        TO HOSTVAR-ELEMENT-1           
               PERFORM 9000-SEND-ERROR-RESULT THRU 9000-EXIT            
               PERFORM 9900-SQL-ERROR-ROUTINE THRU 9900-EXIT            
           END-IF.                                                      
                                                                        
       7400-EXIT.                                                       
           EXIT.                                                        
                                                                        
                                                                        
      ******************************************************************        
      * 7420-GET-ADDRESS-TITLE.                                                 
      ******************************************************************        
                                                                        
       7420-GET-ADDRESS-TITLE.                                          
                                                                        
           EXEC SQL                                                     
               SELECT TOP(1) RTRIM(A.HOUSE_NO) + ' ' + RTRIM(
           A.ADDR_PREFIX_1) + ' ' + RTRIM(A.ADDR_PREFIX_2) + ' ' + 
           RTRIM(A.STREET_NAME) + ' ' + RTRIM(A.STREET_SUFFIX) + ' ' + 
           RTRIM(A.STREET_LOCATION_1) + ' ' + RTRIM(A.STREET_LOCATION_2
           ) + ' ' + RTRIM(A.ADDR_SUFFIX) + ' ' + RTRIM(Z.TOWN)                
                 INTO :WS-ADDRESS-TITLE                                 
                 FROM CSS_ADDR_FORMATTED  A,                            
                      CSS_ZIP_CODE        Z                             
                 WHERE A.ADDRESS_ID      = :DY-ADDRESS-ID   AND         
                       Z.ZIP_CODE        = A.ZIP_CODE       AND         
                       Z.ZIP_CODE_TOKEN  = A.ZIP_CODE_TOKEN             
                                                 
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ020
MFA-TR* MSQ029
MFA-TR* MSQ031
MFA-TR*    EXEC SQL                                                             
MFA-TR*        SELECT RTRIM(A.HOUSE_NO) || ' ' ||                               
MFA-TR*               RTRIM(A.ADDR_PREFIX_1) || ' ' ||                          
MFA-TR*               RTRIM(A.ADDR_PREFIX_2) || ' ' ||                          
MFA-TR*               RTRIM(A.STREET_NAME) ||  ' ' ||                           
MFA-TR*               RTRIM(A.STREET_SUFFIX) || ' ' ||                          
MFA-TR*               RTRIM(A.STREET_LOCATION_1) || ' ' ||                      
MFA-TR*               RTRIM(A.STREET_LOCATION_2) || ' ' ||                      
MFA-TR*               RTRIM(A.ADDR_SUFFIX) || ' ' ||                            
MFA-TR*               RTRIM(Z.TOWN)                                             
MFA-TR*          INTO :WS-ADDRESS-TITLE                                         
MFA-TR*          FROM CSS_ADDR_FORMATTED  A,                                    
MFA-TR*               CSS_ZIP_CODE        Z                                     
MFA-TR*          WHERE A.ADDRESS_ID      = :DY-ADDRESS-ID   AND                 
MFA-TR*                Z.ZIP_CODE        = A.ZIP_CODE       AND                 
MFA-TR*                Z.ZIP_CODE_TOKEN  = A.ZIP_CODE_TOKEN                     
MFA-TR*         FETCH FIRST 1 ROWS ONLY                                         
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 PROGRAM-NAME         TO ABEND-PROGRAM               
               MOVE '7420'               TO ACTIVE-PARAGRAPH            
               MOVE 'SELECT'             TO ABEND-FUNCTION              
               MOVE 'CSS_ADDR_FORMATTED' TO TABLE-1                     
               MOVE 'ADDRESS_ID'         TO TABLE-ELEMENT-1             
               MOVE DY-ADDRESS-ID        TO HOSTVAR-ELEMENT-1           
               PERFORM 9000-SEND-ERROR-RESULT THRU 9000-EXIT            
               PERFORM 9900-SQL-ERROR-ROUTINE THRU 9900-EXIT            
           END-IF.                                                      
                                                                        
       7420-EXIT.                                                       
           EXIT.                                                        
                                                                        
                                                                        
      ******************************************************************        
      * 7650-SELECT-SET-INFO.                                                   
      ******************************************************************        
                                                                        
       7650-SELECT-SET-INFO.                                            
                                                                        
           EXEC SQL                                                     
               SELECT TOP(1) VO.ORDER_STATE_CD                                 
                 INTO :VO-ORDER-STATE-CD                                
                 FROM CSS_SO_DATA         VO                            
                     ,CSS_SO_ORDER_INST   OI                            
                 WHERE VO.PREMISE_NO         = :VO-PREMISE-NO           
                   AND VO.ORDER_TYPE_CD      = 'FM002'                  
                   AND OI.SERV_ORDER_NO      = VO.SERV_ORDER_NO         
                   AND OI.WORK_TYPE_CD       = 'SET'                    
                   AND OI.CODE_UTIL_TYPE     = 'G'                      
                                                 
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ020
MFA-TR*    EXEC SQL                                                             
MFA-TR*        SELECT VO.ORDER_STATE_CD                                         
MFA-TR*          INTO :VO-ORDER-STATE-CD                                        
MFA-TR*          FROM CSS_SO_DATA         VO                                    
MFA-TR*              ,CSS_SO_ORDER_INST   OI                                    
MFA-TR*          WHERE VO.PREMISE_NO         = :VO-PREMISE-NO                   
MFA-TR*            AND VO.ORDER_TYPE_CD      = 'FM002'                          
MFA-TR*            AND OI.SERV_ORDER_NO      = VO.SERV_ORDER_NO                 
MFA-TR*            AND OI.WORK_TYPE_CD       = 'SET'                            
MFA-TR*            AND OI.CODE_UTIL_TYPE     = 'G'                              
MFA-TR*         FETCH FIRST 1 ROWS ONLY                                         
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 PROGRAM-NAME         TO ABEND-PROGRAM               
               MOVE '7650'               TO ACTIVE-PARAGRAPH            
               MOVE 'SELECT'             TO ABEND-FUNCTION              
               MOVE 'CSS_SO_DATA'        TO TABLE-1                     
               MOVE 'PREMISE_NO'         TO TABLE-ELEMENT-1             
               MOVE YV-PREMISE-NO        TO HOSTVAR-ELEMENT-1           
               PERFORM 9000-SEND-ERROR-RESULT THRU 9000-EXIT            
               PERFORM 9900-SQL-ERROR-ROUTINE THRU 9900-EXIT            
           END-IF.                                                      
                                                                        
       7650-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7655-SELECT-SET-INFO-HST.                                               
      ******************************************************************        
                                                                        
       7655-SELECT-SET-INFO-HST.                                        
                                                                        
           EXEC SQL                                                     
               SELECT TOP(1) YV.ORDER_STATE_CD                                 
                 INTO :YV-ORDER-STATE-CD                                
                 FROM HST_SO_DATA         YV                            
                     ,HST_SO_ORDER_INST   ZG                            
                 WHERE YV.PREMISE_NO         = :YV-PREMISE-NO           
                   AND YV.ORDER_TYPE_CD      = 'FM002'                  
                   AND ZG.SERV_ORDER_NO      = YV.SERV_ORDER_NO         
                   AND ZG.WORK_TYPE_CD       = 'SET'                    
                   AND ZG.CODE_UTIL_TYPE     = 'G'                      
                   AND YV.ORDER_STATE_CD     = 'A'                      
                                                 
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ020
MFA-TR*    EXEC SQL                                                             
MFA-TR*        SELECT YV.ORDER_STATE_CD                                         
MFA-TR*          INTO :YV-ORDER-STATE-CD                                        
MFA-TR*          FROM HST_SO_DATA         YV                                    
MFA-TR*              ,HST_SO_ORDER_INST   ZG                                    
MFA-TR*          WHERE YV.PREMISE_NO         = :YV-PREMISE-NO                   
MFA-TR*            AND YV.ORDER_TYPE_CD      = 'FM002'                          
MFA-TR*            AND ZG.SERV_ORDER_NO      = YV.SERV_ORDER_NO                 
MFA-TR*            AND ZG.WORK_TYPE_CD       = 'SET'                            
MFA-TR*            AND ZG.CODE_UTIL_TYPE     = 'G'                              
MFA-TR*            AND YV.ORDER_STATE_CD     = 'A'                              
MFA-TR*         FETCH FIRST 1 ROWS ONLY                                         
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 PROGRAM-NAME         TO ABEND-PROGRAM               
               MOVE '7650'               TO ACTIVE-PARAGRAPH            
               MOVE 'SELECT'             TO ABEND-FUNCTION              
               MOVE 'CSS_SO_DATA'        TO TABLE-1                     
               MOVE 'PREMISE_NO'         TO TABLE-ELEMENT-1             
               MOVE YV-PREMISE-NO        TO HOSTVAR-ELEMENT-1           
               PERFORM 9000-SEND-ERROR-RESULT THRU 9000-EXIT            
               PERFORM 9900-SQL-ERROR-ROUTINE THRU 9900-EXIT            
           END-IF.                                                      
                                                                        
       7655-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7660-SELECT-CNT-INFO.                                                   
      ******************************************************************        
                                                                        
       7660-SELECT-CNT-INFO.                                            
                                                                        
           MOVE SPACE TO CT-DATE-CONTRACT                               
           MOVE SPACE TO CT-CNT-STATUS-CD                               
           EXEC SQL                                                     
               SELECT TOP(1) CT.DATE_CONTRACT,
              CT.CNT_STATUS_CD                                  
                 INTO :CT-DATE-CONTRACT                                 
                     ,:CT-CNT-STATUS-CD                                 
                 FROM CSS_CONTRACT        CT                            
                 WHERE CT.ACCOUNT_NO   = :CT-ACCOUNT-NO                 
                   AND CT.CNT_ITEM_ID  = :CT-CNT-ITEM-ID                
                                                 
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ020
MFA-TR*    EXEC SQL                                                             
MFA-TR*        SELECT CT.DATE_CONTRACT                                          
MFA-TR*              ,CT.CNT_STATUS_CD                                          
MFA-TR*          INTO :CT-DATE-CONTRACT                                         
MFA-TR*              ,:CT-CNT-STATUS-CD                                         
MFA-TR*          FROM CSS_CONTRACT        CT                                    
MFA-TR*          WHERE CT.ACCOUNT_NO   = :CT-ACCOUNT-NO                         
MFA-TR*            AND CT.CNT_ITEM_ID  = :CT-CNT-ITEM-ID                        
MFA-TR*         FETCH FIRST 1 ROWS ONLY                                         
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 PROGRAM-NAME         TO ABEND-PROGRAM               
               MOVE '7660'               TO ACTIVE-PARAGRAPH            
               MOVE 'SELECT'             TO ABEND-FUNCTION              
               MOVE 'CSS_CONTRACT'       TO TABLE-1                     
               MOVE 'ACCOUNT_NO'         TO TABLE-ELEMENT-1             
               MOVE CT-ACCOUNT-NO        TO HOSTVAR-ELEMENT-1           
               PERFORM 9000-SEND-ERROR-RESULT THRU 9000-EXIT            
               PERFORM 9900-SQL-ERROR-ROUTINE THRU 9900-EXIT            
           END-IF.                                                      
                                                                        
       7660-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7700-GET-SERV-AGREE-STAT-DESC.                                          
      ******************************************************************        
                                                                        
       7700-GET-SERV-AGREE-STAT-DESC.                                   
                                                                        
           MOVE SPACE TO WS-SERV-AGREE-STAT-DESC                        
                                                                        
           EXEC SQL                                                     
               SELECT TOP(1) CODE_SHORT_DESC                                   
                 INTO :WS-SERV-AGREE-STAT-DESC                          
                 FROM CSS_CODE_VALUE                                    
                WHERE CODE_VALUE = :Y0-SERV-AGREE-STAT-CD               
                  AND COLUMN_NA  = 'SERV_AGREE_STAT_CD'                 
                  AND TABLE_NA   = 'CSS_PREM_GAS_LINE'                  
                                                 
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ020
MFA-TR*    EXEC SQL                                                             
MFA-TR*        SELECT CODE_SHORT_DESC                                           
MFA-TR*          INTO :WS-SERV-AGREE-STAT-DESC                                  
MFA-TR*          FROM CSS_CODE_VALUE                                            
MFA-TR*         WHERE CODE_VALUE = :Y0-SERV-AGREE-STAT-CD                       
MFA-TR*           AND COLUMN_NA  = 'SERV_AGREE_STAT_CD'                         
MFA-TR*           AND TABLE_NA   = 'CSS_PREM_GAS_LINE'                          
MFA-TR*         FETCH FIRST 1 ROWS ONLY                                         
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 PROGRAM-NAME          TO ABEND-PROGRAM              
               MOVE '7700'                TO ACTIVE-PARAGRAPH           
               MOVE 'SELECT'              TO ABEND-FUNCTION             
               MOVE 'CSS_CODE_VALUE'      TO TABLE-1                    
               MOVE 'SERV_AGREE_STAT_CD'  TO TABLE-ELEMENT-1            
               MOVE Y0-SERV-AGREE-STAT-CD TO HOSTVAR-ELEMENT-1          
               PERFORM 9000-SEND-ERROR-RESULT THRU 9000-EXIT            
               PERFORM 9900-SQL-ERROR-ROUTINE THRU 9900-EXIT            
           END-IF.                                                      
                                                                        
       7700-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      *8000A-DELETE-GTT-ROWS                                                    
      ******************************************************************        
       8000A-DELETE-GTT-ROWS.                                           
                                                                        
            MOVE 'DELETE ROWS'            TO S-SQL-STATEMENT-V.         
                                                                        
            EXEC SQL                                                    
                DELETE FROM #CSR04087_R1                         
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ049
MFA-TR*     EXEC SQL                                                            
MFA-TR*         DELETE FROM SESSION.CSR04087_R1                                 
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 PROGRAM-NAME          TO  ABEND-PROGRAM             
               MOVE '8000A'               TO  ACTIVE-PARAGRAPH          
               MOVE 'DELETE'              TO  ABEND-FUNCTION            
               MOVE SQLCODE               TO  ABEND-SQLCODE             
               MOVE SPACES                TO  ABEND-SQL-PREDICATES      
                                              ABEND-TABLES              
               MOVE 'CSR04087_R1'         TO  TABLE-1                   
               MOVE SPACES                TO  TABLE-ELEMENT-1           
               MOVE SPACES                TO  HOSTVAR-ELEMENT-1         
               PERFORM 9000-SEND-ERROR-RESULT                           
                                          THRU 9000-EXIT                
               PERFORM 9900-SQL-ERROR-ROUTINE                           
                                          THRU 9900-EXIT                
            END-IF.                                                     
                                                                        
       8000A-EXIT.                                                      
            EXIT.                                                       
      *                                                                         
      ******************************************************************        
      *8100-SEND-RESULT                                                         
      *************************************************************             
       8100-SEND-RESULT.                                                
 *                                                                      
            EXEC SQL                                                    
                 INSERT INTO #CSR04087_R1                        
                 (                                                      
                 RETURN_CODE                                            
                ,PREMISE_NO                                             
                ,SERVICE_NO                                             
                ,MKT_REP_NAME                                           
                ,SERV_AGREE_AMT                                         
                ,SERV_AGREE_DT                                          
                ,SERV_AGREE_STATUS                                      
                ,CUSTOMER_NAME                                          
                ,ADDRESS                                                
                ,ADDRESS_TITLE                                          
                ,LOCAL_OFFICE                                           
                ,LOCAL_OFFICE_DESC                                      
                ,SERV_AGREE_ACCT_NO                                     
                ,SET_ORDER_STATE                                        
                ,CNT_STATUS_CD                                          
                ,CNT_ITEM_ID                                            
                ,CNT_DATE                                               
                ,SERV_AGREE_COMMENT                                     
                ,SERV_AGREE_STAT_DESC                                   
                ,CNT_STAT_DESC                                          
                 )                                                      
                 VALUES                                                 
                 (                                                      
                 :RS-RETURN-CODE                                        
                ,:RS-PREMISE-NO                                         
                ,:RS-SERVICE-NO                                         
                ,:RS-MKT-REP-NAME                                       
                ,:RS-SERV-AGREE-AMT                                     
                ,:RS-SERV-AGREE-DT                                      
                ,:RS-SERV-AGREE-STATUS                                  
                ,:RS-CUSTOMER-NAME                                      
                ,:RS-ADDRESS                                            
                ,:RS-ADDRESS-TITLE                                      
                ,:RS-LOCAL-OFFICE                                       
                ,:RS-LOCAL-OFFICE-DESC                                  
                ,:RS-SERV-AGREE-ACCT-NO                                 
                ,:RS-SET-ORDER-STATE                                    
                ,:RS-CNT-STATUS-CD                                      
                ,:RS-CNT-ITEM-ID                                        
                ,:RS-CNT-DATE                                           
                ,:RS-SERV-AGREE-COMMENT-TEXT                            
                ,:RS-SERV-AGREE-STAT-DESC                               
                ,:RS-CNT-STAT-DESC                                      
                 )                                                      
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ049
MFA-TR*     EXEC SQL                                                            
MFA-TR*          INSERT INTO SESSION.CSR04087_R1                                
MFA-TR*          (                                                              
MFA-TR*          RETURN_CODE                                                    
MFA-TR*         ,PREMISE_NO                                                     
MFA-TR*         ,SERVICE_NO                                                     
MFA-TR*         ,MKT_REP_NAME                                                   
MFA-TR*         ,SERV_AGREE_AMT                                                 
MFA-TR*         ,SERV_AGREE_DT                                                  
MFA-TR*         ,SERV_AGREE_STATUS                                              
MFA-TR*         ,CUSTOMER_NAME                                                  
MFA-TR*         ,ADDRESS                                                        
MFA-TR*         ,ADDRESS_TITLE                                                  
MFA-TR*         ,LOCAL_OFFICE                                                   
MFA-TR*         ,LOCAL_OFFICE_DESC                                              
MFA-TR*         ,SERV_AGREE_ACCT_NO                                             
MFA-TR*         ,SET_ORDER_STATE                                                
MFA-TR*         ,CNT_STATUS_CD                                                  
MFA-TR*         ,CNT_ITEM_ID                                                    
MFA-TR*         ,CNT_DATE                                                       
MFA-TR*         ,SERV_AGREE_COMMENT                                             
MFA-TR*         ,SERV_AGREE_STAT_DESC                                           
MFA-TR*         ,CNT_STAT_DESC                                                  
MFA-TR*          )                                                              
MFA-TR*          VALUES                                                         
MFA-TR*          (                                                              
MFA-TR*          :RS-RETURN-CODE                                                
MFA-TR*         ,:RS-PREMISE-NO                                                 
MFA-TR*         ,:RS-SERVICE-NO                                                 
MFA-TR*         ,:RS-MKT-REP-NAME                                               
MFA-TR*         ,:RS-SERV-AGREE-AMT                                             
MFA-TR*         ,:RS-SERV-AGREE-DT                                              
MFA-TR*         ,:RS-SERV-AGREE-STATUS                                          
MFA-TR*         ,:RS-CUSTOMER-NAME                                              
MFA-TR*         ,:RS-ADDRESS                                                    
MFA-TR*         ,:RS-ADDRESS-TITLE                                              
MFA-TR*         ,:RS-LOCAL-OFFICE                                               
MFA-TR*         ,:RS-LOCAL-OFFICE-DESC                                          
MFA-TR*         ,:RS-SERV-AGREE-ACCT-NO                                         
MFA-TR*         ,:RS-SET-ORDER-STATE                                            
MFA-TR*         ,:RS-CNT-STATUS-CD                                              
MFA-TR*         ,:RS-CNT-ITEM-ID                                                
MFA-TR*         ,:RS-CNT-DATE                                                   
MFA-TR*         ,:RS-SERV-AGREE-COMMENT-TEXT                                    
MFA-TR*         ,:RS-SERV-AGREE-STAT-DESC                                       
MFA-TR*         ,:RS-CNT-STAT-DESC                                              
MFA-TR*          )                                                              
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                 
                ADD +1                    TO  CTR-ROWS                  
             ELSE                                                       
                MOVE PROGRAM-NAME         TO  ABEND-PROGRAM             
                MOVE '8100'               TO  ACTIVE-PARAGRAPH          
                MOVE 'INSERT'             TO  ABEND-FUNCTION            
                MOVE SQLCODE              TO  ABEND-SQLCODE             
                MOVE SPACES               TO  ABEND-SQL-PREDICATES      
                                              ABEND-TABLES              
                MOVE 'CSR04087_R1'        TO  TABLE-1                   
                MOVE SPACES               TO  TABLE-ELEMENT-1           
                MOVE SPACES               TO  HOSTVAR-ELEMENT-1         
                PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT          
             END-IF.                                                    
 *                                                                      
        8100-EXIT.                                                      
            EXIT.                                                       
           EXEC SQL                                                             
              INCLUDE CPDSP300                                                  
           END-EXEC.                                                            
      ******************************************************************        
      *       END PROGRAM COPYLIB                                      *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE CPD00320                                                  
           END-EXEC.                                                            
