EBS 常用SQL整理一

模擬登入

 BEGIN
  fnd_global.apps_initialize(user_id => 1196, resp_id => 50719, resp_appl_id => 20023);
  mo_global.set_policy_context( p_access_mode => 'S',
                                p_org_id      => 82 );
END;

檢視請求所屬職責

SELECT fcp.concurrent_program_name,
       fr.RESPONSIBILITY_NAME
 FROM fnd_concurrent_programs fcp,
      fnd_request_group_units frgu,
      fnd_request_groups frg,
      fnd_responsibility_vl fr
WHERE fcp.concurrent_program_name = 'A%'
  AND frgu.request_unit_id = fcp.concurrent_program_id
  AND frgu.request_group_id = frg.request_group_id
  AND fr.REQUEST_GROUP_ID = frg.request_group_id;

使用者對應職責

SELECT fu.user_id,
       fu.user_name,
       frv.responsibility_name,
       ppf.last_name
  FROM fnd_user                         fu,
       apps.fnd_user_resp_groups_direct furg,
       fnd_responsibility_vl            frv,
       per_people_f                     ppf
 WHERE fu.user_id = furg.user_id
   AND furg.responsibility_id = frv.responsibility_id
   AND fu.user_name LIKE 'A%'
   AND fu.employee_id = ppf.person_id;

資料庫操作