1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
| USE [PORTEFEUILLE_COMPTA]
GO
/****** Objet : StoredProcedure [dbo].[VerifieSiDossierNonSolde;1] Date de génération du script : 04/18/2012 18:10:50 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[VerifieSiDossierNonSolde;1]
(
@NumeroDossier varchar(8),
@Periode datetime,
@DossierNonSolde bit OUTPUT
)
AS
-- Recherche des comptes clients non Soldés
-- 326. 316. 345. définitive
--DROP TABLE A2
SELECT RIGHT(Cpt,7) As Dossier, Cpt, SUM(Debit)-SUM(Credit) As Montant
INTO #COMPTESOLDE
FROM ECR_DEF
WHERE ((Cpt LIKE '326.%'+@NumeroDossier) OR (Cpt LIKE '316.%'+@NumeroDossier) OR (Cpt LIKE '345.%'+@NumeroDossier) OR (Cpt LIKE '341.%'+@NumeroDossier) OR (Cpt LIKE '384.%'+@NumeroDossier) OR (Cpt LIKE '336.%'+@NumeroDossier)) AND
Moi <= @Periode
GROUP BY Cpt
UNION ALL
-- 326. 316. 345. provisoire
SELECT RIGHT(Cpt,7) As Dossier, Cpt, SUM(Debit)-SUM(Credit) As Montant
FROM ECR_PROV
WHERE ((Cpt LIKE '326.%'+@NumeroDossier) OR (Cpt LIKE '316.%'+@NumeroDossier) OR (Cpt LIKE '345.%'+@NumeroDossier) OR (Cpt LIKE '341.%'+@NumeroDossier) OR (Cpt LIKE '384.%'+@NumeroDossier) OR (Cpt LIKE '336.%'+@NumeroDossier)) AND
Moi <= @Periode
GROUP BY Cpt
IF (@@ERROR <> 0)
BEGIN
RETURN
END
SELECT Dossier, SUM(Montant) AS Montant
INTO #CPTSOLDE
FROM #COMPTESOLDE
GROUP BY Dossier
DROP TABLE #COMPTESOLDE
DELETE FROM #CPTSOLDE
WHERE Montant <= 0
IF EXISTS (SELECT Dossier
FROM #CPTSOLDE
WHERE Dossier = @NumeroDossier)
SET @DossierNonSolde = 1
ELSE
SET @DossierNonSolde = 0
RETURN 0 |
Partager