-- Add UDDT: AU_EdiDateOffsetHoursType
IF NOT EXISTS (SELECT 1 FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id
WHERE st.name = N'AU_EdiDateOffsetHoursType' AND ss.name = N'dbo')
CREATE TYPE [dbo].[AU_EdiDateOffsetHoursType] FROM smallint NULL
GO
--Create Table: AU_co_contract_line_mst
IF OBJECT_ID(N'[dbo].[AU_co_contract_line_mst]', N'U') IS NULL
CREATE TABLE [dbo].[AU_co_contract_line_mst](
[site_ref] [dbo].[SiteType] NOT NULL
CONSTRAINT [DF_AU_co_contract_line_mst_site_ref] DEFAULT (RTRIM(CONVERT([nvarchar](8),context_info(),0)))
, [contract_id] [dbo].[AU_ContractIDType] NOT NULL
, [co_num] [dbo].[CoNumType] NOT NULL
, [cust_item] [dbo].[CustItemType] NULL
, [CreatedBy] [dbo].[UsernameType] NOT NULL
CONSTRAINT [DF_AU_co_contract_line_mst_CreatedBy] DEFAULT (SUSER_SNAME())
, [UpdatedBy] [dbo].[UsernameType] NOT NULL
CONSTRAINT [DF_AU_co_contract_line_mst_UpdatedBy] DEFAULT (SUSER_SNAME())
, [CreateDate] [dbo].[CurrentDateType] NOT NULL
CONSTRAINT [DF_AU_co_contract_line_mst_CreateDate] DEFAULT (GETDATE())
, [RecordDate] [dbo].[CurrentDateType] NOT NULL
CONSTRAINT [DF_AU_co_contract_line_mst_RecordDate] DEFAULT (GETDATE())
, [RowPointer] [dbo].[RowPointerType] NOT NULL
CONSTRAINT [DF_AU_co_contract_line_mst_RowPointer] DEFAULT (NEWID())
, [NoteExistsFlag] [dbo].[FlagNyType] NOT NULL
CONSTRAINT [DF_AU_co_contract_line_mst_NoteExistsFlag] DEFAULT ((0))
CONSTRAINT [CK_AU_co_contract_line_mst_NoteExistsFlag] CHECK ([NoteExistsFlag] IN (0,1))
, [InWorkflow] [dbo].[FlagNyType] NOT NULL
CONSTRAINT [DF_AU_co_contract_line_mst_InWorkflow] DEFAULT ((0))
CONSTRAINT [CK_AU_co_contract_line_mst_InWorkflow] CHECK ([InWorkflow] IN (0,1))
, CONSTRAINT [PK_AU_co_contract_line_mst] PRIMARY KEY CLUSTERED
(
[contract_id] ASC,
[co_num] ASC,
[co_line] ASC,
[site_ref]
)
, CONSTRAINT [IX_AU_co_contract_line_mst_RowPointer] UNIQUE NONCLUSTERED
(
[RowPointer]
,[site_ref]
)
)
GO
-- Add column with constraints
IF OBJECTPROPERTY(OBJECT_ID(N'[dbo].[so_parms]'), N'IsUserTable') = 1
AND NOT EXISTS (SELECT 1 FROM [sys].[columns]
WHERE [object_id] = OBJECT_ID(N'[dbo].[so_parms]')
AND [name] = N'stat_code')
ALTER TABLE [dbo].[so_parms] ADD
[stat_code] [dbo].[FSStatCodeType] NOT NULL
CONSTRAINT [DF_so_parms_stat_code] DEFAULT (1)
CONSTRAINT [CK_so_parms_stat_code] CHECK ([pick_list_printed] IN (0, 1))
CONSTRAINT [FK_so_parms_stat_code] FOREIGN KEY ([stat_code])
REFERENCES [dbo].[fs_stat_code]([stat_code]) NOT FOR REPLICATION
GO
IF COL_LENGTH('dbo.CRMMobileDeviceIdo', 'IsReadOnly') IS NULL
ALTER TABLE [dbo].[CRMMobileDeviceIdo] ADD [IsReadOnly] [dbo].[ListYesNoType] NOT NULL DEFAULT 0;
GO
-- Add FK between AU_co_contract_line_prc_mst and AU_co_contract_line_mst
IF OBJECTPROPERTY(OBJECT_ID(N'[dbo].[AU_co_contract_line_prc_mst]'), N'IsUserTable') = 1
AND NOT EXISTS (SELECT 1 FROM [sys].[objects]
WHERE [OBJECT_ID] = OBJECT_ID(N'FK_AU_co_contract_line_prc_mst_contract_id_co_num_co_line_site_ref'))
ALTER TABLE [dbo].[AU_co_contract_line_prc_mst] WITH NOCHECK
ADD CONSTRAINT [FK_AU_co_contract_line_prc_mst_contract_id_co_num_co_line_site_ref]
FOREIGN KEY (
[contract_id],
[co_num],
[co_line],
[site_ref]
) REFERENCES [dbo].[AU_co_contract_line_mst](
[contract_id],
[co_num],
[co_line],
[site_ref]
) NOT FOR REPLICATION
GO
-- Add Check Constraint
IF EXISTS (SELECT 1 FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_arpmtd_type]') AND parent_object_id = OBJECT_ID(N'[dbo].[arpmtd]'))
ALTER TABLE [dbo].[arpmtd] DROP CONSTRAINT [CK_arpmtd_type]
GO
ALTER TABLE [dbo].[arpmtd] WITH CHECK ADD CONSTRAINT [CK_arpmtd_type] CHECK (([type]='S' OR ([type]='D' OR ([type]='A' OR ([type]='W' OR [type]='C')))))
GO
ALTER TABLE [dbo].[arpmtd] CHECK CONSTRAINT [CK_arpmtd_type]
GO
-- Remove existed FK
IF EXISTS (SELECT 1
FROM sys.foreign_keys
WHERE OBJECT_ID = OBJECT_ID(N'fs_parmsFk57')
AND parent_OBJECT_ID = OBJECT_ID(N'[dbo].[fs_parms]')
)
BEGIN
ALTER TABLE [dbo].[fs_parms] DROP CONSTRAINT [fs_parmsFk57]
END
GO
-- Remove Existed column
IF EXISTS (SELECT 1 FROM sys.columns c
INNER JOIN sys.objects t ON (c.[OBJECT_ID] = t.[OBJECT_ID])
WHERE t.[OBJECT_ID] = OBJECT_ID(N'[dbo].[fs_parms]')
AND c.[name] = N'parts_sro_template')
BEGIN
ALTER TABLE [dbo].[fs_parms] DROP COLUMN parts_sro_template
END
GO
-- Create Stored Procedure
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
IF EXISTS (SELECT 1 FROM sysobjects WHERE id = object_id(N'MilestoneOperationCheckSp')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE MilestoneOperationCheckSp
GO
CREATE PROCEDURE MilestoneOperationCheckSp (
@PSroNum FSSRONumType
, @Infobar Infobar = NULL OUTPUT
) AS
DECLARE
@Severity INT
SET @Severity = 0
RETURN @Severity
-- Create Function
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SarbCalSp]') AND OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
DROP FUNCTION [dbo].[SarbCalSp]
GO
CREATE FUNCTION dbo.SarbCalSp (
@PFutureDate DateType
, @PNewDate DateType
)
RETURNS SMALLINT
AS
BEGIN
RETURN (month(@PNewDate) - month(@PFutureDate)) + (year(@PNewDate) - year(@PFutureDate)) * 12
END
-- Create Trigger
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UserNamesAppDel]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[UserNamesAppDel]
GO
CREATE TRIGGER dbo.UserNamesAppDel
ON UserNames
FOR DELETE
AS
-- Skip trigger operations as required.
IF dbo.SkipBaseTrigger() = 1
RETURN
DECLARE sssFSUsernamesDelCrs CURSOR LOCAL STATIC
FOR SELECT
dd.RowPointer
, dd.username
FROM deleted AS dd
OPEN sssFSUsernamesDelCrs
WHILE @Severity = 0
BEGIN -- cursor loop
FETCH sssFSUsernamesDelCrs INTO
@RowPointer
, @Username
IF @@FETCH_STATUS = -1
BREAK
END -- End of cursor loop
CLOSE sssFSUsernamesDelCrs
DEALLOCATE sssFSUsernamesDelCrs
IF @Severity = 0
BEGIN
DELETE user_local
FROM
deleted dd
,user_local ul
WHERE ul.UserId = dd.UserId
SELECT @Severity = @@ERROR
END
/* return error result */
IF @Severity <> 0
BEGIN
EXEC RaiseErrorSp @Infobar, @Severity, 3
EXEC @Severity = RollbackTransactionSp
@Severity
IF @Severity != 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
END
No comments:
Post a Comment