[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_TYPE_PKG
Source
1 package body IGS_PS_TYPE_PKG AS
2 /* $Header: IGSPI36B.pls 115.16 2003/06/05 12:54:01 sarakshi ship $ */
3
4
5 l_rowid VARCHAR2(25);
6 old_references IGS_PS_TYPE_ALL%RowType;
7 new_references IGS_PS_TYPE_ALL%RowType;
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 ,
12 x_description IN VARCHAR2 ,
13 x_govt_course_type IN NUMBER ,
14 x_award_course_ind IN VARCHAR2 ,
15 x_course_type_group_cd IN VARCHAR2 ,
16 x_tac_course_level IN VARCHAR2 ,
17 x_research_type_ind IN VARCHAR2 ,
18 x_closed_ind IN VARCHAR2 ,
19 x_course_type IN VARCHAR2 ,
20 x_creation_date IN DATE ,
21 x_created_by IN NUMBER ,
22 x_last_update_date IN DATE ,
23 x_last_updated_by IN NUMBER ,
24 x_last_update_login IN NUMBER ,
25 x_org_id IN NUMBER ,
26 x_primary_auto_select IN VARCHAR2 ,
27 x_fin_aid_program_type IN VARCHAR2 ,
28 x_enrolment_cat IN VARCHAR2
29 ) AS
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM IGS_PS_TYPE_ALL
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 Open cur_old_ref_values;
43 Fetch cur_old_ref_values INTO old_references;
44 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
45 Close cur_old_ref_values;
46 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
47 IGS_GE_MSG_STACK.ADD;
48 App_Exception.Raise_Exception;
49 Return;
50 END IF;
51 Close cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.description := x_description;
55 new_references.govt_course_type := x_govt_course_type;
56 new_references.award_course_ind := x_award_course_ind;
57 new_references.course_type_group_cd := x_course_type_group_cd;
58 new_references.tac_course_level := x_tac_course_level;
59 new_references.research_type_ind := x_research_type_ind;
60 new_references.closed_ind := x_closed_ind;
61 new_references.course_type := x_course_type;
62 new_references.primary_auto_select := x_primary_auto_select;
63 new_references.fin_aid_program_type :=x_fin_aid_program_type;
64 new_references.enrolment_cat := x_enrolment_cat;
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 new_references.org_id := x_org_id;
76 END Set_Column_Values;
77
78 PROCEDURE BeforeRowInsertUpdate(
79 p_inserting IN BOOLEAN ,
80 p_updating IN BOOLEAN
81 ) AS
82 v_message_name VARCHAR2(30);
83 v_description IGS_PS_TYPE_ALL.description%TYPE;
84 v_govt_course_type IGS_PS_TYPE_ALL.govt_course_type%TYPE;
85 v_course_type_group_cd IGS_PS_TYPE_ALL.course_type_group_cd%TYPE;
86 v_tac_course_level IGS_PS_TYPE_ALL.tac_course_level%TYPE;
87 v_closed_ind IGS_PS_TYPE_ALL.closed_ind%TYPE;
88 v_award_course_ind IGS_PS_TYPE_ALL.award_course_ind%TYPE;
89 v_research_type_ind IGS_PS_TYPE_ALL.research_type_ind%TYPE;
90 v_primary_auto_select IGS_PS_TYPE_ALL.primary_auto_select%TYPE;
91 v_enrolment_cat IGS_PS_TYPE_ALL.enrolment_cat%TYPE;
92 x_rowid VARCHAR2(25);
93
94 CURSOR SPTH_CUR IS SELECT Rowid
95 FROM IGS_PS_TYPE_HIST_ALL
96 WHERE course_type = old_references.course_type;
97
98 BEGIN
99 -- Validate DEET IGS_PS_COURSE type.
100 IF p_inserting OR
101 (p_updating AND
102 ((old_references.govt_course_type <> new_references.govt_course_type) OR
103 (old_references.closed_ind = 'Y' AND new_references.closed_ind = 'N')))
104 THEN
105 IF IGS_PS_VAL_CTY.crsp_val_cty_govt (
106 new_references.govt_course_type,
107 v_message_name) = FALSE THEN
108 Fnd_Message.Set_Name('IGS',v_message_name);
109 IGS_GE_MSG_STACK.ADD;
110 App_Exception.Raise_Exception;
111 END IF;
112 END IF;
113 -- Validate IGS_PS_COURSE type group code.
114 IF p_inserting OR
115 (p_updating AND
116 (NVL(old_references.course_type_group_cd, 'null') <>
117 NVL(new_references.course_type_group_cd, 'null') OR
118 (old_references.closed_ind = 'Y' AND new_references.closed_ind = 'N')))
119 THEN
120 IF IGS_PS_VAL_CTY.crsp_val_cty_group (
121 new_references.course_type_group_cd,
122 v_message_name) = FALSE THEN
123 Fnd_Message.Set_Name('IGS',v_message_name);
124 IGS_GE_MSG_STACK.ADD;
125 App_Exception.Raise_Exception;
126 END IF;
127 END IF;
128 -- Validate the IGS_PS_AWD IGS_PS_COURSE indicator.
129 IF (p_updating AND
130 (old_references.award_course_ind <> new_references.award_course_ind)) THEN
131 IF IGS_PS_VAL_CTY.crsp_val_cty_award (
132 new_references.course_type,
133 new_references.award_course_ind,
134 v_message_name) = FALSE THEN
135 Fnd_Message.Set_Name('IGS',v_message_name);
136 IGS_GE_MSG_STACK.ADD;
137 App_Exception.Raise_Exception;
138 END IF;
139 END IF;
140 -- Create history record.
141 IF p_updating THEN
142 IF old_references.description <> new_references.description OR
143 old_references.govt_course_type <> new_references.govt_course_type OR
144 NVL(old_references.course_type_group_cd, 'null') <>
145 NVL(new_references.course_type_group_cd, 'null') OR
146 NVL(old_references.tac_course_level, 'null') <>
147 NVL(new_references.tac_course_level, 'null') OR
148 NVL(old_references.primary_auto_select, 'null') <> -- added as part of Career_Impact build
149 NVL(new_references.primary_auto_select, 'null') OR
150 NVL(old_references.fin_aid_program_type,'null') <>
151 NVL(new_references.fin_aid_program_type,'null') OR
152 old_references.closed_ind <> new_references.closed_ind OR
153 old_references.award_course_ind <> new_references.award_course_ind OR
154 old_references.research_type_ind <> new_references.research_type_ind
155 THEN
156 SELECT DECODE (old_references.description,
157 new_references.description, NULL, old_references.description),
158 DECODE (old_references.govt_course_type,
159 new_references.govt_course_type, NULL,old_references.govt_course_type),
160 DECODE (NVL(old_references.course_type_group_cd, 'null'),
161 NVL(new_references.course_type_group_cd, 'null'),
162 NULL, old_references.course_type_group_cd),
163 DECODE (NVL(old_references.tac_course_level, 'null'),
164 NVL(new_references.tac_course_level, 'null'), NULL,
165 old_references.tac_course_level),
166 DECODE (old_references.closed_ind,
167 new_references.closed_ind, NULL, old_references.closed_ind),
168 DECODE (old_references.award_course_ind,
169 new_references.award_course_ind, NULL,
170 old_references.award_course_ind),
171 DECODE (old_references.research_type_ind,
172 new_references.research_type_ind, NULL,
173 old_references.research_type_ind),
174 DECODE (NVL(old_references.primary_auto_select,'null'),
175 NVL(new_references.primary_auto_select,'null'),
176 NULL,old_references.primary_auto_select)
177 INTO v_description,
178 v_govt_course_type,
179 v_course_type_group_cd,
180 v_tac_course_level,
181 v_closed_ind,
182 v_award_course_ind,
183 v_research_type_ind,
184 v_primary_auto_select
185 FROM dual;
186
187
188 BEGIN
189 IGS_PS_TYPE_HIST_PKG.Insert_Row(
190 X_ROWID => x_rowid,
191 X_COURSE_TYPE => old_references.course_type,
192 X_HIST_START_DT => old_references.last_update_date,
193 X_HIST_END_DT => new_references.last_update_date,
194 X_HIST_WHO => old_references.last_updated_by,
195 X_DESCRIPTION => v_description,
196 X_GOVT_COURSE_TYPE => v_govt_course_type,
197 X_AWARD_COURSE_IND => v_award_course_ind,
198 X_COURSE_TYPE_GROUP_CD => v_course_type_group_cd,
199 X_TAC_COURSE_LEVEL => v_tac_course_level,
200 X_RESEARCH_TYPE_IND => v_research_type_ind,
201 X_CLOSED_IND => v_closed_ind,
202 X_MODE => 'R',
203 X_ORG_ID => old_references.org_id,
204 X_PRIMARY_AUTO_SELECT => v_primary_auto_select,
205 X_FIN_AID_PROGRAM_TYPE => old_references.fin_aid_program_type
206 );
207 END;
208
209 END IF;
210 END IF;
211
212 END BeforeRowInsertUpdate;
213
214 PROCEDURE Check_Constraints (
215 Column_Name IN VARCHAR2 ,
216 Column_Value IN VARCHAR2
217 ) AS
218 BEGIN
219 IF column_name is null THEN
220 NULL;
221 ELSIF upper(column_name) = 'CLOSED_IND' THEN
222 new_references.closed_ind := column_value;
223 ELSIF upper(column_name) = 'RESEARCH_TYPE_IND' THEN
224 new_references.research_type_ind := column_value;
225 ELSIF upper(column_name) = 'COURSE_TYPE' THEN
226 new_references.course_type := column_value;
227 ELSIF upper(column_name) = 'AWARD_COURSE_IND' THEN
228 new_references.award_course_ind := column_value;
229 ELSIF upper(column_name) = 'COURSE_TYPE_GROUP_CD' THEN
230 new_references.course_type_group_cd := column_value;
231 ELSIF upper(column_name) = 'TAC_COURSE_LEVEL' THEN
232 new_references.tac_course_level := column_value;
233 ELSIF upper(column_name) = 'PRIMARY_AUTO_SELECT' THEN -- added as part of Career_Impact build
234 new_references.primary_auto_select := column_value;
235 ELSIF upper(column_name) = 'ENROLMENT_CAT' THEN -- added as part of Self Service Setup build
236 new_references.enrolment_cat := column_value;
237 ELSIF upper(column_name) = 'FIN_AID_PROGRAM_TYPE' THEN -- added as part of FA Program Type build
238 new_references.fin_aid_program_type:= column_value;
239 END IF;
240
241 IF upper(column_name)= 'COURSE_TYPE' OR
242 column_name is null THEN
243 IF new_references.course_type <> UPPER(new_references.course_type )
244 THEN
245 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
246 IGS_GE_MSG_STACK.ADD;
247 App_Exception.Raise_Exception;
248 END IF;
249 END IF;
250
251 IF upper(column_name)= 'COURSE_TYPE_GROUP_CD' OR
252 column_name is null THEN
253 IF new_references.course_type_group_cd <> UPPER(new_references.course_type_group_cd )
254 THEN
255 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
256 IGS_GE_MSG_STACK.ADD;
257 App_Exception.Raise_Exception;
258 END IF;
259 END IF;
260
261 IF upper(column_name)= 'TAC_COURSE_LEVEL' OR
262 column_name is null THEN
263 IF new_references.tac_course_level <> UPPER(new_references.tac_course_level)
264 THEN
265 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
266 IGS_GE_MSG_STACK.ADD;
267 App_Exception.Raise_Exception;
268 END IF;
269 END IF;
270
271 IF upper(column_name)= 'RESEARCH_TYPE_IND' OR
272 column_name is null THEN
273 IF new_references.research_type_ind NOT IN ( 'Y' , 'N' )
274 THEN
275 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
276 IGS_GE_MSG_STACK.ADD;
277 App_Exception.Raise_Exception;
278 END IF;
279 END IF;
280
281 IF upper(column_name)= 'CLOSED_IND' OR
282 column_name is null THEN
283 IF new_references.closed_ind NOT IN ( 'Y' , 'N' )
284 THEN
285 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
286 IGS_GE_MSG_STACK.ADD;
287 App_Exception.Raise_Exception;
288 END IF;
289 END IF;
290
291 IF upper(column_name)= 'AWARD_COURSE_IND' OR
292 column_name is null THEN
293 IF new_references.award_course_ind NOT IN ( 'Y' , 'N' )
294 THEN
295 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
296 IGS_GE_MSG_STACK.ADD;
297 App_Exception.Raise_Exception;
298 END IF;
299 END IF;
300 END Check_Constraints;
301
302 PROCEDURE Check_Parent_Existance AS
303 BEGIN
304
305 IF (((old_references.course_type_group_cd = new_references.course_type_group_cd)) OR
306 ((new_references.course_type_group_cd IS NULL))) THEN
307 NULL;
308 ELSE
309 IF NOT IGS_PS_TYPE_GRP_PKG.Get_PK_For_Validation (
310 new_references.course_type_group_cd
311 )THEN
312 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
313 IGS_GE_MSG_STACK.ADD;
314 App_Exception.Raise_Exception;
315 END IF;
316 END IF;
317
318 IF (((old_references.govt_course_type = new_references.govt_course_type)) OR
319 ((new_references.govt_course_type IS NULL))) THEN
320 NULL;
321 ELSE
322 IF NOT IGS_PS_GOVT_TYPE_PKG.Get_PK_For_Validation (
323 new_references.govt_course_type
324 )THEN
325 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
326 IGS_GE_MSG_STACK.ADD;
327 App_Exception.Raise_Exception;
328 END IF;
329 END IF;
330 --Added new foreign key column enrolment_cat as a part of self service setup build enh bug #2043044
331 IF (((old_references.enrolment_cat = new_references.enrolment_cat)) OR
332 ((new_references.enrolment_cat IS NULL))) THEN
333 NULL;
334 ELSE
335 IF NOT IGS_EN_ENROLMENT_CAT_PKG.Get_PK_For_Validation (
336 new_references.enrolment_cat
337 )THEN
338 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
339 IGS_GE_MSG_STACK.ADD;
340 App_Exception.Raise_Exception;
341 END IF;
342 END IF;
343
344 END Check_Parent_Existance;
345
346
347 FUNCTION Get_PK_For_Validation (
348 x_course_type IN VARCHAR2
349 ) RETURN BOOLEAN AS
350
351 CURSOR cur_rowid IS
352 SELECT rowid
353 FROM IGS_PS_TYPE_ALL
354 WHERE course_type = x_course_type;
355
356 lv_rowid cur_rowid%RowType;
357
358 BEGIN
359
360 Open cur_rowid;
361 Fetch cur_rowid INTO lv_rowid;
362 IF (cur_rowid%FOUND) THEN
363 Close cur_rowid;
364 Return(TRUE);
365 ELSE
366 Close cur_rowid;
367 Return(FALSE);
368 END IF;
369
370 END Get_PK_For_Validation;
371
372 PROCEDURE GET_FK_IGS_PS_TYPE_GRP (
373 x_course_type_group_cd IN VARCHAR2
374 ) AS
375
376 CURSOR cur_rowid IS
377 SELECT rowid
378 FROM IGS_PS_TYPE_ALL
379 WHERE course_type_group_cd = x_course_type_group_cd ;
380
381 lv_rowid cur_rowid%RowType;
382
383 BEGIN
384
385 Open cur_rowid;
386 Fetch cur_rowid INTO lv_rowid;
387 IF (cur_rowid%FOUND) THEN
388 Close cur_rowid;
389 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CTY_CTG_FK');
390 IGS_GE_MSG_STACK.ADD;
391 App_Exception.Raise_Exception;
392 Return;
393 END IF;
394 Close cur_rowid;
395
396 END GET_FK_IGS_PS_TYPE_GRP;
397
398 PROCEDURE GET_FK_IGS_PS_GOVT_TYPE (
399 x_govt_course_type IN NUMBER
400 ) AS
401
402 CURSOR cur_rowid IS
403 SELECT rowid
404 FROM IGS_PS_TYPE_ALL
405 WHERE govt_course_type = x_govt_course_type ;
406
407 lv_rowid cur_rowid%RowType;
408
409 BEGIN
410
411 Open cur_rowid;
412 Fetch cur_rowid INTO lv_rowid;
413 IF (cur_rowid%FOUND) THEN
414 Close cur_rowid;
415 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CTY_GCT_FK');
416 IGS_GE_MSG_STACK.ADD;
417 App_Exception.Raise_Exception;
418 Return;
419 END IF;
420 Close cur_rowid;
421
422 END GET_FK_IGS_PS_GOVT_TYPE;
423
424 PROCEDURE GET_FK_IGS_EN_ENROLMENT_CAT (
425 x_enrolment_cat IN VARCHAR2) AS
426
427 CURSOR cur_rowid IS
428 SELECT rowid
429 FROM IGS_PS_TYPE_ALL
430 WHERE enrolment_cat = x_enrolment_cat ;
431
432 lv_rowid cur_rowid%RowType;
433
434 BEGIN
435
436 Open cur_rowid;
437 Fetch cur_rowid INTO lv_rowid;
438 IF (cur_rowid%FOUND) THEN
439 Close cur_rowid;
440 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CTY_CTG_FK');
441 IGS_GE_MSG_STACK.ADD;
442 App_Exception.Raise_Exception;
443 Return;
444 END IF;
445 Close cur_rowid;
446
447 END GET_FK_IGS_EN_ENROLMENT_CAT;
448
449
450
451 PROCEDURE Before_DML (
452 p_action IN VARCHAR2,
453 x_rowid IN VARCHAR2 ,
454 x_description IN VARCHAR2 ,
455 x_govt_course_type IN NUMBER ,
456 x_award_course_ind IN VARCHAR2 ,
457 x_course_type_group_cd IN VARCHAR2 ,
458 x_tac_course_level IN VARCHAR2 ,
459 x_research_type_ind IN VARCHAR2 ,
460 x_closed_ind IN VARCHAR2 ,
461 x_course_type IN VARCHAR2 ,
462 x_creation_date IN DATE ,
463 x_created_by IN NUMBER ,
464 x_last_update_date IN DATE ,
465 x_last_updated_by IN NUMBER ,
466 x_last_update_login IN NUMBER ,
467 x_org_id IN NUMBER ,
468 x_primary_auto_select IN VARCHAR2 ,
469 x_fin_aid_program_type IN VARCHAR2 ,
470 x_enrolment_cat IN VARCHAR2
471 ) AS
472 BEGIN
473
474 Set_Column_Values (
475 p_action,
476 x_rowid,
477 x_description,
478 x_govt_course_type,
479 x_award_course_ind,
480 x_course_type_group_cd,
481 x_tac_course_level,
482 x_research_type_ind,
483 x_closed_ind,
484 x_course_type,
485 x_creation_date,
486 x_created_by,
487 x_last_update_date,
488 x_last_updated_by,
489 x_last_update_login,
490 x_org_id,
491 x_primary_auto_select,
492 x_fin_aid_program_type,
493 x_enrolment_cat
494 );
495
496 IF (p_action = 'INSERT') THEN
497 -- Call all the procedures related to Before Insert.
498 BeforeRowInsertUpdate( p_inserting => TRUE,p_updating=>FALSE);
499 IF Get_PK_For_Validation(
500 new_references.course_type
501 ) THEN
502 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
503 IGS_GE_MSG_STACK.ADD;
504 App_Exception.Raise_Exception;
505 END IF;
506 Check_Constraints;
507 Check_Parent_Existance;
508 ELSIF (p_action = 'UPDATE') THEN
509 -- Call all the procedures related to Before Update.
510 BeforeRowInsertUpdate( p_updating => TRUE,p_inserting=>FALSE);
511 Check_Constraints;
512 Check_Parent_Existance;
513 ELSIF (p_action = 'VALIDATE_INSERT') THEN
514 IF Get_PK_For_Validation(
515 new_references.course_type
516 ) THEN
517 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
518 IGS_GE_MSG_STACK.ADD;
519 App_Exception.Raise_Exception;
520 END IF;
521 Check_Constraints;
522 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
523 Check_Constraints;
524 END IF;
525 END Before_DML;
526
527 PROCEDURE After_DML (
528 p_action IN VARCHAR2,
529 x_rowid IN VARCHAR2
530 ) AS
531 BEGIN
532
533 l_rowid := x_rowid;
534
535
536 END After_DML;
537
538
539 procedure INSERT_ROW (
540 X_ROWID in out NOCOPY VARCHAR2,
541 X_COURSE_TYPE in VARCHAR2,
542 X_DESCRIPTION in VARCHAR2,
543 X_GOVT_COURSE_TYPE in NUMBER,
544 X_AWARD_COURSE_IND in VARCHAR2,
545 X_COURSE_TYPE_GROUP_CD in VARCHAR2,
546 X_TAC_COURSE_LEVEL in VARCHAR2,
547 X_RESEARCH_TYPE_IND in VARCHAR2,
548 X_CLOSED_IND in VARCHAR2,
549 X_MODE in VARCHAR2 ,
550 X_ORG_ID in NUMBER,
551 X_PRIMARY_AUTO_SELECT IN VARCHAR2 ,
552 X_FIN_AID_PROGRAM_TYPE IN VARCHAR2 ,
553 X_ENROLMENT_CAT in VARCHAR2
554 ) AS
555 cursor C is select ROWID from IGS_PS_TYPE_ALL
556 where COURSE_TYPE = X_COURSE_TYPE;
557 X_LAST_UPDATE_DATE DATE;
558 X_LAST_UPDATED_BY NUMBER;
559 X_LAST_UPDATE_LOGIN NUMBER;
560 BEGIN
561 X_LAST_UPDATE_DATE := SYSDATE;
562 if(X_MODE = 'I') then
563 X_LAST_UPDATED_BY := 1;
564 X_LAST_UPDATE_LOGIN := 0;
565 elsif (X_MODE = 'R') then
566 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
567 if X_LAST_UPDATED_BY is NULL then
568 X_LAST_UPDATED_BY := -1;
569 end if;
570 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
571 if X_LAST_UPDATE_LOGIN is NULL then
572 X_LAST_UPDATE_LOGIN := -1;
573 end if;
574 else
575 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
576 IGS_GE_MSG_STACK.ADD;
577 app_exception.raise_exception;
578 end if;
579 Before_DML( p_action => 'INSERT',
580 x_rowid => X_ROWID,
581 x_description => X_DESCRIPTION,
582 x_govt_course_type => X_GOVT_COURSE_TYPE,
583 x_award_course_ind => NVL(X_AWARD_COURSE_IND,'Y'),
584 x_course_type_group_cd => X_COURSE_TYPE_GROUP_CD,
585 x_tac_course_level => X_TAC_COURSE_LEVEL,
586 x_research_type_ind => NVL(X_RESEARCH_TYPE_IND,'N'),
587 x_closed_ind => NVL(X_CLOSED_IND,'N'),
588 x_course_type => X_COURSE_TYPE,
589 x_creation_date => X_LAST_UPDATE_DATE,
590 x_created_by => X_LAST_UPDATED_BY,
591 x_last_update_date => X_LAST_UPDATE_DATE,
592 x_last_updated_by => X_LAST_UPDATED_BY,
593 x_last_update_login => X_LAST_UPDATE_LOGIN,
594 x_org_id => igs_ge_gen_003.get_org_id,
595 x_primary_auto_select => X_PRIMARY_AUTO_SELECT,
596 x_fin_aid_program_type=>X_FIN_AID_PROGRAM_TYPE,
597 x_enrolment_cat => X_ENROLMENT_CAT
598 );
599 insert into IGS_PS_TYPE_ALL (
600 COURSE_TYPE,
601 DESCRIPTION,
602 GOVT_COURSE_TYPE,
603 AWARD_COURSE_IND,
604 COURSE_TYPE_GROUP_CD,
605 TAC_COURSE_LEVEL,
606 RESEARCH_TYPE_IND,
607 CLOSED_IND,
608 CREATION_DATE,
609 CREATED_BY,
610 LAST_UPDATE_DATE,
611 LAST_UPDATED_BY,
612 LAST_UPDATE_LOGIN,
613 ORG_ID,
614 PRIMARY_AUTO_SELECT,
615 FIN_AID_PROGRAM_TYPE,
616 ENROLMENT_CAT
617 ) values (
618 NEW_REFERENCES.COURSE_TYPE,
619 NEW_REFERENCES.DESCRIPTION,
620 NEW_REFERENCES.GOVT_COURSE_TYPE,
621 NEW_REFERENCES.AWARD_COURSE_IND,
622 NEW_REFERENCES.COURSE_TYPE_GROUP_CD,
623 NEW_REFERENCES.TAC_COURSE_LEVEL,
624 NEW_REFERENCES.RESEARCH_TYPE_IND,
625 NEW_REFERENCES.CLOSED_IND,
626 X_LAST_UPDATE_DATE,
627 X_LAST_UPDATED_BY,
628 X_LAST_UPDATE_DATE,
629 X_LAST_UPDATED_BY,
630 X_LAST_UPDATE_LOGIN,
631 NEW_REFERENCES.ORG_ID,
632 NEW_REFERENCES.PRIMARY_AUTO_SELECT,
633 NEW_REFERENCES.FIN_AID_PROGRAM_TYPE,
634 NEW_REFERENCES.ENROLMENT_CAT
635 );
636
637 open c;
638 fetch c into X_ROWID;
639 if (c%notfound) then
640 close c;
641 raise no_data_found;
642 end if;
643 close c;
644
645 After_DML(
646 p_action => 'INSERT',
647 x_rowid => X_ROWID
648 );
649
650 end INSERT_ROW;
651
652 procedure LOCK_ROW (
653 X_ROWID in VARCHAR2,
654 X_COURSE_TYPE in VARCHAR2,
655 X_DESCRIPTION in VARCHAR2,
656 X_GOVT_COURSE_TYPE in NUMBER,
657 X_AWARD_COURSE_IND in VARCHAR2,
658 X_COURSE_TYPE_GROUP_CD in VARCHAR2,
659 X_TAC_COURSE_LEVEL in VARCHAR2,
660 X_RESEARCH_TYPE_IND in VARCHAR2,
661 X_CLOSED_IND in VARCHAR2,
662 X_PRIMARY_AUTO_SELECT in VARCHAR2,
663 X_FIN_AID_PROGRAM_TYPE IN VARCHAR2,
664 X_ENROLMENT_CAT in VARCHAR2
665 ) AS
666 cursor c1 is select
667 DESCRIPTION,
668 GOVT_COURSE_TYPE,
669 AWARD_COURSE_IND,
670 COURSE_TYPE_GROUP_CD,
671 TAC_COURSE_LEVEL,
672 RESEARCH_TYPE_IND,
673 CLOSED_IND,
674 PRIMARY_AUTO_SELECT,
675 FIN_AID_PROGRAM_TYPE,
676 ENROLMENT_CAT
677 from IGS_PS_TYPE_ALL
678 where ROWID = X_ROWID for update nowait;
679 tlinfo c1%rowtype;
680
681 begin
682 open c1;
683 fetch c1 into tlinfo;
684 if (c1%notfound) then
685 close c1;
686 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
687 IGS_GE_MSG_STACK.ADD;
688 app_exception.raise_exception;
689 return;
690 end if;
691 close c1;
692
693 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
694 AND (tlinfo.GOVT_COURSE_TYPE = X_GOVT_COURSE_TYPE)
695 AND (tlinfo.AWARD_COURSE_IND = X_AWARD_COURSE_IND)
696 AND ((tlinfo.COURSE_TYPE_GROUP_CD = X_COURSE_TYPE_GROUP_CD)
697 OR ((tlinfo.COURSE_TYPE_GROUP_CD is null)
698 AND (X_COURSE_TYPE_GROUP_CD is null)))
699 AND ((tlinfo.TAC_COURSE_LEVEL = X_TAC_COURSE_LEVEL)
700 OR ((tlinfo.TAC_COURSE_LEVEL is null)
701 AND (X_TAC_COURSE_LEVEL is null)))
702 AND (tlinfo.RESEARCH_TYPE_IND = X_RESEARCH_TYPE_IND)
703 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
704 AND ((tlinfo.PRIMARY_AUTO_SELECT = X_PRIMARY_AUTO_SELECT)
705 OR ((tlinfo.PRIMARY_AUTO_SELECT is null)
706 AND (X_PRIMARY_AUTO_SELECT is null)))
707 AND ((tlinfo.ENROLMENT_CAT = X_ENROLMENT_CAT)
708 OR ((tlinfo.ENROLMENT_CAT is null)
709 AND (X_ENROLMENT_CAT is null)))
710 AND ((tlinfo.FIN_AID_PROGRAM_TYPE= X_FIN_AID_PROGRAM_TYPE)
711 OR ((tlinfo.FIN_AID_PROGRAM_TYPE IS NULL)
712 AND (X_FIN_AID_PROGRAM_TYPE IS NULL)))
713 ) then
714 null;
715 else
716 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
717 IGS_GE_MSG_STACK.ADD;
718 app_exception.raise_exception;
719 end if;
720 return;
721 end LOCK_ROW;
722
723 procedure UPDATE_ROW (
724 X_ROWID in VARCHAR2,
725 X_COURSE_TYPE in VARCHAR2,
726 X_DESCRIPTION in VARCHAR2,
727 X_GOVT_COURSE_TYPE in NUMBER,
728 X_AWARD_COURSE_IND in VARCHAR2,
729 X_COURSE_TYPE_GROUP_CD in VARCHAR2,
730 X_TAC_COURSE_LEVEL in VARCHAR2,
731 X_RESEARCH_TYPE_IND in VARCHAR2,
732 X_CLOSED_IND in VARCHAR2,
733 X_MODE in VARCHAR2 ,
734 X_PRIMARY_AUTO_SELECT in VARCHAR2 ,
735 X_FIN_AID_PROGRAM_TYPE IN VARCHAR2,
736 X_ENROLMENT_CAT in VARCHAR2 ) AS
737 X_LAST_UPDATE_DATE DATE;
738 X_LAST_UPDATED_BY NUMBER;
739 X_LAST_UPDATE_LOGIN NUMBER;
740 begin
741 X_LAST_UPDATE_DATE := SYSDATE;
742 if(X_MODE = 'I') then
743 X_LAST_UPDATED_BY := 1;
744 X_LAST_UPDATE_LOGIN := 0;
745 elsif (X_MODE = 'R') then
746 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
747 if X_LAST_UPDATED_BY is NULL then
748 X_LAST_UPDATED_BY := -1;
749 end if;
750 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
751 if X_LAST_UPDATE_LOGIN is NULL then
752 X_LAST_UPDATE_LOGIN := -1;
753 end if;
754 else
755 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
756 IGS_GE_MSG_STACK.ADD;
757 app_exception.raise_exception;
758 end if;
759 Before_DML( p_action => 'UPDATE',
760 x_rowid => X_ROWID,
761 x_description => X_DESCRIPTION,
762 x_govt_course_type => X_GOVT_COURSE_TYPE,
763 x_award_course_ind => X_AWARD_COURSE_IND,
764 x_course_type_group_cd => X_COURSE_TYPE_GROUP_CD,
765 x_tac_course_level => X_TAC_COURSE_LEVEL,
766 x_research_type_ind => X_RESEARCH_TYPE_IND,
767 x_closed_ind => X_CLOSED_IND,
768 x_course_type => X_COURSE_TYPE,
769 x_creation_date => X_LAST_UPDATE_DATE,
770 x_created_by => X_LAST_UPDATED_BY,
771 x_last_update_date => X_LAST_UPDATE_DATE,
772 x_last_updated_by => X_LAST_UPDATED_BY,
773 x_last_update_login => X_LAST_UPDATE_LOGIN,
774 x_primary_auto_select => X_PRIMARY_AUTO_SELECT,
775 x_fin_aid_program_type=> X_FIN_AID_PROGRAM_TYPE,
776 x_enrolment_cat => X_ENROLMENT_CAT
777 );
778 update IGS_PS_TYPE_ALL set
779 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
780 GOVT_COURSE_TYPE = NEW_REFERENCES.GOVT_COURSE_TYPE,
781 AWARD_COURSE_IND = NEW_REFERENCES.AWARD_COURSE_IND,
782 COURSE_TYPE_GROUP_CD = NEW_REFERENCES.COURSE_TYPE_GROUP_CD,
783 TAC_COURSE_LEVEL = NEW_REFERENCES.TAC_COURSE_LEVEL,
784 RESEARCH_TYPE_IND = NEW_REFERENCES.RESEARCH_TYPE_IND,
785 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
786 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
787 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
788 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
789 PRIMARY_AUTO_SELECT = X_PRIMARY_AUTO_SELECT,
790 FIN_AID_PROGRAM_TYPE= X_FIN_AID_PROGRAM_TYPE,
791 ENROLMENT_CAT = X_ENROLMENT_CAT
792 where ROWID = X_ROWID
793 ;
794 if (sql%notfound) then
795 raise no_data_found;
796 end if;
797
798 After_DML(
799 p_action => 'UPDATE',
800 x_rowid => X_ROWID
801 );
802
803 end UPDATE_ROW;
804
805 procedure ADD_ROW (
806 X_ROWID in out NOCOPY VARCHAR2,
807 X_COURSE_TYPE in VARCHAR2,
808 X_DESCRIPTION in VARCHAR2,
809 X_GOVT_COURSE_TYPE in NUMBER,
810 X_AWARD_COURSE_IND in VARCHAR2,
811 X_COURSE_TYPE_GROUP_CD in VARCHAR2,
812 X_TAC_COURSE_LEVEL in VARCHAR2,
813 X_RESEARCH_TYPE_IND in VARCHAR2,
814 X_CLOSED_IND in VARCHAR2,
815 X_MODE in VARCHAR2 ,
816 X_ORG_ID in NUMBER,
817 X_PRIMARY_AUTO_SELECT in VARCHAR2,
818 X_FIN_AID_PROGRAM_TYPE IN VARCHAR2,
819 X_ENROLMENT_CAT in VARCHAR2
820 ) AS
821 cursor c1 is select rowid from IGS_PS_TYPE_ALL
822 where COURSE_TYPE = X_COURSE_TYPE
823 ;
824 begin
825 open c1;
826 fetch c1 into X_ROWID;
827 if (c1%notfound) then
828 close c1;
829 INSERT_ROW (
830 X_ROWID,
831 X_COURSE_TYPE,
832 X_DESCRIPTION,
833 X_GOVT_COURSE_TYPE,
834 X_AWARD_COURSE_IND,
835 X_COURSE_TYPE_GROUP_CD,
836 X_TAC_COURSE_LEVEL,
837 X_RESEARCH_TYPE_IND,
838 X_CLOSED_IND,
839 X_MODE,
840 X_ORG_ID,
841 X_PRIMARY_AUTO_SELECT,
842 X_FIN_AID_PROGRAM_TYPE,
843 X_ENROLMENT_CAT
844 );
845 return;
846 end if;
847 close c1;
848 UPDATE_ROW (
849 X_ROWID,
850 X_COURSE_TYPE,
851 X_DESCRIPTION,
852 X_GOVT_COURSE_TYPE,
853 X_AWARD_COURSE_IND,
854 X_COURSE_TYPE_GROUP_CD,
855 X_TAC_COURSE_LEVEL,
856 X_RESEARCH_TYPE_IND,
857 X_CLOSED_IND,
858 X_MODE,
859 X_PRIMARY_AUTO_SELECT,
860 X_FIN_AID_PROGRAM_TYPE,
861 X_ENROLMENT_CAT
862 );
863 end ADD_ROW;
864
865
866 end IGS_PS_TYPE_PKG;