Skip to content

Instantly share code, notes, and snippets.

@ZSchneidi

ZSchneidi/gist:4201585

Created December 4, 2012 07:38
Show Gist options
  • Save ZSchneidi/4201585 to your computer and use it in GitHub Desktop.
Save ZSchneidi/4201585 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.sql
SELECT *
FROM
(SELECT grd_aufnr ,
grd_konto ,
grd_name_1 ,
grd_kd_grp ,
grd_aufpos ,
grd_datneu ,
grd_identnr ,
grd_ben ,
grd_lfdnr_eintl ,
grd_wunsch_term ,
grd_lief_term ,
grd_lief_termzk ,
grd_menge ,
grd_menge_gel ,
grd_menge_offen ,
grd_fklz ,
grd_bemerkung ,
grd_fkfs ,
grd_menge_fa ,
grd_menge_offen_fa ,
grd_prio ,
grd_ist_dat ,
grd_status_eilt ,
grd_dlz_tage ,
grd_status_clip ,
grd_status_av ,
grd_status_kd_beistellung ,
grd_status_material ,
grd_status_betriebsmittel ,
grd_status_freigabe_muster ,
grd_status_fremdbearb ,
grd_status_ubg ,
grd_herkunft
FROM
(SELECT grd_aufnr ,
grd_konto ,
grd_name_1 ,
grd_kd_grp ,
grd_aufpos ,
grd_datneu ,
grd_identnr ,
grd_ben ,
grd_lfdnr_eintl ,
grd_wunsch_term ,
grd_lief_term ,
grd_lief_termzk ,
grd_menge ,
grd_menge_gel ,
grd_menge_offen ,
grd_fklz ,
grd_bemerkung ,
grd_fkfs ,
grd_menge_fa ,
grd_menge_offen_fa ,
grd_prio ,
grd_ist_dat ,
grd_status_eilt ,
grd_dlz_tage ,
grd_status_clip ,
grd_status_av ,
grd_status_kd_beistellung ,
grd_status_material ,
grd_status_betriebsmittel ,
grd_status_freigabe_muster ,
grd_status_fremdbearb ,
grd_status_ubg ,
grd_herkunft ,
RANK() OVER (PARTITION BY grd_aufnr ORDER BY grd_herkunft) AS ranking
FROM
(SELECT *
FROM
(SELECT A.aufnr grd_aufnr ,
A.konto grd_konto ,
A2.name_1 grd_name_1 ,
A1.kd_grp grd_kd_grp ,
B.aufpos grd_aufpos ,
B.datneu grd_datneu ,
B.identnr grd_identnr ,
B1.ben grd_ben ,
C.lfdnr_eintl grd_lfdnr_eintl ,
C.wunsch_term grd_wunsch_term ,
C.lief_term grd_lief_term ,
C.lief_termzk grd_lief_termzk ,
C.menge grd_menge ,
C.menge_gel grd_menge_gel ,
(C.menge-C.menge_gel) grd_menge_offen ,
D.fklz grd_fklz ,
D.txt8 grd_bemerkung ,
D1.fkfs grd_fkfs ,
D1.menge grd_menge_fa ,
D1.menge_offen grd_menge_offen_fa ,
D2.rvc_prio grd_prio ,
D2.seterm_i grd_ist_dat ,
D2.status grd_status_eilt ,
D2.dlzeit grd_dlz_tage ,
D2.rvc_clipstatus grd_status_clip ,
E1.rvc_status grd_status_av ,
E2.rvc_status grd_status_kd_beistellung ,
E3.rvc_status grd_status_material ,
E4.rvc_status grd_status_betriebsmittel ,
E5.rvc_status grd_status_freigabe_muster ,
E6.rvc_status grd_status_fremdbearb ,
E7.rvc_status grd_status_ubg ,
'1' grd_herkunft
FROM v100 A
INNER JOIN g600 A1
ON A.konto = A1.konto
AND A.satzart = A1.satzart
AND A.fi_nr = A1.fi_nr
INNER JOIN g693 A2
ON A1.adressid = A2.adressid
LEFT OUTER JOIN v110 B
ON A.aufnr = B.aufnr
AND A.fi_nr = B.fi_nr
INNER JOIN g0402 B1
ON B.identnr = B1.identnr
AND B.var = B1.var
AND B.fi_nr = B1.fi_nr
LEFT OUTER JOIN v1101 C
ON B.aufnr = C.aufnr
AND B.aufpos = C.aufpos
AND B.fi_nr = C.fi_nr
LEFT OUTER JOIN rc050 D
ON A.aufnr = D.aufnr
AND B.aufpos = D.aufpos
AND C.lfdnr_eintl = D.lfdnr_eintl
AND A.fi_nr = D.fi_nr
LEFT OUTER JOIN r000 D1
ON D.fklz = D1.fklz
AND D.fi_nr = D1.fi_nr
LEFT OUTER JOIN rc053 D2
ON D2.fklz = D.fklz
AND D2.fi_nr = D.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0531 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0531
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E1
ON E1.fklz = D.fklz
AND E1.fi_nr = D.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0532 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0532
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E2
ON E2.fklz = D.fklz
AND E2.fi_nr = D.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0533 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0533
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E3
ON E3.fklz = D.fklz
AND E3.fi_nr = D.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0534 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0534
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E4
ON E4.fklz = D.fklz
AND E4.fi_nr = D.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0535 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0535
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E5
ON E5.fklz = D.fklz
AND E5.fi_nr = D.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0536 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0536
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E6
ON E6.fklz = D.fklz
AND E6.fi_nr = D.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0537 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0537
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E7
ON E7.fklz = D.fklz
AND E7.fi_nr = D.fi_nr
WHERE B.pwert_offen <> 0
AND B.vvorgart = 1
AND (C.menge-C.menge_gel) <> 0
AND B.identnr LIKE '6%'
ORDER BY A.aufnr ,
B.aufpos ,
C.lfdnr_eintl
) VERTRIEBSAUFTRAEGE
WHERE grd_menge_offen <> 0
UNION ALL
SELECT *
FROM
(SELECT A.aufnr grd_aufnr ,
C.konto grd_konto ,
C2.name_1 grd_name_1 ,
C1.kd_grp grd_kd_grp ,
B.stpos grd_aufpos ,
B.datneu grd_datneu ,
B.identnr grd_identnr ,
B1.ben grd_ben ,
0 grd_lfdnr_eintl ,
NULL grd_wunsch_term ,
B.sbterm grd_lief_term ,
NULL grd_lief_termzk ,
NULL grd_menge ,
NULL grd_menge_gel ,
NULL grd_menge_offen ,
A.fklz grd_fklz ,
E.txt8 grd_bemerkung ,
A.fkfs grd_fkfs ,
A.menge grd_menge_fa ,
B.menge_offen grd_menge_offen_fa ,
D.rvc_prio grd_prio ,
D.seterm_i grd_ist_dat ,
D.status grd_status_eilt ,
D.dlzeit grd_dlz_tage ,
D.rvc_clipstatus grd_status_clip ,
E1.rvc_status grd_status_av ,
E2.rvc_status grd_status_kd_beistellung ,
E3.rvc_status grd_status_material ,
E4.rvc_status grd_status_betriebsmittel ,
E5.rvc_status grd_status_freigabe_muster ,
E6.rvc_status grd_status_fremdbearb ,
E7.rvc_status grd_status_ubg ,
'3' grd_herkunft
FROM r000 A
INNER JOIN r100 B
ON A.fklz = B.fklz
AND A.fi_nr = B.fi_nr
INNER JOIN g0402 B1
ON B.identnr = B1.identnr
AND B.var = B1.var
AND B.fi_nr = B1.fi_nr
LEFT OUTER JOIN v300 C
ON A.aufnr = C.raaufnr
AND A.fi_nr = C.fi_nr
INNER JOIN g600 C1
ON C.konto = C1.konto
AND C.satzart = C1.satzart
AND C.fi_nr = C1.fi_nr
INNER JOIN g693 C2
ON C1.adressid = C2.adressid
LEFT OUTER JOIN rc053 D
ON D.fklz = A.fklz
AND D.fi_nr = A.fi_nr
LEFT OUTER JOIN rc050 E
ON A.aufnr = E.aufnr
AND B.stpos = E.aufpos
AND 0 = E.lfdnr_eintl
AND A.fi_nr = E.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0531 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0531
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E1
ON E1.fklz = A.fklz
AND E1.fi_nr = A.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0532 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0532
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E2
ON E2.fklz = A.fklz
AND E2.fi_nr = A.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0533 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0533
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E3
ON E3.fklz = A.fklz
AND E3.fi_nr = A.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0534 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0534
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E4
ON E4.fklz = A.fklz
AND E4.fi_nr = A.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0535 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0535
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E5
ON E5.fklz = A.fklz
AND E5.fi_nr = A.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0536 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0536
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E6
ON E6.fklz = A.fklz
AND E6.fi_nr = A.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0537 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0537
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E7
ON E7.fklz = A.fklz
AND E7.fi_nr = A.fi_nr
WHERE B.identnr <> 'Kundenauftrag'
AND B.identnr <> 'Vorausdispo'
AND B.identnr LIKE '6%'
AND B.menge_offen <> 0
ORDER BY A.aufnr ,
B.stpos
) UBG
) RANKED_UNION
)
WHERE ranking = 1
UNION ALL
SELECT *
FROM
(SELECT A.aufnr grd_aufnr ,
C.konto grd_konto ,
C2.name_1 grd_name_1 ,
C1.kd_grp grd_kd_grp ,
B.stpos grd_aufpos ,
B.datneu grd_datneu ,
B.identnr grd_identnr ,
B1.ben grd_ben ,
0 grd_lfdnr_eintl ,
NULL grd_wunsch_term ,
B.sbterm grd_lief_term ,
NULL grd_lief_termzk ,
NULL grd_menge ,
NULL grd_menge_gel ,
NULL grd_menge_offen ,
A.fklz grd_fklz ,
E.txt8 grd_bemerkung ,
A.fkfs grd_fkfs ,
A.menge grd_menge_fa ,
B.menge_offen grd_menge_offen_fa ,
D.rvc_prio grd_prio ,
D.seterm_i grd_ist_dat ,
D.status grd_status_eilt ,
D.dlzeit grd_dlz_tage ,
D.rvc_clipstatus grd_status_clip ,
E1.rvc_status grd_status_av ,
E2.rvc_status grd_status_kd_beistellung ,
E3.rvc_status grd_status_material ,
E4.rvc_status grd_status_betriebsmittel ,
E5.rvc_status grd_status_freigabe_muster ,
E6.rvc_status grd_status_fremdbearb ,
E7.rvc_status grd_status_ubg ,
'2' grd_herkunft
FROM r000 A
INNER JOIN r100 B
ON A.fklz = B.fklz
AND A.fi_nr = B.fi_nr
INNER JOIN g0402 B1
ON B.identnr = B1.identnr
AND B.var = B1.var
AND B.fi_nr = B1.fi_nr
LEFT OUTER JOIN v300 C
ON A.aufnr = C.raaufnr
AND A.fi_nr = C.fi_nr
INNER JOIN g600 C1
ON C.konto = C1.konto
AND C.satzart = C1.satzart
AND C.fi_nr = C1.fi_nr
INNER JOIN g693 C2
ON C1.adressid = C2.adressid
LEFT OUTER JOIN rc053 D
ON D.fklz = A.fklz
AND D.fi_nr = A.fi_nr
LEFT OUTER JOIN rc050 E
ON A.aufnr = E.aufnr
AND B.stpos = E.aufpos
AND 0 = E.lfdnr_eintl
AND A.fi_nr = E.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0531 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0531
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E1
ON E1.fklz = A.fklz
AND E1.fi_nr = A.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0532 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0532
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E2
ON E2.fklz = A.fklz
AND E2.fi_nr = A.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0533 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0533
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E3
ON E3.fklz = A.fklz
AND E3.fi_nr = A.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0534 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0534
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E4
ON E4.fklz = A.fklz
AND E4.fi_nr = A.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0535 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0535
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E5
ON E5.fklz = A.fklz
AND E5.fi_nr = A.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0536 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0536
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E6
ON E6.fklz = A.fklz
AND E6.fi_nr = A.fi_nr
LEFT OUTER JOIN
(SELECT fi_nr,
fklz,
rvc_status,
lfdnr
FROM rc0537 temp
WHERE lfdnr =
(SELECT MAX(lfdnr)
FROM rc0537
WHERE fi_nr = temp.fi_nr
AND fklz = temp.fklz
)
) E7
ON E7.fklz = A.fklz
AND E7.fi_nr = A.fi_nr
WHERE A.identnr = 'Vorausdispo'
AND B.identnr LIKE '6%'
AND B.menge_offen <> 0
ORDER BY A.aufnr ,
B.stpos
) MAN_VORMERKUNGEN
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment