create PROCEDURE UPDATE_SCREENIMMUNDATA1 AS
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 for SELECT GROUP_ID FROM EMRGROUPMASTER WHERE STATUS = 1
    Declare ezEMRxScreenImmunRecords CURSOR  for  SELECT * FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID = 0 
          ORDER BY SCREEN_IMMUN_ID  

      --Data Migration for group specific screenings and immunizations records.
    SELECT @currentScreenImmunID  INTO ezEMRxID FROM EMRIDS WHERE PROPERTY_NAME = 'SCREEN_IMMUN_ID'   
    OPEN ezEMRxGroups
    FETCH NEXT FROM ezEMRxGroups INTO @currentGroupID
     WHILE @@FETCH_Status = 0    
      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        
         
          
          --updateQuery1 := 'UPDATE EMRSCREENIMMUNLKUP SET SCREEN_IMMUN_ID = ' + currentScreenImmunID + ' WHERE SCREEN_IMMUN_ID = ' + currentScreenImmunMasterID
         SET  @MASTER_REFERENCE_ID = @SCREEN_IMMUN_ID
         SET  @SCREEN_IMMUN_ID = @currentScreenImmunID
         SET  @GROUP_ID = @currentGroupID
          INSERT INTO EMRSCREENIMMUNLKUP 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 @updateQuery1 = 'UPDATE EMRPatientScreeningDetails SET SCREENING_REFERENCE_ID = ' + @currentScreenImmunID + ' WHERE PATIENT_ID IN ( SELECT DISTINCT PATIENT_ID FROM EMRPATIENTSMASTER WHERE LOCATION_ID IN ( SELECT DISTINCT LOCATION_ID FROM EMRLOCATIONMASTER WHERE GROUP_ID = ' + @currentGroupID + '))'
          SET @updateQuery2 = 'UPDATE EMRPatientImmunizationDetails SET IMMUNIZATION_REFERENCE_ID = ' + @currentScreenImmunID + ' WHERE PATIENT_ID IN ( SELECT DISTINCT PATIENT_ID FROM EMRPATIENTSMASTER WHERE LOCATION_ID IN ( SELECT DISTINCT LOCATION_ID FROM EMRLOCATIONMASTER WHERE GROUP_ID = ' + @currentGroupID + '))'
          
          EXEC @updateQuery1
          EXEC @updateQuery2
       
          
         SET  @currentScreenImmunID = @currentScreenImmunID + 1        
      END       
      CLOSE ezEMRxScreenImmunRecords
      
    END 
    CLOSE ezEMRxGroups
    SET @updateQuery1 = 'update emrids set ezemrxid = ' + @seedScreenImmunID + ' where property_name = ''' + @propertyName + ''''
   EXEC  @updateQuery1
  END