Rapport de consommation: ajouter les immo au réel
[auf_coda_additions.git] / db / procedures / auf_p_Balance_VerificationAUF.sql
CommitLineData
22ab1788
EMS
1USE [coda-prod]
2GO
3
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9-- EXEC auf_p_Balance_VerificationAUF 2009,9
10
11ALTER PROCEDURE [dbo].[auf_p_Balance_VerificationAUF]
12 @exercice int,
13 @periode int
14AS
15
16SET NOCOUNT ON
17
18SELECT
19 el1,
20 CASE
21 WHEN period = 0 THEN 'zero'
22 ELSE 'SupAzero'
23 END periode,
24 debit_value,
25 credit_value,
26 full_value
27INTO #cumul
28FROM oas_balance
29WHERE
30 cmpcode = 'AUF'
31 AND yr = @exercice
32 AND period BETWEEN 0 AND @periode
33 AND repbasis = 0
34 AND balcode ='REEL'
35 AND curcode ='EUR'
36 AND NOT (
37 debit_value = 0
38 AND credit_value = 0
39 AND full_value = 0
40 )
41
42SELECT
43 pcg.el1 PCG ,
44 pcg.sname Nom_PCG,
45 ISNULL(Avant.sommeAvant,0) soldeDebut,
46 ISNULL(Periode.sommeD,0) FluxDebit,
47 ISNULL(Periode.sommeC,0) FluxCredit
48FROM
49 (
50 SELECT code el1,sname
51 FROM oas_element
52 WHERE
53 cmpcode = 'AUF'
54 AND elmlevel = 1
55 AND deldate IS NULL
56 ) pcg
57 LEFT JOIN (
58 SELECT el1, SUM(full_value) sommeAvant
59 FROM #cumul
60 WHERE periode = 'zero'
61 GROUP BY el1
62 ) Avant
63 ON pcg.el1 = Avant.el1
64 LEFT JOIN (
65 SELECT el1,SUM(debit_value) sommeD, SUM(credit_value) sommeC
66 FROM #cumul
67 WHERE periode = 'SupAzero'
68 GROUP BY el1
69 ) Periode
70 ON pcg.el1 = Periode.el1
71ORDER BY pcg.el1
72GO