Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement