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