1、FORM中调用请求
示例：
要完成这个目的，首先要在ebs中注册报表，并注册模板。
然后再方法里先调用FND_REQUEST.ADD_LAYOUT添加模板，然后调用FND_REQUEST.SUBMIT_REQUEST提交并发请求，再调用FND_CONCURRENT.WAIT_FOR_REQUEST等待请求完成并返回分析，状态和完成信息。
procedure print_report(order_number in NUMBER,date_from in date,date_to in date) is
L_REQUEST_ID NUMBER;
L_BL_RESULT BOOLEAN;
v_layout BOOLEAN;
X_PHASE VARCHAR2(100);
X_STATUS VARCHAR2(100);
X_DEV_PHASE VARCHAR2(100);
X_DEV_STATUS VARCHAR2(100);
X_MESSAGE VARCHAR2(100);
BEGIN
/*
--
-- Name
-- add_layout
-- Purpose
-- Called before submission to add layout options for request output.
--
-- Arguments
-- Template_APPL_Name - Template Application Short name.
-- Template_code - Template code
-- Template_Language - Template File language (iso value)
-- Template_Territory - Template File Territory (iso value)
-- Output Format - Output Format
--添加输出模板，FND_REQUEST.add_layout (template_appl_name in varchar2,
template_code in varchar2,
template_language in varchar2,
template_territory in varchar2,
output_format in varchar2) return boolean
*/
v_layout:=FND_REQUEST.ADD_LAYOUT('PO',
'TRNORDER094',
Null,
Null,
'PDF');
/*
--
-- Name
-- submit_request
-- Purpose
-- Submits concurrent request to be processed by a concurrent manager
--
-- Arguments
-- application - Short name of application under which the program
-- - is registered
-- program - concurrent program name for which the request has
-- - to be submitted
-- description - Optional. Will be displayed along with user
-- - concurrent program name
-- start_time - Optional. Time at which the request has to start
-- - running
-- sub_request - Optional. Set to TRUE if the request is submitted
-- - from another running request and has to be treated
-- - as a sub request. Default is FALSE
-- argument1..100 - Optional. Arguments for the concurrent request
-- 提交并发请求
--FND_REQUEST.SUBMIT_REQUEST(application IN varchar2 default NULL,
program IN varchar2 default NULL,
description IN varchar2 default NULL,
start_time IN varchar2 default NULL,
sub_request IN boolean default FALSE
argument1,
argument2, ..., argument99,
argument100) return number;
*/
L_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST('PO',
'TRNORDER094',
'',
'',
FALSE,
order_number,
date_from,
date_to,
CHR(0),'','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','');
COMMIT;
IF L_REQUEST_ID IS NULL OR L_REQUEST_ID = 0 THEN
RETURN;
END IF;
/*
--
-- Name
-- WAIT_FOR_REQUEST
-- Purpose
-- Waits for the request completion, returns phase/status and
-- completion text to the caller. Calls sleep between db checks.
-- Arguments (input)
-- request_id - Request ID to wait on
-- interval - time b/w checks. Number of seconds to sleep
-- - (default 60 seconds)
-- max_wait - Max amount of time to wait (in seconds)
-- - for request's completion
-- Arguments (output)
-- User version of phase and status
-- Developer version of phase and status
-- Completion text if any
-- phase - Request phase ( from meaning in fnd_lookups )
-- status - Request status( for display purposes )
-- dev_phase - Request phase as a constant string so that it
-- - can be used for comparisons )
-- dev_status - Request status as a constatnt string
-- message - Completion message if request has completed
--
--等待并发请求完成，并返回分析/状态和完成信息，FND_CONCURRENT.WAIT_FOR_REQUEST(request_id IN number default NULL,
interval 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;
*/
L_BL_RESULT := FND_CONCURRENT.WAIT_FOR_REQUEST(L_REQUEST_ID,
1,
0,
X_PHASE,
X_STATUS,
X_DEV_PHASE,
X_DEV_STATUS,
X_MESSAGE);
IF X_DEV_PHASE = 'COMPLETE' AND X_DEV_STATUS = 'NORMAL' THEN
editor_pkg.report(L_REQUEST_ID,'Y'); --使输出的报表pdf文件在浏览器中显示。
ELSE
fnd_file.put_line(1,RPAD('x_phase',20,' ') || '=' || X_PHASE);
fnd_file.put_line(1,RPAD('x_status=',20,' ') || '=' || X_STATUS);
fnd_file.put_line(1,RPAD('x_dev_phase=', 20, ' ') || '=' || X_DEV_PHASE);
fnd_file.put_line(1,RPAD('x_dev_status=',20,' ') || '=' || X_DEV_STATUS);
fnd_file.put_line(1,RPAD('x_message=',20,' ') || '=' || X_MESSAGE);
fnd_file.put_line(1,'Warning : Starting journal print report failure! It did not running in 120 seconds, its request_id is ' ||
TO_CHAR(L_REQUEST_ID) || ' please check it.');
END IF;
end print_report;
2、FORM中调用请求参数设置
--- 提交处理事务处理接口请求
l_request_id := FND_REQUEST.SUBMIT_REQUEST(
APPLICATION => 'INV',
PROGRAM => 'INCTCM'
);
IF ( l_request_id = 0 ) THEN
RAISE E_SUBMIT_FAILED;
RETURN;
ELSE
COMMIT;
L_REQUEST_FLAG := FND_CONCURRENT.WAIT_FOR_REQUEST(
REQUEST_ID => L_REQUEST_ID,
INTERVAL => 5,
MAX_WAIT => 0,
PHASE => L_PHASE,
STATUS => L_STATUS,
DEV_PHASE => L_DEV_PHASE,
DEV_STATUS => L_DEV_STATUS,
MESSAGE => L_MESSAGE
);
END IF;
COMMIT;
EXCEPTION
WHEN E_SUBMIT_FAILED THEN
ERRCODE := '1';
ERRMSG := '提交处理事务处理接口请求失败！'||SUBSTR(SQLERRM,1,100);
FND_FILE.PUT_LINE(FND_FILE.LOG,ERRMSG);
ROLLBACK;
RETURN;
END;
今天关注到这个问题，将找到的资料收集在这里：
1、关于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),
'','','',
'','','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','','','','','','');
3、在FORM中动态使用记录组例子：当:trx_controls.site_use_code = 'BILL_TO' 时，对于 bill_to_customer_name的LOV使用的SQL为
select * from XXX1;当:trx_controls.site_use_code = 'SHIP_TO' 时，对于 bill_to_customer_name的LOV使用的SQL为
select *实现：1）为bill_to_customer_name创建一个基本的LOV（常规LOV,用LOV向导创建）bill_to_customer_lov，在LOV创建过程中明确查询出的列，返回值等
2）在记录组中创建 bill_to_group，ship_to_group注：记录组查询的列需要与基本LOV的相同，这样才能在更换记录组后根据基本LOV的设置将相应的项返回给相应的值
SQL分别为 select * from XXX1;
select * from XXX2;3)创建trigger
bill_to_customer_name下创建when_new_item_instance触发器
添加代码：
IF :trx_controls.site_use_code = 'BILL_TO' THENSET_LOV_PROPERTY( 'BILL_TO_CUSTOMERS_LOV', GROUP_NAME,
'BILL_TO_GROUP' );ELSIF :trx_controls.site_use_code = 'SHIP_TO' THENSET_LOV_PROPERTY( 'BILL_TO_CUSTOMERS_LOV', GROUP_NAME,
'SHIP_TO_GROUP' );END IF;from XXX2;
4、FORM中实现行号自动增长
- 在记录块的PRE-QUERY中查询出数据库中已有的最大行号，并将其记录到参数中作为最大的行号，如果单据的头是新建的，那这个参数默认的最大值则为1
- 在行记录的WHEN-CREATE-RECORD中将最大行号的参数值+1赋给行号字段
- 行号数据项的WHEN-VALIDATE-ITEM中检查行号是否<0；同时检查行号在数据库中是否存在(对于同时录入多行相同的行号在这个触发器中无需处理)
- 在WHEN-NEW-RECORD-INSTANCE中检查如果块的状态是NEW，从数据库中获取最大的行号，并设置行号
- 在WHEN-VALIDATE-RECORD中判断当前的行号是否大于参数中的最大行号，如果是则覆盖参数的最大行号
procedure line_num ( event varchar2) is
l_line_num_count number;
begin
if ( event = 'WHEN-VALIDATE-ITEM') then
if :lines.line_number <= 0 then
fnd_message.set_name('XHU','XHU_ALL_ENTER_VALUE_GT_ZERO');
fnd_message.error;
raise form_trigger_failure;
end if;
-- check the unique line number from DB
if xhu_orders_sv.po_line_num_exists(name_in('headers.header_id'),name_in('lines.line_number')) then
fnd_message.set_name('XUH','XHU_ENTER_UNIQUE_LINE_NUM');
fnd_message.error;
raise form_trigger_failure;
end if;
elsif (event = 'WHEN-CREATE-RECORD') then
:lines.line_number := :parameter.max_line_num + 1;
elsif (event = 'PRE-QUERY') then
-- get the maximums line number from the DB
:parameter.max_line_num := xhu_orders_sv.get_max_line_num(name_in('headers.header_id'));
elsif (event = 'WHEN-VALIDATE-RECORD') then
if :lines.line_number > :parameter.max_line_num then
:parameter.max_line_num := :lines.line_number ;
end if;
elsif (event = 'WHEN-NEW-RECORD-INSTANCE')then
if upper(get_block_property('lines',status)) = upper('NEW') and :System.Mode <> 'ENTER-QUERY' then
:parameter.max_line_num := xhu_orders_sv.get_max_line_num(name_in('headers.header_id'));
line_num('WHEN-CREATE-RECORD');
SET_RECORD_PROPERTY(get_block_property('lines',current_record), 'lines', STATUS, NEW_STATUS );
end if;
else
APP_EXCEPTION.INVALID_ARGUMENT('LINE_NUMBER', 'EVENT', EVENT);
end if;
exception
when others THEN
raise;
end line_num;
--headers:单据的头数据块
--lines：行号所属的数据块
--xhu_orders_sv.get_max_line_num：根据头ID取得数据库中单据的最大行号
--XHU_ENTER_UNIQUE_LINE_NUM：消息字典：请输入唯一行号
--XHU_ALL_ENTER_VALUE_GT_ZERO：消息字典：请输入大于0的值
各触发器的HANDLER：procedure when_create_record is
begin
lines.line_num('WHEN-CREATE-RECORD');
end when_create_record;
procedure when_new_record_instance is
begin
lines.line_num('WHEN-NEW-RECORD-INSTANCE');
end when_new_record_instance;
procedure when_validate_record is
begin
lines.line_num('WHEN-VALIDATE-RECORD');
end when_validate_record;
procedure pre_query is
begin
lines.line_num('PRE-QUERY');
end pre_query;
5、改变ITEM属性
app_item_property.set_property('trx_headers.import_flag',
UPDATEABLE, PROPERTY_OFF );
6、改变RECORD属性
set_record_property(:system.trigger_record,
:system.cursor_block, STATUS, QUERY_STATUS); --其中STATUS包括 insert_status,new_status,query_status
7、查询窗口设置日期从、至
查询数据块trx_headers_qf设置gl_date_from、gl_date_to
属性：数据类型—Date 最大长度—12，查询长度—255
添加触发器KEY-LISTVAL,内容为calendar.show;
被查询的数据块trx.headers.query_find;内容为：App_find.query_find('MAIN_WIN', 'HEADERS_QF_WIN', 'TRX_HEADERS_QF');
trx_headers.pre_query;内容为:
PROCEDURE pre_query IS
BEGIN
if :parameter.G_QUERY_FIND = 'TRUE' then
copy(:TRX_HEADERS_QF.bill_to_customer_id,'trx_headers.bill_to_customer_id');
app_find.query_range(:trx_headers_qf.trx_number_from,:trx_headers_qf.trx_number_to,'trx_headers.trx_number');
app_find.query_range(:trx_headers_qf.gl_date_from,:trx_headers_qf.gl_date_to,'trx_headers.gl_date');
:parameter.G_QUERY_FIND := 'FALSE';
end if;
END;
注：检查trx_headers.gl_date的查询长度，需要设置为255，否则在点击‘FIND’后会报错
8、启用Tools菜单栏
用户
最
多可以定义
45个
form-level 的
trigger ，名
称必须为
SPECIALn，
其中
SPECIAL1 to SPECIAL15
属
于
Tools
菜
单
项，
SPECIAL16 to SPECIAL30
属
于
Reports
菜
单
项
, SPECIAL31 to SPECIAL45
属
于
Actions
菜
单
项
,
其中
Reports、
Actions的名
称可以
被
修改。
1）在pre_form中添加app_special.instantiate('SPECIAL1', '&Find', 'bkord');
参数1为触发器名称，
参数2为显示的菜单项内容，
参数3为图标样式，
参数4、参数5有默认值
2）在form级别创建SPECIAL1触发器，实现该菜单项内容
例如：
app_custom.open_window('HEADERS_QF_WIN');
go_block('TRX_HEADERS_QF');
9、自定义右键菜单
右
键
菜单
也
可以自
定义，
form-level PRE-POPUP-MENU trigger 引
用
APPSTAND.EVENT('PRE-POPUP-MENU')
建
立
default menu；
建
立
block /item level pre-pop-menu
trigger
须
(
Execution Hierarchy
->After
)
。
从
block /item level pre-pop-menu trigger(
Execution Hierarchy ->
After
)
中
调
用
app_popup.instantate
函
数
初
始
化右
键
菜单
项
：
procedure APP_POPUP.INSTANTIATE(
option_name varchar2,
txt varchar2,
initially_enabled boolean default true,
separator varchar2 default null);
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
