Tag: STUFF

LOGO SQL FATURA TAHSILAT

On By semt20In LOGO ERP, Sql8 Comments 
SELECT * FROM (
SELECT  
INVOICE.LOGICALREF INVOICELOGICALREF, 
(CASE
WHEN INVOICE.TRCODE=1   THEN 'Satınalma Faturası'
WHEN INVOICE.TRCODE=2   THEN 'Perakende Satış İade Faturası'
WHEN INVOICE.TRCODE=3   THEN 'Toptan Satış İade Faturası'
WHEN INVOICE.TRCODE=4   THEN 'Alınan Hizmet Faturası'
WHEN INVOICE.TRCODE=5   THEN 'Alınan Proforma Faturası'
WHEN INVOICE.TRCODE=6   THEN 'Satınalma İade Faturası'
WHEN INVOICE.TRCODE=7   THEN 'Perakende Satış Faturası'
WHEN INVOICE.TRCODE=8   THEN 'Toptan Satış Faturası'
WHEN INVOICE.TRCODE=9   THEN 'Verilen Hizmet Faturası'
WHEN INVOICE.TRCODE=10  THEN 'Verilen Proforma Faturası'
WHEN INVOICE.TRCODE=12  THEN 'Alınan Vade Farkı Faturası'
WHEN INVOICE.TRCODE=13  THEN 'Satınalma Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=14  THEN 'Satış Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=26  THEN 'Müstahsil Makbuzu'
WHEN INVOICE.TRCODE=1   THEN 'Satınalma Faturası'
WHEN INVOICE.TRCODE=2   THEN 'Perakende Satış İade Faturası'
WHEN INVOICE.TRCODE=3   THEN 'Toptan Satış İade Faturası'
WHEN INVOICE.TRCODE=4   THEN 'Alınan Hizmet Faturası'
WHEN INVOICE.TRCODE=5   THEN 'Alınan Proforma Faturası'
WHEN INVOICE.TRCODE=6   THEN 'Satınalma İade Faturası'
WHEN INVOICE.TRCODE=7   THEN 'Perakende Satış Faturası'
WHEN INVOICE.TRCODE=8   THEN 'Toptan Satış Faturası'
WHEN INVOICE.TRCODE=9   THEN 'Verilen Hizmet Faturası'
WHEN INVOICE.TRCODE=10  THEN 'Verilen Proforma Faturası'
WHEN INVOICE.TRCODE=12  THEN 'Alınan Vade Farkı Faturası'
WHEN INVOICE.TRCODE=13  THEN 'Satınalma Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=14  THEN 'Satış Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=26  THEN 'Müstahsil Makbuzu'
END
) as FATURATIPI,
INVOICE.FICHENO FATURANO,
INVOICE.DATE_  as FATURATARIHI,
YEAR(INVOICE.DATE_)  as YIL,
100*(YEAR(INVOICE.DATE_))+MONTH(INVOICE.DATE_)  AS [Ay],
INVOICE.DOCODE AS BELGENO,
INVOICE.SPECODE AS OZELKOD,
CLCARD.CODE as CARIKOD,
CLCARD.DEFINITION_ as CARIAD, 
(SELECT P.CODE FROM  LG_201_PAYPLANS P WHERE P.LOGICALREF=INVOICE.PAYDEFREF) ODEME,
INVOICE.TRADINGGRP CARIGRUBU,
(SELECT S.CODE+'-'+S.DEFINITION_ FROM LG_SLSMAN S WHERE S.FIRMNR=201 AND S.LOGICALREF= INVOICE.SALESMANREF) SATISELEMANI,
INVOICE.CYPHCODE YETKIKODU,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIDIV D WHERE D.FIRMNR=201 AND D.NR=INVOICE.BRANCH) ISYERI,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIDEPT D WHERE D.FIRMNR=201 AND D.NR=INVOICE.DEPARTMENT) BOLUM,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIFACTORY D WHERE D.FIRMNR=201 AND D.NR=INVOICE.FACTORYNR) FABRIKA,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIWHOUSE D WHERE D.FIRMNR=201 AND D.NR=INVOICE.SOURCEINDEX) AMBAR,
(SELECT LEFT(D.CODE,4) FROM LG_201_PROJECT D WHERE D.LOGICALREF=INVOICE.PROJECTREF) PROJE,
(SELECT D.CODE+'-'+ D.NAME FROM LG_201_PROJECT D WHERE D.LOGICALREF=INVOICE.PROJECTREF) PROJEKODU, 
(SELECT D.NAME FROM LG_201_PROJECT D WHERE D.LOGICALREF=INVOICE.PROJECTREF) PROJEACIKLAMASI, 
(SELECT COUNT(*) FROM LG_201_01_STLINE S WHERE S.INVOICEREF=INVOICE.LOGICALREF ) SATIR_SAYISI,
RTRIM(INVOICE.GENEXP1+' '+INVOICE.GENEXP2+' '+INVOICE.GENEXP3+' '+INVOICE.GENEXP4) AS FISACIKLAMA,
INVOICE.TOTALEXPENSES MASRAF,
INVOICE.TOTALDISCOUNTS INDIRIM,
INVOICE.NETTOTAL-INVOICE.TOTALVAT TOPLAM,
INVOICE.TOTALEXADDTAX EKVERGI,
INVOICE.TOTALVAT KDV,
INVOICE.NETTOTAL NET,
  
