ERP系统常用SQL集锦

http://www.erp100.com/forum.php?mod=viewthread&tid=80&extra=&authorid=8&page=1

查看关键用户
select distinct login_name
  from (select c.user_name           as login_name,
               d.full_name           as employee_name,
               f.name                as department_name,
               a.user_id             as user_id,
               a.responsibility_id   as responsibility_id,
               b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME
          from FND_USER_RESP_GROUPS         a,
               FND_RESPONSIBILITY_VL        b,
               fnd_user                     c,
               hr_employees                 d,
               per_assignments_f            e,
               hr_all_organization_units_tl f
         where a.user_id = c.user_id
           and c.employee_id = d.employee_id
           and c.employee_id = e.PERSON_ID
           and e.ORGANIZATION_ID = f.organization_id
           and a.responsibility_id = b.RESPONSIBILITY_ID
           and sysdate > e.EFFECTIVE_START_DATE
           and sysdate            and b.RESPONSIBILITY_NAME not in
               ('员工自助 (1)')
           order by c.description, c.user_name, a.responsibility_id) a
;

 

得到员工的部门和成本中心
select a.full_name as employee_name,
       c.name as hr_depart,
       cux_hr11_report.getDepartmentNameByName(a.full_name) as account_name
  from (select *
          from apps.PER_PEOPLE_f a
         where a.PERSON_TYPE_ID = 6
           and a.LAST_NAME not like '虚拟%'
           and sysdate > a.EFFECTIVE_START_DATE
           and sysdate        per_assignments_f b,
       hr_all_organization_units_tl c
where a.PERSON_ID = b.ASSIGNMENT_ID
   and b.ORGANIZATION_ID = c.organization_id
   and c.language = 'ZHS'
   and sysdate > b.EFFECTIVE_START_DATE
   and sysdate order by c.name

 

 

导出AR退款的SQL
begin
  fnd_client_info.set_org_context(85);
end;select tt.customer_id,
       tt.customer_name,
       tt.customer_number,
       tt.gl_date as shoukuan_date,
       t.GL_DATE as tuikuan_date,
       t.AMOUNT_APPLIED,
       t.TRX_NUMBER,
       tt.receipt_number
  from AR_RECEIVABLE_APPLICATIONS_V t, AR_CASH_RECEIPTS_V tt
where t.TRX_NUMBER = 'Receipt Write-off'
   and t.CASH_RECEIPT_ID = tt.cash_receipt_id
   and t.GL_DATE > to_date('20050101', 'YYYYMMDD')
   and t.GL_DATE

 

 

基于gl_balances 的部门费用SQL
这是一个统计某个会计科目在某个会计期内的费用合计，在SQL中有
   and b.segment2 = p_department_id
   and b.segment3 = p_account_id
这即是假设你的segment2 为部门段，segment3为会计科目
budget_version_id为预算的ID，可以用select * from gl.gl_budgets t确认相应的预算id
set_of_books_id 的值可以用     select * from  gl_sets_of_books确认
该SQL实际证明是完全可靠和可信赖的，我们的很多取值都用这个SQL。

select sum(a.period_net_dr - a.period_net_cr)
  from apps.gl_balances a, apps.gl_code_combinations b
where b.enabled_flag = 'Y'
   and a.set_of_books_id = 1
   and a.code_combination_id = b.code_combination_id
   and nvl(a.budget_version_id, 1022) = 1022
   AND (upper(a.ACTUAL_FLAG) = upper(p_money_type))
   AND trim(nvl(a.PERIOD_NAME, 'XXX')) =
       trim(to_char(to_date(2005|| '-' || 1, 'YYYY-MM'),
                    'MON-YY',
                    'NLS_DATE_LANGUAGE=American'))
   and b.segment2 = p_department_id
   and b.segment3 = p_account_id

有查询User具体权限的SQL吗?
这就是
    select c.user_name           as login_name,
           d.full_name           as employee_name,
           f.name                as department_name,
           a.user_id             as user_id,
           a.responsibility_id   as responsibility_id,
           b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME
      from FND_USER_RESP_GROUPS         a,
           FND_RESPONSIBILITY_VL        b,
           fnd_user                     c,
           hr_employees                 d,
           per_assignments_f            e,
           hr_all_organization_units_tl f
     where a.user_id = c.user_id
       and c.employee_id = d.employee_id
       and c.employee_id = e.PERSON_ID
       and e.ORGANIZATION_ID = f.organization_id
       and a.responsibility_id = b.RESPONSIBILITY_ID
       and sysdate > e.EFFECTIVE_START_DATE
       and sysdate      order by c.description, c.user_name, a.responsibility_id
  
    ;

关联一下fnd_application 表就可以了select c.user_name as login_name,
       d.full_name as employee_name,
       f.name as department_name,
       a.user_id as user_id,
       a.responsibility_id as responsibility_id,
       b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME,
       g.application_short_name
  from FND_USER_RESP_GROUPS         a,
       FND_RESPONSIBILITY_VL        b,
       fnd_user                     c,
       hr_employees                 d,
       per_assignments_f            e,
       hr_all_organization_units_tl f,
       fnd_application              g
where a.user_id = c.user_id
   and c.employee_id = d.employee_id
   and c.employee_id = e.PERSON_ID
   and e.ORGANIZATION_ID = f.organization_id
   and a.responsibility_id = b.RESPONSIBILITY_ID
   and sysdate > e.EFFECTIVE_START_DATE
   and sysdate    and b.APPLICATION_ID = g.application_id
order by c.description, c.user_name, a.responsibility_id;

 

按照天，列出发票总额
select a.trx_date,
       sum(b.unit_selling_price *
           NVL(b.QUANTITY_CREDITED, b.QUANTITY_INVOICED)) as amount
  from ra_customer_trx_all a, ra_customer_trx_lines_all b
where a.customer_trx_id = b.customer_trx_id
   and a.trx_date >= to_date('20050101', 'YYYYMMDD')
   and a.trx_date    and a.org_id = 85 
group by a.trx_date

 

/*120*240，创建于2011-6-8*/ var cpro_id = 'u501098';-员工姓名及其部门信息表：select a.LAST_NAME,
       a.PERSON_ID,
       a.SEX,
       a.EMAIL_ADDRESS,
       b.ORGANIZATION_ID,
       c.name
  from per_people_f a, PER_ASSIGNMENTS_F b, HR_ALL_ORGANIZATION_UNITS c
where a.PERSON_ID = b.person_id
   and sysdate between b.EFFECTIVE_START_DATE and b.EFFECTIVE_END_DATE
   and a.PERSON_TYPE_ID = 6
   and b.ORGANIZATION_ID = c.organization_id

 

按照部门名称得到报销单select e.name as department_name,
       c.last_name as employee_name,
       --d.PERSON_ID,
       --a.employee_id,
       a.invoice_num as expense_number, 
       b.ITEM_DESCRIPTION as expense_type,
       b.amount,
       b.ATTRIBUTE1,
       b.ATTRIBUTE2,
       b.ATTRIBUTE3,
       b.ATTRIBUTE4,
       b.JUSTIFICATION
  from ap_expense_report_headers_all a,
       ap_expense_report_lines_all   b,
       hr_employees                  c,
       per_assignments_f             d,
       hr_organization_units         e
where a.REPORT_HEADER_ID = b.REPORT_HEADER_ID
   and c.employee_id = d.PERSON_ID
   and a.employee_id = c.employee_id
   and d.ORGANIZATION_ID = e.organization_id
   and e.name = '部门名称
   and a.CREATION_DATE > to_date('20050715', 'YYYYMMDD')
   and (sysdate between d.EFFECTIVE_START_DATE and d.EFFECTIVE_END_DATE)
order by c.last_name, a.invoice_num;

 

得到公司组织架构的SQL：
create or replace view cux_org_level1 as
select a_pa.organization_id as org_id1,  a_pa.name as org_name1, a_ch.organization_id as org_id2, a_ch.name as org_name2
  from per_org_structure_elements t,
       hr_organization_units      a_pa,
       hr_organization_units      a_ch
where t.organization_id_parent = 0
   and a_pa.organization_id = t.organization_id_parent
   and a_ch.organization_id = t.organization_id_child;
create view cux_org_level2 as
select a_pa.organization_id as org_id1,  a_pa.name as org_name1, a_ch.organization_id as org_id2, a_ch.name as org_name2
   from per_org_structure_elements t,
       hr_organization_units      a_pa,
       hr_organization_units      a_ch
where t.organization_id_parent in
       (select t.organization_id_child
          from per_org_structure_elements t
         where t.organization_id_parent = 0)
   and a_pa.organization_id = t.organization_id_parent
   and a_ch.organization_id = t.organization_id_child;
  
  
  
   create view cux_org_level3 as
select a_pa.organization_id as org_id1,
       a_pa.name            as org_name1,
       a_ch.organization_id as org_id2,
       a_ch.name            as org_name2
  from per_org_structure_elements t,
       hr_organization_units      a_pa,
       hr_organization_units      a_ch
where t.organization_id_parent in
       (select t.organization_id_child
          from per_org_structure_elements t
         where t.organization_id_parent in
               (select t.organization_id_child
                  from per_org_structure_elements t
                 where t.organization_id_parent = 0))
   and a_pa.organization_id = t.organization_id_parent
   and a_ch.organization_id = t.organization_id_child;
  
  
  
      create view cux_org_level4 as
select a_pa.organization_id as org_id1,
       a_pa.name            as org_name1,
       a_ch.organization_id as org_id2,
       a_ch.name            as org_name2
  from per_org_structure_elements t,
       hr_organization_units      a_pa,
       hr_organization_units      a_ch
where t.organization_id_parent in
       (select t.organization_id_child
          from per_org_structure_elements t
         where t.organization_id_parent in
               (select t.organization_id_child
                  from per_org_structure_elements t
                 where t.organization_id_parent in
                       (select t.organization_id_child
                          from per_org_structure_elements t
                         where t.organization_id_parent = 0)))
   and a_pa.organization_id = t.organization_id_parent
   and a_ch.organization_id = t.organization_id_child;
  
select t.organization_id_parent, t.organization_id_child
  from per_org_structure_elements t
where t.organization_id_parent in
       (select t.organization_id_child
          from per_org_structure_elements t
         where t.organization_id_parent = 0);
select t1.org_name2,
       t2.org_name2,
       t3.org_name2,
       t4.org_name2    
  from cux_org_level1 t1,
       cux_org_level2 t2,
       cux_org_level3 t3,
       cux_org_level4 t4
where t1.org_id2 = t2.org_id1(+)
   and t2.org_id2 = t3.org_id1(+)
   and t3.org_id2 = t4.org_id1(+)
;

 

事务处理登记
select to_char(d.gl_date, 'YYYY-MON') as yuefen,
       a.trx_number,
       e.name as trx_type,
       a.doc_sequence_value,
       c.customer_name,
       c.customer_number,
       a.trx_date,
       d.gl_date,
       b.unit_selling_price * NVL(b.QUANTITY_CREDITED, b.QUANTITY_INVOICED) as amount,
       a.attribute3
  from ra_customer_trx_all          a,
       ra_customer_trx_lines_all    b,
       ra_customers                 c,
       RA_CUST_TRX_LINE_GL_DIST_ALL d,
       RA_CUST_TRX_TYPES_ALL        e
where a.org_id = 90
   and a.customer_trx_id = b.customer_trx_id
   and a.cust_trx_type_id=e.cust_trx_type_id
   and a.bill_to_customer_id = c.customer_id
   and d.customer_trx_line_id = b.customer_trx_line_id
      --and d.gl_date >= to_date('20050101', 'YYYYMMDD')
      --and d.gl_date    and d.gl_date >= to_date('20050701', 'YYYYMMDD')
   and d.gl_date order by to_char(d.gl_date, 'YYYY-MON')；

 

销售人员信息表
select JRS.SALESREP_ID, JRS.NAME, hla.description  from jtf_rs_srp_vl jrs, per_all_assignments_f pasf, HR_LOCATIONS_ALL hla
where jrs.PERSON_ID = pasf.person_id   and SYSDATE between pasf.effective_start_date and       pasf.effective_end_date   and pasf.location_id = hla.location_id

参看系统请求的SQL
select t.CONCURRENT_PROGRAM_NAME,       t.EXECUTABLE_ID,       tt.EXECUTABLE_NAME,       tt.EXECUTION_FILE_NAME  from FND_CONCURRENT_PROGRAMS_VL t, FND_EXECUTABLES_FORM_V tt where upper(t.CONCURRENT_PROGRAM_NAME) like '%CUX%'   and t.EXECUTABLE_ID = tt.EXECUTABLE_ID order by tt.EXECUTION_FILE_NAME;

 

员工成本,采购订单,部门表(注一个员工如果他的成本有多条的话,那么就有多条记录)
select d.ASSIGNMENT_ID,       a.person_id as person_id,       a.last_name as last_name,       --cux_hr_common.getDepartmentNameByPersonID(a.person_id) as hr_department,       cux_hr_common.getDepartmentIDByPersonID(a.person_id) as hr_department,       e.segment1 || '.' || e.segment2 || '.' || e.segment3 || '.' ||       e.segment4 || '.' || e.segment5 || '.' || e.segment6 || '.' ||       e.segment7 || '.' || e.segment8 "²É¹º¶©µ¥ÐÅÏ¢",       f.PROPORTION,       f.CONCATENATED_SEGMENTS  from PER_PEOPLE_f a,       PER_ASSIGNMENTS_F d,       gl_code_combinations e,       (SELECT PAY.ROWID ROW_ID,               PAY.COST_ALLOCATION_ID,               PAY.EFFECTIVE_START_DATE,               PAY.EFFECTIVE_END_DATE,               PAY.BUSINESS_GROUP_ID,               PAY.COST_ALLOCATION_KEYFLEX_ID,               PAY.ASSIGNMENT_ID,               PAY.PROPORTION,               PAY.REQUEST_ID,               PAY.PROGRAM_APPLICATION_ID,               PAY.PROGRAM_ID,               PAY.PROGRAM_UPDATE_DATE,               PAY.LAST_UPDATE_DATE,               PAY.LAST_UPDATED_BY,               PAY.LAST_UPDATE_LOGIN,               PAY.CREATED_BY,               PAY.CREATION_DATE,               PCAF.CONCATENATED_SEGMENTS          FROM PAY_COST_ALLOCATION_KEYFLEX PCAF, PAY_COST_ALLOCATIONS_F PAY         WHERE PCAF.COST_ALLOCATION_KEYFLEX_ID(+) =               PAY.COST_ALLOCATION_KEYFLEX_ID) f where a.PERSON_ID = d.PERSON_ID   and a.person_type_id = 6   and d.EFFECTIVE_START_DATE >=       (select max(EFFECTIVE_START_DATE)          from PER_ASSIGNMENTS_F         where person_id = d.PERSON_ID)   and sysdate between d.EFFECTIVE_START_DATE and d.EFFECTIVE_END_DATE   and sysdate between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE   and sysdate between f.EFFECTIVE_START_DATE and f.EFFECTIVE_END_DATE   and e.code_combination_id = d.DEFAULT_CODE_COMB_ID   and d.ASSIGNMENT_ID = f.ASSIGNMENT_ID-- and d.ASSIGNMENT_ID = 67

  --以下部分为没有导入成发票的报销单和导入成报销单又没有导入gl的报销单        select 1 as set_of_books_id,               b.code_combination_id,               'CNY' currency_code,               to_char(a.creation_date, 'MON-YY'),               0 a_1,               0 a_2,               0 b_1,               0 b_2,               b.amount unpost_1,               0 unpost_2,               0,               0          from apps.ap_expense_report_headers_all a,               apps.ap_expense_report_lines_all   b,               apps.per_assignments_f             c         where a.report_header_id = b.report_header_id           and a.employee_id = c.PERSON_ID           and a.creation_date between c.EFFECTIVE_START_DATE and               c.EFFECTIVE_END_DATE           and a.expense_current_approver_id <> c.SUPERVISOR_ID           and a.employee_id = 285           and (select count(*)                  from ap_invoices_all t                 where t.invoice_num = a.invoice_num) = 0        union all        select 1 as set_of_books_id,               a.dist_code_combination_id,               'CNY' currency_code,               a.period_name,               0 a_1,               0 a_2,               0 b_1,               0 b_2,               a.amount unpost_1,               0 unpost_2,               0,               0          from apps.AP_INVOICE_DISTRIBUTIONS_all a         where a.posted_flag = 'N'

 

1.begin

2.fnd_client_info.set_org_context(90);

3.end;

4.

5.select a.customer_id,

6.       d.address_id,

7.       a.customer_number 客户编号,

8.       a.customer_name　客户名称,

9.       a.attribute1　与公司关系,

10.       a.attribute2　渠道,

11.       a.attribute3　行业,

12.       b.description　一级行业分类,

13.       c.description　二级行业分类,

14.       d.status　状态,

15.       d.country 国家,

16.       d.address1 地址,

17.       d.address2 地址2,

18.       d.address3 地址3,

19.       d.address4 地址4,

20.       e.last_name 联系人,

21.       f.phone_type 联系方式,

22.       f.area_code 地区代码,

23.       f.phone_number 电话号码

24.  from apps.ra_customers a,

25.       (select t.FLEX_VALUE, t.DESCRIPTION

26.          from apps.FND_FLEX_VALUES_VL t

27.         where t.FLEX_VALUE_SET_ID = 1009676) b,

28.       (select t.FLEX_VALUE, t.DESCRIPTION

29.          from apps.FND_FLEX_VALUES_VL t

30.         where t.FLEX_VALUE_SET_ID = 1009735) c,

31.       apps.AR_ADDRESSES_V d,

32.       apps.ar_contacts_v e,

33.       apps.ra_PHONES f

34.where a.customer_number like 'AD_%'

35.   and b.flex_value(+) = a.attribute4

36.   and c.flex_value(+) = a.attribute5

37.   and d.customer_id = a.customer_id

38.   and e.address_id = d.address_id

39.   and e.contact_id = f.contact_id

查看AP发票的撤销人
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;

 

更改AR发票会计科目
/*120*240，创建于2011-6-8*/ var cpro_id = 'u501098';-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;

 

请求正在执行的sql
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;

 

select a.LAST_NAME 员工姓名,

      a.PERSON_ID 员工ID,

      a.SEX 性别,

     a.EMAIL_ADDRESS　电子邮件,

       b.ORGANIZATION_ID,

       aa.LAST_NAME　主管姓名,

       aa.EMAIL_ADDRESS　主管email

  from per_people_f a, PER_ASSIGNMENTS_F b, per_people_f aa

where a.PERSON_ID = b.person_id

   and sysdate between b.EFFECTIVE_START_DATE and b.EFFECTIVE_END_DATE

   and sysdate between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE

   and sysdate between aa.EFFECTIVE_START_DATE and aa.EFFECTIVE_END_DATE
 and a.PERSON_TYPE_ID <> 9

   and b.SUPERVISOR_ID = aa.PERSON_ID

select a.USER_PROFILE_OPTION_NAME 配置文件名,

       c.user_name　用户名,

      b.profile_option_value　配置文件值,

       a.PROFILE_OPTION_ID　,

       c.user_id,

      c.rowid,

       b.rowid

  from FND_PROFILE_OPTIONS_VL a, fnd_profile_option_values b, fnd_user c

where a.USER_PROFILE_OPTION_NAME = 'FND：诊断'

   and a.PROFILE_OPTION_ID = b.profile_option_id

   and c.user_id = b.level_value

   and b.level_id = 10004　--10004为用户层

select t.message_type,

       t.begin_date,

      t.responder,

       t.from_user,

      t.to_user,

       t.subject

  from wf_notifications t

where t.respOnder= 'SYSADMIN'

  and t.recipient_role <> t.responder

  and t.begin_date between to_date('20070101', 'YYYYMMDD') and

      to_date('20070331', 'YYYYMMDD')