db/vues/auf_v_controleTiersOperations.sql

   1 USE [coda-prod]
   2 GO
   3 SET ANSI_NULLS ON
   4 GO
   5 SET QUOTED_IDENTIFIER ON
   6 GO
   7
   8 ALTER VIEW [dbo].[auf_v_controleTiersOperations]
   9 AS
  10
  11 SELECT
  12     'DANS L''ONGLET OPTION, LE SOUS-ANALYSE DOIT ETRE COCHE A "OUI"' categorie,
  13     code,
  14     [name],
  15     adddate,
  16     usrname,
  17     '' anomalie
  18 FROM oas_element
  19 WHERE
  20     cmpcode = 'AUF'
  21     AND elmlevel = 2
  22     AND deldate IS NULL
  23     AND endyear LIKE 0
  24     AND code LIKE '_____________'
  25     AND subanal <> 76  -- 76 = oui et 46 = non
  26
  27 UNION
  28
  29 SELECT
  30     'DANS L''ONGLET IMPUTATION, N''A PAS ETE CREE COMME ELEMENT DE GAUCHE' categorie,
  31     code,
  32     [name],
  33     adddate,
  34     usrname,
  35     CASE
  36         WHEN accounttype = 9 THEN 'compte de bilan'
  37         WHEN accounttype = 56 THEN 'compte de resultat'
  38     END anomalie
  39 FROM oas_element
  40 WHERE
  41     cmpcode = 'AUF'
  42     AND elmlevel = 2
  43     AND deldate IS NULL
  44     AND endyear LIKE 0
  45     AND code LIKE '_____________'
  46     AND accounttype <> 40  -- 40 veut dire element de gauche
  47
  48 UNION
  49
  50 SELECT
  51     'DANS L''ONGLET IMPUTATION,LA DESCRIPTION OBLIGATOIRE N''A PAS ETE COCHE' categorie,
  52     code,
  53     [name],
  54     adddate,
  55     usrname,
  56     '' anomalie
  57 FROM oas_element
  58 WHERE
  59     cmpcode = 'AUF'
  60     AND elmlevel = 2
  61     AND deldate IS NULL
  62     AND endyear LIKE 0
  63     AND code LIKE '_____________'
  64     AND descr <> 76 -- 76 veut dire oui obligatoire
  65
  66 UNION
  67
  68 SELECT
  69     'DANS L''ONGLET IMPUTATION, L''OPTION SYNTHESE DE COMPTE DOIT ETRE A "OPERATION"'
  70         AS categorie,
  71     code,
  72     [name],
  73     adddate,
  74     usrname,
  75     'accountsummary = ' + accountsummary  anomalie
  76 FROM oas_element
  77 WHERE
  78     cmpcode = 'AUF'
  79     AND elmlevel = 2
  80     AND deldate IS NULL
  81     -- elements actif et on enleve tout ce qui est plus ancien que l'annee en cours
  82     AND (endyear LIKE 0 OR YEAR(adddate) >= YEAR(GETDATE()))
  83     AND code LIKE '_____________'
  84     AND accountsummary <> 'OPERATION'
  85
  86 UNION
  87
  88 SELECT
  89     'DANS L''ONGLET IMPUTATION, L''OPTION ELEMENT ASSOCIE PAR DEFAULT A ETE COCHE' categorie,
  90     code,
  91     [name],
  92     adddate,
  93     usrname,
  94     'Niv. associes = ' + CONVERT(varchar(2),subslevel)  anomalie
  95 FROM oas_element
  96 WHERE
  97     cmpcode = 'AUF'
  98     AND elmlevel = 2
  99     AND deldate IS NULL
 100     AND endyear LIKE 0
 101     AND code LIKE '_____________'
 102     AND subslevel <> 3  -- 3 =  operation est associes a un element 3
 103
 104 UNION
 105
 106 SELECT
 107     'L''ELEMENT 3 ASSOCIE NE CORRESPOND PAS AUX 8 PREMIERS CARACTERES DE L''ELEMENT 2'
 108         AS categorie,
 109     code,
 110     [name],
 111     adddate,
 112     usrname,
 113     'élément 3 associe = ' + subselm anomalie
 114 FROM oas_element
 115 WHERE
 116     cmpcode = 'AUF'
 117     AND elmlevel = 2
 118     AND deldate IS NULL
 119     AND endyear LIKE 0
 120     AND code LIKE '_____________'
 121     AND LEFT(code,8) <> rtrim(subselm)
 122 GO