                                                                        
       IDENTIFICATION DIVISION.                                         
       PROGRAM-ID. CSR03946.                                            
       DATE-WRITTEN. 02/09/07.                                          
       DATE-COMPILED.                                                   
                                                                        
      ******************************************************************        
      *                SOUTH CAROLINA ELECTRIC & GAS                   *        
      *                                                                *        
      *                 P R O G R A M  S U M M A R Y                   *        
      *                                                                *        
      *  THIS PROGRAM IS A COBOL STORED PROCEDURE CALLED FROM CSR      *        
      *  DESKTOP                                                       *        
      *                                                                *        
      *  THIS PROGRAM SELECTS RECORDS FOR REPORTS SERVICEINSTALLS      *        
      *                                                                *        
      ******************************************************************        
      *                                                                *        
      *                     PROGRAM MODIFICATION LOG                   *        
      *                                                                *        
      *    DATE    INITIALS   COMMENTS                                 *        
      *  --------  --------   ---------------------------------------  *        
      *  02/09/07  GV97034    INITIAL VERSION                          *        
C37253*  06/16/08  FMB        CREATED BINDO CARD FOR REOPT(VARS)       *        
C37261*  06/16/08  FMB        CORRECTED DCLGENS                        *        
      *                                                                *        
      ******************************************************************        
      ******************************************************************        
      *                                                                *        
      *                ---- BASIC SEQUENCE STRUCTURE ----              *        
      *                                                                *        
      *  0000 - 0999  MAIN CONTROL PATH AND INITIALIZATION             *        
      *  1000 - 1999  INPUT PROCESSING CONTROL PATH                    *        
      *  2000 - 2999  OUTPUT PROCESSING CONTROL PATH                   *        
      *  3000 - 4999  NOT USED                                         *        
      *  5000 - 5999  COMMON PROGRAM MODULES                           *        
      *  6000 - 6999  COMMON SYSTEM MODULES                            *        
      *  7000 - 7999  INPUT MODULES                                    *        
      *  8000 - 8999  OUTPUT MODULES                                   *        
      *  9000 - 9999  TERMINATION,ABEND, MESSAGING MODULES             *        
      *                                                                *        
      ******************************************************************        
                                                                        
       ENVIRONMENT DIVISION.                                            
       DATA DIVISION.                                                   
       WORKING-STORAGE SECTION.                                         

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

MSQ017 01 MFA-PROGRAM-ID PIC X(8) VALUE 'CSR03946'.
MSQ017     COPY MFASQLM.
                                                                        
       01  WS-START                                   PIC X(45) VALUE   
           'WORKING STORAGE FOR SP CSR03946 STARTS HERE'.               
                                                                        
                                                                        
      ******************************************************************        
      *    DB2 INCLUDES                                                *        
      ******************************************************************        
                                                                        
           EXEC SQL                                                             
              INCLUDE SQLCA                                                     
           END-EXEC.                                                            
      *                                                                         
           EXEC SQL                                                             
              INCLUDE TBSODAT                                                   
           END-EXEC.                                                            
      *                                                                         
           EXEC SQL                                                             
C37261        INCLUDE TBWKAR                                                    
           END-EXEC.                                                            
      *                                                                         
      ******************************************************************        
      *    ERROR HANDLING COPY BOOK                                    *        
      ******************************************************************        
           EXEC SQL                                                             
               INCLUDE CWSX0010                                                 
           END-EXEC.                                                            
                                                                        
      ******************************************************************        
      *    SQL ERROR CHECKING COPYBOOK.                                *        
      ******************************************************************        
           COPY CWS00303.                                                       
                                                                        
      ******************************************************************        
      *    WORK AREAS                                                  *        
      ******************************************************************        
                                                                        
       01  GTT-SVC-OUT.                                                 
            05  GTT-NAME                PIC X(26)                       
                    VALUE 'SESSION.CSR03946_R1'.                        
            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  GTT-SVC-CNT.                                                 
            05  GTT-NAME                PIC X(26)                       
                    VALUE 'SESSION.CSR03946_R2'.                        
            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  GTT-OU-CNT.                                                  
            05  GTT-NAME                PIC X(26)                       
                    VALUE 'SESSION.CSR03946_R3'.                        
            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  GTT-OU-CNT1.                                                 
            05  GTT-NAME                PIC X(26)                       
                    VALUE 'SESSION.CSR03946_R4'.                        
            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  GTT-WA-MATL.                                                 
            05  GTT-NAME                PIC X(26)                       
                    VALUE 'SESSION.CSR03946_R5'.                        
            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  MESSAGE-FIELDS.                                              
            05  MSG-TYPE                     PIC S9(9) COMP.            
            05  MSG-SEVERITY                 PIC S9(9) COMP VALUE 11.   
            05  MSG-NR                       PIC S9(9) COMP VALUE  2.   
            05  MSG-RPC                      PIC X(4)  VALUE 'S200'.    
            05  MSG-RPC                      PIC X(8)  VALUE 'CSR03946'.
            05  MSG-RPC-L                    PIC S9(9) COMP.            
            05  MSG-TEXT                     PIC X(100).                
            05  MSG-TEXT-L                   PIC S9(9) COMP.            
            05  MSG-SQL-ERROR.                                          
                10  FILLER                   PIC X(10)                  
                                             VALUE 'SQLCODE = '.        
                10  MSG-SQL-ERROR-C          PIC -9(3) DISPLAY.         
                10  FILLER                   PIC X(16)                  
                                             VALUE ', ERROR TOKENS: '.  
                10  MSG-SQL-ERROR-K          PIC X(70).                 
                10  MSG-SQL-ERROR-K-CHARS                               
                                             REDEFINES MSG-SQL-ERROR-K  
                                             OCCURS 70 TIMES            
                                             PIC X.                     
            05  MSG-SQL-ERROR-SS             PIC S9(4) COMP.            
                                                                        
       01  CSRERLOG-P.                                                  
            10  S-SP-NAME               PIC X(18)      VALUE SPACES.    
            10  S-SQLCODE               PIC S9(9)      COMP VALUE 0.    
            10  S-SQLSTATE              PIC X(5)       VALUE ' '.       
            10  S-TABLE-NAME            PIC X(18)      VALUE SPACES.    
            10  S-HOST-VARIABLES.                                       
                49  S-HOST-VARIABLES-L  PIC S9(4)      USAGE COMP.      
                49  S-HOST-VARIABLES-V  PIC X(255).                     
            10  S-SQL-STATEMENT.                                        
                49  S-SQL-STATEMENT-L   PIC S9(4)      USAGE COMP.      
                49  S-SQL-STATEMENT-V   PIC X(255).                     
            10  S-SQL-DESCRIPTION.                                      
                49  S-SQL-DESCRIPTION-L PIC S9(4)      USAGE COMP.      
                49  S-SQL-DESCRIPTION-V PIC X(255).                     
                                                                        
       01  FILLER  PIC X(32) VALUE 'THIS IS THE ROW RETURN AREA.'.      
       01  GTT-RETURN-FIELDS.                                           
            05  S-RETURN-CODE           PIC S9(9) COMP VALUE 0.         
            05  S-WQ-FLAG               PIC X(01) VALUE 'N'.            
                                                                        
       01  SWITCHES.                                                    
            05  SEND-DONE-SW            PIC X(01) VALUE 'Y'.            
                88 SEND-DONE-ERROR                VALUE 'N'.            
                88 SEND-DONE-OK                   VALUE 'Y'.            
       01  WS-BEGIN-DT                  PIC X(26).                      
       01  WS-END-DT                    PIC X(26).                      
