科目余额表 v_Balance
职员信息表 v_EmpDepart
会证凭证 v_SumUpostVoucher
凭证表 t_Voucher
凭证分录表 t_VoucherEntry
----------------------
SELECT mt.FYear, mt.FPeriod,
a.FAccountID FAccountID,
a.FNumber FAccountNumber,
a.FName FAccountName,
a.FGroupID FAccountGroup,
a.FLevel FAccountLevel,
a.FDetail FAccountDetail, a.FDC,
mt.FCurrencyID, mt.FCurrencyNumber, mt.FCurrencyName,
mt.FBeginBalanceFor, mt.FDebitFor, mt.FCreditFor, mt.FYtdDebitFor, mt.FYtdCreditFor,
mt.FDebitFor - mt.FCreditFor FEndBalanceFor,
mt.FBeginBalance, mt.FDebit, mt.FCredit,mt.FYtdDebit, mt.FYtdCredit,
mt.FDebit - mt.FCredit FEndBalance
FROM (SELECT v.FYear,
v.FPeriod,
a.FAccountID FAccountID,
a.FNumber FAccountNumber,
c.FCurrencyID,
c.FNumber FCurrencyNumber,
c.FName FCurrencyName,
0 FBeginBalanceFor,
SUM(e.FAmountFor * e.FDC) FDebitFor,
SUM(e.FAmountFor * (1 - e.FDC)) FCreditFor,
SUM(e.FAmountFor * e.FDC) FYtdDebitFor,
SUM(e.FAmountFor * (1 - e.FDC)) FYtdCreditFor,
0 FEndBalanceFor,
0 FBeginBalance,
SUM(e.FAmount * e.FDC) FDebit,
SUM(e.FAmount * (1 - e.FDC)) FCredit,
SUM(e.FAmount * e.FDC) FYtdDebit,
SUM(e.FAmount * (1 - e.FDC)) FYtdCredit,
0 FEndBalance
FROM t_Voucher v, t_VoucherEntry e, t_Account a, t_Currency c
WHERE v.FVoucherID = e.FVoucherID AND e.FAccountID = a.FAccountID AND
e.FCurrencyID = c.FCurrencyID --AND v.FPosted = 0
GROUP BY v.FYear, v.FPeriod, a.FAccountID, a.FNumber, c.FCurrencyID,
c.FNumber, c.FName) mt,
t_Account a
WHERE (mt.FAccountNumber LIKE a.FNumber + '.%' or mt.FAccountNumber = a.FNumber)
AND mt.fperiod = 5