LOGO INVOICE TABLOSU VE URUN SATIRLARI(SQL STUFF)

On January 4, 2023 By semt20In LOGO ERP, SqlLeave a comment

Invoice tablosu baz alınarak sql stuff komutuyla malzeme/hizmetleri getirmek için:

SELECT 
FIS_MALZEME_HIZMET=	(
Stuff	(
					(
					SELECT DISTINCT
						N', ' + CASE 
									WHEN STL.LINETYPE=0 THEN (SELECT ITEMS.NAME FROM LG_121_ITEMS ITEMS WHERE ITEMS.LOGICALREF=STL.STOCKREF)
									WHEN STL.LINETYPE=4 THEN (SELECT SRVCARD.DEFINITION_ FROM LG_121_SRVCARD SRVCARD WHERE SRVCARD.LOGICALREF=STL.STOCKREF)
								END
					FROM 
						LG_121_01_STLINE STL
					WHERE
						STL.INVOICEREF=I.LOGICALREF AND STL.LINETYPE IN (0/*MALZEME*/,4/*HIZMET*/)
					/*ORDER BY 
						STL.LOGICALREF*/
					FOR XML PATH(''),TYPE
					)
.value('text()[1]','nvarchar(max)'),1,2,N''
				)
)
,
* FROM LG_121_01_INVOICE I

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

LOGO GO3 HAREKET GÖRMEMESİNE RAĞMEN SİLİNEMEME

On December 21, 2021December 23, 2021 By semt20In LOGO ERP, SqlLeave a comment

SORUN: CARİ – MALZEME – BANKA HESAP HAREKET GÖRMEMESİNE RAĞMEN SİLİNEMEMESİ. (LOGO GO3)

TRACE KONTROLLERİ YAPILARAK İLGİLİ TABLOLARIN REFERANSLARI KONTROL EDİLDİ VE KAYIT YOKDU:

UPDATE LG_122_CLCARD SET USEDINPERIODS='0' WHERE ( LOGICALREF = 575)
UPDATE LG_122_ITEMS SET USEDINPERIODS='0' WHERE ( LOGICALREF = 1999)
UPDATE LG_122_BANKACC SET USEDINPERIODS='0' WHERE ( LOGICALREF = 4)

YUKARDAKİ KODLAR ÇALIŞTIRILIP KONTROLLER SAĞLANDI…

LOGO LOD SATINALMA FATURASINA MALZEME ÖZELKODU4 GETİRME

On December 10, 2021December 10, 2021 By semt20In LOD, LOGO ERPLeave a comment

Talep: Logo object designer uyarlamada Logo satınalma faturasına, satır eğer malzeme ise ilgili malzemenin tablosundan özelkod4 (specode4) getirme talebi.

İşlem(Tiger 3 2.71) :

Önce InvoiceGrid grid componentine tıklayıp colattribs içinden MalzemeOzelKodu eklendi:

Formshow eventi:

Sub FormShow() 
MGridName="InvoiceGrid"
 MaxGridLine=MGridLines("InvoiceGrid")-1
 for i=0 to MaxGridLine
   GetGridRowHandle("InvoiceGrid",i,recH)
   AppTables[9].GetIntFieldR(recH,"STOCKREF",intSTOCKREF)
   AppTables[9].GetIntFieldR(recH,"LINETYPE",intLineType)
   if intSTOCKREF>0 and intLineType=0 then
   str(intSTOCKREF,STRSTOCKREF)
   TableName = Application.GetTableName(1, Application.CompanyId, Application.FiscPerdId)
   SPECODE4= G_GETTOP1TORESULT(TableName,false,4 ,"SPECODE4","LOGICALREF="+STRSTOCKREF)  
   SetCellText(MGridName,i,10071,SPECODE4) 
   end if  
 next i
End Sub

Controlexit eventi(malzeme kodu / malzeme açıklamasına girince):

