SELECT
        PA.PERSON_NUMBER "Employee Number" , --Parameter 
		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",
PN.TITLE ||' '|| pn.full_name"Full Name",
pnf.full_name "Arabic Name",
		HL5.MEANING "Nationality",
		HL5_AR.MEANING "Nationality In Arabic",
		hpt_en.name "Job Title",
		hpt_ar.name"Job Title In Arabic",
        translate(PNI.NATIONAL_IDENTIFIER_NUMBER,
					 '0123456789',
                     unistr('\0660') || unistr('\0661') || unistr('\0662') ||
                     unistr('\0663') || unistr('\0664') || unistr('\0665') ||
                     unistr('\0666') || unistr('\0667') || unistr('\0668') ||
                     unistr('\0669')) "National Identifier Arabic",
		PNI.NATIONAL_IDENTIFIER_NUMBER "National Identifier",
        TO_CHAR(PPS.DATE_START,'DD/MM/YYYY') "Hire Date",
		translate(TO_CHAR(NVL(TRUNC(PPS.DATE_START),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')) "Hire Date hijrah",
					 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"
HDO.DEI_ATTRIBUTE1

FROM
        PER_PERSON_SECURED_LIST_V PA,
		PER_PERSON_NAMES_F_V PN ,
		PER_PERSON_NAMES_F PNF,
		PER_CITIZENSHIPS CIT,
		HR_LOOKUPS HL5,
		FND_LOOKUP_VALUES_TL HL5_AR,
		PER_ALL_ASSIGNMENTS_F PAA ,
		per_position_secured_list_v hp,
        hr_all_positions_f_vl hpt_en ,
		hr_all_positions_f_tl hpt_ar ,
		PER_NATIONAL_IDENTIFIERS PNI,
		PER_PERIODS_OF_SERVICE PPS,
		HR_DOCUMENT_TYPES_VL HDT,
        HR_DOCUMENTS_OF_RECORD HDO
		
WHERE 1 = 1

        AND PA.PERSON_ID = PN.PERSON_ID
		AND PN.NAME_TYPE = 'GLOBAL' 
        AND PA.PERSON_ID = PNF.PERSON_ID
        AND PNF.NAME_TYPE = 'SA'
		AND PA.PERSON_ID = CIT.PERSON_ID(+)
		AND HL5.LOOKUP_CODE(+) = CIT.LEGISLATION_CODE
        AND HL5.LOOKUP_TYPE (+) = 'NATIONALITY'
		AND HL5_AR.LOOKUP_CODE(+) = CIT.LEGISLATION_CODE
        AND HL5_AR.LOOKUP_TYPE (+) = 'NATIONALITY' 
		AND NVL(HL5_AR.LANGUAGE,'AR') = 'AR'
		AND PA.PERSON_ID = PAA.PERSON_ID(+)
        AND PAA.PRIMARY_FLAG = 'Y'
		AND PAA.ASSIGNMENT_STATUS_TYPE ='INACTIVE'
		AND PAA.POSITION_ID = HP.POSITION_ID(+)
        AND hp.effective_start_date = hpt_en.effective_start_date(+)
        AND hp.effective_end_date = hpt_en.effective_end_date(+)
        AND hp.position_id = hpt_en.position_id(+)
	    AND hp.effective_start_date = hpt_ar.effective_start_date(+)
        AND hp.effective_end_date = hpt_ar.effective_end_date(+)
        AND hp.position_id = hpt_ar.position_id(+)
        AND NVL(hpt_ar.LANGUAGE,'AR') = 'AR'
        AND PA.PERSON_ID = PNI.PERSON_ID(+)
        AND PA.PRIMARY_NID_ID = PNI.NATIONAL_IDENTIFIER_ID(+)
        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 PA.PERSON_ID = HDO.PERSON_ID(+)
        AND HDT.DOCUMENT_TYPE_ID = HDO.DOCUMENT_TYPE_ID	(+)
        AND UPPER(HDT.DOCUMENT_TYPE) = 'LETTER - EMPLOYEE SERVICE CERTIFICATE'
		
        AND TRUNC (SYSDATE) BETWEEN TRUNC (PA.EFFECTIVE_START_DATE)AND TRUNC (PA.EFFECTIVE_END_DATE)
        AND TRUNC (SYSDATE) BETWEEN TRUNC (PN.EFFECTIVE_START_DATE)AND TRUNC (PN.EFFECTIVE_END_DATE)
	    AND TRUNC (SYSDATE) BETWEEN TRUNC (PNF.EFFECTIVE_START_DATE)AND TRUNC (PNF.EFFECTIVE_END_DATE)

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