Tables [dbo].[InternationalShippingDate]
Properties
PropertyValue
Row Count (~)0
Created10:12:49 PM Thursday, December 28, 2006
Last Modified7:27:40 PM Tuesday, June 11, 2024
Columns
NameData TypeMax Length (Bytes)Allow NullsIdentity
Primary Key PK_InternationalShippingDate: InternationalShippingDateIDInternationalShippingDateIDint4
No
1 - 1
Cluster Key IX_InternationalShippingDate_OrdersFID: OrdersFIDForeign Keys FK_InternationalShippingDate_Orders: [dbo].[Orders].OrdersFIDOrdersFIDint4
No
ReadyToGoEstimatedDatedatetime8
Yes
ReadyToGoActualDatedatetime8
Yes
ArrivalOriginPortEstimatedDatedatetime8
Yes
ArrivalOriginPortActualDatedatetime8
Yes
DepartureOriginPortEstimatedDatedatetime8
Yes
DepartureOriginPortActualDatedatetime8
Yes
ArrivalDestinationPortEstimatedDatedatetime8
Yes
ArrivalDestinationPortActualDatedatetime8
Yes
ClearedCustomsEstimatedDatedatetime8
Yes
ClearedCustomsActualDatedatetime8
Yes
PickupDestinationPortEstimatedDatedatetime8
Yes
PickupDestinationPortActualDatedatetime8
Yes
ArrivalAtDestinationEstimatedDatedatetime8
Yes
ArrivalAtDestinationActualDatedatetime8
Yes
PreAlertSentDatedatetime8
Yes
WaybillSentDatedatetime8
Yes
DocumentsPrintedDatedatetime8
Yes
OAPapersSentDatedatetime8
Yes
ShippingDetailSentDatedatetime8
Yes
OAPapersReceivedDatedatetime8
Yes
DeliveryStatusSentDatedatetime8
Yes
BrokerageCollectedDatedatetime8
Yes
PortSailingCutoffActualDatedatetime8
Yes
InlandDepartureCutoffActualDatedatetime8
Yes
InlandDepartureEstimatedDatedatetime8
Yes
InlandDepartureActualDatedatetime8
Yes
InlandArrivalEstimatedDatedatetime8
Yes
InlandArrivalActualDatedatetime8
Yes
DeliveryPermStgEstimatedDatedatetime8
Yes
DeliveryPermStgActualDatedatetime8
Yes
ReadyToBillDatedatetime8
Yes
CertificateDatedatetime8
Yes
ClearedCustomsOrigEstimatedDatedatetime8
Yes
ClearedCustomsOrigActualDatedatetime8
Yes
Foreign Keys FK_InternationalShippingDate_OrderAuditInfo: [dbo].[OrderAuditInfo].OrderAuditInfoFIDIndexes IX_InternationalShippingDate_OrderAuditInfoFID: OrderAuditInfoFIDOrderAuditInfoFIDbigint8
Yes
OrderAuditInfoManualCleanupbigint8
Yes
ReleaseToBillDatedatetime8
Yes
Indexes Indexes
NameColumnsUniqueFill Factor
Primary Key PK_InternationalShippingDate: InternationalShippingDateIDPK_InternationalShippingDateInternationalShippingDateID
Yes
80
Cluster Key IX_InternationalShippingDate_OrdersFID: OrdersFIDIX_InternationalShippingDate_OrdersFIDOrdersFID
Yes
80
IX_InternationalShippingDate_OrderAuditInfoFIDOrderAuditInfoFID
Triggers Triggers
NameANSI Nulls OnQuoted Identifier OnOn
InternationalShippingDateDeletedAudit
Yes
Yes
After Delete
InternationalShippingDateInsertedAudit
Yes
Yes
After Insert
InternationalShippingDateTaskFieldChanged
Yes
Yes
After Insert Update
InternationalShippingDateUpdatedAudit
Yes
Yes
After Update
Foreign Keys Foreign Keys
NameColumns
FK_InternationalShippingDate_OrderAuditInfoOrderAuditInfoFID->[dbo].[OrderAuditInfo].[OrderAuditInfoID]
FK_InternationalShippingDate_OrdersOrdersFID->[dbo].[Orders].[PriKey]
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
CREATE TABLE [dbo].[InternationalShippingDate]
(
[InternationalShippingDateID] [int] NOT NULL IDENTITY(1, 1),
[OrdersFID] [int] NOT NULL,
[ReadyToGoEstimatedDate] [datetime] NULL,
[ReadyToGoActualDate] [datetime] NULL,
[ArrivalOriginPortEstimatedDate] [datetime] NULL,
[ArrivalOriginPortActualDate] [datetime] NULL,
[DepartureOriginPortEstimatedDate] [datetime] NULL,
[DepartureOriginPortActualDate] [datetime] NULL,
[ArrivalDestinationPortEstimatedDate] [datetime] NULL,
[ArrivalDestinationPortActualDate] [datetime] NULL,
[ClearedCustomsEstimatedDate] [datetime] NULL,
[ClearedCustomsActualDate] [datetime] NULL,
[PickupDestinationPortEstimatedDate] [datetime] NULL,
[PickupDestinationPortActualDate] [datetime] NULL,
[ArrivalAtDestinationEstimatedDate] [datetime] NULL,
[ArrivalAtDestinationActualDate] [datetime] NULL,
[PreAlertSentDate] [datetime] NULL,
[WaybillSentDate] [datetime] NULL,
[DocumentsPrintedDate] [datetime] NULL,
[OAPapersSentDate] [datetime] NULL,
[ShippingDetailSentDate] [datetime] NULL,
[OAPapersReceivedDate] [datetime] NULL,
[DeliveryStatusSentDate] [datetime] NULL,
[BrokerageCollectedDate] [datetime] NULL,
[PortSailingCutoffActualDate] [datetime] NULL,
[InlandDepartureCutoffActualDate] [datetime] NULL,
[InlandDepartureEstimatedDate] [datetime] NULL,
[InlandDepartureActualDate] [datetime] NULL,
[InlandArrivalEstimatedDate] [datetime] NULL,
[InlandArrivalActualDate] [datetime] NULL,
[DeliveryPermStgEstimatedDate] [datetime] NULL,
[DeliveryPermStgActualDate] [datetime] NULL,
[ReadyToBillDate] [datetime] NULL,
[CertificateDate] [datetime] NULL,
[ClearedCustomsOrigEstimatedDate] [datetime] NULL,
[ClearedCustomsOrigActualDate] [datetime] NULL,
[OrderAuditInfoFID] [bigint] NULL,
[OrderAuditInfoManualCleanup] [bigint] NULL,
[ReleaseToBillDate] [datetime] NULL
) ON [PRIMARY]
GO

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

    declare @theAuditFields table
    (
        InternationalShippingDateFID int,
        FieldName varchar(128),
        [Value] varchar(256)
    )
    declare @theMinDate datetime
    select @theMinDate = '1900-01-01'
    insert into @theAuditFields
    (
        InternationalShippingDateFID,
        FieldName,
        [Value]
    )
    select deleted.InternationalShippingDateID, 'DepartureOriginPortActualDate', null
    from deleted
    where isnull( deleted.DepartureOriginPortActualDate, @theMinDate ) != @theMinDate
    union all
    select deleted.InternationalShippingDateID, 'ArrivalAtDestinationActualDate', null
    from deleted
    where isnull( deleted.ArrivalAtDestinationActualDate, @theMinDate ) != @theMinDate
    union all
    select deleted.InternationalShippingDateID, 'ClearedCustomsOrigActualDate', null
    from deleted
    where isnull( deleted.ClearedCustomsOrigActualDate, @theMinDate ) != @theMinDate
    union all
    select deleted.InternationalShippingDateID, 'ClearedCustomsActualDate', null
    from deleted
    where isnull( deleted.ClearedCustomsActualDate, @theMinDate ) != @theMinDate
    union all
    select deleted.InternationalShippingDateID, 'CertificateDate', null
    from deleted
    where isnull( deleted.CertificateDate, @theMinDate ) != @theMinDate
    union all
    select deleted.InternationalShippingDateID, 'ReleaseToBillDate', null
    from deleted
    where isnull( deleted.ReleaseToBillDate, @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 = Value,
        ChangedIn =        case
                            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                            else OrderAuditInfo.UpdateSource
                        end
    from @theAuditFields theAuditFields
    inner join deleted on theAuditFields.InternationalShippingDateFID = deleted.InternationalShippingDateID
    left outer join OrderAuditInfo on deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName

    -- we're done with the metadata, so clean it up.
    update InternationalShippingDate
    set OrderAuditInfoFID = NULL
    from deleted
    inner join InternationalShippingDate on deleted.InternationalShippingDateID = InternationalShippingDate.InternationalShippingDateID
    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 InternationalShippingDate record is inserted.
-- Only records audit information for fields that have a value.
CREATE trigger [dbo].[InternationalShippingDateInsertedAudit] on [dbo].[InternationalShippingDate]
after insert
as
set nocount on
    declare @theAuditFields table
    (
        InternationalShippingDateFID int,
        FieldName varchar(128),
        [Value] varchar(256)
    )

    insert into @theAuditFields
    (
        InternationalShippingDateFID,
        FieldName,
        [Value]
    )
    select inserted.InternationalShippingDateID, 'DepartureOriginPortActualDate', convert( varchar(256), inserted.DepartureOriginPortActualDate )
    from inserted
    where update( DepartureOriginPortActualDate )
    union all
    select inserted.InternationalShippingDateID, 'ArrivalAtDestinationActualDate', convert( varchar(256), inserted.ArrivalAtDestinationActualDate )
    from inserted
    where update( ArrivalAtDestinationActualDate )
    union all
    select inserted.InternationalShippingDateID, 'ClearedCustomsOrigActualDate', convert( varchar(256), inserted.ClearedCustomsOrigActualDate )
    from inserted
    where update( ClearedCustomsOrigActualDate )
    union all
    select inserted.InternationalShippingDateID, 'ClearedCustomsActualDate', convert( varchar(256), inserted.ClearedCustomsActualDate )
    from inserted
    where update( ClearedCustomsActualDate )
    union all
    select inserted.InternationalShippingDateID, 'CertificateDate', convert( varchar(256), inserted.CertificateDate )
    from inserted
    where update( CertificateDate )
    union all
    select inserted.InternationalShippingDateID, 'ReleaseToBillDate', convert( varchar(256), inserted.ReleaseToBillDate )
    from inserted
    where update( ReleaseToBillDate )

    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 = [Value],
    ChangedIn =     case
                            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                            else OrderAuditInfo.UpdateSource
                    end
    from @theAuditFields theAuditFields
    inner join inserted on theAuditFields.InternationalShippingDateFID = inserted.InternationalShippingDateID
    left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName
    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 InternationalShippingDate
    set OrderAuditInfoFID = NULL
    from inserted
    inner join InternationalShippingDate on inserted.InternationalShippingDateID = InternationalShippingDate.InternationalShippingDateID
    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].[InternationalShippingDateTaskFieldChanged] on [dbo].[InternationalShippingDate]
