EBS并发请求.docx

1、EBS并发请求并发请求相关总结模拟登录BEGIN fnd_global.apps_initialize(user_id = 3189, resp_id = 20634, resp_appl_id = 401); mo_global.set_policy_context(S, 81);END;1 并发请求定义定义可执行路径：应用开发员=并发=可执行定义并发程序路径：应用开发员=并发=程序查找可执行与程序定义脚本SELECT ptl.user_concurrent_program_name 程序名, p.concurrent_program_name 程序简称, etl.user_executab

2、le_name 可执行, e.executable_name 可执行简称, e.execution_file_name 执行文件名, decode(e.execution_method_code, P, Oracle Reports, I, PL/SQL 存储过程, K, Java 并发程序, 其他) 执行方法 FROM apps.fnd_concurrent_programs p, apps.fnd_concurrent_programs_tl ptl, apps.fnd_executables e, apps.fnd_executables_tl etl WHERE ptl.concurr

3、ent_program_id = p.concurrent_program_id AND p.application_id = ptl.application_id AND ptl.language = ZHS AND p.executable_id = e.executable_id AND e.application_id = etl.application_id AND e.executable_id = etl.executable_id AND etl.language = ZHS AND e.executable_name LIKE CUXAPEST% -可执行-AND p.con

4、current_program_name LIKE SZAEAMAST% -可执行-AND ptl.user_concurrent_program_name LIKE %资产台账% ORDER BY e.executable_name;查找参数定义定义参数路径：应用开发员=并发=程序-参数=值集：必输，限定参数可以输入的值的范围。=默认类型：非必输，如果不为空，必须输入默认值，具体见3.2。=必须：如果勾选，那么该参数必须输入，才能提交并发请求。=范围：如果不为空，必须上限和下限成对出现，FROM为下限，TO为上限。此时TO不能大于FROM,否则不允许提交并发请求，并提示：请选择低于高值的低值

5、。=显示：默认勾选。如果不勾选，那么提交请求的用户看不到该参数，这种情况一般会对其赋默认值。查找并发程序参数SELECT t.descriptive_flexfield_name 程序名, b.column_seq_num 序号, b.end_user_column_name 参数, t.form_left_prompt 提示, fvs.flex_value_set_name 值集, b.default_value 默认值, fvs.description 说明, decode(b.default_type, S, SQL 语句, A, 段, C, 常数, D, 当前日期, T, 当前时间,

6、P, 配置文件, , , 其他) 默认类型 FROM apps.fnd_descr_flex_col_usage_tl t, apps.fnd_descr_flex_column_usages b, apps.fnd_flex_value_sets fvs WHERE b.application_id = t.application_id AND b.descriptive_flexfield_name = t.descriptive_flexfield_name AND b.descriptive_flex_context_code = t.descriptive_flex_context_

7、code AND b.application_column_name = t.application_column_name AND fvs.flex_value_set_id = b.flex_value_set_id AND t.language = ZHS AND t.descriptive_flexfield_name LIKE $SRS$.% and t.form_left_prompt like %部门% ORDER BY b.column_seq_num;并发请求放入请求组路径：系统管理员=安全性=责任=请求查找请求分配信息SELECT g.request_group_name

8、请求组, t.user_concurrent_program_name 报表, p.concurrent_program_name 可执行, rt.responsibility_name 职责 FROM fnd_request_group_units u, fnd_concurrent_programs_tl t, fnd_concurrent_programs p, fnd_request_groups g, fnd_responsibility_vl r, apps.fnd_responsibility_tl rt WHERE u.request_unit_id = t.concurren

9、t_program_id AND g.request_group_id = u.request_group_id AND t.concurrent_program_id = p.concurrent_program_id AND r.request_group_id = u.request_group_id AND rt.responsibility_id = r.responsibility_id AND t.language = ZHS -中文环境 AND rt.language = ZHS -中文环境 -AND UPPER(G.REQUEST_GROUP_NAME) = UPPER(ST

10、D AR User Programs)-根据请求组查找 AND upper(t.user_concurrent_program_name) LIKE %CUX:工单在制成本统计表% -根据报表名查找-AND p.concurrent_program_name LIKE %ZZGLGDL029C% -根据程序名查找-AND rt.responsibility_name LIKE %QD1% -根据职责查找;2 并发请求参数2.1 参数的值集类型值集验证类型：表定义注意验证表信息界面上的值，含义，标识的作用。提交请求时输入参数如下：最后传入并发请求的是【标识】值不可以为空。如果含义为空，则(2)为