Sub ControlExit(ctrl as String, index as Integer) 
If ctrl="InvoiceGrid" and (Index=2 or Index=3)  Then
MGridName="InvoiceGrid"
GridRow=MGridRow(MGridName)
GetCellText(MGridName,GridRow,16,TUR)
        if TUR="Malzeme" Then 
                GetCellText(MGridName,GridRow,1,KODU)
                TableName = Application.GetTableName(1, Application.CompanyId, Application.FiscPerdId)
                SPECODE4= G_GETTOP1TORESULT(TableName,false,4 ,"SPECODE4","CODE='"+KODU+"'")  
                SetCellText(MGridName,GridRow,10071,SPECODE4)
        end if 
END if      
End Sub

G_GETTOP1RESULT fonksiyonu(Ben Globalde kullanıyorum)

 Function G_GETTOP1TORESULT(TableName as String,isCustTable as bool,RESULTFIELDTYPE as int ,SelectField as String,Where as String)   
 ' Fielttype  Alan türü. 1 Integer 2 Date(Logo formatı tarih ) 3 Float 4 Text 

 'TableName = Application.GetTableName(TableId, Application.CompanyId, Application.FiscPerdId)
 'TableName = Application.GetCustTableName(1, Application.CompanyId, Application.FiscPerdId) 
     
 CreateQuery(MyQry1)
 MyQryTable1=TableName
 MySqlTxt1 = "Select top 1 "+SelectField+" From " + MyQryTable1 +  " WITH(NOLOCK) WHERE "+Where 
 MyQry1.SetSQLText(MySqlTxt1) 
 If MyQry1.ExecuteDirect() Then
 Res = MyQry1.First()
 if Res = 0 then
  if RESULTFIELDTYPE=4 then
  FResult = ""
  else
  FResult = 0
 end if
 end if
 Do While Res=1 
  MyQry1.GetFieldValue(1, RESULTFIELDTYPE, FResult) 
  Res=MyQry1.Next()
 Loop
 End if
 MyQry1.Clear()
 result=FResult
End Function

Not: Eğer tiger tarafında lodda eklenen kolon görünmez ise bu işlem yapılır (upgrade formsa tıklanır) test edilebilir(Her adımın yedekleme yaparak ilerlenmesini tavsiye ederim):

LOGO GÜN BAZLI STOKLAR

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


SELECT TOP 100 PERCENT
  X.Ay,
  X.Gun,
  X.Ambar,
  x.Stok_AktifPasif,
  x.Stok_Kodu,
  x.Stok_Adı,
  x.MalzemeAciklamasi2,
  x.MalzemeAciklamasi3,
  x.MalzemeAciklamasi4,
  x.[Bırım],
  x.Malzeme_Turu,
  x.GrupKodu,
  x.OzelKod,
  x.OzelKod2,
  x.OzelKod3,
  x.OzelKod4,
  x.OzelKod5, 
  GunHareketMiktar =SUM([Miktar]),
  Stok = round((select sum(CAST((CASE WHEN S.IOCODE IN (1, 2) THEN 1 ELSE -1  END) * S.AMOUNT * (CASE WHEN ISNULL(UINFO2, 0) = 0 THEN 1 ELSE UINFO2 END) / (CASE WHEN ISNULL(UINFO1, 0) = 0 THEN 1 ELSE UINFO1 END) AS float)) 
			from LG_211_01_STLINE S where S.STFICHEREF IN (SELECT STF.LOGICALREF FROM LG_211_01_STFICHE STF 
			WHERE 
				STF.LOGICALREF = S.STFICHEREF AND STF.CANCELLED=0) AND S.STOCKREF=X.ITEMLOGICALREF AND S.DATE_<=X.Gun AND S.SOURCEINDEX=X.SOURCEINDEX AND S.CANCELLED = 0 AND S.LINETYPE = 0 AND S.IOCODE IN (1, 2, 3, 4) AND S.LPRODSTAT = 0 ),4)