COB305 01 WS-SERV-ORDER-NO        PIC S9(13)V USAGE COMP-3 VALUE 0.       
       01  WS-WORK-TYPE-CD              PIC X(3).                       
       01  WS-AREA-ID                   PIC X(3).                       
       01  WS-REGION-ID                 PIC X(3).                       
       01  WS-WORK-AREA                 PIC X(12).                      
       01  WS-WORK-AREA-TX              PIC X(40).                      
       01  WS-COST-CENTER               PIC X(3).                       
       01  WS-ACTION-CD                 PIC X(7).                       
       01  WS-CNT                       PIC S9(5) USAGE COMP.           
       01  WS-COMP-DT                   PIC X(10).                      
       01  WS-COMP-DT-NULL-IND          PIC S9(4) USAGE COMP VALUE +0.  
       01  WS-OVERHEAD                  PIC X(2).                       
       01  WS-SQLSTATE                  PIC X(05) VALUE SPACES.         
       01  CTR-ROWS                     PIC S9(9) COMP VALUE 0.         
       01  WS-COUNT                     PIC S9(9) COMP VALUE 0.         
       01  WS-NO-MORE-ROWS              PIC X(01) VALUE 'N'.            
          88 NO-MORE-ROWS               VALUE 'Y'.                      
       01  S-CURR-VALUE                 PIC X(20) VALUE SPACE.          
       01  PROGRAM-NAME                 PIC X(08) VALUE 'CSR03946'.     
      ******************************************************************        
      *  CURSOR DECLARATIONS.                                          *        
      ******************************************************************        
           EXEC SQL                                                     
              DECLARE ORDER_INST CURSOR FOR                             
                 SELECT OI.SERV_ORDER_NO                                
                       ,OI.WORK_TYPE_CD                                 
                   FROM CSS_SO_ORDER_INST OI WITH(READUNCOMMITTED)              
                  WHERE (OI.WORK_TYPE_CD IN                             
                        ('ST1','S11','S12','S54','S56','S57','S58'))    
                    AND (OI.CODE_UTIL_TYPE = 'E')                       
                    FOR READ ONLY                               
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ004
MFA-TR* MSQ022
MFA-TR*    EXEC SQL                                                             
MFA-TR*       DECLARE ORDER_INST CURSOR FOR                                     
MFA-TR*          SELECT OI.SERV_ORDER_NO                                        
MFA-TR*                ,OI.WORK_TYPE_CD                                         
MFA-TR*            FROM CSS_SO_ORDER_INST OI                                    
MFA-TR*           WHERE (OI.WORK_TYPE_CD IN                                     
MFA-TR*                 ('ST1','S11','S12','S54','S56','S57','S58'))            
MFA-TR*             AND (OI.CODE_UTIL_TYPE = 'E')                               
MFA-TR*             FOR FETCH ONLY WITH UR                                      
MFA-TR*    END-EXEC.                                                            
                                                                        
           EXEC SQL                                                     
              DECLARE ORDER_HIST CURSOR FOR                             
                 SELECT OI.SERV_ORDER_NO                                
                       ,OI.WORK_TYPE_CD                                 
                   FROM HST_SO_ORDER_INST OI WITH(READUNCOMMITTED)              
                  WHERE (OI.WORK_TYPE_CD IN                             
                        ('ST1','S11','S12','S54','S56','S57','S58'))    
                    AND (OI.CODE_UTIL_TYPE = 'E')                       
                    FOR READ ONLY                               
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ004
MFA-TR* MSQ022
MFA-TR*    EXEC SQL                                                             
MFA-TR*       DECLARE ORDER_HIST CURSOR FOR                                     
MFA-TR*          SELECT OI.SERV_ORDER_NO                                        
MFA-TR*                ,OI.WORK_TYPE_CD                                         
MFA-TR*            FROM HST_SO_ORDER_INST OI                                    
MFA-TR*           WHERE (OI.WORK_TYPE_CD IN                                     
MFA-TR*                 ('ST1','S11','S12','S54','S56','S57','S58'))            
MFA-TR*             AND (OI.CODE_UTIL_TYPE = 'E')                               
MFA-TR*             FOR FETCH ONLY WITH UR                                      
MFA-TR*    END-EXEC.                                                            
      *    EXEC SQL                                                             
      *       DECLARE ORDER_INST CURSOR FOR                                     
      *          SELECT OI.SERV_ORDER_NO                                        
      *                ,OI.WORK_TYPE_CD                                         
      *            FROM CSS_SO_ORDER_INST OI, CSS_SO_REQUIREMENT RQ             
      *           WHERE OI.SERV_ORDER_NO = RQ.SERV_ORDER_NO                     
      *             AND (OI.WORK_TYPE_CD IN                                     
      *                 ('ST1','S11','S12','S54','S56','S57','S58'))            
      *             AND (OI.CODE_UTIL_TYPE = 'E')                               
      *             AND RQ.SERV_ORDER_STATUS <> '50'                            
      *             AND (RQ.REQMNT_COMP_DT >= :WS-BEGIN-DT                      
      *             AND  RQ.REQMNT_COMP_DT <= :WS-END-DT                        
      *             AND  RQ.REQMNT_COMP_DT IS NOT NULL)                         
      *             FOR FETCH ONLY WITH UR                                      
      *    END-EXEC.                                                            
      *    EXEC SQL                                                             
      *       DECLARE ORDER_HIST CURSOR FOR                                     
      *          SELECT OI.SERV_ORDER_NO                                        
      *                ,OI.WORK_TYPE_CD                                         
      *            FROM HST_SO_ORDER_INST OI, HST_SO_REQUIREMENT RQ             
      *           WHERE OI.SERV_ORDER_NO = RQ.SERV_ORDER_NO                     
      *             AND (OI.WORK_TYPE_CD IN                                     
      *                 ('ST1','S11','S12','S54','S56','S57','S58'))            
      *             AND (OI.CODE_UTIL_TYPE = 'E')                               
      *             AND RQ.SERV_ORDER_STATUS <> '50'                            
      *             AND (RQ.REQMNT_COMP_DT >= :WS-BEGIN-DT                      
      *             AND  RQ.REQMNT_COMP_DT <= :WS-END-DT                        
      *             AND  RQ.REQMNT_COMP_DT IS NOT NULL)                         
      *             FOR FETCH ONLY WITH UR                                      
      *    END-EXEC.                                                            
       LINKAGE SECTION.                                                 
       01  PARM-BEGIN-DT      PIC X(26).                                
       01  PARM-END-DT        PIC X(26).                                
                                                                        
       PROCEDURE DIVISION USING PARM-BEGIN-DT                           
                                PARM-END-DT.                            
                                                                        
      ******************************************************************        
      * 0000-MAINLINE                                                  *        
      *     CONTROLS THE MAIN PATH OF THE PROGRAM                      *        
      ******************************************************************        
                                                                        
       0000-MAINLINE.                                                   
                                                                        
            PERFORM 0100-INITIALIZE     THRU 0100-EXIT.                 
            PERFORM 1000-PROCESS-INPUT  THRU 1000-EXIT.                 
            PERFORM 2000-PROCESS-OUTPUT THRU 2000-EXIT.                 
            PERFORM 9999-END-PROGRAM    THRU 9999-EXIT.                 
                                                                        
       0000-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 0100-INITIALIZE                                                *        
      *                                                                *        
      *     1. RESET DB2 ERROR HANDLERS                                *        
      *     2. ESTABLISH GATEWAY ENVIRONMENT (TDINIT)                  *        
      *     3. ACCEPT CLIENT REQUEST/ESTABLISH SNA HANDLE (TDACCEPT)   *        
      *     4. DETERMINE IF CLIENT HAS SENT AN RPC PARAMETER (TDRESULT)*        
      *                                                                *        
      ******************************************************************        
                                                                        
       0100-INITIALIZE.                                                 
                                                                        
            EXEC SQL                                                    
                WHENEVER SQLWARNING CONTINUE                            
            END-EXEC.                                                   
            EXEC SQL                                                    
                WHENEVER SQLERROR   CONTINUE                            
            END-EXEC.                                                   
            EXEC SQL                                                    
                WHENEVER NOT FOUND  CONTINUE                            
            END-EXEC.                                                   
                                                                        
            PERFORM 0100A-DECLARE-GTT THRU 0100A-EXIT.                  
                                                                        
            EXEC SQL                                                    
               DECLARE C1 CURSOR  FOR                        
                  SELECT REGION_ID                                      
                        ,WORK_AREA                                      
                        ,WORK_AREA_TX                                   
                        ,COST_CENTER                                    
                        ,ACTION                                         
                        ,SUM_CNT                                        
                        ,BEGIN_DATE                                     
                        ,END_DATE                                       
                    FROM #CSR03946_R1                            
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ049
MFA-TR*     EXEC SQL                                                            
MFA-TR*        DECLARE C1 CURSOR WITH RETURN FOR                                
MFA-TR*           SELECT REGION_ID                                              
MFA-TR*                 ,WORK_AREA                                              
MFA-TR*                 ,WORK_AREA_TX                                           
MFA-TR*                 ,COST_CENTER                                            
MFA-TR*                 ,ACTION                                                 
MFA-TR*                 ,SUM_CNT                                                
MFA-TR*                 ,BEGIN_DATE                                             
MFA-TR*                 ,END_DATE                                               
MFA-TR*             FROM SESSION.CSR03946_R1                                    
MFA-TR*     END-EXEC.                                                           
                                                                        
       0100-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      *0100A-DECLARE-GTT************************************************        
      ******************************************************************        
       0100A-DECLARE-GTT.                                               
      *                                                                         
           MOVE 'DECLARE GLOBAL TEMPORARY TABLE CSR03946_R1'            
                                                TO S-SQL-STATEMENT-V.   
           EXEC SQL
             CALL CIS.DROP_TEMP_TABLE('#CSR03946_R1')
           END-EXEC
           EXEC SQL
             CREATE TABLE #CSR03946_R1
              (                                                       
                   REGION_ID CHAR(3)  COLLATE LATIN1_GENERAL_100_BIN2           
                  ,WORK_AREA CHAR(12)  COLLATE LATIN1_GENERAL_100_BIN2          
                  ,WORK_AREA_TX CHAR(40)  COLLATE 
                                LATIN1_GENERAL_100_BIN2                     
                  ,COST_CENTER CHAR(3)  COLLATE LATIN1_GENERAL_100_BIN2         
                  ,ACTION CHAR(12)  COLLATE LATIN1_GENERAL_100_BIN2             
                  ,SUM_CNT                 DECIMAL(13,0)                
                  ,BEGIN_DATE              DATETIME2(6)                    
                  ,END_DATE                DATETIME2(6)                    
                )
           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-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
              NEXT SENTENCE                                             
           ELSE                                                         
              MOVE PROGRAM-NAME       TO ABEND-PROGRAM                  
              MOVE SQLCODE            TO ABEND-SQLCODE                  
              MOVE SQLSTATE           TO ABEND-SQLSTATE                 
              MOVE '0100A'            TO ACTIVE-PARAGRAPH               
              MOVE 'DECLARE GTT'      TO ABEND-FUNCTION                 
              MOVE SPACES             TO ABEND-SQL-PREDICATES           
                                         ABEND-TABLES                   
              MOVE 'CSR03946_R1'      TO TABLE-1                        
              MOVE SPACES             TO TABLE-ELEMENT-1                
              MOVE SPACES             TO HOSTVAR-ELEMENT-1              
              PERFORM 9900-SQL-ERROR-ROUTINE                            
                                      THRU  9900-EXIT                   
           END-IF.                                                      
      *                                                                         
                                                                        
           MOVE 'DECLARE GLOBAL TEMPORARY TABLE CSR03946_R2'            
                                      TO S-SQL-STATEMENT-V.             
           EXEC SQL
             CALL CIS.DROP_TEMP_TABLE('#CSR03946_R2')
           END-EXEC
           EXEC SQL
             CREATE TABLE #CSR03946_R2
              (                                                         
                 BEGIN_DATE              DATETIME2(6)                      
                ,END_DATE                DATETIME2(6)                      
                ,SERV_ORDER_NO           DECIMAL(13,0)                  
                ,WORK_TYPE CHAR(3)  COLLATE LATIN1_GENERAL_100_BIN2             
                ,AREA_ID CHAR(3)  COLLATE LATIN1_GENERAL_100_BIN2               
                ,REGION_ID CHAR(3)  COLLATE LATIN1_GENERAL_100_BIN2             
                ,WORK_AREA CHAR(12)  COLLATE LATIN1_GENERAL_100_BIN2            
                ,WORK_AREA_TX CHAR(40)  COLLATE LATIN1_GENERAL_100_BIN2         
                ,COST_CENTER CHAR(3)  COLLATE LATIN1_GENERAL_100_BIN2           
                ,ACTION_CD CHAR(7)  COLLATE LATIN1_GENERAL_100_BIN2             
                ,OVERHEAD_UNDERGROUND CHAR(2)  COLLATE 
                              LATIN1_GENERAL_100_BIN2                        
                ,CNT                     DECIMAL(5,0)                   
                ,COMPLETE_DATE CHAR(7)  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-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
              NEXT SENTENCE                                             
           ELSE                                                         
              MOVE PROGRAM-NAME       TO ABEND-PROGRAM                  
              MOVE SQLCODE            TO ABEND-SQLCODE                  
              MOVE SQLSTATE           TO ABEND-SQLSTATE                 
              MOVE '0100A'            TO ACTIVE-PARAGRAPH               
              MOVE 'DECLARE GTT'      TO ABEND-FUNCTION                 
              MOVE SPACES             TO ABEND-SQL-PREDICATES           
                                         ABEND-TABLES                   
              MOVE 'CSR03946_R2'      TO TABLE-1                        
              MOVE SPACES             TO TABLE-ELEMENT-1                
              MOVE SPACES             TO HOSTVAR-ELEMENT-1              
              PERFORM 9900-SQL-ERROR-ROUTINE                            
                                      THRU  9900-EXIT                   
           END-IF.                                                      
      *                                                                         
           MOVE 'DECLARE GLOBAL TEMPORARY TABLE CSR03946_R3'            
                                         TO S-SQL-STATEMENT-V.          
           EXEC SQL
             CALL CIS.DROP_TEMP_TABLE('#CSR03946_R3')
           END-EXEC
           EXEC SQL
             CREATE TABLE #CSR03946_R3
              (                                                        
                  REGION_ID CHAR(3)  COLLATE LATIN1_GENERAL_100_BIN2            
                 ,COMPLETE_DATE CHAR(7)  COLLATE LATIN1_GENERAL_100_BIN2        
                 ,UNDER_TOT               DECIMAL(7,0)                  
                 ,OU_TOT                  DECIMAL(7,0)                  
                 ,DIVISOR                 DECIMAL(7,2)                  
               )
           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-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
              NEXT SENTENCE                                             
           ELSE                                                         
              MOVE PROGRAM-NAME       TO ABEND-PROGRAM                  
              MOVE SQLCODE            TO ABEND-SQLCODE                  
              MOVE SQLSTATE           TO ABEND-SQLSTATE                 
              MOVE '0100A'            TO ACTIVE-PARAGRAPH               
              MOVE 'DECLARE GTT'      TO ABEND-FUNCTION                 
              MOVE SPACES             TO ABEND-SQL-PREDICATES           
                                         ABEND-TABLES                   
              MOVE 'CSR03946_R3'      TO TABLE-1                        
              MOVE SPACES             TO TABLE-ELEMENT-1                
              MOVE SPACES             TO HOSTVAR-ELEMENT-1              
              PERFORM 9900-SQL-ERROR-ROUTINE                            
                                      THRU  9900-EXIT                   
           END-IF.                                                      
      *                                                                         
           MOVE 'DECLARE GLOBAL TEMPORARY TABLE CSR03946_R4'            
                                      TO S-SQL-STATEMENT-V.             
           EXEC SQL
             CALL CIS.DROP_TEMP_TABLE('#CSR03946_R4')
           END-EXEC
           EXEC SQL
             CREATE TABLE #CSR03946_R4
              (                                                         
                 REGION_ID CHAR(3)  COLLATE LATIN1_GENERAL_100_BIN2             
                ,COMPLETE_DATE CHAR(7)  COLLATE LATIN1_GENERAL_100_BIN2         
                ,UNDER_TOT               DECIMAL(7,0)                   
                ,OU_TOT                  DECIMAL(7,0)                   
                ,DIVISOR                 DECIMAL(7,2)                   
              )
           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-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
              NEXT SENTENCE                                             
           ELSE                                                         
              MOVE PROGRAM-NAME       TO ABEND-PROGRAM                  
              MOVE SQLCODE            TO ABEND-SQLCODE                  
              MOVE SQLSTATE           TO ABEND-SQLSTATE                 
              MOVE '0100A'            TO ACTIVE-PARAGRAPH               
              MOVE 'DECLARE GTT'      TO ABEND-FUNCTION                 
              MOVE SPACES             TO ABEND-SQL-PREDICATES           
                                         ABEND-TABLES                   
              MOVE 'CSR03946_R4'      TO TABLE-1                        
              MOVE SPACES             TO TABLE-ELEMENT-1                
              MOVE SPACES             TO HOSTVAR-ELEMENT-1              
              PERFORM 9900-SQL-ERROR-ROUTINE                            
                                      THRU  9900-EXIT                   
           END-IF.                                                      
      *                                                                         
           MOVE 'DECLARE GLOBAL TEMPORARY TABLE CSR03946_R5'            
                                         TO S-SQL-STATEMENT-V.          
           EXEC SQL
             CALL CIS.DROP_TEMP_TABLE('#CSR03946_R5')
           END-EXEC
           EXEC SQL
             CREATE TABLE #CSR03946_R5
              (                                                         
                 WORK_AREA_ID CHAR(12)  COLLATE LATIN1_GENERAL_100_BIN2         
                ,AREA_ID CHAR(3)  COLLATE LATIN1_GENERAL_100_BIN2               
                ,REGION_ID CHAR(3)  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-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
              NEXT SENTENCE                                             
           ELSE                                                         
              MOVE PROGRAM-NAME       TO ABEND-PROGRAM                  
              MOVE SQLCODE            TO ABEND-SQLCODE                  
              MOVE SQLSTATE           TO ABEND-SQLSTATE                 
              MOVE '0100A'            TO ACTIVE-PARAGRAPH               
              MOVE 'DECLARE GTT'      TO ABEND-FUNCTION                 
              MOVE SPACES             TO ABEND-SQL-PREDICATES           
                                         ABEND-TABLES                   
              MOVE 'CSR03946_R5'      TO TABLE-1                        
              MOVE SPACES             TO TABLE-ELEMENT-1                
              MOVE SPACES             TO HOSTVAR-ELEMENT-1              
              PERFORM 9900-SQL-ERROR-ROUTINE                            
                                      THRU  9900-EXIT                   
           END-IF.                                                      
      *                                                                         
       0100A-EXIT.                                                      
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 1000-PROCESS-INPUT                                             *        
      *                                                                *        
      *     1. RECEIVE PARMS.                                          *        
      *                                                                *        
      ******************************************************************        
                                                                        
       1000-PROCESS-INPUT.                                              
                                                                        
            MOVE PARM-BEGIN-DT           TO WS-BEGIN-DT.                
            MOVE PARM-END-DT             TO WS-END-DT.                  
                                                                        
       1000-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 2000-PROCESS-OUTPUT.                                           *        
      ******************************************************************        
       2000-PROCESS-OUTPUT.                                             
            PERFORM 5000-INSERT-CSR03946-R5        THRU 5000-EXIT.      
            PERFORM 7000-OPEN-ORDER-INST           THRU 7000-EXIT.      
            PERFORM 7010-FETCH-ORDER-INST          THRU 7010-EXIT.      
            PERFORM 5010-PROCESS-ORDER-INST        THRU 5010-EXIT       
                                                   UNTIL NO-MORE-ROWS.  
            PERFORM 7020-CLOSE-ORDER-INST          THRU 7020-EXIT.      
            PERFORM 5030-DELETE-CSR03946-R2        THRU 5030-EXIT.      
            PERFORM 5040-UPDATE-CSR03946-R2        THRU 5040-EXIT.      
            PERFORM 5050-UPD-CSR03946-R2-COMP      THRU 5050-EXIT.      
            MOVE 'N'                               TO WS-NO-MORE-ROWS.  
            PERFORM 7030-OPEN-ORDER-HIST           THRU 7030-EXIT.      
            PERFORM 7040-FETCH-ORDER-HIST          THRU 7040-EXIT.      
            PERFORM 6010-PROCESS-ORDER-HIST        THRU 6010-EXIT       
                                                   UNTIL NO-MORE-ROWS.  
            PERFORM 7050-CLOSE-ORDER-HIST          THRU 7050-EXIT.      
            PERFORM 6030-DELETE-CSR03946-R2        THRU 6030-EXIT.      
            PERFORM 6040-UPDATE-CSR03946-R2        THRU 6040-EXIT.      
            PERFORM 6050-UPD-CSR03946-R2-COMP      THRU 6050-EXIT.      
            PERFORM 5060-UPD-CSR03946-R2-AREA-REG  THRU 5060-EXIT.      
            PERFORM 5070-UPD-CSR03946-R2-COST-CNT  THRU 5070-EXIT.      
            PERFORM 5080-DEL-CSR03946-R2-REG-ID    THRU 5080-EXIT.      
      *     PERFORM 5090-INSERT-CSR03946-R4        THRU 5090-EXIT.              
      *     PERFORM 5100-INSERT-CSR03946-R4-U-TOT  THRU 5100-EXIT.              
      *     PERFORM 5110-INSERT-CSR03946-R3        THRU 5110-EXIT.              
      *     PERFORM 5120-UPDATE-CSR03946-R3        THRU 5120-EXIT.              
            IF CTR-ROWS = 0                                             
               PERFORM 8100-SEND-RESULT    THRU 8100-EXIT               
            END-IF.                                                     
                                                                        
       2000-EXIT.                                                       
            EXIT.                                                       
      ******************************************************************        
      * 5000-INSERT-CSR03946-R5.                                       *        
      ******************************************************************        
       5000-INSERT-CSR03946-R5.                                         
            EXEC SQL                                                    
               INSERT INTO #CSR03946_R5                          
                   SELECT WORK_AREA_ID                                  
                         ,AREA_ID                                       
                         ,REGION_ID                                     
                     FROM CSS_WORK_AREA_MATL                            
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ049
MFA-TR*     EXEC SQL                                                            
MFA-TR*        INSERT INTO SESSION.CSR03946_R5                                  
MFA-TR*            SELECT WORK_AREA_ID                                          
MFA-TR*                  ,AREA_ID                                               
MFA-TR*                  ,REGION_ID                                             
MFA-TR*              FROM CSS_WORK_AREA_MATL                                    
MFA-TR*     END-EXEC.                                                           

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

            MOVE SQLCODE                   TO WS-ACTIVE-RETURN-CODE.    
                                                                        
            IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND     
               IF WS-ACTIVE-RETURN-CODE = NOT-FOUND                     
                  MOVE SPACES                 TO S-CURR-VALUE           
                  PERFORM 8100-SEND-RESULT    THRU 8100-EXIT            
               END-IF                                                   
            ELSE                                                        
               MOVE PROGRAM-NAME         TO ABEND-PROGRAM               
               MOVE SQLCODE              TO ABEND-SQLCODE               
               MOVE '5000'               TO ACTIVE-PARAGRAPH            
               MOVE 'INSERT'             TO ABEND-FUNCTION              
               MOVE SPACES               TO ABEND-SQL-PREDICATES        
                                            ABEND-TABLES                
               MOVE 'CSR03946_R5'        TO TABLE-1                     
               MOVE SPACES               TO TABLE-ELEMENT-1             
               MOVE SPACES               TO HOSTVAR-ELEMENT-1           
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       5000-EXIT.                                                       
            EXIT.                                                       
                                                                        
      *****************************************************************         
      * 7000-OPEN-ORDER-INST.                                         *         
      *****************************************************************         
       7000-OPEN-ORDER-INST.                                            
                                                                        
            EXEC SQL                                                    
                 OPEN ORDER_INST                                        
            END-EXEC.                                                   

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

                                                                        
            MOVE SQLCODE                    TO WS-ACTIVE-RETURN-CODE    
                                                                        
            IF WS-ACTIVE-RETURN-CODE EQUAL SUCCESSFUL-CALL              
               NEXT SENTENCE                                            
            ELSE                                                        
               MOVE PROGRAM-NAME                TO ABEND-PROGRAM        
               MOVE '7000'                      TO ACTIVE-PARAGRAPH     
               MOVE 'OPEN'                      TO ABEND-FUNCTION       
               MOVE 'CSS_SO_ORDER_INST'         TO TABLE-1              
               PERFORM 9000-SEND-ERROR-RESULT   THRU 9000-EXIT          
               PERFORM 9900-SQL-ERROR-ROUTINE   THRU 9900-EXIT          
            END-IF.                                                     
                                                                        
       7000-EXIT.                                                       
            EXIT.                                                       
                                                                        
      *****************************************************************         
      * 7010-FETCH-ORDER-INST.                                        *         
      *****************************************************************         
       7010-FETCH-ORDER-INST.                                           
                                                                        
            EXEC SQL                                                    
                 FETCH ORDER_INST                                       
                  INTO :WS-SERV-ORDER-NO                                
                      ,:WS-WORK-TYPE-CD                                 
            END-EXEC.                                                   

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

                                                                        
            MOVE SQLCODE                       TO WS-ACTIVE-RETURN-CODE 
                                                                        
            IF WS-ACTIVE-RETURN-CODE EQUAL SUCCESSFUL-CALL OR NOT-FOUND 
               IF WS-ACTIVE-RETURN-CODE EQUAL NOT-FOUND                 
                  MOVE 'Y' TO WS-NO-MORE-ROWS                           
               END-IF                                                   
            ELSE                                                        
                MOVE PROGRAM-NAME                TO ABEND-PROGRAM       
                MOVE '7010'                      TO ACTIVE-PARAGRAPH    
                MOVE 'FETCH'                     TO ABEND-FUNCTION      
                MOVE 'CSS_SO_ORDER_INST'         TO TABLE-1             
                PERFORM 9000-SEND-ERROR-RESULT   THRU 9000-EXIT         
                PERFORM 9900-SQL-ERROR-ROUTINE   THRU 9900-EXIT         
            END-IF.                                                     
                                                                        
       7010-EXIT.                                                       
            EXIT.                                                       
                                                                        
      *****************************************************************         
      * 7020-CLOSE-ORDER-INST.                                        *         
      *****************************************************************         
       7020-CLOSE-ORDER-INST.                                           
                                                                        
            EXEC SQL                                                    
                 CLOSE ORDER_INST                                       
            END-EXEC.                                                   

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

                                                                        
            MOVE SQLCODE                    TO  WS-ACTIVE-RETURN-CODE   
                                                                        
            IF WS-ACTIVE-RETURN-CODE EQUAL SUCCESSFUL-CALL              
               NEXT SENTENCE                                            
            ELSE                                                        
               MOVE PROGRAM-NAME                TO ABEND-PROGRAM        
               MOVE '7020'                      TO ACTIVE-PARAGRAPH     
               MOVE 'CLOSE'                     TO ABEND-FUNCTION       
               MOVE 'CSS_SO_ORDER_INST'         TO TABLE-1              
               PERFORM 9000-SEND-ERROR-RESULT   THRU 9000-EXIT          
               PERFORM 9900-SQL-ERROR-ROUTINE   THRU 9900-EXIT          
            END-IF.                                                     
                                                                        
       7020-EXIT.                                                       
            EXIT.                                                       
      ******************************************************************        
      * 5010-PROCESS-ORDER-INST.                                       *        
      ******************************************************************        
       5010-PROCESS-ORDER-INST.                                         
                                                                        
            MOVE SPACES                         TO WS-AREA-ID           
            MOVE '000'                          TO WS-REGION-ID         
            MOVE SPACES                         TO WS-WORK-AREA         
            MOVE SPACES                         TO WS-WORK-AREA-TX      
            MOVE SPACES                         TO WS-COST-CENTER       
            MOVE 'INSTALL'                      TO WS-ACTION-CD         
            MOVE 1                              TO WS-CNT               
            MOVE -1                             TO WS-COMP-DT-NULL-IND  
            IF (WS-WORK-TYPE-CD = 'ST1' OR 'S11')                       
                MOVE 'OH'                       TO WS-OVERHEAD          
            ELSE                                                        
                MOVE 'UG'                       TO WS-OVERHEAD          
            END-IF.                                                     
                                                                        
            PERFORM 5020-INSERT-CSR03946-R2 THRU 5020-EXIT.             
            PERFORM 7010-FETCH-ORDER-INST   THRU 7010-EXIT.             
                                                                        
       5010-EXIT.                                                       
            EXIT.                                                       
      ******************************************************************        
      * 5020-INSERT-CSR03946-R2.                                       *        
      ******************************************************************        
       5020-INSERT-CSR03946-R2.                                         
            EXEC SQL                                                    
                 INSERT INTO #CSR03946_R2                        
                 (                                                      
                   BEGIN_DATE                                           
                  ,END_DATE                                             
                  ,SERV_ORDER_NO                                        
                  ,WORK_TYPE                                            
                  ,AREA_ID                                              
                  ,REGION_ID                                            
                  ,WORK_AREA                                            
                  ,WORK_AREA_TX                                         
                  ,COST_CENTER                                          
                  ,ACTION_CD                                            
                  ,OVERHEAD_UNDERGROUND                                 
                  ,CNT                                                  
                  ,COMPLETE_DATE                                        
                 )                                                      
                 VALUES                                                 
                 (                                                      
                   CIS.CHAR2TIMESTAMP(:WS-BEGIN-DT)                             
                  ,CIS.CHAR2TIMESTAMP(:WS-END-DT)                               
                  ,:WS-SERV-ORDER-NO                                    
                  ,:WS-WORK-TYPE-CD                                     
                  ,:WS-AREA-ID                                          
                  ,:WS-REGION-ID                                        
                  ,:WS-WORK-AREA                                        
                  ,:WS-WORK-AREA-TX                                     
                  ,:WS-COST-CENTER                                      
                  ,:WS-ACTION-CD                                        
                  ,:WS-OVERHEAD                                         
                  ,:WS-CNT                                              
                  ,:WS-COMP-DT :WS-COMP-DT-NULL-IND                     
                 )                                                      
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ049
MFA-TR* MSQ054
MFA-TR*     EXEC SQL                                                            
MFA-TR*          INSERT INTO SESSION.CSR03946_R2                                
MFA-TR*          (                                                              
MFA-TR*            BEGIN_DATE                                                   
MFA-TR*           ,END_DATE                                                     
MFA-TR*           ,SERV_ORDER_NO                                                
MFA-TR*           ,WORK_TYPE                                                    
MFA-TR*           ,AREA_ID                                                      
MFA-TR*           ,REGION_ID                                                    
MFA-TR*           ,WORK_AREA                                                    
MFA-TR*           ,WORK_AREA_TX                                                 
MFA-TR*           ,COST_CENTER                                                  
MFA-TR*           ,ACTION_CD                                                    
MFA-TR*           ,OVERHEAD_UNDERGROUND                                         
MFA-TR*           ,CNT                                                          
MFA-TR*           ,COMPLETE_DATE                                                
MFA-TR*          )                                                              
MFA-TR*          VALUES                                                         
MFA-TR*          (                                                              
MFA-TR*            :WS-BEGIN-DT                                                 
MFA-TR*           ,:WS-END-DT                                                   
MFA-TR*           ,:WS-SERV-ORDER-NO                                            
MFA-TR*           ,:WS-WORK-TYPE-CD                                             
MFA-TR*           ,:WS-AREA-ID                                                  
MFA-TR*           ,:WS-REGION-ID                                                
MFA-TR*           ,:WS-WORK-AREA                                                
MFA-TR*           ,:WS-WORK-AREA-TX                                             
MFA-TR*           ,:WS-COST-CENTER                                              
MFA-TR*           ,:WS-ACTION-CD                                                
MFA-TR*           ,:WS-OVERHEAD                                                 
MFA-TR*           ,:WS-CNT                                                      
MFA-TR*           ,:WS-COMP-DT :WS-COMP-DT-NULL-IND                             
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                  
               NEXT SENTENCE                                            
            ELSE                                                        
               MOVE PROGRAM-NAME          TO ABEND-PROGRAM              
               MOVE '5020'                TO ACTIVE-PARAGRAPH           
               MOVE 'INSERT'              TO ABEND-FUNCTION             
               MOVE 'CSR03946_R2'         TO TABLE-1                    
               MOVE 'SERVICE_ORDER_NO'    TO TABLE-ELEMENT-1            
               MOVE WS-SERV-ORDER-NO      TO HOSTVAR-ELEMENT-1          
               MOVE 'WORK_TYPE_CD'        TO TABLE-ELEMENT-2            
               MOVE WS-WORK-TYPE-CD       TO HOSTVAR-ELEMENT-2          
               PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT           
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       5020-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 5030-DELETE-CSR03946-R2.                                       *        
      ******************************************************************        
       5030-DELETE-CSR03946-R2.                                         
                                                                        
            EXEC SQL                                                    
                 DELETE FROM SC                      
                 FROM #CSR03946_R2 SC
                  WHERE SC.SERV_ORDER_NO IN (SELECT RQ.SERV_ORDER_NO    
                        FROM CSS_SO_REQUIREMENT RQ                      
                  WHERE RQ.SERV_ORDER_STATUS = '50'                     
                    AND (RQ.REQMNT_COMP_DT < CIS.CHAR2TIMESTAMP(
                                                           :WS-BEGIN-DT
              )               
                     OR RQ.REQMNT_COMP_DT > CIS.CHAR2TIMESTAMP(
                                                             :WS-END-DT
              )                  
                     OR RQ.REQMNT_COMP_DT IS NULL))                     
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ019
MFA-TR* MSQ049
MFA-TR* MSQ054
MFA-TR*     EXEC SQL                                                            
MFA-TR*          DELETE FROM SESSION.CSR03946_R2 SC                             
MFA-TR*           WHERE SC.SERV_ORDER_NO IN (SELECT RQ.SERV_ORDER_NO            
MFA-TR*                 FROM CSS_SO_REQUIREMENT RQ                              
MFA-TR*           WHERE RQ.SERV_ORDER_STATUS = '50'                             
MFA-TR*             AND (RQ.REQMNT_COMP_DT < :WS-BEGIN-DT                       
MFA-TR*              OR RQ.REQMNT_COMP_DT > :WS-END-DT                          
MFA-TR*              OR RQ.REQMNT_COMP_DT IS NULL))                             
MFA-TR*     END-EXEC.                                                           

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

                                                                        
            MOVE SQLCODE                  TO WS-ACTIVE-RETURN-CODE.     
                                                                        
            IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND     
               NEXT SENTENCE                                            
            ELSE                                                        
               MOVE PROGRAM-NAME          TO ABEND-PROGRAM              
               MOVE '5030'                TO ACTIVE-PARAGRAPH           
               MOVE 'DELETE'              TO ABEND-FUNCTION             
               MOVE 'CSR03946_R2'         TO TABLE-1                    
               MOVE 'SERV_ORDER_STATUS'   TO TABLE-ELEMENT-1            
               MOVE '50'                  TO HOSTVAR-ELEMENT-1          
               PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT           
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       5030-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 5040-UPDATE-CSR03946-R2.                                       *        
      ******************************************************************        
       5040-UPDATE-CSR03946-R2.                                         
            EXEC SQL                                                    
                 UPDATE SC                           
                    SET SC.WORK_AREA = (SELECT SO.WORK_AREA_ID          
                         FROM CSS_SO_DATA SO                            
                        WHERE SC.SERV_ORDER_NO = SO.SERV_ORDER_NO)      
                 FROM #CSR03946_R2 SC
                  WHERE EXISTS (SELECT 1 FROM CSS_SO_DATA SO            
                        WHERE SC.SERV_ORDER_NO = SO.SERV_ORDER_NO)      
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ019
MFA-TR* MSQ049
MFA-TR*     EXEC SQL                                                            
MFA-TR*          UPDATE SESSION.CSR03946_R2 SC                                  
MFA-TR*             SET SC.WORK_AREA = (SELECT SO.WORK_AREA_ID                  
MFA-TR*                  FROM CSS_SO_DATA SO                                    
MFA-TR*                 WHERE SC.SERV_ORDER_NO = SO.SERV_ORDER_NO)              
MFA-TR*           WHERE EXISTS (SELECT 1 FROM CSS_SO_DATA SO                    
MFA-TR*                 WHERE SC.SERV_ORDER_NO = SO.SERV_ORDER_NO)              
MFA-TR*     END-EXEC.                                                           

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

                                                                        
            MOVE SQLCODE                  TO WS-ACTIVE-RETURN-CODE.     
                                                                        
            IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND     
               NEXT SENTENCE                                            
            ELSE                                                        
               MOVE PROGRAM-NAME          TO ABEND-PROGRAM              
               MOVE '5040'                TO ACTIVE-PARAGRAPH           
               MOVE 'UPDATE'              TO ABEND-FUNCTION             
               MOVE 'CSR03946_R2'         TO TABLE-1                    
               MOVE 'WORK_AREA'           TO TABLE-ELEMENT-1            
               MOVE 'WORK_AREA_ID'        TO HOSTVAR-ELEMENT-1          
               PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT           
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       5040-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 5050-UPD-CSR03946-R2-COMP.                                     *        
      ******************************************************************        
       5050-UPD-CSR03946-R2-COMP.                                       
                                                                        
            EXEC SQL                                                    
                 UPDATE SC                           
                    SET COMPLETE_DATE = (SELECT                         
                        RTRIM(CIS.INT2CHAR(YEAR(RQ.REQMNT_COMP_DT))) + 
           RIGHT(RTRIM('0' + CIS.INT2CHAR(MONTH(RQ.REQMNT_COMP_DT))), 2
           )              
                         FROM CSS_SO_REQUIREMENT RQ                     
                        WHERE SC.SERV_ORDER_NO     = RQ.SERV_ORDER_NO   
                          AND RQ.SERV_ORDER_STATUS = '50')              
                 FROM #CSR03946_R2 SC
                  WHERE EXISTS (SELECT 1                                
                         FROM CSS_SO_REQUIREMENT RQ                     
                        WHERE SC.SERV_ORDER_NO     = RQ.SERV_ORDER_NO   
                          AND RQ.SERV_ORDER_STATUS = '50')              
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ019
MFA-TR* MSQ029
MFA-TR* MSQ031
MFA-TR* MSQ049
MFA-TR*     EXEC SQL                                                            
MFA-TR*          UPDATE SESSION.CSR03946_R2 SC                                  
MFA-TR*             SET COMPLETE_DATE = (SELECT                                 
MFA-TR*                 RTRIM(CHAR(YEAR(RQ.REQMNT_COMP_DT))) ||                 
MFA-TR*                 RIGHT(RTRIM('0' ||                                      
MFA-TR*                 CHAR(MONTH(RQ.REQMNT_COMP_DT))),2)                      
MFA-TR*                  FROM CSS_SO_REQUIREMENT RQ                             
MFA-TR*                 WHERE SC.SERV_ORDER_NO     = RQ.SERV_ORDER_NO           
MFA-TR*                   AND RQ.SERV_ORDER_STATUS = '50')                      
MFA-TR*           WHERE EXISTS (SELECT 1                                        
MFA-TR*                  FROM CSS_SO_REQUIREMENT RQ                             
MFA-TR*                 WHERE SC.SERV_ORDER_NO     = RQ.SERV_ORDER_NO           
MFA-TR*                   AND RQ.SERV_ORDER_STATUS = '50')                      
MFA-TR*     END-EXEC.                                                           

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

                                                                        
            MOVE SQLCODE                  TO WS-ACTIVE-RETURN-CODE.     
                                                                        
            IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND     
               NEXT SENTENCE                                            
            ELSE                                                        
               MOVE PROGRAM-NAME          TO ABEND-PROGRAM              
               MOVE '5050'                TO ACTIVE-PARAGRAPH           
               MOVE 'UPDATE'              TO ABEND-FUNCTION             
               MOVE 'CSR03946_R2'         TO TABLE-1                    
               MOVE 'COMPLETE_DATE'       TO TABLE-ELEMENT-1            
               MOVE 'COMPLETE_DATE'       TO HOSTVAR-ELEMENT-1          
               PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT           
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       5050-EXIT.                                                       
            EXIT.                                                       
                                                                        
      *****************************************************************         
      * 7030-OPEN-ORDER-HIST.                                         *         
      *****************************************************************         
       7030-OPEN-ORDER-HIST.                                            
                                                                        
            EXEC SQL                                                    
                 OPEN ORDER_HIST                                        
            END-EXEC.                                                   

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

                                                                        
            MOVE SQLCODE                    TO WS-ACTIVE-RETURN-CODE    
                                                                        
            IF WS-ACTIVE-RETURN-CODE EQUAL SUCCESSFUL-CALL              
               NEXT SENTENCE                                            
            ELSE                                                        
               MOVE PROGRAM-NAME                TO ABEND-PROGRAM        
               MOVE '7030'                      TO ACTIVE-PARAGRAPH     
               MOVE 'OPEN'                      TO ABEND-FUNCTION       
               MOVE 'HST_SO_ORDER_INST'         TO TABLE-1              
               PERFORM 9000-SEND-ERROR-RESULT   THRU 9000-EXIT          
               PERFORM 9900-SQL-ERROR-ROUTINE   THRU 9900-EXIT          
            END-IF.                                                     
                                                                        
       7030-EXIT.                                                       
            EXIT.                                                       
                                                                        
      *****************************************************************         
      * 7040-FETCH-ORDER-HIST.                                        *         
      *****************************************************************         
       7040-FETCH-ORDER-HIST.                                           
                                                                        
            EXEC SQL                                                    
                 FETCH ORDER_HIST                                       
                  INTO :WS-SERV-ORDER-NO                                
                      ,:WS-WORK-TYPE-CD                                 
            END-EXEC.                                                   

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

                                                                        
            MOVE SQLCODE                       TO WS-ACTIVE-RETURN-CODE 
                                                                        
            IF WS-ACTIVE-RETURN-CODE EQUAL SUCCESSFUL-CALL OR NOT-FOUND 
               IF WS-ACTIVE-RETURN-CODE EQUAL NOT-FOUND                 
                  MOVE 'Y' TO WS-NO-MORE-ROWS                           
               END-IF                                                   
            ELSE                                                        
                MOVE PROGRAM-NAME                TO ABEND-PROGRAM       
                MOVE '7040'                      TO ACTIVE-PARAGRAPH    
                MOVE 'FETCH'                     TO ABEND-FUNCTION      
                MOVE 'HST_SO_ORDER_INST'         TO TABLE-1             
                PERFORM 9000-SEND-ERROR-RESULT   THRU 9000-EXIT         
                PERFORM 9900-SQL-ERROR-ROUTINE   THRU 9900-EXIT         
            END-IF.                                                     
                                                                        
       7040-EXIT.                                                       
            EXIT.                                                       
                                                                        
      *****************************************************************         
      * 7050-CLOSE-ORDER-HIST.                                        *         
      *****************************************************************         
       7050-CLOSE-ORDER-HIST.                                           
                                                                        
            EXEC SQL                                                    
                 CLOSE ORDER_HIST                                       
            END-EXEC.                                                   

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

                                                                        
            MOVE SQLCODE                    TO  WS-ACTIVE-RETURN-CODE   
                                                                        
            IF WS-ACTIVE-RETURN-CODE EQUAL SUCCESSFUL-CALL              
               NEXT SENTENCE                                            
            ELSE                                                        
               MOVE PROGRAM-NAME                TO ABEND-PROGRAM        
               MOVE '7050'                      TO ACTIVE-PARAGRAPH     
               MOVE 'CLOSE'                     TO ABEND-FUNCTION       
               MOVE 'HST_SO_ORDER_INST'         TO TABLE-1              
               PERFORM 9000-SEND-ERROR-RESULT   THRU 9000-EXIT          
               PERFORM 9900-SQL-ERROR-ROUTINE   THRU 9900-EXIT          
            END-IF.                                                     
                                                                        
       7050-EXIT.                                                       
            EXIT.                                                       
      ******************************************************************        
      * 6010-PROCESS-ORDER-HIST.                                       *        
      ******************************************************************        
       6010-PROCESS-ORDER-HIST.                                         
                                                                        
            MOVE SPACES                         TO WS-AREA-ID           
            MOVE '000'                          TO WS-REGION-ID         
            MOVE SPACES                         TO WS-WORK-AREA         
            MOVE SPACES                         TO WS-WORK-AREA-TX      
            MOVE SPACES                         TO WS-COST-CENTER       
            MOVE 'INSTALL'                      TO WS-ACTION-CD         
            MOVE 1                              TO WS-CNT               
            MOVE -1                             TO WS-COMP-DT-NULL-IND  
            IF (WS-WORK-TYPE-CD = 'ST1' OR 'S11')                       
                MOVE 'OH'                       TO WS-OVERHEAD          
            ELSE                                                        
                MOVE 'UG'                       TO WS-OVERHEAD          
            END-IF.                                                     
                                                                        
            PERFORM 5020-INSERT-CSR03946-R2 THRU 5020-EXIT.             
            PERFORM 7040-FETCH-ORDER-HIST   THRU 7040-EXIT.             
                                                                        
       6010-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 6030-DELETE-CSR03946-R2.                                       *        
      ******************************************************************        
       6030-DELETE-CSR03946-R2.                                         
                                                                        
            EXEC SQL                                                    
                 DELETE FROM SC                      
                 FROM #CSR03946_R2 SC
                  WHERE SC.SERV_ORDER_NO IN (SELECT RQ.SERV_ORDER_NO    
                        FROM HST_SO_REQUIREMENT RQ                      
                  WHERE RQ.SERV_ORDER_STATUS = '50'                     
                    AND (RQ.REQMNT_COMP_DT < CIS.CHAR2TIMESTAMP(
                                                           :WS-BEGIN-DT
              )               
                     OR RQ.REQMNT_COMP_DT > CIS.CHAR2TIMESTAMP(
                                                             :WS-END-DT
              )                  
                     OR RQ.REQMNT_COMP_DT IS NULL))                     
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ019
MFA-TR* MSQ049
MFA-TR* MSQ054
MFA-TR*     EXEC SQL                                                            
MFA-TR*          DELETE FROM SESSION.CSR03946_R2 SC                             
MFA-TR*           WHERE SC.SERV_ORDER_NO IN (SELECT RQ.SERV_ORDER_NO            
MFA-TR*                 FROM HST_SO_REQUIREMENT RQ                              
MFA-TR*           WHERE RQ.SERV_ORDER_STATUS = '50'                             
MFA-TR*             AND (RQ.REQMNT_COMP_DT < :WS-BEGIN-DT                       
MFA-TR*              OR RQ.REQMNT_COMP_DT > :WS-END-DT                          
MFA-TR*              OR RQ.REQMNT_COMP_DT IS NULL))                             
MFA-TR*     END-EXEC.                                                           

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

                                                                        
            MOVE SQLCODE                  TO WS-ACTIVE-RETURN-CODE.     
                                                                        
            IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND     
               NEXT SENTENCE                                            
            ELSE                                                        
               MOVE PROGRAM-NAME          TO ABEND-PROGRAM              
               MOVE '6030'                TO ACTIVE-PARAGRAPH           
               MOVE 'DELETE'              TO ABEND-FUNCTION             
               MOVE 'CSR03946_R2'         TO TABLE-1                    
               MOVE 'SERV_ORDER_STATUS'   TO TABLE-ELEMENT-1            
               MOVE '50'                  TO HOSTVAR-ELEMENT-1          
               PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT           
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       6030-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 6040-UPDATE-CSR03946-R2.                                       *        
      ******************************************************************        
       6040-UPDATE-CSR03946-R2.                                         
            EXEC SQL                                                    
                 UPDATE SC                           
                    SET WORK_AREA = (SELECT SO.WORK_AREA_ID             
                         FROM HST_SO_DATA SO                            
                        WHERE SC.SERV_ORDER_NO = SO.SERV_ORDER_NO)      
                 FROM #CSR03946_R2 SC
                  WHERE EXISTS (SELECT 1                                
                         FROM HST_SO_DATA SO                            
                        WHERE SC.SERV_ORDER_NO = SO.SERV_ORDER_NO)      
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ019
MFA-TR* MSQ049
MFA-TR*     EXEC SQL                                                            
MFA-TR*          UPDATE SESSION.CSR03946_R2 SC                                  
MFA-TR*             SET WORK_AREA = (SELECT SO.WORK_AREA_ID                     
MFA-TR*                  FROM HST_SO_DATA SO                                    
MFA-TR*                 WHERE SC.SERV_ORDER_NO = SO.SERV_ORDER_NO)              
MFA-TR*           WHERE EXISTS (SELECT 1                                        
MFA-TR*                  FROM HST_SO_DATA SO                                    
MFA-TR*                 WHERE SC.SERV_ORDER_NO = SO.SERV_ORDER_NO)              
MFA-TR*     END-EXEC.                                                           

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

                                                                        
            MOVE SQLCODE                  TO WS-ACTIVE-RETURN-CODE.     
                                                                        
            IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND     
               NEXT SENTENCE                                            
            ELSE                                                        
               MOVE PROGRAM-NAME          TO ABEND-PROGRAM              
               MOVE '6040'                TO ACTIVE-PARAGRAPH           
               MOVE 'UPDATE'              TO ABEND-FUNCTION             
               MOVE 'CSR03946_R2'         TO TABLE-1                    
               MOVE 'WORK_AREA'           TO TABLE-ELEMENT-1            
               MOVE 'WORK_AREA_ID'        TO HOSTVAR-ELEMENT-1          
               PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT           
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       6040-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 6050-UPD-CSR03946-R2-COMP.                                     *        
      ******************************************************************        
       6050-UPD-CSR03946-R2-COMP.                                       
                                                                        
            EXEC SQL                                                    
                 UPDATE SC                           
                    SET COMPLETE_DATE = (SELECT                         
                        RTRIM(CIS.INT2CHAR(YEAR(RQ.REQMNT_COMP_DT))) + 
           RIGHT(RTRIM('0' + CIS.INT2CHAR(MONTH(RQ.REQMNT_COMP_DT))), 2
           )              
                         FROM HST_SO_REQUIREMENT RQ                     
                        WHERE SC.SERV_ORDER_NO     = RQ.SERV_ORDER_NO   
                          AND RQ.SERV_ORDER_STATUS = '50')              
                 FROM #CSR03946_R2 SC
                  WHERE EXISTS (SELECT 1                                
                         FROM HST_SO_REQUIREMENT RQ                     
                        WHERE SC.SERV_ORDER_NO     = RQ.SERV_ORDER_NO   
                          AND RQ.SERV_ORDER_STATUS = '50')              
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ019
MFA-TR* MSQ029
MFA-TR* MSQ031
MFA-TR* MSQ049
MFA-TR*     EXEC SQL                                                            
MFA-TR*          UPDATE SESSION.CSR03946_R2 SC                                  
MFA-TR*             SET COMPLETE_DATE = (SELECT                                 
MFA-TR*                 RTRIM(CHAR(YEAR(RQ.REQMNT_COMP_DT))) ||                 
MFA-TR*                 RIGHT(RTRIM('0' ||                                      
MFA-TR*                 CHAR(MONTH(RQ.REQMNT_COMP_DT))),2)                      
MFA-TR*                  FROM HST_SO_REQUIREMENT RQ                             
MFA-TR*                 WHERE SC.SERV_ORDER_NO     = RQ.SERV_ORDER_NO           
MFA-TR*                   AND RQ.SERV_ORDER_STATUS = '50')                      
MFA-TR*           WHERE EXISTS (SELECT 1                                        
MFA-TR*                  FROM HST_SO_REQUIREMENT RQ                             
MFA-TR*                 WHERE SC.SERV_ORDER_NO     = RQ.SERV_ORDER_NO           
MFA-TR*                   AND RQ.SERV_ORDER_STATUS = '50')                      
MFA-TR*     END-EXEC.                                                           

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

                                                                        
            MOVE SQLCODE                  TO WS-ACTIVE-RETURN-CODE.     
                                                                        
            IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND     
               NEXT SENTENCE                                            
            ELSE                                                        
               MOVE PROGRAM-NAME          TO ABEND-PROGRAM              
               MOVE '6050'                TO ACTIVE-PARAGRAPH           
               MOVE 'UPDATE'              TO ABEND-FUNCTION             
               MOVE 'CSR03946_R2'         TO TABLE-1                    
               MOVE 'COMPLETE_DATE'       TO TABLE-ELEMENT-1            
               MOVE 'COMPLETE_DATE'       TO HOSTVAR-ELEMENT-1          
               PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT           
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       6050-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 5060-UPD-CSR03946-R2-AREA-REG.                                 *        
      ******************************************************************        
       5060-UPD-CSR03946-R2-AREA-REG.                                   
                                                                        
            EXEC SQL                                                    
                 UPDATE SC                           
                    SET AREA_ID = (SELECT WA.AREA_ID
            FROM #CSR03946_R5 WA
            WHERE SC.WORK_AREA = WA.WORK_AREA_ID),
               REGION_ID = (SELECT WA.REGION_ID
            FROM #CSR03946_R5 WA
            WHERE SC.WORK_AREA = WA.WORK_AREA_ID)           
                 FROM #CSR03946_R2 SC
                  WHERE EXISTS (SELECT 1                                
                         FROM #CSR03946_R5 WA                    
                        WHERE SC.WORK_AREA = WA.WORK_AREA_ID)           
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ019
MFA-TR* MSQ049
MFA-TR* MSQ055
MFA-TR*     EXEC SQL                                                            
MFA-TR*          UPDATE SESSION.CSR03946_R2 SC                                  
MFA-TR*             SET (AREA_ID,REGION_ID) =                                   
MFA-TR*                 (SELECT WA.AREA_ID,WA.REGION_ID                         
MFA-TR*                  FROM SESSION.CSR03946_R5 WA                            
MFA-TR*                 WHERE SC.WORK_AREA = WA.WORK_AREA_ID)                   
MFA-TR*           WHERE EXISTS (SELECT 1                                        
MFA-TR*                  FROM SESSION.CSR03946_R5 WA                            
MFA-TR*                 WHERE SC.WORK_AREA = WA.WORK_AREA_ID)                   
MFA-TR*     END-EXEC.                                                           

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

                                                                        
            MOVE SQLCODE                  TO WS-ACTIVE-RETURN-CODE.     
                                                                        
            IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND     
               NEXT SENTENCE                                            
            ELSE                                                        
               MOVE PROGRAM-NAME          TO ABEND-PROGRAM              
               MOVE '5060'                TO ACTIVE-PARAGRAPH           
               MOVE 'UPDATE'              TO ABEND-FUNCTION             
               MOVE 'CSR03946_R2'         TO TABLE-1                    
               MOVE 'AREA-REGION'         TO TABLE-ELEMENT-1            
               MOVE 'AREA-REGION'         TO HOSTVAR-ELEMENT-1          
               PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT           
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       5060-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 5070-UPD-CSR03946-R2-COST-CNT.                                 *        
      ******************************************************************        
       5070-UPD-CSR03946-R2-COST-CNT.                                   
                                                                        
            EXEC SQL                                                    
                 UPDATE SC                           
                    SET WORK_AREA_TX = (SELECT WA.WORK_AREA_TX
            FROM CSS_WORK_AREA WA
            WHERE SC.WORK_AREA = WA.WORK_AREA_ID),
               COST_CENTER = (SELECT WA.COST_CENTER
            FROM CSS_WORK_AREA WA
            WHERE SC.WORK_AREA = WA.WORK_AREA_ID)           
                 FROM #CSR03946_R2 SC
                  WHERE EXISTS (SELECT 1                                
                         FROM CSS_WORK_AREA WA                          
                        WHERE SC.WORK_AREA = WA.WORK_AREA_ID)           
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ019
MFA-TR* MSQ049
MFA-TR* MSQ055
MFA-TR*     EXEC SQL                                                            
MFA-TR*          UPDATE SESSION.CSR03946_R2 SC                                  
MFA-TR*             SET (WORK_AREA_TX, COST_CENTER) =                           
MFA-TR*                 (SELECT WA.WORK_AREA_TX, WA.COST_CENTER                 
MFA-TR*                  FROM CSS_WORK_AREA WA                                  
MFA-TR*                 WHERE SC.WORK_AREA = WA.WORK_AREA_ID)                   
MFA-TR*           WHERE EXISTS (SELECT 1                                        
MFA-TR*                  FROM CSS_WORK_AREA WA                                  
MFA-TR*                 WHERE SC.WORK_AREA = WA.WORK_AREA_ID)                   
MFA-TR*     END-EXEC.                                                           

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

                                                                        
            MOVE SQLCODE                  TO WS-ACTIVE-RETURN-CODE.     
                                                                        
            IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND     
               NEXT SENTENCE                                            
            ELSE                                                        
               MOVE PROGRAM-NAME           TO ABEND-PROGRAM             
               MOVE '5070'                 TO ACTIVE-PARAGRAPH          
               MOVE 'UPDATE'               TO ABEND-FUNCTION            
               MOVE 'CSR03946_R2'          TO TABLE-1                   
               MOVE 'WRK-AREA-COST-CENTER' TO TABLE-ELEMENT-1           
               MOVE 'WRK-AREA-COST-CENTER' TO HOSTVAR-ELEMENT-1         
               PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT           
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       5070-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 5080-DEL-CSR03946-R2-REG-ID.                                   *        
      ******************************************************************        
       5080-DEL-CSR03946-R2-REG-ID.                                     
                                                                        
            EXEC SQL                                                    
                 DELETE                                                 
                   FROM #CSR03946_R2                             
                  WHERE REGION_ID = '000'                               
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ049
MFA-TR*     EXEC SQL                                                            
MFA-TR*          DELETE                                                         
MFA-TR*            FROM SESSION.CSR03946_R2                                     
MFA-TR*           WHERE REGION_ID = '000'                                       
MFA-TR*     END-EXEC.                                                           

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

                                                                        
            MOVE SQLCODE                  TO WS-ACTIVE-RETURN-CODE.     
                                                                        
            IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND     
               NEXT SENTENCE                                            
            ELSE                                                        
               MOVE PROGRAM-NAME           TO ABEND-PROGRAM             
               MOVE '5080'                 TO ACTIVE-PARAGRAPH          
               MOVE 'DELETE'               TO ABEND-FUNCTION            
               MOVE 'CSR03946_R2'          TO TABLE-1                   
               MOVE 'REGION_ID'            TO TABLE-ELEMENT-1           
               MOVE '000'                  TO HOSTVAR-ELEMENT-1         
               PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT           
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       5080-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 5090-INSERT-CSR03946-R4.                                       *        
      ******************************************************************        
       5090-INSERT-CSR03946-R4.                                         
            EXEC SQL                                                    
                 INSERT INTO #CSR03946_R4                        
                  SELECT REGION_ID                                      
                        ,COMPLETE_DATE                                  
                        ,0                                              
                        ,COUNT(OVERHEAD_UNDERGROUND)                    
                        ,0                                              
                    FROM #CSR03946_R2                            
                   WHERE OVERHEAD_UNDERGROUND IN ('UG','OH')            
                   GROUP BY REGION_ID, COMPLETE_DATE                    
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ049
MFA-TR*     EXEC SQL                                                            
MFA-TR*          INSERT INTO SESSION.CSR03946_R4                                
MFA-TR*           SELECT REGION_ID                                              
MFA-TR*                 ,COMPLETE_DATE                                          
MFA-TR*                 ,0                                                      
MFA-TR*                 ,COUNT(OVERHEAD_UNDERGROUND)                            
MFA-TR*                 ,0                                                      
MFA-TR*             FROM SESSION.CSR03946_R2                                    
MFA-TR*            WHERE OVERHEAD_UNDERGROUND IN ('UG','OH')                    
MFA-TR*            GROUP BY REGION_ID, COMPLETE_DATE                            
MFA-TR*     END-EXEC.                                                           

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

            MOVE SQLCODE   TO WS-ACTIVE-RETURN-CODE.                    
                                                                        
            IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND     
               IF WS-ACTIVE-RETURN-CODE = NOT-FOUND                     
                  MOVE SPACES                 TO S-CURR-VALUE           
                  PERFORM 8100-SEND-RESULT    THRU 8100-EXIT            
               END-IF                                                   
            ELSE                                                        
               MOVE PROGRAM-NAME           TO ABEND-PROGRAM             
               MOVE SQLCODE                TO ABEND-SQLCODE             
               MOVE '5090'                 TO ACTIVE-PARAGRAPH          
               MOVE 'INSERT'               TO ABEND-FUNCTION            
               MOVE SPACES                 TO ABEND-SQL-PREDICATES      
                                              ABEND-TABLES              
               MOVE 'CSR03946_R4'          TO TABLE-1                   
               MOVE 'UNDERGROUND_OVERHEAD' TO TABLE-ELEMENT-1           
               MOVE 'UG-OH'                TO HOSTVAR-ELEMENT-1         
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       5090-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 5100-INSERT-CSR03946-R4-U-TOT.                                 *        
      ******************************************************************        
       5100-INSERT-CSR03946-R4-U-TOT.                                   
            EXEC SQL                                                    
                 INSERT INTO #CSR03946_R4                        
                  SELECT REGION_ID                                      
                        ,COMPLETE_DATE                                  
                        ,COUNT(OVERHEAD_UNDERGROUND)                    
                        ,0                                              
                        ,0                                              
                    FROM #CSR03946_R2                            
                   WHERE OVERHEAD_UNDERGROUND = 'UG'                    
                   GROUP BY REGION_ID, COMPLETE_DATE                    
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ049
MFA-TR*     EXEC SQL                                                            
MFA-TR*          INSERT INTO SESSION.CSR03946_R4                                
MFA-TR*           SELECT REGION_ID                                              
MFA-TR*                 ,COMPLETE_DATE                                          
MFA-TR*                 ,COUNT(OVERHEAD_UNDERGROUND)                            
MFA-TR*                 ,0                                                      
MFA-TR*                 ,0                                                      
MFA-TR*             FROM SESSION.CSR03946_R2                                    
MFA-TR*            WHERE OVERHEAD_UNDERGROUND = 'UG'                            
MFA-TR*            GROUP BY REGION_ID, COMPLETE_DATE                            
MFA-TR*     END-EXEC.                                                           

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

            MOVE SQLCODE   TO WS-ACTIVE-RETURN-CODE.                    
                                                                        
            IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND     
               IF WS-ACTIVE-RETURN-CODE = NOT-FOUND                     
                  MOVE SPACES                 TO S-CURR-VALUE           
                  PERFORM 8100-SEND-RESULT    THRU 8100-EXIT            
               END-IF                                                   
            ELSE                                                        
               MOVE PROGRAM-NAME           TO ABEND-PROGRAM             
               MOVE SQLCODE                TO ABEND-SQLCODE             
               MOVE '5100'                 TO ACTIVE-PARAGRAPH          
               MOVE 'INSERT'               TO ABEND-FUNCTION            
               MOVE SPACES                 TO ABEND-SQL-PREDICATES      
                                              ABEND-TABLES              
               MOVE 'CSR03946_R4'          TO TABLE-1                   
               MOVE 'UNDERGROUND_OVERHEAD' TO TABLE-ELEMENT-1           
               MOVE 'UG'                   TO HOSTVAR-ELEMENT-1         
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       5100-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 5110-INSERT-CSR03946-R3.                                       *        
      ******************************************************************        
       5110-INSERT-CSR03946-R3.                                         
            EXEC SQL                                                    
                 INSERT INTO #CSR03946_R3                        
                  SELECT REGION_ID                                      
                        ,COMPLETE_DATE                                  
                        ,SUM(UNDER_TOT)                                 
                        ,SUM(OU_TOT)                                    
                        ,0                                              
                    FROM #CSR03946_R4                            
                   GROUP BY REGION_ID, COMPLETE_DATE                    
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ049
MFA-TR*     EXEC SQL                                                            
MFA-TR*          INSERT INTO SESSION.CSR03946_R3                                
MFA-TR*           SELECT REGION_ID                                              
MFA-TR*                 ,COMPLETE_DATE                                          
MFA-TR*                 ,SUM(UNDER_TOT)                                         
MFA-TR*                 ,SUM(OU_TOT)                                            
MFA-TR*                 ,0                                                      
MFA-TR*             FROM SESSION.CSR03946_R4                                    
MFA-TR*            GROUP BY REGION_ID, COMPLETE_DATE                            
MFA-TR*     END-EXEC.                                                           

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

            MOVE SQLCODE   TO WS-ACTIVE-RETURN-CODE.                    
                                                                        
            IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND     
               IF WS-ACTIVE-RETURN-CODE = NOT-FOUND                     
                  MOVE SPACES                 TO S-CURR-VALUE           
                  PERFORM 8100-SEND-RESULT    THRU 8100-EXIT            
               END-IF                                                   
            ELSE                                                        
               MOVE PROGRAM-NAME         TO ABEND-PROGRAM               
               MOVE SQLCODE              TO ABEND-SQLCODE               
               MOVE '5110'               TO ACTIVE-PARAGRAPH            
               MOVE 'INSERT'             TO ABEND-FUNCTION              
               MOVE SPACES               TO ABEND-SQL-PREDICATES        
                                            ABEND-TABLES                
               MOVE 'CSR03946_R3'        TO TABLE-1                     
               MOVE 'SUM(UNDER_TOT)'     TO TABLE-ELEMENT-1             
               MOVE 'SUM(OU_TOT)'        TO HOSTVAR-ELEMENT-1           
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       5110-EXIT.                                                       
            EXIT.                                                       
                                                                        
      ******************************************************************        
      * 5120-UPDATE-CSR03946-R3.                     *                          
      ******************************************************************        
       5120-UPDATE-CSR03946-R3.                                         
                                                                        
            EXEC SQL                                                    
                 UPDATE #CSR03946_R3                             
                    SET DIVISOR = ROUND(((UNDER_TOT / OU_TOT) * 10),0)  
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ049
MFA-TR*     EXEC SQL                                                            
MFA-TR*          UPDATE SESSION.CSR03946_R3                                     
MFA-TR*             SET DIVISOR = ROUND(((UNDER_TOT / OU_TOT) * 10),0)          
MFA-TR*     END-EXEC.                                                           

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

                                                                        
            MOVE SQLCODE                  TO WS-ACTIVE-RETURN-CODE.     
                                                                        
            IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND     
               NEXT SENTENCE                                            
            ELSE                                                        
               MOVE PROGRAM-NAME           TO ABEND-PROGRAM             
               MOVE '5120'                 TO ACTIVE-PARAGRAPH          
               MOVE 'UPDATE'               TO ABEND-FUNCTION            
               MOVE 'CSR03946_R3'          TO TABLE-1                   
               MOVE 'DIVISOR'              TO TABLE-ELEMENT-1           
               MOVE 'UNDER_TOT/OU_TOT'     TO HOSTVAR-ELEMENT-1         
               PERFORM 9000-SEND-ERROR-RESULT  THRU 9000-EXIT           
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       5120-EXIT.                                                       
            EXIT.                                                       
      ******************************************************************        
      *8100-SEND-RESULT                                                *        
      ******************************************************************        
       8100-SEND-RESULT.                                                
      *                                                                         
            EXEC SQL                                                    
                 INSERT INTO #CSR03946_R1                        
                 SELECT REGION_ID                                       
                       ,WORK_AREA                                       
                       ,WORK_AREA_TX                                    
                       ,COST_CENTER                                     
                       ,ACTION_CD + ' - ' + OVERHEAD_UNDERGROUND      
                       ,SUM(CNT)                                        
                       ,BEGIN_DATE                                      
                       ,END_DATE                                        
                 FROM #CSR03946_R2                               
                 GROUP BY REGION_ID                                     
                         ,WORK_AREA                                     
                         ,WORK_AREA_TX                                  
                         ,COST_CENTER                                   
                         ,ACTION_CD + ' - ' + OVERHEAD_UNDERGROUND    
                         ,BEGIN_DATE                                    
                         ,END_DATE                                      
            END-EXEC.                                                   

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ031
MFA-TR* MSQ049
MFA-TR*     EXEC SQL                                                            
MFA-TR*          INSERT INTO SESSION.CSR03946_R1                                
MFA-TR*          SELECT REGION_ID                                               
MFA-TR*                ,WORK_AREA                                               
MFA-TR*                ,WORK_AREA_TX                                            
MFA-TR*                ,COST_CENTER                                             
MFA-TR*                ,ACTION_CD || ' - ' || OVERHEAD_UNDERGROUND              
MFA-TR*                ,SUM(CNT)                                                
MFA-TR*                ,BEGIN_DATE                                              
MFA-TR*                ,END_DATE                                                
MFA-TR*          FROM SESSION.CSR03946_R2                                       
MFA-TR*          GROUP BY REGION_ID                                             
MFA-TR*                  ,WORK_AREA                                             
MFA-TR*                  ,WORK_AREA_TX                                          
MFA-TR*                  ,COST_CENTER                                           
MFA-TR*                  ,ACTION_CD || ' - ' || OVERHEAD_UNDERGROUND            
MFA-TR*                  ,BEGIN_DATE                                            
MFA-TR*                  ,END_DATE                                              
MFA-TR*     END-EXEC.                                                           

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

            MOVE SQLCODE              TO WS-ACTIVE-RETURN-CODE.         
                                                                        
            IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND     
               IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL               
                  ADD +1                    TO  CTR-ROWS                
               END-IF                                                   
            ELSE                                                        
               MOVE PROGRAM-NAME         TO ABEND-PROGRAM               
               MOVE SQLCODE              TO ABEND-SQLCODE               
               MOVE '8100'               TO ACTIVE-PARAGRAPH            
               MOVE 'INSERT'             TO ABEND-FUNCTION              
               MOVE SPACES               TO ABEND-SQL-PREDICATES        
                                            ABEND-TABLES                
               MOVE 'CSR03946_R1'        TO TABLE-1                     
               MOVE SPACES               TO TABLE-ELEMENT-1             
               MOVE SPACES               TO HOSTVAR-ELEMENT-1           
               PERFORM 9900-SQL-ERROR-ROUTINE  THRU 9900-EXIT           
            END-IF.                                                     
                                                                        
       8100-EXIT.                                                       
            EXIT.                                                       
      ******************************************************************        
      * 9900- JOURNALING / ERROR HANDLING INCLUDE        *                      
      ******************************************************************        
      *    EXEC SQL                                                             
      *       INCLUDE CPD00300                                                  
      *    END-EXEC.                                                            
                                                                        
           EXEC SQL                                                             
              INCLUDE CPDSP300                                                  
           END-EXEC.                                                            
      ******************************************************************        
      *       END PROGRAM COPYLIB                                      *        
      ******************************************************************        
      *    COPY CPD00302.                                                       
           EXEC SQL                                                             
              INCLUDE CPD00320                                                  
           END-EXEC.                                                            
