new modified query works on DB brings data in 20 seconds (this happened after i joined TRIFM_IZINLER4 otherwise runs under 2 seconds)
but on IEE it doesn’t bring any data, it works forever to tries to get data. doesn’t produce any error.
don’t mind the filter i made a lot of modifications even very simple one but result is the same.
what would be the reason?
PS: it works on TEST Env. brings data in 20 seconds but ON PROD Env. it doesn't bring any data, report seems working and trying to bring data, it takes forever.
Thanks.
Page 1 / 1
Hi
From application Quick report is executed on different user(IFSSYS?) and it might determine different query plan based on conditions in views which are You using.
Hi
From application Quick report is executed on different user(IFSSYS?) and it might determine different query plan based on conditions in views which are You using.
Hi @knepiosko , thank you for reply.
but it works on TEST Env., everything is same even users and privileges.
and it was working before i made aforementioned modifications.
Is Test a fresh copy of Prod environment? Execution plan can be different on both environments depends on number of rows in tables, statistics, etc.
I think this is an IFS standard unification effort through localization. *Report Date* returns data for each day defined in the calendar. It does not work in the same way as other date fields in your table. If you submit your exact query, we can investigate the error.
Is Test a fresh copy of Prod environment? Execution plan can be different on both environments depends on number of rows in tables, statistics, etc.
Hi @knepiosko ,
Thank you for reply.
No it is not a fresh copy of Prod Env. Test Env. almost 6 months older than Prod, we do all our tests on Test Env. So there is enormous amount of data. I don’t understand why execution plan might be different?
I think this is an IFS standard unification effort through localization. *Report Date* returns data for each day defined in the calendar. It does not work in the same way as other date fields in your table. If you submit your exact query, we can investigate the error.
Hi @Alperen
Thank you for reply.
Here is the query.
SELECT DISTINCT CPA.INTERNAL_DISPLAY_NAME as FULL_NAME, CPA.COMPANY_ID as COMPANY, 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,
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 CPA.EMP_NO like UPPER(nvl('&&-C-BL]Kim?','%')) AND CPA.COMPANY_ID like UPPER(nvl('&&-C-BL]Şirket?','%')) AND (TRUNC(TI4.RAPOR_TARIHI)) = (TRUNC(nvl(to_date('&'-C-BL]Tarih', 'DD-MM-YYYY'),TRUNC(SYSDATE)) + (1 - 1 / (60 * 60 * 24))))
ORDER BY CPA.INTERNAL_DISPLAY_NAME
Before I join TRIFM_IZINLER4 table it was working under 2 seconds, now on PROD Env. report keeps running and at the end it says something like “request canceled”.
On Test Env. it works in 20 seconds.
Hi @Alperen, have you a chance to take a look at it?
Hi @Noriro2
Try trifm_izinler3 to create the query. trifm_izinler4 is very slow view. Additionally, don’t forget to filter the rapor_tarihi.
Hi @Noriro2
Try trifm_izinler3 to create the query. trifm_izinler4 is very slow view. Additionally, don’t forget to filter the rapor_tarihi.
Hi @hhy38 ,
Thank you for reply.
I tried that before but couldn’t make it work, it always generates error “sub query returned too many rows” or something like that.
the problem is not that it is not working, it is working in TEST Env. but it is not working in PROD Env. very strange, couldn’t solve it yet.
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
-- last query /*select * from tmp_test_view99 where EMP_NO like UPPER(nvl('&Kim?','%')) AND COMPANY_ID like UPPER(nvl('&Şirket?','%')) */
Please change “TRUNC(TI4.RAPOR_TARIHI) =” to “TI4.RAPOR_TARIHI” between … and ...
Please change “TRUNC(TI4.RAPOR_TARIHI) =” to “TI4.RAPOR_TARIHI” between … and ...
No, the report date cannot be run as a range. Returns values for each day in the calendar, such as the historical view of the Organization Chart. If you enter a range, you will see multiplexed data and no results.
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
-- last query /*select * from tmp_test_view99 where EMP_NO like UPPER(nvl('&Kim?','%')) AND COMPANY_ID like UPPER(nvl('&Şirket?','%')) */
@Alperen ,
Thank you very much, i didn’t know that there is API for this, the first thing that i will check if there is an API in the future.
i agree with your suggestions but i am newbie, it is very long query and seems complex i can’t beg people to review and tune it since i cannot do it either.
I appreciate your work on this, thank you.
Kinds.
Please change “TRUNC(TI4.RAPOR_TARIHI) =” to “TI4.RAPOR_TARIHI” between … and ...
Please change “TRUNC(TI4.RAPOR_TARIHI) =” to “TI4.RAPOR_TARIHI” between … and ...
No, the report date cannot be run as a range. Returns values for each day in the calendar, such as the historical view of the Organization Chart. If you enter a range, you will see multiplexed data and no results.
Hi @knepiosko , @Alperen is right this date cannot be run as a range. Thanks.
TRUNC by default truncate to day so it means the range is a whole day. So then
it asks Report Date (Rapor Tarihi), if you don’t input the date, SYSDATE is default. There is no “Between” in the dates. But i will try your suggestion after i created it as a view as Alperen suggested, then i think i will see the difference.
Here only on parameter used twice:
TI4.RAPOR_TARIHI between TRUNC(nvl(to_date('&Tarih', 'DD-MM-YYYY'),SYSDATE)) and TRUNC(nvl(to_date('&Tarih', 'DD-MM-YYYY'),SYSDATE) + (1 - 1 / (60 * 60 * 24)))
Here only on parameter used twice:
TI4.RAPOR_TARIHI between TRUNC(nvl(to_date('&Tarih', 'DD-MM-YYYY'),SYSDATE)) and TRUNC(nvl(to_date('&Tarih', 'DD-MM-YYYY'),SYSDATE) + (1 - 1 / (60 * 60 * 24)))
Ouch!, we missed the point, since Alperen removed the join part i cannot use the filter in WHERE clause. How can i use it in API?
Hello , Report Date logic is the view of the system on that date. So Report is designed for date leave screen.The fields in your report do not include calendar-based fields. You access a report by tabulating the currently defined data.You should be recording and checking the tables daily so that you can combine this with the report date.To give an example from your query, when you enter both high school and university information, when you change the report date to the period in which you studied in high school, the university information should be blank. (Educational information) To do this, you must create your own report, connect it to the calendar and map it to a report date field (this means having data for each day in the calendar). I will post the code of the report you want to make in the next post.
This first view with acc_Date (new rapor tarihi for our report)
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.", CAL.ACCOUNT_DATE ACC_dATE,
(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, CAL.ACCOUNT_DATE) as "Emp. End", TRUNC(MONTHS_BETWEEN(sysdate, PER.DATE_OF_BIRTH) / 12, 0) as AGE,
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, IFSAPP.PERS PER, CALENDAR CAL WHERE PER.person_id = CPA.person_id AND IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_Start_Date(CPA.COMPANY_ID, CPA.EMP_NO) <= CAL.ACCOUNT_DATE AND ((IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_End_Date(CPA.COMPANY_ID, CPA.EMP_NO, CAL.ACCOUNT_DATE) IS NULL OR IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_End_Date(CPA.COMPANY_ID, CPA.EMP_NO, CAL.ACCOUNT_DATE) = to_Date('31/12/9999', 'DD/MM/YYYY')) OR (IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_End_Date(CPA.COMPANY_ID, CPA.EMP_NO, CAL.ACCOUNT_DATE) > CAL.ACCOUNT_DATE OR IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_End_Date(CPA.COMPANY_ID, CPA.EMP_NO, CAL.ACCOUNT_DATE) <= CAL.ACCOUNT_DATE)) AND CAL.YIL BETWEEN TO_NUMBER(TO_CHAR(IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_Start_Date(CPA.COMPANY_ID, CPA.EMP_NO),'YYYY')) AND TO_NUMBER(TO_CHAR(IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_End_Date(CPA.COMPANY_ID, CPA.EMP_NO, CAL.ACCOUNT_DATE),'YYYY'))
ORDER BY CPA.INTERNAL_DISPLAY_NAME;
This 2 view with left join view code
Check columns
create view tmp_test_view100 as SELECT a.FULL_NAME, a.COMPANY, a.emp_no, a."Dept.", a.ACC_dATE, a.PROJECT, a.Position, a.IBAN, a."Emp. Start", a."Emp. End", a.AGE, a.START_YEAR, a.LAST_END, a.Birth, a."INS. ID", a.GENDER, a.MSS, a."Mar. Status", a.Blood, a."EDU LEVEL", a."University", a."EDU FIELD", a."GRAD. DATE", a."Personal E-Mail", a."Corp. E-Mail", a."Mobile", a."Emergency 1st Person Name", a."Emergency 1st Person Phone", a."Emergency 2nd Person Name", a."Emergency 2nd Person Phone" , ti4.rapor_tarihi, ti4.adi_soyadi, ti4.hizmet_suresi, ti4.izin_hakedis_tarihi, ti4.toplam_hakettigi_izin, ti4.toplam_kullandigi_izin, ti4.kalan_izin, ti4.dogum_tarihi FROM tmp_test_view99 a left outer join trifm_izinler4 ti4 on ti4.rapor_tarihi = a.ACC_dATE and ti4.emp_no = a.emp_no and ti4.company_id=a.COMPANY
and report code
Dont forget acc date and never use between.
And Check your Calendar_tab for every single day :D
select * from tmp_test_view100 x where x.ACC_dATE = trunc(sysdate) -- MUST A SPESIFIC DATE and x.emp_no like nvl('&KIM', '%') AND X.COMPANY LIKE NVL('&COMPANY', '%')
Calendar view may vary depending on the language you are working in. You should check the columns and mappings in the reports for your own language.
This first view with acc_Date (new rapor tarihi for our report)
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.", CAL.ACCOUNT_DATE ACC_dATE,
(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, CAL.ACCOUNT_DATE) as "Emp. End", TRUNC(MONTHS_BETWEEN(sysdate, PER.DATE_OF_BIRTH) / 12, 0) as AGE,
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, IFSAPP.PERS PER, CALENDAR CAL WHERE PER.person_id = CPA.person_id AND IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_Start_Date(CPA.COMPANY_ID, CPA.EMP_NO) <= CAL.ACCOUNT_DATE AND ((IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_End_Date(CPA.COMPANY_ID, CPA.EMP_NO, CAL.ACCOUNT_DATE) IS NULL OR IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_End_Date(CPA.COMPANY_ID, CPA.EMP_NO, CAL.ACCOUNT_DATE) = to_Date('31/12/9999', 'DD/MM/YYYY')) OR (IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_End_Date(CPA.COMPANY_ID, CPA.EMP_NO, CAL.ACCOUNT_DATE) > CAL.ACCOUNT_DATE OR IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_End_Date(CPA.COMPANY_ID, CPA.EMP_NO, CAL.ACCOUNT_DATE) <= CAL.ACCOUNT_DATE)) AND CAL.YIL BETWEEN TO_NUMBER(TO_CHAR(IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_Start_Date(CPA.COMPANY_ID, CPA.EMP_NO),'YYYY')) AND TO_NUMBER(TO_CHAR(IFSAPP.Emp_Employed_Time_API.Get_Common_Emp_End_Date(CPA.COMPANY_ID, CPA.EMP_NO, CAL.ACCOUNT_DATE),'YYYY'))
ORDER BY CPA.INTERNAL_DISPLAY_NAME;
This 2 view with left join view code
Check columns
create view tmp_test_view100 as SELECT a.FULL_NAME, a.COMPANY, a.emp_no, a."Dept.", a.ACC_dATE, a.PROJECT, a.Position, a.IBAN, a."Emp. Start", a."Emp. End", a.AGE, a.START_YEAR, a.LAST_END, a.Birth, a."INS. ID", a.GENDER, a.MSS, a."Mar. Status", a.Blood, a."EDU LEVEL", a."University", a."EDU FIELD", a."GRAD. DATE", a."Personal E-Mail", a."Corp. E-Mail", a."Mobile", a."Emergency 1st Person Name", a."Emergency 1st Person Phone", a."Emergency 2nd Person Name", a."Emergency 2nd Person Phone" , ti4.rapor_tarihi, ti4.adi_soyadi, ti4.hizmet_suresi, ti4.izin_hakedis_tarihi, ti4.toplam_hakettigi_izin, ti4.toplam_kullandigi_izin, ti4.kalan_izin, ti4.dogum_tarihi FROM tmp_test_view99 a left outer join trifm_izinler4 ti4 on ti4.rapor_tarihi = a.ACC_dATE and ti4.emp_no = a.emp_no and ti4.company_id=a.COMPANY
and report code
Dont forget acc date and never use between.
And Check your Calendar_tab for every single day :D
select * from tmp_test_view100 x where x.ACC_dATE = trunc(sysdate) -- MUST A SPESIFIC DATE and x.emp_no like nvl('&KIM', '%') AND X.COMPANY LIKE NVL('&COMPANY', '%')
Calendar view may vary depending on the language you are working in. You should check the columns and mappings in the reports for your own language.
Dear @Alperen
Thank you very much, i appreciate your work and effort, I don't know how to thank you.
i will follow your instructions and apply your code, and let you know the result, but i can do this next working day.
Thank you.
Hi @Alperen ,
as promised i tested you queries (views), and they are working as expected.
I will go live on Prod. Env. after couple of data validations.
