[Home] [Help]
PACKAGE BODY: APPS.PER_PL_LEI_INFO
Source
1 PACKAGE BODY PER_PL_LEI_INFO as
2 /* $Header: peplleip.pkb 120.1 2006/09/13 12:23:47 mseshadr noship $ */
3 --------------------------------------------------------------------------------
4 -- PROCEDURE CREATE_PL_LEI_INFO
5 --------------------------------------------------------------------------------
6
7 PROCEDURE CHK_STREET_NAME(p_lei_information3 VARCHAR2,
8 p_lei_information4 VARCHAR2
9 ) IS
10 --
11 BEGIN
12 --
13 IF p_lei_information3 is not null and p_lei_information4 is null THEN
14 hr_utility.set_message(800,'HR_PL_ST_NAME_NOT_SPEC');
15 hr_utility.raise_error;
16 END IF;
17 END CHK_STREET_NAME;
18
19
20
21 PROCEDURE CREATE_LEI_DETAILS(P_LOCATION_ID NUMBER,
22 P_FLEX_START_DATE VARCHAR2,
23 P_FLEX_END_DATE VARCHAR2,
24 P_INFORMATION_TYPE VARCHAR2) IS
25
26 cursor csr_overlap_rec is
27 select 1
28 from hr_location_extra_info where
29 location_id = p_location_id and
30 INFORMATION_TYPE = P_INFORMATION_TYPE and
31 (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(LEI_INFORMATION1)
32 and nvl(fnd_date.canonical_to_date(LEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
33 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
34 between fnd_date.canonical_to_date(LEI_INFORMATION1)
35 and nvl(fnd_date.canonical_to_date(LEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
36 fnd_date.canonical_to_date(LEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
37 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
38 nvl(fnd_date.canonical_to_date(LEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
39 fnd_date.canonical_to_date(P_FLEX_START_DATE) and
40 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')));
41
42 overlap_found number;
43
44 BEGIN
45
46 /* Added for GSI Bug 5472781 */
47 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
48 hr_utility.trace('PL not installed.Leaving CREATE_LEI_DETAILS');
49 return;
50 END IF;
51
52 if (fnd_date.canonical_to_date(P_FLEX_START_DATE) > fnd_date.canonical_to_date(P_FLEX_END_DATE)) then
53 hr_utility.set_message(800,'HR_ORG_START_DATE_PL');
54 -- This message will be 'Please enter a Start date >= End date
55 hr_utility.raise_error;
56 end if;
57
58 open csr_overlap_rec;
59 fetch csr_overlap_rec into overlap_found;
60 if csr_overlap_rec%found then
61 hr_utility.set_message(800,'HR_ORG_OVERLAP_REC_PL');
62 hr_utility.set_message_token('ORGFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_INFORMATION_TYPE));
63 hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
64 hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
65 hr_utility.raise_error;
66 end if;
67 close csr_overlap_rec;
68
69
70 END CREATE_LEI_DETAILS;
71 -- CREATE_LEI_DETAILS
72 ------------------------------------------------------------------------------------------------------
73 -- UPDATE_LEI_DETAILS --
74 ------------------------------------------------------------------------------------------------------
75
76 PROCEDURE UPDATE_LEI_DETAILS(P_LOCATION_EXTRA_INFO_ID NUMBER,
77 P_FLEX_START_DATE VARCHAR2,
78 P_FLEX_END_DATE VARCHAR2,
79 p_lei_information_category VARCHAR2) IS
80
81
82 cursor csr_overlap_upd_rec is
83 select 1
84 from hr_location_extra_info where
85 INFORMATION_TYPE = p_lei_information_category
86 and location_extra_info_id <> P_LOCATION_EXTRA_INFO_ID and
87 location_id = (select location_id from hr_location_extra_info where LOCATION_EXTRA_INFO_ID = P_LOCATION_EXTRA_INFO_ID)
88 and (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(LEI_INFORMATION1)
89 and nvl(fnd_date.canonical_to_date(LEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
90 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
91 between fnd_date.canonical_to_date(LEI_INFORMATION1)
92 and nvl(fnd_date.canonical_to_date(LEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
93 fnd_date.canonical_to_date(LEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
94 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
95 nvl(fnd_date.canonical_to_date(LEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
96 fnd_date.canonical_to_date(P_FLEX_START_DATE) and
97 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')));
98
99
100 overlap_upd_found number;
101 org_start_date date;
102 org_end_date date;
103
104 BEGIN
105 /* Added for GSI Bug 5472781 */
106 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
107 hr_utility.trace('PL not installed.Leaving UPDATE_LEI_DETAILS');
108 return;
109 END IF;
110
111 if (fnd_date.canonical_to_date(P_FLEX_START_DATE) > fnd_date.canonical_to_date(P_FLEX_END_DATE)) then
112 hr_utility.set_message(800,'HR_ORG_START_DATE_PL');
113 -- This message will be 'Please enter a Start date >= End date
114 hr_utility.raise_error;
115 end if;
116
117 open csr_overlap_upd_rec;
118 fetch csr_overlap_upd_rec into overlap_upd_found;
119 if csr_overlap_upd_rec%found then
120 hr_utility.set_message(800,'HR_ORG_OVERLAP_REC_PL');
121 hr_utility.set_message_token('ORGFLEX',hr_general.decode_lookup('PL_FORM_LABELS',p_lei_information_category));
122 hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
123 hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
124 hr_utility.raise_error;
125 end if;
126 close csr_overlap_upd_rec;
127
128 END UPDATE_LEI_DETAILS;
129 -- UPDATE_LEI_DETAILS
130
131 PROCEDURE CREATE_PL_LEI_INFO(p_information_type VARCHAR2
132 ,p_location_id NUMBER
133 ,p_lei_information1 VARCHAR2
134 ,p_lei_information2 VARCHAR2
135 ,p_lei_information3 VARCHAR2
136 ,p_lei_information4 VARCHAR2
137 ,p_lei_information5 VARCHAR2
138 ,p_lei_information6 VARCHAR2
139 ,p_lei_information7 VARCHAR2
140 ,p_lei_information8 VARCHAR2
141 ,p_lei_information9 VARCHAR2
142 ,p_lei_information10 VARCHAR2
143 ,p_lei_information11 VARCHAR2
144 ,p_lei_information12 VARCHAR2
145 ,p_lei_information13 VARCHAR2
146 ,p_lei_information14 VARCHAR2
147 ,p_lei_information15 VARCHAR2
148 ,p_lei_information16 VARCHAR2
149 ,p_lei_information17 VARCHAR2
150 ,p_lei_information18 VARCHAR2
151 ,p_lei_information19 VARCHAR2
152 ,p_lei_information20 VARCHAR2
153 ,p_lei_information21 VARCHAR2
154 ,p_lei_information22 VARCHAR2
155 ,p_lei_information23 VARCHAR2
156 ,p_lei_information24 VARCHAR2
157 ,p_lei_information25 VARCHAR2
158 ,p_lei_information26 VARCHAR2
159 ,p_lei_information27 VARCHAR2
160 ,p_lei_information28 VARCHAR2
161 ,p_lei_information29 VARCHAR2
162 ,p_lei_information30 VARCHAR2
163 ) IS
164
165 --
166 BEGIN
167 --
168 IF p_information_type ='PL_CORRESPONDENCE_ADDRESS' THEN
169
170 CREATE_LEI_DETAILS(P_LOCATION_ID,
171 p_lei_information1,
172 p_lei_information2,
173 P_INFORMATION_TYPE);
174
175 CHK_STREET_NAME(p_lei_information3,p_lei_information4);
176 END IF;
177
178 --
179 END CREATE_PL_LEI_INFO;
180
181 PROCEDURE UPDATE_PL_LEI_INFO(p_lei_information_category VARCHAR2
182 ,p_location_extra_info_id NUMBER
183 ,p_lei_information1 VARCHAR2
184 ,p_lei_information2 VARCHAR2
185 ,p_lei_information3 VARCHAR2
186 ,p_lei_information4 VARCHAR2
187 ,p_lei_information5 VARCHAR2
188 ,p_lei_information6 VARCHAR2
189 ,p_lei_information7 VARCHAR2
190 ,p_lei_information8 VARCHAR2
191 ,p_lei_information9 VARCHAR2
192 ,p_lei_information10 VARCHAR2
193 ,p_lei_information11 VARCHAR2
194 ,p_lei_information12 VARCHAR2
195 ,p_lei_information13 VARCHAR2
196 ,p_lei_information14 VARCHAR2
197 ,p_lei_information15 VARCHAR2
198 ,p_lei_information16 VARCHAR2
199 ,p_lei_information17 VARCHAR2
200 ,p_lei_information18 VARCHAR2
201 ,p_lei_information19 VARCHAR2
202 ,p_lei_information20 VARCHAR2
203 ,p_lei_information21 VARCHAR2
204 ,p_lei_information22 VARCHAR2
205 ,p_lei_information23 VARCHAR2
206 ,p_lei_information24 VARCHAR2
207 ,p_lei_information25 VARCHAR2
208 ,p_lei_information26 VARCHAR2
209 ,p_lei_information27 VARCHAR2
210 ,p_lei_information28 VARCHAR2
211 ,p_lei_information29 VARCHAR2
212 ,p_lei_information30 VARCHAR2
213 ) IS
214
215 --
216 BEGIN
217 --
218 IF p_lei_information_category ='PL_CORRESPONDENCE_ADDRESS' THEN
219 UPDATE_LEI_DETAILS(P_LOCATION_EXTRA_INFO_ID,
220 p_lei_information1,
221 p_lei_information2,
222 p_lei_information_category);
223
224 CHK_STREET_NAME(p_lei_information3,p_lei_information4);
225 END IF;
226 --
227 END UPDATE_PL_LEI_INFO;
228
229 END PER_PL_LEI_INFO;