Hi, 

i am asked to create a quick report by converting from Excel, i have couple of questions.

1.  4th column is Project didn’t work as expected, i want to fetch all data into one cell with a delimeter.
2. I used APIs in 5th, 6th, 7th and 8th columns, which i am not sure, if this is a correct way of using APIs
3. There are too many sub-queries and few joins

This query works under 3 second, I am NOT EXPERT but newbie (this is my first very long and complicated query, i wrote it all by myself by searching on internet (some part i took from my prev. questions here)).

can you help me to correct this query and make Project column work as expected?

SELECT DISTINCT
CPA.INTERNAL_DISPLAY_NAME as FULL_NAME,
COMPANY_ID as COMPANY,
COMPANY_ORG_API.Get_Org_Name(COMPANY_ID,ORG_CODE) as "Dept.",

--PROJECTS ARE POPULATED, DISTINCT Didn't work.
 (SELECT DISTINCT LISTAGG(PAR1.PROJECT_ID, '; ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY PAR1.PROJECT_ID) FROM Project_access_result2 PAR1 WHERE PAR1.EMPLOYEE_ID = CPA.EMP_NO) as "PROJECT",

COMPANY_POSITION_API.Get_Position_Title(company_id,POS_CODE) as Position,
TRBRD_PERSONEL_BANKA_API.Get_Iban(company_id,emp_no,1,1) as IBAN,
Emp_Employed_Time_API.Get_Common_Emp_Start_Date(COMPANY_ID, EMP_NO) as "Emp. Start",
Emp_Employed_Time_API.Get_Common_Emp_End_Date(COMPANY_ID, EMP_NO, SYSDATE) as "Emp. End",
TRUNC(MONTHS_BETWEEN(sysdate, PER.DATE_OF_BIRTH) / 12,0) as AGE,
'Employment Duration will be calculated based on EXCEL FORMULA =((YEAR(M5)-YEAR(K5))*12+MONTH(M5)-MONTH(K5))/12' as "Duration of Employment",
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   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   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   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   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 PERS_COMMS2 Where METHOD_ID_DB = 'E_MAIL' and DESCRIPTION = 'Şahsi' and PERSON_ID = EMP_NO and ROWNUM = 1) as "Personal E-Mail",

(Select COMM_DATA from PERS_COMMS2 Where METHOD_ID_DB = 'E_MAIL' and DESCRIPTION = 'İş' and PERSON_ID = EMP_NO and ROWNUM = 1) as "Corp. E-Mail",

(Select COMM_DATA from PERS_COMMS2 Where METHOD_ID_DB = 'MOBILE' and DESCRIPTION = 'Şahsi' and PERSON_ID = EMP_NO and ROWNUM = 1) as "Mobile",

(select RELATIVE_NAME from PERS_RELATED where PERSON_ID = EMP_NO AND ROWNUM = 1) as "Emergency 1st Person Name", 

(select RELATIVE_PHONE from PERS_RELATED where PERSON_ID = EMP_NO AND RELATIVE_NAME = (select RELATIVE_NAME from PERS_RELATED where PERSON_ID = 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 PERS_RELATED PR WHERE PERSON_ID = 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 PERS_RELATED PR WHERE PERSON_ID = EMP_NO 
        AND RELATIVE_NAME = (select RELATIVE_NAME 
                                from ( 
                                    SELECT (ROW_NUMBER() OVER (ORDER BY RELATED_ID)) as RN, PR.* 
                                    FROM PERS_RELATED PR 
                                    WHERE PERSON_ID = EMP_NO) 
                                where RN = 2)) 
                            ) as "Emergency 2nd Person Phone"

FROM COMPANY_PERSON_ALL CPA

--LEFT OUTER JOIN Project_access_result2 PAR
--ON CPA.EMP_NO = PAR.EMPLOYEE_ID

LEFT OUTER JOIN PERS PER
ON CPA.EMP_NO = PER.PERSON_ID

--WHERE CPA.EMP_NO = ''
ORDER BY CPA.INTERNAL_DISPLAY_NAME
--GROUP BY CPA.FNAME, CPA.LNAME

Need your help, Thanks.

kinds.