CREATE TABLE [dbo].[ca_location]
(
[location_uuid] [binary] (16) NOT NULL,
[location_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__ca_locati__locat__16B953FD] DEFAULT (' '),
[inactive] [int] NOT NULL CONSTRAINT [DF__ca_locati__inact__17AD7836] DEFAULT ('0'),
[pri_phone_number] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fax_number] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mail_address_1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mail_address_2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mail_address_3] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[state] [int] NULL,
[address_3] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mail_address_6] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mail_address_5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mail_address_4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_6] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[primary_contact_uuid] [binary] (16) NULL,
[zip] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[country] [int] NULL,
[county] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[geo_coord_type] [int] NULL,
[geo_coords] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[contact_address_flag] [int] NULL,
[creation_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[site_id] [int] NULL,
[creation_date] [int] NULL,
[comments] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_update_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_update_date] [int] NULL,
[version_number] [int] NULL CONSTRAINT [DF__ca_locati__versi__18A19C6F] DEFAULT ('0'),
[exclude_registration] [int] NULL,
[delete_time] [int] NULL,
[location_type_id] [int] NULL,
[parent_location_uuid] [binary] (16) NULL,
[organization_uuid] [binary] (16) NULL,
[tenant] [binary] (16) NULL,
[region_id] [int] NULL
) ON [PRIMARY]

GO
CREATE TRIGGER dbo.al_u_ca_location
ON dbo.ca_location
AFTER UPDATE
AS
    if dbo.is_installed(2022) = 0
        return
    declare @update_date int
    set @update_date = datediff(ss, '1/1/1970', getutcdate())
    declare @user_name nvarchar(255)
    set @user_name = SYSTEM_USER + '(al_u_ca_location)'
    if update(location_type_id)
    begin
        insert into al_attribute_tracking (object_uuid, table_name, field_name, [action], old_value, new_value, creation_user, creation_date, last_update_user, last_update_date)
            select i.location_uuid, 'ca_location', 'location_type_id', 0, d.location_type_id, i.location_type_id, @user_name, @update_date, @user_name, @update_date
                from inserted i inner join deleted d on (d.location_uuid = i.location_uuid and dbo.is_different(d.location_type_id, i.location_type_id) = 1)
    end
GO
CREATE TRIGGER dbo.ca_tr_del_ca_location
ON dbo.ca_location
FOR DELETE AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_ca_location (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
location_uuid,
location_name,
inactive,
pri_phone_number,
fax_number,
address_1,
address_2,
mail_address_1,
mail_address_2,
mail_address_3,
city,
state,
address_3,
mail_address_6,
mail_address_5,
mail_address_4,
address_6,
address_5,
address_4,
primary_contact_uuid,
zip,
country,
county,
geo_coord_type,
geo_coords,
contact_address_flag,
creation_user,
site_id,
creation_date,
comments,
last_update_user,
last_update_date,
version_number,
exclude_registration,
delete_time,
location_type_id,
parent_location_uuid,
organization_uuid,
tenant,
region_id) SELECT

