select per.party_id,per.person_id sales_resp_id,per.last_name sales_resp_name,per.full_name,fu.user_id
from
(--查询有效的用户
select per.party_id, per.person_id,per.last_name,per.full_name
from PER_ALL_PEOPLE_F per
where sysdate between per.effective_start_date and per.effective_end_date
) per,
(--取有效的登陆用户
select person_party_id,user_id,user_name from FND_USER
where end_date is null and 1= case when user_name='SIE_CS' then 0 else 1 end --修改用户不取 SIE 因为对应同一个管理员会有person_party_id记录重复
) fu
where per.party_id=fu.person_party_id(+)
--=======================
select sehd.person_id,sehd.last_name, fv.dept_id,sehd.dept_name
from (
select papv.person_id, papv.last_name,ppdv.dept_name
from
(--用户表
select pap.person_id,pap.last_name from PER_ALL_PEOPLE_F pap
where SYSDATE BETWEEN nvl(pap.EFFECTIVE_START_DATE,sysdate) AND nvl(pap.EFFECTIVE_END_DATE,sysdate)
) papv,
(--用户职位表
select paaf.person_id,paaf.position_id
from PER_ALL_ASSIGNMENTS_F paaf
where SYSDATE BETWEEN nvl(paaf.EFFECTIVE_START_DATE,sysdate) AND nvl(paaf.EFFECTIVE_END_DATE,sysdate)
) paafv,
(--职位与职位定义表
select papos.position_id,papos.position_definition_id
from PER_ALL_POSITIONS papos
) paposv,
(--职位定义表
select ppd.position_definition_id,ppd.segment1 dept_name from PER_POSITION_DEFINITIONS ppd
)ppdv
where papv.person_id=paafv.person_id(+)
and paafv.position_id=paposv.position_id(+)
and paposv.position_definition_id=ppdv.position_definition_id(+)
) sehd,
(
select dept_name,dept_id
from(
select fvv.FLEX_VALUE dept_name,fvv.FLEX_VALUE_ID dept_id
,row_number()
over(PARTITION BY fvv.FLEX_VALUE ORDER BY fvv.FLEX_VALUE_ID ) rn
from FND_FLEX_VALUES_VL fvv
where fvv.FLEX_VALUE_SET_ID=1007707 and fvv.ENABLED_FLAG='Y'
) fvv
) fv
where sehd.dept_name=fv.dept_name(+)
and sehd.dept_name is not null
