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