FROM (SELECT
STLINE.SOURCEINDEX,
ITEMLOGICALREF=STLINE.STOCKREF,
Ay=CAST(YEAR(STFICHE.DATE_) AS VARCHAR)+(CASE WHEN LEN(MONTH(STFICHE.DATE_))=1 THEN '0'+CAST(MONTH(STFICHE.DATE_) AS VARCHAR) ELSE CAST(MONTH(STFICHE.DATE_) AS VARCHAR) END),
Gun=STLINE.DATE_,
STFICHE.FICHENO,
  Ambar = (SELECT
    CAST(NR AS varchar) + '-' + NAME
  FROM L_CAPIWHOUSE CAPIWHOUSE
  WHERE CAPIWHOUSE.NR = STLINE.SOURCEINDEX
  AND CAPIWHOUSE.FIRMNR = 211),
  Isyeri = (SELECT
    CAST(NR AS varchar) + '-' + NAME
  FROM L_CAPIDIV CAPIDIV
  WHERE CAPIDIV.NR = STFICHE.BRANCH
  AND CAPIDIV.FIRMNR = 211),
  STFICHE.LOGICALREF [STFICHE_REFERANS],
  Stok_AktifPasif =case when ITEMS.ACTIVE=0 then 'Aktif' else 'Pasif' end,
  Stok_Kodu = ITEMS.CODE,
  Stok_Adı = ITEMS.NAME,
  MalzemeAciklamasi2 = ITEMS.NAME2,
  MalzemeAciklamasi3 = ITEMS.NAME3,
  MalzemeAciklamasi4 = ITEMS.NAME4,
  [Bırım] = BIRIM.CODE,
  Malzeme_Turu =
                CASE ITEMS.CARDTYPE
                  WHEN 1 THEN 'TICARI MAL'
                  WHEN 2 THEN 'KARMA KOLI'
                  WHEN 3 THEN 'DEPOZITOLU MAL'
                  WHEN 4 THEN 'SABIT KIYMET'
                  WHEN 10 THEN 'HAMMADDE'
                  WHEN 11 THEN 'YARI MAMUL'
                  WHEN 12 THEN 'MAMUL'
                  WHEN 13 THEN 'TUKETIM MALI'
                  WHEN 20 THEN 'GENEL MALZEME SINIFI'
                  WHEN 21 THEN 'TABLOLU MALZEME SINIFI'
                  ELSE CONVERT(varchar(20), CARDTYPE)
                END,
  GrupKodu = ITEMS.STGRPCODE,
  OzelKod = ITEMS.SPECODE,
  OzelKod2 = ITEMS.SPECODE2,
  OzelKod3 = ITEMS.SPECODE3,
  OzelKod4 = ITEMS.SPECODE4,
  OzelKod5 = ITEMS.SPECODE5,
  [Miktar] = CAST((CASE
    WHEN STLINE.IOCODE IN (1, 2) THEN 1
    ELSE -1
  END) * STLINE.AMOUNT * (CASE
    WHEN ISNULL(UINFO2, 0) = 0 THEN 1
    ELSE UINFO2
  END) / (CASE
    WHEN ISNULL(UINFO1, 0) = 0 THEN 1
    ELSE UINFO1
  END) AS float)
FROM LG_211_01_STFICHE STFICHE WITH (NOLOCK)
LEFT OUTER JOIN LG_211_01_STLINE STLINE WITH (NOLOCK)
  ON STFICHE.LOGICALREF = STLINE.STFICHEREF
LEFT OUTER JOIN LG_211_ITEMS ITEMS WITH (NOLOCK)
  ON STLINE.STOCKREF = ITEMS.LOGICALREF
LEFT OUTER JOIN LG_211_UNITSETL BIRIM WITH (NOLOCK)
  ON BIRIM.UNITSETREF = ITEMS.UNITSETREF
  AND BIRIM.MAINUNIT = 1
WHERE STFICHE.CANCELLED = 0
AND STLINE.CANCELLED = 0
AND STLINE.LINETYPE = 0
AND STLINE.IOCODE IN (1, 2, 3, 4) 
AND STLINE.LPRODSTAT = 0
 
AND YEAR(STLINE.DATE_)>=2021 AND MONTH(STLINE.DATE_)=10  ) AS X
GROUP BY  
		 X.Ay,
		 X.Gun,
         X.Ambar,
		 x.Stok_AktifPasif,
		 x.SOURCEINDEX,
		 x.ITEMLOGICALREF,
         x.Stok_Kodu,
         x.Stok_Adı,
         x.MalzemeAciklamasi2,
         x.MalzemeAciklamasi3,
         x.MalzemeAciklamasi4,
         x.Bırım,
         x.Malzeme_Turu,
         x.GrupKodu,
         x.OzelKod,
         x.OzelKod2,
         x.OzelKod3,
         x.OzelKod4,
         x.OzelKod5 
		 ORDER BY  X.Stok_Kodu ,  X.Ambar, X.Gun

