eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PER_PL_PERSON_EXTRA_INFO dependencies on FND_DATE

Line 19: pap.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and

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:

Line 20: pap.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) 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:
24: person_type number;

Line 21: ptu.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) 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;
25:

Line 22: ptu.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'));

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

Line 33: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));

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;

Line 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'));

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;

Line 53: (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)

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

Line 54: 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

Line 55: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))

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

Line 56: between fnd_date.canonical_to_date(PEI_INFORMATION1)

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

Line 57: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or

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

Line 58: fnd_date.canonical_to_date(PEI_INFORMATION1) between 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')));

Line 59: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),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')));
63:

Line 60: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between

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

Line 61: fnd_date.canonical_to_date(P_FLEX_START_DATE) and

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)

Line 62: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')));

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'))

Line 65: and effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE)

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;

Line 66: and effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))

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;

Line 73: if (fnd_date.canonical_to_date(P_FLEX_START_DATE) >

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;

Line 74: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) then

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;

Line 86: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));

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:

Line 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'));

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;

Line 98: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));

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;

Line 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'));

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:

Line 118: (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)

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

Line 119: 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

Line 120: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))

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

Line 121: between fnd_date.canonical_to_date(PEI_INFORMATION1)

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

Line 122: and nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or

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

Line 123: fnd_date.canonical_to_date(PEI_INFORMATION1) between 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

Line 124: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),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
128: person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID) and

Line 125: nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between

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;

Line 126: fnd_date.canonical_to_date(P_FLEX_START_DATE) and

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:

Line 127: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) and

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:

Line 142: pap.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and

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:

Line 143: pap.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) 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:
147: cursor cur_inherit is select per_information2 from per_all_people_f where

Line 144: ptu.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) 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
148: person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID)

Line 145: ptu.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'));

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)

Line 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;

Line 150: and 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;
154:

Line 161: if (fnd_date.canonical_to_date(P_FLEX_START_DATE) >

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;

Line 162: nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) then

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;

Line 174: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));

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:

Line 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'));

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;

Line 186: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));

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;

Line 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'));

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;

Line 199: hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));

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;

Line 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'));

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: