常用sql

********************************************
1、初始化环境
********************************************
begin
fnd_global.APPS_INITIALIZE(user_id => youruesr_id ,resp_id => yourresp_id,resp_appl_id =>yourresp_appl_id);
  return (TRUE);
end;
********************************************
2、查询键弹性域
********************************************
select * from fnd_flex_values_vl ffv where ffv.FLEX_VALUE_SET_ID=1009628   --1009629会计科目段
********************************************
3、查询数据库对象是否存在
********************************************
select COUNT(*)
  from all_objects
 WHERE OBJECT_TYPE = 'VIEW'--TABLE /PROCEDURE /PACKAGE /FUNCTION /TRIGGER/ SYNONYM /PACKAGE BODY
   AND STATUS='INVALID'
   AND OBJECT_NAME = 'XXX_TRANS_ITEM_DETAIL_V'
********************************************
4、查询列所在的所有对象（表、视图）
********************************************
select * from all_tab_columns where column_name='WIP_RESOURCE_ID'
********************************************
5、查询用户组织
********************************************
select whse_code
   from ic_whse_mst a, sy_orgn_usr u
  where a.orgn_code = u.orgn_code
    and u.user_id = 1115
********************************************
6、查询仓库组织
********************************************
select organization_code orgn_code,
       organization_name orgn_name,1,od.*
  from org_organization_definitions od --ic_whse_mst
 where organization_id<>123
union
select sorg.ORGN_CODE,
       sorg.ORGN_NAME,2,sorg.*
  from SY_ORGN_MST_VL  sorg
 order by 1
********************************************
7、查询配置文件
********************************************
select * from fnd_profile_options_tl t where t.profile_option_name in ('ALLOW_OPM_TRUNCATE_TXN','IC$EPSILON') and t.language='ZHS'
********************************************
8、应用查询
********************************************
select al.APPLICATION_ID, al.APPLICATION_NAME, al.APPLICATION_SHORT_NAME
  from FND_APPLICATION_VL al
********************************************
9、常用期间查询
********************************************
select * from GL_PERIOD_STATUSES_V gl 打开和关闭期间  GLXOCPER  打开和关闭期间 101
select * from GL_PERIOD_STATUSES ap  AP 会计期  AP_APXSUMPS  控制期间状态 200
select * from GL_PERIOD_STATUSES ar  打开/关闭会计期  AR_ARXSUMPS 打开/关闭会计期 222
select * from ORG_ACCT_PERIODS_V inv  成本期间  INV_INVTTGMP  成本期间
select * from GL_PERIOD_STATUSES po  控制采购期间  PO_POXSTCPP 控制采购期间 GUI 201
select * from FND_APPLICATION_VL
********************************************
10、常用状态查询－快码
********************************************
select *
  from fnd_lookup_values f
 where f.lookup_type = 'DELIVERY_STATUS'
   and f.language = 'ZHS'
   and nvl(f.ENABLED_FLAG, 'N') = 'Y'
   and trunc(sysdate) between nvl(f.start_date_active, trunc(sysdate)) and
       nvl(f.end_date_active, trunc(sysdate))

PICK_STATUS——挑库状态（released_status）——发运 管理系统
DELIVERY_STATUS——发运确认状态（status_code）——
CREDIT_MEMO_REASON——贷项通知单原因——应收张款管理系统
FLOW_STATUS——订单头状态
CANCEL_CODE（oe_lookups）——取消/跟踪/版本跟踪原因（取消订单的原因……）——订单管理系统
SHIP_METHOD——承运人——库存管理系统
********************************************
11、取得主机信息
********************************************
set serveroutput on size unlimited
select
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
from dual
********************************************
12、
********************************************

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/797362/viewspace-592691/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/797362/viewspace-592691/