LOGO CARI RISK SIPARIS KAPAT SQL

On August 20, 2021August 20, 2021 By semt20In LOGO ERP, SqlLeave a comment

SENARYO

Bir Carinin borcu 50 gün ve daha fazlaysa job ile risk bilgilerinde siparişe kapatılsın güncellenmesi yapılacak (LOGO FIRMA ADI 210).
Job çalışma süresi her gün sabah saat 7

İŞLEYİŞ

Önce bu kritere uyan cariler view ile belirlenecek (VIEW ADI: CARIRISKSIPARISKAPAT_210)
Job ile bu view çağrılıp gerekli güncelleme işlemi yapılacak

CREATE VIEW CARIRISKSIPARISKAPAT_210 AS 
SELECT * FROM (
SELECT 
CLLOGICALREF=		C.LOGICALREF,
[Cari Kodu]=		C.CODE,
[Cari Ünvanı]=		C.DEFINITION_,
[İşlem]=			ISNULL((
						SELECT TOP 1
							CASE WHEN DATEDIFF(DAY,P.DATE_,GETDATE())>=50 THEN 'Siparişe Kapat'  END  ISLEM
						FROM LG_210_01_PAYTRANS P WITH(NOLOCK)
						WHERE 
							P.MODULENR=4 AND P.TRCODE IN (7,8) AND P.PAID=0 AND P.CARDREF=C.LOGICALREF AND P.CANCELLED=0 ORDER BY P.DATE_ ASC
					),''),
[Siparişte]=		ISNULL((SELECT 
							CASE CLR.ORDRISKOVER 
								WHEN 0 THEN 'İşleme Devam Edilecek' 
								WHEN 1 THEN 'Kullanıcı Uyarılacak' 
								WHEN 2 THEN 'İşlem Durdurulacak' 
							END FROM LG_210_01_CLRNUMS CLR WITH(NOLOCK) WHERE CLR.CLCARDREF=C.LOGICALREF),'')
FROM LG_210_CLCARD C WITH(NOLOCK) WHERE ACTIVE=0
) AS T WHERE T.İşlem<>'' and Siparişte not like '%Durdurulacak'

/*JOB SORGUSU UPDATE İŞLEMİ*/  
SET QUOTED_IDENTIFIER ON
UPDATE LG_210_01_CLRNUMS SET ORDRISKOVER = 2
WHERE CLCARDREF IN  (SELECT B.CLLOGICALREF FROM CARIRISKSIPARISKAPAT_210 B )

LOGO CARI HESAP LISTESI BAKIYE, BAKIYERD

On August 17, 2021 By semt20In LOGO ERP, Sql1 Comment

SELECT 
  T.LOGICALREF, 
  KOD=			CODE, 
  AD=			T.DEFINITION_, 
  TL=			T.BAKIYE, 
  BA_TL=			CASE WHEN T.BAKIYE < 0 THEN 'A' WHEN T.BAKIYE > 0 THEN 'B' ELSE '' END,
  DOVIZ=		T.BAKIYE_RD,
  BA_DOVIZ=		CASE WHEN T.BAKIYE < 0 THEN 'A' WHEN T.BAKIYE > 0 THEN 'B' ELSE '' END  
FROM 
  (
    SELECT 
      CLC.LOGICALREF, 
      CLC.CODE, 
      CLC.DEFINITION_, 
      ISNULL(
        (
          SELECT 
            CONVERT(
              DECIMAL(38, 2), 
              (
                SUM (GNCLTOT.DEBIT)- SUM (GNCLTOT.CREDIT)
              )
            ) 
          FROM 
            LV_118_01_GNTOTCL GNCLTOT 
          WHERE 
            (GNCLTOT.TOTTYP = 1) AND
			GNCLTOT.CARDREF = CLC.LOGICALREF
        ), 
        0
      ) BAKIYE, 
      '' BA,
      ISNULL(
        (
          SELECT 
            CONVERT(
              DECIMAL(38, 2), 
              (
                SUM (GNCLTOT.DEBIT)- SUM (GNCLTOT.CREDIT)
              )
            ) 
          FROM 
            LV_118_01_GNTOTCL GNCLTOT 
          WHERE 
            (GNCLTOT.TOTTYP = 2) AND
			GNCLTOT.CARDREF = CLC.LOGICALREF
        ), 
        0
      ) BAKIYE_RD, 
      '' BA_RD
    FROM 
      LG_118_CLCARD CLC 
    WHERE 
      CODE <> 'ÿ' 
      AND CLC.ACTIVE = 0
  ) AS T

LOGO PAYTRANS FATURADAN CEK SENET KAPATILAN VADE KONTROLLERI

On July 9, 2021 By semt20In LOGO ERP, SqlLeave a comment

CREATE VIEW [dbo].[CEKSENETDETAY_RAPORU_212] AS 
select * from (
SELECT 
T2.TUR,T2.FISNO,T2.BRANCH ISYERI_NR
,
(select CONVERT(VARCHAR,CD.NR)+', '+NAME from L_CAPIDIV CD WITH(NOLOCK) WHERE CD.FIRMNR=210 AND NR=T2.BRANCH) ISYERI
,
CARI_KOD
,
CARI_AD
,
CARI_OZELKOD3
, 
ISNULL((SELECT PAYPLANS.CODE+', '+PAYPLANS.DEFINITION_ FROM LG_210_PAYPLANS PAYPLANS WITH(NOLOCK) WHERE PAYPLANS.LOGICALREF=T2.PAYPLANREF),'') CARI_VADE
,
ISNULL((SELECT CASE WHEN DAY_='' THEN 0 ELSE DAY_ END FROM LG_210_PAYLINES PL WITH(NOLOCK) WHERE PL.PAYPLANREF=T2.PAYPLANREF),0) CARI_VADE_GUN
,
/*CEK_NO,*/DOVIZ,TUTAR,KAPAMA_TUTAR,FATURA_ISLEM_TARIHI,FATURA_VADE_TARIHI, KARSI_ISLEM_BILGILERI
,  
DATEADD(DAY, ISNULL((SELECT CASE WHEN DAY_='' THEN 0 ELSE DAY_ END FROM LG_210_PAYLINES PL WITH(NOLOCK) WHERE PL.PAYPLANREF=(SELECT C.PAYMENTREF FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=CARDREF)),0), FATURA_VADE_TARIHI) AS FATURA_ODENMESI_GEREKEN_TARIH
,   
convert(datetime, REPLACE(LEFT(KARSI_ISLEM_BILGILERI, 29),'Müşteri Çeki Vade:[',''),103 ) CEKODEME_TARIHI
,  
SUBSTRING(KARSI_ISLEM_BILGILERI,CHARINDEX('CekNo:',KARSI_ISLEM_BILGILERI) ,LEN(KARSI_ISLEM_BILGILERI)) CEKNO
,
DATEDIFF(day,DATEADD(DAY, ISNULL((SELECT CASE WHEN DAY_='' THEN 0 ELSE DAY_ END FROM LG_210_PAYLINES PL WITH(NOLOCK) WHERE PL.PAYPLANREF=(SELECT C.PAYMENTREF FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=CARDREF)),0), FATURA_VADE_TARIHI), convert(datetime, REPLACE(LEFT(KARSI_ISLEM_BILGILERI, 29),'Müşteri Çeki Vade:[',''),103 ) ) FATURA_CEKODEME_TARIH_GUN_FARK
FROM (
SELECT DISTINCT 
*,
CASE T.TRCURR WHEN 0  THEN 'TL' WHEN 160 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE CONVERT(VARCHAR,T.TRCURR) END  DOVIZ
,
(SELECT SUM(P.TOTAL) FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND 
	P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF ) TUTAR
,
ISNULL((SELECT SUM(P.PAID) FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND 
	P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF AND PAID<>0),0) KAPAMA_TUTAR
,
ISNULL((
   STUFF(
                             (SELECT ','+dbo.PAYTRANS2ISLEMTURU(PP.TRCODE,PP.MODULENR)+' Vade:['+(CASE WHEN LEN(CAST(DAY(PP.DATE_) AS varchar))=1 THEN  '0'+CAST(DAY(PP.DATE_) AS varchar) ELSE  CAST(DAY(PP.DATE_) AS varchar) END )+'.'+(CASE WHEN LEN(CAST(MONTH(PP.DATE_) AS varchar))=1 THEN  '0'+CAST(MONTH(PP.DATE_) AS varchar) ELSE  CAST(MONTH(PP.DATE_) AS varchar) END ) +'.'+CAST(YEAR(PP.DATE_) AS varchar)+'] / Tutar:'+format((ROUND(PP.PAID,2)),N'','tr-TR') +
							 ' / CekNo:'+(SELECT CSCARD.NEWSERINO FROM LG_210_01_CSCARD CSCARD WITH(NOLOCK) WHERE CSCARD.LOGICALREF=
	(SELECT CST.CSREF FROM LG_210_01_CSTRANS CST WITH(NOLOCK) WHERE CST.ROLLREF=Pp.FICHEREF AND CST.LOGICALREF=Pp.FICHELINEREF))
							 
							 FROM LG_210_01_PAYTRANS PP WITH(NOLOCK) WHERE PP.LOGICALREF IN ((SELECT P.CROSSREF FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND 
	P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF AND PP.MODULENR=6 ))
                                 FOR XML PATH(''), TYPE
                             ).value('.', 'NVARCHAR(MAX)'),
                             1,
                             1,
                             ''
                         )
),'') KARSI_ISLEM_BILGILERI
FROM (
SELECT 
dbo.BM_PAYTRANS2ISLEMTURU(P.TRCODE,P.MODULENR) TUR,
(SELECT I.FICHENO FROM LG_210_01_INVOICE I WITH(NOLOCK) WHERE I.LOGICALREF=P.FICHEREF) FISNO,
P.BRANCH,
P.TRCURR, 
P.CARDREF ,
(SELECT C.PAYMENTREF FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=P.CARDREF)  PAYPLANREF,
(SELECT C.CODE FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=P.CARDREF) CARI_KOD,
(SELECT C.DEFINITION_ FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=P.CARDREF) CARI_AD,
(SELECT C.SPECODE3 FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=P.CARDREF) CARI_OZELKOD3,
P.FICHEREF PAYTRANS_FICHEREF, /*CSTRANS.ROLLREF*/
P.FICHELINEREF PAYTRANS_FICHELINEREF , /*CSTRANS.LOGICALREF*/ 
/*(SELECT CSCARD.NEWSERINO FROM LG_210_01_CSCARD CSCARD WITH(NOLOCK) WHERE CSCARD.LOGICALREF=
	(SELECT CST.CSREF FROM LG_210_01_CSTRANS CST WITH(NOLOCK) WHERE CST.ROLLREF=P.FICHEREF AND CST.LOGICALREF=P.FICHELINEREF)) CEK_NO,*/
P.PROCDATE FATURA_ISLEM_TARIHI,
P.DATE_ FATURA_VADE_TARIHI
FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE MODULENR=4 AND TRCODE IN (7,8,9)
AND CROSSREF IN (SELECT P2.LOGICALREF FROM LG_210_01_PAYTRANS P2 WITH(NOLOCK) WHERE P2.MODULENR=6)
/*P.MODULENR=6*//*CEKLER*/ /* AND P.CARDREF=5571  
 AND YEAR(DATE_)=2021 AND MONTH(DATE_)=4 AND DAY(DATE_)=8   */
) AS T  
 ) AS T2 
 ) as t3
