Création d'une vue à partir de plusieurs bases avec des group by

**steven1964** · 04/04/2021, 10h34

Bonjour,

Je suis nouveau sur le forum, j'espère respecter les règles en vigueur et poster mes demandes au bon endroit ; j'ai besoin d'un petit coup de pouce pour créer une vue MS-SQL adaptée à ma problématique.

A priori ma requête est correcte, si je prends mes SELECT base par base j'ai bien le bon résultat qui remonte mais lorsque j'effectue un SELECT dans ma vue, pas de message d'erreur non plus mais pas de données remontées.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE VIEW PRJ_GRPBY
AS
-- Société 1
SELECT
  'S1' AS 'Société'
  SELECT
  CASE...
  MAX()...
  (SELECT ... FROM ...)
FROM SA.dbo.PRJ
LEFT OUTER JOIN...
INNER JOIN ...
GROUP BY PRJ.
UNION ALL
--Société 2
SELECT
  'S2' AS 'Société'
  SELECT
  CASE...
  MAX()...
  (SELECT ... FROM ...)
FROM SA.dbo.PRJ
LEFT OUTER JOIN...
INNER JOIN ...
GROUP BY PRJ.
--Société 3
SELECT
  'S2' AS 'Société'
  SELECT
  CASE...
  MAX()...
  (SELECT ... FROM ...)
FROM SA.dbo.PRJ
LEFT OUTER JOIN...
INNER JOIN ...
GROUP BY PRJ.
 
x10 sociétés
;

Je pense que la syntaxe n'est pas bonne ou erronée.

Si vous avez matière à m'aiguiller !

D'avance merci.

cdlt

Stéphane

**Wachter** · 05/04/2021, 13h21

Bonjour,

A priori, il manque un UNION ALL pour la société 3. Au lieu de tester séparément chaque SELECT, je te suggère de tester la requête complète. Si c'est OK, tu mets le code dans un CREATE VIEW.

**steven1964** · 05/04/2021, 16h00

Bonjour,

En fait, les UNION ALL sont bien présents dans ma requête et si je teste comme tu me le proposes la requête avant le CREATE VIEW ; l'exécution de la requête est réussie mais pas une ligne d'affichée.
Si je prends chaque SELECT indépendamment pour chaque base de données, j'ai bien des données en résultat.
J'ai 2 requêtes (avec et sans group by) de ce type sans erreur qui me donne une absence de résultat et je ne comprends pas pkoi !

Une autre piste ?

Amicalement

Stéphane

**SQLpro** · 05/04/2021, 20h06

Postez la vrai requête !

A +

**steven1964** · 05/04/2021, 21h03

Bonjour

Voici 3 requêtes : les 2 premières ne me donnent pas de résultat dans la vue alors que chaque SELECT fonctionne correctement et me renvoie bien des données ; la 3ème requete fonctionne correctement et me donne bien la somme des résultats des SELECT - si vous avez matière à m'aiguiller pour les 2 1ères requetes, ce sera sympa car je sèche !

