From 4e57e2064846887c00df33cca0d90671f96d9674 Mon Sep 17 00:00:00 2001 From: Eric Mc Sween Date: Mon, 10 Mar 2014 13:54:44 -0400 Subject: [PATCH] Modifications diverses --- db/procedures/auf_p_controleDeviseTBTC.sql | 377 +++++++++----------- db/procedures/auf_p_ctrlCptEcriture.sql | 313 ++++------------ .../auf_p_etat_resultats_p4_ex_precedent_2011.sql | 22 +- db/procedures/auf_p_intero_cumul_dep_rec.sql | 8 +- db/vues/auf_v_ControleBoursierBanques.sql | 7 +- db/vues/auf_v_controleBoursiers.sql | 14 +- db/vues/auf_v_controleTiersCDOS.sql | 16 +- db/vues/auf_v_controleTiersOperations.sql | 65 +--- db/vues/auf_v_web_ControleCleCpt.sql | 53 ++- 9 files changed, 315 insertions(+), 560 deletions(-) diff --git a/db/procedures/auf_p_controleDeviseTBTC.sql b/db/procedures/auf_p_controleDeviseTBTC.sql index ae4d7f1..61414e0 100644 --- a/db/procedures/auf_p_controleDeviseTBTC.sql +++ b/db/procedures/auf_p_controleDeviseTBTC.sql @@ -1,4 +1,4 @@ -USE [coda-prod] +USE [coda-prod] GO SET ANSI_NULLS ON @@ -6,240 +6,219 @@ GO SET QUOTED_IDENTIFIER ON GO - - - - - ---EXEC auf_p_controleDevise 0 ,0,'T' ---EXEC auf_p_controleDeviseTBTC 0,0 +-- EXEC auf_p_controleDeviseTBTC 0,0 ALTER PROCEDURE [dbo].[auf_p_controleDeviseTBTC] - - @exercice int, - @typepiece int - + @exercice int, + @typepiece int AS - SET NOCOUNT ON - CREATE TABLE #ErrDevise ( - - exercice int, - periode int, - el2 varchar(15), - descrL varchar(36) , - devElem varchar(5), - doccode varchar(12), - docnum varchar(12), - devDoc varchar(5), - valuedoc money, - usrname varchar(12), - etat_piece varchar(18) + exercice int, + periode int, + el2 varchar(15), + descrL varchar(36) , + devElem varchar(5), + doccode varchar(12), + docnum varchar(12), + devDoc varchar(5), + valuedoc money, + usrname varchar(12), + etat_piece varchar(18) ) - IF @typepiece = 0 AND @exercice = 0 - -INSERT INTO #ErrDevise -SELECT h.yr exercice, - h.period, - l.el2, - l.descr, - e.cur devElem, - h.doccode, - h.docnum, - h.curdoc devDoc, - l.valuedoc, - l.usrname, - CASE h.status - WHEN 77 THEN 'En cours de saisie' - WHEN 78 THEN 'Réel' - WHEN 79 THEN 'Annulée' - WHEN 116 THEN 'Corbeille' - ELSE 'Code réservé' END AS etat_piece - -FROM oas_dochead h, - oas_docline l, - oas_element e - -WHERE h.doccode= l.doccode AND - h.docnum = l.docnum AND - h.doccode <> 'G-CLOT-BILAN' AND - l.el2 LIKE 'T%' AND - h.curdoc <> e.cur AND - l.el2 = e.code AND - e.elmlevel = 2 AND - e.deldate IS NULL - + INSERT INTO #ErrDevise + SELECT + h.yr exercice, + h.period, + l.el2, + l.descr, + e.cur devElem, + h.doccode, + h.docnum, + h.curdoc devDoc, + l.valuedoc, + l.usrname, + CASE h.status + WHEN 77 THEN 'En cours de saisie' + WHEN 78 THEN 'Réel' + WHEN 79 THEN 'Annulée' + WHEN 116 THEN 'Corbeille' + ELSE 'Code réservé' + END AS etat_piece + FROM oas_dochead h, oas_docline l, oas_element e + WHERE + h.cmpcode = 'AUF' + AND l.cmpcode = 'AUF' + AND e.cmpcode = 'AUF' + AND h.doccode= l.doccode + AND h.docnum = l.docnum + AND h.doccode <> 'G-CLOT-BILAN' + AND l.el2 LIKE 'T%' + AND h.curdoc <> e.cur + AND l.el2 = e.code + AND e.elmlevel = 2 + AND e.deldate IS NULL ELSE IF @typepiece = 0 AND @exercice <> 0 - -INSERT INTO #ErrDevise -SELECT h.yr exercice, - h.period, - l.el2, - l.descr, - e.cur devElem, - h.doccode, - h.docnum, - h.curdoc devDoc, - l.valuedoc, - l.usrname, - CASE h.status - WHEN 77 THEN 'En cours de saisie' - WHEN 78 THEN 'Réel' - WHEN 79 THEN 'Annulée' - WHEN 116 THEN 'Corbeille' - ELSE 'Code réservé' END AS etat_piece - -FROM oas_dochead h, - oas_docline l, - oas_element e - -WHERE h.yr = @exercice AND - - h.doccode= l.doccode AND - h.docnum = l.docnum AND - h.doccode <> 'G-CLOT-BILAN' AND - l.el2 LIKE 'T%' AND - h.curdoc <> e.cur AND - l.el2 = e.code AND - e.elmlevel = 2 AND - e.deldate IS NULL - + INSERT INTO #ErrDevise + SELECT + h.yr exercice, + h.period, + l.el2, + l.descr, + e.cur devElem, + h.doccode, + h.docnum, + h.curdoc devDoc, + l.valuedoc, + l.usrname, + CASE h.status + WHEN 77 THEN 'En cours de saisie' + WHEN 78 THEN 'Réel' + WHEN 79 THEN 'Annulée' + WHEN 116 THEN 'Corbeille' + ELSE 'Code réservé' + END AS etat_piece + FROM oas_dochead h, oas_docline l, oas_element e + WHERE + h.cmpcode = 'AUF' + AND l.cmpcode = 'AUF' + AND e.cmpcode = 'AUF' + AND h.yr = @exercice + AND h.doccode= l.doccode + AND h.docnum = l.docnum + AND h.doccode <> 'G-CLOT-BILAN' + AND l.el2 LIKE 'T%' + AND h.curdoc <> e.cur + AND l.el2 = e.code + AND e.elmlevel = 2 + AND e.deldate IS NULL ELSE IF @typepiece <> 0 AND @exercice = 0 - -INSERT INTO #ErrDevise -SELECT h.yr exercice, - h.period, - l.el2, - l.descr, - e.cur devElem, - h.doccode, - h.docnum, - h.curdoc devDoc, - l.valuedoc, - l.usrname, - CASE h.status - WHEN 77 THEN 'En cours de saisie' - WHEN 78 THEN 'Réel' - WHEN 79 THEN 'Annulée' - WHEN 116 THEN 'Corbeille' - ELSE 'Code réservé' END AS etat_piece - -FROM oas_dochead h, - oas_docline l, - oas_element e - -WHERE - h.status = @typepiece AND - h.doccode= l.doccode AND - h.docnum = l.docnum AND - h.doccode <> 'G-CLOT-BILAN' AND - l.el2 LIKE 'T%' AND - h.curdoc <> e.cur AND - l.el2 = e.code AND - e.elmlevel = 2 AND - e.deldate IS NULL - - + INSERT INTO #ErrDevise + SELECT + h.yr exercice, + h.period, + l.el2, + l.descr, + e.cur devElem, + h.doccode, + h.docnum, + h.curdoc devDoc, + l.valuedoc, + l.usrname, + CASE h.status + WHEN 77 THEN 'En cours de saisie' + WHEN 78 THEN 'Réel' + WHEN 79 THEN 'Annulée' + WHEN 116 THEN 'Corbeille' + ELSE 'Code réservé' + END AS etat_piece + FROM oas_dochead h, oas_docline l, oas_element e + WHERE + h.cmpcode = 'AUF' + AND l.cmpcode = 'AUF' + AND e.cmpcode = 'AUF' + AND h.status = @typepiece + AND h.doccode = l.doccode + AND h.docnum = l.docnum + AND h.doccode <> 'G-CLOT-BILAN' + AND l.el2 LIKE 'T%' + AND h.curdoc <> e.cur + AND l.el2 = e.code + AND e.elmlevel = 2 + AND e.deldate IS NULL ELSE IF @typepiece <> 0 AND @exercice <> 0 - -INSERT INTO #ErrDevise -SELECT h.yr exercice, - h.period, - l.el2, - l.descr, - e.cur devElem, - h.doccode, - h.docnum, - h.curdoc devDoc, - l.valuedoc, - l.usrname, - CASE h.status - WHEN 77 THEN 'En cours de saisie' - WHEN 78 THEN 'Réel' - WHEN 79 THEN 'Annulée' - WHEN 116 THEN 'Corbeille' - ELSE 'Code réservé' END AS etat_piece - -FROM oas_dochead h, - oas_docline l, - oas_element e - -WHERE h.yr = @exercice AND - h.status = @typepiece AND - h.doccode= l.doccode AND - h.docnum = l.docnum AND - h.doccode <> 'G-CLOT-BILAN' AND - l.el2 LIKE 'T%' AND - h.curdoc <> e.cur AND - l.el2 = e.code AND - e.elmlevel = 2 AND - e.deldate IS NULL - + INSERT INTO #ErrDevise + SELECT + h.yr exercice, + h.period, + l.el2, + l.descr, + e.cur devElem, + h.doccode, + h.docnum, + h.curdoc devDoc, + l.valuedoc, + l.usrname, + CASE h.status + WHEN 77 THEN 'En cours de saisie' + WHEN 78 THEN 'Réel' + WHEN 79 THEN 'Annulée' + WHEN 116 THEN 'Corbeille' + ELSE 'Code réservé' + END AS etat_piece + FROM oas_dochead h, oas_docline l, oas_element e + WHERE + h.cmpcode = 'AUF' + AND l.cmpcode = 'AUF' + AND e.cmpcode = 'AUF' + AND h.yr = @exercice + AND h.status = @typepiece + AND h.doccode = l.doccode + AND h.docnum = l.docnum + AND h.doccode <> 'G-CLOT-BILAN' + AND l.el2 LIKE 'T%' + AND h.curdoc <> e.cur + AND l.el2 = e.code + AND e.elmlevel = 2 + AND e.deldate IS NULL -- suppression des ecritures dans la somme total par exercice/el2/devdoc/etat piece -SELECT exercice,el2,devDoc,etat_piece -INTO #Errsuppr - FROM #ErrDevise - GROUP BY exercice,el2,devDoc,etat_piece - HAVING SUM(valuedoc) = 0 +SELECT exercice, el2, devDoc, etat_piece +INTO #Errsuppr +FROM #ErrDevise +GROUP BY exercice, el2, devDoc, etat_piece +HAVING SUM(valuedoc) = 0 DELETE #ErrDevise - FROM #Errsuppr t -WHERE #ErrDevise.exercice = t.exercice AND - #ErrDevise.el2 = t.el2 AND - #ErrDevise.devDoc = t.devDoc AND - #ErrDevise.etat_piece = t.etat_piece +WHERE + #ErrDevise.exercice = t.exercice + AND #ErrDevise.el2 = t.el2 + AND #ErrDevise.devDoc = t.devDoc + AND #ErrDevise.etat_piece = t.etat_piece -- suppression des documents annulés ------ LA SOMME DU DOCUMENT INITIAL = LA SOMME DU DOCUMENT ANULLE------ - +-- LA SOMME DU DOCUMENT INITIAL = LA SOMME DU DOCUMENT ANULLE -- supprimer les documents annulés dont le doccode est comme '%-ANN%' -SELECT DISTINCT a.doccode doccodeA, a.docnum docnumA, - i.doccode doccodeI,i.docnum docnumI +SELECT DISTINCT + a.doccode doccodeA, a.docnum docnumA, i.doccode doccodeI, i.docnum docnumI INTO #DocAnnul -FROM #ErrDevise a,#ErrDevise i -WHERE a.doccode LIKE '%-ANN%' AND i.doccode LIKE '%-ANN%' AND - rtrim(a.descrl) LIKE '%' + i.doccode + ' ' + ltrim(rtrim(i.docnum)) +FROM #ErrDevise a,#ErrDevise i +WHERE + a.doccode LIKE '%-ANN%' + AND i.doccode LIKE '%-ANN%' + AND rtrim(a.descrl) LIKE '%' + i.doccode + ' ' + ltrim(rtrim(i.docnum)) DELETE #ErrDevise FROM #DocAnnul -WHERE ( doccode = doccodeA AND docnum = docnumA ) OR - ( doccode = doccodeI AND docnum = docnumI ) - +WHERE + (doccode = doccodeA AND docnum = docnumA) + OR (doccode = doccodeI AND docnum = docnumI) -- supprimer les autres documents annulles -SELECT DISTINCT a.doccode doccodeA, a.docnum docnumA, - i.doccode doccodeI,i.docnum docnumI +SELECT DISTINCT + a.doccode doccodeA, a.docnum docnumA, i.doccode doccodeI,i.docnum docnumI INTO #DocAnnul2 -FROM #ErrDevise a,#ErrDevise i -WHERE a.doccode LIKE '%-ANN%' AND - rtrim(a.descrl) LIKE '%' + i.doccode + ' ' + ltrim(rtrim(i.docnum)) +FROM #ErrDevise a, #ErrDevise i +WHERE + a.doccode LIKE '%-ANN%' + AND rtrim(a.descrl) LIKE '%' + i.doccode + ' ' + ltrim(rtrim(i.docnum)) DELETE #ErrDevise FROM #DocAnnul2 -WHERE ( doccode = doccodeA AND docnum = docnumA ) OR - ( doccode = doccodeI AND docnum = docnumI ) - - +WHERE + (doccode = doccodeA AND docnum = docnumA) + OR (doccode = doccodeI AND docnum = docnumI) SELECT * FROM #ErrDevise ORDER BY el2 - --by exercice,el2 + DROP TABLE #ErrDevise DROP TABLE #Errsuppr - - - - - - - GO diff --git a/db/procedures/auf_p_ctrlCptEcriture.sql b/db/procedures/auf_p_ctrlCptEcriture.sql index 10da112..219607c 100644 --- a/db/procedures/auf_p_ctrlCptEcriture.sql +++ b/db/procedures/auf_p_ctrlCptEcriture.sql @@ -1,32 +1,27 @@ -USE [coda-prod] +USE [coda-prod] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO -ALTER PROCEDURE [dbo].[auf_p_ctrlCptEcriture] - - @cmpcode varchar(12), - @pcg varchar(10), - @elm23 varchar(10), - @operation varchar(10), - @exrcice int, - @periode int +ALTER PROCEDURE [dbo].[auf_p_ctrlCptEcriture] + @cmpcode varchar(12), + @pcg varchar(10), + @elm23 varchar(144), + @operation varchar(10), + @exercice int, + @periode int AS - - -- execute auf_p_ctrlCptEcriture 'AUF','62510','2092RR','714',2008,7 --- cette procédure permet de controler les éléments (pcg, élément 2 et 3 ) et aussi --- les règles des éléments gauches et droites pour la clé comptable - - -DECLARE @elm2 varchar(12) -DECLARE @elm3 varchar(10) +DECLARE @elm2 varchar(72) +DECLARE @elm3 varchar(72) DECLARE @valeur varchar(72) DECLARE @valeur2 varchar(72) DECLARE @valeurfin varchar(72) @@ -40,263 +35,105 @@ DECLARE @startperiod smallint DECLARE @endyear smallint DECLARE @endperiod smallint - SET NOCOUNT ON SELECT @resultat = '' -IF ( ltrim(rtrim(@operation)) = '' ) +IF (ltrim(rtrim(@operation)) = '') BEGIN - SELECT @elm2 = @elm23 - SELECT @elm3 = '' + SELECT @elm2 = @elm23 + SELECT @elm3 = '' END - ELSE +ELSE BEGIN SELECT @elm2 = ltrim(rtrim(@elm23)) + ltrim(rtrim(@operation)) SELECT @elm3 = ltrim(rtrim(@elm23)) END ---- recuperer les maitres éléments - -SELECT code, - elmlevel, - deldate, - startyear, - startperiod, - endyear, - endperiod, - subanal - -INTO #elements - -FROM oas_element - -WHERE ( cmpcode = @cmpcode AND - ltrim(rtrim(code)) <> '' AND code is NOT null ) AND - ( ( code = @pcg AND elmlevel = 1 ) OR - ( code = @elm2 AND elmlevel = 2 ) OR - ( code = @elm3 AND elmlevel = 3 ) - ) - +-- recuperer les maitres éléments +SELECT code, elmlevel, deldate, startyear, startperiod, endyear, endperiod, subanal +INTO #elements +FROM oas_element +WHERE + cmpcode = @cmpcode + AND ltrim(rtrim(code)) <> '' + AND code is NOT null + AND ( + (code = @pcg AND elmlevel = 1) + OR (code = @elm2 AND elmlevel = 2) + OR (code = @elm3 AND elmlevel = 3) + ) ORDER BY elmlevel ---- recuperer les regles des éléments - -SELECT elmcode code , - elmlevel, - direction sense, - lstseqno ordre, - logical operLogic, - openb parDebut, - closeb parfin, - cffunction operateur, - cfvalue valeur - -INTO #regles - -FROM oas_rllist - -WHERE cmpcode = @cmpcode AND - ( ( elmcode = @pcg AND elmlevel = 1 ) OR - ( elmcode = @elm2 AND elmlevel = 2 ) OR - ( elmcode = @elm3 AND elmlevel = 3 ) - ) - - -- Vérifier si les éléments existent dans les maitres éléments +IF NOT exists ( SELECT code FROM #elements WHERE code = @pcg AND elmlevel = 1) + SELECT @resultat = @pcg + ' : PCG non existant' - IF NOT exists ( SELECT code FROM #elements WHERE code = @pcg AND elmlevel = 1) - SELECT @resultat = @pcg + ' : PCG non existant' - - IF ( ltrim(rtrim(@elm2)) <> '' ) - IF NOT exists ( SELECT code FROM #elements WHERE code = @elm2 AND elmlevel = 2) - SELECT @resultat = @resultat + ', ' + @elm2 + ' : élément 2 non existant' - - IF ( ltrim(rtrim(@elm3)) <> '' ) - IF NOT exists ( SELECT code FROM #elements WHERE code = @elm3 AND elmlevel = 3) - SELECT @resultat = @resultat + ', ' + @elm3 + ' : élément 3 non existant' - - - - -DECLARE listeElm cursor for -SELECT code, - elmlevel, - deldate, - startyear, - startperiod, - endyear, - endperiod, - subanal +IF ltrim(rtrim(@elm2)) <> '' + AND NOT exists (SELECT code FROM #elements WHERE code = @elm2 AND elmlevel = 2) + SELECT @resultat = @resultat + ', ' + @elm2 + ' : élément 2 non existant' - FROM #elements +IF ltrim(rtrim(@elm3)) <> '' + AND NOT exists (SELECT code FROM #elements WHERE code = @elm3 AND elmlevel = 3) + SELECT @resultat = @resultat + ', ' + @elm3 + ' : élément 3 non existant' -open listeElm +DECLARE listeElm CURSOR FOR +SELECT code, elmlevel, deldate, startyear, startperiod, endyear, endperiod, subanal +FROM #elements - fetch next FROM listeElm - INTO @code, - @elmlevel, - @deldate, - @startyear, - @startperiod, - @endyear, - @endperiod, - @subanal +OPEN listeElm +FETCH NEXT FROM listeElm +INTO @code, @elmlevel, @deldate, @startyear, @startperiod, @endyear, @endperiod, @subanal - while (@@fetch_status <> -1) - BEGIN --1 - - IF (@@fetch_status <> -2) - BEGIN --2 - - -- vérifier si l'élément est supprimé - IF ( @deldate is NOT null) - BEGIN - SELECT @resultat = ltrim(rtrim(@code)) + ' : est supprimé' - END - -- comparer les dates début et de fin vs exercice et période de l'ecriture - - IF ( ( @startyear <> 0 AND @exrcice < @startyear) OR ( @endyear <> 0 AND @exrcice > @endyear ) ) - SELECT @resultat = @code + ' : exercice de début et/ou de fin non valide' - - IF ( @startyear = @exrcice AND @startperiod > @periode ) - SELECT @resultat = @code + ' : période de début non valide' - - - - IF ( @endyear = @exrcice AND @endperiod < @periode ) - SELECT @resultat = @code + ' : période de fin non valide' +WHILE @@FETCH_STATUS = 0 + BEGIN + -- vérifier si l'élément est supprimé + IF (@deldate is NOT null) + SELECT @resultat = ltrim(rtrim(@code)) + ' : est supprimé' - -- vérifier liste gauches et droites des éléments + -- comparer les dates début et de fin vs exercice et période de l'ecriture + IF (@startyear <> 0 AND @exercice < @startyear) + OR (@endyear <> 0 AND @exercice > @endyear ) + SELECT @resultat = @code + ' : exercice de début et/ou de fin non valide' - -- si PCG non sous analysé - IF ( @elmlevel = 1 ) - BEGIN --el1 + IF (@startyear = @exercice AND @startperiod > @periode) + SELECT @resultat = @code + ' : période de début non valide' - --- si PCG non sous analysé - IF ( @elm2 <> '' AND @subanal = 46 ) - SELECT @resultat = @code + ' : PCG à ne pas sous analysé' + IF (@endyear = @exercice AND @endperiod < @periode) + SELECT @resultat = @code + ' : période de fin non valide' - --- si PCG sous analysé - IF ( @elm2 = '' AND @subanal = 76 ) - SELECT @resultat = @code + ' : PCG à sous analysé' - END + -- si PCG non sous analysé + IF (@elmlevel = 1) + BEGIN -- el1 - IF ( @elmlevel = 2 ) - BEGIN --el1 + -- si PCG non sous analysé + IF ( @elm2 <> '' AND @subanal = 46 ) + SELECT @resultat = @code + ' : PCG à ne pas sous analyser' - --- si élément 2 non sous analysé - IF ( @elm3 <> '' AND @subanal = 46 ) - SELECT @resultat = @code + ' : élément 2 à ne pas sous analysé' + -- si PCG sous analysé + IF ( @elm2 = '' AND @subanal = 76 ) + SELECT @resultat = @code + ' : PCG à sous analyser' - --- si élément 2 sous analysé - IF ( @elm3 = '' AND @subanal = 76 ) - SELECT @resultat = @code + ' : élément 2 à sous analysé' END - -- vérifier liste de droite du PCG et liste de gauche de l'élément 3 - --- si opérateur COMME - IF exists ( SELECT code FROM #regles WHERE code = @code AND - operateur = 6 AND ordre = 1 ) - BEGIN - - SELECT @valeur = ( select ltrim(rtrim(valeur)) - FROM #regles - WHERE code = @code AND operateur = 6 AND ordre = 1 ) - - SELECT @valeur = REPLACE ( @valeur , '*' , '%' ) - - IF exists ( SELECT code FROM #regles WHERE code = @code AND - operateur = 6 AND ordre = 2 ) - BEGIN - - SELECT @valeur2 = ( select ltrim(rtrim(valeur)) - FROM #regles - WHERE code = @code AND operateur = 6 AND ordre = 2 ) - - SELECT @valeur2 = REPLACE ( @valeur2 , '*' , '%' ) - - IF ( @elm2 NOT LIKE @valeur AND @elm2 NOT LIKE @valeur2) - BEGIN - IF ( @elmlevel = 1 ) - SELECT @resultat = @code + ' : élément droite ' + ltrim(rtrim(@elm2)) + ' non valide' - IF ( @elmlevel = 3 ) - SELECT @resultat = @code + ' : élément gauche ' + ltrim(rtrim(@elm2)) + ' non valide' - END - - END - ELSE - BEGIN - - IF ( @elm2 NOT LIKE @valeur ) - BEGIN - - IF ( @elmlevel = 1 ) - SELECT @resultat = @code + ' : élément droite ' + ltrim(rtrim(@elm2)) + ' non valide' - - IF ( @elmlevel = 3 ) - SELECT @resultat = @code + ' : élément gauche ' + ltrim(rtrim(@elm2)) + ' non valide' - - END - END - END - - - - --- si opérateur ENTRE avoir avec nathalie houle - IF exists ( SELECT code FROM #regles WHERE code = @code AND - operateur = 9 AND elmlevel = 1 AND ordre = 1 ) - BEGIN - - -- valeur début - SELECT @valeur = ( select ltrim(rtrim(valeur)) - FROM #regles - WHERE code = @code AND operateur = 9 - AND ordre = 1 ) - - SELECT @valeurfin = ( select ltrim(rtrim(valeur)) - FROM #regles - WHERE code = @code AND operateur = -1 AND ordre = 2 ) + IF ( @elmlevel = 2 ) + BEGIN - IF NOT ( @elm2 BETWEEN @valeur AND @valeurfin ) - BEGIN + -- si élément 2 non sous analysé + IF @elm3 <> '' AND @subanal = 46 + SELECT @resultat = @code + ' : élément 2 à ne pas sous analyser' - IF ( @elmlevel = 1 ) - SELECT @resultat = @code + ' : élément droite ' + ltrim(rtrim(@elm2)) + ' non valide' - IF ( @elmlevel = 3 ) - SELECT @resultat = @code + ' : élément gauche ' + ltrim(rtrim(@elm2)) + ' non valide' - END + -- si élément 2 sous analysé + IF @elm3 = '' AND @subanal = 76 + SELECT @resultat = @code + ' : élément 2 à sous analyser' - END - - END -- 2 - - - - fetch next FROM listeElm - INTO @code, - @elmlevel, - @deldate, - @startyear, - @startperiod, - @endyear, - @endperiod, - @subanal - END -- 1 + END + FETCH NEXT FROM listeElm + INTO @code, @elmlevel, @deldate, @startyear, @startperiod, @endyear, @endperiod, @subanal + END -DROP TABLE #regles DROP TABLE #elements - - SELECT @resultat AS resultat - - - - - - - - GO 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 6909600..c361409 100644 --- a/db/procedures/auf_p_etat_resultats_p4_ex_precedent_2011.sql +++ b/db/procedures/auf_p_etat_resultats_p4_ex_precedent_2011.sql @@ -1,4 +1,4 @@ -USE [coda-prod] +USE [coda-prod] GO SET ANSI_NULLS OFF @@ -269,14 +269,17 @@ SELECT '000-PRODUITS' AS groupe2, b.full_value * -1 AS solde, -- Corriger pour debit / crédit !!!!!!!!!! CASE - WHEN ( + WHEN b.el1 IN ('75100', '70500') OR b.el1 LIKE '756%' OR b.el1 LIKE '708%' - ) THEN '110-Ressources propres' + THEN '110-Ressources propres' WHEN b.el1 LIKE '740%' THEN '120-Contributions gouvernementales' - WHEN b.el1 LIKE '745%' THEN '130-Contributions contractuelles' - WHEN ( + WHEN + b.el1 LIKE '745%' + OR b.el1 = '74998' + THEN '130-Contributions contractuelles' + WHEN b.el1 LIKE '78%' OR b.el1='75700' OR ( @@ -292,12 +295,13 @@ SELECT '91022GR', '91024GR', '91023GR', '91032GR','91025GR', '9122GR', '9124GR', '9123GR', '9132GR','9125GR' ) - ) THEN '140-Apports, autres recettes, provisions' - WHEN ( + THEN '140-Apports, autres recettes, provisions' + WHEN b.el1 LIKE '76%' OR b.el1 LIKE '77%' - ) THEN '150-Produits financiers et exceptionnels' - WHEN b.el1 LIKE '87%' THEN '160-Valeurs des biens et services à titre gratuit' + THEN '150-Produits financiers et exceptionnels' + WHEN b.el1 = '75800' THEN '160-Exploitation' + WHEN b.el1 LIKE '87%' THEN '170-Valeurs des biens et services à titre gratuit' END AS sgroupe FROM #cumul_ecritures b WHERE -- Filtres globaux diff --git a/db/procedures/auf_p_intero_cumul_dep_rec.sql b/db/procedures/auf_p_intero_cumul_dep_rec.sql index ba4cbac..8a3bbfe 100644 --- a/db/procedures/auf_p_intero_cumul_dep_rec.sql +++ b/db/procedures/auf_p_intero_cumul_dep_rec.sql @@ -137,14 +137,12 @@ WHERE AND yr = @exercice AND period BETWEEN 1 AND @periode AND curcode = 'EUR' - AND repbasis = 5 + AND repbasis = 4 AND balcode = 'ENGAGEMENT' AND el1 NOT LIKE '4%' AND el1 NOT LIKE '96%' AND el1 NOT LIKE '97%' AND el1 NOT LIKE '98%' - AND len(rtrim(el3)) <> 8 - AND LEFT(el3,2) <> '95' AND el3 LIKE @like_el3 AND el3 = imp.code AND imp.elmlevel = 3 @@ -199,7 +197,9 @@ FROM ON cumul.el3 = e.code AND e.cmpcode = 'AUF' AND e.elmlevel = 3 -WHERE LEFT(cumul.el3,2) <> '95' +WHERE + (@exercice < 2014 AND LEN(cumul.el3) = 7 AND LEFT(cumul.el3,2) <> '95') + OR (@exercice >= 2014 AND LEN(cumul.el3) = 8 AND LEFT(cumul.el3,1) <> 'P') GROUP BY cumul.el3, e.sname, e.[name] ORDER BY cumul.el3 GO diff --git a/db/vues/auf_v_ControleBoursierBanques.sql b/db/vues/auf_v_ControleBoursierBanques.sql index e28f5a6..4621a37 100644 --- a/db/vues/auf_v_ControleBoursierBanques.sql +++ b/db/vues/auf_v_ControleBoursierBanques.sql @@ -1,14 +1,11 @@ USE [coda-prod] GO -DROP VIEW [dbo].[auf_v_ControleBoursierBanques] -GO - SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -CREATE VIEW [dbo].[auf_v_ControleBoursierBanques] +ALTER VIEW [dbo].[auf_v_ControleBoursierBanques] AS SELECT TOP 100 PERCENT E.code, B.nb_comptes FROM dbo.oas_element E LEFT OUTER JOIN @@ -18,6 +15,4 @@ FROM dbo.oas_element E LEFT OUTER JOIN GROUP BY elmcode) B ON E.code = B.elmcode WHERE (E.elmlevel = 2) AND (E.deldate IS NULL) AND (E.endyear LIKE 0) AND (E.code LIKE '%L') ORDER BY B.nb_comptes DESC - - GO diff --git a/db/vues/auf_v_controleBoursiers.sql b/db/vues/auf_v_controleBoursiers.sql index c2f3c24..1a14fe5 100644 --- a/db/vues/auf_v_controleBoursiers.sql +++ b/db/vues/auf_v_controleBoursiers.sql @@ -30,7 +30,7 @@ WHERE OR endyear > YEAR(GETDATE()) ) AND code LIKE '%L' - AND len(rtrim(ltrim(code))) = 11 + AND len(rtrim(ltrim(code))) IN (11, 14) AND subanal <> 76 -- 76 = oui et 46 = non UNION @@ -59,7 +59,7 @@ WHERE OR endyear > YEAR(GETDATE()) ) AND code LIKE '%L' - AND len(rtrim(ltrim(code))) = 11 + AND len(rtrim(ltrim(code))) IN (11, 14) AND accounttype <> 40 -- 40 veut dire element de gauche UNION @@ -85,7 +85,7 @@ WHERE OR endyear > YEAR(GETDATE()) ) AND code LIKE '%L' - AND len(rtrim(ltrim(code))) = 11 + AND len(rtrim(ltrim(code))) IN (11, 14) AND descr <> 76 -- 76 veut dire oui obligatoire UNION @@ -110,7 +110,7 @@ WHERE ) OR endyear > YEAR(GETDATE()) ) AND code LIKE '%L' - AND len(rtrim(ltrim(code))) = 11 + AND len(rtrim(ltrim(code))) IN (11, 14) AND subslevel <> 3 -- 3 = Fournisseur est associes a un autre element 3 UNION @@ -140,7 +140,7 @@ WHERE OR endyear > YEAR(GETDATE()) ) AND code LIKE '%L' - AND len(rtrim(ltrim(code))) = 11 + AND len(rtrim(ltrim(code))) IN (11, 14) AND (matchable <> 76 OR statpay <> 84) UNION @@ -168,7 +168,7 @@ WHERE OR endyear > YEAR(GETDATE()) ) AND code LIKE '%L' - AND len(rtrim(ltrim(code))) = 11 + AND len(rtrim(ltrim(code))) IN (11, 14) AND paper <> 76 AND elec <> 76 @@ -195,6 +195,6 @@ WHERE ) OR endyear > YEAR(GETDATE()) ) - AND LEN(RTRIM(LTRIM(code))) <> 11 + AND LEN(RTRIM(LTRIM(code))) NOT IN (11, 14) GO diff --git a/db/vues/auf_v_controleTiersCDOS.sql b/db/vues/auf_v_controleTiersCDOS.sql index e0b14e8..e51b933 100644 --- a/db/vues/auf_v_controleTiersCDOS.sql +++ b/db/vues/auf_v_controleTiersCDOS.sql @@ -26,7 +26,7 @@ WHERE code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR - code LIKE 's%' + code LIKE 'S_______' ) AND subanal <> 46 -- 76 = oui et 46 = non @@ -52,7 +52,7 @@ WHERE code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR - code LIKE 's%' + code LIKE 'S_______' ) AND accounttype <> 40 -- 40 veut dire element de gauche @@ -75,7 +75,7 @@ WHERE code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR - code LIKE 's%' + code LIKE 'S_______' ) AND descr <> 76 -- 76 veut dire oui obligatoire @@ -99,7 +99,7 @@ WHERE code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR - code LIKE 's%' + code LIKE 'S_______' ) AND subslevel <> 0 -- 0 = Fournisseur est associes a un autre element @@ -149,7 +149,7 @@ WHERE AND ( code LIKE 'c%' OR code LIKE 'o%' - OR (code LIKE 's%' AND len(code) > 5) + OR (code LIKE 'S_______' AND len(code) > 5) ) AND ( matchable <> 76 @@ -183,7 +183,7 @@ WHERE AND ( code LIKE 'd%' OR code LIKE 'cgrm%' - OR (code LIKE 'S%' AND len (code) < 5) + OR (code LIKE 'S_______' AND len (code) < 5) ) AND ( matchable <> 76 @@ -209,7 +209,7 @@ WHERE AND elmlevel = 2 AND deldate IS NULL AND endyear LIKE 0 - AND (code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR code LIKE 's%') + AND (code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR code LIKE 'S_______') AND code NOT IN ( 'SCA22143','SCA22151','SGA12003','CGRM451','CGRM462','CGRM448', 'SVU11623' ) @@ -230,6 +230,6 @@ WHERE AND elmlevel = 2 AND deldate IS NULL AND endyear LIKE 0 - AND (code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR code LIKE 's%') + AND (code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR code LIKE 'S_______') AND discenable <> 46 GO diff --git a/db/vues/auf_v_controleTiersOperations.sql b/db/vues/auf_v_controleTiersOperations.sql index 2510d0c..f6f6093 100644 --- a/db/vues/auf_v_controleTiersOperations.sql +++ b/db/vues/auf_v_controleTiersOperations.sql @@ -21,15 +21,7 @@ WHERE AND elmlevel = 2 AND deldate IS NULL AND endyear LIKE 0 - AND code NOT LIKE 'c%' - AND code NOT LIKE 'F%' - AND code NOT LIKE 'd%' - AND code NOT LIKE 'o%' - AND code NOT LIKE 's%' - AND code NOT LIKE 't%' - AND code NOT LIKE '%L' - AND code NOT LIKE 'MODELE-%' - AND code <> '8888ZZ000' + AND code LIKE '_____________' AND subanal <> 76 -- 76 = oui et 46 = non UNION @@ -50,15 +42,7 @@ WHERE AND elmlevel = 2 AND deldate IS NULL AND endyear LIKE 0 - AND code NOT LIKE 'c%' - AND code NOT LIKE 'F%' - AND code NOT LIKE 'd%' - AND code NOT LIKE 'o%' - AND code NOT LIKE 's%' - AND code NOT LIKE 't%' - AND code NOT LIKE '%L' - AND code NOT LIKE 'MODELE-%' - AND code <> '8888ZZ000' + AND code LIKE '_____________' AND accounttype <> 40 -- 40 veut dire element de gauche UNION @@ -76,15 +60,7 @@ WHERE AND elmlevel = 2 AND deldate IS NULL AND endyear LIKE 0 - AND code NOT LIKE 'c%' - AND code NOT LIKE 'F%' - AND code NOT LIKE 'd%' - AND code NOT LIKE 'o%' - AND code NOT LIKE 's%' - AND code NOT LIKE 't%' - AND code NOT LIKE '%L' - AND code NOT LIKE 'MODELE-%' - AND code <> '8888ZZ000' + AND code LIKE '_____________' AND descr <> 76 -- 76 veut dire oui obligatoire UNION @@ -104,15 +80,7 @@ WHERE 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 NOT LIKE 'c%' - AND code NOT LIKE 'F%' - AND code NOT LIKE 'd%' - AND code NOT LIKE 'o%' - AND code NOT LIKE 's%' - AND code NOT LIKE 't%' - AND code NOT LIKE '%L' - AND code NOT LIKE 'MODELE-%' - AND code <> '8888ZZ000' + AND code LIKE '_____________' AND accountsummary <> 'OPERATION' UNION @@ -130,21 +98,13 @@ WHERE AND elmlevel = 2 AND deldate IS NULL AND endyear LIKE 0 - AND code NOT LIKE 'c%' - AND code NOT LIKE 'F%' - AND code NOT LIKE 'd%' - AND code NOT LIKE 'o%' - AND code NOT LIKE 's%' - AND code NOT LIKE 't%' - AND code NOT LIKE '%L' - AND code NOT LIKE 'MODELE-%' - AND code <> '8888ZZ000' + AND code LIKE '_____________' AND subslevel <> 3 -- 3 = operation est associes a un element 3 UNION SELECT - 'L''ELEMENT 3 ASSOCIE NE CORRESPOND PAS AUX 7 PREMIERS CARACTERES DE L''ELEMENT 2' + 'L''ELEMENT 3 ASSOCIE NE CORRESPOND PAS AUX 8 PREMIERS CARACTERES DE L''ELEMENT 2' AS categorie, code, [name], @@ -157,15 +117,6 @@ WHERE AND elmlevel = 2 AND deldate IS NULL AND endyear LIKE 0 - AND code NOT LIKE 'c%' - AND code NOT LIKE 'F%' - AND code NOT LIKE 'd%' - AND code NOT LIKE 'o%' - AND code NOT LIKE 's%' - AND code NOT LIKE 't%' - AND code NOT LIKE '%L' - AND code NOT LIKE 'MODELE-%' - AND code <> '8888ZZ000' - AND LEFT(code,7) <> rtrim(subselm) - AND len(code) < 11 + AND code LIKE '_____________' + AND LEFT(code,8) <> rtrim(subselm) GO diff --git a/db/vues/auf_v_web_ControleCleCpt.sql b/db/vues/auf_v_web_ControleCleCpt.sql index 48c8332..e96992c 100644 --- a/db/vues/auf_v_web_ControleCleCpt.sql +++ b/db/vues/auf_v_web_ControleCleCpt.sql @@ -1,19 +1,12 @@ USE [coda-prod] GO -DROP VIEW [dbo].[auf_v_web_ControleCleCpt] -GO - SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO - - - -CREATE VIEW [dbo].[auf_v_web_ControleCleCpt] -AS +ALTER VIEW [dbo].[auf_v_web_ControleCleCpt] AS SELECT ETAT_PIECE, ELEMENT2, ELEMENT1, @@ -32,34 +25,30 @@ SELECT ETAT_PIECE, CONVERT(varchar(4), EXERCICE) + ETAT_PIECE AS exerciceEtat, CONVERT(varchar(4), EXERCICE) + ETAT_PIECE + ELEMENT2 + ELEMENT3 + ELEMENT1 + DEVISE_DOCUMENT AS el2el3el1, ELEMENT2 + ELEMENT3 AS el2el3 - -FROM IMP_LIGNES , (SELECT h.yr, el1, el2, el3 - FROM oas_dochead h, oas_docline l - WHERE h.doccode = l.doccode AND - h.docnum = l.docnum AND - h.doccode <> 'G-CLOT-RESUL' AND - h.doccode <> 'G-CLOT-BILAN' AND - (SUBSTRING(el2, 1, 1) <= '9') AND - (SUBSTRING(el1, 1, 1) <> '5') AND - (SUBSTRING(el1, 1, 1) <> '9') AND - (SUBSTRING(el2, 1, 6) <> LEFT(el3,6)) - -- AND len(ltrim(rtrim(el3))) <> 0 - GROUP BY h.yr,el1,el2,el3 - HAVING SUM(valuedoc) <> 0 ) errEl23 - - +FROM + IMP_LIGNES, + ( + SELECT h.yr, el1, el2, el3 + FROM oas_dochead h, oas_docline l + WHERE + h.doccode = l.doccode + AND h.docnum = l.docnum + AND h.doccode <> 'G-CLOT-RESUL' + AND h.doccode <> 'G-CLOT-BILAN' + AND el1 NOT LIKE '5%' + AND el1 NOT LIKE '9%' + AND el2 LIKE '[0-9]%' + AND ( + LEN(el3) = 0 + OR LEFT(el2, LEN(el3)) <> el3 + ) + GROUP BY h.yr,el1,el2,el3 + HAVING SUM(valuedoc) <> 0 + ) errEl23 WHERE IMP_LIGNES.SOLDE_DEV_DOC <> 0 AND IMP_LIGNES.EXERCICE = errEl23.yr AND IMP_LIGNES.ELEMENT1 = errEl23.el1 AND IMP_LIGNES.ELEMENT2 = errEl23.el2 AND IMP_LIGNES.ELEMENT3 = errEl23.el3 AND IMP_LIGNES.EXERCICE >= 2011 - - - - - - - - GO -- 1.7.10.4