/*birinci yöntem logo ve sql server üzerinden:
hangi firma silinecekse dönemi silinir sonra firması silinir
örnek ilk önce 531li viewleri sil
sonrada tablolardan 531 sil
*/
/*İŞLEM ÖNCESİ VERİTABANININ YEDEK ALINMASI TAVSİYE EDİLİR*/
/*ÖNCE SYSDEN İLGİLİ FİRMANIN DÖNEMİ ÇIKARILIR VE SONRASINDA FİRMA ÇIKAR YAPILIR*/
/*AŞAĞIDAKİ İŞLEMLERE FİRMAYA BAĞLI VİEW,TABLO,PROSEDUR,FONKSIYONLARI SİLMEKTEDİR*/
DECLARE @firmno varchar(3)='999' /*İŞLEM YAPILMASI İSTENİLEN FİRMA NOSU YAZ*/
DECLARE @view_name SYSNAME
DECLARE @table_name SYSNAME
DECLARE @procedure_name SYSNAME
DECLARE @function_name SYSNAME
/*VIEWLERI SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sysobjects
WHERE (name LIKE 'LV_' + @firmno + '%' AND xtype='V' )
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @view_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP VIEW '+ @view_name)
FETCH NEXT FROM Logo_objs into @view_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*VIEWLERI SIL BİTİŞ*/
/*TABLOLARI SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sysobjects
WHERE (name LIKE 'LG_' + @firmno + '%' AND XTYPE = 'U')
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP TABLE '+ @table_name)
FETCH NEXT FROM Logo_objs into @table_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*TABLOLARI SIL BİTİŞ*/
/*PROSEDURLERİ SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sysobjects
WHERE (name LIKE '%' + @firmno + '%' AND XTYPE = 'P')
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @procedure_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP PROCEDURE '+ @procedure_name)
FETCH NEXT FROM Logo_objs into @procedure_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*PROSEDURLERİ SIL BİTİŞ*/
/*FONKSİYONLARI SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE (name like 'LG_' + @firmno + '%' AND type ='FN')
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @function_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP FUNCTION '+ @function_name)
FETCH NEXT FROM Logo_objs into @function_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*FONKSİYONLARI SIL BİTİŞ*/
Category: LOGO ERP
logo Cari Bakiyeler (RD Kontrollü)
SELECT
OGRENCI_NO= CLCARD.CODE ,
OGRENCI_ADI= CLCARD.DEFINITION_,
DOVIZ_TURU= CASE CCURRENCY WHEN 0 THEN 'TL' WHEN 160 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END,
/*TL_BAKIYE= ROUND((SUM((1 - CLFLINE.SIGN) * CLFLINE.AMOUNT) - SUM(CLFLINE.SIGN * CLFLINE.AMOUNT)),2),
RD_BAKIYE= ROUND((SUM((1 - CLFLINE.SIGN) * (CLFLINE.AMOUNT/NULLIF(CLFLINE.REPORTRATE,0)))
-
SUM(CLFLINE.SIGN * (CLFLINE.AMOUNT/NULLIF(CLFLINE.REPORTRATE,0)))),2),*/
/*BAKIYE= CASE WHEN CCURRENCY IN (0,160) THEN
ROUND((SUM((1 - CLFLINE.SIGN) * CLFLINE.AMOUNT) - SUM(CLFLINE.SIGN * CLFLINE.AMOUNT)),2)
ELSE
ROUND((SUM((1 - CLFLINE.SIGN) * (CLFLINE.AMOUNT/NULLIF(CLFLINE.REPORTRATE,0)))
-
SUM(CLFLINE.SIGN * (CLFLINE.AMOUNT/NULLIF(CLFLINE.REPORTRATE,0)))),2)
END ,*/
BAKIYE= CASE WHEN CCURRENCY IN (0,160) THEN
ROUND((SUM((1 - CLFLINE.SIGN) * CLFLINE.AMOUNT) - SUM(CLFLINE.SIGN * CLFLINE.AMOUNT)),2)
ELSE
ROUND((SUM((1 - CLFLINE.SIGN) * (CLFLINE.REPORTNET))
-
SUM(CLFLINE.SIGN * (CLFLINE.REPORTNET))),2)
END
FROM
LG_118_01_CLFLINE CLFLINE INNER JOIN
LG_118_CLCARD CLCARD ON CLFLINE.CLIENTREF = CLCARD.LOGICALREF AND CLFLINE.CANCELLED = 0
WHERE CLCARD.CYPHCODE='UNI' AND CLFLINE.CANCELLED=0
and CLFLINE.DATE_<='09/01/2020' /*ay/gun/yıl*/
GROUP BY CLCARD.CCURRENCY, CLCARD.CODE, CLCARD.DEFINITION_, CLCARD.ACTIVE
HAVING (CLCARD.CODE LIKE '%') and (CLCARD.DEFINITION_ LIKE '%')
AND (CLCARD.ACTIVE = 0) ORDER BY CLCARD.CODE DESC
LOGO MOBILESALES CARI ZIYARET RAPORU
SELECT DISTINCT M.P_KODU,
M.CARI_KODU,M.CARI_ADI,M.GUN,
(
SELECT TOP 1 WP.DATE_ FROM WOR_PROCESS WP ,WOR_USERS WU WHERE WP.FIRM = '121'
AND WU.APPTYPE=1 AND WP.USERID=WU.USERID
AND ( WP.DATE_ BETWEEN ? AND ? )
AND WU.USERNAME=M.P_KODU AND WP.CLCODE=M.CARI_KODU
ORDER BY WP.DATE_ DESC
) SON_GIDILEN_TARIH,
(SELECT W2.FNAME FROM WOR_USERS W2 WHERE W2.CODE=M.P_KODU) AS P_ADI
FROM E_WINGS_121_SCS3 M WHERE 1=1
LOGO CARI RISK SIPARIS KAPAT SQL
SENARYO
- Bir Carinin borcu 50 gün ve daha fazlaysa job ile risk bilgilerinde siparişe kapatılsın güncellenmesi yapılacak (LOGO FIRMA ADI 210).
- Job çalışma süresi her gün sabah saat 7
İŞLEYİŞ
- Önce bu kritere uyan cariler view ile belirlenecek (VIEW ADI: CARIRISKSIPARISKAPAT_210)
- Job ile bu view çağrılıp gerekli güncelleme işlemi yapılacak
CREATE VIEW CARIRISKSIPARISKAPAT_210 AS
SELECT * FROM (
SELECT
CLLOGICALREF= C.LOGICALREF,
[Cari Kodu]= C.CODE,
[Cari Ünvanı]= C.DEFINITION_,
[İşlem]= ISNULL((
SELECT TOP 1
CASE WHEN DATEDIFF(DAY,P.DATE_,GETDATE())>=50 THEN 'Siparişe Kapat' END ISLEM
FROM LG_210_01_PAYTRANS P WITH(NOLOCK)
WHERE
P.MODULENR=4 AND P.TRCODE IN (7,8) AND P.PAID=0 AND P.CARDREF=C.LOGICALREF AND P.CANCELLED=0 ORDER BY P.DATE_ ASC
),''),
[Siparişte]= ISNULL((SELECT
CASE CLR.ORDRISKOVER
WHEN 0 THEN 'İşleme Devam Edilecek'
WHEN 1 THEN 'Kullanıcı Uyarılacak'
WHEN 2 THEN 'İşlem Durdurulacak'
END FROM LG_210_01_CLRNUMS CLR WITH(NOLOCK) WHERE CLR.CLCARDREF=C.LOGICALREF),'')
FROM LG_210_CLCARD C WITH(NOLOCK) WHERE ACTIVE=0
) AS T WHERE T.İşlem<>'' and Siparişte not like '%Durdurulacak'
/*JOB SORGUSU UPDATE İŞLEMİ*/
SET QUOTED_IDENTIFIER ON
UPDATE LG_210_01_CLRNUMS SET ORDRISKOVER = 2
WHERE CLCARDREF IN (SELECT B.CLLOGICALREF FROM CARIRISKSIPARISKAPAT_210 B )
LOGO CARI HESAP LISTESI BAKIYE, BAKIYERD
SELECT
T.LOGICALREF,
KOD= CODE,
AD= T.DEFINITION_,
TL= T.BAKIYE,
BA_TL= CASE WHEN T.BAKIYE < 0 THEN 'A' WHEN T.BAKIYE > 0 THEN 'B' ELSE '' END,
DOVIZ= T.BAKIYE_RD,
BA_DOVIZ= CASE WHEN T.BAKIYE < 0 THEN 'A' WHEN T.BAKIYE > 0 THEN 'B' ELSE '' END
FROM
(
SELECT
CLC.LOGICALREF,
CLC.CODE,
CLC.DEFINITION_,
ISNULL(
(
SELECT
CONVERT(
DECIMAL(38, 2),
(
SUM (GNCLTOT.DEBIT)- SUM (GNCLTOT.CREDIT)
)
)
FROM
LV_118_01_GNTOTCL GNCLTOT
WHERE
(GNCLTOT.TOTTYP = 1) AND
GNCLTOT.CARDREF = CLC.LOGICALREF
),
0
) BAKIYE,
'' BA,
ISNULL(
(
SELECT
CONVERT(
DECIMAL(38, 2),
(
SUM (GNCLTOT.DEBIT)- SUM (GNCLTOT.CREDIT)
)
)
FROM
LV_118_01_GNTOTCL GNCLTOT
WHERE
(GNCLTOT.TOTTYP = 2) AND
GNCLTOT.CARDREF = CLC.LOGICALREF
),
0
) BAKIYE_RD,
'' BA_RD
FROM
LG_118_CLCARD CLC
WHERE
CODE <> 'ÿ'
AND CLC.ACTIVE = 0
) AS T
LOGO LOD BROWSER POPUPTAN İSTENİLENİ GİZLEME
Sub PopupMenuClick(ctrl as String, index as Integer, result as Integer)
'AccFicheBrwPopUp
if ctrl="AccFicheBrwPopUp" then
'toplu iptal 101
'toplu iptal geri al 102
warn(index)
end if
End Sub
Sub PopupFilterAction(ctrl as String, actionId as Integer, canDo as Integer)
if ctrl="AccFicheBrwPopUp" and (actionId=101 or actionId=102 ) then
canDo=0
end if
End Sub
LOGO PAYTRANS FATURADAN CEK SENET KAPATILAN VADE KONTROLLERI
CREATE VIEW [dbo].[CEKSENETDETAY_RAPORU_212] AS
select * from (
SELECT
T2.TUR,T2.FISNO,T2.BRANCH ISYERI_NR
,
(select CONVERT(VARCHAR,CD.NR)+', '+NAME from L_CAPIDIV CD WITH(NOLOCK) WHERE CD.FIRMNR=210 AND NR=T2.BRANCH) ISYERI
,
CARI_KOD
,
CARI_AD
,
CARI_OZELKOD3
,
ISNULL((SELECT PAYPLANS.CODE+', '+PAYPLANS.DEFINITION_ FROM LG_210_PAYPLANS PAYPLANS WITH(NOLOCK) WHERE PAYPLANS.LOGICALREF=T2.PAYPLANREF),'') CARI_VADE
,
ISNULL((SELECT CASE WHEN DAY_='' THEN 0 ELSE DAY_ END FROM LG_210_PAYLINES PL WITH(NOLOCK) WHERE PL.PAYPLANREF=T2.PAYPLANREF),0) CARI_VADE_GUN
,
/*CEK_NO,*/DOVIZ,TUTAR,KAPAMA_TUTAR,FATURA_ISLEM_TARIHI,FATURA_VADE_TARIHI, KARSI_ISLEM_BILGILERI
,
DATEADD(DAY, ISNULL((SELECT CASE WHEN DAY_='' THEN 0 ELSE DAY_ END FROM LG_210_PAYLINES PL WITH(NOLOCK) WHERE PL.PAYPLANREF=(SELECT C.PAYMENTREF FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=CARDREF)),0), FATURA_VADE_TARIHI) AS FATURA_ODENMESI_GEREKEN_TARIH
,
convert(datetime, REPLACE(LEFT(KARSI_ISLEM_BILGILERI, 29),'Müşteri Çeki Vade:[',''),103 ) CEKODEME_TARIHI
,
SUBSTRING(KARSI_ISLEM_BILGILERI,CHARINDEX('CekNo:',KARSI_ISLEM_BILGILERI) ,LEN(KARSI_ISLEM_BILGILERI)) CEKNO
,
DATEDIFF(day,DATEADD(DAY, ISNULL((SELECT CASE WHEN DAY_='' THEN 0 ELSE DAY_ END FROM LG_210_PAYLINES PL WITH(NOLOCK) WHERE PL.PAYPLANREF=(SELECT C.PAYMENTREF FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=CARDREF)),0), FATURA_VADE_TARIHI), convert(datetime, REPLACE(LEFT(KARSI_ISLEM_BILGILERI, 29),'Müşteri Çeki Vade:[',''),103 ) ) FATURA_CEKODEME_TARIH_GUN_FARK
FROM (
SELECT DISTINCT
*,
CASE T.TRCURR WHEN 0 THEN 'TL' WHEN 160 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE CONVERT(VARCHAR,T.TRCURR) END DOVIZ
,
(SELECT SUM(P.TOTAL) FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND
P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF ) TUTAR
,
ISNULL((SELECT SUM(P.PAID) FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND
P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF AND PAID<>0),0) KAPAMA_TUTAR
,
ISNULL((
STUFF(
(SELECT ','+dbo.PAYTRANS2ISLEMTURU(PP.TRCODE,PP.MODULENR)+' Vade:['+(CASE WHEN LEN(CAST(DAY(PP.DATE_) AS varchar))=1 THEN '0'+CAST(DAY(PP.DATE_) AS varchar) ELSE CAST(DAY(PP.DATE_) AS varchar) END )+'.'+(CASE WHEN LEN(CAST(MONTH(PP.DATE_) AS varchar))=1 THEN '0'+CAST(MONTH(PP.DATE_) AS varchar) ELSE CAST(MONTH(PP.DATE_) AS varchar) END ) +'.'+CAST(YEAR(PP.DATE_) AS varchar)+'] / Tutar:'+format((ROUND(PP.PAID,2)),N'','tr-TR') +
' / CekNo:'+(SELECT CSCARD.NEWSERINO FROM LG_210_01_CSCARD CSCARD WITH(NOLOCK) WHERE CSCARD.LOGICALREF=
(SELECT CST.CSREF FROM LG_210_01_CSTRANS CST WITH(NOLOCK) WHERE CST.ROLLREF=Pp.FICHEREF AND CST.LOGICALREF=Pp.FICHELINEREF))
FROM LG_210_01_PAYTRANS PP WITH(NOLOCK) WHERE PP.LOGICALREF IN ((SELECT P.CROSSREF FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND
P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF AND PP.MODULENR=6 ))
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),
1,
1,
''
)
),'') KARSI_ISLEM_BILGILERI
FROM (
SELECT
dbo.BM_PAYTRANS2ISLEMTURU(P.TRCODE,P.MODULENR) TUR,
(SELECT I.FICHENO FROM LG_210_01_INVOICE I WITH(NOLOCK) WHERE I.LOGICALREF=P.FICHEREF) FISNO,
P.BRANCH,
P.TRCURR,
P.CARDREF ,
(SELECT C.PAYMENTREF FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=P.CARDREF) PAYPLANREF,
(SELECT C.CODE FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=P.CARDREF) CARI_KOD,
(SELECT C.DEFINITION_ FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=P.CARDREF) CARI_AD,
(SELECT C.SPECODE3 FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=P.CARDREF) CARI_OZELKOD3,
P.FICHEREF PAYTRANS_FICHEREF, /*CSTRANS.ROLLREF*/
P.FICHELINEREF PAYTRANS_FICHELINEREF , /*CSTRANS.LOGICALREF*/
/*(SELECT CSCARD.NEWSERINO FROM LG_210_01_CSCARD CSCARD WITH(NOLOCK) WHERE CSCARD.LOGICALREF=
(SELECT CST.CSREF FROM LG_210_01_CSTRANS CST WITH(NOLOCK) WHERE CST.ROLLREF=P.FICHEREF AND CST.LOGICALREF=P.FICHELINEREF)) CEK_NO,*/
P.PROCDATE FATURA_ISLEM_TARIHI,
P.DATE_ FATURA_VADE_TARIHI
FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE MODULENR=4 AND TRCODE IN (7,8,9)
AND CROSSREF IN (SELECT P2.LOGICALREF FROM LG_210_01_PAYTRANS P2 WITH(NOLOCK) WHERE P2.MODULENR=6)
/*P.MODULENR=6*//*CEKLER*/ /* AND P.CARDREF=5571
AND YEAR(DATE_)=2021 AND MONTH(DATE_)=4 AND DAY(DATE_)=8 */
) AS T
) AS T2
) as t3
GO
LOGO PAYTRANS ÇEK SENET DETAY RAPORU
SELECT
T2.TUR,T2.BRANCH ISYERI_NR
,
(select CONVERT(VARCHAR,NR)+', '+NAME from L_CAPIDIV WITH(NOLOCK) WHERE FIRMNR=210 and NR=T2.BRANCH) ISYERI
,
(SELECT C.CODE FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=CARDREF) CARI_KOD
,
(SELECT C.DEFINITION_ FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=CARDREF) CARI_AD
,
CEK_NO,ISLEM_TARIHI,VADE_TARIHI,CEK_DOVIZ,TUTAR,KAPAMA_TUTAR,KARSI_ISLEM_BILGILERI
FROM (
SELECT DISTINCT
*,
CASE T.TRCURR WHEN 0 THEN 'TL' WHEN 160 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE CONVERT(VARCHAR,T.TRCURR) END CEK_DOVIZ
,
(SELECT SUM(P.TOTAL) FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND
P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF ) TUTAR
,
ISNULL((SELECT SUM(P.PAID) FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND
P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF AND PAID<>0),0) KAPAMA_TUTAR
,
ISNULL((
STUFF(
(SELECT ','+dbo.PAYTRANS2ISLEMTURU(PP.TRCODE,PP.MODULENR)+'['+ CAST(DAY(PP.PROCDATE) AS varchar)+'.'+CAST(MONTH(PP.PROCDATE) AS varchar) +'.'+CAST(YEAR(PP.PROCDATE) AS varchar)+'] /'+format((ROUND(PP.PAID,2)),N'','tr-TR') FROM LG_210_01_PAYTRANS PP WITH(NOLOCK) WHERE PP.LOGICALREF IN ((SELECT P.CROSSREF FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND
P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF ))
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),
1,
1,
''
)
),'') KARSI_ISLEM_BILGILERI
FROM (
SELECT
dbo.BM_PAYTRANS2ISLEMTURU(P.TRCODE,P.MODULENR) TUR,
P.BRANCH,
P.TRCURR,
P.CARDREF ,
P.FICHEREF PAYTRANS_FICHEREF, /*CSTRANS.ROLLREF*/
P.FICHELINEREF PAYTRANS_FICHELINEREF , /*CSTRANS.LOGICALREF*/
(SELECT CSCARD.NEWSERINO FROM LG_210_01_CSCARD CSCARD WITH(NOLOCK) WHERE CSCARD.LOGICALREF=
(SELECT CST.CSREF FROM LG_210_01_CSTRANS CST WITH(NOLOCK) WHERE CST.ROLLREF=P.FICHEREF AND CST.LOGICALREF=P.FICHELINEREF)) CEK_NO,
P.PROCDATE ISLEM_TARIHI,
P.DATE_ VADE_TARIHI
FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.MODULENR=6/*CEKLER*/ /* AND P.CARDREF=5571 */
/*AND YEAR(DATE_)=2021 AND MONTH(DATE_)=4 AND DAY(DATE_)=8*/
) AS T
) AS T2
LOGO STLINE HATALI MALZEME BİRİMLERİ
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<>''
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…
Blog of Sem 