LOGO YANSITMA ŞABLONU QUERYISI

DECLARE @GIDER_BASL_HESAP_KODU VARCHAR(50), @GIDER_BITIS_HESAP_KODU VARCHAR(50), @GIDER_HESAP_KODU VARCHAR(50), @GIDER_YANSITMA_HESAP_KODU VARCHAR(50), @GELIR_YANSITMA_HESAP_KODU VARCHAR(50), @TARIH_BASL VARCHAR(50), @TARIH_BITIS VARCHAR(50), @FIRMNR VARCHAR(3),
@ACIKLAMA_YANSITMA1  VARCHAR(150),@ACIKLAMA_YANSITMA2  VARCHAR(150),@ACIKLAMA_GELIRLERIKAPAT   VARCHAR(150),@ACIKLAMA_GIDERLERIKAPAT    VARCHAR(150)
SET @FIRMNR='212';
SET @GIDER_HESAP_KODU = '690.01.01.000'
SET @GIDER_BASL_HESAP_KODU='720.01.01.001'
SET @GIDER_BITIS_HESAP_KODU='720.01.01.002'
SET @GIDER_YANSITMA_HESAP_KODU = '721.01.01.001'
SET @GELIR_YANSITMA_HESAP_KODU = '620.01.01.001'
SET @TARIH_BASL= '1-1-2018'
SET @TARIH_BITIS='12-31-2018'
SET @ACIKLAMA_YANSITMA1='24.10.2019 tarihli dönem içi yansıtma kaydı';
SET @ACIKLAMA_YANSITMA2='24.10.2019 tarihli dönem sonu yansıtma kaydı';
SET @ACIKLAMA_GELIRLERIKAPAT='24.10.2019 Tarihli Gelir Kapatma Kaydı';
SET @ACIKLAMA_GIDERLERIKAPAT='24.10.2019 Tarihli Gider Kapatma Kaydı';
--YANSITMA1
SELECT * FROM (
SELECT *
FROM
(
    SELECT '1-YANSITMA1' TUR,
           @GELIR_YANSITMA_HESAP_KODU BORC_HESAP,
@GIDER_YANSITMA_HESAP_KODU  ALACAK_HESAP,@ACIKLAMA_YANSITMA1 ACIKLAMA,
           BRANCH ISYERINR,
           (
               SELECT NAME FROM L_CAPIDIV D WHERE D.NR = GLTRN.BRANCH AND D.FIRMNR = @FIRMNR
           ) ISYERI,
           GLTRN.TRCURR,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT)) DEBIT,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT)) CREDIT,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.REPORTNET * (1 - 2 * GLTRN.SIGN))) REPORTNET,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN))) TRNET_TUTAR,   CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN))) SON
    FROM LG_212_01_EMFLINE GLTRN WITH (NOLOCK)
    WHERE (GLTRN.DATE_ >= CONVERT(DATETIME, @TARIH_BASL, 101))
          AND (GLTRN.DATE_ <= CONVERT(DATETIME, @TARIH_BITIS, 101))
          AND (GLTRN.CANCELLED = 0)
          AND (GLTRN.TRCODE NOT IN ( 5 ))
          AND --
        ACCOUNTCODE
          BETWEEN @GIDER_BASL_HESAP_KODU AND @GIDER_BITIS_HESAP_KODU
    GROUP BY GLTRN.TRCURR,
             BRANCH
) AS T
WHERE T.TRNET_TUTAR <> 0
--YANSITMA2
UNION ALL
SELECT *
FROM
(
    SELECT '2-YANSITMA2' TUR,
            @GIDER_YANSITMA_HESAP_KODU BORC,
           ACCOUNTCODE ALACAK,@ACIKLAMA_YANSITMA2 ACIKLAMA,
           BRANCH ISYERINR,
           (
               SELECT NAME FROM L_CAPIDIV D WHERE D.NR = GLTRN.BRANCH AND D.FIRMNR = @FIRMNR
           ) ISYERI,
           GLTRN.TRCURR,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT)) DEBIT,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT)) CREDIT,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.REPORTNET * (1 - 2 * GLTRN.SIGN))) REPORTNET,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN))) TRNET_TUTAR,   CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN))) SON
    FROM LG_212_01_EMFLINE GLTRN WITH (NOLOCK)
    WHERE (GLTRN.DATE_ >= CONVERT(DATETIME,@TARIH_BASL, 101))
          AND (GLTRN.DATE_ <= CONVERT(DATETIME,@TARIH_BITIS, 101))
          AND (GLTRN.CANCELLED = 0)
          AND (GLTRN.TRCODE NOT IN ( 5 ))
          AND --
        ACCOUNTCODE
         BETWEEN @GIDER_BASL_HESAP_KODU AND @GIDER_BITIS_HESAP_KODU
    GROUP BY GLTRN.TRCURR,ACCOUNTCODE,BRANCH
) AS T
WHERE T.TRNET_TUTAR <> 0
--GELİRLERİ KAPAT
UNION ALL
SELECT  * from (
SELECT *
FROM
(
    SELECT '3-GELİRLERİ KAPAT' TUR,
            @GIDER_HESAP_KODU BORC,
           ACCOUNTCODE ALACAK,@ACIKLAMA_GELIRLERIKAPAT ACIKLAMA,
           BRANCH ISYERINR,
           (
               SELECT NAME FROM L_CAPIDIV D WHERE D.NR = GLTRN.BRANCH AND D.FIRMNR = @FIRMNR
           ) ISYERI,
           GLTRN.TRCURR,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT)) DEBIT,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT)) CREDIT,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.REPORTNET * (1 - 2 * GLTRN.SIGN))) REPORTNET,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN))) TRNET_TUTAR,
    CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT))- CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT)) SON
    FROM LG_212_01_EMFLINE GLTRN WITH (NOLOCK)
    WHERE (GLTRN.DATE_ >= CONVERT(DATETIME,@TARIH_BASL, 101))
          AND (GLTRN.DATE_ <= CONVERT(DATETIME, @TARIH_BITIS, 101))
          AND (GLTRN.CANCELLED = 0)
          AND (GLTRN.TRCODE NOT IN ( 5 ))
          AND
    (ACCOUNTCODE LIKE  LEFT(@GIDER_HESAP_KODU,1) +'%' AND ACCOUNTCODE<@GIDER_HESAP_KODU)
    GROUP BY ACCOUNTCODE,GLTRN.TRCURR,BRANCH
) AS T
WHERE
 (DEBIT-CREDIT>0)  ) AS T2    WHERE SON>0
