BEGIN
MROWINDEX = 0;
FOR
SELECT
P.DocumentType, P.Document_ID, P.PDocumentType, P.PDocument_ID FROM Payments P
WHERE
(P.PDocumentType = :APDocumentType) AND (P.PDocument_ID = :APDocument_ID)
GROUP BY 1,2,3,4
/* Ostatní doklady s příznakem elektronická platba se nezapisují do penežního deníku.
Platby ostatních dokladů s tímto příznakem se chovají, jako by platily doklad, který platí ostatní doklad,
proto jsou platby ostatních dokladů zahrnovány do plateb zdrojového dokladu.*/
/* Přidání plateb ostatních příjmů. */
UNION
SELECT
PP.DocumentType, PP.Document_ID, PP.PDocumentType, PP.PDocument_ID
FROM Payments P
JOIN OtherIncomes OI ON P.Document_ID = OI.ID
JOIN Payments PP ON (PP.PDocumentType = P.DocumentType) AND (PP.PDocument_ID = P.Document_ID)
WHERE
(P.PDocumentType = :APDocumentType) AND (P.PDocument_ID = :APDocument_ID) AND
(P.DocumentType = '01') AND (OI.ElectronicPayment = 'A')
GROUP BY 1,2,3,4
/* Přidání plateb ostatních výdajů. */
UNION
SELECT
PP.DocumentType, PP.Document_ID, PP.PDocumentType, PP.PDocument_ID
FROM Payments P
JOIN OtherExpenses OE ON P.Document_ID = OE.ID
JOIN Payments PP ON (PP.PDocumentType = P.DocumentType) AND (PP.PDocument_ID = P.Document_ID)
WHERE
(P.PDocumentType = :APDocumentType) AND (P.PDocument_ID = :APDocument_ID) AND
(P.DocumentType = '02') AND (OE.ElectronicPayment = 'A')
GROUP BY 1,2,3,4
INTO
:DocumentType, :Document_ID, :PDocumentType, :PDocument_ID
DO BEGIN
IF (DocumentType = '01') THEN BEGIN
Credit = 'A';
FOR
SELECT A.DocDate$DATE, A.Description, D.Code || '-' || A.OrdNumber || '/' || P.Code, D.Name,
D.Code || '-' || IB_LPad(A.OrdNumber, 8, '0') || '/' || P.Code,
SUM(PM.LOCALAMOUNT)
FROM PAYMENTS PM
JOIN OtherIncomes A ON A.ID = PM.DOCUMENT_ID
JOIN DocQueues D ON D.ID = A.DocQueue_ID
JOIN Periods P ON P.ID = A.Period_ID
WHERE
(PM.DOCUMENT_ID=:Document_ID) AND (PM.DOCUMENTTYPE = :DOCUMENTTYPE) AND
(A.ElectronicPayment = 'N') -- Pokud se jedná o elektronickou platbu nezapisuje do deníku
GROUP BY 1,2,3,4,5
INTO :DocDate$DATE, :Text, :DocumentDisplayName, :mText, :Sort_DocumentDisplayName, :LOCALAMOUNT
DO BEGIN
IF (:DocDate$DATE < :AEndDate) THEN BEGIN
IF (Text = '') THEN
Text = mText;
MROWINDEX = MROWINDEX + 1;
ROWINDEX = MROWINDEX;
SUSPEND;
END
END
END
IF (DocumentType = '02') THEN BEGIN
Credit = 'N';
FOR
SELECT A.DocDate$DATE, A.Description, D.Code || '-' || A.OrdNumber || '/' || P.Code, D.Name,
D.Code || '-' || IB_LPad(A.OrdNumber, 8, '0') || '/' || P.Code,
SUM(PM.LOCALAMOUNT)
FROM PAYMENTS PM
JOIN OtherExpenses A ON A.ID = PM.DOCUMENT_ID
JOIN DocQueues D ON D.ID = A.DocQueue_ID
JOIN Periods P ON P.ID = A.Period_ID
WHERE
(PM.DOCUMENT_ID=:Document_ID) AND (PM.DOCUMENTTYPE = :DOCUMENTTYPE) AND
(A.ElectronicPayment = 'N') -- Pokud se jedná o elektronickou platbu nezapisuje do deníku
GROUP BY 1,2,3,4,5
INTO :DocDate$DATE, :Text, :DocumentDisplayName, :mText, :Sort_DocumentDisplayName, :LOCALAMOUNT
DO BEGIN
IF (:DocDate$DATE < :AEndDate) THEN BEGIN
IF (Text = '') THEN
Text = mText;
MROWINDEX = MROWINDEX + 1;
ROWINDEX = MROWINDEX;
SUSPEND;
END
END
END
IF (DocumentType = '05') THEN BEGIN
Credit = 'A';
SELECT A.DocDate$DATE, A.Description, D.Code || '-' || A.OrdNumber || '/' || P.Code, D.Name,
D.Code || '-' || IB_LPad(A.OrdNumber, 8, '0') || '/' || P.Code,
SUM(PM.LOCALAMOUNT)
FROM PAYMENTS PM
JOIN CashReceived A ON A.ID = PM.DOCUMENT_ID
JOIN DocQueues D ON D.ID = A.DocQueue_ID
JOIN Periods P ON P.ID = A.Period_ID
WHERE (PM.DOCUMENT_ID=:Document_ID) AND (PM.DOCUMENTTYPE = :DOCUMENTTYPE)
GROUP BY 1,2,3,4,5
INTO :DocDate$DATE, :Text, :DocumentDisplayName, :mText, :Sort_DocumentDisplayName, :LOCALAMOUNT;
IF (:DocDate$DATE < :AEndDate) THEN BEGIN
IF (Text = '') THEN
Text = mText;
MROWINDEX = MROWINDEX + 1;
ROWINDEX = MROWINDEX;
SUSPEND;
END
END
IF (DocumentType = '06') THEN BEGIN
Credit = 'N';
SELECT A.DocDate$DATE, A.Description, D.Code || '-' || A.OrdNumber || '/' || P.Code, D.Name,
D.Code || '-' || IB_LPad(A.OrdNumber, 8, '0') || '/' || P.Code,
SUM(PM.LOCALAMOUNT)
FROM PAYMENTS PM
JOIN CashPaid A ON A.ID = PM.DOCUMENT_ID
JOIN DocQueues D ON D.ID = A.DocQueue_ID
JOIN Periods P ON P.ID = A.Period_ID
WHERE (PM.DOCUMENT_ID=:Document_ID) AND (PM.DOCUMENTTYPE = :DOCUMENTTYPE)
GROUP BY 1,2,3,4,5
INTO :DocDate$DATE, :Text, :DocumentDisplayName, :mText, :Sort_DocumentDisplayName, :LOCALAMOUNT;
IF (:DocDate$DATE < :AEndDate) THEN BEGIN
IF (Text = '') THEN
Text = mText;
MROWINDEX = MROWINDEX + 1;
ROWINDEX = MROWINDEX;
SUSPEND;
END
END
IF (DocumentType = '09') THEN BEGIN
SELECT A.Credit, A.DocDate$DATE, B.ID, A.Text,
D.Code || '-' || B.OrdNumber || '/' || P.Code || '/' || A.PosIndex, D.Name,
D.Code || '-' || IB_LPad(B.OrdNumber, 8, '0') || '/' || P.Code || '/' || IB_LPad(A.PosIndex, 3, '0'),
SUM(PM.LOCALAMOUNT)
FROM PAYMENTS PM
JOIN BankStatements2 A ON A.ID = PM.DOCUMENT_ID
JOIN BankStatements B ON B.ID = A.Parent_ID
JOIN DocQueues D ON D.ID = B.DocQueue_ID
JOIN Periods P ON P.ID = B.Period_ID
WHERE (PM.DOCUMENT_ID=:Document_ID) AND (PM.DOCUMENTTYPE = :DOCUMENTTYPE) AND
(A.IsMultiPaymentRow = 'N')
GROUP BY 1,2,3,4,5,6,7
INTO :Credit, :DocDate$DATE, :mDocument_ID, :Text, :DocumentDisplayName, :mText, :Sort_DocumentDisplayName, :LOCALAMOUNT;
IF (:DocDate$DATE < :AEndDate) THEN BEGIN
Document_ID = mDocument_ID;
IF (Text = '') THEN
Text = mText;
MROWINDEX = MROWINDEX + 1;
ROWINDEX = MROWINDEX;
SUSPEND;
END
END
IF (DocumentType = 'CM') THEN BEGIN
SELECT A.Credit, A.DocDate$DATE, B.ID, A.Text,
D.Code || '-' || B.OrdNumber || '/' || P.Code || '/' || A.PosIndex, D.Name,
D.Code || '-' || IB_LPad(B.OrdNumber, 8, '0') || '/' || P.Code || '/' || IB_LPad(A.PosIndex, 3, '0'),
SUM(PM.LOCALAMOUNT)
FROM PAYMENTS PM
JOIN Compensations2 A ON A.ID = PM.DOCUMENT_ID
JOIN Compensations B ON B.ID = A.Parent_ID
JOIN DocQueues D ON D.ID = B.DocQueue_ID
JOIN Periods P ON P.ID = B.Period_ID
WHERE (PM.DOCUMENT_ID=:Document_ID) AND (PM.DOCUMENTTYPE = :DOCUMENTTYPE)
GROUP BY 1,2,3,4,5,6,7
INTO :Credit, :DocDate$DATE, :mDocument_ID, :Text, DocumentDisplayName, :MTEXT, :Sort_DocumentDisplayName, :LOCALAMOUNT;
IF (:DocDate$DATE < :AEndDate) THEN BEGIN
Document_ID = mDocument_ID;
IF (Text = '') THEN
Text = mText;
MROWINDEX = MROWINDEX + 1;
ROWINDEX = MROWINDEX;
SUSPEND;
END
END
IF (DocumentType = 'U0') THEN BEGIN
Credit = 'A';
SELECT A.PaymentDate$DATE, B.ID, B.Description, D.Code || '-' || B.OrdNumber || '/' || P.Code, D.Name,
D.Code || '-' || IB_LPad(B.OrdNumber, 8, '0') || '/' || P.Code,
SUM(PM.LOCALAMOUNT)
FROM PAYMENTS PM
JOIN IssuedDepositUsages A ON A.ID = PM.DOCUMENT_ID
JOIN IssuedDInvoices B ON B.ID = A.DepositDocument_ID
JOIN DocQueues D ON D.ID = B.DocQueue_ID
JOIN Periods P ON P.ID = B.Period_ID
WHERE (PM.DOCUMENT_ID=:Document_ID) AND (PM.DOCUMENTTYPE = :DOCUMENTTYPE)
GROUP BY 1,2,3,4,5,6
INTO :DocDate$DATE, :mDocument_ID, :Text, :DocumentDisplayName, :mText, :Sort_DocumentDisplayName, :LOCALAMOUNT;
IF (:DocDate$DATE < :AEndDate) THEN BEGIN
Document_ID = mDocument_ID;
IF (Text = '') THEN
Text = mText;
MROWINDEX = MROWINDEX + 1;
ROWINDEX = MROWINDEX;
SUSPEND;
END
END
IF (DocumentType = 'U1') THEN BEGIN
Credit = 'N';
SELECT A.PaymentDate$DATE, B.ID, B.Description, D.Code || '-' || B.OrdNumber || '/' || P.Code, D.Name,
D.Code || '-' || IB_LPad(B.OrdNumber, 8, '0') || '/' || P.Code,
SUM(PM.LOCALAMOUNT)
FROM PAYMENTS PM
JOIN ReceivedDepositUsages A ON A.ID = PM.DOCUMENT_ID
JOIN ReceivedDInvoices B ON B.ID = A.DepositDocument_ID
JOIN DocQueues D ON D.ID = B.DocQueue_ID
JOIN Periods P ON P.ID = B.Period_ID
WHERE (PM.DOCUMENT_ID=:Document_ID) AND (PM.DOCUMENTTYPE = :DOCUMENTTYPE)
GROUP BY 1,2,3,4,5,6
INTO :DocDate$DATE, :mDocument_ID, :Text, :DocumentDisplayName, :mText, :Sort_DocumentDisplayName, :LOCALAMOUNT;
IF (:DocDate$DATE < :AEndDate) THEN BEGIN
Document_ID = mDocument_ID;
IF (Text = '') THEN
Text = mText;
MROWINDEX = MROWINDEX + 1;
ROWINDEX = MROWINDEX;
SUSPEND;
END
END
-- POSCashReceived
IF (DocumentType = 'CR') THEN BEGIN
Credit = 'A';
DocDate$DATE = -1;
SELECT A.DocDate$DATE, A.Text, A.DocumentDisplayName, A.Sort_DocumentDisplayName, A.LOCALAMOUNT
FROM GetPaymentsForDocumentPOS(:DocumentType, :Document_ID, :AEndDate) A
INTO :DocDate$DATE, :Text, :DocumentDisplayName, :Sort_DocumentDisplayName, :LOCALAMOUNT;
IF ((:DocDate$DATE >= 0) AND (:DocDate$DATE < :AEndDate)) THEN BEGIN
MROWINDEX = MROWINDEX + 1;
ROWINDEX = MROWINDEX;
SUSPEND;
END
END
-- POSCashPaid
IF (DocumentType = 'CP') THEN BEGIN
Credit = 'N';
DocDate$DATE = -1;
SELECT A.DocDate$DATE, A.Text, A.DocumentDisplayName, A.Sort_DocumentDisplayName, A.LOCALAMOUNT
FROM GetPaymentsForDocumentPOS(:DocumentType, :Document_ID, :AEndDate) A
INTO :DocDate$DATE, :Text, :DocumentDisplayName, :Sort_DocumentDisplayName, :LOCALAMOUNT;
IF ((:DocDate$DATE >= 0) AND (:DocDate$DATE < :AEndDate)) THEN BEGIN
MROWINDEX = MROWINDEX + 1;
ROWINDEX = MROWINDEX;
SUSPEND;
END
END
END
IF (APDocumentType = '03') THEN BEGIN
DocumentType = '60';
Credit = 'A';
FOR
SELECT A.DocDate$DATE, A.ID, (A.LocalAmount - A.LocalAmountWithoutVAT),
A.Description, D.Code || '-' || A.OrdNumber || '/' || P.Code, D.Name, A.LocalAmountWithoutVAT,
D.Code || '-' || IB_LPad(A.OrdNumber, 8, '0') || '/' || P.Code
FROM IssuedCreditNotes A
JOIN DocQueues D ON D.ID = A.DocQueue_ID
JOIN Periods P ON P.ID = A.Period_ID
WHERE (A.SOURCE_ID = :APDOCUMENT_ID) AND (A.DocDate$DATE < :AEndDate)
INTO
:DocDate$DATE, :Document_ID, :LOCALVAT, :Text, :DocumentDisplayName, :mText, :LOCALAMOUNT, :Sort_DocumentDisplayName
DO BEGIN
IF (Text = '') THEN
Text = mText;
MROWINDEX = MROWINDEX + 1;
ROWINDEX = MROWINDEX;
SUSPEND;
END
END ELSE IF (APDocumentType = '04') THEN BEGIN
DocumentType = '61';
Credit = 'N';
FOR
SELECT A.DocDate$DATE, A.ID, (A.LocalAmount - A.LocalAmountWithoutVAT),
A.Description, D.Code || '-' || A.OrdNumber || '/' || P.Code, D.Name, A.LocalAmountWithoutVAT,
D.Code || '-' || A.OrdNumber || '/' || P.Code
FROM ReceivedCreditNotes A
JOIN DocQueues D ON D.ID = A.DocQueue_ID
JOIN Periods P ON P.ID = A.Period_ID
WHERE (A.SOURCE_ID = :APDOCUMENT_ID) AND (A.DocDate$DATE < :AEndDate)
INTO
:DocDate$DATE, :Document_ID, :LOCALVAT, :Text, :DocumentDisplayName, :mText, :LOCALAMOUNT, :Sort_DocumentDisplayName
DO BEGIN
IF (Text = '') THEN
Text = mText;
MROWINDEX = MROWINDEX + 1;
ROWINDEX = MROWINDEX;
SUSPEND;
END
END
IF (APDocumentType = '62') THEN BEGIN
DocumentType = '63';
-- z dobropisu se vyrobi platby pro korekci DPH
Credit = 'A';
FOR
SELECT A.DocDate$DATE, A.ID, (A.LocalAmount - A.LocalAmountWithoutVAT),
A.Description, D.Code || '-' || A.OrdNumber || '/' || P.Code, D.Name, A.LocalAmountWithoutVAT,
D.Code || '-' || IB_LPad(A.OrdNumber, 8, '0') || '/' || P.Code
FROM VATIssuedDCreditNotes A
JOIN DocQueues D ON D.ID = A.DocQueue_ID
JOIN Periods P ON P.ID = A.Period_ID
WHERE (A.SOURCE_ID = :APDOCUMENT_ID) AND (A.DocDate$DATE < :AEndDate)
INTO
:DocDate$DATE, :Document_ID, :LOCALVAT, :Text, :DocumentDisplayName, :mText, :LOCALAMOUNT, :Sort_DocumentDisplayName
DO BEGIN
IF (Text = '') THEN
Text = mText;
MROWINDEX = MROWINDEX + 1;
ROWINDEX = MROWINDEX;
SUSPEND;
END
END ELSE IF (APDocumentType = '64') THEN BEGIN
DocumentType = '65';
Credit = 'N';
FOR
SELECT A.DocDate$DATE, A.ID, (A.LocalAmount - A.LocalAmountWithoutVAT),
A.Description, D.Code || '-' || A.OrdNumber || '/' || P.Code, D.Name, A.LocalAmountWithoutVAT,
D.Code || '-' || A.OrdNumber || '/' || P.Code
FROM VATReceivedDCreditNotes A
JOIN DocQueues D ON D.ID = A.DocQueue_ID
JOIN Periods P ON P.ID = A.Period_ID
WHERE (A.SOURCE_ID = :APDOCUMENT_ID) AND (A.DocDate$DATE < :AEndDate)
INTO
:DocDate$DATE, :Document_ID, :LOCALVAT, :Text, :DocumentDisplayName, :mText, :LOCALAMOUNT, :Sort_DocumentDisplayName
DO BEGIN
IF (Text = '') THEN
Text = mText;
MROWINDEX = MROWINDEX + 1;
ROWINDEX = MROWINDEX;
SUSPEND;
END
END
END