@audit_product,old.last_update_user, 'DELETE', datediff(ss, '1/1/1970', getutcdate()),
old.location_uuid,
old.location_name,
old.inactive,
old.pri_phone_number,
old.fax_number,
old.address_1,
old.address_2,
old.mail_address_1,
old.mail_address_2,
old.mail_address_3,
old.city,
old.state,
old.address_3,
old.mail_address_6,
old.mail_address_5,
old.mail_address_4,
old.address_6,
old.address_5,
old.address_4,
old.primary_contact_uuid,
old.zip,
old.country,
old.county,
old.geo_coord_type,
old.geo_coords,
old.contact_address_flag,
old.creation_user,
old.site_id,
old.creation_date,
old.comments,
old.last_update_user,
old.last_update_date,
old.version_number,
old.exclude_registration,
old.delete_time,
old.location_type_id,
old.parent_location_uuid,
old.organization_uuid,
old.tenant,
old.region_id
FROM deleted old
GO
CREATE TRIGGER dbo.ca_tr_ins_ca_location
ON dbo.ca_location
FOR INSERT AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_ca_location (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
location_uuid,
location_name,
inactive,
pri_phone_number,
fax_number,
address_1,
address_2,
mail_address_1,
mail_address_2,
mail_address_3,
city,
state,
address_3,
mail_address_6,
mail_address_5,
mail_address_4,
address_6,
address_5,
address_4,
primary_contact_uuid,
zip,
country,
county,
geo_coord_type,
geo_coords,
contact_address_flag,
creation_user,
site_id,
creation_date,
comments,
last_update_user,
last_update_date,
version_number,
exclude_registration,
delete_time,
location_type_id,
parent_location_uuid,
organization_uuid,
tenant,
region_id) SELECT

@audit_product, new.last_update_user, 'INSERT', datediff(ss, '1/1/1970', getutcdate()),
new.location_uuid,
new.location_name,
new.inactive,
new.pri_phone_number,
new.fax_number,
new.address_1,
new.address_2,
new.mail_address_1,
new.mail_address_2,
new.mail_address_3,
new.city,
new.state,
new.address_3,
new.mail_address_6,
new.mail_address_5,
new.mail_address_4,
new.address_6,
new.address_5,
new.address_4,
new.primary_contact_uuid,
new.zip,
new.country,
new.county,
new.geo_coord_type,
new.geo_coords,
new.contact_address_flag,
new.creation_user,
new.site_id,
new.creation_date,
new.comments,
new.last_update_user,
new.last_update_date,
new.version_number,
new.exclude_registration,
new.delete_time,
new.location_type_id,
new.parent_location_uuid,
new.organization_uuid,
new.tenant,
new.region_id
FROM inserted new
GO
CREATE TRIGGER dbo.ca_tr_upd_ca_location
ON dbo.ca_location
FOR UPDATE AS
DECLARE
        @audit_product nvarchar(64),
        @last_update_user nvarchar(64),
        @version_number int


        SET @audit_product = APP_NAME()
SELECT  @version_number = version_number, @last_update_user = last_update_user FROM inserted

IF @version_number != -1
INSERT INTO dbo.aud_ca_location (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
location_uuid,
location_name,
inactive,
pri_phone_number,
fax_number,
address_1,
address_2,
mail_address_1,
mail_address_2,
mail_address_3,
city,
state,
address_3,
mail_address_6,
mail_address_5,
mail_address_4,
address_6,
address_5,
address_4,
primary_contact_uuid,
zip,
country,
county,
geo_coord_type,
geo_coords,
contact_address_flag,
creation_user,
site_id,
creation_date,
comments,
last_update_user,
last_update_date,
version_number,
exclude_registration,
delete_time,
location_type_id,
parent_location_uuid,
organization_uuid,
tenant,
region_id) SELECT

@audit_product, new.last_update_user, 'UPDATE', datediff(ss, '1/1/1970', getutcdate()),
new.location_uuid,
new.location_name,
new.inactive,
new.pri_phone_number,
new.fax_number,
new.address_1,
new.address_2,
new.mail_address_1,
new.mail_address_2,
new.mail_address_3,
new.city,
new.state,
new.address_3,
new.mail_address_6,
new.mail_address_5,
new.mail_address_4,
new.address_6,
new.address_5,
new.address_4,
new.primary_contact_uuid,
new.zip,
new.country,
new.county,
new.geo_coord_type,
new.geo_coords,
new.contact_address_flag,
new.creation_user,
new.site_id,
new.creation_date,
new.comments,
new.last_update_user,
new.last_update_date,
new.version_number,
new.exclude_registration,
new.delete_time,
new.location_type_id,
new.parent_location_uuid,
new.organization_uuid,
new.tenant,
new.region_id
  FROM inserted new
