Modifications diverses
[auf_coda_additions.git] / db / vues / auf_v_web_ControleCleCpt.sql
1 USE [coda-prod]
2 GO
3
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8
9 ALTER VIEW [dbo].[auf_v_web_ControleCleCpt] AS
10 SELECT ETAT_PIECE,
11 ELEMENT2,
12 ELEMENT1,
13 ELEMENT3,
14 DATE_PIECE,
15 PERIODE,
16 CODE_DOCUMENT,
17 NO_PIECE,
18 NO_LIGNE,
19 DEVISE_DOCUMENT,
20 SOLDE_DEV_DOC,
21 SOLDE_DEV_STE AS solde_euro,
22 LIBELLE_LIGNE,
23 UTILISATEUR,
24 EXERCICE,
25 CONVERT(varchar(4), EXERCICE) + ETAT_PIECE AS exerciceEtat,
26 CONVERT(varchar(4), EXERCICE) + ETAT_PIECE + ELEMENT2 + ELEMENT3 + ELEMENT1 + DEVISE_DOCUMENT AS el2el3el1,
27 ELEMENT2 + ELEMENT3 AS el2el3
28 FROM
29 IMP_LIGNES,
30 (
31 SELECT h.yr, el1, el2, el3
32 FROM oas_dochead h, oas_docline l
33 WHERE
34 h.doccode = l.doccode
35 AND h.docnum = l.docnum
36 AND h.doccode <> 'G-CLOT-RESUL'
37 AND h.doccode <> 'G-CLOT-BILAN'
38 AND el1 NOT LIKE '5%'
39 AND el1 NOT LIKE '9%'
40 AND el2 LIKE '[0-9]%'
41 AND (
42 LEN(el3) = 0
43 OR LEFT(el2, LEN(el3)) <> el3
44 )
45 GROUP BY h.yr,el1,el2,el3
46 HAVING SUM(valuedoc) <> 0
47 ) errEl23
48 WHERE IMP_LIGNES.SOLDE_DEV_DOC <> 0 AND
49 IMP_LIGNES.EXERCICE = errEl23.yr AND
50 IMP_LIGNES.ELEMENT1 = errEl23.el1 AND
51 IMP_LIGNES.ELEMENT2 = errEl23.el2 AND
52 IMP_LIGNES.ELEMENT3 = errEl23.el3
53 AND IMP_LIGNES.EXERCICE >= 2011
54 GO