15: where pap.person_id = P_PERSON_ID and
16: pap.person_id = ptu.person_id and
17: ppf.person_type_id = ptu.person_type_id and
18: ppf.SEEDED_PERSON_TYPE_KEY = 'CONTACT' and
19: pap.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
20: pap.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) and
21: ptu.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
22: ptu.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'));
23:
16: pap.person_id = ptu.person_id and
17: ppf.person_type_id = ptu.person_type_id and
18: ppf.SEEDED_PERSON_TYPE_KEY = 'CONTACT' and
19: pap.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
20: pap.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) and
21: ptu.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
22: ptu.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'));
23:
24: person_type number;
17: ppf.person_type_id = ptu.person_type_id and
18: ppf.SEEDED_PERSON_TYPE_KEY = 'CONTACT' and
19: pap.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
20: pap.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) and
21: ptu.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
22: ptu.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'));
23:
24: person_type number;
25:
18: ppf.SEEDED_PERSON_TYPE_KEY = 'CONTACT' and
19: pap.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
20: pap.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) and
21: ptu.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
22: ptu.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'));
23:
24: person_type number;
25:
26: BEGIN
29: fetch csr_chk_contact into person_type;
30: if csr_chk_contact%NOTFOUND then
31: hr_utility.set_message(800,'HR_CON_INVALID_DETAILS_PL');
32: hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_INFORMATION_TYPE||'_FLEX'));
33: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
34: hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
35: -- This message will be 'These details can be entered only for a Contact. Person type is not a Contact for the date range'
36: hr_utility.raise_error;
37: end if;
30: if csr_chk_contact%NOTFOUND then
31: hr_utility.set_message(800,'HR_CON_INVALID_DETAILS_PL');
32: hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_INFORMATION_TYPE||'_FLEX'));
33: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
34: hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
35: -- This message will be 'These details can be entered only for a Contact. Person type is not a Contact for the date range'
36: hr_utility.raise_error;
37: end if;
38: close csr_chk_contact;
49: cursor csr_overlap_rec is
50: select 1 from per_people_extra_info where
51: person_id = P_PERSON_ID and
52: information_type = P_INFORMATION_TYPE and
53: (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)
54: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
55: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
56: between fnd_date.canonical_to_date(PEI_INFORMATION1)
57: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
50: select 1 from per_people_extra_info where
51: person_id = P_PERSON_ID and
52: information_type = P_INFORMATION_TYPE and
53: (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)
54: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
55: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
56: between fnd_date.canonical_to_date(PEI_INFORMATION1)
57: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
58: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
51: person_id = P_PERSON_ID and
52: information_type = P_INFORMATION_TYPE and
53: (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)
54: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
55: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
56: between fnd_date.canonical_to_date(PEI_INFORMATION1)
57: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
58: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
59: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
52: information_type = P_INFORMATION_TYPE and
53: (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)
54: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
55: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
56: between fnd_date.canonical_to_date(PEI_INFORMATION1)
57: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
58: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
59: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
60: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
53: (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)
54: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
55: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
56: between fnd_date.canonical_to_date(PEI_INFORMATION1)
57: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
58: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
59: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
60: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
61: fnd_date.canonical_to_date(P_FLEX_START_DATE) and
54: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
55: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
56: between fnd_date.canonical_to_date(PEI_INFORMATION1)
57: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
58: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
59: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
60: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
61: fnd_date.canonical_to_date(P_FLEX_START_DATE) and
62: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')));
55: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
56: between fnd_date.canonical_to_date(PEI_INFORMATION1)
57: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
58: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
59: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
60: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
61: fnd_date.canonical_to_date(P_FLEX_START_DATE) and
62: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')));
63:
56: between fnd_date.canonical_to_date(PEI_INFORMATION1)
57: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
58: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
59: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
60: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
61: fnd_date.canonical_to_date(P_FLEX_START_DATE) and
62: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')));
63:
64: cursor cur_inherit is select per_information2 from per_all_people_f where person_id =P_PERSON_ID
57: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
58: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
59: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
60: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
61: fnd_date.canonical_to_date(P_FLEX_START_DATE) and
62: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')));
63:
64: cursor cur_inherit is select per_information2 from per_all_people_f where person_id =P_PERSON_ID
65: and effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE)
58: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
59: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
60: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
61: fnd_date.canonical_to_date(P_FLEX_START_DATE) and
62: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')));
63:
64: cursor cur_inherit is select per_information2 from per_all_people_f where person_id =P_PERSON_ID
65: and effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE)
66: and effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
61: fnd_date.canonical_to_date(P_FLEX_START_DATE) and
62: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')));
63:
64: cursor cur_inherit is select per_information2 from per_all_people_f where person_id =P_PERSON_ID
65: and effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE)
66: and effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
67: and per_information2 ='Y';
68:
69: overlap_found number;
62: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')));
63:
64: cursor cur_inherit is select per_information2 from per_all_people_f where person_id =P_PERSON_ID
65: and effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE)
66: and effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
67: and per_information2 ='Y';
68:
69: overlap_found number;
70: vInherit per_all_people_f.per_information2%type;
69: overlap_found number;
70: vInherit per_all_people_f.per_information2%type;
71: BEGIN
72:
73: if (fnd_date.canonical_to_date(P_FLEX_START_DATE) >
74: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) then
75: hr_utility.set_message(800,'HR_ORG_START_DATE_PL');
76: -- This message will be 'Please enter a Start date >= End date
77: hr_utility.raise_error;
70: vInherit per_all_people_f.per_information2%type;
71: BEGIN
72:
73: if (fnd_date.canonical_to_date(P_FLEX_START_DATE) >
74: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) then
75: hr_utility.set_message(800,'HR_ORG_START_DATE_PL');
76: -- This message will be 'Please enter a Start date >= End date
77: hr_utility.raise_error;
78: end if;
82:
83: if cur_inherit%notfound then
84: hr_utility.set_message(800,'HR_CONTACT_INSURED_FLEX_PL');
85: hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_INFORMATION_TYPE||'_FLEX'));
86: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
87: hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
88: hr_utility.raise_error;
89: end if;
90:
83: if cur_inherit%notfound then
84: hr_utility.set_message(800,'HR_CONTACT_INSURED_FLEX_PL');
85: hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_INFORMATION_TYPE||'_FLEX'));
86: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
87: hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
88: hr_utility.raise_error;
89: end if;
90:
91: close cur_inherit;
94: fetch csr_overlap_rec into overlap_found;
95: if csr_overlap_rec%found then
96: hr_utility.set_message(800,'HR_CONTACT_OVERLAP_REC_PL');
97: hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_INFORMATION_TYPE||'_FLEX'));
98: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
99: hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
100: hr_utility.raise_error;
101: end if;
102: close csr_overlap_rec;
95: if csr_overlap_rec%found then
96: hr_utility.set_message(800,'HR_CONTACT_OVERLAP_REC_PL');
97: hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_INFORMATION_TYPE||'_FLEX'));
98: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
99: hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
100: hr_utility.raise_error;
101: end if;
102: close csr_overlap_rec;
103:
114:
115: cursor csr_overlap_upd_rec is
116: select 1 from per_people_extra_info where
117: pei_information_category = P_PEI_INFORMATION_CATEGORY and
118: (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)
119: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
120: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
121: between fnd_date.canonical_to_date(PEI_INFORMATION1)
122: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
115: cursor csr_overlap_upd_rec is
116: select 1 from per_people_extra_info where
117: pei_information_category = P_PEI_INFORMATION_CATEGORY and
118: (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)
119: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
120: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
121: between fnd_date.canonical_to_date(PEI_INFORMATION1)
122: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
123: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
116: select 1 from per_people_extra_info where
117: pei_information_category = P_PEI_INFORMATION_CATEGORY and
118: (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)
119: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
120: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
121: between fnd_date.canonical_to_date(PEI_INFORMATION1)
122: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
123: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
124: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
117: pei_information_category = P_PEI_INFORMATION_CATEGORY and
118: (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)
119: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
120: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
121: between fnd_date.canonical_to_date(PEI_INFORMATION1)
122: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
123: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
124: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
125: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
118: (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)
119: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
120: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
121: between fnd_date.canonical_to_date(PEI_INFORMATION1)
122: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
123: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
124: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
125: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
126: fnd_date.canonical_to_date(P_FLEX_START_DATE) and
119: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
120: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
121: between fnd_date.canonical_to_date(PEI_INFORMATION1)
122: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
123: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
124: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
125: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
126: fnd_date.canonical_to_date(P_FLEX_START_DATE) and
127: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) and
120: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
121: between fnd_date.canonical_to_date(PEI_INFORMATION1)
122: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
123: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
124: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
125: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
126: fnd_date.canonical_to_date(P_FLEX_START_DATE) and
127: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) and
128: person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID) and
121: between fnd_date.canonical_to_date(PEI_INFORMATION1)
122: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
123: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
124: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
125: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
126: fnd_date.canonical_to_date(P_FLEX_START_DATE) and
127: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) and
128: person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID) and
129: person_extra_info_id <> P_PERSON_EXTRA_INFO_ID;
122: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
123: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
124: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
125: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
126: fnd_date.canonical_to_date(P_FLEX_START_DATE) and
127: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) and
128: person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID) and
129: person_extra_info_id <> P_PERSON_EXTRA_INFO_ID;
130:
123: fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
124: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
125: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
126: fnd_date.canonical_to_date(P_FLEX_START_DATE) and
127: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) and
128: person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID) and
129: person_extra_info_id <> P_PERSON_EXTRA_INFO_ID;
130:
131:
138: where pap.person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID) and
139: pap.person_id = ptu.person_id and
140: ppf.person_type_id = ptu.person_type_id and
141: ppf.SEEDED_PERSON_TYPE_KEY = 'CONTACT' and
142: pap.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
143: pap.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) and
144: ptu.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
145: ptu.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'));
146:
139: pap.person_id = ptu.person_id and
140: ppf.person_type_id = ptu.person_type_id and
141: ppf.SEEDED_PERSON_TYPE_KEY = 'CONTACT' and
142: pap.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
143: pap.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) and
144: ptu.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
145: ptu.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'));
146:
147: cursor cur_inherit is select per_information2 from per_all_people_f where
140: ppf.person_type_id = ptu.person_type_id and
141: ppf.SEEDED_PERSON_TYPE_KEY = 'CONTACT' and
142: pap.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
143: pap.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) and
144: ptu.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
145: ptu.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'));
146:
147: cursor cur_inherit is select per_information2 from per_all_people_f where
148: person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID)
141: ppf.SEEDED_PERSON_TYPE_KEY = 'CONTACT' and
142: pap.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
143: pap.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) and
144: ptu.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
145: ptu.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'));
146:
147: cursor cur_inherit is select per_information2 from per_all_people_f where
148: person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID)
149: and effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE)
145: ptu.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'));
146:
147: cursor cur_inherit is select per_information2 from per_all_people_f where
148: person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID)
149: and effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE)
150: and effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
151: and per_information2 ='Y';
152:
153: vInherit per_all_people_f.per_information2%type;
146:
147: cursor cur_inherit is select per_information2 from per_all_people_f where
148: person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID)
149: and effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE)
150: and effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
151: and per_information2 ='Y';
152:
153: vInherit per_all_people_f.per_information2%type;
154:
157:
158:
159: BEGIN
160:
161: if (fnd_date.canonical_to_date(P_FLEX_START_DATE) >
162: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) then
163: hr_utility.set_message(800,'HR_ORG_START_DATE_PL');
164: -- This message will be 'Please enter a Start date >= End date
165: hr_utility.raise_error;
158:
159: BEGIN
160:
161: if (fnd_date.canonical_to_date(P_FLEX_START_DATE) >
162: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) then
163: hr_utility.set_message(800,'HR_ORG_START_DATE_PL');
164: -- This message will be 'Please enter a Start date >= End date
165: hr_utility.raise_error;
166: end if;
170:
171: if cur_inherit%notfound then
172: hr_utility.set_message(800,'HR_CONTACT_INSURED_FLEX_PL');
173: hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_PEI_INFORMATION_CATEGORY||'_FLEX'));
174: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
175: hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
176: hr_utility.raise_error;
177: end if;
178:
171: if cur_inherit%notfound then
172: hr_utility.set_message(800,'HR_CONTACT_INSURED_FLEX_PL');
173: hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_PEI_INFORMATION_CATEGORY||'_FLEX'));
174: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
175: hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
176: hr_utility.raise_error;
177: end if;
178:
179: close cur_inherit;
182: fetch csr_chk_contact_date into valid_contact_dates;
183: if csr_chk_contact_date%NOTFOUND then
184: hr_utility.set_message(800,'HR_CON_INVALID_DETAILS_PL');
185: hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_PEI_INFORMATION_CATEGORY||'_FLEX'));
186: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
187: hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
188: -- This message will be 'These details can be entered only for a Contact. Person type is not a Contact for the date range'
189: hr_utility.raise_error;
190: end if;
183: if csr_chk_contact_date%NOTFOUND then
184: hr_utility.set_message(800,'HR_CON_INVALID_DETAILS_PL');
185: hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_PEI_INFORMATION_CATEGORY||'_FLEX'));
186: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
187: hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
188: -- This message will be 'These details can be entered only for a Contact. Person type is not a Contact for the date range'
189: hr_utility.raise_error;
190: end if;
191: close csr_chk_contact_date;
195: fetch csr_overlap_upd_rec into overlap_upd_found;
196: if csr_overlap_upd_rec%found then
197: hr_utility.set_message(800,'HR_CONTACT_OVERLAP_REC_PL');
198: hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_PEI_INFORMATION_CATEGORY||'_FLEX'));
199: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
200: hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
201: hr_utility.raise_error;
202: end if;
203: close csr_overlap_upd_rec;
196: if csr_overlap_upd_rec%found then
197: hr_utility.set_message(800,'HR_CONTACT_OVERLAP_REC_PL');
198: hr_utility.set_message_token('CONTACTFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_PEI_INFORMATION_CATEGORY||'_FLEX'));
199: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
200: hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
201: hr_utility.raise_error;
202: end if;
203: close csr_overlap_upd_rec;
204: