[Home] [Help]
PACKAGE BODY: APPS.PER_CAG_SHD
Source
1 Package Body per_cag_shd as
2 /* $Header: pecagrhi.pkb 120.1 2006/10/18 08:42:10 grreddy noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_cag_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
15 --
16 l_proc varchar2(72) := g_package||'constraint_error';
17 --
18 Begin
19 hr_utility.set_location('Entering:'||l_proc, 5);
20 --
21 If (p_constraint_name = 'PER_COLLECTIVE_AGREEMENTS_PK') Then
22 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
23 hr_utility.set_message_token('PROCEDURE', l_proc);
24 hr_utility.set_message_token('STEP','5');
25 hr_utility.raise_error;
26 ElsIf (p_constraint_name = 'PER_COLLECTIVE_AGREEMENTS_UK1') Then
27 hr_utility.set_message(800, 'PER_52835_CAG_INV_CAG_NAME');
28 hr_utility.raise_error;
29 ElsIf (p_constraint_name = 'PER_COLLECTIVE_AGREEMENTS_FK1') Then
30 hr_utility.set_message(800, 'PER_52846_CAG_INV_EMP_ORG');
31 hr_utility.raise_error;
32 ElsIf (p_constraint_name = 'PER_COLLECTIVE_AGREEMENTS_FK2') Then
33 hr_utility.set_message(800, 'PER_52847_CAG_INV_BARG_ORG');
34 hr_utility.raise_error;
35 ElsIf (p_constraint_name = 'PER_COLLECTIVE_AGREEMENTS_FK3') Then
36 hr_utility.set_message(800, 'PER_52848_CAG_INV_BG');
37 hr_utility.raise_error;
38 Else
39 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
40 hr_utility.set_message_token('PROCEDURE', l_proc);
41 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
42 hr_utility.raise_error;
43 End If;
44 --
45 hr_utility.set_location(' Leaving:'||l_proc, 10);
46 End constraint_error;
47 --
48 -- ----------------------------------------------------------------------------
49 -- |-----------------------------< api_updating >-----------------------------|
50 -- ----------------------------------------------------------------------------
51 Function api_updating
52 (
53 p_collective_agreement_id in number,
54 p_object_version_number in number
55 ) Return Boolean Is
56 --
57 --
58 -- Cursor selects the 'current' row from the HR Schema
59 --
60 Cursor C_Sel1 is
61 Select collective_agreement_id,
62 business_group_id,
63 object_version_number,
64 name,
65 status,
66 pl_id,
67 cag_number,
68 description,
69 start_date,
70 end_date,
71 employer_organization_id,
72 employer_signatory,
73 bargaining_organization_id,
74 bargaining_unit_signatory,
75 jurisdiction,
76 authorizing_body,
77 authorized_date,
78 cag_information_category,
79 cag_information1,
80 cag_information2,
81 cag_information3,
82 cag_information4,
83 cag_information5,
84 cag_information6,
85 cag_information7,
86 cag_information8,
87 cag_information9,
88 cag_information10,
89 cag_information11,
90 cag_information12,
91 cag_information13,
92 cag_information14,
93 cag_information15,
94 cag_information16,
95 cag_information17,
96 cag_information18,
97 cag_information19,
98 cag_information20,
99 attribute_category,
100 attribute1,
101 attribute2,
102 attribute3,
103 attribute4,
104 attribute5,
105 attribute6,
106 attribute7,
107 attribute8,
108 attribute9,
109 attribute10,
110 attribute11,
111 attribute12,
112 attribute13,
113 attribute14,
114 attribute15,
115 attribute16,
116 attribute17,
117 attribute18,
118 attribute19,
119 attribute20
120 from per_collective_agreements
121 where collective_agreement_id = p_collective_agreement_id;
122 /* select
123 collective_agreement_id,
124 business_group_id,
125 object_version_number,
126 name,
127 pl_id,
128 status,
129 cag_number,
130 description,
131 start_date,
132 end_date,
133 employer_organization_id,
134 employer_signatory,
135 bargaining_organization_id,
136 bargaining_unit_signatory,
137 jurisdiction,
138 authorizing_body,
139 authorized_date,
140 cag_information_category,
141 cag_information1,
142 cag_information2,
143 cag_information3,
144 cag_information4,
145 cag_information5,
146 cag_information6,
147 cag_information7,
148 cag_information8,
149 cag_information9,
150 cag_information10,
151 cag_information11,
152 cag_information12,
153 cag_information13,
154 cag_information14,
155 cag_information15,
156 cag_information16,
157 cag_information17,
158 cag_information18,
159 cag_information19,
160 cag_information20,
161 attribute_category,
162 attribute1,
163 attribute2,
164 attribute3,
165 attribute4,
166 attribute5,
167 attribute6,
168 attribute7,
169 attribute8,
170 attribute9,
171 attribute10,
172 attribute11,
173 attribute12,
174 attribute13,
175 attribute14,
176 attribute15,
177 attribute16,
178 attribute17,
179 attribute18,
180 attribute19,
181 attribute20 */
182 --
183 l_proc varchar2(72) := g_package||'api_updating';
184 l_fct_ret boolean;
185 --
186 Begin
187 hr_utility.set_location('Entering:'||l_proc, 5);
188 --
189 If (
190 p_collective_agreement_id is null and
191 p_object_version_number is null
192 ) Then
193 --
194 -- One of the primary key arguments is null therefore we must
195 -- set the returning function value to false
196 --
197 l_fct_ret := false;
198 Else
199 If (
200 p_collective_agreement_id = g_old_rec.collective_agreement_id and
201 p_object_version_number = g_old_rec.object_version_number
202 ) Then
203 hr_utility.set_location(l_proc, 10);
204 --
205 -- The g_old_rec is current therefore we must
206 -- set the returning function to true
207 --
208 l_fct_ret := true;
209 Else
210 --
211 -- Select the current row into g_old_rec
212 --
213 Open C_Sel1;
214 Fetch C_Sel1 Into g_old_rec;
215 If C_Sel1%notfound Then
216 Close C_Sel1;
217 --
218 -- The primary key is invalid therefore we must error
219 --
220 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
221 hr_utility.raise_error;
222 End If;
223 Close C_Sel1;
224 If (p_object_version_number <> g_old_rec.object_version_number) Then
225 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
226 hr_utility.raise_error;
227 End If;
228 hr_utility.set_location(l_proc, 15);
229 l_fct_ret := true;
230 End If;
231 End If;
232 hr_utility.set_location(' Leaving:'||l_proc, 20);
233 Return (l_fct_ret);
234 --
235 End api_updating;
236 --
237 -- ----------------------------------------------------------------------------
238 -- |---------------------------------< lck >----------------------------------|
239 -- ----------------------------------------------------------------------------
240 Procedure lck
241 (
242 p_collective_agreement_id in number,
243 p_object_version_number in number
244 ) is
245 --
246 -- Cursor selects the 'current' row from the HR Schema
247 --
248 Cursor C_Sel1 is
249 select
250 collective_agreement_id,
251 business_group_id,
252 object_version_number,
253 name,
254 status,
255 pl_id,
256 cag_number,
257 description,
258 start_date,
259 end_date,
260 employer_organization_id,
261 employer_signatory,
262 bargaining_organization_id,
263 bargaining_unit_signatory,
264 jurisdiction,
265 authorizing_body,
266 authorized_date,
267 cag_information_category,
268 cag_information1,
269 cag_information2,
270 cag_information3,
271 cag_information4,
272 cag_information5,
273 cag_information6,
274 cag_information7,
275 cag_information8,
276 cag_information9,
277 cag_information10,
278 cag_information11,
279 cag_information12,
280 cag_information13,
281 cag_information14,
282 cag_information15,
283 cag_information16,
284 cag_information17,
285 cag_information18,
286 cag_information19,
287 cag_information20,
288 attribute_category,
289 attribute1,
290 attribute2,
291 attribute3,
292 attribute4,
293 attribute5,
294 attribute6,
295 attribute7,
296 attribute8,
297 attribute9,
298 attribute10,
299 attribute11,
300 attribute12,
301 attribute13,
302 attribute14,
303 attribute15,
304 attribute16,
305 attribute17,
306 attribute18,
307 attribute19,
308 attribute20
312 --
309 from per_collective_agreements
310 where collective_agreement_id = p_collective_agreement_id
311 for update nowait;
313 l_proc varchar2(72) := g_package||'lck';
314 --
315 Begin
316 --
317 hr_utility.set_location('Entering:'||l_proc||'/'||p_collective_agreement_id, 5);
318 --
319 -- Ensure that all mandatory arguments are not NULL
320 --
321 hr_api.mandatory_arg_error(p_api_name => l_proc,
322 p_argument => 'collective_agreement_id',
323 p_argument_value => p_collective_agreement_id);
324 --
325 hr_utility.set_location(l_proc, 10);
326 --
327 hr_api.mandatory_arg_error(p_api_name => l_proc,
328 p_argument => 'object_version_number',
329 p_argument_value => p_object_version_number);
330 --
331 hr_utility.set_location(l_proc, 20);
332 --
333 Open C_Sel1;
334 Fetch C_Sel1 Into g_old_rec;
335 hr_utility.set_location(l_proc, 25);
336 If C_Sel1%notfound then
337 Close C_Sel1;
338 --
339 -- The primary key is invalid therefore we must error
340 --
341 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
342 hr_utility.raise_error;
343 End If;
344 Close C_Sel1;
345 If (p_object_version_number <> g_old_rec.object_version_number) Then
346 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
347 hr_utility.raise_error;
348 End If;
349 --
350 hr_utility.set_location(' Leaving:'||l_proc, 999);
351 --
352 -- We need to trap the ORA LOCK exception
353 --
354 Exception
355 When HR_Api.Object_Locked then
356 --
357 -- The object is locked therefore we need to supply a meaningful
358 -- error message.
359 --
360 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
361 hr_utility.set_message_token('TABLE_NAME', 'per_collective_agreements');
362 hr_utility.raise_error;
363 End lck;
364 --
365 -- ----------------------------------------------------------------------------
366 -- |-----------------------------< convert_args >-----------------------------|
367 -- ----------------------------------------------------------------------------
368 Function convert_args
369 (
370 p_collective_agreement_id in number,
371 p_business_group_id in number,
372 p_object_version_number in number,
373 p_name in varchar2,
374 p_pl_id in number,
375 p_status in varchar2,
376 p_cag_number in number,
377 p_description in varchar2,
378 p_start_date in date,
379 p_end_date in date,
380 p_employer_organization_id in number,
381 p_employer_signatory in varchar2,
382 p_bargaining_organization_id in number,
383 p_bargaining_unit_signatory in varchar2,
384 p_jurisdiction in varchar2,
385 p_authorizing_body in varchar2,
386 p_authorized_date in date,
387 p_cag_information_category in varchar2,
388 p_cag_information1 in varchar2,
389 p_cag_information2 in varchar2,
390 p_cag_information3 in varchar2,
391 p_cag_information4 in varchar2,
392 p_cag_information5 in varchar2,
393 p_cag_information6 in varchar2,
394 p_cag_information7 in varchar2,
395 p_cag_information8 in varchar2,
396 p_cag_information9 in varchar2,
397 p_cag_information10 in varchar2,
398 p_cag_information11 in varchar2,
399 p_cag_information12 in varchar2,
400 p_cag_information13 in varchar2,
401 p_cag_information14 in varchar2,
402 p_cag_information15 in varchar2,
403 p_cag_information16 in varchar2,
404 p_cag_information17 in varchar2,
405 p_cag_information18 in varchar2,
406 p_cag_information19 in varchar2,
407 p_cag_information20 in varchar2,
408 p_attribute_category in varchar2,
409 p_attribute1 in varchar2,
410 p_attribute2 in varchar2,
411 p_attribute3 in varchar2,
412 p_attribute4 in varchar2,
413 p_attribute5 in varchar2,
414 p_attribute6 in varchar2,
415 p_attribute7 in varchar2,
416 p_attribute8 in varchar2,
417 p_attribute9 in varchar2,
418 p_attribute10 in varchar2,
419 p_attribute11 in varchar2,
420 p_attribute12 in varchar2,
421 p_attribute13 in varchar2,
422 p_attribute14 in varchar2,
423 p_attribute15 in varchar2,
424 p_attribute16 in varchar2,
425 p_attribute17 in varchar2,
426 p_attribute18 in varchar2,
427 p_attribute19 in varchar2,
428 p_attribute20 in varchar2
429 )
430 Return g_rec_type is
431 --
432 l_rec g_rec_type;
433 l_proc varchar2(72) := g_package||'convert_args';
434 --
435 Begin
436 --
437 hr_utility.set_location('Entering:'||l_proc, 5);
438 --
439 -- Convert arguments into local l_rec structure.
440 --
441 l_rec.collective_agreement_id := p_collective_agreement_id;
442 l_rec.business_group_id := p_business_group_id;
443 l_rec.object_version_number := p_object_version_number;
444 l_rec.name := p_name;
445 l_rec.pl_id := p_pl_id;
446 l_rec.status := p_status;
447 l_rec.cag_number := p_cag_number;
448 l_rec.description := p_description;
449 l_rec.start_date := p_start_date;
450 l_rec.end_date := p_end_date;
451 l_rec.employer_organization_id := p_employer_organization_id;
452 l_rec.employer_signatory := p_employer_signatory;
453 l_rec.bargaining_organization_id := p_bargaining_organization_id;
454 l_rec.bargaining_unit_signatory := p_bargaining_unit_signatory;
455 l_rec.jurisdiction := p_jurisdiction;
456 l_rec.authorizing_body := p_authorizing_body;
457 l_rec.authorized_date := p_authorized_date;
458 l_rec.cag_information_category := p_cag_information_category;
459 l_rec.cag_information1 := p_cag_information1;
460 l_rec.cag_information2 := p_cag_information2;
461 l_rec.cag_information3 := p_cag_information3;
462 l_rec.cag_information4 := p_cag_information4;
463 l_rec.cag_information5 := p_cag_information5;
464 l_rec.cag_information6 := p_cag_information6;
465 l_rec.cag_information7 := p_cag_information7;
466 l_rec.cag_information8 := p_cag_information8;
467 l_rec.cag_information9 := p_cag_information9;
468 l_rec.cag_information10 := p_cag_information10;
469 l_rec.cag_information11 := p_cag_information11;
470 l_rec.cag_information12 := p_cag_information12;
471 l_rec.cag_information13 := p_cag_information13;
472 l_rec.cag_information14 := p_cag_information14;
473 l_rec.cag_information15 := p_cag_information15;
474 l_rec.cag_information16 := p_cag_information16;
475 l_rec.cag_information17 := p_cag_information17;
476 l_rec.cag_information18 := p_cag_information18;
477 l_rec.cag_information19 := p_cag_information19;
478 l_rec.cag_information20 := p_cag_information20;
479 l_rec.attribute_category := p_attribute_category;
480 l_rec.attribute1 := p_attribute1;
481 l_rec.attribute2 := p_attribute2;
482 l_rec.attribute3 := p_attribute3;
483 l_rec.attribute4 := p_attribute4;
484 l_rec.attribute5 := p_attribute5;
485 l_rec.attribute6 := p_attribute6;
486 l_rec.attribute7 := p_attribute7;
487 l_rec.attribute8 := p_attribute8;
488 l_rec.attribute9 := p_attribute9;
489 l_rec.attribute10 := p_attribute10;
490 l_rec.attribute11 := p_attribute11;
491 l_rec.attribute12 := p_attribute12;
492 l_rec.attribute13 := p_attribute13;
493 l_rec.attribute14 := p_attribute14;
494 l_rec.attribute15 := p_attribute15;
495 l_rec.attribute16 := p_attribute16;
496 l_rec.attribute17 := p_attribute17;
497 l_rec.attribute18 := p_attribute18;
498 l_rec.attribute19 := p_attribute19;
499 l_rec.attribute20 := p_attribute20;
500 --
501 -- Return the plsql record structure.
502 --
503 hr_utility.set_location(' Leaving:'||l_proc, 10);
504 Return(l_rec);
505 --
506 End convert_args;
507 --
508
509 end per_cag_shd;