CASE CLCARD.CCURRENCY WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END CARIDOVIZ,
CASE INVOICE.TRCURR WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END ISLEMDOVIZ ,
ISNULL((SELECT SUM(CASE WHEN P.TRCURR IN (0,160) THEN P.PAID ELSE  P.TOTAL*P.TRRATE END) FROM LG_201_01_PAYTRANS P WHERE P.MODULENR=4 AND P.FICHEREF=INVOICE.LOGICALREF AND P.SIGN=0 AND P.PAID>0.00000001),0) TOPLAMTAHSILATTUTAR,
(
   STUFF(
                             (SELECT ','+dbo.PAYTRANS2ISLEMTURU(PP.TRCODE,PP.MODULENR)+'['+ CAST(DAY(PP.DATE_) AS varchar)+'.'+CAST(MONTH(PP.DATE_) AS varchar) +'.'+CAST(YEAR(PP.DATE_) AS varchar)+'] /'+format((CASE WHEN PP.TRCURR IN (0,160) THEN ROUND(PP.PAID,2) ELSE  ROUND(PP.TOTAL*PP.TRRATE,2) END),N'','tr-TR')  FROM LG_201_01_PAYTRANS PP WHERE PP.LOGICALREF IN (
                                 SELECT DISTINCT
                                     S2.CROSSREF    /*','+CAST(S2.CROSSREF AS VARCHAR)*/
                                 FROM LG_201_01_PAYTRANS S2
                                 WHERE S2.FICHEREF = INVOICE.LOGICALREF
                                       AND S2.MODULENR=4 AND S2.SIGN=0 )
                                 FOR XML PATH(''), TYPE
                             ).value('.', 'NVARCHAR(MAX)'),
                             1,
                             1,
                             ''
                         )
) TAHSILATBILGILERI  
FROM LG_201_01_INVOICE AS INVOICE WITH (NOLOCK)  
LEFT JOIN LG_201_CLCARD CLCARD ON CLCARD.LOGICALREF=INVOICE.CLIENTREF   WHERE(INVOICE.TRCODE IN(2,3,7,8,9,10,11,14 /*1,4,5,6,12,13,26,2,3,7,8,9,10,14*/))) AS T
 