Req 1 - avec un group by : pas de msg d'erreur - chaque SELECT fonctionne correctement société par société mais lancé ensemble, ca n'affiche aucune donnée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
CREATE VIEW G_PROJETS_GROUPBY
AS
--	'S1' AS 'Société'
SELECT 
  'S1' AS 'Société'
  ,FORMAT (Max(PRJ_DT_DEB),'dd/MM/yyy') AS 'Date création affaire'
  ,CONVERT(VARCHAR,YEAR(Max(PRJ_DT_DEB))) AS 'Année création affaire'
  ,'T' + CONVERT(VARCHAR,DATEPART([q], Max(PRJ_DT_DEB))) AS 'Trimestre création affaire'
  ,CASE WHEN CONVERT(VARCHAR,DATEPART([q], Max(PRJ_DT_DEB))) IN ('1', '2') THEN 'S1' WHEN CONVERT(VARCHAR,DATEPART([q], Max(PRJ_DT_DEB))) IN ('3', '4') THEN 'S2' END AS 'Semestre création affaire'
  ,CASE MONTH(Max(PRJ_DT_DEB))WHEN '1' THEN '01-Janv' WHEN '2' THEN '02-Fév.' WHEN '3' THEN '03-Mars' WHEN '4' THEN '04-Avr' WHEN '5' THEN '05-Mai' WHEN '6' THEN '06-Juin' WHEN '7' THEN '07-Juil' WHEN '8' THEN '08-Août' WHEN '9' THEN '09-Sept' WHEN '10' THEN '10-Oct' WHEN '11' THEN '11-Nov' WHEN '12' THEN '12-Déc' END AS 'Mois création affaire'
  ,RIGHT('0' + CONVERT(VARCHAR,MONTH(Max(PRJ_DT_DEB))), 2) + '/' + RIGHT('0' + CONVERT(VARCHAR,YEAR(Max(PRJ_DT_DEB))), 4) AS 'Période création affaire'
  ,PRJ_CODE AS 'Code affaire'
  ,Max(PRJ_LIB) AS 'Lbellé affaire'
  ,ISNULL(Max([t].PCF_CODE),'') AS 'Code client'
  ,ISNULL(Max([t].[PCF_RS]),'') AS 'Raison sociale client'
  ,ISNULL(Max([tf].[FAT_LIB]),'') AS 'Famille client'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_FRAIS_BUDGETES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'FM Budget à date'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_FRAIS_REALISES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'FM réalisé à date'
  ,ISNULL((SELECT Sum(PM.LPJ_PRIXTO) FROM PRJ_MO_BUDGETEE PM WHERE PM.PRJ_CODE = PRJ_CODE), 0) AS 'MO budget à date'
  ,ISNULL((SELECT Sum(PM.LPJ_PRIXTO) FROM PRJ_MO_REALISEE PM WHERE PM.PRJ_CODE = PRJ_CODE), 0) AS 'MO Réalisé à date'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_ACHATS_BUDGETES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'Achats Budget à date'
  ,ISNULL((SELECT Sum(CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN ISNULL(D.DOC_MT_HT, 0) ELSE ISNULL(D.DOC_MT_HT, 0) END) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'A' AND D.DOC_STYPE IN ('B', 'F', 'R', '1')), 0) AS 'Achats facturés à date'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_VENTES_BUDGETEES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'Vtes Budget à date'
  ,ISNULL((SELECT Sum(D.DOC_MT_HT) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('F', '1')), 0) 
  - ISNULL((SELECT Sum(D.DOC_MT_HT) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A', '0')), 0) AS 'Vtes facturées à date'
  ,ISNULL((SELECT Sum(CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN -ISNULL(D.DOC_MT_HT, 0) ELSE ISNULL(D.DOC_MT_HT, 0) END) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A', 'F', '0', '1')), 0) 
  - (ISNULL((SELECT Sum(ISNULL(D.DOC_MT_HT, 0)) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'A' AND D.DOC_STYPE IN ('B', 'F', 'R', '1')), 0) 
  + ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_FRAIS_REALISES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) 
  + ISNULL((SELECT Sum(PM.LPJ_PRIXTO) FROM PRJ_MO_REALISEE PM WHERE PM.PRJ_CODE = PRJ_CODE), 0)) AS 'Marge € sur factures à date'
  ,CASE
  WHEN 
  ISNULL((SELECT
     SUM(CASE 
          WHEN (D.DOC_STYPE) IN ('A','0','R') 
          THEN - ISNULL(D.DOC_MT_HT,0)
          ELSE ISNULL(D.DOC_MT_HT,0)
         END)
       FROM DOCUMENTS D 
       WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A','F','0','1')),0)=0
 THEN 0 
 ELSE
 (ISNULL((SELECT
  SUM(CASE 
       WHEN (D.DOC_STYPE) IN ('A','0','R') 
       THEN - ISNULL(D.DOC_MT_HT,0)
       ELSE ISNULL(D.DOC_MT_HT,0)
      END)
 FROM DOCUMENTS D 
 WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A','F','0','1')),0)
 -
 (ISNULL((SELECT
  SUM(ISNULL(D.DOC_MT_HT,0))
 FROM DOCUMENTS D 
 WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'A' AND D.DOC_STYPE IN ('B','F','R','1')),0)
 +
 ISNULL((SELECT
  SUM(PF.LPJ_PRIXTO)
 FROM PRJ_FRAIS_REALISES PF
 WHERE PF.PRJ_CODE = PRJ_CODE),0)
 +
 ISNULL((SELECT
  SUM(PM.LPJ_PRIXTO)
 FROM PRJ_MO_REALISEE PM 
 WHERE PM.PRJ_CODE = PRJ_CODE),0)))/
 (ISNULL((SELECT
  SUM(CASE 
   WHEN (D.DOC_STYPE) IN ('A','0','R') 
   THEN - ISNULL(D.DOC_MT_HT,0)
   ELSE ISNULL(D.DOC_MT_HT,0)
  END)
 FROM DOCUMENTS D 
 WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A','F','0','1')),0))*100 END AS '% Marge sur Affaire'
FROM
  S1.dbo.PROJETS
  LEFT OUTER JOIN [Tiers] [t] ON [t].PCF_CODE =PROJETS.PCF_CODE
  LEFT OUTER JOIN [Tiers_fam] [tf] ON [tf].[fat_code] = [t].[fat_code]
  LEFT OUTER JOIN [Tiers_sfam] [ts] ON [ts].[sft_code] = [t].[sft_code]
  LEFT OUTER JOIN [Services] [s] ON [s].SRV_CODE = [t].SRV_CODE
  LEFT OUTER JOIN [Divisions] [di] ON [di].DIV_CODE = [t].DIV_CODE
  LEFT OUTER JOIN [Tarifs] [tg] ON [tg].TAR_CODE = [t].TAR_CODE
  INNER JOIN [users] [u] ON [u].[usr_name] = PROJETS.PRJ_USRMAJ
  LEFT OUTER JOIN [personnel] [pu] ON [pu].SAL_CODE = [u].SAL_CODE
  LEFT OUTER JOIN [Representants] [r] ON [r].REP_CODE = [t].REP_CODE
GROUP BY
  PROJETS.PRJ_CODE
UNION ALL
-- 'S2' AS 'Société'
SELECT 
	'S2' AS 'Société'
  ,FORMAT (Max(PRJ_DT_DEB),'dd/MM/yyy') AS 'Date création affaire'
  ,CONVERT(VARCHAR,YEAR(Max(PRJ_DT_DEB))) AS 'Année création affaire'
  ,'T' + CONVERT(VARCHAR,DATEPART([q], Max(PRJ_DT_DEB))) AS 'Trimestre création affaire'
  ,CASE WHEN CONVERT(VARCHAR,DATEPART([q], Max(PRJ_DT_DEB))) IN ('1', '2') THEN 'S1' WHEN CONVERT(VARCHAR,DATEPART([q], Max(PRJ_DT_DEB))) IN ('3', '4') THEN 'S2' END AS 'Semestre création affaire'
  ,CASE MONTH(Max(PRJ_DT_DEB))WHEN '1' THEN '01-Janv' WHEN '2' THEN '02-Fév.' WHEN '3' THEN '03-Mars' WHEN '4' THEN '04-Avr' WHEN '5' THEN '05-Mai' WHEN '6' THEN '06-Juin' WHEN '7' THEN '07-Juil' WHEN '8' THEN '08-Août' WHEN '9' THEN '09-Sept' WHEN '10' THEN '10-Oct' WHEN '11' THEN '11-Nov' WHEN '12' THEN '12-Déc' END AS 'Mois création affaire'
  ,RIGHT('0' + CONVERT(VARCHAR,MONTH(Max(PRJ_DT_DEB))), 2) + '/' + RIGHT('0' + CONVERT(VARCHAR,YEAR(Max(PRJ_DT_DEB))), 4) AS 'Période création affaire'
  ,PRJ_CODE AS 'Code affaire'
  ,Max(PRJ_LIB) AS 'Lbellé affaire'
  ,ISNULL(Max([t].PCF_CODE),'') AS 'Code client'
  ,ISNULL(Max([t].[PCF_RS]),'') AS 'Raison sociale client'
  ,ISNULL(Max([tf].[FAT_LIB]),'') AS 'Famille client'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_FRAIS_BUDGETES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'FM Budget à date'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_FRAIS_REALISES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'FM réalisé à date'
  ,ISNULL((SELECT Sum(PM.LPJ_PRIXTO) FROM PRJ_MO_BUDGETEE PM WHERE PM.PRJ_CODE = PRJ_CODE), 0) AS 'MO budget à date'
  ,ISNULL((SELECT Sum(PM.LPJ_PRIXTO) FROM PRJ_MO_REALISEE PM WHERE PM.PRJ_CODE = PRJ_CODE), 0) AS 'MO Réalisé à date'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_ACHATS_BUDGETES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'Achats Budget à date'
  ,ISNULL((SELECT Sum(CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN ISNULL(D.DOC_MT_HT, 0) ELSE ISNULL(D.DOC_MT_HT, 0) END) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'A' AND D.DOC_STYPE IN ('B', 'F', 'R', '1')), 0) AS 'Achats facturés à date'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_VENTES_BUDGETEES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'Vtes Budget à date'
  ,ISNULL((SELECT Sum(D.DOC_MT_HT) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('F', '1')), 0) 
  - ISNULL((SELECT Sum(D.DOC_MT_HT) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A', '0')), 0) AS 'Vtes facturées à date'
  ,ISNULL((SELECT Sum(CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN -ISNULL(D.DOC_MT_HT, 0) ELSE ISNULL(D.DOC_MT_HT, 0) END) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A', 'F', '0', '1')), 0) 
  - (ISNULL((SELECT Sum(ISNULL(D.DOC_MT_HT, 0)) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'A' AND D.DOC_STYPE IN ('B', 'F', 'R', '1')), 0) 
  + ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_FRAIS_REALISES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) 
  + ISNULL((SELECT Sum(PM.LPJ_PRIXTO) FROM PRJ_MO_REALISEE PM WHERE PM.PRJ_CODE = PRJ_CODE), 0)) AS 'Marge € sur factures à date'
  ,CASE
  WHEN 
  ISNULL((SELECT
     SUM(CASE 
          WHEN (D.DOC_STYPE) IN ('A','0','R') 
          THEN - ISNULL(D.DOC_MT_HT,0)
          ELSE ISNULL(D.DOC_MT_HT,0)
         END)
       FROM DOCUMENTS D 
       WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A','F','0','1')),0)=0
 THEN 0 
 ELSE
 (ISNULL((SELECT
  SUM(CASE 
       WHEN (D.DOC_STYPE) IN ('A','0','R') 
       THEN - ISNULL(D.DOC_MT_HT,0)
       ELSE ISNULL(D.DOC_MT_HT,0)
      END)
 FROM DOCUMENTS D 
 WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A','F','0','1')),0)
 -
 (ISNULL((SELECT
  SUM(ISNULL(D.DOC_MT_HT,0))
 FROM DOCUMENTS D 
 WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'A' AND D.DOC_STYPE IN ('B','F','R','1')),0)
 +
 ISNULL((SELECT
  SUM(PF.LPJ_PRIXTO)
 FROM PRJ_FRAIS_REALISES PF
 WHERE PF.PRJ_CODE = PRJ_CODE),0)
 +
 ISNULL((SELECT
  SUM(PM.LPJ_PRIXTO)
 FROM PRJ_MO_REALISEE PM 
 WHERE PM.PRJ_CODE = PRJ_CODE),0)))/
 (ISNULL((SELECT
  SUM(CASE 
   WHEN (D.DOC_STYPE) IN ('A','0','R') 
   THEN - ISNULL(D.DOC_MT_HT,0)
   ELSE ISNULL(D.DOC_MT_HT,0)
  END)
 FROM DOCUMENTS D 
 WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A','F','0','1')),0))*100 END AS '% Marge sur Affaire'
FROM
  S2.dbo.PROJETS
  LEFT OUTER JOIN [Tiers] [t] ON [t].PCF_CODE =PROJETS.PCF_CODE
  LEFT OUTER JOIN [Tiers_fam] [tf] ON [tf].[fat_code] = [t].[fat_code]
  LEFT OUTER JOIN [Tiers_sfam] [ts] ON [ts].[sft_code] = [t].[sft_code]
  LEFT OUTER JOIN [Services] [s] ON [s].SRV_CODE = [t].SRV_CODE
  LEFT OUTER JOIN [Divisions] [di] ON [di].DIV_CODE = [t].DIV_CODE
  LEFT OUTER JOIN [Tarifs] [tg] ON [tg].TAR_CODE = [t].TAR_CODE
  INNER JOIN [users] [u] ON [u].[usr_name] = PROJETS.PRJ_USRMAJ
  LEFT OUTER JOIN [personnel] [pu] ON [pu].SAL_CODE = [u].SAL_CODE
  LEFT OUTER JOIN [Representants] [r] ON [r].REP_CODE = [t].REP_CODE
GROUP BY
  PROJETS.PRJ_CODE
UNION ALL
-- 'S3' AS 'Société'
SELECT 
	'S3' AS 'Société'
  ,FORMAT (Max(PRJ_DT_DEB),'dd/MM/yyy') AS 'Date création affaire'
  ,CONVERT(VARCHAR,YEAR(Max(PRJ_DT_DEB))) AS 'Année création affaire'
  ,'T' + CONVERT(VARCHAR,DATEPART([q], Max(PRJ_DT_DEB))) AS 'Trimestre création affaire'
  ,CASE WHEN CONVERT(VARCHAR,DATEPART([q], Max(PRJ_DT_DEB))) IN ('1', '2') THEN 'S1' WHEN CONVERT(VARCHAR,DATEPART([q], Max(PRJ_DT_DEB))) IN ('3', '4') THEN 'S2' END AS 'Semestre création affaire'
  ,CASE MONTH(Max(PRJ_DT_DEB))WHEN '1' THEN '01-Janv' WHEN '2' THEN '02-Fév.' WHEN '3' THEN '03-Mars' WHEN '4' THEN '04-Avr' WHEN '5' THEN '05-Mai' WHEN '6' THEN '06-Juin' WHEN '7' THEN '07-Juil' WHEN '8' THEN '08-Août' WHEN '9' THEN '09-Sept' WHEN '10' THEN '10-Oct' WHEN '11' THEN '11-Nov' WHEN '12' THEN '12-Déc' END AS 'Mois création affaire'
  ,RIGHT('0' + CONVERT(VARCHAR,MONTH(Max(PRJ_DT_DEB))), 2) + '/' + RIGHT('0' + CONVERT(VARCHAR,YEAR(Max(PRJ_DT_DEB))), 4) AS 'Période création affaire'
  ,PRJ_CODE AS 'Code affaire'
  ,Max(PRJ_LIB) AS 'Lbellé affaire'
  ,ISNULL(Max([t].PCF_CODE),'') AS 'Code client'
  ,ISNULL(Max([t].[PCF_RS]),'') AS 'Raison sociale client'
  ,ISNULL(Max([tf].[FAT_LIB]),'') AS 'Famille client'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_FRAIS_BUDGETES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'FM Budget à date'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_FRAIS_REALISES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'FM réalisé à date'
  ,ISNULL((SELECT Sum(PM.LPJ_PRIXTO) FROM PRJ_MO_BUDGETEE PM WHERE PM.PRJ_CODE = PRJ_CODE), 0) AS 'MO budget à date'
  ,ISNULL((SELECT Sum(PM.LPJ_PRIXTO) FROM PRJ_MO_REALISEE PM WHERE PM.PRJ_CODE = PRJ_CODE), 0) AS 'MO Réalisé à date'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_ACHATS_BUDGETES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'Achats Budget à date'
  ,ISNULL((SELECT Sum(CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN ISNULL(D.DOC_MT_HT, 0) ELSE ISNULL(D.DOC_MT_HT, 0) END) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'A' AND D.DOC_STYPE IN ('B', 'F', 'R', '1')), 0) AS 'Achats facturés à date'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_VENTES_BUDGETEES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'Vtes Budget à date'
  ,ISNULL((SELECT Sum(D.DOC_MT_HT) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('F', '1')), 0) 
  - ISNULL((SELECT Sum(D.DOC_MT_HT) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A', '0')), 0) AS 'Vtes facturées à date'
  ,ISNULL((SELECT Sum(CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN -ISNULL(D.DOC_MT_HT, 0) ELSE ISNULL(D.DOC_MT_HT, 0) END) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A', 'F', '0', '1')), 0) 
  - (ISNULL((SELECT Sum(ISNULL(D.DOC_MT_HT, 0)) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'A' AND D.DOC_STYPE IN ('B', 'F', 'R', '1')), 0) 
  + ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_FRAIS_REALISES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) 
  + ISNULL((SELECT Sum(PM.LPJ_PRIXTO) FROM PRJ_MO_REALISEE PM WHERE PM.PRJ_CODE = PRJ_CODE), 0)) AS 'Marge € sur factures à date'
  ,CASE
  WHEN 
  ISNULL((SELECT
     SUM(CASE 
          WHEN (D.DOC_STYPE) IN ('A','0','R') 
          THEN - ISNULL(D.DOC_MT_HT,0)
          ELSE ISNULL(D.DOC_MT_HT,0)
         END)
       FROM DOCUMENTS D 
       WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A','F','0','1')),0)=0
 THEN 0 
 ELSE
 (ISNULL((SELECT
  SUM(CASE 
       WHEN (D.DOC_STYPE) IN ('A','0','R') 
       THEN - ISNULL(D.DOC_MT_HT,0)
       ELSE ISNULL(D.DOC_MT_HT,0)
      END)
 FROM DOCUMENTS D 
 WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A','F','0','1')),0)
 -
 (ISNULL((SELECT
  SUM(ISNULL(D.DOC_MT_HT,0))
 FROM DOCUMENTS D 
 WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'A' AND D.DOC_STYPE IN ('B','F','R','1')),0)
 +
 ISNULL((SELECT
  SUM(PF.LPJ_PRIXTO)
 FROM PRJ_FRAIS_REALISES PF
 WHERE PF.PRJ_CODE = PRJ_CODE),0)
 +
 ISNULL((SELECT
  SUM(PM.LPJ_PRIXTO)
 FROM PRJ_MO_REALISEE PM 
 WHERE PM.PRJ_CODE = PRJ_CODE),0)))/
 (ISNULL((SELECT
  SUM(CASE 
   WHEN (D.DOC_STYPE) IN ('A','0','R') 
   THEN - ISNULL(D.DOC_MT_HT,0)
   ELSE ISNULL(D.DOC_MT_HT,0)
  END)
 FROM DOCUMENTS D 
 WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A','F','0','1')),0))*100 END AS '% Marge sur Affaire'
FROM
  S3_FC.dbo.PROJETS
  LEFT OUTER JOIN [Tiers] [t] ON [t].PCF_CODE =PROJETS.PCF_CODE
  LEFT OUTER JOIN [Tiers_fam] [tf] ON [tf].[fat_code] = [t].[fat_code]
  LEFT OUTER JOIN [Tiers_sfam] [ts] ON [ts].[sft_code] = [t].[sft_code]
  LEFT OUTER JOIN [Services] [s] ON [s].SRV_CODE = [t].SRV_CODE
  LEFT OUTER JOIN [Divisions] [di] ON [di].DIV_CODE = [t].DIV_CODE
  LEFT OUTER JOIN [Tarifs] [tg] ON [tg].TAR_CODE = [t].TAR_CODE
  INNER JOIN [users] [u] ON [u].[usr_name] = PROJETS.PRJ_USRMAJ
  LEFT OUTER JOIN [personnel] [pu] ON [pu].SAL_CODE = [u].SAL_CODE
  LEFT OUTER JOIN [Representants] [r] ON [r].REP_CODE = [t].REP_CODE
GROUP BY
  PROJETS.PRJ_CODE
UNION ALL
-- 'S4' AS 'Société'
SELECT 
	'S4' AS 'Société'
  ,FORMAT (Max(PRJ_DT_DEB),'dd/MM/yyy') AS 'Date création affaire'
  ,CONVERT(VARCHAR,YEAR(Max(PRJ_DT_DEB))) AS 'Année création affaire'
  ,'T' + CONVERT(VARCHAR,DATEPART([q], Max(PRJ_DT_DEB))) AS 'Trimestre création affaire'
  ,CASE WHEN CONVERT(VARCHAR,DATEPART([q], Max(PRJ_DT_DEB))) IN ('1', '2') THEN 'S1' WHEN CONVERT(VARCHAR,DATEPART([q], Max(PRJ_DT_DEB))) IN ('3', '4') THEN 'S2' END AS 'Semestre création affaire'
  ,CASE MONTH(Max(PRJ_DT_DEB))WHEN '1' THEN '01-Janv' WHEN '2' THEN '02-Fév.' WHEN '3' THEN '03-Mars' WHEN '4' THEN '04-Avr' WHEN '5' THEN '05-Mai' WHEN '6' THEN '06-Juin' WHEN '7' THEN '07-Juil' WHEN '8' THEN '08-Août' WHEN '9' THEN '09-Sept' WHEN '10' THEN '10-Oct' WHEN '11' THEN '11-Nov' WHEN '12' THEN '12-Déc' END AS 'Mois création affaire'
  ,RIGHT('0' + CONVERT(VARCHAR,MONTH(Max(PRJ_DT_DEB))), 2) + '/' + RIGHT('0' + CONVERT(VARCHAR,YEAR(Max(PRJ_DT_DEB))), 4) AS 'Période création affaire'
  ,PRJ_CODE AS 'Code affaire'
  ,Max(PRJ_LIB) AS 'Lbellé affaire'
  ,ISNULL(Max([t].PCF_CODE),'') AS 'Code client'
  ,ISNULL(Max([t].[PCF_RS]),'') AS 'Raison sociale client'
  ,ISNULL(Max([tf].[FAT_LIB]),'') AS 'Famille client'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_FRAIS_BUDGETES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'FM Budget à date'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_FRAIS_REALISES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'FM réalisé à date'
  ,ISNULL((SELECT Sum(PM.LPJ_PRIXTO) FROM PRJ_MO_BUDGETEE PM WHERE PM.PRJ_CODE = PRJ_CODE), 0) AS 'MO budget à date'
  ,ISNULL((SELECT Sum(PM.LPJ_PRIXTO) FROM PRJ_MO_REALISEE PM WHERE PM.PRJ_CODE = PRJ_CODE), 0) AS 'MO Réalisé à date'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_ACHATS_BUDGETES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'Achats Budget à date'
  ,ISNULL((SELECT Sum(CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN ISNULL(D.DOC_MT_HT, 0) ELSE ISNULL(D.DOC_MT_HT, 0) END) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'A' AND D.DOC_STYPE IN ('B', 'F', 'R', '1')), 0) AS 'Achats facturés à date'
  ,ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_VENTES_BUDGETEES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) AS 'Vtes Budget à date'
  ,ISNULL((SELECT Sum(D.DOC_MT_HT) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('F', '1')), 0) 
  - ISNULL((SELECT Sum(D.DOC_MT_HT) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A', '0')), 0) AS 'Vtes facturées à date'
  ,ISNULL((SELECT Sum(CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN -ISNULL(D.DOC_MT_HT, 0) ELSE ISNULL(D.DOC_MT_HT, 0) END) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A', 'F', '0', '1')), 0) 
  - (ISNULL((SELECT Sum(ISNULL(D.DOC_MT_HT, 0)) FROM DOCUMENTS D WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'A' AND D.DOC_STYPE IN ('B', 'F', 'R', '1')), 0) 
  + ISNULL((SELECT Sum(PF.LPJ_PRIXTO) FROM PRJ_FRAIS_REALISES PF WHERE PF.PRJ_CODE = PRJ_CODE), 0) 
  + ISNULL((SELECT Sum(PM.LPJ_PRIXTO) FROM PRJ_MO_REALISEE PM WHERE PM.PRJ_CODE = PRJ_CODE), 0)) AS 'Marge € sur factures à date'
  ,CASE
  WHEN 
  ISNULL((SELECT
     SUM(CASE 
          WHEN (D.DOC_STYPE) IN ('A','0','R') 
          THEN - ISNULL(D.DOC_MT_HT,0)
          ELSE ISNULL(D.DOC_MT_HT,0)
         END)
       FROM DOCUMENTS D 
       WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A','F','0','1')),0)=0
 THEN 0 
 ELSE
 (ISNULL((SELECT
  SUM(CASE 
       WHEN (D.DOC_STYPE) IN ('A','0','R') 
       THEN - ISNULL(D.DOC_MT_HT,0)
       ELSE ISNULL(D.DOC_MT_HT,0)
      END)
 FROM DOCUMENTS D 
 WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A','F','0','1')),0)
 -
 (ISNULL((SELECT
  SUM(ISNULL(D.DOC_MT_HT,0))
 FROM DOCUMENTS D 
 WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'A' AND D.DOC_STYPE IN ('B','F','R','1')),0)
 +
 ISNULL((SELECT
  SUM(PF.LPJ_PRIXTO)
 FROM PRJ_FRAIS_REALISES PF
 WHERE PF.PRJ_CODE = PRJ_CODE),0)
 +
 ISNULL((SELECT
  SUM(PM.LPJ_PRIXTO)
 FROM PRJ_MO_REALISEE PM 
 WHERE PM.PRJ_CODE = PRJ_CODE),0)))/
 (ISNULL((SELECT
  SUM(CASE 
   WHEN (D.DOC_STYPE) IN ('A','0','R') 
   THEN - ISNULL(D.DOC_MT_HT,0)
   ELSE ISNULL(D.DOC_MT_HT,0)
  END)
 FROM DOCUMENTS D 
 WHERE D.PRJ_CODE = PRJ_CODE AND D.DOC_TYPE = 'V' AND D.DOC_STYPE IN ('A','F','0','1')),0))*100 END AS '% Marge sur Affaire'
FROM
  S4.dbo.PROJETS
  LEFT OUTER JOIN [Tiers] [t] ON [t].PCF_CODE =PROJETS.PCF_CODE
  LEFT OUTER JOIN [Tiers_fam] [tf] ON [tf].[fat_code] = [t].[fat_code]
  LEFT OUTER JOIN [Tiers_sfam] [ts] ON [ts].[sft_code] = [t].[sft_code]
  LEFT OUTER JOIN [Services] [s] ON [s].SRV_CODE = [t].SRV_CODE
  LEFT OUTER JOIN [Divisions] [di] ON [di].DIV_CODE = [t].DIV_CODE
  LEFT OUTER JOIN [Tarifs] [tg] ON [tg].TAR_CODE = [t].TAR_CODE
  INNER JOIN [users] [u] ON [u].[usr_name] = PROJETS.PRJ_USRMAJ
  LEFT OUTER JOIN [personnel] [pu] ON [pu].SAL_CODE = [u].SAL_CODE
  LEFT OUTER JOIN [Representants] [r] ON [r].REP_CODE = [t].REP_CODE
GROUP BY
  PROJETS.PRJ_CODE;

Req 2 - autre req sans GROUPE BY : pas de msg d'erreur - chaque SELECT fonctionne correctement société par société mais lancé ensemble, ca n'affiche aucune donnée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
CREATE VIEW G_LIGNES
AS
--	'S1' AS 'Société'
SELECT 
  'S1' AS 'Société'
  ,FORMAT (D.DOC_DATE,'dd/MM/yyy') AS 'Doc_Date'
  ,CONVERT(VARCHAR,YEAR(D.DOC_DATE)) AS 'Doc_Année'
  ,'T' + CONVERT(VARCHAR,DATEPART([q], D.DOC_DATE)) AS 'Doc_Trimestre'
  ,CASE WHEN CONVERT(VARCHAR,DATEPART([q], D.DOC_DATE)) IN ('1', '2') THEN 'S1' WHEN CONVERT(VARCHAR,DATEPART([q], D.DOC_DATE)) IN ('3', '4') THEN 'S2' END AS 'Doc_Semestre'
  ,CASE MONTH(D.DOC_DATE)WHEN '1' THEN '01-Janv' WHEN '2' THEN '02-Fév' WHEN '3' THEN '03-Mars' WHEN '4' THEN '04-Avr' WHEN '5' THEN '05-Mai' WHEN '6' THEN '06-Juin' WHEN '7' THEN '07-Juil' WHEN '8' THEN '08-Août' WHEN '9' THEN '09-Sept' WHEN '10' THEN '10-Oct' WHEN '11' THEN '11-Nov' WHEN '12' THEN '12-Déc' END AS 'Doc_Mois'
  ,RIGHT('0' + CONVERT(VARCHAR,YEAR(D.DOC_DATE)), 4) + '/' + RIGHT('0' + CONVERT(VARCHAR,DATEPART([ww], D.DOC_DATE)), 2) AS 'Doc_Semaine'
  ,RIGHT('0' + CONVERT(VARCHAR,YEAR(D.DOC_DATE)), 4) + '/' + RIGHT('0' + CONVERT(VARCHAR,MONTH(D.DOC_DATE)), 2) AS 'Doc_Période'
  ,D.DOC_PIECE AS 'Doc_N° Pièce'
  ,D.DOC_REFPCF AS 'Doc_Réf'
  ,CASE (D.DOC_TYPE) WHEN 'A' THEN 'Achat' WHEN 'V' THEN 'Ventes' END AS 'Doc_Type'
  ,CASE (D.DOC_TYPE) WHEN 'V' THEN CASE (D.DOC_STYPE)WHEN 'P' THEN 'Pro-forma' WHEN 'D' THEN 'Devis' WHEN 'C' THEN 'Commandes' WHEN 'B' THEN 'Bons de Livraison' WHEN 'R' THEN 'Bons de Retour' WHEN 'F' THEN 'Factures' WHEN '1' THEN 'Factures Financières' WHEN 'A' THEN 'Avoirs' WHEN '0' THEN 'Avoirs Financiers' END WHEN 'A' THEN CASE (D.DOC_STYPE)WHEN 'D' THEN 'Demandes de Prix' WHEN 'C' THEN 'Commandes' WHEN 'B' THEN 'Bons de Réceptions' WHEN 'R' THEN 'Bons de Retour' WHEN 'F' THEN 'Factures' WHEN '1' THEN 'Factures Financières' WHEN 'A' THEN 'Avoirs' WHEN '0' THEN 'Avoirs Financiers' END END AS 'Doc_Sous type'
  ,CASE (D.DOC_ETAT) WHEN 'E' THEN 'Non signé' WHEN 'I' THEN 'Non signé' WHEN 'S' THEN 'Signé' WHEN 'C' THEN 'Comptabilisé' WHEN 'T' THEN 'Transféré partiellement' WHEN 'A' THEN 'Archivé' END AS 'Doc_Etat'
  ,D.DEV_CODE AS 'Doc_Code Devise'
  ,DE.DEV_LIB AS 'Doc_Lib Devise'
  ,D.DOC_TX_DEV AS 'Doc_Cours Devise'
  ,ISNULL(P.PRJ_CODE, '') AS 'Doc_Code Aff'
  ,ISNULL(P.PRJ_LIB, '') AS 'Doc_Lib Aff'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'PRJ' AND TA.TBL_CODE = P.PRJ_ETAT), '') AS 'Aff_Etat'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'GP1' AND TA.TBL_CODE = P.PRJ_GROUP1), '') AS 'Aff_Cat'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'GP2' AND TA.TBL_CODE = P.PRJ_GROUP2), '') AS 'Aff_Sous-Cat'
  ,ISNULL(A.ART_CODE, '') AS 'Art_Code'
  ,ISNULL(A.ART_LIB, '') AS 'Art_Lib'
  ,ISNULL(AF.FAR_LIB, '') AS 'Art_Fam'
  ,ISNULL(SF.SFA_LIB, '') AS 'Art_Sous-Fam'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA1' AND TA.TBL_CODE = A.FA1_CODE), '') AS 'Art_C1'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA2' AND TA.TBL_CODE = A.FA2_CODE), '') AS 'Art_C2'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA3' AND TA.TBL_CODE = A.FA3_CODE), '') AS 'Art_C3'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA4' AND TA.TBL_CODE = A.FA4_CODE), '') AS 'Art_C4'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA5' AND TA.TBL_CODE = A.FA5_CODE), '') AS 'Art_C5'
  ,CASE ISNULL(A.ART_DORT, 0)WHEN 1 THEN 'Non actif' WHEN 0 THEN 'Actif' END AS 'Art_Sommeil'
  ,CASE ISNULL(A.ART_NSTAT, 0)WHEN 1 THEN 'Oui' WHEN 0 THEN 'Non' END AS 'Art_Hors Stat'
  ,CASE ISNULL(A.ART_CONTRM, 0)WHEN 1 THEN 'Oui' WHEN 0 THEN 'Non' END AS 'Art_Contremarque'
  ,CASE (A.ART_CATEG) WHEN 'F' THEN 'Produits Finis' WHEN 'S' THEN 'Produits Semi-Finis' WHEN 'M' THEN 'Matières 1ères' WHEN 'O' THEN 'Main d''oeuvre' WHEN 'T' THEN 'Sous-traitance' WHEN 'A' THEN 'Autres' ELSE '' END AS 'Art_Catégorie'
  ,CASE (A.ART_TYPE)WHEN 'P' THEN 'Pièce' WHEN 'C' THEN 'Consigne' WHEN 'N' THEN 'Nomenclature' WHEN 'F' THEN 'Forfait' WHEN 'V' THEN 'Service' ELSE '' END AS 'Art_Type'
  ,CASE (T.PCF_TYPE)WHEN 'C' THEN 'Clients' WHEN 'F' THEN 'Fournisseurs' WHEN 'P' THEN 'Prospects' END AS 'Tiers_Type'
  ,T.PCF_CODE AS 'Tiers_Code'
  ,T.PCF_RS AS 'Tiers_R.sociale'
  ,ISNULL(TF.FAT_LIB, '') AS 'Tiers_Fam'
  ,ISNULL(TS.SFT_LIB, '') AS 'Tiers_Sous-Fam'
  ,ISNULL(S.SRV_NOM, '') AS 'Services'
  ,ISNULL(DI.DIV_LIB, '') AS 'Divisions'
  ,CASE ISNULL(T.PCF_DORT, 0) WHEN 1 THEN 'Non Actif' WHEN 0 THEN 'Actif' END AS 'Tiers_Sommeil'
  ,CASE ISNULL(T.PCF_BLOQUE, 0)WHEN 1 THEN 'Oui' WHEN 0 THEN 'Non' END AS 'Tiers_Bloqué'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC1' AND TA.TBL_CODE = T.FC1_CODE), '') AS 'Tiers_C1'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC2' AND TA.TBL_CODE = T.FC2_CODE), '') AS 'Tiers_C2'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC3' AND TA.TBL_CODE = T.FC3_CODE), '') AS 'Tiers_C3'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC4' AND TA.TBL_CODE = T.FC4_CODE), '') AS 'Tiers_C4'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC5' AND TA.TBL_CODE = T.FC5_CODE), '') AS 'Tiers_C5'
  ,ISNULL(TG.TAR_LIB, '') AS 'Grille de Tarifs'
  ,ISNULL(TP.PCF_RS, '') AS 'Tiers_Payeur'
  ,ISNULL(R.REP_PRENOM, '') + ' ' + ISNULL(R.REP_NOM, '') AS 'Commercial'
  ,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN -L.LIG_QTE ELSE L.LIG_QTE END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN L.LIG_QTE ELSE -L.LIG_QTE END END AS 'Qte'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN L.LIG_COUT WHEN 'A' THEN L.[LIG_COUT] * -1 END AS 'Prix de Revient'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) END END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * -1 END END END AS 'CA HT Brut'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) END END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * -1 END END END AS 'Remise'
  ,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END END END AS 'CA HT €'
  ,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END END AS 'CAHT_Devise'
  --CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END WHEN (D.DOC_STYPE) = '1' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) END) END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END WHEN (D.DOC_STYPE) = '1' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE
  --             WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) END) END END AS 'Marge €'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 WHEN (D.DOC_STYPE) = '1' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) END) END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) WHEN (D.DOC_STYPE) = '1' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) END) END END AS 'Marge Devise'
FROM
  S1.dbo.LIGNES L
  INNER JOIN DOCUMENTS D ON D.DOC_NUMERO = L.DOC_NUMERO
  LEFT OUTER JOIN DEVISES DE ON DE.DEV_CODE = D.DEV_CODE
  LEFT OUTER JOIN ARTICLES A ON A.ART_CODE = L.ART_CODE
  LEFT OUTER JOIN ART_FAM AF ON AF.FAR_CODE = A.FAR_CODE
  LEFT OUTER JOIN ART_SFAM SF ON SF.SFA_CODE = A.SFA_CODE
  LEFT OUTER JOIN PROJETS P ON P.PRJ_CODE = L.PRJ_CODE
  INNER JOIN TIERS T ON T.PCF_CODE = D.PCF_CODE
  LEFT OUTER JOIN TIERS_FAM TF ON TF.FAT_CODE = T.FAT_CODE
  LEFT OUTER JOIN TIERS_SFAM TS ON TS.SFT_CODE = T.SFT_CODE
  LEFT OUTER JOIN SERVICES S ON S.SRV_CODE = T.SRV_CODE
  LEFT OUTER JOIN DIVISIONS DI ON DI.DIV_CODE = T.DIV_CODE
  LEFT OUTER JOIN TARIFS TG ON TG.TAR_CODE = T.TAR_CODE
  LEFT OUTER JOIN TIERS TP ON TP.PCF_CODE = T.PCF_PAYEUR
  LEFT OUTER JOIN REPRESENTANTS R ON R.REP_CODE = D.REP_CODE
WHERE
  D.DOC_TYPE IN ('A', 'V') AND
  L.LIG_TYPE NOT IN ('x', 'X', 'T')
  UNION ALL
-- 'S2' AS 'Société'
SELECT 
	'S2' AS 'Société'
  ,FORMAT (D.DOC_DATE,'dd/MM/yyy') AS 'Doc_Date'
  ,CONVERT(VARCHAR,YEAR(D.DOC_DATE)) AS 'Doc_Année'
  ,'T' + CONVERT(VARCHAR,DATEPART([q], D.DOC_DATE)) AS 'Doc_Trimestre'
  ,CASE WHEN CONVERT(VARCHAR,DATEPART([q], D.DOC_DATE)) IN ('1', '2') THEN 'S1' WHEN CONVERT(VARCHAR,DATEPART([q], D.DOC_DATE)) IN ('3', '4') THEN 'S2' END AS 'Doc_Semestre'
  ,CASE MONTH(D.DOC_DATE)WHEN '1' THEN '01-Janv' WHEN '2' THEN '02-Fév' WHEN '3' THEN '03-Mars' WHEN '4' THEN '04-Avr' WHEN '5' THEN '05-Mai' WHEN '6' THEN '06-Juin' WHEN '7' THEN '07-Juil' WHEN '8' THEN '08-Août' WHEN '9' THEN '09-Sept' WHEN '10' THEN '10-Oct' WHEN '11' THEN '11-Nov' WHEN '12' THEN '12-Déc' END AS 'Doc_Mois'
  ,RIGHT('0' + CONVERT(VARCHAR,YEAR(D.DOC_DATE)), 4) + '/' + RIGHT('0' + CONVERT(VARCHAR,DATEPART([ww], D.DOC_DATE)), 2) AS 'Doc_Semaine'
  ,RIGHT('0' + CONVERT(VARCHAR,YEAR(D.DOC_DATE)), 4) + '/' + RIGHT('0' + CONVERT(VARCHAR,MONTH(D.DOC_DATE)), 2) AS 'Doc_Période'
  ,D.DOC_PIECE AS 'Doc_N° Pièce'
  ,D.DOC_REFPCF AS 'Doc_Réf'
  ,CASE (D.DOC_TYPE) WHEN 'A' THEN 'Achat' WHEN 'V' THEN 'Ventes' END AS 'Doc_Type'
  ,CASE (D.DOC_TYPE) WHEN 'V' THEN CASE (D.DOC_STYPE)WHEN 'P' THEN 'Pro-forma' WHEN 'D' THEN 'Devis' WHEN 'C' THEN 'Commandes' WHEN 'B' THEN 'Bons de Livraison' WHEN 'R' THEN 'Bons de Retour' WHEN 'F' THEN 'Factures' WHEN '1' THEN 'Factures Financières' WHEN 'A' THEN 'Avoirs' WHEN '0' THEN 'Avoirs Financiers' END WHEN 'A' THEN CASE (D.DOC_STYPE)WHEN 'D' THEN 'Demandes de Prix' WHEN 'C' THEN 'Commandes' WHEN 'B' THEN 'Bons de Réceptions' WHEN 'R' THEN 'Bons de Retour' WHEN 'F' THEN 'Factures' WHEN '1' THEN 'Factures Financières' WHEN 'A' THEN 'Avoirs' WHEN '0' THEN 'Avoirs Financiers' END END AS 'Doc_Sous type'
  ,CASE (D.DOC_ETAT) WHEN 'E' THEN 'Non signé' WHEN 'I' THEN 'Non signé' WHEN 'S' THEN 'Signé' WHEN 'C' THEN 'Comptabilisé' WHEN 'T' THEN 'Transféré partiellement' WHEN 'A' THEN 'Archivé' END AS 'Doc_Etat'
  ,D.DEV_CODE AS 'Doc_Code Devise'
  ,DE.DEV_LIB AS 'Doc_Lib Devise'
  ,D.DOC_TX_DEV AS 'Doc_Cours Devise'
  ,ISNULL(P.PRJ_CODE, '') AS 'Doc_Code Aff'
  ,ISNULL(P.PRJ_LIB, '') AS 'Doc_Lib Aff'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'PRJ' AND TA.TBL_CODE = P.PRJ_ETAT), '') AS 'Aff_Etat'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'GP1' AND TA.TBL_CODE = P.PRJ_GROUP1), '') AS 'Aff_Cat'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'GP2' AND TA.TBL_CODE = P.PRJ_GROUP2), '') AS 'Aff_Sous-Cat'
  ,ISNULL(A.ART_CODE, '') AS 'Art_Code'
  ,ISNULL(A.ART_LIB, '') AS 'Art_Lib'
  ,ISNULL(AF.FAR_LIB, '') AS 'Art_Fam'
  ,ISNULL(SF.SFA_LIB, '') AS 'Art_Sous-Fam'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA1' AND TA.TBL_CODE = A.FA1_CODE), '') AS 'Art_C1'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA2' AND TA.TBL_CODE = A.FA2_CODE), '') AS 'Art_C2'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA3' AND TA.TBL_CODE = A.FA3_CODE), '') AS 'Art_C3'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA4' AND TA.TBL_CODE = A.FA4_CODE), '') AS 'Art_C4'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA5' AND TA.TBL_CODE = A.FA5_CODE), '') AS 'Art_C5'
  ,CASE ISNULL(A.ART_DORT, 0)WHEN 1 THEN 'Non actif' WHEN 0 THEN 'Actif' END AS 'Art_Sommeil'
  ,CASE ISNULL(A.ART_NSTAT, 0)WHEN 1 THEN 'Oui' WHEN 0 THEN 'Non' END AS 'Art_Hors Stat'
  ,CASE ISNULL(A.ART_CONTRM, 0)WHEN 1 THEN 'Oui' WHEN 0 THEN 'Non' END AS 'Art_Contremarque'
  ,CASE (A.ART_CATEG) WHEN 'F' THEN 'Produits Finis' WHEN 'S' THEN 'Produits Semi-Finis' WHEN 'M' THEN 'Matières 1ères' WHEN 'O' THEN 'Main d''oeuvre' WHEN 'T' THEN 'Sous-traitance' WHEN 'A' THEN 'Autres' ELSE '' END AS 'Art_Catégorie'
  ,CASE (A.ART_TYPE)WHEN 'P' THEN 'Pièce' WHEN 'C' THEN 'Consigne' WHEN 'N' THEN 'Nomenclature' WHEN 'F' THEN 'Forfait' WHEN 'V' THEN 'Service' ELSE '' END AS 'Art_Type'
  ,CASE (T.PCF_TYPE)WHEN 'C' THEN 'Clients' WHEN 'F' THEN 'Fournisseurs' WHEN 'P' THEN 'Prospects' END AS 'Tiers_Type'
  ,T.PCF_CODE AS 'Tiers_Code'
  ,T.PCF_RS AS 'Tiers_R.sociale'
  ,ISNULL(TF.FAT_LIB, '') AS 'Tiers_Fam'
  ,ISNULL(TS.SFT_LIB, '') AS 'Tiers_Sous-Fam'
  ,ISNULL(S.SRV_NOM, '') AS 'Services'
  ,ISNULL(DI.DIV_LIB, '') AS 'Divisions'
  ,CASE ISNULL(T.PCF_DORT, 0) WHEN 1 THEN 'Non Actif' WHEN 0 THEN 'Actif' END AS 'Tiers_Sommeil'
  ,CASE ISNULL(T.PCF_BLOQUE, 0)WHEN 1 THEN 'Oui' WHEN 0 THEN 'Non' END AS 'Tiers_Bloqué'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC1' AND TA.TBL_CODE = T.FC1_CODE), '') AS 'Tiers_C1'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC2' AND TA.TBL_CODE = T.FC2_CODE), '') AS 'Tiers_C2'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC3' AND TA.TBL_CODE = T.FC3_CODE), '') AS 'Tiers_C3'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC4' AND TA.TBL_CODE = T.FC4_CODE), '') AS 'Tiers_C4'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC5' AND TA.TBL_CODE = T.FC5_CODE), '') AS 'Tiers_C5'
  ,ISNULL(TG.TAR_LIB, '') AS 'Grille de Tarifs'
  ,ISNULL(TP.PCF_RS, '') AS 'Tiers_Payeur'
  ,ISNULL(R.REP_PRENOM, '') + ' ' + ISNULL(R.REP_NOM, '') AS 'Commercial'
  ,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN -L.LIG_QTE ELSE L.LIG_QTE END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN L.LIG_QTE ELSE -L.LIG_QTE END END AS 'Qte'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN L.LIG_COUT WHEN 'A' THEN L.[LIG_COUT] * -1 END AS 'Prix de Revient'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) END END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * -1 END END END AS 'CA HT Brut'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) END END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * -1 END END END AS 'Remise'
  ,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END END END AS 'CA HT €'
  ,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END END AS 'CAHT_Devise'
  --CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END WHEN (D.DOC_STYPE) = '1' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) END) END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END WHEN (D.DOC_STYPE) = '1' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE
  --             WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) END) END END AS 'Marge €'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 WHEN (D.DOC_STYPE) = '1' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) END) END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) WHEN (D.DOC_STYPE) = '1' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) END) END END AS 'Marge Devise'