11、空白如果标识为空，则默认标识=值。值集验证类型：无例1：标准日期类型FND_STANDARD_DATE这种类型的值集，限定的值直接通过格式类型来确定。此处值格式类型为标准日期，并不代表传入参数的值是日期类型，事实上，传入的值的类型依然是字符类型。并发程序参数类型必须为VARCHAR2。 PROCEDURE main(errbuf OUT VARCHAR2, retcode OUT NUMBER, p_inv_org_id IN NUMBER, p_date_to IN VARCHAR2, p_closed_include IN VARCHAR2, p_job_status IN VARCHAR

12、2, p_job_class_type IN VARCHAR2, p_job_class IN VARCHAR2, p_wip_entity_from IN VARCHAR2, p_wip_entity_to IN VARCHAR2, p_item_id_from IN NUMBER, p_item_id_to IN NUMBER);并发程序定义默认值为SQL语句：select fnd_date.date_to_displaydate(sysdate) from sys.dual提交请求时输入正确日期格式注意前台输入格式与首选项设置的日期格式一致不一致则报错。传入并发请求的格式(后台存储格式)

13、p_date_to := 2013/02/17 00:00:00并发请求程序中调用该参数时需要做将字符型转换为日期型l_date_to := fnd_conc_date.string_to_date(p_date_to);例2：标准日期时间类型如果有这种需求，即希望起始日期是输入日期的第一秒，终止日期是输入日期的最后一秒，即p_date_from :=2011/11/24 00:00:00p_date_to :=2011/11/24 23:59:59可以用值集值集类型为无的标准日期时间类型FND_STANDARD_DATETIME参数默认值为p_date_from : SELECT FND_D