CREATE FUNCTION [dbo].[PAYTRANS2ISLEMTURU](@TRCODE INT,@MODULENR INT ) 
RETURNS VARCHAR (100)
AS
begin
declare @ISLEM VARCHAR (100)
select @ISLEM = ( SELECT
CASE @MODULENR
               WHEN 7 THEN
                   CASE @TRCODE
                       WHEN 3 THEN
                           'Gelen Havale'
                       WHEN 4 THEN
                           'Gönderilen Havale'
                       WHEN 16 THEN
                           'Banka - Alınan Hizmet Faturası'
                       WHEN 17 THEN
                           'Banka - Verilen Hizmet Faturası'
                   END
               WHEN 4 THEN
                   CASE @TRCODE
                       WHEN 1 THEN
                           'Satınalama Faturası'
                       WHEN 4 THEN
                           'Alınan Hizmet Faturası'
                       WHEN 6 THEN
                           'Satınalma İade Faturası'
                       WHEN 13 THEN
                           'Satınalma Fiyat Farkı Faturası'
                       WHEN 2 THEN
                           'Perakende Satış İade Faturası'
                       WHEN 3 THEN
                           'Toptan Satış İade Faturası'
                       WHEN 7 THEN
                           'Perkande Satış Faturası'
                       WHEN 8 THEN
                           'Toptan Satış Faturası'
                       WHEN 9 THEN
                           'Verilen Hizmet Faturası'
                       WHEN 14 THEN
                           'Satış Fiyat Farkı Faturası'
                   END
               WHEN 5 THEN
                   CASE @TRCODE
                       WHEN 70 THEN
                           'Kredi Kartı Fişi'
                       WHEN 2 THEN
                           'Nakit Ödeme'
                       WHEN 1 THEN
                           'Nakit Tahsilat'
                       WHEN 3 THEN
                           'Borç Dekontu'
                       WHEN 4 THEN
                           'Alacak Dekontu'
                       WHEN 5 THEN
                           'Virman Fişi'
                       WHEN 6 THEN
                           'Kurfarkı Fişi'
                       WHEN 14 THEN
                           'Açılış Fişi'
                       WHEN 41 THEN
                           'Verilen Vade Farkı Faturası'
                       WHEN 42 THEN
                           'Alınan Vade Farkı Faturası'
                       WHEN 71 THEN
                           'Kredi Kartı İade Fişi'
                       WHEN 72 THEN
                           'Firma Kredi Kartı Fişi'
                       WHEN 73 THEN
                           'Firma Kredi Kartı İade Fişi'
                   END
               WHEN 6 THEN
                   CASE @TRCODE
                       WHEN 1 THEN
                           'Müşteri Çeki'
                       WHEN 3 THEN
                           'Kendi Çekimiz'
                   END
               WHEN 3 THEN
                   CASE @TRCODE
                       WHEN 2 THEN
                           'Satınalama Siparişi'
                       WHEN 1 THEN
                           'Satış Siparişi'
                   END
               WHEN 10 THEN
                   CASE @TRCODE
                       WHEN 1 THEN
                           'Nakit Tahsilat'
                       WHEN 2 THEN
                           'Nakit Ödeme'
                   END
               WHEN 61 THEN
                   CASE @TRCODE
                       WHEN 3 THEN
                           'Borç Dekontu'
                       WHEN 4 THEN
                           'Alacak Dekontu'
                   END
               WHEN 62 THEN
                   CASE @TRCODE
                       WHEN 3 THEN
                           'Borç Dekontu'
                       WHEN 4 THEN
                           'Alacak Dekontu'
                   END
           END AS ISLEM 
 ) 

return @ISLEM
end
GO

SQL STUFF

On By semt20In LOGO ERP, LOGO OBJECTSLeave a comment
 



