[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_MILESTONE_PKG
Source
1 package body IGS_PR_MILESTONE_PKG AS
2 /* $Header: IGSQI01B.pls 120.0 2005/07/05 11:51:29 appldev noship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_re_val_mil.genp_val_sdtt_sess
7 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
8 -------------------------------------------------------------------------------------------
9 l_rowid VARCHAR2(25);
10 old_references IGS_PR_MILESTONE_ALL%RowType;
11 new_references IGS_PR_MILESTONE_ALL%RowType;
12 PROCEDURE Set_Column_Values (
13 p_action IN VARCHAR2,
14 x_rowid IN VARCHAR2 DEFAULT NULL,
15 x_person_id IN NUMBER DEFAULT NULL,
16 x_ca_sequence_number IN NUMBER DEFAULT NULL,
17 x_sequence_number IN NUMBER DEFAULT NULL,
18 x_milestone_type IN VARCHAR2 DEFAULT NULL,
19 x_milestone_status IN VARCHAR2 DEFAULT NULL,
20 x_due_dt IN DATE DEFAULT NULL,
21 x_description IN VARCHAR2 DEFAULT NULL,
22 x_actual_reached_dt IN DATE DEFAULT NULL,
23 x_preced_sequence_number IN NUMBER DEFAULT NULL,
24 x_ovrd_ntfctn_imminent_days IN NUMBER DEFAULT NULL,
25 x_ovrd_ntfctn_reminder_days IN NUMBER DEFAULT NULL,
26 x_ovrd_ntfctn_re_reminder_days IN NUMBER DEFAULT NULL,
27 x_comments IN VARCHAR2 DEFAULT NULL,
28 x_creation_date IN DATE DEFAULT NULL,
29 x_created_by IN NUMBER DEFAULT NULL,
30 x_last_update_date IN DATE DEFAULT NULL,
31 x_last_updated_by IN NUMBER DEFAULT NULL,
32 x_last_update_login IN NUMBER DEFAULT NULL,
33 x_org_id IN NUMBER DEFAULT NULL
34 ) AS
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM IGS_PR_MILESTONE_ALL
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 Open cur_old_ref_values;
48 Fetch cur_old_ref_values INTO old_references;
49 IF (cur_old_ref_values%NOTFOUND) AND (p_action not in ('INSERT','VALIDATE_INSERT')) THEN
50 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
51 IGS_GE_MSG_STACK.ADD;
52 Close cur_old_ref_values;
53 App_Exception.Raise_Exception;
54 Return;
55 END IF;
56 Close cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.person_id := x_person_id;
60 new_references.ca_sequence_number := x_ca_sequence_number;
61 new_references.sequence_number := x_sequence_number;
62 new_references.milestone_type := x_milestone_type;
63 new_references.milestone_status := x_milestone_status;
64 new_references.due_dt := x_due_dt;
65 new_references.description := x_description;
66 new_references.actual_reached_dt := x_actual_reached_dt;
67 new_references.preced_sequence_number := x_preced_sequence_number;
68 new_references.ovrd_ntfctn_imminent_days := x_ovrd_ntfctn_imminent_days;
69 new_references.ovrd_ntfctn_reminder_days := x_ovrd_ntfctn_reminder_days;
70 new_references.ovrd_ntfctn_re_reminder_days := x_ovrd_ntfctn_re_reminder_days;
71 new_references.comments := x_comments;
72 IF (p_action = 'UPDATE') THEN
73 new_references.creation_date := old_references.creation_date;
74 new_references.created_by := old_references.created_by;
75 ELSE
76 new_references.creation_date := x_creation_date;
77 new_references.created_by := x_created_by;
78 END IF;
79 new_references.last_update_date := x_last_update_date;
80 new_references.last_updated_by := x_last_updated_by;
81 new_references.last_update_login := x_last_update_login;
82 new_references.org_id := x_org_id;
83 END Set_Column_Values;
84
85 -- Trigger description :-
86 -- "OSS_TST".trg_mil_br_iud
87 -- BEFORE INSERT OR DELETE OR UPDATE
88 -- ON IGS_PR_MILESTONE_ALL
89 -- FOR EACH ROW
90
91 PROCEDURE BeforeRowInsertUpdateDelete1(
92 p_inserting IN BOOLEAN DEFAULT FALSE,
93 p_updating IN BOOLEAN DEFAULT FALSE,
94 p_deleting IN BOOLEAN DEFAULT FALSE
95 ) AS
96 v_message_name varchar2(30);
97 BEGIN
98 -- Turn off trigger validation when performing insert of IGS_RE_CANDIDATURE details
99 -- as a result of IGS_PS_COURSE transfer
100 IF igs_as_val_suaap.genp_val_sdtt_sess('ENRP_INS_CA_TRNSFR') THEN
101 IF p_inserting OR
102 ( p_updating AND
103 old_references.milestone_type <> new_references.milestone_type ) THEN
104 IF IGS_RE_VAL_MIL.resp_val_mil_mty( new_references.milestone_type,
105 v_message_name) = FALSE THEN
106 Fnd_Message.Set_Name('IGS',v_message_name);
107 IGS_GE_MSG_STACK.ADD;
108 App_Exception.Raise_Exception;
109 END IF;
110 END IF;
111 IF p_inserting OR
112 ( p_updating AND
113 NVL(old_references.actual_reached_dt,IGS_GE_DATE.IGSDATE('1900/01/01')) <>
114 NVL(new_references.actual_reached_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
115 IF IGS_RE_VAL_MIL.resp_val_mil_actual( new_references.milestone_status,
116 new_references.actual_reached_dt,
117 v_message_name) = FALSE THEN
118 Fnd_Message.Set_Name('IGS',v_message_name);
119 IGS_GE_MSG_STACK.ADD;
120 App_Exception.Raise_Exception;
121 END IF;
122 END IF;
123 IF p_inserting OR
124 ( p_updating AND
125 NVL(old_references.ovrd_ntfctn_imminent_days,-1) <>
126 NVL(new_references.ovrd_ntfctn_imminent_days,-1) OR
127 NVL(old_references.ovrd_ntfctn_reminder_days,-1) <>
128 NVL(new_references.ovrd_ntfctn_reminder_days,-1) OR
129 NVL(old_references.ovrd_ntfctn_re_reminder_days,-1) <>
130 NVL(new_references.ovrd_ntfctn_re_reminder_days,-1)) THEN
131 IF IGS_RE_VAL_MIL.resp_val_mil_days( new_references.milestone_type,
132 new_references.milestone_status,
133 new_references.due_dt,
134 old_references.ovrd_ntfctn_imminent_days,
135 new_references.ovrd_ntfctn_imminent_days,
136 old_references.ovrd_ntfctn_reminder_days,
137 new_references.ovrd_ntfctn_reminder_days,
138 old_references.ovrd_ntfctn_re_reminder_days,
139 new_references.ovrd_ntfctn_re_reminder_days,
140 v_message_name) = FALSE THEN
141 Fnd_Message.Set_Name('IGS',v_message_name);
142 IGS_GE_MSG_STACK.ADD;
143 App_Exception.Raise_Exception;
144 END IF;
145 END IF;
146 END IF;
147 IF p_deleting THEN
148 IF IGS_RE_VAL_MIL.resp_val_mil_del( old_references.person_id,
149 old_references.ca_sequence_number,
150 old_references.sequence_number,
151 old_references.milestone_status,
152 v_message_name) = FALSE THEN
153 Fnd_Message.Set_Name('IGS',v_message_name);
154 IGS_GE_MSG_STACK.ADD;
155 App_Exception.Raise_Exception;
156 END IF;
157 END IF;
158
159
160 END BeforeRowInsertUpdateDelete1;
161
162 -- Trigger description :-
163 -- "OSS_TST".trg_mil_ar_iud
164 -- AFTER INSERT OR DELETE OR UPDATE
165 -- ON IGS_PR_MILESTONE_ALL
166 -- FOR EACH ROW
167
168 PROCEDURE AfterRowInsertUpdateDelete2(
169 p_inserting IN BOOLEAN DEFAULT FALSE,
170 p_updating IN BOOLEAN DEFAULT FALSE,
171 p_deleting IN BOOLEAN DEFAULT FALSE
172 ) AS
173 v_message_name varchar2(30);
174 v_rowid_saved BOOLEAN := FALSE;
175 BEGIN
176 -- update of student IGS_PS_COURSE attempt after student IGS_PS_UNIT attempt is posted
177 -- to the database
178 IF p_updating OR p_deleting THEN
179 IGS_RE_GEN_003.RESP_INS_MIL_HIST(
180 old_references.person_id,
181 old_references.ca_sequence_number,
182 old_references.sequence_number,
183 old_references.milestone_type,
184 new_references.milestone_type,
185 old_references.milestone_status,
186 new_references.milestone_status,
187 old_references.due_dt,
188 new_references.due_dt,
189 old_references.description,
190 new_references.description,
191 old_references.actual_reached_dt,
192 new_references.actual_reached_dt,
193 old_references.preced_sequence_number,
194 new_references.preced_sequence_number,
195 old_references.ovrd_ntfctn_imminent_days,
196 new_references.ovrd_ntfctn_imminent_days,
197 old_references.ovrd_ntfctn_reminder_days,
198 new_references.ovrd_ntfctn_reminder_days,
199 old_references.ovrd_ntfctn_re_reminder_days,
200 new_references.ovrd_ntfctn_re_reminder_days,
201 old_references.comments,
202 new_references.comments,
203 old_references.last_updated_by,
204 NVL(new_references.last_updated_by,FND_GLOBAL.USER_ID),
205 old_references.last_update_date,
206 NVL(new_references.last_update_date,SYSDATE));
207 END IF;
208
209
210 -- The changes are done as per the Enrollments Notifications TD Bug # 3052429
211 -- Workflow is raised when
212 -- 1. New record is created
213 -- 2. The fields MILESTONE_TYPE, MILESTONE_STATUS, DUE_DT or ACTUAL_REACHED_DT is updated.
214 -- 3. Recored is deleted.
215
216 IF p_inserting OR (p_updating AND ( new_references.milestone_type <> old_references.milestone_type OR
217 new_references.milestone_status <> old_references.milestone_status OR
218 trunc(new_references.due_dt) <> trunc(old_references.due_dt) OR
219 ( new_references.actual_reached_dt IS NOT NULL AND old_references.actual_reached_dt IS NOT NULL
220 AND trunc(new_references.actual_reached_dt) <> trunc(old_references.actual_reached_dt)) OR
221 (new_references.actual_reached_dt IS NOT NULL AND old_references.actual_reached_dt IS NULL) OR
222 (new_references.actual_reached_dt IS NULL AND old_references.actual_reached_dt IS NOT NULL))) THEN
223
224
225 igs_re_workflow.milestone_event(
226 p_personid => new_references.person_id,
227 p_ca_seq_num => new_references.ca_sequence_number,
228 p_milestn_typ => new_references.milestone_type,
229 p_milestn_stat => new_references.milestone_status,
230 p_due_dt => new_references.due_dt,
231 p_dt_reached => new_references.actual_reached_dt,
232 p_deleted => 'FALSE'
233 );
234
235 ELSIF (p_deleting AND old_references.milestone_status = 'PLANNED' ) THEN
236
237
238 igs_re_workflow.milestone_event(
239 p_personid => old_references.person_id,
240 p_ca_seq_num => old_references.ca_sequence_number,
241 p_milestn_typ => old_references.milestone_type,
242 p_milestn_stat => old_references.milestone_status,
243 p_due_dt => old_references.due_dt,
244 p_dt_reached => old_references.actual_reached_dt,
245 p_deleted => 'TRUE'
246 );
247 END IF;
248
249 END AfterRowInsertUpdateDelete2;
250
251 -- Trigger description :-
252 -- "OSS_TST".trg_mil_as_iu
253 -- AFTER INSERT OR UPDATE
254 -- ON IGS_PR_MILESTONE_ALL
255
256 PROCEDURE AfterStmtInsertUpdate3(
257 p_inserting IN BOOLEAN DEFAULT FALSE,
258 p_updating IN BOOLEAN DEFAULT FALSE,
259 p_deleting IN BOOLEAN DEFAULT FALSE
260 ) AS
261 v_message_name varchar2(30);
262 BEGIN
263 -- Turn off trigger validation when performing insert of IGS_RE_CANDIDATURE details
264 -- as a result of IGS_PS_COURSE transfer
265 IF igs_as_val_suaap.genp_val_sdtt_sess('ENRP_INS_CA_TRNSFR') THEN
266
267 -- Validate preceeding details.
268 IF IGS_RE_VAL_MIL.resp_val_mil_prcd( new_references.person_id,
269 new_references.ca_sequence_number,
270 new_references.sequence_number,
271 new_references.due_dt,
272 new_references.preced_sequence_number,
273 v_message_name) = FALSE THEN
274 Fnd_Message.Set_Name('IGS',v_message_name);
275 IGS_GE_MSG_STACK.ADD;
276 App_Exception.Raise_Exception;
277 END IF;
278 -- Validate milestone status.
279 IF IGS_RE_VAL_MIL.resp_val_mil_mst(new_references.person_id,
280 new_references.ca_sequence_number,
281 new_references.preced_sequence_number,
282 old_references.milestone_status,
283 new_references.milestone_status,
284 old_references.due_dt,
285 new_references.due_dt,
286 'TRIGGER',
287 v_message_name) = FALSE THEN
288 Fnd_Message.Set_Name('IGS',v_message_name);
289 IGS_GE_MSG_STACK.ADD;
290 App_Exception.Raise_Exception;
291 END IF;
292 -- Validate milestone due date.
293 IF IGS_RE_VAL_MIL.resp_val_mil_due(new_references.person_id,
294 new_references.ca_sequence_number,
295 new_references.sequence_number,
296 old_references.milestone_status,
297 new_references.milestone_status,
298 old_references.due_dt,
299 new_references.due_dt,
300 v_message_name) = FALSE THEN
301 Fnd_Message.Set_Name('IGS',v_message_name);
302 IGS_GE_MSG_STACK.ADD;
303 App_Exception.Raise_Exception;
304 END IF;
305 END IF;
306
307 END AfterStmtInsertUpdate3;
308
309
310 PROCEDURE Check_Parent_Existance AS
311 BEGIN
312
313 IF (((old_references.person_id = new_references.person_id) AND
314 (old_references.ca_sequence_number = new_references.ca_sequence_number)) OR
315 ((new_references.person_id IS NULL) OR
316 (new_references.ca_sequence_number IS NULL))) THEN
317 NULL;
318 ELSE
319 IF NOT IGS_RE_CANDIDATURE_PKG.GET_PK_For_Validation (
320 new_references.person_id,
321 new_references.ca_sequence_number
322 ) THEN
323 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
324 IGS_GE_MSG_STACK.ADD;
325 App_Exception.Raise_Exception;
326 END IF;
327 END IF;
328
329 IF (((old_references.person_id = new_references.person_id) AND
330 (old_references.ca_sequence_number = new_references.ca_sequence_number) AND
331 (old_references.preced_sequence_number = new_references.preced_sequence_number)) OR
332 ((new_references.person_id IS NULL) OR
333 (new_references.ca_sequence_number IS NULL) OR
334 (new_references.preced_sequence_number IS NULL))) THEN
335 NULL;
336 ELSE
337 IF NOT IGS_PR_MILESTONE_PKG.Get_PK_For_Validation (
338 new_references.person_id,
339 new_references.ca_sequence_number,
340 new_references.preced_sequence_number
341 ) THEN
342 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
343 IGS_GE_MSG_STACK.ADD;
344 App_Exception.Raise_Exception;
345 END IF;
346 END IF;
347
348 IF (((old_references.milestone_status= new_references.milestone_status)) OR
349 ((new_references.milestone_status IS NULL))) THEN
350 NULL;
351 ELSE
352 IF NOT IGS_PR_MS_STAT_PKG.Get_PK_For_Validation (
353 new_references.milestone_status
354 ) THEN
355 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
356 IGS_GE_MSG_STACK.ADD;
357 App_Exception.Raise_Exception;
358 END IF;
359 END IF;
360
361 IF (((old_references.milestone_type = new_references.milestone_type)) OR
362 ((new_references.milestone_type IS NULL))) THEN
363 NULL;
364 ELSE
365 IF NOT IGS_PR_MILESTONE_TYP_PKG.Get_PK_For_Validation (
366 new_references.milestone_type
367 ) THEN
368 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
369 IGS_GE_MSG_STACK.ADD;
370 App_Exception.Raise_Exception;
371 END IF;
372 END IF;
373
374 END Check_Parent_Existance;
375
376 PROCEDURE Check_Child_Existance AS
377 BEGIN
378
379 IGS_PR_MILESTONE_PKG.GET_FK_IGS_PR_MILESTONE (
380 old_references.person_id,
381 old_references.ca_sequence_number,
382 old_references.sequence_number
383 );
384
385 END Check_Child_Existance;
386
387 FUNCTION Get_PK_For_Validation (
388 x_person_id IN NUMBER,
389 x_ca_sequence_number IN NUMBER,
390 x_sequence_number IN NUMBER
391 ) RETURN BOOLEAN AS
392
393 CURSOR cur_rowid IS
394 SELECT rowid
395 FROM IGS_PR_MILESTONE_ALL
396 WHERE person_id = x_person_id
397 AND ca_sequence_number = x_ca_sequence_number
398 AND sequence_number = x_sequence_number
399 FOR UPDATE NOWAIT;
400
401 lv_rowid cur_rowid%RowType;
402
403 BEGIN
404
405 Open cur_rowid;
406 Fetch cur_rowid INTO lv_rowid;
407 IF (cur_rowid%FOUND) THEN
408 Close Cur_rowid;
409 Return(TRUE);
410 ELSE
411 Close cur_rowid;
412 Return(FALSE);
413 END IF;
414 END Get_PK_For_Validation;
415
416 PROCEDURE GET_FK_IGS_RE_CANDIDATURE (
417 x_person_id IN NUMBER,
418 x_sequence_number IN NUMBER
419 ) AS
420
421 CURSOR cur_rowid IS
422 SELECT rowid
423 FROM IGS_PR_MILESTONE_ALL
424 WHERE person_id = x_person_id
425 AND ca_sequence_number = x_sequence_number ;
426
427 lv_rowid cur_rowid%RowType;
428
429 BEGIN
430
431 Open cur_rowid;
432 Fetch cur_rowid INTO lv_rowid;
433 IF (cur_rowid%FOUND) THEN
434 Fnd_Message.Set_Name ('IGS', 'IGS_PR_MIL_CA_FK');
435 IGS_GE_MSG_STACK.ADD;
436 App_Exception.Raise_Exception;
437 Close cur_rowid;
438 Return;
439 END IF;
440 Close cur_rowid;
441
442 END GET_FK_IGS_RE_CANDIDATURE;
443
444 PROCEDURE GET_FK_IGS_PR_MILESTONE (
445 x_person_id IN NUMBER,
446 x_ca_sequence_number IN NUMBER,
447 x_sequence_number IN NUMBER
448 ) AS
449
450 CURSOR cur_rowid IS
451 SELECT rowid
452 FROM IGS_PR_MILESTONE_ALL
453 WHERE person_id = x_person_id
454 AND ca_sequence_number = x_ca_sequence_number
455 AND preced_sequence_number = x_sequence_number ;
456
457 lv_rowid cur_rowid%RowType;
458
459 BEGIN
460
461 Open cur_rowid;
462 Fetch cur_rowid INTO lv_rowid;
463 IF (cur_rowid%FOUND) THEN
464 Fnd_Message.Set_Name ('IGS', 'IGS_PR_MIL_MIL_FK');
465 IGS_GE_MSG_STACK.ADD;
466 Close cur_rowid;
467 App_Exception.Raise_Exception;
468 Return;
469 END IF;
470 Close cur_rowid;
471
472 END GET_FK_IGS_PR_MILESTONE;
473
474 PROCEDURE GET_FK_IGS_PR_MS_STAT (
475 x_milestone_status IN VARCHAR2
476 ) AS
477
478 CURSOR cur_rowid IS
479 SELECT rowid
480 FROM IGS_PR_MILESTONE_ALL
481 WHERE milestone_status = x_milestone_status ;
482
483 lv_rowid cur_rowid%RowType;
484
485 BEGIN
486
487 Open cur_rowid;
488 Fetch cur_rowid INTO lv_rowid;
489 IF (cur_rowid%FOUND) THEN
490 Fnd_Message.Set_Name ('IGS', 'IGS_PR_MIL_MST_FK');
491 IGS_GE_MSG_STACK.ADD;
492 Close cur_rowid;
493 App_Exception.Raise_Exception;
494 Return;
495 END IF;
496 Close cur_rowid;
497
498 END GET_FK_IGS_PR_MS_STAT;
499
500 PROCEDURE GET_FK_IGS_PR_MILESTONE_TYPE (
501 x_milestone_type IN VARCHAR2
502 ) AS
503
504 CURSOR cur_rowid IS
505 SELECT rowid
506 FROM IGS_PR_MILESTONE_ALL
507 WHERE milestone_type = x_milestone_type ;
508
509 lv_rowid cur_rowid%RowType;
510
511 BEGIN
512
513 Open cur_rowid;
514 Fetch cur_rowid INTO lv_rowid;
515 IF (cur_rowid%FOUND) THEN
516 Fnd_Message.Set_Name ('IGS', 'IGS_PR_MIL_MTY_FK');
517 IGS_GE_MSG_STACK.ADD;
518 Close cur_rowid;
519 App_Exception.Raise_Exception;
520 Return;
521 END IF;
522 Close cur_rowid;
523
524 END GET_FK_IGS_PR_MILESTONE_TYPE;
525
526 PROCEDURE Before_DML (
527 p_action IN VARCHAR2,
528 x_rowid IN VARCHAR2 DEFAULT NULL,
529 x_person_id IN NUMBER DEFAULT NULL,
530 x_ca_sequence_number IN NUMBER DEFAULT NULL,
531 x_sequence_number IN NUMBER DEFAULT NULL,
532 x_milestone_type IN VARCHAR2 DEFAULT NULL,
533 x_milestone_status IN VARCHAR2 DEFAULT NULL,
534 x_due_dt IN DATE DEFAULT NULL,
535 x_description IN VARCHAR2 DEFAULT NULL,
536 x_actual_reached_dt IN DATE DEFAULT NULL,
537 x_preced_sequence_number IN NUMBER DEFAULT NULL,
538 x_ovrd_ntfctn_imminent_days IN NUMBER DEFAULT NULL,
539 x_ovrd_ntfctn_reminder_days IN NUMBER DEFAULT NULL,
540 x_ovrd_ntfctn_re_reminder_days IN NUMBER DEFAULT NULL,
541 x_comments IN VARCHAR2 DEFAULT NULL,
542 x_creation_date IN DATE DEFAULT NULL,
543 x_created_by IN NUMBER DEFAULT NULL,
544 x_last_update_date IN DATE DEFAULT NULL,
545 x_last_updated_by IN NUMBER DEFAULT NULL,
546 x_last_update_login IN NUMBER DEFAULT NULL,
547 x_org_id IN NUMBER DEFAULT NULL
548 ) AS
549 BEGIN
550
551 Set_Column_Values (
552 p_action,
553 x_rowid,
554 x_person_id,
555 x_ca_sequence_number,
556 x_sequence_number,
557 x_milestone_type,
558 x_milestone_status,
559 x_due_dt,
560 x_description,
561 x_actual_reached_dt,
562 x_preced_sequence_number,
563 x_ovrd_ntfctn_imminent_days,
564 x_ovrd_ntfctn_reminder_days,
565 x_ovrd_ntfctn_re_reminder_days,
566 x_comments,
567 x_creation_date,
568 x_created_by,
569 x_last_update_date,
570 x_last_updated_by,
571 x_last_update_login,
572 x_org_id
573 );
574
575 IF (p_action = 'INSERT') THEN
576 -- Call all the procedures related to Before Insert.
577 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
578 IF Get_PK_For_Validation (
579 new_references.person_id,
580 new_references.ca_sequence_number,
581 new_references.sequence_number
582 ) THEN
583 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
584 IGS_GE_MSG_STACK.ADD;
585 App_Exception.Raise_Exception;
586 END IF;
587 Check_Constraints;
588 Check_Parent_Existance;
589 ELSIF (p_action = 'UPDATE') THEN
590 -- Call all the procedures related to Before Update.
591 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
592 Check_Constraints;
593 Check_Parent_Existance;
594 ELSIF (p_action = 'DELETE') THEN
595 -- Call all the procedures related to Before Delete.
596 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
597 Check_Child_Existance;
598 ELSIF (p_action = 'VALIDATE_INSERT') THEN
599 IF Get_PK_For_Validation (
600 new_references.person_id,
601 new_references.ca_sequence_number,
602 new_references.sequence_number
603 ) THEN
604 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
605 IGS_GE_MSG_STACK.ADD;
606 App_Exception.Raise_Exception;
607 END IF;
608 Check_Constraints;
609 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
610 Check_Constraints;
611 ELSIF (p_action = 'VALIDATE_DELETE') THEN
612 Check_Child_Existance;
613 END IF;
614
615 END Before_DML;
616
617 PROCEDURE After_DML (
618 p_action IN VARCHAR2,
619 x_rowid IN VARCHAR2
620 ) AS
621 BEGIN
622
623 l_rowid := x_rowid;
624
625 IF (p_action = 'INSERT') THEN
626 -- Call all the procedures related to After Insert.
627 AfterRowInsertUpdateDelete2 ( p_inserting => TRUE );
628 AfterStmtInsertUpdate3 ( p_inserting => TRUE );
629 ELSIF (p_action = 'UPDATE') THEN
630 -- Call all the procedures related to After Update.
631 AfterRowInsertUpdateDelete2 ( p_updating => TRUE );
632 AfterStmtInsertUpdate3 ( p_updating => TRUE );
633 ELSIF (p_action = 'DELETE') THEN
634 -- Call all the procedures related to After Delete.
635 AfterRowInsertUpdateDelete2 ( p_deleting => TRUE );
636 END IF;
637
638 END After_DML;
639
640 procedure INSERT_ROW (
641 X_ROWID in out NOCOPY VARCHAR2,
642 X_PERSON_ID in NUMBER,
643 X_CA_SEQUENCE_NUMBER in NUMBER,
644 X_SEQUENCE_NUMBER in NUMBER,
645 X_MILESTONE_TYPE in VARCHAR2,
646 X_MILESTONE_STATUS in VARCHAR2,
647 X_DUE_DT in DATE,
648 X_DESCRIPTION in VARCHAR2,
649 X_ACTUAL_REACHED_DT in DATE,
650 X_PRECED_SEQUENCE_NUMBER in NUMBER,
651 X_OVRD_NTFCTN_IMMINENT_DAYS in NUMBER,
652 X_OVRD_NTFCTN_REMINDER_DAYS in NUMBER,
653 X_OVRD_NTFCTN_RE_REMINDER_DAYS in NUMBER,
654 X_COMMENTS in VARCHAR2,
655 X_MODE in VARCHAR2 default 'R',
656 X_ORG_ID in NUMBER
657 ) AS
658 cursor C is select ROWID from IGS_PR_MILESTONE_ALL
659 where PERSON_ID = X_PERSON_ID
660 and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
661 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
662 X_LAST_UPDATE_DATE DATE;
663 X_LAST_UPDATED_BY NUMBER;
664 X_LAST_UPDATE_LOGIN NUMBER;
665 begin
666 X_LAST_UPDATE_DATE := SYSDATE;
667 if(X_MODE = 'I') then
668 X_LAST_UPDATED_BY := 1;
669 X_LAST_UPDATE_LOGIN := 0;
670 elsif (X_MODE IN ('R', 'S')) then
671 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
672 if X_LAST_UPDATED_BY is NULL then
673 X_LAST_UPDATED_BY := -1;
674 end if;
675 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
676 if X_LAST_UPDATE_LOGIN is NULL then
677 X_LAST_UPDATE_LOGIN := -1;
678 end if;
679 else
680 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
681 IGS_GE_MSG_STACK.ADD;
682 app_exception.raise_exception;
683 end if;
684
685 Before_DML (
686 p_action => 'INSERT',
687 x_rowid => X_ROWID,
688 x_person_id => X_PERSON_ID,
689 x_ca_sequence_number =>x_ca_sequence_number,
690 x_sequence_number =>x_sequence_number ,
691 x_milestone_type =>x_milestone_type ,
692 x_milestone_status =>x_milestone_status ,
693 x_due_dt =>x_due_dt,
694 x_description =>x_description ,
695 x_actual_reached_dt =>x_actual_reached_dt ,
696 x_preced_sequence_number =>x_preced_sequence_number ,
697 x_ovrd_ntfctn_imminent_days => x_ovrd_ntfctn_imminent_days ,
698 x_ovrd_ntfctn_reminder_days =>x_ovrd_ntfctn_reminder_days ,
699 x_ovrd_ntfctn_re_reminder_days =>x_ovrd_ntfctn_re_reminder_days ,
700 x_comments =>x_comments ,
701 x_creation_date =>x_last_update_date ,
702 x_created_by =>x_last_updated_by ,
703 x_last_update_date =>x_last_update_date ,
704 x_last_updated_by =>x_last_updated_by ,
705 x_last_update_login =>x_last_update_login,
706 x_org_id=>igs_ge_gen_003.get_org_id
707 ) ;
708 IF (x_mode = 'S') THEN
709 igs_sc_gen_001.set_ctx('R');
710 END IF;
711 insert into IGS_PR_MILESTONE_ALL (
712 PERSON_ID,
713 CA_SEQUENCE_NUMBER,
714 SEQUENCE_NUMBER,
715 MILESTONE_TYPE,
716 MILESTONE_STATUS,
717 DUE_DT,
718 DESCRIPTION,
719 ACTUAL_REACHED_DT,
720 PRECED_SEQUENCE_NUMBER,
721 OVRD_NTFCTN_IMMINENT_DAYS,
722 OVRD_NTFCTN_REMINDER_DAYS,
723 OVRD_NTFCTN_RE_REMINDER_DAYS,
724 COMMENTS,
725 CREATION_DATE,
726 CREATED_BY,
727 LAST_UPDATE_DATE,
728 LAST_UPDATED_BY,
729 LAST_UPDATE_LOGIN,
730 ORG_ID
731 ) values (
732 NEW_REFERENCES.PERSON_ID,
733 NEW_REFERENCES.CA_SEQUENCE_NUMBER,
734 NEW_REFERENCES.SEQUENCE_NUMBER,
735 NEW_REFERENCES.MILESTONE_TYPE,
736 NEW_REFERENCES.MILESTONE_STATUS,
737 NEW_REFERENCES.DUE_DT,
738 NEW_REFERENCES.DESCRIPTION,
739 NEW_REFERENCES.ACTUAL_REACHED_DT,
740 NEW_REFERENCES.PRECED_SEQUENCE_NUMBER,
741 NEW_REFERENCES.OVRD_NTFCTN_IMMINENT_DAYS,
742 NEW_REFERENCES.OVRD_NTFCTN_REMINDER_DAYS,
743 NEW_REFERENCES.OVRD_NTFCTN_RE_REMINDER_DAYS,
744 NEW_REFERENCES.COMMENTS,
745 X_LAST_UPDATE_DATE,
746 X_LAST_UPDATED_BY,
747 X_LAST_UPDATE_DATE,
748 X_LAST_UPDATED_BY,
749 X_LAST_UPDATE_LOGIN,
750 NEW_REFERENCES.ORG_ID
751 );
752 IF (x_mode = 'S') THEN
753 igs_sc_gen_001.unset_ctx('R');
754 END IF;
755
756
757 open c;
758 fetch c into X_ROWID;
759 if (c%notfound) then
760 close c;
761 raise no_data_found;
762 end if;
763 close c;
764 After_DML (
765 p_action => 'INSERT',
766 x_rowid => X_ROWID
767 );
768 EXCEPTION
769 WHEN OTHERS THEN
770 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
771 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
772 fnd_message.set_token ('ERR_CD', SQLCODE);
773 igs_ge_msg_stack.add;
774 igs_sc_gen_001.unset_ctx('R');
775 app_exception.raise_exception;
776 ELSE
777 igs_sc_gen_001.unset_ctx('R');
778 RAISE;
779 END IF;
780
781 end INSERT_ROW;
782
783 procedure LOCK_ROW (
784 X_ROWID in VARCHAR2,
785 X_PERSON_ID in NUMBER,
786 X_CA_SEQUENCE_NUMBER in NUMBER,
787 X_SEQUENCE_NUMBER in NUMBER,
788 X_MILESTONE_TYPE in VARCHAR2,
789 X_MILESTONE_STATUS in VARCHAR2,
790 X_DUE_DT in DATE,
791 X_DESCRIPTION in VARCHAR2,
792 X_ACTUAL_REACHED_DT in DATE,
793 X_PRECED_SEQUENCE_NUMBER in NUMBER,
794 X_OVRD_NTFCTN_IMMINENT_DAYS in NUMBER,
795 X_OVRD_NTFCTN_REMINDER_DAYS in NUMBER,
796 X_OVRD_NTFCTN_RE_REMINDER_DAYS in NUMBER,
797 X_COMMENTS in VARCHAR2
798 ) AS
799 cursor c1 is select
800 MILESTONE_TYPE,
801 MILESTONE_STATUS,
802 DUE_DT,
803 DESCRIPTION,
804 ACTUAL_REACHED_DT,
805 PRECED_SEQUENCE_NUMBER,
806 OVRD_NTFCTN_IMMINENT_DAYS,
807 OVRD_NTFCTN_REMINDER_DAYS,
808 OVRD_NTFCTN_RE_REMINDER_DAYS,
809 COMMENTS
810 from IGS_PR_MILESTONE_ALL
811 where ROWID = X_ROWID for update nowait;
812 tlinfo c1%rowtype;
813
814 begin
815 open c1;
816 fetch c1 into tlinfo;
817 if (c1%notfound) then
818 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
819 IGS_GE_MSG_STACK.ADD;
820 close c1;
821 app_exception.raise_exception;
822 return;
823 end if;
824 close c1;
825
826 if ( (tlinfo.MILESTONE_TYPE = X_MILESTONE_TYPE)
827 AND (tlinfo.MILESTONE_STATUS = X_MILESTONE_STATUS)
828 AND (tlinfo.DUE_DT = X_DUE_DT)
829 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
830 OR ((tlinfo.DESCRIPTION is null)
831 AND (X_DESCRIPTION is null)))
832 AND ((tlinfo.ACTUAL_REACHED_DT = X_ACTUAL_REACHED_DT)
833 OR ((tlinfo.ACTUAL_REACHED_DT is null)
834 AND (X_ACTUAL_REACHED_DT is null)))
835 AND ((tlinfo.PRECED_SEQUENCE_NUMBER = X_PRECED_SEQUENCE_NUMBER)
836 OR ((tlinfo.PRECED_SEQUENCE_NUMBER is null)
837 AND (X_PRECED_SEQUENCE_NUMBER is null)))
838 AND ((tlinfo.OVRD_NTFCTN_IMMINENT_DAYS = X_OVRD_NTFCTN_IMMINENT_DAYS)
839 OR ((tlinfo.OVRD_NTFCTN_IMMINENT_DAYS is null)
840 AND (X_OVRD_NTFCTN_IMMINENT_DAYS is null)))
841 AND ((tlinfo.OVRD_NTFCTN_REMINDER_DAYS = X_OVRD_NTFCTN_REMINDER_DAYS)
842 OR ((tlinfo.OVRD_NTFCTN_REMINDER_DAYS is null)
843 AND (X_OVRD_NTFCTN_REMINDER_DAYS is null)))
844 AND ((tlinfo.OVRD_NTFCTN_RE_REMINDER_DAYS = X_OVRD_NTFCTN_RE_REMINDER_DAYS)
845 OR ((tlinfo.OVRD_NTFCTN_RE_REMINDER_DAYS is null)
846 AND (X_OVRD_NTFCTN_RE_REMINDER_DAYS is null)))
847 AND ((tlinfo.COMMENTS = X_COMMENTS)
848 OR ((tlinfo.COMMENTS is null)
849 AND (X_COMMENTS is null)))
850 ) then
851 null;
852 else
853 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
854 IGS_GE_MSG_STACK.ADD;
855 app_exception.raise_exception;
856 end if;
857 return;
858 end LOCK_ROW;
859
860 procedure UPDATE_ROW (
861 X_ROWID in VARCHAR2,
862 X_PERSON_ID in NUMBER,
863 X_CA_SEQUENCE_NUMBER in NUMBER,
864 X_SEQUENCE_NUMBER in NUMBER,
865 X_MILESTONE_TYPE in VARCHAR2,
866 X_MILESTONE_STATUS in VARCHAR2,
867 X_DUE_DT in DATE,
868 X_DESCRIPTION in VARCHAR2,
869 X_ACTUAL_REACHED_DT in DATE,
870 X_PRECED_SEQUENCE_NUMBER in NUMBER,
871 X_OVRD_NTFCTN_IMMINENT_DAYS in NUMBER,
872 X_OVRD_NTFCTN_REMINDER_DAYS in NUMBER,
873 X_OVRD_NTFCTN_RE_REMINDER_DAYS in NUMBER,
874 X_COMMENTS in VARCHAR2,
875 X_MODE in VARCHAR2 default 'R'
876 ) AS
877 X_LAST_UPDATE_DATE DATE;
878 X_LAST_UPDATED_BY NUMBER;
879 X_LAST_UPDATE_LOGIN NUMBER;
880 begin
881 X_LAST_UPDATE_DATE := SYSDATE;
882 if(X_MODE = 'I') then
883 X_LAST_UPDATED_BY := 1;
884 X_LAST_UPDATE_LOGIN := 0;
885 elsif (X_MODE IN ('R', 'S')) then
886 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
887 if X_LAST_UPDATED_BY is NULL then
888 X_LAST_UPDATED_BY := -1;
889 end if;
890 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
891 if X_LAST_UPDATE_LOGIN is NULL then
892 X_LAST_UPDATE_LOGIN := -1;
893 end if;
894 else
895 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
896 IGS_GE_MSG_STACK.ADD;
897 app_exception.raise_exception;
898 end if;
899 Before_DML (
900 p_action => 'UPDATE',
901 x_rowid => X_ROWID,
902 x_person_id => X_PERSON_ID,
903 x_ca_sequence_number =>x_ca_sequence_number,
904 x_sequence_number =>x_sequence_number ,
905 x_milestone_type =>x_milestone_type ,
906 x_milestone_status =>x_milestone_status ,
907 x_due_dt =>x_due_dt,
908 x_description =>x_description ,
909 x_actual_reached_dt =>x_actual_reached_dt ,
910 x_preced_sequence_number =>x_preced_sequence_number ,
911 x_ovrd_ntfctn_imminent_days => x_ovrd_ntfctn_imminent_days ,
912 x_ovrd_ntfctn_reminder_days =>x_ovrd_ntfctn_reminder_days ,
913 x_ovrd_ntfctn_re_reminder_days =>x_ovrd_ntfctn_re_reminder_days ,
914 x_comments =>x_comments ,
915 x_creation_date =>x_last_update_date ,
916 x_created_by =>x_last_updated_by ,
917 x_last_update_date =>x_last_update_date ,
918 x_last_updated_by =>x_last_updated_by ,
919 x_last_update_login =>x_last_update_login
920 ) ;
921
922
923 IF (x_mode = 'S') THEN
924 igs_sc_gen_001.set_ctx('R');
925 END IF;
926 update IGS_PR_MILESTONE_ALL set
927 MILESTONE_TYPE = NEW_REFERENCES.MILESTONE_TYPE,
928 MILESTONE_STATUS = NEW_REFERENCES.MILESTONE_STATUS,
929 DUE_DT = NEW_REFERENCES.DUE_DT,
930 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
931 ACTUAL_REACHED_DT = NEW_REFERENCES.ACTUAL_REACHED_DT,
932 PRECED_SEQUENCE_NUMBER = NEW_REFERENCES.PRECED_SEQUENCE_NUMBER,
933 OVRD_NTFCTN_IMMINENT_DAYS = NEW_REFERENCES.OVRD_NTFCTN_IMMINENT_DAYS,
934 OVRD_NTFCTN_REMINDER_DAYS = NEW_REFERENCES.OVRD_NTFCTN_REMINDER_DAYS,
935 OVRD_NTFCTN_RE_REMINDER_DAYS = NEW_REFERENCES.OVRD_NTFCTN_RE_REMINDER_DAYS,
936 COMMENTS = NEW_REFERENCES.COMMENTS,
937 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
938 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
939 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
940 where ROWID = X_ROWID;
941
942 if (sql%notfound) then
943 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
944 igs_ge_msg_stack.add;
945 igs_sc_gen_001.unset_ctx('R');
946 app_exception.raise_exception;
947 end if;
948 IF (x_mode = 'S') THEN
949 igs_sc_gen_001.unset_ctx('R');
950 END IF;
951
952
953 After_DML (
954 p_action => 'UPDATE',
955 x_rowid => X_ROWID
956 );
957 EXCEPTION
958 WHEN OTHERS THEN
959 IF (SQLCODE = (-28115)) THEN
960 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
961 fnd_message.set_token ('ERR_CD', SQLCODE);
962 igs_ge_msg_stack.add;
963 igs_sc_gen_001.unset_ctx('R');
964 app_exception.raise_exception;
965 ELSE
966 igs_sc_gen_001.unset_ctx('R');
967 RAISE;
968 END IF;
969
970 end UPDATE_ROW;
971
972 procedure ADD_ROW (
973 X_ROWID in out NOCOPY VARCHAR2,
974 X_PERSON_ID in NUMBER,
975 X_CA_SEQUENCE_NUMBER in NUMBER,
976 X_SEQUENCE_NUMBER in NUMBER,
977 X_MILESTONE_TYPE in VARCHAR2,
978 X_MILESTONE_STATUS in VARCHAR2,
979 X_DUE_DT in DATE,
980 X_DESCRIPTION in VARCHAR2,
981 X_ACTUAL_REACHED_DT in DATE,
982 X_PRECED_SEQUENCE_NUMBER in NUMBER,
983 X_OVRD_NTFCTN_IMMINENT_DAYS in NUMBER,
984 X_OVRD_NTFCTN_REMINDER_DAYS in NUMBER,
985 X_OVRD_NTFCTN_RE_REMINDER_DAYS in NUMBER,
986 X_COMMENTS in VARCHAR2,
987 X_MODE in VARCHAR2 default 'R',
988 X_ORG_ID IN NUMBER
989 ) AS
990 cursor c1 is select rowid from IGS_PR_MILESTONE_ALL
991 where PERSON_ID = X_PERSON_ID
992 and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
993 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
994 ;
995 begin
996 open c1;
997 fetch c1 into X_ROWID;
998 if (c1%notfound) then
999 close c1;
1000 INSERT_ROW (
1001 X_ROWID,
1002 X_PERSON_ID,
1003 X_CA_SEQUENCE_NUMBER,
1004 X_SEQUENCE_NUMBER,
1005 X_MILESTONE_TYPE,
1006 X_MILESTONE_STATUS,
1007 X_DUE_DT,
1008 X_DESCRIPTION,
1009 X_ACTUAL_REACHED_DT,
1010 X_PRECED_SEQUENCE_NUMBER,
1011 X_OVRD_NTFCTN_IMMINENT_DAYS,
1012 X_OVRD_NTFCTN_REMINDER_DAYS,
1013 X_OVRD_NTFCTN_RE_REMINDER_DAYS,
1014 X_COMMENTS,
1015 X_MODE,
1016 X_ORG_ID );
1017 return;
1018 end if;
1019 close c1;
1020 UPDATE_ROW (
1021 X_ROWID,
1022 X_PERSON_ID,
1023 X_CA_SEQUENCE_NUMBER,
1024 X_SEQUENCE_NUMBER,
1025 X_MILESTONE_TYPE,
1026 X_MILESTONE_STATUS,
1027 X_DUE_DT,
1028 X_DESCRIPTION,
1029 X_ACTUAL_REACHED_DT,
1030 X_PRECED_SEQUENCE_NUMBER,
1031 X_OVRD_NTFCTN_IMMINENT_DAYS,
1032 X_OVRD_NTFCTN_REMINDER_DAYS,
1033 X_OVRD_NTFCTN_RE_REMINDER_DAYS,
1034 X_COMMENTS,
1035 X_MODE
1036 );
1037 end ADD_ROW;
1038
1039 procedure DELETE_ROW (
1040 X_ROWID in VARCHAR2,
1041 x_mode IN VARCHAR2
1042 ) AS
1043 begin
1044 Before_DML (
1045 p_action=>'DELETE',
1046 x_rowid=>X_ROWID
1047 );
1048 IF (x_mode = 'S') THEN
1049 igs_sc_gen_001.set_ctx('R');
1050 END IF;
1051 delete from IGS_PR_MILESTONE_ALL
1052 where ROWID = X_ROWID;
1053 if (sql%notfound) then
1054 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1055 igs_ge_msg_stack.add;
1056 igs_sc_gen_001.unset_ctx('R');
1057 app_exception.raise_exception;
1058 end if;
1059 IF (x_mode = 'S') THEN
1060 igs_sc_gen_001.unset_ctx('R');
1061 END IF;
1062
1063 After_DML (
1064 p_action => 'DELETE',
1065 x_rowid => X_ROWID
1066 );
1067 end DELETE_ROW;
1068
1069 PROCEDURE Check_Constraints (
1070 Column_Name IN VARCHAR2 DEFAULT NULL,
1071 Column_Value IN VARCHAR2 DEFAULT NULL
1072 ) AS
1073 BEGIN
1074 IF column_name is null then
1075 NULL;
1076 ELSIF upper(Column_Name) = 'DESCRIPTION' then
1077 new_references.description := Column_Value;
1078 ELSIF upper(Column_Name) = 'MILESTONE_STATUS' then
1079 new_references.milestone_status := Column_Value;
1080 ELSIF upper(Column_Name) = 'MILESTONE_TYPE' then
1081 new_references.milestone_type := Column_Value;
1082 ELSIF upper(Column_Name) = 'CA_SEQUENCE_NUMBER' then
1083 new_references.ca_sequence_number := IGS_GE_NUMBER.to_num(Column_Value);
1084 ELSIF upper(Column_Name) = 'SEQUENCE_NUMBER' then
1085 new_references.sequence_number := IGS_GE_NUMBER.to_num(Column_Value);
1086 ELSIF upper(Column_Name) = 'PRECED_SEQUENCE_NUMBER' then
1087 new_references.preced_sequence_number := IGS_GE_NUMBER.to_num(Column_Value);
1088 ELSIF upper(Column_Name) = 'OVRD_NTFCTN_REMINDER_DAYS' then
1089 new_references.ovrd_ntfctn_reminder_days := IGS_GE_NUMBER.to_num(Column_Value);
1090 ELSIF upper(Column_Name) = 'OVRD_NTFCTN_RE_REMINDER_DAYS' then
1091 new_references.ovrd_ntfctn_re_reminder_days := IGS_GE_NUMBER.to_num(Column_Value);
1092 ELSIF upper(Column_Name) = 'OVRD_NTFCTN_IMMINENT_DAYS' then
1093 new_references.ovrd_ntfctn_imminent_days := IGS_GE_NUMBER.to_num(Column_Value);
1094 END IF;
1095
1096 IF upper(column_name) = 'MILESTONE_STATUS' OR
1097 column_name is NULL THEN
1098 IF new_references.milestone_status <> UPPER(new_references.milestone_status) THEN
1099 Fnd_Message.Set_Name('IGS','IGS_INVALID_VALUE');
1100 IGS_GE_MSG_STACK.ADD;
1101 App_Exception.Raise_Exception;
1102 END IF;
1103 END IF;
1104
1105
1106 IF upper(column_name) = 'CA_SEQUENCE_NUMBER' OR
1107 column_name is NULL THEN
1108 IF TO_NUMBER(new_references.ca_sequence_number) < 1 OR
1109 TO_NUMBER(new_references.ca_sequence_number) > 999999 THEN
1110 Fnd_Message.Set_Name('IGS','IGS_INVALID_VALUE');
1111 IGS_GE_MSG_STACK.ADD;
1112 App_Exception.Raise_Exception;
1113 END IF;
1114 END IF;
1115
1116
1117 IF upper(column_name) = 'SEQUENCE_NUMBER' OR
1118 column_name is NULL THEN
1119 IF TO_NUMBER(new_references.sequence_number) < 1 OR
1120 TO_NUMBER(new_references.sequence_number) > 999999 THEN
1121 Fnd_Message.Set_Name('IGS','IGS_INVALID_VALUE');
1122 IGS_GE_MSG_STACK.ADD;
1123 App_Exception.Raise_Exception;
1124 END IF;
1125 END IF;
1126
1127
1128 IF upper(column_name) = 'PRECED_SEQUENCE_NUMBER' OR
1129 column_name is NULL THEN
1130 IF TO_NUMBER(new_references.preced_sequence_number) < 1 OR
1131 TO_NUMBER(new_references.preced_sequence_number) > 999999 THEN
1132 Fnd_Message.Set_Name('IGS','IGS_INVALID_VALUE');
1133 IGS_GE_MSG_STACK.ADD;
1134 App_Exception.Raise_Exception;
1135 END IF;
1136 END IF;
1137
1138
1139 IF upper(column_name) = 'OVRD_NTFCTN_IMMINENT_DAYS' OR
1140 column_name is NULL THEN
1141 IF TO_NUMBER(new_references.ovrd_ntfctn_imminent_days) < 0 OR
1142 TO_NUMBER(new_references.ovrd_ntfctn_imminent_days) > 999 THEN
1143 Fnd_Message.Set_Name('IGS','IGS_INVALID_VALUE');
1144 IGS_GE_MSG_STACK.ADD;
1145 App_Exception.Raise_Exception;
1146 END IF;
1147 END IF;
1148
1149
1150
1151 IF upper(column_name) = 'OVRD_NTFCTN_REMINDER_DAYS' OR
1152 column_name is NULL THEN
1153 IF TO_NUMBER(new_references.ovrd_ntfctn_reminder_days) < 0 OR
1154 TO_NUMBER(new_references.ovrd_ntfctn_reminder_days) > 999 THEN
1155 Fnd_Message.Set_Name('IGS','IGS_INVALID_VALUE');
1156 IGS_GE_MSG_STACK.ADD;
1157 App_Exception.Raise_Exception;
1158 END IF;
1159 END IF;
1160
1161 IF upper(column_name) = 'OVRD_NTFCTN_RE_REMINDER_DAYS' OR
1162 column_name is NULL THEN
1163 IF TO_NUMBER(new_references.ovrd_ntfctn_re_reminder_days) < 0 OR
1164 TO_NUMBER(new_references.ovrd_ntfctn_re_reminder_days) > 999 THEN
1165 Fnd_Message.Set_Name('IGS','IGS_INVALID_VALUE');
1166 IGS_GE_MSG_STACK.ADD;
1167 App_Exception.Raise_Exception;
1168 END IF;
1169 END IF;
1170
1171 END Check_Constraints;
1172 end IGS_PR_MILESTONE_PKG;