1 --submit the request 2 l_req_id_fee := fnd_request.submit_request('PO', 3 'REQIMPORT', 4 '', 5 '', 6 FALSE, 7 'POP_REQUEST', 8 to_char(SYSDATE, 'YYYYMMDD'), 9 'ALL', 10 '', 11 'N', 12 'N'); 13 14 if (l_req_id_fee is null) or (l_req_id_fee = 0) then 15 raise l_submit_req; 16 else 17 --提交请求 18 COMMIT; 19 --更新状态(只有请求成功,状态才更改) 20 UPDATE hek_om_pop_headers_all 21 SET flow_status = 'reg_purchase' 22 --select * from hek_om_pop_headers_all 23 WHERE header_id IN (SELECT h.header_id 24 FROM hek_om_pop_headers_v h 25 WHERE h.flow_status = 'wait'); 26 COMMIT; 27 end if;
1、首先在FORM中引入 FNDCONC.pll
2、调用请求,并等待请求完成,附代码如下:PROCEDURE BTN_CLICK(p_btn_name varchar2) isX_REQ_ID NUMBER; L_PHASE_CODE VARCHAR2(1); L_STATUS_CODE VARCHAR2(1); START_DATE DATE; CUR_DATE DATE; DUMMY VARCHAR2(10); P_USER_ID VARCHAR2(30) :=FND_PROFILE.VALUE('USER_ID'); l_result boolean;BEGINl_result := fnd_request.add_layout( template_appl_name => 'CUX', template_code => 'CUXGLCASHFLOWS', template_language => null, template_territory => null, output_format => 'PDF' ); X_REQ_ID := fnd_request.submit_request( 'CUX' ,'CUXGLCASHFLOWS' ,'' ,'' ,FALSE ,'8000' ,'2001' ,'2009-06' ,'CNY' ,chr(0) ,'','','','','','','','','','','','','','', '', '', '', '', '', '', '', '', '','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','',''); IF X_REQ_ID = 0 THEN FND_MESSAGE.set_string('请求提交失败'); FND_MESSAGE.ERROR; ELSE COMMIT; FND_MESSAGE.SET_STRING('请求已提交,请等待'); FND_MESSAGE.show; END IF; SELECT SYSDATE INTO START_DATE FROM DUAL; DBMS_LOCK.SLEEP(7); LOOP SELECT A.PHASE_CODE, A.STATUS_CODE, SYSDATE INTO L_PHASE_CODE, L_STATUS_CODE, CUR_DATE FROM FND_CONC_REQ_SUMMARY_V A WHERE A.REQUEST_ID = X_REQ_ID; EXIT WHEN (L_PHASE_CODE = 'C' AND L_STATUS_CODE = 'C') OR (CUR_DATE - START_DATE)>= 0.002400; DBMS_LOCK.SLEEP(7); END LOOP; EDITOR_PKG.REPORT(X_REQ_ID, 'Y');END BTN_CLICK;
3.fnd_request.submit_request介紹
fnd_request.submit_request的用法:FND_REQUEST.SUBMIT_REQUEST 函数是用来提交一个请求的,它返回一个NUMBER值.具体调用如下 :result := fnd_request.submit_request(application CHAR, --AP模快 program CHAR, --应用程序 description CHAR, --请求说明(可选) start_time CHAR, --RUN 时间(可选) sub_request BOOLEAN, --立刻提交请求 argument1 CHAR, --参数1 argument2 CHAR, --参数2 argument3 CHAR, --参数3 argument4 CHAR, --参数4 argument5 CHAR, --参数5....... argument100 CHAR); 英文说明(zt oracle) : Parameters are as follows:
application - Short name of the application associated with the concurrent request to be submitted.
program - Short name of the concurrent program (not the executable) for which the request should be submitted.
description - Description of the request that is displayed in the Concurrent Requests form (Optional.)
start_time - Time at which the request should start running, formatted as HH24: MI or HH24:MI:SS (Optional.)
sub_request - Set to TRUE if the request is submitted from another request and should be treated as a sub-request.
argument1...100 - Arguments for the concurrent request; up to 100 arguments are permitted. If submitted from Oracle Forms, you must specify all 100 arguments.
补充说明:在用fnd_request.submit_request的时候,第五个参数用false,不要被参数名称误导;这个函数有105个参数,前面五个定义请求本身,后面100个是传递给请求的具体参数,都是Char类型,我们需要转换,默认值是chr(0),代表这个参数不用传递给调用的请求;在Package里面调用只需要传递需要的参数个数,因为它有默认值指示结束;在form里面则不行,要写满105个,而且我们参数结束之后要用一个chr(0)来表示结束
例fnd_request.submit_request('AR','SVAINEX_P','','', FALSE,:parameter.invoice_store,chr(0),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
2、Oracle Erp等待报表运行机制
主要是用到了Fnd_concurrent.wait_for_ruqest这个function.Fnd_concurrent.wait_for_request返回Boolean值,主要参数如下:function FND_CONCURRENT.WAIT_FOR_REQUEST(request_id IN number default NULL, --请求IDinterval IN number default 60, --检查时间间隔max_wait IN number default 0, --最大等待时间phase OUT varchar2, status OUT varchar2, dev_phase OUT varchar2, --请求运行阶段dev_status OUT varchar2, --各个阶段状态message OUT varchar2 --运行完成后输出信息) return boolean;dev_phase有Pending,Running,Complete,Inactive等几种,每种对应不同的Dev-Status,比如Complete阶段后就有Normal,Error,Warning,Cancelled,Terminated等几种状态。
例如:
1l_request_status := Fnd_Concurrent.Wait_For_Request(l_request_id, 25, 30, 4l_phase, 5l_status, 6l_dev_phase, 7l_dev_status, 8l_message); 9 10IF l_request_status THEN 11IF l_dev_status = 'NORMAL' THEN 12NULL; 13ELSE 14Fnd_Message.Debug('请求运行不成功:'||l_dev_status); 15RETURN; 16END IF; 17ELSE 18Fnd_Message.Debug('请求未完成,无法查看报表内容!'); 19RETURN; 20END IF; 21 22Editor_Pkg.Report(l_request_id,'Y');
总结:FND_REQUEST.SUBMIT_REQUEST是一种通过后台方式提交请教的方法,可以在pkg和form中使用,在form中使用要将参数写全。 FND_CONCURRENT.WAIT_FOR_REQUEST是一个等待当前请求运行完毕的程序,可以利用这个等待当前的请求程序运行完毕再运行下面的程序。
有时候提交请求一直会返回0的情况:
检查是否有初始化环境:
SELECT user_id
INTO l_num_user_id FROM applsys.fnd_user WHERE user_name ='user_name';SELECT responsibility_id INTO l_num_resp_id FROM apps.fnd_responsibility_vl WHERE responsibility_name ='responsibility_name';SELECT application_id INTO l_num_resp_appl_id FROM applsys.fnd_application WHERE application_short_name = 'app_short_name';fnd_global.apps_initialize (user_id => l_num_user_id, resp_id => l_num_resp_id, resp_appl_id => l_num_resp_appl_id );