Views [dbo].[EDIInvoice]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnNo
Created11:16:06 AM Wednesday, January 9, 2013
Last Modified7:37:48 PM Tuesday, September 24, 2024
Columns
Name
recid
invoiceno
invoicedate
currencycode
currencyrate
miscamount
freightamount
totinvoiceamount
tottaxableamount
taxable
taxamount
taxpercent
freighttaxamount
tottaxamount
totsalesamount
lineamount
termsid
origordtype
origordno
origorddt
applytono
termsdesc
duedays
discountdays
discountpercent
taxsched
bolno
totalcartons
totalweight
lineitemtotal
orderno
ordertype
orderdate
purchaseorderno
cusno
shiptoxref
shiptoname
shiptoadd1
shiptoadd2
shiptoadd3
shiptocity
shiptostate
shiptozip
shiptocountry
shipdate
canceldate
user1
user2
user3
user4
user5
shipviaid
collect
shippinginstructions1
shippinginstructions2
comment1
comment2
comment3
inventorylocationid
iscreditmemo
department
billtoname
billtoadd1
billtoadd2
billtocity
billtostate
billtozip
billtocountry
billtocontact
billtocontact2
billtophone
billtophone2
billtofax
billtoemail
cususer1
cususer2
cususer3
cususer4
cususer5
altadrname
altadradd1
altadradd2
altadradd3
altadrcity
altadrstate
altadrzip
altadrcountry
altadrcontact
altadrcontact2
altadrphone
altadrphone2
altadrfax
altadruser1
altadruser2
altadruser3
altadruser4
altadruser5
altadremail
locname
locadd1
locadd2
locadd3
loccity
locstate
loczip
loccountry
shipfrname
shipfradd1
shipfradd2
shipfradd3
shipfrcity
shipfrstate
shipfrzip
shipfrcountry
shipfrcontact
shipfrcontact2
shipfrphone
shipfrphone2
shipfrfax
shipfruser1
shipfruser2
shipfruser3
shipfruser4
shipfruser5
shipfremail
recidline
lineno
itemid
cusitemid
itemdesc
itemdesc2
qty
qtytoship
price
uom
qtyreturntostk
reasoncd
extendedprice
taxflag
extendedtaxamount
locid
requestdate
promisedate
requestedshipdate
linuser1
linuser2
linuser3
linuser4
linuser5
priceratio
taxcode
taxableamount
taxstate
linetaxableamt
linetaxamt
linetaxsched
linetaxcd
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
SET QUOTED_IDENTIFIER OFF
GO
CREATE view [dbo].[EDIInvoice]
(
    recid,
    invoiceno,
    invoicedate,
    currencycode,
    currencyrate,
    miscamount,
    freightamount,
    totinvoiceamount,
    tottaxableamount,
    taxable,
    taxamount,
    taxpercent,
    freighttaxamount,
    tottaxamount,
    totsalesamount,
    lineamount,
    termsid,
    origordtype,
    origordno,
    origorddt,
    applytono,
    termsdesc,
    duedays,
    discountdays,
    discountpercent,
    taxsched,
    bolno,
    totalcartons,
    totalweight,
    lineitemtotal,
    orderno,
    ordertype,
    orderdate,
    purchaseorderno,
    cusno,
    shiptoxref,
    shiptoname,
    shiptoadd1,
    shiptoadd2,
    shiptoadd3,
    shiptocity,
    shiptostate,
    shiptozip,
    shiptocountry,
    shipdate,
    canceldate,
    user1,
    user2,
    user3,
    user4,
    user5,
    shipviaid,
    collect,
    shippinginstructions1,
    shippinginstructions2,
    comment1,
    comment2,
    comment3,
    inventorylocationid,
    iscreditmemo,
    department,
    billtoname,
    billtoadd1,
    billtoadd2,
    billtocity,
    billtostate,
    billtozip,
    billtocountry,
    billtocontact,
    billtocontact2,
    billtophone,
    billtophone2,
    billtofax,
    billtoemail,
    cususer1,
    cususer2,
    cususer3,
    cususer4,
    cususer5,

    altadrname,
    altadradd1,
    altadradd2,
    altadradd3,
    altadrcity,
    altadrstate,
    altadrzip,
    altadrcountry,
    altadrcontact,
    altadrcontact2,
    altadrphone,
    altadrphone2,
    altadrfax,
    altadruser1,
    altadruser2,
    altadruser3,
    altadruser4,
    altadruser5,
    altadremail,
    locname,
    locadd1,
    locadd2,
    locadd3,
    loccity,
    locstate,
    loczip,
    loccountry,

    shipfrname,
    shipfradd1,
    shipfradd2,
    shipfradd3,
    shipfrcity,
    shipfrstate,
    shipfrzip,
    shipfrcountry,
    shipfrcontact,
    shipfrcontact2,
    shipfrphone,
    shipfrphone2,
    shipfrfax,
    shipfruser1,
    shipfruser2,
    shipfruser3,
    shipfruser4,
    shipfruser5,
    shipfremail,
    recidline,
    [lineno],
    itemid,
    cusitemid,
    itemdesc,
    itemdesc2,
    qty,
    qtytoship,
    price,
    uom,
    qtyreturntostk,
    reasoncd,
    extendedprice,
    taxflag,
    extendedtaxamount,
    locid,

    requestdate,
    promisedate,
    requestedshipdate,
    linuser1,
    linuser2,
    linuser3,
    linuser4,
    linuser5,

    priceratio,
    taxcode,
    taxableamount,
    taxstate,
    linetaxableamt,
    linetaxamt,
    linetaxsched,
    linetaxcd
)
as