/****** Object:  UserDefinedFunction [dbo].[BM_210_AYRINTILI_SARFET_FNC]    Script Date: 6.04.2019 12:05:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[BM_210_AYRINTILI_SARFET_FNC]
(
@DATE DATETIME
)
RETURNS @RESULTTABLE TABLE
(
  TARIH NVARCHAR(MAX),
LOGICALREF INT,
PROJECTREF INT,
SOURCEINDEX INT,
Ambar NVARCHAR(50),
Proje NVARCHAR(50),
[Proje Açıklaması] NVARCHAR(150),
[Proje L2] NVARCHAR(150),
[Proje L2 Açıklaması] NVARCHAR(150),
[Proje L3] NVARCHAR(150),
[Proje L3 Açıklaması] NVARCHAR(150),
[Proje L4] NVARCHAR(150),
[Proje L4 Açıklaması] NVARCHAR(150),
[Aktivite Kodu] NVARCHAR(150),
[Aktivite Adı] NVARCHAR(150),
[Malzeme Özel Kodu] NVARCHAR(150),
[Malzeme Özel Kodu Açıklaması] NVARCHAR(150),
[Malzeme Kodu] NVARCHAR(150),
[Malzeme Adı] NVARCHAR(150),
[Birim] NVARCHAR(150),
[Sarf Miktar] FLOAT,
[Sarf Matrah] FLOAT,
KDV FLOAT
)
AS
BEGIN

INSERT INTO @RESULTTABLE
SELECT
STUFF((SELECT DISTINCT ',' + QUOTENAME( FORMAT( S2.DATE_, 'dd/MM/yyyy', 'tr-TR')) FROM
BV_210_01_STINVTOT S2
WHERE S2.INVENNO > -1
AND S2.DATE_ <= @DATE
AND S2.PROJECTREF = BV_210_01_STINVTOT.PROJECTREF AND S2.STOCKREF=BV_210_01_STINVTOT.STOCKREF 

            FOR XML PATH(''), TYPE

            ).value('.', 'NVARCHAR(MAX)')

        ,1,1,''),
0 AS LOGICALREF
,PROJECTREF
,INVENNO AS SOURCEINDEX
,(SELECT NAME FROM L_CAPIWHOUSE WHERE NR = INVENNO AND FIRMNR = 201) AS Ambar
--,(SELECT LEFT(CODE, 4) FROM LG_210_PROJECT WHERE LOGICALREF = PROJECTREF) AS [Proje]
,(SELECT CODE FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL1 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje]
,(SELECT NAME FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL1 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje Açıklaması]
,(SELECT CODE FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL2 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L2]
,(SELECT NAME FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL2 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L2 Açıklaması]
,(SELECT CODE FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL3 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L3]
,(SELECT NAME FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL3 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L3 Açıklaması]
,(SELECT CODE FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL4 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L4]
,(SELECT NAME FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL4 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L4 Açıklaması]
,(SELECT CODE FROM LG_210_PROJECT WHERE LOGICALREF = PROJECTREF) AS [Aktivite Kodu]
,(SELECT NAME FROM LG_210_PROJECT WHERE LOGICALREF = PROJECTREF) AS [Aktivite Adı]
,(SELECT SPECODE FROM LG_210_ITEMS WHERE LOGICALREF = STOCKREF) AS [Malzeme Özel Kodu]
,(SELECT TOP 1 DEFINITION_ FROM LG_210_SPECODES WHERE CODETYPE = 1 AND SPECODETYPE = 1 AND SPECODE = (SELECT SPECODE FROM LG_210_ITEMS WHERE LOGICALREF = STOCKREF)) AS [Malzeme Özel Kodu Açıklaması]
,(SELECT CODE FROM LG_210_ITEMS WHERE LOGICALREF = STOCKREF) AS [Malzeme Kodu]
,(SELECT NAME FROM LG_210_ITEMS WHERE LOGICALREF = STOCKREF) AS [Malzeme Adı]
,(SELECT CODE FROM LG_210_UNITSETF WHERE LOGICALREF = (SELECT UNITSETREF FROM LG_210_ITEMS WHERE LOGICALREF = STOCKREF)) AS [Birim]
,ROUND((SUM(ONHAND)), 4) AS [Sarf Miktar]
,ROUND((SUM(VATMATRAH)), 4) [Sarf Matrah]
,ISNULL((SELECT TOP 1 VAT FROM LG_210_01_STLINE WHERE TRCODE = 1 AND CANCELLED = 0 AND LG_210_01_STLINE.STOCKREF = BV_210_01_STINVTOT.STOCKREF ORDER BY DATE_ DESC), -1) AS KDV
FROM
BV_210_01_STINVTOT
WHERE INVENNO > -1
AND DATE_ <= @DATE
AND PROJECTREF > 0
AND (SELECT  S2.STFICHEREF FROM LG_201_01_STLINE S2 WHERE S2.TRCODE = 6 AND S2.STOCKREF = BV_201_01_STINVTOT.STOCKREF AND S2.DATE_ <= @DATE),

AND STFICHEREF IN (SELECT F.LOGICALREF FROM  LG_210_01_STFICHE F WHERE F.SPECODE'IFS')
GROUP BY INVENNO, STOCKREF, PROJECTREF , DATE_
HAVING SUM(ONHAND) > 0

RETURN
END

GO