1 PACKAGE BODY igs_fi_er_ord_dtls_pkg AS
2 /* $Header: IGSSIF4B.pls 120.0 2005/09/09 20:28:48 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_er_ord_dtls%ROWTYPE;
6 new_references igs_fi_er_ord_dtls%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_order_id IN NUMBER,
12 x_elm_rng_order_name IN VARCHAR2,
13 x_order_num IN NUMBER,
14 x_order_attr_value IN VARCHAR2,
15 x_creation_date IN DATE,
16 x_created_by IN NUMBER,
17 x_last_update_date IN DATE,
18 x_last_updated_by IN NUMBER,
19 x_last_update_login IN NUMBER
20 ) AS
21 /*
22 || Created By : [email protected]
23 || Created On : 22-JUN-2005
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 igs_fi_er_ord_dtls
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.order_id := x_order_id;
55 new_references.elm_rng_order_name := x_elm_rng_order_name;
56 new_references.order_num := x_order_num;
57 new_references.order_attr_value := x_order_attr_value;
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 : [email protected]
77 || Created On : 22-JUN-2005
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.order_num,
88 new_references.elm_rng_order_name
89 )
90 ) THEN
91 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
92 igs_ge_msg_stack.add;
93 app_exception.raise_exception;
94 END IF;
95
96 IF ( get_uk2_for_validation (
97 new_references.elm_rng_order_name,
98 new_references.order_attr_value
99 )
100 ) THEN
101 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
102 igs_ge_msg_stack.add;
103 app_exception.raise_exception;
104 END IF;
105
106 END check_uniqueness;
107
108
109 PROCEDURE check_parent_existance AS
110 /*
111 || Created By : [email protected]
112 || Created On : 22-JUN-2005
113 || Purpose : Checks for the existance of Parent records.
114 || Known limitations, enhancements or remarks :
115 || Change History :
116 || Who When What
117 || (reverse chronological order - newest change first)
118 */
119 BEGIN
120
121 IF (((old_references.elm_rng_order_name = new_references.elm_rng_order_name)) OR
122 ((new_references.elm_rng_order_name IS NULL))) THEN
123 NULL;
124 ELSIF NOT igs_fi_elm_rng_ords_pkg.get_pk_for_validation (
125 new_references.elm_rng_order_name
126 ) THEN
127 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
128 igs_ge_msg_stack.add;
129 app_exception.raise_exception;
130 END IF;
131
132 END check_parent_existance;
133
134
135 FUNCTION get_pk_for_validation (
136 x_order_id IN NUMBER
137 ) RETURN BOOLEAN AS
138 /*
139 || Created By : [email protected]
140 || Created On : 22-JUN-2005
141 || Purpose : Validates the Primary Key of the table.
142 || Known limitations, enhancements or remarks :
143 || Change History :
144 || Who When What
145 || (reverse chronological order - newest change first)
146 */
147 CURSOR cur_rowid IS
148 SELECT rowid
149 FROM igs_fi_er_ord_dtls
150 WHERE order_id = x_order_id
151 FOR UPDATE NOWAIT;
152
153 lv_rowid cur_rowid%RowType;
154
155 BEGIN
156
157 OPEN cur_rowid;
158 FETCH cur_rowid INTO lv_rowid;
159 IF (cur_rowid%FOUND) THEN
160 CLOSE cur_rowid;
161 RETURN(TRUE);
162 ELSE
163 CLOSE cur_rowid;
164 RETURN(FALSE);
165 END IF;
166
167 END get_pk_for_validation;
168
169
170 FUNCTION get_uk_for_validation (
171 x_order_num IN NUMBER,
172 x_elm_rng_order_name IN VARCHAR2
173 ) RETURN BOOLEAN AS
174 /*
175 || Created By : [email protected]
176 || Created On : 22-JUN-2005
177 || Purpose : Validates the Unique Keys of the table.
178 || Known limitations, enhancements or remarks :
179 || Change History :
180 || Who When What
181 || (reverse chronological order - newest change first)
182 */
183 CURSOR cur_rowid IS
184 SELECT rowid
185 FROM igs_fi_er_ord_dtls
186 WHERE order_num = x_order_num
187 AND elm_rng_order_name = x_elm_rng_order_name
188 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
189
190 lv_rowid cur_rowid%RowType;
191
192 BEGIN
193
194 OPEN cur_rowid;
195 FETCH cur_rowid INTO lv_rowid;
196 IF (cur_rowid%FOUND) THEN
197 CLOSE cur_rowid;
198 RETURN (true);
199 ELSE
200 CLOSE cur_rowid;
201 RETURN(FALSE);
202 END IF;
203
204 END get_uk_for_validation ;
205
206
207 FUNCTION get_uk2_for_validation (
208 x_elm_rng_order_name IN VARCHAR2,
209 x_order_attr_value IN VARCHAR2
210 ) RETURN BOOLEAN AS
211 /*
212 || Created By : [email protected]
213 || Created On : 22-JUN-2005
214 || Purpose : Validates the Unique Keys of the table.
215 || Known limitations, enhancements or remarks :
216 || Change History :
217 || Who When What
218 || (reverse chronological order - newest change first)
219 */
220 CURSOR cur_rowid IS
221 SELECT rowid
222 FROM igs_fi_er_ord_dtls
223 WHERE elm_rng_order_name = x_elm_rng_order_name
224 AND order_attr_value = x_order_attr_value
225 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
226
227 lv_rowid cur_rowid%RowType;
228
229 BEGIN
230
231 OPEN cur_rowid;
232 FETCH cur_rowid INTO lv_rowid;
233 IF (cur_rowid%FOUND) THEN
234 CLOSE cur_rowid;
235 RETURN (true);
236 ELSE
237 CLOSE cur_rowid;
238 RETURN(FALSE);
239 END IF;
240
241 END get_uk2_for_validation ;
242
243
244 PROCEDURE get_fk_igs_fi_elm_rng_ords (
245 x_elm_rng_order_name IN VARCHAR2
246 ) AS
247 /*
248 || Created By : [email protected]
249 || Created On : 22-JUN-2005
250 || Purpose : Validates the Foreign Keys for the table.
251 || Known limitations, enhancements or remarks :
252 || Change History :
253 || Who When What
254 || (reverse chronological order - newest change first)
255 */
256 CURSOR cur_rowid IS
257 SELECT rowid
258 FROM igs_fi_er_ord_dtls
259 WHERE ((elm_rng_order_name = x_elm_rng_order_name));
260
261 lv_rowid cur_rowid%RowType;
262
263 BEGIN
264
265 OPEN cur_rowid;
266 FETCH cur_rowid INTO lv_rowid;
267 IF (cur_rowid%FOUND) THEN
268 CLOSE cur_rowid;
269 fnd_message.set_name ('IGS', 'IGS_FI_FERD_FERO_FK');
270 igs_ge_msg_stack.add;
271 app_exception.raise_exception;
272 RETURN;
273 END IF;
274 CLOSE cur_rowid;
275
276 END get_fk_igs_fi_elm_rng_ords;
277
278
279 PROCEDURE before_dml (
280 p_action IN VARCHAR2,
281 x_rowid IN VARCHAR2,
282 x_order_id IN NUMBER,
283 x_elm_rng_order_name IN VARCHAR2,
284 x_order_num IN NUMBER,
285 x_order_attr_value IN VARCHAR2,
286 x_creation_date IN DATE,
287 x_created_by IN NUMBER,
288 x_last_update_date IN DATE,
289 x_last_updated_by IN NUMBER,
290 x_last_update_login IN NUMBER
291 ) AS
292 /*
293 || Created By : [email protected]
294 || Created On : 22-JUN-2005
295 || Purpose : Initialises the columns, Checks Constraints, Calls the
296 || Trigger Handlers for the table, before any DML operation.
297 || Known limitations, enhancements or remarks :
298 || Change History :
299 || Who When What
300 || (reverse chronological order - newest change first)
301 */
302 BEGIN
303
304 set_column_values (
305 p_action,
306 x_rowid,
307 x_order_id,
308 x_elm_rng_order_name,
309 x_order_num,
310 x_order_attr_value,
311 x_creation_date,
312 x_created_by,
313 x_last_update_date,
314 x_last_updated_by,
315 x_last_update_login
316 );
317
318 IF (p_action = 'INSERT') THEN
319 -- Call all the procedures related to Before Insert.
320 IF ( get_pk_for_validation(
321 new_references.order_id
322 )
323 ) THEN
324 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
325 igs_ge_msg_stack.add;
326 app_exception.raise_exception;
327 END IF;
328 check_uniqueness;
329 check_parent_existance;
330 ELSIF (p_action = 'UPDATE') THEN
331 -- Call all the procedures related to Before Update.
332 check_uniqueness;
333 check_parent_existance;
334 ELSIF (p_action = 'VALIDATE_INSERT') THEN
335 -- Call all the procedures related to Before Insert.
336 IF ( get_pk_for_validation (
337 new_references.order_id
338 )
339 ) THEN
340 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
341 igs_ge_msg_stack.add;
342 app_exception.raise_exception;
343 END IF;
344 check_uniqueness;
345 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
346 check_uniqueness;
347 END IF;
348
349 END before_dml;
350
351
352 PROCEDURE insert_row (
353 x_rowid IN OUT NOCOPY VARCHAR2,
354 x_order_id IN OUT NOCOPY NUMBER,
355 x_elm_rng_order_name IN VARCHAR2,
356 x_order_num IN NUMBER,
357 x_order_attr_value IN VARCHAR2,
358 x_mode IN VARCHAR2
359 ) AS
360 /*
361 || Created By : [email protected]
362 || Created On : 22-JUN-2005
363 || Purpose : Handles the INSERT DML logic for the table.
364 || Known limitations, enhancements or remarks :
365 || Change History :
366 || Who When What
367 || (reverse chronological order - newest change first)
368 */
369
370 x_last_update_date DATE;
371 x_last_updated_by NUMBER;
372 x_last_update_login NUMBER;
373
374 BEGIN
375
376 x_last_update_date := SYSDATE;
377 IF (x_mode = 'I') THEN
378 x_last_updated_by := 1;
379 x_last_update_login := 0;
380 ELSIF (x_mode = 'R') THEN
381 x_last_updated_by := fnd_global.user_id;
382 IF (x_last_updated_by IS NULL) THEN
383 x_last_updated_by := -1;
384 END IF;
385 x_last_update_login := fnd_global.login_id;
386 IF (x_last_update_login IS NULL) THEN
387 x_last_update_login := -1;
388 END IF;
389 ELSE
390 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
391 fnd_message.set_token ('ROUTINE', 'IGS_FI_ER_ORD_DTLS_PKG.INSERT_ROW');
392 igs_ge_msg_stack.add;
393 app_exception.raise_exception;
394 END IF;
395
396 x_order_id := NULL;
397
398 before_dml(
399 p_action => 'INSERT',
400 x_rowid => x_rowid,
401 x_order_id => x_order_id,
402 x_elm_rng_order_name => x_elm_rng_order_name,
403 x_order_num => x_order_num,
404 x_order_attr_value => x_order_attr_value,
405 x_creation_date => x_last_update_date,
406 x_created_by => x_last_updated_by,
407 x_last_update_date => x_last_update_date,
408 x_last_updated_by => x_last_updated_by,
409 x_last_update_login => x_last_update_login
410 );
411
412 INSERT INTO igs_fi_er_ord_dtls (
413 order_id,
414 elm_rng_order_name,
415 order_num,
416 order_attr_value,
417 creation_date,
418 created_by,
419 last_update_date,
420 last_updated_by,
421 last_update_login
422 ) VALUES (
423 igs_fi_er_ord_dtls_s.NEXTVAL,
424 new_references.elm_rng_order_name,
425 new_references.order_num,
426 new_references.order_attr_value,
427 x_last_update_date,
428 x_last_updated_by,
429 x_last_update_date,
430 x_last_updated_by,
431 x_last_update_login
432 ) RETURNING ROWID, order_id INTO x_rowid, x_order_id;
433
434 END insert_row;
435
436
437 PROCEDURE lock_row (
438 x_rowid IN VARCHAR2,
439 x_order_id IN NUMBER,
440 x_elm_rng_order_name IN VARCHAR2,
444 /*
441 x_order_num IN NUMBER,
442 x_order_attr_value IN VARCHAR2
443 ) AS
445 || Created By : [email protected]
446 || Created On : 22-JUN-2005
447 || Purpose : Handles the LOCK mechanism for the table.
448 || Known limitations, enhancements or remarks :
449 || Change History :
450 || Who When What
451 || (reverse chronological order - newest change first)
452 */
453 CURSOR c1 IS
454 SELECT
455 elm_rng_order_name,
456 order_num,
457 order_attr_value
458 FROM igs_fi_er_ord_dtls
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.elm_rng_order_name = x_elm_rng_order_name)
479 AND (tlinfo.order_num = x_order_num)
480 AND (tlinfo.order_attr_value = x_order_attr_value)
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_order_id IN NUMBER,
497 x_elm_rng_order_name IN VARCHAR2,
498 x_order_num IN NUMBER,
499 x_order_attr_value IN VARCHAR2,
500 x_mode IN VARCHAR2
501 ) AS
502 /*
503 || Created By : [email protected]
504 || Created On : 22-JUN-2005
505 || Purpose : Handles the UPDATE DML logic for the table.
506 || Known limitations, enhancements or remarks :
507 || Change History :
508 || Who When What
509 || (reverse chronological order - newest change first)
510 */
511 x_last_update_date DATE ;
512 x_last_updated_by NUMBER;
513 x_last_update_login NUMBER;
514
515 BEGIN
516
517 x_last_update_date := SYSDATE;
518 IF (X_MODE = 'I') THEN
519 x_last_updated_by := 1;
520 x_last_update_login := 0;
521 ELSIF (x_mode = 'R') THEN
522 x_last_updated_by := fnd_global.user_id;
523 IF x_last_updated_by IS NULL THEN
524 x_last_updated_by := -1;
525 END IF;
526 x_last_update_login := fnd_global.login_id;
527 IF (x_last_update_login IS NULL) THEN
528 x_last_update_login := -1;
529 END IF;
530 ELSE
531 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
532 fnd_message.set_token ('ROUTINE', 'IGS_FI_ER_ORD_DTLS_PKG.UPDATE_ROW');
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_order_id => x_order_id,
541 x_elm_rng_order_name => x_elm_rng_order_name,
542 x_order_num => x_order_num,
543 x_order_attr_value => x_order_attr_value,
544 x_creation_date => x_last_update_date,
545 x_created_by => x_last_updated_by,
546 x_last_update_date => x_last_update_date,
547 x_last_updated_by => x_last_updated_by,
548 x_last_update_login => x_last_update_login
549 );
550
551 UPDATE igs_fi_er_ord_dtls
552 SET
553 elm_rng_order_name = new_references.elm_rng_order_name,
554 order_num = new_references.order_num,
555 order_attr_value = new_references.order_attr_value,
556 last_update_date = x_last_update_date,
557 last_updated_by = x_last_updated_by,
558 last_update_login = x_last_update_login
559 WHERE rowid = x_rowid;
560
561 IF (SQL%NOTFOUND) THEN
562 RAISE NO_DATA_FOUND;
563 END IF;
564
565 END update_row;
566
567
568 PROCEDURE add_row (
569 x_rowid IN OUT NOCOPY VARCHAR2,
570 x_order_id IN OUT NOCOPY NUMBER,
571 x_elm_rng_order_name IN VARCHAR2,
572 x_order_num IN NUMBER,
573 x_order_attr_value IN VARCHAR2,
574 x_mode IN VARCHAR2
575 ) AS
576 /*
577 || Created By : [email protected]
578 || Created On : 22-JUN-2005
579 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
580 || Known limitations, enhancements or remarks :
581 || Change History :
582 || Who When What
583 || (reverse chronological order - newest change first)
584 */
585 CURSOR c1 IS
586 SELECT rowid
587 FROM igs_fi_er_ord_dtls
588 WHERE order_id = x_order_id;
589
590 BEGIN
591
592 OPEN c1;
593 FETCH c1 INTO x_rowid;
594 IF (c1%NOTFOUND) THEN
595 CLOSE c1;
596
597 insert_row (
598 x_rowid,
599 x_order_id,
600 x_elm_rng_order_name,
601 x_order_num,
602 x_order_attr_value,
603 x_mode
604 );
605 RETURN;
606 END IF;
607 CLOSE c1;
608
609 update_row (
610 x_rowid,
611 x_order_id,
612 x_elm_rng_order_name,
613 x_order_num,
614 x_order_attr_value,
615 x_mode
616 );
617
618 END add_row;
619
620
621 PROCEDURE delete_row (
622 x_rowid IN VARCHAR2
623 ) AS
624 /*
625 || Created By : [email protected]
626 || Created On : 22-JUN-2005
627 || Purpose : Handles the DELETE DML logic for the table.
628 || Known limitations, enhancements or remarks :
629 || Change History :
630 || Who When What
631 || (reverse chronological order - newest change first)
632 */
633 BEGIN
634
635 before_dml (
636 p_action => 'DELETE',
637 x_rowid => x_rowid
638 );
639
640 DELETE FROM igs_fi_er_ord_dtls
641 WHERE rowid = x_rowid;
642
643 IF (SQL%NOTFOUND) THEN
644 RAISE NO_DATA_FOUND;
645 END IF;
646
647 END delete_row;
648
649
650 END igs_fi_er_ord_dtls_pkg;
[Home] [Help]