LOGO KAPANMAMIŞ FATURALAR

ALTER VIEW BM_MOBILSALES_KAPANMAMISFATURALAR AS

SELECT
INVFC.DATE_ TARIH,
CLCARD.CODE CARI_KOD,
CLCARD.DEFINITION_ CARI_AD,
 INVFC.FICHENO,
  INVFC.DOCODE,
  SUM(CASE LGMAIN.TRRATE WHEN 0 THEN LGMAIN.TOTAL ELSE LGMAIN.TOTAL*LGMAIN.TRRATE END) AS TUTAR
  -- , SUM(CASE LGMAIN.TRRATE WHEN 0 THEN LGMAIN.TOTAL ELSE LGMAIN.TOTAL*LGMAIN.TRRATE END * DATEDIFF(day,GETDATE(),LGMAIN.DATE_)) AS TOTALGDEB
 FROM
LG_119_01_PAYTRANS LGMAIN WITH(NOLOCK)               LEFT OUTER JOIN LG_119_01_INVOICE INVFC WITH(NOLOCK) ON (LGMAIN.FICHEREF  =  INVFC.LOGICALREF) LEFT OUTER JOIN LG_119_01_CLFLINE CTRNS WITH(NOLOCK) ON (LGMAIN.FICHEREF  =  CTRNS.LOGICALREF) LEFT OUTER JOIN LG_119_01_CSROLL RLFIC WITH(NOLOCK) ON (LGMAIN.FICHEREF  =  RLFIC.LOGICALREF) LEFT OUTER JOIN LG_119_01_BNFLINE BTRNS WITH(NOLOCK) ON (LGMAIN.FICHEREF  =  BTRNS.LOGICALREF) LEFT OUTER JOIN LG_119_01_KSLINES CASHTR WITH(NOLOCK) ON (LGMAIN.FICHEREF  =  CASHTR.LOGICALREF) LEFT OUTER JOIN LG_119_01_ORFICHE ORFIC WITH(NOLOCK) ON (LGMAIN.FICHEREF  =  ORFIC.LOGICALREF)
LEFT JOIN LG_119_CLCARD CLCARD ON CLCARD.LOGICALREF = LGMAIN.CARDREF
 WHERE
((LGMAIN.DATE_ >= CONVERT(dateTime, '1-1-2000', 101)) AND (LGMAIN.DATE_ <= CONVERT(dateTime, '12-31-2051', 101))) AND ((((LGMAIN.MODULENR=3 AND ((ORFIC.BRANCH BETWEEN 0 AND 13)))AND((ORFIC.DATE_ >= CONVERT(dateTime, '1-1-2000', 101)) AND (ORFIC.DATE_ <= CONVERT(dateTime, '12-31-2050', 101)))AND(ORFIC.CANCELLED=0))OR((LGMAIN.MODULENR=4 AND ((INVFC.BRANCH BETWEEN 0 AND 13)))AND((INVFC.DATE_ >= CONVERT(dateTime, '1-1-2000', 101)) AND (INVFC.DATE_ <= CONVERT(dateTime, '12-31-2050', 101)))AND(INVFC.CANCELLED=0))OR((LGMAIN.MODULENR IN (5,61,62) AND ((CTRNS.BRANCH BETWEEN 0 AND 13)))AND((CTRNS.DATE_ >= CONVERT(dateTime, '1-1-2000', 101)) AND (CTRNS.DATE_ <= CONVERT(dateTime, '12-31-2050', 101)))AND(CTRNS.CANCELLED=0))OR((LGMAIN.MODULENR=6 AND ((RLFIC.BRANCH BETWEEN 0 AND 13)))AND((RLFIC.DATE_ >= CONVERT(dateTime, '1-1-2000', 101)) AND (RLFIC.DATE_ <= CONVERT(dateTime, '12-31-2050', 101)))AND(RLFIC.CANCELLED=0))OR((LGMAIN.MODULENR=7 AND ((BTRNS.BRANCH BETWEEN 0 AND 13)))AND((BTRNS.DATE_ >= CONVERT(dateTime, '1-1-2000', 101)) AND (BTRNS.DATE_ <= CONVERT(dateTime, '12-31-2050', 101)))AND(BTRNS.CANCELLED=0))OR((LGMAIN.MODULENR=10 AND ((CASHTR.BRANCH BETWEEN 0 AND 13)))AND((CASHTR.DATE_ >= CONVERT(dateTime, '1-1-2000', 101)) AND (CASHTR.DATE_ <= CONVERT(dateTime, '12-31-2050', 101)))AND(CASHTR.CANCELLED=0)))) AND (LGMAIN.TOTAL-PAID > 0) AND (LGMAIN.TOTAL - LGMAIN.PAID > 0.00000001) AND (LGMAIN.SIGN = 0) AND (LGMAIN.CANCELLED = 0) AND (LGMAIN.PAIDINCASH <> 1)

AND CLCARD.CODE LIKE 'M%'


GROUP BY INVFC.DATE_, CODE,CLCARD.DEFINITION_, INVFC.FICHENO , INVFC.DOCODE