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
SET QUOTED_IDENTIFIER OFF
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
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 [uapmadmin]
GRANT INSERT ON  [dbo].[ca_location] TO [uapmadmin]
GRANT DELETE ON  [dbo].[ca_location] TO [uapmadmin]
GRANT UPDATE ON  [dbo].[ca_location] TO [uapmadmin]
GRANT SELECT ON  [dbo].[ca_location] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[ca_location] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[ca_location] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[ca_location] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[ca_location] TO [uapmbatch]
GRANT INSERT ON  [dbo].[ca_location] TO [uapmbatch]
GRANT DELETE ON  [dbo].[ca_location] TO [uapmbatch]
GRANT UPDATE ON  [dbo].[ca_location] TO [uapmbatch]
GRANT SELECT ON  [dbo].[ca_location] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[ca_location] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[ca_location] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[ca_location] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[ca_location] TO [uapmreporting]
GRANT SELECT ON  [dbo].[ca_location] TO [uapmreporting_group]
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