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