LOGO SQL MALZEME STOKLARI

On June 2, 2022June 2, 2022 By semt20In LOGO ERP, SqlLeave a comment

Aşağıdaki sorgu 281 numaraları ambarın anlık stoklarını getirir(stoğu olan ve kullanıdma olan malzemelerin)

SELECT 
  * 
FROM 
  (
    SELECT 
      TOP 100 PERCENT CODE STOK_KODU, 
      NAME STOK_ADI, 
      SPECODE STOK_OZELKODU, 
      ISNULL(
        (
          SELECT 
            SUM(ONHAND) 
          FROM 
            LV_101_01_STINVTOT STITOTS WITH(NOLOCK) 
          WHERE 
            STITOTS.STOCKREF = ITEMS.LOGICALREF 
            AND STITOTS.INVENNO = 281
        ), 
        0
      ) STOK_ADET 
    FROM 
      LG_101_ITEMS ITEMS WITH(NOLOCK) 
    WHERE 
      ACTIVE = 0
  ) as TF 
WHERE 
  STOK_ADET > 0

LOGO SQL SİPARİŞ MİKTAR BEKLEYEN MİKTAR

On May 20, 2022May 20, 2022 By semt20In LOGO ERP, Sql2 Comments

SELECT  DISTINCT  

  
	(SELECT CAST(C.NR AS VARCHAR)+'-'+C.NAME FROM L_CAPIDIV C WHERE C.FIRMNR=121 AND C.NR = ORFIC.BRANCH) ISYERI,   
/*SLSMC.CODE+'-'+*/SLSMC.DEFINITION_ SIPARIS_SATIS_ELEMANI,
	ORFIC.DATE_ SIPARIS_TARIH,	  
	ORFIC.DOCODE SIPARIS_BELGENO, 
	ORFIC.FICHENO SIPARIS_FISNO, 
	ORFIC.TRADINGGRP SIPARIS_TICARIISLEMGRUBU, 
	ORFIC.SPECODE SIPARIS_OZELKOD, 
	ORFIC.CYPHCODE SIPARIS_YETKIKODU, 
  
	CLNTC.CODE CARI_KOD, 
	CLNTC.DEFINITION_ CARI_AD,
	(SELECT SUM(ORF2.AMOUNT) FROM LG_121_01_ORFLINE ORF2 WHERE ORF2.ORDFICHEREF=ORFIC.LOGICALREF AND LINETYPE=4) FIS_TOPLAM_SIPARIS_MIKTAR,
	(SELECT SUM(ORF2.SHIPPEDAMOUNT) FROM LG_121_01_ORFLINE ORF2 WHERE ORF2.ORDFICHEREF=ORFIC.LOGICALREF AND LINETYPE=4) FIS_TOPLAM_SEVKEDILEN_MIKTAR
	
	/*,
	MALZEME DETAYLARI BAŞ
	USLINE.CODE BIRIM, 
	SERVC.CODE HIZMET_KOD, 
	SERVC.DEFINITION_ HIZMET_AD, 
	OTRNS.AMOUNT SIPARIS_MIKTAR, 
	OTRNS.SHIPPEDAMOUNT SIPARIS_SEVKEDILENMIKTAR
	MALZEME DETAYLARI SON
	*/




	/*, 
	ORFIC.CUSTORDNO,
	ORFIC.STATUS,
	ORFIC.WITHPAYTRANS, 
	ORFIC.FACTORYNR, 
	ORFIC.DEPARTMENT, 
	ORFIC.LOGICALREF, 
	ORFIC.TIME_, 
	ORFIC.SOURCEINDEX, 
	ORFIC.TOTALADDTAX, 
	ORFIC.TRRATE, 
	ORFIC.LINEEXCTYP,
	ORFIC.REPORTRATE, 
	OTRNS.PRCURR, 
	OTRNS.DATE_, 
	OTRNS.SPECODE, 
	OTRNS.SOURCEINDEX, 
	OTRNS.LOGICALREF, 
	OTRNS.ORDFICHEREF, 
	OTRNS.STOCKREF, 
	OTRNS.DETLINE, 
	OTRNS.LINETYPE, 
	OTRNS.REPORTRATE, 
	OTRNS.PRICE, 
	OTRNS.TOTAL, 
	OTRNS.PRPRICE, 
	OTRNS.AMOUNT SIPARIS_MIKTAR, 
	OTRNS.UOMREF, 
	OTRNS.SHIPPEDAMOUNT SIPARIS_SEVKEDILENMIKTAR, 
	OTRNS.CPSTFLAG, 
	OTRNS.CLOSED, 
	OTRNS.ONVEHICLE, 
	OTRNS.DISTRESERVED, 
	OTRNS.LINENO_, 
	OTRNS.UINFO1, 
	OTRNS.UINFO2, 
	OTRNS.ADDTAXAMOUNT,  
	ITMSC.CODE, 
	ITMSC.NAME, 
	ITMSC.ACTIVE, 
	ITMSC.SPECODE, 
	ITMSC.CYPHCODE, 
	ITMSC.PRODUCERCODE, 
	ITMSC.STGRPCODE, 
	ITMSC.LOGICALREF, 
	ITMSC.CARDTYPE, 
	SERVC.LOGICALREF, 
	VARIANT.CODE, 
	VARIANT.NAME,
	CLNTC.SPECODE, 
	CLNTC.CYPHCODE, 
	CLNTC.ACTIVE, 
	CLNTC.LOGICALREF, 
	USLINE.LOGICALREF, 
	USLINE.WIDTHREF, 
	USLINE.LENGTHREF, 
	USLINE.HEIGHTREF, 
	USLINE.AREAREF, 
	USLINE.VOLUMEREF, 
	USLINE.WEIGHTREF, 
	USLINE.UNITSETREF, 
	USLINE.MAINUNIT, 
	USLINE.CONVFACT1, 
	USLINE.CONVFACT2, 
	USLINE.CODE, 
	PROJECT.CODE, 
	PROJECT.NAME, 
	PROJECT.LOGICALREF, 
	SHPINF.CODE, 
	SHPINF.NAME  */
