Třídová akce

Popis:

Kontrola uzávěrky Porovná množství na řádcích účtenek s řádky skladových dokladů. Když nevrátí nic, je test OK. Protože jeden DL může být na několika účtenkách nebo může být čerpaný jinak (např. FV), může být na účtenkách méně než na DL, ale nesmí na nich být více než na DL - podmínka "sum(A.quantity)>0"

Kód:

4

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(SDQuantity) AS SDQuantity,
 SUM(PDQuantity) AS PDQuantity
FROM(
 /* Napocteni dodaneho mnozstvi z radku skladovych dokladu - DeliveredQuantity na DL*/
 SELECT
  MAX(SD2.StoreCard_ID) AS StoreCard_ID,
  MAX(SD2.Store_ID) AS Store_ID,
  MAX(-SD2.DeliveredQuantity) AS Quantity,
  MAX(SD2.DeliveredQuantity) AS SDQuantity,
  0 AS PDQuantity
 FROM POSDocuments2 PD2
 JOIN POSDocuments PD ON PD.ID = PD2.Parent_ID
 LEFT JOIN StoreDocuments2 SD2 ON SD2.ID = PD2.StoreDocumentRow_ID
 JOIN StoreDocuments SD ON SD.ID = SD2.Parent_ID
 WHERE PD.POSSummaredDocument_ID =:PSD_ID
  AND SD.DocumentType = '21'
 GROUP BY SD2.ID
 UNION ALL
 /* Odecteni dodaneho mnozstvi do jinych dokladu FV z radku skladovych dokladu*/
 SELECT
  MAX(SD2.StoreCard_ID) AS StoreCard_ID,
  MAX(SD2.Store_ID) AS Store_ID,
  MAX(II2.Quantity) AS Quantity,
  MAX(-II2.Quantity) AS SDQuantity,
  0 AS PDQuantity
 FROM POSDocuments2 PD2
 JOIN POSDocuments PD ON PD.ID = PD2.Parent_ID
 LEFT JOIN StoreDocuments2 SD2 ON PD2.StoreDocumentRow_ID = SD2.ID
 JOIN StoreDocuments SD ON SD.ID = SD2.Parent_ID
 LEFT JOIN IssuedInvoices2 II2 ON SD2.ID = II2.ProvideRow_ID AND SD2.Parent_ID = II2.Provide_ID
 WHERE PD.POSSummaredDocument_ID =:PSD_ID
  AND SD.DocumentType = '21'
 GROUP BY SD2.ID
 UNION ALL
 /* Odecteni dodaneho mnozstvi do jinych dokladu PP z radku skladovych dokladu*/
 SELECT
  MAX(SD2.StoreCard_ID) AS StoreCard_ID,
  MAX(SD2.Store_ID) AS Store_ID,
  MAX(CR2.Quantity) AS Quantity,
  MAX(-CR2.Quantity) AS SDQuantity,
  0 AS PDQuantity
 FROM POSDocuments2 PD2
 JOIN POSDocuments PD ON PD.ID = PD2.Parent_ID
 LEFT JOIN StoreDocuments2 SD2 ON PD2.StoreDocumentRow_ID = SD2.ID
 JOIN StoreDocuments SD ON SD.ID = SD2.Parent_ID
 LEFT JOIN CashReceived2 CR2 ON SD2.ID = CR2.ProvideRow_ID AND SD2.Parent_ID = CR2.Provide_ID
 WHERE PD.POSSummaredDocument_ID =:PSD_ID
  AND SD.DocumentType = '21'
 GROUP BY SD2.ID
 UNION ALL
 /* Odecteni vraceneho zbozi PR, VR*/
 SELECT
  MAX(SD2.StoreCard_ID) AS StoreCard_ID,
  MAX(SD2.Store_ID) AS Store_ID,
  MAX(SD2.Quantity) AS Quantity,
  MAX(-SD2.Quantity) AS SDQuantity,
  0 AS PDQuantity
 FROM POSDocuments2 PD2
 JOIN POSDocuments PD ON PD.ID = PD2.Parent_ID
 LEFT JOIN StoreDocuments2 SD2 ON SD2.ID = PD2.StoreDocumentRow_ID
 JOIN StoreDocuments SD ON SD.ID = SD2.Parent_ID
 WHERE PD.POSSummaredDocument_ID =:PSD_ID
  AND SD.DocumentType <> '21'
 GROUP BY SD2.ID
 UNION ALL
 /* Odecteni prodaneho mnozstvi z radku uctenek */
 SELECT
  PD2.StoreCard_ID,
  PD2.Store_ID,
  PD2.Quantity,
  0 AS SDQuantity,
  PD2.Quantity AS PDQuantity
 FROM PosDocuments PD
 LEFT JOIN PosDocuments2 PD2 ON PD2.Parent_ID = PD.ID
 WHERE
  PD.PosSummaredDocument_ID =:PSD_ID AND
  PD.InventoryShortFall_ID IS NULL AND
  PD.InvOverPlus_ID IS NULL
) 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