Pylon SQL - Εύρεση ανισοσκέλιστων άρθρων σε συγκεκριμένο διάστημα
SELECT
V1.COMPANY,
V1.ACCID,
V1.DATE,
V1.JUSTIFICATION,
V1.DOC,
V1.SCAFFOLD,
V1.PISTOSI,
V1.XREOSI,
V1.DIAFORA,
SFALMA =
CASE
WHEN V1.ANISOSKELISTO > 0 THEN 'Aνισοσκέλιστο στη Πίστωση.'
WHEN V1.ANISOSKELISTO < 0 THEN 'Aνισοσκέλιστο στη Χρέωση.'
WHEN V1.ANISOSKELISTO = 0 THEN NULL
WHEN V1.LD_FAULT = 1 THEN 'Λάθος ή άγνωστος λογαριασμός Λογιστικής.'
WHEN V1.LD_FAULT = 0 THEN NULL
WHEN V1.ACCOUNT99 = 1 THEN 'Έγινε αντικατάσταση με λογαριασμό διόρθωσης'
WHEN V1.ACCOUNT99 = 0 THEN NULL
WHEN V1.ERROR = 1 THEN 'Σφάλμα στο παραγώμενο άρθρο'
ELSE NULL
END,
V1.ERROR,
V1.LD_FAULT,
V1.ACCOUNT99,
V1.ANISOSKELISTO,
V1.GENACC,
V1.COSTACC,
V1.ACC01,
V1.ACC02,
V1.ACC03,
V1.ESEXACC
FROM (SELECT
heCompanies.HEGENACCMASK AS GENACC,
heCompanies.HECOSTACCMASK AS COSTACC,
heCompanies.HEACCMASK01 AS ACC01,
heCompanies.HEACCMASK02 AS ACC02,
heCompanies.HEACCMASK03 AS ACC03,
heCompanies.HEREVEXPMASK AS ESEXACC,
heCompanies.HENAME AS COMPANY,
heAccArticleLines.HEACATID AS ACCID,
heAccArticles.HEDATE AS DATE,
heAccArticles.HEJUSTIFICATION AS JUSTIFICATION,
heAccArticles.HEDOCCODE AS DOC,
heAccArticles.HEACCARTICLESERIES AS SCAFFOLD,
SUM(heAccArticleLines.HEDEBITVAL) AS PISTOSI,
SUM(heAccArticleLines.HECREDITVAL) AS XREOSI,
(SUM(heAccArticleLines.HEDEBITVAL) - SUM(heAccArticleLines.HECREDITVAL)) AS DIAFORA,
ANISOSKELISTO =
CASE
WHEN (SUM(heAccArticleLines.HEDEBITVAL) - SUM(heAccArticleLines.HECREDITVAL)) > 0 THEN 1
WHEN (SUM(heAccArticleLines.HEDEBITVAL) - SUM(heAccArticleLines.HECREDITVAL)) < 0 THEN -1
WHEN (SUM(heAccArticleLines.HEDEBITVAL) - SUM(heAccArticleLines.HECREDITVAL)) = 0 THEN 0
END,
LD_FAULT = MAX(CASE
WHEN heAccArticleLines.HEACCOUNT LIKE '[0-9][0-9][.-][0-9][0-9][.-][0-9][0-9][.-][0-9][0-9][0-9][0-9]' OR
heAccArticleLines.HEACCOUNT LIKE '[0-9][0-9][.-][0-9][0-9][0-9][0-9]' THEN 0
ELSE 1
END),
ACCOUNT99 = MAX(CASE
WHEN heAccArticleLines.HEACCOUNT LIKE '99[.-]99[.-]99[.-]99[0-9][0-9]' THEN 1
ELSE 0
END),
ERROR =
CASE
WHEN (SUM(heAccArticleLines.HEDEBITVAL) - SUM(heAccArticleLines.HECREDITVAL)) <> 0 OR
MAX(CASE
WHEN heAccArticleLines.HEACCOUNT LIKE '99[.-]99[.-]99[.-]99[0-9][0-9]' THEN 1
ELSE 0
END) = 1 OR
MAX(CASE
WHEN heAccArticleLines.HEACCOUNT LIKE '[0-9][0-9][.-][0-9][0-9][.-][0-9][0-9][.-][0-9][0-9][0-9][0-9]' OR
heAccArticleLines.HEACCOUNT LIKE '[0-9][0-9][.-][0-9][0-9][0-9][0-9]' THEN 0
ELSE 1
END) = 1 THEN 1
ELSE 0
END
FROM heAccArticleLines
INNER JOIN heAccArticles
ON heAccArticleLines.HEACATID = heAccArticles.HEID
INNER JOIN heCompanyBranches
ON heAccArticles.HECOMPID = heCompanyBranches.HECOMPID
AND heAccArticles.HECMBRID = heCompanyBranches.HEID
INNER JOIN heCompanies
ON heCompanyBranches.HECOMPID = heCompanies.HEID
GROUP BY heCompanies.HENAME,
heAccArticleLines.HEACATID,
heAccArticles.HEDATE,
heAccArticles.HEJUSTIFICATION,
heAccArticles.HEDOCCODE,
heAccArticles.HEACCARTICLESERIES,
heCompanies.HEGENACCMASK,
heCompanies.HECOSTACCMASK,
heCompanies.HEACCMASK01,
heCompanies.HEACCMASK02,
heCompanies.HEACCMASK03,
heCompanies.HEREVEXPMASK) AS V1
WHERE V1.ACCID = '9d12bcb7-9413-e611-b84e-e4115b13cc99'
ORDER BY DIAFORA DESC