Nolem的部落格

之前會計師稽核跟我要的資料,
這程式會撈出相關user 對應function,並排除resp exclude 的menu /function ,
排除function 參數為QUERY_ONLY , 排除menu 沒有prompt(即代表user看不到的)
並產出檔案到資料庫端的實體路徑, 此程式可在R11 直接跑,
R12 應該要核對一下schena 調整...

declare
 /*撈出user 及resp 和對應的menu */
-- V_GB_RESPONSIBILITY 只是一個user 串RESPONSIBILITY 的view而已...
  cursor cur is select * from V_GB_RESPONSIBILITY where USER_name like 'G00%'
                  and nvl(user_end,sysdate)>=to_date('20100101','yyyymmdd') order by USER_name;
 
 
 /*撈出指定menu 全部階層link 及對應的function*/
  cursor cur_m(vMENU_ID in number) is select a.menu_id,a.ENTRY_SEQUENCE,a.menu_name,a.user_menu_name,fun.function_id,fun.FUNCTION_NAME,fun.USER_FUNCTION_NAME
 from
 (select
mn.menu_id,mn.menu_name,mn.user_menu_name,b.ENTRY_SEQUENCE,b.function_id from
 fnd_menus_vl mn ,
(select * from fnd_menu_entries
 start with  MENU_ID=vMENU_ID
 connect by  prior SUB_MENU_ID=MENU_ID) b
  where mn.MENU_ID=b.MENU_ID ) a,fnd_form_functions_vl  fun
 where a.function_id=fun.function_id    ;
 user_str varchar2(2000);
 menu_str varchar2(2000);  
 l_file        UTL_FILE.file_type;
 l_file_name   VARCHAR2 (1000):='user_function' || to_char(sysdate,'yyyymmdd');
 l_seprate varchar2(1):='^' ;
 l_prompt varchar2(1000):=null;                                                        
begin
--dbms_output.enable(2000000);
 
/*開啟檔案準備寫入,EXP_DB_DUMP 是對應實體路徑的Directory */
l_file := UTL_FILE.fopen ('EXP_DB_DUMP', l_file_name, 'w');
 for rec in cur loop
  user_str :=null;
  menu_str:=null;
  l_prompt :=null;
/*寫入欄位*/
   l_prompt := 'USER_ID'|| l_seprate ||'USER_NAME'|| l_seprate ||'USER_DESCRIPTION'|| l_seprate ||'USER_START' || l_seprate ||'USER_END';
   l_prompt := l_prompt || l_seprate ||'RESPONSIBILITY_ID'|| l_seprate ||'RESPONSIBILITY_NAME'|| l_seprate ||'menu_id'|| l_seprate ||'menu_name';
   l_prompt := l_prompt || l_seprate ||'user_menu_name'|| l_seprate ||'FUNCTION_ID'|| l_seprate ||'FUNCTION_NAME'|| l_seprate ||'USER_FUNCTION_NAME' ;  
   UTL_FILE.put_line (l_file,l_prompt );

/*組合user 及resp字串*/
user_str := to_char(rec.USER_ID) || l_seprate || rec.USER_NAME|| l_seprate || rec.USER_DESCRIPTION  || l_seprate ||

                  to_char(REC.USER_START,'yyyy/mm/dd')|| l_seprate || to_char(REC.USER_END,'yyyy/mm/dd') || l_seprate

                  || REC.RESPONSIBILITY_ID || l_seprate || REC.RESPONSIBILITY_NAME;

/*取出menu子menu及相關function*/
    for rec_m in cur_m(rec.MENU_ID) loop
      menu_str :=null;
      declare
        vget varchar2(1):=null;
      begin
       if vget is null then  
        begin

        /*排除resp exclude 的menu*/
        select 'x' into vget from fnd_resp_functions
         where RESPONSIBILITY_ID=rec.RESPONSIBILITY_ID
           and rule_type='M'
           and ACTION_ID=rec_m.menu_id;
        exception
         when no_data_found then  null;
        end;
       end if;        
        
       if vget is null then         
        begin
        
        /*排除menu 沒有prompr的fcunction*/
        select 'x' into vget from FND_MENU_ENTRIES_TL
         where PROMPT is null
           and LANGUAGE='US'
           and MENU_ID=rec_m.menu_id
           and ENTRY_SEQUENCE=rec_m.ENTRY_SEQUENCE;    
        exception
         when no_data_found then  null;
        end;
       end if;
               
       if vget is null then         
        begin    

       /*排除resp exclude 的function*/
        select 'x' into vget from fnd_resp_functions
         where RESPONSIBILITY_ID=rec.RESPONSIBILITY_ID
           and rule_type='F'
           and ACTION_ID=rec_m.function_id;
        exception
         when no_data_found then  null;
        end;
       end if;  
             
       if vget is null then   
        begin       

       /*排除function 參數設定只能查詢的function, 此為針對稽核只需要稽查可新增修改的功能加入*/        
        select 'x' into vget from fnd_form_functions_vl
         where PARAMETERS like '%QUERY_ONLY="YES"%'
           and FUNCTION_ID=rec_m.function_id;   
        exception
         when no_data_found then  null;
        end;                
       end if; 

          /*確定不再排除範圍後,寫入menu 及function資訊*/       
          if vget is null then  
          menu_str :=rec_m.menu_id || l_seprate || rec_m.menu_name || l_seprate || rec_m.user_menu_name|| l_seprate

                            || rec_m.FUNCTION_ID || l_seprate || rec_m.FUNCTION_NAME || l_seprate || rec_m.USER_FUNCTION_NAME;
          UTL_FILE.put_line (l_file, user_str|| l_seprate || menu_str);
          end if;
      end;  
    end loop;
 end loop;
 /*free file cache 及close */
 UTL_FILE.fflush (l_file);
 UTL_FILE.fclose (l_file);
EXCEPTION
   WHEN OTHERS
   THEN
      IF UTL_FILE.is_open (l_file)
      THEN
         UTL_FILE.fclose (l_file);
      ELSE
         DBMS_OUTPUT.PUT_LINE ('SQLERRM: ' || SQLERRM);
         DBMS_OUTPUT.PUT_LINE ('SQLCODE: ' || SQLCODE);
      END IF;
end;