[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_ACAD_INTENTS_PKG
Source
1 PACKAGE BODY igs_pe_acad_intents_pkg AS
2 /* $Header: IGSNIB6B.pls 120.0 2006/05/23 12:32:39 vskumar noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_acad_intents%ROWTYPE;
6 new_references igs_pe_acad_intents%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_acad_intent_id IN NUMBER,
12 x_person_id IN NUMBER,
13 x_cal_type IN VARCHAR2,
14 x_sequence_number IN NUMBER,
15 x_acad_intent_code IN VARCHAR2,
16 x_active_flag IN VARCHAR2,
17 x_creation_date IN DATE,
18 x_created_by IN NUMBER,
19 x_last_update_date IN DATE,
20 x_last_updated_by IN NUMBER,
21 x_last_update_login IN NUMBER
22 ) AS
23 /*
24 || Created By : vskumar
25 || Created On : 25-APR-2006
26 || Purpose : Initialises the Old and New references for the columns of the table.
27 || Known limitations, enhancements or remarks :
28 || Change History :
29 || Who When What
30 || (reverse chronological order - newest change first)
31 */
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM igs_pe_acad_intents
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 OPEN cur_old_ref_values;
45 FETCH cur_old_ref_values INTO old_references;
46 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
47 CLOSE cur_old_ref_values;
48 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
49 igs_ge_msg_stack.add;
50 app_exception.raise_exception;
51 RETURN;
52 END IF;
53 CLOSE cur_old_ref_values;
54
55 -- Populate New Values.
56 new_references.acad_intent_id := x_acad_intent_id;
57 new_references.person_id := x_person_id;
58 new_references.cal_type := x_cal_type;
59 new_references.sequence_number := x_sequence_number;
60 new_references.acad_intent_code := x_acad_intent_code;
61 new_references.active_flag := x_active_flag;
62
63 IF (p_action = 'UPDATE') THEN
64 new_references.creation_date := old_references.creation_date;
65 new_references.created_by := old_references.created_by;
66 ELSE
67 new_references.creation_date := x_creation_date;
68 new_references.created_by := x_created_by;
69 END IF;
70
71 new_references.last_update_date := x_last_update_date;
72 new_references.last_updated_by := x_last_updated_by;
73 new_references.last_update_login := x_last_update_login;
74
75 END set_column_values;
76
77
78 PROCEDURE AfterRowInsertUpdate(
79 p_inserting IN BOOLEAN,
80 p_updating IN BOOLEAN,
81 p_deleting IN BOOLEAN
82 ) as
83 ------------------------------------------------------------------------------------------
84 --Created by : vskumar
85 --Date created: 26-APR-2006
86 --
87 --Purpose:
88 --
89 --Known limitations/enhancements and/or remarks:
90 --
91 --Change History:
92 --Who When What
93 ----------------------------------------------------------------------------------------------
94 lv_rowid varchar2(25);
95 lv_acad_intent_code IGS_PE_ACAD_INTENTS.acad_intent_code%TYPE;
96 ln_acad_intent_id NUMBER;
97
98 CURSOR c_act (cp_person_id IGS_PE_ACAD_INTENTS.person_id%TYPE,
99 cp_cal_type IGS_PE_ACAD_INTENTS.cal_type%TYPE,
100 cp_sequence_number IGS_PE_ACAD_INTENTS.sequence_number%TYPE,
101 cp_active_flag IGS_PE_ACAD_INTENTS.active_flag%TYPE,
102 cp_l_rowid varchar2) IS
103 SELECT rowid, acad_intent_code
104 FROM IGS_PE_ACAD_INTENTS
105 WHERE person_id = cp_person_id
106 AND cal_type = cp_cal_type
107 AND sequence_number = cp_sequence_number
108 AND active_flag = cp_active_flag
109 AND ROWID <> cp_l_rowid;
110
111
112 BEGIN
113 IF p_inserting THEN
114 OPEN c_act (new_references.person_id,
115 new_references.cal_type,
116 new_references.sequence_number,
117 'Y',
118 l_rowid);
119
120 FETCH c_act INTO lv_rowid, lv_acad_intent_code;
121 IF (c_act%FOUND) THEN
122 UPDATE igs_pe_acad_intents
123 SET active_flag = 'N'
124 WHERE rowid= lv_rowid;
125 END IF;
126
127 CLOSE c_act;
128
129 IGS_PE_WF_GEN.raise_acad_intent_event(P_ACAD_INTENT_ID => new_references.acad_intent_id,
130 P_PERSON_ID=> new_references.person_id,
131 P_CAL_TYPE=>new_references.cal_type,
132 P_CAL_SEQ_NUMBER=>new_references.sequence_number,
133 P_ACAD_INTENT_CODE=>new_references.acad_intent_code,
134 P_OLD_ACAD_INTENT_CODE=>lv_acad_intent_code);
135
136 ELSIF p_updating THEN
137 INSERT INTO igs_pe_acad_intents (
138 acad_intent_id,
139 person_id,
140 cal_type,
141 sequence_number,
142 acad_intent_code,
143 active_flag,
144 creation_date,
145 created_by,
146 last_update_date,
147 last_updated_by,
148 last_update_login
149 ) VALUES (
150 igs_pe_acad_intents_s.NEXTVAL,
151 new_references.person_id,
152 new_references.cal_type,
153 new_references.sequence_number,
154 new_references.acad_intent_code,
155 'Y',
156 SYSDATE,
157 fnd_global.user_id,
158 SYSDATE,
159 fnd_global.user_id,
160 fnd_global.login_id ) RETURNING acad_intent_id INTO ln_acad_intent_id;
161
162 IGS_PE_WF_GEN.raise_acad_intent_event(P_ACAD_INTENT_ID => ln_acad_intent_id,
163 P_PERSON_ID=> new_references.person_id,
164 P_CAL_TYPE=>new_references.cal_type,
165 P_CAL_SEQ_NUMBER=>new_references.sequence_number,
166 P_ACAD_INTENT_CODE=>new_references.acad_intent_code,
167 P_OLD_ACAD_INTENT_CODE=>old_references.acad_intent_code);
168
169
170 END IF;
171 END AfterRowInsertUpdate;
172
173 PROCEDURE Check_Parent_Existance as
174 BEGIN
175 IF (((old_references.person_id = new_references.person_id)) OR
176 ((new_references.person_id IS NULL))) THEN
177 NULL;
178 ELSE
179 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
180 new_references.person_id ) THEN
181 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
182 IGS_GE_MSG_STACK.ADD;
183 App_Exception.Raise_Exception;
184 END IF;
185 END IF;
186 END Check_Parent_Existance;
187
188 FUNCTION get_pk_for_validation (
189 x_acad_intent_id IN NUMBER
190 ) RETURN BOOLEAN AS
191 /*
192 || Created By : vskumar
193 || Created On : 25-APR-2006
194 || Purpose : Validates the Primary Key of the table.
195 || Known limitations, enhancements or remarks :
196 || Change History :
197 || Who When What
198 || (reverse chronological order - newest change first)
199 */
200 CURSOR cur_rowid IS
201 SELECT rowid
202 FROM igs_pe_acad_intents
203 WHERE acad_intent_id = x_acad_intent_id
204 FOR UPDATE NOWAIT;
205
206 lv_rowid cur_rowid%RowType;
207
208 BEGIN
209
210 OPEN cur_rowid;
211 FETCH cur_rowid INTO lv_rowid;
212 IF (cur_rowid%FOUND) THEN
213 CLOSE cur_rowid;
214 RETURN(TRUE);
215 ELSE
216 CLOSE cur_rowid;
217 RETURN(FALSE);
218 END IF;
219
220 END get_pk_for_validation;
221
222
223 PROCEDURE before_dml (
224 p_action IN VARCHAR2,
225 x_rowid IN VARCHAR2,
226 x_acad_intent_id IN NUMBER,
227 x_person_id IN NUMBER,
228 x_cal_type IN VARCHAR2,
229 x_sequence_number IN NUMBER,
230 x_acad_intent_code IN VARCHAR2,
231 x_active_flag IN VARCHAR2,
232 x_creation_date IN DATE,
233 x_created_by IN NUMBER,
234 x_last_update_date IN DATE,
235 x_last_updated_by IN NUMBER,
236 x_last_update_login IN NUMBER
237 ) AS
238 /*
239 || Created By : vskumar
240 || Created On : 25-APR-2006
241 || Purpose : Initialises the columns, Checks Constraints, Calls the
242 || Trigger Handlers for the table, before any DML operation.
243 || Known limitations, enhancements or remarks :
244 || Change History :
245 || Who When What
246 || (reverse chronological order - newest change first)
247 */
248 BEGIN
249
250 set_column_values (
251 p_action,
252 x_rowid,
253 x_acad_intent_id,
254 x_person_id,
255 x_cal_type,
256 x_sequence_number,
257 x_acad_intent_code,
258 x_active_flag,
259 x_creation_date,
260 x_created_by,
261 x_last_update_date,
262 x_last_updated_by,
263 x_last_update_login
264 );
265
266 IF (p_action = 'INSERT') THEN
267 IF ( get_pk_for_validation(
268 new_references.acad_intent_id
269 )
270 ) THEN
271 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
272 igs_ge_msg_stack.add;
273 app_exception.raise_exception;
274 END IF;
275
276 Check_Parent_Existance; -- if procedure present
277 ELSIF (p_action = 'VALIDATE_INSERT') THEN
278 -- Call all the procedures related to Before Insert.
279 IF ( get_pk_for_validation (
280 new_references.acad_intent_id
281 )
282 ) THEN
283 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
284 igs_ge_msg_stack.add;
285 app_exception.raise_exception;
286 END IF;
287 END IF;
288
289 END before_dml;
290
291 PROCEDURE After_DML (
292 p_action IN VARCHAR2,
293 x_rowid IN VARCHAR2
294 ) as
295 BEGIN
296 l_rowid := x_rowid;
297 IF (p_action = 'INSERT') THEN
298 AfterRowInsertUpdate(p_inserting => TRUE,
299 p_updating => FALSE,
300 p_deleting => FALSE);
301
302 ELSIF (p_action = 'UPDATE') THEN
303 AfterRowInsertUpdate(p_inserting => FALSE,
304 p_updating => TRUE,
305 p_deleting => FALSE);
306
307 ELSIF (p_action = 'DELETE') THEN
308 -- Call all the procedures related to After Delete.
309 NULL;
310 END IF;
311 END After_DML;
312
313 PROCEDURE insert_row (
314 x_rowid IN OUT NOCOPY VARCHAR2,
315 x_acad_intent_id IN OUT NOCOPY NUMBER,
316 x_person_id IN NUMBER,
317 x_cal_type IN VARCHAR2,
318 x_sequence_number IN NUMBER,
319 x_acad_intent_code IN VARCHAR2,
320 x_active_flag IN VARCHAR2,
321 x_mode IN VARCHAR2
322 ) AS
323 /*
324 || Created By : vskumar
325 || Created On : 25-APR-2006
326 || Purpose : Handles the INSERT DML logic for the table.
327 || Known limitations, enhancements or remarks :
328 || Change History :
329 || Who When What
330 || (reverse chronological order - newest change first)
331 */
332
333 x_last_update_date DATE;
334 x_last_updated_by NUMBER;
335 x_last_update_login NUMBER;
336
337 BEGIN
338
339 x_last_update_date := SYSDATE;
340 IF (x_mode = 'I') THEN
341 x_last_updated_by := 1;
342 x_last_update_login := 0;
343 ELSIF (x_mode IN ('R','S')) THEN
344 x_last_updated_by := fnd_global.user_id;
345 IF (x_last_updated_by IS NULL) THEN
346 x_last_updated_by := -1;
347 END IF;
348 x_last_update_login := fnd_global.login_id;
349 IF (x_last_update_login IS NULL) THEN
350 x_last_update_login := -1;
351 END IF;
352 ELSE
353 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
354 fnd_message.set_token ('ROUTINE', 'igs_pe_acad_intents_PKG.INSERT_ROW');
355 igs_ge_msg_stack.add;
356 app_exception.raise_exception;
357 END IF;
358
359 x_acad_intent_id := NULL;
360
361 before_dml(
362 p_action => 'INSERT',
363 x_rowid => x_rowid,
364 x_acad_intent_id => x_acad_intent_id,
365 x_person_id => x_person_id,
366 x_cal_type => x_cal_type,
367 x_sequence_number => x_sequence_number,
368 x_acad_intent_code => x_acad_intent_code,
369 x_active_flag => x_active_flag,
370 x_creation_date => x_last_update_date,
371 x_created_by => x_last_updated_by,
372 x_last_update_date => x_last_update_date,
373 x_last_updated_by => x_last_updated_by,
374 x_last_update_login => x_last_update_login
375 );
376
377 IF (x_mode = 'S') THEN
378 igs_sc_gen_001.set_ctx('R');
379 END IF;
380
381 INSERT INTO igs_pe_acad_intents (
382 acad_intent_id,
383 person_id,
384 cal_type,
385 sequence_number,
386 acad_intent_code,
387 active_flag,
388 creation_date,
389 created_by,
390 last_update_date,
391 last_updated_by,
392 last_update_login
393 ) VALUES (
394 igs_pe_acad_intents_s.NEXTVAL,
395 new_references.person_id,
396 new_references.cal_type,
397 new_references.sequence_number,
398 new_references.acad_intent_code,
399 'Y',
400 x_last_update_date,
401 x_last_updated_by,
402 x_last_update_date,
403 x_last_updated_by,
404 x_last_update_login
405 ) RETURNING ROWID, acad_intent_id INTO x_rowid, x_acad_intent_id;
406
407 new_references.acad_intent_id := x_acad_intent_id;
408
409 IF (x_mode = 'S') THEN
410 igs_sc_gen_001.unset_ctx('R');
411 END IF;
412
413
414 After_DML(
415 p_action => 'INSERT',
416 x_rowid => X_ROWID
417 );
418 EXCEPTION
419 WHEN OTHERS THEN
420 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
421 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
422 fnd_message.set_token ('ERR_CD', SQLCODE);
423 igs_ge_msg_stack.add;
424 igs_sc_gen_001.unset_ctx('R');
425 app_exception.raise_exception;
426 ELSE
427 igs_sc_gen_001.unset_ctx('R');
428 RAISE;
429 END IF;
430 END insert_row;
431
432
433 PROCEDURE lock_row (
434 x_rowid IN VARCHAR2,
435 x_acad_intent_id IN NUMBER,
436 x_person_id IN NUMBER,
437 x_cal_type IN VARCHAR2,
438 x_sequence_number IN NUMBER,
439 x_acad_intent_code IN VARCHAR2,
440 x_active_flag IN VARCHAR2
441 ) AS
442 /*
443 || Created By : vskumar
444 || Created On : 25-APR-2006
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 person_id,
454 cal_type,
455 sequence_number,
456 acad_intent_code,
457 active_flag
458 FROM igs_pe_acad_intents
459 WHERE rowid = x_rowid
460 FOR UPDATE NOWAIT;
461
462 tlinfo c1%ROWTYPE;
463
464 BEGIN
465
466 OPEN c1;
467 FETCH c1 INTO tlinfo;
468 IF (c1%notfound) THEN
469 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
470 igs_ge_msg_stack.add;
471 CLOSE c1;
472 app_exception.raise_exception;
473 RETURN;
474 END IF;
475 CLOSE c1;
476
477 IF (
478 (tlinfo.person_id = x_person_id)
479 AND (tlinfo.cal_type = x_cal_type)
480 AND (tlinfo.sequence_number = x_sequence_number)
481 AND (tlinfo.acad_intent_code = x_acad_intent_code)
482 AND (tlinfo.active_flag = x_active_flag)
483 ) THEN
484 NULL;
485 ELSE
486 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
487 igs_ge_msg_stack.add;
488 app_exception.raise_exception;
489 END IF;
490
491 RETURN;
492
493 END lock_row;
494
495
496 PROCEDURE update_row (
497 x_rowid IN VARCHAR2,
498 x_acad_intent_id IN NUMBER,
499 x_person_id IN NUMBER,
500 x_cal_type IN VARCHAR2,
501 x_sequence_number IN NUMBER,
502 x_acad_intent_code IN VARCHAR2,
503 x_active_flag IN VARCHAR2,
504 x_mode IN VARCHAR2
505 ) AS
506 /*
507 || Created By : vskumar
508 || Created On : 25-APR-2006
509 || Purpose : Handles the UPDATE DML logic for the table.
510 || Known limitations, enhancements or remarks :
511 || Change History :
512 || Who When What
513 || (reverse chronological order - newest change first)
514 */
515 x_last_update_date DATE ;
516 x_last_updated_by NUMBER;
517 x_last_update_login NUMBER;
518
519 BEGIN
520
521 x_last_update_date := SYSDATE;
522 IF (X_MODE = 'I') THEN
523 x_last_updated_by := 1;
524 x_last_update_login := 0;
525 ELSIF (x_mode IN ('R','S')) THEN
526 x_last_updated_by := fnd_global.user_id;
527 IF x_last_updated_by IS NULL THEN
528 x_last_updated_by := -1;
529 END IF;
530 x_last_update_login := fnd_global.login_id;
531 IF (x_last_update_login IS NULL) THEN
532 x_last_update_login := -1;
533 END IF;
534 ELSE
535 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
536 fnd_message.set_token ('ROUTINE', 'igs_pe_acad_intents_PKG.UPDATE_ROW');
537 igs_ge_msg_stack.add;
538 app_exception.raise_exception;
539 END IF;
540
541 before_dml(
542 p_action => 'UPDATE',
543 x_rowid => x_rowid,
544 x_acad_intent_id => x_acad_intent_id,
545 x_person_id => x_person_id,
546 x_cal_type => x_cal_type,
547 x_sequence_number => x_sequence_number,
548 x_acad_intent_code => x_acad_intent_code,
549 x_active_flag => x_active_flag,
550 x_creation_date => x_last_update_date,
551 x_created_by => x_last_updated_by,
552 x_last_update_date => x_last_update_date,
553 x_last_updated_by => x_last_updated_by,
554 x_last_update_login => x_last_update_login
555 );
556
557 IF (x_mode = 'S') THEN
558 igs_sc_gen_001.set_ctx('R');
559 END IF;
560
561 UPDATE igs_pe_acad_intents
562 SET
563 person_id = new_references.person_id,
564 cal_type = new_references.cal_type,
565 sequence_number = new_references.sequence_number,
566 acad_intent_code = old_references.acad_intent_code,
567 active_flag = 'N',
568 last_update_date = x_last_update_date,
569 last_updated_by = x_last_updated_by,
570 last_update_login = x_last_update_login
571 WHERE rowid = x_rowid;
572
573 if (sql%notfound) then
574 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
575 igs_ge_msg_stack.add;
576 igs_sc_gen_001.unset_ctx('R');
577 app_exception.raise_exception;
578 end if;
579 IF (x_mode = 'S') THEN
580 igs_sc_gen_001.unset_ctx('R');
581 END IF;
582
583 After_DML(
584 p_action => 'UPDATE',
585 x_rowid => X_ROWID
586 );
587
588 EXCEPTION
589 WHEN OTHERS THEN
590 IF (SQLCODE = (-28115)) THEN
591 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
592 fnd_message.set_token ('ERR_CD', SQLCODE);
593 igs_ge_msg_stack.add;
594 igs_sc_gen_001.unset_ctx('R');
595 app_exception.raise_exception;
596 ELSE
597 igs_sc_gen_001.unset_ctx('R');
598 RAISE;
599 END IF;
600 END update_row;
601
602
603 PROCEDURE add_row (
604 x_rowid IN OUT NOCOPY VARCHAR2,
605 x_acad_intent_id IN OUT NOCOPY NUMBER,
606 x_person_id IN NUMBER,
607 x_cal_type IN VARCHAR2,
608 x_sequence_number IN NUMBER,
609 x_acad_intent_code IN VARCHAR2,
610 x_active_flag IN VARCHAR2,
611 x_mode IN VARCHAR2
612 ) AS
613 /*
614 || Created By : vskumar
615 || Created On : 25-APR-2006
616 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
617 || Known limitations, enhancements or remarks :
618 || Change History :
619 || Who When What
620 || (reverse chronological order - newest change first)
621 */
622 CURSOR c1 IS
623 SELECT rowid
624 FROM igs_pe_acad_intents
625 WHERE acad_intent_id = x_acad_intent_id;
626
627 BEGIN
628
629 OPEN c1;
630 FETCH c1 INTO x_rowid;
631 IF (c1%NOTFOUND) THEN
632 CLOSE c1;
633
634 insert_row (
635 x_rowid,
636 x_acad_intent_id,
637 x_person_id,
638 x_cal_type,
639 x_sequence_number,
640 x_acad_intent_code,
641 x_active_flag,
642 x_mode
643 );
644 RETURN;
645 END IF;
646 CLOSE c1;
647
648 update_row (
649 x_rowid,
650 x_acad_intent_id,
651 x_person_id,
652 x_cal_type,
653 x_sequence_number,
654 x_acad_intent_code,
655 x_active_flag,
656 x_mode
657 );
658
659 END add_row;
660
661
662 PROCEDURE delete_row (
663 x_rowid IN VARCHAR2,
664 x_mode IN VARCHAR2
665 ) AS
666 /*
667 || Created By : vskumar
668 || Created On : 25-APR-2006
669 || Purpose : Handles the DELETE DML logic for the table.
670 || Known limitations, enhancements or remarks :
671 || Change History :
672 || Who When What
673 || (reverse chronological order - newest change first)
674 */
675 BEGIN
676
677 before_dml (
678 p_action => 'DELETE',
679 x_rowid => x_rowid
680 );
681
682 IF (x_mode = 'S') THEN
683 igs_sc_gen_001.set_ctx('R');
684 END IF;
685 DELETE FROM igs_pe_acad_intents
686 WHERE rowid = x_rowid;
687
688 if (sql%notfound) then
689 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
690 igs_ge_msg_stack.add;
691 igs_sc_gen_001.unset_ctx('R');
692 app_exception.raise_exception;
693 end if;
694 IF (x_mode = 'S') THEN
695 igs_sc_gen_001.unset_ctx('R');
696 END IF;
697
698 END delete_row;
699
700
701 END igs_pe_acad_intents_pkg;