开户行账户可用余额的计算方法

 

 

 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;

 

 

１．其中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

２．取出当日该开户行的科目余额（开户行的对应科目在sett_Branch表中的SSUBJECTCODE字段）

 

 

３．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) ;