[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_UNIT_AI_GRP_PKG
Source
1 PACKAGE BODY igs_as_unit_ai_grp_pkg AS
2 /* $Header: IGSDI81B.pls 115.0 2003/12/05 10:39:40 kdande noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_as_unit_ai_grp%ROWTYPE;
6 new_references igs_as_unit_ai_grp%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_unit_ass_item_group_id IN NUMBER,
12 x_unit_cd IN VARCHAR2,
13 x_version_number IN NUMBER,
14 x_cal_type IN VARCHAR2,
15 x_ci_sequence_number IN NUMBER,
16 x_group_name IN VARCHAR2,
17 x_midterm_formula_code IN VARCHAR2,
18 x_midterm_formula_qty IN NUMBER,
19 x_midterm_weight_qty IN NUMBER,
20 x_final_formula_code IN VARCHAR2,
21 x_final_formula_qty IN NUMBER,
22 x_final_weight_qty IN NUMBER,
23 x_creation_date IN DATE,
24 x_created_by IN NUMBER,
25 x_last_update_date IN DATE,
26 x_last_updated_by IN NUMBER,
27 x_last_update_login IN NUMBER
28 ) AS
29 /*
30 || Created By : [email protected]
31 || Created On : 01-OCT-2003
32 || Purpose : Initialises the Old and New references for the columns of the table.
33 || Known limitations, enhancements or remarks :
34 || Change History :
35 || Who When What
36 || (reverse chronological order - newest change first)
37 */
38
39 CURSOR cur_old_ref_values IS
40 SELECT *
41 FROM igs_as_unit_ai_grp
42 WHERE rowid = x_rowid;
43
44 BEGIN
45
46 l_rowid := x_rowid;
47
48 -- Code for setting the Old and New Reference Values.
49 -- Populate Old Values.
50 OPEN cur_old_ref_values;
51 FETCH cur_old_ref_values INTO old_references;
52 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
53 CLOSE cur_old_ref_values;
54 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
55 igs_ge_msg_stack.add;
56 app_exception.raise_exception;
57 RETURN;
58 END IF;
59 CLOSE cur_old_ref_values;
60
61 -- Populate New Values.
62 new_references.unit_ass_item_group_id := x_unit_ass_item_group_id;
63 new_references.unit_cd := x_unit_cd;
64 new_references.version_number := x_version_number;
65 new_references.cal_type := x_cal_type;
66 new_references.ci_sequence_number := x_ci_sequence_number;
67 new_references.group_name := x_group_name;
68 new_references.midterm_formula_code := x_midterm_formula_code;
69 new_references.midterm_formula_qty := x_midterm_formula_qty;
70 new_references.midterm_weight_qty := x_midterm_weight_qty;
71 new_references.final_formula_code := x_final_formula_code;
72 new_references.final_formula_qty := x_final_formula_qty;
73 new_references.final_weight_qty := x_final_weight_qty;
74
75 IF (p_action = 'UPDATE') THEN
76 new_references.creation_date := old_references.creation_date;
77 new_references.created_by := old_references.created_by;
78 ELSE
79 new_references.creation_date := x_creation_date;
80 new_references.created_by := x_created_by;
81 END IF;
82
83 new_references.last_update_date := x_last_update_date;
84 new_references.last_updated_by := x_last_updated_by;
85 new_references.last_update_login := x_last_update_login;
86
87 END set_column_values;
88
89
90 PROCEDURE check_uniqueness AS
91 /*
92 || Created By : [email protected]
93 || Created On : 01-OCT-2003
94 || Purpose : Handles the Unique Constraint logic defined for the columns.
95 || Known limitations, enhancements or remarks :
96 || Change History :
97 || Who When What
98 || (reverse chronological order - newest change first)
99 */
100 BEGIN
101
102 IF ( get_uk_for_validation (
103 new_references.group_name,
104 new_references.unit_cd,
105 new_references.version_number,
106 new_references.cal_type,
107 new_references.ci_sequence_number
108 )
109 ) THEN
110 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
111 igs_ge_msg_stack.add;
112 app_exception.raise_exception;
113 END IF;
114
115 END check_uniqueness;
116
117 PROCEDURE check_child_existance AS
118 /*
119 || Created By : [email protected]
120 || Created On : 10-OCT-2003
121 || Purpose : Checks for the existance of Parent records.
122 || Known limitations, enhancements or remarks :
123 || Change History :
124 || Who When What
125 || (reverse chronological order - newest change first)
126 */
127 BEGIN
128 igs_as_unitass_item_pkg.get_fk_igs_as_unit_ai_grp (
129 old_references.unit_ass_item_group_id
130 );
131 END check_child_existance;
132
133 PROCEDURE check_parent_existance AS
134 /*
135 || Created By : [email protected]
136 || Created On : 01-OCT-2003
137 || Purpose : Checks for the existance of Parent records.
138 || Known limitations, enhancements or remarks :
139 || Change History :
140 || Who When What
141 || (reverse chronological order - newest change first)
142 */
143 BEGIN
144
145 IF (((old_references.unit_cd = new_references.unit_cd) AND
146 (old_references.version_number = new_references.version_number) AND
147 (old_references.cal_type = new_references.cal_type) AND
148 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
149 ((new_references.unit_cd IS NULL) OR
150 (new_references.version_number IS NULL) OR
151 (new_references.cal_type IS NULL) OR
152 (new_references.ci_sequence_number IS NULL))) THEN
153 NULL;
154 ELSIF NOT igs_ps_unit_ofr_pat_pkg.get_pk_for_validation (
155 new_references.unit_cd,
156 new_references.version_number,
157 new_references.cal_type,
158 new_references.ci_sequence_number
159 ) THEN
160 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
161 igs_ge_msg_stack.add;
162 app_exception.raise_exception;
163 END IF;
164
165 END check_parent_existance;
166
167
168 FUNCTION get_pk_for_validation (
169 x_unit_ass_item_group_id IN NUMBER
170 ) RETURN BOOLEAN AS
171 /*
172 || Created By : [email protected]
173 || Created On : 01-OCT-2003
174 || Purpose : Validates the Primary Key of the table.
175 || Known limitations, enhancements or remarks :
176 || Change History :
177 || Who When What
178 || (reverse chronological order - newest change first)
179 */
180 CURSOR cur_rowid IS
181 SELECT rowid
182 FROM igs_as_unit_ai_grp
183 WHERE unit_ass_item_group_id = x_unit_ass_item_group_id
184 FOR UPDATE NOWAIT;
185
186 lv_rowid cur_rowid%RowType;
187
188 BEGIN
189
190 OPEN cur_rowid;
191 FETCH cur_rowid INTO lv_rowid;
192 IF (cur_rowid%FOUND) THEN
193 CLOSE cur_rowid;
194 RETURN(TRUE);
195 ELSE
196 CLOSE cur_rowid;
197 RETURN(FALSE);
198 END IF;
199
200 END get_pk_for_validation;
201
202
203 FUNCTION get_uk_for_validation (
204 x_group_name IN VARCHAR2,
205 x_unit_cd IN VARCHAR2,
206 x_version_number IN NUMBER,
207 x_cal_type IN VARCHAR2,
208 x_ci_sequence_number IN NUMBER
209 ) RETURN BOOLEAN AS
210 /*
211 || Created By : [email protected]
212 || Created On : 01-OCT-2003
213 || Purpose : Validates the Unique Keys of the table.
214 || Known limitations, enhancements or remarks :
215 || Change History :
216 || Who When What
217 || (reverse chronological order - newest change first)
218 */
219 CURSOR cur_rowid IS
220 SELECT rowid
221 FROM igs_as_unit_ai_grp
222 WHERE group_name = x_group_name
223 AND unit_cd = x_unit_cd
224 AND version_number = x_version_number
225 AND cal_type = x_cal_type
226 AND ci_sequence_number = x_ci_sequence_number
227 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
228
229 lv_rowid cur_rowid%RowType;
230
231 BEGIN
232
233 OPEN cur_rowid;
234 FETCH cur_rowid INTO lv_rowid;
235 IF (cur_rowid%FOUND) THEN
236 CLOSE cur_rowid;
237 RETURN (true);
238 ELSE
239 CLOSE cur_rowid;
240 RETURN(FALSE);
241 END IF;
242
243 END get_uk_for_validation ;
244
245
246 PROCEDURE get_fk_igs_ps_unit_ofr_pat (
247 x_unit_cd IN VARCHAR2,
248 x_version_number IN NUMBER,
249 x_cal_type IN VARCHAR2,
250 x_ci_sequence_number IN NUMBER
251 ) AS
252 /*
253 || Created By : [email protected]
254 || Created On : 01-OCT-2003
255 || Purpose : Validates the Foreign Keys for the table.
256 || Known limitations, enhancements or remarks :
257 || Change History :
258 || Who When What
259 || (reverse chronological order - newest change first)
260 */
261 CURSOR cur_rowid IS
262 SELECT rowid
263 FROM igs_as_unit_ai_grp
264 WHERE ((cal_type = x_cal_type) AND
265 (ci_sequence_number = x_ci_sequence_number) AND
266 (unit_cd = x_unit_cd) AND
267 (version_number = x_version_number));
268
269 lv_rowid cur_rowid%RowType;
270
271 BEGIN
272
273 OPEN cur_rowid;
274 FETCH cur_rowid INTO lv_rowid;
275 IF (cur_rowid%FOUND) THEN
276 CLOSE cur_rowid;
277 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
278 igs_ge_msg_stack.add;
279 app_exception.raise_exception;
280 RETURN;
281 END IF;
282 CLOSE cur_rowid;
283
284 END get_fk_igs_ps_unit_ofr_pat;
285
286
287 PROCEDURE before_dml (
288 p_action IN VARCHAR2,
289 x_rowid IN VARCHAR2,
290 x_unit_ass_item_group_id IN NUMBER,
291 x_unit_cd IN VARCHAR2,
292 x_version_number IN NUMBER,
293 x_cal_type IN VARCHAR2,
294 x_ci_sequence_number IN NUMBER,
295 x_group_name IN VARCHAR2,
296 x_midterm_formula_code IN VARCHAR2,
297 x_midterm_formula_qty IN NUMBER,
298 x_midterm_weight_qty IN NUMBER,
299 x_final_formula_code IN VARCHAR2,
300 x_final_formula_qty IN NUMBER,
301 x_final_weight_qty IN NUMBER,
302 x_creation_date IN DATE,
303 x_created_by IN NUMBER,
304 x_last_update_date IN DATE,
305 x_last_updated_by IN NUMBER,
306 x_last_update_login IN NUMBER
307 ) AS
308 /*
309 || Created By : [email protected]
310 || Created On : 01-OCT-2003
311 || Purpose : Initialises the columns, Checks Constraints, Calls the
312 || Trigger Handlers for the table, before any DML operation.
313 || Known limitations, enhancements or remarks :
314 || Change History :
315 || Who When What
316 || (reverse chronological order - newest change first)
317 */
318 BEGIN
319
320 set_column_values (
321 p_action,
322 x_rowid,
323 x_unit_ass_item_group_id,
324 x_unit_cd,
325 x_version_number,
326 x_cal_type,
327 x_ci_sequence_number,
328 x_group_name,
329 x_midterm_formula_code,
330 x_midterm_formula_qty,
331 x_midterm_weight_qty,
332 x_final_formula_code,
333 x_final_formula_qty,
334 x_final_weight_qty,
335 x_creation_date,
336 x_created_by,
337 x_last_update_date,
338 x_last_updated_by,
339 x_last_update_login
340 );
341
342 IF (p_action = 'INSERT') THEN
343 -- Call all the procedures related to Before Insert.
344 IF ( get_pk_for_validation(
345 new_references.unit_ass_item_group_id
346 )
347 ) THEN
348 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
349 igs_ge_msg_stack.add;
350 app_exception.raise_exception;
351 END IF;
352 check_uniqueness;
353 check_parent_existance;
354 ELSIF (p_action = 'UPDATE') THEN
355 -- Call all the procedures related to Before Update.
356 check_uniqueness;
357 check_parent_existance;
358 ELSIF (p_action = 'VALIDATE_INSERT') THEN
359 -- Call all the procedures related to Before Insert.
360 IF ( get_pk_for_validation (
361 new_references.unit_ass_item_group_id
362 )
363 ) THEN
364 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
365 igs_ge_msg_stack.add;
366 app_exception.raise_exception;
367 END IF;
368 check_uniqueness;
369 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
370 check_uniqueness;
371 ELSIF (p_action = 'VALIDATE_DELETE') THEN
372 Check_Child_Existance;
373 END IF;
374
375 IF (p_action IN ('VALIDATE_INSERT', 'VALIDATE_UPDATE', 'VALIDATE_DELETE')) THEN
376 l_rowid := NULL;
377 END IF;
378
379 END before_dml;
380
381
382 PROCEDURE insert_row (
383 x_rowid IN OUT NOCOPY VARCHAR2,
384 x_unit_ass_item_group_id IN OUT NOCOPY NUMBER,
385 x_unit_cd IN VARCHAR2,
386 x_version_number IN NUMBER,
387 x_cal_type IN VARCHAR2,
388 x_ci_sequence_number IN NUMBER,
389 x_group_name IN VARCHAR2,
390 x_midterm_formula_code IN VARCHAR2,
391 x_midterm_formula_qty IN NUMBER,
392 x_midterm_weight_qty IN NUMBER,
393 x_final_formula_code IN VARCHAR2,
394 x_final_formula_qty IN NUMBER,
395 x_final_weight_qty IN NUMBER,
396 x_mode IN VARCHAR2
397 ) AS
398 /*
399 || Created By : [email protected]
400 || Created On : 01-OCT-2003
401 || Purpose : Handles the INSERT DML logic for the table.
402 || Known limitations, enhancements or remarks :
403 || Change History :
404 || Who When What
405 || (reverse chronological order - newest change first)
406 */
407
408 x_last_update_date DATE;
409 x_last_updated_by NUMBER;
410 x_last_update_login NUMBER;
411
412 BEGIN
413
414 x_last_update_date := SYSDATE;
415 IF (x_mode = 'I') THEN
416 x_last_updated_by := 1;
417 x_last_update_login := 0;
418 ELSIF (x_mode = 'R') THEN
419 x_last_updated_by := fnd_global.user_id;
420 IF (x_last_updated_by IS NULL) THEN
421 x_last_updated_by := -1;
422 END IF;
423 x_last_update_login := fnd_global.login_id;
424 IF (x_last_update_login IS NULL) THEN
425 x_last_update_login := -1;
426 END IF;
427 ELSE
428 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
429 fnd_message.set_token ('ROUTINE', 'IGS_AS_UNIT_AI_GRP_PKG.INSERT_ROW');
430 igs_ge_msg_stack.add;
431 app_exception.raise_exception;
432 END IF;
433
434 x_unit_ass_item_group_id := NULL;
435
436 before_dml(
437 p_action => 'INSERT',
438 x_rowid => x_rowid,
439 x_unit_ass_item_group_id => x_unit_ass_item_group_id,
440 x_unit_cd => x_unit_cd,
441 x_version_number => x_version_number,
442 x_cal_type => x_cal_type,
443 x_ci_sequence_number => x_ci_sequence_number,
444 x_group_name => x_group_name,
445 x_midterm_formula_code => x_midterm_formula_code,
446 x_midterm_formula_qty => x_midterm_formula_qty,
447 x_midterm_weight_qty => x_midterm_weight_qty,
448 x_final_formula_code => x_final_formula_code,
449 x_final_formula_qty => x_final_formula_qty,
450 x_final_weight_qty => x_final_weight_qty,
451 x_creation_date => x_last_update_date,
452 x_created_by => x_last_updated_by,
453 x_last_update_date => x_last_update_date,
454 x_last_updated_by => x_last_updated_by,
455 x_last_update_login => x_last_update_login
456 );
457
458 INSERT INTO igs_as_unit_ai_grp (
459 unit_ass_item_group_id,
460 unit_cd,
461 version_number,
462 cal_type,
463 ci_sequence_number,
464 group_name,
465 midterm_formula_code,
466 midterm_formula_qty,
467 midterm_weight_qty,
468 final_formula_code,
469 final_formula_qty,
470 final_weight_qty,
471 creation_date,
472 created_by,
473 last_update_date,
474 last_updated_by,
475 last_update_login
476 ) VALUES (
477 igs_as_unit_ai_grp_s.NEXTVAL,
478 new_references.unit_cd,
479 new_references.version_number,
480 new_references.cal_type,
481 new_references.ci_sequence_number,
482 new_references.group_name,
483 new_references.midterm_formula_code,
484 new_references.midterm_formula_qty,
485 new_references.midterm_weight_qty,
486 new_references.final_formula_code,
487 new_references.final_formula_qty,
488 new_references.final_weight_qty,
489 x_last_update_date,
490 x_last_updated_by,
491 x_last_update_date,
492 x_last_updated_by,
493 x_last_update_login
494 ) RETURNING ROWID, unit_ass_item_group_id INTO x_rowid, x_unit_ass_item_group_id;
495
496 l_rowid := NULL;
497
498 END insert_row;
499
500
501 PROCEDURE lock_row (
502 x_rowid IN VARCHAR2,
503 x_unit_ass_item_group_id IN NUMBER,
504 x_unit_cd IN VARCHAR2,
505 x_version_number IN NUMBER,
506 x_cal_type IN VARCHAR2,
507 x_ci_sequence_number IN NUMBER,
508 x_group_name IN VARCHAR2,
509 x_midterm_formula_code IN VARCHAR2,
510 x_midterm_formula_qty IN NUMBER,
511 x_midterm_weight_qty IN NUMBER,
512 x_final_formula_code IN VARCHAR2,
513 x_final_formula_qty IN NUMBER,
514 x_final_weight_qty IN NUMBER
515 ) AS
516 /*
517 || Created By : [email protected]
518 || Created On : 01-OCT-2003
519 || Purpose : Handles the LOCK mechanism for the table.
520 || Known limitations, enhancements or remarks :
521 || Change History :
522 || Who When What
523 || (reverse chronological order - newest change first)
524 */
525 CURSOR c1 IS
526 SELECT
527 unit_cd,
528 version_number,
529 cal_type,
530 ci_sequence_number,
531 group_name,
532 midterm_formula_code,
533 midterm_formula_qty,
534 midterm_weight_qty,
535 final_formula_code,
536 final_formula_qty,
537 final_weight_qty
538 FROM igs_as_unit_ai_grp
539 WHERE rowid = x_rowid
540 FOR UPDATE NOWAIT;
541
542 tlinfo c1%ROWTYPE;
543
544 BEGIN
545
546 OPEN c1;
547 FETCH c1 INTO tlinfo;
548 IF (c1%notfound) THEN
549 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
550 igs_ge_msg_stack.add;
551 CLOSE c1;
552 app_exception.raise_exception;
553 RETURN;
554 END IF;
555 CLOSE c1;
556
557 IF (
558 (tlinfo.unit_cd = x_unit_cd)
559 AND (tlinfo.version_number = x_version_number)
560 AND (tlinfo.cal_type = x_cal_type)
561 AND (tlinfo.ci_sequence_number = x_ci_sequence_number)
562 AND (tlinfo.group_name = x_group_name)
563 AND ((tlinfo.midterm_formula_code = x_midterm_formula_code) OR ((tlinfo.midterm_formula_code IS NULL) AND (X_midterm_formula_code IS NULL)))
564 AND ((tlinfo.midterm_formula_qty = x_midterm_formula_qty) OR ((tlinfo.midterm_formula_qty IS NULL) AND (X_midterm_formula_qty IS NULL)))
565 AND ((tlinfo.midterm_weight_qty = x_midterm_weight_qty) OR ((tlinfo.midterm_weight_qty IS NULL) AND (X_midterm_weight_qty IS NULL)))
566 AND ((tlinfo.final_formula_code = x_final_formula_code) OR ((tlinfo.final_formula_code IS NULL) AND (X_final_formula_code IS NULL)))
567 AND ((tlinfo.final_formula_qty = x_final_formula_qty) OR ((tlinfo.final_formula_qty IS NULL) AND (X_final_formula_qty IS NULL)))
568 AND ((tlinfo.final_weight_qty = x_final_weight_qty) OR ((tlinfo.final_weight_qty IS NULL) AND (X_final_weight_qty IS NULL)))
569 ) THEN
570 NULL;
571 ELSE
572 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
573 igs_ge_msg_stack.add;
574 app_exception.raise_exception;
575 END IF;
576
577 RETURN;
578
579 END lock_row;
580
581
582 PROCEDURE update_row (
583 x_rowid IN VARCHAR2,
584 x_unit_ass_item_group_id IN NUMBER,
585 x_unit_cd IN VARCHAR2,
586 x_version_number IN NUMBER,
587 x_cal_type IN VARCHAR2,
588 x_ci_sequence_number IN NUMBER,
589 x_group_name IN VARCHAR2,
590 x_midterm_formula_code IN VARCHAR2,
591 x_midterm_formula_qty IN NUMBER,
592 x_midterm_weight_qty IN NUMBER,
593 x_final_formula_code IN VARCHAR2,
594 x_final_formula_qty IN NUMBER,
595 x_final_weight_qty IN NUMBER,
596 x_mode IN VARCHAR2
597 ) AS
598 /*
599 || Created By : [email protected]
600 || Created On : 01-OCT-2003
601 || Purpose : Handles the UPDATE DML logic for the table.
602 || Known limitations, enhancements or remarks :
603 || Change History :
604 || Who When What
605 || (reverse chronological order - newest change first)
606 */
607 x_last_update_date DATE ;
608 x_last_updated_by NUMBER;
609 x_last_update_login NUMBER;
610
611 BEGIN
612
613 x_last_update_date := SYSDATE;
614 IF (X_MODE = 'I') THEN
615 x_last_updated_by := 1;
616 x_last_update_login := 0;
617 ELSIF (x_mode = 'R') THEN
618 x_last_updated_by := fnd_global.user_id;
619 IF x_last_updated_by IS NULL THEN
620 x_last_updated_by := -1;
621 END IF;
622 x_last_update_login := fnd_global.login_id;
623 IF (x_last_update_login IS NULL) THEN
624 x_last_update_login := -1;
625 END IF;
626 ELSE
627 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
628 fnd_message.set_token ('ROUTINE', 'IGS_AS_UNIT_AI_GRP_PKG.UPDATE_ROW');
629 igs_ge_msg_stack.add;
630 app_exception.raise_exception;
631 END IF;
632
633 before_dml(
634 p_action => 'UPDATE',
635 x_rowid => x_rowid,
636 x_unit_ass_item_group_id => x_unit_ass_item_group_id,
637 x_unit_cd => x_unit_cd,
638 x_version_number => x_version_number,
639 x_cal_type => x_cal_type,
640 x_ci_sequence_number => x_ci_sequence_number,
641 x_group_name => x_group_name,
642 x_midterm_formula_code => x_midterm_formula_code,
643 x_midterm_formula_qty => x_midterm_formula_qty,
644 x_midterm_weight_qty => x_midterm_weight_qty,
645 x_final_formula_code => x_final_formula_code,
646 x_final_formula_qty => x_final_formula_qty,
647 x_final_weight_qty => x_final_weight_qty,
648 x_creation_date => x_last_update_date,
649 x_created_by => x_last_updated_by,
650 x_last_update_date => x_last_update_date,
651 x_last_updated_by => x_last_updated_by,
652 x_last_update_login => x_last_update_login
653 );
654
655 UPDATE igs_as_unit_ai_grp
656 SET
657 unit_cd = new_references.unit_cd,
658 version_number = new_references.version_number,
659 cal_type = new_references.cal_type,
660 ci_sequence_number = new_references.ci_sequence_number,
661 group_name = new_references.group_name,
662 midterm_formula_code = new_references.midterm_formula_code,
663 midterm_formula_qty = new_references.midterm_formula_qty,
664 midterm_weight_qty = new_references.midterm_weight_qty,
665 final_formula_code = new_references.final_formula_code,
666 final_formula_qty = new_references.final_formula_qty,
667 final_weight_qty = new_references.final_weight_qty,
668 last_update_date = x_last_update_date,
669 last_updated_by = x_last_updated_by,
670 last_update_login = x_last_update_login
671 WHERE rowid = x_rowid;
672
673 IF (SQL%NOTFOUND) THEN
674 RAISE NO_DATA_FOUND;
675 END IF;
676
677 l_rowid := NULL;
678
679 END update_row;
680
681
682 PROCEDURE add_row (
683 x_rowid IN OUT NOCOPY VARCHAR2,
684 x_unit_ass_item_group_id IN OUT NOCOPY NUMBER,
685 x_unit_cd IN VARCHAR2,
686 x_version_number IN NUMBER,
687 x_cal_type IN VARCHAR2,
688 x_ci_sequence_number IN NUMBER,
689 x_group_name IN VARCHAR2,
690 x_midterm_formula_code IN VARCHAR2,
691 x_midterm_formula_qty IN NUMBER,
692 x_midterm_weight_qty IN NUMBER,
693 x_final_formula_code IN VARCHAR2,
694 x_final_formula_qty IN NUMBER,
695 x_final_weight_qty IN NUMBER,
696 x_mode IN VARCHAR2
697 ) AS
698 /*
699 || Created By : [email protected]
700 || Created On : 01-OCT-2003
701 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
702 || Known limitations, enhancements or remarks :
703 || Change History :
704 || Who When What
705 || (reverse chronological order - newest change first)
706 */
707 CURSOR c1 IS
708 SELECT rowid
709 FROM igs_as_unit_ai_grp
710 WHERE unit_ass_item_group_id = x_unit_ass_item_group_id;
711
712 BEGIN
713
714 OPEN c1;
715 FETCH c1 INTO x_rowid;
716 IF (c1%NOTFOUND) THEN
717 CLOSE c1;
718
719 insert_row (
720 x_rowid,
721 x_unit_ass_item_group_id,
722 x_unit_cd,
723 x_version_number,
724 x_cal_type,
725 x_ci_sequence_number,
726 x_group_name,
727 x_midterm_formula_code,
728 x_midterm_formula_qty,
729 x_midterm_weight_qty,
730 x_final_formula_code,
731 x_final_formula_qty,
732 x_final_weight_qty,
733 x_mode
734 );
735 RETURN;
736 END IF;
737 CLOSE c1;
738
739 update_row (
740 x_rowid,
741 x_unit_ass_item_group_id,
742 x_unit_cd,
743 x_version_number,
744 x_cal_type,
745 x_ci_sequence_number,
746 x_group_name,
747 x_midterm_formula_code,
748 x_midterm_formula_qty,
749 x_midterm_weight_qty,
750 x_final_formula_code,
751 x_final_formula_qty,
752 x_final_weight_qty,
753 x_mode
754 );
755
756 END add_row;
757
758
759 PROCEDURE delete_row (
760 x_rowid IN VARCHAR2
761 ) AS
762 /*
763 || Created By : [email protected]
764 || Created On : 01-OCT-2003
765 || Purpose : Handles the DELETE DML logic for the table.
766 || Known limitations, enhancements or remarks :
767 || Change History :
768 || Who When What
769 || (reverse chronological order - newest change first)
770 */
771 BEGIN
772
773 before_dml (
774 p_action => 'DELETE',
775 x_rowid => x_rowid
776 );
777
778 DELETE FROM igs_as_unit_ai_grp
779 WHERE rowid = x_rowid;
780
781 IF (SQL%NOTFOUND) THEN
782 RAISE NO_DATA_FOUND;
783 END IF;
784
785 l_rowid := NULL;
786
787 END delete_row;
788
789
790 END igs_as_unit_ai_grp_pkg;