GET_VIEW_PROPERTY(GET_ITEM_PROPERTY(:SYSTEM.CURSOR_ITEM, ITEM_CANVAS),WINDOW_NAME)
设置金额显示格式
PROCEDURE FORMAT_PRICE(EVENT VARCHAR2) IS
BEGIN
IF (EVENT IN ('WHEN-VALIDATE-ITEM','POST-QUERY'))
THEN
APP_ITEM_PROPERTY.SET_PROPERTY('LINES.SUGGESTED_PRICE',FORMAT_MASK, FND_CURRENCY.GET_FORMAT_MASK(:ORDERS.CURRENCY_CODE,GET_ITEM_PROPERTY('LINES.SUGGESTED_PRICE',MAX_LENGTH)));
APP_ITEM_PROPERTY.SET_PROPERTY('LINES.TOTAL_PRICE',FORMAT_MASK, FND_CURRENCY.GET_FORMAT_MASK(:ORDERS.CURRENCY_CODE,GET_ITEM_PROPERTY('LINES.TOTAL_PRICE',MAX_LENGTH)));
ELSE
FND_MESSAGE.DEBUG('Invalid event passed to lines.format_price '||EVENT);
END IF;
END FORMAT_PRICE;
获取帐户帐套，帐户CODE相关信息
/*=====================================
** PROCEDURE: pre_form
**=====================================*/
procedure pre_form is
cursor sob_cur(sob_id number) is
SELECT sob.set_of_books_id,
sob.currency_code,
sob.chart_of_accounts_id
FROM gl_sets_of_books sob
WHERE sob.set_of_books_id = sob_id ;
l_coa_id number;
l_sob_id number;
l_currency_code varchar2(15);
l_org_id number;
l_order_num_mode varchar2(10);
begin
-- Set of Books ID
l_sob_id := fnd_profile.value('GL_SET_OF_BKS_ID');
-- org id
l_org_id := fnd_profile.value('ORG_ID');
if (l_org_id is null) then
fnd_message.set_name('FND', 'PROFILES-CANNOT READ');
fnd_message.set_token('OPTION', 'ORG_ID');
fnd_message.error;
raise form_trigger_failure;
end if;
open sob_cur(l_sob_id) ;
fetch sob_cur into l_sob_id, l_currency_code,l_coa_id;
close sob_cur;
:parameter.currency_code := l_currency_code ;
l_order_num_mode := fnd_profile.value('TRN_PO_ORDER_NUM_TYPE_036');
-- Stash values away for future reference
-- :parameter.org_id := to_number(l_org_id);
--:parameter.set_of_books_id := to_number(l_sob_id);
:parameter.chart_of_accounts_id := to_number(l_coa_id);
:parameter.order_number_mode := l_order_num_mode;
-- set amount mask
set_amount_mask;
end pre_form;
procedure set_amount_mask is
begin
-------------------------------------
-- setup amount field's format mask
-- ----------------------------------
set_item_property('HEADERS.TOTAL_AMOUNT',FORMAT_MASK,
fnd_currency.get_format_mask(
:parameter.currency_code,
get_item_property('HEADERS.TOTAL_AMOUNT', MAX_LENGTH)));
set_item_property('LINES.LINE_AMOUNT',FORMAT_MASK,
fnd_currency.get_format_mask(
:parameter.currency_code,
get_item_property('LINES.LINE_AMOUNT', MAX_LENGTH)));
end set_amount_mask;
动态提交请求
APPS.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,
chr(0),'','','','','','','','','','','','','','','','','','','',
'','','','','','','','','','', '','','','','','','','','','',
'','','','','','','','','','', '','','','','','','','','','',
'','','','','','','','','','', '','','','','','','','','','',
'','','','','','','','','','', '','','','','','','','','',''
)
RETURN NUMBER;
状态判断
get_block_property('headers',status)
:System.Mode
GET_VIEW_PROPERTY(GET_ITEM_PROPERTY(:SYSTEM.CURSOR_ITEM, ITEM_CANVAS),WINDOW_NAME
Get_Block_Property( 'LINES_PROMPT', PREVIOUSBLOCK)
GET_RECORD_PROPERTY(:SYSTEM.CURSOR_RECORD,'SHOPPEDAYOVERTB_V',Status ); --:SYSTEM.RECORD_STATUS ;
键弹性域定义和更新
定义
fnd_key_flex.define(
BLOCK=>'Items',
FIELD=>'EXPENSE_ACCID_DSP',
APPL_SHORT_NAME=>'SQLGL',
CODE=>'GL#',
ID=>'EXPENSE_CCID',
REQUIRED=>'Y',
USEDBFLDS=>'N',
updateable=>'',
VALIDATE=> 'FULL',
VRULE=> '\\\\nSUMMARY_FLAG\\\\nI\\\\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\\\\nN',
NUM=>':PARAMETER.CHART_OF_ACCOUNTS_ID');
Form中执行SQL语句
sql1:=' TRUNCATE TABLE cfnd_matrix_cells';
forms_ddl(sql1);
更新
procedure update_definition(
block varchar2,
field varchar2,
enabled varchar2 default '$FFLEX_DEFAULT$',
validate varchar2 default '$FFLEX_DEFAULT$',
vdate varchar2 default '$FFLEX_DEFAULT$',
displayable varchar2 default '$FFLEX_DEFAULT$',
insertable varchar2 default '$FFLEX_DEFAULT$',
updateable varchar2 default '$FFLEX_DEFAULT$',
vrule varchar2 default '$FFLEX_DEFAULT$',
copy varchar2 default '$FFLEX_DEFAULT$',
derived varchar2 default '$FFLEX_DEFAULT$',
valatt varchar2 default '$FFLEX_DEFAULT$',
title varchar2 default '$FFLEX_DEFAULT$',
required varchar2 default '$FFLEX_DEFAULT$',
autopick varchar2 default '$FFLEX_DEFAULT$',
autocombpick varchar2 default '$FFLEX_DEFAULT$',
usedbflds varchar2 default '$FFLEX_DEFAULT$',
allownulls varchar2 default '$FFLEX_DEFAULT$',
data_set varchar2 default '$FFLEX_DEFAULT$',
column varchar2 default '$FFLEX_DEFAULT$',
where_clause varchar2 default '$FFLEX_DEFAULT$',
query_security varchar2 default '$FFLEX_DEFAULT$',
qbe_in varchar2 default '$FFLEX_DEFAULT$',
read_only varchar2 default '$FFLEX_DEFAULT$',
dinsert varchar2 default '$FFLEX_DEFAULT$',
longlist varchar2 default '$FFLEX_DEFAULT$',
no_combmsg varchar2 default '$FFLEX_DEFAULT$',
lock_flag varchar2 default '$FFLEX_DEFAULT$',
combqp_where varchar2 default '$FFLEX_DEFAULT$',
derive_always varchar2 default '$FFLEX_DEFAULT$',
help varchar2 default '$FFLEX_DEFAULT$',
default_mode varchar2 default '$FFLEX_DEFAULT$',
where_clause_msg VARCHAR2 DEFAULT '$FFLEX_DEFAULT$')
使用该方法更新相应的属性即可。
Form中导入数据
DECLARE
access_id NUMBER;
l_server_url VARCHAR2(100);
l_parameters VARCHAR2(100);
button_choice INTEGER;
l_file_id VARCHAR2(100);
l_gfm_id INTEGER;
BEGIN
IF :file.import_type IS NULL OR :file.template_name IS NULL THEN
fnd_message.set_string('请先输入完整数据.');
fnd_message.show;
RAISE form_trigger_failure;
END IF;
access_id := fnd_gfm.authorize(NULL);
-- BUG 25587 FND File Upload form is not displayed consistently
-- in the correct language - now using fnd_function.execute to enforce
-- ICX security and NLS issues.
fnd_profile.get('APPS_WEB_AGENT',
l_server_url);
l_parameters := 'access_id=' || access_id || ' l_server_url=' || l_server_url;
fnd_function.EXECUTE(function_name => 'FND_FNDFLUPL',
open_flag => 'Y',
session_flag => 'Y',
other_params => l_parameters);
-- Display a modal message for user to indicate file upload
-- is completed.
fnd_message.set_name('FND',
'ATCHMT-FILE-UPLOAD-COMPLETE');
button_choice := fnd_message.question(button1 => 'YES',
button2 => NULL,
button3 => 'NO',
default_btn => 1,
cancel_btn => 3,
icon => 'question');
IF (button_choice = 3) THEN
NULL;
ELSIF (button_choice = 1) THEN
DELETE FROM cfnd_matrix_cells;
COMMIT;
l_file_id := '';
copy(l_file_id,
'document_header.file_name_display');
l_gfm_id := fnd_gfm.get_file_id(access_id);
IF l_gfm_id IS NOT NULL THEN
copy(to_char(l_gfm_id),
'document_header.media_id');
SELECT decode(instr(file_name,
'/'),
0,
file_name,
substr(file_name,
instr(file_name,
'/') + 1))
INTO l_file_id
FROM fnd_lobs
WHERE file_id = l_gfm_id;
IF l_file_id IS NOT NULL THEN
:file.filename := l_file_id;
SELECT cfnd_matrix_cells_s1.NEXTVAL
INTO :file.working_id
FROM dual;
cfnd_upl_pkg.extract_blob(l_gfm_id,
:file.file_cs,
chr(:file.delimiter),
:file.working_id);
go_item('CFND_MATRIX_CELLS.LINE_NO');
clear_block(no_validate);
execute_query;
DELETE FROM fnd_lobs
WHERE file_id = l_gfm_id;
forms_ddl('commit');
forms_ddl('commit');
END IF;
END IF;
END IF;
END;
FORM中获取光标所在的TAB页面
1. 在Form级触发器中添加触发WHEN-TAB-PAGE-CHANGED
2. 在此触发器中写如下代码：
DECLARE
canvas_id VARCHAR2(30); --标签页ID
BEGIN
canvas_id := GET_CANVAS_PROPERTY('标签画布名', topmost_tab_page);
IF canvas_id='标签页1' then go_block('块1');end if;
IF canvas_id='标签页2' then go_block('块2');end if;
IF canvas_id='标签页3' then go_block('块3');end if;
execute_query;
END;
设置时间
DECLARE
timer_id Timer;
one_minute NUMBER(5) := 60000;
BEGIN
timer_id := CREATE_TIMER('emp_timer', one_minute, REPEAT|NO_REPEAT);
END;
生成Editer框
DECLARE
ed_id Editor;
status BOOLEAN;
BEGIN
ed_id:=Find_Editor('edit_name'); ---由'edit_name'导航器定义
IF NOT Id_Null(ed_id) THEN
Show_Editor(ed_id, NULL, :block_name.item_name, status);
ELSE
Message('Editor "Happy_Edit_Window" not found');
RAISE Form_Trigger_Failure;
END IF;
END;
动态产生一个'LOV'框
DECLARE
lv_id LOV;
status BOOLEAN;
BEGIN
lv_id := Find_LOV('lov_name'); ---'lov_name' 由导航器定义
-- IF Id_Null(lv_id) THEN
-- lv_id := Find_LOV('lov_name1'); ---'lov_name1' 由导航器定义
-- END IF;
status := Show_LOV(lv_id,10,20);
END;
打开form上标准菜单
app_menu.set_prop('EDIT.SELECT_ALL', ENABLED, PROPERTY_ON);
app_menu.set_prop('EDIT.DESELECT_ALL', ENABLED, PROPERTY_ON);
在相应层次建立出发器即可；
From 中Item代码格式Format
A typical item handler looks like this:
procedure ITEM_NAME(event VARCHAR2) IS
IF (event = ’WHEN–VALIDATE–ITEM’) THEN
–– validate the item
ELSIF (event = ’INIT’) THEN
–– initialize this dependent item
ELSIF (event in (’PRE–RECORD’, ’POST–QUERY’)) THEN
–– etc.
ELSE fnd_message.debug(’Invalid event passed to item_name: ’ ||
EVENT);
END IF;
END ITEM_NAME;
取关键性弹性域帐户描述的方法
declare
-- Boolean parameters are translated from/to integers:
-- 0/1/null <--> false/true/null
result boolean;
begin
-- Call the function
result := fnd_flex_keyval.validate_ccid(
appl_short_name => :appl_short_name,--SQLGL
key_flex_code => :key_flex_code,--GL#
structure_number => :structure_number,--50228
combination_id => :combination_id,--113773
displayable => :displayable,--ALL
data_set => :data_set,
vrule => :vrule,
security => :security, --IGNORE
get_columns => :get_columns,
resp_appl_id => :resp_appl_id, --101
resp_id => :resp_id,--50481
user_id => :user_id, --11193
select_comb_from_view => :select_comb_from_view);
dbms_output.put_line(fnd_flex_keyval.concatenated_descriptions);
-- Convert false/true/null to 0/1/null
end;
组织访问权限的控制语句(11i)
SELECT ict.ROWID row_id,
ict.organization_id,
ict.rate,
ict.base_type_code,
flv.MEANING,
ict.adjust_account_id,
ict.description,
ict.created_by,
ict.creation_date,
ict.last_updated_by,
ict.last_update_date,
ict.last_update_login,
ood.ORGANIZATION_NAME ORGANIZATION_NAME
FROM dpos_item_cost_rate ict, org_organization_definitions ood,org_access oa,fnd_lookup_values_vl flv
WHERE ict.organization_id = ood.ORGANIZATION_ID
AND oa.organization_id=ood.ORGANIZATION_ID
AND oa.resp_application_id=fnd_profile.VALUE('RESP_APPL_ID')
AND oa.responsibility_id = fnd_profile.VALUE('RESP_ID')
循环访问所有记录
DECLARE
cur_blk VARCHAR2(40) := :System.Cursor_Block;
bk_id Block;
BEGIN
bk_id := Find_Block(cur_blk);
GO_BLOCK('SHOPPEDAYOVERTB_V');
GO_RECORD(1);
LOOP
if get_block_property(bk_id,Update_Allowed)='TRUE' then
:SHOPPEDAYOVERTB_V.import_flag:='N';
UPDATE SHOPPEDAYOVERTB SET import_flag ='N' where SHOPPEDAYOVERTB.ROWID=:SHOPPEDAYOVERTB_V.ROW_ID;
end if;
EXIT WHEN (NAME_IN('SYSTEM.LAST_RECORD') = 'TRUE');
NEXT_RECORD;
END LOOP;
commit;
END;
在查询模式下使LOV有效
To enable LOVs in ENTER–QUERY mode on an item, create an item–level KEY–LISTVAL trigger as follows:
Trigger: KEY–LISTVAL
IF (:SYSTEM.MODE != ’ENTER–QUERY’) THEN LIST_VALUES;
ELSE SHOW_LOV(’query lov’);
END IF;
关闭窗口代码段
PROCEDURE close_window (wnd VARCHAR2) IS
IF wnd = ’HEADER’ THEN
––
–– Exit the form
––
app_window.close_first_window;
ELSIF wnd = ’LINES’ THEN
––
–– Close detail windows (Shipments)
––
app_custom.close_window(’SHIPMENTS’);
––
–– If cursor is in this window,
–– move it to the HEADER block
––
IF (wnd = GET_VIEW_PROPERTY(GET_ITEM_PROPERTY(
:SYSTEM.CURSOR_ITEM,ITEM_CANVAS),
WINDOW_NAME)) THEN
GO_BLOCK(’HEADER’);
END IF;
ELSIF wnd = ’SHIPMENTS’ THEN
––
–– If cursor is in this window,
–– move it to the LINES block
––
IF (wnd = GET_VIEW_PROPERTY(GET_ITEM_PROPERTY(
:SYSTEM.CURSOR_ITEM, ITEM_CANVAS),
WINDOW_NAME)) THEN
GO_BLOCK(’LINES’);
END IF;
END IF;
––
–– THIS CODE MUST REMAIN HERE. It ensures
–– the cursor is not in the window that will
–– be closed by moving it to the previous block.
––
IF (wnd = GET_VIEW_PROPERTY(GET_ITEM_PROPERTY(
:SYSTEM.CURSOR_ITEM, ITEM_CANVAS),
WINDOW_NAME)) THEN
DO_KEY(’PREVIOUS_BLOCK’);
END IF;
––
–– Now actually close the designated window
––
HIDE_WINDOW(wnd);
END close_window;
从当前form转到请求提交页面
fnd_function.execute( function_name => 'FND_FNDRSRUN',
open_flag => 'Y',
session_flag => 'Y',
other_params => 'DODT_REQ_ID="'||TO_CHAR(l_request_id)||'"');
在R12中实现多OU编程
A.首先最重要的是要在pre-form中初始化多OU
BEGIN
APP_STANDARD.EVENT(‘PRE-FORM’);
//必须在APP_STANDARD.EVENT()后执行
MO_GLOBAL.init ('INV');--参数可以使’S’—单OU，’M’-多OU，或者已经注册过的应用简称
END;
B.初始化后获取OU的信息，在Pre-form中获取OU信息，或在块上When-Create-Record获取OU信息
Pre-form
DECLARE
l_default_org_id number;
l_default_ou_name varchar2(240);
l_ou_count number;
BEGIN
...
mo_utils.get_default_ou(l_default_org_id, l_default_ou_name, l_ou_count);
:PARAMETER.mo_default_org_id) := l_default_org_id;
:PARAMETER.mo_default_ou_name := l_default_ou_name;
:PARAMETER.mo_ou_count := l_ou_count;
...
END;
When-Create-Record
IF :parameter.mo_default_org_id is not null and :block.org_id is null THEN
:block.org_id := :parameter.mo_default_org_id);
:block.operating_unit := :parameter.mo_default_ou_name;
END IF;
C.在各个触发器实现多OU的支持的代码
When-Create-Record Trigger of Operating Unit Field Block
IF (:parameter.mo_default_org_id IS NOT NULL ) THEN
-- Defaulting org_id from profile option
:block.org_id := :parameter.mo_default_org_id;
:block.operating_unit := :parameter.mo_default_ou_name;
-- Set policy context
mo_global.set_policy_context('S’,:block.org_id);
ELSE
mo_global.set_policy_context('M', null);
END IF;
IF : IF : -- Get the cache for current org END IF; ELSE -- Refresh the cache ... END IF; When-Validate-Item Trigger of Operating Unit field IF (: IF : mo_global.set_policy_context('S', :block.org_id); -- Get the cache for the current org END IF; ELSE -- :block.org_id is null mo_global.set_policy_context('M', null); -- Refresh the cache END IF; When-New-Record-Instance Trigger of Operating Unit Field Block IF (: IF : mo_global.set_policy_context('S', :block.org_id); -- Get the cache for the current org END IF; ELSE -- :block.org_id is null, so set the context to multiple mo_global.set_policy_context('M', null); -- Refresh the cache END IF; Pre-Insert Trigger of Operating Unit Field Block Use this trigger if the form allows the user to commit multiple records. IF (: IF : mo_global.set_policy_context('S', :block.org_id); -- Get the cache for the current org END IF; ELSE -- :block.org_id is null, so set the context to multiple mo_global.set_policy_context('M', null); -- Refresh the cache END IF; Pre-Query Trigger of Operating Unit Field Block BEGIN IF :parameter.mo_ou_count = 1 THEN mo_global.set_policy_context(‘S’,:parameter.mo_default_org_id); ELSE mo_global.set_policy_context('M', null); END IF; -- Other Code END; Pre-Record Trigger of Operating Unit Field Block use this trigger if the form forces the user to commit each record. IF (:parameter.current_record is not null and :parameter.current_record != :system.trigger_record) THEN IF (:system.form_status in ('CHANGED','INSERT')) THEN mo_global.set_policy_context('S', :parameter.old_org_id); -- Get the cache for the current org -- raise error message to the user to commit; -- raise form_trigger_failure; ELSE -- No pending commits. -- Reset the current record variable. :parameter.current_record := ''; END IF; ELSE -- User has not navigated to another record. -- Do not reset the current record variable. null; END IF; Pre-Update Trigger Use this trigger if the form allows the user to commit multiple records commits that are in different operating units. IF (: IF : mo_global.set_policy_context('S', :block.org_id); -- Get the cache for the current org END IF; END IF;下载本文