LOGO SQL İŞYERİ AMBAR BAZLI ANLIK STOKLAR

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

SELECT
  X.Isyeri,
  X.Ambar,
  x.Stok_Kodu,
  x.Stok_Adı,
  x.MalzemeAciklamasi2,
  x.MalzemeAciklamasi3,
  x.MalzemeAciklamasi4,
  x.[Bırım],
  x.Malzeme_Turu,
  x.GrupKodu,
  x.OzelKod,
  x.OzelKod2,
  x.OzelKod3,
  x.OzelKod4,
  x.OzelKod5,
  Miktar = ROUND(SUM(x.[Miktar]), 4)
FROM (SELECT
  Ambar = (SELECT
    CAST(NR AS varchar) + '-' + NAME
  FROM L_CAPIWHOUSE CAPIWHOUSE
  WHERE CAPIWHOUSE.NR = STLINE.SOURCEINDEX
  AND CAPIWHOUSE.FIRMNR = 211),
  Isyeri = (SELECT
    CAST(NR AS varchar) + '-' + NAME
  FROM L_CAPIDIV CAPIDIV
  WHERE CAPIDIV.NR = STFICHE.BRANCH
  AND CAPIDIV.FIRMNR = 211),
  STFICHE.LOGICALREF [STFICHE_REFERANS],
  Stok_Kodu = ITEMS.CODE,
  Stok_Adı = ITEMS.NAME,
  MalzemeAciklamasi2 = ITEMS.NAME2,
  MalzemeAciklamasi3 = ITEMS.NAME3,
  MalzemeAciklamasi4 = ITEMS.NAME4,
  [Bırım] = BIRIM.CODE,
  Malzeme_Turu =
                CASE ITEMS.CARDTYPE
                  WHEN 1 THEN 'TICARI MAL'
                  WHEN 2 THEN 'KARMA KOLI'
                  WHEN 3 THEN 'DEPOZITOLU MAL'
                  WHEN 4 THEN 'SABIT KIYMET'
                  WHEN 10 THEN 'HAMMADDE'
                  WHEN 11 THEN 'YARI MAMUL'
                  WHEN 12 THEN 'MAMUL'
                  WHEN 13 THEN 'TUKETIM MALI'
                  WHEN 20 THEN 'GENEL MALZEME SINIFI'
                  WHEN 21 THEN 'TABLOLU MALZEME SINIFI'
                  ELSE CONVERT(varchar(20), CARDTYPE)
                END,
  GrupKodu = ITEMS.STGRPCODE,
  OzelKod = ITEMS.SPECODE,
  OzelKod2 = ITEMS.SPECODE2,
  OzelKod3 = ITEMS.SPECODE3,
  OzelKod4 = ITEMS.SPECODE4,
  OzelKod5 = ITEMS.SPECODE5,
  [Miktar] = CAST((CASE
    WHEN STLINE.IOCODE IN (1, 2) THEN 1
    ELSE -1
  END) * STLINE.AMOUNT * (CASE
    WHEN ISNULL(UINFO2, 0) = 0 THEN 1
    ELSE UINFO2
  END) / (CASE
    WHEN ISNULL(UINFO1, 0) = 0 THEN 1
    ELSE UINFO1
  END) AS float)
FROM LG_211_01_STFICHE STFICHE WITH (NOLOCK)
LEFT OUTER JOIN LG_211_01_STLINE STLINE WITH (NOLOCK)
  ON STFICHE.LOGICALREF = STLINE.STFICHEREF
LEFT OUTER JOIN LG_211_ITEMS ITEMS WITH (NOLOCK)
  ON STLINE.STOCKREF = ITEMS.LOGICALREF
LEFT OUTER JOIN LG_211_UNITSETL BIRIM WITH (NOLOCK)
  ON BIRIM.UNITSETREF = ITEMS.UNITSETREF
  AND BIRIM.MAINUNIT = 1
WHERE STFICHE.CANCELLED = 0
AND STLINE.CANCELLED = 0
AND STLINE.LINETYPE = 0
AND STLINE.IOCODE IN (1, 2, 3, 4)

AND STLINE.LPRODSTAT = 0
AND STLINE.DATE_ <= GETDATE()) AS X
GROUP BY X.Isyeri,
         X.Ambar,
         x.Stok_Kodu,
         x.Stok_Adı,
         x.MalzemeAciklamasi2,
         x.MalzemeAciklamasi3,
         x.MalzemeAciklamasi4,
         x.Bırım,
         x.Malzeme_Turu,
         x.GrupKodu,
         x.OzelKod,
         x.OzelKod2,
         x.OzelKod3,
         x.OzelKod4,
         x.OzelKod5

