IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

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


Sujet :

Développement SQL Server

  1. #1
    Nouveau Candidat au Club
    Homme Profil pro
    Conseil - Consultant en systèmes d'information
    Inscrit en
    avril 2021
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Conseil - Consultant en systèmes d'information
    Secteur : Conseil

    Informations forums :
    Inscription : avril 2021
    Messages : 5
    Points : 1
    Points
    1
    Par défaut Création d'une vue à partir de plusieurs bases avec des group by
    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

  2. #2
    Membre éclairé
    Avatar de Wachter
    Homme Profil pro
    Développeur T-SQL
    Inscrit en
    octobre 2008
    Messages
    398
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur T-SQL

    Informations forums :
    Inscription : octobre 2008
    Messages : 398
    Points : 690
    Points
    690
    Par défaut
    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.
    Code parrain certification Voltaire : NTMPH759

  3. #3
    Nouveau Candidat au Club
    Homme Profil pro
    Conseil - Consultant en systèmes d'information
    Inscrit en
    avril 2021
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Conseil - Consultant en systèmes d'information
    Secteur : Conseil

    Informations forums :
    Inscription : avril 2021
    Messages : 5
    Points : 1
    Points
    1
    Par défaut Création d'une vue à partir de plusieurs bases avec et sans group by
    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

  4. #4
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 719
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 719
    Points : 49 089
    Points
    49 089
    Billets dans le blog
    1
    Par défaut
    Postez la vrai requête !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  5. #5
    Nouveau Candidat au Club
    Homme Profil pro
    Conseil - Consultant en systèmes d'information
    Inscrit en
    avril 2021
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Conseil - Consultant en systèmes d'information
    Secteur : Conseil

    Informations forums :
    Inscription : avril 2021
    Messages : 5
    Points : 1
    Points
    1
    Par défaut CREATE VIEW AVEC ET SANS GROUPE BY
    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

  6. #6
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    7 076
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 7 076
    Points : 22 419
    Points
    22 419
    Billets dans le blog
    2
    Par défaut
    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.

  7. #7
    Nouveau Candidat au Club
    Homme Profil pro
    Conseil - Consultant en systèmes d'information
    Inscrit en
    avril 2021
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Conseil - Consultant en systèmes d'information
    Secteur : Conseil

    Informations forums :
    Inscription : avril 2021
    Messages : 5
    Points : 1
    Points
    1
    Par défaut
    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.

  8. #8
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    7 076
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 7 076
    Points : 22 419
    Points
    22 419
    Billets dans le blog
    2
    Par défaut
    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

  9. #9
    Nouveau Candidat au Club
    Homme Profil pro
    Conseil - Consultant en systèmes d'information
    Inscrit en
    avril 2021
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Conseil - Consultant en systèmes d'information
    Secteur : Conseil

    Informations forums :
    Inscription : avril 2021
    Messages : 5
    Points : 1
    Points
    1
    Par défaut
    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

Discussions similaires

  1. Création d'une vue à partir d'une requête sur plusieurs bases de données
    Par archivage-services dans le forum Développement
    Réponses: 2
    Dernier message: 25/10/2018, 10h46
  2. Créer une VUE à partir de plusieurs curseurs
    Par Djene dans le forum Langage SQL
    Réponses: 2
    Dernier message: 01/04/2014, 15h44
  3. Création d'une variable à partir de plusieurs variables
    Par Kiswends dans le forum SAS Base
    Réponses: 1
    Dernier message: 21/11/2013, 15h37
  4. Création d'une table à partir de plusieurs sources
    Par pperrin dans le forum QlikView
    Réponses: 4
    Dernier message: 17/12/2012, 17h40
  5. Création d'une vue à partir d'un modèle
    Par Herwin78800 dans le forum ASP.NET MVC
    Réponses: 2
    Dernier message: 20/06/2011, 18h31

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo