Category: LOGO ERP
Borç Takip Kullanıcı Tanımlı Rapor Döviz
Borç takip kullanıcı tanımlı rapora işlem döviz cinsini getirmek için kullanıcı tanımlı alan metin olarak eklenir:
IF(R9.trCurr=1,”USD”,(IF(R9.trCurr=17,”GBP”,(IF(R9.trCurr=20,”EUR”,”TL”)))))
DATABASEDE ALANA GÖRE ARAMAK
Aşağıdaki sorgu databasede isdenilen tablo alanının aramasını yapar ve hangi tablolarda o alanvarsa gösterir(aşağıdaki örnekte SALESMANREF alanı hangi tablolarda var içindir).
USE [LOGO]
SELECT
sys.columns.name AS ColumnName,
tables.name AS TableName
FROM
sys.columns
JOIN sys.tables ON
sys.columns.object_id = tables.object_id
WHERE
sys.columns.name = 'SALESMANREF'
MOBILSALES SQL PENETRASYON RAPORU
select
(CASE WHEN P.PTYPE='0' THEN 'MIKTAR' ELSE 'VAR/YOK' END) PENETRATIONTYPE,
U.CODE+'-'+U.FNAME SALESMAN,
PNTHEADER.DATE,
CLCARD.CODE+'-'+CLCARD.DEFINITION_ CLIENT,
ISNULL(P.PCODE,'') PCODE,
ISNULL(P.PDEFINITION,'') PDEFINITION,
ISNULL(WD.ITEMNAME,'') ITEMNAME ,
WU.PRICE,
WU.AMOUNT,
WU.NOTE
from WOR_PNTHEADER PNTHEADER
LEFT JOIN WOR_PNTTRANS WU ON WU.PNTHEADER_REF=PNTHEADER.PNT_GUID
LEFT JOIN WOR_PENETRATIONDETAIL WD ON WU.PNTDTL_ID=WD.LOGICALREF
LEFT JOIN WOR_USERS U ON U.USERID=PNTHEADER.SALESMANREF
LEFT JOIN WOR_PENETRATION P ON P.LOGICALREF=PNTHEADER.PNT_ID
LEFT JOIN LG_219_CLCARD CLCARD ON CLCARD.LOGICALREF=PNTHEADER.CLIENTREF
/*where PNTHEADER.LOGICALREF=9*/
ORDER BY PNTHEADER.DATE, WD.SEQUENCE
LOGO CARİ HESAP HAREKET DÖKÜMÜ EKLEME TARİHİ
Talep: Cari Hesap Hareket Dökümü -> Kullanıcı tanımlı rapor kullanarak rapor filtresine created date(clfiche) tarih aralığı filtresi eklemek
Not: Logonun tanımlı alanlar listesinde clfline createddate yok dolayısıyla sql infodan created date getirildi.
VAL(_SQLINFO(“DAY(CAPIBLOCK_CREADEDDATE)”,”LG_001_01_CLFLINE”,”LOGICALREF='”+STR(R1.logicalRef)+”‘”))
S_AY =
VAL(_SQLINFO(“MONTH(CAPIBLOCK_CREADEDDATE)”,”LG_001_01_CLFLINE”,”LOGICALREF='”+STR(R1.logicalRef)+”‘”))
S_YIL =
VAL(_SQLINFO(“YEAR(CAPIBLOCK_CREADEDDATE)”,”LG_001_01_CLFLINE”,”LOGICALREF='”+STR(R1.logicalRef)+”‘”))
S_TARIH=
DATE([S_GUN],[S_AY],[S_YIL])
LOGO SAYISAL ZAMAN İŞLEMLERİ
C# Fonksiyonu
private string LogoTimetoSystemTime(double GELENDEGER)
{
string RESULT = "";
int HOUR = 0;
if (GELENDEGER > 0)
{
HOUR = Convert.ToInt32(Math.Round(GELENDEGER / 16777216, 2));
}
int MINUTE = 0;
if (GELENDEGER > 0)
{
MINUTE = Convert.ToInt32(Math.Round((GELENDEGER - HOUR * 16777216) / 65536, 2));
}
int SECOND = 0;
if (GELENDEGER > 0)
{
SECOND = Convert.ToInt32(Math.Round((GELENDEGER - HOUR * 16777216 - MINUTE * 65536) / 256, 2));
}
RESULT = HOUR.ToString() + ":" + (MINUTE.ToString().Length == 0 ? 0 + MINUTE.ToString() : MINUTE.ToString()) + ":" + (SECOND.ToString().Length == 0 ? 0 + SECOND.ToString() : SECOND.ToString());
return RESULT;
}
Sql Fonksiyonu
create FUNCTION [dbo].[fn_LogoTimetoSystemTime] (@GELENDEGER INT)
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @SAAT VARCHAR(2), @DAKIKA VARCHAR(2), @SANIYE VARCHAR(2), @SONUC VARCHAR(8)
SELECT
@SAAT=RTRIM(CONVERT(char(20), ROUND(@GELENDEGER / 16777216, 2))),
@DAKIKA=RTRIM(CONVERT(char(20), ROUND((@GELENDEGER - ROUND(@GELENDEGER / 16777216, 2) * 16777216)/ 65536, 2))),
@SANIYE=RTRIM(CONVERT(char(20), ROUND((@GELENDEGER - ROUND(@GELENDEGER / 16777216, 2) * 16777216 - ROUND((@GELENDEGER - ROUND(@GELENDEGER / 16777216, 2) * 16777216) / 65536, 2) * 65536) / 256, 2)))
SET @SAAT=CASE WHEN LEN(@SAAT)=1 THEN '0'+@SAAT ELSE @SAAT END
SET @DAKIKA=CASE WHEN LEN(@DAKIKA)=1 THEN '0'+@DAKIKA ELSE @DAKIKA END
SET @SANIYE=CASE WHEN LEN(@SANIYE)=1 THEN '0'+@SANIYE ELSE @SANIYE END
SET @SONUC= @SAAT + ':' +@DAKIKA + ':' + @SANIYE
RETURN @SONUC
END
CREATE FUNCTION [dbo].[LG_TIMETOINT](@HH INT,@MM INT,@SS INT)
RETURNS INT
AS
BEGIN
DECLARE @TIME INT
SELECT @TIME = (@HH*65536*256+@MM*65536+@SS*256)
RETURN(@TIME)
END
GO
CREATE FUNCTION [dbo].[LG_INTTOTIME](@TIME INT)
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @HH INT
DECLARE @MM INT
DECLARE @SS INT
SELECT @HH = @TIME/65536/256
SELECT @MM = (@TIME/65536) - (@HH*256)
SELECT @SS = (@TIME/256) - (@HH*65536) - (@MM*256)
RETURN (RIGHT('0'+CAST(@HH AS VARCHAR(2)),2)+':'+RIGHT('0'+CAST(@MM AS VARCHAR(2)),2)+':'+RIGHT('0'+CAST(@SS AS VARCHAR(2)),2))
END
GO
CREATE FUNCTION [dbo].[LG_ADDTIME](@TIME INT,@ADD INT)
RETURNS INT
AS
BEGIN
DECLARE @HH INT
DECLARE @MM INT
DECLARE @SS INT
SELECT @HH = @TIME/65536/256
SELECT @MM = (@TIME/65536) - (@HH*256)
SELECT @SS = (@TIME/256) - (@HH*65536) - (@MM*256)
SELECT @SS = @SS + @ADD
IF @SS > 59
BEGIN
SELECT @MM = @MM + 1
SELECT @SS = @SS - 60
END
IF @MM > 59
BEGIN
SELECT @HH = @HH + 1
SELECT @MM = @MM - 60
END
RETURN (@HH*65536*256+@MM*65536+@SS*256)
END
GO
CREATE FUNCTION [dbo].[fn_LogoDatetoSystemDate] (@DEGER INT)
RETURNS datetime
AS
BEGIN
DECLARE @GUN VARCHAR(2), @AY VARCHAR(2), @YIL VARCHAR(4)
DECLARE @SONUC datetime
SELECT
@GUN=CAST((CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)-(CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)/256*256)) AS VARCHAR(3))
SELECT
@AY=CAST(((CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)-(65536*(CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)/65536)))-(CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)-(CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)/256*256)))/256 AS VARCHAR(3))
SELECT
@YIL=CAST((CONVERT(INT,CONVERT(BINARY,@DEGER,2),0)/65536) AS VARCHAR(6))
SET @GUN=CASE WHEN LEN(@GUN)<2 THEN '0'+@GUN ELSE @GUN END
SET @AY=CASE WHEN LEN(@AY)<2 THEN '0'+@AY ELSE @AY END
SONUC:
IF @DEGER<>0
BEGIN
SET @SONUC=CONVERT(DATETIME, @YIL + '-' + @AY + '-' + @GUN + ' 00:00:00', 102)
END
IF @DEGER=0
SET @SONUC= NULL
RETURN @SONUC
END
GO
CREATE FUNCTION [dbo].[fn_SystemDateToLogoDate] (@date DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @GUN INT, @AY INT, @YIL INT
DECLARE @SONUC INT
SET @YIL = YEAR(@date)
SET @AY = MONTH(@date)
SET @GUN = DAY(@date)
SET @SONUC = (@YIL * 65536) + (@AY * 256) + @GUN
IF @SONUC = 0
SET @SONUC = NULL
RETURN @SONUC
END
GO
LOGO SATIŞ TEKLİF YÖNETİMİ TABLOLARININ KENDİ KENDİNE SİLİNMESİNE KARŞI GEÇİCİ ÇÖZÜM
Sorun: Ayda 2-3 kere satış teklif yönetimi modülünün tabloları gece saat 00:00 gibi drop-create yapılmakta.
Job, trigger ve servisler kontrol edildi ilgili işlemi tetikleyen bir şey bulunamadı(tiger3e ver:2.76).
Arge devamlılığı için geçici çözüm olarak prosedur->job yaratıldı(yarım saatte bir) .
Teklif modülü tablolarında eğer data var ise yardımcı tabloya alınacak, eğer data yok ise(drop create olunca boşaldıysa) yardımcı tablolardan gerçek tablolara atacak.
CREATE PROCEDURE YT_KONTROL_SATISTEKLIFYONETIMI_TABLOLARI AS
IF EXISTS(SELECT 1 FROM [MYDB].[dbo].[LG_SLSACTIV] WITH(NOLOCK) WHERE LOGICALREF>0)
BEGIN
IF OBJECT_ID(N'[MYDB].[dbo].[YT_RESTORE_LG_SLSACTIV]', N'U') IS NOT NULL DROP TABLE [MYDB].[dbo].[YT_RESTORE_LG_SLSACTIV];
SELECT * INTO [MYDB].[dbo].[YT_RESTORE_LG_SLSACTIV] FROM [MYDB].[dbo].[LG_SLSACTIV]
END
ELSE
BEGIN
SET IDENTITY_INSERT [MYDB].[dbo].[LG_SLSACTIV] ON
INSERT INTO [MYDB].[dbo].[LG_SLSACTIV]
(LOGICALREF,[DATE_]
,[TIME_]
,[ACTTYPE]
,[OPPORREF]
,[SALESMANREF]
,[CSTVNDREF]
,[ACTORDER]
,[DESCRIPTION]
,[PRIORITY]
,[APPROVAL]
,[DUEDATE]
,[DUETIME]
,[SENDVIA]
,[SENDREF]
,[SPECODE]
,[CYPHCODE]
,[CAPIBLOCK_CREATEDBY]
,[CAPIBLOCK_CREADEDDATE]
,[CAPIBLOCK_CREATEDHOUR]
,[CAPIBLOCK_CREATEDMIN]
,[CAPIBLOCK_CREATEDSEC]
,[CAPIBLOCK_MODIFIEDBY]
,[CAPIBLOCK_MODIFIEDDATE]
,[CAPIBLOCK_MODIFIEDHOUR]
,[CAPIBLOCK_MODIFIEDMIN]
,[CAPIBLOCK_MODIFIEDSEC]
,[SITEID]
,[RECSTATUS]
,[ORGLOGICREF]
,[LASTREVISION]
,[TEXTINC]
,[DESCRIPTION2]
,[GUID])
SELECT LOGICALREF,[DATE_]
,[TIME_]
,[ACTTYPE]
,[OPPORREF]
,[SALESMANREF]
,[CSTVNDREF]
,[ACTORDER]
,[DESCRIPTION]
,[PRIORITY]
,[APPROVAL]
,[DUEDATE]
,[DUETIME]
,[SENDVIA]
,[SENDREF]
,[SPECODE]
,[CYPHCODE]
,[CAPIBLOCK_CREATEDBY]
,[CAPIBLOCK_CREADEDDATE]
,[CAPIBLOCK_CREATEDHOUR]
,[CAPIBLOCK_CREATEDMIN]
,[CAPIBLOCK_CREATEDSEC]
,[CAPIBLOCK_MODIFIEDBY]
,[CAPIBLOCK_MODIFIEDDATE]
,[CAPIBLOCK_MODIFIEDHOUR]
,[CAPIBLOCK_MODIFIEDMIN]
,[CAPIBLOCK_MODIFIEDSEC]
,[SITEID]
,[RECSTATUS]
,[ORGLOGICREF]
,[LASTREVISION]
,[TEXTINC]
,[DESCRIPTION2]
,[GUID] FROM [MYDB].[dbo].[YT_RESTORE_LG_SLSACTIV]
SET IDENTITY_INSERT [MYDB].[dbo].[LG_SLSACTIV] OFF
END
IF EXISTS(SELECT 1 FROM [MYDB].[dbo].[LG_SATI] WITH(NOLOCK) WHERE LOGICALREF>0)
BEGIN
IF OBJECT_ID(N'[MYDB].[dbo].[YT_RESTORE_LG_SATI]', N'U') IS NOT NULL DROP TABLE [MYDB].[dbo].[YT_RESTORE_LG_SATI];
SELECT * INTO [MYDB].[dbo].[YT_RESTORE_LG_SATI] FROM [MYDB].[dbo].[LG_SATI]
END
ELSE
BEGIN
SET IDENTITY_INSERT [MYDB].[dbo].[LG_SATI] ON
INSERT INTO [MYDB].[dbo].[LG_SATI]
(LOGICALREF,[USERID]
,[CUSTOM]
,[ITEMTYP]
,[ITEMEXP]
,[PARENTREF]
,[HASCHILD]
,[FILTERREF])
SELECT LOGICALREF,[USERID]
,[CUSTOM]
,[ITEMTYP]
,[ITEMEXP]
,[PARENTREF]
,[HASCHILD]
,[FILTERREF] FROM [MYDB].[dbo].[YT_RESTORE_LG_SATI]
SET IDENTITY_INSERT [MYDB].[dbo].[LG_SATI] OFF
END
IF EXISTS(SELECT 1 FROM [MYDB].[dbo].[LG_CVARPASG] WITH(NOLOCK) WHERE LOGICALREF>0)
BEGIN
IF OBJECT_ID(N'[MYDB].[dbo].[YT_RESTORE_LG_CVARPASG]', N'U') IS NOT NULL DROP TABLE [MYDB].[dbo].[YT_RESTORE_LG_CVARPASG];
SELECT * INTO [MYDB].[dbo].[YT_RESTORE_LG_CVARPASG] FROM [MYDB].[dbo].[LG_CVARPASG]
END
ELSE
BEGIN
SET IDENTITY_INSERT [MYDB].[dbo].[LG_CVARPASG] ON
INSERT INTO [MYDB].[dbo].[LG_CVARPASG]
(LOGICALREF,[CSTVNDREF]
,[ARPREF]
,[FIRMNO]
,[SITEID]
,[RECSTATUS]
,[ORGLOGICREF]
,[DEFAULTFLG])
SELECT LOGICALREF,[CSTVNDREF]
,[ARPREF]
,[FIRMNO]
,[SITEID]
,[RECSTATUS]
,[ORGLOGICREF]
,[DEFAULTFLG] FROM [MYDB].[dbo].[YT_RESTORE_LG_CVARPASG]
SET IDENTITY_INSERT [MYDB].[dbo].[LG_CVARPASG] OFF
END
IF EXISTS(SELECT 1 FROM [MYDB].[dbo].[LG_CSTVND] WITH(NOLOCK) WHERE LOGICALREF>0)
BEGIN
IF OBJECT_ID(N'[MYDB].[dbo].[YT_RESTORE_LG_CSTVND]', N'U') IS NOT NULL DROP TABLE [MYDB].[dbo].[YT_RESTORE_LG_CSTVND];
SELECT * INTO [MYDB].[dbo].[YT_RESTORE_LG_CSTVND] FROM [MYDB].[dbo].[LG_CSTVND]
END
ELSE
BEGIN
SET IDENTITY_INSERT [MYDB].[dbo].[LG_CSTVND] ON
INSERT INTO MYDB.[dbo].[LG_CSTVND]
(LOGICALREF
,[ACTIVE]
,[CARDTYPE]
,[CODE]
,[TITLE]
,[SPECODE]
,[CYPHCODE]
,[ADDR1]
,[ADDR2]
,[CITY]
,[COUNTRY]
,[ORIGIN]
,[POSTCODE]
,[TELNR1]
,[TELNR2]
,[FAXNR]
,[CLANGUAGE]
,[CCURRENCY]
,[WEBURL]
,[CUSTCAT]
,[GROUPORG]
,[PARENTORG]
,[TEXTINC]
,[SITEID]
,[ORGLOGICREF]
,[CAPIBLOCK_CREATEDBY]
,[CAPIBLOCK_CREADEDDATE]
,[CAPIBLOCK_CREATEDHOUR]
,[CAPIBLOCK_CREATEDMIN]
,[CAPIBLOCK_CREATEDSEC]
,[CAPIBLOCK_MODIFIEDBY]
,[CAPIBLOCK_MODIFIEDDATE]
,[CAPIBLOCK_MODIFIEDHOUR]
,[CAPIBLOCK_MODIFIEDMIN]
,[CAPIBLOCK_MODIFIEDSEC]
,[PRIMARYCONTREF]
,[COUNTRYCODE]
,[CITYCODE]
,[TOWNCODE]
,[TOWN]
,[DISTRICTCODE]
,[DISTRICT]
,[RECSTATUS]
,[RIVALFIRM]
,[TELCODES1]
,[TELCODES2]
,[FAXCODE]
,[GUID])
SELECT LOGICALREF,
[ACTIVE]
,[CARDTYPE]
,[CODE]
,[TITLE]
,[SPECODE]
,[CYPHCODE]
,[ADDR1]
,[ADDR2]
,[CITY]
,[COUNTRY]
,[ORIGIN]
,[POSTCODE]
,[TELNR1]
,[TELNR2]
,[FAXNR]
,[CLANGUAGE]
,[CCURRENCY]
,[WEBURL]
,[CUSTCAT]
,[GROUPORG]
,[PARENTORG]
,[TEXTINC]
,[SITEID]
,[ORGLOGICREF]
,[CAPIBLOCK_CREATEDBY]
,[CAPIBLOCK_CREADEDDATE]
,[CAPIBLOCK_CREATEDHOUR]
,[CAPIBLOCK_CREATEDMIN]
,[CAPIBLOCK_CREATEDSEC]
,[CAPIBLOCK_MODIFIEDBY]
,[CAPIBLOCK_MODIFIEDDATE]
,[CAPIBLOCK_MODIFIEDHOUR]
,[CAPIBLOCK_MODIFIEDMIN]
,[CAPIBLOCK_MODIFIEDSEC]
,[PRIMARYCONTREF]
,[COUNTRYCODE]
,[CITYCODE]
,[TOWNCODE]
,[TOWN]
,[DISTRICTCODE]
,[DISTRICT]
,[RECSTATUS]
,[RIVALFIRM]
,[TELCODES1]
,[TELCODES2]
,[FAXCODE]
,[GUID] FROM [MYDB].[dbo].[YT_RESTORE_LG_CSTVND]
SET IDENTITY_INSERT [MYDB].[dbo].[LG_CSTVND] OFF
END
/* EXEC YT_KONTROL_SATISTEKLIFYONETIMI_TABLOLARI */
Logo Erp / Sql Malzeme sınıfı bağlantıları çalışması
kırılım yapısı(3 alt kırılım-FERRARI) : Tüm Malzemeler -> Kırılım1-> Kırılım2-> Kırılım3 -> Kırılım3 Malzemeleri
Kampanya yapısı(2 alt kırılım-KMP) : Tüm Malzemeler -> Kırılım1-> Kırılım2 -> Kırılım2 Malzemeleri
create view [RM_FERRARI_MALZEME_SINIFLARI]
as
select top 100 percent [Type], [Level1Ref] = case when [Level1Ref] = 1 then Level2Ref else Level1Ref end, [Level1] = case when [Level1Ref] = 1 then Level2 else Level1 end, [Level2Ref] = case when [Level1Ref] = 1 then Level3Ref else Level2Ref end, [Level2] = case when [Level1Ref] = 1 then Level3 else Level2 end, [Level3Ref] = case when [Level1Ref] = 1 then 0 else [Level3Ref] end, [Level3] = case when [Level1Ref] = 1 then '' else [Level3] end, [ItemRef], [Code], [Name], [Active]
from (
select [Type] = case when ITMSC.CARDTYPE = 20 then 'Genel Malzeme Sınıfı' else 'Malzeme' end, [Level1Ref] = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
)
), [Level1] = (
select I2.CODE + '-' + I2.name
from LG_101_ITEMS I2
where I2.LOGICALREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
)
)
), [Level2Ref] = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
), [Level2] = (
select I2.CODE + '-' + I2.name
from LG_101_ITEMS I2
where I2.LOGICALREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
)
), [Level3Ref] = LGMAIN.PARENTREF, [Level3] = (
select I2.CODE + '-' + I2.name
from LG_101_ITEMS I2
where I2.LOGICALREF = LGMAIN.PARENTREF
), [ItemRef] = ITMSC.LOGICALREF, [Code] = ITMSC.CODE, [Name] = ITMSC.name, [Active] = ITMSC.ACTIVE
from LG_101_ITMCLSAS LGMAIN with (nolock)
left outer join LG_101_ITEMS ITMSC with (nolock) on (LGMAIN.CHILDREF = ITMSC.LOGICALREF)
) as T
where [Level1] = 'MS01-FERRARI' or Level3 = 'MS01.16-KMP'
order by Level1, Level2, Level3
LOGO RAPOR ÜRETİCİ SELECTSİZ TARİH FİLTRESİ
NOT:
SELECTE EKLENDİ: CONVERT(SMALLDATETIME,’01.01.2000′,103) TARIH
WHERE KISMINA EKLENDİ: BETWEEN {FLTDATEBEG(1)} AND {FLTDATEEND(1)}
--RAPOR SORGUSU:
SELECT TOP 200
CONVERT(SMALLDATETIME,'01.01.2000',103) TARIH,
CARI_KODU,CARI_ADI,MALZEME_GRUP,MALZEME_ALTGRUP,MALZEME_KODU,MALZEME_AÇIKLAMASI, SUM(MIKTAR) AS MIKTAR,
SUM(TUTAR) AS TUTAR,
'' KARLILIK,
'' SATIS_FIYATI
FROM
(
SELECT DATE_ AS TARIH,
(SELECT SPECODE2 FROM LG_001_ITEMS WHERE LOGICALREF=STOCKREF) AS CARI_KODU,
ISNULL((SELECT DEFINITION_ FROM LG_001_CLCARD WHERE CODE=(SELECT SPECODE2 FROM LG_001_ITEMS WHERE LOGICALREF=STOCKREF)),'') AS CARI_ADI,
ISNULL((SELECT SPECODE+' - '+DEFINITION_ FROM LG_001_SPECODES WHERE CODETYPE=4 AND SPECODE=(SELECT STGRPCODE FROM LG_001_ITEMS WHERE LOGICALREF=STOCKREF)),'') AS MALZEME_GRUP,
ISNULL((SELECT SPECODE+' - '+DEFINITION_ FROM LG_001_SPECODES WHERE CODETYPE=1 AND SPECODETYPE=1 AND SPETYP1=1 AND SPECODE=(SELECT SPECODE FROM LG_001_ITEMS WHERE LOGICALREF=STOCKREF)),'') AS MALZEME_ALTGRUP,
(SELECT CODE FROM LG_001_ITEMS WHERE LOGICALREF=STOCKREF) AS MALZEME_KODU,
(SELECT NAME FROM LG_001_ITEMS WHERE LOGICALREF=STOCKREF) AS MALZEME_AÇIKLAMASI,
AMOUNT AS MIKTAR,
VATMATRAH AS TUTAR
FROM LG_001_01_STLINE WHERE CANCELLED=0 AND LINETYPE=0 AND TRCODE=7 AND DATE_ BETWEEN {FLTDATEBEG(1)} AND {FLTDATEEND(1)}
) AS RPR
GROUP BY CARI_KODU,CARI_ADI,MALZEME_GRUP,MALZEME_ALTGRUP,MALZEME_KODU,MALZEME_AÇIKLAMASI
ORDER BY (SUM(MIKTAR)) DESC
Blog of Sem 