GO

LOGO PAYTRANS ÇEK SENET DETAY RAPORU

On July 7, 2021 By semt20In LOGO ERP, Sql2 Comments


SELECT 
T2.TUR,T2.BRANCH ISYERI_NR
,
(select CONVERT(VARCHAR,NR)+', '+NAME from L_CAPIDIV WITH(NOLOCK) WHERE FIRMNR=210 and NR=T2.BRANCH) ISYERI
,
(SELECT C.CODE FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=CARDREF) CARI_KOD
,
(SELECT C.DEFINITION_ FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=CARDREF) CARI_AD
,
CEK_NO,ISLEM_TARIHI,VADE_TARIHI,CEK_DOVIZ,TUTAR,KAPAMA_TUTAR,KARSI_ISLEM_BILGILERI
FROM (
SELECT DISTINCT 
*,
CASE T.TRCURR WHEN 0  THEN 'TL' WHEN 160 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE CONVERT(VARCHAR,T.TRCURR) END CEK_DOVIZ
,
(SELECT SUM(P.TOTAL) FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND 
	P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF ) TUTAR
,
ISNULL((SELECT SUM(P.PAID) FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND 
	P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF AND PAID<>0),0) KAPAMA_TUTAR
,
ISNULL((
   STUFF(
                             (SELECT ','+dbo.PAYTRANS2ISLEMTURU(PP.TRCODE,PP.MODULENR)+'['+ CAST(DAY(PP.PROCDATE) AS varchar)+'.'+CAST(MONTH(PP.PROCDATE) AS varchar) +'.'+CAST(YEAR(PP.PROCDATE) AS varchar)+'] /'+format((ROUND(PP.PAID,2)),N'','tr-TR')  FROM LG_210_01_PAYTRANS PP WITH(NOLOCK) WHERE PP.LOGICALREF IN ((SELECT P.CROSSREF FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND 
	P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF ))
                                 FOR XML PATH(''), TYPE
                             ).value('.', 'NVARCHAR(MAX)'),
                             1,
                             1,
                             ''
                         )
),'') KARSI_ISLEM_BILGILERI
FROM (
SELECT
dbo.BM_PAYTRANS2ISLEMTURU(P.TRCODE,P.MODULENR) TUR,
P.BRANCH,
P.TRCURR, 
P.CARDREF ,
P.FICHEREF PAYTRANS_FICHEREF, /*CSTRANS.ROLLREF*/
P.FICHELINEREF PAYTRANS_FICHELINEREF , /*CSTRANS.LOGICALREF*/ 
(SELECT CSCARD.NEWSERINO FROM LG_210_01_CSCARD CSCARD WITH(NOLOCK) WHERE CSCARD.LOGICALREF=
	(SELECT CST.CSREF FROM LG_210_01_CSTRANS CST WITH(NOLOCK) WHERE CST.ROLLREF=P.FICHEREF AND CST.LOGICALREF=P.FICHELINEREF)) CEK_NO,
P.PROCDATE ISLEM_TARIHI,
P.DATE_ VADE_TARIHI
FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.MODULENR=6/*CEKLER*/ /* AND P.CARDREF=5571   */
/*AND YEAR(DATE_)=2021 AND MONTH(DATE_)=4 AND DAY(DATE_)=8*/
) AS T  
 ) AS T2

