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