← Detay Sayfası
Nesne Düzenle
|
FIS_HESABI_AYSONU_AKTARIM
İlgili Uygulama
-- Seçiniz --
SQL Server
Nesne Türü
View
Stored Procedure
Trigger
Job
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.FIS_HESABI_AYSONU_AKTARIM AS SELECT * FROM (SELECT QQ.CLIENTREF, CC.CODE AS [FIRMA KODU], CC.DEFINITION_ AS [FIRMA ÜNVANI], UPPER(CC.CITY) AS [SEHIR], UPPER(CC.SPECODE2) AS PLASIYER, CASE WHEN LEN(CC.CODE) <= 10 AND ISNULL ((SELECT TOP 1 BAKIYE FROM dbo.FIS_HESABI_CARI_BAKIYELER AS FHC WHERE FHC.REV = CC.CODE AND FHC.CODE NOT LIKE '%SB%' AND FHC.CODE NOT LIKE '%2S%' AND FHC.CODE NOT LIKE '%3S%' AND FHC.CODE NOT LIKE '%SU%' AND FHC.CODE NOT LIKE '%SB%' AND FHC.CODE NOT LIKE '%ITH%' AND FHC.CODE NOT LIKE '%S.%' AND FHC.CODE NOT LIKE '%-S%' AND FHC.CODE NOT LIKE '%GS%' AND FHC.CODE NOT LIKE '%M%' AND FHC.CODE NOT LIKE '%Ü%' ORDER BY BAKIYE DESC), 0) > 0 THEN ISNULL ((SELECT TOP 1 ABS(BAKIYE) FROM dbo.FIS_HESABI_CARI_BAKIYELER AS FHC WHERE FHC.REV = CC.CODE AND FHC.CODE NOT LIKE '%SB%' AND FHC.CODE NOT LIKE '%2S%' AND FHC.CODE NOT LIKE '%3S%' AND FHC.CODE NOT LIKE '%SU%' AND FHC.CODE NOT LIKE '%SB%' AND FHC.CODE NOT LIKE '%ITH%' AND FHC.CODE NOT LIKE '%S.%' AND FHC.CODE NOT LIKE '%-S%' AND FHC.CODE NOT LIKE '%GS%' AND FHC.CODE NOT LIKE '%M%' AND FHC.CODE NOT LIKE '%Ü%' ORDER BY BAKIYE DESC), 0) WHEN LEN(CC.CODE) > 10 AND QQ.BAKIYE < 0 THEN ABS(QQ.BAKIYE) ELSE 0 END AS [ALACAK], CASE WHEN LEN(CC.CODE) <= 10 AND ISNULL ((SELECT TOP 1 BAKIYE FROM dbo.FIS_HESABI_CARI_BAKIYELER AS FHC WHERE FHC.REV = CC.CODE AND FHC.CODE NOT LIKE '%SB%' AND FHC.CODE NOT LIKE '%2S%' AND FHC.CODE NOT LIKE '%3S%' AND FHC.CODE NOT LIKE '%SU%' AND FHC.CODE NOT LIKE '%SB%' AND FHC.CODE NOT LIKE '%ITH%' AND FHC.CODE NOT LIKE '%S.%' AND FHC.CODE NOT LIKE '%-S%' AND FHC.CODE NOT LIKE '%GS%' AND FHC.CODE NOT LIKE '%M%' AND FHC.CODE NOT LIKE '%Ü%' ORDER BY BAKIYE ASC), 0) < 0 THEN (SELECT TOP 1 ABS(BAKIYE) FROM dbo.FIS_HESABI_CARI_BAKIYELER AS FHC WHERE FHC.REV = CC.CODE AND FHC.CODE NOT LIKE '%SB%' AND FHC.CODE NOT LIKE '%2S%' AND FHC.CODE NOT LIKE '%3S%' AND FHC.CODE NOT LIKE '%SU%' AND FHC.CODE NOT LIKE '%SB%' AND FHC.CODE NOT LIKE '%ITH%' AND FHC.CODE NOT LIKE '%S.%' AND FHC.CODE NOT LIKE '%-S%' AND FHC.CODE NOT LIKE '%GS%' AND FHC.CODE NOT LIKE '%M%' AND FHC.CODE NOT LIKE '%Ü%' ORDER BY BAKIYE ASC) WHEN LEN(CC.CODE) > 10 AND QQ.BAKIYE > 0 THEN ABS(QQ.BAKIYE) ELSE 0 END AS [BORÇ], ABS(QQ.BAKIYE) AS BAKIYE, CASE WHEN QQ.BAKIYE > 0 THEN 'ALACAK' WHEN QQ.BAKIYE = 0 THEN '' ELSE 'BORÇ' END AS DURUM FROM (SELECT CLIENTREF, SUM(ALACAK) - SUM(BORC) AS BAKIYE FROM ((SELECT CLIENTREF, TRNET AS BORC, 0 AS ALACAK FROM LG_011_08_CLFLINE WHERE [SIGN] = 0 AND CANCELLED = 0) UNION ALL (SELECT CLIENTREF, 0 AS BORC, TRNET AS ALACAK FROM LG_011_08_CLFLINE WHERE [SIGN] = 1 AND CANCELLED = 0)) KK GROUP BY CLIENTREF) AS QQ LEFT OUTER JOIN LG_011_CLCARD AS CC ON CC.LOGICALREF = QQ.CLIENTREF WHERE CC.ACTIVE = 0 AND REPLACE(SUBSTRING(CC.CODE, 0, 11), ' ', '') IN (SELECT DISTINCT REV FROM dbo.FIS_HESABI_CARI_BAKIYELER) AND CC.LOGICALREF NOT IN (875, 1197, 1939, 1753, 2219, 2531, 261, 2456) AND REPLACE(SUBSTRING(CC.CODE, 0, 11), ' ', '') NOT IN ('67,000,001', '35,000,182', '34,000,273', '34,000,234', '34,000,472', '34,000,387', '34,000,212', '34,000,145', '34,000,145', '35,000,188/M.')) TT WHERE TT.[FIRMA KODU] NOT IN (SELECT FHC.CODE FROM dbo.FIS_HESABI_CARI_BAKIYELER AS FHC WHERE LEN(FHC.CODE) > 10 AND FHC.BAKIYE = 0) AND REPLACE(SUBSTRING(TT.[FIRMA KODU], 0, 11), ' ', '') NOT IN (SELECT DISTINCT REV FROM dbo.FIS_HESABI_CARI_BAKIYELER AS FHC WHERE LEN(FHC.CODE) > 10 AND FHC.BAKIYE BETWEEN - 5 AND 5)
Oluşturulma: 23.12.2025 07:15
Son Güncelleme: 23.12.2025 17:28
İptal
Nesneyi Sil
Değişiklikleri Kaydet