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