Archives
Trending
Support
Login
clear text
XML
Django
JavaScript
MATLAB
C
C++
C#
Python
SQL
Shell
Bash
Markdown
YAML
JSON
HTML
CSS
PHP
Java
Ruby
Go
Rust
Swift
Kotlin
Arduino
TypeScript
Perl
Autohotkey
Lua
SQF
R
Scala
Haskell
Groovy
Dart
Clojure
VB.NET
Objective-C
PowerShell
Bash
CoffeeScript
Verilog
SELECT CONCAT(CONCAT(SPERSO.VORNAME, ' '),SPERSO.NAME) AS "Sales rep employee name" ,SPERSO.PERS_NR AS "Sales rep number" ,ASENDUNG.ABS_NAME1 AS "Name of the sender" ,ASENDUNG.EMP_NAME1 AS "Name of the receiver" ,SCUST.INFO AS "Reason for cancelation" ,ATOURCUSTOMFIELDV.CFBOOLEAN2 AS "CMR check box" ,asendung.check5 ,ATASEND.TAINFO9 ,AKopf.Auftragsnummer ,ASendung.Sendungsnummer ,ATour.tournummer ,ATour.EXTTOURNUMMER ,FrachtAG.NAME1 ,ASendung.Info4 ,(SELECT SUM(STATISTIKBETRAGRG) FROM akopf ak left join asdgabrechnung asd ON ak.nr = asd.akopfnr left join skunden ON ak.FRACHTAGNR = skunden.nr left join skufaktu ON skunden.nr = skufaktu.nr left join asendung ase ON ak.nr = ase.akopfnr left join atasend AT ON ase.sendungid = AT.asendungid left join atour ON AT.tournr = atour.nr left join UDX_VIEW_CUSTOMREALLOCCNTOUR ON atour. nr = UDX_VIEW_CUSTOMREALLOCCNTOUR.tournr WHERE ak.nr = akopf.nr AND ase.sendungid = asendung.sendungid AND AT.nr = atasend.nr GROUP BY asendung.sendungid,AT.nr) ,(SELECT SUM(BETRAGRG) FROM akopf ak left join asdgabrechnung asd ON ak.nr = asd.akopfnr left join skunden ON ak.FRACHTAGNR = skunden.nr left join skufaktu ON skunden.nr = skufaktu.nr left join asendung ase ON ak.nr = ase.akopfnr left join atasend AT ON ase.sendungid = AT.asendungid left join atour ON AT.tournr = atour.nr left join UDX_VIEW_CUSTOMREALLOCCNTOUR ON atour. nr = UDX_VIEW_CUSTOMREALLOCCNTOUR.tournr WHERE ak.nr = akopf.nr AND ase.sendungid = asendung.sendungid AND AT.nr = atasend.nr GROUP BY asendung.sendungid,AT.nr) ,CASE WHEN AKOPF.AUFTRAGSREFERENZ3 IS NULL THEN PVORGABEWGS.ISOWAEHRUNGSCODE ELSE AKOPF.AUFTRAGSREFERENZ3 END ,(SELECT SUM(STATISTIKBBETRAG/asd.ABRGRUPPEITEMSCOUNT ) FROM atasend AT left join asdgabrechnung asd ON AT.nr = asd.ataid left join asendung ase ON ase.sendungid = AT.asendungid left join akopf ak ON ase.akopfnr = ak.nr left join akondition ON asd.AKONDITION = akondition.nr left join skondi0 ON akondition.KONDITIONSNR = skondi0.nr left join ASDGABRECHNUNGPOS asdp ON asd.AABRGRUPPE = asdp.ABRGRUPPE left join SLEISTUNGSARTEN s ON asdp.IDLEISTUNGSART = s.ID WHERE skondi0.nr NOT IN (910368,3996738) AND AT.nr = atasend.nr AND ase.sendungid = asendung.sendungid AND UPPER(s.MATCHCODE) <> UPPER('sconto') AND UPPER(s.MATCHCODE) <> UPPER('ADJUSTMENT') AND UPPER(s.MATCHCODE) <> UPPER('CURRATE') AND akopf.nr = ak.nr GROUP BY AT.nr ) ,(CASE WHEN (SELECT SUM(STATISTIKBETRAGRG) FROM akopf ak left join asdgabrechnung asd ON ak.nr = asd.akopfnr left join skunden ON ak.FRACHTAGNR = skunden.nr left join skufaktu ON skunden.nr = skufaktu.nr left join asendung ase ON ak.nr = ase.akopfnr left join atasend AT ON ase.sendungid = AT.asendungid left join atour ON AT.tournr = atour.nr left join UDX_VIEW_CUSTOMREALLOCCNTOUR ON atour. nr = UDX_VIEW_CUSTOMREALLOCCNTOUR.tournr WHERE ak.nr = akopf.nr AND ase.sendungid = asendung.sendungid AND AT.nr = atasend.nr GROUP BY asendung.sendungid,AT.nr) IS NULL THEN 0 ELSE (SELECT SUM(STATISTIKBETRAGRG) FROM akopf ak left join asdgabrechnung asd ON ak.nr = asd.akopfnr left join skunden ON ak.FRACHTAGNR = skunden.nr left join skufaktu ON skunden.nr = skufaktu.nr left join asendung ase ON ak.nr = ase.akopfnr left join atasend AT ON ase.sendungid = AT.asendungid left join atour ON AT.tournr = atour.nr left join UDX_VIEW_CUSTOMREALLOCCNTOUR ON atour. nr = UDX_VIEW_CUSTOMREALLOCCNTOUR.tournr WHERE ak.nr = akopf.nr AND ase.sendungid = asendung.sendungid AND AT.nr = atasend.nr GROUP BY asendung.sendungid,AT.nr)END - CASE WHEN (SELECT SUM(STATISTIKBBETRAG/asd.ABRGRUPPEITEMSCOUNT ) FROM atasend AT left join asdgabrechnung asd ON AT.nr = asd.ataid left join asendung ase ON ase.sendungid = AT.asendungid left join akopf ak ON ase.akopfnr = ak.nr left join akondition ON asd.AKONDITION = akondition.nr left join skondi0 ON akondition.KONDITIONSNR = skondi0.nr left join ASDGABRECHNUNGPOS asdp ON asd.AABRGRUPPE = asdp.ABRGRUPPE left join SLEISTUNGSARTEN s ON asdp.IDLEISTUNGSART = s.ID WHERE skondi0.nr NOT IN (910368,3996738) AND AT.nr = atasend.nr AND ase.sendungid = asendung.sendungid AND UPPER(s.MATCHCODE) <> UPPER('sconto')AND UPPER(s.MATCHCODE) <> UPPER('ADJUSTMENT') AND UPPER(s.MATCHCODE) <> UPPER('CURRATE') AND akopf.nr = ak.nr GROUP BY AT.nr )IS NULL THEN 0 ELSE (SELECT SUM(STATISTIKBBETRAG/asd.ABRGRUPPEITEMSCOUNT ) FROM atasend AT left join asdgabrechnung asd ON AT.nr = asd.ataid left join asendung ase ON ase.sendungid = AT.asendungid left join akopf ak ON ase.akopfnr = ak.nr left join akondition ON asd.AKONDITION = akondition.nr left join skondi0 ON akondition.KONDITIONSNR = skondi0.nr left join ASDGABRECHNUNGPOS asdp ON asd.AABRGRUPPE = asdp.ABRGRUPPE left join SLEISTUNGSARTEN s ON asdp.IDLEISTUNGSART = s.ID WHERE skondi0.nr NOT IN (910368,3996738) AND AT.nr = atasend.nr AND ase.sendungid = asendung.sendungid AND UPPER(s.MATCHCODE) <> UPPER('sconto')AND UPPER(s.MATCHCODE) <> UPPER('ADJUSTMENT') AND UPPER(s.MATCHCODE) <> UPPER('CURRATE') AND akopf.nr = ak.nr GROUP BY AT.nr ) END ) ,akopf.AEXTERNAUFTRAGSNR ,(SELECT ISOALPHA2CODE FROM SLAND WHERE ATASend.VON_LAND = SLAND.LAND) ,ATASend.VON_PLZ ,ATASend.VON_ORT1 ,(SELECT ISOALPHA2CODE FROM SLAND WHERE ATASend.NACH_LAND = SLAND.LAND) ,ATASend.NACH_PLZ ,ATASend.NACH_ORT1 ,TO_DATE(TO_CHAR(ASendung.VONLADEDATUMZEIT,'hh24:mi'),'hh24:mi') ,TO_DATE(TO_CHAR(ASendung.BISLADEDATUMZEIT,'hh24:mi'),'hh24:mi') ,TO_DATE(TO_CHAR(ASendung.VONLADEDATUMZEIT, 'dd.mm.yyyy'), 'dd.mm.yyyy') ,TO_DATE(TO_CHAR(ASendung.BISLADEDATUMZEIT, 'dd.mm.yyyy'), 'dd.mm.yyyy') ,TO_DATE(TO_CHAR(ATASEND.LOADINGDATEPLANNEDSTART,'hh24:mi'),'hh24:mi') ,TO_DATE(TO_CHAR(ATASEND.LOADINGDATEPLANNEDEND,'hh24:mi'),'hh24:mi') ,TO_DATE(TO_CHAR(ATASEND.LOADINGDATEPLANNEDSTART, 'dd.mm.yyyy'), 'dd.mm.yyyy') ,TO_DATE(TO_CHAR(ATASEND.LOADINGDATEPLANNEDEND, 'dd.mm.yyyy'), 'dd.mm.yyyy') ,TO_DATE(TO_CHAR(Atasend.LADEDATUMSTARTIST,'dd.mm.yyyy'),'dd.mm.yyyy') ,TO_DATE(TO_CHAR(Atasend.LADEDATUMSTARTIST,'hh24:mi'),'hh24:mi') ,TO_DATE(TO_CHAR(Atasend.LADEDATUMENDEIST,'dd.mm.yyyy'),'dd.mm.yyyy') ,TO_DATE(TO_CHAR(Atasend.LADEDATUMENDEIST,'hh24:mi'),'hh24:mi') ,TO_DATE(TO_CHAR(ASendung.VONLIEFERDATUMZEIT,'hh24:mi'),'hh24:mi') ,TO_DATE(TO_CHAR(ASendung.BISLIEFERDATUMZEIT,'hh24:mi'),'hh24:mi') ,TO_DATE(TO_CHAR(ASendung.VONLIEFERDATUMZEIT,'dd.mm.yyyy'),'dd.mm.yyyy') ,TO_DATE(TO_CHAR(ASendung.BISLIEFERDATUMZEIT, 'dd.mm.yyyy'), 'dd.mm.yyyy') ,TO_DATE(TO_CHAR(ATASEND.DELIVERYDATEPLANNEDSTART,'hh24:mi'),'hh24:mi') ,TO_DATE(TO_CHAR(ATASEND.DELIVERYDATEPLANNEDEND,'hh24:mi'),'hh24:mi') ,TO_DATE(TO_CHAR(ATASEND.DELIVERYDATEPLANNEDSTART,'dd.mm.yyyy'),'dd.mm.yyyy') ,TO_DATE(TO_CHAR(ATASEND.DELIVERYDATEPLANNEDEND,'dd.mm.yyyy'),'dd.mm.yyyy') ,TO_DATE(TO_CHAR(ATASEND.LIEFERDATUMSTARTIST, 'dd.mm.yyyy'), 'dd.mm.yyyy') ,TO_DATE(TO_CHAR(ATASEND.LIEFERDATUMSTARTIST,'hh24:mi'),'hh24:mi') ,TO_DATE(TO_CHAR(Atasend.LIEFERDATUMENDEIST, 'dd.mm.yyyy'), 'dd.mm.yyyy') ,TO_DATE(TO_CHAR(Atasend.LIEFERDATUMENDEIST,'hh24:mi'),'hh24:mi') ,aSENDUNG.INFO16 ,ATour.LKWKZ ,ATour.ANHAENGERKZ ,Unternehmer.Name1 ,DECODE(ATASEND.LOCKSTATUS,0,'Free for planning',1,'In plan',2,'Planned',3,'Planning completed',4,'TO finished') ,HAENGERTYP.Bezeichnung ,sverkaufsart_sdg.bezeichnung ,STOURKATEGORIEN.BEZEICHNUNG ,ATASEND.TAINFO1 ,ATASEND.TAINFO2 ,ATASEND.TAINFO3 ,ATASEND.TAINFO4 ,ATASEND.TAINFO5 ,ATASEND.TAINFO6 ,ATASEND.TAINFO7 ,ATASEND.TAINFO8 ,Atour.TOURZUSATZINFO15 ,suser.fullname ,SPVERKAEUFER.VORNAME ||' '|| SPVERKAEUFER.NAME ,TOURUSER.fullname ,SHierarchieorder.Bezeichnung ,SHierarchie.Bezeichnung ,(SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.INHALT T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR) ,(SELECT SUM(AT.ANZAHL) FROM ATAPOSITION AT WHERE ATASEND.NR = AT.ATASENDNR GROUP BY ATASEND.NR) ,(SELECT SUM(AT.GEWICHTT) FROM ATAPOSITION AT WHERE ATASEND.NR = AT.ATASENDNR GROUP BY ATASEND.NR) ,(SELECT SUM(AT.LAENGE) FROM ATAPOSITION AT WHERE ATASEND.NR = AT.ATASENDNR GROUP BY ATASEND.NR) ,(SELECT SUM(AT.BREITE) FROM ATAPOSITION AT WHERE ATASEND.NR = AT.ATASENDNR GROUP BY ATASEND.NR) ,(SELECT SUM(AT.HOEHE) FROM ATAPOSITION AT WHERE ATASEND.NR = AT.ATASENDNR GROUP BY ATASEND.NR) ,(SELECT SUM(AT.VOLMETER) FROM ATAPOSITION AT WHERE ATASEND.NR = AT.ATASENDNR GROUP BY ATASEND.NR) ,(SELECT SUM(AT.VOLMETER3) FROM ATAPOSITION AT WHERE ATASEND.NR = AT.ATASENDNR GROUP BY ATASEND.NR) ,( SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.VERPACKUNG T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR) ,ASendung.Gefahrgut ,(SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.GGVSBAMNR T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR) ,Asendung.Check0 ,(SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.floatfield1 T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR) ,(SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.floatfield2 T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR) ,SCUSTOMTYPEVALUES.BEZEICHNUNG ,(SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.GGVSUNNR T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR) ,(SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.GGVSKLASSE T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR) ,(SELECT LISTAGG(T.T,';') within GROUP (ORDER BY t.t) FROM (SELECT DISTINCT AT.GGVSTCNAME T , AT.ATASENDNR FROM ATAPOSITION AT)t WHERE ATASEND.NR = T.ATASENDNR GROUP BY ATASEND.NR) ,ATASend.km ,ATASEND.ABSINFO1 ,ATASEND.EMPINFO1 ,asendung.check6 ,asendung.check1 ,asendung.check3 ,( SELECT info10 FROM SFUHRP WHERE atour.LKWNR = SFUHRP.FZGNR) FROM AKopf LEFT JOIN ASendung ON ASendung.AKopfNr = AKopf.Nr LEFT JOIN ATASend ON ATASend.ASendungID = ASendung.SendungID LEFT JOIN ATour ON ATASend.TourNr = ATour.Nr Left join SKunden FrachtAG ON AKopf.FrachtAgNr = FrachtAG.Nr Left join SWAEHRUNG PVORGABEWGS ON asendung.WAEHRUNGIDKUNDE = PVORGABEWGS.ID Left join SKunden Unternehmer ON ATour.Unternehmer = Unternehmer.Nr Left join SFuhrp Haenger ON ATour.AnhaengerNr = Haenger.FzgNr Left join SFZGTYP HAENGERTYP ON HAENGER.FZGTYP = HAENGERTYP.NR Left join SVerkaufsart sverkaufsart_sdg ON asendung.verkaufsartid = sverkaufsart_sdg.id Left join STOURKATEGORIEN ON ATour.TOURKATEGORIE = STOURKATEGORIEN.ID Left join suser ON suser.nr = akopf.userid Left join SPERSO SPVERKAEUFER ON AKOPF.VERKAEUFER = SPVERKAEUFER.NR Left join SUSER TOURUSER ON ATour.ERSTELLTVON = TOURUSER.NR Left join shierarchie shierarchieorder ON AKOPF.HIERARCHIE = SHIERARCHIEORDER.ID Left join shierarchie ON atour.Hierarchie = SHierarchie.ID LEFT JOIN shierarchie toorg ON ATASEND.Hierarchie = TOORG.ID Left join SCUSTOMTYPEVALUES ON asendung.SDGTYP11 = SCUSTOMTYPEVALUES.id Left join STransportart STransportart_Sdg ON ASendung.Transportart = STransportart_Sdg.ID left join UDX_VIEW_BPGROUP t ON FrachtAG.NR = t.KundenNr left join SHierarchie SHI ON akopf.ALTHIERARCHIE = SHi.ID LEFT JOIN SKUFAKTU ON FrachtAG.Nr = SKUFAKTU.NR LEFT JOIN SLAND ON SLAND.LAND = SKUFAKTU.USTIDLAND LEFT JOIN sbranche ON FrachtAG.BRANCHE = sbranche.id LEFT JOIN SCUSTOMTYPEVALUES scu ON Unternehmer.GPKATEGORIE6 = scu.id LEFT JOIN SCUSTOMTYPEVALUES sca ON FrachtAG.GPKATEGORIE6 = sca.id LEFT JOIN ATRANSPORTORDERCUSTOMFIELDV ON ATASEND.NR = ATRANSPORTORDERCUSTOMFIELDV.ID LEFT JOIN SPERSO ON SPERSO.NR = AKOPF.VERKAEUFER LEFT JOIN ATOURCUSTOMFIELDV ON ATOURCUSTOMFIELDV.ID = ATOUR.NR Left join SCUSTOMTYPEVALUES SCUST ON asendung.SDGTYP12 = SCUST.id WHERE 0=0 AND akopf.ordertype = 0 AND akopf.INRECYCLEBIN = 0 AND atour.TOURNUMMER = 285157;
Mark as private (unlisted)
for 30 minutes
for 6 hours
for 1 day
for 1 week
for 1 month
for 1 year
everlasting (like CCCP)
