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