29: --
30: -- check mandatory parameters have been set
31: --
32: hr_utility.set_location(' Step:'|| l_proc, 20);
33: hr_api.mandatory_arg_error
34: (p_api_name => l_proc
35: ,p_argument => 'p_training_plan_id'
36: ,p_argument_value => p_training_plan_id
37: );
36: ,p_argument_value => p_training_plan_id
37: );
38: --
39: hr_utility.set_location(' Step:'|| l_proc, 30);
40: hr_api.mandatory_arg_error
41: (p_api_name => l_proc
42: ,p_argument => 'p_business_group_id'
43: ,p_argument_value => p_business_group_id
44: );
97: --
98: -- check mandatory parameters have been set.
99: --
100: hr_utility.set_location(' Step:'|| l_proc, 10);
101: hr_api.mandatory_arg_error
102: (p_api_name => l_proc
103: ,p_argument => 'p_business_group_id'
104: ,p_argument_value => p_business_group_id
105: );
112: -- If this is a changing update, or a new insert, test
113: --
114: IF p_activity_definition_id IS NOT NULL THEN
115: IF ((l_api_updating AND
116: NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
117: NVL(p_activity_definition_id, hr_api.g_number) )
118: OR (NOT l_api_updating) )
119: THEN
120: -- Check that the definition exists
113: --
114: IF p_activity_definition_id IS NOT NULL THEN
115: IF ((l_api_updating AND
116: NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
117: NVL(p_activity_definition_id, hr_api.g_number) )
118: OR (NOT l_api_updating) )
119: THEN
120: -- Check that the definition exists
121: --
182: -- check mandatory parameters have been set
183: --
184: --
185: hr_utility.set_location(' Step:'|| l_proc, 30);
186: hr_api.mandatory_arg_error
187: (p_api_name => l_proc
188: ,p_argument => 'p_business_group_id'
189: ,p_argument_value => p_business_group_id
190: );
190: );
191: --
192: --
193: hr_utility.set_location(' Step:'|| l_proc, 40);
194: hr_api.mandatory_arg_error
195: (p_api_name => l_proc
196: ,p_argument => 'p_training_plan_id'
197: ,p_argument_value => p_training_plan_id
198: );
214: --
215: -- If this is a changing update, no budget records are allowed
216: --
217: IF (l_api_updating AND
218: (NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
219: NVL(p_activity_version_id, hr_api.g_number)
220: OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
221: NVL(p_activity_definition_id, hr_api.g_number) )) THEN
222: --
215: -- If this is a changing update, no budget records are allowed
216: --
217: IF (l_api_updating AND
218: (NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
219: NVL(p_activity_version_id, hr_api.g_number)
220: OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
221: NVL(p_activity_definition_id, hr_api.g_number) )) THEN
222: --
223: hr_utility.set_location(' Step:'|| l_proc, 50);
216: --
217: IF (l_api_updating AND
218: (NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
219: NVL(p_activity_version_id, hr_api.g_number)
220: OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
221: NVL(p_activity_definition_id, hr_api.g_number) )) THEN
222: --
223: hr_utility.set_location(' Step:'|| l_proc, 50);
224: OPEN csr_version_definition;
217: IF (l_api_updating AND
218: (NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
219: NVL(p_activity_version_id, hr_api.g_number)
220: OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
221: NVL(p_activity_definition_id, hr_api.g_number) )) THEN
222: --
223: hr_utility.set_location(' Step:'|| l_proc, 50);
224: OPEN csr_version_definition;
225: FETCH csr_version_definition INTO l_exists;
311: IF hr_multi_message.no_exclusive_error
312: (p_check_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_ID'
313: ,p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_ID') THEN
314:
315: hr_api.mandatory_arg_error
316: (p_api_name => l_proc
317: ,p_argument => 'p_business_group_id'
318: ,p_argument_value => p_business_group_id
319: );
326: -- If this is a changing update, or a new insert, test
327: --
328: IF p_activity_version_id IS NOT NULL THEN
329: IF (l_api_updating AND
330: NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
331: NVL(p_activity_version_id, hr_api.g_number) )
332: OR (NOT l_api_updating)
333: THEN
334: -- Check that the definition exists
327: --
328: IF p_activity_version_id IS NOT NULL THEN
329: IF (l_api_updating AND
330: NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
331: NVL(p_activity_version_id, hr_api.g_number) )
332: OR (NOT l_api_updating)
333: THEN
334: -- Check that the definition exists
335: --
365: CLOSE csr_version_date_range;
366: --
367: -- Test
368: --
369: IF NVL(l_version_end_date, hr_api.g_eot) < NVL(l_plan_start_date, hr_api.g_sot) THEN
370: IF (OTA_TRNG_PLAN_UTIL_SS.is_personal_trng_plan(p_training_plan_id)) THEN
371: fnd_message.set_name('OTA', 'OTA_443569_PLPM_VER_TOO_EARLY');
372: fnd_message.set_token('TP_STARTDATE',l_plan_start_date);
373: ELSE
373: ELSE
374: fnd_message.set_name('OTA', 'OTA_13852_TPM_VER_TOO_EARLY');
375: END IF;
376: fnd_message.raise_error;
377: ELSIF NVL(l_version_start_date, hr_api.g_sot) > NVL(l_plan_end_date, hr_api.g_eot) THEN
378: fnd_message.set_name('OTA', 'OTA_13853_TPM_VER_TOO_LATE');
379: fnd_message.raise_error;
380: END IF;
381: END IF;
478: ,p_associated_column2 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_VERSION_ID'
479: ,p_associated_column3 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_DEFINITION_ID') THEN
480:
481:
482: hr_api.mandatory_arg_error
483: (p_api_name => l_proc
484: ,p_argument => 'p_business_group_id'
485: ,p_argument_value => p_business_group_id
486: );
485: ,p_argument_value => p_business_group_id
486: );
487: --
488: hr_utility.set_location(' Step:'|| l_proc, 40);
489: hr_api.mandatory_arg_error
490: (p_api_name => l_proc
491: ,p_argument => 'p_member_status_type_id'
492: ,p_argument_value => p_member_status_type_id
493: );
492: ,p_argument_value => p_member_status_type_id
493: );
494: --
495: hr_utility.set_location(' Step:'|| l_proc, 45);
496: hr_api.mandatory_arg_error
497: (p_api_name => l_proc
498: ,p_argument => 'p_effective_date'
499: ,p_argument_value => p_effective_date
500: );
499: ,p_argument_value => p_effective_date
500: );
501: --
502: hr_utility.set_location(' Step:'|| l_proc, 50);
503: hr_api.mandatory_arg_error
504: (p_api_name => l_proc
505: ,p_argument => 'p_training_plan_id'
506: ,p_argument_value => p_training_plan_id
507: );
513: --
514: -- If this is a changing update, or a new insert, test status is valid
515: --
516: IF (l_api_updating AND
517: NVL(ota_tpm_shd.g_old_rec.member_status_type_id, hr_api.g_VARCHAR2) <>
518: NVL(p_member_status_type_id, hr_api.g_VARCHAR2) )
519: OR (NOT l_api_updating)
520: THEN
521: -- Check that the lookup code is valid
514: -- If this is a changing update, or a new insert, test status is valid
515: --
516: IF (l_api_updating AND
517: NVL(ota_tpm_shd.g_old_rec.member_status_type_id, hr_api.g_VARCHAR2) <>
518: NVL(p_member_status_type_id, hr_api.g_VARCHAR2) )
519: OR (NOT l_api_updating)
520: THEN
521: -- Check that the lookup code is valid
522: --
520: THEN
521: -- Check that the lookup code is valid
522: --
523: hr_utility.set_location(' Step:'|| l_proc, 55);
524: IF hr_api.not_exists_in_hr_lookups
525: (p_effective_date => p_effective_date
526: ,p_lookup_type => 'OTA_MEMBER_USER_STATUS_TYPE'
527: ,p_lookup_code => p_member_status_type_id
528: ) THEN
620: ,p_associated_column2 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_VERSION_ID'
621: ,p_associated_column3 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_DEFINITION_ID') THEN
622:
623:
624: hr_api.mandatory_arg_error
625: (p_api_name => l_proc
626: ,p_argument => 'p_training_plan_id'
627: ,p_argument_value => p_training_plan_id
628: );
633: --
634: -- Check if anything is changing, or this is an insert
635: --
636: IF (l_api_updating AND
637: NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
638: NVL(p_activity_version_id, hr_api.g_number)
639: OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
640: NVL(p_activity_definition_id, hr_api.g_number))
641: OR (NOT l_api_updating) THEN
634: -- Check if anything is changing, or this is an insert
635: --
636: IF (l_api_updating AND
637: NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
638: NVL(p_activity_version_id, hr_api.g_number)
639: OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
640: NVL(p_activity_definition_id, hr_api.g_number))
641: OR (NOT l_api_updating) THEN
642: --
635: --
636: IF (l_api_updating AND
637: NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
638: NVL(p_activity_version_id, hr_api.g_number)
639: OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
640: NVL(p_activity_definition_id, hr_api.g_number))
641: OR (NOT l_api_updating) THEN
642: --
643: -- check the combination is unique
636: IF (l_api_updating AND
637: NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
638: NVL(p_activity_version_id, hr_api.g_number)
639: OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
640: NVL(p_activity_definition_id, hr_api.g_number))
641: OR (NOT l_api_updating) THEN
642: --
643: -- check the combination is unique
644: --
721: ,p_associated_column2 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_VERSION_ID'
722: ,p_associated_column3 => 'OTA_TRAINING_PLAN_MEMBERS.ACTIVITY_DEFINITION_ID') THEN
723:
724:
725: hr_api.mandatory_arg_error
726: (p_api_name => l_proc
727: ,p_argument => 'p_training_plan_id'
728: ,p_argument_value => p_training_plan_id
729: );
734: --
735: -- Check if anything is changing, or this is an insert
736: --
737: IF (l_api_updating AND
738: NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
739: NVL(p_activity_version_id, hr_api.g_number)
740: OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
741: NVL(p_activity_definition_id, hr_api.g_number)
742: OR NVL(ota_tpm_shd.g_old_rec.target_completion_date, hr_api.g_date) <>
735: -- Check if anything is changing, or this is an insert
736: --
737: IF (l_api_updating AND
738: NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
739: NVL(p_activity_version_id, hr_api.g_number)
740: OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
741: NVL(p_activity_definition_id, hr_api.g_number)
742: OR NVL(ota_tpm_shd.g_old_rec.target_completion_date, hr_api.g_date) <>
743: NVL(p_target_completion_date, hr_api.g_date))
736: --
737: IF (l_api_updating AND
738: NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
739: NVL(p_activity_version_id, hr_api.g_number)
740: OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
741: NVL(p_activity_definition_id, hr_api.g_number)
742: OR NVL(ota_tpm_shd.g_old_rec.target_completion_date, hr_api.g_date) <>
743: NVL(p_target_completion_date, hr_api.g_date))
744: OR (NOT l_api_updating) THEN
737: IF (l_api_updating AND
738: NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
739: NVL(p_activity_version_id, hr_api.g_number)
740: OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
741: NVL(p_activity_definition_id, hr_api.g_number)
742: OR NVL(ota_tpm_shd.g_old_rec.target_completion_date, hr_api.g_date) <>
743: NVL(p_target_completion_date, hr_api.g_date))
744: OR (NOT l_api_updating) THEN
745: --
738: NVL(ota_tpm_shd.g_old_rec.activity_version_id, hr_api.g_number) <>
739: NVL(p_activity_version_id, hr_api.g_number)
740: OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
741: NVL(p_activity_definition_id, hr_api.g_number)
742: OR NVL(ota_tpm_shd.g_old_rec.target_completion_date, hr_api.g_date) <>
743: NVL(p_target_completion_date, hr_api.g_date))
744: OR (NOT l_api_updating) THEN
745: --
746: -- check the combination is unique
739: NVL(p_activity_version_id, hr_api.g_number)
740: OR NVL(ota_tpm_shd.g_old_rec.activity_definition_id, hr_api.g_number) <>
741: NVL(p_activity_definition_id, hr_api.g_number)
742: OR NVL(ota_tpm_shd.g_old_rec.target_completion_date, hr_api.g_date) <>
743: NVL(p_target_completion_date, hr_api.g_date))
744: OR (NOT l_api_updating) THEN
745: --
746: -- check the combination is unique
747: --
816: (p_check_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_ID'
817: ,p_associated_column1 => 'OTA_TRAINING_PLAN_MEMBERS.TRAINING_PLAN_ID') THEN
818:
819:
820: hr_api.mandatory_arg_error
821: (p_api_name => l_proc
822: ,p_argument => 'p_training_plan_member_id'
823: ,p_argument_value => p_training_plan_member_id
824: );
824: );
825: --
826: --
827: hr_utility.set_location(' Step:'|| l_proc, 40);
828: hr_api.mandatory_arg_error
829: (p_api_name => l_proc
830: ,p_argument => 'p_training_plan_id'
831: ,p_argument_value => p_training_plan_id
832: );
881: hr_utility.set_location(' Leaving:'||l_proc, 10);
882: --
883: -- check mandatory parameters has been set
884: --
885: hr_api.mandatory_arg_error
886: (p_api_name => l_proc
887: ,p_argument => 'effective_date'
888: ,p_argument_value => p_effective_date);
889:
887: ,p_argument => 'effective_date'
888: ,p_argument_value => p_effective_date);
889:
890: IF (((p_training_plan_member_id IS NOT NULL) AND
891: NVL(ota_tpm_shd.g_old_rec.source_function,hr_api.g_VARCHAR2) <>
892: NVL(p_source_function,hr_api.g_VARCHAR2))
893: OR
894: (p_training_plan_member_id IS NULL)) THEN
895:
888: ,p_argument_value => p_effective_date);
889:
890: IF (((p_training_plan_member_id IS NOT NULL) AND
891: NVL(ota_tpm_shd.g_old_rec.source_function,hr_api.g_VARCHAR2) <>
892: NVL(p_source_function,hr_api.g_VARCHAR2))
893: OR
894: (p_training_plan_member_id IS NULL)) THEN
895:
896: hr_utility.set_location(' Leaving:'||l_proc, 20);
897: --
898:
899: --
900: IF p_source_function IS NOT NULL THEN
901: IF hr_api.not_exists_in_hr_lookups
902: (p_effective_date => p_effective_date
903: ,p_lookup_type => 'OTA_PLAN_COMPONENT_SOURCE'
904: ,p_lookup_code => p_source_FUNCTION) THEN
905: fnd_message.set_name('OTA','OTA_13178_TPM_SRC_FUNC_INVLD');
942: hr_utility.set_location(' Leaving:'||l_proc, 10);
943: --
944: -- check mandatory parameters has been set
945: --
946: hr_api.mandatory_arg_error
947: (p_api_name => l_proc
948: ,p_argument => 'effective_date'
949: ,p_argument_value => p_effective_date);
950:
948: ,p_argument => 'effective_date'
949: ,p_argument_value => p_effective_date);
950:
951: IF (((p_training_plan_member_id IS NOT NULL) AND
952: NVL(ota_tpm_shd.g_old_rec.cancellation_reason,hr_api.g_VARCHAR2) <>
953: NVL(p_cancellation_reason,hr_api.g_VARCHAR2))
954: OR
955: (p_training_plan_member_id IS NULL)) THEN
956:
949: ,p_argument_value => p_effective_date);
950:
951: IF (((p_training_plan_member_id IS NOT NULL) AND
952: NVL(ota_tpm_shd.g_old_rec.cancellation_reason,hr_api.g_VARCHAR2) <>
953: NVL(p_cancellation_reason,hr_api.g_VARCHAR2))
954: OR
955: (p_training_plan_member_id IS NULL)) THEN
956:
957: hr_utility.set_location(' Leaving:'||l_proc, 20);
958: --
959:
960: --
961: IF p_cancellation_reason IS NOT NULL THEN
962: IF hr_api.not_exists_in_hr_lookups
963: (p_effective_date => p_effective_date
964: ,p_lookup_type => 'OTA_PLAN_CANCELLATION_SOURCE'
965: ,p_lookup_code => p_cancellation_reason) THEN
966: fnd_message.set_name('OTA','OTA_13177_TPM_CANCL_RSN_INVLD');
1040:
1041: -- check mandatory parameters have been set
1042: --
1043: hr_utility.set_location(' Step:'|| l_proc, 10);
1044: hr_api.mandatory_arg_error
1045: (p_api_name => l_proc
1046: ,p_argument => 'p_training_plan_id'
1047: ,p_argument_value => p_training_plan_id
1048: );
1055: --
1056: -- If this is a changing update, or a new insert, test
1057: --
1058: IF ((l_api_updating AND
1059: NVL(ota_tpm_shd.g_old_rec.earliest_start_date, hr_api.g_date) <>
1060: NVL(p_earliest_start_date, hr_api.g_date)
1061: OR NVL(ota_tpm_shd.g_old_rec.target_completion_date, hr_api.g_date) <>
1062: NVL(p_target_completion_date, hr_api.g_date) )
1063: OR (NOT l_api_updating))
1056: -- If this is a changing update, or a new insert, test
1057: --
1058: IF ((l_api_updating AND
1059: NVL(ota_tpm_shd.g_old_rec.earliest_start_date, hr_api.g_date) <>
1060: NVL(p_earliest_start_date, hr_api.g_date)
1061: OR NVL(ota_tpm_shd.g_old_rec.target_completion_date, hr_api.g_date) <>
1062: NVL(p_target_completion_date, hr_api.g_date) )
1063: OR (NOT l_api_updating))
1064: THEN
1057: --
1058: IF ((l_api_updating AND
1059: NVL(ota_tpm_shd.g_old_rec.earliest_start_date, hr_api.g_date) <>
1060: NVL(p_earliest_start_date, hr_api.g_date)
1061: OR NVL(ota_tpm_shd.g_old_rec.target_completion_date, hr_api.g_date) <>
1062: NVL(p_target_completion_date, hr_api.g_date) )
1063: OR (NOT l_api_updating))
1064: THEN
1065: hr_utility.set_location(' Step:'|| l_proc, 20);
1058: IF ((l_api_updating AND
1059: NVL(ota_tpm_shd.g_old_rec.earliest_start_date, hr_api.g_date) <>
1060: NVL(p_earliest_start_date, hr_api.g_date)
1061: OR NVL(ota_tpm_shd.g_old_rec.target_completion_date, hr_api.g_date) <>
1062: NVL(p_target_completion_date, hr_api.g_date) )
1063: OR (NOT l_api_updating))
1064: THEN
1065: hr_utility.set_location(' Step:'|| l_proc, 20);
1066:
1064: THEN
1065: hr_utility.set_location(' Step:'|| l_proc, 20);
1066:
1067: /* IF( NOT l_api_updating
1068: or NVL(ota_tpm_shd.g_old_rec.earliest_start_date, hr_api.g_date) <>
1069: NVL(p_earliest_start_date, hr_api.g_date)) THEN
1070:
1071: IF ( p_earliest_start_date < TRUNC(SYSDATE))THEN
1072: l_flag := 'START_DATE';
1065: hr_utility.set_location(' Step:'|| l_proc, 20);
1066:
1067: /* IF( NOT l_api_updating
1068: or NVL(ota_tpm_shd.g_old_rec.earliest_start_date, hr_api.g_date) <>
1069: NVL(p_earliest_start_date, hr_api.g_date)) THEN
1070:
1071: IF ( p_earliest_start_date < TRUNC(SYSDATE))THEN
1072: l_flag := 'START_DATE';
1073: fnd_message.set_name('OTA', 'OTA_13179_TPM_STRT_DATE');
1098: OPEN csr_version_date_range;
1099: FETCH csr_version_date_range INTO l_version_start_date, l_version_end_date;
1100: CLOSE csr_version_date_range;
1101: --
1102: IF NVL(l_version_end_date, hr_api.g_eot) < NVL(p_earliest_start_date, hr_api.g_sot) THEN
1103: fnd_message.set_name('OTA', 'OTA_13181_TPM_ACT_VRSN_EARLY');
1104: fnd_message.raise_error;
1105: ELSIF NVL(l_version_start_date, hr_api.g_sot) > NVL(p_target_completion_date, hr_api.g_eot) THEN
1106: fnd_message.set_name('OTA', 'OTA_13180_TPM_ACT_VRSN_LATE');
1101: --
1102: IF NVL(l_version_end_date, hr_api.g_eot) < NVL(p_earliest_start_date, hr_api.g_sot) THEN
1103: fnd_message.set_name('OTA', 'OTA_13181_TPM_ACT_VRSN_EARLY');
1104: fnd_message.raise_error;
1105: ELSIF NVL(l_version_start_date, hr_api.g_sot) > NVL(p_target_completion_date, hr_api.g_eot) THEN
1106: fnd_message.set_name('OTA', 'OTA_13180_TPM_ACT_VRSN_LATE');
1107: fnd_message.raise_error;
1108: elsif (l_version_start_date>p_earliest_start_date) or (l_version_end_date