FROM
  S2.dbo.LIGNES L
  INNER JOIN DOCUMENTS D ON D.DOC_NUMERO = L.DOC_NUMERO
  LEFT OUTER JOIN DEVISES DE ON DE.DEV_CODE = D.DEV_CODE
  LEFT OUTER JOIN ARTICLES A ON A.ART_CODE = L.ART_CODE
  LEFT OUTER JOIN ART_FAM AF ON AF.FAR_CODE = A.FAR_CODE
  LEFT OUTER JOIN ART_SFAM SF ON SF.SFA_CODE = A.SFA_CODE
  LEFT OUTER JOIN PROJETS P ON P.PRJ_CODE = L.PRJ_CODE
  INNER JOIN TIERS T ON T.PCF_CODE = D.PCF_CODE
  LEFT OUTER JOIN TIERS_FAM TF ON TF.FAT_CODE = T.FAT_CODE
  LEFT OUTER JOIN TIERS_SFAM TS ON TS.SFT_CODE = T.SFT_CODE
  LEFT OUTER JOIN SERVICES S ON S.SRV_CODE = T.SRV_CODE
  LEFT OUTER JOIN DIVISIONS DI ON DI.DIV_CODE = T.DIV_CODE
  LEFT OUTER JOIN TARIFS TG ON TG.TAR_CODE = T.TAR_CODE
  LEFT OUTER JOIN TIERS TP ON TP.PCF_CODE = T.PCF_PAYEUR
  LEFT OUTER JOIN REPRESENTANTS R ON R.REP_CODE = D.REP_CODE
