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