DROP TRIGGER TR_INSERT_GROUPMASTER
GO
CREATE TRIGGER [dbo].[TR_INSERT_GROUPMASTER] ON 
EMRGROUPMASTER FOR INSERT
AS
BEGIN
DECLARE 
  @currentGroupID               NUMERIC(20,0),
  @currentScreenImmunID         NUMERIC(20,0),
  @currentScreenImmunMasterID   NUMERIC(20,0),
  @currentScreenImmunRecordType VARCHAR(2),
  @seedScreenImmunID            NUMERIC(20,0),
  @updateQuery1                 VARCHAR(4000),
  @updateQuery2                 VARCHAR(4000),
  @insertQuery                  VARCHAR(4000),
  @propertyName                 VARCHAR(50),
  @SCREEN_IMMUN_ID              NUMERIC(20,0),
  @RECORD_TYPE                  VARCHAR(2),
  @GENDER                       NUMERIC(20,0),
  @SUB_GENDER                   NUMERIC(20,0),
  @DESCRIPTION                  VARCHAR(500),
  @STARTING_AGE_OPERATOR        VARCHAR(255),
  @STARTING_AGE_VALUE           NUMERIC(20,0),
  @STARTING_AGE_UNITS           VARCHAR(255),
  @FREQUENCY                    VARCHAR(500),
  @STARTING_AGE_LIMIT_RANGE     NUMERIC(20,0),
  @STARTING_AGE_LIMIT_UNITS     VARCHAR(255),
  @AGE_END_LIMIT                NUMERIC(20,0),
  @AGE_END_LIMIT_UNITS          VARCHAR(255),
  @REPEAT                       INT,
  @REPEAT_AGE_VALUE             NUMERIC(20,0),
  @REPEAT_AGE_UNITS             VARCHAR(255),
  @FREQUENCY_LIMIT              NUMERIC(20,0),
  @REPEAT_CONDITION             VARCHAR(255),
  @GROUP_ID                     NUMERIC(20,0),
  @STATUS                       NUMERIC(5,0),
  @CDC_CODE                     VARCHAR(50),
  @SCREEN_COMMENTS              VARCHAR(255),
  @MASTER_REFERENCE_ID          NUMERIC(20,0)
