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