CREATE TABLE [dbo].[ca_organization]
(
[organization_uuid] [binary] (16) NOT NULL,
[parent_org_uuid] [binary] (16) NULL,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[org_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__ca_organi__org_n__390E6C01] DEFAULT (' '),
[abbreviation] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pri_phone_cc] [int] NULL,
[pri_phone_number] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[alt_phone_cc] [int] NULL,
[alt_phone_number] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fax_cc] [int] NULL,
[fax_number] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[email_address] [nvarchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[location_uuid] [binary] (16) NULL,
[pager_email_address] [nvarchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[inactive] [int] NOT NULL CONSTRAINT [DF__ca_organi__inact__3A02903A] DEFAULT ('0'),
[creation_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_date] [int] 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_organi__versi__3AF6B473] DEFAULT ('0'),
[company_uuid] [binary] (16) NULL,
[comments] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[contact_uuid] [binary] (16) NULL,
[cost_center] [int] NULL,
[exclude_registration] [int] NULL,
[delete_time] [int] NULL,
[tenant] [binary] (16) NULL
) ON [PRIMARY]

GO
CREATE TRIGGER dbo.ca_tr_del_ca_organization
ON dbo.ca_organization
FOR DELETE AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_ca_organization (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
organization_uuid,
parent_org_uuid,
description,
org_name,
abbreviation,
pri_phone_cc,
pri_phone_number,
alt_phone_cc,
alt_phone_number,
fax_cc,
fax_number,
email_address,
location_uuid,
pager_email_address,
inactive,
creation_user,
creation_date,
last_update_user,
last_update_date,
version_number,
company_uuid,
comments,
contact_uuid,
cost_center,
exclude_registration,
delete_time,
tenant) SELECT

@audit_product,old.last_update_user, 'DELETE', datediff(ss, '1/1/1970', getutcdate()),
old.organization_uuid,
old.parent_org_uuid,
old.description,
old.org_name,
old.abbreviation,
old.pri_phone_cc,
old.pri_phone_number,
old.alt_phone_cc,
old.alt_phone_number,
old.fax_cc,
old.fax_number,
old.email_address,
old.location_uuid,
old.pager_email_address,
old.inactive,
old.creation_user,
old.creation_date,
old.last_update_user,
old.last_update_date,
old.version_number,
old.company_uuid,
old.comments,
old.contact_uuid,
old.cost_center,
old.exclude_registration,
old.delete_time,
old.tenant
FROM deleted old
GO
CREATE TRIGGER dbo.ca_tr_ins_ca_organization
ON dbo.ca_organization
FOR INSERT AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_ca_organization (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
organization_uuid,
parent_org_uuid,
description,
org_name,
abbreviation,
pri_phone_cc,
pri_phone_number,
alt_phone_cc,
alt_phone_number,
fax_cc,
fax_number,
email_address,
location_uuid,
pager_email_address,
inactive,
creation_user,
creation_date,
last_update_user,
last_update_date,
version_number,
company_uuid,
comments,
contact_uuid,
cost_center,
exclude_registration,
delete_time,
tenant) SELECT

@audit_product, new.last_update_user, 'INSERT', datediff(ss, '1/1/1970', getutcdate()),
new.organization_uuid,
new.parent_org_uuid,
new.description,
new.org_name,
new.abbreviation,
new.pri_phone_cc,
new.pri_phone_number,
new.alt_phone_cc,
new.alt_phone_number,
new.fax_cc,
new.fax_number,
new.email_address,
new.location_uuid,
new.pager_email_address,
new.inactive,
new.creation_user,
new.creation_date,
new.last_update_user,
new.last_update_date,
new.version_number,
new.company_uuid,
new.comments,
new.contact_uuid,
new.cost_center,
new.exclude_registration,
new.delete_time,
new.tenant
FROM inserted new
GO
CREATE TRIGGER dbo.ca_tr_upd_ca_organization
ON dbo.ca_organization
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_organization (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
organization_uuid,
parent_org_uuid,
description,
org_name,
abbreviation,
pri_phone_cc,
pri_phone_number,
alt_phone_cc,
alt_phone_number,
fax_cc,
fax_number,
email_address,
location_uuid,
pager_email_address,
inactive,
creation_user,
creation_date,
last_update_user,
last_update_date,
version_number,
company_uuid,
comments,
contact_uuid,
cost_center,
exclude_registration,
delete_time,
tenant) SELECT

