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