1 PACKAGE BODY igs_ps_usec_act_type_pkg AS
2 /* $Header: IGSPI2JB.pls 115.5 2003/03/13 15:13:10 smvk ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ps_usec_act_type%ROWTYPE;
6 new_references igs_ps_usec_act_type%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_activity_type_id IN NUMBER DEFAULT NULL,
12 x_activity_type_code IN VARCHAR2 DEFAULT NULL,
13 x_activity_type_description IN VARCHAR2 DEFAULT NULL,
14 x_closed_ind 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 : 25-MAY-2001
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_PS_USEC_ACT_TYPE
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.activity_type_id := x_activity_type_id;
55 new_references.activity_type_code := x_activity_type_code;
56 new_references.activity_type_description := x_activity_type_description;
57 new_references.closed_ind := x_closed_ind;
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 PROCEDURE check_uniqueness AS
75 /*
76 || Created By : [email protected]
77 || Created On : 25-MAY-2001
78 || Purpose : Handles the Unique Constraint logic defined for the columns.
79 || Known limitations, enhancements or remarks :
80 || Change History :
81 || Who When What
82 || (reverse chronological order - newest change first)
83 */
84 BEGIN
85
86 IF ( get_uk_for_validation (
87 new_references.activity_type_code
88 )
89 ) THEN
90 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
91 igs_ge_msg_stack.add;
92 app_exception.raise_exception;
93 END IF;
94
95 END check_uniqueness;
96
97 PROCEDURE check_child_existance IS
98 /*
99 || Created By : [email protected]
100 || Created On : 16-MAY-2001
101 || Purpose : Checks for the existance of Child records.
102 || Known limitations, enhancements or remarks :
103 || Change History :
104 || Who When What
105 || (reverse chronological order - newest change first)
106 */
107 BEGIN
108
109 igs_ps_us_unsched_cl_pkg.get_fk_igs_ps_usec_act_type (
110 old_references.activity_type_id
111 );
112
113 END check_child_existance;
114
115 FUNCTION get_pk_for_validation (
116 x_activity_type_id IN NUMBER
117 ) RETURN BOOLEAN AS
118 /*
119 || Created By : [email protected]
120 || Created On : 25-MAY-2001
121 || Purpose : Validates the Primary Key of the table.
122 || Known limitations, enhancements or remarks :
123 || Change History :
124 || Who When What
125 || (reverse chronological order - newest change first)
126 */
127 CURSOR cur_rowid IS
128 SELECT rowid
129 FROM igs_ps_usec_act_type
130 WHERE activity_type_id = x_activity_type_id
131 FOR UPDATE NOWAIT;
132
133 lv_rowid cur_rowid%RowType;
134
135 BEGIN
136
137 OPEN cur_rowid;
138 FETCH cur_rowid INTO lv_rowid;
139 IF (cur_rowid%FOUND) THEN
140 CLOSE cur_rowid;
141 RETURN(TRUE);
142 ELSE
143 CLOSE cur_rowid;
144 RETURN(FALSE);
145 END IF;
146
147 END get_pk_for_validation;
148
149
150 FUNCTION get_uk_for_validation (
151 x_activity_type_code IN VARCHAR2
152 ) RETURN BOOLEAN AS
153 /*
154 || Created By : [email protected]
155 || Created On : 25-MAY-2001
156 || Purpose : Validates the Unique Keys of the table.
157 || Known limitations, enhancements or remarks :
158 || Change History :
159 || Who When What
160 || (reverse chronological order - newest change first)
161 */
162 CURSOR cur_rowid IS
163 SELECT rowid
164 FROM igs_ps_usec_act_type
165 WHERE activity_type_code = x_activity_type_code
166 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
167
168 lv_rowid cur_rowid%RowType;
169
170 BEGIN
171
172 OPEN cur_rowid;
173 FETCH cur_rowid INTO lv_rowid;
174 IF (cur_rowid%FOUND) THEN
175 CLOSE cur_rowid;
176 RETURN (true);
177 ELSE
178 CLOSE cur_rowid;
179 RETURN(FALSE);
180 END IF;
181
182 END get_uk_for_validation ;
183
184
185 PROCEDURE before_dml (
186 p_action IN VARCHAR2,
187 x_rowid IN VARCHAR2 DEFAULT NULL,
188 x_activity_type_id IN NUMBER DEFAULT NULL,
189 x_activity_type_code IN VARCHAR2 DEFAULT NULL,
190 x_activity_type_description IN VARCHAR2 DEFAULT NULL,
191 x_closed_ind IN VARCHAR2 DEFAULT NULL,
192 x_creation_date IN DATE DEFAULT NULL,
193 x_created_by IN NUMBER DEFAULT NULL,
194 x_last_update_date IN DATE DEFAULT NULL,
195 x_last_updated_by IN NUMBER DEFAULT NULL,
196 x_last_update_login IN NUMBER DEFAULT NULL
197 ) AS
198 /*
199 || Created By : [email protected]
200 || Created On : 25-MAY-2001
201 || Purpose : Initialises the columns, Checks Constraints, Calls the
202 || Trigger Handlers for the table, before any DML operation.
203 || Known limitations, enhancements or remarks :
204 || Change History :
205 || Who When What
206 || smvk 13-Mar-2003 Bug # 2476026. Nullified the value of l_rowid.
207 || (reverse chronological order - newest change first)
208 */
209 BEGIN
210
211 set_column_values (
212 p_action,
213 x_rowid,
214 x_activity_type_id,
215 x_activity_type_code,
216 x_activity_type_description,
217 x_closed_ind,
218 x_creation_date,
219 x_created_by,
220 x_last_update_date,
221 x_last_updated_by,
222 x_last_update_login
223 );
224
225 IF (p_action = 'INSERT') THEN
226 -- Call all the procedures related to Before Insert.
227 IF ( get_pk_for_validation(
228 new_references.activity_type_id
229 )
230 ) THEN
231 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
232 igs_ge_msg_stack.add;
233 app_exception.raise_exception;
234 END IF;
235 check_uniqueness;
236 ELSIF (p_action = 'UPDATE') THEN
237 -- Call all the procedures related to Before Update.
238 check_uniqueness;
239 ELSIF (p_action = 'DELETE') THEN
240 -- Call all the procedures related to Before Delete.
241 check_child_existance;
242 ELSIF (p_action = 'VALIDATE_INSERT') THEN
243 -- Call all the procedures related to Before Insert.
244 IF ( get_pk_for_validation (
245 new_references.activity_type_id
246 )
247 ) THEN
248 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
249 igs_ge_msg_stack.add;
250 app_exception.raise_exception;
251 END IF;
252 check_uniqueness;
253 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
254 check_uniqueness;
255 ELSIF (p_action = 'VALIDATE_DELETE') THEN
256 check_child_existance;
257 END IF;
258
259 l_rowid := NULL; -- Added as a part of Bug # 2476026
260
261 END before_dml;
262
263
264 PROCEDURE insert_row (
265 x_rowid IN OUT NOCOPY VARCHAR2,
266 x_activity_type_id IN OUT NOCOPY NUMBER,
267 x_activity_type_code IN VARCHAR2,
268 x_activity_type_description IN VARCHAR2,
269 x_closed_ind IN VARCHAR2,
270 x_mode IN VARCHAR2 DEFAULT 'R'
271 ) AS
272 /*
273 || Created By : [email protected]
274 || Created On : 25-MAY-2001
275 || Purpose : Handles the INSERT DML logic for the table.
276 || Known limitations, enhancements or remarks :
277 || Change History :
278 || Who When What
279 || (reverse chronological order - newest change first)
280 */
281 CURSOR c IS
282 SELECT rowid
283 FROM igs_ps_usec_act_type
284 WHERE activity_type_id = x_activity_type_id;
285
286 x_last_update_date DATE;
287 x_last_updated_by NUMBER;
288 x_last_update_login NUMBER;
289
290 BEGIN
291
292 x_last_update_date := SYSDATE;
293 IF (x_mode = 'I') THEN
294 x_last_updated_by := 1;
295 x_last_update_login := 0;
296 ELSIF (x_mode = 'R') THEN
297 x_last_updated_by := fnd_global.user_id;
298 IF (x_last_updated_by IS NULL) THEN
299 x_last_updated_by := -1;
300 END IF;
301 x_last_update_login := fnd_global.login_id;
302 IF (x_last_update_login IS NULL) THEN
303 x_last_update_login := -1;
304 END IF;
305 ELSE
306 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
307 igs_ge_msg_stack.add;
308 app_exception.raise_exception;
309 END IF;
310
311 SELECT igs_ps_usec_act_type_s.NEXTVAL
312 INTO x_activity_type_id
313 FROM dual;
314
315 before_dml(
316 p_action => 'INSERT',
317 x_rowid => x_rowid,
318 x_activity_type_id => x_activity_type_id,
319 x_activity_type_code => x_activity_type_code,
320 x_activity_type_description => x_activity_type_description,
321 x_closed_ind => x_closed_ind,
322 x_creation_date => x_last_update_date,
323 x_created_by => x_last_updated_by,
324 x_last_update_date => x_last_update_date,
325 x_last_updated_by => x_last_updated_by,
326 x_last_update_login => x_last_update_login
327 );
328
329 INSERT INTO igs_ps_usec_act_type (
330 activity_type_id,
331 activity_type_code,
332 activity_type_description,
333 closed_ind,
334 creation_date,
335 created_by,
336 last_update_date,
337 last_updated_by,
338 last_update_login
339 ) VALUES (
340 new_references.activity_type_id,
341 new_references.activity_type_code,
342 new_references.activity_type_description,
343 new_references.closed_ind,
344 x_last_update_date,
345 x_last_updated_by,
346 x_last_update_date,
347 x_last_updated_by,
348 x_last_update_login
349 );
350
351 OPEN c;
352 FETCH c INTO x_rowid;
353 IF (c%NOTFOUND) THEN
354 CLOSE c;
355 RAISE NO_DATA_FOUND;
356 END IF;
357 CLOSE c;
358
359 END insert_row;
360
361
362 PROCEDURE lock_row (
363 x_rowid IN VARCHAR2,
364 x_activity_type_id IN NUMBER,
365 x_activity_type_code IN VARCHAR2,
366 x_activity_type_description IN VARCHAR2,
367 x_closed_ind IN VARCHAR2
368 ) AS
369 /*
370 || Created By : [email protected]
371 || Created On : 25-MAY-2001
372 || Purpose : Handles the LOCK mechanism for the table.
373 || Known limitations, enhancements or remarks :
374 || Change History :
375 || Who When What
376 || (reverse chronological order - newest change first)
377 */
378 CURSOR c1 IS
379 SELECT
380 activity_type_code,
381 activity_type_description,
382 closed_ind
383 FROM igs_ps_usec_act_type
384 WHERE rowid = x_rowid
385 FOR UPDATE NOWAIT;
386
387 tlinfo c1%ROWTYPE;
388
389 BEGIN
390
391 OPEN c1;
392 FETCH c1 INTO tlinfo;
393 IF (c1%notfound) THEN
394 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
395 igs_ge_msg_stack.add;
396 CLOSE c1;
397 app_exception.raise_exception;
398 RETURN;
399 END IF;
400 CLOSE c1;
401
402 IF (
403 (tlinfo.activity_type_code = x_activity_type_code)
404 AND (tlinfo.activity_type_description = x_activity_type_description)
405 AND (tlinfo.closed_ind = x_closed_ind)
406 ) THEN
407 NULL;
408 ELSE
409 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
410 igs_ge_msg_stack.add;
411 app_exception.raise_exception;
412 END IF;
413
414 RETURN;
415
416 END lock_row;
417
418
419 PROCEDURE update_row (
420 x_rowid IN VARCHAR2,
421 x_activity_type_id IN NUMBER,
422 x_activity_type_code IN VARCHAR2,
423 x_activity_type_description IN VARCHAR2,
424 x_closed_ind IN VARCHAR2,
425 x_mode IN VARCHAR2 DEFAULT 'R'
426 ) AS
427 /*
428 || Created By : [email protected]
429 || Created On : 25-MAY-2001
430 || Purpose : Handles the UPDATE DML logic for the table.
434 || (reverse chronological order - newest change first)
431 || Known limitations, enhancements or remarks :
432 || Change History :
433 || Who When What
435 */
436 x_last_update_date DATE ;
437 x_last_updated_by NUMBER;
438 x_last_update_login NUMBER;
439
440 BEGIN
441
442 x_last_update_date := SYSDATE;
443 IF (X_MODE = 'I') THEN
444 x_last_updated_by := 1;
445 x_last_update_login := 0;
446 ELSIF (x_mode = 'R') THEN
447 x_last_updated_by := fnd_global.user_id;
448 IF x_last_updated_by IS NULL THEN
449 x_last_updated_by := -1;
450 END IF;
451 x_last_update_login := fnd_global.login_id;
452 IF (x_last_update_login IS NULL) THEN
453 x_last_update_login := -1;
454 END IF;
455 ELSE
456 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
457 igs_ge_msg_stack.add;
458 app_exception.raise_exception;
459 END IF;
460
461 before_dml(
462 p_action => 'UPDATE',
463 x_rowid => x_rowid,
464 x_activity_type_id => x_activity_type_id,
465 x_activity_type_code => x_activity_type_code,
466 x_activity_type_description => x_activity_type_description,
467 x_closed_ind => x_closed_ind,
468 x_creation_date => x_last_update_date,
469 x_created_by => x_last_updated_by,
470 x_last_update_date => x_last_update_date,
471 x_last_updated_by => x_last_updated_by,
472 x_last_update_login => x_last_update_login
473 );
474
475 UPDATE igs_ps_usec_act_type
476 SET
477 activity_type_code = new_references.activity_type_code,
478 activity_type_description = new_references.activity_type_description,
479 closed_ind = new_references.closed_ind,
480 last_update_date = x_last_update_date,
481 last_updated_by = x_last_updated_by,
482 last_update_login = x_last_update_login
483 WHERE rowid = x_rowid;
484
485 IF (SQL%NOTFOUND) THEN
486 RAISE NO_DATA_FOUND;
487 END IF;
488
489 END update_row;
490
491
492 PROCEDURE add_row (
493 x_rowid IN OUT NOCOPY VARCHAR2,
494 x_activity_type_id IN OUT NOCOPY NUMBER,
495 x_activity_type_code IN VARCHAR2,
496 x_activity_type_description IN VARCHAR2,
497 x_closed_ind IN VARCHAR2,
498 x_mode IN VARCHAR2 DEFAULT 'R'
499 ) AS
500 /*
501 || Created By : [email protected]
502 || Created On : 25-MAY-2001
503 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
504 || Known limitations, enhancements or remarks :
505 || Change History :
506 || Who When What
507 || (reverse chronological order - newest change first)
508 */
509 CURSOR c1 IS
510 SELECT rowid
511 FROM igs_ps_usec_act_type
512 WHERE activity_type_id = x_activity_type_id;
513
514 BEGIN
515
516 OPEN c1;
517 FETCH c1 INTO x_rowid;
518 IF (c1%NOTFOUND) THEN
519 CLOSE c1;
520
521 insert_row (
522 x_rowid,
523 x_activity_type_id,
524 x_activity_type_code,
525 x_activity_type_description,
526 x_closed_ind,
527 x_mode
528 );
529 RETURN;
530 END IF;
531 CLOSE c1;
532
533 update_row (
534 x_rowid,
535 x_activity_type_id,
536 x_activity_type_code,
537 x_activity_type_description,
538 x_closed_ind,
539 x_mode
540 );
541
542 END add_row;
543
544
545 PROCEDURE delete_row (
546 x_rowid IN VARCHAR2
547 ) AS
548 /*
549 || Created By : [email protected]
550 || Created On : 25-MAY-2001
551 || Purpose : Handles the DELETE DML logic for the table.
552 || Known limitations, enhancements or remarks :
553 || Change History :
554 || Who When What
555 || (reverse chronological order - newest change first)
556 */
557 BEGIN
558
559 before_dml (
560 p_action => 'DELETE',
561 x_rowid => x_rowid
562 );
563
564 DELETE FROM igs_ps_usec_act_type
565 WHERE rowid = x_rowid;
566
567 IF (SQL%NOTFOUND) THEN
568 RAISE NO_DATA_FOUND;
569 END IF;
570
571 END delete_row;
572
573
574 END igs_ps_usec_act_type_pkg;
[Home] [Help]