BEGIN
AccGroup_ID = '__________';
CreditAmount = 0;
DebetAmount = 0;
for
select Coalesce(D.DebitBusOrder_ID, '0000000000'), D.AccDate$DATE,
D.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or D.DebitAccount_ID in
(select AC.ID from Accounts AC where D.DebitAccount_ID = AC.ID
and AC.Code like :AccountCode ESCAPE '~'
and (:AccountSelID = '' or AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID)))),
D.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or D.CreditAccount_ID in
(select AC.ID from Accounts AC where D.CreditAccount_ID = AC.ID
and AC.Code like :AccountCode ESCAPE '~'
and (:AccountSelID = '' or AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID))))
from GeneralLedger D
where
IsRequest='N' and
(AccDate$DATE >= :AccDateFrom AND AccDate$DATE < :AccDateTo) and
(:TextUsed <> 'A' or Text like :Text ESCAPE '~') and
(D.DebitAccount_ID in (select ID from Accounts where Code like :AccountCode ESCAPE '~') or
D.CreditAccount_ID in (select ID from Accounts where Code like :AccountCode ESCAPE '~')) and
(:AmountUsed <> 'A' or D.Amount BETWEEN :AmountFrom and :AmountTo) and
(:AmountInCurrencyUsed <> 'A' or D.AmountInCurrency BETWEEN :AmountInCurrencyFrom and :AmountInCurrencyTo) and
(:OrdNumberUsed <> 'A' or D.OrdNumber BETWEEN :OrdNumberFrom and :OrdNumberTo) and
(D.Audited like :Audited) and
(:AccountSelID = '' or
D.CreditAccount_ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID) or
D.DebitAccount_ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID)) and
(:DocumentTypeSelID = '' or
D.AccDocQueue_ID IN (SELECT ID FROM AccDocQueues AD WHERE
AD.DocumentType in (select OBJ_ID from SELDAT where SEL_ID=:DocumentTypeSelID))) and
(:AccDocQueueSelID = '' or
D.AccDocQueue_ID in (select OBJ_ID from SELDAT where SEL_ID=:AccDocQueueSelID)) and
(:PeriodSelID = '' or
D.Period_ID in (select OBJ_ID from SELDAT where SEL_ID=:PeriodSelID)) and
(((D.Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :FirmSelID))
or
(D.Firm_ID in (SELECT ID FROM Firms WHERE Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :FirmSelID))))
OR
:FirmSelID = '')
and
(:CurrencySelID = '' or
D.Currency_ID in (select OBJ_ID from SELDAT where SEL_ID=:CurrencySelID)) and
( (:DivisionSelID = '') OR
(D.DebitDivision_ID IN (SELECT Bx.ID FROM Divisions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :DivisionSelID)) OR
((:ADivisionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$Divisions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :DivisionSelID)))))) ) OR
(D.CreditDivision_ID IN (SELECT Bx.ID FROM Divisions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :DivisionSelID)) OR
((:ADivisionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$Divisions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :DivisionSelID)))))) )
) AND
( (:BusOrderSelID = '') OR
(D.DebitBusOrder_ID IN (SELECT Bx.ID FROM BusOrders Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusOrderSelID)) OR
((:ABusOrdersWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusOrders2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusOrderSelID)))))) ) OR
(D.CreditBusOrder_ID IN (SELECT Bx.ID FROM BusOrders Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusOrderSelID)) OR
((:ABusOrdersWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusOrders2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusOrderSelID)))))) )
) AND
( (:BusTransactionSelID = '') OR
(D.DebitBusTransaction_ID IN (SELECT Bx.ID FROM BusTransactions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusTransactionSelID)) OR
((:ABusTransactionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusTransactions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusTransactionSelID)))))) ) OR
(D.CreditBusTransaction_ID IN (SELECT Bx.ID FROM BusTransactions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusTransactionSelID)) OR
((:ABusTransactionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusTransactions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusTransactionSelID)))))) )
) AND
( (:BusProjectSelID = '') OR
(D.DebitBusProject_ID IN (SELECT Bx.ID FROM BusProjects Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusProjectSelID)) OR
((:ABusProjectsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusProjects2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusProjectSelID)))))) ) OR
(D.CreditBusProject_ID IN (SELECT Bx.ID FROM BusProjects Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusProjectSelID)) OR
((:ABusProjectsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusProjects2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusProjectSelID)))))) )
)
UNION ALL
select Coalesce(D.CreditBusOrder_ID, '0000000000'), D.AccDate$DATE,
D.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or D.DebitAccount_ID in
(select AC.ID from Accounts AC where D.DebitAccount_ID = AC.ID
and AC.Code like :AccountCode ESCAPE '~'
and (:AccountSelID = '' or AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID)))),
D.Amount * (select Max(IValue) from SYS$DECIDETable where IValue = 0 or D.CreditAccount_ID in
(select AC.ID from Accounts AC where D.CreditAccount_ID = AC.ID
and AC.Code like :AccountCode ESCAPE '~'
and (:AccountSelID = '' or AC.ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID))))
from GeneralLedger D
where
/* (Coalesce(D.CreditBusOrder_id, '0000000000') <> Coalesce(D.DebitBusorder_ID, '0000000000')) and*/
IsRequest='N' and
(AccDate$DATE >= :AccDateFrom AND AccDate$DATE < :AccDateTo) and
(:TextUsed <> 'A' or Text like :Text ESCAPE '~') and
(D.DebitAccount_ID in (select ID from Accounts where Code like :AccountCode ESCAPE '~') or
D.CreditAccount_ID in (select ID from Accounts where Code like :AccountCode ESCAPE '~')) and
(:AmountUsed <> 'A' or D.Amount BETWEEN :AmountFrom and :AmountTo) and
(:AmountInCurrencyUsed <> 'A' or D.AmountInCurrency BETWEEN :AmountInCurrencyFrom and :AmountInCurrencyTo) and
(:OrdNumberUsed <> 'A' or D.OrdNumber BETWEEN :OrdNumberFrom and :OrdNumberTo) and
(D.Audited like :Audited) and
(:AccountSelID = '' or
D.CreditAccount_ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID) or
D.DebitAccount_ID in (select OBJ_ID from SELDAT where SEL_ID=:AccountSelID)) and
(:DocumentTypeSelID = '' or
D.AccDocQueue_ID IN (SELECT ID FROM AccDocQueues AD WHERE
AD.DocumentType in (select OBJ_ID from SELDAT where SEL_ID=:DocumentTypeSelID))) and
(:AccDocQueueSelID = '' or
D.AccDocQueue_ID in (select OBJ_ID from SELDAT where SEL_ID=:AccDocQueueSelID)) and
(:PeriodSelID = '' or
D.Period_ID in (select OBJ_ID from SELDAT where SEL_ID=:PeriodSelID)) and
(((D.Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :FirmSelID))
or
(D.Firm_ID in (SELECT ID FROM Firms WHERE Firm_ID in (select OBJ_ID from SELDAT where SEL_ID = :FirmSelID))))
OR
:FirmSelID = '')
and
(:CurrencySelID = '' or
D.Currency_ID in (select OBJ_ID from SELDAT where SEL_ID=:CurrencySelID)) and
( (:DivisionSelID = '') OR
(D.DebitDivision_ID IN (SELECT Bx.ID FROM Divisions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :DivisionSelID)) OR
((:ADivisionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$Divisions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :DivisionSelID)))))) ) OR
(D.CreditDivision_ID IN (SELECT Bx.ID FROM Divisions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :DivisionSelID)) OR
((:ADivisionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$Divisions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :DivisionSelID)))))) )
) AND
( (:BusOrderSelID = '') OR
(D.DebitBusOrder_ID IN (SELECT Bx.ID FROM BusOrders Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusOrderSelID)) OR
((:ABusOrdersWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusOrders2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusOrderSelID)))))) ) OR
(D.CreditBusOrder_ID IN (SELECT Bx.ID FROM BusOrders Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusOrderSelID)) OR
((:ABusOrdersWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusOrders2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusOrderSelID)))))) )
) AND
( (:BusTransactionSelID = '') OR
(D.DebitBusTransaction_ID IN (SELECT Bx.ID FROM BusTransactions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusTransactionSelID)) OR
((:ABusTransactionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusTransactions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusTransactionSelID)))))) ) OR
(D.CreditBusTransaction_ID IN (SELECT Bx.ID FROM BusTransactions Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusTransactionSelID)) OR
((:ABusTransactionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusTransactions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusTransactionSelID)))))) )
) AND
( (:BusProjectSelID = '') OR
(D.DebitBusProject_ID IN (SELECT Bx.ID FROM BusProjects Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusProjectSelID)) OR
((:ABusProjectsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusProjects2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusProjectSelID)))))) ) OR
(D.CreditBusProject_ID IN (SELECT Bx.ID FROM BusProjects Bx WHERE Bx.Hidden = 'N' AND
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :BusProjectSelID)) OR
((:ABusProjectsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from Sys$BusProjects2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :BusProjectSelID)))))) )
)
order by 1
into mAccGroup_ID, mAccDate$DATE, mDebetAmount, mCreditAmount
do begin
if (AccGroup_ID = '__________' or ((AccGroup_ID = mAccGroup_ID) or (AccGroup_ID is null and mAccGroup_ID is null))) then begin
CreditAmount = CreditAmount + mCreditAmount;
DebetAmount = DebetAmount + mDebetAmount;
AccGroup_ID = mAccGroup_ID;
AccDate$DATE = mAccDate$DATE;
end
else begin
FOR
SELECT ID
FROM GeneralLedgerGroupsBOA1(:AccDateFrom, :AccDateTo, :AccountCode,
:AccountSelID, :WholeGroup, :AccGroup_ID, :PeriodSelID, :TextUsed, :Text,
:AmountUsed, :AmountFrom, :AmountTo, :AmountInCurrencyUsed, :AmountInCurrencyFrom,
:AmountInCurrencyTo, :OrdNumberUsed, :OrdNumberFrom, :OrdNumberTo, :Audited,
:DocumentTypeSelID, :AccDocQueueSelID, :CurrencySelID, :FirmSelID, :DivisionSelID,
:ADivisionsWithChilds, :BusOrderSelID, :ABusOrdersWithChilds, :BusTransactionSelID,
:ABusTransactionsWithChilds, :BusProjectSelID, :ABusProjectsWithChilds)
INTO :ID
DO BEGIN
IF (ShowGroups = '0') THEN
SUSPEND;
IF (ShowGroups = '1' AND
((CreditAmount <= DebetAmount AND DebetAmount - CreditAmount >= 0.01) OR
(CreditAmount > DebetAmount AND CreditAmount - DebetAmount >= 0.01)) )
THEN
SUSPEND;
IF (ShowGroups = '2' AND
((CreditAmount <= DebetAmount AND DebetAmount - CreditAmount < 0.01) OR
(CreditAmount > DebetAmount AND CreditAmount - DebetAmount < 0.01)) )
THEN
SUSPEND;
END
CreditAmount = mCreditAmount;
DebetAmount = mDebetAmount;
AccGroup_ID = mAccGroup_ID;
AccDate$DATE = mAccDate$DATE;
end
end
/* Tady je ještě potřeba provést pro poslední data !!!! */
if (AccGroup_ID <> '__________') then begin
FOR
SELECT ID
FROM GeneralLedgerGroupsBOA1(:AccDateFrom, :AccDateTo, :AccountCode,
:AccountSelID, :WholeGroup, :AccGroup_ID, :PeriodSelID, :TextUsed, :Text,
:AmountUsed, :AmountFrom, :AmountTo, :AmountInCurrencyUsed, :AmountInCurrencyFrom,
:AmountInCurrencyTo, :OrdNumberUsed, :OrdNumberFrom, :OrdNumberTo, :Audited,
:DocumentTypeSelID, :AccDocQueueSelID, :CurrencySelID, :FirmSelID, :DivisionSelID,
:ADivisionsWithChilds, :BusOrderSelID, :ABusOrdersWithChilds, :BusTransactionSelID,
:ABusTransactionsWithChilds, :BusProjectSelID, :ABusProjectsWithChilds)
INTO :ID
DO BEGIN
IF (ShowGroups = '0') THEN
SUSPEND;
IF (ShowGroups = '1' AND
((CreditAmount <= DebetAmount AND DebetAmount - CreditAmount >= 0.01) OR
(CreditAmount > DebetAmount AND CreditAmount - DebetAmount >= 0.01)) )
THEN
SUSPEND;
IF (ShowGroups = '2' AND
((CreditAmount <= DebetAmount AND DebetAmount - CreditAmount < 0.01) OR
(CreditAmount > DebetAmount AND CreditAmount - DebetAmount < 0.01)) )
THEN
SUSPEND;
END
end
END;