HAND-深入浅出Oracle-EBS之Excel报表开发

OracleERP最佳技术实践E-BUSINESSSUITEORACLE核心应用技术Excel报表开发Author:黄建华Jianhua.HuangMSN:huajhua@CreationDate:March14,2007LastUpdated:November11,2010DocumentRef:<DocumentReferenceNumber>Version:DRAFT1Aals<Approver1><Approver2>CopyNumber_____OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,2010 3DateAuthorVersionChangeReference14-Mar-07Jianhua.HuangDraft1aNoPreviousDocumentNameositionCopyNo.NameLocation1LibraryMasterProjectLibrary2ProjectManager34Ifyoureceiveanelectroniccopyofthisdocumentandprintitout,pleasewriteyournameontheequivalentofthecoverpage,fordocumentcontrolpurposes.Ifyoureceiveahardcopyofthisdocument,pleasewriteyournameonthefrontcover,fordocumentcontrolpurposes.Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-ForinternaluseonlyDocumentControliiOracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,2010nts 1.OverviewandKeyPoints 2 4.1.安装并设置Excel 25 5.1 316.OpenandClosedIssuesforthisDeliverable 32s ClosedIssues 32Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-ForinternaluseonlyDocumentControliiiOracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,2010Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly1.OverviewandKeyPoints1.1.读者要求1.2.开发背景1.1.1.最小要求1.1.2.期望水平1.2.1.来自Excel的压力微软的Excel是如此的深入人心，不管是谁，凡是使用电脑办公或和IT沾边，都会使用，有些用户“玩转”起Excel，用出神入化来形容也决不为过。意拷贝、筛选、排序、引用、统计、公式、数据透视表、图表、可录制的宏、惊人的函数库、样式、条件格式……这些诱人的功能使用起来是如此的简XMLPublisher)、Discoverer，用户都希望能够方便的放到Excel中，自己做二次加然而，从报表结果到Excel，都需要Click很多次，而且新数据要和先前的二次加工结合少还得拷贝几下。不方便哪！Oracle的努力比如AIM文档、BIPublisherDesktop都是基于Word的模版和VBA，早期的ADIClient也是基于Excel及其VBA。OracleERP最佳技术实践1.3.设计思路DocRef:<DocumentReferenceNumber>November11,2010而现在和将来，Oracle将致力于发展WebADI，其将充分发挥Excel的本地加工能力，确保数据和用户的二次分析有机结合，并把这种结合保存下来下次直接使用，这在EBS12版中已经实现！方便哪！基于Excel做些开发了。1.3.1.总体设想在做Excel报表开发时，是基于如下一些基本思想：elOracle最终采用VBAADO连接，使用Windows自带的MicrosoftODBCforOracle驱动，因护量。DeveloperDiscovererWorkflow以。ProviderforOracle，性能要好点。1.3.3.参数与性能考虑户过滤出想要的数据，这个毋庸置疑；然而其对于性能从而对ewOracleWebADIDiscoverer能问题。因为View可能是经过汇总函数如Sum、集合操作如Union等出来的，优化器过滤条件“渗透”到内层基表，也就是，对用户来说，数据是过关有个显而易见的方法可以解决：在ADO不使用View，而是采用完整的Select语句，在为了统一开发模式，看来只能在ADO中使用简单的View了，那么该如何避免性能问题Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-ForinternaluseonlyOverviewandKeyPoints3of34OracleERP最佳技术实践DocRef:<DocumentReferenceOracleERP最佳技术实践November11,2010为此我们可采用另外一个方法：创建了一个公共的参数表cux_rpt_parameters，有15个Varchar2的字段和一个session_id、user_id字段，该表仅有一条session_id为session_id=userenv('SESSIONID')过滤；用户查询数据前，将参数连同当前的userenv('SESSIONID')插入到参数表，查询后Rollback。记录(注：查询时会有2条)，又保证了多用户并发时不会产生锁等待。1.3.4.多组织问题OracleEBS中的数据是分组织的，一个用户不应当看到其它组织比如其他工厂的数们的应对方案很简单：定义用户时为其指定允许查询的组织范围，这样在创建View时就可以根据这个用户过滤组织数据了。1.3.5.数据放在什么位置Excel是多Sheet的，用户可能也希望将不同日期的数据放在不同的Sheet中；每个报表都有基本的标题、一些超过“标准”的警示(如小于5单元格用红色填充)、一些可能cel式等。1.3.6.简单的权限控制模型可能大规模使用，所以采取“用户-报表”的模型直接控制运行哪些报表，中间不采用诸如职责的组；这二者及其关系我们可以在表里直接维护，也可在ERP中开发简单的Form来统一维护；而用户权限的验证、密码的修改、密码安全性的实施，可以通过一个Packagecux_rpt_utils来集中完成。VBA运行报表前，将用户名/密码/报表名称/版本传递给cux_rpt_utils.validate_login，获得验证结果。如果通过，则进一步从数据库获得报表对应的View。控制，采取明确排除的方式，定义了一个表来保存哪些用户不能看Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-ForinternaluseonlyOverviewandKeyPoints4of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,OracleERP最佳技术实践1.3.7.数据库方面的权限考虑个最小权限的用户如CUXRPT比较合适；同时也要考虑视图更新等的维护便利性。然后把Package的执行权限授给CUXRPT。查询权限授给CUXRPT；报表稳定后，也可收回表权限，在APPS下直接创建View然后授权给CUXRPT。1.3.8.运行日志行日志，这样可以获得哪些用户在哪些时间用了哪些报表发生1.3.9.开发模版和客户化思路并非所有报表，其列数都一样，都需要运行额外的宏(初始化日期参数、生成数据透等)，所以也要求我们设计一个开发模版，并留出客户化的函数调用，这样整个Excel报表代码分为两部分：。1.3.10.尚待改进的地方麻烦。Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-ForinternaluseonlyOverviewandKeyPoints5of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,20102.开发框架及其安装2.1.开发框架2.1.1.表：用户定义用户保存在表CUX_RPT_USERS中，主要字段如下：ColumnNameTypeDescriptionUSER_IDNUMBERNUSER_NAMEVARCHAR2(30)NENCRYPT_PASSWORDVARCHAR2(4000)NLAST_ENCRYPT_PASSWORDVARCHAR2(4000)N码LAST_PASSWORD_DATEateNSYSDATE的时间EMAIL_ADDRESSVARCHAR2(240)YEMAIL地址DESCRIPTIONVARCHAR2(240)YEFFECTIVE_DATEDATENSYSDATEDISABLE_DATEDATEY2.1.2.表：用户的组织用户的组织保存在表CUX_RPT_USER_ORG中，主要字段如下：ColumnNameTypeDescriptionUSER_ORG_IDNUMBERNUSER_IDNUMBERN用户内部ID，和组织ID共同构成唯一性ORGANIZATION_IDNUMBERN组织ID，和用户D共同构成唯一性，从hr_all_organization_units_vl取数DESCRIPTIONVARCHAR2(240)YEFFECTIVE_DATEDATENSYSDATEDISABLE_DATEDATEY2.1.3.表：报表定义ColumnNameTypeDescriptionREPORT_IDNUMBERNREPORT_CODEVARCHAR2(30)N唯一性DESCRIPTIONVARCHAR2(240)YEFFECTIVE_DATEDATENSYSDATEDISABLE_DATEDATEY2.1.4.表：报表版本报表保存在表CUX_RPT_REPORT_VERSIONS中，主要字段如下：ColumnNameTypeDescriptionVERSION_IDNUMBERNREPORT_IDNUMBERNIDSQL_TEXTVARCHAR2(4000)N般是：视图名称，大写Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly开发框架及其安装6of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,2010ColumnNameTypeDescriptionVERSION_NUMBERVARCHAR2(30)N'1.0'版本和这个不一致，运行时将警告EFFECTIVE_DATEDATENSYSDATEDISABLE_DATEDATEYDESCRIPTIONVARCHAR2(240)Y2.1.5.表：用户-报表关系用户和报表的关系保存在表CUX_RPT_USER_REPORTS中，主要字段如下：ColumnNameTypeDescriptionUSER_REPORT_IDNUMBERNREPORT_IDNUMBERN报表内部ID，和USER_ID共同构成唯一性USER_IDNUMBERN用户内部ID，和REPORT_ID共同构成唯一性DESCRIPTIONVARCHAR2(240)YEFFECTIVE_DATEDATENSYSDATEDISABLE_DATEDATEY2.1.6.表：字段权限字段权限保存在表CUX_RPT_COLUMN_PRIV中，主要字段如下：ColumnNameTypeDescriptionCOLUMN_PRIV_IDNUMBERNUSER_REPORT_IDNUMBERN用户报表关系内部ID，和COLUMN_NAME构成唯一COLUMN_NAMEVARCHAR2(30)N视图名称，从all_tab_columns取数PRIVILEGEVARCHAR2(1)NN是否有权限，默认NDESCRIPTIONVARCHAR2(240)YEFFECTIVE_DATEDATENSYSDATEDISABLE_DATEDATEY7.Form：用户和报表维护用户和报表的维护可直接在表里进行，也可通过FormCUXRPTFND，该Form有两个Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly开发框架及其安装7of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,20102.1.8.表：公共参数用户运行时的参数保存在CUX_RPT_PARAMETERS中，主要字段如下：ColumnNameTypeDescriptionPARAMETER1VARCHAR2(240)Y参数1PARAMETER2VARCHAR2(240)Y……VARCHAR2(240)YPARAMETER15VARCHAR2(240)Y参数15USER_IDNUMBERYSESSION_IDVARCHAR2(40)NID2.1.9.表：使用日志用户使用报表的日志保存在CUX_RPT_ACCESS中，主要字段如下：ColumnNameTypeDescriptionACCESS_IDNUMBERNLOCAL_HOST_NAMEVARCHAR2(100)YLOCAL_LOGIN_USERVARCHAR2(100)Y统地用户名LOCAL_WINDOWS_VERSTIONVARCHAR2(100)Y客户端Windows版本LOCAL_OFFICE_VERSIONVARCHAR2(100)YOfficeLOCAL_ORGANIZATION_NAMEVARCHAR2(100)YOffice组织名称LOCAL_USER_NAMEVARCHAR2(100)YOffice用户名称LOCAL_PATHVARCHAR2(500)Y路径LOCAL_FILE_NAMEVARCHAR2(500)YREPORT_IDNUMBERY内部IDACCESS_DATEDATEYSYSDATEUSER_IDNUMBERYIDMESSAGESVARCHAR2(4000)Y可能是错误消息2.1.10.View：用户用户表含有密码，所以不能直接授权给其他DB用户，需要构造一个视图后再授权：CREATEORREPLACEVIEWCUX.CUX_RPT_USERS_VASSELECTuser_id,useruser_name,emailemail_address,descriptiondescription,effectiveeffective_date,Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly开发框架及其安装8of34OracleERP最佳技术实践disabledisable_date FROMcux.cux_rpt_users;2.1.11.View：参数DocRef:<DocumentReferenceNumber>November11,2010可以构造一个综合的参数视图，供各个具体的报表使CREATEORREPLACEVIEWCUX.CUX_RPT_PARAMETERS_VASSELECTDISTINCTanization_id,parapara.parameter1,parapara.parameter2,parapara.parameter3,parapara.parameter4,parapara.parameter5,parapara.parameter6,parapara.parameter7,parapara.parameter8,parapara.parameter9,parapara.parameter10,parapara.parameter11,parapara.parameter12,parapara.parameter13,parapara.parameter14,parapara.parameter15 FROMcux.cux_rpt_usersusr,cux.cux_rpt_user_orguo,cux.cux_rpt_parameterspara WHEREusr.user_id=para.user_id ANDusruseriduo.user_id ANDpara.session_id=userenv('SESSIONID');2.1.12.Package：参数处理和权限控制参数处理和权限控制集中在包cux_rpt_utils中，过程和函数说明如下：逻辑：删除session_id=userenv('SESSIONID')。返回：Y/成功、N/失败、W/警告。Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly开发框架及其安装9of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,OracleERP最佳技术实践具有运行指定报表的权限，如Package的变量l_message中、把各种ID和值缓存到对应的l_xxxxxx变量中。返回：Y/成功、N/失败。用户名和旧密码，如果通过再验证新密码是否符合密码安全性要求，如长度必须大于6、不能和最后一次密码相同等等；把错误信息放入Package的变量l_message中。返回：直接返回l_message。如用户ID、报表SQL等。返回：直接返回l_xxxxxx。2.1.13.Function：取得组织列表和字段权限列表CREATEORREPLACEFUNCTIONget_rpt_user_org(p_user_idINNUMBER)RETURNVARCHAR2IS l_resultVARCHAR2(4000);BEGIN FORa_recIN(SELECTanization_codeFROMcux.cux_rpt_user_orguo,_organization_definitionsorgWHEREWHEREanization_id=anization_idANDANDuo.effective_date<=trunc(SYSDATE)ANDnvl(uo.disable_date,trunc(SYSDATE))>=trunc(SYSDATE)ANDANDuo.user_id=p_user_idORDERORDERBY1)LOOP l_result:=l_result||','||a_anization_code;ENDLOOP; l_result:=substr(l_result,2);RETURNl_result;Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly开发框架及其安装10of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,2010END;CREATEORREPLACEFUNCTIONget_rpt_column_priv(p_user_idINNUMBER,p_report_idINNUMBER)RETURNRETURNVARCHAR2IS l_resultVARCHAR2(4000);BEGIN FORa_recIN(SELECTDISTINCTcp.column_nameFROMcux.cux_rpt_user_reportsur,cux.cux_rpt_column_privcpWHEREWHEREur.user_id=p_user_idANDANDur.report_id=p_report_idANDANDcp.effective_date<=trunc(SYSDATE)ANDnvl(cp.disable_date,trunc(SYSDATE))>=trunc(SYSDATE)ANDANDcp.privilege='N'ORDERORDERBY1)LOOP l_result:=l_result||','||a_rec.column_name;ENDLOOP; l_result:=substr(l_result,2);RETURNl_result;END;2.1.14.Sheet：参数和运行页用户输入的参数不通过VBA中的表单来处理，而是直接用一个“Parameter”Sheet来eetUSER_NAME样用户运行报表时不需要每次都输入用户名USER_DESCRIPTION用户对应的描述从表中取到这里AUTO_FRESHD5打开Excel时是否自动重新取数IS_SEQ_NEEDED行前面加行号，需要手工增删Data模版第一列DATA_BEGIND7显示RESULT_SHEETPARAMETER1参数1，不同报表含义可能不一样PARAMETER2D10……PARAMETER10D18参数10，不同报表含义可能不一样，目前最多启用10个和“运行报表”这两个功能按钮也放在这个页面中。具体请看如Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly开发框架及其安装11of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,20102.1.15.Sheet：结果页2.1.16.Sheet：Help页者放置帮助内容之类的。Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly开发框架及其安装12of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>NovemberOracleERP最佳技术实践Sheet定Sheet、添加指定ettils1、Cux_Auto_Open：打开Exel时运行yuxAfterQuery具体代码和注释请直接打开VBA查看。用户入口点“修改密码”、“运行报表”就是分别调用这里面的Change_Password和Query_Data。d“修改密码”的代码比较简单，逻辑如下：包cux_rpt_utils.change_password，修改密码ta“运行报表”的代码比较复杂，逻辑如下：Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly开发框架及其安装13of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,20102.2.开发模版介绍2.2.1.模版功能latexls1、取出指定日期范围内的库存事务处理，View是CUX_RPT_TEMPLATE_VVCREATEORREPLACEVIEWCUX_RPT_TEMPLATE_VVASWITHparaAS(SELECTanization_id, trunc(to_date(nvl(para.parameter1,to_char(SYSDATE,'YYYY-MM-DD')),'YYYY-MM-DD'))date_begin, trunc(to_date(nvl(para.parameter2,to_char(SYSDATE,'YYYY-MM-DD')),'YYYY-MM-DD'))+0.99999date_end FROMcux_rpt_parameters_vpara--测试用数据，因为直接在PL/SQL中运行时cux_rpt_parameters_v没有数据UNIONALLSELECT82,trunc(to_date('2007-03-29','YYYY-MM-DD'))date_begin,trunc(to_date('2007-03-29','YYYY-MM-DD'))+0.99999date_endFROMdual WHEREcux_rpt_utils.get_user_id=0)Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly开发框架及其安装14of34OracleERP最佳技术实践SELECTanization_code,anization_name,mmt.subinventory_code,mst.segment1item_code,trunc(mmt.transaction_date)transaction_date,mmt.transaction_quantity,mmtmmt.transaction_uom FROMinv.mtl_material_transactionsmmt,inv.mtl_system_items_bmst,_organization_definitionsorg,parapara WHEREanization_id=anization_id ANDmmt.inventory_item_id=mst.inventory_item_id ANDanization_id=anization_id ANDanization_id=anization_id ANDmmt.transaction_dateBETWEENpara.date_beginANDpara.date_end ANDmmt.subinventory_codeLIKE'ZCP%' ANDmst.segment1LIKE'100E%'DocRef:<DocumentReferenceNumber>November11,2010Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly开发框架及其安装15of34Excel报表开发FileRef:深入浅出OracleExcel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-ForinternaluseonlyOracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,2010具体内容请直接看Template.xls。OptionExplicit'打开Exel时运行SubCux_Auto_Open() '1、初始化参数，如设置日期为系统日期 '[parameter1].Value=DateEndEndSubSubCux_Init() ''GTDEVCUXRPT connection_url="DRIVER={MicrosoftODBCforOracle};UID=CUXRPT;PWD=handdev;SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=9)(PORT=1590))(CONNECT_DATA=(SID=GTDEV)));"''GTPRODOracleOracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,2010 connection_url="DRIVER={MicrosoftODBCforOracle};UID=CUXRPT;PWD=k8n3vd2p9;SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=3)(PORT=1620))(CONNECT_DATA=(SID=GTPROD)));" report_code="Excel报表模版"'报表名称，不同报表需要修改 version_number="1.0"'版本号EndEndSub证条件FunctionCux_Pre_Query()AsBoolean Cux_Pre_Query=False '这里可对参数做些验证，不通过则Cux_Pre_Query=False即可，一般不用改 IfLen(Trim([parameter1].Value))=0Then MsgBox"开始日期不能为空",,"信息",Err.HelpFile,Err.HelpContextCuxCux_Pre_Query=FalseExitExitFunctionEndEndIf IfLen(Trim([parameter2].Value))=0Then MsgBox"结束日期不能为空",,"信息",Err.HelpFile,Err.HelpContextCuxCux_Pre_Query=FalseExitExitFunctionEndEndIf If[parameter2].Value<[parameter1].ValueThen MsgBox"结束日期不能小于开始日期",,"信息",Err.HelpFile,Err.HelpContextCuxCux_Pre_Query=FalseExitExitFunctionEndEndIfCuxCux_Pre_Query=TrueEndEndFunction透视图SubCux_After_Query()Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly开发框架及其安装17of34OracleERP最佳技术实践 Dima_sheetAsWorksheetDocRef:<DocumentReferenceNumber>November11,2010OnOnErrorGoToErrorHandler Delete_All_Result_Sheet Seta_sheet=ActiveWorkbook.Sheets.Add a_sheet.Name="PivotSheet" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,SourceData:="Data!A1:G36000").CreatePivotTableTableDestination:=Sheets("PivotSheet").Range("A1"),TableName:="数据透视表" ActiveSheet.PivotTableWizardTableDestination:=ActiveSheet.Cells(3,1) ActiveSheet.Cells(3,1).Select ActiveSheet.PivotTables("数据透视表").SmallGrid=False WithActiveSheet.PivotTables("数据透视表").PivotFields("组织")..Orientation=xlPageField..Position=1EndEndWith WithActiveSheet.PivotTables("数据透视表").PivotFields("日期")..Orientation=xlRowField..Position=1EndEndWith WithActiveSheet.PivotTables("数据透视表").PivotFields("子库")..Orientation=xlColumnField..Position=1EndEndWith WithActiveSheet.PivotTables("数据透视表").PivotFields("数量")..Orientation=xlDataField.Caption="Sumof数量"..Function=xlSumEndEndWithChartsCharts.AddActiveChartActiveChart.Name="PivotChart"Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly开发框架及其安装18of34Excel报表开发FileRef:深入浅出OracleExcel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-ForinternaluseonlyOracleERP最佳技术实践 ActiveChart.SetSourceDataSource:=Sheets("PivotSheet").Range("A3")ActiveChartActiveChart.LocationWhere:=xlLocationAsNewSheetExitExitSub改ErrorHandlerErrorHandler: Application.StatusBar=""AddAdd_Msg_Stack Show_Err_Msg_Stack Log_LoginGet_Msg_StackClearClear_Msg_StackEndEndSubSubCux_Post_Query()EndEndSub2.3.框架安装2.3.1.创建数据库对象DocRef:<DocumentReferenceNumber>November11,2010CUX_RPT_OBJECTS_INS.sqlCUX_RPT_UTILS.pckCUXRPTFND.fmbOracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,20103.基于模版的开发步骤3.1.开发步骤本节详细介绍从Template.xls开始开发“分析工具使用日志.xls”的过程。3.1.1.设计报表内容和参数1、报表字段，共11个称、使用用户、用户描述、所属工厂、使用时间、本地路径3.1.2.创建View，如有参数需引用公共参数表CREATEORREPLACEVIEWCUX_RPT_ACCESS_VVASWITHparaAS(SELECTDISTINCT--anization_id, trunc(to_date(nvl(para.parameter1,to_char(SYSDATE,'YYYY-MM-DD')),'YYYY-MM-DD'))date_begin, trunc(to_date(nvl(para.parameter2,to_char(SYSDATE,'YYYY-MM-DD')),'YYYY-MM-DD'))+0.99999date_end FROMcux_rpt_parameters_vpara--测试用数据，因为直接在PL/SQL中运行时cux_rpt_parameters_v没有数据UNIONALLSELECT--82,trunc(to_date('2007-03-29','YYYY-MM-DD'))date_begin,trunc(to_date('2007-06-29','YYYY-MM-DD'))+0.99999date_endFROMdual WHEREcux_rpt_utils.get_user_id=0)SELECTrpt.report_code,usrusr.user_name,usrusr.description,get_rpt_user_org(usr.user_id)rganization_name,cracra.access_date,cracra.local_path,cracra.messages,Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly基于模版的开发步骤20of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,2010cracra.local_file_name,cracra.local_host_name,cra.local_windows_verstion,cra.local_office_version,cracra.local_user_name,cracra.version_number,FROMcux.cux_rpt_report_versionsvWHEREv.report_id=rpt.report_id)cur_version_number FROMcux.cux_rpt_accesscra,cux.cux_rpt_reportsrpt,apps.cux_rpt_users_vusr,para WHEREcra.report_id=rpt.report_id ANDcra.user_id=usr.user_id ANDcra.access_dateBETWEENpara.date_beginANDpara.date_end ORDERBYcra.access_dateDESC3.1.3.注册报表，并授权报表给用户拷贝“Template.xls”为“分析工具使用日志.xls”。约定，参数1就是“开始日期”，参数2就是“结束日期”，这样和写View时候的引用Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly基于模版的开发步骤21of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,20103.1.8.修改Cux_Auto_Open，初始化日期参数SubCux_Auto_Open() '1、初始化参数，如设置日期为系统日期Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly基于模版的开发步骤22of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,2010 [parameter1].Value=DateAdd("M",-1,Date) [parameter2].Value=DateEndEndSub3.1.9.修改Cux_Init，指定报表字段数和名称，连接串则视情况修改：…3.1.10.修改Cux_After_Query，编写透视图、公式等宏代码“PivotSheet”两个Sheet，再删除或注释掉Cux_After_Query所有代码。3.1.11.运行报表就可以运行出结果：3.2.报表实例3.1.12.进一步设计3.2.1.原料库原料收发存日报结果如下：Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly基于模版的开发步骤23of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,2010其中利用Excel功能实现了如下部分：3.2.2.原材料库存明细表其中利用Excel功能实现了如下部分：另外，小计行本身是在View计算，使用UnionAll和明细数据合并在一起的。Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly基于模版的开发步骤24of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,20104.客户端安装过程4.1.安装并设置Excel4.1.1.安装Excel4.1.2.设置安全级别菜单：工具/宏/安全性，选择“中”：4.1.3.设置用户菜单：工具/选项/常规，将用户名改为实际的用户名：Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly客户端安装过程25of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,2010OracleERP最佳技术实践4.2.1.适用环境eracle4.3.1.适用环境.3.2.安装步骤1：安装Oracle客户端双击运行X:\Workflow\setup.vbs。该安装文件自动设置Path环境变量、自动导入注册表项目、自动注册ActiveX控件。Excel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly客户端安装过程26of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>November11,2010OracleERP最佳技术实践动注册标准Scripting.FileSystemObject对象后的提示。4.3.3.安装步骤2：重启4.4.1.适用环境1：安装Oracle客户端任何客户端都可以，这里以安装Workflow为例，下同。双击运行X:\wfclient2635\setup.exeExcel报表开发FileRef:深入浅出OracleEBS之Excel报表开发.docx(v.DRAFT1A)CompanyConfidential-Forinternaluseonly客户端安装过程27of34OracleERP最佳技术实践DocRef:<DocumentReferenceNumber>NovemberOracleERP最佳技术实践首先安装文件将自动解压(如果失败，可能是虚拟内存设的不够的原因)，然后弹出可以保持默认值，也可以更改到其他路径；假定这里仍是D:\OraHome_2。选择下一Excel报表开发FileRef:深入浅出OracleEBS之E