text: -- Script generated by Aqua Data Studio Schema Synchronization for MS SQL Server 2016 on Thu Jan 13 13:52:00 PST 2022
  -- Execute this script on:
  -- 		PIMS_17_00/dbo - This database/schema will be modified
  -- to synchronize it with MS SQL Server 2016:
  -- 		PIMS_19_00/dbo
  
  -- We recommend backing up the database prior to executing the script.
  
  SET XACT_ABORT ON
  GO
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  GO
  BEGIN TRANSACTION
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_LPYPER_A_S_IUD_TR
  PRINT N'Drop trigger dbo.PIMS_LPYPER_A_S_IUD_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_LPYPER_A_S_IUD_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_LPFCST_I_S_U_TR
  PRINT N'Drop trigger dbo.PIMS_LPFCST_I_S_U_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_LPFCST_I_S_U_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_LPFCST_I_S_I_TR
  PRINT N'Drop trigger dbo.PIMS_LPFCST_I_S_I_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_LPFCST_I_S_I_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_LPFCST_A_S_IUD_TR
  PRINT N'Drop trigger dbo.PIMS_LPFCST_A_S_IUD_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_LPFCST_A_S_IUD_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop foreign key constraint dbo.PIM_LSTERM_PIM_LPFCST_FK
  PRINT N'Drop foreign key constraint dbo.PIM_LSTERM_PIM_LPFCST_FK'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT_FORECAST]
  	DROP CONSTRAINT [PIM_LSTERM_PIM_LPFCST_FK]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop foreign key constraint dbo.PIM_LPYPER_PIM_LPFCST_FK
  PRINT N'Drop foreign key constraint dbo.PIM_LPYPER_PIM_LPFCST_FK'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT_FORECAST]
  	DROP CONSTRAINT [PIM_LPYPER_PIM_LPFCST_FK]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop foreign key constraint dbo.PIM_LPSTST_PIM_LPFCST_FK
  PRINT N'Drop foreign key constraint dbo.PIM_LPSTST_PIM_LPFCST_FK'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT_FORECAST]
  	DROP CONSTRAINT [PIM_LPSTST_PIM_LPFCST_FK]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop foreign key constraint dbo.PIM_LPYPER_PIM_LSPYMT_FK
  PRINT N'Drop foreign key constraint dbo.PIM_LPYPER_PIM_LSPYMT_FK'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT]
  	DROP CONSTRAINT [PIM_LPYPER_PIM_LSPYMT_FK]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop foreign key constraint dbo.PIM_SCHLDT_PIM_SECDEP_FK
  PRINT N'Drop foreign key constraint dbo.PIM_SCHLDT_PIM_SECDEP_FK'
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT]
  	DROP CONSTRAINT [PIM_SCHLDT_PIM_SECDEP_FK]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop foreign key constraint dbo.PIM_LSPMTF_PIM_LEASE_FK
  PRINT N'Drop foreign key constraint dbo.PIM_LSPMTF_PIM_LEASE_FK'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE]
  	DROP CONSTRAINT [PIM_LSPMTF_PIM_LEASE_FK]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop index dbo.LPFCST_LEASE_TERM_ID_IDX
  PRINT N'Drop index dbo.LPFCST_LEASE_TERM_ID_IDX'
  GO
  DROP INDEX [dbo].[PIMS_LEASE_PAYMENT_FORECAST].[LPFCST_LEASE_TERM_ID_IDX]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop index dbo.LPFCST_LEASE_PAYMENT_STATUS_TYPE_CODE_IDX
  PRINT N'Drop index dbo.LPFCST_LEASE_PAYMENT_STATUS_TYPE_CODE_IDX'
  GO
  DROP INDEX [dbo].[PIMS_LEASE_PAYMENT_FORECAST].[LPFCST_LEASE_PAYMENT_STATUS_TYPE_CODE_IDX]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop index dbo.LPFCST_LEASE_PAYMENT_PERIOD_ID_IDX
  PRINT N'Drop index dbo.LPFCST_LEASE_PAYMENT_PERIOD_ID_IDX'
  GO
  DROP INDEX [dbo].[PIMS_LEASE_PAYMENT_FORECAST].[LPFCST_LEASE_PAYMENT_PERIOD_ID_IDX]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_SDRTRN_I_S_I_TR
  PRINT N'Drop trigger dbo.PIMS_SDRTRN_I_S_I_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_SDRTRN_I_S_I_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_SECDEP_A_S_IUD_TR
  PRINT N'Drop trigger dbo.PIMS_SECDEP_A_S_IUD_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_SECDEP_A_S_IUD_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop index dbo.SECDEP_SEC_DEP_HOLDER_TYPE_CODE_IDX
  PRINT N'Drop index dbo.SECDEP_SEC_DEP_HOLDER_TYPE_CODE_IDX'
  GO
  DROP INDEX [dbo].[PIMS_SECURITY_DEPOSIT].[SECDEP_SEC_DEP_HOLDER_TYPE_CODE_IDX]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_SDRTRN_I_S_U_TR
  PRINT N'Drop trigger dbo.PIMS_SDRTRN_I_S_U_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_SDRTRN_I_S_U_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_LEASE_I_S_I_TR
  PRINT N'Drop trigger dbo.PIMS_LEASE_I_S_I_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_LEASE_I_S_I_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_LEASE_I_S_U_TR
  PRINT N'Drop trigger dbo.PIMS_LEASE_I_S_U_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_LEASE_I_S_U_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_SDRTRN_A_S_IUD_TR
  PRINT N'Drop trigger dbo.PIMS_SDRTRN_A_S_IUD_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_SDRTRN_A_S_IUD_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_LEASE_A_S_IUD_TR
  PRINT N'Drop trigger dbo.PIMS_LEASE_A_S_IUD_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_LEASE_A_S_IUD_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop index dbo.LEASE_LEASE_PMT_FREQ_TYPE_CODE_IDX
  PRINT N'Drop index dbo.LEASE_LEASE_PMT_FREQ_TYPE_CODE_IDX'
  GO
  DROP INDEX [dbo].[PIMS_LEASE].[LEASE_LEASE_PMT_FREQ_TYPE_CODE_IDX]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_LSPYMT_I_S_I_TR
  PRINT N'Drop trigger dbo.PIMS_LSPYMT_I_S_I_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_LSPYMT_I_S_I_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_LSTERM_A_S_IUD_TR
  PRINT N'Drop trigger dbo.PIMS_LSTERM_A_S_IUD_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_LSTERM_A_S_IUD_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_LSTERM_I_S_U_TR
  PRINT N'Drop trigger dbo.PIMS_LSTERM_I_S_U_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_LSTERM_I_S_U_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_LSPYMT_I_S_U_TR
  PRINT N'Drop trigger dbo.PIMS_LSPYMT_I_S_U_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_LSPYMT_I_S_U_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_SECDEP_I_S_I_TR
  PRINT N'Drop trigger dbo.PIMS_SECDEP_I_S_I_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_SECDEP_I_S_I_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_LSTERM_I_S_I_TR
  PRINT N'Drop trigger dbo.PIMS_LSTERM_I_S_I_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_LSTERM_I_S_I_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_LSPYMT_A_S_IUD_TR
  PRINT N'Drop trigger dbo.PIMS_LSPYMT_A_S_IUD_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_LSPYMT_A_S_IUD_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop index dbo.LSPYMT_LEASE_PAYMENT_PERIOD_ID_IDX
  PRINT N'Drop index dbo.LSPYMT_LEASE_PAYMENT_PERIOD_ID_IDX'
  GO
  DROP INDEX [dbo].[PIMS_LEASE_PAYMENT].[LSPYMT_LEASE_PAYMENT_PERIOD_ID_IDX]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_LPYPER_I_S_U_TR
  PRINT N'Drop trigger dbo.PIMS_LPYPER_I_S_U_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_LPYPER_I_S_U_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_LPYPER_I_S_I_TR
  PRINT N'Drop trigger dbo.PIMS_LPYPER_I_S_I_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_LPYPER_I_S_I_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_SECDEP_I_S_U_TR
  PRINT N'Drop trigger dbo.PIMS_SECDEP_I_S_U_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_SECDEP_I_S_U_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_SCHLDT_I_S_U_TR
  PRINT N'Drop trigger dbo.PIMS_SCHLDT_I_S_U_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_SCHLDT_I_S_U_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop trigger dbo.PIMS_SCHLDT_I_S_I_TR
  PRINT N'Drop trigger dbo.PIMS_SCHLDT_I_S_I_TR'
  GO
  DROP TRIGGER [dbo].[PIMS_SCHLDT_I_S_I_TR]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop unique constraint dbo.PIMS_LPYPER_H_UK
  PRINT N'Drop unique constraint dbo.PIMS_LPYPER_H_UK'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT_PERIOD_HIST]
  	DROP CONSTRAINT [PIMS_LPYPER_H_UK]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop primary key constraint dbo.PIMS_LPYPER_H_PK
  PRINT N'Drop primary key constraint dbo.PIMS_LPYPER_H_PK'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT_PERIOD_HIST]
  	DROP CONSTRAINT [PIMS_LPYPER_H_PK]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop table dbo.PIMS_LEASE_PAYMENT_PERIOD_HIST
  PRINT N'Drop table dbo.PIMS_LEASE_PAYMENT_PERIOD_HIST'
  GO
  DROP TABLE [dbo].[PIMS_LEASE_PAYMENT_PERIOD_HIST]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop unique constraint dbo.PIMS_LPFCST_H_UK
  PRINT N'Drop unique constraint dbo.PIMS_LPFCST_H_UK'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT_FORECAST_HIST]
  	DROP CONSTRAINT [PIMS_LPFCST_H_UK]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop primary key constraint dbo.PIMS_LPFCST_H_PK
  PRINT N'Drop primary key constraint dbo.PIMS_LPFCST_H_PK'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT_FORECAST_HIST]
  	DROP CONSTRAINT [PIMS_LPFCST_H_PK]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop table dbo.PIMS_LEASE_PAYMENT_FORECAST_HIST
  PRINT N'Drop table dbo.PIMS_LEASE_PAYMENT_FORECAST_HIST'
  GO
  DROP TABLE [dbo].[PIMS_LEASE_PAYMENT_FORECAST_HIST]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop primary key constraint dbo.LPFCST_PK
  PRINT N'Drop primary key constraint dbo.LPFCST_PK'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT_FORECAST]
  	DROP CONSTRAINT [LPFCST_PK]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop table dbo.PIMS_LEASE_PAYMENT_FORECAST
  PRINT N'Drop table dbo.PIMS_LEASE_PAYMENT_FORECAST'
  GO
  DROP TABLE [dbo].[PIMS_LEASE_PAYMENT_FORECAST]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop view dbo.PIMS_PROPERTY_BOUNDARY_VW
  PRINT N'Drop view dbo.PIMS_PROPERTY_BOUNDARY_VW'
  GO
  DROP VIEW [dbo].[PIMS_PROPERTY_BOUNDARY_VW]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop primary key constraint dbo.LPYPER_PK
  PRINT N'Drop primary key constraint dbo.LPYPER_PK'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT_PERIOD]
  	DROP CONSTRAINT [LPYPER_PK]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop table dbo.PIMS_LEASE_PAYMENT_PERIOD
  PRINT N'Drop table dbo.PIMS_LEASE_PAYMENT_PERIOD'
  GO
  DROP TABLE [dbo].[PIMS_LEASE_PAYMENT_PERIOD]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop primary key constraint dbo.SCHLDT_PK
  PRINT N'Drop primary key constraint dbo.SCHLDT_PK'
  GO
  ALTER TABLE [dbo].[PIMS_SEC_DEP_HOLDER_TYPE]
  	DROP CONSTRAINT [SCHLDT_PK]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Alter table dbo.PIMS_LEASE
  PRINT N'Alter table dbo.PIMS_LEASE'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE]
  	ADD [REGION_CODE] smallint NULL
  GO
  EXEC sp_addextendedproperty 
  	@name = N'MS_Description', @value = N'MoTI region associated with the lease' , 
  	@level0type = N'Schema', @level0name = N'dbo', 
  	@level1type = N'Table', @level1name = N'PIMS_LEASE', 
  	@level2type = N'Column', @level2name = N'REGION_CODE'
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER TABLE [dbo].[PIMS_LEASE]
  	DROP COLUMN [LEASE_PMT_FREQ_TYPE_CODE], [MOTI_REGION]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create sequence dbo.PIMS_SECURITY_DEPOSIT_HOLDER_ID_SEQ
  PRINT N'Create sequence dbo.PIMS_SECURITY_DEPOSIT_HOLDER_ID_SEQ'
  GO
  CREATE SEQUENCE [dbo].[PIMS_SECURITY_DEPOSIT_HOLDER_ID_SEQ]
  	AS bigint
  	START WITH 1
  	INCREMENT BY 1
  	MINVALUE 1
  	MAXVALUE 2147483647
  	NO CYCLE
  	CACHE 50
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Alter table dbo.PIMS_SECURITY_DEPOSIT
  PRINT N'Alter table dbo.PIMS_SECURITY_DEPOSIT'
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT]
  	ADD [OTHER_DEPOSIT_TYPE_DESC] nvarchar(200) NULL
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT]
  	DROP COLUMN [SEC_DEP_HOLDER_TYPE_CODE], [OTHER_DEP_HOLDER_TYPE_DESC], [ANNUAL_INTEREST_RATE]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create table dbo.PIMS_SECURITY_DEPOSIT_HOLDER
  PRINT N'Create table dbo.PIMS_SECURITY_DEPOSIT_HOLDER'
  GO
  CREATE TABLE [dbo].[PIMS_SECURITY_DEPOSIT_HOLDER]  ( 
  	[SECURITY_DEPOSIT_HOLDER_ID]    	bigint NOT NULL CONSTRAINT [SCDPHL_SECURITY_DEPOSIT_HOLDER_ID_DEF]  DEFAULT (NEXT VALUE FOR [PIMS_SECURITY_DEPOSIT_HOLDER_ID_SEQ]),
  	[SECURITY_DEPOSIT_ID]           	bigint NOT NULL,
  	[PERSON_ID]                     	bigint NULL,
  	[ORGANIZATION_ID]               	bigint NULL,
  	[CONCURRENCY_CONTROL_NUMBER]    	bigint NOT NULL CONSTRAINT [SCDPHL_CONCURRENCY_CONTROL_NUMBER_DEF]  DEFAULT ((1)),
  	[APP_CREATE_TIMESTAMP]          	datetime NOT NULL CONSTRAINT [SCDPHL_APP_CREATE_TIMESTAMP_DEF]  DEFAULT (getutcdate()),
  	[APP_CREATE_USERID]             	nvarchar(30) NOT NULL CONSTRAINT [SCDPHL_APP_CREATE_USERID_DEF]  DEFAULT (user_name()),
  	[APP_CREATE_USER_GUID]          	uniqueidentifier NULL,
  	[APP_CREATE_USER_DIRECTORY]     	nvarchar(30) NOT NULL CONSTRAINT [SCDPHL_APP_CREATE_USER_DIRECTORY_DEF]  DEFAULT (user_name()),
  	[APP_LAST_UPDATE_TIMESTAMP]     	datetime NOT NULL CONSTRAINT [SCDPHL_APP_LAST_UPDATE_TIMESTAMP_DEF]  DEFAULT (getutcdate()),
  	[APP_LAST_UPDATE_USERID]        	nvarchar(30) NOT NULL CONSTRAINT [SCDPHL_APP_LAST_UPDATE_USERID_DEF]  DEFAULT (user_name()),
  	[APP_LAST_UPDATE_USER_GUID]     	uniqueidentifier NULL,
  	[APP_LAST_UPDATE_USER_DIRECTORY]	nvarchar(30) NOT NULL CONSTRAINT [SCDPHL_APP_LAST_UPDATE_USER_DIRECTORY_DEF]  DEFAULT (user_name()),
  	[DB_CREATE_TIMESTAMP]           	datetime NOT NULL CONSTRAINT [SCDPHL_DB_CREATE_TIMESTAMP_DEF]  DEFAULT (getutcdate()),
  	[DB_CREATE_USERID]              	nvarchar(30) NOT NULL CONSTRAINT [SCDPHL_DB_CREATE_USERID_DEF]  DEFAULT (user_name()),
  	[DB_LAST_UPDATE_TIMESTAMP]      	datetime NOT NULL CONSTRAINT [SCDPHL_DB_LAST_UPDATE_TIMESTAMP_DEF]  DEFAULT (getutcdate()),
  	[DB_LAST_UPDATE_USERID]         	nvarchar(30) NOT NULL CONSTRAINT [SCDPHL_DB_LAST_UPDATE_USERID_DEF]  DEFAULT (user_name()),
  	CONSTRAINT [SCDPHL_PK] PRIMARY KEY CLUSTERED([SECURITY_DEPOSIT_HOLDER_ID])
   ON [PRIMARY])
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Alter table dbo.PIMS_LEASE_TERM
  PRINT N'Alter table dbo.PIMS_LEASE_TERM'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_TERM] ALTER COLUMN [LEASE_TERM_STATUS_TYPE_CODE] nvarchar(20) NULL
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_TERM]
  	ADD [LEASE_PMT_FREQ_TYPE_CODE] nvarchar(20) NULL, 
  	[PAYMENT AMOUNT] money NULL, 
  	[PAYMENT_DUE_DATE] nvarchar(200) NULL, 
  	[PAYMENT_NOTE] nvarchar(2000) NULL, 
  	[IS_GST_ELIGIBLE] bit NULL, 
  	[GST_AMOUNT] money NULL, 
  	[IS_TERM_EXERCISED] bit NULL
  GO
  EXEC sp_addextendedproperty 
  	@name = N'MS_Description', @value = N'Foreign key to payment frequency values' , 
  	@level0type = N'Schema', @level0name = N'dbo', 
  	@level1type = N'Table', @level1name = N'PIMS_LEASE_TERM', 
  	@level2type = N'Column', @level2name = N'LEASE_PMT_FREQ_TYPE_CODE'
  GO
  EXEC sp_addextendedproperty 
  	@name = N'MS_Description', @value = N'Agreed-to payment amount (exclusive of GST)' , 
  	@level0type = N'Schema', @level0name = N'dbo', 
  	@level1type = N'Table', @level1name = N'PIMS_LEASE_TERM', 
  	@level2type = N'Column', @level2name = N'PAYMENT AMOUNT'
  GO
  EXEC sp_addextendedproperty 
  	@name = N'MS_Description', @value = N'Anecdotal description of payment due date (e.g. 1st of month, end of month)' , 
  	@level0type = N'Schema', @level0name = N'dbo', 
  	@level1type = N'Table', @level1name = N'PIMS_LEASE_TERM', 
  	@level2type = N'Column', @level2name = N'PAYMENT_DUE_DATE'
  GO
  EXEC sp_addextendedproperty 
  	@name = N'MS_Description', @value = N'Notes regarding payment status for the lease term' , 
  	@level0type = N'Schema', @level0name = N'dbo', 
  	@level1type = N'Table', @level1name = N'PIMS_LEASE_TERM', 
  	@level2type = N'Column', @level2name = N'PAYMENT_NOTE'
  GO
  EXEC sp_addextendedproperty 
  	@name = N'MS_Description', @value = N'Is the lease subject to GST?' , 
  	@level0type = N'Schema', @level0name = N'dbo', 
  	@level1type = N'Table', @level1name = N'PIMS_LEASE_TERM', 
  	@level2type = N'Column', @level2name = N'IS_GST_ELIGIBLE'
  GO
  EXEC sp_addextendedproperty 
  	@name = N'MS_Description', @value = N'Calculated/entered GST portion of the payment.  Can be overridden by the user.' , 
  	@level0type = N'Schema', @level0name = N'dbo', 
  	@level1type = N'Table', @level1name = N'PIMS_LEASE_TERM', 
  	@level2type = N'Column', @level2name = N'GST_AMOUNT'
  GO
  EXEC sp_addextendedproperty 
  	@name = N'MS_Description', @value = N'Has the lease term been exercised?' , 
  	@level0type = N'Schema', @level0name = N'dbo', 
  	@level1type = N'Table', @level1name = N'PIMS_LEASE_TERM', 
  	@level2type = N'Column', @level2name = N'IS_TERM_EXERCISED'
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create sequence dbo.PIMS_SECURITY_DEPOSIT_RETURN_HOLDER_ID_SEQ
  PRINT N'Create sequence dbo.PIMS_SECURITY_DEPOSIT_RETURN_HOLDER_ID_SEQ'
  GO
  CREATE SEQUENCE [dbo].[PIMS_SECURITY_DEPOSIT_RETURN_HOLDER_ID_SEQ]
  	AS bigint
  	START WITH 1
  	INCREMENT BY 1
  	MINVALUE 1
  	MAXVALUE 2147483647
  	NO CYCLE
  	CACHE 50
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Alter table dbo.PIMS_SECURITY_DEPOSIT_RETURN
  PRINT N'Alter table dbo.PIMS_SECURITY_DEPOSIT_RETURN'
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT_RETURN]
  	ADD [SECURITY_DEPOSIT_ID] bigint NULL
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT_RETURN]
  	DROP COLUMN [DEPOSIT_TOTAL], [CHEQUE_NUMBER]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create table dbo.PIMS_SECURITY_DEPOSIT_RETURN_HOLDER
  PRINT N'Create table dbo.PIMS_SECURITY_DEPOSIT_RETURN_HOLDER'
  GO
  CREATE TABLE [dbo].[PIMS_SECURITY_DEPOSIT_RETURN_HOLDER]  ( 
  	[SECURITY_DEPOSIT_RETURN_HOLDER_ID]	bigint NOT NULL CONSTRAINT [SCDPRH_SECURITY_DEPOSIT_RETURN_HOLDER_ID_DEF]  DEFAULT (NEXT VALUE FOR [PIMS_SECURITY_DEPOSIT_RETURN_HOLDER_ID_SEQ]),
  	[SECURITY_DEPOSIT_RETURN_ID]       	bigint NOT NULL,
  	[PERSON_ID]                        	bigint NULL,
  	[ORGANIZATION_ID]                  	bigint NULL,
  	[CONCURRENCY_CONTROL_NUMBER]       	bigint NOT NULL CONSTRAINT [SCDPRH_CONCURRENCY_CONTROL_NUMBER_DEF]  DEFAULT ((1)),
  	[APP_CREATE_TIMESTAMP]             	datetime NOT NULL CONSTRAINT [SCDPRH_APP_CREATE_TIMESTAMP_DEF]  DEFAULT (getutcdate()),
  	[APP_CREATE_USERID]                	nvarchar(30) NOT NULL CONSTRAINT [SCDPRH_APP_CREATE_USERID_DEF]  DEFAULT (user_name()),
  	[APP_CREATE_USER_GUID]             	uniqueidentifier NULL,
  	[APP_CREATE_USER_DIRECTORY]        	nvarchar(30) NOT NULL CONSTRAINT [SCDPRH_APP_CREATE_USER_DIRECTORY_DEF]  DEFAULT (user_name()),
  	[APP_LAST_UPDATE_USER_GUID]        	uniqueidentifier NULL,
  	[APP_LAST_UPDATE_USERID]           	nvarchar(30) NOT NULL CONSTRAINT [SCDPRH_APP_LAST_UPDATE_USERID_DEF]  DEFAULT (user_name()),
  	[APP_LAST_UPDATE_TIMESTAMP]        	datetime NOT NULL CONSTRAINT [SCDPRH_APP_LAST_UPDATE_TIMESTAMP_DEF]  DEFAULT (getutcdate()),
  	[APP_LAST_UPDATE_USER_DIRECTORY]   	nvarchar(30) NOT NULL CONSTRAINT [SCDPRH_APP_LAST_UPDATE_USER_DIRECTORY_DEF]  DEFAULT (user_name()),
  	[DB_CREATE_TIMESTAMP]              	datetime NOT NULL CONSTRAINT [SCDPRH_DB_CREATE_TIMESTAMP_DEF]  DEFAULT (getutcdate()),
  	[DB_CREATE_USERID]                 	nvarchar(30) NOT NULL CONSTRAINT [SCDPRH_DB_CREATE_USERID_DEF]  DEFAULT (user_name()),
  	[DB_LAST_UPDATE_TIMESTAMP]         	datetime NOT NULL CONSTRAINT [SCDPRH_DB_LAST_UPDATE_TIMESTAMP_DEF]  DEFAULT (getutcdate()),
  	[DB_LAST_UPDATE_USERID]            	nvarchar(30) NOT NULL CONSTRAINT [SCDPRH_DB_LAST_UPDATE_USERID_DEF]  DEFAULT (user_name()),
  	CONSTRAINT [SCDPRH_PK] PRIMARY KEY CLUSTERED([SECURITY_DEPOSIT_RETURN_HOLDER_ID])
   ON [PRIMARY])
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Alter table dbo.PIMS_LEASE_PAYMENT
  PRINT N'Alter table dbo.PIMS_LEASE_PAYMENT'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT] ADD DEFAULT getutcdate() FOR [PAYMENT_RECEIVED_DATE]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  EXEC sp_addextendedproperty 
  	@name = N'MS_Description', @value = N'Date the payment was received or sent' , 
  	@level0type = N'Schema', @level0name = N'dbo', 
  	@level1type = N'Table', @level1name = N'PIMS_LEASE_PAYMENT', 
  	@level2type = N'Column', @level2name = N'PAYMENT_RECEIVED_DATE'
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT] ADD DEFAULT 0 FOR [PAYMENT_AMOUNT_PRE_TAX]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  EXEC sp_addextendedproperty 
  	@name = N'MS_Description', @value = N'Principal amount of the payment before applicable taxes' , 
  	@level0type = N'Schema', @level0name = N'dbo', 
  	@level1type = N'Table', @level1name = N'PIMS_LEASE_PAYMENT', 
  	@level2type = N'Column', @level2name = N'PAYMENT_AMOUNT_PRE_TAX'
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  EXEC sp_addextendedproperty 
  	@name = N'MS_Description', @value = N'PST owing on payment if applicable' , 
  	@level0type = N'Schema', @level0name = N'dbo', 
  	@level1type = N'Table', @level1name = N'PIMS_LEASE_PAYMENT', 
  	@level2type = N'Column', @level2name = N'PAYMENT_AMOUNT_PST'
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT] ALTER COLUMN [PAYMENT_AMOUNT_GST] money NULL
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  EXEC sp_addextendedproperty 
  	@name = N'MS_Description', @value = N'GST owing on payment if applicable' , 
  	@level0type = N'Schema', @level0name = N'dbo', 
  	@level1type = N'Table', @level1name = N'PIMS_LEASE_PAYMENT', 
  	@level2type = N'Column', @level2name = N'PAYMENT_AMOUNT_GST'
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT] ADD DEFAULT 0 FOR [PAYMENT_AMOUNT_TOTAL]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  EXEC sp_addextendedproperty 
  	@name = N'MS_Description', @value = N'Total amount of payment including principal plus all applicable taxes' , 
  	@level0type = N'Schema', @level0name = N'dbo', 
  	@level1type = N'Table', @level1name = N'PIMS_LEASE_PAYMENT', 
  	@level2type = N'Column', @level2name = N'PAYMENT_AMOUNT_TOTAL'
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  EXEC sp_addextendedproperty 
  	@name = N'MS_Description', @value = N'Notes regarding this payment' , 
  	@level0type = N'Schema', @level0name = N'dbo', 
  	@level1type = N'Table', @level1name = N'PIMS_LEASE_PAYMENT', 
  	@level2type = N'Column', @level2name = N'NOTE'
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT]
  	ADD [LEASE_PAYMENT_STATUS_TYPE_CODE] nvarchar(20) NULL
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT]
  	DROP COLUMN [LEASE_PAYMENT_PERIOD_ID]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Alter table dbo.PIMS_LEASE_PAYMENT_HIST
  PRINT N'Alter table dbo.PIMS_LEASE_PAYMENT_HIST'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT_HIST] ALTER COLUMN [PAYMENT_AMOUNT_GST] money NULL
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT_HIST]
  	ADD [LEASE_PAYMENT_STATUS_TYPE_CODE] nvarchar(20) NULL
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT_HIST]
  	DROP COLUMN [LEASE_PAYMENT_PERIOD_ID]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create sequence dbo.PIMS_SECURITY_DEPOSIT_HOLDER_H_ID_SEQ
  PRINT N'Create sequence dbo.PIMS_SECURITY_DEPOSIT_HOLDER_H_ID_SEQ'
  GO
  CREATE SEQUENCE [dbo].[PIMS_SECURITY_DEPOSIT_HOLDER_H_ID_SEQ]
  	AS bigint
  	START WITH 1
  	INCREMENT BY 1
  	MINVALUE 1
  	MAXVALUE 2147483647
  	NO CYCLE
  	CACHE 50
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create table dbo.PIMS_SECURITY_DEPOSIT_HOLDER_HIST
  PRINT N'Create table dbo.PIMS_SECURITY_DEPOSIT_HOLDER_HIST'
  GO
  CREATE TABLE [dbo].[PIMS_SECURITY_DEPOSIT_HOLDER_HIST]  ( 
  	[_SECURITY_DEPOSIT_HOLDER_HIST_ID]	bigint NOT NULL DEFAULT (NEXT VALUE FOR [PIMS_SECURITY_DEPOSIT_HOLDER_H_ID_SEQ]),
  	[EFFECTIVE_DATE_HIST]             	datetime NOT NULL DEFAULT (getutcdate()),
  	[END_DATE_HIST]                   	datetime NULL,
  	[SECURITY_DEPOSIT_HOLDER_ID]      	bigint NOT NULL,
  	[SECURITY_DEPOSIT_ID]             	bigint NOT NULL,
  	[PERSON_ID]                       	bigint NULL,
  	[ORGANIZATION_ID]                 	bigint NULL,
  	[CONCURRENCY_CONTROL_NUMBER]      	bigint NOT NULL,
  	[APP_CREATE_TIMESTAMP]            	datetime NOT NULL,
  	[APP_CREATE_USERID]               	nvarchar(30) NOT NULL,
  	[APP_CREATE_USER_GUID]            	uniqueidentifier NULL,
  	[APP_CREATE_USER_DIRECTORY]       	nvarchar(30) NOT NULL,
  	[APP_LAST_UPDATE_TIMESTAMP]       	datetime NOT NULL,
  	[APP_LAST_UPDATE_USERID]          	nvarchar(30) NOT NULL,
  	[APP_LAST_UPDATE_USER_GUID]       	uniqueidentifier NULL,
  	[APP_LAST_UPDATE_USER_DIRECTORY]  	nvarchar(30) NOT NULL,
  	[DB_CREATE_TIMESTAMP]             	datetime NOT NULL,
  	[DB_CREATE_USERID]                	nvarchar(30) NOT NULL,
  	[DB_LAST_UPDATE_TIMESTAMP]        	datetime NOT NULL,
  	[DB_LAST_UPDATE_USERID]           	nvarchar(30) NOT NULL,
  	CONSTRAINT [PIMS_SCDPHL_H_PK] PRIMARY KEY CLUSTERED([_SECURITY_DEPOSIT_HOLDER_HIST_ID])
   ON [PRIMARY])
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create unique constraint dbo.PIMS_SCDPHL_H_UK
  PRINT N'Create unique constraint dbo.PIMS_SCDPHL_H_UK'
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT_HOLDER_HIST]
  	ADD CONSTRAINT [PIMS_SCDPHL_H_UK]
  	UNIQUE ([_SECURITY_DEPOSIT_HOLDER_HIST_ID], [END_DATE_HIST]) 
  	WITH (
  		DATA_COMPRESSION = NONE
  	) ON [PRIMARY]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Alter table dbo.PIMS_LEASE_TERM_HIST
  PRINT N'Alter table dbo.PIMS_LEASE_TERM_HIST'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_TERM_HIST] ALTER COLUMN [LEASE_TERM_STATUS_TYPE_CODE] nvarchar(20) NULL
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_TERM_HIST]
  	ADD [LEASE_PMT_FREQ_TYPE_CODE] nvarchar(20) NULL, 
  	[PAYMENT AMOUNT] money NULL, 
  	[PAYMENT_DUE_DATE] nvarchar(200) NULL, 
  	[PAYMENT_NOTE] nvarchar(2000) NULL, 
  	[IS_GST_ELIGIBLE] bit NULL, 
  	[GST_AMOUNT] money NULL, 
  	[IS_TERM_EXERCISED] bit NULL
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Alter table dbo.PIMS_LEASE_HIST
  PRINT N'Alter table dbo.PIMS_LEASE_HIST'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_HIST]
  	ADD [REGION_CODE] smallint NULL
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_HIST]
  	DROP COLUMN [LEASE_PMT_FREQ_TYPE_CODE], [MOTI_REGION]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Alter table dbo.PIMS_SECURITY_DEPOSIT_RETURN_HIST
  PRINT N'Alter table dbo.PIMS_SECURITY_DEPOSIT_RETURN_HIST'
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT_RETURN_HIST]
  	ADD [SECURITY_DEPOSIT_ID] bigint NULL
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT_RETURN_HIST]
  	DROP COLUMN [DEPOSIT_TOTAL], [CHEQUE_NUMBER]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create sequence dbo.PIMS_SECURITY_DEPOSIT_RETURN_HOLDER_H_ID_SEQ
  PRINT N'Create sequence dbo.PIMS_SECURITY_DEPOSIT_RETURN_HOLDER_H_ID_SEQ'
  GO
  CREATE SEQUENCE [dbo].[PIMS_SECURITY_DEPOSIT_RETURN_HOLDER_H_ID_SEQ]
  	AS bigint
  	START WITH 1
  	INCREMENT BY 1
  	MINVALUE 1
  	MAXVALUE 2147483647
  	NO CYCLE
  	CACHE 50
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create table dbo.PIMS_SECURITY_DEPOSIT_RETURN_HOLDER_HIST
  PRINT N'Create table dbo.PIMS_SECURITY_DEPOSIT_RETURN_HOLDER_HIST'
  GO
  CREATE TABLE [dbo].[PIMS_SECURITY_DEPOSIT_RETURN_HOLDER_HIST]  ( 
  	[_SECURITY_DEPOSIT_RETURN_HOLDER_HIST_ID]	bigint NOT NULL DEFAULT (NEXT VALUE FOR [PIMS_SECURITY_DEPOSIT_RETURN_HOLDER_H_ID_SEQ]),
  	[EFFECTIVE_DATE_HIST]                    	datetime NOT NULL DEFAULT (getutcdate()),
  	[END_DATE_HIST]                          	datetime NULL,
  	[SECURITY_DEPOSIT_RETURN_HOLDER_ID]      	bigint NOT NULL,
  	[SECURITY_DEPOSIT_RETURN_ID]             	bigint NOT NULL,
  	[PERSON_ID]                              	bigint NULL,
  	[ORGANIZATION_ID]                        	bigint NULL,
  	[CONCURRENCY_CONTROL_NUMBER]             	bigint NOT NULL,
  	[APP_CREATE_TIMESTAMP]                   	datetime NOT NULL,
  	[APP_CREATE_USERID]                      	nvarchar(30) NOT NULL,
  	[APP_CREATE_USER_GUID]                   	uniqueidentifier NULL,
  	[APP_CREATE_USER_DIRECTORY]              	nvarchar(30) NOT NULL,
  	[APP_LAST_UPDATE_USER_GUID]              	uniqueidentifier NULL,
  	[APP_LAST_UPDATE_USERID]                 	nvarchar(30) NOT NULL,
  	[APP_LAST_UPDATE_TIMESTAMP]              	datetime NOT NULL,
  	[APP_LAST_UPDATE_USER_DIRECTORY]         	nvarchar(30) NOT NULL,
  	[DB_CREATE_TIMESTAMP]                    	datetime NOT NULL,
  	[DB_CREATE_USERID]                       	nvarchar(30) NOT NULL,
  	[DB_LAST_UPDATE_TIMESTAMP]               	datetime NOT NULL,
  	[DB_LAST_UPDATE_USERID]                  	nvarchar(30) NOT NULL,
  	CONSTRAINT [PIMS_SCDPRH_H_PK] PRIMARY KEY CLUSTERED([_SECURITY_DEPOSIT_RETURN_HOLDER_HIST_ID])
   ON [PRIMARY])
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create unique constraint dbo.PIMS_SCDPRH_H_UK
  PRINT N'Create unique constraint dbo.PIMS_SCDPRH_H_UK'
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT_RETURN_HOLDER_HIST]
  	ADD CONSTRAINT [PIMS_SCDPRH_H_UK]
  	UNIQUE ([_SECURITY_DEPOSIT_RETURN_HOLDER_HIST_ID], [END_DATE_HIST]) 
  	WITH (
  		DATA_COMPRESSION = NONE
  	) ON [PRIMARY]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Alter table dbo.PIMS_SECURITY_DEPOSIT_HIST
  PRINT N'Alter table dbo.PIMS_SECURITY_DEPOSIT_HIST'
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT_HIST]
  	ADD [OTHER_DEPOSIT_TYPE_DESC] nvarchar(200) NULL
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT_HIST]
  	DROP COLUMN [SEC_DEP_HOLDER_TYPE_CODE], [OTHER_DEP_HOLDER_TYPE_DESC], [ANNUAL_INTEREST_RATE]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create view dbo.PIMS_PROPERTY_BOUNDARY_VW
  PRINT N'Create view dbo.PIMS_PROPERTY_BOUNDARY_VW'
  GO
  /* ---------------------------------------------------------------------- */
  /* Add views                                                              */
  /* ---------------------------------------------------------------------- */
  
  CREATE VIEW [dbo].[PIMS_PROPERTY_BOUNDARY_VW] AS
  SELECT PROP.PROPERTY_ID
       , PROP.PID
       , RIGHT('000000000' + CAST(PROP.PID AS VARCHAR(9)), 9) AS PID_PADDED
       , PROP.PIN
       , PROP.PROPERTY_TYPE_CODE
       , PROP.PROPERTY_STATUS_TYPE_CODE
       , PROP.PROPERTY_DATA_SOURCE_TYPE_CODE
       , PROP.PROPERTY_DATA_SOURCE_EFFECTIVE_DATE
       , PROP.PROPERTY_CLASSIFICATION_TYPE_CODE
       , PROP.PROPERTY_TENURE_TYPE_CODE
       , ADDR.STREET_ADDRESS_1
       , ADDR.STREET_ADDRESS_2
       , ADDR.STREET_ADDRESS_3
       , ADDR.MUNICIPALITY_NAME
       , ADDR.POSTAL_CODE
       , PROV.PROVINCE_STATE_CODE
       , PROV.DESCRIPTION AS PROVINCE_NAME
       , CNTY.COUNTRY_CODE
       , CNTY.DESCRIPTION AS COUNTRY_NAME
       , PROP.NAME
       , PROP.DESCRIPTION
       , PROP.ADDRESS_ID
       , PROP.REGION_CODE
       , PROP.DISTRICT_CODE
       , PROP.BOUNDARY AS GEOMETRY
       , PROP.PROPERTY_AREA_UNIT_TYPE_CODE
       , PROP.LAND_AREA
       , PROP.LAND_LEGAL_DESCRIPTION
       , PROP.ENCUMBRANCE_REASON
       , PROP.IS_SENSITIVE
       , PROP.IS_OWNED
       , PROP.IS_PROPERTY_OF_INTEREST
       , PROP.IS_VISIBLE_TO_OTHER_AGENCIES
       , PROP.ZONING,ZONING_POTENTIAL
  FROM   PIMS_PROPERTY       PROP                                                    INNER JOIN
         PIMS_ADDRESS        ADDR ON ADDR.ADDRESS_ID        = PROP.ADDRESS_ID        INNER JOIN
         PIMS_PROVINCE_STATE PROV ON PROV.PROVINCE_STATE_ID = ADDR.PROVINCE_STATE_ID INNER JOIN
         PIMS_COUNTRY        CNTY ON CNTY.COUNTRY_ID        = ADDR.COUNTRY_ID
  WHERE  PROP.BOUNDARY IS NOT NULL
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create index dbo.LEASE_REGION_CODE_IDX
  PRINT N'Create index dbo.LEASE_REGION_CODE_IDX'
  GO
  CREATE NONCLUSTERED INDEX [LEASE_REGION_CODE_IDX]
  	ON [dbo].[PIMS_LEASE]([REGION_CODE])
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create index dbo.SCDPHL_ORGANIZATION_ID_IDX
  PRINT N'Create index dbo.SCDPHL_ORGANIZATION_ID_IDX'
  GO
  CREATE NONCLUSTERED INDEX [SCDPHL_ORGANIZATION_ID_IDX]
  	ON [dbo].[PIMS_SECURITY_DEPOSIT_HOLDER]([ORGANIZATION_ID])
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create index dbo.LSTERM_LEASE_PMT_FREQ_TYPE_CODE_IDX
  PRINT N'Create index dbo.LSTERM_LEASE_PMT_FREQ_TYPE_CODE_IDX'
  GO
  CREATE NONCLUSTERED INDEX [LSTERM_LEASE_PMT_FREQ_TYPE_CODE_IDX]
  	ON [dbo].[PIMS_LEASE_TERM]([LEASE_PMT_FREQ_TYPE_CODE])
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create index dbo.SCDPRH_SECURITY_DEPOSIT_RETURN_ID_IDX
  PRINT N'Create index dbo.SCDPRH_SECURITY_DEPOSIT_RETURN_ID_IDX'
  GO
  CREATE NONCLUSTERED INDEX [SCDPRH_SECURITY_DEPOSIT_RETURN_ID_IDX]
  	ON [dbo].[PIMS_SECURITY_DEPOSIT_RETURN_HOLDER]([SECURITY_DEPOSIT_RETURN_ID])
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create index dbo.SCDPHL_SECURITY_DEPOSIT_ID_IDX
  PRINT N'Create index dbo.SCDPHL_SECURITY_DEPOSIT_ID_IDX'
  GO
  CREATE NONCLUSTERED INDEX [SCDPHL_SECURITY_DEPOSIT_ID_IDX]
  	ON [dbo].[PIMS_SECURITY_DEPOSIT_HOLDER]([SECURITY_DEPOSIT_ID])
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create index dbo.SDRTRN_SECURITY_DEPOSIT_ID_IDX
  PRINT N'Create index dbo.SDRTRN_SECURITY_DEPOSIT_ID_IDX'
  GO
  CREATE NONCLUSTERED INDEX [SDRTRN_SECURITY_DEPOSIT_ID_IDX]
  	ON [dbo].[PIMS_SECURITY_DEPOSIT_RETURN]([SECURITY_DEPOSIT_ID])
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create index dbo.SCDPHL_PERSON_ID_IDX
  PRINT N'Create index dbo.SCDPHL_PERSON_ID_IDX'
  GO
  CREATE NONCLUSTERED INDEX [SCDPHL_PERSON_ID_IDX]
  	ON [dbo].[PIMS_SECURITY_DEPOSIT_HOLDER]([PERSON_ID])
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create index dbo.SCDPRH_ORGANIZATION_ID_IDX
  PRINT N'Create index dbo.SCDPRH_ORGANIZATION_ID_IDX'
  GO
  CREATE NONCLUSTERED INDEX [SCDPRH_ORGANIZATION_ID_IDX]
  	ON [dbo].[PIMS_SECURITY_DEPOSIT_RETURN_HOLDER]([ORGANIZATION_ID])
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create index dbo.SCDPRH_PERSON_ID_IDX
  PRINT N'Create index dbo.SCDPRH_PERSON_ID_IDX'
  GO
  CREATE NONCLUSTERED INDEX [SCDPRH_PERSON_ID_IDX]
  	ON [dbo].[PIMS_SECURITY_DEPOSIT_RETURN_HOLDER]([PERSON_ID])
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_SECDEP_I_S_U_TR
  PRINT N'Create trigger dbo.PIMS_SECDEP_I_S_U_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_SECDEP_I_S_U_TR] ON PIMS_SECURITY_DEPOSIT INSTEAD OF UPDATE AS
  SET NOCOUNT ON
  BEGIN TRY
    IF NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- validate concurrency control
    if exists (select 1 from inserted, deleted where inserted.CONCURRENCY_CONTROL_NUMBER != deleted.CONCURRENCY_CONTROL_NUMBER+1 AND inserted.SECURITY_DEPOSIT_ID = deleted.SECURITY_DEPOSIT_ID)
      raiserror('CONCURRENCY FAILURE.',16,1)
  
  
    -- update statement
    update PIMS_SECURITY_DEPOSIT
      set "SECURITY_DEPOSIT_ID" = inserted."SECURITY_DEPOSIT_ID",
        "LEASE_ID" = inserted."LEASE_ID",
        "SECURITY_DEPOSIT_TYPE_CODE" = inserted."SECURITY_DEPOSIT_TYPE_CODE",
        "OTHER_DEPOSIT_TYPE_DESC" = inserted."OTHER_DEPOSIT_TYPE_DESC",
        "DESCRIPTION" = inserted."DESCRIPTION",
        "AMOUNT_PAID" = inserted."AMOUNT_PAID",
        "DEPOSIT_DATE" = inserted."DEPOSIT_DATE",
        "CONCURRENCY_CONTROL_NUMBER" = inserted."CONCURRENCY_CONTROL_NUMBER",
        "APP_LAST_UPDATE_TIMESTAMP" = inserted."APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID" = inserted."APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID" = inserted."APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY" = inserted."APP_LAST_UPDATE_USER_DIRECTORY"
      , DB_LAST_UPDATE_TIMESTAMP = getutcdate()
      , DB_LAST_UPDATE_USERID = user_name()
      from PIMS_SECURITY_DEPOSIT
      inner join inserted
      on (PIMS_SECURITY_DEPOSIT.SECURITY_DEPOSIT_ID = inserted.SECURITY_DEPOSIT_ID);
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_LSPYMT_A_S_IUD_TR
  PRINT N'Create trigger dbo.PIMS_LSPYMT_A_S_IUD_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_LSPYMT_A_S_IUD_TR] ON PIMS_LEASE_PAYMENT FOR INSERT, UPDATE, DELETE AS
  SET NOCOUNT ON
  BEGIN TRY
  DECLARE @curr_date datetime;
  SET @curr_date = getutcdate();
    IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- historical
    IF EXISTS(SELECT * FROM deleted)
      update PIMS_LEASE_PAYMENT_HIST set END_DATE_HIST = @curr_date where LEASE_PAYMENT_ID in (select LEASE_PAYMENT_ID from deleted) and END_DATE_HIST is null;
    
    IF EXISTS(SELECT * FROM inserted)
      insert into PIMS_LEASE_PAYMENT_HIST ([LEASE_PAYMENT_ID], [LEASE_TERM_ID], [LEASE_PAYMENT_METHOD_TYPE_CODE], [LEASE_PAYMENT_STATUS_TYPE_CODE], [PAYMENT_RECEIVED_DATE], [PAYMENT_AMOUNT_PRE_TAX], [PAYMENT_AMOUNT_PST], [PAYMENT_AMOUNT_GST], [PAYMENT_AMOUNT_TOTAL], [NOTE], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_TIMESTAMP], [APP_CREATE_USERID], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_CREATE_TIMESTAMP], [DB_CREATE_USERID], [DB_LAST_UPDATE_TIMESTAMP], [DB_LAST_UPDATE_USERID], _LEASE_PAYMENT_HIST_ID, END_DATE_HIST, EFFECTIVE_DATE_HIST)
        select [LEASE_PAYMENT_ID], [LEASE_TERM_ID], [LEASE_PAYMENT_METHOD_TYPE_CODE], [LEASE_PAYMENT_STATUS_TYPE_CODE], [PAYMENT_RECEIVED_DATE], [PAYMENT_AMOUNT_PRE_TAX], [PAYMENT_AMOUNT_PST], [PAYMENT_AMOUNT_GST], [PAYMENT_AMOUNT_TOTAL], [NOTE], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_TIMESTAMP], [APP_CREATE_USERID], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_CREATE_TIMESTAMP], [DB_CREATE_USERID], [DB_LAST_UPDATE_TIMESTAMP], [DB_LAST_UPDATE_USERID], (next value for [dbo].[PIMS_LEASE_PAYMENT_H_ID_SEQ]) as [_LEASE_PAYMENT_HIST_ID], null as [END_DATE_HIST], @curr_date as [EFFECTIVE_DATE_HIST] from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_LSTERM_I_S_I_TR
  PRINT N'Create trigger dbo.PIMS_LSTERM_I_S_I_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_LSTERM_I_S_I_TR] ON PIMS_LEASE_TERM INSTEAD OF INSERT AS
  SET NOCOUNT ON
  BEGIN TRY
    IF NOT EXISTS(SELECT * FROM inserted) 
      RETURN;
  
    
    insert into PIMS_LEASE_TERM ("LEASE_TERM_ID",
        "LEASE_ID",
        "LEASE_TERM_STATUS_TYPE_CODE",
        "LEASE_PMT_FREQ_TYPE_CODE",
        "TERM_START_DATE",
        "TERM_EXPIRY_DATE",
        "TERM_RENEWAL_DATE",
        "PAYMENT AMOUNT",
        "PAYMENT_DUE_DATE",
        "PAYMENT_NOTE",
        "IS_GST_ELIGIBLE",
        "GST_AMOUNT",
        "IS_TERM_EXERCISED",
        "CONCURRENCY_CONTROL_NUMBER",
        "APP_CREATE_TIMESTAMP",
        "APP_CREATE_USERID",
        "APP_CREATE_USER_GUID",
        "APP_CREATE_USER_DIRECTORY",
        "APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY")
      select "LEASE_TERM_ID",
        "LEASE_ID",
        "LEASE_TERM_STATUS_TYPE_CODE",
        "LEASE_PMT_FREQ_TYPE_CODE",
        "TERM_START_DATE",
        "TERM_EXPIRY_DATE",
        "TERM_RENEWAL_DATE",
        "PAYMENT AMOUNT",
        "PAYMENT_DUE_DATE",
        "PAYMENT_NOTE",
        "IS_GST_ELIGIBLE",
        "GST_AMOUNT",
        "IS_TERM_EXERCISED",
        "CONCURRENCY_CONTROL_NUMBER",
        "APP_CREATE_TIMESTAMP",
        "APP_CREATE_USERID",
        "APP_CREATE_USER_GUID",
        "APP_CREATE_USER_DIRECTORY",
        "APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY"
      from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_SECDEP_I_S_I_TR
  PRINT N'Create trigger dbo.PIMS_SECDEP_I_S_I_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_SECDEP_I_S_I_TR] ON PIMS_SECURITY_DEPOSIT INSTEAD OF INSERT AS
  SET NOCOUNT ON
  BEGIN TRY
    IF NOT EXISTS(SELECT * FROM inserted) 
      RETURN;
  
    
    insert into PIMS_SECURITY_DEPOSIT ("SECURITY_DEPOSIT_ID",
        "LEASE_ID",
        "SECURITY_DEPOSIT_TYPE_CODE",
        "OTHER_DEPOSIT_TYPE_DESC",
        "DESCRIPTION",
        "AMOUNT_PAID",
        "DEPOSIT_DATE",
        "CONCURRENCY_CONTROL_NUMBER",
        "APP_CREATE_TIMESTAMP",
        "APP_CREATE_USERID",
        "APP_CREATE_USER_GUID",
        "APP_CREATE_USER_DIRECTORY",
        "APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY")
      select "SECURITY_DEPOSIT_ID",
        "LEASE_ID",
        "SECURITY_DEPOSIT_TYPE_CODE",
        "OTHER_DEPOSIT_TYPE_DESC",
        "DESCRIPTION",
        "AMOUNT_PAID",
        "DEPOSIT_DATE",
        "CONCURRENCY_CONTROL_NUMBER",
        "APP_CREATE_TIMESTAMP",
        "APP_CREATE_USERID",
        "APP_CREATE_USER_GUID",
        "APP_CREATE_USER_DIRECTORY",
        "APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY"
      from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_LSPYMT_I_S_U_TR
  PRINT N'Create trigger dbo.PIMS_LSPYMT_I_S_U_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_LSPYMT_I_S_U_TR] ON PIMS_LEASE_PAYMENT INSTEAD OF UPDATE AS
  SET NOCOUNT ON
  BEGIN TRY
    IF NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- validate concurrency control
    if exists (select 1 from inserted, deleted where inserted.CONCURRENCY_CONTROL_NUMBER != deleted.CONCURRENCY_CONTROL_NUMBER+1 AND inserted.LEASE_PAYMENT_ID = deleted.LEASE_PAYMENT_ID)
      raiserror('CONCURRENCY FAILURE.',16,1)
  
  
    -- update statement
    update PIMS_LEASE_PAYMENT
      set "LEASE_PAYMENT_ID" = inserted."LEASE_PAYMENT_ID",
        "LEASE_TERM_ID" = inserted."LEASE_TERM_ID",
        "LEASE_PAYMENT_METHOD_TYPE_CODE" = inserted."LEASE_PAYMENT_METHOD_TYPE_CODE",
        "LEASE_PAYMENT_STATUS_TYPE_CODE" = inserted."LEASE_PAYMENT_STATUS_TYPE_CODE",
        "PAYMENT_RECEIVED_DATE" = inserted."PAYMENT_RECEIVED_DATE",
        "PAYMENT_AMOUNT_PRE_TAX" = inserted."PAYMENT_AMOUNT_PRE_TAX",
        "PAYMENT_AMOUNT_PST" = inserted."PAYMENT_AMOUNT_PST",
        "PAYMENT_AMOUNT_GST" = inserted."PAYMENT_AMOUNT_GST",
        "PAYMENT_AMOUNT_TOTAL" = inserted."PAYMENT_AMOUNT_TOTAL",
        "NOTE" = inserted."NOTE",
        "CONCURRENCY_CONTROL_NUMBER" = inserted."CONCURRENCY_CONTROL_NUMBER",
        "APP_LAST_UPDATE_TIMESTAMP" = inserted."APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID" = inserted."APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID" = inserted."APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY" = inserted."APP_LAST_UPDATE_USER_DIRECTORY"
      , DB_LAST_UPDATE_TIMESTAMP = getutcdate()
      , DB_LAST_UPDATE_USERID = user_name()
      from PIMS_LEASE_PAYMENT
      inner join inserted
      on (PIMS_LEASE_PAYMENT.LEASE_PAYMENT_ID = inserted.LEASE_PAYMENT_ID);
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_LSTERM_I_S_U_TR
  PRINT N'Create trigger dbo.PIMS_LSTERM_I_S_U_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_LSTERM_I_S_U_TR] ON PIMS_LEASE_TERM INSTEAD OF UPDATE AS
  SET NOCOUNT ON
  BEGIN TRY
    IF NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- validate concurrency control
    if exists (select 1 from inserted, deleted where inserted.CONCURRENCY_CONTROL_NUMBER != deleted.CONCURRENCY_CONTROL_NUMBER+1 AND inserted.LEASE_TERM_ID = deleted.LEASE_TERM_ID)
      raiserror('CONCURRENCY FAILURE.',16,1)
  
  
    -- update statement
    update PIMS_LEASE_TERM
      set "LEASE_TERM_ID" = inserted."LEASE_TERM_ID",
        "LEASE_ID" = inserted."LEASE_ID",
        "LEASE_TERM_STATUS_TYPE_CODE" = inserted."LEASE_TERM_STATUS_TYPE_CODE",
        "LEASE_PMT_FREQ_TYPE_CODE" = inserted."LEASE_PMT_FREQ_TYPE_CODE",
        "TERM_START_DATE" = inserted."TERM_START_DATE",
        "TERM_EXPIRY_DATE" = inserted."TERM_EXPIRY_DATE",
        "TERM_RENEWAL_DATE" = inserted."TERM_RENEWAL_DATE",
        "PAYMENT AMOUNT" = inserted."PAYMENT AMOUNT",
        "PAYMENT_DUE_DATE" = inserted."PAYMENT_DUE_DATE",
        "PAYMENT_NOTE" = inserted."PAYMENT_NOTE",
        "IS_GST_ELIGIBLE" = inserted."IS_GST_ELIGIBLE",
        "GST_AMOUNT" = inserted."GST_AMOUNT",
        "IS_TERM_EXERCISED" = inserted."IS_TERM_EXERCISED",
        "CONCURRENCY_CONTROL_NUMBER" = inserted."CONCURRENCY_CONTROL_NUMBER",
        "APP_LAST_UPDATE_TIMESTAMP" = inserted."APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID" = inserted."APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID" = inserted."APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY" = inserted."APP_LAST_UPDATE_USER_DIRECTORY"
      , DB_LAST_UPDATE_TIMESTAMP = getutcdate()
      , DB_LAST_UPDATE_USERID = user_name()
      from PIMS_LEASE_TERM
      inner join inserted
      on (PIMS_LEASE_TERM.LEASE_TERM_ID = inserted.LEASE_TERM_ID);
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_SCDPHL_A_S_IUD_TR
  PRINT N'Create trigger dbo.PIMS_SCDPHL_A_S_IUD_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_SCDPHL_A_S_IUD_TR] ON PIMS_SECURITY_DEPOSIT_HOLDER FOR INSERT, UPDATE, DELETE AS
  SET NOCOUNT ON
  BEGIN TRY
  DECLARE @curr_date datetime;
  SET @curr_date = getutcdate();
    IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- historical
    IF EXISTS(SELECT * FROM deleted)
      update PIMS_SECURITY_DEPOSIT_HOLDER_HIST set END_DATE_HIST = @curr_date where SECURITY_DEPOSIT_HOLDER_ID in (select SECURITY_DEPOSIT_HOLDER_ID from deleted) and END_DATE_HIST is null;
    
    IF EXISTS(SELECT * FROM inserted)
      insert into PIMS_SECURITY_DEPOSIT_HOLDER_HIST ([SECURITY_DEPOSIT_HOLDER_ID], [SECURITY_DEPOSIT_ID], [PERSON_ID], [ORGANIZATION_ID], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_TIMESTAMP], [APP_CREATE_USERID], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_CREATE_TIMESTAMP], [DB_CREATE_USERID], [DB_LAST_UPDATE_TIMESTAMP], [DB_LAST_UPDATE_USERID], _SECURITY_DEPOSIT_HOLDER_HIST_ID, END_DATE_HIST, EFFECTIVE_DATE_HIST)
        select [SECURITY_DEPOSIT_HOLDER_ID], [SECURITY_DEPOSIT_ID], [PERSON_ID], [ORGANIZATION_ID], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_TIMESTAMP], [APP_CREATE_USERID], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_CREATE_TIMESTAMP], [DB_CREATE_USERID], [DB_LAST_UPDATE_TIMESTAMP], [DB_LAST_UPDATE_USERID], (next value for [dbo].[PIMS_SECURITY_DEPOSIT_HOLDER_H_ID_SEQ]) as [_SECURITY_DEPOSIT_HOLDER_HIST_ID], null as [END_DATE_HIST], @curr_date as [EFFECTIVE_DATE_HIST] from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_LSTERM_A_S_IUD_TR
  PRINT N'Create trigger dbo.PIMS_LSTERM_A_S_IUD_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_LSTERM_A_S_IUD_TR] ON PIMS_LEASE_TERM FOR INSERT, UPDATE, DELETE AS
  SET NOCOUNT ON
  BEGIN TRY
  DECLARE @curr_date datetime;
  SET @curr_date = getutcdate();
    IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- historical
    IF EXISTS(SELECT * FROM deleted)
      update PIMS_LEASE_TERM_HIST set END_DATE_HIST = @curr_date where LEASE_TERM_ID in (select LEASE_TERM_ID from deleted) and END_DATE_HIST is null;
    
    IF EXISTS(SELECT * FROM inserted)
      insert into PIMS_LEASE_TERM_HIST ([LEASE_TERM_ID], [LEASE_ID], [LEASE_TERM_STATUS_TYPE_CODE], [LEASE_PMT_FREQ_TYPE_CODE], [TERM_START_DATE], [TERM_EXPIRY_DATE], [TERM_RENEWAL_DATE], [PAYMENT AMOUNT], [PAYMENT_DUE_DATE], [PAYMENT_NOTE], [IS_GST_ELIGIBLE], [GST_AMOUNT], [IS_TERM_EXERCISED], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_TIMESTAMP], [APP_CREATE_USERID], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_CREATE_TIMESTAMP], [DB_CREATE_USERID], [DB_LAST_UPDATE_TIMESTAMP], [DB_LAST_UPDATE_USERID], _LEASE_TERM_HIST_ID, END_DATE_HIST, EFFECTIVE_DATE_HIST)
        select [LEASE_TERM_ID], [LEASE_ID], [LEASE_TERM_STATUS_TYPE_CODE], [LEASE_PMT_FREQ_TYPE_CODE], [TERM_START_DATE], [TERM_EXPIRY_DATE], [TERM_RENEWAL_DATE], [PAYMENT AMOUNT], [PAYMENT_DUE_DATE], [PAYMENT_NOTE], [IS_GST_ELIGIBLE], [GST_AMOUNT], [IS_TERM_EXERCISED], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_TIMESTAMP], [APP_CREATE_USERID], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_CREATE_TIMESTAMP], [DB_CREATE_USERID], [DB_LAST_UPDATE_TIMESTAMP], [DB_LAST_UPDATE_USERID], (next value for [dbo].[PIMS_LEASE_TERM_H_ID_SEQ]) as [_LEASE_TERM_HIST_ID], null as [END_DATE_HIST], @curr_date as [EFFECTIVE_DATE_HIST] from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_SCDPRH_I_S_I_TR
  PRINT N'Create trigger dbo.PIMS_SCDPRH_I_S_I_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_SCDPRH_I_S_I_TR] ON PIMS_SECURITY_DEPOSIT_RETURN_HOLDER INSTEAD OF INSERT AS
  SET NOCOUNT ON
  BEGIN TRY
    IF NOT EXISTS(SELECT * FROM inserted) 
      RETURN;
  
    
    insert into PIMS_SECURITY_DEPOSIT_RETURN_HOLDER ("SECURITY_DEPOSIT_RETURN_HOLDER_ID",
        "SECURITY_DEPOSIT_RETURN_ID",
        "PERSON_ID",
        "ORGANIZATION_ID",
        "CONCURRENCY_CONTROL_NUMBER",
        "APP_CREATE_TIMESTAMP",
        "APP_CREATE_USERID",
        "APP_CREATE_USER_GUID",
        "APP_CREATE_USER_DIRECTORY",
        "APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USER_DIRECTORY")
      select "SECURITY_DEPOSIT_RETURN_HOLDER_ID",
        "SECURITY_DEPOSIT_RETURN_ID",
        "PERSON_ID",
        "ORGANIZATION_ID",
        "CONCURRENCY_CONTROL_NUMBER",
        "APP_CREATE_TIMESTAMP",
        "APP_CREATE_USERID",
        "APP_CREATE_USER_GUID",
        "APP_CREATE_USER_DIRECTORY",
        "APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USER_DIRECTORY"
      from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_LSPYMT_I_S_I_TR
  PRINT N'Create trigger dbo.PIMS_LSPYMT_I_S_I_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_LSPYMT_I_S_I_TR] ON PIMS_LEASE_PAYMENT INSTEAD OF INSERT AS
  SET NOCOUNT ON
  BEGIN TRY
    IF NOT EXISTS(SELECT * FROM inserted) 
      RETURN;
  
    
    insert into PIMS_LEASE_PAYMENT ("LEASE_PAYMENT_ID",
        "LEASE_TERM_ID",
        "LEASE_PAYMENT_METHOD_TYPE_CODE",
        "LEASE_PAYMENT_STATUS_TYPE_CODE",
        "PAYMENT_RECEIVED_DATE",
        "PAYMENT_AMOUNT_PRE_TAX",
        "PAYMENT_AMOUNT_PST",
        "PAYMENT_AMOUNT_GST",
        "PAYMENT_AMOUNT_TOTAL",
        "NOTE",
        "CONCURRENCY_CONTROL_NUMBER",
        "APP_CREATE_TIMESTAMP",
        "APP_CREATE_USERID",
        "APP_CREATE_USER_GUID",
        "APP_CREATE_USER_DIRECTORY",
        "APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY")
      select "LEASE_PAYMENT_ID",
        "LEASE_TERM_ID",
        "LEASE_PAYMENT_METHOD_TYPE_CODE",
        "LEASE_PAYMENT_STATUS_TYPE_CODE",
        "PAYMENT_RECEIVED_DATE",
        "PAYMENT_AMOUNT_PRE_TAX",
        "PAYMENT_AMOUNT_PST",
        "PAYMENT_AMOUNT_GST",
        "PAYMENT_AMOUNT_TOTAL",
        "NOTE",
        "CONCURRENCY_CONTROL_NUMBER",
        "APP_CREATE_TIMESTAMP",
        "APP_CREATE_USERID",
        "APP_CREATE_USER_GUID",
        "APP_CREATE_USER_DIRECTORY",
        "APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY"
      from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_LEASE_A_S_IUD_TR
  PRINT N'Create trigger dbo.PIMS_LEASE_A_S_IUD_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_LEASE_A_S_IUD_TR] ON PIMS_LEASE FOR INSERT, UPDATE, DELETE AS
  SET NOCOUNT ON
  BEGIN TRY
  DECLARE @curr_date datetime;
  SET @curr_date = getutcdate();
    IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- historical
    IF EXISTS(SELECT * FROM deleted)
      update PIMS_LEASE_HIST set END_DATE_HIST = @curr_date where LEASE_ID in (select LEASE_ID from deleted) and END_DATE_HIST is null;
    
    IF EXISTS(SELECT * FROM inserted)
      insert into PIMS_LEASE_HIST ([LEASE_ID], [LEASE_PAY_RVBL_TYPE_CODE], [LEASE_LICENSE_TYPE_CODE], [LEASE_CATEGORY_TYPE_CODE], [LEASE_PURPOSE_TYPE_CODE], [LEASE_PROGRAM_TYPE_CODE], [LEASE_INITIATOR_TYPE_CODE], [LEASE_RESPONSIBILITY_TYPE_CODE], [LEASE_STATUS_TYPE_CODE], [REGION_CODE], [L_FILE_NO], [TFA_FILE_NO], [PS_FILE_NO], [LEASE_CATEGORY_OTHER_DESC], [LEASE_PURPOSE_OTHER_DESC], [MOTI_CONTACT], [DOCUMENTATION_REFERENCE], [OTHER_LEASE_PROGRAM_TYPE], [OTHER_LEASE_LICENSE_TYPE], [OTHER_LEASE_PURPOSE_TYPE], [ORIG_START_DATE], [ORIG_EXPIRY_DATE], [LEASE_AMOUNT], [RESPONSIBILITY_EFFECTIVE_DATE], [INSPECTION_DATE], [IS_SUBJECT_TO_RTA], [IS_COMM_BLDG], [IS_OTHER_IMPROVEMENT], [IS_EXPIRED], [HAS_PHYSICAL_FILE], [HAS_DIGITAL_FILE], [HAS_PHYSICIAL_LICENSE], [HAS_DIGITAL_LICENSE], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_TIMESTAMP], [APP_CREATE_USERID], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_CREATE_TIMESTAMP], [DB_CREATE_USERID], [DB_LAST_UPDATE_TIMESTAMP], [DB_LAST_UPDATE_USERID], _LEASE_HIST_ID, END_DATE_HIST, EFFECTIVE_DATE_HIST)
        select [LEASE_ID], [LEASE_PAY_RVBL_TYPE_CODE], [LEASE_LICENSE_TYPE_CODE], [LEASE_CATEGORY_TYPE_CODE], [LEASE_PURPOSE_TYPE_CODE], [LEASE_PROGRAM_TYPE_CODE], [LEASE_INITIATOR_TYPE_CODE], [LEASE_RESPONSIBILITY_TYPE_CODE], [LEASE_STATUS_TYPE_CODE], [REGION_CODE], [L_FILE_NO], [TFA_FILE_NO], [PS_FILE_NO], [LEASE_CATEGORY_OTHER_DESC], [LEASE_PURPOSE_OTHER_DESC], [MOTI_CONTACT], [DOCUMENTATION_REFERENCE], [OTHER_LEASE_PROGRAM_TYPE], [OTHER_LEASE_LICENSE_TYPE], [OTHER_LEASE_PURPOSE_TYPE], [ORIG_START_DATE], [ORIG_EXPIRY_DATE], [LEASE_AMOUNT], [RESPONSIBILITY_EFFECTIVE_DATE], [INSPECTION_DATE], [IS_SUBJECT_TO_RTA], [IS_COMM_BLDG], [IS_OTHER_IMPROVEMENT], [IS_EXPIRED], [HAS_PHYSICAL_FILE], [HAS_DIGITAL_FILE], [HAS_PHYSICIAL_LICENSE], [HAS_DIGITAL_LICENSE], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_TIMESTAMP], [APP_CREATE_USERID], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_CREATE_TIMESTAMP], [DB_CREATE_USERID], [DB_LAST_UPDATE_TIMESTAMP], [DB_LAST_UPDATE_USERID], (next value for [dbo].[PIMS_LEASE_H_ID_SEQ]) as [_LEASE_HIST_ID], null as [END_DATE_HIST], @curr_date as [EFFECTIVE_DATE_HIST] from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_SDRTRN_A_S_IUD_TR
  PRINT N'Create trigger dbo.PIMS_SDRTRN_A_S_IUD_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_SDRTRN_A_S_IUD_TR] ON PIMS_SECURITY_DEPOSIT_RETURN FOR INSERT, UPDATE, DELETE AS
  SET NOCOUNT ON
  BEGIN TRY
  DECLARE @curr_date datetime;
  SET @curr_date = getutcdate();
    IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- historical
    IF EXISTS(SELECT * FROM deleted)
      update PIMS_SECURITY_DEPOSIT_RETURN_HIST set END_DATE_HIST = @curr_date where SECURITY_DEPOSIT_RETURN_ID in (select SECURITY_DEPOSIT_RETURN_ID from deleted) and END_DATE_HIST is null;
    
    IF EXISTS(SELECT * FROM inserted)
      insert into PIMS_SECURITY_DEPOSIT_RETURN_HIST ([SECURITY_DEPOSIT_RETURN_ID], [LEASE_ID], [SECURITY_DEPOSIT_TYPE_CODE], [SECURITY_DEPOSIT_ID], [TERMINATION_DATE], [CLAIMS_AGAINST], [RETURN_AMOUNT], [RETURN_DATE], [PAYEE_NAME], [PAYEE_ADDRESS], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_TIMESTAMP], [APP_CREATE_USERID], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_CREATE_TIMESTAMP], [DB_CREATE_USERID], [DB_LAST_UPDATE_TIMESTAMP], [DB_LAST_UPDATE_USERID], _SECURITY_DEPOSIT_RETURN_HIST_ID, END_DATE_HIST, EFFECTIVE_DATE_HIST)
        select [SECURITY_DEPOSIT_RETURN_ID], [LEASE_ID], [SECURITY_DEPOSIT_TYPE_CODE], [SECURITY_DEPOSIT_ID], [TERMINATION_DATE], [CLAIMS_AGAINST], [RETURN_AMOUNT], [RETURN_DATE], [PAYEE_NAME], [PAYEE_ADDRESS], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_TIMESTAMP], [APP_CREATE_USERID], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_CREATE_TIMESTAMP], [DB_CREATE_USERID], [DB_LAST_UPDATE_TIMESTAMP], [DB_LAST_UPDATE_USERID], (next value for [dbo].[PIMS_SECURITY_DEPOSIT_RETURN_H_ID_SEQ]) as [_SECURITY_DEPOSIT_RETURN_HIST_ID], null as [END_DATE_HIST], @curr_date as [EFFECTIVE_DATE_HIST] from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_SCDPRH_I_S_U_TR
  PRINT N'Create trigger dbo.PIMS_SCDPRH_I_S_U_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_SCDPRH_I_S_U_TR] ON PIMS_SECURITY_DEPOSIT_RETURN_HOLDER INSTEAD OF UPDATE AS
  SET NOCOUNT ON
  BEGIN TRY
    IF NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- validate concurrency control
    if exists (select 1 from inserted, deleted where inserted.CONCURRENCY_CONTROL_NUMBER != deleted.CONCURRENCY_CONTROL_NUMBER+1 AND inserted.SECURITY_DEPOSIT_RETURN_HOLDER_ID = deleted.SECURITY_DEPOSIT_RETURN_HOLDER_ID)
      raiserror('CONCURRENCY FAILURE.',16,1)
  
  
    -- update statement
    update PIMS_SECURITY_DEPOSIT_RETURN_HOLDER
      set "SECURITY_DEPOSIT_RETURN_HOLDER_ID" = inserted."SECURITY_DEPOSIT_RETURN_HOLDER_ID",
        "SECURITY_DEPOSIT_RETURN_ID" = inserted."SECURITY_DEPOSIT_RETURN_ID",
        "PERSON_ID" = inserted."PERSON_ID",
        "ORGANIZATION_ID" = inserted."ORGANIZATION_ID",
        "CONCURRENCY_CONTROL_NUMBER" = inserted."CONCURRENCY_CONTROL_NUMBER",
        "APP_LAST_UPDATE_USER_GUID" = inserted."APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USERID" = inserted."APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_TIMESTAMP" = inserted."APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USER_DIRECTORY" = inserted."APP_LAST_UPDATE_USER_DIRECTORY"
      , DB_LAST_UPDATE_TIMESTAMP = getutcdate()
      , DB_LAST_UPDATE_USERID = user_name()
      from PIMS_SECURITY_DEPOSIT_RETURN_HOLDER
      inner join inserted
      on (PIMS_SECURITY_DEPOSIT_RETURN_HOLDER.SECURITY_DEPOSIT_RETURN_HOLDER_ID = inserted.SECURITY_DEPOSIT_RETURN_HOLDER_ID);
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_LEASE_I_S_U_TR
  PRINT N'Create trigger dbo.PIMS_LEASE_I_S_U_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_LEASE_I_S_U_TR] ON PIMS_LEASE INSTEAD OF UPDATE AS
  SET NOCOUNT ON
  BEGIN TRY
    IF NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- validate concurrency control
    if exists (select 1 from inserted, deleted where inserted.CONCURRENCY_CONTROL_NUMBER != deleted.CONCURRENCY_CONTROL_NUMBER+1 AND inserted.LEASE_ID = deleted.LEASE_ID)
      raiserror('CONCURRENCY FAILURE.',16,1)
  
  
    -- update statement
    update PIMS_LEASE
      set "LEASE_ID" = inserted."LEASE_ID",
        "LEASE_PAY_RVBL_TYPE_CODE" = inserted."LEASE_PAY_RVBL_TYPE_CODE",
        "LEASE_LICENSE_TYPE_CODE" = inserted."LEASE_LICENSE_TYPE_CODE",
        "LEASE_CATEGORY_TYPE_CODE" = inserted."LEASE_CATEGORY_TYPE_CODE",
        "LEASE_PURPOSE_TYPE_CODE" = inserted."LEASE_PURPOSE_TYPE_CODE",
        "LEASE_PROGRAM_TYPE_CODE" = inserted."LEASE_PROGRAM_TYPE_CODE",
        "LEASE_INITIATOR_TYPE_CODE" = inserted."LEASE_INITIATOR_TYPE_CODE",
        "LEASE_RESPONSIBILITY_TYPE_CODE" = inserted."LEASE_RESPONSIBILITY_TYPE_CODE",
        "LEASE_STATUS_TYPE_CODE" = inserted."LEASE_STATUS_TYPE_CODE",
        "REGION_CODE" = inserted."REGION_CODE",
        "L_FILE_NO" = inserted."L_FILE_NO",
        "TFA_FILE_NO" = inserted."TFA_FILE_NO",
        "PS_FILE_NO" = inserted."PS_FILE_NO",
        "LEASE_DESCRIPTION" = inserted."LEASE_DESCRIPTION",
        "LEASE_CATEGORY_OTHER_DESC" = inserted."LEASE_CATEGORY_OTHER_DESC",
        "LEASE_PURPOSE_OTHER_DESC" = inserted."LEASE_PURPOSE_OTHER_DESC",
        "LEASE_NOTES" = inserted."LEASE_NOTES",
        "MOTI_CONTACT" = inserted."MOTI_CONTACT",
        "DOCUMENTATION_REFERENCE" = inserted."DOCUMENTATION_REFERENCE",
        "RETURN_NOTES" = inserted."RETURN_NOTES",
        "OTHER_LEASE_PROGRAM_TYPE" = inserted."OTHER_LEASE_PROGRAM_TYPE",
        "OTHER_LEASE_LICENSE_TYPE" = inserted."OTHER_LEASE_LICENSE_TYPE",
        "OTHER_LEASE_PURPOSE_TYPE" = inserted."OTHER_LEASE_PURPOSE_TYPE",
        "ORIG_START_DATE" = inserted."ORIG_START_DATE",
        "ORIG_EXPIRY_DATE" = inserted."ORIG_EXPIRY_DATE",
        "LEASE_AMOUNT" = inserted."LEASE_AMOUNT",
        "RESPONSIBILITY_EFFECTIVE_DATE" = inserted."RESPONSIBILITY_EFFECTIVE_DATE",
        "INSPECTION_DATE" = inserted."INSPECTION_DATE",
        "INSPECTION_NOTES" = inserted."INSPECTION_NOTES",
        "IS_SUBJECT_TO_RTA" = inserted."IS_SUBJECT_TO_RTA",
        "IS_COMM_BLDG" = inserted."IS_COMM_BLDG",
        "IS_OTHER_IMPROVEMENT" = inserted."IS_OTHER_IMPROVEMENT",
        "IS_EXPIRED" = inserted."IS_EXPIRED",
        "HAS_PHYSICAL_FILE" = inserted."HAS_PHYSICAL_FILE",
        "HAS_DIGITAL_FILE" = inserted."HAS_DIGITAL_FILE",
        "HAS_PHYSICIAL_LICENSE" = inserted."HAS_PHYSICIAL_LICENSE",
        "HAS_DIGITAL_LICENSE" = inserted."HAS_DIGITAL_LICENSE",
        "CONCURRENCY_CONTROL_NUMBER" = inserted."CONCURRENCY_CONTROL_NUMBER",
        "APP_LAST_UPDATE_TIMESTAMP" = inserted."APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID" = inserted."APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID" = inserted."APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY" = inserted."APP_LAST_UPDATE_USER_DIRECTORY"
      , DB_LAST_UPDATE_TIMESTAMP = getutcdate()
      , DB_LAST_UPDATE_USERID = user_name()
      from PIMS_LEASE
      inner join inserted
      on (PIMS_LEASE.LEASE_ID = inserted.LEASE_ID);
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_SCDPHL_I_S_I_TR
  PRINT N'Create trigger dbo.PIMS_SCDPHL_I_S_I_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_SCDPHL_I_S_I_TR] ON PIMS_SECURITY_DEPOSIT_HOLDER INSTEAD OF INSERT AS
  SET NOCOUNT ON
  BEGIN TRY
    IF NOT EXISTS(SELECT * FROM inserted) 
      RETURN;
  
    
    insert into PIMS_SECURITY_DEPOSIT_HOLDER ("SECURITY_DEPOSIT_HOLDER_ID",
        "SECURITY_DEPOSIT_ID",
        "PERSON_ID",
        "ORGANIZATION_ID",
        "CONCURRENCY_CONTROL_NUMBER",
        "APP_CREATE_TIMESTAMP",
        "APP_CREATE_USERID",
        "APP_CREATE_USER_GUID",
        "APP_CREATE_USER_DIRECTORY",
        "APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY")
      select "SECURITY_DEPOSIT_HOLDER_ID",
        "SECURITY_DEPOSIT_ID",
        "PERSON_ID",
        "ORGANIZATION_ID",
        "CONCURRENCY_CONTROL_NUMBER",
        "APP_CREATE_TIMESTAMP",
        "APP_CREATE_USERID",
        "APP_CREATE_USER_GUID",
        "APP_CREATE_USER_DIRECTORY",
        "APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY"
      from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_SCDPRH_A_S_IUD_TR
  PRINT N'Create trigger dbo.PIMS_SCDPRH_A_S_IUD_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_SCDPRH_A_S_IUD_TR] ON PIMS_SECURITY_DEPOSIT_RETURN_HOLDER FOR INSERT, UPDATE, DELETE AS
  SET NOCOUNT ON
  BEGIN TRY
  DECLARE @curr_date datetime;
  SET @curr_date = getutcdate();
    IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- historical
    IF EXISTS(SELECT * FROM deleted)
      update PIMS_SECURITY_DEPOSIT_RETURN_HOLDER_HIST set END_DATE_HIST = @curr_date where SECURITY_DEPOSIT_RETURN_HOLDER_ID in (select SECURITY_DEPOSIT_RETURN_HOLDER_ID from deleted) and END_DATE_HIST is null;
    
    IF EXISTS(SELECT * FROM inserted)
      insert into PIMS_SECURITY_DEPOSIT_RETURN_HOLDER_HIST ([SECURITY_DEPOSIT_RETURN_HOLDER_ID], [SECURITY_DEPOSIT_RETURN_ID], [PERSON_ID], [ORGANIZATION_ID], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_TIMESTAMP], [APP_CREATE_USERID], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_CREATE_TIMESTAMP], [DB_CREATE_USERID], [DB_LAST_UPDATE_TIMESTAMP], [DB_LAST_UPDATE_USERID], _SECURITY_DEPOSIT_RETURN_HOLDER_HIST_ID, END_DATE_HIST, EFFECTIVE_DATE_HIST)
        select [SECURITY_DEPOSIT_RETURN_HOLDER_ID], [SECURITY_DEPOSIT_RETURN_ID], [PERSON_ID], [ORGANIZATION_ID], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_TIMESTAMP], [APP_CREATE_USERID], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_CREATE_TIMESTAMP], [DB_CREATE_USERID], [DB_LAST_UPDATE_TIMESTAMP], [DB_LAST_UPDATE_USERID], (next value for [dbo].[PIMS_SECURITY_DEPOSIT_RETURN_HOLDER_H_ID_SEQ]) as [_SECURITY_DEPOSIT_RETURN_HOLDER_HIST_ID], null as [END_DATE_HIST], @curr_date as [EFFECTIVE_DATE_HIST] from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_LEASE_I_S_I_TR
  PRINT N'Create trigger dbo.PIMS_LEASE_I_S_I_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_LEASE_I_S_I_TR] ON PIMS_LEASE INSTEAD OF INSERT AS
  SET NOCOUNT ON
  BEGIN TRY
    IF NOT EXISTS(SELECT * FROM inserted) 
      RETURN;
  
    
    insert into PIMS_LEASE ("LEASE_ID",
        "LEASE_PAY_RVBL_TYPE_CODE",
        "LEASE_LICENSE_TYPE_CODE",
        "LEASE_CATEGORY_TYPE_CODE",
        "LEASE_PURPOSE_TYPE_CODE",
        "LEASE_PROGRAM_TYPE_CODE",
        "LEASE_INITIATOR_TYPE_CODE",
        "LEASE_RESPONSIBILITY_TYPE_CODE",
        "LEASE_STATUS_TYPE_CODE",
        "REGION_CODE",
        "L_FILE_NO",
        "TFA_FILE_NO",
        "PS_FILE_NO",
        "LEASE_DESCRIPTION",
        "LEASE_CATEGORY_OTHER_DESC",
        "LEASE_PURPOSE_OTHER_DESC",
        "LEASE_NOTES",
        "MOTI_CONTACT",
        "DOCUMENTATION_REFERENCE",
        "RETURN_NOTES",
        "OTHER_LEASE_PROGRAM_TYPE",
        "OTHER_LEASE_LICENSE_TYPE",
        "OTHER_LEASE_PURPOSE_TYPE",
        "ORIG_START_DATE",
        "ORIG_EXPIRY_DATE",
        "LEASE_AMOUNT",
        "RESPONSIBILITY_EFFECTIVE_DATE",
        "INSPECTION_DATE",
        "INSPECTION_NOTES",
        "IS_SUBJECT_TO_RTA",
        "IS_COMM_BLDG",
        "IS_OTHER_IMPROVEMENT",
        "IS_EXPIRED",
        "HAS_PHYSICAL_FILE",
        "HAS_DIGITAL_FILE",
        "HAS_PHYSICIAL_LICENSE",
        "HAS_DIGITAL_LICENSE",
        "CONCURRENCY_CONTROL_NUMBER",
        "APP_CREATE_TIMESTAMP",
        "APP_CREATE_USERID",
        "APP_CREATE_USER_GUID",
        "APP_CREATE_USER_DIRECTORY",
        "APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY")
      select "LEASE_ID",
        "LEASE_PAY_RVBL_TYPE_CODE",
        "LEASE_LICENSE_TYPE_CODE",
        "LEASE_CATEGORY_TYPE_CODE",
        "LEASE_PURPOSE_TYPE_CODE",
        "LEASE_PROGRAM_TYPE_CODE",
        "LEASE_INITIATOR_TYPE_CODE",
        "LEASE_RESPONSIBILITY_TYPE_CODE",
        "LEASE_STATUS_TYPE_CODE",
        "REGION_CODE",
        "L_FILE_NO",
        "TFA_FILE_NO",
        "PS_FILE_NO",
        "LEASE_DESCRIPTION",
        "LEASE_CATEGORY_OTHER_DESC",
        "LEASE_PURPOSE_OTHER_DESC",
        "LEASE_NOTES",
        "MOTI_CONTACT",
        "DOCUMENTATION_REFERENCE",
        "RETURN_NOTES",
        "OTHER_LEASE_PROGRAM_TYPE",
        "OTHER_LEASE_LICENSE_TYPE",
        "OTHER_LEASE_PURPOSE_TYPE",
        "ORIG_START_DATE",
        "ORIG_EXPIRY_DATE",
        "LEASE_AMOUNT",
        "RESPONSIBILITY_EFFECTIVE_DATE",
        "INSPECTION_DATE",
        "INSPECTION_NOTES",
        "IS_SUBJECT_TO_RTA",
        "IS_COMM_BLDG",
        "IS_OTHER_IMPROVEMENT",
        "IS_EXPIRED",
        "HAS_PHYSICAL_FILE",
        "HAS_DIGITAL_FILE",
        "HAS_PHYSICIAL_LICENSE",
        "HAS_DIGITAL_LICENSE",
        "CONCURRENCY_CONTROL_NUMBER",
        "APP_CREATE_TIMESTAMP",
        "APP_CREATE_USERID",
        "APP_CREATE_USER_GUID",
        "APP_CREATE_USER_DIRECTORY",
        "APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY"
      from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_SDRTRN_I_S_U_TR
  PRINT N'Create trigger dbo.PIMS_SDRTRN_I_S_U_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_SDRTRN_I_S_U_TR] ON PIMS_SECURITY_DEPOSIT_RETURN INSTEAD OF UPDATE AS
  SET NOCOUNT ON
  BEGIN TRY
    IF NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- validate concurrency control
    if exists (select 1 from inserted, deleted where inserted.CONCURRENCY_CONTROL_NUMBER != deleted.CONCURRENCY_CONTROL_NUMBER+1 AND inserted.SECURITY_DEPOSIT_RETURN_ID = deleted.SECURITY_DEPOSIT_RETURN_ID)
      raiserror('CONCURRENCY FAILURE.',16,1)
  
  
    -- update statement
    update PIMS_SECURITY_DEPOSIT_RETURN
      set "SECURITY_DEPOSIT_RETURN_ID" = inserted."SECURITY_DEPOSIT_RETURN_ID",
        "LEASE_ID" = inserted."LEASE_ID",
        "SECURITY_DEPOSIT_TYPE_CODE" = inserted."SECURITY_DEPOSIT_TYPE_CODE",
        "SECURITY_DEPOSIT_ID" = inserted."SECURITY_DEPOSIT_ID",
        "TERMINATION_DATE" = inserted."TERMINATION_DATE",
        "CLAIMS_AGAINST" = inserted."CLAIMS_AGAINST",
        "RETURN_AMOUNT" = inserted."RETURN_AMOUNT",
        "RETURN_DATE" = inserted."RETURN_DATE",
        "PAYEE_NAME" = inserted."PAYEE_NAME",
        "PAYEE_ADDRESS" = inserted."PAYEE_ADDRESS",
        "CONCURRENCY_CONTROL_NUMBER" = inserted."CONCURRENCY_CONTROL_NUMBER",
        "APP_LAST_UPDATE_TIMESTAMP" = inserted."APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID" = inserted."APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID" = inserted."APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY" = inserted."APP_LAST_UPDATE_USER_DIRECTORY"
      , DB_LAST_UPDATE_TIMESTAMP = getutcdate()
      , DB_LAST_UPDATE_USERID = user_name()
      from PIMS_SECURITY_DEPOSIT_RETURN
      inner join inserted
      on (PIMS_SECURITY_DEPOSIT_RETURN.SECURITY_DEPOSIT_RETURN_ID = inserted.SECURITY_DEPOSIT_RETURN_ID);
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_SECDEP_A_S_IUD_TR
  PRINT N'Create trigger dbo.PIMS_SECDEP_A_S_IUD_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_SECDEP_A_S_IUD_TR] ON PIMS_SECURITY_DEPOSIT FOR INSERT, UPDATE, DELETE AS
  SET NOCOUNT ON
  BEGIN TRY
  DECLARE @curr_date datetime;
  SET @curr_date = getutcdate();
    IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- historical
    IF EXISTS(SELECT * FROM deleted)
      update PIMS_SECURITY_DEPOSIT_HIST set END_DATE_HIST = @curr_date where SECURITY_DEPOSIT_ID in (select SECURITY_DEPOSIT_ID from deleted) and END_DATE_HIST is null;
    
    IF EXISTS(SELECT * FROM inserted)
      insert into PIMS_SECURITY_DEPOSIT_HIST ([SECURITY_DEPOSIT_ID], [LEASE_ID], [SECURITY_DEPOSIT_TYPE_CODE], [OTHER_DEPOSIT_TYPE_DESC], [DESCRIPTION], [AMOUNT_PAID], [DEPOSIT_DATE], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_TIMESTAMP], [APP_CREATE_USERID], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_CREATE_TIMESTAMP], [DB_CREATE_USERID], [DB_LAST_UPDATE_TIMESTAMP], [DB_LAST_UPDATE_USERID], _SECURITY_DEPOSIT_HIST_ID, END_DATE_HIST, EFFECTIVE_DATE_HIST)
        select [SECURITY_DEPOSIT_ID], [LEASE_ID], [SECURITY_DEPOSIT_TYPE_CODE], [OTHER_DEPOSIT_TYPE_DESC], [DESCRIPTION], [AMOUNT_PAID], [DEPOSIT_DATE], [CONCURRENCY_CONTROL_NUMBER], [APP_CREATE_TIMESTAMP], [APP_CREATE_USERID], [APP_CREATE_USER_GUID], [APP_CREATE_USER_DIRECTORY], [APP_LAST_UPDATE_TIMESTAMP], [APP_LAST_UPDATE_USERID], [APP_LAST_UPDATE_USER_GUID], [APP_LAST_UPDATE_USER_DIRECTORY], [DB_CREATE_TIMESTAMP], [DB_CREATE_USERID], [DB_LAST_UPDATE_TIMESTAMP], [DB_LAST_UPDATE_USERID], (next value for [dbo].[PIMS_SECURITY_DEPOSIT_H_ID_SEQ]) as [_SECURITY_DEPOSIT_HIST_ID], null as [END_DATE_HIST], @curr_date as [EFFECTIVE_DATE_HIST] from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_SDRTRN_I_S_I_TR
  PRINT N'Create trigger dbo.PIMS_SDRTRN_I_S_I_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_SDRTRN_I_S_I_TR] ON PIMS_SECURITY_DEPOSIT_RETURN INSTEAD OF INSERT AS
  SET NOCOUNT ON
  BEGIN TRY
    IF NOT EXISTS(SELECT * FROM inserted) 
      RETURN;
  
    
    insert into PIMS_SECURITY_DEPOSIT_RETURN ("SECURITY_DEPOSIT_RETURN_ID",
        "LEASE_ID",
        "SECURITY_DEPOSIT_TYPE_CODE",
        "SECURITY_DEPOSIT_ID",
        "TERMINATION_DATE",
        "CLAIMS_AGAINST",
        "RETURN_AMOUNT",
        "RETURN_DATE",
        "PAYEE_NAME",
        "PAYEE_ADDRESS",
        "CONCURRENCY_CONTROL_NUMBER",
        "APP_CREATE_TIMESTAMP",
        "APP_CREATE_USERID",
        "APP_CREATE_USER_GUID",
        "APP_CREATE_USER_DIRECTORY",
        "APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY")
      select "SECURITY_DEPOSIT_RETURN_ID",
        "LEASE_ID",
        "SECURITY_DEPOSIT_TYPE_CODE",
        "SECURITY_DEPOSIT_ID",
        "TERMINATION_DATE",
        "CLAIMS_AGAINST",
        "RETURN_AMOUNT",
        "RETURN_DATE",
        "PAYEE_NAME",
        "PAYEE_ADDRESS",
        "CONCURRENCY_CONTROL_NUMBER",
        "APP_CREATE_TIMESTAMP",
        "APP_CREATE_USERID",
        "APP_CREATE_USER_GUID",
        "APP_CREATE_USER_DIRECTORY",
        "APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY"
      from inserted;
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create trigger dbo.PIMS_SCDPHL_I_S_U_TR
  PRINT N'Create trigger dbo.PIMS_SCDPHL_I_S_U_TR'
  GO
  CREATE TRIGGER [dbo].[PIMS_SCDPHL_I_S_U_TR] ON PIMS_SECURITY_DEPOSIT_HOLDER INSTEAD OF UPDATE AS
  SET NOCOUNT ON
  BEGIN TRY
    IF NOT EXISTS(SELECT * FROM deleted) 
      RETURN;
  
    -- validate concurrency control
    if exists (select 1 from inserted, deleted where inserted.CONCURRENCY_CONTROL_NUMBER != deleted.CONCURRENCY_CONTROL_NUMBER+1 AND inserted.SECURITY_DEPOSIT_HOLDER_ID = deleted.SECURITY_DEPOSIT_HOLDER_ID)
      raiserror('CONCURRENCY FAILURE.',16,1)
  
  
    -- update statement
    update PIMS_SECURITY_DEPOSIT_HOLDER
      set "SECURITY_DEPOSIT_HOLDER_ID" = inserted."SECURITY_DEPOSIT_HOLDER_ID",
        "SECURITY_DEPOSIT_ID" = inserted."SECURITY_DEPOSIT_ID",
        "PERSON_ID" = inserted."PERSON_ID",
        "ORGANIZATION_ID" = inserted."ORGANIZATION_ID",
        "CONCURRENCY_CONTROL_NUMBER" = inserted."CONCURRENCY_CONTROL_NUMBER",
        "APP_LAST_UPDATE_TIMESTAMP" = inserted."APP_LAST_UPDATE_TIMESTAMP",
        "APP_LAST_UPDATE_USERID" = inserted."APP_LAST_UPDATE_USERID",
        "APP_LAST_UPDATE_USER_GUID" = inserted."APP_LAST_UPDATE_USER_GUID",
        "APP_LAST_UPDATE_USER_DIRECTORY" = inserted."APP_LAST_UPDATE_USER_DIRECTORY"
      , DB_LAST_UPDATE_TIMESTAMP = getutcdate()
      , DB_LAST_UPDATE_USERID = user_name()
      from PIMS_SECURITY_DEPOSIT_HOLDER
      inner join inserted
      on (PIMS_SECURITY_DEPOSIT_HOLDER.SECURITY_DEPOSIT_HOLDER_ID = inserted.SECURITY_DEPOSIT_HOLDER_ID);
  
  END TRY
  BEGIN CATCH
     IF @@trancount > 0 ROLLBACK TRANSACTION
     EXEC pims_error_handling
  END CATCH;
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create foreign key constraint dbo.PIM_REGION_PIM_LEASE_FK
  PRINT N'Create foreign key constraint dbo.PIM_REGION_PIM_LEASE_FK'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE]
  	ADD CONSTRAINT [PIM_REGION_PIM_LEASE_FK]
  	FOREIGN KEY([REGION_CODE])
  	REFERENCES [dbo].[PIMS_REGION]([REGION_CODE])
  	ON DELETE NO ACTION 
  	ON UPDATE NO ACTION 
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create foreign key constraint dbo.PIM_SECDEP_PIM_SCDPHL_FK
  PRINT N'Create foreign key constraint dbo.PIM_SECDEP_PIM_SCDPHL_FK'
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT_HOLDER]
  	ADD CONSTRAINT [PIM_SECDEP_PIM_SCDPHL_FK]
  	FOREIGN KEY([SECURITY_DEPOSIT_ID])
  	REFERENCES [dbo].[PIMS_SECURITY_DEPOSIT]([SECURITY_DEPOSIT_ID])
  	ON DELETE NO ACTION 
  	ON UPDATE NO ACTION 
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create foreign key constraint dbo.PIM_ORG_PIM_SCDPHL_FK
  PRINT N'Create foreign key constraint dbo.PIM_ORG_PIM_SCDPHL_FK'
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT_HOLDER]
  	ADD CONSTRAINT [PIM_ORG_PIM_SCDPHL_FK]
  	FOREIGN KEY([ORGANIZATION_ID])
  	REFERENCES [dbo].[PIMS_ORGANIZATION]([ORGANIZATION_ID])
  	ON DELETE NO ACTION 
  	ON UPDATE NO ACTION 
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create foreign key constraint dbo.PIM_PERSON_PIM_SCDPHL_FK
  PRINT N'Create foreign key constraint dbo.PIM_PERSON_PIM_SCDPHL_FK'
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT_HOLDER]
  	ADD CONSTRAINT [PIM_PERSON_PIM_SCDPHL_FK]
  	FOREIGN KEY([PERSON_ID])
  	REFERENCES [dbo].[PIMS_PERSON]([PERSON_ID])
  	ON DELETE NO ACTION 
  	ON UPDATE NO ACTION 
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create foreign key constraint dbo.PIM_LSPMTF_PIM_LSTERM_FK
  PRINT N'Create foreign key constraint dbo.PIM_LSPMTF_PIM_LSTERM_FK'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_TERM]
  	ADD CONSTRAINT [PIM_LSPMTF_PIM_LSTERM_FK]
  	FOREIGN KEY([LEASE_PMT_FREQ_TYPE_CODE])
  	REFERENCES [dbo].[PIMS_LEASE_PMT_FREQ_TYPE]([LEASE_PMT_FREQ_TYPE_CODE])
  	ON DELETE NO ACTION 
  	ON UPDATE NO ACTION 
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create foreign key constraint dbo.PIM_SECDEP_PIM_SDRTRN_FK
  PRINT N'Create foreign key constraint dbo.PIM_SECDEP_PIM_SDRTRN_FK'
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT_RETURN]
  	ADD CONSTRAINT [PIM_SECDEP_PIM_SDRTRN_FK]
  	FOREIGN KEY([SECURITY_DEPOSIT_ID])
  	REFERENCES [dbo].[PIMS_SECURITY_DEPOSIT]([SECURITY_DEPOSIT_ID])
  	ON DELETE NO ACTION 
  	ON UPDATE NO ACTION 
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create foreign key constraint dbo.PIM_PERSON_PIM_SCDPRH_FK
  PRINT N'Create foreign key constraint dbo.PIM_PERSON_PIM_SCDPRH_FK'
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT_RETURN_HOLDER]
  	ADD CONSTRAINT [PIM_PERSON_PIM_SCDPRH_FK]
  	FOREIGN KEY([PERSON_ID])
  	REFERENCES [dbo].[PIMS_PERSON]([PERSON_ID])
  	ON DELETE NO ACTION 
  	ON UPDATE NO ACTION 
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create foreign key constraint dbo.PIM_SDRTRN_PIM_SCDPRH_FK
  PRINT N'Create foreign key constraint dbo.PIM_SDRTRN_PIM_SCDPRH_FK'
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT_RETURN_HOLDER]
  	ADD CONSTRAINT [PIM_SDRTRN_PIM_SCDPRH_FK]
  	FOREIGN KEY([SECURITY_DEPOSIT_RETURN_ID])
  	REFERENCES [dbo].[PIMS_SECURITY_DEPOSIT_RETURN]([SECURITY_DEPOSIT_RETURN_ID])
  	ON DELETE NO ACTION 
  	ON UPDATE NO ACTION 
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create foreign key constraint dbo.PIM_ORG_PIM_SCDPRH_FK
  PRINT N'Create foreign key constraint dbo.PIM_ORG_PIM_SCDPRH_FK'
  GO
  ALTER TABLE [dbo].[PIMS_SECURITY_DEPOSIT_RETURN_HOLDER]
  	ADD CONSTRAINT [PIM_ORG_PIM_SCDPRH_FK]
  	FOREIGN KEY([ORGANIZATION_ID])
  	REFERENCES [dbo].[PIMS_ORGANIZATION]([ORGANIZATION_ID])
  	ON DELETE NO ACTION 
  	ON UPDATE NO ACTION 
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Create foreign key constraint dbo.PIM_LPSTST_PIM_LSPYMT_FK
  PRINT N'Create foreign key constraint dbo.PIM_LPSTST_PIM_LSPYMT_FK'
  GO
  ALTER TABLE [dbo].[PIMS_LEASE_PAYMENT]
  	ADD CONSTRAINT [PIM_LPSTST_PIM_LSPYMT_FK]
  	FOREIGN KEY([LEASE_PAYMENT_STATUS_TYPE_CODE])
  	REFERENCES [dbo].[PIMS_LEASE_PAYMENT_STATUS_TYPE]([LEASE_PAYMENT_STATUS_TYPE_CODE])
  	ON DELETE NO ACTION 
  	ON UPDATE NO ACTION 
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  -- Drop table dbo.PIMS_SEC_DEP_HOLDER_TYPE
  PRINT N'Drop table dbo.PIMS_SEC_DEP_HOLDER_TYPE'
  GO
  DROP TABLE [dbo].[PIMS_SEC_DEP_HOLDER_TYPE]
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  
  COMMIT TRANSACTION
  GO
  IF @@ERROR <> 0 SET NOEXEC ON
  GO
  DECLARE @Success AS BIT
  SET @Success = 1
  SET NOEXEC OFF
  IF (@Success = 1) PRINT 'The database update succeeded'
  ELSE BEGIN
     IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
     PRINT 'The database update failed'
  END
  GO
  