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
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
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
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
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)
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
-USE [coda-prod]
+USE [coda-prod]
GO
SET ANSI_NULLS ON
GO
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],
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
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
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)