Tables [dbo].[SITInformation]
Properties
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
HeapYes
Row Count (~)5755
Created5:32:32 PM Wednesday, January 2, 2008
Last Modified7:33:37 PM Thursday, August 29, 2024
Columns
NameData TypeMax Length (Bytes)Allow NullsIdentity
Primary Key PK_SITInformation: SITInformationIDSITInformationIDint4
No
1 - 1
Foreign Keys FK_SITInformation_Orders: [dbo].[Orders].OrdersFIDIndexes IX_SITInformation_OrderSITType: OrdersFID\SITTypeFIDOrdersFIDint4
No
Foreign Keys FK_SITInformation_SITType: [dbo].[SITType].SITTypeFIDIndexes IX_SITInformation_OrderSITType: OrdersFID\SITTypeFIDSITTypeFIDint4
No
Foreign Keys FK_SITInformation_Agent: [dbo].[Agent].AgentFIDAgentFIDint4
Yes
Foreign Keys FK_SITInformation_MilitaryCarrier: [dbo].[MilitaryCarrier].MilitaryCarrierFIDMilitaryCarrierFIDint4
Yes
VendorIDvarchar(15)15
Yes
Authorizationvarchar(128)128
Yes
Daysint4
Yes
EstimatedInDatedatetime8
Yes
ActualInDatedatetime8
Yes
EstimatedOutDatedatetime8
Yes
ActualOutDatedatetime8
Yes
SITToPermDatedatetime8
Yes
DrayageMilesint4
Yes
Weightint4
Yes
SITNumRequestedDatedatetime8
Yes
SITNumReturnedDatedatetime8
Yes
SITNumToAgentDatedatetime8
Yes
DPSArrivalDatedatetime8
Yes
ArrivalDatedatetime8
Yes
Overflowbit1
Yes
DANotificationDatedatetime8
Yes
TCNotificationDatedatetime8
Yes
Foreign Keys FK_SITInformation_OrderAuditInfo: [dbo].[OrderAuditInfo].OrderAuditInfoFIDIndexes IX_SITInformation_OrderAuditInfoFID: OrderAuditInfoFIDOrderAuditInfoFIDbigint8
Yes
OrderAuditInfoManualCleanupbigint8
Yes
Indexes Indexes
NameColumnsUniqueFill Factor
Primary Key PK_SITInformation: SITInformationIDPK_SITInformationSITInformationID
Yes
80
IX_SITInformation_OrderSITTypeOrdersFID, SITTypeFID
Yes
80
IX_SITInformation_OrderAuditInfoFIDOrderAuditInfoFID
Triggers Triggers
NameANSI Nulls OnQuoted Identifier OnOn
SITInformationDeletedAudit
Yes
Yes
After Delete
SITInformationInsertedAudit
Yes
Yes
After Insert
SITInformationTaskFieldChanged
Yes
Yes
After Insert Update
SITInformationTaskFieldDeleted
Yes
Yes
After Delete
SITInformationUpdatedAudit
Yes
Yes
After Update
Check Constraints Check Constraints
NameConstraint
CK_SITInformation_StorageAgent([AgentFID] IS NULL AND [MilitaryCarrierFID] IS NULL AND [VendorID] IS NULL OR [AgentFID] IS NOT NULL AND [MilitaryCarrierFID] IS NULL AND [VendorID] IS NULL OR [AgentFID] IS NULL AND [MilitaryCarrierFID] IS NOT NULL AND [VendorID] IS NULL OR [AgentFID] IS NULL AND [MilitaryCarrierFID] IS NULL AND [VendorID] IS NOT NULL)
Foreign Keys Foreign Keys
NameColumns
FK_SITInformation_AgentAgentFID->[dbo].[Agent].[AgentPriKey]
FK_SITInformation_MilitaryCarrierMilitaryCarrierFID->[dbo].[MilitaryCarrier].[MilitaryCarrierID]
FK_SITInformation_OrderAuditInfoOrderAuditInfoFID->[dbo].[OrderAuditInfo].[OrderAuditInfoID]
FK_SITInformation_OrdersOrdersFID->[dbo].[Orders].[PriKey]
FK_SITInformation_SITTypeSITTypeFID->[dbo].[SITType].[SITTypeID]
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
CREATE TABLE [dbo].[SITInformation]
(
[SITInformationID] [int] NOT NULL IDENTITY(1, 1),
[OrdersFID] [int] NOT NULL,
[SITTypeFID] [int] NOT NULL,
[AgentFID] [int] NULL,
[MilitaryCarrierFID] [int] NULL,
[VendorID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Authorization] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Days] [int] NULL,
[EstimatedInDate] [datetime] NULL,
[ActualInDate] [datetime] NULL,
[EstimatedOutDate] [datetime] NULL,
[ActualOutDate] [datetime] NULL,
[SITToPermDate] [datetime] NULL,
[DrayageMiles] [int] NULL,
[Weight] [int] NULL,
[SITNumRequestedDate] [datetime] NULL,
[SITNumReturnedDate] [datetime] NULL,
[SITNumToAgentDate] [datetime] NULL,
[DPSArrivalDate] [datetime] NULL,
[ArrivalDate] [datetime] NULL,
[Overflow] [bit] NULL,
[DANotificationDate] [datetime] NULL,
[TCNotificationDate] [datetime] NULL,
[OrderAuditInfoFID] [bigint] NULL,
[OrderAuditInfoManualCleanup] [bigint] NULL
) ON [PRIMARY]
GO