@audit_product, new.last_update_user, 'UPDATE', datediff(ss, '1/1/1970', getutcdate()),
new.organization_uuid,
new.parent_org_uuid,
new.description,
new.org_name,
new.abbreviation,
new.pri_phone_cc,
new.pri_phone_number,
new.alt_phone_cc,
new.alt_phone_number,
new.fax_cc,
new.fax_number,
new.email_address,
new.location_uuid,
new.pager_email_address,
new.inactive,
new.creation_user,
new.creation_date,
new.last_update_user,
new.last_update_date,
new.version_number,
new.company_uuid,
new.comments,
new.contact_uuid,
new.cost_center,
new.exclude_registration,
new.delete_time,
new.tenant
  FROM inserted new
GO
ALTER TABLE [dbo].[ca_organization] ADD CONSTRAINT [XPKca_organization] PRIMARY KEY CLUSTERED ([organization_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_organization_idx_01] ON [dbo].[ca_organization] ([last_update_date]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_organization_idx_02] ON [dbo].[ca_organization] ([org_name]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_organization_idx_03] ON [dbo].[ca_organization] ([tenant]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_organization] ADD CONSTRAINT [ca_organization_fk01] FOREIGN KEY ([contact_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[ca_organization] ADD CONSTRAINT [ca_organization_fk02] FOREIGN KEY ([parent_org_uuid]) REFERENCES [dbo].[ca_organization] ([organization_uuid])
GO
ALTER TABLE [dbo].[ca_organization] ADD CONSTRAINT [ca_organization_fk03] FOREIGN KEY ([location_uuid]) REFERENCES [dbo].[ca_location] ([location_uuid])
GO
ALTER TABLE [dbo].[ca_organization] ADD CONSTRAINT [ca_organization_fk04] FOREIGN KEY ([company_uuid]) REFERENCES [dbo].[ca_company] ([company_uuid])
GO
ALTER TABLE [dbo].[ca_organization] ADD CONSTRAINT [fk_ca_organization_tenant] FOREIGN KEY ([tenant]) REFERENCES [dbo].[ca_tenant] ([id])
GO
ALTER TABLE [dbo].[ca_organization] ADD CONSTRAINT [orccjoin01] FOREIGN KEY ([cost_center]) REFERENCES [dbo].[ca_resource_cost_center] ([id])
GO
GRANT SELECT ON  [dbo].[ca_organization] TO [ams_group]
GRANT SELECT ON  [dbo].[ca_organization] TO [amsgroup]
GRANT SELECT ON  [dbo].[ca_organization] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_organization] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_organization] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_organization] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_organization] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_organization] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_organization] TO [service_desk_admin_group]
GRANT INSERT ON  [dbo].[ca_organization] TO [service_desk_admin_group]
GRANT DELETE ON  [dbo].[ca_organization] TO [service_desk_admin_group]
GRANT UPDATE ON  [dbo].[ca_organization] TO [service_desk_admin_group]
GRANT SELECT ON  [dbo].[ca_organization] TO [service_desk_ro_group]
GRANT SELECT ON  [dbo].[ca_organization] TO [swcmadmin]
GRANT INSERT ON  [dbo].[ca_organization] TO [swcmadmin]
GRANT DELETE ON  [dbo].[ca_organization] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[ca_organization] TO [swcmadmin]
GRANT SELECT ON  [dbo].[ca_organization] TO [upmuser_group]
GO