[Home] [Help]
PACKAGE BODY: APPS.IGS_HE_SUBMSN_HEADER_PKG
Source
1 PACKAGE BODY igs_he_submsn_header_pkg AS
2 /* $Header: IGSWI10B.pls 115.5 2002/11/29 04:37:10 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_he_submsn_header%ROWTYPE;
6 new_references igs_he_submsn_header%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_sub_hdr_id IN NUMBER ,
12 x_submission_name IN VARCHAR2 ,
13 x_description IN VARCHAR2 ,
14 x_enrolment_start_date IN DATE ,
15 x_enrolment_end_date IN DATE ,
16 x_offset_days IN NUMBER ,
17 x_apply_to_atmpt_st_dt IN VARCHAR2 ,
18 x_apply_to_inst_st_dt IN VARCHAR2 ,
19 x_complete_flag IN VARCHAR2 ,
20 x_validation_country IN VARCHAR2 ,
21 x_creation_date IN DATE ,
22 x_created_by IN NUMBER ,
23 x_last_update_date IN DATE ,
24 x_last_updated_by IN NUMBER ,
25 x_last_update_login IN NUMBER
26 ) AS
27 /*
28 || Created By : rgopalan
29 || Created On : 15-JUN-2001
30 || Purpose : Initialises the Old and New references for the columns of the table.
31 || Known limitations, enhancements or remarks :
32 || Change History :
33 || Who When What
34 || (reverse chronological order - newest change first)
35 */
36
37 CURSOR cur_old_ref_values IS
38 SELECT *
39 FROM IGS_HE_SUBMSN_HEADER
40 WHERE rowid = x_rowid;
41
42 BEGIN
43
44 l_rowid := x_rowid;
45
46 -- Code for setting the Old and New Reference Values.
47 -- Populate Old Values.
48 OPEN cur_old_ref_values;
49 FETCH cur_old_ref_values INTO old_references;
50 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
51 CLOSE cur_old_ref_values;
52 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
53 igs_ge_msg_stack.add;
54 app_exception.raise_exception;
55 RETURN;
56 END IF;
57 CLOSE cur_old_ref_values;
58
59 -- Populate New Values.
60 new_references.sub_hdr_id := x_sub_hdr_id;
61 new_references.submission_name := x_submission_name;
62 new_references.description := x_description;
63 new_references.enrolment_start_date := x_enrolment_start_date;
64 new_references.enrolment_end_date := x_enrolment_end_date;
65 new_references.offset_days := x_offset_days;
66 new_references.apply_to_atmpt_st_dt := x_apply_to_atmpt_st_dt;
67 new_references.apply_to_inst_st_dt := x_apply_to_inst_st_dt;
68 new_references.validation_country := x_validation_country;
69 new_references.complete_flag := x_complete_flag;
70
71 IF (p_action = 'UPDATE') THEN
72 new_references.creation_date := old_references.creation_date;
73 new_references.created_by := old_references.created_by;
74 ELSE
75 new_references.creation_date := x_creation_date;
76 new_references.created_by := x_created_by;
77 END IF;
78
79 new_references.last_update_date := x_last_update_date;
80 new_references.last_updated_by := x_last_updated_by;
81 new_references.last_update_login := x_last_update_login;
82
83 END set_column_values;
84
85
86 PROCEDURE check_child_existance IS
87 /*
88 || Created By : rgopalan
89 || Created On : 15-JUN-2001
90 || Purpose : Checks for the existance of Child records.
91 || Known limitations, enhancements or remarks :
92 || Change History :
93 || Who When What
94 || (reverse chronological order - newest change first)
95 */
96 BEGIN
97
98 igs_he_submsn_return_pkg.get_fk_igs_he_submsn_header (
99 old_references.submission_name
100 );
101
102 END check_child_existance;
103
104
105 FUNCTION get_pk_for_validation (
106 x_submission_name IN VARCHAR2
107 ) RETURN BOOLEAN AS
108 /*
109 || Created By : rgopalan
110 || Created On : 15-JUN-2001
111 || Purpose : Validates the Primary Key of the table.
112 || Known limitations, enhancements or remarks :
113 || Change History :
114 || Who When What
115 || (reverse chronological order - newest change first)
116 */
117 CURSOR cur_rowid IS
118 SELECT rowid
119 FROM igs_he_submsn_header
120 WHERE submission_name = x_submission_name
121 FOR UPDATE NOWAIT;
122
123 lv_rowid cur_rowid%RowType;
124
125 BEGIN
126
127 OPEN cur_rowid;
128 FETCH cur_rowid INTO lv_rowid;
129 IF (cur_rowid%FOUND) THEN
130 CLOSE cur_rowid;
131 RETURN(TRUE);
132 ELSE
133 CLOSE cur_rowid;
134 RETURN(FALSE);
135 END IF;
136
137 END get_pk_for_validation;
138
139
140 PROCEDURE before_dml (
141 p_action IN VARCHAR2,
142 x_rowid IN VARCHAR2 ,
143 x_sub_hdr_id IN NUMBER ,
144 x_submission_name IN VARCHAR2 ,
145 x_description IN VARCHAR2 ,
146 x_enrolment_start_date IN DATE ,
147 x_enrolment_end_date IN DATE ,
148 x_offset_days IN NUMBER ,
149 x_apply_to_atmpt_st_dt IN VARCHAR2 ,
150 x_apply_to_inst_st_dt IN VARCHAR2 ,
151 x_complete_flag IN VARCHAR2 ,
152 x_validation_country IN VARCHAR2 ,
153 x_creation_date IN DATE ,
154 x_created_by IN NUMBER ,
155 x_last_update_date IN DATE ,
156 x_last_updated_by IN NUMBER ,
157 x_last_update_login IN NUMBER
158 ) AS
159 /*
160 || Created By : rgopalan
161 || Created On : 15-JUN-2001
162 || Purpose : Initialises the columns, Checks Constraints, Calls the
163 || Trigger Handlers for the table, before any DML operation.
164 || Known limitations, enhancements or remarks :
165 || Change History :
166 || Who When What
167 || (reverse chronological order - newest change first)
168 */
169 BEGIN
170
171 set_column_values (
172 p_action,
173 x_rowid,
174 x_sub_hdr_id,
175 x_submission_name,
176 x_description,
177 x_enrolment_start_date,
178 x_enrolment_end_date,
179 x_offset_days,
180 x_apply_to_atmpt_st_dt,
181 x_apply_to_inst_st_dt,
182 x_complete_flag,
183 x_validation_country ,
184 x_creation_date,
185 x_created_by,
186 x_last_update_date,
187 x_last_updated_by,
188 x_last_update_login
189 );
190
191 IF (p_action = 'INSERT') THEN
192 -- Call all the procedures related to Before Insert.
193 IF ( get_pk_for_validation(
194 new_references.submission_name
195 )
196 ) THEN
197 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
198 igs_ge_msg_stack.add;
199 app_exception.raise_exception;
200 END IF;
201 ELSIF (p_action = 'DELETE') THEN
202 -- Call all the procedures related to Before Delete.
203 check_child_existance;
204 ELSIF (p_action = 'VALIDATE_INSERT') THEN
205 -- Call all the procedures related to Before Insert.
206 IF ( get_pk_for_validation (
207 new_references.submission_name
208 )
209 ) THEN
210 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
211 igs_ge_msg_stack.add;
212 app_exception.raise_exception;
213 END IF;
214 ELSIF (p_action = 'VALIDATE_DELETE') THEN
215 check_child_existance;
216 END IF;
217
218 END before_dml;
219
220
221 PROCEDURE insert_row (
222 x_rowid IN OUT NOCOPY VARCHAR2,
223 x_sub_hdr_id IN OUT NOCOPY NUMBER,
224 x_submission_name IN OUT NOCOPY VARCHAR2,
225 x_description IN VARCHAR2,
226 x_enrolment_start_date IN DATE,
227 x_enrolment_end_date IN DATE,
228 x_offset_days IN NUMBER,
229 x_apply_to_atmpt_st_dt IN VARCHAR2,
230 x_apply_to_inst_st_dt IN VARCHAR2,
231 x_complete_flag IN VARCHAR2,
232 x_validation_country IN VARCHAR2 ,
233 x_mode IN VARCHAR2
234 ) AS
235 /*
236 || Created By : rgopalan
237 || Created On : 15-JUN-2001
238 || Purpose : Handles the INSERT DML logic for the table.
239 || Known limitations, enhancements or remarks :
240 || Change History :
241 || Who When What
242 || (reverse chronological order - newest change first)
243 */
244 CURSOR c IS
245 SELECT rowid
246 FROM igs_he_submsn_header
247 WHERE submission_name = x_submission_name;
248
249 x_last_update_date DATE;
250 x_last_updated_by NUMBER;
251 x_last_update_login NUMBER;
252
253 BEGIN
254
255 x_last_update_date := SYSDATE;
256 IF (x_mode = 'I') THEN
257 x_last_updated_by := 1;
258 x_last_update_login := 0;
259 ELSIF (x_mode = 'R') THEN
260 x_last_updated_by := fnd_global.user_id;
261 IF (x_last_updated_by IS NULL) THEN
262 x_last_updated_by := -1;
263 END IF;
264 x_last_update_login := fnd_global.login_id;
265 IF (x_last_update_login IS NULL) THEN
266 x_last_update_login := -1;
267 END IF;
268 ELSE
269 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
270 igs_ge_msg_stack.add;
271 app_exception.raise_exception;
272 END IF;
273
274 SELECT igs_he_submsn_header_s.NEXTVAL
275 INTO x_sub_hdr_id
276 FROM dual;
277
278 before_dml(
279 p_action => 'INSERT',
280 x_rowid => x_rowid,
281 x_sub_hdr_id => x_sub_hdr_id,
282 x_submission_name => x_submission_name,
283 x_description => x_description,
284 x_enrolment_start_date => x_enrolment_start_date,
285 x_enrolment_end_date => x_enrolment_end_date,
286 x_offset_days => x_offset_days,
287 x_apply_to_atmpt_st_dt => x_apply_to_atmpt_st_dt,
288 x_apply_to_inst_st_dt => x_apply_to_inst_st_dt,
289 x_complete_flag => x_complete_flag,
290 x_validation_country => x_validation_country,
291 x_creation_date => x_last_update_date,
292 x_created_by => x_last_updated_by,
293 x_last_update_date => x_last_update_date,
294 x_last_updated_by => x_last_updated_by,
295 x_last_update_login => x_last_update_login
296 );
297
298 INSERT INTO igs_he_submsn_header (
299 sub_hdr_id,
300 submission_name,
301 description,
302 enrolment_start_date,
303 enrolment_end_date,
304 offset_days,
305 apply_to_atmpt_st_dt,
306 apply_to_inst_st_dt,
307 complete_flag,
308 validation_country,
309 creation_date,
310 created_by,
311 last_update_date,
312 last_updated_by,
313 last_update_login
314 ) VALUES (
315 new_references.sub_hdr_id,
316 new_references.submission_name,
317 new_references.description,
318 new_references.enrolment_start_date,
319 new_references.enrolment_end_date,
320 new_references.offset_days,
321 new_references.apply_to_atmpt_st_dt,
322 new_references.apply_to_inst_st_dt,
323 new_references.complete_flag,
324 new_references.validation_country,
325 x_last_update_date,
326 x_last_updated_by,
327 x_last_update_date,
328 x_last_updated_by,
329 x_last_update_login
330 );
331
332 OPEN c;
333 FETCH c INTO x_rowid;
334 IF (c%NOTFOUND) THEN
335 CLOSE c;
336 RAISE NO_DATA_FOUND;
337 END IF;
338 CLOSE c;
339
340 END insert_row;
341
342
343 PROCEDURE lock_row (
344 x_rowid IN VARCHAR2,
345 x_sub_hdr_id IN NUMBER,
346 x_submission_name IN VARCHAR2,
347 x_description IN VARCHAR2,
348 x_enrolment_start_date IN DATE,
349 x_enrolment_end_date IN DATE,
350 x_offset_days IN NUMBER,
351 x_apply_to_atmpt_st_dt IN VARCHAR2,
352 x_apply_to_inst_st_dt IN VARCHAR2,
353 x_complete_flag IN VARCHAR2,
354 x_validation_country IN VARCHAR2
355 ) AS
356 /*
357 || Created By : rgopalan
358 || Created On : 15-JUN-2001
359 || Purpose : Handles the LOCK mechanism for the table.
360 || Known limitations, enhancements or remarks :
361 || Change History :
362 || Who When What
363 || (reverse chronological order - newest change first)
364 */
365 CURSOR c1 IS
366 SELECT
367 sub_hdr_id,
368 description,
369 enrolment_start_date,
370 enrolment_end_date,
371 offset_days,
372 apply_to_atmpt_st_dt,
373 apply_to_inst_st_dt,
374 complete_flag,
375 validation_country
376 FROM igs_he_submsn_header
377 WHERE rowid = x_rowid
378 FOR UPDATE NOWAIT;
379
380 tlinfo c1%ROWTYPE;
381
382 BEGIN
383
384 OPEN c1;
385 FETCH c1 INTO tlinfo;
386 IF (c1%notfound) THEN
387 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
388 igs_ge_msg_stack.add;
389 CLOSE c1;
390 app_exception.raise_exception;
391 RETURN;
392 END IF;
393 CLOSE c1;
394
395 IF (
396 (tlinfo.sub_hdr_id = x_sub_hdr_id)
397 AND (tlinfo.description = x_description)
398 AND (tlinfo.enrolment_start_date = x_enrolment_start_date)
399 AND (tlinfo.enrolment_end_date = x_enrolment_end_date)
400 AND ((tlinfo.offset_days = x_offset_days) OR ((tlinfo.offset_days IS NULL) AND (X_offset_days IS NULL)))
401 AND ((tlinfo.apply_to_atmpt_st_dt = x_apply_to_atmpt_st_dt) OR ((tlinfo.apply_to_atmpt_st_dt IS NULL) AND (X_apply_to_atmpt_st_dt IS NULL)))
402 AND ((tlinfo.apply_to_inst_st_dt = x_apply_to_inst_st_dt) OR ((tlinfo.apply_to_inst_st_dt IS NULL) AND (X_apply_to_inst_st_dt IS NULL)))
403 AND ((tlinfo.validation_country = x_validation_country) OR ((tlinfo.validation_country IS NULL) AND (X_validation_country IS NULL)))
404 AND (tlinfo.complete_flag = x_complete_flag)
405 ) THEN
406 NULL;
407 ELSE
408 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
409 igs_ge_msg_stack.add;
410 app_exception.raise_exception;
411 END IF;
412
413 RETURN;
414
415 END lock_row;
416
417
418 PROCEDURE update_row (
419 x_rowid IN VARCHAR2,
420 x_sub_hdr_id IN NUMBER,
421 x_submission_name IN VARCHAR2,
422 x_description IN VARCHAR2,
423 x_enrolment_start_date IN DATE,
424 x_enrolment_end_date IN DATE,
425 x_offset_days IN NUMBER,
426 x_apply_to_atmpt_st_dt IN VARCHAR2,
427 x_apply_to_inst_st_dt IN VARCHAR2,
428 x_complete_flag IN VARCHAR2,
429 x_validation_country IN VARCHAR2 ,
430 x_mode IN VARCHAR2
431 ) AS
432 /*
433 || Created By : rgopalan
434 || Created On : 15-JUN-2001
435 || Purpose : Handles the UPDATE DML logic for the table.
436 || Known limitations, enhancements or remarks :
437 || Change History :
438 || Who When What
439 || (reverse chronological order - newest change first)
440 */
441 x_last_update_date DATE ;
442 x_last_updated_by NUMBER;
443 x_last_update_login NUMBER;
444
445 BEGIN
446
447 x_last_update_date := SYSDATE;
448 IF (X_MODE = 'I') THEN
449 x_last_updated_by := 1;
450 x_last_update_login := 0;
451 ELSIF (x_mode = 'R') THEN
452 x_last_updated_by := fnd_global.user_id;
453 IF x_last_updated_by IS NULL THEN
454 x_last_updated_by := -1;
455 END IF;
456 x_last_update_login := fnd_global.login_id;
457 IF (x_last_update_login IS NULL) THEN
458 x_last_update_login := -1;
459 END IF;
460 ELSE
461 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
462 igs_ge_msg_stack.add;
463 app_exception.raise_exception;
464 END IF;
465
466 before_dml(
467 p_action => 'UPDATE',
468 x_rowid => x_rowid,
469 x_sub_hdr_id => x_sub_hdr_id,
470 x_submission_name => x_submission_name,
471 x_description => x_description,
472 x_enrolment_start_date => x_enrolment_start_date,
473 x_enrolment_end_date => x_enrolment_end_date,
474 x_offset_days => x_offset_days,
475 x_apply_to_atmpt_st_dt => x_apply_to_atmpt_st_dt,
476 x_apply_to_inst_st_dt => x_apply_to_inst_st_dt,
477 x_complete_flag => x_complete_flag,
478 x_validation_country => x_validation_country,
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 UPDATE igs_he_submsn_header
487 SET
488 sub_hdr_id = new_references.sub_hdr_id,
489 description = new_references.description,
490 enrolment_start_date = new_references.enrolment_start_date,
491 enrolment_end_date = new_references.enrolment_end_date,
492 offset_days = new_references.offset_days,
493 apply_to_atmpt_st_dt = new_references.apply_to_atmpt_st_dt,
494 apply_to_inst_st_dt = new_references.apply_to_inst_st_dt,
495 complete_flag = new_references.complete_flag,
496 validation_country = new_references.validation_country,
497 last_update_date = x_last_update_date,
498 last_updated_by = x_last_updated_by,
499 last_update_login = x_last_update_login
500 WHERE rowid = x_rowid;
501
502 IF (SQL%NOTFOUND) THEN
503 RAISE NO_DATA_FOUND;
504 END IF;
505
506 END update_row;
507
508
509 PROCEDURE add_row (
510 x_rowid IN OUT NOCOPY VARCHAR2,
511 x_sub_hdr_id IN OUT NOCOPY NUMBER,
512 x_submission_name IN OUT NOCOPY VARCHAR2,
513 x_description IN VARCHAR2,
514 x_enrolment_start_date IN DATE,
515 x_enrolment_end_date IN DATE,
516 x_offset_days IN NUMBER,
517 x_apply_to_atmpt_st_dt IN VARCHAR2,
518 x_apply_to_inst_st_dt IN VARCHAR2,
519 x_complete_flag IN VARCHAR2,
520 x_validation_country IN VARCHAR2,
521 x_mode IN VARCHAR2
522 ) AS
523 /*
524 || Created By : rgopalan
525 || Created On : 15-JUN-2001
526 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
527 || Known limitations, enhancements or remarks :
528 || Change History :
529 || Who When What
530 || (reverse chronological order - newest change first)
531 */
532 CURSOR c1 IS
533 SELECT rowid
534 FROM igs_he_submsn_header
535 WHERE submission_name = x_submission_name;
536
537 BEGIN
538
539 OPEN c1;
540 FETCH c1 INTO x_rowid;
541 IF (c1%NOTFOUND) THEN
542 CLOSE c1;
543
544 insert_row (
545 x_rowid,
546 x_sub_hdr_id,
547 x_submission_name,
548 x_description,
549 x_enrolment_start_date,
550 x_enrolment_end_date,
551 x_offset_days,
552 x_apply_to_atmpt_st_dt,
553 x_apply_to_inst_st_dt,
554 x_complete_flag,
555 x_validation_country,
556 x_mode
557 );
558 RETURN;
559 END IF;
560 CLOSE c1;
561
562 update_row (
563 x_rowid,
564 x_sub_hdr_id,
565 x_submission_name,
566 x_description,
567 x_enrolment_start_date,
568 x_enrolment_end_date,
569 x_offset_days,
570 x_apply_to_atmpt_st_dt,
571 x_apply_to_inst_st_dt,
572 x_complete_flag,
573 x_validation_country,
574 x_mode
575 );
576
577 END add_row;
578
579
580 PROCEDURE delete_row (
581 x_rowid IN VARCHAR2
582 ) AS
583 /*
584 || Created By : rgopalan
585 || Created On : 15-JUN-2001
586 || Purpose : Handles the DELETE DML logic for the table.
587 || Known limitations, enhancements or remarks :
588 || Change History :
589 || Who When What
590 || (reverse chronological order - newest change first)
591 */
592 BEGIN
593
594 before_dml (
595 p_action => 'DELETE',
596 x_rowid => x_rowid
597 );
598
599 DELETE FROM igs_he_submsn_header
600 WHERE rowid = x_rowid;
601
602 IF (SQL%NOTFOUND) THEN
603 RAISE NO_DATA_FOUND;
604 END IF;
605
606 END delete_row;
607
608
609 END igs_he_submsn_header_pkg;