CREATE OR ALTER PROCEDURE [Md].[Pr_OfficeLocationSetupUpsert]
(
	@OfficeLocationID BIGINT,
	@LocationName VARCHAR(255),
	@LocationAddress VARCHAR(1000),
	@DivisionTypeID SMALLINT,
	@ProvinceID SMALLINT,
	@RegionID SMALLINT,
	@LicencingBoardID SMALLINT,
	@PaymentClerkUserID BIGINT,
	@AssessorUserID BIGINT,
	@SeniorManagerApproverUserID BIGINT,
	@InspectionCapturerUserID BIGINT,
	@ReferralCapturerUserID BIGINT,
	@HandlesGazetting BIT,
	@GazetteNumberPrefix VARCHAR(10),
	@AdjudicationOfficerUserID BIGINT,
	@AdjudicationSecrataryUserID BIGINT,
	@AdjudicationAgendaEmails VARCHAR(2000),
	@AdjudicationHearingQuorumCount SMALLINT,
	@AdjudicationMeetingQuorumCount SMALLINT,
	@MaxAdjudicationAutoApplicationCount SMALLINT,
	@BusinessRuleBypassApproverUserID  BIGINT,
	@AdjudicationContactName VARCHAR(255),
	@AdjudicationContactTelPhone VARCHAR(14),
	@AdjudicationContactEmail VARCHAR(150),
	@RouteCapturerUserID BIGINT,
	@RouteApproverUserID BIGINT,
	@UserID BIGINT
)
AS
BEGIN
	
	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1 /*User*/
	DECLARE @ItemIDToCheck BIGINT = @UserID
	DECLARE @Action VARCHAR(255) = 'Read'
	DECLARE @UserOfficeLocationID BIGINT, @UserDivisionTypeID SMALLINT, @UserProvinceID SMALLINT, @UserRegionID SMALLINT, @HasAccess BIT
	
	EXEC App.Pr_GetUserLocationAndSecurity @UserID, @EntityIDToCheck, @ItemIDToCheck, @Action, @UserOfficeLocationID OUTPUT, @UserDivisionTypeID OUTPUT, @UserProvinceID OUTPUT, @UserRegionID OUTPUT, @HasAccess OUTPUT

	IF @HasAccess = 0
	BEGIN
		RAISERROR('User location and security error', 16, 1)
		RETURN
	END	

	DECLARE @Error VARCHAR(255) = NULL
   	DECLARE @AuditDescription VARCHAR(MAX)
	DECLARE @MDApprovalJSONDataTableType AS App.MDApprovalJSONDataTableType --Create a type to hold the JSON data
	DECLARE @JSONData NVARCHAR(MAX);
	DECLARE @WorkflowReference VARCHAR(20)

	SET @WorkflowReference = App.Fn_IsMasterDataApprovalRequestInProgress(1037 /*OfficeLocations*/,@OfficeLocationID)

	IF ISNULL(@WorkflowReference, '') <> ''
	BEGIN
		DECLARE @errorMessage VARCHAR(255) = 'There is an existing approval request: ' + @WorkflowReference
		RAISERROR(@errorMessage, 16,1)
		RETURN
	END

	/********************************************************
					Calculate valid action 
	*********************************************************/

	DECLARE @RequestAction VARCHAR(255)
	IF ISNULL(@OfficeLocationID,0) > 0
	BEGIN
		SET @RequestAction =  'Update'
	END
	ELSE BEGIN
		SET @RequestAction = 'Create'
	END
	
	
	IF EXISTS(SELECT * FROM Md.OfficeLocationSetup WHERE OfficeLocationID = @OfficeLocationID) 
	BEGIN
		DECLARE @OldLocationName VARCHAR(MAX),
				@OldLocationAddress VARCHAR(1000),
				@OldDivisionTypeID SMALLINT,
				@OldProvinceID SMALLINT,
				@OldRegionID SMALLINT,
				@OldLicencingBoardID SMALLINT,
				@OldPaymentClerkUserID BIGINT,
				@OldAssessorUserID BIGINT,
				@OldSeniorManagerApproverUserID BIGINT,
				@OldInspectionCapturerUserID BIGINT,
				@OldReferralCapturerUserID BIGINT,
				@OldHandlesGazetting BIT,
				@OldGazetteNumberPrefix VARCHAR(10),
				@OldAdjudicationOfficerUserID BIGINT,
				@OldAdjudicationSecrataryUserID BIGINT,
				@OldAdjudicationAgendaEmails VARCHAR(2000),
				@OldAdjudicationHearingQuorumCount SMALLINT,
				@OldAdjudicationMeetingQuorumCount SMALLINT,
				@OldMaxAdjudicationAutoApplicationCount SMALLINT,
				@OldBusinessRuleBypassApproverUserID  BIGINT,
				@OldAdjudicationContactName VARCHAR(255),
				@OldAdjudicationContactTelPhone VARCHAR(14),
				@OldAdjudicationContactEmail VARCHAR(150),
				@OldRouteCapturerUserID BIGINT,
				@OldRouteApproverUserID BIGINT
		SELECT 
			 @OldLocationName = LocationName
			,@OldLocationAddress = LocationAddress
			,@OldDivisionTypeID = DivisionTypeID
			,@OldProvinceID = ProvinceID
			,@OldRegionID = RegionID
			,@OldLicencingBoardID = LicencingBoardID
			,@OldPaymentClerkUserID = PaymentClerkUserID
			,@OldAssessorUserID = AssessorUserID
			,@OldSeniorManagerApproverUserID = SeniorManagerApproverUserID
			,@OldInspectionCapturerUserID = InspectionCapturerUserID
			,@OldReferralCapturerUserID = ReferralCapturerUserID
			,@OldHandlesGazetting = HandlesGazetting
			,@OldGazetteNumberPrefix = GazetteNumberPrefix
			,@OldAdjudicationOfficerUserID = AdjudicationOfficerUserID
			,@OldAdjudicationSecrataryUserID = AdjudicationSecrataryUserID
			,@OldAdjudicationAgendaEmails = AdjudicationAgendaEmails
			,@OldAdjudicationHearingQuorumCount = AdjudicationHearingQuorumCount
			,@OldAdjudicationMeetingQuorumCount = AdjudicationMeetingQuorumCount
			,@OldMaxAdjudicationAutoApplicationCount = MaxAdjudicationAutoApplicationCount
			,@OldBusinessRuleBypassApproverUserID = BusinessRuleBypassApproverUserID
			,@OldAdjudicationContactName = AdjudicationContactName
			,@OldAdjudicationContactTelPhone = AdjudicationContactTelPhone
			,@OldAdjudicationContactEmail = AdjudicationContactEmail
			,@OldRouteCapturerUserID = RouteCapturerUserID
			,@OldRouteApproverUserID = RouteApproverUserID
		FROM  Md.OfficeLocationSetup
		WHERE OfficeLocationID = @OfficeLocationID

		IF  ISNULL(@OldLocationName , '') LIKE ISNULL(@LocationName, '')
			AND ISNULL(@OldLocationAddress , -1) = ISNULL(@LocationAddress, -1)
			AND ISNULL(@OldDivisionTypeID , -1) = ISNULL(@DivisionTypeID, -1)
			AND ISNULL(@OldProvinceID , -1) = ISNULL(@ProvinceID, -1)
			AND ISNULL(@OldRegionID , -1) = ISNULL(@RegionID, -1)
			AND ISNULL(@OldLicencingBoardID , -1)= ISNULL(@LicencingBoardID, -1)
			AND ISNULL(@OldPaymentClerkUserID, -1) = ISNULL(@PaymentClerkUserID, -1)
			AND ISNULL(@OldAssessorUserID, -1) = ISNULL(@AssessorUserID, -1)
			AND ISNULL(@OldSeniorManagerApproverUserID , -1)  = ISNULL(@SeniorManagerApproverUserID , -1)
			AND ISNULL(@OldInspectionCapturerUserID, -1)  = ISNULL(@InspectionCapturerUserID , -1)
			AND ISNULL(@OldReferralCapturerUserID, -1)  = ISNULL(@ReferralCapturerUserID , -1)
			AND ISNULL(@OldHandlesGazetting, '')  = ISNULL(@HandlesGazetting, '') 
			AND ISNULL(@OldGazetteNumberPrefix, '')  LIKE ISNULL(@GazetteNumberPrefix, '') 
			AND ISNULL(@OldAdjudicationOfficerUserID , -1) = ISNULL(@AdjudicationOfficerUserID, -1)
			AND ISNULL(@OldAdjudicationSecrataryUserID, -1)  = ISNULL(@AdjudicationSecrataryUserID, -1)
			AND ISNULL(@OldAdjudicationAgendaEmails, '')  LIKE ISNULL(@AdjudicationAgendaEmails, '') 
			AND ISNULL(@OldAdjudicationHearingQuorumCount , -1) = ISNULL(@AdjudicationHearingQuorumCount, -1)
			AND ISNULL(@OldAdjudicationMeetingQuorumCount , -1) = ISNULL(@AdjudicationMeetingQuorumCount, -1)
			AND ISNULL(@OldMaxAdjudicationAutoApplicationCount, -1) = ISNULL(@MaxAdjudicationAutoApplicationCount, -1)
			AND ISNULL(@OldBusinessRuleBypassApproverUserID, -1) = ISNULL(@BusinessRuleBypassApproverUserID, -1)
			AND ISNULL(@OldAdjudicationContactName , '')  LIKE ISNULL(@AdjudicationContactName, '') 
			AND ISNULL(@OldAdjudicationContactTelPhone , '')  LIKE ISNULL(@AdjudicationContactTelPhone, '') 
			AND ISNULL(@OldAdjudicationContactEmail, '') LIKE ISNULL(@AdjudicationContactEmail, '')
			AND ISNULL(@OldRouteCapturerUserID, -1)  = ISNULL(@RouteCapturerUserID , -1)
			AND ISNULL(@OldRouteApproverUserID, -1)  = ISNULL(@RouteApproverUserID , -1)
		BEGIN
			SET @errorMessage  = 'No changes have been made'
			RAISERROR(@errorMessage, 16,1)
			RETURN
		END
		
		SET @AuditDescription = 'Office Location ' + @OldLocationName + ' updated.'

		/********************************************************
				Construct the JSON of the changed values
		*********************************************************/
		
			INSERT INTO @MDApprovalJSONDataTableType (
															FieldKey ,
															FieldOldValue ,
															FieldNewValue ,
															FieldOldText ,
															FieldNewText ,
															FieldName ,
															DataType
														)

			SELECT 'LocationName', @OldLocationName, @LocationName, '', '','Office Location','string'

			UNION 

			SELECT 'LocationAddress', @OldLocationAddress, @LocationAddress, '', '','Office Address','string'

			UNION 

			SELECT 'DivisionTypeID', 
			CAST(ISNULL(@OldDivisionTypeID,-1) AS VARCHAR(20)), 
			CAST(@DivisionTypeID AS VARCHAR(20)),
			(SELECT ISNULL(Description,'blank') FROM Md.DivisionTypes WHERE DivisionTypeID = @OldDivisionTypeID),
			(SELECT ISNULL(Description,'blank') FROM Md.DivisionTypes WHERE DivisionTypeID = @DivisionTypeID),
			'Division Type',
			'int'

			UNION 

			SELECT 'ProvinceID', 
			CAST(ISNULL(@OldProvinceID,-1) AS VARCHAR(20)), 
			CAST(@ProvinceID AS VARCHAR(20)),
			(SELECT ISNULL(Description,'blank') FROM Md.Provinces WHERE ProvinceID = @OldProvinceID),
			(SELECT ISNULL(Description,'blank') FROM Md.Provinces WHERE ProvinceID = @ProvinceID),
			'Province',
			'int'

			UNION 

			SELECT 'RegionID', 
			CAST(ISNULL(@OldRegionID,-1) AS VARCHAR(20)), 
			CAST(@RegionID AS VARCHAR(20)),
			(SELECT ISNULL(Description,'blank') FROM Md.Regions WHERE RegionID = @OldRegionID),
			(SELECT ISNULL(Description,'blank') FROM Md.Regions WHERE RegionID = @RegionID),
			'Region',
			'int'

			UNION 

			SELECT 'LicencingBoardID', 
			CAST(ISNULL(@OldLicencingBoardID,-1) AS VARCHAR(20)), 
			CAST(@LicencingBoardID AS VARCHAR(20)),
			(SELECT ISNULL(Description,'blank') FROM Md.LicencingBoards WHERE LicencingBoardID = @OldLicencingBoardID),
			(SELECT ISNULL(Description,'blank') FROM Md.LicencingBoards WHERE LicencingBoardID = @LicencingBoardID),
			'Licencing Board',
			'int'

			UNION 

			SELECT 'PaymentClerkUserID', 
			CAST(ISNULL(@OldPaymentClerkUserID,-1) AS VARCHAR(20)), 
			CAST(@PaymentClerkUserID AS VARCHAR(20)),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @OldPaymentClerkUserID),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @PaymentClerkUserID),
			'Payment Clerk',
			'int'

			UNION 

			SELECT 'AssessorUserID', 
			CAST(ISNULL(@OldAssessorUserID,-1) AS VARCHAR(20)), 
			CAST(@AssessorUserID AS VARCHAR(20)),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @OldAssessorUserID),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @AssessorUserID),
			'Assessor',
			'int'

			UNION 

			SELECT 'SeniorManagerApproverUserID', 
			CAST(ISNULL(@OldSeniorManagerApproverUserID,-1) AS VARCHAR(20)), 
			CAST(@SeniorManagerApproverUserID AS VARCHAR(20)),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @OldSeniorManagerApproverUserID),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @SeniorManagerApproverUserID),
			'Senior Manager Approver',
			'int'

		
			UNION 

			SELECT 'InspectionCapturerUserID', 
			CAST(ISNULL(@OldInspectionCapturerUserID,-1) AS VARCHAR(20)), 
			CAST(@InspectionCapturerUserID AS VARCHAR(20)),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @OldInspectionCapturerUserID),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @InspectionCapturerUserID),
			'Inspection Capturer',
			'int'

			UNION 

			SELECT 'ReferralCapturerUserID', 
			CAST(ISNULL(@OldReferralCapturerUserID,-1) AS VARCHAR(20)), 
			CAST(@ReferralCapturerUserID AS VARCHAR(20)),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @OldReferralCapturerUserID),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @ReferralCapturerUserID),
			'Referral Capturer',
			'int'

			UNION

			SELECT 'HandlesGazetting', 
			CAST(@OldHandlesGazetting AS VARCHAR(20)), 
			CAST(@HandlesGazetting AS VARCHAR(20)), 
			(CASE WHEN @OldHandlesGazetting = 1 THEN 'Yes' ELSE 'No' END) ,
			(CASE WHEN @HandlesGazetting = 1 THEN 'Yes' ELSE 'No' END) ,
			'Handles Gazetting',
			'int'

			UNION

			SELECT 'GazetteNumberPrefix', @OldGazetteNumberPrefix, @GazetteNumberPrefix, '', '','Gazette Number Prefix','string'

			UNION 
		
			SELECT 'AdjudicationOfficerUserID', 
			CAST(ISNULL(@OldAdjudicationOfficerUserID,-1) AS VARCHAR(20)), 
			CAST(@AdjudicationOfficerUserID AS VARCHAR(20)),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @OldAdjudicationOfficerUserID),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @AdjudicationOfficerUserID),
			'Adjudication Officer',
			'int'

			UNION
		
			SELECT 'AdjudicationSecrataryUserID', 
			CAST(ISNULL(@OldAdjudicationSecrataryUserID,-1) AS VARCHAR(20)), 
			CAST(@AdjudicationSecrataryUserID AS VARCHAR(20)),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @OldAdjudicationSecrataryUserID),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @AdjudicationSecrataryUserID),
			'Adjudication Secratary',
			'int'

			UNION
		
			SELECT 'BusinessRuleBypassApproverUserID', 
			CAST(ISNULL(@OldBusinessRuleBypassApproverUserID,-1) AS VARCHAR(20)), 
			CAST(@BusinessRuleBypassApproverUserID AS VARCHAR(20)),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @OldBusinessRuleBypassApproverUserID),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @BusinessRuleBypassApproverUserID),
			'Business Rules Bypass Approver',
			'int'

			UNION 

			SELECT 'AdjudicationAgendaEmails', 
			CAST(ISNULL(@OldAdjudicationAgendaEmails, 'blank') AS VARCHAR(MAX)), 
			CAST(ISNULL(@AdjudicationAgendaEmails , 'blank')  AS VARCHAR(MAX)), 
			'', 
			'',
			'Adjudication Agenda Emails',
			'string'

			UNION 

			SELECT 
			'AdjudicationHearingQuorumCount', 
			CAST(ISNULL(@OldAdjudicationHearingQuorumCount,-1) AS VARCHAR(20)), 
			CAST(ISNULL(@AdjudicationHearingQuorumCount,-1) AS VARCHAR(20)),
			CAST(ISNULL(@OldAdjudicationHearingQuorumCount,-1) AS VARCHAR(20)), 
			CAST(ISNULL(@AdjudicationHearingQuorumCount,-1) AS VARCHAR(20)),
			'Adjudication Hearing Quorum Count',
			'int'
		
			UNION 

			SELECT 'AdjudicationMeetingQuorumCount', 
			CAST( ISNULL(@OldAdjudicationMeetingQuorumCount,-1) AS VARCHAR(20)), 
			CAST( ISNULL(@AdjudicationMeetingQuorumCount,-1) AS VARCHAR(20)), 
			CAST( ISNULL(@OldAdjudicationMeetingQuorumCount,-1) AS VARCHAR(20)), 
			CAST( ISNULL(@AdjudicationMeetingQuorumCount,-1) AS VARCHAR(20)), 
			'Adjudication Meeting Quorum Count',
			'int'

			UNION 

			SELECT 'MaxAdjudicationAutoApplicationCount', 
			CAST( ISNULL(@OldMaxAdjudicationAutoApplicationCount,-1) AS VARCHAR(20)), 
			CAST( ISNULL(@MaxAdjudicationAutoApplicationCount,-1) AS VARCHAR(20)), 
			CAST( ISNULL(@OldMaxAdjudicationAutoApplicationCount,-1) AS VARCHAR(20)), 
			CAST( ISNULL(@MaxAdjudicationAutoApplicationCount,-1) AS VARCHAR(20)), 
			'Max Adjudication Auto Application Count',
			'int'

			UNION 

			SELECT 'AdjudicationContactName', @OldAdjudicationContactName, @AdjudicationContactName, '', '','Adjudication Contact Name','string'

			UNION 

			SELECT 'AdjudicationContactTelPhone', @OldAdjudicationContactTelPhone, @AdjudicationContactTelPhone, '', '','Adjudication Contact Telephone','string'
			
			UNION 

			SELECT 'AdjudicationContactEmail', @OldAdjudicationContactEmail, @AdjudicationContactEmail, '', '','Adjudication Contact Email','string'

			UNION 

			SELECT 'RouteCapturerUserID', 
			CAST(ISNULL(@OldRouteCapturerUserID,-1) AS VARCHAR(20)), 
			CAST(@RouteCapturerUserID AS VARCHAR(20)),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @OldRouteCapturerUserID),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @RouteCapturerUserID),
			'Route Capturer',
			'int'

			UNION 

			SELECT 'RouteApproverUserID', 
			CAST(ISNULL(@OldRouteApproverUserID,-1) AS VARCHAR(20)), 
			CAST(@RouteApproverUserID AS VARCHAR(20)),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @OldRouteApproverUserID),
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @RouteApproverUserID),
			'Route Approver',
			'int'

		

		SET @JSONData = App.Fn_GetMDApprovalJSONData (@MDApprovalJSONDataTableType)

		
		/********************************************************
				Start the approval workflow
		*********************************************************/	
		EXEC App.Pr_MDApprovalRequestCreate 1037 /*OfficeLocations*/,@OfficeLocationID,@AuditDescription, @JSONData ,@RequestAction ,@UserID   

		--UPDATE Md.OfficeLocationSetup
		--SET LocationName = @LocationName
		--  ,LocationAddress = @LocationAddress
		--  ,DivisionTypeID = @DivisionTypeID
		--  ,ProvinceID = @ProvinceID
		--  ,RegionID = @RegionID
		--  ,LicencingBoardID = @LicencingBoardID
		--  ,PaymentClerkUserID = @PaymentClerkUserID
		--  ,AssessorUserID = @AssessorUserID
		--  ,SeniorManagerApproverUserID = @SeniorManagerApproverUserID
		--  ,InspectionCapturerUserID = @InspectionCapturerUserID
		--  ,ReferralCapturerUserID = @ReferralCapturerUserID
		--  ,HandlesGazetting = @HandlesGazetting
		--  ,GazetteNumberPrefix = @GazetteNumberPrefix
		--  ,AdjudicationOfficerUserID = @AdjudicationOfficerUserID
		--  ,AdjudicationSecrataryUserID = @AdjudicationSecrataryUserID
		--  ,AdjudicationAgendaEmails = @AdjudicationAgendaEmails
		--  ,AdjudicationHearingQuorumCount = @AdjudicationHearingQuorumCount
		--  ,AdjudicationMeetingQuorumCount = @AdjudicationMeetingQuorumCount
		--  ,MaxAdjudicationAutoApplicationCount = @MaxAdjudicationAutoApplicationCount
		--  ,BusinessRuleBypassApproverUserID = @BusinessRuleBypassApproverUserID
		--  ,AdjudicationContactName = @AdjudicationContactName
		--  ,AdjudicationContactTelPhone = @AdjudicationContactTelPhone
		--  ,AdjudicationContactEmail = @AdjudicationContactEmail
		--WHERE OfficeLocationID = @OfficeLocationID


	END
	ELSE BEGIN
		
		SET @OfficeLocationID = (SELECT MAX(OfficeLocationID)+1 FROM Security.OfficeLocations)

		INSERT INTO Security.OfficeLocations(OfficeLocationID,Description,IsCancelled, IsNew)
		VALUES(@OfficeLocationID,@LocationName,0, 1)

		INSERT INTO Md.OfficeLocationSetup
		 (
			 OfficeLocationID,
		     LocationName,
		     LocationAddress,
		     DivisionTypeID,
		     ProvinceID,
		     RegionID,
		     LicencingBoardID,
		     PaymentClerkUserID,
		     AssessorUserID,
		     SeniorManagerApproverUserID,
		     InspectionCapturerUserID,
		     ReferralCapturerUserID,
		     HandlesGazetting,
		     GazetteNumberPrefix,
		     AdjudicationOfficerUserID,
		     AdjudicationSecrataryUserID,
		     AdjudicationAgendaEmails,
		     AdjudicationHearingQuorumCount,
		     AdjudicationMeetingQuorumCount,
		     MaxAdjudicationAutoApplicationCount,
			 BusinessRuleBypassApproverUserID,
			 AdjudicationContactName,
			 AdjudicationContactTelPhone,
			 AdjudicationContactEmail,
			 RouteCapturerUserID,
			 RouteApproverUserID
		 )
		 VALUES
		 (
			@OfficeLocationID,
			@LocationName,
			@LocationAddress,
			@DivisionTypeID,
			@ProvinceID,
			@RegionID,
			@LicencingBoardID,
			@PaymentClerkUserID,
			@AssessorUserID,
			@SeniorManagerApproverUserID,
			@InspectionCapturerUserID,
			@ReferralCapturerUserID,
			@HandlesGazetting,
			@GazetteNumberPrefix,
			@AdjudicationOfficerUserID,
			@AdjudicationSecrataryUserID,
			@AdjudicationAgendaEmails,
			@AdjudicationHearingQuorumCount,
			@AdjudicationMeetingQuorumCount,
			@MaxAdjudicationAutoApplicationCount,
			@BusinessRuleBypassApproverUserID,
			@AdjudicationContactName,
			@AdjudicationContactTelPhone,
			@AdjudicationContactEmail,
			@RouteCapturerUserID,
			@RouteApproverUserID
			)

		INSERT INTO Md.OfficeLocationSetupDocumentTemplates(OfficeLocationID)
		VALUES(@OfficeLocationID)

		SET @AuditDescription = 'Office Location ' + @LocationName + ' created.'

		/********************************************************
				Construct the JSON of the changed values
		*********************************************************/
		
		 INSERT INTO @MDApprovalJSONDataTableType (
															FieldKey ,
															FieldOldValue ,
															FieldNewValue ,
															FieldOldText ,
															FieldNewText ,
															FieldName ,
															DataType
														)

			SELECT 'LocationName', 'blank', @LocationName, '', '','Office Location','string'

			UNION 

			SELECT 'DivisionTypeID', 
			CAST(-1 AS VARCHAR(20)),
			CAST(@DivisionTypeID AS VARCHAR(20)),
			'blank',
			(SELECT ISNULL(Description,'blank') FROM Md.DivisionTypes WHERE DivisionTypeID = @DivisionTypeID),
			'Division Type',
			'int'

			UNION 

			SELECT 'ProvinceID', 
			CAST(-1 AS VARCHAR(20)),
			CAST(@ProvinceID AS VARCHAR(20)),
			'blank',
			(SELECT ISNULL(Description,'blank') FROM Md.Provinces WHERE ProvinceID = @ProvinceID),
			'Province',
			'int'

			UNION 

			SELECT 'RegionID', 
			CAST(-1 AS VARCHAR(20)),
			CAST(@RegionID AS VARCHAR(20)),
			'blank',
			(SELECT ISNULL(Description,'blank') FROM Md.Regions WHERE RegionID = @RegionID),
			'Region',
			'int'

			UNION 

			SELECT 'LicencingBoardID', 
			CAST(-1 AS VARCHAR(20)),
			CAST(@LicencingBoardID AS VARCHAR(20)),
			'blank',
			(SELECT ISNULL(Description,'blank') FROM Md.LicencingBoards WHERE LicencingBoardID = @LicencingBoardID),
			'Licencing Board',
			'int'

			UNION 

			SELECT 'PaymentClerkUserID', 
			CAST(-1 AS VARCHAR(20)),
			CAST(@PaymentClerkUserID AS VARCHAR(20)),
			'blank',
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @PaymentClerkUserID),
			'Payment Clerk User',
			'int'

			UNION 

			SELECT 'AssessorUserID', 
			CAST(-1 AS VARCHAR(20)),
			CAST(@AssessorUserID AS VARCHAR(20)),
			'blank',
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @AssessorUserID),
			'Assessor User',
			'int'
			 
			UNION 

			SELECT 'SeniorManagerApproverUserID', 
			CAST(-1 AS VARCHAR(20)),
			CAST(@SeniorManagerApproverUserID AS VARCHAR(20)),
			'blank',
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @SeniorManagerApproverUserID),
			'Senior Manager Approver',
			'int'

		
			UNION 

			SELECT 'InspectionCapturerUserID', 
			CAST(-1 AS VARCHAR(20)),
			CAST(@InspectionCapturerUserID AS VARCHAR(20)),
			'blank',
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @InspectionCapturerUserID),
			'Inspection Capturer',
			'int'

			UNION 

			SELECT 'ReferralCapturerUserID', 
			CAST(-1 AS VARCHAR(20)), 
			CAST(@ReferralCapturerUserID AS VARCHAR(20)),
			'blank',
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @ReferralCapturerUserID),
			'Referral Capturer',
			'int'

			UNION

			SELECT 'HandlesGazetting', 
			'0', 
			CAST(@HandlesGazetting AS VARCHAR(20)), 
			'blank',
			(CASE WHEN @HandlesGazetting = 1 THEN 'Yes' ELSE 'No' END) ,
			'Handles Gazetting',
			'int'

			UNION

			SELECT 'GazetteNumberPrefix', 'blank', @GazetteNumberPrefix, '', '','Gazette Number Prefix','string'

			UNION 
		
			SELECT 'AdjudicationOfficerUserID', 
			CAST(-1 AS VARCHAR(20)), 
			CAST(@AdjudicationOfficerUserID AS VARCHAR(20)),
			'blank',
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @AdjudicationOfficerUserID),
			'Adjudication Officer',
			'int'

			UNION
		
			SELECT 'AdjudicationSecrataryUserID', 
			CAST(-1 AS VARCHAR(20)),  
			CAST(@AdjudicationSecrataryUserID AS VARCHAR(20)),
			'blank',
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @AdjudicationSecrataryUserID),
			'Adjudication Secratary',
			'int'

			UNION
		
			SELECT 'BusinessRuleBypassApproverUserID', 
			CAST(-1 AS VARCHAR(20)), 
			CAST(@BusinessRuleBypassApproverUserID AS VARCHAR(20)),
			'blank',
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @BusinessRuleBypassApproverUserID),
			'Business Rules Bypass Approver',
			'int'

			UNION 


			SELECT 'AdjudicationAgendaEmails', 'blank', CAST(@AdjudicationAgendaEmails AS VARCHAR(MAX)), '', '','Adjudication Agenda Emails','string'

			UNION 

			SELECT 'AdjudicationHearingQuorumCount', 'blank', CAST(@AdjudicationHearingQuorumCount AS VARCHAR(MAX)), '', '','Adjudication Hearing Quorum Count','string'
		
			UNION 

			SELECT 'AdjudicationMeetingQuorumCount', 'blank', CAST(@AdjudicationMeetingQuorumCount AS VARCHAR(MAX)), '', '','Adjudication Meeting Quorum Count','string'

			UNION 

			SELECT 'MaxAdjudicationAutoApplicationCount', 'blank', CAST(@MaxAdjudicationAutoApplicationCount AS VARCHAR(MAX)), '', '','Max Adjudication Auto Application Count','string'

			UNION 

			SELECT 'AdjudicationContactName', 'blank', CAST(@AdjudicationContactName AS VARCHAR(MAX)), '', '','Adjudication Contact Name','string'

			UNION 

			SELECT 'AdjudicationContactTelPhone', 'blank', CAST(@AdjudicationContactTelPhone AS VARCHAR(MAX)), '', '','Adjudication Contact Telephone','string'
			
			UNION 

			SELECT 'AdjudicationContactEmail', 'blank', CAST(@AdjudicationContactEmail AS VARCHAR(MAX)), '', '','Adjudication Contact Email','string'

			UNION 

			SELECT 'RouteCapturerUserID', 
			CAST(-1 AS VARCHAR(20)),
			CAST(@RouteCapturerUserID AS VARCHAR(20)),
			'blank',
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @RouteCapturerUserID),
			'Route Capturer',
			'int'

			UNION 

			SELECT 'RouteApproverUserID', 
			CAST(-1 AS VARCHAR(20)),
			CAST(@RouteApproverUserID AS VARCHAR(20)),
			'blank',
			(SELECT ISNULL(KnownAs,'blank') FROM Security.Users WHERE UserID = @RouteApproverUserID),
			'Route Approver',
			'int'




		SET @JSONData = App.Fn_GetMDApprovalJSONData (@MDApprovalJSONDataTableType)

		
		/********************************************************
				Start the approval workflow
		*********************************************************/	
		EXEC App.Pr_MDApprovalRequestCreate 1037 /*OfficeLocations*/,@OfficeLocationID,@AuditDescription, @JSONData ,@RequestAction ,@UserID   

		
	END

	--   SELECT OfficeLocationID AS OfficeLocationID
	--  ,LocationName
	--  ,LocationAddress
	--  ,DivisionTypeID
	--  ,ProvinceID
	--  ,RegionID
	--  ,LicencingBoardID
	--  ,PaymentClerkUserID
	--  ,AssessorUserID
	--  ,SeniorManagerApproverUserID
	--  ,InspectionCapturerUserID
	--  ,ReferralCapturerUserID
	--  ,HandlesGazetting
	--  ,GazetteNumberPrefix
	--  ,AdjudicationOfficerUserID
	--  ,AdjudicationSecrataryUserID
	--  ,AdjudicationAgendaEmails
	--  ,AdjudicationHearingQuorumCount
	--  ,AdjudicationMeetingQuorumCount
	--  ,MaxAdjudicationAutoApplicationCount
	--  ,DocumentStoreLocationID
	--  ,PaymentTypeID
	--  ,BusinessRuleBypassApproverUserID
	--  ,AdjudicationContactName
	--  ,AdjudicationContactTelPhone
	--  ,AdjudicationContactEmail
	--FROM Md.OfficeLocationSetup
	--WHERE OfficeLocationID = @OfficeLocationID

END
GO