[Home] [Help]
PACKAGE BODY: APPS.OTA_TFL_API_BUS
Source
1 Package Body ota_tfl_api_bus as
2 /* $Header: ottfl01t.pkb 120.0 2005/05/29 07:41:43 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tfl_api_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< insert_validate >----------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure insert_validate( p_rec in out nocopy ota_tfl_api_shd.g_rec_type
14 , p_transaction_type in varchar2) is
15
16 --
17 l_proc varchar2(72) := g_package||'insert_validate';
18 --
19 l_tfh_type varchar2(30);
20 l_tfh_customer_id number;
21 l_tfh_vendor_id number;
22 l_tfh_receivable_type varchar2(30);
23 l_tfh_transfer_status varchar2(30);
24 l_tfh_superseded_flag varchar2(30);
25 l_tfh_cancelled_flag varchar2(30);
26 Begin
27 --
28 hr_utility.set_location('Entering:'||l_proc, 5);
29 --
30 --
31 ota_tfl_api_business_rules.get_finance_header
32 (p_rec.finance_header_id
33 ,l_tfh_type
34 ,l_tfh_customer_id
35 ,l_tfh_vendor_id
36 ,l_tfh_receivable_type
37 ,l_tfh_transfer_status
38 ,l_tfh_superseded_flag
39 ,l_tfh_cancelled_flag);
40 --
41 -- General checks on the Finance Header
42 ota_tfl_api_business_rules.check_finance_header
43 (l_tfh_type
44 ,l_tfh_superseded_flag
45 ,l_tfh_transfer_status
46 ,l_tfh_cancelled_flag
47 ,TRUE
48 ,TRUE);
49 --
50 if p_transaction_type <> 'COPY' then
51 ota_tfl_api_business_rules.check_unique_finance_line
52 (p_rec.finance_line_id
53 ,p_rec.line_type
54 ,p_rec.booking_id
55 ,p_rec.resource_booking_id
56 ,p_rec.resource_allocation_id);
57 end if;
58 --
59 ota_tfl_api_business_rules.check_type_constraints (
60 p_finance_line_type => p_rec.line_type
61 ,p_finance_header_id => p_rec.finance_header_id
62 ,p_booking_id => p_rec.booking_id
63 ,p_booking_deal_id => p_rec.booking_deal_id
64 ,p_resource_booking_id => p_rec.resource_booking_id
65 ,p_resource_allocation_id => p_rec.resource_allocation_id
66 );
67 --
68 ota_tfl_api_business_rules.Check_currency_code
69 (
70 p_finance_line_type => p_rec.line_type
71 ,p_finance_header_id => p_rec.finance_header_id
72 ,p_booking_id => p_rec.booking_id
73 ,p_booking_deal_id => p_rec.booking_deal_id
74 ,p_resource_allocation_id => p_rec.resource_allocation_id
75 ,p_resource_booking_id => p_rec.resource_booking_id
76 );
77 --
78 ota_tfl_api_business_rules2.set_all_amounts
79 ( p_finance_line_type => p_rec.line_type
80 , p_activity_version_id => null
81 , p_event_id => null
82 , p_booking_id => p_rec.booking_id
83 , p_booking_deal_id => p_rec.booking_deal_id
84 , p_resource_allocation_id => p_rec.resource_allocation_id
85 , p_resource_booking_id => p_rec.resource_booking_id
86 , p_currency_code => p_rec.currency_code
87 , p_standard_amount => p_rec.standard_amount
88 , p_money_amount => p_rec.money_amount
89 , p_unitary_amount => p_rec.unitary_amount );
90 --
91 ota_tfl_api_business_rules.check_type_and_amounts
92 ( p_finance_line_type => p_rec.line_type
93 , p_standard_amount => p_rec.standard_amount
94 , p_money_amount => p_rec.money_amount
95 , p_unitary_amount => p_rec.unitary_amount
96 , p_booking_deal_id => p_rec.booking_deal_id
97 , p_finance_header_id => p_rec.finance_header_id
98 );
99 --
100 hr_utility.trace('Date Raised '||to_char(p_rec.date_raised,'DD-MON-YYYY'));
101 ota_tfl_api_business_rules.get_date_raised
102 ( p_rec.finance_header_id
103 , p_rec.date_raised );
104 --
105 ota_tfl_api_business_rules.get_next_sequence_number
106 ( p_rec.finance_header_id
107 , p_rec.sequence_number );
108 --
109 --
110 hr_utility.set_location(' Leaving:'||l_proc, 10);
111 --
112 End insert_validate;
113 --
114 -- ----------------------------------------------------------------------------
115 -- |---------------------------< update_validate >----------------------------|
116 -- ----------------------------------------------------------------------------
117 Procedure update_validate( p_rec in out nocopy ota_tfl_api_shd.g_rec_type
118 , p_money_amount in out nocopy number
119 , p_unitary_amount in out nocopy number
120 , p_date_raised in out nocopy date
121 , p_sequence_number in out nocopy number
122 , p_transaction_type in varchar2) is
123 --
124 l_proc varchar2(72) := g_package||'update_validate';
125 --
126 l_finance_header_id_changed boolean
127 := ota_general.value_changed( ota_tfl_api_shd.g_old_rec.finance_header_id
128 , p_rec.finance_header_id );
129 --
130 l_line_type_changed boolean
131 := ota_general.value_changed( ota_tfl_api_shd.g_old_rec.line_type
132 , p_rec.line_type );
133 --
134 l_cancelled_flag_changed boolean
135 := ota_general.value_changed( ota_tfl_api_shd.g_old_rec.cancelled_flag
136 , p_rec.cancelled_flag );
137 --
138 l_date_raised_changed boolean
139 := ota_general.value_changed( ota_tfl_api_shd.g_old_rec.date_raised
140 , p_rec.date_raised );
141 --
142 l_sequence_number_changed boolean
143 := ota_general.value_changed( ota_tfl_api_shd.g_old_rec.sequence_number
144 , p_rec.sequence_number );
145 --
146 l_transfer_status_changed boolean
147 := ota_general.value_changed( ota_tfl_api_shd.g_old_rec.transfer_status
148 , p_rec.transfer_status );
149 --
150 l_booking_deal_id_changed boolean
151 := ota_general.value_changed( ota_tfl_api_shd.g_old_rec.booking_deal_id
152 , p_rec.booking_deal_id );
153 --
154 l_resource_booking_id_changed boolean
155 := ota_general.value_changed( ota_tfl_api_shd.g_old_rec.resource_booking_id
156 , p_rec.resource_booking_id );
157 --
158 l_resource_alloc_id_changed boolean
159 := ota_general.value_changed( ota_tfl_api_shd.g_old_rec.resource_allocation_id
160 , p_rec.resource_allocation_id );
161 --
162 l_booking_id_changed boolean
163 := ota_general.value_changed( ota_tfl_api_shd.g_old_rec.booking_id
164 , p_rec.booking_id );
165 --
166 l_standard_amount_changed boolean
167 := ota_general.value_changed( ota_tfl_api_shd.g_old_rec.standard_amount
168 , p_rec.standard_amount );
169 --
170 l_money_amount_changed boolean
171 := ota_general.value_changed( ota_tfl_api_shd.g_old_rec.money_amount
172 , p_rec.money_amount );
173 --
174 l_unitary_amount_changed boolean
175 := ota_general.value_changed( ota_tfl_api_shd.g_old_rec.unitary_amount
176 , p_rec.unitary_amount );
177 --
178 l_tfh_type varchar2(30);
179 l_tfh_customer_id number;
180 l_tfh_vendor_id number;
181 l_tfh_receivable_type varchar2(30);
182 l_tfh_transfer_status varchar2(30);
183 l_tfh_superseded_flag varchar2(30);
184 l_tfh_cancelled_flag varchar2(30);
185 Begin
186 hr_utility.set_location('Entering:'||l_proc, 5);
187 hr_utility.trace(p_transaction_type);
188 --
189 -- Call all supporting business operations
190 --
191 -- p_rec can only be IN parameter !!
192 --
193 p_money_amount := p_rec.money_amount;
194 p_unitary_amount := p_rec.unitary_amount;
195 p_date_raised := p_rec.date_raised;
196 p_sequence_number:= p_rec.sequence_number;
197 --
198 ota_tfl_api_business_rules.get_finance_header
199 (p_rec.finance_header_id
200 ,l_tfh_type
201 ,l_tfh_customer_id
202 ,l_tfh_vendor_id
203 ,l_tfh_receivable_type
204 ,l_tfh_transfer_status
205 ,l_tfh_superseded_flag
206 ,l_tfh_cancelled_flag);
207 --
208 -- General checks on the Finance Header
209 ota_tfl_api_business_rules.check_finance_header
210 (l_tfh_type
211 ,l_tfh_superseded_flag
212 ,l_tfh_transfer_status
213 ,l_tfh_cancelled_flag
214 ,(p_transaction_type <> 'CANCEL_HEADER_LINE' and
215 p_transaction_type <> 'REINSTATE_HEADER_LINE')
216 ,(p_transaction_type <> 'CANCEL_HEADER_LINE'));
217 --
218 if p_transaction_type = 'CANCEL_HEADER_LINE' then
219 return;
220 end if;
221 --
222 if p_transaction_type not in
223 ('CANCEL_LINE','REINSTATE_LINE','REINSTATE_HEADER_LINE') then
224 if l_cancelled_flag_changed then
225 fnd_message.set_name('OTA','OTA_13356_TFL_CANCELLED_FLAG');
226 fnd_message.raise_error;
227 end if;
228 end if;
229 /*
230 ota_tfl_api_business_rules.check_update_cancelled_flag
231 ( p_rec.cancelled_flag );
232 */
233 --
234 ota_tfl_api_business_rules.check_update_attributes(p_rec.transfer_status);
235 --
236 if p_transaction_type = 'CANCEL_LINE' then
237 return;
238 end if;
239 --
240 if p_transaction_type not in
241 ('REINSTATE_LINE','REINSTATE_HEADER_LINE') then
242 if p_rec.cancelled_flag = 'Y' then
243 fnd_message.set_name('OTA','OTA_13488_TFL_CANCELLED');
244 fnd_message.raise_error;
245 end if;
246 end if;
247 --
248 if l_line_type_changed then
249 fnd_message.set_name('OTA','OTA_13354_TFL_NO_TYPE_UPDATE');
250 fnd_message.raise_error;
251 end if;
252 --
253 if (l_cancelled_flag_changed and
254 p_rec.cancelled_flag = 'N')
255 or l_booking_id_changed
256 or l_resource_booking_id_changed
257 or l_resource_alloc_id_changed then
258 ota_tfl_api_business_rules.check_unique_finance_line
259 (p_rec.finance_line_id
260 ,p_rec.line_type
261 ,p_rec.booking_id
262 ,p_rec.resource_booking_id
263 ,p_rec.resource_allocation_id);
264 end if;
265
266 ota_tfl_api_business_rules.check_type_constraints (
267 p_finance_line_type => p_rec.line_type
268 ,p_finance_header_id => p_rec.finance_header_id
269 ,p_booking_id => p_rec.booking_id
270 ,p_booking_deal_id => p_rec.booking_deal_id
271 ,p_resource_booking_id => p_rec.resource_booking_id
272 ,p_resource_allocation_id => p_rec.resource_allocation_id
273 );
274 --
275 ota_tfl_api_business_rules.Check_currency_code
276 (
277 p_finance_line_type => p_rec.line_type
278 ,p_finance_header_id => p_rec.finance_header_id
279 ,p_booking_id => p_rec.booking_id
280 ,p_booking_deal_id => p_rec.booking_deal_id
281 ,p_resource_allocation_id => p_rec.resource_allocation_id
282 ,p_resource_booking_id => p_rec.resource_booking_id
283 );
284 --
285 If p_date_raised is null Then
286 ota_tfl_api_business_rules.get_date_raised
287 ( p_rec.finance_header_id
288 , p_date_raised );
289 End if;
290 --
291 IF p_sequence_number is null Then
292 ota_tfl_api_business_rules.get_next_sequence_number
293 ( p_rec.finance_header_id
294 , p_sequence_number );
295 --
296 Elsif l_sequence_number_changed Then
297 If l_finance_header_id_changed Then
298 ota_tfl_api_business_rules.get_next_sequence_number
299 ( p_rec.finance_header_id
300 , p_sequence_number );
301 Else
302 ota_tfl_api_business_rules.check_sequence_number
303 ( p_rec.finance_header_id
304 , p_sequence_number );
305 End if;
306 --
307 Else
308 If l_finance_header_id_changed Then
309 ota_tfl_api_business_rules.get_next_sequence_number
310 ( p_rec.finance_header_id
311 , p_sequence_number );
312 End if;
313 End if;
314 --
315 if l_booking_id_changed OR
316 l_booking_deal_id_changed OR
317 l_resource_alloc_id_changed OR
318 l_resource_booking_id_changed OR
319 l_standard_amount_changed OR
320 p_money_amount is null Then
321 --
322 ota_tfl_api_business_rules2.set_all_amounts (
323 p_finance_line_type => p_rec.line_type
324 , p_activity_version_id => null
325 , p_event_id => null
326 , p_booking_id => p_rec.booking_id
327 , p_booking_deal_id => p_rec.booking_deal_id
328 , p_resource_allocation_id => p_rec.resource_allocation_id
329 , p_resource_booking_id => p_rec.resource_booking_id
330 , p_currency_code => p_rec.currency_code
331 , p_standard_amount => p_rec.standard_amount
332 , p_money_amount => p_rec.money_amount
333 , p_unitary_amount => p_rec.unitary_amount );
334 --
335 End if;
336 --
337 ota_tfl_api_business_rules.check_type_and_amounts
338 ( p_finance_line_type => p_rec.line_type
339 , p_standard_amount => p_rec.standard_amount
340 , p_money_amount => p_rec.money_amount
341 , p_unitary_amount => p_rec.unitary_amount
342 , p_booking_deal_id => p_rec.booking_deal_id
343 , p_finance_header_id => p_rec.finance_header_id
344 );
345 --
346 hr_utility.set_location(' Leaving:'||l_proc, 10);
347 --
348 End update_validate;
349 --
350 -- ----------------------------------------------------------------------------
351 -- |---------------------------< delete_validate >----------------------------|
352 -- ----------------------------------------------------------------------------
353 Procedure delete_validate(p_rec in ota_tfl_api_shd.g_rec_type) is
354 --
355 l_proc varchar2(72) := g_package||'delete_validate';
356 --
357 Begin
358 hr_utility.set_location('Entering:'||l_proc, 5);
359 --
360 -- Call all supporting business operations
361 -- Do Not allow deletion if Finance Line Transfer_status = 'ST'
362 --
363 if ota_tfl_api_shd.g_old_rec.transfer_status = 'ST' then
364 fnd_message.set_name('OTA','OTA_13610_TFL_DELETE_CHK');
365 fnd_message.raise_error;
366 end if;
367 --
368 hr_utility.set_location(' Leaving:'||l_proc, 10);
369 End delete_validate;
370 --
371 -- ---------------------------------------------------------------------------
372 -- |----------------------< set_security_group_id >--------------------------|
373 -- ---------------------------------------------------------------------------
374 --
375 Procedure set_security_group_id
376 (p_finance_line_id in number
377 ,p_associated_column1 in varchar2 default null
378 ) is
379 --
380 -- Declare cursor
381 --
382 cursor csr_sec_grp is
383 select pbg.security_group_id,
384 pbg.legislation_code
385 from per_business_groups_perf pbg,
386 ota_finance_headers tfh,
387 ota_finance_lines tfl,
388 hr_all_organization_units org
389 where pbg.business_group_id = org.business_group_id
390 and org.organization_id = tfh.organization_id
391 and tfh.finance_header_id = tfl.finance_header_id
392 and tfl.finance_line_id = p_finance_line_id;
393
394 --
395 -- Declare local variables
396 --
397 l_security_group_id number;
398 l_proc varchar2(72) := g_package||'set_security_group_id';
399 l_legislation_code varchar2(150);
400 --
401 begin
402 --
403 hr_utility.set_location('Entering:'|| l_proc, 10);
404 --
405 -- Ensure that all the mandatory parameter are not null
406 --
407 hr_api.mandatory_arg_error
408 (p_api_name => l_proc
409 ,p_argument => 'finance_line_id'
410 ,p_argument_value => p_finance_line_id
411 );
412 --
413 open csr_sec_grp;
414 fetch csr_sec_grp into l_security_group_id
415 , l_legislation_code;
416 --
417 if csr_sec_grp%notfound then
418 --
419 close csr_sec_grp;
420 --
421 -- The primary key is invalid therefore we must error
422 --
423 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
424 hr_multi_message.add
425 (p_associated_column1
426 => nvl(p_associated_column1,'FINANCE_LINE_ID')
427 );
428 --
429 else
430 close csr_sec_grp;
431 --
432 -- Set the security_group_id in CLIENT_INFO
433 --
434 hr_api.set_security_group_id
435 (p_security_group_id => l_security_group_id
436 );
437 --
438 -- Set the sessions legislation context in HR_SESSION_DATA
439 --
440 hr_api.set_legislation_context(l_legislation_code);
441 end if;
442 --
443 hr_utility.set_location(' Leaving:'|| l_proc, 20);
444 --
445 end set_security_group_id;
446
447 -- ----------------------------------------------------------------------------
448 -- |-----------------------< return_legislation_code >-------------------------|
449 -- ----------------------------------------------------------------------------
450 --
451 -- {Start Of Comments}
452 --
453 -- Description:
454 -- This function will be used by the user hooks. This will be used
455 -- of by the user hooks of ota_finance_header and
456 -- ota_finance_lines row handler user hook business process.
457 --
458 -- Pre Conditions:
459 -- This function will be called by the user hook packages.
460 --
461 -- In Arguments:
462 -- p_finance_line_id
463 --
464 -- Post Success:
465 -- Processing continues.
466 --
467 -- Post Failure:
468 -- Errors out
469 --
470 -- Developer Implementation Notes:
471 --
472 -- Access Status:
473 -- Internal Development Use Only.
474 --
475 -- {End Of Comments}
476 --------------------------------------------------------------------------------
477 --
478 Function return_legislation_code
479 ( p_finance_line_id in number
480 ) return varchar2 is
481 --
482 -- Declare cursor
483 --
484 cursor csr_leg_code is
485 select legislation_code
486 from per_business_groups_perf pbg,
487 ota_finance_headers tfh,
488 ota_finance_lines tfl,
489 hr_all_organization_units org
490 where pbg.business_group_id = org.business_group_id
491 and org.organization_id = tfh.organization_id
492 and tfh.finance_header_id = tfl.finance_header_id
493 and tfl.finance_line_id = p_finance_line_id;
494
495
496 l_proc varchar2(72) := g_package||'return_legislation_code';
497 l_legislation_code varchar2(150);
498 --
499 Begin
500 hr_utility.set_location('Entering:'||l_proc, 5);
501 --
502 -- Ensure that all the mandatory parameters are not null
503 --
504 hr_api.mandatory_arg_error (p_api_name => l_proc,
505 p_argument => 'finance_line_id',
506 p_argument_value => p_finance_line_id);
507 open csr_leg_code;
508 fetch csr_leg_code into l_legislation_code;
509 if csr_leg_code%notfound then
510 close csr_leg_code;
511 --
512 -- The primary key is invalid therefore we must error out
513 --
514 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
515 hr_utility.raise_error;
516 end if;
517 --
518 close csr_leg_code;
519 return l_legislation_code;
520 --
521 hr_utility.set_location(' Leaving:'||l_proc, 10);
522 --
523 End return_legislation_code;
524
525
526 end ota_tfl_api_bus;