WHERE
  D.DOC_TYPE IN ('A', 'V') AND
  L.LIG_TYPE NOT IN ('x', 'X', 'T')
  UNION ALL
-- 'S3' AS 'Société'
SELECT 
  'S3' AS 'Société'
  ,FORMAT (D.DOC_DATE,'dd/MM/yyy') AS 'Doc_Date'
  ,CONVERT(VARCHAR,YEAR(D.DOC_DATE)) AS 'Doc_Année'
  ,'T' + CONVERT(VARCHAR,DATEPART([q], D.DOC_DATE)) AS 'Doc_Trimestre'
  ,CASE WHEN CONVERT(VARCHAR,DATEPART([q], D.DOC_DATE)) IN ('1', '2') THEN 'S1' WHEN CONVERT(VARCHAR,DATEPART([q], D.DOC_DATE)) IN ('3', '4') THEN 'S2' END AS 'Doc_Semestre'
  ,CASE MONTH(D.DOC_DATE)WHEN '1' THEN '01-Janv' WHEN '2' THEN '02-Fév' WHEN '3' THEN '03-Mars' WHEN '4' THEN '04-Avr' WHEN '5' THEN '05-Mai' WHEN '6' THEN '06-Juin' WHEN '7' THEN '07-Juil' WHEN '8' THEN '08-Août' WHEN '9' THEN '09-Sept' WHEN '10' THEN '10-Oct' WHEN '11' THEN '11-Nov' WHEN '12' THEN '12-Déc' END AS 'Doc_Mois'
  ,RIGHT('0' + CONVERT(VARCHAR,YEAR(D.DOC_DATE)), 4) + '/' + RIGHT('0' + CONVERT(VARCHAR,DATEPART([ww], D.DOC_DATE)), 2) AS 'Doc_Semaine'
  ,RIGHT('0' + CONVERT(VARCHAR,YEAR(D.DOC_DATE)), 4) + '/' + RIGHT('0' + CONVERT(VARCHAR,MONTH(D.DOC_DATE)), 2) AS 'Doc_Période'
  ,D.DOC_PIECE AS 'Doc_N° Pièce'
  ,D.DOC_REFPCF AS 'Doc_Réf'
  ,CASE (D.DOC_TYPE) WHEN 'A' THEN 'Achat' WHEN 'V' THEN 'Ventes' END AS 'Doc_Type'
  ,CASE (D.DOC_TYPE) WHEN 'V' THEN CASE (D.DOC_STYPE)WHEN 'P' THEN 'Pro-forma' WHEN 'D' THEN 'Devis' WHEN 'C' THEN 'Commandes' WHEN 'B' THEN 'Bons de Livraison' WHEN 'R' THEN 'Bons de Retour' WHEN 'F' THEN 'Factures' WHEN '1' THEN 'Factures Financières' WHEN 'A' THEN 'Avoirs' WHEN '0' THEN 'Avoirs Financiers' END WHEN 'A' THEN CASE (D.DOC_STYPE)WHEN 'D' THEN 'Demandes de Prix' WHEN 'C' THEN 'Commandes' WHEN 'B' THEN 'Bons de Réceptions' WHEN 'R' THEN 'Bons de Retour' WHEN 'F' THEN 'Factures' WHEN '1' THEN 'Factures Financières' WHEN 'A' THEN 'Avoirs' WHEN '0' THEN 'Avoirs Financiers' END END AS 'Doc_Sous type'
  ,CASE (D.DOC_ETAT) WHEN 'E' THEN 'Non signé' WHEN 'I' THEN 'Non signé' WHEN 'S' THEN 'Signé' WHEN 'C' THEN 'Comptabilisé' WHEN 'T' THEN 'Transféré partiellement' WHEN 'A' THEN 'Archivé' END AS 'Doc_Etat'
  ,D.DEV_CODE AS 'Doc_Code Devise'
  ,DE.DEV_LIB AS 'Doc_Lib Devise'
  ,D.DOC_TX_DEV AS 'Doc_Cours Devise'
  ,ISNULL(P.PRJ_CODE, '') AS 'Doc_Code Aff'
  ,ISNULL(P.PRJ_LIB, '') AS 'Doc_Lib Aff'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'PRJ' AND TA.TBL_CODE = P.PRJ_ETAT), '') AS 'Aff_Etat'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'GP1' AND TA.TBL_CODE = P.PRJ_GROUP1), '') AS 'Aff_Cat'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'GP2' AND TA.TBL_CODE = P.PRJ_GROUP2), '') AS 'Aff_Sous-Cat'
  ,ISNULL(A.ART_CODE, '') AS 'Art_Code'
  ,ISNULL(A.ART_LIB, '') AS 'Art_Lib'
  ,ISNULL(AF.FAR_LIB, '') AS 'Art_Fam'
  ,ISNULL(SF.SFA_LIB, '') AS 'Art_Sous-Fam'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA1' AND TA.TBL_CODE = A.FA1_CODE), '') AS 'Art_C1'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA2' AND TA.TBL_CODE = A.FA2_CODE), '') AS 'Art_C2'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA3' AND TA.TBL_CODE = A.FA3_CODE), '') AS 'Art_C3'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA4' AND TA.TBL_CODE = A.FA4_CODE), '') AS 'Art_C4'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA5' AND TA.TBL_CODE = A.FA5_CODE), '') AS 'Art_C5'
  ,CASE ISNULL(A.ART_DORT, 0)WHEN 1 THEN 'Non actif' WHEN 0 THEN 'Actif' END AS 'Art_Sommeil'
  ,CASE ISNULL(A.ART_NSTAT, 0)WHEN 1 THEN 'Oui' WHEN 0 THEN 'Non' END AS 'Art_Hors Stat'
  ,CASE ISNULL(A.ART_CONTRM, 0)WHEN 1 THEN 'Oui' WHEN 0 THEN 'Non' END AS 'Art_Contremarque'
  ,CASE (A.ART_CATEG) WHEN 'F' THEN 'Produits Finis' WHEN 'S' THEN 'Produits Semi-Finis' WHEN 'M' THEN 'Matières 1ères' WHEN 'O' THEN 'Main d''oeuvre' WHEN 'T' THEN 'Sous-traitance' WHEN 'A' THEN 'Autres' ELSE '' END AS 'Art_Catégorie'
  ,CASE (A.ART_TYPE)WHEN 'P' THEN 'Pièce' WHEN 'C' THEN 'Consigne' WHEN 'N' THEN 'Nomenclature' WHEN 'F' THEN 'Forfait' WHEN 'V' THEN 'Service' ELSE '' END AS 'Art_Type'
  ,CASE (T.PCF_TYPE)WHEN 'C' THEN 'Clients' WHEN 'F' THEN 'Fournisseurs' WHEN 'P' THEN 'Prospects' END AS 'Tiers_Type'
  ,T.PCF_CODE AS 'Tiers_Code'
  ,T.PCF_RS AS 'Tiers_R.sociale'
  ,ISNULL(TF.FAT_LIB, '') AS 'Tiers_Fam'
  ,ISNULL(TS.SFT_LIB, '') AS 'Tiers_Sous-Fam'
  ,ISNULL(S.SRV_NOM, '') AS 'Services'
  ,ISNULL(DI.DIV_LIB, '') AS 'Divisions'
  ,CASE ISNULL(T.PCF_DORT, 0) WHEN 1 THEN 'Non Actif' WHEN 0 THEN 'Actif' END AS 'Tiers_Sommeil'
  ,CASE ISNULL(T.PCF_BLOQUE, 0)WHEN 1 THEN 'Oui' WHEN 0 THEN 'Non' END AS 'Tiers_Bloqué'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC1' AND TA.TBL_CODE = T.FC1_CODE), '') AS 'Tiers_C1'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC2' AND TA.TBL_CODE = T.FC2_CODE), '') AS 'Tiers_C2'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC3' AND TA.TBL_CODE = T.FC3_CODE), '') AS 'Tiers_C3'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC4' AND TA.TBL_CODE = T.FC4_CODE), '') AS 'Tiers_C4'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC5' AND TA.TBL_CODE = T.FC5_CODE), '') AS 'Tiers_C5'
  ,ISNULL(TG.TAR_LIB, '') AS 'Grille de Tarifs'
  ,ISNULL(TP.PCF_RS, '') AS 'Tiers_Payeur'
  ,ISNULL(R.REP_PRENOM, '') + ' ' + ISNULL(R.REP_NOM, '') AS 'Commercial'
  ,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN -L.LIG_QTE ELSE L.LIG_QTE END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN L.LIG_QTE ELSE -L.LIG_QTE END END AS 'Qte'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN L.LIG_COUT WHEN 'A' THEN L.[LIG_COUT] * -1 END AS 'Prix de Revient'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) END END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * -1 END END END AS 'CA HT Brut'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) END END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * -1 END END END AS 'Remise'
  ,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END END END AS 'CA HT €'
  ,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END END AS 'CAHT_Devise'
  --CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END WHEN (D.DOC_STYPE) = '1' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) END) END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END WHEN (D.DOC_STYPE) = '1' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE
  --             WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) END) END END AS 'Marge €'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 WHEN (D.DOC_STYPE) = '1' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) END) END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) WHEN (D.DOC_STYPE) = '1' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) END) END END AS 'Marge Devise'
