[Home] [Help]
PACKAGE BODY: APPS.PER_JOB_REQUIREMENTS_PKG2
Source
1 PACKAGE BODY PER_JOB_REQUIREMENTS_PKG2 as
2 /* $Header: pejbr02t.pkb 115.2 99/07/18 13:55:14 porting ship $ */
3
4 PROCEDURE CHECK_DUP_REQS (X_Rowid varchar2,
5 X_Position_Id number,
6 X_Analysis_Criteria_Id number) is
7
8 l_dummy varchar2(1);
9 l_found boolean;
10
11 cursor csr_exists is
12 SELECT '1'
13 FROM PER_JOB_REQUIREMENTS PJR
14 WHERE (ROWID <> X_Rowid
15 OR X_Rowid IS NULL)
16 AND PJR.POSITION_ID = X_Position_Id
17 AND PJR.ANALYSIS_CRITERIA_ID = X_Analysis_Criteria_Id;
18
19 begin
20 open csr_exists;
21 fetch csr_exists into l_dummy;
22 l_found := csr_exists%found;
23 close csr_exists;
24 if l_found then
25 hr_utility.set_message(801,'HR_6657_POS_DUP_REQ');
26 hr_utility.raise_error;
27 end if;
28 end;
29
30 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
31 X_Job_Requirement_Id IN OUT NUMBER,
32 X_Business_Group_Id NUMBER,
33 X_Analysis_Criteria_Id NUMBER,
34 X_Comments VARCHAR2,
35 X_Date_From DATE,
36 X_Date_To DATE,
37 X_Essential VARCHAR2,
38 X_Job_Id NUMBER,
39 X_Position_Id NUMBER,
40 X_Attribute_Category VARCHAR2,
41 X_Attribute1 VARCHAR2,
42 X_Attribute2 VARCHAR2,
43 X_Attribute3 VARCHAR2,
44 X_Attribute4 VARCHAR2,
45 X_Attribute5 VARCHAR2,
46 X_Attribute6 VARCHAR2,
47 X_Attribute7 VARCHAR2,
48 X_Attribute8 VARCHAR2,
49 X_Attribute9 VARCHAR2,
50 X_Attribute10 VARCHAR2,
51 X_Attribute11 VARCHAR2,
52 X_Attribute12 VARCHAR2,
53 X_Attribute13 VARCHAR2,
54 X_Attribute14 VARCHAR2,
55 X_Attribute15 VARCHAR2,
56 X_Attribute16 VARCHAR2,
57 X_Attribute17 VARCHAR2,
58 X_Attribute18 VARCHAR2,
59 X_Attribute19 VARCHAR2,
60 X_Attribute20 VARCHAR2
61 ) IS
62 CURSOR C IS SELECT rowid FROM PER_JOB_REQUIREMENTS
63
64 WHERE job_requirement_id = X_Job_Requirement_Id;
65
66
67
68
69
70 CURSOR C2 IS SELECT per_job_requirements_s.nextval FROM sys.dual;
71 BEGIN
72
73 if (X_Job_Requirement_Id is NULL) then
74 OPEN C2;
75 FETCH C2 INTO X_Job_Requirement_Id;
76 CLOSE C2;
77 end if;
78
79 CHECK_DUP_REQS(X_Rowid,
80 X_Position_Id,
81 X_Analysis_Criteria_Id);
82
83 INSERT INTO PER_JOB_REQUIREMENTS(
84 job_requirement_id,
85 business_group_id,
86 analysis_criteria_id,
87 comments,
88 date_from,
89 date_to,
90 essential,
91 job_id,
92 position_id,
93 attribute_category,
94 attribute1,
95 attribute2,
96 attribute3,
97 attribute4,
98 attribute5,
99 attribute6,
100 attribute7,
101 attribute8,
102 attribute9,
103 attribute10,
104 attribute11,
105 attribute12,
106 attribute13,
107 attribute14,
108 attribute15,
109 attribute16,
110 attribute17,
111 attribute18,
112 attribute19,
113 attribute20
114 ) VALUES (
115 X_Job_Requirement_Id,
116 X_Business_Group_Id,
117 X_Analysis_Criteria_Id,
118 X_Comments,
119 X_Date_From,
120 X_Date_To,
121 X_Essential,
122 X_Job_Id,
123 X_Position_Id,
124 X_Attribute_Category,
125 X_Attribute1,
126 X_Attribute2,
127 X_Attribute3,
128 X_Attribute4,
129 X_Attribute5,
130 X_Attribute6,
131 X_Attribute7,
132 X_Attribute8,
133 X_Attribute9,
134 X_Attribute10,
135 X_Attribute11,
136 X_Attribute12,
137 X_Attribute13,
138 X_Attribute14,
139 X_Attribute15,
140 X_Attribute16,
141 X_Attribute17,
142 X_Attribute18,
143 X_Attribute19,
144 X_Attribute20
145 );
146
147 OPEN C;
148 FETCH C INTO X_Rowid;
149 if (C%NOTFOUND) then
150 CLOSE C;
151 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
152 hr_utility.set_message_token('PROCEDURE','Insert_row');
153 hr_utility.set_message_token('STEP','1');
154 hr_utility.raise_error;
155 end if;
156 CLOSE C;
157 END Insert_Row;
158 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
159
160 X_Job_Requirement_Id NUMBER,
161 X_Business_Group_Id NUMBER,
162 X_Analysis_Criteria_Id NUMBER,
163 X_Comments VARCHAR2,
164 X_Date_From DATE,
165 X_Date_To DATE,
166 X_Essential VARCHAR2,
167 X_Job_Id NUMBER,
168 X_Position_Id NUMBER,
169 X_Attribute_Category VARCHAR2,
170 X_Attribute1 VARCHAR2,
171 X_Attribute2 VARCHAR2,
172 X_Attribute3 VARCHAR2,
173 X_Attribute4 VARCHAR2,
174 X_Attribute5 VARCHAR2,
175 X_Attribute6 VARCHAR2,
176 X_Attribute7 VARCHAR2,
177 X_Attribute8 VARCHAR2,
178 X_Attribute9 VARCHAR2,
179 X_Attribute10 VARCHAR2,
180 X_Attribute11 VARCHAR2,
181 X_Attribute12 VARCHAR2,
182 X_Attribute13 VARCHAR2,
183 X_Attribute14 VARCHAR2,
184 X_Attribute15 VARCHAR2,
185 X_Attribute16 VARCHAR2,
186 X_Attribute17 VARCHAR2,
187 X_Attribute18 VARCHAR2,
188 X_Attribute19 VARCHAR2,
189 X_Attribute20 VARCHAR2
190 ) IS
191 CURSOR C IS
192 SELECT *
193 FROM PER_JOB_REQUIREMENTS
194 WHERE rowid = X_Rowid
195 FOR UPDATE of Job_Requirement_Id NOWAIT;
196 Recinfo C%ROWTYPE;
197 BEGIN
198 OPEN C;
199 FETCH C INTO Recinfo;
200 if (C%NOTFOUND) then
201 CLOSE C;
202 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
203 hr_utility.set_message_token('PROCEDURE','lock_row');
204 hr_utility.set_message_token('STEP','1');
205 hr_utility.raise_error;
206 end if;
207 CLOSE C;
208 --
209 Recinfo.attribute20 := rtrim(Recinfo.attribute20);
210 Recinfo.comments := rtrim(Recinfo.comments);
211 Recinfo.essential := rtrim(Recinfo.essential);
212 Recinfo.attribute_category := rtrim(Recinfo.attribute_category);
213 Recinfo.attribute1 := rtrim(Recinfo.attribute1);
214 Recinfo.attribute2 := rtrim(Recinfo.attribute2);
215 Recinfo.attribute3 := rtrim(Recinfo.attribute3);
216 Recinfo.attribute4 := rtrim(Recinfo.attribute4);
217 Recinfo.attribute5 := rtrim(Recinfo.attribute5);
218 Recinfo.attribute6 := rtrim(Recinfo.attribute6);
219 Recinfo.attribute7 := rtrim(Recinfo.attribute7);
220 Recinfo.attribute8 := rtrim(Recinfo.attribute8);
221 Recinfo.attribute9 := rtrim(Recinfo.attribute9);
222 Recinfo.attribute10 := rtrim(Recinfo.attribute10);
223 Recinfo.attribute11 := rtrim(Recinfo.attribute11);
224 Recinfo.attribute12 := rtrim(Recinfo.attribute12);
225 Recinfo.attribute13 := rtrim(Recinfo.attribute13);
226 Recinfo.attribute14 := rtrim(Recinfo.attribute14);
227 Recinfo.attribute15 := rtrim(Recinfo.attribute15);
228 Recinfo.attribute16 := rtrim(Recinfo.attribute16);
229 Recinfo.attribute17 := rtrim(Recinfo.attribute17);
230 Recinfo.attribute18 := rtrim(Recinfo.attribute18);
231 Recinfo.attribute19 := rtrim(Recinfo.attribute19);
232 --
233 if (
234 ( (Recinfo.job_requirement_id = X_Job_Requirement_Id)
235 OR ( (Recinfo.job_requirement_id IS NULL)
236 AND (X_Job_Requirement_Id IS NULL)))
237 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
238 OR ( (Recinfo.business_group_id IS NULL)
239 AND (X_Business_Group_Id IS NULL)))
240 AND ( (Recinfo.analysis_criteria_id = X_Analysis_Criteria_Id)
241 OR ( (Recinfo.analysis_criteria_id IS NULL)
242 AND (X_Analysis_Criteria_Id IS NULL)))
243 AND ( (Recinfo.comments = X_Comments)
244 OR ( (Recinfo.comments IS NULL)
245 AND (X_Comments IS NULL)))
246 AND ( (Recinfo.date_from = X_Date_From)
247 OR ( (Recinfo.date_from IS NULL)
248 AND (X_Date_From IS NULL)))
249 AND ( (Recinfo.date_to = X_Date_To)
250 OR ( (Recinfo.date_to IS NULL)
251 AND (X_Date_To IS NULL)))
252 AND ( (Recinfo.essential = X_Essential)
253 OR ( (Recinfo.essential IS NULL)
254 AND (X_Essential IS NULL)))
255 AND ( (Recinfo.job_id = X_Job_Id)
256 OR ( (Recinfo.job_id IS NULL)
257 AND (X_Job_Id IS NULL)))
258 AND ( (Recinfo.position_id = X_Position_Id)
259 OR ( (Recinfo.position_id IS NULL)
260 AND (X_Position_Id IS NULL)))
261 AND ( (Recinfo.attribute_category = X_Attribute_Category)
262 OR ( (Recinfo.attribute_category IS NULL)
263 AND (X_Attribute_Category IS NULL)))
264 AND ( (Recinfo.attribute1 = X_Attribute1)
265 OR ( (Recinfo.attribute1 IS NULL)
266 AND (X_Attribute1 IS NULL)))
267 AND ( (Recinfo.attribute2 = X_Attribute2)
268 OR ( (Recinfo.attribute2 IS NULL)
269 AND (X_Attribute2 IS NULL)))
270 AND ( (Recinfo.attribute3 = X_Attribute3)
271 OR ( (Recinfo.attribute3 IS NULL)
272 AND (X_Attribute3 IS NULL)))
273 AND ( (Recinfo.attribute4 = X_Attribute4)
274 OR ( (Recinfo.attribute4 IS NULL)
275 AND (X_Attribute4 IS NULL)))
276 AND ( (Recinfo.attribute5 = X_Attribute5)
277 OR ( (Recinfo.attribute5 IS NULL)
278 AND (X_Attribute5 IS NULL)))
279 AND ( (Recinfo.attribute6 = X_Attribute6)
280 OR ( (Recinfo.attribute6 IS NULL)
281 AND (X_Attribute6 IS NULL)))
282 AND ( (Recinfo.attribute7 = X_Attribute7)
283 OR ( (Recinfo.attribute7 IS NULL)
284 AND (X_Attribute7 IS NULL)))
285 AND ( (Recinfo.attribute8 = X_Attribute8)
286 OR ( (Recinfo.attribute8 IS NULL)
287 AND (X_Attribute8 IS NULL)))
288 AND ( (Recinfo.attribute9 = X_Attribute9)
289 OR ( (Recinfo.attribute9 IS NULL)
290 AND (X_Attribute9 IS NULL)))
291 AND ( (Recinfo.attribute10 = X_Attribute10)
292 OR ( (Recinfo.attribute10 IS NULL)
293 AND (X_Attribute10 IS NULL)))
294 AND ( (Recinfo.attribute11 = X_Attribute11)
295 OR ( (Recinfo.attribute11 IS NULL)
296 AND (X_Attribute11 IS NULL)))
297 AND ( (Recinfo.attribute12 = X_Attribute12)
298 OR ( (Recinfo.attribute12 IS NULL)
299 AND (X_Attribute12 IS NULL)))
300 AND ( (Recinfo.attribute13 = X_Attribute13)
301 OR ( (Recinfo.attribute13 IS NULL)
302 AND (X_Attribute13 IS NULL)))
303 AND ( (Recinfo.attribute14 = X_Attribute14)
304 OR ( (Recinfo.attribute14 IS NULL)
305 AND (X_Attribute14 IS NULL)))
306 AND ( (Recinfo.attribute15 = X_Attribute15)
307 OR ( (Recinfo.attribute15 IS NULL)
308 AND (X_Attribute15 IS NULL)))
309 AND ( (Recinfo.attribute16 = X_Attribute16)
310 OR ( (Recinfo.attribute16 IS NULL)
311 AND (X_Attribute16 IS NULL)))
312 AND ( (Recinfo.attribute17 = X_Attribute17)
313 OR ( (Recinfo.attribute17 IS NULL)
314 AND (X_Attribute17 IS NULL)))
315 AND ( (Recinfo.attribute18 = X_Attribute18)
316 OR ( (Recinfo.attribute18 IS NULL)
317 AND (X_Attribute18 IS NULL)))
318 AND ( (Recinfo.attribute19 = X_Attribute19)
319 OR ( (Recinfo.attribute19 IS NULL)
320 AND (X_Attribute19 IS NULL)))
321 AND ( (Recinfo.attribute20 = X_Attribute20)
322 OR ( (Recinfo.attribute20 IS NULL)
323 AND (X_Attribute20 IS NULL)))
324 ) then
325 return;
326 else
327 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
328 APP_EXCEPTION.RAISE_EXCEPTION;
329 end if;
330 END Lock_Row;
331
332 PROCEDURE Update_Row(X_Rowid VARCHAR2,
333 X_Job_Requirement_Id NUMBER,
334 X_Business_Group_Id NUMBER,
335 X_Analysis_Criteria_Id NUMBER,
336 X_Comments VARCHAR2,
337 X_Date_From DATE,
338 X_Date_To DATE,
339 X_Essential VARCHAR2,
340 X_Job_Id NUMBER,
341 X_Position_Id NUMBER,
342 X_Attribute_Category VARCHAR2,
343 X_Attribute1 VARCHAR2,
344 X_Attribute2 VARCHAR2,
345 X_Attribute3 VARCHAR2,
346 X_Attribute4 VARCHAR2,
347 X_Attribute5 VARCHAR2,
348 X_Attribute6 VARCHAR2,
349 X_Attribute7 VARCHAR2,
350 X_Attribute8 VARCHAR2,
351 X_Attribute9 VARCHAR2,
352 X_Attribute10 VARCHAR2,
353 X_Attribute11 VARCHAR2,
354 X_Attribute12 VARCHAR2,
355 X_Attribute13 VARCHAR2,
359 X_Attribute17 VARCHAR2,
356 X_Attribute14 VARCHAR2,
357 X_Attribute15 VARCHAR2,
358 X_Attribute16 VARCHAR2,
360 X_Attribute18 VARCHAR2,
361 X_Attribute19 VARCHAR2,
362 X_Attribute20 VARCHAR2
363 ) IS
364 BEGIN
365
366 CHECK_DUP_REQS(X_Rowid,
367 X_Position_Id,
368 X_Analysis_Criteria_Id);
369
370 UPDATE PER_JOB_REQUIREMENTS
371 SET
372
373 job_requirement_id = X_Job_Requirement_Id,
374 business_group_id = X_Business_Group_Id,
375 analysis_criteria_id = X_Analysis_Criteria_Id,
376 comments = X_Comments,
377 date_from = X_Date_From,
378 date_to = X_Date_To,
379 essential = X_Essential,
380 job_id = X_Job_Id,
381 position_id = X_Position_Id,
382 attribute_category = X_Attribute_Category,
383 attribute1 = X_Attribute1,
384 attribute2 = X_Attribute2,
385 attribute3 = X_Attribute3,
386 attribute4 = X_Attribute4,
387 attribute5 = X_Attribute5,
388 attribute6 = X_Attribute6,
389 attribute7 = X_Attribute7,
390 attribute8 = X_Attribute8,
391 attribute9 = X_Attribute9,
392 attribute10 = X_Attribute10,
393 attribute11 = X_Attribute11,
394 attribute12 = X_Attribute12,
395 attribute13 = X_Attribute13,
396 attribute14 = X_Attribute14,
397 attribute15 = X_Attribute15,
398 attribute16 = X_Attribute16,
399 attribute17 = X_Attribute17,
400 attribute18 = X_Attribute18,
401 attribute19 = X_Attribute19,
402 attribute20 = X_Attribute20
403 WHERE rowid = X_rowid;
404
405 if (SQL%NOTFOUND) then
406 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
407 hr_utility.set_message_token('PROCEDURE','update_row');
408 hr_utility.set_message_token('STEP','1');
409 hr_utility.raise_error;
410 end if;
411
412 END Update_Row;
413
414 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
415 BEGIN
416 DELETE FROM PER_JOB_REQUIREMENTS
417 WHERE rowid = X_Rowid;
418
419 if (SQL%NOTFOUND) then
420 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
421 hr_utility.set_message_token('PROCEDURE','delete_row');
422 hr_utility.set_message_token('STEP','1');
423 hr_utility.raise_error;
424 end if;
425 END Delete_Row;
426
427 END PER_JOB_REQUIREMENTS_PKG2;