Not: İşyerinin olması akıl karıştırabilir(toplamda bu ambarda şu stok olmalı gibi carpraz kotnrol yapılırsa logodan stok bazen farklı işyerinden düşünce toplam diğer işyerinde olduğu için) dolayısıyla select ve group bydan kaldırılırsa daha sağlıklı kontrol sağlanabilir (ambar şu stok şu)

LOGO DEVİR MALZEME FİŞİ DEVRALINAN STLINEDAN UPDATE İŞLEMİ

On January 14, 2021January 15, 2021 By semt20In LOGO ERPLeave a comment

Devralınan firmanın stlineinana göre devredilen firmadaki devir fişini güncelleme

/*DEVRALINAN FİRMA*/
SELECT STOCKREF,
(
/*MAX LOGICALREFLERIN VATMATRAH/AMOUNTU YANI PRICEI*/
SELECT ROUND(VATMATRAH/NULLIF(AMOUNT,0),2) FROM LG_301_01_STLINE S2 WHERE  S2.LOGICALREF=T.MAXDATELOGICALREF ) AS DEVRALINANPRICE
FROM (

SELECT *,
(
/*EN SON HAREKET TARİHİNİN LOGICALREFI*/
SELECT TOP 1 S2.LOGICALREF FROM LG_301_01_STLINE S2 WHERE 
S2.STOCKREF=S1.STOCKREF AND S2.DATE_=S1.MAXDATE_  AND S2.TRCODE IN (1,14)
ORDER BY S2.LOGICALREF DESC ) AS MAXDATELOGICALREF

FROM (
/*STOCKREFLER VE EN SON HAREKET TARİHLERİ GRUPLU (FİŞ TİPLERİ ALIM VE DEVİR)*/
SELECT STOCKREF,MAX(DATE_) MAXDATE_
FROM LG_301_01_STLINE S1 WHERE TRCODE IN (1,14)
GROUP BY STOCKREF) AS S1 

) T 


-------------------------------

