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 = '',
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
[dbo].[EDIInvoice]