after insert, update
as
set nocount on
    if( update( ReadyToGoEstimatedDate ) or update( ReadyToGoActualDate ) or update( ArrivalOriginPortEstimatedDate ) or update( ArrivalOriginPortActualDate ) or update( DepartureOriginPortEstimatedDate ) or
        update( DepartureOriginPortActualDate ) or update( ArrivalDestinationPortEstimatedDate ) or update( ArrivalDestinationPortActualDate ) or update( ClearedCustomsEstimatedDate ) or update( ClearedCustomsActualDate ) or
        update( PickupDestinationPortEstimatedDate ) or update( PickupDestinationPortActualDate ) or update( ArrivalAtDestinationEstimatedDate ) or update( ArrivalAtDestinationActualDate ) or update( PreAlertSentDate ) or
        update( WaybillSentDate ) or update( DocumentsPrintedDate ) or update( OAPapersSentDate ) or update( ShippingDetailSentDate ) or update( OAPapersReceivedDate ) or
        update( DeliveryStatusSentDate ) or update( BrokerageCollectedDate ) or
        update( PortSailingCutoffActualDate ) or update( InlandDepartureCutoffActualDate ) or
        update( InlandDepartureEstimatedDate) or update( InlandDepartureActualDate ) or
        update( InlandArrivalEstimatedDate ) or update( InlandArrivalActualDate ) or update( DeliveryPermStgEstimatedDate ) or update( DeliveryPermStgActualDate ) or
        update( ReadyToBillDate) or update( CertificateDate ) or update( ClearedCustomsOrigEstimatedDate ) or update( ClearedCustomsOrigActualDate ) or
        update( ReleaseToBillDate ) )
    begin
        update ToDo set ToDo.DueDate = dbo.udfTaskDueDate( ToDo.duetype, ToDo.duedays, dbo.GetInternationalDateByOrderAndType( ToDo.OrderID, DependencyDate.FieldName ) )
        from ToDo
        inner join inserted on inserted.OrdersFID = ToDo.OrderID
        inner join DependencyDate on ToDo.DependencyDateID = DependencyDate.DependencyDateID
        where DependencyDate.TableName = 'InternationalShippingDate'
    end
