Form开发积累的常用代码

获取当前窗口

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;