[Home] [Help]
PACKAGE BODY: APPS.OTA_TFL_API_BUSINESS_RULES2
Source
1 Package Body ota_tfl_api_business_rules2 as
2 /* $Header: ottfl03t.pkb 115.6 2002/11/29 09:25:00 arkashya ship $ */
3 --
4 --
5 -- Global package name
6 --
7 g_package varchar2(33) := ' ota_tfl_api_business_rules2.';
8 --
9 -- Global api dml status
10 --
11 g_api_dml boolean;
12 --
13 --
14 -- ----------------------------------------------------------------------------
15 -- |---------------------------< set_type_V_amounts >-------------------------|
16 -- ----------------------------------------------------------------------------
17 --
18 -- PRIVAT
19 -- Description:
20 --
21 -- Set the amounts standard_amount, money_amount and unitary_amount
22 -- and finance_line is of type VENDOR-PAYMENT.
23 --
24 Procedure set_type_V_amounts
25 (
26 p_resource_booking_id in number
27 ,p_standard_amount in out nocopy number
28 ,p_unitary_amount in out nocopy number
29 ,p_money_amount in out nocopy number
30 ) is
31 --
32 v_absolute_price number(11,2);
33 v_proc varchar2(72) := g_package||'set_type_V_amounts';
34 --
35 cursor sel_type_V_amounts is
36 select trb.absolute_price
37 from ota_resource_bookings trb
38 where trb.resource_booking_id = p_resource_booking_id;
39 --
40 Begin
41 --
42 hr_utility.set_location('Entering:'|| v_proc, 5);
43 --
44 Open sel_type_V_amounts;
45 Fetch sel_type_V_amounts into v_absolute_price;
46 --
47 If sel_type_V_amounts%notfound then
48 --
49 Close sel_type_V_amounts;
50 --
51 fnd_message.set_name('OTA','OTA_13400_TFL_AMOUNT_V');
52 fnd_message.raise_error;
53 --
54 Else
55 --
56 Close sel_type_V_amounts;
57 --
58 p_standard_amount := null;
59 p_unitary_amount := null;
60 --
61 If p_money_amount is null Then
62 --
63 p_money_amount := v_absolute_price;
64 --
65 End if;
66 --
67 End if;
68 --
69 hr_utility.set_location(' Leaving:'|| v_proc, 10);
70 --
71 End set_type_V_amounts;
72 --
73 -- ----------------------------------------------------------------------------
74 -- |--------------------------< set_type_R_amounts >--------------------------|
75 -- ----------------------------------------------------------------------------
76 --
77 -- PRIVAT
78 -- Description:
79 --
80 -- Set the amounts standard_amount, money_amount and unitary_amount
81 -- and finance_line is of type RESOURCE CHARGE.
82 --
83 Procedure set_type_R_amounts
84 (
85 p_resource_allocation_id in number
86 ,p_standard_amount in out nocopy number
87 ,p_unitary_amount in out nocopy number
88 ,p_money_amount in out nocopy number
89 ) is
90 --
91 v_absolute_price number(11,2);
92 v_proc varchar2(72) := g_package||'set_type_R_amounts';
93 --
94 cursor sel_type_R_amounts is
95 select trb.absolute_price
96 from ota_resource_allocations tra
97 , ota_resource_bookings trb
98 where tra.resource_allocation_id = p_resource_allocation_id
99 and tra.charge_delegate_flag = 'Y'
100 and trb.resource_booking_id = tra.equipment_resource_booking_id;
101 --
102 Begin
103 --
104 hr_utility.set_location('Entering:'|| v_proc, 5);
105 --
106 Open sel_type_R_amounts;
107 Fetch sel_type_R_amounts into v_absolute_price;
108 --
109 If sel_type_R_amounts%notfound then
110 --
111 Close sel_type_R_amounts;
112 --
113 fnd_message.set_name('OTA','OTA_13401_TFL_AMOUNT_R');
114 fnd_message.raise_error;
115 --
116 Else
117 --
118 Close sel_type_R_amounts;
119 --
120 p_standard_amount := null;
121 p_unitary_amount := null;
122 --
123 If p_money_amount is null Then
124 --
125 p_money_amount := v_absolute_price;
126 --
127 End if;
128 --
129 End if;
130 --
131 hr_utility.set_location(' Leaving:'|| v_proc, 10);
132 --
133 End set_type_R_amounts;
134 --
135 -- ----------------------------------------------------------------------------
136 -- |---------------------------< set_type_P_amounts >-------------------------|
137 -- ----------------------------------------------------------------------------
138 --
139 -- PRIVAT
140 -- Description:
141 --
142 -- Set the amounts standard_amount, money_amount and unitary_amount
143 -- and finance_line is of type PRE-PURCHASE.
144 --
145 Procedure set_type_P_amounts
146 (
147 p_booking_deal_id in number
148 ,p_currency_precision in number
149 ,p_standard_amount in out nocopy number
150 ,p_unitary_amount in out nocopy number
151 ,p_money_amount in out nocopy number
152 ) is
153 --
154 v_discount_percentage number(11,2);
155 v_price_list_type varchar2(30);
156 v_single_unit_price number(11,2);
157 v_proc varchar2(72) := g_package||'set_type_P_amounts';
158 --
159 cursor sel_type_P_amounts is
160 select nvl( tbd.discount_percentage, 0)
161 , tpl.price_list_type
162 , tpl.single_unit_price
163 from ota_booking_deals tbd
164 , ota_price_lists tpl
165 where tbd.booking_deal_id = p_booking_deal_id
166 and tbd.type = 'P'
167 and tpl.price_list_id = tbd.price_list_id;
168 --
169 Begin
170 --
171 hr_utility.set_location('Entering:'|| v_proc, 5);
172 --
173 Open sel_type_P_amounts;
174 Fetch sel_type_P_amounts into v_discount_percentage
175 , v_price_list_type
176 , v_single_unit_price;
177 --
178 If sel_type_P_amounts%notfound then
179 --
180 Close sel_type_P_amounts;
181 --
182 fnd_message.set_name('OTA','OTA_13402_TFL_AMOUNT_P');
183 fnd_message.raise_error;
184 --
185 Else
186 --
187 Close sel_type_P_amounts;
188 --
189 If v_price_list_type = 'T' Then
190 --
191 -- Pre-purchase deal arranged in training units
192 --
193 p_unitary_amount := nvl( p_unitary_amount, 0);
194 p_standard_amount := null;
195 --
196 If p_money_amount is null Then
197 --
198 p_money_amount := round(v_single_unit_price*p_unitary_amount
199 ,p_currency_precision);
200
201 --
202 End if;
203 --
204 ElsIf v_price_list_type = 'M' Then
205 --
206 -- Pre-purchase deal arranged in money
207 --
208 p_unitary_amount := null;
209 p_standard_amount := null;
210 p_money_amount := p_money_amount;
211 --
212 End if;
213 --
214 End if;
215 --
216 hr_utility.set_location(' Leaving:'|| v_proc, 10);
217 --
218 End set_type_P_amounts;
219 --
220 -- ----------------------------------------------------------------------------
221 -- |------------------------< get_booking_deal_info >-------------------------|
222 -- ----------------------------------------------------------------------------
223 --
224 -- PRIVAT
225 -- Description:
226 --
227 -- Get the relevant existing booking deal informations.
228 --
229 Procedure get_booking_deal_info
230 (
231 p_booking_deal_id in number
232 ,p_event_id in number
233 ,p_book_deal_type out nocopy varchar2
234 ,p_discount_percentage out nocopy number
235 ,p_price_list_id out nocopy number
236 ,p_tbd_event_id out nocopy number
237 ,p_activity_version_id out nocopy number
238 ,p_category out nocopy varchar2
239 ,p_price_list_type out nocopy varchar2
240 ,p_single_unit_price out nocopy number
241 ) is
242 --
243 v_proc varchar2(72) := g_package||'get_booking_deal_info';
244 --
245 cursor sel_book_deal_info is
246 select tbd.type
247 , nvl( tbd.discount_percentage, 0)
248 , tbd.price_list_id
249 , tbd.event_id
250 , tbd.activity_version_id
251 , tbd.category
252 , tpl.price_list_type
253 , nvl( tpl.single_unit_price, 0)
254 from ota_booking_deals tbd
255 , ota_price_lists tpl
256 , ota_events evt
257 where tbd.booking_deal_id = p_booking_deal_id
258 and tpl.price_list_id (+) = tbd.price_list_id
259 and evt.event_id = p_event_id
260 and evt.course_start_date between
261 nvl(tbd.start_date,evt.course_start_date)
262 and nvl(tbd.end_date,evt.course_start_date);
263 --
264 Begin
265 --
266 hr_utility.set_location('Entering:'|| v_proc, 5);
267 --
268 Open sel_book_deal_info;
269 Fetch sel_book_deal_info into p_book_deal_type
270 , p_discount_percentage
271 , p_price_list_id
272 , p_tbd_event_id
273 , p_activity_version_id
274 , p_category
275 , p_price_list_type
276 , p_single_unit_price;
277 --
278 If sel_book_deal_info%notfound then
279 --
280 Close sel_book_deal_info;
281 --
282 fnd_message.set_name('OTA','OTA_13403_TFL_BOOK_DEAL');
283 fnd_message.raise_error;
284 --
285 End if;
286 --
287 Close sel_book_deal_info;
288 --
289 hr_utility.set_location(' Leaving:'|| v_proc, 10);
290 --
291 End get_booking_deal_info;
292 --
293 -- ----------------------------------------------------------------------------
294 -- |-----------------------< set_type_E_tpe_amounts >--------------------------|
295 -- ----------------------------------------------------------------------------
296 --
297 -- PRIVAT
298 -- Description:
299 --
300 -- Set the amounts standard_amount, money_amount and unitary_amount
301 -- and finance_line is of type ENROLLMENT. A booking deal does exist
302 -- and applied to PRICE_LIST.
303 -- The price list entry vendor must be the same vendor as on the event,
304 -- or the price list entry vendor may be null.
305 --
306 Procedure set_type_E_tpe_amounts
307 (
308 p_price_list_type in varchar2
309 ,p_price_list_id in number
310 ,p_event_id in number
311 ,p_activity_version_id in number
312 ,p_price_basis in varchar2
313 ,p_standard_amount in out nocopy number
314 ,p_unitary_amount in out nocopy number
315 ,p_money_amount in out nocopy number
316 ,p_single_unit_price in number
317 ,p_currency_code in varchar2
318 ,p_currency_precision in number
319 ,p_discount_percentage in number
320 ,p_number_of_places in number
321 ,p_cust_no_places in number
322 ) is
323 --
324 v_tpe_price number;
325 v_evt_price number;
326 v_evt_vendor_id number(9);
327 v_tvs_vendor_id number(9);
328 v_event_id number;
329 v_proc varchar2(72) := g_package||'set_type_E_tpe_amounts';
330 --
331 cursor sel_type_E_tpe_event is
332 select decode(p_price_list_type,'T',tpe.price
333 ,tpe.price)
334 , evt.standard_price
335 from ota_events evt
336 , ota_price_list_entries tpe
337 , ota_price_lists tpl
338 where evt.event_id = p_event_id
339 and evt.currency_code = p_currency_code
340 and tpe.price_basis = p_price_basis
341 and tpe.price_list_id = p_price_list_id
342 and tpl.price_list_id = p_price_list_id
343 and tpl.currency_code = p_currency_code
344 and (tpe.price_basis = 'S'
345 or (tpe.price_basis = 'C'
346 and p_cust_no_places between
347 tpe.minimum_attendees and tpe.maximum_attendees))
348 and evt.course_start_date between
349 tpe.start_date and nvl(tpe.end_date,evt.course_start_date)
350 and (( evt.activity_version_id = tpe.activity_version_id
351 and not exists
352 (select null
353 from ota_vendor_supplies tvs
354 , ota_price_list_entries tpe2
355 where tvs.vendor_id = evt.vendor_id
356 and tvs.activity_version_id = evt.activity_version_id
357 and tpe2.vendor_supply_id = tvs.vendor_supply_id
358 and tpe2.price_list_id = p_price_list_id
359 and evt.course_start_date between
360 tpe2.start_date and nvl(tpe2.end_date,evt.course_start_date)
361 )
362 )
363 or
364 ( exists
365 (select null
366 from ota_vendor_supplies tvs
367 where tvs.vendor_id = evt.vendor_id
368 and tvs.activity_version_id = evt.activity_version_id
369 and tpe.vendor_supply_id = tvs.vendor_supply_id)
370 ));
371 --
372 Begin
373 --
374 hr_utility.set_location('Entering:'|| v_proc, 5);
375 --
376 -- Values of parameters
377 --
378 hr_utility.trace(p_price_list_type);
379 hr_utility.trace(p_price_list_id);
380 hr_utility.trace(p_event_id);
381 hr_utility.trace(p_activity_version_id);
382 hr_utility.trace(p_price_basis);
383 hr_utility.trace(p_standard_amount);
384 hr_utility.trace(p_unitary_amount);
385 hr_utility.trace(p_money_amount);
386 hr_utility.trace(p_single_unit_price);
387 hr_utility.trace(p_currency_code);
388 hr_utility.trace(p_currency_precision);
389 hr_utility.trace(p_discount_percentage);
390 hr_utility.trace(p_number_of_places);
391 hr_utility.trace(p_cust_no_places);
392 --
393 --
394 Open sel_type_E_tpe_event;
395 Fetch sel_type_E_tpe_event into v_tpe_price, v_evt_price;
396 --
397 -- * v_price includes either the value of money or trainig_unit
398 --
399 If sel_type_E_tpe_event%notfound then
400 --
401 Close sel_type_E_tpe_event;
402 --
403 fnd_message.set_name('OTA','OTA_13404_TFL_AMOUNT_EP');
404 fnd_message.raise_error;
405 --
406 Else
407 --
408 Close sel_type_E_tpe_event;
409 --
410 --
411 if p_price_basis = 'C' then
412 null;
413 else
414 p_standard_amount := v_evt_price;
415 end if;
416 --
417 If p_price_list_type = 'T' Then
418 --
419 -- * Pre-purchase deal arranged in training units
420 --
421 If p_unitary_amount is null Then
422 --
423 p_unitary_amount :=
424 round(v_tpe_price*p_number_of_places*
425 (1 - p_discount_percentage/100) ,2);
426 --
427 End if;
428 --
429 -- If dealing with training units always recalculate the money
430 -- amount, the units may vary
431 --
432 p_money_amount := round(
433 p_unitary_amount*
434 p_single_unit_price
435 ,p_currency_precision);
436 --
437 ElsIf p_price_list_type = 'M' Then
441 p_unitary_amount := null;
438 --
439 -- * Pre-purchase or Discount deal arranged in money
440 --
442 --
443 If p_money_amount is null Then
444 --
445 --
446 -- GP Changed so that standard price taken from price list
447 --
448 p_standard_amount := v_tpe_price;
449 p_money_amount :=
450 round(p_standard_amount*p_number_of_places*
451 (1 - p_discount_percentage/100)
452 ,p_currency_precision);
453 --
454 End if;
455 --
456 End if;
457 --
458 End if;
459 --
460 hr_utility.trace(p_price_list_type);
461 hr_utility.trace(p_price_list_id);
462 hr_utility.trace(p_event_id);
463 hr_utility.trace(p_activity_version_id);
464 hr_utility.trace(p_price_basis);
465 hr_utility.trace(p_standard_amount);
466 hr_utility.trace(p_unitary_amount);
467 hr_utility.trace(p_money_amount);
468 hr_utility.trace(p_single_unit_price);
469 hr_utility.trace(p_currency_code);
470 hr_utility.trace(p_currency_precision);
471 hr_utility.trace(p_discount_percentage);
472 hr_utility.trace(p_number_of_places);
473 hr_utility.trace(p_cust_no_places);
474 --
475 hr_utility.set_location(' Leaving:'|| v_proc, 10);
476 --
477 End set_type_E_tpe_amounts;
478 --
479 --
480 -- ----------------------------------------------------------------------------
481 -- |------------------------< set_type_E_evt_amounts >------------------------|
482 -- ----------------------------------------------------------------------------
483 --
484 -- PRIVAT
485 -- Description:
486 --
487 -- Set the amounts standard_amount, money_amount and unitary_amount
488 -- and finance_line is of type ENROLLMENT. A booking deal does exist
489 -- and applied to EVENT.
490 --
491 Procedure set_type_E_evt_amounts
492 (
493 p_event_id in number
494 ,p_tbd_event_id in number
495 ,p_price_basis in varchar2
496 ,p_standard_amount in out nocopy number
497 ,p_unitary_amount in out nocopy number
498 ,p_money_amount in out nocopy number
499 ,p_currency_code in varchar2
500 ,p_currency_precision in number
501 ,p_discount_percentage in number
502 ,p_number_of_places in number
503 ) is
504 --
505 v_standard_price number(11,2);
506 v_proc varchar2(72) := g_package||'set_type_E_evt_amounts';
507 --
508 cursor sel_type_E_evt_event is
509 select evt.standard_price
510 from ota_events evt
511 where evt.event_id = p_event_id
512 and evt.event_id = p_tbd_event_id
513 and evt.currency_code = p_currency_code;
514 --
515 Begin
516 --
517 hr_utility.set_location('Entering:'|| v_proc, 5);
518 --
519 -- * Take Event for searching
520 --
521 If p_event_id is NOT null Then
522 --
523 Open sel_type_E_evt_event;
524 Fetch sel_type_E_evt_event into v_standard_price;
525 --
526 If sel_type_E_evt_event%notfound then
527 --
528 Close sel_type_E_evt_event;
529 --
530 fnd_message.set_name('OTA','OTA_13405_TFL_SET_AMOUNT_E');
531 fnd_message.raise_error;
532 --
533 Else
534 --
535 Close sel_type_E_evt_event;
536 --
537 if p_price_basis = 'C' then
538 null;
539 else
540 p_standard_amount := v_standard_price;
541 end if;
542 p_unitary_amount := null;
543 --
544 If p_money_amount is null Then
545 --
546 p_money_amount :=
547 round(p_standard_amount*p_number_of_places*
548 (1 - p_discount_percentage/100)
549 ,p_currency_precision);
550 --
551 End if;
552 --
553 End if;
554 --
555 End if;
556 End set_type_E_evt_amounts;
557 --
558 -- ----------------------------------------------------------------------------
559 -- |------------------------< set_type_E_tav_amounts >------------------------|
560 -- ----------------------------------------------------------------------------
561 --
562 -- PRIVAT
563 -- Description:
564 --
565 -- Set the amounts standard_amount, money_amount and unitary_amount
566 -- and finance_line is of type ENROLLMENT. A booking deal does exist
567 -- and applied to ACTIVITY_VERSION.
568 --
569 Procedure set_type_E_tav_amounts
570 (
571 p_event_id in number
572 ,p_activity_version_id in number
573 ,p_price_basis in varchar2
574 ,p_standard_amount in out nocopy number
575 ,p_unitary_amount in out nocopy number
576 ,p_money_amount in out nocopy number
577 ,p_currency_code in varchar2
578 ,p_currency_precision in number
582 --
579 ,p_discount_percentage in number
580 ,p_number_of_places in number
581 ) is
583 v_standard_price number(11,2);
584 v_proc varchar2(72) := g_package||'set_type_E_tav_amounts';
585 --
586 cursor sel_type_E_tav_event is
587 select evt.standard_price
588 from ota_events evt
589 where evt.event_id = p_event_id
590 and evt.activity_version_id = p_activity_version_id
591 and evt.currency_code = p_currency_code;
592 --
593 Begin
594 --
595 hr_utility.set_location('Entering:'|| v_proc, 5);
596 --
597 --
598 Open sel_type_E_tav_event;
599 Fetch sel_type_E_tav_event into v_standard_price;
600 --
601 If sel_type_E_tav_event%notfound then
602 --
603 Close sel_type_E_tav_event;
604 --
605 fnd_message.set_name('OTA','OTA_13406_TFL_SET_AMOUNT_A');
606 fnd_message.raise_error;
607 --
608 Else
609 --
610 Close sel_type_E_tav_event;
611 --
612 if p_price_basis = 'C' then
613 null;
614 else
615 p_standard_amount := v_standard_price;
616 end if;
617 p_unitary_amount := null;
618 --
619 If p_money_amount is null Then
620 --
621 p_money_amount :=
622 round(p_standard_amount*p_number_of_places*
623 (1 - p_discount_percentage/100)
624 ,p_currency_precision);
625 --
626 End if;
627 --
628 End if;
629 --
630 hr_utility.set_location(' Leaving:'|| v_proc, 10);
631 --
632 End set_type_E_tav_amounts;
633 --
634 -- ----------------------------------------------------------------------------
635 -- |------------------------< set_type_E_cat_amounts >------------------------|
636 -- ----------------------------------------------------------------------------
637 --
638 -- PRIVAT
639 -- Description:
640 --
641 -- Set the amounts standard_amount, money_amount and unitary_amount
642 -- and finance_line is of type ENROLLMENT. A booking deal does exist
643 -- and applied to CATEGORY.
644 --
645 Procedure set_type_E_cat_amounts
646 (
647 p_event_id in number
648 ,p_category in varchar2
649 ,p_price_basis in varchar2
650 ,p_standard_amount in out nocopy number
651 ,p_unitary_amount in out nocopy number
652 ,p_money_amount in out nocopy number
653 ,p_currency_code in varchar2
654 ,p_currency_precision in number
655 ,p_discount_percentage in number
656 ,p_number_of_places in number
657 ) is
658 --
659 v_standard_price number(11,2);
660 v_proc varchar2(72) := g_package||'set_type_E_cat_amounts';
661 --
662 cursor sel_type_E_cat_event is
663 select evt.standard_price
664 from ota_events evt
665 , ota_act_cat_inclusions aci
666 where evt.event_id = p_event_id
667 and aci.activity_version_id = evt.activity_version_id
668 and aci.activity_category = p_category
669 and evt.currency_code = p_currency_code;
670 --
671 Begin
672 --
673 hr_utility.set_location('Entering:'|| v_proc, 5);
674 --
675 -- Take Event and Activity Version for searching
676 --
677 Open sel_type_E_cat_event;
678 Fetch sel_type_E_cat_event into v_standard_price;
679 --
680 If sel_type_E_cat_event%notfound then
681 --
682 Close sel_type_E_cat_event;
683 --
684 fnd_message.set_name('OTA','OTA_13407_TFL_SET_AMOUNT_C');
685 fnd_message.raise_error;
686 --
687 Else
688 --
689 Close sel_type_E_cat_event;
690 --
691 if p_price_basis = 'C' then
692 null;
693 else
694 p_standard_amount := v_standard_price;
695 end if;
696 p_unitary_amount := null;
697 --
698 If p_money_amount is null Then
699 --
700 p_money_amount :=
701 round(p_standard_amount*p_number_of_places*
702 (1 - p_discount_percentage/100)
703 ,p_currency_precision);
704 --
705 End if;
706 --
707 End if;
708 --
709 hr_utility.set_location(' Leaving:'|| v_proc, 10);
710 --
711 End set_type_E_cat_amounts;
712 --
713 -- ----------------------------------------------------------------------------
714 -- |-------------------------< set_no_deal_amounts >--------------------------|
715 -- ----------------------------------------------------------------------------
716 --
717 -- PRIVAT
718 -- Description:
719 --
720 -- Set the amounts standard_amount, money_amount and unitary_amount
721 -- and finance_line is of type ENROLLMENT. A booking deal does NOT exist.
722 --
723 Procedure set_no_deal_amounts
724 (
725 p_event_id in number
726 ,p_currency_code in varchar2
727 ,p_price_basis in varchar2
728 ,p_standard_amount in out nocopy number
729 ,p_unitary_amount in out nocopy number
733 --
730 ,p_money_amount in out nocopy number
731 ,p_number_of_places in number
732 ) is
734 v_standard_price number;
735 v_proc varchar2(72) := g_package||'set_no_deal_amounts';
736 --
737 cursor sel_event_no_deal is
738 select evt.standard_price
739 from ota_events evt
740 where evt.event_id = p_event_id
741 and evt.currency_code = p_currency_code;
742 --
743 Begin
744 --
745 hr_utility.set_location('Entering:'|| v_proc, 5);
746 --
747 --
748 If p_event_id is not null then
749 Open sel_event_no_deal;
750 Fetch sel_event_no_deal into v_standard_price;
751 --
752 If sel_event_no_deal%notfound then
753 --
754 Close sel_event_no_deal;
755 --
756 fnd_message.set_name('OTA','OTA_13408_TFL_SET_AMOUNT');
757 fnd_message.raise_error;
758 --
759 Else
760 --
761 Close sel_event_no_deal;
762 --
763 if p_price_basis = 'C' then
764 null;
765 else
766 p_standard_amount := v_standard_price;
767 end if;
768 p_unitary_amount := null;
769 --
770 If p_money_amount is null Then
771 --
772 p_money_amount := p_standard_amount*p_number_of_places;
773 --
774 End if;
775 --
776 End if;
777 --
778 End if;
779 --
780 hr_utility.set_location(' Leaving:'|| v_proc, 10);
781 --
782 End set_no_deal_amounts;
783 --
784 -- ----------------------------------------------------------------------------
785 -- |----------------------------< set_all_amounts >---------------------------|
786 -- ----------------------------------------------------------------------------
787 --
788 -- PUBLIC
789 -- Description:
790 --
791 -- Set the AMOUNTS standard_amount, money_amount and unitary_amount
792 -- depending on the finance_line type and the booking deal type.
793 --
794 Procedure set_all_amounts
795 (
796 p_finance_line_type in varchar2
797 ,p_activity_version_id in number
798 ,p_event_id in number
799 ,p_price_basis in varchar2 default null
800 ,p_booking_id in number
801 ,p_number_of_places in number default 1
802 ,p_booking_deal_id in number
803 ,p_resource_allocation_id in number
804 ,p_resource_booking_id in number
805 ,p_currency_code in varchar2
806 ,p_standard_amount in out nocopy number
807 ,p_money_amount in out nocopy number
808 ,p_unitary_amount in out nocopy number
809 ) is
810 --
811 v_book_deal_type varchar2(30);
812 v_discount_percentage number;
813 v_price_list_type varchar(30);
814 v_single_unit_price number(11,2);
815 v_price_list_id number(9);
816 v_event_id number(9);
817 v_tbd_event_id number(9);
818 v_activity_version_id number(9);
819 v_category varchar2(30);
820 v_currency_precision number;
821 v_number_of_places number;
822 v_cust_no_places number;
823 v_price_basis varchar2(30);
824 v_proc varchar2(72) := g_package||'set_all_amounts';
825 --
826 cursor get_currency_precision is
827 select precision
828 from fnd_currencies
829 where currency_code = p_currency_code;
830 --
831 cursor get_booking is
832 select tdb.event_id
833 , tdb.number_of_places
834 , nvl(evt.price_basis,'S')
835 from ota_delegate_bookings tdb
836 , ota_events evt
837 where tdb.booking_id = p_booking_id
838 and tdb.event_id = evt.event_id;
839
840 Begin
841 --
842 hr_utility.set_location('Entering:'|| v_proc, 5);
843 --
844 open get_currency_precision;
845 fetch get_currency_precision into v_currency_precision;
846 close get_currency_precision;
847 --
848 If p_finance_line_type = 'V' Then
849 --
850 -- * VENDOR PAYMENT
851 --
852 set_type_V_amounts( p_resource_booking_id
853 , p_standard_amount
854 , p_unitary_amount
855 , p_money_amount );
856 --
857 ElsIf p_finance_line_type = 'R' Then
858 --
859 -- * RESOURCE CHARGE
860 --
861 set_type_R_amounts( p_resource_allocation_id
862 , p_standard_amount
863 , p_unitary_amount
864 , p_money_amount );
865 --
866 ElsIf p_finance_line_type = 'P' Then
867 --
868 -- * PRE-PURCHASE
869 --
870 set_type_P_amounts( p_booking_deal_id
871 , v_currency_precision
872 , p_standard_amount
873 , p_unitary_amount
874 , p_money_amount );
875 --
876 ElsIf p_finance_line_type = 'E' Then
877 --
878 -- * ENROLLMENT CHARGE
879 --
880 if p_event_id is null then
881 open get_booking;
885 close get_booking;
882 fetch get_booking into v_event_id
883 ,v_number_of_places
884 ,v_price_basis;
886 else
887 v_event_id := p_event_id;
888 v_number_of_places := p_number_of_places;
889 v_price_basis := p_price_basis;
890 end if;
891 --
892 /*
893 Set the number of places to 1 if the Price Basis is 'Customer' because
894 the standard price represents the price for the entire booking (not per place)
895 --
896 Store the actual number of places (used to derive price from Customer based
897 price list)
898 */
899 if v_price_basis = 'C' then
900 v_number_of_places := 1;
901 v_cust_no_places := p_number_of_places;
902 end if;
903 --
904 --
905 If p_booking_deal_id is NOT null Then
906 --
907 get_booking_deal_info( p_booking_deal_id
908 , v_event_id
909 , v_book_deal_type
910 , v_discount_percentage
911 , v_price_list_id
912 , v_tbd_event_id
913 , v_activity_version_id
914 , v_category
915 , v_price_list_type
916 , v_single_unit_price );
917 --
918 /*
919 If the Price Basis is 'C' then the Standard Amount is used to calculate
920 the monetary and unitary amounts.
921
922 However the SQL that is used to retrieve the Standard Price for Price Basis
923 of 'S' is still used to establish that the Event and Booking Deals are
924 compatible.
925 */
926 If v_book_deal_type = 'P' Then
927 --
928 -- * PRE-PURCHASE DEAL
929 --
930 set_type_E_tpe_amounts( v_price_list_type
931 , v_price_list_id
932 , v_event_id
933 , p_activity_version_id
934 , v_price_basis
935 , p_standard_amount
936 , p_unitary_amount
937 , p_money_amount
938 , v_single_unit_price
939 , p_currency_code
940 , v_currency_precision
941 , v_discount_percentage
942 , v_number_of_places
943 , v_cust_no_places);
944 --
945 Elsif v_book_deal_type = 'D' Then
946 --
947 -- * DISCOUNT DEAL
948 -- The discount deal must be for either the event of the delegate booking,
949 -- the activity version on the event, or the activity must be within the
950 -- category defined on the discount deal, or the activity is included within
951 -- the price list for the referenced discont deal. When the discount deal
952 -- is for a price list, if the vendor supplier is entered on the
953 -- price list entry then the supplier of the event must match it.
954 -- The starting price for the discount comes from this price list
955 -- entry. When a discount is applied, the original value is placed in the
956 -- STANDARD_AMOUNT attribute.
957 --
958 If v_price_list_id is NOT null Then
959 --
960 set_type_E_tpe_amounts( v_price_list_type
961 , v_price_list_id
962 , v_event_id
963 , p_activity_version_id
964 , v_price_basis
965 , p_standard_amount
966 , p_unitary_amount
967 , p_money_amount
968 , v_single_unit_price
969 , p_currency_code
970 , v_currency_precision
971 , v_discount_percentage
972 , v_number_of_places
973 , v_cust_no_places);
974 --
975 ElsIf v_tbd_event_id is NOT null Then
976 --
977 set_type_E_evt_amounts( v_event_id
978 , v_tbd_event_id
979 , v_price_basis
980 , p_standard_amount
981 , p_unitary_amount
982 , p_money_amount
983 , p_currency_code
984 , v_currency_precision
985 , v_discount_percentage
986 , v_number_of_places);
987 --
988 ElsIf v_activity_version_id is NOT null Then
989 --
990 set_type_E_tav_amounts( v_event_id
991 , v_activity_version_id
992 , v_price_basis
993 , p_standard_amount
994 , p_unitary_amount
995 , p_money_amount
996 , p_currency_code
997 , v_currency_precision
998 , v_discount_percentage
1002 --
999 , v_number_of_places);
1000 --
1001 ElsIf v_category is NOT null Then
1003 set_type_E_cat_amounts( v_event_id
1004 , v_category
1005 , v_price_basis
1006 , p_standard_amount
1007 , p_unitary_amount
1008 , p_money_amount
1009 , p_currency_code
1010 , v_currency_precision
1011 , v_discount_percentage
1012 , v_number_of_places);
1013 --
1014 End if;
1015 --
1016 End if;
1017 --
1018 Else
1019 --
1020 -- * NO DEAL for the booking is available
1021 --
1022 set_no_deal_amounts( v_event_id
1023 , p_currency_code
1024 , v_price_basis
1025 , p_standard_amount
1026 , p_unitary_amount
1027 , p_money_amount
1028 , v_number_of_places);
1029 --
1030 End if;
1031 --
1032 End if;
1033 --
1034 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1035 --
1036 End set_all_amounts;
1037 --
1038 -- ----------------------------------------------------------------------------
1039 -- |--------------------------< copy_finance_line >---------------------------|
1040 -- ----------------------------------------------------------------------------
1041 --
1042 -- PUBLIC
1043 -- Description:
1044 --
1045 Procedure copy_finance_line
1046 (
1047 p_finance_header_id_to in number
1048 ,p_rec_finance_line in out nocopy ota_finance_lines%rowtype
1049 ,p_transaction_type in varchar2
1050 ) is
1051 --
1052 v_proc varchar2(72) := g_package||'copy_finance_line';
1053 l_finance_line_id number := null;
1054 l_finance_header_id number := p_finance_header_id_to;
1055 l_transfer_status varchar2(2) := 'NT';
1056 --Bug 1664464
1057 l_transfer_date ota_finance_lines.transfer_date%TYPE := null;
1058 --Bug 1664464
1059 --
1060 Begin
1061 --
1062 hr_utility.set_location('Entering:'|| v_proc, 5);
1063 --
1064 -- For 7.3.2
1065 -- p_rec_finance_line.finance_header_id := p_finance_header_id_to;
1066 -- p_rec_finance_line.finance_line_id := null;
1067 -- p_rec_finance_line.transfer_status := 'NT';
1068 --
1069 ota_tfl_api_ins.ins
1070 ( l_finance_line_id
1071 , l_finance_header_id
1072 , p_rec_finance_line.cancelled_flag
1073 , p_rec_finance_line.date_raised
1074 , p_rec_finance_line.line_type
1075 , p_rec_finance_line.object_version_number
1076 , p_rec_finance_line.sequence_number
1077 , l_transfer_status
1078 , p_rec_finance_line.comments
1079 , p_rec_finance_line.currency_code
1080 , p_rec_finance_line.money_amount
1081 , p_rec_finance_line.standard_amount
1082 , p_rec_finance_line.trans_information_category
1083 , p_rec_finance_line.trans_information1
1084 , p_rec_finance_line.trans_information10
1085 , p_rec_finance_line.trans_information11
1086 , p_rec_finance_line.trans_information12
1087 , p_rec_finance_line.trans_information13
1088 , p_rec_finance_line.trans_information14
1089 , p_rec_finance_line.trans_information15
1090 , p_rec_finance_line.trans_information16
1091 , p_rec_finance_line.trans_information17
1092 , p_rec_finance_line.trans_information18
1093 , p_rec_finance_line.trans_information19
1094 , p_rec_finance_line.trans_information2
1095 , p_rec_finance_line.trans_information20
1096 , p_rec_finance_line.trans_information3
1097 , p_rec_finance_line.trans_information4
1098 , p_rec_finance_line.trans_information5
1099 , p_rec_finance_line.trans_information6
1100 , p_rec_finance_line.trans_information7
1101 , p_rec_finance_line.trans_information8
1102 , p_rec_finance_line.trans_information9
1103 , l_transfer_date -- p_rec_finance_line.transfer_date Bug 1664464
1104 , p_rec_finance_line.transfer_message
1105 , p_rec_finance_line.unitary_amount
1106 , p_rec_finance_line.booking_deal_id
1107 , p_rec_finance_line.booking_id
1108 , p_rec_finance_line.resource_allocation_id
1109 , p_rec_finance_line.resource_booking_id
1110 , p_rec_finance_line.tfl_information_category
1111 , p_rec_finance_line.tfl_information1
1112 , p_rec_finance_line.tfl_information2
1113 , p_rec_finance_line.tfl_information3
1114 , p_rec_finance_line.tfl_information4
1115 , p_rec_finance_line.tfl_information5
1119 , p_rec_finance_line.tfl_information9
1116 , p_rec_finance_line.tfl_information6
1117 , p_rec_finance_line.tfl_information7
1118 , p_rec_finance_line.tfl_information8
1120 , p_rec_finance_line.tfl_information10
1121 , p_rec_finance_line.tfl_information11
1122 , p_rec_finance_line.tfl_information12
1123 , p_rec_finance_line.tfl_information13
1124 , p_rec_finance_line.tfl_information14
1125 , p_rec_finance_line.tfl_information15
1126 , p_rec_finance_line.tfl_information16
1127 , p_rec_finance_line.tfl_information17
1128 , p_rec_finance_line.tfl_information18
1129 , p_rec_finance_line.tfl_information19
1130 , p_rec_finance_line.tfl_information20
1131 , p_validate => false
1132 , p_transaction_type => p_transaction_type);
1133 --
1134 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1135 --
1136 End copy_finance_line;
1137 --
1138 -- ----------------------------------------------------------------------------
1139 -- |-----------------------< copy_lines_to_new_header >-----------------------|
1140 -- ----------------------------------------------------------------------------
1141 --
1142 -- PUBLIC
1143 -- Description:
1144 -- A procedure is required to copy all finance lines from one finance
1145 -- header to another. Only those lines with a cancelled_flag = 'N'
1146 -- will be copied.
1147 --
1148 Procedure copy_lines_to_new_header
1149 (
1150 p_finance_header_id_from in number
1151 ,p_finance_header_id_to in number
1152 ) is
1153 --
1154 l_rec_finance_line ota_finance_lines%rowtype;
1155 v_proc varchar2(72) := g_package||'copy_lines_to_new_header';
1156 --
1157 cursor sel_finance_lines is
1158 select *
1159 from ota_finance_lines tfl
1160 where tfl.finance_header_id = p_finance_header_id_from
1161 and tfl.cancelled_flag = 'N'
1162 order by sequence_number;
1163 --
1164 Begin
1165 --
1166 hr_utility.set_location('Entering:'|| v_proc, 5);
1167 --
1168 If p_finance_header_id_from is NOT null Then
1169 --
1170 If p_finance_header_id_to is null OR
1171 p_finance_header_id_to = p_finance_header_id_from Then
1172 --
1173 fnd_message.set_name('OTA','OTA_13359_TFL_COPY_LINES');
1174 fnd_message.raise_error;
1175 --
1176 End if;
1177 --
1178 Open sel_finance_lines;
1179 Fetch sel_finance_lines into l_rec_finance_line;
1180 --
1181 If sel_finance_lines%notfound then
1182 --
1183 Close sel_finance_lines;
1184 --
1185 Else
1186 --
1187 Loop
1188 --
1189 Exit when sel_finance_lines%notfound;
1190 --
1191 hr_utility.trace('Finance Line ID = '||to_char(l_rec_finance_line.finance_line_id)) ;
1192 copy_finance_line( p_finance_header_id_to
1193 , l_rec_finance_line
1194 ,p_transaction_type => 'COPY');
1195 --
1196 Fetch sel_finance_lines into l_rec_finance_line;
1197 --
1198 End Loop;
1199 --
1200 Close sel_finance_lines;
1201 --
1202 End if;
1203 --
1204 End if;
1205 --
1206 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1207 --
1208 End copy_lines_to_new_header;
1209 --
1210 -- ----------------------------------------------------------------------------
1211 -- |----------------------< update_cancel_flag >-------------------------|
1212 -- ----------------------------------------------------------------------------
1213 --
1214 -- PRIVAT
1215 -- Description:
1216 --
1217 -- A procedure is required to set the attribute cancelled_flag
1218 -- The new cancelled flag is passed in as a parameter
1219 --
1220 Procedure update_cancel_flag
1221 (
1222 p_rec_cancel in out nocopy ota_finance_lines%rowtype
1223 ,p_new_cancelled_flag in varchar2
1224 ,p_transaction_type in varchar2
1225 ,p_object_version_number in out nocopy number
1226 ) is
1227 --
1228 v_proc varchar2(72) := g_package||'update_cancel_flag';
1229 --
1230 Begin
1231 --
1232 hr_utility.set_location('Entering:'|| v_proc, 5);
1233 --
1234 If p_rec_cancel.finance_line_id is null Then
1235 --
1236 fnd_message.set_name('OTA','OTA_13360_TFL_CANCEL_LINES');
1237 fnd_message.raise_error;
1238 --
1239 hr_utility.set_location('Entering:'|| v_proc, 5);
1240 Else
1241 --
1242 -- Cancel the finance_lines record
1243 --
1244 p_rec_cancel.cancelled_flag := p_new_cancelled_flag;
1245 --
1246 hr_utility.set_location('Entering:'|| v_proc, 15);
1247 ota_tfl_api_upd.upd
1248 ( p_finance_line_id => p_rec_cancel.finance_line_id
1249 , p_date_raised => p_rec_cancel.date_raised
1250 , p_cancelled_flag => p_rec_cancel.cancelled_flag
1251 , p_object_version_number=> p_rec_cancel.object_version_number
1255 --
1252 , p_sequence_number => p_rec_cancel.sequence_number
1253 , p_validate => false
1254 ,p_transaction_type => p_transaction_type);
1256 p_object_version_number := p_rec_cancel.object_version_number;
1257 --
1258 End if;
1259 --
1260 hr_utility.set_location(' Leaving:'|| v_proc, 15);
1261 --
1262 End update_cancel_flag;
1263 --
1264 -- ----------------------------------------------------------------------------
1265 -- |----------------------< set_cancel_flag_for_header>-----------------------|
1266 -- ----------------------------------------------------------------------------
1267 --
1268 -- PUBLIC
1269 -- Description:
1270 -- A procedure is required to call the cancellation procedure for
1271 -- each finance line defined for a given finance header.
1272 --
1273 Procedure set_cancel_flag_for_header
1274 (
1275 p_finance_header_id in number
1276 ,p_new_cancelled_flag in varchar2
1277 ) is
1278 --
1279 v_rec_finance_line ota_finance_lines%rowtype;
1280 l_transaction_type varchar2(30);
1281 v_proc varchar2(72) := g_package||'set_cancel_flag_for_header';
1282 v_ovn number;
1283 --
1284 cursor sel_finance_lines is
1285 select *
1286 from ota_finance_lines tfl
1287 where tfl.finance_header_id = p_finance_header_id;
1288 --
1289 Begin
1290 --
1291 hr_utility.set_location('Entering:'|| v_proc, 5);
1292 --
1293 If p_finance_header_id is NOT null Then
1294 --
1295 Open sel_finance_lines;
1296 Fetch sel_finance_lines into v_rec_finance_line;
1297 --
1298 If sel_finance_lines%notfound then
1299 --
1300 Close sel_finance_lines;
1301 --
1302 fnd_message.set_name('OTA','OTA_13409_TFL_SET_CANCEL');
1303 fnd_message.raise_error;
1304 --
1305 Else
1306 --
1307 Loop
1308 --
1309 Exit when sel_finance_lines%notfound;
1310 --
1311 If v_rec_finance_line.cancelled_flag <> p_new_cancelled_flag Then
1312 --
1313 if p_new_cancelled_flag = 'Y' then
1314 l_transaction_type := 'CANCEL_HEADER_LINE';
1315 else
1316 l_transaction_type := 'REINSTATE_HEADER_LINE';
1317 end if;
1318 --
1319 update_cancel_flag ( v_rec_finance_line
1320 , p_new_cancelled_flag
1321 ,p_transaction_type => l_transaction_type
1322 ,p_object_version_number => v_ovn);
1323 --
1324 End if;
1325 --
1326 Fetch sel_finance_lines into v_rec_finance_line;
1327 --
1328 End Loop;
1329 --
1330 Close sel_finance_lines;
1331 --
1332 End if;
1333 --
1334 End if;
1335 --
1336 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1337 --
1338 End set_cancel_flag_for_header;
1339 -- ----------------------------------------------------------------------------
1340 -- |------------------------< cancel_finance_line >---------------------------|
1341 -- ----------------------------------------------------------------------------
1342 --
1343 -- PUBLIC
1344 -- Description:
1345 --
1346 -- A procedure is required to set the attribute cancelled_flag to 'Y'.
1347 --
1348 Procedure cancel_finance_line
1349 (
1350 p_finance_line_id in number
1351 ,p_cancelled_flag in out nocopy varchar2
1352 ,p_transfer_status in varchar2
1353 ,p_finance_header_id in number
1354 ,p_object_version_number in out nocopy number
1355 ,p_validate in boolean
1356 ,p_commit in boolean default FALSE
1357 ) is
1358 --
1359 v_proc varchar2(72) := g_package||'cancel_finance_line';
1360 v_header_transfer_status varchar2(30);
1361 v_rec_finance_line ota_finance_lines%rowtype;
1362 --
1363 cursor sel_finance_line is
1364 select *
1365 from ota_finance_lines tfl
1366 where tfl.finance_line_id = p_finance_line_id;
1367 --
1368 cursor sel_finance_header is
1369 select tfh.transfer_status
1370 from ota_finance_headers tfh
1371 where tfh.finance_header_id = p_finance_header_id;
1372 --
1373 Begin
1374 --
1375 hr_utility.set_location('Entering:'|| v_proc, 1);
1376 --
1377 If p_finance_line_id is null Then
1378 hr_utility.set_location('Entering:'|| v_proc, 2);
1379 --
1380 fnd_message.set_name('OTA','OTA_13410_TFL_CANCEL_LINES');
1381 fnd_message.set_token('STEP','1');
1382 fnd_message.raise_error;
1383 --
1384 ElsIf p_cancelled_flag = 'Y' Then
1385 hr_utility.set_location('Entering:'|| v_proc, 3);
1386 --
1387 -- * Line is cancelled
1388 --
1389 fnd_message.set_name('OTA','OTA_13410_TFL_CANCEL_LINES');
1390 fnd_message.set_token('STEP','2');
1391 fnd_message.raise_error;
1392 --
1393 /*
1394 ElsIf p_transfer_status = 'ST' Then
1395 hr_utility.set_location('Entering:'|| v_proc, 4);
1396 --
1397 -- * Line has been successful transferred
1401 fnd_message.raise_error;
1398 --
1399 fnd_message.set_name('OTA','OTA_13410_TFL_CANCEL_LINES');
1400 fnd_message.set_token('STEP','3');
1402 */
1403 Else
1404 --
1405 /*
1406 If p_finance_header_id is NOT null Then
1407 --
1408 Open sel_finance_header;
1409 Fetch sel_finance_header into v_header_transfer_status;
1410 --
1411 If sel_finance_header%notfound then
1412 hr_utility.set_location('Entering:'|| v_proc, 6);
1413 --
1414 Close sel_finance_header;
1415 --
1416 fnd_message.set_name('OTA','OTA_13410_TFL_CANCEL_LINES');
1417 fnd_message.set_token('STEP','4');
1418 fnd_message.raise_error;
1419 --
1420 ElsIf v_header_transfer_status = 'ST' Then
1421 hr_utility.set_location('Entering:'|| v_proc, 7);
1422 --
1423 -- * Finance Header has been successfull transferred
1424 --
1425 fnd_message.set_name('OTA','OTA_13410_TFL_CANCEL_LINES');
1426 fnd_message.set_token('STEP','5');
1427 fnd_message.raise_error;
1428 --
1429 End if;
1430 --
1431 End if;
1432 */
1433 --
1434 Open sel_finance_line;
1435 Fetch sel_finance_line into v_rec_finance_line;
1436 --
1437 If sel_finance_line%notfound then
1438 hr_utility.set_location('Entering:'|| v_proc, 8);
1439 --
1440 Close sel_finance_line;
1441 --
1442 fnd_message.set_name('OTA','OTA_13410_TFL_CANCEL_LINES');
1443 fnd_message.set_token('STEP','6');
1444 fnd_message.raise_error;
1445 --
1446 Else
1447 --
1448 Close sel_finance_line;
1449 --
1450 -- * Cancel the Finance Line record
1451 --
1452 If p_validate = false Then
1453 --
1454 update_cancel_flag (p_rec_cancel => v_rec_finance_line
1455 ,p_new_cancelled_flag => 'Y'
1456 ,p_transaction_type => 'CANCEL_LINE'
1457 ,p_object_version_number => p_object_version_number);
1458 --
1459 p_cancelled_flag := 'Y';
1460 --
1461 End if;
1462 --
1463 End if;
1464 --
1465 End if;
1466 --
1467 if p_commit then
1468 commit;
1469 end if;
1470 hr_utility.set_location(' Leaving:'|| v_proc, 20);
1471 --
1472 End cancel_finance_line;
1473 --
1474 -- ----------------------------------------------------------------------------
1475 -- |------------------------< cancel_finance_line >---------------------------|
1476 -- ----------------------------------------------------------------------------
1477 --
1478 -- PUBLIC
1479 -- Description:
1480 --
1481 -- Overloaded procedure to allow object version number to be ignored from
1482 -- Finance Line Button
1483 --
1484 Procedure cancel_finance_line
1485 (
1486 p_finance_line_id in number
1487 ,p_cancelled_flag in out nocopy varchar2
1488 ,p_transfer_status in varchar2
1489 ,p_finance_header_id in number
1490 ,p_validate in boolean
1491 ,p_commit in boolean default FALSE
1492 ) is
1493 --
1494 v_proc varchar2(72) := g_package||'cancel_finance_line';
1495 l_ovn number;
1496 --
1497 begin
1498 hr_utility.set_location('Entering:'|| v_proc, 5);
1499 --
1500 cancel_finance_line(p_finance_line_id
1501 ,p_cancelled_flag
1502 ,p_transfer_status
1503 ,p_finance_header_id
1504 ,l_ovn
1505 ,p_validate
1506 ,p_commit);
1507 --
1508 hr_utility.set_location('Entering:'|| v_proc, 10);
1509 end cancel_finance_line;
1510 --
1511 --
1512 -- ----------------------------------------------------------------------------
1513 -- |------------------------< recancel_finance_line >-------------------------|
1514 -- ----------------------------------------------------------------------------
1515 --
1516 -- PUBLIC
1517 -- Description:
1518 --
1519 -- A procedure is required to set the attribute cancelled_flag to 'N'.
1520 --
1521 Procedure recancel_finance_line
1522 (
1523 p_finance_line_id in number
1524 ,p_cancelled_flag in out nocopy varchar2
1525 ,p_transfer_status in varchar2
1526 ,p_finance_header_id in number
1527 ,p_validate in boolean
1528 ,p_commit in boolean default FALSE
1529 ) is
1530 --
1531 v_proc varchar2(72) := g_package||'recancel_finance_line';
1532 v_header_transfer_status varchar2(30);
1533 v_rec_finance_line ota_finance_lines%rowtype;
1534 v_ovn number;
1535 --
1536 cursor sel_finance_line is
1537 select *
1538 from ota_finance_lines tfl
1539 where tfl.finance_line_id = p_finance_line_id;
1540 --
1541 cursor sel_finance_header is
1542 select tfh.transfer_status
1543 from ota_finance_headers tfh
1544 where tfh.finance_header_id = p_finance_header_id;
1545 --
1546 Begin
1547 --
1551 --
1548 hr_utility.set_location('Entering:'|| v_proc, 5);
1549 --
1550 If p_finance_line_id is null Then
1552 fnd_message.set_name('OTA','OTA_13361_TFL_RECANCEL_LINES');
1553 fnd_message.set_token('STEP','1');
1554 fnd_message.raise_error;
1555 --
1556 ElsIf p_cancelled_flag = 'N' Then
1557 --
1558 -- * Line is not cancelled
1559 --
1560 fnd_message.set_name('OTA','OTA_13361_TFL_RECANCEL_LINES');
1561 fnd_message.set_token('STEP','2');
1562 fnd_message.raise_error;
1563 --
1564 /*
1565 ElsIf p_transfer_status = 'ST' Then
1566 --
1567 -- * Line has been successful transferred
1568 --
1569 fnd_message.set_name('OTA','OTA_13361_TFL_RECANCEL_LINES');
1570 fnd_message.set_token('STEP','3');
1571 fnd_message.raise_error;
1572 --
1573 */
1574 Else
1575 --
1576 /*
1577 If p_finance_header_id is NOT null Then
1578 --
1579 Open sel_finance_header;
1580 Fetch sel_finance_header into v_header_transfer_status;
1581 --
1582 If sel_finance_header%notfound then
1583 --
1584 Close sel_finance_header;
1585 --
1586 fnd_message.set_name('OTA','OTA_13361_TFL_RECANCEL_LINES');
1587 fnd_message.set_token('STEP','4');
1588 fnd_message.raise_error;
1589 --
1590 ElsIf v_header_transfer_status = 'ST' Then
1591 --
1592 -- * Finance Header has been successfull transferred
1593 --
1594 fnd_message.set_name('OTA','OTA_13361_TFL_RECANCEL_LINES');
1595 fnd_message.set_token('STEP','5');
1596 fnd_message.raise_error;
1597 --
1598 End if;
1599 --
1600 End if;
1601 */
1602 --
1603 Open sel_finance_line;
1604 Fetch sel_finance_line into v_rec_finance_line;
1605 --
1606 If sel_finance_line%notfound then
1607 --
1608 Close sel_finance_line;
1609 --
1610 fnd_message.set_name('OTA','OTA_13361_TFL_RECANCEL_LINES');
1611 fnd_message.set_token('STEP','6');
1612 fnd_message.raise_error;
1613 --
1614 Else
1615 --
1616 Close sel_finance_line;
1617 --
1618 -- * Re-Cancel the Finance Line record
1619 --
1620 If p_validate = false Then
1621 --
1622 update_cancel_flag ( p_rec_cancel => v_rec_finance_line
1623 , p_new_cancelled_flag => 'N'
1624 ,p_transaction_type => 'REINSTATE_LINE'
1625 ,p_object_version_number => v_ovn);
1626 --
1627 p_cancelled_flag := 'N';
1628 --
1629 End if;
1630 --
1631 End if;
1632 --
1633 End if;
1634 --
1635 if p_commit then
1636 commit;
1637 end if;
1638 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1639 --
1640 End recancel_finance_line;
1641 --
1642 -- ----------------------------------------------------------------------------
1643 -- |------------------------< change_line_for_header >------------------------|
1644 -- ----------------------------------------------------------------------------
1645 --
1646 -- PUBLIC
1647 -- Description:
1648 -- A procedure is required to update all the finance lines for a header
1649 --
1650 -- This can be used for a number of types of update
1651 -- The ones used so far are
1652 --
1653 -- i. Update the transfer status of all the lines having the same transfer
1654 -- as the header
1655 --
1656 Procedure change_line_for_header
1657 (
1658 p_finance_header_id in number
1659 ,p_new_transfer_status in varchar2
1660 ,p_old_transfer_status in varchar2
1661 ,p_include_cancelled in varchar2 default 'N'
1662 ) is
1663 --
1664 v_proc varchar2(72) := g_package||'change_line_for_header';
1665 l_finance_line_id number;
1666 l_transfer_status varchar2(30);
1667 l_date_raised date;
1668 l_object_version_number number;
1669 l_sequence_number number;
1670 --
1671 cursor get_finance_line is
1672 select finance_line_id
1673 , date_raised
1674 , object_version_number
1675 , sequence_number
1676 , transfer_status
1677 from ota_finance_lines
1678 where finance_header_id = p_finance_header_id
1679 and ((p_include_cancelled = 'N'
1680 and cancelled_flag = 'N')
1681 or (p_include_cancelled <> 'N'));
1682 --
1683 Begin
1684 --
1685 hr_utility.set_location('Entering:'|| v_proc, 5);
1686 --
1687 open get_finance_line;
1688 fetch get_finance_line into l_finance_line_id
1689 , l_date_raised
1690 , l_object_version_number
1691 , l_sequence_number
1692 , l_transfer_status;
1693 --
1694 while get_finance_line%found loop
1695 if l_transfer_status = p_old_transfer_status then
1696 ota_tfl_api_upd.upd(p_finance_line_id => l_finance_line_id
1697 ,p_finance_header_id => p_finance_header_id
1698 ,p_date_raised => l_date_raised
1702 ,p_transaction_type => 'CHANGE_HEADER_LINE'
1699 ,p_object_version_number => l_object_version_number
1700 ,p_sequence_number => l_sequence_number
1701 ,p_transfer_status => p_new_transfer_status
1703 );
1704 end if;
1705 --
1706 fetch get_finance_line into l_finance_line_id
1707 , l_date_raised
1708 , l_object_version_number
1709 , l_sequence_number
1710 , l_transfer_status;
1711 end loop;
1712 --
1713 close get_finance_line;
1714 --
1715 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1716 --
1717 End change_line_for_header;
1718 --
1719 --
1720 -- ----------------------------------------------------------------------------
1721 end ota_tfl_api_business_rules2;