基于ARP底表的课题经费使用情况查询功能实现探讨.doc

基于ARP底表的课题经费使用情况查询功能实现探讨（部分供参考）
1 前言
目前各研究所的所级ARP系统和网上报销系统已正式使用,课题经费使用情况查询多在ARP财务模块中或通过discover报表进行,操作不方便和不直观,有些研究所,特别是按课题本进行经费核算的研究所,希望能为一线课题负责人和所领导提供实时快捷的经费使用情况查询功能.笔者就此对ARP系统底表进行分析研究,介绍如下.
2 主要功能
课题经费使用情况查询为独立于所级ARP的外挂程序,可集成在各所portal或内部办公系统(所务公开网)中,功能是课题(本)负责人登录后,查看自已负责的课题(本)信息和某段时间收支累计和明细;所领导或科研处、财务处管理人员登录后,能按课题名称、课题编号、负责人等查询所有课题的经费信息.具体包括:
提供组合查询功能,结合身份验证,列出全部或自已负责的课题本信息及当前余额.
列出课题本各期间的期初期末余额和收支额,提供按年度、时间段查询和收支累积.
列出课题本某期间或时间段内每笔收入、支出金额、凭证号、摘要、经手人等明细信息.
列出课题借还款明细,列出累积欠款额.
所级ARP系统中,课题及产出物信息在科研项目模块维护,课题收支在综合财务模块管理.收支明细记录于总帐的日记帐中,课题与日记帐间按帐套、会计期间、币种与会计科目号关联,会计科目由单位段、部门段、科目段、子目段、课题段、备用段共同组成,其中课题段即相当于课题本号,课题(本)经费查询主要按其汇总.
3 用户与权限
查询功能可能独立运行,也可能集成在其它系统,应用程序需自带用户身份认证模块,同时也提供和支持与其它系统的接口.
Oracle底表中没有记录人员的查询权限级别的信息项,对于查询全部课题本经费信息的用户需能指定和配置.
普通用户可直接根据ARP系统中课题人员信息实时控制权限,即按子课题信息表和子课题人员表关联查询列出所负责课题,但这种方式无法列出虚拟课题号和不支持指定课题的第二负责人或其它查询人员.若将课题本信息一次性导出后为应用程序建立专门课题本与用户的查询授权对照表,控制方式灵活,但日后变化时需进行相应维护.结合两种方式优缺点,可以采用以ARP课题人员表为主,另配有对虚拟课题或第二查询人进行补充的授权表方式,即同时根据ARP课题人员表和补充的授权对照表控制权限.
由于不建议直接在Oracle ERP 库中建表或增改字段,用户信息与补充授权表存储在应用程序部署的服务器上(如加密码的mdb库),跨服务器的组合权限控制由应用程序验证模块实现.
4 底表分析
ORACLE ERP中有3万多底表、视图与数十万字段,关系复杂.一般结合系统界面可以针对性获得当前FORM相应表或视图信息,然后通过查看技术手册或Fnd_Tables、Fnd_Columns、Fnd_Views表获得详细信息.
ARP底表中,会计科目表为gl_code_combinations ,课题段为segment5字段,课题段的说明文字对应弹性域表fnd_flex_values 、fnd_flex_values_tl 的cas_coa_proj值集.子课题信息表为pa_tasks ,子课题人员信息表可使用视图pa_project_players_v查看,两者通过task_id关联,与会计科目表的课题段通过课题编号(task_number)关联.总帐余额表为gl_balances,日记帐表分别为gl_je_batches(日记批)、gl_je_headers(日记头)、gl_je_lines(日记明细),均通过code_combination_id与科目表关联,其它相关表还有:gl_sets_of_books(帐套表)、gl_periods(会计期间表)等[3] [4].
图1 ERP中GL、PA部分底表关系图
5 课题本信息列表
课题编号和课题名称在科研项目中,但不含虚拟课题(行政部门经费),完整的课题本帐号信息在综合财务的会计科目表课题段中,帐号名称由项目模块传达到帐务时自动编译生成,常由由负责人+旧本号+经费来源+课题名称组成.课题帐号列表的实现有取自项目模块的真实课题名称或取自财务模块的课题段说明两种方式.
前者课题编号和课题名称分别对应子课题表的task_number和task_longname,负责人对应课题人员表的full_name.其中子课题表中,同时含有管理需要或方便系统运行用配置的非真实课题等,需进行屏蔽,如service_type_code<>'管理需要' 或task_number in (select segment5 from gl_code_combinations) ;课题人员表中,包括课题组长、主要人员等,需通过人员角色(role)来判断,代码如:
select task_number as 课题编号,task_longname as 课题名称,project_id from pa_tasks where task_number in (select segment5 from gl_code_combinations) and project_id in (select project_id from pa_project_players_v where role ='10课题组长' and person_id=人员编号) order by task_number
后者课题本号取自科目表课题段,课题本名称、负责人、旧课题本号等可从弹性域说明中获得,脚本如:
select a.flex_value as 课题本号, c.description as 课题本名称 , case when instr ( c.description 0 then substr ( c.description , 1 , instr ( c.description 1 ) when length ( c.description ) > 11 then substr ( c.description , 11 , length ( c.description ) – 10 ) else '' end as 负责人from fnd_flex_values a, fnd_flex_value_sets b,fnd_flex_values_tl c where a.flex_value_set_id = b.flex_value_set_id and c.flex_value_id = a.flex_value_id and c.language = 'ZHS' and a.enabled_flag = 'n' and b.flex_value_set_name = 'cas_coa_proj' order by a.flex_value
系统实现时对两种方式均支持,能通过配置首选项选择,同时提供组合查询、自动判断当前期间和计算各课题当前余额功能.如图2.
图2 课题本查询与列表
6 课题各月收支情况和余额列表
各月收支情况可从gl_balances中计算获得.
gl_balances表中记录有各会计科目在各期间的实际发生额、预算金额或保留款(encumbrance)金额,课题经费查询只需查询实际发生的,可通过actual_flag='a'标识判断.
一个课题本(段)可能与其它段,如科目段、部门段交叉涉及多个会计科目,在查询某课题收支情况时,需将相关科目按期间进行汇总.会计科目表中含实际科目和汇总用科目上,可根据 summary_flag与template_id 判断,实际明细科目为 summary_flag ='n' and template_id null..
本期开支和收入金额数分别对应period_net_dr 、period_net_c字段,期初开支或收入数对应begin_balance_dr 与begin_balance_cr字段,各期当前余额可由四者加减计算出来,即:begin_balance_cr - begin_balance_dr + period_net_cr - period_net_dr .
课题经费查询的帐套应对应"科研事业费帐套",具体id号通过帐套信息表gl_sets_of_books查询.期间对应gl_periods表,只要期间打开此表中即有相应记录,其中第13个月为虚拟期间,需将其过滤(period_num<>13).示例代码:
select period_name as 期间,sum(period_net_dr) as 支出,sum(period_net_cr) as 收入, ( sum ( begin_balance_cr ) + sum ( period_net_cr ) – sum ( begin_balance_dr ) – sum ( period_net_dr ) ) as 余额 , period_year , period_num from gl_balances b1, (select code_combination_id from gl_code_combinations where segment5 = '课题编号' and summary_flag = 'n' ) c1 where actual_flag='a' and period_num<>13 and set_of_books_id = 1 and c1.code_combination_id = b1.code_combination_id group by period_year , period_num , period_name
系统中列出课题各期收支情况时,提供按年度查询,自动累积全年度的收支总额.如图3.
图3 课题各期间收支概况
7 课题本收支、摘要的明细列表
课题的收支明细均记录于日记帐中,主要涉及gl_je_batches、gl_je_headers、gl_je_lines三表,具体金额、日期、摘要、经手人均以明细表(gl_je_lines)为准.
凭单号:可取自日记帐头名的前11位.
收支时间:对应gl_je_lines的effective_date.
收支金额:有entered _dr、entered _cr、accounted_dr、accounted_cr 几个字段,前两个为输入时币种(可能为外币)数额,后两者对应折合基本币种的数额,应以后者为准.
摘要:对应gl_je_lines的description(不是gl_je_headers的description).有些日记帐为从工资、采购、年底结转、网上报销或其它模块导入,此处均显示为"已创建日记帐导入",或后面附带的有请求标识号,可尽量根据日记帐的批名中间部分文字来区别.
经手人:对应gl_je_lines的attribute1 .从应付过来等日记帐导入者,需从应付模块(如ap_invoices_v)来查询.
日记帐明细表中,同时含有中转科目等信息,可通过余额类型判断过滤,je_headers.actual_flag='a' (不能含b或e)
收支明细查询中,对于最近信息,因网上报销等系统应用,可能尚有一些未过帐的凭证.这类凭证在标准ERP中是不提供查询的.是否过帐的状态由status标记,p表示已过帐,u表示未过帐,查询中含未过帐,即相当于能实时查询最新信息,余额表不含此未过帐信息,展示时应单独标识.示例代码如下:
select effective_date as 日期,accounted_dr as 支出 ,accounted_cr as 收入,substr(gjh.name,1,11) as 凭证号 ,(case when gjl.description<>'已创建日记帐导入' then gjl.description else substr(name,18,3) end) || decode(gjl.status,'U','(尚未过帐)','') as 摘要 from gl_je_lines gjl,gl_je_headers gjh, (select code_combination_id from gl_code_combinations where segment5='课题本号' and summary_flag='n') gcc where gjh.je_header_id=gjl.je_header_id and gjh.actual_flag='a' and gjl.set_of_books_id=1 and gjl.period_name='期间' and gcc.code_combination_id= gjl.code_combination_id order by gjl.effective_date
图4 课题某期间收支明细
8 借款情况查询、按收支类别分析
按期间的收支明细查询中,包含了课题本对应的所有科目(资产类、负债、收入、费用、所有权等),其中借款按支出、还款按收入进行处理.为方便查看借款情况,可只将所有"其他应收款"查询列出(ARP中的110科目下,and segment3 like '110%') ,然后在显示时计算累计欠款额.若需排除已冲销的业务,见需进一步与应付模块关联.
类似的,可通过余额表(gl_balances)与相关表关联并根据需要进行分类,实现某些或全部课题按经费来源或支出类别的汇总分析.
图5 课题借款还款情况
9 开发、部署与安全
表结构与关系分析清楚后,编码容易,根据不同应用环境,同时开发提供.net版和jsp版.
Oracle应用系统11i版本是完全基于互联网INTERNET架构,中科院ARP沿用Oracle安全方案,所级ARP系统逻辑上属于ARP区,所内ARP区与非ARP区之间及INTERNET通过VPN防火墙进行阻隔和安全管理.经费使用情况查询直接操作ARP的oracle底表,要与oracle数据库联通,需部署ARP区或能互通的内网,否则需要进行路由设置和防火墙调整.
ERP系统中Apps帐户(schema)拥有访问整个EBS数据模型的权限,可以访问操作整个数据库包括views,packages,procedures,functions,triggers等.为了安全,查询程序中连接操作数据库表的帐号尽量不要使用此帐号,可新建专门帐号和只进行相关表的查询授权.如:create user 帐号 identified by 密码; grant create session to 帐号; grant select on 表名 to 帐号.
10 总结
经费使用情况查询是研究所科研人员及领导非常关心和实用的功能,标准的Oracle ERP中无法提供和不方便,通过分析Oracle ERP 底表结构与相互关系,能较好实现这一个性需求,并有助于深入了解Oracle ERP和进一步补充完善ARP系统.
以上脚本均测试通过并在中国科学院力学所、地理所、植物所等正式应用.
11 参考文献
1. Oracle中国有限公司
2. 中国科学院ARP网站,
3. ORACLE 总账管理11i ,Oracle公司白皮书
4. Oracle ERP Technical Reference Manual,Oracle公司
其它:参考附件
课题经费查询
经费查询详细