GO

-- Create a trigger that records audit information when a InternationalShippingDate record is updated.
-- Only records audit information for fields that were updated.
CREATE trigger [dbo].[InternationalShippingDateUpdatedAudit] on [dbo].[InternationalShippingDate]
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
        (
            InternationalShippingDateFID int,
            FieldName varchar(128),
            [Value] varchar(256)
        )

        declare @theMinDate datetime
        SELECT @theMinDate = '1900-01-01'
        insert into @theAuditFields
        (
            InternationalShippingDateFID,
            FieldName,
            [Value]
        )
        select inserted.InternationalShippingDateID, 'DepartureOriginPortActualDate', CONVERT( varchar(256), inserted.DepartureOriginPortActualDate )
        from inserted
        inner join deleted on inserted.InternationalShippingDateID = deleted.InternationalShippingDateID and
            isnull( inserted.DepartureOriginPortActualDate, @theMinDate ) != isnull( deleted.DepartureOriginPortActualDate, @theMinDate )
        where update( DepartureOriginPortActualDate )
        union all
        select inserted.InternationalShippingDateID, 'ArrivalAtDestinationActualDate', CONVERT( varchar(256), inserted.ArrivalAtDestinationActualDate )
        from inserted
        inner join deleted on inserted.InternationalShippingDateID = deleted.InternationalShippingDateID and
            isnull( inserted.ArrivalAtDestinationActualDate, @theMinDate ) != isnull( deleted.ArrivalAtDestinationActualDate, @theMinDate )
        where update( ArrivalAtDestinationActualDate )
        union all
        select inserted.InternationalShippingDateID, 'ClearedCustomsOrigActualDate', CONVERT( varchar(256), inserted.ClearedCustomsOrigActualDate )
        from inserted
        inner join deleted on inserted.InternationalShippingDateID = deleted.InternationalShippingDateID and
            isnull( inserted.ClearedCustomsOrigActualDate, @theMinDate ) != isnull( deleted.ClearedCustomsOrigActualDate, @theMinDate )
        where update( ClearedCustomsOrigActualDate )
        union all
        select inserted.InternationalShippingDateID, 'ClearedCustomsActualDate', CONVERT( varchar(256), inserted.ClearedCustomsActualDate )
        from inserted
        inner join deleted on inserted.InternationalShippingDateID = deleted.InternationalShippingDateID and
            isnull( inserted.ClearedCustomsActualDate, @theMinDate ) != isnull( deleted.ClearedCustomsActualDate, @theMinDate )
        where update( ClearedCustomsActualDate )
        union all
        select inserted.InternationalShippingDateID, 'CertificateDate', CONVERT( varchar(256), inserted.CertificateDate )
        from inserted
        inner join deleted on inserted.InternationalShippingDateID = deleted.InternationalShippingDateID and
            isnull( inserted.CertificateDate, @theMinDate ) != isnull( deleted.CertificateDate, @theMinDate )
        where update( CertificateDate )
        union all
        select inserted.InternationalShippingDateID, 'ReleaseToBillDate', CONVERT( varchar(256), inserted.ReleaseToBillDate )
        from inserted
        inner join deleted on inserted.InternationalShippingDateID = deleted.InternationalShippingDateID and
            isnull( inserted.ReleaseToBillDate, @theMinDate ) != isnull( deleted.ReleaseToBillDate, @theMinDate )
        where update( ReleaseToBillDate )

        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 = [Value],
            ChangedIn =     case
                                when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                                else OrderAuditInfo.UpdateSource
                            end
        from @theAuditFields theAuditFields
        inner join inserted on theAuditFields.InternationalShippingDateFID = inserted.InternationalShippingDateID
        left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName

        -- 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 InternationalShippingDate
            set OrderAuditInfoFID = NULL
            from inserted
            inner join InternationalShippingDate on inserted.InternationalShippingDateID = InternationalShippingDate.InternationalShippingDateID

            delete OrderAuditInfo
            from inserted
            inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        end
    end -- if update( OrderAuditInfoFID )
GO
ALTER TABLE [dbo].[InternationalShippingDate] ADD CONSTRAINT [PK_InternationalShippingDate] PRIMARY KEY NONCLUSTERED  ([InternationalShippingDateID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[InternationalShippingDate] ADD CONSTRAINT [IX_InternationalShippingDate_OrdersFID] UNIQUE CLUSTERED  ([OrdersFID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_InternationalShippingDate_OrderAuditInfoFID] ON [dbo].[InternationalShippingDate] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[InternationalShippingDate] ADD CONSTRAINT [FK_InternationalShippingDate_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[InternationalShippingDate] ADD CONSTRAINT [FK_InternationalShippingDate_Orders] FOREIGN KEY ([OrdersFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
GRANT SELECT ON  [dbo].[InternationalShippingDate] TO [MssExec]
GRANT INSERT ON  [dbo].[InternationalShippingDate] TO [MssExec]
GRANT DELETE ON  [dbo].[InternationalShippingDate] TO [MssExec]
GRANT UPDATE ON  [dbo].[InternationalShippingDate] TO [MssExec]
GO
Uses
Used By