BEGIN
    DECLARE ezEMRxGroups CURSOR STATIC for SELECT GROUP_ID FROM EMRGROUPMASTER WHERE STATUS = 1
    DECLARE ezEMRxScreenImmunRecords CURSOR STATIC for  SELECT SCREEN_IMMUN_ID, RECORD_TYPE,GENDER,SUB_GENDER,DESCRIPTION,STARTING_AGE_OPERATOR,
	                       STARTING_AGE_VALUE,STARTING_AGE_UNITS,FREQUENCY,STARTING_AGE_LIMIT_RANGE,STARTING_AGE_LIMIT_UNITS,
			       AGE_END_LIMIT,AGE_END_LIMIT_UNITS,REPEAT,REPEAT_AGE_VALUE,REPEAT_AGE_UNITS,FREQUENCY_LIMIT,
                               REPEAT_CONDITION, GROUP_ID,STATUS,CDC_CODE,SCREEN_COMMENTS,MASTER_REFERENCE_ID FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID = 0 
          ORDER BY SCREEN_IMMUN_ID  

    SELECT @currentScreenImmunID = ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME = 'SCREEN_IMMUN_ID'   
    OPEN ezEMRxGroups
    FETCH NEXT FROM ezEMRxGroups INTO @currentGroupID
    WHILE @@FETCH_Status = 0
     BEGIN
       
       OPEN ezEMRxScreenImmunRecords
       FETCH NEXT FROM ezEMRxScreenImmunRecords INTO @SCREEN_IMMUN_ID, @RECORD_TYPE,@GENDER,@SUB_GENDER,@DESCRIPTION,@STARTING_AGE_OPERATOR,
	                       @STARTING_AGE_VALUE,@STARTING_AGE_UNITS,@FREQUENCY,@STARTING_AGE_LIMIT_RANGE,@STARTING_AGE_LIMIT_UNITS,
			       @AGE_END_LIMIT,@AGE_END_LIMIT_UNITS,@REPEAT,@REPEAT_AGE_VALUE,@REPEAT_AGE_UNITS,@FREQUENCY_LIMIT,
                               @REPEAT_CONDITION, @GROUP_ID,@STATUS,@CDC_CODE,@SCREEN_COMMENTS,@MASTER_REFERENCE_ID
      
       WHILE @@FETCH_Status = 0   
	BEGIN     
         SET  @MASTER_REFERENCE_ID = @SCREEN_IMMUN_ID
         SET  @SCREEN_IMMUN_ID = @currentScreenImmunID
         SET  @GROUP_ID = @currentGroupID
         INSERT INTO EMRSCREENIMMUNLKUP(SCREEN_IMMUN_ID, RECORD_TYPE,GENDER,SUB_GENDER,DESCRIPTION,STARTING_AGE_OPERATOR,
	                       STARTING_AGE_VALUE,STARTING_AGE_UNITS,FREQUENCY,STARTING_AGE_LIMIT_RANGE,STARTING_AGE_LIMIT_UNITS,
			       AGE_END_LIMIT,AGE_END_LIMIT_UNITS,REPEAT,REPEAT_AGE_VALUE,REPEAT_AGE_UNITS,FREQUENCY_LIMIT,
                               REPEAT_CONDITION, GROUP_ID,STATUS,CDC_CODE,SCREEN_COMMENTS,MASTER_REFERENCE_ID) 
		  VALUES (@SCREEN_IMMUN_ID, @RECORD_TYPE,@GENDER,@SUB_GENDER,@DESCRIPTION,@STARTING_AGE_OPERATOR,
	                       @STARTING_AGE_VALUE,@STARTING_AGE_UNITS,@FREQUENCY,@STARTING_AGE_LIMIT_RANGE,@STARTING_AGE_LIMIT_UNITS,
			       @AGE_END_LIMIT,@AGE_END_LIMIT_UNITS,@REPEAT,@REPEAT_AGE_VALUE,@REPEAT_AGE_UNITS,@FREQUENCY_LIMIT,
                               @REPEAT_CONDITION, @GROUP_ID,@STATUS,@CDC_CODE,@SCREEN_COMMENTS,@MASTER_REFERENCE_ID)
              
         SET  @currentScreenImmunID = @currentScreenImmunID + 1  
         FETCH NEXT FROM ezEMRxScreenImmunRecords INTO @SCREEN_IMMUN_ID, @RECORD_TYPE,@GENDER,@SUB_GENDER,@DESCRIPTION,@STARTING_AGE_OPERATOR,
	                       @STARTING_AGE_VALUE,@STARTING_AGE_UNITS,@FREQUENCY,@STARTING_AGE_LIMIT_RANGE,@STARTING_AGE_LIMIT_UNITS,
			       @AGE_END_LIMIT,@AGE_END_LIMIT_UNITS,@REPEAT,@REPEAT_AGE_VALUE,@REPEAT_AGE_UNITS,@FREQUENCY_LIMIT,
                               @REPEAT_CONDITION, @GROUP_ID,@STATUS,@CDC_CODE,@SCREEN_COMMENTS,@MASTER_REFERENCE_ID         
      END
       CLOSE ezEMRxScreenImmunRecords
       FETCH NEXT FROM ezEMRxGroups INTO @currentGroupID
     END
    CLOSE ezEMRxGroups   
    DEALLOCATE ezEMRxGroups
    DEALLOCATE ezEMRxScreenImmunRecords   
    SET @updateQuery1 = 'UPDATE EMRIDS SET EZEMRXID = ' + Cast(@currentScreenImmunID as VARCHAR) + ' WHERE PROPERTY_NAME = ''' + @propertyName + ''''
    EXEC (@updateQuery1) 
  --DELETE FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID IS NULL  
END
END
GO