nikolasd
10/26/2017 - 8:03 PM

Εύρεση ανισοσκέλιστων άρθρων σε συγκεκριμένο διάστημα

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