|
楼主 |
发表于 2012-1-14 14:09
|
显示全部楼层
本帖最后由 hxj710802 于 2012-1-14 14:10 编辑
上传下截取的生成会计科目余额表的SQL语句
- select ACCOUNT.STRACCOUNTCODE As "科目编码",ACCOUNT.STRACCOUNTNAME As "科目名称",SUM(DECODE(ACCOUNT.INTDIRECTION,1,1,0)*DECODE(SIGN(TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD')-TO_DATE('2011-12-01','YYYY-MM-DD')),-1,1,0)*((dblUnPostedDebit+dblPostedDebit)-(dblUnPostedCredit+dblPostedCredit)))+SUM(DECODE(ACCOUNT.INTDIRECTION,-1,1,0)*DECODE(SIGN(TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD')-TO_DATE('2011-12-01','YYYY-MM-DD')),-1,1,0)*((dblUnPostedCredit+dblPostedCredit)-(dblUnPostedDebit+dblPostedDebit))) As "期初余额本币金额",SUM(DECODE(SIGN(TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD')- TO_DATE('2011-12-01','YYYY-MM-DD')),-1,0,1)*DECODE(SIGN(TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD')- TO_DATE('2011-12-31','YYYY-MM-DD')),1,0,1)*(dblUnPostedDebit+dblPostedDebit)) As "本期借方发生额本币金额",SUM(DECODE(SIGN(TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD')- TO_DATE('2011-12-01','YYYY-MM-DD')),-1,0,1)*DECODE(SIGN(TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD')- TO_DATE('2011-12-31','YYYY-MM-DD')),1,0,1)*(dblUnPostedCredit+dblPostedCredit)) As "本期贷方发生额本币金额",DECODE(ACCOUNT.INTDIRECTION,1,'借','贷') As "方向",SUM(DECODE(ACCOUNT.INTDIRECTION,1,1,0)*DECODE(SIGN(TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD')-TO_DATE('2011-12-31','YYYY-MM-DD')),1,0,1)*((dblUnPostedDebit+dblPostedDebit)-(dblUnPostedCredit+dblPostedCredit)))+SUM(DECODE(ACCOUNT.INTDIRECTION,-1,1,0)*DECODE(SIGN( TO_DATE(ACCOUNTDAILY.STRDATE,'YYYY-MM-DD')-TO_DATE('2011-12-31','YYYY-MM-DD')),1,0,1)*((dblUnPostedCredit+dblPostedCredit)-(dblUnPostedDebit+dblPostedDebit))) As "期末余额本币金额",max(Account.blnIsDetail),max(Account.intLevel),Max(Account.lngAccountTypeID) From Account,AccountDaily,Currencys,Customer,CustomerType,Employee,Organization,Organization OrganizationAux Where ((Upper(Account.strAccountCode)=Upper('521') Or Account.strAccountCode Like '521-%')) And Account.lngAccountID=AccountDaily.lngAccountID And Currencys.lngCurrencyID(+)=AccountDaily.lngCurrencyID And Customer.lngCustomerID(+)=AccountDaily.lngCustomerID And Customer.lngCustomerTypeID = CustomerType.lngCustomerTypeID(+) And Employee.lngEmployeeID(+)=AccountDaily.lngEmployeeID AND AccountDaily.lngOrganizationID = Organization.lngOrganizationID(+) AND AccountDaily.lngSourceOrganizationID = OrganizationAux.lngOrganizationID(+) And Abs(dblPostedDebit)+ Abs(dblUnPostedDebit)+Abs(dblPostedCredit)+Abs(dblUnPostedCredit)+ Abs(dblCurrencyPostedDebit)+Abs(dblCurrencyUnPostedDebit)+Abs(dblCurrencyPostedCredit)+Abs(dblCurrencyUnPostedCredit)+Abs(dblQuantityPostedDebit)+Abs(dblQuantityUnPostedDebit)+Abs(dblQuantityPostedCredit)+Abs(dblQuantityUnPostedCredit)<>0 AND AccountDaily.strDate <= '2011-12-31' Group By ACCOUNT.STRACCOUNTCODE,ACCOUNT.STRACCOUNTNAME,ACCOUNT.INTDIRECTION Order By ACCOUNT.STRACCOUNTCODE,ACCOUNT.STRACCOUNTNAME,ACCOUNT.INTDIRECTION
复制代码
|
|