1 package body IGS_PE_PERS_UNT_EXCL_PKG AS
2 /* $Header: IGSNI33B.pls 120.1 2005/09/08 14:23:26 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PE_PERS_UNT_EXCL%RowType;
6 new_references IGS_PE_PERS_UNT_EXCL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_encumbrance_type IN VARCHAR2 DEFAULT NULL,
13 x_pen_start_dt IN DATE DEFAULT NULL,
14 x_s_encmb_effect_type IN VARCHAR2 DEFAULT NULL,
15 x_pee_start_dt IN DATE DEFAULT NULL,
16 x_pee_sequence_number IN NUMBER DEFAULT NULL,
17 x_unit_cd IN VARCHAR2 DEFAULT NULL,
18 x_pue_start_dt IN DATE DEFAULT NULL,
19 x_expiry_dt IN DATE DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL
25 ) AS
26
27 CURSOR cur_old_ref_values IS
28 SELECT *
29 FROM IGS_PE_PERS_UNT_EXCL
30 WHERE rowid = x_rowid;
31
32 BEGIN
33
34 l_rowid := x_rowid;
35
36 -- Code for setting the Old and New Reference Values.
37 -- Populate Old Values.
38 Open cur_old_ref_values;
39 Fetch cur_old_ref_values INTO old_references;
40 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN( 'INSERT','VALIDATE_INSERT')) THEN
41 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
42 IGS_GE_MSG_STACK.ADD;
43 Close cur_old_ref_values;
44 App_Exception.Raise_Exception;
45 Return;
46 END IF;
47 Close cur_old_ref_values;
48
49 -- Populate New Values.
50 new_references.person_id := x_person_id;
51 new_references.encumbrance_type := x_encumbrance_type;
52 new_references.pen_start_dt := x_pen_start_dt;
53 new_references.s_encmb_effect_type := x_s_encmb_effect_type;
54 new_references.pee_start_dt := x_pee_start_dt;
55 new_references.pee_sequence_number := x_pee_sequence_number;
56 new_references.unit_cd := x_unit_cd;
57 new_references.pue_start_dt := x_pue_start_dt;
58 new_references.expiry_dt := x_expiry_dt;
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66 new_references.last_update_date := x_last_update_date;
67 new_references.last_updated_by := x_last_updated_by;
68 new_references.last_update_login := x_last_update_login;
69
70 END Set_Column_Values;
71
72 PROCEDURE BeforeRowInsertUpdate1(
73 p_inserting IN BOOLEAN DEFAULT FALSE,
74 p_updating IN BOOLEAN DEFAULT FALSE,
75 p_deleting IN BOOLEAN DEFAULT FALSE
76 ) AS
77 v_message_name varchar2(30);
78 BEGIN
79 -- Validate that start date is not less than the current date.
80 IF (new_references.pue_start_dt IS NOT NULL) AND
81 (p_inserting OR (p_updating AND
82 (NVL(old_references.pue_start_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
83 <> new_references.pue_start_dt)))
84 THEN
85 IF IGS_EN_VAL_PCE.enrp_val_encmb_dt (
86 new_references.pue_start_dt,
87 v_message_name ) = FALSE THEN
88 Fnd_Message.Set_Name('IGS', v_message_name);
89 IGS_GE_MSG_STACK.ADD;
90 App_Exception.Raise_Exception;
91 END IF;
92 END IF;
93 -- Validate that start date is not less than the parent IGS_PE_PERSON
94 -- Encumbrance Effect start date.
95 IF p_inserting THEN
96 IF IGS_EN_VAL_PCE.enrp_val_encmb_dts (
97 new_references.pee_start_dt,
98 new_references.pue_start_dt,
99 v_message_name ) = FALSE THEN
100 Fnd_Message.Set_Name('IGS', v_message_name);
101 IGS_GE_MSG_STACK.ADD;
102 App_Exception.Raise_Exception;
103 END IF;
104 END IF;
105 -- Validate that if expiry date is specified, then expiry date is not
106 -- less than the start date.
107 IF (new_references.expiry_dt IS NOT NULL) AND
108 (p_inserting OR (p_updating AND
109 (NVL(old_references.expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
110 <> new_references.expiry_dt)))
111 THEN
112 IF IGS_EN_VAL_PCE.enrp_val_strt_exp_dt (
113 new_references.pue_start_dt,
114 new_references.expiry_dt,
115 v_message_name ) = FALSE THEN
116 Fnd_Message.Set_Name('IGS', v_message_name);
117 IGS_GE_MSG_STACK.ADD;
118 App_Exception.Raise_Exception;
119 END IF;
120 IF IGS_EN_VAL_PCE.enrp_val_encmb_dt (
121 new_references.expiry_dt,
122 v_message_name ) = FALSE THEN
123 Fnd_Message.Set_Name('IGS', v_message_name);
124 IGS_GE_MSG_STACK.ADD;
125 App_Exception.Raise_Exception;
126 END IF;
127 END IF;
128 -- Validate that records for this table can be created for the encumbrance
129 -- effect type.
130 IF p_inserting THEN
131 IF IGS_EN_VAL_PCE.enrp_val_pee_table (
132 new_references.s_encmb_effect_type,
133 'IGS_PE_PERS_UNT_EXCL',
134 v_message_name ) = FALSE THEN
135 Fnd_Message.Set_Name('IGS', v_message_name);
136 IGS_GE_MSG_STACK.ADD;
137 App_Exception.Raise_Exception;
138 END IF;
139 END IF;
140 -- Validate if IGS_PE_PERSON is enrolled in the IGS_PS_UNIT which is to be excluded. If so,
141 -- raise an error as this is not allowable.
142 IF p_inserting THEN
143 IF IGS_EN_VAL_PUE.enrp_val_pue_unit (
144 new_references.person_id,
145 new_references.unit_cd,
146 null,
147 v_message_name ) = FALSE THEN
148 Fnd_Message.Set_Name('IGS', v_message_name);
149 IGS_GE_MSG_STACK.ADD;
150 App_Exception.Raise_Exception;
151 END IF;
152 END IF;
153
154
155 END BeforeRowInsertUpdate1;
156
157 -- Trigger description :-
158 -- "OSS_TST".trg_pue_ar_iu
159 -- AFTER INSERT OR UPDATE
160 -- ON IGS_PE_PERS_UNT_EXCL
161 -- FOR EACH ROW
162
163 PROCEDURE AfterRowInsertUpdate2(
164 p_inserting IN BOOLEAN DEFAULT FALSE,
165 p_updating IN BOOLEAN DEFAULT FALSE,
166 p_deleting IN BOOLEAN DEFAULT FALSE
167 ) AS
168 v_message_name varchar2(30);
169 v_rowid_saved BOOLEAN := FALSE;
170 BEGIN
171 -- Validate for open ended IGS_PE_PERSON IGS_PS_UNIT exclusion records.
172 IF new_references.expiry_dt IS NULL THEN
173 -- Validate for open ended IGS_PE_PERS_UNT_EXCL records.
174 IF new_references.expiry_dt IS NULL THEN
175 IF IGS_EN_VAL_PUE.enrp_val_pue_open (
176 new_references.person_id,
177 new_references.encumbrance_type,
178 new_references.pen_start_dt,
179 new_references.s_encmb_effect_type,
180 new_references.pee_start_dt,
181 new_references.unit_cd,
182 new_references.pue_start_dt,
183 v_message_name) = FALSE THEN
184 Fnd_Message.Set_Name('IGS', v_message_name);
185 IGS_GE_MSG_STACK.ADD;
186 App_Exception.Raise_Exception;
187 END IF;
188 END IF;
189
190 END IF;
191
192
193 END AfterRowInsertUpdate2;
194
195 -- Trigger description :-
196 -- "OSS_TST".trg_pue_as_iu
197 -- AFTER INSERT OR UPDATE
198 -- ON IGS_PE_PERS_UNT_EXCL
199
200
201
202 PROCEDURE Check_Constraints (
203 Column_Name IN VARCHAR2 DEFAULT NULL,
204 Column_Value IN VARCHAR2 DEFAULT NULL
205 )
206 AS
207 BEGIN
208 IF column_name is null then
209 NULL;
210
211 ELSIF upper(Column_name) = 'ENCUMBRANCE_TYPE' then
212 new_references.encumbrance_type := column_value;
213 ELSIF upper(Column_name) = 'S_ENCMB_EFFECT_TYPE' then
214 new_references.s_encmb_effect_type := column_value;
215 ELSIF upper(Column_name) = 'UNIT_CD' then
216 new_references.unit_cd:= column_value;
217 END IF;
218 IF upper(column_name) = 'ENCUMBRANCE_TYPE' OR
219 column_name is null Then
220 IF new_references.encumbrance_type <>UPPER(new_references.encumbrance_type)Then
221 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
222 IGS_GE_MSG_STACK.ADD;
223 App_Exception.Raise_Exception;
224 END IF;
225 END IF;
226 IF upper(column_name) = 'S_ENCMB_EFFECT_TYPE' OR
227 column_name is null Then
228 IF new_references.s_encmb_effect_type <>UPPER(new_references.s_encmb_effect_type)Then
229 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
230 IGS_GE_MSG_STACK.ADD;
231 App_Exception.Raise_Exception;
232 END IF;
233 END IF;
234 IF upper(column_name) = 'UNIT_CD' OR
235 column_name is null Then
236 IF new_references.unit_cd <>UPPER(new_references.unit_cd)Then
237 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
238 IGS_GE_MSG_STACK.ADD;
239 App_Exception.Raise_Exception;
240 END IF;
241 END IF;
242
243 END Check_Constraints;
244
245 PROCEDURE Check_Parent_Existance AS
246 BEGIN
247
248 IF (((old_references.person_id = new_references.person_id) AND
249 (old_references.encumbrance_type = new_references.encumbrance_type) AND
250 (old_references.pen_start_dt = new_references.pen_start_dt) AND
251 (old_references.s_encmb_effect_type = new_references.s_encmb_effect_type) AND
252 (old_references.pee_start_dt = new_references.pee_start_dt) AND
253 (old_references.pee_sequence_number = new_references.pee_sequence_number)) OR
254 ((new_references.person_id IS NULL) OR
255 (new_references.encumbrance_type IS NULL) OR
256 (new_references.pen_start_dt IS NULL) OR
257 (new_references.s_encmb_effect_type IS NULL) OR
258 (new_references.pee_start_dt IS NULL) OR
259 (new_references.pee_sequence_number IS NULL))) THEN
260 NULL;
261 ELSE
262 IF NOT IGS_PE_PERSENC_EFFCT_PKG.Get_PK_For_Validation (
263 new_references.person_id,
264 new_references.encumbrance_type,
265 new_references.pen_start_dt,
266 new_references.s_encmb_effect_type,
267 new_references.pee_start_dt,
268 new_references.pee_sequence_number ) THEN
269 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
270 IGS_GE_MSG_STACK.ADD;
271 App_Exception.Raise_Exception;
272 END IF;
273 END IF;
274
275 IF (((old_references.unit_cd = new_references.unit_cd)) OR
276 ((new_references.unit_cd IS NULL))) THEN
277 NULL;
278 ELSE
279 IF NOT IGS_PS_UNIT_PKG.Get_PK_For_Validation (
280 new_references.unit_cd) THEN
281 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
282 IGS_GE_MSG_STACK.ADD;
283 App_Exception.Raise_Exception;
284 END IF;
285 END IF;
286
287 END Check_Parent_Existance;
288
289 FUNCTION Get_PK_For_Validation (
290 x_person_id IN NUMBER,
291 x_encumbrance_type IN VARCHAR2,
292 x_pen_start_dt IN DATE,
293 x_s_encmb_effect_type IN VARCHAR2,
294 x_pee_start_dt IN DATE,
295 x_pee_sequence_number IN NUMBER,
296 x_unit_cd IN VARCHAR2,
297 x_pue_start_dt IN DATE
298 ) RETURN BOOLEAN AS
299
300 CURSOR cur_rowid IS
301 SELECT rowid
302 FROM IGS_PE_PERS_UNT_EXCL
303 WHERE person_id = x_person_id
304 AND encumbrance_type = x_encumbrance_type
305 AND pen_start_dt = x_pen_start_dt
306 AND s_encmb_effect_type = x_s_encmb_effect_type
307 AND pee_start_dt = x_pee_start_dt
308 AND pee_sequence_number = x_pee_sequence_number
309 AND unit_cd = x_unit_cd
310 AND pue_start_dt = x_pue_start_dt
311 FOR UPDATE NOWAIT;
312
313 lv_rowid cur_rowid%RowType;
314
315 BEGIN
316
317 Open cur_rowid;
318 Fetch cur_rowid INTO lv_rowid;
319 IF (cur_rowid%FOUND) THEN
320 Close cur_rowid;
321 Return (TRUE);
322 ELSE
323 Close cur_rowid;
324 Return (FALSE);
325 END IF;
326
327 END Get_PK_For_Validation;
328
329 PROCEDURE GET_FK_IGS_PE_PERSENC_EFFCT (
330 x_person_id IN NUMBER,
331 x_encumbrance_type IN VARCHAR2,
332 x_pen_start_dt IN DATE,
333 x_s_encmb_effect_type IN VARCHAR2,
334 x_pee_start_dt IN DATE,
335 x_pee_sequence_number IN NUMBER
336 ) AS
337
338 CURSOR cur_rowid IS
339 SELECT rowid
340 FROM IGS_PE_PERS_UNT_EXCL
341 WHERE person_id = x_person_id
342 AND encumbrance_type = x_encumbrance_type
343 AND pen_start_dt = x_pen_start_dt
344 AND s_encmb_effect_type = x_s_encmb_effect_type
345 AND pee_start_dt = x_pee_start_dt
346 AND pee_sequence_number = x_pee_sequence_number ;
347
348 lv_rowid cur_rowid%RowType;
349
350 BEGIN
351
352 Open cur_rowid;
353 Fetch cur_rowid INTO lv_rowid;
354 IF (cur_rowid%FOUND) THEN
355 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PUE_PEE_FK');
356 IGS_GE_MSG_STACK.ADD;
357 Close cur_rowid;
358 App_Exception.Raise_Exception;
359 Return;
360 END IF;
361 Close cur_rowid;
362
363 END GET_FK_IGS_PE_PERSENC_EFFCT;
364
365 PROCEDURE GET_FK_IGS_PS_UNIT (
366 x_unit_cd IN VARCHAR2
367 ) AS
368
369 CURSOR cur_rowid IS
370 SELECT rowid
371 FROM IGS_PE_PERS_UNT_EXCL
372 WHERE unit_cd = x_unit_cd ;
373
374 lv_rowid cur_rowid%RowType;
375
376 BEGIN
377
378 Open cur_rowid;
379 Fetch cur_rowid INTO lv_rowid;
380 IF (cur_rowid%FOUND) THEN
381 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PUE_UN_FK');
382 IGS_GE_MSG_STACK.ADD;
383 Close cur_rowid;
384 App_Exception.Raise_Exception;
385 Return;
386 END IF;
387 Close cur_rowid;
388
389 END GET_FK_IGS_PS_UNIT;
390
391 PROCEDURE Before_DML (
392 p_action IN VARCHAR2,
393 x_rowid IN VARCHAR2 DEFAULT NULL,
394 x_person_id IN NUMBER DEFAULT NULL,
395 x_encumbrance_type IN VARCHAR2 DEFAULT NULL,
396 x_pen_start_dt IN DATE DEFAULT NULL,
397 x_s_encmb_effect_type IN VARCHAR2 DEFAULT NULL,
398 x_pee_start_dt IN DATE DEFAULT NULL,
399 x_pee_sequence_number IN NUMBER DEFAULT NULL,
400 x_unit_cd IN VARCHAR2 DEFAULT NULL,
401 x_pue_start_dt IN DATE DEFAULT NULL,
402 x_expiry_dt IN DATE DEFAULT NULL,
403 x_creation_date IN DATE DEFAULT NULL,
404 x_created_by IN NUMBER DEFAULT NULL,
405 x_last_update_date IN DATE DEFAULT NULL,
406 x_last_updated_by IN NUMBER DEFAULT NULL,
407 x_last_update_login IN NUMBER DEFAULT NULL
408 ) AS
409 BEGIN
410
411 Set_Column_Values (
412 p_action,
413 x_rowid,
414 x_person_id,
415 x_encumbrance_type,
416 x_pen_start_dt,
417 x_s_encmb_effect_type,
418 x_pee_start_dt,
419 x_pee_sequence_number,
420 x_unit_cd,
421 x_pue_start_dt,
422 x_expiry_dt,
423 x_creation_date,
424 x_created_by,
425 x_last_update_date,
426 x_last_updated_by,
427 x_last_update_login
428 );
429
430 IF (p_action = 'INSERT') THEN
431 -- Call all the procedures related to Before Insert.
432 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
433 IF Get_PK_For_Validation (
434 NEW_REFERENCES.person_id,
435 NEW_REFERENCES.encumbrance_type,
436 NEW_REFERENCES.pen_start_dt ,
437 NEW_REFERENCES.s_encmb_effect_type ,
438 NEW_REFERENCES.pee_start_dt ,
439 NEW_REFERENCES.pee_sequence_number,
440 NEW_REFERENCES.unit_cd ,
441 NEW_REFERENCES.pue_start_dt
442 ) THEN
443 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
444 IGS_GE_MSG_STACK.ADD;
445 App_Exception.Raise_Exception;
446 END IF;
447
448 Check_Constraints; -- if procedure present
449 Check_Parent_Existance; -- if procedure present
450 ELSIF (p_action = 'UPDATE') THEN
451 -- Call all the procedures related to Before Update.
452 BeforeRowInsertUpdate1 ( p_updating => TRUE );
453
454 Check_Constraints; -- if procedure present
455 Check_Parent_Existance; -- if procedure present
456
457 ELSIF (p_action = 'DELETE') THEN
458 -- Call all the procedures related to Before Delete.
459
460 NULL;
461 ELSIF (p_action = 'VALIDATE_INSERT') THEN
462 IF Get_PK_For_Validation (
463 NEW_REFERENCES.person_id,
464 NEW_REFERENCES.encumbrance_type,
465 NEW_REFERENCES.pen_start_dt ,
466 NEW_REFERENCES.s_encmb_effect_type ,
467 NEW_REFERENCES.pee_start_dt ,
468 NEW_REFERENCES.pee_sequence_number,
469 NEW_REFERENCES.unit_cd ,
470 NEW_REFERENCES.pue_start_dt) THEN
471 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
472 IGS_GE_MSG_STACK.ADD;
473 App_Exception.Raise_Exception;
474 END IF;
475
476 Check_Constraints; -- if procedure present
477 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
478
479 Check_Constraints; -- if procedure present
480
481 ELSIF (p_action = 'VALIDATE_DELETE') THEN
482 NULL;
483 END IF;
484
485 END Before_DML;
486
487 PROCEDURE After_DML (
488 p_action IN VARCHAR2,
489 x_rowid IN VARCHAR2
490 ) AS
491 BEGIN
492
493 l_rowid := x_rowid;
494
495 IF (p_action = 'INSERT') THEN
496 -- Call all the procedures related to After Insert.
497 AfterRowInsertUpdate2 ( p_inserting => TRUE );
498
499 ELSIF (p_action = 'UPDATE') THEN
500 -- Call all the procedures related to After Update.
501 AfterRowInsertUpdate2 ( p_updating => TRUE );
502 ELSIF (p_action = 'DELETE') THEN
503 -- Call all the procedures related to After Delete.
504 Null;
505 END IF;
506
507 END After_DML;
508
509
510 procedure INSERT_ROW (
511 X_ROWID in out NOCOPY VARCHAR2,
512 X_PERSON_ID in NUMBER,
513 X_ENCUMBRANCE_TYPE in VARCHAR2,
514 X_PEN_START_DT in DATE,
515 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
516 X_PEE_START_DT in DATE,
517 X_PEE_SEQUENCE_NUMBER in NUMBER,
518 X_UNIT_CD in VARCHAR2,
519 X_PUE_START_DT in DATE,
520 X_EXPIRY_DT in DATE,
521 X_MODE in VARCHAR2 default 'R'
522 ) AS
523 cursor C is select ROWID from IGS_PE_PERS_UNT_EXCL
524 where PERSON_ID = X_PERSON_ID
525 and ENCUMBRANCE_TYPE = X_ENCUMBRANCE_TYPE
526 and PEN_START_DT = X_PEN_START_DT
527 and S_ENCMB_EFFECT_TYPE = X_S_ENCMB_EFFECT_TYPE
528 and PEE_START_DT = X_PEE_START_DT
529 and PEE_SEQUENCE_NUMBER = X_PEE_SEQUENCE_NUMBER
530 and UNIT_CD = X_UNIT_CD
531 and PUE_START_DT = X_PUE_START_DT;
532 X_LAST_UPDATE_DATE DATE;
533 X_LAST_UPDATED_BY NUMBER;
534 X_LAST_UPDATE_LOGIN NUMBER;
535 begin
536 X_LAST_UPDATE_DATE := SYSDATE;
537 if(X_MODE = 'I') then
538 X_LAST_UPDATED_BY := 1;
539 X_LAST_UPDATE_LOGIN := 0;
540 elsif (X_MODE = 'R') then
541 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
542 if X_LAST_UPDATED_BY is NULL then
543 X_LAST_UPDATED_BY := -1;
544 end if;
545 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
546 if X_LAST_UPDATE_LOGIN is NULL then
547 X_LAST_UPDATE_LOGIN := -1;
548 end if;
549 else
550 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
551 IGS_GE_MSG_STACK.ADD;
552 app_exception.raise_exception;
553 end if;
554 Before_DML(
555 p_action=>'INSERT',
556 x_rowid=>X_ROWID,
557 x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
558 x_expiry_dt=>X_EXPIRY_DT,
559 x_pee_sequence_number=>X_PEE_SEQUENCE_NUMBER,
560 x_pee_start_dt=>X_PEE_START_DT,
561 x_pen_start_dt=>X_PEN_START_DT,
562 x_person_id=>X_PERSON_ID,
563 x_pue_start_dt=>X_PUE_START_DT,
564 x_s_encmb_effect_type=>X_S_ENCMB_EFFECT_TYPE,
565 x_unit_cd=>X_UNIT_CD,
566 x_creation_date=>X_LAST_UPDATE_DATE,
567 x_created_by=>X_LAST_UPDATED_BY,
568 x_last_update_date=>X_LAST_UPDATE_DATE,
569 x_last_updated_by=>X_LAST_UPDATED_BY,
570 x_last_update_login=>X_LAST_UPDATE_LOGIN
571 );
572
573 insert into IGS_PE_PERS_UNT_EXCL (
574 PERSON_ID,
575 ENCUMBRANCE_TYPE,
576 PEN_START_DT,
577 S_ENCMB_EFFECT_TYPE,
578 PEE_START_DT,
579 PEE_SEQUENCE_NUMBER,
580 UNIT_CD,
581 PUE_START_DT,
582 EXPIRY_DT,
583 CREATION_DATE,
584 CREATED_BY,
585 LAST_UPDATE_DATE,
586 LAST_UPDATED_BY,
587 LAST_UPDATE_LOGIN
588 ) values (
589 NEW_REFERENCES.PERSON_ID,
590 NEW_REFERENCES.ENCUMBRANCE_TYPE,
591 NEW_REFERENCES.PEN_START_DT,
592 NEW_REFERENCES.S_ENCMB_EFFECT_TYPE,
593 NEW_REFERENCES.PEE_START_DT,
594 NEW_REFERENCES.PEE_SEQUENCE_NUMBER,
595 NEW_REFERENCES.UNIT_CD,
596 NEW_REFERENCES.PUE_START_DT,
597 NEW_REFERENCES.EXPIRY_DT,
598 X_LAST_UPDATE_DATE,
599 X_LAST_UPDATED_BY,
600 X_LAST_UPDATE_DATE,
601 X_LAST_UPDATED_BY,
602 X_LAST_UPDATE_LOGIN
603 );
604
605 open c;
606 fetch c into X_ROWID;
607 if (c%notfound) then
608 close c;
609 raise no_data_found;
610 end if;
611 close c;
612 After_DML(
613 p_action => 'INSERT',
614 x_rowid => X_ROWID
615 );
616 end INSERT_ROW;
617
618 procedure LOCK_ROW (
619 X_ROWID in VARCHAR2,
620 X_PERSON_ID in NUMBER,
621 X_ENCUMBRANCE_TYPE in VARCHAR2,
622 X_PEN_START_DT in DATE,
623 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
624 X_PEE_START_DT in DATE,
625 X_PEE_SEQUENCE_NUMBER in NUMBER,
626 X_UNIT_CD in VARCHAR2,
627 X_PUE_START_DT in DATE,
628 X_EXPIRY_DT in DATE
629 ) AS
630 cursor c1 is select
631 EXPIRY_DT
632 from IGS_PE_PERS_UNT_EXCL
633 where ROWID = X_ROWID
634 for update nowait;
635 tlinfo c1%rowtype;
636
637 begin
638 open c1;
639 fetch c1 into tlinfo;
640 if (c1%notfound) then
641 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
642
643 close c1;
644 App_Exception.Raise_Exception;
645 return;
646 end if;
647 close c1;
648
649 if ( ((tlinfo.EXPIRY_DT = X_EXPIRY_DT)
650 OR ((tlinfo.EXPIRY_DT is null)
651 AND (X_EXPIRY_DT is null)))
652 ) then
653 null;
654 else
655 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
656 app_exception.raise_exception;
657 end if;
658 return;
659 end LOCK_ROW;
660
661 procedure UPDATE_ROW (
662 X_ROWID in VARCHAR2,
663 X_PERSON_ID in NUMBER,
664 X_ENCUMBRANCE_TYPE in VARCHAR2,
665 X_PEN_START_DT in DATE,
666 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
667 X_PEE_START_DT in DATE,
668 X_PEE_SEQUENCE_NUMBER in NUMBER,
669 X_UNIT_CD in VARCHAR2,
670 X_PUE_START_DT in DATE,
671 X_EXPIRY_DT in DATE,
672 X_MODE in VARCHAR2 default 'R'
673 ) AS
674 X_LAST_UPDATE_DATE DATE;
675 X_LAST_UPDATED_BY NUMBER;
676 X_LAST_UPDATE_LOGIN NUMBER;
677 begin
678 X_LAST_UPDATE_DATE := SYSDATE;
679 if(X_MODE = 'I') then
680 X_LAST_UPDATED_BY := 1;
681 X_LAST_UPDATE_LOGIN := 0;
682 elsif (X_MODE = 'R') then
683 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
684 if X_LAST_UPDATED_BY is NULL then
685 X_LAST_UPDATED_BY := -1;
686 end if;
687 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
688 if X_LAST_UPDATE_LOGIN is NULL then
689 X_LAST_UPDATE_LOGIN := -1;
690 end if;
691 else
692 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
693 IGS_GE_MSG_STACK.ADD;
694 app_exception.raise_exception;
695 end if;
696 Before_DML(
697 p_action=>'UPDATE',
698 x_rowid=>X_ROWID,
699 x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
700 x_expiry_dt=>X_EXPIRY_DT,
701 x_pee_sequence_number=>X_PEE_SEQUENCE_NUMBER,
702 x_pee_start_dt=>X_PEE_START_DT,
703 x_pen_start_dt=>X_PEN_START_DT,
704 x_person_id=>X_PERSON_ID,
705 x_pue_start_dt=>X_PUE_START_DT,
706 x_s_encmb_effect_type=>X_S_ENCMB_EFFECT_TYPE,
707 x_unit_cd=>X_UNIT_CD,
708 x_creation_date=>X_LAST_UPDATE_DATE,
709 x_created_by=>X_LAST_UPDATED_BY,
710 x_last_update_date=>X_LAST_UPDATE_DATE,
711 x_last_updated_by=>X_LAST_UPDATED_BY,
712 x_last_update_login=>X_LAST_UPDATE_LOGIN
713 );
714
715 update IGS_PE_PERS_UNT_EXCL set
716 EXPIRY_DT = NEW_REFERENCES.EXPIRY_DT,
717 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
718 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
719 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
720 where ROWID = X_ROWID
721 ;
722 if (sql%notfound) then
723 raise no_data_found;
724 end if;
725
726 After_DML(
727 p_action => 'UPDATE',
728 x_rowid => X_ROWID
729 );
730 end UPDATE_ROW;
731
732 procedure ADD_ROW (
733 X_ROWID in out NOCOPY VARCHAR2,
734 X_PERSON_ID in NUMBER,
735 X_ENCUMBRANCE_TYPE in VARCHAR2,
736 X_PEN_START_DT in DATE,
737 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
738 X_PEE_START_DT in DATE,
739 X_PEE_SEQUENCE_NUMBER in NUMBER,
740 X_UNIT_CD in VARCHAR2,
741 X_PUE_START_DT in DATE,
742 X_EXPIRY_DT in DATE,
743 X_MODE in VARCHAR2 default 'R'
744 ) AS
745 cursor c1 is select rowid from IGS_PE_PERS_UNT_EXCL
746 where PERSON_ID = X_PERSON_ID
747 and ENCUMBRANCE_TYPE = X_ENCUMBRANCE_TYPE
748 and PEN_START_DT = X_PEN_START_DT
749 and S_ENCMB_EFFECT_TYPE = X_S_ENCMB_EFFECT_TYPE
750 and PEE_START_DT = X_PEE_START_DT
751 and PEE_SEQUENCE_NUMBER = X_PEE_SEQUENCE_NUMBER
752 and UNIT_CD = X_UNIT_CD
753 and PUE_START_DT = X_PUE_START_DT
754 ;
755
756 begin
757 open c1;
758 fetch c1 into X_ROWID;
759 if (c1%notfound) then
760 close c1;
761 INSERT_ROW (
762 X_ROWID,
763 X_PERSON_ID,
764 X_ENCUMBRANCE_TYPE,
765 X_PEN_START_DT,
766 X_S_ENCMB_EFFECT_TYPE,
767 X_PEE_START_DT,
768 X_PEE_SEQUENCE_NUMBER,
769 X_UNIT_CD,
770 X_PUE_START_DT,
771 X_EXPIRY_DT,
772 X_MODE);
773 return;
774 end if;
775 close c1;
776 UPDATE_ROW (
777 X_ROWID,
778 X_PERSON_ID,
779 X_ENCUMBRANCE_TYPE,
780 X_PEN_START_DT,
781 X_S_ENCMB_EFFECT_TYPE,
782 X_PEE_START_DT,
783 X_PEE_SEQUENCE_NUMBER,
784 X_UNIT_CD,
785 X_PUE_START_DT,
786 X_EXPIRY_DT,
787 X_MODE);
788 end ADD_ROW;
789
790 procedure DELETE_ROW (
791 X_ROWID in VARCHAR2
792 ) AS
793 begin
794 Before_DML(
795 p_action => 'DELETE',
796 x_rowid => X_ROWID
797 );
798 delete from IGS_PE_PERS_UNT_EXCL
799 where ROWID = X_ROWID;
800 if (sql%notfound) then
801 raise no_data_found;
802 end if;
803 After_DML(
804 p_action => 'DELETE',
805 x_rowid => X_ROWID
806 );
807 end DELETE_ROW;
808
809 end IGS_PE_PERS_UNT_EXCL_PKG;
[Home] [Help]