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