USE [TIGERDB]
GO
/****** Object: StoredProcedure [dbo].[usp_VadeDagilimliBorc021] Script Date: 04/08/2010 19:49:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_VadeDagilimliBorc021](@reportDate DATETIME,@groupCode nvarchar(100),@groupName nvarchar(200),
@cariCode nvarchar(100),@cariAdi nvarchar(200),@islemTipi nvarchar(10))
AS
BEGIN
DECLARE
@LOGICALREF int,@CARDREF int,@DATE datetime,@SIGN int,@TUTAR float,@ODENEN float,@KALAN_PARA float,@tmpLOGICALREF int,@ISLEM_TUTARI float
SET NOCOUNT ON;
CREATE TABLE #BorclarAlacaklar
(
LOGICALREF int,
CARDREF int,
DATE datetime,
SIGN int,
TUTAR float,
ODENEN float,
KALAN_PARA float
)
INSERT INTO #BorclarAlacaklar(LOGICALREF,CARDREF,DATE,SIGN,TUTAR,ODENEN,KALAN_PARA)
SELECT LOGICALREF,CARDREF,ISLEM_TARIHI,SIGN,TOTAL,ODENEN,KALAN_PARA FROM
(
SELECT
PY.LOGICALREF,PY.CARDREF,
CASE PY.MODULENR
WHEN 4 THEN (SELECT DATE_ FROM LG_021_01_INVOICE WITH (NOLOCK) WHERE LG_021_01_INVOICE.LOGICALREF=PY.FICHEREF)
WHEN 5 THEN (SELECT DATE_ FROM LG_021_01_CLFLINE WITH (NOLOCK) WHERE LG_021_01_CLFLINE.LOGICALREF=PY.FICHEREF)
WHEN 6 THEN (SELECT DATE_ FROM LG_021_01_CLFLINE WITH (NOLOCK) WHERE LG_021_01_CLFLINE.MODULENR=6 AND LG_021_01_CLFLINE.SOURCEFREF=PY.FICHEREF)
WHEN 7 THEN (SELECT DATE_ FROM LG_021_01_BNFLINE WITH (NOLOCK) WHERE LG_021_01_BNFLINE.LOGICALREF=PY.FICHEREF)
WHEN 10 THEN (SELECT DATE_ FROM LG_021_01_CLFLINE WITH (NOLOCK) WHERE LG_021_01_CLFLINE.LOGICALREF=PY.FICHEREF)
ELSE NULL END AS ISLEM_TARIHI,
PY.SIGN,
CASE WHEN PY.TRCURR<>0 THEN PY.TRRATE*PY.TOTAL ELSE PY.TOTAL END AS TOTAL,
0 AS ODENEN,
CASE WHEN PY.SIGN=0 THEN (CASE WHEN PY.TRCURR<>0 THEN PY.TRRATE*PY.TOTAL ELSE PY.TOTAL END) ELSE 0 END AS KALAN_PARA
FROM dbo.LG_021_01_PAYTRANS AS PY
INNER JOIN LG_021_CLCARD AS CL WITH (NOLOCK) ON PY.CARDREF=CL.LOGICALREF
INNER JOIN LG_021_CLCARD AS GR WITH (NOLOCK) ON CL.PARENTCLREF=GR.LOGICALREF
WHERE PY.CANCELLED=0
AND ((@groupCode IS NOT NULL AND GR.CODE LIKE @groupCode+'%') OR (@groupCode IS NULL AND GR.CODE LIKE '320%'))
AND (GR.DEFINITION_ LIKE @groupName+'%' OR @groupName IS NULL)
AND (CL.CODE LIKE @cariCode+'%' OR @cariCode IS NULL)
AND (CL.DEFINITION_ LIKE @cariAdi+'%' OR @cariAdi IS NULL)
-- AND (PY.CARDREF=@islemTipi OR @islemTipi IS NULL)
) AS TEMP
WHERE (ISLEM_TARIHI <= @reportDate)
ORDER BY SIGN DESC,ISLEM_TARIHI
DECLARE OdemeIslemi CURSOR FOR
SELECT LOGICALREF,CARDREF,DATE,SIGN,TUTAR,ODENEN,KALAN_PARA FROM #BorclarAlacaklar
OPEN OdemeIslemi
FETCH NEXT FROM OdemeIslemi INTO @LOGICALREF,@CARDREF,@DATE,@SIGN,@TUTAR,@ODENEN,@KALAN_PARA
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@SIGN=0 AND @KALAN_PARA>0) AND
(SELECT count(1) FROM #BorclarAlacaklar WHERE CARDREF=@CARDREF AND SIGN=1 AND TUTAR>ODENEN )>0
BEGIN
DECLARE Dagitim CURSOR FOR
SELECT LOGICALREF FROM #BorclarAlacaklar WHERE CARDREF=@CARDREF AND SIGN=1 AND TUTAR>ODENEN
OPEN Dagitim
FETCH NEXT FROM Dagitim INTO @tmpLOGICALREF
WHILE @@FETCH_STATUS = 0
BEGIN
IF @KALAN_PARA>0
BEGIN
SET @ISLEM_TUTARI=(SELECT CASE WHEN (TUTAR-ODENEN)>@KALAN_PARA THEN @KALAN_PARA
ELSE (TUTAR-ODENEN) END FROM #BorclarAlacaklar WHERE LOGICALREF=@tmpLOGICALREF)
yasak #BorclarAlacaklar SET ODENEN=ODENEN+@ISLEM_TUTARI WHERE LOGICALREF=@tmpLOGICALREF
yasak #BorclarAlacaklar SET KALAN_PARA=KALAN_PARA-@ISLEM_TUTARI WHERE LOGICALREF=@LOGICALREF
SET @KALAN_PARA=@KALAN_PARA-@ISLEM_TUTARI
END
FETCH NEXT FROM Dagitim INTO @tmpLOGICALREF
END
CLOSE Dagitim
DEALLOCATE Dagitim
END
FETCH NEXT FROM OdemeIslemi INTO @LOGICALREF,@CARDREF,@DATE,@SIGN,@TUTAR,@ODENEN,@KALAN_PARA
END
CLOSE OdemeIslemi
DEALLOCATE OdemeIslemi
SELECT SIGN,SUBE,[GRUP KODU] AS GRUP_KODU,[GRUP ADI] AS GRUP_ADI,
[CARI KODU] AS CARI_KODU,[CARI ADI] AS CARI_ADI,ISLEM_TIPI,[FIS_NO],[BELGE NO] AS BELGE_NO,ISLEM_TARIHI,VADE_TARIHI,
DATEADD(day,SANAL_VADE_GUN,ISLEM_TARIHI) AS SANAL_VADE,
CASE WHEN VADE_TARIHI>ISLEM_TARIHI THEN DATEDIFF(day,ISLEM_TARIHI,VADE_TARIHI) ELSE 0 END AS VADE_GUN,
CASE WHEN @reportDate>VADE_TARIHI THEN DATEDIFF(day,VADE_TARIHI,@reportDate) ELSE 0 END AS GECEN_GUN,
[SANAL_VADE_GUN],TUTAR,CASE WHEN SIGN=1 THEN (TUTAR-ODENEN) ELSE 0 END AS KALAN_BORC
FROM
(
SELECT PY.SIGN,(SELECT EMPLOYERNAME FROM dbo.L_CAPIFIRM WHERE NR=021) AS SUBE,
GR.CODE AS [GRUP KODU],GR.DEFINITION_ AS [GRUP ADI],
CL.CODE AS [CARI KODU],CL.DEFINITION_ AS [CARI ADI],
CASE PY.MODULENR
WHEN 4 THEN (SELECT FICHENO FROM LG_021_01_INVOICE WITH (NOLOCK) WHERE LG_021_01_INVOICE.LOGICALREF=PY.FICHEREF)
WHEN 5 THEN (SELECT TRANNO FROM LG_021_01_CLFLINE WITH (NOLOCK) WHERE LG_021_01_CLFLINE.LOGICALREF=PY.FICHEREF)
WHEN 6 THEN (SELECT TRANNO FROM LG_021_01_CLFLINE WITH (NOLOCK) WHERE LG_021_01_CLFLINE.MODULENR=6 AND LG_021_01_CLFLINE.SOURCEFREF=PY.FICHEREF)
WHEN 7 THEN (SELECT TRANNO FROM LG_021_01_BNFLINE WITH (NOLOCK) WHERE LG_021_01_BNFLINE.LOGICALREF=PY.FICHEREF)
WHEN 10 THEN (SELECT TRANNO FROM LG_021_01_CLFLINE WITH (NOLOCK) WHERE LG_021_01_CLFLINE.LOGICALREF=PY.FICHEREF)
ELSE NULL END AS FIS_NO,
CASE PY.MODULENR
WHEN 4 THEN (SELECT DOCODE FROM LG_021_01_INVOICE WITH (NOLOCK) WHERE LG_021_01_INVOICE.LOGICALREF=PY.FICHEREF)
WHEN 5 THEN (SELECT DOCODE FROM LG_021_01_CLFLINE WITH (NOLOCK) WHERE LG_021_01_CLFLINE.LOGICALREF=PY.FICHEREF)
WHEN 6 THEN (SELECT DOCODE FROM LG_021_01_CLFLINE WITH (NOLOCK) WHERE LG_021_01_CLFLINE.MODULENR=6 AND LG_021_01_CLFLINE.SOURCEFREF=PY.FICHEREF)
WHEN 7 THEN (SELECT DOCODE FROM LG_021_01_BNFLINE WITH (NOLOCK) WHERE LG_021_01_BNFLINE.LOGICALREF=PY.FICHEREF)
WHEN 10 THEN (SELECT DOCODE FROM LG_021_01_CLFLINE WITH (NOLOCK) WHERE LG_021_01_CLFLINE.LOGICALREF=PY.FICHEREF)
ELSE NULL END AS [BELGE NO],
BR.DATE AS ISLEM_TARIHI,
CASE PY.MODULENR
WHEN 4 THEN 'A'+CAST(PY.TRCODE AS VARCHAR(10))
WHEN 5 THEN 'B'+CAST(PY.TRCODE AS VARCHAR(10))
WHEN 6 THEN 'C'+CAST(PY.TRCODE AS VARCHAR(10))
WHEN 7 THEN 'D'+CAST(PY.TRCODE AS VARCHAR(10))
WHEN 10 THEN 'E'+CAST(PY.TRCODE AS VARCHAR(10))
ELSE NULL END AS ISLEM_TIPI ,
PY.DISCDUEDATE AS VADE_TARIHI,
CAST(ISNULL(CL.SPECODE5,0) AS INT) AS [SANAL_VADE_GUN],
CASE WHEN PY.TRCURR<>0 THEN PY.TRRATE*PY.TOTAL ELSE PY.TOTAL END AS TUTAR,BR.ODENEN AS ODENEN
FROM #BorclarAlacaklar AS BR
INNER JOIN LG_021_01_PAYTRANS AS PY WITH (NOLOCK) ON PY.LOGICALREF=BR.LOGICALREF
INNER JOIN LG_021_CLCARD AS CL WITH (NOLOCK) ON PY.CARDREF=CL.LOGICALREF
INNER JOIN LG_021_CLCARD AS GR WITH (NOLOCK) ON CL.PARENTCLREF=GR.LOGICALREF
WHERE
PY.CANCELLED=0
AND ((@groupCode IS NOT NULL AND GR.CODE LIKE @groupCode+'%') OR (@groupCode IS NULL AND GR.CODE LIKE '320.%'))
AND (GR.DEFINITION_ LIKE @groupName+'%' OR @groupName IS NULL)
AND (CL.CODE LIKE @cariCode+'%' OR @cariCode IS NULL)
AND (CL.DEFINITION_ LIKE @cariAdi+'%' OR @cariAdi IS NULL)
AND (BR.DATE <= @reportDate)
) AS TEMP
WHERE (ISLEM_TIPI=@islemTipi OR @islemTipi IS NULL)
ORDER BY SIGN DESC,ISLEM_TARIHI
DROP TABLE #BorclarAlacaklar
END