-- Create a trigger that records audit information when a SITInformation record is deleted.
-- Only records audit information for fields that contained data before the record was deleted.
CREATE trigger [dbo].[SITInformationDeletedAudit] on [dbo].[SITInformation]
after delete
as
    set nocount on

    declare @theAuditFields table
    (
        SITInformationFID int,
        FieldName varchar(128),
        [Value] varchar(256)
    )
    declare @theMinDate datetime
    select @theMinDate = '1900-01-01'
    insert into @theAuditFields
    (
        SITInformationFID,
        FieldName,
        [Value]
    )
    select deleted.SITInformationID, 'ActualOutDate', null
    from deleted
    where isnull( deleted.ActualOutDate, @theMinDate ) != @theMinDate
    union all
    select deleted.SITInformationID, 'Authorization', null
    from deleted
    where isnull( deleted.[Authorization], '' ) <> ''
    union all
    select deleted.SITInformationID, 'ActualInDate', null
    from deleted
    where isnull( deleted.ActualInDate, @theMinDate ) != @theMinDate

    declare @theChangedOn datetime
    set @theChangedOn = dbo.GetMssDateTime()

    declare @theExtAppUser int

    if exists( select top 1 OrderAuditInfoFID from deleted where OrderAuditInfoFID is null )
    begin
      select @theExtAppUser = SysUser.SysUserID
        from dbo.Sysuser
        where SysUser.FIRSTNAME = 'External' and
        SysUser.LASTNAME = 'Application'
    end   

    insert into OrderHistory
    (
        OrderFID,
        ChangedBy,
        ChangedOn,
        OrderHistoryFieldFID,
        ChangedTo,
        ChangedIn
    )
    select
        OrderFID = deleted.OrdersFID,
        ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
        ChangedOn = @theChangedOn,
        OrderHistoryFieldFID = OrderHistoryField.OrderHistoryFieldID,
        ChangedTo = theAuditFields.[Value],
        ChangedIn =        case
                            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                            else OrderAuditInfo.UpdateSource
                        end
    from @theAuditFields theAuditFields
    inner join deleted on theAuditFields.SITInformationFID = deleted.SITInformationID
    left outer join OrderAuditInfo on deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join SITType on deleted.SITTypeFID = SITType.SITTypeID
    inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName and  
        SITType.[Type] = OrderHistoryField.TableType

    -- we're done with the metadata, so clean it up.
    update SITInformation
    set OrderAuditInfoFID = NULL
    from deleted
    inner join SITInformation on deleted.SITInformationID = SITInformation.SITInformationID
    where deleted.OrderAuditInfoManualCleanup is null

    delete OrderAuditInfo
    from deleted
    inner join OrderAuditInfo on deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    where deleted.OrderAuditInfoManualCleanup is null
GO

