近来,开发客制化总帐相关报表遇到一个问题:
发现按一级科目汇总,或二级科目汇总,或三级科汇总后,各个初期金额不等。提交请求“科目表 - 帐户分层结构”查看层次结构也是没有问题。通过对比以后发现,部分二级科目和三级科目没有在系统余额表中汇总记录。
通过系统中准标查询功能:GL->查询->帐户,查询出结果也是二级科目和三级科目也是没有初至今或初至年的余额。
最后,在GL模块中,找到一个请求“程序 - 逐级添加/删除汇总模板”提交后发现二级科目汇总的余额就产生了。
另外提供一些参考SQL:
Select ACCOUNT_NUM,
ACCD_DESC,
Sum(START_DEBIT) START_DEBIT,
Sum(START_CREDIT) START_CREDIT,
Sum(DEBIT_AMOUNT) DEBIT_AMOUNT, --借方
Sum(CREDIT_AMOUNT) CREDIT_AMOUNT, --贷方
Sum(END_DEBIT) END_DEBIT,
Sum(END_CREDIT) END_CREDIT,
Sum(YEAR_DEBIT) YEAR_DEBIT,
Sum(YEAR_CREDIT) YEAR_CREDIT
From (Select ACC.FLEX_VALUE ACCOUNT_NUM,
ACC.DESCRIPTION ACCD_DESC,
NVL(BEGIN_BALANCE_DR, 0) START_DEBIT,
NVL(BEGIN_BALANCE_CR, 0) START_CREDIT,
NVL(PERIOD_NET_DR, 0) DEBIT_AMOUNT, --借方
NVL(PERIOD_NET_CR, 0) CREDIT_AMOUNT, --贷方
NVL(PERIOD_NET_DR, 0) + NVL(BEGIN_BALANCE_DR, 0) END_DEBIT,
NVL(PERIOD_NET_CR, 0) + NVL(BEGIN_BALANCE_CR, 0) END_CREDIT,
0 YEAR_DEBIT,
0 YEAR_CREDIT
From GL_BALANCES glb,
GL_CODE_COMBINATIONS GLCC,
CUX_GL_ACCOUNT_V ACC
Where GLB.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
And GLB.SET_OF_BOOKS_ID = &P_SET_OF_BOOKS_ID
And GLCC.SEGMENT3 = ACC.FLEX_VALUE
And ACC.STRUCTURED_HIERARCHY_LEVEL Is Null
And GLB.PERIOD_NAME = &P_PERIOD_NAME
And (GLB.TEMPLATE_ID = &P_TEMPLATE_ID Or &P_TEMPLATE_ID Is Null)
And GLCC.SEGMENT1 = &P_COMPANY
AND GLB.ACTUAL_FLAG = 'A'
Union All
Select ACC.FLEX_VALUE ACCOUNT_NUM,
ACC.DESCRIPTION ACCD_DESC,
0 START_DEBIT,
0 START_CREDIT,
0 DEBIT_AMOUNT, --借方
0 CREDIT_AMOUNT, --贷方
0 END_DEBIT,
0 END_CREDIT,
Sum(DECODE(GLB.PERIOD_NAME,
&P_PERIOD_NAME,
NVL(PERIOD_NET_DR, 0) + NVL(BEGIN_BALANCE_DR, 0),
0)) YEAR_DEBIT,
Sum(DECODE(GLB.PERIOD_NAME,
&P_PERIOD_NAME,
NVL(PERIOD_NET_CR, 0) + NVL(BEGIN_BALANCE_CR, 0),
0)) YEAR_CREDIT
From GL_BALANCES glb,
GL_CODE_COMBINATIONS GLCC,
CUX_GL_ACCOUNT_V ACC
Where GLB.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
And GLB.SET_OF_BOOKS_ID = &P_SET_OF_BOOKS_ID
And GLCC.SEGMENT3 = ACC.FLEX_VALUE
/*And ACC.STRUCTURED_HIERARCHY_LEVEL Is Null*/
And GLB.PERIOD_NAME In (&FIRST_PERIOD_NAME, &P_PERIOD_NAME)
And (GLB.TEMPLATE_ID = &P_TEMPLATE_ID Or &P_TEMPLATE_ID Is Null)
And GLCC.SEGMENT1 = &P_COMPANY
AND GLB.ACTUAL_FLAG = 'A'
Group By GLCC.SEGMENT2, ACC.FLEX_VALUE, ACC.DESCRIPTION) T1
Group By ACCOUNT_NUM, ACCD_DESC