00001_SON_ALIM_LISTESI

Genel Bilgiler

Uygulama SQL Server

Nesne Türü View

Dönem Hassasiyeti ⚠️ Devirde Güncellenmeli

Oluşturulma 23.12.2025 07:15

Son Güncelleme 23.12.2025 16:10

Notlar

Düzenle

Henüz not eklenmemiş.

+ Not ekle

İstatistikler

Kod Analizi

Satır

Kullandığı

Kullanan

→ Kullandığı Nesneler (0)

Bağımlılık bulunamadı

← Kullanan Nesneler (3)

👁️ GENEL_EKSIK_SIPARIS

USES_VIEW

⏰ STR_GUNCELLE

USES_VIEW

⏰ STR_GUNCELLE

USES_VIEW

→ Bağımlılık Ağacı

Bağımlılık bulunamadı

← Etki Ağacı

👁️GENEL_EKSIK_SIPARISUSES_VIEW
⏰STR_GUNCELLEUSES_VIEW
⏰STR_GUNCELLEUSES_VIEW

Tam SQL Kodu

Düzenle

CREATE VIEW dbo.[00001_SON_ALIM_LISTESI]
AS
SELECT        STOCKREF, AMOUNT, DATE_, ROW_NUMBER() OVER (PARTITION BY STOCKREF
ORDER BY DATE_ DESC) AS [ORDER_], ROW_NUMBER() OVER (PARTITION BY STOCKREF
ORDER BY DATE_ ASC) AS [ORDER2_]
FROM            ((SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
                            FROM            LG_011_08_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                                                     LG_011_08_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
                            WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)
                         UNION ALL
                         (SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
                          FROM            LG_011_07_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                                                   LG_011_07_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
                          WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)
UNION ALL
(SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
 FROM            LG_011_06_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                          LG_011_06_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
 WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)
UNION ALL
(SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
 FROM            LG_011_05_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                          LG_011_05_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
 WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)
UNION ALL
(SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
 FROM            LG_011_04_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                          LG_011_04_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
 WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)
UNION ALL
(SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
 FROM            LG_011_03_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                          LG_011_03_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
 WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)
UNION ALL
(SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
 FROM            LG_011_02_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                          LG_011_02_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
 WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)
UNION ALL
(SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
 FROM            LG_011_01_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                          LG_011_01_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
 WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)) QQ

Devir Edilmiş Kod

011_08 → 011_09 (1 değişiklik)

