[Home] [Help]
PACKAGE BODY: APPS.IGS_PT_CUSTM_PKG
Source
1 PACKAGE BODY igs_pt_custm_pkg AS
2 /* $Header: IGSZI01B.pls 120.1 2005/09/08 15:06:16 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pt_custm%ROWTYPE;
6 new_references igs_pt_custm%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_cus_id IN NUMBER DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_custm_name IN VARCHAR2 DEFAULT NULL,
14 x_custm_value IN VARCHAR2 DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL
20 ) AS
21 /*
22 || Created By : [email protected]
23 || Created On : 27-MAY-2002
24 || Purpose : Initialises the Old and New references for the columns of the table.
25 || Known limitations, enhancements or remarks :
26 || Change History :
27 || Who When What
28 || (reverse chronological order - newest change first)
29 */
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM igs_pt_custm
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 OPEN cur_old_ref_values;
43 FETCH cur_old_ref_values INTO old_references;
44 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
45 CLOSE cur_old_ref_values;
46 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
47 igs_ge_msg_stack.add;
48 app_exception.raise_exception;
49 RETURN;
50 END IF;
51 CLOSE cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.cus_id := x_cus_id;
55 new_references.person_id := x_person_id;
56 new_references.custm_name := x_custm_name;
57 new_references.custm_value := x_custm_value;
58
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70
71 END set_column_values;
72
73
74 FUNCTION get_pk_for_validation (
75 x_cus_id IN NUMBER
76 ) RETURN BOOLEAN AS
77 /*
78 || Created By : [email protected]
79 || Created On : 27-MAY-2002
80 || Purpose : Validates the Primary Key of the table.
81 || Known limitations, enhancements or remarks :
82 || Change History :
83 || Who When What
84 || (reverse chronological order - newest change first)
85 */
86 CURSOR cur_rowid IS
87 SELECT rowid
88 FROM igs_pt_custm
89 WHERE cus_id = x_cus_id
90 FOR UPDATE NOWAIT;
91
92 lv_rowid cur_rowid%RowType;
93
94 BEGIN
95
96 OPEN cur_rowid;
97 FETCH cur_rowid INTO lv_rowid;
98 IF (cur_rowid%FOUND) THEN
99 CLOSE cur_rowid;
100 RETURN(TRUE);
101 ELSE
102 CLOSE cur_rowid;
103 RETURN(FALSE);
104 END IF;
105
106 END get_pk_for_validation;
107
108
109 PROCEDURE before_dml (
110 p_action IN VARCHAR2,
111 x_rowid IN VARCHAR2 DEFAULT NULL,
112 x_cus_id IN NUMBER DEFAULT NULL,
113 x_person_id IN NUMBER DEFAULT NULL,
114 x_custm_name IN VARCHAR2 DEFAULT NULL,
115 x_custm_value IN VARCHAR2 DEFAULT NULL,
116 x_creation_date IN DATE DEFAULT NULL,
117 x_created_by IN NUMBER DEFAULT NULL,
118 x_last_update_date IN DATE DEFAULT NULL,
119 x_last_updated_by IN NUMBER DEFAULT NULL,
120 x_last_update_login IN NUMBER DEFAULT NULL
121 ) AS
122 /*
123 || Created By : [email protected]
124 || Created On : 27-MAY-2002
125 || Purpose : Initialises the columns, Checks Constraints, Calls the
126 || Trigger Handlers for the table, before any DML operation.
127 || Known limitations, enhancements or remarks :
128 || Change History :
129 || Who When What
130 || (reverse chronological order - newest change first)
131 */
132 BEGIN
133
134 set_column_values (
135 p_action,
136 x_rowid,
137 x_cus_id,
138 x_person_id,
139 x_custm_name,
140 x_custm_value,
141 x_creation_date,
142 x_created_by,
143 x_last_update_date,
144 x_last_updated_by,
145 x_last_update_login
146 );
147
148 IF (p_action = 'INSERT') THEN
149 -- Call all the procedures related to Before Insert.
150 IF ( get_pk_for_validation(
151 new_references.cus_id
152 )
153 ) THEN
154 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
155 igs_ge_msg_stack.add;
156 app_exception.raise_exception;
157 END IF;
158 ELSIF (p_action = 'VALIDATE_INSERT') THEN
159 -- Call all the procedures related to Before Insert.
160 IF ( get_pk_for_validation (
161 new_references.cus_id
162 )
163 ) THEN
164 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
165 igs_ge_msg_stack.add;
166 app_exception.raise_exception;
167 END IF;
168 END IF;
169
170 END before_dml;
171
172
173 PROCEDURE insert_row (
174 x_rowid IN OUT NOCOPY VARCHAR2,
175 x_cus_id IN OUT NOCOPY NUMBER,
176 x_person_id IN NUMBER,
177 x_custm_name IN VARCHAR2,
178 x_custm_value IN VARCHAR2,
179 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
180 x_MSG_DATA OUT NOCOPY VARCHAR2,
181 x_MSG_COUNT OUT NOCOPY NUMBER,
182 x_mode IN VARCHAR2 DEFAULT 'R'
183 ) AS
184 /*
185 || Created By : [email protected]
186 || Created On : 27-MAY-2002
187 || Purpose : Handles the INSERT DML logic for the table.
188 || Known limitations, enhancements or remarks :
189 || Change History :
190 || Who When What
191 || (reverse chronological order - newest change first)
192 */
193 CURSOR c IS
194 SELECT rowid
195 FROM igs_pt_custm
196 WHERE cus_id = x_cus_id;
197
198 x_last_update_date DATE;
199 x_last_updated_by NUMBER;
200 x_last_update_login NUMBER;
201
202 cursor c_cus_id is SELECT igs_pt_custm_S.NEXTVAL FROM DUAL;
203
204 BEGIN
205
206 FND_MSG_PUB.initialize;
207
208 IF x_cus_id IS NULL THEN
209 OPEN C_CUS_ID ;
210 FETCH C_CUS_ID INTO x_cus_id;
211 CLOSE C_CUS_ID;
212 END IF;
213
214 x_last_update_date := SYSDATE;
215 IF (x_mode = 'I') THEN
216 x_last_updated_by := 1;
217 x_last_update_login := 0;
218 ELSIF (x_mode = 'R') THEN
219 x_last_updated_by := fnd_global.user_id;
220 IF (x_last_updated_by IS NULL) THEN
221 x_last_updated_by := -1;
222 END IF;
223 x_last_update_login := fnd_global.login_id;
224 IF (x_last_update_login IS NULL) THEN
225 x_last_update_login := -1;
226 END IF;
227 ELSE
228 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
229 igs_ge_msg_stack.add;
230 app_exception.raise_exception;
231 END IF;
232
233 SELECT igs_pt_custm_s.NEXTVAL
234 INTO x_cus_id
235 FROM dual;
236
237 before_dml(
238 p_action => 'INSERT',
239 x_rowid => x_rowid,
240 x_cus_id => x_cus_id,
241 x_person_id => x_person_id,
242 x_custm_name => x_custm_name,
243 x_custm_value => x_custm_value,
244 x_creation_date => x_last_update_date,
245 x_created_by => x_last_updated_by,
246 x_last_update_date => x_last_update_date,
247 x_last_updated_by => x_last_updated_by,
248 x_last_update_login => x_last_update_login
249 );
250
251 INSERT INTO igs_pt_custm (
252 cus_id,
253 person_id,
254 custm_name,
255 custm_value,
256 creation_date,
257 created_by,
258 last_update_date,
259 last_updated_by,
260 last_update_login
261 ) VALUES (
262 new_references.cus_id,
263 new_references.person_id,
264 new_references.custm_name,
265 new_references.custm_value,
266 x_last_update_date,
267 x_last_updated_by,
268 x_last_update_date,
269 x_last_updated_by,
270 x_last_update_login
271 );
272
273 OPEN c;
274 FETCH c INTO x_rowid;
275 IF (c%NOTFOUND) THEN
276 CLOSE c;
277 RAISE NO_DATA_FOUND;
278 END IF;
279 CLOSE c;
280
281
282 -- Initialize API return status to success.
283 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
284 -- Standard call to get message count and if count is 1, get message
285 -- info.
286 FND_MSG_PUB.Count_And_Get(
287 p_encoded => FND_API.G_FALSE,
288 p_count => x_MSG_COUNT,
289 p_data => X_MSG_DATA);
290
291 EXCEPTION
292 WHEN FND_API.G_EXC_ERROR THEN
293 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
294 FND_MSG_PUB.Count_And_Get(
295 p_encoded => FND_API.G_FALSE,
296 p_count => x_MSG_COUNT,
297 p_data => X_MSG_DATA);
298 RETURN;
299 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
300 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
301 FND_MSG_PUB.Count_And_Get(
302 p_encoded => FND_API.G_FALSE,
303 p_count => x_MSG_COUNT,
304 p_data => X_MSG_DATA);
305 RETURN;
306 WHEN OTHERS THEN
307 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
308 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
309 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
310 FND_MSG_PUB.ADD;
311 FND_MSG_PUB.Count_And_Get(
312 p_encoded => FND_API.G_FALSE,
313 p_count => x_MSG_COUNT,
314 p_data => X_MSG_DATA);
315 RETURN;
316
317 END insert_row;
318
319
320 PROCEDURE lock_row (
321 x_rowid IN VARCHAR2,
322 x_cus_id IN NUMBER,
323 x_person_id IN NUMBER,
324 x_custm_name IN VARCHAR2,
325 x_custm_value IN VARCHAR2,
326 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
327 x_MSG_DATA OUT NOCOPY VARCHAR2,
328 x_MSG_COUNT OUT NOCOPY NUMBER
329 ) AS
330 /*
331 || Created By : [email protected]
332 || Created On : 27-MAY-2002
333 || Purpose : Handles the LOCK mechanism for the table.
334 || Known limitations, enhancements or remarks :
335 || Change History :
336 || Who When What
337 || (reverse chronological order - newest change first)
338 */
339 CURSOR c1 IS
340 SELECT
341 person_id,
342 custm_name,
343 custm_value
344 FROM igs_pt_custm
345 WHERE rowid = x_rowid
346 FOR UPDATE NOWAIT;
347
348 tlinfo c1%ROWTYPE;
349
350 BEGIN
351
352 FND_MSG_PUB.initialize;
353
354 OPEN c1;
355 FETCH c1 INTO tlinfo;
356 IF (c1%notfound) THEN
357 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
358 igs_ge_msg_stack.add;
359 CLOSE c1;
360 app_exception.raise_exception;
361 RETURN;
362 END IF;
363 CLOSE c1;
364
365 IF (
366 (tlinfo.person_id = x_person_id)
367 AND (tlinfo.custm_name = x_custm_name)
368 AND ((tlinfo.custm_value = x_custm_value) OR ((tlinfo.custm_value IS NULL) AND (X_custm_value IS NULL)))
369 ) THEN
370 NULL;
371 ELSE
372 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
373 igs_ge_msg_stack.add;
374 app_exception.raise_exception;
375 END IF;
376
377 -- Initialize API return status to success.
378 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
379 -- Standard call to get message count and if count is 1, get message
380 -- info.
381 FND_MSG_PUB.Count_And_Get(
382 p_encoded => FND_API.G_FALSE,
383 p_count => x_MSG_COUNT,
384 p_data => X_MSG_DATA);
385
386 EXCEPTION
387 WHEN FND_API.G_EXC_ERROR THEN
388 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
389 FND_MSG_PUB.Count_And_Get(
390 p_encoded => FND_API.G_FALSE,
391 p_count => x_MSG_COUNT,
392 p_data => X_MSG_DATA);
393 RETURN;
394 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
395 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
396 FND_MSG_PUB.Count_And_Get(
397 p_encoded => FND_API.G_FALSE,
398 p_count => x_MSG_COUNT,
399 p_data => X_MSG_DATA);
400 RETURN;
401 WHEN OTHERS THEN
402 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
403 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
404 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
405 FND_MSG_PUB.ADD;
406 FND_MSG_PUB.Count_And_Get(
407 p_encoded => FND_API.G_FALSE,
411
408 p_count => x_MSG_COUNT,
409 p_data => X_MSG_DATA);
410 RETURN;
412 END lock_row;
413
414
415 PROCEDURE update_row (
416 x_rowid IN VARCHAR2,
417 x_cus_id IN NUMBER,
418 x_person_id IN NUMBER,
419 x_custm_name IN VARCHAR2,
420 x_custm_value IN VARCHAR2,
421 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
422 x_MSG_DATA OUT NOCOPY VARCHAR2,
423 x_MSG_COUNT OUT NOCOPY NUMBER,
424 x_mode IN VARCHAR2 DEFAULT 'R'
425 ) AS
426 /*
427 || Created By : [email protected]
428 || Created On : 27-MAY-2002
429 || Purpose : Handles the UPDATE DML logic for the table.
430 || Known limitations, enhancements or remarks :
431 || Change History :
432 || Who When What
433 || (reverse chronological order - newest change first)
434 */
435 x_last_update_date DATE ;
436 x_last_updated_by NUMBER;
437 x_last_update_login NUMBER;
438
439 BEGIN
440
441 FND_MSG_PUB.initialize;
442
443 x_last_update_date := SYSDATE;
444 IF (X_MODE = 'I') THEN
445 x_last_updated_by := 1;
446 x_last_update_login := 0;
447 ELSIF (x_mode = 'R') THEN
448 x_last_updated_by := fnd_global.user_id;
449 IF x_last_updated_by IS NULL THEN
450 x_last_updated_by := -1;
451 END IF;
452 x_last_update_login := fnd_global.login_id;
453 IF (x_last_update_login IS NULL) THEN
454 x_last_update_login := -1;
455 END IF;
456 ELSE
457 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
458 igs_ge_msg_stack.add;
459 app_exception.raise_exception;
460 END IF;
461
462 before_dml(
463 p_action => 'UPDATE',
464 x_rowid => x_rowid,
465 x_cus_id => x_cus_id,
466 x_person_id => x_person_id,
467 x_custm_name => x_custm_name,
468 x_custm_value => x_custm_value,
469 x_creation_date => x_last_update_date,
470 x_created_by => x_last_updated_by,
471 x_last_update_date => x_last_update_date,
472 x_last_updated_by => x_last_updated_by,
473 x_last_update_login => x_last_update_login
474 );
475
476 UPDATE igs_pt_custm
477 SET
478 person_id = new_references.person_id,
479 custm_name = new_references.custm_name,
480 custm_value = new_references.custm_value,
481 last_update_date = x_last_update_date,
482 last_updated_by = x_last_updated_by,
483 last_update_login = x_last_update_login
484 WHERE rowid = x_rowid;
485
486 IF (SQL%NOTFOUND) THEN
487 RAISE NO_DATA_FOUND;
488 END IF;
489
490 -- Initialize API return status to success.
491 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
492 -- Standard call to get message count and if count is 1, get message
493 -- info.
494 FND_MSG_PUB.Count_And_Get(
495 p_encoded => FND_API.G_FALSE,
496 p_count => x_MSG_COUNT,
497 p_data => X_MSG_DATA);
498
499 EXCEPTION
500 WHEN FND_API.G_EXC_ERROR THEN
501 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
502 FND_MSG_PUB.Count_And_Get(
503 p_encoded => FND_API.G_FALSE,
504 p_count => x_MSG_COUNT,
505 p_data => X_MSG_DATA);
506 RETURN;
507 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
508 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
509 FND_MSG_PUB.Count_And_Get(
510 p_encoded => FND_API.G_FALSE,
511 p_count => x_MSG_COUNT,
512 p_data => X_MSG_DATA);
513 RETURN;
514 WHEN OTHERS THEN
515 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
516 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
517 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
518 FND_MSG_PUB.ADD;
519 FND_MSG_PUB.Count_And_Get(
520 p_encoded => FND_API.G_FALSE,
521 p_count => x_MSG_COUNT,
522 p_data => X_MSG_DATA);
523 RETURN;
524
525 END update_row;
526
527
528 PROCEDURE add_row (
529 x_rowid IN OUT NOCOPY VARCHAR2,
530 x_cus_id IN OUT NOCOPY NUMBER,
531 x_person_id IN NUMBER,
532 x_custm_name IN VARCHAR2,
533 x_custm_value IN VARCHAR2,
534 x_mode IN VARCHAR2 DEFAULT 'R' ,
535 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
536 x_MSG_DATA OUT NOCOPY VARCHAR2,
537 x_MSG_COUNT OUT NOCOPY NUMBER
538 ) AS
539 /*
540 || Created By : [email protected]
541 || Created On : 27-MAY-2002
542 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
543 || Known limitations, enhancements or remarks :
544 || Change History :
545 || Who When What
546 || (reverse chronological order - newest change first)
547 */
548 CURSOR c1 IS
549 SELECT rowid
550 FROM igs_pt_custm
551 WHERE cus_id = x_cus_id;
552
553 BEGIN
554
555 OPEN c1;
556 FETCH c1 INTO x_rowid;
557 IF (c1%NOTFOUND) THEN
558 CLOSE c1;
559
560 insert_row (
561 x_rowid,
562 x_cus_id,
563 x_person_id,
564 x_custm_name,
565 x_custm_value,
566 x_mode => 'R',
567 x_RETURN_STATUS =>x_RETURN_STATUS,
568 x_MSG_DATA =>x_MSG_DATA ,
569 x_MSG_COUNT =>x_MSG_COUNT
570 );
571 RETURN;
572
573 END IF;
574 CLOSE c1;
575
576 update_row (
577 x_rowid => x_rowid,
578 x_cus_id=> x_cus_id,
579 x_person_id => x_person_id,
580 x_custm_name => x_custm_name,
581 x_custm_value => x_custm_value,
582 x_mode => 'R',
583 x_RETURN_STATUS =>x_RETURN_STATUS,
584 x_MSG_DATA =>x_MSG_DATA ,
585 x_MSG_COUNT =>x_MSG_COUNT
586
587 );
588
589
590 END add_row;
591
592
593 PROCEDURE delete_row (
594 x_rowid IN VARCHAR2 ,
595 x_RETURN_STATUS OUT NOCOPY VARCHAR2,
596 x_MSG_DATA OUT NOCOPY VARCHAR2,
597 x_MSG_COUNT OUT NOCOPY NUMBER
598 ) AS
599 /*
600 || Created By : [email protected]
601 || Created On : 27-MAY-2002
602 || Purpose : Handles the DELETE DML logic for the table.
603 || Known limitations, enhancements or remarks :
604 || Change History :
605 || Who When What
606 || (reverse chronological order - newest change first)
607 */
608 BEGIN
609
610 FND_MSG_PUB.initialize;
611
612 before_dml (
613 p_action => 'DELETE',
614 x_rowid => x_rowid
615 );
616
617 DELETE FROM igs_pt_custm
618 WHERE rowid = x_rowid;
619
620 IF (SQL%NOTFOUND) THEN
621 RAISE NO_DATA_FOUND;
622 END IF;
623
624 -- Initialize API return status to success.
625 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
626 -- Standard call to get message count and if count is 1, get message
627 -- info.
628 FND_MSG_PUB.Count_And_Get(
629 p_encoded => FND_API.G_FALSE,
630 p_count => x_MSG_COUNT,
631 p_data => X_MSG_DATA);
632
633 EXCEPTION
634 WHEN FND_API.G_EXC_ERROR THEN
635 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
636 FND_MSG_PUB.Count_And_Get(
637 p_encoded => FND_API.G_FALSE,
638 p_count => x_MSG_COUNT,
639 p_data => X_MSG_DATA);
640 RETURN;
641 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
642 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
643 FND_MSG_PUB.Count_And_Get(
644 p_encoded => FND_API.G_FALSE,
645 p_count => x_MSG_COUNT,
646 p_data => X_MSG_DATA);
647 RETURN;
648 WHEN OTHERS THEN
649 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
650 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
651 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
652 FND_MSG_PUB.ADD;
653 FND_MSG_PUB.Count_And_Get(
654 p_encoded => FND_API.G_FALSE,
655 p_count => x_MSG_COUNT,
656 p_data => X_MSG_DATA);
657 RETURN;
658
659 END delete_row;
660
661
662 END igs_pt_custm_pkg;