[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_PERS_NOTE_PKG
Source
1 package body IGS_PE_PERS_NOTE_PKG AS
2 /* $Header: IGSNI27B.pls 120.1 2005/06/28 06:09:32 appldev ship $ */
3
4
5 l_rowid VARCHAR2(25);
6 old_references IGS_PE_PERS_NOTE%RowType;
7 new_references IGS_PE_PERS_NOTE%RowType;
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2, -- DEFAULT NULL,
12 x_person_id IN NUMBER, -- DEFAULT NULL,
13 x_reference_number IN NUMBER, -- DEFAULT NULL,
14 x_pe_note_type IN VARCHAR2,-- DEFAULT NULL,
15 X_START_DATE IN DATE,
16 X_END_DATE IN DATE,
17 x_creation_date IN DATE, -- DEFAULT NULL,
18 x_created_by IN NUMBER, -- DEFAULT NULL,
19 x_last_update_date IN DATE, -- DEFAULT NULL,
20 x_last_updated_by IN NUMBER, -- DEFAULT NULL,
21 x_last_update_login IN NUMBER -- DEFAULT NULL
22 ) AS
23
24 CURSOR cur_old_ref_values IS
25 SELECT *
26 FROM IGS_PE_PERS_NOTE
27 WHERE rowid = x_rowid;
28
29 BEGIN
30 l_rowid := x_rowid;
31
32 -- Code for setting the Old and New Reference Values.
33 -- Populate Old Values.
34 Open cur_old_ref_values;
35 Fetch cur_old_ref_values INTO old_references;
36 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
37 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38 IGS_GE_MSG_STACK.ADD;
39 Close cur_old_ref_values;
40 App_Exception.Raise_Exception;
41 Return;
42 END IF;
43 Close cur_old_ref_values;
44
45 -- Populate New Values.
46 new_references.person_id := x_person_id;
47 new_references.reference_number := x_reference_number;
48 new_references.pe_note_type := x_pe_note_type;
49 new_references.start_date := X_START_DATE;
50 new_references.end_date := X_END_DATE;
51 IF (p_action = 'UPDATE') THEN
52 new_references.creation_date := old_references.creation_date;
53 new_references.created_by := old_references.created_by;
54 ELSE
55 new_references.creation_date := x_creation_date;
56 new_references.created_by := x_created_by;
57 END IF;
58 new_references.last_update_date := x_last_update_date;
59 new_references.last_updated_by := x_last_updated_by;
60 new_references.last_update_login := x_last_update_login;
61 END Set_Column_Values;
62
63
64 PROCEDURE Check_Constraints (
65 Column_Name IN VARCHAR2,
66 Column_Value IN VARCHAR2
67 )
68 AS
69 BEGIN
70 IF column_name is null then
71 NULL;
72 ELSIF upper(Column_name) = 'PE_NOTE_TYPE' then
73 new_references.pe_note_type:= column_value;
74 END IF;
75
76 IF upper(column_name) = 'PE_NOTE_TYPE' OR
77 column_name is null Then
78 IF new_references.pe_note_type <>UPPER(new_references.pe_note_type)Then
79 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
80 IGS_GE_MSG_STACK.ADD;
81 App_Exception.Raise_Exception;
82 END IF;
83 END IF;
84
85 END Check_Constraints;
86
87 PROCEDURE Check_Parent_Existance AS
88 BEGIN
89
90 IF (((old_references.reference_number = new_references.reference_number)) OR
91 ((new_references.reference_number IS NULL))) THEN
92 NULL;
93 ELSE
94 IF NOT IGS_GE_NOTE_PKG.Get_PK_For_Validation (
95 new_references.reference_number ) THEN
96 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
97 IGS_GE_MSG_STACK.ADD;
98 App_Exception.Raise_Exception;
99 END IF;
100
101 END IF;
102
103 IF (((old_references.person_id = new_references.person_id)) OR
104 ((new_references.person_id IS NULL))) THEN
105 NULL;
106 ELSE
107 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
108 new_references.person_id ) 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 IF;
115
116 IF (((old_references.pe_note_type = new_references.pe_note_type)) OR
117 ((new_references.pe_note_type IS NULL))) THEN
118 NULL;
119 ELSE
120
121
122 -- kumma 2608360, replace the IGS_PE_NOTE_TYPE_PKG.Get_PK_For_Validation with the following
123 IF NOT IGS_LOOKUPS_view_Pkg.Get_PK_For_Validation (
124 'PE_NOTE_TYPE',
125 new_references.pe_note_type
126 ) THEN
127 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
128 IGS_GE_MSG_STACK.ADD;
129 App_Exception.Raise_Exception;
130 END IF;
131 END IF;
132
133 END Check_Parent_Existance;
134
135 FUNCTION Get_PK_For_Validation (
136 x_person_id IN NUMBER,
137 x_reference_number IN NUMBER
138 ) RETURN BOOLEAN AS
139
140 CURSOR cur_rowid IS
141 SELECT rowid
142 FROM IGS_PE_PERS_NOTE
143 WHERE person_id = x_person_id
144 AND reference_number = x_reference_number
145 FOR UPDATE NOWAIT;
146
147 lv_rowid cur_rowid%RowType;
148
149 BEGIN
150
151 Open cur_rowid;
152 Fetch cur_rowid INTO lv_rowid;
153 IF (cur_rowid%FOUND) THEN
154 Close cur_rowid;
155 Return (TRUE);
156 ELSE
157 Close cur_rowid;
158 Return (FALSE);
159 END IF;
160
161 END Get_PK_For_Validation;
162
163 PROCEDURE GET_FK_IGS_GE_NOTE (
164 x_reference_number IN NUMBER
165 ) AS
166
167 CURSOR cur_rowid IS
168 SELECT rowid
169 FROM IGS_PE_PERS_NOTE
170 WHERE reference_number = x_reference_number ;
171
172 lv_rowid cur_rowid%RowType;
173
174 BEGIN
175
176 Open cur_rowid;
177 Fetch cur_rowid INTO lv_rowid;
178 IF (cur_rowid%FOUND) THEN
179 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PN_NOTE_FK');
180 IGS_GE_MSG_STACK.ADD;
181 Close cur_rowid;
182 App_Exception.Raise_Exception;
183 Return;
184 END IF;
185 Close cur_rowid;
186
187 END GET_FK_IGS_GE_NOTE;
188
189 PROCEDURE GET_FK_IGS_PE_PERSON (
190 x_person_id IN NUMBER
191 ) AS
192
193 CURSOR cur_rowid IS
194 SELECT rowid
195 FROM IGS_PE_PERS_NOTE
196 WHERE person_id = x_person_id ;
197
198 lv_rowid cur_rowid%RowType;
199
200 BEGIN
201
202 Open cur_rowid;
203 Fetch cur_rowid INTO lv_rowid;
204 IF (cur_rowid%FOUND) THEN
205 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PN_PE_FK');
206 IGS_GE_MSG_STACK.ADD;
207 Close cur_rowid;
208 App_Exception.Raise_Exception;
209 Return;
210 END IF;
211 Close cur_rowid;
212
213 END GET_FK_IGS_PE_PERSON;
214
215 PROCEDURE GET_FK_IGS_PE_NOTE_TYPE (
216 x_pe_note_type IN VARCHAR2
217 ) AS
218
219 CURSOR cur_rowid IS
220 SELECT rowid
221 FROM IGS_PE_PERS_NOTE
222 WHERE pe_note_type = x_pe_note_type ;
223
224 lv_rowid cur_rowid%RowType;
225
226 BEGIN
227
228 Open cur_rowid;
229 Fetch cur_rowid INTO lv_rowid;
230 IF (cur_rowid%FOUND) THEN
231 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PN_PNT_FK');
232 IGS_GE_MSG_STACK.ADD;
233 Close cur_rowid;
234 App_Exception.Raise_Exception;
235 Return;
236 END IF;
237 Close cur_rowid;
238
239 END GET_FK_IGS_PE_NOTE_TYPE;
240
241 PROCEDURE Before_DML (
242 p_action IN VARCHAR2,
243 x_rowid IN VARCHAR2,
244 x_person_id IN NUMBER,
245 x_reference_number IN NUMBER,
246 x_pe_note_type IN VARCHAR2,
247 X_START_DATE IN DATE,
248 X_END_DATE IN DATE,
249 x_creation_date IN DATE,
250 x_created_by IN NUMBER,
251 x_last_update_date IN DATE,
252 x_last_updated_by IN NUMBER,
253 x_last_update_login IN NUMBER
254
255 ) AS
256 BEGIN
257 Set_Column_Values (
258 p_action,
259 x_rowid,
260 x_person_id,
261 x_reference_number,
262 x_pe_note_type,
263 X_START_DATE,
264 X_END_DATE,
265 x_creation_date,
266 x_created_by,
267 x_last_update_date,
268 x_last_updated_by,
269 x_last_update_login
270 );
271
272 IF (p_action = 'INSERT') THEN
273 -- Call all the procedures related to Before Insert.
274
275 IF Get_PK_For_Validation (
276 new_references.person_id,
277 new_references.reference_number )THEN
278 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
279 IGS_GE_MSG_STACK.ADD;
280 App_Exception.Raise_Exception;
281 END IF;
282 Check_Constraints; -- if procedure present
283 Check_Parent_Existance; -- if procedure present
284 ELSIF (p_action = 'UPDATE') THEN
285 -- Call all the procedures related to Before Update.
286
287 Check_Constraints; -- if procedure present
288 Check_Parent_Existance; -- if procedure present
289 ELSIF (p_action = 'DELETE') THEN
290 -- Call all the procedures related to Before Delete.
291 NULL;
292 ELSIF (p_action = 'VALIDATE_INSERT') THEN
293 IF Get_PK_For_Validation (
294 new_references.person_id,
295 new_references.reference_number ) THEN
296 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
297 IGS_GE_MSG_STACK.ADD;
298 App_Exception.Raise_Exception;
299 END IF;
300
301 Check_Constraints; -- if procedure present
302 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
303 Check_Constraints; -- if procedure present
304 ELSIF (p_action = 'VALIDATE_DELETE') THEN
305 NULL;
306 END IF;
307 END Before_DML;
308
309 PROCEDURE After_DML (
310 p_action IN VARCHAR2,
311 x_rowid IN VARCHAR2
312 ) AS
313 BEGIN
314
315 l_rowid := x_rowid;
316
317 IF (p_action = 'INSERT') THEN
318 -- Call all the procedures related to After Insert.
319 Null;
320 ELSIF (p_action = 'UPDATE') THEN
321 -- Call all the procedures related to After Update.
322 Null;
323 ELSIF (p_action = 'DELETE') THEN
324 -- Call all the procedures related to After Delete.
325 Null;
326 END IF;
327
328 END After_DML;
329
330 procedure INSERT_ROW (
331 X_ROWID in out NOCOPY VARCHAR2,
332 X_PERSON_ID in NUMBER,
333 X_REFERENCE_NUMBER in NUMBER,
334 X_PE_NOTE_TYPE in VARCHAR2,
335 X_START_DATE IN DATE,
336 X_END_DATE IN DATE,
337 X_MODE in VARCHAR2
338 ) AS
339 cursor C is select ROWID from IGS_PE_PERS_NOTE
340 where PERSON_ID = X_PERSON_ID
341 and REFERENCE_NUMBER = X_REFERENCE_NUMBER;
342 X_LAST_UPDATE_DATE DATE;
343 X_LAST_UPDATED_BY NUMBER;
344 X_LAST_UPDATE_LOGIN NUMBER;
345 begin
346 X_LAST_UPDATE_DATE := SYSDATE;
347 if(X_MODE = 'I') then
348 X_LAST_UPDATED_BY := 1;
349 X_LAST_UPDATE_LOGIN := 0;
350 elsif (X_MODE IN ('R', 'S')) then
351 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
352 if X_LAST_UPDATED_BY is NULL then
353 X_LAST_UPDATED_BY := -1;
354 end if;
355 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
356 if X_LAST_UPDATE_LOGIN is NULL then
357 X_LAST_UPDATE_LOGIN := -1;
358 end if;
359 else
360 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
361 IGS_GE_MSG_STACK.ADD;
362 app_exception.raise_exception;
363 end if;
364
365 Before_DML(
366 p_action=>'INSERT',
367 x_rowid=>X_ROWID,
368 x_pe_note_type=>X_PE_NOTE_TYPE,
369 x_person_id=>X_PERSON_ID,
370 x_start_date => X_START_DATE,
371 x_end_date => X_END_DATE,
372 x_reference_number=>X_REFERENCE_NUMBER,
373 x_creation_date=>X_LAST_UPDATE_DATE,
374 x_created_by=>X_LAST_UPDATED_BY,
375 x_last_update_date=>X_LAST_UPDATE_DATE,
376 x_last_updated_by=>X_LAST_UPDATED_BY,
377 x_last_update_login=>X_LAST_UPDATE_LOGIN
378 );
379
380 IF (x_mode = 'S') THEN
381 igs_sc_gen_001.set_ctx('R');
382 END IF;
383 insert into IGS_PE_PERS_NOTE (
384 PERSON_ID,
385 REFERENCE_NUMBER,
386 PE_NOTE_TYPE,
387 START_DATE,
388 END_DATE,
389 CREATION_DATE,
390 CREATED_BY,
391 LAST_UPDATE_DATE,
392 LAST_UPDATED_BY,
393 LAST_UPDATE_LOGIN
394 ) values (
395 NEW_REFERENCES.PERSON_ID,
396 NEW_REFERENCES.REFERENCE_NUMBER,
397 NEW_REFERENCES.PE_NOTE_TYPE,
398 NEW_REFERENCES.START_DATE,
399 NEW_REFERENCES.END_DATE,
400 X_LAST_UPDATE_DATE,
401 X_LAST_UPDATED_BY,
402 X_LAST_UPDATE_DATE,
403 X_LAST_UPDATED_BY,
404 X_LAST_UPDATE_LOGIN
405 );
406 IF (x_mode = 'S') THEN
407 igs_sc_gen_001.unset_ctx('R');
408 END IF;
409
410
411 open c;
412 fetch c into X_ROWID;
413 if (c%notfound) then
414 close c;
415 raise no_data_found;
416 end if;
417 close c;
418
419 After_DML(
420 p_action => 'INSERT',
421 x_rowid => X_ROWID
422 );
423
424 EXCEPTION
425 WHEN OTHERS THEN
426 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
427 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
428 fnd_message.set_token ('ERR_CD', SQLCODE);
429 igs_ge_msg_stack.add;
430 igs_sc_gen_001.unset_ctx('R');
431 app_exception.raise_exception;
432 ELSE
433 igs_sc_gen_001.unset_ctx('R');
434 RAISE;
435 END IF;
436
437 end INSERT_ROW;
438
439 procedure LOCK_ROW (
440 X_ROWID in VARCHAR2,
441 X_PERSON_ID in NUMBER,
442 X_REFERENCE_NUMBER in NUMBER,
443 X_PE_NOTE_TYPE in VARCHAR2,
444 X_START_DATE IN DATE,
445 X_END_DATE IN DATE
446 ) AS
447 cursor c1 is select
448 PE_NOTE_TYPE,
449 START_DATE,
450 END_DATE
451 from IGS_PE_PERS_NOTE
452 where ROWID = X_ROWID
453 for update nowait;
454 tlinfo c1%rowtype;
455
456 begin
457 open c1;
458 fetch c1 into tlinfo;
459 if (c1%notfound) then
460 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
461
462 close c1;
463 App_Exception.Raise_Exception;
464 return;
465 end if;
466 close c1;
467
468 if ( (tlinfo.PE_NOTE_TYPE = X_PE_NOTE_TYPE)
469 AND ((tlinfo.start_date = X_START_DATE) OR ((tlinfo.start_date IS NULL) AND (X_START_DATE IS NULL)))
470 AND ((tlinfo.end_date = X_END_DATE) OR ((tlinfo.end_date IS NULL) AND (X_END_DATE IS NULL)))
471 ) then
472 NULL;
473 else
474 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
475 app_exception.raise_exception;
476 end if;
477 return;
478 end LOCK_ROW;
479
480 procedure UPDATE_ROW (
481 X_ROWID in VARCHAR2,
482 X_PERSON_ID in NUMBER,
483 X_REFERENCE_NUMBER in NUMBER,
484 X_PE_NOTE_TYPE in VARCHAR2,
485 X_START_DATE IN DATE,
486 X_END_DATE IN DATE,
487 X_MODE in VARCHAR2
488 ) AS
489 X_LAST_UPDATE_DATE DATE;
490 X_LAST_UPDATED_BY NUMBER;
491 X_LAST_UPDATE_LOGIN NUMBER;
492 begin
493 X_LAST_UPDATE_DATE := SYSDATE;
494 if(X_MODE = 'I') then
495 X_LAST_UPDATED_BY := 1;
496 X_LAST_UPDATE_LOGIN := 0;
497 elsif (X_MODE IN ('R', 'S')) then
498 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
499 if X_LAST_UPDATED_BY is NULL then
500 X_LAST_UPDATED_BY := -1;
501 end if;
502 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
503 if X_LAST_UPDATE_LOGIN is NULL then
504 X_LAST_UPDATE_LOGIN := -1;
505 end if;
506 else
507 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
508 IGS_GE_MSG_STACK.ADD;
509 app_exception.raise_exception;
510 end if;
511
512 Before_DML(
513 p_action=>'UPDATE',
514 x_rowid=>X_ROWID,
515 x_pe_note_type=>X_PE_NOTE_TYPE,
516 x_start_date => X_START_DATE,
517 x_end_date => X_END_DATE,
518 x_person_id=>X_PERSON_ID,
519 x_reference_number=>X_REFERENCE_NUMBER,
520 x_creation_date=>X_LAST_UPDATE_DATE,
521 x_created_by=>X_LAST_UPDATED_BY,
522 x_last_update_date=>X_LAST_UPDATE_DATE,
523 x_last_updated_by=>X_LAST_UPDATED_BY,
524 x_last_update_login=>X_LAST_UPDATE_LOGIN
525 );
526 IF (x_mode = 'S') THEN
527 igs_sc_gen_001.set_ctx('R');
528 END IF;
529 update IGS_PE_PERS_NOTE set
530 PE_NOTE_TYPE = NEW_REFERENCES.PE_NOTE_TYPE,
531 START_DATE = NEW_REFERENCES.START_DATE,
532 END_DATE = NEW_REFERENCES.END_DATE,
533 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
534 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
535 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
536 where ROWID = X_ROWID
537 ;
538 if (sql%notfound) then
539 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
540 igs_ge_msg_stack.add;
541 igs_sc_gen_001.unset_ctx('R');
542 app_exception.raise_exception;
543 end if;
544 IF (x_mode = 'S') THEN
545 igs_sc_gen_001.unset_ctx('R');
546 END IF;
547
548
549 After_DML(
550 p_action => 'UPDATE',
551 x_rowid => X_ROWID
552 );
553
554 EXCEPTION
555 WHEN OTHERS THEN
556 IF (SQLCODE = (-28115)) THEN
557 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
558 fnd_message.set_token ('ERR_CD', SQLCODE);
559 igs_ge_msg_stack.add;
560 igs_sc_gen_001.unset_ctx('R');
561 app_exception.raise_exception;
562 ELSE
563 igs_sc_gen_001.unset_ctx('R');
564 RAISE;
565 END IF;
566
567 end UPDATE_ROW;
568
569 procedure ADD_ROW (
570 X_ROWID in out NOCOPY VARCHAR2,
571 X_PERSON_ID in NUMBER,
572 X_REFERENCE_NUMBER in NUMBER,
573 X_PE_NOTE_TYPE in VARCHAR2,
574 X_START_DATE IN DATE,
575 X_END_DATE IN DATE,
576 X_MODE in VARCHAR2
577 ) AS
578 cursor c1 is select rowid from IGS_PE_PERS_NOTE
579 where PERSON_ID = X_PERSON_ID
580 and REFERENCE_NUMBER = X_REFERENCE_NUMBER
581 ;
582
583 begin
584 open c1;
585 fetch c1 into X_ROWID;
586 if (c1%notfound) then
587 close c1;
588 INSERT_ROW (
589 X_ROWID,
590 X_PERSON_ID,
591 X_REFERENCE_NUMBER,
592 X_PE_NOTE_TYPE,
593 X_START_DATE ,
594 X_END_DATE,
595 X_MODE);
596 return;
597 end if;
598 close c1;
599 UPDATE_ROW (
600 X_ROWID,
601 X_PERSON_ID,
602 X_REFERENCE_NUMBER,
603 X_PE_NOTE_TYPE,
604 X_START_DATE ,
605 X_END_DATE,
606 X_MODE);
607 end ADD_ROW;
608
609 procedure DELETE_ROW (
610 X_ROWID in VARCHAR2,
611 x_mode IN VARCHAR2
612 ) AS
613 begin
614
615 Before_DML(
616 p_action => 'DELETE',
617 x_rowid => X_ROWID
618 );
619
620 IF (x_mode = 'S') THEN
621 igs_sc_gen_001.set_ctx('R');
622 END IF;
623 delete from IGS_PE_PERS_NOTE
624 where ROWID = X_ROWID;
625 if (sql%notfound) then
626 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
627 igs_ge_msg_stack.add;
628 igs_sc_gen_001.unset_ctx('R');
629 app_exception.raise_exception;
630 end if;
631 IF (x_mode = 'S') THEN
632 igs_sc_gen_001.unset_ctx('R');
633 END IF;
634
635
636 After_DML(
637 p_action => 'DELETE',
638 x_rowid => X_ROWID
639 );
640
641 end DELETE_ROW;
642
643 end IGS_PE_PERS_NOTE_PKG;