1 PACKAGE BODY IGS_AS_SU_SETATMPT_PKG AS
2 /* $Header: IGSDI29B.pls 120.2 2006/05/29 07:53:01 sarakshi noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AS_SU_SETATMPT%ROWTYPE;
6 new_references IGS_AS_SU_SETATMPT%ROWTYPE;
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_person_id IN NUMBER ,
11 x_course_cd IN VARCHAR2 ,
12 x_unit_set_cd IN VARCHAR2 ,
13 x_us_version_number IN NUMBER ,
14 x_sequence_number IN NUMBER ,
15 x_selection_dt IN DATE ,
16 x_student_confirmed_ind IN VARCHAR2 ,
17 x_end_dt IN DATE ,
18 x_parent_unit_set_cd IN VARCHAR2 ,
19 x_parent_sequence_number IN NUMBER ,
20 x_primary_set_ind IN VARCHAR2 ,
21 x_voluntary_end_ind IN VARCHAR2 ,
22 x_authorised_person_id IN NUMBER ,
23 x_authorised_on IN DATE ,
24 x_override_title IN VARCHAR2 ,
25 x_rqrmnts_complete_ind IN VARCHAR2 ,
26 x_rqrmnts_complete_dt IN DATE ,
27 x_s_completed_source_type IN VARCHAR2 ,
28 x_catalog_cal_type IN VARCHAR2 ,
29 x_catalog_seq_num IN NUMBER ,
30 x_attribute_category IN VARCHAR2 ,
31 x_attribute1 IN VARCHAR2 ,
32 x_attribute2 IN VARCHAR2 ,
33 x_attribute3 IN VARCHAR2 ,
34 x_attribute4 IN VARCHAR2 ,
35 x_attribute5 IN VARCHAR2 ,
36 x_attribute6 IN VARCHAR2 ,
37 x_attribute7 IN VARCHAR2 ,
38 x_attribute8 IN VARCHAR2 ,
39 x_attribute9 IN VARCHAR2 ,
40 x_attribute10 IN VARCHAR2 ,
41 x_attribute11 IN VARCHAR2 ,
42 x_attribute12 IN VARCHAR2 ,
43 x_attribute13 IN VARCHAR2 ,
44 x_attribute14 IN VARCHAR2 ,
45 x_attribute15 IN VARCHAR2 ,
46 x_attribute16 IN VARCHAR2 ,
47 x_attribute17 IN VARCHAR2 ,
48 x_attribute18 IN VARCHAR2 ,
49 x_attribute19 IN VARCHAR2 ,
50 x_attribute20 IN VARCHAR2 ,
51 x_creation_date IN DATE ,
52 x_created_by IN NUMBER ,
53 x_last_update_date IN DATE ,
54 x_last_updated_by IN NUMBER ,
55 x_last_update_login IN NUMBER
56 ) AS
57 CURSOR cur_old_ref_values IS
58 SELECT *
59 FROM IGS_AS_SU_SETATMPT
60 WHERE ROWID = x_rowid;
61 BEGIN
62 l_rowid := x_rowid;
63 -- Code for setting the Old and New Reference Values.
64 -- Populate Old Values.
65 OPEN cur_old_ref_values;
66 FETCH cur_old_ref_values INTO old_references;
67 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
68 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
69 CLOSE cur_old_ref_values;
70 App_Exception.Raise_Exception;
71 RETURN;
72 END IF;
73 CLOSE cur_old_ref_values;
74 -- Populate New Values.
75 new_references.person_id := x_person_id;
76 new_references.course_cd := x_course_cd;
77 new_references.unit_set_cd := x_unit_set_cd;
78 new_references.us_version_number := x_us_version_number;
79 new_references.sequence_number := x_sequence_number;
80 new_references.selection_dt := x_selection_dt;
81 new_references.student_confirmed_ind := x_student_confirmed_ind;
82 new_references.end_dt := x_end_dt;
83 new_references.parent_unit_set_cd := x_parent_unit_set_cd;
84 new_references.parent_sequence_number := x_parent_sequence_number;
85 new_references.primary_set_ind := x_primary_set_ind;
86 new_references.voluntary_end_ind := x_voluntary_end_ind;
87 new_references.authorised_person_id := x_authorised_person_id;
88 new_references.authorised_on := x_authorised_on;
89 new_references.override_title := x_override_title;
90 new_references.rqrmnts_complete_ind := x_rqrmnts_complete_ind;
91 new_references.rqrmnts_complete_dt := x_rqrmnts_complete_dt;
92 new_references.s_completed_source_type := x_s_completed_source_type;
93 new_references.catalog_cal_type := x_catalog_cal_type;
94 new_references.catalog_seq_num := x_catalog_seq_num;
95 new_references.attribute_category:= x_attribute_category;
96 new_references.attribute1:= x_attribute1;
97 new_references.attribute2:= x_attribute2;
98 new_references.attribute3:= x_attribute3;
99 new_references.attribute4:= x_attribute4;
100 new_references.attribute5:= x_attribute5;
101 new_references.attribute6:= x_attribute6;
102 new_references.attribute7:= x_attribute7;
103 new_references.attribute8:= x_attribute8;
104 new_references.attribute9:= x_attribute9;
105 new_references.attribute10:= x_attribute10;
106 new_references.attribute11:= x_attribute11;
107 new_references.attribute12:= x_attribute12;
108 new_references.attribute13:= x_attribute13;
109 new_references.attribute14:= x_attribute14;
110 new_references.attribute15:= x_attribute15;
111 new_references.attribute16:= x_attribute16;
112 new_references.attribute17:= x_attribute17;
113 new_references.attribute18:= x_attribute18;
114 new_references.attribute19:= x_attribute19;
115 new_references.attribute20:= x_attribute20;
116 IF (p_action = 'UPDATE') THEN
117 new_references.creation_date := old_references.creation_date;
118 new_references.created_by := old_references.created_by;
119 ELSE
120 new_references.creation_date := x_creation_date;
121 new_references.created_by := x_created_by;
122 END IF;
123 new_references.last_update_date := x_last_update_date;
124 new_references.last_updated_by := x_last_updated_by;
125 new_references.last_update_login := x_last_update_login;
126 END Set_Column_Values;
127 -- Trigger description :-
128 -- "OSS_TST".trg_susa_br_iud
129 -- BEFORE INSERT OR DELETE OR UPDATE
130 -- ON IGS_AS_SU_SETATMPT
131 -- FOR EACH ROW
132 PROCEDURE BeforeRowInsertUpdateDelete1(
133 p_inserting IN BOOLEAN ,
134 p_updating IN BOOLEAN ,
135 p_deleting IN BOOLEAN
136 ) AS
137 -- check whether the unit set is a valid one for the prorgam offering option
138 CURSOR c_us_valid IS
139 SELECT 'x'
140 FROM IGS_PS_OFR_OPT_UNIT_SET_V usoo,
141 IGS_EN_STDNT_PS_ATT spa
142 WHERE spa.person_id = new_references.person_id
143 AND spa.course_cd = new_references.course_cd
144 AND usoo.unit_set_cd = new_references.unit_set_cd
145 AND usoo.us_version_number = new_references.us_version_number
146 AND usoo.coo_id = spa.coo_id;
147
148 l_dummy VARCHAR2(1);
149
150 v_message_name VARCHAR2(30);
151 BEGIN
152 -- verify if this unit set is a valid unit set for the program offering option
153 IF p_updating THEN
154 OPEN c_us_valid;
155 FETCH c_us_valid INTO l_dummy;
156 IF c_us_valid%NOTFOUND THEN
157 CLOSE c_us_valid;
158 Fnd_Message.Set_Name('IGS', 'IGS_EN_UNIT_SETNOT_PERMITTED');
159 IGS_GE_MSG_STACK.ADD;
160 App_Exception.Raise_Exception;
161 END IF;
162 CLOSE c_us_valid;
163 END IF;
164
165 -- If trigger has not been disabled, perform required processing
166 -- Warning: disabling has been done for IGS_EN_GEN_013.ENRP_UPD_SUSA_END_DT and
167 -- IGS_EN_GEN_013.ENRP_UPD_SUSA_SCI processing which occurs within the after statment
168 -- trigger. If wishing to disable the triggers, use a different table name as
169 -- an identifier eg. 'STUDENT_UNIT_SET_ATTEMPT2'
170 -- IGS_GE_NOTE: Any alterations to susa triggers should be applied to module
171 -- enrp_val_susa.
172 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_AS_SU_SETATMPT') THEN
173 IF p_inserting THEN
174 -- Validate the the IGS_PS_UNIT set is able to be created.
175 -- against the student IGS_PS_COURSE attempt.
176 IF IGS_EN_VAL_SUSA.enrp_val_susa_sca(
177 new_references.person_id,
178 new_references.course_cd,
179 v_message_name ) = FALSE THEN
180 Fnd_Message.Set_Name('IGS', v_message_name);
181 IGS_GE_MSG_STACK.ADD;
182 App_Exception.Raise_Exception;
183 END IF;
184 -- Validate the the IGS_PS_UNIT set is able to be created.
185 -- The student cannot have completed it previously,
186 -- no encumbrances must exist and it must be applicable
187 -- to the IGS_PS_COURSE offering.
188 -- p_legacy value passed as 'N' as function is called in non-legacy mode
189 IF IGS_EN_VAL_SUSA.enrp_val_susa_ins(
190 new_references.person_id,
191 new_references.course_cd,
192 new_references.unit_set_cd,
193 new_references.sequence_number,
194 new_references.us_version_number,
195 v_message_name,
196 'N') = FALSE THEN
197 Fnd_Message.Set_Name('IGS', v_message_name);
198 IGS_GE_MSG_STACK.ADD;
199 App_Exception.Raise_Exception;
200 END IF;
201 END IF;
202 -- Validate that the authorisation fields can only be set when end date is set
203 -- or the IGS_PS_UNIT set cd requires authorisation (IGS_EN_UNIT_SET.authorisation_ind =
204 -- 'Y')
205 IF p_inserting OR
206 (p_updating AND
207 ((NVL(new_references.authorised_person_id, 0) <>
208 NVL(old_references.authorised_person_id, 0)) OR
209 (NVL(new_references.authorised_on,IGS_GE_DATE.IGSDATE('1900/01/01'))
210 <> NVL(old_references.authorised_on, IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
211
212 -- p_legacy value passed as 'N' as function is called in non-legacy mode
213 IF IGS_EN_VAL_SUSA.enrp_val_susa_auth(
214 new_references.unit_set_cd,
215 new_references.us_version_number,
216 new_references.end_dt,
217 new_references.authorised_person_id,
218 new_references.authorised_on,
219 v_message_name,
220 'N') = FALSE THEN
221 Fnd_Message.Set_Name('IGS', v_message_name);
222 IGS_GE_MSG_STACK.ADD;
223 App_Exception.Raise_Exception;
224 END IF;
225 END IF;
226 IF p_inserting OR
227 (p_updating AND
228 ((NVL(new_references.authorised_person_id, 0) <>
229 NVL(old_references.authorised_person_id, 0)) OR
230 (new_references.student_confirmed_ind <> old_references.student_confirmed_ind) OR
231 (NVL(new_references.authorised_on,IGS_GE_DATE.IGSDATE('1900/01/01'))
232 <> NVL(old_references.authorised_on, IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
233 -- Validate that the authorisation fields must be set when
234 -- the IGS_PS_UNIT set cd requires authorisation (IGS_EN_UNIT_SET.authorisation_ind = 'Y')
235 -- Check required only when the IGS_PS_UNIT set is confirmed.
236 IF (new_references.student_confirmed_ind = 'Y') THEN
237 IF IGS_EN_VAL_SUSA.enrp_val_susa_us_ath(
238 new_references.unit_set_cd,
239 new_references.us_version_number,
240 new_references.authorised_person_id,
241 new_references.authorised_on,
242 v_message_name ) = FALSE THEN
243 Fnd_Message.Set_Name('IGS', v_message_name);
244 IGS_GE_MSG_STACK.ADD;
245 App_Exception.Raise_Exception;
246 END IF;
247 END IF;
248 END IF;
249 -- Validate that the completion fields can only be set when IGS_PS_UNIT set is
250 -- confirmed
251 IF p_inserting OR
252 (p_updating AND
253 ((NVL(new_references.rqrmnts_complete_ind, 'x')
254 <> NVL(old_references.rqrmnts_complete_ind, 'x')) OR
255 (NVL(new_references.rqrmnts_complete_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
256 <> NVL(old_references.rqrmnts_complete_dt,
257 IGS_GE_DATE.IGSDATE('1900/01/01')))))THEN
258
259 -- p_legacy value passed as 'N' as function is called in non-legacy mode
260 IF IGS_EN_VAL_SUSA.enrp_val_susa_cmplt(
261 new_references.rqrmnts_complete_dt,
262 new_references.rqrmnts_complete_ind,
263 new_references.student_confirmed_ind,
264 v_message_name,
265 'N') = FALSE THEN
266 Fnd_Message.Set_Name('IGS', v_message_name);
267 IGS_GE_MSG_STACK.ADD;
268 App_Exception.Raise_Exception;
269 END IF;
270 END IF;
271 -- Validate that the system competed source type field can only be
272 -- set when completion fields are set.
273 IF p_inserting OR
274 (p_updating AND
275 ((NVL(new_references.rqrmnts_complete_ind, 'x')
276 <> NVL(old_references.rqrmnts_complete_ind, 'x')) OR
277 (NVL(new_references.s_completed_source_type, 'x')
278 <> NVL(old_references.s_completed_source_type, 'x')) OR
279 (NVL(new_references.rqrmnts_complete_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
280 <> NVL(old_references.rqrmnts_complete_dt,
281 IGS_GE_DATE.IGSDATE('1900/01/01')))))THEN
282 IF IGS_EN_VAL_SUSA.enrp_val_susa_scst(
283 new_references.rqrmnts_complete_dt,
284 new_references.rqrmnts_complete_ind,
285 new_references.s_completed_source_type,
286 v_message_name ) = FALSE THEN
287 Fnd_Message.Set_Name('IGS', v_message_name);
288 IGS_GE_MSG_STACK.ADD;
289 App_Exception.Raise_Exception;
290 END IF;
291 END IF;
292 -- The peice of code was existing here to Validate the date fields. Which got removed in UK Enhancement Build.
293 -- Enh Bug#2580731. The code was raising exception if the Selection_dt, end_dt or rqrmnts_complete_dt is more than sysdate.
294
295 -- Validate that the selection date can only be set/unset when IGS_PS_UNIT set is
296 -- confirmed/unconfirmed
297 IF p_inserting OR
298 (p_updating AND
299 ((new_references.student_confirmed_ind <> old_references.student_confirmed_ind) OR
300 (NVL(new_references.selection_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
301 <> NVL(old_references.selection_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
302
303 -- p_legacy value passed as 'N' as function is called in non-legacy mode
304 IF IGS_EN_VAL_SUSA.enrp_val_susa_sci_sd(
305 new_references.student_confirmed_ind,
306 new_references.selection_dt,
307 v_message_name,
308 'N') = FALSE THEN
309 Fnd_Message.Set_Name('IGS', v_message_name);
310 IGS_GE_MSG_STACK.ADD;
311 App_Exception.Raise_Exception;
312 END IF;
313 END IF;
314 -- Validate that the voluntary_end_ind can only be set when the end date is
315 -- set.
316 IF p_inserting OR
317 (p_updating AND
318 ((new_references.voluntary_end_ind <> old_references.voluntary_end_ind) OR
319 (NVL(new_references.end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
320 <> NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
321 IF IGS_EN_VAL_SUSA.enrp_val_susa_end_vi(
322 new_references.voluntary_end_ind,
323 new_references.end_dt,
324 v_message_name ) = FALSE THEN
325 Fnd_Message.Set_Name('IGS', v_message_name);
326 IGS_GE_MSG_STACK.ADD;
327 App_Exception.Raise_Exception;
328 END IF;
329 END IF;
330 -- Validate that the IGS_PS_UNIT set version number cannot be updated.
331 IF (p_updating AND
332 (new_references.us_version_number <> old_references.us_version_number)) THEN
333 Fnd_Message.Set_Name('IGS', 'IGS_EN_UNIT_SET_VERNUM_NOTUPD');
334 IGS_GE_MSG_STACK.ADD;
335 App_Exception.Raise_Exception;
336 END IF;
337 IF p_deleting THEN
338 -- Validate that the records can be deleted.
339 IF IGS_EN_VAL_SUSA.enrp_val_susa_del(
340 old_references.person_id,
341 old_references.course_cd,
342 old_references.unit_set_cd,
343 old_references.sequence_number,
344 old_references.us_version_number,
345 old_references.end_dt,
346 old_references.rqrmnts_complete_ind,
347 'Y', -- indicating this is called from db trigger.
348 v_message_name ) = FALSE THEN
349 Fnd_Message.Set_Name('IGS', v_message_name);
350 IGS_GE_MSG_STACK.ADD;
351 App_Exception.Raise_Exception;
352 END IF;
353 END IF;
354 END IF;
355 END BeforeRowInsertUpdateDelete1;
356 PROCEDURE RowValMutation(
357 p_inserting IN BOOLEAN ,
358 p_updating IN BOOLEAN ,
359 p_deleting IN BOOLEAN ,
360 p_parent_unit_set_cd IN VARCHAR2 ,
361 p_end_dt IN DATE ,
362 p_student_confirmed_ind IN VARCHAR2 ,
363 p_primary_set_ind IN VARCHAR2
364 ) AS
365 v_insert BOOLEAN;
366 v_update BOOLEAN;
367 v_delete BOOLEAN;
368 cst_error CONSTANT CHAR := 'E';
369 v_message_name VARCHAR2(30);
370 BEGIN
371 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_AS_SU_SETATMPT') THEN
372 IF p_inserting THEN
373 -- Validate the the unit set is able to be created
374 -- with the unit set status being valid and the
375 -- expiry date not set. If set then person must have
376 -- previously selected it.
377 IF IGS_EN_VAL_SUSA.enrp_val_susa_us_act(
378 new_references.person_id,
379 new_references.course_cd,
380 new_references.unit_set_cd,
381 new_references.sequence_number,
382 new_references.us_version_number,
383 v_message_name) = FALSE THEN
384 Fnd_Message.Set_Name('IGS', v_message_name);
385 IGS_GE_MSG_STACK.ADD;
386 App_Exception.Raise_Exception;
387 END IF;
388 END IF;
389 IF (p_inserting OR p_updating) AND
390 p_parent_unit_set_cd IS NOT NULL THEN
391 -- Validate if the unit set is to be defined as a subordinate or if
392 -- relationship specified, that it is valid within the course offering.
393 -- p_legacy value passed as 'N' as function is called in non-legacy mode
394 IF IGS_EN_VAL_SUSA.enrp_val_susa_cousr(
395 new_references.person_id,
396 new_references.course_cd,
397 new_references.unit_set_cd,
398 new_references.us_version_number,
399 new_references.parent_unit_set_cd,
400 new_references.parent_sequence_number,
401 cst_error,
402 v_message_name,
403 'N') = FALSE THEN
404 Fnd_Message.Set_Name('IGS', v_message_name);
405 IGS_GE_MSG_STACK.ADD;
406 App_Exception.Raise_Exception;
407 END IF;
408 -- Validate if the parent unit set has a null end date, unit set is
409 -- not being linked to itself (directly or indirectly). Cannot be
410 -- confirmed if parent is unconfirmed.
411 -- p_legacy value passed as 'N' as function is called in non-legacy mode
412 IF IGS_EN_VAL_SUSA.enrp_val_susa_parent(
413 new_references.person_id,
414 new_references.course_cd,
415 new_references.unit_set_cd,
416 new_references.sequence_number,
417 new_references.parent_unit_set_cd,
418 new_references.parent_sequence_number,
419 new_references.student_confirmed_ind,
420 v_message_name,
421 'N') = FALSE THEN
422 Fnd_Message.Set_Name('IGS', v_message_name);
423 IGS_GE_MSG_STACK.ADD;
424 App_Exception.Raise_Exception;
425 END IF;
426 END IF;
427 IF (p_inserting OR p_updating) AND
428 p_end_dt IS NOT NULL THEN
429 -- Validate the end date, check if the authorisation details
430 -- need to be set or if more than one open end dated instance
431 -- of the unit set exists. Also cannot be cleared if parent ended.
432 -- If part of the admissions offer, authorisation required to end
433 -- the unit set.
434 -- p_legacy value passed as 'N' as function is called in non-legacy mode
435 IF IGS_EN_VAL_SUSA.enrp_val_susa_end_dt(
436 new_references.person_id,
437 new_references.course_cd,
438 new_references.unit_set_cd,
439 new_references.sequence_number,
440 new_references.us_version_number,
441 new_references.end_dt,
442 new_references.authorised_person_id,
443 new_references.authorised_on,
444 new_references.parent_unit_set_cd,
445 new_references.parent_sequence_number,
446 cst_error,
447 v_message_name,
448 'N') = FALSE THEN
449 -- Check if warning message returned.
450 IF v_message_name <> 'IGS_EN_UNITSET_REQ_AUTHORISAT' THEN
451 Fnd_Message.Set_Name('IGS', v_message_name);
452 IGS_GE_MSG_STACK.ADD;
453 App_Exception.Raise_Exception;
454 END IF;
455 END IF;
456 -- If updating and the end date has been set, cascade the end date
457 -- through to any descendant unit sets (Inserted records cannot have
458 -- children at that point).
459 IF p_updating AND
460 new_references.end_dt IS NOT NULL THEN
461 IF IGS_EN_GEN_013.ENRP_UPD_SUSA_END_DT(
462 new_references.person_id,
463 new_references.course_cd,
464 new_references.unit_set_cd,
465 new_references.sequence_number,
466 new_references.end_dt,
467 new_references.voluntary_end_ind,
468 new_references.authorised_person_id,
469 new_references.authorised_on,
470 v_message_name) = FALSE THEN
471 Fnd_Message.Set_Name('IGS', v_message_name);
472 IGS_GE_MSG_STACK.ADD;
473 App_Exception.Raise_Exception;
474 END IF;
475 END IF;
476 END IF;
477 IF (p_inserting OR p_updating) AND
478 p_student_confirmed_ind IS NOT NULL THEN
479 -- Validate that the unit set is not confirmed when the student course
480 -- attempt is unconfirmed.
481 -- Also check that not unset one end date or complete date set. Cannot be
482 -- confirmed and linked to a parent that is unconfirmed. Cannot be
483 -- confirmed if encumbrances exist.
484 -- p_legacy value passed as 'N' as function is called in non-legacy mode
485 IF IGS_EN_VAL_SUSA.enrp_val_susa_sci(
486 new_references.person_id,
487 new_references.course_cd,
488 new_references.unit_set_cd,
489 new_references.sequence_number,
490 new_references.us_version_number,
491 new_references.parent_unit_set_cd,
492 new_references.parent_sequence_number,
493 new_references.student_confirmed_ind,
494 new_references.selection_dt,
495 new_references.end_dt,
496 new_references.rqrmnts_complete_ind,
497 v_message_name,
498 'N') = FALSE THEN
499 Fnd_Message.Set_Name('IGS', v_message_name);
500 IGS_GE_MSG_STACK.ADD;
501 App_Exception.Raise_Exception;
502 END IF;
503 -- If updating and the student confirmed indicator is being unset,
504 -- then unset any descendant unit sets. (Only concerned with update
505 -- as unit set cannot have descendant at the point of creation).
506 IF p_updating AND
507 new_references.student_confirmed_ind = 'N' THEN
508 IF IGS_EN_GEN_013.ENRP_UPD_SUSA_SCI(
509 new_references.person_id,
510 new_references.course_cd,
511 new_references.unit_set_cd,
512 new_references.sequence_number,
513 new_references.student_confirmed_ind,
514 v_message_name) = FALSE THEN
515 Fnd_Message.Set_Name('IGS', v_message_name);
516 IGS_GE_MSG_STACK.ADD;
517 App_Exception.Raise_Exception;
518 END IF;
519 END IF;
520 END IF;
521 IF (p_inserting OR p_updating) AND
522 p_primary_set_ind IS NOT NULL THEN
523 -- Validate the primary set indicator is only set for
524 -- non-administrative sets and that there does not already
525 -- exist a unit set that has a higher rank.
526 -- p_legacy value passed as 'N' as function is called in non-legacy mode
527 IF IGS_EN_VAL_SUSA.enrp_val_susa_prmry(
528 new_references.person_id,
529 new_references.course_cd,
530 new_references.unit_set_cd,
531 new_references.us_version_number,
532 new_references.primary_set_ind,
533 v_message_name,
534 'N') = FALSE THEN
535 Fnd_Message.Set_Name('IGS', v_message_name);
536 IGS_GE_MSG_STACK.ADD;
537 App_Exception.Raise_Exception;
538 END IF;
539 END IF;
540 END IF;
541 END RowValMutation;
542 -- Trigger description :-
543 -- "OSS_TST".trg_susa_ar_iu
544 -- AFTER INSERT OR UPDATE
545 -- ON IGS_AS_SU_SETATMPT
546 -- FOR EACH ROW
547 PROCEDURE AfterRowInsertUpdate2(
548 p_inserting IN BOOLEAN,
549 p_updating IN BOOLEAN,
550 p_deleting IN BOOLEAN
551 ) AS
552 ------------------------------------------------------------------------------
553 -- Change History:
554 -- Who When What
555 -- SVANUKUR 27-NOV-2003 added logic to create a TODO record of the TODO type FEE_RECALC
556 -- If a record is being inserted/updated/deleted
557 ------------------------------------------------------------------------------
558 v_tmp_end_dt DATE;
559 v_sequence_number NUMBER;
560 v_unit_set VARCHAR2(1);
561
562 --check if the unit set attempt being inserted/update/deleted is
563 --of type 'PRE-ENROLL'
564 CURSOR c_unit_set(cp_unit_set_cd IGS_EN_UNIT_SET.unit_set_cd%TYPE) IS
565 SELECT 'X'
566 FROM IGS_EN_UNIT_SET us,
567 IGS_EN_UNIT_SET_CAT usc
568 WHERE us.unit_set_cd = cp_unit_set_cd
569 AND us.unit_set_cat = usc.unit_set_cat
570 AND usc.unit_set_cat = 'PRE-ENROLL';
571
572
573
574 BEGIN
575 -- If trigger has not been disabled, perform required processing
576 -- Warning: disabling has been done for IGS_EN_GEN_013.ENRP_UPD_SUSA_END_DT and
577 -- IGS_EN_GEN_013.ENRP_UPD_SUSA_SCI processing which occurs within the after statment
578 -- trigger. If wishing to disable the triggers, use a different table name as
579 -- an identifier eg. 'STUDENT_UNIT_SET_ATTEMPT2'
580 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_AS_SU_SETATMPT') THEN
581 -- Validate the the IGS_PS_UNIT set is able to be created
582 -- with the IGS_PS_UNIT set status being valid and the
583 -- expiry date not set. If set then IGS_PE_PERSON must have
584 -- previously selected it.
585 IF p_inserting THEN
586 -- Validate the the IGS_PS_UNIT set is able to be created
587 -- with the IGS_PS_UNIT set status being valid and the
588 -- expiry date not set. If set then IGS_PE_PERSON must have
589 -- previously selected it. (IGS_EN_VAL_SUSA.enrp_val_susa_us_act)
590 -- Cannot call modules because trigger will be mutating.
591 -- Save the rowid of the current row.
592 RowValMutation(
593 p_inserting ,
594 p_updating ,
595 p_deleting ,
596 NULL ,
597 NULL ,
598 'N',
599 'N'
600 );
601 END IF;
602 -- Validate if the IGS_PS_UNIT set parent relationship.
603 IF p_inserting OR
604 (p_updating AND
605 ((NVL(new_references.parent_unit_set_cd, 'NULL')
606 <> NVL(old_references.parent_unit_set_cd, 'NULL')) OR
607 (NVL(new_references.parent_sequence_number, 0)
608 <> NVL(old_references.parent_sequence_number, 0)))) THEN
609 -- Validate if the IGS_PS_UNIT set is to be defined as a subordinate or if
610 -- relationship specified, that it is valid within the IGS_PS_COURSE offering
611 -- (IGS_EN_VAL_SUSA.enrp_val_susa_cousr).
612 -- Validate if the parent IGS_PS_UNIT set has a null end date, IGS_PS_UNIT set is
613 -- not being linked to itself (directly or indirectly). Cannot be
614 -- confirmed if parent is unconfirmed (IGS_EN_VAL_SUSA.enrp_val_susa_parent).
615 -- Cannot call modules because trigger will be mutating.
616 -- Save the rowid of the current row setting the parent IGS_PS_UNIT set field to
617 -- indicate to perform parent IGS_PS_UNIT set code validation
618 RowValMutation(
619 p_inserting ,
620 p_updating ,
621 p_deleting ,
622 new_references.parent_unit_set_cd ,
623 NULL ,
624 'N',
625 'N'
626 );
627 END IF;
628 -- Validate the end date, check if the authorisation details
629 -- need to be set or if more than one open end dated instance
630 -- of the IGS_PS_UNIT set exists. Also cannot be cleared if parent ended.
631 -- If part of the admissions offer, authorisation required to end
632 -- the IGS_PS_UNIT set.
633 IF p_inserting OR
634 (NVL(new_references.end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
635 <> NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
636 -- Store away the rowid as the validation will cause a mutating trigger.
637 -- Set the end date field to indicate validation required for end date.
638 -- If p_inserting, validation is still to occur. If end_is null then set it
639 -- such that validation will happen in the after statement trigger.
640 IF p_inserting AND
641 (NVL(new_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
642 = IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
643 v_tmp_end_dt := IGS_GE_DATE.IGSDATE('1900/01/01');
644 ELSE
645 v_tmp_end_dt := new_references.end_dt;
646 END IF;
647 RowValMutation(
648 p_inserting ,
649 p_updating ,
650 p_deleting ,
651 NULL ,
652 v_tmp_end_dt,
653 'N',
654 'N'
655 );
656 END IF;
657 -- Validate if the IGS_PS_UNIT set parent relationship.
658 IF (p_inserting AND new_references.student_confirmed_ind = 'Y') OR
659 (p_updating AND
660 (new_references.student_confirmed_ind <> old_references.student_confirmed_ind)) THEN
661 -- Validate that the IGS_PS_UNIT set is not confirmed when the student IGS_PS_COURSE
662 -- attempt is unconfirmed.
663 -- Also check that not unset one end date or complete date set. Cannot be
664 -- confirmed and linked to a parent that is unconfirmed. Cannot be
665 -- confirmed if encumbrances exist.
666 -- Cannot call modules because trigger will be mutating.
667 -- Save the rowid of the current row setting the student confirmed field to
668 -- indicate to perform student_confirmed_ind validation
669 RowValMutation(
670 p_inserting ,
671 p_updating ,
672 p_deleting ,
673 NULL ,
674 NULL,
675 new_references.student_confirmed_ind,
676 'N'
677 );
678 END IF;
679 -- Validate if the primary set indicator.
680 IF p_inserting OR
681 (p_updating AND
682 (new_references.primary_set_ind <> old_references.primary_set_ind)) THEN
683 -- Validate the primary set indicator is only set for
684 -- non-administrative sets and that there does not already
685 -- exist a IGS_PS_UNIT set that has a higher rank.
686 -- Cannot call modules because trigger will be mutating.
687 -- Save the rowid of the current row setting the primary_set field to
688 -- indicate to perform primary_set_ind validation
689 RowValMutation(
690 p_inserting ,
691 p_updating ,
692 p_deleting ,
693 NULL ,
694 NULL,
695 'N',
696 new_references.primary_set_ind
697 );
698 END IF;
699 END IF;
700 IF p_inserting OR p_updating THEN
701 v_unit_set := NULL;
702 OPEN c_unit_set(new_references.unit_set_cd);
703 FETCH c_unit_set INTO v_unit_set;
704 CLOSE c_unit_set;
705 IF v_unit_set IS NOT NULL THEN
706 v_sequence_number := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(
707 new_references.person_id,
708 'FEE_RECALC',
709 SYSDATE,
710 'Y');
711 END IF;
712 ELSE
713 v_unit_set := NULL;
714 OPEN c_unit_set(old_references.unit_set_cd);
715 FETCH c_unit_set INTO v_unit_set;
716 CLOSE c_unit_set;
717 IF v_unit_set IS NOT NULL THEN
718 v_sequence_number := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(
719 old_references.person_id,
720 'FEE_RECALC',
721 SYSDATE,
722 'Y');
723 END IF;
724 END IF;
725 END AfterRowInsertUpdate2;
726 -- Trigger description :-
727 -- "OSS_TST".trg_susa_ar_ud_hist
728 -- AFTER DELETE OR UPDATE
729 -- ON IGS_AS_SU_SETATMPT
730 -- FOR EACH ROW
731 PROCEDURE AfterRowUpdateDelete3(
732 p_inserting IN BOOLEAN,
733 p_updating IN BOOLEAN,
734 p_deleting IN BOOLEAN
735 ) AS
736 v_message_name VARCHAR2(30);
737 BEGIN
738 IF p_updating THEN
739 -- Create IGS_AS_SU_SETATMPT history record.
740 IGS_EN_GEN_010.ENRP_INS_SUSA_HIST (
741 new_references.person_id,
742 new_references.course_cd,
743 new_references.unit_set_cd,
744 new_references.sequence_number,
745 new_references.us_version_number,
746 old_references.us_version_number ,
747 new_references.selection_dt,
748 old_references.selection_dt,
749 new_references.student_confirmed_ind,
750 old_references.student_confirmed_ind,
751 new_references.end_dt,
752 old_references.end_dt,
753 new_references.parent_unit_set_cd,
754 old_references.parent_unit_set_cd,
755 new_references.parent_sequence_number,
756 old_references.parent_sequence_number,
757 new_references.primary_set_ind,
758 old_references.primary_set_ind,
759 new_references.voluntary_end_ind,
760 old_references.voluntary_end_ind,
761 new_references.authorised_person_id,
762 old_references.authorised_person_id,
763 new_references.authorised_on,
764 old_references.authorised_on,
765 new_references.override_title,
766 old_references.override_title,
767 new_references.rqrmnts_complete_ind,
768 old_references.rqrmnts_complete_ind,
769 new_references.rqrmnts_complete_dt,
770 old_references.rqrmnts_complete_dt,
771 new_references.s_completed_source_type,
772 old_references.s_completed_source_type,
773 new_references.catalog_cal_type ,
774 old_references.catalog_cal_type ,
775 new_references.catalog_seq_num ,
776 old_references.catalog_seq_num ,
777 new_references.last_updated_by,
778 old_references.last_updated_by,
779 new_references.last_update_date,
780 old_references.last_update_date);
781 END IF;
782 IF p_deleting THEN
783 -- Delete IGS_AS_SU_SETATMPT history records.
784 IF IGS_EN_GEN_001.ENRP_DEL_SUSA_HIST (
785 old_references.person_id,
786 old_references.course_cd,
787 old_references.unit_set_cd,
788 old_references.sequence_number,
789 v_message_name) = FALSE THEN
790 Fnd_Message.Set_Name('IGS', v_message_name);
791 IGS_GE_MSG_STACK.ADD;
792 App_Exception.Raise_Exception;
793 END IF;
794 END IF;
795 END AfterRowUpdateDelete3;
796 -- Trigger description :-
797 -- "OSS_TST".trg_susa_as_iu
798 -- AFTER INSERT OR UPDATE
799 -- ON IGS_AS_SU_SETATMPT
800
801 PROCEDURE Check_Parent_Existance AS
802
803 -- check if the parent unit set is a valid one
804 CURSOR c_parent_rel_exists IS
805
806 SELECT 'x'
807 FROM IGS_PS_OF_UNT_SET_RL cousr,
808 IGS_AS_SU_SETATMPT susa,
809 IGS_EN_STDNT_PS_ATT spa
810 WHERE spa.person_id = susa.person_id AND
811 spa.course_cd = susa.course_cd AND
812 susa.person_id = new_references.person_id AND
813 susa.course_cd = new_references.course_cd AND
814 susa.unit_set_cd = new_references.parent_unit_set_cd AND
815 susa.sequence_number = new_references.parent_sequence_number AND
816 cousr.course_cd = spa.course_cd AND
817 cousr.crv_version_number = spa.version_number AND
818 cousr.cal_type = spa.cal_type AND
819 cousr.sub_unit_set_cd = new_references.unit_set_cd AND
820 cousr.sub_us_version_number = new_references.us_version_number AND
821 cousr.sup_unit_set_cd = susa.unit_set_cd AND
822 cousr.sup_us_version_number = susa.us_version_number;
823 l_dummy VARCHAR2(1);
824
825 BEGIN
826
827 IF (new_references.parent_unit_set_cd IS NOT NULL AND
828 new_references.parent_sequence_number IS NOT NULL) THEN
829 OPEN c_parent_rel_exists;
830 FETCH c_parent_rel_exists INTO l_dummy;
831 IF c_parent_rel_exists%NOTFOUND THEN
832 CLOSE c_parent_rel_exists;
833 Fnd_Message.Set_Name ('IGS', 'IGS_EN_UNIT_SET_RELATIONSHIP');
834 IGS_GE_MSG_STACK.ADD;
835 App_Exception.Raise_Exception;
836 END IF;
837 CLOSE c_parent_rel_exists;
838 END IF;
839
840
841 IF (((old_references.person_id = new_references.person_id) AND
842 (old_references.course_cd = new_references.course_cd)) OR
843 (new_references.person_id IS NULL) OR
844 (new_references.course_cd IS NULL)) THEN
845 NULL;
846 ELSE
847 IF NOT IGS_EN_STDNT_PS_ATT_PKG.Get_PK_For_Validation (
848 new_references.person_id,
849 new_references.course_cd
850 ) THEN
851 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
852 IGS_GE_MSG_STACK.ADD;
853 App_Exception.Raise_Exception;
854 END IF;
855 END IF;
856 IF (((old_references.person_id = new_references.person_id) AND
857 (old_references.course_cd = new_references.course_cd) AND
858 (old_references.parent_unit_set_cd = new_references.parent_unit_set_cd) AND
859 (old_references.parent_sequence_number = new_references.parent_sequence_number)) OR
860 (new_references.person_id IS NULL) OR
861 (new_references.course_cd IS NULL) OR
862 (new_references.parent_unit_set_cd IS NULL) OR
863 (new_references.parent_sequence_number IS NULL)) THEN
864 NULL;
865 ELSE
866 IF NOT IGS_AS_SU_SETATMPT_PKG.Get_PK_For_Validation (
867 new_references.person_id,
868 new_references.course_cd,
869 new_references.parent_unit_set_cd,
870 new_references.parent_sequence_number
871 )THEN
872 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
873 IGS_GE_MSG_STACK.ADD;
874 App_Exception.Raise_Exception;
875 END IF;
876 END IF;
877 IF (((old_references.unit_set_cd = new_references.unit_set_cd) AND
878 (old_references.us_version_number = new_references.us_version_number)) OR
879 (new_references.unit_set_cd IS NULL) OR
880 (new_references.us_version_number IS NULL)) THEN
881 NULL;
882 ELSE
883 IF NOT IGS_EN_UNIT_SET_PKG.Get_PK_For_Validation (
884 new_references.unit_set_cd,
885 new_references.us_version_number
886 )THEN
887 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
888 IGS_GE_MSG_STACK.ADD;
889 App_Exception.Raise_Exception;
890 END IF;
891 END IF;
892 END Check_Parent_Existance;
893
894 PROCEDURE Check_Constraints (
895 Column_Name IN VARCHAR2 ,
896 Column_Value IN VARCHAR2
897 )
898 AS
899 BEGIN
900
901 IF column_name IS NULL THEN
902 NULL;
903 ELSIF UPPER(Column_name) = 'AUTHORISED_PERSON_ID' THEN
904 new_references.authorised_person_id:= column_value;
905 ELSIF UPPER(Column_name) = 'COURSE_CD' THEN
906 new_references.course_cd:= column_value;
907 ELSIF UPPER(Column_name) = 'OVERRIDE_TITLE' THEN
908 new_references.override_title:= column_value;
909 ELSIF UPPER(Column_name) = 'PARENT_SEQUENCE_NUMBER' THEN
910 new_references.parent_sequence_number:= igs_ge_number.to_num(column_value);
911 ELSIF UPPER(Column_name) = 'PARENT_UNIT_SET_CD' THEN
912 new_references.parent_unit_set_cd:= column_value;
913 ELSIF UPPER(Column_name) = 'PRIMARY_SET_IND' THEN
914 new_references.primary_set_ind:= column_value;
915 ELSIF UPPER(Column_name) = 'RQRMNTS_COMPLETE_IND' THEN
916 new_references.rqrmnts_complete_ind:= column_value;
917 ELSIF UPPER(Column_name) = 'S_COMPLETED_SOURCE_TYPE' THEN
918 new_references.s_completed_source_type:= column_value;
919 ELSIF UPPER(Column_name) = 'SEQUENCE_NUMBER' THEN
920 new_references.sequence_number:= column_value;
921 ELSIF UPPER(Column_name) = 'STUDENT_CONFIRMED_IND' THEN
922 new_references.student_confirmed_ind:= column_value;
923 ELSIF UPPER(Column_name) = 'UNIT_SET_CD' THEN
924 new_references.unit_set_cd:= column_value;
925 ELSIF UPPER(Column_name) = 'VOLUNTARY_END_IND' THEN
926 new_references.voluntary_end_ind:= column_value;
927 END IF;
928
929 IF UPPER(column_name) = 'AUTHORISED_PERSON_ID' OR
930 column_name IS NULL THEN
931 IF (new_references.authorised_person_id < 0 AND new_references.authorised_person_id > 9999999999) THEN
932 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
933 IGS_GE_MSG_STACK.ADD;
934 App_Exception.Raise_Exception;
935 END IF;
936 END IF;
937
938 IF UPPER(column_name) = 'COURSE_CD' OR
939 column_name IS NULL THEN
940 IF new_references.course_cd <> UPPER(new_references.course_cd) THEN
941 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
942 IGS_GE_MSG_STACK.ADD;
943 App_Exception.Raise_Exception;
944 END IF;
945 END IF;
946
947 IF UPPER(column_name) = 'PARENT_SEQUENCE_NUMBER' OR
948 column_name IS NULL THEN
949 IF new_references.parent_sequence_number < 1 AND new_references.parent_sequence_number > 999999 THEN
950 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
951 IGS_GE_MSG_STACK.ADD;
952 App_Exception.Raise_Exception;
953 END IF;
954 END IF;
955 IF UPPER(column_name) = 'PARENT_UNIT_SET_CD' OR
956 column_name IS NULL THEN
957 IF new_references.parent_unit_set_cd <> UPPER(new_references.parent_unit_set_cd) THEN
958 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
959 IGS_GE_MSG_STACK.ADD;
960 App_Exception.Raise_Exception;
961 END IF;
962 END IF;
963 IF UPPER(column_name) = 'PRIMARY_SET_IND' OR
964 column_name IS NULL THEN
965 IF new_references.primary_set_ind <> UPPER(new_references.primary_set_ind) OR new_references.primary_set_ind NOT IN ( 'Y' , 'N' ) THEN
966 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
967 IGS_GE_MSG_STACK.ADD;
968 App_Exception.Raise_Exception;
969 END IF;
970 END IF;
971 IF UPPER(column_name) = 'RQRMNTS_COMPLETE_IND' OR
972 column_name IS NULL THEN
973 IF new_references.rqrmnts_complete_ind <> UPPER(new_references.rqrmnts_complete_ind) OR new_references.rqrmnts_complete_ind NOT IN ( 'Y' , 'N' ) THEN
974 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
975 IGS_GE_MSG_STACK.ADD;
976 App_Exception.Raise_Exception;
977 END IF;
978 END IF;
979 IF UPPER(column_name) = 'S_COMPLETED_SOURCE_TYPE' OR
980 column_name IS NULL THEN
981 IF new_references.s_completed_source_type <> UPPER(new_references.s_completed_source_type) OR (new_references.s_completed_source_type NOT IN ( 'SYSTEM' , 'MANUAL' )) THEN
982 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
983 IGS_GE_MSG_STACK.ADD;
984 App_Exception.Raise_Exception;
985 END IF;
986 END IF;
987 IF UPPER(column_name) = 'SEQUENCE_NUMBER' OR
988 column_name IS NULL THEN
989 IF new_references.sequence_number < 1 AND new_references.sequence_number > 999999 THEN
990 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
991 IGS_GE_MSG_STACK.ADD;
992 App_Exception.Raise_Exception;
993 END IF;
994 END IF;
995 IF UPPER(column_name) = 'STUDENT_CONFIRMED_IND' OR
996 column_name IS NULL THEN
997 IF new_references.student_confirmed_ind <> UPPER(new_references.student_confirmed_ind) OR new_references.student_confirmed_ind NOT IN ( 'Y' , 'N' ) THEN
998 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
999 IGS_GE_MSG_STACK.ADD;
1000 App_Exception.Raise_Exception;
1001 END IF;
1002 END IF;
1003 IF UPPER(column_name) = 'UNIT_SET_CD' OR
1004 column_name IS NULL THEN
1005 IF new_references.unit_set_cd <> UPPER(new_references.unit_set_cd) THEN
1006 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1007 IGS_GE_MSG_STACK.ADD;
1008 App_Exception.Raise_Exception;
1009 END IF;
1010 END IF;
1011 IF UPPER(column_name) = 'VOLUNTARY_END_IND' OR
1012 column_name IS NULL THEN
1013 IF new_references.voluntary_end_ind <> UPPER(new_references.voluntary_end_ind) OR new_references.voluntary_end_ind NOT IN ( 'Y' , 'N' ) THEN
1014 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1015 IGS_GE_MSG_STACK.ADD;
1016 App_Exception.Raise_Exception;
1017 END IF;
1018 END IF;
1019 END Check_Constraints;
1020
1021
1022 PROCEDURE Check_Child_Existance AS
1023 BEGIN
1024 IGS_AS_SU_SETATMPT_PKG.GET_FK_IGS_AS_SU_SETATMPT (
1025 OLD_references.person_id,
1026 OLD_references.course_cd,
1027 OLD_references.unit_set_cd,
1028 OLD_references.sequence_number
1029 );
1030 END Check_Child_Existance;
1031 FUNCTION Get_PK_For_Validation (
1032 x_person_id IN NUMBER,
1033 x_course_cd IN VARCHAR2,
1034 x_unit_set_cd IN VARCHAR2,
1035 x_sequence_number IN NUMBER
1036 ) RETURN BOOLEAN AS
1037 CURSOR cur_rowid IS
1038 SELECT ROWID
1039 FROM IGS_AS_SU_SETATMPT
1040 WHERE person_id = x_person_id
1041 AND course_cd = x_course_cd
1042 AND unit_set_cd = x_unit_set_cd
1043 AND sequence_number = x_sequence_number
1044 FOR UPDATE NOWAIT;
1045 lv_rowid cur_rowid%ROWTYPE;
1046 BEGIN
1047 OPEN cur_rowid;
1048 FETCH cur_rowid INTO lv_rowid;
1049 IF (cur_rowid%FOUND) THEN
1050 CLOSE cur_rowid;
1051 RETURN (TRUE);
1052 ELSE
1053 CLOSE cur_rowid;
1054 RETURN (FALSE);
1055 END IF;
1056 END Get_PK_For_Validation;
1057 PROCEDURE GET_FK_IGS_EN_STDNT_PS_ATT (
1058 x_person_id IN NUMBER,
1059 x_course_cd IN VARCHAR2
1060 ) AS
1061 CURSOR cur_rowid IS
1062 SELECT ROWID
1063 FROM IGS_AS_SU_SETATMPT
1064 WHERE person_id = x_person_id
1065 AND course_cd = x_course_cd ;
1066 lv_rowid cur_rowid%ROWTYPE;
1067 BEGIN
1068 OPEN cur_rowid;
1069 FETCH cur_rowid INTO lv_rowid;
1070 IF (cur_rowid%FOUND) THEN
1071 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUSA_SCA_FK');
1072 IGS_GE_MSG_STACK.ADD;
1073 CLOSE cur_rowid;
1074 App_Exception.Raise_Exception;
1075
1076 RETURN;
1077 END IF;
1078 CLOSE cur_rowid;
1079 END GET_FK_IGS_EN_STDNT_PS_ATT;
1080 PROCEDURE GET_FK_IGS_AS_SU_SETATMPT (
1081 x_person_id IN NUMBER,
1082 x_course_cd IN VARCHAR2,
1083 x_unit_set_cd IN VARCHAR2,
1084 x_sequence_number IN NUMBER
1085 ) AS
1086 CURSOR cur_rowid IS
1087 SELECT ROWID
1088 FROM IGS_AS_SU_SETATMPT
1089 WHERE person_id = x_person_id
1090 AND course_cd = x_course_cd
1091 AND parent_unit_set_cd = x_unit_set_cd
1092 AND parent_sequence_number = x_sequence_number ;
1093 lv_rowid cur_rowid%ROWTYPE;
1094 BEGIN
1095 OPEN cur_rowid;
1096 FETCH cur_rowid INTO lv_rowid;
1097 IF (cur_rowid%FOUND) THEN
1098 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUSA_SUSA_PRNT_FK');
1099 IGS_GE_MSG_STACK.ADD;
1100 CLOSE cur_rowid;
1101 App_Exception.Raise_Exception;
1102
1103 RETURN;
1104 END IF;
1105 CLOSE cur_rowid;
1106 END GET_FK_IGS_AS_SU_SETATMPT;
1107 PROCEDURE GET_FK_IGS_EN_UNIT_SET (
1108 x_unit_set_cd IN VARCHAR2,
1109 x_version_number IN NUMBER
1110 ) AS
1111 CURSOR cur_rowid IS
1112 SELECT ROWID
1113 FROM IGS_AS_SU_SETATMPT
1114 WHERE unit_set_cd = x_unit_set_cd
1115 AND us_version_number = x_version_number ;
1116 lv_rowid cur_rowid%ROWTYPE;
1117 BEGIN
1118 OPEN cur_rowid;
1119 FETCH cur_rowid INTO lv_rowid;
1120 IF (cur_rowid%FOUND) THEN
1121 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUSA_US_FK');
1122 IGS_GE_MSG_STACK.ADD;
1123 CLOSE cur_rowid;
1124 App_Exception.Raise_Exception;
1125
1126 RETURN;
1127 END IF;
1128 CLOSE cur_rowid;
1129 END GET_FK_IGS_EN_UNIT_SET;
1130
1131
1132
1133 PROCEDURE GET_FK_IGS_CA_INST (
1134 X_CATALOG_CAL_TYPE IN VARCHAR2,
1135 X_CATALOG_SEQ_NUM IN NUMBER
1136 ) AS
1137 CURSOR cur_rowid IS
1138 SELECT ROWID
1139 FROM IGS_AS_SU_SETATMPT
1140 WHERE CATALOG_CAL_TYPE = X_CATALOG_CAL_TYPE
1141 AND CATALOG_SEQ_NUM = X_CATALOG_SEQ_NUM
1142 ;
1143
1144 lv_rowid cur_rowid%ROWTYPE;
1145
1146 BEGIN
1147 OPEN cur_rowid;
1148 FETCH cur_rowid INTO lv_rowid;
1149 IF (cur_rowid%FOUND) THEN
1150 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUSA_CI_FK');
1151 Igs_Ge_Msg_Stack.ADD;
1152 CLOSE cur_rowid;
1153 App_Exception.Raise_Exception;
1154 RETURN;
1155 END IF;
1156 CLOSE cur_rowid;
1157 END GET_FK_IGS_CA_INST;
1158
1159 PROCEDURE GET_FK_IGS_PS_OFR_UNIT_SET (
1160 x_course_cd IN VARCHAR2,
1161 x_version_number IN NUMBER,
1162 x_cal_type IN VARCHAR2,
1163 x_unit_set_cd IN VARCHAR2,
1164 x_us_version_number IN NUMBER
1165 ) AS
1166 /*************************************************************
1167 Created By :sarakshi
1168 Date Created By :29-May-2006
1169 Purpose :
1170 Know limitations, enhancements or remarks
1171 Change History
1172 Who When What
1173
1174 (reverse chronological order - newest change first)
1175 ***************************************************************/
1176 CURSOR cur_rowid IS
1177 SELECT a.rowid
1178 FROM igs_as_su_setatmpt a, igs_en_stdnt_ps_att b
1179 WHERE a.course_cd=b.course_cd
1180 AND a.person_id=b.person_id
1181 AND b.course_cd=x_course_cd
1182 AND b.version_number=x_version_number
1183 AND b.cal_type = x_cal_type
1184 AND a.unit_set_cd = x_unit_set_cd
1185 AND a.us_version_number = x_us_version_number ;
1186
1187 lv_rowid cur_rowid%RowType;
1188
1189 BEGIN
1190
1191 Open cur_rowid;
1192 Fetch cur_rowid INTO lv_rowid;
1193 IF (cur_rowid%FOUND) THEN
1194 Close cur_rowid;
1195 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUSA_US_FK');
1196 IGS_GE_MSG_STACK.ADD;
1197 App_Exception.Raise_Exception;
1198 Return;
1199 END IF;
1200 Close cur_rowid;
1201
1202 END GET_FK_IGS_PS_OFR_UNIT_SET;
1203
1204 PROCEDURE Before_DML (
1205 p_action IN VARCHAR2,
1206 x_rowid IN VARCHAR2 ,
1207 x_person_id IN NUMBER ,
1208 x_course_cd IN VARCHAR2 ,
1209 x_unit_set_cd IN VARCHAR2 ,
1210 x_us_version_number IN NUMBER ,
1211 x_sequence_number IN NUMBER ,
1212 x_selection_dt IN DATE ,
1213 x_student_confirmed_ind IN VARCHAR2 ,
1214 x_end_dt IN DATE ,
1215 x_parent_unit_set_cd IN VARCHAR2 ,
1216 x_parent_sequence_number IN NUMBER ,
1217 x_primary_set_ind IN VARCHAR2 ,
1218 x_voluntary_end_ind IN VARCHAR2 ,
1219 x_authorised_person_id IN NUMBER ,
1220 x_authorised_on IN DATE ,
1221 x_override_title IN VARCHAR2 ,
1222 x_rqrmnts_complete_ind IN VARCHAR2 ,
1223 x_rqrmnts_complete_dt IN DATE ,
1224 x_s_completed_source_type IN VARCHAR2 ,
1225 x_creation_date IN DATE ,
1226 x_created_by IN NUMBER ,
1227 x_last_update_date IN DATE ,
1228 x_last_updated_by IN NUMBER ,
1229 x_last_update_login IN NUMBER ,
1230 x_catalog_cal_type IN VARCHAR2 ,
1231 x_catalog_seq_num IN NUMBER ,
1232 x_attribute_category IN VARCHAR2 ,
1233 x_attribute1 IN VARCHAR2 ,
1234 x_attribute2 IN VARCHAR2 ,
1235 x_attribute3 IN VARCHAR2 ,
1236 x_attribute4 IN VARCHAR2 ,
1237 x_attribute5 IN VARCHAR2 ,
1238 x_attribute6 IN VARCHAR2 ,
1239 x_attribute7 IN VARCHAR2 ,
1240 x_attribute8 IN VARCHAR2 ,
1241 x_attribute9 IN VARCHAR2 ,
1242 x_attribute10 IN VARCHAR2 ,
1243 x_attribute11 IN VARCHAR2 ,
1244 x_attribute12 IN VARCHAR2 ,
1245 x_attribute13 IN VARCHAR2 ,
1246 x_attribute14 IN VARCHAR2 ,
1247 x_attribute15 IN VARCHAR2 ,
1248 x_attribute16 IN VARCHAR2 ,
1249 x_attribute17 IN VARCHAR2 ,
1250 x_attribute18 IN VARCHAR2 ,
1251 x_attribute19 IN VARCHAR2 ,
1252 x_attribute20 IN VARCHAR2
1253 ) AS
1254 BEGIN
1255 Set_Column_Values (
1256 p_action,
1257 x_rowid,
1258 x_person_id,
1259 x_course_cd,
1260 x_unit_set_cd,
1261 x_us_version_number,
1262 x_sequence_number,
1263 x_selection_dt,
1264 x_student_confirmed_ind,
1265 x_end_dt,
1266 x_parent_unit_set_cd,
1267 x_parent_sequence_number,
1268 x_primary_set_ind,
1269 x_voluntary_end_ind,
1270 x_authorised_person_id,
1271 x_authorised_on,
1272 x_override_title,
1273 x_rqrmnts_complete_ind,
1274 x_rqrmnts_complete_dt,
1275 x_s_completed_source_type,
1276 x_catalog_cal_type,
1277 x_catalog_seq_num,
1278 x_attribute_category,
1279 x_attribute1,
1280 x_attribute2,
1281 x_attribute3,
1282 x_attribute4,
1283 x_attribute5,
1284 x_attribute6,
1285 x_attribute7,
1286 x_attribute8,
1287 x_attribute9,
1288 x_attribute10,
1289 x_attribute11,
1290 x_attribute12,
1291 x_attribute13,
1292 x_attribute14,
1293 x_attribute15,
1294 x_attribute16,
1295 x_attribute17,
1296 x_attribute18,
1297 x_attribute19,
1298 x_attribute20,
1299 x_creation_date,
1300 x_created_by,
1301 x_last_update_date,
1302 x_last_updated_by,
1303 x_last_update_login
1304 );
1305
1306 IF (p_action = 'INSERT') THEN
1307 -- Call all the procedures related to Before Insert.
1308 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE, p_updating=> FALSE, p_deleting=> FALSE );
1309 IF Get_PK_For_Validation (
1310 new_references.person_id,
1311 new_references.course_cd,
1312 new_references.unit_set_cd,
1313 new_references.sequence_number
1314 ) THEN
1315 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
1316 IGS_GE_MSG_STACK.ADD;
1317 App_Exception.Raise_Exception;
1318 END IF;
1319 Check_Constraints;
1320 Check_Parent_Existance;
1321 ELSIF (p_action = 'UPDATE') THEN
1322 -- Call all the procedures related to Before Update.
1323 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE, p_updating=> TRUE, p_deleting=> FALSE );
1324 Check_Constraints; -- if procedure present
1325 Check_Parent_Existance; -- if procedure present
1326 ELSIF (p_action = 'DELETE') THEN
1327 -- Call all the procedures related to Before Delete.
1328 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE, p_updating=> FALSE, p_deleting=> TRUE );
1329 Check_Child_Existance; -- if procedure present
1330 ELSIF (p_action = 'VALIDATE_INSERT') THEN
1331 IF Get_PK_For_Validation (
1332 new_references.person_id,
1333 new_references.course_cd,
1334 new_references.unit_set_cd,
1335 new_references.sequence_number
1336 ) THEN
1337 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
1338 IGS_GE_MSG_STACK.ADD;
1339 App_Exception.Raise_Exception;
1340 END IF;
1341 Check_Constraints;
1342 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
1343 Check_Constraints;
1344 ELSIF (p_action = 'VALIDATE_DELETE') THEN
1345 Check_Child_Existance;
1346
1347 END IF;
1348
1349 END Before_DML;
1350
1351
1352 PROCEDURE After_DML (
1353 p_action IN VARCHAR2,
1354 x_rowid IN VARCHAR2
1355 ) AS
1356 ------------------------------------------------------------------------------
1357 -- Change History:
1358 -- Who When What
1359 -- svanukur 27-NOV-2003 added call to AfterRowInsertUpdate2 if p_action=delete
1360 ------------------------------------------------------------------------------
1361 BEGIN
1362 l_rowid := x_rowid;
1363 IF (p_action = 'INSERT') THEN
1364 -- Call all the procedures related to After Insert.
1365 AfterRowInsertUpdate2 ( p_inserting => TRUE, p_updating => FALSE, p_deleting => FALSE);
1366 ELSIF (p_action = 'UPDATE') THEN
1367 -- Call all the procedures related to After Update.
1368 AfterRowInsertUpdate2 ( p_inserting => FALSE, p_updating => TRUE, p_deleting => FALSE);
1369 AfterRowUpdateDelete3 ( p_inserting => FALSE, p_updating => TRUE, p_deleting => FALSE);
1370 ELSIF (p_action = 'DELETE') THEN
1371 -- Call all the procedures related to After Delete.
1372 AfterRowUpdateDelete3 ( p_inserting => FALSE, p_updating => FALSE, p_deleting => TRUE);
1373 AfterRowInsertUpdate2 ( p_inserting => FALSE, p_updating => FALSE, p_deleting => TRUE);
1374 END IF;
1375 END After_DML;
1376 --
1377 PROCEDURE INSERT_ROW (
1378 X_ROWID IN OUT NOCOPY VARCHAR2,
1379 X_PERSON_ID IN NUMBER,
1380 X_COURSE_CD IN VARCHAR2,
1381 X_UNIT_SET_CD IN VARCHAR2,
1382 X_SEQUENCE_NUMBER IN NUMBER,
1383 X_US_VERSION_NUMBER IN NUMBER,
1384 X_SELECTION_DT IN DATE,
1385 X_STUDENT_CONFIRMED_IND IN VARCHAR2,
1386 X_END_DT IN DATE,
1387 X_PARENT_UNIT_SET_CD IN VARCHAR2,
1388 X_PARENT_SEQUENCE_NUMBER IN NUMBER,
1389 X_PRIMARY_SET_IND IN VARCHAR2,
1390 X_VOLUNTARY_END_IND IN VARCHAR2,
1391 X_AUTHORISED_PERSON_ID IN NUMBER,
1392 X_AUTHORISED_ON IN DATE,
1393 X_OVERRIDE_TITLE IN VARCHAR2,
1394 X_RQRMNTS_COMPLETE_IND IN VARCHAR2,
1395 X_RQRMNTS_COMPLETE_DT IN DATE,
1396 X_S_COMPLETED_SOURCE_TYPE IN VARCHAR2,
1397 X_CATALOG_CAL_TYPE IN VARCHAR2 ,
1398 X_CATALOG_SEQ_NUM IN NUMBER ,
1399 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
1400 X_ATTRIBUTE1 IN VARCHAR2 ,
1401 X_ATTRIBUTE2 IN VARCHAR2 ,
1402 X_ATTRIBUTE3 IN VARCHAR2 ,
1403 X_ATTRIBUTE4 IN VARCHAR2 ,
1404 X_ATTRIBUTE5 IN VARCHAR2 ,
1405 X_ATTRIBUTE6 IN VARCHAR2 ,
1406 X_ATTRIBUTE7 IN VARCHAR2 ,
1407 X_ATTRIBUTE8 IN VARCHAR2 ,
1408 X_ATTRIBUTE9 IN VARCHAR2 ,
1409 X_ATTRIBUTE10 IN VARCHAR2 ,
1410 X_ATTRIBUTE11 IN VARCHAR2 ,
1411 X_ATTRIBUTE12 IN VARCHAR2 ,
1412 X_ATTRIBUTE13 IN VARCHAR2 ,
1413 X_ATTRIBUTE14 IN VARCHAR2 ,
1414 X_ATTRIBUTE15 IN VARCHAR2 ,
1415 X_ATTRIBUTE16 IN VARCHAR2 ,
1416 X_ATTRIBUTE17 IN VARCHAR2 ,
1417 X_ATTRIBUTE18 IN VARCHAR2 ,
1418 X_ATTRIBUTE19 IN VARCHAR2 ,
1419 X_ATTRIBUTE20 IN VARCHAR2 ,
1420 X_MODE IN VARCHAR2
1421 ) AS
1422 CURSOR C IS SELECT ROWID FROM IGS_AS_SU_SETATMPT
1423 WHERE PERSON_ID = X_PERSON_ID
1424 AND COURSE_CD = X_COURSE_CD
1425 AND UNIT_SET_CD = X_UNIT_SET_CD
1426 AND SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
1427 X_LAST_UPDATE_DATE DATE;
1428 X_LAST_UPDATED_BY NUMBER;
1429 X_LAST_UPDATE_LOGIN NUMBER;
1430 X_REQUEST_ID NUMBER;
1431 X_PROGRAM_ID NUMBER;
1432 X_PROGRAM_APPLICATION_ID NUMBER;
1433 X_PROGRAM_UPDATE_DATE DATE;
1434 BEGIN
1435 X_LAST_UPDATE_DATE := SYSDATE;
1436 IF(X_MODE = 'I') THEN
1437 X_LAST_UPDATED_BY := 1;
1438 X_LAST_UPDATE_LOGIN := 0;
1439 ELSIF (X_MODE IN ('R', 'S')) THEN
1440 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1441 IF X_LAST_UPDATED_BY IS NULL THEN
1442 X_LAST_UPDATED_BY := -1;
1443 END IF;
1444 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1445 IF X_LAST_UPDATE_LOGIN IS NULL THEN
1446 X_LAST_UPDATE_LOGIN := -1;
1447 END IF;
1448 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1449 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1450 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1451 IF (X_REQUEST_ID = -1) THEN
1452 X_REQUEST_ID := NULL;
1453 X_PROGRAM_ID := NULL;
1454 X_PROGRAM_APPLICATION_ID := NULL;
1455 X_PROGRAM_UPDATE_DATE := NULL;
1456 ELSE
1457 X_PROGRAM_UPDATE_DATE := SYSDATE;
1458 END IF;
1459 ELSE
1460 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1461 IGS_GE_MSG_STACK.ADD;
1462 app_exception.raise_exception;
1463 END IF;
1464 --
1465 Before_DML(
1466 p_action=>'INSERT',
1467 x_rowid=>X_ROWID,
1468 x_authorised_on=>X_AUTHORISED_ON,
1469 x_authorised_person_id=>X_AUTHORISED_PERSON_ID,
1470 x_course_cd=>X_COURSE_CD,
1471 x_end_dt=>X_END_DT,
1472 x_override_title=>X_OVERRIDE_TITLE,
1473 x_parent_sequence_number=>X_PARENT_SEQUENCE_NUMBER,
1474 x_parent_unit_set_cd=>X_PARENT_UNIT_SET_CD,
1475 x_person_id=>X_PERSON_ID,
1476 x_primary_set_ind=> NVL(X_PRIMARY_SET_IND,'N'),
1477 x_rqrmnts_complete_dt=>X_RQRMNTS_COMPLETE_DT,
1478 x_rqrmnts_complete_ind=> NVL(X_RQRMNTS_COMPLETE_IND,'N'),
1479 x_s_completed_source_type=>X_S_COMPLETED_SOURCE_TYPE,
1480 x_selection_dt=>X_SELECTION_DT,
1481 x_sequence_number=>X_SEQUENCE_NUMBER,
1482 x_student_confirmed_ind=>X_STUDENT_CONFIRMED_IND,
1483 x_unit_set_cd=>X_UNIT_SET_CD,
1484 x_us_version_number=>X_US_VERSION_NUMBER,
1485 x_voluntary_end_ind=> NVL(X_VOLUNTARY_END_IND,'N'),
1486 x_creation_date=>X_LAST_UPDATE_DATE,
1487 x_created_by=>X_LAST_UPDATED_BY,
1488 x_last_update_date=>X_LAST_UPDATE_DATE,
1489 x_last_updated_by=>X_LAST_UPDATED_BY,
1490 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1491 x_catalog_cal_type => X_CATALOG_CAL_TYPE,
1492 x_catalog_seq_num => X_CATALOG_SEQ_NUM,
1493 x_attribute_category=> X_ATTRIBUTE_CATEGORY,
1494 x_attribute1=> X_ATTRIBUTE1,
1495 x_attribute2=> X_ATTRIBUTE2,
1496 x_attribute3=> X_ATTRIBUTE3,
1497 x_attribute4=> X_ATTRIBUTE4,
1498 x_attribute5=> X_ATTRIBUTE5,
1499 x_attribute6=> X_ATTRIBUTE6,
1500 x_attribute7=> X_ATTRIBUTE7,
1501 x_attribute8=> X_ATTRIBUTE8,
1502 x_attribute9=> X_ATTRIBUTE9,
1503 x_attribute10=>X_ATTRIBUTE10,
1504 x_attribute11=>X_ATTRIBUTE11,
1505 x_attribute12=>X_ATTRIBUTE12,
1506 x_attribute13=>X_ATTRIBUTE13,
1507 x_attribute14=>X_ATTRIBUTE14,
1508 x_attribute15=>X_ATTRIBUTE15,
1509 x_attribute16=>X_ATTRIBUTE16,
1510 x_attribute17=>X_ATTRIBUTE17,
1511 x_attribute18=>X_ATTRIBUTE18,
1512 x_attribute19=>X_ATTRIBUTE19,
1513 x_attribute20=>X_ATTRIBUTE20
1514 );
1515 --
1516 IF (x_mode = 'S') THEN
1517 igs_sc_gen_001.set_ctx('R');
1518 END IF;
1519 INSERT INTO IGS_AS_SU_SETATMPT (
1520 PERSON_ID,
1521 COURSE_CD,
1522 UNIT_SET_CD,
1523 US_VERSION_NUMBER,
1524 SEQUENCE_NUMBER,
1525 SELECTION_DT,
1526 STUDENT_CONFIRMED_IND,
1527 END_DT,
1528 PARENT_UNIT_SET_CD,
1529 PARENT_SEQUENCE_NUMBER,
1530 PRIMARY_SET_IND,
1531 VOLUNTARY_END_IND,
1532 AUTHORISED_PERSON_ID,
1533 AUTHORISED_ON,
1534 OVERRIDE_TITLE,
1535 RQRMNTS_COMPLETE_IND,
1536 RQRMNTS_COMPLETE_DT,
1537 S_COMPLETED_SOURCE_TYPE,
1538 CATALOG_CAL_TYPE,
1539 CATALOG_SEQ_NUM,
1540 CREATION_DATE,
1541 CREATED_BY,
1542 LAST_UPDATE_DATE,
1543 LAST_UPDATED_BY,
1544 LAST_UPDATE_LOGIN,
1545 REQUEST_ID,
1546 PROGRAM_ID,
1547 PROGRAM_APPLICATION_ID,
1548 PROGRAM_UPDATE_DATE,
1549 ATTRIBUTE_CATEGORY,
1550 ATTRIBUTE1,
1551 ATTRIBUTE2,
1552 ATTRIBUTE3,
1553 ATTRIBUTE4,
1554 ATTRIBUTE5,
1555 ATTRIBUTE6,
1556 ATTRIBUTE7,
1557 ATTRIBUTE8,
1558 ATTRIBUTE9,
1559 ATTRIBUTE10,
1560 ATTRIBUTE11,
1561 ATTRIBUTE12,
1562 ATTRIBUTE13,
1563 ATTRIBUTE14,
1564 ATTRIBUTE15,
1565 ATTRIBUTE16,
1566 ATTRIBUTE17,
1567 ATTRIBUTE18,
1568 ATTRIBUTE19,
1569 ATTRIBUTE20
1570 ) VALUES (
1571 NEW_REFERENCES.PERSON_ID,
1572 NEW_REFERENCES.COURSE_CD,
1573 NEW_REFERENCES.UNIT_SET_CD,
1574 NEW_REFERENCES.US_VERSION_NUMBER,
1575 NEW_REFERENCES.SEQUENCE_NUMBER,
1576 NEW_REFERENCES.SELECTION_DT,
1577 NEW_REFERENCES.STUDENT_CONFIRMED_IND,
1578 NEW_REFERENCES.END_DT,
1579 NEW_REFERENCES.PARENT_UNIT_SET_CD,
1580 NEW_REFERENCES.PARENT_SEQUENCE_NUMBER,
1581 NEW_REFERENCES.PRIMARY_SET_IND,
1582 NEW_REFERENCES.VOLUNTARY_END_IND,
1583 NEW_REFERENCES.AUTHORISED_PERSON_ID,
1584 NEW_REFERENCES.AUTHORISED_ON,
1585 NEW_REFERENCES.OVERRIDE_TITLE,
1586 NEW_REFERENCES.RQRMNTS_COMPLETE_IND,
1587 NEW_REFERENCES.RQRMNTS_COMPLETE_DT,
1588 NEW_REFERENCES.S_COMPLETED_SOURCE_TYPE,
1589 NEW_REFERENCES.CATALOG_CAL_TYPE,
1590 NEW_REFERENCES.CATALOG_SEQ_NUM,
1591 X_LAST_UPDATE_DATE,
1592 X_LAST_UPDATED_BY,
1593 X_LAST_UPDATE_DATE,
1594 X_LAST_UPDATED_BY,
1595 X_LAST_UPDATE_LOGIN,
1596 X_REQUEST_ID,
1597 X_PROGRAM_ID,
1598 X_PROGRAM_APPLICATION_ID,
1599 X_PROGRAM_UPDATE_DATE,
1600 NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1601 NEW_REFERENCES.ATTRIBUTE1,
1602 NEW_REFERENCES.ATTRIBUTE2,
1603 NEW_REFERENCES.ATTRIBUTE3,
1604 NEW_REFERENCES.ATTRIBUTE4,
1605 NEW_REFERENCES.ATTRIBUTE5,
1606 NEW_REFERENCES.ATTRIBUTE6,
1607 NEW_REFERENCES.ATTRIBUTE7,
1608 NEW_REFERENCES.ATTRIBUTE8,
1609 NEW_REFERENCES.ATTRIBUTE9,
1610 NEW_REFERENCES.ATTRIBUTE10,
1611 NEW_REFERENCES.ATTRIBUTE11,
1612 NEW_REFERENCES.ATTRIBUTE12,
1613 NEW_REFERENCES.ATTRIBUTE13,
1614 NEW_REFERENCES.ATTRIBUTE14,
1615 NEW_REFERENCES.ATTRIBUTE15,
1616 NEW_REFERENCES.ATTRIBUTE16,
1617 NEW_REFERENCES.ATTRIBUTE17,
1618 NEW_REFERENCES.ATTRIBUTE18,
1619 NEW_REFERENCES.ATTRIBUTE19,
1620 NEW_REFERENCES.ATTRIBUTE20
1621 );
1622 IF (x_mode = 'S') THEN
1623 igs_sc_gen_001.unset_ctx('R');
1624 END IF;
1625
1626 OPEN c;
1627 FETCH c INTO X_ROWID;
1628 IF (c%NOTFOUND) THEN
1629 CLOSE c;
1630 RAISE NO_DATA_FOUND;
1631 END IF;
1632 CLOSE c;
1633 --
1634 After_DML(
1635 p_action => 'INSERT',
1636 x_rowid => X_ROWID
1637 );
1638 --
1639 EXCEPTION
1640 WHEN OTHERS THEN
1641 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1642 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1643 fnd_message.set_token ('ERR_CD', SQLCODE);
1644 igs_ge_msg_stack.add;
1645 igs_sc_gen_001.unset_ctx('R');
1646 app_exception.raise_exception;
1647 ELSE
1648 igs_sc_gen_001.unset_ctx('R');
1649 RAISE;
1650 END IF;
1651
1652 END INSERT_ROW;
1653 PROCEDURE LOCK_ROW (
1654 X_ROWID IN VARCHAR2,
1655 X_PERSON_ID IN NUMBER,
1656 X_COURSE_CD IN VARCHAR2,
1657 X_UNIT_SET_CD IN VARCHAR2,
1658 X_SEQUENCE_NUMBER IN NUMBER,
1659 X_US_VERSION_NUMBER IN NUMBER,
1660 X_SELECTION_DT IN DATE,
1661 X_STUDENT_CONFIRMED_IND IN VARCHAR2,
1662 X_END_DT IN DATE,
1663 X_PARENT_UNIT_SET_CD IN VARCHAR2,
1664 X_PARENT_SEQUENCE_NUMBER IN NUMBER,
1665 X_PRIMARY_SET_IND IN VARCHAR2,
1666 X_VOLUNTARY_END_IND IN VARCHAR2,
1667 X_AUTHORISED_PERSON_ID IN NUMBER,
1668 X_AUTHORISED_ON IN DATE,
1669 X_OVERRIDE_TITLE IN VARCHAR2,
1670 X_RQRMNTS_COMPLETE_IND IN VARCHAR2,
1671 X_RQRMNTS_COMPLETE_DT IN DATE,
1672 X_S_COMPLETED_SOURCE_TYPE IN VARCHAR2,
1673 X_CATALOG_CAL_TYPE IN VARCHAR2 ,
1674 X_CATALOG_SEQ_NUM IN NUMBER ,
1675 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
1676 X_ATTRIBUTE1 IN VARCHAR2 ,
1677 X_ATTRIBUTE2 IN VARCHAR2 ,
1678 X_ATTRIBUTE3 IN VARCHAR2 ,
1679 X_ATTRIBUTE4 IN VARCHAR2 ,
1680 X_ATTRIBUTE5 IN VARCHAR2 ,
1681 X_ATTRIBUTE6 IN VARCHAR2 ,
1682 X_ATTRIBUTE7 IN VARCHAR2 ,
1683 X_ATTRIBUTE8 IN VARCHAR2 ,
1684 X_ATTRIBUTE9 IN VARCHAR2 ,
1685 X_ATTRIBUTE10 IN VARCHAR2 ,
1686 X_ATTRIBUTE11 IN VARCHAR2 ,
1687 X_ATTRIBUTE12 IN VARCHAR2 ,
1688 X_ATTRIBUTE13 IN VARCHAR2 ,
1689 X_ATTRIBUTE14 IN VARCHAR2 ,
1690 X_ATTRIBUTE15 IN VARCHAR2 ,
1691 X_ATTRIBUTE16 IN VARCHAR2 ,
1692 X_ATTRIBUTE17 IN VARCHAR2 ,
1693 X_ATTRIBUTE18 IN VARCHAR2 ,
1694 X_ATTRIBUTE19 IN VARCHAR2 ,
1695 X_ATTRIBUTE20 IN VARCHAR2
1696 ) AS
1697 CURSOR c1 IS SELECT
1698 US_VERSION_NUMBER,
1699 SELECTION_DT,
1700 STUDENT_CONFIRMED_IND,
1701 END_DT,
1702 PARENT_UNIT_SET_CD,
1703 PARENT_SEQUENCE_NUMBER,
1704 PRIMARY_SET_IND,
1705 VOLUNTARY_END_IND,
1706 AUTHORISED_PERSON_ID,
1707 AUTHORISED_ON,
1708 OVERRIDE_TITLE,
1709 RQRMNTS_COMPLETE_IND,
1710 RQRMNTS_COMPLETE_DT,
1711 S_COMPLETED_SOURCE_TYPE,
1712 CATALOG_CAL_TYPE,
1713 CATALOG_SEQ_NUM,
1714 ATTRIBUTE_CATEGORY,
1715 ATTRIBUTE1,
1716 ATTRIBUTE2,
1717 ATTRIBUTE3,
1718 ATTRIBUTE4,
1719 ATTRIBUTE5,
1720 ATTRIBUTE6,
1721 ATTRIBUTE7,
1722 ATTRIBUTE8,
1723 ATTRIBUTE9,
1724 ATTRIBUTE10,
1725 ATTRIBUTE11,
1726 ATTRIBUTE12,
1727 ATTRIBUTE13,
1728 ATTRIBUTE14,
1729 ATTRIBUTE15,
1730 ATTRIBUTE16,
1731 ATTRIBUTE17,
1732 ATTRIBUTE18,
1733 ATTRIBUTE19,
1734 ATTRIBUTE20
1735 FROM IGS_AS_SU_SETATMPT
1736 WHERE ROWID = X_ROWID FOR UPDATE NOWAIT;
1737 tlinfo c1%ROWTYPE;
1738 BEGIN
1739 OPEN c1;
1740 FETCH c1 INTO tlinfo;
1741 IF (c1%NOTFOUND) THEN
1742 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1743 IGS_GE_MSG_STACK.ADD;
1744 CLOSE c1;
1745 app_exception.raise_exception;
1746
1747 RETURN;
1748 END IF;
1749 CLOSE c1;
1750 IF ( (tlinfo.US_VERSION_NUMBER = X_US_VERSION_NUMBER)
1751 AND ((trunc(tlinfo.SELECTION_DT) = trunc(X_SELECTION_DT))
1752 OR ((tlinfo.SELECTION_DT IS NULL)
1753 AND (X_SELECTION_DT IS NULL)))
1754 AND (tlinfo.STUDENT_CONFIRMED_IND = X_STUDENT_CONFIRMED_IND)
1755 AND ((trunc(tlinfo.END_DT) = trunc(X_END_DT))
1756 OR ((tlinfo.END_DT IS NULL)
1757 AND (X_END_DT IS NULL)))
1758 AND ((tlinfo.PARENT_UNIT_SET_CD = X_PARENT_UNIT_SET_CD)
1759 OR ((tlinfo.PARENT_UNIT_SET_CD IS NULL)
1760 AND (X_PARENT_UNIT_SET_CD IS NULL)))
1761 AND ((tlinfo.PARENT_SEQUENCE_NUMBER = X_PARENT_SEQUENCE_NUMBER)
1762 OR ((tlinfo.PARENT_SEQUENCE_NUMBER IS NULL)
1763 AND (X_PARENT_SEQUENCE_NUMBER IS NULL)))
1764 AND (tlinfo.PRIMARY_SET_IND = X_PRIMARY_SET_IND)
1765 AND (tlinfo.VOLUNTARY_END_IND = X_VOLUNTARY_END_IND)
1766 AND ((tlinfo.AUTHORISED_PERSON_ID = X_AUTHORISED_PERSON_ID)
1767 OR ((tlinfo.AUTHORISED_PERSON_ID IS NULL)
1768 AND (X_AUTHORISED_PERSON_ID IS NULL)))
1769 AND ((tlinfo.AUTHORISED_ON = X_AUTHORISED_ON)
1770 OR ((tlinfo.AUTHORISED_ON IS NULL)
1771 AND (X_AUTHORISED_ON IS NULL)))
1772 AND ((tlinfo.OVERRIDE_TITLE = X_OVERRIDE_TITLE)
1773 OR ((tlinfo.OVERRIDE_TITLE IS NULL)
1774 AND (X_OVERRIDE_TITLE IS NULL)))
1775 AND (tlinfo.RQRMNTS_COMPLETE_IND = X_RQRMNTS_COMPLETE_IND)
1776 AND ((trunc(tlinfo.RQRMNTS_COMPLETE_DT) = trunc(X_RQRMNTS_COMPLETE_DT))
1777 OR ((tlinfo.RQRMNTS_COMPLETE_DT IS NULL)
1778 AND (X_RQRMNTS_COMPLETE_DT IS NULL)))
1779 AND ((tlinfo.S_COMPLETED_SOURCE_TYPE = X_S_COMPLETED_SOURCE_TYPE)
1780 OR ((tlinfo.S_COMPLETED_SOURCE_TYPE IS NULL)
1781 AND (X_S_COMPLETED_SOURCE_TYPE IS NULL)))
1782 AND ((tlinfo.CATALOG_CAL_TYPE = X_CATALOG_CAL_TYPE)
1783 OR ((tlinfo.CATALOG_CAL_TYPE IS NULL)
1784 AND (X_CATALOG_CAL_TYPE IS NULL)))
1785 AND ((tlinfo.CATALOG_SEQ_NUM = X_CATALOG_SEQ_NUM)
1786 OR ((tlinfo.CATALOG_SEQ_NUM IS NULL)
1787 AND (X_CATALOG_SEQ_NUM IS NULL)))
1788 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
1789 OR ((tlinfo.ATTRIBUTE_CATEGORY IS NULL)
1790 AND (X_ATTRIBUTE_CATEGORY IS NULL)))
1791 AND ((tlinfo.ATTRIBUTE1= X_ATTRIBUTE1)
1792 OR ((tlinfo.ATTRIBUTE1 IS NULL)
1793 AND (X_ATTRIBUTE1 IS NULL)))
1794 AND ((tlinfo.ATTRIBUTE2= X_ATTRIBUTE2)
1795 OR ((tlinfo.ATTRIBUTE2 IS NULL)
1796 AND (X_ATTRIBUTE2 IS NULL)))
1797 AND ((tlinfo.ATTRIBUTE3= X_ATTRIBUTE3)
1798 OR ((tlinfo.ATTRIBUTE3 IS NULL)
1799 AND (X_ATTRIBUTE3 IS NULL)))
1800 AND ((tlinfo.ATTRIBUTE4= X_ATTRIBUTE4)
1801 OR ((tlinfo.ATTRIBUTE4 IS NULL)
1802 AND (X_ATTRIBUTE4 IS NULL)))
1803 AND ((tlinfo.ATTRIBUTE5= X_ATTRIBUTE5)
1804 OR ((tlinfo.ATTRIBUTE5 IS NULL)
1805 AND (X_ATTRIBUTE5 IS NULL)))
1806 AND ((tlinfo.ATTRIBUTE6= X_ATTRIBUTE6)
1807 OR ((tlinfo.ATTRIBUTE6 IS NULL)
1808 AND (X_ATTRIBUTE6 IS NULL)))
1809 AND ((tlinfo.ATTRIBUTE7= X_ATTRIBUTE7)
1810 OR ((tlinfo.ATTRIBUTE7 IS NULL)
1811 AND (X_ATTRIBUTE7 IS NULL)))
1812 AND ((tlinfo.ATTRIBUTE8= X_ATTRIBUTE8)
1813 OR ((tlinfo.ATTRIBUTE8 IS NULL)
1814 AND (X_ATTRIBUTE8 IS NULL)))
1815 AND ((tlinfo.ATTRIBUTE9= X_ATTRIBUTE9)
1816 OR ((tlinfo.ATTRIBUTE9 IS NULL)
1817 AND (X_ATTRIBUTE9 IS NULL)))
1818 AND ((tlinfo.ATTRIBUTE10= X_ATTRIBUTE10)
1819 OR ((tlinfo.ATTRIBUTE10 IS NULL)
1820 AND (X_ATTRIBUTE10 IS NULL)))
1821 AND ((tlinfo.ATTRIBUTE11= X_ATTRIBUTE11)
1822 OR ((tlinfo.ATTRIBUTE11 IS NULL)
1823 AND (X_ATTRIBUTE11 IS NULL)))
1824 AND ((tlinfo.ATTRIBUTE12= X_ATTRIBUTE12)
1825 OR ((tlinfo.ATTRIBUTE12 IS NULL)
1826 AND (X_ATTRIBUTE12 IS NULL)))
1827 AND ((tlinfo.ATTRIBUTE13= X_ATTRIBUTE13)
1828 OR ((tlinfo.ATTRIBUTE13 IS NULL)
1829 AND (X_ATTRIBUTE13 IS NULL)))
1830 AND ((tlinfo.ATTRIBUTE14= X_ATTRIBUTE14)
1831 OR ((tlinfo.ATTRIBUTE14 IS NULL)
1832 AND (X_ATTRIBUTE14 IS NULL)))
1833 AND ((tlinfo.ATTRIBUTE15= X_ATTRIBUTE15)
1834 OR ((tlinfo.ATTRIBUTE15 IS NULL)
1835 AND (X_ATTRIBUTE15 IS NULL)))
1836 AND ((tlinfo.ATTRIBUTE16= X_ATTRIBUTE16)
1837 OR ((tlinfo.ATTRIBUTE16 IS NULL)
1838 AND (X_ATTRIBUTE16 IS NULL)))
1839 AND ((tlinfo.ATTRIBUTE17= X_ATTRIBUTE17)
1840 OR ((tlinfo.ATTRIBUTE17 IS NULL)
1841 AND (X_ATTRIBUTE17 IS NULL)))
1842 AND ((tlinfo.ATTRIBUTE18= X_ATTRIBUTE18)
1843 OR ((tlinfo.ATTRIBUTE18 IS NULL)
1844 AND (X_ATTRIBUTE18 IS NULL)))
1845 AND ((tlinfo.ATTRIBUTE19= X_ATTRIBUTE19)
1846 OR ((tlinfo.ATTRIBUTE19 IS NULL)
1847 AND (X_ATTRIBUTE19 IS NULL)))
1848 AND ((tlinfo.ATTRIBUTE20= X_ATTRIBUTE20)
1849 OR ((tlinfo.ATTRIBUTE20 IS NULL)
1850 AND (X_ATTRIBUTE20 IS NULL)))
1851 ) THEN
1852 NULL;
1853 ELSE
1854 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1855 IGS_GE_MSG_STACK.ADD;
1856 app_exception.raise_exception;
1857 END IF;
1858 RETURN;
1859 END LOCK_ROW;
1860 PROCEDURE UPDATE_ROW (
1861 X_ROWID IN VARCHAR2,
1862 X_PERSON_ID IN NUMBER,
1863 X_COURSE_CD IN VARCHAR2,
1864 X_UNIT_SET_CD IN VARCHAR2,
1865 X_SEQUENCE_NUMBER IN NUMBER,
1866 X_US_VERSION_NUMBER IN NUMBER,
1867 X_SELECTION_DT IN DATE,
1868 X_STUDENT_CONFIRMED_IND IN VARCHAR2,
1869 X_END_DT IN DATE,
1870 X_PARENT_UNIT_SET_CD IN VARCHAR2,
1871 X_PARENT_SEQUENCE_NUMBER IN NUMBER,
1872 X_PRIMARY_SET_IND IN VARCHAR2,
1873 X_VOLUNTARY_END_IND IN VARCHAR2,
1874 X_AUTHORISED_PERSON_ID IN NUMBER,
1875 X_AUTHORISED_ON IN DATE,
1876 X_OVERRIDE_TITLE IN VARCHAR2,
1877 X_RQRMNTS_COMPLETE_IND IN VARCHAR2,
1878 X_RQRMNTS_COMPLETE_DT IN DATE,
1879 X_S_COMPLETED_SOURCE_TYPE IN VARCHAR2,
1880 X_CATALOG_CAL_TYPE IN VARCHAR2 ,
1881 X_CATALOG_SEQ_NUM IN NUMBER ,
1882 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
1883 X_ATTRIBUTE1 IN VARCHAR2 ,
1884 X_ATTRIBUTE2 IN VARCHAR2 ,
1885 X_ATTRIBUTE3 IN VARCHAR2 ,
1886 X_ATTRIBUTE4 IN VARCHAR2 ,
1887 X_ATTRIBUTE5 IN VARCHAR2 ,
1888 X_ATTRIBUTE6 IN VARCHAR2 ,
1889 X_ATTRIBUTE7 IN VARCHAR2 ,
1890 X_ATTRIBUTE8 IN VARCHAR2 ,
1891 X_ATTRIBUTE9 IN VARCHAR2 ,
1892 X_ATTRIBUTE10 IN VARCHAR2 ,
1893 X_ATTRIBUTE11 IN VARCHAR2 ,
1894 X_ATTRIBUTE12 IN VARCHAR2 ,
1895 X_ATTRIBUTE13 IN VARCHAR2 ,
1896 X_ATTRIBUTE14 IN VARCHAR2 ,
1897 X_ATTRIBUTE15 IN VARCHAR2 ,
1898 X_ATTRIBUTE16 IN VARCHAR2 ,
1899 X_ATTRIBUTE17 IN VARCHAR2 ,
1900 X_ATTRIBUTE18 IN VARCHAR2 ,
1901 X_ATTRIBUTE19 IN VARCHAR2 ,
1902 X_ATTRIBUTE20 IN VARCHAR2 ,
1903 X_MODE IN VARCHAR2
1904 ) AS
1905 X_LAST_UPDATE_DATE DATE;
1906 X_LAST_UPDATED_BY NUMBER;
1907 X_LAST_UPDATE_LOGIN NUMBER;
1908 X_REQUEST_ID NUMBER;
1909 X_PROGRAM_ID NUMBER;
1910 X_PROGRAM_APPLICATION_ID NUMBER;
1911 X_PROGRAM_UPDATE_DATE DATE;
1912 BEGIN
1913 X_LAST_UPDATE_DATE := SYSDATE;
1914 IF(X_MODE = 'I') THEN
1915 X_LAST_UPDATED_BY := 1;
1916 X_LAST_UPDATE_LOGIN := 0;
1917 ELSIF (X_MODE IN ('R', 'S')) THEN
1918 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1919 IF X_LAST_UPDATED_BY IS NULL THEN
1920 X_LAST_UPDATED_BY := -1;
1921 END IF;
1922 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1923 IF X_LAST_UPDATE_LOGIN IS NULL THEN
1924 X_LAST_UPDATE_LOGIN := -1;
1925 END IF;
1926 ELSE
1927 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1928 IGS_GE_MSG_STACK.ADD;
1929 app_exception.raise_exception;
1930 END IF;
1931 Before_DML(
1932 p_action=>'UPDATE',
1933 x_rowid=>X_ROWID,
1934 x_authorised_on=>X_AUTHORISED_ON,
1935 x_authorised_person_id=>X_AUTHORISED_PERSON_ID,
1936 x_course_cd=>X_COURSE_CD,
1937 x_end_dt=>X_END_DT,
1938 x_override_title=>X_OVERRIDE_TITLE,
1939 x_parent_sequence_number=>X_PARENT_SEQUENCE_NUMBER,
1940 x_parent_unit_set_cd=>X_PARENT_UNIT_SET_CD,
1941 x_person_id=>X_PERSON_ID,
1942 x_primary_set_ind=>X_PRIMARY_SET_IND,
1943 x_rqrmnts_complete_dt=>X_RQRMNTS_COMPLETE_DT,
1944 x_rqrmnts_complete_ind=>X_RQRMNTS_COMPLETE_IND,
1945 x_s_completed_source_type=>X_S_COMPLETED_SOURCE_TYPE,
1946 x_selection_dt=>X_SELECTION_DT,
1947 x_sequence_number=>X_SEQUENCE_NUMBER,
1948 x_student_confirmed_ind=>X_STUDENT_CONFIRMED_IND,
1949 x_unit_set_cd=>X_UNIT_SET_CD,
1950 x_us_version_number=>X_US_VERSION_NUMBER,
1951 x_voluntary_end_ind=>X_VOLUNTARY_END_IND,
1952 x_creation_date=>X_LAST_UPDATE_DATE,
1953 x_created_by=>X_LAST_UPDATED_BY,
1954 x_last_update_date=>X_LAST_UPDATE_DATE,
1955 x_last_updated_by=>X_LAST_UPDATED_BY,
1956 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1957 x_catalog_cal_type => X_CATALOG_CAL_TYPE,
1958 x_catalog_seq_num => X_CATALOG_SEQ_NUM,
1959 x_attribute_category=> X_ATTRIBUTE_CATEGORY,
1960 x_attribute1=> X_ATTRIBUTE1,
1961 x_attribute2=> X_ATTRIBUTE2,
1962 x_attribute3=> X_ATTRIBUTE3,
1963 x_attribute4=> X_ATTRIBUTE4,
1964 x_attribute5=> X_ATTRIBUTE5,
1965 x_attribute6=> X_ATTRIBUTE6,
1966 x_attribute7=> X_ATTRIBUTE7,
1967 x_attribute8=> X_ATTRIBUTE8,
1968 x_attribute9=> X_ATTRIBUTE9,
1969 x_attribute10=>X_ATTRIBUTE10,
1970 x_attribute11=>X_ATTRIBUTE11,
1971 x_attribute12=>X_ATTRIBUTE12,
1972 x_attribute13=>X_ATTRIBUTE13,
1973 x_attribute14=>X_ATTRIBUTE14,
1974 x_attribute15=>X_ATTRIBUTE15,
1975 x_attribute16=>X_ATTRIBUTE16,
1976 x_attribute17=>X_ATTRIBUTE17,
1977 x_attribute18=>X_ATTRIBUTE18,
1978 x_attribute19=>X_ATTRIBUTE19,
1979 x_attribute20=>X_ATTRIBUTE20
1980 );
1981 IF (X_MODE IN ('R', 'S')) THEN
1982 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1983 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1984 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1985 IF (X_REQUEST_ID = -1) THEN
1986 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1987 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
1988 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1989 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1990 ELSE
1991 X_PROGRAM_UPDATE_DATE := SYSDATE;
1992 END IF;
1993 --
1994 --
1995 END IF;
1996 IF (x_mode = 'S') THEN
1997 igs_sc_gen_001.set_ctx('R');
1998 END IF;
1999 UPDATE IGS_AS_SU_SETATMPT SET
2000 US_VERSION_NUMBER = NEW_REFERENCES.US_VERSION_NUMBER,
2001 SELECTION_DT = NEW_REFERENCES.SELECTION_DT,
2002 STUDENT_CONFIRMED_IND = NEW_REFERENCES.STUDENT_CONFIRMED_IND,
2003 END_DT = NEW_REFERENCES.END_DT,
2004 PARENT_UNIT_SET_CD = NEW_REFERENCES.PARENT_UNIT_SET_CD,
2005 PARENT_SEQUENCE_NUMBER = NEW_REFERENCES.PARENT_SEQUENCE_NUMBER,
2006 PRIMARY_SET_IND = NEW_REFERENCES.PRIMARY_SET_IND,
2007 VOLUNTARY_END_IND = NEW_REFERENCES.VOLUNTARY_END_IND,
2008 AUTHORISED_PERSON_ID = NEW_REFERENCES.AUTHORISED_PERSON_ID,
2009 AUTHORISED_ON = NEW_REFERENCES.AUTHORISED_ON,
2010 OVERRIDE_TITLE = NEW_REFERENCES.OVERRIDE_TITLE,
2011 RQRMNTS_COMPLETE_IND = NEW_REFERENCES.RQRMNTS_COMPLETE_IND,
2012 RQRMNTS_COMPLETE_DT = NEW_REFERENCES.RQRMNTS_COMPLETE_DT,
2013 S_COMPLETED_SOURCE_TYPE = NEW_REFERENCES.S_COMPLETED_SOURCE_TYPE,
2014 CATALOG_CAL_TYPE = NEW_REFERENCES.CATALOG_CAL_TYPE,
2015 CATALOG_SEQ_NUM = NEW_REFERENCES.CATALOG_SEQ_NUM,
2016 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
2017 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2018 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
2019 REQUEST_ID = X_REQUEST_ID,
2020 PROGRAM_ID = X_PROGRAM_ID,
2021 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
2022 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
2023 ATTRIBUTE_CATEGORY=X_ATTRIBUTE_CATEGORY,
2024 ATTRIBUTE1=NEW_REFERENCES.ATTRIBUTE1,
2025 ATTRIBUTE2=NEW_REFERENCES.ATTRIBUTE2,
2026 ATTRIBUTE3=NEW_REFERENCES.ATTRIBUTE3,
2027 ATTRIBUTE4=NEW_REFERENCES.ATTRIBUTE4,
2028 ATTRIBUTE5=NEW_REFERENCES.ATTRIBUTE5,
2029 ATTRIBUTE6=NEW_REFERENCES.ATTRIBUTE6,
2030 ATTRIBUTE7=NEW_REFERENCES.ATTRIBUTE7,
2031 ATTRIBUTE8=NEW_REFERENCES.ATTRIBUTE8,
2032 ATTRIBUTE9=NEW_REFERENCES.ATTRIBUTE9,
2033 ATTRIBUTE10=NEW_REFERENCES.ATTRIBUTE10,
2034 ATTRIBUTE11=NEW_REFERENCES.ATTRIBUTE11,
2035 ATTRIBUTE12=NEW_REFERENCES.ATTRIBUTE12,
2036 ATTRIBUTE13=NEW_REFERENCES.ATTRIBUTE13,
2037 ATTRIBUTE14=NEW_REFERENCES.ATTRIBUTE14,
2038 ATTRIBUTE15=NEW_REFERENCES.ATTRIBUTE15,
2039 ATTRIBUTE16=NEW_REFERENCES.ATTRIBUTE16,
2040 ATTRIBUTE17=NEW_REFERENCES.ATTRIBUTE17,
2041 ATTRIBUTE18=NEW_REFERENCES.ATTRIBUTE18,
2042 ATTRIBUTE19=NEW_REFERENCES.ATTRIBUTE19,
2043 ATTRIBUTE20=NEW_REFERENCES.ATTRIBUTE20
2044 WHERE ROWID = X_ROWID;
2045 IF (SQL%NOTFOUND) THEN
2046 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
2047 igs_ge_msg_stack.add;
2048 igs_sc_gen_001.unset_ctx('R');
2049 app_exception.raise_exception;
2050 END IF;
2051 IF (x_mode = 'S') THEN
2052 igs_sc_gen_001.unset_ctx('R');
2053 END IF;
2054
2055 --
2056 After_DML(
2057 p_action => 'UPDATE',
2058 x_rowid => X_ROWID
2059 );
2060 --
2061 EXCEPTION
2062 WHEN OTHERS THEN
2063 IF (SQLCODE = (-28115)) THEN
2064 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
2065 fnd_message.set_token ('ERR_CD', SQLCODE);
2066 igs_ge_msg_stack.add;
2067 igs_sc_gen_001.unset_ctx('R');
2068 app_exception.raise_exception;
2069 ELSE
2070 igs_sc_gen_001.unset_ctx('R');
2071 RAISE;
2072 END IF;
2073
2074 END UPDATE_ROW;
2075 PROCEDURE ADD_ROW (
2076 X_ROWID IN OUT NOCOPY VARCHAR2,
2077 X_PERSON_ID IN NUMBER,
2078 X_COURSE_CD IN VARCHAR2,
2079 X_UNIT_SET_CD IN VARCHAR2,
2080 X_SEQUENCE_NUMBER IN NUMBER,
2081 X_US_VERSION_NUMBER IN NUMBER,
2082 X_SELECTION_DT IN DATE,
2083 X_STUDENT_CONFIRMED_IND IN VARCHAR2,
2084 X_END_DT IN DATE,
2085 X_PARENT_UNIT_SET_CD IN VARCHAR2,
2086 X_PARENT_SEQUENCE_NUMBER IN NUMBER,
2087 X_PRIMARY_SET_IND IN VARCHAR2,
2088 X_VOLUNTARY_END_IND IN VARCHAR2,
2089 X_AUTHORISED_PERSON_ID IN NUMBER,
2090 X_AUTHORISED_ON IN DATE,
2091 X_OVERRIDE_TITLE IN VARCHAR2,
2092 X_RQRMNTS_COMPLETE_IND IN VARCHAR2,
2093 X_RQRMNTS_COMPLETE_DT IN DATE,
2094 X_S_COMPLETED_SOURCE_TYPE IN VARCHAR2,
2095 X_CATALOG_CAL_TYPE IN VARCHAR2 ,
2096 X_CATALOG_SEQ_NUM IN NUMBER ,
2097 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
2098 X_ATTRIBUTE1 IN VARCHAR2 ,
2099 X_ATTRIBUTE2 IN VARCHAR2 ,
2100 X_ATTRIBUTE3 IN VARCHAR2 ,
2101 X_ATTRIBUTE4 IN VARCHAR2 ,
2102 X_ATTRIBUTE5 IN VARCHAR2 ,
2103 X_ATTRIBUTE6 IN VARCHAR2 ,
2104 X_ATTRIBUTE7 IN VARCHAR2 ,
2105 X_ATTRIBUTE8 IN VARCHAR2 ,
2106 X_ATTRIBUTE9 IN VARCHAR2 ,
2107 X_ATTRIBUTE10 IN VARCHAR2 ,
2108 X_ATTRIBUTE11 IN VARCHAR2 ,
2109 X_ATTRIBUTE12 IN VARCHAR2 ,
2110 X_ATTRIBUTE13 IN VARCHAR2 ,
2111 X_ATTRIBUTE14 IN VARCHAR2 ,
2112 X_ATTRIBUTE15 IN VARCHAR2 ,
2113 X_ATTRIBUTE16 IN VARCHAR2 ,
2114 X_ATTRIBUTE17 IN VARCHAR2 ,
2115 X_ATTRIBUTE18 IN VARCHAR2 ,
2116 X_ATTRIBUTE19 IN VARCHAR2 ,
2117 X_ATTRIBUTE20 IN VARCHAR2 ,
2118 X_MODE IN VARCHAR2
2119 ) AS
2120 CURSOR c1 IS SELECT ROWID FROM IGS_AS_SU_SETATMPT
2121 WHERE PERSON_ID = X_PERSON_ID
2122 AND COURSE_CD = X_COURSE_CD
2123 AND UNIT_SET_CD = X_UNIT_SET_CD
2124 AND SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
2125 ;
2126 BEGIN
2127 OPEN c1;
2128 FETCH c1 INTO X_ROWID;
2129 IF (c1%NOTFOUND) THEN
2130 CLOSE c1;
2131 INSERT_ROW (
2132 X_ROWID,
2133 X_PERSON_ID,
2134 X_COURSE_CD,
2135 X_UNIT_SET_CD,
2136 X_SEQUENCE_NUMBER,
2137 X_US_VERSION_NUMBER,
2138 X_SELECTION_DT,
2139 X_STUDENT_CONFIRMED_IND,
2140 X_END_DT,
2141 X_PARENT_UNIT_SET_CD,
2142 X_PARENT_SEQUENCE_NUMBER,
2143 X_PRIMARY_SET_IND,
2144 X_VOLUNTARY_END_IND,
2145 X_AUTHORISED_PERSON_ID,
2146 X_AUTHORISED_ON,
2147 X_OVERRIDE_TITLE,
2148 X_RQRMNTS_COMPLETE_IND,
2149 X_RQRMNTS_COMPLETE_DT,
2150 X_S_COMPLETED_SOURCE_TYPE,
2151 X_CATALOG_CAL_TYPE,
2152 X_CATALOG_SEQ_NUM,
2153 X_ATTRIBUTE_CATEGORY,
2154 X_ATTRIBUTE1,
2155 X_ATTRIBUTE2,
2156 X_ATTRIBUTE3,
2157 X_ATTRIBUTE4,
2158 X_ATTRIBUTE5,
2159 X_ATTRIBUTE6,
2160 X_ATTRIBUTE7,
2161 X_ATTRIBUTE8,
2162 X_ATTRIBUTE9,
2163 X_ATTRIBUTE10,
2164 X_ATTRIBUTE11,
2165 X_ATTRIBUTE12,
2166 X_ATTRIBUTE13,
2167 X_ATTRIBUTE14,
2168 X_ATTRIBUTE15,
2169 X_ATTRIBUTE16,
2170 X_ATTRIBUTE17,
2171 X_ATTRIBUTE18,
2172 X_ATTRIBUTE19,
2173 X_ATTRIBUTE20,
2174 X_MODE);
2175 RETURN;
2176 END IF;
2177 CLOSE c1;
2178 UPDATE_ROW (
2179 X_ROWID,
2180 X_PERSON_ID,
2181 X_COURSE_CD,
2182 X_UNIT_SET_CD,
2183 X_SEQUENCE_NUMBER,
2184 X_US_VERSION_NUMBER,
2185 X_SELECTION_DT,
2186 X_STUDENT_CONFIRMED_IND,
2187 X_END_DT,
2188 X_PARENT_UNIT_SET_CD,
2189 X_PARENT_SEQUENCE_NUMBER,
2190 X_PRIMARY_SET_IND,
2191 X_VOLUNTARY_END_IND,
2192 X_AUTHORISED_PERSON_ID,
2193 X_AUTHORISED_ON,
2194 X_OVERRIDE_TITLE,
2195 X_RQRMNTS_COMPLETE_IND,
2196 X_RQRMNTS_COMPLETE_DT,
2197 X_S_COMPLETED_SOURCE_TYPE,
2198 X_CATALOG_CAL_TYPE,
2199 X_CATALOG_SEQ_NUM,
2200 X_ATTRIBUTE_CATEGORY,
2201 X_ATTRIBUTE1,
2202 X_ATTRIBUTE2,
2203 X_ATTRIBUTE3,
2204 X_ATTRIBUTE4,
2205 X_ATTRIBUTE5,
2206 X_ATTRIBUTE6,
2207 X_ATTRIBUTE7,
2208 X_ATTRIBUTE8,
2209 X_ATTRIBUTE9,
2210 X_ATTRIBUTE10,
2211 X_ATTRIBUTE11,
2212 X_ATTRIBUTE12,
2213 X_ATTRIBUTE13,
2214 X_ATTRIBUTE14,
2215 X_ATTRIBUTE15,
2216 X_ATTRIBUTE16,
2217 X_ATTRIBUTE17,
2218 X_ATTRIBUTE18,
2219 X_ATTRIBUTE19,
2220 X_ATTRIBUTE20,
2221 X_MODE);
2222 END ADD_ROW;
2223 PROCEDURE DELETE_ROW (
2224 X_ROWID IN VARCHAR2,
2225 x_mode IN VARCHAR2) AS
2226 BEGIN
2227 --
2228 Before_DML(
2229 p_action => 'DELETE',
2230 x_rowid => X_ROWID
2231 );
2232 --
2233 IF (x_mode = 'S') THEN
2234 igs_sc_gen_001.set_ctx('R');
2235 END IF;
2236 DELETE FROM IGS_AS_SU_SETATMPT
2237 WHERE ROWID = X_ROWID;
2238 IF (SQL%NOTFOUND) THEN
2239 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
2240 igs_ge_msg_stack.add;
2241 igs_sc_gen_001.unset_ctx('R');
2242 app_exception.raise_exception;
2243 END IF;
2244 IF (x_mode = 'S') THEN
2245 igs_sc_gen_001.unset_ctx('R');
2246 END IF;
2247
2248 --
2249 After_DML(
2250 p_action => 'DELETE',
2251 x_rowid => X_ROWID
2252 );
2253 --
2254 END DELETE_ROW;
2255 END IGS_AS_SU_SETATMPT_PKG;
[Home] [Help]