WITH ItemHierarcyh (STCREF, MAINCREF, CARDTYPE)
AS (
SELECT STCREF, MAINCREF, CARDTYPE
FROM LG_123_STCOMPLN
WHERE MAINCREF = (SELECT I.LOGICALREF FROM LG_123_ITEMS I WHERE I.CODE='RECETE0016')
UNION ALL
SELECT S.STCREF, S.MAINCREF, S.CARDTYPE
FROM LG_123_STCOMPLN S
JOIN ItemHierarcyh PH ON S.MAINCREF = PH.STCREF
)
SELECT
STCREF,
AltMalzeme=(select I.CODE+'-'+I.NAME from LG_123_ITEMS I WHERE I.LOGICALREF =STCREF),
MAINCREF,
AnaMalzeme=(select I.CODE+'-'+I.NAME from LG_123_ITEMS I WHERE I.LOGICALREF =MAINCREF),
CARDTYPE
FROM ItemHierarcyh --where CARDTYPE in (11,12)
OPTION (MAXRECURSION 0)
Tag: ITEMS
LOGO INVOICE TABLOSU VE URUN SATIRLARI(SQL STUFF)
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 Erp / Sql Malzeme sınıfı bağlantıları çalışması
kırılım yapısı(3 alt kırılım-FERRARI) : Tüm Malzemeler -> Kırılım1-> Kırılım2-> Kırılım3 -> Kırılım3 Malzemeleri
Kampanya yapısı(2 alt kırılım-KMP) : Tüm Malzemeler -> Kırılım1-> Kırılım2 -> Kırılım2 Malzemeleri
create view [RM_FERRARI_MALZEME_SINIFLARI]
as
select top 100 percent [Type], [Level1Ref] = case when [Level1Ref] = 1 then Level2Ref else Level1Ref end, [Level1] = case when [Level1Ref] = 1 then Level2 else Level1 end, [Level2Ref] = case when [Level1Ref] = 1 then Level3Ref else Level2Ref end, [Level2] = case when [Level1Ref] = 1 then Level3 else Level2 end, [Level3Ref] = case when [Level1Ref] = 1 then 0 else [Level3Ref] end, [Level3] = case when [Level1Ref] = 1 then '' else [Level3] end, [ItemRef], [Code], [Name], [Active]
from (
select [Type] = case when ITMSC.CARDTYPE = 20 then 'Genel Malzeme Sınıfı' else 'Malzeme' end, [Level1Ref] = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
)
), [Level1] = (
select I2.CODE + '-' + I2.name
from LG_101_ITEMS I2
where I2.LOGICALREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
)
)
), [Level2Ref] = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
), [Level2] = (
select I2.CODE + '-' + I2.name
from LG_101_ITEMS I2
where I2.LOGICALREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
)
), [Level3Ref] = LGMAIN.PARENTREF, [Level3] = (
select I2.CODE + '-' + I2.name
from LG_101_ITEMS I2
where I2.LOGICALREF = LGMAIN.PARENTREF
), [ItemRef] = ITMSC.LOGICALREF, [Code] = ITMSC.CODE, [Name] = ITMSC.name, [Active] = ITMSC.ACTIVE
from LG_101_ITMCLSAS LGMAIN with (nolock)
left outer join LG_101_ITEMS ITMSC with (nolock) on (LGMAIN.CHILDREF = ITMSC.LOGICALREF)
) as T
where [Level1] = 'MS01-FERRARI' or Level3 = 'MS01.16-KMP'
order by Level1, Level2, Level3
Logo Malzeme Grup Açıklamaları Sql
select
CODE,
NAME,
STGRPCODE,
(
select
TOP 1 S.DEFINITION_
from
LG_301_SPECODES S
WHERE
(
(
(S.CODETYPE = 4)
AND (S.SPECODETYPE = 0)
)
OR (
(S.CODETYPE = 4)
AND (S.SPECODETYPE > 0)
)
OR (
(S.CODETYPE > 4)
)
)
AND (
(
(S.CODETYPE = 4)
AND (S.SPECODETYPE <= 0)
)
OR (
(S.CODETYPE < 4)
)
)
AND S.SPECODE = I.STGRPCODE
) STGRPCODE_A,
SPECODE,
(
select
TOP 1 S.DEFINITION_
from
LG_301_SPECODES S
WHERE
S.SPECODE = I.SPECODE
AND S.SPETYP1 = 1
AND S.CODETYPE = 1
AND S.SPECODETYPE = 1
) SPECODE_A,
SPECODE2,
(
select
TOP 1 S.DEFINITION_
from
LG_301_SPECODES S
WHERE
S.SPECODE = I.SPECODE2
AND S.SPETYP2 = 1
AND S.CODETYPE = 1
AND S.SPECODETYPE = 1
) SPECODE2_A,
SPECODE3,
(
select
TOP 1 S.DEFINITION_
from
LG_301_SPECODES S
WHERE
S.SPECODE = I.SPECODE3
AND S.SPETYP3 = 1
AND S.CODETYPE = 1
AND S.SPECODETYPE = 1
) SPECODE3_A,
SPECODE4,
(
select
TOP 1 S.DEFINITION_
from
LG_301_SPECODES S
WHERE
S.SPECODE = I.SPECODE4
AND S.SPETYP4 = 1
AND S.CODETYPE = 1
AND S.SPECODETYPE = 1
) SPECODE4_A
from
LG_301_ITEMS I
LOGO MALZEME BİRİM ÇARPANLARI
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 SQL HAREKET GÖRMEYEN MALZEMELER LİSTESİ
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'
LOGO GO3 HAREKET GÖRMEMESİNE RAĞMEN SİLİNEMEME
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
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 SQL İŞYERİ AMBAR BAZLI ANLIK STOKLAR
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 STLINE HATALI MALZEME BİRİMLERİ
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<>''
Blog of Sem 