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

 

 

 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表是这样的：
科目余额表
２．取出当日该开户行的科目余额（开户行的对应科目在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) ;
posted @ 2010-07-21 10:05  0707  阅读(467)  评论(1)  编辑  收藏  举报