Modifs qui n'avaient pas été enregistrées...

diff --git a/db/procedures/auf_p_controle_Bilan.sql b/db/procedures/auf_p_controle_Bilan.sql
index dd184e4..4bcad48 100644 (file)
--- a/db/procedures/auf_p_controle_Bilan.sql
+++ b/db/procedures/auf_p_controle_Bilan.sql
@@ -225,7 +225,8 @@ SELECT
     l.groupe,
     CASE
         WHEN
-            bal.el2 IS NULL OR ltrim(rtrim(bal.el2)) =''
+            bal.el2 IS NULL
+            OR ltrim(rtrim(bal.el2)) = ''
             THEN l.pcg + ' - ' + (
                 SELECT [name]
                 FROM oas_element
@@ -234,7 +235,18 @@ SELECT
                     AND code = l.pcg
                     AND elmlevel = 1
             )
-        ELSE l.pcg + ' - ' + bal.el2 + '  : ' + el.[name]
+        WHEN
+            bal.el1 BETWEEN '13000' AND '13999'
+            OR bal.el1 BETWEEN '20000' AND '29999'
+            THEN l.pcg + ' - ' + bal.el2 + ' : ' + (
+                SELECT [name]
+                FROM oas_element
+                WHERE
+                    cmpcode = 'AUF'
+                    AND code = l.pcg
+                    AND elmlevel = 1
+            )
+        ELSE l.pcg + ' - ' + bal.el2 + ' : ' + el.[name]
     END libelle,
     bal.full_value AS montant,
     'EUR' devise
@@ -275,7 +287,29 @@ SELECT
     CASE
         WHEN l.pcg = '58000' THEN l.pcg + ' - Virements internes de l''implantation'
         WHEN l.pcg = '58100' THEN l.pcg + ' - Virements internes entre implantation'
-        ELSE l.pcg + ' - ' + bal.el2 + '  : ' + el.[name]
+        WHEN
+            bal.el2 IS NULL
+            OR ltrim(rtrim(bal.el2)) = ''
+            THEN l.pcg + ' - ' + (
+                SELECT [name]
+                FROM oas_element
+                WHERE
+                    cmpcode = 'AUF'
+                    AND code = l.pcg
+                    AND elmlevel = 1
+            )
+        WHEN
+            bal.el1 BETWEEN '13000' AND '13999'
+            OR bal.el1 BETWEEN '20000' AND '29999'
+            THEN l.pcg + ' - ' + bal.el2 + ' : ' + (
+                SELECT [name]
+                FROM oas_element
+                WHERE
+                    cmpcode = 'AUF'
+                    AND code = l.pcg
+                    AND elmlevel = 1
+            )
+        ELSE l.pcg + ' - ' + bal.el2 + ' : ' + el.[name]
     END libelle,
     bal.full_value AS montant,
     bal.curcode AS devise

diff --git a/db/procedures/auf_p_detailBilan.sql b/db/procedures/auf_p_detailBilan.sql
index 30e7a0b..a6dd5ec 100644 (file)
--- a/db/procedures/auf_p_detailBilan.sql
+++ b/db/procedures/auf_p_detailBilan.sql
@@ -6,44 +6,26 @@ GO
 SET QUOTED_IDENTIFIER ON
 GO
 
-
 ALTER PROCEDURE [dbo].[auf_p_detailBilan]
-
-
-              @exercice int,
-                      @periode int
-
+@exercice int,
+@periode int
 AS
 
 SET NOCOUNT ON  --- tres important pour afficher le resultat sur EXCEL
 
-SELECT yr,
-       el1,
-       el2,
-       el3,
-       [name],
-
-            CASE WHEN    el1 = '50700' OR el1 = '50800'
-
-                    THEN 'Placements temporaires'  -- Placements temporaires
-
-
-            WHEN    el1 = '48600'
-
-                    THEN 'Frais payés d''avance et dépots'  -- Frais payés d'avance et dépots
-
-
-            WHEN    el1 = '48700'
-
-                    THEN 'Produits reportés'  -- Produits reporté
-
-            WHEN    el1 LIKE '2%'
-
-                    THEN 'Immobilisations'  -- Immobilisations
-
-
-          END  groupe,
-          full_value
+SELECT
+    yr, el1, el2, el3, [name],
+    CASE
+        WHEN el1 = '50700' OR el1 = '50800'
+            THEN 'Placements temporaires'
+        WHEN el1 = '48600'
+            THEN 'Frais payés d''avance et dépots'
+        WHEN el1 = '48700'
+            THEN 'Produits reportés'
+        WHEN el1 LIKE '2%'
+            THEN 'Immobilisations'
+    END  groupe,
+    full_value
 
 INTO #bilan
 

