1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224
| IF EXiSTS (Select name
FROM sysobjects
Where name = 'V_DIM_GRANDSCOMPTES'
AND Type = 'V')
DROP VIEW V_DIM_GRANDSCOMPTES
GO
CREATE VIEW V_DIM_GRANDSCOMPTES
AS
SELECT
gc1.TypeGrandsComptes_Id AS NiveauHolding
, gc1.GrandsComptes_Id AS GrandsComptes_IdHolding
, gc1.EntiteClienteFille_Id AS EntiteClienteHolding
, tdec1.RaisonSociale AS RaisonSocialeHolding
, gc2.TypeGrandsComptes_Id AS NiveauHoldingFiliale
, gc2.GrandsComptes_Id AS GrandsComptes_IdHoldingFiliale
, gc2.EntiteClienteFille_Id AS EntiteClienteHoldingFiliale
, tdec2.RaisonSociale AS RaisonSocialeHoldingFiliale
, gc3.TypeGrandsComptes_Id AS NiveauFiliale
, gc3.GrandsComptes_Id AS GrandsComptes_IdFiliale
, gc3.EntiteClienteFille_Id AS EntiteClienteFiliale
, tdec3.RaisonSociale AS RaisonSocialeFiliale
, gc3.TypeGrandsComptes_Id AS TypeGrandsComptes_Id
, gc3.TailleCompte_Id AS TailleCompte_Id
, gc3.Suivi_Id AS Suivi_Id
, gc3.NumeroGrandCompte AS [Numero Grand Compte]
, gc3.Responsable_Id AS Responsable
, Isnull(gc1.DateFin_Id, 0) AS DateValidite
FROM
T_DIM_GRANDS_COMPTES gc1
JOIN T_DIM_TYPES_ENTITES_GRANDS_COMPTES tgc1
ON tgc1.TypeGrandsComptes_Id = gc1.TypeGrandsComptes_Id
JOIN T_DIM_ENTITES_CLIENTES AS tdec1
ON gc1.EntiteClienteFille_Id = tdec1.EntiteCliente_Id
JOIN T_DIM_CALENDRIER cal
ON cal.Calendrier_Id = gc1.DateFin_Id
, T_DIM_GRANDS_COMPTES AS gc2
JOIN T_DIM_TYPES_ENTITES_GRANDS_COMPTES tgc2
ON tgc2.TypeGrandsComptes_Id = gc2.TypeGrandsComptes_Id
JOIN T_DIM_ENTITES_CLIENTES AS tdec2
ON gc2.EntiteClienteFille_Id = tdec2.EntiteCliente_Id
, T_DIM_GRANDS_COMPTES AS gc3
JOIN T_DIM_TYPES_ENTITES_GRANDS_COMPTES tgc3
ON tgc3.TypeGrandsComptes_Id = gc3.TypeGrandsComptes_Id
JOIN T_DIM_ENTITES_CLIENTES AS tdec3
ON gc3.EntiteClienteFille_Id = tdec3.EntiteCliente_Id
WHERE
tgc1.Holding = 1
AND tgc1.Filiale = 0
AND tgc2.Holding = 1
AND tgc2.Filiale = 1
AND tgc3.Holding = 0
AND tgc3.Filiale = 1
AND gc1.EntiteClienteFille_Id = gc2.EntiteClienteMere_Id
AND gc2.EntiteClienteFille_Id = gc3.EntiteClienteMere_Id
AND ISNULL(gc1.DateFin_Id, 0) = ISNULL(gc2.DateFin_Id, 0)
AND ISNULL(gc1.DateFin_Id, 0) = ISNULL(gc3.DateFin_Id, 0)
AND cal.mm = 01
AND cal.jj = 01
UNION ALL
SELECT
gc1.TypeGrandsComptes_Id AS NiveauHolding
, gc1.GrandsComptes_Id AS GrandsComptes_IdHolding
, gc1.EntiteClienteFille_Id AS EntiteClienteHolding
, tdec1.RaisonSociale AS RaisonSocialeHolding
, 1 AS NiveauHoldingFiliale
, gc1.GrandsComptes_Id AS GrandsComptes_IdHoldingFiliale
, gc1.EntiteClienteFille_Id AS EntiteClienteHoldingFiliale
, tdec1.RaisonSociale AS RaisonSocialeHoldingFiliale
, 3 AS NiveauFiliale
, gc3.GrandsComptes_Id AS GrandsComptes_IdFiliale
, gc3.EntiteClienteFille_Id AS EntiteClienteFiliale
, tdec3.RaisonSociale AS RaisonSocialeFiliale
, gc3.TypeGrandsComptes_Id AS TypeGrandsComptes_Id
, gc3.TailleCompte_Id AS TailleCompte_Id
, gc3.Suivi_Id AS Suivi_Id
, gc3.NumeroGrandCompte AS [Numero Grand Compte]
, gc3.Responsable_Id AS Responsable
, Isnull(gc1.DateFin_Id, 0) AS DateValidite
FROM
T_DIM_GRANDS_COMPTES gc1
JOIN T_DIM_TYPES_ENTITES_GRANDS_COMPTES tgc1
ON tgc1.TypeGrandsComptes_Id = gc1.TypeGrandsComptes_Id
JOIN T_DIM_ENTITES_CLIENTES AS tdec1
ON gc1.EntiteClienteFille_Id = tdec1.EntiteCliente_Id
JOIN T_DIM_CALENDRIER cal
ON cal.Calendrier_Id = gc1.DateFin_Id
, T_DIM_GRANDS_COMPTES AS gc3
JOIN T_DIM_TYPES_ENTITES_GRANDS_COMPTES tgc3
ON tgc3.TypeGrandsComptes_Id = gc3.TypeGrandsComptes_Id
JOIN T_DIM_ENTITES_CLIENTES AS tdec3
ON gc3.EntiteClienteFille_Id = tdec3.EntiteCliente_Id
WHERE
tgc1.Holding = 1
AND tgc1.Filiale = 0
AND tgc3.Holding = 0
AND tgc3.Filiale = 1
AND gc1.EntiteClienteFille_Id = gc3.EntiteClienteMere_Id
AND ISNULL(gc1.DateFin_Id, 0) = ISNULL(gc3.DateFin_Id, 0)
AND cal.mm = 01
AND cal.jj = 01
UNION ALL
SELECT
gc1.TypeGrandsComptes_Id AS NiveauHolding
, gc1.GrandsComptes_Id AS GrandsComptes_IdHolding
, gc1.EntiteClienteFille_Id AS EntiteClienteHolding
, tdec1.RaisonSociale AS RaisonSocialeHolding
, gc2.TypeGrandsComptes_Id AS NiveauHoldingFiliale
, gc2.GrandsComptes_Id AS GrandsComptes_IdHoldingFiliale
, gc2.EntiteClienteFille_Id AS EntiteClienteHoldingFiliale
, tdec2.RaisonSociale AS RaisonSocialeHoldingFiliale
, gc2.TypeGrandsComptes_Id AS NiveauFiliale
, gc2.GrandsComptes_Id AS GrandsComptes_IdFiliale
, gc2.EntiteClienteFille_Id AS EntiteClienteFiliale
, tdec2.RaisonSociale AS RaisonSocialeFiliale
, gc2.TypeGrandsComptes_Id AS TypeGrandsComptes_Id
, gc2.TailleCompte_Id AS TailleCompte_Id
, gc2.Suivi_Id AS Suivi_Id
, gc2.NumeroGrandCompte AS [Numero Grand Compte]
, gc2.Responsable_Id AS Responsable
, Isnull(gc1.DateFin_Id, 0) AS DateValidite
FROM
T_DIM_GRANDS_COMPTES gc1
JOIN T_DIM_TYPES_ENTITES_GRANDS_COMPTES tgc1
ON tgc1.TypeGrandsComptes_Id = gc1.TypeGrandsComptes_Id
JOIN T_DIM_ENTITES_CLIENTES AS tdec1
ON gc1.EntiteClienteFille_Id = tdec1.EntiteCliente_Id
JOIN T_DIM_CALENDRIER cal
ON cal.Calendrier_Id = gc1.DateFin_Id
, T_DIM_GRANDS_COMPTES AS gc2
JOIN T_DIM_TYPES_ENTITES_GRANDS_COMPTES tgc2
ON tgc2.TypeGrandsComptes_Id = gc2.TypeGrandsComptes_Id
JOIN T_DIM_ENTITES_CLIENTES AS tdec2
ON gc2.EntiteClienteFille_Id = tdec2.EntiteCliente_Id
WHERE
tgc1.Holding = 1
AND tgc1.Filiale = 0
AND tgc2.Holding = 1
AND tgc2.Filiale = 1
AND gc1.EntiteClienteFille_Id = gc2.EntiteClienteMere_Id
AND ISNULL(gc1.DateFin_Id, 0) = ISNULL(gc2.DateFin_Id, 0)
AND cal.mm = 01
AND cal.jj = 01
UNION ALL
SELECT
gc1.TypeGrandsComptes_Id AS NiveauHolding
, gc1.GrandsComptes_Id AS GrandsComptes_IdHolding
, gc1.EntiteClienteFille_Id AS EntiteClienteHolding
, tdec1.RaisonSociale AS RaisonSocialeHolding
, gc1.TypeGrandsComptes_Id AS NiveauHoldingFiliale
, gc1.GrandsComptes_Id AS GrandsComptes_IdHoldingFiliale
, gc1.EntiteClienteFille_Id AS EntiteClienteHoldingFiliale
, tdec1.RaisonSociale AS RaisonSocialeHoldingFiliale
, gc1.TypeGrandsComptes_Id AS NiveauFiliale
, gc1.GrandsComptes_Id AS GrandsComptes_IdFiliale
, gc1.EntiteClienteFille_Id AS EntiteClienteFiliale
, tdec1.RaisonSociale AS RaisonSocialeFiliale
, gc1.TypeGrandsComptes_Id AS TypeGrandsComptes_Id
, gc1.TailleCompte_Id AS TailleCompte_Id
, gc1.Suivi_Id AS Suivi_Id
, gc1.NumeroGrandCompte AS [Numero Grand Compte]
, gc1.Responsable_Id AS Responsable
, Isnull(gc1.DateFin_Id, 0) AS DateValidite
FROM
T_DIM_GRANDS_COMPTES gc1
JOIN T_DIM_TYPES_ENTITES_GRANDS_COMPTES tgc1
ON tgc1.TypeGrandsComptes_Id = gc1.TypeGrandsComptes_Id
JOIN T_DIM_ENTITES_CLIENTES AS tdec1
ON gc1.EntiteClienteFille_Id = tdec1.EntiteCliente_Id
JOIN T_DIM_CALENDRIER cal
ON cal.Calendrier_Id = gc1.DateFin_Id
WHERE
tgc1.Holding = 1
AND tgc1.Filiale = 0
AND cal.mm = 01
AND cal.jj = 01
GO |
Partager