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