← Detay Sayfası
Nesne Düzenle
|
GUNLUK_SATIS_ISLEMLERI_RAPORU
İ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.GUNLUK_SATIS_ISLEMLERI_RAPORU AS SELECT * FROM (/*--------------------------------------------------------------------*/ (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, /* (Ö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 (/*--------------------------------------------------------------------*/ 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 /*----------------------------------------------------------------------------*/ 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 /*----------------------------------------------------------------------------*/ 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) /* 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 /*-----------------------------------------------------*/ (((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 /* 5 TL'den fazla fark olacak.*/ WHERE (HESAPLANAN IS NULL) OR (ABS(HESAPLANAN - TOPLAM_TUTAR / MIKTAR) > 5))/*--------------------------------------------------------------------*/ ) TT /* Son filtre: GOSTER=1 olan kayitlari getir.*/ WHERE TT.GOSTER = 1 AND TT.SPECODE = '';
Oluşturulma: 23.12.2025 07:15
Son Güncelleme: 23.12.2025 17:28
İptal
Nesneyi Sil
Değişiklikleri Kaydet