-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
projets / auf_coda_additions.git / blobdiff