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