销售员= 资源+员工的分配+OU
职责:CRM 资源管理器
1. 资源信息:
SELECT * FROM jtf_rs_resource_extns --资源信息
SELECT * FROM jtf_rs_resource_extns_tl --资源信息的多语言表
2. 真正的销售员的表
SELECT * FROM jtf_rs_salesreps;
3. 销售员的视图
SELECT * FROM ra_salesreps;
4. 具有MOAC的SYNONYM-JTF_RS_SALESREPS_MO_V(jtf_rs_salesreps的同义词,加了一个POLICY,实现MOAC)
SELECT * FROM JTF_RS_SALESREPS_MO_V;
————————————————
版权声明:本文为CSDN博主「hutianci」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/hutianci/article/details/84183998
后面为自己的发现
从合同关联销售人员
select papf.last_name
from okc_contacts_v okc,
jtf.jtf_rs_salesreps rs,
jtf_rs_resource_extns_tl jt,
okc_k_party_roles_v okv,
jtf_rs_resource_extns je,
per_all_people_f papf
where rs.salesrep_id = okc.OBJECT1_ID1
and okv.ID = okc.CPL_ID
and okv.RLE_CODE = 'SHIP_FROM'
and okv.CHR_ID = p_header_id----合同ID参数
and rs.person_id = papf.person_id
and nvl(papf.effective_start_date, sysdate - 1) <= sysdate
and nvl(papf.effective_end_date, sysdate + 1) >= sysdate
and rs.resource_id = jt.resource_id
and jt.resource_id = je.resource_id
and jt.language = userenv('LANG')
and je.category = jt.category
--销售订单关联销售人员
用以下关系
Oe_Order_Lines_All.salesrep_id = Jtf_Rs_Salesreps.salesrep_id
AND Jtf_Rs_Salesreps.Person_Id = Per_All_People_f.Person_Id
然后查询人员表名称,记得Per_All_People_f的有效日期
--销售订单信息
SELECT Oola.Ship_From_Org_Id
,Ood.Organization_Code
,ooha.order_number
,ooha.order_type_id
,ottl.name
,oola.line_number
,msib.segment1
,oola.schedule_status_code
,oola.flow_status_code
--,oola.li --发运行状态
,oola.salesrep_id
,papf.last_name
,oola.visible_demand_flag
,oola.schedule_ship_date
,oola.demand_class_code
FROM Oe_Order_Headers_All Ooha
,Oe_Order_Lines_All Oola
,org_organization_definitions Ood
,Oe_Transaction_Types_Tl ottl
,mtl_system_items_b msib
,Jtf_Rs_Salesreps jrs
,Per_All_People_f papf
WHERE Ooha.Header_Id = Oola.Header_Id
AND Oola.Ship_From_Org_Id = Ood.Organization_Id
and ooha.order_type_id = ottl.Transaction_Type_Id
AND ottl.Language = Userenv('LANG')
and oola.inventory_item_id = msib.inventory_item_id
and oola.ship_from_org_id = msib.organization_id
and oola.salesrep_id = jrs.salesrep_id
AND jrs.Person_Id = Papf.Person_Id
AND Nvl(Papf.Effective_Start_Date, SYSDATE - 1) <= SYSDATE
AND Nvl(Papf.Effective_End_Date, SYSDATE + 1) >= SYSDATE
and ooha.header_id = 3714085
and rownum <= 5;