FROM
  S3.dbo.LIGNES L
  INNER JOIN DOCUMENTS D ON D.DOC_NUMERO = L.DOC_NUMERO
  LEFT OUTER JOIN DEVISES DE ON DE.DEV_CODE = D.DEV_CODE
  LEFT OUTER JOIN ARTICLES A ON A.ART_CODE = L.ART_CODE
  LEFT OUTER JOIN ART_FAM AF ON AF.FAR_CODE = A.FAR_CODE
  LEFT OUTER JOIN ART_SFAM SF ON SF.SFA_CODE = A.SFA_CODE
  LEFT OUTER JOIN PROJETS P ON P.PRJ_CODE = L.PRJ_CODE
  INNER JOIN TIERS T ON T.PCF_CODE = D.PCF_CODE
  LEFT OUTER JOIN TIERS_FAM TF ON TF.FAT_CODE = T.FAT_CODE
  LEFT OUTER JOIN TIERS_SFAM TS ON TS.SFT_CODE = T.SFT_CODE
  LEFT OUTER JOIN SERVICES S ON S.SRV_CODE = T.SRV_CODE
  LEFT OUTER JOIN DIVISIONS DI ON DI.DIV_CODE = T.DIV_CODE
  LEFT OUTER JOIN TARIFS TG ON TG.TAR_CODE = T.TAR_CODE
  LEFT OUTER JOIN TIERS TP ON TP.PCF_CODE = T.PCF_PAYEUR
  LEFT OUTER JOIN REPRESENTANTS R ON R.REP_CODE = D.REP_CODE