FROM 
  LG_121_01_ORFICHE ORFIC WITH(NOLOCK) 
  LEFT OUTER JOIN LG_121_SHIPINFO SHPINF WITH(NOLOCK) ON (
    ORFIC.SHIPINFOREF = SHPINF.LOGICALREF
  ) 
  LEFT OUTER JOIN LG_121_01_ORFLINE OTRNS WITH(NOLOCK) ON (
    ORFIC.LOGICALREF = OTRNS.ORDFICHEREF
  ) 
  LEFT OUTER JOIN LG_121_ITEMS ITMSC WITH(NOLOCK) ON (
    OTRNS.STOCKREF = ITMSC.LOGICALREF
  ) 
  LEFT OUTER JOIN LG_121_SRVCARD SERVC WITH(NOLOCK) ON (
    OTRNS.STOCKREF = SERVC.LOGICALREF
  ) 
  LEFT OUTER JOIN LG_121_VARIANT VARIANT WITH(NOLOCK) ON (
    OTRNS.VARIANTREF = VARIANT.LOGICALREF
  ) 
  LEFT OUTER JOIN LG_121_CLCARD CLNTC WITH(NOLOCK) ON (
    OTRNS.CLIENTREF = CLNTC.LOGICALREF
  ) 
  LEFT OUTER JOIN LG_121_UNITSETL USLINE WITH(NOLOCK) ON (OTRNS.UOMREF = USLINE.LOGICALREF) 
  LEFT OUTER JOIN LG_121_PROJECT PROJECT WITH(NOLOCK) ON (
    OTRNS.PROJECTREF = PROJECT.LOGICALREF
  ) 
   LEFT OUTER JOIN LG_SLSMAN SLSMC WITH(NOLOCK) ON (
    ORFIC.SALESMANREF = SLSMC.LOGICALREF
  ) 
WHERE 
  (
    ORFIC.WITHPAYTRANS IN (1)
  ) 
  AND (
    ORFIC.STATUS IN (4)
  ) 
  AND (
    ORFIC.WITHPAYTRANS IN (1)
  )  
  AND ORFIC.TRCODE = 1 
  AND (SLSMC.CODE IN ( /*M=*/'2021/2022' ,/*G*/ '2021-22'))  
 