GO
ALTER TABLE [dbo].[ca_location] ADD CONSTRAINT [XPKca_location] PRIMARY KEY CLUSTERED ([location_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_location_idx_01] ON [dbo].[ca_location] ([location_name]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_location_idx_03] ON [dbo].[ca_location] ([location_type_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_location_idx_02] ON [dbo].[ca_location] ([parent_location_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_location_idx_04] ON [dbo].[ca_location] ([tenant]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_location] ADD CONSTRAINT [ca_location_fk01] FOREIGN KEY ([primary_contact_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[ca_location] ADD CONSTRAINT [ca_location_fk02] FOREIGN KEY ([site_id]) REFERENCES [dbo].[ca_site] ([id])
GO
ALTER TABLE [dbo].[ca_location] ADD CONSTRAINT [ca_location_fk03] FOREIGN KEY ([geo_coord_type]) REFERENCES [dbo].[ca_geo_coord_type] ([id])
GO
ALTER TABLE [dbo].[ca_location] ADD CONSTRAINT [ca_location_fk04] FOREIGN KEY ([state]) REFERENCES [dbo].[ca_state_province] ([id])
GO
ALTER TABLE [dbo].[ca_location] ADD CONSTRAINT [FK_ca_location_ca_regions] FOREIGN KEY ([region_id]) REFERENCES [dbo].[ca_regions] ([id])
GO
ALTER TABLE [dbo].[ca_location] ADD CONSTRAINT [fk_ca_location_tenant] FOREIGN KEY ([tenant]) REFERENCES [dbo].[ca_tenant] ([id])
GO
ALTER TABLE [dbo].[ca_location] ADD CONSTRAINT [locrjoin01] FOREIGN KEY ([country]) REFERENCES [dbo].[ca_country] ([id])
GO
ALTER TABLE [dbo].[ca_location] ADD CONSTRAINT [loorgjoin01] FOREIGN KEY ([organization_uuid]) REFERENCES [dbo].[ca_organization] ([organization_uuid])
GO
ALTER TABLE [dbo].[ca_location] ADD CONSTRAINT [loplojoin01] FOREIGN KEY ([parent_location_uuid]) REFERENCES [dbo].[ca_location] ([location_uuid])
GO
ALTER TABLE [dbo].[ca_location] ADD CONSTRAINT [lotypjoin01] FOREIGN KEY ([location_type_id]) REFERENCES [dbo].[ca_location_type] ([id])
GO
GRANT SELECT ON  [dbo].[ca_location] TO [aiadmin]
GRANT SELECT ON  [dbo].[ca_location] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_location] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_location] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_location] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_location] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_location] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_location] TO [service_desk_admin_group]
GRANT INSERT ON  [dbo].[ca_location] TO [service_desk_admin_group]
GRANT DELETE ON  [dbo].[ca_location] TO [service_desk_admin_group]
GRANT UPDATE ON  [dbo].[ca_location] TO [service_desk_admin_group]
GRANT SELECT ON  [dbo].[ca_location] TO [service_desk_ro_group]
GRANT SELECT ON  [dbo].[ca_location] TO [swcmadmin]
GRANT INSERT ON  [dbo].[ca_location] TO [swcmadmin]
GRANT DELETE ON  [dbo].[ca_location] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[ca_location] TO [swcmadmin]
GRANT SELECT ON  [dbo].[ca_location] TO [upmuser_group]
GRANT INSERT ON  [dbo].[ca_location] TO [upmuser_group]
GRANT UPDATE ON  [dbo].[ca_location] TO [upmuser_group]
GRANT SELECT ON  [dbo].[ca_location] TO [usmgroup]
GRANT INSERT ON  [dbo].[ca_location] TO [usmgroup]
GRANT DELETE ON  [dbo].[ca_location] TO [usmgroup]
GRANT UPDATE ON  [dbo].[ca_location] TO [usmgroup]
GO