1 PACKAGE BODY igs_en_nstd_usec_dl_pkg AS
2 /* $Header: IGSEI47B.pls 115.6 2002/11/28 23:43:52 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_nstd_usec_dl%ROWTYPE;
6 new_references igs_en_nstd_usec_dl%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_nstd_usec_dl_id IN NUMBER DEFAULT NULL,
12 x_non_std_usec_dls_id IN NUMBER DEFAULT NULL,
13 x_function_name 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_enr_dl_date IN DATE DEFAULT NULL,
22 x_enr_dl_total_days IN NUMBER DEFAULT NULL,
23 x_enr_dl_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_NSTD_USEC_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.nstd_usec_dl_id := x_nstd_usec_dl_id;
64 new_references.non_std_usec_dls_id := x_non_std_usec_dls_id;
65 new_references.function_name := x_function_name;
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.enr_dl_date := x_enr_dl_date;
74 new_references.enr_dl_total_days := x_enr_dl_total_days;
75 new_references.enr_dl_offset_days := x_enr_dl_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.function_name,
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.uoo_id = new_references.uoo_id)) OR
130 ((new_references.uoo_id IS NULL))) THEN
131 NULL;
132 ELSIF NOT igs_ps_unit_ofr_opt_pkg.get_uk_For_validation (
133 new_references.uoo_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.non_std_usec_dls_id = new_references.non_std_usec_dls_id)) OR
141 ((new_references.non_std_usec_dls_id IS NULL))) THEN
142 NULL;
143 ELSIF NOT igs_en_nsu_dlstp_pkg.get_pk_for_validation (
144 new_references.non_std_usec_dls_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_nstd_usec_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_nstd_usec_dl
169 WHERE nstd_usec_dl_id = x_nstd_usec_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_function_name 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_nstd_usec_dl
205 WHERE ((function_name = x_function_name) OR (function_name IS NULL AND x_function_name 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_ufk_igs_ps_unit_ofr_opt (
227 x_uoo_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_nstd_usec_dl
241 WHERE ((uoo_id = x_uoo_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_NSDL_UOO_UFK');
252 igs_ge_msg_stack.add;
253 app_exception.raise_exception;
254 RETURN;
255 END IF;
256 CLOSE cur_rowid;
257
258 END get_ufk_igs_ps_unit_ofr_opt;
259
260
261 PROCEDURE get_fk_igs_en_nsu_dlstp_all (
262 x_non_std_usec_dls_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_nstd_usec_dl
276 WHERE ((non_std_usec_dls_id = x_non_std_usec_dls_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_NSDL_NSUD_FK');
287 igs_ge_msg_stack.add;
288 app_exception.raise_exception;
289 RETURN;
290 END IF;
291 CLOSE cur_rowid;
292
293 END get_fk_igs_en_nsu_dlstp_all;
294
295
296 PROCEDURE before_dml (
297 p_action IN VARCHAR2,
298 x_rowid IN VARCHAR2 DEFAULT NULL,
299 x_nstd_usec_dl_id IN NUMBER DEFAULT NULL,
300 x_non_std_usec_dls_id IN NUMBER DEFAULT NULL,
301 x_function_name 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_enr_dl_date IN DATE DEFAULT NULL,
310 x_enr_dl_total_days IN NUMBER DEFAULT NULL,
311 x_enr_dl_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_nstd_usec_dl_id,
334 x_non_std_usec_dls_id,
335 x_function_name,
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_enr_dl_date,
344 x_enr_dl_total_days,
345 x_enr_dl_offset_days,
346 x_creation_date,
347 x_created_by,
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.nstd_usec_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.nstd_usec_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 END before_dml;
385
386
387 PROCEDURE insert_row (
388 x_rowid IN OUT NOCOPY VARCHAR2,
389 x_nstd_usec_dl_id IN OUT NOCOPY NUMBER,
390 x_non_std_usec_dls_id IN NUMBER,
391 x_function_name IN VARCHAR2,
392 x_definition_code IN VARCHAR2,
393 x_org_unit_code IN VARCHAR2,
394 x_formula_method IN VARCHAR2,
395 x_round_method IN VARCHAR2,
396 x_offset_dt_code IN VARCHAR2,
397 x_offset_duration IN NUMBER,
398 x_uoo_id IN NUMBER,
399 x_enr_dl_date IN DATE,
400 x_enr_dl_total_days IN NUMBER,
401 x_enr_dl_offset_days IN NUMBER,
405 || Created By : [email protected]
402 x_mode IN VARCHAR2 DEFAULT 'R'
403 ) AS
404 /*
406 || Created On : 30-MAR-2001
407 || Purpose : Handles the INSERT DML logic for the table.
408 || Known limitations, enhancements or remarks :
409 || Change History :
410 || Who When What
411 || (reverse chronological order - newest change first)
412 */
413 CURSOR c IS
414 SELECT rowid
415 FROM igs_en_nstd_usec_dl
416 WHERE nstd_usec_dl_id = x_nstd_usec_dl_id;
417
418 x_last_update_date DATE;
419 x_last_updated_by NUMBER;
420 x_last_update_login NUMBER;
421
422 BEGIN
423
424 x_last_update_date := SYSDATE;
425 IF (x_mode = 'I') THEN
426 x_last_updated_by := 1;
427 x_last_update_login := 0;
428 ELSIF (x_mode = 'R') THEN
429 x_last_updated_by := fnd_global.user_id;
430 IF (x_last_updated_by IS NULL) THEN
431 x_last_updated_by := -1;
432 END IF;
433 x_last_update_login := fnd_global.login_id;
434 IF (x_last_update_login IS NULL) THEN
435 x_last_update_login := -1;
436 END IF;
437 ELSE
438 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
439 igs_ge_msg_stack.add;
440 app_exception.raise_exception;
441 END IF;
442
443 SELECT igs_en_nstd_usec_dl_s.NEXTVAL
444 INTO x_nstd_usec_dl_id
445 FROM dual;
446
447 before_dml(
448 p_action => 'INSERT',
449 x_rowid => x_rowid,
450 x_nstd_usec_dl_id => x_nstd_usec_dl_id,
451 x_non_std_usec_dls_id => x_non_std_usec_dls_id,
452 x_function_name => x_function_name,
453 x_definition_code => x_definition_code,
454 x_org_unit_code => x_org_unit_code,
455 x_formula_method => x_formula_method,
456 x_round_method => x_round_method,
457 x_offset_dt_code => x_offset_dt_code,
458 x_offset_duration => x_offset_duration,
459 x_uoo_id => x_uoo_id,
460 x_enr_dl_date => x_enr_dl_date,
461 x_enr_dl_total_days => x_enr_dl_total_days,
462 x_enr_dl_offset_days => x_enr_dl_offset_days,
463 x_creation_date => x_last_update_date,
464 x_created_by => x_last_updated_by,
465 x_last_update_date => x_last_update_date,
466 x_last_updated_by => x_last_updated_by,
467 x_last_update_login => x_last_update_login
468 );
469
470 INSERT INTO igs_en_nstd_usec_dl (
471 nstd_usec_dl_id,
472 non_std_usec_dls_id,
473 function_name,
474 definition_code,
475 org_unit_code,
476 formula_method,
477 round_method,
478 offset_dt_code,
479 offset_duration,
480 uoo_id,
481 enr_dl_date,
482 enr_dl_total_days,
483 enr_dl_offset_days,
484 creation_date,
485 created_by,
486 last_update_date,
487 last_updated_by,
488 last_update_login
489 ) VALUES (
490 new_references.nstd_usec_dl_id,
491 new_references.non_std_usec_dls_id,
492 new_references.function_name,
493 new_references.definition_code,
494 new_references.org_unit_code,
495 new_references.formula_method,
496 new_references.round_method,
497 new_references.offset_dt_code,
498 new_references.offset_duration,
499 new_references.uoo_id,
500 new_references.enr_dl_date,
501 new_references.enr_dl_total_days,
502 new_references.enr_dl_offset_days,
503 x_last_update_date,
504 x_last_updated_by,
505 x_last_update_date,
506 x_last_updated_by,
507 x_last_update_login
508 );
509
510 OPEN c;
511 FETCH c INTO x_rowid;
512 IF (c%NOTFOUND) THEN
513 CLOSE c;
514 RAISE NO_DATA_FOUND;
515 END IF;
516 CLOSE c;
517
518 END insert_row;
519
520
521 PROCEDURE lock_row (
522 x_rowid IN VARCHAR2,
523 x_nstd_usec_dl_id IN NUMBER,
524 x_non_std_usec_dls_id IN NUMBER,
525 x_function_name IN VARCHAR2,
526 x_definition_code IN VARCHAR2,
527 x_org_unit_code IN VARCHAR2,
528 x_formula_method IN VARCHAR2,
529 x_round_method IN VARCHAR2,
530 x_offset_dt_code IN VARCHAR2,
531 x_offset_duration IN NUMBER,
532 x_uoo_id IN NUMBER,
533 x_enr_dl_date IN DATE,
534 x_enr_dl_total_days IN NUMBER,
535 x_enr_dl_offset_days IN NUMBER
536 ) AS
540 || Purpose : Handles the LOCK mechanism for the table.
537 /*
538 || Created By : [email protected]
539 || Created On : 30-MAR-2001
541 || Known limitations, enhancements or remarks :
542 || Change History :
543 || Who When What
544 || (reverse chronological order - newest change first)
545 */
546 CURSOR c1 IS
547 SELECT
548 non_std_usec_dls_id,
549 function_name,
550 definition_code,
551 org_unit_code,
552 formula_method,
553 round_method,
554 offset_dt_code,
555 offset_duration,
556 uoo_id,
557 enr_dl_date,
558 enr_dl_total_days,
559 enr_dl_offset_days
560 FROM igs_en_nstd_usec_dl
561 WHERE rowid = x_rowid
562 FOR UPDATE NOWAIT;
563
564 tlinfo c1%ROWTYPE;
565
566 BEGIN
567
568 OPEN c1;
569 FETCH c1 INTO tlinfo;
570 IF (c1%notfound) THEN
571 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
572 igs_ge_msg_stack.add;
573 CLOSE c1;
574 app_exception.raise_exception;
575 RETURN;
576 END IF;
577 CLOSE c1;
578
579 IF (
580 ((tlinfo.non_std_usec_dls_id = x_non_std_usec_dls_id) OR ((tlinfo.non_std_usec_dls_id IS NULL) AND (X_non_std_usec_dls_id IS NULL)))
581 AND ((tlinfo.function_name = x_function_name) OR ((tlinfo.function_name IS NULL) AND (X_function_name IS NULL)))
582 AND ((tlinfo.definition_code = x_definition_code) OR ((tlinfo.definition_code IS NULL) AND (X_definition_code IS NULL)))
583 AND ((tlinfo.org_unit_code = x_org_unit_code) OR ((tlinfo.org_unit_code IS NULL) AND (X_org_unit_code IS NULL)))
584 AND ((tlinfo.formula_method = x_formula_method) OR ((tlinfo.formula_method IS NULL) AND (X_formula_method IS NULL)))
585 AND ((tlinfo.round_method = x_round_method) OR ((tlinfo.round_method IS NULL) AND (X_round_method IS NULL)))
586 AND ((tlinfo.offset_dt_code = x_offset_dt_code) OR ((tlinfo.offset_dt_code IS NULL) AND (X_offset_dt_code IS NULL)))
587 AND ((tlinfo.offset_duration = x_offset_duration) OR ((tlinfo.offset_duration IS NULL) AND (X_offset_duration IS NULL)))
588 AND (tlinfo.uoo_id = x_uoo_id)
589 AND (tlinfo.enr_dl_date = x_enr_dl_date)
590 AND ((tlinfo.enr_dl_total_days = x_enr_dl_total_days) OR ((tlinfo.enr_dl_total_days IS NULL) AND (X_enr_dl_total_days IS NULL)))
591 AND ((tlinfo.enr_dl_offset_days = x_enr_dl_offset_days) OR ((tlinfo.enr_dl_offset_days IS NULL) AND (X_enr_dl_offset_days IS NULL)))
592 ) THEN
593 NULL;
594 ELSE
595 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
596 igs_ge_msg_stack.add;
597 app_exception.raise_exception;
598 END IF;
599
600 RETURN;
601
602 END lock_row;
603
604
605 PROCEDURE update_row (
606 x_rowid IN VARCHAR2,
607 x_nstd_usec_dl_id IN NUMBER,
608 x_non_std_usec_dls_id IN NUMBER,
609 x_function_name IN VARCHAR2,
610 x_definition_code IN VARCHAR2,
611 x_org_unit_code IN VARCHAR2,
612 x_formula_method IN VARCHAR2,
613 x_round_method IN VARCHAR2,
614 x_offset_dt_code IN VARCHAR2,
615 x_offset_duration IN NUMBER,
616 x_uoo_id IN NUMBER,
617 x_enr_dl_date IN DATE,
618 x_enr_dl_total_days IN NUMBER,
619 x_enr_dl_offset_days IN NUMBER,
620 x_mode IN VARCHAR2 DEFAULT 'R'
621 ) AS
622 /*
623 || Created By : [email protected]
624 || Created On : 30-MAR-2001
625 || Purpose : Handles the UPDATE DML logic for the table.
626 || Known limitations, enhancements or remarks :
627 || Change History :
628 || Who When What
629 || (reverse chronological order - newest change first)
630 */
631 x_last_update_date DATE ;
632 x_last_updated_by NUMBER;
633 x_last_update_login NUMBER;
634
635 BEGIN
636
637 x_last_update_date := SYSDATE;
638 IF (X_MODE = 'I') THEN
639 x_last_updated_by := 1;
640 x_last_update_login := 0;
641 ELSIF (x_mode = 'R') THEN
642 x_last_updated_by := fnd_global.user_id;
643 IF x_last_updated_by IS NULL THEN
644 x_last_updated_by := -1;
645 END IF;
646 x_last_update_login := fnd_global.login_id;
647 IF (x_last_update_login IS NULL) THEN
648 x_last_update_login := -1;
649 END IF;
650 ELSE
651 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
652 igs_ge_msg_stack.add;
653 app_exception.raise_exception;
654 END IF;
655
656 before_dml(
657 p_action => 'UPDATE',
658 x_rowid => x_rowid,
659 x_nstd_usec_dl_id => x_nstd_usec_dl_id,
660 x_non_std_usec_dls_id => x_non_std_usec_dls_id,
664 x_formula_method => x_formula_method,
661 x_function_name => x_function_name,
662 x_definition_code => x_definition_code,
663 x_org_unit_code => x_org_unit_code,
665 x_round_method => x_round_method,
666 x_offset_dt_code => x_offset_dt_code,
667 x_offset_duration => x_offset_duration,
668 x_uoo_id => x_uoo_id,
669 x_enr_dl_date => x_enr_dl_date,
670 x_enr_dl_total_days => x_enr_dl_total_days,
671 x_enr_dl_offset_days => x_enr_dl_offset_days,
672 x_creation_date => x_last_update_date,
673 x_created_by => x_last_updated_by,
674 x_last_update_date => x_last_update_date,
675 x_last_updated_by => x_last_updated_by,
676 x_last_update_login => x_last_update_login
677 );
678
679 UPDATE igs_en_nstd_usec_dl
680 SET
681 non_std_usec_dls_id = new_references.non_std_usec_dls_id,
682 function_name = new_references.function_name,
683 definition_code = new_references.definition_code,
684 org_unit_code = new_references.org_unit_code,
685 formula_method = new_references.formula_method,
686 round_method = new_references.round_method,
687 offset_dt_code = new_references.offset_dt_code,
688 offset_duration = new_references.offset_duration,
689 uoo_id = new_references.uoo_id,
690 enr_dl_date = new_references.enr_dl_date,
691 enr_dl_total_days = new_references.enr_dl_total_days,
692 enr_dl_offset_days = new_references.enr_dl_offset_days,
693 last_update_date = x_last_update_date,
694 last_updated_by = x_last_updated_by,
695 last_update_login = x_last_update_login
696 WHERE rowid = x_rowid;
697
698 IF (SQL%NOTFOUND) THEN
699 RAISE NO_DATA_FOUND;
700 END IF;
701
702 END update_row;
703
704
705 PROCEDURE add_row (
706 x_rowid IN OUT NOCOPY VARCHAR2,
707 x_nstd_usec_dl_id IN OUT NOCOPY NUMBER,
708 x_non_std_usec_dls_id IN NUMBER,
709 x_function_name IN VARCHAR2,
710 x_definition_code IN VARCHAR2,
711 x_org_unit_code IN VARCHAR2,
712 x_formula_method IN VARCHAR2,
713 x_round_method IN VARCHAR2,
714 x_offset_dt_code IN VARCHAR2,
715 x_offset_duration IN NUMBER,
716 x_uoo_id IN NUMBER,
717 x_enr_dl_date IN DATE,
718 x_enr_dl_total_days IN NUMBER,
719 x_enr_dl_offset_days IN NUMBER,
720 x_mode IN VARCHAR2 DEFAULT 'R'
721 ) AS
722 /*
723 || Created By : [email protected]
724 || Created On : 30-MAR-2001
725 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
726 || Known limitations, enhancements or remarks :
727 || Change History :
728 || Who When What
729 || (reverse chronological order - newest change first)
730 */
731 CURSOR c1 IS
732 SELECT rowid
733 FROM igs_en_nstd_usec_dl
734 WHERE nstd_usec_dl_id = x_nstd_usec_dl_id;
735
736 BEGIN
737
738 OPEN c1;
739 FETCH c1 INTO x_rowid;
740 IF (c1%NOTFOUND) THEN
741 CLOSE c1;
742
743 insert_row (
744 x_rowid,
745 x_nstd_usec_dl_id,
746 x_non_std_usec_dls_id,
747 x_function_name,
748 x_definition_code,
749 x_org_unit_code,
750 x_formula_method,
751 x_round_method,
752 x_offset_dt_code,
753 x_offset_duration,
754 x_uoo_id,
755 x_enr_dl_date,
756 x_enr_dl_total_days,
757 x_enr_dl_offset_days,
758 x_mode
759 );
760 RETURN;
761 END IF;
762 CLOSE c1;
763
764 update_row (
765 x_rowid,
766 x_nstd_usec_dl_id,
767 x_non_std_usec_dls_id,
768 x_function_name,
769 x_definition_code,
770 x_org_unit_code,
771 x_formula_method,
772 x_round_method,
773 x_offset_dt_code,
774 x_offset_duration,
775 x_uoo_id,
776 x_enr_dl_date,
777 x_enr_dl_total_days,
778 x_enr_dl_offset_days,
779 x_mode
780 );
781
782 END add_row;
783
784
785 PROCEDURE delete_row (
786 x_rowid IN VARCHAR2
787 ) AS
788 /*
789 || Created By : [email protected]
790 || Created On : 30-MAR-2001
791 || Purpose : Handles the DELETE DML logic for the table.
792 || Known limitations, enhancements or remarks :
793 || Change History :
794 || Who When What
795 || (reverse chronological order - newest change first)
796 */
797 BEGIN
798
799 before_dml (
800 p_action => 'DELETE',
801 x_rowid => x_rowid
802 );
803
804 DELETE FROM igs_en_nstd_usec_dl
805 WHERE rowid = x_rowid;
806
807 IF (SQL%NOTFOUND) THEN
808 RAISE NO_DATA_FOUND;
809 END IF;
810
811 END delete_row;
812
813
814 END igs_en_nstd_usec_dl_pkg;
[Home] [Help]