oracle的汇总模板,Oracle EBS新汇总模板创建系统余额表的记录

近来，开发客制化总帐相关报表遇到一个问题:

发现按一级科目汇总，或二级科目汇总，或三级科汇总后，各个初期金额不等。提交请求“科目表 - 帐户分层结构”查看层次结构也是没有问题。通过对比以后发现，部分二级科目和三级科目没有在系统余额表中汇总记录。

通过系统中准标查询功能: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