壹佰网|ERP100 - 企业信息化知识门户

 找回密码
 注册

QQ登录

只需一步,快速开始

查看: 67409|回复: 185

ERP系统常用SQL集锦

    [复制链接]
发表于 2005/4/14 10:24:44 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。如果您注册时有任何问题请联系客服QQ: 83569622  。

您需要 登录 才可以下载或查看,没有帐号?注册

x
查看关键用户
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
;

评分

参与人数 1壹佰币 +1 收起 理由
tracywoo2005 + 1 谢谢分享(^_^)

查看全部评分

 楼主| 发表于 2005/4/14 10:26:03 | 显示全部楼层
得到员工的部门和成本中心
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编辑过]

 楼主| 发表于 2005/4/15 11:45:22 | 显示全部楼层
导出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');
 楼主| 发表于 2005/6/22 11:55:33 | 显示全部楼层

基于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

 楼主| 发表于 2005/6/29 10:51:31 | 显示全部楼层
有查询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
复制代码

 楼主| 发表于 2005/6/29 12:49:40 | 显示全部楼层
关联一下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;
 楼主| 发表于 2005/6/29 16:56:51 | 显示全部楼层
以下是引用Vicky在2005-6-29 15:50:38的发言:

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

那你自己关联一下表吧。应该可以搞定的。
 楼主| 发表于 2005/7/7 16:06:23 | 显示全部楼层
按照天,列出发票总额
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
 楼主| 发表于 2005/8/10 15:09:19 | 显示全部楼层
员工姓名及其部门信息表: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
 楼主| 发表于 2005/8/14 11:43:54 | 显示全部楼层
按照部门名称得到报销单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;
 楼主| 发表于 2005/8/19 08:08:04 | 显示全部楼层
得到公司组织架构的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编辑过]

hO0s0BGD.txt

3.32 KB, 下载次数: 219, 下载积分: 努力值 -5 点

ERP系统常用SQL集锦

 楼主| 发表于 2005/8/25 17:31:04 | 显示全部楼层
事务处理登记
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')
 楼主| 发表于 2005/12/12 15:08:36 | 显示全部楼层
-- 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;
 楼主| 发表于 2005/12/19 22:49:19 | 显示全部楼层
销售人员信息表
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
 楼主| 发表于 2006/3/7 15:23:37 | 显示全部楼层
参看系统请求的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;
 楼主| 发表于 2006/3/7 20:54:17 | 显示全部楼层
员工成本,采购订单,部门表(注一个员工如果他的成本有多条的话,那么就有多条记录)
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
;
 楼主| 发表于 2006/3/13 16:34:23 | 显示全部楼层
        --以下部分为没有导入成发票的报销单和导入成报销单又没有导入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'
 楼主| 发表于 2006/3/14 20:03:50 | 显示全部楼层
以下是引用junice1227在2006-3-14 19:35:49的发言:终于找到一个好的ERP论坛了
谢谢阿,欢迎你的到来
 楼主| 发表于 2006/9/12 00:32:15 | 显示全部楼层

AR客户联系人的SQL


  1. begin
  2. fnd_client_info.set_org_context(90);
  3. end;

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


复制代码
 楼主| 发表于 2006/10/12 20:05:21 | 显示全部楼层

查看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;
您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|Archiver|小黑屋|手机版|壹佰网 ERP100 ( 京ICP备12025635号 京ICP证120590号 )

Copyright © 2005-2012 北京海之大网络技术有限责任公司 服务器托管由互联互通
手机:13911575376
网站技术点击发送消息给对方83569622   广告&合作 点击发送消息给对方27675401   点击发送消息给对方634043306   咨询及人才点击发送消息给对方138011526

GMT+8, 2021/3/6 00:13 , Processed in 0.134863 second(s), 11 queries , Redis On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表