-- Create a trigger that records audit information when a SITInformation record is inserted.
-- Only records audit information for fields that have a value.
CREATE trigger [dbo].[SITInformationInsertedAudit] on [dbo].[SITInformation]
after insert
as
set nocount on
    declare @theAuditFields table
    (
        SITInformationFID int,
        FieldName varchar(128),
        [Value] varchar(256)
    )

    insert into @theAuditFields
    (
        SITInformationFID,
        FieldName,
        [Value]
    )
    select inserted.SITInformationID, 'ActualOutDate', convert( varchar(256), inserted.ActualOutDate )
    from inserted
    where update( ActualOutDate )
    union all
    select inserted.SITInformationID, 'Authorization', inserted.[Authorization]
    from inserted
    where update( [Authorization] )
    union all
    select inserted.SITInformationID, 'ActualInDate', convert( varchar(256), inserted.ActualInDate )
    from inserted
    where update( ActualInDate )

    declare @theChangedOn datetime
    set @theChangedOn = dbo.GetMssDateTime()

    declare @theExtAppUser int

    if exists( select top 1 OrderAuditInfoFID from inserted where OrderAuditInfoFID is null )
    begin
      select @theExtAppUser = SysUser.SysUserID
        from dbo.Sysuser
        where SysUser.FIRSTNAME = 'External' and
        SysUser.LASTNAME = 'Application'
    end   

    insert into OrderHistory
    (
        OrderFID,
        ChangedBy,
        ChangedOn,
        OrderHistoryFieldFID,
        ChangedTo,
        ChangedIn
    )
    select
    OrderFID = inserted.OrdersFID,
    ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
    ChangedOn = @theChangedOn,
    OrderHistoryFieldFID = OrderHistoryField.OrderHistoryFieldID,
    ChangedTo = theAuditFields.[Value],
    ChangedIn =     case
                            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                            else OrderAuditInfo.UpdateSource
                    end
    from @theAuditFields theAuditFields
    inner join inserted on theAuditFields.SITInformationFID = inserted.SITInformationID
    left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join SITType on inserted.SITTypeFID = SITTYpe.SITTypeID
    inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName and
        SITType.[Type] = OrderHistoryField.TableType
    where theAuditFields.[Value] is not null

    -- we're done with the metadata, so clean it up. (unless a manual cleanup flag was specified, in
    -- which case its the responsibility of the inserter to clean up this record ) .
    update SITInformation
    set OrderAuditInfoFID = NULL
    from inserted
    inner join SITInformation on inserted.SITInformationID = SITInformation.SITInformationID
    where inserted.OrderAuditInfoManualCleanup is null

    delete OrderAuditInfo
    from inserted
    inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    where inserted.OrderAuditInfoManualCleanup is null
GO

CREATE trigger [dbo].[SITInformationTaskFieldChanged] on [dbo].[SITInformation]
after insert, update
as
set nocount on
    if( update( EstimatedInDate ) or update( EstimatedOutDate ) or update( ActualInDate ) or update( ActualOutDate ) or update( SITToPermDate ) )
    begin
        update ToDo set ToDo.DueDate = dbo.udfTaskDueDate( ToDo.duetype, ToDo.duedays, dbo.GetSITDateByOrderAndType( ToDo.OrderID, DependencyDate.FieldName, DependencyDate.[Description] ) )
        from ToDo
        inner join inserted on inserted.OrdersFID = ToDo.OrderID
        inner join SITType on SITType.SITTypeID = inserted.SITTypeFID
        inner join DependencyDate on ToDo.DependencyDateID = DependencyDate.DependencyDateID
        where DependencyDate.TableName = 'SITInformation' and
            SITType.[Type] = case
                when DependencyDate.[Description] like '%Orig%' then 'Origin'
                else 'Destination'
            end
    end
GO

create trigger [dbo].[SITInformationTaskFieldDeleted] on [dbo].[SITInformation]
after delete
as
set nocount on
    update ToDo set ToDo.DueDate = null
    from ToDo
    inner join deleted on deleted.OrdersFID = ToDo.OrderID
    inner join SITType on SITType.SITTypeID = deleted.SITTypeFID
    inner join DependencyDate on ToDo.DependencyDateID = DependencyDate.DependencyDateID
    where DependencyDate.TableName = 'SITInformation' and
        SITType.[Type] = case
            when DependencyDate.[Description] like '%Orig%' then 'Origin'
            else 'Destination'
        end
GO

