Třídová akce

Popis:

Kontrola uzávěrky Porovná množství na řádcích účtenek s řádky souhrnné účtenky. Když nevrátí nic, je test OK.

Kód:

5

Tělo:

SELECT
 max(ST.Code || ' ' || ST.Name) as Store,
 max(SC.Code || ' ' || SC.Name) as StoreCard,
 sum(A.Quantity) as Quantity,
 max(SC.MainUnitCode) as QUnit,
 sum(PDQuantity) as PDQuantity,
 sum(PSQuantity) as PSQuantity
FROM(
 SELECT
  PD2.StoreCard_ID,
  PD2.Store_ID,
  -PD2.Quantity as Quantity,
  0 as PSQuantity,
  PD2.Quantity as PDQuantity
 FROM PosDocuments PD
 LEFT JOIN PosDocuments2 PD2 ON PD2.Parent_ID = PD.ID
 WHERE PD.PosSummaredDocument_ID=:OID1
 UNION ALL
 SELECT
  PSD2.StoreCard_ID,
  PSD2.Store_ID,
  (PSD2.Quantity*PSD2.UnitRate) as Quantity,
  (PSD2.Quantity*PSD2.UnitRate) as PSQuantity,
  0 as PDQuantity
 FROM PosSummaredDocuments2 PSD2
 WHERE PSD2.Parent_ID=:OID2
 UNION ALL
 SELECT
  PSD3.StoreCard_ID,
  PSD3.Store_ID,
  (PSD3.Quantity*PSD3.UnitRate) as Quantity,
  (PSD3.Quantity*PSD3.UnitRate) as PSQuantity,
  0 as PDQuantity
 FROM PosSummaredDocuments3 PSD3
 WHERE PSD3.Parent_ID=:OID3
 ) A
JOIN Stores ST ON ST.ID=A.Store_ID
JOIN StoreCards SC ON SC.ID=A.StoreCard_ID
GROUP BY
 A.StoreCard_ID,
 A.Store_ID
HAVING sum(A.Quantity)<>0

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