Tables [dbo].[MilitaryOrder]
Properties
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
HeapYes
Row Count (~)28320
Created4:57:09 PM Thursday, September 7, 2006
Last Modified9:08:36 AM Thursday, May 23, 2024
Columns
NameData TypeMax Length (Bytes)Allow NullsIdentity
Primary Key PK_MilitaryOrder: MilitaryOrderIDMilitaryOrderIDint4
No
1 - 1
Foreign Keys FK_MilitaryOrder_Orders: [dbo].[Orders].OrdersFIDIndexes IX_MilitaryOrder_GBLNumber: OrdersFID\GBLNumberIndexes IX_MilitaryOrder_OrdersFID: OrdersFIDOrdersFIDint4
No
Indexes IX_MilitaryOrder_GBLNumber: OrdersFID\GBLNumberGBLNumbervarchar(21)21
Yes
1840Scorevarchar(6)6
Yes
1840Submitteddatetime8
Yes
1840Scoreddatetime8
Yes
SSNNumbervarchar(11)11
Yes
RankGradevarchar(10)10
Yes
Foreign Keys FK_MilitaryOrder_MilitaryBranchOfService: [dbo].[MilitaryBranchOfService].MilitaryBranchOfServiceFIDMilitaryBranchOfServiceFIDint4
Yes
Foreign Keys FK_MilitaryOrder_MilitaryCodeOfServiceFID: [dbo].[MilitaryCodeOfService].MilitaryCodeOfServiceFIDMilitaryCodeOfServiceFIDint4
Yes
ContractLotNumbervarchar(10)10
Yes
ServiceOrderNumbervarchar(16)16
Yes
Foreign Keys FK_MilitaryOrder_MilitaryCarrier: [dbo].[MilitaryCarrier].MilitaryCarrierFIDMilitaryCarrierFIDint4
Yes
Foreign Keys FK_MilitaryOrder_OrderingBase: [dbo].[MilitaryBase].OrderingBaseFIDOrderingBaseFIDint4
Yes
Foreign Keys FK_MilitaryOrder_OrderingState: [dbo].[State].OrderingStateFIDOrderingStateFIDint4
Yes
Foreign Keys FK_MilitaryOrder_OriginBase: [dbo].[MilitaryBase].OriginBaseFIDOriginBaseFIDint4
Yes
Foreign Keys FK_MilitaryOrder_OriginState: [dbo].[State].OriginStateFIDOriginStateFIDint4
Yes
Foreign Keys FK_MilitaryOrder_DestinationBase: [dbo].[MilitaryBase].DestinationBaseFIDDestinationBaseFIDint4
Yes
Foreign Keys FK_MilitaryOrder_DestinationState: [dbo].[State].DestinationStateFIDDestinationStateFIDint4
Yes
Channelvarchar(32)32
Yes
SurveyCompleteddatetime8
Yes
TspScoresmallint2
Yes
PackQualitysmallint2
Yes
LoadCourtesysmallint2
Yes
PickupTimelinesssmallint2
Yes
DestCourtesysmallint2
Yes
DelvryTimelinesssmallint2
Yes
OverallStatisfactionsmallint2
Yes
PackAgentFIDint4
Yes
PickupAgentFIDint4
Yes
DelvryAgentFIDint4
Yes
FollowUpSurveybit1
Yes
Foreign Keys FK_MilitaryOrder_OrderAuditInfo: [dbo].[OrderAuditInfo].OrderAuditInfoFIDIndexes IX_MilitaryOrder_OrderAuditInfoFID: OrderAuditInfoFIDOrderAuditInfoFIDbigint8
Yes
SsnLast4varchar(4)4
Yes
Indexes Indexes
NameColumnsUniqueFill Factor
Primary Key PK_MilitaryOrder: MilitaryOrderIDPK_MilitaryOrderMilitaryOrderID
Yes
80
IX_MilitaryOrder_OrdersFIDOrdersFID
Yes
80
IX_MilitaryOrder_GBLNumberOrdersFID, GBLNumber
IX_MilitaryOrder_OrderAuditInfoFIDOrderAuditInfoFID
Triggers Triggers
NameANSI Nulls OnQuoted Identifier OnOn
MilitaryOrderAuditFieldsInitiallySet
Yes
Yes
After Insert
MilitaryOrderOrderAuditFieldsChanged
Yes
Yes
After Update
MilitaryOrderTaskFieldChanged
Yes
Yes
After Insert Update
Foreign Keys Foreign Keys
NameColumns
FK_MilitaryOrder_DestinationBaseDestinationBaseFID->[dbo].[MilitaryBase].[MilitaryBaseID]
FK_MilitaryOrder_DestinationStateDestinationStateFID->[dbo].[State].[StateID]
FK_MilitaryOrder_MilitaryBranchOfServiceMilitaryBranchOfServiceFID->[dbo].[MilitaryBranchOfService].[MilitaryBranchOfServiceID]
FK_MilitaryOrder_MilitaryCarrierMilitaryCarrierFID->[dbo].[MilitaryCarrier].[MilitaryCarrierID]
FK_MilitaryOrder_MilitaryCodeOfServiceFIDMilitaryCodeOfServiceFID->[dbo].[MilitaryCodeOfService].[MilitaryCodeOfServiceID]
FK_MilitaryOrder_OrderAuditInfoOrderAuditInfoFID->[dbo].[OrderAuditInfo].[OrderAuditInfoID]
FK_MilitaryOrder_OrderingBaseOrderingBaseFID->[dbo].[MilitaryBase].[MilitaryBaseID]
FK_MilitaryOrder_OrderingStateOrderingStateFID->[dbo].[State].[StateID]
FK_MilitaryOrder_OrdersOrdersFID->[dbo].[Orders].[PriKey]
FK_MilitaryOrder_OriginBaseOriginBaseFID->[dbo].[MilitaryBase].[MilitaryBaseID]
FK_MilitaryOrder_OriginStateOriginStateFID->[dbo].[State].[StateID]
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
CREATE TABLE [dbo].[MilitaryOrder]
(
[MilitaryOrderID] [int] NOT NULL IDENTITY(1, 1),
[OrdersFID] [int] NOT NULL,
[GBLNumber] [varchar] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[1840Score] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[1840Submitted] [datetime] NULL,
[1840Scored] [datetime] NULL,
[SSNNumber] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RankGrade] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MilitaryBranchOfServiceFID] [int] NULL,
[MilitaryCodeOfServiceFID] [int] NULL,
[ContractLotNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServiceOrderNumber] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MilitaryCarrierFID] [int] NULL,
[OrderingBaseFID] [int] NULL,
[OrderingStateFID] [int] NULL,
[OriginBaseFID] [int] NULL,
[OriginStateFID] [int] NULL,
[DestinationBaseFID] [int] NULL,
[DestinationStateFID] [int] NULL,
[Channel] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SurveyCompleted] [datetime] NULL,
[TspScore] [smallint] NULL,
[PackQuality] [smallint] NULL,
[LoadCourtesy] [smallint] NULL,
[PickupTimeliness] [smallint] NULL,
[DestCourtesy] [smallint] NULL,
[DelvryTimeliness] [smallint] NULL,
[OverallStatisfaction] [smallint] NULL,
[PackAgentFID] [int] NULL,
[PickupAgentFID] [int] NULL,
[DelvryAgentFID] [int] NULL,
[FollowUpSurvey] [bit] NULL,
[OrderAuditInfoFID] [bigint] NULL,
[SsnLast4] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE trigger [dbo].[MilitaryOrderAuditFieldsInitiallySet] on [dbo].[MilitaryOrder]
after insert
as
set nocount on

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

    declare @theAuditInfosByOrderID table
    (
        OrderID int,
        OrderAuditInfoFID bigint      
    )


    declare @theExtAppUser int
    
    --no reason to look up this info if it doesn't get used.
    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 = inserted.GBLNumber,
        ChangedIn =         case
                                when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                                else OrderAuditInfo.UpdateSource
                            end
    from inserted
    left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join OrderHistoryField on OrderHistoryField.FieldName = 'GBLNumber'
    where GBLNumber is not null

    -- we're done with the metadata, so clean it up.
    update MilitaryOrder
    set OrderAuditInfoFID = NULL
    from inserted
    inner join MilitaryOrder on inserted.MilitaryOrderID = MilitaryOrder.MilitaryOrderID

    delete OrderAuditInfo
    from inserted
    inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
