SELECT DISTINCT
        PA.PERSON_NUMBER "Person Number" ,
        PN.FIRST_NAME ||' '|| PN.MIDDLE_NAMES ||' '||PN.LAST_NAME "Employee Name",
		HR.MEANING "Assignment Category",
		Job_EN.NAME "Job Title",
		CASE WHEN GR.NAME is not null 
		     THEN  '  '
			 ELSE  '  ' 
			 END "Grade",
		HL5.MEANING "Nationality",
        CASE WHEN PNI.NATIONAL_IDENTIFIER_NUMBER IS NULL 
		     THEN PASSPORT.PASSPORT_NUMBER 
			 ELSE PNI.NATIONAL_IDENTIFIER_NUMBER 
			 END  "Iqama Number / Saudi National ID",
		PNI_G.NATIONAL_IDENTIFIER_NUMBER GOSI_Number,
		TO_CHAR(PA.START_DATE,'DD/MM/YYYY') "Hire Date",
		TO_CHAR(POS.ACTUAL_TERMINATION_DATE	,'DD/MM/YYYY')"Termination Date",
		CASE WHEN PAV.ACTION_NAME = 'Hire'
		     THEN  '  '
			 ELSE PAV.ACTION_NAME 
			 END  "Termination Type",
		HOUT.NAME "Business Unit",
		Depart.Name "Department",
		PA_MNG.PERSON_NUMBER "Manager Number",
		PMN.FIRST_NAME "Manager First Name",
		PMN.LAST_NAME "Manager Last Name",
		EMAIL.EMAIL_ADDRESS "Line Manager Email",
		CASE
          WHEN HSA.DOCUMENT_TYPE LIKE '%Termination'
          THEN  'Yes'
          ELSE 'No'
          END "Have Termination Attachment?",
		PP.PAYROLL_NAME "Payroll"
FROM
        PER_PERSON_SECURED_LIST_V PA,
		PER_PERSON_NAMES_F_V PN,
		PER_ALL_ASSIGNMENTS_M PAA,
		PER_JOBS_F_TL Job_EN,
        PER_JOB_SECURED_LIST_V Job,
		PER_GRADES_F_TL GR ,
		HR_LOOKUPS HR,
		PER_CITIZENSHIPS CIT,
		HR_LOOKUPS HL5,
		PER_NATIONAL_IDENTIFIERS PNI,
		PER_NATIONAL_IDENTIFIERS PNI_G,
		PER_PASSPORTS PASSPORT,
		PER_PERIODS_OF_SERVICE POS ,
		PER_ACTION_OCCURRENCES PAO, 
        PER_ACTION_REASONS_TL PAR,
	    PER_ACTIONS_TL PAV,
		HR_ORGANIZATION_UNITS_F_TL HOUT,
		PER_DEPARTMENT_SECURED_LIST_V PDSL,
	    HR_ORGANIZATION_UNITS_F_TL Depart,
		PER_ASSIGNMENT_SUPERVISORS_F PAS,
		PER_PERSON_SECURED_LIST_V PA_MNG,
		PER_PERSON_NAMES_F_V PMN,
		PER_ALL_ASSIGNMENTS_M PAA_MNG ,
		PER_EMAIL_ADDRESSES EMAIL,
		PAY_PAY_RELATIONSHIPS_DN PREL,
        PAY_REL_GROUPS_DN PAYREL,
        PAY_ASSIGNED_PAYROLLS_DN PAPD,
        PAY_PAYROLL_SECURED_LIST_V PP,
		HRC_SEM_ATTACHMENTS HSA ,
		HR_ALL_POSITIONS_F_VL HP,
	    HR_ALL_POSITIONS_F_TL HP_EN,
		HR_LOCATIONS HL
