日志

关于从基于Mult-Org的视图中查询数据的问题

热度 2已有 1833 次阅读2008/3/25 22:00

记得之前一次面视考官问过这样一个问题，在Report中如何获得基于Muti-Org的视图中的数据？

答：begin

fnd_global.APPS_Initialize( user_id=>3070,

resp_id=>50650,

resp_appl_id =>20003);

end;

考官：可以实现，但不是Oracle推荐的方法

答：在定义Cocurrent Program时定义参数P_ORG_ID，设定初始值为fnd_profile.value(‘ORG_ID’), 在报表的BeforeReport Trigger中加入下面的代码：

fnd_client_info.set_org_context(to_char(x_org_id));

考官：可以实现，但不是Oracle推荐的方法，正确的方法应当是使用用户出口函数

问：是不是像测试报表性能时那样，

在BeforeReport Trigger加入：SRW.USER_EXIT('FND SRWINIT');

在AfterReport Trigger加入：SRW.USER_EXIT('FND SRWEXIT');

报表中还要添加一个Number类型的用户参数P_CONC_REQUEST_ID

考官：正是。

注：测试报表性能时还要将Cocurrent Program定义中的Enable Trace打勾

随后总结了下，要查询得到基于Muti-Org的视图中的数据，一般有以下几种方法：

1．

begin

dbms_application_info.set_client_info(:org_id);-

end;

2．

Declare

x_org_id number;

begin

Fnd_profile.GET('ORG_ID',x_org_id);

fnd_client_info.set_org_context(x_org_id);

end;

3．

begin

fnd_client_info.setup.client_info(application_id => 1,

responsibility_id => 2,

user_id in number => 3,

security_group_id => 4);

end;

begin

fnd_global.APPS_Initialize( user_id=>3070,

resp_id=>50650,

resp_appl_id =>20003);

end;

其原理都是直接或间接的获取OU的ORG_ID后设置环境环境变量，我一般用2，4最多

重点参考Package fnd_global，Fnd_profile，可以得到获取许多其他profile或者环境变量的方法，比如：

fnd_profile.value('GL_SET_OF_BKS_ID') from dual

fnd_profile.value('SO_ORGANIZATION_ID') from dual;

fnd_profile.value('ORG_ID');

fnd_profile.value('MFG_ORGANIZATION_ID')

FND_PROFILE.VALUE('USER_ID');

FND_PROFILE.VALUE('login_ID');

FND_PROFILE.VALUE('USERNAME');

FND_PROFILE.VALUE('CONCURRENT_REQUEST_ID');

FND_PROFILE.VALUE('RESP_APPL_ID');

FND_PROFILE.VALUE('APPL_SHRT_NAME');

我的理解是对应于Form. $PROFILE$块的字段：

FND_GLOBAL.USER_ID

FND_Global.User_Name

以下是User Guide中对这些变量的说明

变量	说明
USERNAME	Your user’s current Oracle Application Object Library username.
USER_ID	Your user’s current Oracle Application Object Library user ID.
RESP_ID	Your user’s current responsibility ID.
APPL_SHRT_NAME	The short name of the application connected to your user’s current responsibility.
RESP_APPL_ID	The application ID of the application connected to your user’s current responsibility.
FORM_NAME	The name of the current form. Not available for concurrent programs.
FORM_ID	The form. ID of the current form. Not available for concurrent programs.
FORM_APPL_NAME	The name of the application for which the current form. is registered. Not available for concurrent programs.
FORM_APPL_ID	The application ID of the application for which the current form. is registered. Not available for concurrent programs.
LOGON_DATE	Your user’s logon date for the current session.
LAST_LOGON_DATE	Your user’s logon date for the previous session.
LOGIN_ID	Your user’s Sign–On Audit login ID in Oracle Application Object Library.
CONC_REQUEST_ID	instance of your running current program. You can only use this profile option in a concurrent program. You use this profile option to fill the REQUEST_ID Who column.
CONC_PROGRAM_ID	The program ID associated with a running current program. You can only use this profile option in a concurrent program. You use this profile option to fill the PROGRAM_ID Who column.
CONC_PROGRAM_APPLICATION_ID	The application ID associated with a running current program. You can only use this profile option in a concurrent program. You use this profile option to fill the PROGRAM_APPLICATION_ID Who column.
CONC_LOGIN_ID	The login ID associated with a running concurrent program. You can only use this profile option in a concurrent program. You can use this profile option to fill the LAST_UPDATE_LOGIN Who column.
CONC_PRINT_OUTPUT	The value Yes or No that you enter in the Print Output field when you register a concurrentprogram. You can use the routine afpoput() fromyour concurrent programs to change the value ofthis profile option for a particular instance of yourrunning concurrent program. This profile optiondetermines whether the concurrent managers printthe concurrent program’s output to the printer.
CONC_PRINT_STYLE	The print style. of your concurrent program’soutput that you enter in the Print Style. field whenyou register a concurrent program. You can usethe routine afpoput() from your concurrentprograms to change the value of this profile option

以上内容部分参考：

http://hi.baidu.com/yunfanleo/blog/item/a2fb70c298154e36e4dd3b42.html

另外还要说明的是开发中经常需要获取的mfg_org_id，即库存组织Id

可以用fnd_profile.value('MFG_ORGANIZATION_ID')来获得，但是未必能得到，因为打开某些模块的Form时系统并不会给给变量赋值，直到执行Change Oranization，选择一个库存组织后才能取到。

Form中通常采用的办法是在PRE-FORM或WHEN-NEW-FORM-INSANCE trigger中使用：fnd_org.choose_org ；并添加4个用户参数chart_of_account_id(number), org_name(char), org_code(char), org_id(number).

下表是一些和OU，库存组织相关的表和视图

表或视图	说明
HR_OPERATING_UNITS	OU信息
ORG_ORGANIZATION _DEFINITIONS	库存组织信息
OE_SYS_PARAMETERS_V	库存主组织,PARAMETER_CODE = MASTER_ORGANIZATION_ID

收藏邀请使用道具 | 编辑| 删除|

发表评论评论 (1 个评论)

回复 uplar 2012/3/2 10:55: 太好了，正缺少这种经验。谢谢！

的个人空间 https://www.erp100.com/?0 [收藏] [复制] [RSS]

日志

关于从基于Mult-Org的视图中查询数据的问题

发表评论 评论 (1 个评论)

发表评论评论 (1 个评论)