       IDENTIFICATION DIVISION.                                         
       PROGRAM-ID.      CSR04429.                                       
       AUTHOR.       SAKTHI.                                            
COB303 DATE-WRITTEN. MAR 2011.                                           
       DATE-COMPILED.                                                   
      *----------------------------------------------------------------         
      *--                  SOUTH CAROLINA ELECTRIC & GAS             --         
      *----------------------------------------------------------------         
      *--                   STORED PROCEDURE:  CSR04429              --         
      *----------------------------------------------------------------         
      *--                         S U M M A R Y                      --         
      *--                                                            --         
      *--  CSR04429 :                                                --         
      *--                                                            --         
      *--  #  INVOKED FROM BPA RATE ENTRY SCREENS                    --         
      *--  #  UPDATE  CRM TABLE INFORMATION                          --         
      *--                 1. CRM_MKT_TIER                            --         
      *--                 2. CRM_RATE_GROUP                          --         
      *--                 3. CRM_RT_ATTR_INCTV                       --         
      *--                 4. CRM_MKT_TIER_ADJ                        --         
      *--                 5. CRM_TIER_ACCT_TYPE                      --         
      *--                                                            --         
      *--  # APPL-RETURN-CODE:                                       --         
      *--     5001 - OFFERS ARE ATTACHED TO THE TIER                 --         
      *--     5002 - OFFERS ARE ATTACHED TO THE RATE GROUP           --         
      *--     5003 - OPTIONS ARE ATTACHED TO THE ATTRIBUTE/INCENTIVE --         
      *--     5004 - MARKET TIER ARE ATTACHED TO THE TIER ACCT TYPE  --         
      *--     5007 - ROW ALREADY EXISTS                              --         
      *--     5008 - ROW ALREADY EXISTS FOR THIS DATE RANGE          --         
      *--     5009 - DEFAULT TIER ROW ALREADY EXIST                  --         
      *----------------------------------------------------------------         
      *--                                                            --         
      *--  PARAMETERS                                                --         
      *--                                                            --         
      *--      IN                                                    --         
      *--                                                            --         
      *--            I_APPL_PGM_ID     CHAR(03)                      --         
      *--            I_RESOURCE_ID     CHAR(32)                      --         
      *--            I_USER_ID         CHAR(07)                      --         
      *--            I_OPERATION_ID    CHAR(05)                      --         
      *--            I_COMPANY_NO      CHAR(02)                      --         
      *--            I_UPDATE_DATA     CHAR(2200)                    --         
      *--                                                            --         
      *--      OUT                                                   --         
      *--            NONE                                            --         
      *--                                                            --         
      *--      INOUT                                                 --         
      *--            NONE                                            --         
      *--                                                            --         
      *--  RESULT SET                                                --         
      *--                                                            --         
      *--            RETURN_CODE           CHAR(9)                   --         
      *--            APPL_RETURN_CODE      PIC S9(09)                --         
      *--                                                            --         
      *----------------------------------------------------------------         
      *--                        MODIFICATION LOG                    --         
      *--                                                            --         
      *--  DATE          INITIALS    COMMENTS                        --         
      *--  -----------   --------    ----------------------------------         
P00473*--  03/28/2011    MS93554     PROGRAM INTIALLY WRITTEN TO     --         
P00473*--                            UPDATE CRM ADMIN TABLE INFO.     --        
PRJ680*--  04/13/2013    MS93554     ADDED VERSIONING LOGIC IN TO     --        
PRJ680*--                            7002 & 7012 PARA.                --        
      *----------------------------------------------------------------         
                                                                        
       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 'CSR04429'.
