Modifications diverses
[auf_coda_additions.git] / db / vues / auf_v_web_ControleCleCpt.sql
CommitLineData
22ab1788
EMS
1USE [coda-prod]
2GO
3
22ab1788
EMS
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
4e57e206 9ALTER VIEW [dbo].[auf_v_web_ControleCleCpt] AS
22ab1788
EMS
10SELECT 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
4e57e206
EMS
28FROM
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
22ab1788
EMS
48WHERE 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
22ab1788 54GO