JTL-WaWi - Eigene Übersicht - Artikel - Bestelldaten

## erstellt 29.02.2020 durch Sven Firnrohr ##

SELECT
    po.cEigeneBestellnummer AS 'Bestellnummer',
    CASE
        WHEN po.nStatus = '5' THEN 'Freigegeben'
        WHEN po.nStatus = '20' THEN 'In Bearbeitung'
        WHEN po.nStatus = '100' THEN 'Preisanfrage'
    END AS 'Status',
    sup.cFirma AS 'Lieferant',
    lart.cSonstiges AS 'Lief.-Kommentar',
    CONVERT(VARCHAR, po.dErstellt, 104) AS 'Bestelldatum',
    ROUND(CONVERT(FLOAT, pop.fMenge), 2) AS 'Bestellmenge',
    ROUND(CONVERT(FLOAT, pop.fMengeGeliefert), 2) AS 'Gelieferte Menge',
    ROUND(CONVERT(FLOAT, pop.fAnzahlOffen), 2) AS 'Offene Menge',
    CONVERT(VARCHAR, pop.dLieferdatum, 104) AS 'Vorauss. Lieferdatum',
    pop.cHinweis AS 'Hinweis',
    po.cInternerKommentar AS 'Interner Kommentar',
    po.cDruckAnmerkung AS 'Druckanmerkung',
    CASE
        WHEN po.kKunde > 0 THEN CONCAT(pol.cVorname, ' ', pol.cNachname, ' ', pol.cFirma)
        ELSE ISNULL(wh.cName, '')
    END AS 'Zielort'
FROM eazybusiness.dbo.tLieferantenBestellung po
JOIN eazybusiness.dbo.tlieferant sup
    ON sup.kLieferant = po.kLieferant
JOIN eazybusiness.dbo.tLieferantenBestellungPos pop
    ON pop.kLieferantenBestellung = po.kLieferantenBestellung
JOIN eazybusiness.dbo.tArtikel art
    ON art.kArtikel = pop.kArtikel
LEFT JOIN eazybusiness.dbo.tWarenLager wh
    ON wh.kWarenLager = po.kLager
JOIN eazybusiness.dbo.tLieferantenBestellungLA pol
    ON pol.kLieferantenBestellungLA = po.kLieferantenBestellungLA
JOIN eazybusiness.dbo.tliefartikel lart
    ON lart.tArtikel_kArtikel = art.kArtikel
WHERE art.kArtikel = '329'
    AND po.nStatus >= 5
    AND po.nStatus < 500
    AND (nDeleted = 0 OR nDeleted IS NULL)
    AND pop.fMengeGeliefert < pop.fMenge
ORDER BY 'Vorauss. Lieferdatum' ASC