MSQ017     COPY MFASQLM.
                                                                        
       01 WS-GET-PARM-VALUES.                                           
          05 WS-APPL-PGM-ID                   PIC X(08).                
          05 WS-RESOURCE-ID                   PIC X(32).                
          05 WS-OPERATION-ID                  PIC X(05).                
          05 WS-APPL-RETURN-CODE              PIC S9(09) COMP VALUE 0.  
                                                                        
       01 WS-PARM-UPDATE-DATA.                                          
          05 WS-UPDATE-LEN                    PIC S9(4) COMP.           
          05 WS-UPDATE-DATA-TEXT              PIC X(2200).              
                                                                        
       01 WS-827-INSERT-FLAGS.                                          
          05 WS-DFLT-TIER-ROW-EXIST           PIC X(01) VALUE 'N'.      
          05 WS-MKT-TIER-ROW-EXIST            PIC X(01) VALUE 'N'.      
          05 WS-RATE-GROUP-ROW-EXIST          PIC X(01) VALUE 'N'.      
          05 WS-ATTR-INCTV-ROW-EXIST          PIC X(01) VALUE 'N'.      
          05 WS-MKT-TIER-ADJ-ROW-EXIST        PIC X(01) VALUE 'N'.      
          05 WS-TIER-ACCT-TYPE-ROW-EXIST      PIC X(01) VALUE 'N'.      
                                                                        
       01 WS-827-DELETE-FLAGS.                                          
          05 WS-MKT-TIER-OFF-ROW-EXIST        PIC X(01) VALUE 'N'.      
          05 WS-RATE-GROUP-OFF-ROW-EXIST      PIC X(01) VALUE 'N'.      
          05 WS-ATTR-INCTV-OPT-ROW-EXIST      PIC X(01) VALUE 'N'.      
          05 WS-ACCT-TYPE-TIER-ROW-EXIST      PIC X(01) VALUE 'N'.      
                                                                        
       01 WS-827-UPDATE-FLAGS.                                          
          05 WS-ACTV-OFF-ROW-EXIST-FOR-TIER   PIC X(01) VALUE 'N'.      
          05 WS-UPD-MKT-END-DT                PIC X(01) VALUE 'N'.      
          05 WS-UPD-RT-GRP-END-DT             PIC X(01) VALUE 'N'.      
                                                                        
       01 WS-827-MISC.                                                  
          05 WS-RT-GRP-END-DT                 PIC X(10).                
          05 WS-YES-NULL                      PIC S9(04) VALUE -1.      
          05 WS-MAX-ADJ-TIER-SEQ-NO-NULL      PIC S9(04) COMP.          
          05 WS-MIN-GOOD-PAY-QTY-NULL         PIC S9(04) COMP.          
          05 WS-MISSED-PYMT-QTY-NULL          PIC S9(04) COMP.          
          05 WS-EVAL-MONTHS-QTY-NULL          PIC S9(04) COMP.          
          05 WS-SMALLINT-NUM                  PIC 9(04).                
          05 WS-INTEGER-NUM                   PIC 9(09).                
                                                                        
       01 WS-SWITCHES.                                                  
          05 ALL-DONE-SW                      PIC X(01) VALUE 'N'.      
             88 NOT-ALL-DONE                            VALUE 'N'.      
             88 ALL-DONE                                VALUE 'Y'.      
          05 SEND-DONE-SW                     PIC X(01) VALUE 'Y'.      
             88 SEND-DONE-ERROR                         VALUE 'N'.      
             88 SEND-DONE-OK                            VALUE 'Y'.      
                                                                        
       01  WS-LITERALS.                                                 
           05 PROGRAM-NAME            PIC X(08) VALUE 'CSR04429'.       
           05 MCSMS827                PIC X(08) VALUE 'MCSMS827'.       
           05 WS-N                    PIC X(01) VALUE 'N'.              
           05 GTT-NAME                PIC X(26)                         
                                            VALUE 'SESSION.CSR04429_R1'.
                                                                        
       01  GTT-MISC-FIELDS.                                             
            05  GTT-ROW.                                                
                49 GTT-ROW-LEN          PIC S9(04) COMP.                
                49 GTT-ROW-CHAR         PIC X(1024).                    
            05  GTT-SQLCODE             PIC S9(9) COMP.                 
                                                                        
       01  COUNTER-FIELDS.                                              
           05  CTR-COLUMN              PIC S9(9) COMP VALUE 1.          
           05  CTR-ROWS                PIC S9(9) COMP VALUE 0.          
                                                                        
       01  WORK-FIELDS.                                                 
           05  MAX-LENGTH-PARM         PIC S9(9) COMP.                  
           05  WRKLEN1                 PIC S9(9) COMP.                  
           05  WRKLEN2                 PIC S9(9) COMP.                  
           05  WRK-DONE-STATUS         PIC S9(9) COMP.                  
                                                                        
       01  TDS-RETURN-FIELDS.                                           
           05 RS-RETURN-CODE           PIC S9(09) COMP VALUE 0.         
                                                                        
       01  GTT-RETURN-FIELDS.                                           
           05 S-RETURN-CODE            PIC S9(09) COMP VALUE 0.         
       01  GTT-APPL-RETURN-FIELDS.                                      
           05 S-APPL-RETURN-CODE       PIC S9(09) COMP VALUE 0.         
                                                                        
       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).                     
                                                                        
      ******************************************************************        
      * DB2 INCLUDES                                                   *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE SQLCA                                                     
           END-EXEC.                                                            
      ******************************************************************        
      * CRM_RT_PKG_OFFER   - FG                                        *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBRTPOFF                                                  
           END-EXEC.                                                            
      ******************************************************************        
      * CRM_MKT_TIER       - IG                                        *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE TBMKTTER                                                  
           END-EXEC.                                                            
      *****************************************************************         
      * CRM_RATE_GROUP     - F7                                       *         
      *****************************************************************         
           EXEC SQL                                                             
                INCLUDE TBRATGRP                                                
           END-EXEC.                                                            
      *****************************************************************         
      * CRM_RATE_GRP_OFFER - G5                                       *         
      *****************************************************************         
           EXEC SQL                                                             
                INCLUDE TBRTEGRP                                                
           END-EXEC.                                                            
      *****************************************************************         
      * CRM_RT_ATTR_INCTV  - G7                                       *         
      *****************************************************************         
           EXEC SQL                                                             
                INCLUDE TBRTINCT                                                
           END-EXEC.                                                            
      *****************************************************************         
      * CRM_MKT_TIER_ADJ   - 1B                                       *         
      *****************************************************************         
           EXEC SQL                                                             
                INCLUDE TBMKTTAD                                                
           END-EXEC.                                                            
      *****************************************************************         
      * CRM_TIER_ACCT_TYPE - TT                                       *         
      *****************************************************************         
           EXEC SQL                                                             
                INCLUDE TBTIACTP                                                
           END-EXEC.                                                            
      *****************************************************************         
      * CRM_OFF_MKT_TIER   - DU                                       *         
      *****************************************************************         
           EXEC SQL                                                             
                INCLUDE TBOFFMKT                                                
           END-EXEC.                                                            
      *****************************************************************         
      * CRM_OPT_ATTR_INCTV - HQ                                       *         
      *****************************************************************         
           EXEC SQL                                                             
                INCLUDE TBOPTATT                                                
           END-EXEC.                                                            
      ******************************************************************        
      *    SUPPORTS DB2 AND SQL ERROR CHECKING                         *        
      ******************************************************************        
           COPY CWS00303.                                                       
      ******************************************************************        
      *    WORK AREAS TO XFER DATA.                                    *        
      ******************************************************************        
           COPY CCA00001.                                                       
      ******************************************************************        
      *    WORKING STORAGE SUPPORT FOR 827.                            *        
      ******************************************************************        
           COPY CWS00827.                                                       
      ******************************************************************        
      *    ERROR HANDLING COPY BOOK.                                   *        
      ******************************************************************        
           EXEC SQL                                                             
                INCLUDE CWSX0010                                                
           END-EXEC.                                                            
      ******************************************************************        
                                                                        
       LINKAGE SECTION.                                                 
       01  PARM-APPL-PGM-ID        PIC X(03).                           
       01  PARM-RESOURCE-ID        PIC X(32).                           
       01  PARM-USER-ID            PIC X(07).                           
       01  PARM-OPERATION-ID       PIC X(05).                           
       01  PARM-COMPANY-NO         PIC X(02).                           
       01  PARM-UPDATE-DATA        PIC X(2200).                         
                                                                        
       PROCEDURE DIVISION  USING PARM-APPL-PGM-ID                       
                                 PARM-RESOURCE-ID                       
                                 PARM-USER-ID                           
                                 PARM-OPERATION-ID                      
                                 PARM-COMPANY-NO                        
                                 PARM-UPDATE-DATA.                      
                                                                        
      ******************************************************************        
      * 0000-MAINLINE.                                                 *        
      ******************************************************************        
       0000-MAINLINE.                                                   
                                                                        
           PERFORM 0100-INITIALIZE             THRU 0100-EXIT.          
           PERFORM 1000-PROCESS-INPUT          THRU 1000-EXIT.          
           PERFORM 2000-PROCESS-OUTPUT         THRU 2000-EXIT.          
           PERFORM 9999-END-PROGRAM            THRU 9999-EXIT.          
                                                                        
       0000-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 0100-INITIALIZE                                                *        
      ******************************************************************        
       0100-INITIALIZE.                                                 
                                                                        
           EXEC SQL WHENEVER SQLWARNING CONTINUE END-EXEC.              
           EXEC SQL WHENEVER SQLERROR   CONTINUE END-EXEC.              
           EXEC SQL WHENEVER NOT FOUND  CONTINUE END-EXEC.              
                                                                        
           EXEC SQL                                                     
              DECLARE C1 CURSOR  FOR                         
                 SELECT :S-RETURN-CODE       AS RETURN_CODE             
                       ,:S-APPL-RETURN-CODE  AS APPL_RETURN_CODE        
                   FROM CIS.SYSDUMMY1                                
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ027
MFA-TR*    EXEC SQL                                                             
MFA-TR*       DECLARE C1 CURSOR WITH RETURN FOR                                 
MFA-TR*          SELECT :S-RETURN-CODE       AS RETURN_CODE                     
MFA-TR*                ,:S-APPL-RETURN-CODE  AS APPL_RETURN_CODE                
MFA-TR*            FROM SYSIBM.SYSDUMMY1                                        
MFA-TR*    END-EXEC.                                                            
                                                                        
       0100-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      *                                                                *        
      * 1000-PROCESS-INPUT                                             *        
      ******************************************************************        
       1000-PROCESS-INPUT.                                              
                                                                        
           MOVE PARM-APPL-PGM-ID          TO WS-APPL-PGM-ID             
                                             WS-CRM-APPL-PGM-ID         
           MOVE PARM-RESOURCE-ID          TO WS-RESOURCE-ID             
                                             WS-CRM-RESOURCE-ID         
           MOVE PARM-OPERATION-ID         TO WS-OPERATION-ID            
                                             WS-CRM-OPERATION-ID        
                                                                        
           MOVE PARM-USER-ID              TO WS-CRM-USER-ID             
           MOVE PARM-COMPANY-NO           TO WS-CRM-COMPANY-NO          
           MOVE PARM-UPDATE-DATA          TO WS-PARM-UPDATE-DATA        
                                                                        
      ***************************************************************           
      *   MOVE UPDATE DATA'S TO LINKAGE SECTION BASED ON THE        *           
      *   OPERATION ID (WS-CRM-OPERATION-ID).                       *           
      ***************************************************************           
                                                                        
           EVALUATE TRUE                                                
           WHEN CRM-TIER-CHANGE                                         
               MOVE WS-UPDATE-DATA-TEXT  TO WS-CRM-TIER-INFO-IG         
                                                                        
           WHEN CRM-RT-GROUP-CHANGE                                     
               MOVE WS-UPDATE-DATA-TEXT  TO WS-CRM-RATE-GROUP-INFO-F7   
                                                                        
           WHEN CRM-RT-ATTR-INCTV-CHANGE                                
               MOVE WS-UPDATE-DATA-TEXT  TO WS-CRM-RT-ATTR-INCTV-INFO-G7
                                                                        
           WHEN CRM-TIER-ADJ-CHANGE                                     
               MOVE WS-UPDATE-DATA-TEXT  TO WS-CRM-TIER-ADJ-INFO-1B     
                                                                        
           WHEN CRM-TIER-ACCT-TYPE-CHANGE                               
               MOVE WS-UPDATE-DATA-TEXT  TO                             
                                           WS-CRM-TIER-ACCT-TYPE-INFO-TT
           END-EVALUATE.                                                
                                                                        
       1000-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 2000-PROCESS-OUTPUT.                                           *        
      ******************************************************************        
       2000-PROCESS-OUTPUT.                                             
                                                                        
           PERFORM 6000-UPDATE-CRM-ADMIN-ENTRIES THRU 6000-EXIT.        
           PERFORM 2000A-MOVE-RESULT             THRU 2000A-EXIT.       
           PERFORM 8000-SEND-RESULT              THRU 8000-EXIT.        
                                                                        
       2000-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 2000A-MOVE-RESULT.                                             *        
      ******************************************************************        
       2000A-MOVE-RESULT.                                               
                                                                        
            MOVE RS-RETURN-CODE             TO S-RETURN-CODE.           
            MOVE WS-APPL-RETURN-CODE        TO S-APPL-RETURN-CODE.      
                                                                        
       2000A-EXIT.                                                      
             EXIT.                                                      
                                                                        
      *****************************************************************         
      * 6000-UPDATE-CRM-ADMIN-ENTRIES.                                *         
      *****************************************************************         
       6000-UPDATE-CRM-ADMIN-ENTRIES.                                   
                                                                        
      ************************************************************              
      *    INSERT / UPDATE / DELETE FOR MARKET TIER TABLE - IG   *              
      ************************************************************              
           IF CRM-TIER-CHANGE THEN                                      
                                                                        
              PERFORM 6510-MOVE-MKT-TIER             THRU 6510-EXIT     
              PERFORM 6600-VLDT-OFF-MKT-TIER         THRU 6600-EXIT     
                                                                        
              EVALUATE TRUE                                             
              WHEN CRM-INSERT-MKT-TIER                                  
                   PERFORM 8030-INS-MKT-TIER         THRU 8030-EXIT     
              WHEN CRM-UPDATE-MKT-TIER                                  
                   PERFORM 8040-UPT-MKT-TIER         THRU 8040-EXIT     
              WHEN CRM-DELETE-MKT-TIER                                  
                   PERFORM 8045-DEL-MKT-TIER         THRU 8045-EXIT     
              END-EVALUATE                                              
                                                                        
           END-IF.                                                      
                                                                        
      ************************************************************              
      * INSERT / UPDATE / DELETE FOR RATE GROUP TABLE TABLE - F7 *              
      ************************************************************              
           IF CRM-RT-GROUP-CHANGE  THEN                                 
                                                                        
              PERFORM 6520-MOVE-RATE-GROUP           THRU 6520-EXIT     
              PERFORM 6610-VLDT-RATE-GRP-OFF         THRU 6610-EXIT     
                                                                        
              EVALUATE TRUE                                             
              WHEN CRM-INSERT-RATE-GROUP                                
                   PERFORM 8050-INS-RATE-GROUP       THRU 8050-EXIT     
              WHEN CRM-UPDATE-RATE-GROUP                                
                   PERFORM 8060-UPT-RATE-GROUP       THRU 8060-EXIT     
              WHEN CRM-DELETE-RATE-GROUP                                
                   PERFORM 8065-DEL-RATE-GROUP       THRU 8065-EXIT     
              END-EVALUATE                                              
                                                                        
           END-IF.                                                      
                                                                        
      ************************************************************              
      * INSERT / UPDATE / DELETE FOR ATTR INCTV TABLE - G7       *              
      ************************************************************              
           IF CRM-RT-ATTR-INCTV-CHANGE THEN                             
                                                                        
              PERFORM 6540-MOVE-ATTR-INCTV           THRU 6540-EXIT     
              PERFORM 6620-VLDT-ATTR-INCTV           THRU 6620-EXIT     
                                                                        
              EVALUATE TRUE                                             
              WHEN CRM-INSERT-RT-ATTR-INCTV                             
                   PERFORM 8100-INS-ATTR-INCTV       THRU 8100-EXIT     
              WHEN CRM-UPDATE-RT-ATTR-INCTV                             
                  PERFORM 8110-UPT-ATTR-INCTV        THRU 8110-EXIT     
              WHEN CRM-DELETE-RT-ATTR-INCTV                             
                   PERFORM 8120-DEL-ATTR-INCTV       THRU 8120-EXIT     
              END-EVALUATE                                              
                                                                        
           END-IF.                                                      
                                                                        
      ***********************************************************               
      * INSERT / UPDATE / DELETE FOR MKT TIER ADJ TABLE - 1B    *               
      ***********************************************************               
           IF CRM-TIER-ADJ-CHANGE THEN                                  
                                                                        
              PERFORM 6550-MOVE-MKT-TIER-ADJ         THRU 6550-EXIT     
                                                                        
              EVALUATE TRUE                                             
              WHEN CRM-INSERT-TIER-ADJ                                  
                   PERFORM 8130-INS-MKT-TIER-ADJ     THRU 8130-EXIT     
              WHEN CRM-UPDATE-TIER-ADJ                                  
                   PERFORM 8140-UPT-MKT-TIER-ADJ     THRU 8140-EXIT     
              WHEN CRM-DELETE-TIER-ADJ                                  
                   PERFORM 8145-DEL-MKT-TIER-ADJ     THRU 8145-EXIT     
              END-EVALUATE                                              
                                                                        
           END-IF.                                                      
                                                                        
      **************************************************************            
      *  INSERT / UPDATE /DELETE FOR TIER ACCOUNT TYPE TABLE - TT  *            
      **************************************************************            
           IF CRM-TIER-ACCT-TYPE-CHANGE THEN                            
                                                                        
              PERFORM 6560-MOVE-TIER-ACCT-TYPE       THRU 6560-EXIT     
              PERFORM 6640-VLDT-TIER-ACCT-TYPE       THRU 6640-EXIT     
                                                                        
              EVALUATE TRUE                                             
              WHEN CRM-INSERT-TIER-ACCT-TYPE                            
                   PERFORM 8150-INS-TIER-ACCT-TYPE   THRU 8150-EXIT     
              WHEN CRM-UPDATE-TIER-ACCT-TYPE                            
                   PERFORM 8160-UPT-TIER-ACCT-TYPE   THRU 8160-EXIT     
              WHEN CRM-DELETE-TIER-ACCT-TYPE                            
                   PERFORM 8165-DEL-TIER-ACCT-TYPE   THRU 8165-EXIT     
              END-EVALUATE                                              
                                                                        
           END-IF.                                                      
                                                                        
       6000-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * 6510-MOVE-MKT-TIER.                                           *         
      *****************************************************************         
       6510-MOVE-MKT-TIER.                                              
                                                                        
           MOVE WS-CRM-COMPANY-NO            TO IG-COMPANY-NO           
           MOVE WS-CRM-TIER-CUST-STAT-CD-IG  TO IG-TIER-CUST-STAT-CD    
           MOVE WS-CRM-TIER-ACCT-TYPE-CD-IG  TO IG-TIER-ACCT-TYPE-CD    
           MOVE WS-CRM-MKT-TIER-RANKING-IG   TO WS-SMALLINT-NUM         
           MOVE WS-SMALLINT-NUM              TO IG-MKT-TIER-RANKING     
           MOVE WS-CRM-MKT-TIER-START-DT-IG  TO IG-MKT-TIER-START-DT    
           MOVE WS-CRM-MKT-TIER-DESC-IG      TO IG-MKT-TIER-DESC        
           MOVE WS-CRM-MKT-TIER-END-DT-IG    TO IG-MKT-TIER-END-DT      
           MOVE WS-CRM-TIER-OFFER-VIEW-IG    TO WS-SMALLINT-NUM         
           MOVE WS-SMALLINT-NUM              TO IG-TIER-OFFER-VIEW      
           MOVE WS-CRM-CUST-ACCT-DEFLT-FL-IG                            
                                             TO IG-CUST-ACCT-DEFLT-FL   
           MOVE WS-CRM-TIER-DESC-LONG-LEN-IG TO                         
                                              WS-SMALLINT-NUM           
           MOVE WS-SMALLINT-NUM              TO                         
                                              IG-MKT-TIER-DESC-LONG-LEN 
           MOVE WS-CRM-TIER-DESC-LONG-TEXT-IG TO                        
                                              IG-MKT-TIER-DESC-LONG-TEXT
           MOVE WS-CRM-USER-ID               TO IG-LAST-UPDATE-USERID   
           MOVE WS-CRM-LAST-UPDATE-TS-IG     TO IG-LAST-UPDATE-TS.      
                                                                        
      *    MOVING THE VARIABLES FOR VALIDATION.                                 
           MOVE IG-COMPANY-NO                TO DU-COMPANY-NO.          
           MOVE IG-TIER-CUST-STAT-CD         TO DU-TIER-CUST-STAT-CD.   
           MOVE IG-TIER-ACCT-TYPE-CD         TO DU-TIER-ACCT-TYPE-CD.   
           MOVE IG-MKT-TIER-RANKING          TO DU-MKT-TIER-RANKING.    
           MOVE IG-MKT-TIER-START-DT         TO DU-MKT-TIER-START-DT.   
           MOVE IG-MKT-TIER-END-DT           TO DU-OFFER-TIER-END-DT.   
                                                                        
                                                                        
       6510-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * 6520-MOVE-RATE-GROUP.                                         *         
      *****************************************************************         
       6520-MOVE-RATE-GROUP.                                            
                                                                        
           MOVE WS-CRM-COMPANY-NO            TO F7-COMPANY-NO           
           MOVE WS-CRM-RATE-GROUP-TYPE-CD-F7 TO F7-RATE-GROUP-TYPE-CD   
           MOVE WS-CRM-RATE-GROUP-CD-F7      TO F7-RATE-GROUP-CD        
           MOVE WS-CRM-RT-GRP-SORT-SEQ-NO-F7 TO WS-SMALLINT-NUM         
           MOVE WS-SMALLINT-NUM              TO F7-RT-GRP-SORT-SEQ-NO   
           MOVE WS-CRM-RATE-GRP-DESC-F7      TO F7-RATE-GRP-DESC        
           MOVE WS-CRM-RATE-GRP-DESC-LONG-F7 TO F7-RATE-GRP-DESC-LONG   
           MOVE WS-CRM-USER-ID               TO F7-LAST-UPDATE-USERID   
           MOVE WS-CRM-LAST-UPDATE-TS-F7     TO F7-LAST-UPDATE-TS.      
           MOVE WS-CRM-RT-GRP-START-DT-F7    TO F7-RT-GRP-START-DT.     
           MOVE WS-CRM-RT-GRP-END-DT-F7      TO F7-RT-GRP-END-DT.       
                                                                        
      *    MOVING THE VARIABLES FOR VALIDATION.                                 
           MOVE F7-COMPANY-NO                TO G5-COMPANY-NO.          
           MOVE F7-RATE-GROUP-TYPE-CD        TO G5-RATE-GROUP-TYPE-CD.  
           MOVE F7-RATE-GROUP-CD             TO G5-RATE-GROUP-CD.       
           MOVE F7-RT-GRP-END-DT             TO G5-EFF-END-DT.          
                                                                        
                                                                        
       6520-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * 6540-MOVE-ATTR-INCTV.                                         *         
      *****************************************************************         
       6540-MOVE-ATTR-INCTV.                                            
                                                                        
           MOVE WS-CRM-COMPANY-NO            TO G7-COMPANY-NO           
           MOVE WS-CRM-RT-ATTR-INCTV-TYPE-G7 TO G7-RT-ATTR-INCTV-TYPE   
           MOVE WS-CRM-RT-ATTR-INCTV-DESC-G7 TO G7-RT-ATTR-INCTV-DESC   
           MOVE WS-CRM-SOURCE-CD-G7          TO G7-SOURCE-CD            
           MOVE WS-CRM-DURATION-START-CD-G7  TO G7-DURATION-START-CD    
           MOVE WS-CRM-ATTR-INCTV-DISP-CD-G7 TO G7-ATTR-INCTV-DISP-CD   
           MOVE WS-CRM-ATTR-INCTV-GEN-CD-G7  TO G7-ATTR-INCTV-GEN-CD    
           MOVE WS-CRM-STATUS-CD-G7          TO G7-STATUS-CD            
           MOVE WS-CRM-USER-ID               TO G7-LAST-UPDATE-USERID   
           MOVE WS-CRM-LAST-UPDATE-TS-G7     TO G7-LAST-UPDATE-TS.      
                                                                        
      *    MOVING THE VARIABLES FOR VALIDATION.                                 
           MOVE G7-COMPANY-NO                TO HQ-COMPANY-NO.          
           MOVE G7-RT-ATTR-INCTV-TYPE        TO HQ-RT-ATTR-INCTV-TYPE.  
                                                                        
       6540-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * 6550-MOVE-MKT-TIER-ADJ.                                       *         
      *****************************************************************         
       6550-MOVE-MKT-TIER-ADJ.                                          
                                                                        
           MOVE WS-CRM-COMPANY-NO              TO 1B-COMPANY-NO         
           MOVE WS-CRM-TIER-CUST-STAT-CD-1B    TO 1B-TIER-CUST-STAT-CD  
           MOVE WS-CRM-TIER-ACCT-TYPE-CD-1B    TO 1B-TIER-ACCT-TYPE-CD  
           MOVE WS-CRM-MKT-TIER-RANKING-1B     TO WS-SMALLINT-NUM       
           MOVE WS-SMALLINT-NUM                TO 1B-MKT-TIER-RANKING   
           MOVE WS-CRM-MKT-TIER-START-DT-1B    TO 1B-MKT-TIER-START-DT  
                                                                        
           IF CRM-INSERT-TIER-ADJ                                       
              PERFORM 7100-GET-MAX-ADJ-TIER-SEQ-NO THRU 7100-EXIT       
              ADD +1                           TO 1B-ADJ-TIER-SEQ-NO    
           ELSE                                                         
              MOVE WS-CRM-ADJ-TIER-SEQ-NO-1B   TO WS-SMALLINT-NUM       
              MOVE WS-SMALLINT-NUM             TO 1B-ADJ-TIER-SEQ-NO    
           END-IF.                                                      
                                                                        
           MOVE WS-CRM-AGL-LOW-INCM-SR-FL-1B   TO 1B-AGL-LOW-INCM-SR-FL 
                                                                        
           IF WS-CRM-MIN-GOOD-PAY-QTY-1B = SPACES                       
              MOVE WS-YES-NULL  TO WS-MIN-GOOD-PAY-QTY-NULL             
           ELSE                                                         
              MOVE WS-CRM-MIN-GOOD-PAY-QTY-1B  TO WS-SMALLINT-NUM       
              MOVE WS-SMALLINT-NUM             TO 1B-MIN-GOOD-PAY-QTY   
           END-IF.                                                      
                                                                        
           IF WS-CRM-MISSED-PYMT-QTY-1B = SPACES                        
              MOVE WS-YES-NULL  TO WS-MISSED-PYMT-QTY-NULL              
           ELSE                                                         
              MOVE WS-CRM-MISSED-PYMT-QTY-1B   TO WS-SMALLINT-NUM       
              MOVE WS-SMALLINT-NUM             TO 1B-MISSED-PYMT-QTY    
           END-IF.                                                      
                                                                        
           IF WS-CRM-EVAL-MONTHS-QTY-1B = SPACES                        
              MOVE WS-YES-NULL  TO WS-EVAL-MONTHS-QTY-NULL              
           ELSE                                                         
              MOVE WS-CRM-EVAL-MONTHS-QTY-1B   TO WS-SMALLINT-NUM       
              MOVE WS-SMALLINT-NUM             TO 1B-EVAL-MONTHS-QTY    
           END-IF.                                                      
                                                                        
           MOVE WS-CRM-APPLICATION-CD-1B       TO 1B-APPLICATION-CD     
           MOVE WS-CRM-ADJ-TIER-ACCT-TYPE-1B   TO 1B-ADJ-TIER-ACCT-TYPE 
           MOVE WS-CRM-ADJ-TIER-RANKING-1B     TO WS-SMALLINT-NUM       
           MOVE WS-SMALLINT-NUM                TO 1B-ADJ-TIER-RANKING   
           MOVE WS-CRM-ADJ-TIER-START-DT-1B    TO 1B-ADJ-TIER-START-DT  
           MOVE WS-CRM-ADJ-START-DT-1B         TO 1B-ADJ-START-DT       
           MOVE WS-CRM-ADJ-END-DT-1B           TO 1B-ADJ-END-DT         
           MOVE WS-CRM-ADJ-SOURCE-CD-1B        TO 1B-ADJ-SOURCE-CD      
           MOVE WS-CRM-LAST-UPDATE-TS-1B       TO 1B-LAST-UPDATE-TS     
           MOVE WS-CRM-USER-ID                 TO 1B-LAST-UPDATE-USERID 
           MOVE WS-CRM-DHR-QUALIFIED-FL-1B     TO 1B-DHR-QUALIFIED-FL   
           MOVE WS-CRM-TIER-ADJ-DESC-1B        TO 1B-TIER-ADJ-DESC.     
                                                                        
       6550-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * 6560-MOVE-TIER-ACCT-TYPE.                                     *         
      *****************************************************************         
       6560-MOVE-TIER-ACCT-TYPE.                                        
                                                                        
           MOVE WS-CRM-COMPANY-NO            TO TT-COMPANY-NO           
           MOVE WS-CRM-TIER-ACCT-TYPE-CD-TT  TO TT-TIER-ACCT-TYPE-CD    
           MOVE WS-CRM-ACCOUNT-TYPE-CODE-TT  TO TT-ACCOUNT-TYPE-CODE    
           MOVE WS-CRM-TIER-ACCT-DESC-TT     TO TT-TIER-ACCT-DESC       
           MOVE WS-CRM-TIER-ACCT-DESC-LNG-TT TO TT-TIER-ACCT-DESC-LNG   
           MOVE WS-CRM-LAST-UPDATE-TS-TT     TO TT-LAST-UPDATE-TS       
           MOVE WS-CRM-USER-ID               TO TT-LAST-UPDATE-USERID   
           MOVE WS-CRM-WEB-RENEWAL-FL-TT     TO TT-WEB-RENEWAL-FL       
           MOVE WS-CRM-REV-COMPANY-NO-TT     TO TT-REV-COMPANY-NO.      
                                                                        
      * MOVING THE VARIABLES FOR VALIDATION.                                    
           MOVE TT-COMPANY-NO                TO IG-COMPANY-NO.          
           MOVE TT-TIER-ACCT-TYPE-CD         TO IG-TIER-ACCT-TYPE-CD.   
                                                                        
       6560-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      *        VALIDATION FOR OFFER CRM_MKT_TIER                      *         
      *****************************************************************         
       6600-VLDT-OFF-MKT-TIER.                                          
                                                                        
           EVALUATE TRUE                                                
           WHEN CRM-INSERT-MKT-TIER                                     
                PERFORM 7000-CHK-TIER-ROW-EXIST       THRU 7000-EXIT    
                IF WS-MKT-TIER-ROW-EXIST = 'Y'                          
                   MOVE 5008    TO WS-APPL-RETURN-CODE                  
                   PERFORM 9910-SQL-ERROR             THRU 9910-EXIT    
                END-IF                                                  
                IF WS-CRM-CUST-ACCT-DEFLT-FL-IG = 'Y'                   
                   PERFORM 7001-CHK-DFLT-TR-EXST-4INS  THRU 7001-EXIT   
                   IF WS-DFLT-TIER-ROW-EXIST = 'Y'                      
                      MOVE 5009    TO WS-APPL-RETURN-CODE               
                      PERFORM 9910-SQL-ERROR             THRU 9910-EXIT 
                   END-IF                                               
                END-IF                                                  
                                                                        
           WHEN CRM-UPDATE-MKT-TIER                                     
                PERFORM 7004-GET-MKT-TIER-END-DT      THRU 7004-EXIT    
                IF WS-UPD-MKT-END-DT = 'Y'                              
                  PERFORM 7002-CHK-ACT-OFF-ROW-EXIST  THRU 7002-EXIT    
                  IF WS-ACTV-OFF-ROW-EXIST-FOR-TIER = 'Y'               
                     MOVE 5001    TO WS-APPL-RETURN-CODE                
                     PERFORM 9910-SQL-ERROR           THRU 9910-EXIT    
                  END-IF                                                
                END-IF                                                  
                                                                        
                IF WS-CRM-CUST-ACCT-DEFLT-FL-IG = 'Y'                   
                   PERFORM 7003-CHK-DFLT-TR-EXST-4UPD THRU 7003-EXIT    
                   IF WS-DFLT-TIER-ROW-EXIST = 'Y'                      
                      MOVE 5009    TO WS-APPL-RETURN-CODE               
                      PERFORM 9910-SQL-ERROR          THRU 9910-EXIT    
                   END-IF                                               
                END-IF                                                  
                                                                        
           WHEN CRM-DELETE-MKT-TIER                                     
                PERFORM 7005-OFF-ROW-EXIST-FOR-TIER THRU 7005-EXIT      
                IF WS-ACTV-OFF-ROW-EXIST-FOR-TIER = 'Y'                 
                   MOVE 5001    TO WS-APPL-RETURN-CODE                  
                   PERFORM 9910-SQL-ERROR             THRU 9910-EXIT    
                END-IF                                                  
           END-EVALUATE.                                                
                                                                        
       6600-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      *         VALIDATION FOR CRM_RATE_GROUP                         *         
      *****************************************************************         
       6610-VLDT-RATE-GRP-OFF.                                          
                                                                        
           EVALUATE TRUE                                                
           WHEN CRM-INSERT-RATE-GROUP                                   
                PERFORM 7010-CHK-RATE-GRP-ROW-EXIST   THRU 7010-EXIT    
      ****************************************************************          
      * IF ROW EXIST IN THE CRM_RATE_GROUP TABLE ABEDING THE PROGRAM *          
      * TO AVOID -803 WHILE INSERT.                                  *          
      * NOTE: NEWLY ADDED RT_GRP_START_DT IS NOT PART OF PRIMARY KEY.*          
      ****************************************************************          
                IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL              
                   IF WS-RT-GRP-END-DT  >= F7-RT-GRP-START-DT           
                      MOVE 5008  TO WS-APPL-RETURN-CODE                 
                   ELSE                                                 
                      MOVE 5007  TO WS-APPL-RETURN-CODE                 
                   END-IF                                               
                   PERFORM 9910-SQL-ERROR             THRU 9910-EXIT    
                END-IF                                                  
                                                                        
           WHEN CRM-UPDATE-RATE-GROUP                                   
                PERFORM 7011-GET-RT-GROUP-END-DT      THRU 7011-EXIT    
                IF WS-UPD-RT-GRP-END-DT = 'Y'                           
                   PERFORM 7012-CHK-ACT-OFF-ROW-EXIST THRU 7012-EXIT    
                   IF WS-RATE-GROUP-OFF-ROW-EXIST  = 'Y'                
                      MOVE 5002     TO WS-APPL-RETURN-CODE              
                      PERFORM 9910-SQL-ERROR          THRU 9910-EXIT    
                   END-IF                                               
                END-IF                                                  
                                                                        
           WHEN CRM-DELETE-RATE-GROUP                                   
                PERFORM 7015-CHK-OFF-EXIST-FOR-RT-GRP THRU 7015-EXIT    
                IF WS-RATE-GROUP-OFF-ROW-EXIST  =  'Y'                  
                   MOVE 5002     TO WS-APPL-RETURN-CODE                 
                   PERFORM 9910-SQL-ERROR             THRU 9910-EXIT    
                END-IF                                                  
           END-EVALUATE.                                                
                                                                        
       6610-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ************************ *************************************            
      *           VALIDATION FOR OPTION CRM_RT_ATTR INCTV          *            
      **************************************************************            
       6620-VLDT-ATTR-INCTV.                                            
                                                                        
           EVALUATE TRUE                                                
           WHEN CRM-INSERT-RT-ATTR-INCTV                                
                PERFORM 7020-CHK-ATTR-INCTV-ROW-EXIST THRU 7020-EXIT    
                IF WS-ATTR-INCTV-ROW-EXIST = 'Y'                        
                   MOVE 5007    TO WS-APPL-RETURN-CODE                  
                   PERFORM 9910-SQL-ERROR             THRU 9910-EXIT    
                END-IF                                                  
                                                                        
           WHEN CRM-DELETE-RT-ATTR-INCTV                                
                PERFORM 7025-CHK-OPT-ROW-EXIST-FOR-G7 THRU 7025-EXIT    
                IF WS-ATTR-INCTV-OPT-ROW-EXIST = 'Y'                    
                   MOVE 5003    TO WS-APPL-RETURN-CODE                  
                   PERFORM 9910-SQL-ERROR             THRU 9910-EXIT    
                END-IF                                                  
           END-EVALUATE.                                                
                                                                        
       6620-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ************************ ****************************************         
      *         VALIDATION FOR TIER ACCT TYPE                         *         
      *****************************************************************         
       6640-VLDT-TIER-ACCT-TYPE.                                        
                                                                        
           EVALUATE TRUE                                                
           WHEN CRM-INSERT-TIER-ACCT-TYPE                               
                PERFORM 7035-CHK-ACCT-TYPE-ROW-EXIST  THRU 7035-EXIT    
                IF WS-TIER-ACCT-TYPE-ROW-EXIST = 'Y'                    
                   MOVE 5007    TO WS-APPL-RETURN-CODE                  
                   PERFORM 9910-SQL-ERROR             THRU 9910-EXIT    
                END-IF                                                  
                                                                        
           WHEN CRM-DELETE-TIER-ACCT-TYPE                               
                PERFORM 7040-CHK-ACCT-TYP-ROW-EXIST-IG THRU 7040-EXIT   
                IF WS-ACCT-TYPE-TIER-ROW-EXIST = 'Y'                    
                   MOVE 5004    TO WS-APPL-RETURN-CODE                  
                   PERFORM 9910-SQL-ERROR             THRU 9910-EXIT    
                END-IF                                                  
           END-EVALUATE.                                                
                                                                        
       6640-EXIT.                                                       
           EXIT.                                                        
      *                                                                         
      *****************************************************************         
      * 7000-CHK-TIER-ROW-EXIST.                                      *         
      *****************************************************************         
       7000-CHK-TIER-ROW-EXIST.                                         
                                                                        
           EXEC SQL                                                     
                SELECT TOP(1) 'Y'                                              
                  INTO :WS-MKT-TIER-ROW-EXIST                           
                  FROM CRM_MKT_TIER IG WITH(READUNCOMMITTED)                    
                 WHERE IG.COMPANY_NO          = :IG-COMPANY-NO          
                   AND IG.TIER_CUST_STAT_CD   = :IG-TIER-CUST-STAT-CD   
                   AND IG.TIER_ACCT_TYPE_CD   = :IG-TIER-ACCT-TYPE-CD   
                   AND IG.MKT_TIER_RANKING    = :IG-MKT-TIER-RANKING    
                   AND IG.MKT_TIER_END_DT    >= IIF(TRY_CONVERT(DATE, 
                                                  :IG-MKT-TIER-START-DT
              ) IS NULL OR (PATINDEX('%.%', :IG-MKT-TIER-START-DT
              ) <> 0) OR (LEN(:IG-MKT-TIER-START-DT
              ) <> 10), CIS.CHAR2DATE(:IG-MKT-TIER-START-DT
              ), CONVERT(DATE, :IG-MKT-TIER-START-DT) )   
                   ORDER BY IG.MKT_TIER_END_DT DESC                     
                                             
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ020
MFA-TR* MSQ022
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*         SELECT 'Y'                                                      
MFA-TR*           INTO :WS-MKT-TIER-ROW-EXIST                                   
MFA-TR*           FROM CRM_MKT_TIER IG                                          
MFA-TR*          WHERE IG.COMPANY_NO          = :IG-COMPANY-NO                  
MFA-TR*            AND IG.TIER_CUST_STAT_CD   = :IG-TIER-CUST-STAT-CD           
MFA-TR*            AND IG.TIER_ACCT_TYPE_CD   = :IG-TIER-ACCT-TYPE-CD           
MFA-TR*            AND IG.MKT_TIER_RANKING    = :IG-MKT-TIER-RANKING            
MFA-TR*            AND IG.MKT_TIER_END_DT    >= :IG-MKT-TIER-START-DT           
MFA-TR*            ORDER BY IG.MKT_TIER_END_DT DESC                             
MFA-TR*            FETCH FIRST ROW ONLY WITH UR                                 
MFA-TR*        QUERYNO 7000                                                     
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    
                                               RS-RETURN-CODE.          
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
              CONTINUE                                                  
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '7000'                    TO ACTIVE-PARAGRAPH        
              MOVE 'SELECT'                  TO ABEND-FUNCTION          
              MOVE 'CRM_MKT_TIER'            TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'TIER_CUST_STAT_CD'       TO TABLE-ELEMENT-2         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-3         
              MOVE 'MKT_TIER_RANKING'        TO TABLE-ELEMENT-4         
              MOVE IG-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE IG-TIER-CUST-STAT-CD      TO HOSTVAR-ELEMENT-2       
              MOVE IG-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-3       
              MOVE IG-MKT-TIER-RANKING       TO HOSTVAR-ELEMENT-4       
              PERFORM 9000-SEND-ERROR-RESULT     THRU 9000-EXIT         
              PERFORM 9900-SQL-ERROR-ROUTINE     THRU 9900-EXIT         
           END-IF.                                                      
                                                                        
       7000-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * CHECK WETHER THE CUSTOMER STATUS / ACCOUNT TYPE / COMPANY NO  *         
      * COMBINATION HAS ANY DEFAULT ROW EXIST.                        *         
      *****************************************************************         
       7001-CHK-DFLT-TR-EXST-4INS.                                      
                                                                        
           EXEC SQL                                                     
                SELECT TOP(1) 'Y'                                              
                  INTO :WS-DFLT-TIER-ROW-EXIST                          
                  FROM CRM_MKT_TIER IG WITH(READUNCOMMITTED)                    
                 WHERE IG.COMPANY_NO          = :IG-COMPANY-NO          
                   AND IG.TIER_CUST_STAT_CD   = :IG-TIER-CUST-STAT-CD   
                   AND IG.TIER_ACCT_TYPE_CD   = :IG-TIER-ACCT-TYPE-CD   
                   AND IG.CUST_ACCT_DEFLT_FL  = :IG-CUST-ACCT-DEFLT-FL  
                   ORDER BY IG.CUST_ACCT_DEFLT_FL DESC                  
                                             
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ020
MFA-TR* MSQ022
MFA-TR*    EXEC SQL                                                             
MFA-TR*         SELECT 'Y'                                                      
MFA-TR*           INTO :WS-DFLT-TIER-ROW-EXIST                                  
MFA-TR*           FROM CRM_MKT_TIER IG                                          
MFA-TR*          WHERE IG.COMPANY_NO          = :IG-COMPANY-NO                  
MFA-TR*            AND IG.TIER_CUST_STAT_CD   = :IG-TIER-CUST-STAT-CD           
MFA-TR*            AND IG.TIER_ACCT_TYPE_CD   = :IG-TIER-ACCT-TYPE-CD           
MFA-TR*            AND IG.CUST_ACCT_DEFLT_FL  = :IG-CUST-ACCT-DEFLT-FL          
MFA-TR*            ORDER BY IG.CUST_ACCT_DEFLT_FL DESC                          
MFA-TR*            FETCH FIRST ROW ONLY WITH UR                                 
MFA-TR*        QUERYNO 7001                                                     
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    
                                               RS-RETURN-CODE.          
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
              CONTINUE                                                  
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '7001'                    TO ACTIVE-PARAGRAPH        
              MOVE 'SELECT'                  TO ABEND-FUNCTION          
              MOVE 'CRM_MKT_TIER'            TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'TIER_CUST_STAT_CD'       TO TABLE-ELEMENT-2         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-3         
              MOVE 'CUST_ACCT_DEFLT_FL'      TO TABLE-ELEMENT-4         
              MOVE IG-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE IG-TIER-CUST-STAT-CD      TO HOSTVAR-ELEMENT-2       
              MOVE IG-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-3       
              MOVE IG-CUST-ACCT-DEFLT-FL     TO HOSTVAR-ELEMENT-4       
              PERFORM 9000-SEND-ERROR-RESULT     THRU 9000-EXIT         
              PERFORM 9900-SQL-ERROR-ROUTINE     THRU 9900-EXIT         
           END-IF.                                                      
                                                                        
       7001-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * CHECK IS THERE ANY ACTIVE OFFERS ALREADY ATTACHED             *         
      * TO THIS MKT TIER.                                             *         
      *****************************************************************         
       7002-CHK-ACT-OFF-ROW-EXIST.                                      
                                                                        
           EXEC SQL                                                     
                SELECT TOP(1) 'Y'                                              
                  INTO :WS-ACTV-OFF-ROW-EXIST-FOR-TIER                  
                  FROM CRM_OFF_MKT_TIER DU WITH(READUNCOMMITTED)                
                 WHERE DU.COMPANY_NO             = :DU-COMPANY-NO       
                   AND DU.TIER_CUST_STAT_CD      = :DU-TIER-CUST-STAT-CD
                   AND DU.TIER_ACCT_TYPE_CD      = :DU-TIER-ACCT-TYPE-CD
                   AND DU.MKT_TIER_RANKING       = :DU-MKT-TIER-RANKING 
                   AND DU.OFFER_TIER_END_DT      > 
              IIF(TRY_CONVERT(DATE, :DU-OFFER-TIER-END-DT
              ) IS NULL OR (PATINDEX('%.%', :DU-OFFER-TIER-END-DT
              ) <> 0) OR (LEN(:DU-OFFER-TIER-END-DT
              ) <> 10), CIS.CHAR2DATE(:DU-OFFER-TIER-END-DT
              ), CONVERT(DATE, :DU-OFFER-TIER-END-DT) )
PRJ680             AND CAST(DU.OFF_TIER_VEND_TS AS DATE) > 
              IIF(TRY_CONVERT(DATE, :DU-OFFER-TIER-END-DT
              ) IS NULL OR (PATINDEX('%.%', :DU-OFFER-TIER-END-DT
              ) <> 0) OR (LEN(:DU-OFFER-TIER-END-DT
              ) <> 10), CIS.CHAR2DATE(:DU-OFFER-TIER-END-DT
              ), CONVERT(DATE, :DU-OFFER-TIER-END-DT) )
                                             
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ020
MFA-TR* MSQ022
MFA-TR* MSQ029
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*         SELECT 'Y'                                                      
MFA-TR*           INTO :WS-ACTV-OFF-ROW-EXIST-FOR-TIER                          
MFA-TR*           FROM CRM_OFF_MKT_TIER DU                                      
MFA-TR*          WHERE DU.COMPANY_NO             = :DU-COMPANY-NO               
MFA-TR*            AND DU.TIER_CUST_STAT_CD      = :DU-TIER-CUST-STAT-CD        
MFA-TR*            AND DU.TIER_ACCT_TYPE_CD      = :DU-TIER-ACCT-TYPE-CD        
MFA-TR*            AND DU.MKT_TIER_RANKING       = :DU-MKT-TIER-RANKING         
MFA-TR*            AND DU.OFFER_TIER_END_DT      > :DU-OFFER-TIER-END-DT        
MFA-TR*            AND DATE(DU.OFF_TIER_VEND_TS) > :DU-OFFER-TIER-END-DT        
MFA-TR*            FETCH FIRST ROW ONLY WITH UR                                 
MFA-TR*        QUERYNO 7002                                                     
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    
                                               RS-RETURN-CODE.          
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
              CONTINUE                                                  
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '7002'                    TO ACTIVE-PARAGRAPH        
              MOVE 'SELECT'                  TO ABEND-FUNCTION          
              MOVE 'CRM_OFF_MKT_TIER'        TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'TIER_CUST_STAT_CD'       TO TABLE-ELEMENT-2         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-3         
              MOVE 'MKT_TIER_RANKING'        TO TABLE-ELEMENT-4         
              MOVE DU-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE DU-TIER-CUST-STAT-CD      TO HOSTVAR-ELEMENT-2       
              MOVE DU-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-3       
              MOVE DU-MKT-TIER-RANKING       TO HOSTVAR-ELEMENT-4       
              PERFORM 9000-SEND-ERROR-RESULT     THRU 9000-EXIT         
              PERFORM 9900-SQL-ERROR-ROUTINE     THRU 9900-EXIT         
           END-IF.                                                      
                                                                        
       7002-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * CHECK WETHER THE CUSTOMER STATUS / ACCOUNT TYPE / COMPANY NO  *         
      * COMBINATION HAS ANY DEFAULT ROW EXIST.                        *         
      *****************************************************************         
       7003-CHK-DFLT-TR-EXST-4UPD.                                      
                                                                        
           EXEC SQL                                                     
                SELECT TOP(1) 'Y'                                              
                  INTO :WS-DFLT-TIER-ROW-EXIST                          
                  FROM CRM_MKT_TIER IG WITH(READUNCOMMITTED)                    
                 WHERE IG.COMPANY_NO          = :IG-COMPANY-NO          
                   AND IG.TIER_CUST_STAT_CD   = :IG-TIER-CUST-STAT-CD   
                   AND IG.TIER_ACCT_TYPE_CD   = :IG-TIER-ACCT-TYPE-CD   
                   AND IG.CUST_ACCT_DEFLT_FL  = :IG-CUST-ACCT-DEFLT-FL  
                   AND IG.MKT_TIER_RANKING   <> :IG-MKT-TIER-RANKING    
                   ORDER BY IG.CUST_ACCT_DEFLT_FL DESC                  
                                             
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ020
MFA-TR* MSQ022
MFA-TR*    EXEC SQL                                                             
MFA-TR*         SELECT 'Y'                                                      
MFA-TR*           INTO :WS-DFLT-TIER-ROW-EXIST                                  
MFA-TR*           FROM CRM_MKT_TIER IG                                          
MFA-TR*          WHERE IG.COMPANY_NO          = :IG-COMPANY-NO                  
MFA-TR*            AND IG.TIER_CUST_STAT_CD   = :IG-TIER-CUST-STAT-CD           
MFA-TR*            AND IG.TIER_ACCT_TYPE_CD   = :IG-TIER-ACCT-TYPE-CD           
MFA-TR*            AND IG.CUST_ACCT_DEFLT_FL  = :IG-CUST-ACCT-DEFLT-FL          
MFA-TR*            AND IG.MKT_TIER_RANKING   <> :IG-MKT-TIER-RANKING            
MFA-TR*            ORDER BY IG.CUST_ACCT_DEFLT_FL DESC                          
MFA-TR*            FETCH FIRST ROW ONLY WITH UR                                 
MFA-TR*        QUERYNO 7003                                                     
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    
                                               RS-RETURN-CODE.          
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
              CONTINUE                                                  
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '7001'                    TO ACTIVE-PARAGRAPH        
              MOVE 'SELECT'                  TO ABEND-FUNCTION          
              MOVE 'CRM_MKT_TIER'            TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'TIER_CUST_STAT_CD'       TO TABLE-ELEMENT-2         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-3         
              MOVE 'CUST_ACCT_DEFLT_FL'      TO TABLE-ELEMENT-4         
              MOVE IG-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE IG-TIER-CUST-STAT-CD      TO HOSTVAR-ELEMENT-2       
              MOVE IG-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-3       
              MOVE IG-CUST-ACCT-DEFLT-FL     TO HOSTVAR-ELEMENT-4       
              PERFORM 9000-SEND-ERROR-RESULT     THRU 9000-EXIT         
              PERFORM 9900-SQL-ERROR-ROUTINE     THRU 9900-EXIT         
           END-IF.                                                      
                                                                        
       7003-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      *                                                                         
      * GET MKT TIER END DT FOR THE INPUT VALUES                                
      *****************************************************************         
       7004-GET-MKT-TIER-END-DT.                                        
                                                                        
           EXEC SQL                                                     
                SELECT 'Y'                                              
                  INTO :WS-UPD-MKT-END-DT                               
                  FROM CRM_MKT_TIER IG                                  
                 WHERE IG.COMPANY_NO          = :IG-COMPANY-NO          
                   AND IG.TIER_CUST_STAT_CD   = :IG-TIER-CUST-STAT-CD   
                   AND IG.TIER_ACCT_TYPE_CD   = :IG-TIER-ACCT-TYPE-CD   
                   AND IG.MKT_TIER_RANKING    = :IG-MKT-TIER-RANKING    
                   AND IG.MKT_TIER_START_DT   = IIF(TRY_CONVERT(DATE, 
                                                  :IG-MKT-TIER-START-DT
              ) IS NULL OR (PATINDEX('%.%', :IG-MKT-TIER-START-DT
              ) <> 0) OR (LEN(:IG-MKT-TIER-START-DT
              ) <> 10), CIS.CHAR2DATE(:IG-MKT-TIER-START-DT
              ), CONVERT(DATE, :IG-MKT-TIER-START-DT) )   
                   AND IG.MKT_TIER_END_DT    <> IIF(TRY_CONVERT(DATE, 
                                                    :IG-MKT-TIER-END-DT
              ) IS NULL OR (PATINDEX('%.%', :IG-MKT-TIER-END-DT
              ) <> 0) OR (LEN(:IG-MKT-TIER-END-DT
              ) <> 10), CIS.CHAR2DATE(:IG-MKT-TIER-END-DT
              ), CONVERT(DATE, :IG-MKT-TIER-END-DT) )     
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*         SELECT 'Y'                                                      
MFA-TR*           INTO :WS-UPD-MKT-END-DT                                       
MFA-TR*           FROM CRM_MKT_TIER IG                                          
MFA-TR*          WHERE IG.COMPANY_NO          = :IG-COMPANY-NO                  
MFA-TR*            AND IG.TIER_CUST_STAT_CD   = :IG-TIER-CUST-STAT-CD           
MFA-TR*            AND IG.TIER_ACCT_TYPE_CD   = :IG-TIER-ACCT-TYPE-CD           
MFA-TR*            AND IG.MKT_TIER_RANKING    = :IG-MKT-TIER-RANKING            
MFA-TR*            AND IG.MKT_TIER_START_DT   = :IG-MKT-TIER-START-DT           
MFA-TR*            AND IG.MKT_TIER_END_DT    <> :IG-MKT-TIER-END-DT             
MFA-TR*        QUERYNO 7004                                                     
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    
                                               RS-RETURN-CODE.          
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
              CONTINUE                                                  
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '7004'                    TO ACTIVE-PARAGRAPH        
              MOVE 'SELECT'                  TO ABEND-FUNCTION          
              MOVE 'CRM_MKT_TIER'            TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'TIER_CUST_STAT_CD'       TO TABLE-ELEMENT-2         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-3         
              MOVE 'CUST_ACCT_DEFLT_FL'      TO TABLE-ELEMENT-4         
              MOVE IG-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE IG-TIER-CUST-STAT-CD      TO HOSTVAR-ELEMENT-2       
              MOVE IG-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-3       
              MOVE IG-CUST-ACCT-DEFLT-FL     TO HOSTVAR-ELEMENT-4       
              PERFORM 9000-SEND-ERROR-RESULT     THRU 9000-EXIT         
              PERFORM 9900-SQL-ERROR-ROUTINE     THRU 9900-EXIT         
           END-IF.                                                      
                                                                        
       7004-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * VALIDATION FOR DELETE - CRM_OFF_MKT_TIER DU                             
      * CHECK IS THERE ANY OFFERS EXISTS IN CRM_OFF_MKT_TIER FOR TIER *         
      *****************************************************************         
       7005-OFF-ROW-EXIST-FOR-TIER.                                     
                                                                        
           EXEC SQL                                                     
                SELECT TOP(1) 'Y'                                              
                  INTO :WS-ACTV-OFF-ROW-EXIST-FOR-TIER                  
                  FROM CRM_OFF_MKT_TIER DU WITH(READUNCOMMITTED)                
                 WHERE DU.COMPANY_NO        = :DU-COMPANY-NO            
                   AND DU.TIER_CUST_STAT_CD = :DU-TIER-CUST-STAT-CD     
                   AND DU.TIER_ACCT_TYPE_CD = :DU-TIER-ACCT-TYPE-CD     
                   AND DU.MKT_TIER_RANKING  = :DU-MKT-TIER-RANKING      
                   AND DU.MKT_TIER_START_DT = IIF(TRY_CONVERT(DATE, 
                                                  :DU-MKT-TIER-START-DT
              ) IS NULL OR (PATINDEX('%.%', :DU-MKT-TIER-START-DT
              ) <> 0) OR (LEN(:DU-MKT-TIER-START-DT
              ) <> 10), CIS.CHAR2DATE(:DU-MKT-TIER-START-DT
              ), CONVERT(DATE, :DU-MKT-TIER-START-DT) )     
                                             
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ020
MFA-TR* MSQ022
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*         SELECT 'Y'                                                      
MFA-TR*           INTO :WS-ACTV-OFF-ROW-EXIST-FOR-TIER                          
MFA-TR*           FROM CRM_OFF_MKT_TIER DU                                      
MFA-TR*          WHERE DU.COMPANY_NO        = :DU-COMPANY-NO                    
MFA-TR*            AND DU.TIER_CUST_STAT_CD = :DU-TIER-CUST-STAT-CD             
MFA-TR*            AND DU.TIER_ACCT_TYPE_CD = :DU-TIER-ACCT-TYPE-CD             
MFA-TR*            AND DU.MKT_TIER_RANKING  = :DU-MKT-TIER-RANKING              
MFA-TR*            AND DU.MKT_TIER_START_DT = :DU-MKT-TIER-START-DT             
MFA-TR*          FETCH FIRST ROW ONLY WITH UR                                   
MFA-TR*        QUERYNO 7005                                                     
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    
                                               RS-RETURN-CODE.          
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '7005'                    TO ACTIVE-PARAGRAPH        
              MOVE 'SELECT'                  TO ABEND-FUNCTION          
              MOVE 'CRM_OFF_MKT_TIER'        TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'TIER_CUST_STAT_CD'       TO TABLE-ELEMENT-2         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-3         
              MOVE 'MKT_TIER_RANKING'        TO TABLE-ELEMENT-4         
              MOVE DU-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE DU-TIER-CUST-STAT-CD      TO HOSTVAR-ELEMENT-2       
              MOVE DU-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-3       
              MOVE DU-MKT-TIER-RANKING       TO HOSTVAR-ELEMENT-4       
              PERFORM 9000-SEND-ERROR-RESULT     THRU 9000-EXIT         
              PERFORM 9900-SQL-ERROR-ROUTINE     THRU 9900-EXIT         
           END-IF.                                                      
                                                                        
       7005-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * CHECK IS THERE ANY ACTIVE RATE GRP ROW EXISTS                           
      *****************************************************************         
       7010-CHK-RATE-GRP-ROW-EXIST.                                     
                                                                        
           EXEC SQL                                                     
                SELECT TOP(1) F7.RT_GRP_END_DT                                 
                  INTO :WS-RT-GRP-END-DT                                
                  FROM CRM_RATE_GROUP   F7 WITH(READUNCOMMITTED)                
                 WHERE F7.COMPANY_NO           = :F7-COMPANY-NO         
                   AND F7.RATE_GROUP_TYPE_CD   = :F7-RATE-GROUP-TYPE-CD 
                   AND F7.RATE_GROUP_CD        = :F7-RATE-GROUP-CD      
                  ORDER BY F7.RT_GRP_END_DT DESC                        
                                             
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ020
MFA-TR* MSQ022
MFA-TR*    EXEC SQL                                                             
MFA-TR*         SELECT F7.RT_GRP_END_DT                                         
MFA-TR*           INTO :WS-RT-GRP-END-DT                                        
MFA-TR*           FROM CRM_RATE_GROUP   F7                                      
MFA-TR*          WHERE F7.COMPANY_NO           = :F7-COMPANY-NO                 
MFA-TR*            AND F7.RATE_GROUP_TYPE_CD   = :F7-RATE-GROUP-TYPE-CD         
MFA-TR*            AND F7.RATE_GROUP_CD        = :F7-RATE-GROUP-CD              
MFA-TR*           ORDER BY F7.RT_GRP_END_DT DESC                                
MFA-TR*           FETCH FIRST ROW ONLY WITH UR                                  
MFA-TR*        QUERYNO 7010                                                     
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    
                                               RS-RETURN-CODE.          
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
              CONTINUE                                                  
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '7010'                    TO ACTIVE-PARAGRAPH        
              MOVE 'SELECT'                  TO ABEND-FUNCTION          
              MOVE 'CRM_RATE_GROUP'          TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'RATE_GROUP_TYPE_CD'      TO TABLE-ELEMENT-2         
              MOVE 'RATE_GROUP_CD'           TO TABLE-ELEMENT-3         
              MOVE 'RT_GRP_END_DT'           TO TABLE-ELEMENT-3         
              MOVE F7-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE F7-RATE-GROUP-TYPE-CD     TO HOSTVAR-ELEMENT-2       
              MOVE F7-RATE-GROUP-CD          TO HOSTVAR-ELEMENT-3       
              MOVE F7-RT-GRP-START-DT        TO HOSTVAR-ELEMENT-4       
              PERFORM 9000-SEND-ERROR-RESULT     THRU 9000-EXIT         
              PERFORM 9900-SQL-ERROR-ROUTINE     THRU 9900-EXIT         
           END-IF.                                                      
                                                                        
       7010-EXIT.                                                       
           EXIT.                                                        
      *                                                                         
      *****************************************************************         
      *                                                                         
      * GET RATE GROUP END DT FOR THE INPUT VALUES                              
      *****************************************************************         
       7011-GET-RT-GROUP-END-DT.                                        
                                                                        
           EXEC SQL                                                     
                SELECT 'Y'                                              
                  INTO :WS-UPD-RT-GRP-END-DT                            
                  FROM CRM_RATE_GROUP F7                                
                 WHERE F7.COMPANY_NO          = :F7-COMPANY-NO          
                   AND F7.RATE_GROUP_TYPE_CD  = :F7-RATE-GROUP-TYPE-CD  
                   AND F7.RATE_GROUP_CD       = :F7-RATE-GROUP-CD       
                   AND F7.RT_GRP_END_DT      <> IIF(TRY_CONVERT(DATE, 
                                                      :F7-RT-GRP-END-DT
              ) IS NULL OR (PATINDEX('%.%', :F7-RT-GRP-END-DT
              ) <> 0) OR (LEN(:F7-RT-GRP-END-DT) <> 10), CIS.CHAR2DATE(
                                                      :F7-RT-GRP-END-DT
              ), CONVERT(DATE, :F7-RT-GRP-END-DT) )       
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*         SELECT 'Y'                                                      
MFA-TR*           INTO :WS-UPD-RT-GRP-END-DT                                    
MFA-TR*           FROM CRM_RATE_GROUP F7                                        
MFA-TR*          WHERE F7.COMPANY_NO          = :F7-COMPANY-NO                  
MFA-TR*            AND F7.RATE_GROUP_TYPE_CD  = :F7-RATE-GROUP-TYPE-CD          
MFA-TR*            AND F7.RATE_GROUP_CD       = :F7-RATE-GROUP-CD               
MFA-TR*            AND F7.RT_GRP_END_DT      <> :F7-RT-GRP-END-DT               
MFA-TR*        QUERYNO 7011                                                     
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    
                                               RS-RETURN-CODE.          
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
              CONTINUE                                                  
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '7011'                    TO ACTIVE-PARAGRAPH        
              MOVE 'SELECT'                  TO ABEND-FUNCTION          
              MOVE 'CRM_RATE_GROUP'          TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'RATE_GROUP_TYPE_CD'      TO TABLE-ELEMENT-2         
              MOVE 'RATE_GROUP_CD'           TO TABLE-ELEMENT-3         
              MOVE F7-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE F7-RATE-GROUP-TYPE-CD     TO HOSTVAR-ELEMENT-2       
              MOVE F7-RATE-GROUP-CD          TO HOSTVAR-ELEMENT-3       
              PERFORM 9000-SEND-ERROR-RESULT     THRU 9000-EXIT         
              PERFORM 9900-SQL-ERROR-ROUTINE     THRU 9900-EXIT         
           END-IF.                                                      
                                                                        
       7011-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * CHECK IS THERE ANY ACTIVE OFFERS ALREADY ATTACHED             *         
      * TO THIS RATE GROUP                                            *         
      *****************************************************************         
       7012-CHK-ACT-OFF-ROW-EXIST.                                      
                                                                        
           EXEC SQL                                                     
                SELECT TOP(1) 'Y'                                              
                  INTO :WS-RATE-GROUP-OFF-ROW-EXIST                     
                  FROM CRM_RATE_GRP_OFFER G5 WITH(READUNCOMMITTED)              
                 WHERE G5.COMPANY_NO            = :G5-COMPANY-NO        
                   AND G5.RATE_GROUP_TYPE_CD    = :G5-RATE-GROUP-TYPE-CD
                   AND G5.RATE_GROUP_CD         = :G5-RATE-GROUP-CD     
                   AND G5.EFF_END_DT            > IIF(TRY_CONVERT(DATE, 
                                                      :F7-RT-GRP-END-DT
              ) IS NULL OR (PATINDEX('%.%', :F7-RT-GRP-END-DT
              ) <> 0) OR (LEN(:F7-RT-GRP-END-DT) <> 10), CIS.CHAR2DATE(
                                                      :F7-RT-GRP-END-DT
              ), CONVERT(DATE, :F7-RT-GRP-END-DT) )     
