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
Category: LOGO ERP
LOGO SQL Devir Maliyet Raporu
SELECT
(SELECT I.CODE FROM LG_121_ITEMS I WHERE I.LOGICALREF=SS3.SREF) URUNKODU ,
(SELECT I.NAME FROM LG_121_ITEMS I WHERE I.LOGICALREF=SS3.SREF) URUNKODU ,
SS3.MLYT, ST4.AMOUNT
FROM LG_121_01_STLINE AS ST4 LEFT OUTER JOIN
(SELECT ST3.VATMATRAH / ST3.AMOUNT AS MLYT, SS2.SREF FROM
(SELECT MAX(DISTINCT ST2.LOGICALREF) AS LREF, ST2.STOCKREF AS SREF FROM
(SELECT ST1.STOCKREF, MAX(ST1.DATE_) AS DATE_ FROM LG_117_01_STLINE AS ST1 WHERE
(ST1.IOCODE =1) AND (ST1.LINETYPE = 0) AND (ST1.CANCELLED = 0) AND (ST1.PRICE <> 0) GROUP BY ST1.STOCKREF)
AS SS1 LEFT OUTER JOIN LG_117_01_STLINE AS ST2 ON SS1.DATE_ = ST2.DATE_ AND SS1.STOCKREF = ST2.STOCKREF
WHERE (ST2.LINETYPE = 0) AND (ST2.IOCODE =1) AND (ST2.CANCELLED = 0) AND (ST2.PRICE <> 0) GROUP BY ST2.STOCKREF) AS SS2
LEFT OUTER JOIN LG_117_01_STLINE AS ST3 ON SS2.SREF = ST3.STOCKREF AND SS2.LREF = ST3.LOGICALREF) AS SS3
ON ST4.STOCKREF = SS3.SREF
WHERE (ST4.TRCODE = 14) AND (ST4.CANCELLED = 0) AND (ST4.LINETYPE = 0) AND (SS3.MLYT <> 0) AND (ST4.LINENET = 0)
Logo Satış Siparişi Belirli Saate Göre Sql Rapor
Talep: 1/1/2021‘den itibaren cumartesileri saat 14:00dan sonraki satış siparişleri net tutarını gösteren rapor.
SELECT DISTINCT * FROM (
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.TIME_, */
dbo.fn_LogoTimetoSystemTime(LG_101_01_ORFICHE.TIME_) Sipariş_Saat,
LG_101_01_ORFICHE.FICHENO Sipariş_No,
CLCARD.CODE AS Müşteri_No,
dbo.Get_Satis_Eleman_adi(LG_101_01_ORFICHE.SALESMANREF) Satis_Elemani,
CLCARD.DEFINITION_ AS Müşteri_Adı,
CASE LG_101_01_ORFICHE.TRCURR WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END Islem_Dovizi,
LG_101_01_ORFICHE.NETTOTAL Net_Tutar
/*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 LG_101_01_ORFICHE.DATE_ IN (
select
dateadd(dd, number, '20210101')
from
master..spt_values
where
type = 'p'
and year(
dateadd(dd, number, '20210101')
)= year('20210101')
and DATEPART(
dw,
dateadd(dd, number, '20210101')
) = 7
UNION ALL
select
dateadd(dd, number, '20220101')
from
master..spt_values
where
type = 'p'
and year(
dateadd(dd, number, '20220101')
)= year('20220101')
and DATEPART(
dw,
dateadd(dd, number, '20220101')
) = 7
)
AND LG_101_01_ORFICHE.TIME_ > 230000000 /*SAAT 14:00DAN SONRA*/
) AS T5 ORDER BY Sipariş_No DESC
LOGO SQL MALZEME STOKLARI
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
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)
LOGO KASA BROWSERINDA BAKİYE GÖRÜNMEMESİ
Sorun
Firma kopyalamada yeni firmada kasa işlemleri olmasına rağmen kasa bakiyeleri görünmemekte.
Sebep
Kontrol edildiğinde CSHTOTS tablosu DAY_ kolonunda negatif değer olmaması gerekirdi fakat vardı .
Çözüm
Öncelikle aşağıdaki tablolardaki alanlar kontrol edilir(tarih aralıkları)
LG_XXX_XX_TRANSAC (PERIODBEGDATE,PERIODENDDATE)
L_CAPIPERIOD (BEGDATE,ENDDATE)
DAHA SONRA DOSYA->BAKIM İLŞEMLERİ->VERİ TOPLAMLARI->KASA TOPLAMLARI SERVİSİ ÇALIŞTIRILIR.
LOGO MUHASEBE FİŞİ HESAP DAĞITIM BİLGİSİ
CREATE VIEW BMS_211_MUHASEBEFISLERI_HESAPDAGITIM AS
SELECT TOP 100 PERCENT
(
CASE EMFICHE.TRCODE
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
) FISTURU,
EMFICHE.FICHENO FIS_NO,
EMFICHE.DATE_ FIS_TARIH,
(SELECT EMCENTER.CODE FROM LG_211_EMCENTER EMCENTER WHERE EMCENTER.LOGICALREF=ACCDISTDETLN.CENTERREF) MASRAFMERKEZI_KODU,
(SELECT EMCENTER.DEFINITION_ FROM LG_211_EMCENTER EMCENTER WHERE EMCENTER.LOGICALREF=ACCDISTDETLN.CENTERREF) MASRAFMERKEZI_ADI,
EMFLINE.ACCOUNTCODE HESAPKODU,
(SELECT EMUHACC.DEFINITION_ FROM LG_211_EMUHACC EMUHACC WHERE EMUHACC.LOGICALREF=EMFLINE.ACCOUNTREF) HESAPADI,
EMFLINE.DEBIT,
EMFLINE.CREDIT,
ISNULL(ACCDISTDETLN.DISTRATE,0) HESAPDAGITIM_ORAN,
ISNULL(ACCDISTDETLN.TRNET,0) HESAPDAGITIM_TUTAR
FROM LG_211_01_EMFICHE EMFICHE
LEFT JOIN LG_211_01_EMFLINE EMFLINE ON EMFICHE.LOGICALREF=EMFLINE.ACCFICHEREF
LEFT JOIN LG_211_01_ACCDISTDETLN ACCDISTDETLN ON EMFLINE.LOGICALREF=ACCDISTDETLN.PREVLINEREF AND EMFLINE.ACCOUNTREF=ACCDISTDETLN.ACCOUNTREF
/*WHERE EMFICHE.FICHENO='202201002909'*/
ORDER BY EMFICHE.LOGICALREF , EMFLINE.LINENO_
GO
Başlıklar:
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 Satınalma faturalarını Sql üzerinden Sil
Ö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'
Blog of Sem 