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
C# TURKCE KARAKTER->INGILIZCE KARAKTER FONKSIYONU
public string TurkishCharacterToEnglish(string text)
{
char[] turkishChars = { 'ı', 'ğ', 'İ', 'Ğ', 'ç', 'Ç', 'ş', 'Ş', 'ö', 'Ö', 'ü', 'Ü' };
char[] englishChars = { 'i', 'g', 'I', 'G', 'c', 'C', 's', 'S', 'o', 'O', 'u', 'U' };
for (int i = 0; i < turkishChars.Length; i++)
text = text.Replace(turkishChars[i], englishChars[i]);
return text;
}
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…
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
LOGO SQL FATURA TAHSILAT
SELECT * FROM (
SELECT
INVOICE.LOGICALREF INVOICELOGICALREF,
(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 FATURATIPI,
INVOICE.FICHENO FATURANO,
INVOICE.DATE_ as FATURATARIHI,
YEAR(INVOICE.DATE_) as YIL,
100*(YEAR(INVOICE.DATE_))+MONTH(INVOICE.DATE_) AS [Ay],
INVOICE.DOCODE AS BELGENO,
INVOICE.SPECODE AS OZELKOD,
CLCARD.CODE as CARIKOD,
CLCARD.DEFINITION_ as CARIAD,
(SELECT P.CODE FROM LG_201_PAYPLANS P WHERE P.LOGICALREF=INVOICE.PAYDEFREF) ODEME,
INVOICE.TRADINGGRP CARIGRUBU,
(SELECT S.CODE+'-'+S.DEFINITION_ FROM LG_SLSMAN S WHERE S.FIRMNR=201 AND S.LOGICALREF= INVOICE.SALESMANREF) SATISELEMANI,
INVOICE.CYPHCODE YETKIKODU,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIDIV D WHERE D.FIRMNR=201 AND D.NR=INVOICE.BRANCH) ISYERI,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIDEPT D WHERE D.FIRMNR=201 AND D.NR=INVOICE.DEPARTMENT) BOLUM,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIFACTORY D WHERE D.FIRMNR=201 AND D.NR=INVOICE.FACTORYNR) FABRIKA,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIWHOUSE D WHERE D.FIRMNR=201 AND D.NR=INVOICE.SOURCEINDEX) AMBAR,
(SELECT LEFT(D.CODE,4) FROM LG_201_PROJECT D WHERE D.LOGICALREF=INVOICE.PROJECTREF) PROJE,
(SELECT D.CODE+'-'+ D.NAME FROM LG_201_PROJECT D WHERE D.LOGICALREF=INVOICE.PROJECTREF) PROJEKODU,
(SELECT D.NAME FROM LG_201_PROJECT D WHERE D.LOGICALREF=INVOICE.PROJECTREF) PROJEACIKLAMASI,
(SELECT COUNT(*) FROM LG_201_01_STLINE S WHERE S.INVOICEREF=INVOICE.LOGICALREF ) SATIR_SAYISI,
RTRIM(INVOICE.GENEXP1+' '+INVOICE.GENEXP2+' '+INVOICE.GENEXP3+' '+INVOICE.GENEXP4) AS FISACIKLAMA,
INVOICE.TOTALEXPENSES MASRAF,
INVOICE.TOTALDISCOUNTS INDIRIM,
INVOICE.NETTOTAL-INVOICE.TOTALVAT TOPLAM,
INVOICE.TOTALEXADDTAX EKVERGI,
INVOICE.TOTALVAT KDV,
INVOICE.NETTOTAL NET,
CASE CLCARD.CCURRENCY WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END CARIDOVIZ,
CASE INVOICE.TRCURR WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END ISLEMDOVIZ ,
ISNULL((SELECT SUM(CASE WHEN P.TRCURR IN (0,160) THEN P.PAID ELSE P.TOTAL*P.TRRATE END) FROM LG_201_01_PAYTRANS P WHERE P.MODULENR=4 AND P.FICHEREF=INVOICE.LOGICALREF AND P.SIGN=0 AND P.PAID>0.00000001),0) TOPLAMTAHSILATTUTAR,
(
STUFF(
(SELECT ','+dbo.PAYTRANS2ISLEMTURU(PP.TRCODE,PP.MODULENR)+'['+ CAST(DAY(PP.DATE_) AS varchar)+'.'+CAST(MONTH(PP.DATE_) AS varchar) +'.'+CAST(YEAR(PP.DATE_) AS varchar)+'] /'+format((CASE WHEN PP.TRCURR IN (0,160) THEN ROUND(PP.PAID,2) ELSE ROUND(PP.TOTAL*PP.TRRATE,2) END),N'','tr-TR') FROM LG_201_01_PAYTRANS PP WHERE PP.LOGICALREF IN (
SELECT DISTINCT
S2.CROSSREF /*','+CAST(S2.CROSSREF AS VARCHAR)*/
FROM LG_201_01_PAYTRANS S2
WHERE S2.FICHEREF = INVOICE.LOGICALREF
AND S2.MODULENR=4 AND S2.SIGN=0 )
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),
1,
1,
''
)
) TAHSILATBILGILERI
FROM LG_201_01_INVOICE AS INVOICE WITH (NOLOCK)
LEFT JOIN LG_201_CLCARD CLCARD ON CLCARD.LOGICALREF=INVOICE.CLIENTREF WHERE(INVOICE.TRCODE IN(2,3,7,8,9,10,11,14 /*1,4,5,6,12,13,26,2,3,7,8,9,10,14*/))) AS T
CREATE FUNCTION [dbo].[PAYTRANS2ISLEMTURU](@TRCODE INT,@MODULENR INT )
RETURNS VARCHAR (100)
AS
begin
declare @ISLEM VARCHAR (100)
select @ISLEM = ( SELECT
CASE @MODULENR
WHEN 7 THEN
CASE @TRCODE
WHEN 3 THEN
'Gelen Havale'
WHEN 4 THEN
'Gönderilen Havale'
WHEN 16 THEN
'Banka - Alınan Hizmet Faturası'
WHEN 17 THEN
'Banka - Verilen Hizmet Faturası'
END
WHEN 4 THEN
CASE @TRCODE
WHEN 1 THEN
'Satınalama Faturası'
WHEN 4 THEN
'Alınan Hizmet Faturası'
WHEN 6 THEN
'Satınalma İade Faturası'
WHEN 13 THEN
'Satınalma Fiyat Farkı Faturası'
WHEN 2 THEN
'Perakende Satış İade Faturası'
WHEN 3 THEN
'Toptan Satış İade Faturası'
WHEN 7 THEN
'Perkande Satış Faturası'
WHEN 8 THEN
'Toptan Satış Faturası'
WHEN 9 THEN
'Verilen Hizmet Faturası'
WHEN 14 THEN
'Satış Fiyat Farkı Faturası'
END
WHEN 5 THEN
CASE @TRCODE
WHEN 70 THEN
'Kredi Kartı Fişi'
WHEN 2 THEN
'Nakit Ödeme'
WHEN 1 THEN
'Nakit Tahsilat'
WHEN 3 THEN
'Borç Dekontu'
WHEN 4 THEN
'Alacak Dekontu'
WHEN 5 THEN
'Virman Fişi'
WHEN 6 THEN
'Kurfarkı Fişi'
WHEN 14 THEN
'Açılış Fişi'
WHEN 41 THEN
'Verilen Vade Farkı Faturası'
WHEN 42 THEN
'Alınan Vade Farkı Faturası'
WHEN 71 THEN
'Kredi Kartı İade Fişi'
WHEN 72 THEN
'Firma Kredi Kartı Fişi'
WHEN 73 THEN
'Firma Kredi Kartı İade Fişi'
END
WHEN 6 THEN
CASE @TRCODE
WHEN 1 THEN
'Müşteri Çeki'
WHEN 3 THEN
'Kendi Çekimiz'
END
WHEN 3 THEN
CASE @TRCODE
WHEN 2 THEN
'Satınalama Siparişi'
WHEN 1 THEN
'Satış Siparişi'
END
WHEN 10 THEN
CASE @TRCODE
WHEN 1 THEN
'Nakit Tahsilat'
WHEN 2 THEN
'Nakit Ödeme'
END
WHEN 61 THEN
CASE @TRCODE
WHEN 3 THEN
'Borç Dekontu'
WHEN 4 THEN
'Alacak Dekontu'
END
WHEN 62 THEN
CASE @TRCODE
WHEN 3 THEN
'Borç Dekontu'
WHEN 4 THEN
'Alacak Dekontu'
END
END AS ISLEM
)
return @ISLEM
end
GO
Blog of Sem 