1 Package Body ota_tfl_api_business_rules as
2 /* $Header: ottfl02t.pkb 115.4 2002/11/29 09:24:27 arkashya ship $ */
3 --
4 --
5 -- Global package name
6 --
7 g_package varchar2(33) := ' ota_tfl_api_business_rules.';
8 g_standard varchar2(40) := 'STANDARD';
9 g_pre_payment varchar2(40) := 'PRE-PAYMENT';
10 g_pre_purchase_payment varchar2(40) := 'PRE-PURCHASE PAYMENT';
11 g_pre_purchase_use varchar2(40) := 'PRE-PURCHASE USE';
12 --
13 -- Global api dml status
14 --
15 g_api_dml boolean;
16 --
17 -- ----------------------------------------------------------------------------
18 -- |----------------------< check_valid_header>-------------------|
19 -- ----------------------------------------------------------------------------
20 --
21 -- Description:
22 -- Check finance_header exists, check type and receivable type of the header,
23 -- Check it is not a cancellation header.
24 --
25 Procedure check_valid_header
26 (
27 p_tfh_type in varchar2
28 ,p_tfh_receivable_type in varchar2
29 ) Is
30 -----------------
31 v_proc varchar2(72) := g_package||'check_valid_header';
32 -----------------
33 Begin
34 --
35 hr_utility.set_location('Entering:'|| v_proc, 5);
36 --
37 if p_tfh_type = 'C' then
38 --
39 -- Finance header of type Cancellation
40 --
41 fnd_message.set_name('OTA','OTA_13412_TFL_NO_HEADER');
42 fnd_message.raise_error;
43 --
44 elsIf p_tfh_type is null then
45 fnd_message.set_name('OTA','OTA_13412_TFL_NO_HEADER');
46 fnd_message.raise_error;
47 --
48 end if;
49 --
50 End check_valid_header;
51 --
52 -- ----------------------------------------------------------------------------
53 -- |-----------------------< check_line_type_domain >-------------------------|
54 -- ----------------------------------------------------------------------------
55 --
56 -- Description:
57 -- The attributee 'TYPE' must be in the domain of 'Finance Line Type'.
58 --
59 Procedure check_line_type_domain
60 (
61 p_type in varchar2
62 ) Is
63 --
64 v_proc varchar2(72) := g_package||'check_type';
65 --
66 Begin
67 --
68 hr_utility.set_location('Entering:'||v_proc, 5);
69 --
70 If p_type is not null Then
71 ota_general.check_domain_value( 'FINANCE_LINE_TYPE', p_type);
72 --
73 Else
74 fnd_message.set_name('OTA','OTA_13459_TFL_TYPE_NOT_FOUND');
75 fnd_message.raise_error;
76 End If;
77 --
78 hr_utility.set_location(' Leaving:'||v_proc, 10);
79 --
80 End check_line_type_domain;
81 --
82 -- ----------------------------------------------------------------------------
83 -- |--------------------< check_line_type >---------------------------|
84 -- ----------------------------------------------------------------------------
85 --
86 -- Description:
87 -- The type of header allowed to linked to a line must obey the
88 -- following rules:
89 -- Header of type cancelled - NO LINES ALLOWED
90 -- Header of type payable - Only vendor payment lines
91 -- Header of type receivable - Lines of type pre-purchase deal,
92 -- enrollment charge and resource
93 -- charge only.
94 -- Header of type cost transfer - Only vendor payment and
95 -- enrollment lines.
96 --
97 Procedure check_line_type
98 (
99 p_finance_line_type in varchar2
100 ,p_finance_header_type in varchar2
101 ,p_receivable_type in varchar2
102 ) is
103 -----------------
104 v_proc varchar2(72) := g_package||'check_line_type';
105 -----------------
106 Begin
107 --
108 hr_utility.set_location('Entering:'|| v_proc, 5);
109 --
110 check_valid_header(p_finance_header_type,p_receivable_type);
111 --
112 check_line_type_domain(p_finance_line_type);
113 --
114 If (p_finance_line_type not in ('E','R','P','V') or
115 p_finance_header_type <> 'CT') then
116 return;
117 end if;
118 --
119 If p_finance_header_type <> 'CT' then
120 If (p_finance_header_type = 'P' AND p_finance_line_type <> 'V'
121 )
122 OR
123 ( p_finance_line_type <> 'E' and
124 (p_receivable_type = g_standard OR
125 p_receivable_type = g_pre_payment OR
126 p_receivable_type = g_pre_purchase_use)
127 )
128 OR
129 ( p_receivable_type=g_pre_purchase_payment and
130 p_finance_line_type <> 'P'
131 )
132 Then
133 --
134 fnd_message.set_name('OTA','OTA_13460_TFL_WRONG_LINE_TYPE');
135 fnd_message.raise_error;
136 End if;
137 End if;
138 --
139 hr_utility.set_location(' Leaving:'||v_proc, 10);
140 --
141 end check_line_type;
142 --
143 -- ----------------------------------------------------------------------------
144 -- |-----------------------< check_transfer_status >--------------------------|
145 -- ----------------------------------------------------------------------------
146 --
147 -- PUBLIC
148 -- Description:
149 -- The attribute 'Transfer Status' must be in the domain of
150 -- 'GL Transfer Status'.
151 --
152 Procedure check_transfer_status
153 (
154 p_transfer_status in varchar2
155 ) is
156 --
157 v_proc varchar2(72) := g_package||'check_transfer_status';
158 --
159 Begin
160 --
161 hr_utility.set_location('Entering:'|| v_proc, 5);
162 --
163 If p_transfer_status is not null Then
164 --
165 ota_general.check_domain_value( 'GL_TRANSFER_STATUS', p_transfer_status);
166 --
167 Else
168 ota_tfl_api_shd.constraint_error( 'OTA_TFL_TRANSFER_STATUS_CHK');
169 --
170 End If;
171 --
172 hr_utility.set_location(' Leaving:'|| v_proc, 10);
173 --
174 End check_transfer_status;
175 --
176 -- ----------------------------------------------------------------------------
177 -- |--------------------< check_booking_deal_type > -----------------------|
178 -- ----------------------------------------------------------------------------
179 --
180 procedure check_booking_deal_type(
181 p_booking_deal_type in varchar2
182 ,p_receivable_type in varchar2)
183 is
184 ---------------
185 v_proc varchar2(72) := g_package||'check_booking_deal_type';
186 ---------------
187 Begin
188 --
189 hr_utility.set_location('Entering:'|| v_proc, 5);
190 --
191 if p_receivable_type in (g_pre_purchase_payment,g_pre_purchase_use)
192 and p_booking_deal_type <> 'P' then
193 fnd_message.set_name('OTA','OTA_13456_TFL_WRONG_DEAL_TYPE');
194 fnd_message.raise_error;
195 --
196 elsif p_receivable_type in (g_standard,g_pre_payment)
197 and p_booking_deal_type <> 'D' then
198 fnd_message.set_name('OTA','OTA_13456_TFL_WRONG_DEAL_TYPE');
199 fnd_message.raise_error;
200 --
201 end if;
202 --
203 hr_utility.set_location(' Leaving:'|| v_proc, 10);
204 --
205 end check_booking_deal_type;
206 --
207 -- ----------------------------------------------------------------------------
208 -- |--------------------< check_booking_deal>-----------------------|
209 -- ----------------------------------------------------------------------------
210 --
211 -- Description:
212 -- This procedure check that:
213 -- - the type of the booking deal is correct.
214 -- This procedure replaces former procedures check_deal_customer and
215 -- check_valid_discount_deal
216 --
217 procedure check_booking_deal (
218 p_finance_header_id in number
219 ,p_booking_deal_id in number
220 ) is
221 ---------------
222 v_proc varchar2(72) := g_package||'check_booking_deal';
223 v_book_deal_customer_id OTA_BOOKING_DEALS.CUSTOMER_ID%type;
224 v_finance_customer_id OTA_FINANCE_HEADERS.CUSTOMER_ID%type;
225 v_receivable_type OTA_FINANCE_HEADERS.RECEIVABLE_TYPE%type;
226 v_deal_type OTA_BOOKING_DEALS.TYPE%type;
227 v_number_of_places number;
228 v_limit_each_event_flag varchar2(1);
229 --
230 cursor csr_tfh is
231 select customer_id,receivable_type
232 from ota_finance_headers
233 where finance_header_id = p_finance_header_id;
234 --
235 cursor csr_tbd is
236 select customer_id,type,number_of_places,limit_each_event_flag
237 from ota_booking_deals
238 where booking_deal_id = p_booking_deal_id;
239 ---------------
240 Begin
241 --
242 hr_utility.set_location('Entering:'|| v_proc, 5);
243 --
244 Open csr_tbd;
245 Fetch csr_tbd into v_book_deal_customer_id
246 ,v_deal_type
247 ,v_number_of_places
248 ,v_limit_each_event_flag;
249 Close csr_tbd;
250 --
251 If v_deal_type is null then
252 fnd_message.set_name('OTA','OTA_13458_TFL_DEAL_NOT_FOUND');
253 fnd_message.raise_error;
254 end if;
255 --
256 If v_book_deal_customer_id is NOT null Then
257 Open csr_tfh;
258 Fetch csr_tfh into v_finance_customer_id,v_receivable_type;
259 Close csr_tfh;
260 End if;
261 --
262 check_booking_deal_type(v_deal_type,v_receivable_type);
263 --
264 hr_utility.set_location(' Leaving:'|| v_proc, 10);
265 --
266 end check_booking_deal;
267 --
268 -- ----------------------------------------------------------------------------
269 -- |-----------------------------< check_vendor >-----------------------------|
270 -- ----------------------------------------------------------------------------
271 --
272 -- PUBLIC
273 -- Description:
274 -- The vendor defined on the resource booking must be the same vendor
275 -- as entered on the header.
276 --
277 Procedure check_vendor
278 (
279 p_finance_header_id in number
280 ,p_resource_booking_id in number
281 ) is
282 --
283 v_resource_vendor_id number;
284 v_finance_vendor_id number;
285 v_proc varchar2(72) := g_package||'check_vendor';
286 --
287 cursor sel_resource_vendor is
288 select tsr.vendor_id
289 from ota_resource_bookings trb
290 , ota_suppliable_resources tsr
291 where trb.resource_booking_id = p_resource_booking_id
292 and tsr.supplied_resource_id = trb.supplied_resource_id;
293 --
294 cursor sel_finance_vendor is
295 select tfh.vendor_id
296 from ota_finance_headers tfh
297 where tfh.finance_header_id = p_finance_header_id;
298 --
299 Begin
300 --
301 hr_utility.set_location('Entering:'|| v_proc, 5);
302 --
303 If p_resource_booking_id is NOT null AND
304 p_finance_header_id is NOT null Then
305 --
306 Open sel_resource_vendor;
307 Fetch sel_resource_vendor into v_resource_vendor_id;
308 --
309 If sel_resource_vendor%notfound Then
310 --
311 Close sel_resource_vendor;
312 --
313 fnd_message.set_name('OTA','OTA_13345_TFL_VENDOR');
314 fnd_message.raise_error;
315 --
316 Else
317 --
318 Close sel_resource_vendor;
319 Open sel_finance_vendor;
320 Fetch sel_finance_vendor into v_finance_vendor_id;
321 --
322 If sel_finance_vendor%notfound Then
323 --
324 Close sel_finance_vendor;
325 --
326 fnd_message.set_name('OTA','OTA_13345_TFL_VENDOR');
327 fnd_message.raise_error;
328 --
329 Else
330 --
331 If v_resource_vendor_id <> v_finance_vendor_id OR
332 v_finance_vendor_id is null Then
333 --
334 Close sel_finance_vendor;
335 --
336 fnd_message.set_name('OTA','OTA_13345_TFL_VENDOR');
337 fnd_message.raise_error;
338 --
339 End if;
340 --
341 End if;
342 --
343 Close sel_finance_vendor;
344 --
345 End if;
346 --
347 End if;
348 --
349 hr_utility.set_location(' Leaving:'|| v_proc, 10);
350 --
351 End check_vendor;
352 --
353 -- ----------------------------------------------------------------------------
354 -- |-----------------------< check_resource_charge >--------------------------|
355 -- ----------------------------------------------------------------------------
356 --
357 -- Description:
358 -- Only resource allocations for delegate bookings are allowed to be
359 -- referenced. The resource allocation must be for a delegate booking for the
360 -- customer defined on the header, or for a delegate booking with a
361 -- booking_source of 'R' for reseller. Or the header_id is null.
362 --
363 Procedure check_resource_charge
364 (
365 p_finance_header_id in number
366 ,p_resource_allocation_id in number
367 ) is
368 --
369 v_finance_customer_id number;
370 v_booking_customer_id number;
371 v_source_of_booking varchar(30);
372 v_proc varchar2(72) := g_package||'check_resource_charge';
373 --
374 cursor sel_booking_customer is
375 select tdb.customer_id
376 , tdb.source_of_booking
377 from ota_resource_allocations tra
378 , ota_delegate_bookings tdb
379 where tra.resource_allocation_id = p_resource_allocation_id
380 and tdb.booking_id = tra.booking_id;
381 --
382 cursor sel_finance_customer is
383 select tfh.customer_id
384 from ota_finance_headers tfh
385 where tfh.finance_header_id = p_finance_header_id;
386 --
387 Begin
388 --
389 hr_utility.set_location('Entering:'|| v_proc, 5);
390 --
391 if p_finance_header_id is not null and p_resource_allocation_id is not null
392 then
393 --
394 Open sel_booking_customer;
395 Fetch sel_booking_customer into v_booking_customer_id
396 , v_source_of_booking;
397 --
398 If sel_booking_customer%notfound then
399 --
400 Close sel_booking_customer;
401 --
402 fnd_message.set_name('OTA','OTA_13347_TFL_RES_CHARGE');
403 fnd_message.raise_error;
404 --
405 Else
406 --
407 Close sel_booking_customer;
408 Open sel_finance_customer;
409 Fetch sel_finance_customer into v_finance_customer_id;
410 --
411 If sel_finance_customer%notfound then
412 --
413 Close sel_finance_customer;
414 --
415 fnd_message.set_name('OTA','OTA_13347_TFL_RES_CHARGE');
416 fnd_message.raise_error;
417 --
418 Else
419 --
420 Close sel_finance_customer;
421 --
422 -- Resource of booking is NOT a reseller
423 --
424 If v_source_of_booking <> 'R' OR
425 v_source_of_booking is null Then
426 --
427 If v_finance_customer_id <> v_booking_customer_id OR
428 ( v_finance_customer_id is null AND
429 v_booking_customer_id is NOT null ) OR
430 ( v_booking_customer_id is null AND
431 v_finance_customer_id is NOT null ) Then
432 --
433 fnd_message.set_name('OTA','OTA_13347_TFL_RES_CHARGE');
437 --
434 fnd_message.raise_error;
435 --
436 End if;
438 End if;
439 --
440 End if;
441 --
442 End if;
443 --
444 End if;
445 --
446 hr_utility.set_location(' Leaving:'|| v_proc, 10);
447 --
448 End check_resource_charge;
449 --
450 -- ----------------------------------------------------------------------------
451 -- |----------------------< check_valid_delegate >----------------------------|
452 -- ----------------------------------------------------------------------------
453 --
454 -- PUBLIC
455 -- Description:
456 -- The delegate booking must be for the customer defined on the header
457 -- or the header_id is null, or the delegate booking has the booking_source
458 -- set to 'R' for reseller.
459 --
460 Procedure check_valid_delegate
461 (
462 p_finance_header_id in number
463 ,p_booking_id in number
464 ) is
465 --
466 v_booking_customer_id number;
467 v_source_of_booking varchar(30);
468 v_finance_customer_id number;
469 v_proc varchar2(72) := g_package||'check_valid_delegate';
470 --
471 cursor sel_booking_customer is
472 select tdb.customer_id
473 , tdb.source_of_booking
474 from ota_delegate_bookings tdb
475 where tdb.booking_id = p_booking_id;
476 --
477 cursor sel_finance_customer is
478 select tfh.customer_id
479 from ota_finance_headers tfh
480 where tfh.finance_header_id = p_finance_header_id;
481 --
482 Begin
483 --
484 hr_utility.set_location('Entering:'|| v_proc, 5);
485 null;
486 /**********************
487 This procedure has been omitted until further clarification is received
488 --
489 If p_booking_id is NOT null AND
490 p_finance_header_id is NOT null Then
491 --
492 Open sel_booking_customer;
493 Fetch sel_booking_customer into v_booking_customer_id
494 , v_source_of_booking;
495 --
496 If sel_booking_customer%notfound then
497 --
498 Close sel_booking_customer;
499 --
500 fnd_message.set_name('OTA','OTA_13346_TFL_DELEGATE');
501 fnd_message.raise_error;
502 --
503 Else
504 --
505 Close sel_booking_customer;
506 Open sel_finance_customer;
507 Fetch sel_finance_customer into v_finance_customer_id;
508 --
509 If sel_finance_customer%notfound then
510 --
511 Close sel_finance_customer;
512 --
513 fnd_message.set_name('OTA','OTA_13346_TFL_DELEGATE');
514 fnd_message.raise_error;
515 --
516 Else
517 --
518 Close sel_finance_customer;
519 --
520 -- Resource of booking is NOT a reseller
521 --
522 If v_source_of_booking <> 'R' OR
523 v_source_of_booking is null Then
524
525 --
526 If v_booking_customer_id <> v_finance_customer_id OR
527 ( v_finance_customer_id is null AND
528 v_booking_customer_id is NOT null ) OR
529 ( v_booking_customer_id is null AND
530 v_finance_customer_id is NOT null ) Then
531 --
532 fnd_message.set_name('OTA','OTA_13346_TFL_DELEGATE');
533 fnd_message.raise_error;
534 --
535 End if;
536 --
537 End if;
538 --
539 End if;
540 --
541 End if;
542 --
543 End if;
544 ****************************/
545 end check_valid_delegate;
546 --
547 -- ----------------------------------------------------------------------------
548 -- |-------------------< check_pre_purchase_units >------------------------|
549 -- ----------------------------------------------------------------------------
550 --
551 -- PUBLIC
552 -- Description:
553 -- If the enrollment charge or pre-purchase charge is linked to a
554 -- pre-purchase deal then if the price list is in training units then
555 -- the units used must be entered, otherwise it must be null
556 --
557 Procedure check_pre_purchase_units
558 (
559 p_finance_line_type in varchar2
560 ,p_standard_amount in number
561 ,p_money_amount in number
562 ,p_unitary_amount in number
563 ,p_booking_deal_id in number
564 ) is
565 --
566 v_price_list_type varchar2(30);
567 v_book_deal_type varchar2(30);
568 v_proc varchar2(72) := g_package||'check_pre_purchase_units';
569 --
570 cursor sel_pricelist_type is
571 select tbd.type
572 , tpl.price_list_type
573 from ota_booking_deals tbd
574 , ota_price_lists tpl
575 where tbd.booking_deal_id = p_booking_deal_id
576 and tpl.price_list_id (+) = tbd.price_list_id;
577 --
578 Begin
579 --
580 hr_utility.set_location('Entering:'|| v_proc, 5);
581 --
585 --
582 Open sel_pricelist_type;
583 Fetch sel_pricelist_type into v_book_deal_type
584 , v_price_list_type;
586 If sel_pricelist_type%notfound then
587 --
588 Close sel_pricelist_type;
589 --
590 fnd_message.set_name('OTA','OTA_13351_TFL_PRE_PURCH_UNIT');
591 fnd_message.raise_error;
592 --
593 Else
594 --
595 Close sel_pricelist_type;
596 --
597 If v_book_deal_type = 'P' Then
598 --
599 -- It's a Pre-purchase deal
600 --
601 -- If the Price List used on the booking deal is in units then
602 -- the number of units used must be recorded otherwise it
603 -- must be null
604 --
605 If (v_price_list_type = 'T' and
606 p_unitary_amount is null
607 OR (v_price_list_type = 'M' and
608 p_unitary_amount is NOT null)) Then
609 --
610 fnd_message.set_name('OTA','OTA_13351_TFL_PRE_PURCH_UNIT');
611 fnd_message.raise_error;
612 --
613 End if;
614 --
615 End if;
616 --
617 End if;
618 --
619 hr_utility.set_location(' Leaving:'|| v_proc, 10);
620 --
621 End check_pre_purchase_units;
622 --
623 -- ----------------------------------------------------------------------------
624 -- |--------------------< check_tfl_foreign_keys >---------------------------|
625 -- ----------------------------------------------------------------------------
626 --
627 procedure check_tfl_foreign_keys (
628 p_finance_header_id in varchar2
629 ,p_finance_line_type in varchar2
630 ,p_receivable_type in varchar2
631 ,p_booking_id in number
632 ,p_booking_deal_id in number
633 ,p_resource_booking_id in number
634 ,p_resource_allocation_id in number
635 ) is
636 --
637 Cursor csr_type IS
638 select type
639 from OTA_FINANCE_HEADERS
640 where finance_header_id = p_finance_header_id;
641 ---------------------
642 v_type varchar2(30);
643 begin
644 --
645 if p_finance_header_id is not null then
646 open csr_type;
647 fetch csr_type into v_type;
648 close csr_type;
649 end if;
650 if p_finance_line_type = 'E' then
651 if p_booking_id is null
652 or p_resource_booking_id is not null
653 or p_resource_allocation_id is not null
654 then
655 fnd_message.set_name('OTA','OTA_13350_TFL_ENROLLMENT_ATTR');
656 fnd_message.raise_error;
657 end if;
658 if p_receivable_type = g_pre_purchase_use
659 and p_booking_deal_id is null then
660 fnd_message.set_name('OTA','OTA_13350_TFL_ENROLLMENT_ATTR');
661 fnd_message.raise_error;
662 end if;
663
664 --
665 elsif p_finance_line_type = 'V' then
666 if p_resource_booking_id is null
667 or p_booking_id is not null
668 or p_booking_deal_id is not null
669 or p_resource_allocation_id is not null
670 then
671 fnd_message.set_name('OTA','OTA_13353_TFL_VENDOR_PAY_ATTR');
672 fnd_message.raise_error;
673 end if;
674 --
675 elsif p_finance_line_type = 'P' then
676 if p_booking_deal_id is null
677 or p_booking_id is not null
678 or p_resource_booking_id is not null
679 or p_resource_allocation_id is not null
680 then
681 fnd_message.set_name('OTA','OTA_13349_TFL_PRE_PURCH_ATTR');
682 fnd_message.raise_error;
683 end if;
684 --
685 elsif p_finance_line_type = 'R' then
686 fnd_message.set_name('OTA','OTA_13352_TFL_RES_CHARGE_ATTR');
687 fnd_message.raise_error;
688 --
689 else
690 if p_booking_deal_id is not null
691 or p_booking_id is not null
692 or p_resource_booking_id is not null
693 or p_resource_allocation_id is not null then
694 fnd_message.set_name('OTA','OTA_13590_USER_FIN_TYPE_KEY');
695 fnd_message.raise_error;
696 end if;
697 end if;
698 --
699 --
700 if (p_resource_booking_id is not null and
701 v_type <> 'CT') then
702 check_vendor(p_finance_header_id,p_resource_booking_id);
703 end if;
704 --
705 if p_booking_deal_id is not null then
706 check_booking_deal(p_finance_header_id,p_booking_deal_id);
707 ota_tfl_api_business_rules3.check_customer_booking_deal
708 (p_finance_header_id,p_booking_deal_id);
709 end if;
710 --
711 if p_resource_allocation_id is not null then
712 check_resource_charge(p_finance_header_id,p_resource_allocation_id);
713 end if;
714 --
715 end check_tfl_foreign_keys;
716 --
717 -- ----------------------------------------------------------------------------
718 -- |---------------------< check_type_and_amounts >----------------------|
719 -- ----------------------------------------------------------------------------
720 --
721 -- Description:
722 --
723 Procedure check_type_and_amounts
724 (
728 ,p_unitary_amount in number
725 p_finance_line_type in varchar2
726 ,p_standard_amount in number
727 ,p_money_amount in number
729 ,p_booking_deal_id in number
730 ,p_finance_header_id in number
731 ) is
732 --
733 CURSOR csr_header_type IS
734 SELECT type
735 FROM ota_finance_headers
736 WHERE finance_header_id = p_finance_header_id;
737 --
738 v_proc varchar2(72) := g_package||'check_enrollment_attributes';
739 --
740 v_finance_header_type varchar2(30);
741 Begin
742 --
743 hr_utility.set_location('Entering:'|| v_proc, 5);
744 --
745 if p_finance_header_id is not null then
746 open csr_header_type;
747 fetch csr_header_type into v_finance_header_type;
748 close csr_header_type;
749 end if;
750 --
751 If p_finance_line_type in ('E','R') and p_standard_amount is null Then
752 fnd_message.set_name('OTA','OTA_13454_TFL_ST_AMNT_REQ');
753 fnd_message.raise_error;
754 elsif p_finance_line_type in ('V','P') and p_standard_amount is not null then
755 fnd_message.set_name('OTA','OTA_13464_TFL_ST_AMNT_NULL');
756 fnd_message.raise_error;
757 end if;
758 --
759 if p_money_amount is null then
760 fnd_message.set_name('OTA','OTA_13455_TFL_MONEY_AMNT_REQ');
761 fnd_message.raise_error;
762 end if;
763 --
764 if p_finance_line_type in ('R','V') and p_unitary_amount is not null then
765 fnd_message.set_name('OTA','OTA_13465_UNIT_AMNT_NULL');
766 fnd_message.raise_error;
767 end if;
768 --
769 if p_finance_line_type in ('E','P') and p_booking_deal_id is not null then
770 check_pre_purchase_units(p_finance_line_type
771 ,p_standard_amount
772 ,p_money_amount
773 ,p_unitary_amount
774 ,p_booking_deal_id
775 );
776 end if;
777 --
778 if p_finance_line_type is not null and
779 v_finance_header_type <> 'CT' and
780 p_finance_line_type not in ('E','P','R','V') and
781 ( p_unitary_amount is not null
782 or p_standard_amount is not null
783 or p_money_amount is null) then
784 fnd_message.set_name('OTA','OTA_13589_USER_FIN_TYPE_ERROR');
785 fnd_message.raise_error;
786 end if;
787
788 hr_utility.set_location('Entering:'|| v_proc, 5);
789 --
790 end check_type_and_amounts;
791 --
792 -- ----------------------------------------------------------------------------
793 -- |----------------------< check_update_attributes >-------------------------|
794 -- ----------------------------------------------------------------------------
795 --
796 -- PUBLIC
797 -- Description:
798 -- If the line has been transferred then no attributes may be updated.
799 --
800 Procedure check_update_attributes
801 (
802 p_transfer_status in varchar2
803 ) is
804 --
805 v_proc varchar2(72) := g_package||'check_update_attributes';
806 --
807 Begin
808 --
809 hr_utility.set_location('Entering:'|| v_proc, 5);
810 --
811 If p_transfer_status = 'ST' Then
812 --
813 fnd_message.set_name('OTA','OTA_13355_TFL_UPDATE');
814 fnd_message.raise_error;
815 --
816 End if;
817 --
818 hr_utility.set_location(' Leaving:'|| v_proc, 10);
819 --
820 End check_update_attributes;
821 --
822 -- ----------------------------------------------------------------------------
823 -- |-------------------------< check_cancelled_flag >-------------------------|
824 -- ----------------------------------------------------------------------------
825 --
826 -- PUBLIC
827 -- Description:
828 -- The attribute CANCELLED_FLAG must be in the doamin 'Yes No'
829 --
830 Procedure check_cancelled_flag
831 (
832 p_cancelled_flag in varchar2
833 ) is
834 --
835 v_proc varchar2(72) := g_package||'check_cancelled_flag';
836 --
837 Begin
838 --
839 hr_utility.set_location('Entering:'|| v_proc, 5);
840 --
841 ota_general.check_domain_value('YES_NO',p_cancelled_flag);
842 --
843 hr_utility.set_location(' Leaving:'|| v_proc, 10);
844 --
845 End check_cancelled_flag;
846 --
847 -- ----------------------------------------------------------------------------
848 -- |------------------------< check_sequence_number >-------------------------|
849 -- ----------------------------------------------------------------------------
850 --
851 -- PUBLIC
852 -- Description:
853 -- The sequence_number for a finance line within a finance_header_id
854 -- MUST be unique.
855 --
856 Procedure check_sequence_number
857 (
858 p_finance_header_id in number
859 ,p_sequence_number in number
860 ) is
861 --
862 v_exists varchar2(1);
863 v_proc varchar2(72) := g_package||'check_sequence_number';
864 --
865 cursor sel_sequence_number is
866 select 'Y'
870 --
867 from ota_finance_lines tfl
868 where tfl.finance_header_id = p_finance_header_id
869 and tfl.sequence_number = p_sequence_number;
871 Begin
872 --
873 hr_utility.set_location('Entering:'|| v_proc, 5);
874 --
875 If p_finance_header_id is not null Then
876 --
877 If p_sequence_number <= 0 OR
878 p_sequence_number is NULL Then
879 --
880 fnd_message.set_name('OTA','OTA_13364_TFL_SEQUENCE');
881 fnd_message.raise_error;
882 --
883 Else
884 --
885 Open sel_sequence_number;
886 Fetch sel_sequence_number into v_exists;
887 --
888 If sel_sequence_number%found then
889 --
890 Close sel_sequence_number;
891 --
892 fnd_message.set_name('OTA','OTA_13364_TFL_SEQUENCE');
893 fnd_message.raise_error;
894 --
895 End if;
896 --
897 Close sel_sequence_number;
898 --
899 End if;
900 --
901 End if;
902 --
903 hr_utility.set_location(' Leaving:'|| v_proc, 10);
904 --
905 End check_sequence_number;
906 --
907 -- ----------------------------------------------------------------------------
908 -- |-----------------------< get_next_sequence_number >-----------------------|
909 -- ----------------------------------------------------------------------------
910 --
911 -- PUBLIC
912 -- Description:
913 -- Fetch the next valid sequence number for a finance line.
914 --
915 Procedure get_next_sequence_number
916 (
917 p_finance_header_id in number
918 ,p_sequence_number in out nocopy number
919 ) is
920 --
921 v_last_sequence_number number( 11);
922 v_proc varchar2(72) := g_package||'get_next_sequence_number';
923 --
924 cursor get_sequence_number is
925 select nvl( max( tfl.sequence_number), 0)
926 from ota_finance_lines tfl
927 where tfl.finance_header_id = p_finance_header_id;
928 --
929 Begin
930 --
931 hr_utility.set_location('Entering:'|| v_proc, 5);
932 --
933 If p_finance_header_id is not null Then
934 --
935 Open get_sequence_number;
936 Fetch get_sequence_number into v_last_sequence_number;
937 --
938 If get_sequence_number%notfound then
939 --
940 Close get_sequence_number;
941 --
942 p_sequence_number := 10;
943 --
944 Else
945 --
946 Close get_sequence_number;
947 --
948 p_sequence_number := v_last_sequence_number + 10;
949 --
950 End if;
951 --
952 Else
953 --
954 p_sequence_number := 10;
955 --
956 End if;
957 --
958 hr_utility.set_location(' Leaving:'|| v_proc, 10);
959 --
960 End get_next_sequence_number;
961 --
962 -- ----------------------------------------------------------------------------
963 -- |--------------------------< get_date_raised >---------------------------|
964 -- ----------------------------------------------------------------------------
965 --
966 -- PUBLIC
967 -- Description:
968 -- Get a valid date_raised for a finance line.
969 --
970 Procedure get_date_raised
971 (
972 p_finance_header_id in number
973 ,p_date_raised in out nocopy date
974 ) is
975 --
976 v_date_raised date;
977 v_proc varchar2(72) := g_package||'get_date_raised';
978 --
979 cursor sel_date_raised is
980 select tfh.date_raised
981 from ota_finance_headers tfh
982 where tfh.finance_header_id = p_finance_header_id;
983 --
984 Begin
985 --
986 hr_utility.set_location('Entering:'|| v_proc, 5);
987 if p_date_raised is null then
988 --
989 If p_finance_header_id is not null Then
990 --
991 Open sel_date_raised;
992 Fetch sel_date_raised into v_date_raised;
993 --
994 If sel_date_raised%notfound then
995 --
996 Close sel_date_raised;
997 --
998 fnd_message.set_name('OTA','OTA_13365_TFL_DATE_RAISED');
999 fnd_message.raise_error;
1000 --
1001 End if;
1002 --
1003 Close sel_date_raised;
1004 --
1005 p_date_raised := v_date_raised;
1006 --
1007 Else
1008 --
1009 p_date_raised := sysdate;
1010 --
1011 End if;
1012 end if;
1013 --
1014 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1015 --
1016 End get_date_raised;
1017 --
1018 -- ----------------------------------------------------------------------------
1019 -- |-----------------------< check_delete_attempt >---------------------------|
1020 -- ----------------------------------------------------------------------------
1021 --
1022 -- PUBLIC
1023 -- Description:
1024 -- No finance lines may be deleted.
1025 --
1026 Procedure check_delete_attempt
1027 (
1028 p_finance_header_id in number
1029 ,p_finance_line_id in number
1033 --
1030 ) is
1031 --
1032 v_proc varchar2(72) := g_package||'check_delete_attempt';
1034 Begin
1035 --
1036 hr_utility.set_location('Entering:'|| v_proc, 5);
1037 --
1038 fnd_message.set_name('OTA','OTA_13357_TFL_DELETE');
1039 fnd_message.raise_error;
1040 --
1041 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1042 --
1043 End check_delete_attempt;
1044 --
1045 -- ----------------------------------------------------------------------------
1046 -- |------------------------< check_currency_code >---------------------------|
1047 -- ----------------------------------------------------------------------------
1048 --
1049 -- PUBLIC
1050 -- Description:
1051 --
1052 Procedure check_currency_code
1053 (
1054 p_finance_line_type in varchar2
1055 ,p_finance_header_id in number
1056 ,p_booking_id in number
1057 ,p_booking_deal_id in number
1058 ,p_resource_allocation_id in number
1059 ,p_resource_booking_id in number
1060 ) is
1061 --
1062 v_header_currency_code ota_events.currency_code%type;
1063 v_line_currency_code ota_events.currency_code%type;
1064 v_type ota_finance_headers.type%type;
1065 v_proc varchar2(72) := g_package||'check_currency_code';
1066 --
1067 cursor sel_finance_currency is
1068 select tfh.currency_code,tfh.type
1069 from ota_finance_headers tfh
1070 where tfh.finance_header_id = p_finance_header_id;
1071 --
1072 cursor sel_event_currency is
1073 select evt.currency_code
1074 from ota_delegate_bookings tdb
1075 , ota_events evt
1076 where tdb.booking_id = p_booking_id
1077 and evt.event_id = tdb.event_id;
1078 --
1079 cursor sel_price_list_currency is
1080 select tpl.currency_code
1081 from ota_booking_deals tbd
1082 , ota_price_lists tpl
1083 where tbd.booking_deal_id = p_booking_deal_id
1084 and tpl.price_list_id = tbd.price_list_id;
1085 --
1086 cursor sel_resource_alloc_currency is
1087 select evt.currency_code
1088 from ota_resource_allocations tra
1089 , ota_delegate_bookings tdb
1090 , ota_events evt
1091 where tra.resource_allocation_id = p_resource_allocation_id
1092 and tdb.booking_id = tra.booking_id
1093 and evt.event_id = tdb.event_id;
1094 --
1095 cursor sel_resource_book_currency is
1096 select tsr.currency_code
1097 from ota_suppliable_resources tsr,
1098 ota_resource_bookings trb
1099 where tsr.supplied_resource_id = trb.supplied_resource_id
1100 and trb.resource_booking_id = p_resource_booking_id;
1101
1102 /* select decode(evt.event_id,'',v_header_currency_code
1103 ,evt.currency_code)
1104 from ota_resource_bookings trb
1105 , ota_events evt
1106 where trb.resource_booking_id = p_resource_booking_id
1107 and evt.event_id(+) = trb.event_id;*/
1108 --
1109 Begin
1110 --
1111 hr_utility.set_location('Entering:'|| v_proc, 5);
1112 --
1113 Open sel_finance_currency;
1114 Fetch sel_finance_currency into v_header_currency_code,v_type;
1115 --
1116 If sel_finance_currency%notfound then
1117 --
1118 Close sel_finance_currency;
1119 --
1120 fnd_message.set_name('OTA','OTA_13297_TFL_NO_CURRENCY');
1121 fnd_message.raise_error;
1122 --
1123 End if;
1124 --
1125 Close sel_finance_currency;
1126 --
1127 If v_type <> 'CT' Then
1128 If p_finance_line_type = 'V' Then
1129 --
1130 -- * TYPE Vendor Payment
1131 --
1132 Open sel_resource_book_currency;
1133 Fetch sel_resource_book_currency into v_line_currency_code;
1134 --
1135 If sel_resource_book_currency%notfound OR
1136 v_line_currency_code <> v_header_currency_code OR
1137 v_line_currency_code is null Then
1138 --
1139 Close sel_resource_book_currency;
1140 --
1141 fnd_message.set_name('OTA','OTA_13358_TFL_CURRENCY');
1142 fnd_message.raise_error;
1143 --
1144 End if;
1145 --
1146 Close sel_resource_book_currency;
1147 --
1148 --
1149 --
1150 ElsIf p_finance_line_type = 'E' Then
1151 --
1152 -- TYPE Enrollment Charge
1153 --
1154 Open sel_event_currency;
1155 Fetch sel_event_currency into v_line_currency_code;
1156 --
1157 If sel_event_currency%notfound OR
1158 v_line_currency_code <> v_header_currency_code OR
1159 v_line_currency_code is null Then
1160 --
1161 Close sel_event_currency;
1162 --
1163 fnd_message.set_name('OTA','OTA_13358_TFL_CURRENCY');
1164 fnd_message.raise_error;
1165 --
1166 End if;
1167 --
1168 Close sel_event_currency;
1169 --
1173 --
1170 ElsIf p_finance_line_type = 'P' Then
1171 --
1172 -- TYPE Pre-purchase deal
1174 Open sel_price_list_currency;
1175 Fetch sel_price_list_currency into v_line_currency_code;
1176 --
1177 If sel_price_list_currency%notfound OR
1178 v_line_currency_code <> v_header_currency_code OR
1179 v_line_currency_code is null Then
1180 --
1181 Close sel_price_list_currency;
1182 --
1183 fnd_message.set_name('OTA','OTA_13358_TFL_CURRENCY');
1184 fnd_message.raise_error;
1185 --
1186 End if;
1187 --
1188 Close sel_price_list_currency;
1189 --
1190 --
1191 --
1192 ElsIf p_finance_line_type = 'R' Then
1193 --
1194 -- TYPE Resource Charge
1195 --
1196 Open sel_resource_alloc_currency;
1197 Fetch sel_resource_alloc_currency into v_line_currency_code;
1198 --
1199 If sel_resource_alloc_currency%notfound OR
1200 v_line_currency_code <> v_header_currency_code OR
1201 v_line_currency_code is null Then
1202 --
1203 Close sel_resource_alloc_currency;
1204 --
1205 fnd_message.set_name('OTA','OTA_13358_TFL_CURRENCY');
1206 fnd_message.raise_error;
1207 --
1208 End if;
1209 --
1210 Close sel_resource_alloc_currency;
1211 --
1212 End if;
1213 --
1214 End if;
1215 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1216 --
1217 End check_currency_code;
1218 --
1219 -- ----------------------------------------------------------------------------
1220 -- |--------------------------< get_currency_code >---------------------------|
1221 -- ----------------------------------------------------------------------------
1222 --
1223 -- PUBLIC
1224 -- Description:
1225 --
1226 Procedure get_currency_code
1227 (
1228 p_finance_line_type in varchar2
1229 ,p_booking_id in number
1230 ,p_booking_deal_id in number
1231 ,p_resource_allocation_id in number
1232 ,p_resource_booking_id in number
1233 ,p_currency_code out nocopy varchar2
1234 ) is
1235 --
1236 v_line_currency_code ota_events.currency_code%type;
1237 v_proc varchar2(72) := g_package||'get_currency_code';
1238 --
1239 cursor sel_event_currency is
1240 select evt.currency_code
1241 from ota_delegate_bookings tdb
1242 , ota_events evt
1243 where tdb.booking_id = p_booking_id
1244 and evt.event_id = tdb.event_id;
1245 --
1246 cursor sel_price_list_currency is
1247 select tpl.currency_code
1248 from ota_booking_deals tbd
1249 , ota_price_lists tpl
1250 where tbd.booking_deal_id = p_booking_deal_id
1251 and tpl.price_list_id = tbd.price_list_id;
1252 --
1253 cursor sel_resource_alloc_currency is
1254 select evt.currency_code
1255 from ota_resource_allocations tra
1256 , ota_delegate_bookings tdb
1257 , ota_events evt
1258 where tra.resource_allocation_id = p_resource_allocation_id
1259 and tdb.booking_id = tra.booking_id
1260 and evt.event_id = tdb.event_id;
1261 --
1262 cursor sel_resource_book_currency is
1263 select evt.currency_code
1264 from ota_resource_bookings trb
1265 , ota_events evt
1266 where trb.resource_booking_id = p_resource_booking_id
1267 and evt.event_id = trb.event_id;
1268 --
1269 Begin
1270 --
1271 hr_utility.set_location('Entering:'|| v_proc, 5);
1272 --
1273 --
1274 If p_finance_line_type = 'V' Then
1275 --
1276 -- * TYPE Vendor Payment
1277 --
1278 Open sel_resource_book_currency;
1279 Fetch sel_resource_book_currency into v_line_currency_code;
1280 --
1281 If sel_resource_book_currency%notfound OR
1282 v_line_currency_code is null Then
1283 --
1284 Close sel_resource_book_currency;
1285 --
1286 fnd_message.set_name('OTA','OTA_13358_TFL_CURRENCY');
1287 fnd_message.raise_error;
1288 --
1289 End if;
1290 --
1291 Close sel_resource_book_currency;
1292 --
1293 ElsIf p_finance_line_type = 'E' Then
1294 --
1295 -- * TYPE Enrollment Charge
1296 --
1297 Open sel_event_currency;
1298 Fetch sel_event_currency into v_line_currency_code;
1299 --
1300 If sel_event_currency%notfound OR
1301 v_line_currency_code is null Then
1302 --
1303 Close sel_event_currency;
1304 --
1305 fnd_message.set_name('OTA','OTA_13358_TFL_CURRENCY');
1306 fnd_message.raise_error;
1307 --
1308 End if;
1309 --
1310 Close sel_event_currency;
1311 --
1315 --
1312 ElsIf p_finance_line_type = 'P' Then
1313 --
1314 -- * TYPE Pre-purchase deal
1316 Open sel_price_list_currency;
1317 Fetch sel_price_list_currency into v_line_currency_code;
1318 --
1319 If sel_price_list_currency%notfound OR
1320 v_line_currency_code is null Then
1321 --
1322 Close sel_price_list_currency;
1323 --
1324 fnd_message.set_name('OTA','OTA_13358_TFL_CURRENCY');
1325 fnd_message.raise_error;
1326 --
1327 End if;
1328 --
1329 Close sel_price_list_currency;
1330 --
1331 ElsIf p_finance_line_type = 'R' Then
1332 --
1333 -- * TYPE Resource Charge
1334 --
1335 Open sel_resource_alloc_currency;
1336 Fetch sel_resource_alloc_currency into v_line_currency_code;
1337 --
1338 If sel_resource_alloc_currency%notfound OR
1339 v_line_currency_code is null Then
1340 --
1341 Close sel_resource_alloc_currency;
1342 --
1343 fnd_message.set_name('OTA','OTA_13358_TFL_CURRENCY');
1344 fnd_message.raise_error;
1345 --
1346 End if;
1347 --
1348 Close sel_resource_alloc_currency;
1349 --
1350 -- End if;
1351 --
1352 p_currency_code := v_line_currency_code;
1353 --
1354 End if;
1355 --
1356 hr_utility.set_location(' Leaving:'|| v_proc, 10);
1357 --
1358 End get_currency_code;
1359 --
1360 -- ----------------------------------------------------------------------------
1361 -- |---------------------< check_type_constraints >---------------------------|
1362 -- ----------------------------------------------------------------------------
1363 --
1364 -- PUBLIC
1365 -- Description:
1366 --
1367 procedure check_type_constraints (
1368 p_finance_line_type in varchar2
1369 ,p_finance_header_id in number
1370 ,p_booking_id in number
1371 ,p_booking_deal_id in number
1372 ,p_resource_booking_id in number
1373 ,p_resource_allocation_id in number
1374 ) is
1375 -----------------
1376 cursor csr_tfh is
1377 select tfh.type,tfh.receivable_type
1378 from ota_finance_headers tfh
1379 where tfh.finance_header_id = p_finance_header_id;
1380 --
1381 v_proc varchar2(72) := g_package||'check_line_type';
1382 --
1383 l_tfh_type varchar2(30);
1384 l_tfh_receivable_type varchar2(30);
1385 --------------
1386 begin
1387 --
1388 hr_utility.set_location('Entering:'|| v_proc, 5);
1389 --
1390 Open csr_tfh;
1391 Fetch csr_tfh into l_tfh_type,l_tfh_receivable_type;
1392 Close csr_tfh;
1393 --
1394 check_line_type (
1395 p_finance_line_type => p_finance_line_type
1396 ,p_finance_header_type => l_tfh_type
1397 ,p_receivable_type => l_tfh_receivable_type
1398 );
1399 --
1400 check_tfl_foreign_keys (
1401 p_finance_header_id => p_finance_header_id
1402 ,p_receivable_type => l_tfh_receivable_type
1403 ,p_finance_line_type => p_finance_line_type
1404 ,p_booking_id => p_booking_id
1405 ,p_booking_deal_id => p_booking_deal_id
1406 ,p_resource_booking_id => p_resource_booking_id
1407 ,p_resource_allocation_id => p_resource_allocation_id
1408 );
1409 --
1410 end check_type_constraints;
1411 --
1412 -- ---------------------------------------------------------------------------
1413 -- |--------------------< check_unique_finance_line >-----------------------|
1414 -- ---------------------------------------------------------------------------
1415 --
1416 -- PUBLIC
1417 -- Description:
1418 --
1419 procedure check_unique_finance_line
1420 (p_finance_line_id in number
1421 ,p_line_type in varchar2
1422 ,p_booking_id in number
1423 ,p_resource_booking_id in number
1424 ,p_resource_allocation_id in number ) is
1425 --
1426 v_proc varchar2(72) := g_package||'check_unique_finance_line';
1427 --
1428 l_finance_line_id number;
1429 --
1430 cursor find_other_lines is
1431 select finance_line_id
1432 from ota_finance_lines
1433 where (p_finance_line_id is null
1434 or p_finance_line_id <> finance_line_id)
1435 and cancelled_flag = 'N'
1436 and ((p_line_type = 'E' and
1437 booking_id = p_booking_id)
1438 or (p_line_type = 'R' and
1439 resource_allocation_id = p_resource_allocation_id)
1440 or (p_line_type = 'V' and
1441 resource_booking_id = p_resource_booking_id));
1442 --
1443 begin
1444 hr_utility.set_location('Entering:'|| v_proc, 5);
1445 --
1446 if p_line_type in ('E','R','V','CT') then
1447 open find_other_lines;
1448 fetch find_other_lines into l_finance_line_id;
1449 if find_other_lines%found then
1450 close find_other_lines;
1451 --
1452 fnd_message.set_name('OTA','OTA_13384_TFL_NO_DUPLICATES');
1453 fnd_message.raise_error;
1454 end if;
1455 close find_other_lines;
1456 end if;
1457 --
1461 --
1458 hr_utility.set_location('Leaving:'|| v_proc, 10);
1459 end;
1460
1462 -- ---------------------------------------------------------------------------
1463 -- |--------------------< get_finance_header >-----------------------------|
1464 -- ---------------------------------------------------------------------------
1465 --
1466 -- PUBLIC
1467 -- Description:
1468 --
1469 procedure get_finance_header
1470 (p_finance_header_id in number
1471 ,p_tfh_type in out nocopy varchar2
1472 ,p_customer_id in out nocopy number
1473 ,p_vendor_id in out nocopy number
1474 ,p_tfh_receivable_type in out nocopy varchar2
1475 ,p_tfh_transfer_status in out nocopy varchar2
1476 ,p_tfh_superseded_flag in out nocopy varchar2
1477 ,p_tfh_cancelled_flag in out nocopy varchar2) is
1478 --
1479 l_proc varchar2(72) := g_package||'get_finance_header';
1480 --
1481 cursor get_finance_header is
1482 select type
1483 , customer_id
1484 , vendor_id
1485 , receivable_type
1486 , transfer_status
1487 , cancelled_flag
1488 from ota_finance_headers
1489 where finance_header_id = p_finance_header_id;
1490 --
1491 cursor get_superseding_header is
1492 select 'Y'
1493 from ota_finance_headers
1494 where superceding_header_id = p_finance_header_id;
1495 --
1496 begin
1497 hr_utility.set_location('Entering:'||l_proc, 5);
1498 --
1499 open get_finance_header;
1500 fetch get_finance_header into p_tfh_type,
1501 p_customer_id,
1502 p_vendor_id,
1503 p_tfh_receivable_type,
1504 p_tfh_transfer_status,
1505 p_tfh_cancelled_flag;
1506 close get_finance_header;
1507 --
1508 open get_superseding_header;
1509 fetch get_superseding_header into p_tfh_superseded_flag;
1510 if get_superseding_header%notfound then
1511 p_tfh_superseded_flag := 'N';
1512 close get_superseding_header;
1513 end if;
1514 --
1515 hr_utility.set_location('Leaving:'||l_proc, 10);
1516 end get_finance_header;
1517 --
1518 --
1519 -- ---------------------------------------------------------------------------
1520 -- |--------------------< check_finance_header >---------------------------|
1521 -- ---------------------------------------------------------------------------
1522 --
1523 -- PUBLIC
1524 -- Description:
1525 --
1526 procedure check_finance_header(p_type in varchar2
1527 ,p_superseded_flag in varchar2
1528 ,p_transfer_status in varchar2
1529 ,p_cancelled_flag in varchar2
1530 ,p_check_cancelled_flag in boolean
1531 ,p_check_successful_transfer in boolean) is
1532 l_proc varchar2(72) := g_package||'check_finance_header';
1533 begin
1534 hr_utility.set_location('Entering:'||l_proc, 5);
1535 if p_type = 'C' then
1536 fnd_message.set_name('OTA','OTA_13490_TFL_INVALID_TFH');
1537 fnd_message.set_token('STEP','1');
1538 fnd_message.raise_error;
1539 end if;
1540 --
1541 /* N.B. p_check_successful_transfer indicates that the mode currently running
1542 is CANCEL_HEADER_LINE. When performing this process the Finance Header
1543 for the lines to be processed has already been updated with
1544 Cancelled_flag = 'Y' and Transfer Status = 'ST' and
1545 Superseded_flag = 'Y'. We therefore do not want to repeat checks on
1546 these attributes.
1547 */
1548 if p_check_successful_transfer then
1549 hr_utility.trace('Superseded Flag = '||p_superseded_flag);
1550 if p_superseded_flag = 'Y' then
1551 fnd_message.set_name('OTA','OTA_13490_TFL_INVALID_TFH');
1552 fnd_message.set_token('STEP','2');
1553 fnd_message.raise_error;
1554 end if;
1555 end if;
1556 --
1557 if p_check_successful_transfer then
1558 hr_utility.trace('Transfer Status = '||p_transfer_status);
1559 if p_transfer_status = 'ST' then
1560 fnd_message.set_name('OTA','OTA_13490_TFL_INVALID_TFH');
1561 fnd_message.set_token('STEP','3');
1562 fnd_message.raise_error;
1563 end if;
1564 hr_utility.trace('After Transfer Status Check');
1565 end if;
1566 --
1567 if p_check_cancelled_flag then
1568 hr_utility.trace('Cancelled Flag = '||p_cancelled_flag);
1569 if p_cancelled_flag = 'Y' then
1570 fnd_message.set_name('OTA','OTA_13490_TFL_INVALID_TFH');
1571 fnd_message.set_token('STEP','4');
1572 fnd_message.raise_error;
1573 end if;
1574 end if;
1575 --
1576 hr_utility.set_location('Leaving:'||l_proc, 10);
1577 end check_finance_header;
1578
1579 end ota_tfl_api_business_rules;