select
    recid = ltrim( rtrim( InvoicedHeader.IHPriKey ) ),
    invoiceno = case
        when InvoicedHeader.InvoiceNumber is null then null
        when charindex( '/', InvoicedHeader.InvoiceNumber ) = 0 then dbo.EdiTextCleaner( InvoicedHeader.OrderNumber + '-' + InvoicedHeader.InvoiceNumber )
        else dbo.EdiTextCleaner( InvoicedHeader.OrderNumber + '-' + reverse( substring( reverse( InvoicedHeader.InvoiceNumber ), 1, charindex('/', reverse( InvoicedHeader.InvoiceNumber ) ) - 1 ) ) )
    end,
    invoicedate = convert( nvarchar(8), InvoicedHeader.InvoiceDate, 112 ),
    currencycode = 'USD',
    currencyrate = 1,
    miscamount = 0,
    freightamount = 0,
    totinvoiceamount = convert( decimal(19, 2), InvoicedHeader.InvoiceTotal ),
    tottaxableamount = 0,
    taxable = 0,
    taxamount = 0,
    taxpercent = 0,
    freighttaxamount = 0,
    tottaxamount = 0,
    totsalesamount = 0,
    lineamount = convert( decimal(19, 2), case
        when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( InvoicedDetail.Amount, 0 )
        else isnull( InvoicedGroup.Amount, 0 ) end ),
    termsid = '',
    origordtype = convert( nvarchar(10), convert( float, isnull( Orders.AlternateWeight, 0 ) ) / convert( float, 100 ) ),
    origordno = convert( nvarchar(10), convert( float, isnull( Orders.BilledWeight, 0 ) ) / convert( float, 100 ) ),
    origorddt = convert( nvarchar(8), isnull( Orders.CreatedOn, 0 ), 112),
    applytono = dbo.EdiTextCleaner( coalesce( OrigSITInformation.[Authorization], DestSITInformation.[Authorization], '' ) ),
    termsdesc = 'DPSD',
    duedays = 0,
    discountdays = 0,
    discountpercent = 0,
    taxsched = '',
    bolno = dbo.EdiTextCleaner( MilitaryOrder.GBLNumber ),
    totalcartons = 0,
    totalweight = convert( nvarchar(10), convert( float, isnull( Orders.BilledWeight, 0 ) ) / convert( float, 100 ) ),
    lineitemtotal = 0,
    orderno = '',
    ordertype = case
        when isnull( dbo.GetShipmentStatus( Orders.PriKey ), '' ) = 'delivered warehouse' then 'ST'
        else 'DV' end,
    orderdate = convert( nvarchar(8), isnull( Orders.CreatedOn, 0 ), 112),
    purchaseorderno = case
        when convert( decimal(19, 2), InvoicedHeader.InvoiceTotal ) >= 0 then 'DI'
        else 'CN' end,
    cusno = dbo.EdiTextCleaner( InvoicedHeader.CustomerNumber ),
    shiptoxref = dbo.EdiTextCleaner( DestMilitaryBase.BaseCode ),
    shiptoname = dbo.EdiTextCleaner( dbo.FormatFirstNameLastName( Orders.FirstName, Orders.LastName ) ),
    shiptoadd1 = '',
    shiptoadd2 = dbo.EdiTextCleaner( OrigMilitaryBase.BaseCode ),
    shiptoadd3 = dbo.EdiTextCleaner( Orders.ToCounty ),
    shiptocity = dbo.EdiTextCleaner( DestOrderAddress.City ),
    shiptostate = dbo.EdiTextCleaner( DestOrderAddress.[State] ),
    shiptozip = dbo.EdiTextCleaner( DestOrderAddress.PostalCode ),
    shiptocountry = dbo.EdiTextCleaner( case isnull( DestCountry.VanlineCountryCode, '' )
        when 'USA' then 'US'
        else isnull( DestCountry.VanlineCountryCode, 'US' ) end ),
    shipdate = convert( nvarchar(8), isnull( Orders.ActLoadDate, 0 ), 112),
    canceldate = '',
    user1 = dbo.EdiTextCleaner( MilitaryCarrier.SCACNumber ),
    user2 = 'SUVL',
    user3 = dbo.EdiTextCleaner( MilitaryCarrierPayeeCode.PayeeCode ),
    user4 = case
        when isnull( MilitaryWeight.ReweighNet, 0 ) > 0 then 'REWEIGH:' + convert( nvarchar(8), MilitaryWeight.ReweighNet )
        else '' end,
    user5 = '',
    shipviaid = '',
    collect = '',
    shippinginstructions1 = case
        when InvoicedHeader.InvoiceNumber is null then null
        when charindex( '/', InvoicedHeader.InvoiceNumber ) = 0 then dbo.EdiTextCleaner( InvoicedHeader.InvoiceNumber )
        else dbo.EdiTextCleaner( InvoicedHeader.OrderNumber + '-' + reverse( substring( reverse( InvoicedHeader.InvoiceNumber ), 1, charindex('/', reverse( InvoicedHeader.InvoiceNumber ) ) - 1 ) ) )
    end,
    shippinginstructions2 = '',
    comment1 = '',
    comment2 = '',
    comment3 = '',
    inventorylocationid = '',
    iscreditmemo = 0,
    department = '',
    billtoname = '',
    billtoadd1 = '',
    billtoadd2 = '',
    billtocity = '',
    billtostate = '',
    billtozip = '',
    billtocountry = '',
    billtocontact = '',
    billtocontact2 = '',
    billtophone = '',
    billtophone2 = '',
    billtofax = '',
    billtoemail = '',
    cususer1 = '',
    cususer2 = '',
    cususer3 = '',
    cususer4 = '',
    cususer5 = '',

    altadrname = '',
    altadradd1 = '',
    altadradd2 = '',
    altadradd3 = '',
    altadrcity = dbo.EdiTextCleaner( XtraStopAddress.City ),
    altadrstate = dbo.EdiTextCleaner( XtraStopAddress.[State] ),
    altadrzip = dbo.EdiTextCleaner( XtraStopAddress.PostalCode ),
    altadrcountry = dbo.EdiTextCleaner( case isnull( XtraStopCountryCodeStandard.VanlineCountryCode, '' )
        when 'USA' then 'US'
        else isnull( XtraStopCountryCodeStandard.VanlineCountryCode, '' ) end ),
    altadrcontact = '',
    altadrcontact2 = '',
    altadrphone = '',
    altadrphone2 = '',
    altadrfax = '',
    altadruser1 = '',
    altadruser2 = dbo.EdiTextCleaner( XtraStop.County ),
    altadruser3 = '',
    altadruser4 = '',
    altadruser5 = '',
    altadremail = '',
    locname = '',
    locadd1 = '',
    locadd2 = '',
    locadd3 = '',
    loccity = '',
    locstate = '',
    loczip = '',
    loccountry = '',

    shipfrname = dbo.EdiTextCleaner( dbo.FormatFirstNameLastName( Orders.FirstName, Orders.LastName ) ),
    shipfradd1 = '',
    shipfradd2 = '',
    shipfradd3 = '',
    shipfrcity = dbo.EdiTextCleaner( OrigOrderAddress.City ),
    shipfrcity = dbo.EdiTextCleaner( OrigOrderAddress.[State] ),
    shipfrzip = dbo.EdiTextCleaner( OrigOrderAddress.PostalCode ),
    shipfrcountry = dbo.EdiTextCleaner( case isnull( OrigCountry.VanlineCountryCode, '' )
        when 'USA' then 'US'
        else isnull( OrigCountry.VanlineCountryCode, 'US' ) end ),
    shipfrcontact = '',
    shipfrcontact2 = '',
    shipfrphone = '',
    shipfrphone2 = '',
    shipfrfax = '',
    shipfruser1 = dbo.EdiTextCleaner( OrigMilitaryBase.BaseCode ),
    shipfruser2 = dbo.EdiTextCleaner( Orders.FromCounty ),
    shipfruser3 = '',
    shipfruser4 = '',
    shipfruser5 = '',
    shipfremail = '',

    -- Need a unique id for each row returned that can be linked back to our data (if necessary).
    recidline = ( convert ( bigint, InvoicedGroup.IGPriKey ) * 2147483648 ) + convert( bigint,  isnull( InvoicedDetail.IDPriKey, 0 ) ),

    [lineno] = row_number() over ( partition by InvoicedHeader.IHPriKey order by InvoicedGroup.SortOrder asc, isnull( InvoicedDetail.SortOrder, 0 ) asc ),
    itemid = dbo.EdiTextCleaner( case
        when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then DetailCustomerInvoiceReportEDIItem.EDIItemCode
        else GroupCustomerInvoiceReportEDIItem.EDIItemCode end ),
    cusitemid = convert( nvarchar(6),
        case
            isnull( case
                when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then InvoiceDetailEDIMileageType.[Description]
                else InvoiceGroupEDIMileageType.[Description]
            end, 'Order Miles' )
        when 'Drayage Miles' then
            isnull( case
                case
                    when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( InvoiceDetailPointOfService.[Description], '' )
                    else isnull( InvoiceGroupPointOfService.[Description], '' )
                end
            when 'Destination' then DestSITInformation.DrayageMiles
            when 'Origin' then OrigSITInformation.DrayageMiles
            else 30
            end, 30 )

        when 'Shuttle Miles' then
            isnull( case
                case
                    when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( InvoiceDetailPointOfService.[Description], '' )
                    else isnull( InvoiceGroupPointOfService.[Description], '' )
                end
            when 'Destination' then Orders.DestinationShuttleMiles
            when 'Origin' then Orders.OriginShuttleMiles
            else 10
            end, 10 )

        else
            isnull( Orders.Miles, 0 )
        end ),
    itemdesc = dbo.EdiTextCleaner( case
        when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then DetailCustomerInvoiceReportEDIItem.[Description]
        else GroupCustomerInvoiceReportEDIItem.[Description] end ),
    itemdesc2 = '',
    qty = case
            case
                when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( DetailCustomerInvoiceReportEDIItem.UnitOfMeasure1, '' )
                else isnull( GroupCustomerInvoiceReportEDIItem.UnitOfMeasure1, '' )
            end
        when 'TH' then
            case
                when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( InvoicedDetail.Quantity * InvoicedDetail.Quantity2, 0 )
                else isnull( InvoicedGroup.Quantity * InvoicedGroup.Quantity2, 0 )
            end
        else
            case
                case
                    when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( InvoiceDetailRateType.QuantityTwoDescription, '' )
                    else isnull( InvoiceGroupRateType.QuantityTwoDescription, '' )
                end
            when 'Weight' then
                case
                    when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( InvoicedDetail.Quantity, 100 )
                    else isnull( InvoicedGroup.Quantity, 100 )
                end / convert( float, 100 )
            else
                case
                    when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( InvoicedDetail.Quantity, 1 )
                    else isnull( InvoicedGroup.Quantity, 1 )
                end
            end
        end,
    qtytoship = case
            case
                when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( DetailCustomerInvoiceReportEDIItem.UnitOfMeasure1, '' )
                else isnull( GroupCustomerInvoiceReportEDIItem.UnitOfMeasure1, '' )
            end
        when 'TH' then 1
        else
            case
                case
                    when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( InvoiceDetailRateType.QuantityOneDescription, '' )
                    else isnull( InvoiceGroupRateType.QuantityOneDescription, '' )
                end
            when 'Weight' then
                case
                    when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( InvoicedDetail.Quantity2, 0 )
                    else isnull( InvoicedGroup.Quantity2, 0 )
                end / convert( float, 100 )
            else
                case
                    when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( InvoicedDetail.Quantity2, 0 )
                    else isnull( InvoicedGroup.Quantity2, 0 )
                end
            end
        end,
    price = case
        when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( InvoicedDetail.Rate, 0 )
        else isnull( InvoicedGroup.Rate, 0 ) end,
    uom = dbo.EdiTextCleaner( case
        when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( DetailCustomerInvoiceReportEDIItem.UnitOfMeasure1, '' )
        else isnull( GroupCustomerInvoiceReportEDIItem.UnitOfMeasure1, '' ) end ),
    qtyreturntostk = 0,
    reasoncd = left( dbo.EdiTextCleaner( case
        when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( InvoicedDetail.NoteText, '' )
        else isnull( InvoicedGroup.NoteText, '' )
        end ), 45 ),
    extendedprice = convert( decimal(19, 2), case
        when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( InvoicedDetail.Amount, 0 )
        else isnull( InvoicedGroup.Amount, 0 ) end ),
    taxflag = 0,
    extendedtaxamount = convert( decimal(19, 2),
        case when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then
            case when charindex( '130', DetailCustomerInvoiceReportEDIItem.EDIItemCode, 0 ) <> 0 and
                isnull( InvoicedDetail.Quantity, 1 ) >= 2 then isnull( InvoicedDetail.Rate, 0 )
            else isnull( InvoicedDetail.GrossAmount, 0 ) end
        else
            case when charindex( '130', GroupCustomerInvoiceReportEDIItem.EDIItemCode, 0 ) <> 0 and
                isnull( InvoicedGroup.Quantity, 1 ) >= 2 then isnull( InvoicedGroup.Rate, 0 )
            else isnull( InvoicedGroup.GrossAmount, 0 ) end
        end ),
    locid = '',
    requestdate = convert( nvarchar(8), isnull( Orders.CreatedOn, 0 ), 112),
    promisedate = convert( nvarchar(8), isnull( Orders.ActDelDate, 0 ), 112),
    requestedshipdate = convert( nvarchar(8), isnull( Orders.ActLoadDate, 0 ), 112),
    linuser1 = case coalesce( InvoiceDetailPointOfService.[Description], InvoiceGroupPointOfService.[Description], 'N' )
        when 'Origin' then 'O'
        when 'Destination' then 'D'
        else 'N' end,
    linuser2 = dbo.EdiTextCleaner( case
        when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( DetailCustomerInvoiceReportEDIItem.UnitOfMeasure2, '' )
        else isnull( GroupCustomerInvoiceReportEDIItem.UnitOfMeasure2, '' ) end ),
    linuser3 =
        dbo.EdiTextCleaner( case
            case
                when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( DetailCustomerInvoiceReportEDIItem.LocationCode1, '' )
                else isnull( GroupCustomerInvoiceReportEDIItem.LocationCode1, '' )
            end
        when 'LH1' then case when isnull( OrigSITInformation.[Authorization], '' ) != '' then 'WO' else 'PW' end
        else case
                when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( DetailCustomerInvoiceReportEDIItem.LocationCode1, '' )
                else isnull( GroupCustomerInvoiceReportEDIItem.LocationCode1, '' )
            end
        end ),
    linuser4 =
        dbo.EdiTextCleaner( case
            case
                when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( DetailCustomerInvoiceReportEDIItem.LocationCode2, '' )
                else isnull( GroupCustomerInvoiceReportEDIItem.LocationCode2, '' )
            end
        when 'LH2' then case when isnull( DestSITInformation.[Authorization], '' ) != '' then 'WD' else 'DA' end
        else case
                when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( DetailCustomerInvoiceReportEDIItem.LocationCode2, '' )
                else isnull( GroupCustomerInvoiceReportEDIItem.LocationCode2, '' )
            end
        end ),
    linuser5 = ( convert( float, 100 ) - ( case
        when DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null then isnull( InvoicedDetail.Discount, 0 )
        else isnull( InvoicedGroup.Discount, 0 ) end ) ) / convert( float, 100 ),

    priceratio = 1,
    taxcode = '',
    taxableamount = 0,
    taxstate = '',
    linetaxableamt = 0,
    linetaxamt = 0,
    linetaxsched = '',
    linetaxcd = ''

