EBS客户化标准及多组织应用.docx

二月 14, 2014AIMMD070 Programmer TrainingEBS客户化标准及多组织应用 Author:梁贤院Creation Date:October 16, 2011Last Updated:二月 14, 2014Document Ref: Version:V1.0Note: 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.Approvals: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.Document ControlChange Record10DateAuthorVersionChange Reference16-Oct-11SandyLiang1.0No Previous DocumentReviewersNamePositionDistributionCopy No.NameLocation1234Note: 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.Note To Holders:If you receive an electronic copy of this document and print it out, please write your name on the equivalent of the cover page, for document control purposes.If you receive a hard copy of this document, please write your name on the front cover, for document control purposes.ContentsDocument Controlii环境ivEBS 环境iv本地环境iv新建客户化应用v客户化应用EBS配置v客户化UNIX配置vii客户化应用数据库配置viii客户化多组织应用10MO配置文件10客户化应用多组织注册10客户化表多组织注册11OAF中多组织应用12请求中多组织应用14表单中多组织应用15多组织应用常用SQL16VPD技术的继续探讨17Open and Closed Issues for this Deliverable19Open Issues19Closed Issues19环境EBS 环境R12本地环境新建客户化应用客户化应用EBS配置系统管理员 应用产品 注册Vanke CustomXVKXVK_TOP系统管理员- 安全性 Oracle 注册XVKXVK启用1Vanke Custom Application User系统管理员- 安全性 Oracle DataGroup标准Vanke CustomAPPSMultiple Reporting CurrenciesVanke CustomXVK客户化UNIX配置cd $APPL_TOPmkdir xvkcd xvkmkdir 12.0.0cd 12.0.0mkdir adminmkdir binmkdir formsmkdir graphsmkdir helpmkdir htmlmkdir installmkdir importmkdir jarmkdir javamkdir libmkdir logmkdir mdsmkdir mediamkdir mesgmkdir outmkdir patchmkdir reportsmkdir resourcemkdir sqlcd formsmkdir USmkdir ZHScd .cd reportsmkdir USmkdir ZHS在ENV 文件中 增加环境变量 XVK_TOP=/app/applprod/prodappl/xvk/12.0.0export XVK_TOP增加CONTEXT_FILE 配置echo $CONTEXT_FILE1.打开$CONTEXT_FILE（即/ebs12/VIS/inst/apps/VIS_erp/appl/admin/VIS_erp.xml）找到GL_TOP子句，在它后面加上/ebs12/VIS/apps/apps_st/appl/xvk/12.0.02.到$ADMIN_SCRIPTS_HOME（即apache脚本运行目录：/ebs12/VIS/inst/apps/VIS_erp/admin/scripts），重新配置adautocfg.sh客户化应用数据库配置建立数据库文件(数据文件大小根据业务增加)CREATE TABLESPACE XVK_DATA DATAFILE /u01/orauat/uatdata/XVK_DATA.dbf SIZE 4096M;CREATE TABLESPACE XVK_IDX DATAFILE /u01/orauat/uatdata/XVK_IDX.dbf SIZE 1024M;创建数据库用户CREATE USER XVK IDENTIFIED BY XVK DEFAULT TABLESPACE XVK_DATA TEMPORARY TABLESPACE TEMP PROFILE DEFAULT QUOTA UNLIMITED ON XVK_DATA QUOTA UNLIMITED ON XVK_IDX QUOTA UNLIMITED ON APPS_TS_ARCHIVE QUOTA UNLIMITED ON APPS_TS_INTERFACE QUOTA UNLIMITED ON APPS_TS_MEDIA QUOTA UNLIMITED ON APPS_TS_NOLOGGING QUOTA UNLIMITED ON APPS_TS_QUEUES QUOTA UNLIMITED ON APPS_TS_SEED QUOTA UNLIMITED ON APPS_TS_SUMMARY QUOTA UNLIMITED ON APPS_TS_TX_DATA QUOTA UNLIMITED ON APPS_TS_TX_IDX; GRANT EXECUTE ON AD_DDL TO XVK;GRANT ALTER ANY OUTLINE TO XVK;GRANT ALTER SESSION TO XVK;GRANT ANALYZE ANY TO XVK;GRANT CREATE ANY OUTLINE TO XVK;GRANT CREATE CLUSTER TO XVK;GRANT CREATE DATABASE LINK TO XVK;GRANT CREATE SEQUENCE TO XVK;GRANT CREATE SESSION TO XVK;GRANT CREATE SNAPSHOT TO XVK;GRANT CREATE TABLE TO XVK;GRANT CREATE ANY INDEX TO XVK;GRANT CREATE TRIGGER TO XVK;GRANT CREATE TYPE TO XVK;GRANT DROP ANY OUTLINE TO XVK;GRANT QUERY REWRITE TO XVK;Open and Closed Issues for this Deliverable EBS客户化标准及多组织应用 File Ref: 31fc76663ea91a8aca2f34dfddf8195a.pdf (V1.0 )Company Confidential - For internal use only客户化多组织应用MO配置文件MO：业务实体MO：安全性配置文件MO：默认业务实体客户化应用多组织注册BEGINfnd_mo_product_init_pkg.register_application(XVK, SYSADMIN, Y);END;客户化表多组织注册创建一个ALL表CREATE TABLE XVK.XVK_PA_CHG_MEMOS_ALL( MEMO_ID NUMBER not null, SEGMENT1 VARCHAR2(300), PROJECT_ID NUMBER, CHANGE_ORDER_TYPE VARCHAR2(20), TYPE1 VARCHAR2(30), TYPE2 VARCHAR2(30)TABLESPACE XVK_DATA/创建主索引,注意tablespaceALTER TABLE XVK. XVK_PA_CHG_MEMOS_ALL ADD CONSTRAINT XVK_PA_CHG_MEMOS_PK PRIMARY KEY (MEMO_ID) USING INDEX TABLESPACE XVK_IDX/CREATE UNIQUE INDEX XVK.XVK_PA_CHG_MEMOS_U1 ON XVK.XVK_PA_CHG_MEMOS_ALL (SEGMENT1) TABLESPACE XVK_IDX/创建同义词CREATE OR REPLACE SYNONYM APPS.XVK_PA_CHG_MEMOS FOR XVK.XVK_PA_CHG_MEMOS_ALL/CREATE OR REPLACE SYNONYM APPS.XVK_PA_CHG_MEMOS_ALL FOR XVK.XVK_PA_CHG_MEMOS_ALL/注册多组织安全性DECLARE - Boolean parameters are translated from/to integers: - 0/1/null false/true/null update_check BOOLEAN := _to_bool(0); enable_flag BOOLEAN := _to_bool(1); static_policy BOOLEAN := _to_bool(0); long_predicate BOOLEAN := _to_bool(0);BEGIN - Call the procedure sys.dbms_rls.add_policy(object_schema = APPS ,object_name = XVK_PA_CHG_MEMOS ,policy_name = XVK_PA_CHG_MEMOS_POLICY ,function_schema = APPS ,policy_function = MO_GLOBAL.ORG_SECURITY ,statement_types = SELECT ,update_check = update_check ,enable = enable_flag ,static_policy = static_policy ,policy_type = NULL ,long_predicate = long_predicate ,sec_relevant_cols = NULL ,sec_relevant_cols_opt = NULL);END;/取消注册dbms_rls.drop_policy(object_schema = APPS, object_name = XVK_PA_CHG_MEMOS, policy_name = XVK_PA_CHG_MEMOS_POLICY);OAF中多组织应用在创建的application module 增加客户化属性 MULTIORG_ENABLED或者在AM afterConnect写下事件super.afterConnect();OADBTransaction oaDbTrans = getOADBTransaction();oaDbTrans.setMultiOrgPolicyContext(S, orgId);如果是多组织设置成M请求中多组织应用UPDATE fnd_concurrent_programs t SET t.multi_org_category = S WHERE t.concurrent_program_name = XVK_PROJECT_COST_RPT;如果是多组织设置成M或者用系统管理员-系统管理-并发-程序表单中多组织应用在WHEN-NEW-FORM-INSTANCE里面调用npo_common_public_pkg.mo_init(CPO);下面是程序方法.PROCEDURE mo_init(p_short_name IN VARCHAR2 DEFAULT NULL) IS l_short_name VARCHAR2(30); l_default_org_id NUMBER; l_default_ou_name VARCHAR2(240); l_ou_count NUMBER; l_count NUMBER; BEGIN IF p_short_name IS NULL THEN IF fnd_global.resp_appl_id != -1 THEN SELECT fap.application_short_name INTO l_short_name FROM fnd_application fap WHERE fap.application_id = fnd_global.resp_appl_id; END IF; ELSE l_short_name := p_short_name; END IF; SELECT COUNT(1) INTO l_count FROM fnd_mo_product_init t WHERE t.application_short_name = l_short_name AND status = Y; IF l_count 0 THEN mo_global.init(l_short_name); mo_utils.get_default_ou(l_default_org_id, l_default_ou_name, l_ou_count); IF l_default_org_id IS NOT NULL THEN mo_global.set_policy_context(S, l_default_org_id); END IF; ELSE mo_global.set_policy_context(M, NULL); END IF; END mo_init;多组织应用常用SQL模拟登录BEGIN fnd_global.apps_initialize(138097,52325, 275); MO_GLOBAL.init(PA);END;138097:USER_ID 当前用户ID52325:RESP_ID 当前职责ID275:RESP_APPL_ID 当前职责应用ID模拟登录后查询当前能访问的组织SELECT * FROM mo_glob_org_access_tmp查询某个对象的建立的策略SELECT * FROM all_policies t WHERE t.OBJECT_NAME=AP_CHECKS;查询某个对象的策略结果SELECT * FROM V$VPD_POLICY t WHERE t.OBJECT_NAME=AP_CHECKS;VPD技术的继续探讨如某些数据需要按照部门屏蔽1. 我们可以EBS设计思路 设计1个配置文件.部门读权限2. 建立2个VPD表小表,并维护CREATE TABLE CUST_GENERAL.NOM_SALE_VPD_ALL( SO_VPD_ID NUMBER NOT NULL, LAST_UPDATE_DATE DATE NOT NULL, LAST_UPDATED_BY NUMBER(15) NOT NULL, CREATION_DATE DATE NOT NULL, CREATED_BY NUMBER(15) NOT NULL, LAST_UPDATE_LOGIN NUMBER(15) , DISPLAY_NAME VARCHAR2(60) NOT NULL)/CREATE TABLE CUST_GENERAL.NOM_SALE_VPD_LINE_ALL( SO_VPD_ID NUMBER NOT NULL, FLEX_VALUE VARCHAR2(30) NOT NULL, LAST_UPDATE_DATE DATE NOT NULL, LAST_UPDATED_BY NUMBER(15) NOT NULL, CREATION_DATE DATE NOT NULL, CREATED_BY NUMBER(15) NOT NULL, LAST_UPDATE_LOGIN NUMBER(15) , LINE_NUM NUMBER NOT NULL)/3. 在配置文件里面选择值SO_VPD_ID4. 注册policyBEGIN dbms_rls.add_policy(object_schema = APPS ,object_name = OE_ORDER_HEADERS ,policy_name = NOE_ORDER_HEADERS_POLICY ,function_schema = APPS ,policy_function = NOM_SALE_SECURITY_PKG.READ_SECURITY ,statement_types = SELECT);END;5. 写一个FUNTION.并启用ATTRIBUTE15作为安全性屏蔽字段 FUNCTION read_security ( owner VARCHAR2 ,objname VARCHAR2 ) RETURN VARCHAR2 IS l_dept_code VARCHAR2(30); BEGIN l_dept_code := fnd_profile.VALUE(XY_CUX_DEPARTMENT_READ); IF l_dept_code IS NULL THEN RETURN NUL