PROCEDURE [repl].[usp_upd_repl_out_application]

ALTER PROCEDURE [repl].[usp_upd_repl_out_application]
(
	@applicationId	int
)
AS
BEGIN
	BEGIN TRAN
	BEGIN TRY

	

	IF NOT EXISTS  (	SELECT	1 
						FROM	[app].[applicationItemGroupChange] 
						WHERE	[applicationid] = @applicationId 
						AND		[status]		= 1
					 )
		BEGIN

			-- getting allergies for updating replication (Returns allergies which combine allergy and animalAllergy to update replication)
			DECLARE @finalAllergyForUpdateReplication VARCHAR(20) = (SELECT [repl].[ftn_getAllergiesForReplication](@applicationid));

			-- For AIFS DE applications use flag get by API
			DECLARE		@insuranceFlag	VARCHAR(10) = (SELECT [repl].[ftn_getInsuranceFlagForReplication](@applicationid))

			-- getting current status
			DECLARE	@currentApplicationStatus	VARCHAR(3) = (SELECT status FROM app.application WHERE applicationId = @applicationId)

			-- Insert or Update data
			UPDATE  JUNOCore_Repl_Pub.dbo.repl_out_application
			SET		status=rv.status,
					onHold=rv.onHold,
					emailAddress=rv.emailAddress,
					firstName=rv.firstName,
					otherNames=rv.otherNames,
					lastName=rv.lastName,
					skypeId=rv.skypeId,
					birthDate=rv.birthDate,
					gender=rv.gender,
					applicationCountryCode=rv.applicationCountryCode,
					flightDateEarliest=rv.flightDateEarliest,
					flightDateLatest=rv.flightDateLatest,
					frequentDriver=rv.frequentDriver,
					prematch=rv.prematch,
					programID=rv.programID,
					returner=rv.returner,
					infantQualified=rv.infantQualified,
					childCareHours=rv.childCareHours,
					departureCity=rv.departureCity,
					smoking=rv.smoking,
					drivingLicense=rv.drivingLicense,
					religion=rv.religion,
					passportExpiry=rv.passportExpiry,
					passportNumber=rv.passportNumber,					
					siblings=rv.siblings,
					drivingLicenseDate=rv.drivingLicenseDate,
					address1=rv.address1,
					address2=rv.address2,
					address3=rv.address3,
					address4=rv.address4,
					postcode=rv.postcode,
					phone1Countrycode=rv.phone1Countrycode,
					phone1AreaCode=rv.phone1AreaCode,
					phone1Number=rv.phone1Number,
					phone1TimeToCall=rv.phone1TimeToCall,
					phone2Countrycode=rv.phone2Countrycode,
					phone2AreaCode=rv.phone2AreaCode,
					phone2Number=rv.phone2Number,
					phone2TimeToCall=rv.phone2TimeToCall,
					emergencyContactName=rv.emergencyContactName,
					emergencyContactEnglish=rv.emergencyContactEnglish,
					emergencyContactCountryCode=rv.emergencyContactCountryCode,
					emergencyContactAreaCode=rv.emergencyContactAreaCode,
					emergencyContactNumber=rv.emergencyContactNumber,
					birthCity=rv.birthCity,
					birthCountryCode=rv.birthCountryCode,
					passportCountry=rv.passportCountry,
					addressCountry=rv.addressCountry,
					willing3mth1yr=rv.willing3month1year,
					willing1to2=rv.willing1to2,
					willing2to6=rv.willing2to6,
					willing6=rv.willing6Plus,
					willing2children=rv.willing2children,
					willingSpecialNeeds=rv.willingSpecialNeeds,
					experience3mth1yr=rv.experience3month1year,
					experience1to2=rv.experience1to2,
					experience2to6=rv.experience2to6,
					experience6=rv.experience6Plus,
					experience2children=rv.experience2children,
					experienceSpecialNeeds=rv.experienceSpecialNeeds,
					allergies = @finalAllergyForUpdateReplication,
					allergydetails=rv.allergydetails,
					specialDiet=rv.specialDiet,
					specialDietDetails=rv.specialDietDetails,
					specialDietGluten=rv.specialDietGluten,
					specialDietKosher=rv.specialDietKosher,
					specialDietLactose=rv.specialDietLactose,
					specialDietOther=rv.specialDietOther,
					specialDietVegan=rv.specialDietVegan,
					specialDietVegetarian=rv.specialDietVegetarian,	
					specialDietHalal = rv.specialDietHalal,	
					language=rv.language,
					otherLanguages=rv.otherLanguages,
					livedAway=rv.livedAway,
					occupationCode=rv.occupationCode,
					academicStatus=rv.academicStatus,
					apexSubCode=rv.apexSubCode,
					apexEducationCode=rv.apexEducationCode,			
					visaStatus=rv.visaStatus,
					visaAppointmentDate=rv.visaAppointmentDate,
					contactCode=rv.contactCode,
					agencyCode=rv.agencyCode,
					--overallGrade=rv.overallGrade,
					documentStateDate=rv.documentStateDate,
					notes=rv.notes,
					insuranceFlag=@insuranceFlag,
					AtlasComment=rv.AtlasComment,
					AtlasCommentInitials=rv.AtlasCommentInitials,
					DateModified =getdate(),
					planForUniversityCollege=rv.planForUniversityCollege,
					profileDescription=rv.profileDescription,
					experiencedisabilityautism=rv.experiencedisabilityautism,
					experiencedisabilitylearning=rv.experiencedisabilitylearning,
					experiencedisabilityphysical=rv.experiencedisabilityphysical,
					experiencedisabilitydetails=rv.experiencedisabilitydetails,
					specialSkills=rv.specialSkills,
					previousApplicationId=rv.previousApplicationId,
					--iTepGradeDescription=rv.iTepGradeDescription,
					nationalityCode=rv.nationalityCode,
					occupationDescription=rv.occupationDescription,
					spokenEnglish=rv.spokenEnglish,
					spokenEnglishDescription=rv.spokenEnglishDescription,
					lastLogin=rv.lastLogin,
					drivingLicenseType=rv.drivingLicenseType,
					iqSuitableForOlderkids = rv.iqSuitableForOlderkids,
					drivingFrequency = rv.drivingFrequency,
					swimmingLevel = rv.swimmingLevel
			FROM	JUNOCore_Repl_Pub.dbo.repl_out_application 
			INNER JOIN app.vw_repl_out_application	AS rv ON repl_out_application.applicationId = rv.applicationId
			WHERE	rv.applicationId		= @applicationId		
	
			--IF status is cancel then record should not be inserted but sometime after TBM, PLC application can be cancelled in this case record will be updated only which is already updated above.
			IF (@@ROWCOUNT = 0 AND @currentApplicationStatus <> 'CAN')
			BEGIN
				INSERT INTO JUNOCore_Repl_Pub.dbo.repl_out_application 
				(
							applicationId,
							firstSentDate,
							status,
							onHold,
							emailAddress,
							firstName,
							otherNames,
							lastName,
							skypeId,
							birthDate,
							gender,
							applicationCountryCode,
							flightDateEarliest,
							flightDateLatest,
							frequentDriver,
							prematch,
							programID,
							returner,
							infantQualified,
							childCareHours,
							departureCity,
							smoking,
							drivingLicense,
							religion,
							passportExpiry,
							passportNumber,							
							siblings,
							drivingLicenseDate,
							address1,
							address2,
							address3,
							address4,
							postcode,
							phone1Countrycode,
							phone1AreaCode,
							phone1Number,
							phone1TimeToCall,
							phone2Countrycode,
							phone2AreaCode,
							phone2Number,
							phone2TimeToCall,
							emergencyContactName,
							emergencyContactEnglish,
							emergencyContactCountryCode,
							emergencyContactAreaCode,
							emergencyContactNumber,
							birthCity,
							birthCountryCode,
							passportCountry,
							addressCountry,
							willing3mth1yr,
							willing1to2,
							willing2to6,
							willing6,
							willing2children,
							willingSpecialNeeds,
							experience3mth1yr,
							experience1to2,
							experience2to6,
							experience6,
							experience2children,
							experienceSpecialNeeds,
							allergies,
							allergydetails,
							specialDiet,
							specialDietDetails,
							specialDietGluten,
							specialDietKosher,
							specialDietLactose,
							specialDietOther,
							specialDietVegan,
							specialDietVegetarian,	
							specialDietHalal,
							language,
							otherLanguages,
							livedAway,
							occupationCode,
							academicStatus,
							apexSubCode,
							apexEducationCode,					
							visaStatus,
							visaAppointmentDate,
							contactCode,
							agencyCode,
							--overallGrade,
							documentStateDate,
							notes,
							insuranceFlag,
							AtlasComment,
							AtlasCommentInitials,
							DateCreated,
							DateModified,
							planForUniversityCollege,
							profileDescription,
							experiencedisabilityautism,
							experiencedisabilitylearning,
							experiencedisabilityphysical,
							experiencedisabilitydetails,
							specialSkills,
							previousApplicationId,
							--iTepGradeDescription,
							nationalityCode,
							occupationDescription,
							spokenEnglish,
							spokenEnglishDescription,
							lastLogin,
							drivingLicenseType,
							iqSuitableForOlderkids,
							drivingFrequency,
							swimmingLevel
				)
				SELECT		applicationId,
							getdate(),
							status,
							onHold,
							emailAddress,
							firstName,
							otherNames,
							lastName,
							skypeId,
							birthDate,
							gender,
							applicationCountryCode,
							flightDateEarliest,
							flightDateLatest,
							frequentDriver,
							prematch,
							programID,
							returner,
							infantQualified,
							childCareHours,
							departureCity,
							smoking,
							drivingLicense,
							religion,
							passportExpiry,
							passportNumber,							
							siblings,
							drivingLicenseDate,
							address1,
							address2,
							address3,
							address4,
							postcode,
							phone1Countrycode,
							phone1AreaCode,
							phone1Number,
							phone1TimeToCall,
							phone2Countrycode,
							phone2AreaCode,
							phone2Number,
							phone2TimeToCall,
							emergencyContactName,
							emergencyContactEnglish,
							emergencyContactCountryCode,
							emergencyContactAreaCode,
							emergencyContactNumber,
							birthCity,
							birthCountryCode,
							passportCountry,
							addressCountry,
							willing3month1year,
							willing1to2,
							willing2to6,
							willing6Plus,
							willing2children,
							willingSpecialNeeds,
							experience3month1year,
							experience1to2,
							experience2to6,
							experience6Plus,
							experience2children,
							experienceSpecialNeeds,
							@finalAllergyForUpdateReplication,
							allergydetails,
							specialDiet,
							specialDietDetails,
							specialDietGluten,
							specialDietKosher,
							specialDietLactose,
							specialDietOther,
							specialDietVegan,
							specialDietVegetarian,
							specialDietHalal,
							language,
							otherLanguages,
							livedAway,
							occupationCode,
							academicStatus,
							apexSubCode,
							apexEducationCode,					
							visaStatus,
							visaAppointmentDate,
							contactCode,
							agencyCode,
							--overallGrade,
							documentStateDate,
							notes,
							@insuranceFlag,
							AtlasComment,
							AtlasCommentInitials,
							getdate(),
							getdate(),
							planForUniversityCollege,
							profileDescription,
							experiencedisabilityautism,
							experiencedisabilitylearning,
							experiencedisabilityphysical,
							experiencedisabilitydetails,
							specialSkills,
							previousApplicationId,
							--iTepGradeDescription,
							nationalityCode,
							occupationDescription,
							spokenEnglish,
							spokenEnglishDescription,
							lastLogin,
							drivingLicenseType,
							iqSuitableForOlderkids,
							drivingFrequency,
							swimmingLevel
				FROM		app.vw_repl_out_application
				WHERE		applicationId			= @applicationId		
			END

			
		END
		COMMIT
	END TRY
	BEGIN CATCH
		DECLARE		@ErrorMessage	nvarchar(max),
					@ErrorSeverity	int,
					@ErrorState		int;
		SELECT		@ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

		ROLLBACK;

		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	END CATCH
END