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
|
insert into IOC_EXTR_ALM_TRA
select
Tranche.COD_TCE as Code_tranche,
Contrat.COD_CON as Code_contrat,
isnull(UFO_Negociatrice.COD_UFO,'XXX') as Code_UFO_Negociatrice,
isnull(UFO_Negociatrice.LIB_UFO,'Generic Deal Risk Unit') as Libelle_UFO_Negociatrice,
isnull(RAF_Emprunteur.COD_RAF,'') as Code_RAF_Emprunteur,
isnull(RAF_Emprunteur.COD_DXT,'') as Code_DXT_Emprunteur,
isnull(RAF_Emprunteur.LIB_LON_RAF,'') as Libelle_long_RAF_Emprunteur,
isnull(Portefeuille_commercial.COD_POR_COM,'') as Code_portefeuille_commercial,
isnull(Portefeuille_commercial.LIB_POR_COM,'') as c9,
isnull(Contrat.TYP_CRE_FIN,'') as Type_de_credit_financier,
isnull(Produit_technique.COD_PRD_TEC,'') as Code_produit_technique,
isnull(Produit_technique.LIB_CRT_PRD_TEC,'') as c12,
isnull(Produit_technique.LIB_LON_PRD_TEC,'') as c13,
isnull(Produit_technique.LIB_MNE_PRD_TEC,'') as c14,
isnull(T7.COD_FAM_PRD_TEC,'') as c15,
isnull(T7.LIB_FAM_PRD_TEC,'') as c16,
isnull(Nature_de_credit.COD_NAT_CRD,'') as Code_nature_de_credit,
isnull(Nature_de_credit.LIB_NAT_CRD,'') as Libelle_nature_de_credit,
isnull(Coeur_de_produits_techniques.COD_COE_PRD_TEC,'') as c19,
isnull(Coeur_de_produits_techniques.LIB_CRT_COE_PRD_TEC,'') as c20,
isnull(Coeur_de_produits_techniques.LIB_LON_COE_PRD_TEC,'') as c21,
isnull(T10.COD_RGP,'') as Code_regroupement_de_coeurs,
isnull(T10.LIB_RGP,'') as c23,
isnull(convert(char(10),Tranche.DAT_COM_ENG ,112),'') as Date_accord_commercial,
isnull(convert(char(10),Tranche.DAT_DEB_TCE ,112),'') as Date_debut_tranche,
isnull(convert(char(10),Tranche.DAT_FIN_TCE ,112),'') as Date_fin_tranche,
isnull(Devise.COD_DEV,'') as Code_devise,
convert(decimal(20,3),isnull(SUM(Encours.ENG_INI ),0.0)) as Engagement_initial,
convert(decimal(20,3),isnull(SUM(Encours.ENC_NTI),0.0)) as Encours_hors_bilan,
convert(decimal(20,3),isnull(SUM(Encours.ENC_TIR),0.0)) as Encours_bilan,
convert(decimal(20,3),isnull(SUM(Encours.ENC_TIR_EXI),0.0)) as Encours_bilan_exigible,
convert(decimal(20,3),isnull(SUM(Encours.ENC_TOT),0.0)) as Encours_total,
convert(decimal(20,3),isnull(SUM(Encours.CPT_CLI),0.0)) as Solde_compte_client
from DIM_POR_COM Portefeuille_commercial ,
DIM_RAF RAF_Emprunteur,
FAI_ENC Encours,
DIM_DAT Date_ ,
DIM_DEV Devise,
DIM_TCE Tranche,
DIM_CON Contrat,
DIM_COE_PRD_TEC Coeur_de_produits_techniques,
DIM_PRD Produit_technique,
DIM_FAM_PRD_TEC T7,
DIM_NAT_CRD Nature_de_credit,
GRP_COE_PRD_TEC T10,
DIM_UFO UFO_Negociatrice
where Portefeuille_commercial.COD_POR_COM =* RAF_Emprunteur.COD_POR_COM
and Coeur_de_produits_techniques.COD_COE_PRD_TEC =* Produit_technique.COD_COE_PRD_TEC
and T7.COD_FAM_PRD_TEC =* Produit_technique.COD_FAM_PRD_TEC
and Nature_de_credit.COD_NAT_CRD =* Produit_technique.COD_NAT_CRD
and T10.COD_RGP =* Coeur_de_produits_techniques.COD_RGP
and Produit_technique.COD_PRD_TEC =* Encours.COD_PRD_TEC
and UFO_Negociatrice.COD_UFO =* Encours.COD_UFO
and RAF_Emprunteur.COD_RAF =* Encours.COD_RAF
and Date_.COD_DAT = Encours.COD_DAT
and (Devise.COD_DAT = Encours.COD_DAT) and (Devise.COD_DEV = Encours.COD_DEV)
and Encours.COD_TCE = Tranche.COD_TCE
and Contrat.COD_CON = Tranche.COD_CON
and Date_.COD_DAT = '20081009'
group by
Tranche.COD_TCE,
Contrat.COD_CON,
UFO_Negociatrice.COD_UFO,
UFO_Negociatrice.LIB_UFO,
RAF_Emprunteur.COD_RAF,
RAF_Emprunteur.COD_DXT,
RAF_Emprunteur.LIB_LON_RAF,
Portefeuille_commercial.COD_POR_COM,
Portefeuille_commercial.LIB_POR_COM,
Contrat.TYP_CRE_FIN,
Produit_technique.COD_PRD_TEC,
Produit_technique.LIB_CRT_PRD_TEC,
Produit_technique.LIB_LON_PRD_TEC,
Produit_technique.LIB_MNE_PRD_TEC,
T7.COD_FAM_PRD_TEC,
T7.LIB_FAM_PRD_TEC,
Nature_de_credit.COD_NAT_CRD,
Nature_de_credit.LIB_NAT_CRD,
Coeur_de_produits_techniques.COD_COE_PRD_TEC,
Coeur_de_produits_techniques.LIB_CRT_COE_PRD_TEC,
Coeur_de_produits_techniques.LIB_LON_COE_PRD_TEC,
T10.COD_RGP,
T10.LIB_RGP,
Tranche.DAT_COM_ENG,
Tranche.DAT_DEB_TCE,
Tranche.DAT_FIN_TCE,
Devise.COD_DEV |
Partager