Düzenle: 001_PLASIYER_GIDER_LISTESI

İlgili Uygulama

Nesne Türü

Nesne Adı

Bu nesne dönem devrinden etkileniyor mu? (Örn: İçinde _01_ gibi sabit dönem kodu var mı?)

Genel Kullanım Amacı ve Notlar

Tam SQL Kodu

CREATE VIEW dbo.[001_PLASIYER_GIDER_LISTESI]
AS
SELECT        *
FROM            ((SELECT        KSL.DATE_ AS TARIH, KSL.LINEEXP AS ACK, KSL.TRNET AS TUTAR, SP2.DEFINITION_ AS PLAS, SP1.DEFINITION_ AS GIDER, MONTH(DATE_) AS AY, YEAR(DATE_) AS [YIL], 'KASA' AS TUR, 
                                                     CASE WHEN KSL.CUSTTITLE LIKE '%GENEL GIDER%' THEN '' ELSE KSL.CUSTTITLE END AS [DEF]
                            FROM            LG_011_08_KSLINES AS KSL LEFT OUTER JOIN
                                                     LG_011_SPECODES AS SP1 ON SP1.SPECODE = KSL.SPECODE AND SP1.CODETYPE = 1 AND SP1.SPECODETYPE = 44 LEFT OUTER JOIN
                                                     LG_011_SPECODES AS SP2 ON SP2.SPECODE = KSL.CYPHCODE AND SP2.CODETYPE = 2 AND SP2.SPECODETYPE = 44
                            WHERE        KSL.TRCODE = 12 AND KSL.SPECODE <> '' AND KSL.CYPHCODE <> '')

UNION ALL
                         (SELECT        CLF.DATE_ AS TARIH,CC.DEFINITION_ +' '+ CLF.LINEEXP AS ACK, CLF.TRNET AS TUTAR, 'SIRKET'  AS PLAS, 'MAAS ÖDEME' AS GIDER, MONTH(DATE_) 
                                                   AS AY, YEAR(DATE_) AS YIL, 'KASA' AS TUR, CC.DEFINITION_ AS [DEF]
                          FROM            LG_011_08_CLFLINE AS CLF LEFT OUTER JOIN
                                                   LG_011_CLCARD AS CC ON CC.LOGICALREF = CLF.CLIENTREF
                          WHERE        CLF.TRCODE = 4 AND CLF.CANCELLED = 0 AND (CLF.LINEEXP LIKE '%MAAS%'))

UNION ALL
(SELECT        CLF.DATE_ AS TARIH, CLF.LINEEXP AS ACK, CLF.TRNET AS TUTAR, CASE WHEN CC.DEFINITION_ = 'MURAT ÜZÜM' THEN 'MURAT ÜZÜM' WHEN CC.DEFINITION_ = 'MURAT TURHAN' THEN NULL WHEN LEFT(CC.DEFINITION_,
                           CHARINDEX(' ', CC.DEFINITION_) - 1) = 'Ali' THEN 'Emre' ELSE LEFT(CC.DEFINITION_, CHARINDEX(' ', CC.DEFINITION_) - 1) END AS PLAS, 'YEMEK' AS GIDER, MONTH(DATE_) AS AY, YEAR(DATE_) AS YIL, 'KASA' AS TUR, 
                          CC.DEFINITION_ AS [DEF]
 FROM            LG_011_08_CLFLINE AS CLF LEFT OUTER JOIN
                          LG_011_CLCARD AS CC ON CC.LOGICALREF = CLF.CLIENTREF
 WHERE        CLF.TRCODE = 4 AND CLF.CANCELLED = 0 AND (CLF.LINEEXP LIKE '%YEMEK%'))
UNION ALL
(SELECT        BNL.DATE_ AS TARIH, BNL.GENEXP1 AS ACK, BNL.CREDITTOT AS TUTAR, SP2.DEFINITION_ AS PLAS, SP1.DEFINITION_ AS GIDER, MONTH(DATE_) AS AY, YEAR(DATE_) AS [YIL], 'BANKA' AS TUR, '' AS [DEF]
 FROM            LG_011_08_BNFICHE AS BNL LEFT OUTER JOIN
                          LG_011_SPECODES AS SP1 ON SP1.SPECODE = BNL.SPECODE AND SP1.CODETYPE = 1 AND SP1.SPECODETYPE = 43 LEFT OUTER JOIN
                          LG_011_SPECODES AS SP2 ON SP2.SPECODE = BNL.CYPHCODE AND SP2.CODETYPE = 2 AND SP2.SPECODETYPE = 43
 WHERE        BNL.SPECODE <> '' AND BNL.CYPHCODE <> '')
UNION ALL
(SELECT        CSR.DATE_ AS TARIH, CSR.GENEXP1 AS ACK, CSR.TRNET AS TUTAR, SP2.DEFINITION_ AS PLAS, SP1.DEFINITION_ AS GIDER, MONTH(DATE_) AS AY, YEAR(DATE_) AS [YIL], 'ÇEK' AS TUR, CC.DEFINITION_ AS [DEF]
 FROM            LG_011_07_CSROLL AS CSR LEFT OUTER JOIN
                          LG_011_CLCARD AS CC ON CC.LOGICALREF = CSR.CARDREF LEFT OUTER JOIN
                          LG_011_SPECODES AS SP1 ON SP1.SPECODE = CSR.SPECODE AND SP1.CODETYPE = 1 AND SP1.SPECODETYPE = 30 LEFT OUTER JOIN
                          LG_011_SPECODES AS SP2 ON SP2.SPECODE = CSR.CYPHCODE AND SP2.CODETYPE = 2 AND SP2.SPECODETYPE = 30
 WHERE        CSR.SPECODE <> '' AND CSR.CYPHCODE <> '')
UNION ALL
(SELECT        CAST(TRR.IslemTarihi AS DATE) AS TARIH, TRR.IstasyonAdi AS ACK, TRR.Tutar AS TUTAR, PRP.PLAS, 'YAKIT' AS GIDER, MONTH(TRR.IslemTarihi) AS AY, YEAR(TRR.IslemTarihi) AS [YIL], 'KASA' AS TUR, '' AS DEF
 FROM            dbo.TuketimRaporu AS TRR LEFT OUTER JOIN
                          dbo.PLAS_RAPOR_PLAKALAR AS PRP ON PRP.PLAKA = TRR.Plaka
 WHERE        PRP.PLAS IS NOT NULL)
UNION ALL
(SELECT        TTR.DATE_ AS TARIH, CONCAT(MONTH(TTR.DATE_), '. Ay Turkcell Fatura Ödeme') AS ACK, TTR.TRNET AS TUTAR, PRP.PLAS, 'TELEKOM ÖDEME' AS GIDER, MONTH(TTR.DATE_) AS AY, YEAR(TTR.DATE_) AS YIL, 
                          'BANKA' AS TUR, '' AS DEF
 FROM            (SELECT        LEFT(BNL.LINEEXP, CHARINDEX(' - ', BNL.LINEEXP)) AS ACK, TRNET, DATE_
                           FROM            LG_011_08_BNFLINE AS BNL
                           WHERE        BNL.TRCODE = 4 AND BNL.CANCELLED = 0 AND LINEEXP LIKE '%Turkcell%') AS TTR LEFT OUTER JOIN
                          dbo.PLAS_RAPOR_PLAKALAR AS PRP ON PRP.TELEFON = TTR.ACK
 WHERE        PRP.PLAS IS NOT NULL)) QQ
WHERE        QQ.YIL = 2025 AND (QQ.ACK NOT LIKE '%prim%');

Oluşturulma: 23.12.2025 07:15 Son Güncelleme: 23.12.2025 17:28

İptal Nesneyi Sil