LOGO STLINE HATALI MALZEME BİRİMLERİ

On July 1, 2021 By semt20In LOGO ERP, SqlLeave a comment



SELECT * FROM (
SELECT *,
CASE WHEN MALZEMEUNITSETREF<>STLINEUSREF THEN 'ESIT DEGIL' ELSE '' END UNITSETREF_ESITLIKDURUMU
,
CASE WHEN MALZEMEUOMREF<>STLINEUOMREF THEN 'ESIT DEGIL' ELSE '' END UOMREF_ESITLIKDURUMU
 FROM (
SELECT DISTINCT
(select TOP 1 I.CODE from LG_211_ITEMS I WHERE I.LOGICALREF=S.STOCKREF) MALZEMEKODU
,
(select TOP 1 I.NAME from LG_211_ITEMS I WHERE I.LOGICALREF=S.STOCKREF) MALZEMEADI
,
(select TOP 1 I.UNITSETREF from LG_211_ITEMS I WHERE I.LOGICALREF=S.STOCKREF) MALZEMEUNITSETREF,
(SELECT TOP 1 CODE FROM LG_211_UNITSETL U WHERE U.UNITSETREF= 
	(select TOP 1 I.UNITSETREF from LG_211_ITEMS I WHERE I.LOGICALREF=S.STOCKREF)) MALZEMEBIRIMADI
,
(SELECT TOP 1 LOGICALREF FROM LG_211_UNITSETL U WHERE U.UNITSETREF= 
	(select TOP 1 I.UNITSETREF from LG_211_ITEMS I WHERE I.LOGICALREF=S.STOCKREF)) MALZEMEUOMREF
,
USREF STLINEUSREF
,
UOMREF STLINEUOMREF
,
(SELECT TOP 1 UNT.CODE FROM LG_211_UNITSETL UNT WHERE  UNT.UNITSETREF=S.USREF AND LINENR=1 ) STLINEBIRIMADI
FROM LG_211_01_STLINE S WHERE LINETYPE=0 
/* AND STOCKREF=(select TOP 1 I.LOGICALREF from LG_211_ITEMS I WHERE I.CODE='BYK-0008') */
) AS T WHERE MALZEMEKODU IS NOT NULL
) AS T2  WHERE UNITSETREF_ESITLIKDURUMU<>'' OR UOMREF_ESITLIKDURUMU<>''

