[Home] [Help]
PACKAGE BODY: APPS.IGS_ST_GVTSEMLOAD_OV_PKG
Source
1 package body IGS_ST_GVTSEMLOAD_OV_PKG AS
2 /* $Header: IGSVI04B.pls 115.5 2002/11/29 04:31:41 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_ST_GVTSEMLOAD_OV%RowType;
5 new_references IGS_ST_GVTSEMLOAD_OV%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_govt_semester IN NUMBER DEFAULT NULL,
13 x_cal_type IN VARCHAR2 DEFAULT NULL,
14 x_ci_sequence_number IN NUMBER DEFAULT NULL,
15 x_teach_cal_type IN VARCHAR2 DEFAULT NULL,
16 x_creation_date IN DATE DEFAULT NULL,
17 x_created_by IN NUMBER DEFAULT NULL,
18 x_last_update_date IN DATE DEFAULT NULL,
19 x_last_updated_by IN NUMBER DEFAULT NULL,
20 x_last_update_login IN NUMBER DEFAULT NULL
21 ) AS
22
23 CURSOR cur_old_ref_values IS
24 SELECT *
25 FROM IGS_ST_GVTSEMLOAD_OV
26 WHERE rowid = x_rowid;
27
28 BEGIN
29
30 l_rowid := x_rowid;
31
32 -- Code for setting the Old and New Reference Values.
33 -- Populate Old Values.
34 Open cur_old_ref_values;
35 Fetch cur_old_ref_values INTO old_references;
36 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
37 Close cur_old_ref_values;
38 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
39 IGS_GE_MSG_STACK.ADD;
40 App_Exception.Raise_Exception;
41 Return;
42 END IF;
43 Close cur_old_ref_values;
44
45 -- Populate New Values.
46 new_references.submission_yr := x_submission_yr;
47 new_references.submission_number := x_submission_number;
48 new_references.govt_semester := x_govt_semester;
49 new_references.cal_type := x_cal_type;
50 new_references.ci_sequence_number := x_ci_sequence_number;
51 new_references.teach_cal_type := x_teach_cal_type;
52 IF (p_action = 'UPDATE') THEN
53 new_references.creation_date := old_references.creation_date;
54 new_references.created_by := old_references.created_by;
55 ELSE
56 new_references.creation_date := x_creation_date;
57 new_references.created_by := x_created_by;
58 END IF;
59 new_references.last_update_date := x_last_update_date;
60 new_references.last_updated_by := x_last_updated_by;
61 new_references.last_update_login := x_last_update_login;
62
63 END Set_Column_Values;
64
65 PROCEDURE BeforeRowInsertUpdateDelete1(
66 p_inserting IN BOOLEAN DEFAULT FALSE,
67 p_updating IN BOOLEAN DEFAULT FALSE,
68 p_deleting IN BOOLEAN DEFAULT FALSE
69 ) AS
70 v_message_name VARCHAR2(30);
71 v_submission_yr IGS_ST_GVTSEMLOAD_OV.submission_yr%TYPE;
72 v_submission_number IGS_ST_GVTSEMLOAD_OV.submission_number%TYPE;
73 BEGIN
74 IF p_inserting OR p_updating THEN
75 v_submission_yr := new_references.submission_yr;
76 v_submission_number := new_references.submission_number;
77 ELSE
78 v_submission_yr := old_references.submission_yr;
79 v_submission_number := old_references.submission_number;
80 END IF;
81 -- Validate if insert, update or delete is allowed.
82 /*
83 || Bug ID 1956374 - Removal of Duplicate Program Units from OSS.
84 || Changed the reference of "IGS_ST_VAL_GSLOV.STAP_VAL_GSC_SDT_UPD" to program unit "IGS_ST_VAL_GSC.STAP_VAL_GSC_SDT_UPD". -- kdande
85 */
86 IF IGS_ST_VAL_GSC.stap_val_gsc_sdt_upd (
87 v_submission_yr,
88 v_submission_number,
89 v_message_name) = FALSE THEN
90 v_message_name := 'IGS_ST_GOVT_SUBM_COMPLETE';
91 Fnd_Message.Set_Name('IGS',v_message_name);
92 IGS_GE_MSG_STACK.ADD;
93 App_Exception.Raise_Exception;
94 END IF;
95 -- Validate the calendar instance.
96 IF p_inserting OR
97 (p_updating AND
98 (old_references.cal_type <> new_references.cal_type AND
99 old_references.ci_sequence_number <> new_references.ci_sequence_number)) THEN
100 /*
101 || Bug ID 1956374 - Removal of Duplicate Program Units from OSS.
102 || Changed the reference of "IGS_ST_VAL_GSLOV.STAP_VAL_CI_STATUS" to program unit "IGS_EN_VAL_DLA.STAP_VAL_CI_STATUS". -- kdande
103 */
104 IF IGS_EN_VAL_DLA.stap_val_ci_status (
105 new_references.cal_type,
106 new_references.ci_sequence_number,
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
114
115 END BeforeRowInsertUpdateDelete1;
116
117 PROCEDURE Check_Uniqueness AS
118 Begin
119 IF Get_UK1_For_Validation (
120 new_references.submission_yr,
121 new_references.submission_number,
122 new_references.cal_type,
123 new_references.ci_sequence_number,
124 new_references.teach_cal_type) THEN
125 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
126 IGS_GE_MSG_STACK.ADD;
127 App_Exception.Raise_Exception;
128 END IF;
129 END Check_uniqueness;
130
131 PROCEDURE Check_Constraints (
132 Column_Name IN VARCHAR2 DEFAULT NULL,
133 Column_Value IN VARCHAR2 DEFAULT NULL
134 )
135 AS
136 BEGIN
137 IF column_name is null then
138 NULL;
139 ELSIF upper(Column_name) = 'CI_SEQUENCE_NUMBER ' then
140 new_references.ci_sequence_number := IGS_GE_NUMBER.to_num(column_value);
141 ELSIF upper(Column_name) = 'CAL_TYPE' then
142 new_references.cal_type := column_value;
143 ELSIF upper(Column_name) = 'TEACH_CAL_TYPE' then
144 new_references.teach_cal_type := column_value;
145 END IF;
146
147 IF upper(column_name) = 'CI_SEQUENCE_NUMBER ' OR
148 column_name is null Then
149 IF new_references.ci_sequence_number < 1 OR
150 new_references.ci_sequence_number > 999999 Then
151 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
152 IGS_GE_MSG_STACK.ADD;
153 App_Exception.Raise_Exception;
154 END IF;
155 END IF;
156 IF upper(column_name) = 'CAL_TYPE' OR
157 column_name is null Then
158 IF new_references.cal_type <> UPPER(new_references.cal_type) Then
159 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
160 IGS_GE_MSG_STACK.ADD;
161 App_Exception.Raise_Exception;
162 END IF;
163 END IF;
164
165 IF upper(column_name) = 'TEACH_CAL_TYPE' OR
166 column_name is null Then
167 IF new_references.teach_cal_type <> UPPER(new_references.teach_cal_type) Then
168 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
169 IGS_GE_MSG_STACK.ADD;
170 App_Exception.Raise_Exception;
171 END IF;
172 END IF;
173 END Check_Constraints;
174
175 PROCEDURE Check_Parent_Existance AS
176 BEGIN
177
178 IF (((old_references.cal_type = new_references.cal_type) AND
179 (old_references.ci_sequence_number = new_references.ci_sequence_number) AND
180 (old_references.teach_cal_type = new_references.teach_cal_type)) OR
181 ((new_references.cal_type IS NULL) OR
182 (new_references.ci_sequence_number IS NULL) OR
183 (new_references.teach_cal_type IS NULL))) THEN
184 NULL;
185 ELSE
186 IF NOT IGS_ST_DFT_LOAD_APPO_PKG.Get_PK_For_Validation (
187 new_references.cal_type,
188 new_references.ci_sequence_number,
189 new_references.teach_cal_type
190 ) THEN
191 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
192 IGS_GE_MSG_STACK.ADD;
193 App_Exception.Raise_Exception;
194 END IF;
195 END IF;
196
197 IF (((old_references.submission_yr = new_references.submission_yr) AND
198 (old_references.submission_number = new_references.submission_number) AND
199 (old_references.govt_semester = new_references.govt_semester)) OR
200 ((new_references.submission_yr IS NULL) OR
201 (new_references.submission_number IS NULL) OR
202 (new_references.govt_semester IS NULL))) THEN
203 NULL;
204 ELSE
205 IF NOT IGS_ST_GOVT_SEMESTER_PKG.Get_PK_For_Validation (
206 new_references.submission_yr,
207 new_references.submission_number,
208 new_references.govt_semester
209 ) THEN
210 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
211 IGS_GE_MSG_STACK.ADD;
212 App_Exception.Raise_Exception;
213 END IF;
214 END IF;
215
216 END Check_Parent_Existance;
217
218 FUNCTION Get_PK_For_Validation (
219 x_submission_yr IN NUMBER,
220 x_submission_number IN NUMBER,
221 x_govt_semester IN NUMBER,
222 x_cal_type IN VARCHAR2,
223 x_ci_sequence_number IN NUMBER,
224 x_teach_cal_type IN VARCHAR2
225 )
226 RETURN BOOLEAN
227 AS
228
229 CURSOR cur_rowid IS
230 SELECT rowid
231 FROM IGS_ST_GVTSEMLOAD_OV
232 WHERE submission_yr = x_submission_yr
233 AND submission_number = x_submission_number
234 AND govt_semester = x_govt_semester
235 AND cal_type = x_cal_type
236 AND ci_sequence_number = x_ci_sequence_number
237 AND teach_cal_type = x_teach_cal_type
238 FOR UPDATE NOWAIT;
239
240 lv_rowid cur_rowid%RowType;
241
242 BEGIN
243
244 Open cur_rowid;
245 Fetch cur_rowid INTO lv_rowid;
246 IF (cur_rowid%FOUND) THEN
247 Close cur_rowid;
248 Return (TRUE);
249 ELSE
250 Close cur_rowid;
251 Return (FALSE);
252 END IF;
253 END Get_PK_For_Validation;
254
255 FUNCTION Get_UK1_For_Validation (
256 x_submission_yr in NUMBER,
257 x_submission_number IN NUMBER,
258 x_cal_type IN VARCHAR2,
259 x_ci_sequence_number IN NUMBER,
260 x_teach_cal_type IN VARCHAR2
261 )
262 RETURN BOOLEAN
263 AS
264 CURSOR cur_rowid IS
265 SELECT rowid
266 FROM IGS_ST_GVTSEMLOAD_OV
267 WHERE submission_yr = x_submission_yr
268 AND submission_number = x_submission_number
269 AND cal_type = x_cal_type
270 AND ci_sequence_number = x_ci_sequence_number
271 AND teach_cal_type = x_teach_cal_type
272 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
273
274 FOR UPDATE NOWAIT;
275
276 lv_rowid cur_rowid%RowType;
277
278 BEGIN
279
280 Open cur_rowid;
281 Fetch cur_rowid INTO lv_rowid;
282 IF (cur_rowid%FOUND) THEN
283 Close cur_rowid;
284 Return (TRUE);
285 ELSE
286 Close cur_rowid;
287 Return (FALSE);
288 END IF;
289 END Get_UK1_For_Validation;
290
291 PROCEDURE GET_FK_IGS_ST_DFT_LOAD_APPO (
292 x_cal_type IN VARCHAR2,
293 x_ci_sequence_number IN NUMBER,
294 x_teach_cal_type IN VARCHAR2
295 ) AS
296
297 CURSOR cur_rowid IS
298 SELECT rowid
299 FROM IGS_ST_GVTSEMLOAD_OV
300 WHERE cal_type = x_cal_type
301 AND ci_sequence_number = x_ci_sequence_number
302 AND teach_cal_type = x_teach_cal_type ;
303
304 lv_rowid cur_rowid%RowType;
305
306 BEGIN
307
308 Open cur_rowid;
309 Fetch cur_rowid INTO lv_rowid;
310 IF (cur_rowid%FOUND) THEN
311 Close cur_rowid;
312 Fnd_Message.Set_Name ('IGS', 'IGS_ST_GSLOV_DLA_FK');
313 IGS_GE_MSG_STACK.ADD;
314 App_Exception.Raise_Exception;
315 Return;
316 END IF;
317 Close cur_rowid;
318
319 END GET_FK_IGS_ST_DFT_LOAD_APPO;
320
321 PROCEDURE GET_FK_IGS_ST_GOVT_SEMESTER (
322 x_submission_yr IN NUMBER,
323 x_submission_number IN NUMBER,
324 x_govt_semester IN NUMBER
325 ) AS
326
327 CURSOR cur_rowid IS
328 SELECT rowid
329 FROM IGS_ST_GVTSEMLOAD_OV
330 WHERE submission_yr = x_submission_yr
331 AND submission_number = x_submission_number
332 AND govt_semester = x_govt_semester ;
333
334 lv_rowid cur_rowid%RowType;
335
336 BEGIN
337
338 Open cur_rowid;
339 Fetch cur_rowid INTO lv_rowid;
340 IF (cur_rowid%FOUND) THEN
341 Close cur_rowid;
342 Fnd_Message.Set_Name ('IGS', 'IGS_ST_GSLOV_GSEM_FK');
343 IGS_GE_MSG_STACK.ADD;
344 App_Exception.Raise_Exception;
345 Return;
346 END IF;
347 Close cur_rowid;
348
349 END GET_FK_IGS_ST_GOVT_SEMESTER;
350
351 PROCEDURE Before_DML (
352 p_action IN VARCHAR2,
353 x_rowid IN VARCHAR2 DEFAULT NULL,
354 x_submission_yr IN NUMBER DEFAULT NULL,
355 x_submission_number IN NUMBER DEFAULT NULL,
356 x_govt_semester IN NUMBER DEFAULT NULL,
357 x_cal_type IN VARCHAR2 DEFAULT NULL,
358 x_ci_sequence_number IN NUMBER DEFAULT NULL,
359 x_teach_cal_type IN VARCHAR2 DEFAULT NULL,
360 x_creation_date IN DATE DEFAULT NULL,
361 x_created_by IN NUMBER DEFAULT NULL,
362 x_last_update_date IN DATE DEFAULT NULL,
363 x_last_updated_by IN NUMBER DEFAULT NULL,
364 x_last_update_login IN NUMBER DEFAULT NULL
365 ) AS
366 BEGIN
367
368 Set_Column_Values (
369 p_action,
370 x_rowid,
371 x_submission_yr,
372 x_submission_number,
373 x_govt_semester,
374 x_cal_type,
375 x_ci_sequence_number,
376 x_teach_cal_type,
377 x_creation_date,
378 x_created_by,
379 x_last_update_date,
380 x_last_updated_by,
381 x_last_update_login
382 );
383 IF (p_action = 'INSERT') THEN
384 -- Call all the procedures related to Before Insert.
385 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
386 IF Get_PK_For_Validation (
387 new_references.submission_yr,
388 new_references.submission_number,
389 new_references.govt_semester,
390 new_references.cal_type,
391 new_references.ci_sequence_number,
392 new_references.teach_cal_type
393 ) THEN
394 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
395 IGS_GE_MSG_STACK.ADD;
396 App_Exception.Raise_Exception;
397 END IF;
398 Check_Uniqueness;
399 Check_Constraints;
400 Check_Parent_Existance;
401 ELSIF (p_action = 'UPDATE') THEN
402 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
403 Check_Uniqueness;
404 Check_Constraints;
405 Check_Parent_Existance;
406 ELSIF (p_action = 'DELETE') THEN
407 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
408 ELSIF (p_action = 'VALIDATE_INSERT') THEN
409 IF Get_PK_For_Validation (
410 new_references.submission_yr,
411 new_references.submission_number,
412 new_references.govt_semester,
413 new_references.cal_type,
414 new_references.ci_sequence_number,
415 new_references.teach_cal_type
416 ) THEN
417 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
418 IGS_GE_MSG_STACK.ADD;
419 App_Exception.Raise_Exception;
420 END IF;
421 Check_Uniqueness;
422 Check_Constraints;
423 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
424 Check_Uniqueness;
425 Check_Constraints;
426 END IF;
427 END Before_DML;
428
429 PROCEDURE After_DML (
430 p_action IN VARCHAR2,
431 x_rowid IN VARCHAR2
432 ) AS
433 BEGIN
434
435 l_rowid := x_rowid;
436
437 END After_DML;
438
439 procedure INSERT_ROW (
440 X_ROWID in out NOCOPY VARCHAR2,
441 X_SUBMISSION_YR in NUMBER,
442 X_SUBMISSION_NUMBER in NUMBER,
443 X_GOVT_SEMESTER in NUMBER,
444 X_CAL_TYPE in VARCHAR2,
445 X_CI_SEQUENCE_NUMBER in NUMBER,
446 X_TEACH_CAL_TYPE in VARCHAR2,
447 X_MODE in VARCHAR2 default 'R'
448 ) AS
449 cursor C is select ROWID from IGS_ST_GVTSEMLOAD_OV
450 where SUBMISSION_YR = X_SUBMISSION_YR
451 and SUBMISSION_NUMBER = X_SUBMISSION_NUMBER
452 and GOVT_SEMESTER = X_GOVT_SEMESTER
453 and CAL_TYPE = X_CAL_TYPE
454 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
455 and TEACH_CAL_TYPE = X_TEACH_CAL_TYPE;
456 X_LAST_UPDATE_DATE DATE;
457 X_LAST_UPDATED_BY NUMBER;
458 X_LAST_UPDATE_LOGIN NUMBER;
459 begin
460 X_LAST_UPDATE_DATE := SYSDATE;
461 if(X_MODE = 'I') then
462 X_LAST_UPDATED_BY := 1;
463 X_LAST_UPDATE_LOGIN := 0;
464 elsif (X_MODE = 'R') then
465 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
466 if X_LAST_UPDATED_BY is NULL then
467 X_LAST_UPDATED_BY := -1;
468 end if;
469 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
470 if X_LAST_UPDATE_LOGIN is NULL then
471 X_LAST_UPDATE_LOGIN := -1;
472 end if;
473 else
474 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
475 IGS_GE_MSG_STACK.ADD;
476 app_exception.raise_exception;
477 end if;
478
479 Before_DML (
480 p_action => 'INSERT',
481 x_rowid => X_ROWID,
482 x_submission_yr => X_SUBMISSION_YR,
483 x_submission_number => X_SUBMISSION_NUMBER,
484 x_govt_semester => X_GOVT_SEMESTER,
485 x_cal_type => X_CAL_TYPE,
486 x_ci_sequence_number => X_CI_SEQUENCE_NUMBER,
487 x_teach_cal_type => X_TEACH_CAL_TYPE,
488 x_creation_date => X_LAST_UPDATE_DATE,
489 x_created_by => X_LAST_UPDATED_BY,
490 x_last_update_date => X_LAST_UPDATE_DATE,
491 x_last_updated_by => X_LAST_UPDATED_BY,
492 x_last_update_login => X_LAST_UPDATE_LOGIN
493 );
494
495 insert into IGS_ST_GVTSEMLOAD_OV (
496 SUBMISSION_YR,
497 SUBMISSION_NUMBER,
498 GOVT_SEMESTER,
499 CAL_TYPE,
500 CI_SEQUENCE_NUMBER,
501 TEACH_CAL_TYPE,
502 CREATION_DATE,
503 CREATED_BY,
504 LAST_UPDATE_DATE,
505 LAST_UPDATED_BY,
506 LAST_UPDATE_LOGIN
507 ) values (
508 NEW_REFERENCES.SUBMISSION_YR,
509 NEW_REFERENCES.SUBMISSION_NUMBER,
510 NEW_REFERENCES.GOVT_SEMESTER,
511 NEW_REFERENCES.CAL_TYPE,
512 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
513 NEW_REFERENCES.TEACH_CAL_TYPE,
514 X_LAST_UPDATE_DATE,
515 X_LAST_UPDATED_BY,
516 X_LAST_UPDATE_DATE,
517 X_LAST_UPDATED_BY,
518 X_LAST_UPDATE_LOGIN
519 );
520
521 open c;
522 fetch c into X_ROWID;
523 if (c%notfound) then
524 close c;
525 raise no_data_found;
526 end if;
527 close c;
528 After_DML (
529 p_action => 'INSERT',
530 x_rowid => X_ROWID
531 );
532 end INSERT_ROW;
533
534 procedure LOCK_ROW (
535 X_ROWID in VARCHAR2,
536 X_SUBMISSION_YR in NUMBER,
537 X_SUBMISSION_NUMBER in NUMBER,
538 X_GOVT_SEMESTER in NUMBER,
539 X_CAL_TYPE in VARCHAR2,
540 X_CI_SEQUENCE_NUMBER in NUMBER,
541 X_TEACH_CAL_TYPE in VARCHAR2
542 ) AS
543 cursor c1 is select
544 rowid
545 from IGS_ST_GVTSEMLOAD_OV
546 where ROWID = X_ROWID for update nowait;
547 tlinfo c1%rowtype;
548
549 begin
550 open c1;
551 fetch c1 into tlinfo;
552 if (c1%notfound) then
553 close c1;
554 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
555 IGS_GE_MSG_STACK.ADD;
556 app_exception.raise_exception;
557 return;
558 end if;
559 close c1;
560
561 return;
562 end LOCK_ROW;
563
564 procedure DELETE_ROW (
565 X_ROWID in VARCHAR2
566 ) AS
567 begin
568 Before_DML(
569 p_action => 'DELETE',
570 x_rowid => X_ROWID);
571
572 delete from IGS_ST_GVTSEMLOAD_OV
573 where ROWID = X_ROWID;
574 if (sql%notfound) then
575 raise no_data_found;
576 end if;
577 After_DML (
578 p_action => 'DELETE',
579 x_rowid => X_ROWID
580 );
581
582 end DELETE_ROW;
583
584 end IGS_ST_GVTSEMLOAD_OV_PKG;