-- Yorumlanan satırlara tarih filtresi getirilmeli
SELECT DISTINCT
CASE WHEN PER.TYP = 1 THEN 'Aktif' ELSE 'Pasif' END AS Durum,
PNT.PERDBEG [Puantaj Tarihi],
---UNT.NAME [Birim],
---PNT.PERDBEG [Dönem başlangıcı],
---PNT.PERDBEG [Bordro Tarihi],
---PNT.MNR [Ayi],
PNT.BALN_DAYGROSSWAGE [Günlük ücret],
PNT.BALN_HOURGROSSWAGE [Saatlik ücreti],
BALN_PAYMENTS_PTD [SGK Matrah],
PNT.BALN_SSKNBASEAP_PTD [Devredilebilir Ek Ödemelerden gelen SGK matrahı],
PNT.BALN_SSKNONTRNSAP_PTD [Devretmeyen ek ödemeler toplamı],
PNT.BALN_TAXFIX [Sabit GV],
PNT.BALN_ROUNDDIFF_PTD [Yuvarlama Farkı],
PNT.BALN_MINWAGEDISC [AGİ1],
PNT.BALN_SSKPRIMGOV [5510 - Hazine İndirimi],
PNT.SSKMDAYREASON [Eksik çalışma kodları],
PNT.BALN_SSKAPRMDIFF [İşv. yüklendiği kaza işçi prim tutarı],
PNT.BALN_SSKABASE_PTD [Kaza SSK matrahı],
PNT.BALN_SSKADAY_PTD [Kaza SSK gün sayısı],
--ISNULL((SELECT SUM(TAXEXCL) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG),0)-PNL_SSK_ISSIZLIK.TAXEXCL-ISNULL((SELECT SUM(TAXEXCL) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG AND TYP=5 AND NR=6),0) [İşveren SGK-ESKİ],
PNT.BALN_SSKNDAY_PTD*22.22 [657 Nolu TEŞVİK],
--(SELECT SUM(NETAM) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG AND TYP=6 AND NR IN (4,7)) [İşçi SGK],
ROUND(PNT.BALN_SSKPRIMUNEMP,2) [4857-Engelli],
--(SELECT SUM(NETAM) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG AND TYP=5 AND NR IN (1)) [Avans],
PNT.BALN_SSKNBASE_PTD*0.05 [İndirim %5],
STR(FRM.NR,1,1) + ' - ' + FRM.NAME [Firma],
PNT.ACTDATE [İşten çıkış],
PNT.WAGE_WAGE [Ücret],
PNT.BALN_ADDDDCTEPLE_PTD [Diğer Kesintiler],
PNT.BALN_SSKNBASEWG_PTD [Ücretden Gelen Kazançlar],
PNT.BALN_TAXNORM [Normal GV (AGİ'siz)],
PNT.BALN_TAXNBASE_YTD [Toplam Normal GV Matrahı],
PNT.BALN_TAXNORM_YTD [Ödenen Toplam GV],
PNT.BALN_TAXNORM [Ödenecek GV],
PNT.BALN_PAYMENTS_PTD [Brüt Kazançlar Toplamı],
PNT.BALN_STAMPBASE_PTD [DV matrahı],
PNT.BALN_NETWAGE [Net İstihkak],
PNT.BALN_NETDESERVE-PNT.BALN_MINWAGEDISC_PTD [Net Kazanç2],
--PNT.BALN_NETWAGE-PNT.BALN_MINWAGEDISC_PTD + ISNULL((SELECT SUM(NETAM) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG AND TYP=5 AND NR IN (1,5)),0) [Net Kazanç(Kesintisiz)],
PNT.BALN_TAXDISC_PTD [İşçi Kesintiler Taplam],
PNL_SSK_ISSIZLIK.TAXEXCL [İşveren İşsizlik1],
BALN_SSKNDAY_MTD [ek gün],
PNT.BALN_AUTOINDPEND_PTD [Zorunlu BES],
--(SELECT SUM(NETAM) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG AND TYP=5 AND NR IN (10)) [Bes],


FRM.NAME [Firma],
DIV.NAME [İşyeri],
MIDNAME AS Bölge,
PER.CODE [Sicil No],
PER.NAME + ' ' + PER.SURNAME [Ad Soyad],
PER.TTFNO [TC NO],
FINI.IbanNo as IBAN,
CASE FINI.SSKSTATUS
WHEN 1 THEN 'Normal' WHEN 2 THEN 'Emekli'end as SSK,
CASE PER.SPECODE
WHEN 1 THEN '1.ci Tur' WHEN 2 THEN '2.ci Tur'end as Ödeme,
PER.SPECIALCODE [ÖZEL KOD],
PER.OUTDATE [İşten çıkış tarihi],
CONVERT(DATETIME,PER.FIRMINDATE,101) AS [İşe Giriş Tarihi],
YEAR(PNT.PERDBEG) AS Yıl,
DATEPART (M, PNT.PERDBEG) AS Ay,
PNT.BALN_GROSSWAGE [Brüt ücreti],
PNT.BALN_SSKNDAY_PTD [Çalışma Günü],
PNT.BALN_WORKS_PTD [Mesai Kazanç toplamı],
PNT.BALN_ADDWORKS_PTD [Fazla Mesai toplamı],
PNT.BALN_ADDPAYMS_PTD [Ek ödemeler toplamı],
CASE PNT.SSKMDAYREASON
WHEN 21 THEN 'Ücretsiz izin' WHEN 01 THEN 'İstirahat' WHEN 12 THEN 'Birden Fazla' WHEN 13 THEN 'Diğer' end as [Eksik Gün],
ISNULL((SELECT SUM(GROSSAM) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG AND TYP=4 AND NR IN (7)),0) [İzin Ücreti(Brüt)],
PNT.BALN_TTFEXCLTOT [SGK Matrahı],
PNT.BALN_SSKNBASE_PTD [Normal SGK matrahı],
PNT.BALN_SECEPLE_PTD [İşçi Kesintileri Toplamı],
PNT.BALN_LAWDEDUCTS_PTD [Yasal kesintiler],
ISNULL((SELECT SUM(NETAM) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG AND TYP=6 AND NR IN (41,42,43,44)),0) [Gelir Vergisi],
PNT.BALN_TAXNBASE_PTD [Normal GV matrahı],
PNT.BALN_MINWAGEDISC_PTD [AGİ],
PNT.BALN_STAMP [DV],
PNT.BALN_NETDESERVE [Net Ödeme],
PNT.BALN_NETWAGE-PNT.BALN_MINWAGEDISC_PTD [Net Kazanç],
PNT.BALN_SECEPLR_PTD [İşveren Kesintiler Toplamı],
PNT.BALN_SSKPRIMGOV [5510 İndirim],
PNT.BALN_GROSSWAGE*0.155 [4857-Engelli],
CASE WHEN LAW.SSKDISCLAW=2 THEN PNT.BALN_SSKNBASE_PTD*0.155 WHEN LAW.SSKDISCLAW=8 THEN PNT.BALN_SSKNBASE_PTD*0.205 WHEN LAW.SSKDISCLAW=1 THEN PNT.BALN_SSKNBASE_PTD*0.245 END [4857],
CASE WHEN LAW.SSKDISCLAW=2 THEN PNT.BALN_SSKNBASE_PTD*0.155 END [4857],
ROUND(PNT.BALN_SSKPRIMGOVDEPLAW,2) [Özürlü %5 İndirimi],
PNT.BALN_ADDDDCTEPLE_PTD [Ek kesintiler işçi payı toplamı],
ISNULL((SELECT SUM(TAXEXCL) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG AND TYP=6 AND NR IN (6)),0) [İşveren İşsizlik],
ISNULL((SELECT SUM(NETAM) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG AND TYP=6 AND NR IN (6)),0) [İşçi İşsizlik],
(SELECT SUM(TAXEXCL+NETAM) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG AND TYP=6 AND NR IN (6)) [Toplam İşsizlik],
ISNULL((SELECT SUM(NETAM) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG AND TYP=6 AND NR IN (4,5,7)),0) [İşçi SGK],
ISNULL((SELECT SUM(TAXEXCL) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG AND TYP=6 AND NR IN (0,4,5,7)),0) [İşveren SGK],
(SELECT SUM(TAXEXCL+NETAM) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG AND TYP=6 AND NR IN (0,4,5,7)) [Toplam SGK],
PNT.BALN_SECEPLR_PTD+PNT.BALN_SECEPLE_PTD [TOPLAM PRİM],
(SELECT SUM(NETAM) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG AND TYP=5 AND NR IN (5,8)) [Maaş Haczi],
PNT.BALN_GROSSWAGE+PNT.BALN_SECEPLR_PTD [Maliyet],
PNT.BALN_PAYMENTS_PTD+PNT.BALN_SECEPLR_PTD-PNT.BALN_SSKPRIMGOV [Maliyet (İndirimli)],
--ISNULL((SELECT SUM(TAXEXCL) FROM LH_001_PNTLINE WHERE PERREF=PNT.PERREF AND PREF=PNT.LREF AND PERDBEG=PNT.PERDBEG AND TYP=6 AND NR IN (0,4,5,7)),0)-PNT.BALN_SSKPRIMGOV [İndirimli İşveren SGK],

PNL7.HOUR_ 'FM Saat (%150)',
PNL8.HOUR_ 'GT Saat (%100)',
PNL.DAY_ 'Normal Gün',
PNL2.DAY_ 'Hafta Tatili',
PNL3.DAY_ 'Genel Tatil',
PNL4.DAY_ 'İstirahat',
PNL5.DAY_ 'Ücretsiz İzin',
PNL9.DAY_ 'Toplam Gün'
FROM LH_001_PNTCARD PNT
LEFT JOIN LH_001_PERSON PER ON PER.LREF=PNT.PERREF
LEFT JOIN L_CAPIUNIT UNT ON UNT.NR=PER.UNITNR AND UNT.FIRMNR=PER.FIRMNR
LEFT JOIN L_CAPIFIRM FRM ON FRM.NR=PNT.FIRMNR
LEFT JOIN LH_001_PNTLINE PNL_SSK_ISSIZLIK ON PNL_SSK_ISSIZLIK.PERREF=PNT.PERREF AND PNL_SSK_ISSIZLIK.PERDBEG=PNT.PERDBEG AND PNL_SSK_ISSIZLIK.TYP=6 AND PNL_SSK_ISSIZLIK.NR=6
LEFT JOIN L_CAPIDIV DIV ON DIV.NR=PNT.LOCNR AND DIV.FIRMNR=PNT.FIRMNR
LEFT JOIN LH_001_PNTLINE PNL ON PNL.PERREF=PNT.PERREF AND PNL.PERDBEG=PNT.PERDBEG AND PNL.TYP=1 AND PNL.NR=1
LEFT JOIN LH_001_PNTLINE PNL2 ON PNL2.PERREF=PNT.PERREF AND PNL2.PERDBEG=PNT.PERDBEG AND PNL2.TYP=1 AND PNL2.NR=2
LEFT JOIN LH_001_PNTLINE PNL3 ON PNL3.PERREF=PNT.PERREF AND PNL3.PERDBEG=PNT.PERDBEG AND PNL3.TYP=1 AND PNL3.NR=3
LEFT JOIN LH_001_PNTLINE PNL4 ON PNL4.PERREF=PNT.PERREF AND PNL4.PERDBEG=PNT.PERDBEG AND PNL4.TYP=1 AND PNL4.NR=7
LEFT JOIN LH_001_PNTLINE PNL5 ON PNL5.PERREF=PNT.PERREF AND PNL5.PERDBEG=PNT.PERDBEG AND PNL5.TYP=1 AND PNL5.NR=6
LEFT JOIN LH_001_PNTLINE PNL7 ON PNL7.PERREF=PNT.PERREF AND PNL7.PERDBEG=PNT.PERDBEG AND PNL7.TYP=2 AND PNL7.NR=2
LEFT JOIN LH_001_PNTLINE PNL8 ON PNL8.PERREF=PNT.PERREF AND PNL8.PERDBEG=PNT.PERDBEG AND PNL8.TYP=2 AND PNL8.NR=4
LEFT JOIN LH_001_PNTLINE PNL9 ON PNL9.PERREF=PNT.PERREF AND PNL9.PERDBEG=PNT.PERDBEG AND PNL9.TYP=0 AND PNL9.NR=1
LEFT JOIN LH_001_PERFIN FINI ON (PER.LREF = FINI.PERREF)
LEFT JOIN LH_001_LAWCHG LAW ON LAW.PERREF=PER.LREF
order by PNT.PERDBEG