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');

Tigerwang

兄弟,你太无私了.非顶不可

纵横四海

这是一个统计某个会计科目在某个会计期内的费用合计，在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

Vicky

有查询User具体权限的SQL吗?(具体到Function)

纵横四海

有查询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

复制代码

Vicky

多谢楼主,不过这只是精确到Responsibility这一层的(我已有了),现在我需要精确到Function这一层,即需要知道每个具体的操作权限,有吗?

纵横四海

关联一下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

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

纵横四海

以下是引用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

zhuoxi

果然很无私啊，呵呵

daybreak

不错，不错。

crach

very good

PL_SQLer

好...支持楼主!

纵横四海

员工姓名及其部门信息表：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

howell

可惜没上HR模块

howell

加一个,七层BOM,
select xye.*,
       xy7.item1 item7,
       xy7.item_desc1 item_desc7,
       xy7.item_type1 item_type7,
       xy7.l1 l7,
       xy7.compqty1*xye.compqty6 compqty7,
       xy7.sub1 sub7
from (select msib1.segment1 item,
             msib1.description item_desc,
             msib.segment1 item1,
             msib.description item_desc1,
             msib.item_type item_type1,
             bic.item_num  l1,
             bic.component_quantity compqty1,
             bic.supply_subinventory sub1
        from BOM.bom_bill_of_materials  bbm,
             BOM.bom_inventory_components bic,
             inv.mtl_system_items_b msib,
             inv.mtl_system_items_b msib1
             where  bbm.bill_sequence_id=bic.bill_sequence_id
               and  bbm.assembly_item_id=msib1.inventory_item_id
               and  bic.component_item_id=msib.inventory_item_id
          order by  item,l1) xy7,
          (select
            xyd.*,
            xy6.item1 item6,
            xy6.item_desc1 item_desc6,
            xy6.item_type1 item_type6,
            xy6.l1 l6,
            xy6.compqty1*xyd.compqty5 compqty6,
            xy6.sub1 sub6
           from
             (select
               msib1.segment1 item,
               msib1.description item_desc,
               msib.segment1 item1,
               msib.item_type item_type1,
               msib.description item_desc1,
               bic.item_num  l1,
               bic.component_quantity compqty1,
               Bic.supply_subinventory sub1
              from
               bom.bom_bill_of_materials  bbm,
               bom.bom_inventory_components bic,
               inv.mtl_system_items_b msib,
               inv.mtl_system_items_b msib1
              where      
                    bbm.bill_sequence_id=bic.bill_sequence_id
               and  bbm.assembly_item_id=msib1.inventory_item_id
               and  bic.component_item_id=msib.inventory_item_id
          order by  item,l1)xy6,
                  (select
                        xyc.*,
                        xy5.item1 item5,
                        xy5.item_desc1 item_desc5,
                        xy5.item_type1 item_type5,
                        xy5.l1 l5,
                        xy5.compqty1*xyc.compqty4 compqty5,
                        xy5.sub1 sub5
                   from (select  msib1.segment1 item,
                                 msib1.description item_desc,
                                 msib.segment1 item1,
                                 msib.description item_desc1,
                                 msib.item_type item_type1,
                                 bic.item_num  l1,
                                 bic.component_quantity compqty1,
                                 bic.supply_subinventory sub1
                           from  bom.bom_bill_of_materials  bbm,
                                 bom.bom_inventory_components bic,
                                 inv.mtl_system_items_b msib,
                                 inv.mtl_system_items_b msib1
       where   bbm.bill_sequence_id=bic.bill_sequence_id
         and  bbm.assembly_item_id=msib1.inventory_item_id
         and  bic.component_item_id=msib.inventory_item_id
    order by  item,l1) xy5,
             (select xyb.*,
                     xy4.item1 item4,
                     xy4.item_desc1 item_desc4,
                     xy4.item_type1 item_type4,
                     xy4.l1 l4,
                     xy4.compqty1*xyb.compqty3 compqty4,
                     xy4.sub1 sub4
               from
                    (select msib1.segment1 item,
                            msib1.description item_desc,
                            msib.segment1 item1,
                            msib.description item_desc1,
                            msib.item_type item_type1,
                            bic.item_num  l1,
                            bic.component_quantity compqty1,
                            bic.supply_subinventory sub1
                       from bom.bom_bill_of_materials  bbm
                            ,bom.bom_inventory_components bic
                            ,inv.mtl_system_items_b msib
                            ,inv.mtl_system_items_b msib1
                      where bbm.bill_sequence_id=bic.bill_sequence_id
                        and bbm.assembly_item_id=msib1.inventory_item_id
                        and bic.component_item_id=msib.inventory_item_id
                        and bbm.alternate_bom_designator is  null
                   order by item,l1) xy4,
                            (select xy2.*,
                                    xy3.item1 item3,
                                    xy3.item_desc1 item_desc3,
                                    xy3.item_type1 item_type3,
                                    xy3.l1 l3,
                                    xy3.compqty1*xy2.compqty2 compqty3,
                                    xy3.sub1 sub3
      
                              from (select msib1.segment1 item,
                                           msib1.description item_desc,
                                           msib.segment1 item1,
                                           msib.description item_desc1,
                                           msib.item_type item_type1,
                                           bic.item_num  l1,
                                           bic.component_quantity compqty1,
                                           bic.supply_subinventory sub1
                                      from bom.bom_bill_of_materials  bbm
                                           ,bom.bom_inventory_components bic
                                           ,inv.mtl_system_items_b msib
                                           ,inv.mtl_system_items_b msib1
                                     where bbm.bill_sequence_id=bic.bill_sequence_id
                                      and  bbm.assembly_item_id=msib1.inventory_item_id
                                      and  bic.component_item_id=msib.inventory_item_id
                                      and  bbm.alternate_bom_designator is  null
                                 order by  item,l1) xy3,
                                           (select xy.*,
                                                   xy1.item1 item2,
                                                   xy1.item_desc1 item_desc2,
                                                   xy1.item_type1 item_type2,
                                                   xy1.l1 l2,
                                                   xy1.compqty1*xy.compqty1 compqty2,
                                                   xy1.sub1 sub2
                                              from
                                                   (select msib1.segment1 item,
                                                    msib1.description item_desc,
                                                    msib.segment1 item1,
                                                    msib.description item_desc1,
                                                    msib.item_type item_type1,
                                                    bic.item_num  l1,
                                                    bic.component_quantity compqty1,
                                                    bic.supply_subinventory sub1
                                              from  bom.bom_bill_of_materials  bbm
                                                    ,bom.bom_inventory_components bic
                                                    ,inv.mtl_system_items_b msib
                                                    ,inv.mtl_system_items_b msib1
                                             where  bbm.bill_sequence_id=bic.bill_sequence_id
                                               and  bbm.assembly_item_id=msib1.inventory_item_id
                                               and  bic.component_item_id=msib.inventory_item_id
                       
                                               and  bbm.alternate_bom_designator is null
                                          order by  item,l1) xy,
                                                           (select msib1.segment1 item,
              msib1.description item_desc,
              msib.segment1 item1,
              msib.description item_desc1,
              msib.item_type item_type1,
              bic.item_num  l1,
              bic.component_quantity compqty1,
              bic.supply_subinventory sub1
             from bom.bom_bill_of_materials  bbm
            ,bom.bom_inventory_components bic
            ,inv.mtl_system_items_b msib
            ,inv.mtl_system_items_b msib1
             where      bbm.bill_sequence_id=bic.bill_sequence_id
                        and  bbm.assembly_item_id=msib1.inventory_item_id
                       and  bic.component_item_id=msib.inventory_item_id
                       and bbm.alternate_bom_designator is null
             order by item,l1) xy1
where   
       xy1.item(+)=xy.item1
     
order by xy.item,xy.l1) xy2
where
xy3.item(+)=xy2.item2) xyb
where xy4.item(+)=xyb.item3) xyc
where xy5.item(+)=xyc.item4) xyd
where xy6.item(+)=xyd.item5) xye
where xy7.item(+)=xye.item6
and xye.item like'XXX%'

and length(xye.item)=16
order by 1

[此贴子已经被作者于2005-8-15 16:23:06编辑过]

wandser

thanks! good!