select distinct org.organization_id dpt_id,
org.name dpt_name,
pas.assignment_id,
hr_person_type_usage_info.get_user_person_type(pers.effective_end_date,pers.person_id) as PERS_TYPE,
pas.person_id,
pers.last_name,
pers.first_name,
pers.employee_number,
-- pas.effective_start_date,
-- pas.effective_end_date,
PTYPE.user_person_type,
PPG.segment2 CATG_BUD,
PPG.segment1 QUALIF ,
(select otljs.coeff_js
from XXST_OTL_JS otljs
where otljs.departement = org.name
and otljs.type_coeff_std = 'JO'
and otljs.CATEGORIE_BUDG = ppg.segment2 ) coeff_std,
(select pac.segment3
from per_person_analyses ppan ,per_analysis_criteria pac
where ppan.id_flex_num = :CP_id_flex_num
and ppan.person_id = pas.person_id
and nvl(ppan.date_to, TO_DATE(:CP_END_DATE)) >= TO_DATE(:CP_END_DATE)
and nvl(ppan.DATE_FROM,:CP_START_DATE ) <= TO_DATE(:CP_START_DATE)
and ppan.analysis_criteria_id=pac.analysis_criteria_id(+)) MOD_RTT,
decode( (select pac.segment1
from per_person_analyses ppan ,per_analysis_criteria pac
where ppan.id_flex_num = :CP_id_flex_num
and ppan.person_id = pas.person_id
and nvl(ppan.date_to, TO_DATE(:CP_END_DATE)) >= TO_DATE(:CP_END_DATE)
and nvl(ppan.DATE_FROM,:CP_START_DATE ) <= TO_DATE(:CP_START_DATE)
and ppan.analysis_criteria_id=pac.analysis_criteria_id(+)),'Y','S','N','B','?') AFF
from (select distinct organization_id,effective_start_date, effective_end_date,assignment_id,people_group_id,person_id from PER_ALL_ASSIGNMENTS_F
where effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation
and NVL(effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE) ) PAS,
PER_ALL_PEOPLE_F PERS ,
HR_ALL_ORGANIZATION_UNITS ORG,
PER_PERSON_TYPES PTYPE,
PAY_PEOPLE_GROUPS PPG
&CP_TABLE
WHERE ORG.TYPE='DEPARTMENT'
&Q_ORG
&Q_ORG2
and ORG.organization_id = PAS.organization_id(+)
and ORG.DATE_FROM <=TO_DATE(:CP_END_DATE)
and NVL(ORG.DATE_TO,TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE)
and PAS.effective_start_date <= TO_DATE(:CP_END_DATE) --validite affectation
and NVL(PAS.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE)
and PERS.person_id=PAS.person_id
and PERS.effective_start_date <= TO_DATE(:CP_END_DATE) --validite personnes
and NVL(PERS.effective_end_date, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE)
and PTYPE.system_person_type='EMP'
and PTYPE.PERSON_TYPE_ID = PERS.PERSON_TYPE_ID
and PAS.people_group_id = PPG.people_group_id
and PPG.start_date_active <= TO_DATE(:CP_END_DATE) --validite personnes
and NVL(PPG.end_date_active, TO_DATE('01-01-3000','DD-MM-YYYY')) >=TO_DATE(:CP_START_DATE)
order by last_name