--GIDERLERİ KAPAT
UNION ALL
SELECT  * from ( 
SELECT *
FROM
(
    SELECT '4-GIDERLERİ KAPAT' TUR,
            ACCOUNTCODE   BORC,
           @GIDER_HESAP_KODU  ALACAK,@ACIKLAMA_GIDERLERIKAPAT ACIKLAMA,
           BRANCH ISYERINR,
           (
               SELECT NAME FROM L_CAPIDIV D WHERE D.NR = GLTRN.BRANCH AND D.FIRMNR = @FIRMNR
           ) ISYERI,
           GLTRN.TRCURR,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT)) DEBIT,
           CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT)) CREDIT,
           ABS(CONVERT(DECIMAL(10, 2), SUM(GLTRN.REPORTNET * (1 - 2 * GLTRN.SIGN)))) REPORTNET,
           ABS(CONVERT(DECIMAL(10, 2), SUM(GLTRN.TRNET * (1 - 2 * GLTRN.SIGN)))) TRNET_TUTAR,
   CONVERT(DECIMAL(10, 2), SUM(GLTRN.CREDIT))- CONVERT(DECIMAL(10, 2), SUM(GLTRN.DEBIT)) SON
    FROM LG_212_01_EMFLINE GLTRN WITH (NOLOCK)
    WHERE (GLTRN.DATE_ >= CONVERT(DATETIME, @TARIH_BASL, 101))
          AND (GLTRN.DATE_ <= CONVERT(DATETIME,@TARIH_BITIS, 101))
          AND (GLTRN.CANCELLED = 0)
          AND (GLTRN.TRCODE NOT IN ( 5 ))
          AND --
          (ACCOUNTCODE LIKE  LEFT(@GIDER_HESAP_KODU,1) +'%' AND ACCOUNTCODE<@GIDER_HESAP_KODU)
    GROUP BY ACCOUNTCODE,GLTRN.TRCURR,BRANCH
) AS T
WHERE
T.CREDIT-T.DEBIT>0   ) AS T2   
 ) AS YANSITMASABLONU ORDER BY TUR,YANSITMASABLONU.ISYERINR,YANSITMASABLONU.TRCURR