SELECT 
  CASE WHEN ORFICHE.TRCODE = 1 THEN 'Satış/Pazarlama Siparişleri' WHEN ORFICHE.TRCODE = 2 THEN 'Alış/Satınalma Siparişleri' ELSE 'Diğer' END AS 'Sipariş Türü', 
  ORFICHE.DATE_ AS 'Fiş Tarihi', 
  ORFICHE.FICHENO AS 'Fiş No', 
  CLCARD.CODE AS 'Cari Kodu', 
  CLCARD.DEFINITION_ as 'Cari Adı', 
  ITEMS.CODE AS 'Stok Kodu', 
  ITEMS.DEFINITION_ AS 'Stok Adı', 
  ORFLINE.AMOUNT AS 'Sipariş Miktar', 
  ISNULL(
    (
      select 
        sum(amount) 
      FROM 
        LG_121_01_STLINE 
      WHERE 
        ORDTRANSREF = ORFLINE.LOGICALREF
    ), 
    0
  ) SEVKMIKTAR 
FROM 
  LG_121_01_ORFICHE ORFICHE 
  INNER JOIN LG_121_01_ORFLINE ORFLINE ON ORFICHE.LOGICALREF = ORFLINE.ORDFICHEREF 
  AND ORFICHE.CLIENTREF = ORFLINE.CLIENTREF 
  INNER JOIN LG_121_CLCARD CLCARD ON ORFICHE.CLIENTREF = CLCARD.LOGICALREF 
  INNER JOIN LG_121_SRVCARD ITEMS ON ORFLINE.STOCKREF = ITEMS.LOGICALREF 
WHERE 
  (ORFLINE.CANCELLED = 0) 
  AND (ORFLINE.STATUS = 4) 
  AND (ORFLINE.CLOSED = 0)

Protected: LOGO KDV RAPORU SQL

On May 12, 2022May 20, 2022 By semt20In LOGO ERP, Sql

LOGO MALZEME BİRİM ÇARPANLARI

On April 6, 2022 By semt20In LOGO ERP, SqlLeave a comment

SELECT dbo.LG_121_ITEMS.CODE AS [Malzeme Kodu], dbo.LG_121_ITEMS.NAME, dbo.LG_121_ITMUNITA.CONVFACT1, dbo.LG_121_ITMUNITA.CONVFACT2, dbo.LG_121_ITMUNITA.LINENR,
dbo.LG_121_UNITSETL.NAME AS Unitcel_name, dbo.LG_121_UNITSETL.CODE AS Unitcel_code
FROM dbo.LG_121_ITMUNITA INNER JOIN
dbo.LG_121_ITEMS ON dbo.LG_121_ITMUNITA.ITEMREF = dbo.LG_121_ITEMS.LOGICALREF INNER JOIN
dbo.LG_121_UNITSETL ON dbo.LG_121_ITMUNITA.UNITLINEREF = dbo.LG_121_UNITSETL.LOGICALREF

sql serverden duplike değerleri silme işlemi

On March 24, 2022 By semt20In SqlLeave a comment

WITH cte AS (
    SELECT 
        contact_id, 
        first_name, 
        last_name, 
        email, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                first_name, 
                last_name, 
                email
            ORDER BY 
                first_name, 
                last_name, 
                email
        ) row_num
     FROM 
        sales.contacts
)
DELETE FROM cte
WHERE row_num > 1;

YEDEK KOPYALAMA

On March 24, 2022March 24, 2022 By semt20In BACKUP, SqlLeave a comment

Talep: Logo – Sql Data’nın yedeklediği klasordeki isteğe bağlı en güncel yedeği (en güncel 1 dosyayı ) networkde sürücüye atanmış başka bir yere kopyala. (Not:Aynı dosya orada var ise kopyalamasın).

Yapı: Exeye parametre gönderilince kopyalama işlemini yapıp exeyi kapatacak. öncesinde source folder(kaynak klasör) ve destination folder(hedef klasör) belirlemek için exe direk çalıştırılır. Son olarak windows task schedulera(windows görev yöneticisi) ekleme yaparak exeye parametreli gönderimle istenilen zamanda çalışması için ayarlar yapılır.

