Ücretli İstek Cari Hesap Ekstresi Stok Detaylı

USE [TIGER]
GO

/****** Object:  View [dbo].[ZLV_001_01_CARIHESAPEKSTRE_STOKDETAYLI]    Script Date: 17.02.2021 11:58:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






ALTER View [dbo].[ZLV_001_01_CARIHESAPEKSTRE_STOKDETAYLI]
as
SELECT  Id, [Sıra No], A.Tarih, [Fiş No], [Fiş Türü], [Cari Kod], [Cari Açıklama], [Stok Kodu], [Stok Adı], [Özel Kod 1], [Özel Kod 2], [Özel Kod 3], [Özel Kod 4], [Özel Kod 5],
Kod1, Kod2, Kod3, Kod4, Kod5, Kod6, Kod7, Kod8 + ' ' as Kod8, Miktar,[Birim Fiyat], [Para Birimi], Borç, Alacak, Case When Borç>0 Then Borç Else Alacak*-1 End as Tutar, ROUND(SUM(Borç-Alacak) OVER (PARTITION BY [Cari Kod], [Para Birimi] ORDER BY [Cari Kod], [Para Birimi], Tarih, Id, [Sıra No]),2) as 'Bakiye', Açıklama
FROM (
SELECT CFL.LOGICALREF 'Id', CFL.LINENR 'Sıra No', CFL.DATE_ 'Tarih', CFL.TRANNO as 'Fiş No', dbo.fn_crfline_module_trcode_aciklama(CFL.MODULENR,CFL.TRCODE) as 'Fiş Türü', C.CODE 'Cari Kod', C.DEFINITION_ as 'Cari Açıklama', '' as 'Stok Kodu', '' as 'Stok Adı',
'' as 'Özel Kod 1', '' 'Özel Kod 2', '' as 'Özel Kod 3', '' as 'Özel Kod 4', '' as 'Özel Kod 5',
'' as Kod1, '' as Kod2, '' as Kod3, '' as Kod4, '' as Kod5, '' as Kod6, '' as Kod7, '' Kod8, 0 as 'Miktar', 0 as 'Birim Fiyat',
CASE WHEN TRCURR=0 THEN 'TRY ' WHEN TRCURR=1 THEN 'USD ' WHEN TRCURR=20 THEN 'EUR ' WHEN TRCURR=17 THEN 'GBP' ELSE '    ' End as 'Para Birimi',
CASE WHEN CFL.SIGN=0 THEN CFL.TRNET ELSE 0 END AS 'Borç',
CASE WHEN CFL.SIGN=1 THEN CFL.TRNET ELSE 0 END AS 'Alacak', CFL.LINEEXP AS 'Açıklama'
FROM LG_001_01_CLFLINE CFL
LEFT JOIN LG_001_CLCARD C ON C.LOGICALREF=CFL.CLIENTREF
WHERE MODULENR NOT IN (4,6) and CLIENTREF>0
UNION ALL
SELECT CFL.LOGICALREF 'Id', FD.INVOICELNNO as 'Sıra No', F.DATE_ as 'Tarih', F.FICHENO as 'Fiş No', dbo.fn_crfline_module_trcode_aciklama(CFL.MODULENR,CFL.TRCODE) as 'Fiş Türü', C.CODE 'Cari Kod', C.DEFINITION_ as 'Cari Açıklama',
CASE WHEN FD.LINETYPE=0 THEN S.CODE WHEN FD.LINETYPE=4 THEN SRV.CODE ELSE '' END AS 'Stok Kodu', CASE WHEN FD.LINETYPE=0 THEN S.NAME WHEN FD.LINETYPE=4 THEN SRV.DEFINITION_ ELSE '' END AS 'Stok Adı',
s.SPECODE as 'Özel Kod 1', S.SPECODE2 'Özel Kod 2', S.SPECODE3 as 'Özel Kod 3', S.SPECODE4 as 'Özel Kod 4', S.SPECODE5 as 'Özel Kod 5',
ISNULL(Kod1,'') Kod1, ISNULL(Kod2,'') Kod2 ,ISNULL(Kod3,'') Kod3, ISNULL(Kod4,'') Kod4, ISNULL(Kod5,'') Kod5, ISNULL(Kod6,'') Kod6, ISNULL(Kod7,'') Kod7, ISNULL(Kod8,'') Kod8, FD.AMOUNT as 'Miktar',
ISNULL(ROUND(Case When FD.TRCURR=0 Then PRICE Else PRICE/NULLIF(FD.TRRATE,0) End,5),0) 'Birim Fiyat',
CASE WHEN FD.TRCURR=0 THEN 'TRY ' WHEN FD.TRCURR=1 THEN 'USD ' WHEN FD.TRCURR=20 THEN 'EUR ' WHEN FD.TRCURR=17 THEN 'GBP' ELSE '    ' End as 'Para Birimi',
Case When CFL.SIGN=0 Then ISNULL(ROUND(Case When FD.TRCURR=0 Then FD.VATAMNT+ LINENET Else  (FD.VATAMNT+ LINENET)/NULLIF(FD.TRRATE,0) End,2),0) Else 0  End as 'Borç',
Case When CFL.SIGN=1 Then ISNULL(ROUND(Case When FD.TRCURR=0 Then FD.VATAMNT+ LINENET Else  (FD.VATAMNT+ LINENET)/NULLIF(FD.TRRATE,0) End,2),0) Else 0 End as 'Alacak', '' as 'Açıklama'
FROM LG_001_01_CLFLINE CFL
LEFT JOIN LG_001_CLCARD C ON C.LOGICALREF=CFL.CLIENTREF
LEFT JOIN LG_001_01_INVOICE F ON F.LOGICALREF=CFL.SOURCEFREF
LEFT JOIN LG_001_01_STLINE FD ON FD.INVOICEREF=F.LOGICALREF
LEFT JOIN LG_001_ITEMS S ON S.LOGICALREF=FD.STOCKREF
LEFT JOIN LG_001_SRVCARD SRV ON SRV.LOGICALREF=FD.STOCKREF
LEFT JOIN ZLV_001_VARIANT V ON V.ITEMREF=S.LOGICALREF AND V.VARIANTREF=FD.VARIANTREF
WHERE MODULENR=4
UNION ALL
SELECT CFL.LOGICALREF 'Id', CEKH.LINENO_ as 'Sıra No', CEKB.DATE_ as 'Tarih', CFL.TRANNO as 'Fiş No', dbo.fn_crfline_module_trcode_aciklama(CFL.MODULENR,CFL.TRCODE) as 'Fiş Türü', C.CODE 'Cari Kod', C.DEFINITION_ as 'Cari Açıklama', '' as 'Stok Kodu', '' as 'Stok Adı',
'' as 'Özel Kod 1', '' 'Özel Kod 2', '' as 'Özel Kod 3', '' as 'Özel Kod 4', '' as 'Özel Kod 5',
'' as Kod1, '' as Kod2, '' as Kod3, '' as Kod4, '' as Kod5, '' as Kod6, '' as Kod7, '' Kod8, 0 as 'Miktar', 0 as 'Birim Fiyat',
CASE WHEN CFL.TRCURR=0 THEN 'TRY ' WHEN CFL.TRCURR=1 THEN 'USD ' WHEN CFL.TRCURR=20 THEN 'EUR ' WHEN CFL.TRCURR=17 THEN 'GBP' ELSE '    ' End as 'Para Birimi',
CASE WHEN CFL.SIGN=0 THEN CEKC.TRNET ELSE 0 END AS 'Borç',
CASE WHEN CFL.SIGN=1 THEN CEKC.TRNET ELSE 0 END AS 'Alacak', Case When OURBANKREF>0 THEN BA.DEFINITION_ + '/' + CONVERT(varchar, CEKC.DUEDATE, 104) + '/' + CEKC.NEWSERINO Else '' End AS 'Açıklama'
FROM LG_001_01_CLFLINE CFL
LEFT JOIN LG_001_CLCARD C ON C.LOGICALREF=CFL.CLIENTREF
LEFT JOIN LG_001_01_CSROLL CEKB ON CEKB.LOGICALREF=CFL.SOURCEFREF
LEFT JOIN LG_001_01_CSTRANS CEKH ON CEKH.ROLLREF=CEKB.LOGICALREF
LEFT JOIN LG_001_01_CSCARD CEKC ON CEKC.LOGICALREF=CEKH.CSREF
LEFT JOIN LG_001_BANKACC BAH ON BAH.LOGICALREF=CEKC.OURBANKREF
LEFT JOIN LG_001_BNCARD BA ON BA.LOGICALREF=BAH.BANKREF
WHERE CFL.MODULENR=6
UNION ALL
SELECT 10000000 as 'Id', 10000 as 'Sıra No', ı.DATE_ as 'Tarih', I.FICHENO as 'Fiş No', dbo.fn_stfiche_trcode_aciklama(I.TRCODE) as 'Fiş Türü', C.CODE 'Cari Kod', C.DEFINITION_ as 'Cari Açıklama',
CASE WHEN FD.LINETYPE=0 THEN S.CODE WHEN FD.LINETYPE=4 THEN SRV.CODE ELSE '' END AS 'Stok Kodu', CASE WHEN FD.LINETYPE=0 THEN S.NAME WHEN FD.LINETYPE=4 THEN SRV.DEFINITION_ ELSE '' END AS 'Stok Adı',
s.SPECODE as 'Özel Kod 1', S.SPECODE2 'Özel Kod 2', S.SPECODE3 as 'Özel Kod 3', S.SPECODE4 as 'Özel Kod 4', S.SPECODE5 as 'Özel Kod 5',
ISNULL(Kod1,'') Kod1, ISNULL(Kod2,'') Kod2 ,ISNULL(Kod3,'') Kod3, ISNULL(Kod4,'') Kod4, ISNULL(Kod5,'') Kod5, ISNULL(Kod6,'') Kod6, ISNULL(Kod7,'') Kod7, ISNULL(Kod8,'') Kod8, FD.AMOUNT as 'Miktar', 0 as 'Birim Fiyat',
CASE WHEN C.CCURRENCY=0 THEN 'TRY ' WHEN C.CCURRENCY=1 THEN 'USD ' WHEN C.CCURRENCY=20 THEN 'EUR ' WHEN C.CCURRENCY=17 THEN 'GBP' ELSE '    ' End as 'Para Birimi', 0 as 'Borç', 0 as 'Alacak', '' as 'Açıklama'
FROM LG_001_01_STFICHE I
LEFT JOIN LG_001_CLCARD C ON C.LOGICALREF=I.CLIENTREF
LEFT JOIN LG_001_01_STLINE FD ON FD.STFICHEREF=I.LOGICALREF AND LINETYPE=0
LEFT JOIN LG_001_ITEMS S ON S.LOGICALREF=FD.STOCKREF
LEFT JOIN LG_001_SRVCARD SRV ON SRV.LOGICALREF=FD.STOCKREF
LEFT JOIN ZLV_001_VARIANT V ON V.ITEMREF=S.LOGICALREF AND V.VARIANTREF=FD.VARIANTREF
WHERE I.BILLED=0
) A WHERE [Cari Kod] LIKE '320%'


GO