SELECT
        PF.PERSON_NUMBER,
		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",
        PNI.NATIONAL_IDENTIFIER_NUMBER ,
		TO_CHAR(PPS.DATE_START,'DD/MM/YYYY') "Joining Date",
		TO_CHAR(PPS.ACTUAL_TERMINATION_DATE, 'DD/MM/YYYY')"Termination Date",
		PN.FIRST_NAME || (' ') || PN.LAST_NAME FULLNAME,
		PN_AR.FIRST_NAME || (' ') || PN_AR.LAST_NAME FULLNAME_AR,
		HP.NAME "Position" ,
		HP_AR.NAME "Position_AR" ,
		hl5t.meaning nationality ,
		hl5t_en.meaning nationality_en 
     		
    FROM PER_PERSON_SECURED_LIST_V PF,
		PER_PERSON_NAMES_F PN,
		PER_PERSON_NAMES_F PN_AR,
		PER_ALL_ASSIGNMENTS_M PAA , 
		HR_ALL_ORGANIZATION_UNITS_VL HOUT,
		PER_DEPARTMENT_SECURED_LIST_V PDSL,
		HR_ALL_POSITIONS_F_VL HP,
		HR_ALL_POSITIONS_F_TL HP_AR,
		PER_NATIONAL_IDENTIFIERS PNI,
		per_citizenships CIT,
		FND_LOOKUP_VALUES_B hl5,
		FND_LOOKUP_VALUES_TL hl5t,
		FND_LOOKUP_VALUES_TL hl5t_en,
		HR_DOCUMENT_TYPES_VL	HDT,
		HR_DOCUMENTS_OF_RECORD hdo,
		PER_PERIODS_OF_SERVICE PPS
    WHERE 1 = 1
		AND PF.PERSON_ID = PN.PERSON_ID
		AND PN.NAME_TYPE = 'GLOBAL' 
		AND PF.PERSON_ID = PN_AR.PERSON_ID
		AND PN_AR.NAME_TYPE = 'SA' 
		AND PF.PERSON_ID = PAA.PERSON_ID 
		AND PAA.PRIMARY_FLAG = 'Y'
		AND PAA.ASSIGNMENT_STATUS_TYPE != 'ACTIVE'
		AND PAA.BUSINESS_UNIT_ID = HOUT.ORGANIZATION_ID
		AND HOUT.ORGANIZATION_ID = PDSL.ORGANIZATION_ID
		AND PAA.POSITION_ID = HP.POSITION_ID(+)	
		AND PAA.POSITION_ID = HP_AR.POSITION_ID(+)
		AND NVL(HP_AR.LANGUAGE,'AR')='AR'
		AND PF.PERSON_ID = PNI.PERSON_ID(+)
		AND PF.PRIMARY_NID_ID = PNI.NATIONAL_IDENTIFIER_ID(+)
		AND PF.person_id = cit.person_id (+)
		AND hl5.lookup_code(+) = cit.legislation_code
		AND hl5.lookup_type(+) = 'NATIONALITY'
		AND hl5t.LOOKUP_TYPE(+) = hl5.LOOKUP_TYPE

        AND hl5t.LOOKUP_CODE(+) = hl5.LOOKUP_CODE
        AND hl5t.VIEW_APPLICATION_ID(+) = hl5.VIEW_APPLICATION_ID
        AND hl5t.SET_ID(+) = hl5.SET_ID
        AND hl5t.LANGUAGE(+) = 'AR'
		AND hl5t_en.LOOKUP_TYPE(+) = hl5.LOOKUP_TYPE
        AND hl5t_en.LOOKUP_CODE(+) = hl5.LOOKUP_CODE
        AND hl5t_en.VIEW_APPLICATION_ID(+) = hl5.VIEW_APPLICATION_ID
        AND hl5t_en.SET_ID(+) = hl5.SET_ID
        AND hl5t_en.LANGUAGE(+) = 'US'
		AND PF.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 hdo.person_id = PF.person_id
        AND HDT.document_type_id = hdo.document_type_id
		AND UPPER(HDT.DOCUMENT_TYPE) = 'LETTER - CLEARANCE'
		
        AND (( COALESCE(NULL,:P_PERSON_NUMBER ) IS NULL ) OR (PF.PERSON_NUMBER  IN ( :P_PERSON_NUMBER ) ))
		AND ((HDO.CREATION_DATE ) BETWEEN NVL(:FROM_DATE,HDO.CREATION_DATE) AND NVL(:TO_DATE,HDO.CREATION_DATE))
		AND SYSDATE BETWEEN PF.EFFECTIVE_START_DATE AND PF.EFFECTIVE_END_DATE
		AND SYSDATE BETWEEN PN.EFFECTIVE_START_DATE AND PN.EFFECTIVE_END_DATE
		AND SYSDATE BETWEEN PN_AR.EFFECTIVE_START_DATE AND PN_AR.EFFECTIVE_END_DATE
		AND SYSDATE BETWEEN HOUT.EFFECTIVE_START_DATE AND HOUT.EFFECTIVE_END_DATE
		AND SYSDATE BETWEEN HP.EFFECTIVE_START_DATE(+) AND HP.EFFECTIVE_END_DATE(+)
		AND SYSDATE BETWEEN HP_AR.EFFECTIVE_START_DATE(+) AND HP_AR.EFFECTIVE_END_DATE(+)
		ORDER BY PF.PERSON_NUMBER