seen from United States
seen from Australia
seen from Norway
seen from China
seen from Malaysia
seen from United States
seen from United States
seen from China
seen from United States
seen from Türkiye
seen from United States
seen from Canada
seen from Malaysia
seen from Malaysia
seen from Malaysia
seen from United States
seen from Malaysia
seen from United States
seen from Poland
seen from China
Logo Diğer Malzeme Fişlerinde Sayım Eksiği veya Sayım Fazlası Fişi Düzeltme
Logo’da Ambar sayımı ya da stok düzeltme zamanında elle girilen Sayım fazlası ya da sayım eksiği fişlerinde hatalı girilen stok verilerini silip yeniden girmeden gerekli Sql düzeltmesi.. Hatalı veri kaydedildikten sonraki yapılacak iş Örneğin Sayım Fazlası yerine sayım eksiği girilen fişi düzeltme ÖNCE SELECT İLE AMBAR FİŞİNİN LOGICALREF KODUNU SQL’DE BULMA SELECT * FROM…
Sql Çoklu Where Sorgusu
SELECT * FROM LG_001_PRCLIST
WHERE PTYPE='2' AND (CODE='TM001' OR CODE='TM999')
Cari Bakiye Son Durum
SELECT CODE AS [CARİ KODU], DEFINITION_ [CARİ ADI], BAKIYE AS [BAKİYE],CITY AS [ŞEHİR],ADDR1,ADDR2,COUNTRY,TELNRS1,TELNRS2,FAXNR,TAXNR,EMAILADDR,TRADINGGRP,INVSENDEMAILADDR,
[B/A]=CASE WHEN B.BAKIYE < 0 THEN 'A' WHEN B.BAKIYE > 0 THEN 'B' ELSE '' END
FROM (SELECT CL.CODE, CL.DEFINITION_, CL.CITY,CL.ADDR1,CL.ADDR2,CL.COUNTRY,CL.POSTCODE,CL.TELNRS1,CL.TELNRS2, CL.FAXNR,CL.TAXNR,CL.EMAILADDR,CL.TRADINGGRP,CL.INVSENDEMAILADDR, ISNULL((SELECT(SUM (GT.DEBIT)- SUM (GT.CREDIT))
FROM LV_001_05_GNTOTCL GT
WHERE(GT.TOTTYP = 1) AND GT.CARDREF = CL.LOGICALREF), 0) BAKIYE,'' BA
FROM LG_001_CLCARD CL
WHERE CL.ACTIVE = 0) AS B
ORDER BY BAKIYE DESC
esinti kaynağı: https://semt20.home.blog/2021/08/17/logo-cari-hesap-listesi-bakiye-bakiyerd/
Logo Sql Fatura Hareket Durumu
SELECT I.CODE [KODU],I.NAME [STOK ADI], ST.AMOUNT [MİKTAR], ST.PRICE [FİYAT], ST.VATAMNT [KDV],ST.VATMATRAH [ARA TOP.],ST.DISTCOST [İNDİRİM],ST.TOTAL [TOPLAM], st.DATE_ [TARİH], CL.CODE [CARİ KODU],CL.DEFINITION_ [CARİ ADI],STFICHE.FICHENO [İRS.NO],INV.FICHENO [FATURA NO], INV.DOCODE [BELGE NO],INV.SPECODE [ÖZEL KODU], INV.CYPHCODE[CYPHCODE], ST.LINEEXP [AÇIKLAMA],
'Fiş Türü'= case ST.TRCODE
WHEN 1 THEN 'Mal Alım İrsaliyesi'
WHEN 2 THEN 'Per.Sat.İade İrs'
WHEN 3 THEN 'Top. Sat. İade İrs'
WHEN 6 THEN 'Alım İade İrs'
WHEN 7 THEN 'Per. Sat. İade İrs'
WHEN 8 THEN 'Top. Satış. İrs'
WHEN 11 THEN 'Fire Fişi'
WHEN 12 THEN 'Sarf Fişi'
WHEN 13 THEN 'Üretim Fişi'
WHEN 14 THEN 'Devir Fişi'
WHEN 50 THEN 'Sayım Fazlası Fişi'
WHEN 51 THEN 'Sayım Eksiği Fişi ' END,
'G/Ç'= case ST.IOCODE
WHEN 0 THEN 'Giriş'
WHEN 1 THEN 'Giriş'
WHEN 2 THEN 'Giriş'
WHEN 3 THEN 'Çıkış'
WHEN 4 THEN 'Çıkış' END
FROM LG_001_05_STLINE ST
JOIN LG_001_ITEMS I ON ST.STOCKREF=I.LOGICALREF
JOIN LG_001_CLCARD CL ON ST.CLIENTREF=CL.LOGICALREF
JOIN LG_001_05_INVOICE INV ON INV.LOGICALREF=ST.INVOICEREF
JOIN LG_001_05_STFICHE STFICHE ON STFICHE.LOGICALREF=ST.STFICHEREF
AND ST.DATE_ >= '2020-01-02 00:00:00.000'
AND ST.DATE_ <= '2025-12-31 23:59:59.999'
ORDER BY ST.DATE_
=================
1:Mal alım irsaliyesi, 2:Per. sat. iade irs., 3:Topt.sat. iade irs.,
4:Kons. çıkış iade irs., 5:Konsinye giriş irs., 6:Alım iade irs.,
7:Perakende satış irs., 8:Toptan satış irs., 9:Konsinye çıkış irs.,
10:Konsinye giriş iade irs., 11:Fire fişi, 12:Sarf fişi, 13:üretimden giriş fişi,
14:Devir fişi, 25:Ambar fişi, 26:Mustahsil irs., 50:Sayım Fazlası Fişi,
51: Sayım Eksiği Fişi
Malzeme Ambar Durum Raporu (Price destekli)
SELECT URUN.ACTIVE AS 'DURUM AKT-PAS', URUN.CODE AS 'ÜRÜN KODU', URUN.NAME AS 'ÜRÜN ADI', URUN.SPECODE2 AS 'ÖZEL KOD2', URUN.SPECODE3 AS 'ÖZEL KOD3', URUN.STGRPCODE AS 'GRUP KODU', URUN.PRODUCERCODE AS 'ÜRETİCİ KODU', URUN.SPECODE AS 'RAF YERİ', 'FİİLİ STOK '= CASE WHEN (AMBARM.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR END, 'A.FIYAT'= CASE WHEN (ALIS.MIKTAR IS NULL) THEN '0' WHEN (ALIS.MIKTAR IS NOT NULL) THEN ALIS.MIKTAR END, case ALIS.CURRENCY WHEN 1 THEN 'USD' WHEN 20 THEN 'EURO' WHEN 160 THEN 'TL' END AS [DÖVİZ AÇIKLAMA], 'S.FIYAT'= CASE WHEN (SATIS.MIKTAR IS NULL) THEN '0' WHEN (SATIS.MIKTAR IS NOT NULL) THEN SATIS.MIKTAR END, case SATIS.CURRENCY WHEN 1 THEN 'USD' WHEN 20 THEN 'EURO' WHEN 160 THEN 'TL' END AS [DÖVİZ TÜRÜ], 'A.DEGER'= CASE WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * ALIS.MIKTAR END, 'S.DEGER'= CASE WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * SATIS.MIKTAR END, URUN.CANDEDUCT AS 'TEVKİFAT', URUN.IMAGEINC AS 'RESİM' FROM {oj LG_001_ITEMS URUN LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_05_STINVTOT WHERE INVENNO=-1 GROUP BY STOCKREF) AMBARM ON URUN.LOGICALREF = AMBARM.STOCKREF LEFT JOIN ((SELECT MAX(PRICE) AS 'MIKTAR',CARDREF,CURRENCY FROM LG_001_PRCLIST PRC WHERE PTYPE=1 GROUP BY CARDREF,CURRENCY)) ALIS ON URUN.LOGICALREF = ALIS.CARDREF LEFT JOIN ((SELECT MAX(PRICE) AS 'MIKTAR',CARDREF,CURRENCY FROM LG_001_PRCLIST PRC WHERE PTYPE=2 GROUP BY CARDREF,CURRENCY)) SATIS ON URUN.LOGICALREF = SATIS.CARDREF } WHERE URUN.CARDTYPE<>'22' ORDER BY URUN.CODE =====================
Ambar tek olduğu için diğer ambarlar silindi. Çoklu ambar ihtiyaç halinde aşağıdaki bağlantıda mevcut.
Yapılacak çalışma: Fiyat döviz türü tabloya eklenmeli ..... EKLENDİ...
Sonraki İş: Döviz fiyat türünü isim olarak referans al
SON DURUM: CASE when then end sorgusu ile Currency ler ilişkilendirildi....
Esinlenme: excelvba.net/viewtopic.php?t=25838&f=105&sid=8ae52c41d23111419a9e315745bafb26#p196350
Logo Sql Hareket gören Malzeme birim seti değiştirme
BU SORGUDA ADET OLAN ÜRÜN BİRİM SETİNİ TAKIMA ÇEVRİLDİ....
SEÇİLEN MALZEMENİN LOGICALREF ==>> 3467
DEĞİŞECEK BİRİM SETİ OLAN "TAKIM " IN LOGICALREF==>> 27 İKEN UNITSETREF ==>> 9
=====================================
/*MALZEME */SELECT LOGICALREF FROM dbo.LG_001_ITEMS WITH(NOLOCK) WHERE CODE='MB.0008603347' /*MALZEMENIN LOGICALREFINI BULMAK ICIN=1367*/
/*BIRIM*/ SELECT LOGICALREF,UNITSETREF,CODE,NAME FROM LG_001_UNITSETL WITH(NOLOCK) WHERE MAINUNIT=1 AND NAME='TAKIM' /*HANGI MALZEME BIRIMI OLACAK ÖRN LOGICALREF=55 UNITSETREF=6*/
/*MALZEMELER EKRANI GUNCELLEME ISLEMI*/
update LG_001_ITEMS set UNITSETREF = 9 /*BIRIMIN UNITSETREFI*/ where LOGICALREF = 3467 /*MALZEMENIN LOGICALREFI*/
update LG_001_ITMUNITA set UNITLINEREF = 27 /*BIRIMIN LOGICALREFI*/ where ITEMREF = 3467 /*MALZEMENIN LOGICALREFI*/
/*RISKLI:GERIYE DONUK HAREKETLERI GUNCELLEMEK ICIN (TALEP,SIPARIS VE IRSALIYE)*/
update LG_001_05_STLINE set UOMREF = 27 /*BIRIMIN LOGICALREFI*/ , USREF = 9 /*BIRIMIN UNITSETREFI*/ where Stockref = 3467 /*MALZEMENIN LOGICALREFI*/
update LG_001_05_ORFLINE set UOMREF = 27 /*BIRIMIN LOGICALREFI*/ , USREF = 9 /*BIRIMIN UNITSETREFI*/ where Stockref = 3467 /*MALZEMENIN LOGICALREFI*/
update LG_001_05_DEMANDLINE set UOMREF = 27 /*BIRIMIN LOGICALREFI*/ , USREF = 9 /*BIRIMIN UNITSETREFI*/ where ITEMREF = 3467 /*MALZEMENIN LOGICALREFI*/
====================================
KONTROL ETMEK İÇİN SORGULAR
SELECT * FROM LG_xx_xx_STLINE
SELECT * FROM LG_xxx_UNITSETF
SELECT * FROM LG_xxx_ITEMS
SELECT * FROM LG_xxx_xx_ORFLINE
kaynak: https://semt20.home.blog/page/12/
Logo Sql Çek-Senet Sorgusu
SELECT
convert(date,SETDATE,104)AS TARİH,
convert(date,DUEDATE,104) AS VADE,
(SELECT TOP (1) CL.DEFINITION_ FROM LG_001_05_CSTRANS AS CST INNER JOIN LG_001_CLCARD AS CL ON CST.CARDREF = CL.LOGICALREF WHERE (CST.CSREF = LGMAIN.LOGICALREF) ORDER BY CST.LOGICALREF) AS [CARİ ÜNVANI],
OWING AS BORÇLU,
CASE WHEN LGMAIN.CURRSTAT = 1 THEN 'Portföyde' WHEN LGMAIN.CURRSTAT = 2 THEN 'Ciro Edildi' WHEN LGMAIN.CURRSTAT = 3 THEN 'Teminata Verildi' WHEN LGMAIN.CURRSTAT = 4 THEN 'Tahsile Verildi'
WHEN LGMAIN.CURRSTAT = 5 THEN 'Tahsile Verildi(Protestolu)' WHEN LGMAIN.CURRSTAT = 6 THEN 'İade Edildi' WHEN LGMAIN.CURRSTAT = 7 THEN 'Protesto Edildi' WHEN LGMAIN.CURRSTAT = 8 THEN 'Tahsil Edildi'
WHEN LGMAIN.CURRSTAT = 9 THEN 'Verilen Çek' when LGMAIN.CURRSTAT = 9 then 'Borç Senedimiz' WHEN LGMAIN.CURRSTAT = 10 THEN 'Borç Senedimiz' WHEN LGMAIN.CURRSTAT = 11 THEN 'Karşılığı Yok' WHEN LGMAIN.CURRSTAT =
12 THEN 'Tahsil Edilemiyor' WHEN LGMAIN.CURRSTAT = 14 THEN 'Portföyde Protestolu' END AS DURUM,
CASE WHEN LGMAIN.DOC = 3 THEN 'Kendi Çekimiz' WHEN LGMAIN.DOC = 2 THEN 'Müşteri Seneti' WHEN LGMAIN.DOC = 4 THEN 'Kendi Senedimiz' WHEN LGMAIN.DOC = 1 THEN 'Müşteri Çeki' END AS TUR,
BANKNAME AS [BANKA AÇIKLAMASI], BNBRANCHNO AS [ŞUBE KODU], BNACCOUNTNO AS [HESAP NO],
NEWSERINO AS SERINO, TRNET AS TUTAR,'DÖVİZ TÜRÜ'=CASE WHEN LGMAIN.TRCURR IN (0,160) THEN 'TL' WHEN LGMAIN.TRCURR=1 THEN 'USD' WHEN LGMAIN.TRCURR=20
THEN 'EUR' ELSE '' END,
[DEVİR] = CASE LGMAIN.DEVIR WHEN 0 THEN 'Hayir' ELSE 'Evet' END,
CASE WHEN LGMAIN.CURRSTAT = 1 THEN '' ELSE
(SELECT TOP 1 CASE WHEN CST.CARDMD = 5 THEN(SELECT CL.DEFINITION_
FROM LG_001_CLCARD CL
WHERE CST.CARDREF = CL.LOGICALREF) WHEN CST.CARDMD = 7 THEN
(SELECT BNK.DEFINITION_
FROM LG_001_BANKACC BNK
WHERE CST.CARDREF = BNK.LOGICALREF) END
FROM LG_001_05_CSTRANS CST
WHERE CST.CSREF = LGMAIN.LOGICALREF
ORDER BY CST.LOGICALREF DESC) END AS [VERİLEN (CARİ/BANKA)], CASE WHEN LGMAIN.CURRSTAT = 1 THEN '' ELSE
(SELECT TOP 1 CASE WHEN CST.CARDMD = 5 THEN
(SELECT CL.CODE
FROM LG_001_CLCARD CL
WHERE CST.CARDREF = CL.LOGICALREF) WHEN CST.CARDMD = 7 THEN
(SELECT BNK.CODE
FROM LG_001_BANKACC BNK
WHERE CST.CARDREF = BNK.LOGICALREF) END
FROM LG_001_05_CSTRANS CST
WHERE CST.CSREF = LGMAIN.LOGICALREF
ORDER BY CST.LOGICALREF DESC) END AS [VERİLEN (CARİ/BANKA) KODU]
FROM LG_001_05_CSCARD AS LGMAIN
ORDER BY DUEDATE ASC
Kaynak: https://excelturkey.com/konu/portfoydeki-cek-durumunda-ciro-eden-bilgisi.729/ Emek için teşekkürler...
Tablodaki bazı alanları temizledim. Biraz daha revize edilmeyi bekliyor....
Logo Go Tüm Faturalar Sorgusu (Cari ile Birlikte)
SELECT 'TÜRÜ'=CASE I.TRCODE
WHEN 1 THEN '1-SATINALMA FAT.'
WHEN 2 THEN '1-PERAKENDE SATIŞ İADE FAT.'
WHEN 3 THEN '1-TOPTAN SATIŞ İADE FAT.'
WHEN 4 THEN '1-ALINAN HİZMET FAT.'
WHEN 6 THEN '2-ALIM İADE FAT.'
WHEN 7 THEN '2-PERAKENDE SATIŞ FAT.'
WHEN 8 THEN '2-TOPTAN SATIŞ FAT.'
WHEN 9 THEN '2-VERİLEN HİZMET FAT.'
WHEN 13 THEN '1-ALINAN FİYAT FARK FAT.'
WHEN 14 THEN '2-VERİLEN FİYAT FARK FAT.'
END,
'GRUP KODU'=CASE I.GRPCODE
WHEN 1 THEN 'GİRİŞ-1'
WHEN 2 THEN 'ÇIKIŞ-2' END,
I.FICHENO [FATURA NO] ,I.DOCODE AS [BELGE NO] , C.CODE [CARİ KODU], C.DEFINITION_ [CARİ AÇIKLAMA],
I.SPECODE AS [ÖZEL KOD] ,I.CYPHCODE AS [YETKİ KODU], DATE_ AS TARİH,I.GROSSTOTAL AS [BRÜT TOPLAM] ,
I.ADDDISCOUNTS AS[GENEL İNDİRİMLER] ,I.TOTALDISCOUNTS AS [SATIR İNDİRİM],I.TOTALDISCOUNTED AS [ARA TOPLAM] , I.TOTALVAT AS [KDV TOPLAM], I.PAYDEFREF [ÖDEME],
I.NETTOTAL AS [NET TOPLAM], I.GENEXP1 AS AÇIKLAMA, I.TRADINGGRP AS [İŞLEM GRUBU],
'İPTAL DURUMU'=CASE I.CANCELLED
WHEN 0 THEN 'İPTAL EDİLMEMİŞ'
WHEN 1 THEN 'İPTAL' END,
'MUHASEBELEŞTİRME'=CASE I.ACCOUNTED
WHEN 0 THEN 'YAPILMAMIŞ '
WHEN 1 THEN 'MUHASEBELEŞTİRİLMİŞ' END
FROM LG_001_05_INVOICE I LEFT OUTER JOIN LG_001_CLCARD C ON C.LOGICALREF=I.CLIENTREF WHERE ACTIVE=0
LG_XXX_XX_INVOICE TABLOSUNUN CARİ İSİM VE CARİ KODLARI İLE BİRLİKTE SORGUSU