/*DEVREDİLEN FİRMA-HER UPDATE İŞLEMİ TEK TEK SIRAYLA*/
UPDATE LG_401_01_STLINE  SET PRICE=/*DEVRALINANPRICE*/0 FROM WHERE TRCODE=14 AND STOCKREF=/*DEVRALINANSTOCKREF*/0
UPDATE LG_401_01_STLINE  SET VATMATRAH=PRICE*AMOUNT WHERE TRCODE=14 AND STOCKREF=/*DEVRALINANSTOCKREF*/0
UPDATE LG_401_01_STLINE  SET LINENET=VATMATRAH, TOTAL=VATMATRAH  WHERE TRCODE=14 AND STOCKREF=/*DEVRALINANSTOCKREF*/0

Protected: LOGO KALEM DEĞİŞEN FİYATLAR

On November 13, 2020 By semt20In LOGO ERP, LOGO OBJECTS, Sql

LOGO SQL MALZEME EKSTRE

On August 18, 2020 By semt20In LOGO ERP, Sql12 Comments


/*TEST = 
select * from  dbo.SELECT_MALZEMEEXTRE_201('01/01/2017',getdate(),'01-YK-0001')*/
/****** Object:  UserDefinedFunction [dbo].[SELECT_MALZEMEEXTRE_201]    Script Date: 18.8.2020 09:09:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[SELECT_MALZEMEEXTRE_201]
(
    @BAT DATETIME,
	@BIT DATETIME,
	@MALZEMEKODU VARCHAR(50)
)
RETURNS TABLE
AS
RETURN 
SELECT
X.AMBAR,
x.[Stfıche_Referans] ,
x.[Stlıne_Referans] ,
x.[Fiş No],
x.[Fiş Türü],
x.[Belge No],
x.[Stfıche Tarıh],
x.[Stlıne Tarıh],
x.[Ftıme],
x.[Stok Kodu],
x.[Stok Adı],
x.[Bırım],
x.[Giriş Çıkış],
x.[Miktar],
  X.STFICHELNNO,
x.Kalan,
x.[Tutar]
FROM (
SELECT
(SELECT NAME FROM L_CAPIWHOUSE CAPIWHOUSE WHERE CAPIWHOUSE.NR = STLINE.SOURCEINDEX AND CAPIWHOUSE.FIRMNR = 201 ) AMBAR,

STFICHE.LOGICALREF [STFICHE_REFERANS],
STLINE.LOGICALREF [STLINE_REFERANS],
[Fiş No]=STFICHE.FICHENO,

[Fiş Türü]=CASE STFICHE.TRCODE 

WHEN  1 then 'Satınalma İrsaliyesi'

WHEN  2 then 'Perakende Satış İade İrsaliyesi'

WHEN  3 then 'Toptan Satış İade İrsaliyesi'

WHEN  4 then 'Konsinye Çıkış İade İrsaliyesi'

WHEN  5 then 'Konsinye Giriş İrsaliyesi'

WHEN  6 then 'Satınalma İade İrsaliyesi'

WHEN  7 then 'Perakende Satış İrsaliyesi'

WHEN  8 then 'Toptan Satış İrsaliyesi'

WHEN  9 then 'Konsinye Çıkış İrsaliyesi'

WHEN  10 then 'Konsinye Giriş İade İrsaliyesi'

WHEN  11 then 'Fire Fişi'

WHEN  12 then 'Sarf Fişi'

WHEN  13 then 'Üretimden Giriş Fişi'

WHEN  14 then 'Devir Fişi'

WHEN  25 then 'Ambar Fişi'

WHEN  26 then 'Muhtahsil İrsaliyesi'

WHEN  50 then 'Sayım Fazlası Fişi'

WHEN  51 then 'Sayım Eksiği Fişi' ELSE '' END,

[Belge No]=STFICHE.DOCODE,
 
[Stfıche Tarıh]=STFICHE.DATE_,

[Stlıne Tarıh]=STLINE.DATE_,

[Ftıme]=STLINE.FTIME,

[Stok Kodu]=ITEMS.CODE,

[Stok Adı]=ITEMS.NAME,

[Bırım]=BIRIM.CODE,

[Giriş Çıkış]= CASE WHEN STLINE.IOCODE IN(1,2) THEN 'Giriş' when STLINE.IOCODE IN(3,4) THEN 'Çıkış' else '' end,

[Miktar]=CAST((CASE WHEN STLINE.IOCODE IN(1,2) THEN 1 ELSE -1 end) * STLINE.AMOUNT*(CASE WHEN ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END)/(CASE WHEN ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END)  AS float),
STLINE.STFICHELNNO,

0.00 KALAN,

[Tutar]=(CASE WHEN STLINE.IOCODE IN(1,2) THEN 1 ELSE -1 end) * STLINE.VATMATRAH

FROM LG_201_01_STFICHE STFICHE WITH (NOLOCK)

LEFT OUTER JOIN LG_201_01_STLINE STLINE WITH (NOLOCK) ON STFICHE.LOGICALREF=STLINE.STFICHEREF

LEFT OUTER JOIN LG_201_ITEMS ITEMS      WITH (NOLOCK) ON STLINE.STOCKREF =ITEMS.LOGICALREF

LEFT OUTER JOIN LG_201_UNITSETL BIRIM   WITH (NOLOCK) ON BIRIM.UNITSETREF= ITEMS.UNITSETREF AND BIRIM.MAINUNIT=1
WHERE
STFICHE.CANCELLED=0 AND STLINE.CANCELLED=0
AND STLINE.LINETYPE=0
AND STLINE.IOCODE IN (1,2,3,4)
AND ITEMS.CODE=@MALZEMEKODU
AND STLINE.LPRODSTAT=0
AND (STLINE.DATE_>=@BAT AND STLINE.DATE_<=@BIT )
)
 AS  X 
GO

LOGO ERP SQL HAREKET GÖRMÜŞ MALZEMENİN BİRİMİNİ ÇEVİRME HAREKETLER DAHIL

On February 22, 2020February 24, 2020 By semt20In LOGO ERP, SqlLeave a comment

/*MALZEME */SELECT LOGICALREF FROM dbo.LG_211_ITEMS WITH(NOLOCK) WHERE  CODE='M-4299-04-CL-SP' /*MALZEMENIN LOGICALREFINI BULMAK ICIN=1367*/
/*BIRIM*/ SELECT LOGICALREF,UNITSETREF,CODE,NAME FROM LG_211_UNITSETL WITH(NOLOCK) WHERE MAINUNIT=1 AND NAME='ADET' /*HANGI MALZEME BIRIMI OLACAK LOGICALREF=55 UNITSETREF=6*/