Ayar Ekranı(indir -113kb ) :

Logo Satınalma faturalarını Sql üzerinden Sil

On March 15, 2022March 16, 2022 By semt20In LOGO ERP, SqlLeave a comment

Önemli: İşlem öncesi yedek alınsın ne olur ne olmaz. Siparişten faturaya dönüşen satınalma faturaları , kasadan oluşan satınalma faturaları vb. varsa bu işlem tavsiye edilmez.

delete LG_101_01_CLFLINE WHERE MODULENR=4 AND TRCODE=31 AND SOURCEFREF IN
( select I.LOGICALREF from LG_101_01_INVOICE I WHERE I.TRCODE='1'/*SATINALMA FATURASI ,7=TOPTAN SATIS FATURASI*/ AND GENEXP2='FIS')
 
delete LG_101_01_PAYTRANS WHERE MODULENR=4 AND TRCODE=1 AND FICHEREF IN
( select I.LOGICALREF from LG_101_01_INVOICE I WHERE I.TRCODE='1'/*SATINALMA FATURASI ,7=TOPTAN SATIS FATURASI*/ AND GENEXP2='FIS')
 
delete LG_101_01_STLINE WHERE INVOICEREF IN
( select I.LOGICALREF from LG_101_01_INVOICE I WHERE I.TRCODE='1'/*SATINALMA FATURASI ,7=TOPTAN SATIS FATURASI*/ AND GENEXP2='FIS')
 
delete LG_101_01_STFICHE WHERE INVOICEREF IN
( select I.LOGICALREF from LG_101_01_INVOICE I WHERE I.TRCODE='1'/*SATINALMA FATURASI ,7=TOPTAN SATIS FATURASI*/ AND GENEXP2='FIS')
 
delete LG_101_01_INVOICE WHERE TRCODE='1'/*SATINALMA FATURASI ,7=TOPTAN SATIS FATURASI*/ AND  GENEXP2='FIS'

ORACLE SQL SERVER LINKED SERVER SORU İŞARETİ SORUNU

On February 11, 2022February 11, 2022 By semt20In ORACLE, SqlLeave a comment

YAPI: ODBC ORACLE UZERINDEN SQL SERVERE LINK SERVER ATILMAKTA.

SQL SERVERDE SELECT CEKINCE (LINKED SERVER) ASAGIDAKI GIBI TURKCE KARAKTER SORUNLARI OLMAKTA,

ÇÖZÜM:

CONTROL PANEL->ADMINISTRATOR SETTINSDE ODBC 64 BIT->WORKAROUNDS -> FORCE SQL_WCHAR SUPPORT TIKINI KOY:

LOGO CARI HAREKET FATURA BAGLANTISIZLAR

On January 19, 2022 By semt20In LOGO ERP, SqlLeave a comment

