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;
