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