CREATE VIEW [dbo].[BV_121_CLFLINE_FATURA_BAGLANTISIZ] AS 
SELECT 
CLFL.LOGICALREF CLFLINE_LOGICALREF,
 CASE ((MODULENR*100)+TRCODE)
            WHEN 381 THEN 'Satış Siparişi'
            WHEN 382 THEN 'Satınalma Siparişi'
            WHEN 431 THEN 'Satın Alma Faturası'
            WHEN 432 THEN 'Perakende Satış İade Faturası'
            WHEN 433 THEN 'Toptan Satış İade Faturası'
            WHEN 434 THEN 'Alınan Hizmet Faturası'
            WHEN 435 THEN 'Alınan Proforma Faturası'
            WHEN 436 THEN 'Alım İade Faturası'
            WHEN 437 THEN 'Perakende Satış Faturası'
            WHEN 438 THEN 'Toptan Satış Faturası'
            WHEN 439 THEN 'Verilen Hizmet Faturası'
            WHEN 440 THEN 'Verilen Proforma Faturası'
            WHEN 441 THEN 'Verilen Vade Farkı Faturası'
            WHEN 442 THEN 'Alınan Vade Farkı Faturası'
            WHEN 443 THEN 'Alınan Fiyat Farkı Faturası'
            WHEN 444 THEN 'Verilen Fiyat Farkı Faturası'
            WHEN 456 THEN 'Müstahsil Makbuzu'
            WHEN 501 THEN 'Nakit Tahsilat'
            WHEN 502 THEN 'Nakit Ödeme'
            WHEN 503 THEN 'Borç Dekontu'
            WHEN 504 THEN 'Alacak Dekontu'
            WHEN 505 THEN 'Virman İşlemi'
            WHEN 506 THEN 'Kur Farkı İşlemi'
            WHEN 512 THEN 'Özel İşlem'
            WHEN 514 THEN 'Açılış Fişi'
            WHEN 570 THEN 'Kredi Kartı Fişi'
            WHEN 661 THEN 'Çek Girişi'
            WHEN 662  THEN 'Senet Girişi'
            WHEN 663 THEN 'Çek Çıkış Cari Hesaba'--CSTRANS.CLACCREF
            WHEN 664 THEN 'Senet Çıkış Cari Hesaba'
            WHEN 720 THEN 'Gelen Havaleler'
            WHEN 721 THEN 'Gönderilen Havaleler'
            WHEN 728 THEN 'Banka Alınan Hizmet'
            WHEN 729 THEN 'Banka Verilen Hizmet'
            WHEN 1001 THEN 'Nakit Tahsilat'
            WHEN 1002 THEN 'Nakit Ödeme'
            WHEN 6103 THEN 'Borç Dekontu (Çek)'
            WHEN 6104 THEN 'Alacak Dekontu (Çek)'
        END [FIS_TURU],
(SELECT C.CODE FROM LG_121_CLCARD C WHERE C.LOGICALREF=CLIENTREF ) CARIKODU,
(SELECT C.DEFINITION_ FROM LG_121_CLCARD C WHERE C.LOGICALREF=CLIENTREF )  CARIADI,
DATE_ TARIH,
TRANNO,
DOCODE BELGENO,
AMOUNT TUTAR FROM LG_121_01_CLFLINE CLFL WHERE MODULENR=4 AND SOURCEFREF NOT IN  (SELECT LOGICALREF FROM LG_121_01_INVOICE)

Silme işlemi:

delete LG_121_01_CLFLINE WHERE LOGICALREF IN (SELECT CLFLINE_LOGICALREF FROM BV_121_CLFLINE_FATURA_BAGLANTISIZ)

LOGO SQL HAREKET GÖRMEYEN MALZEMELER LİSTESİ

On January 17, 2022 By semt20In LOGO ERP, SqlLeave a comment

Talep: 122 Firmasında hareket görmeyen malzemeler(devir ile 120->121->122 gelmekte yıllık)

SELECT STOCKREF,MALZEMEKODU,MALZEMEADI,SONHAREKETTARIHI FROM (
SELECT *,
   (SELECT MAX(SONHAREKET)
      FROM (VALUES (SONHAREKET120),(SONHAREKET121),(SONHAREKET122)) AS UpdateDate(SONHAREKET)) 
   AS SONHAREKETTARIHI

FROM (
SELECT LOGICALREF AS STOCKREF, CODE AS MALZEMEKODU, NAME AS MALZEMEADI,
ISNULL((SELECT  MAX(DATE_) SONHAREKETTARIHI FROM [ERULKU2016_2020].[dbo].[LG_120_01_STLINE] T  WHERE T.STOCKREF=ITEMS.LOGICALREF AND T.LINETYPE=0 AND T.CANCELLED=0 ),'01-01-2000') SONHAREKET120,
ISNULL((SELECT  MAX(DATE_) SONHAREKETTARIHI FROM [LG_121_01_STLINE] T  WHERE T.STOCKREF=ITEMS.LOGICALREF AND T.LINETYPE=0 AND T.CANCELLED=0 ),'01-01-2000') SONHAREKET121,
ISNULL((SELECT  MAX(DATE_) SONHAREKETTARIHI FROM [LG_122_01_STLINE] T  WHERE T.STOCKREF=ITEMS.LOGICALREF AND T.LINETYPE=0 AND T.CANCELLED=0 ),'01-01-2000') SONHAREKET122
FROM LG_122_ITEMS ITEMS
) AS T
) AS TFINAL WHERE SONHAREKETTARIHI<='2020-01-01'

Blog of Sem

Category: Sql