[Home] [Help]
PACKAGE BODY: APPS.PER_MM_VALID_GRADES_PKG
Source
1 package body PER_MM_VALID_GRADES_PKG as
2 /* $Header: pemmv04t.pkb 120.0.12010000.2 2008/08/06 09:16:03 ubhat ship $ */
3 --
4 --
5 procedure insert_row
6 (p_mass_move_id in number,
7 p_position_id in number,
8 p_target_grade_id in number,
9 p_attribute_category in varchar2,
10 p_attribute1 in varchar2,
11 p_attribute2 in varchar2,
12 p_attribute3 in varchar2,
13 p_attribute4 in varchar2,
14 p_attribute5 in varchar2,
15 p_attribute6 in varchar2,
16 p_attribute7 in varchar2,
17 p_attribute8 in varchar2,
18 p_attribute9 in varchar2,
19 p_attribute10 in varchar2,
20 p_attribute11 in varchar2,
21 p_attribute12 in varchar2,
22 p_attribute13 in varchar2,
23 p_attribute14 in varchar2,
24 p_attribute15 in varchar2,
25 p_attribute16 in varchar2,
26 p_attribute17 in varchar2,
27 p_attribute18 in varchar2,
28 p_attribute19 in varchar2,
29 p_attribute20 in varchar2)
30
31 is
32 cursor c is
33 select 'x'
34 from
35 per_mm_valid_grades
36 where mass_move_id = p_mass_move_id;
37 l_dummy varchar(1);
38
39 begin
40 insert into per_mm_valid_grades
41 (mass_move_id,
42 position_id,
43 target_grade_id,
44 attribute_category,
45 attribute1,
46 attribute2,
47 attribute3,
48 attribute4,
49 attribute5,
50 attribute6,
51 attribute7,
52 attribute8,
53 attribute9,
54 attribute10,
55 attribute11,
56 attribute12,
57 attribute13,
58 attribute14,
59 attribute15,
60 attribute16,
61 attribute17,
62 attribute18,
63 attribute19,
64 attribute20)
65 values
66 (p_mass_move_id,
67 p_position_id,
68 p_target_grade_id,
69 p_attribute_category,
70 p_attribute1,
71 p_attribute2,
72 p_attribute3,
73 p_attribute4,
74 p_attribute5,
75 p_attribute6,
76 p_attribute7,
77 p_attribute8,
78 p_attribute9,
79 p_attribute10,
80 p_attribute11,
81 p_attribute12,
82 p_attribute13,
83 p_attribute14,
84 p_attribute15,
85 p_attribute16,
86 p_attribute17,
87 p_attribute18,
88 p_attribute19,
89 p_attribute20);
90 open c;
91 fetch c into l_dummy;
92 if (c%notfound) then
93 close c;
94 raise no_data_found;
95 end if;
96 close c;
97 end insert_row;
98 --
99 --
100 procedure update_row
101 (p_target_grade_id in number,
102 p_attribute1 in varchar2,
103 p_attribute2 in varchar2,
104 p_attribute3 in varchar2,
105 p_attribute4 in varchar2,
106 p_attribute5 in varchar2,
107 p_attribute6 in varchar2,
108 p_attribute7 in varchar2,
109 p_attribute8 in varchar2,
110 p_attribute9 in varchar2,
111 p_attribute10 in varchar2,
112 p_attribute11 in varchar2,
113 p_attribute12 in varchar2,
114 p_attribute13 in varchar2,
115 p_attribute14 in varchar2,
116 p_attribute15 in varchar2,
117 p_attribute16 in varchar2,
118 p_attribute17 in varchar2,
119 p_attribute18 in varchar2,
120 p_attribute19 in varchar2,
121 p_attribute20 in varchar2,
122 p_row_id in varchar2)
123 is
124 begin
125 update per_mm_valid_grades
126 set
127 target_grade_id = p_target_grade_id,
128 attribute1 = p_attribute1,
129 attribute2 = p_attribute2,
130 attribute3 = p_attribute3,
131 attribute4 = p_attribute4,
132 attribute5 = p_attribute5,
133 attribute6 = p_attribute6,
134 attribute7 = p_attribute7,
135 attribute8 = p_attribute8,
136 attribute9 = p_attribute9,
137 attribute10 = p_attribute10,
138 attribute11 = p_attribute11,
139 attribute12 = p_attribute12,
140 attribute13 = p_attribute13,
141 attribute14 = p_attribute14,
142 attribute15 = p_attribute15,
143 attribute16 = p_attribute16,
144 attribute17 = p_attribute17,
145 attribute18 = p_attribute18,
146 attribute19 = p_attribute19,
147 attribute20 = p_attribute20
148 where rowid = p_row_id;
149 if (sql%notfound) then
150 raise no_data_found;
151 end if;
152 end update_row;
153 --
154 --
155 procedure delete_row
156 (p_row_id in varchar2)
157
158 is
159 begin
160 delete from per_mm_valid_grades
161 where rowid = p_row_id;
162 if (sql%notfound) then
163 raise no_data_found;
164 end if;
165 end delete_row;
166 --
167 --
168 procedure load_rows
169 (p_mass_move_id in number)
170 is
171 l_effective_date per_mass_moves.effective_date%type;
172
173 BEGIN
174 hr_utility.set_location('Inside Load_rows. before insert ',10);
175 -- added for 7214283
176 select effective_date into l_effective_date
177 from per_mass_moves where mass_move_id = p_mass_move_id;
178 -- added for 7214283
179
180 insert into per_mm_valid_grades
181 (MASS_MOVE_ID,
182 position_id,
183 TARGET_GRADE_ID,
184 ATTRIBUTE_CATEGORY,
185 ATTRIBUTE1,
186 ATTRIBUTE2,
187 ATTRIBUTE3,
188 ATTRIBUTE4,
189 ATTRIBUTE5,
190 ATTRIBUTE6,
191 ATTRIBUTE7,
192 ATTRIBUTE8,
193 ATTRIBUTE9,
194 ATTRIBUTE10,
195 ATTRIBUTE11,
196 ATTRIBUTE12,
197 ATTRIBUTE13,
198 ATTRIBUTE14,
199 ATTRIBUTE15,
200 ATTRIBUTE16,
201 ATTRIBUTE17,
202 ATTRIBUTE18,
203 ATTRIBUTE19,
204 ATTRIBUTE20
205 )
206 select
207 p_mass_move_id,
208 mmpos.position_id,
209 vgr.GRADE_ID,
210 vgr.ATTRIBUTE_CATEGORY,
211 vgr.ATTRIBUTE1,
212 vgr.ATTRIBUTE2,
213 vgr.ATTRIBUTE3,
214 vgr.ATTRIBUTE4,
215 vgr.ATTRIBUTE5,
216 vgr.ATTRIBUTE6,
217 vgr.ATTRIBUTE7,
218 vgr.ATTRIBUTE8,
219 vgr.ATTRIBUTE9,
220 vgr.ATTRIBUTE10,
221 vgr.ATTRIBUTE11,
222 vgr.ATTRIBUTE12,
223 vgr.ATTRIBUTE13,
224 vgr.ATTRIBUTE14,
225 vgr.ATTRIBUTE15,
226 vgr.ATTRIBUTE16,
227 vgr.ATTRIBUTE17,
228 vgr.ATTRIBUTE18,
229 vgr.ATTRIBUTE19,
230 vgr.ATTRIBUTE20
231 from per_valid_grades vgr,
232 per_mm_positions mmpos
233 where vgr.position_id = mmpos.position_id
234 and mmpos.mass_move_id = p_mass_move_id
235 and l_effective_date between vgr.date_from and vgr.date_to; -- This condition added for 7214283
236
237 hr_utility.set_location('Load_rows. After insert',20);
238
239 exception
240 when no_data_found THEN
241 hr_utility.set_location(' Inside No data found ',30);
242 null;
243 when others THEN
244 hr_utility.set_location('Inside Others ',40);
245 hr_utility.set_location(sqlerrm,50);
246 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
247 hr_utility.set_message_token('PROCEDURE','per_mm_valid_grades_pkg.load_rows');
248 hr_utility.set_message_token('STEP','1');
249 hr_utility.raise_error;
250 end load_rows;
251 --
252 --
253 procedure lock_row
254 (p_mass_move_id in number,
255 p_position_id in number,
256 p_target_grade_id in number,
257 p_attribute_category in varchar2,
258 p_attribute1 in varchar2,
259 p_attribute2 in varchar2,
260 p_attribute3 in varchar2,
261 p_attribute4 in varchar2,
262 p_attribute5 in varchar2,
263 p_attribute6 in varchar2,
264 p_attribute7 in varchar2,
265 p_attribute8 in varchar2,
266 p_attribute9 in varchar2,
267 p_attribute10 in varchar2,
268 p_attribute11 in varchar2,
269 p_attribute12 in varchar2,
270 p_attribute13 in varchar2,
271 p_attribute14 in varchar2,
272 p_attribute15 in varchar2,
273 p_attribute16 in varchar2,
274 p_attribute17 in varchar2,
275 p_attribute18 in varchar2,
276 p_attribute19 in varchar2,
277 p_attribute20 in varchar2,
278 p_row_id in varchar2)
279
280 is
281 counter number;
282 cursor c is
283 select *
284 from per_mm_valid_grades
285 where rowid = p_row_id
286 for update of target_grade_id nowait;
287 recinfo c%rowtype;
288 begin
289 counter := 0;
290 loop
291 begin
292 counter := counter + 1;
293 open c;
294 fetch c into recinfo;
295 if (c%notfound) then
296 close c;
297 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
298 hr_utility.set_message_token('PROCEDURE','per_mm_valid_grades_pkg.lock_rows');
299 hr_utility.set_message_token('STEP','1');
300 hr_utility.raise_error;
301 end if;
302 close c;
303 if (
304 (recinfo.mass_move_id = p_mass_move_id)
305 AND
306 (recinfo.position_id = p_position_id)
307 AND
308 (recinfo.target_grade_id = p_target_grade_id)
309 AND(
310 (recinfo.attribute1 = p_attribute1)
311 OR ( (recinfo.attribute1 is null)
312 AND (p_attribute1 is null)))
313 AND(
314 (recinfo.attribute2 = p_attribute2)
315 OR ( (recinfo.attribute2 is null)
316 AND (p_attribute2 is null)))
317 AND(
318 (recinfo.attribute3 = p_attribute3)
319 OR ( (recinfo.attribute3 is null)
320 AND (p_attribute3 is null)))
321 AND(
322 (recinfo.attribute4 = p_attribute4)
323 OR ( (recinfo.attribute4 is null)
324 AND (p_attribute4 is null)))
325 AND(
326 (recinfo.attribute5 = p_attribute5)
327 OR ( (recinfo.attribute5 is null)
328 AND (p_attribute5 is null)))
329 AND(
330 (recinfo.attribute6 = p_attribute6)
331 OR ( (recinfo.attribute6 is null)
332 AND (p_attribute6 is null)))
333 AND(
334 (recinfo.attribute7 = p_attribute7)
335 OR ( (recinfo.attribute7 is null)
336 AND (p_attribute7 is null)))
337 AND(
338 (recinfo.attribute8 = p_attribute8)
339 OR ( (recinfo.attribute8 is null)
340 AND (p_attribute8 is null)))
341 AND(
342 (recinfo.attribute9 = p_attribute9)
343 OR ( (recinfo.attribute9 is null)
344 AND (p_attribute9 is null)))
345 AND(
346 (recinfo.attribute10 = p_attribute10)
347 OR ( (recinfo.attribute10 is null)
348 AND (p_attribute10 is null)))
349 AND(
350 (recinfo.attribute11 = p_attribute11)
351 OR ( (recinfo.attribute11 is null)
352 AND (p_attribute11 is null)))
353 AND(
354 (recinfo.attribute12 = p_attribute12)
355 OR ( (recinfo.attribute12 is null)
356 AND (p_attribute12 is null)))
357 AND(
358 (recinfo.attribute13 = p_attribute13)
359 OR ( (recinfo.attribute13 is null)
360 AND (p_attribute13 is null)))
361 AND(
362 (recinfo.attribute14 = p_attribute14)
363 OR ( (recinfo.attribute14 is null)
364 AND (p_attribute14 is null)))
365 AND(
366 (recinfo.attribute15 = p_attribute15)
367 OR ( (recinfo.attribute15 is null)
368 AND (p_attribute15 is null)))
369 AND(
370 (recinfo.attribute16 = p_attribute16)
371 OR ( (recinfo.attribute16 is null)
372 AND (p_attribute16 is null)))
373 AND(
374 (recinfo.attribute17 = p_attribute17)
375 OR ( (recinfo.attribute17 is null)
376 AND (p_attribute17 is null)))
377 AND(
378 (recinfo.attribute18 = p_attribute18)
379 OR ( (recinfo.attribute18 is null)
380 AND (p_attribute18 is null)))
381 AND(
382 (recinfo.attribute19 = p_attribute19)
383 OR ( (recinfo.attribute19 is null)
384 AND (p_attribute19 is null)))
385 AND(
386 (recinfo.attribute20 = p_attribute20)
387 OR ( (recinfo.attribute20 is null)
388 AND (p_attribute20 is null)))
389 ) then
390 return;
391 else
392 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
393 hr_utility.set_message_token('PROCEDURE','per_mm_valid_grades_pkg.lock_rows');
394 hr_utility.set_message_token('STEP','2');
395 hr_utility.raise_error;
396 end if;
397 exception
398 when app_exceptions.record_lock_exception then
399 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
400 hr_utility.set_message_token('PROCEDURE','per_mm_valid_grades_pkg.lock_rows');
401 hr_utility.set_message_token('STEP','3');
402 hr_utility.raise_error;
403 end;
404 end loop;
405 end lock_row ;
406 --
407 --
408 end per_mm_valid_grades_pkg;
409