14、ATE.date_to_displaydt(TRUNC(SYSDATE) FROM DUALp_date_to: SELECT FND_DATE.date_to_displaydt(TRUNC(SYSDATE+1)-(1/(60*60*24) FROM DUAL最终传入的日期值并发请求程序中调用该参数时需要做将字符型转换为日期型l_date_from := fnd_conc_date.string_to_date(p_date_from);l_date_to := fnd_conc_date.string_to_date(p_date_to);值集验证类型：对定义参数说明值集值集类型P_PAG

15、EBREAK_SEG_NUM分页段GL_SRS_PAGEBREAK_SEGS 表P_LOW_PB_SEG 分页段下限GL_SRS_PAGEBREAK_RANGES对P_HIGH_PB_SEG分页段上限GL_SRS_PAGEBREAK_RANGES对GL_SRS_COA_UNVALIDATED无值集名称：GL_SRS_PAGEBREAK_SEGS值集验证类型：表查询语句：SELECT segment_name, s.description, segment_num FROM fnd_id_flex_segments_vl s, fnd_segment_attribute_values v WHE

16、RE s.id_flex_code = GL# AND v.id_flex_code = GL# AND s.id_flex_num = :$flex$.gl_srs_coa_unvalidated AND v.id_flex_num = :$flex$.gl_srs_coa_unvalidated AND s.enabled_flag = Y AND s.application_column_name = v.application_column_name AND s.application_id = 101 AND v.application_id = 101 AND v.segment_

17、attribute_type = GL_ACCOUNT AND v.attribute_value = N ORDER BY segment_num值集名称：GL_SRS_PAGEBREAK_RANGES值集类型：对FND POPIDR APPL_SHORT_NAME=SQLGL CODE=GL# DISPLAY=:$FLEX$.GL_SRS_PAGEBREAK_SEGS NUM=:$FLEX$.GL_SRS_COA_UNVALIDATEDREQUIRED=Y VALIDATE=NONE SEG=:!VALUE DESC=:!MEANING NAVIGATE=!DIRFND VALIDR AP

18、PL_SHORT_NAME=SQLGL CODE=GL# DISPLAY=:$FLEX$.GL_SRS_PAGEBREAK_SEGS NUM=:$FLEX$.GL_SRS_COA_UNVALIDATEDVALIDATE=NONE REQUIRED=Y DESC =:!MEANING SEG=:!VALUE 值集名称：GL_SRS_COA_UNVALIDATED效果2.2 并发请求参数默认值我们在为并发程序定义参数的时候，有时候需要为其定义默认值，默认值的类型有P,配置文件S,SQL语句C,常数D,当前日期T,当前时间A,段留空不管是那一种默认类型，我们在输入默认值时取值逻辑时（比如一个SQL语

19、句或者一个常数），获得的值，必然是包含于所选值集对应的【值】中，而不是【标识】中。默认类型为SQL语句参数为p_organization_id值集为PO_SRS_ORGANIZATIONS默认类型为SQL 语句默认值为SQL语句SELECT OOD.ORGANIZATION_CODE FROM MTL_PARAMETERS OOD WHERE OOD.ORGANIZATION_ID = :$PROFILES$.MFG_ORGANIZATION_ID我们可以看到，并发程序参数需要被赋予的值是organization_id,但是默认值我们获得的是ORGANIZATION_CODE，我们去看下值集P

20、O_SRS_ORGANIZATIONS的定义，因此默认值时取得的值，必然是包含于所选值集对应的【值】中，而不是【标识】中。默认类型为常数默认类型为配置文件配置文件定义（PO：MFG 组织标识）默认类型为段作用：当输入【期间自】参数后，自动带出【期间至】参数。默认类型为当前日期默认类型为当前时间参数间的依赖关系SELECT T.CUST_TRX_TYPE_ID, T.NAME, T.DESCRIPTION FROM RA_CUST_TRX_TYPES_ALL T WHERE ORG_ID = :$FLEX$.CUX_AR_CURRENT_OU AND SET_OF_BOOKS_ID = :$FL

21、EX$.CUX_AR_BOOKSET2;常用参数的标准值集及其默认值1）通用日期时间相关ParameterValue SetDefault Type/ValuePromptCommentP_TRANSACTION_DATE_FROMFND_STANDARD_DATETIMESELECT FND_DATE.date_to_displaydt(TRUNC(SYSDATE) FROM DUALXX日期自P_TRANSACTION_DATE_TOFND_STANDARD_DATETIMESELECT FND_DATE.date_to_displaydt(TRUNC(SYSDATE+1)-(1/(60*

22、60*24) FROM DUALXX日期至p_start_dateFND_STANDARD_DATEselect fnd_date.date_to_displaydate(sysdate) from sys.dual起始日期select fnd_date.date_to_displaydate(effective_date) from fnd_sessions where session_id = userenv(sessionid) - Convert the varchar2 date input to a date datatype l_start_date := fnd_conc_da

23、te.string_to_date(p_start _date) ; l_end_date := fnd_conc_date.string_to_date(p_end_date) ;数字相关ParameterValue SetDefault Type/ValuePromptCommentP_QTY_PRECISIONINV_SRS_PRECISIONREPORT_QUANTITY_PRECISION数量精确度选项帐套p_set_of_book_id默认值 配置文件 GL_SET_OF_BKS_ID2）库存模块ParameterValue SetDefault Type/ValuePromptC

24、ommentP_ORGANIZATION_IDINV_SRS_ORG_ACCESSSELECT organization_code FROM mtl_parameters WHERE organization_id = fnd_profile.value(MFG_ORGANIZATION_ID)库存组织ID999 其他相关-1-查看请求的XML输出-2-设置并发程序不兼容效果，当程序A正在运行时，与其不兼容的程序B只能等待A完成后才能运行-3-请求提交后-查找请求信息SELECT t.request_id 请求ID, pletion_text 完成状态, t.actual_start_date

25、 请求开始日期, t.actual_completion_date 请求完成日期, frtl.responsibility_name 职责, pro.user_concurrent_program_name 报表名, pros.concurrent_program_name 可执行名, t.request_date 请求时间, t.outfile_name 输出文件路径, fu.user_name 请求提交人, t.oracle_session_id -v$session FROM apps.fnd_concurrent_requests t, apps.fnd_responsibility_

26、tl frtl, apps.fnd_concurrent_programs_tl pro, apps.fnd_concurrent_programs pros, apps.fnd_user fu WHERE t.responsibility_id = frtl.responsibility_id AND t.concurrent_program_id = pro.concurrent_program_id AND pro.concurrent_program_id = pros.concurrent_program_id AND fu.user_id = t.requested_by AND

27、pro.language = ZHS AND frtl.language = ZHS -AND t.request_id = 1983022 -请求编号 AND pro.user_concurrent_program_name LIKE %CUX:工单在制成本统计表% -请求名称-AND pros.concurrent_program_name = ZZAPGML010C-可执行名称;-4-提交请求后-查找请求参数信息-找参数值SELECT t.request_id, t.argument1, t.argument2, t.argument3, t.argument4, t.argument5

28、, t.argument6, t.argument7, t.argument8, t.argument9, t.argument10, t.argument11, t.argument12, t.argument13, t.argument14, t.argument15, t.argument16, t.argument17, t.argument18, t.argument19, t.argument20, t.argument21, t.argument22, t.argument23, t.argument24, t.argument25 FROM fnd_concurrent_req

29、uests t, fnd_concurrent_programs p, fnd_responsibility_tl r WHERE t.concurrent_program_id = p.concurrent_program_id AND t.responsibility_id = r.responsibility_id AND r.language = ZHS -AND P.CONCURRENT_PROGRAM_NAME LIKE %ZZARRZZ104C% -AND T.ARGUMENT13 = YES -AND R.RESPONSIBILITY_NAME LIKE %WX1% -AND t.request_date SYSDATE - 1 AND t.request_id = 20538818;-5-批量取消并发请求fnd_concurr