SELECT
		PF.PERSON_NUMBER,
		TRANSLATE(PF.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_AR,
		TO_CHAR(PPS.DATE_START,'DD/MM/YYYY') "Joining Date",
		TO_CHAR(SYSDATE,'DD/MM/YYYY')"SYSTEMDATE",
		PN.FIRST_NAME || (' ') || PN.LAST_NAME FULLNAME,
		PN_AR.FIRST_NAME || (' ') || PN_AR.LAST_NAME FULLNAME_AR,
		PN.TITLE,
		HOUT.NAME Business_Unit,
		HP.NAME "Position" ,
		HPT_AR.NAME "Position_AR" ,
		CASE WHEN PNI.NATIONAL_IDENTIFIER_NUMBER IS NULL THEN PASSPORT.PASSPORT_NUMBER ELSE PNI.NATIONAL_IDENTIFIER_NUMBER END NAT_OR_PASSPORT,
		PNI.NATIONAL_IDENTIFIER_NUMBER,
		PASSPORT.PASSPORT_NUMBER,
		CS.SALARY_AMOUNT "Basic Salary",
		TO_CHAR(SYSDATE,'DD/MM/YYYY') "DATE",
		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",
        hl5.meaning nationality,
		HL5_AR.MEANING "Nationality_AR",
		CIT.legislation_code
PF.PERSON_ID,
HDO.DEI_ATTRIBUTE1 EMBASSY     
	
	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 hpt_ar ,
		PER_PASSPORTS PASSPORT,
		PER_NATIONAL_IDENTIFIERS PNI,
		CMP_SALARY CS,
        per_citizenships CIT,
		hr_lookups hl5,
		FND_LOOKUP_VALUES_TL HL5_AR,
		PER_PERIODS_OF_SERVICE PPS,
		HR_DOCUMENT_TYPES_VL HDT,
        HR_DOCUMENTS_OF_RECORD HDO

	WHERE 1 = 1
		AND HDO.PERSON_ID = PF.PERSON_ID
        AND HDT.DOCUMENT_TYPE_ID = HDO.DOCUMENT_TYPE_ID	
		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 hp.position_id = hpt_ar.position_id(+)
        AND NVL(hpt_ar.LANGUAGE,'AR') = 'AR'
		AND PF.PERSON_ID = PASSPORT.PERSON_ID(+)
		AND PF.PERSON_ID = PNI.PERSON_ID(+)
		AND PF.PRIMARY_NID_ID = PNI.NATIONAL_IDENTIFIER_ID(+)
		AND PF.PERSON_ID = CS.PERSON_ID(+)
		AND PAA.ASSIGNMENT_ID = CS.ASSIGNMENT_ID(+)
        AND PF.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 UPPER(HDT.DOCUMENT_TYPE) = 'LETTER - EMBASSY'

		AND PF.PERSON_ID = PPS.PERSON_ID(+)
		AND PAA.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID(+)	
		AND (( COALESCE(NULL,:P_PERSON_Number ) IS NULL ) OR (PF.person_id IN ( :P_PERSON_Number ) ))
		
		AND TRUNC(hdo.CREATION_DATE) BETWEEN NVL(:p_from_date, PF.EFFECTIVE_START_DATE) AND NVL(:p_to_date, PF.EFFECTIVE_END_DATE)
		
		AND SYSDATE BETWEEN PF.EFFECTIVE_START_DATE AND PF.EFFECTIVE_END_DATE
		AND SYSDATE BETWEEN PAA.EFFECTIVE_START_DATE AND PAA.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 HPT_AR.EFFECTIVE_START_DATE(+) AND HPT_AR.EFFECTIVE_END_DATE(+)
		AND SYSDATE BETWEEN CS.DATE_FROM(+) AND CS.DATE_TO(+)