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<>''
Tag: LOGO
Logo Çek Sorgusu
SELECT
C.BRANCH ISYERI_KODU,
CONVERT(VARCHAR,CAPIDIV.NR)+', '+CAPIDIV.NAME ISYERI,
LG_211_CLCARD_CEKGIRISCARI.LOGICALREF CEK_GIRIS_CARIREF,
LG_211_CLCARD_CEKGIRISCARI.CODE CEK_GIRIS_CARI_KOD,
LG_211_CLCARD_CEKGIRISCARI.DEFINITION_ CEK_GIRIS_CARI,
ISNULL(PAYPLANS.CODE+', '+PAYPLANS.DEFINITION_,'') CEK_GIRIS_CARI_VADE,
LG_211_CLCARD_CEKGIRISCARI.SPECODE CEK_GIRIS_CARI_OZELKOD,
LG_211_CLCARD_CEKGIRISCARI.SPECODE2 CEK_GIRIS_CARI_OZELKOD2,
LG_211_CLCARD_CEKGIRISCARI.SPECODE3 CEK_GIRIS_CARI_OZELKOD3,
LG_211_CLCARD_CEKGIRISCARI.SPECODE4 CEK_GIRIS_CARI_OZELKOD4,
LG_211_CLCARD_CEKGIRISCARI.SPECODE5 CEK_GIRIS_CARI_OZELKOD5,
ISNULL((
SELECT SUM(P.TOTAL) TOPLAM FROM BM_211_01_PAYTRANS P WITH(NOLOCK) WHERE P.SIGN=0 AND P.PAID=0 AND P.CANCELLED=0 AND P.CARDREF = LG_211_CLCARD_CEKGIRISCARI.LOGICALREF AND P.BRANCH=C.BRANCH AND P.TRCURR=LG_211_01_CSROLL.TRCURR
),0) CEK_GIRIS_CARI_BAKIYE,
CASE LG_211_01_CSROLL.TRCURR
WHEN 0 THEN 'TL'
WHEN 160 THEN 'TL'
WHEN 1 THEN 'USD'
WHEN 17 THEN 'GBP'
WHEN 20 THEN 'EUR'
ELSE CONVERT(VARCHAR,LG_211_01_CSROLL.TRCURR) END CEK_DOVIZ,
LG_211_CLCARD_2.CODE AS KİMDEN,
/*C.DOC AS [çek 1 senet 2], */
C.BANKNAME AS BANKA,
Substring(C.BNBRANCHNO, 6, 12) AS ŞUBE,
C.NEWSERINO AS [Seri No],
/*Substring(CONVERT(varchar(10), C.DUEDATE, 103), 7, 4) + Substring(CONVERT(varchar(10), C.DUEDATE, 103), 4, 2) + Substring(CONVERT(varchar(10), C.DUEDATE, 103), 1, 2) AS vadesi, */
C.AMOUNT AS Tutar,
CASE CURRSTAT
WHEN 1 THEN 'Portföyde'
WHEN 2 THEN 'Ciro Edildi'
WHEN 3 THEN 'Teminata Verildi'
WHEN 4 THEN 'Tahsile Verildi'
WHEN 5 THEN 'Protestolu Tahsile Verildi'
WHEN 6 THEN 'İade Edildi'
WHEN 7 THEN 'Protesto Edildi'
WHEN 8 THEN 'Tahsil Edildi'
WHEN 9 THEN 'Kendi Çekimiz'
WHEN 10 THEN 'Borç Senedimiz'
WHEN 11 THEN 'Karşılığı Yok'
WHEN 12 THEN 'Tahsil Edilemiyor'
ELSE 'Ne Oldugu Belirsiz'
END
AS DURUMU,
C.OWING AS [CİRO EDEN],
(SELECT TOP 1 ASD.DATE_ FROM LG_211_01_CSTRANS ASD WITH(NOLOCK) WHERE ASD.CSREF=LG_211_01_CSTRANS_2.CSREF AND ASD.STATUS=1 ORDER BY ASD.LOGICALREF ASC) CEK_GIRIS_TARIHI,
C.SETDATE AS TARIH,
C.DUEDATE AS VADE,
LG_211_BANKACC.CODE
BANKA
FROM LG_211_01_CSCARD AS C WITH(NOLOCK)
INNER JOIN dbo.LG_211_CLCARD AS LG_211_CLCARD_2 WITH(NOLOCK)
INNER JOIN dbo.LG_211_01_CSROLL WITH(NOLOCK) ON LG_211_CLCARD_2.LOGICALREF = dbo.LG_211_01_CSROLL.CARDREF
INNER JOIN dbo.LG_211_01_CSTRANS AS LG_211_01_CSTRANS_2 WITH(NOLOCK) ON dbo.LG_211_01_CSROLL.LOGICALREF = LG_211_01_CSTRANS_2.ROLLREF ON C.LOGICALREF = LG_211_01_CSTRANS_2.CSREF
INNER JOIN LG_211_BANKACC WITH(NOLOCK) ON LG_211_BANKACC.LOGICALREF = LG_211_01_CSTRANS_2.CARDREF
INNER JOIN dbo.LG_211_CLCARD AS LG_211_CLCARD_CEKGIRISCARI WITH(NOLOCK) ON LG_211_CLCARD_CEKGIRISCARI.LOGICALREF=(SELECT TOP 1 ASD.CARDREF FROM LG_211_01_CSTRANS ASD WITH(NOLOCK) WHERE ASD.CSREF=LG_211_01_CSTRANS_2.CSREF AND ASD.STATUS=1 ORDER BY ASD.LOGICALREF ASC)
INNER JOIN LG_211_PAYPLANS PAYPLANS WITH(NOLOCK) ON PAYPLANS.LOGICALREF= LG_211_CLCARD_CEKGIRISCARI.PAYMENTREF
INNER JOIN L_CAPIDIV CAPIDIV WITH(NOLOCK) ON CAPIDIV.NR=C.BRANCH AND CAPIDIV.FIRMNR=211
Not: https://github.com/ugurozpinar/Logo/blob/master/cekler.sql Revizesidir… Emeğe saygılar…
LOGO SQL AYRINTILI SEVK BİLGİLERİ
BAŞLIKLAR:
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.FICHENO Sipariş_No,
CLCARD.CODE AS Müşteri_No,
CLCARD.DEFINITION_ AS Müşteri_Adı,
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 LINETYPE = 0
AND LG_101_01_ORFICHE.DEPARTMENT = 2
ORDER BY
Sipariş_Tarihi DESC
LOD CUSTOM FORMA CUSTOM TABLEDAN UPDATE İŞLEMİ
Not: Custom tableda tek row olacaktır db kullanıcı bilgilerini tutacaktır. Dolayısıyla Insert işlemi olmayacaktır.Sadece update olacaktır
.
Sub ButtonClick(ctrl as String)
if ctrl="B_SAVE" then
DbErr=0
CustTables[4].Search(2,2,1)'tek row olacağı için sondaki 1 olmalı ilk logref yani
if DbErr<>0 Then
CustTables[4].NewRecord()
End if
GetTextVal("T_SERVER",T_SERVER)
GetTextVal("T_DB",T_DB)
GetTextVal("T_SQL",T_SQL)
GetTextVal("T_PAROLA",T_PAROLA)
CustTables[4].SetTxtField("SERVER", T_SERVER)
CustTables[4].SetTxtField("DB", T_DB)
CustTables[4].SetTxtField("USERNAME", T_SQL)
CustTables[4].SetTxtField("PASSWORD", T_PAROLA)
DbErr=0
CustTables[4].UpdateRecord()
if DBErr<>0 then
Warn(DBErr)
end if
CloseForm()
end if
End Sub
Sub FormShow()
DbErr=0
CustTables[4].Search(2,2,1)'tek row olacağı için sondaki 1 olmalı ilk logref yani
if DbErr=0 Then
CustTables[4].GetTxtField("SERVER",T_SERVER)
CustTables[4].GetTxtField("DB",T_DB)
CustTables[4].GetTxtField("USERNAME",T_SQL)
CustTables[4].GetTxtField("PASSWORD",T_PAROLA)
end if
SetTextVal("T_SERVER",T_SERVER)
SetTextVal("T_DB",T_DB)
SetTextVal("T_SQL",T_SQL)
SetTextVal("T_PAROLA",T_PAROLA)
End Sub
LOGO SQL ÜRETİM MİKTAR İSTASYON GİDER
SELECT
ITEMS.CODE 'Malzeme Kodu',
ITEMS.NAME 'Malzeme Adı',
year(PRODORD.DATE_) 'Yıl',
MONTH(PRODORD.DATE_) 'Ay',
(SELECT CODE FROM LG_212_ROUTING WHERE LOGICALREF=PRODORD.ROUTINGref) 'Rota Kodu',
PRODORD.ACTAMOUNT 'Miktar',WORKSTAT.CODE 'İş İstasyonu Kodu',
WORKSTAT.LOGICALREF 'İş İstasyonu Adı',
PRODORD.DATE_ 'Üretim Tarihi',PRODORD.FICHENO 'Üretim No',PRODORD.ACTMATERIALCOST 'Malzeme Maliyeti',
PRODORD.ACTOVERHCOST 'Gider Maliyet',PRODORD.ACTTOTALCOST 'Toplam Maliyet',
(SELECT CODE FROM LG_212_OPERTION WHERE LOGICALREF=DISPLINE.OPERATIONREF) 'Operasyon Kodu',
(SELECT NAME FROM LG_212_OPERTION WHERE LOGICALREF=DISPLINE.OPERATIONREF) 'Operasyon Adı',
OVERHEADS.CODE+' '+ OVERHEADS.DEFINITION 'Gider Kodu/Adı',OVHDTRANS.TOTAL 'Gider Tutarı'
--PRODORD.PLNDURATION 'Planlanan Süre'
FROM LG_212_PRODORD PRODORD
LEFT JOIN LG_212_OVHDTRANS OVHDTRANS ON OVHDTRANS.PRODORDREF=PRODORD.LOGICALREF
LEFT JOIN LG_212_OVRHDCENTERLN OVRHDCENTERLN ON OVHDTRANS.OVHDCLNREF=OVRHDCENTERLN.LOGICALREF
LEFT JOIN LG_212_OVERHEADS OVERHEADS ON OVERHEADS.LOGICALREF = OVRHDCENTERLN.OVERHEADREF
LEFT JOIN LG_212_DISPLINE DISPLINE ON DISPLINE.PRODORDREF = PRODORD.LOGICALREF
LEFT JOIN LG_212_ITEMS ITEMS ON ITEMS.LOGICALREF = PRODORD.ITEMREF
LEFT JOIN LG_212_WORKSTAT WORKSTAT ON WORKSTAT.LOGICALREF= DISPLINE.WSREF
/*ESKI HALI
FROM LG_212_OVRHDCENTERLN OVRHDCENTERLN
INNER JOIN LG_212_OVERHEADS OVERHEADS ON OVERHEADS.LOGICALREF = OVRHDCENTERLN.OVERHEADREF
INNER JOIN LG_212_OVHDTRANS OVHDTRANS ON OVHDTRANS.OVHDCLNREF = OVRHDCENTERLN.LOGICALREF
INNER JOIN LG_212_PRODORD PRODORD ON PRODORD.LOGICALREF = OVHDTRANS.PRODORDREF
INNER JOIN LG_212_DISPLINE DISPLINE ON DISPLINE.PRODORDREF = PRODORD.LOGICALREF AND DISPLINE.LOGICALREF = OVHDTRANS.WORKORDREF
INNER JOIN LG_212_ITEMS ITEMS ON ITEMS.LOGICALREF = PRODORD.ITEMREF
INNER JOIN LG_212_WORKSTAT WORKSTAT ON WORKSTAT.LOGICALREF= DISPLINE.WSREF
LOGO BORÇ TAKİP KONTROLLERİ SQL
--BORÇ TAKİPTE OLUP DİĞER MODULLERDE OLMAYAN
SELECT *
FROM LG_001_01_PAYTRANS
WHERE
(
(MODULENR=4 AND TRCODE IN (6,7,8,9,41) AND SIGN=1) OR
(MODULENR=5 AND TRCODE IN (2,3) AND SIGN=1) OR
(MODULENR=6 AND TRCODE IN (3,4) AND SIGN=1)OR
(MODULENR=7 AND TRCODE IN (4) AND SIGN=1)OR
(MODULENR=10 AND TRCODE IN (2) AND SIGN=1)OR
(MODULENR=4 AND TRCODE IN (1,2,3,4,26,42) AND SIGN=0)OR
(MODULENR=5 AND TRCODE IN (1,4) AND SIGN=0)OR
(MODULENR=6 AND TRCODE IN (1,2) AND SIGN=0)OR
(MODULENR=7 AND TRCODE IN (3) AND SIGN=0)OR
(MODULENR=10 AND TRCODE IN (1) AND SIGN=0)
)
OR
(
((MODULENR = 0) AND (TRCODE=0) AND (FICHEREF >= 0))OR
((MODULENR>=0) AND (TRCODE=0)AND(FICHEREF=0)) OR
((MODULENR=0) AND (TRCODE>=0)AND(FICHEREF=0))
)
OR
(
(MODULENR=4 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_INVOICE)) OR
(MODULENR=5 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_CLFLINE)) OR
(MODULENR=6 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_CSROLL)) OR
(MODULENR=7 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_BNFLINE)) OR
(MODULENR=10 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_KSLINES))
)
--FİŞİ OLMAYAN CARİ HESAP FİŞLERİ
SELECT * FROM LG_001_01_CLFLINE
WHERE MODULENR=5 AND SOURCEFREF NOT IN(SELECT LOGICALREF FROM LG_001_01_CLFICHE)
--CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN(SELECT LOGICALREF FROM LG_001_CLCARD;
--CARİ HESAP HAREKET HATALI SATIRLAR
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 AND
SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_INVOICE)
Merhaba,
Yedek aldıktan sonra işlemleri yapınız.
---------FİŞİ OLMAYAN CARİ HESAP HAREKETLERİ
SELECT * FROM LG_001_01_CLFLINE
WHERE MODULENR=5 AND SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_CLFICHE)
---------CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN (SELECT LOGICALREF FROM LG_001_CLCARD)
---------CARİ HESAP HAREKET HATALI SATIRLAR
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 AND
SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_INVOICE)
***********************
BORÇ TAKİP İLE CARİ HESAP HAREKETLERİNİ KARŞILAŞTIRIYOR
SELECT * FROM LG_208_01_PAYTRANS P WITH(NOLOCK) LEFT OUTER JOIN LG_208_01_CLFLINE L WITH(NOLOCK) ON P.FICHEREF=L.SOURCEFREF AND P.MODULENR=L.MODULENR WHERE P.MODULENR NOT IN (5,61,62,63,64) AND L.LOGICALREF IS NULL UNION
SELECT * FROM LG_208_01_PAYTRANS P WITH(NOLOCK) LEFT OUTER JOIN LG_208_01_CLFLINE L WITH(NOLOCK) ON P.FICHEREF=L.LOGICALREF AND P.MODULENR=L.MODULENR WHERE P.MODULENR IN (5,61,62,63,64) AND L.LOGICALREF IS NULL
*****************
CARİ HESAP HAREKETLERİ İLE BORÇ TAKİP KARŞILAŞTIRIYOR
SELECT * FROM LG_208_01_CLFLINE L WITH(NOLOCK) LEFT OUTER JOIN LG_208_01_PAYTRANS P WITH(NOLOCK) ON L.SOURCEFREF=P.FICHEREF AND L.MODULENR=P.MODULENR WHERE L.MODULENR NOT IN (5,61,62,63,64) AND L.AMOUNT>0
-- AND L.CLIENTREF=140
AND P.LOGICALREF IS NULL
UNION
SELECT
*
FROM
LG_208_01_CLFLINE L WITH(NOLOCK)
LEFT OUTER JOIN LG_208_01_PAYTRANS P WITH(NOLOCK) ON L.LOGICALREF=P.FICHEREF AND L.MODULENR=P.MODULENR
WHERE
L.MODULENR IN (5,61,62,63,64)
AND L.AMOUNT>0
-- AND L.CLIENTREF=140
AND P.LOGICALREF IS NULL
SELECT * FROM LG_208_01_PAYTRANS WHERE MODULENR=10 AND FICHEREF=19372
SELECT * FROM LG_208_01_CLFLINE WHERE MODULENR=10 AND SOURCEFREF=19372
------CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN (SELECT LOGICALREF FROM LG_001_CLCARD)
------CARİ HESAP HAREKET HATALI SATIRLAR
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 AND
SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_INVOICE)
-------FATURASI OLUP CARI HESAP HAREKETİ OLMAYANLAR
SELECT * FROM LG_001_01_INVOICE WHERE TRCODE=8 AND LOGICALREF NOT IN (
SELECT SOURCEFREF FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 )
------FİŞİ OLMAYAN CARİ HESAP HAREKETLERİ
SELECT * FROM LG_001_01_CLFLINE
WHERE MODULENR=5 AND SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_CLFICHE)
-----CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN (SELECT LOGICALREF FROM LG_001_CLCARD)
----CARİ HESAP HAREKETLERİNDE OLUP CARİ HESAP FİŞİ BULUNMAYAN KAYITLAR İÇİN
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=5 AND SOURCEFREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_CLFICHE)
----CARİ HESAP FİŞİ OLAN ANCAK CARİ HAREKETLERDE GÖZÜKMEYEN KAYITLAR İÇİN
SELECT * FROM LG_001_01_CLFICHE WHERE LOGICALREF NOT IN
(SELECT SOURCEFREF FROM LG_001_01_CLFLINE WHERE MODULENR=5)
LOGO YIL AY BAZLI MALZEME SATIŞLARI
SELECT * FROM (
SELECT
ITMSM.YEAR_ YIL,
ITMSM.MONTH_ AY,
I.CODE URUNKODU,
I.NAME URUNADI,
SUM(ITMSM.SALES_AMOUNT) SATIS_MIKTAR
FROM
LV_119_01_STINVENS ITMSM
WITH(NOLOCK)
LEFT JOIN LG_119_ITEMS I WITH(NOLOCK) ON I.LOGICALREF=ITMSM.STOCKREF
WHERE
(ITMSM.INVENNO = -1) AND (( ITMSM.MONTH_ >= 1 ) AND ( ITMSM.MONTH_ <= 12 ) ) AND (ITMSM.MTRLINC = 0) AND (ITMSM.VIRTUALINVEN = 0)
GROUP BY ITMSM.YEAR_,
ITMSM.MONTH_,I.CODE,I.NAME ) AS T ORDER BY YIL,AY,URUNADI
LOGO DEVİR MALZEME FİŞİ DEVRALINAN STLINEDAN UPDATE İŞLEMİ
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
Blog of Sem 