SELECT LOGICALREF,CODE,NAME FROM LG_211_UNITSETF BIRIMLER WITH(NOLOCK)
UPDATE LG_211_ITEMS SET UNITSETREF=6 WHERE CODE='yem-069' AND UNITSETREF=35
UPDATE LG_211_ITEMS SET UNITSETREF=6 WHERE CODE='yem-070' AND UNITSETREF=35
SELECT *
FROM LG_211_ITMUNITA WITH(NOLOCK) WHERE ITEMREF=MALZEMENIN LOGICALREFI
UPDATE LG_211_ITMUNITA SET UNITLINEREF=UOMREF WHERE ITEMREF=MALZEMENIN LOGICALREFI
Category: LOGO ERP
LOGO KAMPANYALARI GUNLUK DOVIZ KURUNA GORE GUNCELLEME
UPDATE LG_119_CAMPAIGN SET VARIABLEDEFS1=(
SELECT CASE WHEN RATES4>7.0 THEN 7.0 ELSE RATES4 END FROM LG_EXCHANGE_119 WITH(NOLOCK)
WHERE convert(varchar, EDATE, 104)= convert(varchar, getdate(), 104) AND CRTYPE=20
)
LOGO SQLINFO MALZEME SATIN ALMA VE SATIS FIYATI DOVIZ KURLARIDA DIKKATE ALINIR
Ayrıntılı Satış raporunda yapıldı…
3 adet METIN TANIMLI ALAN TANIMLANIR
3 adet METIN TANIMLI ALAN TANIMLANIR
1-SAFDOVIZCINSINR,
2-SAFDOVIZCINSIMETIN,
3-SAF ÇIKTI = (ÖR 111 USD)
SATIN ALMA FIYATI(SATIS FIYATI ICIN PTYPE 2 YAPILIR)
—————–
SAFDOVIZCINSINR=_SQLINFO(“CURRENCY”,”LG_116_PRCLIST”,”PTYPE=1 AND CARDREF='”+STR(R2.logicalRef)+”‘”)
SAFDOVIZCINSIMETIN=_SQLINFO(“CURCODE”,”L_CURRENCYLIST”,”CURTYPE='”+[SAFDOVIZCINSINR]+”‘”)
SAF=_SQLINFO(“PRICE”,”LG_116_PRCLIST”,”PTYPE=1 AND CARDREF='”+STR(R2.logicalRef)+”‘”) +” ” + [SAFDOVIZCINSIMETIN])
DOVIZ KURU TEK CINSSE :
SATIN ALMA FIYATI
VAL(_SQLINFO(“PRICE”,”LG_116_PRCLIST”,”PTYPE=1 AND ACTIVE=0 AND CARDREF='”+STR(R2.logicalRef)+”‘”))
SATIS FIYATI
VAL(_SQLINFO(“PRICE”,”LG_116_PRCLIST”,”PTYPE=2 AND ACTIVE=0 AND CARDREF='”+STR(R2.logicalRef)+”‘”))
———
SQLINFO GUNLUK DOVIZ KURU
SAYI :
VAL(_SQLINFO(“RATES3″,”LG_EXCHANGE_116″,”CONVERT(DATE,EDATE)= CONVERT(DATE,GETDATE()) AND CRTYPE='”+[SAFDOVIZCINSINR]+”‘”))
VERIDE ALANA CIFT TIKLA VE
1-TABLO KOLONLARINA EKLE
2-PARAMETRELER KISMINDAN PARASAL DEGER
3-FORMAT 12345.6789
LOGO SQL FATURAYA BAGLI IRSALIYELERIN ESITLIK KONTROLLERI
SELECT *
,CASE WHEN T.FTARIH=T.ITARIH THEN 'ESIT' ELSE 'ESIT DEGIL' END AS TARIHESITMI
,CASE WHEN T.FTUTAR=T.ITUTAR THEN 'ESIT' ELSE 'ESIT DEGIL' END AS TUTARESITMI
,CASE WHEN T.FISYERI=T.IISYERI THEN 'ESIT' ELSE 'ESIT DEGIL' END AS ISYERIESITMI
,CASE WHEN T.FAMBAR=T.IAMBAR THEN 'ESIT' ELSE 'ESIT DEGIL' END AS AMBARESITMI
FROM(
SELECT I.FICHENO FFISNO,I.DATE_ FTARIH,I.NETTOTAL FTUTAR,I.BRANCH FISYERI,I.SOURCEINDEX FAMBAR,
S.FICHENO IFISNO,S.DATE_ ITARIH,S.NETTOTAL ITUTAR,S.BRANCH IISYERI,S.SOURCEINDEX IAMBAR
FROM LG_104_01_INVOICE I WITH(NOLOCK)
LEFT JOIN LG_104_01_STFICHE S ON S.INVOICEREF=I.LOGICALREF WHERE S.TRCODE=1 AND I.TRCODE=1
) AS T
LOGO İRSALİYELERİN TARİHİNİ FATURA TARİHLERİNE EŞİTLEME
--STLINE TARİHİ EŞİTLE
UPDATE INVDATEUPDT SET STLDATE=INVDATE FROM
(
SELECT STL.DATE_ AS STLDATE,
INV.DATE_ AS INVDATE
FROM LG_201_01_STLINE AS STL
LEFT OUTER JOIN
LG_201_01_INVOICE AS INV ON INV.LOGICALREF=STL.INVOICEREF
WHERE STL.INVOICEREF0 AND STL.DATE_INV.DATE_
)AS INVDATEUPDT
----------------------------------------------
--IRSALYE TARİHİ EŞİTLE
UPDATE STFICHEDATEUPD SET STFDATE=INVDATE FROM
(
SELECT INV.DATE_ AS INVDATE,
STF.DATE_ AS STFDATE
FROM LG_201_01_INVOICE AS INV
LEFT OUTER JOIN
LG_201_01_STFICHE AS STF ON INV.LOGICALREF=STF.INVOICEREF
WHERE STF.INVOICEREF0 AND STF.DATE_INV.DATE_
) AS STFICHEDATEUPD
*/
LOGO AMORTİSMAN HESAPLAMA İŞLEMLERİ
önce dbden ilgili yıl ve aydaki hesaplamalar silinir
delete from LG_101_FAYEAR WHERE YEAR_ in (2019) AND CALCMON in (3)
delete from LG_101_FAYEAR WHERE YEAR_ in (2019) AND CALCMON in (3)
duran varlık yönetimi-işlemler-toplu amortisman değerleme/hesaplama
2019 ve 3üncü ay
daha sonra dosya öndeğerleme duran varlık hareketlerinden ilgili yıl ve ayı seç muhasebe masraf değerlerini bağlantıdan okuya bas ve tamama bas.
Daha sonra genel muhasebe – işlemler – muhasebeleştirme – sabit kıymet amortisman ve değerleme işlemleri ilgili yıl ve tamam bas
LOGO KURULU SERVERIN ŞİFRESİ DEĞİŞTİĞİNDE LOGODA SERVİS BAŞLAMAMA HATASI ÇÖZÜMÜ
start run comexp.msc /32
Administrative Tools -> Component Services -> My computer -> DCOM
bunu bul {00CDA438-213D-47A3-AF3E-13DB9290D2A0}
sağ tık identity de mevcut admini kaydet şifresiyle
Administrative Tools -> Component Services -> My computer -> DCOM
bunu bul {00CDA438-213D-47A3-AF3E-13DB9290D2A0}
sağ tık identity de mevcut admini kaydet şifresiyle
SC DELETE “LOGO_TIGER3ENTERPRISE_Service_17500_300794_2_57”
SC CREATE “LOGO_TIGER3ENTERPRISE_Service_17500_300794_2_57″ binpath=”D:\LOGO\TIGER3ENTERPRISE\LOGO_TIGER3ENTERPRISE_Service.exe”
SERVİS YİNEDE ÇALIŞMAZSA VERSİON GUNCELLE OLARAK 2 defa TEKRAR KUR
LOD LOGO OBJECTS DESIGNER
STRUCTURE – > CUSTENTR1
EVENTS BEFORESAVEE AŞAĞIDAKINI YAPIŞTIR
Sub BeforeSave(proceed as Integer)
MaxGridLine=MGridLines(“InvoiceGrid”)-1
for i=0 to MaxGridLine
GetGridRowHandle(“InvoiceGrid”,i,recH)
AppTables[9].GetIntFieldR(recH,”LINETYPE”,intLineType)
AppTables[9].GetIntFieldR(recH,”GLOBTRANS”,intGlobTrans)
AppTables[9].GetIntFieldR(recH,”PARENTLNREF”,intPARENTLNREF)
AppTables[9].GetIntFieldR(recH,”TRCODE”,intTRCODE)
if (intLineType = 1) and (intGlobTrans = 1) and (intPARENTLNREF = 0) and (intTRCODE = 1) then
warn(“Fiş Genelindeki Promosyonları silmelisiniz”)
proceed = 0
end if
next i
End Sub
EVENTS BEFORESAVEE AŞAĞIDAKINI YAPIŞTIR
Sub BeforeSave(proceed as Integer)
MaxGridLine=MGridLines(“InvoiceGrid”)-1
for i=0 to MaxGridLine
GetGridRowHandle(“InvoiceGrid”,i,recH)
AppTables[9].GetIntFieldR(recH,”LINETYPE”,intLineType)
AppTables[9].GetIntFieldR(recH,”GLOBTRANS”,intGlobTrans)
AppTables[9].GetIntFieldR(recH,”PARENTLNREF”,intPARENTLNREF)
AppTables[9].GetIntFieldR(recH,”TRCODE”,intTRCODE)
if (intLineType = 1) and (intGlobTrans = 1) and (intPARENTLNREF = 0) and (intTRCODE = 1) then
warn(“Fiş Genelindeki Promosyonları silmelisiniz”)
proceed = 0
end if
next i
End Sub
Sub BeforeSave(proceed as Integer)
trcode=0
dontsave=1
MaxGridLine=MGridLines("InvoiceGrid")-1
for i=0 to MaxGridLine
GetGridRowHandle("InvoiceGrid",i,recH)
AppTables[9].GetIntFieldR(recH,"LINETYPE",intLineType)
AppTables[9].GetIntFieldR(recH,"GLOBTRANS",intGlobTrans)
AppTables[9].GetIntFieldR(recH,"PARENTLNREF",intPARENTLNREF)
AppTables[9].GetIntFieldR(recH,"TRCODE",intTRCODE)
AppTables[9].GetFltFieldR(recH,"DISCPER",floatDISCPER)
if intTRCODE>0 then
trcode=intTRCODE
end if
'if (intLineType = 1) and (intGlobTrans = 1) and (intPARENTLNREF = 0) and (intTRCODE = 1) then
'warn(floatDISCPER)
if intGlobTrans = 1 and trcode=1 then
if intLineType=2 and floatDISCPER>0 then
dontsave = 0
end if
if intLineType=1 then
dontsave = 0
end if
if dontsave=0 then
warn("Fiş Geneline(Çift Çizgi Altına) Promosyon/Indirim Eklenemez")
proceed=dontsave
end if
end if
next i
End Sub
'LINETYPE 0 MALZEME
'LINETYPE 1 PROMOSYON
'LINETYPE 2 INDIRIM
logo sql mail at sp_Send_DBMail
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
EXEC sp_configure 'Database Mail XPs'
go*/
/*management database mailden MAIL_PROFILE isminde profil yarat*/
DECLARE @Delimiter Char(1)
DECLARE @Dosyaadi VARCHAR(249)
SET @Delimiter = CHAR(9) /*Tab char(9) Line feed char(10) Carriage return char(13)*/
SET @Dosyaadi='Sanayi_Güvenli_Stok_Seviye_Kontrol_'+CONVERT(VARCHAR(10),GETDATE(),104)+'-'+ REPLACE( CONVERT(VARCHAR(8), CONVERT(TIME, GETDATE())),':','.')+'.xls';
EXEC MSDB.dbo.sp_Send_DBMail
@profile_name = 'MAIL_PROFILE',
@Recipients='ALICI@MAIL.com',
@Subject='SUBJECT',
@Body='BODY',
@Query='select TOP 10 * from LG_101_ITEMS;',
@Attach_Query_Result_As_File = 1,
@Query_Result_Header = 1,
@Query_Attachment_Filename = @Dosyaadi,
@query_result_no_padding=1,
@query_result_width = 32767,
@Query_Result_Separator = @Delimiter ,
@execute_query_database = 'DATABASENAME'
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
//HTML FORMAT (MAIL_PROFILE Unutma)
DECLARE @HTML VARCHAR(MAX);
SET @HTML = CAST(DAY(GETDATE()) AS VARCHAR)+'.'+CAST(MONTH(GETDATE()) AS VARCHAR)+'.'+CAST(YEAR(GETDATE()) AS VARCHAR)+' '+
CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR)+':'+CAST(DATEPART(MINUTE, GETDATE()) AS VARCHAR)+' Tarihli Xx Aktarım Kontrolü
<table border="1">
<tr>
<th>TABLO</th>
<th>BUGUN_XXDEN_LOGOYA_AKTARILAN_SATIR_ADET</th>
</tr>' +
CAST (
(
SELECT TOP 20
td = TABLO, '',
td = BUGUN_XXDEN_LOGOYA_AKTARILAN_SATIR_ADET
FROM [LOGO].[dbo].[BM_301_XXTRANSACTIONSMAIL]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) + '
</table>';
-- Envia o e-mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MAIL_PROFILE', -- sysname
@recipients = 'xx@gmail.com', -- varchar(max)
@subject = N'Bilmark XxTransaction Control', -- nvarchar(255)
@body = @HTML, -- nvarchar(max)
@body_format = 'html'
//Clear mail queue
DECLARE @GETDATE datetime
SET @GETDATE = GETDATE();
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE;
GO
LOGO SQL MUHASEBE FİŞLERİ KAYNAK FİŞ TARİH KONTROLU
CREATE FUNCTION [dbo].[BMF_EMFICHE_MODULENR_KAYNAKFISTURU] (@EMFICHEMODULENR INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE @EMFICHE_MODULENR_KAYNAKFISTURU VARCHAR(255);
SET @EMFICHE_MODULENR_KAYNAKFISTURU = (
CASE @EMFICHEMODULENR
WHEN 0 THEN '(0) Kaynak Fiş Türü Olmayan'
WHEN 1 THEN '(1) Malzeme Fişleri'
WHEN 2 THEN '(2) Satın Alma Faturası'
WHEN 3 THEN '(3) Satis Faturasi'
WHEN 4 THEN '(4) Cari Hesap Fisleri'
WHEN 5 THEN '(5) Cek/Senet Bordrosu'
WHEN 6 THEN '(6) Banka Fisleri'
WHEN 7 THEN '(7) Kasa Islemleri'
WHEN 20 THEN '(20) Dagitim Fisi'
WHEN 25 THEN '(25) Sabit Kiymet'
WHEN 160 THEN '(160) Teminat Bordrolari'
WHEN 170 THEN '(170) Leasing Islemleri'
END
)
RETURN @EMFICHE_MODULENR_KAYNAKFISTURU
END
GO
CREATE FUNCTION [dbo].[BMF_EMFLINE_TRCODE_ACIKLAMA] (@EMFLINETRCODE INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE @BMF_EMFLINE_TRCODE_ACIKLAMA VARCHAR(255);
SET @BMF_EMFLINE_TRCODE_ACIKLAMA = (
CASE @EMFLINETRCODE
WHEN 1 THEN '(1) Açılış Fişi'
WHEN 2 THEN '(2) Tahsil Fişi'
WHEN 3 THEN '(3) Tediye Fişi'
WHEN 4 THEN '(4) Mahsup Fişi'
WHEN 5 THEN '(5) Özel Fiş'
WHEN 6 THEN '(6) Kur Farkı Fişi'
WHEN 7 THEN '(7) Kapanış Fişi'
WHEN 8 THEN '(8) Enflasyon Muh. Fişi'
WHEN 9 THEN '(9) Konsolidasyon Düzeltme Fişi'
WHEN 10 THEN '(10) TFRS Düzeltme Fişi'
END
)
RETURN @BMF_EMFLINE_TRCODE_ACIKLAMA
END
GO
----------------
SELECT * FROM (
SELECT
-- DISTINCT dbo.BMF_EMFICHE_MODULENR_KAYNAKFISTURU(EMFICHE.MODULENR) KAYNAKFISTURUMODULENR,dbo.[BMF_EMFICHE_TRCODE_FISTURU] (EMFICHE.TRCODE) FISTURUTRCODE
dbo.BMF_EMFICHE_MODULENR_KAYNAKFISTURU(EMFICHE.MODULENR) KAYNAKFISTURUMODULENR,dbo.[BMF_EMFICHE_TRCODE_FISTURU] (EMFICHE.TRCODE) FISTURUTRCODE,
EMFICHE.FICHENO,
EMFICHE.DATE_ EMFICHEDATE,
CASE EMFICHE.MODULENR
WHEN 1 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_STFICHE K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 2 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_INVOICE K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 3 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_INVOICE K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 4 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_CLFICHE K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 5 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_CSROLL K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 6 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_BNFICHE K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 7 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_KSLINES K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 11 THEN (SELECT TOP 1 K.DATE_ FROM LG_211_01_CSROLL K WHERE K.LOGICALREF=EMFLINE.SOURCEFREF)
WHEN 0 THEN EMFICHE.DATE_
END KAYNAKFISDATE
FROM LG_211_01_EMFICHE EMFICHE WITH(NOLOCK)
LEFT JOIN LG_211_01_EMFLINE EMFLINE ON EMFICHE.LOGICALREF=EMFLINE.ACCFICHEREF
) AS T WHERE T.EMFICHEDATET.KAYNAKFISDATE
Blog of Sem 