-- Create a trigger that records audit information when a SITInformation record is updated.
-- Only records audit information for fields that were updated.
CREATE trigger [dbo].[SITInformationUpdatedAudit] on [dbo].[SITInformation]
after update
as
set nocount on
    set nocount on

    -- When we clear the OrderAuditInfoFID field, no auditing is needed.
    if( update( OrderAuditInfoFID ) and not exists ( select OrderAuditInfoFID from inserted where OrderAuditInfoFID is not null ) )
    begin
        return  
    end
    else
    begin
        declare @theAuditFields table
        (
            SITInformationFID int,
            FieldName varchar(128),
            [Value] varchar(256)
        )

        declare @theMinDate datetime
        SELECT @theMinDate = '1900-01-01'
        insert into @theAuditFields
        (
            SITInformationFID,
            FieldName,
            [Value]
        )
        select inserted.SITInformationID, 'ActualOutDate', CONVERT( varchar(256), inserted.ActualOutDate )
        from inserted
        inner join deleted on inserted.SITInformationID = deleted.SITInformationID and
            isnull( inserted.ActualOutDate, @theMinDate ) != isnull( deleted.ActualOutDate, @theMinDate )
        where update( ActualOutDate )
        union all
        select inserted.SITInformationID, 'Authorization', inserted.[Authorization]
        from inserted
        inner join deleted on inserted.SITInformationID = deleted.SITInformationID and
            isnull( inserted.[Authorization], '' ) != isnull( deleted.[Authorization], '' )
        where update( [Authorization] )
        union all
        select inserted.SITInformationID, 'ActualInDate', CONVERT( varchar(256), inserted.ActualInDate )
        from inserted
        inner join deleted on inserted.SITInformationID = deleted.SITInformationID and
            isnull( inserted.ActualInDate, @theMinDate ) != isnull( deleted.ActualInDate, @theMinDate )
        where update( ActualInDate )

        declare @theChangedOn datetime
        set @theChangedOn = dbo.GetMssDateTime()

        declare @theExtAppUser int
        if exists( select top 1 OrderAuditInfoFID from inserted where OrderAuditInfoFID is null )
        begin
          select @theExtAppUser = SysUser.SysUserID
            from dbo.Sysuser
            where SysUser.FIRSTNAME = 'External' and
            SysUser.LASTNAME = 'Application'
        end   

        insert into OrderHistory
        (
            OrderFID,
            ChangedBy,
            ChangedOn,
            OrderHistoryFieldFID,
            ChangedTo,
            ChangedIn
        )
        select
        OrderFID = inserted.OrdersFID,
        ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
        ChangedOn = @theChangedOn,
        OrderHistoryFieldFID = OrderHistoryField.OrderHistoryFieldID,
        ChangedTo = theAuditFields.[Value],
        ChangedIn =     case
                            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                            else OrderAuditInfo.UpdateSource
                        end
        from @theAuditFields theAuditFields
        inner join inserted on theAuditFields.SITInformationFID = inserted.SITInformationID
        left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        inner join SITType on inserted.SITTypeFID = SITType.SITTypeID
        inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName and
            SITType.[Type] = OrderHistoryField.TableType

        -- we're done with the metadata, so clean it up. (unless a manual cleanup flag was specified, in
        -- which case its the responsibility of the updater to clean up this record )
        if not(update( OrderAuditInfoManualCleanup ) )
        begin
            update SITInformation
            set OrderAuditInfoFID = NULL
            from inserted
            inner join SITInformation on inserted.SITInformationID = SITInformation.SITInformationID

            delete OrderAuditInfo
            from inserted
            inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        end
    end -- if update( OrderAuditInfoFID )
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [CK_SITInformation_StorageAgent] CHECK (([AgentFID] IS NULL AND [MilitaryCarrierFID] IS NULL AND [VendorID] IS NULL OR [AgentFID] IS NOT NULL AND [MilitaryCarrierFID] IS NULL AND [VendorID] IS NULL OR [AgentFID] IS NULL AND [MilitaryCarrierFID] IS NOT NULL AND [VendorID] IS NULL OR [AgentFID] IS NULL AND [MilitaryCarrierFID] IS NULL AND [VendorID] IS NOT NULL))
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [PK_SITInformation] PRIMARY KEY NONCLUSTERED  ([SITInformationID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [IX_SITInformation_OrderSITType] UNIQUE NONCLUSTERED  ([OrdersFID], [SITTypeFID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SITInformation_OrderAuditInfoFID] ON [dbo].[SITInformation] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [FK_SITInformation_Agent] FOREIGN KEY ([AgentFID]) REFERENCES [dbo].[Agent] ([AgentPriKey])
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [FK_SITInformation_MilitaryCarrier] FOREIGN KEY ([MilitaryCarrierFID]) REFERENCES [dbo].[MilitaryCarrier] ([MilitaryCarrierID])
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [FK_SITInformation_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [FK_SITInformation_Orders] FOREIGN KEY ([OrdersFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
ALTER TABLE [dbo].[SITInformation] ADD CONSTRAINT [FK_SITInformation_SITType] FOREIGN KEY ([SITTypeFID]) REFERENCES [dbo].[SITType] ([SITTypeID])
GO
GRANT SELECT ON  [dbo].[SITInformation] TO [MssExec]
GRANT INSERT ON  [dbo].[SITInformation] TO [MssExec]
GRANT DELETE ON  [dbo].[SITInformation] TO [MssExec]
GRANT UPDATE ON  [dbo].[SITInformation] TO [MssExec]
GO
Uses
Used By