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