PER Saudi Contract Expiry

SELECT org.name
              ,people.employee_number
              ,people.full_name
              ,con.reference
              ,hr1.meaning type
              ,decode(con.ctr_information1,null,null,hr2.meaning) status
              ,con.active_start_date
              ,con.duration
              ,con.duration_units
              /*,NVL(to_date(con.ctr_information2,'RRRR/MM/DD HH24:MI:SS'),  DECODE(duration_units,'D',(active_start_date+duration),
                                                           'W', (active_start_date+(duration*7)),
                                                           'M',(add_months(active_start_date,duration)),
                                                           'Y',(add_months(active_start_date,(duration*12))),'')) expiry_date,*/
			  ,fnd_date.date_to_displaydate(NVL(to_date(con.ctr_information2,'RRRR/MM/DD HH24:MI:SS'),  DECODE(duration_units,'D',(active_start_date+duration),
                                                           'W', (active_start_date+(duration*7)),
                                                           'M',(add_months(active_start_date,duration)),
                                                           'Y',(add_months(active_start_date,(duration*12))),'')), calendar_aware=>1) expiry_date,
	PER_PERSACTR_XMLP_PKG.cf_1formula() CF_1
FROM   per_all_assignments_f assg
             ,per_all_people     people
             ,per_contracts        con
             ,hr_lookups hr1
             ,hr_lookups hr2
             ,hr_all_organization_units   org
WHERE assg.person_id = people.person_id
AND   assg.ASSIGNMENT_TYPE='E'
AND     NVL(:P_DATE,sysdate) BETWEEN assg.effective_start_date
                  AND   assg.effective_end_date
AND     NVL(:P_DATE,sysdate) between people.effective_start_date
                  AND   people.effective_end_date
AND    assg.contract_id = con.contract_id
AND    hr1.lookup_type = 'CONTRACT_TYPE'
AND    hr1.lookup_code = con.type
AND    assg.organization_id = org.organization_id
AND    con.ctr_information_category = 'SA'
AND    nvl(con.ctr_information1,'BACHELOR_STATUS') = hr2.lookup_code (+)
AND    decode(:P_EMP_STATUS,null,nvl(con.ctr_information1,'0000'), :P_EMP_STATUS) = nvl(con.ctr_information1,'0000')
AND    hr2.lookup_type(+) = 'SA_EMPLOYMENT_STATUS'
AND    con.status like 'A-%'
&l_org_condition
&l_type_condition
&l_status_condition
AND org.business_group_id = :p_business_group_id
AND    NVL(to_date(ctr_information2,'RRRR/MM/DD HH24:MI:SS'),  DECODE(duration_units,'D',(active_start_date+duration),'W',(active_start_date+(duration*7)),
               'M',(add_months(active_start_date,duration)),
               'Y',(add_months(active_start_date,(duration*12))),'')) BETWEEN 
NVL(:P_DATE,sysdate) AND DECODE(:P_EXP_UNITS,
       'D',(NVL(:P_DATE,sysdate)+:P_EXP),
       'W',(NVL(:P_DATE,sysdate)+(:P_EXP*7)),
       'M',(add_months(NVL(:P_DATE,sysdate),:P_EXP)),
       'Y',(add_months(NVL(:P_DATE,sysdate),(:P_EXP*12)))) 
ORDER BY org.name, expiry_date, full_name