EBS12.2.4 SLA导入导出.docx

汉得融晶金融行业最佳实践ebs r12.2.4 sla导入导出note: title, subject, last updated date, reference number, and version are marked by a word bookmark so that they can be easily reproduced in the header and footer of documents. when you change any of these values, be careful not to accidentally delete the bookmark. you can make bookmarks visible by selecting tools-optionsview and checking the bookmarks option in the show region.作者:扈建峰创建日期:2015-05-06最后更新:2015-05-06控制码: 版本: 1.0 审批签字:客户项目经理融晶项目经理note: to add additional approval lines, press tab from the last cell in the table above. copy number_note: you can delete any elements of this cover page that you do not need for your document. for example, copy number is only required if this is a controlled document and you need to track each copy that you distribute.文档控制修改记录3日期作者版本修改参考2015-05-06扈建峰1.0扈建峰初始版本审阅姓名职位分发拷贝姓名地点1234note: the copy numbers referenced above should be written into the copy number space on the cover of each distributed copy. if the document is not controlled, you can delete this table, the note to holders, and the copy number label from the cover page.目录文档控制ii汉得融晶金融行业最佳实践4综述4关于xla_subledgers5导出xla_subledgers5导入xla_subledgers602.关于xla_aad7导出xla_aad7导入xla_aad7遇到的问题9open and closed issues for this deliverable13open issues13closed issues13note: to update the table of contents, put the cursor anywhere in the table and press f9. to change the number of levels displayed, select the menu option insertindex and tables, make sure the table of contents tab is active, and change the number of levels to a new value.文档控制 3file ref: 80d39dae936756cf4a38f4e5dcdd7ede.pdf (v 1.0 )company confidential - for internal use only 汉得融晶金融行业最佳实践综述起因在有些银行ebs 大总账项目中，存在多个模块需要配置sla。同时，也存在多个环境需要对sla进行复制（重新配置）。为减轻功能顾问的工作量，避免因为手动配置导致的sla错误，我们需要用sla的fndload导入导出，在各个环境上做迁移。致谢首先感谢胡伟山老师提供的脚本，让我能在看过脚本之后，才具备了在r12.2.4环境上做实验的能力。其次感谢喻根平同事，为我指点迷津，理清思考问题的方向。ps：站在巨人的肩膀上做事，飞一样的感觉，超帅！关于xla_subledgers借鉴山哥提供的脚本。xla_subledgers的导入导出比较简单，直接命令行即可，下面会简单介绍。导出xla_subledgersxlaemseed.lct download注意：不知是否是版本缘故，r12.2.4中的xlaemseed.lct脚本是有错误的，里面有三条select语句的order by列，是table中没有的。解决办法：可以是手动改掉这三个语句然后覆盖掉原文件，也可以去下载patch（p20531252_r12.xla.c_r12_generic）并安装（感谢任军老师提供此patch）。错误的select语句如下：-1.select app.application_short_name, js.je_source_key, xla.valuation_method_flag, xla.drilldown_procedure_name, xla.security_function_name, xla.application_type_code, xla.alc_enabled_flag, xla.control_account_type_code, xla.control_account_enabled_flag, xla.default_party_type_code, xla.control_account_source_code, fnd_load_util.owner_name(xla.last_updated_by) owner, to_char(xla.last_update_date, yyyy/mm/dd) last_update_date from xla_subledgers xla, fnd_application app, gl_je_sources js where app.application_id = xla.application_id and js.je_source_name = xla.je_source_name and xla.application_id = :application_id order by /*xla.application_short_name*/ 1;-2.select accounting_attribute_code, app.application_short_name source_app_short_name, source_type_code, source_code, default_flag, assignment_owner_code, fnd_load_util.owner_name(xec.last_updated_by) owner, to_char(xec.last_update_date, yyyy/mm/dd) last_update_date from xla_evt_class_acct_attrs xec, fnd_application app where app.application_id = xec.source_application_id and xec.application_id = :application_id and xec.event_class_code = :event_class_code order by xec.accounting_attribute_code, /*xec.source_app_short_name,*/ xec.source_type_code, xec.source_code;- 3.select app.application_short_name source_app_short_name, xla.source_type_code, xla.source_code, xla.active_flag, xla.level_code, fnd_load_util.owner_name(xla.last_updated_by) owner, to_char(xla.last_update_date, yyyy/mm/dd) last_update_date from xla_event_sources xla, fnd_application app where app.application_id(+) = xla.source_application_id and xla.application_id = :application_id and xla.entity_code = :entity_code and xla.event_class_code = :event_class_code order by /*xla.source_app_short_name,*/ xla.source_type_code, xla.source_code然后，执行下面语句生成download命令：select fndload apps/apps 0 y download $xla_top/patch/115/import/xlaemseed.lct | fa.application_short_name |_subledgers.ldt xla_subledgers application_id= | fa.application_id | ; from fnd_application fa where fa.application_short_name like btl%;这条语句生成类似如下样式的命令，设置好语言和字符集参数，在终端的合适路径下，命令行执行，获取ldt文件。nls_lang=american_america.utf8; export nls_lang;fndload apps/apps 0 y download $xla_top/patch/115/import/xlaemseed.lct btl_ei_subledgers.ldt xla_subledgers application_id=20023;导入xla_subledgers xlaemseed.lct uploadfndload apps/apps 0 y upload $xla_top/patch/115/import/xlaemseed.lct btl_dd_subledgers.ldt。或标准化的安装工具install.pl02.关于xla_aad借鉴山哥提供的脚本。xla_aad的导入比较复杂，需要提交请求。导出xla_aadxlaaadrule.lct download生成download命令：select fndload apps/apps 0 y download $xla_top/patch/115/import/xlaaadrule.lct | fa.application_short_name | _aad.ldt xla_aad application_id= | fa.application_id | amb_context_code=default; from fnd_application fa where fa.application_short_name like btl%;生成的样式如下，设置字符集语言参数并执行，获取ldt文件：nls_lang=american_america.utf8; export nls_lang;fndload apps/apps 0 y download $xla_top/patch/115/import/xlaaadrule.lct btl_ad_aad.ldt xla_aad application_id=20025 amb_context_code=default;导入xla_aad不能直接xlaaadrule.lct uploadfndload apps/apps 0 y upload $xla_top/patch/115/import/xlaaadrule.lct btl_dd_aad.ldt。这样是不行的。xla_aad的导入，需要提交请求。请求脚本（样例）样例如下：declare l_amb_context_code varchar2(30) := default; l_request_id number; l_child_phase varchar2(100); l_child_status varchar2(100); l_dev_phase varchar2(100); l_dev_status varchar2(100); l_message varchar2(1000); l_wait_req boolean; cursor c_a is select a.application_id, a.application_short_name, a.application_name, /home/appltestu/installhjf/install/ | a.application_short_name | _aad.ldt file_name from fnd_application_vl a where a.application_short_name like btl% and a.application_short_name not in (btl_fc);begin fnd_global.apps_initialize(user_id = 1115, resp_id = 50717, resp_appl_id = 20003); for rec in c_a loop dbms_output.put_line(import | rec.application_name | 导入应用产品会计定义！); l_request_id := fnd_request.submit_request(xla, xlaalimp, -导入应用产品会计定义 , to_char(sysdate, yyyy/mm/dd hh24:mi:ss), false, rec.application_id, -application l_amb_context_code, -amb context rec.file_name, -source file pathname n, -merge analysis null, -batch name y, -dependency check 1 overwrite, -import option n, -validate y, -dependency check 2 y -force overwrite ); dbms_output.put_line(rec.application_name | 导入应用产品会计定义请求id: | l_request_id); commit; l_wait_req := fnd_concurrent.wait_for_request(request_id = l_request_id, interval = 2, max_wait = 0, phase = l_child_phase, status = l_child_status, dev_phase = l_dev_phase, dev_status = l_dev_status, message = l_message); if l_dev_phase = complete and l_dev_status = normal then dbms_output.put_line(rec.application_name | 导入应用产品会计定义请求id: | l_request_id | 正常完成); -更新验证状态 update xla_product_rules_b prb set pile_status_code = n where duct_rule_type_code = c and prb.application_id = rec.application_id and prb.amb_context_code = l_amb_context_code; commit; dbms_output.put_line(rec.application_name | 验证应用产品会计定义); l_request_id := fnd_request.submit_request(xla, xlaabacr, -验证应用产品会计定义 , to_char(sysdate, yyyy/mm/dd hh24:mi:ss), false, null, -ledger n, -exclude valid definitions rec.application_id, -application y, -dummy1 null, -aad null, -aad owner n, -report only mode null, -event class null, -event type null, -journal lines definition owner null -journal lines definition ); dbms_output.put_line(rec.application_name | 验证应用产品会计定义请求id: | l_request_id); commit; elsif l_dev_phase = complete and l_dev_status = warning then dbms_output.put_line(rec.application_name | 导入应用产品会计定义请求id: | l_request_id | 警告！); else dbms_output.put_line(rec.application_name | 导入应用产品会计定义请求id: | l_request_id | 错误！); end if; end loop;end;注意：上面代码里，提交请求时的参数们，各种y/n，有几个还是蛮重要的，最好不要改掉。比方说，xlaalimp请求中的merge analysis参数，这个参数对应在程序中是l_p_merge_analysis_only_flag，如果把它改成y，则不能实现覆盖的效果，也就是说，重新导入sla的话，不能覆盖曾经导入过的数据。遇到的问题错误描述正常来讲，上面那段脚本要提交三个请求，分别是如下图所示：先是导入请求，导入请求中调用了加载请求。最后调用验证请求，验证sla。然而，我在导入的时候，总是遇到下面的错误：如图所示，总是提示我科目组合不存在。这种坑爹错误，我刚开始并不认为是日志里所说的这样，因为科目组合不存在，系统可以动态创建的呀。反复调试查找，发现如下图所示的情况，才会有这种错误：而output type全是segment的推导规则，就不会报错：解决方案我翻开那个导入请求的代码看了看里面调用的api，根据报错消息，找到了这一段代码，发现这里的fnd_flex_ext.get_combination_id函数调用是有问题的，总是返回false。其实我