[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_SU_ATMPT_PAT_PKG
Source
1 package body IGS_AS_SU_ATMPT_PAT_PKG AS
2 /* $Header: IGSDI07B.pls 120.0 2005/07/05 11:43:45 appldev noship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 24-AUG-2001 Bug No. 1956374 .The reference to igs_as_val_uai.genp_val_sdtt_sess
7 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
8 --svanukur 29-APR-03 Added new column uoo_id, redefined primary Key from
9 -- (person_id,course_cd,unit_cd,cal_type,ci_sequence_number,ass_pattern_id,creation_dt) to
10 -- (person_id,course_cd,uoo_id,ass_pattern_id,creation_dt)redefined FK
11 -- to (PERSON_ID, COURSE_CD,UOO_ID)as part of MUS build, # 2829262
12 -------------------------------------------------------------------------------------------
13 l_rowid VARCHAR2(25);
14 old_references IGS_AS_SU_ATMPT_PAT%RowType;
15 new_references IGS_AS_SU_ATMPT_PAT%RowType;
16 PROCEDURE Set_Column_Values (
17 p_action IN VARCHAR2,
18 x_rowid IN VARCHAR2 DEFAULT NULL,
19 x_person_id IN NUMBER DEFAULT NULL,
20 x_course_cd IN VARCHAR2 DEFAULT NULL,
21 x_unit_cd IN VARCHAR2 DEFAULT NULL,
22 x_cal_type IN VARCHAR2 DEFAULT NULL,
23 x_ci_sequence_number IN NUMBER DEFAULT NULL,
24 x_ass_pattern_id IN NUMBER DEFAULT NULL,
25 x_creation_dt IN DATE DEFAULT NULL,
26 x_s_default_ind IN VARCHAR2 DEFAULT NULL,
27 x_logical_delete_dt IN DATE DEFAULT NULL,
28 x_creation_date IN DATE DEFAULT NULL,
29 x_created_by IN NUMBER DEFAULT NULL,
30 x_last_update_date IN DATE DEFAULT NULL,
31 x_last_updated_by IN NUMBER DEFAULT NULL,
32 x_last_update_login IN NUMBER DEFAULT NULL,
33 x_uoo_id IN NUMBER DEFAULT NULL
34 ) AS
35 CURSOR cur_old_ref_values IS
36 SELECT *
37 FROM IGS_AS_SU_ATMPT_PAT
38 WHERE rowid = x_rowid;
39 BEGIN
40 l_rowid := x_rowid;
41 -- Code for setting the Old and New Reference Values.
42 -- Populate Old Values.
43 Open cur_old_ref_values;
44 Fetch cur_old_ref_values INTO old_references;
45 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
46 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
47 IGS_GE_MSG_STACK.ADD;
48 Close cur_old_ref_values;
49 APP_EXCEPTION.RAISE_EXCEPTION;
50
51 Return;
52 END IF;
53 Close cur_old_ref_values;
54 -- Populate New Values.
55 new_references.person_id := x_person_id;
56 new_references.course_cd := x_course_cd;
57 new_references.unit_cd := x_unit_cd;
58 new_references.cal_type:= x_cal_type;
59 new_references.ci_sequence_number := x_ci_sequence_number;
60 new_references.ass_pattern_id := x_ass_pattern_id;
61 new_references.creation_dt := x_creation_dt;
62 new_references.s_default_ind := x_s_default_ind;
63 new_references.logical_delete_dt := x_logical_delete_dt;
64 new_references.uoo_id := x_uoo_id;
65 IF (p_action = 'UPDATE') THEN
66 new_references.creation_date := old_references.creation_date;
67 new_references.created_by := old_references.created_by;
68 ELSE
69 new_references.creation_date := x_creation_date;
70 new_references.created_by := x_created_by;
71 END IF;
72 new_references.last_update_date := x_last_update_date;
73 new_references.last_updated_by := x_last_updated_by;
74 new_references.last_update_login := x_last_update_login;
75 END Set_Column_Values;
76
77 -------------------------------------------------------------------------------------------
78 --Change History:
79 --Who When What
80 --svanukur 29-APR-03 Passed uoo_id to IGS_AS_GEN_004.ASSP_INS_SUAAP_SUAAI as part of MUS build, # 2829262
81 -------------------------------------------------------------------------------------------
82
83
84 PROCEDURE BeforeRowInsertUpdate1(
85 p_inserting IN BOOLEAN DEFAULT FALSE,
86 p_updating IN BOOLEAN DEFAULT FALSE,
87 p_deleting IN BOOLEAN DEFAULT FALSE
88 ) AS
89 v_message_name VARCHAR2(30);
90 v_error_count NUMBER;
91 v_warning_count NUMBER;
92 v_version_number IGS_EN_SU_ATTEMPT.version_number%TYPE;
93 CURSOR c_sua (cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
94 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
95 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE) IS
96 SELECT version_number
97 FROM IGS_EN_SU_ATTEMPT
98 WHERE person_id = cp_person_id AND
99 course_cd = cp_course_cd AND
100 uoo_id = cp_uoo_id;
101 BEGIN
102 -- If p_inserting, validate that the assessment pattern is applicable to the
103 -- student IGS_PS_UNIT attempt and that the IGS_PS_UNIT attempt status is ENROLLED or
104 -- UNCONFIRMED.
105 IF p_inserting THEN
106 IF IGS_AS_VAL_SUAAP.assp_val_suaap_ins(new_references.person_id,
107 new_references.course_cd,
108 new_references.unit_cd,
109 new_references.cal_type,
110 new_references.ci_sequence_number,
111 new_references.ass_pattern_id,
112 v_message_name,
113 new_references.uoo_id) = FALSE THEN
114 FND_MESSAGE.SET_NAME('IGS',v_message_name);
115 IGS_GE_MSG_STACK.ADD;
116 APP_EXCEPTION.RAISE_EXCEPTION;
117
118 END IF;
119 OPEN c_sua( new_references.person_id,
120 new_references.course_cd,
121 new_references.uoo_id);
122 FETCH c_sua INTO v_version_number;
123 IF c_sua%NOTFOUND THEN
124 CLOSE c_sua;
125 RAISE NO_DATA_FOUND;
126 END IF;
127 CLOSE c_sua;
128 -- Check if IGS_AS_GEN_004.ASSP_INS_SUAAP_DFLT has not disabled the trigger
129 -- and the logical delete date is not set.
130 IF IGS_AS_VAL_SUAAP.GENP_VAL_SDTT_SESS('IGS_AS_SU_ATMPT_PAT') AND
131 (NVL(new_references.logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
132 = NVL(old_references.logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
133 -- Allocate unit_ass_pattern_items within the pattern to the student
134 -- (IGS_AS_SU_ATMPT_ITM).
135 IF IGS_AS_GEN_004.ASSP_INS_SUAAP_SUAAI(new_references.person_id,
136 new_references.course_cd,
137 new_references.unit_cd,
138 v_version_number,
139 new_references.cal_type,
140 new_references.ci_sequence_number,
141 new_references.ass_pattern_id,
142 new_references.creation_dt,
143 new_references.s_default_ind,
144 'Y', -- Called from database trigger.
145 v_message_name,
146 new_references.uoo_id) = FALSE THEN
147 FND_MESSAGE.SET_NAME('IGS',v_message_name); APP_EXCEPTION.RAISE_EXCEPTION;
148 IGS_GE_MSG_STACK.ADD;
149 END IF;
150 END IF;
151 END IF;
152 END BeforeRowInsertUpdate1;
153 -- Trigger description :-
154 -- "OSS_TST".trg_suaap_ar_iu
155 -- AFTER INSERT OR UPDATE
156 -- ON IGS_AS_SU_ATMPT_PAT
157 -- FOR EACH ROW
158 -------------------------------------------------------------------------------------------
159 --Change History:
160 --Who When What
161 --svanukur 29-APR-03 Passed uoo_id to IGS_AS_GEN_001.ASSP_DEL_SUAAP_SUAAI, IGS_AS_VAL_SUAAP.assp_val_suaap_actv as part of MUS build, # 2829262
162 -------------------------------------------------------------------------------------------
163 PROCEDURE AfterRowInsertUpdate2(
164 p_inserting IN BOOLEAN DEFAULT FALSE,
165 p_updating IN BOOLEAN DEFAULT FALSE,
166 p_deleting IN BOOLEAN DEFAULT FALSE
167 ) AS
168 v_message_name VARCHAR2(30);
169 v_error_count NUMBER(5);
170 v_warning_count NUMBER;
171 BEGIN
172 IF p_inserting THEN
173 IF IGS_AS_VAL_SUAAP.assp_val_suaap_actv( new_references.person_id,
174 new_references.course_cd,
175 new_references.unit_cd,
176 new_references.cal_type,
177 new_references.ci_sequence_number,
178 new_references.ass_pattern_id,
179 new_references.creation_dt,
180 v_message_name,
181 new_references.uoo_id) = FALSE THEN
182 FND_MESSAGE.SET_NAME('IGS',v_message_name);
183 IGS_GE_MSG_STACK.ADD;
184 APP_EXCEPTION.RAISE_EXCEPTION;
185 END IF;
186 -- Validate there is only one active instance of the pattern for the student..
187 -- Cannot call assp_val_suaap_activ because trigger will be mutating.
188 -- Save the rowid of the current row.
189 END IF;
190 IF p_updating AND
191 (NVL(new_references.logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
192 <> NVL(old_references.logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
193 -- If logically p_deleting the suaap record.
194 -- Check if IGS_AS_GEN_001.ASSP_DEL_SUAAP_DFLT has not disabled the trigger.
195 IF IGS_AS_VAL_SUAAP.GENP_VAL_SDTT_SESS('IGS_AS_SU_ATMPT_PAT') THEN
196 IF IGS_AS_GEN_001.ASSP_DEL_SUAAP_SUAAI( new_references.person_id,
197 new_references.course_cd,
198 new_references.unit_cd,
199 new_references.cal_type,
200 new_references.ci_sequence_number,
201 new_references.ass_pattern_id,
202 new_references.creation_dt,
203 NULL, -- p_ass_id
204 'Y', -- Called from database trigger.
205 NULL, -- p_s_log_type
206 NULL, -- p_key
207 NULL, -- p_ssl_key
208 v_error_count,
209 v_warning_count,
210 v_message_name,
211 new_references.uoo_id) = FALSE THEN
212 FND_MESSAGE.SET_NAME('IGS',v_message_name);
213 IGS_GE_MSG_STACK.ADD;
214 APP_EXCEPTION.RAISE_EXCEPTION;
215 END IF;
216 -- Logically delete unit_ass_pattern_items within the pattern to the student
217 -- (IGS_AS_SU_ATMPT_ITM).
218 -- Store away the rowid as the routine IGS_AS_GEN_001.ASSP_DEL_SUAAP_SUAAI will cause the
219 -- trigger to be mutating.
220 -- IGS_AS_VAL_SUAAP.genp_set_rowid(l_rowid);
221 END IF;
222 END IF;
223 END AfterRowInsertUpdate2;
224 -- Trigger description :-
225 -- "OSS_TST".trg_suaap_as_iu
226 -- AFTER INSERT OR UPDATE
227 -- ON IGS_AS_SU_ATMPT_PAT
228 PROCEDURE Check_Parent_Existance IS
229 BEGIN
230 IF (((old_references.person_id = new_references.person_id) AND
231 (old_references.course_cd = new_references.course_cd) AND
232 (old_references.uoo_id= new_references.uoo_id)) OR
233 ((new_references.person_id IS NULL) OR
234 (new_references.course_cd IS NULL) OR
235 (new_references.uoo_id IS NULL))) THEN
236 NULL;
237 ELSIF NOT IGS_EN_SU_ATTEMPT_PKG.Get_PK_For_Validation (
238 new_references.person_id,
239 new_references.course_cd,
240 new_references.uoo_id
241 )THEN
242 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
243 IGS_GE_MSG_STACK.ADD;
244 APP_EXCEPTION.RAISE_EXCEPTION;
245
246 END IF;
247 IF (((old_references.ass_pattern_id = new_references.ass_pattern_id)) OR
248 ((new_references.ass_pattern_id IS NULL))) THEN
249 NULL;
250 ELSIF NOT IGS_AS_UNTAS_PATTERN_PKG.Get_UK_For_Validation (
251 new_references.ass_pattern_id ) THEN
252 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
253 IGS_GE_MSG_STACK.ADD;
254 APP_EXCEPTION.RAISE_EXCEPTION;
255
256 END IF;
257 END Check_Parent_Existance;
258 -------------------------------------------------------------------------------------------
259 --Change History:
260 --Who When What
261 --svanukur 29-APR-03 changed the PK columns as part of MUS build, # 2829262
262 -------------------------------------------------------------------------------------------
263
264 FUNCTION Get_PK_For_Validation (
265 x_course_cd IN VARCHAR2,
266 x_person_id IN NUMBER,
267 x_ass_pattern_id IN NUMBER,
268 x_creation_dt IN DATE,
269 x_uoo_id IN NUMBER
270 ) RETURN BOOLEAN AS
271 CURSOR cur_rowid IS
272 SELECT rowid
273 FROM IGS_AS_SU_ATMPT_PAT
274 WHERE course_cd = x_course_cd
275 AND person_id = x_person_id
276 AND ass_pattern_id = x_ass_pattern_id
277 AND creation_dt = x_creation_dt
278 AND uoo_id = x_uoo_id
279 FOR UPDATE NOWAIT;
280 lv_rowid cur_rowid%RowType;
281 BEGIN
282 Open cur_rowid;
283 Fetch cur_rowid INTO lv_rowid;
284 IF (cur_rowid%FOUND) THEN
285 Close cur_rowid;
286 Return (TRUE);
287 ELSE
288 Close cur_rowid;
289 Return (FALSE);
290 END IF;
291 END Get_PK_For_Validation;
292 PROCEDURE GET_FK_IGS_EN_SU_ATTEMPT (
293 x_person_id IN NUMBER,
294 x_course_cd IN VARCHAR2,
295 x_uoo_id IN NUMBER
296 ) IS
297 CURSOR cur_rowid IS
298 SELECT rowid
299 FROM IGS_AS_SU_ATMPT_PAT
300 WHERE person_id = x_person_id
301 AND course_cd = x_course_cd
302 AND uoo_id = x_uoo_id;
303 lv_rowid cur_rowid%RowType;
304 BEGIN
305 Open cur_rowid;
306 Fetch cur_rowid INTO lv_rowid;
307 IF (cur_rowid%FOUND) THEN
308 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUAAP_SUA_FK');
309 IGS_GE_MSG_STACK.ADD;
310 Close cur_rowid;
311 APP_EXCEPTION.RAISE_EXCEPTION;
312
313 Return;
314 END IF;
315 Close cur_rowid;
316 END GET_FK_IGS_EN_SU_ATTEMPT;
317 PROCEDURE GET_UFK_IGS_AS_UNTAS_PATTERN (
321 SELECT rowid
318 x_ass_pattern_id IN NUMBER
319 ) IS
320 CURSOR cur_rowid IS
322 FROM IGS_AS_SU_ATMPT_PAT
323 WHERE ass_pattern_id = x_ass_pattern_id ;
324 lv_rowid cur_rowid%RowType;
325 BEGIN
326 Open cur_rowid;
327 Fetch cur_rowid INTO lv_rowid;
328 IF (cur_rowid%FOUND) THEN
329 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUAAP_SUA_FK');
330 IGS_GE_MSG_STACK.ADD;
331 Close cur_rowid;
332 APP_EXCEPTION.RAISE_EXCEPTION;
333
334 Return;
335 END IF;
336 Close cur_rowid;
337 END GET_UFK_IGS_AS_UNTAS_PATTERN;
338 PROCEDURE Before_DML (
339 p_action IN VARCHAR2,
340 x_rowid IN VARCHAR2 DEFAULT NULL,
341 x_person_id IN NUMBER DEFAULT NULL,
342 x_course_cd IN VARCHAR2 DEFAULT NULL,
343 x_unit_cd IN VARCHAR2 DEFAULT NULL,
344 x_cal_type IN VARCHAR2 DEFAULT NULL,
345 x_ci_sequence_number IN NUMBER DEFAULT NULL,
346 x_ass_pattern_id IN NUMBER DEFAULT NULL,
347 x_creation_dt IN DATE DEFAULT NULL,
348 x_s_default_ind IN VARCHAR2 DEFAULT NULL,
349 x_logical_delete_dt IN DATE DEFAULT NULL,
350 x_creation_date IN DATE DEFAULT NULL,
351 x_created_by IN NUMBER DEFAULT NULL,
352 x_last_update_date IN DATE DEFAULT NULL,
353 x_last_updated_by IN NUMBER DEFAULT NULL,
354 x_last_update_login IN NUMBER DEFAULT NULL,
355 x_uoo_id IN NUMBER DEFAULT NULL
356 ) AS
357 BEGIN
358 Set_Column_Values (
359 p_action,
360 x_rowid,
361 x_person_id,
362 x_course_cd,
363 x_unit_cd,
364 x_cal_type,
365 x_ci_sequence_number,
366 x_ass_pattern_id,
367 x_creation_dt,
368 x_s_default_ind,
369 x_logical_delete_dt,
370 x_creation_date,
371 x_created_by,
372 x_last_update_date,
373 x_last_updated_by,
374 x_last_update_login,
375 x_uoo_id
376 );
377 IF (p_action = 'INSERT') THEN
378 -- Call all the procedures related to Before Insert.
379 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
380 IF Get_PK_For_Validation (
381 NEW_REFERENCES.course_cd ,
382 NEW_REFERENCES.person_id ,
383 NEW_REFERENCES.ass_pattern_id ,
384 NEW_REFERENCES.creation_dt,
385 NEW_REFERENCES.uoo_id) THEN
386 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
387 IGS_GE_MSG_STACK.ADD;
388 APP_EXCEPTION.RAISE_EXCEPTION;
389 END IF;
390
391 Check_Constraints;
392
393
394 Check_Parent_Existance;
395 ELSIF (p_action = 'UPDATE') THEN
396 -- Call all the procedures related to Before Update.
397 BeforeRowInsertUpdate1 ( p_updating => TRUE );
398
399 Check_Constraints;
400 Check_Parent_Existance;
401
402 ELSIF (p_action = 'VALIDATE_INSERT') THEN
403 IF Get_PK_For_Validation (
404
405 new_references.course_cd ,
406 new_references.person_id ,
407 new_references.ass_pattern_id ,
408 new_references.creation_dt,
409 new_references.uoo_id) THEN
410 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
411 IGS_GE_MSG_STACK.ADD;
412 APP_EXCEPTION.RAISE_EXCEPTION;
413 END IF;
414
415 Check_Constraints;
416 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
417
418 Check_Constraints;
419
420
421
422 END IF;
423 END Before_DML;
424
425 procedure INSERT_ROW (
426 X_ROWID in out NOCOPY VARCHAR2,
427 X_PERSON_ID in NUMBER,
428 X_COURSE_CD in VARCHAR2,
429 X_UNIT_CD in VARCHAR2,
430 X_CAL_TYPE in VARCHAR2,
431 X_CI_SEQUENCE_NUMBER in NUMBER,
432 X_ASS_PATTERN_ID in NUMBER,
433 X_CREATION_DT in DATE,
434 X_S_DEFAULT_IND in VARCHAR2,
435 X_LOGICAL_DELETE_DT in DATE,
436 X_MODE in VARCHAR2 default 'R',
437 X_UOO_ID in NUMBER
438 ) AS
439 cursor C is select ROWID from IGS_AS_SU_ATMPT_PAT
440 where PERSON_ID = X_PERSON_ID
441 and COURSE_CD = X_COURSE_CD
442 and ASS_PATTERN_ID = X_ASS_PATTERN_ID
443 and CREATION_DT = X_CREATION_DT
444 and UOO_ID = X_UOO_ID;
445 X_LAST_UPDATE_DATE DATE;
446 X_LAST_UPDATED_BY NUMBER;
447 X_LAST_UPDATE_LOGIN NUMBER;
448 X_REQUEST_ID NUMBER;
449 X_PROGRAM_ID NUMBER;
450 X_PROGRAM_APPLICATION_ID NUMBER;
451 X_PROGRAM_UPDATE_DATE DATE;
452 begin
453 X_LAST_UPDATE_DATE := SYSDATE;
454 if(X_MODE = 'I') then
455 X_LAST_UPDATED_BY := 1;
456 X_LAST_UPDATE_LOGIN := 0;
457 elsif (X_MODE = 'R') then
458 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
459 if X_LAST_UPDATED_BY is NULL then
460 X_LAST_UPDATED_BY := -1;
461 end if;
462 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
463 if X_LAST_UPDATE_LOGIN is NULL then
464 X_LAST_UPDATE_LOGIN := -1;
465 end if;
466 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
467 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
468 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
469 if (X_REQUEST_ID = -1) then
470 X_REQUEST_ID := NULL;
471 X_PROGRAM_ID := NULL;
472 X_PROGRAM_APPLICATION_ID := NULL;
473 X_PROGRAM_UPDATE_DATE := NULL;
474 else
475 X_PROGRAM_UPDATE_DATE := SYSDATE;
476 end if;
477 else
478 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
479 IGS_GE_MSG_STACK.ADD;
480 APP_EXCEPTION.RAISE_EXCEPTION;
484 x_rowid=>X_ROWID,
481 end if;
482 Before_DML(
483 p_action=>'INSERT',
485 x_ass_pattern_id=>X_ASS_PATTERN_ID,
486 x_cal_type=>X_CAL_TYPE,
487 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
488 x_course_cd=>X_COURSE_CD,
489 x_creation_dt=>X_CREATION_DT,
490 x_logical_delete_dt=>X_LOGICAL_DELETE_DT,
491 x_person_id=>X_PERSON_ID,
492 x_s_default_ind=> NVL(X_S_DEFAULT_IND,'N'),
493 x_unit_cd=>X_UNIT_CD,
494 x_creation_date=>X_LAST_UPDATE_DATE,
495 x_created_by=>X_LAST_UPDATED_BY,
496 x_last_update_date=>X_LAST_UPDATE_DATE,
497 x_last_updated_by=>X_LAST_UPDATED_BY,
498 x_last_update_login=>X_LAST_UPDATE_LOGIN,
499 x_uoo_id=>X_UOO_ID
500 );
501 insert into IGS_AS_SU_ATMPT_PAT (
502 PERSON_ID,
503 COURSE_CD,
504 UNIT_CD,
505 CAL_TYPE,
506 CI_SEQUENCE_NUMBER,
507 ASS_PATTERN_ID,
508 CREATION_DT,
509 S_DEFAULT_IND,
510 LOGICAL_DELETE_DT,
511 CREATION_DATE,
512 CREATED_BY,
513 LAST_UPDATE_DATE,
514 LAST_UPDATED_BY,
515 LAST_UPDATE_LOGIN,
516 REQUEST_ID,
517 PROGRAM_ID,
518 PROGRAM_APPLICATION_ID,
519 PROGRAM_UPDATE_DATE,
520 UOO_ID
521 ) values (
522 NEW_REFERENCES.PERSON_ID,
523 NEW_REFERENCES.COURSE_CD,
524 NEW_REFERENCES.UNIT_CD,
525 NEW_REFERENCES.CAL_TYPE,
526 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
527 NEW_REFERENCES.ASS_PATTERN_ID,
528 NEW_REFERENCES.CREATION_DT,
529 NEW_REFERENCES.S_DEFAULT_IND,
530 NEW_REFERENCES.LOGICAL_DELETE_DT,
531 X_LAST_UPDATE_DATE,
532 X_LAST_UPDATED_BY,
533 X_LAST_UPDATE_DATE,
534 X_LAST_UPDATED_BY,
535 X_LAST_UPDATE_LOGIN,
536 X_REQUEST_ID,
537 X_PROGRAM_ID,
538 X_PROGRAM_APPLICATION_ID,
539 X_PROGRAM_UPDATE_DATE,
540 NEW_REFERENCES.UOO_ID
541 );
542 open c;
543 fetch c into X_ROWID;
544 if (c%notfound) then
545 close c;
546 raise no_data_found;
547 end if;
548 close c;
549
550 end INSERT_ROW;
551 procedure LOCK_ROW (
552 X_ROWID in VARCHAR2,
553 X_PERSON_ID in NUMBER,
554 X_COURSE_CD in VARCHAR2,
555 X_UNIT_CD in VARCHAR2,
556 X_CAL_TYPE in VARCHAR2,
557 X_CI_SEQUENCE_NUMBER in NUMBER,
558 X_ASS_PATTERN_ID in NUMBER,
559 X_CREATION_DT in DATE,
560 X_S_DEFAULT_IND in VARCHAR2,
561 X_LOGICAL_DELETE_DT in DATE,
562 X_UOO_ID in NUMBER
563 ) AS
564 cursor c1 is select
565 S_DEFAULT_IND,
566 LOGICAL_DELETE_DT
567 from IGS_AS_SU_ATMPT_PAT
568 where ROWID = X_ROWID for update nowait;
569 tlinfo c1%rowtype;
570 begin
571 open c1;
572 fetch c1 into tlinfo;
573 if (c1%notfound) then
574 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
575 IGS_GE_MSG_STACK.ADD;
576 APP_EXCEPTION.RAISE_EXCEPTION;
577 close c1;
578 return;
579 end if;
580 close c1;
581 if ( (tlinfo.S_DEFAULT_IND = X_S_DEFAULT_IND)
582 AND ((tlinfo.LOGICAL_DELETE_DT = X_LOGICAL_DELETE_DT)
583 OR ((tlinfo.LOGICAL_DELETE_DT is null)
584 AND (X_LOGICAL_DELETE_DT is null)))
585 ) then
586 null;
587 else
588 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
589 IGS_GE_MSG_STACK.ADD;
590 APP_EXCEPTION.RAISE_EXCEPTION;
591 end if;
592 return;
593 end LOCK_ROW;
594 procedure UPDATE_ROW (
595 X_ROWID in VARCHAR2,
596 X_PERSON_ID in NUMBER,
597 X_COURSE_CD in VARCHAR2,
598 X_UNIT_CD in VARCHAR2,
599 X_CAL_TYPE in VARCHAR2,
600 X_CI_SEQUENCE_NUMBER in NUMBER,
601 X_ASS_PATTERN_ID in NUMBER,
602 X_CREATION_DT in DATE,
603 X_S_DEFAULT_IND in VARCHAR2,
604 X_LOGICAL_DELETE_DT in DATE,
605 X_MODE in VARCHAR2 default 'R',
606 X_UOO_ID in NUMBER
607 ) AS
608 X_LAST_UPDATE_DATE DATE;
609 X_LAST_UPDATED_BY NUMBER;
610 X_LAST_UPDATE_LOGIN NUMBER;
611 X_REQUEST_ID NUMBER;
612 X_PROGRAM_ID NUMBER;
613 X_PROGRAM_APPLICATION_ID NUMBER;
614 X_PROGRAM_UPDATE_DATE DATE;
615 begin
616 X_LAST_UPDATE_DATE := SYSDATE;
617 if(X_MODE = 'I') then
618 X_LAST_UPDATED_BY := 1;
619 X_LAST_UPDATE_LOGIN := 0;
620 elsif (X_MODE = 'R') then
621 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
622 if X_LAST_UPDATED_BY is NULL then
623 X_LAST_UPDATED_BY := -1;
624 end if;
625 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
626 if X_LAST_UPDATE_LOGIN is NULL then
627 X_LAST_UPDATE_LOGIN := -1;
628 end if;
629 else
630 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
631 IGS_GE_MSG_STACK.ADD;
632 APP_EXCEPTION.RAISE_EXCEPTION;
633 end if;
634 Before_DML(
635 p_action=>'UPDATE',
636 x_rowid=>X_ROWID,
637 x_ass_pattern_id=>X_ASS_PATTERN_ID,
638 x_cal_type=>X_CAL_TYPE,
639 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
640 x_course_cd=>X_COURSE_CD,
641 x_creation_dt=>X_CREATION_DT,
642 x_logical_delete_dt=>X_LOGICAL_DELETE_DT,
643 x_person_id=>X_PERSON_ID,
644 x_s_default_ind=>X_S_DEFAULT_IND,
645 x_unit_cd=>X_UNIT_CD,
646 x_creation_date=>X_LAST_UPDATE_DATE,
647 x_created_by=>X_LAST_UPDATED_BY,
648 x_last_update_date=>X_LAST_UPDATE_DATE,
649 x_last_updated_by=>X_LAST_UPDATED_BY,
650 x_last_update_login=>X_LAST_UPDATE_LOGIN,
651 x_uoo_id=>X_UOO_ID
652 );
653 if (X_MODE = 'R') then
654 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
655 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
656 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
657 if (X_REQUEST_ID = -1) then
658 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
659 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
660 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
661 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
662 else
663 X_PROGRAM_UPDATE_DATE := SYSDATE;
664 end if;
665 end if;
666 update IGS_AS_SU_ATMPT_PAT set
667 S_DEFAULT_IND = NEW_REFERENCES.S_DEFAULT_IND,
668 LOGICAL_DELETE_DT = NEW_REFERENCES.LOGICAL_DELETE_DT,
669 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
670 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
671 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
672 REQUEST_ID = X_REQUEST_ID,
673 PROGRAM_ID = X_PROGRAM_ID,
674 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
675 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
676 where ROWID = X_ROWID;
677 if (sql%notfound) then
678 raise no_data_found;
679 end if;
680
681 end UPDATE_ROW;
682 procedure ADD_ROW (
683 X_ROWID in out NOCOPY VARCHAR2,
684 X_PERSON_ID in NUMBER,
685 X_COURSE_CD in VARCHAR2,
686 X_UNIT_CD in VARCHAR2,
687 X_CAL_TYPE in VARCHAR2,
688 X_CI_SEQUENCE_NUMBER in NUMBER,
689 X_ASS_PATTERN_ID in NUMBER,
690 X_CREATION_DT in DATE,
691 X_S_DEFAULT_IND in VARCHAR2,
692 X_LOGICAL_DELETE_DT in DATE,
693 X_MODE in VARCHAR2 default 'R',
694 X_UOO_ID in NUMBER
695 ) AS
696 cursor c1 is select rowid from IGS_AS_SU_ATMPT_PAT
697 where PERSON_ID = X_PERSON_ID
698 and COURSE_CD = X_COURSE_CD
699 and ASS_PATTERN_ID = X_ASS_PATTERN_ID
700 and CREATION_DT = X_CREATION_DT
701 and UOO_ID = X_UOO_ID
702 ;
703 begin
704 open c1;
705 fetch c1 into X_ROWID;
706 if (c1%notfound) then
707 close c1;
708 INSERT_ROW (
709 X_ROWID,
710 X_PERSON_ID,
711 X_COURSE_CD,
712 X_UNIT_CD,
713 X_CAL_TYPE,
714 X_CI_SEQUENCE_NUMBER,
715 X_ASS_PATTERN_ID,
716 X_CREATION_DT,
717 X_S_DEFAULT_IND,
718 X_LOGICAL_DELETE_DT,
719 X_MODE,
720 X_UOO_ID);
721 return;
722 end if;
723 close c1;
724 UPDATE_ROW (
725 X_ROWID,
726 X_PERSON_ID,
727 X_COURSE_CD,
728 X_UNIT_CD,
729 X_CAL_TYPE,
730 X_CI_SEQUENCE_NUMBER,
731 X_ASS_PATTERN_ID,
732 X_CREATION_DT,
733 X_S_DEFAULT_IND,
734 X_LOGICAL_DELETE_DT,
735 X_MODE,
736 X_UOO_ID);
737 end ADD_ROW;
738 procedure DELETE_ROW (
739 X_ROWID in VARCHAR2) AS
740 begin
741 Before_DML(
742 p_action => 'DELETE',
743 x_rowid => X_ROWID
744 );
745 delete from IGS_AS_SU_ATMPT_PAT
746 where ROWID = X_ROWID;
747 if (sql%notfound) then
748 raise no_data_found;
749 end if;
750
751 end DELETE_ROW;
752 PROCEDURE Check_Constraints (
753 Column_Name IN VARCHAR2 DEFAULT NULL,
754 Column_Value IN VARCHAR2 DEFAULT NULL
755 )
756 AS
757 BEGIN
758 IF column_name is null then
759 NULL;
760 ELSIF upper(Column_name) = 'CI_SEQUENCE_NUMBER' then
761 new_references.CI_SEQUENCE_NUMBER := igs_ge_number.to_num(column_value);
762
763 ELSIF upper(Column_name) = 'S_DEFAULT_IND' then
764 new_references.S_DEFAULT_IND := column_value;
765 END IF;
766
767 IF upper(column_name) = 'CI_SEQUENCE_NUMBER ' OR
768 column_name is null Then
769 IF new_references.ci_sequence_number < 1 OR new_references.ci_sequence_number > 999999 Then
770 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
771 IGS_GE_MSG_STACK.ADD;
772 APP_EXCEPTION.RAISE_EXCEPTION;
773 END IF;
774 END IF;
775
776
777 IF upper(column_name) = 'S_DEFAULT_IND' OR
778 column_name is null Then
779 IF new_references.S_DEFAULT_IND NOT IN ('Y','N') Then
780 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
781 IGS_GE_MSG_STACK.ADD;
782 APP_EXCEPTION.RAISE_EXCEPTION;
783 END IF;
784 END IF;
785
786
787 END Check_Constraints;
788 end IGS_AS_SU_ATMPT_PAT_PKG;