From: Eric Mc Sween Date: Tue, 24 Mar 2015 18:32:22 +0000 (-0400) Subject: Ajouté le rapport de consommation X-Git-Url: http://git.auf.org/?p=auf_coda_additions.git;a=commitdiff_plain;h=1c38b4f0e835203f5bcf3415ab55915fefcffacf Ajouté le rapport de consommation --- diff --git a/rapports/consommation.sql b/rapports/consommation.sql new file mode 100644 index 0000000..45649ec --- /dev/null +++ b/rapports/consommation.sql @@ -0,0 +1,689 @@ +-- Engagements non-rapprochés par numéro de commande et el3 {{{1 + +DECLARE @engagements_non_rapproches TABLE ( + code_commande VARCHAR(12) NOT NULL, + num_commande VARCHAR(32) NOT NULL, + el3 VARCHAR(8), + exercice SMALLINT NOT NULL, + montant MONEY NOT NULL, + UNIQUE (code_commande, num_commande, el3) +); + +INSERT INTO @engagements_non_rapproches +SELECT + odoccode AS code_commande, + odocnum AS num_commande, + el3, + balyp_yr AS exercice, + SUM(homevalue) AS montant +FROM pop_commitments +WHERE + doctype = 31997 -- Engagements + AND balyp_yr IN (2014, 2015) + AND matchid IS NULL +GROUP BY odoccode, odocnum, el3, balyp_yr +HAVING SUM(homevalue) != 0; + +-- Engagements rapprochés par matchid et el3 {{{1 + +DECLARE @engagements_rapproches TABLE ( + matchid NUMERIC(28, 0) NOT NULL, + el3 VARCHAR(8), + exercice SMALLINT NOT NULL, + exercice_reception SMALLINT NOT NULL, + montant MONEY NOT NULL, + UNIQUE (matchid, el3) +); + +INSERT INTO @engagements_rapproches +SELECT + c.matchid, + c.el3, + c.balyp_yr AS exercice, + gh.docyearperiod_yr AS exercice_reception, + SUM(c.homevalue) AS montant +FROM + pop_commitments c + INNER JOIN pim_mline ml ON ml.mlineid = c.matchid + INNER JOIN pim_line l ON l.lineid = ml.lrnlnlineid + INNER JOIN pim_lrnhead h ON h.documentid = l.docdocumentid + INNER JOIN pop_grnhead gh + ON gh.doccode = h.documentcode + AND gh.docnum = h.documentnumber +WHERE + c.doctype = 31997 + AND c.balyp_yr IN (2014, 2015) + AND c.matchid IS NOT NULL +GROUP BY c.matchid, c.el3, c.balyp_yr, gh.docyearperiod_yr +HAVING SUM(homevalue) != 0 + +-- Montants réceptionnés par commande et el3 {{{1 + +DECLARE @receptions TABLE ( + code_commande VARCHAR(12) NOT NULL, + num_commande VARCHAR(32) NOT NULL, + el3 VARCHAR(8), + montant_2014 MONEY NOT NULL, + montant_2015 MONEY NOT NULL, + UNIQUE (code_commande, num_commande, el3) +); + +INSERT INTO @receptions +SELECT + ll.orderdocumentcode AS code_commande, + ll.ordernumber AS num_commande, + LEFT(ll.departmentmastercode, 8) AS el3, + SUM( + CASE + WHEN h.yearperiod_yr = 2014 + THEN ROUND( + ll.unmatchedvaluevalue / + (ll.documentrate_rate / 10000000) * + ( + 1 + + (COALESCE(t.rate, 0) / 1000000000) * + (1 - COALESCE(t.rec_percent, 0) / 1000000000) + ) + , 2) + ELSE 0 + END + ) AS montant_2014, + SUM( + CASE + WHEN h.yearperiod_yr = 2015 + THEN ROUND( + ll.unmatchedvaluevalue / + (ll.documentrate_rate / 10000000) * + ( + 1 + + (COALESCE(t.rate, 0) / 1000000000) * + (1 - COALESCE(t.rec_percent, 0) / 1000000000) + ) + , 2) + ELSE 0 + END + ) AS montant_2015 +FROM pim_lrnline ll + INNER JOIN pim_line l ON l.lineid = ll.lineid + INNER JOIN pim_dochead h ON h.documentid = l.docdocumentid + LEFT JOIN oas_taxlist t + ON t.cmpcode = 'AUF' + AND t.taxcode = l.linetaxcode + AND t.effect_date = ( + SELECT MAX(effect_date) + FROM oas_taxlist + WHERE cmpcode = 'AUF' AND taxcode = l.linetaxcode + ) +WHERE + ll.orderdocumentcode IS NOT NULL + AND ll.ordernumber IS NOT NULL +GROUP BY + ll.orderdocumentcode, + ll.ordernumber, + LEFT(ll.departmentmastercode, 8); + +-- Engagements non rapprochés par el3 {{{1 + +DECLARE @engagements_non_rapproches_el3 TABLE ( + el3 VARCHAR(8), + eng2014_rec2014 MONEY NOT NULL, + eng2014_rec2015 MONEY NOT NULL, + eng2015_rec2015 MONEY NOT NULL, + eng2014_non_receptionnes MONEY NOT NULL, + eng2015_non_receptionnes MONEY NOT NULL, + UNIQUE (el3) +); + +INSERT INTO @engagements_non_rapproches_el3 +SELECT + e.el3, + SUM( + CASE + WHEN e.exercice = 2014 THEN COALESCE(r.montant_2014, 0) + ELSE 0 + END + ) AS eng2014_rec2014, + SUM( + CASE + WHEN e.exercice = 2014 THEN COALESCE(r.montant_2015, 0) + ELSE 0 + END + ) AS eng2014_rec2015, + SUM( + CASE + WHEN e.exercice = 2015 THEN COALESCE(r.montant_2015, 0) + ELSE 0 + END + ) AS eng2015_rec2015, + SUM( + CASE + WHEN e.exercice = 2014 + THEN e.montant - COALESCE(r.montant_2014 + r.montant_2015, 0) + ELSE 0 + END + ) AS eng2014_non_receptionnes, + SUM( + CASE + WHEN e.exercice = 2015 + THEN e.montant - COALESCE(r.montant_2014 + r.montant_2015, 0) + ELSE 0 + END + ) AS eng2015_non_receptionnes +FROM + @engagements_non_rapproches e + LEFT JOIN @receptions r + ON e.code_commande = r.code_commande + AND e.num_commande = r.num_commande + and e.el3 = r.el3 +GROUP BY e.el3; + +-- Engagements rapprochés par el3 {{{1 + +DECLARE @engagements_rapproches_el3 TABLE ( + el3 VARCHAR(8), + eng2014_rec2014 MONEY NOT NULL, + eng2014_rec2015 MONEY NOT NULL, + eng2015_rec2015 MONEY NOT NULL, + UNIQUE (el3) +); + +INSERT INTO @engagements_rapproches_el3 +SELECT + el3, + SUM( + CASE + WHEN exercice = 2014 AND exercice_reception = 2014 THEN montant + ELSE 0 + END + ) AS eng2014_rec2014, + SUM( + CASE + WHEN exercice = 2014 AND exercice_reception = 2015 THEN montant + ELSE 0 + END + ) AS eng2014_rec2015, + SUM( + CASE WHEN exercice = 2015 THEN montant ELSE 0 END + ) AS eng2015_rec2015 +FROM @engagements_rapproches +GROUP BY el3; + +-- Budget par el3 {{{1 + +DECLARE @budget_el3 TABLE ( + el3 VARCHAR(8) NOT NULL, + exercice SMALLINT NOT NULL, + montant MONEY NOT NULL, + UNIQUE (el3, exercice) +); + +INSERT INTO @budget_el3 +SELECT + el3, + yr AS exercice, + SUM(debit_value) AS montant +FROM oas_balance +WHERE + cmpcode = 'AUF' + AND yr IN (2014, 2015) + AND balcode = 'BUDGET' + AND repbasis = 5 + AND curcode = 'EUR' +GROUP BY el3, yr; + +-- Budget sans BSTG par el3 + +DECLARE @budget_sans_bstg_el3 TABLE ( + el3 VARCHAR(8) NOT NULL, + exercice SMALLINT NOT NULL, + montant MONEY NOT NULL, + UNIQUE (el3, exercice) +); + +INSERT INTO @budget_sans_bstg_el3 +SELECT + el3, + yr AS exercice, + SUM(debit_value) AS montant +FROM oas_balance +WHERE + cmpcode = 'AUF' + AND yr IN (2014, 2015) + AND balcode = 'BUDGET' + AND repbasis = 5 + AND curcode = 'EUR' + AND el1 NOT LIKE '8%' +GROUP BY el3, yr; + +-- Préengagement par el3 {{{1 + +DECLARE @preengagement_el3 TABLE ( + el3 VARCHAR(8) NOT NULL, + montant MONEY NOT NULL, + UNIQUE (el3) +); + +INSERT INTO @preengagement_el3 +SELECT el3, SUM(full_value) +FROM oas_balance +WHERE + cmpcode = 'AUF' + AND yr = 2015 + AND balcode = 'PRENGAGEMENT' + AND repbasis = 5 + AND curcode = 'EUR' +GROUP BY el3; + +-- Réel par el3 {{{1 + +DECLARE @reel_el3 TABLE ( + el3 VARCHAR(8) NOT NULL, + exercice SMALLINT NOT NULL, + montant MONEY NOT NULL, + UNIQUE (el3, exercice) +); + +INSERT INTO @reel_el3 +SELECT el3, yr, SUM(full_value) +FROM oas_balance +WHERE + cmpcode = 'AUF' + AND yr IN (2014, 2015) + AND balcode = 'REEL' + AND repbasis = 5 + AND curcode = 'EUR' + AND el1 LIKE '6%' +GROUP BY el3, yr; + +-- Provisions par el3 {{{1 + +DECLARE @provisions_el3 TABLE ( + el3 VARCHAR(8) NOT NULL, + montant MONEY NOT NULL, + UNIQUE (el3) +); + +INSERT INTO @provisions_el3 +SELECT l.el3, SUM(l.valuehome) +FROM oas_docline l, oas_dochead h +WHERE + l.cmpcode = 'AUF' + AND h.cmpcode = 'AUF' + AND l.doccode = h.doccode + AND l.docnum = h.docnum + AND ( + h.doccode = 'F-CTP' + OR (h.doccode = 'G-ODD-WEB' AND h.docnum BETWEEN 699 AND 702) + OR (h.doccode IN ('F-ANN', 'G-ANN') AND h.origdoccode = 'F-CTP') + OR ( + h.doccode IN ('F-ANN', 'G-ANN') + AND h.origdoccode = 'G-ODD-WEB' + AND h.origdocnum BETWEEN 699 AND 702 + ) + ) + AND h.yr = 2014 + AND l.el3 IS NOT NULL AND l.el3 != '' +GROUP BY l.el3; + +-- Engagement par el3 {{{1 + +DECLARE @engagement_el3 TABLE ( + el3 VARCHAR(8), + montant MONEY NOT NULL, + UNIQUE (el3) +); + +INSERT INTO @engagement_el3 +SELECT el3, SUM(full_value) +FROM oas_balance +WHERE + cmpcode = 'AUF' + AND balcode = 'ENGAGEMENT' + AND repbasis = 5 + AND curcode = 'EUR' +GROUP BY el3; + +-- Sommaire par el3 {{{1 + +DECLARE @sommaire_el3 TABLE ( + el3 VARCHAR(8), + budget_2014 MONEY NOT NULL, + budget_2014_sans_bstg MONEY NOT NULL, + budget_2015 MONEY NOT NULL, + budget_2015_sans_bstg MONEY NOT NULL, + preengagement MONEY NOT NULL, + eng2014_rec2014 MONEY NOT NULL, + eng2014_rec2015 MONEY NOT NULL, + eng2015_rec2015 MONEY NOT NULL, + eng2014_non_receptionnes MONEY NOT NULL, + eng2015_non_receptionnes MONEY NOT NULL, + reel_2014 MONEY NOT NULL, + reel_2015 MONEY NOT NULL, + provisions MONEY NOT NULL, + engagement MONEY NOT NULL, + UNIQUE (el3) +); + +INSERT INTO @sommaire_el3 +SELECT + el3, + SUM(budget_2014), + SUM(budget_2014_sans_bstg), + SUM(budget_2015), + SUM(budget_2015_sans_bstg), + SUM(preengagement), + SUM(eng2014_rec2014), + SUM(eng2014_rec2015), + SUM(eng2015_rec2015), + SUM(eng2014_non_receptionnes), + SUM(eng2015_non_receptionnes), + SUM(reel_2014), + SUM(reel_2015), + SUM(provisions), + SUM(engagement) +FROM ( + SELECT + el3, + 0 AS budget_2014, + 0 AS budget_2014_sans_bstg, + 0 AS budget_2015, + 0 AS budget_2015_sans_bstg, + 0 AS preengagement, + eng2014_rec2014, + eng2014_rec2015, + eng2015_rec2015, + eng2014_non_receptionnes, + eng2015_non_receptionnes, + 0 AS reel_2014, + 0 AS reel_2015, + 0 AS provisions, + 0 AS engagement + FROM @engagements_non_rapproches_el3 + UNION ALL + SELECT + el3, + 0 AS budget_2014, + 0 AS budget_2014_sans_bstg, + 0 AS budget_2015, + 0 AS budget_2015_sans_bstg, + 0 AS preengagement, + eng2014_rec2014, + eng2014_rec2015, + eng2015_rec2015, + 0 AS eng2014_non_receptionnes, + 0 AS eng2015_non_receptionnes, + 0 AS reel_2014, + 0 AS reel_2015, + 0 AS provisions, + 0 AS engagement + FROM @engagements_rapproches_el3 + UNION ALL + SELECT + el3, + CASE WHEN exercice = 2014 THEN montant ELSE 0 END AS budget_2014, + 0 AS budget_2014_sans_bstg, + CASE WHEN exercice = 2015 THEN montant ELSE 0 END AS budget_2015, + 0 AS budget_2015_sans_bstg, + 0 AS preengagement, + 0 AS eng2014_rec2014, + 0 AS eng2014_rec2015, + 0 AS eng2015_rec2015, + 0 AS eng2014_non_receptionnes, + 0 AS eng2015_non_receptionnes, + 0 AS reel_2014, + 0 AS reel_2015, + 0 AS provisions, + 0 AS engagement + FROM @budget_el3 + UNION ALL + SELECT + el3, + 0 AS budget_2014, + CASE WHEN exercice = 2014 THEN montant ELSE 0 END AS budget_2014_sans_bstg, + 0 AS budget_2015, + CASE WHEN exercice = 2015 THEN montant ELSE 0 END AS budget_2015_sans_bstg, + 0 AS preengagement, + 0 AS eng2014_rec2014, + 0 AS eng2014_rec2015, + 0 AS eng2015_rec2015, + 0 AS eng2014_non_receptionnes, + 0 AS eng2015_non_receptionnes, + 0 AS reel_2014, + 0 AS reel_2015, + 0 AS provisions, + 0 AS engagement + FROM @budget_sans_bstg_el3 + UNION ALL + SELECT + el3, + 0 AS budget_2014, + 0 AS budget_2014_sans_bstg, + 0 AS budget_2015, + 0 AS budget_2015_sans_bstg, + montant AS preengagement, + 0 AS eng2014_rec2014, + 0 AS eng2014_rec2015, + 0 AS eng2015_rec2015, + 0 AS eng2014_non_receptionnes, + 0 AS eng2015_non_receptionnes, + 0 AS reel_2014, + 0 AS reel_2015, + 0 AS provisions, + 0 AS engagement + FROM @preengagement_el3 + UNION ALL + SELECT + el3, + 0 AS budget_2014, + 0 AS budget_2014_sans_bstg, + 0 AS budget_2015, + 0 AS budget_2015_sans_bstg, + 0 AS preengagement, + 0 AS eng2014_rec2014, + 0 AS eng2014_rec2015, + 0 AS eng2015_rec2015, + 0 AS eng2014_non_receptionnes, + 0 AS eng2015_non_receptionnes, + CASE WHEN exercice = 2014 THEN montant ELSE 0 END AS reel_2014, + CASE WHEN exercice = 2015 THEN montant ELSE 0 END AS reel_2015, + 0 AS provisions, + 0 AS engagement + FROM @reel_el3 + UNION ALL + SELECT + el3, + 0 AS budget_2014, + 0 AS budget_2014_sans_bstg, + 0 AS budget_2015, + 0 AS budget_2015_sans_bstg, + 0 AS preengagement, + 0 AS eng2014_rec2014, + 0 AS eng2014_rec2015, + 0 AS eng2015_rec2015, + 0 AS eng2014_non_receptionnes, + 0 AS eng2015_non_receptionnes, + 0 AS reel_2014, + 0 AS reel_2015, + montant AS provisions, + 0 AS engagement + FROM @provisions_el3 + UNION ALL + SELECT + el3, + 0 AS budget_2014, + 0 AS budget_2014_sans_bstg, + 0 AS budget_2015, + 0 AS budget_2015_sans_bstg, + 0 AS preengagement, + 0 AS eng2014_rec2014, + 0 AS eng2014_rec2015, + 0 AS eng2015_rec2015, + 0 AS eng2014_non_receptionnes, + 0 AS eng2015_non_receptionnes, + 0 AS reel_2014, + 0 AS reel_2015, + 0 AS provisions, + montant AS engagement + FROM @engagement_el3 +) t +GROUP BY t.el3; + +-- Consommation 2015 {{{1 + +SELECT + el3.code AS el3, + el3.sname AS Projet, + el3.name AS POB, + grpI.code AS [Code implantation], + grpI.name AS Implantation, + grpN.code AS [Code objectif], + grpN.name AS Objectif, + grpR.code AS [Code rubrique], + grpR.name AS Rubrique, + budget_2015 AS Budget, + budget_2015_sans_bstg AS [Budget sans BSTG], + eng2014_rec2015 AS [Engagements 2014 reçus en 2015], + eng2015_rec2015 AS [Engagements 2015 reçus en 2015], + preengagement AS [Pré-engagement], + eng2014_non_receptionnes AS [Engagements 2014 non reçus], + eng2015_non_receptionnes AS [Engagements 2015 non reçus], + ( + eng2014_rec2015 + eng2015_rec2015 + preengagement + + eng2014_non_receptionnes + eng2015_non_receptionnes + ) AS [Total engagé et pré-engagé], + CASE + WHEN budget_2015_sans_bstg != 0 + THEN ( + eng2014_rec2015 + eng2015_rec2015 + preengagement + + eng2014_non_receptionnes + eng2015_non_receptionnes + ) / budget_2015_sans_bstg + END AS [Taux de consommation 1], + reel_2015 AS [Réel 2015], + -provisions AS [Provisions 2014], + engagement AS [Engagement], + CASE + WHEN budget_2015_sans_bstg != 0 + THEN (reel_2015 + engagement) / budget_2015_sans_bstg + END AS [Taux de consommation 2] +FROM + @sommaire_el3 e + INNER JOIN oas_element el3 + ON el3.cmpcode = 'AUF' + AND el3.elmlevel = 3 + AND el3.code = e.el3 + LEFT JOIN oas_grplist glI + ON glI.cmpcode = 'AUF' + AND glI.elmlevel = 3 + AND glI.code = e.el3 + AND glI.grpcode LIKE 'I%' + LEFT JOIN oas_group grpI + ON grpI.cmpcode = 'AUF' + AND grpI.groupwhat = 3 + AND grpI.code = glI.grpcode + LEFT JOIN oas_grplist glN + ON glN.cmpcode = 'AUF' + AND glN.elmlevel = 3 + AND glN.code = e.el3 + AND glN.grpcode LIKE 'N%' + LEFT JOIN oas_group grpN + ON grpN.cmpcode = 'AUF' + AND grpN.groupwhat = 3 + AND grpN.code = glN.grpcode + LEFT JOIN oas_grplist glR + ON glR.cmpcode = 'AUF' + AND glR.elmlevel = 3 + AND glR.code = e.el3 + AND glR.grpcode LIKE 'R%' + LEFT JOIN oas_group grpR + ON grpR.cmpcode = 'AUF' + AND grpR.groupwhat = 3 + AND grpR.code = glR.grpcode +WHERE + budget_2015 != 0 + OR budget_2015_sans_bstg != 0 + OR preengagement != 0 + OR eng2014_non_receptionnes != 0 + OR eng2015_non_receptionnes != 0 + OR eng2014_rec2015 != 0 + OR eng2015_rec2015 != 0 + OR reel_2015 != 0 + OR provisions != 0 + OR engagement != 0 +ORDER BY e.el3 + +-- Consommation 2014 {{{1 + +SELECT + el3.code AS el3, + el3.sname AS Projet, + el3.name AS POB, + grpI.code AS [Code implantation], + grpI.name AS Implantation, + grpN.code AS [Code objectif], + grpN.name AS Objectif, + grpR.code AS [Code rubrique], + grpR.name AS Rubrique, + budget_2014 AS Budget, + budget_2014_sans_bstg AS [Budget sans BSTG], + eng2014_rec2014 AS [Engagements 2014 reçus en 2014], + eng2014_non_receptionnes + eng2014_rec2015 + AS [Engagements 2014 non reçus en 2014], + CASE + WHEN budget_2014_sans_bstg != 0 + THEN eng2014_rec2014 / budget_2014_sans_bstg + END AS [Taux exécution], + CASE + WHEN budget_2014_sans_bstg != 0 + THEN (eng2014_rec2014 + eng2014_rec2015 + eng2014_non_receptionnes) + / budget_2014_sans_bstg + END AS [Taux engagement], + reel_2014 AS [Réel], + provisions AS Provisions, + CASE + WHEN budget_2014_sans_bstg != 0 + THEN reel_2014 / budget_2014_sans_bstg + END AS [Taux de consommation] +FROM + @sommaire_el3 e + INNER JOIN oas_element el3 + ON el3.cmpcode = 'AUF' + AND el3.elmlevel = 3 + AND el3.code = e.el3 + LEFT JOIN oas_grplist glI + ON glI.cmpcode = 'AUF' + AND glI.elmlevel = 3 + AND glI.code = e.el3 + AND glI.grpcode LIKE 'I%' + LEFT JOIN oas_group grpI + ON grpI.cmpcode = 'AUF' + AND grpI.groupwhat = 3 + AND grpI.code = glI.grpcode + LEFT JOIN oas_grplist glN + ON glN.cmpcode = 'AUF' + AND glN.elmlevel = 3 + AND glN.code = e.el3 + AND glN.grpcode LIKE 'N%' + LEFT JOIN oas_group grpN + ON grpN.cmpcode = 'AUF' + AND grpN.groupwhat = 3 + AND grpN.code = glN.grpcode + LEFT JOIN oas_grplist glR + ON glR.cmpcode = 'AUF' + AND glR.elmlevel = 3 + AND glR.code = e.el3 + AND glR.grpcode LIKE 'R%' + LEFT JOIN oas_group grpR + ON grpR.cmpcode = 'AUF' + AND grpR.groupwhat = 3 + AND grpR.code = glR.grpcode +WHERE + budget_2014 != 0 + OR budget_2014_sans_bstg != 0 + OR eng2014_non_receptionnes != 0 + OR eng2014_rec2014 != 0 + OR eng2014_rec2015 != 0 + OR reel_2014 != 0 + OR provisions != 0 +ORDER BY e.el3 + +-- vim: foldmethod=marker