[Home] [Help]
PACKAGE BODY: APPS.IGS_ST_GVT_STDNTLOAD_PKG
Source
1 package body IGS_ST_GVT_STDNTLOAD_PKG as
2 /* $Header: IGSVI09B.pls 115.4 2003/05/20 06:09:04 svanukur ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_ST_GVT_STDNTLOAD%RowType;
5 new_references IGS_ST_GVT_STDNTLOAD%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_submission_yr IN NUMBER DEFAULT NULL,
11 x_submission_number IN NUMBER DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_course_cd IN VARCHAR2 DEFAULT NULL,
14 x_crv_version_number IN NUMBER DEFAULT NULL,
15 x_govt_semester IN NUMBER DEFAULT NULL,
16 x_unit_cd IN VARCHAR2 DEFAULT NULL,
17 x_uv_version_number IN NUMBER DEFAULT NULL,
18 x_sua_cal_type IN VARCHAR2 DEFAULT NULL,
19 x_sua_ci_sequence_number IN NUMBER DEFAULT NULL,
20 x_tr_org_unit_cd IN VARCHAR2 DEFAULT NULL,
21 x_tr_ou_start_dt IN DATE DEFAULT NULL,
22 x_discipline_group_cd IN VARCHAR2 DEFAULT NULL,
23 x_govt_discipline_group_cd IN VARCHAR2 DEFAULT NULL,
24 x_industrial_ind IN VARCHAR2 DEFAULT NULL,
25 x_eftsu IN NUMBER DEFAULT NULL,
26 x_unit_completion_status IN NUMBER DEFAULT NULL,
27 x_creation_date IN DATE DEFAULT NULL,
28 x_created_by IN NUMBER DEFAULT NULL,
29 x_last_update_date IN DATE DEFAULT NULL,
30 x_last_updated_by IN NUMBER DEFAULT NULL,
31 x_last_update_login IN NUMBER DEFAULT NULL,
32 x_unit_class IN VARCHAR2 DEFAULT NULL,
33 x_sua_location_cd IN VARCHAR2 DEFAULT NULL
34 ) as
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM IGS_ST_GVT_STDNTLOAD
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 Open cur_old_ref_values;
48 Fetch cur_old_ref_values INTO old_references;
49 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
50 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
51 IGS_GE_MSG_STACK.ADD;
52 App_Exception.Raise_Exception;
53 Close cur_old_ref_values;
54 Return;
55 END IF;
56 Close cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.submission_yr := x_submission_yr;
60 new_references.submission_number := x_submission_number;
61 new_references.person_id := x_person_id;
62 new_references.course_cd := x_course_cd;
63 new_references.crv_version_number := x_crv_version_number;
64 new_references.govt_semester := x_govt_semester;
65 new_references.unit_cd := x_unit_cd;
66 new_references.uv_version_number := x_uv_version_number;
67 new_references.sua_cal_type := x_sua_cal_type;
68 new_references.sua_ci_sequence_number := x_sua_ci_sequence_number;
69 new_references.tr_org_unit_cd := x_tr_org_unit_cd;
70 new_references.tr_ou_start_dt := x_tr_ou_start_dt;
71 new_references.discipline_group_cd := x_discipline_group_cd;
72 new_references.govt_discipline_group_cd := x_govt_discipline_group_cd;
73 new_references.industrial_ind := x_industrial_ind;
74 new_references.eftsu := x_eftsu;
75 new_references.unit_completion_status := x_unit_completion_status;
76 new_references.unit_class :=x_unit_class;
77 new_references.sua_location_cd := x_sua_location_cd;
78 IF (p_action = 'UPDATE') THEN
79 new_references.creation_date := old_references.creation_date;
80 new_references.created_by := old_references.created_by;
81 ELSE
82 new_references.creation_date := x_creation_date;
83 new_references.created_by := x_created_by;
84 END IF;
85 new_references.last_update_date := x_last_update_date;
86 new_references.last_updated_by := x_last_updated_by;
87 new_references.last_update_login := x_last_update_login;
88
89 END Set_Column_Values;
90
91 PROCEDURE BeforeRowInsertUpdateDelete1(
92 p_inserting IN BOOLEAN DEFAULT FALSE,
93 p_updating IN BOOLEAN DEFAULT FALSE,
94 p_deleting IN BOOLEAN DEFAULT FALSE
95 ) as
96 v_message_name VARCHAR2(30);
97 v_transaction_type VARCHAR2(15);
98 v_submission_yr IGS_ST_GVT_STDNTLOAD.submission_yr%TYPE;
99 v_submission_number IGS_ST_GVT_STDNTLOAD.submission_number%TYPE;
100 BEGIN
101 IF p_inserting THEN
102 v_transaction_type := 'p_inserting';
103 v_submission_yr := new_references.submission_yr;
104 v_submission_number := new_references.submission_number;
105 ELSIF p_updating THEN
106 v_transaction_type := 'p_updating';
107 v_submission_yr := new_references.submission_yr;
108 v_submission_number := new_references.submission_number;
109 ELSIF p_deleting THEN
110 v_transaction_type := 'p_deleting';
111 v_submission_yr := old_references.submission_yr;
112 v_submission_number := old_references.submission_number;
113 END IF;
114 IF IGS_ST_VAL_GSE.stap_val_govt_snpsht (
115 v_submission_yr,
116 v_submission_number,
117 v_transaction_type,
118 v_message_name) = FALSE THEN
119 --raise_application_error(-20000, IGS_GE_GEN_002.GENP_GET_MESSAGE(v_message_num));
120 FND_MESSAGE.SET_NAME('IGS',v_message_name);
121 IGS_GE_MSG_STACK.ADD;
122 APP_EXCEPTION.RAISE_EXCEPTION;
123 END IF;
124
125 END BeforeRowInsertUpdateDelete1;
126
127 PROCEDURE Check_Parent_Existance as
128 BEGIN
129
130 IF (((old_references.submission_yr = new_references.submission_yr) AND
131 (old_references.submission_number = new_references.submission_number) AND
132 (old_references.govt_semester = new_references.govt_semester)) OR
133 ((new_references.submission_yr IS NULL) OR
134 (new_references.submission_number IS NULL) OR
135 (new_references.govt_semester IS NULL))) THEN
136 NULL;
137 ELSE
138 IF NOT IGS_ST_GOVT_SEMESTER_PKG.Get_PK_For_Validation (
139 new_references.submission_yr,
140 new_references.submission_number,
141 new_references.govt_semester
142 )THEN
143 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
144 IGS_GE_MSG_STACK.ADD;
145 APP_EXCEPTION.RAISE_EXCEPTION;
146 END IF;
147 END IF;
148
149 END Check_Parent_Existance;
150
151 FUNCTION Get_PK_For_Validation (
152 x_submission_yr IN NUMBER,
153 x_submission_number IN NUMBER,
154 x_person_id IN NUMBER,
155 x_course_cd IN VARCHAR2,
156 x_govt_semester IN NUMBER,
157 x_unit_cd IN VARCHAR2,
158 x_sua_cal_type IN VARCHAR2,
159 x_sua_ci_sequence_number IN NUMBER,
160 x_tr_org_unit_cd IN VARCHAR2,
161 x_tr_ou_start_dt IN DATE,
162 x_discipline_group_cd IN VARCHAR2,
163 x_govt_discipline_group_cd IN VARCHAR2
164 ) RETURN BOOLEAN as
165
166 CURSOR cur_rowid IS
167 SELECT rowid
168 FROM IGS_ST_GVT_STDNTLOAD
169 WHERE submission_yr = x_submission_yr
170 AND submission_number = x_submission_number
171 AND person_id = x_person_id
172 AND course_cd = x_course_cd
173 AND govt_semester = x_govt_semester
174 AND unit_cd = x_unit_cd
175 AND sua_cal_type = x_sua_cal_type
176 AND sua_ci_sequence_number = x_sua_ci_sequence_number
177 AND tr_org_unit_cd = x_tr_org_unit_cd
178 AND tr_ou_start_dt = x_tr_ou_start_dt
179 AND discipline_group_cd = x_discipline_group_cd
180 AND govt_discipline_group_cd = x_govt_discipline_group_cd
181 FOR UPDATE NOWAIT;
182
183 lv_rowid cur_rowid%RowType;
184
185 BEGIN
186
187 Open cur_rowid;
188 Fetch cur_rowid INTO lv_rowid;
189 IF (cur_rowid%FOUND) THEN
190 Close cur_rowid;
191 Return TRUE;
192 ELSE
193 Close cur_rowid;
194 Return FALSE;
195 END IF;
196
197 END Get_PK_For_Validation;
198
199 PROCEDURE GET_FK_IGS_ST_GOVT_SEMESTER (
200 x_submission_yr IN NUMBER,
201 x_submission_number IN NUMBER,
202 x_govt_semester IN NUMBER
203 ) as
204
205 CURSOR cur_rowid IS
206 SELECT rowid
207 FROM IGS_ST_GVT_STDNTLOAD
208 WHERE submission_yr = x_submission_yr
209 AND submission_number = x_submission_number
210 AND govt_semester = x_govt_semester ;
211
212 lv_rowid cur_rowid%RowType;
213
214 BEGIN
215
216 Open cur_rowid;
217 Fetch cur_rowid INTO lv_rowid;
218 IF (cur_rowid%FOUND) THEN
219 Close cur_rowid;
220 Fnd_Message.Set_Name ('IGS', 'IGS_ST_GSLO_GSEM_FK');
221 IGS_GE_MSG_STACK.ADD;
222 App_Exception.Raise_Exception;
223 Return;
224 END IF;
225 Close cur_rowid;
226
227 END GET_FK_IGS_ST_GOVT_SEMESTER;
228
229 -- procedure to check constraints
230 PROCEDURE CHECK_CONSTRAINTS(
231 column_name IN VARCHAR2 DEFAULT NULL,
232 column_value IN VARCHAR2 DEFAULT NULL
233 ) as
234 BEGIN
235 IF column_name is null THEN
236 NULL;
237 ELSIF upper(column_name) = 'INDUSTRIAL_IND' THEN
238 new_references.industrial_ind := column_value;
239 ELSIF upper(column_name) = 'SUBMISSION_YR' THEN
240 new_references.submission_yr := IGS_GE_NUMBER.to_num(column_value);
241 ELSIF upper(column_name) = 'EFTSU' THEN
242 new_references.eftsu := IGS_GE_NUMBER.to_num(column_value);
243 ELSIF upper(column_name) = 'UNIT_COMPLETION_STATUS' THEN
244 new_references.unit_completion_status := IGS_GE_NUMBER.to_num(column_value);
245 END IF;
246
247 IF upper(column_name) = 'INDUSTRIAL_IND' OR column_name IS NULL THEN
248 IF new_references.industrial_ind NOT IN ('Y','N') THEN
249 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
250 IGS_GE_MSG_STACK.ADD;
251 APP_EXCEPTION.RAISE_EXCEPTION;
252 END IF;
253 END IF;
254 IF upper(column_name) = 'SUBMISSION_YR' OR column_name IS NULL THEN
255 IF new_references.submission_yr < 0000 OR new_references.submission_yr > 9999 THEN
256 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
257 IGS_GE_MSG_STACK.ADD;
258 APP_EXCEPTION.RAISE_EXCEPTION;
259 END IF;
260 END IF;
261 IF upper(column_name) = 'EFTSU' OR column_name IS NULL THEN
262 IF new_references.eftsu < 0000.000 OR new_references.eftsu > 9999.999 THEN
263 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
264 IGS_GE_MSG_STACK.ADD;
265 APP_EXCEPTION.RAISE_EXCEPTION;
266 END IF;
267 END IF;
268 IF upper(column_name) = 'UNIT_COMPLETION_STATUS' OR column_name IS NULL THEN
269 IF new_references.unit_completion_status NOT IN (1,2,3,4) THEN
270 IGS_GE_MSG_STACK.ADD;
271 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
272 IGS_GE_MSG_STACK.ADD;
273 APP_EXCEPTION.RAISE_EXCEPTION;
274 END IF;
275 END IF;
276
277 END CHECK_CONSTRAINTS;
278
279
280 PROCEDURE Before_DML (
281 p_action IN VARCHAR2,
282 x_rowid IN VARCHAR2 DEFAULT NULL,
283 x_submission_yr IN NUMBER DEFAULT NULL,
284 x_submission_number IN NUMBER DEFAULT NULL,
285 x_person_id IN NUMBER DEFAULT NULL,
286 x_course_cd IN VARCHAR2 DEFAULT NULL,
287 x_crv_version_number IN NUMBER DEFAULT NULL,
288 x_govt_semester IN NUMBER DEFAULT NULL,
289 x_unit_cd IN VARCHAR2 DEFAULT NULL,
290 x_uv_version_number IN NUMBER DEFAULT NULL,
291 x_sua_cal_type IN VARCHAR2 DEFAULT NULL,
292 x_sua_ci_sequence_number IN NUMBER DEFAULT NULL,
293 x_tr_org_unit_cd IN VARCHAR2 DEFAULT NULL,
294 x_tr_ou_start_dt IN DATE DEFAULT NULL,
295 x_discipline_group_cd IN VARCHAR2 DEFAULT NULL,
296 x_govt_discipline_group_cd IN VARCHAR2 DEFAULT NULL,
297 x_industrial_ind IN VARCHAR2 DEFAULT NULL,
298 x_eftsu IN NUMBER DEFAULT NULL,
299 x_unit_completion_status IN NUMBER DEFAULT NULL,
300 x_creation_date IN DATE DEFAULT NULL,
301 x_created_by IN NUMBER DEFAULT NULL,
302 x_last_update_date IN DATE DEFAULT NULL,
303 x_last_updated_by IN NUMBER DEFAULT NULL,
304 x_last_update_login IN NUMBER DEFAULT NULL,
305 x_unit_class IN VARCHAR2 DEFAULT NULL,
306 x_sua_location_cd IN VARCHAR2 DEFAULT NULL
307 ) as
308 BEGIN
309
310 Set_Column_Values (
311 p_action,
312 x_rowid,
313 x_submission_yr,
314 x_submission_number,
315 x_person_id,
316 x_course_cd,
317 x_crv_version_number,
318 x_govt_semester,
319 x_unit_cd,
320 x_uv_version_number,
321 x_sua_cal_type,
322 x_sua_ci_sequence_number,
323 x_tr_org_unit_cd,
324 x_tr_ou_start_dt,
325 x_discipline_group_cd,
326 x_govt_discipline_group_cd,
327 x_industrial_ind,
328 x_eftsu,
329 x_unit_completion_status,
330 x_creation_date,
331 x_created_by,
332 x_last_update_date,
333 x_last_updated_by,
334 x_last_update_login,
335 x_unit_class,
336 x_sua_location_cd
337 );
338
339 IF (p_action = 'INSERT') THEN
340 -- Call all the procedures related to Before Insert.
341 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
342 IF GET_PK_FOR_VALIDATION(
343 new_references.submission_yr,
344 new_references.submission_number,
345 new_references.person_id,
346 new_references.course_cd,
347 new_references.govt_semester,
348 new_references.unit_cd,
349 new_references.sua_cal_type,
350 new_references.sua_ci_sequence_number,
351 new_references.tr_org_unit_cd,
352 new_references.tr_ou_start_dt,
353 new_references.discipline_group_cd,
354 new_references.govt_discipline_group_cd
355 )THEN
356 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
357 IGS_GE_MSG_STACK.ADD;
358 APP_EXCEPTION.RAISE_EXCEPTION;
359 END IF;
360 Check_Constraints;
361 Check_Parent_Existance;
362 ELSIF (p_action = 'UPDATE') THEN
363 -- Call all the procedures related to Before Update.
364 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
365 Check_Constraints;
366 Check_Parent_Existance;
367 ELSIF (p_action = 'DELETE') THEN
368 -- Call all the procedures related to Before Delete.
369 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
370 ELSIF (p_action = 'VALIDATE_INSERT') THEN
371 -- Call all the procedures related to Before Delete.
372 IF GET_PK_FOR_VALIDATION(
373 new_references.submission_yr,
374 new_references.submission_number,
375 new_references.person_id,
376 new_references.course_cd,
377 new_references.govt_semester,
378 new_references.unit_cd,
379 new_references.sua_cal_type,
380 new_references.sua_ci_sequence_number,
381 new_references.tr_org_unit_cd,
382 new_references.tr_ou_start_dt,
383 new_references.discipline_group_cd,
384 new_references.govt_discipline_group_cd
385 )THEN
386 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
387 IGS_GE_MSG_STACK.ADD;
388 APP_EXCEPTION.RAISE_EXCEPTION;
389 END IF;
390 Check_Constraints;
391 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
392 -- Call all the procedures related to Before Delete.
393 check_constraints;
394 ELSIF (p_action = 'VALIDATE_DELETE') THEN
395 -- Call all the procedures related to Before Delete.
396 NULL;
397 END IF;
398
399 END Before_DML;
400
401 PROCEDURE After_DML (
402 p_action IN VARCHAR2,
403 x_rowid IN VARCHAR2
404 ) as
405 BEGIN
406
407 l_rowid := x_rowid;
408
409 END After_DML;
410
411 procedure INSERT_ROW (
412 X_ROWID in out NOCOPY VARCHAR2,
413 X_SUBMISSION_YR in NUMBER,
414 X_SUBMISSION_NUMBER in NUMBER,
415 X_PERSON_ID in NUMBER,
416 X_COURSE_CD in VARCHAR2,
417 X_GOVT_SEMESTER in NUMBER,
418 X_UNIT_CD in VARCHAR2,
419 X_SUA_CAL_TYPE in VARCHAR2,
420 X_SUA_CI_SEQUENCE_NUMBER in NUMBER,
421 X_TR_ORG_UNIT_CD in VARCHAR2,
422 X_TR_OU_START_DT in DATE,
423 X_DISCIPLINE_GROUP_CD in VARCHAR2,
424 X_GOVT_DISCIPLINE_GROUP_CD in VARCHAR2,
425 X_INDUSTRIAL_IND in VARCHAR2,
426 X_EFTSU in NUMBER,
427 X_UNIT_COMPLETION_STATUS in NUMBER,
428 X_CRV_VERSION_NUMBER in NUMBER,
429 X_UV_VERSION_NUMBER in NUMBER,
430 X_MODE in VARCHAR2 default 'R',
431 X_UNIT_CLASS IN VARCHAR2 ,
432 X_SUA_LOCATION_CD IN VARCHAR2
433 ) as
434 cursor C is select ROWID from IGS_ST_GVT_STDNTLOAD
435 where SUBMISSION_YR = X_SUBMISSION_YR
436 and SUBMISSION_NUMBER = X_SUBMISSION_NUMBER
437 and PERSON_ID = X_PERSON_ID
438 and COURSE_CD = X_COURSE_CD
439 and GOVT_SEMESTER = X_GOVT_SEMESTER
440 and UNIT_CD = X_UNIT_CD
441 and SUA_CAL_TYPE = X_SUA_CAL_TYPE
442 and SUA_CI_SEQUENCE_NUMBER = X_SUA_CI_SEQUENCE_NUMBER
443 and TR_ORG_UNIT_CD = X_TR_ORG_UNIT_CD
444 and TR_OU_START_DT = X_TR_OU_START_DT
445 and DISCIPLINE_GROUP_CD = X_DISCIPLINE_GROUP_CD
446 and GOVT_DISCIPLINE_GROUP_CD = X_GOVT_DISCIPLINE_GROUP_CD;
447 X_LAST_UPDATE_DATE DATE;
448 X_LAST_UPDATED_BY NUMBER;
449 X_LAST_UPDATE_LOGIN NUMBER;
450 X_REQUEST_ID NUMBER;
451 X_PROGRAM_ID NUMBER;
452 X_PROGRAM_APPLICATION_ID NUMBER;
453 X_PROGRAM_UPDATE_DATE DATE;
454 begin
455 X_LAST_UPDATE_DATE := SYSDATE;
456 if(X_MODE = 'I') then
457 X_LAST_UPDATED_BY := 1;
458 X_LAST_UPDATE_LOGIN := 0;
459 elsif (X_MODE = 'R') then
460 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
461 if X_LAST_UPDATED_BY is NULL then
462 X_LAST_UPDATED_BY := -1;
463 end if;
464 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
465 if X_LAST_UPDATE_LOGIN is NULL then
466 X_LAST_UPDATE_LOGIN := -1;
467 end if;
468 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
469 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
470 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
471 if (X_REQUEST_ID = -1) then
472 X_REQUEST_ID := NULL;
473 X_PROGRAM_ID := NULL;
474 X_PROGRAM_APPLICATION_ID := NULL;
475 X_PROGRAM_UPDATE_DATE := NULL;
476 else
477 X_PROGRAM_UPDATE_DATE := SYSDATE;
478 end if;
479 else
480 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
481 IGS_GE_MSG_STACK.ADD;
482 app_exception.raise_exception;
483 end if;
484
485 Before_DML (
486 p_action =>'INSERT',
487 x_rowid =>X_ROWID,
488 x_submission_yr => X_SUBMISSION_YR,
489 x_submission_number => X_SUBMISSION_NUMBER,
490 x_person_id => X_PERSON_ID,
491 x_course_cd => X_COURSE_CD,
492 x_crv_version_number => X_CRV_VERSION_NUMBER,
493 x_govt_semester => X_GOVT_SEMESTER,
494 x_unit_cd => X_UNIT_CD,
495 x_uv_version_number => X_UV_VERSION_NUMBER,
496 x_sua_cal_type => X_SUA_CAL_TYPE,
497 x_sua_ci_sequence_number => X_SUA_CI_SEQUENCE_NUMBER,
498 x_tr_org_unit_cd => X_TR_ORG_UNIT_CD,
499 x_tr_ou_start_dt => X_TR_OU_START_DT,
500 x_discipline_group_cd => X_DISCIPLINE_GROUP_CD,
501 x_govt_discipline_group_cd => X_GOVT_DISCIPLINE_GROUP_CD,
502 x_industrial_ind => X_INDUSTRIAL_IND,
503 x_eftsu => X_EFTSU,
504 x_unit_completion_status => X_UNIT_COMPLETION_STATUS,
505 x_creation_date =>X_LAST_UPDATE_DATE,
506 x_created_by =>X_LAST_UPDATED_BY,
507 x_last_update_date =>X_LAST_UPDATE_DATE,
508 x_last_updated_by =>X_LAST_UPDATED_BY,
509 x_last_update_login =>X_LAST_UPDATE_LOGIN,
510 x_unit_class => X_UNIT_CLASS,
511 x_sua_location_cd =>X_SUA_LOCATION_CD
512 );
513
514
515 insert into IGS_ST_GVT_STDNTLOAD (
516 TR_ORG_UNIT_CD,
517 TR_OU_START_DT,
518 DISCIPLINE_GROUP_CD,
519 GOVT_DISCIPLINE_GROUP_CD,
520 INDUSTRIAL_IND,
521 EFTSU,
522 UNIT_COMPLETION_STATUS,
523 SUBMISSION_YR,
524 SUBMISSION_NUMBER,
525 PERSON_ID,
526 COURSE_CD,
527 CRV_VERSION_NUMBER,
528 GOVT_SEMESTER,
529 UNIT_CD,
530 UV_VERSION_NUMBER,
531 SUA_CAL_TYPE,
532 SUA_CI_SEQUENCE_NUMBER,
533 CREATION_DATE,
534 CREATED_BY,
535 LAST_UPDATE_DATE,
536 LAST_UPDATED_BY,
537 LAST_UPDATE_LOGIN,
538 REQUEST_ID,
539 PROGRAM_ID,
540 PROGRAM_APPLICATION_ID,
541 PROGRAM_UPDATE_DATE,
542 UNIT_CLASS,
543 SUA_LOCATION_CD
544 ) values (
545 NEW_REFERENCES.TR_ORG_UNIT_CD,
546 NEW_REFERENCES.TR_OU_START_DT,
547 NEW_REFERENCES.DISCIPLINE_GROUP_CD,
548 NEW_REFERENCES.GOVT_DISCIPLINE_GROUP_CD,
549 NEW_REFERENCES.INDUSTRIAL_IND,
550 NEW_REFERENCES.EFTSU,
551 NEW_REFERENCES.UNIT_COMPLETION_STATUS,
552 NEW_REFERENCES.SUBMISSION_YR,
553 NEW_REFERENCES.SUBMISSION_NUMBER,
554 NEW_REFERENCES.PERSON_ID,
555 NEW_REFERENCES.COURSE_CD,
556 NEW_REFERENCES.CRV_VERSION_NUMBER,
557 NEW_REFERENCES.GOVT_SEMESTER,
558 NEW_REFERENCES.UNIT_CD,
559 NEW_REFERENCES.UV_VERSION_NUMBER,
560 NEW_REFERENCES.SUA_CAL_TYPE,
561 NEW_REFERENCES.SUA_CI_SEQUENCE_NUMBER,
562 X_LAST_UPDATE_DATE,
563 X_LAST_UPDATED_BY,
564 X_LAST_UPDATE_DATE,
565 X_LAST_UPDATED_BY,
566 X_LAST_UPDATE_LOGIN,
567 X_REQUEST_ID,
568 X_PROGRAM_ID,
569 X_PROGRAM_APPLICATION_ID,
570 X_PROGRAM_UPDATE_DATE,
571 NEW_REFERENCES.UNIT_CLASS,
572 NEW_REFERENCES.SUA_LOCATION_CD
573 );
574
575 open c;
576 fetch c into X_ROWID;
577 if (c%notfound) then
578 close c;
579 raise no_data_found;
580 end if;
581 close c;
582
583 After_DML(
584 p_action =>'INSERT',
585 x_rowid => X_ROWID
586 );
587
588 end INSERT_ROW;
589
590 procedure LOCK_ROW (
591 X_ROWID in VARCHAR2,
592 X_SUBMISSION_YR in NUMBER,
593 X_SUBMISSION_NUMBER in NUMBER,
594 X_PERSON_ID in NUMBER,
595 X_COURSE_CD in VARCHAR2,
596 X_GOVT_SEMESTER in NUMBER,
597 X_UNIT_CD in VARCHAR2,
598 X_SUA_CAL_TYPE in VARCHAR2,
599 X_SUA_CI_SEQUENCE_NUMBER in NUMBER,
600 X_TR_ORG_UNIT_CD in VARCHAR2,
601 X_TR_OU_START_DT in DATE,
602 X_DISCIPLINE_GROUP_CD in VARCHAR2,
603 X_GOVT_DISCIPLINE_GROUP_CD in VARCHAR2,
604 X_INDUSTRIAL_IND in VARCHAR2,
605 X_EFTSU in NUMBER,
606 X_UNIT_COMPLETION_STATUS in NUMBER,
607 X_CRV_VERSION_NUMBER in NUMBER,
608 X_UV_VERSION_NUMBER in NUMBER,
609 X_UNIT_CLASS in VARCHAR2,
610 X_SUA_LOCATION_CD in varchar2
611 ) as
612 cursor c1 is select
613 INDUSTRIAL_IND,
614 EFTSU,
615 UNIT_COMPLETION_STATUS,
616 CRV_VERSION_NUMBER,
617 UV_VERSION_NUMBER
618 from IGS_ST_GVT_STDNTLOAD
619 where ROWID = X_ROWID
620 for update nowait;
621 tlinfo c1%rowtype;
622
623 begin
624 open c1;
625 fetch c1 into tlinfo;
626 if (c1%notfound) then
627 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
628 IGS_GE_MSG_STACK.ADD;
629 app_exception.raise_exception;
630 close c1;
631 return;
632 end if;
633 close c1;
634
635 if ( (tlinfo.INDUSTRIAL_IND = X_INDUSTRIAL_IND)
636 AND (tlinfo.EFTSU = X_EFTSU)
637 AND (tlinfo.UNIT_COMPLETION_STATUS = X_UNIT_COMPLETION_STATUS)
638 AND (tlinfo.CRV_VERSION_NUMBER = X_CRV_VERSION_NUMBER)
639 AND (tlinfo.UV_VERSION_NUMBER = X_UV_VERSION_NUMBER)
640 ) then
641 null;
642 else
643 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
644 IGS_GE_MSG_STACK.ADD;
645 app_exception.raise_exception;
646 end if;
647 return;
648 end LOCK_ROW;
649
650 procedure UPDATE_ROW (
651 X_ROWID in VARCHAR2,
652 X_SUBMISSION_YR in NUMBER,
653 X_SUBMISSION_NUMBER in NUMBER,
654 X_PERSON_ID in NUMBER,
655 X_COURSE_CD in VARCHAR2,
656 X_GOVT_SEMESTER in NUMBER,
657 X_UNIT_CD in VARCHAR2,
658 X_SUA_CAL_TYPE in VARCHAR2,
659 X_SUA_CI_SEQUENCE_NUMBER in NUMBER,
660 X_TR_ORG_UNIT_CD in VARCHAR2,
661 X_TR_OU_START_DT in DATE,
662 X_DISCIPLINE_GROUP_CD in VARCHAR2,
663 X_GOVT_DISCIPLINE_GROUP_CD in VARCHAR2,
664 X_INDUSTRIAL_IND in VARCHAR2,
665 X_EFTSU in NUMBER,
666 X_UNIT_COMPLETION_STATUS in NUMBER,
667 X_CRV_VERSION_NUMBER in NUMBER,
668 X_UV_VERSION_NUMBER in NUMBER,
669 X_MODE in VARCHAR2 default 'R',
670 X_UNIT_CLASS in VARCHAR2,
671 X_SUA_LOCATION_CD in varchar2
672 ) as
673 X_LAST_UPDATE_DATE DATE;
674 X_LAST_UPDATED_BY NUMBER;
675 X_LAST_UPDATE_LOGIN NUMBER;
676 X_REQUEST_ID NUMBER;
677 X_PROGRAM_ID NUMBER;
678 X_PROGRAM_APPLICATION_ID NUMBER;
679 X_PROGRAM_UPDATE_DATE DATE;
680 begin
681 X_LAST_UPDATE_DATE := SYSDATE;
682 if(X_MODE = 'I') then
683 X_LAST_UPDATED_BY := 1;
684 X_LAST_UPDATE_LOGIN := 0;
685 elsif (X_MODE = 'R') then
686 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
687 if X_LAST_UPDATED_BY is NULL then
688 X_LAST_UPDATED_BY := -1;
689 end if;
690 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
691 if X_LAST_UPDATE_LOGIN is NULL then
692 X_LAST_UPDATE_LOGIN := -1;
693 end if;
694 else
695 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
696 IGS_GE_MSG_STACK.ADD;
697 app_exception.raise_exception;
698 end if;
699
700 Before_DML (
701 p_action =>'UPDATE',
702 x_rowid =>X_ROWID,
703 x_submission_yr => X_SUBMISSION_YR,
704 x_submission_number => X_SUBMISSION_NUMBER,
705 x_person_id => X_PERSON_ID,
706 x_course_cd => X_COURSE_CD,
707 x_crv_version_number => X_CRV_VERSION_NUMBER,
708 x_govt_semester => X_GOVT_SEMESTER,
709 x_unit_cd => X_UNIT_CD,
710 x_uv_version_number => X_UV_VERSION_NUMBER,
711 x_sua_cal_type => X_SUA_CAL_TYPE,
712 x_sua_ci_sequence_number => X_SUA_CI_SEQUENCE_NUMBER,
713 x_tr_org_unit_cd => X_TR_ORG_UNIT_CD,
714 x_tr_ou_start_dt => X_TR_OU_START_DT,
715 x_discipline_group_cd => X_DISCIPLINE_GROUP_CD,
716 x_govt_discipline_group_cd => X_GOVT_DISCIPLINE_GROUP_CD,
717 x_industrial_ind => X_INDUSTRIAL_IND,
718 x_eftsu => X_EFTSU,
719 x_unit_completion_status => X_UNIT_COMPLETION_STATUS,
720 x_creation_date =>X_LAST_UPDATE_DATE,
721 x_created_by =>X_LAST_UPDATED_BY,
722 x_last_update_date =>X_LAST_UPDATE_DATE,
723 x_last_updated_by =>X_LAST_UPDATED_BY,
724 x_last_update_login =>X_LAST_UPDATE_LOGIN,
725 x_unit_class => X_UNIT_CLASS,
726 x_sua_location_cd =>X_SUA_LOCATION_CD
727 );
728
729 if (X_MODE = 'R') then
730 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
731 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
732 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
733 if (X_REQUEST_ID = -1) then
734 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
735 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
736 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
737 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
738 else
739 X_PROGRAM_UPDATE_DATE := SYSDATE;
740 end if;
741 end if;
742 update IGS_ST_GVT_STDNTLOAD set
743 INDUSTRIAL_IND = NEW_REFERENCES.INDUSTRIAL_IND,
744 EFTSU = NEW_REFERENCES.EFTSU,
745 UNIT_COMPLETION_STATUS = NEW_REFERENCES.UNIT_COMPLETION_STATUS,
746 CRV_VERSION_NUMBER = NEW_REFERENCES.CRV_VERSION_NUMBER,
747 UV_VERSION_NUMBER = NEW_REFERENCES.UV_VERSION_NUMBER,
748 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
749 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
750 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
751 REQUEST_ID = X_REQUEST_ID,
752 PROGRAM_ID = X_PROGRAM_ID,
753 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
754 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
755 UNIT_CLASS = X_UNIT_CLASS,
756 SUA_LOCATION_CD = X_SUA_LOCATION_CD
757 where ROWID = X_ROWID
758 ;
759 if (sql%notfound) then
760 raise no_data_found;
761 end if;
762
763 After_DML(
764 p_action =>'UPDATE',
765 x_rowid => X_ROWID
766 );
767
768 end UPDATE_ROW;
769
770 procedure ADD_ROW (
771 X_ROWID in out NOCOPY VARCHAR2,
772 X_SUBMISSION_YR in NUMBER,
773 X_SUBMISSION_NUMBER in NUMBER,
774 X_PERSON_ID in NUMBER,
775 X_COURSE_CD in VARCHAR2,
776 X_GOVT_SEMESTER in NUMBER,
777 X_UNIT_CD in VARCHAR2,
778 X_SUA_CAL_TYPE in VARCHAR2,
779 X_SUA_CI_SEQUENCE_NUMBER in NUMBER,
780 X_TR_ORG_UNIT_CD in VARCHAR2,
781 X_TR_OU_START_DT in DATE,
782 X_DISCIPLINE_GROUP_CD in VARCHAR2,
783 X_GOVT_DISCIPLINE_GROUP_CD in VARCHAR2,
784 X_INDUSTRIAL_IND in VARCHAR2,
785 X_EFTSU in NUMBER,
786 X_UNIT_COMPLETION_STATUS in NUMBER,
787 X_CRV_VERSION_NUMBER in NUMBER,
788 X_UV_VERSION_NUMBER in NUMBER,
789 X_MODE in VARCHAR2 default 'R',
790 X_UNIT_CLASS in VARCHAR2,
791 X_SUA_LOCATION_CD in varchar2
792 ) as
793 cursor c1 is select rowid from IGS_ST_GVT_STDNTLOAD
794 where SUBMISSION_YR = X_SUBMISSION_YR
795 and SUBMISSION_NUMBER = X_SUBMISSION_NUMBER
796 and PERSON_ID = X_PERSON_ID
797 and COURSE_CD = X_COURSE_CD
798 and GOVT_SEMESTER = X_GOVT_SEMESTER
799 and UNIT_CD = X_UNIT_CD
800 and SUA_CAL_TYPE = X_SUA_CAL_TYPE
801 and SUA_CI_SEQUENCE_NUMBER = X_SUA_CI_SEQUENCE_NUMBER
802 and TR_ORG_UNIT_CD = X_TR_ORG_UNIT_CD
803 and TR_OU_START_DT = X_TR_OU_START_DT
804 and DISCIPLINE_GROUP_CD = X_DISCIPLINE_GROUP_CD
805 and GOVT_DISCIPLINE_GROUP_CD = X_GOVT_DISCIPLINE_GROUP_CD
806 ;
807 begin
808 open c1;
809 fetch c1 into X_ROWID;
810 if (c1%notfound) then
811 close c1;
812 INSERT_ROW (
813 X_ROWID,
814 X_SUBMISSION_YR,
815 X_SUBMISSION_NUMBER,
816 X_PERSON_ID,
817 X_COURSE_CD,
818 X_GOVT_SEMESTER,
819 X_UNIT_CD,
820 X_SUA_CAL_TYPE,
821 X_SUA_CI_SEQUENCE_NUMBER,
822 X_TR_ORG_UNIT_CD,
823 X_TR_OU_START_DT,
824 X_DISCIPLINE_GROUP_CD,
825 X_GOVT_DISCIPLINE_GROUP_CD,
826 X_INDUSTRIAL_IND,
827 X_EFTSU,
828 X_UNIT_COMPLETION_STATUS,
829 X_CRV_VERSION_NUMBER,
830 X_UV_VERSION_NUMBER,
831 X_MODE,
832 X_UNIT_CLASS,
833 X_SUA_LOCATION_CD);
834 return;
835 end if;
836 close c1;
837 UPDATE_ROW (
838 X_ROWID,
839 X_SUBMISSION_YR,
840 X_SUBMISSION_NUMBER,
841 X_PERSON_ID,
842 X_COURSE_CD,
843 X_GOVT_SEMESTER,
844 X_UNIT_CD,
845 X_SUA_CAL_TYPE,
846 X_SUA_CI_SEQUENCE_NUMBER,
847 X_TR_ORG_UNIT_CD,
848 X_TR_OU_START_DT,
849 X_DISCIPLINE_GROUP_CD,
850 X_GOVT_DISCIPLINE_GROUP_CD,
851 X_INDUSTRIAL_IND,
852 X_EFTSU,
853 X_UNIT_COMPLETION_STATUS,
854 X_CRV_VERSION_NUMBER,
855 X_UV_VERSION_NUMBER,
856 X_MODE,
857 X_UNIT_CLASS,
858 X_SUA_LOCATION_CD );
859 end ADD_ROW;
860
861 procedure DELETE_ROW (
862 X_ROWID in VARCHAR2
863 ) as
864 begin
865
866 Before_DML(
867 p_action =>'DELETE',
868 x_rowid => X_ROWID
869 );
870
871 delete from IGS_ST_GVT_STDNTLOAD
872 where ROWID = X_ROWID;
873 if (sql%notfound) then
874 raise no_data_found;
875 end if;
876
877 After_DML(
878 p_action =>'DELETE',
879 x_rowid => X_ROWID
880 );
881 end DELETE_ROW;
882
883 end IGS_ST_GVT_STDNTLOAD_PKG;