← Detay Sayfası
Nesne Düzenle
|
MALZEME_LISTESI_HIZLI
İ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.MALZEME_LISTESI_HIZLI AS SELECT * FROM ((SELECT UML.CODE, ITM.CODE AS CODE2, CASE WHEN ITM.[NAME] IS NULL THEN ITS.ITEMNAME ELSE CONCAT(CONCAT(ITM.[NAME], ' '), ITM.[NAME3]) END AS ITEMNAME, UML.ITEMNAME_EN, CASE WHEN ITM.STGRPCODE IS NULL THEN ITS.GROUPCODE ELSE ITM.STGRPCODE END AS GROUPCODE, ISNULL(PRD.PRODUCERCODE, '') AS PROCODE, ISNULL(PRD2.PRODUCERCODE, '') AS PROCODE2, UML.FIRMCODE, UML.ALIMFIYAT, UML.ALIMKUR, UML.LASTPRICE, UML.LASTCURRENCY, UML.SALEPRICE, ITS.LINEEXP1, ITS.LINEEXP2, UML.IHRSAT, UML.IHRCURRENCY, UML.SALECURRENCY, UML.ORTALAMA, UML.STOCK, UML.EKSIK, UML.ADET2022, UML.FAT2022, UML.ADET2021, UML.FAT2021, MSG.DURUM, MSG.MIKTAR, ISNULL ((SELECT COUNT(*) AS Expr1 FROM dbo.FICHE_LINES AS FCL WHERE (STATUS NOT IN (1, 2)) AND (ITEMREF = ITS.REF)), 0) AS SATIR, MSG.SCOMP, ITS.BCOLOR, UML.SONIKI, UML.ITOP, ITS.REF, CASE WHEN ITM.ACTIVE = 1 OR UML.PRICE IS NULL THEN 'YOK' ELSE 'VAR' END AKTIF, ITM.LOGICALREF, ISNULL(UML.[AIDE2022], 0) AS [AIDE2022], ITS.BENZER, XT.ETIKET_ACIKLAMA, XT.MODEL_BILGI, UML.ADET2020, ROUND(UML.KAMPANYATL, 2) AS KAMPANYATL, ROUND(UML.KAMPANYAUSD, 2) AS KAMPANYAUSD, SP.TOPLAM_SIPARIS AS AMOUNT, SP.KULLANICILAR AS KULL, ITM.EXPCTGNO, ITM.UETDSUNDEF, ITS.ALTERNATIF_FIYAT, ITS.ALTERNATIF_FIRMA FROM dbo.ITEMS AS ITS LEFT OUTER JOIN dbo.[1aUNI_malzeme_listesi] AS UML ON UML.REF = ITS.REF LEFT OUTER JOIN dbo.MALZEME_URETICI_KOD_LISTESI AS PRD ON PRD.REF = ITS.REF AND PRD.SIRA = 1 AND PRD.STS = 1 LEFT OUTER JOIN dbo.MALZEME_URETICI_KOD_LISTESI AS PRD2 ON PRD2.REF = ITS.REF AND PRD2.SIRA = 1 AND PRD2.STS = 2 LEFT OUTER JOIN UNIVERSALDB.dbo.LG_011_ITEMS AS ITM ON ITM.LOGICALREF = ITS.LOGICALREF LEFT OUTER JOIN dbo.MALZEME_SIPARIS_GECMISI AS MSG ON MSG.ITEMREF = ITS.REF AND MSG.ORD = 1 LEFT OUTER JOIN UNIVERSALDB.dbo.LG_XT1001_011 AS XT ON XT.PARLOGREF = ITM.LOGICALREF LEFT OUTER JOIN UNIVERSALDB.dbo.STOK_RAPOR_SIPARISLER_TOPLAM_DEGER AS SP ON SP.STOCKREF = ITS.LOGICALREF AND SP.STOCKREF <> 0 WHERE ITS.SIL = 0 AND (UML.CODE IS NOT NULL AND UML.CODE <> '' OR ITM.CODE IS NOT NULL AND ITM.CODE <> '')) UNION ALL (SELECT '' AS CODE, ITM.CODE AS CODE2, CONCAT(CONCAT(ITM.[NAME], ' '), ITM.NAME3) AS ITEMNAME, '' AS ITEMNAME_EN, STGRPCODE AS GROUPCODE, '' AS PROCODE, '' AS PROCCODE2, '' AS FIRMCODE, 0 AS ALIMFIYAT, '' AS ALIMKUR, 0 AS LASTPRICE, '' AS LASTCURRENCY, ISNULL(PRI.PRICE, 0) AS SALEPRICE, '' AS LINEEXP1, '' AS LINEEXP2, 0 AS IHRSAT, '' AS IHRCURRENCY, CASE WHEN PRI.CURRENCY = 1 THEN 'USD' WHEN PRI.CURRENCY = 13 THEN 'CNY' WHEN PRI.CURRENCY = 20 THEN 'EUR' WHEN PRI.CURRENCY = 160 THEN 'TL' ELSE 'USD' END AS SALECURRENCY, ISNULL(ORT.ORTALAMA, 0) AS ORTALAMA, ISNULL(GNT.ONHAND - GNT.RESERVED - GNT.TEMPIN, 0) AS STOCK, ISNULL(ORT.ORT_EKSIK, 0) AS EKSIK, ISNULL(TFS22.ADET, 0) AS ADET2022, ISNULL(TFS22.FAT, 0) AS FAT2022, ISNULL(TFS21.ADET, 0) AS ADET2021, ISNULL(TFS21.FAT, 0) AS FAT2021, MSG.DURUM, MSG.MIKTAR, 0 AS SATIR, MSG.SCOMP, '' AS BCOLOR, ISNULL(SSM.SONIKI, 0) AS SONIKI, ISNULL(SSM.TOPLAM, 0) AS ITOP, 0 AS REF, 'EKSIK' AS AKTIF, ITM.LOGICALREF, ISNULL(UML.[AIDE2022], 0) AS [AIDE2022], '' AS BENZER, XT.ETIKET_ACIKLAMA, XT.MODEL_BILGI, ISNULL(TFS20.ADET, 0) AS ADET2020, ROUND(UML.KAMPANYATL, 2) AS KAMPANYATL, ROUND(UML.KAMPANYAUSD, 2) AS KAMPANYAUSD, SP.TOPLAM_SIPARIS AS AMOUNT, SP.KULLANICILAR AS KULL, ITM.EXPCTGNO, ITM.UETDSUNDEF, NULL AS ALTERNATIF_FIYAT, NULL AS ALTERNATIF_FIRMA FROM [UNIVERSALDB].[dbo].[LG_011_ITEMS] AS ITM LEFT OUTER JOIN dbo.[1aUNI_malzeme_listesi] AS UML ON UML.REF = ITM.LOGICALREF LEFT OUTER JOIN UNIVERSALDB.dbo.LV_011_08_GNTOTST AS GNT ON GNT.STOCKREF = ITM.LOGICALREF AND GNT.INVENNO = - 1 LEFT OUTER JOIN UNIVERSALDB.dbo.LG_011_PRCLIST AS PRI ON PRI.CARDREF = ITM.LOGICALREF AND PRI.PTYPE = 2 AND PRI.PRIORITY = 0 LEFT OUTER JOIN UNIVERSALDB.dbo.[00001_SATIS_IADELER] AS SSM ON SSM.STOCKREF = ITM.LOGICALREF LEFT OUTER JOIN dbo.TOPLAM_SATIS_ADETLERI_VE_FATURA_SAYILARI_22 AS TFS22 ON TFS22.STOCKREF = ITM.LOGICALREF AND TFS22.YEAR_ = 2025 LEFT OUTER JOIN dbo.TOPLAM_SATIS_ADETLERI_VE_FATURA_SAYILARI_21 AS TFS21 ON TFS21.STOCKREF = ITM.LOGICALREF AND TFS21.YEAR_ = 2024 LEFT OUTER JOIN dbo.TOPLAM_SATIS_ADETLERI_VE_FATURA_SAYILARI_20 AS TFS20 ON TFS20.STOCKREF = ITM.LOGICALREF AND TFS20.YEAR_ = 2023 LEFT OUTER JOIN UNIVERSALDB.dbo.[1aUNI_ortalama_eksik] AS ORT ON ORT.STOCKREF = ITM.LOGICALREF LEFT OUTER JOIN dbo.MALZEME_SIPARIS_GECMISI AS MSG ON MSG.LOGICALREF = ITM.LOGICALREF AND MSG.ORD = 1 LEFT OUTER JOIN UNIVERSALDB.dbo.LG_XT1001_011 AS XT ON XT.PARLOGREF = ITM.LOGICALREF LEFT OUTER JOIN UNIVERSALDB.dbo.STOK_RAPOR_SIPARISLER_TOPLAM_DEGER AS SP ON SP.STOCKREF = ITM.LOGICALREF AND SP.STOCKREF <> 0 WHERE ITM.CODE NOT IN (SELECT CODE2 FROM [IHR].[dbo].[MALZEME_LISTESI_ENSON] WHERE CODE2 IS NOT NULL AND CODE2 <> '') AND ITM.ACTIVE = 0 AND ITM.[LOGICALREF] IN (SELECT DISTINCT CARDREF FROM [UNIVERSALDB].[dbo].[LG_011_PRCLIST] WHERE PTYPE = 2))) QQ
Oluşturulma: 23.12.2025 15:52
Son Güncelleme: 23.12.2025 17:28
İptal
Nesneyi Sil
Değişiklikleri Kaydet