Form开发积累的常用代码

hutianci

获取当前窗口

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 2589587 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 :<your block name.org_id> is not null\
 IF :<block name.org_id> <> nvl(:<parameter.old_org_id>,-99) THEN
   -- Get the cache for current org
 END IF;
ELSE
 -- Refresh the cache
...
END IF;
When-Validate-Item Trigger of Operating Unit field
IF (:<your block name.org_id> IS NOT NULL ) THEN
 IF :<block name.org_id> <> nvl(:<parameter.old_org_id>,-99) THEN
    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 (:<your block name.org_id> IS NOT NULL ) THEN
 IF :<block name.org_id> <> nvl(:<parameter.old_org_id>,-99) THEN
   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 (:<your block name.org_id> IS NOT NULL ) THEN
 IF :<block name.org_id> <> nvl(:<parameter.old_org_id>,-99) THEN
   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 (:<your block name.org_id> IS NOT NULL ) THEN
  IF :<block name.org_id> <> nvl(:<parameter.old_org_id>,-99) THEN
    mo_global.set_policy_context('S', :block.org_id);
    -- Get the cache for the current org
  END IF;
END IF;