select distinct b.ID BranchID,nSubjectType, b.sCode BranchNo, b.sName BranchName, b.sBankAccountCode BranchAccountNo, b.NBANKTYPE BranchTypeID, TO_CHAR(decode(ba.status, 2, 0, 5, 0, 7, 0, decode(nSubjectType, 4, 1, 5, 1, -1) * ((nvl(CurrentBalance.mDebitBalance, 0) + nvl(CurrentBalance.mCreditBalance, 0) + nvl(b.transamount, 0))) - nvl(basub.freezeamount, 0)), '999,999,999,999,999,999,990.99') muserableBalance from sett_vbranchtransamount b, ba_bankaccount ba, ba_subaccount basub, (select distinct * from sett_GlBalance where dtGlDate in (select dtopendate from sett_officetime where nofficeid = 1 and ncurrencyid = 1) and nofficeid = 1 and ncurrencyid = 1) CurrentBalance where b.sSubjectCode = CurrentBalance.sGlSubjectCode(+) and b.NBANKACCOUNTID = ba.id and ba.id = basub.accountid(+) and ba.status in (1, 8, 2) and b.nOfficeID = 1 and b.nCurrencyID = 1 order by b.sCode;
1.其中CurrentBalance表是这样的:
科目余额表select distinct * from sett_GlBalance where dtGlDate in (select dtopendate from sett_officetime where nofficeid = 1 and ncurrencyid = 1) and nofficeid = 1 and ncurrencyid = 1
2.取出当日该开户行的科目余额(开户行的对应科目在sett_Branch表中的SSUBJECTCODE字段)
3.sett_vbranchtransamount 的SQL语句如下:
select sett_Branch."ID", sett_Branch."NOFFICEID", sett_Branch."SCODE", sett_Branch."SNAME", sett_Branch."SSUBJECTCODE", sett_Branch."SBRANCHPROVINCE", sett_Branch."SBRANCHCITY", sett_Branch."NSTATUSID", sett_Branch."SBANKACCOUNTCODE", sett_Branch."SCREDITBOOKEDACCOUNT", sett_Branch."SDEBITBOOKEDACCOUNT", sett_Branch."NISSINGLE", sett_Branch."NCURRENCYID", sett_Branch."SCASHCREDITBOOKEDACCOUNT", sett_Branch."SCASHDEBITBOOKEDACCOUNT", sett_Branch."STRANSFERCREDITBOOKEDACCOUNT", sett_Branch."STRANSFERDEBITBOOKEDACCOUNT", sett_Branch."SPRINTNAME", sett_Branch."NBANKTYPE", sett_Branch."NISAUTOVIREMENTBYBANK", sett_Branch."SBANKSERVICENAME", sett_Branch."SENTERPRISENAME", sett_Branch."SBANKEXCHANGECODE", sett_Branch."SBRANCHCODE", sett_Branch."NACCOUNTTYPEID", sett_Branch."NDEPOSITTERM", sett_Branch."SINTERESTSUBJECT", sett_Branch."SPANSUBJECT", sett_Branch."NBANKACCOUNTID", nvl(nSubjectType, 1) nSubjectType, nvl(vcheckedtrans.mamount, 0) amount, nvl(vcheckedtrans.mamount, 0) + nvl(vunchecktrans.mamount, 0) transamount from sett_Branch, (select nbankid, sum(mamount) mamount from sett_vbranchunchecktransamount group by nbankid) vunchecktrans, sett_vbranchcheckedtransamount vcheckedtrans, sett_VglSubjectDefinition vsubject where sett_Branch.sSubjectCode = vsubject.sSubjectCode(+) and sett_Branch.nOfficeID = vsubject.nOfficeID(+) and sett_Branch.ncurrencyid = vsubject.ncurrencyid(+) and sett_Branch.sSubjectCode = vcheckedtrans.sSubjectCode(+) and sett_Branch.nOfficeID = vcheckedtrans.nOfficeID(+) and sett_Branch.ncurrencyid = vcheckedtrans.ncurrencyid(+) and sett_Branch.id = vunchecktrans.nbankid(+) and sett_Branch.nStatusID = 1 and sett_branch.naccounttypeid = 1 order by sett_Branch.Scode 注: b.mamount = nvl(vcheckedtrans.mamount, 0) + nvl(vunchecktrans.mamount, 0) ;