SELECT
         TO_CHAR(SYSDATE,'DD/MM/YYYY')"SYSTEMDATE",
        translate(TO_CHAR(NVL(TRUNC(SYSDATE),TRUNC(SYSDATE + 30 )),'YYYY/MM/DD', 'nls_calendar=''arabic hijrah'''),
                    '0123456789',
                     unistr('\0660') || unistr('\0661') || unistr('\0662') ||
                     unistr('\0663') || unistr('\0664') || unistr('\0665') ||
                     unistr('\0666') || unistr('\0667') || unistr('\0668') ||
                     unistr('\0669')) "SYSDATE Date hijrah",
		PA.PERSON_NUMBER "Person Number" ,
		translate(PA.PERSON_NUMBER,
					 '0123456789',
                     unistr('\0660') || unistr('\0661') || unistr('\0662') ||
                     unistr('\0663') || unistr('\0664') || unistr('\0665') ||
                     unistr('\0666') || unistr('\0667') || unistr('\0668') ||
                     unistr('\0669')) "PERSON NUMBER Arabic",
        PN.FIRST_NAME ||' '|| PN.MIDDLE_NAMES ||' '||PN.LAST_NAME "Employee Name",
		PNF.FIRST_NAME ||' '|| PNF.MIDDLE_NAMES ||' '||PNF.LAST_NAME "Arabic Name",
		TO_CHAR(PPS.ACTUAL_TERMINATION_DATE, 'DD/MM/YYYY')"End of Service Date",
		translate(TO_CHAR(NVL(TRUNC(PPS.ACTUAL_TERMINATION_DATE),TRUNC(SYSDATE + 30 )),'YYYY/MM/DD', 'nls_calendar=''arabic hijrah'''),
					
					'0123456789',
                     unistr('\0660') || unistr('\0661') || unistr('\0662') ||
                     unistr('\0663') || unistr('\0664') || unistr('\0665') ||
                     unistr('\0666') || unistr('\0667') || unistr('\0668') ||
                     unistr('\0669')) "End of Service Date hijrah"

FROM
        PER_PERSON_SECURED_LIST_V PA,
		PER_PERSON_NAMES_F_V PN ,
		PER_PERSON_NAMES_F PNF,
		PER_ALL_ASSIGNMENTS_F PAA ,
		PER_PERIODS_OF_SERVICE PPS
		
WHERE 1 = 1

        AND PA.PERSON_ID = PN.PERSON_ID
        AND PA.PERSON_ID = PNF.PERSON_ID
        AND PNF.NAME_TYPE = 'SA'
		AND PA.PERSON_ID = PAA.PERSON_ID(+)
        AND PAA.PRIMARY_FLAG = 'Y'
        AND PA.PERSON_ID = PPS.PERSON_ID
		AND PAA.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID(+)
        AND PAA.LEGAL_ENTITY_ID = PPS.LEGAL_ENTITY_ID (+)
		AND PPS.ACTUAL_TERMINATION_DATE IS NOT NULL
		
        AND TRUNC (SYSDATE) BETWEEN  (PA.EFFECTIVE_START_DATE)AND  (PA.EFFECTIVE_END_DATE)
        AND TRUNC (SYSDATE) BETWEEN  (PN.EFFECTIVE_START_DATE)AND  (PN.EFFECTIVE_END_DATE)
	    AND TRUNC (SYSDATE) BETWEEN  (PNF.EFFECTIVE_START_DATE)AND  (PNF.EFFECTIVE_END_DATE)
        AND TRUNC (SYSDATE) BETWEEN  (PAA.EFFECTIVE_START_DATE)AND  (PAA.EFFECTIVE_END_DATE)

		AND ((PA.PERSON_ID IN (:P_Employee_Number)) OR ( COALESCE(:P_Employee_Number,NULL) IS NULL))
		
		ORDER BY PA.PERSON_NUMBER