CREATE VIEW [dbo].[VW_005_01_INDIRIM_SATIRLARI]
AS
SELECT
ST.INVOICEREF,
INV.FICHENO AS FAT_NO,
INV.DATE_ AS TARIH,
UH.KART_TURU,
CL.CODE AS MUST_KODU,
CL.DEFINITION_ AS MUSTERI,
UH.CODE AS URUN_KODU,
UH.NAME AS URUN_ADI,
CAST(ST.DISCPER AS DECIMAL(18,2)) AS ISK_ORANI,
CAST(ST.TOTAL AS DECIMAL(18,2)) AS TOPLAM_INDIRIM
FROM
LG_005_01_STLINE ST OUTER APPLY(
(SELECT IT.NAME,IT.CODE,
CASE
WHEN IT.LOGICALREF > 0 THEN
'ÜRÜN'
ELSE
'HİZMET'
END AS KART_TURU
FROM VW_005_URUN_HIZMET IT
WHERE
IT.LOGICALREF = (SELECT TOP 1 CASE WHEN LINETYPE = 4 THEN -1* STOCKREF ELSE STOCKREF END FROM LG_005_01_STLINE L2 WHERE L2.LINETYPE IN (0,4) AND ST.PARENTLNREF = L2.LOGICALREF ORDER BY L2.LOGICALREF DESC)) -- L2.LINETYPE IN (0,4) AND ST.LOGICALREF > L2.LOGICALREF ORDER BY L2.LOGICALREF DESC
) UH
LEFT OUTER JOIN LG_005_01_INVOICE INV ON ST.INVOICEREF = INV.LOGICALREF
LEFT OUTER JOIN LG_005_CLCARD CL ON ST.CLIENTREF = CL.LOGICALREF
WHERE
LINETYPE=2
AND ST.CANCELLED=0
AND ST.TRCODE IN (7,8)
AND ST.GLOBTRANS = 0