平时积累的一些SQL语句(转） 2

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');

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);

ARAMETER.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;