[Home] [Help]
PACKAGE BODY: APPS.GHR_POI_FLEX_DDF
Source
1 Package Body ghr_poi_flex_ddf as
2 /* $Header: ghpoiddf.pkb 120.7 2005/06/28 11:24:38 vravikan noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ghr_poi_flex_ddf.'; -- Global package name
9
10
11
12 --
13 -- ----------------------------------------------------------------------------
14 -- |-------------------------------< ddf >-------------------------------------|
15 -- ----------------------------------------------------------------------------
16 --
17 procedure ddf
18 (
19 p_position_extra_info_id in number ,
20 p_position_id in number ,
21 p_information_type in varchar2 ,
22 p_request_id in number ,
23 p_program_application_id in number ,
24 p_program_id in number ,
25 p_program_update_date in date ,
26 p_poei_attribute_category in varchar2 ,
27 p_poei_attribute1 in varchar2 ,
28 p_poei_attribute2 in varchar2 ,
29 p_poei_attribute3 in varchar2 ,
30 p_poei_attribute4 in varchar2 ,
31 p_poei_attribute5 in varchar2 ,
32 p_poei_attribute6 in varchar2 ,
33 p_poei_attribute7 in varchar2 ,
34 p_poei_attribute8 in varchar2 ,
35 p_poei_attribute9 in varchar2 ,
36 p_poei_attribute10 in varchar2 ,
37 p_poei_attribute11 in varchar2 ,
38 p_poei_attribute12 in varchar2 ,
39 p_poei_attribute13 in varchar2 ,
40 p_poei_attribute14 in varchar2 ,
41 p_poei_attribute15 in varchar2 ,
42 p_poei_attribute16 in varchar2 ,
43 p_poei_attribute17 in varchar2 ,
44 p_poei_attribute18 in varchar2 ,
45 p_poei_attribute19 in varchar2 ,
46 p_poei_attribute20 in varchar2 ,
47 p_poei_information_category in varchar2 ,
48 p_poei_information1 in varchar2 ,
49 p_poei_information2 in varchar2 ,
50 p_poei_information3 in varchar2 ,
51 p_poei_information4 in varchar2 ,
52 p_poei_information5 in varchar2 ,
53 p_poei_information6 in varchar2 ,
54 p_poei_information7 in varchar2 ,
55 p_poei_information8 in varchar2 ,
56 p_poei_information9 in varchar2 ,
57 p_poei_information10 in varchar2 ,
58 p_poei_information11 in varchar2 ,
59 p_poei_information12 in varchar2 ,
60 p_poei_information13 in varchar2 ,
61 p_poei_information14 in varchar2 ,
62 p_poei_information15 in varchar2 ,
63 p_poei_information16 in varchar2 ,
64 p_poei_information17 in varchar2 ,
65 p_poei_information18 in varchar2 ,
66 p_poei_information19 in varchar2 ,
67 p_poei_information20 in varchar2 ,
68 p_poei_information21 in varchar2 ,
69 p_poei_information22 in varchar2 ,
70 p_poei_information23 in varchar2 ,
71 p_poei_information24 in varchar2 ,
72 p_poei_information25 in varchar2 ,
73 p_poei_information26 in varchar2 ,
74 p_poei_information27 in varchar2 ,
75 p_poei_information28 in varchar2 ,
76 p_poei_information29 in varchar2 ,
77 p_poei_information30 in varchar2
78 )
79 is
80 --
81 l_proc varchar2(72) := g_package||'ddf';
82 l_error exception;
83 l_date_from date;
84 --
85 cursor c_pos_segments(p_session_date in fnd_sessions.effective_date%type) is
86 select information6,segment1,segment2,segment3,segment4,
87 segment5,segment6,segment7
88 from per_position_definitions pdf, hr_all_positions_f pos
89 where
90 pos.position_definition_id = pdf.position_definition_id
91 and pos.position_id = p_position_id
92 and p_session_date between pos.effective_start_date and
93 pos.effective_end_date;
94 cursor c_get_session_date is
95 select trunc(effective_date) session_date
96 from fnd_sessions
97 where session_id = (select userenv('sessionid') from dual);
98
99 l_session_date date;
100 Begin
101 hr_utility.set_location('Entering:'||l_proc, 5);
102
103 if ghr_utility.is_ghr = 'TRUE' then
104 -- Added by Dinkar Karumuri
105 if p_information_type is not null
106 then
107 if p_information_type = 'GHR_US_POSITION_DESCRIPTION'
108 then
109 chk_date_from
110 (p_position_description_id => p_poei_information3
111 ,p_date_from => p_poei_information1
112 );
113 chk_pos_desc_id
114 (p_position_extra_info_id => p_position_extra_info_id
115 ,p_pos_desc_id => p_poei_information3
116 );
117 chk_date_to
118 (p_position_extra_info_id => p_position_extra_info_id
119 ,p_date_to => p_poei_information2
120 ,p_date_from => p_poei_information1
121 );
122 end if;
123 end if;
124
125 if ghr_utility.is_ghr_nfc = 'TRUE' then
126 -- Get Session Date
127 l_session_date := trunc(sysdate);
128 for ses_rec in c_get_session_date loop
129 l_session_date := ses_rec.session_date;
130 end loop;
131 -- Fetch the current segments from hr_all_positions_f
132 for c_pos_rec in c_pos_segments(l_session_date) loop
133 -- Do not allow modification of EIT segments which are populated
134 -- from the Position KFF
135 -- GHR_US_POS_GRP1 --> Personnel Officer ID
136
137 if p_information_type = 'GHR_US_POS_GRP1' then
138 if c_pos_rec.segment4 <> p_poei_information3 then
139 hr_utility.set_message(8301, 'GHR_38945_NFC_ERROR1');
140 hr_utility.raise_error;
141 end if;
142 end if;
143 -- GHR_US_POS_GRP3 --> NFC Agency Code
144 if p_information_type = 'GHR_US_POS_GRP3' then
145 if c_pos_rec.segment3 <> p_poei_information21 then
146 hr_utility.set_message(8301, 'GHR_38947_NFC_ERROR3');
147 hr_utility.raise_error;
148 end if;
149 end if;
150 -- GHR_US_POS_VALID_GRADE --> Grade From
151 if p_information_type = 'GHR_US_POS_VALID_GRADE' then
152 if c_pos_rec.segment7 <> p_poei_information3 then
153 hr_utility.set_message(8301, 'GHR_38946_NFC_ERROR2');
154 hr_utility.raise_error;
155 end if;
156 end if;
157 end loop;
158
159 end if; -- ghr_utility.is_ghr_nfc
160 end if; -- ghr_utility.is_ghr
161 hr_utility.set_location(' Leaving:'||l_proc, 10);
162 exception
163 when l_error then
164 hr_utility.set_message(999, 'HR_9999_FLEX_INV_INFO_ARG');
165 hr_utility.raise_error;
166 hr_utility.set_location(' Leaving:'||l_proc, 10);
167
168 end ddf;
169
170
171 procedure create_ddf
172 (
173 p_position_id in number ,
174 p_information_type in varchar2 ,
175 p_poei_attribute_category in varchar2 ,
176 p_poei_attribute1 in varchar2 ,
177 p_poei_attribute2 in varchar2 ,
178 p_poei_attribute3 in varchar2 ,
179 p_poei_attribute4 in varchar2 ,
180 p_poei_attribute5 in varchar2 ,
181 p_poei_attribute6 in varchar2 ,
182 p_poei_attribute7 in varchar2 ,
183 p_poei_attribute8 in varchar2 ,
184 p_poei_attribute9 in varchar2 ,
185 p_poei_attribute10 in varchar2 ,
186 p_poei_attribute11 in varchar2 ,
187 p_poei_attribute12 in varchar2 ,
188 p_poei_attribute13 in varchar2 ,
189 p_poei_attribute14 in varchar2 ,
190 p_poei_attribute15 in varchar2 ,
191 p_poei_attribute16 in varchar2 ,
192 p_poei_attribute17 in varchar2 ,
193 p_poei_attribute18 in varchar2 ,
194 p_poei_attribute19 in varchar2 ,
195 p_poei_attribute20 in varchar2 ,
196 p_poei_information_category in varchar2 ,
197 p_poei_information1 in varchar2 ,
198 p_poei_information2 in varchar2 ,
199 p_poei_information3 in varchar2 ,
200 p_poei_information4 in varchar2 ,
201 p_poei_information5 in varchar2 ,
202 p_poei_information6 in varchar2 ,
203 p_poei_information7 in varchar2 ,
204 p_poei_information8 in varchar2 ,
205 p_poei_information9 in varchar2 ,
206 p_poei_information10 in varchar2 ,
207 p_poei_information11 in varchar2 ,
208 p_poei_information12 in varchar2 ,
209 p_poei_information13 in varchar2 ,
210 p_poei_information14 in varchar2 ,
211 p_poei_information15 in varchar2 ,
212 p_poei_information16 in varchar2 ,
213 p_poei_information17 in varchar2 ,
214 p_poei_information18 in varchar2 ,
215 p_poei_information19 in varchar2 ,
216 p_poei_information20 in varchar2 ,
217 p_poei_information21 in varchar2 ,
218 p_poei_information22 in varchar2 ,
219 p_poei_information23 in varchar2 ,
220 p_poei_information24 in varchar2 ,
221 p_poei_information25 in varchar2 ,
222 p_poei_information26 in varchar2 ,
223 p_poei_information27 in varchar2 ,
224 p_poei_information28 in varchar2 ,
225 p_poei_information29 in varchar2 ,
226 p_poei_information30 in varchar2
227 )
228 is
229 --
230 l_proc varchar2(72) := g_package||'create_ddf';
231 l_error exception;
232 l_date_from date;
233 --
234 cursor c_pos_segments(p_session_date in date) is
235 select information6,segment1,segment2,segment3,segment4,
236 segment5,segment6,segment7
237 from per_position_definitions pdf, hr_all_positions_f pos
238 where pos.position_definition_id = pdf.position_definition_id
239 and pos.position_id = p_position_id
240 and p_session_date between pos.effective_start_date
241 and pos.effective_end_date;
242 cursor c_get_session_date is
243 select trunc(effective_date) session_date
244 from fnd_sessions
245 where session_id = (select userenv('sessionid') from dual);
246 l_session_date date;
247 Begin
248 hr_utility.set_location('Entering:'||l_proc, 5);
249
250 if ghr_utility.is_ghr_nfc = 'TRUE' then
251 -- Get Session Date
252 l_session_date := trunc(sysdate);
253 for ses_rec in c_get_session_date loop
254 l_session_date := ses_rec.session_date;
255 end loop;
256
257 -- Fetch the current segments from hr_all_positions_f
258 for c_pos_rec in c_pos_segments(l_session_date) loop
259 -- Do not allow modification of EIT segments which are populated
260 -- from the Position KFF
261 -- GHR_US_POS_GRP1 --> Personnel Officer ID
262 if p_information_type = 'GHR_US_POS_GRP1' then
263 if c_pos_rec.segment4 <> p_poei_information3 then
264 hr_utility.set_message(8301, 'GHR_38945_NFC_ERROR1');
265 hr_utility.raise_error;
266 end if;
267 end if;
268 -- GHR_US_POS_GRP3 --> NFC Agency Code
269 if p_information_type = 'GHR_US_POS_GRP3' then
270 if c_pos_rec.segment3 <> p_poei_information21 then
271 hr_utility.set_message(8301, 'GHR_38947_NFC_ERROR3');
272 hr_utility.raise_error;
273 end if;
274 end if;
275 -- GHR_US_POS_VALID_GRADE --> Grade From
276 if p_information_type = 'GHR_US_POS_VALID_GRADE' then
277 if c_pos_rec.segment7 <> p_poei_information3 then
278 hr_utility.set_message(8301, 'GHR_38946_NFC_ERROR2');
279 hr_utility.raise_error;
280 end if;
281 end if;
282 end loop;
283 end if; -- ghr_utility.is_nfc_ghr
284 hr_utility.set_location(' Leaving:'||l_proc, 10);
285 exception
286 when l_error then
287 hr_utility.set_message(999, 'HR_9999_FLEX_INV_INFO_ARG');
288 hr_utility.raise_error;
289 hr_utility.set_location(' Leaving:'||l_proc, 10);
290
291 end create_ddf;
292
293
294 -- ---------------------------------------------------------------------------------------------------
295 -- |------------------------------- < chk_date_from > ------------------------------------------------|
296 -- ---------------------------------------------------------------------------------------------------
297 --
298 procedure chk_date_from
299 (p_position_description_id in ghr_position_descriptions.position_description_id%TYPE
300 ,p_date_from in per_position_extra_info.poei_information1%TYPE
301 ) is
302 --
303 l_proc varchar2(72) := 'chk_date_from';
304 l_api_updating boolean;
305 --
306 begin
307 hr_utility.set_location('Entering: '|| l_proc, 10);
308 --
309 if p_position_description_id is not null and p_date_from is null
310 then
311 hr_utility.set_message(8301, 'GHR_DATE_FROM_INVALID');
312 hr_utility.raise_error;
313 end if;
314 hr_utility.set_location('Leaving: '|| l_proc, 20);
315 end chk_date_from;
316 --
317 -- ---------------------------------------------------------------------------------------------------
318 -- |------------------------------- < chk_pos_desc_id > ---------------------------------------------|
319 -- ---------------------------------------------------------------------------------------------------
320 --
321 procedure chk_pos_desc_id
322 (p_position_extra_info_id in per_position_extra_info.position_extra_info_id%TYPE
323 ,p_pos_desc_id in per_position_extra_info.poei_information3%TYPE
324 ) is
325 --
326 l_exists varchar2(1);
327 l_proc varchar2(72) := 'chk_pos_desc_id';
328 l_api_updating boolean;
329 cursor csr_pos_desc is
330 select null
331 from ghr_position_descriptions
332 where position_description_id = to_number(p_pos_desc_id);
333 --
334 begin
335 hr_utility.set_location('Entering: '|| l_proc, 10);
336 --
337 -- Check mandatory parameters have been set
338 --
339 hr_api.mandatory_arg_error
340 (p_api_name => l_proc
341 ,p_argument => 'position description id'
342 ,p_argument_value => p_pos_desc_id
343 );
344 if p_pos_desc_id is null
345 then
346 hr_utility.set_message(8301, 'GHR_POS_DESC_ID_INVALID');
347 hr_utility.raise_error;
348 end if;
349 open csr_pos_desc;
350 fetch csr_pos_desc into l_exists;
351 if csr_pos_desc%notfound then
352 close csr_pos_desc;
353 hr_utility.set_message(8301, 'GHR_PD_FOREIGN_KEY_CONSTRAINT');
354 hr_utility.raise_error;
355 end if ;
356 hr_utility.set_location(' Leaving: '|| l_proc, 20);
357 end chk_pos_desc_id;
358 --
359 -- ---------------------------------------------------------------------------------------------------
360 -- |------------------------------------ < chk_date_to >---------------------------------------------|
361 -- ---------------------------------------------------------------------------------------------------
362 --
363 procedure chk_date_to
364 (p_position_extra_info_id in per_position_extra_info.position_extra_info_id%TYPE
365 ,p_date_to in per_position_extra_info.poei_information2%TYPE
366 ,p_date_from in per_position_extra_info.poei_information1%TYPE
367 ) is
368 --
369 l_proc varchar2(72) := 'chk_date_to';
370 l_api_updating boolean;
371 --
372 begin
373 hr_utility.set_location('Entering: '|| l_proc, 10);
374 if p_date_to is not null and p_date_from is not null
375 then
376 if not(fnd_date.canonical_to_date(p_date_to) > fnd_date.canonical_to_date(p_date_from))
377 then
378 hr_utility.set_message(8301, 'GHR_DATE_TO_INVALID');
379 hr_utility.raise_error;
380 end if;
381 end if;
382 hr_utility.set_location('Leaving: '|| l_proc, 10);
383 end chk_date_to;
384 --
385
386 --
387 end ghr_poi_flex_ddf;