1 PACKAGE BODY QRM_PA_AGGREGATION_P AS
2 /* $Header: qrmpaggb.pls 120.13 2005/09/20 11:49:16 csutaria ship $ */
3
4
5 /***************************************************************
6 This procedure determines the underlying currency of the report:
7 IF v_amount EXIST
8 IF already 1 underlying Ccy
9 IF Deals Ccy
10 Use Deals Ccy
11 IF SOB Ccy
12 Use SOB Ccy
13 IF Reporting Ccy
14 Use Rerportin Ccy
15 ELSIF Ccy is one of the aggregate attributes
16 IF Deals Ccy
17 Use Deals Ccy
18 IF SOB Ccy
19 Use SOB Ccy
20 IF Reporting Ccy
21 Use Rerportin Ccy
22 --make sure the aggregate level above the Ccy agg.
23 --cannot be Totaled
24 ELSE
25 Use Reporting Ccy
26
27 It finds the ccy aggregate lowest level and the underlying ccy.
28 It also determines the suffix '_USD' or '_SOB' to be appended
29 onto the columns names.
30 In addition, it calculates the currency multiplier in the case
31 of Reporting Ccy other than USD is used.
32 ***************************************************************/
33 PROCEDURE get_underlying_currency(p_name VARCHAR2,
34 p_ref_date DATE,
35 p_style VARCHAR2,
36 p_md_set_code VARCHAR2,
37 p_currency_source VARCHAR2,
38 p_curr_reporting VARCHAR2,
39 p_amount SYSTEM.QRM_VARCHAR_TABLE,
40 p_agg IN OUT NOCOPY SYSTEM.QRM_VARCHAR240_TABLE,
41 p_ccy_aggregate IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
42 p_type IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
43 p_order IN OUT NOCOPY XTR_MD_NUM_TABLE,
44 p_ccy_suffix OUT NOCOPY VARCHAR2,
45 p_ccy_multiplier OUT NOCOPY NUMBER,
46 p_ccy_agg_flag OUT NOCOPY NUMBER,
47 p_underlying_ccy OUT NOCOPY VARCHAR2,
48 p_ccy_case_flag OUT NOCOPY NUMBER,
49 p_measure VARCHAR2,
50 p_ccy_agg_level OUT NOCOPY NUMBER,
51 --only necessary for style='T'
52 p_table_col_curr IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
53 p_agg_col_curr IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
54 p_sensitivity SYSTEM.QRM_VARCHAR_TABLE)
55 IS
56
57 v_amount_type BOOLEAN;
58 v_uniform_ccy BOOLEAN := TRUE;
59 v_count NUMBER;
60 v_meas_temp VARCHAR2(240);
61 v_temp VARCHAR2(30);
62 v_deal_currency_col VARCHAR2(30);
63 i NUMBER(5) := 0;
64 j NUMBER(5);
65 v_count_sens NUMBER;
66 v_underlying_ccy_sens VARCHAR2(30);
67
68 CURSOR check_deal_ccy IS
69 SELECT DISTINCT dc.deal_ccy
70 --or reval_ccy for FX??
71 --Felicia will copy reval_ccy to currency for FX
72 --02/05/2002
73 FROM qrm_deal_calculations dc, qrm_deals_analyses da
74 WHERE da.analysis_name=p_name
75 AND da.deal_calc_id=dc.deal_calc_id;
76 CURSOR check_sob_ccy IS
77 SELECT DISTINCT dc.sob_ccy
78 FROM qrm_deal_calculations dc, qrm_deals_analyses da
79 WHERE da.analysis_name=p_name
80 AND da.deal_calc_id=dc.deal_calc_id;
81 CURSOR check_base_ccy IS
82 SELECT DISTINCT v.base_ccy
83 FROM qrm_deal_calculations dc, qrm_deals_analyses da,
84 qrm_current_deals_v v
85 WHERE da.analysis_name=p_name
86 AND da.deal_calc_id=dc.deal_calc_id
87 AND v.deal_no=dc.deal_no
88 AND v.transaction_no=dc.transaction_no;
89 CURSOR check_contra_ccy IS
90 SELECT DISTINCT v.contra_ccy
91 FROM qrm_deal_calculations dc, qrm_deals_analyses da,
92 qrm_current_deals_v v
93 WHERE da.analysis_name=p_name
94 AND da.deal_calc_id=dc.deal_calc_id
95 AND v.deal_no=dc.deal_no
96 AND v.transaction_no=dc.transaction_no;
97 CURSOR check_buy_ccy IS
98 SELECT DISTINCT v.buy_ccy
99 FROM qrm_deal_calculations dc, qrm_deals_analyses da,
100 qrm_current_deals_v v
101 WHERE da.analysis_name=p_name
102 AND da.deal_calc_id=dc.deal_calc_id
103 AND dc.deal_no=v.deal_no
104 AND dc.transaction_no=v.transaction_no;
105 CURSOR check_sell_ccy IS
106 SELECT DISTINCT v.sell_ccy
107 FROM qrm_deal_calculations dc, qrm_deals_analyses da,
108 qrm_current_deals_v v
109 WHERE da.analysis_name=p_name
110 AND da.deal_calc_id=dc.deal_calc_id
111 AND dc.deal_no=v.deal_no
112 AND dc.transaction_no=v.transaction_no;
113 CURSOR check_foreign_ccy IS
114 SELECT DISTINCT v.foreign_ccy
115 FROM qrm_deal_calculations dc, qrm_deals_analyses da,
116 qrm_current_deals_v v
117 WHERE da.analysis_name=p_name
118 AND da.deal_calc_id=dc.deal_calc_id
119 AND dc.deal_no=v.deal_no
120 AND dc.transaction_no=v.transaction_no;
121 CURSOR check_domestic_ccy IS
122 SELECT DISTINCT v.domestic_ccy
123 FROM qrm_deal_calculations dc, qrm_deals_analyses da,
124 qrm_current_deals_v v
125 WHERE da.analysis_name=p_name
126 AND da.deal_calc_id=dc.deal_calc_id
127 AND dc.deal_no=v.deal_no
128 AND dc.transaction_no=v.transaction_no;
129 CURSOR check_sensitivity_ccy IS
130 SELECT DISTINCT v.sensitivity_ccy
131 FROM qrm_deal_calculations dc, qrm_deals_analyses da,
132 qrm_current_deals_v v
133 WHERE da.analysis_name=p_name
134 AND da.deal_calc_id=dc.deal_calc_id
135 AND dc.deal_no=v.deal_no
136 AND dc.transaction_no=v.transaction_no;
137 CURSOR get_deal_currency_col IS
138 SELECT deal_currency_col FROM qrm_ana_atts_lookups
139 WHERE attribute_name=v_meas_temp;
140
141 BEGIN
142 IF (g_proc_level>=g_debug_level) THEN
143 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.GET_UNDERLYING_CCY');
144 END IF;
145 ------------------CURRENCY LOGIC-----------------------
146 --for sensitivity can only be averaged/summed if the underlying
147 --sensitivity_ccy is uniform
148 --check only if Currency Source is not Deal Currency, coz. for
149 --Deal Currency the checking is done in the loop below.
150 IF p_style='T' AND p_currency_source<>'D' THEN
151 OPEN check_sensitivity_ccy;
152 LOOP
153 FETCH check_sensitivity_ccy INTO v_underlying_ccy_sens;
154 EXIT WHEN check_sensitivity_ccy%ROWCOUNT>2 OR check_sensitivity_ccy%NOTFOUND;
155 END LOOP;
156 v_count_sens := check_sensitivity_ccy%ROWCOUNT;
157 CLOSE check_sensitivity_ccy;
158 END IF;
159
160 --Assumption: AMOUNT type is the only measure that
161 --can only be converted to different area
162 --get the underlying currency
163 IF p_currency_source='D' THEN --Deal ccy
164 IF p_style IN('X','C') THEN
165 j := 1;
166 ELSE --style='A','T'
167 j := p_agg.COUNT;
168 END IF;
169 FOR i IN 1..j LOOP
170 IF p_style='A' THEN
171 IF p_amount(i)='Y' THEN
172 v_amount_type := TRUE;
173 v_meas_temp := p_agg(i);
174 ELSE
175 v_amount_type := FALSE;
176 END IF;
177 ELSIF p_style='T' THEN
178 v_amount_type := TRUE;
179 v_meas_temp := p_agg(i);
180 ELSE --style C,X, the check was done earlier
181 v_amount_type := TRUE;
182 v_meas_temp := p_measure;
183 END IF;
184 IF v_amount_type THEN
185 --get the deal_currency_col from qrm_ana_atts_lookups
186 OPEN get_deal_currency_col;
187 FETCH get_deal_currency_col INTO v_deal_currency_col;
188 CLOSE get_deal_currency_col;
189 IF v_deal_currency_col='BASE_CCY' THEN
190 --check for uniform deal ccy
191 OPEN check_base_ccy;
192 LOOP
193 FETCH check_base_ccy INTO p_underlying_ccy;
194 EXIT WHEN check_base_ccy%ROWCOUNT>2 OR check_base_ccy%NOTFOUND;
195 END LOOP;
196 v_count := check_base_ccy%ROWCOUNT;
197 CLOSE check_base_ccy;
198 ELSIF v_deal_currency_col='CONTRA_CCY' THEN
199 --check for uniform deal ccy
200 OPEN check_contra_ccy;
201 LOOP
202 FETCH check_contra_ccy INTO p_underlying_ccy;
203 EXIT WHEN check_contra_ccy%ROWCOUNT>2 OR check_contra_ccy%NOTFOUND;
204 END LOOP;
205 v_count := check_contra_ccy%ROWCOUNT;
206 CLOSE check_contra_ccy;
207 ELSIF v_deal_currency_col='BUY_CCY' THEN
208 --check for uniform deal ccy
209 OPEN check_buy_ccy;
210 LOOP
211 FETCH check_buy_ccy INTO p_underlying_ccy;
212 EXIT WHEN check_buy_ccy%ROWCOUNT>2 OR check_buy_ccy%NOTFOUND;
213 END LOOP;
214 v_count := check_buy_ccy%ROWCOUNT;
215 CLOSE check_buy_ccy;
216 ELSIF v_deal_currency_col='SELL_CCY' THEN
217 --check for uniform deal ccy
218 OPEN check_sell_ccy;
219 LOOP
220 FETCH check_sell_ccy INTO p_underlying_ccy;
221 EXIT WHEN check_sell_ccy%ROWCOUNT>2 OR check_sell_ccy%NOTFOUND;
222 END LOOP;
223 v_count := check_sell_ccy%ROWCOUNT;
224 CLOSE check_sell_ccy;
225 ELSIF v_deal_currency_col='FOREIGN_CCY' THEN
226 --check for uniform deal ccy
227 OPEN check_foreign_ccy;
228 LOOP
229 FETCH check_foreign_ccy INTO p_underlying_ccy;
230 EXIT WHEN check_foreign_ccy%ROWCOUNT>2 OR check_foreign_ccy%NOTFOUND;
231 END LOOP;
232 v_count := check_foreign_ccy%ROWCOUNT;
233 CLOSE check_foreign_ccy;
234 ELSIF v_deal_currency_col='DOMESTIC_CCY' THEN
235 --check for uniform deal ccy
236 OPEN check_domestic_ccy;
237 LOOP
238 FETCH check_domestic_ccy INTO p_underlying_ccy;
239 EXIT WHEN check_domestic_ccy%ROWCOUNT>2 OR check_domestic_ccy%NOTFOUND;
240 END LOOP;
241 v_count := check_domestic_ccy%ROWCOUNT;
242 CLOSE check_domestic_ccy;
243 ELSIF v_deal_currency_col='SENSITIVITY_CCY' THEN
244 --check for uniform sensitivity ccy
245 OPEN check_sensitivity_ccy;
246 LOOP
247 FETCH check_sensitivity_ccy INTO p_underlying_ccy;
248 EXIT WHEN check_sensitivity_ccy%ROWCOUNT>2 OR check_sensitivity_ccy%NOTFOUND;
249 END LOOP;
250 v_count := check_sensitivity_ccy%ROWCOUNT;
251 CLOSE check_sensitivity_ccy;
252 ELSE
253 --check for uniform deal ccy
254 OPEN check_deal_ccy;
255 LOOP
256 FETCH check_deal_ccy INTO p_underlying_ccy;
257 EXIT WHEN check_deal_ccy%ROWCOUNT>2 OR check_deal_ccy%NOTFOUND;
258 END LOOP;
259 v_count := check_deal_ccy%ROWCOUNT;
260 CLOSE check_deal_ccy;
261 END IF;
262 END IF;
263 IF p_style='A' THEN
264 --check whether 1 ccy/column
265 IF v_count>1 THEN
266 p_ccy_case_flag := 0;
267 p_underlying_ccy := NULL;
268 EXIT;
269 ELSE --put the underlying currency into the array
270 p_agg_col_curr.EXTEND;
271 p_agg_col_curr(i) := p_underlying_ccy;
272 END IF;
273 --check whether 1 underlying ccy
274 IF i>1 THEN
275 IF v_temp<>p_underlying_ccy THEN
276 v_uniform_ccy := FALSE;
277 END IF;
278 END IF;
279 v_temp := p_underlying_ccy;
280 --For Table style try to either fill in the v_table_col_curr
281 --or append the suffix to p_agg.
282 --Unlike style='A', Table style determine currency per column basis
283 ELSIF p_style='T' THEN
284 --check whether 1 ccy for the particular column
285 IF v_count>1 THEN
286 IF p_sensitivity(i)='N' THEN
287 --revert to Reporting Currency
288 p_agg(i) := p_agg(i)||'_USD';
289 --leave p_table_col_curr NULL,necessary for Spot Rate
290 --validations
291 ELSE
292 --NULL for sensitivity
293 p_agg(i) := NULL;
294 END IF;
295 ELSE --use the currency
296 p_table_col_curr(i) := p_underlying_ccy;
297 END IF;
298 END IF;
299 END LOOP;
300 --determine v_ccy_case_flag
301 IF p_style='A' THEN
302 IF p_ccy_case_flag IS NULL THEN
303 IF v_uniform_ccy THEN
304 p_ccy_case_flag := 1;--1 ccy
305 ELSE
306 p_ccy_case_flag := 2;--1 ccy/col
307 p_underlying_ccy := NULL;
308 END IF;
309 END IF;
310 ELSIF p_style IN ('C','X') THEN
311 IF v_count=1 THEN
312 p_ccy_case_flag := 1;--1 ccy
313 ELSE
314 p_ccy_case_flag := 0;-->1 ccy
315 p_underlying_ccy := NULL;
316 END IF;
317 END IF;
318 ELSIF p_currency_source='B' THEN
319 --check for uniform deal ccy
320 OPEN check_sob_ccy;
321 LOOP
322 FETCH check_sob_ccy INTO p_underlying_ccy;
323 EXIT WHEN check_sob_ccy%ROWCOUNT>2 OR check_sob_ccy%NOTFOUND;
324 END LOOP;
325 v_count := check_sob_ccy%ROWCOUNT;
326 CLOSE check_sob_ccy;
327 --For Table style try to either fill in the v_table_col_curr
328 --or append the suffix to p_agg.
329 --Unlike style='A', Table style determine currency per column basis
330 IF p_style='T' THEN
331 IF v_count=1 THEN --use SOB
332 p_ccy_suffix := '_SOB';
333 ELSE --use Reporting Currency
334 p_ccy_suffix := '_USD';
335 p_underlying_ccy := NULL;
336 END IF;
337 FOR i IN 1..p_agg.COUNT LOOP
338 IF p_sensitivity(i)='N' THEN
339 p_agg(i) := p_agg(i)||p_ccy_suffix;
340 p_table_col_curr(i) := p_underlying_ccy;
341 ELSE --sensitivity
342 IF v_count_sens>1 THEN --NULL's the p_agg(i)
343 p_agg(i) := NULL;
344 ELSE --use the currency
345 p_table_col_curr(i) := v_underlying_ccy_sens;
346 END IF;
347 END IF;
348 END LOOP;
349 ELSE --style='A','C','X'
350 IF v_count=1 THEN
351 p_ccy_case_flag := 1;
352 ELSE
353 p_ccy_case_flag := 0;
354 p_underlying_ccy := NULL;
355 END IF;
356 END IF;
357 ELSIF p_style='T' THEN --p_currency_source='R'
358 --Append Reporting Currency suffix to the p_agg
359 FOR i IN 1..p_agg.COUNT LOOP
360 IF p_sensitivity(i) = 'N' THEN
361 p_agg(i) := p_agg(i)||'_USD';
362 ELSE --sensitivity
363 IF v_count_sens>1 THEN --NULL's the p_agg(i)
364 p_agg(i) := NULL;
365 ELSE --use the currency
366 p_table_col_curr(i) := v_underlying_ccy_sens;
367 END IF;
368 END IF;
369 END LOOP;
370 END IF;
371
372 --determine p_ccy_agg_flag only necessary when >1 ccy and style<>'T'
373 --and ccy pref<>'R'
374 IF p_style<>'T' THEN
375 IF (p_ccy_case_flag<>1 AND p_currency_source<>'R') THEN
376 FOR i IN REVERSE 1..p_agg.COUNT LOOP
377 IF p_currency_source='D' THEN
378 --1 ccy/col will have p_ccy_agg_flag=0
379 --make sure only the underlying ccy is considered
380 --i.e. BUY_AMOUNT only consider BUY_CCY
381 IF p_ccy_case_flag<>2 AND p_ccy_aggregate(i)='Y'
382 AND p_agg(i)=v_deal_currency_col THEN
383 p_ccy_agg_level := p_order(i);
384 IF p_type(i)='R' THEN
385 p_ccy_agg_flag := 1;
386 p_ccy_agg_level := i;
387 EXIT;
388 ELSE --p_type(i)='C'
389 p_ccy_agg_flag := 2;
390 p_ccy_agg_level := i;
391 EXIT;
392 END IF;
393 ELSE
394 IF i=1 THEN --last interation
395 p_ccy_agg_flag := 0;
396 p_ccy_agg_level := 0;
397 END IF;
398 END IF;
399 ELSE --p_currency_source='B'
400 IF p_agg(i)='COMPANY_CODE' THEN
401 p_ccy_agg_level := p_order(i);
402 IF p_type(i)='R' THEN
403 p_ccy_agg_flag := 1;
404 p_ccy_agg_level := i;
405 EXIT;
406 ELSE --p_type(i)='C'
407 p_ccy_agg_flag := 2;
408 p_ccy_agg_level := i;
409 EXIT;
410 END IF;
411 ELSE
412 IF i=1 THEN --last iteration
413 p_ccy_agg_flag := 0;
414 p_ccy_agg_level := 0;
415 END IF;
416 END IF;
417 END IF;
418 END LOOP;
419 END IF;
420
421 --determine p_ccy_suffix=ccy pref used
422 --not necessary for Table style
423 IF (p_ccy_case_flag=0 AND p_ccy_agg_flag=0) OR p_currency_source='R' THEN
424 --revert to reporting ccy
425 p_ccy_suffix := '_USD';
426 p_underlying_ccy := p_curr_reporting;
427 ELSE --no need to revert to reporting ccy
428 IF p_currency_source='B' THEN --ccy pref=SOB
429 p_ccy_suffix := '_SOB';
430 END IF;
431 END IF;
432 END IF; --p_style<>'T'
433
434 --Find multiplier
435 IF p_curr_reporting<>'USD' THEN
436 p_ccy_multiplier := get_fx_rate(p_md_set_code,
437 p_ref_date,
438 'USD',
439 p_curr_reporting,
440 'M');
441 END IF;
442 ------------------END OF CURRENCY LOGIC----------------
443 IF (g_proc_level>=g_debug_level) THEN
444 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.GET_UNDERLYING_CCY');
445 END IF;
446 END get_underlying_currency;
447
448
449
450 /***************************************************************
451 This procedure appends suffix '_USD' or '_SOB' to the actual
452 column name to be used in the dynamic SQL.
453 ***************************************************************/
454 PROCEDURE get_actual_column_name(p_name VARCHAR2,
455 p_amount SYSTEM.QRM_VARCHAR_TABLE,
456 p_agg IN OUT NOCOPY SYSTEM.QRM_VARCHAR240_TABLE,
457 p_actual_agg IN OUT NOCOPY SYSTEM.QRM_VARCHAR240_TABLE,
458 p_nom_fl IN OUT NOCOPY SYSTEM.QRM_VARCHAR240_TABLE,
459 p_denom_fl IN OUT NOCOPY SYSTEM.QRM_VARCHAR240_TABLE,
460 p_actual_agg_usd IN OUT NOCOPY SYSTEM.QRM_VARCHAR240_TABLE,
461 p_nom_fl_usd IN OUT NOCOPY SYSTEM.QRM_VARCHAR240_TABLE,
462 p_denom_fl_usd IN OUT NOCOPY SYSTEM.QRM_VARCHAR240_TABLE,
463 p_ccy_suffix VARCHAR2,
464 p_ccy_multiplier NUMBER,
465 p_need_usd_arr BOOLEAN,
466 p_origin IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
467 p_origin_usd IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
468 p_num_denom_origin IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
469 p_num_denom_origin_usd IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
470 p_type IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
471 p_sensitivity SYSTEM.QRM_VARCHAR_TABLE)
472 IS
473
474 i NUMBER(5);
475 v_col_name VARCHAR2(200);
476
477 CURSOR get_nom_denom IS
478 SELECT numerator,denominator,num_denom_origin
479 FROM qrm_ana_atts_lookups
480 WHERE attribute_name=v_col_name;
481 CURSOR get_origin IS
482 SELECT origin FROM qrm_ana_atts_lookups
483 WHERE attribute_name=v_col_name;
484 v_deal_type_arr SYSTEM.QRM_VARCHAR_table;
485 v_market_type_arr SYSTEM.QRM_VARCHAR_table;
486 v_count NUMBER;
487
488 BEGIN
489 IF (g_proc_level>=g_debug_level) THEN
490 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.GET_ACT_COL_NAME');
491 END IF;
492
493 p_actual_agg.EXTEND(p_agg.COUNT);
494 ----------This is for the Total ccy conversion purpose
495 --check whether we need USD ccy on backup
496 IF p_need_usd_arr THEN
497 p_actual_agg_usd.EXTEND(p_agg.COUNT);
498 p_nom_fl_usd.EXTEND(p_agg.COUNT);
499 p_denom_fl_usd.EXTEND(p_agg.COUNT);
500 p_origin_usd.EXTEND(p_agg.COUNT);
501 p_num_denom_origin_usd.EXTEND(p_agg.COUNT);
502 END IF;
503 IF (g_proc_level>=g_debug_level) THEN
504 xtr_risk_debug_pkg.dlog('get_actual_column_name: ' || 'p_nom_fl_usd.COUNT',p_nom_fl_usd.COUNT);
505 END IF;
506 ----------End This is for the Total ccy conversion purpose
507 --update col. name based on the currency conversion
508 FOR i IN 1..p_agg.COUNT LOOP
509 --Only Measure can be appended with _USD and _SOB for curr. consistency
510 --but cannot be sensitivity
511 IF p_type(i)='M' AND p_sensitivity(i)='N' THEN
512 IF p_ccy_suffix IS NOT NULL THEN
513 --need to modify p_nom_fl and p_denom_fl
514 v_col_name := p_agg(i)||p_ccy_suffix;
515 OPEN get_nom_denom;
516 FETCH get_nom_denom INTO p_nom_fl(i),p_denom_fl(i),p_num_denom_origin(i);
517 CLOSE get_nom_denom;
518 --p_nom_fl and p_denom_fl are already updated above
519 --modify the origin, bec. origin may not be the same
520 OPEN get_origin;
521 FETCH get_origin INTO p_origin(i);
522 CLOSE get_origin;
523 --need to modify the actual column name
524 --can only add ccy multiplier for amount type
525 IF p_ccy_suffix='_USD' AND p_ccy_multiplier IS NOT NULL
526 AND p_amount(i)='Y' THEN
527 --append ccy suffix and multiplier to the act. col. names
528
529 --WDK: The following code breaks NLS
530 --p_actual_agg(i) := p_agg(i)||p_ccy_suffix||'*'||TO_CHAR(p_ccy_multiplier);
531 --p_nom_fl(i) := p_nom_fl(i)||'*'||TO_CHAR(p_ccy_multiplier);
532
533 --WDK: NLS fix
534 p_actual_agg(i) := p_agg(i)||p_ccy_suffix||'*TO_NUMBER('''||TO_CHAR(p_ccy_multiplier)||''')';
535 p_nom_fl(i) := p_nom_fl(i)||'*TO_NUMBER('''||TO_CHAR(p_ccy_multiplier)||''')';
536 ELSE
537 p_actual_agg(i) := p_agg(i)||p_ccy_suffix;
538 END IF;
539 ELSE
540 p_actual_agg(i) := p_agg(i);
541 END IF;
542 ----------This is for the Total ccy conversion purpose
543 --check whether we need USD ccy on backup
544 IF p_need_usd_arr THEN
545 v_col_name := p_agg(i)||'_USD';
546 OPEN get_nom_denom;
547 FETCH get_nom_denom INTO p_nom_fl_usd(i),p_denom_fl_usd(i),p_num_denom_origin_usd(i);
548 CLOSE get_nom_denom;
549 --p_nom_fl and p_denom_fl are already updated above
550 --modify the origin, bec. origin may not be the same
551 OPEN get_origin;
552 FETCH get_origin INTO p_origin_usd(i);
553 CLOSE get_origin;
554 --need to modify the usd column name
555 --can only add ccy multiplier for amount type
556 IF p_ccy_multiplier IS NOT NULL AND p_amount(i)='Y' THEN
557 --append ccy suffix and multiplier to the act. col. names
558 --WDK: The following code breaks NLS
559 --p_actual_agg_usd(i) := p_agg(i)||'_USD*'||TO_CHAR(p_ccy_multiplier);
560 --p_nom_fl_usd(i) := p_nom_fl(i)||'*'||TO_CHAR(p_ccy_multiplier);
561 --WDK: NLS fix
562 p_actual_agg_usd(i) := p_agg(i)||'_USD*TO_NUMBER('''||TO_CHAR(p_ccy_multiplier)||''')';
563 p_nom_fl_usd(i) := p_nom_fl(i)||'*TO_NUMBER('''||TO_CHAR(p_ccy_multiplier)||''')';
564 --p_ccy_multiplier is not necessary for denom
565 p_denom_fl_usd(i) := p_denom_fl(i);
566 ELSE
567 p_actual_agg_usd(i) := p_agg(i)||'_USD';
568 --p_nom_fl and p_denom_fl are already updated above
569 END IF;
570 END IF;
571 ----------End This is for the Total ccy conversion purpose
572 ELSE
573 p_actual_agg(i) := p_agg(i);
574 END IF;
575 END LOOP;
576
577 -----------Testing
578 FOR i IN 1..p_actual_agg.COUNT LOOP
579 IF (g_proc_level>=g_debug_level) THEN
580 xtr_risk_debug_pkg.dlog('get_actual_column_name: ' || 'p_actual_agg(i)',i||':'||p_actual_agg(i));
581 END IF;
582 IF p_need_usd_arr THEN
583 IF (g_proc_level>=g_debug_level) THEN
584 xtr_risk_debug_pkg.dlog('get_actual_column_name: ' || 'p_nom_fl_usd(i)',i||':'||p_nom_fl_usd(i));
585 END IF;
586 END IF;
587 END LOOP;
588 -----------End Testing
589 IF (g_proc_level>=g_debug_level) THEN
590 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.GET_ACT_COL_NAME');
591 END IF;
592 END get_actual_column_name;
593
594
595
596 /***************************************************************
597 This procedure calculate the currency multipliers if there are
598 > 1 underlying currencies across the column aggregates.
599 The currency multipliers will then saved into an array that is
600 identical to the array used when inserting measures into
601 QRM_SAVED_ANALYSES_ROW.
602 Please refer to Bug 2566711.
603 ***************************************************************/
604 PROCEDURE get_col_ccy_multp(p_col_ccy_multp IN OUT NOCOPY XTR_MD_NUM_TABLE,
605 p_a1 IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
606 p_default_ccy VARCHAR2,
607 p_row_agg_no NUMBER,
608 p_max_col_no NUMBER,
609 p_md_set_code VARCHAR2,
610 p_ref_date DATE)
611 IS
612
613 v_prev_ccy gl_sets_of_books.currency_code%TYPE;
614 v_ccy gl_sets_of_books.currency_code%TYPE;
615 v_fx_rate NUMBER;
616
617 CURSOR get_sob_ccy (p_company_code VARCHAR2) IS
618 SELECT sob.currency_code
619 FROM gl_sets_of_books sob, xtr_party_info pinfo
620 WHERE pinfo.party_code = p_company_code AND
621 pinfo.set_of_books_id = sob.set_of_books_id;
622
623 BEGIN
624 IF (g_proc_level>=g_debug_level) THEN
625 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.GET_COL_CCY_MULTP');
626 xtr_risk_debug_pkg.dlog('get_col_ccy_multp: ' || 'p_currency_source',p_default_ccy);
627 END IF;
628
629 p_col_ccy_multp.EXTEND(p_max_col_no);
630
631 FOR i IN p_row_agg_no+1..p_a1.COUNT-1 LOOP
632 IF p_default_ccy='D' THEN --Deal ccy
633 --do some checks for optimization
634 IF p_a1(i) IS NOT NULL THEN
635 --Additional check for optimization
636 IF i>1 and p_a1(i)=p_a1(i-1) THEN
637 p_col_ccy_multp(i-p_row_agg_no) := v_fx_rate;
638 ELSE
639 v_fx_rate := get_fx_rate(p_md_set_code,
640 p_ref_date,
641 p_a1(i),
642 'USD',
643 'M');
644 p_col_ccy_multp(i-p_row_agg_no) := v_fx_rate;
645 END IF;
646 END IF;
647 ELSIF p_default_ccy='B' THEN --SOB ccy
648 --do some checks for optimization
649 IF p_a1(i) IS NOT NULL THEN
650 --Additional check for optimization
651 IF i>1 and p_a1(i)=p_a1(i-1) THEN
652 p_col_ccy_multp(i-p_row_agg_no) := v_fx_rate;
653 ELSE
654 OPEN get_sob_ccy(p_a1(i));
655 FETCH get_sob_ccy INTO v_ccy;
656 CLOSE get_sob_ccy;
657 --Additional check for optimization
658 IF v_ccy=v_prev_ccy THEN
659 p_col_ccy_multp(i-p_row_agg_no) := v_fx_rate;
660 ELSE
661 v_fx_rate := get_fx_rate(p_md_set_code,
662 p_ref_date,
663 p_a1(i),
664 'USD',
665 'M');
666 p_col_ccy_multp(i-p_row_agg_no) := v_fx_rate;
667 v_prev_ccy := v_ccy;
668 END IF;
669 END IF;
670 END IF;
671 END IF;
672 IF (g_proc_level>=g_debug_level) THEN
673 xtr_risk_debug_pkg.dlog('get_col_ccy_multp: ' || 'i:p_a1',i||':'||p_a1(i));
674 xtr_risk_debug_pkg.dlog('get_col_ccy_multp: ' || 'v_fx_rate',v_fx_rate);
675 xtr_risk_debug_pkg.dlog('i:p_col_ccy_multp',i||':'||p_col_ccy_multp(i-p_row_agg_no));
676 END IF;
677 END LOOP;
678
679 IF (g_proc_level>=g_debug_level) THEN
680 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.GET_COL_CCY_MULTP');
681 END IF;
682 END get_col_ccy_multp;
683
684
685
686 /***************************************************************
687 This function updates the columns' totals in the case of Crosstab
688 style with different underlying currencies for different columns.
689 Please refer to Bug 2566711.
690 ***************************************************************/
691 FUNCTION calc_col_total(p_col_ccy_multp IN OUT NOCOPY XTR_MD_NUM_TABLE,
692 p_measure IN OUT NOCOPY XTR_MD_NUM_TABLE,
693 p_max_col_no NUMBER,
694 p_ccy_case_flag NUMBER,
695 p_ccy_agg_flag NUMBER,
696 p_max_col_agg_level NUMBER)
697 RETURN NUMBER IS
698
699 v_total NUMBER := 0;
700 v_temp_meas XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
701 v_null BOOLEAN := TRUE;
702
703 BEGIN
704
705 v_temp_meas.EXTEND(p_measure.COUNT);
706
707 --do simple stuffs for 1 level column aggregate
708 IF p_max_col_agg_level=1 THEN
709 --only need to do extra if columns has different currencies
710 IF p_ccy_case_flag=0 and p_ccy_agg_flag=2 and p_max_col_no-1>1 THEN
711 FOR i IN 1..p_measure.COUNT-1 LOOP
712 IF p_measure(i) IS NOT NULL and p_col_ccy_multp(i) is not null
713 THEN
714 v_total := v_total + p_measure(i)*p_col_ccy_multp(i);
715 v_null := FALSE;
716 END IF;
717 END LOOP;
718 END IF;
719 ELSE
720 NULL;
721 --put in similar totaling logic to the one for
722 --QRM_SAVED_ANALYSES_ROW
723 END IF;
724
725 IF v_null THEN
726 v_total := NULL;
727 END IF;
728
729 RETURN v_total;
730
731 END calc_col_total;
732
733
734
735 /***************************************************************
736 This procedure insert 1 row at a time to qrm_saved_analyses_row
737 called when looping through the main cursor. Cannot do bulk insert
738 without initializing 100 arrays, thus, static insert per row basis
739 is the next viable option.
740 ***************************************************************/
741 FUNCTION insert_row(sh IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
742 sm IN OUT NOCOPY XTR_MD_NUM_TABLE,
743 p_row_agg_no NUMBER,
744 p_max_col_no NUMBER,
745 p_name VARCHAR2,
746 p_row NUMBER,
747 p_type NUMBER,
748 p_hidden VARCHAR2,
749 p_tot_currency VARCHAR2,
750 p_style VARCHAR2,
751 p_ref_date DATE,
752 p_ccy_case_flag NUMBER,
753 p_ccy_agg_flag NUMBER,
754 p_col_ccy_multp IN OUT NOCOPY XTR_MD_NUM_TABLE)
755 RETURN BOOLEAN IS
756
757 v_tot_currency VARCHAR2(20);
758 v_tot_currency_label VARCHAR2(25);
759 v_null BOOLEAN := TRUE;
760 -----Testing purposes
761 v_test VARCHAR2(4096); --WDK: this limit increased
762 -----End
763
764 BEGIN
765
766 IF (g_proc_level>=g_debug_level) THEN
767 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.INSERT_ROW');
768 END IF;
769
770 ---------Should be removed if multiple level columns is implemented
771 --If multiple col level is implemented, replace this with
772 --a function that transfer the value of the 'sm' array to a new array
773 --while inserting and calculating Total whenever necessary.
774 --Equivalent to the process for the row.
775 --calculate end column total
776 sm(p_max_col_no) := 0;
777 IF p_style<>'A' THEN
778
779 ----Start Bug 2566711
780 IF p_style='C' and p_ccy_case_flag=0 and p_ccy_agg_flag=2
781 and p_max_col_no-1>1 THEN
782 sm(p_max_col_no) := calc_col_total(p_col_ccy_multp,
783 sm,
784 p_max_col_no,
785 p_ccy_case_flag,
786 p_ccy_agg_flag,
787 1);
788 IF sm(p_max_col_no) IS NULL THEN
789 v_null := TRUE;
790 ELSE
791 v_null := FALSE;
792 END IF;
793 ----End Bug 2566711
794
795 ELSE
796 FOR i IN 1..p_max_col_no-1 LOOP
797 IF sm(i) IS NOT NULL THEN
798 sm(p_max_col_no):=sm(p_max_col_no)+sm(i);
799 v_null := FALSE;
800 END IF;
801 END LOOP;
802 END IF;
803 ELSE -- style=A cannot TOTAL at the end
804 sm(p_max_col_no) := NULL;
805 END IF;
806 --if all columns are null then make TOTAL null
807 IF v_null THEN
808 sm(p_max_col_no) := NULL;
809 END IF;
810 -------------------------------------------------------------------
811
812 ------------------Testing purpose
813 FOR j IN 1..p_row_agg_no+p_max_col_no LOOP
814 --WDK: v_test is only 4096 characters big. if bigger than that then do not want
815 --to error out, so dump contents and continue
816 IF (length(v_test)>4000) THEN
817 IF (g_proc_level>=g_debug_level) THEN
818 xtr_risk_debug_pkg.dlog('insert_row: ' || '',p_row||':'||v_test);
819 END IF;
820 v_test:='';
821 END IF;
822 IF j <= p_row_agg_no THEN
823 v_test := v_test||':'||sh(j);
824 ELSE
825 v_test := v_test||':'||sm(j-p_row_agg_no);
826 END IF;
827 END LOOP;
828 IF (g_proc_level>=g_debug_level) THEN
829 xtr_risk_debug_pkg.dlog('insert_row: ' || '',p_row||':'||v_test);
830 END IF;
831 ------------------End Testing purpose
832
833 --format the tot, ccy to be append-ready (for now formatting is done in SQL)
834 IF p_tot_currency IS NOT NULL THEN
835 v_tot_currency_label := ' ('||p_tot_currency||')';
836 v_tot_currency := p_tot_currency;
837 END IF;
838
839 IF p_row_agg_no<=5 AND p_max_col_no<=20 THEN
840 IF sh.COUNT<5 THEN
841 sh.EXTEND(5-p_row_agg_no);
842 END IF;
843 IF sm.COUNT<20 THEN
844 sm.EXTEND(20-p_max_col_no);
845 END IF;
846 INSERT INTO qrm_saved_analyses_row(analysis_name,seq_no,type,hidden,
847 tot_currency,tot_currency_label,a1,a2,a3,a4,a5,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,
848 m16,m17,m18,m19,m20,created_by,creation_date,last_updated_by,last_update_date,
849 last_update_login)
850 VALUES(p_name,p_row,p_type,p_hidden,v_tot_currency,v_tot_currency_label,
851 sh(1),sh(2),sh(3),sh(4),
852 sh(5),sm(1),sm(2),sm(3),sm(4),sm(5),sm(6),sm(7),sm(8),sm(9),sm(10),sm(11),
853 sm(12),sm(13),sm(14),sm(15),sm(16),sm(17),sm(18),sm(19),sm(20),
854 FND_GLOBAL.user_id,p_ref_date,FND_GLOBAL.user_id,p_ref_date,
855 FND_GLOBAL.login_id);
856
857 ELSIF p_row_agg_no<=10 AND p_max_col_no<=50 THEN
858 IF sh.COUNT<10 THEN
859 sh.EXTEND(10-p_row_agg_no);
860 END IF;
861 IF sm.COUNT<50 THEN
862 sm.EXTEND(50-p_max_col_no);
863 END IF;
864 INSERT INTO qrm_saved_analyses_row(analysis_name,seq_no,type,hidden,
865 tot_currency,tot_currency_label,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,m1,m2,m3,m4,
866 m5,m6,m7,m8,m9,m10,m11,
867 m12,m13,m14,m15,m16,m17,m18,m19,m20,m21,m22,m23,m24,m25,m26,m27,m28,m29,m30,
868 m31,m32,m33,m34,m35,m36,m37,m38,m39,m40,m41,m42,m43,m44,m45,m46,m47,m48,m49,
869 m50,created_by,creation_date,last_updated_by,last_update_date,
870 last_update_login)
871 VALUES(p_name,p_row,p_type,p_hidden,v_tot_currency,v_tot_currency_label,
872 sh(1),sh(2),sh(3),sh(4),
873 sh(5),sh(6),sh(7),sh(8),sh(9),sh(10),sm(1),sm(2),sm(3),sm(4),sm(5),sm(6),sm(7),
874 sm(8),sm(9),sm(10),sm(11),sm(12),sm(13),sm(14),sm(15),sm(16),sm(17),sm(18),
875 sm(19),sm(20),sm(21),sm(22),sm(23),sm(24),sm(25),sm(26),sm(27),sm(28),sm(29),
876 sm(30),sm(31),sm(32),sm(33),sm(34),sm(35),sm(36),sm(37),sm(38),sm(39),sm(40),
877 sm(41),sm(42),sm(43),sm(44),sm(45),sm(46),sm(47),sm(48),sm(49),sm(50),
878 FND_GLOBAL.user_id,p_ref_date,FND_GLOBAL.user_id,p_ref_date,
879 FND_GLOBAL.login_id);
880
881 ELSE --p_row_agg_no<=16 AND p_max_col_no<=100
882 IF sh.COUNT<16 THEN
883 sh.EXTEND(16-p_row_agg_no);
884 END IF;
885 IF sm.COUNT<100 THEN
886 sm.EXTEND(100-p_max_col_no);
887 END IF;
888 INSERT INTO qrm_saved_analyses_row(analysis_name,seq_no,type,hidden,
889 tot_currency,tot_currency_label,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,
890 a14,a15,a16,m1,m2,m3,
891 m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,m18,m19,m20,m21,m22,m23,m24,
892 m25,m26,m27,m28,m29,m30,m31,m32,m33,m34,m35,m36,m37,m38,m39,m40,m41,m42,m43,
893 m44,m45,m46,m47,m48,m49,m50,m51,m52,m53,m54,m55,m56,m57,m58,m59,m60,m61,m62,
894 m63,m64,m65,m66,m67,m68,m69,m70,m71,m72,m73,m74,m75,m76,m77,m78,m79,m80,m81,
895 m82,m83,m84,m85,m86,m87,m88,m89,m90,m91,m92,m93,m94,m95,m96,m97,m98,m99,m100,
896 created_by,creation_date,last_updated_by,last_update_date,last_update_login)
897 VALUES(p_name,p_row,p_type,p_hidden,v_tot_currency,v_tot_currency_label,
898 sh(1),sh(2),sh(3),sh(4),
899 sh(5),sh(6),sh(7),sh(8),sh(9),sh(10),sh(11),sh(12),sh(13),sh(14),sh(15),sh(16),
900 sm(1),sm(2),sm(3),sm(4),sm(5),sm(6),sm(7),sm(8),sm(9),sm(10),sm(11),sm(12),
901 sm(13),sm(14),sm(15),sm(16),sm(17),sm(18),sm(19),sm(20),sm(21),sm(22),sm(23),
902 sm(24),sm(25),sm(26),sm(27),sm(28),sm(29),sm(30),sm(31),sm(32),sm(33),sm(34),
903 sm(35),sm(36),sm(37),sm(38),sm(39),sm(40),sm(41),sm(42),sm(43),sm(44),sm(45),
904 sm(46),sm(47),sm(48),sm(49),sm(50),sm(51),sm(52),sm(53),sm(54),sm(55),sm(56),
905 sm(57),sm(58),sm(59),sm(60),sm(61),sm(62),sm(63),sm(64),sm(65),sm(66),sm(67),
906 sm(68),sm(69),sm(70),sm(71),sm(72),sm(73),sm(74),sm(75),sm(76),sm(77),sm(78),
907 sm(79),sm(80),sm(81),sm(82),sm(83),sm(84),sm(85),sm(86),sm(87),sm(88),sm(89),
908 sm(90),sm(91),sm(92),sm(93),sm(94),sm(95),sm(96),sm(97),sm(98),sm(99),sm(100),
909 FND_GLOBAL.user_id,p_ref_date,FND_GLOBAL.user_id,p_ref_date,
910 FND_GLOBAL.login_id);
911 END IF;
912
913 IF (g_event_level>=g_debug_level) THEN --bug 3236479
914 XTR_RISK_DEBUG_PKG.dlog('DML','Inserted into QRM_SAVED_ANALYSES_ROW',
915 'QRM_PA_AGGREGATION_P.INSERT_ROW',g_event_level);
916 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.INSERT_ROW');
917 END IF;
918
919 RETURN TRUE;
920 EXCEPTION
921 WHEN OTHERS THEN
922 IF (g_ERROR_level>=g_debug_level) THEN
923 xtr_risk_debug_pkg.dlog('EXCEPTION','UNEXPECTED',
924 'QRM_PA_AGGREGATION_P.INSERT_ROW',G_ERROR_LEVEL);
925 END IF;
926 RETURN FALSE;
927 END insert_row;
928
929
930
931 /***************************************************************
932 This procedure constructs dynamic SQL statement that does the
933 aggregation.
934 ***************************************************************/
935 PROCEDURE create_cursor (p_name VARCHAR2,
936 p_style VARCHAR2,
937 p_analysis_type VARCHAR2,
938 p_agg IN OUT NOCOPY SYSTEM.QRM_VARCHAR240_TABLE,
939 p_type IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
940 p_nom IN OUT NOCOPY SYSTEM.QRM_VARCHAR240_TABLE,
941 p_denom IN OUT NOCOPY SYSTEM.QRM_VARCHAR240_TABLE,
942 p_tot_avg SYSTEM.QRM_VARCHAR_TABLE,
943 p_sql OUT NOCOPY VARCHAR2,
944 p_sql_col OUT NOCOPY VARCHAR2,
945 p_row_agg_no OUT NOCOPY NUMBER,
946 p_measure_no IN OUT NOCOPY NUMBER,
947 p_origin IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
948 p_tb_calc_used IN OUT NOCOPY BOOLEAN,
949 p_tb_calc_used_col IN OUT NOCOPY BOOLEAN,
950 p_need_usd_arr BOOLEAN,
951 p_ccy_suffix VARCHAR2,
952 p_ccy_multiplier NUMBER,
953 p_amount SYSTEM.QRM_VARCHAR_TABLE,
954 p_table_col_curr IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
955 p_num_denom_origin IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
956 p_curr_reporting VARCHAR2,
957 p_sensitivity SYSTEM.QRM_VARCHAR_TABLE) IS
958
959 --IMPORTANT: v_act_agg include multiplier
960 v_act_agg SYSTEM.QRM_VARCHAR240_TABLE := SYSTEM.QRM_VARCHAR240_TABLE();
961 v_agg_usd SYSTEM.QRM_VARCHAR240_TABLE := SYSTEM.QRM_VARCHAR240_TABLE();
962 v_origin_usd SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
963 v_nom_fl_usd SYSTEM.QRM_VARCHAR240_TABLE := SYSTEM.QRM_VARCHAR240_TABLE();
964 v_denom_fl_usd SYSTEM.QRM_VARCHAR240_TABLE := SYSTEM.QRM_VARCHAR240_TABLE();
965 v_num_denom_origin_usd SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
966 v_from VARCHAR2(100):=' FROM qrm_deal_calculations dc,qrm_deals_analyses da,qrm_current_deals_v v';
967 v_where VARCHAR2(255):=' WHERE da.deal_calc_id=dc.deal_calc_id AND da.analysis_name=:analysis_name AND dc.deal_no=v.deal_no AND dc.transaction_no=v.transaction_no';
968 i NUMBER(5);
969 j NUMBER(5);
970 v_aggregate_level NUMBER(5);
971 --whether qrm_saved_analyses_col is used or not
972 v_col_used BOOLEAN := FALSE;
973 --whether qrm_deal_calculations is used or not for p_sql_col
974 v_table_used_col BOOLEAN := FALSE;
975 v_origin VARCHAR2(1);
976 v_col_name VARCHAR2(200);
977 --Special indicators to treat DEAL_SUBTYPE
978 v_deal_subtype BOOLEAN := FALSE;
979 v_deal_subtype_col BOOLEAN := FALSE;
980 v_where_col VARCHAR2(255);
981
982 BEGIN
983 IF (g_proc_level>=g_debug_level) THEN
984 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.CREATE_CURSOR');
985 END IF;
986
987 --call get_actual_column_name proc
988 IF p_style<>'T' THEN
989 get_actual_column_name(p_name,p_amount,p_agg,v_act_agg,
990 p_nom,p_denom,v_agg_usd,
991 v_nom_fl_usd,v_denom_fl_usd,p_ccy_suffix,
992 p_ccy_multiplier,p_need_usd_arr,p_origin,v_origin_usd,
993 p_num_denom_origin,v_num_denom_origin_usd,
994 p_type, p_sensitivity);
995 ELSE
996 --for style='T', the p_agg already contains the actual att. name
997 --hence just need to append ccy multiplier and copy them to v_act_agg
998 v_act_agg.EXTEND(p_agg.COUNT);
999 FOR i IN 1..p_agg.COUNT LOOP
1000 --NULL in p_table_col_curr means that it 's converted to
1001 --Reporting Currency.
1002 --Those are the one that we need to attach multiplier on.
1003 v_act_agg(i) := p_agg(i);
1004 IF p_table_col_curr(i) IS NULL THEN
1005 --NULL means value is converted to reporting currency
1006 p_table_col_curr(i) := p_curr_reporting;
1007 IF (g_proc_level>=g_debug_level) THEN
1008 xtr_risk_debug_pkg.dlog('create_cursor: ' || 'p_ccy_multiplier',p_ccy_multiplier);
1009 END IF;
1010 --ccy multiplier only applicable to amount type
1011 IF p_ccy_multiplier IS NOT NULL AND p_amount(i)='Y' THEN
1012 v_act_agg(i) := v_act_agg(i)||'*TO_NUMBER('''||p_ccy_multiplier||''')';
1013 p_nom(i) := p_nom(i)||'*TO_NUMBER('''||p_ccy_multiplier||''')';
1014 END IF;
1015 END IF;
1016 END LOOP;
1017 END IF;
1018 -----------Testing
1019 FOR i IN 1..v_act_agg.COUNT LOOP
1020 IF (g_proc_level>=g_debug_level) THEN
1021 xtr_risk_debug_pkg.dlog('create_cursor: ' || 'v_act_agg(i)',i||':'||v_act_agg(i));
1022 END IF;
1023 END LOOP;
1024 -----------End Testing
1025
1026 --see whether we need join with qrm_save_analyses_col or not
1027 --style Crosstab Timebuckets and Crosstab need to join
1028 IF p_style IN ('C','X') THEN
1029 v_col_used := TRUE;
1030 END IF;
1031 --need to construct p_sql_col for style='C'
1032 IF p_style='C' THEN
1033 p_sql_col := 'SELECT DISTINCT ';
1034 END IF;
1035 p_sql := 'SELECT ';
1036
1037 FOR j IN 1..v_act_agg.COUNT LOOP
1038 --for cross-tab and aggregated-table
1039 --IF (p_style='C') OR (p_style='A') THEN
1040 IF (g_proc_level>=g_debug_level) THEN
1041 xtr_risk_debug_pkg.dlog('create_cursor: ' || 'j:v_act_agg(j)',j||':'||v_act_agg(j));
1042 xtr_risk_debug_pkg.dlog('create_cursor: ' || 'j:p_type(j)',j||':'||p_type(j));
1043 END IF;
1044 IF p_type(j)='R' THEN
1045 --check whether att. is from table or view
1046 IF p_origin(j)='T' THEN
1047 p_sql := p_sql||'dc.'||v_act_agg(j);
1048 ELSIF p_origin(j)='V' THEN
1049 --check for deal_subtype
1050 --SELECT dst.user_deal_subtype deal_subtype
1051 IF v_act_agg(j)='DEAL_SUBTYPE' THEN
1052 p_sql := p_sql||'dst.user_deal_subtype '||v_act_agg(j);
1053 v_deal_subtype := TRUE;
1054 ELSE
1055 p_sql := p_sql||'v.'||v_act_agg(j);
1056 END IF;
1057 ELSE --IF p_origin(j)='B'
1058 p_sql := p_sql||'tb.'||v_act_agg(j);
1059 p_tb_calc_used := TRUE;
1060 END IF;
1061
1062 --find the aggregate level, if the next aggregate is of type M
1063 IF p_type(j+1)<>'R' THEN
1064 p_row_agg_no := j;
1065 IF p_style='A' THEN
1066 v_aggregate_level := j;
1067 END IF;
1068 END IF;
1069 --xtr_risk_debug_pkg.dlog('p_row_agg_no',p_row_agg_no);
1070 --if style = X and the next one is Measure then v_aggregate_level=j
1071 IF p_style='X' AND p_type(j+1)='M' THEN
1072 p_sql := p_sql||',c.seq_no';
1073 v_aggregate_level := j;
1074 END IF;
1075
1076 ELSIF p_type(j)='C' THEN --only style=C has type=C
1077 p_sql := p_sql||'c.seq_no';
1078 --dynamic sql for column header
1079 --check whether att. is from table or view
1080 IF p_origin(j)='T' THEN
1081 p_sql_col := p_sql_col||'dc.'||v_act_agg(j);
1082 v_table_used_col := TRUE;
1083 --exclude the null value
1084 v_where_col := v_where_col||' AND dc.'||v_act_agg(j)||' IS NOT NULL';
1085 ELSIF p_origin(j)='V' THEN
1086 --check for deal_subtype
1087 --SELECT dst.user_deal_subtype deal_subtype
1088 IF v_act_agg(j)='DEAL_SUBTYPE' THEN
1089 p_sql_col := p_sql_col||'dst.user_deal_subtype '||v_act_agg(j);
1090 v_deal_subtype := TRUE;
1091 v_deal_subtype_col := TRUE;
1092 ELSE
1093 p_sql_col := p_sql_col||'v.'||v_act_agg(j);
1094 END IF;
1095 --exclude the null value
1096 v_where_col := v_where_col||' AND v.'||v_act_agg(j)||' IS NOT NULL';
1097 ELSE --IF p_origin(j)='B'
1098 --xtr_risk_debug_pkg.dlog('C->B: v_act_agg(j)',v_act_agg(j));
1099 p_sql_col := p_sql_col||'tb.'||v_act_agg(j);
1100 p_tb_calc_used := TRUE;
1101 p_tb_calc_used_col := TRUE;
1102 --exclude the null value
1103 v_where_col := v_where_col||' AND tb.'||v_act_agg(j)||' IS NOT NULL';
1104 END IF;
1105
1106 --find the aggregate level, if the next aggregate is of type M
1107 --if so then we found the no. of aggregate level
1108 IF p_type(j+1)='M' THEN
1109 v_aggregate_level := j;
1110 END IF;
1111
1112 --p_type(j)='M' AND
1113 ELSIF v_act_agg(j) IN ('NUMBER_DEALS','NUMBER_DEALS_USD','NUMBER_DEALS_SOB') THEN
1114 --this attribute calculate the number of deals aggregated/displayed
1115 IF p_style='T' THEN --just put 1 bec. 1 row represent 1 deal
1116 p_sql := p_sql||'1,NULL';
1117 ELSE
1118 p_sql := p_sql||'COUNT(*),NULL';
1119 END IF;
1120 p_measure_no := p_measure_no+2;
1121 IF p_need_usd_arr THEN
1122 p_sql := p_sql||',NULL,NULL';
1123 p_measure_no := p_measure_no+2;
1124 END IF;
1125
1126 --p_type(j)='M' AND
1127 --xtr_risk_debug_pkg.dlog('M->T: j:v_act_agg,p_origin,p_tot_avg=',j||':'||v_act_agg(j)||','||p_origin(j)||','||p_tot_avg(j));
1128 ELSIF v_act_agg(j) IS NOT NULL AND p_tot_avg(j)='T' THEN
1129 IF p_origin(j)='T' THEN
1130 p_sql := p_sql||'SUM(dc.'||v_act_agg(j)||'),NULL';
1131 ELSIF p_origin(j)='V' THEN
1132 p_sql := p_sql||'SUM(v.'||v_act_agg(j)||'),NULL';
1133 ELSE --IF p_origin(j)='B'
1134 p_sql := p_sql||'SUM(tb.'||v_act_agg(j)||'),NULL';
1135 p_tb_calc_used := TRUE;
1136 END IF;
1137 p_measure_no := p_measure_no+2;
1138 --if style = X then v_aggregate_level=j-1
1139 IF p_need_usd_arr THEN
1140 --first find the origin bec. col+'_usd' may be different from
1141 --the original
1142 v_col_name := v_act_agg(j)||'_USD';
1143 --xtr_risk_debug_pkg.dlog('M->T: v_col_name=',v_col_name);
1144 --xtr_risk_debug_pkg.dlog('M->T: v_origin=',v_origin);
1145 --xtr_risk_debug_pkg.dlog('M->T: v_origin_usd(j)=',v_origin_usd(j));
1146 --xtr_risk_debug_pkg.dlog('M->T: v_agg_usd(j)=',v_agg_usd(j));
1147 IF v_origin_usd(j)='T' THEN
1148 p_sql := p_sql||',SUM(dc.'||v_agg_usd(j)||'),NULL';
1149 ELSIF v_origin_usd(j)='V' THEN
1150 p_sql := p_sql||',SUM(v.'||v_agg_usd(j)||'),NULL';
1151 ELSE --IF v_origin_usd(j)='B'
1152 p_sql := p_sql||',SUM(tb.'||v_agg_usd(j)||'),NULL';
1153 p_tb_calc_used := TRUE;
1154 END IF;
1155 p_measure_no := p_measure_no+2;
1156 END IF;
1157 --xtr_risk_debug_pkg.dlog('p_measure_no',p_measure_no);
1158 --p_type(j)='M' AND
1159 ELSIF v_act_agg(j) IS NOT NULL AND p_tot_avg(j)='A' THEN
1160 --xtr_risk_debug_pkg.dlog('M->A: v_act_agg',v_act_agg(j));
1161 IF p_nom(j) IS NULL THEN
1162 p_nom(j) := 'NULL';
1163 END IF;
1164 IF p_denom(j) IS NULL THEN
1165 p_denom(j) := 'NULL';
1166 END IF;
1167 p_sql := p_sql||p_nom(j)||','||p_denom(j);
1168 IF p_num_denom_origin(j) IN ('2','3') THEN
1169 p_tb_calc_used := TRUE;
1170 END IF;
1171 p_measure_no := p_measure_no+2;
1172 --if style = X then v_aggregate_level=j-1
1173 --xtr_risk_debug_pkg.dlog('j,v_nom_fl_usd.COUNT',j||','||v_nom_fl_usd.COUNT);
1174 IF p_need_usd_arr THEN
1175 --xtr_risk_debug_pkg.dlog('M->T: v_nom_fl_usd(j)=',v_nom_fl_usd(j));
1176 IF v_nom_fl_usd(j) IS NULL THEN
1177 v_nom_fl_usd(j) := 'NULL';
1178 END IF;
1179 --xtr_risk_debug_pkg.dlog('M->T: v_denom_fl_usd(j)=',v_denom_fl_usd(j));
1180 IF v_denom_fl_usd(j) IS NULL THEN
1181 v_denom_fl_usd(j) := 'NULL';
1182 END IF;
1183 p_sql := p_sql||','||v_nom_fl_usd(j)||','||v_denom_fl_usd(j);
1184 IF v_num_denom_origin_usd(j) IN ('2','3') THEN
1185 p_tb_calc_used := TRUE;
1186 END IF;
1187 p_measure_no := p_measure_no+2;
1188 END IF;
1189 ELSE
1190 p_sql := p_sql||'NULL,NULL';
1191 p_measure_no := p_measure_no+2;
1192 END IF;
1193 --add comma unless the end of SELECT clause
1194 IF j < v_act_agg.COUNT THEN
1195 p_sql := p_sql||',';
1196 END IF;
1197 END LOOP; --v_act_agg LOOP
1198
1199 --Construct p_sql_col
1200 IF p_style='C' THEN
1201 --add BULK COLLECT clause for p_sql_col
1202 j := 0;
1203 p_sql_col := p_sql_col||' BULK COLLECT INTO ';
1204 FOR i IN 1..v_aggregate_level LOOP
1205 IF p_type(i)='C' THEN
1206 j := j+1;
1207 IF j>1 THEN
1208 p_sql_col := p_sql_col||',';
1209 END IF;
1210 p_sql_col := p_sql_col||':'||TO_CHAR(j);
1211 END IF;
1212 END LOOP;
1213
1214 --add FROM clause for p_sql_col
1215 p_sql_col := p_sql_col||v_from;
1216 IF p_tb_calc_used_col THEN
1217 p_sql_col := p_sql_col||',qrm_tb_calculations tb';
1218 END IF;
1219 --FROM ...,xtr_deal_subtypes dst
1220 IF v_deal_subtype_col THEN
1221 p_sql_col := p_sql_col||',xtr_deal_subtypes dst';
1222 END IF;
1223
1224 --add WHERE clause for p_sql_col
1225 p_sql_col := p_sql_col||v_where||v_where_col;
1226 IF p_tb_calc_used_col THEN
1227 --p_sql_col := p_sql_col||' AND dc.deal_no=tb.deal_no AND dc.transaction_no=tb.transaction_no'; --bug 2550158
1228 p_sql_col := p_sql_col||' AND dc.deal_no=tb.deal_no AND dc.transaction_no=tb.transaction_no AND dc.market_data_set = tb.market_data_set'; --bug 2550158
1229 IF p_analysis_type='P' THEN
1233 p_sql_col := p_sql_col||' AND (tb.start_date IS NULL OR NVL(dc.gap_date,:ref_date+1)>=tb.start_date) AND NVL(dc.gap_date,:ref_date+1)<=tb.end_date';--bug 2638277
1230 --Bug 2318165 no.7
1231 p_sql_col := p_sql_col||' AND (tb.pos_start_date IS NULL OR :end_date>=tb.pos_start_date) AND (tb.pos_end_date IS NULL OR :end_date<tb.pos_end_date)'; --bug 3085258
1232 ELSIF p_analysis_type='G' THEN
1234 --p_sql_col := p_sql_col||' AND (tb.start_date IS NULL OR NVL(dc.gap_date,:ref_date+1)>=tb.start_date) AND NVL(dc.gap_date,:ref_date+1)<tb.end_date';--bug 2638277
1235 ELSE --p_analysis_type='M' THEN
1236 --p_sql_col := p_sql_col||' AND (tb.start_date IS NULL OR NVL(v.end_date,:ref_date+1)>=tb.start_date) AND NVL(v.end_date,:ref_date+1)<tb.end_date';
1237 --bug 2637950
1238 p_sql_col := p_sql_col||' AND ((v.start_date>=(:ref_date+1) AND NVL(v.end_date,v.start_date+1)>=tb.start_date AND NVL(v.end_date,v.start_date+1)<=tb.end_date)'||
1239 ' OR (v.start_date< (:ref_date+1) AND NVL(v.end_date,:ref_date+1)>=tb.start_date AND NVL(v.end_date,:ref_date+1)<=tb.end_date))';
1240 END IF;
1241 END IF;
1242 --WHERE ... AND dst.deal_subtype=v.deal_subtype
1243 IF v_deal_subtype_col THEN
1244 p_sql_col := p_sql_col||' AND dst.deal_subtype=v.deal_subtype';
1245 END IF;
1246
1247 --add ORDER BY clause for p_sql_col
1248 j := 0;
1249 p_sql_col := p_sql_col||' ORDER BY ';
1250 FOR i IN 1..v_aggregate_level LOOP
1251 IF p_type(i)='C' THEN
1252 j := j+1;
1253 IF j>1 THEN
1254 p_sql_col := p_sql_col||',';
1255 END IF;
1256 p_sql_col := p_sql_col||TO_CHAR(j);
1257 END IF;
1258 END LOOP;
1259 p_sql_col := p_sql_col||';';
1260 END IF;
1261
1262 --add FROM clause
1263 p_sql := p_sql||v_from;
1264 IF v_col_used THEN
1265 p_sql := p_sql||','||'qrm_saved_analyses_col c';
1266 END IF;
1267 IF p_tb_calc_used THEN
1268 p_sql := p_sql||','||'qrm_tb_calculations tb';
1269 END IF;
1270 --FROM ...,xtr_deal_subtypes dst
1271 IF v_deal_subtype THEN
1272 p_sql := p_sql||',xtr_deal_subtypes dst';
1273 END IF;
1274
1275 --add WHERE clause
1276 p_sql := p_sql||v_where;
1277 IF v_col_used THEN
1278 p_sql := p_sql||' AND c.analysis_name=da.analysis_name';
1279 --
1280 IF p_style='C' THEN
1281 p_sql := p_sql||' AND c.seq_no_key=';
1282 FOR j IN 1..v_aggregate_level LOOP
1283 IF p_type(j)='C' THEN
1284 --check whether att. is from table or view
1285 IF p_origin(j)='T' THEN
1286 p_sql := p_sql||'dc.'||v_act_agg(j);
1287 ELSIF p_origin(j)='V' THEN
1288 IF v_deal_subtype_col THEN
1289 p_sql := p_sql||'dst.user_deal_subtype';
1290 ELSE
1291 p_sql := p_sql||'v.'||v_act_agg(j);
1292 END IF;
1293 ELSE --p_origin(j)='B' THEN
1294 p_sql := p_sql||'tb.'||v_act_agg(j);
1295 END IF;
1296 END IF;
1297 END LOOP;
1298 END IF;
1299 END IF;
1300 IF p_style='X' AND p_analysis_type='P' THEN
1301 p_sql := p_sql||' AND v.deal_date<=c.end_date AND (v.end_date IS NULL OR c.end_date<=v.end_date)';
1302 ELSIF p_style='X' AND p_analysis_type='G' THEN
1303 p_sql := p_sql||' AND NVL(dc.gap_date,:ref_date+1)>=c.start_date AND NVL(dc.gap_date,:ref_date+1)<=c.end_date';--bug 2638277
1304 --p_sql := p_sql||' AND NVL(dc.gap_date,:ref_date+1)>=c.start_date AND NVL(dc.gap_date,:ref_date+1)<c.end_date';--bug 2638277
1305 ELSIF p_style='X' AND p_analysis_type='M' THEN
1306 --p_sql := p_sql||' AND c.start_date<=NVL(v.end_date,:ref_date+1) AND NVL(v.end_date,:ref_date+1)<c.end_date';
1307 --bug 2637950
1308 p_sql := p_sql||' AND ((v.start_date>=(:ref_date+1) AND NVL(v.end_date,v.start_date+1)>=c.start_date AND NVL(v.end_date,v.start_date+1)<=c.end_date)'||
1309 ' OR (v.start_date< (:ref_date+1) AND NVL(v.end_date,:ref_date+1)>=c.start_date AND NVL(v.end_date,:ref_date+1)<=c.end_date))';
1310 END IF;
1311 IF p_tb_calc_used THEN
1312 --p_sql := p_sql||' AND dc.deal_no=tb.deal_no AND dc.transaction_no=tb.transaction_no'; --bug 2550158
1313 p_sql := p_sql||' AND dc.deal_no=tb.deal_no AND dc.transaction_no=tb.transaction_no AND dc.market_data_set = tb.market_data_set'; --bug 2550158
1314 IF p_style='X' THEN
1315 IF p_analysis_type='P' THEN
1316 p_sql := p_sql||' AND (c.end_date>=tb.pos_start_date OR tb.pos_start_date IS NULL) AND (tb.pos_end_date IS NULL OR c.end_date<tb.pos_end_date)';
1317 ELSE
1318 --p_sql := p_sql||' AND (c.end_date>=tb.start_date OR tb.start_date IS NULL) AND c.end_date<=tb.end_date'; --bug 2638277
1319 p_sql := p_sql||' AND (c.start_date<=tb.end_date OR tb.start_date IS NULL) AND c.end_date>=tb.end_date'||
1320 ' AND (NVL(v.end_date,:ref_date+1)>=tb.start_date OR tb.start_date IS NULL) AND NVL(v.end_date,:ref_date+1)<=tb.end_date'; --bug 2638277
1321 END IF;
1322 ELSE --single period
1323 IF p_analysis_type='P' THEN
1324 --Bug 2318165 no.7
1325 p_sql := p_sql||' AND (tb.pos_start_date IS NULL OR :end_date>=tb.pos_start_date) AND (tb.pos_end_date IS NULL OR :end_date<tb.pos_end_date)'; --bug 3085258
1326 ELSIF p_analysis_type='G' THEN
1327 p_sql := p_sql||' AND (tb.start_date IS NULL OR NVL(dc.gap_date,:ref_date+1)>=tb.start_date) AND NVL(dc.gap_date,:ref_date+1)<=tb.end_date'; --bug 2638277
1328 --p_sql := p_sql||' AND (tb.start_date IS NULL OR NVL(dc.gap_date,:ref_date+1)>=tb.start_date) AND NVL(dc.gap_date,:ref_date+1)<tb.end_date'; --bug 2638277
1329 ELSE --p_analysis_type='M' THEN
1330 --p_sql := p_sql||' AND (tb.start_date IS NULL OR NVL(v.end_date,:ref_date+1)>=tb.start_date) AND NVL(v.end_date,:ref_date+1)<tb.end_date';
1331 --bug 2637950
1332 p_sql := p_sql||' AND ((v.start_date>=(:ref_date+1) AND NVL(v.end_date,v.start_date+1)>=tb.start_date AND NVL(v.end_date,v.start_date+1)<=tb.end_date)'||
1333 ' OR (v.start_date< (:ref_date+1) AND NVL(v.end_date,:ref_date+1)>=tb.start_date AND NVL(v.end_date,:ref_date+1)<=tb.end_date))';
1334 END IF;
1335 END IF;
1336 END IF;
1337 --WHERE ... AND dst.deal_subtype=v.deal_subtype
1338 IF v_deal_subtype THEN
1339 p_sql := p_sql||' AND dst.deal_subtype=v.deal_subtype AND dst.deal_type=v.deal_type';
1340 END IF;
1341
1342 IF p_style<>'T' THEN
1343 --add GROUP BY clause
1344 p_sql := p_sql||' GROUP BY ';
1345 IF (g_proc_level>=g_debug_level) THEN
1346 xtr_risk_debug_pkg.dlog('create_cursor: ' || 'v_aggregate_level',v_aggregate_level);
1347 END IF;
1348 FOR j IN 1..v_aggregate_level LOOP
1349 --xtr_risk_debug_pkg.dlog('v_act_agg(j)',j||':'||v_act_agg(j));
1350 --check whether att. is from table or view
1351 IF p_type(j)<>'C' THEN
1352 IF p_origin(j)='T' THEN
1353 p_sql := p_sql||'dc.'||v_act_agg(j);
1354 ELSIF p_origin(j)='V' THEN
1355 IF v_act_agg(j)='DEAL_SUBTYPE' THEN
1356 p_sql := p_sql||'dst.user_deal_subtype';
1357 ELSE
1358 p_sql := p_sql||'v.'||v_act_agg(j);
1359 END IF;
1360 ELSE --IF p_origin(j)='B'
1361 p_sql := p_sql||'tb.'||v_act_agg(j);
1362 END IF;
1363 ELSE --group by seq_no
1364 p_sql := p_sql||'c.seq_no';
1365 END IF;
1366 --special case where style=X
1367 IF p_style='X' AND p_type(j)='R' AND p_type(j+1)='M' THEN
1368 p_sql := p_sql||',c.seq_no';
1369 END IF;
1370 --put coma if not the end of Group By clause
1371 IF j<v_aggregate_level THEN
1372 p_sql := p_sql||',';
1373 END IF;
1374 END LOOP;
1375
1376 --add ORDER BY clause
1377 p_sql := p_sql||' ORDER BY ';
1378 FOR j IN 1..v_aggregate_level LOOP
1379 p_sql := p_sql||TO_CHAR(j);
1380 IF j<v_aggregate_level THEN
1381 p_sql := p_sql||',';
1382 END IF;
1383 END LOOP;
1384 END IF; --p_style<>'T'
1385
1386 IF (g_proc_level>=g_debug_level) THEN
1387 xtr_risk_debug_pkg.dpop(null,'QRM_PORTFOLIO_ANALYSIS.CREATE_CURSOR');
1388 END IF;
1389 END create_cursor;
1390
1391
1392
1393 /***************************************************************
1394 This function is the main function that does aggregation,
1395 transformation of the data and saving them into the
1396 qrm_saved_analyses_row and qrm_saved_analyses_col tables.
1397 ***************************************************************/
1398 FUNCTION transform_and_save (p_name VARCHAR2,
1399 p_ref_date DATE,
1400 p_caller_flag VARCHAR2)
1401 --'OA' if called from OA
1402 --'CONC' if called from Concurrent Program
1403 RETURN VARCHAR2 IS --'T' if success or 'F' for unsuccessful
1404
1405 v_start_date SYSTEM.QRM_DATE_TABLE := SYSTEM.QRM_DATE_TABLE();
1406 v_end_date SYSTEM.QRM_DATE_TABLE := SYSTEM.QRM_DATE_TABLE();
1407 v_col SYSTEM.QRM_VARCHAR_table;
1408 v_col_seq_no xtr_md_num_table := xtr_md_num_table();
1409 v_col_name_map SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
1410 v_percent_col_name_map SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
1411 v_a1 SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
1412 v_col_hidden SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
1413 v_col_type xtr_md_num_table := xtr_md_num_table();
1414 v_col_seq_no_key SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
1415 v_col_order xtr_md_num_table;
1416 v_col_ccy_multp xtr_md_num_table := xtr_md_num_table();--Bug 2566711
1417
1418 v_header SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
1419 v_measure xtr_md_num_table := xtr_md_num_table();
1420 v_save_header SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
1421 v_save_measure xtr_md_num_table := xtr_md_num_table();
1422 v_nom xtr_md_num_table := xtr_md_num_table();
1423 v_denom xtr_md_num_table := xtr_md_num_table();
1424 v_nom_usd xtr_md_num_table := xtr_md_num_table();
1425 v_denom_usd xtr_md_num_table := xtr_md_num_table();
1426 v_origin SYSTEM.QRM_VARCHAR_table;
1427 v_tb_label_arr SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
1428 v_num_denom_origin SYSTEM.QRM_VARCHAR_TABLE;
1429
1430 v_sql VARCHAR2(4000);
1431 v_sql_col VARCHAR2(4000);
1432 v_max_col_no NUMBER(5):=0; --actual column span length excluding rowheader
1433 v_row_agg_no NUMBER(5); --no of levels of row agg.
1434 --for C style means no of levels of col agg.
1435 --for A style means no of aggregated col.
1436 v_col_agg_no NUMBER(5) := 1;
1437 v_measure_no NUMBER(5) := 0; --no of measure columns in the SQL statement
1438 --no of measure columns+other number type columns in the SQL statement
1439 v_all_meas_no NUMBER(5);
1440
1441 v_date_type VARCHAR2(1);
1442 v_as_of_date DATE;--=v_start_date_fix
1443 v_start_date_ref VARCHAR2(2);
1444 v_start_date_offset NUMBER;
1445 v_start_offset_type VARCHAR2(1);
1446 v_end_date_fix DATE;
1447 v_end_date_ref VARCHAR2(2);
1448 v_end_date_offset NUMBER;
1449 v_end_offset_type VARCHAR2(1);
1450 v_tb_name VARCHAR2(20);
1451 v_tb_label VARCHAR2(1);
1452 v_style VARCHAR2(1);
1453 v_analysis_type VARCHAR2(1);
1454 v_calendar_id NUMBER(15);
1455 v_business_week VARCHAR2(1);
1456 v_currency_source VARCHAR2(1);
1457 v_curr_reporting VARCHAR2(15);
1458 v_md_set_code VARCHAR2(20);
1459 v_sens_exist BOOLEAN := FALSE; --whether the meas. contains sens.meas.
1460 v_sens_flag NUMBER(1);
1461 v_tb_calc_used BOOLEAN := FALSE;
1462 v_tb_calc_used_col BOOLEAN := FALSE;
1463 ----------CCY Variables------------
1464 v_ccy_suffix VARCHAR2(20); --suffix to be appended to att.(i.e. '_USD')
1465 v_ccy_multiplier NUMBER; --the FX rate between USD and other CCY
1466 --Flag for v_ccy_case_flag:
1467 --NULL = never tested
1468 --1 = 1 ccy for the whole analysis
1469 --0 = >1 ccy for the whole analysis(>1 ccy in each col. for style=A)
1470 --2 = >1 ccy for the whole analysis but 1 ccy in each col (style=A)
1471 v_ccy_case_flag NUMBER(1);
1472 --whether ccy/company is part of the agg.,depending on ccy pref(DEAL/SOB)
1473 --Flag for v_ccy_agg_flag:
1474 --NULL = never tested
1475 --1 = CCY (or COMPANY_CODE) is part of the aggregates
1476 --0 = CCY (or COMPANY_CODE) is not part of the aggregates
1477 --2 = CCY (or COMPANY_CODE) is part of row agg
1478 --3 = CCY (or COMPANY_CODE) is part of col agg
1479 v_ccy_agg_flag NUMBER(1);
1480 v_ccy_agg_level NUMBER(5);
1481 v_underlying_ccy VARCHAR2(15);--the ccy in the case of 1 underlying ccy used
1482 v_need_usd_arr BOOLEAN;
1483 v_current_ccy VARCHAR2(15);--the current ccy when CCY is aggregate
1484 v_row_ccy VARCHAR2(15);--the ccy of the last row with v_header(ccy_agg_level)
1485 --dummy variable used for style='T' required for get_underlying_currency
1486 v_table_col_curr SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
1487 v_agg_col_curr SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
1488 ----------End CCY Variables--------
1489 CURSOR analysis_data IS
1490 SELECT analysis_type, style, tb_label, tb_name, date_type, start_date,
1491 start_date_ref, start_date_offset, start_offset_type, end_date,
1492 end_date_ref, end_date_offset, end_offset_type, business_week,
1493 gl_calendar_id,currency_source,curr_reporting,md_set_code
1494 FROM qrm_analysis_settings
1495 WHERE analysis_name=p_name AND history_flag='S';
1496
1497 CURSOR attribute_data IS
1498 SELECT DECODE(a.type,'R',1,'C',2,'M',3,4) seq, a.att_order,
1499 a.attribute_name, a.type, a.total_average, a.percentage, l.numerator,
1500 l.denominator, l.origin, l.amount, l.ccy_aggregate, l.sensitivity,
1501 l.num_denom_origin
1502 FROM qrm_analysis_atts a, qrm_ana_atts_lookups l
1503 WHERE a.attribute_name=l.attribute_name AND a.analysis_name=p_name
1504 AND history_flag='S'
1505 ORDER BY 1,2;
1506
1507 CURSOR count_max_col_no IS
1508 SELECT COUNT(*) FROM qrm_saved_analyses_col
1509 WHERE analysis_name=p_name
1510 AND type>-2;
1511
1512 v_agg SYSTEM.QRM_VARCHAR240_TABLE;
1513 --array of the actual column names including currency conversion
1514 --to be inserted into the DYNAMIC SQL
1515 v_att_type SYSTEM.QRM_VARCHAR_table;
1516 v_nom_fl SYSTEM.QRM_VARCHAR240_TABLE;
1517 v_denom_fl SYSTEM.QRM_VARCHAR240_TABLE;
1518 v_tot_avg SYSTEM.QRM_VARCHAR_table;
1519 v_percent SYSTEM.QRM_VARCHAR_table;
1520 v_dummy1 xtr_md_num_table;
1521 v_amount SYSTEM.QRM_VARCHAR_table;
1522 v_ccy_aggregate SYSTEM.QRM_VARCHAR_table;
1523 v_sensitivity SYSTEM.QRM_VARCHAR_table;
1524
1525 i NUMBER(5);
1526 j NUMBER(5);
1527 k NUMBER(5);
1528 m NUMBER(5);
1529 n NUMBER(5);
1530 v_row NUMBER(5) := 0;
1531 v_new_row BOOLEAN;
1532 v_type NUMBER(5);
1533 v_hidden VARCHAR(1);
1534 v_divisor NUMBER := 0.000001;
1535
1536 --the row agg level which is totaled up to the previous level
1537 v_total_level NUMBER(5);
1538 v_cursor INT;
1539 v_rows_processed NUMBER;
1540 v_success BOOLEAN := TRUE;
1541 v_skip_row BOOLEAN;
1542 -----------
1543 --For col seq no 1
1544 --CO.A NI USD
1545 --L1 L2 L3 --=v_total_level
1546 -----------
1547
1548 -------Testing parameters
1549 dummy NUMBER;
1550 v_test VARCHAR2(255);
1551 -------End Testing parameters
1552
1553 BEGIN
1554 IF (g_proc_level>=g_debug_level) THEN
1555 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.TRANSFORM_AND_SAVE');
1556 END IF;
1557
1558 --initialize fnd_msg_pub if it has not been intialized
1559 IF p_caller_flag='OA' AND fnd_msg_pub.count_msg>0 THEN
1560 fnd_msg_pub.Initialize;
1561 END IF;
1562
1563 --query analysis data
1564 OPEN analysis_data;
1565 FETCH analysis_data INTO v_analysis_type, v_style, v_tb_label, v_tb_name,
1566 v_date_type, v_as_of_date, v_start_date_ref, v_start_date_offset,
1567 v_start_offset_type, v_end_date_fix, v_end_date_ref, v_end_date_offset,
1568 v_end_offset_type, v_business_week, v_calendar_id, v_currency_source,
1569 v_curr_reporting, v_md_set_code;
1570 CLOSE analysis_data;
1571
1572 --check whether analysis settings still there by looking whether the required
1573 --column value return IS NULL or not
1574 IF v_style IS NULL THEN
1575 FND_MESSAGE.SET_NAME('QRM','QRM_ANA_NO_SETTING');
1576 FND_MESSAGE.SET_TOKEN('ANALYSIS_NAME',p_name);
1577 RAISE e_pagg_no_setting_found;
1578 END IF;
1579
1580 --DELETE existing saved data
1581 DELETE qrm_saved_analyses_row WHERE analysis_name=p_name;
1582 IF NOT (v_style='X' AND p_caller_flag='OA') THEN
1583 DELETE qrm_saved_analyses_col WHERE analysis_name=p_name;
1584 END IF;
1585 COMMIT;
1586 --xtr_risk_debug_pkg.dlog('v_analysis_type',v_analysis_type);
1587 --xtr_risk_debug_pkg.dlog('v_style',v_style);
1588 --xtr_risk_debug_pkg.dlog('v_tb_label',v_tb_label);
1589 --xtr_risk_debug_pkg.dlog('v_tb_name',v_tb_name);
1590 --xtr_risk_debug_pkg.dlog('v_date_type',v_date_type);
1591 --xtr_risk_debug_pkg.dlog('v_as_of_date',v_as_of_date);
1592 --xtr_risk_debug_pkg.dlog('v_start_date_ref',v_start_date_ref);
1593 --xtr_risk_debug_pkg.dlog('v_start_date_offset',v_start_date_offset);
1594 --xtr_risk_debug_pkg.dlog('v_start_offset_type',v_start_offset_type);
1595 --xtr_risk_debug_pkg.dlog('v_business_week',v_business_week);
1596 --xtr_risk_debug_pkg.dlog('v_calendar_id',v_calendar_id);
1597 --query attributes data to from SQL statement
1598 OPEN attribute_data;
1599 FETCH attribute_data BULK COLLECT INTO v_dummy1,v_col_order,v_agg,v_att_type,
1600 v_tot_avg,v_percent,v_nom_fl,v_denom_fl,v_origin,v_amount,
1601 v_ccy_aggregate,v_sensitivity,v_num_denom_origin;
1602 CLOSE attribute_data;
1603 --xtr_risk_debug_pkg.dlog('v_agg.COUNT',v_agg.COUNT);
1604 --use some logic to determine what the report underlying currency is
1605 --First, find if there is any AMOUNT type
1606 --bec. amount typemeas. are the only one that can be converted to diff ccy
1607 FOR i IN 1..v_amount.LAST LOOP
1608 IF v_amount(i)='Y' THEN
1609 get_underlying_currency(p_name,p_ref_date,v_style,v_md_set_code,
1610 v_currency_source,v_curr_reporting,
1611 v_amount,v_agg,v_ccy_aggregate,v_att_type,
1612 v_col_order,v_ccy_suffix,
1613 v_ccy_multiplier,v_ccy_agg_flag,v_underlying_ccy,
1614 v_ccy_case_flag,v_agg(i),v_ccy_agg_level,
1615 v_table_col_curr,v_agg_col_curr,
1616 NULL);
1617 IF (g_proc_level>=g_debug_level) THEN
1618 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_ccy_suffix',v_ccy_suffix);
1619 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_ccy_multiplier',v_ccy_multiplier);
1620 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_ccy_case_flag',v_ccy_case_flag);
1621 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_ccy_agg_flag',v_ccy_agg_flag);
1622 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_ccy_agg_level',v_ccy_agg_level);
1623 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'After get_underlying_ccy: v_underlying_ccy',v_underlying_ccy);
1624 END IF;
1625 --see whether need USD array container if Total CCY is diff.
1626 --from CCY used
1627 IF (v_ccy_case_flag=0 AND v_ccy_agg_flag>0) THEN
1628 v_need_usd_arr := TRUE;
1629 ELSE
1630 v_need_usd_arr := FALSE;
1631 END IF;
1632 IF (g_proc_level>=g_debug_level) THEN
1633 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_need_usd_arr',v_need_usd_arr);
1634 END IF;
1635 EXIT;
1636 END IF;
1637 END LOOP;
1638
1639 --Make sure that v_ccy_multiplier is not NULL when needed, otherwise,
1640 --throws exception
1641 IF v_ccy_multiplier IS NULL AND v_curr_reporting<>'USD' AND
1642 ((v_ccy_case_flag=0 AND v_ccy_agg_flag=0) OR v_currency_source='R' OR
1643 (v_ccy_case_flag=0 AND v_ccy_agg_flag IN (1,2) AND v_ccy_agg_level>1))
1644 THEN
1645 FND_MESSAGE.SET_NAME('QRM','QRM_CALC_NO_DEFAULT_SPOT_ERR');
1646 FND_MESSAGE.SET_TOKEN('CCY',v_curr_reporting);
1647 raise e_pagg_no_fxrate_found;
1648 END IF;
1649 --Update currency_used in qrm_analysis_settings
1650 IF v_ccy_case_flag=0 AND v_ccy_agg_flag=0 THEN --use curr_reporting
1651 UPDATE qrm_analysis_settings
1652 SET currency_used='R', last_updated_by=FND_GLOBAL.user_id,
1653 last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
1654 WHERE analysis_name=p_name AND history_flag='S';
1655 ELSE --use whatever in currency_source
1656 UPDATE qrm_analysis_settings
1657 SET currency_used=currency_source, last_updated_by=FND_GLOBAL.user_id,
1658 last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
1659 WHERE analysis_name=p_name AND history_flag='S';
1660 END IF;
1661
1662 IF (g_event_level>=g_debug_level) THEN --bug 3236479
1663 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_ANALYSIS_SETTINGS.CURRENCY_USED',
1664 'QRM_PA_AGGREGATION_P.TRANSFORM_AND_SAVE',g_event_level);
1665 END IF;
1666
1667 --Fix end date to be used later, alrady calculated and saved by FHU engine
1668
1669 --construct the DYNAMIC SQL for the aggregate
1670 create_cursor(p_name,v_style,v_analysis_type,
1671 v_agg,v_att_type,v_nom_fl,v_denom_fl,v_tot_avg,
1672 v_sql,v_sql_col,v_row_agg_no,v_measure_no,v_origin,
1673 v_tb_calc_used,v_tb_calc_used_col,v_need_usd_arr,
1674 v_ccy_suffix,
1675 v_ccy_multiplier,v_amount,v_table_col_curr,v_num_denom_origin,
1676 v_curr_reporting,v_sensitivity);
1677
1678 IF (g_proc_level>=g_debug_level) THEN
1679 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'LENGTH(v_sql)',LENGTH(v_sql));
1680 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_sql',v_sql);
1681 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_sql_col',v_sql_col);
1682 END IF;
1683
1684 IF v_style='X' AND p_caller_flag='OA' THEN
1685 OPEN count_max_col_no;
1686 FETCH count_max_col_no INTO v_max_col_no;
1687 CLOSE count_max_col_no;
1688 --there is no agg for column in qrm_analysis_atts for style=X
1689 v_col_agg_no := 0;
1690 ELSE
1691 --loop over the col. header cursor and populate qrm_saved_analyses_table
1692 v_col_seq_no.EXTEND(v_row_agg_no);
1693 v_col_seq_no_key.EXTEND(v_row_agg_no);
1694 v_col_name_map.EXTEND(v_row_agg_no);
1695 v_percent_col_name_map.EXTEND(v_row_agg_no);
1696 v_a1.EXTEND(v_row_agg_no);
1697 v_col_type.EXTEND(v_row_agg_no);
1698 v_col_hidden.EXTEND(v_row_agg_no);
1699
1700 FOR j IN 1..v_agg.LAST LOOP
1701 --row aggreagate info on the column header
1702 IF v_att_type(j)='R' THEN
1703 v_col_seq_no(j) := j;
1704 --Need to create a LOOP in case of multiple col. level
1705 v_col_seq_no_key(j) := v_agg(j);
1706 v_col_name_map(j) := 'A'||TO_CHAR(j);
1707 v_a1(j) := v_agg(j);
1708 v_col_type(j) := -2; --header type
1709 v_col_hidden(j) := 'N';
1710 --column
1711 ELSIF v_att_type(j)='C' THEN
1712 --include col. agg. values in the column header
1713 --only v_style='C' has att_type='C'
1714 --bulk fetch into PL/SQL temp array
1715 v_sql_col := 'BEGIN '||v_sql_col||' END;';
1716 IF v_tb_calc_used_col THEN
1717 IF v_analysis_type='P' THEN
1718 EXECUTE IMMEDIATE v_sql_col USING OUT v_col,p_name,v_end_date_fix;
1719 ELSE
1720 EXECUTE IMMEDIATE v_sql_col USING OUT v_col,p_name,TRUNC(p_ref_date);
1721 END IF;
1722 ELSE
1723 EXECUTE IMMEDIATE v_sql_col USING OUT v_col,p_name;
1724 END IF;
1725
1726 v_max_col_no := v_col.COUNT;
1727 IF (g_proc_level>=g_debug_level) THEN
1728 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_max_col_no',v_max_col_no);
1729 END IF;
1730 --construct v_seq_no, v_col_name_map, v_hidden, v_type
1731 v_col_seq_no.EXTEND(v_max_col_no);
1732 v_col_seq_no_key.EXTEND(v_max_col_no);
1733 v_col_name_map.EXTEND(v_max_col_no);
1734 v_percent_col_name_map.EXTEND(v_max_col_no);
1735 v_a1.EXTEND(v_max_col_no);
1736 v_col_type.EXTEND(v_max_col_no);
1737 v_col_hidden.EXTEND(v_max_col_no);
1738 FOR i IN v_row_agg_no+1..(v_row_agg_no+v_max_col_no) LOOP
1739 v_col_seq_no(i) := i;
1740 v_col_seq_no_key(i) := v_col(i-v_row_agg_no);
1741 v_col_name_map(i) := 'M'||TO_CHAR(i-v_row_agg_no);
1742 v_percent_col_name_map(i) := 'P'||TO_CHAR(i-v_row_agg_no);
1743 v_a1(i) := v_col(i-v_row_agg_no);
1744 v_col_type(i) := -1; --measure type
1745 v_col_hidden(i) := 'N';
1746 END LOOP;
1747 --add total column at the end
1748
1749 --measure, becomes a column header only in the case of Aggregated Table
1750 ELSE
1751 --must be att. type = 'M' (measure)
1752 IF v_style='X' THEN
1753 --hardcoded date_type=(F)ixed, because qrmpaca*.pls will have
1754 --saved the fixed date by the time this function is called.
1755 update_timebuckets(p_name,p_ref_date,
1756 v_tb_name,v_tb_label,v_as_of_date,
1757 v_start_date_ref,v_start_date_offset,v_start_offset_type,
1758 v_row_agg_no,v_max_col_no,'F',v_calendar_id,
1759 v_business_week,v_col_seq_no,v_col_seq_no_key,
1760 v_col_name_map,v_percent_col_name_map,v_a1,
1761 v_col_type,v_col_hidden,v_start_date,v_end_date,
1762 v_tb_label_arr);
1763 --there is no agg for column in qrm_analysis_atts for style=X
1764 v_col_agg_no := 0;
1765 ELSIF v_style='A' THEN
1766 --if need usd arr then v_measure will include additional '_usd'
1767 --amount columns
1768 IF v_need_usd_arr THEN
1769 v_max_col_no := v_measure_no/4;
1770 ELSE
1771 v_max_col_no := v_measure_no/2;
1772 END IF;
1773
1774 v_col_agg_no := 0;
1775 v_col_seq_no.EXTEND(v_max_col_no);
1776 v_col_seq_no_key.EXTEND(v_max_col_no);
1777 v_col_name_map.EXTEND(v_max_col_no);
1778 v_percent_col_name_map.EXTEND(v_max_col_no);
1779 v_a1.EXTEND(v_max_col_no);
1780 v_col_type.EXTEND(v_max_col_no);
1781 v_col_hidden.EXTEND(v_max_col_no);
1782 v_col_seq_no(j) := j;
1783 v_col_seq_no_key(j) := v_agg(j);
1784 v_col_name_map(j) := 'M'||TO_CHAR(j-v_row_agg_no);
1785 v_percent_col_name_map(j) := 'P'||TO_CHAR(j-v_row_agg_no);
1786 v_a1(j) := v_agg(j);
1787 v_col_type(j) := -1;
1788 v_col_hidden(j) := 'N';
1789 ELSE
1790 --exit path for style C and X
1791 EXIT;
1792 END IF;
1793 END IF;
1794 END LOOP;
1795
1796 ------------Should be removed and done in a separate function
1797 ------------if multiple cil level is implemented
1798 --add total column at the end
1799 v_max_col_no := v_max_col_no+1;
1800 v_col_seq_no.EXTEND;
1801 v_col_seq_no_key.EXTEND;
1802 v_col_type.EXTEND;
1803 v_col_hidden.EXTEND;
1804 v_col_name_map.EXTEND;
1805 v_percent_col_name_map.EXTEND;
1806 v_a1.EXTEND;
1807 IF v_style='X' THEN
1808 v_start_date.EXTEND;
1809 v_end_date.EXTEND;
1810 v_tb_label_arr.EXTEND;
1811 END IF;
1812 j := v_max_col_no+v_row_agg_no;
1813 v_col_seq_no(j) := j;
1814 v_col_seq_no_key(j) := 'TOTAL';
1815 v_col_name_map(j) := 'M'||TO_CHAR(v_max_col_no);
1816 v_percent_col_name_map(j) := 'P'||TO_CHAR(v_max_col_no);
1817 --need to append ccy to total do the ccy logic
1818 v_a1(j) := 'TOTAL';
1819 v_col_type(j) := 1; --total level 1, grand total
1820 v_col_hidden(j) := 'N';
1821 ------------End Should be removed and done in a separate function
1822
1823 -------------Testing purposes
1824 FOR i IN 1..v_row_agg_no+v_max_col_no LOOP
1825 IF (g_proc_level>=g_debug_level) THEN
1826 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'i:seq_no:seq_no_key:a1',i||':'||v_col_seq_no(i)||':'||v_col_seq_no_key(i)||':'||v_a1(i));
1827 END IF;
1828 -- xtr_risk_debug_pkg.dlog('i:type:hidden:col_name_map:percent_col_name_map',i||':'||v_col_type(i)||':'||v_col_hidden(i)||':'||v_col_name_map(i)||':'||v_percent_col_name_map(i)||':');
1829 IF v_style='X' THEN
1830 IF (g_proc_level>=g_debug_level) THEN
1831 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'i:start:end',i||':'||v_start_date(i)||':'||v_end_date(i));
1832 END IF;
1833 END IF;
1834 END LOOP;
1835 -------------End Testing purposes
1836
1837 --bulk insert
1838 IF v_style='X' THEN
1839 FORALL i IN 1..v_max_col_no+v_row_agg_no
1840 INSERT INTO qrm_saved_analyses_col(analysis_name,seq_no,seq_no_key,
1841 type,hidden,col_name_map,a1,percent_col_name_map,start_date,
1842 end_date,tb_label,created_by,creation_date,last_updated_by,
1843 last_update_date,
1844 last_update_login) VALUES(p_name,v_col_seq_no(i),
1845 v_col_seq_no_key(i),v_col_type(i),v_col_hidden(i),
1846 v_col_name_map(i),
1847 v_a1(i),v_percent_col_name_map(i),v_start_date(i),v_end_date(i),
1848 v_tb_label_arr(i),
1849 FND_GLOBAL.user_id,p_ref_date,FND_GLOBAL.user_id,p_ref_date,
1850 FND_GLOBAL.login_id);
1851 ELSE
1852 FORALL i IN 1..v_max_col_no+v_row_agg_no
1853 INSERT INTO qrm_saved_analyses_col(analysis_name,seq_no,seq_no_key,
1854 type,hidden,col_name_map,a1,percent_col_name_map,created_by,
1855 creation_date,last_updated_by,last_update_date,
1856 last_update_login)
1857 VALUES(p_name,v_col_seq_no(i),v_col_seq_no_key(i),v_col_type(i),
1858 v_col_hidden(i),v_col_name_map(i),v_a1(i),
1859 v_percent_col_name_map(i),
1860 FND_GLOBAL.user_id,p_ref_date,FND_GLOBAL.user_id,p_ref_date,
1861 FND_GLOBAL.login_id);
1862 END IF;
1863 COMMIT;
1864
1865 IF (g_event_level>=g_debug_level) THEN --bug 3236479
1866 XTR_RISK_DEBUG_PKG.dlog('DML','Inserted into QRM_SAVED_ANALYSES_COL',
1867 'QRM_PA_AGGREGATION_P.TRANSFORM_AND_SAVE',g_event_level);
1868 END IF;
1869
1870 ----Start Bug 2566711
1871 --update the tot_currency and tot_currency_label columns
1872 IF v_style IN ('C','X') THEN
1873 IF v_ccy_case_flag=0 AND v_ccy_agg_flag=2 THEN
1874 UPDATE qrm_saved_analyses_col
1875 SET tot_currency=v_curr_reporting,
1876 tot_currency_label=' ('||v_curr_reporting||')',
1877 last_updated_by=FND_GLOBAL.user_id,
1878 last_update_date=p_ref_date,
1879 last_update_login=FND_GLOBAL.login_id
1880 WHERE type=1;
1881 ELSIF v_ccy_case_flag=1 THEN
1882 UPDATE qrm_saved_analyses_col
1883 SET tot_currency=v_underlying_ccy,
1884 tot_currency_label=' ('||v_underlying_ccy||')',
1885 last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date,
1886 last_update_login=FND_GLOBAL.login_id
1887 WHERE type=1;
1888 ELSE
1889 UPDATE qrm_saved_analyses_col
1890 SET tot_currency=NULL,
1891 tot_currency_label=NULL,
1892 last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date,
1893 last_update_login=FND_GLOBAL.login_id
1894 WHERE type=1;
1895 END IF;
1896 END IF;
1897 ----End Bug 2566711
1898 COMMIT;
1899
1900 IF (g_event_level>=g_debug_level) THEN --bug 3236479
1901 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SAVED_ANALYSES_COL.TOT_CURRENCY',
1902 'QRM_PA_AGGREGATION_P.TRANSFORM_AND_SAVE',g_event_level);
1903 END IF;
1904
1905 END IF;--for p_caller_flag='OA' and v_style='X'
1906
1907 --prepare the columns currencies multiplier in case each column has
1908 --different currencies. This is necessary to TOTAL columnwise.
1909 IF v_style IN ('C','X') and v_ccy_case_flag=0 and v_ccy_agg_flag=2 THEN
1910 get_col_ccy_multp(v_col_ccy_multp,
1911 v_a1,
1912 v_currency_source,
1913 v_row_agg_no,
1914 v_max_col_no,
1915 v_md_set_code,
1916 p_ref_date);
1917 END IF;
1918
1919 --find no of measure columns+other number type columns in the SQL statement
1920 IF v_style='A' THEN
1921 v_all_meas_no := v_measure_no;
1922 ELSE
1923 --add 1 for the col span seq no
1924 v_all_meas_no := v_measure_no+1;
1925 END IF;
1926
1927 --open dynamic cursor
1928 v_cursor := dbms_sql.open_cursor;
1929 --Parse the results (row: qrm_saved_analyses_row)
1930 dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);
1931 dbms_sql.bind_variable(v_cursor,'analysis_name',p_name);
1932 --remember to add end_date as binding variable
1933 --for special cases
1934 IF (g_proc_level>=g_debug_level) THEN
1935 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_tb_calc_used',v_tb_calc_used);
1936 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_end_date_fix',v_end_date_fix);
1937 END IF;
1938 IF v_style='X' THEN
1939 IF v_analysis_type<>'P' THEN
1940 dbms_sql.bind_variable(v_cursor,'ref_date',TRUNC(p_ref_date));
1941 END IF;
1942 ELSE
1943 IF v_tb_calc_used THEN
1944 IF v_analysis_type='P' THEN
1945 dbms_sql.bind_variable(v_cursor,'end_date',v_end_date_fix);
1946 ELSE
1947 dbms_sql.bind_variable(v_cursor,'ref_date',TRUNC(p_ref_date));
1948 END IF;
1949 END IF;
1950 END IF;
1951
1952 --define columns for row agg
1953 v_header.EXTEND(2*v_row_agg_no); -- the extra storage to be used later
1954 v_measure.EXTEND(v_all_meas_no);
1955 FOR i IN 1..v_row_agg_no LOOP
1956 dbms_sql.define_column(v_cursor,i,v_header(i),20);
1957 END LOOP;
1958 IF (g_proc_level>=g_debug_level) THEN
1959 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_measure_no',v_measure_no);
1960 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_all_meas_no',v_all_meas_no||':'||v_measure.COUNT);
1961 END IF;
1962 FOR i IN 1..v_all_meas_no LOOP
1963 dbms_sql.define_column(v_cursor,i+v_row_agg_no,v_measure(i));
1964 END LOOP;
1965
1966 --execute
1967 v_rows_processed := dbms_sql.execute(v_cursor);
1968 --xtr_risk_debug_pkg.dlog('v_cursor = ',v_cursor);
1969 --xtr_risk_debug_pkg.dlog('v_rows_processed = ',v_rows_processed);
1970
1971 --calculate multiplier to be used in the total array for style=A
1972 IF v_need_usd_arr THEN -- need to consider USD array then
1973 m := 4;
1974 n := m-2;
1975 ELSE
1976 m := 2;
1977 n := m-2;
1978 END IF;
1979
1980 --creating/preparing the temporary storage
1981 v_save_header.EXTEND(v_row_agg_no);
1982
1983 v_save_measure.EXTEND(v_max_col_no);
1984 v_nom.EXTEND(v_row_agg_no*v_max_col_no);
1985 v_denom.EXTEND(v_row_agg_no*v_max_col_no);
1986 IF v_need_usd_arr THEN
1987 v_nom_usd.EXTEND(v_row_agg_no*v_max_col_no);
1988 v_denom_usd.EXTEND(v_row_agg_no*v_max_col_no);
1989 END IF;
1990
1991
1992 --loop through the fetched rows
1993 LOOP
1994 --fetch a row
1995 IF dbms_sql.fetch_rows(v_cursor)>0 THEN
1996 --fetch header/VARCHAR type
1997 FOR i IN 1..v_row_agg_no LOOP
1998 dbms_sql.column_value(v_cursor,i,v_header(i));
1999 --xtr_risk_debug_pkg.dlog('v_header(i)',i||':'||v_header(i));
2000 END LOOP;
2001
2002 --first reset the indicator
2003 v_skip_row := FALSE;
2004 --skip the row if the at least one of the header aggregate att IS NULL
2005 FOR i IN 1..v_row_agg_no LOOP
2006 IF v_header(i) IS NULL THEN
2007 v_skip_row := TRUE;
2008 END IF;
2009 END LOOP;
2010
2011 IF NOT v_skip_row THEN
2012 --fetch measure and other NUMBER type
2013 FOR i IN 1..v_all_meas_no LOOP
2014 dbms_sql.column_value(v_cursor,i+v_row_agg_no,v_measure(i));
2015 END LOOP;
2016
2017 --different treatment for Cross Tab and Aggregated Table
2018 IF v_style='A' THEN --no need for pivoting for style=A
2019 --check whether need to insert total
2020 v_total_level := 0;
2021 IF v_row<>0 THEN
2022 FOR i IN 1..v_row_agg_no-1 LOOP
2023 IF v_save_header(i)<>v_header(i) THEN
2024 v_total_level := i+1;
2025 EXIT;
2026 END IF;
2027 END LOOP;
2028 --INSERT the old values: v_save_header and v_save_measure
2029 v_type := -1;
2030 v_hidden := 'N';
2031 v_success := insert_row(v_save_header,v_save_measure,
2032 v_row_agg_no,v_max_col_no,
2033 p_name,v_row,v_type,v_hidden,NULL,
2034 v_style,p_ref_date,v_ccy_case_flag,
2035 v_ccy_agg_flag,v_col_ccy_multp);
2036
2037 --delete the previous array content in case the new column
2038 --is null
2039 FOR i IN 1..v_row_agg_no LOOP
2040 v_save_header(i) := NULL;
2041 END LOOP;
2042 FOR i IN 1..v_max_col_no LOOP
2043 v_save_measure(i) := NULL;
2044 END LOOP;
2045 END IF;
2046
2047 --if v_total_level>0 then calc total and insert
2048 ------------------Calculating the Total Row------------------------
2049 --see if we need to insert total in between
2050
2051 IF v_total_level>0 THEN
2052 FOR i IN REVERSE v_total_level..v_row_agg_no LOOP
2053 v_row := v_row+1;
2054 v_save_header(i) := 'TOTAL';
2055 -- loop through the measures total
2056 FOR j IN 1..v_max_col_no LOOP
2057 k := (i-1)*v_max_col_no+j;
2058 --determine whether to fetch USD arr or reg. arr
2059 IF v_need_usd_arr AND i<=v_ccy_agg_level THEN
2060 IF v_denom_usd(k)=0 THEN
2061 v_save_measure(j):=v_nom_usd(k)/v_divisor;
2062 ELSE
2063 v_save_measure(j):=v_nom_usd(k)/v_denom_usd(k);
2064 END IF;
2065 ELSE
2066 IF v_denom(k)=0 THEN
2067 v_save_measure(j):=v_nom(k)/v_divisor;
2068 ELSE
2069 v_save_measure(j):=v_nom(k)/v_denom(k);
2070 END IF;
2071 END IF;
2072 v_nom(k) := NULL;
2073 v_denom(k) := NULL;
2074 IF v_need_usd_arr THEN
2075 v_nom_usd(k) := NULL;
2076 v_denom_usd(k) := NULL;
2077 END IF;
2078 END LOOP;
2082 --determine the CCY to be appended after TOTAL
2079 --INSERT the old values: v_save_header and v_save_measure
2080 v_type := i;
2081 v_hidden := 'Y';
2083 --does not need to worry for the CCY agg logic
2084 --(tot. level),
2085 --bec. it's captured in v_current_ccy
2086 --Make sure that ccy is appended to Total that has
2087 --uniform currency across the columns
2088 IF v_need_usd_arr THEN
2089 IF v_ccy_agg_flag=2 THEN
2090 v_current_ccy := NULL;--cannot display CCY
2091 ELSE --check whether before/after the ccy_agg_level
2092 IF i>=v_ccy_agg_level THEN
2093 v_current_ccy := v_row_ccy;
2094 ELSE --v_current_ccy=v_header(ccy_agg_level)
2095 v_current_ccy := v_curr_reporting;
2096 END IF;
2097 --ELSE keep the v_current_ccy
2098 END IF;
2099 ELSIF v_ccy_case_flag=2 THEN
2100 v_current_ccy := NULL;--cannot display CCY
2101 ELSE
2102 v_current_ccy := v_underlying_ccy;
2103 END IF;
2104 v_success := insert_row(v_save_header,v_save_measure,
2105 v_row_agg_no,v_max_col_no,
2106 p_name,v_row,v_type,v_hidden,
2107 v_current_ccy,
2108 v_style,p_ref_date,v_ccy_case_flag,
2109 v_ccy_agg_flag,v_col_ccy_multp);
2110 v_current_ccy := NULL; --reset v_current_ccy
2111 --delete the previous array content
2112 FOR i IN 1..v_row_agg_no LOOP
2113 v_save_header(i) := NULL;
2114 END LOOP;
2115 FOR i IN 1..v_max_col_no LOOP
2116 v_save_measure(i) := NULL;
2117 END LOOP;
2118 END LOOP;
2119 END IF;
2120 ------------------End Calculating the Total Row--------------------
2121
2122 v_row := v_row+1;
2123 --copy to v_save_measure and v_save_header assign to nom and
2124 --denom
2125 FOR i IN 1..v_row_agg_no LOOP
2126 v_save_header(i):=v_header(i);
2127 --determine the current row ccy
2128 IF v_ccy_agg_level=i AND v_need_usd_arr THEN
2129 v_row_ccy := v_header(i);
2130 END IF;
2131 END LOOP;
2132 --for style=A v_measure_no=2*(v_max_col_no-1)
2133 --1 is for the total at the column end
2134 --'-1' in 'v_max_col_no-1' is to compensate for the End Col TOTAL
2135 FOR k IN 1..v_max_col_no-1 LOOP
2136 IF v_tot_avg(v_row_agg_no+k)='T' THEN
2137 v_save_measure(k):=v_measure(k*m-n-1);
2138 FOR i IN 1..v_row_agg_no LOOP
2139 --determining the storage location for a specific
2140 --level total
2141 j := (i-1)*v_max_col_no+k;
2142 IF v_nom(j) IS NOT NULL THEN
2143 IF v_measure(k*m-n-1) IS NOT NULL THEN
2144 v_nom(j):=v_nom(j)+v_measure(k*m-n-1);
2145 END IF;
2146 ELSE
2147 v_nom(j):=v_measure(k*m-n-1);
2148 END IF;
2149 v_denom(j):=1;
2150 --determining the storage location for a USD specific
2151 --level total
2152 IF v_need_usd_arr THEN
2153 IF v_nom_usd(j) IS NOT NULL THEN
2154 IF v_measure(k*m-1) IS NOT NULL THEN
2155 v_nom_usd(j):=v_nom_usd(j)+v_measure(k*m-1);
2156 END IF;
2157 ELSE
2158 v_nom_usd(j):=v_measure(k*m-1);
2159 END IF;
2160 v_denom_usd(j):=1;
2161 END IF;
2162 END LOOP;
2163 --Weighted Average operation
2164 ELSE --insert to side array to be totaled later
2165 IF v_measure(k*m-n)=0 THEN
2166 v_save_measure(k):=v_measure(k*m-n-1)/v_divisor;
2167 ELSE
2168 v_save_measure(k):=v_measure(k*m-n-1)/v_measure(k*m-n);
2169 END IF;
2170 FOR i IN 1..v_row_agg_no LOOP
2171 --determining the storage location for a specific
2172 --level total
2173 j := (i-1)*v_max_col_no+k;
2174 IF v_nom(j) IS NOT NULL THEN
2175 IF v_measure(k*m-n-1) IS NOT NULL THEN
2176 v_nom(j):=v_nom(j)+v_measure(k*m-n-1);
2177 v_denom(j) := v_denom(j)+v_measure(k*m-n);
2178 END IF;
2179 ELSE
2180 v_nom(j):=v_measure(k*m-n-1);
2181 v_denom(j) := v_measure(k*m-n);
2182 END IF;
2183 --determining the storage location for a USD specific
2184 --level total
2185 IF v_need_usd_arr THEN
2186 IF v_nom(j) IS NOT NULL THEN
2187 IF v_measure(k*m-1) IS NOT NULL THEN
2188 v_nom_usd(j):=v_nom_usd(j)+v_measure(k*m-1);
2189 v_denom_usd(j) := v_denom_usd(j)+v_measure(k*m);
2190 END IF;
2191 ELSE
2192 v_nom_usd(j):=v_measure(k*m-1);
2193 v_denom_usd(j) := v_measure(k*m);
2194 END IF;
2195 END IF;
2196 END LOOP;
2197 END IF;
2198 END LOOP;
2199
2200 ELSE --pivoting for style='C','X'
2201 --save the previous row header info in v_header(2*v_row+i)
2202 --insert to temp array all the output
2203 --nontotal case
2204 v_new_row := FALSE;
2205 v_total_level := 0;
2206 --check whether it's a new row
2207 FOR i IN 1..v_row_agg_no LOOP
2208 IF v_row=0 OR v_save_header(i)<>v_header(i) THEN
2209 v_new_row := TRUE;
2210 --exclude condition of the first row fetched
2211 IF v_row<>0 AND i<>v_row_agg_no THEN
2212 v_total_level := i+1;
2213 END IF;
2214 EXIT;
2215 END IF;
2216 END LOOP;
2217 --new row,therefore insert row header
2218 IF v_new_row THEN
2219 IF v_row<>0 THEN
2220 --INSERT the old values: v_save_header and v_save_measure
2221 v_type := -1;
2222 v_hidden := 'N';
2223 v_success := insert_row(v_save_header,v_save_measure,
2224 v_row_agg_no,v_max_col_no,
2225 p_name,v_row,v_type,v_hidden,NULL,
2226 v_style,p_ref_date,v_ccy_case_flag,
2227 v_ccy_agg_flag,v_col_ccy_multp);
2228 --delete the previous array content
2229 FOR i IN 1..v_row_agg_no LOOP
2230 v_save_header(i) := NULL;
2231 END LOOP;
2232 FOR i IN 1..v_max_col_no LOOP
2233 v_save_measure(i) := NULL;
2234 END LOOP;
2235 END IF;
2236
2237 ------------------Calculating the Total Row------------------------
2238 --see if we need to insert total in between
2239 IF v_total_level>0 THEN
2240 FOR i IN REVERSE v_total_level..v_row_agg_no LOOP
2241 v_row := v_row+1;
2242 v_save_header(i) := 'TOTAL';
2243 -- loop through the measures total
2244 FOR j IN 1..v_max_col_no LOOP
2245 --determine whether to fetch USD arr or reg. arr
2246 IF v_need_usd_arr AND i<=v_ccy_agg_level
2247 AND v_ccy_agg_flag<>2 THEN --bug 2566711
2248 IF v_denom_usd((i-1)*v_max_col_no+j)=0 THEN
2249 v_save_measure(j):=v_nom_usd((i-1)*v_max_col_no+j)/v_divisor;
2250 ELSE
2251 v_save_measure(j):=v_nom_usd((i-1)*v_max_col_no+j)/v_denom_usd((i-1)*v_max_col_no+j);
2252 END IF;
2253 ELSE
2254 IF v_denom((i-1)*v_max_col_no+j)=0 THEN
2255 v_save_measure(j):=v_nom((i-1)*v_max_col_no+j)/v_divisor;
2256 ELSE
2257 v_save_measure(j):=v_nom((i-1)*v_max_col_no+j)/v_denom((i-1)*v_max_col_no+j);
2258 END IF;
2259 END IF;
2260 v_nom((i-1)*v_max_col_no+j) := NULL;
2261 v_denom((i-1)*v_max_col_no+j) := NULL;
2262 IF v_need_usd_arr THEN
2263 v_nom_usd((i-1)*v_max_col_no+j) := NULL;
2264 v_denom_usd((i-1)*v_max_col_no+j) := NULL;
2265 END IF;
2266 END LOOP;
2267
2268 --INSERT the old values: v_save_header and
2269 --v_save_measure
2270 v_type := i;
2271 v_hidden := 'Y';
2272 --determine the CCY to be appended after TOTAL
2273 --does not need to worry for the CCY agg logic
2274 --(tot. level),
2275 --bec. it's captured in v_current_ccy
2276 --Make sure that ccy is appended to Total that has
2277 --uniform currency across the columns
2278 IF v_need_usd_arr THEN
2279 IF v_ccy_agg_flag=2 THEN
2280 v_current_ccy := NULL;--cannot display CCY
2281 ELSE --check whether before/after the ccy_agg_level
2282 IF i> v_ccy_agg_level THEN -- Bug 4533431
2283 v_current_ccy := v_row_ccy;
2284 ELSE --v_current_ccy=v_header(ccy_agg_level)
2285 v_current_ccy := v_curr_reporting;
2286 END IF;
2287 END IF;
2288 ELSIF v_ccy_case_flag=2 THEN
2289 v_current_ccy := NULL;--cannot display CCY
2290 ELSE
2291 v_current_ccy := v_underlying_ccy;
2292 END IF;
2293 v_success := insert_row(v_save_header,v_save_measure,
2294 v_row_agg_no,v_max_col_no,
2295 p_name,v_row,v_type,v_hidden,
2296 v_current_ccy,
2297 v_style,p_ref_date,v_ccy_case_flag,
2298 v_ccy_agg_flag,v_col_ccy_multp);
2299 v_current_ccy := NULL; --reset
2300 --delete the previous array content
2301 FOR i IN 1..v_row_agg_no LOOP
2302 v_save_header(i) := NULL;
2303 END LOOP;
2304 FOR i IN 1..v_max_col_no LOOP
2305 v_save_measure(i) := NULL;
2306 END LOOP;
2307 END LOOP;
2308 END IF;
2309 ------------------End Calculating the Total Row--------------------
2310
2311 --insert header increase row count
2312 v_row := v_row+1;
2313 --do not include the column aggregate in the row header
2314 FOR i IN 1..v_row_agg_no LOOP
2315 v_save_header(i):=v_header(i);
2316 --determine the current row ccy
2317 IF v_ccy_agg_level=i AND v_need_usd_arr THEN
2318 v_row_ccy := v_header(i);
2319 END IF;
2320 END LOOP;
2321 END IF;
2322 --insert measure
2323 --v_measure(1) has the Y Axis coordinate
2324 --Total operation
2325 IF v_tot_avg(v_row_agg_no+v_col_agg_no+1)='T' THEN
2326 v_save_measure(v_measure(1)-v_row_agg_no):=v_measure(2);
2327 FOR i IN 1..v_row_agg_no LOOP
2328 --determining the storage location for a specific level
2329 --total
2330 j:=(i-1)*v_max_col_no+(v_measure(1)-v_row_agg_no);
2331 IF v_nom(j) IS NOT NULL THEN
2332 IF v_measure(2) IS NOT NULL THEN
2333 v_nom(j):=v_nom(j)+v_measure(2);
2334 END IF;
2335 ELSE
2336 v_nom(j):=v_measure(2);
2337 END IF;
2338 v_denom(j):=1;
2339 --determining the storage location for USD specific level
2340 --total
2341 IF v_need_usd_arr THEN
2342 IF v_nom_usd(j) IS NOT NULL THEN
2343 IF v_measure(4) IS NOT NULL THEN
2344 v_nom_usd(j):=v_nom_usd(j)+v_measure(4);
2345 END IF;
2346 ELSE
2347 v_nom_usd(j):=v_measure(4);
2348 END IF;
2349 v_denom_usd(j):=1;
2350 END IF;
2351 END LOOP;
2352 --Weighted Average operation
2353 ELSE --insert to side array to be totaled later
2354 IF v_measure(3)=0 THEN
2355 v_save_measure(v_measure(1)-v_row_agg_no):=v_measure(2)/v_divisor;
2356 ELSE
2357 v_save_measure(v_measure(1)-v_row_agg_no):=v_measure(2)/v_measure(3);
2358 END IF;
2359 FOR i IN 1..v_row_agg_no LOOP
2360 --determining the storage location for a specific level
2361 --total
2362 j:=(i-1)*v_max_col_no+(v_measure(1)-v_row_agg_no);
2363 IF v_nom(j) IS NOT NULL THEN
2364 IF v_measure(2) IS NOT NULL THEN
2365 v_nom(j) := v_nom(j)+v_measure(2);
2366 v_denom(j) := v_denom(j)+v_measure(3);
2367 END IF;
2368 ELSE
2369 v_nom(j) := v_measure(2);
2370 v_denom(j) := v_measure(3);
2371 END IF;
2372 --determining the storage location for USD specific level
2373 --total
2374 IF v_need_usd_arr THEN
2375 IF v_nom_usd(j) IS NOT NULL THEN
2376 IF v_measure(4) IS NOT NULL THEN
2377 v_nom_usd(j) := v_nom_usd(j)+v_measure(4);
2378 v_denom_usd(j) := v_denom_usd(j)+v_measure(5);
2379 END IF;
2380 ELSE
2384 END IF;
2381 v_nom_usd(j) := v_measure(4);
2382 v_denom_usd(j) := v_measure(5);
2383 END IF;
2385 END LOOP;
2386 END IF;
2387 END IF; --style
2388 END IF; --v_skip_row
2389
2390 ELSE
2391 --before exit:insert last row and do grand total at the bottom
2392 IF v_row<>0 THEN
2393 --INSERT the old values: v_save_header and v_save_measure
2394 v_type := -1;
2395 v_hidden := 'N';
2396 v_success := insert_row(v_save_header,v_save_measure,
2397 v_row_agg_no,v_max_col_no,
2398 p_name,v_row,v_type,v_hidden,NULL,
2399 v_style,p_ref_date,v_ccy_case_flag,
2400 v_ccy_agg_flag,v_col_ccy_multp);
2401
2402 --start from 1,the grand total
2403 v_total_level := 1;
2404 FOR i IN REVERSE v_total_level..v_row_agg_no LOOP
2405 --delete the previous array content
2406 FOR i IN 1..v_row_agg_no LOOP
2407 v_save_header(i) := NULL;
2408 END LOOP;
2409 FOR i IN 1..v_max_col_no LOOP
2410 v_save_measure(i) := NULL;
2411 END LOOP;
2412 v_row := v_row+1;
2413 v_save_header(i) := 'TOTAL';
2414 -- loop through the measures total
2415 FOR j IN 1..v_max_col_no LOOP
2416 --determine whether to fetch USD arr or reg. arr
2417 IF v_need_usd_arr AND i<=v_ccy_agg_level
2418 AND v_ccy_agg_flag<>2 THEN --bug 2566711
2419 IF v_denom_usd((i-1)*v_max_col_no+j)=0 THEN
2420 v_save_measure(j):=v_nom_usd((i-1)*v_max_col_no+j)/v_divisor;
2421 ELSE
2422 v_save_measure(j):=v_nom_usd((i-1)*v_max_col_no+j)/v_denom_usd((i-1)*v_max_col_no+j);
2423 END IF;
2424 ELSE
2425 IF v_denom((i-1)*v_max_col_no+j)=0 THEN
2426 v_save_measure(j):=v_nom((i-1)*v_max_col_no+j)/v_divisor;
2427 ELSE
2428 v_save_measure(j):=v_nom((i-1)*v_max_col_no+j)/v_denom((i-1)*v_max_col_no+j);
2429 END IF;
2430 END IF;
2431 v_nom((i-1)*v_max_col_no+j) := NULL;
2432 v_denom((i-1)*v_max_col_no+j) := NULL;
2433 IF v_need_usd_arr THEN
2434 v_nom_usd((i-1)*v_max_col_no+j) := NULL;
2435 v_denom_usd((i-1)*v_max_col_no+j) := NULL;
2436 END IF;
2437 END LOOP;
2438 --INSERT total
2439 v_type := i;
2440 v_hidden := 'Y';
2441 --determine the CCY to be appended after TOTAL
2442 --does not need to worry for the CCY agg logic (tot. level),
2443 --bec. it's captured in v_current_ccy
2444 IF v_need_usd_arr THEN
2445 IF v_ccy_agg_flag=2 THEN
2446 v_current_ccy := NULL;--cannot display CCY
2447 ELSE --check whether before/after the ccy_agg_level
2448 IF i > v_ccy_agg_level THEN --Bug 4533431
2449 v_current_ccy := v_row_ccy;
2450 ELSE --v_current_ccy=v_header(ccy_agg_level)
2451 v_current_ccy := v_curr_reporting;
2452 END IF;
2453 END IF;
2454 ELSIF v_ccy_case_flag=2 THEN
2455 v_current_ccy := NULL;--cannot display CCY
2456 ELSE
2457 v_current_ccy := v_underlying_ccy;
2458 END IF;
2459 v_success := insert_row(v_save_header,v_save_measure,
2460 v_row_agg_no,v_max_col_no,
2461 p_name,v_row,v_type,v_hidden,
2462 v_current_ccy,
2463 v_style,p_ref_date,v_ccy_case_flag,
2464 v_ccy_agg_flag,v_col_ccy_multp);
2465 END LOOP;
2466 END IF;
2467 EXIT;
2468 END IF;
2469 END LOOP;
2470
2471 COMMIT;
2472 dbms_sql.close_cursor(v_cursor);
2473
2474 --update total
2475 v_success := update_total(p_name,p_ref_date);
2476 IF (g_proc_level>=g_debug_level) THEN
2477 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_successT',v_success);
2478 END IF;
2479 IF NOT v_success THEN
2480 RAISE e_pagg_update_total_fail;
2481 END IF;
2482 --find out NOCOPY currency for each saved cells in the table
2483 v_success := update_aggregate_curr(p_name,p_ref_date,v_ccy_case_flag,
2484 v_ccy_agg_flag,v_ccy_agg_level,v_row_agg_no,v_max_col_no,
2485 v_underlying_ccy,v_currency_source,v_curr_reporting,v_agg_col_curr);
2486
2487 IF (g_proc_level>=g_debug_level) THEN
2488 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_successCurr',v_success);
2489 END IF;
2490 IF NOT v_success THEN
2491 RAISE e_pagg_update_agg_curr_fail;
2492 END IF;
2493 --update percent
2494 v_success := update_percent (p_name,v_style,v_row_agg_no,v_max_col_no,
2495 p_ref_date,v_md_set_code);
2496 IF (g_proc_level>=g_debug_level) THEN
2497 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_success%',v_success);
2498 END IF;
2499 IF NOT v_success THEN
2500 RAISE e_pagg_update_percent_fail;
2501 END IF;
2502 IF v_style='C' THEN
2503 v_success := update_label(p_name,v_agg,v_col_order,v_att_type,p_ref_date);
2504 IF (g_proc_level>=g_debug_level) THEN
2505 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_successL',v_success);
2506 END IF;
2510 END IF;
2507 IF NOT v_success THEN
2508 RAISE e_pagg_update_label_fail;
2509 END IF;
2511 --update timebuckets label
2512 IF v_style='X' AND p_caller_flag='OA' THEN
2513 v_success := update_timebuckets_label(p_name);
2514 IF (g_proc_level>=g_debug_level) THEN
2515 xtr_risk_debug_pkg.dlog('transform_and_save: ' || 'v_successTBLabel',v_success);
2516 END IF;
2517 IF NOT v_success THEN
2518 RAISE e_pagg_update_tb_label_fail;
2519 END IF;
2520 END IF;
2521
2522 --update dirty column if called from 'OA'
2523 IF p_caller_flag='OA' THEN
2524 UPDATE qrm_analysis_settings SET dirty='N' WHERE analysis_name=p_name;
2525
2526 IF (g_event_level>=g_debug_level) THEN --bug 3236479
2527 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_ANALYSIS_SETTINGS.DIRTY=N',
2528 'QRM_PA_AGGREGATION_P.TRANSFORM_AND_SAVE',g_event_level);
2529 END IF;
2530
2531 END IF;
2532
2533 COMMIT;
2534 IF (g_proc_level>=g_debug_level) THEN
2535 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.TRANSFORM_AND_SAVE');
2536 END IF;
2537 RETURN 'T';
2538
2539 EXCEPTION
2540 WHEN e_pagg_no_fxrate_found THEN
2541 IF p_caller_flag='CONC' THEN
2542 RAISE e_pagg_no_fxrate_found;
2543 ELSE
2544 fnd_msg_pub.add;
2545 END IF;
2546 IF (g_error_level>=g_debug_level) THEN
2547 xtr_risk_debug_pkg.dlog('EXCEPTION','e_pagg_no_fxrate_found','QRM_PA_AGGREGATION_P.TRANSFORM_AND_SAVE',g_error_level);--bug 3236479
2548 --xtr_risk_debug_pkg.dpop('transform_and_save: ' || 'QRM_PA_AGGREGATION_P.T_AND_S');
2549 END IF;
2550 RETURN 'F';
2551 WHEN e_pagg_no_timebuckets_found THEN
2552 IF p_caller_flag='CONC' THEN
2553 RAISE e_pagg_no_timebuckets_found;
2554 ELSE
2555 fnd_msg_pub.add;
2556 END IF;
2557 IF (g_error_level>=g_debug_level) THEN
2558 xtr_risk_debug_pkg.dlog('EXCEPTION','e_pagg_no_timebuckets_found','QRM_PA_AGGREGATION_P.TRANSFORM_AND_SAVE',g_error_level);--bug 3236479
2559 --xtr_risk_debug_pkg.dpop('transform_and_save: ' || 'QRM_PA_AGGREGATION_P.T_AND_S');
2560 END IF;
2561 RETURN 'F';
2562 WHEN e_pagg_no_setting_found THEN
2563 IF p_caller_flag='CONC' THEN
2564 RAISE e_pagg_no_setting_found;
2565 ELSE
2566 fnd_msg_pub.add;
2567 END IF;
2568 IF (g_error_level>=g_debug_level) THEN
2569 xtr_risk_debug_pkg.dlog('EXCEPTION','e_pagg_no_setting_found','QRM_PA_AGGREGATION_P.TRANSFORM_AND_SAVE',g_error_level);--bug 3236479
2570 --xtr_risk_debug_pkg.dpop('transform_and_save: ' || 'QRM_PA_AGGREGATION_P.T_AND_S');
2571 END IF;
2572 RETURN 'F';
2573 WHEN OTHERS THEN
2574 IF p_caller_flag='OA' THEN
2575 FND_MESSAGE.SET_NAME('QRM','QRM_ANA_UNEXPECTED_ERROR');
2576 fnd_msg_pub.add;
2577 END IF;
2578 IF (g_error_level>=g_debug_level) THEN
2579 xtr_risk_debug_pkg.dlog('EXCEPTION','others','QRM_PA_AGGREGATION_P.TRANSFORM_AND_SAVE',g_error_level);--bug 3236479
2580 --xtr_risk_debug_pkg.dpop('transform_and_save: ' || 'QRM_PA_AGGREGATION_P.T_AND_S');
2581 END IF;
2582 RETURN 'F';
2583 END transform_and_save;
2584
2585
2586
2587 /***************************************************************
2588 This function check whether a particular date is a holiday or not
2589 in GL Calendar and if so, find the next/previous business day.
2590
2591 If anything goes wrong during the operation, the function will
2592 return the date passed in as the argument.
2593 ***************************************************************/
2594 -- from GL Calendar
2595 FUNCTION find_gl_business_days(p_indicator VARCHAR2,
2596 --'F'=forward next bus.days,'B'=backward next bus.days
2597 p_date_in DATE,
2598 p_calendar_id NUMBER)
2599 RETURN DATE IS
2600 v_date_out DATE;
2601 v_move VARCHAR2(1); --'B' for backward,'F' for forward
2602 v_day VARCHAR2(3);
2603 v_week SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
2604 v_day_no NUMBER;
2605 i NUMBER(1);
2606 j NUMBER(1);
2607 v_found BOOLEAN := FALSE;
2608 BEGIN
2609
2610 IF (g_proc_level>=g_debug_level) THEN --bug 3236479
2611 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.FIND_GL_BUSINESS_DAY');
2612 END IF;
2613
2614 IF p_calendar_id IS NULL THEN
2615 v_date_out := p_date_in;
2616 ELSE
2617 IF p_indicator='F' THEN --find the next bus. day forward
2618 SELECT MIN(transaction_date)
2619 INTO v_date_out
2620 FROM gl_transaction_dates
2621 WHERE transaction_calendar_id = p_calendar_id
2622 AND TRUNC(transaction_date) >= TRUNC(p_date_in)
2623 AND business_day_flag = 'Y';
2624 ELSIF p_indicator='B' THEN --find the next bus. day backward
2625 SELECT MAX(transaction_date)
2626 INTO v_date_out
2627 FROM gl_transaction_dates
2628 WHERE transaction_calendar_id = p_calendar_id
2629 AND TRUNC(transaction_date) <= TRUNC(p_date_in)
2630 AND business_day_flag = 'Y';
2631 ELSE
2632 RAISE_APPLICATION_ERROR
2633 (-20001,'Invalid Interval Type of Time-Buckets');
2634 END IF;
2635 END IF;
2636
2637 IF (g_proc_level>=g_debug_level) THEN --bug 3236479
2638 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.FIND_GL_BUSINESS_DAY');
2639 END IF;
2640
2641 RETURN v_date_out;
2642 EXCEPTION
2643 WHEN OTHERS THEN
2644
2645 IF (g_error_level>=g_debug_level) THEN --bug 3236479
2646 xtr_risk_debug_pkg.dlog('EXCEPTION','OTHERS','QRM_PA_AGGREGATION_P.FIND_GL_BUSINESS_DAY',g_error_level);
2647 END IF;
2648
2649 RETURN p_date_in;
2650 END find_gl_business_days;
2651
2652
2653
2654 /********************************************************************
2655 get_beg_end_of_week
2656 Procedure that provides the beginning and end of the week given a
2657 reference date, based on a GL Transaction Calendar. In the case
2658 where there are either no or all business days, it defines the
2659 first of the week by following NLS_TERRITORY settings.
2660
2661 This procedure attempts to determine the beginning of the week by
2662 looking for the first business day following a "block" of
2663 non-business days.
2664
2665 In the case where there are multiple "blocks" of non-business days
2666 it will be based on the "block" that ends closest to (but on or
2667 after) Friday.
2668
2669 p_gl_trans_calendar_id - GL Transacation Calendar ID
2670 p_ref_date - Reference date
2671 p_beg_date - Beginning of the week
2672 p_end_date - End of the week
2673 ********************************************************************/
2674 PROCEDURE get_beg_end_of_week(p_gl_trans_calendar_id IN NUMBER,
2675 p_ref_date IN DATE,
2676 p_beg_date OUT NOCOPY DATE,
2677 p_end_date OUT NOCOPY DATE) IS
2678 --
2679 -- Be very careful when modifying this. The code runs on the
2680 -- the assumption that Friday comes first.
2681 CURSOR days_of_week_cursor IS
2682 SELECT
2683 fri_business_day_flag,
2684 sat_business_day_flag,
2685 sun_business_day_flag,
2686 mon_business_day_flag,
2687 tue_business_day_flag,
2688 wed_business_day_flag,
2689 thu_business_day_flag
2690 FROM gl_transaction_calendar
2691 WHERE transaction_calendar_id = p_gl_trans_calendar_id;
2692 --
2693 v_day_table SYSTEM.QRM_VARCHAR_TABLE;
2694 v_fri GL_TRANSACTION_CALENDAR.SAT_BUSINESS_DAY_FLAG%TYPE;
2695 v_sat GL_TRANSACTION_CALENDAR.SAT_BUSINESS_DAY_FLAG%TYPE;
2696 v_sun GL_TRANSACTION_CALENDAR.SAT_BUSINESS_DAY_FLAG%TYPE;
2697 v_mon GL_TRANSACTION_CALENDAR.SAT_BUSINESS_DAY_FLAG%TYPE;
2698 v_tue GL_TRANSACTION_CALENDAR.SAT_BUSINESS_DAY_FLAG%TYPE;
2699 v_wed GL_TRANSACTION_CALENDAR.SAT_BUSINESS_DAY_FLAG%TYPE;
2700 v_thu GL_TRANSACTION_CALENDAR.SAT_BUSINESS_DAY_FLAG%TYPE;
2701 --
2702 v_last_nonbus_day NUMBER := 0;
2703 v_nonbus_count NUMBER := 0;
2704 v_ref_day NUMBER;
2705 v_days_before NUMBER;
2706 v_offset NUMBER;
2707 --
2708 BEGIN
2709 IF (g_proc_level>=g_debug_level) THEN
2710 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.get_beg_end_of_week');
2711 END IF;
2712
2713 OPEN days_of_week_cursor;
2714 FETCH days_of_week_cursor INTO v_fri,v_sat,v_sun,v_mon,v_tue,v_wed,v_thu;
2715 CLOSE days_of_week_cursor;
2716
2717 -- Put the days of the week in an array for code cleanliness.
2718 v_day_table := SYSTEM.QRM_VARCHAR_table(Nvl(v_fri, 'Y'), Nvl(v_sat, 'Y'),
2719 Nvl(v_sun, 'Y'), Nvl(v_mon, 'Y'), Nvl(v_tue, 'Y'),
2720 Nvl(v_wed, 'Y'), Nvl(v_thu, 'Y'));
2721
2722 -- First go from top to bottom (Friday to Thursday)
2723 FOR i IN 1..v_day_table.count LOOP
2724 -- If non-business day, then we want to add to the count of the
2725 -- number of days this "block" of non-business days consists of
2726 IF v_day_table(i) = 'N' THEN
2727 v_nonbus_count := v_nonbus_count + 1;
2728 v_last_nonbus_day := i;
2729 ELSE
2730 -- If we hit a business day, and we've already encounted a non-business
2731 -- day "block" (count>0), we're done.
2732 IF v_nonbus_count > 0 THEN
2733 EXIT;
2734 END IF;
2735 END IF;
2736 END LOOP;
2737
2738 -- Now we just have to make sure this "block" of non-business days
2739 -- is not any bigger than it seems. It is bigger if it starts on
2740 -- the first day and the last day turns out NOCOPY to be a non-business
2741 -- day as well. If this is the case, we need to increase the "block"
2742 -- size, by running backwards.
2743 IF v_day_table(1) = 'N' AND v_day_table(7)= 'N' THEN
2744 FOR i IN REVERSE 1..v_day_table.count LOOP
2745 -- If non-business day, we continue add to the "block" size.
2746 IF v_day_table(i) = 'N' THEN
2747 v_nonbus_count := v_nonbus_count + 1;
2748 ELSE
2749 EXIT;
2750 END IF;
2751 END LOOP;
2752 END IF;
2753
2754 IF (g_proc_level>=g_debug_level) THEN
2755 xtr_risk_debug_pkg.dlog('get_beg_end_of_week: ' || 'v_last_nonbus_day',v_last_nonbus_day);
2756 xtr_risk_debug_pkg.dlog('get_beg_end_of_week: ' || 'v_nonbus_count',v_nonbus_count);
2757 END IF;
2758
2759 IF v_nonbus_count <= 0 OR v_nonbus_count >= 7 THEN
2760 p_beg_date := trunc(p_ref_date,'D');
2761 p_end_date := trunc(p_ref_date,'D')+6;
2762 ELSE
2763 -- TO_CHAR with 'D' format returns 1-7 for SUN-SAT (dependent on
2764 -- NLS_TERRITORY settings).
2765 -- Since this changes depending on the customer settings,
2766 -- we need a fixed reference point (Fri 5/31/2002).
2767 -- Now that we know NLS number system, we will apply that "offset"
2768 -- to our ref_date so that it is back in our system (where Friday
2769 -- is 1). If Friday is no longer 1, we would need to pick a date
2770 -- that falls on that new reference day.
2771 v_offset := to_number(to_char(to_date('05/31/2002','MM/DD/YYYY'),'D'))-1;
2772 v_ref_day := to_number(to_char(p_ref_date,'D')) - v_offset;
2773 -- If negative, need to wrap so represent the correct day
2774 IF v_ref_day < 0 THEN
2775 v_ref_day := v_ref_day + 7;
2776 END IF;
2777 -- If negative, need to wrap so represent the correct day
2778 v_days_before := v_ref_day - (v_last_nonbus_day + 1);
2779 IF v_days_before < 0 THEN
2780 v_days_before := v_days_before + 7;
2781 END IF;
2782 p_beg_date := trunc(p_ref_date) - v_days_before;
2783 p_end_date := trunc(p_ref_date) + (7 - (v_days_before + v_nonbus_count) - 1);
2784
2785 IF (g_proc_level>=g_debug_level) THEN
2786 xtr_risk_debug_pkg.dlog('get_beg_end_of_week: ' || 'v_ref_day',v_ref_day);
2787 xtr_risk_debug_pkg.dlog('get_beg_end_of_week: ' || 'v_days_before',v_days_before);
2788 END IF;
2789 END IF;
2790
2791 IF (g_proc_level>=g_debug_level) THEN
2792 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGRREGATION_P.get_beg_end_of_week');
2793 END IF;
2794 END get_beg_end_of_week;
2795
2796
2797
2798 /***************************************************************
2799 This function calculates the actual date givent the relative date
2800 ,fixed date, and the date type.
2801 ***************************************************************/
2802 FUNCTION calculate_relative_date(p_ref_date DATE,
2803 p_date_type VARCHAR2,
2804 p_as_of_date DATE,
2805 p_start_date_ref VARCHAR2,
2806 p_start_date_offset NUMBER,
2807 p_start_offset_type VARCHAR2,
2808 p_calendar_id NUMBER,
2809 p_business_week VARCHAR2)
2810 RETURN DATE IS
2811 v_date DATE;
2812 v_dummy DATE;
2813 BEGIN
2814
2815 IF (g_proc_level>=g_debug_level) THEN --bug 3236479
2816 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGRREGATION_P.CALCULATE_RELATIVE_DATE');
2817 END IF;
2818
2819 --see whether the as of date is fixed or relative
2820 IF p_date_type='F' THEN
2821 v_date := TRUNC(p_as_of_date);
2822 IF (g_proc_level>=g_debug_level) THEN
2823 xtr_risk_debug_pkg.dlog('calculate_relative_date: ' || 'v_date',v_date);
2824 END IF;
2825 ELSE
2826 --reference
2827 IF p_start_date_ref='BW' THEN --Beginning of Week
2828 get_beg_end_of_week(p_calendar_id,TRUNC(p_ref_date),v_date,v_dummy);
2829 ELSIF p_start_date_ref='BM' THEN --Beginning of Month
2830 v_date := TRUNC(p_ref_date,'MONTH');
2831 ELSIF p_start_date_ref='BY' THEN --Beginning of Year
2832 v_date := TRUNC(p_ref_date,'YEAR');
2833 ELSIF p_start_date_ref='S' THEN --p_ref_date
2834 v_date := TRUNC(p_ref_date);
2835 ELSIF p_start_date_ref='EW' THEN --End of Week
2836 get_beg_end_of_week(p_calendar_id,TRUNC(p_ref_date),v_dummy,v_date);
2837 ELSIF p_start_date_ref='EM' THEN --End of Month
2838 v_date := ADD_MONTHS(TRUNC(p_ref_date,'MONTH'),1)-1;
2839 ELSIF p_start_date_ref='EY' THEN --End of Year
2840 v_date := ADD_MONTHS(TRUNC(p_ref_date,'YEAR'),12)-1;
2841 ELSE
2842 RAISE_APPLICATION_ERROR
2843 (-20001,'Invalid start_date_ref.');
2844 END IF;
2845 --offset
2846 IF p_start_date_offset IS NOT NULL THEN
2847 IF p_start_offset_type='D' THEN
2848 v_date := v_date+p_start_date_offset;
2849 ELSIF p_start_offset_type='W' THEN
2850 v_date := v_date+7*p_start_date_offset;
2851 ELSIF p_start_offset_type='M' THEN
2852 v_date := ADD_MONTHS(v_date,p_start_date_offset);
2853 ELSIF p_start_offset_type='Y' THEN
2854 v_date := ADD_MONTHS(v_date,12*p_start_date_offset);
2855 ELSE
2856 RAISE_APPLICATION_ERROR
2857 (-20001,'Invalid start_offset_type.');
2858 END IF;
2859 END IF;
2860 END IF;
2861 IF (g_proc_level>=g_debug_level) THEN
2862 xtr_risk_debug_pkg.dlog('calculate_relative_date: ' || 'v_date',v_date);
2863 xtr_risk_debug_pkg.dlog('calculate_relative_date: ' || 'p_calendar_id',p_calendar_id);
2864 END IF;
2865 --Only check holiday if p_business_week='C' otherwise
2866 --assume 7-days-week
2867 IF p_business_week='C' THEN
2868 v_date := find_gl_business_days('F',
2869 v_date, p_calendar_id);
2870 END IF;
2871
2872 IF (g_proc_level>=g_debug_level) THEN --bug 3236479
2873 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGRREGATION_P.CALCULATE_RELATIVE_DATE');
2874 END IF;
2875
2876 RETURN v_date;
2877 END calculate_relative_date;
2878
2879
2880
2881 /***************************************************************
2882 This procedure calculates the time-buckets interval actual
2883 dates and saved them intot qrm_saved_analyses_col
2884 ***************************************************************/
2885 PROCEDURE update_timebuckets (p_name VARCHAR2,
2886 p_ref_date DATE,
2887 p_tb_name VARCHAR2,
2888 p_tb_label VARCHAR2,
2889 p_as_of_date DATE,
2890 p_start_date_ref VARCHAR2,
2891 p_start_date_offset NUMBER,
2892 p_start_offset_type VARCHAR2,
2893 p_row_agg_no NUMBER,
2894 p_max_col_no OUT NOCOPY NUMBER,
2895 p_date_type VARCHAR2,
2896 p_calendar_id NUMBER,
2897 p_business_week VARCHAR2,
2898 p_col_seq_no IN OUT NOCOPY XTR_MD_NUM_TABLE,
2899 p_col_seq_no_key IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
2900 p_col_name_map IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
2901 p_percent_col_name_map IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
2902 p_a1 IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
2903 p_col_type IN OUT NOCOPY XTR_MD_NUM_TABLE,
2904 p_col_hidden IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
2905 p_start_date IN OUT NOCOPY SYSTEM.QRM_DATE_TABLE,
2906 p_end_date IN OUT NOCOPY SYSTEM.QRM_DATE_TABLE,
2907 p_tb_label_arr IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE) IS
2908
2909 CURSOR v_tb_cursor IS
2910 SELECT interval_length, interval_type, label
2911 FROM qrm_time_intervals
2912 WHERE tb_name = p_tb_name AND sequence_number<>0
2913 ORDER BY sequence_number;
2914 CURSOR v_find_tb IS
2915 SELECT COUNT(*) FROM qrm_time_buckets
2916 WHERE tb_name = p_tb_name;
2917 v_int_length xtr_md_num_table;
2918 v_int_type SYSTEM.QRM_VARCHAR_table;
2919 v_label SYSTEM.QRM_VARCHAR_table;
2920 v_date DATE;
2921 v_prev_end_date DATE;
2922 v_dummy NUMBER;
2923 i NUMBER(5);
2924 j NUMBER(5);
2925 BEGIN
2926 IF (g_proc_level>=g_debug_level) THEN
2927 xtr_risk_debug_pkg.dpush(null,'QRM_PA_P.UPDATE_TIMEBUCKETS'); --bug3236479
2928 END IF;
2929
2930 --check whether time buckets exist
2931 OPEN v_find_tb;
2932 FETCH v_find_tb INTO v_dummy;
2933 CLOSE v_find_tb;
2934 IF v_dummy=0 THEN
2935 FND_MESSAGE.SET_NAME('QRM','QRM_ANA_NO_TIMEBUCKETS');
2936 FND_MESSAGE.SET_TOKEN('TB_NAME',p_tb_name);
2937 RAISE e_pagg_no_timebuckets_found;
2938 END IF;
2939
2940 OPEN v_tb_cursor;
2941 FETCH v_tb_cursor BULK COLLECT INTO v_int_length,v_int_type,v_label;
2942 p_max_col_no := v_int_length.LAST;
2943 CLOSE v_tb_cursor;
2944
2945 --construct p_seq_no, p_a1, p_hidden, p_type
2946 p_col_name_map.EXTEND(p_max_col_no);
2947 p_percent_col_name_map.EXTEND(p_max_col_no);
2948 p_a1.EXTEND(p_max_col_no);
2952 p_col_seq_no_key.EXTEND(p_max_col_no);
2949 p_col_seq_no.EXTEND(p_max_col_no);
2950 p_col_type.EXTEND(p_max_col_no);
2951 p_col_hidden.EXTEND(p_max_col_no);
2953 --start and end date has not been initilized earlier, thus, lacking
2954 --p_row_agg_no rows
2955 p_start_date.EXTEND(p_max_col_no+p_row_agg_no);
2956 p_end_date.EXTEND(p_max_col_no+p_row_agg_no);
2957 p_tb_label_arr.EXTEND(p_max_col_no+p_row_agg_no);
2958
2959 --for the rest of the timebuckets
2960 FOR i IN p_row_agg_no+1..(p_row_agg_no+p_max_col_no) LOOP
2961 --find start date = previous end date + 1
2962 IF i=p_row_agg_no+1 THEN --for the first case/timebucket
2963 p_start_date(i) := calculate_relative_date(p_ref_date,
2964 p_date_type,
2965 p_as_of_date,
2966 p_start_date_ref,
2967 p_start_date_offset,
2968 p_start_offset_type,
2969 p_calendar_id,
2970 p_business_week);
2971 v_prev_end_date := p_start_date(i);
2972 ELSE
2973 IF p_business_week='C' THEN
2974 p_start_date(i) := find_gl_business_days('F',
2975 p_end_date(i-1)+1,p_calendar_id);
2976 ELSE
2977 p_start_date(i) := p_end_date(i-1)+1;
2978 END IF;
2979 --need the -1 since timebucket start and end date cannot overlap.
2980 v_prev_end_date := p_start_date(i);
2981 END IF;
2982 IF (g_proc_level>=g_debug_level) THEN
2983 xtr_risk_debug_pkg.dlog('p_start_date(i)',i||':'||p_start_date(i));
2984 END IF;
2985 xtr_risk_debug_pkg.dlog('v_int_type(i),v_int_length(i)',i||':'||v_int_type(i-p_row_agg_no)||','||v_int_length(i-p_row_agg_no));
2986 IF (g_proc_level>=g_debug_level) THEN
2987 xtr_risk_debug_pkg.dlog('v_prev_end_date',v_prev_end_date);
2988 END IF;
2989 --find temp end date
2990 IF v_int_type(i-p_row_agg_no)='D' THEN
2991 v_date := v_prev_end_date+v_int_length(i-p_row_agg_no);
2992 ELSIF v_int_type(i-p_row_agg_no)='W' THEN
2993 v_date := v_prev_end_date+7*v_int_length(i-p_row_agg_no);
2994 ELSIF v_int_type(i-p_row_agg_no)='M' THEN
2995 v_date := add_months(v_prev_end_date,v_int_length(i-p_row_agg_no));
2996 ELSIF v_int_type(i-p_row_agg_no)='Y' THEN
2997 v_date := add_months(v_prev_end_date,12*v_int_length(i-p_row_agg_no));
2998 ELSE
2999 RAISE_APPLICATION_ERROR
3000 (-20001,'Invalid Interval Type of Time-Buckets');
3001 END IF;
3002 --find actual end date
3003 IF p_business_week='C' THEN
3004 p_end_date(i) := find_gl_business_days('B',v_date-1,p_calendar_id);
3005 ELSE
3006 p_end_date(i) := v_date-1;
3007 END IF;
3008 --xtr_risk_debug_pkg.dlog('p_end_date(i)',i||':'||p_end_date(i));
3009 p_col_seq_no(i) := i;
3010 p_col_seq_no_key(i) := TO_CHAR(i);
3011 p_col_name_map(i) := 'M'||TO_CHAR(i-p_row_agg_no);
3012 p_percent_col_name_map(i) := 'P'||TO_CHAR(i-p_row_agg_no);
3013 p_tb_label_arr(i) := v_label(i-p_row_agg_no);
3014 --logic whether to look for TB Label or display the end date
3015 IF p_tb_label='L' THEN
3016 p_a1(i) := p_tb_label_arr(i);
3017 ELSE--p_tb_label='D'
3018 p_a1(i) := TO_CHAR(TRUNC(p_end_date(i)));
3019 END IF;
3020 p_col_type(i) := -1; --measure type
3021 p_col_hidden(i) := 'N';
3022 END LOOP;
3023
3024 IF (g_proc_level>=g_debug_level) THEN
3025 xtr_risk_debug_pkg.dpop(null,'QRM_PA_P.UPDATE_TIMEBUCKETS');--bug 3236479
3026 END IF;
3027 END update_timebuckets;
3028
3029
3030
3031 /***************************************************************
3032 This procedure calculates the time-buckets start date and end date
3033 and used by qrmpacab.pls
3034
3035 IMPORTANT:
3036 For Timebuckets style, there is a discrepancy between the start
3037 and end date in the DB and in the display.
3038 END_DATE in DB for style=X means the first
3039 START_DATE in the Timebuckets display.
3040 That's why this procedure passes in DB's END_DATE in what should've
3041 been the Timebuckets START_DATE only when analysis type='P'.
3042 (Bug 2355584 no.2)
3043 ***************************************************************/
3044 PROCEDURE calc_tb_start_end_dates (p_name VARCHAR2,
3045 p_ref_date DATE,
3046 p_tb_name VARCHAR2,
3047 p_tb_label VARCHAR2,
3048 p_end_date IN OUT NOCOPY DATE,
3049 p_end_date_ref IN OUT NOCOPY VARCHAR2,
3050 p_end_date_offset IN OUT NOCOPY NUMBER,
3051 p_end_offset_type IN OUT NOCOPY VARCHAR2,
3052 p_date_type VARCHAR2,
3053 p_calendar_id NUMBER,
3054 p_business_week VARCHAR2,
3055 p_start_date IN OUT NOCOPY DATE,
3056 p_start_date_ref IN OUT NOCOPY VARCHAR2,
3057 p_start_date_offset IN OUT NOCOPY NUMBER,
3058 p_start_offset_type IN OUT NOCOPY VARCHAR2,
3059 p_analysis_type VARCHAR2) IS
3060
3061 v_col_seq_no XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
3062 v_col_seq_no_key SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
3063 v_col_name_map SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
3064 v_percent_col_name_map SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
3065 v_a1 SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
3066 v_col_type XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
3067 v_col_hidden SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
3068 v_start_date SYSTEM.QRM_DATE_TABLE := SYSTEM.QRM_DATE_TABLE();
3069 v_end_date SYSTEM.QRM_DATE_TABLE := SYSTEM.QRM_DATE_TABLE();
3070 v_tb_label_arr SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
3071 v_row_agg_no NUMBER := 0;
3072 v_max_col_no NUMBER;
3073
3074 BEGIN
3075 IF (g_proc_level>=g_debug_level) THEN
3076 xtr_risk_debug_pkg.dpush(null,'QRM_PA_P.CALC_TB_START_END_DATE');--bug 3236479
3077 END IF;
3078 --need to swap because the END_DATE is the start date for the
3079 --timebuckets
3080 update_timebuckets(p_name, p_ref_date, p_tb_name, p_tb_label, p_end_date,
3081 p_end_date_ref, p_end_date_offset,
3082 p_end_offset_type, v_row_agg_no, v_max_col_no,
3083 p_date_type, p_calendar_id, p_business_week,
3084 v_col_seq_no, v_col_seq_no_key, v_col_name_map,
3085 v_percent_col_name_map, v_a1, v_col_type,
3086 v_col_hidden, v_start_date, v_end_date,
3087 v_tb_label_arr);
3088 p_start_date := v_start_date(1);
3089 p_start_date_ref := p_end_date_ref;
3090 p_start_date_offset := p_end_date_offset;
3091 p_start_offset_type := p_end_offset_type;
3092 p_end_date := v_end_date(v_end_date.LAST);
3093
3094 IF (g_proc_level>=g_debug_level) THEN
3095 xtr_risk_debug_pkg.dpop(null,'QRM_PA_P.CALC_TB_START_END_DATE');--bug 3236479
3096 END IF;
3097 END calc_tb_start_end_dates;
3098
3099
3100
3101 /***************************************************************
3102 This function should be used
3103 everytime the Total page is modified.
3104 ***************************************************************/
3105 FUNCTION update_total(p_name VARCHAR2, p_ref_date DATE) RETURN BOOLEAN IS
3106 BEGIN
3107 IF (g_proc_level>=g_debug_level) THEN
3108 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_TOTAL');--bug 3236479
3109 END IF;
3110 --update qrm_saved_analyses_row first
3111 --set all the total rows to be hidden
3112 UPDATE qrm_saved_analyses_row
3113 SET hidden='Y', last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
3114 WHERE type>0 AND analysis_name=p_name;
3115
3116 IF (g_event_level>=g_debug_level) THEN --bug 3236479
3117 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_ROW.HIDDEN=Y',
3118 'QRM_PA_AGGREGATION_P.UPDATE_TOTAL',g_event_level);
3119 END IF;
3120
3121 --set selected total rows to be displayed
3122 UPDATE qrm_saved_analyses_row
3123 SET hidden='N', last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
3124 WHERE analysis_name=p_name
3125 AND type IN (SELECT att_order FROM qrm_analysis_atts
3126 WHERE analysis_name=p_name
3127 AND total_ind<>'N'
3128 AND type='R'
3129 AND history_flag='S');
3130
3131 IF (g_event_level>=g_debug_level) THEN --bug 3236479
3132 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_ROW.HIDDEN=N for those that need to be displayed',
3133 'QRM_PA_AGGREGATION_P.UPDATE_TOTAL',g_event_level);
3134 END IF;
3135
3136 --update qrm_saved_analyses_col
3137 --set all the total rows to be hidden
3138 UPDATE qrm_saved_analyses_col
3139 SET hidden='Y', last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
3140 WHERE type>0 AND analysis_name=p_name;
3141
3142 IF (g_event_level>=g_debug_level) THEN --bug 3236479
3143 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_COL.HIDDEN=Y',
3144 'QRM_PA_AGGREGATION_P.UPDATE_TOTAL',g_event_level);
3145 END IF;
3146
3147 --set selected total rows to be displayed
3148 UPDATE qrm_saved_analyses_col
3149 SET hidden='N', last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
3150 WHERE analysis_name=p_name
3151 AND type IN (SELECT att_order FROM qrm_analysis_atts
3152 WHERE analysis_name=p_name
3153 AND total_ind<>'N'
3154 AND type='C'
3155 AND history_flag='S');
3156
3157 IF (g_event_level>=g_debug_level) THEN --bug 3236479
3158 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_COL.HIDDEN=N for those that need to be displayed',
3159 'QRM_PA_AGGREGATION_P.UPDATE_TOTAL',g_event_level);
3160 END IF;
3161
3162 COMMIT;
3163 IF (g_proc_level>=g_debug_level) THEN
3164 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_TOTAL');--bug 3236479
3165 END IF;
3166 RETURN TRUE;
3167 EXCEPTION
3168 WHEN OTHERS THEN
3169 IF (g_error_level>=g_debug_level) THEN
3170 --xtr_risk_debug_pkg.dpop('QRM_PA_AGGREGATION_P.UPDATE_TOTAL');
3171 xtr_risk_debug_pkg.dlog('EXCEPTION','OTHERS','QRM_PA_AGGREGATION_P.UPDATE_TOTAL',g_error_level);--bug 3236479
3172 END IF;
3173 RETURN FALSE;
3174 END update_total;
3175
3176
3177
3178 /***************************************************************
3179 This function updates the percent columns (P1..P100) given
3180 the analysis name and seq_no, one row at the time.
3181 ***************************************************************/
3182 FUNCTION update_percent_cols(p_name VARCHAR2,
3183 p_row NUMBER,
3184 p_ref_date DATE,
3185 p_meas XTR_MD_NUM_TABLE)
3186 RETURN BOOLEAN IS
3187 BEGIN
3188
3189 IF (g_proc_level>=g_debug_level) THEN
3190 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_PERCENT_COLS');--bug 3236479
3191 END IF;
3192
3193 UPDATE qrm_saved_analyses_row
3194 SET p1=p_meas(1),p2=p_meas(2),p3=p_meas(3),
3195 p4=p_meas(4),p5=p_meas(5),p6=p_meas(6),
3196 p7=p_meas(7),p8=p_meas(8),p9=p_meas(9),
3197 p10=p_meas(10),p11=p_meas(11),
3198 p12=p_meas(12),
3199 p13=p_meas(13),p14=p_meas(14),
3200 p15=p_meas(15),
3201 p16=p_meas(16),p17=p_meas(17),
3202 p18=p_meas(18),
3203 p19=p_meas(19),p20=p_meas(20),
3204 p21=p_meas(21),p22=p_meas(22),
3205 p23=p_meas(23),
3206 p24=p_meas(24),p25=p_meas(25),
3207 p26=p_meas(26),
3208 p27=p_meas(27),p28=p_meas(28),
3209 p29=p_meas(29),
3210 p30=p_meas(30),p31=p_meas(31),
3211 p32=p_meas(32),
3212 p33=p_meas(33),p34=p_meas(34),
3213 p35=p_meas(35),
3214 p36=p_meas(36),p37=p_meas(37),
3215 p38=p_meas(38),
3216 p39=p_meas(39),p40=p_meas(40),
3217 p41=p_meas(41),p42=p_meas(42),
3218 p43=p_meas(43),
3219 p44=p_meas(44),p45=p_meas(45),
3220 p46=p_meas(46),
3221 p47=p_meas(47),p48=p_meas(48),
3222 p49=p_meas(49),p50=p_meas(50),
3223 p51=p_meas(51),p52=p_meas(52),
3224 p53=p_meas(53),
3225 p54=p_meas(54),p55=p_meas(55),
3226 p56=p_meas(56),
3227 p57=p_meas(57),p58=p_meas(58),
3228 p59=p_meas(59),
3229 p60=p_meas(60),p61=p_meas(61),
3230 p62=p_meas(62),
3231 p63=p_meas(63),p64=p_meas(64),
3232 p65=p_meas(65),
3233 p66=p_meas(66),p67=p_meas(67),
3234 p68=p_meas(68),
3235 p69=p_meas(69),p70=p_meas(70),
3236 p71=p_meas(71),p72=p_meas(72),
3237 p73=p_meas(73),
3238 p74=p_meas(74),p75=p_meas(75),
3239 p76=p_meas(76),
3240 p77=p_meas(77),p78=p_meas(78),
3241 p79=p_meas(79),
3242 p80=p_meas(80),p81=p_meas(81),
3243 p82=p_meas(82),
3244 p83=p_meas(83),p84=p_meas(84),
3245 p85=p_meas(85),
3246 p86=p_meas(86),p87=p_meas(87),
3247 p88=p_meas(88),
3248 p89=p_meas(89),p90=p_meas(90),
3249 p91=p_meas(91),p92=p_meas(92),
3250 p93=p_meas(93),
3251 p94=p_meas(94),p95=p_meas(95),
3252 p96=p_meas(96),
3253 p97=p_meas(97),p98=p_meas(98),
3254 p99=p_meas(99),p100=p_meas(100),
3255 last_updated_by=FND_GLOBAL.user_id,
3256 last_update_date=p_ref_date,
3257 last_update_login=FND_GLOBAL.login_id
3258 WHERE analysis_name=p_name
3259 AND seq_no=p_row;
3260
3261 IF (g_proc_level>=g_debug_level) THEN
3262 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_PERCENT_COLS');--bug 3236479
3263 END IF;
3264
3265 RETURN TRUE;
3266 EXCEPTION
3267 WHEN OTHERS THEN
3268 IF (g_proc_level>=g_debug_level) THEN
3269 --xtr_risk_debug_pkg.dpop('QRM_PA_AGGREGATION_P.UPDATE_PERCENT_COLS');
3270 xtr_risk_debug_pkg.dlog('EXCEPTION','OTHERS','QRM_PA_AGGREGATION_P.UPDATE_PERCENT_COLS');--bug 3236479
3271 END IF;
3272 RETURN FALSE;
3273 END update_percent_cols;
3274
3275
3276
3277 /***************************************************************
3278 This function converts values from M1..M100 and translate them
3279 into USD and then updates the P1..P100 before calculating the
3280 percent. This is necessary to get values based on 1 currency so
3281 that they can be percentaged correctly.
3282 Please refer to bug 2393589 for more details.
3283 ***************************************************************/
3284 FUNCTION translate_to_usd (p_name VARCHAR2,
3285 p_ref_date DATE,
3286 p_md_set_code VARCHAR2,
3287 p_tot_level NUMBER)
3288 RETURN BOOLEAN IS
3289
3290 v_level NUMBER;
3291 v_row NUMBER;
3292 v_meas XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
3293 v_ccy SYSTEM.QRM_VARCHAR_TABLE := SYSTEM.QRM_VARCHAR_TABLE();
3294 v_fx_rate NUMBER;
3295 v_success BOOLEAN;
3296
3297 CURSOR get_all_measures IS
3298 SELECT seq_no,type,
3299 m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,
3300 m18,m19,m20,m21,m22,m23,m24,m25,m26,m27,m28,m29,m30,m31,m32,m33,m34,
3301 m35,m36,m37,m38,m39,m40,m41,m42,m43,m44,m45,m46,m47,m48,m49,m50,m51,
3302 m52,m53,m54,m55,m56,m57,m58,m59,m60,m61,m62,m63,m64,m65,m66,m67,m68,
3303 m69,m70,m71,m72,m73,m74,m75,m76,m77,m78,m79,m80,m81,m82,m83,m84,m85,
3304 m86,m87,m88,m89,m90,m91,m92,m93,m94,m95,m96,m97,m98,m99,m100,
3305 curr1,curr2,curr3,curr4,curr5,curr6,curr7,curr8,curr9,
3306 curr10,curr11,curr12,curr13,curr14,curr15,curr16,curr17,
3307 curr18,curr19,curr20,curr21,curr22,curr23,curr24,curr25,curr26,
3308 curr27,curr28,curr29,curr30,curr31,curr32,curr33,curr34,
3309 curr35,curr36,curr37,curr38,curr39,curr40,curr41,curr42,curr43,
3310 curr44,curr45,curr46,curr47,curr48,curr49,curr50,curr51,
3311 curr52,curr53,curr54,curr55,curr56,curr57,curr58,curr59,curr60,
3312 curr61,curr62,curr63,curr64,curr65,curr66,curr67,curr68,
3313 curr69,curr70,curr71,curr72,curr73,curr74,curr75,curr76,curr77,
3314 curr78,curr79,curr80,curr81,curr82,curr83,curr84,curr85,
3315 curr86,curr87,curr88,curr89,curr90,curr91,curr92,curr93,curr94,
3316 curr95,curr96,curr97,curr98,curr99,curr100
3317 FROM qrm_saved_analyses_row
3318 WHERE analysis_name=p_name
3319 ORDER BY seq_no;
3320
3321 BEGIN
3322
3323 IF (g_proc_level>=g_debug_level) THEN
3324 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.TRANSLATE_TO_USD');
3325 END IF;
3326
3327 OPEN get_all_measures;
3328 v_meas.EXTEND(100);
3329 v_ccy.EXTEND(100);
3330 --xtr_risk_debug_pkg.dlog('p_name',p_name);
3331 LOOP
3332 FETCH get_all_measures INTO
3333 v_row,v_level,v_meas(1),v_meas(2),v_meas(3),v_meas(4),v_meas(5),
3334 v_meas(6),v_meas(7),v_meas(8),v_meas(9),v_meas(10),v_meas(11),
3335 v_meas(12),v_meas(13),v_meas(14),v_meas(15),v_meas(16),
3336 v_meas(17),
3337 v_meas(18),v_meas(19),v_meas(20),
3338 v_meas(21),v_meas(22),v_meas(23),v_meas(24),v_meas(25),
3339 v_meas(26),v_meas(27),v_meas(28),v_meas(29),v_meas(30),
3340 v_meas(31),
3341 v_meas(32),v_meas(33),v_meas(34),v_meas(35),v_meas(36),
3342 v_meas(37),
3343 v_meas(38),v_meas(39),v_meas(40),
3344 v_meas(41),v_meas(42),v_meas(43),v_meas(44),v_meas(45),
3345 v_meas(46),v_meas(47),v_meas(48),v_meas(49),v_meas(50),
3346 v_meas(51),v_meas(52),v_meas(53),v_meas(54),v_meas(55),
3347 v_meas(56),v_meas(57),v_meas(58),v_meas(59),v_meas(60),
3348 v_meas(61),
3349 v_meas(62),v_meas(63),v_meas(64),v_meas(65),v_meas(66),
3350 v_meas(67),
3351 v_meas(68),v_meas(69),v_meas(70),
3352 v_meas(71),v_meas(72),v_meas(73),v_meas(74),v_meas(75),
3353 v_meas(76),v_meas(77),v_meas(78),v_meas(79),v_meas(80),
3354 v_meas(81),
3355 v_meas(82),v_meas(83),v_meas(84),v_meas(85),v_meas(86),
3356 v_meas(87),
3357 v_meas(88),v_meas(89),v_meas(90),
3358 v_meas(91),v_meas(92),v_meas(93),v_meas(94),v_meas(95),
3359 v_meas(96),v_meas(97),v_meas(98),v_meas(99),v_meas(100),
3360 v_ccy(1),v_ccy(2),v_ccy(3),v_ccy(4),v_ccy(5),
3361 v_ccy(6),v_ccy(7),v_ccy(8),v_ccy(9),v_ccy(10),v_ccy(11),
3362 v_ccy(12),v_ccy(13),v_ccy(14),v_ccy(15),v_ccy(16),
3363 v_ccy(17),
3364 v_ccy(18),v_ccy(19),v_ccy(20),
3365 v_ccy(21),v_ccy(22),v_ccy(23),v_ccy(24),v_ccy(25),
3366 v_ccy(26),v_ccy(27),v_ccy(28),v_ccy(29),v_ccy(30),
3367 v_ccy(31),
3368 v_ccy(32),v_ccy(33),v_ccy(34),v_ccy(35),v_ccy(36),
3369 v_ccy(37),
3370 v_ccy(38),v_ccy(39),v_ccy(40),
3371 v_ccy(41),v_ccy(42),v_ccy(43),v_ccy(44),v_ccy(45),
3372 v_ccy(46),v_ccy(47),v_ccy(48),v_ccy(49),v_ccy(50),
3373 v_ccy(51),v_ccy(52),v_ccy(53),v_ccy(54),v_ccy(55),
3374 v_ccy(56),v_ccy(57),v_ccy(58),v_ccy(59),v_ccy(60),
3375 v_ccy(61),
3376 v_ccy(62),v_ccy(63),v_ccy(64),v_ccy(65),v_ccy(66),
3377 v_ccy(67),
3378 v_ccy(68),v_ccy(69),v_ccy(70),
3379 v_ccy(71),v_ccy(72),v_ccy(73),v_ccy(74),v_ccy(75),
3380 v_ccy(76),v_ccy(77),v_ccy(78),v_ccy(79),v_ccy(80),
3381 v_ccy(81),
3382 v_ccy(82),v_ccy(83),v_ccy(84),v_ccy(85),v_ccy(86),
3383 v_ccy(87),
3384 v_ccy(88),v_ccy(89),v_ccy(90),
3385 v_ccy(91),v_ccy(92),v_ccy(93),v_ccy(94),v_ccy(95),
3386 v_ccy(96),v_ccy(97),v_ccy(98),v_ccy(99),v_ccy(100);
3387 EXIT WHEN get_all_measures%NOTFOUND OR get_all_measures%NOTFOUND IS NULL;
3388 --
3389 FOR i IN 1..v_ccy.COUNT LOOP
3390 --if TYPE > total level then insert NULL
3391 IF v_level>0 AND v_level<p_tot_level THEN
3392 v_meas(i) := NULL;
3393 ELSE
3394 --do some checks for optimization
3395 IF v_ccy(i) IS NOT NULL and v_meas(i) IS NOT NULL and
3396 v_meas(i)<>0 THEN
3397 --Additional check for optimization
3398 IF i>1 and v_ccy(i)=v_ccy(i-1) THEN
3399 v_meas(i) := v_meas(i)*v_fx_rate;
3400 ELSE
3401 v_fx_rate := get_fx_rate(p_md_set_code,
3402 p_ref_date,
3403 v_ccy(i),
3404 'USD',
3405 'M');
3406 v_meas(i) := v_meas(i)*v_fx_rate;
3407 END IF;
3408 END IF;
3409 END IF;
3410 END LOOP;
3411 --
3412 v_success := update_percent_cols(p_name,v_row,p_ref_date,v_meas);
3413 --
3414 END LOOP;
3415 CLOSE get_all_measures;
3416
3417 IF (g_proc_level>=g_debug_level) THEN
3418 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.TRANSLATE_TO_USD'); --bug 3236479
3419 END IF;
3420
3421 RETURN TRUE;
3422
3423 EXCEPTION
3424 WHEN OTHERS THEN
3425 IF (g_error_level>=g_debug_level) THEN
3426 xtr_risk_debug_pkg.dlog('EXCEPTION','OTHERS','QRM_PA_AGGREGATION_P.TRANSLATE_TO_USD',g_error_level);--bug 3236479
3427 END IF;
3428 RETURN FALSE;
3429 END translate_to_usd;
3430
3431
3432
3433 /***************************************************************
3434 This update_percent function should be used by external call
3435 everytime the Total page is modified.
3436 Internal call (transform_and_save) will use the other signature.
3437 ***************************************************************/
3438 FUNCTION update_percent (p_name VARCHAR2,p_ref_date DATE)
3439 RETURN BOOLEAN IS
3440
3441 CURSOR count_column IS
3442 SELECT COUNT(*) FROM qrm_saved_analyses_col
3443 WHERE analysis_name=p_name
3444 AND type>-2;
3445 CURSOR count_row_header IS
3446 SELECT COUNT(*) FROM qrm_saved_analyses_col
3447 WHERE analysis_name=p_name
3448 AND type=-2;
3449 CURSOR get_style IS
3450 SELECT style,md_set_code FROM qrm_analysis_settings
3451 WHERE analysis_name=p_name AND history_flag='S';
3452 v_max_col_no NUMBER(5);
3453 v_row_agg_no NUMBER(5);
3454 v_style VARCHAR2(1);
3455 v_success BOOLEAN;
3456 v_md_set_code qrm_analysis_settings.md_set_code%TYPE;
3457
3458 BEGIN
3459
3460 IF (g_proc_level>=g_debug_level) THEN
3461 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_PERCENT'); --bug 3236479
3462 END IF;
3463
3464 OPEN count_column;
3465 FETCH count_column INTO v_max_col_no;
3466 CLOSE count_column;
3467 OPEN count_row_header;
3468 FETCH count_row_header INTO v_row_agg_no;
3469 CLOSE count_row_header;
3470 OPEN get_style;
3471 FETCH get_style INTO v_style,v_md_set_code;
3472 CLOSE get_style;
3473 v_success:=update_percent(p_name,v_style,v_row_agg_no,v_max_col_no,
3474 p_ref_date,v_md_set_code);
3475
3476 IF (g_proc_level>=g_debug_level) THEN
3477 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_PERCENT'); --bug 3236479
3478 END IF;
3479
3480 RETURN v_success;
3481 END update_percent;
3482
3483
3484
3485 /***************************************************************
3486 This update_percent function should be used by internal call
3487 from transform_and_save procedure.
3488 External call should use the other signature.
3489 ***************************************************************/
3490 FUNCTION update_percent (p_name VARCHAR2,
3491 p_style VARCHAR2,
3492 p_row_agg_no NUMBER,
3493 p_max_col_no NUMBER,
3494 p_ref_date DATE,
3495 p_md_set_code VARCHAR2)
3496 RETURN BOOLEAN IS
3497
3498 v_tot XTR_MD_NUM_TABLE := XTR_MD_NUM_TABLE();
3499 v_type VARCHAR2(1);
3500 v_level VARCHAR2(1); --the order
3501 v_row NUMBER;
3502 v_previous_count NUMBER := 1;
3503 i NUMBER(5);
3504 v_sql VARCHAR2(4000);
3505 v_tot_segment NUMBER;
3506 v_divisor NUMBER := 0.000001; --bug 2393972
3507 v_success BOOLEAN;
3508
3509 CURSOR percent_info IS
3510 SELECT type,att_order FROM qrm_analysis_atts
3511 WHERE analysis_name=p_name AND percentage='Y' AND history_flag='S';
3512 CURSOR get_col_tot_seq_no IS
3513 SELECT seq_no FROM qrm_saved_analyses_col
3514 WHERE analysis_name=p_name AND type=v_level;
3515 CURSOR col_100 IS
3516 SELECT seq_no,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,m13,m14,m15,m16,m17,
3517 m18,m19,m20,m21,m22,m23,m24,m25,m26,m27,m28,m29,m30,m31,m32,m33,m34,
3518 m35,m36,m37,m38,m39,m40,m41,m42,m43,m44,m45,m46,m47,m48,m49,m50,m51,
3519 m52,m53,m54,m55,m56,m57,m58,m59,m60,m61,m62,m63,m64,m65,m66,m67,m68,
3520 m69,m70,m71,m72,m73,m74,m75,m76,m77,m78,m79,m80,m81,m82,m83,m84,m85,
3521 m86,m87,m88,m89,m90,m91,m92,m93,m94,m95,m96,m97,m98,m99,m100
3522 FROM qrm_saved_analyses_row
3523 WHERE analysis_name=p_name AND type=v_level ORDER BY seq_no;
3524 BEGIN
3525
3526 IF (g_proc_level>=g_debug_level) THEN
3527 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_PERCENT');
3528 END IF;
3529
3530 OPEN percent_info;
3531 FETCH percent_info INTO v_type,v_level;
3532 CLOSE percent_info;
3533 IF (g_proc_level>=g_debug_level) THEN
3534 xtr_risk_debug_pkg.dlog('v_type:v_level',v_type||':'||v_level);
3535 END IF;
3536
3537 v_success := translate_to_usd (p_name,
3538 p_ref_date,
3539 p_md_set_code,
3540 v_level);
3541
3542 --percentage is based on the row aggregates
3543 IF v_type='R' THEN
3544
3545 --prep cursor that contains total at the specified level
3546 v_tot.EXTEND(100);
3547
3548 --bug 2393972
3549 FOR i in 1..100 LOOP
3550 IF (v_tot(i)=0) THEN
3551 v_tot(i):=v_divisor;
3552 END IF;
3553 END LOOP;
3554
3555 OPEN col_100;
3556 LOOP
3557 --fetch the specified level total row
3558 FETCH col_100 INTO
3559 v_row,v_tot(1),v_tot(2),v_tot(3),v_tot(4),v_tot(5),
3560 v_tot(6),v_tot(7),v_tot(8),v_tot(9),v_tot(10),v_tot(11),
3561 v_tot(12),v_tot(13),v_tot(14),v_tot(15),v_tot(16),v_tot(17),
3562 v_tot(18),v_tot(19),v_tot(20),
3563 v_tot(21),v_tot(22),v_tot(23),v_tot(24),v_tot(25),
3564 v_tot(26),v_tot(27),v_tot(28),v_tot(29),v_tot(30),v_tot(31),
3565 v_tot(32),v_tot(33),v_tot(34),v_tot(35),v_tot(36),v_tot(37),
3566 v_tot(38),v_tot(39),v_tot(40),
3567 v_tot(41),v_tot(42),v_tot(43),v_tot(44),v_tot(45),
3568 v_tot(46),v_tot(47),v_tot(48),v_tot(49),v_tot(50),
3569 v_tot(51),v_tot(52),v_tot(53),v_tot(54),v_tot(55),
3570 v_tot(56),v_tot(57),v_tot(58),v_tot(59),v_tot(60),v_tot(61),
3571 v_tot(62),v_tot(63),v_tot(64),v_tot(65),v_tot(66),v_tot(67),
3572 v_tot(68),v_tot(69),v_tot(70),
3573 v_tot(71),v_tot(72),v_tot(73),v_tot(74),v_tot(75),
3574 v_tot(76),v_tot(77),v_tot(78),v_tot(79),v_tot(80),v_tot(81),
3575 v_tot(82),v_tot(83),v_tot(84),v_tot(85),v_tot(86),v_tot(87),
3576 v_tot(88),v_tot(89),v_tot(90),
3577 v_tot(91),v_tot(92),v_tot(93),v_tot(94),v_tot(95),
3578 v_tot(96),v_tot(97),v_tot(98),v_tot(99),v_tot(100);
3579 --calculating the % while updating the rows in between
3580 UPDATE qrm_saved_analyses_row
3581 SET p1=100*p1/v_tot(1),p2=100*p2/v_tot(2),p3=100*p3/v_tot(3),
3582 p4=100*p4/v_tot(4),p5=100*p5/v_tot(5),p6=100*p6/v_tot(6),
3583 p7=100*p7/v_tot(7),p8=100*p8/v_tot(8),p9=100*p9/v_tot(9),
3584 p10=100*p10/v_tot(10),p11=100*p11/v_tot(11),
3585 p12=100*p12/v_tot(12),
3586 p13=100*p13/v_tot(13),p14=100*p14/v_tot(14),
3587 p15=100*p15/v_tot(15),
3588 p16=100*p16/v_tot(16),p17=100*p17/v_tot(17),
3589 p18=100*p18/v_tot(18),
3590 p19=100*p19/v_tot(19),p20=100*p20/v_tot(20),
3591 p21=100*p21/v_tot(21),p22=100*p22/v_tot(22),
3592 p23=100*p23/v_tot(23),
3593 p24=100*p24/v_tot(24),p25=100*p25/v_tot(25),
3594 p26=100*p26/v_tot(26),
3595 p27=100*p27/v_tot(27),p28=100*p28/v_tot(28),
3596 p29=100*p29/v_tot(29),
3597 p30=100*p30/v_tot(30),p31=100*p31/v_tot(31),
3598 p32=100*p32/v_tot(32),
3599 p33=100*p33/v_tot(33),p34=100*p34/v_tot(34),
3600 p35=100*p35/v_tot(35),
3601 p36=100*p36/v_tot(36),p37=100*p37/v_tot(37),
3602 p38=100*p38/v_tot(38),
3603 p39=100*p39/v_tot(39),p40=100*p40/v_tot(40),
3604 p41=100*p41/v_tot(41),p42=100*p42/v_tot(42),
3605 p43=100*p43/v_tot(43),
3606 p44=100*p44/v_tot(44),p45=100*p45/v_tot(45),
3607 p46=100*p46/v_tot(46),
3608 p47=100*p47/v_tot(47),p48=100*p48/v_tot(48),
3609 p49=100*p49/v_tot(49),p50=100*p50/v_tot(50),
3610 p51=100*p51/v_tot(51),p52=100*p52/v_tot(52),
3611 p53=100*p53/v_tot(53),
3612 p54=100*p54/v_tot(54),p55=100*p55/v_tot(55),
3613 p56=100*p56/v_tot(56),
3614 p57=100*p57/v_tot(57),p58=100*p58/v_tot(58),
3615 p59=100*p59/v_tot(59),
3616 p60=100*p60/v_tot(60),p61=100*p61/v_tot(61),
3617 p62=100*p62/v_tot(62),
3618 p63=100*p63/v_tot(63),p64=100*p64/v_tot(64),
3619 p65=100*p65/v_tot(65),
3620 p66=100*p66/v_tot(66),p67=100*p67/v_tot(67),
3621 p68=100*p68/v_tot(68),
3622 p69=100*p69/v_tot(69),p70=100*p70/v_tot(70),
3623 p71=100*p71/v_tot(71),p72=100*p72/v_tot(72),
3624 p73=100*p73/v_tot(73),
3625 p74=100*p74/v_tot(74),p75=100*p75/v_tot(75),
3626 p76=100*p76/v_tot(76),
3627 p77=100*p77/v_tot(77),p78=100*p78/v_tot(78),
3628 p79=100*p79/v_tot(79),
3629 p80=100*p80/v_tot(80),p81=100*p81/v_tot(81),
3630 p82=100*p82/v_tot(82),
3631 p83=100*p83/v_tot(83),p84=100*p84/v_tot(84),
3632 p85=100*p85/v_tot(85),
3633 p86=100*p86/v_tot(86),p87=100*p87/v_tot(87),
3634 p88=100*p88/v_tot(88),
3635 p89=100*p89/v_tot(89),p90=100*p90/v_tot(90),
3636 p91=100*p91/v_tot(91),p92=100*p92/v_tot(92),
3637 p93=100*p93/v_tot(93),
3638 p94=100*p94/v_tot(94),p95=100*p95/v_tot(95),
3639 p96=100*p96/v_tot(96),
3640 p97=100*p97/v_tot(97),p98=100*p98/v_tot(98),
3641 p99=100*p99/v_tot(99),p100=100*p100/v_tot(100),
3642 last_updated_by=FND_GLOBAL.user_id,
3643 last_update_date=p_ref_date,
3644 last_update_login=FND_GLOBAL.login_id
3645 WHERE analysis_name=p_name AND seq_no>=v_previous_count
3646 AND seq_no<=v_row
3647 AND (type=-1 OR type>=v_level);
3648
3649 IF (g_event_level>=g_debug_level) THEN --bug 3236479
3650 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_ROW.P1..P100 Row Level Agg',
3651 'QRM_PA_AGGREGATION_P.UPDATE_PERCENT',g_event_level);
3652 END IF;
3653
3654 v_previous_count := v_row+1;
3655 EXIT WHEN col_100%NOTFOUND;
3656 END LOOP;
3657 CLOSE col_100;
3658
3659 --update the column info, first null all the COL_PERCENT_LEVEL
3660 UPDATE qrm_saved_analyses_col
3661 SET col_percent_level=NULL, last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
3662 WHERE analysis_name=p_name;
3663 --left null total columns,so it won't be displayed
3664 UPDATE qrm_saved_analyses_col
3665 SET col_percent_level=v_level, last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
3666 WHERE analysis_name=p_name AND type=-1;
3667
3668 IF (g_event_level>=g_debug_level) THEN --bug 3236479
3669 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_COL.COL_PERCENT_LEVEL to null then '||v_level,
3670 'QRM_PA_AGGREGATION_P.UPDATE_PERCENT',g_event_level);
3671 END IF;
3672
3673 --percentage is based on the col aggregates
3674 ELSIF v_type='C' THEN
3675 --get all the specified total column index
3676 v_tot := NULL;
3677 IF (g_proc_level>=g_debug_level) THEN
3678 xtr_risk_debug_pkg.dlog('Before FETCH v_tot');
3679 END IF;
3680 OPEN get_col_tot_seq_no;
3681 FETCH get_col_tot_seq_no BULK COLLECT INTO v_tot;
3682 CLOSE get_col_tot_seq_no;
3683 IF (g_proc_level>=g_debug_level) THEN
3684 xtr_risk_debug_pkg.dlog('After FETCH v_tot');
3685 END IF;
3686 v_previous_count := p_row_agg_no;
3690 xtr_risk_debug_pkg.dlog('v_previous_count',v_previous_count);
3687 IF (g_proc_level>=g_debug_level) THEN
3688 xtr_risk_debug_pkg.dlog('v_tot.COUNT',v_tot.COUNT);
3689 xtr_risk_debug_pkg.dlog('v_tot.LAST',v_tot.LAST);
3691 END IF;
3692 FOR i IN 1..v_tot.COUNT LOOP
3693 --
3694 --Have to do dynamic sql bec. we have to divide per block of col
3695 --Trying to get all column between the total index
3696 --
3697 IF (g_proc_level>=g_debug_level) THEN
3698 xtr_risk_debug_pkg.dlog('v_tot(i)',i||':'||v_tot(i));
3699 END IF;
3700 v_tot_segment := v_tot(i)-v_previous_count;
3701 FOR j IN 1..v_tot_segment LOOP
3702 --IF j<v_tot(i)-v_previous_count THEN
3703 -- v_sql := v_sql||'p'||j||'=100*m'||j||'/m'||v_tot_segment||',';
3704 --ELSE
3705 -- v_sql := v_sql||'p'||j||'=100*m'||j||'/m'||v_tot_segment;
3706 --END IF;
3707
3708 --bug 2393972
3709 IF j<v_tot(i)-v_previous_count THEN
3710 v_sql := v_sql||'p'||j||'=100*p'||j||'/DECODE(p'||v_tot_segment||',0,TO_NUMBER('''||v_divisor||'''),p'||v_tot_segment||'),';
3711 ELSE
3712 v_sql := v_sql||'p'||j||'=100*p'||j||'/DECODE(p'||v_tot_segment||',0,TO_NUMBER('''||v_divisor||'''),p'||v_tot_segment||')';
3713 END IF;
3714
3715 END LOOP;
3716 v_sql:='UPDATE qrm_saved_analyses_row SET '||v_sql||' , last_updated_by=FND_GLOBAL.user_id, last_update_date=:p_ref_date, last_update_login=FND_GLOBAL.login_id WHERE analysis_name=:p_name';
3717
3718 IF (g_proc_level>=g_debug_level) THEN
3719 xtr_risk_debug_pkg.dlog('v_sql',v_sql);
3720 END IF;
3721 EXECUTE IMMEDIATE v_sql USING p_ref_date,p_name;
3722 v_previous_count := v_tot(i)+1;
3723 END LOOP;
3724 IF (g_proc_level>=g_debug_level) THEN
3725 xtr_risk_debug_pkg.dlog('After EXECUTE IMMEDIATE');
3726 END IF;
3727 --update the column info, first null all the COL_PERCENT_LEVEL
3728 UPDATE qrm_saved_analyses_col
3729 SET col_percent_level=NULL, last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
3730 WHERE analysis_name=p_name;
3731 --left null total columns,so it won't be displayed
3732 UPDATE qrm_saved_analyses_col
3733 SET col_percent_level=-v_level, last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
3734 WHERE analysis_name=p_name AND (type=-1 OR type>=v_level);
3735
3736 IF (g_event_level>=g_debug_level) THEN --bug 3236479
3737 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_COL.COL_PERCENT_LEVEL to null then '||v_level,
3738 'QRM_PA_AGGREGATION_P.UPDATE_PERCENT',g_event_level);
3739 END IF;
3740
3741 ELSE
3742 --no percentage calculation is necessary
3743 IF (g_proc_level>=g_debug_level) THEN
3744 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_PERCENT');--bug 3236479
3745 END IF;
3746 RETURN TRUE;
3747 END IF;
3748
3749 COMMIT;
3750 IF (g_proc_level>=g_debug_level) THEN
3751 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_PERCENT');-- bug 3236479
3752 END IF;
3753 RETURN TRUE;
3754 EXCEPTION
3755 WHEN OTHERS THEN
3756
3757 IF (g_error_level>=g_debug_level) THEN
3758 xtr_risk_debug_pkg.dlog('EXCEPTION','OTHERS',
3759 'QRM_PA_AGGREGATION_P.UPDATE_PERCENT',g_error_level);--bug 3236479
3760 END IF;
3761
3762 RETURN FALSE;
3763 END update_percent;
3764
3765
3766
3767 /***************************************************************
3768 This function updates the label of the DEAL_TYPE and
3769 DEAL_SUBTYPE aggregate attributes with the user defined ones
3770 from table qrm_saved_analyses_col.
3771 The label of table qrm_saved_analyses_row will be updated at run
3772 time in OA.
3773
3774 DEAL_TYPE -> xtr_deal_types.user_deal_type
3775
3776 DEAL_SUBTYPE -> xtr_deal_subtypes.user_deal_subtype
3777 is taken care in the dynamic cursor when aggregation is done.
3778 ***************************************************************/
3779 FUNCTION update_label(p_name VARCHAR2,
3780 p_agg IN OUT NOCOPY SYSTEM.QRM_VARCHAR240_TABLE,
3781 p_col_order IN OUT NOCOPY XTR_MD_NUM_TABLE,
3782 p_att_type IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,
3783 p_ref_date DATE)
3784 RETURN BOOLEAN IS
3785 v_col_name VARCHAR2(50);
3786 BEGIN
3787 IF (g_proc_level>=g_debug_level) THEN
3788 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_LABEL');
3789 END IF;
3790
3791 --loop through all the aggregate to see whether DEAL_TYPE is there.
3792 FOR i IN 1..p_agg.COUNT LOOP
3793 IF p_agg(i)='DEAL_TYPE' AND p_att_type(i)='C' THEN
3794 --update the column header to reflect user defined DEAL_TYPE
3795 UPDATE qrm_saved_analyses_col
3796 SET a1=(SELECT DISTINCT user_deal_type FROM xtr_deal_types WHERE deal_type=a1), last_updated_by=FND_GLOBAL.user_id, last_update_date=p_ref_date, last_update_login=FND_GLOBAL.login_id
3797 WHERE analysis_name=p_name AND type=-1;
3798 COMMIT;
3799
3800 IF (g_event_level>=g_debug_level) THEN --bug 3236479
3801 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SET_ANALYSES_COL.A1 to user_deal_type',
3802 'QRM_PA_AGGREGATION_P.UPDATE_LABEL',g_event_level);
3803 END IF;
3804
3805 EXIT;
3806 END IF;
3807 END LOOP;
3808
3809 IF (g_proc_level>=g_debug_level) THEN
3810 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_LABEL');
3811 END IF;
3812 RETURN TRUE;
3813 EXCEPTION
3814 WHEN OTHERS THEN
3818 RETURN FALSE;
3815 IF (g_error_level>=g_debug_level) THEN
3816 xtr_risk_debug_pkg.dlog('EXCEPTION','OTHERS','QRM_PA_AGGREGATION_P.UPDATE_LABEL',g_error_level);-- bug 3236479
3817 END IF;
3819 END update_label;
3820
3821
3822
3823 /***************************************************************
3824 This procedure calculate the Totals for Table style using the
3825 currency conversion logic. Moreover, the underlying currency
3826 of the Totals will be returned together with the calculated totals.
3827 Also calculate the currency multipler (FX Rate) for reporting
3828 currency as needed.
3829 IMPORTANT:
3830 If an attribute cannot be averaged/totaled (sensitivity with
3831 > 1 underlying curr) then the p_att_name(i) will be NULLED.
3832 ***************************************************************/
3833 PROCEDURE calc_table_total_and_fxrate(p_name VARCHAR2,--1
3834 p_calc_total_ind VARCHAR2,--2: 'Y'es or 'N'o
3835 p_curr_reporting VARCHAR2,--3
3836 p_currency_source VARCHAR2,--4
3837 p_last_run_date DATE,--5
3838 p_md_set_code VARCHAR2,--6
3839 p_dirty VARCHAR2,--7
3840 p_end_date_fix DATE,--8
3841 p_tot_avg SYSTEM.QRM_VARCHAR_TABLE,--9
3842 p_ccy_multiplier OUT NOCOPY NUMBER,--10
3843 p_att_name IN OUT NOCOPY SYSTEM.QRM_VARCHAR240_TABLE,--11
3844 p_total OUT NOCOPY XTR_MD_NUM_TABLE,--12
3845 p_table_col_curr OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE,--13
3846 p_sensitivity SYSTEM.QRM_VARCHAR_TABLE, --14
3847 p_analysis_type VARCHAR2, --15
3848 p_business_week VARCHAR2,--16
3849 p_amount SYSTEM.QRM_VARCHAR_TABLE)--17
3850 IS
3851
3852 v_ccy_suffix VARCHAR2(20);
3853 v_underlying_ccy VARCHAR2(20);
3854 v_origin SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
3855 v_att_type SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
3856 v_nom SYSTEM.QRM_VARCHAR240_TABLE := SYSTEM.QRM_VARCHAR240_TABLE();
3857 v_denom SYSTEM.QRM_VARCHAR240_TABLE := SYSTEM.QRM_VARCHAR240_TABLE();
3858 v_num_denom_origin SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
3859 v_sql VARCHAR2(4000);
3860 i NUMBER(5);
3861 v_cursor INTEGER;
3862 v_rows_processed INTEGER;
3863 v_tb_calc_used BOOLEAN;
3864 v_temp VARCHAR2(240);
3865 v_total_temp xtr_md_num_table := xtr_md_num_table();
3866
3867 --dummy variables required only for other styles
3868 v_tb_calc_used_col BOOLEAN;
3869 v_ccy_aggregate SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
3870 v_type SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
3871 v_order xtr_md_num_table := xtr_md_num_table();
3872 v_ccy_agg_flag NUMBER;
3873 v_ccy_case_flag NUMBER;
3874 v_ccy_agg_level NUMBER;
3875 v_measure_no NUMBER(5);
3876 v_row_agg_no NUMBER(5);
3877 v_dummy VARCHAR2(1);
3878 v_sql_col VARCHAR2(1);
3879 v_agg_col_curr SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
3880
3881 CURSOR get_lookup_prop IS
3882 SELECT origin,attribute_type,numerator,denominator,num_denom_origin
3883 FROM qrm_ana_atts_lookups
3884 WHERE attribute_name=v_temp;
3885
3886 BEGIN
3887 IF (g_proc_level>=g_debug_level) THEN
3888 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.CALC_TABLE_TOTAL');--bug 3236479
3889 END IF;
3890
3891 --initialize fnd_msg_pub if it has not been intialized
3892 IF fnd_msg_pub.count_msg>0 THEN
3893 fnd_msg_pub.Initialize;
3894 END IF;
3895
3896 --FHU will convert the relative date and saved them into the fix date col
3897
3898 --see whether we need to do total or just to get multiplier
3899 IF p_calc_total_ind='Y' THEN
3900 --first extend v_table_col_curr
3901 IF (g_proc_level>=g_debug_level) THEN
3902 xtr_risk_debug_pkg.dlog('p_att_name.COUNT,p_att_name.LAST',p_att_name.COUNT||','||p_att_name.LAST);
3903 END IF;
3904 p_table_col_curr := SYSTEM.QRM_VARCHAR_table();
3905 p_table_col_curr.EXTEND(p_att_name.COUNT);
3906 v_origin.EXTEND(p_att_name.COUNT);
3907 v_att_type.EXTEND(p_att_name.COUNT);
3908 v_nom.EXTEND(p_att_name.COUNT);
3909 v_denom.EXTEND(p_att_name.COUNT);
3910 v_num_denom_origin.EXTEND(p_att_name.COUNT);
3911 p_total := xtr_md_num_table();
3912 p_total.EXTEND(p_att_name.COUNT);
3913
3914 --xtr_risk_debug_pkg.dlog('p_table_col_curr.COUNT',p_table_col_curr.COUNT);
3915 --get the underlying currency for each column and modify the the
3916 --attribute name with '_USD' and '_SOB'
3917 get_underlying_currency(p_name,p_last_run_date,'T',p_md_set_code,
3918 p_currency_source,p_curr_reporting,p_amount,
3919 p_att_name,v_ccy_aggregate,v_type,v_order,
3920 v_ccy_suffix,p_ccy_multiplier,v_ccy_agg_flag,
3921 v_underlying_ccy,v_ccy_case_flag,NULL,
3922 v_ccy_agg_level,p_table_col_curr,v_agg_col_curr,
3923 p_sensitivity);
3924 --xtr_risk_debug_pkg.dlog('p_table_col_curr.COUNT',p_table_col_curr.COUNT);
3925 IF (g_proc_level>=g_debug_level) THEN
3926 xtr_risk_debug_pkg.dlog('p_ccy_multiplier',p_ccy_multiplier);
3927 END IF;
3928 --make sure p_ccy_multiplier is not null when necessary otherwise throws error
3929 FOR i IN 1..p_table_col_curr.COUNT LOOP
3930 --xtr_risk_debug_pkg.dlog('p_table_col_curr(i)',i||':'||p_table_col_curr(i));
3931 IF p_table_col_curr(i) IS NULL THEN
3932 IF p_curr_reporting<>'USD' AND p_ccy_multiplier IS NULL THEN
3933 FND_MESSAGE.SET_NAME('QRM','QRM_CALC_NO_DEFAULT_SPOT_ERR');
3934 FND_MESSAGE.SET_TOKEN('CCY',p_curr_reporting);
3935 RAISE e_pagg_no_fxrate_found;
3936 END IF;
3937 END IF;
3938 END LOOP;
3939
3940 --get the property of the new name attributes
3941 FOR i IN 1..p_att_name.COUNT LOOP
3942 IF p_att_name(i) IS NOT NULL THEN
3943 v_temp := p_att_name(i);
3944 --xtr_risk_debug_pkg.dlog('v_temp',v_temp);
3945 OPEN get_lookup_prop;
3946 FETCH get_lookup_prop INTO v_origin(i),v_att_type(i),v_nom(i),
3947 v_denom(i),v_num_denom_origin(i);
3948 IF (g_proc_level>=g_debug_level) THEN
3949 xtr_risk_debug_pkg.dlog('v_origin,v_att_type,v_nom,v_denom',i||':'||v_origin(i)||','||v_att_type(i)||','||v_nom(i)||','||v_denom(i));
3950 END IF;
3951 CLOSE get_lookup_prop;
3952 END IF;
3953 END LOOP;
3954
3955 --create dynamic sql, first get the origin and alias
3956 create_cursor (p_name,'T',p_analysis_type,p_att_name,v_att_type,
3957 v_nom,v_denom,p_tot_avg,v_sql,v_sql_col,
3958 v_row_agg_no,v_measure_no,v_origin,v_tb_calc_used,
3959 v_tb_calc_used_col,
3960 FALSE,v_ccy_suffix,p_ccy_multiplier,
3961 p_amount,p_table_col_curr,v_num_denom_origin,
3962 p_curr_reporting,p_sensitivity);
3963
3964 IF (g_proc_level>=g_debug_level) THEN
3965 xtr_risk_debug_pkg.dlog('v_sql',v_sql);
3966 END IF;
3967 --open dynamic cursor
3968 v_cursor := dbms_sql.open_cursor;
3969 --Parse the results
3970 dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);
3971 dbms_sql.bind_variable(v_cursor,'analysis_name',p_name);
3972 --remember to add end_date as binding variable
3973 --for special cases: v_tb_calc_used=TRUE
3974 IF v_tb_calc_used THEN
3975 IF p_analysis_type='P' THEN
3976 dbms_sql.bind_variable(v_cursor,'end_date',p_end_date_fix);
3977 ELSE
3978 dbms_sql.bind_variable(v_cursor,'ref_date',TRUNC(p_last_run_date));
3979 END IF;
3980 END IF;
3981 --xtr_risk_debug_pkg.dlog('v_measure_no',v_measure_no);
3982 --the number of columns is twice as many in the SQL bec. of nom and denom
3983 v_total_temp.EXTEND(p_att_name.COUNT*2);
3984 FOR i IN 1..v_total_temp.COUNT LOOP
3985 dbms_sql.define_column(v_cursor,i,v_total_temp(i));
3986 END LOOP;
3987 --execute
3988 v_rows_processed := dbms_sql.execute(v_cursor);
3989 --get the value
3990 LOOP
3991 IF dbms_sql.fetch_rows(v_cursor)>0 THEN
3992 FOR i IN 1..v_total_temp.COUNT LOOP
3993 dbms_sql.column_value(v_cursor,i,v_total_temp(i));
3994 END LOOP;
3995 ELSE
3996 EXIT;
3997 END IF;
3998 END LOOP;
3999 --transfer the value from v_total_temp to p_total
4000 FOR i IN 1..p_total.COUNT LOOP
4001 IF p_att_name(i) IS NOT NULL THEN
4002 IF p_tot_avg(i)='T' THEN --do SUM operation
4003 p_total(i) := v_total_temp(i*2-1);
4004 ELSE --do AVERAGE operation
4005 p_total(i) := v_total_temp(i*2-1)/v_total_temp(i*2);
4006 END IF;
4007 END IF;
4008 IF (g_proc_level>=g_debug_level) THEN
4009 xtr_risk_debug_pkg.dlog('p_total(i)',i||':'||p_att_name(i)||','||p_total(i)||','||p_table_col_curr(i));
4010 END IF;
4011 END LOOP;
4012 --close dynamic cursor
4013 dbms_sql.close_cursor(v_cursor);
4014
4015 ELSE --just need to call MD API to get the FX Rate/Ccy multiplier
4016 --Find multiplier
4017 p_ccy_multiplier := get_fx_rate(p_md_set_code,
4018 p_last_run_date,
4019 'USD',
4020 p_curr_reporting,
4021 'M');
4022
4023 --check whether p_ccy_multiplier is not null when needed
4024 IF p_ccy_multiplier IS NULL THEN
4025 FND_MESSAGE.SET_NAME('QRM','QRM_CALC_NO_DEFAULT_SPOT_ERR');
4026 FND_MESSAGE.SET_TOKEN('CCY',p_curr_reporting);
4027 RAISE e_pagg_no_fxrate_found;
4028 END IF;
4029 END IF;
4030
4031 --update dirty flag if semi-dirty
4032 IF p_dirty IN ('C','S') THEN
4033 UPDATE qrm_analysis_settings SET dirty='N' WHERE analysis_name=p_name;
4034 END IF;
4035
4036 IF (g_event_level>=g_debug_level) THEN
4037 xtr_risk_debug_pkg.dlog('DML','UPDATE QRM_ANALYSIS_SETTINGS.DIRTY=N',
4038 'QRM_PA_AGGREGATION_P.CALC_TABLE_TOTAL',g_event_level);--bug 3236479
4039 END IF;
4040
4041 IF (g_proc_level>=g_debug_level) THEN
4042 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.CALC_TABLE_TOTAL');--bug 3236479
4043 END IF;
4044
4045 EXCEPTION
4046 WHEN e_pagg_no_fxrate_found THEN
4047 fnd_msg_pub.add;
4048 IF (g_error_level>=g_debug_level) THEN
4049 xtr_risk_debug_pkg.dlog('EXCEPTION','e_pagg_no_fxrate_found','QRM_PA_AGGREGATION_P.CALC_TABLE_TOTAL',g_error_level);--bug 3236479
4050 END IF;
4051 WHEN OTHERS THEN
4052 IF (g_error_level>=g_debug_level) THEN
4053 xtr_risk_debug_pkg.dlog('EXCEPTION','OTHERS','QRM_PA_AGGREGATION_P.CALC_TABLE_TOTAL',g_error_level);--bug 3236479
4054 END IF;
4055 END calc_table_total_and_fxrate;
4056
4057
4058
4059 /***************************************************************
4060 This procedure calculate the Totals for Table style using the
4061 currency conversion logic. Moreover, the underlying currency
4062 of the Totals will be returned together with the calculated totals.
4063 Also calculate the currency multipler (FX Rate) for reporting
4064 currency as needed.
4065 ***************************************************************/
4066 FUNCTION update_currency_columns(p_indicator NUMBER,
4067 p_name VARCHAR2,
4068 p_ref_date DATE,
4069 v_curr IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE)
4070 RETURN BOOLEAN IS
4071 BEGIN
4072 IF (g_proc_level>=g_debug_level) THEN
4073 xtr_risk_debug_pkg.dpush('QRM_PA_AGGREGATION_P.UPDATE_CURR_COL');
4074 END IF;
4075 IF p_indicator=1 THEN
4076 UPDATE qrm_saved_analyses_row
4077 SET curr1=v_curr(1),curr2=v_curr(2),curr3=v_curr(3),
4078 curr4=v_curr(4),curr5=v_curr(5),curr6=v_curr(6),
4079 curr7=v_curr(7),curr8=v_curr(8),curr9=v_curr(9),
4080 curr10=v_curr(10),curr11=v_curr(11),curr12=v_curr(12),
4084 curr21=v_curr(21),curr22=v_curr(22),curr23=v_curr(23),
4081 curr13=v_curr(13),curr14=v_curr(14),curr15=v_curr(15),
4082 curr16=v_curr(16),curr17=v_curr(17),curr18=v_curr(18),
4083 curr19=v_curr(19),curr20=v_curr(20),
4085 curr24=v_curr(24),curr25=v_curr(25),curr26=v_curr(26),
4086 curr27=v_curr(27),curr28=v_curr(28),curr29=v_curr(29),
4087 curr30=v_curr(30),curr31=v_curr(31),curr32=v_curr(32),
4088 curr33=v_curr(33),curr34=v_curr(34),curr35=v_curr(35),
4089 curr36=v_curr(36),curr37=v_curr(37),curr38=v_curr(38),
4090 curr39=v_curr(39),curr40=v_curr(40),
4091 curr41=v_curr(41),curr42=v_curr(42),curr43=v_curr(43),
4092 curr44=v_curr(44),curr45=v_curr(45),curr46=v_curr(46),
4093 curr47=v_curr(47),curr48=v_curr(48),
4094 curr49=v_curr(49),curr50=v_curr(50),
4095 curr51=v_curr(51),curr52=v_curr(52),curr53=v_curr(53),
4096 curr54=v_curr(54),curr55=v_curr(55),curr56=v_curr(56),
4097 curr57=v_curr(57),curr58=v_curr(58),curr59=v_curr(59),
4098 curr60=v_curr(60),curr61=v_curr(61),curr62=v_curr(62),
4099 curr63=v_curr(63),curr64=v_curr(64),curr65=v_curr(65),
4100 curr66=v_curr(66),curr67=v_curr(67),curr68=v_curr(68),
4101 curr69=v_curr(69),curr70=v_curr(70),
4102 curr71=v_curr(71),curr72=v_curr(72),curr73=v_curr(73),
4103 curr74=v_curr(74),curr75=v_curr(75),curr76=v_curr(76),
4104 curr77=v_curr(77),curr78=v_curr(78),curr79=v_curr(79),
4105 curr80=v_curr(80),curr81=v_curr(81),curr82=v_curr(82),
4106 curr83=v_curr(83),curr84=v_curr(84),curr85=v_curr(85),
4107 curr86=v_curr(86),curr87=v_curr(87),curr88=v_curr(88),
4108 curr89=v_curr(89),curr90=v_curr(90),
4109 curr91=v_curr(91),curr92=v_curr(92),curr93=v_curr(93),
4110 curr94=v_curr(94),curr95=v_curr(95),curr96=v_curr(96),
4111 curr97=v_curr(97),curr98=v_curr(98),
4112 curr99=v_curr(99),curr100=v_curr(100),
4113 last_updated_by=FND_GLOBAL.user_id,
4114 last_update_date=p_ref_date,
4115 last_update_login=FND_GLOBAL.login_id
4116 WHERE analysis_name=p_name;
4117
4118 IF (g_event_level>=g_debug_level) THEN --bug 3236479
4119 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SAVED_ANALYSES_ROW.CURR1..100 with p_indicator=1',
4120 'QRM_PA_AGGREGATION_P.UPDATE_CURRENCY_COLUMNS',g_event_level);
4121 END IF;
4122
4123 ELSE --p_indicator=2 where curr1 has the CCY for the row
4124 UPDATE qrm_saved_analyses_row
4125 SET curr2=curr1,curr3=curr1,
4126 curr4=curr1,curr5=curr1,curr6=curr1,
4127 curr7=curr1,curr8=curr1,curr9=curr1,
4128 curr10=curr1,curr11=curr1,curr12=curr1,
4129 curr13=curr1,curr14=curr1,curr15=curr1,
4130 curr16=curr1,curr17=curr1,curr18=curr1,
4131 curr19=curr1,curr20=curr1,
4132 curr21=curr1,curr22=curr1,curr23=curr1,
4133 curr24=curr1,curr25=curr1,curr26=curr1,
4134 curr27=curr1,curr28=curr1,curr29=curr1,
4135 curr30=curr1,curr31=curr1,curr32=curr1,
4136 curr33=curr1,curr34=curr1,curr35=curr1,
4137 curr36=curr1,curr37=curr1,curr38=curr1,
4138 curr39=curr1,curr40=curr1,
4139 curr41=curr1,curr42=curr1,curr43=curr1,
4140 curr44=curr1,curr45=curr1,curr46=curr1,
4141 curr47=curr1,curr48=curr1,
4142 curr49=curr1,curr50=curr1,
4143 curr51=curr1,curr52=curr1,curr53=curr1,
4144 curr54=curr1,curr55=curr1,curr56=curr1,
4145 curr57=curr1,curr58=curr1,curr59=curr1,
4146 curr60=curr1,curr61=curr1,curr62=curr1,
4147 curr63=curr1,curr64=curr1,curr65=curr1,
4148 curr66=curr1,curr67=curr1,curr68=curr1,
4149 curr69=curr1,curr70=curr1,
4150 curr71=curr1,curr72=curr1,curr73=curr1,
4151 curr74=curr1,curr75=curr1,curr76=curr1,
4152 curr77=curr1,curr78=curr1,curr79=curr1,
4153 curr80=curr1,curr81=curr1,curr82=curr1,
4154 curr83=curr1,curr84=curr1,curr85=curr1,
4155 curr86=curr1,curr87=curr1,curr88=curr1,
4156 curr89=curr1,curr90=curr1,
4157 curr91=curr1,curr92=curr1,curr93=curr1,
4158 curr94=curr1,curr95=curr1,curr96=curr1,
4159 curr97=curr1,curr98=curr1,
4160 curr99=curr1,curr100=curr1,
4161 last_updated_by=FND_GLOBAL.user_id,
4162 last_update_date=p_ref_date,
4163 last_update_login=FND_GLOBAL.login_id
4164 WHERE analysis_name=p_name;
4165
4166 IF (g_event_level>=g_debug_level) THEN --bug 3236479
4167 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SAVED_ANALYSES_ROW.CURR1..100 with p_indicator='||p_indicator,
4168 'QRM_PA_AGGREGATION_P.UPDATE_CURRENCY_COLUMNS',g_event_level);
4169 END IF;
4170
4171 END IF;
4172 IF (g_proc_level>=g_debug_level) THEN
4173 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_CURR_COL');
4174 END IF;
4175 RETURN TRUE;
4176 EXCEPTION
4177 WHEN OTHERS THEN
4178 IF (g_error_level>=g_debug_level) THEN
4179 xtr_risk_debug_pkg.dlog('EXCEPTION','OTHERS','QRM_PA_AGGREGATION_P.UPDATE_CURR_COL',g_error_level);--bug 3236479
4180 END IF;
4181 RETURN FALSE;
4182 END update_currency_columns;
4183
4184
4185
4186 /***************************************************************
4187 This procedure calculate the Totals for Table style using the
4188 currency conversion logic. Moreover, the underlying currency
4189 of the Totals will be returned together with the calculated totals.
4193 FUNCTION update_aggregate_curr(p_name VARCHAR2,
4190 Also calculate the currency multipler (FX Rate) for reporting
4191 currency as needed.
4192 ***************************************************************/
4194 p_ref_date DATE,
4195 p_ccy_case_flag NUMBER,
4196 p_ccy_agg_flag NUMBER,
4197 p_ccy_agg_level NUMBER,
4198 p_row_agg_no NUMBER,
4199 p_max_col_no NUMBER,
4200 p_underlying_ccy VARCHAR2,
4201 p_currency_source VARCHAR2,
4202 p_curr_reporting VARCHAR2,
4203 p_agg_col_curr IN OUT NOCOPY SYSTEM.QRM_VARCHAR_TABLE)
4204 RETURN BOOLEAN IS
4205
4206 v_curr SYSTEM.QRM_VARCHAR_table;
4207 i NUMBER(3);
4208 v_success BOOLEAN;
4209 v_sql VARCHAR2(255);
4210 v_curr_col_name_map SYSTEM.QRM_VARCHAR_table := SYSTEM.QRM_VARCHAR_table();
4211
4212 CURSOR get_column_header IS
4213 SELECT DECODE(type,-2,a1,-1,a1,tot_currency) FROM qrm_saved_analyses_col
4214 WHERE analysis_name=p_name AND seq_no>p_row_agg_no
4215 ORDER BY 1;
4216
4217 BEGIN
4218 IF (g_proc_level>=g_debug_level) THEN
4219 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_AGG_CURR');--bug 3236479
4220 END IF;
4221
4222 IF p_ccy_case_flag=1 THEN
4223 v_curr := SYSTEM.QRM_VARCHAR_table();
4224 v_curr.EXTEND(100);
4225 FOR i IN 1..v_curr.COUNT LOOP
4226 v_curr(i) := p_underlying_ccy;
4227 END LOOP;
4228 v_success := update_currency_columns(1,p_name,p_ref_date,v_curr);
4229 ELSIF p_ccy_case_flag=2 THEN
4230 v_curr := SYSTEM.QRM_VARCHAR_table();
4231 v_curr.EXTEND(100);
4232 --need to make it into array of size 100 bec. of the
4233 --update SQL
4234 FOR i IN 1..p_agg_col_curr.COUNT LOOP
4235 v_curr(i) := p_agg_col_curr(i);
4236 END LOOP;
4237 v_success := update_currency_columns(1,p_name,p_ref_date,v_curr);
4238 ELSIF p_ccy_case_flag=0 THEN
4239 IF p_ccy_agg_flag=1 THEN
4240 v_sql := 'UPDATE qrm_saved_analyses_row SET curr1=DECODE(type,-1,a'||p_ccy_agg_level||',tot_currency) WHERE analysis_name=:analysis_name';
4241 IF (g_proc_level>=g_debug_level) THEN
4242 xtr_risk_debug_pkg.dlog('v_sql',v_sql);
4243 END IF;
4244 EXECUTE IMMEDIATE v_sql USING p_name;
4245
4246 IF (g_event_level>=g_debug_level) THEN --bug 3236479
4247 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SAVED_ANALYSES_ROW.CURR1',
4248 'QRM_PA_AGGREGATION_P.UPDATE_AGGREGATE_CURR',g_event_level);
4249 END IF;
4250
4251 v_success := update_currency_columns(2,p_name,p_ref_date,v_curr);
4252 ELSIF p_ccy_agg_flag=2 THEN
4253 OPEN get_column_header;
4254 FETCH get_column_header BULK COLLECT INTO v_curr;
4255 CLOSE get_column_header;
4256 --need to make it into array of size 100 bec. of the
4257 --update SQL
4258 v_curr.EXTEND(100-p_max_col_no);
4259 v_success := update_currency_columns(1,p_name,p_ref_date,v_curr);
4260 ELSE --p_ccy_agg_flag=0
4261 v_curr := SYSTEM.QRM_VARCHAR_table();
4262 v_curr.EXTEND(100);
4263 FOR i IN 1..v_curr.COUNT LOOP
4264 v_curr(i) := p_curr_reporting;
4265 END LOOP;
4266 v_success := update_currency_columns(1,p_name,p_ref_date,v_curr);
4267 END IF;
4268 END IF;
4269
4270 --insert the currency col name (CURR1, CURR2, ...)
4271 v_curr_col_name_map.EXTEND(p_max_col_no+p_row_agg_no);
4272 FOR i IN p_row_agg_no+1..p_row_agg_no+p_max_col_no LOOP
4273 v_curr_col_name_map(i) := 'CURR'||i;
4274 UPDATE qrm_saved_analyses_col
4275 SET curr_col_name_map=v_curr_col_name_map(i)
4276 WHERE analysis_name=p_name AND seq_no=i;
4277 END LOOP;
4278
4279 IF (g_event_level>=g_debug_level) THEN --bug 3236479
4280 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SAVED_ANALYSES_COL.CURR_COL_NAME',
4281 'QRM_PA_AGGREGATION_P.UPDATE_AGGREGATE_CURR',g_event_level);
4282 END IF;
4283
4284 IF (g_proc_level>=g_debug_level) THEN
4285 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_AGG_CURR');-- bug 3236479
4286 END IF;
4287 RETURN TRUE;
4288
4289 EXCEPTION
4290 WHEN OTHERS THEN
4291 IF (g_error_level>=g_debug_level) THEN
4292 xtr_risk_debug_pkg.dlog('EXCEPTION','OTHERS',
4293 'QRM_PA_AGGREGATION_P.UPDATE_AGG_CURR',g_error_level);--bug 3236479
4294 END IF;
4295 RETURN FALSE;
4296 END update_aggregate_curr;
4297
4298
4299
4300 /***************************************************************
4301 This function should be called if TB Label in the Setup changed.
4302 i.e. The user prefer to show the user-defined labels in the
4303 cross-tab from showing the End Date.
4304 ***************************************************************/
4305 FUNCTION update_timebuckets_label(p_name VARCHAR2)
4306 RETURN BOOLEAN IS
4307
4308 v_tb_label VARCHAR2(1);
4309 i NUMBER(5);
4310 v_row_agg_no NUMBER(5);
4311 v_dummy NUMBER;
4312 CURSOR get_tb_label IS
4313 SELECT tb_label FROM qrm_analysis_settings
4314 WHERE analysis_name=p_name AND history_flag='S';
4315 CURSOR get_row_agg_no IS
4316 SELECT COUNT(*) FROM qrm_saved_analyses_col
4317 WHERE analysis_name=p_name AND type=-2;
4318 BEGIN
4319 IF (g_proc_level>=g_debug_level) THEN
4320 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_TB_LABEL');
4321 END IF;
4322
4323 OPEN get_tb_label;
4324 FETCH get_tb_label INTO v_tb_label;
4325 CLOSE get_tb_label;
4326 IF v_tb_label IS NULL THEN
4327 FND_MESSAGE.SET_NAME('QRM','QRM_ANA_NO_SETTING');
4328 FND_MESSAGE.SET_TOKEN('ANALYSIS_NAME',p_name);
4329 RAISE e_pagg_no_setting_found;
4330 END IF;
4331 OPEN get_row_agg_no;
4332 FETCH get_row_agg_no INTO v_row_agg_no;
4333 CLOSE get_row_agg_no;
4334
4335 IF v_tb_label='L' THEN
4336 --update the qrm_saved_analyses_col by using the tb label
4337 UPDATE qrm_saved_analyses_col SET a1=tb_label
4338 WHERE analysis_name=p_name AND seq_no>v_row_agg_no
4339 AND type<0;
4340 ELSE --v_tb_label='D'
4341 --update the qrm_saved_analyses_col by using the end date
4342 UPDATE qrm_saved_analyses_col SET a1=TO_CHAR(end_date)
4343 WHERE analysis_name=p_name AND seq_no>v_row_agg_no
4344 AND type<0;
4345 END IF;
4346 COMMIT;
4347
4348 IF (g_event_level>=g_debug_level) THEN --bug 3236479
4349 XTR_RISK_DEBUG_PKG.dlog('DML','Updated QRM_SAVED_ANALYSES_COL.A1 with v_tb_label='||v_tb_label,
4350 'QRM_PA_AGGREGATION_P.UPDATE_TIMEBUCKETS_LABELS',g_event_level);
4351 END IF;
4352
4353 IF (g_proc_level>=g_debug_level) THEN
4354 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_TB_LABEL');
4355 END IF;
4356 RETURN TRUE;
4357
4358 EXCEPTION
4359 WHEN e_pagg_no_setting_found THEN
4360 RAISE e_pagg_no_setting_found;
4361
4362 IF (g_error_level>=g_debug_level) THEN
4363 xtr_risk_debug_pkg.dlog('EXCEPTION','e_pagg_no_setting_found',
4364 'QRM_PA_AGGREGATION_P.UPDATE_TB_LABEL',g_error_level);--bug 3236479
4365 END IF;
4366
4367 WHEN OTHERS THEN
4368
4369 IF (g_error_level>=g_debug_level) THEN
4370 xtr_risk_debug_pkg.dlog('EXCEPTION','OTHERS',
4371 'QRM_PA_AGGREGATION_P.UPDATE_TB_LABEL',g_error_level);--bug 3236479
4372 END IF;
4373
4374 RETURN FALSE;
4375 END update_timebuckets_label;
4376
4377
4378
4379 /***************************************************************
4380 This function should be called from OA if the DIRTY indicator
4381 is semi-dirty that does not involved currency updates (S).
4382 ***************************************************************/
4383 FUNCTION update_semidirty(p_name VARCHAR2, p_ref_date DATE)
4384 RETURN VARCHAR2 IS
4385
4386 v_style VARCHAR2(1);
4387 v_success BOOLEAN := TRUE;
4388 CURSOR get_style IS
4389 SELECT style FROM qrm_analysis_settings
4390 WHERE analysis_name=p_name AND history_flag='S';
4391 BEGIN
4392 IF (g_proc_level>=g_debug_level) THEN
4393 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.UPDATE_SEMIDIRTY');
4394 END IF;
4395 --initialize fnd_msg_pub if it has not been intialized
4396 IF fnd_msg_pub.count_msg>0 THEN
4397 fnd_msg_pub.Initialize;
4398 END IF;
4399 --check whether analysis settings still there by looking whether the required
4400 --column value return IS NULL or not
4401 OPEN get_style;
4402 FETCH get_style INTO v_style;
4403 CLOSE get_style;
4404 IF v_style IS NULL THEN
4405 FND_MESSAGE.SET_NAME('QRM','QRM_ANA_NO_SETTING');
4406 FND_MESSAGE.SET_TOKEN('ANALYSIS_NAME',p_name);
4407 RAISE e_pagg_no_setting_found;
4408 END IF;
4409
4410 --update total
4411 v_success := update_total(p_name,p_ref_date);
4412 IF (g_proc_level>=g_debug_level) THEN
4413 xtr_risk_debug_pkg.dlog('update_semidirty: ' || 'v_successT',v_success);
4414 END IF;
4415 IF NOT v_success THEN
4416 RAISE e_pagg_update_total_fail;
4417 END IF;
4418 --update percent
4419 v_success := update_percent (p_name,p_ref_date);
4420 IF (g_proc_level>=g_debug_level) THEN
4421 xtr_risk_debug_pkg.dlog('update_semidirty: ' || 'v_success%',v_success);
4422 END IF;
4423 IF NOT v_success THEN
4424 RAISE e_pagg_update_percent_fail;
4425 END IF;
4426 --update timebuckets label
4427 IF v_style='X' THEN
4428 v_success := update_timebuckets_label(p_name);
4429 IF (g_proc_level>=g_debug_level) THEN
4430 xtr_risk_debug_pkg.dlog('update_semidirty: ' || 'v_successTBLabel',v_success);
4431 END IF;
4432 IF NOT v_success THEN
4433 RAISE e_pagg_update_tb_label_fail;
4434 END IF;
4435 END IF;
4436
4437 --update dirty flag
4438 UPDATE qrm_analysis_settings SET dirty='N' WHERE analysis_name=p_name;
4439 COMMIT;
4440 IF (g_proc_level>=g_debug_level) THEN
4441 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.UPDATE_SEMIDIRTY');
4442 END IF;
4443 RETURN 'T';
4444 EXCEPTION
4445 WHEN e_pagg_no_setting_found THEN
4446 fnd_msg_pub.add;
4447 --bug 3236479
4448 IF (g_proc_level>=g_ERROR_level) THEN
4449 XTR_RISK_DEBUG_PKG.dlog('EXCEPTION','QRM_PA_AGGREGATION_P.E_PAGG_NO_SETTING_FOUND',
4450 'QRM_PA_AGGREGATION_P.UPDATE_SEMIDIRTY',
4451 g_error_level);
4452 END IF;
4453 RETURN 'F';
4454 WHEN OTHERS THEN
4455
4456 IF (g_error_level>=g_debug_level) THEN
4457 xtr_risk_debug_pkg.dlog('EXCEPTION','OTHERS',
4458 'QRM_PA_AGGREGATION_P.UPDATE_SEMIDIRTY',g_error_level);--bug 3236479
4459 END IF;
4460
4461 RETURN 'F';
4462 END update_semidirty;
4463
4464
4465
4466 /***************************************************************
4467 This function find the FX Spot Rate. If there is MD_SET_CODE defined
4468 this will call xtr_market_data_p.get_md_from_set otherwise
4469 this will call qrm_calculators_p.get_rates_from_base
4470 ***************************************************************/
4471 FUNCTION get_fx_rate(p_md_set_code VARCHAR2,
4472 p_spot_date DATE,
4473 p_base_ccy VARCHAR2,
4474 p_contra_ccy VARCHAR2,
4475 p_side VARCHAR2)
4476 RETURN NUMBER IS
4477
4478 v_md_in xtr_market_data_p.md_from_set_in_rec_type;
4479 v_md_out xtr_market_data_p.md_from_set_out_rec_type;
4480 v_ccy_multiplier NUMBER;
4481 v_bid_rate_comm NUMBER;
4482 v_ask_rate_comm NUMBER;
4483 v_bid_rate_base NUMBER;
4484 v_ask_rate_base NUMBER;
4485 v_ccy VARCHAR2(15);
4486
4487 --use the same cursor as in XTR_MARKET_DATA_P.GET_MD_FROM_SET
4488 CURSOR get_fx_spot_rates IS
4489 SELECT usd_base_curr_bid_rate bid_rate,
4490 usd_base_curr_offer_rate ask_rate,
4491 1/usd_base_curr_offer_rate bid_rate_base,
4492 1/usd_base_curr_bid_rate ask_rate_base,
4493 currency
4494 FROM xtr_spot_rates
4495 WHERE (rate_date, currency) IN (SELECT MAX(rate_date), currency
4496 FROM xtr_spot_rates
4497 WHERE currency IN (p_base_ccy, p_contra_ccy)
4498 AND currency <> 'USD'
4499 AND trunc(rate_date) <= trunc(p_spot_date)
4500 GROUP BY currency);
4501
4502 BEGIN
4503 IF (g_proc_level>=g_debug_level) THEN
4504 xtr_risk_debug_pkg.dpush(null,'QRM_PA_AGGREGATION_P.GET_FX_RATE');
4505 END IF;
4506
4507 IF p_base_ccy=p_contra_ccy THEN
4508 v_ccy_multiplier := 1;
4509 ELSE
4510 IF p_md_set_code IS NOT NULL THEN
4511 v_md_in.p_md_set_code := p_md_set_code;
4512 v_md_in.p_source := 'C';
4513 v_md_in.p_indicator := 'S';
4514 v_md_in.p_spot_date := p_spot_date;
4515 v_md_in.p_ccy := p_base_ccy;
4516 v_md_in.p_contra_ccy := p_contra_ccy;
4517 v_md_in.p_side := p_side;
4518 xtr_market_data_p.get_md_from_set(v_md_in,v_md_out);
4519 --Round the FX Spot
4520 v_ccy_multiplier := v_md_out.p_md_out;
4521 ELSE
4522 OPEN get_fx_spot_rates;
4523 FETCH get_fx_spot_rates INTO v_bid_rate_comm, v_ask_rate_comm,
4524 v_bid_rate_base, v_ask_rate_base,
4525 v_ccy;
4526 CLOSE get_fx_spot_rates;
4527 IF v_bid_rate_comm IS NULL THEN
4528 FND_MESSAGE.SET_NAME('QRM','QRM_CALC_NO_DEFAULT_SPOT_ERR');
4529 FND_MESSAGE.SET_TOKEN('CCY',p_contra_ccy);
4530 RAISE e_pagg_no_fxrate_found;
4531 ELSE
4532 IF p_side IN ('A','ASK') THEN
4533 IF p_base_ccy='USD' THEN --use Commodity Quote Basis
4534 v_ccy_multiplier := v_ask_rate_comm;
4535 ELSE
4536 v_ccy_multiplier := v_ask_rate_base;
4537 END IF;
4538 ELSIF p_side IN ('B','BID') THEN
4539 IF p_base_ccy='USD' THEN --use Commodity Quote Basis
4540 v_ccy_multiplier := v_bid_rate_comm;
4541 ELSE
4542 v_ccy_multiplier := v_bid_rate_base;
4543 END IF;
4544 ELSE --'MID'
4545 IF p_base_ccy='USD' THEN --use Commodity Quote Basis
4546 v_ccy_multiplier := (v_ask_rate_comm+v_bid_rate_comm)/2;
4547 ELSE
4548 v_ccy_multiplier := (v_ask_rate_base+v_bid_rate_base)/2;
4549 END IF;
4550 END IF;
4551 END IF;
4552 END IF;
4553
4554 END IF;
4555 IF (g_proc_level>=g_debug_level) THEN
4556 xtr_risk_debug_pkg.dpop(null,'QRM_PA_AGGREGATION_P.GET_FX_RATE');
4557 END IF;
4558 RETURN v_ccy_multiplier;
4559
4560 EXCEPTION
4561 WHEN xtr_market_data_p.e_mdcs_no_data_found THEN
4562
4563 IF (G_ERROR_level>=g_debug_level) THEN
4564 xtr_risk_debug_pkg.dlog('EXCEPTION','xtr_market_data.e_mdcs_no_data_found',
4565 'QRM_PA_AGGREGATION_P.GET_FX_RATE',g_eRROR_level);--bug 3236479
4566 END IF;
4567
4568 FND_MESSAGE.SET_NAME('QRM','QRM_CALC_NO_DEFAULT_SPOT_ERR');
4569 FND_MESSAGE.SET_TOKEN('CCY',p_contra_ccy);
4570 RAISE e_pagg_no_fxrate_found;
4571 END get_fx_rate;
4572
4573
4574
4575
4576 END QRM_PA_AGGREGATION_P;
[Home] [Help]