[Home] [Help]
PACKAGE BODY: APPS.XTR_HEDGE_PROCESS_P
Source
1 PACKAGE BODY XTR_HEDGE_PROCESS_P AS
2 /* $Header: xtrhdgpb.pls 120.8.12020000.2 2013/03/19 14:43:15 nipant ship $ */
3
4
5 /*=====================================================================
6 BEGIN: New objects for BUG 3378028 - FAS HEDGE ACCOUNTING PROJECT
7 ======================================================================*/
8
9
10 /*-------------------------------------------------------
11 This is a utility procedure to easily switch the debugging
12 to the fnd_log file or SQL*PLUS console.
13 --------------------------------------------------------*/
14 PROCEDURE LOG_MSG(P_TEXT IN VARCHAR2 DEFAULT NULL, P_VALUE IN VARCHAR2 DEFAULT NULL) IS
15
16 l_flag VARCHAR2(1);
17
18 BEGIN
19
20 if l_flag = 'D' then
21 dbms_output.put_line(p_text||' : '||p_value);
22 elsif l_flag = 'C' then
23 fnd_file.put_line(1,p_text||' : '||p_value);
24 else
25 xtr_risk_debug_pkg.dlog(p_text, p_value);
26 end if;
27
28 END LOG_MSG;
29
30 ---------------------------------------------------------------------------
31
32 PROCEDURE CALC_RECLASS(P_COMPANY IN VARCHAR2,
33 P_BATCH_ID IN NUMBER,
34 P_HEDGE_NO IN NUMBER,
35 P_RECLASS_ID IN NUMBER,
36 P_DATE IN DATE) IS
37
38 l_retro_test_id NUMBER;
39 l_approach VARCHAR2(30);
40 l_round NUMBER;
41 l_gain_loss_ccy VARCHAR2(15);
42 l_amount_type VARCHAR2(30);
43 l_excl_item VARCHAR2(30);
44
45 l_hedge_amt NUMBER;
46 l_ref_amount NUMBER;
47
48 l_orig_hedge_amt NUMBER;
49 l_rem_hedge_amt NUMBER;
50
51 l_rec_hdg_amt NUMBER;
52 l_cum_rec_hdg_amt NUMBER;
53 l_cum_rec_gl_amt NUMBER;
54
55 l_cum_eff_amt NUMBER;
56 l_rec_gl_amt NUMBER;
57
58 l_deal_count NUMBER;
59 l_deal_total NUMBER;
60
61 l_item_prv_cum NUMBER;
62 l_inst_prv_cum NUMBER;
63 l_eff_prv_cum NUMBER;
64 l_ineff_prv_cum NUMBER;
65 l_excl_prv_cum NUMBER;
66
67 l_hd_eff_prv_cum NUMBER;
68 l_hd_ineff_prv_cum NUMBER;
69 l_hd_excl_prv_cum NUMBER;
70
71 cursor hdg is
72 select s.hedge_approach, h.hedge_amount, h.exclusion_item_code
73 from xtr_hedge_strategies s, xtr_hedge_attributes h
74 where s.strategy_code = h.strategy_code
75 and h.hedge_attribute_id = p_hedge_no;
76
77 cursor ref_amt is
78 select abs(sum(r.reference_amount)) ref_amt
79 from xtr_hedge_relationships r
80 where r.hedge_attribute_id = p_hedge_no
81 and instrument_item_flag = 'I';
82
83 cursor reclass is
84 select reclass_hedge_amt
85 from xtr_reclass_details
86 where reclass_details_id = p_reclass_id;
87
88 cursor prv_reclass is
89 select sum(reclass_hedge_amt), sum(reclass_gain_loss_amt)
90 from xtr_reclass_details
91 where hedge_attribute_id = p_hedge_no
92 and reclass_date < p_date;
93
94 cursor cum_eff is
95 select eff_cum_gain_loss_amt
96 from xtr_hedge_retro_tests T1
97 where hedge_attribute_id = p_hedge_no
98 and result_date = (select max(result_date) from xtr_hedge_retro_tests T2
99 where T1.hedge_attribute_id = T2.hedge_attribute_id
100 and result_code is not null);
101
102 -- Unit Test Change: Added "and result_code is not null" condition
103 -- This will give correct result for MANUAL Tests
104
105 cursor deal_count is
106 select count(primary_code) deal_count,
107 abs(sum(nvl(reference_amount,0))) deal_total
108 from xtr_hedge_relationships
109 where hedge_attribute_id = p_hedge_no
110 and instrument_item_flag = 'U';
111
112 cursor get_deals is
113 select primary_code deal_no,
114 abs(nvl(reference_amount,0)) alloc_ref_amt
115 from xtr_hedge_relationships
116 where hedge_attribute_id = p_hedge_no
117 and instrument_item_flag = 'U';
118
119 cursor get_cumu(p_hedge_no in number, p_batch_id in number) is
120 select item_cum_gain_loss_amt, inst_cum_gain_loss_amt, eff_cum_gain_loss_amt,
121 ineff_cum_gain_loss_amt, excluded_cum_gain_loss_amt
122 from xtr_hedge_retro_tests
123 where hedge_attribute_id = p_hedge_no
124 and result_date = (select max(result_date) from xtr_hedge_retro_tests r
125 where r. hedge_attribute_id = p_hedge_no
126 and r.batch_id <= p_batch_id);
127
128
129 cursor get_prev_ineff(p_hedge_no in number, p_deal_no in number) is
130 select eff_cum_gain_loss_amt, ineff_cum_gain_loss_amt, excluded_prd_gain_loss_amt
131 from xtr_deal_retro_tests
132 where hedge_attribute_id = p_hedge_no
133 and deal_number = p_deal_no
134 and result_date = (select max(result_date) from xtr_deal_retro_tests r
135 where r. hedge_attribute_id = p_hedge_no
136 and r.deal_number = p_deal_no
137 and r.batch_id <= p_batch_id);
138
139 cursor cur_rnd(p_ccy in varchar2) is
140 select rounding_factor
141 from xtr_master_currencies_v
142 where currency = p_ccy;
143
144 BEGIN
145
146 LOG_MSG('p_reclass_id', p_reclass_id);
147
148 open hdg;
149 fetch hdg into l_approach, l_hedge_amt, l_excl_item;
150 close hdg;
151
152 if l_excl_item = 'NONE' then
153 l_amount_type := 'UNREAL';
154 elsif l_excl_item = 'TIME' then
155 l_amount_type := 'CCYUNRL';
156 end if;
157
158 l_gain_loss_ccy := get_gl_ccy(l_amount_type, p_hedge_no, p_company);
159
160 open cur_rnd(l_gain_loss_ccy);
161 fetch cur_rnd into l_round;
162 close cur_rnd;
163
164 if l_approach = 'FORECAST' then
165
166 l_orig_hedge_amt := NVL(l_hedge_amt,0);
167
168 -- elsif l_approach = 'ASSTLIA' then
169 else
170 open ref_amt;
171 fetch ref_amt into l_orig_hedge_amt;
172 close ref_amt;
173 end if;
174
175 LOG_MSG('l_orig_hedge_amt', l_orig_hedge_amt);
176
177 open reclass;
178 fetch reclass into l_rec_hdg_amt;
179 close reclass;
180
181 LOG_MSG('l_rec_hdg_amt', l_rec_hdg_amt);
182
183 open prv_reclass;
184 fetch prv_reclass into l_cum_rec_hdg_amt, l_cum_rec_gl_amt;
185 close prv_reclass;
186
187 LOG_MSG('l_cum_rec_hdg_amt', l_cum_rec_hdg_amt);
188 LOG_MSG('l_cum_rec_gl_amt', l_cum_rec_gl_amt);
189
190 l_rem_hedge_amt := nvl(l_orig_hedge_amt, 0) - nvl(l_cum_rec_hdg_amt, 0);
191
192 LOG_MSG('l_rem_hedge_amt', l_rem_hedge_amt);
193
194 open cum_eff;
195 fetch cum_eff into l_cum_eff_amt;
196 close cum_eff;
197
198 LOG_MSG('p_reclass_id' , p_reclass_id);
199 LOG_MSG('l_rec_hdg_amt' , l_rec_hdg_amt);
200 LOG_MSG('l_cum_eff_amt' , l_cum_eff_amt);
201 LOG_MSG('l_cum_rec_gl_amt' , l_cum_rec_gl_amt);
202 LOG_MSG('l_orig_hedge_amt' , l_orig_hedge_amt);
203 LOG_MSG('l_rem_hedge_amt' , l_rem_hedge_amt);
204
205 l_rec_gl_amt := round((l_rec_hdg_amt/l_rem_hedge_amt) * ( nvl(l_cum_eff_amt,0) - nvl(l_cum_rec_gl_amt,0)), l_round);
206
207 LOG_MSG('l_rec_gl_amt' ,l_rec_gl_amt);
208
209 LOG_MSG('event', 'updating xtr_reclass_details...');
210
211 update xtr_reclass_details
212 set reclass_gain_loss_amt = nvl(l_rec_gl_amt,0),
213 retro_batch_id = p_batch_id
214 where reclass_details_id = p_reclass_id;
215
216 LOG_MSG('event', 'updating xtr_hedge_relationships...');
217
218 update xtr_hedge_relationships
219 set cur_pct_allocation = round(pct_allocation * (l_rem_hedge_amt - l_rec_hdg_amt)/l_orig_hedge_amt,2),
220 cur_reference_amt = round(reference_amount * (l_rem_hedge_amt - l_rec_hdg_amt)/l_orig_hedge_amt, l_round)
221 where hedge_attribute_id = p_hedge_no;
222
223 LOG_MSG('event', 'updating xtr_hedge_retro_tests...');
224
225 update xtr_hedge_retro_tests
226 set reclass_gain_loss_amt = nvl(l_rec_gl_amt,0)
227 where hedge_attribute_id = p_hedge_no
228 and result_date = p_date;
229
230 if sql%found then
231 LOG_MSG('event', 'updated xtr_hedge_retro_tests sucessfully!');
232 else
233 LOG_MSG('event', 'could not update xtr_hedge_retro_tests. The hedge already matured.');
234
235 open get_cumu(p_hedge_no, p_batch_id);
236 fetch get_cumu into l_item_prv_cum, l_inst_prv_cum, l_eff_prv_cum,
237 l_ineff_prv_cum, l_excl_prv_cum;
238 close get_cumu;
239
240 LOG_MSG('l_amount_type = '||l_amount_type);
241
242 select xtr_hedge_retro_tests_s.nextval into l_retro_test_id from dual;
243
244 insert into xtr_hedge_retro_tests
245 (HEDGE_RETRO_TEST_ID, COMPANY_CODE, HEDGE_ATTRIBUTE_ID, RESULT_CODE,
246 BATCH_ID, RESULT_DATE,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
247 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,PROGRAM_ID, PROGRAM_LOGIN_ID,
248 PROGRAM_APPLICATION_ID, REQUEST_ID, COMPLETE_FLAG,GAIN_LOSS_CCY,
249 AMOUNT_TYPE, ITEM_PRD_GAIN_LOSS_AMT, INST_PRD_GAIN_LOSS_AMT,
250 ITEM_CUM_GAIN_LOSS_AMT, INST_CUM_GAIN_LOSS_AMT, PCT_EFFECTIVE, EFF_PRD_GAIN_LOSS_AMT,
251 INEFF_PRD_GAIN_LOSS_AMT, EXCLUDED_PRD_GAIN_LOSS_AMT,
252 RECLASS_GAIN_LOSS_AMT,
253 EFF_CUM_GAIN_LOSS_AMT, INEFF_CUM_GAIN_LOSS_AMT, EXCLUDED_CUM_GAIN_LOSS_AMT
254 )
255 values (l_retro_test_id, p_company, p_hedge_no, NULL,
256 p_batch_id, p_date, fnd_global.user_id, sysdate, fnd_global.user_id,
257 sysdate, fnd_global.login_id, fnd_global.conc_program_id, fnd_global.conc_login_id,
258 fnd_global.prog_appl_id, fnd_global.conc_request_id,'Y', l_gain_loss_ccy,
259 l_amount_type, 0, 0, l_item_prv_cum, l_inst_prv_cum, NULL, 0,0,0,
260 nvl(l_rec_gl_amt,0), l_eff_prv_cum, l_ineff_prv_cum, l_excl_prv_cum
261 );
262 end if;
263
264 open deal_count;
265 fetch deal_count into l_deal_count, l_deal_total;
266 close deal_count;
267
268 LOG_MSG('l_deal_count', l_deal_count);
269
270 for r in get_deals loop
271
272 LOG_MSG('event', 'updating xtr_deal_retro_tests...');
273
274 update xtr_deal_retro_tests
275 set reclass_gain_loss_amt = nvl(l_rec_gl_amt,0) * round(r.alloc_ref_amt/l_deal_total, l_round)
276 where hedge_attribute_id = p_hedge_no
277 and deal_number = r.deal_no
278 and result_date = p_date;
279
280 if sql%found then
281
282 LOG_MSG('event', 'updated xtr_deal_retro_tests sucessfully!');
283
284 else
285
286 LOG_MSG('event', 'could not update xtr_deal_retro_tests. The hedge already matured.');
287
288 open get_prev_ineff(p_hedge_no, r.deal_no);
289 fetch get_prev_ineff into l_hd_eff_prv_cum, l_hd_ineff_prv_cum, l_hd_excl_prv_cum;
290 close get_prev_ineff;
291
292 LOG_MSG('l_amount_type = '||l_amount_type);
293
294 insert into xtr_deal_retro_tests
295 (DEAL_RETRO_TEST_ID,
296 HEDGE_RETRO_TEST_ID,
297 BATCH_ID,
298 HEDGE_ATTRIBUTE_ID,
299 CREATED_BY,
300 CREATION_DATE,
301 LAST_UPDATED_BY,
302 LAST_UPDATE_DATE,
303 LAST_UPDATE_LOGIN,
304 PROGRAM_ID,
305 PROGRAM_LOGIN_ID,
306 PROGRAM_APPLICATION_ID,
307 REQUEST_ID,
308 DEAL_NUMBER,
309 AMOUNT_TYPE,
310 EFF_PRD_GAIN_LOSS_AMT,
311 INEFF_PRD_GAIN_LOSS_AMT,
312 EXCLUDED_PRD_GAIN_LOSS_AMT,
313 RECLASS_GAIN_LOSS_AMT,
314 EFF_CUM_GAIN_LOSS_AMT,
315 INEFF_CUM_GAIN_LOSS_AMT,
316 EXCLUDED_CUM_GAIN_LOSS_AMT,
317 RESULT_DATE
318 )
319 values (
320 xtr_deal_retro_tests_s.nextval,
321 l_retro_test_id,
322 p_batch_id,
323 p_hedge_no,
324 fnd_global.user_id,
325 sysdate,
326 fnd_global.user_id,
327 sysdate,
328 fnd_global.login_id,
329 fnd_global.conc_program_id,
330 fnd_global.conc_login_id,
331 fnd_global.prog_appl_id,
332 fnd_global.conc_request_id,
333 r.deal_no,
334 l_amount_type,
335 0,
336 0,
337 0,
338 nvl(l_rec_gl_amt,0) * round(r.alloc_ref_amt/l_deal_total, l_round),
339 l_hd_eff_prv_cum,
340 l_hd_ineff_prv_cum,
341 l_hd_excl_prv_cum,
342 p_date
343 );
344
345 end if;
346
347 end loop;
348 END CALC_RECLASS;
349
350 ---------------------------------------------------------------------------
351
352 PROCEDURE AUTHORIZE(p_company IN VARCHAR2, p_batch_id in NUMBER) IS
353
354 l_mbst_date DATE;
355 l_temp_date DATE;
356 l_reval_id NUMBER;
357 l_deal_reval_amt NUMBER;
358 l_dummy VARCHAR2(1);
359
360 l_dmbtd VARCHAR2(1) := 'N';
361 l_dmotd VARCHAR2(1) := 'N';
362
363 l_drbtd VARCHAR2(1) := 'N';
364 l_drotd VARCHAR2(1) := 'N';
365
366 l_deal_ineff_prd_tot NUMBER := 0;
367 l_deal_ineff_dnm_tot NUMBER := 0;
368
369 l_deal_dnm_ineff NUMBER := 0;
370 l_deal_dnm_eff NUMBER := 0;
371 l_deal_cum_recl_amt NUMBER:= 0;
372
373 l_amt_type_eu VARCHAR2(30);
374 l_amt_type_iu VARCHAR2(30);
375 l_amt_type_ru VARCHAR2(30);
376 l_amt_type_rr VARCHAR2(30);
377 l_amt_type_ir VARCHAR2(30);
378
379 cursor cur_dnm(p_batch_id in number) is
380 select deal_number, amount_type, result_date,
381 nvl(sum(eff_prd_gain_loss_amt),0) eff_prd_gain_loss_amt,
382 nvl(sum(reclass_gain_loss_amt),0) reclass_gain_loss_amt
383 from xtr_deal_retro_tests d
384 where d.batch_id = p_batch_id
385 and d.hedge_attribute_id in (select hedge_attribute_id
386 from xtr_hedge_attributes ha, xtr_hedge_strategies hs
387 where ha.strategy_code = hs.strategy_code
388 and hs.hedge_type = 'CASHFLOW')
389 group by deal_number, amount_type, result_date
390 order by deal_number, amount_type, result_date;
391
392 cursor prv_reclass (p_deal_no in number) is
393 select nvl(sum(reclass_gain_loss_amt),0)
394 from xtr_deal_retro_tests
395 where deal_number = p_deal_no
396 and result_date <= l_temp_date;
397
398 CURSOR reval (p_amount_type in varchar2, p_reval_id in number) is
399 --select decode(p_amount_type, 'UNREAL', unrealised_pl, curr_gain_loss_amount) reval_amt
400 select decode(p_amount_type, 'UNREAL', unrealised_pl, curr_gain_loss_amount) reval_amt
401 from xtr_revaluation_details
402 where revaluation_details_id = p_reval_id;
403
404 cursor deal_mbtd (p_deal_no in number, p_date in DATE) is
405 select 'Y' from xtr_revaluation_details
406 where deal_no = p_deal_no
407 and realized_flag = 'Y'
408 and period_to < p_date;
409
410 cursor deal_motd (p_deal_no in number, p_date in DATE) is
411 select 'Y' from xtr_revaluation_details
412 where deal_no = p_deal_no
413 and realized_flag = 'Y'
414 and period_to = p_date;
415
416 cursor deal_rbtd (p_deal_no in number, p_date in DATE) is
417 select 'Y' from xtr_deal_retro_tests
418 where deal_number = p_deal_no
419 and nvl(reclass_gain_loss_amt,0) <> 0
420 and result_date < p_date;
421
422 /* Not proabably needed
423 cursor deal_rotd (p_deal_no in number, p_date in DATE) is
424 select 'Y' from xtr_deal_retro_tests
425 where deal_no = p_deal_no
426 and nvl(reclass_gain_loss_amt,0) <> 0
427 and result_date = p_date;
428 */
429
430 cursor dnm (p_deal_no in number, p_flag in VARCHAR2, p_amount_type in VARCHAR2, p_date_type in VARCHAR2) is
431 select sum(DECODE(ACTION, 'LOSS',-AMOUNT, AMOUNT)) from xtr_gain_loss_dnm
432 where journal_date <= l_temp_date
433 and deal_number = p_deal_no
434 and reval_eff_flag = p_flag
435 and amount_type = p_amount_type
436 and date_type = p_date_type;
437
438 cursor reval_dtls(p_deal_no in number) is
439 select revaluation_details_id from xtr_revaluation_details
440 where deal_no = p_deal_no
441 and period_from >= nvl(l_mbst_date, period_from)
442 -- and period_to = l_temp_date -- Unit Test Change
443 and period_to <= l_temp_date
444 and realized_flag = 'N'
445 and batch_id = p_batch_id
446 and deal_type = 'FX'
447 and deal_subtype = 'FORWARD';
448
449
450 cursor mbstdt(p_deal_no in number, p_end_date in DATE) is
451 select max(period_from)
452 from xtr_revaluation_details
453 where batch_id = p_batch_id
454 and deal_no = p_deal_no
455 and deal_type = 'FX'
456 and deal_subtype = 'FORWARD'
457 and period_to <= p_end_date
458 and period_from <= p_end_date;
459
460 PROCEDURE xtr_ins_dnm (P_DEAL_NO IN NUMBER,
461 P_DATE IN DATE,
462 P_DATE_TYPE IN VARCHAR2,
463 P_AMOUNT_TYPE IN VARCHAR2,
464 P_AMOUNT IN NUMBER DEFAULT NULL) is
465
466 l_dnm_id NUMBER;
467 l_row_id VARCHAR2(64);
468 l_action VARCHAR2(7);
469 l_gl_ccy VARCHAR2(15);
470
471 BEGIN
472
473 select XTR_GAIN_LOSS_DNM_S.nextval into l_dnm_id from dual;
474
475 if p_amount > 0 then
476 l_action := 'PROFIT';
477 elsif p_amount < 0 then
478 l_action := 'LOSS';
479 end if;
480
481 l_gl_ccy := get_gl_ccy(p_amount_type, p_deal_no, p_company);
482
483 XTR_GAIN_LOSS_DNM_PKG.INSERT_ROW(
484 l_row_id,
485 l_dnm_id ,
486 p_batch_id,
487 p_company,
488 p_deal_no,
489 1,
490 p_date_type,
491 abs(p_amount),
492 p_amount_type,
493 l_action,
494 l_gl_ccy,
495 p_date,
496 'T',
497 sysdate,
498 fnd_global.user_id,
499 sysdate,
500 fnd_global.user_id,
501 fnd_global.login_id
502 );
503
504 Exception
505 when others then
506 fnd_message.set_name('XTR', 'XTR_154');
507 APP_EXCEPTION.Raise_exception;
508 LOG_MSG('Error', 'Error occured in XTR_HEDGE_PROCESS_P.xtr_ins_dnm');
509 LOG_MSG('Error', SQLERRM);
510 END xtr_ins_dnm;
511
512 Begin
513
514 for dnm_rec in cur_dnm(p_batch_id) loop
515
516 if dnm_rec.amount_type in ('UNREAL','REAL') then
517 l_amt_type_eu := 'NRECUNR';
518 l_amt_type_iu := 'UNREAL' ;
519 l_amt_type_ru := 'RECUNRL';
520 l_amt_type_rr := 'RECREAL';
521 l_amt_type_ir := 'REAL' ;
522 elsif dnm_rec.amount_type in ('CCYUNRL','CCYREAL') then
523 l_amt_type_eu := 'NRECCYU';
524 l_amt_type_iu := 'CCYUNRL' ;
525 l_amt_type_ru := 'RECCYUN';
526 l_amt_type_rr := 'RECCYRE';
527 l_amt_type_ir := 'CCYREAL' ;
528 end if;
529
530 LOG_MSG('event', 'Authorizing for result date '||dnm_rec.result_date);
531 LOG_MSG('dnm_rec.deal_number', dnm_rec.deal_number);
532 LOG_MSG('p_batch_id', p_batch_id);
533
534 l_temp_date := dnm_rec.result_date;
535
536 l_deal_reval_amt := 0;
537
538 --if l_mbst_date is null then --bug 16407397 starts
539 open mbstdt(dnm_rec.deal_number, l_temp_date);
540 fetch mbstdt into l_mbst_date;
541 close mbstdt;
542 LOG_MSG('l_mbst_date: '||l_mbst_date);
543 --end if;Bug 16407397 ends
544
545
546 for out_rec in reval_dtls(dnm_rec.deal_number) loop
547 LOG_MSG('l_reval_id:', out_rec.revaluation_details_id);
548 for in_rec in reval(dnm_rec.amount_type, out_rec.revaluation_details_id) loop
549 LOG_MSG('in_rec.reval_amt:', in_rec.reval_amt);
550 l_deal_reval_amt := nvl(l_deal_reval_amt,0) + in_rec.reval_amt;
551 end loop;
552 end loop;
553
554
555 /* Unit Test Change
556 open reval_dtls(dnm_rec.deal_number);
557 fetch reval_dtls into l_reval_id;
558 if reval_dtls%notfound then
559 LOG_MSG('status', 'deal already matured');
560 else
561 open reval(dnm_rec.amount_type, l_reval_id);
562 fetch reval into l_deal_reval_amt;
563 close reval;
564 end if;
565 close reval_dtls;
566 */
567
568 -- LOG_MSG('l_reval_id', l_reval_id);
569 LOG_MSG('l_deal_reval_amt', l_deal_reval_amt);
570
571 l_deal_ineff_prd_tot := nvl(l_deal_reval_amt,0) - nvl(dnm_rec.eff_prd_gain_loss_amt,0);
572
573 LOG_MSG('deal ineff g/l', l_deal_ineff_prd_tot);
574
575 open dnm(dnm_rec.deal_number, 'T',dnm_rec.amount_type, 'REVAL');
576 fetch dnm into l_deal_ineff_dnm_tot;
577 close dnm;
578
579 LOG_MSG('l_deal_ineff_dnm_tot', l_deal_ineff_dnm_tot);
580
581 open deal_mbtd(dnm_rec.deal_number, dnm_rec.result_date);
582 fetch deal_mbtd into l_dummy;
583 if deal_mbtd%FOUND then
584 l_dmbtd := 'Y';
585 end if;
586 close deal_mbtd;
587
588 LOG_MSG('l_dmbtd', l_dmbtd);
589
590 if l_dmbtd = 'Y' then
591
592 if nvl(dnm_rec.RECLASS_GAIN_LOSS_AMT,0) <> 0 then
593 LOG_MSG('event', 'generating RECUNRL row with amount '||dnm_rec.RECLASS_GAIN_LOSS_AMT);
594 xtr_ins_dnm(dnm_rec.deal_number, dnm_rec.result_date, 'RECLASS', l_amt_type_ru, dnm_rec.RECLASS_GAIN_LOSS_AMT);
595 LOG_MSG('event', 'generating RECREAL row with amount '||dnm_rec.RECLASS_GAIN_LOSS_AMT);
596 xtr_ins_dnm(dnm_rec.deal_number, dnm_rec.result_date, 'RECLASS', l_amt_type_rr, dnm_rec.RECLASS_GAIN_LOSS_AMT);
597 end if;
598
599 elsif l_dmbtd = 'N' then
600
601 open deal_motd(dnm_rec.deal_number, dnm_rec.result_date);
602 fetch deal_motd into l_dummy;
603 if deal_motd%FOUND then
604 l_dmotd := 'Y';
605 end if;
606 close deal_motd;
607
608 LOG_MSG('l_dmotd = '||l_dmotd);
609
610 if l_dmotd = 'Y' then
611
612 open dnm(dnm_rec.deal_number, 'R', l_amt_type_iu, 'REVAL');
613 fetch dnm into l_deal_dnm_ineff;
614 close dnm;
615
616 open dnm(dnm_rec.deal_number, 'T', l_amt_type_eu, 'REVAL');
617 fetch dnm into l_deal_dnm_eff;
618 close dnm;
619
620 if nvl(l_deal_ineff_prd_tot,0) <> 0 then
621 LOG_MSG('event', 'generating UNREAL row');
622 LOG_MSG('event', 'generating UNREAL row with amount: '||l_deal_ineff_prd_tot);
623 xtr_ins_dnm(dnm_rec.deal_number, dnm_rec.result_date, 'REVAL', l_amt_type_iu, l_deal_ineff_prd_tot);
624 end if;
625
626 LOG_MSG('event', 'generating REAL row with amount: ');
627
628 LOG_MSG(l_deal_dnm_ineff - l_deal_dnm_eff);
629
630 xtr_ins_dnm(dnm_rec.deal_number, dnm_rec.result_date, 'REVAL', l_amt_type_ir,
631 nvl(l_deal_dnm_ineff,0) - nvl(l_deal_dnm_eff,0));
632
633 /* if the hedge is still active at this time then geenrate NRECUNR row */
634
635 if nvl(dnm_rec.EFF_PRD_GAIN_LOSS_AMT,0) <> 0 then
636 LOG_MSG('event', 'generating NRECUNR row');
637 xtr_ins_dnm(dnm_rec.deal_number, dnm_rec.result_date, 'REVAL', l_amt_type_eu, dnm_rec.EFF_PRD_GAIN_LOSS_AMT);
638 end if;
639
640 if nvl(dnm_rec.RECLASS_GAIN_LOSS_AMT,0) <> 0 then
641 l_drotd := 'Y';
642 xtr_ins_dnm(dnm_rec.deal_number, dnm_rec.result_date, 'RECLASS', l_amt_type_ru, dnm_rec.RECLASS_GAIN_LOSS_AMT);
643 else
644 open deal_rbtd(dnm_rec.deal_number, dnm_rec.result_date);
645 fetch deal_rbtd into l_dummy;
646 if deal_rbtd%FOUND then
647 l_drbtd := 'Y';
648 end if;
649 close deal_rbtd;
650 end if;
651
652
653 if l_drotd = 'Y' or l_drbtd = 'Y' then
654
655 open prv_reclass(dnm_rec.deal_number);
656 fetch prv_reclass into l_deal_cum_recl_amt;
657 close prv_reclass;
658
659 LOG_MSG('event', 'generating RECREAL row with amount: '||l_deal_cum_recl_amt);
660
661 LOG_MSG('event', 'l_deal_cum_recl_amt = '||l_deal_cum_recl_amt);
662
663 xtr_ins_dnm(dnm_rec.deal_number, dnm_rec.result_date, 'REVAL', l_amt_type_rr,
664 l_deal_cum_recl_amt);
665 end if;
666
667 else
668
669 if nvl(l_deal_ineff_prd_tot,0) <> 0 then
670 LOG_MSG('event', 'generating REVAL/UNREAL row');
671 xtr_ins_dnm(dnm_rec.deal_number, dnm_rec.result_date, 'REVAL', l_amt_type_iu, l_deal_ineff_prd_tot);
672 end if;
673
674 if nvl(dnm_rec.EFF_PRD_GAIN_LOSS_AMT,0) <> 0 then
675 LOG_MSG('event', 'generating REVAL/NRECUNR row');
676 xtr_ins_dnm(dnm_rec.deal_number, dnm_rec.result_date, 'REVAL', l_amt_type_eu, dnm_rec.EFF_PRD_GAIN_LOSS_AMT);
677 end if;
678
679 if nvl(dnm_rec.RECLASS_GAIN_LOSS_AMT,0) <> 0 then
680 LOG_MSG('event', 'generating RECLASS/RECUNRL row');
681 xtr_ins_dnm(dnm_rec.deal_number, dnm_rec.result_date, 'RECLASS', l_amt_type_ru, dnm_rec.RECLASS_GAIN_LOSS_AMT);
682 end if;
683
684 end if;
685
686 end if;
687
688 l_mbst_date := dnm_rec.result_date; -- Unit Test Change
689
690 end loop;
691
692 Update XTR_BATCH_EVENTS
693 set AUTHORIZED = 'Y',
694 AUTHORIZED_BY = FND_GLOBAL.USER_ID,
695 AUTHORIZED_ON = SYSDATE,
696 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
697 LAST_UPDATE_DATE = SYSDATE,
698 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
699 where BATCH_ID = P_BATCH_ID
700 and EVENT_CODE = 'RETROET'
701 and nvl(AUTHORIZED,'N') <> 'Y';
702
703 Exception
704 when others then
705 fnd_message.set_name('XTR', 'XTR_154');
706 APP_EXCEPTION.Raise_exception;
707 END AUTHORIZE;
708
709
710 /*-----------------------------------------------------------------
711 This function returns a ratio representing the deal's
712 share of the total hedge instruments amount
713 ------------------------------------------------------------------*/
714
715 FUNCTION get_deal_alloc(p_deal_no IN NUMBER,
716 p_hedge_no IN NUMBER) return NUMBER is
717
718 l_ref_amt NUMBER;
719 l_ref_tot NUMBER;
720
721 /*-------------------------------------------------------
722 get deals assigned to this hedge
723 --------------------------------------------------------*/
724
725 cursor ref_amt is
726 select nvl(reference_amount,0) alloc_ref_amt
727 from xtr_hedge_relationships
728 where hedge_attribute_id = p_hedge_no
729 and primary_code = p_deal_no
730 and instrument_item_flag = 'U';
731
732
733 cursor ref_tot is
734 select sum(nvl(reference_amount,0)) deal_total
735 from xtr_hedge_relationships
736 where hedge_attribute_id = p_hedge_no
737 and instrument_item_flag = 'U';
738
739 Begin
740
741 open ref_amt;
742 fetch ref_amt into l_ref_amt;
743 close ref_amt;
744
745 open ref_tot;
746 fetch ref_tot into l_ref_tot;
747 close ref_tot;
748
749 return round(l_ref_amt/l_ref_tot,2);
750
751 END get_deal_alloc;
752
753
754 /*-----------------------------------------------------------------
755 This function returns the gain/loss
756 currency based on the Amount Type
757 ------------------------------------------------------------------*/
758 FUNCTION get_gl_ccy(p_amount_type IN VARCHAR2,
759 p_deal_no IN NUMBER,
760 p_company IN VARCHAR2) return VARCHAR2 is
761
762 l_gl_ccy VARCHAR2(15);
763
764
765 cursor deal_ccy is
766 select distinct reval_ccy
767 from xtr_revaluation_details
768 where deal_no = p_deal_no;
769
770 cursor sob_ccy is
771 select set_of_books_currency
772 from xtr_parties_v
773 where party_code = p_company
774 and party_type = 'C';
775
776 Begin
777
778
779 If p_amount_type in ('UNREAL', 'REAL', 'NRECUNR', 'RECUNRL', 'RECREAL') then
780 open deal_ccy;
781 fetch deal_ccy into l_gl_ccy;
782 close deal_ccy;
783 End If;
784
785 If p_amount_type in ('CCYUNRL', 'CCYREAL', 'NRECCYU', 'RECCYUN', 'RECCYRE') or (l_gl_ccy is NULL) then
786 open sob_ccy;
787 fetch sob_ccy into l_gl_ccy;
788 close sob_ccy;
789 End If;
790
791 return l_gl_ccy;
792 Exception
793 When Others then return NULL;
794 END get_gl_ccy;
795
796
797 /*-----------------------------------------------------------------
798 This function returns 'Y' if the company performs
799 retrospective effectiveness testing; otherwise returns 'N'
800 ------------------------------------------------------------------*/
801 FUNCTION performs_retro(p_company IN VARCHAR2) return VARCHAR2 is
802
803 l_dummy VARCHAR2(30) := 'N';
804
805 cursor param is
806 select parameter_value_code
807 from XTR_COMPANY_PARAMETERS
808 where company_code = p_company
809 and parameter_code = 'ACCNT_BTEST';
810
811 Begin
812
813 if p_company is not NULL then
814 open param;
815 fetch param into l_dummy;
816 close param;
817 end if;
818
819 return (l_dummy);
820
821 Exception
822 When Others then return ('N');
823 END performs_retro;
824 ---------------------------------------------------------------------------
825
826
827 ---------------------------------------------------------------------------
828 PROCEDURE retro_main_calc(P_COMPANY IN VARCHAR2, P_BATCH_ID IN NUMBER) IS
829
830 l_temp VARCHAR2(200);
831
832 l_retro_test_id NUMBER;
833
834 l_deal_no NUMBER;
835 l_from_date DATE;
836 l_to_date DATE;
837
838 l_result varchar2(30);
839 l_test_method varchar2(30);
840 l_min_tol number;
841 l_max_tol number;
842 l_pct_eff number;
843 l_deal_pct number;
844 l_excl_item VARCHAR2(30);
845
846
847 /*-------------------------------------------------------
848 These 2 variables are used to allocate the hedge level
849 measurement results to the deals.
850 --------------------------------------------------------*/
851 l_deal_count NUMBER;
852 l_deal_total NUMBER;
853
854 /*-------------------------------------------------------
855 These 2 variables are only used for calulating pct_eff
856 and then to determine PASS or FAIL
857 --------------------------------------------------------*/
858
859 l_inst_amt NUMBER := 0;
860 l_item_amt NUMBER := 0;
861
862 l_inst_prd NUMBER := 0;
863 l_item_prd NUMBER := 0;
864 l_excl_prd NUMBER := 0;
865
866 l_inst_cum NUMBER := 0;
867 l_item_cum NUMBER := 0;
868 l_excl_cum NUMBER := 0;
869
870 l_inst_prv_cum NUMBER := 0;
871 l_item_prv_cum NUMBER := 0;
872 l_excl_prv_cum NUMBER := 0;
873
874 l_eff_prv_cum NUMBER := 0;
875 l_ineff_prv_cum NUMBER := 0;
876
877 l_eff_cum NUMBER := 0;
878 l_ineff_cum NUMBER := 0;
879
880 l_eff_prd NUMBER := 0;
881 l_ineff_prd NUMBER := 0;
882
883 l_deal_prd_tot NUMBER := 0;
884 l_deal_eff_prd_tot NUMBER := 0;
885 l_deal_ineff_prd_tot NUMBER := 0;
886
887 l_deal_eff_prv_cum NUMBER;
888 l_deal_ineff_prv_cum NUMBER;
889
890 l_hd_eff_prd NUMBER := 0;
891 l_hd_ineff_prd NUMBER := 0;
892 l_hd_eff_prv_cum NUMBER := 0;
893 l_hd_ineff_prv_cum NUMBER := 0;
894
895 l_cb_end_date DATE;
896 l_pb_end_date DATE;
897
898 l_temp_date DATE;
899 l_reval_id NUMBER;
900 l_reval_amt NUMBER;
901 l_complete_flag VARCHAR2(1);
902 l_amount_type VARCHAR2(30);
903 l_gain_loss_ccy VARCHAR2(15);
904 l_round NUMBER;
905
906 TYPE eff_table_type is table of NUMBER INDEX BY BINARY_INTEGER;
907 eff_table eff_table_type;
908
909
910 /*-------------------------------------------------------
911 get eligible hedges
912 --------------------------------------------------------*/
913 cursor get_hedges(p_cb_end_date in DATE, p_pb_end_date in DATE) is
914 select
915 deal_no hedge_no
916 ,revaluation_details_id rec_id
917 ,'10' rec_source
918 ,period_from period_from
919 ,period_to period_to
920 ,unrealised_pl unrealised_pl
921 ,realised_pl realised_pl
922 ,curr_gain_loss_amount curr_gain_loss_amount
923 ,realized_flag realized_flag
924 from xtr_revaluation_details r
925 where batch_id is not null
926 and company_code = p_company
927 and not exists (select 'Y' from xtr_batch_events e
928 where e.batch_id = r.batch_id and
929 e.event_code = 'RETROET')
930 and batch_id in (select batch_id from xtr_batches b where
931 b.period_end <= p_cb_end_date)
932 and exists (select 'Y' from XTR_COMPANY_PARAMETERS
933 where company_code = p_company
934 and parameter_code = 'ACCNT_BTEST'
935 and parameter_value_code = 'Y')
936 and deal_type = 'HEDGE'
937 UNION
938 select DISTINCT
939 ha.deal_no hedge_no
940 ,-9999 rec_id
941 ,'20' rec_source
942 ,period_from period_from
943 ,period_to period_to
944 ,0 unrealised_pl
945 ,0 realised_pl
946 ,0 curr_gain_loss_amount
947 ,NULL realized_flag
948 from xtr_revaluation_details r, xtr_eligible_hedges_v ha, xtr_hedge_relationships hr
949 where batch_id is not null
950 and r.company_code = p_company
951 and r.deal_no = hr.primary_code
952 and r.deal_type = 'FX'
953 and r.deal_subtype = 'FORWARD'
954 and r.realized_flag = 'N'
955 and hr.instrument_item_flag = 'U'
956 and ha.deal_no = hr.hedge_attribute_id
957 and r.period_to > ha.maturity_date
958 and not exists (select 'Y' from xtr_batch_events e
959 where e.batch_id = r.batch_id and
960 e.event_code = 'RETROET')
961 and batch_id in (select batch_id from xtr_batches b where
962 b.period_end <= p_cb_end_date)
963 and exists (select 'Y' from XTR_COMPANY_PARAMETERS
964 where company_code = p_company
965 and parameter_code = 'ACCNT_BTEST'
966 and parameter_value_code = 'Y')
967 UNION
968 select
969 r.hedge_attribute_id hedge_no
970 ,reclass_details_id rec_id
971 ,'30' rec_source
972 ,reclass_date period_from
973 ,reclass_date period_to
974 ,NULL unrealised_pl
975 ,NULL realised_pl
976 ,NULL curr_gain_loss_amount
977 ,NULL realized_flag
978 from xtr_reclass_details r, xtr_hedge_attributes h, xtr_hedge_strategies s
979 where r.hedge_attribute_id = h.hedge_attribute_id
980 and h.strategy_code = s.strategy_code
981 and h.company_code = p_company
982 and r.reclass_date <= p_cb_end_date
983 and r.reclass_date > nvl(p_pb_end_date, r.reclass_date - 5)
984 and h.hedge_status not in ('DESIGNATE', 'CANCELLED')
985 and exists (select 'Y' from XTR_COMPANY_PARAMETERS
986 where company_code = p_company
987 and parameter_code = 'ACCNT_BTEST'
988 and parameter_value_code = 'Y')
989 and retro_batch_id is NULL
990 and NVL(h.retro_method,'@@') <> 'NOTEST'
991 and h.exclusion_item_code is not NULL
992 and s.hedge_type = 'CASHFLOW'
993 order by hedge_no, period_to, rec_source asc;
994
995
996 /*-------------------------------------------------------
997 get effectiveness test results for previous
998 retro batch for this hedge
999 --------------------------------------------------------*/
1000 cursor get_cumu(p_hedge_no in number, p_batch_id in number) is
1001 select item_cum_gain_loss_amt, inst_cum_gain_loss_amt, eff_cum_gain_loss_amt,
1002 ineff_cum_gain_loss_amt, excluded_cum_gain_loss_amt
1003 from xtr_hedge_retro_tests
1004 where hedge_attribute_id = p_hedge_no
1005 and result_date = (select max(result_date) from xtr_hedge_retro_tests r
1006 where r. hedge_attribute_id = p_hedge_no
1007 and r.batch_id <= p_batch_id);
1008
1009
1010 cursor get_result(p_hedge_no in number, p_batch_id in number) is
1011 select result_code, pct_effective
1012 from xtr_hedge_retro_tests
1013 where hedge_attribute_id = p_hedge_no
1014 and batch_id = p_batch_id;
1015
1016 /*-------------------------------------------------------
1017 get deal level effectiveness test results for
1018 this hedge/batch id combination
1019 --------------------------------------------------------*/
1020
1021 cursor get_dist_deals(p_batch_id in number) is
1022 select distinct deal_number
1023 from xtr_deal_retro_tests
1024 where batch_id = p_batch_id;
1025
1026 cursor get_deal_retro(p_batch_id in number) is
1027 select deal_number, hedge_attribute_id, hedge_retro_test_id,deal_retro_test_id,
1028 ineff_cum_gain_loss_amt
1029 from xtr_deal_retro_tests
1030 where batch_id = p_batch_id;
1031
1032 /*-------------------------------------------------------
1033 get deal level cum ineffective amt for previous
1034 batch
1035 --------------------------------------------------------*/
1036 cursor get_prev_ineff(p_hedge_no in number, p_deal_no in number) is
1037 select eff_cum_gain_loss_amt, ineff_cum_gain_loss_amt
1038 from xtr_deal_retro_tests
1039 where hedge_attribute_id = p_hedge_no
1040 and deal_number = p_deal_no
1041 and result_date = (select max(result_date) from xtr_deal_retro_tests r
1042 where r. hedge_attribute_id = p_hedge_no
1043 and r.deal_number = p_deal_no
1044 and r.batch_id <= p_batch_id);
1045
1046 /*-------------------------------------------------------
1047 get sum of deal level effectiveness gain/loss
1048 amounts for this hedge/batch id combination
1049 --------------------------------------------------------*/
1050 cursor get_tot_eff(p_deal_no in number, p_hedge_retro_test_id in number) is
1051 select sum(eff_prd_gain_loss_amt)
1052 from xtr_deal_retro_tests
1053 where deal_number = p_deal_no
1054 and hedge_retro_test_id = p_hedge_retro_test_id
1055 group by deal_number, hedge_retro_test_id;
1056
1057
1058 /*-------------------------------------------------------
1059 get deals assigned to this hedge
1060 --------------------------------------------------------*/
1061 cursor get_deals(p_hedge_no in number) is
1062 select primary_code deal_no,
1063 cur_pct_allocation deal_pct,
1064 abs(nvl(reference_amount,0)) alloc_ref_amt
1065 from xtr_hedge_relationships
1066 where hedge_attribute_id = p_hedge_no
1067 and instrument_item_flag = 'U';
1068
1069 /*-------------------------------------------------------
1070 get reval gain/loss amounts for this deal
1071 --------------------------------------------------------*/
1072 cursor get_reval is
1073 select unrealised_pl, realised_pl, curr_gain_loss_amount,
1074 (nvl(unrealised_pl,0)-nvl(curr_gain_loss_amount,0)) excluded_amt,
1075 revaluation_details_id
1076 from xtr_revaluation_details
1077 where deal_no = l_deal_no
1078 --and period_from = l_from_date
1079 --and period_to = l_to_date
1080 and period_from >= l_from_date
1081 and period_to <= l_to_date
1082 and realized_flag = 'N';
1083
1084
1085 /*-------------------------------------------------------
1086 get reval gain/loss amount for this deal
1087 --------------------------------------------------------*/
1088 cursor get_deal_gl(p_deal_no in NUMBER) is
1089 select DECODE(l_excl_item, 'NONE', unrealised_pl, 'TIME',curr_gain_loss_amount) deal_pl
1090 from xtr_revaluation_details
1091 where deal_no = p_deal_no
1092 and period_from = l_from_date
1093 and period_to = l_to_date
1094 and batch_id = p_batch_id;
1095
1096
1097 /*-------------------------------------------------------
1098 get end date for this batch
1099 --------------------------------------------------------*/
1100 cursor cur_date is
1101 select period_end from xtr_batches
1102 where batch_id = p_batch_id;
1103
1104 /*-------------------------------------------------------
1105 get end date for immedietly previous batch
1106 --------------------------------------------------------*/
1107 cursor prv_date is
1108 select max(period_end) from xtr_batches b, xtr_batch_events e
1109 where b.batch_id = e.batch_id
1110 and b.batch_id < p_batch_id
1111 and e.event_code = 'RETROET'
1112 and b.company_code = p_company;
1113
1114 /*-------------------------------------------------------
1115 get test related data for this hedge
1116 --------------------------------------------------------*/
1117 cursor hedge(p_hedge_no in number) is
1118 select retro_method, retro_tolerance_min,retro_tolerance_max,exclusion_item_code
1119 from xtr_hedge_attributes
1120 where hedge_attribute_id = p_hedge_no;
1121
1122 cursor retro(p_hedge_no in number) is
1123 select pct_effective
1124 from xtr_hedge_retro_tests
1125 where batch_id = p_batch_id
1126 and hedge_attribute_id = p_hedge_no;
1127
1128
1129 /*-------------------------------------------------------
1130 get deal count and total reference amount
1131 of the deals assigned to this hedge
1132 --------------------------------------------------------*/
1133 cursor deal_count(p_hedge_no in number) is
1134 select count(primary_code) deal_count,
1135 abs(sum(nvl(reference_amount,0))) deal_total
1136 from xtr_hedge_relationships
1137 where hedge_attribute_id = p_hedge_no
1138 and instrument_item_flag = 'U';
1139
1140 cursor cur_rnd(p_ccy in varchar2) is
1141 select rounding_factor
1142 from xtr_master_currencies_v
1143 where currency = p_ccy;
1144
1145 BEGIN
1146
1147 open cur_date;
1148 fetch cur_date into l_cb_end_date;
1149 close cur_date;
1150
1151 open prv_date;
1152 fetch prv_date into l_pb_end_date;
1153 close prv_date;
1154
1155 LOG_MSG('l_cb_end_date', l_cb_end_date);
1156 LOG_MSG('l_pb_end_date', l_pb_end_date);
1157
1158 for hdg_rec in get_hedges(l_cb_end_date, l_pb_end_date) Loop
1159
1160 LOG_MSG('status', 'Entered the main loop');
1161
1162 Declare
1163
1164 TYPE alloc_tbl_type is table of NUMBER INDEX BY BINARY_INTEGER;
1165 alloc_tbl alloc_tbl_type;
1166
1167 l_alloc_tbl NUMBER;
1168
1169 Begin
1170
1171 if hdg_rec.rec_source in (10, 20) then
1172
1173 l_from_date := hdg_rec.period_from; /* mini batch start date */
1174 l_to_date := hdg_rec.period_to; /* mini batch end date;
1175 will also be the test/result/siginificant date */
1176
1177 LOG_MSG('hdg_rec.period_from ', hdg_rec.period_from);
1178 LOG_MSG('hdg_rec.period_to', hdg_rec.period_to);
1179
1180 open hedge (hdg_rec.hedge_no);
1181 fetch hedge into l_test_method, l_min_tol, l_max_tol, l_excl_item;
1182 close hedge;
1183
1184 if l_excl_item = 'NONE' then
1185 l_item_prd := nvl(hdg_rec.unrealised_pl,0);
1186 elsif l_excl_item = 'TIME' then
1187 l_item_prd := nvl(hdg_rec.curr_gain_loss_amount,0);
1188 end if;
1189
1190 -- bug 4276958
1191 l_item_prv_cum := 0;
1192 l_inst_prv_cum :=0;
1193 l_eff_prv_cum := 0;
1194 l_ineff_prv_cum := 0;
1195 l_excl_prv_cum := 0;
1196 open get_cumu(hdg_rec.hedge_no, p_batch_id);
1197 fetch get_cumu into l_item_prv_cum, l_inst_prv_cum, l_eff_prv_cum,
1198 l_ineff_prv_cum, l_excl_prv_cum;
1199 close get_cumu;
1200
1201 LOG_MSG('Hedge No' , hdg_rec.hedge_no);
1202 LOG_MSG('source' , hdg_rec.rec_source);
1203 LOG_MSG('Test Method', l_test_method);
1204 LOG_MSG('Excl Item' , l_excl_item);
1205
1206 LOG_MSG('item unreal amt', l_item_prd);
1207
1208 l_inst_prd := 0;
1209 l_excl_prd := 0; -- Added for Bug 4214515
1210
1211 for deal_rec in get_deals(hdg_rec.hedge_no) Loop
1212
1213 l_deal_no := deal_rec.deal_no;
1214
1215 if hdg_rec.rec_source = 20 then
1216 l_deal_pct := 0;
1217 else
1218 l_deal_pct := deal_rec.deal_pct/100;
1219 end if;
1220
1221 LOG_MSG('Deal No' , deal_rec.deal_no);
1222 LOG_MSG('% Alloc' , deal_rec.deal_pct);
1223
1224 for reval_rec in get_reval loop
1225
1226 if l_excl_item = 'NONE' then
1227 l_reval_amt := nvl(reval_rec.unrealised_pl,0);
1228 l_amount_type := 'UNREAL';
1229 elsif l_excl_item = 'TIME' then
1230 l_reval_amt := nvl(reval_rec.curr_gain_loss_amount,0);
1231 l_amount_type := 'CCYUNRL';
1232 end if;
1233
1234 eff_table(reval_rec.revaluation_details_id) := l_reval_amt;
1235 l_alloc_tbl := NVL(l_alloc_tbl,0) + l_reval_amt * (l_deal_pct);
1236 l_inst_prd := nvl(l_inst_prd,0) + l_reval_amt * (l_deal_pct);
1237
1238 if l_excl_item = 'TIME' then
1239 l_excl_prd := nvl(l_excl_prd,0) + reval_rec.excluded_amt* (l_deal_pct);
1240 end if;
1241
1242 end loop;
1243
1244 alloc_tbl(deal_rec.deal_no) := l_alloc_tbl;
1245
1246 LOG_MSG('alloc_tbl('||deal_rec.deal_no||')', alloc_tbl(deal_rec.deal_no));
1247
1248 l_inst_cum := nvl(l_inst_prd,0) + nvl(l_inst_prv_cum,0);
1249 l_item_cum := nvl(l_item_prd,0) + nvl(l_item_prv_cum,0);
1250 l_excl_cum := nvl(l_excl_prd,0) + nvl(l_excl_prv_cum,0);
1251
1252 end loop; -- Deals Loop
1253
1254 l_gain_loss_ccy := get_gl_ccy(l_amount_type, hdg_rec.hedge_no, p_company);
1255 LOG_MSG('l_gain_loss_ccy', l_gain_loss_ccy);
1256
1257 open cur_rnd(l_gain_loss_ccy);
1258 fetch cur_rnd into l_round;
1259 close cur_rnd;
1260
1261 LOG_MSG('l_round', l_round);
1262
1263 l_item_prd := round(l_item_prd, l_round);
1264 l_inst_prd := round(l_inst_prd, l_round);
1265 l_excl_prd := round(l_excl_prd, l_round);
1266 l_inst_cum := round(l_inst_cum, l_round);
1267 l_item_cum := round(l_item_cum, l_round);
1268 l_excl_cum := round(l_excl_cum, l_round);
1269
1270 LOG_MSG('l_inst_prd', l_inst_prd);
1271 LOG_MSG('l_item_prd', l_item_prd);
1272 LOG_MSG('l_excl_prd', l_excl_prd);
1273 LOG_MSG('l_inst_cum', l_inst_cum);
1274 LOG_MSG('l_item_cum', l_item_cum);
1275 LOG_MSG('l_excl_cum', l_excl_cum);
1276
1277 /*************************************************************************
1278 Test Result
1279 *************************************************************************/
1280
1281 if l_test_method = 'SHORTCUT' then
1282
1283 l_result := 'PASS';
1284 l_pct_eff := 100;
1285
1286 elsif l_test_method = 'MANUAL' then
1287
1288 l_result := NULL;
1289 l_pct_eff := NULL;
1290
1291 elsif l_test_method in ('PEROFF', 'CUMOFF') then
1292
1293 if l_test_method = 'PEROFF' then
1294 l_inst_amt := l_inst_prd;
1295 l_item_amt := l_item_prd;
1296 elsif l_test_method = 'CUMOFF' then
1297 l_inst_amt := l_inst_cum;
1298 l_item_amt := l_item_cum;
1299 end if;
1300
1301 if nvl(l_inst_amt,0) = 0 and nvl(l_item_amt,0) = 0 then
1302 l_result := 'PASS';
1303 l_pct_eff := 100;
1304 elsif (nvl(l_inst_amt,0) = 0 or nvl(l_item_amt,0) = 0) then
1305 l_result := 'FAIL';
1306 l_pct_eff := 0;
1307 elsif (sign(l_inst_amt) = sign(l_item_amt)) then
1308 l_result := 'FAIL';
1309 l_pct_eff := ABS(l_inst_amt/l_item_amt)*100;
1310 -- l_pct_eff := 0;
1311 else
1312 l_pct_eff := ABS(l_inst_amt/l_item_amt)*100;
1313 if l_pct_eff < nvl(l_min_tol,0) OR l_pct_eff > nvl(l_max_tol,0) then
1314 l_result := 'FAIL';
1315 else
1316 l_result := 'PASS';
1317 end if;
1318 end if;
1319
1320 end if;
1321
1322 l_pct_eff := ROUND(l_pct_eff, 6);
1323
1324 LOG_MSG('Test Result', l_result);
1325 LOG_MSG('l_pct_eff', l_pct_eff);
1326
1327 If l_result is not NULL and l_result = 'FAIL' then
1328 fnd_message.set_name('XTR', 'XTR_DO_PROSPECTIVE_TEST_LOG');
1329 fnd_message.set_token('P_HEDGE_NO', hdg_rec.hedge_no);
1330 fnd_message.set_token('P_TEST_DATE', hdg_rec.period_to);
1331 fnd_file.put_line(fnd_file.log, fnd_message.get);
1332 end if;
1333
1334 -- l_gain_loss_ccy := get_gl_ccy(l_amount_type, hdg_rec.hedge_no, p_company);
1335
1336 LOG_MSG('l_gain_loss_ccy', l_gain_loss_ccy);
1337 LOG_MSG('l_amount_type', l_amount_type);
1338
1339 /*************************************************************************/
1340
1341 /*************************************************************************
1342 Determine the cumulative effective based on test result
1343 *************************************************************************/
1344
1345 if NVL(l_test_method, '@@@') = 'MANUAL' then
1346
1347 LOG_MSG('event', 'inserting MANUAL test - part of retro calculations');
1348
1349 /* This implies, not called from the form, but done
1350 as part of retro calcualtion for the first time */
1351
1352 select xtr_hedge_retro_tests_s.nextval into l_retro_test_id from dual;
1353
1354 insert into xtr_hedge_retro_tests
1355 (HEDGE_RETRO_TEST_ID, COMPANY_CODE, HEDGE_ATTRIBUTE_ID, RESULT_CODE,
1356 BATCH_ID, RESULT_DATE,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
1357 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,PROGRAM_ID, PROGRAM_LOGIN_ID,
1358 PROGRAM_APPLICATION_ID, REQUEST_ID, COMPLETE_FLAG,GAIN_LOSS_CCY,
1359 AMOUNT_TYPE, ITEM_PRD_GAIN_LOSS_AMT, INST_PRD_GAIN_LOSS_AMT,
1360 ITEM_CUM_GAIN_LOSS_AMT, INST_CUM_GAIN_LOSS_AMT, PCT_EFFECTIVE, EFF_PRD_GAIN_LOSS_AMT,
1361 INEFF_PRD_GAIN_LOSS_AMT, EXCLUDED_PRD_GAIN_LOSS_AMT, RECLASS_GAIN_LOSS_AMT,
1362 EFF_CUM_GAIN_LOSS_AMT, INEFF_CUM_GAIN_LOSS_AMT, EXCLUDED_CUM_GAIN_LOSS_AMT)
1363 values (l_retro_test_id, p_company, hdg_rec.hedge_no, NULL,
1364 p_batch_id, hdg_rec.period_to, fnd_global.user_id, sysdate, fnd_global.user_id,
1365 sysdate, fnd_global.login_id, fnd_global.conc_program_id, fnd_global.conc_login_id,
1366 fnd_global.prog_appl_id, fnd_global.conc_request_id,'N',l_gain_loss_ccy,
1367 l_amount_type, l_item_prd, l_inst_prd, l_item_cum, l_inst_cum, NULL, NULL,
1368 NULL, l_excl_prd, NULL, NULL, NULL, l_excl_cum);
1369
1370 elsif NVL(l_test_method, '@@@') <> 'MANUAL' then
1371
1372 If l_result = 'PASS' then
1373 if l_test_method = 'SHORTCUT' then
1374 l_eff_cum := l_inst_cum;
1375 else
1376 l_eff_cum := LEAST(ABS(l_inst_cum), ABS(l_item_cum))*SIGN(l_inst_cum);
1377 end if;
1378 Elsif l_result = 'FAIL' then
1379 l_eff_cum := l_eff_prv_cum;
1380 End If;
1381
1382 l_eff_prd := nvl(l_eff_cum,0) - nvl(l_eff_prv_cum,0);
1383 l_ineff_prd := nvl(l_inst_prd,0) - nvl(l_eff_prd,0);
1384
1385 LOG_MSG('Results:');
1386 LOG_MSG('l_eff_cum' , l_eff_cum);
1387 LOG_MSG('l_eff_prv_cum' , l_eff_prv_cum);
1388 LOG_MSG('l_eff_prd' , l_eff_prd);
1389 LOG_MSG('l_ineff_prd' , l_ineff_prd);
1390
1391 l_ineff_cum:= nvl(l_ineff_prv_cum,0) + nvl(l_ineff_prd,0);
1392
1393 select xtr_hedge_retro_tests_s.nextval into l_retro_test_id from dual;
1394
1395 LOG_MSG('event', 'inserting NON-MANUAL test - part of retro calculations');
1396
1397 insert into xtr_hedge_retro_tests
1398 (HEDGE_RETRO_TEST_ID, COMPANY_CODE, HEDGE_ATTRIBUTE_ID, RESULT_CODE,
1399 BATCH_ID, RESULT_DATE,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
1400 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,PROGRAM_ID, PROGRAM_LOGIN_ID,
1401 PROGRAM_APPLICATION_ID, REQUEST_ID, COMPLETE_FLAG, GAIN_LOSS_CCY,
1402 AMOUNT_TYPE, ITEM_PRD_GAIN_LOSS_AMT, INST_PRD_GAIN_LOSS_AMT,
1403 ITEM_CUM_GAIN_LOSS_AMT, INST_CUM_GAIN_LOSS_AMT, PCT_EFFECTIVE, EFF_PRD_GAIN_LOSS_AMT,
1404 INEFF_PRD_GAIN_LOSS_AMT, EXCLUDED_PRD_GAIN_LOSS_AMT, RECLASS_GAIN_LOSS_AMT,
1405 EFF_CUM_GAIN_LOSS_AMT, INEFF_CUM_GAIN_LOSS_AMT, EXCLUDED_CUM_GAIN_LOSS_AMT)
1406 values (l_retro_test_id, p_company, hdg_rec.hedge_no, l_result,
1407 p_batch_id, hdg_rec.period_to, fnd_global.user_id, sysdate, fnd_global.user_id,
1408 sysdate, fnd_global.login_id, fnd_global.conc_program_id, fnd_global.conc_login_id,
1409 fnd_global.prog_appl_id, fnd_global.conc_request_id,'Y',l_gain_loss_ccy,
1410 l_amount_type, l_item_prd, l_inst_prd, l_item_cum, l_inst_cum, l_pct_eff, l_eff_prd,
1411 l_ineff_prd, l_excl_prd, NULL, l_eff_cum, l_ineff_cum, l_excl_cum);
1412
1413 /*-------------------------------------------------------
1414 if successful, split the amounts among the deals
1415 --------------------------------------------------------*/
1416
1417 if sql%found then
1418 if (l_test_method <> 'MANUAL') then
1419 LOG_MSG('status', 'INSERT sucessful for xtr_hedge_retro_tests');
1420
1421 open deal_count(hdg_rec.hedge_no);
1422 fetch deal_count into l_deal_count, l_deal_total;
1423 close deal_count;
1424
1425 LOG_MSG('deal level amounts:');
1426 LOG_MSG('l_deal_count' , l_deal_count);
1427 LOG_MSG('l_deal_total' , l_deal_total);
1428 LOG_MSG('l_eff_prd' , l_eff_prd);
1429
1430
1431 for deal_rec in get_deals(hdg_rec.hedge_no) Loop
1432 LOG_MSG('hdg_rec.hedge_no', hdg_rec.hedge_no);
1433 LOG_MSG('deal_rec.deal_no', deal_rec.deal_no);
1434 LOG_MSG('alloc ref amt' , deal_rec.alloc_ref_amt);
1435 LOG_MSG('alloc ratio' , round(deal_rec.alloc_ref_amt/l_deal_total, l_round));
1436 LOG_MSG('Deal prd g/l' , l_eff_prd * (deal_rec.alloc_ref_amt/l_deal_total));
1437
1438 LOG_MSG('deal_rec.deal_no', deal_rec.deal_no);
1439 LOG_MSG(' alloc_tbl('||deal_rec.deal_no||') ', alloc_tbl(deal_rec.deal_no));
1440
1441 l_hd_ineff_prd := round(alloc_tbl(deal_rec.deal_no) -
1442 NVL(l_eff_prd * (deal_rec.alloc_ref_amt/l_deal_total),0), l_round);
1443
1444 LOG_MSG('Per Hedge-Per Deal: Ineff Amt', l_hd_ineff_prd);
1445
1446 open get_prev_ineff(hdg_rec.hedge_no, deal_rec.deal_no);
1447 fetch get_prev_ineff into l_hd_eff_prv_cum, l_hd_ineff_prv_cum ;
1448 close get_prev_ineff;
1449
1450 insert into xtr_deal_retro_tests
1451 (DEAL_RETRO_TEST_ID, HEDGE_RETRO_TEST_ID, BATCH_ID, HEDGE_ATTRIBUTE_ID, CREATED_BY, CREATION_DATE,
1452 LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, PROGRAM_ID, PROGRAM_LOGIN_ID,
1453 PROGRAM_APPLICATION_ID, REQUEST_ID, DEAL_NUMBER, AMOUNT_TYPE,
1454 EFF_PRD_GAIN_LOSS_AMT, INEFF_PRD_GAIN_LOSS_AMT, EXCLUDED_PRD_GAIN_LOSS_AMT,
1455 RECLASS_GAIN_LOSS_AMT, EFF_CUM_GAIN_LOSS_AMT, INEFF_CUM_GAIN_LOSS_AMT,
1456 EXCLUDED_CUM_GAIN_LOSS_AMT, RESULT_DATE)
1457
1458 values (xtr_deal_retro_tests_s.nextval, l_retro_test_id, p_batch_id, hdg_rec.hedge_no,
1459 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id,
1460 fnd_global.conc_program_id, fnd_global.conc_login_id, fnd_global.prog_appl_id,
1461 fnd_global.conc_request_id, deal_rec.deal_no, l_amount_type,
1462 round(l_eff_prd * (deal_rec.alloc_ref_amt/l_deal_total), l_round),
1463 l_hd_ineff_prd,
1464 round(l_excl_prd * (deal_rec.alloc_ref_amt/l_deal_total), l_round), NULL,
1465 round(l_eff_cum * (deal_rec.alloc_ref_amt/l_deal_total), l_round),
1466 nvl(l_hd_ineff_prv_cum,0) + nvl(l_hd_ineff_prd,0),
1467 round(l_excl_cum * (deal_rec.alloc_ref_amt/l_deal_total), l_round), hdg_rec.period_to
1468 );
1469
1470 if sql%found then
1471 LOG_MSG('status', 'INSERT successful for xtr_deal_retro_tests');
1472 else
1473 LOG_MSG('status', 'INSERT failed for xtr_deal_retro_tests');
1474 end if;
1475
1476 end loop;
1477 end if; -- end if for (l_test_method <> 'MANUAL') condition
1478
1479 else -- did not insert record into xtr_hedge_retro_tests
1480
1481 LOG_MSG('status', 'INSERT failed for xtr_hedge_retro_tests');
1482
1483 end if;
1484
1485 end if; --end if for if l_test_method = 'MANUAL' condition
1486
1487 /*************************************************************************/
1488
1489 elsif hdg_rec.rec_source = 30 then -- elseif for hdg_rec = 10,20 condition
1490 LOG_MSG('hdg_rec.rec_source', hdg_rec.rec_source);
1491 LOG_MSG('l_test_method', l_test_method);
1492
1493 if (NVL(l_test_method,'@@@') <> 'MANUAL') then
1494 LOG_MSG('Calling Reclassification..');
1495 CALC_RECLASS(p_company, p_batch_id, hdg_rec.hedge_no, hdg_rec.rec_id, hdg_rec.period_to);
1496 LOG_MSG('status', 'Returned from Reclassification.');
1497 end if;
1498 end if; -- end if for hdg_rec = 30 condition
1499
1500 End;
1501
1502 End Loop; -- Hedges Loop
1503
1504 /*-----------------------------------------------------------
1505 If everyting went fine then generate RETROET event
1506 ------------------------------------------------------------*/
1507
1508 LOG_MSG('status', 'generating RETROET event..');
1509
1510 ins_retro_event(P_BATCH_ID, 'RETROET');
1511
1512 LOG_MSG('status', 'generated RETROET event..');
1513
1514 Exception
1515 when others then
1516 LOG_MSG(SQLERRM);
1517 fnd_message.set_name('XTR', 'XTR_154');
1518 APP_EXCEPTION.raise_exception;
1519 END retro_main_calc;
1520
1521
1522 /****************************************************************/
1523 PROCEDURE RETRO_EFF_TEST (ERRBUF OUT NOCOPY VARCHAR2,
1524 RETCODE OUT NOCOPY VARCHAR2,
1525 P_COMPANY IN VARCHAR2,
1526 P_BATCH_ID IN NUMBER) IS
1527
1528 BEGIN
1529
1530 retro_main_calc(P_COMPANY, P_BATCH_ID);
1531
1532 END RETRO_EFF_TEST;
1533 /***************************************************************/
1534
1535
1536
1537
1538 /***************************************************************
1539 This procedure inserts a new RETROET event
1540 into XTR_BATCH_EVENTS table
1541 ***************************************************************/
1542 PROCEDURE ins_retro_event(p_batch_id IN NUMBER, p_event in VARCHAR2) is
1543
1544 Cursor CHK_BATCH_RUN is
1545 Select 'Y'
1546 From XTR_BATCH_EVENTS
1547 Where batch_id = p_batch_id
1548 and event_code = p_event;
1549
1550 l_event_id XTR_BATCH_EVENTS.BATCH_EVENT_ID%TYPE;
1551 l_sysdate DATE := trunc(sysdate);
1552 l_cur VARCHAR2(1);
1553
1554 Begin
1555 Open CHK_BATCH_RUN;
1556 Fetch CHK_BATCH_RUN into l_cur;
1557 If CHK_BATCH_RUN%FOUND then -- the current batch has run
1558 Close CHK_BATCH_RUN;
1559 Raise e_batch_been_run;
1560 Else
1561 Close CHK_BATCH_RUN;
1562
1563 select XTR_BATCH_EVENTS_S.NEXTVAL into l_event_id from DUAL;
1564
1565 Insert into XTR_BATCH_EVENTS(batch_event_id, batch_id, event_code, authorized,
1566 authorized_by, authorized_on, created_by, creation_date,
1567 last_updated_by, last_update_date, last_update_login)
1568 values(l_event_id, p_batch_id, p_event, 'N', null, null, fnd_global.user_id,
1569 l_sysdate, fnd_global.user_id, l_sysdate, fnd_global.login_id);
1570 End If;
1571
1572 EXCEPTION
1573 When e_batch_been_run then
1574 FND_MESSAGE.Set_Name('XTR', 'XTR_RETRO_CP_RUNNING');
1575 APP_EXCEPTION.raise_exception;
1576 End;
1577 /***************************************************************/
1578
1579 /*=====================================================================
1580 END: New objects for BUG 3378028 - FAS HEDGE ACCOUNTING PROJECT
1581 ======================================================================*/
1582
1583
1584
1585 PROCEDURE POPULATE_ITEMS(P_HEDGE_NO IN NUMBER) IS
1586
1587 TYPE HCurTyp IS REF CURSOR;
1588 hcur HCurTyp;
1589
1590 TYPE X_HEDGE_ATTRIBUTE_ID is table of XTR_HEDGE_ITEMS_TEMP.HEDGE_ATTRIBUTE_ID%type Index By BINARY_INTEGER;
1591 TYPE X_TRX_INV_ID is table of XTR_HEDGE_ITEMS_TEMP.TRX_INV_ID%type Index By BINARY_INTEGER;
1592 TYPE X_PAY_SCHEDULE_ID is table of XTR_HEDGE_ITEMS_TEMP.PAY_SCHEDULE_ID%type Index By BINARY_INTEGER;
1593 TYPE X_SOURCE_NAME is table of XTR_HEDGE_ITEMS_TEMP.SOURCE_NAME%type Index By BINARY_INTEGER;
1594 TYPE X_TRX_TYPE_NAME is table of XTR_HEDGE_ITEMS_TEMP.TRX_TYPE_NAME%type Index By BINARY_INTEGER;
1595 TYPE X_ORG_NAME is table of XTR_HEDGE_ITEMS_TEMP.ORG_NAME%type Index By BINARY_INTEGER;
1596 TYPE X_VEND_CUST_NAME is table of XTR_HEDGE_ITEMS_TEMP.VEND_CUST_NAME%type Index By BINARY_INTEGER;
1597 TYPE X_TRX_DATE is table of XTR_HEDGE_ITEMS_TEMP.TRX_DATE%type Index By BINARY_INTEGER;
1598 TYPE X_TRX_NUMBER is table of XTR_HEDGE_ITEMS_TEMP.TRX_NUMBER%type Index By BINARY_INTEGER;
1599 TYPE X_CURRENCY_CODE is table of XTR_HEDGE_ITEMS_TEMP.CURRENCY_CODE%type Index By BINARY_INTEGER;
1600 TYPE X_AMOUNT is table of XTR_HEDGE_ITEMS_TEMP.AMOUNT%type Index By BINARY_INTEGER;
1601 TYPE X_DUE_DATE is table of XTR_HEDGE_ITEMS_TEMP.DUE_DATE%type Index By BINARY_INTEGER;
1602 TYPE X_PCT_ALLOCATION is table of XTR_HEDGE_ITEMS_TEMP.PCT_ALLOCATION%type Index By BINARY_INTEGER;
1603 TYPE X_REFERENCE_AMOUNT is table of XTR_HEDGE_ITEMS_TEMP.REFERENCE_AMOUNT%type Index By BINARY_INTEGER;
1604 TYPE X_REQUEST_ID is table of XTR_HEDGE_ITEMS_TEMP.REQUEST_ID%type Index By BINARY_INTEGER;
1605 TYPE X_CREATED_BY is table of XTR_HEDGE_ITEMS_TEMP.CREATED_BY%type Index By BINARY_INTEGER;
1606 TYPE X_CREATION_DATE is table of XTR_HEDGE_ITEMS_TEMP.CREATION_DATE%type Index By BINARY_INTEGER;
1607 TYPE X_LAST_UPDATED_BY is table of XTR_HEDGE_ITEMS_TEMP.LAST_UPDATED_BY%type Index By BINARY_INTEGER;
1608 TYPE X_LAST_UPDATE_DATE is table of XTR_HEDGE_ITEMS_TEMP.LAST_UPDATE_DATE%type Index By BINARY_INTEGER;
1609 TYPE X_LAST_UPDATE_LOGIN is table of XTR_HEDGE_ITEMS_TEMP.LAST_UPDATE_LOGIN %type Index By BINARY_INTEGER;
1610
1611 HEDGE_ATTRIBUTE_ID X_HEDGE_ATTRIBUTE_ID;
1612 TRX_INV_ID X_TRX_INV_ID;
1613 PAY_SCHEDULE_ID X_PAY_SCHEDULE_ID;
1614 SOURCE_NAME X_SOURCE_NAME;
1615 TRX_TYPE_NAME X_TRX_TYPE_NAME;
1616 ORG_NAME X_ORG_NAME;
1617 VEND_CUST_NAME X_VEND_CUST_NAME;
1618 TRX_DATE X_TRX_DATE;
1619 TRX_NUMBER X_TRX_NUMBER;
1620 CURRENCY_CODE X_CURRENCY_CODE;
1621 AMOUNT X_AMOUNT;
1622 DUE_DATE X_DUE_DATE;
1623 PCT_ALLOCATION X_PCT_ALLOCATION;
1624 REFERENCE_AMOUNT X_REFERENCE_AMOUNT;
1625 REQUEST_ID X_REQUEST_ID;
1626 CREATED_BY X_CREATED_BY;
1627 CREATION_DATE X_CREATION_DATE;
1628 LAST_UPDATED_BY X_LAST_UPDATED_BY;
1629 LAST_UPDATE_DATE X_LAST_UPDATE_DATE;
1630 LAST_UPDATE_LOGIN X_LAST_UPDATE_LOGIN;
1631
1632 idx NUMBER := 1;
1633 cnt NUMBER := 1;
1634 l_data_source VARCHAR2(10);
1635
1636 l_query VARCHAR2(32000);
1637
1638 -- AR Related Variables and cursors Begin
1639
1640 cursor cur_rel(HEDGE_NO IN NUMBER) is
1641 SELECT PRIMARY_CODE TRX_INV_ID, SECONDARY_CODE PAY_SCHEDULE_ID, HEDGE_ATTRIBUTE_ID,
1642 PCT_ALLOCATION, REFERENCE_AMOUNT HEDGE_AMOUNT
1643 FROM XTR_HEDGE_RELATIONSHIPS
1644 WHERE HEDGE_ATTRIBUTE_ID = HEDGE_NO
1645 AND INSTRUMENT_ITEM_FLAG = 'I';
1646
1647 cursor trx(p_trx_id in number) is
1648 select trx_number, trx_date, batch_source_id, cust_trx_type_id,
1649 org_id, invoice_currency_code
1650 from ra_customer_trx_all
1651 where customer_trx_id = p_trx_id;
1652
1653 l_ar_trxtype_id NUMBER(15);
1654 l_customer_id NUMBER(15);
1655 l_ar_org_id NUMBER(15);
1656 l_batch_source_id NUMBER(15);
1657
1658 cursor ar_pmts(p_trx_id in number, p_paysch_id in number) is
1659 select due_date,customer_id
1660 from ar_payment_schedules_all
1661 where customer_trx_id = p_trx_id
1662 and payment_schedule_id = p_paysch_id;
1663
1664 cursor ar_trxtype is
1665 select name
1666 from ra_cust_trx_types_all
1667 where cust_trx_type_id = l_ar_trxtype_id
1668 and org_id = l_ar_org_id;
1669
1670 cursor batch is
1671 select name
1672 from ra_batch_sources_all
1673 where batch_source_id = l_batch_source_id
1674 and org_id = l_ar_org_id;
1675
1676
1677 /* BUG 3497802 Repalcing RA_CUSTOMERS with HZ_PARTIES
1678
1679 cursor customer(p_customer_id in number) is
1680 select customer_name
1681 from ra_customers
1682 where customer_id = p_customer_id;
1683
1684 */
1685
1686 cursor customer(p_customer_id in number) is
1687 select substrb(PARTY.PARTY_NAME,1,50)
1688 from hz_parties party, hz_cust_accounts cust_acct
1689 where cust_acct.party_id = party.party_id
1690 and cust_acct.cust_account_id = p_customer_id;
1691
1692 cursor ar_org is
1693 select name
1694 from hr_operating_units
1695 where organization_id = l_ar_org_id;
1696
1697 -- AR Related Variables and cursors End
1698
1699 -- AP Related Variables and cursors Begin
1700
1701 l_ap_invtype VARCHAR2(25);
1702 l_vendor_id NUMBER(15);
1703 l_ap_org_id NUMBER(15);
1704 l_ap_source AP_INVOICES_ALL.source%type;
1705
1706 cursor invoice(p_invoice_id in number) is
1707 select invoice_num, invoice_date, source, invoice_type_lookup_code,
1708 vendor_id, org_id, payment_currency_code
1709 from ap_invoices_all
1710 where invoice_id = p_invoice_id;
1711
1712 cursor ap_pmts(p_invoice_id in number, p_payment_num in number) is
1713 select due_date
1714 from ap_payment_schedules_all
1715 where invoice_id = p_invoice_id
1716 and payment_num = p_payment_num;
1717
1718 cursor ap_trxtype is
1719 select displayed_field
1720 from ap_lookup_codes k
1721 where lookup_type = 'INVOICE TYPE'
1722 and lookup_code = l_ap_invtype;
1723
1724 cursor ap_source is
1725 select displayed_field
1726 from ap_lookup_codes k
1727 where lookup_type = 'SOURCE'
1728 and lookup_code = l_ap_source;
1729
1730 cursor vendor(p_vendor_id in number) is
1731 select vendor_name
1732 from po_vendors
1733 where vendor_id = p_vendor_id;
1734
1735 cursor ap_org is
1736 select name
1737 from hr_operating_units
1738 where organization_id = l_ap_org_id;
1739
1740 -- AP Related Variables and cursors End
1741
1742 l_status XTR_HEDGE_ATTRIBUTES.HEDGE_STATUS%TYPE;
1743
1744 cursor hedge(hedge_no in number) is
1745 select hedge_status
1746 from xtr_hedge_attributes
1747 where hedge_attribute_id = hedge_no;
1748
1749 l_count NUMBER := 0;
1750
1751 Cursor cur_dtls(p_hedge_no in number) is
1752 select count(*) from xtr_hedge_relationships
1753 where hedge_attribute_id = p_hedge_no
1754 and instrument_item_flag = 'I';
1755
1756 l_iu_flag VARCHAR2(1) := 'I';
1757
1758 BEGIN
1759
1760 open hedge(P_HEDGE_NO);
1761 fetch hedge into l_status;
1762 close hedge;
1763
1764 open cur_dtls(P_HEDGE_NO);
1765 fetch cur_dtls into l_count;
1766 close cur_dtls;
1767
1768 If (l_status = 'CURRENT' and l_count = 0) then
1769 l_data_source := 'EXT';
1770 If Get_Source_Code(P_HEDGE_NO) = 'AP' then
1771 l_query := 'SELECT INVOICE_ID TRX_INV_ID, PAYMENT_NUM PAY_SCHEDULE_ID, SOURCE_NAME, TRX_TYPE_NAME, ORG_NAME,
1772 VENDOR_NAME VEND_CUST_NAME, TRX_DATE, TRX_NUMBER, CURRENCY_CODE, AMOUNT, DUE_DATE FROM
1773 XTR_AP_ORIG_TRX_V '||get_where_clause(p_hedge_no);
1774 Elsif Get_Source_Code(P_HEDGE_NO) = 'AR' then
1775 l_query := 'SELECT CUSTOMER_TRX_ID TRX_INV_ID, PAYMENT_SCHEDULE_ID PAY_SCHEDULE_ID, SOURCE_NAME, TRX_TYPE_NAME,
1776 ORG_NAME, CUSTOMER_NAME VEND_CUST_NAME, TRX_DATE, TRX_NUMBER, CURRENCY_CODE, AMOUNT, DUE_DATE FROM
1777 XTR_AR_ORIG_TRX_V '||get_where_clause(p_hedge_no);
1778 End If;
1779
1780 OPEN hcur FOR l_query;
1781 LOOP
1782 FETCH hcur INTO TRX_INV_ID(idx),PAY_SCHEDULE_ID(idx),SOURCE_NAME(idx),
1783 TRX_TYPE_NAME(idx),ORG_NAME(idx),VEND_CUST_NAME(idx),
1784 TRX_DATE(idx),TRX_NUMBER(idx),CURRENCY_CODE(idx),
1785 AMOUNT(idx),DUE_DATE(idx);
1786 HEDGE_ATTRIBUTE_ID(idx) := P_HEDGE_NO;
1787 PCT_ALLOCATION(idx) := NULL;
1788 REFERENCE_AMOUNT(idx) := NULL;
1789 EXIT WHEN hcur%NOTFOUND;
1790 REQUEST_ID(idx) := FND_GLOBAL.CONC_REQUEST_ID;
1791 CREATED_BY(idx) := FND_GLOBAL.USER_ID;
1792 CREATION_DATE(idx) := SYSDATE;
1793 LAST_UPDATED_BY(idx) := FND_GLOBAL.USER_ID;
1794 LAST_UPDATE_DATE(idx) := SYSDATE;
1795 LAST_UPDATE_LOGIN(idx) := FND_GLOBAL.LOGIN_ID;
1796 idx := idx+1;
1797 END LOOP;
1798 CLOSE hcur;
1799 Elsif (l_status in ('FULFILLED','FAILED') or (l_status = 'CURRENT' and l_count <> 0)) then
1800 l_data_source := 'XTR';
1801 l_query := 'SELECT PRIMARY_CODE TRX_INV_ID, SECONDARY_CODE PAY_SCHEDULE_ID, HEDGE_ATTRIBUTE_ID, AMOUNT,
1802 PCT_ALLOCATION, REFERENCE_AMOUNT FROM XTR_HEDGE_RELATIONSHIPS WHERE
1803 INSTRUMENT_ITEM_FLAG = :iu_flag AND HEDGE_ATTRIBUTE_ID = :HEDGE_NO';
1804
1805 OPEN hcur FOR l_query using l_iu_flag, p_hedge_no;
1806 LOOP
1807 FETCH hcur INTO TRX_INV_ID(cnt),PAY_SCHEDULE_ID(cnt),HEDGE_ATTRIBUTE_ID(cnt),AMOUNT(cnt),
1808 PCT_ALLOCATION(cnt),REFERENCE_AMOUNT(cnt);
1809
1810 EXIT WHEN hcur%NOTFOUND;
1811 If Get_Source_Code(P_HEDGE_NO) = 'AP' then
1812 Open invoice(TRX_INV_ID(cnt));
1813 Fetch invoice into TRX_NUMBER(cnt), TRX_DATE(cnt), l_ap_source,
1814 l_ap_invtype, l_vendor_id, l_ap_org_id, CURRENCY_CODE(cnt);
1815 if invoice%notfound then
1816 TRX_NUMBER(cnt) := NULL;
1817 TRX_DATE(cnt) := NULL;
1818 l_ap_source := NULL;
1819 l_ap_invtype := NULL;
1820 l_vendor_id := NULL;
1821 l_ap_org_id := NULL;
1822 CURRENCY_CODE(cnt) := NULL;
1823 end if;
1824 Close invoice;
1825
1826 Open ap_pmts(TRX_INV_ID(cnt),PAY_SCHEDULE_ID(cnt));
1827 Fetch ap_pmts into DUE_DATE(cnt);
1828 if ap_pmts%notfound then
1829 DUE_DATE(cnt) := NULL;
1830 end if;
1831 Close ap_pmts;
1832
1833 open ap_trxtype;
1834 fetch ap_trxtype into TRX_TYPE_NAME(cnt);
1835 if ap_trxtype%notfound then
1836 TRX_TYPE_NAME(cnt) := NULL;
1837 end if;
1838 close ap_trxtype;
1839
1840 open ap_source;
1841 fetch ap_source into SOURCE_NAME(cnt);
1842 if ap_source%NOTFOUND then
1843 select decode(l_ap_source,'Manual Invoice Entry',
1844 fnd_message.get_string('XTR','XTR_AP_SOURCE_MANUAL'),
1845 fnd_message.get_string('XTR','XTR_AP_SOURCE_OTHER'))
1846 INTO SOURCE_NAME(cnt) from dual;
1847 end if;
1848 close ap_source;
1849
1850 open vendor(l_vendor_id);
1851 fetch vendor into VEND_CUST_NAME(cnt);
1852 if vendor%notfound then
1853 VEND_CUST_NAME(cnt) := NULL;
1854 end if;
1855 close vendor;
1856
1857 open ap_org;
1858 fetch ap_org into ORG_NAME(cnt);
1859 if ap_org%notfound then
1860 ORG_NAME(cnt) := NULL;
1861 end if;
1862 close ap_org;
1863
1864 Elsif Get_Source_Code(P_HEDGE_NO) = 'AR' then
1865
1866 Open trx(TRX_INV_ID(cnt));
1867 Fetch trx into TRX_NUMBER(cnt), TRX_DATE(cnt), l_batch_source_id,
1868 l_ar_trxtype_id, l_ar_org_id, CURRENCY_CODE(cnt);
1869 if trx%notfound then
1870 TRX_NUMBER(cnt) := NULL;
1871 TRX_DATE(cnt) := NULL;
1872 l_batch_source_id := NULL;
1873 l_ar_trxtype_id := NULL;
1874 l_ar_org_id := NULL;
1875 CURRENCY_CODE(cnt) := NULL;
1876 end if;
1877 Close trx;
1878
1879 Open ar_pmts(TRX_INV_ID(cnt),PAY_SCHEDULE_ID(cnt));
1880 Fetch ar_pmts into DUE_DATE(cnt),l_customer_id;
1881 if ar_pmts%notfound then
1882 DUE_DATE(cnt) := NULL;
1883 l_customer_id := NULL;
1884 end if;
1885 Close ar_pmts;
1886
1887 open ar_trxtype;
1888 fetch ar_trxtype into TRX_TYPE_NAME(cnt);
1889 if ar_trxtype%notfound then
1890 TRX_TYPE_NAME(cnt) := NULL;
1891 end if;
1892 close ar_trxtype;
1893
1894 open batch;
1895 fetch batch into SOURCE_NAME(cnt);
1896 if batch%notfound then
1897 SOURCE_NAME(cnt) := NULL;
1898 end if;
1899 close batch;
1900
1901 open customer(l_customer_id);
1902 fetch customer into VEND_CUST_NAME(cnt);
1903 if customer%notfound then
1904 VEND_CUST_NAME(cnt) := NULL;
1905 end if;
1906 close customer;
1907
1908 open ar_org;
1909 fetch ar_org into ORG_NAME(cnt);
1910 if ar_org%notfound then
1911 ORG_NAME(cnt) := NULL;
1912 end if;
1913 close ar_org;
1914
1915 End If;
1916 REQUEST_ID(cnt) := FND_GLOBAL.CONC_REQUEST_ID;
1917 CREATED_BY(cnt) := FND_GLOBAL.USER_ID;
1918 CREATION_DATE(cnt) := SYSDATE;
1919 LAST_UPDATED_BY(cnt) := FND_GLOBAL.USER_ID;
1920 LAST_UPDATE_DATE(cnt) := SYSDATE;
1921 LAST_UPDATE_LOGIN(cnt) := FND_GLOBAL.LOGIN_ID;
1922 cnt := cnt+1;
1923 END LOOP;
1924 CLOSE hcur;
1925 End If;
1926
1927 IF (l_data_source = 'EXT' AND idx > 1) OR (l_data_source = 'XTR' AND cnt > 1) then
1928 FORALL J in TRX_INV_ID.FIRST..TRX_INV_ID.LAST
1929 insert into XTR_HEDGE_ITEMS_TEMP(HEDGE_ATTRIBUTE_ID,
1930 TRX_INV_ID,PAY_SCHEDULE_ID,SOURCE_NAME,
1931 TRX_TYPE_NAME, ORG_NAME,VEND_CUST_NAME,TRX_DATE,
1932 TRX_NUMBER,CURRENCY_CODE, AMOUNT,PCT_ALLOCATION, REFERENCE_AMOUNT,
1933 DUE_DATE,REQUEST_ID,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
1934 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
1935 )
1936 values (HEDGE_ATTRIBUTE_ID(j),
1937 TRX_INV_ID(j),PAY_SCHEDULE_ID(j),SOURCE_NAME(j),
1938 TRX_TYPE_NAME(j), ORG_NAME(j),VEND_CUST_NAME(j),TRX_DATE(j),
1939 TRX_NUMBER(j),CURRENCY_CODE(j), AMOUNT(j),PCT_ALLOCATION(j),REFERENCE_AMOUNT(j),
1940 DUE_DATE(j),REQUEST_ID(j),CREATED_BY(j), CREATION_DATE(j), LAST_UPDATED_BY(j),
1941 LAST_UPDATE_DATE(j), LAST_UPDATE_LOGIN(j)
1942 );
1943 END IF;
1944
1945 EXCEPTION
1946 WHEN OTHERS THEN
1947 APP_EXCEPTION.RAISE_EXCEPTION;
1948 END POPULATE_ITEMS;
1949
1950 PROCEDURE CALC_PCT_ALLOC (ERRBUF OUT NOCOPY VARCHAR2,
1951 RETCODE OUT NOCOPY VARCHAR2,
1952 P_HEDGE_NO IN NUMBER
1953 ) AS
1954 TYPE HCurTyp IS REF CURSOR;
1955
1956 hcur HCurTyp;
1957
1958 TYPE X_HEDGE_RELATIONSHIP_ID is table of xtr_hedge_relationships.HEDGE_RELATIONSHIP_ID%type Index By BINARY_INTEGER;
1959 TYPE X_HEDGE_ATTRIBUTE_ID is table of xtr_hedge_relationships.HEDGE_ATTRIBUTE_ID%type Index By BINARY_INTEGER;
1960 TYPE X_SOURCE_TYPE_ID is table of xtr_hedge_relationships.SOURCE_TYPE_ID%type Index By BINARY_INTEGER;
1961 TYPE X_PRIMARY_CODE is table of xtr_hedge_relationships.PRIMARY_CODE%type Index By BINARY_INTEGER;
1962 TYPE X_SECONDARY_CODE is table of xtr_hedge_relationships.SECONDARY_CODE%type Index By BINARY_INTEGER;
1963 TYPE X_INSTRUMENT_ITEM_FLAG is table of xtr_hedge_relationships.INSTRUMENT_ITEM_FLAG%type Index By BINARY_INTEGER;
1964 TYPE X_PCT_ALLOCATION is table of xtr_hedge_relationships.PCT_ALLOCATION%type Index By BINARY_INTEGER;
1965 TYPE X_REFERENCE_AMOUNT is table of xtr_hedge_relationships.REFERENCE_AMOUNT%type Index By BINARY_INTEGER;
1966 TYPE X_AMOUNT is table of xtr_hedge_relationships.AMOUNT%type Index By BINARY_INTEGER;
1967 TYPE X_CREATED_BY is table of xtr_hedge_relationships.CREATED_BY%type Index By BINARY_INTEGER;
1968 TYPE X_CREATION_DATE is table of xtr_hedge_relationships.CREATION_DATE%type Index By BINARY_INTEGER;
1969 TYPE X_LAST_UPDATED_BY is table of xtr_hedge_relationships.LAST_UPDATED_BY%type Index By BINARY_INTEGER;
1970 TYPE X_LAST_UPDATE_DATE is table of xtr_hedge_relationships.LAST_UPDATE_DATE%type Index By BINARY_INTEGER;
1971 TYPE X_LAST_UPDATE_LOGIN is table of xtr_hedge_relationships.LAST_UPDATE_LOGIN%type Index By BINARY_INTEGER;
1972
1973 HEDGE_RELATIONSHIP_ID X_HEDGE_RELATIONSHIP_ID;
1974 HEDGE_ATTRIBUTE_ID X_HEDGE_ATTRIBUTE_ID;
1975 SOURCE_TYPE_ID X_SOURCE_TYPE_ID;
1976 PRIMARY_CODE X_PRIMARY_CODE;
1977 SECONDARY_CODE X_SECONDARY_CODE;
1978 INSTRUMENT_ITEM_FLAG X_INSTRUMENT_ITEM_FLAG;
1979 PCT_ALLOCATION X_PCT_ALLOCATION;
1980 REFERENCE_AMOUNT X_REFERENCE_AMOUNT;
1981 AMOUNT X_AMOUNT;
1982 CREATED_BY X_CREATED_BY;
1983 CREATION_DATE X_CREATION_DATE;
1984 LAST_UPDATED_BY X_LAST_UPDATED_BY;
1985 LAST_UPDATE_DATE X_LAST_UPDATE_DATE;
1986 LAST_UPDATE_LOGIN X_LAST_UPDATE_LOGIN;
1987
1988 cursor pct(p_prim_code in VARCHAR2, p_sec_code in VARCHAR2) is
1989 select nvl(sum(pct_allocation),0) from
1990 xtr_hedge_relationships hr, xtr_hedge_attributes ha
1991 where hr.hedge_attribute_id = ha.hedge_attribute_id
1992 and ((ha.hedge_status in ('DESIGNATE','CURRENT','FULFILLED'))
1993 OR (hedge_status in ('FAILED','DEDESIGNATED') and ha.start_date <= ha.discontinue_date)
1994 )
1995 and primary_code = p_prim_code
1996 and secondary_code = p_sec_code;
1997
1998 l_pct NUMBER := 0;
1999
2000 cursor source(hedge_no in NUMBER) is
2001 select st.source_type_id, instrument_item_flag
2002 from xtr_hedge_criteria hc,
2003 xtr_source_types st
2004 where hedge_attribute_id = hedge_no
2005 and hc.from_value = st.source_code
2006 and criteria_type = 'BASIC'
2007 and criteria_code = 'ITEM_SOURCE';
2008
2009 l_source_id XTR_SOURCE_TYPES.SOURCE_TYPE_ID%TYPE;
2010 l_iu_flag XTR_SOURCE_TYPES.INSTRUMENT_ITEM_FLAG%TYPE;
2011
2012 idx NUMBER := 1;
2013 l_query VARCHAR2(32000);
2014 l_select VARCHAR2(350);
2015
2016 BEGIN
2017
2018 open source(P_HEDGE_NO);
2019 fetch source into l_source_id, l_iu_flag;
2020 close source;
2021
2022 If xtr_hedge_process_p.get_source_code(P_HEDGE_NO) = 'AP' then
2023 l_query := ' SELECT INVOICE_ID, PAYMENT_NUM, AMOUNT FROM XTR_AP_ORIG_TRX_V '||get_where_clause(P_HEDGE_NO);
2024 Elsif xtr_hedge_process_p.get_source_code(P_HEDGE_NO) = 'AR' then
2025 l_query := ' SELECT CUSTOMER_TRX_ID, PAYMENT_SCHEDULE_ID, AMOUNT FROM XTR_AR_ORIG_TRX_V '||get_where_clause(P_HEDGE_NO);
2026 End If;
2027
2028 OPEN hcur FOR l_query;
2029 LOOP
2030 FETCH hcur INTO PRIMARY_CODE(idx),SECONDARY_CODE(idx),AMOUNT(idx);
2031 EXIT WHEN hcur%NOTFOUND;
2032 idx := idx+1;
2033 END LOOP;
2034 CLOSE hcur;
2035
2036 If idx > 1 then
2037 FOR k in PRIMARY_CODE.FIRST..PRIMARY_CODE.LAST LOOP
2038 l_pct := 0;
2039 open pct(PRIMARY_CODE(k), SECONDARY_CODE(k));
2040 fetch pct into l_pct;
2041 close pct;
2042
2043 PCT_ALLOCATION(k) := 100-nvl(l_pct,0);
2044 REFERENCE_AMOUNT(k) := AMOUNT(k) * (PCT_ALLOCATION(k)/100);
2045 select xtr_hedge_relationships_s.nextval into HEDGE_RELATIONSHIP_ID(k) from dual;
2046
2047 HEDGE_ATTRIBUTE_ID(k) := p_hedge_no;
2048 SOURCE_TYPE_ID(k) := l_source_id;
2049 INSTRUMENT_ITEM_FLAG(k) := l_iu_flag;
2050 CREATED_BY(k) := fnd_global.user_id;
2051 CREATION_DATE(k) := sysdate;
2052 LAST_UPDATED_BY(k) := fnd_global.user_id;
2053 LAST_UPDATE_DATE(k) := sysdate;
2054 LAST_UPDATE_LOGIN(k) := fnd_global.login_id;
2055 END LOOP;
2056
2057 FORALL J in PRIMARY_CODE.FIRST..PRIMARY_CODE.LAST
2058 insert into xtr_hedge_relationships
2059 (HEDGE_RELATIONSHIP_ID,HEDGE_ATTRIBUTE_ID,SOURCE_TYPE_ID,PRIMARY_CODE,
2060 SECONDARY_CODE,INSTRUMENT_ITEM_FLAG,PCT_ALLOCATION,REFERENCE_AMOUNT,AMOUNT,
2061 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
2062 values (HEDGE_RELATIONSHIP_ID(j),HEDGE_ATTRIBUTE_ID(j),SOURCE_TYPE_ID(j),PRIMARY_CODE(j),
2063 SECONDARY_CODE(j),INSTRUMENT_ITEM_FLAG(j),PCT_ALLOCATION(j),REFERENCE_AMOUNT(j),
2064 AMOUNT(j),CREATED_BY(j),CREATION_DATE(j),LAST_UPDATED_BY(j),LAST_UPDATE_DATE(j),
2065 LAST_UPDATE_LOGIN(j));
2066 End If;
2067 EXCEPTION
2068 WHEN OTHERS THEN
2069 APP_EXCEPTION.RAISE_EXCEPTION;
2070 END CALC_PCT_ALLOC;
2071
2072
2073 /*CHECK_ADV_CRIT_SET_CONSISTENCY is a common procedure to insure the
2074 data is consistent. The count of each advanced item and condition_count
2075 must all be equal
2076 */
2077 PROCEDURE CHECK_ADV_CRIT_SET_CONSISTENCY(p_crit_set IN CRITERIA_SET_REC_TYPE) IS
2078 BEGIN
2079 if p_crit_set.condition_count <> p_crit_set.item.count or
2080 p_crit_set.condition_count <> p_crit_set.condition.count or
2081 p_crit_set.condition_count <> p_crit_set.value.count or
2082 p_crit_set.condition_count is null then
2083 raise e_invalid_criteria_set;
2084 end if;
2085 END CHECK_ADV_CRIT_SET_CONSISTENCY;
2086
2087
2088
2089 /* Local procedure to generate dynamic portion of where clause
2090 Warning: This procedure returns only additional AND clauses.
2091 You must add a 'WHERE' clause before using the results from
2092 this procedure.
2093 AR where clause is returned in the second parameter.
2094 AP where clause is returned in the third parameter.
2095 Do not confuse the order of these two.
2096 The dynamic portion is common code for ACTUAL and FORECAST hedge
2097 queries, so it was extracted to keep only one source of code.
2098 */
2099 PROCEDURE GENERATE_DYN_WHERE_CLAUSE(p_crit_set IN CRITERIA_SET_REC_TYPE,
2100 v_ar_where OUT NOCOPY VARCHAR2,
2101 v_ap_where OUT NOCOPY VARCHAR2) IS
2102
2103 v_ap_invtype_eq Varchar2(5000);
2104 v_ap_invtype_neq Varchar2(5000);
2105 v_ap_source_eq Varchar2(5000);
2106 v_ap_source_neq Varchar2(5000);
2107 v_ap_supplier_eq Varchar2(5000);
2108 v_ap_supplier_neq Varchar2(5000);
2109 v_ar_customer_eq Varchar2(5000);
2110 v_ar_customer_neq Varchar2(5000);
2111 v_ar_source_eq Varchar2(5000);
2112 v_ar_source_neq Varchar2(5000);
2113 v_ar_trxtype_eq Varchar2(5000);
2114 v_ar_trxtype_neq Varchar2(5000);
2115 v_opunit_eq Varchar2(5000);
2116 v_opunit_neq Varchar2(5000);
2117
2118 Cursor ar_source(p_source in VARCHAR2, p_company in VARCHAR2) is
2119 select '(BATCH_SOURCE_ID = '|| batch_source_id || ' AND ORG_ID = '||org_id||') OR ' source
2120 from ra_batch_sources_all bs,
2121 hr_operating_units ho,
2122 xtr_parties_v xp
2123 where bs.org_id = ho.organization_id
2124 and ho.set_of_books_id = (SELECT glle.ledger_id FROM gl_ledger_le_v glle
2125 WHERE glle.legal_entity_id = xp.legal_entity_id
2126 AND glle.ledger_category_code = 'PRIMARY') -- bug 4654775
2127 and xp.party_code = nvl(p_company,xp.party_code) --BUG 3002000 #7
2128 and bs.name = p_source;
2129
2130 Cursor ar_trxtype(p_trxtype in VARCHAR2, p_company in VARCHAR2) is
2131 select '(CUST_TRX_TYPE_ID = '|| cust_trx_type_id || ' AND ORG_ID = '||org_id||') OR ' trxtype
2132 from ra_cust_trx_types_all tt,
2133 hr_operating_units ho,
2134 xtr_parties_v xp
2135 where tt.org_id = ho.organization_id
2136 and ho.set_of_books_id = (SELECT glle.ledger_id FROM gl_ledger_le_v glle
2137 WHERE glle.legal_entity_id = xp.legal_entity_id
2138 AND glle.ledger_category_code = 'PRIMARY') -- bug 4654775
2139 and xp.party_code = nvl(p_company,xp.party_code) -- BUG 3002000 #7
2140 and tt.name = p_trxtype
2141 union all
2142 select '(CUST_TRX_TYPE_ID = -99999) OR ' trxtype
2143 from DUAL
2144 where substr(fnd_message.get_string('XTR','XTR_AR_TRX_TYPE_CASH'),1,20) = p_trxtype;
2145
2146 /* BUG 3497802 Repalcing RA_CUSTOMERS with HZ_PARTIES
2147
2148 cursor ar_customer(p_customer in VARCHAR2) is
2149 SELECT CUSTOMER_ID ||',' customer
2150 FROM RA_CUSTOMERS
2151 WHERE CUSTOMER_NAME = p_customer;
2152
2153 */
2154
2155 cursor ar_customer(p_customer in VARCHAR2) is
2156 SELECT cust_acct.cust_account_id||',' customer
2157 FROM hz_parties party, hz_cust_accounts cust_acct
2158 WHERE substrb(PARTY.PARTY_NAME,1,50) = p_customer
2159 and cust_acct.party_id = party.party_id;
2160
2161 l_ar_customer_eq VARCHAR2(5000);
2162 l_ar_customer_neq VARCHAR2(5000);
2163
2164 l_ar_source_eq VARCHAR2(5000);
2165 l_ar_trxtype_eq Varchar2(5000);
2166
2167 l_ar_source_neq VARCHAR2(5000);
2168 l_ar_trxtype_neq Varchar2(5000);
2169
2170
2171 BEGIN
2172
2173 CHECK_ADV_CRIT_SET_CONSISTENCY(p_crit_set);
2174
2175 For i in 0..p_crit_set.condition_count-1 Loop
2176 If p_crit_set.item(i) = 'AP_INVTYPE' and p_crit_set.condition(i) = '=' then
2177 if v_ap_invtype_eq is null then
2178 v_ap_invtype_eq := 'IN ('''||replace(p_crit_set.value(i),'''','''''')||'''';
2179 else
2180 v_ap_invtype_eq := v_ap_invtype_eq||','''||replace(p_crit_set.value(i),'''','''''')||'''';
2181 end if;
2182 Elsif p_crit_set.item(i) = 'AP_INVTYPE' and p_crit_set.condition(i) = '<>' then
2183 if v_ap_invtype_neq is null then
2184 v_ap_invtype_neq := 'NOT IN ('''||replace(p_crit_set.value(i),'''','''''')||'''';
2185 else
2186 v_ap_invtype_neq := v_ap_invtype_neq||','''||replace(p_crit_set.value(i),'''','''''')||'''';
2187 end if;
2188 Elsif p_crit_set.item(i) = 'AP_SOURCE' and p_crit_set.condition(i) = '=' then
2189 if v_ap_source_eq is null then
2190 v_ap_source_eq := 'IN ('''||replace(p_crit_set.value(i),'''','''''')||'''';
2191 else
2192 v_ap_source_eq := v_ap_source_eq||','''||replace(p_crit_set.value(i),'''','''''')||'''';
2193 end if;
2194 Elsif p_crit_set.item(i) = 'AP_SOURCE' and p_crit_set.condition(i) = '<>' then
2195 if v_ap_source_neq is null then
2196 v_ap_source_neq := 'NOT IN ('''||replace(p_crit_set.value(i),'''','''''')||'''';
2197 else
2198 v_ap_source_neq := v_ap_source_neq||','''||replace(p_crit_set.value(i),'''','''''')||'''';
2199 end if;
2200 Elsif p_crit_set.item(i) = 'AP_SUPPLIER' and p_crit_set.condition(i) = '=' then
2201 if v_ap_supplier_eq is null then
2202 v_ap_supplier_eq := 'IN ('''||replace(p_crit_set.value(i),'''','''''')||'''';
2203 else
2204 v_ap_supplier_eq := v_ap_supplier_eq||','''||replace(p_crit_set.value(i),'''','''''')||'''';
2205 end if;
2206 Elsif p_crit_set.item(i) = 'AP_SUPPLIER' and p_crit_set.condition(i) = '<>' then
2207
2208 if v_ap_supplier_neq is null then
2209 v_ap_supplier_neq := 'NOT IN ('''||replace(p_crit_set.value(i),'''','''''')||'''';
2210 else
2211 v_ap_supplier_neq := v_ap_supplier_neq||','''||replace(p_crit_set.value(i),'''','''''')||'''';
2212 end if;
2213 Elsif p_crit_set.item(i) = 'AR_CUSTOMER' and p_crit_set.condition(i) = '=' then
2214 Begin
2215 l_ar_customer_eq := NULL;
2216 for r in ar_customer(p_crit_set.value(i)) Loop
2217 l_ar_customer_eq := l_ar_customer_eq||r.customer;
2218 end loop;
2219 v_ar_customer_eq := v_ar_customer_eq||l_ar_customer_eq;
2220 End;
2221
2222 Elsif p_crit_set.item(i) = 'AR_CUSTOMER' and p_crit_set.condition(i) = '<>' then
2223 Begin
2224 l_ar_customer_neq := NULL;
2225 for r in ar_customer(p_crit_set.value(i)) Loop
2226 l_ar_customer_neq := l_ar_customer_neq||r.customer;
2227 end loop;
2228 v_ar_customer_neq := v_ar_customer_neq||l_ar_customer_neq;
2229 End;
2230
2231 Elsif p_crit_set.item(i) = 'AR_SOURCE' and p_crit_set.condition(i) = '=' then
2232 Begin
2233 l_ar_source_eq := NULL;
2234 for r in ar_source(p_crit_set.value(i), p_crit_set.company_code) Loop
2235 l_ar_source_eq := l_ar_source_eq||r.source;
2236 end loop;
2237 v_ar_source_eq := v_ar_source_eq||l_ar_source_eq;
2238 End;
2239 Elsif p_crit_set.item(i) = 'AR_SOURCE' and p_crit_set.condition(i) = '<>' then
2240 Begin
2241 l_ar_source_neq := NULL;
2242 for r in ar_source(p_crit_set.value(i), p_crit_set.company_code) Loop
2243 l_ar_source_neq := l_ar_source_neq||r.source;
2244 end loop;
2245 v_ar_source_neq := v_ar_source_neq||l_ar_source_neq;
2246 End;
2247 Elsif p_crit_set.item(i) = 'AR_TRXTYPE' and p_crit_set.condition(i) = '=' then
2248 Begin
2249 l_ar_trxtype_eq := NULL;
2250 for r in ar_trxtype(p_crit_set.value(i), p_crit_set.company_code) Loop
2251 l_ar_trxtype_eq := l_ar_trxtype_eq||r.trxtype;
2252 end loop;
2253 v_ar_trxtype_eq := v_ar_trxtype_eq||l_ar_trxtype_eq;
2254 End;
2255 Elsif p_crit_set.item(i) = 'AR_TRXTYPE' and p_crit_set.condition(i) = '<>' then
2256 Begin
2257 l_ar_trxtype_neq := NULL;
2258 for r in ar_trxtype(p_crit_set.value(i), p_crit_set.company_code) Loop
2259 l_ar_trxtype_neq := l_ar_trxtype_neq||r.trxtype;
2260 end loop;
2261 v_ar_trxtype_neq := v_ar_trxtype_neq||l_ar_trxtype_neq;
2262 End;
2263 Elsif p_crit_set.item(i) = 'COMN_OPUNIT' and p_crit_set.condition(i) = '=' then
2264 if v_opunit_eq is null then
2265 v_opunit_eq := 'IN ('||p_crit_set.value(i);
2266 else
2267 v_opunit_eq := v_opunit_eq ||','||p_crit_set.value(i);
2268 end if;
2269 Elsif p_crit_set.item(i) = 'COMN_OPUNIT' and p_crit_set.condition(i) = '<>' then
2270 if v_opunit_neq is null then
2271 v_opunit_neq := 'NOT IN ('||p_crit_set.value(i);
2272 else
2273 v_opunit_neq := v_opunit_neq ||','||p_crit_set.value(i);
2274 end if;
2275 End If;
2276 End Loop;
2277
2278 If v_ap_invtype_eq is not null then
2279 v_ap_invtype_eq:= ' and trx_type_id '||v_ap_invtype_eq||')';
2280 End If;
2281 If v_ap_invtype_neq is not null then
2282 v_ap_invtype_neq:= ' and trx_type_id '||v_ap_invtype_neq||')';
2283 End if;
2284 If v_ap_source_eq is not null then
2285 v_ap_source_eq:= ' and source '||v_ap_source_eq||')';
2286 End If;
2287 If v_ap_source_neq is not null then
2288 v_ap_source_neq:= ' and source '||v_ap_source_neq||')';
2289 End If;
2290
2291 If v_ap_supplier_eq is not null then
2292 v_ap_supplier_eq := ' AND VENDOR_ID '||v_ap_supplier_eq||')';
2293 End If;
2294
2295 If v_ap_supplier_neq is not null then
2296 v_ap_supplier_neq := ' AND VENDOR_ID '||v_ap_supplier_neq||')';
2297 End If;
2298
2299 If v_ar_customer_eq is not null then
2300 v_ar_customer_eq := ' AND CUSTOMER_ID IN ('||substr(v_ar_customer_eq,1,length(v_ar_customer_eq)-1)||') ';
2301 End If;
2302
2303 If v_ar_customer_neq is not null then
2304 v_ar_customer_neq := ' AND CUSTOMER_ID NOT IN ('||substr(v_ar_customer_neq,1,length(v_ar_customer_neq)-1)||') ';
2305 End If;
2306
2307 If v_ar_source_eq is not null then
2308 v_ar_source_eq := ' AND ('||substr(v_ar_source_eq,1,length(v_ar_source_eq)-4)||')';
2309 End IF;
2310
2311 If v_ar_source_neq is not null then
2312 v_ar_source_neq := ' AND NOT ('||substr(v_ar_source_neq,1,length(v_ar_source_neq)-4)||')';
2313 End IF;
2314
2315 If v_ar_trxtype_eq is not null then
2316 v_ar_trxtype_eq := ' AND ('||substr(v_ar_trxtype_eq,1,length(v_ar_trxtype_eq)-4)||')';
2317 End If;
2318
2319 If v_ar_trxtype_neq is not null then
2320 v_ar_trxtype_neq := ' AND NOT ('||substr(v_ar_trxtype_neq,1,length(v_ar_trxtype_neq)-4)||')';
2321 End If;
2322
2323 If v_opunit_eq is not null then
2324 v_opunit_eq := ' and org_id '||v_opunit_eq||')';
2325 End If;
2326 If v_opunit_neq is not null then
2327 v_opunit_neq := ' and org_id '||v_opunit_neq||')';
2328 End If;
2329
2330 If p_crit_set.source = 'AP' or p_crit_set.source = 'BOTH' then
2331 v_ap_where := v_ap_invtype_eq||v_ap_invtype_neq||v_ap_source_eq||v_ap_source_neq||
2332 v_ap_supplier_eq||v_ap_supplier_neq||v_opunit_eq||v_opunit_neq;
2333 End if;
2334 If p_crit_set.source = 'AR' or p_crit_set.source = 'BOTH' then
2335 v_ar_where := v_ar_customer_eq||v_ar_customer_neq||v_ar_source_eq||v_ar_source_neq||
2336 v_ar_trxtype_eq||v_ar_trxtype_neq||v_opunit_eq||v_opunit_neq;
2337 End If;
2338
2339 END GENERATE_DYN_WHERE_CLAUSE;
2340
2341
2342
2343
2344 /* GET_WHERE_CLAUSE is unique only to the FORECAST hedge project. This procedure
2345 requires an associated hedge number to generate the assocaited query */
2346 FUNCTION GET_WHERE_CLAUSE(P_HEDGE_NO IN NUMBER) RETURN VARCHAR2 IS
2347 v_where VARCHAR2(32000) := ' WHERE 1=1 ';
2348 v_ar_where VARCHAR2(32000);
2349 v_ap_where VARCHAR2(32000);
2350 p_crit_set CRITERIA_SET_REC_TYPE;
2351
2352
2353 cursor hedge_dtls(hedge_no in NUMBER) is
2354 select company_code, hedge_currency
2355 from xtr_hedge_attributes
2356 where hedge_attribute_id = hedge_no;
2357
2358 l_company XTR_PARTY_INFO.PARTY_CODE%TYPE;
2359 l_currency XTR_MASTER_CURRENCIES.CURRENCY%TYPE;
2360
2361 cursor source(hedge_no in NUMBER) is
2362 select from_value
2363 from xtr_hedge_criteria
2364 where hedge_attribute_id = hedge_no
2365 and criteria_type = 'BASIC'
2366 and criteria_code = 'ITEM_SOURCE';
2367
2368 l_source XTR_HEDGE_CRITERIA.FROM_VALUE%TYPE;
2369
2370 cursor basic_crit(hedge_no in NUMBER) is
2371 --select from_value, to_value
2372 select fnd_date.canonical_to_date(from_value), fnd_date.canonical_to_date(to_value)
2373 from xtr_hedge_criteria
2374 where hedge_attribute_id = hedge_no
2375 and criteria_type = 'BASIC'
2376 and criteria_code = 'TRX_DATES';
2377
2378 --l_from_value XTR_HEDGE_CRITERIA.FROM_VALUE%TYPE;
2379 --l_to_value XTR_HEDGE_CRITERIA.TO_VALUE%TYPE;
2380
2381 l_from_value DATE;
2382 l_to_value DATE;
2383
2384 cursor adv_crit(hedge_no in NUMBER) is
2385 select criteria_code,operator,from_value
2386 from xtr_hedge_criteria
2387 where criteria_type = 'ADVANCED'
2388 and hedge_attribute_id = hedge_no;
2389
2390 Begin
2391 open hedge_dtls(P_HEDGE_NO);
2392 fetch hedge_dtls into l_company, l_currency;
2393 close hedge_dtls;
2394 open source(P_HEDGE_NO);
2395 fetch source into l_source;
2396 close source;
2397 open basic_crit(P_HEDGE_NO);
2398 fetch basic_crit into l_from_value, l_to_value;
2399 close basic_crit;
2400 v_where := v_where||' And Company_Code = '''||replace(l_company,'''','''''') ||'''';
2401 v_where := v_where||' And Currency_Code = '''||replace(l_currency,'''','''''')||'''';
2402 v_where := v_where||' AND (trx_date >= '''
2403 ||l_from_value
2404 ||''' AND trx_date <= '''
2405 ||l_to_value
2406 ||''')';
2407
2408 p_crit_set.company_code := l_company;
2409 p_crit_set.currency := l_currency;
2410 p_crit_set.source := l_source;
2411
2412 p_crit_set.condition_count := 0;
2413 for rec in adv_crit(p_hedge_no) loop
2414 p_crit_set.item(p_crit_set.condition_count) := rec.criteria_code;
2415 p_crit_set.condition(p_crit_set.condition_count) := rec.operator;
2416 p_crit_set.value(p_crit_set.condition_count) := rec.from_value;
2417
2418 p_crit_set.condition_count := p_crit_set.condition_count+1;
2419
2420 end loop;
2421
2422 GENERATE_DYN_WHERE_CLAUSE(p_crit_set,v_ar_where,v_ap_where);
2423
2424
2425 If l_source = 'AP' then
2426 v_where := v_where||v_ap_where;
2427 Elsif l_source = 'AR' then
2428 v_where := v_where||v_ar_where;
2429 end if;
2430
2431 RETURN (v_where);
2432
2433 END GET_WHERE_CLAUSE;
2434
2435 FUNCTION GET_SOURCE_CODE(P_HEDGE_NO IN NUMBER) RETURN VARCHAR2 IS
2436
2437 cursor source(hedge_no in NUMBER) is
2438 select st.source_code
2439 from xtr_hedge_criteria hc,
2440 xtr_source_types st
2441 where hedge_attribute_id = hedge_no
2442 and hc.from_value = st.source_code
2443 and criteria_type = 'BASIC'
2444 and criteria_code = 'ITEM_SOURCE';
2445
2446 l_source_code XTR_SOURCE_TYPES.SOURCE_CODE%TYPE;
2447
2448 BEGIN
2449 open source(P_HEDGE_NO);
2450 fetch source into l_source_code;
2451 close source;
2452
2453 RETURN(l_source_code);
2454
2455 END GET_SOURCE_CODE;
2456
2457 FUNCTION GET_REQUEST_STATUS(P_REQUEST_ID IN NUMBER) RETURN VARCHAR2 IS
2458
2459 call_status boolean;
2460 rphase varchar2(30);
2461 rstatus varchar2(30);
2462 dphase varchar2(30);
2463 dstatus varchar2(30);
2464 message varchar2(240);
2465 l_request_id NUMBER(15);
2466
2467 BEGIN
2468 l_request_id := P_REQUEST_ID;
2469 call_status := FND_CONCURRENT.GET_REQUEST_STATUS(l_request_id, '', '',
2470 rphase,rstatus,dphase,dstatus, message);
2471 return(dphase);
2472
2473 END GET_REQUEST_STATUS;
2474
2475
2476
2477
2478 /* MINIMUM_DEFAULT is a procedure to ensure that loaded criteria sets have
2479 at least the bare bones minimum data
2480 */
2481 PROCEDURE MINIMUM_DEFAULT(p_crit_set IN OUT NOCOPY CRITERIA_SET_REC_TYPE) IS
2482 BEGIN
2483
2484 if (p_crit_set.currency is null) then
2485 SELECT PARAM_VALUE
2486 INTO p_crit_set.currency
2487 FROM xtr_pro_param
2488 WHERE param_name = 'SYSTEM_FUNCTIONAL_CCY';
2489 p_crit_set.currency := nvl(p_crit_set.currency,'USD');
2490 end if;
2491
2492 p_crit_set.source := nvl(p_crit_set.source ,'AR');
2493 p_crit_set.factor := nvl(p_crit_set.factor ,'1');
2494 p_crit_set.ar_unpld := nvl(p_crit_set.ar_unpld,'N');
2495 p_crit_set.ap_unpld := nvl(p_crit_set.ap_unpld,'N');
2496 if (p_crit_set.source <> 'AR') then
2497 p_crit_set.discount := nvl(p_crit_set.discount,'NONE');
2498 end if;
2499 p_crit_set.condition_count := nvl(p_crit_set.condition_count, 0);
2500 END MINIMUM_DEFAULT;
2501
2502
2503 /* Generate_common_date_clause reduces errors by consolidating the shared piece
2504 of code that is common to building the date where clause
2505 */
2506 PROCEDURE GENERATE_COMMON_DATE_CLAUSE(p_crit_set CRITERIA_SET_REC_TYPE,
2507 v_due_date_ar_and IN OUT NOCOPY VARCHAR2,
2508 v_due_date_ap_and IN OUT NOCOPY VARCHAR2) IS
2509 v_date_comparer VARCHAR2(80);
2510 BEGIN
2511 If p_crit_set.discount='MAX' then
2512 v_date_comparer := 'Max_Discounted_Date';
2513 elsif p_crit_set.discount='MIN' then
2514 v_date_comparer := 'Min_Discounted_Date';
2515 else
2516 v_date_comparer := 'Min_Due_Date';
2517 end if;
2518
2519 -- v_due_date_ar_and is for AR
2520 If p_crit_set.DUE_DATE_FROM IS NOT NULL AND p_crit_set.DUE_DATE_TO IS NOT NULL then
2521 v_due_date_ar_and := ' AND (min_due_date is null OR min_due_date >= to_date('''
2522 ||p_crit_set.DUE_DATE_FROM
2523 ||''',''RRRR/MM/DD'') AND min_due_date <= to_date('''
2524 ||p_crit_set.DUE_DATE_TO
2525 ||''',''RRRR/MM/DD''))';
2526 Elsif p_crit_set.DUE_DATE_FROM IS NOT NULL AND p_crit_set.DUE_DATE_TO IS NULL then
2527 v_due_date_ar_and := ' AND (min_due_date is null OR min_due_date >= to_date('''
2528 ||p_crit_set.DUE_DATE_FROM
2529 ||''',''RRRR/MM/DD''))';
2530 Elsif p_crit_set.DUE_DATE_FROM IS NULL AND p_crit_set.DUE_DATE_TO IS NOT NULL then
2531 v_due_date_ar_and := ' AND (min_due_date is null OR min_due_date <= to_date('''
2532 ||p_crit_set.DUE_DATE_TO
2533 ||''',''RRRR/MM/DD''))';
2534 Else
2535 v_due_date_ar_and := ' AND (1=1) ';
2536 End If;
2537
2538 -- v_due_date_ap_and is for AP
2539 If p_crit_set.DUE_DATE_FROM IS NOT NULL AND p_crit_set.DUE_DATE_TO IS NOT NULL then
2540 v_due_date_ap_and := ' AND ('||v_date_comparer||' is null OR '||v_date_comparer||' >= to_date('''
2541 ||p_crit_set.DUE_DATE_FROM
2542 ||''',''RRRR/MM/DD'') AND '||v_date_comparer||' <= to_date('''
2543 ||p_crit_set.DUE_DATE_TO
2544 ||''',''RRRR/MM/DD''))';
2545 Elsif p_crit_set.DUE_DATE_FROM IS NOT NULL AND p_crit_set.DUE_DATE_TO IS NULL then
2546 v_due_date_ap_and := ' AND ('||v_date_comparer||' is null OR '||v_date_comparer||' >= to_date('''
2547 ||p_crit_set.DUE_DATE_FROM
2548 ||''',''RRRR/MM/DD''))';
2549 Elsif p_crit_set.DUE_DATE_FROM IS NULL AND p_crit_set.DUE_DATE_TO IS NOT NULL then
2550 v_due_date_ap_and := ' AND ('||v_date_comparer||' is null OR '||v_date_comparer||' <= to_date('''
2551 ||p_crit_set.DUE_DATE_TO
2552 ||''',''RRRR/MM/DD''))';
2553 Else
2554 v_due_date_ap_and := ' AND (1=1) ';
2555 End If;
2556
2557 END GENERATE_COMMON_DATE_CLAUSE;
2558
2559
2560
2561 /* Generate_query_from_details is the procedure called from the find positions form
2562 This generates a query from the details provided in the criteria_set_rec_type
2563 p_crit_set.criteria_set value is ignored in this procedure and has no meaning.
2564 This code was essentially extracted from the FIND_ARAP forms procedure
2565 */
2566 PROCEDURE GENERATE_QUERY_FROM_DETAILS(p_crit_set CRITERIA_SET_REC_TYPE, p_query OUT NOCOPY VARCHAR2,
2567 p_where OUT NOCOPY VARCHAR2,
2568 p_where1 OUT NOCOPY VARCHAR2,
2569 p_where2 OUT NOCOPY VARCHAR2) IS
2570 v_select Varchar2(5000) := 'Select company_code company, sob_currency_code sob_curreny, k.meaning source, sum(amount) amount from ';
2571 v_select1 Varchar2(10000) :=
2572 'Select company_code, sob_currency_code sob_currency, k.meaning source, sum( '
2573 ||' decode( '
2574 ||' nvl('
2575 ||''''
2576 ||p_crit_set.Discount
2577 ||''''
2578 ||', ''NONE''), '
2579 ||' ''NONE'', Amount, '
2580 ||' ''MAX'', Max_Discounted_Amount, '
2581 ||' ''MIN'', Min_Discounted_Amount '
2582 ||')'
2583 ||') amount from ';
2584 v_tablename1 Varchar2(200) := ' xtr_ar_open_trx_v, fnd_lookups k '; --If modified, do not forget
2585 v_tablename2 Varchar2(200) := ' xtr_ap_open_trx_v, fnd_lookups k '; --to modify code in body as well
2586 v_where Varchar2(5000) := ' Where k.lookup_type = ''XTR_HEDGE_SOURCES'' ';
2587 v_ar_where Varchar2(5000);
2588 v_ap_where Varchar2(5000);
2589 v_and Varchar2(15000) := ' And k.lookup_code = ';
2590 v_and1 Varchar2(15000);
2591 v_and2 Varchar2(15000);
2592 v_due_date_ar_and Varchar2(2000);
2593 v_due_date_ap_and Varchar2(2000);
2594 v_group Varchar2(15000) := ' Group by company_code,sob_currency_code,k.meaning ';
2595 v_order Varchar2(15000) := ' Order by 1,2,3,4 ';
2596 v_query Varchar2(32000);
2597 v_both Char(1) := 'N';
2598 v_and1_flag Char(1) := 'N';
2599 v_org_flag Char(1);
2600
2601 v_company VARCHAR2(7);
2602 v_source VARCHAR2(80);
2603 v_amount NUMBER;
2604 v_date_comparer VARCHAR2(80):='min_due_date';
2605
2606 source_cursor INTEGER;
2607 destination_cursor INTEGER;
2608 ignore INTEGER;
2609 native constant INTEGER := 1;
2610
2611 dec_pos NUMBER;
2612 l_ar_tot NUMBER;
2613 l_ap_tot NUMBER;
2614 l_tot1 NUMBER;
2615
2616 BEGIN
2617 if (p_crit_set.ar_unpld = 'N') then
2618 v_tablename1:=' xtr_ar_open_apld_trx_v, fnd_lookups k ';
2619 end if;
2620 if (p_crit_set.ap_unpld = 'N') then
2621 v_tablename2:=' xtr_ap_open_apld_trx_v, fnd_lookups k ';
2622 end if;
2623 If p_crit_set.Source = 'AR' then
2624 v_select := v_select||v_tablename1;
2625 v_and := v_and||'''AR''';
2626 Elsif p_crit_set.Source = 'AP' then
2627 v_select := v_select1||v_tablename2;
2628 v_and := v_and||'''AP''';
2629 Else
2630 v_both := 'Y';
2631 v_and1 := v_and||'''AR''';
2632 v_and2 := v_and||'''AP''';
2633 End if;
2634
2635 GENERATE_COMMON_DATE_CLAUSE(p_crit_set,v_due_date_ar_and,v_due_date_ap_and);
2636
2637 if nvl(p_crit_set.ar_unpld,'N') = 'N' then
2638 v_due_date_ar_and := v_due_date_ar_and || ' AND APPLIED_TRX=''Y'' ';
2639 end if;
2640 if nvl(p_crit_set.ap_unpld,'N') = 'N' then
2641 v_due_date_ap_and := v_due_date_ap_and || ' AND APPLIED_TRX=''Y'' ';
2642 end if;
2643
2644
2645 If v_both = 'N' then
2646 v_and := v_and||' And Currency_Code = '''||replace(p_crit_set.currency,'''','''''')||'''';
2647 If p_crit_set.Company_Code is not null then
2648 v_and := v_and||' And Company_Code = '''||replace(p_crit_set.company_code,'''','''''')||'''';
2649 End if;
2650 if p_crit_set.sob_currency is not null then
2651 v_and := v_and||' And sob_currency_code = '''||replace(p_crit_set.sob_currency,'''','''''')||'''';
2652 end if;
2653 -- v_and := v_and||v_due_date_and;
2654
2655 Else
2656 v_and1 := v_and1||' and Currency_Code = '''||replace(p_crit_set.currency,'''','''''')||'''';
2657 v_and2 := v_and2||' and Currency_Code = '''||replace(p_crit_set.currency,'''','''''')||'''';
2658
2659 If p_crit_set.Company_Code is not null then
2660 v_and1 := v_and1||' And Company_Code = '''||replace(p_crit_set.company_code,'''','''''')||'''';
2661 v_and2 := v_and2||' And Company_Code = '''||replace(p_crit_set.company_Code,'''','''''')||'''';
2662 End if;
2663 if p_crit_set.sob_currency is not null then
2664 v_and1 := v_and1||' And Sob_currency_code = '''||replace(p_crit_set.sob_currency,'''','''''')||'''';
2665 v_and2 := v_and2||' And Sob_currency_code = '''||replace(p_crit_set.sob_currency,'''','''''')||'''';
2666 end if;
2667
2668 -- v_and1 := v_and1||v_due_date_and;
2669 -- v_and2 := v_and2||v_due_date_and;
2670 End if;
2671
2672
2673
2674 GENERATE_DYN_WHERE_CLAUSE(p_crit_set,v_ar_where,v_ap_where);
2675
2676
2677 if p_crit_set.source = 'AR' then
2678 v_and := v_and||v_due_date_ar_and||v_ar_where;
2679 elsif p_crit_set.source = 'AP' then
2680 v_and := v_and||v_due_date_ap_and||v_ap_where;
2681 else
2682 v_and1 := v_and1||v_due_date_ar_and||v_ar_where;
2683 v_and2 := v_and2||v_due_date_ap_and||v_ap_where;
2684 end if;
2685
2686 If v_both = 'N' then
2687 p_query := v_Select||v_where||v_and||v_group||v_order;
2688 p_where := substr(v_and, instr(v_and, ' Currency_Code'));
2689 Else
2690 p_query := v_Select||v_tablename1||v_where||v_and1||v_group||' union '||
2691 v_Select1||v_tablename2||v_where||v_and2||v_group||v_order;
2692 p_where1 := substr(v_and1, instr(v_and1, ' Currency_Code'));
2693 p_where2 := substr(v_and2, instr(v_and2, ' Currency_Code'));
2694 End if;
2695
2696
2697 END GENERATE_QUERY_FROM_DETAILS;
2698
2699
2700
2701
2702 /* GET_HOAPR_REPORT_PARAMETERS is the procedure called from the
2703 position - outstanding receivables / payables report.
2704 It takes a criteria set and returns a where clause for the AR table
2705 and the AP table and the respective tables to pull the information from.
2706 p_crit_set.criteria_set value is ignored in this procedure and has no meaning.
2707 This code was essentially extracted from the FIND_ARAP forms procedure
2708 */
2709 PROCEDURE GET_HOAPR_REPORT_PARAMETERS(p_criteria_set_name VARCHAR2,
2710 p_criteria_set_owner VARCHAR2,
2711 p_source IN OUT NOCOPY xtr_hedge_criteria.from_value%TYPE,
2712 p_currency IN OUT NOCOPY xtr_hedge_criteria.from_value%TYPE,
2713 p_company_code IN OUT NOCOPY xtr_hedge_criteria.from_value%TYPE,
2714 p_sob_currency IN OUT NOCOPY xtr_hedge_criteria.from_value%TYPE,
2715 p_discount IN OUT NOCOPY xtr_hedge_criteria.from_value%TYPE,
2716 p_factor IN OUT NOCOPY xtr_hedge_criteria.from_value%TYPE,
2717 p_due_date_from IN OUT NOCOPY xtr_hedge_criteria.from_value%TYPE,
2718 p_due_date_to IN OUT NOCOPY xtr_hedge_criteria.to_value%TYPE,
2719 p_ar_unpld IN OUT NOCOPY xtr_hedge_criteria.from_value%TYPE,
2720 p_ap_unpld IN OUT NOCOPY xtr_hedge_criteria.from_value%TYPE,
2721 p_ar_from OUT NOCOPY VARCHAR2,
2722 p_ap_from OUT NOCOPY VARCHAR2,
2723 p_ar_where OUT NOCOPY VARCHAR2,
2724 p_ap_where OUT NOCOPY VARCHAR2) IS
2725
2726 p_crit_set CRITERIA_SET_REC_TYPE;
2727 v_date_comparer VARCHAR2(80);
2728 v_due_date_ar_and VARCHAR2(250);
2729 v_due_date_ap_and VARCHAR2(250);
2730 v_dyn_ar_where VARCHAR2(15000);
2731 v_dyn_ap_where VARCHAR2(15000);
2732 n_dummy NUMBER;
2733
2734
2735 BEGIN
2736
2737 if (p_criteria_set_name is not null) then
2738 select count(*)
2739 into n_dummy
2740 from xtr_hedge_criteria
2741 where criteria_set = p_criteria_set_name
2742 and criteria_set_owner = p_criteria_set_owner;
2743
2744 if (n_dummy > 0) then
2745 p_crit_set.criteria_set_owner := p_criteria_set_owner;
2746 end if;
2747
2748 p_crit_set.criteria_set := p_criteria_set_name;
2749 load_criteria_set(p_crit_set);
2750 end if;
2751 p_crit_set.source := nvl(p_source ,p_crit_set.source);
2752 p_crit_set.currency := nvl(p_currency ,p_crit_set.currency);
2753 p_crit_set.company_code := nvl(p_company_code ,p_crit_set.company_code);
2754 p_crit_set.sob_currency := nvl(p_sob_currency ,p_crit_set.sob_currency);
2755 p_crit_set.discount := nvl(p_discount ,p_crit_set.discount);
2756 p_crit_set.factor := nvl(p_factor ,p_crit_set.factor);
2757 p_crit_set.due_date_from := nvl(p_due_date_from,p_crit_set.due_date_from);
2758 p_crit_set.due_date_to := nvl(p_due_date_to ,p_crit_set.due_date_to );
2759 p_crit_set.ar_unpld := nvl(p_ar_unpld ,p_crit_set.ar_unpld);
2760 p_crit_set.ap_unpld := nvl(p_ar_unpld ,p_crit_set.ap_unpld);
2761
2762 MINIMUM_DEFAULT(p_crit_set);
2763
2764 p_source := p_crit_set.source;
2765 p_currency := p_crit_set.currency;
2766 p_company_code := p_crit_set.company_code;
2767 p_sob_currency := p_crit_set.sob_currency;
2768 p_discount := p_crit_set.discount;
2769 p_factor := p_crit_set.factor;
2770 p_due_date_from := p_crit_set.due_date_from;
2771 p_due_date_to := p_crit_set.due_date_to;
2772 p_ar_unpld := p_crit_set.ar_unpld;
2773 p_ap_unpld := p_crit_set.ap_unpld;
2774
2775
2776 if (p_crit_set.ar_unpld='Y') then
2777 p_ar_from := 'XTR_AR_OPEN_TRX_V';
2778 else
2779 p_ar_from := 'XTR_AR_OPEN_APLD_TRX_V';
2780 end if;
2781
2782 if (p_crit_set.ap_unpld='Y') then
2783 p_ap_from := 'XTR_AP_OPEN_TRX_V';
2784 else
2785 p_ap_from := 'XTR_AP_OPEN_APLD_TRX_V';
2786 end if;
2787
2788 if (p_crit_set.source not in ('AR','BOTH')) then
2789 p_ar_from := 'XTR_AR_OPEN_APLD_TRX_V';
2790 p_ar_where := ' AND 1=2 ';
2791 end if;
2792
2793 if (p_crit_set.source not in ('AP','BOTH')) then
2794 p_ap_from := 'XTR_AP_OPEN_APLD_TRX_V';
2795 p_ap_where := ' AND 1=2 ';
2796 end if;
2797
2798 GENERATE_COMMON_DATE_CLAUSE(p_crit_set,v_due_date_ar_and,v_due_date_ap_and);
2799
2800 p_ar_where := p_ar_where||' and Currency_Code = '''||replace(p_crit_set.currency,'''','''''')||'''';
2801 p_ap_where := p_ap_where||' and Currency_Code = '''||replace(p_crit_set.currency,'''','''''')||'''';
2802
2803 If p_crit_set.Company_Code is not null then
2804 p_ar_where := p_ar_where||' And Company_Code = '''||replace(p_crit_set.company_code,'''','''''')||'''';
2805 p_ap_where := p_ap_where||' And Company_Code = '''||replace(p_crit_set.company_Code,'''','''''')||'''';
2806 End if;
2807 if p_crit_set.sob_currency is not null then
2808 p_ar_where := p_ar_where||' And Sob_currency_code = '''||replace(p_crit_set.sob_currency,'''','''''')||'''';
2809 p_ap_where := p_ap_where||' And Sob_currency_code = '''||replace(p_crit_set.sob_currency,'''','''''')||'''';
2810 end if;
2811
2812
2813 GENERATE_DYN_WHERE_CLAUSE(p_crit_set,v_dyn_ar_where,v_dyn_ap_where);
2814
2815 p_ar_where := p_ar_where||v_due_date_ar_and||v_dyn_ar_where;
2816 p_ap_where := p_ap_where||v_due_date_ap_and||v_dyn_ap_where;
2817
2818 END GET_HOAPR_REPORT_PARAMETERS;
2819
2820
2821 /* SAVE_CRITERIA_SET takes in a criteria_set_rec_type and saves the information
2822 into the table.
2823 1) if no set name is specified, raise an exception
2824 2) if name matches already used name, load, retain, and use creator id and created date
2825 delete all rows that belong to the matching set
2826 3) For every non null criteria add a row into xtr_hedge_criteria
2827 Note: date is saved relative to system date.
2828 */
2829 PROCEDURE SAVE_CRITERIA_SET(p_crit_set CRITERIA_SET_REC_TYPE) IS
2830
2831 v_user_id NUMBER := FND_GLOBAL.user_id;
2832 v_date DATE := sysdate;
2833 v_log_in_id NUMBER := FND_GLOBAL.login_id;
2834 v_create_id NUMBER;
2835 v_create_date DATE;
2836
2837 cursor get_old_set_info(p_criteria_set varchar2,p_criteria_set_owner number) is
2838 select created_by,creation_date
2839 from xtr_hedge_criteria
2840 where criteria_set = p_criteria_set
2841 and (criteria_set_owner = p_criteria_set_owner
2842 or (criteria_set_owner is null
2843 and p_criteria_set_owner is null
2844 )
2845 );
2846
2847
2848 PROCEDURE ADD_CRITERIA(p_type in VARCHAR2,
2849 p_code in VARCHAR2,
2850 p_operator in VARCHAR2,
2851 p_from in VARCHAR2,
2852 p_to in VARCHAR2 default NULL) IS
2853 BEGIN
2854 if (p_from is not null) then
2855 insert into xtr_hedge_criteria(hedge_criteria_id,
2856 criteria_type,
2857 criteria_code,
2858 operator,
2859 from_value,
2860 to_value,
2861 created_by,
2862 creation_date,
2863 last_updated_by,
2864 last_update_date,
2865 last_update_login,
2866 criteria_set,
2867 criteria_set_owner)
2868 values(xtr_hedge_criteria_s.nextval,
2869 p_type,
2870 p_code,
2871 p_operator,
2872 p_from,
2873 p_to,
2874 v_create_id,
2875 v_create_date,
2876 v_user_id,
2877 v_date,
2878 v_log_in_id,
2879 p_crit_set.criteria_set,
2880 p_crit_set.criteria_set_owner);
2881 end if;
2882 END;
2883
2884 BEGIN
2885 if (p_crit_set.criteria_set is null) then
2886 raise e_invalid_criteria_set;
2887 end if;
2888
2889 open get_old_set_info(p_crit_set.criteria_set,p_crit_set.criteria_set_owner);
2890 fetch get_old_set_info into v_create_id,v_create_date;
2891 if (get_old_set_info%FOUND) then
2892
2893 DELETE_CRITERIA_SET(p_crit_set);
2894
2895 else
2896
2897 v_create_id := v_user_id;
2898 v_create_date := v_date;
2899
2900 end if;
2901 close get_old_set_info;
2902
2903 CHECK_ADV_CRIT_SET_CONSISTENCY(p_crit_set);
2904
2905 ADD_CRITERIA('BASIC','ITEM_SOURCE','=',p_crit_set.source);
2906 ADD_CRITERIA('BASIC','CURRENCY','=',p_crit_set.currency);
2907 ADD_CRITERIA('BASIC','COMPANY_CODE','=',p_crit_set.company_code);
2908 ADD_CRITERIA('BASIC','SOB_CURRENCY','=',p_crit_set.sob_currency);
2909 ADD_CRITERIA('BASIC','DISCOUNT','=',p_crit_set.discount);
2910 ADD_CRITERIA('BASIC','FACTOR','=',p_crit_set.factor);
2911
2912 ADD_CRITERIA('BASIC','TRX_DATES','BETWEEN',to_date(p_crit_set.due_date_from,'RRRR/MM/DD')-trunc(sysdate),to_date(p_crit_set.due_date_to,'RRRR/MM/DD')-trunc(sysdate));
2913 ADD_CRITERIA('BASIC','AR_UNPLD','=',p_crit_set.ar_unpld);
2914 ADD_CRITERIA('BASIC','AP_UNPLD','=',p_crit_set.ap_unpld);
2915
2916 for i in 0..p_crit_set.condition_count - 1 loop
2917 ADD_CRITERIA('ADVANCED',p_crit_set.item(i),p_crit_set.condition(i),p_crit_set.value(i));
2918 end loop;
2919
2920 END SAVE_CRITERIA_SET;
2921
2922 /* LOAD_CRITERIA_SET works in the opposite manner as SAVE_CRITERIA_SET
2923 This time, the procedure is given a set name and owner and uses that
2924 information to load all the details from xtr_hedge_criteria
2925 */
2926 PROCEDURE LOAD_CRITERIA_SET(p_crit_set IN OUT NOCOPY CRITERIA_SET_REC_TYPE) IS
2927
2928 v_dummy xtr_hedge_criteria.to_value%type;
2929 i Number;
2930
2931 cursor get_advanced_criteria is
2932 select criteria_code,operator,from_value
2933 from xtr_hedge_criteria
2934 where criteria_type='ADVANCED'
2935 and criteria_set = p_crit_set.criteria_set
2936 and (criteria_set_owner = p_crit_set.criteria_set_owner
2937 or (criteria_set_owner is null
2938 and p_crit_set.criteria_set_owner is null
2939 )
2940 );
2941
2942 cursor get_basic_criteria(p_criteria_code VARCHAR2) is
2943 select from_value,to_value
2944 from xtr_hedge_criteria
2945 where criteria_type = 'BASIC'
2946 and criteria_code = p_criteria_code
2947 and criteria_set = p_crit_set.criteria_set
2948 and (criteria_set_owner = p_crit_set.criteria_set_owner
2949 or (criteria_set_owner is null
2950 and p_crit_set.criteria_set_owner is null
2951 )
2952 );
2953
2954 PROCEDURE LOAD_BASIC_CRITERIA(p_code in VARCHAR2,
2955 p_from out NOCOPY VARCHAR2,
2956 p_to out NOCOPY VARCHAR2) IS
2957 BEGIN
2958 open get_basic_criteria(p_code);
2959 fetch get_basic_criteria into p_from,p_to;
2960 close get_basic_criteria;
2961 END;
2962
2963 BEGIN
2964 if (p_crit_set.criteria_set is null) then
2965 raise e_invalid_criteria_set;
2966 end if;
2967
2968 LOAD_BASIC_CRITERIA('ITEM_SOURCE',p_crit_set.source,v_dummy);
2969 LOAD_BASIC_CRITERIA('CURRENCY',p_crit_set.currency,v_dummy);
2970 LOAD_BASIC_CRITERIA('COMPANY_CODE',p_crit_set.company_code,v_dummy);
2971 LOAD_BASIC_CRITERIA('SOB_CURRENCY',p_crit_set.sob_currency,v_dummy);
2972 LOAD_BASIC_CRITERIA('DISCOUNT',p_crit_set.discount,v_dummy);
2973 LOAD_BASIC_CRITERIA('FACTOR',p_crit_set.factor,v_dummy);
2974 LOAD_BASIC_CRITERIA('TRX_DATES',p_crit_set.due_date_from,p_crit_set.due_date_to);
2975 p_crit_set.due_date_from := to_char(p_crit_set.due_date_from + trunc(sysdate),'RRRR/MM/DD');
2976 p_crit_set.due_date_to := to_char(p_crit_set.due_date_to + trunc(sysdate),'RRRR/MM/DD');
2977 LOAD_BASIC_CRITERIA('AR_UNPLD',p_crit_set.ar_unpld,v_dummy);
2978 LOAD_BASIC_CRITERIA('AP_UNPLD',p_crit_set.ap_unpld,v_dummy);
2979 i:=0;
2980 for r in get_advanced_criteria loop
2981 p_crit_set.item(i):=r.criteria_code;
2982 p_crit_set.condition(i):=r.operator;
2983 p_crit_set.value(i):=r.from_value;
2984 i := i+1;
2985 end loop;
2986 p_crit_set.condition_count := i;
2987
2988 MINIMUM_DEFAULT(p_crit_set);
2989
2990 END LOAD_CRITERIA_SET;
2991
2992
2993
2994
2995 /* DELETE_CRITERIA_SET removes all rows from xtr_hedge_criteria
2996 that match the set name and owner
2997 */
2998 PROCEDURE DELETE_CRITERIA_SET(p_crit_set CRITERIA_SET_REC_TYPE) IS
2999 BEGIN
3000 DELETE xtr_hedge_criteria
3001 WHERE criteria_set = p_crit_set.criteria_set
3002 AND (
3003 criteria_set_owner = p_crit_set.criteria_set_owner
3004 or
3005 (
3006 criteria_set_owner is null
3007 and
3008 p_crit_set.criteria_set_owner is null
3009 )
3010 );
3011
3012 END DELETE_CRITERIA_SET;
3013
3014 END XTR_HEDGE_PROCESS_P;
3015