1 Package Body psp_pee_bus as
2 /* $Header: PSPEERHB.pls 120.3 2006/02/08 05:35 dpaudel noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' psp_pee_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_external_effort_line_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_external_effort_line_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id,
29 pbg.legislation_code
30 from per_business_groups_perf pbg
31 , psp_external_effort_lines pee
32 where pee.external_effort_line_id = p_external_effort_line_id
33 and pbg.business_group_id = pee.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 l_legislation_code varchar2(150);
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'external_effort_line_id'
50 ,p_argument_value => p_external_effort_line_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id
55 , l_legislation_code;
56 --
57 if csr_sec_grp%notfound then
58 --
59 close csr_sec_grp;
60 --
61 -- The primary key is invalid therefore we must error
62 --
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 hr_multi_message.add
65 (p_associated_column1
66 => nvl(p_associated_column1,'EXTERNAL_EFFORT_LINE_ID')
67 );
68 --
69 else
70 close csr_sec_grp;
71 --
72 -- Set the security_group_id in CLIENT_INFO
73 --
74 hr_api.set_security_group_id
75 (p_security_group_id => l_security_group_id
76 );
77 --
78 -- Set the sessions legislation context in HR_SESSION_DATA
79 --
80 hr_api.set_legislation_context(l_legislation_code);
81 end if;
82 --
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_external_effort_line_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 cursor csr_leg_code is
99 select pbg.legislation_code
100 from per_business_groups_perf pbg
101 , psp_external_effort_lines pee
102 where pee.external_effort_line_id = p_external_effort_line_id
103 and pbg.business_group_id = pee.business_group_id;
104 --
105 -- Declare local variables
106 --
107 l_legislation_code varchar2(150);
108 l_proc varchar2(72) := g_package||'return_legislation_code';
109 --
110 Begin
111 --
112 hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Ensure that all the mandatory parameter are not null
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'external_effort_line_id'
119 ,p_argument_value => p_external_effort_line_id
120 );
121 --
122 if ( nvl(psp_pee_bus.g_external_effort_line_id, hr_api.g_number)
123 = p_external_effort_line_id) then
124 --
125 -- The legislation code has already been found with a previous
126 -- call to this function. Just return the value in the global
127 -- variable.
128 --
129 l_legislation_code := psp_pee_bus.g_legislation_code;
130 hr_utility.set_location(l_proc, 20);
131 else
132 --
133 -- The ID is different to the last call to this function
134 -- or this is the first call to this function.
135 --
136 open csr_leg_code;
137 fetch csr_leg_code into l_legislation_code;
138 --
139 if csr_leg_code%notfound then
140 --
141 -- The primary key is invalid therefore we must error
142 --
143 close csr_leg_code;
144 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
145 fnd_message.raise_error;
146 end if;
147 hr_utility.set_location(l_proc,30);
148 --
149 -- Set the global variables so the values are
150 -- available for the next call to this function.
151 --
152 close csr_leg_code;
153 psp_pee_bus.g_external_effort_line_id := p_external_effort_line_id;
154 psp_pee_bus.g_legislation_code := l_legislation_code;
155 end if;
156 hr_utility.set_location(' Leaving:'|| l_proc, 40);
157 return l_legislation_code;
158 end return_legislation_code;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |------------------------------< chk_df >----------------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 -- Description:
165 -- Validates all the Descriptive Flexfield values.
166 --
167 -- Prerequisites:
168 -- All other columns have been validated. Must be called as the
169 -- last step from insert_validate and update_validate.
170 --
171 -- In Arguments:
172 -- p_rec
173 --
174 -- Post Success:
175 -- If the Descriptive Flexfield structure column and data values are
176 -- all valid this procedure will end normally and processing will
177 -- continue.
178 --
179 -- Post Failure:
180 -- If the Descriptive Flexfield structure column value or any of
181 -- the data values are invalid then an application error is raised as
182 -- a PL/SQL exception.
183 --
184 -- Access Status:
185 -- Internal Row Handler Use Only.
186 --
187 -- ----------------------------------------------------------------------------
188 procedure chk_df
189 (p_rec in psp_pee_shd.g_rec_type
190 ) is
191 --
192 l_proc varchar2(72) := g_package || 'chk_df';
193 --
194 begin
195 hr_utility.set_location('Entering:'||l_proc,10);
196 --
197 if ((p_rec.external_effort_line_id is not null) and (
198 nvl(psp_pee_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
199 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
200 nvl(psp_pee_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
201 nvl(p_rec.attribute1, hr_api.g_varchar2) or
202 nvl(psp_pee_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
203 nvl(p_rec.attribute2, hr_api.g_varchar2) or
204 nvl(psp_pee_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
205 nvl(p_rec.attribute3, hr_api.g_varchar2) or
206 nvl(psp_pee_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
207 nvl(p_rec.attribute4, hr_api.g_varchar2) or
208 nvl(psp_pee_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
209 nvl(p_rec.attribute5, hr_api.g_varchar2) or
210 nvl(psp_pee_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
211 nvl(p_rec.attribute6, hr_api.g_varchar2) or
212 nvl(psp_pee_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
213 nvl(p_rec.attribute7, hr_api.g_varchar2) or
214 nvl(psp_pee_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
215 nvl(p_rec.attribute8, hr_api.g_varchar2) or
216 nvl(psp_pee_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
217 nvl(p_rec.attribute9, hr_api.g_varchar2) or
218 nvl(psp_pee_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
219 nvl(p_rec.attribute10, hr_api.g_varchar2) or
220 nvl(psp_pee_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
221 nvl(p_rec.attribute11, hr_api.g_varchar2) or
222 nvl(psp_pee_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
223 nvl(p_rec.attribute12, hr_api.g_varchar2) or
224 nvl(psp_pee_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
225 nvl(p_rec.attribute13, hr_api.g_varchar2) or
226 nvl(psp_pee_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
227 nvl(p_rec.attribute14, hr_api.g_varchar2) or
228 nvl(psp_pee_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
229 nvl(p_rec.attribute15, hr_api.g_varchar2) ))
230 or (p_rec.external_effort_line_id is null) then
231 --
232 -- Only execute the validation if absolutely necessary:
233 -- a) During update, the structure column value or any
234 -- of the attribute values have actually changed.
235 -- b) During insert.
236 --
237 hr_dflex_utility.ins_or_upd_descflex_attribs
238 (p_appl_short_name => 'PSP'
239 ,p_descflex_name => 'External Effort Lines DF'
240 ,p_attribute_category => p_rec.attribute_category
241 ,p_attribute1_name => 'ATTRIBUTE1'
242 ,p_attribute1_value => p_rec.attribute1
243 ,p_attribute2_name => 'ATTRIBUTE2'
244 ,p_attribute2_value => p_rec.attribute2
245 ,p_attribute3_name => 'ATTRIBUTE3'
246 ,p_attribute3_value => p_rec.attribute3
247 ,p_attribute4_name => 'ATTRIBUTE4'
248 ,p_attribute4_value => p_rec.attribute4
249 ,p_attribute5_name => 'ATTRIBUTE5'
250 ,p_attribute5_value => p_rec.attribute5
251 ,p_attribute6_name => 'ATTRIBUTE6'
252 ,p_attribute6_value => p_rec.attribute6
253 ,p_attribute7_name => 'ATTRIBUTE7'
254 ,p_attribute7_value => p_rec.attribute7
255 ,p_attribute8_name => 'ATTRIBUTE8'
256 ,p_attribute8_value => p_rec.attribute8
257 ,p_attribute9_name => 'ATTRIBUTE9'
258 ,p_attribute9_value => p_rec.attribute9
259 ,p_attribute10_name => 'ATTRIBUTE10'
260 ,p_attribute10_value => p_rec.attribute10
261 ,p_attribute11_name => 'ATTRIBUTE11'
262 ,p_attribute11_value => p_rec.attribute11
263 ,p_attribute12_name => 'ATTRIBUTE12'
264 ,p_attribute12_value => p_rec.attribute12
265 ,p_attribute13_name => 'ATTRIBUTE13'
266 ,p_attribute13_value => p_rec.attribute13
267 ,p_attribute14_name => 'ATTRIBUTE14'
268 ,p_attribute14_value => p_rec.attribute14
269 ,p_attribute15_name => 'ATTRIBUTE15'
270 ,p_attribute15_value => p_rec.attribute15
271 );
272 end if;
273 --
274 hr_utility.set_location(' Leaving:'||l_proc,20);
275 end chk_df;
276 --
277 -- ----------------------------------------------------------------------------
278 -- |-----------------------< chk_non_updateable_args >------------------------|
279 -- ----------------------------------------------------------------------------
280 -- {Start Of Comments}
281 --
282 -- Description:
283 -- This procedure is used to ensure that non updateable attributes have
284 -- not been updated. If an attribute has been updated an error is generated.
285 --
286 -- Pre Conditions:
287 -- g_old_rec has been populated with details of the values currently in
288 -- the database.
289 --
290 -- In Arguments:
291 -- p_rec has been populated with the updated values the user would like the
292 -- record set to.
293 --
294 -- Post Success:
295 -- Processing continues if all the non updateable attributes have not
296 -- changed.
297 --
298 -- Post Failure:
299 -- An application error is raised if any of the non updatable attributes
300 -- have been altered.
301 --
302 -- {End Of Comments}
303 -- ----------------------------------------------------------------------------
304 Procedure chk_non_updateable_args
305 (p_rec in psp_pee_shd.g_rec_type
306 ) IS
307 --
308 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
309 --
310 Begin
311 --
312 -- Only proceed with the validation if a row exists for the current
313 -- record in the HR Schema.
314 --
315 IF NOT psp_pee_shd.api_updating
316 (p_external_effort_line_id => p_rec.external_effort_line_id
317 ,p_object_version_number => p_rec.object_version_number
318 ) THEN
319 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
320 fnd_message.set_token('PROCEDURE ', l_proc);
321 fnd_message.set_token('STEP ', '5');
322 fnd_message.raise_error;
323 END IF;
324 --
325 -- EDIT_HERE: Add checks to ensure non-updateable args have
326 -- not been updated.
327 --
328 End chk_non_updateable_args;
329
330
331 --
332 -- ----------------------------------------------------------------------------
333 -- |---------------------------< chk_business_group_id >----------------------------|
334 -- ----------------------------------------------------------------------------
335 PROCEDURE chk_business_group_id
336 ( p_business_group_id IN NUMBER
337 , p_distribution_date IN DATE
338 )
339 IS
340 l_proc VARCHAR2(72) := g_package||'chk_business_group_id';
341 l_business_group_id NUMBER;
342 --
343 CURSOR business_group_csr IS
344 SELECT business_group_id
345 FROM per_business_groups
346 where business_group_id = p_business_group_id
347 AND p_distribution_date between DATE_FROM and nvl(DATE_TO,to_date('31/12/4712','DD/MM/RRRR'));
348 --
349 BEGIN
350 --
351 -- Check mandatory parameters have been set
352 --
353 hr_api.mandatory_arg_error
354 (p_api_name => l_proc
355 ,p_argument => 'business group id'
356 ,p_argument_value => p_business_group_id
357 );
358
359 hr_api.mandatory_arg_error
360 (p_api_name => l_proc
361 ,p_argument => 'distribution date'
362 ,p_argument_value => p_distribution_date
363 );
364
365 OPEN business_group_csr;
366 FETCH business_group_csr INTO l_business_group_id;
367 IF business_group_csr%NOTFOUND THEN
368 CLOSE business_group_csr;
369 hr_utility.set_message(8403, 'PSP_ER_INVALID_BUSINESS_GROUP');
370 hr_utility.raise_error;
371 END IF;
372 CLOSE business_group_csr;
373 EXCEPTION
374 WHEN app_exception.application_exception THEN
375 IF hr_multi_message.exception_add(p_associated_column1 => 'PSP_EFFORT_INTERFACE.BUSINESS_GROUP_ID') THEN
376 RAISE;
377 END IF;
378 END chk_business_group_id;
379
380 --
381 -- ----------------------------------------------------------------------------
382 -- |---------------------------< chk_set_of_books_id >----------------------------|
383 -- ----------------------------------------------------------------------------
384 PROCEDURE chk_set_of_books_id
385 (p_set_of_books_id IN NUMBER
386 )
387 IS
388 l_proc VARCHAR2(72) := g_package||'chk_set_of_books_id';
389 l_set_of_books_id NUMBER;
390 --
391 CURSOR set_of_books_id_csr IS
392 SELECT set_of_books_id
393 FROM gl_sets_of_books
394 WHERE set_of_books_id = p_set_of_books_id;
395 --
396 BEGIN
397 --
398 -- Check mandatory parameters have been set
399 --
400 hr_api.mandatory_arg_error
401 (p_api_name => l_proc
402 ,p_argument => 'set of books id'
403 ,p_argument_value => p_set_of_books_id
404 );
405
406 OPEN set_of_books_id_csr;
407 FETCH set_of_books_id_csr INTO l_set_of_books_id;
408 if set_of_books_id_csr%notfound then
409 CLOSE set_of_books_id_csr;
410 hr_utility.set_message(8403, 'PSP_ER_INVALID_SET_OF_BOOKS');
411 fnd_message.set_token('SET_OF_BOOKS_ID',l_set_of_books_id);
412 hr_utility.raise_error;
413 END IF;
414 CLOSE set_of_books_id_csr;
415 EXCEPTION
416 WHEN app_exception.application_exception THEN
417 IF hr_multi_message.exception_add(p_associated_column1 => 'PSP_EFFORT_INTERFACE.SET_OF_BOOKS_ID') THEN
418 RAISE;
419 END IF;
420 END chk_set_of_books_id;
421
422 --
423 -- ----------------------------------------------------------------------------
424 -- |---------------------------< chk_person_id >----------------------------|
425 -- ----------------------------------------------------------------------------
426 PROCEDURE chk_person_id
427 ( p_person_id IN NUMBER
428 , p_business_group_id IN NUMBER
429 , p_distribution_date IN DATE
430 )
431 IS
432 --
433 l_proc VARCHAR2(72) := g_package||'chk_person_id';
434 l_person_id NUMBER;
435 --
436 CURSOR person_id_csr IS
437 SELECT person_id
438 FROM per_all_people_f
439 WHERE person_id = p_person_id
440 AND current_employee_flag = 'Y'
441 AND business_group_id = p_business_group_id
442 AND p_distribution_date between effective_start_date and effective_end_date;
443 BEGIN
444 --
445 -- Check mandatory parameters have been set
446 --
447 hr_api.mandatory_arg_error
448 (p_api_name => l_proc
449 ,p_argument => 'person id'
450 ,p_argument_value => p_person_id
451 );
452
453 hr_api.mandatory_arg_error
454 (p_api_name => l_proc
455 ,p_argument => 'business group id'
456 ,p_argument_value => p_business_group_id
457 );
458
459 hr_api.mandatory_arg_error
460 (p_api_name => l_proc
461 ,p_argument => 'distribution date'
462 ,p_argument_value => p_distribution_date
463 );
464
465 OPEN person_id_csr;
466 FETCH person_id_csr INTO l_person_id;
467 if person_id_csr%notfound then
468 CLOSE person_id_csr;
469 hr_utility.set_message(8403, 'PSP_ER_INVALID_PERSON');
470 fnd_message.set_token('PERSON_ID',l_person_id);
471 hr_utility.raise_error;
472 END IF;
473 CLOSE person_id_csr;
474 EXCEPTION
475 WHEN app_exception.application_exception THEN
476 IF hr_multi_message.exception_add(p_associated_column1 => 'PSP_EFFORT_INTERFACE.PERSON_ID') THEN
477 RAISE;
478 END IF;
479 END chk_person_id;
480
481
482
483
484 --
485 -- ----------------------------------------------------------------------------
486 -- |---------------------------< chk_assignment_id >----------------------------|
487 -- ----------------------------------------------------------------------------
488 PROCEDURE chk_assignment_id
489 ( p_assignment_id IN NUMBER
490 , p_person_id IN NUMBER
491 , p_business_group_id IN NUMBER
492 , p_distribution_date IN DATE
493 )
494 IS
495 --
496 l_proc VARCHAR2(72) := g_package||'chk_person_id';
497 l_assignment_id NUMBER;
498 --
499 CURSOR assignment_id_csr IS
500 SELECT assignment_id
501 FROM per_all_assignments_f paaf,
502 pay_all_payrolls_f papf
503 WHERE paaf.payroll_id = papf.payroll_id
504 AND paaf.assignment_id = p_assignment_id
505 AND paaf.person_id = p_person_id
506 AND paaf.business_group_id = p_business_group_id
507 AND p_distribution_date between paaf.effective_start_date and paaf.effective_end_date
508 AND p_distribution_date between papf.effective_start_date and papf.effective_end_date;
509 BEGIN
510 --
511 -- Check mandatory parameters have been set
512 --
513 hr_api.mandatory_arg_error
514 (p_api_name => l_proc
515 ,p_argument => 'assignment id'
516 ,p_argument_value => p_assignment_id
517 );
518
519 hr_api.mandatory_arg_error
520 (p_api_name => l_proc
521 ,p_argument => 'person id'
522 ,p_argument_value => p_person_id
523 );
524
525 hr_api.mandatory_arg_error
526 (p_api_name => l_proc
527 ,p_argument => 'business group id'
528 ,p_argument_value => p_business_group_id
529 );
530
531 hr_api.mandatory_arg_error
532 (p_api_name => l_proc
533 ,p_argument => 'distribution date'
534 ,p_argument_value => p_distribution_date
535 );
536
537 OPEN assignment_id_csr;
538 FETCH assignment_id_csr INTO l_assignment_id;
539 if assignment_id_csr%notfound then
540 CLOSE assignment_id_csr;
541 hr_utility.set_message(8403, 'PSP_ER_INVALID_ASSIGNMENT');
542 fnd_message.set_token('ASSIGNMENT_ID',l_assignment_id);
543 hr_utility.raise_error;
544 END IF;
545 CLOSE assignment_id_csr;
546 EXCEPTION
547 WHEN app_exception.application_exception THEN
548 IF hr_multi_message.exception_add(p_associated_column1 => 'PSP_EFFORT_INTERFACE.ASSIGNMENT_ID') THEN
549 RAISE;
550 END IF;
551 END chk_assignment_id;
552
553
554 --
555 -- ----------------------------------------------------------------------------
556 -- |---------------------------< chk_currency >----------------------------|
557 -- ----------------------------------------------------------------------------
558 PROCEDURE chk_currency
559 ( p_currency_code IN VARCHAR2
560 , p_distribution_date IN DATE
561 )
562 IS
563 --
564 l_proc VARCHAR2(72) := g_package||'chk_currency';
565 l_currency_code VARCHAR2(15);
566 --
567 CURSOR currency_csr (p_currency_code IN VARCHAR2, p_distribution_date IN DATE )IS
568 SELECT fc.currency_code
569 FROM fnd_currencies fc,
570 per_business_groups pbg
571 WHERE fc.currency_code =pbg.currency_code
572 AND fc.enabled_flag = 'Y'
573 AND fc.currency_flag = 'Y'
574 AND fc.currency_code = p_currency_code
575 AND p_distribution_date between nvl(fc.start_date_active,p_distribution_date) and nvl(fc.end_date_active ,to_date('31/12/4712','DD/MM/RRRR'))
576 AND p_distribution_date between date_from and nvl(pbg.DATE_TO,to_date('31/12/4712','DD/MM/RRRR'));
577 BEGIN
578 --
579 -- Check mandatory parameters have been set
580 --
581 hr_api.mandatory_arg_error
582 (p_api_name => l_proc
583 ,p_argument => 'currency code'
584 ,p_argument_value => p_currency_code
585 );
586
587 hr_api.mandatory_arg_error
588 (p_api_name => l_proc
589 ,p_argument => 'distribution date'
590 ,p_argument_value => p_distribution_date
591 );
592
593 OPEN currency_csr(p_currency_code, p_distribution_date);
594 FETCH currency_csr INTO l_currency_code;
595 if currency_csr%notfound then
596 CLOSE currency_csr;
597 hr_utility.set_message(8403, 'PSP_ER_INVALID_CURRENCY');
598 fnd_message.set_token('CURRENCY_CODE',l_currency_code);
599 hr_utility.raise_error;
600 END IF;
601 CLOSE currency_csr;
602 EXCEPTION
603 WHEN app_exception.application_exception THEN
604 IF hr_multi_message.exception_add(p_associated_column1 => 'PSP_EFFORT_INTERFACE.CURRENCY_CODE') THEN
605 RAISE;
606 END IF;
607 END chk_currency;
608
609
610
611 --
612 -- ----------------------------------------------------------------------------
613 -- |---------------------------< chk_ptaoe_gl_combination >----------------------------|
614 -- ----------------------------------------------------------------------------
615 PROCEDURE chk_ptaoe_gl_combination
616 (p_rec IN psp_pee_shd.g_rec_type
617 ) IS
618 BEGIN
619 IF p_rec.project_id IS NULL AND p_rec.gl_code_combination_id IS NULL THEN
620 hr_utility.set_message(8403, 'PSP_POETA_GL');
621 hr_utility.raise_error;
622 END IF;
623
624 IF p_rec.gl_code_combination_id IS NOT NULL
625 AND (p_rec.project_id IS NOT NULL
626 OR p_rec.task_id IS NOT NULL
627 OR p_rec.award_id IS NOT NULL
628 OR p_rec.expenditure_organization_id IS NOT NULL
629 OR p_rec.expenditure_type IS NOT NULL
630 )
631 THEN
632 hr_utility.set_message(8403, 'PSP_ER_EXTRA_CI');
633 hr_utility.raise_error;
634 END IF;
635 EXCEPTION
636 WHEN app_exception.application_exception THEN
637 IF hr_multi_message.exception_add() THEN
638 RAISE;
639 END IF;
640 END chk_ptaoe_gl_combination;
641
642
643
644 --
645 -- ----------------------------------------------------------------------------
646 -- |---------------------------< chk_gl_code_combination >----------------------------|
647 -- ----------------------------------------------------------------------------
648 PROCEDURE chk_gl_code_combination
649 ( p_gl_code_combination_id IN NUMBER
650 )
651 IS
652 --
653 l_proc VARCHAR2(72) := g_package||'chk_gl_code_combination';
654 l_gl_code_combination_id NUMBER;
655 --
656 CURSOR gl_code_combination_csr (p_gl_code_combination_id IN NUMBER) IS
657 SELECT code_combination_id
658 FROM gl_code_combinations
659 WHERE code_combination_id = p_gl_code_combination_id
660 AND enabled_flag ='Y';
661 BEGIN
662 --
663 -- Check mandatory parameters have been set
664 --
665 hr_api.mandatory_arg_error
666 (p_api_name => l_proc
667 ,p_argument => 'gl code combination id'
668 ,p_argument_value => p_gl_code_combination_id
669 );
670
671 OPEN gl_code_combination_csr(p_gl_code_combination_id);
672 FETCH gl_code_combination_csr INTO l_gl_code_combination_id;
673 IF gl_code_combination_csr%notfound then
674 CLOSE gl_code_combination_csr;
675 hr_utility.set_message(8403, 'PSP_ER_INVALID_GLCC');
676 fnd_message.set_token('GL_CODE_COMBINATION_ID',l_gl_code_combination_id);
677 hr_utility.raise_error;
678 END IF;
679 CLOSE gl_code_combination_csr;
680 EXCEPTION
681 WHEN app_exception.application_exception THEN
682 IF hr_multi_message.exception_add(p_associated_column1 => 'PSP_EFFORT_INTERFACE.GL_CODE_COMBINATION') THEN
683 RAISE;
684 END IF;
685 END chk_gl_code_combination;
686
687
688
689 --
690 -- ----------------------------------------------------------------------------
691 -- |---------------------------< chk_project_id >----------------------------|
692 -- ----------------------------------------------------------------------------
693 PROCEDURE chk_project_id
694 ( p_project_id IN NUMBER
695 , p_distribution_date IN DATE)
696 IS
697 --
698 l_proc VARCHAR2(72) := g_package||'chk_project_id';
699 l_project_id NUMBER;
700 --
701 CURSOR project_id_csr(p_project_id IN NUMBER, p_distribution_date IN DATE) IS
702 SELECT project_id
703 FROM pa_projects_all
704 WHERE project_id = p_project_id
705 AND p_distribution_date between nvl(start_date,trunc(p_distribution_date)) and nvl(completion_date,to_date('31/12/4712','DD/MM/RRRR'));
706 BEGIN
707 --
708 -- Check mandatory parameters have been set
709 --
710 hr_api.mandatory_arg_error
711 (p_api_name => l_proc
712 ,p_argument => 'project id'
713 ,p_argument_value => p_project_id
714 );
715
716 hr_api.mandatory_arg_error
717 (p_api_name => l_proc
718 ,p_argument => 'distribution date'
719 ,p_argument_value => p_distribution_date
720 );
721
722 OPEN project_id_csr(p_project_id, p_distribution_date);
723 FETCH project_id_csr INTO l_project_id;
724 if project_id_csr%notfound then
725 CLOSE project_id_csr;
726 hr_utility.set_message(8403, 'PSP_ER_INVALID_PROJECT_ID');
727 fnd_message.set_token('PROJECT_ID',l_project_id);
728 hr_utility.raise_error;
729 END IF;
730 CLOSE project_id_csr;
731 EXCEPTION
732 WHEN app_exception.application_exception THEN
733 IF hr_multi_message.exception_add(p_associated_column1 => 'PSP_EFFORT_INTERFACE.PROJECT_ID') THEN
734 RAISE;
735 END IF;
736 END chk_project_id;
737
738
739 --
740 -- ----------------------------------------------------------------------------
741 -- |---------------------------< chk_task_id >----------------------------|
742 -- ----------------------------------------------------------------------------
743 PROCEDURE chk_task_id
744 ( p_task_id IN NUMBER
745 , p_distribution_date IN DATE)
746 IS
747 --
748 l_proc VARCHAR2(72) := g_package||'chk_task_id';
749 l_task_id NUMBER;
750 --
751 CURSOR task_id_csr(p_task_id IN NUMBER, p_distribution_date IN DATE) IS
752 SELECT task_id
753 FROM pa_tasks
754 WHERE task_id = p_task_id
755 AND p_distribution_date between nvl(start_date,trunc(p_distribution_date)) and nvl(completion_date,to_date('31/12/4712','DD/MM/RRRR'));
756 BEGIN
757 --
758 -- Check mandatory parameters have been set
759 --
760 hr_api.mandatory_arg_error
761 (p_api_name => l_proc
762 ,p_argument => 'task id'
763 ,p_argument_value => p_task_id
764 );
765
766 hr_api.mandatory_arg_error
767 (p_api_name => l_proc
768 ,p_argument => 'distribution date'
769 ,p_argument_value => p_distribution_date
770 );
771
772 OPEN task_id_csr(p_task_id, p_distribution_date);
773 FETCH task_id_csr INTO l_task_id;
774 if task_id_csr%notfound then
775 CLOSE task_id_csr;
776 hr_utility.set_message(8403, 'PSP_ER_INVALID_TASK_ID');
777 fnd_message.set_token('TASK_ID',l_task_id);
778 hr_utility.raise_error;
779 END IF;
780 CLOSE task_id_csr;
781 EXCEPTION
782 WHEN app_exception.application_exception THEN
783 IF hr_multi_message.exception_add(p_associated_column1 => 'PSP_EFFORT_INTERFACE.TASK_ID') THEN
784 RAISE;
785 END IF;
786 END chk_task_id;
787
788
789 --
790 -- ----------------------------------------------------------------------------
791 -- |---------------------------< chk_award_id >----------------------------|
792 -- ----------------------------------------------------------------------------
793 PROCEDURE chk_award_id
794 ( p_award_id IN NUMBER
795 , p_distribution_date IN DATE)
796 IS
797 --
798 l_proc VARCHAR2(72) := g_package||'chk_award_id';
799 l_award_id NUMBER;
800 --
801 CURSOR award_id_csr(p_award_id IN NUMBER, p_distribution_date IN DATE) IS
802 SELECT award_id
803 FROM gms_awards_all
804 WHERE award_id = p_award_id
805 AND p_distribution_date between nvl(start_date_active,trunc(p_distribution_date)) and nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR'));
806 BEGIN
807 --
808 -- Check mandatory parameters have been set
809 --
810 hr_api.mandatory_arg_error
811 (p_api_name => l_proc
812 ,p_argument => 'award id'
813 ,p_argument_value => p_award_id
814 );
815
816 hr_api.mandatory_arg_error
817 (p_api_name => l_proc
818 ,p_argument => 'distribution date'
819 ,p_argument_value => p_distribution_date
820 );
821
822 OPEN award_id_csr(p_award_id, p_distribution_date);
823 FETCH award_id_csr INTO l_award_id;
824 if award_id_csr%notfound then
825 CLOSE award_id_csr;
826 hr_utility.set_message(8403, 'PSP_ER_INVALID_AWARD_ID');
827 fnd_message.set_token('AWARD_ID',l_award_id);
828 hr_utility.raise_error;
829 END IF;
830 CLOSE award_id_csr;
831 EXCEPTION
832 WHEN app_exception.application_exception THEN
833 IF hr_multi_message.exception_add(p_associated_column1 => 'PSP_EFFORT_INTERFACE.AWARD_ID') THEN
834 RAISE;
835 END IF;
836 END chk_award_id;
837
838
839 --
840 -- ----------------------------------------------------------------------------
841 -- |---------------------------< chk_exp_org_id >----------------------------|
842 -- ----------------------------------------------------------------------------
843 PROCEDURE chk_exp_org_id
844 ( p_expenditure_organization_id IN NUMBER
845 , p_distribution_date IN DATE)
846 IS
847 --
848 l_proc VARCHAR2(72) := g_package||'chk_exp_org_id';
849 l_expenditure_organization_id NUMBER;
850
851 --
852 CURSOR expenditure_organization_csr(p_expenditure_organization_id IN NUMBER, p_distribution_date IN DATE) IS
853 SELECT organization_id
854 FROM psp_organizations_expend_v
855 WHERE organization_id = p_expenditure_organization_id
856 AND trunc(p_distribution_date) between date_from and nvl(date_to,trunc(p_distribution_date));
857 BEGIN
858 --
859 -- Check mandatory parameters have been set
860 --
861 hr_api.mandatory_arg_error
862 (p_api_name => l_proc
863 ,p_argument => 'expenditure organization id'
864 ,p_argument_value => p_expenditure_organization_id
865 );
866
867 hr_api.mandatory_arg_error
868 (p_api_name => l_proc
869 ,p_argument => 'distribution date'
870 ,p_argument_value => p_distribution_date
871 );
872
873 OPEN expenditure_organization_csr(p_expenditure_organization_id, p_distribution_date);
874 FETCH expenditure_organization_csr INTO l_expenditure_organization_id;
875 if expenditure_organization_csr%notfound then
876 CLOSE expenditure_organization_csr;
877 hr_utility.set_message(8403, 'PSP_ER_INVALID_EXP_ORG_ID');
878 fnd_message.set_token('EXP_ORG_ID',l_expenditure_organization_id);
879 hr_utility.raise_error;
880 END IF;
881 CLOSE expenditure_organization_csr;
882 EXCEPTION
883 WHEN app_exception.application_exception THEN
884 IF hr_multi_message.exception_add(p_associated_column1 => 'PSP_EFFORT_INTERFACE.expenditure_organization_id') THEN
885 RAISE;
886 END IF;
887 END chk_exp_org_id;
888
889
890
891
892
893
894
895 --
896 -- ----------------------------------------------------------------------------
897 -- |---------------------------< chk_existing_eff_report >----------------------------|
898 -- ----------------------------------------------------------------------------
899 PROCEDURE chk_existing_eff_report
900 ( p_person_id IN NUMBER
901 , p_assignment_id IN NUMBER
902 , p_distribution_date IN DATE
903 )
904 IS
905 CURSOR person_effort_report_csr(p_person_id IN NUMBER, p_distribution_date IN DATE) IS
906 SELECT per.effort_report_id, prth.selection_match_level
907 FROM psp_eff_reports per,
908 psp_report_templates_h prth
909 WHERE per.request_id = prth.request_id
910 AND per.PERSON_ID = p_person_id
911 AND p_distribution_date between per.start_date and per.end_date
912 AND per.STATUS_CODE IN ('N','A');
913
914 CURSOR assignment_effort_report_csr (p_effort_report_id IN NUMBER, p_assignment_id IN NUMBER) IS
915 SELECT assignment_id
916 FROM psp_eff_report_details perd
917 WHERE effort_report_id = p_effort_report_id
918 AND ASSIGNMENT_ID = p_assignment_id;
919 --
920 l_effort_report_id NUMBER;
921 l_selection_match_level VARCHAR2(30);
922 l_assignment_id NUMBER;
923 --
924 person_effort_report_exist EXCEPTION;
925 assignment_effort_report_exist EXCEPTION;
926
927 BEGIN
928 -- If an Effort Report already exists for the person and overlapping date range
929 OPEN person_effort_report_csr(p_person_id, p_distribution_date);
930 FETCH person_effort_report_csr INTO l_effort_report_id, l_selection_match_level;
931 CLOSE person_effort_report_csr;
932
933 IF l_effort_report_id IS NOT NULL THEN
934 IF l_selection_match_level = 'EMP' THEN
935 RAISE person_effort_report_exist;
936 ELSIF l_selection_match_level = 'ASG' THEN
937 OPEN assignment_effort_report_csr(l_effort_report_id, p_assignment_id);
938 FETCH assignment_effort_report_csr INTO l_assignment_id;
939 CLOSE assignment_effort_report_csr;
940 IF l_assignment_id IS NOT NULL THEN
941 RAISE assignment_effort_report_exist;
942 END IF;
943 END IF;
944 END IF;
945 EXCEPTION
946 WHEN person_effort_report_exist THEN
947 fnd_message.set_name('PSP', 'PSP_ER_PERSON_EFF_REPORT_EXIST');
948 fnd_message.set_token('PERSON_ID',p_person_id);
949 fnd_message.set_token('DISTRIBUTION_DATE',p_distribution_date);
950 fnd_message.raise_error;
951 WHEN assignment_effort_report_exist THEN
952 fnd_message.set_name('PSP', 'PSP_ER_ASSGN_EFF_REPORT_EXIST');
953 fnd_message.set_token('PERSON_ID',p_person_id);
954 fnd_message.set_token('ASSIGNMENT_ID',p_assignment_id);
955 fnd_message.set_token('DISTRIBUTION_DATE',p_distribution_date);
956 fnd_message.raise_error;
957 END chk_existing_eff_report;
958
959 --
960 -- ----------------------------------------------------------------------------
961 -- |---------------------------< chk_record_validity >----------------------------|
962 -- ----------------------------------------------------------------------------
963 PROCEDURE chk_record_validity
964 (p_rec IN psp_pee_shd.g_rec_type
965 ) IS
966 --
967 /*
968 CURSOR person_effort_report_csr(p_person_id IN NUMBER, p_distribution_date IN DATE) IS
969 SELECT per.effort_report_id, prth.selection_match_level
970 FROM psp_eff_reports per,
971 psp_report_templates_h prth
972 WHERE per.request_id = prth.request_id
973 AND per.PERSON_ID = p_person_id
974 AND p_distribution_date between per.start_date and per.end_date
975 AND per.STATUS_CODE IN ('N','A');
976
977 CURSOR assignment_effort_report_csr (p_effort_report_id IN NUMBER, p_assignment_id IN NUMBER) IS
978 SELECT assignment_id
979 FROM psp_eff_report_details perd
980 WHERE effort_report_id = p_effort_report_id
981 AND ASSIGNMENT_ID = p_assignment_id;
982 */
983 CURSOR business_group_csr(p_business_group_id IN NUMBER, p_distribution_date IN DATE) IS
984 SELECT business_group_id
985 FROM per_business_groups
986 where business_group_id = p_business_group_id
987 AND p_distribution_date between DATE_FROM and nvl(DATE_TO,to_date('31/12/4712','DD/MM/RRRR'));
988
989 CURSOR set_of_books_csr(p_set_of_books_id IN NUMBER) IS
990 SELECT set_of_books_id
991 FROM gl_sets_of_books
992 WHERE set_of_books_id = p_set_of_books_id;
993
994 CURSOR person_csr(p_person_id IN NUMBER, p_business_group_id IN NUMBER, p_distribution_date IN DATE) IS
995 SELECT person_id
996 FROM per_all_people_f
997 WHERE person_id = p_person_id
998 AND current_employee_flag = 'Y'
999 AND business_group_id = p_business_group_id
1000 AND p_distribution_date between effective_start_date and effective_end_date;
1001
1002 CURSOR assignment_csr(p_assignment_id IN NUMBER, p_person_id IN NUMBER, p_business_group_id IN NUMBER, p_distribution_date IN DATE) IS
1003 SELECT assignment_id
1004 FROM per_all_assignments_f paaf,
1005 pay_all_payrolls_f papf
1006 WHERE paaf.payroll_id = papf.payroll_id
1007 AND paaf.assignment_id = p_assignment_id
1008 AND paaf.person_id = p_person_id
1009 AND paaf.business_group_id = p_business_group_id
1010 AND p_distribution_date between paaf.effective_start_date and paaf.effective_end_date
1011 AND p_distribution_date between papf.effective_start_date and papf.effective_end_date;
1012
1013 CURSOR currency_csr (p_currency_code IN VARCHAR2, p_distribution_date IN DATE )IS
1014 SELECT fc.currency_code
1015 FROM fnd_currencies fc,
1016 per_business_groups pbg
1017 WHERE fc.currency_code =pbg.currency_code
1018 AND fc.enabled_flag = 'Y'
1019 AND fc.currency_flag = 'Y'
1020 AND fc.currency_code = p_currency_code
1021 AND p_distribution_date between nvl(fc.start_date_active,p_distribution_date) and nvl(fc.end_date_active ,to_date('31/12/4712','DD/MM/RRRR'))
1022 AND p_distribution_date between date_from and nvl(pbg.DATE_TO,to_date('31/12/4712','DD/MM/RRRR'));
1023
1024 CURSOR gl_code_combination_csr (p_gl_code_combination_id IN NUMBER) IS
1025 SELECT code_combination_id
1026 FROM gl_code_combinations
1027 WHERE code_combination_id = p_gl_code_combination_id
1028 AND enabled_flag ='Y';
1029
1030 CURSOR project_csr(p_project_id IN NUMBER, p_distribution_date IN DATE) IS
1031 SELECT project_id
1032 FROM pa_projects_all
1033 WHERE project_id = p_project_id
1034 AND p_distribution_date between nvl(start_date,trunc(p_distribution_date)) and nvl(completion_date,to_date('31/12/4712','DD/MM/RRRR'));
1035
1036 CURSOR task_csr(p_task_id IN NUMBER, p_distribution_date IN DATE) IS
1037 SELECT task_id
1038 FROM pa_tasks
1039 WHERE task_id = p_task_id
1040 AND p_distribution_date between nvl(start_date,trunc(p_distribution_date)) and nvl(completion_date,to_date('31/12/4712','DD/MM/RRRR'));
1041
1042 CURSOR award_csr(p_award_id IN NUMBER, p_distribution_date IN DATE) IS
1043 SELECT award_id
1044 FROM gms_awards_all
1045 WHERE award_id = p_award_id
1046 AND p_distribution_date between nvl(start_date_active,trunc(p_distribution_date)) and nvl(end_date_active,to_date('31/12/4712','DD/MM/RRRR'));
1047
1048 CURSOR expenditure_organization_csr(p_expenditure_organization_id IN NUMBER, p_distribution_date IN DATE) IS
1049 SELECT organization_id
1050 FROM psp_organizations_expend_v
1051 WHERE organization_id = p_expenditure_organization_id
1052 AND trunc(p_distribution_date) between date_from and nvl(date_to,trunc(p_distribution_date));
1053
1054 --
1055 l_effort_report_id NUMBER;
1056 l_selection_match_level VARCHAR2(30);
1057 l_assignment_id_temp NUMBER;
1058 l_business_group_id NUMBER;
1059 l_set_of_books_id NUMBER;
1060 l_person_id NUMBER;
1061 l_assignment_id NUMBER;
1062 l_currency_code VARCHAR2(15);
1063 l_project_id NUMBER;
1064 l_task_id NUMBER;
1065 l_award_id NUMBER;
1066 l_expenditure_organization_id NUMBER;
1067 l_gl_code_combination_id NUMBER;
1068 --
1069 /*
1070 person_effort_report_exist EXCEPTION;
1071 assignment_effort_report_exist EXCEPTION;
1072 */
1073 invalid_business_group EXCEPTION;
1074 invalid_set_of_books EXCEPTION;
1075 invalid_person EXCEPTION;
1076 invalid_assignment EXCEPTION;
1077 invalid_currency EXCEPTION;
1078 invalid_gl_code_combination EXCEPTION;
1079 invalid_project EXCEPTION;
1080 invalid_task EXCEPTION;
1081 invalid_award EXCEPTION;
1082 invalid_exp_org EXCEPTION;
1083 no_charging_instruction EXCEPTION;
1084 extra_charging_instruction EXCEPTION;
1085
1086 --
1087 BEGIN
1088 /*
1089 -- If an Effort Report already exists for the person and overlapping date range
1090 OPEN person_effort_report_csr(p_rec.person_id, p_rec.distribution_date);
1091 FETCH person_effort_report_csr INTO l_effort_report_id, l_selection_match_level;
1092 CLOSE person_effort_report_csr;
1093
1094 IF l_effort_report_id IS NOT NULL THEN
1095 IF l_selection_match_level = 'EMP' THEN
1096 RAISE person_effort_report_exist;
1097 ELSIF l_selection_match_level = 'ASG' THEN
1098 OPEN assignment_effort_report_csr(l_effort_report_id, p_rec.assignment_id);
1099 FETCH assignment_effort_report_csr INTO l_assignment_id_temp;
1100 CLOSE assignment_effort_report_csr;
1101 IF l_assignment_id IS NOT NULL THEN
1102 RAISE assignment_effort_report_exist;
1103 END IF;
1104 END IF;
1105 END IF;
1106 */
1107
1108 chk_existing_eff_report(p_rec.person_id, p_rec.assignment_id, p_rec.distribution_date);
1109 -- If Business_group is invalid
1110 OPEN business_group_csr (p_rec.business_group_id, p_rec.distribution_date);
1111 FETCH business_group_csr INTO l_business_group_id;
1112 CLOSE business_group_csr;
1113 IF l_business_group_id IS NULL THEN
1114 RAISE invalid_business_group;
1115 END IF;
1116
1117 -- If set of books is invalid
1118 OPEN set_of_books_csr(p_rec.set_of_books_id);
1119 FETCH set_of_books_csr INTO l_set_of_books_id;
1120 CLOSE set_of_books_csr;
1121 IF l_set_of_books_id IS NULL THEN
1122 RAISE invalid_set_of_books;
1123 END IF;
1124
1125 -- If person is invalid
1126 OPEN person_csr(p_rec.person_id, p_rec.business_group_id, p_rec.distribution_date);
1127 FETCH person_csr INTO l_person_id;
1128 CLOSE person_csr;
1129 IF l_person_id IS NULL THEN
1130 RAISE invalid_person;
1131 END IF;
1132
1133 -- If assignment is invalid
1134 OPEN assignment_csr (p_rec.assignment_id, p_rec.person_id, p_rec.business_group_id, p_rec.distribution_date);
1135 FETCH assignment_csr INTO l_assignment_id;
1136 CLOSE assignment_csr;
1137 IF l_assignment_id IS NULL THEN
1138 RAISE invalid_assignment;
1139 END IF;
1140
1141 -- If currency is invalid
1142 OPEN currency_csr(p_rec.currency_code, p_rec.distribution_date);
1143 FETCH currency_csr INTO l_currency_code;
1144 CLOSE currency_csr;
1145 IF l_currency_code IS NULL THEN
1146 RAISE invalid_currency;
1147 END IF;
1148
1149
1150 -- Check PTAOE or GL
1151 IF p_rec.project_id IS NULL AND p_rec.gl_code_combination_id IS NULL THEN
1152 RAISE no_charging_instruction;
1153 END IF;
1154
1155 IF p_rec.gl_code_combination_id IS NOT NULL
1156 AND (p_rec.project_id IS NOT NULL
1157 OR p_rec.task_id IS NOT NULL
1158 OR p_rec.award_id IS NOT NULL
1159 OR p_rec.expenditure_organization_id IS NOT NULL
1160 OR p_rec.expenditure_type IS NOT NULL
1161 )
1162 THEN
1163 RAISE extra_charging_instruction;
1164 END IF;
1165
1166 IF p_rec.gl_code_combination_id IS NOT NULL THEN
1167 -- If glccid is invalid
1168 OPEN gl_code_combination_csr(p_rec.gl_code_combination_id);
1169 FETCH gl_code_combination_csr INTO l_gl_code_combination_id;
1170 CLOSE gl_code_combination_csr;
1171 IF l_gl_code_combination_id IS NULL THEN
1172 RAISE invalid_gl_code_combination;
1173 END IF;
1174 ELSIF p_rec.project_id IS NOT NULL THEN
1175 -- If Project is invalid
1176 OPEN project_csr(p_rec.project_id, p_rec.distribution_date);
1177 FETCH project_csr into l_project_id;
1178 CLOSE project_csr;
1179 IF l_project_id IS NULL THEN
1180 RAISE invalid_project;
1181 END IF;
1182
1183 -- If task is invalid
1184 IF p_rec.task_id IS NOT NULL THEN
1185 OPEN task_csr(p_rec.task_id, p_rec.distribution_date);
1186 FETCH task_csr into l_task_id;
1187 CLOSE task_csr;
1188 IF l_task_id IS NULL THEN
1189 RAISE invalid_task;
1190 END IF;
1191 END IF;
1192
1193 -- If award is invalid
1194 IF p_rec.award_id IS NOT NULL THEN
1195 OPEN award_csr(p_rec.award_id, p_rec.distribution_date);
1196 FETCH award_csr into l_award_id;
1197 CLOSE award_csr;
1198 IF l_award_id IS NULL THEN
1199 RAISE invalid_award;
1200 END IF;
1201 END IF;
1202
1203 -- If expenditure_organization is invalid
1204 IF p_rec.expenditure_organization_id IS NOT NULL THEN
1205 OPEN expenditure_organization_csr(p_rec.expenditure_organization_id, p_rec.distribution_date);
1206 FETCH expenditure_organization_csr into l_expenditure_organization_id;
1207 CLOSE expenditure_organization_csr;
1208 IF l_expenditure_organization_id IS NULL THEN
1209 RAISE invalid_exp_org;
1210 END IF;
1211 END IF;
1212
1213 END IF;
1214
1215
1216 EXCEPTION
1217 /*
1218 WHEN person_effort_report_exist THEN
1219 fnd_message.set_name('PSP', 'PSP_ER_PERSON_EFF_REPORT_EXIST');
1220 fnd_message.set_token('PERSON_ID',p_rec.person_id);
1221 fnd_message.set_token('DISTRIBUTION_DATE',p_rec.distribution_date);
1222 fnd_message.raise_error;
1223 WHEN assignment_effort_report_exist THEN
1224 fnd_message.set_name('PSP', 'PSP_ER_ASSGN_EFF_REPORT_EXIST');
1225 fnd_message.set_token('PERSON_ID',p_rec.person_id);
1226 fnd_message.set_token('ASSIGNMENt_ID',p_rec.assignment_id);
1227 fnd_message.set_token('DISTRIBUTION_DATE',p_rec.distribution_date);
1228 fnd_message.raise_error;
1229 */
1230 WHEN invalid_business_group THEN
1231 fnd_message.set_name('PSP', 'PSP_ER_INVALID_BUSINESS_GROUP');
1232 fnd_message.set_token('BUSINESS_GROUP_ID',p_rec.business_group_id);
1233 fnd_message.raise_error;
1234 WHEN invalid_set_of_books THEN
1235 fnd_message.set_name('PSP', 'PSP_ER_INVALID_SET_OF_BOOKS');
1236 fnd_message.set_token('SET_OF_BOOKS_ID',p_rec.set_of_books_id);
1237 fnd_message.raise_error;
1238 WHEN invalid_person THEN
1239 fnd_message.set_name('PSP', 'PSP_ER_INVALID_PERSON');
1240 fnd_message.set_token('PERSON_ID',p_rec.person_id);
1241 fnd_message.raise_error;
1242 WHEN invalid_assignment THEN
1243 fnd_message.set_name('PSP', 'PSP_ER_INVALID_ASSIGNMENT');
1244 fnd_message.set_token('ASSIGNMENT_ID',p_rec.assignment_id);
1245 fnd_message.raise_error;
1246 WHEN invalid_currency THEN
1247 fnd_message.set_name('PSP', 'PSP_ER_INVALID_CURRENCY');
1248 fnd_message.set_token('CURRENCY_CODE',p_rec.currency_code);
1249 fnd_message.raise_error;
1250 WHEN invalid_project THEN
1251 fnd_message.set_name('PSP', 'PSP_ER_INVALID_PROJECT');
1252 fnd_message.set_token('PROJECT_ID',p_rec.project_id);
1253 fnd_message.raise_error;
1254 WHEN invalid_task THEN
1255 fnd_message.set_name('PSP', 'PSP_ER_INVALID_TASK');
1256 fnd_message.set_token('TASK_ID',p_rec.task_id);
1257 fnd_message.raise_error;
1258 WHEN invalid_award THEN
1259 fnd_message.set_name('PSP', 'PSP_ER_INVALID_AWARD');
1260 fnd_message.set_token('AWARD_ID',p_rec.award_id);
1261 fnd_message.raise_error;
1262 WHEN invalid_exp_org THEN
1263 fnd_message.set_name('PSP', 'PSP_ER_INVALID_EXP_ORG');
1264 fnd_message.set_token('EXPENDITURE_ORGANIZATION_ID',p_rec.expenditure_organization_id);
1265 fnd_message.raise_error;
1266 WHEN extra_charging_instruction THEN
1267 fnd_message.set_name('PSP', 'PSP_ER_EXTRA_CI');
1268 fnd_message.set_token('EXPENDITURE_ORGANIZATION_ID',p_rec.expenditure_organization_id);
1269 fnd_message.raise_error;
1270 WHEN no_charging_instruction THEN
1271 fnd_message.set_name('PSP', 'PSP_POETA_GL');
1272 fnd_message.raise_error;
1273 END chk_record_validity;
1274
1275
1276
1277 --
1278 -- ----------------------------------------------------------------------------
1279 -- |---------------------------< insert_validate >----------------------------|
1280 -- ----------------------------------------------------------------------------
1281 Procedure insert_validate
1282 (p_rec in psp_pee_shd.g_rec_type
1283 ) is
1284 --
1285 l_proc varchar2(72) := g_package||'insert_validate';
1286 --
1287 Begin
1288 hr_utility.set_location('Entering:'||l_proc, 5);
1289 --
1290 -- Call all supporting business operations
1291 --
1292
1293
1294 hr_api.validate_bus_grp_id
1295 (p_business_group_id => p_rec.business_group_id
1296 ,p_associated_column1 => psp_pee_shd.g_tab_nam
1297 || '.BUSINESS_GROUP_ID');
1298 --
1299 -- After validating the set of important attributes,
1300 -- if Multiple Message detection is enabled and at least
1301 -- one error has been found then abort further validation.
1302 --
1303 hr_multi_message.end_validation_set;
1304 --
1305 -- Validate Dependent Attributes
1306 --
1307 --
1308 chk_set_of_books_id (p_set_of_books_id => p_rec.set_of_books_id );
1309
1310 chk_person_id
1311 ( p_person_id => p_rec.person_id
1312 , p_business_group_id => p_rec.business_group_id
1313 , p_distribution_date => p_rec.distribution_date );
1314
1315
1316 chk_assignment_id
1317 ( p_assignment_id => p_rec.assignment_id
1318 , p_person_id => p_rec.person_id
1319 , p_business_group_id => p_rec.business_group_id
1320 , p_distribution_date => p_rec.distribution_date );
1321
1322
1323 chk_existing_eff_report
1324 ( p_person_id => p_rec.person_id
1325 , p_assignment_id => p_rec.assignment_id
1326 , p_distribution_date => p_rec.distribution_date );
1327
1328
1329 chk_currency
1330 ( p_currency_code => p_rec.currency_code
1331 , p_distribution_date => p_rec.distribution_date );
1332
1333 chk_ptaoe_gl_combination(p_rec);
1334
1335 IF p_rec.gl_code_combination_id IS NOT NULL THEN
1336 chk_gl_code_combination ( p_gl_code_combination_id => p_rec.gl_code_combination_id );
1337 END IF;
1338
1339 IF p_rec.project_id IS NOT NULL THEN
1340 chk_project_id
1341 ( p_project_id => p_rec.project_id
1342 , p_distribution_date => p_rec.distribution_date );
1343 END IF;
1344
1345 IF p_rec.task_id IS NOT NULL THEN
1346 chk_task_id
1347 ( p_task_id => p_rec.task_id
1348 , p_distribution_date => p_rec.distribution_date );
1349 END IF;
1350
1351 IF p_rec.award_id IS NOT NULL THEN
1352 chk_award_id
1353 ( p_award_id => p_rec.award_id
1354 , p_distribution_date => p_rec.distribution_date );
1355 END IF;
1356
1357
1358 IF p_rec.expenditure_organization_id IS NOT NULL THEN
1359 chk_exp_org_id
1360 ( p_expenditure_organization_id => p_rec.expenditure_organization_id
1361 , p_distribution_date => p_rec.distribution_date );
1362 END IF;
1363
1364 psp_pee_bus.chk_df(p_rec);
1365 --
1366 hr_utility.set_location(' Leaving:'||l_proc, 10);
1367 End insert_validate;
1368 --
1369 -- ----------------------------------------------------------------------------
1370 -- |---------------------------< update_validate >----------------------------|
1371 -- ----------------------------------------------------------------------------
1372 Procedure update_validate
1373 (p_rec in psp_pee_shd.g_rec_type
1374 ) is
1375 --
1376 l_proc varchar2(72) := g_package||'update_validate';
1377 --
1378 Begin
1379 hr_utility.set_location('Entering:'||l_proc, 5);
1380 --
1381 -- Call all supporting business operations
1382 --
1383 hr_api.validate_bus_grp_id
1384 (p_business_group_id => p_rec.business_group_id
1385 ,p_associated_column1 => psp_pee_shd.g_tab_nam
1386 || '.BUSINESS_GROUP_ID');
1387
1388 --
1389 -- After validating the set of important attributes,
1390 -- if Multiple Message detection is enabled and at least
1391 -- one error has been found then abort further validation.
1392 --
1393 hr_multi_message.end_validation_set;
1394 --
1395 -- Validate Dependent Attributes
1396 --
1397 --
1398 chk_set_of_books_id (p_set_of_books_id => p_rec.set_of_books_id );
1399
1400 chk_person_id
1401 ( p_person_id => p_rec.person_id
1402 , p_business_group_id => p_rec.business_group_id
1403 , p_distribution_date => p_rec.distribution_date );
1404
1405
1406 chk_assignment_id
1407 ( p_assignment_id => p_rec.assignment_id
1408 , p_person_id => p_rec.person_id
1409 , p_business_group_id => p_rec.business_group_id
1410 , p_distribution_date => p_rec.distribution_date );
1411
1412
1413 chk_existing_eff_report
1414 ( p_person_id => p_rec.person_id
1415 , p_assignment_id => p_rec.assignment_id
1416 , p_distribution_date => p_rec.distribution_date );
1417
1418
1419 chk_currency
1420 ( p_currency_code => p_rec.currency_code
1421 , p_distribution_date => p_rec.distribution_date );
1422
1423 chk_ptaoe_gl_combination(p_rec);
1424
1425 IF p_rec.gl_code_combination_id IS NOT NULL THEN
1426 chk_gl_code_combination ( p_gl_code_combination_id => p_rec.gl_code_combination_id );
1427 END IF;
1428
1429 IF p_rec.project_id IS NOT NULL THEN
1430 chk_project_id
1431 ( p_project_id => p_rec.project_id
1432 , p_distribution_date => p_rec.distribution_date );
1433 END IF;
1434
1435 IF p_rec.task_id IS NOT NULL THEN
1436 chk_task_id
1437 ( p_task_id => p_rec.task_id
1438 , p_distribution_date => p_rec.distribution_date );
1439 END IF;
1440
1441 IF p_rec.award_id IS NOT NULL THEN
1442 chk_award_id
1443 ( p_award_id => p_rec.award_id
1444 , p_distribution_date => p_rec.distribution_date );
1445 END IF;
1446
1447
1448 IF p_rec.expenditure_organization_id IS NOT NULL THEN
1449 chk_exp_org_id
1450 ( p_expenditure_organization_id => p_rec.expenditure_organization_id
1451 , p_distribution_date => p_rec.distribution_date );
1452 END IF;
1453
1454 chk_non_updateable_args
1455 (p_rec => p_rec
1456 );
1457 --
1458 --
1459 psp_pee_bus.chk_df(p_rec);
1460 --
1461 hr_utility.set_location(' Leaving:'||l_proc, 10);
1462 End update_validate;
1463 --
1464 -- ----------------------------------------------------------------------------
1465 -- |---------------------------< delete_validate >----------------------------|
1466 -- ----------------------------------------------------------------------------
1467 Procedure delete_validate
1468 (p_rec in psp_pee_shd.g_rec_type
1469 ) is
1470 --
1471 l_proc varchar2(72) := g_package||'delete_validate';
1472 --
1473 Begin
1474 hr_utility.set_location('Entering:'||l_proc, 5);
1475 --
1476 -- Call all supporting business operations
1477 --
1478 chk_existing_eff_report(p_rec.person_id, p_rec.assignment_id, p_rec.distribution_date);
1479
1480
1481 hr_utility.set_location(' Leaving:'||l_proc, 10);
1482 End delete_validate;
1483 --
1484 end psp_pee_bus;
[Home] [Help]