[Home] [Help]
PACKAGE BODY: APPS.PSB_ATTRIBUTE_VALUES_PVT
Source
1 PACKAGE BODY PSB_ATTRIBUTE_VALUES_PVT AS
2 /* $Header: PSBVPAVB.pls 120.2 2005/07/13 11:27:45 shtripat ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_ATTRIBUTE_VALUES_PVT';
6
7 procedure INSERT_ROW (
8 p_api_version IN NUMBER,
9 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
10 p_commit IN VARCHAR2 := FND_API.G_FALSE,
11 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
12 p_return_status OUT NOCOPY VARCHAR2,
13 p_msg_count OUT NOCOPY NUMBER,
14 p_msg_data OUT NOCOPY VARCHAR2,
15 --
16 P_ROWID in OUT NOCOPY VARCHAR2,
17 --
18 P_ATTRIBUTE_VALUE_ID in NUMBER,
19 P_ATTRIBUTE_ID in NUMBER,
20 P_ATTRIBUTE_VALUE in VARCHAR2,
21 P_HR_VALUE_ID in VARCHAR2,
22 P_DESCRIPTION in VARCHAR2,
23 P_DATA_EXTRACT_ID in NUMBER,
24 P_CONTEXT in VARCHAR2,
25 P_ATTRIBUTE1 in VARCHAR2,
26 P_ATTRIBUTE2 in VARCHAR2,
27 P_ATTRIBUTE3 in VARCHAR2,
28 P_ATTRIBUTE4 in VARCHAR2,
29 P_ATTRIBUTE5 in VARCHAR2,
30 P_ATTRIBUTE6 in VARCHAR2,
31 P_ATTRIBUTE7 in VARCHAR2,
32 P_ATTRIBUTE8 in VARCHAR2,
33 P_ATTRIBUTE9 in VARCHAR2,
34 P_ATTRIBUTE10 in VARCHAR2,
35 P_ATTRIBUTE11 in VARCHAR2,
36 P_ATTRIBUTE12 in VARCHAR2,
37 P_ATTRIBUTE13 in VARCHAR2,
38 P_ATTRIBUTE14 in VARCHAR2,
39 P_ATTRIBUTE15 in VARCHAR2,
40 P_ATTRIBUTE16 in VARCHAR2,
41 P_ATTRIBUTE17 in VARCHAR2,
42 P_ATTRIBUTE18 in VARCHAR2,
43 P_ATTRIBUTE19 in VARCHAR2,
44 P_ATTRIBUTE20 in VARCHAR2,
45 P_ATTRIBUTE21 in VARCHAR2,
46 P_ATTRIBUTE22 in VARCHAR2,
47 P_ATTRIBUTE23 in VARCHAR2,
48 P_ATTRIBUTE24 in VARCHAR2,
49 P_ATTRIBUTE25 in VARCHAR2,
50 P_ATTRIBUTE26 in VARCHAR2,
51 P_ATTRIBUTE27 in VARCHAR2,
52 P_ATTRIBUTE28 in VARCHAR2,
53 P_ATTRIBUTE29 in VARCHAR2,
54 P_ATTRIBUTE30 in VARCHAR2,
55 P_LAST_UPDATE_DATE in DATE,
56 P_LAST_UPDATED_BY in NUMBER,
57 P_LAST_UPDATE_LOGIN in NUMBER,
58 P_CREATED_BY in NUMBER,
59 P_CREATION_DATE in DATE
60 ) as
61 --
62 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
63 l_api_version CONSTANT NUMBER := 1.0;
64 --
65 cursor C is select ROWID from PSB_ATTRIBUTE_VALUES
66 where ATTRIBUTE_VALUE_ID = P_ATTRIBUTE_VALUE_ID;
67
68 BEGIN
69 --
70 SAVEPOINT Insert_Row_Pvt ;
71 --
72 IF NOT FND_API.Compatible_API_Call ( l_api_version,
73 p_api_version,
74 l_api_name,
75 G_PKG_NAME )
76 THEN
77 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
78 END IF;
79 --
80
81 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
82 FND_MSG_PUB.initialize ;
83 END IF;
84 --
85 p_return_status := FND_API.G_RET_STS_SUCCESS ;
86 --
87
88 insert into PSB_ATTRIBUTE_VALUES (
89 ATTRIBUTE_VALUE_ID ,
90 ATTRIBUTE_ID ,
91 ATTRIBUTE_VALUE ,
92 HR_VALUE_ID ,
93 DESCRIPTION ,
94 DATA_EXTRACT_ID ,
95 CONTEXT ,
96 ATTRIBUTE1 ,
97 ATTRIBUTE2 ,
98 ATTRIBUTE3 ,
99 ATTRIBUTE4 ,
100 ATTRIBUTE5 ,
101 ATTRIBUTE6 ,
102 ATTRIBUTE7 ,
103 ATTRIBUTE8 ,
104 ATTRIBUTE9 ,
105 ATTRIBUTE10 ,
106 ATTRIBUTE11 ,
107 ATTRIBUTE12 ,
108 ATTRIBUTE13 ,
109 ATTRIBUTE14 ,
110 ATTRIBUTE15 ,
111 ATTRIBUTE16 ,
112 ATTRIBUTE17 ,
113 ATTRIBUTE18 ,
114 ATTRIBUTE19 ,
115 ATTRIBUTE20 ,
116 ATTRIBUTE21 ,
117 ATTRIBUTE22 ,
118 ATTRIBUTE23 ,
119 ATTRIBUTE24 ,
120 ATTRIBUTE25 ,
121 ATTRIBUTE26 ,
122 ATTRIBUTE27 ,
123 ATTRIBUTE28 ,
124 ATTRIBUTE29 ,
125 ATTRIBUTE30 ,
126 LAST_UPDATE_DATE ,
127 LAST_UPDATED_BY ,
128 LAST_UPDATE_LOGIN ,
129 CREATED_BY ,
130 CREATION_DATE
131 ) values (
132 P_ATTRIBUTE_VALUE_ID ,
133 P_ATTRIBUTE_ID ,
134 P_ATTRIBUTE_VALUE ,
135 P_HR_VALUE_ID ,
136 P_DESCRIPTION ,
137 P_DATA_EXTRACT_ID ,
138 P_CONTEXT ,
139 P_ATTRIBUTE1 ,
140 P_ATTRIBUTE2 ,
141 P_ATTRIBUTE3 ,
142 P_ATTRIBUTE4 ,
143 P_ATTRIBUTE5 ,
144 P_ATTRIBUTE6 ,
145 P_ATTRIBUTE7 ,
146 P_ATTRIBUTE8 ,
147 P_ATTRIBUTE9 ,
148 P_ATTRIBUTE10 ,
149 P_ATTRIBUTE11 ,
150 P_ATTRIBUTE12 ,
151 P_ATTRIBUTE13 ,
152 P_ATTRIBUTE14 ,
153 P_ATTRIBUTE15 ,
154 P_ATTRIBUTE16 ,
155 P_ATTRIBUTE17 ,
156 P_ATTRIBUTE18 ,
157 P_ATTRIBUTE19 ,
158 P_ATTRIBUTE20 ,
159 P_ATTRIBUTE21 ,
160 P_ATTRIBUTE22 ,
161 P_ATTRIBUTE23 ,
162 P_ATTRIBUTE24 ,
163 P_ATTRIBUTE25 ,
164 P_ATTRIBUTE26 ,
165 P_ATTRIBUTE27 ,
166 P_ATTRIBUTE28 ,
167 P_ATTRIBUTE29 ,
168 P_ATTRIBUTE30 ,
169 P_LAST_UPDATE_DATE ,
170 P_LAST_UPDATED_BY ,
171 P_LAST_UPDATE_LOGIN ,
172 P_CREATED_BY ,
173 P_CREATION_DATE
174 );
175
176 open c;
177 fetch c into P_ROWID;
178 if (c%notfound) then
179 close c;
180 raise no_data_found;
181 end if;
182 close c;
183
184 --
185 IF FND_API.To_Boolean ( p_commit ) THEN
186 COMMIT WORK;
187 END iF;
188 --
189 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
190 p_data => p_msg_data );
191 --
192 EXCEPTION
193 --
194 WHEN FND_API.G_EXC_ERROR THEN
195 --
196 ROLLBACK TO Insert_Row_Pvt ;
197 p_return_status := FND_API.G_RET_STS_ERROR;
198 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
199 p_data => p_msg_data );
200 --
201 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
202 --
203 ROLLBACK TO Insert_Row_Pvt ;
204 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
205 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
206 p_data => p_msg_data );
207 --
208 WHEN OTHERS THEN
209 --
210 ROLLBACK TO Insert_Row_Pvt ;
211 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
212 --
213 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
214 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
215 l_api_name);
216 END if;
217 --
218 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
219 p_data => p_msg_data );
220 --
221 END INSERT_ROW;
222
223 procedure LOCK_ROW (
224 p_api_version IN NUMBER,
225 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
226 p_commit IN VARCHAR2 := FND_API.G_FALSE,
227 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
228 p_return_status OUT NOCOPY VARCHAR2,
229 p_msg_count OUT NOCOPY NUMBER,
230 p_msg_data OUT NOCOPY VARCHAR2,
231 --
232 p_lock_row OUT NOCOPY VARCHAR2,
233 P_ROWID IN VARCHAR2,
234 --
235 P_ATTRIBUTE_VALUE_ID in NUMBER,
236 P_ATTRIBUTE_ID in NUMBER,
237 P_ATTRIBUTE_VALUE in VARCHAR2,
238 P_HR_VALUE_ID in VARCHAR2,
239 P_DESCRIPTION in VARCHAR2,
240 P_DATA_EXTRACT_ID in NUMBER,
241 P_CONTEXT in VARCHAR2,
242 P_ATTRIBUTE1 in VARCHAR2,
243 P_ATTRIBUTE2 in VARCHAR2,
244 P_ATTRIBUTE3 in VARCHAR2,
245 P_ATTRIBUTE4 in VARCHAR2,
246 P_ATTRIBUTE5 in VARCHAR2,
247 P_ATTRIBUTE6 in VARCHAR2,
248 P_ATTRIBUTE7 in VARCHAR2,
249 P_ATTRIBUTE8 in VARCHAR2,
250 P_ATTRIBUTE9 in VARCHAR2,
251 P_ATTRIBUTE10 in VARCHAR2,
252 P_ATTRIBUTE11 in VARCHAR2,
253 P_ATTRIBUTE12 in VARCHAR2,
254 P_ATTRIBUTE13 in VARCHAR2,
255 P_ATTRIBUTE14 in VARCHAR2,
256 P_ATTRIBUTE15 in VARCHAR2,
257 P_ATTRIBUTE16 in VARCHAR2,
258 P_ATTRIBUTE17 in VARCHAR2,
259 P_ATTRIBUTE18 in VARCHAR2,
260 P_ATTRIBUTE19 in VARCHAR2,
261 P_ATTRIBUTE20 in VARCHAR2,
262 P_ATTRIBUTE21 in VARCHAR2,
263 P_ATTRIBUTE22 in VARCHAR2,
264 P_ATTRIBUTE23 in VARCHAR2,
265 P_ATTRIBUTE24 in VARCHAR2,
266 P_ATTRIBUTE25 in VARCHAR2,
267 P_ATTRIBUTE26 in VARCHAR2,
268 P_ATTRIBUTE27 in VARCHAR2,
269 P_ATTRIBUTE28 in VARCHAR2,
270 P_ATTRIBUTE29 in VARCHAR2,
271 P_ATTRIBUTE30 in VARCHAR2
272 ) as
273 --
274 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
275 l_api_version CONSTANT NUMBER := 1.0;
276 --
277 Counter NUMBER;
278 cursor c1 is select
279 ATTRIBUTE_VALUE_ID ,
280 ATTRIBUTE_ID ,
281 ATTRIBUTE_VALUE ,
282 HR_VALUE_ID ,
283 DESCRIPTION ,
284 DATA_EXTRACT_ID ,
285 CONTEXT ,
286 ATTRIBUTE1 ,
287 ATTRIBUTE2 ,
288 ATTRIBUTE3 ,
289 ATTRIBUTE4 ,
290 ATTRIBUTE5 ,
291 ATTRIBUTE6 ,
292 ATTRIBUTE7 ,
293 ATTRIBUTE8 ,
294 ATTRIBUTE9 ,
295 ATTRIBUTE10 ,
296 ATTRIBUTE11 ,
297 ATTRIBUTE12 ,
298 ATTRIBUTE13 ,
299 ATTRIBUTE14 ,
300 ATTRIBUTE15 ,
301 ATTRIBUTE16 ,
302 ATTRIBUTE17 ,
303 ATTRIBUTE18 ,
304 ATTRIBUTE19 ,
305 ATTRIBUTE20 ,
306 ATTRIBUTE21 ,
307 ATTRIBUTE22 ,
308 ATTRIBUTE23 ,
309 ATTRIBUTE24 ,
310 ATTRIBUTE25 ,
311 ATTRIBUTE26 ,
312 ATTRIBUTE27 ,
313 ATTRIBUTE28 ,
314 ATTRIBUTE29 ,
315 ATTRIBUTE30
316 from PSB_ATTRIBUTE_VALUES
317 where ROWID = P_ROWID
318 for update of ATTRIBUTE_VALUE_ID nowait;
319 tlinfo c1%rowtype;
320
321 BEGIN
322 --
323 SAVEPOINT Lock_Row_Pvt ;
324 --
325 IF NOT FND_API.Compatible_API_Call ( l_api_version,
326 p_api_version,
327 l_api_name,
328 G_PKG_NAME )
329 THEN
330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
331 END IF;
332 --
333
334 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
335 FND_MSG_PUB.initialize ;
336 END IF;
337 --
338 p_return_status := FND_API.G_RET_STS_SUCCESS ;
339 --
340 open c1;
341 fetch c1 into tlinfo;
342 if (c1%notfound) then
343 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
344 app_exception.raise_exception;
345 close c1;
346 return;
347 end if;
348 close c1;
349
350 if (
351 (tlinfo.ATTRIBUTE_VALUE_ID = P_ATTRIBUTE_VALUE_ID)
352 AND (tlinfo.ATTRIBUTE_ID = P_ATTRIBUTE_ID)
353
354 AND ((tlinfo.ATTRIBUTE_VALUE = P_ATTRIBUTE_VALUE)
355 OR ((tlinfo.ATTRIBUTE_VALUE is null)
356 AND (P_ATTRIBUTE_VALUE is null)))
357 AND ((tlinfo.HR_VALUE_ID = P_HR_VALUE_ID)
358 OR ((tlinfo.HR_VALUE_ID is null)
359 AND (P_HR_VALUE_ID is null)))
360 AND ((tlinfo.DESCRIPTION = P_DESCRIPTION)
361 OR ((tlinfo.DESCRIPTION is null)
362 AND (P_DESCRIPTION is null)))
363 AND ((tlinfo.DATA_EXTRACT_ID = P_DATA_EXTRACT_ID)
364 OR ((tlinfo.DATA_EXTRACT_ID is null)
365 AND (P_DATA_EXTRACT_ID is null)))
366 AND ((tlinfo.CONTEXT = P_CONTEXT)
367 OR ((tlinfo.CONTEXT is null)
368 AND (P_CONTEXT is null)))
369 AND ((tlinfo.ATTRIBUTE1 = P_ATTRIBUTE1)
370 OR ((tlinfo.ATTRIBUTE1 is null)
371 AND (P_ATTRIBUTE1 is null)))
372 AND ((tlinfo.ATTRIBUTE2 = P_ATTRIBUTE2)
373 OR ((tlinfo.ATTRIBUTE2 is null)
374 AND (P_ATTRIBUTE2 is null)))
375 AND ((tlinfo.ATTRIBUTE3 = P_ATTRIBUTE3)
376 OR ((tlinfo.ATTRIBUTE3 is null)
377 AND (P_ATTRIBUTE3 is null)))
378 AND ((tlinfo.ATTRIBUTE4 = P_ATTRIBUTE4)
379 OR ((tlinfo.ATTRIBUTE4 is null)
380 AND (P_ATTRIBUTE4 is null)))
381 AND ((tlinfo.ATTRIBUTE5 = P_ATTRIBUTE5)
382 OR ((tlinfo.ATTRIBUTE5 is null)
383 AND (P_ATTRIBUTE5 is null)))
384 AND ((tlinfo.ATTRIBUTE6 = P_ATTRIBUTE6)
385 OR ((tlinfo.ATTRIBUTE6 is null)
386 AND (P_ATTRIBUTE6 is null)))
387 AND ((tlinfo.ATTRIBUTE7 = P_ATTRIBUTE7)
388 OR ((tlinfo.ATTRIBUTE7 is null)
389 AND (P_ATTRIBUTE7 is null)))
390 AND ((tlinfo.ATTRIBUTE8 = P_ATTRIBUTE8)
391 OR ((tlinfo.ATTRIBUTE8 is null)
392 AND (P_ATTRIBUTE8 is null)))
393 AND ((tlinfo.ATTRIBUTE9 = P_ATTRIBUTE9)
394 OR ((tlinfo.ATTRIBUTE9 is null)
395 AND (P_ATTRIBUTE9 is null)))
396 AND ((tlinfo.ATTRIBUTE10 = P_ATTRIBUTE10)
397 OR ((tlinfo.ATTRIBUTE10 is null)
398 AND (P_ATTRIBUTE10 is null)))
399 AND ((tlinfo.ATTRIBUTE11 = P_ATTRIBUTE11)
400 OR ((tlinfo.ATTRIBUTE11 is null)
401 AND (P_ATTRIBUTE11 is null)))
402 AND ((tlinfo.ATTRIBUTE12 = P_ATTRIBUTE12)
403 OR ((tlinfo.ATTRIBUTE12 is null)
404 AND (P_ATTRIBUTE12 is null)))
405 AND ((tlinfo.ATTRIBUTE13 = P_ATTRIBUTE13)
406 OR ((tlinfo.ATTRIBUTE13 is null)
407 AND (P_ATTRIBUTE13 is null)))
408 AND ((tlinfo.ATTRIBUTE14 = P_ATTRIBUTE14)
409 OR ((tlinfo.ATTRIBUTE14 is null)
410 AND (P_ATTRIBUTE14 is null)))
411 AND ((tlinfo.ATTRIBUTE15 = P_ATTRIBUTE15)
412 OR ((tlinfo.ATTRIBUTE15 is null)
413 AND (P_ATTRIBUTE15 is null)))
414 AND ((tlinfo.ATTRIBUTE16 = P_ATTRIBUTE16)
415 OR ((tlinfo.ATTRIBUTE16 is null)
416 AND (P_ATTRIBUTE16 is null)))
417 AND ((tlinfo.ATTRIBUTE17 = P_ATTRIBUTE17)
418 OR ((tlinfo.ATTRIBUTE17 is null)
419 AND (P_ATTRIBUTE17 is null)))
420 AND ((tlinfo.ATTRIBUTE18 = P_ATTRIBUTE18)
421 OR ((tlinfo.ATTRIBUTE18 is null)
422 AND (P_ATTRIBUTE18 is null)))
423 AND ((tlinfo.ATTRIBUTE19 = P_ATTRIBUTE19)
424 OR ((tlinfo.ATTRIBUTE19 is null)
425 AND (P_ATTRIBUTE19 is null)))
426 AND ((tlinfo.ATTRIBUTE20 = P_ATTRIBUTE20)
427 OR ((tlinfo.ATTRIBUTE20 is null)
428 AND (P_ATTRIBUTE20 is null)))
429 AND ((tlinfo.ATTRIBUTE21 = P_ATTRIBUTE21)
430 OR ((tlinfo.ATTRIBUTE21 is null)
431 AND (P_ATTRIBUTE21 is null)))
432 AND ((tlinfo.ATTRIBUTE22 = P_ATTRIBUTE22)
433 OR ((tlinfo.ATTRIBUTE22 is null)
434 AND (P_ATTRIBUTE22 is null)))
435 AND ((tlinfo.ATTRIBUTE23 = P_ATTRIBUTE23)
436 OR ((tlinfo.ATTRIBUTE23 is null)
437 AND (P_ATTRIBUTE23 is null)))
438 AND ((tlinfo.ATTRIBUTE24 = P_ATTRIBUTE24)
439 OR ((tlinfo.ATTRIBUTE24 is null)
440 AND (P_ATTRIBUTE24 is null)))
441 AND ((tlinfo.ATTRIBUTE25 = P_ATTRIBUTE25)
442 OR ((tlinfo.ATTRIBUTE25 is null)
443 AND (P_ATTRIBUTE25 is null)))
444 AND ((tlinfo.ATTRIBUTE26 = P_ATTRIBUTE26)
445 OR ((tlinfo.ATTRIBUTE26 is null)
446 AND (P_ATTRIBUTE26 is null)))
447 AND ((tlinfo.ATTRIBUTE27 = P_ATTRIBUTE27)
448 OR ((tlinfo.ATTRIBUTE27 is null)
449 AND (P_ATTRIBUTE27 is null)))
450 AND ((tlinfo.ATTRIBUTE28 = P_ATTRIBUTE28)
451 OR ((tlinfo.ATTRIBUTE28 is null)
452 AND (P_ATTRIBUTE28 is null)))
453 AND ((tlinfo.ATTRIBUTE29 = P_ATTRIBUTE29)
454 OR ((tlinfo.ATTRIBUTE29 is null)
455 AND (P_ATTRIBUTE29 is null)))
456 AND ((tlinfo.ATTRIBUTE30 = P_ATTRIBUTE30)
457 OR ((tlinfo.ATTRIBUTE30 is null)
458 AND (P_ATTRIBUTE30 is null)))
459
460
461 ) then
462 p_lock_row := FND_API.G_TRUE;
463 ELSE
464 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
465 FND_MSG_PUB.Add;
466 RAISE FND_API.G_EXC_ERROR ;
467 END IF;
468
469 --
470 IF FND_API.To_Boolean ( p_commit ) THEN
471 COMMIT WORK;
472 END iF;
473 --
474 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
475 p_data => p_msg_data );
476 --
477 EXCEPTION
478 --
479 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
480 --
481 ROLLBACK TO Lock_Row_Pvt ;
482 p_lock_row := FND_API.G_FALSE;
483 p_return_status := FND_API.G_RET_STS_ERROR;
484 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
485 p_data => p_msg_data );
486 --
487 WHEN FND_API.G_EXC_ERROR THEN
488 --
489 ROLLBACK TO Lock_Row_Pvt ;
490 p_lock_row := FND_API.G_FALSE;
491 p_return_status := FND_API.G_RET_STS_ERROR;
492 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
493 p_data => p_msg_data );
494 --
495 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
496 --
497 ROLLBACK TO Lock_Row_Pvt ;
498 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
499 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
500 p_data => p_msg_data );
501 --
502 WHEN OTHERS THEN
503 --
504 ROLLBACK TO Lock_Row_Pvt ;
505 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
506 --
507 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
508 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
509 l_api_name);
510 END if;
511 --
512 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
513 p_data => p_msg_data );
514 --
515 END LOCK_ROW;
516
517 procedure UPDATE_ROW (
518 p_api_version IN NUMBER,
519 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
520 p_commit IN VARCHAR2 := FND_API.G_FALSE,
521 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
522 p_return_status OUT NOCOPY VARCHAR2,
523 p_msg_count OUT NOCOPY NUMBER,
524 p_msg_data OUT NOCOPY VARCHAR2,
525 --
526 P_ATTRIBUTE_VALUE_ID in NUMBER,
527 P_ATTRIBUTE_ID in NUMBER,
528 P_ATTRIBUTE_VALUE in VARCHAR2,
529 P_HR_VALUE_ID in VARCHAR2,
530 P_DESCRIPTION in VARCHAR2,
531 P_DATA_EXTRACT_ID in NUMBER,
532 P_CONTEXT in VARCHAR2,
533 P_ATTRIBUTE1 in VARCHAR2,
534 P_ATTRIBUTE2 in VARCHAR2,
535 P_ATTRIBUTE3 in VARCHAR2,
536 P_ATTRIBUTE4 in VARCHAR2,
537 P_ATTRIBUTE5 in VARCHAR2,
538 P_ATTRIBUTE6 in VARCHAR2,
539 P_ATTRIBUTE7 in VARCHAR2,
540 P_ATTRIBUTE8 in VARCHAR2,
541 P_ATTRIBUTE9 in VARCHAR2,
542 P_ATTRIBUTE10 in VARCHAR2,
543 P_ATTRIBUTE11 in VARCHAR2,
544 P_ATTRIBUTE12 in VARCHAR2,
545 P_ATTRIBUTE13 in VARCHAR2,
546 P_ATTRIBUTE14 in VARCHAR2,
547 P_ATTRIBUTE15 in VARCHAR2,
548 P_ATTRIBUTE16 in VARCHAR2,
549 P_ATTRIBUTE17 in VARCHAR2,
550 P_ATTRIBUTE18 in VARCHAR2,
551 P_ATTRIBUTE19 in VARCHAR2,
552 P_ATTRIBUTE20 in VARCHAR2,
553 P_ATTRIBUTE21 in VARCHAR2,
554 P_ATTRIBUTE22 in VARCHAR2,
555 P_ATTRIBUTE23 in VARCHAR2,
556 P_ATTRIBUTE24 in VARCHAR2,
557 P_ATTRIBUTE25 in VARCHAR2,
558 P_ATTRIBUTE26 in VARCHAR2,
559 P_ATTRIBUTE27 in VARCHAR2,
560 P_ATTRIBUTE28 in VARCHAR2,
561 P_ATTRIBUTE29 in VARCHAR2,
562 P_ATTRIBUTE30 in VARCHAR2,
563 P_LAST_UPDATE_DATE in DATE,
564 P_LAST_UPDATED_BY in NUMBER,
565 P_LAST_UPDATE_LOGIN in NUMBER
566 ) as
567 --
568 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
569 l_api_version CONSTANT NUMBER := 1.0;
570 --
571 BEGIN
572 --
573 SAVEPOINT Update_Row_Pvt ;
574 --
575 IF NOT FND_API.Compatible_API_Call ( l_api_version,
576 p_api_version,
577 l_api_name,
578 G_PKG_NAME )
579 THEN
580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
581 END IF;
582 --
583
584 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
585 FND_MSG_PUB.initialize ;
586 END IF;
587 --
588 p_return_status := FND_API.G_RET_STS_SUCCESS ;
589 --
590 update PSB_ATTRIBUTE_VALUES set
591 ATTRIBUTE_VALUE_ID = P_ATTRIBUTE_VALUE_ID,
592 ATTRIBUTE_ID = P_ATTRIBUTE_ID,
593 ATTRIBUTE_VALUE = P_ATTRIBUTE_VALUE,
594 HR_VALUE_ID = P_HR_VALUE_ID,
595 DESCRIPTION = P_DESCRIPTION,
596 DATA_EXTRACT_ID = P_DATA_EXTRACT_ID,
597 CONTEXT = P_CONTEXT,
598 ATTRIBUTE1 = P_ATTRIBUTE1,
599 ATTRIBUTE2 = P_ATTRIBUTE2,
600 ATTRIBUTE3 = P_ATTRIBUTE3,
601 ATTRIBUTE4 = P_ATTRIBUTE4,
602 ATTRIBUTE5 = P_ATTRIBUTE5,
603 ATTRIBUTE6 = P_ATTRIBUTE6,
604 ATTRIBUTE7 = P_ATTRIBUTE7,
605 ATTRIBUTE8 = P_ATTRIBUTE8,
606 ATTRIBUTE9 = P_ATTRIBUTE9,
607 ATTRIBUTE10 = P_ATTRIBUTE10,
608 ATTRIBUTE11 = P_ATTRIBUTE11,
609 ATTRIBUTE12 = P_ATTRIBUTE12,
610 ATTRIBUTE13 = P_ATTRIBUTE13,
611 ATTRIBUTE14 = P_ATTRIBUTE14,
612 ATTRIBUTE15 = P_ATTRIBUTE15,
613 ATTRIBUTE16 = P_ATTRIBUTE16,
614 ATTRIBUTE17 = P_ATTRIBUTE17,
615 ATTRIBUTE18 = P_ATTRIBUTE18,
616 ATTRIBUTE19 = P_ATTRIBUTE19,
617 ATTRIBUTE20 = P_ATTRIBUTE20,
618 ATTRIBUTE21 = P_ATTRIBUTE21,
619 ATTRIBUTE22 = P_ATTRIBUTE22,
620 ATTRIBUTE23 = P_ATTRIBUTE23,
621 ATTRIBUTE24 = P_ATTRIBUTE24,
622 ATTRIBUTE25 = P_ATTRIBUTE25,
623 ATTRIBUTE26 = P_ATTRIBUTE26,
624 ATTRIBUTE27 = P_ATTRIBUTE27,
625 ATTRIBUTE28 = P_ATTRIBUTE28,
626 ATTRIBUTE29 = P_ATTRIBUTE29,
627 ATTRIBUTE30 = P_ATTRIBUTE30,
628 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
629 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
630 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
631 where ATTRIBUTE_VALUE_ID = P_ATTRIBUTE_VALUE_ID;
632
633 if (sql%notfound) then
634 raise no_data_found;
635 end if;
636 --
637 IF FND_API.To_Boolean ( p_commit ) THEN
638 COMMIT WORK;
639 END iF;
640 --
641 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
642 p_data => p_msg_data );
643 --
644 EXCEPTION
645 --
646 WHEN FND_API.G_EXC_ERROR THEN
647 --
648 ROLLBACK TO Update_Row_Pvt ;
649 p_return_status := FND_API.G_RET_STS_ERROR;
650 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
651 p_data => p_msg_data );
652 --
653 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
654 --
655 ROLLBACK TO Update_Row_Pvt ;
656 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
657 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
658 p_data => p_msg_data );
659 --
660 WHEN OTHERS THEN
661 --
662 ROLLBACK TO Update_Row_Pvt ;
663 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
664 --
665 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
666 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
667 l_api_name);
668 END if;
669 --
670 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
671 p_data => p_msg_data );
672 --
673 END UPDATE_ROW;
674
678 p_commit IN VARCHAR2 := FND_API.G_FALSE,
675 procedure DELETE_ROW (
676 p_api_version IN NUMBER,
677 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
679 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
680 p_return_status OUT NOCOPY VARCHAR2,
681 p_msg_count OUT NOCOPY NUMBER,
682 p_msg_data OUT NOCOPY VARCHAR2,
683 --
684 P_ATTRIBUTE_VALUE_ID in NUMBER ) as
685 --
686 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
687 l_api_version CONSTANT NUMBER := 1.0;
688 --
689 BEGIN
690 --
691 SAVEPOINT Delete_Row_Pvt ;
692 --
693 IF NOT FND_API.Compatible_API_Call ( l_api_version,
694 p_api_version,
695 l_api_name,
696 G_PKG_NAME )
697 THEN
698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
699 END IF;
700 --
701
702 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
703 FND_MSG_PUB.initialize ;
704 END IF;
705 --
706 p_return_status := FND_API.G_RET_STS_SUCCESS ;
707 --
708 delete from PSB_ATTRIBUTE_VALUES
709 where ATTRIBUTE_VALUE_ID = P_ATTRIBUTE_VALUE_ID;
710 if (sql%notfound) then
711 raise no_data_found;
712 end if;
713 --
714 IF FND_API.To_Boolean ( p_commit ) THEN
715 COMMIT WORK;
716 END iF;
717 --
718 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
719 p_data => p_msg_data );
720
721 EXCEPTION
722 --
723 WHEN FND_API.G_EXC_ERROR THEN
724 --
725 ROLLBACK TO Delete_Row_Pvt ;
726 p_return_status := FND_API.G_RET_STS_ERROR;
727 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
728 p_data => p_msg_data );
729 --
730 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
731 --
732 ROLLBACK TO Delete_Row_Pvt ;
733 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
734 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
735 p_data => p_msg_data );
736 --
737 WHEN OTHERS THEN
738 --
739 ROLLBACK TO Delete_Row_Pvt ;
740 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
741 --
742 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
743 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
744 l_api_name);
745 END if;
746 --
747 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
748 p_data => p_msg_data );
749 --
750 END DELETE_ROW;
751
752
753 PROCEDURE Check_References
754 (
755 p_api_version IN NUMBER,
756 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
757 p_commit IN VARCHAR2 := FND_API.G_FALSE,
758 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
759 p_return_status OUT NOCOPY VARCHAR2,
760 p_msg_count OUT NOCOPY NUMBER,
761 p_msg_data OUT NOCOPY VARCHAR2,
762 --
763 p_ATTRIBUTE_ID IN NUMBER,
764 p_ATTRIBUTE_VALUE_ID IN NUMBER,
765 p_Return_Value IN OUT NOCOPY VARCHAR2
766 )
767 AS
768 --
769 l_api_name CONSTANT VARCHAR2(30) := 'Check_References';
770 l_api_version CONSTANT NUMBER := 1.0;
771 --
772 l_tmp VARCHAR2(1);
773
774 CURSOR c IS
775 SELECT '1'
776 FROM psb_position_assignments
777 WHERE attribute_id = p_attribute_Id
778 AND attribute_value_id = p_attribute_value_id;
779
780 BEGIN
781 --
782 SAVEPOINT Check_References_Pvt ;
783 --
784 IF NOT FND_API.Compatible_API_Call ( l_api_version,
785 p_api_version,
786 l_api_name,
787 G_PKG_NAME )
788 THEN
789 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
790 END IF;
791 --
792
793 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
794 FND_MSG_PUB.initialize ;
795 END IF;
796 --
797 p_return_status := FND_API.G_RET_STS_SUCCESS ;
798 --
799
800 -- Checking the Psb_set_relations table for references.
801 OPEN c;
802 FETCH c INTO l_tmp;
803 --
804 -- p_Return_Value tells whether references exist or not.
805 IF l_tmp IS NULL THEN
806 p_Return_Value := 'FALSE';
807 ELSE
808 p_Return_Value := 'TRUE';
809 END IF;
810
811 CLOSE c;
812 --
813 IF FND_API.To_Boolean ( p_commit ) THEN
814 COMMIT WORK;
815 END iF;
816 --
817 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
818 p_data => p_msg_data );
819
820 EXCEPTION
821 --
822 WHEN FND_API.G_EXC_ERROR THEN
823 --
824 ROLLBACK TO Check_References_Pvt ;
825 p_return_status := FND_API.G_RET_STS_ERROR;
826 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
827 p_data => p_msg_data );
828 --
829 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
830 --
831 ROLLBACK TO Check_References_Pvt ;
832 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
833 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
834 p_data => p_msg_data );
835 --
836 WHEN OTHERS THEN
837 --
838 ROLLBACK TO Check_References_Pvt ;
839 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
840 --
841 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
842 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
843 l_api_name);
844 END if;
845 --
846 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
847 p_data => p_msg_data );
848 --
849 END Check_References;
850
851
852 end PSB_ATTRIBUTE_VALUES_PVT;