Category: Sql
LOGO Banka İşlem Fişi Kesilmiştir Detay
Logoda kk fişi silerken banka işlem fişi kesilmiştir hatası veren fişlerin bağlarını bulmak için :
SELECT * FROM (
SELECT
DISTINCT
Tarih,
CariFisKKFisNo=(SELECT CLFICHE.FICHENO FROM LG_301_01_CLFICHE CLFICHE WHERE CLFICHE.LOGICALREF=CariFisKKFisRef),
CariFisMuhasebeFisNo=(SELECT EMFICHE.FICHENO FROM LG_301_01_EMFICHE EMFICHE WHERE EMFICHE.LOGICALREF=CariFisMuhasebeFisRef),
BankaFisNo,
BankaFisMuhasebeFisNo=(SELECT EMFICHE.FICHENO FROM LG_301_01_EMFICHE EMFICHE WHERE EMFICHE.LOGICALREF=BankaFisMuhasebeFisRef)
FROM (
SELECT
Tarih=BNFICHE.DATE_,
CariFisKKFisRef=BNFLINE.CRCARDFCREF,
CariFisMuhasebeFisRef=(SELECT CLFICHE.ACCFICHEREF FROM LG_301_01_CLFICHE CLFICHE WHERE CLFICHE.LOGICALREF=(CASE WHEN ISNULL(BNFICHE.CRCARDFCREF,'')=0 THEN BNFLINE.CRCARDFCREF ELSE BNFICHE.CRCARDFCREF END)),
BankaFisNo=BNFICHE.FICHENO,
BankaFisMuhasebeFisRef=(SELECT EMFICHE.LOGICALREF FROM LG_301_01_EMFICHE EMFICHE WHERE EMFICHE.LOGICALREF=BNFICHE.ACCFICHEREF)
FROM
LG_301_01_BNFICHE BNFICHE LEFT JOIN LG_301_01_BNFLINE BNFLINE ON BNFLINE.SOURCEFREF=BNFICHE.LOGICALREF
WHERE BNFICHE.TRCODE=1 AND BNFICHE.MODULENR=7
) AS TT
) AS TF WHERE CariFisKKFisNo='BMS.0073921'
LOGO TAHSİL EDİLMEMİŞ ÇEK RAPORU
SELECT TOP (100) PERCENT LG_300_01_PAYTRANS_1.PROCDATE AS [MAK.T.],
dbo.LG_300_CLCARD.CODE AS [CH KOD],
LEFT(dbo.LG_300_CLCARD.DEFINITION_, 40) AS [CH ACIKLAMA],
LEFT(dbo.LG_300_01_INVOICE.FICHENO, 11) AS [FAT.NO.],
dbo.LG_300_01_PAYTRANS.PROCDATE AS [FAT.T.],
LG_300_01_PAYTRANS_1.DATE_ AS [MAK.V.],
LEFT(dbo.LG_300_01_INVOICE.NETTOTAL, 10) AS [FAT.(TL)],
DATEDIFF(DAY, dbo.LG_300_01_PAYTRANS.PROCDATE, LG_300_01_PAYTRANS_1.DATE_) AS GUN,
MONTH(dbo.LG_300_01_INVOICE.DATE_) AS AY,
CASE LG_300_01_PAYTRANS.TRCURR
WHEN 0
THEN (
CASE LG_300_01_PAYTRANS_1.CROSSTOTAL
WHEN 0
THEN (
CASE LG_300_01_PAYTRANS.TRCURR
WHEN 0
THEN (dbo.LG_300_01_PAYTRANS.TOTAL)
ELSE (dbo.LG_300_01_PAYTRANS.TOTAL * dbo.LG_300_01_PAYTRANS.TRRATE)
END
)
ELSE (LG_300_01_PAYTRANS_1.CROSSTOTAL)
END
)
ELSE (LG_300_01_PAYTRANS_1.CROSSTOTAL * dbo.LG_300_01_PAYTRANS.TRRATE)
END AS [MAK. (TL)],
LEFT(dbo.LG_SLSMAN.DEFINITION_, 15) AS [SATIS ELEMANI],
CASE LG_300_01_PAYTRANS_1.MODULENR
WHEN 5
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 4
THEN 'İNDİRİM'
WHEN 70
THEN 'KREDİ KARTI'
WHEN 5
THEN 'VİRMAN'
WHEN 14
THEN 'AÇILIŞ FİŞİ'
END
)
WHEN 10
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 1
THEN 'NAKİT'
END
)
WHEN 6
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 1
THEN 'ÇEK'
END
)
WHEN 7
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 3
THEN 'HAVALE'
END
)
WHEN 4
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 3
THEN 'İADE FATURASI'
WHEN 4
THEN 'HİZMET FATURASI'
WHEN 1
THEN 'MAL FATURASI'
END
)
END AS [MAK.TİPİ],
dbo.LG_300_01_INVOICE.TRADINGGRP
FROM dbo.LG_SLSMAN
RIGHT OUTER JOIN dbo.LG_300_01_INVOICE
ON dbo.LG_SLSMAN.LOGICALREF = dbo.LG_300_01_INVOICE.SALESMANREF
LEFT OUTER JOIN dbo.LG_300_CLCARD
ON dbo.LG_300_01_INVOICE.CLIENTREF = dbo.LG_300_CLCARD.LOGICALREF
RIGHT OUTER JOIN dbo.LG_300_01_PAYTRANS AS LG_300_01_PAYTRANS_1
LEFT OUTER JOIN dbo.LG_300_BANKACC
ON LG_300_01_PAYTRANS_1.BANKACCREF = dbo.LG_300_BANKACC.LOGICALREF
RIGHT OUTER JOIN dbo.LG_300_01_PAYTRANS
ON LG_300_01_PAYTRANS_1.LOGICALREF = dbo.LG_300_01_PAYTRANS.CROSSREF
ON dbo.LG_300_01_INVOICE.TRCODE = dbo.LG_300_01_PAYTRANS.TRCODE
AND dbo.LG_300_01_INVOICE.LOGICALREF = dbo.LG_300_01_PAYTRANS.FICHEREF WHERE (
dbo.LG_300_01_PAYTRANS.TRCODE = 8
OR dbo.LG_300_01_PAYTRANS.TRCODE = 14
)
AND (
CASE LG_300_01_PAYTRANS_1.MODULENR
WHEN 5
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 4
THEN 'İNDİRİM'
WHEN 70
THEN 'KREDİ KARTI'
WHEN 5
THEN 'VİRMAN'
WHEN 14
THEN 'AÇILIŞ FİŞİ'
END
)
WHEN 10
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 1
THEN 'NAKİT'
END
)
WHEN 6
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 1
THEN 'ÇEK'
END
)
WHEN 7
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 3
THEN 'HAVALE'
END
)
WHEN 4
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 3
THEN 'İADE FATURASI'
WHEN 4
THEN 'HİZMET FATURASI'
WHEN 1
THEN 'MAL FATURASI'
END
)
END = 'ÇEK'
)
AND (GETDATE() - LG_300_01_PAYTRANS_1.PROCDATE <= 90)
AND (
LG_300_01_PAYTRANS_1.FICHEREF NOT IN (
SELECT CST.ROLLREF
FROM LG_300_01_CSTRANS CST
WHERE CST.CSREF IN (
SELECT CSC.LOGICALREF
FROM LG_300_01_CSCARD CSC
WHERE CSC.CURRSTAT = 8
)
)
)
ORDER BY [CH ACIKLAMA]
GO
SQL SERVER SEARCH ALL DATABASE SPECIFIC TABLE NAME
USE YENIDATA
GO
DECLARE @dbname VARCHAR(500)
DECLARE @tblname NVARCHAR(500)
SET @tblname = 'LG%'
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT * FROM [sys].[databases] d
INNER JOIN [sys].[master_files] m ON d.database_id = m.database_id
WHERE d.state_desc = 'ONLINE'
AND m.state = 0 -- ONLINE
AND d.name = @dbname
AND EXISTS (SELECT 1 FROM [sys].[tables] t WHERE t.name LIKE @tblname))
BEGIN
PRINT 'Tables starting with [' + @tblname + '] exists in database [' + @dbname + ']'
EXEC ('USE [' + @dbname + ']; SELECT TABLE_CATALOG+''.''+TABLE_SCHEMA+''.''+TABLE_NAME AS ''Table Name'' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''' + @tblname + ''' AND TABLE_TYPE=''BASE TABLE''')
END
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
LOGO REÇETE VE ALT MAMÜLLERİ SQL
WITH ItemHierarcyh (STCREF, MAINCREF, CARDTYPE)
AS (
SELECT STCREF, MAINCREF, CARDTYPE
FROM LG_123_STCOMPLN
WHERE MAINCREF = (SELECT I.LOGICALREF FROM LG_123_ITEMS I WHERE I.CODE='RECETE0016')
UNION ALL
SELECT S.STCREF, S.MAINCREF, S.CARDTYPE
FROM LG_123_STCOMPLN S
JOIN ItemHierarcyh PH ON S.MAINCREF = PH.STCREF
)
SELECT
STCREF,
AltMalzeme=(select I.CODE+'-'+I.NAME from LG_123_ITEMS I WHERE I.LOGICALREF =STCREF),
MAINCREF,
AnaMalzeme=(select I.CODE+'-'+I.NAME from LG_123_ITEMS I WHERE I.LOGICALREF =MAINCREF),
CARDTYPE
FROM ItemHierarcyh --where CARDTYPE in (11,12)
OPTION (MAXRECURSION 0)
LOGO INVOICE TABLOSU VE URUN SATIRLARI(SQL STUFF)
Invoice tablosu baz alınarak sql stuff komutuyla malzeme/hizmetleri getirmek için:
SELECT
FIS_MALZEME_HIZMET= (
Stuff (
(
SELECT DISTINCT
N', ' + CASE
WHEN STL.LINETYPE=0 THEN (SELECT ITEMS.NAME FROM LG_121_ITEMS ITEMS WHERE ITEMS.LOGICALREF=STL.STOCKREF)
WHEN STL.LINETYPE=4 THEN (SELECT SRVCARD.DEFINITION_ FROM LG_121_SRVCARD SRVCARD WHERE SRVCARD.LOGICALREF=STL.STOCKREF)
END
FROM
LG_121_01_STLINE STL
WHERE
STL.INVOICEREF=I.LOGICALREF AND STL.LINETYPE IN (0/*MALZEME*/,4/*HIZMET*/)
/*ORDER BY
STL.LOGICALREF*/
FOR XML PATH(''),TYPE
)
.value('text()[1]','nvarchar(max)'),1,2,N''
)
)
,
* FROM LG_121_01_INVOICE I
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])
Blog of Sem 