1 PACKAGE BODY igs_uc_app_stats_pkg AS
2 /* $Header: IGSXI07B.pls 115.10 2003/06/11 10:29:43 smaddali noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_uc_app_stats%ROWTYPE;
6 new_references igs_uc_app_stats%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_app_stat_id IN NUMBER ,
12 x_app_id IN NUMBER ,
13 x_app_no IN NUMBER ,
14 x_starh_ethnic IN NUMBER ,
15 x_starh_social_class IN VARCHAR2 ,
16 x_starh_pocc_edu_chg_dt IN DATE ,
17 x_starh_pocc IN VARCHAR2 ,
18 x_starh_pocc_text IN VARCHAR2 ,
19 x_starh_last_edu_inst IN NUMBER ,
20 x_starh_edu_leave_date IN NUMBER ,
21 x_starh_lea IN NUMBER ,
22 x_starx_ethnic IN NUMBER ,
23 x_starx_pocc_edu_chg IN DATE ,
24 x_starx_pocc IN VARCHAR2 ,
25 x_starx_pocc_text IN VARCHAR2 ,
26 x_sent_to_hesa IN VARCHAR2 ,
27 x_creation_date IN DATE ,
28 x_created_by IN NUMBER ,
29 x_last_update_date IN DATE ,
30 x_last_updated_by IN NUMBER ,
31 x_last_update_login IN NUMBER ,
32 -- Added following 3 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
33 x_starh_socio_economic IN NUMBER ,
34 x_starx_socio_economic IN NUMBER ,
35 x_starx_occ_background IN VARCHAR2 ,
36 -- Added following Columns as part of UCFD102Build. Bug NO: 2643048 by bayadav
37 x_ivstarh_dependants IN NUMBER ,
38 x_ivstarh_married IN VARCHAR2 ,
39 x_ivstarx_religion IN NUMBER ,
40 x_ivstarx_dependants IN NUMBER ,
41 x_ivstarx_married IN VARCHAR2
42 ) AS
43 /*
44 || Created By : [email protected]
45 || Created On : 21-FEB-2002
46 || Purpose : Initialises the Old and New references for the columns of the table.
47 || Known limitations, enhancements or remarks :
48 || Change History :
49 || Who When What
50 || smaddali 10-jun-03 obsoleting timestamp columns for ucfd203 - multiple cycles build , bug#2669208 |
51 || (reverse chronological order - newest change first)
52 */
53
54 CURSOR cur_old_ref_values IS
55 SELECT *
56 FROM IGS_UC_APP_STATS
57 WHERE rowid = x_rowid;
58
59 BEGIN
60
61 l_rowid := x_rowid;
62
63 -- Code for setting the Old and New Reference Values.
64 -- Populate Old Values.
65 OPEN cur_old_ref_values;
66 FETCH cur_old_ref_values INTO old_references;
67 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
68 CLOSE cur_old_ref_values;
69 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
70 igs_ge_msg_stack.add;
71 app_exception.raise_exception;
72 RETURN;
73 END IF;
74 CLOSE cur_old_ref_values;
75
76 -- Populate New Values.
77 new_references.app_stat_id := x_app_stat_id;
78 new_references.app_id := x_app_id;
79 new_references.app_no := x_app_no;
80 new_references.starh_ethnic := x_starh_ethnic;
81 new_references.starh_social_class := x_starh_social_class;
82 new_references.starh_pocc_edu_chg_dt := x_starh_pocc_edu_chg_dt;
83 new_references.starh_pocc := x_starh_pocc;
84 new_references.starh_pocc_text := x_starh_pocc_text;
85 new_references.starh_last_edu_inst := x_starh_last_edu_inst;
86 new_references.starh_edu_leave_date := x_starh_edu_leave_date;
87 new_references.starh_lea := x_starh_lea;
88 new_references.starx_ethnic := x_starx_ethnic;
89 new_references.starx_pocc_edu_chg := x_starx_pocc_edu_chg;
90 new_references.starx_pocc := x_starx_pocc;
91 new_references.starx_pocc_text := x_starx_pocc_text;
92 new_references.sent_to_hesa := x_sent_to_hesa;
93 new_references.starh_socio_economic := x_starh_socio_economic;
94 new_references.starx_socio_economic := x_starx_socio_economic;
95 new_references.starx_occ_background := x_starx_occ_background;
96 new_references.ivstarh_dependants := x_ivstarh_dependants;
97 new_references.ivstarh_married := x_ivstarh_married;
98 new_references.ivstarx_religion := x_ivstarx_religion;
99 new_references.ivstarx_dependants := x_ivstarx_dependants;
100 new_references.ivstarx_married := x_ivstarx_married;
101
102
103 IF (p_action = 'UPDATE') THEN
104 new_references.creation_date := old_references.creation_date;
105 new_references.created_by := old_references.created_by;
106 ELSE
107 new_references.creation_date := x_creation_date;
108 new_references.created_by := x_created_by;
109 END IF;
110
111 new_references.last_update_date := x_last_update_date;
112 new_references.last_updated_by := x_last_updated_by;
113 new_references.last_update_login := x_last_update_login;
114
115 END set_column_values;
116
117
118 PROCEDURE check_uniqueness AS
119 /*
120 || Created By : [email protected]
121 || Created On : 21-FEB-2002
122 || Purpose : Handles the Unique Constraint logic defined for the columns.
123 || Known limitations, enhancements or remarks :
124 || Change History :
125 || Who When What
126 || (reverse chronological order - newest change first)
127 */
128 BEGIN
129
130 IF ( get_uk_for_validation (
131 new_references.app_no
132 )
133 ) THEN
134 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
135 igs_ge_msg_stack.add;
136 app_exception.raise_exception;
137 END IF;
138
139 END check_uniqueness;
140
141
142 PROCEDURE check_parent_existance AS
143 /*
144 || Created By : [email protected]
145 || Created On : 21-FEB-2002
146 || Purpose : Checks for the existance of Parent records.
147 || Known limitations, enhancements or remarks :
148 || Change History :
149 || Who When What
150 || (reverse chronological order - newest change first)
151 */
152 BEGIN
153
154 IF (((old_references.app_id = new_references.app_id)) OR
155 ((new_references.app_id IS NULL))) THEN
156 NULL;
157 ELSIF NOT igs_uc_applicants_pkg.get_pk_for_validation (
158 new_references.app_id
159 ) THEN
160 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
161 igs_ge_msg_stack.add;
162 app_exception.raise_exception;
163 END IF;
164
165 END check_parent_existance;
166
167
168 FUNCTION get_pk_for_validation (
169 x_app_stat_id IN NUMBER
170 ) RETURN BOOLEAN AS
171 /*
172 || Created By : [email protected]
173 || Created On : 21-FEB-2002
174 || Purpose : Validates the Primary Key of the table.
175 || Known limitations, enhancements or remarks :
176 || Change History :
177 || Who When What
178 || (reverse chronological order - newest change first)
179 */
180 CURSOR cur_rowid IS
181 SELECT rowid
182 FROM igs_uc_app_stats
183 WHERE app_stat_id = x_app_stat_id ;
184
185 lv_rowid cur_rowid%RowType;
186
187 BEGIN
188
189 OPEN cur_rowid;
190 FETCH cur_rowid INTO lv_rowid;
191 IF (cur_rowid%FOUND) THEN
192 CLOSE cur_rowid;
193 RETURN(TRUE);
194 ELSE
195 CLOSE cur_rowid;
196 RETURN(FALSE);
197 END IF;
198
199 END get_pk_for_validation;
200
201
202 FUNCTION get_uk_for_validation (
203 x_app_no IN NUMBER
204 ) RETURN BOOLEAN AS
205 /*
206 || Created By : [email protected]
207 || Created On : 21-FEB-2002
208 || Purpose : Validates the Unique Keys of the table.
209 || Known limitations, enhancements or remarks :
210 || Change History :
211 || Who When What
212 || (reverse chronological order - newest change first)
213 */
214 CURSOR cur_rowid IS
215 SELECT rowid
216 FROM igs_uc_app_stats
217 WHERE app_no = x_app_no
218 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
219
220 lv_rowid cur_rowid%RowType;
221
222 BEGIN
223
224 OPEN cur_rowid;
225 FETCH cur_rowid INTO lv_rowid;
226 IF (cur_rowid%FOUND) THEN
227 CLOSE cur_rowid;
228 RETURN (true);
229 ELSE
230 CLOSE cur_rowid;
231 RETURN(FALSE);
232 END IF;
233
234 END get_uk_for_validation ;
235
236
237 PROCEDURE get_fk_igs_uc_applicants (
238 x_app_id IN NUMBER
239 ) AS
240 /*
241 || Created By : [email protected]
242 || Created On : 21-FEB-2002
243 || Purpose : Validates the Foreign Keys for the table.
244 || Known limitations, enhancements or remarks :
245 || Change History :
246 || Who When What
247 || (reverse chronological order - newest change first)
248 */
249 CURSOR cur_rowid IS
250 SELECT rowid
251 FROM igs_uc_app_stats
252 WHERE ((app_id = x_app_id));
253
254 lv_rowid cur_rowid%RowType;
255
256 BEGIN
257
258 OPEN cur_rowid;
259 FETCH cur_rowid INTO lv_rowid;
260 IF (cur_rowid%FOUND) THEN
261 CLOSE cur_rowid;
262 fnd_message.set_name ('IGS', 'IGS_UC_UCAPST_UCAP_FK');
263 igs_ge_msg_stack.add;
264 app_exception.raise_exception;
265 RETURN;
266 END IF;
267 CLOSE cur_rowid;
268
269 END get_fk_igs_uc_applicants;
270
271
272 PROCEDURE before_dml (
273 p_action IN VARCHAR2,
274 x_rowid IN VARCHAR2 ,
275 x_app_stat_id IN NUMBER ,
276 x_app_id IN NUMBER ,
277 x_app_no IN NUMBER ,
278 x_starh_ethnic IN NUMBER ,
279 x_starh_social_class IN VARCHAR2 ,
280 x_starh_pocc_edu_chg_dt IN DATE ,
281 x_starh_pocc IN VARCHAR2 ,
282 x_starh_pocc_text IN VARCHAR2 ,
283 x_starh_last_edu_inst IN NUMBER ,
284 x_starh_edu_leave_date IN NUMBER ,
285 x_starh_lea IN NUMBER ,
286 x_starx_ethnic IN NUMBER ,
287 x_starx_pocc_edu_chg IN DATE ,
288 x_starx_pocc IN VARCHAR2 ,
289 x_starx_pocc_text IN VARCHAR2 ,
290 x_sent_to_hesa IN VARCHAR2 ,
291 x_creation_date IN DATE ,
292 x_created_by IN NUMBER ,
293 x_last_update_date IN DATE ,
294 x_last_updated_by IN NUMBER ,
295 x_last_update_login IN NUMBER ,
296 -- Added following 3 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
297 x_starh_socio_economic IN NUMBER ,
298 x_starx_socio_economic IN NUMBER ,
299 x_starx_occ_background IN VARCHAR2 ,
300 -- Added following Columns as part of UCFD102Build. Bug NO: 2643048 by bayadav
301 x_ivstarh_dependants IN NUMBER ,
302 x_ivstarh_married IN VARCHAR2 ,
303 x_ivstarx_religion IN NUMBER ,
304 x_ivstarx_dependants IN NUMBER ,
305 x_ivstarx_married IN VARCHAR2
306 ) AS
307 /*
308 || Created By : [email protected]
309 || Created On : 21-FEB-2002
310 || Purpose : Initialises the columns, Checks Constraints, Calls the
311 || Trigger Handlers for the table, before any DML operation.
312 || Known limitations, enhancements or remarks :
313 || Change History :
314 || Who When What
315 || smaddali 10-jun-03 obsoleting timestamp columns for ucfd203 - multiple cycles build , bug#2669208 |
316 || (reverse chronological order - newest change first)
317 */
318 BEGIN
319
320 set_column_values (
321 p_action,
322 x_rowid,
323 x_app_stat_id,
324 x_app_id,
325 x_app_no,
326 x_starh_ethnic,
327 x_starh_social_class,
328 x_starh_pocc_edu_chg_dt,
329 x_starh_pocc,
330 x_starh_pocc_text,
331 x_starh_last_edu_inst,
332 x_starh_edu_leave_date,
333 x_starh_lea,
334 x_starx_ethnic,
335 x_starx_pocc_edu_chg,
336 x_starx_pocc,
337 x_starx_pocc_text,
338 x_sent_to_hesa,
339 x_creation_date,
340 x_created_by,
341 x_last_update_date,
342 x_last_updated_by,
343 x_last_update_login,
344 x_starh_socio_economic,
345 x_starx_socio_economic,
346 x_starx_occ_background,
347 x_ivstarh_dependants,
348 x_ivstarh_married,
349 x_ivstarx_religion,
350 x_ivstarx_dependants,
351 x_ivstarx_married
352 );
353
354 IF (p_action = 'INSERT') THEN
355 -- Call all the procedures related to Before Insert.
356 IF ( get_pk_for_validation(
357 new_references.app_stat_id
358 )
359 ) THEN
360 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
361 igs_ge_msg_stack.add;
362 app_exception.raise_exception;
363 END IF;
364 check_uniqueness;
365 check_parent_existance;
366 ELSIF (p_action = 'UPDATE') THEN
367 -- Call all the procedures related to Before Update.
368 check_uniqueness;
369 check_parent_existance;
370 ELSIF (p_action = 'VALIDATE_INSERT') THEN
371 -- Call all the procedures related to Before Insert.
372 IF ( get_pk_for_validation (
373 new_references.app_stat_id
374 )
375 ) THEN
376 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
377 igs_ge_msg_stack.add;
378 app_exception.raise_exception;
379 END IF;
380 check_uniqueness;
381 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
382 check_uniqueness;
383 END IF;
384
385 END before_dml;
386
387
388 PROCEDURE insert_row (
389 x_rowid IN OUT NOCOPY VARCHAR2,
390 x_app_stat_id IN OUT NOCOPY NUMBER,
391 x_app_id IN NUMBER,
392 x_app_no IN NUMBER,
393 x_starh_ethnic IN NUMBER,
394 x_starh_social_class IN VARCHAR2,
395 x_starh_pocc_edu_chg_dt IN DATE,
396 x_starh_pocc IN VARCHAR2,
397 x_starh_pocc_text IN VARCHAR2,
398 x_starh_last_edu_inst IN NUMBER,
399 x_starh_edu_leave_date IN NUMBER,
400 x_starh_lea IN NUMBER,
401 x_starx_ethnic IN NUMBER,
402 x_starx_pocc_edu_chg IN DATE,
403 x_starx_pocc IN VARCHAR2,
404 x_starx_pocc_text IN VARCHAR2,
405 x_sent_to_hesa IN VARCHAR2,
406 x_mode IN VARCHAR2 ,
407 -- Added following 3 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
408 x_starh_socio_economic IN NUMBER ,
409 x_starx_socio_economic IN NUMBER ,
410 x_starx_occ_background IN VARCHAR2 ,
411 -- Added following Columns as part of UCFD102Build. Bug NO: 2643048 by bayadav
412 x_ivstarh_dependants IN NUMBER ,
413 x_ivstarh_married IN VARCHAR2 ,
414 x_ivstarx_religion IN NUMBER ,
415 x_ivstarx_dependants IN NUMBER ,
416 x_ivstarx_married IN VARCHAR2
417 ) AS
418 /*
419 || Created By : [email protected]
420 || Created On : 21-FEB-2002
421 || Purpose : Handles the INSERT DML logic for the table.
422 || Known limitations, enhancements or remarks :
423 || Change History :
424 || Who When What
425 || smaddali 10-jun-03 obsoleting timestamp columns for ucfd203 - multiple cycles build , bug#2669208 |
426 || (reverse chronological order - newest change first)
427 */
428 CURSOR c IS
429 SELECT rowid
430 FROM igs_uc_app_stats
431 WHERE app_stat_id = x_app_stat_id;
432
433 x_last_update_date DATE;
434 x_last_updated_by NUMBER;
435 x_last_update_login NUMBER;
436
437 BEGIN
438
439 x_last_update_date := SYSDATE;
440 IF (x_mode = 'I') THEN
441 x_last_updated_by := 1;
442 x_last_update_login := 0;
443 ELSIF (x_mode = 'R') THEN
444 x_last_updated_by := fnd_global.user_id;
445 IF (x_last_updated_by IS NULL) THEN
446 x_last_updated_by := -1;
447 END IF;
448 x_last_update_login := fnd_global.login_id;
449 IF (x_last_update_login IS NULL) THEN
450 x_last_update_login := -1;
451 END IF;
452 ELSE
453 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
454 igs_ge_msg_stack.add;
455 app_exception.raise_exception;
456 END IF;
457
458 SELECT igs_uc_app_stats_s.NEXTVAL
459 INTO x_app_stat_id
460 FROM dual;
461
462 before_dml(
463 p_action => 'INSERT',
464 x_rowid => x_rowid,
465 x_app_stat_id => x_app_stat_id,
466 x_app_id => x_app_id,
467 x_app_no => x_app_no,
468 x_starh_ethnic => x_starh_ethnic,
469 x_starh_social_class => x_starh_social_class,
470 x_starh_pocc_edu_chg_dt => x_starh_pocc_edu_chg_dt,
471 x_starh_pocc => x_starh_pocc,
472 x_starh_pocc_text => x_starh_pocc_text,
473 x_starh_last_edu_inst => x_starh_last_edu_inst,
474 x_starh_edu_leave_date => x_starh_edu_leave_date,
475 x_starh_lea => x_starh_lea,
476 x_starx_ethnic => x_starx_ethnic,
477 x_starx_pocc_edu_chg => x_starx_pocc_edu_chg,
478 x_starx_pocc => x_starx_pocc,
479 x_starx_pocc_text => x_starx_pocc_text,
480 x_sent_to_hesa => x_sent_to_hesa,
481 x_creation_date => x_last_update_date,
482 x_created_by => x_last_updated_by,
483 x_last_update_date => x_last_update_date,
484 x_last_updated_by => x_last_updated_by,
485 x_last_update_login => x_last_update_login,
486 x_starh_socio_economic => x_starh_socio_economic,
487 x_starx_socio_economic => x_starx_socio_economic,
488 x_starx_occ_background => x_starx_occ_background,
489 x_ivstarh_dependants => x_ivstarh_dependants,
490 x_ivstarh_married => x_ivstarh_married,
491 x_ivstarx_religion => x_ivstarx_religion,
492 x_ivstarx_dependants => x_ivstarx_dependants,
493 x_ivstarx_married => x_ivstarx_married
494 );
495
496 INSERT INTO igs_uc_app_stats (
497 app_stat_id,
498 app_id,
499 app_no,
500 starh_ethnic,
501 starh_social_class,
502 starh_pocc_edu_chg_dt,
503 starh_pocc,
504 starh_pocc_text,
505 starh_last_edu_inst,
506 starh_edu_leave_date,
507 starh_lea,
508 starx_ethnic,
509 starx_pocc_edu_chg,
510 starx_pocc,
511 starx_pocc_text,
512 sent_to_hesa,
513 creation_date,
514 created_by,
515 last_update_date,
516 last_updated_by,
517 last_update_login,
518 starh_socio_economic,
519 starx_socio_economic,
520 starx_occ_background,
521 ivstarh_dependants,
522 ivstarh_married,
523 ivstarx_religion,
524 ivstarx_dependants,
525 ivstarx_married
526 ) VALUES (
527 new_references.app_stat_id,
528 new_references.app_id,
529 new_references.app_no,
530 new_references.starh_ethnic,
531 new_references.starh_social_class,
532 new_references.starh_pocc_edu_chg_dt,
533 new_references.starh_pocc,
534 new_references.starh_pocc_text,
535 new_references.starh_last_edu_inst,
536 new_references.starh_edu_leave_date,
537 new_references.starh_lea,
538 new_references.starx_ethnic,
539 new_references.starx_pocc_edu_chg,
540 new_references.starx_pocc,
541 new_references.starx_pocc_text,
542 new_references.sent_to_hesa,
543 x_last_update_date,
544 x_last_updated_by,
545 x_last_update_date,
546 x_last_updated_by,
547 x_last_update_login,
548 new_references.starh_socio_economic,
549 new_references.starx_socio_economic,
550 new_references.starx_occ_background,
551 new_references.ivstarh_dependants,
552 new_references.ivstarh_married,
553 new_references.ivstarx_religion,
554 new_references.ivstarx_dependants,
555 new_references.ivstarx_married
556 );
557
558 OPEN c;
559 FETCH c INTO x_rowid;
560 IF (c%NOTFOUND) THEN
561 CLOSE c;
562 RAISE NO_DATA_FOUND;
563 END IF;
564 CLOSE c;
565
566 END insert_row;
567
568
569 PROCEDURE lock_row (
570 x_rowid IN VARCHAR2,
571 x_app_stat_id IN NUMBER,
572 x_app_id IN NUMBER,
573 x_app_no IN NUMBER,
574 x_starh_ethnic IN NUMBER,
575 x_starh_social_class IN VARCHAR2,
576 x_starh_pocc_edu_chg_dt IN DATE,
577 x_starh_pocc IN VARCHAR2,
578 x_starh_pocc_text IN VARCHAR2,
579 x_starh_last_edu_inst IN NUMBER,
580 x_starh_edu_leave_date IN NUMBER,
581 x_starh_lea IN NUMBER,
582 x_starx_ethnic IN NUMBER,
583 x_starx_pocc_edu_chg IN DATE,
584 x_starx_pocc IN VARCHAR2,
585 x_starx_pocc_text IN VARCHAR2,
586 x_sent_to_hesa IN VARCHAR2,
587 -- Added following 3 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
588 x_starh_socio_economic IN NUMBER ,
589 x_starx_socio_economic IN NUMBER ,
590 x_starx_occ_background IN VARCHAR2 ,
591 -- Added following Columns as part of UCFD102Build. Bug NO: 2643048 by bayadav
592 x_ivstarh_dependants IN NUMBER ,
593 x_ivstarh_married IN VARCHAR2 ,
594 x_ivstarx_religion IN NUMBER ,
595 x_ivstarx_dependants IN NUMBER ,
596 x_ivstarx_married IN VARCHAR2
597 ) AS
598 /*
599 || Created By : [email protected]
600 || Created On : 21-FEB-2002
601 || Purpose : Handles the LOCK mechanism for the table.
602 || Known limitations, enhancements or remarks :
603 || Change History :
604 || Who When What
605 || smaddali 10-jun-03 obsoleting timestamp columns for ucfd203 - multiple cycles build , bug#2669208 |
606 || (reverse chronological order - newest change first)
607 */
608 CURSOR c1 IS
609 SELECT
610 app_id,
611 app_no,
612 starh_ethnic,
613 starh_social_class,
614 starh_pocc_edu_chg_dt,
615 starh_pocc,
616 starh_pocc_text,
617 starh_last_edu_inst,
618 starh_edu_leave_date,
619 starh_lea,
620 starx_ethnic,
621 starx_pocc_edu_chg,
622 starx_pocc,
623 starx_pocc_text,
624 sent_to_hesa,
625 starh_socio_economic,
626 starx_socio_economic,
627 starx_occ_background,
628 ivstarh_dependants,
629 ivstarh_married,
630 ivstarx_religion,
631 ivstarx_dependants,
632 ivstarx_married
633 FROM igs_uc_app_stats
634 WHERE rowid = x_rowid
635 FOR UPDATE NOWAIT;
636
637 tlinfo c1%ROWTYPE;
638
639 BEGIN
640
641 OPEN c1;
642 FETCH c1 INTO tlinfo;
643 IF (c1%notfound) THEN
644 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
645 igs_ge_msg_stack.add;
646 CLOSE c1;
647 app_exception.raise_exception;
648 RETURN;
649 END IF;
650 CLOSE c1;
651
652 IF (
653 (tlinfo.app_id = x_app_id)
654 AND (tlinfo.app_no = x_app_no)
655 AND ((tlinfo.starh_ethnic = x_starh_ethnic) OR ((tlinfo.starh_ethnic IS NULL) AND (X_starh_ethnic IS NULL)))
656 AND ((tlinfo.starh_social_class = x_starh_social_class) OR ((tlinfo.starh_social_class IS NULL) AND (X_starh_social_class IS NULL)))
657 AND ((tlinfo.starh_pocc_edu_chg_dt = x_starh_pocc_edu_chg_dt) OR ((tlinfo.starh_pocc_edu_chg_dt IS NULL) AND (X_starh_pocc_edu_chg_dt IS NULL)))
658 AND ((tlinfo.starh_pocc = x_starh_pocc) OR ((tlinfo.starh_pocc IS NULL) AND (X_starh_pocc IS NULL)))
659 AND ((tlinfo.starh_pocc_text = x_starh_pocc_text) OR ((tlinfo.starh_pocc_text IS NULL) AND (X_starh_pocc_text IS NULL)))
660 AND ((tlinfo.starh_last_edu_inst = x_starh_last_edu_inst) OR ((tlinfo.starh_last_edu_inst IS NULL) AND (X_starh_last_edu_inst IS NULL)))
661 AND ((tlinfo.starh_edu_leave_date = x_starh_edu_leave_date) OR ((tlinfo.starh_edu_leave_date IS NULL) AND (X_starh_edu_leave_date IS NULL)))
662 AND ((tlinfo.starh_lea = x_starh_lea) OR ((tlinfo.starh_lea IS NULL) AND (X_starh_lea IS NULL)))
663 AND ((tlinfo.starx_ethnic = x_starx_ethnic) OR ((tlinfo.starx_ethnic IS NULL) AND (X_starx_ethnic IS NULL)))
664 AND ((tlinfo.starx_pocc_edu_chg = x_starx_pocc_edu_chg) OR ((tlinfo.starx_pocc_edu_chg IS NULL) AND (X_starx_pocc_edu_chg IS NULL)))
665 AND ((tlinfo.starx_pocc = x_starx_pocc) OR ((tlinfo.starx_pocc IS NULL) AND (X_starx_pocc IS NULL)))
666 AND ((tlinfo.starx_pocc_text = x_starx_pocc_text) OR ((tlinfo.starx_pocc_text IS NULL) AND (X_starx_pocc_text IS NULL)))
667 AND (tlinfo.sent_to_hesa = x_sent_to_hesa)
668 AND ((tlinfo.starh_socio_economic = x_starh_socio_economic) OR ((tlinfo.starh_socio_economic IS NULL) AND ( x_starh_socio_economic IS NULL)))
669 AND ((tlinfo.starx_socio_economic = x_starx_socio_economic) OR ((tlinfo.starx_socio_economic IS NULL) AND ( x_starx_socio_economic IS NULL)))
670 AND ((tlinfo.starx_occ_background = x_starx_occ_background) OR ((tlinfo.starx_occ_background IS NULL) AND ( x_starx_occ_background IS NULL)))
671 AND ((tlinfo.ivstarh_dependants = x_ivstarh_dependants) OR ((tlinfo.ivstarh_dependants IS NULL) AND ( x_ivstarh_dependants IS NULL)))
672 AND ((tlinfo.ivstarh_married = x_ivstarh_married) OR ((tlinfo.ivstarh_married IS NULL) AND ( x_ivstarh_married IS NULL)))
673 AND ((tlinfo.ivstarx_religion = x_ivstarx_religion) OR ((tlinfo.ivstarx_religion IS NULL) AND ( x_ivstarx_religion IS NULL)))
674 AND ((tlinfo.ivstarx_dependants = x_ivstarx_dependants) OR ((tlinfo.ivstarx_dependants IS NULL) AND ( x_ivstarx_dependants IS NULL)))
675 AND ((tlinfo.ivstarx_married = x_ivstarx_married) OR ((tlinfo.ivstarx_married IS NULL) AND ( x_ivstarx_married IS NULL)))
676 ) THEN
677 NULL;
678 ELSE
679 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
680 igs_ge_msg_stack.add;
681 app_exception.raise_exception;
682 END IF;
683
684 RETURN;
685
686 END lock_row;
687
688
689 PROCEDURE update_row (
690 x_rowid IN VARCHAR2,
691 x_app_stat_id IN NUMBER,
692 x_app_id IN NUMBER,
693 x_app_no IN NUMBER,
694 x_starh_ethnic IN NUMBER,
695 x_starh_social_class IN VARCHAR2,
696 x_starh_pocc_edu_chg_dt IN DATE,
697 x_starh_pocc IN VARCHAR2,
698 x_starh_pocc_text IN VARCHAR2,
699 x_starh_last_edu_inst IN NUMBER,
700 x_starh_edu_leave_date IN NUMBER,
701 x_starh_lea IN NUMBER,
702 x_starx_ethnic IN NUMBER,
703 x_starx_pocc_edu_chg IN DATE,
704 x_starx_pocc IN VARCHAR2,
705 x_starx_pocc_text IN VARCHAR2,
706 x_sent_to_hesa IN VARCHAR2,
707 x_mode IN VARCHAR2 ,
708 -- Added following 3 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
709 x_starh_socio_economic IN NUMBER ,
710 x_starx_socio_economic IN NUMBER ,
711 x_starx_occ_background IN VARCHAR2 ,
712 -- Added following Columns as part of UCFD102Build. Bug NO: 2643048 by bayadav
713 x_ivstarh_dependants IN NUMBER ,
714 x_ivstarh_married IN VARCHAR2 ,
715 x_ivstarx_religion IN NUMBER ,
716 x_ivstarx_dependants IN NUMBER ,
717 x_ivstarx_married IN VARCHAR2
718 ) AS
719 /*
720 || Created By : [email protected]
721 || Created On : 21-FEB-2002
722 || Purpose : Handles the UPDATE DML logic for the table.
723 || Known limitations, enhancements or remarks :
724 || Change History :
725 || Who When What
726 || smaddali 10-jun-03 obsoleting timestamp columns for ucfd203 - multiple cycles build , bug#2669208 |
727 || (reverse chronological order - newest change first)
728 */
729 x_last_update_date DATE ;
730 x_last_updated_by NUMBER;
731 x_last_update_login NUMBER;
732
733 BEGIN
734
735 x_last_update_date := SYSDATE;
736 IF (X_MODE = 'I') THEN
737 x_last_updated_by := 1;
738 x_last_update_login := 0;
739 ELSIF (x_mode = 'R') THEN
740 x_last_updated_by := fnd_global.user_id;
741 IF x_last_updated_by IS NULL THEN
742 x_last_updated_by := -1;
743 END IF;
744 x_last_update_login := fnd_global.login_id;
745 IF (x_last_update_login IS NULL) THEN
746 x_last_update_login := -1;
747 END IF;
748 ELSE
749 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
750 igs_ge_msg_stack.add;
751 app_exception.raise_exception;
752 END IF;
753
754 before_dml(
755 p_action => 'UPDATE',
756 x_rowid => x_rowid,
757 x_app_stat_id => x_app_stat_id,
758 x_app_id => x_app_id,
759 x_app_no => x_app_no,
760 x_starh_ethnic => x_starh_ethnic,
761 x_starh_social_class => x_starh_social_class,
762 x_starh_pocc_edu_chg_dt => x_starh_pocc_edu_chg_dt,
763 x_starh_pocc => x_starh_pocc,
764 x_starh_pocc_text => x_starh_pocc_text,
765 x_starh_last_edu_inst => x_starh_last_edu_inst,
766 x_starh_edu_leave_date => x_starh_edu_leave_date,
767 x_starh_lea => x_starh_lea,
768 x_starx_ethnic => x_starx_ethnic,
769 x_starx_pocc_edu_chg => x_starx_pocc_edu_chg,
770 x_starx_pocc => x_starx_pocc,
771 x_starx_pocc_text => x_starx_pocc_text,
772 x_sent_to_hesa => x_sent_to_hesa,
773 x_creation_date => x_last_update_date,
774 x_created_by => x_last_updated_by,
775 x_last_update_date => x_last_update_date,
776 x_last_updated_by => x_last_updated_by,
777 x_last_update_login => x_last_update_login,
778 x_starh_socio_economic => x_starh_socio_economic,
779 x_starx_socio_economic => x_starx_socio_economic,
780 x_starx_occ_background => x_starx_occ_background,
781 x_ivstarh_dependants => x_ivstarh_dependants,
782 x_ivstarh_married => x_ivstarh_married,
783 x_ivstarx_religion => x_ivstarx_religion,
784 x_ivstarx_dependants => x_ivstarx_dependants,
785 x_ivstarx_married => x_ivstarx_married
786 );
787
788 UPDATE igs_uc_app_stats
789 SET
790 app_id = new_references.app_id,
791 app_no = new_references.app_no,
792 starh_ethnic = new_references.starh_ethnic,
793 starh_social_class = new_references.starh_social_class,
794 starh_pocc_edu_chg_dt = new_references.starh_pocc_edu_chg_dt,
795 starh_pocc = new_references.starh_pocc,
796 starh_pocc_text = new_references.starh_pocc_text,
797 starh_last_edu_inst = new_references.starh_last_edu_inst,
798 starh_edu_leave_date = new_references.starh_edu_leave_date,
799 starh_lea = new_references.starh_lea,
800 starx_ethnic = new_references.starx_ethnic,
801 starx_pocc_edu_chg = new_references.starx_pocc_edu_chg,
802 starx_pocc = new_references.starx_pocc,
803 starx_pocc_text = new_references.starx_pocc_text,
804 sent_to_hesa = new_references.sent_to_hesa,
805 last_update_date = x_last_update_date,
806 last_updated_by = x_last_updated_by,
807 last_update_login = x_last_update_login,
808 starh_socio_economic = new_references.starh_socio_economic,
809 starx_socio_economic = new_references.starx_socio_economic,
810 starx_occ_background = new_references.starx_occ_background,
811 -- Added following Columns as part of UCFD102Build. Bug NO: 2643048 by bayadav
812 ivstarh_dependants = new_references.ivstarh_dependants,
813 ivstarh_married = new_references.ivstarh_married,
814 ivstarx_religion = new_references.ivstarx_religion,
815 ivstarx_dependants = new_references.ivstarx_dependants,
816 ivstarx_married = new_references.ivstarx_married
817 WHERE rowid = x_rowid;
818
819 IF (SQL%NOTFOUND) THEN
820 RAISE NO_DATA_FOUND;
821 END IF;
822
823 END update_row;
824
825
826 PROCEDURE add_row (
827 x_rowid IN OUT NOCOPY VARCHAR2,
828 x_app_stat_id IN OUT NOCOPY NUMBER,
829 x_app_id IN NUMBER,
830 x_app_no IN NUMBER,
831 x_starh_ethnic IN NUMBER,
832 x_starh_social_class IN VARCHAR2,
833 x_starh_pocc_edu_chg_dt IN DATE,
834 x_starh_pocc IN VARCHAR2,
835 x_starh_pocc_text IN VARCHAR2,
836 x_starh_last_edu_inst IN NUMBER,
837 x_starh_edu_leave_date IN NUMBER,
838 x_starh_lea IN NUMBER,
839 x_starx_ethnic IN NUMBER,
840 x_starx_pocc_edu_chg IN DATE,
841 x_starx_pocc IN VARCHAR2,
842 x_starx_pocc_text IN VARCHAR2,
843 x_sent_to_hesa IN VARCHAR2,
844 x_mode IN VARCHAR2 ,
845 -- Added following 3 Columns as part of UCCR002 Build. Bug NO: 2278817 by rbezawad
846 x_starh_socio_economic IN NUMBER ,
847 x_starx_socio_economic IN NUMBER ,
848 x_starx_occ_background IN VARCHAR2 ,
849 -- Added following Columns as part of UCFD102Build. Bug NO: 2643048 by bayadav
850 x_ivstarh_dependants IN NUMBER ,
851 x_ivstarh_married IN VARCHAR2 ,
852 x_ivstarx_religion IN NUMBER ,
853 x_ivstarx_dependants IN NUMBER ,
854 x_ivstarx_married IN VARCHAR2
855 ) AS
856 /*
857 || Created By : [email protected]
858 || Created On : 21-FEB-2002
859 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
860 || Known limitations, enhancements or remarks :
861 || Change History :
862 || Who When What
863 || smaddali 10-jun-03 obsoleting timestamp columns for ucfd203 - multiple cycles build , bug#2669208 |
864 || (reverse chronological order - newest change first)
865 */
866 CURSOR c1 IS
867 SELECT rowid
868 FROM igs_uc_app_stats
869 WHERE app_stat_id = x_app_stat_id;
870
871 BEGIN
872
873 OPEN c1;
874 FETCH c1 INTO x_rowid;
875 IF (c1%NOTFOUND) THEN
876 CLOSE c1;
877
878 insert_row (
879 x_rowid,
880 x_app_stat_id,
881 x_app_id,
882 x_app_no,
883 x_starh_ethnic,
884 x_starh_social_class,
885 x_starh_pocc_edu_chg_dt,
886 x_starh_pocc,
887 x_starh_pocc_text,
888 x_starh_last_edu_inst,
889 x_starh_edu_leave_date,
890 x_starh_lea,
891 x_starx_ethnic,
892 x_starx_pocc_edu_chg,
893 x_starx_pocc,
894 x_starx_pocc_text,
895 x_sent_to_hesa,
896 x_mode,
897 x_starh_socio_economic,
898 x_starx_socio_economic,
899 x_starx_occ_background,
900 x_ivstarh_dependants,
901 x_ivstarh_married,
902 x_ivstarx_religion,
903 x_ivstarx_dependants,
904 x_ivstarx_married
905 );
906 RETURN;
907 END IF;
908 CLOSE c1;
909
910 update_row (
911 x_rowid,
912 x_app_stat_id,
913 x_app_id,
914 x_app_no,
915 x_starh_ethnic,
916 x_starh_social_class,
917 x_starh_pocc_edu_chg_dt,
918 x_starh_pocc,
919 x_starh_pocc_text,
920 x_starh_last_edu_inst,
921 x_starh_edu_leave_date,
922 x_starh_lea,
923 x_starx_ethnic,
924 x_starx_pocc_edu_chg,
925 x_starx_pocc,
926 x_starx_pocc_text,
927 x_sent_to_hesa,
928 x_mode,
929 x_starh_socio_economic,
930 x_starx_socio_economic,
931 x_starx_occ_background ,
932 x_ivstarh_dependants,
933 x_ivstarh_married,
934 x_ivstarx_religion,
935 x_ivstarx_dependants,
936 x_ivstarx_married
937 );
938
939 END add_row;
940
941
942 PROCEDURE delete_row (
943 x_rowid IN VARCHAR2
944 ) AS
945 /*
946 || Created By : [email protected]
947 || Created On : 21-FEB-2002
948 || Purpose : Handles the DELETE DML logic for the table.
949 || Known limitations, enhancements or remarks :
950 || Change History :
951 || Who When What
952 || (reverse chronological order - newest change first)
953 */
954 BEGIN
955
956 before_dml (
957 p_action => 'DELETE',
958 x_rowid => x_rowid
959 );
960
961 DELETE FROM igs_uc_app_stats
962 WHERE rowid = x_rowid;
963
964 IF (SQL%NOTFOUND) THEN
965 RAISE NO_DATA_FOUND;
966 END IF;
967
968 END delete_row;
969
970
971 END igs_uc_app_stats_pkg;
[Home] [Help]