       IDENTIFICATION DIVISION.                                         
       PROGRAM-ID.      CSR04525.                                       
       AUTHOR.       MS93554.                                           
COB303 DATE-WRITTEN. 03 NOV 2011.                                        
       DATE-COMPILED.                                                   
      *----------------------------------------------------------------         
      *--                  SOUTH CAROLINA ELECTRIC & GAS             --         
      *----------------------------------------------------------------         
      *--                   STORED PROCEDURE:  CSR04525              --         
      *----------------------------------------------------------------         
      *--                         S U M M A R Y                      --         
      *--                                                            --         
      *--  CSR04525 :                                                --         
      *--                                                            --         
      *--  # MODERNIZATION SUPPORT                                   --         
      *--  # RETRIEVE EMAIL TEMPLATE                                            
      *--                                                            --         
      *--  # APPL-RETURN-CODE                                        --         
      *--   +1000 - INPUT PARAMETERS ARE IS INVALID                             
      *----------------------------------------------------------------         
      *--                                                            --         
      *--  PARAMETERS                                                --         
      *--                                                            --         
      *--      IN                                                    --         
      *--                                                            --         
      *--            I_APPL_PROG_ID        CHAR(03)                  --         
      *--            I_USER_ID             CHAR(07)                  --         
      *--            I_COMPANY_NO          CHAR(01)                  --         
      *--            I_EMAIL_TYPE_CD       CHAR(03)                  --         
      *--            I_EMAIL_SUBTYPE_CD    CHAR(03)                  --         
      *--            I_MAX_VERSION_FL      CHAR(01)                  --         
      *--            I_VERSION_NO          CHAR(10)                  --         
      *--                                                            --         
      *--      OUT                                                   --         
      *--            NONE                                            --         
      *--                                                            --         
      *--      INOUT                                                 --         
      *--            NONE                                            --         
      *--                                                            --         
      *--  RESULT SET                                                --         
      *--                                                            --         
      *--            RETURN_CODE           INTEGER                   --         
      *--            APPL_RETURN_CODE      INTEGER                   --         
      *--            COMPANY_NO            CHAR(01)                  --         
      *--            EMAIL_TYPE_CD         CHAR(03)                  --         
      *--            EMAIL_SUBTYPE_CD      CHAR(03)                  --         
      *--            VERSION_NO            CHAR(10)                  --         
      *--            VERSION_START_TS      CHAR (26)                 --         
      *--            STATUS_CD             CHAR(01)                  --         
      *--            EMAIL_ENTITY          VARCHAR(255)              --         
      *--            EMAIL_SUBJECT         VARCHAR(255)              --         
      *--            EMAIL_BODY            VARCHAR(30000)            --         
      *--            EMAIL_FROM_ADDR       VARCHAR(255)              --         
      *--            EMAIL_TO_ADDR         VARCHAR(255)              --         
      *--            EMAIL_CC_ADDR         VARCHAR(255)              --         
      *--            EMAIL_BCC_ADDR        VARCHAR(255)              --         
      *--            EMAIL_AUDIT_FL        CHAR(01)                  --         
      *--            CREATED_TS            CHAR (26)                 --         
      *--            CREATED_BY_USERID     CHAR(07)                  --         
      *--            LAST_UPDATE_TS        CHAR (26)                 --         
      *--            LAST_UPDATE_USERID    CHAR(07)                  --         
      *--                                                            --         
      *----------------------------------------------------------------         
      *--                        MODIFICATION LOG                    --         
      *--                                                            --         
      *--  DATE          INITIALS    COMMENTS                        --         
      *--  -----------   --------    ----------------------------------         
P00473*--  11/03/2011    MS93554     RETRIEVE EMAIL TEMPLATE.        --         
      *----------------------------------------------------------------         
                                                                        
       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 'CSR04525'.
MSQ017     COPY MFASQLM.
                                                                        
      ******************************************************************        
      *    DB2 INCLUDES                                                *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE SQLCA                                                     
           END-EXEC.                                                            
                                                                        
      ******************************************************************        
      *    CSS_EMAIL_TEMPLATE 2E                                       *        
      ******************************************************************        
      *                                                                         
           EXEC SQL                                                             
              INCLUDE TBEMTEMP                                                  
           END-EXEC.                                                            
      *                                                                         
      ******************************************************************        
      *    SUPPORTS DB2 AND SQL ERROR CHECKING                         *        
      ******************************************************************        
           COPY CWS00303.                                                       
                                                                        
      ******************************************************************        
      *    WORK AREAS TO XFER DATA.                                    *        
      ******************************************************************        
           COPY CCA00001.                                                       
                                                                        
      ******************************************************************        
      *    ERROR HANDLING COPY BOOK.                                   *        
      ******************************************************************        
           EXEC SQL                                                             
                INCLUDE CWSX0010                                                
           END-EXEC.                                                            
                                                                        
       01  WS-WORKING-STORAGE.                                          
           05 WS-SQLSTATE              PIC X(05) VALUE SPACES.          
                                                                        
       01  WS-SWITCHES.                                                 
           05 ALL-DONE-SW              PIC X(01) VALUE 'N'.             
              88 NOT-ALL-DONE                    VALUE 'N'.             
              88 ALL-DONE                        VALUE 'Y'.             
           05 SEND-DONE-SW             PIC X(01) VALUE 'Y'.             
              88 SEND-DONE-ERROR                 VALUE 'N'.             
              88 SEND-DONE-OK                    VALUE 'Y'.             
           05 WS-NO-MORE-ROWS          PIC X(01) VALUE 'N'.             
              88 NO-MORE-ROWS                    VALUE 'Y'.             
                                                                        
       01  WS-LITERALS.                                                 
           05 PROGRAM-NAME             PIC X(08) VALUE 'CSR04525'.      
           05 WS-ACTIVE                PIC X(01) VALUE 'A'.             
           05 WS-YES                   PIC X(01) VALUE 'Y'.             
           05 WS-NO                    PIC X(01) VALUE 'N'.             
           05 GTT-NAME                 PIC X(26) VALUE                  
                                                'SESSION.CSR04525_R1'.  
                                                                        
       01  WS-MISC.                                                     
           05 WS-COMPANY-NO            PIC X(02).                       
           05 WS-EMAIL-TYPE-CD         PIC X(03).                       
           05 WS-EMAIL-SUBTYPE-CD      PIC X(03).                       
           05 WS-MAX-VERSION-FL        PIC X(01).                       
