1 PACKAGE BODY igf_ap_st_corr_text_pkg AS
2 /* $Header: IGFAI39B.pls 115.4 2002/11/28 13:59:58 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_ap_st_corr_text%ROWTYPE;
6 new_references igf_ap_st_corr_text%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2 ,
10 x_rowid IN VARCHAR2 ,
11 x_corsp_id IN NUMBER ,
12 x_base_id IN NUMBER ,
13 x_custom_text IN VARCHAR2 ,
14 x_run_date IN DATE ,
15 x_active IN VARCHAR2 ,
16 x_creation_date IN DATE ,
17 x_created_by IN NUMBER ,
18 x_last_update_date IN DATE ,
19 x_last_updated_by IN NUMBER ,
20 x_last_update_login IN NUMBER
21 ) AS
22 /*
23 || Created By : kkillams
24 || Created On : 03-JUN-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 IGF_AP_ST_CORR_TEXT
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.corsp_id := x_corsp_id;
56 new_references.base_id := x_base_id;
57 new_references.custom_text := x_custom_text;
58 new_references.run_date := x_run_date;
59 new_references.active := x_active;
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 : 03-JUN-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.base_id = new_references.base_id)) OR
89 ((new_references.base_id IS NULL))) THEN
90 NULL;
91 ELSIF NOT igf_ap_fa_base_rec_pkg.get_pk_for_validation (
92 new_references.base_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_corsp_id IN NUMBER
104 ) RETURN BOOLEAN AS
105 /*
106 || Created By : kkillams
107 || Created On : 03-JUN-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 igf_ap_st_corr_text
117 WHERE corsp_id = x_corsp_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_fk_igf_ap_fa_base_rec_all (
138 x_base_id IN NUMBER
139 ) AS
140 /*
141 || Created By : kkillams
142 || Created On : 03-JUN-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 igf_ap_st_corr_text
152 WHERE ((base_id = x_base_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 ('IGF', 'IGF_AP_CORRT_FABASE_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_fk_igf_ap_fa_base_rec_all;
170
171
172 PROCEDURE before_dml (
173 p_action IN VARCHAR2 ,
174 x_rowid IN VARCHAR2 ,
175 x_corsp_id IN NUMBER ,
176 x_base_id IN NUMBER ,
177 x_custom_text IN VARCHAR2 ,
178 x_run_date IN DATE ,
179 x_active IN VARCHAR2 ,
180 x_creation_date IN DATE ,
181 x_created_by IN NUMBER ,
182 x_last_update_date IN DATE ,
183 x_last_updated_by IN NUMBER ,
184 x_last_update_login IN NUMBER
185 ) AS
186 /*
187 || Created By : kkillams
188 || Created On : 03-JUN-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_corsp_id,
202 x_base_id,
203 x_custom_text,
204 x_run_date,
205 x_active,
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.corsp_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.corsp_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_corsp_id IN OUT NOCOPY NUMBER,
245 x_base_id IN NUMBER,
246 x_custom_text IN VARCHAR2,
247 x_run_date IN DATE,
248 x_active IN VARCHAR2,
249 x_mode IN VARCHAR2
250 ) AS
251 /*
252 || Created By : kkillams
253 || Created On : 03-JUN-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 igf_ap_st_corr_text
263 WHERE corsp_id = x_corsp_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 igf_ap_st_corr_text_s.NEXTVAL
291 INTO x_corsp_id
292 FROM dual;
293
294 before_dml(
295 p_action => 'INSERT',
296 x_rowid => x_rowid,
297 x_corsp_id => x_corsp_id,
298 x_base_id => x_base_id,
299 x_custom_text => x_custom_text,
300 x_run_date => x_run_date,
301 x_active => x_active,
302 x_creation_date => x_last_update_date,
303 x_created_by => x_last_updated_by,
304 x_last_update_date => x_last_update_date,
305 x_last_updated_by => x_last_updated_by,
306 x_last_update_login => x_last_update_login
307 );
308
309 INSERT INTO igf_ap_st_corr_text (
310 corsp_id,
311 base_id,
312 custom_text,
313 run_date,
314 active,
315 creation_date,
316 created_by,
317 last_update_date,
318 last_updated_by,
319 last_update_login
320 ) VALUES (
321 new_references.corsp_id,
322 new_references.base_id,
323 new_references.custom_text,
324 new_references.run_date,
325 new_references.active,
326 x_last_update_date,
327 x_last_updated_by,
328 x_last_update_date,
329 x_last_updated_by,
330 x_last_update_login
331 );
332
333 OPEN c;
334 FETCH c INTO x_rowid;
335 IF (c%NOTFOUND) THEN
336 CLOSE c;
337 RAISE NO_DATA_FOUND;
338 END IF;
339 CLOSE c;
340
341 END insert_row;
342
343
344 PROCEDURE lock_row (
345 x_rowid IN VARCHAR2,
346 x_corsp_id IN NUMBER,
347 x_base_id IN NUMBER,
348 x_custom_text IN VARCHAR2,
349 x_run_date IN DATE,
350 x_active IN VARCHAR2
351 ) AS
352 /*
353 || Created By : kkillams
354 || Created On : 03-JUN-2001
355 || Purpose : Handles the LOCK mechanism for the table.
356 || Known limitations, enhancements or remarks :
357 || Change History :
358 || Who When What
359 || (reverse chronological order - newest change first)
360 */
361 CURSOR c1 IS
362 SELECT
363 base_id,
364 custom_text,
365 run_date,
366 active
367 FROM igf_ap_st_corr_text
368 WHERE rowid = x_rowid
369 FOR UPDATE NOWAIT;
370
371 tlinfo c1%ROWTYPE;
372
373 BEGIN
374
375 OPEN c1;
376 FETCH c1 INTO tlinfo;
377 IF (c1%notfound) THEN
378 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
379 igs_ge_msg_stack.add;
380 CLOSE c1;
381 app_exception.raise_exception;
382 RETURN;
383 END IF;
384 CLOSE c1;
385
386 IF (
387 (tlinfo.base_id = x_base_id)
388 AND (tlinfo.custom_text = x_custom_text)
389 AND ((tlinfo.run_date = x_run_date) OR ((tlinfo.run_date IS NULL) AND (X_run_date IS NULL)))
390 AND (tlinfo.active = x_active)
391 ) THEN
392 NULL;
393 ELSE
394 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
395 igs_ge_msg_stack.add;
396 app_exception.raise_exception;
397 END IF;
398
399 RETURN;
400
401 END lock_row;
402
403
404 PROCEDURE update_row (
405 x_rowid IN VARCHAR2,
406 x_corsp_id IN NUMBER,
407 x_base_id IN NUMBER,
408 x_custom_text IN VARCHAR2,
409 x_run_date IN DATE,
410 x_active IN VARCHAR2,
411 x_mode IN VARCHAR2
412 ) AS
413 /*
414 || Created By : kkillams
415 || Created On : 03-JUN-2001
416 || Purpose : Handles the UPDATE DML logic for the table.
417 || Known limitations, enhancements or remarks :
418 || Change History :
419 || Who When What
420 || (reverse chronological order - newest change first)
421 */
422 x_last_update_date DATE ;
423 x_last_updated_by NUMBER;
424 x_last_update_login NUMBER;
425
426 BEGIN
427
428 x_last_update_date := SYSDATE;
429 IF (X_MODE = 'I') THEN
430 x_last_updated_by := 1;
431 x_last_update_login := 0;
432 ELSIF (x_mode = 'R') THEN
433 x_last_updated_by := fnd_global.user_id;
434 IF x_last_updated_by IS NULL THEN
435 x_last_updated_by := -1;
436 END IF;
437 x_last_update_login := fnd_global.login_id;
438 IF (x_last_update_login IS NULL) THEN
439 x_last_update_login := -1;
440 END IF;
441 ELSE
442 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
443 igs_ge_msg_stack.add;
444 app_exception.raise_exception;
445 END IF;
446
447 before_dml(
448 p_action => 'UPDATE',
449 x_rowid => x_rowid,
450 x_corsp_id => x_corsp_id,
451 x_base_id => x_base_id,
452 x_custom_text => x_custom_text,
453 x_run_date => x_run_date,
454 x_active => x_active,
455 x_creation_date => x_last_update_date,
456 x_created_by => x_last_updated_by,
457 x_last_update_date => x_last_update_date,
458 x_last_updated_by => x_last_updated_by,
459 x_last_update_login => x_last_update_login
460 );
461
462 UPDATE igf_ap_st_corr_text
463 SET
464 base_id = new_references.base_id,
465 custom_text = new_references.custom_text,
466 run_date = new_references.run_date,
467 active = new_references.active,
468 last_update_date = x_last_update_date,
469 last_updated_by = x_last_updated_by,
470 last_update_login = x_last_update_login
471 WHERE rowid = x_rowid;
472
473 IF (SQL%NOTFOUND) THEN
474 RAISE NO_DATA_FOUND;
475 END IF;
476
477 END update_row;
478
479
480 PROCEDURE add_row (
481 x_rowid IN OUT NOCOPY VARCHAR2,
482 x_corsp_id IN OUT NOCOPY NUMBER,
483 x_base_id IN NUMBER,
484 x_custom_text IN VARCHAR2,
485 x_run_date IN DATE,
486 x_active IN VARCHAR2,
487 x_mode IN VARCHAR2
488 ) AS
489 /*
490 || Created By : kkillams
491 || Created On : 03-JUN-2001
492 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
493 || Known limitations, enhancements or remarks :
494 || Change History :
495 || Who When What
496 || (reverse chronological order - newest change first)
497 */
498 CURSOR c1 IS
499 SELECT rowid
500 FROM igf_ap_st_corr_text
501 WHERE corsp_id = x_corsp_id;
502
503 BEGIN
504
505 OPEN c1;
506 FETCH c1 INTO x_rowid;
507 IF (c1%NOTFOUND) THEN
508 CLOSE c1;
509
510 insert_row (
511 x_rowid,
512 x_corsp_id,
513 x_base_id,
514 x_custom_text,
515 x_run_date,
516 x_active,
517 x_mode
518 );
519 RETURN;
520 END IF;
521 CLOSE c1;
522
523 update_row (
524 x_rowid,
525 x_corsp_id,
526 x_base_id,
527 x_custom_text,
528 x_run_date,
529 x_active,
530 x_mode
531 );
532
533 END add_row;
534
535
536 PROCEDURE delete_row (
537 x_rowid IN VARCHAR2
538 ) AS
539 /*
540 || Created By : kkillams
541 || Created On : 03-JUN-2001
542 || Purpose : Handles the DELETE DML logic 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 BEGIN
549
550 before_dml (
551 p_action => 'DELETE',
552 x_rowid => x_rowid
553 );
554
555 DELETE FROM igf_ap_st_corr_text
556 WHERE rowid = x_rowid;
557
558 IF (SQL%NOTFOUND) THEN
559 RAISE NO_DATA_FOUND;
560 END IF;
561
562 END delete_row;
563
564
565 END igf_ap_st_corr_text_pkg;
[Home] [Help]