1 PACKAGE BODY igs_en_usec_disc_dl_pkg AS
2 /* $Header: IGSEI49B.pls 115.7 2003/03/21 08:10:24 sarakshi ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_usec_disc_dl%ROWTYPE;
6 new_references igs_en_usec_disc_dl%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_usec_disc_dl_id IN NUMBER DEFAULT NULL,
12 x_non_std_disc_dl_stp_id IN NUMBER DEFAULT NULL,
13 x_administrative_unit_status IN VARCHAR2 DEFAULT NULL,
14 x_definition_code IN VARCHAR2 DEFAULT NULL,
15 x_org_unit_code IN VARCHAR2 DEFAULT NULL,
16 x_formula_method IN VARCHAR2 DEFAULT NULL,
17 x_round_method IN VARCHAR2 DEFAULT NULL,
18 x_offset_dt_code IN VARCHAR2 DEFAULT NULL,
19 x_offset_duration IN NUMBER DEFAULT NULL,
20 x_uoo_id IN NUMBER DEFAULT NULL,
21 x_usec_disc_dl_date IN DATE DEFAULT NULL,
22 x_usec_disc_total_days IN NUMBER DEFAULT NULL,
23 x_usec_disc_offset_days IN NUMBER DEFAULT NULL,
24 x_creation_date IN DATE DEFAULT NULL,
25 x_created_by IN NUMBER DEFAULT NULL,
26 x_last_update_date IN DATE DEFAULT NULL,
27 x_last_updated_by IN NUMBER DEFAULT NULL,
28 x_last_update_login IN NUMBER DEFAULT NULL
29 ) AS
30 /*
31 || Created By : [email protected]
32 || Created On : 30-MAR-2001
33 || Purpose : Initialises the Old and New references for the columns of the table.
34 || Known limitations, enhancements or remarks :
35 || Change History :
36 || Who When What
37 || (reverse chronological order - newest change first)
38 */
39
40 CURSOR cur_old_ref_values IS
41 SELECT *
42 FROM IGS_EN_USEC_DISC_DL
43 WHERE rowid = x_rowid;
44
45 BEGIN
46
47 l_rowid := x_rowid;
48
49 -- Code for setting the Old and New Reference Values.
50 -- Populate Old Values.
51 OPEN cur_old_ref_values;
52 FETCH cur_old_ref_values INTO old_references;
53 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
54 CLOSE cur_old_ref_values;
55 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
56 igs_ge_msg_stack.add;
57 app_exception.raise_exception;
58 RETURN;
59 END IF;
60 CLOSE cur_old_ref_values;
61
62 -- Populate New Values.
63 new_references.usec_disc_dl_id := x_usec_disc_dl_id;
64 new_references.non_std_disc_dl_stp_id := x_non_std_disc_dl_stp_id;
65 new_references.administrative_unit_status := x_administrative_unit_status;
66 new_references.definition_code := x_definition_code;
67 new_references.org_unit_code := x_org_unit_code;
68 new_references.formula_method := x_formula_method;
69 new_references.round_method := x_round_method;
70 new_references.offset_dt_code := x_offset_dt_code;
71 new_references.offset_duration := x_offset_duration;
72 new_references.uoo_id := x_uoo_id;
73 new_references.usec_disc_dl_date := x_usec_disc_dl_date;
74 new_references.usec_disc_total_days := x_usec_disc_total_days;
75 new_references.usec_disc_offset_days := x_usec_disc_offset_days;
76
77 IF (p_action = 'UPDATE') THEN
78 new_references.creation_date := old_references.creation_date;
79 new_references.created_by := old_references.created_by;
80 ELSE
81 new_references.creation_date := x_creation_date;
82 new_references.created_by := x_created_by;
83 END IF;
84
85 new_references.last_update_date := x_last_update_date;
86 new_references.last_updated_by := x_last_updated_by;
87 new_references.last_update_login := x_last_update_login;
88
89 END set_column_values;
90
91
92 PROCEDURE check_uniqueness AS
93 /*
94 || Created By : [email protected]
95 || Created On : 30-MAR-2001
96 || Purpose : Handles the Unique Constraint logic defined for the columns.
97 || Known limitations, enhancements or remarks :
98 || Change History :
99 || Who When What
100 || (reverse chronological order - newest change first)
101 */
102 BEGIN
103
104 IF ( get_uk_for_validation (
105 new_references.administrative_unit_status,
106 new_references.uoo_id
107 )
108 ) THEN
109 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
110 igs_ge_msg_stack.add;
111 app_exception.raise_exception;
112 END IF;
113
114 END check_uniqueness;
115
116
117 PROCEDURE check_parent_existance AS
118 /*
119 || Created By : [email protected]
120 || Created On : 30-MAR-2001
121 || Purpose : Checks for the existance of Parent records.
122 || Known limitations, enhancements or remarks :
123 || Change History :
124 || Who When What
125 || (reverse chronological order - newest change first)
126 */
127 BEGIN
128
129 IF (((old_references.non_std_disc_dl_stp_id = new_references.non_std_disc_dl_stp_id)) OR
130 ((new_references.non_std_disc_dl_stp_id IS NULL))) THEN
131 NULL;
132 ELSIF NOT igs_en_nsd_dlstp_pkg.get_pk_for_validation (
133 new_references.non_std_disc_dl_stp_id
134 ) THEN
135 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
136 igs_ge_msg_stack.add;
137 app_exception.raise_exception;
138 END IF;
139
140 IF (((old_references.uoo_id = new_references.uoo_id)) OR
141 ((new_references.uoo_id IS NULL))) THEN
142 NULL;
143 ELSIF NOT igs_ps_unit_ofr_opt_pkg.get_uk_For_validation (
144 new_references.uoo_id
145 ) THEN
146 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
147 igs_ge_msg_stack.add;
148 app_exception.raise_exception;
149 END IF;
150
151 END check_parent_existance;
152
153
154 FUNCTION get_pk_for_validation (
155 x_usec_disc_dl_id IN NUMBER
156 ) RETURN BOOLEAN AS
157 /*
158 || Created By : [email protected]
159 || Created On : 30-MAR-2001
160 || Purpose : Validates the Primary Key of the table.
161 || Known limitations, enhancements or remarks :
162 || Change History :
163 || Who When What
164 || (reverse chronological order - newest change first)
165 */
166 CURSOR cur_rowid IS
167 SELECT rowid
168 FROM igs_en_usec_disc_dl
169 WHERE usec_disc_dl_id = x_usec_disc_dl_id
170 FOR UPDATE NOWAIT;
171
172 lv_rowid cur_rowid%RowType;
173
174 BEGIN
175
176 OPEN cur_rowid;
177 FETCH cur_rowid INTO lv_rowid;
178 IF (cur_rowid%FOUND) THEN
179 CLOSE cur_rowid;
180 RETURN(TRUE);
181 ELSE
182 CLOSE cur_rowid;
183 RETURN(FALSE);
184 END IF;
185
186 END get_pk_for_validation;
187
188
189 FUNCTION get_uk_for_validation (
190 x_administrative_unit_status IN VARCHAR2,
191 x_uoo_id IN NUMBER
192 ) RETURN BOOLEAN AS
193 /*
194 || Created By : [email protected]
195 || Created On : 30-MAR-2001
196 || Purpose : Validates the Unique Keys of the table.
197 || Known limitations, enhancements or remarks :
198 || Change History :
199 || Who When What
200 || (reverse chronological order - newest change first)
201 */
202 CURSOR cur_rowid IS
203 SELECT rowid
204 FROM igs_en_usec_disc_dl
205 WHERE ((administrative_unit_status = x_administrative_unit_status) OR (administrative_unit_status IS NULL AND x_administrative_unit_status IS NULL))
206 AND uoo_id = x_uoo_id
207 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
208
209 lv_rowid cur_rowid%RowType;
210
211 BEGIN
212
213 OPEN cur_rowid;
214 FETCH cur_rowid INTO lv_rowid;
215 IF (cur_rowid%FOUND) THEN
216 CLOSE cur_rowid;
217 RETURN (true);
218 ELSE
219 CLOSE cur_rowid;
220 RETURN(FALSE);
221 END IF;
222
223 END get_uk_for_validation ;
224
225
226 PROCEDURE get_fk_igs_en_nsd_dlstp_all (
227 x_non_std_disc_dl_stp_id IN NUMBER
228 ) AS
229 /*
230 || Created By : [email protected]
231 || Created On : 30-MAR-2001
232 || Purpose : Validates the Foreign Keys for the table.
233 || Known limitations, enhancements or remarks :
234 || Change History :
235 || Who When What
236 || (reverse chronological order - newest change first)
237 */
238 CURSOR cur_rowid IS
239 SELECT rowid
240 FROM igs_en_usec_disc_dl
241 WHERE ((non_std_disc_dl_stp_id = x_non_std_disc_dl_stp_id));
242
243 lv_rowid cur_rowid%RowType;
244
245 BEGIN
246
247 OPEN cur_rowid;
248 FETCH cur_rowid INTO lv_rowid;
249 IF (cur_rowid%FOUND) THEN
250 CLOSE cur_rowid;
251 fnd_message.set_name ('IGS', 'IGS_EN_UDD_NDDS_FK');
252 igs_ge_msg_stack.add;
253 app_exception.raise_exception;
254 RETURN;
255 END IF;
256 CLOSE cur_rowid;
257
258 END get_fk_igs_en_nsd_dlstp_all;
259
260
261 PROCEDURE get_ufk_igs_ps_unit_ofr_opt (
262 x_uoo_id IN NUMBER
263 ) AS
264 /*
265 || Created By : [email protected]
266 || Created On : 30-MAR-2001
267 || Purpose : Validates the Foreign Keys for the table.
268 || Known limitations, enhancements or remarks :
269 || Change History :
270 || Who When What
271 || (reverse chronological order - newest change first)
272 */
273 CURSOR cur_rowid IS
274 SELECT rowid
275 FROM igs_en_usec_disc_dl
276 WHERE ((uoo_id = x_uoo_id));
277
278 lv_rowid cur_rowid%RowType;
279
280 BEGIN
281
282 OPEN cur_rowid;
283 FETCH cur_rowid INTO lv_rowid;
284 IF (cur_rowid%FOUND) THEN
285 CLOSE cur_rowid;
286 fnd_message.set_name ('IGS', 'IGS_EN_UDD_UOO_UFK');
287 igs_ge_msg_stack.add;
288 app_exception.raise_exception;
289 RETURN;
290 END IF;
291 CLOSE cur_rowid;
292
293 END get_ufk_igs_ps_unit_ofr_opt;
294
295
296 PROCEDURE before_dml (
297 p_action IN VARCHAR2,
298 x_rowid IN VARCHAR2 DEFAULT NULL,
299 x_usec_disc_dl_id IN NUMBER DEFAULT NULL,
300 x_non_std_disc_dl_stp_id IN NUMBER DEFAULT NULL,
301 x_administrative_unit_status IN VARCHAR2 DEFAULT NULL,
302 x_definition_code IN VARCHAR2 DEFAULT NULL,
303 x_org_unit_code IN VARCHAR2 DEFAULT NULL,
304 x_formula_method IN VARCHAR2 DEFAULT NULL,
305 x_round_method IN VARCHAR2 DEFAULT NULL,
306 x_offset_dt_code IN VARCHAR2 DEFAULT NULL,
307 x_offset_duration IN NUMBER DEFAULT NULL,
308 x_uoo_id IN NUMBER DEFAULT NULL,
309 x_usec_disc_dl_date IN DATE DEFAULT NULL,
310 x_usec_disc_total_days IN NUMBER DEFAULT NULL,
311 x_usec_disc_offset_days IN NUMBER DEFAULT NULL,
312 x_creation_date IN DATE DEFAULT NULL,
313 x_created_by IN NUMBER DEFAULT NULL,
314 x_last_update_date IN DATE DEFAULT NULL,
315 x_last_updated_by IN NUMBER DEFAULT NULL,
316 x_last_update_login IN NUMBER DEFAULT NULL
317 ) AS
318 /*
319 || Created By : [email protected]
320 || Created On : 30-MAR-2001
321 || Purpose : Initialises the columns, Checks Constraints, Calls the
322 || Trigger Handlers for the table, before any DML operation.
323 || Known limitations, enhancements or remarks :
324 || Change History :
325 || Who When What
326 || (reverse chronological order - newest change first)
327 */
328 BEGIN
329
330 set_column_values (
331 p_action,
332 x_rowid,
333 x_usec_disc_dl_id,
334 x_non_std_disc_dl_stp_id,
335 x_administrative_unit_status,
336 x_definition_code,
337 x_org_unit_code,
338 x_formula_method,
339 x_round_method,
340 x_offset_dt_code,
341 x_offset_duration,
342 x_uoo_id,
343 x_usec_disc_dl_date,
347 x_created_by,
344 x_usec_disc_total_days,
345 x_usec_disc_offset_days,
346 x_creation_date,
348 x_last_update_date,
349 x_last_updated_by,
350 x_last_update_login
351 );
352
353 IF (p_action = 'INSERT') THEN
354 -- Call all the procedures related to Before Insert.
355 IF ( get_pk_for_validation(
356 new_references.usec_disc_dl_id
357 )
358 ) THEN
359 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
360 igs_ge_msg_stack.add;
361 app_exception.raise_exception;
362 END IF;
363 check_uniqueness;
364 check_parent_existance;
365 ELSIF (p_action = 'UPDATE') THEN
366 -- Call all the procedures related to Before Update.
367 check_uniqueness;
368 check_parent_existance;
369 ELSIF (p_action = 'VALIDATE_INSERT') THEN
370 -- Call all the procedures related to Before Insert.
371 IF ( get_pk_for_validation (
372 new_references.usec_disc_dl_id
373 )
374 ) THEN
375 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
376 igs_ge_msg_stack.add;
377 app_exception.raise_exception;
378 END IF;
379 check_uniqueness;
380 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
381 check_uniqueness;
382 END IF;
383
384 l_rowid:=NULL;
385
386 END before_dml;
387
388
389 PROCEDURE insert_row (
390 x_rowid IN OUT NOCOPY VARCHAR2,
391 x_usec_disc_dl_id IN OUT NOCOPY NUMBER,
392 x_non_std_disc_dl_stp_id IN NUMBER,
393 x_administrative_unit_status IN VARCHAR2,
394 x_definition_code IN VARCHAR2,
395 x_org_unit_code IN VARCHAR2,
396 x_formula_method IN VARCHAR2,
397 x_round_method IN VARCHAR2,
398 x_offset_dt_code IN VARCHAR2,
399 x_offset_duration IN NUMBER,
400 x_uoo_id IN NUMBER,
401 x_usec_disc_dl_date IN DATE,
402 x_usec_disc_total_days IN NUMBER,
403 x_usec_disc_offset_days IN NUMBER,
404 x_mode IN VARCHAR2 DEFAULT 'R'
405 ) AS
406 /*
407 || Created By : [email protected]
408 || Created On : 30-MAR-2001
409 || Purpose : Handles the INSERT DML logic for the table.
410 || Known limitations, enhancements or remarks :
411 || Change History :
412 || Who When What
413 || (reverse chronological order - newest change first)
414 */
415 CURSOR c IS
416 SELECT rowid
417 FROM igs_en_usec_disc_dl
418 WHERE usec_disc_dl_id = x_usec_disc_dl_id;
419
420 x_last_update_date DATE;
421 x_last_updated_by NUMBER;
422 x_last_update_login NUMBER;
423
424 BEGIN
425
426 x_last_update_date := SYSDATE;
427 IF (x_mode = 'I') THEN
428 x_last_updated_by := 1;
429 x_last_update_login := 0;
430 ELSIF (x_mode = 'R') THEN
431 x_last_updated_by := fnd_global.user_id;
432 IF (x_last_updated_by IS NULL) THEN
433 x_last_updated_by := -1;
434 END IF;
435 x_last_update_login := fnd_global.login_id;
436 IF (x_last_update_login IS NULL) THEN
437 x_last_update_login := -1;
438 END IF;
439 ELSE
440 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
441 igs_ge_msg_stack.add;
442 app_exception.raise_exception;
443 END IF;
444
445 SELECT igs_en_usec_disc_dl_s.NEXTVAL
446 INTO x_usec_disc_dl_id
447 FROM dual;
448
449 before_dml(
450 p_action => 'INSERT',
451 x_rowid => x_rowid,
452 x_usec_disc_dl_id => x_usec_disc_dl_id,
453 x_non_std_disc_dl_stp_id => x_non_std_disc_dl_stp_id,
454 x_administrative_unit_status => x_administrative_unit_status,
455 x_definition_code => x_definition_code,
456 x_org_unit_code => x_org_unit_code,
457 x_formula_method => x_formula_method,
458 x_round_method => x_round_method,
459 x_offset_dt_code => x_offset_dt_code,
460 x_offset_duration => x_offset_duration,
461 x_uoo_id => x_uoo_id,
462 x_usec_disc_dl_date => x_usec_disc_dl_date,
463 x_usec_disc_total_days => x_usec_disc_total_days,
464 x_usec_disc_offset_days => x_usec_disc_offset_days,
465 x_creation_date => x_last_update_date,
466 x_created_by => x_last_updated_by,
467 x_last_update_date => x_last_update_date,
468 x_last_updated_by => x_last_updated_by,
469 x_last_update_login => x_last_update_login
470 );
471
472 INSERT INTO igs_en_usec_disc_dl (
476 definition_code,
473 usec_disc_dl_id,
474 non_std_disc_dl_stp_id,
475 administrative_unit_status,
477 org_unit_code,
478 formula_method,
479 round_method,
480 offset_dt_code,
481 offset_duration,
482 uoo_id,
483 usec_disc_dl_date,
484 usec_disc_total_days,
485 usec_disc_offset_days,
486 creation_date,
487 created_by,
488 last_update_date,
489 last_updated_by,
490 last_update_login
491 ) VALUES (
492 new_references.usec_disc_dl_id,
493 new_references.non_std_disc_dl_stp_id,
494 new_references.administrative_unit_status,
495 new_references.definition_code,
496 new_references.org_unit_code,
497 new_references.formula_method,
498 new_references.round_method,
499 new_references.offset_dt_code,
500 new_references.offset_duration,
501 new_references.uoo_id,
502 new_references.usec_disc_dl_date,
503 new_references.usec_disc_total_days,
504 new_references.usec_disc_offset_days,
505 x_last_update_date,
506 x_last_updated_by,
507 x_last_update_date,
508 x_last_updated_by,
509 x_last_update_login
510 );
511
512 OPEN c;
513 FETCH c INTO x_rowid;
514 IF (c%NOTFOUND) THEN
515 CLOSE c;
516 RAISE NO_DATA_FOUND;
517 END IF;
518 CLOSE c;
519
520 END insert_row;
521
522
523 PROCEDURE lock_row (
524 x_rowid IN VARCHAR2,
525 x_usec_disc_dl_id IN NUMBER,
526 x_non_std_disc_dl_stp_id IN NUMBER,
527 x_administrative_unit_status IN VARCHAR2,
528 x_definition_code IN VARCHAR2,
529 x_org_unit_code IN VARCHAR2,
530 x_formula_method IN VARCHAR2,
531 x_round_method IN VARCHAR2,
532 x_offset_dt_code IN VARCHAR2,
533 x_offset_duration IN NUMBER,
534 x_uoo_id IN NUMBER,
535 x_usec_disc_dl_date IN DATE,
536 x_usec_disc_total_days IN NUMBER,
537 x_usec_disc_offset_days IN NUMBER
538 ) AS
539 /*
540 || Created By : [email protected]
541 || Created On : 30-MAR-2001
542 || Purpose : Handles the LOCK mechanism for the table.
543 || Known limitations, enhancements or remarks :
544 || Change History :
545 || Who When What
546 || (reverse chronological order - newest change first)
547 */
548 CURSOR c1 IS
549 SELECT
550 non_std_disc_dl_stp_id,
551 administrative_unit_status,
552 definition_code,
553 org_unit_code,
554 formula_method,
555 round_method,
556 offset_dt_code,
557 offset_duration,
558 uoo_id,
559 usec_disc_dl_date,
560 usec_disc_total_days,
561 usec_disc_offset_days
562 FROM igs_en_usec_disc_dl
563 WHERE rowid = x_rowid
564 FOR UPDATE NOWAIT;
565
566 tlinfo c1%ROWTYPE;
567
568 BEGIN
569
570 OPEN c1;
571 FETCH c1 INTO tlinfo;
572 IF (c1%notfound) THEN
573 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
574 igs_ge_msg_stack.add;
575 CLOSE c1;
576 app_exception.raise_exception;
577 RETURN;
578 END IF;
579 CLOSE c1;
580
581 IF (
582 ((tlinfo.non_std_disc_dl_stp_id = x_non_std_disc_dl_stp_id) OR ((tlinfo.non_std_disc_dl_stp_id IS NULL) AND (X_non_std_disc_dl_stp_id IS NULL)))
583 AND ((tlinfo.administrative_unit_status = x_administrative_unit_status) OR ((tlinfo.administrative_unit_status IS NULL) AND (X_administrative_unit_status IS NULL)))
584 AND ((tlinfo.definition_code = x_definition_code) OR ((tlinfo.definition_code IS NULL) AND (X_definition_code IS NULL)))
585 AND ((tlinfo.org_unit_code = x_org_unit_code) OR ((tlinfo.org_unit_code IS NULL) AND (X_org_unit_code IS NULL)))
586 AND ((tlinfo.formula_method = x_formula_method) OR ((tlinfo.formula_method IS NULL) AND (X_formula_method IS NULL)))
587 AND ((tlinfo.round_method = x_round_method) OR ((tlinfo.round_method IS NULL) AND (X_round_method IS NULL)))
588 AND ((tlinfo.offset_dt_code = x_offset_dt_code) OR ((tlinfo.offset_dt_code IS NULL) AND (X_offset_dt_code IS NULL)))
589 AND ((tlinfo.offset_duration = x_offset_duration) OR ((tlinfo.offset_duration IS NULL) AND (X_offset_duration IS NULL)))
590 AND (tlinfo.uoo_id = x_uoo_id)
591 AND (tlinfo.usec_disc_dl_date = x_usec_disc_dl_date)
592 AND ((tlinfo.usec_disc_total_days = x_usec_disc_total_days) OR ((tlinfo.usec_disc_total_days IS NULL) AND (X_usec_disc_total_days IS NULL)))
593 AND ((tlinfo.usec_disc_offset_days = x_usec_disc_offset_days) OR ((tlinfo.usec_disc_offset_days IS NULL) AND (X_usec_disc_offset_days IS NULL)))
594 ) THEN
595 NULL;
596 ELSE
597 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
598 igs_ge_msg_stack.add;
599 app_exception.raise_exception;
600 END IF;
601
602 RETURN;
603
604 END lock_row;
608 x_rowid IN VARCHAR2,
605
606
607 PROCEDURE update_row (
609 x_usec_disc_dl_id IN NUMBER,
610 x_non_std_disc_dl_stp_id IN NUMBER,
611 x_administrative_unit_status IN VARCHAR2,
612 x_definition_code IN VARCHAR2,
613 x_org_unit_code IN VARCHAR2,
614 x_formula_method IN VARCHAR2,
615 x_round_method IN VARCHAR2,
616 x_offset_dt_code IN VARCHAR2,
617 x_offset_duration IN NUMBER,
618 x_uoo_id IN NUMBER,
619 x_usec_disc_dl_date IN DATE,
620 x_usec_disc_total_days IN NUMBER,
621 x_usec_disc_offset_days IN NUMBER,
622 x_mode IN VARCHAR2 DEFAULT 'R'
623 ) AS
624 /*
625 || Created By : [email protected]
626 || Created On : 30-MAR-2001
627 || Purpose : Handles the UPDATE 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 x_last_update_date DATE ;
634 x_last_updated_by NUMBER;
635 x_last_update_login NUMBER;
636
637 BEGIN
638
639 x_last_update_date := SYSDATE;
640 IF (X_MODE = 'I') THEN
641 x_last_updated_by := 1;
642 x_last_update_login := 0;
643 ELSIF (x_mode = 'R') THEN
644 x_last_updated_by := fnd_global.user_id;
645 IF x_last_updated_by IS NULL THEN
646 x_last_updated_by := -1;
647 END IF;
648 x_last_update_login := fnd_global.login_id;
649 IF (x_last_update_login IS NULL) THEN
650 x_last_update_login := -1;
651 END IF;
652 ELSE
653 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
654 igs_ge_msg_stack.add;
655 app_exception.raise_exception;
656 END IF;
657
658 before_dml(
659 p_action => 'UPDATE',
660 x_rowid => x_rowid,
661 x_usec_disc_dl_id => x_usec_disc_dl_id,
662 x_non_std_disc_dl_stp_id => x_non_std_disc_dl_stp_id,
663 x_administrative_unit_status => x_administrative_unit_status,
664 x_definition_code => x_definition_code,
665 x_org_unit_code => x_org_unit_code,
666 x_formula_method => x_formula_method,
667 x_round_method => x_round_method,
668 x_offset_dt_code => x_offset_dt_code,
669 x_offset_duration => x_offset_duration,
670 x_uoo_id => x_uoo_id,
671 x_usec_disc_dl_date => x_usec_disc_dl_date,
672 x_usec_disc_total_days => x_usec_disc_total_days,
673 x_usec_disc_offset_days => x_usec_disc_offset_days,
674 x_creation_date => x_last_update_date,
675 x_created_by => x_last_updated_by,
676 x_last_update_date => x_last_update_date,
677 x_last_updated_by => x_last_updated_by,
678 x_last_update_login => x_last_update_login
679 );
680
681 UPDATE igs_en_usec_disc_dl
682 SET
683 non_std_disc_dl_stp_id = new_references.non_std_disc_dl_stp_id,
684 administrative_unit_status = new_references.administrative_unit_status,
685 definition_code = new_references.definition_code,
686 org_unit_code = new_references.org_unit_code,
687 formula_method = new_references.formula_method,
688 round_method = new_references.round_method,
689 offset_dt_code = new_references.offset_dt_code,
690 offset_duration = new_references.offset_duration,
691 uoo_id = new_references.uoo_id,
692 usec_disc_dl_date = new_references.usec_disc_dl_date,
693 usec_disc_total_days = new_references.usec_disc_total_days,
694 usec_disc_offset_days = new_references.usec_disc_offset_days,
695 last_update_date = x_last_update_date,
696 last_updated_by = x_last_updated_by,
697 last_update_login = x_last_update_login
698 WHERE rowid = x_rowid;
699
700 IF (SQL%NOTFOUND) THEN
701 RAISE NO_DATA_FOUND;
702 END IF;
703
704 END update_row;
705
706
707 PROCEDURE add_row (
708 x_rowid IN OUT NOCOPY VARCHAR2,
709 x_usec_disc_dl_id IN OUT NOCOPY NUMBER,
710 x_non_std_disc_dl_stp_id IN NUMBER,
711 x_administrative_unit_status IN VARCHAR2,
712 x_definition_code IN VARCHAR2,
713 x_org_unit_code IN VARCHAR2,
714 x_formula_method IN VARCHAR2,
715 x_round_method IN VARCHAR2,
716 x_offset_dt_code IN VARCHAR2,
717 x_offset_duration IN NUMBER,
718 x_uoo_id IN NUMBER,
719 x_usec_disc_dl_date IN DATE,
720 x_usec_disc_total_days IN NUMBER,
721 x_usec_disc_offset_days IN NUMBER,
722 x_mode IN VARCHAR2 DEFAULT 'R'
723 ) AS
724 /*
725 || Created By : [email protected]
726 || Created On : 30-MAR-2001
727 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
728 || Known limitations, enhancements or remarks :
729 || Change History :
730 || Who When What
731 || (reverse chronological order - newest change first)
732 */
733 CURSOR c1 IS
734 SELECT rowid
735 FROM igs_en_usec_disc_dl
736 WHERE usec_disc_dl_id = x_usec_disc_dl_id;
737
738 BEGIN
739
740 OPEN c1;
741 FETCH c1 INTO x_rowid;
742 IF (c1%NOTFOUND) THEN
743 CLOSE c1;
744
745 insert_row (
746 x_rowid,
747 x_usec_disc_dl_id,
748 x_non_std_disc_dl_stp_id,
749 x_administrative_unit_status,
750 x_definition_code,
751 x_org_unit_code,
752 x_formula_method,
753 x_round_method,
754 x_offset_dt_code,
755 x_offset_duration,
756 x_uoo_id,
757 x_usec_disc_dl_date,
758 x_usec_disc_total_days,
759 x_usec_disc_offset_days,
760 x_mode
761 );
762 RETURN;
763 END IF;
764 CLOSE c1;
765
766 update_row (
767 x_rowid,
768 x_usec_disc_dl_id,
769 x_non_std_disc_dl_stp_id,
770 x_administrative_unit_status,
771 x_definition_code,
772 x_org_unit_code,
773 x_formula_method,
774 x_round_method,
775 x_offset_dt_code,
776 x_offset_duration,
777 x_uoo_id,
778 x_usec_disc_dl_date,
779 x_usec_disc_total_days,
780 x_usec_disc_offset_days,
781 x_mode
782 );
783
784 END add_row;
785
786
787 PROCEDURE delete_row (
788 x_rowid IN VARCHAR2
789 ) AS
790 /*
791 || Created By : [email protected]
792 || Created On : 30-MAR-2001
793 || Purpose : Handles the DELETE DML logic for the table.
794 || Known limitations, enhancements or remarks :
795 || Change History :
796 || Who When What
797 || (reverse chronological order - newest change first)
798 */
799 BEGIN
800
801 before_dml (
802 p_action => 'DELETE',
803 x_rowid => x_rowid
804 );
805
806 DELETE FROM igs_en_usec_disc_dl
807 WHERE rowid = x_rowid;
808
809 IF (SQL%NOTFOUND) THEN
810 RAISE NO_DATA_FOUND;
811 END IF;
812
813 END delete_row;
814
815
816 END igs_en_usec_disc_dl_pkg;
[Home] [Help]