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
| DECLARE
v_annee number;
v_mois number;
v_jour number;
BEGIN
select to_number(to_char(DTVALOREF,'DD')),to_number(to_char(DTVALOREF,'MM')),to_number(to_char(DTVALOREF,'YYYY'))
into v_jour,v_mois, v_annee
from TDBFI_OPHYR_DTS;
MERGE INTO TDBFI_QH_REF QH
USING (SELECT
cnt.ct_itu,
cnt.ll_typ_itu, cnt.typinst, cnt.libtyins, cnt.cd_sou_typ_cnt,
cnt.ll_sou_typ_cnt, cnt.codeisin, cnt.libval, cnt.dd_cnt,
cnt.df_cnt, cnt.cd_fam_rsq, cnt.ll_fam_rsq, cnt.tx_pln, cnt.mt_plf,
cnt.va_ect, cnt.da_opt_swa, cnt.typtx, cnt.tx_fix_cnt,
cnt.cd_tau_ref_var, cnt.cd_frq_int, cnt.cd_bas_cal_div, cnt.nomi,
cnt.cd_dev, cnt.fl_swa, tiers.ay_nio_tie,tiers.cd_ric_tie,tiers.cd_ric_grp_tie,
tiers.cd_sec_bay_tie,tiers.cd_sec_gic_tie,tiers.cd_sec_nac_tie,tiers.da_not_ctm_fitch,
tiers.da_not_ctm_mood,tiers.da_not_ctm_sp,tiers.da_not_ltm_fitch,tiers.da_not_ltm_mood,
tiers.da_not_ltm_sp,tiers.da_ten_ctm_fitch,tiers.da_ten_ctm_mood,tiers.da_ten_ctm_sp,
tiers.da_ten_ltm_fitch,tiers.da_ten_ltm_mood,tiers.da_ten_ltm_sp,tiers.ll_gar_tie,
tiers.cd_grp_tie,tiers.ll_sec_bay_tie,tiers.ll_sec_gic_tie,tiers.ll_sec_nac_tie,tiers.ll_tie,
tiers.ll_grp_tie,tiers.ll_nio_tie,tiers.ll_ric_tie,tiers.ll_ric_grp_tie,tiers.ll_ent_tie,
tiers.ll_nat_tie,tiers.cd_not_tie_ctm_fitch,tiers.cd_not_tie_ctm_mood,
tiers.cd_not_tie_ctm_sp,tiers.cd_not_tie_ltm_fitch,tiers.cd_not_tie_ltm_mood,
tiers.cd_not_tie_ltm_sp,tiers.cd_rle_tie,tiers.ll_pay,tiers.fl_zon_eur,tiers.cd_ten_ctm_fitch,
tiers.cd_ten_ctm_mood,tiers.cd_ten_ctm_sp,tiers.cd_ten_ltm_fitch,tiers.cd_ten_ltm_mood,
tiers.cd_ten_ltm_sp,tiers.fl_tie_eea,tiers.fl_tie_emg,tiers.id_tie,tiers.fl_tie_ocd,
tiers.fl_tie_zon_eur,tiers.cd_ent_tie,
j1.LC_SEN_JAM,j1.CD_SJA_JAM,j1.CT_SJA_JAM,j1.LC_TYP_SJA_JAM,j1.LL_JAM,j1.VA_ECT_JAM,j1.CT_TAU_JAM,
j1.TX_FIX_JAM,j1.CD_TAU_REF_VAR_JAM,j1.CD_FRQ_INT_JAM,j1.CD_BAS_CAL_DIV_JAM,j1.MT_NOM_JAM,j1.CD_DEV_JAM,j1.LL_TYP_CLL_JAM,
j2.LC_SEN_JAM_PTR,j2.CD_SJA_JAM_PTR,j2.CT_SJA_JAM_PTR,j2.LC_TYP_SJA_JAM_PTR,j2.LL_JAM_PTR,j2.VA_ECT_JAM_PTR,j2.CT_TAU_JAM_PTR,
j2.TX_FIX_JAM_PTR,j2.CD_TAU_REF_VAR_JAM_PTR,j2.CD_FRQ_INT_JAM_PTR,j2.CD_BAS_CAL_DIV_JAM_PTR,j2.MT_NOM_JAM_PTR,j2.CD_DEV_JAM_PTR,j2.LL_TYP_CLL_JAM_PTR
FROM TDBFI_OPHYR_04 tiers,
TDBFI_OPHYR_02 cnt,
TDBFI_JAMBE_RECE j1,
TDBFI_JAMBE_PTR j2
WHERE cnt.ct_itu = 'SWAP'
AND cnt.codeisin = j1.codeisin(+)
and cnt.codeisin = j2.codeisin(+)
AND cnt.id_tie_ctp = tiers.id_tie(+)) C
ON
( QH.PASTRF =v_annee
and QH.PMSTRF =v_mois
and QH.PJSTRF =v_jour
and QH.CODEISIN = C.CODEISIN
)
WHEN MATCHED THEN
UPDATE SET
QH.CT_ITU = C.CT_ITU,
QH.LL_TYP_ITU = C.LL_TYP_ITU,
QH.TYPINST = C.TYPINST ,
QH.LIBTYINS = C.LIBTYINS,
QH.CD_SOU_TYP_CNT_VAL = C.CD_SOU_TYP_CNT,
QH.LL_SOU_TYP_CNT_VAL = C.LL_SOU_TYP_CNT,
QH.LIBVAL = C.LIBVAL,
QH.DD_CNT = C.DD_CNT,
QH.DF_CNT = C.DF_CNT,
QH.CD_FAM_RSQ = C.CD_FAM_RSQ,
QH.LL_FAM_RSQ = C.LL_FAM_RSQ,
QH.TX_PLN = C.TX_PLN,
QH.MT_PLF = C.MT_PLF,
QH.VA_ECT = C.VA_ECT,
QH.DA_OPT_SWA = C.DA_OPT_SWA,
QH.TYPTX = C.TYPTX,
QH.TX_FIX_CNT = C.TX_FIX_CNT,
QH.CD_TAU_REF_VAR = C.CD_TAU_REF_VAR,
QH.CD_FRQ_INT = C.CD_FRQ_INT,
QH.CD_BAS_CAL_DIV = C.CD_BAS_CAL_DIV,
QH.NOMI = C.NOMI ,
QH.CD_DEV = C.CD_DEV ,
QH.FL_SWA = C.FL_SWA ,
QH.AY_NIO_TIE_CTP = c.ay_nio_tie,
QH.CD_RIC_TIE_CTP = c.cd_ric_tie,
QH.CD_RIC_GRP_TIE_CTP = c.cd_ric_grp_tie,
QH.CD_SEC_BAY_TIE_CTP = c.cd_sec_bay_tie,
QH.CD_SEC_GIC_TIE_CTP = c.cd_sec_gic_tie,
QH.CD_SEC_NAC_TIE_CTP = c.cd_sec_nac_tie,
QH.DA_NOT_CTP_CTM_FITCH = c.da_not_ctm_fitch,
QH.DA_NOT_CTP_LTM_MOOD = c.da_not_ctm_mood,
QH.DA_NOT_CTP_CTM_SP = c.da_not_ctm_sp,
QH.DA_NOT_CTP_LTM_FITCH = c.da_not_ltm_fitch,
QH.DA_NOT_CTP_CTM_MOOD = c.da_not_ltm_mood,
QH.DA_NOT_CTP_LTM_SP = c.da_not_ltm_sp,
QH.DA_TEN_CTP_CTM_FITCH = c.da_ten_ctm_fitch,
QH.DA_TEN_CTP_CTM_MOOD = c.da_ten_ctm_mood,
QH.DA_TEN_CTP_CTM_SP = c.da_ten_ctm_sp,
QH.DA_TEN_CTP_LTM_FITCH = c.da_ten_ltm_fitch,
QH.DA_TEN_CTP_LTM_MOOD = c.da_ten_ltm_mood,
QH.DA_TEN_CTP_LTM_SP = c.da_ten_ltm_sp,
QH.LL_GAR_TIE_CTP = c.ll_gar_tie,
QH.CD_GRP_TIE_CTP = c.cd_grp_tie,
QH.LL_SEC_BAY_TIE_CTP = c.ll_sec_bay_tie,
QH.LL_SEC_GIC_TIE_CTP = c.ll_sec_gic_tie,
QH.LL_SEC_NAC_TIE_CTP = c.ll_sec_nac_tie,
QH.LL_TIE_CTP = c.ll_tie,
QH.LL_GRP_TIE_CTP = c.ll_grp_tie,
QH.LL_NIO_TIE_CTP = c.ll_nio_tie,
QH.LL_RIC_TIE_CTP = c.ll_ric_tie,
QH.LL_RIC_GRP_TIE_CTP = c.ll_ric_grp_tie,
QH.LL_ENT_TIE_CTP = c.ll_ent_tie,
QH.LL_NAT_TIE_CTP = c.ll_nat_tie,
QH.CD_NOT_TIE_CTP_CTM_FITCH= c.cd_not_tie_ctm_fitch,
QH.CD_NOT_TIE_CTP_CTM_MOOD = c.cd_not_tie_ctm_mood,
QH.CD_NOT_TIE_CTP_CTM_SP = c.cd_not_tie_ctm_sp,
QH.CD_NOT_TIE_CTP_LTM_FITCH= c.cd_not_tie_ltm_fitch,
QH.CD_NOT_TIE_CTP_LTM_MOOD = c.cd_not_tie_ltm_mood,
QH.CD_NOT_TIE_CTP_LTM_SP = c.cd_not_tie_ltm_sp,
QH.CD_RLE_TIE_CTP = c.cd_rle_tie,
QH.LL_PAY_CTP = c.ll_pay,
QH.FL_ZON_EUR_CTP = c.fl_zon_eur,
QH.CD_TEN_CTP_CTM_FITCH = c.cd_ten_ctm_fitch,
QH.CD_TEN_CTP_CTM_MOOD = c.cd_ten_ctm_mood,
QH.CD_TEN_CTP_CTM_SP = c.cd_ten_ctm_sp,
QH.CD_TEN_CTP_LTM_FITCH = c.cd_ten_ltm_fitch,
QH.CD_TEN_CTP_LTM_MOOD = c.cd_ten_ltm_mood,
QH.CD_TEN_CTP_LTM_SP = c.cd_ten_ltm_sp,
QH.FL_TIE_CTP_EEA = c.fl_tie_eea,
QH.FL_TIE_CTP_EMG = c.fl_tie_emg,
QH.ID_TIE_CTP = c.id_tie,
QH.FL_TIE_CTP_OCD = c.fl_tie_ocd,
QH.FL_TIE_CTP_ZON_EUR = c.fl_tie_zon_eur,
QH.CD_ENT_TIE_CTP = c.cd_ent_tie,
QH.LL_TYP_JAM_RPN = C.LL_TYP_CLL_JAM,
QH.LC_SEN_JAM_RPN = C.LC_SEN_JAM,
QH.CD_SJA_JAM_RPN = C.CD_SJA_JAM,
QH.CT_SJA_JAM_RPN = C.CT_SJA_JAM ,
QH.LC_TYP_SJA_JAM_RPN = C.LC_TYP_SJA_JAM,
QH.LL_JAM_RPN = C.LL_JAM,
QH.VA_ECT_JAM_RPN = C.VA_ECT_JAM,
QH.CT_TAU_JAM_RPN = C.CT_TAU_JAM ,
QH.TX_FIX_JAM_RPN = C.TX_FIX_JAM,
QH.CD_TAU_REF_VAR_RPN = C.CD_TAU_REF_VAR_JAM,
QH.CD_FRQ_INT_JAM_RPN = C.CD_FRQ_INT_JAM ,
QH.CD_BAS_CAL_DIV_RPN = C.CD_BAS_CAL_DIV_JAM,
QH.MT_NOM_JAM_RPN = C.MT_NOM_JAM,
QH.CD_DEV_JAM_RPN = C.CD_DEV_JAM,
QH.LL_TYP_CLL_JAM_RPN = C.LL_TYP_CLL_JAM,
QH.LL_TYP_JAM_PTR = C.LL_TYP_CLL_JAM_PTR,
QH.LC_SEN_JAM_PTR = C.LC_SEN_JAM_PTR,
QH.CD_SJA_JAM_PTR = C.CD_SJA_JAM_PTR,
QH.CT_SJA_JAM_PTR = C.CT_SJA_JAM_PTR,
QH.LC_TYP_SJA_JAM_PTR = C.LC_TYP_SJA_JAM_PTR,
QH.LL_JAM_PTR = C.LL_JAM_PTR,
QH.VA_ECT_JAM_PTR = C.VA_ECT_JAM_PTR,
QH.CT_TAU_JAM_PTR = C.CT_TAU_JAM_PTR,
QH.TX_FIX_JAM_PTR = C.TX_FIX_JAM_PTR,
QH.CD_TAU_REF_VAR_PTR = C.CD_TAU_REF_VAR_JAM_PTR,
QH.CD_FRQ_INT_JAM_PTR = C.CD_FRQ_INT_JAM_PTR,
QH.CD_BAS_CAL_DIV_PTR = C.CD_BAS_CAL_DIV_JAM_PTR ,
QH.MT_NOM_JAM_PTR = C.MT_NOM_JAM_PTR,
QH.CD_DEV_JAM_PTR = C.CD_DEV_JAM_PTR,
QH.LL_TYP_CLL_JAM_PTR = C.LL_TYP_CLL_JAM_PTR
WHEN NOT MATCHED THEN
INSERT
(QH.PASTRF,QH.PMSTRF,QH.PJSTRF,QH.CT_ITU,QH.LL_TYP_ITU,QH.TYPINST,QH.LIBTYINS,QH.CD_SOU_TYP_CNT_VAL,QH.LL_SOU_TYP_CNT_VAL,QH.CODEISIN,
QH.LIBVAL,QH.DD_CNT,QH.DF_CNT,QH.CD_FAM_RSQ,QH.LL_FAM_RSQ,QH.TX_PLN,QH.MT_PLF,QH.VA_ECT,QH.DA_OPT_SWA,QH.TYPTX,QH.TX_FIX_CNT,QH.CD_TAU_REF_VAR,
QH.CD_FRQ_INT,QH.CD_BAS_CAL_DIV,QH.NOMI,QH.CD_DEV,QH.FL_SWA,QH.AY_NIO_TIE_CTP,QH.CD_RIC_TIE_CTP,QH.CD_RIC_GRP_TIE_CTP,QH.CD_SEC_BAY_TIE_CTP,
QH.CD_SEC_GIC_TIE_CTP,QH.CD_SEC_NAC_TIE_CTP,QH.DA_NOT_CTP_CTM_FITCH,QH.DA_NOT_CTP_LTM_MOOD,QH.DA_NOT_CTP_CTM_SP,QH.DA_NOT_CTP_LTM_FITCH,QH.DA_NOT_CTP_CTM_MOOD,
QH.DA_NOT_CTP_LTM_SP,QH.DA_TEN_CTP_CTM_FITCH,QH.DA_TEN_CTP_CTM_MOOD,QH.DA_TEN_CTP_CTM_SP,QH.DA_TEN_CTP_LTM_FITCH,QH.DA_TEN_CTP_LTM_MOOD,QH.DA_TEN_CTP_LTM_SP,
QH.LL_GAR_TIE_CTP,QH.CD_GRP_TIE_CTP,QH.LL_SEC_BAY_TIE_CTP,QH.LL_SEC_GIC_TIE_CTP,QH.LL_SEC_NAC_TIE_CTP,QH.LL_TIE_CTP,QH.LL_GRP_TIE_CTP,QH.LL_NIO_TIE_CTP,
QH.LL_RIC_TIE_CTP,QH.LL_RIC_GRP_TIE_CTP,QH.LL_ENT_TIE_CTP,QH.LL_NAT_TIE_CTP,QH.CD_NOT_TIE_CTP_CTM_FITCH,
QH.CD_NOT_TIE_CTP_CTM_MOOD,QH.CD_NOT_TIE_CTP_CTM_SP,QH.CD_NOT_TIE_CTP_LTM_FITCH,QH.CD_NOT_TIE_CTP_LTM_MOOD,
QH.CD_NOT_TIE_CTP_LTM_SP,QH.CD_RLE_TIE_CTP,QH.LL_PAY_CTP,QH.FL_ZON_EUR_CTP,QH.CD_TEN_CTP_CTM_FITCH,QH.CD_TEN_CTP_CTM_MOOD,QH.CD_TEN_CTP_CTM_SP,QH.CD_TEN_CTP_LTM_FITCH,
QH.CD_TEN_CTP_LTM_MOOD,QH.CD_TEN_CTP_LTM_SP,QH.FL_TIE_CTP_EEA,QH.FL_TIE_CTP_EMG,QH.ID_TIE_CTP,QH.FL_TIE_CTP_OCD,QH.FL_TIE_CTP_ZON_EUR,QH.CD_ENT_TIE_CTP,
QH.LL_TYP_JAM_RPN,QH.LC_SEN_JAM_RPN,QH.CD_SJA_JAM_RPN,QH.CT_SJA_JAM_RPN,QH.LC_TYP_SJA_JAM_RPN,QH.LL_JAM_RPN,QH.VA_ECT_JAM_RPN,QH.CT_TAU_JAM_RPN,
QH.TX_FIX_JAM_RPN,QH.CD_TAU_REF_VAR_RPN,QH.CD_FRQ_INT_JAM_RPN,QH.CD_BAS_CAL_DIV_RPN,QH.MT_NOM_JAM_RPN,QH.CD_DEV_JAM_RPN,QH.LL_TYP_CLL_JAM_RPN,
QH.LL_TYP_JAM_PTR,QH.LC_SEN_JAM_PTR,QH.CD_SJA_JAM_PTR,QH.CT_SJA_JAM_PTR,QH.LC_TYP_SJA_JAM_PTR,QH.LL_JAM_PTR,QH.VA_ECT_JAM_PTR,QH.CT_TAU_JAM_PTR,
QH.TX_FIX_JAM_PTR,QH.CD_TAU_REF_VAR_PTR,QH.CD_FRQ_INT_JAM_PTR,QH.CD_BAS_CAL_DIV_PTR,QH.MT_NOM_JAM_PTR,QH.CD_DEV_JAM_PTR,
QH.LL_TYP_CLL_JAM_PTR)
VALUES
(v_annee,v_mois,v_jour,C.ct_itu,C.ll_typ_itu,C.typinst,C.libtyins,C.cd_sou_typ_cnt,C.ll_sou_typ_cnt,C.codeisin,C.libval,
C.dd_cnt,C.df_cnt,C.cd_fam_rsq,C.ll_fam_rsq,C.tx_pln,C.mt_plf,C.va_ect,C.da_opt_swa,C.typtx,C.tx_fix_cnt,C.cd_tau_ref_var,
C.cd_frq_int,C.cd_bas_cal_div,C.nomi,C.cd_dev,C.fl_swa,C.ay_nio_tie,C.cd_ric_tie,C.cd_ric_grp_tie,C.cd_sec_bay_tie,
C.cd_sec_gic_tie,C.cd_sec_nac_tie,C.da_not_ctm_fitch,C.da_not_ctm_mood,C.da_not_ctm_sp,C.da_not_ltm_fitch,C.da_not_ltm_mood,C.da_not_ltm_sp,
C.da_ten_ctm_fitch,C.da_ten_ctm_mood,C.da_ten_ctm_sp,C.da_ten_ltm_fitch,C.da_ten_ltm_mood,C.da_ten_ltm_sp,C.ll_gar_tie,
C.cd_grp_tie,C.ll_sec_bay_tie,C.ll_sec_gic_tie,C.ll_sec_nac_tie,C.ll_tie,C.ll_grp_tie,C.ll_nio_tie,C.ll_ric_tie,
C.ll_ric_grp_tie,C.ll_ent_tie,C.ll_nat_tie,C.cd_not_tie_ctm_fitch,C.cd_not_tie_ctm_mood,C.cd_not_tie_ctm_sp,C.cd_not_tie_ltm_fitch,
C.cd_not_tie_ltm_mood,C.cd_not_tie_ltm_sp,C.cd_rle_tie,C.ll_pay,C.fl_zon_eur,C.cd_ten_ctm_fitch,C.cd_ten_ctm_mood,
C.cd_ten_ctm_sp,C.cd_ten_ltm_fitch,C.cd_ten_ltm_mood,C.cd_ten_ltm_sp,C.fl_tie_eea,C.fl_tie_emg,C.id_tie,
C.fl_tie_ocd,C.fl_tie_zon_eur,C.cd_ent_tie,C.ll_typ_cll_jam,C.LC_SEN_JAM,C.CD_SJA_JAM,C.CT_SJA_JAM,C.LC_TYP_SJA_JAM,C.LL_JAM,
C.VA_ECT_JAM,C.CT_TAU_JAM,C.TX_FIX_JAM,C.CD_TAU_REF_VAR_JAM,C.CD_FRQ_INT_JAM,C.CD_BAS_CAL_DIV_JAM,C.MT_NOM_JAM,C.CD_DEV_JAM,
C.LL_TYP_CLL_JAM,C.LL_TYP_CLL_JAM_PTR,C.LC_SEN_JAM_PTR,C.CD_SJA_JAM_PTR,C.CT_SJA_JAM_PTR,C.LC_TYP_SJA_JAM_PTR,C.LL_JAM_PTR,C.VA_ECT_JAM_PTR,
C.CT_TAU_JAM_PTR,C.TX_FIX_JAM_PTR,C.CD_TAU_REF_VAR_JAM_PTR,C.CD_FRQ_INT_JAM_PTR,C.CD_BAS_CAL_DIV_JAM_PTR,C.MT_NOM_JAM_PTR,C.CD_DEV_JAM_PTR,
C.LL_TYP_CLL_JAM_PTR);
END; |
fin de fichier sur canal de communication
Répondre avec citation
Partager