SELECT ROW_NUMBER() OVER( ORDER BY INTLOGICALREF ASC) AS INTROWID, * FROM (
SELECT
LINETYPE,
'520' TXTFIRMA,
INVOICE.LOGICALREF INTLOGICALREF,
INVOICE.TRCODE INTFATURATURU,
(CASE
WHEN INVOICE.TRCODE=1 THEN 'Satınalma Faturası'
WHEN INVOICE.TRCODE=2 THEN 'Perakende Satış İade Faturası'
WHEN INVOICE.TRCODE=3 THEN 'Toptan Satış İade Faturası'
WHEN INVOICE.TRCODE=4 THEN 'Alınan Hizmet Faturası'
WHEN INVOICE.TRCODE=5 THEN 'Alınan Proforma Faturası'
WHEN INVOICE.TRCODE=6 THEN 'Satınalma İade Faturası'
WHEN INVOICE.TRCODE=7 THEN 'Perakende Satış Faturası'
WHEN INVOICE.TRCODE=8 THEN 'Toptan Satış Faturası'
WHEN INVOICE.TRCODE=9 THEN 'Verilen Hizmet Faturası'
WHEN INVOICE.TRCODE=10 THEN 'Verilen Proforma Faturası'
WHEN INVOICE.TRCODE=12 THEN 'Alınan Vade Farkı Faturası'
WHEN INVOICE.TRCODE=13 THEN 'Satınalma Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=14 THEN 'Satış Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=26 THEN 'Müstahsil Makbuzu'
WHEN INVOICE.TRCODE=1 THEN 'Satınalma Faturası'
WHEN INVOICE.TRCODE=2 THEN 'Perakende Satış İade Faturası'
WHEN INVOICE.TRCODE=3 THEN 'Toptan Satış İade Faturası'
WHEN INVOICE.TRCODE=4 THEN 'Alınan Hizmet Faturası'
WHEN INVOICE.TRCODE=5 THEN 'Alınan Proforma Faturası'
WHEN INVOICE.TRCODE=6 THEN 'Satınalma İade Faturası'
WHEN INVOICE.TRCODE=7 THEN 'Perakende Satış Faturası'
WHEN INVOICE.TRCODE=8 THEN 'Toptan Satış Faturası'
WHEN INVOICE.TRCODE=9 THEN 'Verilen Hizmet Faturası'
WHEN INVOICE.TRCODE=10 THEN 'Verilen Proforma Faturası'
WHEN INVOICE.TRCODE=12 THEN 'Alınan Vade Farkı Faturası'
WHEN INVOICE.TRCODE=13 THEN 'Satınalma Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=14 THEN 'Satış Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=26 THEN 'Müstahsil Makbuzu'
END
) as TXTFATURATIPI,
INVOICE.FICHENO TXTFATURANO,
INVOICE.DATE_ as TRHFATURATARIHI,
INVOICE.DOCODE AS TXTBELGENO,
INVOICE.SPECODE AS TXTOZELKOD,
CLCARD.CODE as LNGMUSTERIKOD,
CLCARD.DEFINITION_ as LNGMUSTERIAD,
(SELECT P.CODE FROM LG_520_PAYPLANS P WHERE P.LOGICALREF=INVOICE.PAYDEFREF) TXTODEMELER,
INVOICE.TRADINGGRP TXTTICARIISLEMGRUBU,
(SELECT S.CODE+'-'+S.DEFINITION_ FROM LG_SLSMAN S WHERE S.FIRMNR=520 AND S.LOGICALREF= INVOICE.SALESMANREF) TXTSATISELEMANI,
INVOICE.CYPHCODE TXTYETKIKODU,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIDIV D WHERE D.FIRMNR=520 AND D.NR=INVOICE.BRANCH) TXTISYERI,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIDEPT D WHERE D.FIRMNR=520 AND D.NR=INVOICE.DEPARTMENT) TXTBOLUM,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIFACTORY D WHERE D.FIRMNR=520 AND D.NR=INVOICE.FACTORYNR) TXTFABRIKA,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIWHOUSE D WHERE D.FIRMNR=520 AND D.NR=INVOICE.SOURCEINDEX) TXTAMBAR,
(SELECT D.CODE+'-'+ D.NAME FROM LG_520_PROJECT D WHERE D.LOGICALREF=INVOICE.PROJECTREF) TXTPROJEKODU,
RTRIM(INVOICE.GENEXP1+' '+INVOICE.GENEXP2+' '+INVOICE.GENEXP3+' '+INVOICE.GENEXP4) AS TXTACIKLAMAFIS,
INVOICE.TOTALEXPENSES DBLYPBTOPLAMMASRAF,
INVOICE.TOTALDISCOUNTS DBLYPBTOPLAMINDIRIM,
INVOICE.NETTOTAL-INVOICE.TOTALVAT DBLYPBTOPLAM,
INVOICE.TOTALEXADDTAX DBLYPBTOPLAMEKVERGI,
INVOICE.TOTALVAT DBLYPBTOPLAMKDV,
INVOICE.NETTOTAL DBLYPBTOPLAMNET,
ROUND(INVOICE.TOTALEXPENSES/NULLIF(INVOICE.TRRATE,0),2) DBLIDTOPLAMMASRAF,
ROUND(INVOICE.TOTALDISCOUNTS/NULLIF(INVOICE.TRRATE,0),2) DBLIDTOPLAMINDIRIM,
ROUND((INVOICE.NETTOTAL-INVOICE.TOTALVAT)/NULLIF(INVOICE.TRRATE,0),2) DBLIDTOPLAM,
ROUND(INVOICE.TOTALEXADDTAX/NULLIF(INVOICE.TRRATE,0),2) DBLIDTOPLAMEKVERGI,
ROUND(INVOICE.TOTALVAT/NULLIF(INVOICE.TRRATE,0),2) DBLIDTOPLAMKDV,
ROUND(INVOICE.NETTOTAL/NULLIF(INVOICE.TRRATE,0),2) DBLIDTOPLAMNET,
CASE CLCARD.CCURRENCY WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END TXTCARIDOVIZ,
CASE INVOICE.TRCURR WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END TXTISLEMDOVIZ,
CASE LINETYPE WHEN 0 THEN 'MALZEME' WHEN 1 THEN 'PROMOSYON' WHEN 2 THEN 'INDIRIM' WHEN 3 THEN 'MASRAF' WHEN 4 THEN 'HIZMET' ELSE '' END TXTTUR,
CASE WHEN STLINE.LINETYPE=4 THEN SRV.CODE ELSE INV.CODE END AS TXTKOD,
CASE WHEN STLINE.LINETYPE=4 THEN SRV.DEFINITION_ ELSE INV.NAME END TXTACIKLAMA,
COALESCE(
CASE STLINE.UINFO1
WHEN 0 THEN STLINE.AMOUNT
ELSE STLINE.AMOUNT*STLINE.UINFO2/STLINE.UINFO1
END, STLINE.AMOUNT/1000) DBLMIKTAR,
CASE WHEN LINETYPE=4 THEN UNTS.CODE ELSE UNT.CODE END TXTBIRIM,
ROUND(STLINE.PRICE,2) DBLBIRIMFIYAT,
CASE STLINE.PRCURR WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END TXTDOVIZTURU,
ROUND(STLINE.DISCPER,2) DBLYUZDE,
ROUND(STLINE.VAT,2) DBLKDV,
ROUND(STLINE.TOTAL,2) DBLTUTAR,
ROUND(STLINE.VATAMNT,2) DBLKDVTUTARI,
ROUND(STLINE.VATMATRAH,2) DBLNETTUTAR
FROM LG_520_01_INVOICE AS INVOICE WITH (NOLOCK)
LEFT JOIN LG_520_01_STLINE AS STLINE ON INVOICE.LOGICALREF = STLINE.INVOICEREF
LEFT JOIN LG_520_ITEMS INV ON INV.LOGICALREF=STLINE.STOCKREF
LEFT JOIN LG_520_SRVCARD SRV ON SRV.LOGICALREF=STLINE.STOCKREF
LEFT JOIN LG_520_CLCARD CLCARD ON CLCARD.LOGICALREF=INVOICE.CLIENTREF
LEFT JOIN LG_520_EMUHACC EMUH ON EMUH.LOGICALREF=STLINE.ACCOUNTREF
LEFT JOIN LG_520_UNITSETL UNT ON UNT.UNITSETREF=INV.UNITSETREF AND LINENR=1
LEFT JOIN LG_520_UNITSETL UNTS ON UNTS.UNITSETREF=SRV.UNITSETREF AND UNTS.LINENR=1 WHERE(INVOICE.TRCODE IN(1/*,4,5,6,12,13,26,2,3,7,8,9,10,14*/))) AS T
Category: LOGO ERP
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
LOGO LENGINE3.EXE ACCESS VIOLATION
Bu hata karşıma çıkdı kurulun logo versionu newui olduğu için bu hatayı veriyordu newuisiz kurunca vermedi
LOGO SQL SERVER
TRIGGERLE İŞLEMLERDE ÖZELLİKLE UPDATE TRIGGERINDE SET NOCOUNT ON YAP
LOGO ÜRÜN BARKOD VE SATIŞ FİYATI LİSTESİ SQL
select
ISNULL(B.BARCODE,'') ProductCode,
ISNULL(I.NAME,'') ProductName,
ISNULL((SELECT TOP 1 ROUND(PRICE,2) FROM LG_320_PRCLIST P WITH(NOLOCK) WHERE CARDREF = I.LOGICALREF AND ACTIVE = 0 AND PTYPE = 2 AND CLIENTCODE = '' order by P.LOGICALREF DESC ),0) Price
FROM dbo.LG_320_ITEMS I WITH(NOLOCK) LEFT JOIN LG_320_UNITBARCODE B ON B.ITEMREF=I.LOGICALREF WHERE I.CODE<>'ÿ'AND ISNULL(B.BARCODE,'') <> ''
LOGO SQL MALZEME EKSTRE
/*TEST =
select * from dbo.SELECT_MALZEMEEXTRE_201('01/01/2017',getdate(),'01-YK-0001')*/
/****** Object: UserDefinedFunction [dbo].[SELECT_MALZEMEEXTRE_201] Script Date: 18.8.2020 09:09:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SELECT_MALZEMEEXTRE_201]
(
@BAT DATETIME,
@BIT DATETIME,
@MALZEMEKODU VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
SELECT
X.AMBAR,
x.[Stfıche_Referans] ,
x.[Stlıne_Referans] ,
x.[Fiş No],
x.[Fiş Türü],
x.[Belge No],
x.[Stfıche Tarıh],
x.[Stlıne Tarıh],
x.[Ftıme],
x.[Stok Kodu],
x.[Stok Adı],
x.[Bırım],
x.[Giriş Çıkış],
x.[Miktar],
X.STFICHELNNO,
x.Kalan,
x.[Tutar]
FROM (
SELECT
(SELECT NAME FROM L_CAPIWHOUSE CAPIWHOUSE WHERE CAPIWHOUSE.NR = STLINE.SOURCEINDEX AND CAPIWHOUSE.FIRMNR = 201 ) AMBAR,
STFICHE.LOGICALREF [STFICHE_REFERANS],
STLINE.LOGICALREF [STLINE_REFERANS],
[Fiş No]=STFICHE.FICHENO,
[Fiş Türü]=CASE STFICHE.TRCODE
WHEN 1 then 'Satınalma İrsaliyesi'
WHEN 2 then 'Perakende Satış İade İrsaliyesi'
WHEN 3 then 'Toptan Satış İade İrsaliyesi'
WHEN 4 then 'Konsinye Çıkış İade İrsaliyesi'
WHEN 5 then 'Konsinye Giriş İrsaliyesi'
WHEN 6 then 'Satınalma İade İrsaliyesi'
WHEN 7 then 'Perakende Satış İrsaliyesi'
WHEN 8 then 'Toptan Satış İrsaliyesi'
WHEN 9 then 'Konsinye Çıkış İrsaliyesi'
WHEN 10 then 'Konsinye Giriş İade İrsaliyesi'
WHEN 11 then 'Fire Fişi'
WHEN 12 then 'Sarf Fişi'
WHEN 13 then 'Üretimden Giriş Fişi'
WHEN 14 then 'Devir Fişi'
WHEN 25 then 'Ambar Fişi'
WHEN 26 then 'Muhtahsil İrsaliyesi'
WHEN 50 then 'Sayım Fazlası Fişi'
WHEN 51 then 'Sayım Eksiği Fişi' ELSE '' END,
[Belge No]=STFICHE.DOCODE,
[Stfıche Tarıh]=STFICHE.DATE_,
[Stlıne Tarıh]=STLINE.DATE_,
[Ftıme]=STLINE.FTIME,
[Stok Kodu]=ITEMS.CODE,
[Stok Adı]=ITEMS.NAME,
[Bırım]=BIRIM.CODE,
[Giriş Çıkış]= CASE WHEN STLINE.IOCODE IN(1,2) THEN 'Giriş' when STLINE.IOCODE IN(3,4) THEN 'Çıkış' else '' end,
[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),
STLINE.STFICHELNNO,
0.00 KALAN,
[Tutar]=(CASE WHEN STLINE.IOCODE IN(1,2) THEN 1 ELSE -1 end) * STLINE.VATMATRAH
FROM LG_201_01_STFICHE STFICHE WITH (NOLOCK)
LEFT OUTER JOIN LG_201_01_STLINE STLINE WITH (NOLOCK) ON STFICHE.LOGICALREF=STLINE.STFICHEREF
LEFT OUTER JOIN LG_201_ITEMS ITEMS WITH (NOLOCK) ON STLINE.STOCKREF =ITEMS.LOGICALREF
LEFT OUTER JOIN LG_201_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 ITEMS.CODE=@MALZEMEKODU
AND STLINE.LPRODSTAT=0
AND (STLINE.DATE_>=@BAT AND STLINE.DATE_<=@BIT )
)
AS X
GO
LOGO SQL MASRAF MERKEZİ MUHASEBE FİŞ TOPLAM KONTROL
SELECT
E.TRCODE "FIŞ TÜRÜ",
E.FICHENO "FIŞ NO",
L.DATE_ "FIŞ TARIHI",
L.ACCOUNTCODE "MUH_KODU",
CASE
WHEN L.SIGN =0 THEN "BORÇ" ELSE "ALACAK"
END AS [BORÇ/ALACAK TÜRÜ] ,
L.DEBIT "HAREKET_BORÇ",
MASRAF_MERKEZI_TOPLAM.BORÇ "MASRAF MERK_BORÇ",
L.CREDIT "HAREKET_ALACAK",
MASRAF_MERKEZI_TOPLAM.ALACAK "MASRAF MERK_ALACAK"
FROM
LG_101_01_EMFICHE E INNER JOIN
LG_101_01_EMFLINE L ON E.LOGICALREF = L.ACCFICHEREF
LEFT OUTER JOIN
(
SELECT
M.PREVLINEREF,
M.TRCODE,
M.TSIGN,
SUM((2 - M.TSIGN - 1) * M.TRNET) AS BORÇ,
SUM((M.TSIGN - 1 + 1) * M.TRNET) AS ALACAK
FROM
LG_101_01_ACCDISTDETLN M INNER JOIN
LG_101_01_EMFLINE L ON M.PREVLINEREF = L.LOGICALREF
GROUP BY
M.PREVLINEREF, M.TRCODE, M.TSIGN
)MASRAF_MERKEZI_TOPLAM ON L.LOGICALREF = MASRAF_MERKEZI_TOPLAM.PREVLINEREF
WHERE
((MASRAF_MERKEZI_TOPLAM.BORÇ <> L.DEBIT) OR (MASRAF_MERKEZI_TOPLAM.ALACAK <> L.CREDIT))
LOGO LOD- SATIŞ FATURA SATIRINA İKİNCİ MİKTAR GETİRME
Events:
Modules
Table:
Events:
Sub FormShow()
Call GetData()
End Sub
Sub BeforeSave(proceed as Integer)
Call SetLineArray()
End Sub
Sub AfterSave()
AppTables[14].GetIntFieldR(RecHandle,”LOGICALREF”,gSETLOGREF)
Call SetData(gSETLOGREF)
End Sub
Modules:
Dim LineDataArr1(200) as string
Sub GetData()
AppTables[14].GetIntFieldR(RecHandle,”LOGICALREF”,iMasrefRef)
MaxGridLine=MGridLines("InvoiceGrid")
for i=0 to MaxGridLine-1
GetGridRowHandle("InvoiceGrid",i,recH)
AppTables[9].GetIntFieldR(recH,"LOGICALREF",intLineRef)
CustTables[62].Search(2,2,intLineRef)
if not (DBErr <>0) then
CustTables[62].GetFltField("MIKTAR2",fMIKTAR2)
SetCellFloat("InvoiceGrid",i,10072,fMIKTAR2)
end if
next i
End Sub
Sub SetData(intLogref as Long)
i=0
CreateQuery(Qry)
tblName = Application.GetTableName(9, Application.CompanyId, Application.FiscPerdId)
Qry.AddTable(“A”, tblName)
Qry.AddSelField(“A”, “LOGICALREF,”)
Qry.AddSelField(“A”, “INVOICELNNO”)
Str(intLogref, LogRefTxt)
MyWhereCond = “INVOICEREF = ” + LogRefTxt
Qry.AddWhereCond(MyWhereCond,1)
Qry.Execute()
if Qry.Ready = 1 then
res = Qry.First()
Do while res = 1
Qry.GetFieldValue(1, 1, MyLineRef)
Qry.GetFieldValue(2, 1, MyLineNo)
if MyLineRef<>0 then
CustTables[62].Search(2,2,MyLineRef)
if (DBErr <>0) then
CustTables[62].NewRecord()
CustTables[62].SetIntField("PARLOGREF",MyLineRef)
CustTables[62].SetIntField("INVFICHEREF",intLogref)
CustTables[62].SetFltField("MIKTAR2",LineDataArr1[MyLineNo - 1])
else
CustTables[62].SetFltField("MIKTAR2",LineDataArr1[MyLineNo - 1])
end if
CustTables[62].UpdateRecord()
end if
res = Qry.Next()
Loop
end if
End Sub
Sub SetLineArray()
MaxGridLine=MGridLines(“InvoiceGrid”)
i=0
for i=0 to MaxGridLine-1
GetCellFloat(“InvoiceGrid”,i,10072,LineDataArr1[i])
next i
End Sub
Blog of Sem 