Logo Çek Sorgusu

On June 22, 2021June 22, 2021 By semt20In LOGO ERP, SqlLeave a comment

SELECT 
C.BRANCH ISYERI_KODU,
CONVERT(VARCHAR,CAPIDIV.NR)+', '+CAPIDIV.NAME  ISYERI,
LG_211_CLCARD_CEKGIRISCARI.LOGICALREF CEK_GIRIS_CARIREF,
LG_211_CLCARD_CEKGIRISCARI.CODE CEK_GIRIS_CARI_KOD,
LG_211_CLCARD_CEKGIRISCARI.DEFINITION_ CEK_GIRIS_CARI,
ISNULL(PAYPLANS.CODE+', '+PAYPLANS.DEFINITION_,'') CEK_GIRIS_CARI_VADE,
LG_211_CLCARD_CEKGIRISCARI.SPECODE CEK_GIRIS_CARI_OZELKOD,
LG_211_CLCARD_CEKGIRISCARI.SPECODE2 CEK_GIRIS_CARI_OZELKOD2,
LG_211_CLCARD_CEKGIRISCARI.SPECODE3 CEK_GIRIS_CARI_OZELKOD3,
LG_211_CLCARD_CEKGIRISCARI.SPECODE4 CEK_GIRIS_CARI_OZELKOD4,
LG_211_CLCARD_CEKGIRISCARI.SPECODE5 CEK_GIRIS_CARI_OZELKOD5,
 ISNULL((
 SELECT   SUM(P.TOTAL) TOPLAM  FROM BM_211_01_PAYTRANS P WITH(NOLOCK) WHERE P.SIGN=0  AND P.PAID=0  AND P.CANCELLED=0 AND P.CARDREF = LG_211_CLCARD_CEKGIRISCARI.LOGICALREF AND P.BRANCH=C.BRANCH AND P.TRCURR=LG_211_01_CSROLL.TRCURR  
 ),0) CEK_GIRIS_CARI_BAKIYE,

CASE LG_211_01_CSROLL.TRCURR 
WHEN 0  THEN 'TL'
WHEN 160 THEN 'TL'
WHEN 1 THEN 'USD'
WHEN 17 THEN 'GBP'
WHEN 20 THEN 'EUR'
ELSE CONVERT(VARCHAR,LG_211_01_CSROLL.TRCURR) END CEK_DOVIZ,
LG_211_CLCARD_2.CODE AS KİMDEN, 
/*C.DOC AS [çek 1 senet 2], */
C.BANKNAME AS BANKA, 
Substring(C.BNBRANCHNO, 6, 12) AS ŞUBE, 
C.NEWSERINO AS [Seri No], 
/*Substring(CONVERT(varchar(10), C.DUEDATE, 103), 7, 4) + Substring(CONVERT(varchar(10), C.DUEDATE, 103), 4, 2) + Substring(CONVERT(varchar(10), C.DUEDATE, 103), 1, 2) AS vadesi, */
C.AMOUNT AS Tutar, 
CASE CURRSTAT 
	WHEN 1 THEN 'Portföyde' 
	WHEN 2 THEN 'Ciro Edildi' 
	WHEN 3 THEN 'Teminata Verildi' 
	WHEN 4 THEN 'Tahsile Verildi' 
	WHEN 5 THEN 'Protestolu Tahsile Verildi' 
	WHEN 6 THEN 'İade Edildi' 
	WHEN 7 THEN 'Protesto Edildi' 
	WHEN 8 THEN 'Tahsil Edildi' 
	WHEN 9 THEN 'Kendi Çekimiz' 
	WHEN 10 THEN 'Borç Senedimiz' 
	WHEN 11 THEN 'Karşılığı Yok' 
	WHEN 12 THEN 'Tahsil Edilemiyor' 
	ELSE 'Ne Oldugu Belirsiz' 
