1 PACKAGE BODY IGS_EN_PLAN_UNITS_PKG AS
2 /* $Header: IGSEI79B.pls 120.2 2005/10/27 04:18:29 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_plan_units%ROWTYPE;
6 new_references igs_en_plan_units%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_person_id IN NUMBER,
12 x_course_cd IN VARCHAR2,
13 x_uoo_id IN NUMBER,
14 x_term_cal_type IN VARCHAR2,
15 x_term_ci_sequence_number IN NUMBER,
16 x_no_assessment_ind IN VARCHAR2,
17 x_sup_uoo_id IN NUMBER,
18 x_override_enrolled_cp IN NUMBER,
19 x_grading_schema_code IN VARCHAR2,
20 x_gs_version_number IN NUMBER,
21 x_core_indicator_code IN VARCHAR2,
22 x_alternative_title IN VARCHAR2,
23 x_cart_error_flag IN VARCHAR2,
24 x_session_id IN NUMBER,
25 x_creation_date IN DATE,
26 x_created_by IN NUMBER,
27 x_last_update_date IN DATE,
28 x_last_updated_by IN NUMBER,
29 x_last_update_login IN NUMBER
30 ) AS
31 /*
32 || Created By :
33 || Created On : 30-MAY-2005
34 || Purpose : Initialises the Old and New references for the columns of the table.
35 || Known limitations, enhancements or remarks :
36 || Change History :
37 || Who When What
38 || (reverse chronological order - newest change first)
39 */
40
41 CURSOR cur_old_ref_values IS
42 SELECT *
43 FROM igs_en_plan_units
44 WHERE rowid = x_rowid;
45
46 BEGIN
47
48 l_rowid := x_rowid;
49
50 -- Code for setting the Old and New Reference Values.
51 -- Populate Old Values.
52 OPEN cur_old_ref_values;
53 FETCH cur_old_ref_values INTO old_references;
54 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
55 CLOSE cur_old_ref_values;
56 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
57 igs_ge_msg_stack.add;
58 app_exception.raise_exception;
59 RETURN;
60 END IF;
61 CLOSE cur_old_ref_values;
62
63 -- Populate New Values.
64 new_references.person_id := x_person_id;
65 new_references.course_cd := x_course_cd;
66 new_references.uoo_id := x_uoo_id;
67 new_references.term_cal_type := x_term_cal_type;
68 new_references.term_ci_sequence_number := x_term_ci_sequence_number;
69 new_references.no_assessment_ind := x_no_assessment_ind;
70 new_references.sup_uoo_id := x_sup_uoo_id;
71 new_references.override_enrolled_cp := NVL(IGS_EN_GEN_015.enrp_get_appr_cr_pt(x_person_id,x_uoo_id), x_override_enrolled_cp);
72 new_references.grading_schema_code := x_grading_schema_code;
73 new_references.gs_version_number := x_gs_version_number;
74 new_references.core_indicator_code := x_core_indicator_code;
75 new_references.alternative_title := x_alternative_title;
76 new_references.cart_error_flag := x_cart_error_flag;
77 new_references.session_id := x_session_id;
78
79 IF (p_action = 'UPDATE') THEN
80 new_references.creation_date := old_references.creation_date;
81 new_references.created_by := old_references.created_by;
82 ELSE
83 new_references.creation_date := x_creation_date;
84 new_references.created_by := x_created_by;
85 END IF;
86
87 new_references.last_update_date := x_last_update_date;
88 new_references.last_updated_by := x_last_updated_by;
89 new_references.last_update_login := x_last_update_login;
90
91 END set_column_values;
92
93
94 PROCEDURE check_parent_existance AS
95 /*
96 || Created By :
97 || Created On : 30-MAY-2005
98 || Purpose : Checks for the existance of Parent records.
99 || Known limitations, enhancements or remarks :
100 || Change History :
101 || Who When What
102 || (reverse chronological order - newest change first)
103 */
104 BEGIN
105
106 -- The following validation is added because
107 -- SPA terms record is created only when SUA (enrolled) attempt is being created or
108 -- PLAN units record is created.
109 -- When Add_units_api gets some error while creating SUA record it creates error
110 -- plan units record. While creating error plan units record it is not necessary to
111 -- check for FK relationship, as error plan units record are just used for display
112 -- purpose only and not used for any processing.
113 IF new_references.cart_error_flag = 'Y' THEN
114 RETURN;
115 END IF;
116
117 IF (((old_references.person_id = new_references.person_id) AND
118 (old_references.course_cd = new_references.course_cd)) OR
119 ((new_references.person_id IS NULL) OR
120 (new_references.course_cd IS NULL))) THEN
121 NULL;
122 ELSIF NOT igs_en_stdnt_ps_att_pkg.get_pk_for_validation (
123 new_references.person_id,
124 new_references.course_cd
125 ) THEN
126 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
127 igs_ge_msg_stack.add;
128 app_exception.raise_exception;
129 END IF;
130
131 IF (((old_references.person_id = new_references.person_id) AND
132 (old_references.course_cd = new_references.course_cd) AND
133 (old_references.term_cal_type = new_references.term_cal_type) AND
134 (old_references.term_ci_sequence_number = new_references.term_ci_sequence_number)) OR
135 ((new_references.person_id IS NULL) OR
136 (new_references.course_cd IS NULL) OR
137 (new_references.term_cal_type IS NULL) OR
138 (new_references.term_ci_sequence_number IS NULL))) THEN
139 NULL;
140 ELSIF NOT igs_en_spa_terms_pkg.get_uk_for_validation (
141 new_references.person_id,
142 new_references.course_cd,
143 new_references.term_cal_type,
144 new_references.term_ci_sequence_number
145 ) THEN
146 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
147 igs_ge_msg_stack.add;
148 app_exception.raise_exception;
149 END IF;
150
151 END check_parent_existance;
152
153
154 FUNCTION get_pk_for_validation (
155 x_person_id IN NUMBER,
156 x_course_cd IN VARCHAR2,
157 x_uoo_id IN NUMBER,
158 x_cart_error_flag IN VARCHAR2
159 ) RETURN BOOLEAN AS
160 /*
161 || Created By :
162 || Created On : 30-MAY-2005
163 || Purpose : Validates the Primary Key of the table.
164 || Known limitations, enhancements or remarks :
165 || Change History :
166 || Who When What
167 || (reverse chronological order - newest change first)
168 */
169 CURSOR cur_rowid IS
170 SELECT rowid
171 FROM igs_en_plan_units
172 WHERE person_id = x_person_id
173 AND course_cd = x_course_cd
174 AND uoo_id = x_uoo_id
175 AND cart_error_flag = x_cart_error_flag
176 FOR UPDATE NOWAIT;
177
178 lv_rowid cur_rowid%RowType;
179
180 BEGIN
181
182 OPEN cur_rowid;
183 FETCH cur_rowid INTO lv_rowid;
184 IF (cur_rowid%FOUND) THEN
185 CLOSE cur_rowid;
186 RETURN(TRUE);
187 ELSE
188 CLOSE cur_rowid;
189 RETURN(FALSE);
190 END IF;
191
192 END get_pk_for_validation;
193
194
195 PROCEDURE get_fk_igs_en_stdnt_ps_att (
196 x_person_id IN NUMBER,
197 x_course_cd IN VARCHAR2
198 ) AS
199 /*
200 || Created By :
201 || Created On : 30-MAY-2005
202 || Purpose : Validates the Foreign Keys for the table.
203 || Known limitations, enhancements or remarks :
204 || Change History :
205 || Who When What
206 || (reverse chronological order - newest change first)
207 */
208 CURSOR cur_rowid IS
209 SELECT rowid
210 FROM igs_en_plan_units
211 WHERE ((course_cd = x_course_cd) AND
212 (person_id = x_person_id));
213
214 lv_rowid cur_rowid%RowType;
215
216 BEGIN
217
218 OPEN cur_rowid;
219 FETCH cur_rowid INTO lv_rowid;
220 IF (cur_rowid%FOUND) THEN
221 CLOSE cur_rowid;
222
223 fnd_message.set_name ('IGS', 'IGS_EN_PLSHT_SCA_FK');
224 igs_ge_msg_stack.add;
225 app_exception.raise_exception;
226 RETURN;
227 END IF;
228 CLOSE cur_rowid;
229
230 END get_fk_igs_en_stdnt_ps_att;
231
232
233 PROCEDURE get_fk_igs_en_spa_terms (
234 x_person_id IN NUMBER,
235 x_program_cd IN VARCHAR2,
236 x_term_cal_type IN VARCHAR2,
237 x_term_sequence_number IN NUMBER
238 ) AS
239 /*
240 || Created By :
241 || Created On : 30-MAY-2005
242 || Purpose : Validates the Foreign Keys for the table.
243 || Known limitations, enhancements or remarks :
244 || Change History :
245 || Who When What
246 || (reverse chronological order - newest change first)
247 */
248 CURSOR cur_rowid IS
249 SELECT rowid
250 FROM igs_en_plan_units
251 WHERE ((course_cd = x_program_cd) AND
252 (person_id = x_person_id) AND
253 (term_cal_type = x_term_cal_type) AND
254 (term_ci_sequence_number = x_term_sequence_number));
255
256 lv_rowid cur_rowid%RowType;
257
258 BEGIN
259
260 OPEN cur_rowid;
261 FETCH cur_rowid INTO lv_rowid;
262 IF (cur_rowid%FOUND) THEN
263 CLOSE cur_rowid;
264 fnd_message.set_name ('IGS', 'IGS_EN_PLSHT_ESPT_FK');
265 igs_ge_msg_stack.add;
266 app_exception.raise_exception;
267 RETURN;
268 END IF;
269 CLOSE cur_rowid;
270
271 END get_fk_igs_en_spa_terms;
272
273 PROCEDURE before_insert_update(p_action IN VARCHAR2) IS
274
275 CURSOR c_spa_exists (cp_person_id IN NUMBER, cp_program_cd IN VARCHAR2, cp_term_cal IN VARCHAR2, cp_term_seq IN NUMBER) IS
276 SELECT PLAN_SHT_STATUS
277 FROM IGS_EN_SPA_TERMS
278 WHERE person_id = cp_person_id
279 AND program_cd = cp_program_cd
280 AND term_cal_type = cp_term_cal
281 AND term_sequence_number = cp_term_seq;
282
283
284 l_message_name VARCHAR2(2000);
285 l_plan_sht_status igs_en_spa_terms.plan_sht_status%TYPE;
286
287 BEGIN
288 IF p_action NOT IN ('INSERT','UPDATE') or new_references.cart_error_flag = 'Y' THEN
289 RETURN;
290 END IF;
291
292 OPEN c_spa_exists(new_references.person_id, new_references.course_cd, new_references.term_cal_type, new_references.term_ci_sequence_number);
293 FETCH c_spa_exists INTO l_plan_sht_status;
294 IF c_spa_exists%FOUND THEN
295 CLOSE c_spa_exists;
296 IF l_plan_sht_status = 'PLAN' THEN
297 RETURN;
298 END IF;
299 ELSE
300 CLOSE c_spa_exists;
301 END IF;
302
303
304 -- Call the API to Create/Update the term record.
305 igs_en_spa_terms_api.create_update_term_rec(p_person_id => new_references.person_id,
306 p_program_cd => new_references.course_cd,
307 p_term_cal_type =>new_references.term_cal_type,
308 p_term_sequence_number => new_references.term_ci_sequence_number,
309 p_plan_sht_status => 'PLAN',
310 p_ripple_frwrd => FALSE,
311 p_message_name => l_message_name,
312 p_update_rec => TRUE);
313
314
315 END before_insert_update;
316
317 PROCEDURE After_DML (
318 p_action IN VARCHAR2,
319 x_rowid IN VARCHAR2
320 ) AS
321 CURSOR c_plan_rec_exists (cp_person_id IGS_EN_PLAN_UNITS.PERSON_ID%TYPE,
322 cp_course_cd IGS_EN_PLAN_UNITS.COURSE_CD%TYPE,
323 cp_term_cal_type IGS_EN_PLAN_UNITS.TERM_CAL_TYPE%TYPE,
324 cp_term_ci_sequence_number IGS_EN_PLAN_UNITS.TERM_CI_SEQUENCE_NUMBER%TYPE
325 )IS
326 SELECT UOO_ID FROM IGS_EN_PLAN_UNITS
327 WHERE PERSON_ID= cp_person_id
328 AND COURSE_CD = cp_course_Cd
329 AND TERM_CAL_TYPE = cp_term_cal_type
330 AND TERM_CI_SEQUENCE_NUMBER =cp_term_ci_sequence_number
331 AND CART_ERROR_FLAG ='N';
332
333 l_dummy IGS_EN_PLAN_UNITS.UOO_ID%TYPE;
334 l_message_name VARCHAR2(30);
335 BEGIN
336
337 IF p_action = 'DELETE' THEN
338
339 OPEN c_plan_rec_exists(old_references.person_id,old_references.course_cd,
340 old_references.term_cal_type,old_references.term_ci_sequence_number) ;
341 FETCH c_plan_rec_exists INTO l_dummy;
342 IF c_plan_rec_exists%NOTFOUND THEN
343 -- Call the API to Create/Update the term record.
344 igs_en_spa_terms_api.create_update_term_rec(p_person_id => old_references.person_id,
345 p_program_cd => old_references.course_cd,
346 p_term_cal_type =>old_references.term_cal_type,
347 p_term_sequence_number => old_references.term_ci_sequence_number,
348 p_plan_sht_status => 'NONE',
349 p_ripple_frwrd => FALSE,
350 p_message_name => l_message_name,
351 p_update_rec => TRUE);
352 END IF; -- c_plan_rec_exists%NOTFOUND
353 CLOSE c_plan_rec_exists;
354 END IF; -- p_action = 'DELETE'
355
356 END;
357
358 PROCEDURE before_dml (
359 p_action IN VARCHAR2,
360 x_rowid IN VARCHAR2,
361 x_person_id IN NUMBER,
362 x_course_cd IN VARCHAR2,
363 x_uoo_id IN NUMBER,
364 x_term_cal_type IN VARCHAR2,
365 x_term_ci_sequence_number IN NUMBER,
366 x_no_assessment_ind IN VARCHAR2,
367 x_sup_uoo_id IN NUMBER,
368 x_override_enrolled_cp IN NUMBER,
369 x_grading_schema_code IN VARCHAR2,
370 x_gs_version_number IN NUMBER,
371 x_core_indicator_code IN VARCHAR2,
372 x_alternative_title IN VARCHAR2,
373 x_cart_error_flag IN VARCHAR2,
374 x_session_id IN NUMBER,
375 x_creation_date IN DATE,
376 x_created_by IN NUMBER,
377 x_last_update_date IN DATE,
378 x_last_updated_by IN NUMBER,
379 x_last_update_login IN NUMBER
380 ) AS
381 /*
382 || Created By :
383 || Created On : 30-MAY-2005
384 || Purpose : Initialises the columns, Checks Constraints, Calls the
385 || Trigger Handlers for the table, before any DML operation.
386 || Known limitations, enhancements or remarks :
387 || Change History :
388 || Who When What
389 || (reverse chronological order - newest change first)
390 */
391 CURSOR cur_sub_uoo(cp_n_uoo_id IN NUMBER) IS
392 SELECT sub.sup_uoo_id
393 FROM igs_ps_unit_ofr_opt sub
394 WHERE sub.uoo_id = cp_n_uoo_id ;
395 l_sup_uoo_id igs_ps_unit_ofr_opt.sup_uoo_id%TYPE;
396
397 BEGIN
398
399 set_column_values (
400 p_action,
401 x_rowid,
402 x_person_id,
403 x_course_cd,
404 x_uoo_id,
405 x_term_cal_type,
406 x_term_ci_sequence_number,
407 x_no_assessment_ind,
408 x_sup_uoo_id,
409 x_override_enrolled_cp,
410 x_grading_schema_code,
411 x_gs_version_number,
412 x_core_indicator_code,
413 x_alternative_title,
414 x_cart_error_flag,
415 x_session_id,
416 x_creation_date,
417 x_created_by,
418 x_last_update_date,
419 x_last_updated_by,
420 x_last_update_login
421 );
422
423 IF (p_action = 'INSERT') THEN
424 -- Call all the procedures related to Before Insert.
425 IF ( get_pk_for_validation(
426 new_references.person_id,
427 new_references.course_cd,
428 new_references.uoo_id,
429 new_references.cart_error_flag
430 )
431 ) THEN
432 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
433 igs_ge_msg_stack.add;
434 app_exception.raise_exception;
435 END IF;
436 before_insert_update(p_action);
437 check_parent_existance;
438
439 -- when taking as audit, the enrolled_cp is 0
440 IF (new_references.no_assessment_ind = 'Y') THEN
441 new_references.override_enrolled_cp := 0;
442 END IF;
443
444 ELSIF (p_action = 'UPDATE') THEN
445 -- Call all the procedures related to Before Update.
446 before_insert_update(p_action);
447 check_parent_existance;
448
449 -- when taking as audit, the enrolled_cp is 0
450 IF old_references.no_assessment_ind = 'N'
451 AND new_references.no_assessment_ind = 'Y'
452 THEN
453 new_references.override_enrolled_cp := 0;
454 END IF;
455
456 ELSIF (p_action = 'VALIDATE_INSERT') THEN
457 -- Call all the procedures related to Before Insert.
458 IF ( get_pk_for_validation (
459 new_references.person_id,
460 new_references.course_cd,
461 new_references.uoo_id,
462 new_references.cart_error_flag
463 )
464 ) THEN
465 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
466 igs_ge_msg_stack.add;
467 app_exception.raise_exception;
468 END IF;
469 END IF;
470
471 -- populate the sup_uoo_id if context unit is a subordinate unit
472 IF p_action IN ( 'INSERT','UPDATE') AND new_references.sup_uoo_id IS NULL THEN
473 OPEN cur_sub_uoo(new_references.uoo_id);
474 FETCH cur_sub_uoo INTO new_references.sup_uoo_id;
475 CLOSE cur_sub_uoo;
476 END IF;
477
478 END before_dml;
479
480
481 PROCEDURE insert_row (
482 x_rowid IN OUT NOCOPY VARCHAR2,
483 x_person_id IN NUMBER,
484 x_course_cd IN VARCHAR2,
485 x_uoo_id IN NUMBER,
486 x_term_cal_type IN VARCHAR2,
487 x_term_ci_sequence_number IN NUMBER,
488 x_no_assessment_ind IN VARCHAR2,
489 x_sup_uoo_id IN NUMBER,
490 x_override_enrolled_cp IN NUMBER,
491 x_grading_schema_code IN VARCHAR2,
492 x_gs_version_number IN NUMBER,
493 x_core_indicator_code IN VARCHAR2,
494 x_alternative_title IN VARCHAR2,
495 x_cart_error_flag IN VARCHAR2,
496 x_session_id IN NUMBER,
497 x_mode IN VARCHAR2
498 ) AS
499 /*
500 || Created By :
501 || Created On : 30-MAY-2005
502 || Purpose : Handles the INSERT DML logic for the table.
503 || Known limitations, enhancements or remarks :
504 || Change History :
505 || Who When What
506 || (reverse chronological order - newest change first)
507 */
508
509 x_last_update_date DATE;
510 x_last_updated_by NUMBER;
511 x_last_update_login NUMBER;
512
513 BEGIN
514
515 x_last_update_date := SYSDATE;
516 IF (x_mode = 'I') THEN
517 x_last_updated_by := 1;
518 x_last_update_login := 0;
519 ELSIF (x_mode = 'R') THEN
520 x_last_updated_by := fnd_global.user_id;
521 IF (x_last_updated_by IS NULL) THEN
522 x_last_updated_by := -1;
523 END IF;
524 x_last_update_login := fnd_global.login_id;
525 IF (x_last_update_login IS NULL) THEN
526 x_last_update_login := -1;
527 END IF;
528 ELSE
529 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
530 fnd_message.set_token ('ROUTINE', 'IGS_EN_PLAN_UNITS_PKG.INSERT_ROW');
531 igs_ge_msg_stack.add;
532 app_exception.raise_exception;
533 END IF;
534
535 before_dml(
536 p_action => 'INSERT',
537 x_rowid => x_rowid,
538 x_person_id => x_person_id,
539 x_course_cd => x_course_cd,
540 x_uoo_id => x_uoo_id,
541 x_term_cal_type => x_term_cal_type,
542 x_term_ci_sequence_number => x_term_ci_sequence_number,
543 x_no_assessment_ind => x_no_assessment_ind,
544 x_sup_uoo_id => x_sup_uoo_id,
545 x_override_enrolled_cp => x_override_enrolled_cp,
546 x_grading_schema_code => x_grading_schema_code,
547 x_gs_version_number => x_gs_version_number,
548 x_core_indicator_code => x_core_indicator_code,
549 x_alternative_title => x_alternative_title,
550 x_cart_error_flag => x_cart_error_flag,
551 x_session_id => x_session_id,
552 x_creation_date => x_last_update_date,
553 x_created_by => x_last_updated_by,
554 x_last_update_date => x_last_update_date,
555 x_last_updated_by => x_last_updated_by,
556 x_last_update_login => x_last_update_login
557 );
558
559 INSERT INTO igs_en_plan_units (
560 person_id,
561 course_cd,
562 uoo_id,
563 term_cal_type,
564 term_ci_sequence_number,
565 no_assessment_ind,
566 sup_uoo_id,
567 override_enrolled_cp,
568 grading_schema_code,
569 gs_version_number,
570 core_indicator_code,
571 alternative_title,
572 cart_error_flag,
573 session_id,
574 creation_date,
575 created_by,
576 last_update_date,
577 last_updated_by,
578 last_update_login
579 ) VALUES (
580 new_references.person_id,
581 new_references.course_cd,
582 new_references.uoo_id,
583 new_references.term_cal_type,
584 new_references.term_ci_sequence_number,
585 new_references.no_assessment_ind,
586 new_references.sup_uoo_id,
587 new_references.override_enrolled_cp,
588 new_references.grading_schema_code,
589 new_references.gs_version_number,
590 new_references.core_indicator_code,
591 new_references.alternative_title,
592 new_references.cart_error_flag,
593 new_references.session_id,
594 x_last_update_date,
595 x_last_updated_by,
596 x_last_update_date,
597 x_last_updated_by,
598 x_last_update_login
599 ) RETURNING ROWID INTO x_rowid;
600
601 END insert_row;
602
603
604 PROCEDURE lock_row (
605 x_rowid IN VARCHAR2,
606 x_person_id IN NUMBER,
607 x_course_cd IN VARCHAR2,
608 x_uoo_id IN NUMBER,
609 x_term_cal_type IN VARCHAR2,
610 x_term_ci_sequence_number IN NUMBER,
611 x_no_assessment_ind IN VARCHAR2,
612 x_sup_uoo_id IN NUMBER,
613 x_override_enrolled_cp IN NUMBER,
614 x_grading_schema_code IN VARCHAR2,
615 x_gs_version_number IN NUMBER,
616 x_core_indicator_code IN VARCHAR2,
617 x_alternative_title IN VARCHAR2,
618 x_cart_error_flag IN VARCHAR2,
619 x_session_id IN NUMBER
620 ) AS
621 /*
622 || Created By :
623 || Created On : 30-MAY-2005
624 || Purpose : Handles the LOCK mechanism for the table.
625 || Known limitations, enhancements or remarks :
626 || Change History :
627 || Who When What
628 || (reverse chronological order - newest change first)
629 */
630 CURSOR c1 IS
631 SELECT
632 term_cal_type,
633 term_ci_sequence_number,
634 no_assessment_ind,
635 sup_uoo_id,
636 override_enrolled_cp,
637 grading_schema_code,
638 gs_version_number,
639 core_indicator_code,
640 alternative_title,
641 session_id
642 FROM igs_en_plan_units
643 WHERE rowid = x_rowid
644 FOR UPDATE NOWAIT;
645
646 tlinfo c1%ROWTYPE;
647
648 BEGIN
649
650 OPEN c1;
651 FETCH c1 INTO tlinfo;
652 IF (c1%notfound) THEN
653 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
654 igs_ge_msg_stack.add;
655 CLOSE c1;
656 app_exception.raise_exception;
657 RETURN;
658 END IF;
659 CLOSE c1;
660
661 IF (
662 (tlinfo.term_cal_type = x_term_cal_type)
663 AND (tlinfo.term_ci_sequence_number = x_term_ci_sequence_number)
664 AND (tlinfo.no_assessment_ind = x_no_assessment_ind)
665 AND ((tlinfo.sup_uoo_id = x_sup_uoo_id) OR ((tlinfo.sup_uoo_id IS NULL) AND (X_sup_uoo_id IS NULL)))
666 AND ((tlinfo.override_enrolled_cp = x_override_enrolled_cp) OR ((tlinfo.override_enrolled_cp IS NULL) AND (X_override_enrolled_cp IS NULL)))
667 AND ((tlinfo.grading_schema_code = x_grading_schema_code) OR ((tlinfo.grading_schema_code IS NULL) AND (X_grading_schema_code IS NULL)))
668 AND ((tlinfo.gs_version_number = x_gs_version_number) OR ((tlinfo.gs_version_number IS NULL) AND (X_gs_version_number IS NULL)))
669 AND ((tlinfo.core_indicator_code = x_core_indicator_code) OR ((tlinfo.core_indicator_code IS NULL) AND (X_core_indicator_code IS NULL)))
670 AND ((tlinfo.alternative_title = x_alternative_title) OR ((tlinfo.alternative_title IS NULL) AND (X_alternative_title IS NULL)))
671 AND (tlinfo.session_id = x_session_id)
672 ) THEN
673 NULL;
674 ELSE
675 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
676 igs_ge_msg_stack.add;
677 app_exception.raise_exception;
678 END IF;
679
680 RETURN;
681
682 END lock_row;
683
684
685 PROCEDURE update_row (
686 x_rowid IN VARCHAR2,
687 x_person_id IN NUMBER,
688 x_course_cd IN VARCHAR2,
689 x_uoo_id IN NUMBER,
690 x_term_cal_type IN VARCHAR2,
691 x_term_ci_sequence_number IN NUMBER,
692 x_no_assessment_ind IN VARCHAR2,
693 x_sup_uoo_id IN NUMBER,
694 x_override_enrolled_cp IN NUMBER,
695 x_grading_schema_code IN VARCHAR2,
696 x_gs_version_number IN NUMBER,
697 x_core_indicator_code IN VARCHAR2,
698 x_alternative_title IN VARCHAR2,
699 x_cart_error_flag IN VARCHAR2,
700 x_session_id IN NUMBER,
701 x_mode IN VARCHAR2
702 ) AS
703 /*
704 || Created By :
705 || Created On : 30-MAY-2005
706 || Purpose : Handles the UPDATE DML logic for the table.
707 || Known limitations, enhancements or remarks :
708 || Change History :
709 || Who When What
710 || (reverse chronological order - newest change first)
711 */
712 x_last_update_date DATE ;
713 x_last_updated_by NUMBER;
714 x_last_update_login NUMBER;
715
716 BEGIN
717
718 x_last_update_date := SYSDATE;
719 IF (X_MODE = 'I') THEN
720 x_last_updated_by := 1;
721 x_last_update_login := 0;
722 ELSIF (x_mode = 'R') THEN
723 x_last_updated_by := fnd_global.user_id;
724 IF x_last_updated_by IS NULL THEN
725 x_last_updated_by := -1;
726 END IF;
727 x_last_update_login := fnd_global.login_id;
728 IF (x_last_update_login IS NULL) THEN
729 x_last_update_login := -1;
730 END IF;
731 ELSE
732 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
733 fnd_message.set_token ('ROUTINE', 'IGS_EN_PLAN_UNITS_PKG.UPDATE_ROW');
734 igs_ge_msg_stack.add;
735 app_exception.raise_exception;
736 END IF;
737
738 before_dml(
739 p_action => 'UPDATE',
740 x_rowid => x_rowid,
741 x_person_id => x_person_id,
742 x_course_cd => x_course_cd,
743 x_uoo_id => x_uoo_id,
744 x_term_cal_type => x_term_cal_type,
745 x_term_ci_sequence_number => x_term_ci_sequence_number,
746 x_no_assessment_ind => x_no_assessment_ind,
747 x_sup_uoo_id => x_sup_uoo_id,
748 x_override_enrolled_cp => x_override_enrolled_cp,
749 x_grading_schema_code => x_grading_schema_code,
750 x_gs_version_number => x_gs_version_number,
751 x_core_indicator_code => x_core_indicator_code,
752 x_alternative_title => x_alternative_title,
753 x_cart_error_flag => x_cart_error_flag,
754 x_session_id => x_session_id,
755 x_creation_date => x_last_update_date,
756 x_created_by => x_last_updated_by,
757 x_last_update_date => x_last_update_date,
758 x_last_updated_by => x_last_updated_by,
759 x_last_update_login => x_last_update_login
760 );
761
762 UPDATE igs_en_plan_units
763 SET
764 term_cal_type = new_references.term_cal_type,
765 term_ci_sequence_number = new_references.term_ci_sequence_number,
766 no_assessment_ind = new_references.no_assessment_ind,
767 sup_uoo_id = new_references.sup_uoo_id,
768 override_enrolled_cp = new_references.override_enrolled_cp,
769 grading_schema_code = new_references.grading_schema_code,
770 gs_version_number = new_references.gs_version_number,
771 core_indicator_code = new_references.core_indicator_code,
772 alternative_title = new_references.alternative_title,
773 session_id = new_references.session_id,
774 last_update_date = x_last_update_date,
775 last_updated_by = x_last_updated_by,
776 last_update_login = x_last_update_login
777 WHERE rowid = x_rowid;
778
779 IF (SQL%NOTFOUND) THEN
780 RAISE NO_DATA_FOUND;
781 END IF;
782
783 END update_row;
784
785
786 PROCEDURE add_row (
787 x_rowid IN OUT NOCOPY VARCHAR2,
788 x_person_id IN NUMBER,
789 x_course_cd IN VARCHAR2,
790 x_uoo_id IN NUMBER,
791 x_term_cal_type IN VARCHAR2,
792 x_term_ci_sequence_number IN NUMBER,
793 x_no_assessment_ind IN VARCHAR2,
794 x_sup_uoo_id IN NUMBER,
795 x_override_enrolled_cp IN NUMBER,
796 x_grading_schema_code IN VARCHAR2,
797 x_gs_version_number IN NUMBER,
798 x_core_indicator_code IN VARCHAR2,
799 x_alternative_title IN VARCHAR2,
800 x_cart_error_flag IN VARCHAR2,
801 x_session_id IN NUMBER,
802 x_mode IN VARCHAR2
803 ) AS
804 /*
805 || Created By :
806 || Created On : 30-MAY-2005
807 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
808 || Known limitations, enhancements or remarks :
809 || Change History :
810 || Who When What
811 || (reverse chronological order - newest change first)
812 */
813 CURSOR c1 IS
814 SELECT rowid
815 FROM igs_en_plan_units
816 WHERE person_id = x_person_id
817 AND course_cd = x_course_cd
818 AND uoo_id = x_uoo_id
819 AND cart_error_flag = x_cart_error_flag;
820
821 BEGIN
822
823 OPEN c1;
824 FETCH c1 INTO x_rowid;
825 IF (c1%NOTFOUND) THEN
826 CLOSE c1;
827
828 insert_row (
829 x_rowid,
830 x_person_id,
831 x_course_cd,
832 x_uoo_id,
833 x_term_cal_type,
834 x_term_ci_sequence_number,
835 x_no_assessment_ind,
836 x_sup_uoo_id,
837 x_override_enrolled_cp,
838 x_grading_schema_code,
839 x_gs_version_number,
840 x_core_indicator_code,
841 x_alternative_title,
842 x_cart_error_flag,
843 x_session_id,
844 x_mode
845 );
846 RETURN;
847 END IF;
848 CLOSE c1;
849
850 update_row (
851 x_rowid,
852 x_person_id,
853 x_course_cd,
854 x_uoo_id,
855 x_term_cal_type,
856 x_term_ci_sequence_number,
857 x_no_assessment_ind,
858 x_sup_uoo_id,
859 x_override_enrolled_cp,
860 x_grading_schema_code,
861 x_gs_version_number,
862 x_core_indicator_code,
863 x_alternative_title,
864 x_cart_error_flag,
865 x_session_id,
866 x_mode
867 );
868
869 END add_row;
870
871
872 PROCEDURE delete_row (
873 x_rowid IN VARCHAR2
874 ) AS
875 /*
876 || Created By :
877 || Created On : 30-MAY-2005
878 || Purpose : Handles the DELETE DML logic for the table.
879 || Known limitations, enhancements or remarks :
880 || Change History :
881 || Who When What
882 || (reverse chronological order - newest change first)
883 */
884 BEGIN
885
886 before_dml (
887 p_action => 'DELETE',
888 x_rowid => x_rowid
889 );
890
891 DELETE FROM igs_en_plan_units
892 WHERE rowid = x_rowid;
893
894 IF (SQL%NOTFOUND) THEN
895 RAISE NO_DATA_FOUND;
896 END IF;
897
898 After_DML(
899 p_action => 'DELETE',
900 x_rowid => X_ROWID
901 );
902
903
904 END delete_row;
905
906
907 END igs_en_plan_units_pkg;
[Home] [Help]