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