END 
AS DURUMU, 
C.OWING AS [CİRO EDEN], 
(SELECT TOP 1 ASD.DATE_ FROM LG_211_01_CSTRANS ASD  WITH(NOLOCK) WHERE ASD.CSREF=LG_211_01_CSTRANS_2.CSREF AND ASD.STATUS=1 ORDER BY ASD.LOGICALREF ASC) CEK_GIRIS_TARIHI,
C.SETDATE AS TARIH,
C.DUEDATE AS VADE,  
LG_211_BANKACC.CODE 
BANKA 
FROM   LG_211_01_CSCARD AS C  WITH(NOLOCK) 
INNER JOIN dbo.LG_211_CLCARD AS LG_211_CLCARD_2  WITH(NOLOCK) 
INNER JOIN dbo.LG_211_01_CSROLL  WITH(NOLOCK)  ON LG_211_CLCARD_2.LOGICALREF = dbo.LG_211_01_CSROLL.CARDREF 
INNER JOIN dbo.LG_211_01_CSTRANS AS LG_211_01_CSTRANS_2  WITH(NOLOCK)  ON dbo.LG_211_01_CSROLL.LOGICALREF = LG_211_01_CSTRANS_2.ROLLREF ON C.LOGICALREF = LG_211_01_CSTRANS_2.CSREF 
INNER JOIN LG_211_BANKACC  WITH(NOLOCK)  ON LG_211_BANKACC.LOGICALREF = LG_211_01_CSTRANS_2.CARDREF 
INNER JOIN dbo.LG_211_CLCARD AS LG_211_CLCARD_CEKGIRISCARI  WITH(NOLOCK)  ON LG_211_CLCARD_CEKGIRISCARI.LOGICALREF=(SELECT TOP 1 ASD.CARDREF FROM LG_211_01_CSTRANS ASD  WITH(NOLOCK)  WHERE ASD.CSREF=LG_211_01_CSTRANS_2.CSREF AND ASD.STATUS=1 ORDER BY ASD.LOGICALREF ASC)
INNER JOIN LG_211_PAYPLANS PAYPLANS  WITH(NOLOCK)  ON PAYPLANS.LOGICALREF= LG_211_CLCARD_CEKGIRISCARI.PAYMENTREF
INNER JOIN L_CAPIDIV CAPIDIV WITH(NOLOCK)  ON CAPIDIV.NR=C.BRANCH AND CAPIDIV.FIRMNR=211

Not: https://github.com/ugurozpinar/Logo/blob/master/cekler.sql Revizesidir… Emeğe saygılar…

LOGO SQL AYRINTILI SEVK BİLGİLERİ

On June 15, 2021June 15, 2021 By semt20In LOGO ERP, SqlLeave a comment

BAŞLIKLAR:

