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