151 lines
4.4 KiB
TypeScript
151 lines
4.4 KiB
TypeScript
export let getOrderToSend = `
|
|
select * from (
|
|
Select IdAdresse as addressId,
|
|
LieferAdressBez as addressAlias,
|
|
LEFT(ArtikelVariantenAlias, charindex(' ', ArtikelVariantenAlias) - 1) item,
|
|
IdArtikelVarianten as article,
|
|
ArtikelVariantenAlias as articleAlias,
|
|
IdAuftragsAbruf as releaseNumber,
|
|
AuftragsNummer AS Header,
|
|
AuftragsNummer as CustomerLineItemNo,
|
|
AbrufNummer AS CustomerReleaseNumber,
|
|
AbrufMengeVPK AS Pallets,
|
|
AbrufMenge AS QTY,
|
|
IdAdresse AS CUSTOMERID,
|
|
AbrufLadeDatum AS LoadingDate,
|
|
AbrufLiefertermin AS DeliveryDate
|
|
,carrierAV
|
|
,singleTrip
|
|
,roundTrip
|
|
,countryAv
|
|
,zipCode
|
|
,streetAddress
|
|
,city -- split on here by ,
|
|
--,OrderStatus = 'loading'
|
|
,ac.costCenter -- also called pfc
|
|
,pkg.pkgHeight
|
|
,pkg.pkgLengh
|
|
,pkg.pkgWidth
|
|
,ROUND((ac.weight * pkg.palletCount / 1000) + pkg.totalPKGWeight,2)as pkgWeight
|
|
,AbrufStatus as status
|
|
,remark
|
|
,ac.artileType
|
|
--,*
|
|
FROM alplaprod_test1.dbo.V_TrackerAuftragsAbrufe (nolock) x
|
|
|
|
--av info
|
|
left join
|
|
(SELECT [IdArtikelvarianten] as article
|
|
,[FibuKontenKontoNr] as costCenter
|
|
,ArtikelGewicht as weight,
|
|
s.pkgId
|
|
,artikelvariantentypbez as artileType
|
|
FROM [AlplaPROD_test1].[dbo].[V_Artikelvarianten_BASIS] (nolock) x
|
|
|
|
left join
|
|
(
|
|
select * from (select
|
|
ROW_NUMBER() OVER(PARTITION BY [IdArtikelvarianten] ORDER BY gueltigabDatum DESC) AS rn
|
|
,[IdArtikelvarianten] as article
|
|
,IdVpkVorschrift as pkgId
|
|
from [AlplaPROD_test1].[dbo].[T_HistoryVK] (nolock)) a where rn = 1
|
|
) as s
|
|
on
|
|
x.[IdArtikelvarianten] = s.article
|
|
) as ac
|
|
on
|
|
x.IdArtikelVarianten = ac.article
|
|
|
|
-- transport part of query
|
|
left join
|
|
(SELECT [IdHistoryTransportkosten]
|
|
,[IdLieferadresse] as customerAddressAV
|
|
,[IdSpediteuradresse] as carrierAV
|
|
,[GueltigabDatum] as validDate
|
|
,[Einzelfahrtkosten] as singleTrip
|
|
,[Rundfahrtkosten] as roundTrip
|
|
,[EinzelfahrtkostenProKubikmeter] as singletripCostsperCubicMeter
|
|
,[RundfahrtkostenProKubikmeter] as roundSingletripCostsperCubicMeter
|
|
,[Standard] as standard
|
|
,[Aktiv] as active
|
|
--,[FWEinzelfahrtkosten]
|
|
--,[FWRundfahrtkosten]
|
|
--,[FWEinzelfahrtkostenProKubikmeter]
|
|
--,[FWRundfahrtkostenProKubikmeter]
|
|
FROM [AlplaPROD_test1].[dbo].[T_HistoryTransportkosten] (nolock)
|
|
|
|
where Standard = 1 and Aktiv = 1) as carrier
|
|
|
|
on x.IdAdresse = carrier.customerAddressAV
|
|
|
|
-- address stuff
|
|
left join
|
|
(SELECT [IdAdressen] as addressAV
|
|
,[IdAdressentyp] as addressType -- 1 customer,2 supplier, 4 transport
|
|
--,[IdZahlKond]
|
|
--,[IdMwst]
|
|
,[Bezeichnung] as addressName
|
|
,[IdStaaten] as countryAv
|
|
,[PLZ] as zipCode
|
|
,[Strasse] as streetAddress
|
|
,[PLZPostfach] as poBox
|
|
,[Postfach] as mailbox
|
|
,[Ort] as city
|
|
,[Tel] as customerPhone
|
|
,[DebNr] as debitorNr
|
|
,xy.[Bonus] as bonus
|
|
,[Bemerkung] as remark
|
|
,[Aktiv] as active
|
|
,Entfernung as distanceKM
|
|
,Transportzeit as transportTime
|
|
,IdLieferkondition as deliveryCondtionAV
|
|
,delc.deliveryContionAlias
|
|
,delc.deliveryContionAbv
|
|
--,ac.costCenter
|
|
FROM [AlplaPROD_test1].[dbo].[T_Adressen] (nolock) xy
|
|
|
|
--delivery condtion details
|
|
left join
|
|
(SELECT [IdLieferkondition] as deliveryCondtionAV
|
|
,[Bezeichnung] as deliveryContionAlias
|
|
,[Kurzbezeichnung] as deliveryContionAbv
|
|
,[Bemerkung] as deliveryContionRemark
|
|
,[Aktiv] as active
|
|
FROM [AlplaPROD_test1].[dbo].[T_Lieferkonditionen] (nolock)) as delC
|
|
|
|
on xy.IdLieferkondition = delC.deliveryCondtionAV
|
|
) as del
|
|
on
|
|
x.IdAdresse = del.addressAV
|
|
|
|
-- pkg info
|
|
left join
|
|
(
|
|
SELECT [IdVpkVorschrift] as pkgId
|
|
,[Aktiv] as active
|
|
,[Bezeichnung] as alias
|
|
,[AnzahlAVProVpk] as palletCount
|
|
,[AnzahlVpkProLKW] as totalTruck
|
|
,[AnzahlKistenProKarton]
|
|
,[BruttoGewicht] / 1000 as totalPKGWeight
|
|
--,[AnzahlAVProHE]
|
|
,[VpkDimensionenHoehe] as pkgHeight
|
|
,[VpkDimensionenBreite] as pkgWidth
|
|
,[VpkDimensionenTiefe] as pkgLengh
|
|
FROM [AlplaPROD_test1].[dbo].[V_Vpk_BASIS]
|
|
)as pkg
|
|
|
|
on
|
|
ac.pkgId = pkg.pkgId
|
|
|
|
WHERE AbrufStatus = 1
|
|
--and AbrufLiefertermin between DATEADD(HOUR, -[from], getdate()) and DATEADD(HOUR, [to], getdate())-- this number will be grabbed from the db with a default of 24hours
|
|
and AbrufLadeDatum between DATEADD(HOUR, -[from], getdate()) and DATEADD(HOUR, [to], getdate())-- this number will be grabbed from the db with a default of 24hours
|
|
and deliveryContionAbv not in ('EXW')
|
|
|
|
--ORDER BY AbrufLiefertermin)
|
|
) a
|
|
|
|
where releaseNumber = [releaseToProcess]
|
|
`;
|