from InvoicedHeader
inner join CustomerInvoiceReport on CustomerInvoiceReport.CustomerNumber = InvoicedHeader.CustomerNumber
inner join InvoiceStatus on InvoiceStatus.InvoiceStatusID = InvoicedHeader.InvoiceStatusFID
inner join InvoicedGroup on InvoicedGroup.IHPriKey = InvoicedHeader.IHPriKey
inner join Orders on Orders.PriKey = InvoicedHeader.OrdPriKey
left outer join CustomerInvoiceReportEDIItem as GroupCustomerInvoiceReportEDIItem on GroupCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID =
    InvoicedGroup.CustomerInvoiceReportEDIItemFID
left outer join InvoicedDetail on InvoicedDetail.IGPriKey = InvoicedGroup.IGPriKey
left outer join CustomerInvoiceReportEDIItem as DetailCustomerInvoiceReportEDIItem on DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID =
    InvoicedDetail.CustomerInvoiceReportEDIItemFID
left outer join PointOfService as InvoiceGroupPointOfService on InvoiceGroupPointOfService.PointOfServiceID = InvoicedGroup.PointOfServiceFID
left outer join PointOfService as InvoiceDetailPointOfService on InvoiceDetailPointOfService.PointOfServiceID = InvoicedDetail.PointOfServiceFID
left outer join SITType as OrigSITType on OrigSITType.Type = 'Origin'
left outer join SITInformation as OrigSITInformation on OrigSITInformation.OrdersFID = Orders.PriKey and OrigSITInformation.SITTypeFID = OrigSITType.SITTypeID
left outer join SITType as DestSITType on DestSITType.Type = 'Destination'
left outer join SITInformation as DestSITInformation on DestSITInformation.OrdersFID = Orders.PriKey and DestSITInformation.SITTypeFID = DestSITType.SITTypeID
left outer join MilitaryOrder on MilitaryOrder.OrdersFID = Orders.PriKey
left outer join MilitaryBase as OrigMilitaryBase on OrigMilitaryBase.MilitaryBaseID = MilitaryOrder.OriginBaseFID
left outer join MilitaryBase as DestMilitaryBase on DestMilitaryBase.MilitaryBaseID = MilitaryOrder.DestinationBaseFID
left outer join MilitaryBase as OrderingMilitaryBase on OrderingMilitaryBase.MilitaryBaseID = MilitaryOrder.OrderingBaseFID
left outer join MilitaryCarrier on MilitaryCarrier.MilitaryCarrierID = MilitaryOrder.MilitaryCarrierFID
left outer join MilitaryCarrierPayeeCode on MilitaryCarrierPayeeCode.MilitaryCarrierFID = MilitaryOrder.MilitaryCarrierFID and MilitaryCarrierPayeeCode.IsDefault = 1
left outer join MilitaryWeight on MilitaryWeight.OrdersFID = Orders.PriKey
left outer join AddressType as OrigAddressType on OrigAddressType.TypeName = 'Origin'
left outer join OrderAddress as OrigOrderAddress on OrigOrderAddress.OrderFID = Orders.PriKey and OrigOrderAddress.AddressTypeFID = OrigAddressType.AddressTypeID
left outer join CountryCodeStandard as OrigCountry on OrigCountry.CountryCodeStandardID = OrigOrderAddress.CountryCodeStandardFID
left outer join AddressType as DestAddressType on DestAddressType.TypeName = 'Destination'
left outer join OrderAddress as DestOrderAddress on DestOrderAddress.OrderFID = Orders.PriKey and DestOrderAddress.AddressTypeFID = DestAddressType.AddressTypeID
left outer join CountryCodeStandard as DestCountry on DestCountry.CountryCodeStandardID = DestOrderAddress.CountryCodeStandardFID
left outer join RateTypes as InvoiceGroupRateType on InvoiceGroupRateType.RTypePriKey = InvoicedGroup.RateTypeFID
left outer join RateTypes as InvoiceDetailRateType on InvoiceDetailRateType.RTypePriKey = InvoicedDetail.RateTypeFID
left outer join EDIMileageType as InvoiceGroupEDIMileageType on InvoiceGroupEDIMileageType.EDIMileageTypeID = GroupCustomerInvoiceReportEDIItem.EDIMileageTypeFID
left outer join EDIMileageType as InvoiceDetailEDIMileageType on InvoiceDetailEDIMileageType.EDIMileageTypeID = DetailCustomerInvoiceReportEDIItem.EDIMileageTypeFID
left outer join
(
    select
        OrdPriKey = InnerXtraStop.OrdPriKey,
        XtraStopID = min( InnerXtraStop.PriKey )
    from XtraStop as InnerXtraStop
    group by InnerXtraStop.OrdPriKey
) as FirstExtraStop on FirstExtraStop.OrdPriKey = Orders.PriKey
left outer join XtraStop on XtraStop.PriKey = FirstExtraStop.XtraStopID
left outer join XtraStopAddress on XtraStopAddress.XtraStopFID = FirstExtraStop.XtraStopID
left outer join CountryCodeStandard as XtraStopCountryCodeStandard on XtraStopCountryCodeStandard.CountryCodeStandardID = XtraStopAddress.CountryCodeStandardFID

where InvoicedHeader.InvoiceDate >= dateadd( D, -30, datediff( D, 0, getdate() ) ) and
    InvoiceStatus.Status = 'Invoice' and
    ( ( InvoicedDetail.IDPriKey is not null and DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null ) or
    ( InvoicedDetail.IDPriKey is null and GroupCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null ) )
GO
GRANT SELECT ON  [dbo].[EDIInvoice] TO [MssExec]
GRANT INSERT ON  [dbo].[EDIInvoice] TO [MssExec]
GRANT DELETE ON  [dbo].[EDIInvoice] TO [MssExec]
GRANT UPDATE ON  [dbo].[EDIInvoice] TO [MssExec]
GO
Uses