1 PACKAGE BODY igs_pe_felony_dtls_pkg AS
2 /* $Header: IGSNI91B.pls 120.5 2005/10/17 02:22:21 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_felony_dtls%ROWTYPE;
6 new_references igs_pe_felony_dtls%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_felony_details_id IN NUMBER ,
12 x_person_id IN NUMBER ,
13 x_crime_nature IN VARCHAR2 ,
14 x_crime_date IN DATE ,
15 x_convict_ind IN VARCHAR2 ,
16 x_disp_action_info 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 : cdcruz
25 || Created On : 21-SEP-2001
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_felony_dtls
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.felony_details_id := x_felony_details_id;
57 new_references.person_id := x_person_id;
58 new_references.crime_nature := x_crime_nature;
59 new_references.crime_date := x_crime_date;
60 new_references.convict_ind := x_convict_ind;
61 new_references.disp_action_info := x_disp_action_info;
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 PROCEDURE BeforeRowInsertUpdate1(
78 p_inserting IN BOOLEAN,
79 p_updating IN BOOLEAN,
80 p_deleting IN BOOLEAN
81 ) as
82 ------------------------------------------------------------------------------------------
83 --Created by : pkpatel
84 --Date created: 06-JUN-2002
85 --
86 --Purpose:
87 --Known limitations/enhancements and/or remarks:
88 --
89 --Change History:
90 --Who When What
91 --gmaheswa 12-Aug-2005 4327807: Added the validation Crime date shouldn't be greater than sysdate.
92 ----------------------------------------------------------------------------------------------
93 CURSOR validate_cr_dt IS
94 SELECT birth_date FROM
95 IGS_PE_PERSON_BASE_V
96 WHERE person_id = new_references.person_id ;
97
98 l_birth_dt IGS_PE_PERSON_BASE_V.BIRTH_DATE%TYPE;
99
100 Cursor mes_token IS
101 SELECT meaning FROM
102 IGS_LOOKUP_VALUES WHERE
103 lookup_code = 'CRIME_DATE' AND
104 lookup_type = 'PE_MESSAGE_TOKENS' AND
105 ENABLED_FLAG = 'Y';
106
107 l_meaning igs_lookup_values.meaning%TYPE;
108 BEGIN
109 IF p_inserting OR p_updating THEN
110 OPEN validate_cr_dt;
111 FETCH validate_cr_dt INTO l_birth_dt;
112 CLOSE validate_cr_dt;
113 IF l_birth_dt IS NOT NULL AND l_birth_dt > new_references.crime_date THEN
114 FND_MESSAGE.SET_NAME('IGS','IGS_PE_INT_DT_LT_BRDT');
115 IGS_GE_MSG_STACK.ADD;
116 APP_EXCEPTION.RAISE_EXCEPTION;
117 END IF;
118 END IF;
119
120 IF new_references.crime_date > TRUNC(SYSDATE) THEN
121 OPEN mes_token;
122 FETCH mes_token INTO l_meaning;
123 CLOSE mes_token;
124 FND_MESSAGE.SET_NAME('IGS','IGS_AD_DATE_SYSDATE');
125 FND_MESSAGE.SET_TOKEN('NAME',l_meaning);
126 IGS_GE_MSG_STACK.ADD;
127 APP_EXCEPTION.RAISE_EXCEPTION;
128 END IF;
129 END BeforeRowInsertUpdate1;
130
131 PROCEDURE BeforeRowInsertUpdate2(
132 p_inserting IN BOOLEAN,
133 p_updating IN BOOLEAN
134 ) as
135 ----------------------------------------------------------------------------------------------
136 --Created by : skpandey
137 --Date created: 04-JUL-2005
138 --
139 --Purpose: To check the flag status of felony_convicted_flag in igs_pe_hz_parties table and
140 -- convict_ind and display error message according to Business Rule
141 --Known limitations/enhancements and/or remarks:
142 --
143 --Change History:
144 --Who When What
145 --skpandey 15-Jul-2005 Bug : 4327807
146 -- Changed the cursor felony_convicted_flag_cur Open and Close logic
147 ----------------------------------------------------------------------------------------------
148 CURSOR felony_convicted_flag_cur IS
149 SELECT felony_convicted_flag
150 FROM igs_pe_hz_parties
151 WHERE party_id = new_references.person_id;
152
153 x_felony_convicted_flag VARCHAR2(1);
154
155 BEGIN
156
157 OPEN felony_convicted_flag_cur;
158 FETCH felony_convicted_flag_cur INTO x_felony_convicted_flag;
159 CLOSE felony_convicted_flag_cur;
160 IF p_inserting THEN
161 IF x_felony_convicted_flag IS NOT NULL THEN
162 IF new_references.convict_ind = 'Y' AND x_felony_convicted_flag = 'N' THEN
163 fnd_message.set_name('IGS','IGS_PE_SS_NO_CRMNL_CONVICT');
164 igs_ge_msg_stack.add;
165 app_exception.raise_exception;
166 END IF;
167 ELSIF x_felony_convicted_flag IS NULL THEN
168 fnd_message.set_name('IGS','IGS_PE_SS_FLNY_CANT_INSERT');
169 igs_ge_msg_stack.add;
170 app_exception.raise_exception;
171 END IF;
172 ELSIF p_updating THEN
173 IF new_references.convict_ind = 'Y' AND x_felony_convicted_flag <> 'Y' THEN
174 FND_MESSAGE.SET_NAME('IGS','IGS_PE_SS_NO_CRMNL_CONVICT');
175 igs_ge_msg_stack.add;
176 app_exception.raise_exception;
177 END IF;
178 END IF;
179
180 END BeforeRowInsertUpdate2;
181
182
183 PROCEDURE check_uniqueness AS
184 /*
185 || Created By : cdcruz
186 || Created On : 21-SEP-2001
187 || Purpose : Handles the Unique Constraint logic defined for the columns.
188 || Known limitations, enhancements or remarks :
189 || Change History :
190 || Who When What
191 || (reverse chronological order - newest change first)
192 */
193 BEGIN
194
195 IF ( get_uk_for_validation (
196 new_references.person_id,
197 new_references.crime_nature,
198 new_references.crime_date
199 )
200 ) THEN
201 fnd_message.set_name ('IGS', 'IGS_PE_FLNY_DUP_EXISTS');
202 igs_ge_msg_stack.add;
203 app_exception.raise_exception;
204 END IF;
205
206 END check_uniqueness;
207
208
209 PROCEDURE check_parent_existance AS
210 /*
211 || Created By : cdcruz
212 || Created On : 21-SEP-2001
213 || Purpose : Checks for the existance of Parent records.
214 || Known limitations, enhancements or remarks :
215 || Change History :
216 || Who When What
217 || (reverse chronological order - newest change first)
218 */
219 BEGIN
220
221 IF (((old_references.person_id = new_references.person_id)) OR
222 ((new_references.person_id IS NULL))) THEN
223 NULL;
224 ELSIF NOT igs_pe_person_pkg.get_pk_for_validation (
225 new_references.person_id
226 ) THEN
227 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
228 igs_ge_msg_stack.add;
229 app_exception.raise_exception;
230 END IF;
231
232 END check_parent_existance;
233
234
235 FUNCTION get_pk_for_validation (
236 x_felony_details_id IN NUMBER
237 ) RETURN BOOLEAN AS
238 /*
239 || Created By : cdcruz
240 || Created On : 21-SEP-2001
241 || Purpose : Validates the Primary Key of the table.
242 || Known limitations, enhancements or remarks :
243 || Change History :
244 || Who When What
245 || (reverse chronological order - newest change first)
246 */
247 CURSOR cur_rowid IS
248 SELECT rowid
249 FROM igs_pe_felony_dtls
250 WHERE felony_details_id = x_felony_details_id
251 FOR UPDATE NOWAIT;
252
253 lv_rowid cur_rowid%RowType;
254
255 BEGIN
256
257 OPEN cur_rowid;
258 FETCH cur_rowid INTO lv_rowid;
259 IF (cur_rowid%FOUND) THEN
260 CLOSE cur_rowid;
261 RETURN(TRUE);
262 ELSE
263 CLOSE cur_rowid;
264 RETURN(FALSE);
265 END IF;
266
267 END get_pk_for_validation;
268
269
270 FUNCTION get_uk_for_validation (
271 x_person_id IN NUMBER,
272 x_crime_nature IN VARCHAR2,
273 x_crime_date IN DATE
274 ) RETURN BOOLEAN AS
275 /*
276 || Created By : cdcruz
277 || Created On : 21-SEP-2001
278 || Purpose : Validates the Unique Keys of the table.
279 || Known limitations, enhancements or remarks :
280 || Change History :
281 || Who When What
282 || (reverse chronological order - newest change first)
283 */
284 CURSOR cur_rowid IS
285 SELECT rowid
286 FROM igs_pe_felony_dtls
287 WHERE person_id = x_person_id
288 AND crime_nature = x_crime_nature
289 AND crime_date = x_crime_date
290 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
291
292 lv_rowid cur_rowid%RowType;
293
294 BEGIN
295
296 OPEN cur_rowid;
297 FETCH cur_rowid INTO lv_rowid;
298 IF (cur_rowid%FOUND) THEN
299 CLOSE cur_rowid;
300 RETURN (true);
301 ELSE
302 CLOSE cur_rowid;
303 RETURN(FALSE);
304 END IF;
305
306 END get_uk_for_validation ;
307
308
309 PROCEDURE get_fk_hz_parties (
310 x_party_id IN NUMBER
311 ) AS
312 /*
313 || Created By : cdcruz
314 || Created On : 21-SEP-2001
315 || Purpose : Validates the Foreign Keys for the table.
316 || Known limitations, enhancements or remarks :
317 || Change History :
318 || Who When What
319 || (reverse chronological order - newest change first)
320 */
321 CURSOR cur_rowid IS
322 SELECT rowid
323 FROM igs_pe_felony_dtls
324 WHERE ((person_id = x_party_id));
325
326 lv_rowid cur_rowid%RowType;
327
328 BEGIN
329
330 OPEN cur_rowid;
331 FETCH cur_rowid INTO lv_rowid;
332 IF (cur_rowid%FOUND) THEN
333 CLOSE cur_rowid;
334 fnd_message.set_name ('IGS', 'IGS_PE_PEFD_HZ_FK');
335 igs_ge_msg_stack.add;
336 app_exception.raise_exception;
337 RETURN;
338 END IF;
339 CLOSE cur_rowid;
340
341 END get_fk_hz_parties;
342
343
344 PROCEDURE before_dml (
345 p_action IN VARCHAR2 ,
346 x_rowid IN VARCHAR2 ,
347 x_felony_details_id IN NUMBER ,
348 x_person_id IN NUMBER ,
349 x_crime_nature IN VARCHAR2 ,
350 x_crime_date IN DATE ,
351 x_convict_ind IN VARCHAR2 ,
352 x_disp_action_info IN VARCHAR2 ,
353 x_creation_date IN DATE ,
354 x_created_by IN NUMBER ,
355 x_last_update_date IN DATE ,
356 x_last_updated_by IN NUMBER ,
357 x_last_update_login IN NUMBER
358 ) AS
359
360 /*
361 || Created By : cdcruz
362 || Created On : 21-SEP-2001
363 || Purpose : Initialises the columns, Checks Constraints, Calls the
364 || Trigger Handlers for the table, before any DML operation.
365 || Known limitations, enhancements or remarks :
366 || Change History :
367 || Who When What
368 || (reverse chronological order - newest change first)
369 */
370
371 BEGIN
372
373 set_column_values (
374 p_action,
375 x_rowid,
376 x_felony_details_id,
377 x_person_id,
378 x_crime_nature,
379 x_crime_date,
380 x_convict_ind,
381 x_disp_action_info,
382 x_creation_date,
383 x_created_by,
384 x_last_update_date,
385 x_last_updated_by,
386 x_last_update_login
387 );
388
389 IF (p_action = 'INSERT') THEN
390 -- Call all the procedures related to Before Insert.
391 BeforeRowInsertUpdate1( TRUE, FALSE,FALSE );
392 IF ( get_pk_for_validation(
393 new_references.felony_details_id
394 )
395 ) THEN
396 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
397 igs_ge_msg_stack.add;
398 app_exception.raise_exception;
399 END IF;
400 check_uniqueness;
401 check_parent_existance;
402 BeforeRowInsertUpdate2(TRUE, FALSE);
403
404 ELSIF (p_action = 'UPDATE') THEN
405 BeforeRowInsertUpdate1( FALSE,TRUE,FALSE );
406 -- Call all the procedures related to Before Update.
407 check_uniqueness;
408 check_parent_existance;
409 BeforeRowInsertUpdate2(FALSE, TRUE);
410
411 ELSIF (p_action = 'VALIDATE_INSERT') THEN
412 -- Call all the procedures related to Before Insert.
413
414 BeforeRowInsertUpdate1( TRUE, FALSE,FALSE );
415 IF ( get_pk_for_validation (
416 new_references.felony_details_id
417 )
418 ) THEN
419 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
420 igs_ge_msg_stack.add;
421 app_exception.raise_exception;
422 END IF;
423 check_uniqueness;
424 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
425 BeforeRowInsertUpdate1( FALSE,TRUE,FALSE );
426 check_uniqueness;
427 END IF;
428
429 END before_dml;
430
431
432 PROCEDURE insert_row (
433 x_rowid IN OUT NOCOPY VARCHAR2,
434 x_felony_details_id IN OUT NOCOPY NUMBER,
435 x_person_id IN NUMBER,
436 x_crime_nature IN VARCHAR2,
437 x_crime_date IN DATE,
438 x_convict_ind IN VARCHAR2,
439 x_disp_action_info IN VARCHAR2,
440 x_mode IN VARCHAR2
441 ) AS
442 /*
443 || Created By : cdcruz
444 || Created On : 21-SEP-2001
445 || Purpose : Handles the INSERT DML logic 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 c IS
452 SELECT rowid
453 FROM igs_pe_felony_dtls
454 WHERE felony_details_id = x_felony_details_id;
455
456 x_last_update_date DATE;
457 x_last_updated_by NUMBER;
458 x_last_update_login NUMBER;
459
460 BEGIN
461
462 x_last_update_date := SYSDATE;
463 IF (x_mode = 'I') THEN
464 x_last_updated_by := 1;
465 x_last_update_login := 0;
466 ELSIF (X_MODE IN ('R', 'S')) THEN
467 x_last_updated_by := fnd_global.user_id;
468 IF (x_last_updated_by IS NULL) THEN
469 x_last_updated_by := -1;
470 END IF;
471 x_last_update_login := fnd_global.login_id;
472 IF (x_last_update_login IS NULL) THEN
473 x_last_update_login := -1;
474 END IF;
475 ELSE
476 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
477 igs_ge_msg_stack.add;
478 app_exception.raise_exception;
479 END IF;
480
481 SELECT igs_pe_felony_dtls_s.NEXTVAL
482 INTO x_felony_details_id
483 FROM dual;
484
485 before_dml(
486 p_action => 'INSERT',
487 x_rowid => x_rowid,
488 x_felony_details_id => x_felony_details_id,
489 x_person_id => x_person_id,
490 x_crime_nature => x_crime_nature,
491 x_crime_date => x_crime_date,
492 x_convict_ind => x_convict_ind,
493 x_disp_action_info => x_disp_action_info,
494 x_creation_date => x_last_update_date,
495 x_created_by => x_last_updated_by,
496 x_last_update_date => x_last_update_date,
497 x_last_updated_by => x_last_updated_by,
498 x_last_update_login => x_last_update_login
499 );
500
501 IF (x_mode = 'S') THEN
502 igs_sc_gen_001.set_ctx('R');
503 END IF;
504 INSERT INTO igs_pe_felony_dtls (
505 felony_details_id,
506 person_id,
507 crime_nature,
508 crime_date,
509 convict_ind,
510 disp_action_info,
511 creation_date,
512 created_by,
513 last_update_date,
514 last_updated_by,
515 last_update_login
516 ) VALUES (
517 new_references.felony_details_id,
518 new_references.person_id,
519 new_references.crime_nature,
520 new_references.crime_date,
521 new_references.convict_ind,
522 new_references.disp_action_info,
523 x_last_update_date,
524 x_last_updated_by,
525 x_last_update_date,
526 x_last_updated_by,
527 x_last_update_login
528 );
529 IF (x_mode = 'S') THEN
530 igs_sc_gen_001.unset_ctx('R');
531 END IF;
532
533
534 OPEN c;
535 FETCH c INTO x_rowid;
536 IF (c%NOTFOUND) THEN
537 CLOSE c;
538 RAISE NO_DATA_FOUND;
539 END IF;
540 CLOSE c;
541
542
543 EXCEPTION
544 WHEN OTHERS THEN
545 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
546 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
547 fnd_message.set_token ('ERR_CD', SQLCODE);
548 igs_ge_msg_stack.add;
549 igs_sc_gen_001.unset_ctx('R');
550 app_exception.raise_exception;
551 ELSE
552 igs_sc_gen_001.unset_ctx('R');
553 RAISE;
554 END IF;
555 END insert_row;
556
557
558 PROCEDURE lock_row (
559 x_rowid IN VARCHAR2,
560 x_felony_details_id IN NUMBER,
561 x_person_id IN NUMBER,
562 x_crime_nature IN VARCHAR2,
563 x_crime_date IN DATE,
564 x_convict_ind IN VARCHAR2,
565 x_disp_action_info IN VARCHAR2
566
567 ) AS
568 /*
569 || Created By : cdcruz
570 || Created On : 21-SEP-2001
571 || Purpose : Handles the LOCK mechanism for the table.
572 || Known limitations, enhancements or remarks :
573 || Change History :
574 || Who When What
575 || (reverse chronological order - newest change first)
576 */
577 CURSOR c1 IS
578 SELECT
579 person_id,
580 crime_nature,
581 crime_date,
582 convict_ind,
583 disp_action_info
584 FROM igs_pe_felony_dtls
585 WHERE rowid = x_rowid
586 FOR UPDATE NOWAIT;
587
588 tlinfo c1%ROWTYPE;
589
590 BEGIN
591
592 OPEN c1;
593 FETCH c1 INTO tlinfo;
594 IF (c1%notfound) THEN
595 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
596 igs_ge_msg_stack.add;
597 CLOSE c1;
598 app_exception.raise_exception;
599 RETURN;
600 END IF;
601 CLOSE c1;
602
603 IF (
604 (tlinfo.person_id = x_person_id)
605 AND (tlinfo.crime_nature = x_crime_nature)
606 AND (tlinfo.crime_date = x_crime_date)
607 AND (tlinfo.convict_ind = x_convict_ind)
608
609
610 AND ((tlinfo.disp_action_info = x_disp_action_info)
611 OR ((tlinfo.disp_action_info is null)
612 AND (x_disp_action_info is null)))
613
614 ) THEN
615 NULL;
616 ELSE
617 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
618 igs_ge_msg_stack.add;
619 app_exception.raise_exception;
620 END IF;
621
622 RETURN;
623
624 END lock_row;
625
626
627 PROCEDURE update_row (
628 x_rowid IN VARCHAR2,
629 x_felony_details_id IN NUMBER,
630 x_person_id IN NUMBER,
631 x_crime_nature IN VARCHAR2,
632 x_crime_date IN DATE,
633 x_convict_ind IN VARCHAR2,
634 x_disp_action_info IN VARCHAR2,
635 x_mode IN VARCHAR2
636 ) AS
637 /*
638 || Created By : cdcruz
639 || Created On : 21-SEP-2001
640 || Purpose : Handles the UPDATE DML logic for the table.
641 || Known limitations, enhancements or remarks :
642 || Change History :
643 || Who When What
644 || (reverse chronological order - newest change first)
645 */
646 x_last_update_date DATE ;
647 x_last_updated_by NUMBER;
648 x_last_update_login NUMBER;
649
650 BEGIN
651
652 x_last_update_date := SYSDATE;
653 IF (X_MODE = 'I') THEN
654 x_last_updated_by := 1;
655 x_last_update_login := 0;
656 ELSIF (X_MODE IN ('R', 'S')) THEN
657 x_last_updated_by := fnd_global.user_id;
658 IF x_last_updated_by IS NULL THEN
659 x_last_updated_by := -1;
660 END IF;
661 x_last_update_login := fnd_global.login_id;
662 IF (x_last_update_login IS NULL) THEN
663 x_last_update_login := -1;
664 END IF;
665 ELSE
666 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
667 igs_ge_msg_stack.add;
668 app_exception.raise_exception;
669 END IF;
670
671 before_dml(
672 p_action => 'UPDATE',
673 x_rowid => x_rowid,
674 x_felony_details_id => x_felony_details_id,
675 x_person_id => x_person_id,
676 x_crime_nature => x_crime_nature,
677 x_crime_date => x_crime_date,
678 x_convict_ind => x_convict_ind,
679 X_disp_action_info => x_disp_action_info,
680 x_creation_date => x_last_update_date,
681 x_created_by => x_last_updated_by,
682 x_last_update_date => x_last_update_date,
683 x_last_updated_by => x_last_updated_by,
684 x_last_update_login => x_last_update_login
685 );
686
687 IF (x_mode = 'S') THEN
688 igs_sc_gen_001.set_ctx('R');
689 END IF;
690 UPDATE igs_pe_felony_dtls
691 SET
692 person_id = new_references.person_id,
693 crime_nature = new_references.crime_nature,
694 crime_date = new_references.crime_date,
695 convict_ind = new_references.convict_ind,
696 disp_action_info = new_references.disp_action_info,
697 last_update_date = x_last_update_date,
698 last_updated_by = x_last_updated_by,
699 last_update_login = x_last_update_login
700 WHERE rowid = x_rowid;
701
702 IF (SQL%NOTFOUND) THEN
703 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
704 igs_ge_msg_stack.add;
705 igs_sc_gen_001.unset_ctx('R');
706 app_exception.raise_exception;
707 END IF;
708 IF (x_mode = 'S') THEN
709 igs_sc_gen_001.unset_ctx('R');
710 END IF;
711
712
713
714 EXCEPTION
715 WHEN OTHERS THEN
716 IF (SQLCODE = (-28115)) THEN
717 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
718 fnd_message.set_token ('ERR_CD', SQLCODE);
719 igs_ge_msg_stack.add;
720 igs_sc_gen_001.unset_ctx('R');
721 app_exception.raise_exception;
722 ELSE
723 igs_sc_gen_001.unset_ctx('R');
724 RAISE;
725 END IF;
726 END update_row;
727
728
729 PROCEDURE add_row (
730 x_rowid IN OUT NOCOPY VARCHAR2,
731 x_felony_details_id IN OUT NOCOPY NUMBER,
732 x_person_id IN NUMBER,
733 x_crime_nature IN VARCHAR2,
734 x_crime_date IN DATE,
735 x_convict_ind IN VARCHAR2,
736 x_disp_action_info IN VARCHAR2,
737 x_mode IN VARCHAR2
738
739 ) AS
740 /*
741 || Created By : cdcruz
742 || Created On : 21-SEP-2001
743 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
744 || Known limitations, enhancements or remarks :
745 || Change History :
746 || Who When What
747 || (reverse chronological order - newest change first)
748 */
749 CURSOR c1 IS
750 SELECT rowid
751 FROM igs_pe_felony_dtls
752 WHERE felony_details_id = x_felony_details_id;
753
754 BEGIN
755
756 OPEN c1;
757 FETCH c1 INTO x_rowid;
758 IF (c1%NOTFOUND) THEN
759 CLOSE c1;
760
761 insert_row (
762 x_rowid,
763 x_felony_details_id,
764 x_person_id,
765 x_crime_nature,
766 x_crime_date,
767 x_convict_ind,
768 x_disp_action_info,
769 x_mode
770 );
771 RETURN;
772 END IF;
773 CLOSE c1;
774
775 update_row (
776 x_rowid,
777 x_felony_details_id,
778 x_person_id,
779 x_crime_nature,
780 x_crime_date,
781 x_convict_ind,
782 x_disp_action_info,
783 x_mode
784 );
785
786 END add_row;
787
788
789 PROCEDURE delete_row (
790 x_rowid IN VARCHAR2,
791 x_mode IN VARCHAR2
792 ) AS
793 /*
794 || Created By : cdcruz
795 || Created On : 21-SEP-2001
796 || Purpose : Handles the DELETE DML logic for the table.
797 || Known limitations, enhancements or remarks :
798 || Change History :
799 || Who When What
800 || (reverse chronological order - newest change first)
801 */
802 BEGIN
803
804 before_dml (
805 p_action => 'DELETE',
806 x_rowid => x_rowid
807 );
808
809 IF (x_mode = 'S') THEN
810 igs_sc_gen_001.set_ctx('R');
811 END IF;
812 DELETE FROM igs_pe_felony_dtls
813 WHERE rowid = x_rowid;
814
815 IF (SQL%NOTFOUND) THEN
816 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
817 igs_ge_msg_stack.add;
818 igs_sc_gen_001.unset_ctx('R');
819 app_exception.raise_exception;
820 END IF;
821 IF (x_mode = 'S') THEN
822 igs_sc_gen_001.unset_ctx('R');
823 END IF;
824
825
826 END delete_row;
827
828
829 END igs_pe_felony_dtls_pkg;
[Home] [Help]