WHERE 1 = 1
        AND PA.PERSON_ID = PN.PERSON_ID
		AND PA.PERSON_ID = PAA.PERSON_ID(+)
        AND PAA.PRIMARY_FLAG = 'Y'
		AND PAA.BUSINESS_GROUP_ID=GR.BUSINESS_GROUP_ID
		AND NVL(GR.LANGUAGE,'AR') = 'AR'
		--AND PAA.ASSIGNMENT_STATUS_TYPE ='INACTIVE'
		AND PAA.JOB_ID = Job_EN.JOB_ID(+)
        AND Job_EN.JOB_ID = Job.JOB_ID(+)
        AND NVL(Job_EN.LANGUAGE,'AR') = 'AR'
		AND PA.PERSON_ID = CIT.PERSON_ID(+)
		AND HL5.LOOKUP_CODE(+) = CIT.LEGISLATION_CODE
        AND HL5.LOOKUP_TYPE (+) = 'NATIONALITY'
		---
				AND HR.LOOKUP_CODE(+) = PAA.EMPLOYMENT_CATEGORY
				AND HR.LOOKUP_TYPE (+) = 'EMP_CAT'
		AND PA.PERSON_ID = PNI.PERSON_ID(+)
        AND PA.PRIMARY_NID_ID = PNI.NATIONAL_IDENTIFIER_ID(+)
		AND PA.PERSON_ID = PNI_G.PERSON_ID(+)
		AND PA.PERSON_ID = PASSPORT.PERSON_ID(+)
		AND PNI_G.NATIONAL_IDENTIFIER_TYPE (+)='GOSI_NUMBER'
		AND PA.PERSON_ID = POS.PERSON_ID(+)
		AND POS.PRIMARY_FLAG ='Y'
		AND POS.ACTION_OCCURRENCE_ID = PAO.ACTION_OCCURRENCE_ID(+)
		AND PAO.ACTION_REASON_ID = PAR.ACTION_REASON_ID(+)
		AND NVL (PAR.SOURCE_LANG, 'US') = 'US' 
		AND PAO.ACTION_ID = PAV.ACTION_ID(+)
		AND NVL(PAV.LANGUAGE,'US') = 'US'
		AND PAA.BUSINESS_UNIT_ID = HOUT.ORGANIZATION_ID(+)
		AND HOUT.ORGANIZATION_ID = PDSL.ORGANIZATION_ID(+)
		AND NVL(HOUT.LANGUAGE,'AR') = 'AR'
		AND PAA.ORGANIZATION_ID=Depart.ORGANIZATION_ID(+)
		AND NVL(Depart.LANGUAGE,'AR') = 'AR'
		
		AND PA.PERSON_ID=PAS.PERSON_ID(+)
		AND PAA.assignment_id = PAS.assignment_id (+)
		AND PAS.MANAGER_ASSIGNMENT_ID = PAA_MNG.ASSIGNMENT_ID(+)
		AND PAS.MANAGER_ID = PA_mng.PERSON_ID(+)
		AND PA_mng.PERSON_ID=PMN.PERSON_ID(+)
		AND PA_mng.PERSON_ID =EMAIL.PERSON_ID(+)
		
		AND PA.PERSON_ID = PREL.PERSON_ID(+)
        AND PREL.PAYROLL_RELATIONSHIP_ID = PAYREL.PAYROLL_RELATIONSHIP_ID(+)
        AND PAYREL.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID(+)
        AND PAYREL.PARENT_REL_GROUP_ID = PAPD.PAYROLL_TERM_ID(+)	
        AND PAYREL.GROUP_TYPE = 'A'
        AND PAPD.PAYROLL_ID = PP.PAYROLL_ID(+)
		AND PA.PERSON_ID = HSA.PERSON_ID(+)
		AND PAA.POSITION_ID = HP.POSITION_ID(+)
	    AND HP.POSITION_ID = HP_EN.POSITION_ID(+)
		AND NVL(HP_EN.LANGUAGE,'AR') = 'AR'
		AND PAA.LOCATION_ID = HL.LOCATION_ID(+)
        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  (PAA.EFFECTIVE_START_DATE)AND  (PAA.EFFECTIVE_END_DATE)
  		AND TRUNC (SYSDATE) BETWEEN  (PA_MNG.EFFECTIVE_START_DATE(+))AND  (PA_MNG.EFFECTIVE_END_DATE(+))
		AND TRUNC (SYSDATE) BETWEEN  (PMN.EFFECTIVE_START_DATE(+))AND  (PMN.EFFECTIVE_END_DATE(+))
		AND TRUNC (SYSDATE) BETWEEN  (PAA_MNG.EFFECTIVE_START_DATE(+))AND  (PAA_MNG.EFFECTIVE_END_DATE(+))

	    AND ((Depart.Name IN (:Department)) OR ( COALESCE(:Department,NULL) IS NULL))
	    AND ((HP_EN.NAME IN (:Position)) OR COALESCE(:Position,NULL) IS NULL)
	    AND ((Job_EN.NAME IN (:Job)) OR COALESCE(:Job,NULL) IS NULL)
		AND ((HL.LOCATION_NAME IN (:Location)) OR COALESCE(:Location,NULL) IS NULL)
		AND ((HOUT.NAME IN (:Business_Unit)) OR ( COALESCE(:Business_Unit,NULL) IS NULL))
		AND ((PA.PERSON_ID IN (:Employee_number)) OR ( COALESCE(:Employee_number,NULL) IS NULL))
		AND ((PAA.EMPLOYMENT_CATEGORY IN (:Assignment_Category)) OR COALESCE(:Assignment_Category,NULL) IS NULL)
		AND (((POS.ACTUAL_TERMINATION_DATE ) BETWEEN (:FROM_DATE) AND (:TO_DATE)) OR ((PA.START_DATE ) BETWEEN (:FROM_DATE) AND (:TO_DATE)))
	    
		  
		AND ((PAV.ACTION_NAME IN (:Employee_hiring_status)) OR COALESCE(:Employee_hiring_status,NULL) IS NULL)

		  ORDER BY PA.PERSON_NUMBER