[Home] [Help]
31: l_error exception;
32: l_argument varchar2(30);
33: --
34: Begin
35: hr_utility.set_location('Entering:'||l_proc, 5);
36: --
37: -- Only proceed with validation if a row exists for
38: -- the current record in the HR Schema
39: --
40: if not per_par_shd.api_updating
41: (p_participant_id => p_rec.participant_id
42: ,p_object_version_number => p_rec.object_version_number
43: ) then
44: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
45: hr_utility.set_message_token('PROCEDURE', l_proc);
46: hr_utility.set_message_token('STEP', '5');
47: end if;
48: --
41: (p_participant_id => p_rec.participant_id
42: ,p_object_version_number => p_rec.object_version_number
43: ) then
44: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
45: hr_utility.set_message_token('PROCEDURE', l_proc);
46: hr_utility.set_message_token('STEP', '5');
47: end if;
48: --
49: hr_utility.set_location(l_proc, 6);
42: ,p_object_version_number => p_rec.object_version_number
43: ) then
44: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
45: hr_utility.set_message_token('PROCEDURE', l_proc);
46: hr_utility.set_message_token('STEP', '5');
47: end if;
48: --
49: hr_utility.set_location(l_proc, 6);
50: --
45: hr_utility.set_message_token('PROCEDURE', l_proc);
46: hr_utility.set_message_token('STEP', '5');
47: end if;
48: --
49: hr_utility.set_location(l_proc, 6);
50: --
51: if p_rec.business_group_id <> per_par_shd.g_old_rec.business_group_id then
52: l_argument := 'business_group_id';
53: raise l_error;
54: -- elsif p_rec.questionnaire_template_id <> per_par_shd.g_old_rec.questionnaire_template_id then
55: -- l_argument := 'questionnaire_template_id';
56: -- raise l_error;
57: elsif p_rec.participation_in_table <> per_par_shd.g_old_rec.participation_in_table then
58: hr_utility.set_location(l_proc, 7);
59: l_argument := 'participation_in_table';
60: raise l_error;
61: elsif p_rec.participation_in_column <> per_par_shd.g_old_rec.participation_in_column then
62: hr_utility.set_location(l_proc, 8);
58: hr_utility.set_location(l_proc, 7);
59: l_argument := 'participation_in_table';
60: raise l_error;
61: elsif p_rec.participation_in_column <> per_par_shd.g_old_rec.participation_in_column then
62: hr_utility.set_location(l_proc, 8);
63: l_argument := 'participation_in_column';
64: raise l_error;
65: elsif p_rec.participation_in_id <> per_par_shd.g_old_rec.participation_in_id then
66: hr_utility.set_location(l_proc, 9);
62: hr_utility.set_location(l_proc, 8);
63: l_argument := 'participation_in_column';
64: raise l_error;
65: elsif p_rec.participation_in_id <> per_par_shd.g_old_rec.participation_in_id then
66: hr_utility.set_location(l_proc, 9);
67: l_argument := 'participation_in_id';
68: raise l_error;
69: end if;
70: hr_utility.set_location(l_proc, 11);
66: hr_utility.set_location(l_proc, 9);
67: l_argument := 'participation_in_id';
68: raise l_error;
69: end if;
70: hr_utility.set_location(l_proc, 11);
71: --
72: exception
73: when l_error then
74: hr_api.argument_changed_error
76: ,p_argument => l_argument
77: ,p_base_table => per_par_shd.g_tab_nam);
78: when others then
79: raise;
80: hr_utility.set_location(' Leaving:'||l_proc, 12);
81: end chk_non_updateable_args;
82: --
83: -- ----------------------------------------------------------------------------
84: -- |------------------------< chk_update_allowed >----------------------------|
118: l_proc varchar2(72) := g_package || 'chk_update_allowed';
119: --
120: begin
121: --
122: hr_utility.set_location('Entering: '||l_proc,10);
123: --
124: -- Check that the participation status is not closed.
125: --
126: -- if (p_rec.participation_status = 'CLOSED' or
234: fnd_message.raise_error;
235: end if;
236: end if;
237: --
238: hr_utility.set_location('Leaving: '||l_proc,20);
239: end chk_update_allowed;
240: --
241: -- ----------------------------------------------------------------------------
242: -- |---------------------< chk_participation_type >---------------------------|
283: l_api_updating boolean;
284: --
285: begin
286: --
287: hr_utility.set_location('Entering: '||l_proc,10);
288: --
289: l_api_updating := per_par_shd.api_updating
290: (p_participant_id => p_participant_id
291: ,p_object_version_number => p_object_version_number
290: (p_participant_id => p_participant_id
291: ,p_object_version_number => p_object_version_number
292: );
293: --
294: hr_utility.set_location(l_proc,20);
295: --
296: -- Check that update is allowed
297: --
298: if l_api_updating and (per_par_shd.g_old_rec.participation_type <> null) then
295: --
296: -- Check that update is allowed
297: --
298: if l_api_updating and (per_par_shd.g_old_rec.participation_type <> null) then
299: hr_utility.set_message(801,'PER_52466_PAR_TYPE_VAL_EXISTS');
300: hr_utility.raise_error;
301: else
302: if (p_participation_type <> null) and
303: (hr_api.not_exists_in_hr_lookups
296: -- Check that update is allowed
297: --
298: if l_api_updating and (per_par_shd.g_old_rec.participation_type <> null) then
299: hr_utility.set_message(801,'PER_52466_PAR_TYPE_VAL_EXISTS');
300: hr_utility.raise_error;
301: else
302: if (p_participation_type <> null) and
303: (hr_api.not_exists_in_hr_lookups
304: (p_effective_date => p_effective_date
305: ,p_lookup_type => 'PARTICIPATION_TYPE'
306: ,p_lookup_code => p_participation_type
307: )) then
308: -- p_participation_type does not exist in lookup, thus error.
309: hr_utility.set_message(800,'PER_52463_PAR_INVAL_PART_TYPE');
310: hr_utility.raise_error;
311: end if;
312: end if;
313: --
306: ,p_lookup_code => p_participation_type
307: )) then
308: -- p_participation_type does not exist in lookup, thus error.
309: hr_utility.set_message(800,'PER_52463_PAR_INVAL_PART_TYPE');
310: hr_utility.raise_error;
311: end if;
312: end if;
313: --
314: hr_utility.set_location('Leaving: '||l_proc,20);
310: hr_utility.raise_error;
311: end if;
312: end if;
313: --
314: hr_utility.set_location('Leaving: '||l_proc,20);
315: --
316: EXCEPTION
317: when app_exception.application_exception then
318: if hr_multi_message.exception_add
359: l_proc varchar2(72) := g_package || 'chk_participation_status';
360: --
361: begin
362: --
363: hr_utility.set_location('Entering: '||l_proc,10);
364: --
365: if (p_participation_status <> null) and
366: (hr_api.not_exists_in_hr_lookups
367: (p_effective_date => p_effective_date
368: ,p_lookup_type => 'PARTICIPANT_ACCESS'
369: ,p_lookup_code => p_participation_status
370: )) then
371: -- p_participation_status does not exist in lookup, thus error
372: hr_utility.set_message(800,'PER_52464_PAR_INVAL_PAR_STATUS');
373: hr_utility.raise_error;
374: end if;
375: --
376: hr_utility.set_location('Leaving: '||l_proc,20);
369: ,p_lookup_code => p_participation_status
370: )) then
371: -- p_participation_status does not exist in lookup, thus error
372: hr_utility.set_message(800,'PER_52464_PAR_INVAL_PAR_STATUS');
373: hr_utility.raise_error;
374: end if;
375: --
376: hr_utility.set_location('Leaving: '||l_proc,20);
377: --
372: hr_utility.set_message(800,'PER_52464_PAR_INVAL_PAR_STATUS');
373: hr_utility.raise_error;
374: end if;
375: --
376: hr_utility.set_location('Leaving: '||l_proc,20);
377: --
378: EXCEPTION
379: when app_exception.application_exception then
380: if hr_multi_message.exception_add
425: where table_name = upper(p_participation_in_table)
426: and owner = l_per_owner;
427:
428: begin
429: hr_utility.set_location('Entering:'|| l_proc, 1);
430: --
431: if (p_participation_in_table is NULL) then
432: hr_utility.set_message(801, 'HR_52064_PAR_TABLE_NULL');
433: hr_utility.raise_error;
428: begin
429: hr_utility.set_location('Entering:'|| l_proc, 1);
430: --
431: if (p_participation_in_table is NULL) then
432: hr_utility.set_message(801, 'HR_52064_PAR_TABLE_NULL');
433: hr_utility.raise_error;
434: end if;
435: --
436: hr_utility.set_location('Entering:'|| l_proc, 5);
429: hr_utility.set_location('Entering:'|| l_proc, 1);
430: --
431: if (p_participation_in_table is NULL) then
432: hr_utility.set_message(801, 'HR_52064_PAR_TABLE_NULL');
433: hr_utility.raise_error;
434: end if;
435: --
436: hr_utility.set_location('Entering:'|| l_proc, 5);
437: --
432: hr_utility.set_message(801, 'HR_52064_PAR_TABLE_NULL');
433: hr_utility.raise_error;
434: end if;
435: --
436: hr_utility.set_location('Entering:'|| l_proc, 5);
437: --
438: open chk_table_exists;
439: fetch chk_table_exists into l_exists;
440: if chk_table_exists%notfound then
438: open chk_table_exists;
439: fetch chk_table_exists into l_exists;
440: if chk_table_exists%notfound then
441: close chk_table_exists;
442: hr_utility.set_message(801,'HR_52065_PAR_INVALID_TABLE');
443: hr_utility.raise_error;
444: end if;
445: close chk_table_exists;
446: hr_utility.set_location(l_proc, 3);
439: fetch chk_table_exists into l_exists;
440: if chk_table_exists%notfound then
441: close chk_table_exists;
442: hr_utility.set_message(801,'HR_52065_PAR_INVALID_TABLE');
443: hr_utility.raise_error;
444: end if;
445: close chk_table_exists;
446: hr_utility.set_location(l_proc, 3);
447: --
442: hr_utility.set_message(801,'HR_52065_PAR_INVALID_TABLE');
443: hr_utility.raise_error;
444: end if;
445: close chk_table_exists;
446: hr_utility.set_location(l_proc, 3);
447: --
448: hr_utility.set_location('Leaving: '|| l_proc, 10);
449: EXCEPTION
450: when app_exception.application_exception then
444: end if;
445: close chk_table_exists;
446: hr_utility.set_location(l_proc, 3);
447: --
448: hr_utility.set_location('Leaving: '|| l_proc, 10);
449: EXCEPTION
450: when app_exception.application_exception then
451: if hr_multi_message.exception_add
452: (p_associated_column1 => 'PER_PARTICIPANTS.PARTICIPATION_IN_TABLE'
500: and column_name = upper(p_participation_in_column)
501: and owner = l_per_owner;
502:
503: begin
504: hr_utility.set_location('Entering:'|| l_proc, 1);
505: --
506: if (p_participation_in_column is NULL) then
507: hr_utility.set_message(801, 'HR_52066_PAR_COLUMN_NULL');
508: hr_utility.raise_error;
503: begin
504: hr_utility.set_location('Entering:'|| l_proc, 1);
505: --
506: if (p_participation_in_column is NULL) then
507: hr_utility.set_message(801, 'HR_52066_PAR_COLUMN_NULL');
508: hr_utility.raise_error;
509: end if;
510: --
511: open chk_column_exists;
504: hr_utility.set_location('Entering:'|| l_proc, 1);
505: --
506: if (p_participation_in_column is NULL) then
507: hr_utility.set_message(801, 'HR_52066_PAR_COLUMN_NULL');
508: hr_utility.raise_error;
509: end if;
510: --
511: open chk_column_exists;
512: fetch chk_column_exists into l_exists;
511: open chk_column_exists;
512: fetch chk_column_exists into l_exists;
513: if chk_column_exists%notfound then
514: close chk_column_exists;
515: hr_utility.set_message(801,'HR_52067_PAR_INVALID_COLUMN');
516: hr_utility.raise_error;
517: end if;
518: close chk_column_exists;
519: hr_utility.set_location(l_proc, 3);
512: fetch chk_column_exists into l_exists;
513: if chk_column_exists%notfound then
514: close chk_column_exists;
515: hr_utility.set_message(801,'HR_52067_PAR_INVALID_COLUMN');
516: hr_utility.raise_error;
517: end if;
518: close chk_column_exists;
519: hr_utility.set_location(l_proc, 3);
520: --
515: hr_utility.set_message(801,'HR_52067_PAR_INVALID_COLUMN');
516: hr_utility.raise_error;
517: end if;
518: close chk_column_exists;
519: hr_utility.set_location(l_proc, 3);
520: --
521: hr_utility.set_location('Leaving: '|| l_proc, 10);
522:
523: EXCEPTION
517: end if;
518: close chk_column_exists;
519: hr_utility.set_location(l_proc, 3);
520: --
521: hr_utility.set_location('Leaving: '|| l_proc, 10);
522:
523: EXCEPTION
524: when app_exception.application_exception then
525: if hr_multi_message.exception_add
587: 'from {p_participation_in_table} par ' ||
588: 'where par.{participation_in_column} = :p_participation_in_id ' ;
589: */
590: --
591: hr_utility.set_location('Entering:'|| l_proc, 1);
592: -- check if the participant_in_id is set as it is a mandatory
593: -- column, if not then error
594: if (p_participation_in_id is NULL) then
595: hr_utility.set_message(801, 'HR_52068_PAR_COLUMN_ID_NULL');
591: hr_utility.set_location('Entering:'|| l_proc, 1);
592: -- check if the participant_in_id is set as it is a mandatory
593: -- column, if not then error
594: if (p_participation_in_id is NULL) then
595: hr_utility.set_message(801, 'HR_52068_PAR_COLUMN_ID_NULL');
596: hr_utility.raise_error;
597: end if;
598: --
599: -- Check mandatory parameters have been set
592: -- check if the participant_in_id is set as it is a mandatory
593: -- column, if not then error
594: if (p_participation_in_id is NULL) then
595: hr_utility.set_message(801, 'HR_52068_PAR_COLUMN_ID_NULL');
596: hr_utility.raise_error;
597: end if;
598: --
599: -- Check mandatory parameters have been set
600: --
610: ,p_argument => 'participation_in_column'
611: ,p_argument_value => p_participation_in_column
612: );
613: --
614: hr_utility.set_location(l_proc, 2);
615: --
616: --
617: -- Dynamic literal string replacement
618: --
625: l_dynamic_sql := Replace(l_dynamic_sql, '{p_participation_in_column}',
626: p_participation_in_column);
627: */
628: --
629: hr_Utility.Set_Location(substr(L_DYNAMIC_SQL, 1,50), 4);
630: hr_Utility.Set_Location(substr(L_DYNAMIC_SQL, 51), 4);
631:
632: -- Dynamic sql steps:
633: -- ==================
626: p_participation_in_column);
627: */
628: --
629: hr_Utility.Set_Location(substr(L_DYNAMIC_SQL, 1,50), 4);
630: hr_Utility.Set_Location(substr(L_DYNAMIC_SQL, 51), 4);
631:
632: -- Dynamic sql steps:
633: -- ==================
634: -- 1. Open dynamic sql cursor
636: -- 3. Bind dynamic sql variables
637: -- 4. Define dynamic sql columns
638: -- 5. Execute and fetch dynamic sql
639: --
640: hr_Utility.Set_Location(l_proc, 6);
641: l_sql_cursor := dbms_sql.open_cursor; -- Step 1
642: --
643: hr_Utility.Set_Location(l_proc, 10);
644: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
639: --
640: hr_Utility.Set_Location(l_proc, 6);
641: l_sql_cursor := dbms_sql.open_cursor; -- Step 1
642: --
643: hr_Utility.Set_Location(l_proc, 10);
644: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
645: --
646: hr_Utility.Set_Location(l_proc, 15);
647: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
642: --
643: hr_Utility.Set_Location(l_proc, 10);
644: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
645: --
646: hr_Utility.Set_Location(l_proc, 15);
647: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
648: ':p_participation_in_id', p_participation_in_id);
649: --
650: hr_Utility.Set_Location(l_proc, 20);
646: hr_Utility.Set_Location(l_proc, 15);
647: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
648: ':p_participation_in_id', p_participation_in_id);
649: --
650: hr_Utility.Set_Location(l_proc, 20);
651: dbms_sql.define_column(l_sql_cursor, 1, p_business_group_id); -- Step 4
652: --
653: hr_Utility.Set_Location(l_proc, 30);
654: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
649: --
650: hr_Utility.Set_Location(l_proc, 20);
651: dbms_sql.define_column(l_sql_cursor, 1, p_business_group_id); -- Step 4
652: --
653: hr_Utility.Set_Location(l_proc, 30);
654: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
655: If (l_rows = 0 ) then
656: -- if no rows are returned that means the id does not exist
657: -- hence error
655: If (l_rows = 0 ) then
656: -- if no rows are returned that means the id does not exist
657: -- hence error
658: dbms_sql.close_cursor(l_sql_cursor);
659: hr_Utility.set_Location(l_proc, 35);
660: hr_utility.set_message(801,'HR_52069_PAR_INVALID_COLUMN_ID');
661: hr_utility.raise_error;
662: Else
663: -- check if the business groups match
656: -- if no rows are returned that means the id does not exist
657: -- hence error
658: dbms_sql.close_cursor(l_sql_cursor);
659: hr_Utility.set_Location(l_proc, 35);
660: hr_utility.set_message(801,'HR_52069_PAR_INVALID_COLUMN_ID');
661: hr_utility.raise_error;
662: Else
663: -- check if the business groups match
664: --
657: -- hence error
658: dbms_sql.close_cursor(l_sql_cursor);
659: hr_Utility.set_Location(l_proc, 35);
660: hr_utility.set_message(801,'HR_52069_PAR_INVALID_COLUMN_ID');
661: hr_utility.raise_error;
662: Else
663: -- check if the business groups match
664: --
665: -- Get the column values and close the cursor
663: -- check if the business groups match
664: --
665: -- Get the column values and close the cursor
666: --
667: hr_Utility.set_Location(l_proc, 40);
668: dbms_sql.column_value(l_sql_cursor, 1, l_business_group_id);
669: dbms_sql.close_cursor(l_sql_cursor);
670:
671: -- bug 1980440 fix starts
674:
675: if lv_cross_business_group <> 'Y' THEN
676:
677: if p_business_group_id <> l_business_group_id then
678: hr_utility.set_message(801,'HR_52070_PAR_COL_ID_DIFF_BG');
679: hr_utility.raise_error;
680: end if;
681:
682: end if;
675: if lv_cross_business_group <> 'Y' THEN
676:
677: if p_business_group_id <> l_business_group_id then
678: hr_utility.set_message(801,'HR_52070_PAR_COL_ID_DIFF_BG');
679: hr_utility.raise_error;
680: end if;
681:
682: end if;
683:
697: raise;
698: end if;
699:
700: When Others Then
701: Hr_Utility.Set_Location(l_proc, 50);
702: If (dbms_sql.is_open(l_sql_cursor)) then
703: hr_utility.Set_Location(l_proc, 55);
704: dbms_sql.close_cursor(l_sql_cursor);
705: End If;
699:
700: When Others Then
701: Hr_Utility.Set_Location(l_proc, 50);
702: If (dbms_sql.is_open(l_sql_cursor)) then
703: hr_utility.Set_Location(l_proc, 55);
704: dbms_sql.close_cursor(l_sql_cursor);
705: End If;
706: Raise;
707: --
704: dbms_sql.close_cursor(l_sql_cursor);
705: End If;
706: Raise;
707: --
708: hr_utility.set_location(l_proc, 55);
709: --
710: hr_utility.set_location('Leaving: '|| l_proc, 60);
711:
712:
706: Raise;
707: --
708: hr_utility.set_location(l_proc, 55);
709: --
710: hr_utility.set_location('Leaving: '|| l_proc, 60);
711:
712:
713: end chk_participation_in_id;
714: --
834: and par.participation_in_id = p_participation_in_id
835: and par.person_id = p_person_id;
836: --
837: begin
838: hr_utility.set_location('Entering:'|| l_proc, 1);
839: --
840: if (p_person_id is NULL) then
841: hr_utility.set_message(801, 'HR_52075_PAR_PERSON_NULL');
842: hr_utility.raise_error;
837: begin
838: hr_utility.set_location('Entering:'|| l_proc, 1);
839: --
840: if (p_person_id is NULL) then
841: hr_utility.set_message(801, 'HR_52075_PAR_PERSON_NULL');
842: hr_utility.raise_error;
843: end if;
844: --
845: -- Check mandatory parameters have been set
838: hr_utility.set_location('Entering:'|| l_proc, 1);
839: --
840: if (p_person_id is NULL) then
841: hr_utility.set_message(801, 'HR_52075_PAR_PERSON_NULL');
842: hr_utility.raise_error;
843: end if;
844: --
845: -- Check mandatory parameters have been set
846: --
876: ,p_argument_value => p_participation_in_id
877: );
878: --
879: --
880: hr_utility.set_location('Entering:'|| l_proc, 2);
881: --
882: --
883: -- bug 1980440 fix starts
884: lv_cross_business_group := fnd_profile.value('HR_CROSS_BUSINESS_GROUP');
897: ) or
898: (NOT l_api_updating)
899: ) then
900: --
901: hr_utility.set_location(l_proc, 2);
902: --
903: if p_person_id is not null then
904: -- Bug 1980440 fix
905: -- if Cross Business Group is not enabled use csr_person_bg cursor
908: open csr_person_bg;
909: fetch csr_person_bg into l_business_group_id;
910: if csr_person_bg%notfound then
911: close csr_person_bg;
912: hr_utility.set_message(801,'HR_52071_PAR_PERSON_NOT_EXIST');
913: hr_utility.raise_error;
914: end if;
915: close csr_person_bg;
916: else -- use the csr_cbg_person_bg cursor to get the Business Group Id
909: fetch csr_person_bg into l_business_group_id;
910: if csr_person_bg%notfound then
911: close csr_person_bg;
912: hr_utility.set_message(801,'HR_52071_PAR_PERSON_NOT_EXIST');
913: hr_utility.raise_error;
914: end if;
915: close csr_person_bg;
916: else -- use the csr_cbg_person_bg cursor to get the Business Group Id
917: open csr_cbg_person_bg;
917: open csr_cbg_person_bg;
918: fetch csr_cbg_person_bg into l_business_group_id;
919: if csr_cbg_person_bg%notfound then
920: close csr_cbg_person_bg;
921: hr_utility.set_message(801,'HR_52071_PAR_PERSON_NOT_EXIST');
922: hr_utility.raise_error;
923: end if;
924: close csr_cbg_person_bg;
925: end if;
918: fetch csr_cbg_person_bg into l_business_group_id;
919: if csr_cbg_person_bg%notfound then
920: close csr_cbg_person_bg;
921: hr_utility.set_message(801,'HR_52071_PAR_PERSON_NOT_EXIST');
922: hr_utility.raise_error;
923: end if;
924: close csr_cbg_person_bg;
925: end if;
926: hr_utility.set_location(l_proc, 3);
922: hr_utility.raise_error;
923: end if;
924: close csr_cbg_person_bg;
925: end if;
926: hr_utility.set_location(l_proc, 3);
927:
928: -- bug 1980440 fix starts
929: -- do the validation if Cross Business Group profile is not enabled
930: if lv_cross_business_group <> 'Y' THEN
929: -- do the validation if Cross Business Group profile is not enabled
930: if lv_cross_business_group <> 'Y' THEN
931: -- check if business group match
932: if p_business_group_id <> l_business_group_id then
933: hr_utility.set_message(801,'HR_52072_PAR_PERSON_DIFF_BG');
934: hr_utility.raise_error;
935: end if;
936: end if;
937: -- bug 1980440 fix ends
930: if lv_cross_business_group <> 'Y' THEN
931: -- check if business group match
932: if p_business_group_id <> l_business_group_id then
933: hr_utility.set_message(801,'HR_52072_PAR_PERSON_DIFF_BG');
934: hr_utility.raise_error;
935: end if;
936: end if;
937: -- bug 1980440 fix ends
938:
935: end if;
936: end if;
937: -- bug 1980440 fix ends
938:
939: hr_utility.set_location(l_proc, 4);
940: -- check if person is valid as of effective date
941: -- bug 1980440 fix starts
942: if lv_cross_business_group <> 'Y' THEN
943: open csr_person_valid_date;
943: open csr_person_valid_date;
944: fetch csr_person_valid_date into l_exists;
945: if csr_person_valid_date%notfound then
946: close csr_person_valid_date;
947: hr_utility.set_message(801,'HR_52073_PAR_PERSON_DATE_RANGE');
948: hr_utility.raise_error;
949: end if;
950: close csr_person_valid_date;
951: else
944: fetch csr_person_valid_date into l_exists;
945: if csr_person_valid_date%notfound then
946: close csr_person_valid_date;
947: hr_utility.set_message(801,'HR_52073_PAR_PERSON_DATE_RANGE');
948: hr_utility.raise_error;
949: end if;
950: close csr_person_valid_date;
951: else
952: open csr_cbg_person_valid_date;
952: open csr_cbg_person_valid_date;
953: fetch csr_cbg_person_valid_date into l_exists;
954: if csr_cbg_person_valid_date%notfound then
955: close csr_cbg_person_valid_date;
956: hr_utility.set_message(801,'HR_52073_PAR_PERSON_DATE_RANGE');
957: hr_utility.raise_error;
958: end if;
959: close csr_cbg_person_valid_date;
960: end if;
953: fetch csr_cbg_person_valid_date into l_exists;
954: if csr_cbg_person_valid_date%notfound then
955: close csr_cbg_person_valid_date;
956: hr_utility.set_message(801,'HR_52073_PAR_PERSON_DATE_RANGE');
957: hr_utility.raise_error;
958: end if;
959: close csr_cbg_person_valid_date;
960: end if;
961: -- bug 1980440 fix ends
959: close csr_cbg_person_valid_date;
960: end if;
961: -- bug 1980440 fix ends
962: -- check that the person id is unique
963: hr_utility.set_location(l_proc, 5);
964: open csr_person_id_unique;
965: fetch csr_person_id_unique into l_exists;
966: if csr_person_id_unique%found then
967: close csr_person_id_unique;
964: open csr_person_id_unique;
965: fetch csr_person_id_unique into l_exists;
966: if csr_person_id_unique%found then
967: close csr_person_id_unique;
968: hr_utility.set_message(801,'HR_52074_PAR_PERSON_NOT_UNIQUE');
969: hr_utility.raise_error;
970: end if;
971: close csr_person_id_unique;
972: end if;
965: fetch csr_person_id_unique into l_exists;
966: if csr_person_id_unique%found then
967: close csr_person_id_unique;
968: hr_utility.set_message(801,'HR_52074_PAR_PERSON_NOT_UNIQUE');
969: hr_utility.raise_error;
970: end if;
971: close csr_person_id_unique;
972: end if;
973: --
972: end if;
973: --
974: end if;
975: --
976: hr_utility.set_location(l_proc, 6);
977: --
978: hr_utility.set_location('Leaving: '|| l_proc, 10);
979: --
980: EXCEPTION
974: end if;
975: --
976: hr_utility.set_location(l_proc, 6);
977: --
978: hr_utility.set_location('Leaving: '|| l_proc, 10);
979: --
980: EXCEPTION
981: when app_exception.application_exception then
982: if hr_multi_message.exception_add
1018: --
1019: l_proc varchar2(72) := g_package||'chk_df';
1020: --
1021: begin
1022: hr_utility.set_location('Entering:'||l_proc, 10);
1023: --
1024: if ((p_rec.participant_id is not null) and (
1025: nvl(per_par_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1026: nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1120: ,p_attribute21_value => p_rec.participant_usage_status
1121: );
1122: end if;
1123: --
1124: hr_utility.set_location(' Leaving:'||l_proc, 20);
1125: --
1126: end chk_df;
1127: --
1128: -- ----------------------------------------------------------------------------
1164: --
1165: BEGIN
1166:
1167:
1168: hr_utility.set_location('Entering:'|| l_proc, 10);
1169:
1170:
1171: --
1172: -- Only proceed with validation if :
1187: IF p_participant_usage_status IS NOT null THEN
1188: --
1189: -- Check that oparticipant_usage_status is valid.
1190: --
1191: hr_utility.set_location(l_proc, 20);
1192: IF hr_api.not_exists_in_hr_lookups
1193: (p_effective_date => p_effective_date
1194: ,p_lookup_type => 'APPRAISAL_OFFLINE_STATUS'
1195: ,p_lookup_code => upper(p_participant_usage_status)
1205: END IF;*/
1206:
1207: END IF;
1208:
1209: hr_utility.set_location('Leaving:'|| l_proc, 970);
1210: EXCEPTION
1211:
1212: WHEN app_exception.application_exception THEN
1213: IF hr_multi_message.exception_add
1212: WHEN app_exception.application_exception THEN
1213: IF hr_multi_message.exception_add
1214: (p_associated_column1 => 'PER_PARTICIPANTS.PARTICIPANT_USAGE_STATUS')
1215: THEN
1216: hr_utility.set_location(' Leaving:'|| l_proc, 980);
1217: RAISE;
1218: END IF;
1219: hr_utility.set_location(' Leaving:'|| l_proc, 990);
1220:
1215: THEN
1216: hr_utility.set_location(' Leaving:'|| l_proc, 980);
1217: RAISE;
1218: END IF;
1219: hr_utility.set_location(' Leaving:'|| l_proc, 990);
1220:
1221: END chk_participant_usage_status;
1222: --
1223: -- ----------------------------------------------------------------------------
1228: --
1229: l_proc varchar2(72) := g_package||'insert_validate';
1230: --
1231: Begin
1232: hr_utility.set_location('Entering:'||l_proc, 5);
1233: --
1234: -- Call all supporting business operations
1235: --
1236: hr_api.validate_bus_grp_id(p_rec.business_group_id
1237: ,p_associated_column1 => per_par_shd.g_tab_nam ||
1238: '.BUSINESS_GROUP_ID'); -- Validate Bus Grp
1239: hr_multi_message.end_validation_set;
1240: --
1241: hr_utility.set_location(l_proc, 1);
1242: --
1243: per_par_bus.chk_participation_in_table
1244: (p_participation_in_table => p_rec.participation_in_table
1245: );
1243: per_par_bus.chk_participation_in_table
1244: (p_participation_in_table => p_rec.participation_in_table
1245: );
1246: --
1247: hr_utility.set_location(l_proc, 2);
1248: --
1249: per_par_bus.chk_participation_in_column
1250: (p_participation_in_table => p_rec.participation_in_table
1251: ,p_participation_in_column => p_rec.participation_in_column
1250: (p_participation_in_table => p_rec.participation_in_table
1251: ,p_participation_in_column => p_rec.participation_in_column
1252: );
1253: --
1254: hr_utility.set_location(l_proc, 3);
1255: --
1256: per_par_bus.chk_participation_in_id
1257: (p_participation_in_table => p_rec.participation_in_table
1258: ,p_participation_in_column => p_rec.participation_in_column
1259: ,p_participation_in_id => p_rec.participation_in_id
1260: ,p_business_group_id => p_rec.business_group_id
1261: );
1262: --
1263: hr_utility.set_location(l_proc, 4);
1264: --
1265: per_par_bus.chk_person_id
1266: (p_participant_id => p_rec.participant_id
1267: ,p_object_version_number => p_rec.object_version_number
1272: ,p_participation_in_id => p_rec.participation_in_id
1273: ,p_effective_date => p_effective_date
1274: );
1275: --
1276: hr_utility.set_location(l_proc, 5);
1277: --
1278: per_par_bus.chk_participation_status
1279: (p_participation_status => p_rec.participation_status
1280: ,p_effective_date => p_effective_date
1279: (p_participation_status => p_rec.participation_status
1280: ,p_effective_date => p_effective_date
1281: );
1282: --
1283: hr_utility.set_location(l_proc,6);
1284: --
1285: per_par_bus.chk_participation_type
1286: (p_participant_id => p_rec.participant_id
1287: ,p_object_version_number => p_rec.object_version_number
1288: ,p_participation_type => p_rec.participation_type
1289: ,p_effective_date => p_effective_date
1290: );
1291: --
1292: hr_utility.set_location(' Leaving:'||l_proc, 10);
1293: --
1294: per_par_bus.chk_participant_usage_status
1295: (p_participant_id => p_rec.participant_id
1296: ,p_object_version_number => p_rec.object_version_number
1312: --
1313: l_proc varchar2(72) := g_package||'update_validate';
1314: --
1315: Begin
1316: hr_utility.set_location('Entering:'||l_proc, 1);
1317: --
1318: -- Call all supporting business operations
1319: --
1320: --
1328: -- Rule Check non-updateable fields cannot be updated
1329: --
1330: chk_non_updateable_args(p_rec => p_rec);
1331: --
1332: hr_utility.set_location(l_proc, 2);
1333: --
1334: per_par_bus.chk_participation_status
1335: (p_participation_status => p_rec.participation_status
1336: ,p_effective_date => p_effective_date
1346: ,p_participation_type => p_rec.participation_type
1347: ,p_effective_date => p_effective_date
1348: );
1349: --
1350: hr_utility.set_location(l_proc,3);
1351: --
1352: per_par_bus.chk_person_id
1353: (p_participant_id => p_rec.participant_id
1354: ,p_object_version_number => p_rec.object_version_number
1359: ,p_participation_in_id => p_rec.participation_in_id
1360: ,p_effective_date => p_effective_date
1361: );
1362: --
1363: hr_utility.set_location(l_proc, 5);
1364: --
1365: hr_api.validate_bus_grp_id(p_rec.business_group_id
1366: ,p_associated_column1 => per_par_shd.g_tab_nam ||
1367: '.BUSINESS_GROUP_ID'); -- Validate Bus Grp
1367: '.BUSINESS_GROUP_ID'); -- Validate Bus Grp
1368:
1369: hr_multi_message.end_validation_set;
1370: --
1371: hr_utility.set_location(' Leaving:'||l_proc, 10);
1372: --
1373: per_par_bus.chk_participant_usage_status
1374: (p_participant_id => p_rec.participant_id
1375: ,p_object_version_number => p_rec.object_version_number
1390: --
1391: l_proc varchar2(72) := g_package||'delete_validate';
1392: --
1393: Begin
1394: hr_utility.set_location('Entering:'||l_proc, 5);
1395: --
1396: -- Call all supporting business operations
1397: --
1398: hr_utility.set_location(' Leaving:'||l_proc, 10);
1394: hr_utility.set_location('Entering:'||l_proc, 5);
1395: --
1396: -- Call all supporting business operations
1397: --
1398: hr_utility.set_location(' Leaving:'||l_proc, 10);
1399: End delete_validate;
1400: --
1401: -- ----------------------------------------------------------------------------
1402: -- |-----------------------< return_legislation_code >-------------------------|
1417: l_proc varchar2(72) := g_package||'return_legislation_code';
1418: l_legislation_code varchar2(150);
1419: --
1420: Begin
1421: hr_utility.set_location('Entering:'||l_proc, 5);
1422: --
1423: -- Ensure that all the mandatory parameters are not null
1424: --
1425: hr_api.mandatory_arg_error (p_api_name => l_proc,
1432: -- call to this function. Just return the value in the global
1433: -- variable.
1434: --
1435: l_legislation_code := g_legislation_code;
1436: hr_utility.set_location(l_proc, 10);
1437: else
1438: --
1439: -- The ID is different to the last call to this function
1440: -- or this is the first call to this function.
1445: close csr_leg_code;
1446: --
1447: -- The primary key is invalid therefore we must error out
1448: --
1449: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1450: hr_utility.raise_error;
1451: end if;
1452: --
1453: close csr_leg_code;
1446: --
1447: -- The primary key is invalid therefore we must error out
1448: --
1449: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1450: hr_utility.raise_error;
1451: end if;
1452: --
1453: close csr_leg_code;
1454: g_participant_id := p_participant_id;
1453: close csr_leg_code;
1454: g_participant_id := p_participant_id;
1455: g_legislation_code := l_legislation_code;
1456: end if;
1457: hr_utility.set_location(' Leaving:'|| l_proc, 20);
1458:
1459: return l_legislation_code;
1460: --
1461: hr_utility.set_location(' Leaving:'||l_proc, 30);
1457: hr_utility.set_location(' Leaving:'|| l_proc, 20);
1458:
1459: return l_legislation_code;
1460: --
1461: hr_utility.set_location(' Leaving:'||l_proc, 30);
1462: --
1463: End return_legislation_code;
1464: --
1465: end per_par_bus;