SELECT 
  TOP (100) PERCENT CAST(
    LG_101_01_ORFICHE.BRANCH AS VARCHAR(3)
  )+ ' - ' +(
    SELECT 
      NAME 
    FROM 
      L_CAPIDIV 
    WHERE 
      NR = LG_101_01_ORFICHE.BRANCH 
      AND FIRMNR = 101
  ) AS İşyeri, 
  ORFLINE.DUEDATE AS [Sevk Tarihi], 
  LG_101_01_ORFICHE.DATE_ AS Sipariş_Tarihi, 
  LG_101_01_ORFICHE.FICHENO Sipariş_No, 
  CLCARD.CODE AS Müşteri_No, 
  CLCARD.DEFINITION_ AS Müşteri_Adı, 
  ISNULL(
    (
      SELECT 
        LEFT(S.CODE, 7)+ ' - ' +(
          SELECT 
            NAME 
          FROM 
            LG_101_ITEMS 
          WHERE 
            CODE = LEFT(S.CODE, 7)
        ) 
      FROM 
        LG_101_ITEMS I (NOLOCK) 
        LEFT OUTER JOIN LG_101_ITMCLSAS C (NOLOCK) ON I.LOGICALREF = C.CHILDREF 
        AND C.UPLEVEL = 0 
        LEFT OUTER JOIN LG_101_ITEMS S (NOLOCK) ON C.PARENTREF = S.LOGICALREF 
      WHERE 
        S.CODE LIKE 'MS01.%' 
        AND I.CODE NOT LIKE 'MS01.%' 
        AND I.LOGICALREF = ITEMS.LOGICALREF
    ), 
    ''
  ) AS Üst_Sınıf, 
  ISNULL(
    (
      SELECT 
        S.CODE + ' - ' + S.NAME 
      FROM 
        LG_101_ITEMS I (NOLOCK) 
        LEFT OUTER JOIN LG_101_ITMCLSAS C (NOLOCK) ON I.LOGICALREF = C.CHILDREF 
        AND C.UPLEVEL = 0 
        LEFT OUTER JOIN LG_101_ITEMS S (NOLOCK) ON C.PARENTREF = S.LOGICALREF 
      WHERE 
        S.CODE LIKE 'MS01.%' 
        AND I.CODE NOT LIKE 'MS01.%' 
        AND I.LOGICALREF = ITEMS.LOGICALREF
    ), 
    ''
  ) AS Mlz_Sınıfı, 
  ITEMS.PRODUCERCODE Üretici_Kodu, 
  ITEMS.CODE AS Mlz_Kodu, 
  ITEMS.NAME AS Mlz_Açıklaması, 
  'Adet' as Birim, 
  ORFLINE.AMOUNT AS Sipariş_Miktarı, 
  ORFLINE.SHIPPEDAMOUNT AS Sevk_Edilen_Miktar, 
  ROUND(VATMATRAH, 2) AS Net_Tutar, 
  ROUND(VATAMNT, 2) AS KDV_Tutarı, 
  ROUND(VATMATRAH + VATAMNT, 2) AS Brüt_Tutar, 
  CASE (TOTAL - VATMATRAH - VATAMNT) WHEN 0 THEN 0 ELSE ROUND(
    100 *(TOTAL - VATMATRAH - VATAMNT)/ TOTAL, 
    2
  ) END AS İndirim, 
  ROUND(TOTAL - VATMATRAH - VATAMNT, 2) AS İndirim_Tutarı, 
  dbo.Get_Siparis_Masraf_ORFICHE_101(ORDFICHEREF) Masraf, 
  dbo.Get_Satis_Eleman_adi(ORFLINE.SALESMANREF) Satis_Elemani, 
  ORFLINE.CLOSED AS [0(Açık) ] 
FROM 
  LG_101_SPECODES AS SPECODES 
  RIGHT OUTER JOIN LG_101_01_ORFLINE AS ORFLINE ON SPECODES.SPECODE = ORFLINE.SPECODE 
  LEFT OUTER JOIN LG_101_ITEMS AS ITEMS ON ORFLINE.STOCKREF = ITEMS.LOGICALREF FULL 
  OUTER JOIN LG_101_CLCARD AS CLCARD 
  RIGHT OUTER JOIN LG_101_SHIPINFO 
  RIGHT OUTER JOIN LG_101_01_ORFICHE ON LG_101_SHIPINFO.LOGICALREF = LG_101_01_ORFICHE.SHIPINFOREF ON CLCARD.LOGICALREF = LG_101_01_ORFICHE.CLIENTREF ON ORFLINE.ORDFICHEREF = LG_101_01_ORFICHE.LOGICALREF 
WHERE 
  (LG_101_01_ORFICHE.TRCODE = 1) 
  AND LINETYPE = 0 
  AND LG_101_01_ORFICHE.DEPARTMENT = 2 
ORDER BY 
  Sipariş_Tarihi DESC

LOGO SQL FATURA TAHSILAT

On April 20, 2021June 11, 2022 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

Protected: SQL SERVER BINARY EXTRACT BLOB

On April 2, 2021 By semt20In LOGO ERP, LOGO OBJECTS, Sql

LOGO SQL ÜRETİM MİKTAR İSTASYON GİDER

On March 24, 2021 By semt20In LOGO ERP, SqlLeave a comment

