--fnd
select * from fnd_application   
select * from fnd_application_tl where application_id=101
select * from fnd_application_vl where application_id = 101
----值集
select * from fnd_flex_value_sets   
select * from fnd_flex_values       
select * from fnd_flex_values_vl
----弹性域
select * from fnd_id_flexs         
select * from fnd_id_flex_structures where id_flex_code='GL#'
select * from fnd_id_flex_segments where id_flex_code='GL#' and id_flex_num=50671

select * from fnd_profile_options_vl
select * from fnd_concurrent_programs    程序表
select * from fnd_concurrent_requests    请求表
select * from fnd_concurrent_processes   进程表

--inv
select * from org_organization_definitions   库存组织
select * from mtl_parameters                 组织参数
select * from mtl_system_items_b where inventory_item_id = 171 and organization_id=204  物料表
select * from mtl_secondary_inventories    子库存
select * from mtl_item_locations           货位
select * from mtl_lot_numbers              批次
select * from mtl_onhand_quantities        现有量表
select * from mtl_serial_numbers           序列
select * from mtl_material_transactions    物料事务记录
select * from mtl_transaction_accounts     会计分录
select * from mtl_transaction_types        事务类型
select * from mtl_txn_source_types         事务来源类型
select * from mfg_lookups ml where ml.LOOKUP_TYPE = 'MTL_TRANSACTION_ACTION'

--po
select * from po_requisition_headers_all   请求头
select * from po_requisition_lines_all     请求行
select * from po_headers_all               订单头
select * from po_lines_all                 订单行
select * from po_line_locations_all       
select * from po_distributions_all          分配
select * from po_releases_all               发送
select * from rcv_shipment_headers          采购接收头
select * from rcv_shipment_lines            采购接收行
select * from rcv_transactions              接收事务处理
select * from po_agents                    
select * from po_vendors                    订单
select * from po_vendor_sites_all

--oe
select * from ra_customers                  客户
select * from ra_addresses_all              地址
select * from ra_site_uses_all              用户

select * from oe_order_headers_all          销售头
select * from oe_order_lines_all            销售行

select * from wsh_new_deliveries            发送
select * from wsh_delivery_details
select * from wsh_delivery_assignments

--gl
select * from gl_sets_of_books     总帐
select * from gl_code_combinations gcc where gcc.summary_flag='Y'   科目组合
select * from gl_balances          科目余额
select * from gl_je_batches        凭证批
select * from gl_je_headers        凭证头
select * from gl_je_lines          凭证行
select * from gl_je_categories     凭证分类
select * from gl_je_sources        凭证来源
select * from gl_summary_templates 科目汇总模板
select * from gl_account_hierarchies  科目汇总模板层次

--ar
select * from ar_batches_all                 事务处理批
select * from ra_customer_trx_all            发票头
select * from ra_customer_trx_lines_all      发票行
select * from ra_cust_trx_line_gl_dist_all   发票分配
select * from ar_cash_receipts_all           收款
select * from ar_receivable_applications_all 核销
select * from ar_payment_schedules_all       发票调整
select * from ar_adjustments_all             会计分录
select * from ar_distributions_all           付款计划

--ap
select * from ap_invoices_all               发票头
select * from ap_invoice_distributions_all  发票行
select * from ap_payment_schedules_all      付款计划
select * from ap_check_stocks_all           单据
select * from ap_checks_all                 付款
select * from ap_bank_branches              银行
select * from ap_bank_accounts_all          银行帐号
select * from ap_invoice_payments_all       核销

 

 

erp常用sql

 

