BEGIN
/* Omezeni deniku za skupinu uctu */
mAccountCode = InAccountCode;
IF (mAccountCode = '') THEN
mAccountCode = '%';
FOR
/* Debit Side - MD */
SELECT ALL
A.ID,
A.Amount,
CAST(1 AS INTEGER), /* A.DebitSide */
CAST(0 AS INTEGER), /* A.CreditSide */
A.AccDate$DATE,
A.AccDocQueue_ID,
A.Period_ID,
A.OrdNumber,
A.Text,
A.Firm_ID,
A.DebitAccount_ID,
A.CreditAccount_ID,
A.DebitDivision_ID,
A.CreditDivision_ID,
A.DebitBusOrder_ID,
A.CreditBusOrder_ID,
A.DebitBusTransaction_ID,
A.CreditBusTransaction_ID,
A.DebitBusProject_ID,
A.CreditBusProject_ID
FROM
GeneralLedger A
LEFT join Accounts AC ON A.DebitAccount_ID = AC.ID
WHERE
( (:InAccountSelID = '') OR
(A.DebitAccount_ID IN (SELECT OBJ_ID FROM SelDat WHERE SEL_ID = :InAccountSelID)) ) AND
A.Audited LIKE :InAudited AND
(A.IsRequest = 'N' or (:InAllowRequest = 'A' and A.IsRequest = :InAllowRequest)) AND
(A.AccDate$DATE >= :InAccDateFrom and A.AccDate$DATE < :InAccDateTo) AND
( (:InDivisionSelID = '') OR
(A.DebitDivision_ID IN (SELECT Bx.ID FROM Divisions Bx WHERE
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :InDivisionSelID)) OR
((:InDivisionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from SYS$Divisions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :InDivisionSelID)))))) ) ) AND
( (:InBusOrderSelID = '') OR
(A.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 = :InBusOrderSelID)) OR
((:InBusOrdersWithChilds in ('1', 'A')) and (Bx.ID in (select ID from SYS$BusOrders2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :InBusOrderSelID)))))) ) ) AND
( (:InBusTransactionSelID = '') OR
(A.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 = :InBusTransactionSelID)) OR
((:InBusTransactionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from SYS$BusTransactions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :InBusTransactionSelID)))))) ) ) AND
( (:InBusProjectSelID = '') OR
(A.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 = :InBusProjectSelID)) OR
((:InBusProjectsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from SYS$BusProjects2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :InBusProjectSelID)))))) ) ) AND
( (:InFirmSelID = '') OR
(A.Firm_ID IN (SELECT OBJ_ID FROM SelDat WHERE SEL_ID = :InFirmSelID)) OR
(A.Firm_ID IN (SELECT ID FROM Firms WHERE Firm_ID IN (SELECT OBJ_ID FROM SelDat WHERE SEL_ID = :InFirmSelID))) ) AND
((AC.Code LIKE :mAccountCode ESCAPE '~') OR ((:InAllowRequest = 'A') AND (:mAccountCode = '%') AND (AC.Code IS NULL))) AND
((AC.AccountType LIKE :InAccountType ESCAPE '~') OR ((:InAllowRequest = 'A') AND (:InAccountType = '%') AND (AC.AccountType IS NULL)))
UNION ALL
/* Credit Side - D */
SELECT ALL
A.ID,
A.Amount,
CAST(0 AS INTEGER), /* A.DebitSide */
CAST(1 AS INTEGER), /* A.CreditSide */
A.AccDate$DATE,
A.AccDocQueue_ID,
A.Period_ID,
A.OrdNumber,
A.Text,
A.Firm_ID,
A.CreditAccount_ID,
A.DebitAccount_ID,
A.CreditDivision_ID,
A.DebitDivision_ID,
A.CreditBusOrder_ID,
A.DebitBusOrder_ID,
A.CreditBusTransaction_ID,
A.DebitBusTransaction_ID,
A.CreditBusProject_ID,
A.DebitBusProject_ID
FROM
GeneralLedger A
LEFT join Accounts AC ON A.CreditAccount_ID = AC.ID
WHERE
( (:InAccountSelID = '') OR
(A.CreditAccount_ID IN (SELECT OBJ_ID FROM SelDat WHERE SEL_ID = :InAccountSelID)) ) AND
A.Audited LIKE :InAudited AND
(A.IsRequest = 'N' or (:InAllowRequest = 'A' and A.IsRequest = :InAllowRequest)) AND
(A.AccDate$DATE >= :InAccDateFrom and A.AccDate$DATE < :InAccDateTo) AND
( (:InDivisionSelID = '') OR
(A.CreditDivision_ID IN (SELECT Bx.ID FROM Divisions Bx WHERE
( (Bx.ID in (select OBJ_ID from SELDAT where SEL_ID = :InDivisionSelID)) OR
((:InDivisionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from SYS$Divisions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :InDivisionSelID)))))) ) ) AND
( (:InBusOrderSelID = '') OR
(A.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 = :InBusOrderSelID)) OR
((:InBusOrdersWithChilds in ('1', 'A')) and (Bx.ID in (select ID from SYS$BusOrders2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :InBusOrderSelID)))))) ) ) AND
( (:InBusTransactionSelID = '') OR
(A.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 = :InBusTransactionSelID)) OR
((:InBusTransactionsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from SYS$BusTransactions2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :InBusTransactionSelID)))))) ) ) AND
( (:InBusProjectSelID = '') OR
(A.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 = :InBusProjectSelID)) OR
((:InBusProjectsWithChilds in ('1', 'A')) and (Bx.ID in (select ID from SYS$BusProjects2 where Superior_ID in (select OBJ_ID from SELDAT where SEL_ID = :InBusProjectSelID)))))) ) ) AND
( (:InFirmSelID = '') OR
(A.Firm_ID IN (SELECT OBJ_ID FROM SelDat WHERE SEL_ID = :InFirmSelID)) OR
(A.Firm_ID IN (SELECT ID FROM Firms WHERE Firm_ID IN (SELECT OBJ_ID FROM SelDat WHERE SEL_ID = :InFirmSelID))) ) AND
((AC.Code LIKE :mAccountCode ESCAPE '~') OR ((:InAllowRequest = 'A') AND (:mAccountCode = '%') AND (AC.Code IS NULL))) AND
((AC.AccountType LIKE :InAccountType ESCAPE '~') OR ((:InAllowRequest = 'A') AND (:InAccountType = '%') AND (AC.AccountType IS NULL)))
INTO
:ID,
:Amount,
:DebitSide,
:CreditSide,
:AccDate$Date,
:AccDocQueue_ID,
:Period_ID,
:OrdNumber,
:Text,
:Firm_ID,
:Account_ID,
:SAccount_ID,
:Division_ID,
:SDivision_ID,
:BusOrder_ID,
:SBusOrder_ID,
:BusTransaction_ID,
:SBusTransaction_ID,
:BusProject_ID,
:SBusProject_ID
DO BEGIN
/* Suspendnuti vety */
SUSPEND;
END
END;