GO

CREATE trigger [dbo].[MilitaryOrderOrderAuditFieldsChanged] on [dbo].[MilitaryOrder]
after update
as
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 @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 = inserted.GBLNumber,
            ChangedIn =        case
                                when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                                else OrderAuditInfo.UpdateSource
                            end
        from inserted
        inner join deleted on
            inserted.MilitaryOrderID = deleted.MilitaryOrderID and
            isnull( inserted.GBLNumber, '' ) != isnull( deleted.GBLNumber, '' )
        inner join OrderHistoryField on OrderHistoryField.FieldName = 'GBLNumber'
        left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID

        -- we're done with the metadata, so clean it up.
        update MilitaryOrder
        set OrderAuditInfoFID = NULL
        from inserted
        inner join MilitaryOrder on inserted.MilitaryOrderID = MilitaryOrder.MilitaryOrderID

        delete OrderAuditInfo
        from inserted
        inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    end
GO

CREATE trigger [dbo].[MilitaryOrderTaskFieldChanged] on [dbo].[MilitaryOrder]
after insert, update
as
set nocount on
    if( update( [1840Scored] ) or update( [1840Submitted] ) )
    begin
        update ToDo set ToDo.DueDate =
            case DependencyDate.FieldName
                when '1840Scored' then dbo.udfTaskDueDate( ToDo.duetype, ToDo.duedays, inserted.[1840Scored] )
                when '1840Submitted' then dbo.udfTaskDueDate( ToDo.duetype, ToDo.duedays, inserted.[1840Submitted] )
            end
        from ToDo
        inner join inserted on inserted.OrdersFID = ToDo.OrderID
        inner join DependencyDate on ToDo.DependencyDateID = DependencyDate.DependencyDateID
        where DependencyDate.TableName = 'MilitaryOrder'
    end
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [PK_MilitaryOrder] PRIMARY KEY NONCLUSTERED  ([MilitaryOrderID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [IX_MilitaryOrder_OrdersFID] UNIQUE NONCLUSTERED  ([OrdersFID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MilitaryOrder_GBLNumber] ON [dbo].[MilitaryOrder] ([GBLNumber]) INCLUDE ([OrdersFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MilitaryOrder_OrderAuditInfoFID] ON [dbo].[MilitaryOrder] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_DestinationBase] FOREIGN KEY ([DestinationBaseFID]) REFERENCES [dbo].[MilitaryBase] ([MilitaryBaseID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_DestinationState] FOREIGN KEY ([DestinationStateFID]) REFERENCES [dbo].[State] ([StateID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_MilitaryBranchOfService] FOREIGN KEY ([MilitaryBranchOfServiceFID]) REFERENCES [dbo].[MilitaryBranchOfService] ([MilitaryBranchOfServiceID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_MilitaryCarrier] FOREIGN KEY ([MilitaryCarrierFID]) REFERENCES [dbo].[MilitaryCarrier] ([MilitaryCarrierID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_MilitaryCodeOfServiceFID] FOREIGN KEY ([MilitaryCodeOfServiceFID]) REFERENCES [dbo].[MilitaryCodeOfService] ([MilitaryCodeOfServiceID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_OrderingBase] FOREIGN KEY ([OrderingBaseFID]) REFERENCES [dbo].[MilitaryBase] ([MilitaryBaseID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_OrderingState] FOREIGN KEY ([OrderingStateFID]) REFERENCES [dbo].[State] ([StateID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_Orders] FOREIGN KEY ([OrdersFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_OriginBase] FOREIGN KEY ([OriginBaseFID]) REFERENCES [dbo].[MilitaryBase] ([MilitaryBaseID])
GO
ALTER TABLE [dbo].[MilitaryOrder] ADD CONSTRAINT [FK_MilitaryOrder_OriginState] FOREIGN KEY ([OriginStateFID]) REFERENCES [dbo].[State] ([StateID])
GO
GRANT SELECT ON  [dbo].[MilitaryOrder] TO [MssExec]
GRANT INSERT ON  [dbo].[MilitaryOrder] TO [MssExec]
GRANT DELETE ON  [dbo].[MilitaryOrder] TO [MssExec]
GRANT UPDATE ON  [dbo].[MilitaryOrder] TO [MssExec]
GO
Uses
Used By