ALTER VIEW dbo.[00001_SON_ALIM_LISTESI]
AS
SELECT        STOCKREF, AMOUNT, DATE_, ROW_NUMBER() OVER (PARTITION BY STOCKREF
ORDER BY DATE_ DESC) AS [ORDER_], ROW_NUMBER() OVER (PARTITION BY STOCKREF
ORDER BY DATE_ ASC) AS [ORDER2_]
FROM            ((SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
                            FROM            LG_011_09_STLINE AS STL WITH (NOLOCK)
                         UNION ALL
                         (SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
                            FROM            LG_011_08_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                                                     LG_011_08_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
                            WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)
                         UNION ALL
                         (SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
                          FROM            LG_011_07_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                                                   LG_011_07_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
                          WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)
UNION ALL
(SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
 FROM            LG_011_06_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                          LG_011_06_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
 WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)
UNION ALL
(SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
 FROM            LG_011_05_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                          LG_011_05_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
 WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)
UNION ALL
(SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
 FROM            LG_011_04_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                          LG_011_04_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
 WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)
UNION ALL
(SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
 FROM            LG_011_03_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                          LG_011_03_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
 WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)
UNION ALL
(SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
 FROM            LG_011_02_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                          LG_011_02_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
 WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)
UNION ALL
(SELECT        STL.STOCKREF, STL.AMOUNT, INV.DATE_
 FROM            LG_011_01_STLINE AS STL WITH (NOLOCK) LEFT OUTER JOIN
                          LG_011_01_STFICHE AS INV WITH (NOLOCK) ON INV.LOGICALREF = STL.STFICHEREF
 WHERE        STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0)) QQ

Kodun Çalışma Mantığı (Adım Adım)

Analiz Ekle/Düzenle

Adım 1

SELECT STL.STOCKREF, STL.AMOUNT, INV.DATE_
FROM LG_011_08_STLINE AS STL WITH (NOLOCK)
LEFT OUTER JOIN LG_011_08_STFICHE AS INV WITH (NOLOCK)
  ON INV.LOGICALREF = STL.STFICHEREF
WHERE STL.TRCODE = 1 AND STL.CANCELLED = 0 AND STL.LINETYPE = 0

Bu blok şu mantıkla çalışır:

Kaynak tablo: LG_011_08_STLINE
→ Logo’da stok hareket satırları / fatura-irsaliye satırları burada durur (satır detayları). mobilteg.com.tr+1
TRCODE = 1
→ Satınalma Faturası satırları seçilir. logoyazilimdestek.com.tr+1
CANCELLED = 0
→ iptal satırları dışarıda kalır.
LINETYPE = 0
→ sadece malzeme/stok satırı alınır (hizmet/indirim vb. satırlar değil). ugurozpinar.github.io
STFICHE ile join:
STL.STFICHEREF = INV.LOGICALREF
→ Satırın bağlı olduğu fişin/faturanın tarihini almak için başlık tablosuna gidiliyor.
STFICHE.DATE_ alanı “Fiş Tarihi”dir. ugurozpinar.github.io

Bu join’in amacı: STLINE’dan satır miktarını alıp, STFICHE’dan o satırın bağlı olduğu fişin DATE_ bilgisini getirmek.

Aynı bloklar sırasıyla şunlar için de var:

LG_011_07_STLINE / LG_011_07_STFICHE
LG_011_06...
...
LG_011_01...

Hepsi sonunda şu 3 kolonu üretir:

STOCKREF
AMOUNT
DATE_

Adım 2

... (08 dönemi)
UNION ALL
... (07 dönemi)
UNION ALL
... (06 dönemi)
...
UNION ALL
... (01 dönemi)

Burada olan şey:

Her dönemden gelen satırlar tek bir büyük liste gibi alt alta ekleniyor.
Tekilleştirme yok; yani aynı stok farklı tarihlerde birçok satır olarak kalır.

Bu birleşik listeye en sonda QQ adı veriliyor:

Adım 3

ROW_NUMBER() OVER (PARTITION BY STOCKREF ORDER BY DATE_ DESC) AS [ORDER_],
ROW_NUMBER() OVER (PARTITION BY STOCKREF ORDER BY DATE_ ASC)  AS [ORDER2_]

3A) `PARTITION BY STOCKREF`

SQL Server tüm listeyi alıyor,
her stok için ayrı bir grup oluşturuyor (her STOCKREF kendi içinde değerlendiriliyor).

3B) `ORDER BY DATE_ DESC` → en yeniye 1 ver

Her stok grubunda tarihe göre yeniden eskiye sıralıyor.
En yeni satır ORDER_ = 1 olur.
1. en yeni ORDER_ = 2 … diye gider.

3C) `ORDER BY DATE_ ASC` → en eskiye 1 ver

Aynı stok grubunda bu kez tarihe göre eskiden yeniye sıralıyor.
En eski satır ORDER2_ = 1 olur.
1. en eski ORDER2_ = 2 … diye gider.

Genel Bilgiler

Notlar

İstatistikler

→ Kullandığı Nesneler (0)

← Kullanan Nesneler (3)

→ Bağımlılık Ağacı

← Etki Ağacı

Tam SQL Kodu

Devir Edilmiş Kod

Kodun Çalışma Mantığı (Adım Adım)

3A) PARTITION BY STOCKREF

3B) ORDER BY DATE_ DESC → en yeniye 1 ver

3C) ORDER BY DATE_ ASC → en eskiye 1 ver

3A) `PARTITION BY STOCKREF`

3B) `ORDER BY DATE_ DESC` → en yeniye 1 ver

3C) `ORDER BY DATE_ ASC` → en eskiye 1 ver