Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT T1."KD_VORGANGS_NR" "c1", T1."KD_VORGANGS_ART" "c2", T1."POSITIONS_NR" "c3", T1."UNTER_POSITIONS_NR" "c4", T1."AUFNAHMEDATUM" "c5", T1."POSITIONS_TYP" "c6", T1."KUNDEN_NR" "c7", T1."AUFTRART" "c8", DECODE(T1."LIEFERMENGEN_EINHEIT",'TSD',T1."AUFTRAGS_MENGE"*1000 ,T1."AUFTRAGS_MENGE") "c9", T1."Z_L_TERMIN" "c10", T1."WUNSCH_LIEFERTERMIN" "c11", T1."ARTIKEL_NR" "c12", T1."BEZEICHNUNG_1" "c13", T1."PRODUKTGRUPPE" "c14", ((T1."POSITIONSWERT_EXTERN"*1.0e0)/T1."WAEHRUNGSEINHEIT")*T1."KURS" "c15", T1."WAEHRUNGSEINHEIT" "c16", T1."KURS" "c17", T1."FAKTURIERTER_BETRAG" "c18", DECODE(T1."LIEFERMENGEN_EINHEIT",'TSD',T1."MENGE_IST"*1000 ,T1."MENGE_IST") "c19", T1."POSITIONS_STATUS" "c20", T1."RUECKMELDE_NR" "c21", DECODE(T1."Z_L_TERMIN",'00000000',T1."WUNSCH_LIEFERTERMIN" ,T1."Z_L_TERMIN") "c22", DECODE(T1."Z_L_TERMIN",'00000000',T1."WUNSCH_LIEFERTERMIN" ,T1."Z_L_TERMIN") "c23", DECODE(T1."KD_VORGANGS_ART",'AU',ROUND(((T1."POSITIONSWERT_EXTERN"*1.0e0)/T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2) ,DECODE(T1."POSITIONS_TYP",'H',0 ,ROUND((((((T1."AUFTRAGS_MENGE"*1.0e0)/T2."PREISEINHEIT")*T2."GRUNDPREIS")*1.0e0)/T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2))) "c24", T3."KUNDEN_NAME" "c26", DECODE(T1."KD_VORGANGS_ART",'AU',ROUND(((T1."FAKTURIERTER_BETRAG" - T1."STORNO_BETRAG")*T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2) ,DECODE(T1."POSITIONS_TYP",'H',0 ,ROUND((((((T1."MENGE_IST"*T2."GRUNDPREIS")*1.0e0)/T2."PREISEINHEIT")*1.0e0)/T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2))) "c27", CASE WHEN DECODE(T1."KD_VORGANGS_ART",'AU',ROUND(((T1."POSITIONSWERT_EXTERN"*1.0e0)/T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2) ,DECODE(T1."POSITIONS_TYP",'H',0 ,ROUND((((((T1."AUFTRAGS_MENGE"*1.0e0)/T2."PREISEINHEIT")*T2."GRUNDPREIS")*1.0e0)/T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2)))<DECODE(T1."KD_VORGANGS_ART",'AU',ROUND(((T1."FAKTURIERTER_BETRAG" - T1."STORNO_BETRAG")*T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2) ,DECODE(T1."POSITIONS_TYP",'H',0 ,ROUND((((((T1."MENGE_IST"*T2."GRUNDPREIS")*1.0e0)/T2."PREISEINHEIT")*1.0e0)/T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2))) THEN 0 ELSE DECODE(T1."KD_VORGANGS_ART",'AU',ROUND(((T1."POSITIONSWERT_EXTERN"*1.0e0)/T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2) ,DECODE(T1."POSITIONS_TYP",'H',0 ,ROUND((((((T1."AUFTRAGS_MENGE"*1.0e0)/T2."PREISEINHEIT")*T2."GRUNDPREIS")*1.0e0)/T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2))) - DECODE(T1."KD_VORGANGS_ART",'AU',ROUND(((T1."FAKTURIERTER_BETRAG" - T1."STORNO_BETRAG")*T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2) ,DECODE(T1."POSITIONS_TYP",'H',0 ,ROUND((((((T1."MENGE_IST"*T2."GRUNDPREIS")*1.0e0)/T2."PREISEINHEIT")*1.0e0)/T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2))) END "c29", T1."AU_DISPONENT" "c31", T2."GRUNDPREIS" "c32", T2."PREISEINHEIT" "c33", T1."POSITIONSWERT_EXTERN" "c34", CONCAT(CONCAT(SUBSTR(DECODE(T1."Z_L_TERMIN",'00000000',T1."WUNSCH_LIEFERTERMIN" ,T1."Z_L_TERMIN"),(LENGTH(DECODE(T1."Z_L_TERMIN",'00000000',T1."WUNSCH_LIEFERTERMIN" ,T1."Z_L_TERMIN")) - 2)+1 ,2) , '.') , SUBSTR(DECODE(T1."Z_L_TERMIN",'00000000',T1."WUNSCH_LIEFERTERMIN" ,T1."Z_L_TERMIN"),1 ,4)) "c35", SUBSTR(T1."ARTIKEL_NR",1 ,2) "c36", T1."ERLOESGRUPPE" "c37", DECODE(T1."LIEFERMENGEN_EINHEIT",'TSD',T1."AUFTRAGS_MENGE"*1000 ,T1."AUFTRAGS_MENGE") - DECODE(T1."LIEFERMENGEN_EINHEIT",'TSD',T1."MENGE_IST"*1000 ,T1."MENGE_IST") "c38", T1."INCOTERM_KENNER" "c39", T4."VORGANGS_STATUS" "c40", T4."VORGANGS_STATUS" "c41", T1."POSITIONS_STATUS" "c42"
- FROM "LAG84"."PKVP" T1, "LAG84"."PKVP" T2, "LAG84"."PKUN" T3, "LAG84"."PKVK" T4
- WHERE T1."KD_VORGANGS_NR"=T2."KD_VORGANGS_NR" AND T1."KD_VORGANGS_ART"=T2."KD_VORGANGS_ART" AND T1."POSITIONS_NR"=T2."POSITIONS_NR" AND T2."UNTER_POSITIONS_NR"=0 AND T3."KUNDEN_NR"=T1."KUNDEN_NR" AND T4."KD_VORGANGS_NR"=T1."KD_VORGANGS_NR" AND T4."KD_VORGANGS_ART"=T1."KD_VORGANGS_ART" AND T1."KD_VORGANGS_ART"<>'AN' AND T1."POSITIONS_STATUS"<6 AND NOT T1."PRODUKTGRUPPE" IS NULL AND NOT (DECODE(T1."Z_L_TERMIN",'00000000',T1."WUNSCH_LIEFERTERMIN" ,T1."Z_L_TERMIN")='000000') AND T1."PRODUKTGRUPPE">=CASE WHEN ('*'='*') THEN '0' ELSE '*' END AND T1."PRODUKTGRUPPE"<=CASE WHEN ('*'='*') THEN '9999' ELSE '*' END AND DECODE(T1."Z_L_TERMIN",'00000000',T1."WUNSCH_LIEFERTERMIN" ,T1."Z_L_TERMIN")>='20240701' AND DECODE(T1."Z_L_TERMIN",'00000000',T1."WUNSCH_LIEFERTERMIN" ,T1."Z_L_TERMIN")<='20250101' AND NOT (CASE WHEN (DECODE(T1."KD_VORGANGS_ART",'AU',ROUND((T1."POSITIONSWERT_EXTERN"/T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2) ,DECODE(T1."POSITIONS_TYP",'H',0 ,ROUND((((T1."AUFTRAGS_MENGE"/T2."PREISEINHEIT")*T2."GRUNDPREIS")/T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2)))<DECODE(T1."KD_VORGANGS_ART",'AU',ROUND(((T1."FAKTURIERTER_BETRAG" - T1."STORNO_BETRAG")*T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2) ,DECODE(T1."POSITIONS_TYP",'H',0 ,ROUND((((T1."MENGE_IST"*T2."GRUNDPREIS")/T2."PREISEINHEIT")/T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2)))) THEN 0 ELSE DECODE(T1."KD_VORGANGS_ART",'AU',ROUND((T1."POSITIONSWERT_EXTERN"/T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2) ,DECODE(T1."POSITIONS_TYP",'H',0 ,ROUND((((T1."AUFTRAGS_MENGE"/T2."PREISEINHEIT")*T2."GRUNDPREIS")/T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2))) - DECODE(T1."KD_VORGANGS_ART",'AU',ROUND(((T1."FAKTURIERTER_BETRAG" - T1."STORNO_BETRAG")*T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2) ,DECODE(T1."POSITIONS_TYP",'H',0 ,ROUND((((T1."MENGE_IST"*T2."GRUNDPREIS")/T2."PREISEINHEIT")/T1."WAEHRUNGSEINHEIT")*T1."KURS" ,2))) END =0) AND T1."ARTIKEL_NR" NOT LIKE '95-%'
- ORDER BY "c10" ASC, "c11" ASC, "c14" ASC, "c22" ASC, "c12" ASC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
