Třídová akce

Popis:

Kontrola uzávěrky Porovná částky na hlavičkách účtenek s hlavičkou souhrnné účtenky. Když nevrátí nic, je test OK.

Kód:

7

Tělo:

SELECT
  SUM(A.PSAmount) AS PSAmount,
  SUM(A.PSAmountWithoutVAT) AS PSAmountWithoutVAT,
  -SUM(A.PDAmount) AS PDAmount,
  -SUM(A.PDAmountWithoutVAT) AS PDAmountWithoutVAT,
  SUM(A.PSAmount + A.PDAmount) AS DiffAmount,
  SUM(A.PSAmountWithoutVAT + A.PDAmountWithoutVAT) AS DiffAmountWithoutVAT,
  MAX(CU.Code) AS Currency,
  COUNT(PDID)-1 AS PDCount
FROM (
  /* Zjisteni castek z hlavicek uctenek */
  /* Pricteni zaokrouhleni z prevodu meny k AmountWithoutVAT z hlavicek uctenek */
  SELECT
    PD.ID AS PDID,
    PD.Currency_ID AS Currency_ID,
    0 AS PSAmount,
    0 AS PSAmountWithoutVAT,
    (-PD.Amount - PD.RoundingCurrency) AS PDAmount,
    (-PD.AmountWithoutVAT -PD.RoundingCurrency) AS PDAmountWithoutVAT
  FROM PosDocuments PD 
  WHERE
    PD.POSSummaredDocument_ID = :OID1
  UNION ALL
  /* Pricteni haleroveho vyrovnani k AmountWithoutVAT z hlavicek uctenek */
  SELECT
    PD.ID AS PDID,
    PD.Currency_ID AS Currency_ID,
    0 AS PSAmount,
    0 AS PSAmountWithoutVAT,
    0 AS PDAmount,
    -PD.RoundingAmount AS PDAmountWithoutVAT
  FROM PosDocuments PD 
  WHERE
    PD.POSSummaredDocument_ID = :OID1 AND
    PD.PricesWithVAT = 'N'
  UNION ALL
  /* Zjisteni castek z hlavicek souhrnnych uctenek */
  SELECT
    '-' AS PDID,
    PS.Currency_ID AS Currency_ID,
    PS.Amount AS PSAmount,
    PS.AmountWithoutVAT,
    0 AS PDAmount,
    0 AS PDAmountWithoutVAT
  FROM PosSummaredDocuments PS
  WHERE
    PS.ID = :OID2
) A
JOIN Currencies CU on CU.ID=A.Currency_ID
GROUP BY A.Currency_ID
HAVING
  SUM(A.PSAmount + A.PDAmount) <> 0 OR
  SUM(A.PSAmountWithoutVAT + A.PDAmountWithoutVAT) <> 0

Generated by ABRA Software a.s. 27.10.2021 16:34:30