diff --git a/db/procedures/auf_p_etat_resultats_p4_ex_precedent_2011.sql b/db/procedures/auf_p_etat_resultats_p4_ex_precedent_2011.sql
index 10b471f..13781dd 100644 (file)
--- a/db/procedures/auf_p_etat_resultats_p4_ex_precedent_2011.sql
+++ b/db/procedures/auf_p_etat_resultats_p4_ex_precedent_2011.sql
@@ -28,7 +28,15 @@ DECLARE @publication varchar(3)
 
 SET NOCOUNT ON
 
---- extraire la catégorie de 'état
+-- Cas spécial à partir de 2014
+
+IF @exercice >= 2014
+    BEGIN
+        EXEC auf_p_etat_resultats_p4_ex_precedent_2014 @exercice, @periode, @code, @retraitement
+        RETURN
+    END
+
+-- extraire la catégorie de 'état
 
 SELECT @categorie = LEFT(ltrim(@code),1)
 

diff --git a/db/procedures/auf_p_interro_detail.sql b/db/procedures/auf_p_interro_detail.sql
index c6a4904..940d3f3 100644 (file)
--- a/db/procedures/auf_p_interro_detail.sql
+++ b/db/procedures/auf_p_interro_detail.sql
@@ -82,6 +82,7 @@ SELECT
 FROM oas_dochead h, oas_docline l
 WHERE
     h.cmpcode = 'AUF'
+    AND h.status = 78
     AND l.cmpcode = 'AUF'
     AND h.yr = @annee
     AND h.period BETWEEN 1 AND 12

diff --git a/db/vues/auf_v_controleTiersOperations.sql b/db/vues/auf_v_controleTiersOperations.sql
index f6f6093..23954a4 100644 (file)
--- a/db/vues/auf_v_controleTiersOperations.sql
+++ b/db/vues/auf_v_controleTiersOperations.sql
@@ -1,4 +1,4 @@
-USE [coda-prod]
+USE [coda-prod]
 GO
 SET ANSI_NULLS ON
 GO
@@ -66,26 +66,6 @@ WHERE
 UNION
 
 SELECT
-    'DANS L''ONGLET IMPUTATION, L''OPTION SYNTHESE DE COMPTE DOIT ETRE A "OPERATION"'
-        AS categorie,
-    code,
-    [name],
-    adddate,
-    usrname,
-    'accountsummary = ' + accountsummary  anomalie
-FROM oas_element
-WHERE
-    cmpcode = 'AUF'
-    AND elmlevel = 2
-    AND deldate IS NULL
-    -- elements actif et on enleve tout ce qui est plus ancien que l'annee en cours
-    AND (endyear LIKE 0 OR YEAR(adddate) >= YEAR(GETDATE()))
-    AND code LIKE '_____________'
-    AND accountsummary <> 'OPERATION'
-
-UNION
-
-SELECT
     'DANS L''ONGLET IMPUTATION, L''OPTION ELEMENT ASSOCIE PAR DEFAULT A ETE COCHE' categorie,
     code,
     [name],

diff --git a/db/vues/auf_v_p4_projets_groupes.sql b/db/vues/auf_v_p4_projets_groupes.sql
index d33a073..d8897bf 100644 (file)
--- a/db/vues/auf_v_p4_projets_groupes.sql
+++ b/db/vues/auf_v_p4_projets_groupes.sql
@@ -1,14 +1,11 @@
 USE [coda-prod]
 GO
 
-DROP VIEW [dbo].[auf_v_p4_projets_groupes]
-GO
-
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
-CREATE VIEW [dbo].[auf_v_p4_projets_groupes]
+ALTER VIEW [dbo].[auf_v_p4_projets_groupes]
 AS
 
 SELECT
@@ -45,7 +42,7 @@ FROM
    gl2.cmpcode  = e1.cmpcode
    AND gl2.code  = e1.code
    AND gl2.elmlevel = e1.elmlevel
-   AND gl2.grpcode LIKE 'H%'
+   AND gl2.grpcode LIKE 'N%'
 
 
   left outer join oas_grplist gl3 on
@@ -76,7 +73,7 @@ WHERE
 
         e1.cmpcode   = 'AUF' AND
         e1.elmlevel = 3 AND
-        len(ltrim(rtrim(e1.code))) = 7 AND
+        len(ltrim(rtrim(e1.code))) = 8 AND
         e1.deldate is  null
       AND (e1.endyear = 0) AND (e1.endperiod = 0)