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