[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_ST_SNAPSHOT_PKG
Source
1 package body IGS_EN_ST_SNAPSHOT_PKG as
2 /* $Header: IGSEI08B.pls 115.3 2002/11/28 23:33:02 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_EN_ST_SNAPSHOT%RowType;
6 new_references IGS_EN_ST_SNAPSHOT%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_ESS_ID IN NUMBER DEFAULT NULL,
12 x_govt_attendance_type IN VARCHAR2 DEFAULT NULL,
13 x_funding_source IN VARCHAR2 DEFAULT NULL,
14 x_govt_funding_source IN NUMBER DEFAULT NULL,
15 x_major_course IN NUMBER DEFAULT NULL,
16 x_commencing_student_ind IN VARCHAR2 DEFAULT NULL,
17 x_school_leaver IN NUMBER DEFAULT NULL,
18 x_new_to_higher_education IN NUMBER DEFAULT NULL,
19 x_sua_location_cd IN VARCHAR2 DEFAULT NULL,
20 x_unit_class IN VARCHAR2 DEFAULT NULL,
21 x_unit_level IN VARCHAR2 DEFAULT NULL,
22 x_enrolled_dt IN DATE DEFAULT NULL,
23 x_discontinued_dt IN DATE DEFAULT NULL,
24 x_eftsu IN NUMBER DEFAULT NULL,
25 x_weftsu IN NUMBER DEFAULT NULL,
26 x_unit_int_course_level_cd IN VARCHAR2 DEFAULT NULL,
27 x_govt_reportable_ind IN VARCHAR2 DEFAULT NULL,
28 x_snapshot_dt_time IN DATE DEFAULT NULL,
29 x_ci_cal_type IN VARCHAR2 DEFAULT NULL,
30 x_ci_sequence_number IN NUMBER DEFAULT NULL,
31 x_person_id IN NUMBER DEFAULT NULL,
32 x_course_cd IN VARCHAR2 DEFAULT NULL,
33 x_crv_version_number IN NUMBER DEFAULT NULL,
34 x_unit_cd IN VARCHAR2 DEFAULT NULL,
35 x_uv_version_number IN NUMBER DEFAULT NULL,
36 x_sua_cal_type IN VARCHAR2 DEFAULT NULL,
37 x_sua_ci_sequence_number IN NUMBER DEFAULT NULL,
38 x_tr_org_unit_cd IN VARCHAR2 DEFAULT NULL,
39 x_tr_ou_start_dt IN DATE DEFAULT NULL,
40 x_discipline_group_cd IN VARCHAR2 DEFAULT NULL,
41 x_govt_discipline_group_cd IN VARCHAR2 DEFAULT NULL,
42 x_crv_org_unit_cd IN VARCHAR2 DEFAULT NULL,
43 x_crv_ou_start_dt IN DATE DEFAULT NULL,
44 x_course_type IN VARCHAR2 DEFAULT NULL,
45 x_govt_course_type IN NUMBER DEFAULT NULL,
46 x_course_type_group_cd IN VARCHAR2 DEFAULT NULL,
47 x_sca_location_cd IN VARCHAR2 DEFAULT NULL,
48 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
49 x_govt_attendance_mode IN VARCHAR2 DEFAULT NULL,
50 x_attendance_type IN VARCHAR2 DEFAULT NULL,
51 x_creation_date IN DATE DEFAULT NULL,
52 x_created_by IN NUMBER DEFAULT NULL,
53 x_last_update_date IN DATE DEFAULT NULL,
54 x_last_updated_by IN NUMBER DEFAULT NULL,
55 x_last_update_login IN NUMBER DEFAULT NULL
56 ) AS
57
58 CURSOR cur_old_ref_values IS
59 SELECT *
60 FROM IGS_EN_ST_SNAPSHOT
61 WHERE rowid = x_rowid;
62
63 BEGIN
64
65 l_rowid := x_rowid;
66
67 -- Code for setting the Old and New Reference Values.
68 -- Populate Old Values.
69 Open cur_old_ref_values;
70 Fetch cur_old_ref_values INTO old_references;
71 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
72 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
73 IGS_GE_MSG_STACK.ADD;
74 Close cur_old_ref_values;
75 App_Exception.Raise_Exception;
76 Return;
77 END IF;
78 Close cur_old_ref_values;
79
80 -- Populate New Values.
81 new_references.ESS_ID := x_ESS_ID;
82 new_references.govt_attendance_type := x_govt_attendance_type;
83 new_references.funding_source := x_funding_source;
84 new_references.govt_funding_source := x_govt_funding_source;
85 new_references.major_course := x_major_course;
86 new_references.commencing_student_ind := x_commencing_student_ind;
87 new_references.school_leaver := x_school_leaver;
88 new_references.new_to_higher_education := x_new_to_higher_education;
89 new_references.sua_location_cd := x_sua_location_cd;
90 new_references.unit_class := x_unit_class;
91 new_references.unit_level := x_unit_level;
92 new_references.enrolled_dt := x_enrolled_dt;
93 new_references.discontinued_dt := x_discontinued_dt;
94 new_references.eftsu := x_eftsu;
95 new_references.weftsu := x_weftsu;
96 new_references.unit_int_course_level_cd := x_unit_int_course_level_cd;
97 new_references.govt_reportable_ind := x_govt_reportable_ind;
98 new_references.snapshot_dt_time := x_snapshot_dt_time;
99 new_references.ci_cal_type := x_ci_cal_type;
100 new_references.ci_sequence_number := x_ci_sequence_number;
101 new_references.person_id := x_person_id;
102 new_references.course_cd := x_course_cd;
103 new_references.crv_version_number := x_crv_version_number;
104 new_references.unit_cd := x_unit_cd;
105 new_references.uv_version_number := x_uv_version_number;
106 new_references.sua_cal_type := x_sua_cal_type;
107 new_references.sua_ci_sequence_number := x_sua_ci_sequence_number;
108 new_references.tr_org_unit_cd := x_tr_org_unit_cd;
109 new_references.tr_ou_start_dt := x_tr_ou_start_dt;
110 new_references.discipline_group_cd := x_discipline_group_cd;
111 new_references.govt_discipline_group_cd := x_govt_discipline_group_cd;
112 new_references.crv_org_unit_cd := x_crv_org_unit_cd;
113 new_references.crv_ou_start_dt := x_crv_ou_start_dt;
114 new_references.course_type := x_course_type;
115 new_references.govt_course_type := x_govt_course_type;
116 new_references.course_type_group_cd := x_course_type_group_cd;
117 new_references.sca_location_cd := x_sca_location_cd;
118 new_references.attendance_mode := x_attendance_mode;
119 new_references.govt_attendance_mode := x_govt_attendance_mode;
120 new_references.attendance_type := x_attendance_type;
121 IF (p_action = 'UPDATE') THEN
122 new_references.creation_date := old_references.creation_date;
123 new_references.created_by := old_references.created_by;
124 ELSE
125 new_references.creation_date := x_creation_date;
126 new_references.created_by := x_created_by;
127 END IF;
128 new_references.last_update_date := x_last_update_date;
129 new_references.last_updated_by := x_last_updated_by;
130 new_references.last_update_login := x_last_update_login;
131
132 END Set_Column_Values;
133
134
135
136 PROCEDURE Check_Parent_Existance AS
137 BEGIN
138
139 IF (((old_references.snapshot_dt_time = new_references.snapshot_dt_time)) OR
140 ((new_references.snapshot_dt_time IS NULL))) THEN
141 NULL;
142 ELSE
143 IF NOT IGS_EN_ST_SPSHT_CTL_PKG.Get_PK_For_Validation (
144 new_references.snapshot_dt_time
145 ) THEN
146 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
147 IGS_GE_MSG_STACK.ADD;
148 App_Exception.Raise_Exception;
149
150 END IF;
151 END IF;
152
153 END Check_Parent_Existance;
154
155 PROCEDURE GET_FK_IGS_EN_ST_SPSHT_CTL (
156 x_snapshot_dt_time IN DATE
157 ) AS
158
159 CURSOR cur_rowid IS
160 SELECT rowid
161 FROM IGS_EN_ST_SNAPSHOT
162 WHERE snapshot_dt_time = x_snapshot_dt_time ;
163
164 lv_rowid cur_rowid%RowType;
165
166 BEGIN
167
168 Open cur_rowid;
169 Fetch cur_rowid INTO lv_rowid;
170 IF (cur_rowid%FOUND) THEN
171 Fnd_Message.Set_Name ('IGS', 'IGS_EN_ESS_ESSC_FK');
172 IGS_GE_MSG_STACK.ADD;
173 Close cur_rowid;
174 App_Exception.Raise_Exception;
175 Return;
176 END IF;
177 Close cur_rowid;
178
179 END GET_FK_IGS_EN_ST_SPSHT_CTL;
180
181 FUNCTION Get_PK_For_Validation (
182 x_ESS_ID IN NUMBER
183 ) RETURN BOOLEAN as
184
185 CURSOR cur_rowid IS
186 SELECT rowid
187 FROM IGS_EN_ST_SNAPSHOT
188 WHERE ESS_ID = x_ESS_ID
189 FOR UPDATE NOWAIT;
190
191 lv_rowid cur_rowid%RowType;
192
193 BEGIN
194
195 Open cur_rowid;
196 Fetch cur_rowid INTO lv_rowid;
197
198 IF (cur_rowid%FOUND) THEN
199 Close cur_rowid;
200 Return(TRUE);
201 ELSE
202 Close cur_rowid;
203 Return(FALSE);
204 END IF;
205
206 END Get_PK_For_Validation;
207
208 PROCEDURE Before_DML (
209 p_action IN VARCHAR2,
210 x_rowid IN VARCHAR2 DEFAULT NULL,
211 x_ESS_ID IN NUMBER DEFAULT NULL,
212 x_govt_attendance_type IN VARCHAR2 DEFAULT NULL,
213 x_funding_source IN VARCHAR2 DEFAULT NULL,
214 x_govt_funding_source IN NUMBER DEFAULT NULL,
215 x_major_course IN NUMBER DEFAULT NULL,
216 x_commencing_student_ind IN VARCHAR2 DEFAULT NULL,
217 x_school_leaver IN NUMBER DEFAULT NULL,
218 x_new_to_higher_education IN NUMBER DEFAULT NULL,
219 x_sua_location_cd IN VARCHAR2 DEFAULT NULL,
220 x_unit_class IN VARCHAR2 DEFAULT NULL,
221 x_unit_level IN VARCHAR2 DEFAULT NULL,
222 x_enrolled_dt IN DATE DEFAULT NULL,
223 x_discontinued_dt IN DATE DEFAULT NULL,
224 x_eftsu IN NUMBER DEFAULT NULL,
225 x_weftsu IN NUMBER DEFAULT NULL,
226 x_unit_int_course_level_cd IN VARCHAR2 DEFAULT NULL,
227 x_govt_reportable_ind IN VARCHAR2 DEFAULT NULL,
228 x_snapshot_dt_time IN DATE DEFAULT NULL,
229 x_ci_cal_type IN VARCHAR2 DEFAULT NULL,
230 x_ci_sequence_number IN NUMBER DEFAULT NULL,
231 x_person_id IN NUMBER DEFAULT NULL,
232 x_course_cd IN VARCHAR2 DEFAULT NULL,
233 x_crv_version_number IN NUMBER DEFAULT NULL,
234 x_unit_cd IN VARCHAR2 DEFAULT NULL,
235 x_uv_version_number IN NUMBER DEFAULT NULL,
236 x_sua_cal_type IN VARCHAR2 DEFAULT NULL,
237 x_sua_ci_sequence_number IN NUMBER DEFAULT NULL,
238 x_tr_org_unit_cd IN VARCHAR2 DEFAULT NULL,
239 x_tr_ou_start_dt IN DATE DEFAULT NULL,
240 x_discipline_group_cd IN VARCHAR2 DEFAULT NULL,
241 x_govt_discipline_group_cd IN VARCHAR2 DEFAULT NULL,
242 x_crv_org_unit_cd IN VARCHAR2 DEFAULT NULL,
243 x_crv_ou_start_dt IN DATE DEFAULT NULL,
244 x_course_type IN VARCHAR2 DEFAULT NULL,
245 x_govt_course_type IN NUMBER DEFAULT NULL,
246 x_course_type_group_cd IN VARCHAR2 DEFAULT NULL,
247 x_sca_location_cd IN VARCHAR2 DEFAULT NULL,
248 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
249 x_govt_attendance_mode IN VARCHAR2 DEFAULT NULL,
250 x_attendance_type IN VARCHAR2 DEFAULT NULL,
251 x_creation_date IN DATE DEFAULT NULL,
252 x_created_by IN NUMBER DEFAULT NULL,
253 x_last_update_date IN DATE DEFAULT NULL,
254 x_last_updated_by IN NUMBER DEFAULT NULL,
255 x_last_update_login IN NUMBER DEFAULT NULL
256 ) AS
257 BEGIN
258
259 Set_Column_Values (
260 p_action,
261 x_rowid,
262 x_ESS_ID,
263 x_govt_attendance_type,
264 x_funding_source,
265 x_govt_funding_source,
266 x_major_course,
267 x_commencing_student_ind,
268 x_school_leaver,
269 x_new_to_higher_education,
270 x_sua_location_cd,
271 x_unit_class,
272 x_unit_level,
273 x_enrolled_dt,
274 x_discontinued_dt,
275 x_eftsu,
276 x_weftsu,
277 x_unit_int_course_level_cd,
278 x_govt_reportable_ind,
279 x_snapshot_dt_time,
280 x_ci_cal_type,
281 x_ci_sequence_number,
282 x_person_id,
283 x_course_cd,
284 x_crv_version_number,
285 x_unit_cd,
286 x_uv_version_number,
287 x_sua_cal_type,
288 x_sua_ci_sequence_number,
289 x_tr_org_unit_cd,
290 x_tr_ou_start_dt,
291 x_discipline_group_cd,
292 x_govt_discipline_group_cd,
293 x_crv_org_unit_cd,
294 x_crv_ou_start_dt,
295 x_course_type,
296 x_govt_course_type,
297 x_course_type_group_cd,
298 x_sca_location_cd,
299 x_attendance_mode,
300 x_govt_attendance_mode,
301 x_attendance_type,
302 x_creation_date,
303 x_created_by,
304 x_last_update_date,
305 x_last_updated_by,
306 x_last_update_login
307 );
308
309 IF (p_action = 'INSERT') THEN
310 -- Call all the procedures related to Before Insert.
311 IF Get_PK_For_Validation(
312 new_references.ESS_ID
313 ) THEN
314
315 Fnd_message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
316 IGS_GE_MSG_STACK.ADD;
317 App_Exception.Raise_Exception;
318
319 END IF;
320 Check_Parent_Existance;
321
322 ELSIF (p_action = 'UPDATE') THEN
323 -- Call all the procedures related to Before Update.
324 Check_Parent_Existance;
325 ELSIF (p_action = 'DELETE') THEN
326 -- Call all the procedures related to Before Delete.
327 Null;
328 ELSIF (p_action = 'VALIDATE_INSERT') THEN
329 IF Get_PK_For_Validation (
330 new_references.ESS_ID
331 ) THEN
332 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
333 IGS_GE_MSG_STACK.ADD;
334 App_Exception.Raise_Exception;
335 END IF;
336 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
337 null;
338 ELSIF (p_action = 'VALIDATE_DELETE') THEN
339 null;
340 END IF;
341
342 END Before_DML;
343
344 PROCEDURE After_DML (
345 p_action IN VARCHAR2,
346 x_rowid IN VARCHAR2
347 ) AS
348 BEGIN
349
350 l_rowid := x_rowid;
351
352 IF (p_action = 'INSERT') THEN
353 -- Call all the procedures related to After Insert.
354 Null;
355 ELSIF (p_action = 'UPDATE') THEN
356 -- Call all the procedures related to After Update.
357 Null;
358 ELSIF (p_action = 'DELETE') THEN
359 -- Call all the procedures related to After Delete.
360 Null;
361 END IF;
362
363 END After_DML;
364 procedure INSERT_ROW (
365 X_ROWID in out NOCOPY VARCHAR2,
366 X_ESS_ID in out NOCOPY NUMBER,
367 X_SNAPSHOT_DT_TIME in DATE,
368 X_CI_CAL_TYPE in VARCHAR2,
369 X_CI_SEQUENCE_NUMBER in NUMBER,
370 X_PERSON_ID in NUMBER,
371 X_COURSE_CD in VARCHAR2,
372 X_CRV_VERSION_NUMBER in NUMBER,
373 X_UNIT_CD in VARCHAR2,
374 X_UV_VERSION_NUMBER in NUMBER,
375 X_SUA_CAL_TYPE in VARCHAR2,
376 X_SUA_CI_SEQUENCE_NUMBER in NUMBER,
377 X_TR_ORG_UNIT_CD in VARCHAR2,
378 X_TR_OU_START_DT in DATE,
379 X_DISCIPLINE_GROUP_CD in VARCHAR2,
380 X_GOVT_DISCIPLINE_GROUP_CD in VARCHAR2,
381 X_CRV_ORG_UNIT_CD in VARCHAR2,
382 X_CRV_OU_START_DT in DATE,
383 X_COURSE_TYPE in VARCHAR2,
384 X_GOVT_COURSE_TYPE in NUMBER,
385 X_COURSE_TYPE_GROUP_CD in VARCHAR2,
389 X_ATTENDANCE_TYPE in VARCHAR2,
386 X_SCA_LOCATION_CD in VARCHAR2,
387 X_ATTENDANCE_MODE in VARCHAR2,
388 X_GOVT_ATTENDANCE_MODE in VARCHAR2,
390 X_GOVT_ATTENDANCE_TYPE in VARCHAR2,
391 X_FUNDING_SOURCE in VARCHAR2,
392 X_GOVT_FUNDING_SOURCE in NUMBER,
393 X_MAJOR_COURSE in NUMBER,
394 X_COMMENCING_STUDENT_IND in VARCHAR2,
395 X_SCHOOL_LEAVER in NUMBER,
396 X_NEW_TO_HIGHER_EDUCATION in NUMBER,
397 X_SUA_LOCATION_CD in VARCHAR2,
398 X_UNIT_CLASS in VARCHAR2,
399 X_UNIT_LEVEL in VARCHAR2,
400 X_ENROLLED_DT in DATE,
401 X_DISCONTINUED_DT in DATE,
402 X_EFTSU in NUMBER,
403 X_WEFTSU in NUMBER,
404 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
405 X_GOVT_REPORTABLE_IND in VARCHAR2,
406 X_MODE in VARCHAR2 default 'R'
407 ) AS
408 cursor C is select ROWID from IGS_EN_ST_SNAPSHOT
409 where ESS_ID = X_ESS_ID;
410 X_LAST_UPDATE_DATE DATE;
411 X_LAST_UPDATED_BY NUMBER;
412 X_LAST_UPDATE_LOGIN NUMBER;
413 X_REQUEST_ID NUMBER;
414 X_PROGRAM_ID NUMBER;
415 X_PROGRAM_APPLICATION_ID NUMBER;
416 X_PROGRAM_UPDATE_DATE DATE;
417 begin
418 X_LAST_UPDATE_DATE := SYSDATE;
419 if(X_MODE = 'I') then
420 X_LAST_UPDATED_BY := 1;
421 X_LAST_UPDATE_LOGIN := 0;
422 elsif (X_MODE = 'R') then
423 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
424 if X_LAST_UPDATED_BY is NULL then
425 X_LAST_UPDATED_BY := -1;
426 end if;
427 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
428 if X_LAST_UPDATE_LOGIN is NULL then
429 X_LAST_UPDATE_LOGIN := -1;
430 end if;
431
432 x_request_id := fnd_global.conc_request_id;
433 x_program_id := fnd_global.conc_program_id;
434 x_program_application_id := fnd_global.prog_appl_id;
435
436 if (x_request_id = -1) then
437 x_request_id := NULL;
438 x_program_id := NULL;
439 x_program_application_id := NULL;
440 x_program_update_date := NULL;
441 else
442 x_program_update_date := SYSDATE;
443 end if;
444
445 else
446 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
447 IGS_GE_MSG_STACK.ADD;
448 app_exception.raise_exception;
449 end if;
450
451 SELECT IGS_EN_ST_SNAPSHOT_ESS_ID_S.Nextval
452 INTO X_ESS_ID
453 FROM DUAL;
454
455 Before_DML(
456 p_action=>'INSERT',
457 x_rowid=>X_ROWID,
458 x_attendance_mode=>X_ATTENDANCE_MODE,
459 x_attendance_type=>X_ATTENDANCE_TYPE,
460 x_ci_cal_type=>X_CI_CAL_TYPE,
461 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
462 x_commencing_student_ind=>X_COMMENCING_STUDENT_IND,
463 x_course_cd=>X_COURSE_CD,
464 x_course_type=>X_COURSE_TYPE,
465 x_course_type_group_cd=>X_COURSE_TYPE_GROUP_CD,
466 x_crv_org_unit_cd=>X_CRV_ORG_UNIT_CD,
467 x_crv_ou_start_dt=>X_CRV_OU_START_DT,
468 x_crv_version_number=>X_CRV_VERSION_NUMBER,
469 x_discipline_group_cd=>X_DISCIPLINE_GROUP_CD,
470 x_discontinued_dt=>X_DISCONTINUED_DT,
471 x_eftsu=>X_EFTSU,
472 x_enrolled_dt=>X_ENROLLED_DT,
473 x_funding_source=>X_FUNDING_SOURCE,
474 x_govt_attendance_mode=>X_GOVT_ATTENDANCE_MODE,
475 x_govt_attendance_type=>X_GOVT_ATTENDANCE_TYPE,
476 x_govt_course_type=>X_GOVT_COURSE_TYPE,
477 x_govt_discipline_group_cd=>X_GOVT_DISCIPLINE_GROUP_CD,
478 x_govt_funding_source=>X_GOVT_FUNDING_SOURCE,
479 x_govt_reportable_ind=>X_GOVT_REPORTABLE_IND,
480 x_major_course=>X_MAJOR_COURSE,
481 x_new_to_higher_education=>X_NEW_TO_HIGHER_EDUCATION,
482 x_person_id=>X_PERSON_ID,
483 x_sca_location_cd=>X_SCA_LOCATION_CD,
484 x_school_leaver=>X_SCHOOL_LEAVER,
485 x_ESS_ID=>X_ESS_ID,
486 x_snapshot_dt_time=>X_SNAPSHOT_DT_TIME,
487 x_sua_cal_type=>X_SUA_CAL_TYPE,
488 x_sua_ci_sequence_number=>X_SUA_CI_SEQUENCE_NUMBER,
489 x_sua_location_cd=>X_SUA_LOCATION_CD,
490 x_tr_org_unit_cd=>X_TR_ORG_UNIT_CD,
491 x_tr_ou_start_dt=>X_TR_OU_START_DT,
492 x_unit_cd=>X_UNIT_CD,
493 x_unit_class=>X_UNIT_CLASS,
494 x_unit_int_course_level_cd=>X_UNIT_INT_COURSE_LEVEL_CD,
495 x_unit_level=>X_UNIT_LEVEL,
496 x_uv_version_number=>X_UV_VERSION_NUMBER,
497 x_weftsu=>X_WEFTSU,
498 x_creation_date=>X_LAST_UPDATE_DATE,
499 x_created_by=>X_LAST_UPDATED_BY,
500 x_last_update_date=>X_LAST_UPDATE_DATE,
501 x_last_updated_by=>X_LAST_UPDATED_BY,
502 x_last_update_login=>X_LAST_UPDATE_LOGIN
503 );
504
505 insert into IGS_EN_ST_SNAPSHOT (
506 ESS_ID,
507 SNAPSHOT_DT_TIME,
508 CI_CAL_TYPE,
509 CI_SEQUENCE_NUMBER,
510 PERSON_ID,
511 COURSE_CD,
512 CRV_VERSION_NUMBER,
513 UNIT_CD,
514 UV_VERSION_NUMBER,
515 SUA_CAL_TYPE,
516 SUA_CI_SEQUENCE_NUMBER,
517 TR_ORG_UNIT_CD,
518 TR_OU_START_DT,
519 DISCIPLINE_GROUP_CD,
520 GOVT_DISCIPLINE_GROUP_CD,
521 CRV_ORG_UNIT_CD,
522 CRV_OU_START_DT,
523 COURSE_TYPE,
524 GOVT_COURSE_TYPE,
525 COURSE_TYPE_GROUP_CD,
526 SCA_LOCATION_CD,
527 ATTENDANCE_MODE,
528 GOVT_ATTENDANCE_MODE,
529 ATTENDANCE_TYPE,
530 GOVT_ATTENDANCE_TYPE,
531 FUNDING_SOURCE,
535 SCHOOL_LEAVER,
532 GOVT_FUNDING_SOURCE,
533 MAJOR_COURSE,
534 COMMENCING_STUDENT_IND,
536 NEW_TO_HIGHER_EDUCATION,
537 SUA_LOCATION_CD,
538 UNIT_CLASS,
539 UNIT_LEVEL,
540 ENROLLED_DT,
541 DISCONTINUED_DT,
542 EFTSU,
543 WEFTSU,
544 UNIT_INT_COURSE_LEVEL_CD,
545 GOVT_REPORTABLE_IND,
546 CREATION_DATE,
547 CREATED_BY,
548 LAST_UPDATE_DATE,
549 LAST_UPDATED_BY,
550 LAST_UPDATE_LOGIN,
551 REQUEST_ID,
552 PROGRAM_ID,
553 PROGRAM_APPLICATION_ID,
554 PROGRAM_UPDATE_DATE
555 ) values (
556 new_references.ESS_ID,
557 new_references.SNAPSHOT_DT_TIME,
558 new_references.CI_CAL_TYPE,
559 new_references.CI_SEQUENCE_NUMBER,
560 new_references.PERSON_ID,
561 new_references.COURSE_CD,
562 new_references.CRV_VERSION_NUMBER,
563 new_references.UNIT_CD,
564 new_references.UV_VERSION_NUMBER,
565 new_references.SUA_CAL_TYPE,
566 new_references.SUA_CI_SEQUENCE_NUMBER,
567 new_references.TR_ORG_UNIT_CD,
568 new_references.TR_OU_START_DT,
569 new_references.DISCIPLINE_GROUP_CD,
570 new_references.GOVT_DISCIPLINE_GROUP_CD,
571 new_references.CRV_ORG_UNIT_CD,
572 new_references.CRV_OU_START_DT,
573 new_references.COURSE_TYPE,
574 new_references.GOVT_COURSE_TYPE,
575 new_references.COURSE_TYPE_GROUP_CD,
576 new_references.SCA_LOCATION_CD,
577 new_references.ATTENDANCE_MODE,
578 new_references.GOVT_ATTENDANCE_MODE,
579 new_references.ATTENDANCE_TYPE,
580 new_references.GOVT_ATTENDANCE_TYPE,
581 new_references.FUNDING_SOURCE,
582 new_references.GOVT_FUNDING_SOURCE,
583 new_references.MAJOR_COURSE,
584 new_references.COMMENCING_STUDENT_IND,
585 new_references.SCHOOL_LEAVER,
586 new_references.NEW_TO_HIGHER_EDUCATION,
587 new_references.SUA_LOCATION_CD,
588 new_references.UNIT_CLASS,
589 new_references.UNIT_LEVEL,
590 new_references.ENROLLED_DT,
591 new_references.DISCONTINUED_DT,
592 new_references.EFTSU,
593 new_references.WEFTSU,
594 new_references.UNIT_INT_COURSE_LEVEL_CD,
595 new_references.GOVT_REPORTABLE_IND,
596 X_LAST_UPDATE_DATE,
597 X_LAST_UPDATED_BY,
598 X_LAST_UPDATE_DATE,
599 X_LAST_UPDATED_BY,
600 X_LAST_UPDATE_LOGIN,
601 X_REQUEST_ID,
602 X_PROGRAM_ID,
603 X_PROGRAM_APPLICATION_ID,
604 X_PROGRAM_UPDATE_DATE
605 );
606
607 open c;
608 fetch c into X_ROWID;
609 if (c%notfound) then
610 close c;
611 raise no_data_found;
612 end if;
613 close c;
614 After_DML(
615 p_action => 'INSERT',
616 x_rowid => X_ROWID
617 );
618 end INSERT_ROW;
619
620 procedure LOCK_ROW (
621 X_ROWID in VARCHAR2,
622 X_ESS_ID in NUMBER,
623 X_SNAPSHOT_DT_TIME in DATE,
624 X_CI_CAL_TYPE in VARCHAR2,
625 X_CI_SEQUENCE_NUMBER in NUMBER,
626 X_PERSON_ID in NUMBER,
627 X_COURSE_CD in VARCHAR2,
628 X_CRV_VERSION_NUMBER in NUMBER,
629 X_UNIT_CD in VARCHAR2,
630 X_UV_VERSION_NUMBER in NUMBER,
631 X_SUA_CAL_TYPE in VARCHAR2,
632 X_SUA_CI_SEQUENCE_NUMBER in NUMBER,
633 X_TR_ORG_UNIT_CD in VARCHAR2,
634 X_TR_OU_START_DT in DATE,
635 X_DISCIPLINE_GROUP_CD in VARCHAR2,
636 X_GOVT_DISCIPLINE_GROUP_CD in VARCHAR2,
637 X_CRV_ORG_UNIT_CD in VARCHAR2,
638 X_CRV_OU_START_DT in DATE,
639 X_COURSE_TYPE in VARCHAR2,
640 X_GOVT_COURSE_TYPE in NUMBER,
641 X_COURSE_TYPE_GROUP_CD in VARCHAR2,
642 X_SCA_LOCATION_CD in VARCHAR2,
643 X_ATTENDANCE_MODE in VARCHAR2,
644 X_GOVT_ATTENDANCE_MODE in VARCHAR2,
645 X_ATTENDANCE_TYPE in VARCHAR2,
646 X_GOVT_ATTENDANCE_TYPE in VARCHAR2,
647 X_FUNDING_SOURCE in VARCHAR2,
648 X_GOVT_FUNDING_SOURCE in NUMBER,
649 X_MAJOR_COURSE in NUMBER,
650 X_COMMENCING_STUDENT_IND in VARCHAR2,
651 X_SCHOOL_LEAVER in NUMBER,
652 X_NEW_TO_HIGHER_EDUCATION in NUMBER,
653 X_SUA_LOCATION_CD in VARCHAR2,
654 X_UNIT_CLASS in VARCHAR2,
655 X_UNIT_LEVEL in VARCHAR2,
656 X_ENROLLED_DT in DATE,
657 X_DISCONTINUED_DT in DATE,
658 X_EFTSU in NUMBER,
659 X_WEFTSU in NUMBER,
660 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
661 X_GOVT_REPORTABLE_IND in VARCHAR2
662 ) AS
663 cursor c1 is select
664 SNAPSHOT_DT_TIME,
665 CI_CAL_TYPE,
666 CI_SEQUENCE_NUMBER,
667 PERSON_ID,
668 COURSE_CD,
669 CRV_VERSION_NUMBER,
670 UNIT_CD,
671 UV_VERSION_NUMBER,
672 SUA_CAL_TYPE,
673 SUA_CI_SEQUENCE_NUMBER,
674 TR_ORG_UNIT_CD,
675 TR_OU_START_DT,
676 DISCIPLINE_GROUP_CD,
677 GOVT_DISCIPLINE_GROUP_CD,
678 CRV_ORG_UNIT_CD,
679 CRV_OU_START_DT,
680 COURSE_TYPE,
681 GOVT_COURSE_TYPE,
682 COURSE_TYPE_GROUP_CD,
683 SCA_LOCATION_CD,
684 ATTENDANCE_MODE,
685 GOVT_ATTENDANCE_MODE,
686 ATTENDANCE_TYPE,
687 GOVT_ATTENDANCE_TYPE,
688 FUNDING_SOURCE,
692 SCHOOL_LEAVER,
689 GOVT_FUNDING_SOURCE,
690 MAJOR_COURSE,
691 COMMENCING_STUDENT_IND,
693 NEW_TO_HIGHER_EDUCATION,
694 SUA_LOCATION_CD,
695 UNIT_CLASS,
696 UNIT_LEVEL,
697 ENROLLED_DT,
698 DISCONTINUED_DT,
699 EFTSU,
700 WEFTSU,
701 UNIT_INT_COURSE_LEVEL_CD,
702 GOVT_REPORTABLE_IND
703 from IGS_EN_ST_SNAPSHOT
704 where rowid = x_rowid for update nowait;
705 tlinfo c1%rowtype;
706
707 begin
708 open c1;
709 fetch c1 into tlinfo;
710 if (c1%notfound) then
711 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
712 IGS_GE_MSG_STACK.ADD;
713 close c1;
714 app_exception.raise_exception;
715 return;
716 end if;
717 close c1;
718
719 if ( (tlinfo.SNAPSHOT_DT_TIME = X_SNAPSHOT_DT_TIME)
720 AND (tlinfo.CI_CAL_TYPE = X_CI_CAL_TYPE)
721 AND (tlinfo.CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER)
722 AND (tlinfo.PERSON_ID = X_PERSON_ID)
723 AND (tlinfo.COURSE_CD = X_COURSE_CD)
724 AND (tlinfo.CRV_VERSION_NUMBER = X_CRV_VERSION_NUMBER)
725 AND (tlinfo.UNIT_CD = X_UNIT_CD)
726 AND (tlinfo.UV_VERSION_NUMBER = X_UV_VERSION_NUMBER)
727 AND (tlinfo.SUA_CAL_TYPE = X_SUA_CAL_TYPE)
728 AND (tlinfo.SUA_CI_SEQUENCE_NUMBER = X_SUA_CI_SEQUENCE_NUMBER)
729 AND (tlinfo.TR_ORG_UNIT_CD = X_TR_ORG_UNIT_CD)
730 AND (tlinfo.TR_OU_START_DT = X_TR_OU_START_DT)
731 AND (tlinfo.DISCIPLINE_GROUP_CD = X_DISCIPLINE_GROUP_CD)
732 AND (tlinfo.GOVT_DISCIPLINE_GROUP_CD = X_GOVT_DISCIPLINE_GROUP_CD)
733 AND (tlinfo.CRV_ORG_UNIT_CD = X_CRV_ORG_UNIT_CD)
734 AND (tlinfo.CRV_OU_START_DT = X_CRV_OU_START_DT)
735 AND (tlinfo.COURSE_TYPE = X_COURSE_TYPE)
736 AND (tlinfo.GOVT_COURSE_TYPE = X_GOVT_COURSE_TYPE)
737 AND ((tlinfo.COURSE_TYPE_GROUP_CD = X_COURSE_TYPE_GROUP_CD)
738 OR ((tlinfo.COURSE_TYPE_GROUP_CD is null)
739 AND (X_COURSE_TYPE_GROUP_CD is null)))
740 AND (tlinfo.SCA_LOCATION_CD = X_SCA_LOCATION_CD)
741 AND ((tlinfo.ATTENDANCE_MODE = X_ATTENDANCE_MODE)
742 OR ((tlinfo.ATTENDANCE_MODE is null)
743 AND (X_ATTENDANCE_MODE is null)))
744 AND ((tlinfo.GOVT_ATTENDANCE_MODE = X_GOVT_ATTENDANCE_MODE)
745 OR ((tlinfo.GOVT_ATTENDANCE_MODE is null)
746 AND (X_GOVT_ATTENDANCE_MODE is null)))
747 AND (tlinfo.ATTENDANCE_TYPE = X_ATTENDANCE_TYPE)
748 AND (tlinfo.GOVT_ATTENDANCE_TYPE = X_GOVT_ATTENDANCE_TYPE)
749 AND ((tlinfo.FUNDING_SOURCE = X_FUNDING_SOURCE)
750 OR ((tlinfo.FUNDING_SOURCE is null)
751 AND (X_FUNDING_SOURCE is null)))
752 AND ((tlinfo.GOVT_FUNDING_SOURCE = X_GOVT_FUNDING_SOURCE)
753 OR ((tlinfo.GOVT_FUNDING_SOURCE is null)
754 AND (X_GOVT_FUNDING_SOURCE is null)))
755 AND (tlinfo.MAJOR_COURSE = X_MAJOR_COURSE)
756 AND (tlinfo.COMMENCING_STUDENT_IND = X_COMMENCING_STUDENT_IND)
757 AND (tlinfo.SCHOOL_LEAVER = X_SCHOOL_LEAVER)
758 AND (tlinfo.NEW_TO_HIGHER_EDUCATION = X_NEW_TO_HIGHER_EDUCATION)
759 AND (tlinfo.SUA_LOCATION_CD = X_SUA_LOCATION_CD)
760 AND (tlinfo.UNIT_CLASS = X_UNIT_CLASS)
761 AND (tlinfo.UNIT_LEVEL = X_UNIT_LEVEL)
762 AND (tlinfo.ENROLLED_DT = X_ENROLLED_DT)
763 AND ((tlinfo.DISCONTINUED_DT = X_DISCONTINUED_DT)
764 OR ((tlinfo.DISCONTINUED_DT is null)
765 AND (X_DISCONTINUED_DT is null)))
766 AND (tlinfo.EFTSU = X_EFTSU)
767 AND (tlinfo.WEFTSU = X_WEFTSU)
768 AND ((tlinfo.UNIT_INT_COURSE_LEVEL_CD = X_UNIT_INT_COURSE_LEVEL_CD)
769 OR ((tlinfo.UNIT_INT_COURSE_LEVEL_CD is null)
770 AND (X_UNIT_INT_COURSE_LEVEL_CD is null)))
771 AND (tlinfo.GOVT_REPORTABLE_IND = X_GOVT_REPORTABLE_IND)
772 ) then
773 null;
774 else
775 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
776 IGS_GE_MSG_STACK.ADD;
777 app_exception.raise_exception;
778 end if;
779 return;
780 end LOCK_ROW;
781
782 procedure UPDATE_ROW (
783 x_rowid IN VARCHAR2,
784 X_ESS_ID in NUMBER,
785 X_SNAPSHOT_DT_TIME in DATE,
786 X_CI_CAL_TYPE in VARCHAR2,
787 X_CI_SEQUENCE_NUMBER in NUMBER,
788 X_PERSON_ID in NUMBER,
789 X_COURSE_CD in VARCHAR2,
790 X_CRV_VERSION_NUMBER in NUMBER,
791 X_UNIT_CD in VARCHAR2,
792 X_UV_VERSION_NUMBER in NUMBER,
793 X_SUA_CAL_TYPE in VARCHAR2,
794 X_SUA_CI_SEQUENCE_NUMBER in NUMBER,
795 X_TR_ORG_UNIT_CD in VARCHAR2,
796 X_TR_OU_START_DT in DATE,
797 X_DISCIPLINE_GROUP_CD in VARCHAR2,
798 X_GOVT_DISCIPLINE_GROUP_CD in VARCHAR2,
799 X_CRV_ORG_UNIT_CD in VARCHAR2,
800 X_CRV_OU_START_DT in DATE,
801 X_COURSE_TYPE in VARCHAR2,
802 X_GOVT_COURSE_TYPE in NUMBER,
803 X_COURSE_TYPE_GROUP_CD in VARCHAR2,
804 X_SCA_LOCATION_CD in VARCHAR2,
805 X_ATTENDANCE_MODE in VARCHAR2,
806 X_GOVT_ATTENDANCE_MODE in VARCHAR2,
807 X_ATTENDANCE_TYPE in VARCHAR2,
808 X_GOVT_ATTENDANCE_TYPE in VARCHAR2,
809 X_FUNDING_SOURCE in VARCHAR2,
810 X_GOVT_FUNDING_SOURCE in NUMBER,
811 X_MAJOR_COURSE in NUMBER,
812 X_COMMENCING_STUDENT_IND in VARCHAR2,
813 X_SCHOOL_LEAVER in NUMBER,
814 X_NEW_TO_HIGHER_EDUCATION in NUMBER,
818 X_ENROLLED_DT in DATE,
815 X_SUA_LOCATION_CD in VARCHAR2,
816 X_UNIT_CLASS in VARCHAR2,
817 X_UNIT_LEVEL in VARCHAR2,
819 X_DISCONTINUED_DT in DATE,
820 X_EFTSU in NUMBER,
821 X_WEFTSU in NUMBER,
822 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
823 X_GOVT_REPORTABLE_IND in VARCHAR2,
824 X_MODE in VARCHAR2 default 'R'
825 ) AS
826 X_LAST_UPDATE_DATE DATE;
827 X_LAST_UPDATED_BY NUMBER;
828 X_LAST_UPDATE_LOGIN NUMBER;
829 X_REQUEST_ID NUMBER;
830 X_PROGRAM_ID NUMBER;
831 X_PROGRAM_APPLICATION_ID NUMBER;
832 X_PROGRAM_UPDATE_DATE DATE;
833 begin
834 X_LAST_UPDATE_DATE := SYSDATE;
835 if(X_MODE = 'I') then
836 X_LAST_UPDATED_BY := 1;
837 X_LAST_UPDATE_LOGIN := 0;
838 elsif (X_MODE = 'R') then
839 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
840 if X_LAST_UPDATED_BY is NULL then
841 X_LAST_UPDATED_BY := -1;
842 end if;
843 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
844 if X_LAST_UPDATE_LOGIN is NULL then
845 X_LAST_UPDATE_LOGIN := -1;
846 end if;
847 else
848 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
849 IGS_GE_MSG_STACK.ADD;
850 app_exception.raise_exception;
851 end if;
852
853 Before_DML(
854 p_action=>'UPDATE',
855 x_rowid=>X_ROWID,
856 x_attendance_mode=>X_ATTENDANCE_MODE,
857 x_attendance_type=>X_ATTENDANCE_TYPE,
858 x_ci_cal_type=>X_CI_CAL_TYPE,
859 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
860 x_commencing_student_ind=>X_COMMENCING_STUDENT_IND,
861 x_course_cd=>X_COURSE_CD,
862 x_course_type=>X_COURSE_TYPE,
863 x_course_type_group_cd=>X_COURSE_TYPE_GROUP_CD,
864 x_crv_org_unit_cd=>X_CRV_ORG_UNIT_CD,
865 x_crv_ou_start_dt=>X_CRV_OU_START_DT,
866 x_crv_version_number=>X_CRV_VERSION_NUMBER,
867 x_discipline_group_cd=>X_DISCIPLINE_GROUP_CD,
868 x_discontinued_dt=>X_DISCONTINUED_DT,
869 x_eftsu=>X_EFTSU,
870 x_enrolled_dt=>X_ENROLLED_DT,
871 x_funding_source=>X_FUNDING_SOURCE,
872 x_govt_attendance_mode=>X_GOVT_ATTENDANCE_MODE,
873 x_govt_attendance_type=>X_GOVT_ATTENDANCE_TYPE,
874 x_govt_course_type=>X_GOVT_COURSE_TYPE,
875 x_govt_discipline_group_cd=>X_GOVT_DISCIPLINE_GROUP_CD,
876 x_govt_funding_source=>X_GOVT_FUNDING_SOURCE,
877 x_govt_reportable_ind=>X_GOVT_REPORTABLE_IND,
878 x_major_course=>X_MAJOR_COURSE,
879 x_new_to_higher_education=>X_NEW_TO_HIGHER_EDUCATION,
880 x_person_id=>X_PERSON_ID,
881 x_sca_location_cd=>X_SCA_LOCATION_CD,
882 x_school_leaver=>X_SCHOOL_LEAVER,
883 x_ESS_ID=>X_ESS_ID,
884 x_snapshot_dt_time=>X_SNAPSHOT_DT_TIME,
885 x_sua_cal_type=>X_SUA_CAL_TYPE,
886 x_sua_ci_sequence_number=>X_SUA_CI_SEQUENCE_NUMBER,
887 x_sua_location_cd=>X_SUA_LOCATION_CD,
888 x_tr_org_unit_cd=>X_TR_ORG_UNIT_CD,
889 x_tr_ou_start_dt=>X_TR_OU_START_DT,
890 x_unit_cd=>X_UNIT_CD,
891 x_unit_class=>X_UNIT_CLASS,
892 x_unit_int_course_level_cd=>X_UNIT_INT_COURSE_LEVEL_CD,
893 x_unit_level=>X_UNIT_LEVEL,
894 x_uv_version_number=>X_UV_VERSION_NUMBER,
895 x_weftsu=>X_WEFTSU,
896 x_creation_date=>X_LAST_UPDATE_DATE,
897 x_created_by=>X_LAST_UPDATED_BY,
898 x_last_update_date=>X_LAST_UPDATE_DATE,
899 x_last_updated_by=>X_LAST_UPDATED_BY,
900 x_last_update_login=>X_LAST_UPDATE_LOGIN
901 );
902
903
904 if (X_MODE = 'R') then
905 x_request_id := fnd_global.conc_request_id;
906 x_program_id := fnd_global.conc_program_id;
907 x_program_application_id := fnd_global.prog_appl_id;
908
909 if (x_request_id = -1) then
910 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
911 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
912 X_PROGRAM_APPLICATION_ID :=
913 OLD_REFERENCES.PROGRAM_APPLICATION_ID;
914 X_PROGRAM_UPDATE_DATE :=
915 OLD_REFERENCES.PROGRAM_UPDATE_DATE;
916 else
917 x_program_update_date := SYSDATE;
918 end if;
919 end if;
920
921 update IGS_EN_ST_SNAPSHOT set
922 SNAPSHOT_DT_TIME = new_references.SNAPSHOT_DT_TIME,
923 CI_CAL_TYPE = new_references.CI_CAL_TYPE,
924 CI_SEQUENCE_NUMBER = new_references.CI_SEQUENCE_NUMBER,
925 PERSON_ID = new_references.PERSON_ID,
926 COURSE_CD = new_references.COURSE_CD,
927 CRV_VERSION_NUMBER = new_references.CRV_VERSION_NUMBER,
928 UNIT_CD = new_references.UNIT_CD,
929 UV_VERSION_NUMBER = new_references.UV_VERSION_NUMBER,
930 SUA_CAL_TYPE = new_references.SUA_CAL_TYPE,
931 SUA_CI_SEQUENCE_NUMBER = new_references.SUA_CI_SEQUENCE_NUMBER,
932 TR_ORG_UNIT_CD = new_references.TR_ORG_UNIT_CD,
933 TR_OU_START_DT = new_references.TR_OU_START_DT,
934 DISCIPLINE_GROUP_CD = new_references.DISCIPLINE_GROUP_CD,
935 GOVT_DISCIPLINE_GROUP_CD = new_references.GOVT_DISCIPLINE_GROUP_CD,
936 CRV_ORG_UNIT_CD = new_references.CRV_ORG_UNIT_CD,
937 CRV_OU_START_DT = new_references.CRV_OU_START_DT,
938 COURSE_TYPE = new_references.COURSE_TYPE,
939 GOVT_COURSE_TYPE = new_references.GOVT_COURSE_TYPE,
940 COURSE_TYPE_GROUP_CD = new_references.COURSE_TYPE_GROUP_CD,
941 SCA_LOCATION_CD = new_references.SCA_LOCATION_CD,
942 ATTENDANCE_MODE = new_references.ATTENDANCE_MODE,
943 GOVT_ATTENDANCE_MODE = new_references.GOVT_ATTENDANCE_MODE,
944 ATTENDANCE_TYPE = new_references.ATTENDANCE_TYPE,
948 MAJOR_COURSE = new_references.MAJOR_COURSE,
945 GOVT_ATTENDANCE_TYPE = new_references.GOVT_ATTENDANCE_TYPE,
946 FUNDING_SOURCE = new_references.FUNDING_SOURCE,
947 GOVT_FUNDING_SOURCE = new_references.GOVT_FUNDING_SOURCE,
949 COMMENCING_STUDENT_IND = new_references.COMMENCING_STUDENT_IND,
950 SCHOOL_LEAVER = new_references.SCHOOL_LEAVER,
951 NEW_TO_HIGHER_EDUCATION = new_references.NEW_TO_HIGHER_EDUCATION,
952 SUA_LOCATION_CD = new_references.SUA_LOCATION_CD,
953 UNIT_CLASS = new_references.UNIT_CLASS,
954 UNIT_LEVEL = new_references.UNIT_LEVEL,
955 ENROLLED_DT = new_references.ENROLLED_DT,
956 DISCONTINUED_DT = new_references.DISCONTINUED_DT,
957 EFTSU = new_references.EFTSU,
958 WEFTSU = new_references.WEFTSU,
959 UNIT_INT_COURSE_LEVEL_CD = new_references.UNIT_INT_COURSE_LEVEL_CD,
960 GOVT_REPORTABLE_IND = new_references.GOVT_REPORTABLE_IND,
961 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
962 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
963 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
964 REQUEST_ID = X_REQUEST_ID,
965 PROGRAM_ID = X_PROGRAM_ID,
966 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
967 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
968 where ROWID = X_ROWID;
969
970 if (sql%notfound) then
971 raise no_data_found;
972 end if;
973
974
975 After_DML(
976 p_action => 'UPDATE',
977 x_rowid => X_ROWID
978 );
979
980 end UPDATE_ROW;
981
982 procedure ADD_ROW (
983 X_ROWID in out NOCOPY VARCHAR2,
984 X_ESS_ID in out NOCOPY NUMBER,
985 X_SNAPSHOT_DT_TIME in DATE,
986 X_CI_CAL_TYPE in VARCHAR2,
987 X_CI_SEQUENCE_NUMBER in NUMBER,
988 X_PERSON_ID in NUMBER,
989 X_COURSE_CD in VARCHAR2,
990 X_CRV_VERSION_NUMBER in NUMBER,
991 X_UNIT_CD in VARCHAR2,
992 X_UV_VERSION_NUMBER in NUMBER,
993 X_SUA_CAL_TYPE in VARCHAR2,
994 X_SUA_CI_SEQUENCE_NUMBER in NUMBER,
995 X_TR_ORG_UNIT_CD in VARCHAR2,
996 X_TR_OU_START_DT in DATE,
997 X_DISCIPLINE_GROUP_CD in VARCHAR2,
998 X_GOVT_DISCIPLINE_GROUP_CD in VARCHAR2,
999 X_CRV_ORG_UNIT_CD in VARCHAR2,
1000 X_CRV_OU_START_DT in DATE,
1001 X_COURSE_TYPE in VARCHAR2,
1002 X_GOVT_COURSE_TYPE in NUMBER,
1003 X_COURSE_TYPE_GROUP_CD in VARCHAR2,
1004 X_SCA_LOCATION_CD in VARCHAR2,
1005 X_ATTENDANCE_MODE in VARCHAR2,
1006 X_GOVT_ATTENDANCE_MODE in VARCHAR2,
1007 X_ATTENDANCE_TYPE in VARCHAR2,
1008 X_GOVT_ATTENDANCE_TYPE in VARCHAR2,
1009 X_FUNDING_SOURCE in VARCHAR2,
1010 X_GOVT_FUNDING_SOURCE in NUMBER,
1011 X_MAJOR_COURSE in NUMBER,
1012 X_COMMENCING_STUDENT_IND in VARCHAR2,
1013 X_SCHOOL_LEAVER in NUMBER,
1014 X_NEW_TO_HIGHER_EDUCATION in NUMBER,
1015 X_SUA_LOCATION_CD in VARCHAR2,
1016 X_UNIT_CLASS in VARCHAR2,
1017 X_UNIT_LEVEL in VARCHAR2,
1021 X_WEFTSU in NUMBER,
1018 X_ENROLLED_DT in DATE,
1019 X_DISCONTINUED_DT in DATE,
1020 X_EFTSU in NUMBER,
1022 X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
1026 cursor c1 is select rowid from IGS_EN_ST_SNAPSHOT
1023 X_GOVT_REPORTABLE_IND in VARCHAR2,
1024 X_MODE in VARCHAR2 default 'R'
1025 ) AS
1027 where ESS_ID = X_ESS_ID;
1028
1029 begin
1030 open c1;
1031 fetch c1 into X_ROWID;
1032 if (c1%notfound) then
1033 close c1;
1034 INSERT_ROW (
1035 X_ROWID,
1036 X_ESS_ID,
1037 X_SNAPSHOT_DT_TIME,
1038 X_CI_CAL_TYPE,
1039 X_CI_SEQUENCE_NUMBER,
1040 X_PERSON_ID,
1041 X_COURSE_CD,
1042 X_CRV_VERSION_NUMBER,
1043 X_UNIT_CD,
1044 X_UV_VERSION_NUMBER,
1045 X_SUA_CAL_TYPE,
1046 X_SUA_CI_SEQUENCE_NUMBER,
1047 X_TR_ORG_UNIT_CD,
1048 X_TR_OU_START_DT,
1049 X_DISCIPLINE_GROUP_CD,
1050 X_GOVT_DISCIPLINE_GROUP_CD,
1051 X_CRV_ORG_UNIT_CD,
1052 X_CRV_OU_START_DT,
1053 X_COURSE_TYPE,
1054 X_GOVT_COURSE_TYPE,
1055 X_COURSE_TYPE_GROUP_CD,
1056 X_SCA_LOCATION_CD,
1057 X_ATTENDANCE_MODE,
1058 X_GOVT_ATTENDANCE_MODE,
1059 X_ATTENDANCE_TYPE,
1060 X_GOVT_ATTENDANCE_TYPE,
1061 X_FUNDING_SOURCE,
1062 X_GOVT_FUNDING_SOURCE,
1063 X_MAJOR_COURSE,
1064 X_COMMENCING_STUDENT_IND,
1065 X_SCHOOL_LEAVER,
1066 X_NEW_TO_HIGHER_EDUCATION,
1067 X_SUA_LOCATION_CD,
1068 X_UNIT_CLASS,
1069 X_UNIT_LEVEL,
1070 X_ENROLLED_DT,
1071 X_DISCONTINUED_DT,
1072 X_EFTSU,
1073 X_WEFTSU,
1074 X_UNIT_INT_COURSE_LEVEL_CD,
1075 X_GOVT_REPORTABLE_IND,
1076 X_MODE);
1077 return;
1078 end if;
1079 close c1;
1080 UPDATE_ROW (
1081 X_ROWID,
1082 X_ESS_ID,
1083 X_SNAPSHOT_DT_TIME,
1084 X_CI_CAL_TYPE,
1085 X_CI_SEQUENCE_NUMBER,
1086 X_PERSON_ID,
1087 X_COURSE_CD,
1088 X_CRV_VERSION_NUMBER,
1089 X_UNIT_CD,
1090 X_UV_VERSION_NUMBER,
1091 X_SUA_CAL_TYPE,
1092 X_SUA_CI_SEQUENCE_NUMBER,
1093 X_TR_ORG_UNIT_CD,
1094 X_TR_OU_START_DT,
1095 X_DISCIPLINE_GROUP_CD,
1096 X_GOVT_DISCIPLINE_GROUP_CD,
1097 X_CRV_ORG_UNIT_CD,
1098 X_CRV_OU_START_DT,
1099 X_COURSE_TYPE,
1100 X_GOVT_COURSE_TYPE,
1101 X_COURSE_TYPE_GROUP_CD,
1102 X_SCA_LOCATION_CD,
1103 X_ATTENDANCE_MODE,
1104 X_GOVT_ATTENDANCE_MODE,
1105 X_ATTENDANCE_TYPE,
1106 X_GOVT_ATTENDANCE_TYPE,
1107 X_FUNDING_SOURCE,
1108 X_GOVT_FUNDING_SOURCE,
1109 X_MAJOR_COURSE,
1110 X_COMMENCING_STUDENT_IND,
1111 X_SCHOOL_LEAVER,
1112 X_NEW_TO_HIGHER_EDUCATION,
1113 X_SUA_LOCATION_CD,
1114 X_UNIT_CLASS,
1115 X_UNIT_LEVEL,
1116 X_ENROLLED_DT,
1117 X_DISCONTINUED_DT,
1118 X_EFTSU,
1119 X_WEFTSU,
1120 X_UNIT_INT_COURSE_LEVEL_CD,
1121 X_GOVT_REPORTABLE_IND,
1122 X_MODE);
1123 end ADD_ROW;
1124
1125 procedure DELETE_ROW (X_ROWID IN VARCHAR2
1126 ) AS
1127 begin
1128 Before_DML(
1129 p_action => 'DELETE',
1130 x_rowid => X_ROWID
1131 );
1132 delete from IGS_EN_ST_SNAPSHOT
1133 where ROWID = X_ROWID;
1134 if (sql%notfound) then
1135 raise no_data_found;
1136 end if;
1137
1138 After_DML(
1139 p_action => 'DELETE',
1140 x_rowid => X_ROWID
1141 );
1142
1143 end DELETE_ROW;
1144
1145 end IGS_EN_ST_SNAPSHOT_PKG;