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;

 

数据库操作

1、分析数据表

analyze table inv.mtl_system_items_b compute statistics;

 

2、修改数据表字段长度

alter table zzinv.zzinv_wms_info_interface
     modify shipping_instructions VARCHAR2(256);

 

3、添加数据表字段

alter table ZZPO.ZZPO_PSI_PO_INTERFACE_ERRORS add CURRENT_PO_NUMBER varchar2(50);