Genel Bilgiler
SQL Server
View
⚠️ Devirde Güncellenmeli
23.12.2025 07:15
23.12.2025 16:10
İstatistikler
0
Kod Analizi
307
Satır
1
Kullandığı
0
Kullanan
→ Kullandığı Nesneler (1)
USES_VIEW
← Kullanan Nesneler (0)
Bu nesneyi kullanan yok
→ Bağımlılık Ağacı
- ISKONTO_MARKAUSES_VIEW
← Etki Ağacı
Bu nesneyi kullanan yok
Tam SQL Kodu
CREATE VIEW dbo.Kampanyali_urunler
AS
SELECT *
FROM
(
----------------------------------------------------------------------
-- Ana Alt Sorgu Baslangici (TT)
----------------------------------------------------------------------
(
SELECT
-- Temel alanlar
ISLEM_TARIH,
IRSALIYE_NO,
FKODU,
FUNVANI,
MALZEME_KODU,
MALZEME,
MARKA,
MIKTAR,
-- Liste fiyati ve fiyatlar
ISNULL(LISTE_FIYATI, 0) AS LISTE_FIYATI,
FIYAT,
TOPLAM,
INDIRIM,
-- KDV'ye göre fis tipi seçimi
CASE
WHEN KDV = 0 THEN 'BEYAZ FIS'
ELSE 'FATURA'
END AS KDV,
-- Toplam tutari miktara bölerek satir bazinda birim tutar hesaplama
TOPLAM_TUTAR / MIKTAR AS TOPLAM_TUTAR,
-- Hesaplanan fiyat (null ise 0 dönülüyor)
ISNULL(HESAPLANAN, 0) AS HESAPLANAN,
-- Fark hesaplamasi (5 TL’nin altindaki farki 0, üstündekini mutlak degeriyle göster)
ISNULL
(
CASE
WHEN ABS(ROUND(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR), 2)) < 5
THEN 0
ELSE ABS(ROUND(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR), 2))
END
, 0) AS FARK,
-- Yüzde fark hesaplamasi (HESAPLANAN sifir veya null degilse)
ROUND
(
(
CASE
WHEN QQ.[FIYAT] IS NULL
OR QQ.[FIYAT] = 0 THEN 0
ELSE
(
ISNULL
(
CASE
WHEN ABS(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR)) < 5
THEN 0
ELSE ABS(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR))
END
, 0
)
/ (QQ.[HESAPLANAN])
)
END
) * 100
, 0) AS YUZDE,
-- Durum kolonu (FIYAT KARTI YOK / INDIRIMLI / YÜKSEK FIYAT / bos)
CASE
WHEN HESAPLANAN IS NULL
THEN 'FIYAT KARTI YOK'
ELSE
CASE
WHEN ABS(ROUND(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR), 2)) >= 0
AND ABS(ROUND(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR), 2)) < 5
THEN ''
WHEN ROUND(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR), 2) > 5
THEN 'INDIRIMLI'
ELSE 'YÜKSEK FIYAT'
END
END AS DURUM,
-- Iskonto kolonlari
ISNULL(CARI_ISKONTOSU, 0) AS CARI_ISKONTOSU,
ISNULL(MARKA_ISKONTOSU, 0) AS MARKA_ISKONTOSU,
ISNULL(FIS_ISKONTOSU1, 0) AS FIS_ISKONTOSU1,
ISNULL(FIS_ISKONTOSU2, 0) AS FIS_ISKONTOSU2,
ISNULL(PESIN_ISKONTO1, 0) AS PESIN_ISKONTO1,
ISNULL(PESIN_ISKONTO2, 0) AS PESIN_ISKONTO2,
ISNULL(TUM_ISK, 0) AS TUM_ISK,
SPECODE,
-- Gösterimi kontrol eden bayrak:
-- (Örnek logic: KDV=0, Yüzde=10 ve hesaplanan fark > 5 ise 0, aksi halde 1)
CASE
WHEN KDV = 0
AND ROUND
(
(
CASE
WHEN QQ.[FIYAT] IS NULL OR QQ.[FIYAT] = 0
THEN 0
ELSE
(
ISNULL
(
CASE
WHEN ABS(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR)) < 5
THEN 0
ELSE ABS(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR))
END
, 0
)
/ QQ.[HESAPLANAN]
)
END
) * 100
, 0) = 10
AND ROUND(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR), 2) > 5
THEN 0
ELSE 1
END AS GOSTER
FROM
(
----------------------------------------------------------------------
-- Ikinci Alt Sorgu (QQ)
-- Fiyat hesaplamalari, iskonto hesaplamalari, vb. yapiliyor.
----------------------------------------------------------------------
SELECT
STF.DATE_ AS ISLEM_TARIH,
STF.FICHENO AS IRSALIYE_NO,
CC.CODE AS FKODU,
CC.DEFINITION_ AS FUNVANI,
ITM.CODE AS MALZEME_KODU,
ITM.NAME AS MALZEME,
ITM.SPECODE4 AS MARKA,
STL.AMOUNT AS MIKTAR,
STL.PRICE AS FIYAT,
STL.TOTAL AS TOPLAM,
STL.DISTDISC AS INDIRIM,
STL.VATAMNT AS KDV,
STL.VATMATRAH AS TOPLAM_TUTAR,
-- HESAPLANAN fiyatin belirlenmesi
ROUND
(
CASE
WHEN ITM.SPECODE4 LIKE '%-T'
THEN
------------------------------------------------------------------------------
-- 1) MARKA Sonu '-T' Olanlar: Sadece %30 Indirim
------------------------------------------------------------------------------
CASE
WHEN STL.VATAMNT > 0
THEN
(
PRC.PRICE
* CASE
WHEN PRC.CURRENCY = 1 THEN
(
SELECT TOP (1) RATES1
FROM [UNIVERSALDB].[dbo].[L_DAILYEXCHANGES]
WHERE CRTYPE = 1
AND EDATE = STF.DATE_
)
ELSE 1
END
) * 0.70 -- => %30 indirim
ELSE
(
PRC.PRICE
* CASE
WHEN PRC.CURRENCY = 1 THEN
(
SELECT TOP (1) RATES1
FROM [UNIVERSALDB].[dbo].[L_DAILYEXCHANGES]
WHERE CRTYPE = 1
AND EDATE = STF.DATE_
)
ELSE 1
END
) * 0.70 *1.2 -- => %30 indirim
END
ELSE
------------------------------------------------------------------------------
-- 2) Diger Markalar: KDV Varsa/Yoksa Ayrimi
------------------------------------------------------------------------------
CASE
WHEN STL.VATAMNT > 0
THEN
-------------------------------------------------------
-- KDV Varsa
-------------------------------------------------------
(
(PRC.PRICE
* CASE
WHEN PRC.CURRENCY = 1 THEN
(
SELECT TOP (1) RATES1
FROM [UNIVERSALDB].[dbo].[L_DAILYEXCHANGES]
WHERE CRTYPE = 1
AND EDATE = STF.DATE_
)
ELSE 1
END
)
-- Cari iskonto + Pesin/Fis iskontolar + TUM
* (1 - (CC.DISCRATE / 100))
* (1 - (ISNULL(CAST(XT.PESIN AS FLOAT), 0) / 100))
* (1 - (ISNULL(CAST(XT.PESIN2 AS FLOAT), 0) / 100))
* (1 - (ISNULL(CAST(XT.FIS AS FLOAT), 0) / 100))
* (1 - (ISNULL(CAST(XT.FIS2 AS FLOAT), 0) / 100))
* (1 - (ISNULL(CAST(XT.TUM AS FLOAT), 0) / 100))
)
ELSE
-------------------------------------------------------
-- KDV Yoksa
-------------------------------------------------------
(
(
(PRC.PRICE
* CASE
WHEN PRC.CURRENCY = 1 THEN
(
SELECT TOP (1) RATES1
FROM [UNIVERSALDB].[dbo].[L_DAILYEXCHANGES]
WHERE CRTYPE = 1
AND EDATE = STF.DATE_
)
ELSE 1
END
)
* 1.2 -- KDV Yoksa 1.2 Çarpani (örnek: eskiden SART kosullarinda kullaniliyordu)
)
* (1 - (CC.DISCRATE / 100))
* (1 - (ISNULL(CAST(XT.PESIN AS FLOAT), 0) / 100))
* (1 - (ISNULL(CAST(XT.PESIN2 AS FLOAT), 0) / 100))
* (1 - (ISNULL(CAST(XT.FIS AS FLOAT), 0) / 100))
* (1 - (ISNULL(CAST(XT.FIS2 AS FLOAT), 0) / 100))
* (1 - (ISNULL(CAST(XT.TUM AS FLOAT), 0) / 100))
)
END
END
, 2) AS HESAPLANAN,
-- Liste fiyati (Kuru bilgisine göre çevirim yapiliyor)
PRC.PRICE
*
(
CASE
WHEN PRC.CURRENCY = 1 THEN
(
SELECT TOP (1) [RATES1]
FROM [UNIVERSALDB].[dbo].[L_DAILYEXCHANGES]
WHERE CRTYPE = 1 AND EDATE = STF.DATE_
)
ELSE 1
END
) AS LISTE_FIYATI,
-- Iskonto oranlari
CC.DISCRATE AS CARI_ISKONTOSU,
ISK.ISKONTO AS MARKA_ISKONTOSU,
XT.FIS AS FIS_ISKONTOSU1,
XT.FIS2 AS FIS_ISKONTOSU2,
XT.PESIN AS PESIN_ISKONTO1,
XT.PESIN2 AS PESIN_ISKONTO2,
XT.TUM AS TUM_ISK,
-- Specode
STL.SPECODE
FROM dbo.LG_011_08_STLINE AS STL
LEFT OUTER JOIN dbo.LG_011_08_STFICHE AS STF ON STF.LOGICALREF = STL.STFICHEREF
LEFT OUTER JOIN dbo.LG_011_CLCARD AS CC ON CC.LOGICALREF = STL.CLIENTREF
LEFT OUTER JOIN dbo.LG_XT1015_011 AS XT ON XT.PARLOGREF = STL.CLIENTREF
LEFT OUTER JOIN dbo.LG_011_ITEMS AS ITM ON ITM.LOGICALREF = STL.STOCKREF
LEFT OUTER JOIN dbo.ISKONTO_MARKA AS ISK ON ISK.LOGICALREF = STL.STOCKREF
LEFT OUTER JOIN dbo.LG_011_PRCLIST AS PRC ON PRC.CARDREF = STL.STOCKREF
AND PRC.PTYPE = 2
WHERE
STL.TRCODE = 8 -- Irsaliye tipi
AND STL.CANCELLED = 0 -- Iptal edilmemis satirlar
AND STL.LINETYPE = 0 -- Normal satir
AND ITM.CODE <> 'A01-NAK'
) AS QQ
-- Filtre: Ya HESAPLANAN bos (null) olacak, ya da fiili fiyat ile hesaplanan fiyat arasinda
-- 5 TL'den fazla fark olacak.
WHERE (HESAPLANAN IS NULL)
OR (ABS(HESAPLANAN - TOPLAM_TUTAR / MIKTAR) > 5)
)
----------------------------------------------------------------------
-- Ana Alt Sorgu Bitisi (TT)
----------------------------------------------------------------------
) TT
-- Son filtre: GOSTER=1 olan kayitlari getir.
WHERE TT.GOSTER = 1
AND
TT.SPECODE = 1