BEGIN
IF ((AAccDocQueue_ID = '##########') AND (APeriod_ID = '##########') AND
(AOrdNumber = 0) AND(AAccDate = 0))
THEN BEGIN
/* Dotaz na nezaúčtované doklady */
FOR
SELECT A.id, 7, A.LocalRoundingAmount, A.DocQueue_ID, A.Period_ID, A.OrdNumber,
F.Name, A.DocDate$Date
FROM CashReceived A join Firms F ON F.ID = A.Firm_ID
WHERE
(A.CashDesk_ID = :ACashDesk_ID) AND
( (:ADocDateFrom <= A.DocDate$Date) AND (A.DocDate$Date < :ADocDateTo) ) AND
( (:ADocQueuesSel_ID = '') OR
(A.DocQueue_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ADocQueuesSel_ID)) ) AND
(0 = (SELECT COUNT(*) FROM relations WHERE (leftside_id = A.id) AND (rel_def = 7))) AND
( (:AFirmsSel_ID = '') OR
(A.Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID)) OR
(A.Firm_ID in (SELECT ID FROM Firms WHERE Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID))) ) AND
( (:ACreatedBySel_ID = '') OR
(A.CreatedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACreatedBySel_ID)) ) AND
( (:ACorrectedBySel_ID = '') OR
(A.CorrectedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACorrectedBySel_ID)) )
UNION ALL
SELECT A.id, 8, A.LocalRoundingAmount, A.DocQueue_ID, A.Period_ID, A.OrdNumber,
F.Name, A.DocDate$Date
FROM CashPaid A join Firms F ON F.ID = A.Firm_ID
WHERE
(A.CashDesk_ID = :ACashDesk_ID) AND
( (:ADocDateFrom <= A.DocDate$Date) AND (A.DocDate$Date < :ADocDateTo) ) AND
( (:ADocQueuesSel_ID = '') OR
(A.DocQueue_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ADocQueuesSel_ID)) ) AND
(0 = (SELECT COUNT(*) FROM relations WHERE (leftside_id = A.id) AND (rel_def = 8))) AND
( (:AFirmsSel_ID = '') OR
(A.Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID)) OR
(A.Firm_ID in (SELECT ID FROM Firms WHERE Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID))) ) AND
( (:ACreatedBySel_ID = '') OR
(A.CreatedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACreatedBySel_ID)) ) AND
( (:ACorrectedBySel_ID = '') OR
(A.CorrectedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACorrectedBySel_ID)) )
UNION ALL
SELECT A.id, 9, A.LocalRoundingAmount, A.DocQueue_ID, A.Period_ID, A.OrdNumber,
F.Name, A.DocDate$Date
FROM RefundedCashReceived A join Firms F ON F.ID = A.Firm_ID
WHERE
(A.CashDesk_ID = :ACashDesk_ID) AND
( (:ADocDateFrom <= A.DocDate$Date) AND (A.DocDate$Date < :ADocDateTo) ) AND
( (:ADocQueuesSel_ID = '') OR
(A.DocQueue_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ADocQueuesSel_ID)) ) AND
(0 = (SELECT COUNT(*) FROM relations WHERE (leftside_id = A.id) AND (rel_def = 9))) AND
( (:AFirmsSel_ID = '') OR
(A.Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID)) OR
(A.Firm_ID in (SELECT ID FROM Firms WHERE Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID))) ) AND
( (:ACreatedBySel_ID = '') OR
(A.CreatedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACreatedBySel_ID)) ) AND
( (:ACorrectedBySel_ID = '') OR
(A.CorrectedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACorrectedBySel_ID)) )
UNION ALL
SELECT A.id, 10, A.LocalRoundingAmount, A.DocQueue_ID, A.Period_ID, A.OrdNumber,
F.Name, A.DocDate$Date
FROM RefundedCashPaid A join Firms F ON F.ID = A.Firm_ID
WHERE
(A.CashDesk_ID = :ACashDesk_ID) AND
( (:ADocDateFrom <= A.DocDate$Date) AND (A.DocDate$Date < :ADocDateTo) ) AND
( (:ADocQueuesSel_ID = '') OR
(A.DocQueue_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ADocQueuesSel_ID)) ) AND
(0 = (SELECT COUNT(*) FROM relations WHERE (leftside_id = A.id) AND (rel_def = 10))) AND
( (:AFirmsSel_ID = '') OR
(A.Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID)) OR
(A.Firm_ID in (SELECT ID FROM Firms WHERE Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID))) ) AND
( (:ACreatedBySel_ID = '') OR
(A.CreatedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACreatedBySel_ID)) ) AND
( (:ACorrectedBySel_ID = '') OR
(A.CorrectedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACorrectedBySel_ID)) )
UNION ALL
SELECT A.id, 18, CAST(0 AS NUMERIC(15, 2)), A.DocQueue_ID, A.Period_ID, A.OrdNumber,
CAST('' AS VARCHAR(80)), A.DocDate$Date
FROM CashDeskExchangeDifferences A
WHERE
(A.CashDesk_ID = :ACashDesk_ID) AND
( (:ADocDateFrom <= A.DocDate$Date) AND (A.DocDate$Date < :ADocDateTo) ) AND
( (:ADocQueuesSel_ID = '') OR
(A.DocQueue_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ADocQueuesSel_ID)) ) AND
(0 = (SELECT COUNT(*) FROM relations WHERE (leftside_id = A.id) AND (rel_def = 18))) AND
( (:ACreatedBySel_ID = '') OR
(A.CreatedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACreatedBySel_ID)) ) AND
( (:ACorrectedBySel_ID = '') OR
(A.CorrectedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACorrectedBySel_ID)) )
INTO
:CashDoc_ID, :CashDocRelDef, :LocalRoundingAmount, :mDocQueue_ID, :mPeriod_ID,
:mOrdNumber, :FirmName, :DocDate$Date
DO BEGIN
IF (CashDocRelDef IN (8, 9)) THEN
LocalRoundingAmount = -1 * LocalRoundingAmount;
SELECT out_displayname
FROM FmtDisplayName(:mDocQueue_ID, :mOrdNumber, :mPeriod_ID)
INTO :DisplayName;
SUSPEND;
END
END
ELSE BEGIN
/* Dotaz na zaúčtované doklady */
FOR
SELECT
R.leftside_id, R.rel_def
FROM
Relations R
join GeneralLedger GL ON GL.ID = R.RightSide_ID
WHERE
(R.rel_def IN (7, 8, 9, 10, 18)) AND
(GL.AccDocQueue_ID = :AAccDocQueue_ID) AND
(GL.Period_ID = :APeriod_ID) AND
(GL.OrdNumber = :AOrdNumber) AND
(ib_encodedate(ib_decodedate_year(GL.AccDate$Date), ib_decodedate_month(GL.AccDate$Date), ib_decodedate_day(GL.AccDate$Date)) = :AAccDate)
GROUP BY
R.leftside_id, R.rel_def
INTO
:CashDoc_ID, :CashDocRelDef
DO BEGIN
IF (CashDocRelDef = 7) THEN BEGIN
FOR
SELECT A.LocalRoundingAmount, A.DocQueue_ID, A.Period_ID, A.OrdNumber, F.Name, A.DocDate$Date
FROM CashReceived A join Firms F ON F.ID = A.Firm_ID
WHERE
(A.ID = :CashDoc_ID) AND
(A.CashDesk_ID = :ACashDesk_ID) AND
( (:ADocDateFrom <= A.DocDate$Date) AND (A.DocDate$Date < :ADocDateTo) ) AND
( (:ADocQueuesSel_ID = '') OR
(A.DocQueue_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ADocQueuesSel_ID)) ) AND
( (:AFirmsSel_ID = '') OR
(A.Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID)) OR
(A.Firm_ID in (SELECT ID FROM Firms WHERE Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID))) ) AND
( (:ACreatedBySel_ID = '') OR
(A.CreatedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACreatedBySel_ID)) ) AND
( (:ACorrectedBySel_ID = '') OR
(A.CorrectedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACorrectedBySel_ID)) )
INTO
:LocalRoundingAmount, :mDocQueue_ID, :mPeriod_ID, :mOrdNumber, :FirmName, :DocDate$Date
DO BEGIN
SELECT out_displayname
FROM FmtDisplayName(:mDocQueue_ID, :mOrdNumber, :mPeriod_ID)
INTO :DisplayName;
SUSPEND;
END
END
IF (CashDocRelDef = 8) THEN BEGIN
FOR
SELECT A.LocalRoundingAmount, A.DocQueue_ID, A.Period_ID, A.OrdNumber, F.Name, A.DocDate$Date
FROM CashPaid A join Firms F ON F.ID = A.Firm_ID
WHERE
(A.ID = :CashDoc_ID) AND
(A.CashDesk_ID = :ACashDesk_ID) AND
( (:ADocDateFrom <= A.DocDate$Date) AND (A.DocDate$Date < :ADocDateTo) ) AND
( (:ADocQueuesSel_ID = '') OR
(A.DocQueue_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ADocQueuesSel_ID)) ) AND
( (:AFirmsSel_ID = '') OR
(A.Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID)) OR
(A.Firm_ID in (SELECT ID FROM Firms WHERE Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID))) ) AND
( (:ACreatedBySel_ID = '') OR
(A.CreatedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACreatedBySel_ID)) ) AND
( (:ACorrectedBySel_ID = '') OR
(A.CorrectedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACorrectedBySel_ID)) )
INTO
:LocalRoundingAmount, :mDocQueue_ID, :mPeriod_ID, :mOrdNumber, :FirmName, :DocDate$Date
DO BEGIN
LocalRoundingAmount = -1 * LocalRoundingAmount;
SELECT out_displayname
FROM FmtDisplayName(:mDocQueue_ID, :mOrdNumber, :mPeriod_ID)
INTO :DisplayName;
SUSPEND;
END
END
IF (CashDocRelDef = 9) THEN BEGIN
FOR
SELECT A.LocalRoundingAmount, A.DocQueue_ID, A.Period_ID, A.OrdNumber, F.Name, A.DocDate$Date
FROM RefundedCashReceived A join Firms F ON F.ID = A.Firm_ID
WHERE
(A.ID = :CashDoc_ID) AND
(A.CashDesk_ID = :ACashDesk_ID) AND
( (:ADocDateFrom <= A.DocDate$Date) AND (A.DocDate$Date < :ADocDateTo) ) AND
( (:ADocQueuesSel_ID = '') OR
(A.DocQueue_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ADocQueuesSel_ID)) ) AND
( (:AFirmsSel_ID = '') OR
(A.Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID)) OR
(A.Firm_ID in (SELECT ID FROM Firms WHERE Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID))) ) AND
( (:ACreatedBySel_ID = '') OR
(A.CreatedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACreatedBySel_ID)) ) AND
( (:ACorrectedBySel_ID = '') OR
(A.CorrectedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACorrectedBySel_ID)) )
INTO
:LocalRoundingAmount, :mDocQueue_ID, :mPeriod_ID, :mOrdNumber, :FirmName, :DocDate$Date
DO BEGIN
LocalRoundingAmount = -1 * LocalRoundingAmount;
SELECT out_displayname
FROM FmtDisplayName(:mDocQueue_ID, :mOrdNumber, :mPeriod_ID)
INTO :DisplayName;
SUSPEND;
END
END
IF (CashDocRelDef = 10) THEN BEGIN
FOR
SELECT A.LocalRoundingAmount, A.DocQueue_ID, A.Period_ID, A.OrdNumber, F.Name, A.DocDate$Date
FROM RefundedCashPaid A join Firms F ON F.ID = A.Firm_ID
WHERE
(A.ID = :CashDoc_ID) AND
(A.CashDesk_ID = :ACashDesk_ID) AND
( (:ADocDateFrom <= A.DocDate$Date) AND (A.DocDate$Date < :ADocDateTo) ) AND
( (:ADocQueuesSel_ID = '') OR
(A.DocQueue_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ADocQueuesSel_ID)) ) AND
( (:AFirmsSel_ID = '') OR
(A.Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID)) OR
(A.Firm_ID in (SELECT ID FROM Firms WHERE Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :AFirmsSel_ID))) ) AND
( (:ACreatedBySel_ID = '') OR
(A.CreatedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACreatedBySel_ID)) ) AND
( (:ACorrectedBySel_ID = '') OR
(A.CorrectedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACorrectedBySel_ID)) )
INTO
:LocalRoundingAmount, :mDocQueue_ID, :mPeriod_ID, :mOrdNumber, :FirmName, :DocDate$Date
DO BEGIN
SELECT out_displayname
FROM FmtDisplayName(:mDocQueue_ID, :mOrdNumber, :mPeriod_ID)
INTO :DisplayName;
SUSPEND;
END
END
IF (CashDocRelDef = 18) THEN BEGIN
FOR
SELECT CAST(0 AS NUMERIC(15, 2)), A.DocQueue_ID, A.Period_ID, A.OrdNumber, CAST('' AS VARCHAR(80)), A.DocDate$Date
FROM CashDeskExchangeDifferences A
WHERE
(A.ID = :CashDoc_ID) AND
(A.CashDesk_ID = :ACashDesk_ID) AND
( (:ADocDateFrom <= A.DocDate$Date) AND (A.DocDate$Date < :ADocDateTo) ) AND
( (:ADocQueuesSel_ID = '') OR
(A.DocQueue_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ADocQueuesSel_ID)) ) AND
( (:ACreatedBySel_ID = '') OR
(A.CreatedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACreatedBySel_ID)) ) AND
( (:ACorrectedBySel_ID = '') OR
(A.CorrectedBy_ID IN (SELECT obj_id FROM SELDAT WHERE sel_id = :ACorrectedBySel_ID)) )
INTO
:LocalRoundingAmount, :mDocQueue_ID, :mPeriod_ID, :mOrdNumber, :FirmName, :DocDate$Date
DO BEGIN
SELECT out_displayname
FROM FmtDisplayName(:mDocQueue_ID, :mOrdNumber, :mPeriod_ID)
INTO :DisplayName;
SUSPEND;
END
END
END
END
END;