SQL Sorgu Logo' da SQL ile tüm muhasbe fişlerini çekmek.

enverdersin · 27 Ağu 2021

bu koda muhasebe fişindeki masraf merkezi kodu ve işyeri numarası ekleyebilir miyiz?

SELECT
EMU.CODE AS [Hesap Kodu],
EMU.DEFINITION_ AS [Hesap Adı],
EMF.JOURNALNO [Yevmiye No],
EMFL.DATE_ AS [Tarih],
EMFL.LINEEXP [İşlem Açıklaması],
CASE EMF.TRCODE WHEN 1 THEN 'Açılış' WHEN 2 THEN 'Tahsil' WHEN
3 THEN 'Tediye' WHEN 4 THEN 'Mahsup' WHEN 5 THEN 'Özel Fiş' WHEN
6 THEN 'Kur Farkı' WHEN 7 THEN 'Kapanış' ELSE 'Diğer' END AS [Fiş Türü],

EMF.FICHENO AS [Fiş Numarası],

CASE EMFL.SIGN WHEN 0 THEN EMFL.DEBIT ELSE '' END AS [Borç ],
CASE EMFL.SIGN WHEN 1 THEN EMFL.CREDIT ELSE '' END AS [Alacak ]

FROM dbo.LG_420_02_EMFLINE AS EMFL WITH (NOLOCK) LEFT OUTER JOIN LG_420_02_EMFICHE AS EMF WITH (NOLOCK)
ON EMFL.ACCFICHEREF=EMF.LOGICALREF
LEFT JOIN LG_420_EMUHACC AS EMU WITH (NOLOCK)
LEFT JOIN LG_420_EMUHACC AS EM2 WITH (NOLOCK)
ON LEFT(EMU.CODE,3)= EM2.CODE
ON EMU.LOGICALREF = EMFL.ACCOUNTREF LEFT JOIN L_CAPIDIV AS CP
ON EMF.BRANCH = CP.NR
WHERE (EMFL.CANCELLED = 0)
--AND EMF.MODULENR IN (2)
AND EMU.SUBACCOUNTS =0 AND CP.FIRMNR = 420 AND
MONTH (EMFL.DATE_) IN (1,2,3,4,5,6,7,8,9,10,11,12)

mith · 28 Ağu 2021

Masraf merkezi ve işleri numarası eklenmiş ve düzenlenmiş şekilde:

SQL:

SELECT
    EMU.CODE AS [Hesap Kodu],
    EMU.DEFINITION_ AS [Hesap Adı],
    EMF.JOURNALNO [Yevmiye No],
    EMFL.DATE_ AS [Tarih],
    EMFL.LINEEXP [İşlem Açıklaması],
    CASE EMF.TRCODE WHEN 1 THEN 'Açılış' WHEN 2 THEN 'Tahsil' WHEN
    3 THEN 'Tediye' WHEN 4 THEN 'Mahsup' WHEN 5 THEN 'Özel Fiş' WHEN
    6 THEN 'Kur Farkı' WHEN 7 THEN 'Kapanış' ELSE 'Diğer' END AS [Fiş Türü],
    EMF.FICHENO AS [Fiş Numarası],
    CASE EMFL.SIGN WHEN 0 THEN EMFL.DEBIT ELSE '' END AS [Borç ],
    CASE EMFL.SIGN WHEN 1 THEN EMFL.CREDIT ELSE '' END AS [Alacak ],
    EMC.CODE AS [Masraf Merkezi Kodu],
    EMFL.BRANCH AS [İşyeri Numarası]
FROM
    LG_420_02_EMFLINE AS EMFL WITH (NOLOCK)
    LEFT OUTER JOIN LG_420_02_EMFICHE AS EMF WITH (NOLOCK) ON EMFL.ACCFICHEREF=EMF.LOGICALREF
    LEFT JOIN LG_420_EMUHACC AS EMU WITH (NOLOCK) ON EMU.LOGICALREF = EMFL.ACCOUNTREF
    LEFT JOIN LG_420_EMUHACC AS EM2 WITH (NOLOCK) ON LEFT(EMU.CODE,3)= EM2.CODE
    LEFT JOIN L_CAPIDIV AS CP WITH (NOLOCK) ON EMF.BRANCH = CP.NR
    LEFT JOIN LG_420_EMCENTER AS EMC WITH (NOLOCK) ON EMFL.CENTERREF=EMC.LOGICALREF
WHERE
    (EMFL.CANCELLED = 0) --AND EMF.MODULENR IN (2)
    AND EMU.SUBACCOUNTS =0 AND CP.FIRMNR = 420
    AND    MONTH (EMFL.DATE_) IN (1,2,3,4,5,6,7,8,9,10,11,12)

SQL Sorgu Logo' da SQL ile tüm muhasbe fişlerini çekmek.

enverdersin

mith