| Název | Popis | Datový typ |
|---|---|---|
| DocQueue_ID | Char(10) | |
| DocumentType | Char(2) | |
| Period_ID | Char(10) | |
| DateTo$DATE | Float(0, 0) | |
| LocalCurrency_ID | Char(10) |
BEGIN
/* Uzávěrka fakturace - Nastavení ClosingPeriod_ID na všech přesně zaplacených dokladech v lokální i cizí měně pro období úzávěrky a daného typu. */
/* Provádí se ve druhém kroku uzávěrky. */
/* Doklady s nastaveným ClosingPeriod_ID do uzávěrky fakturace nevstupují. */
/* Faktury vydané */
if (DocumentType = '03') then
UPDATE IssuedInvoices A SET A.ClosingPeriod_ID = :Period_ID
WHERE A.Period_ID = :Period_ID and A.DocQueue_ID = :DocQueue_ID AND
A.Currency_ID <> :LocalCurrency_ID and
A.Amount - A.CreditAmount =
(SELECT Sum(P.Amount) FROM PAYMENTSFORDOCUMENT_VIEW P WHERE
P.PDOCUMENTTYPE = :DocumentType AND P.PDOCUMENT_ID = A.ID AND P.DOCDATE$DATE <= :DateTo$DATE) AND
A.LocalAmount - A.LocalCreditAmount =
(SELECT Sum(P.LocalAmount) FROM PAYMENTSFORDOCUMENT_VIEW P WHERE
P.PDOCUMENTTYPE = :DocumentType and P.PDOCUMENT_ID = A.ID AND P.DOCDATE$DATE <= :DateTo$DATE);
/* Faktury přijaté */
if (DocumentType = '04') then
UPDATE ReceivedInvoices A SET A.ClosingPeriod_ID = :Period_ID
WHERE A.Period_ID = :Period_ID and A.DocQueue_ID = :DocQueue_ID AND
A.Currency_ID <> :LocalCurrency_ID and
A.Amount - A.CreditAmount =
(SELECT Sum(P.Amount) FROM PAYMENTSFORDOCUMENT_VIEW P WHERE
P.PDOCUMENTTYPE = :DocumentType AND P.PDOCUMENT_ID = A.ID AND P.DOCDATE$DATE <= :DateTo$DATE) AND
A.LocalAmount - A.LocalCreditAmount =
(SELECT Sum(P.LocalAmount) FROM PAYMENTSFORDOCUMENT_VIEW P WHERE
P.PDOCUMENTTYPE = :DocumentType and P.PDOCUMENT_ID = A.ID AND P.DOCDATE$DATE <= :DateTo$DATE);
/* Dobropisy faktur vydaných */
if (DocumentType = '60') then
UPDATE IssuedCreditNotes A SET A.ClosingPeriod_ID = :Period_ID
WHERE A.Period_ID = :Period_ID and A.DocQueue_ID = :DocQueue_ID AND
A.Currency_ID <> :LocalCurrency_ID and
A.Amount =
(SELECT Sum(P.Amount) FROM PAYMENTSFORDOCUMENT_VIEW P WHERE
P.PDOCUMENTTYPE = :DocumentType AND P.PDOCUMENT_ID = A.ID AND P.DOCDATE$DATE <= :DateTo$DATE) AND
A.LocalAmount =
(SELECT Sum(P.LocalAmount) FROM PAYMENTSFORDOCUMENT_VIEW P WHERE
P.PDOCUMENTTYPE = :DocumentType and P.PDOCUMENT_ID = A.ID AND P.DOCDATE$DATE <= :DateTo$DATE);
/* Dobropisy faktur vydaných - korekce zpětně dobropisů, které plně vrací zdrojovou fakturu */
if (DocumentType = '60') then
UPDATE IssuedCreditNotes SET IssuedCreditNotes.ClosingPeriod_ID = :Period_ID
WHERE
IssuedCreditNotes.ClosingPeriod_ID IS NULL AND
IssuedCreditNotes.DocDate$DATE <= :DateTo$DATE AND
IssuedCreditNotes.DocQueue_ID = :DocQueue_ID AND
IssuedCreditNotes.Currency_ID <> :LocalCurrency_ID and
NOT EXISTS (SELECT P.Amount FROM PAYMENTSFORDOCUMENT_VIEW P WHERE P.PDOCUMENTTYPE = :DocumentType AND P.PDOCUMENT_ID = IssuedCreditNotes.ID AND P.DOCDATE$DATE <= :DateTo$DATE) AND
NOT EXISTS (SELECT P.Amount FROM PAYMENTSFORDOCUMENT_VIEW P WHERE P.PDOCUMENTTYPE = '03' AND P.PDOCUMENT_ID = IssuedCreditNotes.Source_ID AND P.DOCDATE$DATE <= :DateTo$DATE) AND
IssuedCreditNotes.LocalAmount = (SELECT A.LocalAmount FROM IssuedInvoices A WHERE A.ID = IssuedCreditNotes.Source_ID) AND
IssuedCreditNotes.Amount = (SELECT A.Amount FROM IssuedInvoices A WHERE A.ID = IssuedCreditNotes.Source_ID);
/* Dobropisy faktury přijatých */
if (DocumentType = '61') then
UPDATE ReceivedCreditNotes A SET A.ClosingPeriod_ID = :Period_ID
WHERE A.Period_ID = :Period_ID and A.DocQueue_ID = :DocQueue_ID AND
A.Currency_ID <> :LocalCurrency_ID and
A.Amount =
(SELECT Sum(P.Amount) FROM PAYMENTSFORDOCUMENT_VIEW P WHERE
P.PDOCUMENTTYPE = :DocumentType AND P.PDOCUMENT_ID = A.ID AND P.DOCDATE$DATE <= :DateTo$DATE) AND
A.LocalAmount =
(SELECT Sum(P.LocalAmount) FROM PAYMENTSFORDOCUMENT_VIEW P WHERE
P.PDOCUMENTTYPE = :DocumentType and P.PDOCUMENT_ID = A.ID AND P.DOCDATE$DATE <= :DateTo$DATE);
/* Zálohové listy vydané */
/* eliminujeme plně zúčtované ZLV a zároveň plně zaplacené vždy k datu provádění uzávěrky */
/* nebo */
/* nejsou k datu vůbec zúčtovány a zároveň nejsou k datu vůbec zaplaceny */
if (DocumentType = '10') then
UPDATE IssuedDInvoices SET IssuedDInvoices.ClosingPeriod_ID = :Period_ID
WHERE
IssuedDInvoices.ClosingPeriod_ID IS NULL AND
IssuedDInvoices.DocDate$DATE <= :DateTo$DATE AND
IssuedDInvoices.DocQueue_ID = :DocQueue_ID AND
IssuedDInvoices.Currency_ID <> :LocalCurrency_ID and
(
(( /* je plně zaplacen k datu v obou měnách*/
IssuedDInvoices.Amount =
(SELECT Sum(P.Amount) FROM PAYMENTSFORDOCUMENT_VIEW P WHERE
P.PDOCUMENTTYPE = :DocumentType AND P.PDOCUMENT_ID = IssuedDInvoices.ID AND P.DOCDATE$DATE <= :DateTo$DATE)
AND
IssuedDInvoices.LocalAmount =
(SELECT Sum(P.LocalAmount) FROM PAYMENTSFORDOCUMENT_VIEW P WHERE
P.PDOCUMENTTYPE = :DocumentType and P.PDOCUMENT_ID = IssuedDInvoices.ID AND P.DOCDATE$DATE <= :DateTo$DATE)
) AND
( /* a zároveň je plně zúčtován k datu - stačí v cizí měně */
IssuedDInvoices.UsedAmount =
(SELECT Sum(U.Amount) FROM IssuedDepositUsages U WHERE
U.DepositDocument_ID = IssuedDInvoices.ID AND U.PaymentDate$Date <= :DateTo$DATE)
))
OR
(
/* nejsou k datu vůbec zúčtovány a zároveň nejsou k datu vůbec zaplaceny */
NOT EXISTS (SELECT U.Amount FROM IssuedDepositUsages U WHERE U.DepositDocument_ID = IssuedDInvoices.ID AND U.PaymentDate$Date <= :DateTo$DATE)
AND
NOT EXISTS (SELECT P.Amount FROM PAYMENTSFORDOCUMENT_VIEW P WHERE P.PDOCUMENTTYPE = :DocumentType AND P.PDOCUMENT_ID = IssuedDInvoices.ID AND P.DOCDATE$DATE <= :DateTo$DATE)
));
END;Generated by ABRA Software a.s. 27.10.2021 16:34:16