I added a get function that you can use instead of Left Join. You can pull the columns from this API with the get function. You can edit the query you created as a view and use where conditions there. Your query will run much faster this way. Also, instead of joining with the PERS view, you can use the get functions of the Pers API. Writing so many sub queries will leave you very open to errors.
create or replace view tmp_test_view99 as
SELECT DISTINCT
CPA.INTERNAL_DISPLAY_NAME as FULL_NAME,
CPA.COMPANY_ID as COMPANY,
cpa.emp_no as EMP_NO,
IFSAPP.COMPANY_ORG_API.Get_Org_Name(CPA.COMPANY_ID,ORG_CODE) as "Dept.",
(SELECT LISTAGG(PROJECT_ID, '; ') WITHIN GROUP (ORDER BY PROJECT_ID)
FROM (
SELECT DISTINCT par1.PROJECT_ID
FROM IFSAPP.Project_access_result2 par1
WHERE par1.EMPLOYEE_ID = cpa.emp_no
)
) as "PROJECT",
IFSAPP.COMPANY_POSITION_API.Get_Position_Title(CPA.company_id,POS_CODE) as Position,
IFSAPP.TRBRD_PERSONEL_BANKA_API.Get_Iban(CPA.company_id,CPA.emp_no,1,1) as IBAN,
IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_Start_Date(CPA.COMPANY_ID, CPA.EMP_NO) as "Emp. Start",
IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_End_Date(CPA.COMPANY_ID, CPA.EMP_NO, SYSDATE) as "Emp. End",
TRUNC(MONTHS_BETWEEN(sysdate, PER.DATE_OF_BIRTH) / 12,0) as AGE,
--TI4.HIZMET_SURESI,
TRIFM_IZINLER_API.Get_Calisilan_Yil(CPA.company_id , CPA.emp_no , TRUNC(SYSDATE)) HIZMET_SURESI,
PER.DATE_OF_BIRTH as Birth,
PER.INSURANCE_ID as "INS. ID",
PER.GENDER as Gender,
'Military Service status field will be added' as MSS,
PER.MARITAL_STATUS as "Mar. Status",
PER.BLOOD_TYPE as Blood,
(SELECT education_level_name
FROM (
SELECT education_level_name
FROM IFSAPP.pers_education_profile
WHERE person_id = cpa.person_id
AND extract(YEAR FROM end_year) != 9999
ORDER BY end_year DESC, education_level_no DESC
)
WHERE ROWNUM = 1) as "EDU LEVEL",
(SELECT EDU_INSTITUTION_NAME
FROM (
SELECT EDU_INSTITUTION_NAME
FROM IFSAPP.pers_education_profile
WHERE person_id = cpa.person_id
AND extract(YEAR FROM end_year) != 9999
ORDER BY end_year DESC, education_level_no DESC
)
WHERE ROWNUM = 1) as "University",
(SELECT education_field_name
FROM (
SELECT education_field_name
FROM IFSAPP.pers_education_profile
WHERE person_id = cpa.person_id
AND extract(YEAR FROM end_year) != 9999
ORDER BY end_year DESC, education_level_no DESC
)
WHERE ROWNUM = 1) as "EDU FIELD",
(SELECT END_YEAR
FROM (
SELECT END_YEAR
FROM IFSAPP.pers_education_profile
WHERE person_id = cpa.person_id
AND extract(YEAR FROM end_year) != 9999
ORDER BY end_year DESC, education_level_no DESC
)
WHERE ROWNUM = 1) as "GRAD. DATE",
(Select COMM_DATA from IFSAPP.PERS_COMMS2 Where METHOD_ID_DB = 'E_MAIL' and DESCRIPTION = 'Şahsi' and PERSON_ID = CPA.EMP_NO and ROWNUM = 1) as "Personal E-Mail",
(Select COMM_DATA from IFSAPP.PERS_COMMS2 Where METHOD_ID_DB = 'E_MAIL' and DESCRIPTION = 'İş' and PERSON_ID = CPA.EMP_NO and ROWNUM = 1) as "Corp. E-Mail",
(Select COMM_DATA from IFSAPP.PERS_COMMS2 Where METHOD_ID_DB = 'MOBILE' and DESCRIPTION = 'Şahsi' and PERSON_ID = CPA.EMP_NO and ROWNUM = 1) as "Mobile",
(select RELATIVE_NAME from IFSAPP.PERS_RELATED where PERSON_ID = CPA.EMP_NO AND ROWNUM = 1) as "Emergency 1st Person Name",
(select RELATIVE_PHONE from IFSAPP.PERS_RELATED where PERSON_ID = CPA.EMP_NO AND RELATIVE_NAME = (select RELATIVE_NAME from IFSAPP.PERS_RELATED where PERSON_ID = CPA.EMP_NO AND ROWNUM = 1) AND ROWNUM = 1 ) as "Emergency 1st Person Phone",
(select RELATIVE_NAME from (SELECT (ROW_NUMBER() OVER (ORDER BY RELATED_ID)) as RN, PR.* FROM IFSAPP.PERS_RELATED PR WHERE PERSON_ID = CPA.EMP_NO) where RN = 2) as "Emergency 2nd Person Name",
(select RELATIVE_PHONE from (SELECT (ROW_NUMBER() OVER (ORDER BY RELATED_ID)) as RN, PR.* FROM IFSAPP.PERS_RELATED PR WHERE PERSON_ID = CPA.EMP_NO AND RELATIVE_NAME = (select RELATIVE_NAME from ( SELECT (ROW_NUMBER() OVER (ORDER BY RELATED_ID)) as RN, PR.* FROM IFSAPP.PERS_RELATED PR WHERE PERSON_ID = CPA.EMP_NO) where RN = 2)) ) as "Emergency 2nd Person Phone"
FROM IFSAPP.COMPANY_PERSON_ALL CPA
LEFT OUTER JOIN IFSAPP.PERS PER ON CPA.EMP_NO = PER.PERSON_ID
--LEFT OUTER JOIN IFSAPP.TRIFM_IZINLER4 TI4 ON CPA.EMP_NO = TI4.PERSON_ID AND CPA.COMPANY_ID = TI4.COMPANY_ID
--WHERE EMP_NO like UPPER(nvl('&Kim?','%'))
--AND COMPANY_ID like UPPER(nvl('&Şirket?','%'))
--AND (TRUNC(TI4.RAPOR_TARIHI)) = (TRUNC(nvl(to_date('&Tarih', 'DD-MM-YYYY'),TRUNC(SYSDATE)) + (1 - 1 / (60 * 60 * 24))))
ORDER BY CPA.INTERNAL_DISPLAY_NAME ;
-- last query
/*select * from tmp_test_view99 where EMP_NO like UPPER(nvl('&Kim?','%'))
AND COMPANY_ID like UPPER(nvl('&Şirket?','%')) */
