SELECT * FROM (SELECT

 CAST(NULL AS FTINT32) IKPERSONEL_NO,

 CAST (IP.VATANDAS_NO AS FTINT64) VATANDAS_NO,

 CAST(IP.TAKIP_NO AS VARCHAR(24)) PERSON_TAKIP_NO,

 CAST(IP.TAKIP_ONAY_NO AS VARCHAR(24)) TAKIP_ONAY_NO,

 CAST(IP.ADI AS VARCHAR(30)) ADI,

 CAST(IP.SOYADI AS VARCHAR(30)) SOYADI,

 CAST(IP.ADISOYADI AS VARCHAR(80)) ADISOYADI,

 CAST(IP.ONCEKI_SOYADI AS VARCHAR(30)) ONCEKI_SOYADI,

 'G' PERSON_KAYIT_DURUMU,

 CAST(IP.EMAIL AS VARCHAR(250)) EMAIL,

 CAST(IP.BABAADI AS VARCHAR(30)) BABAADI,

 CAST(IP.ANAADI AS VARCHAR(30)) ANAADI,

 CAST(IP.DOGUM_YERI AS VARCHAR(30)) DOGUM_YERI,

 IP.DOGUM_TARIHI,

 CAST(IP.CINSIYETI AS VARCHAR(10)) CINSIYETI,

 CAST(IP.MEDENI_HALI AS VARCHAR(10)) MEDENI_HALI,

 CAST(IP.KAN_GRUBU AS VARCHAR(10)) KAN_GRUBU,

 CAST(IP.NU_IL AS VARCHAR(30)) NU_IL,

 CAST(IP.NU_ILCE AS VARCHAR(30)) NU_ILCE,

 CAST(IP.NU_MAHALLE_KOY AS VARCHAR(30)) NU_MAHALLE_KOY,

 CAST(IP.NU_SERI_NO AS VARCHAR(10)) NU_SERI_NO,

 CAST(IP.NU_CILT_NO AS VARCHAR(10)) NU_CILT_NO,

 CAST(IP.NU_SAYFA_NO AS VARCHAR(10)) NU_SAYFA_NO,

 CAST(IP.NU_SIRA_NO AS VARCHAR(10)) NU_SIRA_NO,

 CAST(IP.NU_DINI AS VARCHAR(30)) NU_DINI,

 CAST(IP.NU_CUZDAN_KAYIT_NO AS VARCHAR(24)) NU_CUZDAN_KAYIT_NO,

 IP.NU_CUZDAN_VERILIS_TARIHI,

 CAST(IP.NU_CUZDAN_VERILIS_NEDENI AS VARCHAR(30)) NU_CUZDAN_VERILIS_NEDENI,

 CAST(IP.NU_UYRUGU AS VARCHAR(40)) NU_UYRUGU,

 CAST(IP.ASKERLIK_DURUMU AS VARCHAR(30)) ASKERLIK_DURUMU,

  CAST(NULL AS DATE) ASKERLIK_TECIL_TARIHI,

 CAST(IP.SOSYAL_GUVENLIK_KURULUSU AS VARCHAR(80)) SOSYAL_GUVENLIK_KURULUSU,

 CAST(IP.SSK_NO AS VARCHAR(30)) SSK_NO,

 CAST(IP.GRBAS_TARIHI_ILK AS DATE) GRBAS_TARIHI_ILK,

 CAST(IP.GRBAS_TARIHI_ASLI AS DATE) GRBAS_TARIHI_SON,

 CAST(IP.OGRENIMI AS VARCHAR(30)) OGRENIMI,

 CAST(IP.ESKI_HUKUMLU AS VARCHAR(1)) ESKI_HUKUMLU,

 CAST(IP.DIGER_KURUM_HIZMETLERI AS VARCHAR(250)) DIGER_KURUM_HIZMETLERI,

 CAST(IP.EH_IL AS VARCHAR(30)) EH_IL,

 CAST(IP.EH_ILCE AS VARCHAR(30)) EH_ILCE,

 CAST(IP.EH_BELGE_NO AS VARCHAR(30)) EH_BELGE_NO,

 CAST(IP.EH_TARIH AS DATE) EH_TARIH,

 CAST(IP.EH_SINIFI AS VARCHAR(10)) EH_SINIFI,

 CAST(IP.VERGI_NO AS VARCHAR(30)) VERGI_NO,

 CAST(IP.VERGI_DAIRESI AS VARCHAR(30)) VERGI_DAIRESI,

 CAST(IP.NOTLAR AS VARCHAR(250)) NOTLAR,

 CAST(IP.TAKIP_NO AS VARCHAR(24)) ISYERI_TAKIP_NO,

 CAST(IP.SICIL_NO AS VARCHAR(24)) SICIL_NO,

 'H' CALISMA_DURUMU,

 CAST(IP.SSK_GOREV_KODU AS VARCHAR(30)) SSK_GOREV_KODU,

 CAST(NULL AS FTINT32) GECICI_ISLEM_NOKTASI_NO,

 CAST(NULL AS FTINT32) GECICI_ISLEM_NOKTASI_ADI,

 CAST(NULL AS FTINT32) FIILI_ISLEM_NOKTASI_NO,

 CAST(NULL AS DATE) ISE_BASLAMA_TARIHI,

 CAST(NULL AS DATE) ISE_ILK_BASLAMA_TARIHI,

 CAST(NULL AS DATE) ISTEN_AYRILIS_TARIHI,

 CAST(NULL AS FTINT32) CARI_NO,

 CAST(NULL AS VARCHAR(24)) TAKIP_CARI_KODU,

 CAST(IP.BANKA_ADI AS VARCHAR(40)) BANKA_ADI,

 CAST(REPLACE('',NULL,IP.BANKA_SUBE_NO) AS FTINT32) BANKA_SUBE_NO,

 CAST(IP.BANKA_HESAP_NO AS VARCHAR(40)) BANKA_HESAP_NUMARASI,

 CAST(NULL AS VARCHAR(40)) BANKA_IBAN_NUMARASI,

 CAST(IP.ODEME_SEKLI AS VARCHAR(30)) ODEME_SEKLI,

 CAST('E' AS VARCHAR(1)) OTO_ISYERI_PERSONEL_KAYIT,

 CAST(NULL AS FTINT32) FIRMA_SUBE_NO,

 CAST(F.FIRMA_ADI AS VARCHAR(100)) FIRMA_ISYERI_ADI,

 CAST(SUBSTRING(F.FIRMA_ADI FROM 1 FOR 30) AS VARCHAR(30)) FIRMA_ISYERI_KISA_ADI,

 CAST(NULL AS FTINT32) FIRMA_ISYERI_NO,

 CAST(F.FIRMA_KODU AS VARCHAR(24)) FIRMA_ISYERI_KODU,

 CAST(NULL AS FTINT32) ISLEM_NOKTASI_NO,

 CAST(GR.GOREV_ADI AS VARCHAR(80)) GOREV_ADI,

 CAST(GR.GOREV_KODU AS VARCHAR(24)) GOREV_KODU,

 CAST(NULL AS FTINT32) IK_GOREV_NO,

 CAST(UN.UNVAN_ADI AS VARCHAR(80)) UNVAN_ADI,

 CAST(UN.UNVAN_KISA_ADI AS VARCHAR(30)) UNVAN_KISA_ADI,

 CAST(UN.UNVAN_KODU AS VARCHAR(24)) UNVAN_KODU,

 CAST(NULL AS FTINT32) IK_UNVAN_NO,

 'O' ISYERI_KAYIT_DURUMU,

 'O' GOREV_KAYIT_DURUMU,

 CAST(GR.GRUBU AS VARCHAR(80)) GRUBU,

 CAST (GR.ACIKLAMA1 AS BLOB SUB_TYPE TEXT) GOREV_ACIKLAMA,

 CAST(NULL AS FTINT32) SIRA_NO,

 'O' UNVAN_KAYIT_DURUMU,

 CAST (UN.ACIKLAMA1 AS BLOB SUB_TYPE TEXT) UNVAN_ACIKLAMA,

 (SELECT FIRST 1 TARIH  FROM IKPERSHIST WHERE IKPERSONEL_NO = IP.IKPERSONEL_NO ORDER BY GRBAS_TARIHI_BULUNDUGU DESC) TARIH,

 'A' HAREKET_KAYIT_DURUMU,

 CAST(NULL AS VARCHAR(20)) TURU,

 (SELECT FIRST 1 ISTEN_AYRILIS_TARIHI  FROM IKPERSHIST H WHERE IKPERSONEL_NO = IP.IKPERSONEL_NO AND H.IKFIRMA_NO = IP.IKFIRMA_NO ORDER BY H.GRBAS_TARIHI_BULUNDUGU ) AYRILIS_TARIHI,

 (SELECT FIRST 1 GRBAS_TARIHI_BULUNDUGU  FROM IKPERSHIST H WHERE IKPERSONEL_NO = IP.IKPERSONEL_NO AND H.IKFIRMA_NO = IP.IKFIRMA_NO ORDER BY H.GRBAS_TARIHI_BULUNDUGU ) BASLAMA_TARIHI,

 CAST(NULL AS FTINT32) REFERANS_NO,

 (SELECT FIRST 1 ISTEN_AYRILMA_NEDENI  FROM IKPERSHIST H WHERE IKPERSONEL_NO = IP.IKPERSONEL_NO AND H.IKFIRMA_NO = IP.IKFIRMA_NO ORDER BY GRBAS_TARIHI_BULUNDUGU DESC) ISTEN_AYRILMA_NEDENI ,

 (SELECT FIRST 1 ACIKLAMA1  FROM IKPERSHIST H WHERE IKPERSONEL_NO = IP.IKPERSONEL_NO AND H.IKFIRMA_NO = IP.IKFIRMA_NO ORDER BY GRBAS_TARIHI_BULUNDUGU DESC) HAREKET_ACIKLAMA,

 'E' SGK_BILDIRGE_GONDERILDI,

 CAST(NULL AS FTINT32) ESKI_IK_GOREV_NO,

 CAST(NULL AS FTINT32) ESKI_ISLEM_NOKTASI_NO,

 CAST(NULL AS FTINT32) ESKI_FIRMA_ISYERI_NO,

 CAST(NULL AS FTINT32) YENI_IK_GOREV_NO,

 CAST(NULL AS FTINT32) YENI_ISLEM_NOKTASI_NO,

 CAST(NULL AS FTINT32) YENI_FIRMA_ISYERI_NO,

 CAST(AD.ADRES_ADI AS VARCHAR(80)) ADRES_ADI,

 'A' ADRES_KAYIT_DURUMU,

 CAST(AD.TURU AS VARCHAR(1)) ADRES_TURU,

 CAST(AD.ADI AS VARCHAR(26)) ADRES_AD,

 CAST(AD.SOYADI AS VARCHAR(26)) ADRES_SOYAD,

 CAST(AD.ADISOYADI AS VARCHAR(62)) ADRES_ADISOYAD,

 CAST(AD.ADRES1 AS VARCHAR(40)) ADRES1,

 CAST(AD.ADRES2 AS VARCHAR(40)) ADRES2,

 CAST(AD.ADRES3 AS VARCHAR(40)) ADRES3,

 CAST(AD.SEHIR AS VARCHAR(30)) SEHIR,

 CAST(AD.ILCE AS VARCHAR(30)) ILCE,

 CAST(AD.POSTA_KODU AS VARCHAR(12)) POSTA_KODU,

 CAST(AD.VERGI_DAIRESI AS VARCHAR(30)) ADRES_VERGI_DAIRESI,

 CAST(AD.VERGI_NO AS VARCHAR(30)) ADRES_VERGI_NO,

 AD.ADRES_NO,

 TL.VARSAYILAN,

 TF.TELEFON_NO,

 'A' TELEFON_KAYIT_DURUMU,

 CAST(TF.ALAN_KODU AS VARCHAR(10)) ALAN_KODU,

 CAST(TF.TELEFON AS VARCHAR(10)) TELEFON,

 CAST(TF.TELEFON_ADI AS VARCHAR(30)) TELEFON_ADI,

 CAST(TF.ULKE_KODU AS VARCHAR(10)) ULKE_KODU,

 CAST(TF.DAHILI AS VARCHAR(5)) DAHILI,

 CAST(TF.TURU AS VARCHAR(1)) TELEFON_TURU,

 'A' BORDRO_KAYIT_DURUMU,

 CAST (EXTRACT (YEAR FROM CURRENT_DATE)||'-01-01' AS DATE) ILK_TARIH,

 CAST (EXTRACT (YEAR FROM CURRENT_DATE)||'-12-31' AS DATE) SON_TARIH,

 'H' ASGARI_UCRET,

 B.UCRET,

 'TL' UCRET_DOVIZI,

 CASE

   WHEN (B.UCRET_BICIMI ='Günlük Net' OR B.UCRET_BICIMI ='Aylık Net' OR B.UCRET_BICIMI ='Saatlik Net') THEN 'N'

   WHEN (B.UCRET_BICIMI ='Günlük' OR B.UCRET_BICIMI ='Aylık Brüt' OR B.UCRET_BICIMI ='Saatlik') THEN 'B'

 END UCRET_NET_BRUT,

 CASE

   WHEN (B.UCRET_BICIMI ='Günlük Net' OR B.UCRET_BICIMI ='Günlük') THEN 'G'

   WHEN (B.UCRET_BICIMI ='Aylık Net' OR B.UCRET_BICIMI ='Aylık Brüt') THEN 'A'

   WHEN (B.UCRET_BICIMI ='Saatlik Net' OR B.UCRET_BICIMI ='Saatlik') THEN 'S'

 END UCRET_TURU,

 CAST(NULL AS FTSAYI) IKRAMIYE_ORANI,

 CAST(NULL AS VARCHAR(80)) IKRAMIYE_PERIYODU,

 CAST(FORMATFLOAT('00000', B.KanunNo) AS VARCHAR(24)) ISTIHDAM_YASASI,

 SUBSTRING(B.MESLEKKOD FROM 1 FOR 8) MESLEK_KODU,

 CAST(B.ENGELLILIK AS VARCHAR(24)) SAKATLIK_DERECESI,

 CASE

   WHEN (B.kismisurelimi ='E') THEN '2'

   ELSE '1'

 END CALISMA_SEKLI,

 '0' VERGI_GRUBU,

 CAST(NULL AS VARCHAR(24)) SENDIKA,

 SUBSTRING(B.SigortaTuru FROM 1 FOR POSITION('-',B.SigortaTuru )-2 ) SIGORTA_GRUBU,

 CASE

   WHEN (SigortaGrubu = 'Emekli') THEN '2019/1E'

   WHEN (SigortaGrubu = 'Normal Sigortalı') THEN '2019/1N'

   WHEN (SigortaGrubu = 'Bağ-Kur') THEN '2019/1Y'

   WHEN (SigortaGrubu = 'Çırak Sigortalı') THEN '2019/1S'

 END CPARAMETRE_KODU,

 'Yıllık İzin' TAKIP_TURU,

 (SELECT FIRST 1 YILI  FROM IKYILLIKIZIN WHERE IKPERSONEL_NO = IP.IKPERSONEL_NO ORDER BY YILI DESC) YILI,

 0 ONCEKI_SENEDEN_DEVIR,

 (SELECT FIRST 1 HAKKI  FROM IKYILLIKIZIN WHERE IKPERSONEL_NO = IP.IKPERSONEL_NO ORDER BY YILI DESC) HAKKI,

 (SELECT FIRST 1 KULLANILAN  FROM IKYILLIKIZIN WHERE IKPERSONEL_NO = IP.IKPERSONEL_NO ORDER BY YILI DESC) KULLANILAN,

 (SELECT FIRST 1 GECERSIZ  FROM IKYILLIKIZIN WHERE IKPERSONEL_NO = IP.IKPERSONEL_NO ORDER BY YILI DESC) GECERSIZ,

 (SELECT FIRST 1 KALAN  FROM IKYILLIKIZIN WHERE IKPERSONEL_NO = IP.IKPERSONEL_NO ORDER BY YILI DESC) KALAN,

 (SELECT FIRST 1 SENEYE_DEVIR  FROM IKYILLIKIZIN WHERE IKPERSONEL_NO = IP.IKPERSONEL_NO ORDER BY YILI DESC) SENEYE_DEVIR,

 B.BES_E_H BES_KESILECEK_MI,

 B.BES_KAT_OR BES_ORAN,

 B.BES_BAS_TAR BES_KATILIM_TARIHI,

 B.BES_BTS_TAR BES_AYRILIS_TARIHI,

 B.BES_STATU BES_STATU,

 B.BES_BTS_NEDENI BES_AYRILIS_NEDEN,

 CAST(NULL AS FTSAYI) HUZUR_HAKKI,

 (SELECT MAX(SAYI) FROM PBBORDDE BD

   JOIN PBALANLA A ON A.BORDRO_ALAN_NO = BD.BORDRO_ALAN_NO

   JOIN PBBORDRO PB ON PB.BORDRO_NO = BD.BORDRO_NO

   WHERE BD.SINTERNAL = 'PERS' AND BD.INTERNAL = IP.IKPERSONEL_NO AND A.DEGISKEN_ADI = 'SKGVM' and PB.ILK_TARIH >= '2023-04-01') KGVM,

 CAST(EXTRACT (YEAR FROM CURRENT_DATE) AS VARCHAR(4)) KGVM_GECERLILIK_YIL,

 CAST(NULL AS FTSAYI) HHNET_BURUT,

 'E' AUGVM_UYGULANIR,

 CAST(NULL AS FTSAYI) OSSGV_INDIRIMI,

 CAST(NULL AS FTSAYI) OSS_PRIM_TUTARI,

 CAST(NULL AS VARCHAR(6)) OSS_PRIM_DONEMI,

 CAST(NULL AS VARCHAR(30)) I4691_PERSONEL_TURU,

 P.PERIYOT_ADI

FROM IKPERSONEL IP

 JOIN IKPERSONEL_LIST PL ON PL.IKPERSONEL_NO = IP.IKPERSONEL_NO

 JOIN IKFIRMALAR F ON F.IKFIRMA_NO = IP.IKFIRMA_NO

 JOIN IKGOREVLER GR ON GR.IKGOREV_NO = IP.IKGOREV_NO

 JOIN IKUNVANLAR UN ON UN.UNVAN_NO = IP.UNVAN_NO

 JOIN (SELECT BD.INTERNAL,

        MIN(IIF(BA.DEGISKEN_ADI = 'Ucret',BD.SAYI, NULL)) UCRET,

        MIN(IIF(BA.DEGISKEN_ADI = 'UcretlendirmeBicimi',CAST(BD.YAZI30 AS VARCHAR(30)), NULL)) UCRET_BICIMI,

        MIN(IIF(BA.DEGISKEN_ADI = 'SigortaTuru',CAST(BD.YAZI100 AS VARCHAR(100)), NULL)) SigortaTuru,

        MIN(IIF(BA.DEGISKEN_ADI = 'SigortaGrubu',CAST(BD.YAZI30 AS VARCHAR(100)), NULL)) SigortaGrubu,

        MIN(IIF(BA.DEGISKEN_ADI = 'BelgeTuru', CAST(BD.YAZI100 AS VARCHAR(100)), NULL)) BelgeTuru,

        MIN(IIF(BA.DEGISKEN_ADI = 'KanunNo', CAST(BD.YAZI30 AS VARCHAR(30)), NULL)) KanunNo,

        MIN(IIF(BA.DEGISKEN_ADI = 'MESLEKKOD', CAST(BD.YAZI100 AS VARCHAR(100)), NULL)) MESLEKKOD,

        MIN(IIF(BA.DEGISKEN_ADI = 'SakatlikDerecesi', CAST(BD.YAZI30 AS VARCHAR(30)), NULL)) ENGELLILIK,

        MIN(IIF(BA.DEGISKEN_ADI = 'IstYasKanun', BD.YAZI50 , NULL)) IstYasKanun,

        MIN(IIF(BA.DEGISKEN_ADI = 'kismisurelimi', BD.YAZI50 , NULL)) kismisurelimi,

        MIN(IIF(BA.DEGISKEN_ADI = 'BES_E_H', CAST(BD.YAZI1 AS VARCHAR(1)), NULL)) BES_E_H,

        MIN(IIF(BA.DEGISKEN_ADI = 'BES_BAS_TAR', FormatDateTime('dd.MM.yyyy', BD.TARIH), NULL)) BES_BAS_TAR,

        MIN(IIF(BA.DEGISKEN_ADI = 'BES_BTS_TAR', FormatDateTime('dd.MM.yyyy', BD.TARIH), NULL)) BES_BTS_TAR,

        MIN(IIF(BA.DEGISKEN_ADI = 'BES_STATU',CAST(BD.YAZI100 AS VARCHAR(100)) , NULL)) BES_STATU,

        MIN(IIF(BA.DEGISKEN_ADI = 'BES_PER_ORAN',  BD.SAYI , NULL)) BES_KAT_OR,

        MIN(IIF(BA.DEGISKEN_ADI = 'BES_BTS_NEDENI',  BD.SAYI , NULL)) BES_BTS_NEDENI  

      FROM PBBORDDE BD

      JOIN PBALANLA BA ON BD.BORDRO_ALAN_NO = BA.BORDRO_ALAN_NO

        WHERE BD.SINTERNAL = 'PERS' AND BD.BORDRO_NO = 0

      GROUP BY 1) B ON IP.IKPERSONEL_NO = B.INTERNAL

   LEFT JOIN ADRELINK AL ON AL.SINTERNAL = 'IKPERS' AND AL.INTERNAL = IP.IKPERSONEL_NO AND AL.VARSAYILAN= 'E'

   LEFT JOIN ADRESLER AD ON AD.ADRES_NO = AL.ADRES_NO

   LEFT JOIN TELELINK TL ON TL.SINTERNAL = 'IKPERS' AND TL.INTERNAL = IP.IKPERSONEL_NO AND TL.VARSAYILAN= 'E'

   LEFT JOIN TELEFONL TF ON TF.TELEFON_NO = TL.TELEFON_NO

   LEFT JOIN PDKSUYPE UP ON UP.SINTERNAL = 'PERS' AND IP.IKPERSONEL_NO = UP.INTERNAL AND UP.PERIYOT_SINIF_NO = -1

   LEFT JOIN PDKSPERI P ON P.PERIYOT_NO = UP.PERIYOT_NO

   WHERE ((SELECT FIRST 1 ISTEN_AYRILIS_TARIHI  FROM IKPERSHIST H WHERE IKPERSONEL_NO = IP.IKPERSONEL_NO AND H.IKFIRMA_NO = IP.IKFIRMA_NO ORDER BY H.GRBAS_TARIHI_BULUNDUGU ) IS NULL

         OR (SELECT FIRST 1 ISTEN_AYRILIS_TARIHI  FROM IKPERSHIST H WHERE IKPERSONEL_NO = IP.IKPERSONEL_NO AND H.IKFIRMA_NO = IP.IKFIRMA_NO ORDER BY H.GRBAS_TARIHI_BULUNDUGU) >= '2024-04-01')

   )