SELECT 
ITEMS.CODE 'Malzeme Kodu',
ITEMS.NAME 'Malzeme Adı',
year(PRODORD.DATE_) 'Yıl',
MONTH(PRODORD.DATE_) 'Ay',
(SELECT CODE FROM LG_212_ROUTING WHERE LOGICALREF=PRODORD.ROUTINGref) 'Rota Kodu',
PRODORD.ACTAMOUNT 'Miktar',WORKSTAT.CODE 'İş İstasyonu Kodu',
WORKSTAT.LOGICALREF 'İş İstasyonu Adı', 

PRODORD.DATE_ 'Üretim Tarihi',PRODORD.FICHENO 'Üretim No',PRODORD.ACTMATERIALCOST 'Malzeme Maliyeti',
PRODORD.ACTOVERHCOST 'Gider Maliyet',PRODORD.ACTTOTALCOST 'Toplam Maliyet',
(SELECT CODE FROM LG_212_OPERTION WHERE LOGICALREF=DISPLINE.OPERATIONREF)  'Operasyon Kodu',
(SELECT NAME FROM LG_212_OPERTION WHERE LOGICALREF=DISPLINE.OPERATIONREF) 'Operasyon Adı',
OVERHEADS.CODE+' '+ OVERHEADS.DEFINITION 'Gider Kodu/Adı',OVHDTRANS.TOTAL 'Gider Tutarı'

--PRODORD.PLNDURATION 'Planlanan Süre'
FROM LG_212_PRODORD PRODORD  
LEFT JOIN LG_212_OVHDTRANS OVHDTRANS ON OVHDTRANS.PRODORDREF=PRODORD.LOGICALREF
LEFT JOIN LG_212_OVRHDCENTERLN OVRHDCENTERLN ON OVHDTRANS.OVHDCLNREF=OVRHDCENTERLN.LOGICALREF
LEFT JOIN LG_212_OVERHEADS OVERHEADS ON OVERHEADS.LOGICALREF = OVRHDCENTERLN.OVERHEADREF
LEFT JOIN LG_212_DISPLINE DISPLINE ON DISPLINE.PRODORDREF = PRODORD.LOGICALREF  
LEFT JOIN LG_212_ITEMS ITEMS ON ITEMS.LOGICALREF = PRODORD.ITEMREF 
LEFT JOIN LG_212_WORKSTAT WORKSTAT ON WORKSTAT.LOGICALREF= DISPLINE.WSREF
/*ESKI HALI
FROM LG_212_OVRHDCENTERLN OVRHDCENTERLN 
INNER JOIN LG_212_OVERHEADS OVERHEADS ON OVERHEADS.LOGICALREF = OVRHDCENTERLN.OVERHEADREF
INNER JOIN LG_212_OVHDTRANS OVHDTRANS ON OVHDTRANS.OVHDCLNREF = OVRHDCENTERLN.LOGICALREF
INNER JOIN LG_212_PRODORD PRODORD ON PRODORD.LOGICALREF = OVHDTRANS.PRODORDREF 
INNER JOIN LG_212_DISPLINE DISPLINE ON DISPLINE.PRODORDREF = PRODORD.LOGICALREF AND DISPLINE.LOGICALREF = OVHDTRANS.WORKORDREF
INNER JOIN LG_212_ITEMS ITEMS ON ITEMS.LOGICALREF = PRODORD.ITEMREF 
INNER JOIN LG_212_WORKSTAT WORKSTAT ON WORKSTAT.LOGICALREF= DISPLINE.WSREF

Blog of Sem

Category: Sql

LOGO CARI RISK SIPARIS KAPAT SQL

LOGO CARI HESAP LISTESI BAKIYE, BAKIYERD

LOGO PAYTRANS FATURADAN CEK SENET KAPATILAN VADE KONTROLLERI

LOGO PAYTRANS ÇEK SENET DETAY RAPORU

LOGO STLINE HATALI MALZEME BİRİMLERİ

Logo Çek Sorgusu

LOGO SQL AYRINTILI SEVK BİLGİLERİ

LOGO SQL FATURA TAHSILAT

Protected: SQL SERVER BINARY EXTRACT BLOB

LOGO SQL ÜRETİM MİKTAR İSTASYON GİDER