Thank you for using the English version of the JTL-Guide!
We are currently still building up the English documentation; not all sections have been translated yet. Please note that there may still be German screenshots or links to German pages even on pages that have already been translated. This guide mostly addresses English speaking users in Germany.
We are currently still building up the English documentation; not all sections have been translated yet. Please note that there may still be German screenshots or links to German pages even on pages that have already been translated. This guide mostly addresses English speaking users in Germany.
Custom overviews for Customers
On this page you will find sample SQL commands for custom overviews in the Customers area of JTL-Wawi. Read the Creating custom overviews page to learn how to create custom overviews.
Customer-specific prices
Select a customer in the JTL-Wawi customer management to see all valid customer-specific prices at a glance. Scale prices are also taken into account. The following information is shown:
SQL for customer-specific prices
SELECT
tartikel.cArtNr AS ArtNr,
tArtikelBeschreibung.cName AS Artikelname,
tPreisDetail.nAnzahlAb AS 'Preis ab Menge',
CONVERT(decimal(10,2), tPreisDetail.fNettoPreis) AS 'Nettopreis',
CONVERT(decimal(10,2), tPreisDetail.fNettoPreis * (100 + tSteuersatz.fSteuersatz) / 100) AS 'Bruttopreis'
FROM tKunde
JOIN tPreis ON tPreis.kKunde = tKunde.kKunde
JOIN tPreisDetail ON tPreisDetail.kPreis = tPreis.kPreis
JOIN tartikel ON tartikel.kArtikel = tPreis.kArtikel
JOIN dbo.tSpracheUsed ON nStandard = 1
JOIN dbo.tArtikelBeschreibung ON tArtikelBeschreibung.kArtikel = tartikel.kArtikel
AND tArtikelBeschreibung.kSprache = tSpracheUsed.kSprache
AND tArtikelBeschreibung.kPlattform=1
JOIN (
SELECT TOP 1 kFirma, cLandISO FROM tFirma
ORDER by kFirma) AS jFirma ON 1=1
JOIN tSteuerzoneLand ON tSteuerzoneLand.cISO = jFirma.cLandISO
JOIN tSteuerzone ON tSteuerzone.kSteuerzone = tSteuerzoneLand.kSteuerzone
AND tSteuerzone.kFirma = 0
JOIN tSteuersatz ON tSteuersatz.kSteuerzone = tSteuerzone.kSteuerzone
AND tSteuersatz.kSteuerklasse = tartikel.kSteuerklasse
WHERE tKunde.kKunde = @Key
ORDER BY ArtNr, 'Preis ab Menge'
Read the following documentation on the subject: Setting up customer-specific prices.
Related topics