ERP系统常用SQL集锦

纵横四海

查看关键用户
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 < e.EFFECTIVE_END_DATE
           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 &gt; a.EFFECTIVE_START_DATE
           and sysdate &lt; a.EFFECTIVE_END_DATE) a,
       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 &gt; b.EFFECTIVE_START_DATE
   and sysdate &lt; b.EFFECTIVE_END_DATE
order by c.name

[此贴子已经被作者于2005-4-14 10:27:03编辑过]

纵横四海

导出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 &gt; to_date('20050101', 'YYYYMMDD')
   and t.GL_DATE &lt; to_date('20050331', 'YYYYMMDD');

纵横四海

这是一个统计某个会计科目在某个会计期内的费用合计，在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 < e.EFFECTIVE_END_DATE
     order by c.description, c.user_name, a.responsibility_id
   
    ;

客户电话

1. select tt.customer_name,
   
2. tt.customer_number,
   
3. tt.customer_id,
   
4. a.contact_id,
   
5. a.address_id,
   
6. a.customer_id,
   
7. a.last_name,
   
8. t.contact_point_type 联系方式,
   
9. t.phone_type 类型,
   
10. t.address_id,
    
11. t.contact_id,
    
12. t.phone_id,
    
13. t.phone_number from　apps.ar_contacts_v a,
    
14. apps.ra_PHONES t,
    
15. apps.ra_customers tt
    
16. where a.contact_id = t.contact_id
    
17. and a.customer_id = tt.customer_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 &gt; e.EFFECTIVE_START_DATE
   and sysdate &lt; e.EFFECTIVE_END_DATE
   and b.APPLICATION_ID = g.application_id
order by c.description, c.user_name, a.responsibility_id;

纵横四海

以下是引用Vicky在2005-6-29 15:50:38的发言：

楼主,我说的Fuction是指Menu下的Fuction,比如说:Invoice entry, Merge supplier, Define item cost等等.

那你自己关联一下表吧。应该可以搞定的。

纵横四海

按照天，列出发票总额
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 &gt;= to_date('20050101', 'YYYYMMDD')
   and a.trx_date &lt; to_date('20050701', 'YYYYMMDD')
   and a.org_id = 85  
group by a.trx_date

纵横四海

员工姓名及其部门信息表：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 &gt; 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(+)
;

[upload=txt]viewFile.asp?ID=223[/upload]

[此贴子已经被作者于2005-8-19 8:09:15编辑过]

纵横四海

事务处理登记
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 &gt;= to_date('20050101', 'YYYYMMDD')
      --and d.gl_date &lt; to_date('20050801', 'YYYYMMDD')
   and d.gl_date &gt;= to_date('20050701', 'YYYYMMDD')
   and d.gl_date &lt; to_date('20050801', 'YYYYMMDD')
order by to_char(d.gl_date, 'YYYY-MON')

纵横四海

-- Author  : Wanjun Hu  -- Created : 2005-12-12 14:59:54  -- Purpose : &cedil;ù&frac34;&Yacute;&raquo;á&frac14;&AElig;&AElig;&Uacute;,&sup1;&laquo;&Euml;&frac34;,&sup2;&iquest;&Atilde;&Aring;,&raquo;á&frac14;&AElig;&iquest;&AElig;&Auml;&iquest;&micro;&Atilde;&micro;&frac12;&AElig;&auml;&micro;±&AElig;&Uacute;·&cent;&Eacute;ú&Ouml;&micro;  function getNetBySegmentsAndPeriodName(p_period_name varchar2,                                         p_segment1    varchar2,                                         p_segment2    varchar2,                                         p_segment3    varchar2)    return number is    p_result number;  begin      select sum(gba.Period_Net_Dr - gba.Period_Net_Cr) a_net      into p_result      from apps.gl_balances          gba,           apps.gl_periods           gp,           apps.gl_code_combinations gcc     where gcc.code_combination_id = gba.code_combination_id       and gba.actual_flag = 'A'       and gba.period_year in (2004, 2005, 2006)       and gba.currency_code = 'CNY'       and gba.set_of_books_id = 1       and gp.period_year in (2004, 2005, 2006)       and gp.period_set_name = '########'       and gp.period_name = gba.period_name       and (gba.template_id is null or gba.template_id = 227)       and gba.period_name = p_period_name       and gcc.segment1 = p_segment1       and gcc.segment2 = p_segment2       and gcc.segment3 = p_segment3;      return(p_result);  end getNetBySegmentsAndPeriodName;

纵横四海

销售人员信息表
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 "&sup2;&Eacute;&sup1;&ordm;&para;&copy;&micro;&yen;&ETH;&Aring;&Iuml;&cent;",       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 &gt;=       (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 &lt;&gt; 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'

纵横四海

以下是引用junice1227在2006-3-14 19:35:49的发言：终于找到一个好的ERP论坛了
谢谢阿,欢迎你的到来

纵横四海

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. 
    

复制代码

纵横四海

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;