       IDENTIFICATION DIVISION.                                         
       PROGRAM-ID.   PCSSO164.                                          
       AUTHOR.       PRASAD ALLADA.                                     
       DATE-WRITTEN. OCT 2009.                                          
       DATE-COMPILED.                                                   
      ******************************************************************        
      **                     PROGRAM SUMMARY                          **        
      ******************************************************************        
      **   THIS PROGRAM CREATES ROWS INTO CSS_BATCH_EMAIL WHEN A SET  **        
      **   OR CUSTOMER CHANGE OR OCCUPANT CHANGE ORDER IS POSTED ON A **        
      **   NEW ACCOUNT WHEN CUSTOMER IS ENROLLED IN RECURRING CREDIT  **        
      **   CARD PAYMENT OPTION.                                       **        
      ******************************************************************        
      **           BASIC BATCH PARAGRAPH 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 **        
      ******************************************************************        
      **                PROGRAM  MODIFICATION  LOG                    **        
      ******************************************************************        
      ** DATE     USER ID  REASON                                     **        
      ** -------  -------  ------                                     **        
      ** 10/2009  PA94870  INITIAL IMPLEMENTATION FOR PROJCSR526                
A02525** 07/2010  PA94870  APPL00002525 - CORRECT GENERATION OF                 
A02525**                   RECURRING CREDIT CARD PAPERLESS EMAIL                
A02525**                   SET ORDERS; CHANGE ORDERS ARE SUPPOSED TO            
A02525**                   LOOK FOR ALL PREVIOUS ACCOUNTS IN AUTOCARD           
A02525**                   TABLE TO DETERMINE WHETHER TO SEND A LETTER;         
A02525**                   EMAILS SHOULD GO TO PREV ACCTS ENROLLED              
A02525** 09/16/11 DB18339  CHANGED AUTOPAY CHECK TO BE FASTER                   
ACT070*  02/25/16 MC95456  USE NEXT VALUE FOR BATCH_EMAIL_ID      **            
ACT070*  APPL5460          IN CSS_BATCH_EMAIL TABLE.              **            
      **                                                             **         
      ******************************************************************        
      *                                                                         
       ENVIRONMENT DIVISION.                                            
       CONFIGURATION SECTION.                                           
       SOURCE-COMPUTER.    IBM-370.                                     
       OBJECT-COMPUTER.    IBM-370.                                     
      *                                                                         
       DATA DIVISION.                                                   
      *                                                                         
       WORKING-STORAGE SECTION.                                         

