[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_LOCVENUE_USE_PKG
Source
1 PACKAGE BODY igs_pe_locvenue_use_pkg AS
2 /* $Header: IGSNI76B.pls 115.4 2002/11/29 01:31:17 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_locvenue_use%ROWTYPE;
6 new_references igs_pe_locvenue_use%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_locvenue_use_id IN NUMBER DEFAULT NULL,
12 x_loc_venue_addr_id IN NUMBER DEFAULT NULL,
13 x_site_use_code IN VARCHAR2 DEFAULT NULL,
14 x_active_ind IN VARCHAR2 DEFAULT NULL,
15 x_location 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 : sitaram.rachakonda
24 || Created On : 31-AUG-2000
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_PE_LOCVENUE_USE
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.locvenue_use_id := x_locvenue_use_id;
56 new_references.loc_venue_addr_id := x_loc_venue_addr_id;
57 new_references.site_use_code := x_site_use_code;
58 new_references.active_ind := x_active_ind;
59 new_references.location := x_location;
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 PROCEDURE check_uniqueness AS
76 /*************************************************************
77 Created By : pkpatel
78 Date Created By : 13-SEP-2001
79 Purpose :
80 Know limitations, enhancements or remarks
81 Change History
82 Who When What
83
84 (reverse chronological order - newest change first)
85 ***************************************************************/
86
87 BEGIN
88 IF get_uk_for_validation (
89 new_references.loc_venue_addr_id,
90 new_references.site_use_code
91 ) THEN
92 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
93 IGS_GE_MSG_STACK.ADD;
94 APP_EXCEPTION.RAISE_EXCEPTION;
95 END IF;
96 END Check_Uniqueness ;
97
98 PROCEDURE check_parent_existance AS
99 /*
100 || Created By : sitaram.rachakonda
101 || Created On : 31-AUG-2000
102 || Purpose : Checks for the existance of Parent records.
103 || Known limitations, enhancements or remarks :
104 || Change History :
105 || Who When What
106 || (reverse chronological order - newest change first)
107 */
108 BEGIN
109
110 IF (((old_references.loc_venue_addr_id = new_references.loc_venue_addr_id)) OR
111 ((new_references.loc_venue_addr_id IS NULL))) THEN
112 NULL;
113 ELSIF NOT igs_ad_locvenue_addr_pkg.get_pk_for_validation (
114 new_references.loc_venue_addr_id
115 ) THEN
116 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
117 igs_ge_msg_stack.add;
118 app_exception.raise_exception;
119 END IF;
120
121 END check_parent_existance;
122
123
124 FUNCTION get_uk_for_validation(
125 x_loc_venue_addr_id IN NUMBER,
126 x_site_use_code IN VARCHAR2
127 ) RETURN BOOLEAN AS
128 /*
129 || Created By : sitaram.rachakonda
130 || Created On : 31-AUG-2000
131 || Purpose : Validates the Primary Key of the table.
132 || Known limitations, enhancements or remarks :
133 || Change History :
134 || Who When What
135 || (reverse chronological order - newest change first)
136 */
137
138 CURSOR cur_rowid IS
139 SELECT rowid
140 FROM igs_pe_locvenue_use
141 WHERE loc_venue_addr_id = x_loc_venue_addr_id AND
142 ((site_use_code = x_site_use_code) OR (site_use_code IS NULL AND x_site_use_code IS NULL)) AND
143 ((l_rowid is null) or (rowid <> l_rowid)) ;
144
145 lv_rowid cur_rowid%RowType;
146
147 BEGIN
148
149 OPEN cur_rowid;
150 FETCH cur_rowid INTO lv_rowid;
151 IF (cur_rowid%FOUND) THEN
152 CLOSE cur_rowid;
153 RETURN (true);
154 ELSE
155 CLOSE cur_rowid;
156 RETURN(false);
157 END IF;
158
159
160 END get_uk_for_validation;
161
162 FUNCTION get_pk_for_validation (
163 x_locvenue_use_id IN NUMBER
164 ) RETURN BOOLEAN AS
165 /*
166 || Created By : sitaram.rachakonda
167 || Created On : 31-AUG-2000
168 || Purpose : Validates the Primary Key of the table.
169 || Known limitations, enhancements or remarks :
170 || Change History :
171 || Who When What
172 || (reverse chronological order - newest change first)
173 */
174 CURSOR cur_rowid IS
175 SELECT rowid
176 FROM igs_pe_locvenue_use
177 WHERE locvenue_use_id = x_locvenue_use_id
178 FOR UPDATE NOWAIT;
179
180 lv_rowid cur_rowid%RowType;
181
182 BEGIN
183
184 OPEN cur_rowid;
185 FETCH cur_rowid INTO lv_rowid;
186 IF (cur_rowid%FOUND) THEN
187 CLOSE cur_rowid;
188 RETURN(TRUE);
189 ELSE
190 CLOSE cur_rowid;
191 RETURN(FALSE);
192 END IF;
193
194 END get_pk_for_validation;
195
196
197 PROCEDURE get_fk_igs_ad_locvenue_addr (
198 x_location_venue_addr_id IN NUMBER
199 ) AS
200 /*
201 || Created By : sitaram.rachakonda
202 || Created On : 31-AUG-2000
203 || Purpose : Validates the Foreign Keys for the table.
204 || Known limitations, enhancements or remarks :
205 || Change History :
206 || Who When What
207 || (reverse chronological order - newest change first)
208 */
209 CURSOR cur_rowid IS
210 SELECT rowid
211 FROM igs_pe_locvenue_use
212 WHERE ((loc_venue_addr_id = x_location_venue_addr_id));
213
214 lv_rowid cur_rowid%RowType;
215
216 BEGIN
217
218 OPEN cur_rowid;
219 FETCH cur_rowid INTO lv_rowid;
220 IF (cur_rowid%FOUND) THEN
221 CLOSE cur_rowid;
222 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
223 igs_ge_msg_stack.add;
224 app_exception.raise_exception;
225 RETURN;
226 END IF;
227 CLOSE cur_rowid;
228
229 END get_fk_igs_ad_locvenue_addr;
230
231
232 PROCEDURE before_dml (
233 p_action IN VARCHAR2,
234 x_rowid IN VARCHAR2 DEFAULT NULL,
235 x_locvenue_use_id IN NUMBER DEFAULT NULL,
236 x_loc_venue_addr_id IN NUMBER DEFAULT NULL,
237 x_site_use_code IN VARCHAR2 DEFAULT NULL,
238 x_active_ind IN VARCHAR2 DEFAULT NULL,
239 x_location IN VARCHAR2 DEFAULT NULL,
240 x_creation_date IN DATE DEFAULT NULL,
241 x_created_by IN NUMBER DEFAULT NULL,
242 x_last_update_date IN DATE DEFAULT NULL,
243 x_last_updated_by IN NUMBER DEFAULT NULL,
244 x_last_update_login IN NUMBER DEFAULT NULL
245 ) AS
246 /*
247 || Created By : sitaram.rachakonda
248 || Created On : 31-AUG-2000
249 || Purpose : Initialises the columns, Checks Constraints, Calls the
250 || Trigger Handlers for the table, before any DML operation.
251 || Known limitations, enhancements or remarks :
252 || Change History :
253 || Who When What
254 || (reverse chronological order - newest change first)
255 */
256 BEGIN
257
258 set_column_values (
259 p_action,
260 x_rowid,
261 x_locvenue_use_id,
262 x_loc_venue_addr_id,
263 x_site_use_code,
264 x_active_ind,
265 x_location,
266 x_creation_date,
267 x_created_by,
268 x_last_update_date,
269 x_last_updated_by,
270 x_last_update_login
271 );
272
273 IF (p_action = 'INSERT') THEN
274 -- Call all the procedures related to Before Insert.
275 IF ( get_pk_for_validation(
276 new_references.locvenue_use_id
277 )
278 ) THEN
279 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
280 igs_ge_msg_stack.add;
281 app_exception.raise_exception;
282 END IF;
283 check_uniqueness;
284 check_parent_existance;
285 ELSIF (p_action = 'UPDATE') THEN
286 -- Call all the procedures related to Before Update.
287 check_uniqueness;
288 check_parent_existance;
289 ELSIF (p_action = 'VALIDATE_INSERT') THEN
290 -- Call all the procedures related to Before Insert.
291 IF ( get_pk_for_validation (
292 new_references.locvenue_use_id
293 )
294 ) THEN
295 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
296 igs_ge_msg_stack.add;
297 app_exception.raise_exception;
298 END IF;
299 check_uniqueness;
300 END IF;
301
302 END before_dml;
303
304
305 PROCEDURE insert_row (
306 x_rowid IN OUT NOCOPY VARCHAR2,
307 x_locvenue_use_id IN OUT NOCOPY NUMBER,
308 x_loc_venue_addr_id IN NUMBER,
309 x_site_use_code IN VARCHAR2,
310 x_active_ind IN VARCHAR2,
311 x_location IN VARCHAR2,
312 x_mode IN VARCHAR2 DEFAULT 'R'
313 ) AS
314 /*
315 || Created By : sitaram.rachakonda
316 || Created On : 31-AUG-2000
317 || Purpose : Handles the INSERT DML logic for the table.
318 || Known limitations, enhancements or remarks :
319 || Change History :
320 || Who When What
321 || (reverse chronological order - newest change first)
322 */
323 CURSOR c IS
324 SELECT rowid
325 FROM igs_pe_locvenue_use
326 WHERE locvenue_use_id = x_locvenue_use_id;
327
328 l_locvenue_use_id NUMBER;
329 x_last_update_date DATE;
330 x_last_updated_by NUMBER;
331 x_last_update_login NUMBER;
332 x_request_id NUMBER;
333 x_program_id NUMBER;
334 x_program_application_id NUMBER;
335 x_program_update_date DATE;
336
337 BEGIN
338 SELECT IGS_PE_LOCVENUE_USE_S.NEXTVAL INTO L_LOCVENUE_USE_ID FROM DUAL;
339 x_locvenue_use_id := l_locvenue_use_id;
340 x_last_update_date := SYSDATE;
341 IF (x_mode = 'I') THEN
342 x_last_updated_by := 1;
343 x_last_update_login := 0;
344 ELSIF (x_mode = 'R') THEN
345 x_last_updated_by := fnd_global.user_id;
346 IF (x_last_updated_by IS NULL) THEN
347 x_last_updated_by := -1;
348 END IF;
349 x_last_update_login := fnd_global.login_id;
350 IF (x_last_update_login IS NULL) THEN
351 x_last_update_login := -1;
352 END IF;
353 x_request_id := fnd_global.conc_request_id;
354 x_program_id := fnd_global.conc_program_id;
355 x_program_application_id := fnd_global.prog_appl_id;
356
357 IF (x_request_id = -1) THEN
358 x_request_id := NULL;
359 x_program_id := NULL;
360 x_program_application_id := NULL;
361 x_program_update_date := NULL;
362 ELSE
363 x_program_update_date := SYSDATE;
364 END IF;
365 ELSE
366 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
367 igs_ge_msg_stack.add;
368 app_exception.raise_exception;
369 END IF;
370
371 before_dml(
372 p_action => 'INSERT',
373 x_rowid => x_rowid,
374 x_locvenue_use_id => x_locvenue_use_id,
375 x_loc_venue_addr_id => x_loc_venue_addr_id,
376 x_site_use_code => x_site_use_code,
377 x_active_ind => x_active_ind,
378 x_location => x_location,
379 x_creation_date => x_last_update_date,
380 x_created_by => x_last_updated_by,
381 x_last_update_date => x_last_update_date,
382 x_last_updated_by => x_last_updated_by,
383 x_last_update_login => x_last_update_login
384 );
385
386 INSERT INTO igs_pe_locvenue_use (
387 locvenue_use_id,
388 loc_venue_addr_id,
389 site_use_code,
390 active_ind,
391 location,
392 creation_date,
393 created_by,
394 last_update_date,
395 last_updated_by,
396 last_update_login,
397 request_id,
398 program_id,
399 program_application_id,
400 program_update_date
401 ) VALUES (
402 new_references.locvenue_use_id,
403 new_references.loc_venue_addr_id,
404 new_references.site_use_code,
405 new_references.active_ind,
406 new_references.location,
407 x_last_update_date,
408 x_last_updated_by,
409 x_last_update_date,
410 x_last_updated_by,
411 x_last_update_login ,
412 x_request_id,
413 x_program_id,
414 x_program_application_id,
415 x_program_update_date
416 );
417
418 OPEN c;
419 FETCH c INTO x_rowid;
420 IF (c%NOTFOUND) THEN
421 CLOSE c;
422 RAISE NO_DATA_FOUND;
423 END IF;
424 CLOSE c;
425
426 END insert_row;
427
428
429 PROCEDURE lock_row (
430 x_rowid IN VARCHAR2,
431 x_locvenue_use_id IN NUMBER,
432 x_loc_venue_addr_id IN NUMBER,
433 x_site_use_code IN VARCHAR2,
434 x_active_ind IN VARCHAR2,
435 x_location IN VARCHAR2
436 ) AS
437 /*
438 || Created By : sitaram.rachakonda
439 || Created On : 31-AUG-2000
440 || Purpose : Handles the LOCK mechanism for the table.
441 || Known limitations, enhancements or remarks :
442 || Change History :
443 || Who When What
444 || (reverse chronological order - newest change first)
445 */
446 CURSOR c1 IS
447 SELECT
448 loc_venue_addr_id,
449 site_use_code,
450 active_ind,
451 location
452 FROM igs_pe_locvenue_use
453 WHERE rowid = x_rowid
454 FOR UPDATE NOWAIT;
455
456 tlinfo c1%ROWTYPE;
457
458 BEGIN
459
460 OPEN c1;
461 FETCH c1 INTO tlinfo;
462 IF (c1%notfound) THEN
463 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
464 igs_ge_msg_stack.add;
465 CLOSE c1;
466 app_exception.raise_exception;
467 RETURN;
468 END IF;
469 CLOSE c1;
470
471 IF (
472 (tlinfo.loc_venue_addr_id = x_loc_venue_addr_id)
473 AND ((tlinfo.site_use_code = x_site_use_code) OR ((tlinfo.site_use_code IS NULL) AND (X_site_use_code IS NULL)))
474 AND (tlinfo.active_ind = x_active_ind)
475 AND ((tlinfo.location = x_location) OR ((tlinfo.location IS NULL) AND (X_location IS NULL)))
476 ) THEN
477 NULL;
478 ELSE
479 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
480 igs_ge_msg_stack.add;
481 app_exception.raise_exception;
482 END IF;
483
484 RETURN;
485
486 END lock_row;
487
488
489 PROCEDURE update_row (
490 x_rowid IN VARCHAR2,
491 x_locvenue_use_id IN NUMBER,
492 x_loc_venue_addr_id IN NUMBER,
493 x_site_use_code IN VARCHAR2,
494 x_active_ind IN VARCHAR2,
495 x_location IN VARCHAR2,
496 x_mode IN VARCHAR2 DEFAULT 'R'
497 ) AS
498 /*
499 || Created By : sitaram.rachakonda
500 || Created On : 31-AUG-2000
501 || Purpose : Handles the UPDATE DML logic for the table.
502 || Known limitations, enhancements or remarks :
503 || Change History :
504 || Who When What
505 || (reverse chronological order - newest change first)
506 */
507 x_last_update_date DATE ;
508 x_last_updated_by NUMBER;
509 x_last_update_login NUMBER;
510 x_request_id NUMBER;
511 x_program_id NUMBER;
512 x_program_application_id NUMBER;
513 x_program_update_date DATE;
514
515 BEGIN
516
517 x_last_update_date := SYSDATE;
518 IF (X_MODE = 'I') THEN
519 x_last_updated_by := 1;
520 x_last_update_login := 0;
521 ELSIF (x_mode = 'R') THEN
522 x_last_updated_by := fnd_global.user_id;
523 IF x_last_updated_by IS NULL THEN
524 x_last_updated_by := -1;
525 END IF;
526 x_last_update_login := fnd_global.login_id;
527 IF (x_last_update_login IS NULL) THEN
528 x_last_update_login := -1;
529 END IF;
530 ELSE
531 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
532 igs_ge_msg_stack.add;
533 app_exception.raise_exception;
534 END IF;
535
536 before_dml(
537 p_action => 'UPDATE',
538 x_rowid => x_rowid,
539 x_locvenue_use_id => x_locvenue_use_id,
540 x_loc_venue_addr_id => x_loc_venue_addr_id,
541 x_site_use_code => x_site_use_code,
542 x_active_ind => x_active_ind,
543 x_location => x_location,
544 x_creation_date => x_last_update_date,
545 x_created_by => x_last_updated_by,
546 x_last_update_date => x_last_update_date,
547 x_last_updated_by => x_last_updated_by,
548 x_last_update_login => x_last_update_login
549 );
550
551 IF (x_mode = 'R') THEN
552 x_request_id := fnd_global.conc_request_id;
553 x_program_id := fnd_global.conc_program_id;
554 x_program_application_id := fnd_global.prog_appl_id;
555 IF (x_request_id = -1) THEN
556 x_request_id := old_references.request_id;
557 x_program_id := old_references.program_id;
558 x_program_application_id := old_references.program_application_id;
559 x_program_update_date := old_references.program_update_date;
560 ELSE
561 x_program_update_date := SYSDATE;
562 END IF;
563 END IF;
564
565 UPDATE igs_pe_locvenue_use
566 SET
567 loc_venue_addr_id = new_references.loc_venue_addr_id,
568 site_use_code = new_references.site_use_code,
569 active_ind = new_references.active_ind,
570 location = new_references.location,
571 last_update_date = x_last_update_date,
572 last_updated_by = x_last_updated_by,
573 last_update_login = x_last_update_login ,
574 request_id = x_request_id,
575 program_id = x_program_id,
576 program_application_id = x_program_application_id,
577 program_update_date = x_program_update_date
578 WHERE rowid = x_rowid;
579
580 IF (SQL%NOTFOUND) THEN
581 RAISE NO_DATA_FOUND;
582 END IF;
583
584 END update_row;
585
586
587 PROCEDURE add_row (
588 x_rowid IN OUT NOCOPY VARCHAR2,
589 x_locvenue_use_id IN OUT NOCOPY NUMBER,
590 x_loc_venue_addr_id IN NUMBER,
591 x_site_use_code IN VARCHAR2,
592 x_active_ind IN VARCHAR2,
593 x_location IN VARCHAR2,
594 x_mode IN VARCHAR2 DEFAULT 'R'
595 ) AS
596 /*
597 || Created By : sitaram.rachakonda
598 || Created On : 31-AUG-2000
599 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
600 || Known limitations, enhancements or remarks :
601 || Change History :
602 || Who When What
603 || (reverse chronological order - newest change first)
604 */
605 CURSOR c1 IS
606 SELECT rowid
607 FROM igs_pe_locvenue_use
608 WHERE locvenue_use_id = x_locvenue_use_id;
609
610 BEGIN
611
612 OPEN c1;
613 FETCH c1 INTO x_rowid;
614 IF (c1%NOTFOUND) THEN
615 CLOSE c1;
616
617 insert_row (
618 x_rowid,
619 x_locvenue_use_id,
620 x_loc_venue_addr_id,
621 x_site_use_code,
622 x_active_ind,
623 x_location,
624 x_mode
625 );
626 RETURN;
627 END IF;
628 CLOSE c1;
629
630 update_row (
631 x_rowid,
632 x_locvenue_use_id,
633 x_loc_venue_addr_id,
634 x_site_use_code,
635 x_active_ind,
636 x_location,
637 x_mode
638 );
639
640 END add_row;
641
642
643 PROCEDURE delete_row (
644 x_rowid IN VARCHAR2
645 ) AS
646 /*
647 || Created By : sitaram.rachakonda
648 || Created On : 31-AUG-2000
649 || Purpose : Handles the DELETE DML logic for the table.
650 || Known limitations, enhancements or remarks :
651 || Change History :
652 || Who When What
653 || (reverse chronological order - newest change first)
654 */
655 BEGIN
656
657 before_dml (
658 p_action => 'DELETE',
659 x_rowid => x_rowid
660 );
661
662 DELETE FROM igs_pe_locvenue_use
663 WHERE rowid = x_rowid;
664
665 IF (SQL%NOTFOUND) THEN
666 RAISE NO_DATA_FOUND;
667 END IF;
668
669 END delete_row;
670
671
672 END igs_pe_locvenue_use_pkg;