ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 常用sql

常用sql

原创 Linux操作系统 作者:veyard 时间:2009-05-05 22:17:52 0 删除 编辑
********************************************
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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-05-04

  • 博文量
    10
  • 访问量
    13812