ERP系统常用SQL集锦

纵横四海

按照部门名称得到报销单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;

lyoffice

顶呀！[em01]

gsplage

Thank You!!!

zhuoxi

不错，得好好学学！

纵横四海

得到公司组织架构的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编辑过]

zhangyl052

进来一看，个个高手！惭愧！

纵横四海

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

nancy018

非常感謝樓主的無私奉獻

howell

出来的数据有部份这amount个字段值为NULL,且数据重复[em07]

howell

select to_char(d.gl_date, 'YYYY-MON') as yuefen,
       a.trx_number 发票号,
       e.name  发票类型,
       a.doc_sequence_value,
       c.customer_name 客户名称,
       c.customer_number 客户编码,
       a.trx_date,
       d.gl_date GL日期,
       b.sales_order 销售定单,
       b.description 物料描述,
       b.uom_code 物料单位,
       b.QUANTITY_CREDITED 退货数量,
       b.QUANTITY_INVOICED 销售数量,
       b.unit_selling_price 收入单价,
       b.gross_unit_selling_price 销售单价,
       b.revenue_amount 收入额,
       b.gross_extended_amount - b.revenue_amount 税额,
       b.gross_extended_amount 发票额,
       a.attribute3 发货方式
  from apps.ra_customer_trx_all          a,
       apps.ra_customer_trx_lines_all    b,
       apps.ra_customers                 c,
       apps.RA_CUST_TRX_LINE_GL_DIST_ALL d,
       apps.RA_CUST_TRX_TYPES_ALL        e
where a.org_id = 94 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 < to_date('20050801', 'YYYYMMDD')
       and d.gl_date >= to_date('20050726', 'YYYYMMDD') and
       d.gl_date <= to_date('20050825', 'YYYYMMDD') and
       b.revenue_amount is not null
order by to_char(d.gl_date, 'YYYY-MON'),a.trx_number[em01]

[此贴子已经被作者于2005-8-31 13:25:16编辑过]

smiszch

thanks

sophial_le

[em17]  Thanks !!

qqbaby1029

不錯[em07]

aatek

好东西

hjrnet

兄弟呀,你应该搞一个培训班才行呀,厉害呀

seven97

[em01][em01]呵呵！不断更新中！！支持！！

ZHANGIVAN

好，都是高手，正在学习中。。。。。

caidong888

好同志

lxszmylove

在哪里可以找到ORACLE ERP的资料

wlh_hong

辛苦了，谢谢，支持你！