projets / auf_coda_additions.git / blame
| Commit | Line | Data |
|---|---|---|
| 2295d186 EMS |
1 | |
| 2 | ||
| 3 | ||
| 4 | ||
| 5 | --exec auf_p_p4_intero_cumul 2011,1,12,'91060PJ','','','' | |
| 6 | ||
| 7 | ||
| 8 | CREATE procedure auf_p_p4_intero_cumul | |
| 9 | ||
| 10 | @annee int , | |
| 11 | @periodeD int, | |
| 12 | @periodeF int, | |
| 13 | @projet varchar(7), | |
| 14 | @region varchar(12), | |
| 15 | @implantation varchar(12), | |
| 16 | @el1 varchar(6) | |
| 17 | ||
| 18 | ||
| 19 | as | |
| 20 | ||
| 21 | ||
| 22 | SET NOCOUNT ON | |
| 23 | ||
| 24 | ||
| 25 | create table #cumuls (el1 varchar(10), | |
| 26 | el3 varchar(12), | |
| 27 | balcode varchar(12), | |
| 28 | nature varchar(30), | |
| 29 | montant money null) | |
| 30 | ||
| 31 | ||
| 32 | insert into #cumuls | |
| 33 | ||
| 34 | select | |
| 35 | el1, | |
| 36 | el3, | |
| 37 | balcode , | |
| 38 | case when el1 like '97%' then | |
| 39 | 'Recette' | |
| 40 | when el1 like '2%' then | |
| 41 | 'Dépense' | |
| 42 | when (el1 like '1%' AND el3 like '9003%') then | |
| 43 | 'Dépense' | |
| 44 | when (el1 like '1%' AND el3 not like '9003%') then | |
| 45 | 'Recette' | |
| 46 | when el1 like '6%' then | |
| 47 | 'Dépense' | |
| 48 | when el1 like '7%' then | |
| 49 | 'Recette' | |
| 50 | when el1 like '86%' then | |
| 51 | 'Dépense BSTG' | |
| 52 | when el1 like '87%' then | |
| 53 | 'Recette BSTG' | |
| 54 | when el1 like '987%' then | |
| 55 | 'Recette BSTG' | |
| 56 | when el1 like '986%' then | |
| 57 | 'Dépense BSTG' | |
| 58 | else | |
| 59 | 'Dépense' | |
| 60 | end , | |
| 61 | sum(full_value) mont | |
| 62 | ||
| 63 | ||
| 64 | from oas_balance | |
| 65 | ||
| 66 | where yr = @annee and | |
| 67 | period between @periodeD and @periodeF and | |
| 68 | curcode = 'EUR' and | |
| 69 | repbasis = 5 and | |
| 70 | balcode not like 'DBM%' and | |
| 71 | el3 like @projet + '%' and | |
| 72 | el1 not like '4%' and | |
| 73 | el1 like @el1 + '%' | |
| 74 | ------------------------------------- | |
| 75 | ||
| 76 | group by el1,el3, balcode | |
| 77 | ||
| 78 | ||
| 79 | ||
| 80 | select @annee exercice, | |
| 81 | cumul.el1, | |
| 82 | cumul.el3, | |
| 83 | groupe.nom_court, | |
| 84 | groupe.nom, | |
| 85 | cumul.nature, | |
| 86 | groupe.region, | |
| 87 | groupe.implantation, | |
| 88 | --groupe.implantation_nc, | |
| 89 | ||
| 90 | sum(isnull(budget.montant,0)) budget, | |
| 91 | sum(isnull(estime.montant,0)) estime, | |
| 92 | sum(isnull(mensul.montant,0)) mensul, | |
| 93 | sum(isnull(reel.montant,0)) reel , | |
| 94 | sum(isnull(engage.montant,0)) engage | |
| 95 | ||
| 96 | from auf_v_p4_projets_groupes groupe, | |
| 97 | ||
| 98 | ( select distinct el1,el3,nature | |
| 99 | from #cumuls ) cumul left join | |
| 100 | ( select el1, | |
| 101 | el3, | |
| 102 | balcode, | |
| 103 | montant | |
| 104 | from #cumuls) budget | |
| 105 | ||
| 106 | on ( cumul.el1 = budget.el1 and | |
| 107 | cumul.el3 = budget.el3 and | |
| 108 | budget.balcode = 'BUDGET' ) | |
| 109 | ||
| 110 | left join | |
| 111 | ( select el1, | |
| 112 | el3, | |
| 113 | balcode, | |
| 114 | montant | |
| 115 | ||
| 116 | from #cumuls) estime | |
| 117 | ||
| 118 | on ( cumul.el1 = estime.el1 and | |
| 119 | cumul.el3 = estime.el3 and | |
| 120 | estime.balcode = 'ESTIME' ) | |
| 121 | ||
| 122 | left join | |
| 123 | ( select el1, | |
| 124 | el3, | |
| 125 | balcode, | |
| 126 | montant | |
| 127 | ||
| 128 | from #cumuls) mensul | |
| 129 | ||
| 130 | on ( cumul.el1 = mensul.el1 and | |
| 131 | cumul.el3 = mensul.el3 and | |
| 132 | mensul.balcode = 'BUDGET-MENS' ) | |
| 133 | ||
| 134 | left join | |
| 135 | ( select el1, | |
| 136 | el3, | |
| 137 | balcode, | |
| 138 | montant | |
| 139 | ||
| 140 | from #cumuls) engage | |
| 141 | ||
| 142 | on ( cumul.el1 = engage.el1 and | |
| 143 | cumul.el3 = engage.el3 and | |
| 144 | engage.balcode = 'ENGAGE' ) | |
| 145 | ||
| 146 | left join | |
| 147 | ( select el1, | |
| 148 | el3, | |
| 149 | balcode, | |
| 150 | montant | |
| 151 | from #cumuls) reel | |
| 152 | ||
| 153 | on ( cumul.el1 = reel.el1 and | |
| 154 | cumul.el3 = reel.el3 and | |
| 155 | reel.balcode = 'REEL' ) | |
| 156 | ||
| 157 | where cumul.el3 = groupe.code_budgetaire and | |
| 158 | groupe.region like @region + '%' and | |
| 159 | groupe.implantation like 'I' + rtrim(ltrim(@implantation)) + '%' | |
| 160 | ||
| 161 | group by cumul.el1, | |
| 162 | cumul.el3, | |
| 163 | groupe.nom_court, | |
| 164 | groupe.nom, | |
| 165 | cumul.nature, | |
| 166 | groupe.region , | |
| 167 | groupe.implantation | |
| 168 | --groupe.implantation_nc | |
| 169 | GO |