[Home] [Help]
PACKAGE BODY: APPS.FARX_C_WD
Source
1 PACKAGE BODY FARX_C_WD as
2 /* $Header: farxcwdb.pls 120.18 2011/09/30 17:59:35 gigupta ship $ */
3
4 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
5
6
7 PROCEDURE WHATIF (
8 argument1 in varchar2, -- book
9 argument20 in varchar2, -- set_of_books_id /* added for enhancement bug 3037321 */
10 argument2 in varchar2, -- begin_period
11 argument3 in varchar2, -- num_periods
12 argument4 in varchar2 default null, -- begin_asset
13 argument5 in varchar2 default null, -- end_asset
14 argument6 in varchar2 default null, -- begin_dpis
15 argument7 in varchar2 default null, -- end_dpis
16 argument8 in varchar2 default null, -- description
17 argument9 in varchar2 default null, -- category flex struct
18 argument10 in varchar2 default null, -- category_id
19 argument11 in varchar2 default null, -- new method
20 argument12 in varchar2 default null, -- new life in months
21 argument13 in varchar2 default null, -- new rate
22 argument14 in varchar2 default null, -- new prorate convention
23 argument15 in varchar2 default null, -- new salvage percentage
24 argument16 in varchar2 default null, -- AMORTIZED yes_no
25 argument17 in varchar2 default null, -- fully reserved yes_no
26 argument18 in varchar2 default 'NO', -- hypothetical NO
27 argument19 in varchar2 default null, -- bonus_rule
28 argument21 in varchar2 default 'N', -- calc_extend_flag NO -- ERnos 6612615 what-if start
29 argument22 in varchar2 default null, -- first_period -- ERnos 6612615 what-if end
30 p_parent_request_id in number,
31 p_total_requests in number,
32 p_request_number in number,
33 x_success_count out NOCOPY number,
34 x_failure_count out NOCOPY number,
35 x_worker_jobs out NOCOPY number,
36 x_return_status out NOCOPY number,
37 argument28 in varchar2 default null,
38 argument29 in varchar2 default null,
39 argument30 in varchar2 default null,
40 argument31 in varchar2 default null,
41 argument32 in varchar2 default null,
42 argument33 in varchar2 default null,
43 argument34 in varchar2 default null,
44 argument35 in varchar2 default null,
45 argument36 in varchar2 default null,
46 argument37 in varchar2 default null,
47 argument38 in varchar2 default null,
48 argument39 in varchar2 default null,
49 argument40 in varchar2 default null,
50 argument41 in varchar2 default null,
51 argument42 in varchar2 default null,
52 argument43 in varchar2 default null,
53 argument44 in varchar2 default null,
54 argument45 in varchar2 default null,
55 argument46 in varchar2 default null,
56 argument47 in varchar2 default null,
57 argument48 in varchar2 default null,
58 argument49 in varchar2 default null,
59 argument50 in varchar2 default null,
60 argument51 in varchar2 default null,
61 argument52 in varchar2 default null,
62 argument53 in varchar2 default null,
63 argument54 in varchar2 default null,
64 argument55 in varchar2 default null,
65 argument56 in varchar2 default null,
66 argument57 in varchar2 default null,
67 argument58 in varchar2 default null,
68 argument59 in varchar2 default null,
69 argument60 in varchar2 default null,
70 argument61 in varchar2 default null,
71 argument62 in varchar2 default null,
72 argument63 in varchar2 default null,
73 argument64 in varchar2 default null,
74 argument65 in varchar2 default null,
75 argument66 in varchar2 default null,
76 argument67 in varchar2 default null,
77 argument68 in varchar2 default null,
78 argument69 in varchar2 default null,
79 argument70 in varchar2 default null,
80 argument71 in varchar2 default null,
81 argument72 in varchar2 default null,
82 argument73 in varchar2 default null,
83 argument74 in varchar2 default null,
84 argument75 in varchar2 default null,
85 argument76 in varchar2 default null,
86 argument77 in varchar2 default null,
87 argument78 in varchar2 default null,
88 argument79 in varchar2 default null,
89 argument80 in varchar2 default null,
90 argument81 in varchar2 default null,
91 argument82 in varchar2 default null,
92 argument83 in varchar2 default null,
93 argument84 in varchar2 default null,
94 argument85 in varchar2 default null,
95 argument86 in varchar2 default null,
96 argument87 in varchar2 default null,
97 argument88 in varchar2 default null,
98 argument89 in varchar2 default null,
99 argument90 in varchar2 default null,
100 argument91 in varchar2 default null,
101 argument92 in varchar2 default null,
102 argument93 in varchar2 default null,
103 argument94 in varchar2 default null,
104 argument95 in varchar2 default null,
105 argument96 in varchar2 default null,
106 argument97 in varchar2 default null,
107 argument98 in varchar2 default null,
108 argument99 in varchar2 default null,
109 argument100 in varchar2 default null) is
110
111
112 -- Arguments as follows: ('M' indicated mandatory; others are optional)
113 -- argument1 book (M)
114 -- argument2 begin_period (M)
115 -- argument3 num_periods (M)
116 -- argument4 begin_asset
117 -- argument5 end_asset
118 -- argument6 begin_dpis
119 -- argument7 end_dpis
120 -- argument8 description
121 -- argument9 category_id
122 -- argument11 new method
123 -- argument12 new life
124 -- argument13 new adjusted_rate
125 -- argument14 new prorate convention
126 -- argument15 new salvage percentage
127 -- argument16 EXPENSED or AMORTIZED
128 -- argument17 check fully reserved assets flag
129 -- argument18 hypothetical not used
130 -- argument19 bonus_rule
131 -- argument20 set_of_books_id (M) /* added for enhancement bug 3037321 */
132 -- argument21 calc_extend_flag NO -- ERnos 6612615 what-if start
133 -- argument22 first_period -- ERnos 6612615 what-if end
134
135 h_request_id number;
136 h_user_id varchar2(20);
137 ret boolean;
138
139 h_assets fa_std_types.number_tbl_type;
140 h_num_assets number;
141
142 h_begin_dpis date;
143 h_date_in_service date;
144 h_end_dpis date;
145 h_begin_str varchar2(25);
146 h_end_str varchar2(25);
147 h_date_format varchar2(25);
148
149 h_begin_per varchar2(25);
150
151 h_exp_amt varchar2(10);
152 h_cat_id number;
153 h_cat_struct number;
154
155 h_adj_rate number;
156
157 h_count number;
158
159 h_sqlstmt varchar2(400);
160
161
162 h_mesg_name varchar2(30);
163 h_mesg_str varchar2(2000);
164 h_param_error varchar2(30);
165 h_value_error varchar2(240);
166
167 h_check varchar2(5);
168
169 l_mode varchar2(10); -- This stores value of h_exp_amt or 'PROJ'
170 -- if user didn't provide any parameters.
171 /*Added for parallelization*/
172 l_batch_size number;
173
174 l_unassigned_cnt number := 0;
175 l_failed_cnt number := 0;
176 l_wip_cnt number := 0;
177 l_completed_cnt number := 0;
178 l_total_cnt number := 0;
179 l_count number := 0;
180 l_start_range number := 0;
181 l_end_range number := 0;
182
183 l_calling_fn varchar2(40) := 'FARX_C_WD.WHATIF';
184
185 done_exc exception;
186 error_found exception;
187
188 begin
189
190 /*Added for parallelization*/
191 x_success_count := 0;
192 x_failure_count := 0;
193 x_worker_jobs := 0;
194 x_return_status := 0;
195
196 -- dbms_session.reset_package;
197
198 -- VALIDATE THE ARGUMENTS. SINCE RX CLIENT CURRENTLY DOES NO
199 -- VALIDATION, USER CAN ENTER GARBAGE STRINGS IF THEY WANT. NEED
200 -- TO VALIDATE EVERYTHING RIGHT HERE.
201
202
203 -- BOOK
204
205 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
206 h_value_error := argument1;
207 h_param_error := 'BOOK';
208
209 h_count := 0;
210
211 if argument1 is null then
212
213 fnd_message.set_name('OFA','FA_WHATIF_PARAM_REQUIRED');
214 h_mesg_str := fnd_message.get;
215 fa_rx_conc_mesg_pkg.log(h_mesg_str);
216
217 x_return_status := 2;
218 return;
219
220 end if;
221
222 select count(*) into h_count
223 from fa_book_controls
224 where book_Type_code = argument1 and rownum < 2;
225
226 if h_count = 0 then
227 fnd_message.set_name('OFA','FA_WHATIF_PARAM_REQUIRED');
228 h_mesg_str := fnd_message.get;
229 fa_rx_conc_mesg_pkg.log(h_mesg_str);
230
231 x_return_status := 2;
232 return;
233 end if;
234
235 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
236 h_value_error := argument20;
237 h_param_error := 'SET OF BOOKS ID';
238
239 -- Enhancement Bug 3037321
240 FARX_C_WD.sob_id := to_number(argument20);
241 select mrc_sob_type_code,currency_code
242 into FARX_C_WD.mrc_sob_type,FARX_C_WD.currency
243 from gl_sets_of_books
244 where set_of_books_id = to_number(argument20);
245
246 -- Enhancement Bug 3037321
247 if(FARX_C_WD.mrc_sob_type = 'R') then
248 fnd_client_info.set_currency_context(FARX_C_WD.sob_id);
249 end if;
250
251 -- PERIOD NAME
252 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
253 h_value_error := argument2;
254 h_param_error := 'PERIOD NAME';
255
256
257 if argument2 is null then
258 fnd_message.set_name('OFA','FA_WHATIF_PARAM_REQUIRED');
259 h_mesg_str := fnd_message.get;
260 fa_rx_conc_mesg_pkg.log(h_mesg_str);
261
262 x_return_status := 2;
263 return;
264 end if;
265
266 h_count := 0;
267 select count(*) into h_count
268 from fa_book_controls bc, fa_calendar_periods cp
269 where bc.book_type_code = argument1
270 and bc.deprn_calendar = cp.calendar_type
271 and cp.period_name = argument2 and rownum < 2;
272
273 if h_count = 0 then
274
275 fnd_message.set_name('OFA','FA_WHATIF_PARAM_REQUIRED');
276 h_mesg_str := fnd_message.get;
277 fa_rx_conc_mesg_pkg.log(h_mesg_str);
278
279 x_return_status := 2;
280 return;
281 end if;
282
283
284
285 -- NUM PERIODS
286
287 if ((argument3 is null) OR (to_number(argument3) <= 0 OR
288 to_number(argument3) <> floor(to_number(argument3)))) then
289
290 fnd_message.set_name('OFA','FA_WHATIF_PARAM_REQUIRED');
291 h_mesg_str := fnd_message.get;
292 fa_rx_conc_mesg_pkg.log(h_mesg_str);
293
294 x_return_status := 2;
295 return;
296 end if;
297
298
299 -- Make sure calendar is defined for duration of projection.
300
301 select count(*) into h_count
302 from fa_book_controls bc, fa_calendar_types ct,
303 fa_calendar_periods cp
304 where bc.book_type_code = argument1
305 and bc.deprn_calendar = ct.calendar_type
306 and ct.calendar_type = cp.calendar_type
307 and cp.start_date >= (select cp1.start_date from
308 fa_calendar_periods cp1
309 where cp1.calendar_type = cp.calendar_type
310 and cp1.period_name = argument2);
311
312 if h_count < to_number(argument3) then
313 fnd_message.set_name('OFA','FA_PROJ_CALS_UNDEFINED');
314 h_mesg_str := fnd_message.get;
315 fa_rx_conc_mesg_pkg.log(h_mesg_str);
316 x_return_status := 2;
317 return;
318 end if;
319
320
321 --
322 -- NOT hypothetical case
323 --
324 if (upper(argument18) in ('NO', 'N')) then
325
326 -- BEGIN/END ASSET
327
328 if (argument4 is not null and argument5 is not null AND
329 argument5 < argument4) then
330
331 fnd_message.set_name('OFA','FA_WHATIF_BEGIN_END_ASSET');
332 h_mesg_str := fnd_message.get;
333 fa_rx_conc_mesg_pkg.log(h_mesg_str);
334
335 x_return_status := 2;
336 return;
337 end if;
338
339 -- NOTE: DATE FORMATTING PROBLEMS SHOULD BE FEW AND FAR BETWEEN.
340 -- THIS IS ONE OF THE FEW THINGS THAT THE RX CLIENT CURRENTLY CHECKS
341 -- FOR. MOREOVER, CM WILL PUT ANY UNHANDLED DATE FORMATTING
342 -- EXCEPTIONS INTO THE LOG FILE.
343
344 -- CATEGORY: FIRST GET CATEGORY_ID, THEN CHECK IF EXISTS, ENABLED, ETC.
345
346 if (argument10 is not null) then
347 h_cat_id := to_number(argument10);
348 end if;
349
350 /* ********************************************************************
351 Commenting out all code to validate and get category_id since this
352 is passed from SRS in argument 10
353
354 h_mesg_name := 'FA_FE_LOOKUP_IN_SYSTEM_CTLS';
355 select category_flex_structure into h_cat_struct
356 from fa_system_controls;
357
358 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
359 h_value_error := argument9;
360 h_param_error := 'CATEGORY';
361
362 if fnd_flex_keyval.validate_segs (
363 operation => 'CHECK_COMBINATION',
364 appl_short_name => 'OFA',
365 key_flex_code => 'CAT#',
366 structure_number => h_cat_struct,
367 concat_segments => argument9,
368 values_or_ids => 'V',
369 validation_date =>SYSDATE,
370 displayable => 'ALL',
371 data_set => NULL,
372 vrule => NULL,
373 where_clause => NULL,
374 get_columns => NULL,
375 allow_nulls => FALSE,
376 allow_orphans => FALSE,
377 resp_appl_id => NULL,
378 resp_id => NULL,
379 user_id => NULL) = FALSE then
380
381 fnd_message.set_name('OFA','FA_WHATIF_NO_CAT');
382 fnd_message.set_token('CAT',argument9,FALSE);
383 h_mesg_str := fnd_message.get;
384 fa_rx_conc_mesg_pkg.log(h_mesg_str);
385
386 x_return_status := 2;
387 return;
388 end if;
389 h_cat_id := fnd_flex_keyval.combination_id;
390 h_count := 0;
391
392 select count(*) into h_count from fa_categories cat,
393 fa_category_books cb
394 where cat.category_id = h_cat_id
395 and cat.enabled_flag = 'Y' and cat.capitalize_flag = 'YES'
396 and sysdate between nvl(cat.start_date_active,sysdate-1) and
397 nvl(cat.end_date_active,sysdate+1)
398 and cat.category_id = cb.category_id
399 and cb.book_type_code = argument1 and rownum < 2;
400
401 if h_count = 0 then
402 fnd_message.set_name('OFA','FA_WHATIF_CAT_NOT_SET_UP');
403 fnd_message.set_token('CAT',argument9,FALSE);
404 h_mesg_str := fnd_message.get;
405 fa_rx_conc_mesg_pkg.log(h_mesg_str);
406
407 x_return_status := 2;
408 return;
409 end if;
410 end if;
411 ********************************************************************* */
412
413
414 -- CHECK_FULLY_RESERVED_FLAG
415
416 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
417 h_value_error := argument17;
418 h_param_error := 'FULLY_RSVD_FLAG';
419
420 if (argument17 not in ('Y','N','YES','NO')) then
421
422 fnd_message.set_name('OFA',h_mesg_name);
423 if h_mesg_name = 'FA_WHATIF_PARAM_ERROR' then
424 fnd_message.set_token('VALUE',h_value_error,FALSE);
425 fnd_message.set_token('PARAM',h_param_error,FALSE);
426 end if;
427 h_mesg_str := fnd_message.get;
428 fa_rx_conc_mesg_pkg.log(h_mesg_str);
429 x_return_status := 2;
430
431 return;
432 end if;
433
434 -- CONVERT DPIS'S INTO DATE-TYPED VARIABLES
435 -- THIS IS THE APPS STANDARD. SUBMISSION FORM DOES THE SAME THING.
436
437 if (argument6 is not null) then
438 h_begin_dpis := to_date(argument6, 'YYYY/MM/DD HH24:MI:SS');
439 end if;
440
441 if (argument7 is not null) then
442 h_end_dpis := to_date(argument7, 'YYYY/MM/DD HH24:MI:SS');
443 end if;
444
445 if (argument6 is not null and argument7 is not null) then
446 if h_end_dpis < h_begin_dpis then
447 fnd_message.set_name('OFA','FA_SHARED_BAD_END_DATE');
448 h_mesg_str := fnd_message.get;
449 fa_rx_conc_mesg_pkg.log(h_mesg_str);
450 x_return_status := 2;
451 return;
452 end if;
453 end if;
454
455 end if; -- NOT hypothetical case
456
457 -- METHOD
458 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
459 h_value_error := argument10;
460 h_param_error := 'METHOD';
461
462 if (argument11 is not null) then
463
464
465 h_count := 0;
466 select count(*) into h_count
467 from fa_methods
468 where method_code = argument11 and rownum < 2;
469
470 if h_count = 0 then
471 fnd_message.set_name('OFA','FA_WHATIF_NO_METHOD');
472 fnd_message.set_token('METHOD',argument11,FALSE);
473 h_mesg_str := fnd_message.get;
474 fa_rx_conc_mesg_pkg.log(h_mesg_str);
475
476 x_return_status := 2;
477 return;
478 end if;
479 end if;
480
481
482
483 if (argument11 is not null) then
484
485 -- LIFE
486 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
487 h_value_error := argument12;
488 h_param_error := 'LIFE';
489
490 h_count := 0;
491 select count(*) into h_count from fa_methods
492 where method_code = argument11
493 and rate_source_rule in ('TABLE','CALCULATED','FORMULA')
494 and rownum < 2;
495
496 if h_count > 0 then -- this is a life-based method
497
498 if (argument12 is null) then
499
500 fnd_message.set_name('OFA','FA_MASSCHG_LIFE_BASED_METHOD');
501 h_mesg_str := fnd_message.get;
502 fa_rx_conc_mesg_pkg.log(h_mesg_str);
503
504 x_return_status := 2;
505 return;
506 end if;
507
508 h_count := 0;
509 select count(*) into h_count from fa_methods
510 where method_code = argument11 and life_in_months = to_number(argument12)
511 and rownum < 2;
512
513 if h_count = 0 then
514 fnd_message.set_name('OFA','FA_SHARED_INVALID_METHOD_LIFE');
515 h_mesg_str := fnd_message.get;
516 fa_rx_conc_mesg_pkg.log(h_mesg_str);
517
518 x_return_status := 2;
519 return;
520 end if;
521
522 if (argument13 is not null) then
523 fnd_message.set_name('OFA','FA_METHOD_NO_RATES');
524 h_mesg_str := fnd_message.get;
525 fa_rx_conc_mesg_pkg.log(h_mesg_str);
526
527 x_return_status := 2;
528 return;
529
530 end if;
531
532 end if;
533
534 -- RATE
535 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
536 h_value_error := argument13;
537 h_param_error := 'RATE';
538
539
540 h_count := 0;
541 select count(*) into h_count from fa_methods m
542 where m.method_code = argument11
543 and m.rate_source_rule = 'FLAT' and rownum < 2;
544
545 if h_count > 0 then -- this is a rate-based method
546
547 if (argument13 is null) then
548 fnd_message.set_name('OFA','FA_MASSCHG_RATE_BASED_METHOD');
549 h_mesg_str := fnd_message.get;
550 fa_rx_conc_mesg_pkg.log(h_mesg_str);
551
552 x_return_status := 2;
553 return;
554 end if;
555
556 h_adj_rate := fnd_number.canonical_to_number(argument13) / 100; /*12934676 This will take care of current territory*/
557
558 h_count := 0;
559 select count(*) into h_count from fa_methods m, fa_flat_rates r
560 where m.method_code = argument11
561 and m.method_id = r.method_id
562 and r.adjusted_rate = h_adj_rate and rownum < 2;
563
564 if h_count = 0 then
565 fnd_message.set_name('OFA','FA_SHARED_INVALID_METHOD_RATE');
566 h_mesg_str := fnd_message.get;
567 fa_rx_conc_mesg_pkg.log(h_mesg_str);
568
569 x_return_status := 2;
570 return;
571
572 end if;
573
574 if (argument12 is not null) then
575 fnd_message.set_name('OFA','FA_METHOD_NO_LIFE');
576 h_mesg_str := fnd_message.get;
577 fa_rx_conc_mesg_pkg.log(h_mesg_str);
578
579 x_return_status := 2;
580 return;
581
582
583 end if;
584
585 end if;
586
587 end if;
588
589 -- PRORATE CONVENTION
590
591 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
592 h_value_error := argument14;
593 h_param_error := 'CONVENTION';
594
595 if argument14 is not null then
596
597 h_count := 0;
598 select count(*) into h_count from fa_conventions
599 where prorate_convention_code = argument14 and rownum < 2;
600
601 if h_count = 0 then
602 fnd_message.set_name('OFA','FA_WHATIF_NO_CONVENTION');
603 fnd_message.set_token('CONV',argument14,FALSE);
604 h_mesg_str := fnd_message.get;
605 fa_rx_conc_mesg_pkg.log(h_mesg_str);
606
607 x_return_status := 2;
608 return;
609 end if;
610 end if;
611
612 -- SALVAGE_VALUE_PERCENTAGE
613
614 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
615 h_value_error := argument15;
616 h_param_error := 'SALVAGE VALUE';
617
618 if argument15 is not null then
619
620 if (to_number(argument15) < 0 OR to_number(argument15) > 100) then
621 fnd_message.set_name('OFA','FA_SHARED_BAD_PERCENT');
622 h_mesg_str := fnd_message.get;
623 fa_rx_conc_mesg_pkg.log(h_mesg_str);
624
625 x_return_status := 2;
626 return;
627 end if;
628 end if;
629
630 -- AMORTIZE_FLAG
631
632 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
633 h_value_error := argument16;
634 h_param_error := 'AMORTIZE_FLAG';
635
636
637 if (upper(argument16) not in ('Y','N','YES','NO','EXPENSED','AMORTIZED'))
638 then
639
640 fnd_message.set_name('OFA',h_mesg_name);
641 if h_mesg_name = 'FA_WHATIF_PARAM_ERROR' then
642 fnd_message.set_token('VALUE',h_value_error,FALSE);
643 fnd_message.set_token('PARAM',h_param_error,FALSE);
644 end if;
645 h_mesg_str := fnd_message.get;
646 fa_rx_conc_mesg_pkg.log(h_mesg_str);
647 x_return_status := 2;
648
649 return;
650
651 end if;
652
653
654 -- USER_ID
655
656 /* ***************************************************
657 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
658 h_value_error := argument17;
659 h_param_error := 'USER_ID';
660
661 h_count := 0;
662 select count(*) into h_count from fnd_user
663 where user_id = to_number(argument17) and rownum < 2;
664
665 if (h_count = 0 and to_number(nvl(argument17,'0')) <> 0) then
666
667 fnd_message.set_name('OFA',h_mesg_name);
668 if h_mesg_name = 'FA_WHATIF_PARAM_ERROR' then
669 fnd_message.set_token('VALUE',h_value_error,FALSE);
670 fnd_message.set_token('PARAM',h_param_error,FALSE);
671 end if;
672 h_mesg_str := fnd_message.get;
673 fa_rx_conc_mesg_pkg.log(h_mesg_str);
674 x_return_status := 2;
675 return;
676 end if;
677 ******************************************************* */
678
679 h_request_id := fnd_global.conc_request_id;
680 fnd_profile.get('USER_ID',h_user_id);
681
682 -- CHECK AMORTIZE_FLAG
683
684 if (upper(argument16) in ('YES','Y')) then h_exp_amt := 'AMORTIZED';
685 elsif (upper(argument16) in ('NO','N')) then h_exp_amt := 'EXPENSED';
686 elsif (argument16 is null) then h_exp_amt := 'EXPENSED';
687 else h_exp_amt := upper(argument16);
688 end if;
689
690
691 -- BONUS RULE
692
693 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
694 h_value_error := argument19;
695 h_param_error := 'BONUS RULE';
696
697 if argument19 is not null then
698
699 h_count := 0;
700 select count(*) into h_count from fa_bonus_rules
701 where bonus_rule = argument19 and rownum < 2;
702
703 if h_count = 0 then
704
705
706 fnd_message.set_name('OFA',h_mesg_name);
707 if h_mesg_name = 'FA_WHATIF_PARAM_ERROR' then
708 fnd_message.set_token('VALUE',h_value_error,FALSE);
709 fnd_message.set_token('PARAM',h_param_error,FALSE);
710 end if;
711 h_mesg_str := fnd_message.get;
712 fa_rx_conc_mesg_pkg.log(h_mesg_str);
713 x_return_status := 2;
714 return;
715
716 end if;
717 end if;
718
719 /*Added for parallelism start */
720 if (p_total_requests > 1) then
721
722 select nvl(sum(decode(status,'UNASSIGNED', 1, 0)),0),
723 nvl(sum(decode(status,'FAILED', 1, 0)),0),
724 nvl(sum(decode(status,'IN PROCESS', 1, 0)),0),
725 nvl(sum(decode(status,'COMPLETED',1 , 0)),0),
726 count(*)
727 into l_unassigned_cnt,
728 l_failed_cnt,
729 l_wip_cnt,
730 l_completed_cnt,
731 l_total_cnt
732 from fa_worker_jobs
733 where request_id = p_parent_request_id;
734
735 if g_print_debug then
736 fa_debug_pkg.add(l_calling_fn, 'Job status - Unassigned: ', l_unassigned_cnt);
737 fa_debug_pkg.add(l_calling_fn, 'Job status - In Process: ', l_wip_cnt);
738 fa_debug_pkg.add(l_calling_fn, 'Job status - Completed: ', l_completed_cnt);
739 fa_debug_pkg.add(l_calling_fn, 'Job status - Failed: ', l_failed_cnt);
740 fa_debug_pkg.add(l_calling_fn, 'Job status - Total: ', l_total_cnt);
741 end if;
742
743 if (l_failed_cnt > 0) then
744 if g_print_debug then
745 fa_debug_pkg.add(l_calling_fn, 'another worker has errored out: ', 'stop processing');
746 end if;
747 raise error_found; -- probably not
748 elsif (l_unassigned_cnt = 0) then
749 if g_print_debug then
750 fa_debug_pkg.add(l_calling_fn, 'no more jobs left', 'terminating.');
751 end if;
752 raise done_exc;
753 elsif (l_completed_cnt = l_total_cnt) then
754 if g_print_debug then
755 fa_debug_pkg.add(l_calling_fn, 'all jobs completed, no more jobs. ', 'terminating');
756 end if;
757 raise done_exc;
758 elsif (l_unassigned_cnt > 0) then
759 update fa_worker_jobs
760 set status = 'IN PROCESS',
761 worker_num = p_request_number
762 where status = 'UNASSIGNED'
763 and request_id = p_parent_request_id
764 and rownum < 2;
765 if g_print_debug then
766 fa_debug_pkg.add(l_calling_fn, 'taking job from job queue', sql%rowcount);
767 end if;
768 l_count := sql%rowcount;
769 x_worker_jobs := l_unassigned_cnt;
770 commit;
771 end if;
772 end if; -- if (p_total_requests > 1) then
773
774 /*end parallelism*/
775
776 if (l_count > 0 or p_total_requests < 2) then
777
778 --begin
779
780 begin
781
782 select start_range
783 ,end_range
784 into l_start_range
785 ,l_end_range
786 from fa_worker_jobs
787 where request_id = p_parent_request_id
788 and worker_num = p_request_number
789 and status = 'IN PROCESS';
790
791 exception
792
793 when no_data_found then
794 fa_debug_pkg.add(l_calling_fn, 'selecting', 'null ranges');
795 end;
796
797
798 -- NOT hypothetical case
799
800 if (upper(argument18) in ('NO', 'N')) then
801
802 -- If user doesn't provide value for any of following parameter
803 -- user just wants to see projected amount so any validation in
804 -- whatif_get_assets should not be performed.
805 --
806 -- method => argument11 life => argument12
807 -- adjusted_rate => h_adj_rate prorate_conv => argument14
808 -- salvage_pct => argument15 bonus_rule => argument19
809 --
810 if (argument11 is null) and
811 (argument12 is null) and
812 (h_adj_rate is null) and
813 (argument14 is null) and
814 (argument15 is null) and
815 (argument19 is null) then
816 l_mode := 'PROJ';
817 else
818 l_mode := h_exp_amt;
819 end if;
820
821 -- GENERATE LIST OF ASSETS ON WHICH TO PERFORM WHAT-IF
822
823 h_mesg_name := 'FA_WHATIF_GET_ASSETS_ERR';
824
825 if(p_total_requests > 1) then
826 ret := fa_whatif_deprn2_pkg.whatif_get_assets (
827 X_book => argument1,
828 X_begin_asset => argument4,
829 X_end_asset => argument5,
830 X_begin_dpis => h_begin_dpis,
831 X_end_dpis => h_end_dpis,
832 X_description => argument8,
833 X_category_id => h_cat_id,
834 X_mode => l_mode,
835 X_rsv_flag => argument17,
836 X_good_assets => h_assets,
837 X_num_good => h_num_assets,
838 X_start_range => l_start_range,
839 X_end_range => l_end_range,
840 x_return_status => x_return_status);
841 else
842 ret := fa_whatif_deprn2_pkg.whatif_get_assets (
843 X_book => argument1,
844 X_begin_asset => argument4,
845 X_end_asset => argument5,
846 X_begin_dpis => h_begin_dpis,
847 X_end_dpis => h_end_dpis,
848 X_description => argument8,
849 X_category_id => h_cat_id,
850 X_mode => l_mode,
851 X_rsv_flag => argument17,
852 X_good_assets => h_assets,
853 X_num_good => h_num_assets,
854 X_start_range => null,
855 X_end_range => null,
856 x_return_status => x_return_status);
857 end if;
858 -- IF NO ASSETS RETURNED, EXIT WITH WARNING.
859 -- NO NEED TO RUN WHATIF. NO ROWS WOULD GET GENERATED ANYWAYS.
860 -- Bug 8930129, changing the return status to 0 so that no error is raised
861 -- when there is NO ASSET is there to process.
862 if h_num_assets = 0 then
863 fnd_message.set_name('OFA','FA_WHATIF_NO_ASSETS');
864 h_mesg_str := fnd_message.get;
865 fa_rx_conc_mesg_pkg.log(h_mesg_str);
866
867 if (p_total_requests > 1) then
868 update fa_worker_jobs
869 set status = 'COMPLETED'
870 where request_id = p_parent_request_id
871 and worker_num = p_request_number
872 and status = 'IN PROCESS';
873
874 commit;
875 end if;
876 x_return_status := 0;
877 return;
878 end if;
879
880 -- DO WHAT-IF
881 h_mesg_name := 'FA_WHATIF_RUN_WHATIF_ERR';
882
883 ret := fa_whatif_deprn2_pkg.whatif_deprn (
884 X_assets => h_assets,
885 X_num_assets => h_num_assets,
886 X_method => argument11,
887 X_life => to_number(argument12),
888 X_adjusted_rate => h_adj_rate,
889 X_prorate_conv => argument14,
890 X_salvage_pct => to_number(argument15),
891 X_exp_amt => h_exp_amt,
892 X_book => argument1,
893 X_start_per => argument2,
894 X_num_per => to_number(argument3),
895 X_request_id => nvl(p_parent_request_id,h_request_id),
896 X_user_id => to_number(h_user_id),
897 X_hypo => upper(argument18),
898 X_dpis => NULL,
899 X_cost => NULL,
900 X_deprn_rsv => NULL,
901 X_cat_id => NULL,
902 X_bonus_rule => argument19,
903 x_return_status => x_return_status,
904 X_fullresv_flg => argument17, -- ERnos 6612615 what-if start
905 X_extnd_deprn_flg => argument21,
906 X_first_period => argument22); -- ERnos 6612615 what-if end
907
908 -- x_return_status := 0;
909
910 fa_whatif_deprn_pkg.g_deprn.delete;
911 if (ret) then
912
913 if (p_total_requests < 2 ) then
914 raise done_exc;
915 else
916 update fa_worker_jobs
917 set status = 'COMPLETED'
918 where request_id = p_parent_request_id
919 and worker_num = p_request_number
920 and status = 'IN PROCESS';
921
922 commit;
923 end if;
924
925 else
926
927 if (p_total_requests < 2 ) then
928 raise error_found;
929 else
930 update fa_worker_jobs
931 set status = 'FAILED'
932 where request_id = p_parent_request_id
933 and worker_num = p_request_number
934 and status = 'IN PROCESS';
935
936 commit;
937
938 end if;
939 end if;
940 else -- hypothetical case
941
942 --fa_rx_conc_mesg_pkg.log('calling whatif deprn package');
943
944 if (argument4 is not null) then
945 h_date_in_service := fnd_date.canonical_to_date(argument4);
946 end if;
947
948 if (argument7 is not null) then
949 h_cat_id := to_number(argument7);
950 end if;
951
952 /* *********************************************************
953 h_count := 0;
954
955 h_mesg_name := 'FA_FE_LOOKUP_IN_SYSTEM_CTLS';
956 select category_flex_structure into h_cat_struct
957 from fa_system_controls;
958
959 h_mesg_name := 'FA_WHATIF_PARAM_ERROR';
960 h_value_error := argument7;
961 h_param_error := 'CATEGORY';
962
963 if fnd_flex_keyval.validate_segs (
964 operation => 'CHECK_COMBINATION',
965 appl_short_name => 'OFA',
966 key_flex_code => 'CAT#',
967 structure_number => h_cat_struct,
968 concat_segments => argument7,
969 values_or_ids => 'V',
970 validation_date =>SYSDATE,
971 displayable => 'ALL',
972 data_set => NULL,
973 vrule => NULL,
974 where_clause => NULL,
975 get_columns => NULL,
976 allow_nulls => FALSE,
977 allow_orphans => FALSE,
978 resp_appl_id => NULL,
979 resp_id => NULL,
980 user_id => NULL) = FALSE then
981
982 fnd_message.set_name('OFA','FA_WHATIF_NO_CAT');
983 fnd_message.set_token('CAT',argument7,FALSE);
984 h_mesg_str := fnd_message.get;
985 fa_rx_conc_mesg_pkg.log(h_mesg_str);
986
987 x_return_status := 2;
988 return;
989 end if;
990 h_cat_id := fnd_flex_keyval.combination_id;
991
992 h_count := 0;
993
994 select count(*) into h_count from fa_categories cat,
995 fa_category_books cb
996 where cat.category_id = h_cat_id
997 and cat.enabled_flag = 'Y' and cat.capitalize_flag = 'YES'
998 and sysdate between nvl(cat.start_date_active,sysdate-1) and
999 nvl(cat.end_date_active,sysdate+1)
1000 and cat.category_id = cb.category_id
1001 and cb.book_type_code = argument1 and rownum < 2;
1002
1003 if h_count = 0 then
1004 fnd_message.set_name('OFA','FA_WHATIF_CAT_NOT_SET_UP');
1005 fnd_message.set_token('CAT',argument7,FALSE);
1006 h_mesg_str := fnd_message.get;
1007 fa_rx_conc_mesg_pkg.log(h_mesg_str);
1008
1009 x_return_status := 2;
1010 return;
1011 end if;
1012 end if;
1013 ********************************************************** */
1014 /*Bug 9048083 Passed the correct value of category ID below via argument8
1015 Correct val of category ID is passed through argument 8 when program is
1016 run from rxi report but argument 8 is null when program is called from what if form*/
1017
1018 h_cat_id := NVL (argument8,h_cat_id);
1019
1020 --fa_rx_conc_mesg_pkg.log(h_date_in_service);
1021 ret := fa_whatif_deprn2_pkg.whatif_deprn (
1022 X_assets => h_assets,
1023 X_num_assets => NULL,
1024 X_method => argument11,
1025 X_life => to_number(argument12),
1026 X_adjusted_rate => h_adj_rate,
1027 X_prorate_conv => argument14,
1028 X_salvage_pct => to_number(argument15),
1029 X_exp_amt => h_exp_amt,
1030 X_book => argument1,
1031 X_start_per => argument2,
1032 X_num_per => to_number(argument3),
1033 X_request_id => h_request_id,
1034 X_user_id => to_number(h_user_id),
1035 X_hypo => upper(argument18),
1036 X_dpis => h_date_in_service ,
1037 X_cost => to_number(argument5),
1038 X_deprn_rsv => to_number(argument6),
1039 X_cat_id => h_cat_id, --argument8,
1040 X_bonus_rule => argument19,
1041 x_return_status => x_return_status,
1042 X_fullresv_flg => argument17, -- ERnos 6612615 what-if start
1043 X_extnd_deprn_flg => argument21,
1044 X_first_period => argument22); -- ERnos 6612615 what-if end
1045
1046 if (ret) then
1047 if (p_total_requests < 2 ) then
1048 raise done_exc;
1049 else
1050 update fa_worker_jobs
1051 set status = 'COMPLETED'
1052 where request_id = p_parent_request_id
1053 and worker_num = p_request_number
1054 and status = 'IN PROCESS';
1055
1056 commit;
1057
1058 end if;
1059 else
1060 if (p_total_requests < 2 ) then
1061 raise error_found;
1062 else
1063 update fa_worker_jobs
1064 set status = 'FAILED'
1065 where request_id = p_parent_request_id
1066 and worker_num = p_request_number
1067 and status = 'IN PROCESS';
1068
1069 commit;
1070
1071 end if;
1072 end if;
1073 end if; -- NOT hypothetical case
1074 end if; /* if (l_count > 0 or p_total_requests=1) */
1075 /*End parallelism */
1076 /*Bug 11772954 - set return status and count if reached here before returning*/
1077 x_return_status := 0;
1078 x_success_count := x_success_count + 1;
1079 EXCEPTION
1080 WHEN done_exc then
1081 if (p_total_requests > 1) then
1082
1083 update fa_worker_jobs
1084 set status = 'COMPLETED'
1085 where request_id = p_parent_request_id
1086 and worker_num = p_request_number
1087 and status = 'IN PROCESS';
1088 commit;
1089
1090 if g_print_debug then
1091 fa_debug_pkg.add(l_calling_fn, 'updating', 'worker jobs');
1092 end if;
1093 end if;
1094
1095 x_success_count := x_success_count + 1;
1096 if (g_print_debug) then
1097 fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1098 end if;
1099 x_return_status := 0;
1100 return;
1101
1102 WHEN error_found then
1103
1104 update fa_worker_jobs
1105 set status = 'FAILED'
1106 where request_id = p_parent_request_id
1107 and worker_num = p_request_number
1108 and status = 'IN PROCESS';
1109
1110 commit;
1111
1112 x_failure_count := x_failure_count + 1;
1113 fa_srvr_msg.add_message(calling_fn => 'FARX_C_WD.WHATIF');
1114 if (g_print_debug) then
1115 fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1116 end if;
1117 x_return_status := 2;
1118 return;
1119
1120 WHEN OTHERS THEN
1121
1122 update fa_worker_jobs
1123 set status = 'FAILED'
1124 where request_id = p_parent_request_id
1125 and worker_num = p_request_number
1126 and status = 'IN PROCESS';
1127
1128 commit;
1129
1130 x_failure_count := x_failure_count + 1;
1131 fa_srvr_msg.add_sql_error(calling_fn => 'FARX_C_WD.WHATIF');
1132 if (g_print_debug) then
1133 fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1134 end if;
1135 x_return_status := 2;
1136 fa_whatif_deprn_pkg.g_deprn.delete;
1137 return;
1138 /*
1139 if SQLCODE <> 0 then
1140 fa_Rx_conc_mesg_pkg.log(SQLERRM);
1141 end if;
1142
1143 fnd_message.set_name('OFA',h_mesg_name);
1144 if h_mesg_name = 'FA_WHATIF_PARAM_ERROR' then
1145 fnd_message.set_token('VALUE',h_value_error,FALSE);
1146 fnd_message.set_token('PARAM',h_param_error,FALSE);
1147 end if;
1148 h_mesg_str := fnd_message.get;
1149 fa_rx_conc_mesg_pkg.log(h_mesg_str);
1150
1151 x_return_status := 2;
1152 */
1153 end WHATIF;
1154
1155
1156 PROCEDURE Load_Workers(
1157 p_book_type_code IN VARCHAR2,
1158 p_parent_request_id IN NUMBER,
1159 p_total_requests IN NUMBER,
1160 x_worker_jobs OUT NOCOPY NUMBER,
1161 x_return_status OUT NOCOPY number
1162 ) is
1163
1164 l_batch_size number;
1165 l_calling_fn varchar2(60) := 'FARX_C_WD.Load_Workers';
1166
1167 error_found exception;
1168
1169 BEGIN
1170
1171 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 1000);
1172
1173 if (p_total_requests > 1) then
1174
1175 insert into fa_worker_jobs
1176 (start_range, end_range, worker_num, status,request_id)
1177 select min(asset_id), max(asset_id), 0,
1178 'UNASSIGNED', p_parent_request_id from ( select /*+ parallel(dh) */
1179 asset_id, floor(rank()
1180 over (order by asset_id)/l_batch_size ) unit_id
1181 from fa_books
1182 where book_type_code = p_book_type_code )
1183 group by unit_id;
1184
1185 if g_print_debug then
1186 fa_debug_pkg.add(l_calling_fn, 'rows inserted into worker jobs: ', SQL%ROWCOUNT);
1187 end if;
1188
1189 x_worker_jobs := sql%rowcount;
1190
1191 commit;
1192 end if;
1193
1194 if g_print_debug then
1195 fa_debug_pkg.add(l_calling_fn, 'rows inserted into worker jobs: ', x_worker_jobs);
1196 end if;
1197
1198 x_return_status := 0;
1199
1200 EXCEPTION
1201 when OTHERS then
1202 fa_srvr_msg.add_sql_error(calling_fn => 'FARX_C_WD.WHATIF');
1203 rollback;
1204 if (g_print_debug) then
1205 fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1206 end if;
1207 x_return_status := 2;
1208
1209 END Load_Workers;
1210
1211 END FARX_C_WD;