[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_SDT_PR_RU_CK_PKG
Source
1 package body IGS_PR_SDT_PR_RU_CK_PKG AS
2 /* $Header: IGSQI17B.pls 115.7 2002/11/29 03:18:19 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_PR_SDT_PR_RU_CK_ALL%RowType;
5 new_references IGS_PR_SDT_PR_RU_CK_ALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_person_id IN NUMBER DEFAULT NULL,
11 x_course_cd IN VARCHAR2 DEFAULT NULL,
12 x_prg_cal_type IN VARCHAR2 DEFAULT NULL,
13 x_prg_ci_sequence_number IN NUMBER DEFAULT NULL,
14 x_rule_check_dt IN DATE DEFAULT NULL,
15 x_progression_rule_cat IN VARCHAR2 DEFAULT NULL,
16 x_pra_sequence_number IN NUMBER DEFAULT NULL,
17 x_passed_ind IN VARCHAR2 DEFAULT NULL,
18 x_rule_message_text IN VARCHAR2 DEFAULT NULL,
19 x_creation_date IN DATE DEFAULT NULL,
20 x_created_by IN NUMBER DEFAULT NULL,
21 x_last_update_date IN DATE DEFAULT NULL,
22 x_last_updated_by IN NUMBER DEFAULT NULL,
23 x_last_update_login IN NUMBER DEFAULT NULL,
24 x_org_id IN NUMBER DEFAULT NULL
25 ) AS
26
27 CURSOR cur_old_ref_values IS
28 SELECT *
29 FROM IGS_PR_SDT_PR_RU_CK_ALL
30 WHERE rowid = x_rowid;
31
32 BEGIN
33
34 l_rowid := x_rowid;
35
36 -- Code for setting the Old and New Reference Values.
37 -- Populate Old Values.
38 Open cur_old_ref_values;
39 Fetch cur_old_ref_values INTO old_references;
40 IF (cur_old_ref_values%NOTFOUND) AND (p_action not in ('INSERT','VALIDATE_INSERT')) THEN
41 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
42 IGS_GE_MSG_STACK.ADD;
43 Close cur_old_ref_values;
44 App_Exception.Raise_Exception;
45
46 Return;
47 END IF;
48 Close cur_old_ref_values;
49
50 -- Populate New Values.
51 new_references.person_id := x_person_id;
52 new_references.course_cd := x_course_cd;
53 new_references.prg_cal_type := x_prg_cal_type;
54 new_references.prg_ci_sequence_number := x_prg_ci_sequence_number;
55 new_references.rule_check_dt := x_rule_check_dt;
56 new_references.progression_rule_cat := x_progression_rule_cat;
57 new_references.pra_sequence_number := x_pra_sequence_number;
58 new_references.passed_ind := x_passed_ind;
59 new_references.rule_message_text := x_rule_message_text;
60 IF (p_action = 'UPDATE') THEN
61 new_references.creation_date := old_references.creation_date;
62 new_references.created_by := old_references.created_by;
63 ELSE
64 new_references.creation_date := x_creation_date;
65 new_references.created_by := x_created_by;
66 END IF;
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70 new_references.org_id := x_org_id;
71 END Set_Column_Values;
72
73 PROCEDURE Check_Parent_Existance AS
74 BEGIN
75
76 IF (((old_references.progression_rule_cat = new_references.progression_rule_cat) AND
77 (old_references.pra_sequence_number = new_references.pra_sequence_number)) OR
78 ((new_references.progression_rule_cat IS NULL) OR
79 (new_references.pra_sequence_number IS NULL))) THEN
80 NULL;
81 ELSE
82 IF NOT IGS_PR_RU_APPL_PKG.Get_PK_For_Validation (
83 new_references.progression_rule_cat,
84 new_references.pra_sequence_number
85 )THEN
86 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
87 IGS_GE_MSG_STACK.ADD;
88 App_Exception.Raise_Exception;
89
90 END IF;
91
92 END IF;
93
94 IF (((old_references.person_id = new_references.person_id) AND
95 (old_references.course_cd = new_references.course_cd) AND
96 (old_references.prg_cal_type = new_references.prg_cal_type) AND
97 (old_references.prg_ci_sequence_number = new_references.prg_ci_sequence_number) AND
98 (old_references.rule_check_dt = new_references.rule_check_dt)) OR
99 ((new_references.person_id IS NULL) OR
100 (new_references.course_cd IS NULL) OR
101 (new_references.prg_cal_type IS NULL) OR
102 (new_references.prg_ci_sequence_number IS NULL) OR
103 (new_references.rule_check_dt IS NULL))) THEN
104 NULL;
105 ELSE
106 IF NOT IGS_PR_STDNT_PR_CK_PKG.Get_PK_For_Validation (
107 new_references.person_id,
108 new_references.course_cd,
109 new_references.prg_cal_type,
110 new_references.prg_ci_sequence_number,
111 new_references.rule_check_dt
112 )THEN
113 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
114 IGS_GE_MSG_STACK.ADD;
115 App_Exception.Raise_Exception;
116
117 END IF;
118
119 END IF;
120
121 END Check_Parent_Existance;
122
123 PROCEDURE Check_Child_Existance AS
124 BEGIN
125
126 IGS_PR_STDNT_PR_OU_PKG.GET_FK_IGS_PR_SDT_PR_RU_CK (
127 old_references.person_id,
128 old_references.course_cd,
129 old_references.prg_cal_type,
130 old_references.prg_ci_sequence_number,
131 old_references.progression_rule_cat,
132 old_references.pra_sequence_number,
133 old_references.rule_check_dt
134 );
135
136 END Check_Child_Existance;
137
138 FUNCTION Get_PK_For_Validation (
139 x_person_id IN NUMBER,
140 x_course_cd IN VARCHAR2,
141 x_prg_cal_type IN VARCHAR2,
142 x_prg_ci_sequence_number IN NUMBER,
143 x_progression_rule_cat IN VARCHAR2,
144 x_pra_sequence_number IN NUMBER,
145 x_rule_check_dt IN DATE
146 ) RETURN BOOLEAN AS
147
148 CURSOR cur_rowid IS
149 SELECT rowid
150 FROM IGS_PR_SDT_PR_RU_CK_ALL
151 WHERE person_id = x_person_id
152 AND course_cd = x_course_cd
153 AND prg_cal_type = x_prg_cal_type
154 AND prg_ci_sequence_number = x_prg_ci_sequence_number
155 AND progression_rule_cat = x_progression_rule_cat
156 AND pra_sequence_number = x_pra_sequence_number
157 AND rule_check_dt = x_rule_check_dt
158 FOR UPDATE NOWAIT;
159
160 lv_rowid cur_rowid%RowType;
161
162 BEGIN
163
164 Open cur_rowid;
165 Fetch cur_rowid INTO lv_rowid;
166 IF (cur_rowid%FOUND) THEN
167 Close cur_rowid;
168 Return (TRUE);
169 ELSE
170 Close cur_rowid;
171 Return (FALSE);
172 END IF;
173
174 END Get_PK_For_Validation;
175
176 PROCEDURE GET_FK_IGS_PR_RU_APPL (
177 x_progression_rule_cat IN VARCHAR2,
178 x_sequence_number IN NUMBER
179 ) AS
180
181 CURSOR cur_rowid IS
182 SELECT rowid
183 FROM IGS_PR_SDT_PR_RU_CK_ALL
184 WHERE progression_rule_cat = x_progression_rule_cat
185 AND pra_sequence_number = x_sequence_number ;
186
187 lv_rowid cur_rowid%RowType;
188
189 BEGIN
190
191 Open cur_rowid;
192 Fetch cur_rowid INTO lv_rowid;
193 IF (cur_rowid%FOUND) THEN
194 Fnd_Message.Set_Name ('IGS', 'IGS_PR_SPRC_PRA_FK');
195 IGS_GE_MSG_STACK.ADD;
196 Close cur_rowid;
197 App_Exception.Raise_Exception;
198
199 Return;
200 END IF;
201 Close cur_rowid;
202
203 END GET_FK_IGS_PR_RU_APPL;
204
205 PROCEDURE GET_FK_IGS_PR_STDNT_PR_CK (
206 x_person_id IN NUMBER,
207 x_course_cd IN VARCHAR2,
208 x_prg_cal_type IN VARCHAR2,
209 x_prg_ci_sequence_number IN NUMBER,
210 x_rule_check_dt IN DATE
211 ) AS
212
213 CURSOR cur_rowid IS
214 SELECT rowid
215 FROM IGS_PR_SDT_PR_RU_CK_ALL
216 WHERE person_id = x_person_id
217 AND course_cd = x_course_cd
218 AND prg_cal_type = x_prg_cal_type
219 AND prg_ci_sequence_number = x_prg_ci_sequence_number
220 AND rule_check_dt = x_rule_check_dt ;
221
222 lv_rowid cur_rowid%RowType;
223
224 BEGIN
225
226 Open cur_rowid;
227 Fetch cur_rowid INTO lv_rowid;
228 IF (cur_rowid%FOUND) THEN
229 Fnd_Message.Set_Name ('IGS', 'IGS_PR_SPRC_SPCHK_FK');
230 IGS_GE_MSG_STACK.ADD;
231 Close cur_rowid;
232 App_Exception.Raise_Exception;
233
234 Return;
235 END IF;
236 Close cur_rowid;
237
238 END GET_FK_IGS_PR_STDNT_PR_CK;
239
240 PROCEDURE Before_DML (
241 p_action IN VARCHAR2,
242 x_rowid IN VARCHAR2 DEFAULT NULL,
243 x_person_id IN NUMBER DEFAULT NULL,
244 x_course_cd IN VARCHAR2 DEFAULT NULL,
245 x_prg_cal_type IN VARCHAR2 DEFAULT NULL,
246 x_prg_ci_sequence_number IN NUMBER DEFAULT NULL,
247 x_rule_check_dt IN DATE DEFAULT NULL,
248 x_progression_rule_cat IN VARCHAR2 DEFAULT NULL,
249 x_pra_sequence_number IN NUMBER DEFAULT NULL,
250 x_rule_message_text IN VARCHAR2 DEFAULT NULL,
251 x_passed_ind IN VARCHAR2 DEFAULT NULL,
252 x_creation_date IN DATE DEFAULT NULL,
253 x_created_by IN NUMBER DEFAULT NULL,
254 x_last_update_date IN DATE DEFAULT NULL,
255 x_last_updated_by IN NUMBER DEFAULT NULL,
256 x_last_update_login IN NUMBER DEFAULT NULL,
257 x_org_id IN NUMBER DEFAULT NULL
258
259 ) AS
260 BEGIN
261
262 Set_Column_Values (
263 p_action,
264 x_rowid,
265 x_person_id,
266 x_course_cd,
267 x_prg_cal_type,
268 x_prg_ci_sequence_number,
269 x_rule_check_dt,
270 x_progression_rule_cat,
271 x_pra_sequence_number,
272 x_passed_ind,
273 x_rule_message_text,
274 x_creation_date,
275 x_created_by,
276 x_last_update_date,
277 x_last_updated_by,
278 x_last_update_login,
279 x_org_id
280 );
281
282 IF (p_action = 'INSERT') THEN
283 -- Call all the procedures related to Before Insert.
284 Check_Parent_Existance;
285 IF GET_PK_FOR_VALIDATION(
286 new_references.person_id,
287 new_references.course_cd,
288 new_references.prg_cal_type,
289 new_references.prg_ci_sequence_number,
290 new_references.progression_rule_cat,
291 new_references.pra_sequence_number,
292 new_references.rule_check_dt) THEN
293 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
294 IGS_GE_MSG_STACK.ADD;
295 App_Exception.Raise_Exception;
296 END IF;
297 CHECK_CONSTRAINTS;
298
299 ELSIF (p_action = 'UPDATE') THEN
300 -- Call all the procedures related to Before Update.
301 Check_Parent_Existance;
302 CHECK_CONSTRAINTS;
303
304 ELSIF (p_action = 'DELETE') THEN
305 -- Call all the procedures related to Before Delete.
306 Check_Child_Existance;
307
308 ELSIF (p_action = 'VALIDATE_INSERT') THEN
309 IF GET_PK_FOR_VALIDATION(
310 new_references.person_id,
311 new_references.course_cd,
312 new_references.prg_cal_type,
313 new_references.prg_ci_sequence_number,
314 new_references.progression_rule_cat,
315 new_references.pra_sequence_number,
316 new_references.rule_check_dt) THEN
317 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
318 IGS_GE_MSG_STACK.ADD;
319 App_Exception.Raise_Exception;
320 END IF;
321 CHECK_CONSTRAINTS;
322
323 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
324 CHECK_CONSTRAINTS;
325
326 ELSIF (p_action = 'VALIDATE_DELETE') THEN
327 Check_Child_Existance;
328 END IF;
329
330 END Before_DML;
331
332 procedure INSERT_ROW (
333 X_ROWID in out NOCOPY VARCHAR2,
334 X_PERSON_ID in NUMBER,
335 X_COURSE_CD in VARCHAR2,
336 X_PRG_CAL_TYPE in VARCHAR2,
337 X_PRG_CI_SEQUENCE_NUMBER in NUMBER,
338 X_PROGRESSION_RULE_CAT in VARCHAR2,
339 X_PRA_SEQUENCE_NUMBER in NUMBER,
340 X_RULE_CHECK_DT in DATE,
341 X_PASSED_IND in VARCHAR2,
342 X_RULE_MESSAGE_TEXT in VARCHAR2,
343 X_MODE in VARCHAR2 default 'R',
344 X_ORG_ID in NUMBER
345 ) AS
346 cursor C is select ROWID from IGS_PR_SDT_PR_RU_CK_ALL
347 where PERSON_ID = X_PERSON_ID
348 and COURSE_CD = X_COURSE_CD
349 and PRG_CAL_TYPE = X_PRG_CAL_TYPE
350 and PRG_CI_SEQUENCE_NUMBER = X_PRG_CI_SEQUENCE_NUMBER
351 and PROGRESSION_RULE_CAT = X_PROGRESSION_RULE_CAT
352 and PRA_SEQUENCE_NUMBER = X_PRA_SEQUENCE_NUMBER
353 and RULE_CHECK_DT = X_RULE_CHECK_DT;
354 X_LAST_UPDATE_DATE DATE;
355 X_LAST_UPDATED_BY NUMBER;
356 X_LAST_UPDATE_LOGIN NUMBER;
357 begin
358 X_LAST_UPDATE_DATE := SYSDATE;
359 if(X_MODE = 'I') then
360 X_LAST_UPDATED_BY := 1;
361 X_LAST_UPDATE_LOGIN := 0;
362 elsif (X_MODE = 'R') then
363 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
364 if X_LAST_UPDATED_BY is NULL then
365 X_LAST_UPDATED_BY := -1;
366 end if;
367 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
368 if X_LAST_UPDATE_LOGIN is NULL then
369 X_LAST_UPDATE_LOGIN := -1;
370 end if;
371 else
372 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
373 IGS_GE_MSG_STACK.ADD;
374 app_exception.raise_exception;
375 end if;
376 Before_DML (
377 p_action => 'INSERT',
378 x_rowid => x_rowid ,
379 x_person_id => x_person_id ,
380 x_course_cd => x_course_cd ,
381 x_prg_cal_type => x_prg_cal_type ,
382 x_prg_ci_sequence_number => x_prg_ci_sequence_number ,
383 x_rule_check_dt => x_rule_check_dt ,
384 x_progression_rule_cat => x_progression_rule_cat ,
385 x_pra_sequence_number => x_pra_sequence_number ,
386 x_rule_message_text => x_rule_message_text ,
387 x_passed_ind => nvl( x_passed_ind, 'Y') ,
388 x_creation_date => x_last_update_date ,
389 x_created_by => x_last_updated_by ,
390 x_last_update_date => x_last_update_date ,
391 x_last_updated_by => x_last_updated_by ,
392 x_last_update_login => x_last_update_login,
393 x_org_id => igs_ge_gen_003.get_org_id
394 );
395 insert into IGS_PR_SDT_PR_RU_CK_ALL (
396 PERSON_ID,
397 COURSE_CD,
398 PRG_CAL_TYPE,
399 PRG_CI_SEQUENCE_NUMBER,
400 RULE_CHECK_DT,
401 PROGRESSION_RULE_CAT,
402 PRA_SEQUENCE_NUMBER,
403 PASSED_IND,
404 RULE_MESSAGE_TEXT,
405 CREATION_DATE,
406 CREATED_BY,
407 LAST_UPDATE_DATE,
408 LAST_UPDATED_BY,
409 LAST_UPDATE_LOGIN,
410 ORG_ID
411 ) values (
412 NEW_REFERENCES.PERSON_ID,
413 NEW_REFERENCES.COURSE_CD,
414 NEW_REFERENCES.PRG_CAL_TYPE,
415 NEW_REFERENCES.PRG_CI_SEQUENCE_NUMBER,
416 NEW_REFERENCES.RULE_CHECK_DT,
417 NEW_REFERENCES.PROGRESSION_RULE_CAT,
418 NEW_REFERENCES.PRA_SEQUENCE_NUMBER,
419 NEW_REFERENCES.PASSED_IND,
420 NEW_REFERENCES.RULE_MESSAGE_TEXT,
421 X_LAST_UPDATE_DATE,
422 X_LAST_UPDATED_BY,
423 X_LAST_UPDATE_DATE,
424 X_LAST_UPDATED_BY,
425 X_LAST_UPDATE_LOGIN,
426 NEW_REFERENCES.ORG_ID
427 );
428
429 open c;
430 fetch c into X_ROWID;
431 if (c%notfound) then
432 close c;
433 raise no_data_found;
434 end if;
435 close c;
436 end INSERT_ROW;
437
438 procedure LOCK_ROW (
439 X_ROWID in VARCHAR2,
440 X_PERSON_ID in NUMBER,
441 X_COURSE_CD in VARCHAR2,
442 X_PRG_CAL_TYPE in VARCHAR2,
443 X_PRG_CI_SEQUENCE_NUMBER in NUMBER,
444 X_PROGRESSION_RULE_CAT in VARCHAR2,
445 X_PRA_SEQUENCE_NUMBER in NUMBER,
446 X_RULE_CHECK_DT in DATE,
447 X_PASSED_IND in VARCHAR2,
448 X_RULE_MESSAGE_TEXT in VARCHAR2
449 ) AS
450 cursor c1 is select
451 PERSON_ID,
452 COURSE_CD,
453 PRG_CAL_TYPE,
454 PRG_CI_SEQUENCE_NUMBER,
455 PROGRESSION_RULE_CAT ,
456 PRA_SEQUENCE_NUMBER ,
457 RULE_CHECK_DT ,
458 PASSED_IND ,
459 RULE_MESSAGE_TEXT
460 from IGS_PR_SDT_PR_RU_CK_ALL
461 where ROWID = X_ROWID for update nowait;
462 tlinfo c1%rowtype;
463
464 begin
465 open c1;
466 fetch c1 into tlinfo;
467 if (c1%notfound) then
468 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
469 IGS_GE_MSG_STACK.ADD;
470 close c1;
471 app_exception.raise_exception;
472
473 return;
474 end if;
475 close c1;
476
477 if (
478 (tlinfo.PERSON_ID = X_PERSON_ID) AND
479 (tlinfo.COURSE_CD =X_COURSE_CD) AND
480 (tlinfo.PRG_CAL_TYPE = X_PRG_CAL_TYPE) AND
481 (tlinfo.PRG_CI_SEQUENCE_NUMBER = X_PRG_CI_SEQUENCE_NUMBER) AND
482 (tlinfo.PROGRESSION_RULE_CAT =X_PROGRESSION_RULE_CAT) AND
483 (tlinfo.PRA_SEQUENCE_NUMBER =X_PRA_SEQUENCE_NUMBER) AND
484 (tlinfo.RULE_CHECK_DT =X_RULE_CHECK_DT) AND
485 (tlinfo.PASSED_IND =X_PASSED_IND) AND
486 ( (tlinfo.RULE_MESSAGE_TEXT =X_RULE_MESSAGE_TEXT)
487 OR (( tlinfo.RULE_MESSAGE_TEXT is null)
488 AND (X_RULE_MESSAGE_TEXT is null)))
489
490 ) then
491 null;
492 else
493 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
494 IGS_GE_MSG_STACK.ADD;
495 app_exception.raise_exception;
496 end if;
497 return;
498 end LOCK_ROW;
499
500 procedure UPDATE_ROW (
501 X_ROWID in VARCHAR2,
502 X_PERSON_ID in NUMBER,
503 X_COURSE_CD in VARCHAR2,
504 X_PRG_CAL_TYPE in VARCHAR2,
505 X_PRG_CI_SEQUENCE_NUMBER in NUMBER,
506 X_PROGRESSION_RULE_CAT in VARCHAR2,
507 X_PRA_SEQUENCE_NUMBER in NUMBER,
508 X_RULE_CHECK_DT in DATE,
509 X_PASSED_IND in VARCHAR2,
510 X_RULE_MESSAGE_TEXT in VARCHAR2,
511 X_MODE in VARCHAR2 default 'R'
512 ) AS
513 X_LAST_UPDATE_DATE DATE;
514 X_LAST_UPDATED_BY NUMBER;
515 X_LAST_UPDATE_LOGIN NUMBER;
516 begin
517 X_LAST_UPDATE_DATE := SYSDATE;
518 if(X_MODE = 'I') then
519 X_LAST_UPDATED_BY := 1;
520 X_LAST_UPDATE_LOGIN := 0;
521 elsif (X_MODE = 'R') then
522 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
523 if X_LAST_UPDATED_BY is NULL then
524 X_LAST_UPDATED_BY := -1;
525 end if;
526 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
527 if X_LAST_UPDATE_LOGIN is NULL then
528 X_LAST_UPDATE_LOGIN := -1;
529 end if;
530 else
531 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
532 IGS_GE_MSG_STACK.ADD;
533 app_exception.raise_exception;
534 end if;
535 Before_DML (
536 p_action => 'UPDATE',
537 x_rowid => x_rowid ,
538 x_person_id => x_person_id ,
539 x_course_cd => x_course_cd ,
540 x_prg_cal_type => x_prg_cal_type ,
541 x_prg_ci_sequence_number => x_prg_ci_sequence_number ,
542 x_rule_check_dt => x_rule_check_dt ,
543 x_progression_rule_cat => x_progression_rule_cat ,
544 x_pra_sequence_number => x_pra_sequence_number ,
545 x_rule_message_text => x_rule_message_text ,
546 x_passed_ind => x_passed_ind ,
547 x_creation_date => x_last_update_date ,
548 x_created_by => x_last_updated_by ,
549 x_last_update_date => x_last_update_date ,
550 x_last_updated_by => x_last_updated_by ,
551 x_last_update_login => x_last_update_login
552 );
553
554 update IGS_PR_SDT_PR_RU_CK_ALL set
555 PASSED_IND = NEW_REFERENCES.PASSED_IND,
556 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
557 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
558 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
559 where ROWID = X_ROWID;
560 if (sql%notfound) then
561 raise no_data_found;
562 end if;
563 end UPDATE_ROW;
564
565 procedure ADD_ROW (
566 X_ROWID in out NOCOPY VARCHAR2,
567 X_PERSON_ID in NUMBER,
568 X_COURSE_CD in VARCHAR2,
569 X_PRG_CAL_TYPE in VARCHAR2,
570 X_PRG_CI_SEQUENCE_NUMBER in NUMBER,
571 X_PROGRESSION_RULE_CAT in VARCHAR2,
572 X_PRA_SEQUENCE_NUMBER in NUMBER,
573 X_RULE_CHECK_DT in DATE,
574 X_PASSED_IND in VARCHAR2,
575 X_RULE_MESSAGE_TEXT in VARCHAR2,
576 X_MODE in VARCHAR2 default 'R',
577 X_ORG_ID in NUMBER
578 ) AS
579 cursor c1 is select rowid from IGS_PR_SDT_PR_RU_CK_ALL
580 where PERSON_ID = X_PERSON_ID
581 and COURSE_CD = X_COURSE_CD
582 and PRG_CAL_TYPE = X_PRG_CAL_TYPE
583 and PRG_CI_SEQUENCE_NUMBER = X_PRG_CI_SEQUENCE_NUMBER
584 and PROGRESSION_RULE_CAT = X_PROGRESSION_RULE_CAT
585 and PRA_SEQUENCE_NUMBER = X_PRA_SEQUENCE_NUMBER
586 and RULE_CHECK_DT = X_RULE_CHECK_DT
587 ;
588 begin
589 open c1;
590 fetch c1 into X_ROWID;
591 if (c1%notfound) then
592 close c1;
593 INSERT_ROW (
594 X_ROWID,
595 X_PERSON_ID,
596 X_COURSE_CD,
597 X_PRG_CAL_TYPE,
598 X_PRG_CI_SEQUENCE_NUMBER,
599 X_PROGRESSION_RULE_CAT,
600 X_PRA_SEQUENCE_NUMBER,
601 X_RULE_CHECK_DT,
602 X_PASSED_IND,
603 X_RULE_MESSAGE_TEXT,
604 X_MODE,
605 X_ORG_ID);
606 return;
607 end if;
608 close c1;
609 UPDATE_ROW (
610 X_ROWID ,
611 X_PERSON_ID,
612 X_COURSE_CD,
613 X_PRG_CAL_TYPE,
614 X_PRG_CI_SEQUENCE_NUMBER,
615 X_PROGRESSION_RULE_CAT,
616 X_PRA_SEQUENCE_NUMBER,
617 X_RULE_CHECK_DT,
618 X_PASSED_IND,
619 X_RULE_MESSAGE_TEXT,
620 X_MODE
621 );
622 end ADD_ROW;
623
624 procedure DELETE_ROW (
625 X_ROWID in VARCHAR2
626 ) AS
627 begin
628 Before_DML (
629 p_action => 'DELETE',
630 x_rowid => X_ROWID
631 ) ;
632 delete from IGS_PR_SDT_PR_RU_CK_ALL
633 where ROWID = X_ROWID;
634 if (sql%notfound) then
635 raise no_data_found;
636 end if;
637 end DELETE_ROW;
638
639 PROCEDURE Check_Constraints (
640 Column_Name IN VARCHAR2 DEFAULT NULL,
641 Column_Value IN VARCHAR2 DEFAULT NULL
642 ) AS
643 BEGIN
644 IF Column_Name is null THEN
645 NULL;
646 ELSIF upper(Column_name) = 'COURSE_CD' THEN
647 new_references.COURSE_CD:= COLUMN_VALUE ;
648
649 ELSIF upper(Column_name) = 'PASSED_IND' THEN
650 new_references.PASSED_IND:= COLUMN_VALUE ;
651
652 ELSIF upper(Column_name) = 'PRG_CAL_TYPE' THEN
653 new_references.PRG_CAL_TYPE:= COLUMN_VALUE ;
654
655 ELSIF upper(Column_name) = 'PROGRESSION_RULE_CAT' THEN
656 new_references.PROGRESSION_RULE_CAT:= COLUMN_VALUE ;
657
658 ELSIF upper(Column_name) = 'PRA_SEQUENCE_NUMBER' THEN
659 new_references.PRA_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
660
661 ELSIF upper(Column_name) = 'PRG_CI_SEQUENCE_NUMBER' THEN
662 new_references.PRG_CI_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
663
664 END IF ;
665
666 IF upper(Column_name) = 'COURSE_CD' OR COLUMN_NAME IS NULL THEN
667 IF new_references.COURSE_CD<> upper(new_references.COURSE_CD) then
668 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
669 IGS_GE_MSG_STACK.ADD;
670 App_Exception.Raise_Exception ;
671 END IF;
672
673 END IF ;
674
675 IF upper(Column_name) = 'PASSED_IND' OR COLUMN_NAME IS NULL THEN
676 IF new_references.PASSED_IND<> upper(new_references.PASSED_IND) then
677 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
678 IGS_GE_MSG_STACK.ADD;
679 App_Exception.Raise_Exception ;
680 END IF;
681
682 IF new_references.PASSED_IND not in ('Y','N') then
683 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
684 IGS_GE_MSG_STACK.ADD;
685 App_Exception.Raise_Exception ;
686 END IF;
687
688 END IF ;
689
690 IF upper(Column_name) = 'PRG_CAL_TYPE' OR COLUMN_NAME IS NULL THEN
691 IF new_references.PRG_CAL_TYPE<> upper(new_references.PRG_CAL_TYPE) then
692 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
693 IGS_GE_MSG_STACK.ADD;
694 App_Exception.Raise_Exception ;
695 END IF;
696
697 END IF ;
698
699 IF upper(Column_name) = 'PROGRESSION_RULE_CAT' OR COLUMN_NAME IS NULL THEN
700 IF new_references.PROGRESSION_RULE_CAT<> upper(new_references.PROGRESSION_RULE_CAT) then
701 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
702 IGS_GE_MSG_STACK.ADD;
703 App_Exception.Raise_Exception ;
704 END IF;
705
706 END IF ;
707
708 IF upper(Column_name) = 'PRA_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
709 IF new_references.PRA_SEQUENCE_NUMBER < 1 or new_references.PRA_SEQUENCE_NUMBER > 999999 then
710 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
711 IGS_GE_MSG_STACK.ADD;
712 App_Exception.Raise_Exception ;
713 END IF;
714
715 END IF ;
716
717 IF upper(Column_name) = 'PRG_CI_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
718 IF new_references.PRG_CI_SEQUENCE_NUMBER < 1 or new_references.PRG_CI_SEQUENCE_NUMBER > 999999 then
719 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
720 IGS_GE_MSG_STACK.ADD;
721 App_Exception.Raise_Exception ;
722 END IF;
723
724 END IF ;
725
726 END Check_Constraints;
727
728
729 end IGS_PR_SDT_PR_RU_CK_PKG;