----查找运行请求时间,参数等(可以是某用户的,某个报表)
SELECT C.USER_NAME,
       papf.full_name,
       B.USER_CONCURRENT_PROGRAM_NAME,
       A.REQUEST_DATE,
       A.ARGUMENT_TEXT,
       (A.ACTUAL_COMPLETION_DATE - A.ACTUAL_START_DATE) * 24 * 60 MINUTES,
       A.ACTUAL_START_DATE,
       A.ACTUAL_COMPLETION_DATE,
       a.request_id,
       a.outfile_name

  FROM FND_CONCURRENT_REQUESTS    A,
       FND_CONCURRENT_PROGRAMS_VL B,
       FND_USER                   C,
       per_all_people_f           papf
 WHERE A.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID
   AND A.REQUESTED_BY = C.USER_ID
   and c.user_name = papf.employee_number(+)
   AND A.ACTUAL_COMPLETION_DATE IS NOT NULL
   and B.USER_CONCURRENT_PROGRAM_NAME = '你的程序名称' --- like '%XXX%'
   and c.user_name = ' 你要找的用户的'
   and a.request_date <=
       to_date('2005-03-01 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
   and a.request_date >=
       to_date('2005-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
   AND a.request_id > 2254198 ---为了提高速度,选一个合适时间点的ID

 

/* 2 查找在标准请求组里提交的报表所在的职责*/
SELECT A.RESPONSIBILITY_NAME,B.USER_CONCURRENT_PROGRAM_NAME,B.CONCURRENT_PROGRAM_NAME
FROM FND_RESPONSIBILITY_VL A,
FND_CONCURRENT_PROGRAMS_VL B,
FND_REQUEST_GROUP_UNITS C
WHERE A.APPLICATION_ID=C.APPLICATION_ID
AND A.REQUEST_GROUP_ID=C.REQUEST_GROUP_ID
AND B.APPLICATION_ID=C.UNIT_APPLICATION_ID
AND B.CONCURRENT_PROGRAM_ID=C.REQUEST_UNIT_ID
AND B.USER_CONCURRENT_PROGRAM_NAME LIKE '%物料%'

 

/* 3 查找在菜单里提交的报表所在职责*/
SELECT A.RESPONSIBILITY_NAME, B.PROMPT, F.USER_CONCURRENT_PROGRAM_NAME
  FROM FND_RESPONSIBILITY_VL      A,
       FND_MENU_ENTRIES_VL        B,
       FND_FORM_FUNCTIONS_VL      C,
       FND_REQUEST_GROUPS         D,
       FND_REQUEST_GROUP_UNITS    E,
       FND_CONCURRENT_PROGRAMS_VL F
 WHERE A.MENU_ID = B.MENU_ID
   AND B.FUNCTION_ID = C.FUNCTION_ID
   AND C.PARAMETERS LIKE '%' || D.REQUEST_GROUP_CODE || '%'
   AND D.APPLICATION_ID = E.APPLICATION_ID
   AND D.REQUEST_GROUP_ID = E.REQUEST_GROUP_ID
   AND E.UNIT_APPLICATION_ID = F.APPLICATION_ID
   AND E.REQUEST_UNIT_ID = F.CONCURRENT_PROGRAM_ID
   AND F.USER_CONCURRENT_PROGRAM_NAME LIKE '%物料%'  --报表名  
   and  A.RESPONSIBILITY_NAME like 'ML%'
   order by A.RESPONSIBILITY_NAME

 

/* 1 根据报表文件名称关键字查找报表的执行文件名称等信息*/
SELECT A.USER_CONCURRENT_PROGRAM_NAME,
       A.CONCURRENT_PROGRAM_NAME,
       A.OUTPUT_FILE_TYPE,
       B.EXECUTION_FILE_NAME,
       B.EXECUTABLE_NAME,
       FND_L.MEANING,
       B.USER_EXECUTABLE_NAME,
       B.DESCRIPTION
  FROM FND_CONCURRENT_PROGRAMS_VL A,
       fnd_executables_vl         B,
       FND_LOOKUPS                FND_L
 WHERE A.APPLICATION_ID = B.APPLICATION_ID
   AND A.EXECUTABLE_ID = B.EXECUTABLE_ID
   AND B.EXECUTION_METHOD_CODE = FND_L.LOOKUP_CODE(+)
   AND FND_L.LOOKUP_TYPE = 'CP_EXECUTION_METHOD_CODE'
   AND A.USER_CONCURRENT_PROGRAM_NAME LIKE '%物料%'

 

/* 根据窗口名称查找关键字弹性域用到的表,列等信息*/ --
SELECT C.ID_FLEX_NAME,
       A.ID_FLEX_STRUCTURE_NAME,
       B.FORM_LEFT_PROMPT,
       C.APPLICATION_TABLE_NAME,
       B.APPLICATION_COLUMN_NAME,
       B.FLEX_VALUE_SET_ID
  FROM FND_ID_FLEX_STRUCTURES_VL A,
       FND_ID_FLEX_SEGMENTS_VL   B,
       FND_ID_FLEXS              C
 WHERE A.ID_FLEX_STRUCTURE_NAME = '帐户别名' --用你自己要查的代替,就是Form窗口的标题
   AND A.APPLICATION_ID = B.APPLICATION_ID
   AND A.ID_FLEX_CODE = B.ID_FLEX_CODE
   AND A.ID_FLEX_NUM = B.ID_FLEX_NUM
   AND A.APPLICATION_ID = C.APPLICATION_ID
   AND A.ID_FLEX_CODE = C.ID_FLEX_CODE
--根据上面FLEX_VALUE_SET_ID查弹性域的数据
  SELECT *
          FROM FND_FLEX_VALUES_VL T
         WHERE T.FLEX_VALUE_SET_ID = 1009677 -- FLEX_VALUE_SET_ID
        --具体某一数据
          SELECT *
                  FROM FND_FLEX_VALUES_VL T
                 WHERE T.FLEX_VALUE_SET_ID = 1009677
                   AND T.FLEX_VALUE = '720611'

 

/*根据描述性弹性域的标题查找描述性弹性域表和列*/ --
SELECT FND_DFV.TITLE,
       FND_DFV.DESCRIPTIVE_FLEXFIELD_NAME,
       FND_DFV.APPLICATION_TABLE_NAME,
       FND_DFU.APPLICATION_COLUMN_NAME,
       FND_DFU.FORM_LEFT_PROMPT,
       FND_DFU.FORM_ABOVE_PROMPT
  FROM FND_DESCRIPTIVE_FLEXS_VL    FND_DFV,
       FND_DESCR_FLEX_COL_USAGE_VL FND_DFU
 WHERE FND_DFV.TITLE = '物料' --如:物料
   AND FND_DFU.DESCRIPTIVE_FLEXFIELD_NAME =
       FND_DFV.DESCRIPTIVE_FLEXFIELD_NAME
 
 

采购订单配置和暂挂问题解决方法 

订单暂挂问题sql解决:

 

 

   ---查询请购单的状态

select

 *

from

 PO_REQUISITION_HEADERS_ALL por

where

por.requisition_header_id=63578

 

 

---修改请购单状态为未提交审批

update

 po_requisition_headers_all porh

set

 porh.authorization_status='INCOMPLETE'

where

 porh.requisition_header_id=63578

 

 

 

---查询采购单状态

select

 *

from

 po_headers_all aa

where

 aa.po_header_id in (20430,20431,20306)

 

 

---修改采购单状态为未审批

update

 po_headers_all aa

set

 aa.wf_item_type=null,

 aa.wf_item_key=null,

 aa.approved_flag=null,

 aa.authorization_status=null

where

 aa.po_header_id in (20430,20431)   

 

 

值集维护

 

------------得到值集数据
    -------------通过值集的名称得到值集的ID
    ---值集表: fnd_flex_value_sets
    ---值集ID: flex_value_set_id   
    ---值集名称:flex_value_set_name
     Select flex_value_set_id    From apps.fnd_flex_value_sets     Where flex_value_set_name='ML_INV_物料大类'
    
    ---通过值集ID得到值集列表
    ---值集列表:FND_FLEX_VALUES_VL
    ---列表ID:FLEX_VALUE
    ---列表名称:Description
    ---是否启用:ENABLED_FLAG   Y是,N否
    ---层次关系父:SUMMARY_FLAG   Y是,N否
    ---有效期始:START_DATE_ACTIVE
    ---有效期止:END_DATE_ACTIVE
    --排序:attribute50
     --俗称说明:attribute48
     Select A.FLEX_VALUE    flex_no
          , A.Description   flex_dsc
          ,attribute50,attribute48
          From apps.FND_FLEX_VALUES_VL a Where  a.FLEX_VALUE_SET_ID=1009627 And a.ENABLED_FLAG='Y'
          For Update
          ---调整排序
          Select flex_value,attribute50 From apps.FND_FLEX_VALUES a Where a.flex_value_set_id=1009627
--Order By a.flex_value
For Update
----值集代码修改
Select * From APPS.FND_FLEX_VALUES_TL T Where
T.FLEX_VALUE_ID=59940 and T.LANGUAGE = userenv('LANG')
For Update

posted on 2012-01-19 16:12  everkitty  阅读(651)  评论(0编辑  收藏  举报