/*GUNCELLEME ISLEMI*/
update LG_211_ITEMS set UNITSETREF = 6 /*BIRIMIN UNITSETREFI*/ where LOGICALREF = 1367 /*MALZEMENIN LOGICALREFI*/
update LG_211_ITMUNITA set UNITLINEREF = 55 /*BIRIMIN LOGICALREFI*/ where ITEMREF = 1367 /*MALZEMENIN LOGICALREFI*/

/*RISKLI:GERIYE DONUK HAREKETLERI GUNCELLEMEK ICIN (TALEP,SIPARIS VE IRSALIYE)*/
update LG_211_01_STLINE set UOMREF = 55 /*BIRIMIN LOGICALREFI*/ , USREF = 6 /*BIRIMIN UNITSETREFI*/ where Stockref = 1367 /*MALZEMENIN LOGICALREFI*/
update LG_211_01_ORFLINE set UOMREF = 55 /*BIRIMIN LOGICALREFI*/ , USREF = 6 /*BIRIMIN UNITSETREFI*/ where Stockref = 1367 /*MALZEMENIN LOGICALREFI*/
update LG_211_01_DEMANDLINE  set UOMREF = 55 /*BIRIMIN LOGICALREFI*/ , USREF = 6 /*BIRIMIN UNITSETREFI*/ where Stockref = 1367 /*MALZEMENIN LOGICALREFI*/

Blog of Sem

Tag: malzeme