MSQ017 01 MFA-PROGRAM-ID PIC X(8) VALUE 'PCSSO164'.
MSQ017     COPY MFASQLM.
      ******************************************************************        
      *                                                                *        
      *             'WORKING STORAGE FOR PCSSO164 STARTS HERE'.        *        
      ******************************************************************        
                                                                        
       01  WS-START                        PIC X(40)                    
           VALUE 'WORKING STORAGE FOR PCSSO164 STARTS HERE'.            
                                                                        
       01  RS-RPC-RETURN-CODE.                                          
           05  RS-RETURN-CODE              PIC S9(04) COMP VALUE 0.     
           05  RS-RETURN-CODE-DISP         PIC +Z(04).                  
                                                                        
       01  WS-VARIABLES.                                                
           05  WS-WORK-DATE          PIC X(10) VALUE SPACES.            
           05  WS-EVENT-CD           PIC X(05) VALUE 'ECS29'.           
           05  WS-EVENT-DT           PIC X(10) VALUE SPACES.            
           05  WS-PROCESSED-CD       PIC X(01).                         
           05  WS-PROCESSED-DATE     PIC X(10) VALUE SPACES.            
           05  WS-INSERT-ROW         PIC X(01) VALUE 'N'.               
           05  WS-NO                 PIC X(01) VALUE 'N'.               
           05  WS-YES                PIC X(01) VALUE 'Y'.               
           05  WS-WEEKEND-HLDY       PIC X(01) VALUE 'N'.               
           05  WS-PGRMNAME           PIC X(08) VALUE 'PCSSO164'.        
           05  WS-CHECK-ACCOUNT-EXISTS PIC X(01) VALUE 'N'.             
           05  WS-DISPLAY-SQLCODE    PIC -(8)9.                         
           05  WS-COMMIT-COUNT       PIC S9(09) COMP VALUE 0.           
           05  WS-NULL-IND-01        PIC S9(4)  COMP VALUE 0.           
           05  WS-LAST-RUN-DATA.                                        
             10 WS-LAST-RUN            PIC X(09) VALUE 'RUN DATE='.     
             10 WS-LAST-RUN-DT         PIC X(10).                       
             10 FILLER                 PIC X(61) VALUE SPACES.          
       01  WS-UPD-DATETIME-LOW       PIC X(26).                         
       01  WS-UPD-DATE1-TS       REDEFINES WS-UPD-DATETIME-LOW.         
                05  WS-UPD-DATE1              PIC X(10).                
                05  WS-UPD-DATE-LOW           PIC X(16).                
       01  WS-UPD-DATETIME-HIGH          PIC X(26) VALUE SPACES.        
       01  WS-UPD-DATE2-TS       REDEFINES WS-UPD-DATETIME-HIGH.        
                05  WS-UPD-DATE2              PIC X(10).                
                05  WS-UPD-DATE-HIGH          PIC X(16).                
                                                                        
       01  WS-SWITCHES-AND-INDICATORS.                                  
           05  WS-END-ACCOUNT-CUR-SW       PIC X(1) VALUE 'N'.          
               88  WS-END-ACCOUNT-CUR      VALUE 'Y'.                   
               88  WS-NOT-END-ACCOUNT-CUR  VALUE 'N'.                   
           05  WS-FETCH-SUCCESS-SW         PIC X(1) VALUE 'N'.          
               88  WS-FETCH-SUCCESS        VALUE 'Y'.                   
               88  WS-FETCH-NOT-SUCCESS    VALUE 'N'.                   
                                                                        
      /*****   SQL WORK VARIABLES.                                              
       COPY CWS00303.                                                           
                                                                        
      /*****   WS AREA FOR ABEND SWITCH.                                        
       COPY CWS09900.                                                           
                                                                        
      /*****   WS AREA FOR ABEND WORK.                                          
       COPY CWS00010.                                                           
                                                                        
       01  WS-END                          PIC X(40)                    
           VALUE 'DB2 INCLUDES FOR PCSSO164 START HERE '.               
      ******************************************************************        
      **                                                              **        
      **  TABLE DECLARATIONS GO AFTER OTHER WORKING STORAGE ITEMS     **        
      **  (IF DIRECT ACCESS TO DB2 TABLES IS ALLOWED). FIRST ITEM     **        
      **  WILL ALWAYS BE SQLCA.                                       **        
      **                                                              **        
      ******************************************************************        
           EXEC SQL                                                             
               INCLUDE SQLCA                                                    
           END-EXEC.                                                            
      ******************************************************************        
      *                    CSS_JOB_PARM DCLGEN                         *        
      ******************************************************************        
           EXEC SQL                                                             
               INCLUDE TBJBPARM                                                 
           END-EXEC.                                                            
      ******************************************************************        
      *                     CSS_ACCOUNT DCLGEN                         *        
      ******************************************************************        
           EXEC SQL                                                             
               INCLUDE TBACCT                                                   
           END-EXEC.                                                            
      ******************************************************************        
      *                     CSS_BATCH_EMAIL                            *        
      ******************************************************************        
           EXEC SQL                                                             
               INCLUDE TBBEMAIL                                                 
           END-EXEC.                                                            
      ******************************************************************        
      *                      CSS_SO_DATA                               *        
      ******************************************************************        
           EXEC SQL                                                             
               INCLUDE TBSODAT                                                  
           END-EXEC.                                                            
      ******************************************************************        
      *                       CSS_HOLIDAY                              *        
      ******************************************************************        
           EXEC SQL                                                             
               INCLUDE TBHLDAY                                                  
           END-EXEC.                                                            
      ******************************************************************        
      *                        COPY BOOK FOR CPD00315                  *        
      ******************************************************************        
           EXEC SQL                                                             
               INCLUDE CWS00315                                                 
           END-EXEC.                                                            
      ******************************************************************        
      **                                                              **        
      **  CURSOR DECLARATIONS (IF ANY) GO LAST                        **        
      **                                                              **        
      ******************************************************************        
      **CURSOR TO EXTRACT THE ACCOUNT CODE ACCOUNTS                             
           EXEC SQL                                                     
              DECLARE ACCOUNT_CUR CURSOR WITH HOLD FOR                  
                SELECT AT.ACCOUNT_NO                                    
                      ,AT.CUSTOMER_NO                                   
                      ,AT.COMPANY_NO                                    
                FROM CSS_SO_DATA          SO WITH(READUNCOMMITTED)              
                    ,CSS_SO_UPDATE        SU WITH(READUNCOMMITTED)              
                    ,CSS_ACCOUNT          AT WITH(READUNCOMMITTED)              
                WHERE                                                   
                        SO.ACCOUNT_NO = AT.ACCOUNT_NO                   
                    AND SO.ORDER_TYPE_CD IN                             
                        ('FM002','CC001','CC002','CC003','NC001')       
                    AND SO.ORDER_STATE_CD = 'Z'                         
                    AND SO.SERV_ORDER_NO = SU.SERV_ORDER_NO             
                    AND SU.UPDATE_DATETIME >= CIS.CHAR2TIMESTAMP(
                                                   :WS-UPD-DATETIME-LOW
              )      
                    AND SU.UPDATE_DATETIME <= CIS.CHAR2TIMESTAMP(
                                                  :WS-UPD-DATETIME-HIGH
              )     
                    AND SU.PANEL_NO = '090'                             
              FOR READ ONLY                                     
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ004
MFA-TR* MSQ022
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*       DECLARE ACCOUNT_CUR CURSOR WITH HOLD FOR                          
MFA-TR*         SELECT AT.ACCOUNT_NO                                            
MFA-TR*               ,AT.CUSTOMER_NO                                           
MFA-TR*               ,AT.COMPANY_NO                                            
MFA-TR*         FROM CSS_SO_DATA          SO                                    
MFA-TR*             ,CSS_SO_UPDATE        SU                                    
MFA-TR*             ,CSS_ACCOUNT          AT                                    
MFA-TR*         WHERE                                                           
MFA-TR*                 SO.ACCOUNT_NO = AT.ACCOUNT_NO                           
MFA-TR*             AND SO.ORDER_TYPE_CD IN                                     
MFA-TR*                 ('FM002','CC001','CC002','CC003','NC001')               
MFA-TR*             AND SO.ORDER_STATE_CD = 'Z'                                 
MFA-TR*             AND SO.SERV_ORDER_NO = SU.SERV_ORDER_NO                     
MFA-TR*             AND SU.UPDATE_DATETIME >= :WS-UPD-DATETIME-LOW              
MFA-TR*             AND SU.UPDATE_DATETIME <= :WS-UPD-DATETIME-HIGH             
MFA-TR*             AND SU.PANEL_NO = '090'                                     
MFA-TR*       FOR FETCH ONLY WITH UR                                            
MFA-TR*       QUERYNO 7000                                                      
MFA-TR*    END-EXEC.                                                            
      *                                                                         
       PROCEDURE DIVISION.                                              
      ****************************************************************          
      **                                                            **          
      **  0000-MAINLINE                                             **          
      **      CONTROLS MAIN PROCESSING OF PROGRAM                   **          
      **                                                            **          
      ****************************************************************          
       0000-MAINLINE.                                                   
      *                                                                         
           PERFORM 0100-INITIALIZATION  THRU 0100-EXIT.                 
           PERFORM 1000-PROCESS-ACCOUNT THRU 1000-EXIT                  
             UNTIL WS-END-ACCOUNT-CUR.                                  
           PERFORM 7020-CLOSE-ACCOUNT   THRU 7020-EXIT.                 
           PERFORM 8900-UPDATE-RUN-DATE THRU 8900-EXIT.                 
           PERFORM 9000-TERMINATE       THRU 9000-EXIT.                 
           STOP RUN.                                                    
      *                                                                         
       0000-EXIT.                                                       
           EXIT.                                                        
      *                                                                         
      ****************************************************************          
      **                                                            **          
      **  0100-INITIALIZATION                                       **          
      **      COMMON INITIALIZATION ROUTINE                         **          
      **                                                            **          
      ****************************************************************          
      *                                                                         
       0100-INITIALIZATION.                                             
      *                                                                         
           MOVE '0100'                   TO WS-ACTIVE-PARAGRAPH.        
           DISPLAY '******************************************'.        
           DISPLAY '**     PCSSO164 PROCESSING              **'.        
           DISPLAY '** CURRENT PARAGRAPH = ' WS-ACTIVE-PARAGRAPH.       
           DISPLAY '******************************************'.        
                                                                        
           PERFORM 7500-GET-WORK-DATE  THRU 7500-EXIT.                  
           PERFORM 3000-GET-PREV-DAY   THRU 3000-EXIT.                  
           PERFORM 7000-OPEN-ACCOUNT   THRU 7000-EXIT.                  
      *                                                                         
       0100-EXIT.                                                       
           EXIT.                                                        
      *                                                                         
      ****************************************************************          
      **  1000-PROCESS-ACCOUNT                                      **          
      **      GETS THE ACCOUNT OPTION CODE DETAILS AND WRITE THEM   **          
      **      IN THE OUTPUT EXTRACT FILE                            **          
      ****************************************************************          
       1000-PROCESS-ACCOUNT.                                            
      *                                                                         
           PERFORM 7010-FETCH-ACCOUNT           THRU 7010-EXIT.         
           IF WS-FETCH-SUCCESS                                          
              PERFORM 7400-CHECK-AUTO-PAY       THRU 7400-EXIT          
              IF WS-INSERT-ROW = 'Y'                                    
                 PERFORM 7300-CHECK-ACCOUNT-EXIST  THRU 7300-EXIT       
                 IF WS-CHECK-ACCOUNT-EXISTS = 'N'                       
ACT070              PERFORM 7600-SET-NEXT-VALUE       THRU 7600-EXIT    
                    PERFORM 8500-INSERT-ACCOUNT       THRU 8500-EXIT    
                 END-IF                                                 
              END-IF                                                    
           END-IF.                                                      
                                                                        
      *                                                                         
       1000-EXIT.                                                       
           EXIT.                                                        
      *                                                                         
                                                                        
       3000-GET-PREV-DAY.                                               
           MOVE '3000' TO WS-ACTIVE-PARAGRAPH.                          
                                                                        
      *    CALCULATE PREV BUSINESS DAY FROM THE JOB PARM DATE                   
           MOVE WS-WORK-DATE TO WS-NEW-BUSINESS-DAY                     
            PERFORM UNTIL NEXT-BUSINESS-DAY-FOUND                       
               PERFORM 6525-PREV-BUSINESS-DAY       THRU                
                    6525-PREV-BUSINESS-DAY-EXIT                         
               DISPLAY '** WORK  DATE = ' WS-NEW-BUSINESS-DAY           
               MOVE WS-NEW-BUSINESS-DAY TO WS-EVENT-DT                  
               MOVE WS-NEW-BUSINESS-DAY TO WS-UPD-DATE1                 
      *        IF THERE IS A WEEKEND OR A HOLIDAY THEN THOSE DATES              
      *        SHOULD BE INCLUDED WHILE RETRIEVING ORDERS                       
               IF (WS-DAY-OF-WEEK EQUAL WS-SATURDAY                     
                  OR WS-DAY-OF-WEEK EQUAL WS-SUNDAY                     
                  OR WS-HOLIDAY-EXISTS EQUAL 'Y')                       
                  MOVE 'Y' TO WS-WEEKEND-HLDY                           
               END-IF                                                   
               IF WS-WEEKEND-HLDY = 'Y'                                 
                MOVE WS-WORK-DATE TO WS-UPD-DATE2                       
                MOVE '-00.00.00.000000' TO WS-UPD-DATE-HIGH             
               ELSE                                                     
                MOVE WS-NEW-BUSINESS-DAY TO WS-UPD-DATE2                
ACT070          MOVE '-23.59.59.000000' TO WS-UPD-DATE-HIGH             
               END-IF                                                   
            END-PERFORM.                                                
                                                                        
           MOVE '-00.00.00.000000' TO WS-UPD-DATE-LOW.                  
                                                                        
      *    CALCULATE THE NEXT BUSINESS DATE. JOB PARM TABLE                     
      *    WILL BE UPDATED WITH THIS DATE UPON SUCCESSFULL RUN                  
                                                                        
           MOVE 'N' TO WS-NEXT-BUSINESS-DAY                             
           MOVE WS-WORK-DATE TO WS-NEW-BUSINESS-DAY                     
           PERFORM 6500-GET-NEXT-BUSINESS-DAY THRU                      
                    6500-NEXT-BUSINESS-DAY-EXIT UNTIL                   
                      NEXT-BUSINESS-DAY-FOUND                           
           MOVE WS-NEW-BUSINESS-DAY  TO WS-LAST-RUN-DT.                 
      *                                                                         
       3000-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************33047900
      * 6525-PREV-BUSINESS-DAY                                         *33048000
      * DETERMINE THE PREVIOUS BUSINESS DAY                            *33048100
      ******************************************************************33048200
                                                                        
           EXEC SQL                                                     33048400
               INCLUDE CPD00315                                         33048500
           END-EXEC.                                                    33048600
                                                                        
      *                                                                         
      ****************************************************************          
      **  7000-OPEN-ACCOUNT                                         **          
      **      OPEN THE MAIN CURSOR ACCOUNT_CUR                      **          
      ****************************************************************          
      *                                                                         
       7000-OPEN-ACCOUNT.                                               
      *                                                                         
           MOVE '7000' TO WS-ACTIVE-PARAGRAPH.                          
           EXEC SQL                                                     
               OPEN ACCOUNT_CUR                                         
           END-EXEC.                                                    

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

                                                                        
           MOVE SQLCODE TO WS-ACTIVE-RETURN-CODE.                       
                                                                        
           IF  WS-ACTIVE-RETURN-CODE NOT = SUCCESSFUL-CALL              
               MOVE WS-ACTIVE-RETURN-CODE TO WS-DISPLAY-SQLCODE         
               DISPLAY '*******************************************'    
               DISPLAY '       ABENDING PROGRAM '                       
               DISPLAY '       OPEN ACCOUNT_CUR '                       
               DISPLAY '       7000-OPEN-ACCOUNT '                      
               DISPLAY ' RETURN CODE    = ' WS-DISPLAY-SQLCODE          
               DISPLAY '*******************************************'    
               PERFORM 9900-ABEND      THRU 9900-EXIT                   
           END-IF.                                                      
      *                                                                         
       7000-EXIT.                                                       
           EXIT.                                                        
      *                                                                         
      ****************************************************************          
      **  7010-FETCH-ACCOUNT                                        **          
      **      FETCH THE ACCOUNTS FOR PROCESSING                     **          
      ****************************************************************          
      *                                                                         
       7010-FETCH-ACCOUNT.                                              
      *                                                                         
           MOVE '7010' TO WS-ACTIVE-PARAGRAPH.                          
           EXEC SQL                                                     
               FETCH ACCOUNT_CUR                                        
                INTO  :AT-ACCOUNT-NO                                    
                     ,:AT-CUSTOMER-NO                                   
                     ,:AT-COMPANY-NO                                    
           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                   
              SET WS-FETCH-SUCCESS TO TRUE                              
           ELSE                                                         
              IF WS-ACTIVE-RETURN-CODE = NOT-FOUND                      
                 SET WS-END-ACCOUNT-CUR TO TRUE                         
                 SET WS-FETCH-NOT-SUCCESS TO TRUE                       
              ELSE                                                      
                 MOVE WS-ACTIVE-RETURN-CODE TO WS-DISPLAY-SQLCODE       
                 DISPLAY '*******************************************'  
                 DISPLAY '       ABENDING PROGRAM '                     
                 DISPLAY '       FETCH ACCOUNT_CUR '                    
                 DISPLAY '       7010-FETCH-ACCOUNT '                   
                 DISPLAY ' RETURN CODE    = ' WS-DISPLAY-SQLCODE        
                 DISPLAY ' ACCOUNT NO     = ' AT-ACCOUNT-NO             
                 DISPLAY '*******************************************'  
                 PERFORM 9900-ABEND      THRU 9900-EXIT                 
              END-IF                                                    
           END-IF.                                                      
      *                                                                         
       7010-EXIT.                                                       
           EXIT.                                                        
      *                                                                         
      ****************************************************************          
      **  7020-CLOSE-ACCOUNT                                        **          
      **      CLOSE THE MAIN CURSOR ACCOUNT_CUR                     **          
      ****************************************************************          
      *                                                                         
       7020-CLOSE-ACCOUNT.                                              
      *                                                                         
           MOVE '7020' TO WS-ACTIVE-PARAGRAPH.                          
           EXEC SQL                                                     
               CLOSE ACCOUNT_CUR                                        
           END-EXEC.                                                    

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

                                                                        
           MOVE SQLCODE                TO WS-ACTIVE-RETURN-CODE.        
                                                                        
           IF WS-ACTIVE-RETURN-CODE NOT = SUCCESSFUL-CALL               
                 MOVE WS-ACTIVE-RETURN-CODE TO WS-DISPLAY-SQLCODE       
                 DISPLAY '*******************************************'  
                 DISPLAY '       ABENDING PROGRAM '                     
                 DISPLAY '       CLOSE ACCOUNT_CUR '                    
                 DISPLAY '       7020-CLOSE-ACCOUNT '                   
                 DISPLAY ' RETURN CODE    = ' WS-DISPLAY-SQLCODE        
                 DISPLAY ' ACCOUNT NO     = ' AT-ACCOUNT-NO             
                 DISPLAY '*******************************************'  
           END-IF.                                                      
      *                                                                         
       7020-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      *   7300-CHECK-ACCOUNT-EXIST                                     *08090000
      *        CHECKS IF ACCOUNT ALREADY EXISTS IN CSS_BATCH_EMAIL     *08100000
      ******************************************************************        
                                                                        
       7300-CHECK-ACCOUNT-EXIST.                                        
                                                                        
           MOVE '7300'                     TO WS-ACTIVE-PARAGRAPH       
           MOVE 'N' TO WS-CHECK-ACCOUNT-EXISTS.                         
                                                                        
           EXEC SQL                                                     
               SELECT TOP(1) 'Y'                                              
                  INTO :WS-CHECK-ACCOUNT-EXISTS                         
                  FROM CSS_BATCH_EMAIL AE WITH(READUNCOMMITTED)                 
                  WHERE AE.ACCOUNT_NO = :AT-ACCOUNT-NO                  
                    AND AE.EMAIL_EVENT_CD = :WS-EVENT-CD                
                    AND AE.COMPANY_NO = :AT-COMPANY-NO                  
                                           
                                                            
           END-EXEC                                                     

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ020
MFA-TR* MSQ022
MFA-TR*    EXEC SQL                                                     08180000
MFA-TR*        SELECT  'Y'                                                      
MFA-TR*           INTO :WS-CHECK-ACCOUNT-EXISTS                                 
MFA-TR*           FROM CSS_BATCH_EMAIL AE                                       
MFA-TR*           WHERE AE.ACCOUNT_NO = :AT-ACCOUNT-NO                          
MFA-TR*             AND AE.EMAIL_EVENT_CD = :WS-EVENT-CD                        
MFA-TR*             AND AE.COMPANY_NO = :AT-COMPANY-NO                          
MFA-TR*        WITH UR FETCH FIRST 1 ROW ONLY                                   
MFA-TR*        QUERYNO 7300                                                     
MFA-TR*    END-EXEC                                                     08310000

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 WS-ACTIVE-RETURN-CODE EQUAL NOT-FOUND)                
              NEXT SENTENCE                                             
           ELSE                                                         
              MOVE WS-ACTIVE-RETURN-CODE TO WS-DISPLAY-SQLCODE          
              DISPLAY '***********************************'             
              DISPLAY '**  PROCESSING ERROR             **'             
              DISPLAY '**  PARAGRAPH      = 7300-CHECK-ACCOUNT-EXIST'   
              DISPLAY '**  RETURN CODE    = ' WS-DISPLAY-SQLCODE        
              DISPLAY '**  PROCESSING TERMINATED        **'             
              DISPLAY '***********************************'             
           END-IF                                                       
           .                                                            
       7300-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      *   7400-CHECK-AUTO-PAY                                          *08090000
      *        CHECKS IF THERE EXISTS AN ACCOUNT WITH AUTO PAY         *08100000
      ******************************************************************        
       7400-CHECK-AUTO-PAY.                                             
                                                                        
           MOVE '7400'                     TO WS-ACTIVE-PARAGRAPH       
           MOVE 'N' TO WS-INSERT-ROW.                                   
                                                                        
A02525     EXEC SQL                                                     
A02525        SELECT TOP(1) 'Y'                                               
A02525           INTO :WS-INSERT-ROW                                    
A02525           FROM CSS_ACCOUNT AT WITH(READUNCOMMITTED)                      
A02525               ,CSS_ACCT_ATTRIBUTE YP WITH(READUNCOMMITTED)               
A02525           WHERE AT.ACCOUNT_NO = YP.ACCOUNT_NO                    
A02525             AND AT.CUSTOMER_NO = :AT-CUSTOMER-NO                 
A02525             AND YP.ACCOUNT_NO <> :AT-ACCOUNT-NO                  
A02525             AND YP.ATTRIBUTE_DESC = 'AUTO_CARD_PAY'              
A02525             AND YP.ATTRIBUTE_VALUE_CD = 'AUTOC'                  
A02525             AND YP.STATUS_CD = 'A'                               
A02525                                     
A02525                                                      
A02525     END-EXEC                                                     

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ020
MFA-TR* MSQ022
MFA-TR*    EXEC SQL                                                     08180000
MFA-TR*       SELECT  'Y'                                               08320000
MFA-TR*          INTO :WS-INSERT-ROW                                    08320000
MFA-TR*          FROM CSS_ACCOUNT AT                                    08320000
MFA-TR*              ,CSS_ACCT_ATTRIBUTE YP                             08320000
MFA-TR*          WHERE AT.ACCOUNT_NO = YP.ACCOUNT_NO                    08320000
MFA-TR*            AND AT.CUSTOMER_NO = :AT-CUSTOMER-NO                 08320000
MFA-TR*            AND YP.ACCOUNT_NO <> :AT-ACCOUNT-NO                  08320000
MFA-TR*            AND YP.ATTRIBUTE_DESC = 'AUTO_CARD_PAY'              08320000
MFA-TR*            AND YP.ATTRIBUTE_VALUE_CD = 'AUTOC'                  08320000
MFA-TR*            AND YP.STATUS_CD = 'A'                               08320000
MFA-TR*       WITH UR FETCH FIRST 1 ROW ONLY                            08320000
MFA-TR*       QUERYNO 7400                                              08320000
MFA-TR*    END-EXEC                                                     08310000

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 WS-ACTIVE-RETURN-CODE EQUAL NOT-FOUND)                
              NEXT SENTENCE                                             
           ELSE                                                         
              MOVE WS-ACTIVE-RETURN-CODE TO WS-DISPLAY-SQLCODE          
              DISPLAY '***********************************'             
              DISPLAY '**  PROCESSING ERROR             **'             
              DISPLAY '**  PARAGRAPH      = 7400-CHECK-AUTO-PAY '       
              DISPLAY '**  RETURN CODE    = ' WS-DISPLAY-SQLCODE        
              DISPLAY '**  PROCESSING TERMINATED        **'             
A02525        DISPLAY '**  ACCOUNT NO    = ' AT-ACCOUNT-NO              
A02525        DISPLAY '**  ORDER TYPE CD = ' VO-ORDER-TYPE-CD           
              DISPLAY '***********************************'             
              PERFORM 9900-ABEND           THRU 9900-EXIT               
           END-IF.                                                      
                                                                        
       7400-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      ** SELECTS THE RUN DATE                                         **        
      ******************************************************************        
       7500-GET-WORK-DATE.                                              
           MOVE '7500'                   TO WS-ACTIVE-PARAGRAPH         
                                                                        
           EXEC SQL                                                     
             SELECT PARM_DATA                                           
               INTO :G6-PARM-DATA                                       
               FROM CSS_JOB_PARM                                        
              WHERE PROGRAM_NAME = 'PCSSO164'                           
                AND COMPANY_NO   = '01'                                 
                AND CMND_CODE    = 'DATE'                               
                AND SEQ_NO       = 10                                   
                AND STATUS       = 'A'                                  
                AND CIS.SUBSTR3(PARM_DATA,1,9) = 'RUN DATE='                 
           END-EXEC                                                     

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ029
MFA-TR*    EXEC SQL                                                             
MFA-TR*      SELECT PARM_DATA                                                   
MFA-TR*        INTO :G6-PARM-DATA                                               
MFA-TR*        FROM CSS_JOB_PARM                                                
MFA-TR*       WHERE PROGRAM_NAME = 'PCSSO164'                                   
MFA-TR*         AND COMPANY_NO   = '01'                                         
MFA-TR*         AND CMND_CODE    = 'DATE'                                       
MFA-TR*         AND SEQ_NO       = 10                                           
MFA-TR*         AND STATUS       = 'A'                                          
MFA-TR*         AND SUBSTR(PARM_DATA,1,9) = 'RUN 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                   
              MOVE G6-PARM-DATA(10:10) TO WS-WORK-DATE                  
           ELSE                                                         
              MOVE WS-ACTIVE-RETURN-CODE TO WS-DISPLAY-SQLCODE          
              DISPLAY '***********************************'             
              DISPLAY '**  ERROR WHILE FETCHING RUN DATE     **'        
              DISPLAY '**  PLEASE ADD RUN DATE PARM          **'        
              DISPLAY '**  PARAGRAPH     = 7500-GET-WORK-DATE'          
              DISPLAY '**  RETURN CODE   = ' WS-DISPLAY-SQLCODE         
              DISPLAY '***********************************'             
              PERFORM 9900-ABEND      THRU 9900-EXIT                    
           END-IF                                                       
           .                                                            
       7500-EXIT.                                                       
           EXIT.                                                        
                                                                        
ACT070*****************************************************************         
ACT070* 7600-SET-NEXT-VALUE.                                          *         
ACT070*****************************************************************         
ACT070 7600-SET-NEXT-VALUE.                                             
      *                                                                         
ACT070     EXEC SQL                                                     
ACT070        SELECT
              NEXT VALUE FOR SEQ_AE_BATCH_EMAIL_ID
            INTO
              :AE-BATCH-EMAIL-ID
ACT070     END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ006
MFA-TR* MSQ048
MFA-TR*    EXEC SQL                                                             
MFA-TR*       SET :AE-BATCH-EMAIL-ID = NEXTVAL FOR SEQ_AE_BATCH_EMAIL_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

      *                                                                         
ACT070     MOVE SQLCODE                        TO WS-ACTIVE-RETURN-CODE.
                                                                        
ACT070     IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
ACT070        CONTINUE                                                  
ACT070     ELSE                                                         
ACT070        DISPLAY '***********************************'             
ACT070        DISPLAY '**  ERROR WHILE SETTING NEXT VALUE     **'       
ACT070        DISPLAY '**  PARAGRAPH     = 7600-SET-NEXT-VALUE'         
ACT070        DISPLAY '**  RETURN CODE   = ' WS-DISPLAY-SQLCODE         
ACT070        DISPLAY '***********************************'             
ACT070        PERFORM 9900-ABEND      THRU 9900-EXIT                    
ACT070     END-IF.                                                      
                                                                        
      *                                                                         
ACT070 7600-EXIT.                                                       
ACT070     EXIT.                                                        
                                                                        
      ******************************************************************        
      *   8500-INSERT-ACCOUNT.                                         *08090000
      *        INSERTS ROWS INTO   CSS_BATCH_EMAIL TABLE               *08100000
      ******************************************************************        
       8500-INSERT-ACCOUNT.                                             
                                                                        
           MOVE '8500'                     TO WS-ACTIVE-PARAGRAPH       
           MOVE 'N' TO WS-INSERT-ROW                                    
           ADD 1 TO WS-COMMIT-COUNT                                     
           MOVE -1 TO WS-NULL-IND-01                                    
                                                                        
           EXEC SQL                                                     
                INSERT INTO CSS_BATCH_EMAIL                             
                       (ACCOUNT_NO                                      
                       ,CUSTOMER_NO                                     
                       ,EMAIL_EVENT_CD                                  
                       ,EMAIL_EVENT_DT                                  
                       ,COMPANY_NO                                      
                       ,PROCESSED_CD                                    
                       ,PROCESSED_DT                                    
ACT070                 ,BATCH_EMAIL_ID                                  
                       )                                                
               VALUES (:AT-ACCOUNT-NO                                   
                      ,:AT-CUSTOMER-NO                                  
                      ,:WS-EVENT-CD                                     
                      ,IIF(TRY_CONVERT(DATE, :WS-EVENT-DT
              ) IS NULL OR (PATINDEX('%.%', :WS-EVENT-DT
              ) <> 0) OR (LEN(:WS-EVENT-DT) <> 10), CIS.CHAR2DATE(
                                                           :WS-EVENT-DT
              ), CONVERT(DATE, :WS-EVENT-DT) )                                 
                      ,:AT-COMPANY-NO                                   
                      ,:WS-PROCESSED-CD :WS-NULL-IND-01                  
                      ,IIF(TRY_CONVERT(DATE, :WS-PROCESSED-DATE 
                                             :WS-NULL-IND-01
              ) IS NULL OR (PATINDEX('%.%', :WS-PROCESSED-DATE 
                                                        :WS-NULL-IND-01
              ) <> 0) OR (LEN(:WS-PROCESSED-DATE :WS-NULL-IND-01
              ) <> 10), CIS.CHAR2DATE(:WS-PROCESSED-DATE 
                                                        :WS-NULL-IND-01
              ), CONVERT(DATE, :WS-PROCESSED-DATE :WS-NULL-IND-01) )           
ACT070                ,:AE-BATCH-EMAIL-ID                               
                      )                                                 
           END-EXEC                                                     

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                     08180000
MFA-TR*         INSERT INTO CSS_BATCH_EMAIL                             08190000
MFA-TR*                (ACCOUNT_NO                                      08200000
MFA-TR*                ,CUSTOMER_NO                                     08210000
MFA-TR*                ,EMAIL_EVENT_CD                                  08220000
MFA-TR*                ,EMAIL_EVENT_DT                                  08230000
MFA-TR*                ,COMPANY_NO                                              
MFA-TR*                ,PROCESSED_CD                                            
MFA-TR*                ,PROCESSED_DT                                            
MFA-TR*                ,BATCH_EMAIL_ID                                          
MFA-TR*                )                                                        
MFA-TR*        VALUES (:AT-ACCOUNT-NO                                           
MFA-TR*               ,:AT-CUSTOMER-NO                                          
MFA-TR*               ,:WS-EVENT-CD                                             
MFA-TR*               ,:WS-EVENT-DT                                             
MFA-TR*               ,:AT-COMPANY-NO                                           
MFA-TR*               ,:WS-PROCESSED-CD:WS-NULL-IND-01                          
MFA-TR*               ,:WS-PROCESSED-DATE:WS-NULL-IND-01                        
MFA-TR*               ,:AE-BATCH-EMAIL-ID                                       
MFA-TR*               )                                                         
MFA-TR*    END-EXEC                                                     08310000

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 WS-ACTIVE-RETURN-CODE TO WS-DISPLAY-SQLCODE          
              DISPLAY '***********************************'             
              DISPLAY '**  PROCESSING ERROR             **'             
              DISPLAY '**  PARAGRAPH      = 8500-INSERT-ROW     '       
              DISPLAY '**  RETURN CODE    = ' WS-DISPLAY-SQLCODE        
              DISPLAY '**  PROCESSING TERMINATED        **'             
              DISPLAY '***********************************'             
              PERFORM 9900-ABEND           THRU 9900-EXIT               
           END-IF                                                       
                                                                        
           IF WS-COMMIT-COUNT > 100                                     
               EXEC SQL                                                 
                   COMMIT                                               
               END-EXEC                                                 

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

               MOVE 0 TO WS-COMMIT-COUNT
           END-IF.                               
           .                                                            
       8500-EXIT.                                                       
           EXIT.                                                        
                                                                        
       8900-UPDATE-RUN-DATE.                                            
           MOVE '8900'                   TO WS-ACTIVE-PARAGRAPH         
           MOVE WS-LAST-RUN-DATA             TO G6-PARM-DATA.           
                                                                        
           EXEC SQL                                                     
             UPDATE CSS_JOB_PARM                                        
                SET PARM_DATA = :G6-PARM-DATA                           
              WHERE PROGRAM_NAME = 'PCSSO164'                           
                AND COMPANY_NO   = '01'                                 
                AND CMND_CODE    = 'DATE'                               
                AND SEQ_NO       = 10                                   
                AND STATUS       = 'A'                                  
           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      
              CONTINUE                                                  
           ELSE                                                         
              MOVE WS-ACTIVE-RETURN-CODE TO WS-DISPLAY-SQLCODE          
              DISPLAY '***********************************'             
              DISPLAY '**  ERROR WHILE UPDATING RUN DATE     **'        
              DISPLAY '**  PARAGRAPH     = 8900-UPDATE-RUN-DATE'        
              DISPLAY '**  RETURN CODE   = ' WS-DISPLAY-SQLCODE         
              DISPLAY '***********************************'             
           END-IF                                                       
           .                                                            
       8900-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ****************************************************************          
      **  9000-TERMINATE                                            **          
      **       PROGRAM WRAP UP                                      **          
      ****************************************************************          
       9000-TERMINATE.                                                  
      *                                                                         
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
             DISPLAY '*******************************************'      
             DISPLAY '           PCSSO164 ENDING '                      
             DISPLAY '*******************************************'      
           END-IF.                                                      
      *                                                                         
       9000-EXIT.                                                       
           EXIT.                                                        
      *                                                                         
      ****************************************************************          
      **  9700-PROCESS-ABEND.                                       **          
      ****************************************************************          
      *                                                                         
       COPY CPD0023B.                                                           
      *                                                                         
      ****************************************************************          
      **  9900-ABEND INCLUDES SQL ROLLBACK                          **          
      ****************************************************************          
      *                                                                         
           EXEC SQL                                                             
             INCLUDE CPD09900                                                   
           END-EXEC.                                                            
      *                                                                         
      ****************************************************************          
