ERP系统常用SQL集锦

dev_student

有人回答问题吗

garfield_like

谢谢分享，楼主好人吖～～～

纵横四海

1. 
   
2. begin
   
3. fnd_client_info.set_org_context(90);
   
4. end;
   
5. 
   
6. select a.customer_id,
   
7.        d.address_id,
   
8.        a.customer_number 客户编号,
   
9.        a.customer_name　客户名称,
   
10.        a.attribute1　与公司关系,
    
11.        a.attribute2　渠道,
    
12.        a.attribute3　行业,
    
13.        b.description　一级行业分类,
    
14.        c.description　二级行业分类,
    
15.        d.status　状态,
    
16.        d.country 国家,
    
17.        d.address1 地址,
    
18.        d.address2 地址2,
    
19.        d.address3 地址3,
    
20.        d.address4 地址4,
    
21.        e.last_name 联系人,
    
22.        f.phone_type 联系方式,
    
23.        f.area_code 地区代码,
    
24.        f.phone_number 电话号码
    
25.   from apps.ra_customers a,
    
26.        (select t.FLEX_VALUE, t.DESCRIPTION
    
27.           from apps.FND_FLEX_VALUES_VL t
    
28.          where t.FLEX_VALUE_SET_ID = 1009676) b,
    
29.        (select t.FLEX_VALUE, t.DESCRIPTION
    
30.           from apps.FND_FLEX_VALUES_VL t
    
31.          where t.FLEX_VALUE_SET_ID = 1009735) c,
    
32.        apps.AR_ADDRESSES_V d,
    
33.        apps.ar_contacts_v e,
    
34.        apps.ra_PHONES f
    
35. where a.customer_number like 'AD_%'
    
36.    and b.flex_value(+) = a.attribute4
    
37.    and c.flex_value(+) = a.attribute5
    
38.    and d.customer_id = a.customer_id
    
39.    and e.address_id = d.address_id
    
40.    and e.contact_id = f.contact_id
    
41. 
    
42. 
    

复制代码

sunweijie22

不顶对不起党和人民!

laozhu

不错的东东..收藏了... 我也发几个..

FIND JOBS
col os form A6
col program form A50
set pages 200
set lines 200
col time form 9999
col "Req Id" form 9999999
col "Prg Id" form 999999
set recsep off
select a.request_id "Req Id",a.concurrent_program_id "Prg Id"
      ,a.phase_code,b.os_process_id "OS"
        ,request_date,actual_start_date
        ,actual_completion_date
      ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
      ,c.concurrent_program_name||' - '||
       c.concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
    ,APPLSYS.fnd_concurrent_programs c
where a.controlling_manager = b.concurrent_process_id
  and a.concurrent_program_id = c.concurrent_program_id
  and a.program_application_id = c.application_id
  and a.request_id = &req_id
--and a.phase_code in ('R','T')
order by 5
/
FIND OPEN CURSOR

set verify off

accept sid      prompt 'Please enter the sid        : '

select /*+ ordered */
       a.sid, b.first_load_time, b.executions, b.disk_reads, b.buffer_gets, b.sql_text
from v$open_cursor a,
     v$sqlarea b
where a.sid = &sid
  and a.hash_value = b.hash_value
  and a.address = b.address
order by b.executions desc

纵横四海

select t.invoice_num, t.cancelled_by, t.cancelled_by_display
  from ap_invoices_v t
where t.gl_date between to_date('20060701', 'YYYYMMDD') and
       to_date('20060930', 'YYYYMMDD')
   and t.cancelled_by is not null;

begin
  fnd_client_info.set_org_context(560);
end;

wei913yi

请问有有关hrms的相关文档吗?
有的话共享下哟 ,非常感谢也!

纵横四海

update ra_cust_trx_line_gl_dist_all t
   set t.code_combination_id = 374510
where t.customer_trx_id in
       (select customer_trx_id
          from RA_CUSTOMER_TRX_ALL t
         where t.trx_number >= 'SFQC20061001001174'
           and t.trx_number <= 'SFQC20061001012968')
   and t.customer_trx_line_id is not null;

xzh709

个个是高手

sunjinhai

经典语句啊

chinayokel

请问哪位有Knowledge Xpert for PLSQL的KEY，能否分享一下。

谢谢

纵横四海

SELECT to_char(s.sid) || ',' || to_char(s.serial#), sql_text
  FROM applsys.fnd_concurrent_requests r,
       v$process                       p,
       v$session                       s,
       v$sqltext_with_newlines         sqlt
WHERE r.oracle_process_id = p.spid
   AND p.addr = s.paddr(+)
   AND s.sql_address = sqlt.address(+)
   AND s.sql_hash_value = sqlt.hash_value(+)
   AND r.request_id = 2641173
ORDER BY piece;

goodqin

怎样设置把物料由IDM设成EXPENSE呢？

clxclxclx

沒有理由不學啊!沒有理由不進步啊!感謝各位。

jianpenggu

非常感謝樓主

纵横四海

1. 
   
2. select a.LAST_NAME 员工姓名,
   
3.        a.PERSON_ID 员工ID,
   
4.        a.SEX 性别,
   
5.        a.EMAIL_ADDRESS　电子邮件,
   
6.        b.ORGANIZATION_ID,
   
7.        aa.LAST_NAME　主管姓名,
   
8.        aa.EMAIL_ADDRESS　主管email
   
9.   from per_people_f a, PER_ASSIGNMENTS_F b, per_people_f aa
   
10. where a.PERSON_ID = b.person_id
    
11.    and sysdate between b.EFFECTIVE_START_DATE and b.EFFECTIVE_END_DATE
    
12.    and sysdate between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE
    
13.    and sysdate between aa.EFFECTIVE_START_DATE and aa.EFFECTIVE_END_DATE
    
14.    and a.PERSON_TYPE_ID <> 9
    
15.    and b.SUPERVISOR_ID = aa.PERSON_ID
    
16. 
    

复制代码

jennifer_zhao

大家好，新手报到

kikima

szjwm

四海真的是太好了！

nathan

我想知道在某個reponsibility底下的request_group有什麼report！
請問有誰知道該怎麼抓呢？