MAN012*    05 WS-VERSION-NO            PIC X(10). 
MAN012     05 WS-VERSION-NO            PIC X(10) JUSTIFIED RIGHT.                       
           05 WS-VERSION-NO-RED REDEFINES WS-VERSION-NO                 
                                       PIC 9(10).                       
           05 WS-VERN-NO-PASSED        PIC X(01)  VALUE 'N'.            
           05 S-APPL-RETURN-CODE       PIC S9(09) COMP VALUE 0.         
		   
       01  GTT-MISC-FIELDS.                                             
            05  GTT-ROW.                                                
                49 GTT-ROW-LEN         PIC S9(04) COMP.                 
                49 GTT-ROW-CHAR        PIC X(1024).                     
            05  GTT-SQLCODE            PIC S9(9) COMP.                  
                                                                        
       01  COUNTER-FIELDS.                                              
           05  CTR-COLUMN              PIC S9(9) COMP VALUE 1.          
           05  CTR-ROWS                PIC S9(9) COMP VALUE 0.          
                                                                        
       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.                  
                                                                        
       01  TDS-RETURN-FIELDS.                                           
           05 RS-RETURN-CODE           PIC S9(09) COMP VALUE 0.         
           05 RS-APPL-RETURN-CODE      PIC S9(09) COMP VALUE 0.         
           05 RS-COMPANY-NO            PIC X(02)  VALUE SPACES.         
           05 RS-EMAIL-TYPE-CD         PIC X(03)  VALUE SPACES.         
           05 RS-EMAIL-SUBTYPE-CD      PIC X(03)  VALUE SPACES.         
           05 RS-VERSION-NO            PIC X(10).                       
           05 RS-VERSION-START-TS      PIC X(26).                       
           05 RS-STATUS-CD             PIC X(01)  VALUE SPACES.         
           05 RS-EMAIL-ENTITY          PIC X(255) VALUE SPACES.         
           05 RS-EMAIL-SUBJECT         PIC X(255) VALUE SPACES.         
           05 RS-EMAIL-BODY            PIC X(30000) VALUE SPACES.       
           05 RS-EMAIL-FROM-ADDR       PIC X(255) VALUE SPACES.         
           05 RS-EMAIL-TO-ADDR         PIC X(255) VALUE SPACES.         
           05 RS-EMAIL-CC-ADDR         PIC X(255) VALUE SPACES.         
           05 RS-EMAIL-BCC-ADDR        PIC X(255) VALUE SPACES.         
           05 RS-EMAIL-AUDIT-FL        PIC X(01)  VALUE SPACES.         
           05 RS-CREATED-TS            PIC X(26).                       
           05 RS-CREATED-BY-USERID     PIC X(07).                       
           05 RS-LAST-UPDATE-TS        PIC X(26).                       
           05 RS-LAST-UPDATE-USERID    PIC X(07).                       
                                                                        
       01  GTT-RETURN-FIELDS.                                           
           05 S-RETURN-CODE            PIC S9(09) COMP VALUE 0.         
           05 S-COMPANY-NO             PIC X(02).                       
           05 S-EMAIL-TYPE-CD          PIC X(03).                       
           05 S-EMAIL-SUBTYPE-CD       PIC X(03).                       
           05 S-VERSION-NO             PIC X(10).                       
           05 S-VERSION-START-TS       PIC X(26).                       
           05 S-STATUS-CD              PIC X(01).                       
           05 S-EMAIL-ENTITY.                                           
              49 S-EMAIL-ENTITY-LEN    PIC S9(4)  USAGE COMP.           
              49 S-EMAIL-ENTITY-TEXT   PIC X(255).                      
           05 S-EMAIL-SUBJECT.                                          
              49 S-EMAIL-SUBJECT-LEN   PIC S9(4)  USAGE COMP.           
              49 S-EMAIL-SUBJECT-TEXT  PIC X(255).                      
           05 S-EMAIL-BODY.                                             
              49 S-EMAIL-BODY-LEN      PIC S9(4)  USAGE COMP.           
              49 S-EMAIL-BODY-TEXT     PIC X(30000).                    
           05 S-EMAIL-FROM-ADDR.                                        
              49 S-EMAIL-FROM-ADDR-LEN PIC S9(4)  USAGE COMP.           
              49 S-EMAIL-FROM-ADDR-TEXT PIC X(255).                     
           05 S-EMAIL-TO-ADDR.                                          
              49 S-EMAIL-TO-ADDR-LEN   PIC S9(4)  USAGE COMP.           
              49 S-EMAIL-TO-ADDR-TEXT  PIC X(255).                      
           05 S-EMAIL-CC-ADDR.                                          
              49 S-EMAIL-CC-ADDR-LEN   PIC S9(4)  USAGE COMP.           
              49 S-EMAIL-CC-ADDR-TEXT  PIC X(255).                      
           05 S-EMAIL-BCC-ADDR.                                         
              49 S-EMAIL-BCC-ADDR-LEN  PIC S9(4)  USAGE COMP.           
              49 S-EMAIL-BCC-ADDR-TEXT PIC X(255).                      
           05 S-EMAIL-AUDIT-FL         PIC X(01).                       
           05 S-CREATED-TS             PIC X(26).                       
           05 S-CREATED-BY-USERID      PIC X(07).                       
           05 S-LAST-UPDATE-TS         PIC X(26).                       
           05 S-LAST-UPDATE-USERID     PIC X(07).                       
                                                                        
       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).                     
                                                                        
      ******************************************************************        
      *    CURSOR DECLARATION TO FETCH ALL EMAIL TEMPLATE DETAILS      *        
      *    VERSION NO GREATER THAN ZERO OR(VERSION_NO NOT GREATER THAN *        
      *    ZERO AND MAX_VERSION_FL NOT EQUAL 'Y' )                    *         
      ******************************************************************        
                                                                        
           EXEC SQL                                                     
               DECLARE EMAIL_TMPLT_CUR1 CURSOR FOR                      
                SELECT T2E.COMPANY_NO                                   
                      ,T2E.EMAIL_TYPE_CD                                
                      ,T2E.EMAIL_SUBTYPE_CD                             
                      ,T2E.VERSION_NO                                   
                      ,REPLACE(REPLACE(CONVERT(CHAR(26), 
           T2E.VERSION_START_TS, 121), ' ', '-'), ':', '.') 
           VERSION_START_TS                             
                      ,T2E.STATUS_CD                                    
                      ,T2E.EMAIL_ENTITY                                 
                      ,T2E.EMAIL_SUBJECT                                
                      ,T2E.EMAIL_BODY                                   
                      ,T2E.EMAIL_FROM_ADDR                              
                      ,T2E.EMAIL_TO_ADDR                                
                      ,T2E.EMAIL_CC_ADDR                                
                      ,T2E.EMAIL_BCC_ADDR                               
                      ,T2E.EMAIL_AUDIT_FL                               
                      ,REPLACE(REPLACE(CONVERT(CHAR(26), T2E.CREATED_TS
           , 121), ' ', '-'), ':', '.') CREATED_TS                             
                      ,T2E.CREATED_BY_USERID                            
                      ,REPLACE(REPLACE(CONVERT(CHAR(26), 
           T2E.LAST_UPDATE_TS, 121), ' ', '-'), ':', '.') 
           LAST_UPDATE_TS                               
                      ,T2E.LAST_UPDATE_USERID                           
                 FROM CSS_EMAIL_TEMPLATE T2E WITH(READUNCOMMITTED)              
                WHERE (                                                 
                        'Y'                = :WS-VERN-NO-PASSED         
                  AND T2E.COMPANY_NO       = :2E-COMPANY-NO             
                  AND T2E.EMAIL_TYPE_CD    = :2E-EMAIL-TYPE-CD          
                  AND T2E.EMAIL_SUBTYPE_CD = :2E-EMAIL-SUBTYPE-CD       
                  AND T2E.VERSION_NO       = :2E-VERSION-NO )           
                  OR                                                    
                      ( 'N'                = :WS-VERN-NO-PASSED         
                  AND   'Y'               != :WS-MAX-VERSION-FL         
                  AND T2E.COMPANY_NO       = :2E-COMPANY-NO             
                  AND T2E.EMAIL_TYPE_CD    = :2E-EMAIL-TYPE-CD          
                  AND T2E.EMAIL_SUBTYPE_CD = :2E-EMAIL-SUBTYPE-CD )     
                 FOR READ ONLY                                  
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ004
MFA-TR* MSQ022
MFA-TR* MSQ061
MFA-TR*    EXEC SQL                                                             
MFA-TR*        DECLARE EMAIL_TMPLT_CUR1 CURSOR FOR                              
MFA-TR*         SELECT T2E.COMPANY_NO                                           
MFA-TR*               ,T2E.EMAIL_TYPE_CD                                        
MFA-TR*               ,T2E.EMAIL_SUBTYPE_CD                                     
MFA-TR*               ,T2E.VERSION_NO                                           
MFA-TR*               ,T2E.VERSION_START_TS                                     
MFA-TR*               ,T2E.STATUS_CD                                            
MFA-TR*               ,T2E.EMAIL_ENTITY                                         
MFA-TR*               ,T2E.EMAIL_SUBJECT                                        
MFA-TR*               ,T2E.EMAIL_BODY                                           
MFA-TR*               ,T2E.EMAIL_FROM_ADDR                                      
MFA-TR*               ,T2E.EMAIL_TO_ADDR                                        
MFA-TR*               ,T2E.EMAIL_CC_ADDR                                        
MFA-TR*               ,T2E.EMAIL_BCC_ADDR                                       
MFA-TR*               ,T2E.EMAIL_AUDIT_FL                                       
MFA-TR*               ,T2E.CREATED_TS                                           
MFA-TR*               ,T2E.CREATED_BY_USERID                                    
MFA-TR*               ,T2E.LAST_UPDATE_TS                                       
MFA-TR*               ,T2E.LAST_UPDATE_USERID                                   
MFA-TR*          FROM CSS_EMAIL_TEMPLATE T2E                                    
MFA-TR*         WHERE (                                                         
MFA-TR*                 'Y'                = :WS-VERN-NO-PASSED                 
MFA-TR*           AND T2E.COMPANY_NO       = :2E-COMPANY-NO                     
MFA-TR*           AND T2E.EMAIL_TYPE_CD    = :2E-EMAIL-TYPE-CD                  
MFA-TR*           AND T2E.EMAIL_SUBTYPE_CD = :2E-EMAIL-SUBTYPE-CD               
MFA-TR*           AND T2E.VERSION_NO       = :2E-VERSION-NO )                   
MFA-TR*           OR                                                            
MFA-TR*               ( 'N'                = :WS-VERN-NO-PASSED                 
MFA-TR*           AND   'Y'               != :WS-MAX-VERSION-FL                 
MFA-TR*           AND T2E.COMPANY_NO       = :2E-COMPANY-NO                     
MFA-TR*           AND T2E.EMAIL_TYPE_CD    = :2E-EMAIL-TYPE-CD                  
MFA-TR*           AND T2E.EMAIL_SUBTYPE_CD = :2E-EMAIL-SUBTYPE-CD )             
MFA-TR*          FOR FETCH ONLY WITH UR                                         
MFA-TR*          QUERYNO 7100                                                   
MFA-TR*    END-EXEC.                                                            
                                                                        
      ******************************************************************        
      *    CURSOR DECLARATION TO FETCH ALL EMAIL TEMPLATE DETAILS      *        
      *    BASED ON VERSION_NO NOT > 0 AND MAX_VERSION_FL = 'Y'        *        
      ******************************************************************        
                                                                        
           EXEC SQL                                                     
               DECLARE EMAIL_TMPLT_CUR2 CURSOR FOR                      
                SELECT T2E.COMPANY_NO                                   
                      ,T2E.EMAIL_TYPE_CD                                
                      ,T2E.EMAIL_SUBTYPE_CD                             
                      ,T2E.VERSION_NO                                   
                      ,REPLACE(REPLACE(CONVERT(CHAR(26), 
           T2E.VERSION_START_TS, 121), ' ', '-'), ':', '.') 
           VERSION_START_TS                             
                      ,T2E.STATUS_CD                                    
                      ,T2E.EMAIL_ENTITY                                 
                      ,T2E.EMAIL_SUBJECT                                
                      ,T2E.EMAIL_BODY                                   
                      ,T2E.EMAIL_FROM_ADDR                              
                      ,T2E.EMAIL_TO_ADDR                                
                      ,T2E.EMAIL_CC_ADDR                                
                      ,T2E.EMAIL_BCC_ADDR                               
                      ,T2E.EMAIL_AUDIT_FL                               
                      ,REPLACE(REPLACE(CONVERT(CHAR(26), T2E.CREATED_TS
           , 121), ' ', '-'), ':', '.') CREATED_TS                             
                      ,T2E.CREATED_BY_USERID                            
                      ,REPLACE(REPLACE(CONVERT(CHAR(26), 
           T2E.LAST_UPDATE_TS, 121), ' ', '-'), ':', '.') 
           LAST_UPDATE_TS                               
                      ,T2E.LAST_UPDATE_USERID                           
                 FROM CSS_EMAIL_TEMPLATE T2E WITH(READUNCOMMITTED)              
                WHERE T2E.COMPANY_NO        = :2E-COMPANY-NO            
                  AND T2E.EMAIL_TYPE_CD     = :2E-EMAIL-TYPE-CD         
                  AND T2E.EMAIL_SUBTYPE_CD  = :2E-EMAIL-SUBTYPE-CD      
                  AND T2E.VERSION_NO =                                  
                         (SELECT MAX(B.VERSION_NO)                      
                           FROM CSS_EMAIL_TEMPLATE B
                           WITH(READUNCOMMITTED)                    
                          WHERE B.COMPANY_NO     = :2E-COMPANY-NO       
                          AND B.EMAIL_TYPE_CD    = :2E-EMAIL-TYPE-CD    
                          AND B.EMAIL_SUBTYPE_CD = :2E-EMAIL-SUBTYPE-CD 
                          AND B.STATUS_CD        = 'A'                  
                          AND B.VERSION_START_TS <= 
           CIS.CURRENT$TIMESTAMP())  
                  FOR READ ONLY                                 
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ004
MFA-TR* MSQ022
MFA-TR* MSQ028
MFA-TR* MSQ061
MFA-TR*    EXEC SQL                                                             
MFA-TR*        DECLARE EMAIL_TMPLT_CUR2 CURSOR FOR                              
MFA-TR*         SELECT T2E.COMPANY_NO                                           
MFA-TR*               ,T2E.EMAIL_TYPE_CD                                        
MFA-TR*               ,T2E.EMAIL_SUBTYPE_CD                                     
MFA-TR*               ,T2E.VERSION_NO                                           
MFA-TR*               ,T2E.VERSION_START_TS                                     
MFA-TR*               ,T2E.STATUS_CD                                            
MFA-TR*               ,T2E.EMAIL_ENTITY                                         
MFA-TR*               ,T2E.EMAIL_SUBJECT                                        
MFA-TR*               ,T2E.EMAIL_BODY                                           
MFA-TR*               ,T2E.EMAIL_FROM_ADDR                                      
MFA-TR*               ,T2E.EMAIL_TO_ADDR                                        
MFA-TR*               ,T2E.EMAIL_CC_ADDR                                        
MFA-TR*               ,T2E.EMAIL_BCC_ADDR                                       
MFA-TR*               ,T2E.EMAIL_AUDIT_FL                                       
MFA-TR*               ,T2E.CREATED_TS                                           
MFA-TR*               ,T2E.CREATED_BY_USERID                                    
MFA-TR*               ,T2E.LAST_UPDATE_TS                                       
MFA-TR*               ,T2E.LAST_UPDATE_USERID                                   
MFA-TR*          FROM CSS_EMAIL_TEMPLATE T2E                                    
MFA-TR*         WHERE T2E.COMPANY_NO        = :2E-COMPANY-NO                    
MFA-TR*           AND T2E.EMAIL_TYPE_CD     = :2E-EMAIL-TYPE-CD                 
MFA-TR*           AND T2E.EMAIL_SUBTYPE_CD  = :2E-EMAIL-SUBTYPE-CD              
MFA-TR*           AND T2E.VERSION_NO =                                          
MFA-TR*                  (SELECT MAX(B.VERSION_NO)                              
MFA-TR*                    FROM CSS_EMAIL_TEMPLATE B                            
MFA-TR*                   WHERE B.COMPANY_NO     = :2E-COMPANY-NO               
MFA-TR*                   AND B.EMAIL_TYPE_CD    = :2E-EMAIL-TYPE-CD            
MFA-TR*                   AND B.EMAIL_SUBTYPE_CD = :2E-EMAIL-SUBTYPE-CD         
MFA-TR*                   AND B.STATUS_CD        = 'A'                          
MFA-TR*                   AND B.VERSION_START_TS <= CURRENT TIMESTAMP)          
MFA-TR*           FOR FETCH ONLY WITH UR                                        
MFA-TR*           QUERYNO 7200                                                  
MFA-TR*    END-EXEC.                                                            
                                                                        
      ******************************************************************        
      * CURSOR DECLARATION TO FETCH ALL EMAIL TEMPLATE DETAILS WHEN    *        
      * COMPNAY NO AND EMAIL TYPE AND EMAIL SUB TYPE EQUAL TO SPACES   *        
      ******************************************************************        
                                                                        
           EXEC SQL                                                     
               DECLARE EMAIL_TMPLT_CUR3 CURSOR FOR                      
                SELECT T2E.COMPANY_NO                                   
                      ,T2E.EMAIL_TYPE_CD                                
                      ,T2E.EMAIL_SUBTYPE_CD                             
                      ,T2E.VERSION_NO                                   
                      ,REPLACE(REPLACE(CONVERT(CHAR(26), 
           T2E.VERSION_START_TS, 121), ' ', '-'), ':', '.') 
           VERSION_START_TS                             
                      ,T2E.STATUS_CD                                    
                      ,T2E.EMAIL_ENTITY                                 
                      ,T2E.EMAIL_SUBJECT                                
                      ,T2E.EMAIL_BODY                                   
                      ,T2E.EMAIL_FROM_ADDR                              
                      ,T2E.EMAIL_TO_ADDR                                
                      ,T2E.EMAIL_CC_ADDR                                
                      ,T2E.EMAIL_BCC_ADDR                               
                      ,T2E.EMAIL_AUDIT_FL                               
                      ,REPLACE(REPLACE(CONVERT(CHAR(26), T2E.CREATED_TS
           , 121), ' ', '-'), ':', '.') CREATED_TS                             
                      ,T2E.CREATED_BY_USERID                            
                      ,REPLACE(REPLACE(CONVERT(CHAR(26), 
           T2E.LAST_UPDATE_TS, 121), ' ', '-'), ':', '.') 
           LAST_UPDATE_TS                               
                      ,T2E.LAST_UPDATE_USERID                           
                 FROM CSS_EMAIL_TEMPLATE T2E WITH(READUNCOMMITTED)              
                ORDER BY  T2E.COMPANY_NO,T2E.EMAIL_TYPE_CD,             
                          T2E.EMAIL_SUBTYPE_CD,T2E.VERSION_NO DESC      
                  FOR READ ONLY                                 
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ004
MFA-TR* MSQ022
MFA-TR* MSQ061
MFA-TR*    EXEC SQL                                                             
MFA-TR*        DECLARE EMAIL_TMPLT_CUR3 CURSOR FOR                              
MFA-TR*         SELECT T2E.COMPANY_NO                                           
MFA-TR*               ,T2E.EMAIL_TYPE_CD                                        
MFA-TR*               ,T2E.EMAIL_SUBTYPE_CD                                     
MFA-TR*               ,T2E.VERSION_NO                                           
MFA-TR*               ,T2E.VERSION_START_TS                                     
MFA-TR*               ,T2E.STATUS_CD                                            
MFA-TR*               ,T2E.EMAIL_ENTITY                                         
MFA-TR*               ,T2E.EMAIL_SUBJECT                                        
MFA-TR*               ,T2E.EMAIL_BODY                                           
MFA-TR*               ,T2E.EMAIL_FROM_ADDR                                      
MFA-TR*               ,T2E.EMAIL_TO_ADDR                                        
MFA-TR*               ,T2E.EMAIL_CC_ADDR                                        
MFA-TR*               ,T2E.EMAIL_BCC_ADDR                                       
MFA-TR*               ,T2E.EMAIL_AUDIT_FL                                       
MFA-TR*               ,T2E.CREATED_TS                                           
MFA-TR*               ,T2E.CREATED_BY_USERID                                    
MFA-TR*               ,T2E.LAST_UPDATE_TS                                       
MFA-TR*               ,T2E.LAST_UPDATE_USERID                                   
MFA-TR*          FROM CSS_EMAIL_TEMPLATE T2E                                    
MFA-TR*         ORDER BY  T2E.COMPANY_NO,T2E.EMAIL_TYPE_CD,                     
MFA-TR*                   T2E.EMAIL_SUBTYPE_CD,T2E.VERSION_NO DESC              
MFA-TR*           FOR FETCH ONLY WITH UR                                        
MFA-TR*           QUERYNO 7300                                                  
MFA-TR*    END-EXEC.                                                            
      *                                                                         
       LINKAGE SECTION.                                                 
       01  PARM-APPL-PROG-ID           PIC X(03).                       
       01  PARM-USER-ID                PIC X(07).                       
       01  PARM-COMPANY-NO             PIC X(02).                       
       01  PARM-EMAIL-TYPE-CD          PIC X(03).                       
       01  PARM-EMAIL-SUBTYPE-CD       PIC X(03).                       
       01  PARM-MAX-VERSION-FL         PIC X(01).                       
       01  PARM-VERSION-NO             PIC X(10).                       
                                                                        
       PROCEDURE DIVISION  USING PARM-APPL-PROG-ID                      
                                 PARM-USER-ID                           
                                 PARM-COMPANY-NO                        
                                 PARM-EMAIL-TYPE-CD                     
                                 PARM-EMAIL-SUBTYPE-CD                  
                                 PARM-MAX-VERSION-FL                    
                                 PARM-VERSION-NO.                       
                                                                        
      ******************************************************************        
      * 0000-MAINLINE.                                                 *        
      ******************************************************************        
       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                                                *        
      ******************************************************************        
       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.              
                                                                        
       0100-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 0100A-DECLARE-GTT                                              *        
      ******************************************************************        
       0100A-DECLARE-GTT.                                               
                                                                        
           EXEC SQL
             CALL CIS.DROP_TEMP_TABLE('#CSR04525_R1')
           END-EXEC
           EXEC SQL
             CREATE TABLE #CSR04525_R1
              (                                                       
                    RETURN_CODE          INT                        
                   ,APPL_RETURN_CODE     INT                        
                   ,COMPANY_NO CHAR(02)  COLLATE LATIN1_GENERAL_100_BIN2        
                   ,EMAIL_TYPE_CD CHAR(03)  COLLATE 
                                 LATIN1_GENERAL_100_BIN2                       
                   ,EMAIL_SUBTYPE_CD CHAR(03)  COLLATE 
                                 LATIN1_GENERAL_100_BIN2                       
                   ,VERSION_NO CHAR(10)  COLLATE LATIN1_GENERAL_100_BIN2        
                   ,VERSION_START_TS CHAR(26)  COLLATE 
                                 LATIN1_GENERAL_100_BIN2                       
                   ,STATUS_CD CHAR(01)  COLLATE LATIN1_GENERAL_100_BIN2         
                   ,EMAIL_ENTITY VARCHAR(255)  COLLATE 
                                 LATIN1_GENERAL_100_BIN2                   
                   ,EMAIL_SUBJECT VARCHAR(255)  COLLATE 
                                 LATIN1_GENERAL_100_BIN2                   
                   ,EMAIL_BODY VARCHAR(MAX)  COLLATE 
                                 LATIN1_GENERAL_100_BIN2                 
                   ,EMAIL_FROM_ADDR VARCHAR(255)  COLLATE 
                                 LATIN1_GENERAL_100_BIN2                   
                   ,EMAIL_TO_ADDR VARCHAR(255)  COLLATE 
                                 LATIN1_GENERAL_100_BIN2                   
                   ,EMAIL_CC_ADDR VARCHAR(255)  COLLATE 
                                 LATIN1_GENERAL_100_BIN2                   
                   ,EMAIL_BCC_ADDR VARCHAR(255)  COLLATE 
                                 LATIN1_GENERAL_100_BIN2                   
                   ,EMAIL_AUDIT_FL CHAR(01)  COLLATE 
                                 LATIN1_GENERAL_100_BIN2                       
                   ,CREATED_TS CHAR(26)  COLLATE LATIN1_GENERAL_100_BIN2        
                   ,CREATED_BY_USERID CHAR(07)  COLLATE 
                                 LATIN1_GENERAL_100_BIN2                       
                   ,LAST_UPDATE_TS CHAR(26)  COLLATE 
                                 LATIN1_GENERAL_100_BIN2                       
                   ,LAST_UPDATE_USERID CHAR(07)  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-DEL-GTT-ROWS    THRU 8000A-EXIT             
           ELSE                                                         
              IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                
                 CONTINUE                                               
              ELSE                                                      
                 MOVE PROGRAM-NAME          TO ABEND-PROGRAM            
                 MOVE '0100'                TO ACTIVE-PARAGRAPH         
                 MOVE SQLCODE               TO ABEND-SQLCODE            
                 MOVE SQLSTATE              TO ABEND-SQLSTATE           
                 MOVE 'DECLARE GTT'         TO ABEND-FUNCTION           
                 MOVE SPACES                TO ABEND-SQL-PREDICATES     
                                               ABEND-TABLES             
                 MOVE GTT-NAME              TO TABLE-1                  
                 MOVE SPACES                TO TABLE-ELEMENT-1          
                 MOVE SPACES                TO HOSTVAR-ELEMENT-1        
                 PERFORM 9700-PROCESS-ABEND    THRU 9700-EXIT           
              END-IF                                                    
           END-IF.                                                      
                                                                        
           EXEC SQL                                                     
               DECLARE C1 CURSOR                             
                                 WITH ROWSET POSITIONING FOR            
                SELECT RETURN_CODE                                      
                      ,APPL_RETURN_CODE                                 
                      ,LTRIM(RTRIM(COMPANY_NO))         AS COMPANY_NO          
                      ,LTRIM(RTRIM(EMAIL_TYPE_CD))      AS EMAIL_TYPE_CD       
                      ,LTRIM(RTRIM(EMAIL_SUBTYPE_CD))   AS 
           EMAIL_SUBTYPE_CD    
                      ,VERSION_NO                AS VERSION_NO          
                      ,LTRIM(RTRIM(VERSION_START_TS))   AS 
           VERSION_START_TS    
                      ,LTRIM(RTRIM(STATUS_CD))          AS STATUS_CD           
                      ,LTRIM(RTRIM(EMAIL_ENTITY))       AS EMAIL_ENTITY        
                      ,LTRIM(RTRIM(EMAIL_SUBJECT))      AS EMAIL_SUBJECT       
                      ,LTRIM(RTRIM(EMAIL_BODY))         AS EMAIL_BODY          
                      ,LTRIM(RTRIM(EMAIL_FROM_ADDR))    AS 
           EMAIL_FROM_ADDR     
                      ,LTRIM(RTRIM(EMAIL_TO_ADDR))      AS EMAIL_TO_ADDR       
                      ,LTRIM(RTRIM(EMAIL_CC_ADDR))      AS EMAIL_CC_ADDR       
                      ,LTRIM(RTRIM(EMAIL_BCC_ADDR))     AS 
           EMAIL_BCC_ADDR      
                      ,LTRIM(RTRIM(EMAIL_AUDIT_FL))     AS 
           EMAIL_AUDIT_FL      
                      ,LTRIM(RTRIM(CREATED_TS))         AS CREATED_TS          
                      ,LTRIM(RTRIM(CREATED_BY_USERID))  AS 
           CREATED_BY_USERID   
                      ,LTRIM(RTRIM(LAST_UPDATE_TS))     AS 
           LAST_UPDATE_TS      
                      ,LTRIM(RTRIM(LAST_UPDATE_USERID)) AS 
           LAST_UPDATE_USERID  
                  FROM #CSR04525_R1                              
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ029
MFA-TR* MSQ049
MFA-TR*    EXEC SQL                                                             
MFA-TR*        DECLARE C1 CURSOR WITH RETURN                                    
MFA-TR*                          WITH ROWSET POSITIONING FOR                    
MFA-TR*         SELECT RETURN_CODE                                              
MFA-TR*               ,APPL_RETURN_CODE                                         
MFA-TR*               ,STRIP(COMPANY_NO)         AS COMPANY_NO                  
MFA-TR*               ,STRIP(EMAIL_TYPE_CD)      AS EMAIL_TYPE_CD               
MFA-TR*               ,STRIP(EMAIL_SUBTYPE_CD)   AS EMAIL_SUBTYPE_CD            
MFA-TR*               ,VERSION_NO                AS VERSION_NO                  
MFA-TR*               ,STRIP(VERSION_START_TS)   AS VERSION_START_TS            
MFA-TR*               ,STRIP(STATUS_CD)          AS STATUS_CD                   
MFA-TR*               ,STRIP(EMAIL_ENTITY)       AS EMAIL_ENTITY                
MFA-TR*               ,STRIP(EMAIL_SUBJECT)      AS EMAIL_SUBJECT               
MFA-TR*               ,STRIP(EMAIL_BODY)         AS EMAIL_BODY                  
MFA-TR*               ,STRIP(EMAIL_FROM_ADDR)    AS EMAIL_FROM_ADDR             
MFA-TR*               ,STRIP(EMAIL_TO_ADDR)      AS EMAIL_TO_ADDR               
MFA-TR*               ,STRIP(EMAIL_CC_ADDR)      AS EMAIL_CC_ADDR               
MFA-TR*               ,STRIP(EMAIL_BCC_ADDR)     AS EMAIL_BCC_ADDR              
MFA-TR*               ,STRIP(EMAIL_AUDIT_FL)     AS EMAIL_AUDIT_FL              
MFA-TR*               ,STRIP(CREATED_TS)         AS CREATED_TS                  
MFA-TR*               ,STRIP(CREATED_BY_USERID)  AS CREATED_BY_USERID           
MFA-TR*               ,STRIP(LAST_UPDATE_TS)     AS LAST_UPDATE_TS              
MFA-TR*               ,STRIP(LAST_UPDATE_USERID) AS LAST_UPDATE_USERID          
MFA-TR*           FROM SESSION.CSR04525_R1                                      
MFA-TR*    END-EXEC.                                                            
                                                                        
       0100A-EXIT.                                                      
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 1000-PROCESS-INPUT                                             *        
      ******************************************************************        
       1000-PROCESS-INPUT.                                              
                                                                        
           PERFORM 1100-VALIDATE-INPUT         THRU 1100-EXIT.          
                                                                        
           MOVE PARM-COMPANY-NO             TO WS-COMPANY-NO            
                                               2E-COMPANY-NO.           
           MOVE PARM-EMAIL-TYPE-CD          TO WS-EMAIL-TYPE-CD         
                                               2E-EMAIL-TYPE-CD.        
           MOVE PARM-EMAIL-SUBTYPE-CD       TO WS-EMAIL-SUBTYPE-CD      
                                               2E-EMAIL-SUBTYPE-CD.     
           MOVE PARM-MAX-VERSION-FL         TO WS-MAX-VERSION-FL.       
MAN012*    MOVE PARM-VERSION-NO             TO WS-VERSION-NO.           
MAN012     UNSTRING PARM-VERSION-NO DELIMITED BY SPACE 
MAN012              INTO WS-VERSION-NO. 
MAN012     INSPECT WS-VERSION-NO REPLACING LEADING SPACE BY ZERO.
           MOVE WS-VERSION-NO-RED           TO 2E-VERSION-NO.           
                                                                        
           IF 2E-VERSION-NO > 0                                         
              MOVE WS-YES   TO WS-VERN-NO-PASSED                        
           END-IF.                                                      
                                                                        
       1000-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * VALIDATION FOR INPUT VARIABLES                                *         
      *****************************************************************         
       1100-VALIDATE-INPUT.                                             
                                                                        
           IF PARM-COMPANY-NO       > SPACES  AND                       
             (PARM-EMAIL-TYPE-CD    = SPACES  OR                        
              PARM-EMAIL-SUBTYPE-CD = SPACES )                          
                                                                        
              MOVE PROGRAM-NAME          TO ABEND-PROGRAM               
              MOVE '1100'                TO ACTIVE-PARAGRAPH            
              MOVE 'RECEIVE INPUT'       TO ABEND-FUNCTION              
              MOVE SPACES                TO ABEND-SQL-PREDICATES        
                                            ABEND-TABLES                
              MOVE SPACES                TO TABLE-1                     
              MOVE SPACES                TO TABLE-ELEMENT-1             
              MOVE SPACES                TO HOSTVAR-ELEMENT-1           
      *************************************************************             
      *      IF INPUT IS INVALID                                  *             
      *************************************************************             
              MOVE +0                    TO WS-ACTIVE-RETURN-CODE       
              MOVE +1000                 TO S-APPL-RETURN-CODE          
              PERFORM 9700-PROCESS-ABEND THRU 9700-EXIT                 
           END-IF.                                                      
                                                                        
       1100-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 2000-PROCESS-OUTPUT.                                           *        
      ******************************************************************        
       2000-PROCESS-OUTPUT.                                             
                                                                        
           MOVE WS-NO           TO WS-NO-MORE-ROWS.                     
                                                                        
           IF WS-COMPANY-NO = SPACES AND WS-EMAIL-TYPE-CD = SPACES      
              AND WS-EMAIL-SUBTYPE-CD = SPACES                          
                                                                        
              PERFORM 7300-OPEN-EMAIL-TMPLT-CUR3  THRU 7300-EXIT        
              PERFORM 7310-FETCH-EMAIL-TMPLT-CUR3 THRU 7310-EXIT        
              PERFORM UNTIL NO-MORE-ROWS                                
                PERFORM 2100-MOVE-EMAIL-TEMPLATE  THRU 2100-EXIT        
                PERFORM 7310-FETCH-EMAIL-TMPLT-CUR3                     
                                                  THRU 7310-EXIT        
              END-PERFORM                                               
              PERFORM 7320-CLSOE-EMAIL-TMPLT-CUR3 THRU 7320-EXIT        
                                                                        
           ELSE                                                         
                                                                        
              IF WS-VERSION-NO-RED > 0 OR ( WS-VERSION-NO-RED = 0 AND   
                 WS-MAX-VERSION-FL NOT = WS-YES )                       
                                                                        
                 PERFORM 7100-OPEN-EMAIL-TMPLT-CUR1  THRU 7100-EXIT     
                 PERFORM 7110-FETCH-EMAIL-TMPLT-CUR1 THRU 7110-EXIT     
                 PERFORM UNTIL NO-MORE-ROWS                             
                   PERFORM 2100-MOVE-EMAIL-TEMPLATE  THRU 2100-EXIT     
                   PERFORM 7110-FETCH-EMAIL-TMPLT-CUR1                  
                                                     THRU 7110-EXIT     
                 END-PERFORM                                            
                 PERFORM 7120-CLSOE-EMAIL-TMPLT-CUR1 THRU 7120-EXIT     
                                                                        
              ELSE                                                      
                IF WS-VERSION-NO-RED = 0 AND WS-MAX-VERSION-FL = WS-YES 
                                                                        
                    PERFORM 7200-OPEN-EMAIL-TMPLT-CUR2  THRU 7200-EXIT  
                    PERFORM 7210-FETCH-EMAIL-TMPLT-CUR2 THRU 7210-EXIT  
                    PERFORM UNTIL NO-MORE-ROWS                          
                      PERFORM 2100-MOVE-EMAIL-TEMPLATE  THRU 2100-EXIT  
                      PERFORM 7210-FETCH-EMAIL-TMPLT-CUR2               
                                                        THRU 7210-EXIT  
                    END-PERFORM                                         
                    PERFORM 7220-CLSOE-EMAIL-TMPLT-CUR2 THRU 7220-EXIT  
                                                                        
              END-IF                                                    
           END-IF
           END-IF.                                                      
                                                                        
           IF CTR-ROWS EQUAL ZERO                                       
              MOVE 100                        TO S-RETURN-CODE          
              MOVE   0                        TO S-APPL-RETURN-CODE     
              PERFORM 8100-SEND-RESULT           THRU 8100-EXIT         
           END-IF.                                                      
                                                                        
       2000-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 2000A-MOVE-RESULT.                                             *        
      ******************************************************************        
       2000A-MOVE-RESULT.                                               
                                                                        
           MOVE RS-COMPANY-NO               TO S-COMPANY-NO             
           MOVE RS-EMAIL-TYPE-CD            TO S-EMAIL-TYPE-CD          
           MOVE RS-EMAIL-SUBTYPE-CD         TO S-EMAIL-SUBTYPE-CD       
           MOVE RS-VERSION-NO               TO S-VERSION-NO             
           MOVE RS-VERSION-START-TS         TO S-VERSION-START-TS       
           MOVE RS-STATUS-CD                TO S-STATUS-CD              
           MOVE RS-EMAIL-ENTITY             TO S-EMAIL-ENTITY           
           MOVE RS-EMAIL-SUBJECT            TO S-EMAIL-SUBJECT          
           MOVE RS-EMAIL-BODY               TO S-EMAIL-BODY             
           MOVE RS-EMAIL-FROM-ADDR          TO S-EMAIL-FROM-ADDR        
           MOVE RS-EMAIL-TO-ADDR            TO S-EMAIL-TO-ADDR          
           MOVE RS-EMAIL-CC-ADDR            TO S-EMAIL-CC-ADDR          
           MOVE RS-EMAIL-BCC-ADDR           TO S-EMAIL-BCC-ADDR         
           MOVE RS-EMAIL-AUDIT-FL           TO S-EMAIL-AUDIT-FL         
           MOVE RS-CREATED-TS               TO S-CREATED-TS             
           MOVE RS-CREATED-BY-USERID        TO S-CREATED-BY-USERID      
           MOVE RS-LAST-UPDATE-TS           TO S-LAST-UPDATE-TS         
           MOVE RS-LAST-UPDATE-USERID       TO S-LAST-UPDATE-USERID.    
                                                                        
       2000A-EXIT.                                                      
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * 2100-PROCESS-CUR1.                                            *         
      *****************************************************************         
       2100-MOVE-EMAIL-TEMPLATE.                                        
                                                                        
           MOVE 2E-COMPANY-NO               TO RS-COMPANY-NO            
           MOVE 2E-EMAIL-TYPE-CD            TO RS-EMAIL-TYPE-CD         
           MOVE 2E-EMAIL-SUBTYPE-CD         TO RS-EMAIL-SUBTYPE-CD      
           MOVE 2E-VERSION-NO               TO RS-VERSION-NO            
           MOVE 2E-VERSION-START-TS         TO RS-VERSION-START-TS      
           MOVE 2E-STATUS-CD                TO RS-STATUS-CD             
           MOVE 2E-EMAIL-ENTITY             TO RS-EMAIL-ENTITY          
           MOVE 2E-EMAIL-SUBJECT            TO RS-EMAIL-SUBJECT         
           MOVE 2E-EMAIL-BODY               TO RS-EMAIL-BODY            
           MOVE 2E-EMAIL-FROM-ADDR          TO RS-EMAIL-FROM-ADDR       
           MOVE 2E-EMAIL-TO-ADDR            TO RS-EMAIL-TO-ADDR         
           MOVE 2E-EMAIL-CC-ADDR            TO RS-EMAIL-CC-ADDR         
           MOVE 2E-EMAIL-BCC-ADDR           TO RS-EMAIL-BCC-ADDR        
           MOVE 2E-EMAIL-AUDIT-FL           TO RS-EMAIL-AUDIT-FL        
           MOVE 2E-CREATED-TS               TO RS-CREATED-TS            
           MOVE 2E-CREATED-BY-USERID        TO RS-CREATED-BY-USERID     
           MOVE 2E-LAST-UPDATE-TS           TO RS-LAST-UPDATE-TS        
           MOVE 2E-LAST-UPDATE-USERID       TO RS-LAST-UPDATE-USERID.   
                                                                        
           PERFORM 2000A-MOVE-RESULT           THRU 2000A-EXIT          
           PERFORM 8100-SEND-RESULT            THRU 8100-EXIT.          
                                                                        
       2100-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      *  7100-OPEN-EMAIL-TMPLT-CUR.                                    *        
      ******************************************************************        
       7100-OPEN-EMAIL-TMPLT-CUR1.                                      
                                                                        
           EXEC SQL                                                     
                OPEN EMAIL_TMPLT_CUR1                                   
           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                   
              CONTINUE                                                  
           ELSE                                                         
              MOVE WS-ACTIVE-RETURN-CODE TO RS-RETURN-CODE              
              MOVE PROGRAM-NAME          TO ABEND-PROGRAM               
              MOVE '7100'                TO ACTIVE-PARAGRAPH            
              MOVE 'OPEN'                TO ABEND-FUNCTION              
              MOVE SPACES                TO ABEND-SQL-PREDICATES        
                                            ABEND-TABLES                
              MOVE 'CSS_EMAIL_TEMPLATE'  TO TABLE-1                     
              MOVE 'COMPANY_NO'          TO TABLE-ELEMENT-1             
              MOVE 'EMAIL_TYPE_CD'       TO TABLE-ELEMENT-2             
              MOVE 'EMAIL_SUBTYPE_CD'    TO TABLE-ELEMENT-3             
              MOVE 2E-COMPANY-NO         TO HOSTVAR-ELEMENT-1           
              MOVE 2E-EMAIL-TYPE-CD      TO HOSTVAR-ELEMENT-2           
              MOVE 2E-EMAIL-SUBTYPE-CD   TO HOSTVAR-ELEMENT-3           
              PERFORM 9700-PROCESS-ABEND THRU 9700-EXIT                 
           END-IF.                                                      
                                                                        
       7100-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7110-FETCH-EMAIL-TMPLT-CUR1.                                   *        
      ******************************************************************        
       7110-FETCH-EMAIL-TMPLT-CUR1.                                     
                                                                        
           EXEC SQL                                                     
               FETCH EMAIL_TMPLT_CUR1                                   
                INTO :2E-COMPANY-NO                                     
                    ,:2E-EMAIL-TYPE-CD                                  
                    ,:2E-EMAIL-SUBTYPE-CD                               
                    ,:2E-VERSION-NO                                     
                    ,:2E-VERSION-START-TS                               
                    ,:2E-STATUS-CD                                      
                    ,:2E-EMAIL-ENTITY                                   
                    ,:2E-EMAIL-SUBJECT                                  
                    ,:2E-EMAIL-BODY                                     
                    ,:2E-EMAIL-FROM-ADDR                                
                    ,:2E-EMAIL-TO-ADDR                                  
                    ,:2E-EMAIL-CC-ADDR                                  
                    ,:2E-EMAIL-BCC-ADDR                                 
                    ,:2E-EMAIL-AUDIT-FL                                 
                    ,:2E-CREATED-TS                                     
                    ,:2E-CREATED-BY-USERID                              
                    ,:2E-LAST-UPDATE-TS                                 
                    ,:2E-LAST-UPDATE-USERID                             
           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 
                                                                        
           EVALUATE WS-ACTIVE-RETURN-CODE                               
               WHEN SUCCESSFUL-CALL                                     
                    CONTINUE                                            
               WHEN NOT-FOUND                                           
                    MOVE WS-YES                TO WS-NO-MORE-ROWS       
               WHEN OTHER                                               
                    MOVE WS-ACTIVE-RETURN-CODE TO RS-RETURN-CODE        
                    MOVE PROGRAM-NAME          TO ABEND-PROGRAM         
                    MOVE '7110'                TO ACTIVE-PARAGRAPH      
                    MOVE 'FETCH'               TO ABEND-FUNCTION        
                    MOVE SPACES                TO ABEND-SQL-PREDICATES  
                                                  ABEND-TABLES          
                    MOVE 'CSS_EMAIL_TEMPLATE'  TO TABLE-1               
                    MOVE 'COMPANY_NO'          TO TABLE-ELEMENT-1       
                    MOVE 'EMAIL_TYPE_CD'       TO TABLE-ELEMENT-2       
                    MOVE 'EMAIL_SUBTYPE_CD'    TO TABLE-ELEMENT-3       
                    MOVE 2E-COMPANY-NO         TO HOSTVAR-ELEMENT-1     
                    MOVE 2E-EMAIL-TYPE-CD      TO HOSTVAR-ELEMENT-2     
                    MOVE 2E-EMAIL-SUBTYPE-CD   TO HOSTVAR-ELEMENT-3     
                    PERFORM 9700-PROCESS-ABEND THRU 9700-EXIT           
           END-EVALUATE.                                                
                                                                        
       7110-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7120-CLSOE-EMAIL-TMPLT-CUR1.                                   *        
      ******************************************************************        
       7120-CLSOE-EMAIL-TMPLT-CUR1.                                     
                                                                        
           EXEC SQL                                                     
                CLOSE EMAIL_TMPLT_CUR1                                  
           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                   
              CONTINUE                                                  
           ELSE                                                         
              MOVE WS-ACTIVE-RETURN-CODE    TO RS-RETURN-CODE           
              MOVE PROGRAM-NAME             TO ABEND-PROGRAM            
              MOVE '7120'                   TO ACTIVE-PARAGRAPH         
              MOVE 'CLOSE'                  TO ABEND-FUNCTION           
              MOVE SPACES                   TO ABEND-SQL-PREDICATES     
                                               ABEND-TABLES             
              MOVE 'CSS_EMAIL_TEMPLATE'     TO TABLE-1                  
              MOVE 'COMPANY_NO'             TO TABLE-ELEMENT-1          
              MOVE 'EMAIL_TYPE_CD'          TO TABLE-ELEMENT-2          
              MOVE 'EMAIL_SUBTYPE_CD'       TO TABLE-ELEMENT-3          
              MOVE 2E-COMPANY-NO            TO HOSTVAR-ELEMENT-1        
              MOVE 2E-EMAIL-TYPE-CD         TO HOSTVAR-ELEMENT-2        
              MOVE 2E-EMAIL-SUBTYPE-CD      TO HOSTVAR-ELEMENT-3        
              PERFORM 9700-PROCESS-ABEND       THRU 9700-EXIT           
           END-IF.                                                      
                                                                        
       7120-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      *  7200-OPEN-EMAIL-TMPLT-CUR2.                                   *        
      ******************************************************************        
       7200-OPEN-EMAIL-TMPLT-CUR2.                                      
                                                                        
           EXEC SQL                                                     
                OPEN EMAIL_TMPLT_CUR2                                   
           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                   
              CONTINUE                                                  
           ELSE                                                         
              MOVE WS-ACTIVE-RETURN-CODE TO RS-RETURN-CODE              
              MOVE PROGRAM-NAME          TO ABEND-PROGRAM               
              MOVE '7200'                TO ACTIVE-PARAGRAPH            
              MOVE 'OPEN'                TO ABEND-FUNCTION              
              MOVE SPACES                TO ABEND-SQL-PREDICATES        
                                            ABEND-TABLES                
              MOVE 'CSS_EMAIL_TEMPLATE'  TO TABLE-1                     
              MOVE 'COMPANY_NO'          TO TABLE-ELEMENT-1             
              MOVE 'EMAIL_TYPE_CD'       TO TABLE-ELEMENT-2             
              MOVE 'EMAIL_SUBTYPE_CD'    TO TABLE-ELEMENT-3             
              MOVE 2E-COMPANY-NO         TO HOSTVAR-ELEMENT-1           
              MOVE 2E-EMAIL-TYPE-CD      TO HOSTVAR-ELEMENT-2           
              MOVE 2E-EMAIL-SUBTYPE-CD   TO HOSTVAR-ELEMENT-3           
              PERFORM 9700-PROCESS-ABEND THRU 9700-EXIT                 
           END-IF.                                                      
                                                                        
       7200-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7210-FETCH-EMAIL-TMPLT-CUR2.                                   *        
      ******************************************************************        
       7210-FETCH-EMAIL-TMPLT-CUR2.                                     
                                                                        
           EXEC SQL                                                     
               FETCH EMAIL_TMPLT_CUR2                                   
                INTO :2E-COMPANY-NO                                     
                    ,:2E-EMAIL-TYPE-CD                                  
                    ,:2E-EMAIL-SUBTYPE-CD                               
                    ,:2E-VERSION-NO                                     
                    ,:2E-VERSION-START-TS                               
                    ,:2E-STATUS-CD                                      
                    ,:2E-EMAIL-ENTITY                                   
                    ,:2E-EMAIL-SUBJECT                                  
                    ,:2E-EMAIL-BODY                                     
                    ,:2E-EMAIL-FROM-ADDR                                
                    ,:2E-EMAIL-TO-ADDR                                  
                    ,:2E-EMAIL-CC-ADDR                                  
                    ,:2E-EMAIL-BCC-ADDR                                 
                    ,:2E-EMAIL-AUDIT-FL                                 
                    ,:2E-CREATED-TS                                     
                    ,:2E-CREATED-BY-USERID                              
                    ,:2E-LAST-UPDATE-TS                                 
                    ,:2E-LAST-UPDATE-USERID                             
           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 
                                                                        
           EVALUATE WS-ACTIVE-RETURN-CODE                               
               WHEN SUCCESSFUL-CALL                                     
                    CONTINUE                                            
               WHEN NOT-FOUND                                           
                    MOVE WS-YES                TO WS-NO-MORE-ROWS       
               WHEN OTHER                                               
                    MOVE WS-ACTIVE-RETURN-CODE TO RS-RETURN-CODE        
                    MOVE PROGRAM-NAME          TO ABEND-PROGRAM         
                    MOVE '7210'                TO ACTIVE-PARAGRAPH      
                    MOVE 'FETCH'               TO ABEND-FUNCTION        
                    MOVE SPACES                TO ABEND-SQL-PREDICATES  
                                                  ABEND-TABLES          
                    MOVE 'CSS_EMAIL_TEMPLATE'  TO TABLE-1               
                    MOVE 'COMPANY_NO'          TO TABLE-ELEMENT-1       
                    MOVE 'EMAIL_TYPE_CD'       TO TABLE-ELEMENT-2       
                    MOVE 'EMAIL_SUBTYPE_CD'    TO TABLE-ELEMENT-3       
                    MOVE 2E-COMPANY-NO         TO HOSTVAR-ELEMENT-1     
                    MOVE 2E-EMAIL-TYPE-CD      TO HOSTVAR-ELEMENT-2     
                    MOVE 2E-EMAIL-SUBTYPE-CD   TO HOSTVAR-ELEMENT-3     
                    PERFORM 9700-PROCESS-ABEND THRU 9700-EXIT           
           END-EVALUATE.                                                
                                                                        
       7210-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7220-CLSOE-EMAIL-TMPLT-CUR2.                                   *        
      ******************************************************************        
       7220-CLSOE-EMAIL-TMPLT-CUR2.                                     
                                                                        
           EXEC SQL                                                     
                CLOSE EMAIL_TMPLT_CUR2                                  
           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                   
              CONTINUE                                                  
           ELSE                                                         
              MOVE WS-ACTIVE-RETURN-CODE    TO RS-RETURN-CODE           
              MOVE PROGRAM-NAME             TO ABEND-PROGRAM            
              MOVE '7220'                   TO ACTIVE-PARAGRAPH         
              MOVE 'CLOSE'                  TO ABEND-FUNCTION           
              MOVE SPACES                   TO ABEND-SQL-PREDICATES     
                                               ABEND-TABLES             
              MOVE 'CSS_EMAIL_TEMPLATE'     TO TABLE-1                  
              MOVE 'COMPANY_NO'             TO TABLE-ELEMENT-1          
              MOVE 'EMAIL_TYPE_CD'          TO TABLE-ELEMENT-2          
              MOVE 'EMAIL_SUBTYPE_CD'       TO TABLE-ELEMENT-3          
              MOVE 2E-COMPANY-NO            TO HOSTVAR-ELEMENT-1        
              MOVE 2E-EMAIL-TYPE-CD         TO HOSTVAR-ELEMENT-2        
              MOVE 2E-EMAIL-SUBTYPE-CD      TO HOSTVAR-ELEMENT-3        
              PERFORM 9700-PROCESS-ABEND       THRU 9700-EXIT           
           END-IF.                                                      
                                                                        
       7220-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      *  7300-OPEN-EMAIL-TMPLT-CUR3.                                   *        
      ******************************************************************        
       7300-OPEN-EMAIL-TMPLT-CUR3.                                      
                                                                        
           EXEC SQL                                                     
                OPEN EMAIL_TMPLT_CUR3                                   
           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                   
              CONTINUE                                                  
           ELSE                                                         
              MOVE WS-ACTIVE-RETURN-CODE TO RS-RETURN-CODE              
              MOVE PROGRAM-NAME          TO ABEND-PROGRAM               
              MOVE '7300'                TO ACTIVE-PARAGRAPH            
              MOVE 'OPEN'                TO ABEND-FUNCTION              
              MOVE SPACES                TO ABEND-SQL-PREDICATES        
                                            ABEND-TABLES                
              MOVE 'CSS_EMAIL_TEMPLATE'  TO TABLE-1                     
              MOVE 'COMPANY_NO'          TO TABLE-ELEMENT-1             
              MOVE 'EMAIL_TYPE_CD'       TO TABLE-ELEMENT-2             
              MOVE 'EMAIL_SUBTYPE_CD'    TO TABLE-ELEMENT-3             
              MOVE 2E-COMPANY-NO         TO HOSTVAR-ELEMENT-1           
              MOVE 2E-EMAIL-TYPE-CD      TO HOSTVAR-ELEMENT-2           
              MOVE 2E-EMAIL-SUBTYPE-CD   TO HOSTVAR-ELEMENT-3           
              PERFORM 9700-PROCESS-ABEND THRU 9700-EXIT                 
           END-IF.                                                      
                                                                        
       7300-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7310-FETCH-EMAIL-TMPLT-CUR3.                                   *        
      ******************************************************************        
       7310-FETCH-EMAIL-TMPLT-CUR3.                                     
                                                                        
           EXEC SQL                                                     
               FETCH EMAIL_TMPLT_CUR3                                   
                INTO :2E-COMPANY-NO                                     
                    ,:2E-EMAIL-TYPE-CD                                  
                    ,:2E-EMAIL-SUBTYPE-CD                               
                    ,:2E-VERSION-NO                                     
                    ,:2E-VERSION-START-TS                               
                    ,:2E-STATUS-CD                                      
                    ,:2E-EMAIL-ENTITY                                   
                    ,:2E-EMAIL-SUBJECT                                  
                    ,:2E-EMAIL-BODY                                     
                    ,:2E-EMAIL-FROM-ADDR                                
                    ,:2E-EMAIL-TO-ADDR                                  
                    ,:2E-EMAIL-CC-ADDR                                  
                    ,:2E-EMAIL-BCC-ADDR                                 
                    ,:2E-EMAIL-AUDIT-FL                                 
                    ,:2E-CREATED-TS                                     
                    ,:2E-CREATED-BY-USERID                              
                    ,:2E-LAST-UPDATE-TS                                 
                    ,:2E-LAST-UPDATE-USERID                             
           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 
                                                                        
           EVALUATE WS-ACTIVE-RETURN-CODE                               
               WHEN SUCCESSFUL-CALL                                     
                    CONTINUE                                            
               WHEN NOT-FOUND                                           
                    MOVE WS-YES                TO WS-NO-MORE-ROWS       
               WHEN OTHER                                               
                    MOVE WS-ACTIVE-RETURN-CODE TO RS-RETURN-CODE        
                    MOVE PROGRAM-NAME          TO ABEND-PROGRAM         
                    MOVE '7310'                TO ACTIVE-PARAGRAPH      
                    MOVE 'FETCH'               TO ABEND-FUNCTION        
                    MOVE SPACES                TO ABEND-SQL-PREDICATES  
                                                  ABEND-TABLES          
                    MOVE 'CSS_EMAIL_TEMPLATE'  TO TABLE-1               
                    MOVE 'COMPANY_NO'          TO TABLE-ELEMENT-1       
                    MOVE 'EMAIL_TYPE_CD'       TO TABLE-ELEMENT-2       
                    MOVE 'EMAIL_SUBTYPE_CD'    TO TABLE-ELEMENT-3       
                    MOVE 2E-COMPANY-NO         TO HOSTVAR-ELEMENT-1     
                    MOVE 2E-EMAIL-TYPE-CD      TO HOSTVAR-ELEMENT-2     
                    MOVE 2E-EMAIL-SUBTYPE-CD   TO HOSTVAR-ELEMENT-3     
                    PERFORM 9700-PROCESS-ABEND THRU 9700-EXIT           
           END-EVALUATE.                                                
                                                                        
       7310-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7320-CLSOE-EMAIL-TMPLT-CUR3.                                   *        
      ******************************************************************        
       7320-CLSOE-EMAIL-TMPLT-CUR3.                                     
                                                                        
           EXEC SQL                                                     
                CLOSE EMAIL_TMPLT_CUR3                                  
           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                   
              CONTINUE                                                  
           ELSE                                                         
              MOVE WS-ACTIVE-RETURN-CODE    TO RS-RETURN-CODE           
              MOVE PROGRAM-NAME             TO ABEND-PROGRAM            
              MOVE '7320'                   TO ACTIVE-PARAGRAPH         
              MOVE 'CLOSE'                  TO ABEND-FUNCTION           
              MOVE SPACES                   TO ABEND-SQL-PREDICATES     
                                               ABEND-TABLES             
              MOVE 'CSS_EMAIL_TEMPLATE'     TO TABLE-1                  
              MOVE 'COMPANY_NO'             TO TABLE-ELEMENT-1          
              MOVE 'EMAIL_TYPE_CD'          TO TABLE-ELEMENT-2          
              MOVE 'EMAIL_SUBTYPE_CD'       TO TABLE-ELEMENT-3          
              MOVE 2E-COMPANY-NO            TO HOSTVAR-ELEMENT-1        
              MOVE 2E-EMAIL-TYPE-CD         TO HOSTVAR-ELEMENT-2        
              MOVE 2E-EMAIL-SUBTYPE-CD      TO HOSTVAR-ELEMENT-3        
              PERFORM 9700-PROCESS-ABEND       THRU 9700-EXIT           
           END-IF.                                                      
                                                                        
       7320-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * 8000A-DEL-GTT-ROWS.                                           *         
      *****************************************************************         
       8000A-DEL-GTT-ROWS.                                              
                                                                        
           EXEC SQL                                                     
               DELETE FROM #CSR04525_R1                          
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ049
MFA-TR*    EXEC SQL                                                             
MFA-TR*        DELETE FROM SESSION.CSR04525_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    
                                                                        
           EVALUATE WS-ACTIVE-RETURN-CODE                               
               WHEN SUCCESSFUL-CALL                                     
                    CONTINUE                                            
               WHEN NOT-FOUND                                           
                    MOVE ZEROES             TO WS-ACTIVE-RETURN-CODE    
               WHEN OTHER                                               
                    MOVE PROGRAM-NAME       TO ABEND-PROGRAM            
                    MOVE SQLCODE            TO ABEND-SQLCODE            
                    MOVE SQLSTATE           TO ABEND-SQLSTATE           
                    MOVE '8000A'            TO ACTIVE-PARAGRAPH         
                    MOVE 'DELETE'           TO ABEND-FUNCTION           
                    MOVE SPACES             TO ABEND-SQL-PREDICATES     
                                               ABEND-TABLES             
                    MOVE 'CSR04525_R1'      TO TABLE-1                  
                    MOVE SPACES             TO TABLE-ELEMENT-1          
                    MOVE SPACES             TO HOSTVAR-ELEMENT-1        
                    PERFORM 9700-PROCESS-ABEND THRU 9700-EXIT           
           END-EVALUATE.                                                
                                                                        
       8000A-EXIT.                                                      
            EXIT.                                                       
                                                                        
                                                                        
      ******************************************************************        
      * 8100-SEND-RESULT                                               *        
      ******************************************************************        
       8100-SEND-RESULT.                                                
                                                                        
           EXEC SQL                                                     
              INSERT INTO #CSR04525_R1                           
                 (                                                      
                   RETURN_CODE                                          
                  ,APPL_RETURN_CODE                                     
                  ,COMPANY_NO                                           
                  ,EMAIL_TYPE_CD                                        
                  ,EMAIL_SUBTYPE_CD                                     
                  ,VERSION_NO                                           
                  ,VERSION_START_TS                                     
                  ,STATUS_CD                                            
                  ,EMAIL_ENTITY                                         
                  ,EMAIL_SUBJECT                                        
                  ,EMAIL_BODY                                           
                  ,EMAIL_FROM_ADDR                                      
                  ,EMAIL_TO_ADDR                                        
                  ,EMAIL_CC_ADDR                                        
                  ,EMAIL_BCC_ADDR                                       
                  ,EMAIL_AUDIT_FL                                       
                  ,CREATED_TS                                           
                  ,CREATED_BY_USERID                                    
                  ,LAST_UPDATE_TS                                       
                  ,LAST_UPDATE_USERID                                   
                 )                                                      
              VALUES                                                    
                 (                                                      
                   :S-RETURN-CODE                                       
                  ,:S-APPL-RETURN-CODE                                  
                  ,:S-COMPANY-NO                                        
                  ,:S-EMAIL-TYPE-CD                                     
                  ,:S-EMAIL-SUBTYPE-CD                                  
                  ,:S-VERSION-NO                                        
                  ,:S-VERSION-START-TS                                  
                  ,:S-STATUS-CD                                         
                  ,:S-EMAIL-ENTITY                                      
                  ,:S-EMAIL-SUBJECT                                     
                  ,:S-EMAIL-BODY                                        
                  ,:S-EMAIL-FROM-ADDR                                   
                  ,:S-EMAIL-TO-ADDR                                     
                  ,:S-EMAIL-CC-ADDR                                     
                  ,:S-EMAIL-BCC-ADDR                                    
                  ,:S-EMAIL-AUDIT-FL                                    
                  ,:S-CREATED-TS                                        
                  ,:S-CREATED-BY-USERID                                 
                  ,:S-LAST-UPDATE-TS                                    
                  ,:S-LAST-UPDATE-USERID                                
                 )                                                      
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ049
MFA-TR*    EXEC SQL                                                             
MFA-TR*       INSERT INTO SESSION.CSR04525_R1                                   
MFA-TR*          (                                                              
MFA-TR*            RETURN_CODE                                                  
MFA-TR*           ,APPL_RETURN_CODE                                             
MFA-TR*           ,COMPANY_NO                                                   
MFA-TR*           ,EMAIL_TYPE_CD                                                
MFA-TR*           ,EMAIL_SUBTYPE_CD                                             
MFA-TR*           ,VERSION_NO                                                   
MFA-TR*           ,VERSION_START_TS                                             
MFA-TR*           ,STATUS_CD                                                    
MFA-TR*           ,EMAIL_ENTITY                                                 
MFA-TR*           ,EMAIL_SUBJECT                                                
MFA-TR*           ,EMAIL_BODY                                                   
MFA-TR*           ,EMAIL_FROM_ADDR                                              
MFA-TR*           ,EMAIL_TO_ADDR                                                
MFA-TR*           ,EMAIL_CC_ADDR                                                
MFA-TR*           ,EMAIL_BCC_ADDR                                               
MFA-TR*           ,EMAIL_AUDIT_FL                                               
MFA-TR*           ,CREATED_TS                                                   
MFA-TR*           ,CREATED_BY_USERID                                            
MFA-TR*           ,LAST_UPDATE_TS                                               
MFA-TR*           ,LAST_UPDATE_USERID                                           
MFA-TR*          )                                                              
MFA-TR*       VALUES                                                            
MFA-TR*          (                                                              
MFA-TR*            :S-RETURN-CODE                                               
MFA-TR*           ,:S-APPL-RETURN-CODE                                          
MFA-TR*           ,:S-COMPANY-NO                                                
MFA-TR*           ,:S-EMAIL-TYPE-CD                                             
MFA-TR*           ,:S-EMAIL-SUBTYPE-CD                                          
MFA-TR*           ,:S-VERSION-NO                                                
MFA-TR*           ,:S-VERSION-START-TS                                          
MFA-TR*           ,:S-STATUS-CD                                                 
MFA-TR*           ,:S-EMAIL-ENTITY                                              
MFA-TR*           ,:S-EMAIL-SUBJECT                                             
MFA-TR*           ,:S-EMAIL-BODY                                                
MFA-TR*           ,:S-EMAIL-FROM-ADDR                                           
MFA-TR*           ,:S-EMAIL-TO-ADDR                                             
MFA-TR*           ,:S-EMAIL-CC-ADDR                                             
MFA-TR*           ,:S-EMAIL-BCC-ADDR                                            
MFA-TR*           ,:S-EMAIL-AUDIT-FL                                            
MFA-TR*           ,:S-CREATED-TS                                                
MFA-TR*           ,:S-CREATED-BY-USERID                                         
MFA-TR*           ,:S-LAST-UPDATE-TS                                            
MFA-TR*           ,:S-LAST-UPDATE-USERID                                        
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 WS-ACTIVE-RETURN-CODE TO RS-RETURN-CODE              
              MOVE PROGRAM-NAME          TO  ABEND-PROGRAM              
              MOVE '8100'                TO  ACTIVE-PARAGRAPH           
              MOVE SQLCODE               TO  ABEND-SQLCODE              
              MOVE 'INSERT'              TO  ABEND-FUNCTION             
              MOVE SPACES                TO  ABEND-SQL-PREDICATES       
                                             ABEND-TABLES               
              MOVE 'CSR04525_R1'         TO  TABLE-1                    
              MOVE SPACES                TO  TABLE-ELEMENT-1            
              MOVE SPACES                TO  HOSTVAR-ELEMENT-1          
              PERFORM 9700-PROCESS-ABEND     THRU 9700-EXIT             
           END-IF.                                                      
                                                                        
                                                                        
       8100-EXIT.                                                       
           EXIT.                                                        
                                                                        
        9700-PROCESS-ABEND.                                             
                                                                        
             PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT.            
                                                                        
             PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT.            
                                                                        
        9700-EXIT.                                                      
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 9900 - JOURNALING / ERROR HANDLING ROUTINE                     *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE CPDSP300                                                  
           END-EXEC.                                                            
                                                                        
      ******************************************************************        
      *       END PROGRAM COPYLIB                                      *        
      ******************************************************************        
           EXEC SQL                                                             
             INCLUDE CPD00320                                                   
           END-EXEC.                                                            
                                                                        
