订单信息表

纵横四海

select a.order_number || '' as order_number,
       a.cust_po_number,
       a.sold_to_org_id as customer_id,
       a.salesrep_id as salesrep_id,
       b.name as salesrep_name,
       c.customer_name,
       c.customer_number,
       (select sum(nvl(t.ordered_quantity * t.unit_selling_price, 0))
          from apps.oe_order_lines_all t
         where t.header_id = a.header_id) payment_amount,
      
       aa.ordered_item,
       aa.attribute15,
       aa.attribute16

  from ont.oe_order_headers_all a,
       ont.oe_order_lines_all   aa,
       jtf.JTF_RS_SALESREPS     b,
       apps.ra_customers        c
where a.salesrep_id = b.salesrep_id(+)
   and a.sold_to_org_id = c.customer_id(+)
   and a.org_id = 85
   and aa.header_id = a.header_id
   and a.BOOKED_FLAG = 'Y'
   AND a.CANCELLED_FLAG = 'N'
   AND a.OPEN_FLAG = 'Y'
   AND a.FLOW_STATUS_CODE = 'BOOKED'
order by a.order_number, aa.line_id

jxywlee

呵呵，偶不懂，偶是来骗分的。

maker

邊頂邊學了....

kevin_hu

一定要顶

ZHANGIVAN

顶～～～～

gaoyiming

我在用 这个 salesERP_id 这个字段的时候 郁闷了

纵横四海

原帖由 gaoyiming 于 2006-12-16 15:01 发表
我在用 这个 saleserp_id 这个字段的时候 郁闷了

销售人员：oe_order_headers_all. salesrep_id关联jtf.JTF_RS_SALESREPS.salesrep_id,然后在jtf.JTF_RS_SALESREPS表中得到销售人员的person_id,然后利用如下的SQL取上下级关系．
代码如下：
select t.salesrep_id,t.person_id from jtf.JTF_RS_SALESREPS t where t.person_id is not null
取上下级的SQL:

1. 
   
2. select a.LAST_NAME 员工姓名,
   
3.        a.PERSON_ID 员工ID,
   
4.        a.SEX 性别,
   
5.        a.EMAIL_ADDRESS　电子邮件,
   
6.        b.ORGANIZATION_ID,
   
7.        aa.LAST_NAME　主管姓名,
   
8.        aa.EMAIL_ADDRESS　主管email
   
9.   from per_people_f a, PER_ASSIGNMENTS_F b, per_people_f aa
   
10. where a.PERSON_ID = b.person_id
    
11.    and sysdate between b.EFFECTIVE_START_DATE and b.EFFECTIVE_END_DATE
    
12.    and sysdate between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE
    
13.    and sysdate between aa.EFFECTIVE_START_DATE and aa.EFFECTIVE_END_DATE
    
14.    and a.PERSON_TYPE_ID <> 9
    
15.    and b.SUPERVISOR_ID = aa.PERSON_ID
    

复制代码

gaoyiming

特别感谢  

huisir

受用了