CREATE TABLE [dbo].[usm_offering]
(
[offering_id] [int] NOT NULL,
[parent_id] [int] NOT NULL,
[base_offering] [int] NOT NULL,
[top_base_offering] [int] NOT NULL,
[status] [int] NOT NULL,
[date_available] [datetime] NULL,
[date_unavailable] [datetime] NULL,
[date_created] [datetime] NOT NULL,
[date_cancelled] [datetime] NULL,
[code] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[offering_name] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[description] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[info_link] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[notes_id] [int] NULL,
[selection_type] [int] NULL,
[is_leaf] [int] NULL,
[sort_order_type] [int] NOT NULL,
[sort_order_no] [int] NOT NULL,
[sort_order] [int] NOT NULL,
[sub_note_control] [int] NULL,
[image_file] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[domain] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[approval_process] [int] NOT NULL CONSTRAINT [DF__usm_offer__appro__48868512] DEFAULT ((0)),
[approval_level] [int] NULL CONSTRAINT [DF__usm_offer__appro__497AA94B] DEFAULT ((0)),
[version] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[type] [int] NULL CONSTRAINT [DF__usm_offeri__type__4A6ECD84] DEFAULT ((0)),
[default_on_approval_status] [int] NULL CONSTRAINT [DF__usm_offer__defau__4B62F1BD] DEFAULT ((2)),
[sd_request_area] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[path] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[tree_level] [int] NULL,
[feature_sort_order_type] [int] NOT NULL CONSTRAINT [DF__usm_offer__featu__08237DE8] DEFAULT ((0)),
[feature_sort_order] [int] NOT NULL CONSTRAINT [DF__usm_offer__featu__0917A221] DEFAULT ((0)),
[date_last_modified] [datetime] NOT NULL,
[folder_display_type] [int] NOT NULL CONSTRAINT [DF__usm_offer__folde__0442A275] DEFAULT ((0)),
[calendar_id] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[business_hour_id] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[usm_offering] ADD CONSTRAINT [XPKusm_offering] PRIMARY KEY CLUSTERED ([offering_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [usm_offering_idx_03] ON [dbo].[usm_offering] ([base_offering], [status]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [usm_offering_idx_02] ON [dbo].[usm_offering] ([is_leaf], [domain], [status]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [usm_offering_idx001] ON [dbo].[usm_offering] ([is_leaf], [tree_level], [status], [offering_name], [path]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [usm_offering_idx_04] ON [dbo].[usm_offering] ([offering_name]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [usm_offering_idx_01] ON [dbo].[usm_offering] ([parent_id], [status]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[usm_offering] ADD CONSTRAINT [XFKusm_offering1] FOREIGN KEY ([domain]) REFERENCES [dbo].[usm_tenant_ext] ([tenant_id])
GO
ALTER TABLE [dbo].[usm_offering] ADD CONSTRAINT [XFKusm_offering1_cal] FOREIGN KEY ([calendar_id]) REFERENCES [dbo].[usm_sla_calendar] ([calendar_id])
GO
ALTER TABLE [dbo].[usm_offering] ADD CONSTRAINT [XFKusm_offering2_bh] FOREIGN KEY ([business_hour_id]) REFERENCES [dbo].[usm_sla_calendar] ([calendar_id])
GO
GRANT SELECT ON  [dbo].[usm_offering] TO [usmgroup]
GRANT INSERT ON  [dbo].[usm_offering] TO [usmgroup]
GRANT DELETE ON  [dbo].[usm_offering] TO [usmgroup]
GRANT UPDATE ON  [dbo].[usm_offering] TO [usmgroup]
GO