-USE [coda-prod]
+USE [coda-prod]
GO
SET ANSI_NULLS ON
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
-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)
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
-USE [coda-prod]
+USE [coda-prod]
GO
SET ANSI_NULLS OFF
'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 (
'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
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
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
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
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
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
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
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
) 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
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
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
)
OR endyear > YEAR(GETDATE())
)
- AND LEN(RTRIM(LTRIM(code))) <> 11
+ AND LEN(RTRIM(LTRIM(code))) NOT IN (11, 14)
GO
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
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
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
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
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
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
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'
)
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
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
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
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
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
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],
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
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,
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
projets / auf_coda_additions.git / commitdiff