PRJ680             AND CAST(G5.GRP_OFF_VEND_TS AS DATE) > 
              IIF(TRY_CONVERT(DATE, :F7-RT-GRP-END-DT
              ) IS NULL OR (PATINDEX('%.%', :F7-RT-GRP-END-DT
              ) <> 0) OR (LEN(:F7-RT-GRP-END-DT) <> 10), CIS.CHAR2DATE(
                                                      :F7-RT-GRP-END-DT
              ), CONVERT(DATE, :F7-RT-GRP-END-DT) )     
                                             
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ020
MFA-TR* MSQ022
MFA-TR* MSQ029
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*         SELECT 'Y'                                                      
MFA-TR*           INTO :WS-RATE-GROUP-OFF-ROW-EXIST                             
MFA-TR*           FROM CRM_RATE_GRP_OFFER G5                                    
MFA-TR*          WHERE G5.COMPANY_NO            = :G5-COMPANY-NO                
MFA-TR*            AND G5.RATE_GROUP_TYPE_CD    = :G5-RATE-GROUP-TYPE-CD        
MFA-TR*            AND G5.RATE_GROUP_CD         = :G5-RATE-GROUP-CD             
MFA-TR*            AND G5.EFF_END_DT            > :F7-RT-GRP-END-DT             
MFA-TR*            AND DATE(G5.GRP_OFF_VEND_TS) > :F7-RT-GRP-END-DT             
MFA-TR*           FETCH FIRST ROW ONLY WITH UR                                  
MFA-TR*        QUERYNO 7012                                                     
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    
                                               RS-RETURN-CODE.          
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
              CONTINUE                                                  
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '7012'                    TO ACTIVE-PARAGRAPH        
              MOVE 'SELECT'                  TO ABEND-FUNCTION          
              MOVE 'CRM_RATE_GRP_OFFER'      TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'RATE_GROUP_TYPE_CD'      TO TABLE-ELEMENT-2         
              MOVE 'RATE_GROUP_CD'           TO TABLE-ELEMENT-3         
              MOVE G5-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE G5-RATE-GROUP-TYPE-CD     TO HOSTVAR-ELEMENT-2       
              MOVE G5-RATE-GROUP-CD          TO HOSTVAR-ELEMENT-3       
              PERFORM 9000-SEND-ERROR-RESULT     THRU 9000-EXIT         
              PERFORM 9900-SQL-ERROR-ROUTINE     THRU 9900-EXIT         
           END-IF.                                                      
                                                                        
       7012-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * VALIDATION FOR DELETE - CRM_RATE_GRP_OFFER G5                           
      * CHECK IS THERE ANY OFFER EXISTS IN CRM_RATE_GRP_OFFER FOR     *         
      * THIS RATE GROUP.                                              *         
      *****************************************************************         
       7015-CHK-OFF-EXIST-FOR-RT-GRP.                                   
                                                                        
           EXEC SQL                                                     
                SELECT TOP(1) 'Y'                                              
                  INTO :WS-RATE-GROUP-OFF-ROW-EXIST                     
                  FROM CRM_RATE_GRP_OFFER G5 WITH(READUNCOMMITTED)              
                 WHERE G5.COMPANY_NO         = :G5-COMPANY-NO           
                   AND G5.RATE_GROUP_TYPE_CD = :G5-RATE-GROUP-TYPE-CD   
                   AND G5.RATE_GROUP_CD      = :G5-RATE-GROUP-CD        
                                             
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ020
MFA-TR* MSQ022
MFA-TR*    EXEC SQL                                                             
MFA-TR*         SELECT 'Y'                                                      
MFA-TR*           INTO :WS-RATE-GROUP-OFF-ROW-EXIST                             
MFA-TR*           FROM CRM_RATE_GRP_OFFER G5                                    
MFA-TR*          WHERE G5.COMPANY_NO         = :G5-COMPANY-NO                   
MFA-TR*            AND G5.RATE_GROUP_TYPE_CD = :G5-RATE-GROUP-TYPE-CD           
MFA-TR*            AND G5.RATE_GROUP_CD      = :G5-RATE-GROUP-CD                
MFA-TR*          FETCH FIRST ROW ONLY WITH UR                                   
MFA-TR*        QUERYNO 7015                                                     
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
                                                   RS-RETURN-CODE.      
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '7015'                    TO ACTIVE-PARAGRAPH        
              MOVE 'SELECT'                  TO ABEND-FUNCTION          
              MOVE SPACES                    TO ABEND-SQL-PREDICATES    
                                                ABEND-TABLES            
              MOVE 'CRM_RATE_GRP_OFF'        TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'RATE_GROUP_TYPE_CD'      TO TABLE-ELEMENT-2         
              MOVE 'RATE_GROUP_CD'           TO TABLE-ELEMENT-3         
              MOVE G5-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE G5-RATE-GROUP-TYPE-CD     TO HOSTVAR-ELEMENT-2       
              MOVE G5-RATE-GROUP-CD          TO HOSTVAR-ELEMENT-3       
              PERFORM 9000-SEND-ERROR-RESULT     THRU 9000-EXIT         
              PERFORM 9900-SQL-ERROR-ROUTINE     THRU 9900-EXIT         
           END-IF.                                                      
                                                                        
       7015-EXIT.                                                       
           EXIT.                                                        
      *                                                                         
      *****************************************************************         
      *  VALIDATION FOR INSERT ( CRM_RT_ATTR_INCTV G7 )               *         
      *  CHECK IS THERE ANY ATTR INCTV ACTIVE ROW EXISTS              *         
      *****************************************************************         
       7020-CHK-ATTR-INCTV-ROW-EXIST.                                   
                                                                        
           EXEC SQL                                                     
                SELECT TOP(1) 'Y'                                              
                  INTO :WS-ATTR-INCTV-ROW-EXIST                         
                  FROM CRM_RT_ATTR_INCTV G7 WITH(READUNCOMMITTED)               
                 WHERE G7.COMPANY_NO         = :G7-COMPANY-NO           
                   AND G7.RT_ATTR_INCTV_TYPE = :G7-RT-ATTR-INCTV-TYPE   
                                             
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ020
MFA-TR* MSQ022
MFA-TR*    EXEC SQL                                                             
MFA-TR*         SELECT 'Y'                                                      
MFA-TR*           INTO :WS-ATTR-INCTV-ROW-EXIST                                 
MFA-TR*           FROM CRM_RT_ATTR_INCTV G7                                     
MFA-TR*          WHERE G7.COMPANY_NO         = :G7-COMPANY-NO                   
MFA-TR*            AND G7.RT_ATTR_INCTV_TYPE = :G7-RT-ATTR-INCTV-TYPE           
MFA-TR*          FETCH FIRST ROW ONLY WITH UR                                   
MFA-TR*        QUERYNO 7020                                                     
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '7020'                    TO ACTIVE-PARAGRAPH        
              MOVE 'SELECT'                  TO ABEND-FUNCTION          
              MOVE SPACES                    TO ABEND-SQL-PREDICATES    
                                                ABEND-TABLES            
              MOVE 'CRM_RT_ATTR_INCTV'       TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'RT_ATTR_INCTV_TYPE'      TO TABLE-ELEMENT-2         
              MOVE G7-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE G7-RT-ATTR-INCTV-TYPE     TO HOSTVAR-ELEMENT-2       
              PERFORM 9000-SEND-ERROR-RESULT     THRU 9000-EXIT         
              PERFORM 9900-SQL-ERROR-ROUTINE     THRU 9900-EXIT         
           END-IF.                                                      
                                                                        
       7020-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * VALIDATION FOR DELETE ( CRM_RT_ATTR_INCTV G7 )                *         
      * CHECK IS THERE ANY OPTION EXISTS IN CRM_OPT_ATTR_INCTV FOR    *         
      * THIS ATTR INCTV.                                              *         
      *****************************************************************         
       7025-CHK-OPT-ROW-EXIST-FOR-G7.                                   
                                                                        
           EXEC SQL                                                     
                SELECT TOP(1) 'Y'                                              
                  INTO :WS-ATTR-INCTV-OPT-ROW-EXIST                     
                  FROM CRM_OPT_ATTR_INCTV HQ WITH(READUNCOMMITTED)              
                 WHERE HQ.COMPANY_NO         = :HQ-COMPANY-NO           
                   AND HQ.RT_ATTR_INCTV_TYPE = :HQ-RT-ATTR-INCTV-TYPE   
                                             
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ020
MFA-TR* MSQ022
MFA-TR*    EXEC SQL                                                             
MFA-TR*         SELECT 'Y'                                                      
MFA-TR*           INTO :WS-ATTR-INCTV-OPT-ROW-EXIST                             
MFA-TR*           FROM CRM_OPT_ATTR_INCTV HQ                                    
MFA-TR*          WHERE HQ.COMPANY_NO         = :HQ-COMPANY-NO                   
MFA-TR*            AND HQ.RT_ATTR_INCTV_TYPE = :HQ-RT-ATTR-INCTV-TYPE           
MFA-TR*          FETCH FIRST ROW ONLY WITH UR                                   
MFA-TR*        QUERYNO 7025                                                     
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '7025'                    TO ACTIVE-PARAGRAPH        
              MOVE 'SELECT'                  TO ABEND-FUNCTION          
              MOVE SPACES                    TO ABEND-SQL-PREDICATES    
                                                ABEND-TABLES            
              MOVE 'CRM_OPT_ATTR_INCTV'      TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'RT_ATTR_INCTV_TYPE'      TO TABLE-ELEMENT-2         
              MOVE HQ-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE HQ-RT-ATTR-INCTV-TYPE     TO HOSTVAR-ELEMENT-2       
              PERFORM 9000-SEND-ERROR-RESULT     THRU 9000-EXIT         
              PERFORM 9900-SQL-ERROR-ROUTINE     THRU 9900-EXIT         
           END-IF.                                                      
                                                                        
       7025-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * VALIDATION FOR INSERT - CRM_TIER_ACCT_TYPE TT                 *         
      * CHECK IS THERE ANY TIER ACCT TYPE ACTIVE ROW EXISTS           *         
      *****************************************************************         
       7035-CHK-ACCT-TYPE-ROW-EXIST.                                    
                                                                        
           EXEC SQL                                                     
                SELECT TOP(1) 'Y'                                              
                  INTO :WS-TIER-ACCT-TYPE-ROW-EXIST                     
                  FROM CRM_TIER_ACCT_TYPE TT WITH(READUNCOMMITTED)              
                 WHERE TT.COMPANY_NO         = :TT-COMPANY-NO           
                   AND TT.TIER_ACCT_TYPE_CD  = :TT-TIER-ACCT-TYPE-CD    
                                             
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ020
MFA-TR* MSQ022
MFA-TR*    EXEC SQL                                                             
MFA-TR*         SELECT 'Y'                                                      
MFA-TR*           INTO :WS-TIER-ACCT-TYPE-ROW-EXIST                             
MFA-TR*           FROM CRM_TIER_ACCT_TYPE TT                                    
MFA-TR*          WHERE TT.COMPANY_NO         = :TT-COMPANY-NO                   
MFA-TR*            AND TT.TIER_ACCT_TYPE_CD  = :TT-TIER-ACCT-TYPE-CD            
MFA-TR*          FETCH FIRST ROW ONLY WITH UR                                   
MFA-TR*        QUERYNO 7035                                                     
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '7035'                    TO ACTIVE-PARAGRAPH        
              MOVE 'SELECT'                  TO ABEND-FUNCTION          
              MOVE SPACES                    TO ABEND-SQL-PREDICATES    
                                                ABEND-TABLES            
              MOVE 'CRM_TIER_ACCT_TYPE'      TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-2         
              MOVE TT-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE TT-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-2       
              PERFORM 9000-SEND-ERROR-RESULT     THRU 9000-EXIT         
              PERFORM 9900-SQL-ERROR-ROUTINE     THRU 9900-EXIT         
           END-IF.                                                      
                                                                        
       7035-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * VALIDATION FOR DELETE - CRM_MKT_TIER  IG                      *         
      * CHECK IS THERE ANY ACCT TYPE EXIST IN CRM_MKT_TIER FOR THIS    *        
      * TIER ACCOUNT TYPE.                                            *         
      *****************************************************************         
       7040-CHK-ACCT-TYP-ROW-EXIST-IG.                                  
                                                                        
           EXEC SQL                                                     
                SELECT TOP(1) 'Y'                                              
                  INTO :WS-ACCT-TYPE-TIER-ROW-EXIST                     
                  FROM CRM_MKT_TIER  IG WITH(READUNCOMMITTED)                   
                 WHERE IG.COMPANY_NO         = :IG-COMPANY-NO           
                   AND IG.TIER_ACCT_TYPE_CD  = :IG-TIER-ACCT-TYPE-CD    
                                             
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ020
MFA-TR* MSQ022
MFA-TR*    EXEC SQL                                                             
MFA-TR*         SELECT 'Y'                                                      
MFA-TR*           INTO :WS-ACCT-TYPE-TIER-ROW-EXIST                             
MFA-TR*           FROM CRM_MKT_TIER  IG                                         
MFA-TR*          WHERE IG.COMPANY_NO         = :IG-COMPANY-NO                   
MFA-TR*            AND IG.TIER_ACCT_TYPE_CD  = :IG-TIER-ACCT-TYPE-CD            
MFA-TR*          FETCH FIRST ROW ONLY WITH UR                                   
MFA-TR*        QUERYNO 7040                                                     
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL OR NOT-FOUND      
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '7040'                    TO ACTIVE-PARAGRAPH        
              MOVE 'SELECT'                  TO ABEND-FUNCTION          
              MOVE SPACES                    TO ABEND-SQL-PREDICATES    
                                                ABEND-TABLES            
              MOVE 'CRM_MKT_TIER'            TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-2         
              MOVE IG-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE IG-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-2       
              PERFORM 9000-SEND-ERROR-RESULT     THRU 9000-EXIT         
              PERFORM 9900-SQL-ERROR-ROUTINE     THRU 9900-EXIT         
           END-IF.                                                      
                                                                        
       7040-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 7100-GET-MAX-ADJ-TIER-SEQ-NO.                                  *        
      ******************************************************************        
       7100-GET-MAX-ADJ-TIER-SEQ-NO.                                    
                                                                        
           EXEC SQL                                                     
              SELECT TOP(1) MAX([1B].ADJ_TIER_SEQ_NO)                           
                INTO :1B-ADJ-TIER-SEQ-NO :WS-MAX-ADJ-TIER-SEQ-NO-NULL    
                FROM CRM_MKT_TIER_ADJ [1B] WITH(READUNCOMMITTED)                
                                                    
                                                                 
                                                            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ003
MFA-TR* MSQ020
MFA-TR* MSQ022
MFA-TR* MSQ026
MFA-TR*    EXEC SQL                                                             
MFA-TR*       SELECT MAX(1B.ADJ_TIER_SEQ_NO)                                    
MFA-TR*         INTO :1B-ADJ-TIER-SEQ-NO:WS-MAX-ADJ-TIER-SEQ-NO-NULL            
MFA-TR*         FROM CRM_MKT_TIER_ADJ 1B                                        
MFA-TR*        FETCH FIRST ROW ONLY                                             
MFA-TR*         WITH UR                                                         
MFA-TR*      QUERYNO 7100                                                       
MFA-TR*    END-EXEC.                                                            

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

                                                                        
           MOVE SQLCODE                      TO WS-ACTIVE-RETURN-CODE.  
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
              IF WS-MAX-ADJ-TIER-SEQ-NO-NULL  = WS-YES-NULL             
                 MOVE ZERO                   TO 1B-ADJ-TIER-SEQ-NO      
              END-IF                                                    
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '7100'                    TO ACTIVE-PARAGRAPH        
              MOVE 'SELECT'                  TO ABEND-FUNCTION          
              MOVE SPACES                    TO ABEND-SQL-PREDICATES    
                                                ABEND-TABLES            
              MOVE 'CRM_MKT_TIER_ADJ'        TO TABLE-1                 
              MOVE 'TIER_CUST_STAT_CD'       TO TABLE-ELEMENT-1         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-2         
              MOVE 'MKT_TIER_RANKING'        TO TABLE-ELEMENT-3         
              MOVE 'MKT_TIER_START_DT'       TO TABLE-ELEMENT-4         
              MOVE 1B-TIER-CUST-STAT-CD      TO HOSTVAR-ELEMENT-1       
              MOVE 1B-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-2       
              MOVE 1B-MKT-TIER-RANKING       TO HOSTVAR-ELEMENT-3       
              MOVE 1B-MKT-TIER-START-DT      TO HOSTVAR-ELEMENT-4       
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       7100-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 8030-INS-MKT-TIER.                                             *        
      ******************************************************************        
       8030-INS-MKT-TIER.                                               
                                                                        
           EXEC SQL                                                     
               INSERT INTO CRM_MKT_TIER                                 
               (                                                        
                  COMPANY_NO                                            
                 ,TIER_CUST_STAT_CD                                     
                 ,TIER_ACCT_TYPE_CD                                     
                 ,MKT_TIER_RANKING                                      
                 ,MKT_TIER_START_DT                                     
                 ,MKT_TIER_DESC                                         
                 ,MKT_TIER_END_DT                                       
                 ,TIER_OFFER_VIEW                                       
                 ,CUST_ACCT_DEFLT_FL                                    
                 ,MKT_TIER_DESC_LONG                                    
                 ,LAST_UPDATE_USERID                                    
                 ,LAST_UPDATE_TS                                        
               )                                                        
               VALUES                                                   
               (                                                        
                  :IG-COMPANY-NO                                        
                 ,:IG-TIER-CUST-STAT-CD                                 
                 ,:IG-TIER-ACCT-TYPE-CD                                 
                 ,:IG-MKT-TIER-RANKING                                  
                 ,IIF(TRY_CONVERT(DATE, :IG-MKT-TIER-START-DT
              ) IS NULL OR (PATINDEX('%.%', :IG-MKT-TIER-START-DT
              ) <> 0) OR (LEN(:IG-MKT-TIER-START-DT
              ) <> 10), CIS.CHAR2DATE(:IG-MKT-TIER-START-DT
              ), CONVERT(DATE, :IG-MKT-TIER-START-DT) )                        
                 ,:IG-MKT-TIER-DESC                                     
                 ,IIF(TRY_CONVERT(DATE, :IG-MKT-TIER-END-DT
              ) IS NULL OR (PATINDEX('%.%', :IG-MKT-TIER-END-DT
              ) <> 0) OR (LEN(:IG-MKT-TIER-END-DT
              ) <> 10), CIS.CHAR2DATE(:IG-MKT-TIER-END-DT
              ), CONVERT(DATE, :IG-MKT-TIER-END-DT) )                          
                 ,:IG-TIER-OFFER-VIEW                                   
                 ,:IG-CUST-ACCT-DEFLT-FL                                
                 ,:IG-MKT-TIER-DESC-LONG                                
                 ,:IG-LAST-UPDATE-USERID                                
                 , CIS.CURRENT$TIMESTAMP()                                    
               )                                                        
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ028
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*        INSERT INTO CRM_MKT_TIER                                         
MFA-TR*        (                                                                
MFA-TR*           COMPANY_NO                                                    
MFA-TR*          ,TIER_CUST_STAT_CD                                             
MFA-TR*          ,TIER_ACCT_TYPE_CD                                             
MFA-TR*          ,MKT_TIER_RANKING                                              
MFA-TR*          ,MKT_TIER_START_DT                                             
MFA-TR*          ,MKT_TIER_DESC                                                 
MFA-TR*          ,MKT_TIER_END_DT                                               
MFA-TR*          ,TIER_OFFER_VIEW                                               
MFA-TR*          ,CUST_ACCT_DEFLT_FL                                            
MFA-TR*          ,MKT_TIER_DESC_LONG                                            
MFA-TR*          ,LAST_UPDATE_USERID                                            
MFA-TR*          ,LAST_UPDATE_TS                                                
MFA-TR*        )                                                                
MFA-TR*        VALUES                                                           
MFA-TR*        (                                                                
MFA-TR*           :IG-COMPANY-NO                                                
MFA-TR*          ,:IG-TIER-CUST-STAT-CD                                         
MFA-TR*          ,:IG-TIER-ACCT-TYPE-CD                                         
MFA-TR*          ,:IG-MKT-TIER-RANKING                                          
MFA-TR*          ,:IG-MKT-TIER-START-DT                                         
MFA-TR*          ,:IG-MKT-TIER-DESC                                             
MFA-TR*          ,:IG-MKT-TIER-END-DT                                           
MFA-TR*          ,:IG-TIER-OFFER-VIEW                                           
MFA-TR*          ,:IG-CUST-ACCT-DEFLT-FL                                        
MFA-TR*          ,:IG-MKT-TIER-DESC-LONG                                        
MFA-TR*          ,:IG-LAST-UPDATE-USERID                                        
MFA-TR*          , CURRENT TIMESTAMP                                            
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '8030'                    TO ACTIVE-PARAGRAPH        
              MOVE 'INSERT'                  TO ABEND-FUNCTION          
              MOVE 'CRM_MKT_TIER'            TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'TIER_CUST_STAT_CD'       TO TABLE-ELEMENT-2         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-3         
              MOVE 'MKT_TIER_RANKING'        TO TABLE-ELEMENT-4         
              MOVE IG-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE IG-TIER-CUST-STAT-CD      TO HOSTVAR-ELEMENT-2       
              MOVE IG-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-3       
              MOVE IG-MKT-TIER-RANKING       TO HOSTVAR-ELEMENT-4       
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       8030-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 8040-UPT-MKT-TIER.                                             *        
      ******************************************************************        
       8040-UPT-MKT-TIER.                                               
                                                                        
           EXEC SQL                                                     
               UPDATE CRM_MKT_TIER                                      
                  SET MKT_TIER_DESC      = :IG-MKT-TIER-DESC            
                     ,MKT_TIER_END_DT    = IIF(TRY_CONVERT(DATE, 
                                                    :IG-MKT-TIER-END-DT
              ) IS NULL OR (PATINDEX('%.%', :IG-MKT-TIER-END-DT
              ) <> 0) OR (LEN(:IG-MKT-TIER-END-DT
              ) <> 10), CIS.CHAR2DATE(:IG-MKT-TIER-END-DT
              ), CONVERT(DATE, :IG-MKT-TIER-END-DT) )          
                     ,TIER_OFFER_VIEW    = :IG-TIER-OFFER-VIEW          
                     ,CUST_ACCT_DEFLT_FL = :IG-CUST-ACCT-DEFLT-FL       
                     ,MKT_TIER_DESC_LONG = :IG-MKT-TIER-DESC-LONG       
                     ,LAST_UPDATE_USERID = :IG-LAST-UPDATE-USERID       
                     ,LAST_UPDATE_TS     =  CIS.CURRENT$TIMESTAMP()           
                WHERE COMPANY_NO         = :IG-COMPANY-NO               
                  AND TIER_CUST_STAT_CD  = :IG-TIER-CUST-STAT-CD        
                  AND TIER_ACCT_TYPE_CD  = :IG-TIER-ACCT-TYPE-CD        
                  AND MKT_TIER_RANKING   = :IG-MKT-TIER-RANKING         
                  AND MKT_TIER_START_DT  = IIF(TRY_CONVERT(DATE, 
                                                  :IG-MKT-TIER-START-DT
              ) IS NULL OR (PATINDEX('%.%', :IG-MKT-TIER-START-DT
              ) <> 0) OR (LEN(:IG-MKT-TIER-START-DT
              ) <> 10), CIS.CHAR2DATE(:IG-MKT-TIER-START-DT
              ), CONVERT(DATE, :IG-MKT-TIER-START-DT) )        
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ028
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*        UPDATE CRM_MKT_TIER                                              
MFA-TR*           SET MKT_TIER_DESC      = :IG-MKT-TIER-DESC                    
MFA-TR*              ,MKT_TIER_END_DT    = :IG-MKT-TIER-END-DT                  
MFA-TR*              ,TIER_OFFER_VIEW    = :IG-TIER-OFFER-VIEW                  
MFA-TR*              ,CUST_ACCT_DEFLT_FL = :IG-CUST-ACCT-DEFLT-FL               
MFA-TR*              ,MKT_TIER_DESC_LONG = :IG-MKT-TIER-DESC-LONG               
MFA-TR*              ,LAST_UPDATE_USERID = :IG-LAST-UPDATE-USERID               
MFA-TR*              ,LAST_UPDATE_TS     =  CURRENT TIMESTAMP                   
MFA-TR*         WHERE COMPANY_NO         = :IG-COMPANY-NO                       
MFA-TR*           AND TIER_CUST_STAT_CD  = :IG-TIER-CUST-STAT-CD                
MFA-TR*           AND TIER_ACCT_TYPE_CD  = :IG-TIER-ACCT-TYPE-CD                
MFA-TR*           AND MKT_TIER_RANKING   = :IG-MKT-TIER-RANKING                 
MFA-TR*           AND MKT_TIER_START_DT  = :IG-MKT-TIER-START-DT                
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '8040'                    TO ACTIVE-PARAGRAPH        
              MOVE 'UPDATE'                  TO ABEND-FUNCTION          
              MOVE 'CRM_MKT_TIER'            TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'TIER_CUST_STAT_CD'       TO TABLE-ELEMENT-2         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-3         
              MOVE 'MKT_TIER_RANKING'        TO TABLE-ELEMENT-4         
              MOVE IG-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE IG-TIER-CUST-STAT-CD      TO HOSTVAR-ELEMENT-2       
              MOVE IG-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-3       
              MOVE IG-MKT-TIER-RANKING       TO HOSTVAR-ELEMENT-4       
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       8040-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      *                                                                *        
      * IF THERE IS NO OFFERS EXISTS IN CRM_OFF_MKT_TIER FOR THH TIER  *        
      ******************************************************************        
       8045-DEL-MKT-TIER.                                               
                                                                        
           EXEC SQL                                                     
             DELETE FROM IG                                 
             FROM CRM_MKT_TIER IG
              WHERE IG.COMPANY_NO          = :IG-COMPANY-NO             
                AND IG.TIER_CUST_STAT_CD   = :IG-TIER-CUST-STAT-CD      
                AND IG.TIER_ACCT_TYPE_CD   = :IG-TIER-ACCT-TYPE-CD      
                AND IG.MKT_TIER_RANKING    = :IG-MKT-TIER-RANKING       
                AND IG.MKT_TIER_START_DT   = IIF(TRY_CONVERT(DATE, 
                                                  :IG-MKT-TIER-START-DT
              ) IS NULL OR (PATINDEX('%.%', :IG-MKT-TIER-START-DT
              ) <> 0) OR (LEN(:IG-MKT-TIER-START-DT
              ) <> 10), CIS.CHAR2DATE(:IG-MKT-TIER-START-DT
              ), CONVERT(DATE, :IG-MKT-TIER-START-DT) )      
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ019
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*      DELETE FROM CRM_MKT_TIER IG                                        
MFA-TR*       WHERE IG.COMPANY_NO          = :IG-COMPANY-NO                     
MFA-TR*         AND IG.TIER_CUST_STAT_CD   = :IG-TIER-CUST-STAT-CD              
MFA-TR*         AND IG.TIER_ACCT_TYPE_CD   = :IG-TIER-ACCT-TYPE-CD              
MFA-TR*         AND IG.MKT_TIER_RANKING    = :IG-MKT-TIER-RANKING               
MFA-TR*         AND IG.MKT_TIER_START_DT   = :IG-MKT-TIER-START-DT              
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '8045'                    TO ACTIVE-PARAGRAPH        
              MOVE 'DELETE'                  TO ABEND-FUNCTION          
              MOVE 'CRM_MKT_TIER'            TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'TIER_CUST_STAT_CD'       TO TABLE-ELEMENT-2         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-3         
              MOVE 'MKT_TIER_RANKING'        TO TABLE-ELEMENT-4         
              MOVE IG-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE IG-TIER-CUST-STAT-CD      TO HOSTVAR-ELEMENT-2       
              MOVE IG-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-3       
              MOVE IG-MKT-TIER-RANKING       TO HOSTVAR-ELEMENT-4       
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       8045-EXIT.                                                       
           EXIT.                                                        
                                                                        
                                                                        
      ******************************************************************        
      *                                                                *        
      * 8050-INS-RATE-GROUP.                                           *        
      ******************************************************************        
       8050-INS-RATE-GROUP.                                             
                                                                        
           EXEC SQL                                                     
               INSERT INTO CRM_RATE_GROUP                               
               (                                                        
                  COMPANY_NO                                            
                 ,RATE_GROUP_TYPE_CD                                    
                 ,RATE_GROUP_CD                                         
                 ,RT_GRP_SORT_SEQ_NO                                    
                 ,RATE_GRP_DESC                                         
                 ,RATE_GRP_DESC_LONG                                    
                 ,LAST_UPDATE_USERID                                    
                 ,LAST_UPDATE_TS                                        
                 ,RT_GRP_START_DT                                       
                 ,RT_GRP_END_DT                                         
               )                                                        
               VALUES                                                   
               (                                                        
                  :F7-COMPANY-NO                                        
                 ,:F7-RATE-GROUP-TYPE-CD                                
                 ,:F7-RATE-GROUP-CD                                     
                 ,:F7-RT-GRP-SORT-SEQ-NO                                
                 ,:F7-RATE-GRP-DESC                                     
                 ,:F7-RATE-GRP-DESC-LONG                                
                 ,:F7-LAST-UPDATE-USERID                                
                 , CIS.CURRENT$TIMESTAMP()                                    
                 ,IIF(TRY_CONVERT(DATE, :F7-RT-GRP-START-DT
              ) IS NULL OR (PATINDEX('%.%', :F7-RT-GRP-START-DT
              ) <> 0) OR (LEN(:F7-RT-GRP-START-DT
              ) <> 10), CIS.CHAR2DATE(:F7-RT-GRP-START-DT
              ), CONVERT(DATE, :F7-RT-GRP-START-DT) )                          
                 ,IIF(TRY_CONVERT(DATE, :F7-RT-GRP-END-DT
              ) IS NULL OR (PATINDEX('%.%', :F7-RT-GRP-END-DT
              ) <> 0) OR (LEN(:F7-RT-GRP-END-DT) <> 10), CIS.CHAR2DATE(
                                                      :F7-RT-GRP-END-DT
              ), CONVERT(DATE, :F7-RT-GRP-END-DT) )                            
               )                                                        
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ028
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*        INSERT INTO CRM_RATE_GROUP                                       
MFA-TR*        (                                                                
MFA-TR*           COMPANY_NO                                                    
MFA-TR*          ,RATE_GROUP_TYPE_CD                                            
MFA-TR*          ,RATE_GROUP_CD                                                 
MFA-TR*          ,RT_GRP_SORT_SEQ_NO                                            
MFA-TR*          ,RATE_GRP_DESC                                                 
MFA-TR*          ,RATE_GRP_DESC_LONG                                            
MFA-TR*          ,LAST_UPDATE_USERID                                            
MFA-TR*          ,LAST_UPDATE_TS                                                
MFA-TR*          ,RT_GRP_START_DT                                               
MFA-TR*          ,RT_GRP_END_DT                                                 
MFA-TR*        )                                                                
MFA-TR*        VALUES                                                           
MFA-TR*        (                                                                
MFA-TR*           :F7-COMPANY-NO                                                
MFA-TR*          ,:F7-RATE-GROUP-TYPE-CD                                        
MFA-TR*          ,:F7-RATE-GROUP-CD                                             
MFA-TR*          ,:F7-RT-GRP-SORT-SEQ-NO                                        
MFA-TR*          ,:F7-RATE-GRP-DESC                                             
MFA-TR*          ,:F7-RATE-GRP-DESC-LONG                                        
MFA-TR*          ,:F7-LAST-UPDATE-USERID                                        
MFA-TR*          , CURRENT TIMESTAMP                                            
MFA-TR*          ,:F7-RT-GRP-START-DT                                           
MFA-TR*          ,:F7-RT-GRP-END-DT                                             
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '8050'                    TO ACTIVE-PARAGRAPH        
              MOVE 'INSERT'                  TO ABEND-FUNCTION          
              MOVE 'CRM_RATE_GROUP'          TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'RATE_GROUP_TYPE_CD'      TO TABLE-ELEMENT-2         
              MOVE 'RATE_GROUP_CD'           TO TABLE-ELEMENT-3         
              MOVE F7-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE F7-RATE-GROUP-TYPE-CD     TO HOSTVAR-ELEMENT-1       
              MOVE F7-RATE-GROUP-CD          TO HOSTVAR-ELEMENT-3       
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       8050-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 8060-UPT-RATE-GROUP.                                           *        
      ******************************************************************        
       8060-UPT-RATE-GROUP.                                             
                                                                        
           EXEC SQL                                                     
               UPDATE CRM_RATE_GROUP                                    
                  SET RT_GRP_SORT_SEQ_NO = :F7-RT-GRP-SORT-SEQ-NO       
                     ,RATE_GRP_DESC      = :F7-RATE-GRP-DESC            
                     ,RATE_GRP_DESC_LONG = :F7-RATE-GRP-DESC-LONG       
                     ,LAST_UPDATE_USERID = :F7-LAST-UPDATE-USERID       
                     ,LAST_UPDATE_TS     =  CIS.CURRENT$TIMESTAMP()           
                     ,RT_GRP_START_DT    = IIF(TRY_CONVERT(DATE, 
                                                    :F7-RT-GRP-START-DT
              ) IS NULL OR (PATINDEX('%.%', :F7-RT-GRP-START-DT
              ) <> 0) OR (LEN(:F7-RT-GRP-START-DT
              ) <> 10), CIS.CHAR2DATE(:F7-RT-GRP-START-DT
              ), CONVERT(DATE, :F7-RT-GRP-START-DT) )          
                     ,RT_GRP_END_DT      = IIF(TRY_CONVERT(DATE, 
                                                      :F7-RT-GRP-END-DT
              ) IS NULL OR (PATINDEX('%.%', :F7-RT-GRP-END-DT
              ) <> 0) OR (LEN(:F7-RT-GRP-END-DT) <> 10), CIS.CHAR2DATE(
                                                      :F7-RT-GRP-END-DT
              ), CONVERT(DATE, :F7-RT-GRP-END-DT) )            
                WHERE COMPANY_NO         = :F7-COMPANY-NO               
                  AND RATE_GROUP_TYPE_CD = :F7-RATE-GROUP-TYPE-CD       
                  AND RATE_GROUP_CD      = :F7-RATE-GROUP-CD            
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ028
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*        UPDATE CRM_RATE_GROUP                                            
MFA-TR*           SET RT_GRP_SORT_SEQ_NO = :F7-RT-GRP-SORT-SEQ-NO               
MFA-TR*              ,RATE_GRP_DESC      = :F7-RATE-GRP-DESC                    
MFA-TR*              ,RATE_GRP_DESC_LONG = :F7-RATE-GRP-DESC-LONG               
MFA-TR*              ,LAST_UPDATE_USERID = :F7-LAST-UPDATE-USERID               
MFA-TR*              ,LAST_UPDATE_TS     =  CURRENT TIMESTAMP                   
MFA-TR*              ,RT_GRP_START_DT    = :F7-RT-GRP-START-DT                  
MFA-TR*              ,RT_GRP_END_DT      = :F7-RT-GRP-END-DT                    
MFA-TR*         WHERE COMPANY_NO         = :F7-COMPANY-NO                       
MFA-TR*           AND RATE_GROUP_TYPE_CD = :F7-RATE-GROUP-TYPE-CD               
MFA-TR*           AND RATE_GROUP_CD      = :F7-RATE-GROUP-CD                    
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '8060'                    TO ACTIVE-PARAGRAPH        
              MOVE 'UPDATE'                  TO ABEND-FUNCTION          
              MOVE 'CRM_RATE_GROUP'          TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'RATE_GROUP_TYPE_CD'      TO TABLE-ELEMENT-2         
              MOVE 'RATE_GROUP_CD'           TO TABLE-ELEMENT-3         
              MOVE F7-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE F7-RATE-GROUP-TYPE-CD     TO HOSTVAR-ELEMENT-1       
              MOVE F7-RATE-GROUP-CD          TO HOSTVAR-ELEMENT-3       
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       8060-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * IF THERE IS NO OFFERS EXISTS IN CRM_RATE_GRP_OFF FOR THH RT GRP*        
      ******************************************************************        
       8065-DEL-RATE-GROUP.                                             
                                                                        
           EXEC SQL                                                     
             DELETE FROM F7                               
             FROM CRM_RATE_GROUP F7
              WHERE F7.COMPANY_NO          = :F7-COMPANY-NO             
                AND F7.RATE_GROUP_TYPE_CD  = :F7-RATE-GROUP-TYPE-CD     
                AND F7.RATE_GROUP_CD       = :F7-RATE-GROUP-CD          
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ019
MFA-TR*    EXEC SQL                                                             
MFA-TR*      DELETE FROM CRM_RATE_GROUP F7                                      
MFA-TR*       WHERE F7.COMPANY_NO          = :F7-COMPANY-NO                     
MFA-TR*         AND F7.RATE_GROUP_TYPE_CD  = :F7-RATE-GROUP-TYPE-CD             
MFA-TR*         AND F7.RATE_GROUP_CD       = :F7-RATE-GROUP-CD                  
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '8065'                    TO ACTIVE-PARAGRAPH        
              MOVE 'DELETE'                  TO ABEND-FUNCTION          
              MOVE 'CRM_RATE_GROUP'          TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'RATE_GROUP_TYPE_CD'      TO TABLE-ELEMENT-2         
              MOVE 'RATE_GROUP_CD'           TO TABLE-ELEMENT-3         
              MOVE F7-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE F7-RATE-GROUP-TYPE-CD     TO HOSTVAR-ELEMENT-2       
              MOVE F7-RATE-GROUP-CD          TO HOSTVAR-ELEMENT-3       
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       8065-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 8100-INS-ATTR-INCTV.                                           *        
      ******************************************************************        
       8100-INS-ATTR-INCTV.                                             
                                                                        
           EXEC SQL                                                     
               INSERT INTO CRM_RT_ATTR_INCTV                            
               (                                                        
                  COMPANY_NO                                            
                 ,RT_ATTR_INCTV_TYPE                                    
                 ,RT_ATTR_INCTV_DESC                                    
                 ,SOURCE_CD                                             
                 ,DURATION_START_CD                                     
                 ,ATTR_INCTV_DISP_CD                                    
                 ,ATTR_INCTV_GEN_CD                                     
                 ,STATUS_CD                                             
                 ,LAST_UPDATE_USERID                                    
                 ,LAST_UPDATE_TS                                        
               )                                                        
               VALUES                                                   
               (                                                        
                  :G7-COMPANY-NO                                        
                 ,:G7-RT-ATTR-INCTV-TYPE                                
                 ,:G7-RT-ATTR-INCTV-DESC                                
                 ,:G7-SOURCE-CD                                         
                 ,:G7-DURATION-START-CD                                 
                 ,:G7-ATTR-INCTV-DISP-CD                                
                 ,:G7-ATTR-INCTV-GEN-CD                                 
                 ,:G7-STATUS-CD                                         
                 ,:G7-LAST-UPDATE-USERID                                
                 ,CIS.CURRENT$TIMESTAMP()                                     
               )                                                        
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ028
MFA-TR*    EXEC SQL                                                             
MFA-TR*        INSERT INTO CRM_RT_ATTR_INCTV                                    
MFA-TR*        (                                                                
MFA-TR*           COMPANY_NO                                                    
MFA-TR*          ,RT_ATTR_INCTV_TYPE                                            
MFA-TR*          ,RT_ATTR_INCTV_DESC                                            
MFA-TR*          ,SOURCE_CD                                                     
MFA-TR*          ,DURATION_START_CD                                             
MFA-TR*          ,ATTR_INCTV_DISP_CD                                            
MFA-TR*          ,ATTR_INCTV_GEN_CD                                             
MFA-TR*          ,STATUS_CD                                                     
MFA-TR*          ,LAST_UPDATE_USERID                                            
MFA-TR*          ,LAST_UPDATE_TS                                                
MFA-TR*        )                                                                
MFA-TR*        VALUES                                                           
MFA-TR*        (                                                                
MFA-TR*           :G7-COMPANY-NO                                                
MFA-TR*          ,:G7-RT-ATTR-INCTV-TYPE                                        
MFA-TR*          ,:G7-RT-ATTR-INCTV-DESC                                        
MFA-TR*          ,:G7-SOURCE-CD                                                 
MFA-TR*          ,:G7-DURATION-START-CD                                         
MFA-TR*          ,:G7-ATTR-INCTV-DISP-CD                                        
MFA-TR*          ,:G7-ATTR-INCTV-GEN-CD                                         
MFA-TR*          ,:G7-STATUS-CD                                                 
MFA-TR*          ,:G7-LAST-UPDATE-USERID                                        
MFA-TR*          ,CURRENT TIMESTAMP                                             
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '8100'                    TO ACTIVE-PARAGRAPH        
              MOVE 'INSERT'                  TO ABEND-FUNCTION          
              MOVE 'CRM_RT_ATTR_INCTV'       TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'RT_ATTR_INCTV_TYPE'      TO TABLE-ELEMENT-2         
              MOVE G7-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE G7-RT-ATTR-INCTV-TYPE     TO HOSTVAR-ELEMENT-2       
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       8100-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 8110-UPT-ATTR-INCTV.                                           *        
      ******************************************************************        
       8110-UPT-ATTR-INCTV.                                             
                                                                        
           EXEC SQL                                                     
               UPDATE G7                               
                  SET RT_ATTR_INCTV_DESC = :G7-RT-ATTR-INCTV-DESC       
                     ,SOURCE_CD          = :G7-SOURCE-CD                
                     ,DURATION_START_CD  = :G7-DURATION-START-CD        
                     ,ATTR_INCTV_DISP_CD = :G7-ATTR-INCTV-DISP-CD       
                     ,ATTR_INCTV_GEN_CD  = :G7-ATTR-INCTV-GEN-CD        
                     ,STATUS_CD          = :G7-STATUS-CD                
                     ,LAST_UPDATE_USERID = :G7-LAST-UPDATE-USERID       
                     ,LAST_UPDATE_TS     =  CIS.CURRENT$TIMESTAMP()           
               FROM CRM_RT_ATTR_INCTV G7
                WHERE COMPANY_NO         = :G7-COMPANY-NO               
                  AND RT_ATTR_INCTV_TYPE = :G7-RT-ATTR-INCTV-TYPE       
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ019
MFA-TR* MSQ028
MFA-TR*    EXEC SQL                                                             
MFA-TR*        UPDATE CRM_RT_ATTR_INCTV G7                                      
MFA-TR*           SET RT_ATTR_INCTV_DESC = :G7-RT-ATTR-INCTV-DESC               
MFA-TR*              ,SOURCE_CD          = :G7-SOURCE-CD                        
MFA-TR*              ,DURATION_START_CD  = :G7-DURATION-START-CD                
MFA-TR*              ,ATTR_INCTV_DISP_CD = :G7-ATTR-INCTV-DISP-CD               
MFA-TR*              ,ATTR_INCTV_GEN_CD  = :G7-ATTR-INCTV-GEN-CD                
MFA-TR*              ,STATUS_CD          = :G7-STATUS-CD                        
MFA-TR*              ,LAST_UPDATE_USERID = :G7-LAST-UPDATE-USERID               
MFA-TR*              ,LAST_UPDATE_TS     =  CURRENT TIMESTAMP                   
MFA-TR*         WHERE COMPANY_NO         = :G7-COMPANY-NO                       
MFA-TR*           AND RT_ATTR_INCTV_TYPE = :G7-RT-ATTR-INCTV-TYPE               
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '8110'                    TO ACTIVE-PARAGRAPH        
              MOVE 'UPDATE'                  TO ABEND-FUNCTION          
              MOVE 'CRM_RT_ATTR_INCTV'       TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'RT_ATTR_INCTV_TYPE'      TO TABLE-ELEMENT-2         
              MOVE WS-CRM-COMPANY-NO         TO HOSTVAR-ELEMENT-1       
              MOVE G7-RT-ATTR-INCTV-TYPE     TO HOSTVAR-ELEMENT-2       
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       8110-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * IF THERE IS NO OPTION EXISTS IN CRM_OPT_ATTR_INCTV             *        
      * FOR THE ATTR INCTV.                                            *        
      ******************************************************************        
       8120-DEL-ATTR-INCTV.                                             
                                                                        
           EXEC SQL                                                     
             DELETE FROM G7                            
             FROM CRM_RT_ATTR_INCTV G7
              WHERE G7.COMPANY_NO          = :G7-COMPANY-NO             
                AND G7.RT_ATTR_INCTV_TYPE  = :G7-RT-ATTR-INCTV-TYPE     
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ019
MFA-TR*    EXEC SQL                                                             
MFA-TR*      DELETE FROM CRM_RT_ATTR_INCTV G7                                   
MFA-TR*       WHERE G7.COMPANY_NO          = :G7-COMPANY-NO                     
MFA-TR*         AND G7.RT_ATTR_INCTV_TYPE  = :G7-RT-ATTR-INCTV-TYPE             
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '8120'                    TO ACTIVE-PARAGRAPH        
              MOVE 'DELETE'                  TO ABEND-FUNCTION          
              MOVE 'CRM_RT_ATTR_INCTV'       TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'RT_ATTR_INCTV_TYPE'      TO TABLE-ELEMENT-2         
              MOVE G7-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE G7-RT-ATTR-INCTV-TYPE     TO HOSTVAR-ELEMENT-2       
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       8120-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 8130-INS-MKT-TIER-ADJ.                                         *        
      ******************************************************************        
       8130-INS-MKT-TIER-ADJ.                                           
                                                                        
           EXEC SQL                                                     
               INSERT INTO CRM_MKT_TIER_ADJ                             
               (                                                        
                  COMPANY_NO                                            
                 ,TIER_CUST_STAT_CD                                     
                 ,TIER_ACCT_TYPE_CD                                     
                 ,MKT_TIER_RANKING                                      
                 ,MKT_TIER_START_DT                                     
                 ,ADJ_TIER_SEQ_NO                                       
                 ,AGL_LOW_INCM_SR_FL                                    
                 ,MIN_GOOD_PAY_QTY                                      
                 ,MISSED_PYMT_QTY                                       
                 ,EVAL_MONTHS_QTY                                       
                 ,APPLICATION_CD                                        
                 ,ADJ_TIER_ACCT_TYPE                                    
                 ,ADJ_TIER_RANKING                                      
                 ,ADJ_TIER_START_DT                                     
                 ,ADJ_START_DT                                          
                 ,ADJ_END_DT                                            
                 ,ADJ_SOURCE_CD                                         
                 ,LAST_UPDATE_TS                                        
                 ,LAST_UPDATE_USERID                                    
                 ,DHR_QUALIFIED_FL                                      
                 ,TIER_ADJ_DESC                                         
               )                                                        
               VALUES                                                   
               (                                                        
                  :1B-COMPANY-NO                                        
                 ,:1B-TIER-CUST-STAT-CD                                 
                 ,:1B-TIER-ACCT-TYPE-CD                                 
                 ,:1B-MKT-TIER-RANKING                                  
                 ,IIF(TRY_CONVERT(DATE, :1B-MKT-TIER-START-DT
              ) IS NULL OR (PATINDEX('%.%', :1B-MKT-TIER-START-DT
              ) <> 0) OR (LEN(:1B-MKT-TIER-START-DT
              ) <> 10), CIS.CHAR2DATE(:1B-MKT-TIER-START-DT
              ), CONVERT(DATE, :1B-MKT-TIER-START-DT) )                        
                 ,:1B-ADJ-TIER-SEQ-NO                                   
                 ,:1B-AGL-LOW-INCM-SR-FL                                
                 ,:1B-MIN-GOOD-PAY-QTY :WS-MIN-GOOD-PAY-QTY-NULL         
                 ,:1B-MISSED-PYMT-QTY :WS-MISSED-PYMT-QTY-NULL           
                 ,:1B-EVAL-MONTHS-QTY :WS-EVAL-MONTHS-QTY-NULL           
                 ,:1B-APPLICATION-CD                                    
                 ,:1B-ADJ-TIER-ACCT-TYPE                                
                 ,:1B-ADJ-TIER-RANKING                                  
                 ,IIF(TRY_CONVERT(DATE, :1B-ADJ-TIER-START-DT
              ) IS NULL OR (PATINDEX('%.%', :1B-ADJ-TIER-START-DT
              ) <> 0) OR (LEN(:1B-ADJ-TIER-START-DT
              ) <> 10), CIS.CHAR2DATE(:1B-ADJ-TIER-START-DT
              ), CONVERT(DATE, :1B-ADJ-TIER-START-DT) )                        
                 ,IIF(TRY_CONVERT(DATE, :1B-ADJ-START-DT
              ) IS NULL OR (PATINDEX('%.%', :1B-ADJ-START-DT
              ) <> 0) OR (LEN(:1B-ADJ-START-DT) <> 10), CIS.CHAR2DATE(
                                                       :1B-ADJ-START-DT
              ), CONVERT(DATE, :1B-ADJ-START-DT) )                             
                 ,IIF(TRY_CONVERT(DATE, :1B-ADJ-END-DT
              ) IS NULL OR (PATINDEX('%.%', :1B-ADJ-END-DT
              ) <> 0) OR (LEN(:1B-ADJ-END-DT) <> 10), CIS.CHAR2DATE(
                                                         :1B-ADJ-END-DT
              ), CONVERT(DATE, :1B-ADJ-END-DT) )                               
                 ,:1B-ADJ-SOURCE-CD                                     
                 , CIS.CURRENT$TIMESTAMP()                                    
                 ,:1B-LAST-UPDATE-USERID                                
                 ,:1B-DHR-QUALIFIED-FL                                  
                 ,:1B-TIER-ADJ-DESC                                     
               )                                                        
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ028
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*        INSERT INTO CRM_MKT_TIER_ADJ                                     
MFA-TR*        (                                                                
MFA-TR*           COMPANY_NO                                                    
MFA-TR*          ,TIER_CUST_STAT_CD                                             
MFA-TR*          ,TIER_ACCT_TYPE_CD                                             
MFA-TR*          ,MKT_TIER_RANKING                                              
MFA-TR*          ,MKT_TIER_START_DT                                             
MFA-TR*          ,ADJ_TIER_SEQ_NO                                               
MFA-TR*          ,AGL_LOW_INCM_SR_FL                                            
MFA-TR*          ,MIN_GOOD_PAY_QTY                                              
MFA-TR*          ,MISSED_PYMT_QTY                                               
MFA-TR*          ,EVAL_MONTHS_QTY                                               
MFA-TR*          ,APPLICATION_CD                                                
MFA-TR*          ,ADJ_TIER_ACCT_TYPE                                            
MFA-TR*          ,ADJ_TIER_RANKING                                              
MFA-TR*          ,ADJ_TIER_START_DT                                             
MFA-TR*          ,ADJ_START_DT                                                  
MFA-TR*          ,ADJ_END_DT                                                    
MFA-TR*          ,ADJ_SOURCE_CD                                                 
MFA-TR*          ,LAST_UPDATE_TS                                                
MFA-TR*          ,LAST_UPDATE_USERID                                            
MFA-TR*          ,DHR_QUALIFIED_FL                                              
MFA-TR*          ,TIER_ADJ_DESC                                                 
MFA-TR*        )                                                                
MFA-TR*        VALUES                                                           
MFA-TR*        (                                                                
MFA-TR*           :1B-COMPANY-NO                                                
MFA-TR*          ,:1B-TIER-CUST-STAT-CD                                         
MFA-TR*          ,:1B-TIER-ACCT-TYPE-CD                                         
MFA-TR*          ,:1B-MKT-TIER-RANKING                                          
MFA-TR*          ,:1B-MKT-TIER-START-DT                                         
MFA-TR*          ,:1B-ADJ-TIER-SEQ-NO                                           
MFA-TR*          ,:1B-AGL-LOW-INCM-SR-FL                                        
MFA-TR*          ,:1B-MIN-GOOD-PAY-QTY:WS-MIN-GOOD-PAY-QTY-NULL                 
MFA-TR*          ,:1B-MISSED-PYMT-QTY:WS-MISSED-PYMT-QTY-NULL                   
MFA-TR*          ,:1B-EVAL-MONTHS-QTY:WS-EVAL-MONTHS-QTY-NULL                   
MFA-TR*          ,:1B-APPLICATION-CD                                            
MFA-TR*          ,:1B-ADJ-TIER-ACCT-TYPE                                        
MFA-TR*          ,:1B-ADJ-TIER-RANKING                                          
MFA-TR*          ,:1B-ADJ-TIER-START-DT                                         
MFA-TR*          ,:1B-ADJ-START-DT                                              
MFA-TR*          ,:1B-ADJ-END-DT                                                
MFA-TR*          ,:1B-ADJ-SOURCE-CD                                             
MFA-TR*          , CURRENT TIMESTAMP                                            
MFA-TR*          ,:1B-LAST-UPDATE-USERID                                        
MFA-TR*          ,:1B-DHR-QUALIFIED-FL                                          
MFA-TR*          ,:1B-TIER-ADJ-DESC                                             
MFA-TR*        )                                                                
MFA-TR*    END-EXEC.                                                            

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

                                                                        
           MOVE SQLCODE                      TO WS-ACTIVE-RETURN-CODE   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '8130'                    TO ACTIVE-PARAGRAPH        
              MOVE 'INSERT'                  TO ABEND-FUNCTION          
              MOVE 'CRM_MKT_TIER_ADJ'        TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'TIER_CUST_STAT_CD'       TO TABLE-ELEMENT-2         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-3         
              MOVE 'MKT_TIER_RANKING'        TO TABLE-ELEMENT-4         
              MOVE 1B-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE 1B-TIER-CUST-STAT-CD      TO HOSTVAR-ELEMENT-2       
              MOVE 1B-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-3       
              MOVE 1B-MKT-TIER-RANKING       TO HOSTVAR-ELEMENT-4       
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       8130-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 8140-UPT-MKT-TIER-ADJ.                                         *        
      ******************************************************************        
       8140-UPT-MKT-TIER-ADJ.                                           
                                                                        
           EXEC SQL                                                     
               UPDATE CRM_MKT_TIER_ADJ                                  
                  SET AGL_LOW_INCM_SR_FL = :1B-AGL-LOW-INCM-SR-FL       
                     ,MIN_GOOD_PAY_QTY   = :1B-MIN-GOOD-PAY-QTY 
                                           :WS-MIN-GOOD-PAY-QTY-NULL    
                     ,MISSED_PYMT_QTY    = :1B-MISSED-PYMT-QTY 
                                           :WS-MISSED-PYMT-QTY-NULL     
                     ,EVAL_MONTHS_QTY    = :1B-EVAL-MONTHS-QTY 
                                           :WS-EVAL-MONTHS-QTY-NULL     
                     ,APPLICATION_CD     = :1B-APPLICATION-CD           
                     ,ADJ_TIER_ACCT_TYPE = :1B-ADJ-TIER-ACCT-TYPE       
                     ,ADJ_TIER_RANKING   = :1B-ADJ-TIER-RANKING         
                     ,ADJ_TIER_START_DT  = IIF(TRY_CONVERT(DATE, 
                                                  :1B-ADJ-TIER-START-DT
              ) IS NULL OR (PATINDEX('%.%', :1B-ADJ-TIER-START-DT
              ) <> 0) OR (LEN(:1B-ADJ-TIER-START-DT
              ) <> 10), CIS.CHAR2DATE(:1B-ADJ-TIER-START-DT
              ), CONVERT(DATE, :1B-ADJ-TIER-START-DT) )        
                     ,ADJ_START_DT       = IIF(TRY_CONVERT(DATE, 
                                                       :1B-ADJ-START-DT
              ) IS NULL OR (PATINDEX('%.%', :1B-ADJ-START-DT
              ) <> 0) OR (LEN(:1B-ADJ-START-DT) <> 10), CIS.CHAR2DATE(
                                                       :1B-ADJ-START-DT
              ), CONVERT(DATE, :1B-ADJ-START-DT) )             
                     ,ADJ_END_DT         = IIF(TRY_CONVERT(DATE, 
                                                         :1B-ADJ-END-DT
              ) IS NULL OR (PATINDEX('%.%', :1B-ADJ-END-DT
              ) <> 0) OR (LEN(:1B-ADJ-END-DT) <> 10), CIS.CHAR2DATE(
                                                         :1B-ADJ-END-DT
              ), CONVERT(DATE, :1B-ADJ-END-DT) )               
                     ,ADJ_SOURCE_CD      = :1B-ADJ-SOURCE-CD            
                     ,LAST_UPDATE_TS     =  CIS.CURRENT$TIMESTAMP()           
                     ,LAST_UPDATE_USERID = :1B-LAST-UPDATE-USERID       
                     ,DHR_QUALIFIED_FL   = :1B-DHR-QUALIFIED-FL         
                     ,TIER_ADJ_DESC      = :1B-TIER-ADJ-DESC            
                WHERE COMPANY_NO         = :1B-COMPANY-NO               
                  AND TIER_CUST_STAT_CD  = :1B-TIER-CUST-STAT-CD        
                  AND TIER_ACCT_TYPE_CD  = :1B-TIER-ACCT-TYPE-CD        
                  AND MKT_TIER_RANKING   = :1B-MKT-TIER-RANKING         
                  AND MKT_TIER_START_DT  = IIF(TRY_CONVERT(DATE, 
                                                  :1B-MKT-TIER-START-DT
              ) IS NULL OR (PATINDEX('%.%', :1B-MKT-TIER-START-DT
              ) <> 0) OR (LEN(:1B-MKT-TIER-START-DT
              ) <> 10), CIS.CHAR2DATE(:1B-MKT-TIER-START-DT
              ), CONVERT(DATE, :1B-MKT-TIER-START-DT) )        
                  AND ADJ_TIER_SEQ_NO    = :1B-ADJ-TIER-SEQ-NO          
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ028
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*        UPDATE CRM_MKT_TIER_ADJ                                          
MFA-TR*           SET AGL_LOW_INCM_SR_FL = :1B-AGL-LOW-INCM-SR-FL               
MFA-TR*              ,MIN_GOOD_PAY_QTY   = :1B-MIN-GOOD-PAY-QTY                 
MFA-TR*                                    :WS-MIN-GOOD-PAY-QTY-NULL            
MFA-TR*              ,MISSED_PYMT_QTY    = :1B-MISSED-PYMT-QTY                  
MFA-TR*                                    :WS-MISSED-PYMT-QTY-NULL             
MFA-TR*              ,EVAL_MONTHS_QTY    = :1B-EVAL-MONTHS-QTY                  
MFA-TR*                                    :WS-EVAL-MONTHS-QTY-NULL             
MFA-TR*              ,APPLICATION_CD     = :1B-APPLICATION-CD                   
MFA-TR*              ,ADJ_TIER_ACCT_TYPE = :1B-ADJ-TIER-ACCT-TYPE               
MFA-TR*              ,ADJ_TIER_RANKING   = :1B-ADJ-TIER-RANKING                 
MFA-TR*              ,ADJ_TIER_START_DT  = :1B-ADJ-TIER-START-DT                
MFA-TR*              ,ADJ_START_DT       = :1B-ADJ-START-DT                     
MFA-TR*              ,ADJ_END_DT         = :1B-ADJ-END-DT                       
MFA-TR*              ,ADJ_SOURCE_CD      = :1B-ADJ-SOURCE-CD                    
MFA-TR*              ,LAST_UPDATE_TS     =  CURRENT TIMESTAMP                   
MFA-TR*              ,LAST_UPDATE_USERID = :1B-LAST-UPDATE-USERID               
MFA-TR*              ,DHR_QUALIFIED_FL   = :1B-DHR-QUALIFIED-FL                 
MFA-TR*              ,TIER_ADJ_DESC      = :1B-TIER-ADJ-DESC                    
MFA-TR*         WHERE COMPANY_NO         = :1B-COMPANY-NO                       
MFA-TR*           AND TIER_CUST_STAT_CD  = :1B-TIER-CUST-STAT-CD                
MFA-TR*           AND TIER_ACCT_TYPE_CD  = :1B-TIER-ACCT-TYPE-CD                
MFA-TR*           AND MKT_TIER_RANKING   = :1B-MKT-TIER-RANKING                 
MFA-TR*           AND MKT_TIER_START_DT  = :1B-MKT-TIER-START-DT                
MFA-TR*           AND ADJ_TIER_SEQ_NO    = :1B-ADJ-TIER-SEQ-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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '8140'                    TO ACTIVE-PARAGRAPH        
              MOVE 'UPDATE'                  TO ABEND-FUNCTION          
              MOVE 'CRM_MKT_TIER_ADJ'        TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'TIER_CUST_STAT_CD'       TO TABLE-ELEMENT-2         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-3         
              MOVE 'MKT_TIER_RANKING'        TO TABLE-ELEMENT-4         
              MOVE 1B-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE 1B-TIER-CUST-STAT-CD      TO HOSTVAR-ELEMENT-2       
              MOVE 1B-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-3       
              MOVE 1B-MKT-TIER-RANKING       TO HOSTVAR-ELEMENT-4       
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       8140-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * DELETE IN CRM_MKT_TIER_ADJ WHEN                                         
      *                                                                         
      ******************************************************************        
       8145-DEL-MKT-TIER-ADJ.                                           
                                                                        
           EXEC SQL                                                     
             DELETE FROM [1B]                             
             FROM CRM_MKT_TIER_ADJ [1B]
              WHERE [1B].COMPANY_NO         = :1B-COMPANY-NO              
                AND [1B].TIER_CUST_STAT_CD  = :1B-TIER-CUST-STAT-CD       
                AND [1B].TIER_ACCT_TYPE_CD  = :1B-TIER-ACCT-TYPE-CD       
                AND [1B].MKT_TIER_RANKING   = :1B-MKT-TIER-RANKING        
                AND [1B].MKT_TIER_START_DT  = IIF(TRY_CONVERT(DATE, 
                                                  :1B-MKT-TIER-START-DT
              ) IS NULL OR (PATINDEX('%.%', :1B-MKT-TIER-START-DT
              ) <> 0) OR (LEN(:1B-MKT-TIER-START-DT
              ) <> 10), CIS.CHAR2DATE(:1B-MKT-TIER-START-DT
              ), CONVERT(DATE, :1B-MKT-TIER-START-DT) )       
                AND [1B].ADJ_TIER_SEQ_NO    = :1B-ADJ-TIER-SEQ-NO         
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ019
MFA-TR* MSQ026
MFA-TR* MSQ054
MFA-TR*    EXEC SQL                                                             
MFA-TR*      DELETE FROM CRM_MKT_TIER_ADJ 1B                                    
MFA-TR*       WHERE 1B.COMPANY_NO         = :1B-COMPANY-NO                      
MFA-TR*         AND 1B.TIER_CUST_STAT_CD  = :1B-TIER-CUST-STAT-CD               
MFA-TR*         AND 1B.TIER_ACCT_TYPE_CD  = :1B-TIER-ACCT-TYPE-CD               
MFA-TR*         AND 1B.MKT_TIER_RANKING   = :1B-MKT-TIER-RANKING                
MFA-TR*         AND 1B.MKT_TIER_START_DT  = :1B-MKT-TIER-START-DT               
MFA-TR*         AND 1B.ADJ_TIER_SEQ_NO    = :1B-ADJ-TIER-SEQ-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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '8145'                    TO ACTIVE-PARAGRAPH        
              MOVE 'DELETE'                  TO ABEND-FUNCTION          
              MOVE 'CRM_MKT_TIER_ADJ'        TO TABLE-1                 
              MOVE 'IB.TIER_CUST_STAT_CD'    TO TABLE-ELEMENT-1         
              MOVE 'IB.TIER_ACCT_TYPE_CD'    TO TABLE-ELEMENT-2         
              MOVE 'IB.MKT_TIER_RANKING'     TO TABLE-ELEMENT-3         
              MOVE 1B-TIER-CUST-STAT-CD      TO HOSTVAR-ELEMENT-1       
              MOVE 1B-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-2       
              MOVE 1B-MKT-TIER-RANKING      TO HOSTVAR-ELEMENT-3        
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       8145-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 8150-INS-TIER-ACCT-TYPE.                                       *        
      ******************************************************************        
       8150-INS-TIER-ACCT-TYPE.                                         
                                                                        
           EXEC SQL                                                     
               INSERT INTO CRM_TIER_ACCT_TYPE                           
               (                                                        
                  COMPANY_NO                                            
                 ,TIER_ACCT_TYPE_CD                                     
                 ,ACCOUNT_TYPE_CODE                                     
                 ,TIER_ACCT_DESC                                        
                 ,TIER_ACCT_DESC_LNG                                    
                 ,LAST_UPDATE_TS                                        
                 ,LAST_UPDATE_USERID                                    
                 ,WEB_RENEWAL_FL                                        
                 ,REV_COMPANY_NO                                        
               )                                                        
               VALUES                                                   
               (                                                        
                  :TT-COMPANY-NO                                        
                 ,:TT-TIER-ACCT-TYPE-CD                                 
                 ,:TT-ACCOUNT-TYPE-CODE                                 
                 ,:TT-TIER-ACCT-DESC                                    
                 ,:TT-TIER-ACCT-DESC-LNG                                
                 , CIS.CURRENT$TIMESTAMP()                                    
                 ,:TT-LAST-UPDATE-USERID                                
                 ,:TT-WEB-RENEWAL-FL                                    
                 ,:TT-REV-COMPANY-NO                                    
               )                                                        
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ028
MFA-TR*    EXEC SQL                                                             
MFA-TR*        INSERT INTO CRM_TIER_ACCT_TYPE                                   
MFA-TR*        (                                                                
MFA-TR*           COMPANY_NO                                                    
MFA-TR*          ,TIER_ACCT_TYPE_CD                                             
MFA-TR*          ,ACCOUNT_TYPE_CODE                                             
MFA-TR*          ,TIER_ACCT_DESC                                                
MFA-TR*          ,TIER_ACCT_DESC_LNG                                            
MFA-TR*          ,LAST_UPDATE_TS                                                
MFA-TR*          ,LAST_UPDATE_USERID                                            
MFA-TR*          ,WEB_RENEWAL_FL                                                
MFA-TR*          ,REV_COMPANY_NO                                                
MFA-TR*        )                                                                
MFA-TR*        VALUES                                                           
MFA-TR*        (                                                                
MFA-TR*           :TT-COMPANY-NO                                                
MFA-TR*          ,:TT-TIER-ACCT-TYPE-CD                                         
MFA-TR*          ,:TT-ACCOUNT-TYPE-CODE                                         
MFA-TR*          ,:TT-TIER-ACCT-DESC                                            
MFA-TR*          ,:TT-TIER-ACCT-DESC-LNG                                        
MFA-TR*          , CURRENT TIMESTAMP                                            
MFA-TR*          ,:TT-LAST-UPDATE-USERID                                        
MFA-TR*          ,:TT-WEB-RENEWAL-FL                                            
MFA-TR*          ,:TT-REV-COMPANY-NO                                            
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '8150'                    TO ACTIVE-PARAGRAPH        
              MOVE 'INSERT'                  TO ABEND-FUNCTION          
              MOVE 'CRM_TIER_ACCT_TYPE'      TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-2         
              MOVE TT-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE TT-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-2       
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       8150-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 8160-UPT-TIER-ACCT-TYPE                                        *        
      ******************************************************************        
       8160-UPT-TIER-ACCT-TYPE.                                         
                                                                        
           EXEC SQL                                                     
               UPDATE CRM_TIER_ACCT_TYPE                                
                                                                        
                  SET ACCOUNT_TYPE_CODE  = :TT-ACCOUNT-TYPE-CODE        
                     ,TIER_ACCT_DESC     = :TT-TIER-ACCT-DESC           
                     ,TIER_ACCT_DESC_LNG = :TT-TIER-ACCT-DESC-LNG       
                     ,LAST_UPDATE_TS     =  CIS.CURRENT$TIMESTAMP()           
                     ,LAST_UPDATE_USERID = :TT-LAST-UPDATE-USERID       
                     ,WEB_RENEWAL_FL     = :TT-WEB-RENEWAL-FL           
                     ,REV_COMPANY_NO     = :TT-REV-COMPANY-NO           
                WHERE COMPANY_NO         = :TT-COMPANY-NO               
                  AND TIER_ACCT_TYPE_CD  = :TT-TIER-ACCT-TYPE-CD        
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ028
MFA-TR*    EXEC SQL                                                             
MFA-TR*        UPDATE CRM_TIER_ACCT_TYPE                                        
MFA-TR*                                                                         
MFA-TR*           SET ACCOUNT_TYPE_CODE  = :TT-ACCOUNT-TYPE-CODE                
MFA-TR*              ,TIER_ACCT_DESC     = :TT-TIER-ACCT-DESC                   
MFA-TR*              ,TIER_ACCT_DESC_LNG = :TT-TIER-ACCT-DESC-LNG               
MFA-TR*              ,LAST_UPDATE_TS     =  CURRENT TIMESTAMP                   
MFA-TR*              ,LAST_UPDATE_USERID = :TT-LAST-UPDATE-USERID               
MFA-TR*              ,WEB_RENEWAL_FL     = :TT-WEB-RENEWAL-FL                   
MFA-TR*              ,REV_COMPANY_NO     = :TT-REV-COMPANY-NO                   
MFA-TR*         WHERE COMPANY_NO         = :TT-COMPANY-NO                       
MFA-TR*           AND TIER_ACCT_TYPE_CD  = :TT-TIER-ACCT-TYPE-CD                
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '8160'                    TO ACTIVE-PARAGRAPH        
              MOVE 'UPDATE'                  TO ABEND-FUNCTION          
              MOVE 'CRM_TIER_ACCT_TYPE'      TO TABLE-1                 
              MOVE 'COMPANY_NO'              TO TABLE-ELEMENT-1         
              MOVE 'TIER_ACCT_TYPE_CD'       TO TABLE-ELEMENT-2         
              MOVE TT-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE TT-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-2       
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       8160-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * DELETE IN CRM_TIER_ACCT_TYPE WHEN THERE IS NO KT TIER ATTACHED *        
      * WITH TIER ACCOUNT TYPE.                                        *        
      ******************************************************************        
       8165-DEL-TIER-ACCT-TYPE.                                         
                                                                        
           EXEC SQL                                                     
             DELETE FROM TT                           
             FROM CRM_TIER_ACCT_TYPE TT
              WHERE TT.COMPANY_NO         = :TT-COMPANY-NO              
                AND TT.TIER_ACCT_TYPE_CD  = :TT-TIER-ACCT-TYPE-CD       
           END-EXEC.                                                    

MFA-TR* TRANSFORMATION LIST
MFA-TR* MSQ019
MFA-TR*    EXEC SQL                                                             
MFA-TR*      DELETE FROM CRM_TIER_ACCT_TYPE TT                                  
MFA-TR*       WHERE TT.COMPANY_NO         = :TT-COMPANY-NO                      
MFA-TR*         AND TT.TIER_ACCT_TYPE_CD  = :TT-TIER-ACCT-TYPE-CD               
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   
                                                RS-RETURN-CODE.         
                                                                        
           IF WS-ACTIVE-RETURN-CODE = SUCCESSFUL-CALL                   
               NEXT SENTENCE                                            
           ELSE                                                         
              MOVE PROGRAM-NAME              TO ABEND-PROGRAM           
              MOVE '8165'                    TO ACTIVE-PARAGRAPH        
              MOVE 'DELETE'                  TO ABEND-FUNCTION          
              MOVE 'CRM_TIER_ACCT_TYPE'      TO TABLE-1                 
              MOVE 'TT.COMPANY_NO'           TO TABLE-ELEMENT-1         
              MOVE 'TT.TIER_ACCT_TYPE_CD'    TO TABLE-ELEMENT-2         
              MOVE TT-COMPANY-NO             TO HOSTVAR-ELEMENT-1       
              MOVE TT-TIER-ACCT-TYPE-CD      TO HOSTVAR-ELEMENT-2       
              PERFORM 9000-SEND-ERROR-RESULT    THRU 9000-EXIT          
              PERFORM 9900-SQL-ERROR-ROUTINE    THRU 9900-EXIT          
           END-IF.                                                      
                                                                        
       8165-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      *8000-SEND-RESULT                                                *        
      ******************************************************************        
       8000-SEND-RESULT.                                                
                                                                        
           ADD +1                       TO CTR-ROWS.                    
                                                                        
       8000-EXIT.                                                       
           EXIT.                                                        
                                                                        
      *****************************************************************         
      * 9910-SQL-ERROR.                                               *         
      *****************************************************************         
       9910-SQL-ERROR.                                                  
                                                                        
           PERFORM 2000A-MOVE-RESULT             THRU 2000A-EXIT.       
           PERFORM 8000-SEND-RESULT              THRU 8000-EXIT.        
           PERFORM 9999-END-PROGRAM              THRU 9999-EXIT.        
                                                                        
       9910-EXIT.                                                       
           EXIT.                                                        
                                                                        
      ******************************************************************        
      * 9900 - JOURNALING / ERROR HANDLING ROUTINE                     *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE CPDSP300                                                  
           END-EXEC.                                                            
                                                                        
      ******************************************************************        
      * 9000-SEND-ERROR-RESULT.                                        *        
      ******************************************************************        
           EXEC SQL                                                             
              INCLUDE CPD00321                                                  
           END-EXEC.                                                            