WHERE
  D.DOC_TYPE IN ('A', 'V') AND
  L.LIG_TYPE NOT IN ('x', 'X', 'T')
  UNION ALL
-- 'S4' AS 'Société'
SELECT 
	'S4' AS 'Société'
  ,FORMAT (D.DOC_DATE,'dd/MM/yyy') AS 'Doc_Date'
  ,CONVERT(VARCHAR,YEAR(D.DOC_DATE)) AS 'Doc_Année'
  ,'T' + CONVERT(VARCHAR,DATEPART([q], D.DOC_DATE)) AS 'Doc_Trimestre'
  ,CASE WHEN CONVERT(VARCHAR,DATEPART([q], D.DOC_DATE)) IN ('1', '2') THEN 'S1' WHEN CONVERT(VARCHAR,DATEPART([q], D.DOC_DATE)) IN ('3', '4') THEN 'S2' END AS 'Doc_Semestre'
  ,CASE MONTH(D.DOC_DATE)WHEN '1' THEN '01-Janv' WHEN '2' THEN '02-Fév' WHEN '3' THEN '03-Mars' WHEN '4' THEN '04-Avr' WHEN '5' THEN '05-Mai' WHEN '6' THEN '06-Juin' WHEN '7' THEN '07-Juil' WHEN '8' THEN '08-Août' WHEN '9' THEN '09-Sept' WHEN '10' THEN '10-Oct' WHEN '11' THEN '11-Nov' WHEN '12' THEN '12-Déc' END AS 'Doc_Mois'
  ,RIGHT('0' + CONVERT(VARCHAR,YEAR(D.DOC_DATE)), 4) + '/' + RIGHT('0' + CONVERT(VARCHAR,DATEPART([ww], D.DOC_DATE)), 2) AS 'Doc_Semaine'
  ,RIGHT('0' + CONVERT(VARCHAR,YEAR(D.DOC_DATE)), 4) + '/' + RIGHT('0' + CONVERT(VARCHAR,MONTH(D.DOC_DATE)), 2) AS 'Doc_Période'
  ,D.DOC_PIECE AS 'Doc_N° Pièce'
  ,D.DOC_REFPCF AS 'Doc_Réf'
  ,CASE (D.DOC_TYPE) WHEN 'A' THEN 'Achat' WHEN 'V' THEN 'Ventes' END AS 'Doc_Type'
  ,CASE (D.DOC_TYPE) WHEN 'V' THEN CASE (D.DOC_STYPE)WHEN 'P' THEN 'Pro-forma' WHEN 'D' THEN 'Devis' WHEN 'C' THEN 'Commandes' WHEN 'B' THEN 'Bons de Livraison' WHEN 'R' THEN 'Bons de Retour' WHEN 'F' THEN 'Factures' WHEN '1' THEN 'Factures Financières' WHEN 'A' THEN 'Avoirs' WHEN '0' THEN 'Avoirs Financiers' END WHEN 'A' THEN CASE (D.DOC_STYPE)WHEN 'D' THEN 'Demandes de Prix' WHEN 'C' THEN 'Commandes' WHEN 'B' THEN 'Bons de Réceptions' WHEN 'R' THEN 'Bons de Retour' WHEN 'F' THEN 'Factures' WHEN '1' THEN 'Factures Financières' WHEN 'A' THEN 'Avoirs' WHEN '0' THEN 'Avoirs Financiers' END END AS 'Doc_Sous type'
  ,CASE (D.DOC_ETAT) WHEN 'E' THEN 'Non signé' WHEN 'I' THEN 'Non signé' WHEN 'S' THEN 'Signé' WHEN 'C' THEN 'Comptabilisé' WHEN 'T' THEN 'Transféré partiellement' WHEN 'A' THEN 'Archivé' END AS 'Doc_Etat'
  ,D.DEV_CODE AS 'Doc_Code Devise'
  ,DE.DEV_LIB AS 'Doc_Lib Devise'
  ,D.DOC_TX_DEV AS 'Doc_Cours Devise'
  ,ISNULL(P.PRJ_CODE, '') AS 'Doc_Code Aff'
  ,ISNULL(P.PRJ_LIB, '') AS 'Doc_Lib Aff'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'PRJ' AND TA.TBL_CODE = P.PRJ_ETAT), '') AS 'Aff_Etat'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'GP1' AND TA.TBL_CODE = P.PRJ_GROUP1), '') AS 'Aff_Cat'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'GP2' AND TA.TBL_CODE = P.PRJ_GROUP2), '') AS 'Aff_Sous-Cat'
  ,ISNULL(A.ART_CODE, '') AS 'Art_Code'
  ,ISNULL(A.ART_LIB, '') AS 'Art_Lib'
  ,ISNULL(AF.FAR_LIB, '') AS 'Art_Fam'
  ,ISNULL(SF.SFA_LIB, '') AS 'Art_Sous-Fam'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA1' AND TA.TBL_CODE = A.FA1_CODE), '') AS 'Art_C1'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA2' AND TA.TBL_CODE = A.FA2_CODE), '') AS 'Art_C2'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA3' AND TA.TBL_CODE = A.FA3_CODE), '') AS 'Art_C3'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA4' AND TA.TBL_CODE = A.FA4_CODE), '') AS 'Art_C4'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FA5' AND TA.TBL_CODE = A.FA5_CODE), '') AS 'Art_C5'
  ,CASE ISNULL(A.ART_DORT, 0)WHEN 1 THEN 'Non actif' WHEN 0 THEN 'Actif' END AS 'Art_Sommeil'
  ,CASE ISNULL(A.ART_NSTAT, 0)WHEN 1 THEN 'Oui' WHEN 0 THEN 'Non' END AS 'Art_Hors Stat'
  ,CASE ISNULL(A.ART_CONTRM, 0)WHEN 1 THEN 'Oui' WHEN 0 THEN 'Non' END AS 'Art_Contremarque'
  ,CASE (A.ART_CATEG) WHEN 'F' THEN 'Produits Finis' WHEN 'S' THEN 'Produits Semi-Finis' WHEN 'M' THEN 'Matières 1ères' WHEN 'O' THEN 'Main d''oeuvre' WHEN 'T' THEN 'Sous-traitance' WHEN 'A' THEN 'Autres' ELSE '' END AS 'Art_Catégorie'
  ,CASE (A.ART_TYPE)WHEN 'P' THEN 'Pièce' WHEN 'C' THEN 'Consigne' WHEN 'N' THEN 'Nomenclature' WHEN 'F' THEN 'Forfait' WHEN 'V' THEN 'Service' ELSE '' END AS 'Art_Type'
  ,CASE (T.PCF_TYPE)WHEN 'C' THEN 'Clients' WHEN 'F' THEN 'Fournisseurs' WHEN 'P' THEN 'Prospects' END AS 'Tiers_Type'
  ,T.PCF_CODE AS 'Tiers_Code'
  ,T.PCF_RS AS 'Tiers_R.sociale'
  ,ISNULL(TF.FAT_LIB, '') AS 'Tiers_Fam'
  ,ISNULL(TS.SFT_LIB, '') AS 'Tiers_Sous-Fam'
  ,ISNULL(S.SRV_NOM, '') AS 'Services'
  ,ISNULL(DI.DIV_LIB, '') AS 'Divisions'
  ,CASE ISNULL(T.PCF_DORT, 0) WHEN 1 THEN 'Non Actif' WHEN 0 THEN 'Actif' END AS 'Tiers_Sommeil'
  ,CASE ISNULL(T.PCF_BLOQUE, 0)WHEN 1 THEN 'Oui' WHEN 0 THEN 'Non' END AS 'Tiers_Bloqué'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC1' AND TA.TBL_CODE = T.FC1_CODE), '') AS 'Tiers_C1'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC2' AND TA.TBL_CODE = T.FC2_CODE), '') AS 'Tiers_C2'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC3' AND TA.TBL_CODE = T.FC3_CODE), '') AS 'Tiers_C3'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC4' AND TA.TBL_CODE = T.FC4_CODE), '') AS 'Tiers_C4'
  ,ISNULL((SELECT TA.TBL_LIB FROM TABLES TA WHERE TA.TBL_ID = 'FC5' AND TA.TBL_CODE = T.FC5_CODE), '') AS 'Tiers_C5'
  ,ISNULL(TG.TAR_LIB, '') AS 'Grille de Tarifs'
  ,ISNULL(TP.PCF_RS, '') AS 'Tiers_Payeur'
  ,ISNULL(R.REP_PRENOM, '') + ' ' + ISNULL(R.REP_NOM, '') AS 'Commercial'
  ,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN -L.LIG_QTE ELSE L.LIG_QTE END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN L.LIG_QTE ELSE -L.LIG_QTE END END AS 'Qte'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN L.LIG_COUT WHEN 'A' THEN L.[LIG_COUT] * -1 END AS 'Prix de Revient'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) END END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0) * -1 END END END AS 'CA HT Brut'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) END END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE (ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) - (ISNULL(L.LIG_P_BRUT, 0) * ISNULL(L.LIG_QTE, 0))) * -1 END END END AS 'Remise'
  ,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN DE.DEV_INCERT = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END END END AS 'CA HT €'
  ,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END END AS 'CAHT_Devise'
  --CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END WHEN (D.DOC_STYPE) = '1' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) END) END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END WHEN (D.DOC_STYPE) = '1' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END ELSE CASE WHEN D.DEV_CODE <> 'EUR' THEN CASE WHEN [DEV_INCERT] = 1 THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * ISNULL(D.DOC_TX_DEV, 1) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) / CASE
  --             WHEN ISNULL(D.DOC_TX_DEV, 0) <> 0 THEN ISNULL(D.DOC_TX_DEV, 1) ELSE 1 END * -1 END ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) END) END END AS 'Marge €'
  --,CASE (D.DOC_TYPE)WHEN 'V' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 WHEN (D.DOC_STYPE) = '1' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) END) END WHEN 'A' THEN CASE WHEN (D.DOC_STYPE) = '0' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) WHEN (D.DOC_STYPE) = '1' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 ELSE CASE WHEN (D.DOC_STYPE) = 'A' THEN ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) ELSE ISNULL((CASE WHEN ISNULL(D.DOC_EN_TTC, 0) = 1 THEN ISNULL(L.LIG_TOTAL, 0) / (1 + ISNULL(L.NAT_TVATX, 0) / 100) ELSE ISNULL(L.LIG_TOTAL, 0) END), 0) * -1 END + (CASE WHEN D.DOC_STYPE = 'A' THEN (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) * -1 END) ELSE (CASE WHEN L.LIG_COUT = 0 THEN 0 ELSE ISNULL(L.LIG_COUT, 0) END) END) END END AS 'Marge Devise'
FROM
  S4.dbo.LIGNES L
  INNER JOIN DOCUMENTS D ON D.DOC_NUMERO = L.DOC_NUMERO
  LEFT OUTER JOIN DEVISES DE ON DE.DEV_CODE = D.DEV_CODE
  LEFT OUTER JOIN ARTICLES A ON A.ART_CODE = L.ART_CODE
  LEFT OUTER JOIN ART_FAM AF ON AF.FAR_CODE = A.FAR_CODE
  LEFT OUTER JOIN ART_SFAM SF ON SF.SFA_CODE = A.SFA_CODE
  LEFT OUTER JOIN PROJETS P ON P.PRJ_CODE = L.PRJ_CODE
  INNER JOIN TIERS T ON T.PCF_CODE = D.PCF_CODE
  LEFT OUTER JOIN TIERS_FAM TF ON TF.FAT_CODE = T.FAT_CODE
  LEFT OUTER JOIN TIERS_SFAM TS ON TS.SFT_CODE = T.SFT_CODE
  LEFT OUTER JOIN SERVICES S ON S.SRV_CODE = T.SRV_CODE
  LEFT OUTER JOIN DIVISIONS DI ON DI.DIV_CODE = T.DIV_CODE
  LEFT OUTER JOIN TARIFS TG ON TG.TAR_CODE = T.TAR_CODE
  LEFT OUTER JOIN TIERS TP ON TP.PCF_CODE = T.PCF_PAYEUR
  LEFT OUTER JOIN REPRESENTANTS R ON R.REP_CODE = D.REP_CODE
WHERE
  D.DOC_TYPE IN ('A', 'V') AND
  L.LIG_TYPE NOT IN ('x', 'X', 'T');

Req 3 : celle-ci est opérationnelle - elle fonctionne pour chaque SELECT et renvoie bien des données dans la vue

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
CREATE VIEW G_PCG
AS
--	'S1' AS 'Société'
SELECT 
	'S1' AS 'Société'
	,CPT_NUMERO AS 'Cpte_N°'
	,CPT_LIB AS'Cpte_Lib'
	,CPT_REPORT.RPT_COMPTE AS 'Report_Cpte'
	,CPT_REPORT.RPT_LIB AS 'Report_Lib'
	,CASE 
		WHEN ISNULL(CPT_ANA,'')='P' THEN 'Possible'
		WHEN ISNULL(CPT_ANA,'')='I' THEN 'Interdit' 
		WHEN ISNULL(CPT_ANA,'')='O' THEN 'Obligatoire' 
	 END AS 'Analyt_Saisie'
	,substring(CPT_NUMERO, 1, 1) AS 'Cpte_Classe'
	,CASE WHEN (substring(CPT_NUMERO, 1, 1)) = '1' THEN 'Capitaux' WHEN (substring(CPT_NUMERO, 1, 1)) = '2' THEN 'Immos' WHEN (substring(CPT_NUMERO, 1, 1)) = '3' THEN 'Stock' WHEN (substring(CPT_NUMERO, 1, 1)) = '4' THEN 'Tiers' WHEN (substring(CPT_NUMERO, 1, 1)) = '5' THEN 'Financiers' WHEN (substring(CPT_NUMERO, 1, 1)) = '6' THEN 'Charges' WHEN (substring(CPT_NUMERO, 1, 1)) = '7' THEN 'Produits' END AS 'Cpte_Classe_Lib'
	,CASE WHEN (substring(CPT_NUMERO, 1, 2)) = '40' THEN 'Frns' WHEN (substring(CPT_NUMERO, 1, 2)) = '41' THEN 'Clients' WHEN (substring(CPT_NUMERO, 1, 1)) = '4' THEN 'Autres Tiers' END AS 'Clt/Frn'
	,substring(CPT_NUMERO, 1, 2) AS 'Cpte_Rgpt_1'
	,substring(CPT_NUMERO, 1, 3) AS 'Cpte_Rgpt_2'
	,substring(CPT_NUMERO, 1, 4) AS 'Cpte_Rgpt_3'
	,substring(CPT_NUMERO, 1, 5) AS 'Cpte_Rgpt_4'
FROM S1.dbo.COMPTES
LEFT JOIN CPT_REPORT ON CPT_REPORT.RPT_COMPTE = COMPTES.RPT_COMPTE
WHERE CPT_TYPE ='G'
UNION ALL
-- 'S2' AS 'Société'
SELECT 
	'S2' AS 'Société'
	,CPT_NUMERO AS 'Cpte_N°'
	,CPT_LIB AS'Cpte_Lib'
	,CPT_REPORT.RPT_COMPTE AS 'Report_Cpte'
	,CPT_REPORT.RPT_LIB AS 'Report_Lib'
	,CASE 
		WHEN ISNULL(CPT_ANA,'')='P' THEN 'Possible'
		WHEN ISNULL(CPT_ANA,'')='I' THEN 'Interdit' 
		WHEN ISNULL(CPT_ANA,'')='O' THEN 'Obligatoire' 
	 END AS 'Analyt_Saisie'
	,substring(CPT_NUMERO, 1, 1) AS 'Cpte_Classe'
	,CASE WHEN (substring(CPT_NUMERO, 1, 1)) = '1' THEN 'Capitaux' WHEN (substring(CPT_NUMERO, 1, 1)) = '2' THEN 'Immos' WHEN (substring(CPT_NUMERO, 1, 1)) = '3' THEN 'Stock' WHEN (substring(CPT_NUMERO, 1, 1)) = '4' THEN 'Tiers' WHEN (substring(CPT_NUMERO, 1, 1)) = '5' THEN 'Financiers' WHEN (substring(CPT_NUMERO, 1, 1)) = '6' THEN 'Charges' WHEN (substring(CPT_NUMERO, 1, 1)) = '7' THEN 'Produits' END AS 'Cpte_Classe_Lib'
	,CASE WHEN (substring(CPT_NUMERO, 1, 2)) = '40' THEN 'Frns' WHEN (substring(CPT_NUMERO, 1, 2)) = '41' THEN 'Clients' WHEN (substring(CPT_NUMERO, 1, 1)) = '4' THEN 'Autres Tiers' END AS 'Clt/Frn'
	,substring(CPT_NUMERO, 1, 2) AS 'Cpte_Rgpt_1'
	,substring(CPT_NUMERO, 1, 3) AS 'Cpte_Rgpt_2'
	,substring(CPT_NUMERO, 1, 4) AS 'Cpte_Rgpt_3'
	,substring(CPT_NUMERO, 1, 5) AS 'Cpte_Rgpt_4'
FROM S2.dbo.COMPTES
LEFT JOIN CPT_REPORT ON CPT_REPORT.RPT_COMPTE = COMPTES.RPT_COMPTE
WHERE CPT_TYPE ='G'
UNION ALL
-- 'S3' AS 'Société'
SELECT 
	'S3' AS 'Société'
	,CPT_NUMERO AS 'Cpte_N°'
	,CPT_LIB AS'Cpte_Lib'
	,CPT_REPORT.RPT_COMPTE AS 'Report_Cpte'
	,CPT_REPORT.RPT_LIB AS 'Report_Lib'
	,CASE 
		WHEN ISNULL(CPT_ANA,'')='P' THEN 'Possible'
		WHEN ISNULL(CPT_ANA,'')='I' THEN 'Interdit' 
		WHEN ISNULL(CPT_ANA,'')='O' THEN 'Obligatoire' 
	 END AS 'Analyt_Saisie'
	,substring(CPT_NUMERO, 1, 1) AS 'Cpte_Classe'
	,CASE WHEN (substring(CPT_NUMERO, 1, 1)) = '1' THEN 'Capitaux' WHEN (substring(CPT_NUMERO, 1, 1)) = '2' THEN 'Immos' WHEN (substring(CPT_NUMERO, 1, 1)) = '3' THEN 'Stock' WHEN (substring(CPT_NUMERO, 1, 1)) = '4' THEN 'Tiers' WHEN (substring(CPT_NUMERO, 1, 1)) = '5' THEN 'Financiers' WHEN (substring(CPT_NUMERO, 1, 1)) = '6' THEN 'Charges' WHEN (substring(CPT_NUMERO, 1, 1)) = '7' THEN 'Produits' END AS 'Cpte_Classe_Lib'
	,CASE WHEN (substring(CPT_NUMERO, 1, 2)) = '40' THEN 'Frns' WHEN (substring(CPT_NUMERO, 1, 2)) = '41' THEN 'Clients' WHEN (substring(CPT_NUMERO, 1, 1)) = '4' THEN 'Autres Tiers' END AS 'Clt/Frn'
	,substring(CPT_NUMERO, 1, 2) AS 'Cpte_Rgpt_1'
	,substring(CPT_NUMERO, 1, 3) AS 'Cpte_Rgpt_2'
	,substring(CPT_NUMERO, 1, 4) AS 'Cpte_Rgpt_3'
	,substring(CPT_NUMERO, 1, 5) AS 'Cpte_Rgpt_4'
FROM S3.dbo.COMPTES
LEFT JOIN CPT_REPORT ON CPT_REPORT.RPT_COMPTE = COMPTES.RPT_COMPTE
WHERE CPT_TYPE ='G'
UNION ALL
-- 'S4' AS 'Société'
SELECT 
	'S4' AS 'Société'
	,CPT_NUMERO AS 'Cpte_N°'
	,CPT_LIB AS'Cpte_Lib'
	,CPT_REPORT.RPT_COMPTE AS 'Report_Cpte'
	,CPT_REPORT.RPT_LIB AS 'Report_Lib'
	,CASE 
		WHEN ISNULL(CPT_ANA,'')='P' THEN 'Possible'
		WHEN ISNULL(CPT_ANA,'')='I' THEN 'Interdit' 
		WHEN ISNULL(CPT_ANA,'')='O' THEN 'Obligatoire' 
	 END AS 'Analyt_Saisie'
	,substring(CPT_NUMERO, 1, 1) AS 'Cpte_Classe'
	,CASE WHEN (substring(CPT_NUMERO, 1, 1)) = '1' THEN 'Capitaux' WHEN (substring(CPT_NUMERO, 1, 1)) = '2' THEN 'Immos' WHEN (substring(CPT_NUMERO, 1, 1)) = '3' THEN 'Stock' WHEN (substring(CPT_NUMERO, 1, 1)) = '4' THEN 'Tiers' WHEN (substring(CPT_NUMERO, 1, 1)) = '5' THEN 'Financiers' WHEN (substring(CPT_NUMERO, 1, 1)) = '6' THEN 'Charges' WHEN (substring(CPT_NUMERO, 1, 1)) = '7' THEN 'Produits' END AS 'Cpte_Classe_Lib'
	,CASE WHEN (substring(CPT_NUMERO, 1, 2)) = '40' THEN 'Frns' WHEN (substring(CPT_NUMERO, 1, 2)) = '41' THEN 'Clients' WHEN (substring(CPT_NUMERO, 1, 1)) = '4' THEN 'Autres Tiers' END AS 'Clt/Frn'
	,substring(CPT_NUMERO, 1, 2) AS 'Cpte_Rgpt_1'
	,substring(CPT_NUMERO, 1, 3) AS 'Cpte_Rgpt_2'
	,substring(CPT_NUMERO, 1, 4) AS 'Cpte_Rgpt_3'
	,substring(CPT_NUMERO, 1, 5) AS 'Cpte_Rgpt_4'
FROM S4.dbo.COMPTES
LEFT JOIN CPT_REPORT ON CPT_REPORT.RPT_COMPTE = COMPTES.RPT_COMPTE
WHERE CPT_TYPE ='G';

Amicalement

Stéphane

**escartefigue** · 06/04/2021, 14h13

Bonjour,

Plutôt que de faire une requête qui semble être la même pour toutes les sociétés, ce serait plus simple de faire déjà une vue qui consolide toutes les sociétés.

**steven1964** · 06/04/2021, 14h34

Bonjour,

Oui c'est ce que j'essaie de faire, créer une vue qui consolide toutes les données de toutes les sociétés afin de faciliter le requetage mais je n'arrive pas à obtenir gain de cause malgré toutes mes tentatives.
Je peux effectivement tenter de créer une vue par société et requeter les vues avec UNION ALL ; ca devrait alléger les requetes.

Je tente cette piste !

Merci

cdlt
Stéphane.

**escartefigue** · 06/04/2021, 16h10

Je pensais plutôt à une première vue non filtrante toute simple qui récupère toutes les colonnes de toutes les sociétés sans faire de regroupement ni de jointure, uniquement les unions.
Puis faire une requête, dans une autre vue ou directement, à partir de cette première vue.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
CREATE V1 AS
    SELECT Col1, Col2, ..., Coln
    FROM S1.dbo.tableX
    UNION ALL
    SELECT Col1, Col2, ..., Coln
    FROM S2.dbo.tableX
    UNION ALL
    SELECT Col1, Col2, ..., Coln
    FROM S3.dbo.tableX
    UNION ALL
    SELECT Col1, Col2, ..., Coln
    FROM S4.dbo.tableX

**steven1964** · 07/04/2021, 14h44

Bonjour,

J'ai trouvé la solution, il manquait le nom de la base de données à mes requêtes et ça fonctionne correctement maintenant !
du style ci-dessous pour chaque société :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 S1.dbo.PROJETS
  LEFT OUTER JOIN S1.dbo.[Tiers] [t] ON [t].PCF_CODE =PROJETS.PCF_CODE
  LEFT OUTER JOIN S1.dbo.[Tiers_fam] [tf] ON [tf].[fat_code] = [t].[fat_code]
  LEFT OUTER JOIN S1.dbo.[Tiers_sfam] [ts] ON [ts].[sft_code] = [t].[sft_code]
  LEFT OUTER JOIN S1.dbo.[Services] [s] ON [s].SRV_CODE = [t].SRV_CODE
  LEFT OUTER JOIN S1.dbo.[Divisions] [di] ON [di].DIV_CODE = [t].DIV_CODE
  LEFT OUTER JOIN S1.dbo.[Tarifs] [tg] ON [tg].TAR_CODE = [t].TAR_CODE
  INNER JOIN S1.dbo.[users] [u] ON [u].[usr_name] = PROJETS.PRJ_USRMAJ
  LEFT OUTER JOIN S1.dbo.[personnel] [pu] ON [pu].SAL_CODE = [u].SAL_CODE
  LEFT OUTER JOIN S1.dbo.[Representants] [r] ON [r].REP_CODE = [t].REP_CODE
GROUP BY
  S1.dbo.PROJETS.PRJ_CODE

Création d'une vue à partir de plusieurs bases avec des group by

Développement SQL Server

Discussions similaires

Partager

Partager