1 PACKAGE BODY igs_tr_gen_002 AS
2 /* $Header: IGSTR02B.pls 120.1 2006/05/18 03:15:58 prbhardw noship $ */
3 --msrinivi 27 Aug,2001 Bug 1956374. Pointed genp_al_prsn_id to igs_co_val_oc
4
5 FUNCTION trkp_del_tri(
6 p_tracking_id IN NUMBER ,
7 p_message_name OUT NOCOPY VARCHAR2 )
8 RETURN BOOLEAN AS
9
10 /*******************************************************************************
11 Created by : Oracle IDC
12 Date created:
13
14 Purpose:
15 1. Used for deletion of tracking step notes, deletion of
16 tracking steps, for tracking items, deletion of tracking group members for
17 IGS_TR_ITEMS, deletion of tracking item notes for IGS_TR_ITEMS and deletion of
18 IGS_TR_ITEMS
19
20 Usage: (e.g. restricted, unrestricted, where to call from)
21 1. Called from IGSTR007.fmb
22
23 Known limitations/enhancements/remarks:
24 -
25
26 Change History: (who, when, what: NO CREATION RECORDS HERE!)
27 Who When What
28 *******************************************************************************/
29
30 lv_param_values VARCHAR2(1080);
31 gv_other_detail VARCHAR2(255);
32
33 BEGIN
34
35 -- trkp_del_tri
36 -- Delete a tracking item.
37 DECLARE
38
39 e_resource_busy EXCEPTION;
40 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
41 v_message_name VARCHAR2(30);
42
43 ------------------------------------------------------------------------------
44 -- 1. Delete tracking_step_notes for the IGS_TR_ITEM.
45 ------------------------------------------------------------------------------
46
47 FUNCTION trkpl_del_tsn (
48 lp_tracking_id igs_tr_item.tracking_id%TYPE)
49 RETURN BOOLEAN AS
50 lv_param_values VARCHAR2(1080);
51 BEGIN
52
53 DECLARE
54
55 CURSOR c_del_tsn IS
56 SELECT ROWID,reference_number
57 FROM igs_tr_step_note
58 WHERE tracking_id = lp_tracking_id
59 FOR UPDATE OF tracking_id NOWAIT;
60
61 BEGIN
62
63 FOR v_tsn_rec IN c_del_tsn LOOP
64
65 igs_tr_step_note_pkg.delete_row(x_rowid => v_tsn_rec.ROWID);
66 IF igs_ge_gen_001.genp_del_note( v_tsn_rec.reference_number, v_message_name) = FALSE THEN
67 p_message_name := v_message_name;
68 EXIT;
69 END IF;
70 END LOOP;
71
72 IF (v_message_name <> NULL) THEN
73 RETURN FALSE;
74 END IF;
75
76 RETURN TRUE;
77
78 END;
79
80 EXCEPTION
81
82 WHEN e_resource_busy THEN
83 p_message_name := 'IGS_TR_ITEM_STEP_NOTE_LOCKED';
84 RETURN FALSE;
85
86 WHEN OTHERS THEN
87 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
88 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkpl_del_tsn'||'-'||SQLERRM);
89 igs_ge_msg_stack.ADD;
90 lv_param_values:= TO_CHAR(lp_tracking_id);
91 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
92 fnd_message.set_token('VALUE',lv_param_values);
93 igs_ge_msg_stack.ADD;
94 app_exception.raise_exception;
95
96 END trkpl_del_tsn;
97
98 ------------------------------------------------------------------------------
99 -- 2. Delete tracking_step for the IGS_TR_ITEM.
100 ------------------------------------------------------------------------------
101 FUNCTION trkpl_del_ts(
102 lp_tracking_id igs_tr_item.tracking_id%TYPE)
103 RETURN BOOLEAN AS
104
105 lv_param_values VARCHAR2(1080);
106
107 BEGIN
108
109 DECLARE
110
111 CURSOR c_del_ts IS
112 SELECT igs_tr_step.*,ROWID
113 FROM igs_tr_step
114 WHERE tracking_id = lp_tracking_id
115 FOR UPDATE OF tracking_id NOWAIT;
116
117 BEGIN
118
119 FOR v_ts_rec IN c_del_ts LOOP
120 igs_tr_step_pkg.delete_row( x_rowid => v_ts_rec.ROWID);
121 END LOOP;
122 RETURN TRUE;
123
124 END;
125
126 EXCEPTION
127 WHEN e_resource_busy THEN
128 p_message_name := 'IGS_TR_STEP_RECORD_LOCKED';
129 RETURN FALSE;
130
131 WHEN OTHERS THEN
132 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
133 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkpl_del_ts'||'-'||SQLERRM);
134 igs_ge_msg_stack.ADD;
135 lv_param_values:= TO_CHAR(lp_tracking_id);
136 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
137 fnd_message.set_token('VALUE',lv_param_values);
138 igs_ge_msg_stack.ADD;
139 app_exception.raise_exception;
140
141 END trkpl_del_ts;
142
143 ------------------------------------------------------------------------------
144 -- 3. Delete tracking_group_member for the IGS_TR_ITEM.
145 ------------------------------------------------------------------------------
146 FUNCTION trkpl_del_tgm(
147 lp_tracking_id igs_tr_item.tracking_id%TYPE)
148 RETURN BOOLEAN AS
149 lv_param_values VARCHAR2(1080);
150 BEGIN
151
152 DECLARE
153
154 CURSOR c_del_tgm IS
155 SELECT ROWID,igs_tr_group_member.*
156 FROM igs_tr_group_member
157 WHERE tracking_id = p_tracking_id
158 FOR UPDATE OF tracking_id NOWAIT;
159
160 BEGIN
161
162 FOR v_tgm_rec IN c_del_tgm LOOP
163 igs_tr_group_member_pkg.delete_row( x_rowid => v_tgm_rec.ROWID);
164 END LOOP;
165
166 RETURN TRUE;
167 END;
168
169 EXCEPTION
170 WHEN e_resource_busy THEN
171 p_message_name := 'IGS_TR_GRP_RECORD_LOCKED';
172 RETURN FALSE;
173
174 WHEN OTHERS THEN
175 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
176 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkpl_del_tgm'||'-'||SQLERRM);
177 igs_ge_msg_stack.ADD;
178 lv_param_values:= TO_CHAR(lp_tracking_id);
179 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
180 fnd_message.set_token('VALUE',lv_param_values);
181 igs_ge_msg_stack.ADD;
182 app_exception.raise_exception;
183
184 END trkpl_del_tgm;
185
186 ------------------------------------------------------------------------------
187 -- 4. Delete tracking_item_notes for the IGS_TR_ITEM.
188 ------------------------------------------------------------------------------
189 FUNCTION trkpl_del_tin (
190 lp_tracking_id igs_tr_item.tracking_id%TYPE)
191 RETURN BOOLEAN AS
192 lv_param_values VARCHAR2(1080);
193 BEGIN
194
195 DECLARE
196
197 CURSOR c_del_tin IS
198 SELECT ROWID, reference_number
199 FROM igs_tr_item_note
200 WHERE tracking_id = lp_tracking_id
201 FOR UPDATE OF tracking_id NOWAIT;
202
203 BEGIN
204
205 FOR v_tin_rec IN c_del_tin LOOP
206 igs_tr_item_note_pkg.delete_row( x_rowid => v_tin_rec.ROWID);
207 IF igs_ge_gen_001.genp_del_note( v_tin_rec.reference_number, v_message_name) = FALSE THEN
208 p_message_name := v_message_name;
209 EXIT;
210 END IF;
211 END LOOP;
212
213 IF (v_message_name <> NULL) THEN
214 RETURN FALSE;
215 END IF;
216
217 RETURN TRUE;
218 END;
219
220 EXCEPTION
221
222 WHEN e_resource_busy THEN
223 p_message_name := 'IGS_TR_ITEM_NOTE_REC_LOCKED';
224 RETURN FALSE;
225
226 WHEN OTHERS THEN
227 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
228 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkpl_del_tin'||'-'||SQLERRM);
229 igs_ge_msg_stack.ADD;
230 lv_param_values:= TO_CHAR(lp_tracking_id);
231 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
232 fnd_message.set_token('VALUE',lv_param_values);
233 igs_ge_msg_stack.ADD;
234 app_exception.raise_exception;
235
236 END trkpl_del_tin;
237
238 ------------------------------------------------------------------------------
239 -- 5. Delete the IGS_TR_ITEM.
240 ------------------------------------------------------------------------------
241 FUNCTION trkpl_del_tri(
242 lp_tracking_id igs_tr_item.tracking_id%TYPE)
243 RETURN BOOLEAN AS
244 lv_param_values VARCHAR2(1080);
245 BEGIN
246
247 DECLARE
248
249 CURSOR c_del_tri IS
250 SELECT ROWID, igs_tr_item.*
251 FROM igs_tr_item
252 WHERE tracking_id = lp_tracking_id
253 FOR UPDATE OF tracking_id NOWAIT;
254
255 BEGIN
256
257 FOR v_tri_rec IN c_del_tri LOOP
258 igs_tr_item_pkg.delete_row( x_rowid => v_tri_rec.ROWID);
259 END LOOP;
260
261 RETURN TRUE;
262 END;
263
264 EXCEPTION
265
266 WHEN e_resource_busy THEN
267 p_message_name := 'IGS_TR_ITEM_RECORD_LOCKED';
268 RETURN FALSE;
269
270 WHEN OTHERS THEN
271 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
272 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkpl_del_tri'||'-'||SQLERRM);
273 igs_ge_msg_stack.ADD;
274 lv_param_values:= TO_CHAR(lp_tracking_id);
275 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
276 fnd_message.set_token('VALUE',lv_param_values);
277 igs_ge_msg_stack.ADD;
278 app_exception.raise_exception;
279
280 END trkpl_del_tri;
281
282
283 ------------------------------------------------------------------------------
284 -- 6. Delete step group limit from IGS_TR_STEP_GRP_LMT
285 -- Function added by pradhakr as part of CCR for Tracking in Applicant
286 -- Self Service Part 3.
287 ------------------------------------------------------------------------------
288 FUNCTION trkpl_del_grp_lmt(
289 lp_tracking_id igs_tr_step_grp_lmt.tracking_id%TYPE )
290 RETURN BOOLEAN AS
291 lv_param_values VARCHAR2(1080);
292 BEGIN
293
294 DECLARE
295
296 CURSOR c_del_grp_lmt IS
297 SELECT ROWID
298 FROM igs_tr_step_grp_lmt
299 WHERE tracking_id = lp_tracking_id
300 FOR UPDATE OF tracking_id NOWAIT;
301
302 BEGIN
303
304 FOR rec_del_grp_lmt IN c_del_grp_lmt LOOP
305 igs_tr_step_grp_lmt_pkg.delete_row( x_rowid => rec_del_grp_lmt.rowid);
306 END LOOP;
307
308 RETURN TRUE;
309 END;
310
311 EXCEPTION
312
313 WHEN e_resource_busy THEN
314 p_message_name := 'IGS_TR_LIMIT_RECORD_LOCKED';
315 RETURN FALSE;
316
317 WHEN OTHERS THEN
318 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
319 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkpl_del_grp_lmt'||'-'||SQLERRM);
320 igs_ge_msg_stack.add;
321 lv_param_values:= TO_CHAR(lp_tracking_id);
322 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
323 fnd_message.set_token('VALUE',lv_param_values);
324 igs_ge_msg_stack.add;
325 app_exception.raise_exception;
326
327 END trkpl_del_grp_lmt; /* End of the procedure */
328
329 BEGIN
330
331 SAVEPOINT sp_del_tri;
332 p_message_name := NULL;
333
334 IF ( -- Delete tracking_step_note
335 trkpl_del_tsn(p_tracking_id) = FALSE OR
336 -- Delete tracking_step
337 trkpl_del_ts(p_tracking_id) = FALSE OR
338 -- Deleting step group limit from IGS_TR_STEP_GRP_LMT
339 trkpl_del_grp_lmt(p_tracking_id) = FALSE OR
340 -- Delete tracking_group_member
341 trkpl_del_tgm(p_tracking_id) = FALSE OR
342 -- Delete tracking_item_note
343 trkpl_del_tin(p_tracking_id) = FALSE OR
344 -- Delete tracking_item
345 trkpl_del_tri(p_tracking_id) = FALSE) THEN
346
347 ROLLBACK TO sp_del_tri;
348 RETURN FALSE;
349 END IF;
350 RETURN TRUE;
351
352 END;
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
357 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkp_del_tri'||'-'||SQLERRM);
358 igs_ge_msg_stack.ADD;
359 lv_param_values:= TO_CHAR(p_tracking_id);
360 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
361 fnd_message.set_token('VALUE',lv_param_values);
362 igs_ge_msg_stack.ADD;
363 app_exception.raise_exception;
364
365 END trkp_del_tri;
366
367
368 FUNCTION trkp_get_group_sts(
369 p_tracking_group_id IN NUMBER )
370 RETURN VARCHAR2 IS
371
372 /*******************************************************************************
373 Created by : Oracle IDC
374 Date created:
375
376 Purpose:
377 1. This function returns the status of a tracking group
378
379 Usage: (e.g. restricted, unrestricted, where to call from)
380 1. Called from IGSTR007.FMB
381
382 Known limitations/enhancements/remarks:
383 -
384 Change History: (who, when, what: NO CREATION RECORDS HERE!)
385 Who When What
386 *******************************************************************************/
387
388 BEGIN
389
390 -- return status of a tracking group
391 DECLARE
392
393 v_other_detail VARCHAR2(255);
394 v_tracking_id igs_tr_item.tracking_id%TYPE;
395 v_s_tracking_status igs_tr_status.s_tracking_status%TYPE;
396 v_active_flag BOOLEAN DEFAULT FALSE;
397 v_cancelled_cnt NUMBER(3) DEFAULT 0;
398 v_row_cnt NUMBER(3) DEFAULT 0;
399
400 CURSOR c_tracking_group_member ( cp_tracking_group_id igs_tr_group.tracking_group_id%TYPE) IS
401 SELECT tracking_id
402 FROM igs_tr_group_member
403 WHERE tracking_group_id = cp_tracking_group_id;
404
405 BEGIN
406
407 OPEN c_tracking_group_member(p_tracking_group_id);
408
409 LOOP
410
411 FETCH c_tracking_group_member INTO v_tracking_id;
412 EXIT WHEN c_tracking_group_member%NOTFOUND;
413 v_s_tracking_status := trkp_get_item_status(v_tracking_id);
414 -- exit if any tracking item has a system status of ACTIVE
415
416 IF (v_s_tracking_status = 'ACTIVE') THEN
417 v_active_flag := TRUE;
418 EXIT;
419
420 ELSIF (v_s_tracking_status = 'CANCELLED') THEN
421 v_cancelled_cnt := v_cancelled_cnt + 1;
422 END IF;
423
424 END LOOP;
425
426 v_row_cnt := c_tracking_group_member%rowcount;
427 CLOSE c_tracking_group_member;
428
429 -- no members exist in the group
430 IF (v_row_cnt = 0) THEN
431 RETURN NULL;
432 END IF;
433
434 -- there exists tracking item has a system status of 'ACTIVE'
435 IF (v_active_flag = TRUE) THEN
436 RETURN 'ACTIVE';
437 END IF;
438
439 -- all tracking items have system status of 'CANCELLED'
440 IF (v_row_cnt = v_cancelled_cnt) THEN
441 RETURN 'CANCELLED';
442 END IF;
443 -- tracking items have system status of both 'CANCELLED' and 'COMPLETE'
444 RETURN 'COMPLETE';
445 END;
446
447 END trkp_get_group_sts;
448
449 FUNCTION trkp_get_item_status(
450 p_tracking_id IN NUMBER )
451 RETURN VARCHAR2 IS
452
453 /*******************************************************************************
454 Created by : Oracle IDC
455 Date created:
456
457 Purpose:
458 1. This function returns the status of a tracking item
459
460 Usage: (e.g. restricted, unrestricted, where to call from)
461 1. Called from IGSTR007.FMB
462
463 Known limitations/enhancements/remarks:
464 -
465
466 Change History: (who, when, what: NO CREATION RECORDS HERE!)
467 Who When What
468 *******************************************************************************/
469
470 BEGIN
471
472 -- Returns the status of tracking item
473 DECLARE
474
475 v_other_detail VARCHAR2(255);
476 v_s_tracking_status igs_tr_status.s_tracking_status%TYPE;
477
478 CURSOR c_get_s_tracking_status ( cp_tracking_id igs_tr_item.tracking_id%TYPE) IS
479 SELECT s_tracking_status
480 FROM igs_tr_item it, igs_tr_status ts
481 WHERE it.tracking_id = cp_tracking_id
482 AND it.tracking_status = ts.tracking_status;
483
484 BEGIN
485
486 -- Returns the status of the tracking item
487 OPEN c_get_s_tracking_status(p_tracking_id);
488 FETCH c_get_s_tracking_status INTO v_s_tracking_status;
489 IF (c_get_s_tracking_status%NOTFOUND) THEN
490 CLOSE c_get_s_tracking_status;
491 RAISE NO_DATA_FOUND;
492 END IF;
493 CLOSE c_get_s_tracking_status;
494 RETURN v_s_tracking_status;
495
496 END;
497
498 END trkp_get_item_status;
499
500
501 PROCEDURE trkp_ins_dflt_trst(
502 p_tracking_id IN NUMBER ,
503 p_message_name OUT NOCOPY VARCHAR2 )
504 IS
505
506 /*******************************************************************************
507 Created by : Oracle IDC
508 Date created:
509
510 Purpose:
511 1. This program unit is used to insert the default tracking_steps for an item
512 based on the IGS_TR_TYPE and its associated tracking_type_steps, duplicate
513 existing IGS_GE_NOTE records and insert IGS_TR_STEP_NOTES
514
515 Usage: (e.g. restricted, unrestricted, where to call from)
516 1. Called from IGSTR007.FMB upon creation of a tracking item.
517
518 Known limitations/enhancements/remarks:
519 -
520
521 Change History: (who, when, what: NO CREATION RECORDS HERE!)
522 Who When What
523 msrinivi 06 Jul,2001 Added logic to insert the newly added columns, i.e.
524 step catalog id,step group id and publish indicator
525 pradhakr 14-Feb-2002 Added code to insert step group limit after the
526 default of tracking type steps.
527 *******************************************************************************/
528
529 gv_other_detail VARCHAR2(255);
530 lv_param_values VARCHAR2(1080);
531
532 BEGIN
533
534 -- trkp_ins_dftl_trst
535 -- Insert the default tracking_steps for an item based on the
536 -- the IGS_TR_TYPE and its associated tracking_type_steps.
537 -- This procedure should only be call on creation of a
538 -- tracking item.
539
540 DECLARE
541
542 v_other_detail VARCHAR2(255);
543 v_tracking_type igs_tr_item.tracking_type%TYPE;
544 v_originator_person_id igs_tr_item.originator_person_id%TYPE;
545 v_new_reference_number igs_ge_note.reference_number%TYPE;
546 p_rowid VARCHAR2(25);
547 v_start_dt igs_tr_item.start_dt%TYPE;
548 v_completion_due_dt igs_tr_item.completion_due_dt%TYPE;
549 v_override_offset_clc_ind igs_tr_item.override_offset_clc_ind%TYPE;
550
551 CURSOR c_tracking_item ( cp_tracking_id igs_tr_item.tracking_id%TYPE) IS
552 SELECT tracking_type, originator_person_id,start_dt,completion_due_dt, override_offset_clc_ind
553 FROM igs_tr_item
554 WHERE tracking_id = cp_tracking_id;
555
556 CURSOR c_tracking_type_step ( cp_tracking_type igs_tr_type.tracking_type%TYPE) IS
557 SELECT *
558 FROM igs_tr_type_step
559 WHERE tracking_type = cp_tracking_type;
560
561 CURSOR c_tracking_type_step_note (
562 cp_tracking_type igs_tr_type.tracking_type%TYPE,
563 cp_tracking_type_step_id igs_tr_type_step.tracking_type_step_id%TYPE
564 ) IS
565 SELECT reference_number, trk_note_type
566 FROM igs_tr_typ_step_note
567 WHERE tracking_type = cp_tracking_type
568 AND tracking_type_step_id = cp_tracking_type_step_id;
569
570 -- Cursor to fetch group limit
571 CURSOR c_tracking_type_step_grplmt (cp_tracking_type igs_tr_type.tracking_type%TYPE) IS
572 SELECT step_group_id,
573 step_group_limit
574 FROM igs_tr_tstp_grp_lmt
575 WHERE tracking_type = cp_tracking_type;
576
577
578 -- This procedure insert a IGS_TR_STEP record
579 PROCEDURE trkpl_ins_ts_rec (
580 p_tracking_id igs_tr_step.tracking_id%TYPE,
581 p_tracking_step_id igs_tr_step.tracking_step_id%TYPE,
582 p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
583 p_description igs_tr_step.description%TYPE,
584 p_completion_dt igs_tr_step.completion_dt%TYPE,
585 p_action_days igs_tr_step.action_days%TYPE,
586 p_step_completion_ind igs_tr_step.step_completion_ind%TYPE,
587 p_by_pass_ind igs_tr_step.by_pass_ind%TYPE,
588 p_recipient_id igs_tr_step.recipient_id%TYPE,
589 p_s_tracking_step_type igs_tr_type_step.s_tracking_step_type%TYPE,
590 p_step_catalog_cd igs_tr_type_step.step_catalog_cd%TYPE,
591 p_step_group_id igs_tr_type_step.step_group_id%TYPE,
592 p_publish_ind igs_tr_type_step.publish_ind%TYPE)
593
594 AS
595
596 lv_param_values VARCHAR2(1080);
597 p_rowid VARCHAR2(25);
598
599 BEGIN
600
601 igs_tr_step_pkg.insert_row (
602 x_rowid => p_rowid,
603 x_tracking_id => p_tracking_id,
604 x_tracking_step_id => p_tracking_step_id ,
605 x_tracking_step_number => p_tracking_step_number,
606 x_description => p_description,
607 x_s_tracking_step_type=> p_s_tracking_step_type,
608 x_completion_dt => p_completion_dt,
609 x_action_days => p_action_days,
610 x_step_completion_ind => p_step_completion_ind,
611 x_by_pass_ind => p_by_pass_ind,
612 x_recipient_id => p_recipient_id,
613 x_mode => 'R',
614 x_step_catalog_cd => p_step_catalog_cd,
615 x_step_group_id => p_step_group_id,
616 x_publish_ind => p_publish_ind
617 );
618
619 EXCEPTION
620
621 WHEN OTHERS THEN
622 app_exception.raise_exception;
623 DECLARE
624 l_message_name VARCHAR2(30);
625 l_app VARCHAR2(50);
626 BEGIN
627 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
628 IF l_message_name = 'IGS_TR_STEP_CTLG_CLOSED' THEN
629 fnd_message.set_name('IGS',l_message_name);
630 igs_ge_msg_stack.ADD;
631 app_exception.raise_exception;
632 ELSE
633 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
634 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkpl_ins_ts_rec'||'-'||SQLERRM);
635 igs_ge_msg_stack.ADD;
636 lv_param_values:=TO_CHAR(p_tracking_id)||','||TO_CHAR(p_tracking_step_id);
637 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
638 fnd_message.set_token('VALUE',lv_param_values);
639 igs_ge_msg_stack.ADD;
640 lv_param_values:=TO_CHAR(p_tracking_step_number)||','||p_description;
641 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
642 fnd_message.set_token('VALUE',lv_param_values);
643 igs_ge_msg_stack.ADD;
644 lv_param_values:=igs_ge_date.igschar (p_completion_dt)||','||TO_CHAR(p_action_days);
645 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
646 fnd_message.set_token('VALUE',lv_param_values);
647 igs_ge_msg_stack.ADD;
648 lv_param_values:=p_step_completion_ind||','||p_by_pass_ind||','||TO_CHAR(p_recipient_id );
649 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
650 fnd_message.set_token('VALUE',lv_param_values);
651 igs_ge_msg_stack.ADD;
652 lv_param_values:=p_s_tracking_step_type;
653 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
654 fnd_message.set_token('VALUE',lv_param_values);
655 igs_ge_msg_stack.ADD;
656 app_exception.raise_exception;
657 END IF;
658 END;
659 END trkpl_ins_ts_rec;
660
661 -- Duplicate existing IGS_GE_NOTE record
662 PROCEDURE trkpl_dup_note_rec (
663 p_reference_number igs_ge_note.reference_number%TYPE,
664 p_new_reference_number OUT NOCOPY igs_ge_note.reference_number%TYPE)
665 AS
666
667 lv_param_values VARCHAR2(1080);
668 p_rowid VARCHAR2(25);
669 v_s_note_format_type igs_ge_note.s_note_format_type%TYPE;
670 v_note_text igs_ge_note.note_text%TYPE;
671 v_next_ref_number igs_ge_note.reference_number%TYPE;
672
673 CURSOR c_note IS
674 SELECT s_note_format_type, note_text
675 FROM igs_ge_note
676 WHERE reference_number = p_reference_number;
677
678 BEGIN
679
680 OPEN c_note;
681 FETCH c_note INTO v_s_note_format_type, v_note_text;
682 CLOSE c_note;
683 SELECT igs_ge_note_rf_num_s.NEXTVAL INTO v_next_ref_number FROM dual;
684
685 p_new_reference_number := v_next_ref_number;
686 igs_ge_note_pkg.insert_row(
687 x_rowid => p_rowid,
688 x_reference_number => v_next_ref_number,
689 x_s_note_format_type => v_s_note_format_type,
690 x_note_text => v_note_text,
691 x_mode => 'R'
692 );
693
694 /* CALL ROUTINE TO COPY OLE DATA HERE */
695 EXCEPTION
696 WHEN OTHERS THEN
697 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
698 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkpl_dup_note_rec'||'-'||SQLERRM);
699 igs_ge_msg_stack.ADD;
700 lv_param_values:=TO_CHAR(p_reference_number);
701 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
702 fnd_message.set_token('VALUE',lv_param_values);
703 igs_ge_msg_stack.ADD;
704 app_exception.raise_exception;
705
706 END trkpl_dup_note_rec;
707
708 -- Insert IGS_TR_STEP_NOTE record
709 PROCEDURE trkpl_ins_tsn_rec(
710 p_tracking_id igs_tr_step_note.tracking_id%TYPE,
711 p_tracking_step_id igs_tr_step_note.tracking_step_id%TYPE,
712 p_reference_number igs_tr_step_note.reference_number%TYPE,
713 p_trk_note_type igs_tr_step_note.trk_note_type%TYPE)
714 AS
715
716 lv_param_values VARCHAR2(1080);
717 p_rowid VARCHAR2(25);
718
719 BEGIN
720
721 igs_tr_step_note_pkg.insert_row(
722 x_rowid => p_rowid,
723 x_tracking_id => p_tracking_id,
724 x_tracking_step_id => p_tracking_step_id,
725 x_reference_number => p_reference_number,
726 x_trk_note_type => p_trk_note_type,
727 x_mode => 'R'
728 );
729
730 EXCEPTION
731 WHEN OTHERS THEN
732 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
733 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkpl_ins_tsn_rec'||'-'||SQLERRM);
734 igs_ge_msg_stack.ADD;
735 lv_param_values:=TO_CHAR(p_tracking_id)||','||TO_CHAR(p_tracking_step_id)||','||TO_CHAR(p_reference_number);
736 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
737 fnd_message.set_token('VALUE',lv_param_values);
738 igs_ge_msg_stack.ADD;
739 lv_param_values:=p_trk_note_type;
740 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
741 fnd_message.set_token('VALUE',lv_param_values);
742 igs_ge_msg_stack.ADD;
743 app_exception.raise_exception;
744 END trkpl_ins_tsn_rec;
745
746
747 -- API to default the tracking steps for item based on the Tracking Type
748 -- Added by pradhakr
749 PROCEDURE trkpl_ins_ts_grp_lmt (
750 p_tracking_id IN igs_tr_step_grp_lmt.tracking_id%TYPE,
751 p_step_group_id IN igs_tr_step_grp_lmt.step_group_id%TYPE,
752 p_step_group_limit IN igs_tr_step_grp_lmt.step_group_limit%TYPE
753 ) AS
754
755 lv_param_values VARCHAR2(1080);
756 l_rowid VARCHAR2(25);
757
758 BEGIN
759
760 IGS_TR_STEP_GRP_LMT_PKG.INSERT_ROW (
761 X_ROWID => l_rowid,
762 X_TRACKING_ID => p_tracking_id,
763 X_STEP_GROUP_ID => p_step_group_id,
764 X_STEP_GROUP_LIMIT => p_step_group_limit
765 );
766
767 EXCEPTION
768 WHEN OTHERS THEN
769 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
770 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkpl_ins_ts_grp_lmt'||'-'||SQLERRM);
771 igs_ge_msg_stack.add;
772 lv_param_values:=TO_CHAR(p_tracking_id)||','||TO_CHAR(p_step_group_id)||','||TO_CHAR(p_step_group_limit);
773 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
774 fnd_message.set_token('VALUE',lv_param_values);
775 igs_ge_msg_stack.add;
776 app_exception.raise_exception;
777
778 END trkpl_ins_ts_grp_lmt; /* End of the procedure */
779
780 BEGIN
781
782 p_message_name := NULL;
783 -- Select the IGS_TR_TYPE, originator_person_id
784 OPEN c_tracking_item( p_tracking_id);
785 FETCH c_tracking_item
786 INTO
787 v_tracking_type,
788 v_originator_person_id,
789 v_start_dt,
790 v_completion_due_dt,
791 v_override_offset_clc_ind;
792
793 IF (c_tracking_item%NOTFOUND) THEN
794 CLOSE c_tracking_item;
795 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
796 RETURN;
797 END IF;
798
799 CLOSE c_tracking_item;
800
801 -- Select all IGS_TR_TYPE_STEP records
802
803 FOR v_tts_rec IN c_tracking_type_step( v_tracking_type) LOOP
804
805 IF (v_tts_rec.recipient_id IS NULL) THEN
806 v_tts_rec.recipient_id := v_originator_person_id;
807 END IF;
808
809 -- If the override offset ind is set, then set the action days to be
810 -- completion due date minus the start date
811 IF (NVL(v_override_offset_clc_ind,'N') = 'Y') THEN
812 v_tts_rec.action_days := v_completion_due_dt - v_start_dt;
813 END IF;
814
815 -- For each record found, create a new IGS_TR_STEP record
816
817 trkpl_ins_ts_rec (
818 p_tracking_id,
819 v_tts_rec.tracking_type_step_id,
820 v_tts_rec.tracking_type_step_number,
821 v_tts_rec.description,
822 NULL,
823 v_tts_rec.action_days,
824 'N',
825 'N',
826 v_tts_rec.recipient_id,
827 v_tts_rec.s_tracking_step_type,
828 v_tts_rec.step_catalog_cd,
829 v_tts_rec.step_group_id,
830 v_tts_rec.publish_ind
831 );
832
833 -- If any tracking_type_step_notes exist then
834 -- create a new IGS_GE_NOTE record(s) which duplicates the existing one
835
836 FOR v_ttsn_rec IN c_tracking_type_step_note ( v_tts_rec.tracking_type, v_tts_rec.tracking_type_step_id) LOOP
837
838 trkpl_dup_note_rec(v_ttsn_rec.reference_number, v_new_reference_number);
839 -- then create a new IGS_TR_STEP_NOTE
840
841 trkpl_ins_tsn_rec(
842 p_tracking_id,
843 v_tts_rec.tracking_type_step_id,
844 v_new_reference_number,
845 v_ttsn_rec.trk_note_type
846 );
847
848 END LOOP;
849
850 END LOOP;
851
852 -- If any tracking group limit exists then insert into IGS_TR_STEP_GRP_LMT
853 FOR rec_tracking_type_step_grplmt IN c_tracking_type_step_grplmt(v_tracking_type) LOOP
854 trkpl_ins_ts_grp_lmt (p_tracking_id,
855 rec_tracking_type_step_grplmt.step_group_id,
856 rec_tracking_type_step_grplmt.step_group_limit);
857 END LOOP;
858
859 END;
860
861 EXCEPTION
862 WHEN OTHERS THEN
863 DECLARE
864 l_message_name VARCHAR2(30);
865 l_app VARCHAR2(50);
866 BEGIN
867 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
868 --kumma,2989286 , if l_message_name is not null then show message else unhandled
869 IF l_message_name IS NOT NULL THEN
870 FND_MESSAGE.SET_NAME('IGS',l_message_name);
871 IGS_GE_MSG_STACK.ADD;
872 APP_EXCEPTION.RAISE_EXCEPTION;
873 ELSE
874 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
875 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkp_ins_dflt_trst'||'-'||SQLERRM);
876 igs_ge_msg_stack.ADD;
877 lv_param_values:=TO_CHAR(p_tracking_id);
878 lv_param_values:=fnd_message.get;
879 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
880 fnd_message.set_token('VALUE',lv_param_values);
881 igs_ge_msg_stack.ADD;
882 app_exception.raise_exception;
883 END IF;
884 END;
885
886 END trkp_ins_dflt_trst;
887
888 PROCEDURE trkp_ins_trk_item(
889 p_tracking_status IN VARCHAR2 ,
890 p_tracking_type IN VARCHAR2 ,
891 p_source_person_id IN NUMBER ,
892 p_start_dt IN DATE ,
893 p_target_days IN NUMBER ,
894 p_sequence_ind IN VARCHAR2 ,
895 p_business_days_ind IN VARCHAR2 ,
896 p_originator_person_id IN NUMBER ,
897 p_s_created_ind IN VARCHAR2 ,
898 p_tracking_id OUT NOCOPY NUMBER ,
899 p_message_name OUT NOCOPY VARCHAR2,
900 p_override_offset_clc_ind IN VARCHAR2 ,
901 p_completion_due_dt IN DATE ,
902 p_publish_ind IN VARCHAR2 )
903 IS
904
905 /*******************************************************************************
906 Created by : Oracle IDC
907 Date created:
908
909 Purpose:
910 1. This procedure will be used by batch processing to create a tracking item
911 It will accept the details of the item to be created and insert a
912 IGS_TR_ITEM record. The tracking item step will be defaulted
913 when the database table insert trigger fires for the tracking item.
914
915 Usage: (e.g. restricted, unrestricted, where to call from)
916 1. Called from IGSTR007.FMB
917
918 Known limitations/enhancements/remarks:
919 -
920
921 Change History: (who, when, what: NO CREATION RECORDS HERE!)
922 Who When What
923 msrinivi 06 Jul,2001 Added 3 new columns : completion due dt,
924 override offset clc ind and publish ind
925 ssawhney 18 Oct 2002 p_target_days, use NVL with cursor value. RE03 was not passing them
926 *******************************************************************************/
927
928 gv_other_detail VARCHAR2(255);
929 lv_param_values VARCHAR2(1080);
930 v_completion_due_dt igs_tr_item.completion_due_dt%TYPE;
931
932 BEGIN
933
934 -- trkp_ins_trk_item
935 -- This procedure will be used by batch processing to create a tracking item
936 -- It will accept the details of the item to be created and insert a
937 -- IGS_TR_ITEM record. The tracking item step will be defaulted
938 -- when the database table insert trigger fires for the tracking item.
939
940 DECLARE
941
942 v_check CHAR;
943 v_tracking_type igs_tr_type%ROWTYPE;
944 v_nxt_tracking_id igs_tr_item.tracking_id%TYPE;
945 l_sequence_ind igs_tr_type.sequence_ind%TYPE;
946 l_business_days_ind igs_tr_type.business_days_ind%TYPE;
947 p_rowid VARCHAR2(25);
948
949 CURSOR c_tracking_status IS
950 SELECT 'x'
951 FROM igs_tr_status
952 WHERE tracking_status = p_tracking_status;
953
954 CURSOR c_tracking_type IS
955 SELECT *
956 FROM igs_tr_type
957 WHERE tracking_type = p_tracking_type;
958
959 -- person exists check from person_base_v
960 CURSOR c_person (cp_person_id igs_pe_person.person_id%TYPE) IS
961 SELECT 'x'
962 FROM igs_pe_person_base_v
963 WHERE person_id = cp_person_id;
964
965 CURSOR c_tri_nxt_seq_num IS
966 SELECT igs_tr_item_tr_id_s.NEXTVAL
967 FROM dual;
968
969 BEGIN
970
971 p_message_name := NULL;
972
973 IF (p_tracking_status IS NULL) THEN
974 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
975 RETURN;
976 END IF;
977
978 OPEN c_tracking_status;
979
980 FETCH c_tracking_status INTO v_check;
981 IF (c_tracking_status%NOTFOUND) THEN
982 CLOSE c_tracking_status;
983 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
984 RETURN;
985 END IF;
986
987 CLOSE c_tracking_status;
988
989 IF (p_tracking_type IS NULL) THEN
990 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
991 RETURN;
992 END IF;
993
994 OPEN c_tracking_type;
995
996 FETCH c_tracking_type INTO v_tracking_type;
997 IF (c_tracking_type%NOTFOUND) THEN
998 CLOSE c_tracking_type;
999 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
1000 RETURN;
1001 END IF;
1002
1003 CLOSE c_tracking_type;
1004
1005 IF (p_originator_person_id IS NULL) THEN
1006 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
1007 RETURN;
1008 END IF;
1009
1010 OPEN c_person(p_originator_person_id);
1011 FETCH c_person INTO v_check;
1012
1013 IF (c_person%NOTFOUND) THEN
1014 CLOSE c_person;
1015 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
1016 RETURN;
1017 END IF;
1018
1019 CLOSE c_person;
1020
1021 IF (p_source_person_id IS NOT NULL) THEN
1022 OPEN c_person(p_source_person_id);
1023 FETCH c_person INTO v_check;
1024 IF (c_person%NOTFOUND) THEN
1025 CLOSE c_person;
1026 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
1027 RETURN;
1028 END IF;
1029 CLOSE c_person;
1030 END IF;
1031
1032 l_sequence_ind := NVL(p_sequence_ind, v_tracking_type.sequence_ind);
1033 l_business_days_ind := NVL(p_business_days_ind, v_tracking_type.business_days_ind);
1034
1035 IF (l_sequence_ind = 'Y' OR l_business_days_ind = 'Y') AND
1036 NVL(p_override_offset_clc_ind,'N') = 'Y' THEN
1037 p_message_name := 'IGS_TR_CANNOT_CHK_SEQ_OR_BD';
1038 RETURN;
1039 END IF;
1040
1041 --Manu
1042 IF NVL(p_override_offset_clc_ind,'N') = 'Y' AND p_completion_due_dt IS NULL THEN
1043 p_message_name := 'IGS_TR_COMP_DUE_DATE_REQ';
1044 RETURN;
1045 END IF;
1046
1047 IF NVL(p_override_offset_clc_ind,'N') = 'N' AND p_completion_due_dt IS NOT NULL THEN
1048 p_message_name := 'IGS_TR_COMP_DUE_DATE_NOT_SUPP';
1049 RETURN;
1050 END IF;
1051
1052 -- ssawhney customer bug 2610394,
1053 -- RE was passing p_target_days as null and because of that v_comp_due_dt was coming out NOCOPY to be same as p_start_dt.
1054
1055 IF p_completion_due_dt IS NULL THEN
1056 v_completion_due_dt := igs_tr_gen_001.trkp_clc_dt_offset(p_start_dt,
1057 NVL(p_target_days, v_tracking_type.target_days), -- p_target_days,
1058 l_business_days_ind,
1059 NVL(p_override_offset_clc_ind,'N'));
1060 END IF;
1061 --Manu
1062
1063 OPEN c_tri_nxt_seq_num;
1064 FETCH c_tri_nxt_seq_num INTO v_nxt_tracking_id;
1065 CLOSE c_tri_nxt_seq_num;
1066 p_tracking_id := v_nxt_tracking_id;
1067
1068 igs_tr_item_pkg.insert_row(
1069 x_rowid => p_rowid,
1070 x_tracking_id => v_nxt_tracking_id,
1071 x_tracking_status => p_tracking_status ,
1072 x_tracking_type => p_tracking_type,
1073 x_source_person_id => p_source_person_id,
1074 x_start_dt => NVL(p_start_dt, SYSDATE),
1075 x_target_days => NVL(p_target_days, v_tracking_type.target_days),
1076 x_sequence_ind => l_sequence_ind,
1077 x_business_days_ind => l_business_days_ind,
1078 x_originator_person_id => p_originator_person_id,
1079 x_s_created_ind => NVL(p_s_created_ind, 'N'),
1080 x_mode => 'R',
1081 x_org_id => igs_ge_gen_003.get_org_id,
1082 x_override_offset_clc_ind => NVL(p_override_offset_clc_ind,'N'),
1083 x_completion_due_dt => NVL(p_completion_due_dt,v_completion_due_dt),
1084 x_publish_ind => p_publish_ind
1085 );
1086
1087 END;
1088
1089 EXCEPTION
1090 WHEN OTHERS THEN
1091 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1092 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkp_ins_trk_item'||'-'||SQLERRM);
1093 igs_ge_msg_stack.ADD;
1094 lv_param_values:= p_tracking_status ||','||p_tracking_type||','||TO_CHAR(p_source_person_id );
1095 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
1096 fnd_message.set_token('VALUE',lv_param_values);
1097 igs_ge_msg_stack.ADD;
1098 lv_param_values:= igs_ge_date.igschar (p_start_dt)||','|| TO_CHAR(p_target_days)||','||p_sequence_ind ;
1099 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
1100 fnd_message.set_token('VALUE',lv_param_values);
1101 igs_ge_msg_stack.ADD;
1102 lv_param_values:= p_business_days_ind ||','||TO_CHAR(p_originator_person_id )||','||p_s_created_ind ;
1103 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
1104 fnd_message.set_token('VALUE',lv_param_values);
1105 igs_ge_msg_stack.ADD;
1106 app_exception.raise_exception;
1107
1108 END trkp_ins_trk_item;
1109
1110 FUNCTION trkp_next_step_cmplt (
1111 p_tracking_id igs_tr_step.tracking_id%TYPE,
1112 p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
1113 p_step_group_id igs_tr_step.step_group_id%TYPE,
1114 p_message_name OUT NOCOPY VARCHAR2
1115 ) RETURN BOOLEAN IS
1116 ------------------------------------------------------------------
1117 --Created by : Apex, Oracle IDC (ssanyal.in)
1118 --Date created: 15-DEC-1999
1119 --
1120 --Purpose:
1121 --
1122 --
1123 --Known limitations/enhancements and/or remarks:
1124 --
1125 --Change History:
1126 --Who When What
1127 --msrinivi 13 Jul,2001 To check validity of change in by pass flag
1128 -------------------------------------------------------------------
1129 lv_param_values VARCHAR2(1080);
1130 gv_other_detail VARCHAR2(255);
1131
1132 l_prev_grp_step_comp VARCHAR2(1) := 'N';
1133 l_next_step_comp VARCHAR2(1) := 'N';
1134
1135 TYPE rec_step_group_id IS RECORD ( step_group_id NUMBER);
1136 TYPE tab_step_group_id IS TABLE OF
1137 rec_step_group_id INDEX BY BINARY_INTEGER;
1138 plsql_step_group_id tab_step_group_id;
1139 cnt NUMBER DEFAULT 1;
1140 l_row_count NUMBER DEFAULT 0;
1141 l_num_rows NUMBER DEFAULT 0;
1142
1143 -- ssawhney, table reference included instead of view
1144 CURSOR c_trst_all IS
1145 SELECT *
1146 FROM igs_tr_step
1147 WHERE tracking_id = p_tracking_id
1148 ORDER BY tracking_step_number;
1149
1150 CURSOR c_trst_later IS
1151 SELECT *
1152 FROM igs_Tr_step
1153 WHERE tracking_id = p_tracking_id
1154 AND tracking_step_number > p_tracking_step_number
1155 ORDER BY tracking_step_number;
1156
1157 c_trst_all_rec c_trst_all%ROWTYPE;
1158
1159 BEGIN
1160 FOR l_trst_all IN c_trst_all LOOP
1161 EXIT WHEN (l_trst_all.tracking_step_number = p_tracking_step_number);
1162 IF l_trst_all.step_group_id IS NOT NULL AND
1163 l_trst_all.step_completion_ind = 'Y' THEN
1164 plsql_step_group_id(cnt).step_group_id := l_trst_all.step_group_id;
1165 cnt := cnt + 1;
1166 END IF;
1167 END LOOP;
1168
1169 FOR l_trst_all IN c_trst_all LOOP
1170 l_row_count := l_row_count+1;
1171 EXIT WHEN (l_trst_all.tracking_step_number = p_tracking_step_number OR
1172 l_prev_grp_step_comp = 'Y');
1173 IF l_trst_all.completion_dt IS NOT NULL AND
1174 l_trst_all.step_group_id IS NOT NULL THEN
1175 IF l_trst_all.step_group_id = p_step_group_id THEN
1176 l_prev_grp_step_comp := 'Y';
1177 EXIT;
1178 END IF;
1179 END IF;
1180 END LOOP;
1181
1182 IF l_prev_grp_step_comp = 'Y' THEN
1183 RETURN FALSE;
1184 END IF;
1185
1186 l_next_step_comp := 'N';
1187
1188 FOR l_trst_all IN c_trst_all LOOP
1189 l_num_rows := l_num_rows + 1;
1190 END LOOP;
1191
1192 IF l_num_rows = l_row_count THEN
1193 RETURN FALSE;
1194 END IF;
1195
1196 FOR l_trst_later IN c_trst_later LOOP
1197 EXIT WHEN (l_next_step_comp = 'Y');
1198 IF (l_trst_later.completion_dt IS NOT NULL) THEN
1199 IF l_trst_later.step_group_id IS NOT NULL THEN
1200 l_next_step_comp := 'Y';
1201 FOR idx IN 1..plsql_step_group_id.COUNT
1202 LOOP
1203 IF plsql_step_group_id(idx).step_group_id = l_trst_later.step_group_id THEN
1204 l_next_step_comp := 'N';
1205 EXIT;
1206 END IF;
1207 END LOOP;
1208 ELSE
1209 l_next_step_comp := 'Y';
1210 END IF;
1211 END IF;
1212 END LOOP;
1213
1214 IF l_next_step_comp = 'Y' THEN
1215 p_message_name := 'IGS_TR_CANNOT_UNCHK_COMPL_IND';
1216 RETURN TRUE;
1217 ELSE
1218 RETURN FALSE;
1219 END IF;
1220
1221 EXCEPTION
1222 WHEN OTHERS THEN
1223 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1224 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkp_next_step_cmplt'||'-'||SQLERRM);
1225 igs_ge_msg_stack.ADD;
1226 lv_param_values:= TO_CHAR(p_tracking_id) ||','||TO_CHAR(p_tracking_step_number)||','||TO_CHAR(p_step_group_id);
1227 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
1228 fnd_message.set_token('VALUE',lv_param_values);
1229 igs_ge_msg_stack.ADD;
1230 app_exception.raise_exception;
1231 END trkp_next_step_cmplt;
1232
1233 FUNCTION trkp_next_grp_step_cmplt (
1234 p_tracking_id igs_tr_step.tracking_id%TYPE,
1235 p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
1236 p_step_group_id igs_tr_step.step_group_id%TYPE,
1237 p_message_name OUT NOCOPY VARCHAR2
1238 ) RETURN BOOLEAN IS
1239 ------------------------------------------------------------------
1240 --Created by : Apex, Oracle IDC (ssanyal.in)
1241 --Date created: 15-DEC-1999
1242 --
1243 --Purpose:
1244 --
1245 --
1246 --Known limitations/enhancements and/or remarks:
1247 --
1248 --Change History:
1249 --Who When What
1250 -------------------------------------------------------------------
1251 lv_param_values VARCHAR2(1080);
1252 gv_other_detail VARCHAR2(255);
1253
1254 l_next_step_comp VARCHAR2(1) := 'N';
1255
1256 TYPE rec_step_group_id IS RECORD ( step_group_id NUMBER);
1257 TYPE tab_step_group_id IS TABLE OF
1258 rec_step_group_id INDEX BY BINARY_INTEGER;
1259 plsql_step_group_id tab_step_group_id;
1260 cnt NUMBER DEFAULT 1;
1261 l_row_count NUMBER DEFAULT 0;
1262 l_num_rows NUMBER DEFAULT 0;
1263
1264 --ssawhney, view reference changed to table.
1265 CURSOR c_trst_later IS
1266 SELECT *
1267 FROM igs_tr_step
1268 WHERE tracking_id = p_tracking_id
1269 AND tracking_step_number > p_tracking_step_number
1270 ORDER BY tracking_step_number;
1271
1272 CURSOR c_trst_all IS
1273 SELECT *
1274 FROM igs_tr_step
1275 WHERE tracking_id = p_tracking_id
1276 ORDER BY tracking_step_number;
1277
1278 CURSOR c_trst_next(p_tracking_step_number NUMBER) IS
1279 SELECT *
1280 FROM igs_tr_step
1281 WHERE tracking_id = p_tracking_id
1282 AND tracking_step_number > p_tracking_step_number
1283 ORDER BY tracking_step_number;
1284
1285 c_trst_all_rec c_trst_all%ROWTYPE;
1286 c_trst_later_rec c_trst_later%ROWTYPE;
1287
1288 BEGIN
1289
1290 OPEN c_trst_later;
1291 FETCH c_trst_later INTO c_trst_later_rec;
1292 IF (c_trst_later%NOTFOUND) THEN
1293 CLOSE c_trst_later;
1294 RETURN FALSE;
1295 END IF;
1296 CLOSE c_trst_later;
1297
1298 FOR l_trst_all IN c_trst_all LOOP
1299 EXIT WHEN (l_trst_all.tracking_step_number = p_tracking_step_number);
1300 IF l_trst_all.step_group_id IS NOT NULL AND
1301 l_trst_all.step_completion_ind = 'Y' THEN
1302 plsql_step_group_id(cnt).step_group_id := l_trst_all.step_group_id;
1303 cnt := cnt + 1;
1304 END IF;
1305 END LOOP;
1306
1307 l_row_count := p_tracking_step_number;
1308
1309 FOR l_trst_later IN c_trst_later LOOP
1310 l_row_count := l_row_count + 1;
1311 IF (l_trst_later.step_group_id = p_step_group_id AND
1312 l_trst_later.step_completion_ind = 'N' AND
1313 l_trst_later.by_pass_ind = 'N') THEN
1314 EXIT;
1315 END IF;
1316 END LOOP;
1317
1318 FOR l_trst_all IN c_trst_all LOOP
1319 l_num_rows := l_num_rows + 1;
1320 END LOOP;
1321
1322 l_next_step_comp := 'N';
1323
1324 IF l_num_rows = l_row_count THEN
1325 RETURN FALSE;
1326 END IF;
1327
1328 FOR l_trst_next IN c_trst_next(l_row_count) LOOP
1329 EXIT WHEN (l_next_step_comp = 'Y');
1330 IF l_trst_next.completion_dt IS NOT NULL THEN
1331 l_next_step_comp := 'Y';
1332 ELSE
1333 l_next_step_comp := 'N';
1334 END IF;
1335 END LOOP;
1336
1337 IF l_next_step_comp = 'Y' THEN
1338 p_message_name := 'IGS_TR_CANNOT_CHK_BYPAS_IND';
1339 RETURN TRUE;
1340 ELSE
1341 RETURN FALSE;
1342 END IF;
1343
1344 EXCEPTION
1345 WHEN OTHERS THEN
1346 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1347 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkp_next_grp_step_cmplt'||'-'||SQLERRM);
1348 igs_ge_msg_stack.ADD;
1349 lv_param_values:= TO_CHAR(p_tracking_id) ||','||TO_CHAR(p_tracking_step_number)||','||TO_CHAR(p_step_group_id);
1350 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
1351 fnd_message.set_token('VALUE',lv_param_values);
1352 igs_ge_msg_stack.ADD;
1353 app_exception.raise_exception;
1354 END trkp_next_grp_step_cmplt;
1355
1356
1357 FUNCTION trkp_check_step_group_limit (
1358 p_tracking_id IN igs_tr_step.tracking_id%TYPE,
1359 p_tracking_step_number IN igs_tr_step.tracking_step_number%TYPE,
1360 p_step_group_id IN igs_tr_step.step_group_id%TYPE,
1361 p_message_name OUT NOCOPY VARCHAR2
1362 ) RETURN BOOLEAN IS
1363 ------------------------------------------------------------------
1364 --Created by : pradhakr, Oracle IDC
1365 --Date created: 11-FEB-2002
1366 --
1367 --Purpose:
1368 --
1369 --
1370 --Known limitations/enhancements and/or remarks:
1371 --
1372 --Change History:
1373 --Who When What
1374 -------------------------------------------------------------------
1375
1376 lv_param_values VARCHAR2(1080);
1377
1378 -- Cursor to get all step group ids and the number of completed steps prior to the
1379 -- passed step group id (p_step_group_id) and tracking step number (p_ tracking_step_number) and
1380 -- comparing the result with the one in the table IGS_TR_STEP_GRP_LMT
1381
1382 CURSOR c_grp_limit IS
1383 SELECT tab.step_group_id, tab.step_group_limit
1384 FROM
1385 ( SELECT step_group_id, count(*) step_group_limit
1386 FROM igs_tr_step trst
1387 WHERE trst.tracking_id = p_tracking_id
1388 AND trst.tracking_step_number < p_tracking_step_number
1389 AND trst.step_group_id is not null
1390 AND trst.step_group_id <> p_step_group_id
1391 AND trst.step_completion_ind = 'Y'
1392 AND trst.by_pass_ind = 'N'
1393 GROUP BY trst.step_group_id, trst.step_completion_ind
1394 MINUS
1395 SELECT step_group_id, step_group_limit
1396 FROM igs_tr_step_grp_lmt
1397 WHERE tracking_id = p_tracking_id
1398 ) tab ,
1399 igs_tr_step_grp_lmt trg
1400 WHERE tab.step_group_id = trg.step_group_id
1401 AND trg.tracking_id = p_tracking_id
1402 AND tab.step_group_limit < trg.step_group_limit ;
1403
1404 l_grp_limit c_grp_limit%ROWTYPE;
1405
1406 BEGIN
1407
1408 OPEN c_grp_limit;
1409 FETCH c_grp_limit INTO l_grp_limit;
1410 -- If record exists, it means that some of the step group limit has not been met for the step group id.
1411 IF c_grp_limit%FOUND THEN
1412 p_message_name := 'IGS_TR_STEP_GRPLMT_VIOLATE';
1413 RETURN FALSE;
1414 ELSE
1415 RETURN TRUE;
1416 END IF;
1417 CLOSE c_grp_limit;
1418
1419 EXCEPTION
1420 WHEN OTHERS THEN
1421 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1422 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkp_check_step_group_limit'||'-'||SQLERRM);
1423 igs_ge_msg_stack.add;
1424 lv_param_values:= TO_CHAR(p_tracking_id) ||','||TO_CHAR(p_tracking_step_number)||','||TO_CHAR(p_step_group_id);
1425 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
1426 fnd_message.set_token('VALUE',lv_param_values);
1427 igs_ge_msg_stack.add;
1428 app_exception.raise_exception;
1429
1430 END trkp_check_step_group_limit;
1431
1432
1433 FUNCTION trkp_prev_step_cmplt (
1434 p_tracking_id igs_tr_step.tracking_id%TYPE,
1435 p_tracking_step_number igs_tr_step.tracking_step_number%TYPE,
1436 p_step_group_id igs_tr_step.step_group_id%TYPE,
1437 p_message_name OUT NOCOPY VARCHAR2
1438 ) RETURN BOOLEAN IS
1439 ------------------------------------------------------------------
1440 --Created by : Apex, Oracle IDC (ssanyal.in)
1441 --Date created: 15-DEC-1999
1442 --
1443 --Purpose:
1444 --
1445 --
1446 --Known limitations/enhancements and/or remarks:
1447 --
1448 --Change History:
1449 --Who When What
1450 -------------------------------------------------------------------
1451 lv_param_values VARCHAR2(1080);
1452 gv_other_detail VARCHAR2(255);
1453
1454
1455 CURSOR c_earlier_step_trst IS
1456 SELECT DISTINCT TO_NUMBER('1')
1457 FROM igs_tr_step trst
1458 WHERE trst.tracking_id = p_tracking_id
1459 AND trst.tracking_step_number < p_tracking_step_number
1460 AND trst.step_group_id IS NULL
1461 AND trst.step_completion_ind = 'N'
1462 AND trst.by_pass_ind = 'N'
1463 UNION
1464 (
1465 SELECT DISTINCT trst.step_group_id
1466 FROM igs_tr_step trst
1467 WHERE trst.tracking_id = p_tracking_id
1468 AND trst.tracking_step_number < p_tracking_step_number
1469 AND trst.step_group_id IS NOT NULL
1470 AND trst.step_completion_ind = 'N'
1471 AND trst.by_pass_ind = 'N'
1472 GROUP BY trst.step_group_id, trst.step_completion_ind
1473 MINUS
1474 SELECT DISTINCT trst.step_group_id
1475 FROM igs_tr_step trst
1476 WHERE trst.tracking_id = p_tracking_id
1477 AND trst.tracking_step_number < p_tracking_step_number
1478 AND trst.step_group_id IS NOT NULL
1479 AND trst.step_completion_ind = 'Y'
1480 GROUP BY trst.step_group_id, trst.step_completion_ind
1481 );
1482 v_step_completion_ind_temp NUMBER;
1483
1484 BEGIN
1485
1486 OPEN c_earlier_step_trst;
1487 FETCH c_earlier_step_trst INTO v_step_completion_ind_temp;
1488 IF (c_earlier_step_trst%FOUND) THEN
1489 CLOSE c_earlier_step_trst;
1490 p_message_name := 'IGS_TR_PREV_STEP_COMPLETED';
1491 RETURN FALSE;
1492 END IF;
1493 CLOSE c_earlier_step_trst;
1494
1495 IF trkp_check_step_group_limit (
1496 p_tracking_id,
1497 p_tracking_step_number,
1498 p_step_group_id,
1499 p_message_name ) THEN
1500
1501 RETURN TRUE;
1502 ELSE
1503 RETURN FALSE;
1504 END IF;
1505
1506 EXCEPTION
1507 WHEN OTHERS THEN
1508 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1509 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkp_next_grp_step_cmplt'||'-'||SQLERRM);
1510 igs_ge_msg_stack.ADD;
1511 lv_param_values:= TO_CHAR(p_tracking_id) ||','||TO_CHAR(p_tracking_step_number);
1512 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
1513 fnd_message.set_token('VALUE',lv_param_values);
1514 igs_ge_msg_stack.ADD;
1515 app_exception.raise_exception;
1516 END trkp_prev_step_cmplt;
1517
1518
1519 FUNCTION trkp_upd_trst(
1520 p_tracking_id IN NUMBER ,
1521 p_tracking_step_id IN NUMBER ,
1522 p_s_tracking_step_type IN VARCHAR2 ,
1523 p_action_dt IN DATE ,
1524 p_completion_dt IN DATE ,
1525 p_step_completion_ind IN VARCHAR2,
1526 p_by_pass_ind IN VARCHAR2,
1527 p_recipient_id IN NUMBER ,
1528 p_message_name OUT NOCOPY VARCHAR2
1529 )
1530 RETURN BOOLEAN AS
1531
1532 /***********************************************************************************************************
1533 Created by : Oracle IDC
1534 Date created:
1535
1536 Purpose:
1537 1. This module will update fields of the action days of
1538 a IGS_TR_STEP record.
1539 Usage: (e.g. restricted, unrestricted, where to call from)
1540 1. Called from IGSTR007.FMB
1541
1542 Known limitations/enhancements/remarks:
1543 -
1544
1545 Change History: (who, when, what: NO CREATION RECORDS HERE!)
1546 Who When What
1547
1548 msrinivi 06 Jul,2001 Modified to have the logic for step groups before update. msrinivi
1549 11 Jul,2001 Step update not allowed if item is complete,
1550 Complete item if all the steps are considered complete
1551
1552 Aiyer 24-Apr-2002 This code has been modified by Aiyer for the bug 2309359
1553 In call to function igs_tr_gen_001.trkp_clc_bus_dt the p_business_days
1554 parameter was being passed as 'N'.This was causing a numeric conversion error .
1555 This has been set to NVL(p_action_days,0) in this fix.
1556
1557 ssawhney 4-Nov-2003 Bug 3206700. The cursor to check item completion modified.
1558 Logic also modified to concider BY PASS item as complete.
1559 **************************************************************************************************************/
1560
1561 lv_param_values VARCHAR2(1080);
1562 gv_other_detail VARCHAR2(255);
1563
1564
1565 BEGIN
1566
1567 -- trkp_upd_trst
1568 -- This module will update fields of the action days of
1569 -- a IGS_TR_STEP record.
1570
1571 DECLARE
1572
1573 e_resource_busy_exception EXCEPTION;
1574
1575 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
1576
1577 v_tracking_step_id igs_tr_step.tracking_step_id%TYPE;
1578 v_action_days NUMBER;
1579 v_completion_dt igs_tr_step.completion_dt%TYPE;
1580 v_step_completion_ind igs_tr_step.step_completion_ind%TYPE;
1581 v_by_pass_ind igs_tr_step.by_pass_ind%TYPE;
1582 v_recipient_id igs_tr_step.recipient_id%TYPE;
1583 v_start_dt igs_tr_item.start_dt%TYPE;
1584 v_business_days_ind igs_tr_item.business_days_ind%TYPE;
1585 v_sequence_ind igs_tr_item.sequence_ind%TYPE;
1586 v_step_completion_ind_temp igs_tr_step.step_completion_ind%TYPE;
1587 v_action_dt igs_tr_step_v.action_dt%TYPE;
1588 v_message_name VARCHAR2(30);
1589 v_rowid VARCHAR2(25);
1590 v_step_group_id igs_tr_step.step_group_id%TYPE;
1591 v_tracking_step_number igs_tr_step.tracking_step_number%TYPE;
1592 l_field_mod VARCHAR2(10) := NULL;
1593 l_end_dt DATE;
1594 l_action_days igs_tr_step.action_days%TYPE;
1595 l_action_dt DATE;
1596
1597 TYPE rec_step_group_id IS RECORD ( step_group_id NUMBER);
1598 TYPE tab_step_group_id IS TABLE OF
1599 rec_step_group_id INDEX BY BINARY_INTEGER;
1600 plsql_step_group_id tab_step_group_id;
1601 cnt NUMBER DEFAULT 1;
1602
1603 CURSOR c_tri_all IS
1604 SELECT *
1605 FROM igs_tr_item
1606 WHERE tracking_id = p_tracking_id;
1607
1608 CURSOR c_sys_trk_sts(p_tracking_status igs_tr_status.tracking_status%TYPE) IS
1609 SELECT s_tracking_status
1610 FROM igs_tr_status
1611 WHERE tracking_status = p_tracking_status;
1612
1613 --ssawhney cant change view reference..using view logic in select.
1614 CURSOR c_update_trst IS
1615 SELECT trst.*, trst.rowid ROW_ID, igs_tr_gen_001.trkp_clc_action_dt ( trst.tracking_id, trst.tracking_step_number,
1616 tri.start_dt, tri.sequence_ind,tri.business_days_ind ) action_dt
1617 FROM igs_tr_step trst,
1618 igs_tr_item tri
1619 WHERE trst.tracking_id = p_tracking_id
1620 AND trst.tracking_id = tri.tracking_id
1621 AND ((NVL(p_tracking_step_id, 0) = 0)
1622 OR
1623 (trst.tracking_step_id = p_tracking_step_id))
1624 AND ((NVL(p_s_tracking_step_type, 'NULL') = 'NULL')
1625 OR
1626 (trst.s_tracking_step_type = p_s_tracking_step_type))
1627 AND ROWNUM = 1
1628 ORDER BY trst.tracking_step_number DESC
1629 FOR UPDATE OF trst.action_days NOWAIT;
1630
1631 --Added by Manu to check if the item should be marked as complete
1632 --if the all the steps can be considered as complete
1633 -- Cursor modified for Bug 3206700
1634 -- first half gets info if any step outside the group is not complete
1635 -- second half gets info if any group has steps open/bypass or steps are complete by group limit is not achieved.
1636
1637 --ssawhney view reference changed to table.
1638 CURSOR c_check_item_cmpltn IS
1639 SELECT DISTINCT TO_NUMBER('1')
1640 FROM igs_tr_step trst
1641 WHERE trst.tracking_id = p_tracking_id
1642 AND trst.step_group_id IS NULL
1643 AND trst.step_completion_ind = 'N'
1644 AND trst.by_pass_ind = 'N'
1645 UNION
1646 (
1647 SELECT distinct step_group_id
1648 FROM igs_tr_step a
1649 WHERE a.tracking_id = p_tracking_id
1650 AND a.step_group_id IS NOT NULL
1651 -- total no of groups for the tracking item
1652 MINUS
1653 -- subtract groups that are open
1654 SELECT c.step_group_id
1655 FROM (
1656 SELECT tab.step_group_id, tab.count_step
1657 FROM (
1658 SELECT trst.step_group_id step_group_id,
1659 COUNT(*) count_step
1660 FROM igs_tr_step trst
1661 WHERE trst.tracking_id = p_tracking_id
1662 AND trst.step_group_id IS NOT NULL
1663 AND ((trst.step_completion_ind = 'Y' AND trst.by_pass_ind = 'N') OR
1664 (trst.step_completion_ind = 'N' AND trst.by_pass_ind = 'Y'))
1665 GROUP BY trst.step_group_id,
1666 trst.step_completion_ind
1667 ) tab,
1668 -- a step is considered complete if either its BY PASSED or its completion ind is Y
1669 igs_tr_step_grp_lmt trg
1670 WHERE tab.step_group_id = trg.step_group_id
1671 AND trg.tracking_id = p_tracking_id
1672 AND tab.count_step >= trg.step_group_limit
1673 -- subtract only if the total steps completed are less than the group limit.
1674 ) c
1675 );
1676
1677 CURSOR c_dflt_trk_sts IS
1678 SELECT tracking_status
1679 FROM igs_tr_status
1680 WHERE s_tracking_status = 'COMPLETE'
1681 AND default_ind = 'Y';
1682
1683 igs_tr_step_rec c_update_trst%ROWTYPE;
1684 igs_tr_item_rec c_tri_all%ROWTYPE;
1685 l_sys_trk_sts igs_tr_status.s_tracking_status%TYPE;
1686 l_trk_sts igs_tr_status.tracking_status%TYPE;
1687 l_item_cmpl c_check_item_cmpltn%ROWTYPE;
1688
1689 -- validitate action / completion date and get action days
1690 FUNCTION process_date (
1691 p_date_type IN VARCHAR2,
1692 p_tracking_id IN igs_tr_step.tracking_id%TYPE,
1693 p_tracking_step_number IN igs_tr_step.tracking_step_number%TYPE,
1694 p_end_dt IN OUT NOCOPY DATE,
1695 p_action_days IN OUT NOCOPY igs_tr_step.action_days%TYPE,
1696 p_message_name OUT NOCOPY VARCHAR2
1697 )RETURN BOOLEAN IS
1698 --ssawhney view reference changed to table in the second part..first part still referes to the function for getting the dates.
1699
1700 CURSOR c_start_dt IS
1701 SELECT NVL(trst.completion_dt,igs_tr_gen_001.trkp_clc_action_dt ( trst.tracking_id, trst.tracking_step_number,
1702 tri.start_dt, tri.sequence_ind,tri.business_days_ind )) start_dt
1703 FROM igs_tr_step trst, igs_tr_item tri
1704 WHERE trst.tracking_id = tri.tracking_id
1705 AND trst.tracking_id = p_tracking_id
1706 AND trst.tracking_step_number = (SELECT MAX(b.tracking_step_number)
1707 FROM igs_tr_step b
1708 WHERE b.tracking_id = p_tracking_id
1709 AND b.tracking_step_number < p_tracking_step_number
1710 AND b.by_pass_ind = 'N');
1711
1712 --ssawhney view reference changed to table and introduced fucntions.
1713 CURSOR c_upd_next_dt IS
1714 SELECT trst.*, trst.rowid ROW_ID, igs_tr_gen_001.trkp_clc_action_dt ( trst.tracking_id, trst.tracking_step_number,
1715 tri.start_dt, tri.sequence_ind,tri.business_days_ind ) action_dt
1716 FROM igs_tr_step trst,
1717 igs_tr_item tri
1718 WHERE trst.tracking_id = tri.tracking_id
1719 AND trst.tracking_id = p_tracking_id
1720 AND trst.tracking_step_number > p_tracking_step_number
1721 FOR UPDATE OF trst.action_days NOWAIT;
1722
1723 l_start_dt DATE;
1724 l_p_action_dt DATE;
1725 l_action_days igs_tr_step.action_days%TYPE;
1726 l_next_start_dt_diff NUMBER;
1727 l_next_start_dt DATE;
1728
1729 FUNCTION validate_date (
1730 p_date_type IN VARCHAR2,
1731 p_tracking_id IN igs_tr_step.tracking_id%TYPE,
1732 p_tracking_step_number IN igs_tr_step.tracking_step_number%TYPE,
1733 p_start_dt IN DATE,
1734 p_end_dt IN OUT NOCOPY DATE,
1735 p_action_days IN OUT NOCOPY igs_tr_step.action_days%TYPE,
1736 p_message_name OUT NOCOPY VARCHAR2
1737 )RETURN BOOLEAN IS
1738
1739 l_clc_end_dt DATE;
1740
1741 BEGIN
1742 IF p_start_dt > p_end_dt THEN
1743 p_message_name := 'IGS_TR_DT_CANNOT_LT_ST_DATE';
1744 RETURN FALSE;
1745 END IF;
1746
1747 IF igs_tr_item_rec.business_days_ind = 'Y' THEN
1748 -- This code has been changed by Aiyer for the bug 2309359
1749 -- In function igs_tr_gen_001.trkp_clc_bus_dt the p_business_days parameter was being passed as 'N'.
1750 -- This was causing a numeric conversion error . This has been set to NVL(p_action_days,0) in this fix.
1751 l_clc_end_dt := igs_tr_gen_001.trkp_clc_bus_dt(p_end_dt, NVL(p_action_days,0));
1752
1753 IF p_end_dt IS NOT NULL AND p_end_dt <> l_clc_end_dt THEN
1754 p_end_dt := l_clc_end_dt;
1755 RETURN FALSE;
1756 ELSE
1757 p_end_dt := l_clc_end_dt;
1758 END IF;
1759 END IF;
1760
1761 -- Validate that the action days has not exceeded the maximum allowable.
1762 --kumma, 2719789, Increased the length of the constant from 999 to 9999
1763 IF (TRUNC(p_start_dt) - TRUNC(p_end_dt)) > 9999 THEN
1764 p_message_name := 'IGS_TR_MAXIMUM_DAYS_EXCEEDED';
1765 RETURN FALSE;
1766 ELSE
1767 IF p_date_type = 'A' THEN
1768 p_action_days := TRUNC(p_end_dt) - TRUNC(p_start_dt);
1769 END IF;
1770 END IF;
1771
1772 RETURN TRUE;
1773 EXCEPTION
1774
1775 WHEN OTHERS THEN
1776 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1777 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkp_upd_trst.process_date.validate_date'||'-'||SQLERRM);
1778 igs_ge_msg_stack.ADD;
1779 lv_param_values:= TO_CHAR(p_tracking_id) ||','||TO_CHAR(p_tracking_step_number)||','||TO_CHAR(p_action_days)
1780 ||','||p_date_type||','||igs_ge_date.igschar (p_start_dt)
1781 ||','||igs_ge_date.igschar (p_end_dt);
1782 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
1783 fnd_message.set_token('VALUE',lv_param_values);
1784 igs_ge_msg_stack.ADD;
1785 app_exception.raise_exception;
1786 END validate_date;
1787
1788 BEGIN
1789 IF igs_tr_item_rec.sequence_ind = 'Y' THEN
1790 IF (p_tracking_step_number - 1) = 0 THEN
1791 l_start_dt := igs_tr_item_rec.start_dt;
1792 ELSE
1793 OPEN c_start_dt;
1794 FETCH c_start_dt INTO l_start_dt;
1795 CLOSE c_start_dt;
1796 END IF;
1797 ELSE
1798 l_start_dt := igs_tr_item_rec.start_dt;
1799 END IF;
1800
1801 IF NOT (validate_date (p_date_type,
1802 p_tracking_id,
1803 p_tracking_step_number,
1804 l_start_dt,
1805 p_end_dt,
1806 p_action_days,
1807 p_message_name)) THEN
1808 RETURN FALSE;
1809 END IF;
1810
1811 IF p_date_type = 'A' THEN
1812 l_next_start_dt_diff := TRUNC(p_end_dt) - TRUNC(igs_tr_step_rec.action_dt);
1813 l_next_start_dt := TRUNC(p_end_dt);
1814 ELSIF p_date_type = 'C' THEN
1815 l_next_start_dt_diff := TRUNC(p_end_dt) - TRUNC(igs_tr_step_rec.completion_dt);
1816 l_next_start_dt := TRUNC(p_end_dt);
1817 END IF;
1818
1819 IF igs_tr_item_rec.sequence_ind = 'Y' THEN
1820
1821 FOR l_upd_next_dt_rec IN c_upd_next_dt LOOP
1822
1823 IF (l_upd_next_dt_rec.step_completion_ind = 'N' AND l_upd_next_dt_rec.by_pass_ind = 'N') THEN
1824
1825 l_p_action_dt := l_next_start_dt + l_upd_next_dt_rec.action_days;
1826 IF NOT (validate_date ('A',
1827 l_upd_next_dt_rec.tracking_id,
1828 l_upd_next_dt_rec.tracking_step_number,
1829 l_next_start_dt,
1830 l_p_action_dt,
1831 l_action_days,
1832 p_message_name)) THEN
1833 RETURN FALSE;
1834 END IF;
1835
1836 -- Update the record with NOWAIT option
1837
1838 IF p_date_type = 'C' THEN
1839 l_action_days := l_upd_next_dt_rec.action_days;
1840 END IF;
1841
1842 igs_tr_step_pkg.update_row(
1843 x_rowid => l_upd_next_dt_rec.row_id,
1844 x_tracking_id => l_upd_next_dt_rec.tracking_id,
1845 x_tracking_step_id => l_upd_next_dt_rec.tracking_step_id,
1846 x_tracking_step_number => l_upd_next_dt_rec.tracking_step_number,
1847 x_description => l_upd_next_dt_rec.description,
1848 x_s_tracking_step_type => l_upd_next_dt_rec.s_tracking_step_type,
1849 x_completion_dt => l_upd_next_dt_rec.completion_dt,
1850 x_action_days => l_action_days,
1851 x_step_completion_ind => l_upd_next_dt_rec.step_completion_ind,
1852 x_by_pass_ind => l_upd_next_dt_rec.by_pass_ind,
1853 x_recipient_id => l_upd_next_dt_rec.recipient_id,
1854 x_mode => 'R',
1855 x_step_group_id => l_upd_next_dt_rec.step_group_id,
1856 x_step_catalog_cd => l_upd_next_dt_rec.step_catalog_cd,
1857 x_publish_ind => NVL(l_upd_next_dt_rec.publish_ind,'N')
1858 );
1859
1860 l_next_start_dt_diff := TRUNC(l_p_action_dt) - TRUNC(l_upd_next_dt_rec.action_dt);
1861 l_next_start_dt := TRUNC(l_p_action_dt);
1862
1863 ELSIF l_upd_next_dt_rec.step_completion_ind = 'Y' THEN
1864 l_next_start_dt_diff := 0;
1865 l_next_start_dt := TRUNC(l_upd_next_dt_rec.completion_dt);
1866
1867 ELSIF l_upd_next_dt_rec.by_pass_ind = 'Y' THEN
1868 NULL;
1869 END IF;
1870
1871 END LOOP;
1872 END IF;
1873
1874 RETURN TRUE;
1875
1876 EXCEPTION
1877 WHEN e_resource_busy_exception THEN
1878 p_message_name := 'IGS_TR_CANNOT_UPDATE_STEP';
1879 RETURN FALSE;
1880
1881 WHEN OTHERS THEN
1882 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1883 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkp_upd_trst.process_date'||'-'||SQLERRM);
1884 igs_ge_msg_stack.ADD;
1885 lv_param_values:= p_date_type||','||TO_CHAR(p_tracking_id) ||','||TO_CHAR(p_tracking_step_number)||
1886 ','||igs_ge_date.igschar (p_end_dt)||','||TO_CHAR(p_action_days);
1887 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
1888 fnd_message.set_token('VALUE',lv_param_values);
1889 igs_ge_msg_stack.ADD;
1890 app_exception.raise_exception;
1891 END process_date;
1892
1893 BEGIN
1894
1895 -- Set the default message number
1896 p_message_name := NULL;
1897
1898 -- Validate that p_tracking_step_id and p_s_tracking_step_type cannot both be null.
1899 IF ((p_tracking_step_id IS NULL) AND (p_s_tracking_step_type IS NULL)) THEN
1900 p_message_name := 'IGS_TR_STEP_TYPE_ID_NOT_NULL';
1901 RETURN FALSE;
1902 END IF;
1903
1904 -- Manu Validate that the step being updated does not
1905 -- belong to an item that is complete
1906 OPEN c_tri_all;
1907 FETCH c_tri_all INTO igs_tr_item_rec;
1908
1909 IF c_tri_all%NOTFOUND THEN
1910 CLOSE c_tri_all;
1911 p_message_name := 'IGS_TR_INVALID_ITEM_ID';
1912 RETURN FALSE;
1913
1914 ELSE
1915 OPEN c_sys_trk_sts(igs_tr_item_rec.tracking_status);
1916 FETCH c_sys_trk_sts INTO l_sys_trk_sts;
1917 CLOSE c_sys_trk_sts;
1918
1919 IF (l_sys_trk_sts <> 'ACTIVE') THEN
1920 p_message_name := 'IGS_TR_ST_UPD_CMPLT_ITM_NA';
1921 CLOSE c_tri_all;
1922 RETURN FALSE;
1923 END IF;
1924
1925 CLOSE c_tri_all;
1926 END IF;
1927
1928 -- Select the original fields of the tracking step record for UPDATE NOWWAIT.
1929 -- Select only the first record from this query if p_tracking_id is null and
1930 -- multiple steps having the same s_tracking_step_type exists,
1931 -- since we are only interested in the first one.
1932
1933 OPEN c_update_trst;
1934 FETCH c_update_trst INTO igs_tr_step_rec ;
1935
1936 IF (c_update_trst%NOTFOUND) THEN
1937 CLOSE c_update_trst;
1938 p_message_name := 'IGS_TR_INVALID_STEP_ID';
1939 RETURN FALSE;
1940 END IF;
1941
1942 IF (p_step_completion_ind IS NOT NULL AND
1943 p_step_completion_ind <> igs_tr_step_rec.step_completion_ind) THEN
1944 l_field_mod := l_field_mod || 'S';
1945 END IF;
1946
1947 IF (p_by_pass_ind IS NOT NULL AND
1948 p_by_pass_ind <> igs_tr_step_rec.by_pass_ind) THEN
1949 l_field_mod := l_field_mod || 'B';
1950 END IF;
1951
1952 IF (igs_tr_item_rec.override_offset_clc_ind = 'N' AND
1953 p_action_dt IS NOT NULL AND
1954 p_action_dt <> igs_tr_step_rec.action_dt AND
1955 igs_tr_step_rec.step_completion_ind = 'N' AND
1956 igs_tr_step_rec.by_pass_ind = 'N') THEN
1957 IF (igs_tr_item_rec.sequence_ind = 'Y' AND
1958 NVL(INSTR(l_field_mod,'S'),0) = 0 AND
1959 NVL(INSTR(l_field_mod,'B'),0) = 0) OR
1960 (igs_tr_item_rec.sequence_ind = 'N') THEN
1961 l_field_mod := l_field_mod || 'A';
1962 END IF;
1963 END IF;
1964
1965 IF (NVL(INSTR(l_field_mod,'S'),0) = 0 AND
1966 NVL(INSTR(l_field_mod,'B'),0) = 0) THEN
1967 IF (p_completion_dt IS NOT NULL AND
1968 igs_tr_step_rec.step_completion_ind = 'Y' AND
1969 p_completion_dt <> igs_tr_step_rec.completion_dt) THEN
1970 l_field_mod := l_field_mod || 'C';
1971 END IF;
1972 END IF;
1973
1974 IF (NVL(INSTR(l_field_mod,'S'),0) = 0 AND
1975 NVL(INSTR(l_field_mod,'B'),0) = 0 AND
1976 igs_tr_step_rec.step_completion_ind = 'N' AND
1977 igs_tr_step_rec.by_pass_ind = 'N') OR
1978 NVL(INSTR(l_field_mod,'S'),0) > 0 OR
1979 NVL(INSTR(l_field_mod,'B'),0) > 0 THEN
1980 IF (p_recipient_id IS NOT NULL) THEN
1981 IF (igs_tr_step_rec.recipient_id IS NOT NULL AND
1982 p_recipient_id <> igs_tr_step_rec.recipient_id ) OR
1983 (igs_tr_step_rec.recipient_id IS NULL) THEN
1984 l_field_mod := l_field_mod || 'R';
1985 END IF;
1986 END IF;
1987 END IF;
1988
1989 -- Validate that the step being updated does not modify
1990 -- step_completion_ind and by_pass_ind together to 'Y'
1991 IF ((p_step_completion_ind = 'Y') AND
1992 (p_by_pass_ind = 'Y')) THEN
1993 p_message_name := 'IGS_TR_BOTH_CMP_BYPAS_UPD_Y_NA';
1994 CLOSE c_update_trst;
1995 RETURN FALSE;
1996 END IF;
1997
1998 IF (igs_tr_item_rec.sequence_ind = 'Y') THEN
1999 IF NVL(INSTR(l_field_mod,'S'),0) > 0 THEN
2000 IF p_step_completion_ind = 'Y' THEN
2001 -- Validate new value, on error return false
2002 IF trkp_prev_step_cmplt (
2003 igs_tr_step_rec.tracking_id,
2004 igs_tr_step_rec.tracking_step_number,
2005 igs_tr_step_rec.step_group_id,
2006 p_message_name) THEN
2007
2008 igs_tr_step_rec.step_completion_ind := 'Y';
2009 igs_tr_step_rec.by_pass_ind := 'N';
2010
2011 l_end_dt := NVL(p_completion_dt,SYSDATE);
2012 IF NOT (process_date (
2013 'C',
2014 igs_tr_step_rec.tracking_id,
2015 igs_tr_step_rec.tracking_step_number,
2016 l_end_dt,
2017 l_action_days,
2018 p_message_name)) THEN
2019 CLOSE c_update_trst;
2020 RETURN FALSE;
2021 END IF;
2022 igs_tr_step_rec.completion_dt := l_end_dt;
2023
2024 ELSE
2025 CLOSE c_update_trst;
2026 RETURN FALSE;
2027 END IF;
2028
2029 ELSIF p_step_completion_ind = 'N' THEN
2030 -- Validate new value, on error return false
2031 IF NOT (trkp_next_step_cmplt (
2032 igs_tr_step_rec.tracking_id,
2033 igs_tr_step_rec.tracking_step_number,
2034 igs_tr_step_rec.step_group_id,
2035 p_message_name)) THEN
2036
2037 IF NVL(INSTR(l_field_mod,'C'),0) > 0 AND
2038 p_completion_dt IS NOT NULL THEN
2039 p_message_name := 'IGS_TR_CANNOT_SET_CMP_DATE';
2040 CLOSE c_update_trst;
2041 RETURN FALSE;
2042 END IF;
2043
2044 igs_tr_step_rec.step_completion_ind := 'N';
2045 igs_tr_step_rec.completion_dt := NULL;
2046 IF NVL(INSTR(l_field_mod,'A'),0) = 0 THEN
2047 l_action_dt := NVL(p_action_dt,igs_tr_step_rec.action_dt);
2048 l_field_mod := l_field_mod || 'A';
2049 END IF;
2050
2051 ELSE
2052 CLOSE c_update_trst;
2053 RETURN FALSE;
2054 END IF;
2055 END IF;
2056
2057 END IF;
2058
2059 IF NVL(INSTR(l_field_mod,'B'),0) > 0 THEN
2060 IF p_by_pass_ind = 'Y' THEN
2061 -- Validate new value, on error return false
2062 IF NOT (trkp_next_grp_step_cmplt (
2063 igs_tr_step_rec.tracking_id,
2064 igs_tr_step_rec.tracking_step_number,
2065 igs_tr_step_rec.step_group_id,
2066 p_message_name)) THEN
2067
2068 IF NVL(INSTR(l_field_mod,'C'),0) > 0 AND
2069 p_completion_dt IS NOT NULL THEN
2070 p_message_name := 'IGS_TR_CANNOT_SET_CMP_DATE';
2071 CLOSE c_update_trst;
2072 RETURN FALSE;
2073 END IF;
2074
2075 igs_tr_step_rec.step_completion_ind := 'N';
2076 igs_tr_step_rec.by_pass_ind := 'Y';
2077 igs_tr_step_rec.completion_dt := NULL;
2078 IF NVL(INSTR(l_field_mod,'A'),0) = 0 THEN
2079 l_action_dt := NVL(p_action_dt,igs_tr_step_rec.action_dt);
2080 l_field_mod := l_field_mod || 'A';
2081 END IF;
2082
2083 ELSE
2084 CLOSE c_update_trst;
2085 RETURN FALSE;
2086 END IF;
2087
2088 ELSIF p_by_pass_ind = 'N' THEN
2089 -- Validate new value, on error return false
2090 IF NOT (trkp_next_step_cmplt (
2091 igs_tr_step_rec.tracking_id,
2092 igs_tr_step_rec.tracking_step_number,
2093 igs_tr_step_rec.step_group_id,
2094 p_message_name)) THEN
2095 IF NVL(INSTR(l_field_mod,'C'),0) > 0 THEN
2096 p_message_name := 'IGS_TR_CANNOT_SET_CMP_DATE';
2097 CLOSE c_update_trst;
2098 RETURN FALSE;
2099 END IF;
2100
2101 igs_tr_step_rec.by_pass_ind := 'N';
2102
2103 ELSE
2104 CLOSE c_update_trst;
2105 RETURN FALSE;
2106 END IF;
2107 END IF;
2108
2109 END IF;
2110
2111 IF NVL(INSTR(l_field_mod,'A'),0) > 0 THEN
2112 -- Validate new value, on error return false
2113 l_end_dt := NVL(p_action_dt,l_action_dt);
2114 IF NOT (process_date (
2115 'A',
2116 igs_tr_step_rec.tracking_id,
2117 igs_tr_step_rec.tracking_step_number,
2118 l_end_dt,
2119 l_action_days,
2120 p_message_name)) THEN
2121 CLOSE c_update_trst;
2122 RETURN FALSE;
2123 END IF;
2124
2125 igs_tr_step_rec.action_dt := l_end_dt;
2126 igs_tr_step_rec.action_days := l_action_days;
2127
2128 END IF;
2129
2130 IF NVL(INSTR(l_field_mod,'C'),0) > 0 THEN
2131 -- Validate new value, on error return false
2132
2133 l_end_dt := NVL(p_completion_dt,SYSDATE);
2134 IF NOT (process_date (
2135 'C',
2136 igs_tr_step_rec.tracking_id,
2137 igs_tr_step_rec.tracking_step_number,
2138 l_end_dt,
2139 l_action_days,
2140 p_message_name)) THEN
2141 CLOSE c_update_trst;
2142 RETURN FALSE;
2143 END IF;
2144 igs_tr_step_rec.completion_dt := l_end_dt;
2145 END IF;
2146
2147 IF NVL(INSTR(l_field_mod,'R'),0) > 0 THEN
2148 -- Validate new value, on error return false
2149
2150 IF NOT (igs_co_val_oc.genp_val_prsn_id(p_recipient_id, p_message_name)) THEN
2151 CLOSE c_update_trst;
2152 RETURN FALSE;
2153 ELSE
2154 igs_tr_step_rec.recipient_id := p_recipient_id;
2155 END IF;
2156
2157 END IF;
2158
2159 ELSIF (igs_tr_item_rec.sequence_ind = 'N') THEN
2160
2161 IF NVL(INSTR(l_field_mod,'S'),0) > 0 THEN
2162 IF p_step_completion_ind = 'Y' THEN
2163 -- Validate new value, on error return false
2164
2165 igs_tr_step_rec.step_completion_ind := 'Y';
2166 igs_tr_step_rec.by_pass_ind := 'N';
2167
2168 l_end_dt := NVL(p_completion_dt,SYSDATE);
2169 IF NOT (process_date (
2170 'C',
2171 igs_tr_step_rec.tracking_id,
2172 igs_tr_step_rec.tracking_step_number,
2173 l_end_dt,
2174 l_action_days,
2175 p_message_name)) THEN
2176 CLOSE c_update_trst;
2177 RETURN FALSE;
2178 END IF;
2179 igs_tr_step_rec.completion_dt := l_end_dt;
2180
2181 ELSIF p_step_completion_ind = 'N' THEN
2182 -- Validate new value, on error return false
2183
2184 IF NVL(INSTR(l_field_mod,'C'),0) > 0 AND
2185 p_completion_dt IS NOT NULL THEN
2186 p_message_name := 'IGS_TR_CANNOT_SET_CMP_DATE';
2187 CLOSE c_update_trst;
2188 RETURN FALSE;
2189 END IF;
2190
2191 igs_tr_step_rec.step_completion_ind := 'N';
2192 igs_tr_step_rec.completion_dt := NULL;
2193
2194 END IF;
2195
2196 END IF;
2197
2198 IF NVL(INSTR(l_field_mod,'B'),0) > 0 THEN
2199 IF p_by_pass_ind = 'Y' THEN
2200 -- Validate new value, on error return false
2201
2202 IF NVL(INSTR(l_field_mod,'C'),0) > 0 AND
2203 p_completion_dt IS NOT NULL THEN
2204 p_message_name := 'IGS_TR_CANNOT_SET_CMP_DATE';
2205 CLOSE c_update_trst;
2206 RETURN FALSE;
2207 END IF;
2208
2209 igs_tr_step_rec.step_completion_ind := 'N';
2210 igs_tr_step_rec.by_pass_ind := 'Y';
2211 igs_tr_step_rec.completion_dt := NULL;
2212
2213 ELSIF p_by_pass_ind = 'N' THEN
2214 -- Validate new value, on error return false
2215
2216 IF NVL(INSTR(l_field_mod,'C'),0) > 0 THEN
2217 p_message_name := 'IGS_TR_CANNOT_SET_CMP_DATE';
2218 CLOSE c_update_trst;
2219 RETURN FALSE;
2220 END IF;
2221
2222 igs_tr_step_rec.by_pass_ind := 'N';
2223
2224 END IF;
2225
2226 END IF;
2227
2228 IF NVL(INSTR(l_field_mod,'A'),0) > 0 THEN
2229 -- Validate new value, on error return false
2230
2231 IF p_action_dt IS NOT NULL THEN
2232 l_end_dt := p_action_dt;
2233 IF NOT (process_date (
2234 'A',
2235 igs_tr_step_rec.tracking_id,
2236 igs_tr_step_rec.tracking_step_number,
2237 l_end_dt,
2238 l_action_days,
2239 p_message_name)) THEN
2240 CLOSE c_update_trst;
2241 RETURN FALSE;
2242 END IF;
2243 igs_tr_step_rec.action_dt := l_end_dt;
2244 igs_tr_step_rec.action_days := l_action_days;
2245 END IF;
2246 END IF;
2247
2248 IF NVL(INSTR(l_field_mod,'C'),0) > 0 THEN
2249 -- Validate new value, on error return false
2250
2251 l_end_dt := NVL(p_completion_dt,SYSDATE);
2252 IF NOT (process_date (
2253 'C',
2254 igs_tr_step_rec.tracking_id,
2255 igs_tr_step_rec.tracking_step_number,
2256 l_end_dt,
2257 l_action_days,
2258 p_message_name)) THEN
2259 CLOSE c_update_trst;
2260 RETURN FALSE;
2261 END IF;
2262 igs_tr_step_rec.completion_dt := l_end_dt;
2263 END IF;
2264
2265 IF NVL(INSTR(l_field_mod,'R'),0) > 0 THEN
2266 -- Validate new value, on error return false
2267
2268 IF NOT (igs_co_val_oc.genp_val_prsn_id(p_recipient_id, p_message_name)) THEN
2269 CLOSE c_update_trst;
2270 RETURN FALSE;
2271 ELSE
2272 igs_tr_step_rec.recipient_id := p_recipient_id;
2273 END IF;
2274 END IF;
2275
2276 END IF; -- End of sequential validation
2277
2278 -- Update the record with NOWAIT option
2279
2280 igs_tr_step_pkg.update_row(
2281 x_rowid => igs_tr_step_rec.row_id,
2282 x_tracking_id => igs_tr_step_rec.tracking_id,
2283 x_tracking_step_id => igs_tr_step_rec.tracking_step_id,
2284 x_tracking_step_number => igs_tr_step_rec.tracking_step_number,
2285 x_description => igs_tr_step_rec.description,
2286 x_s_tracking_step_type => igs_tr_step_rec.s_tracking_step_type,
2287 x_completion_dt => igs_tr_step_rec.completion_dt,
2288 x_action_days => igs_tr_step_rec.action_days,
2289 x_step_completion_ind => igs_tr_step_rec.step_completion_ind,
2290 x_by_pass_ind => igs_tr_step_rec.by_pass_ind,
2291 x_recipient_id => igs_tr_step_rec.recipient_id,
2292 x_mode => 'R',
2293 x_step_group_id => igs_tr_step_rec.step_group_id,
2294 x_step_catalog_cd => igs_tr_step_rec.step_catalog_cd,
2295 x_publish_ind => NVL(igs_tr_step_rec.publish_ind,'N')
2296 );
2297
2298 CLOSE c_update_trst;
2299
2300 --Added by Manu : The following check if the item should be marked as complete
2301 OPEN c_check_item_cmpltn;
2302 FETCH c_check_item_cmpltn INTO l_item_cmpl;
2303 IF c_check_item_cmpltn%NOTFOUND THEN
2304 -- The item should be marked complete since the steps are considered complete
2305
2306 OPEN c_dflt_trk_sts;
2307 FETCH c_dflt_trk_sts INTO l_trk_sts;
2308 IF c_dflt_trk_sts%NOTFOUND THEN
2309 CLOSE c_dflt_trk_sts;
2310 CLOSE c_check_item_cmpltn;
2311 p_message_name := 'IGS_TR_DFLT_STATUS_NOT_DEFINED';
2312 RETURN FALSE;
2313 END IF;
2314 CLOSE c_dflt_trk_sts;
2315
2316 igs_tr_item_pkg.update_row(
2317 x_mode => 'R',
2318 x_rowid => igs_tr_item_rec.row_id,
2319 x_tracking_id => igs_tr_item_rec.tracking_id,
2320 x_tracking_status => l_trk_sts,
2321 x_tracking_type => igs_tr_item_rec.tracking_type,
2322 x_source_person_id => igs_tr_item_rec.source_person_id,
2323 x_start_dt => igs_tr_item_rec.start_dt,
2324 x_target_days => igs_tr_item_rec.target_days,
2325 x_sequence_ind => igs_tr_item_rec.sequence_ind,
2326 x_business_days_ind => igs_tr_item_rec.business_days_ind,
2327 x_originator_person_id => igs_tr_item_rec.originator_person_id,
2328 x_s_created_ind => igs_tr_item_rec.s_created_ind,
2329 x_override_offset_clc_ind => igs_tr_item_rec.override_offset_clc_ind,
2330 x_completion_due_dt => igs_tr_item_rec.completion_due_dt,
2331 x_publish_ind => igs_tr_item_rec.publish_ind
2332 );
2333
2334 END IF;
2335 CLOSE c_check_item_cmpltn;
2336
2337 EXCEPTION
2338 WHEN e_resource_busy_exception THEN
2339 p_message_name := 'IGS_TR_CANNOT_UPDATE_STEP';
2340 RETURN FALSE;
2341
2342 END;
2343 RETURN TRUE;
2344
2345 EXCEPTION
2346 WHEN OTHERS THEN
2347 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2348 fnd_message.set_token('NAME','IGS_TR_GEN_002.trkp_upd_trst'||'-'||SQLERRM);
2349 igs_ge_msg_stack.ADD;
2350 lv_param_values:= TO_CHAR(p_tracking_id) ||','||TO_CHAR(p_tracking_step_id)||','||p_s_tracking_step_type;
2351 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
2352 fnd_message.set_token('VALUE',lv_param_values);
2353 igs_ge_msg_stack.ADD;
2354 lv_param_values:= igs_ge_date.igschar (p_action_dt)||','||igs_ge_date.igschar (p_completion_dt);
2355 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
2356 fnd_message.set_token('VALUE',lv_param_values);
2357 igs_ge_msg_stack.ADD;
2358 lv_param_values:= p_step_completion_ind ||','||p_by_pass_ind ||','||TO_CHAR(p_recipient_id);
2359 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
2360 fnd_message.set_token('VALUE',lv_param_values);
2361 igs_ge_msg_stack.ADD;
2362 app_exception.raise_exception;
2363
2364 END trkp_upd_trst;
2365
2366 PROCEDURE sync_trk_type_grplmt ( p_tracking_type IGS_TR_TYPE_STEP_V.tracking_type%TYPE, p_execute VARCHAR2 ) IS
2367
2368 /***********************************************************************************************************
2369
2370 Created By: pradhakr
2371 Date Created By: 11-Feb-2002
2372 Purpose: This procedure will synchronise the content of the table IGS_TR_STEP_GRP_LMT with the content
2373 of IGS_TR_TYPE_STEP_V.
2374 1. If that step group being deleted in form IGSTR001 ( block tracking step) is the last one of its
2375 kind for a particular tracking id, the step group id will be deleted from IGS_TR_TSTP_GRP_LMT
2376 else Step Group Limit will be decremented by 1.
2377 2. Any new Step Group ID being created in the form IGSTR001 ( block tracking step) will also be created
2378 in the table IGS_TR_TSTP_GRP_LMT and the Step Group Limit would be defaulted to 1.
2379
2380 Known limitations,enhancements,remarks:
2381 Change History
2382 Who When What
2383 ************************************************************************************************************/
2384
2385 --ssawhney view reference changed to table
2386 -- Decrements a step group limit
2387 CURSOR c_decrement_step_grp_lmt IS
2388 SELECT step_group_id
2389 FROM igs_tr_tstp_grp_lmt
2390 WHERE tracking_type = p_tracking_type
2391 MINUS
2392 SELECT step_group_id
2393 FROM igs_tr_type_step tts
2394 WHERE tracking_type = p_tracking_type
2395 AND tts.step_group_id IS NOT NULL
2396 GROUP BY tts.step_group_id;
2397
2398 --ssawhney view reference changed to table
2399 -- Insert a step group limit
2400 CURSOR c_insert_step_grp_lmt IS
2401 SELECT step_group_id
2402 FROM igs_tr_type_step tts
2403 WHERE tracking_type = p_tracking_type
2404 AND tts.step_group_id IS NOT NULL
2405 GROUP BY tts.step_group_id
2406 MINUS
2407 SELECT step_group_id
2408 FROM igs_tr_tstp_grp_lmt
2409 WHERE tracking_type = p_tracking_type;
2410
2411
2412 -- Cursor to fetch the Rowid
2413 CURSOR c_rowid (p_tracking_type IGS_TR_TYPE_STEP_V.tracking_type%TYPE, p_step_group_id igs_tr_tstp_grp_lmt.step_group_id%TYPE) IS
2414 SELECT ROWID
2415 FROM igs_tr_tstp_grp_lmt
2416 WHERE tracking_type = p_tracking_type
2417 AND step_group_id = p_step_group_id;
2418
2419 --ssawhney view reference changed to table
2420 -- Cursor to get the count of step group id from tracking type step
2421 CURSOR c_type_step (p_tracking_type igs_tr_type_step_v.tracking_type%TYPE) IS
2422 SELECT step_group_id, COUNT(*) step_group_count
2423 FROM igs_tr_type_step
2424 WHERE tracking_type = p_tracking_type
2425 GROUP BY step_group_id;
2426
2427 -- Cursor to get the step group limit from igs_tr_tstp_grp_lmt table
2428 CURSOR c_grp_lmt (p_tracking_type igs_tr_tstp_grp_lmt.tracking_type%TYPE, p_step_group_id igs_tr_tstp_grp_lmt.step_group_id%TYPE) IS
2429 SELECT step_group_limit
2430 FROM igs_tr_tstp_grp_lmt
2431 WHERE tracking_type = p_tracking_type
2432 AND step_group_id = p_step_group_id;
2433
2434 lv_rowid VARCHAR2(25);
2435 l_rowid VARCHAR2(25);
2436 l_step_group_limit igs_tr_tstp_grp_lmt.step_group_limit%TYPE;
2437 l_grp_lmt igs_tr_tstp_grp_lmt.step_group_limit%TYPE;
2438
2439 BEGIN
2440
2441 -- Do not execute the code if the records are being updated in group limit block
2442
2443 IF p_execute = 'N' THEN
2444 RETURN;
2445 END IF;
2446
2447 -- Delete records from IGS_TR_TSTP_GRP_LMT table when a tracking_type and step_group_id combination exists in
2448 -- IGS_TR_TSTP_GRP_LMT but does not exist in view IGS_TR_TYPE_STEP_V
2449
2450 FOR rec_decrement_step_grp_lmt IN c_decrement_step_grp_lmt LOOP
2451
2452 OPEN c_rowid(p_tracking_type, rec_decrement_step_grp_lmt.step_group_id );
2453 FETCH c_rowid INTO l_rowid;
2454 CLOSE c_rowid;
2455
2456 igs_tr_tstp_grp_lmt_pkg.delete_row ( X_ROWID => l_rowid );
2457
2458 END LOOP;
2459
2460
2461 -- Insert records into IGS_TR_TSTP_GRP_LMT when tracking_type and step_group_id combination
2462 -- exists in IGS_TR_TYPE_STEP_V but not in IGS_TR_TSTP_GRP_LMT.
2463 -- Default the value of Step_group_limit to 1.
2464
2465 FOR rec_insert_step_grp_lmt IN c_insert_step_grp_lmt LOOP
2466 igs_tr_tstp_grp_lmt_pkg.insert_row (
2467 X_ROWID => lv_rowid ,
2468 X_TRACKING_TYPE => p_tracking_type ,
2469 X_STEP_GROUP_ID => rec_insert_step_grp_lmt.step_group_id ,
2470 X_STEP_GROUP_LIMIT => 1 ,
2471 X_MODE => 'R'
2472 );
2473 END LOOP;
2474
2475 FOR rec_type_step IN c_type_step (p_tracking_type)
2476 LOOP
2477 OPEN c_grp_lmt (p_tracking_type, rec_type_step.step_group_id);
2478 FETCH c_grp_lmt INTO l_grp_lmt;
2479 CLOSE c_grp_lmt;
2480
2481 OPEN c_rowid(p_tracking_type, rec_type_step.step_group_id );
2482 FETCH c_rowid INTO l_rowid;
2483 CLOSE c_rowid;
2484
2485 -- Checking whether Group limit is greater than the count of Tracking step types for a group id
2486 IF l_grp_lmt > rec_type_step.step_group_count THEN
2487 igs_tr_tstp_grp_lmt_pkg.update_row (
2488 X_ROWID => l_rowid ,
2489 X_TRACKING_TYPE => p_tracking_type ,
2490 X_STEP_GROUP_ID => rec_type_step.step_group_id ,
2491 X_STEP_GROUP_LIMIT => rec_type_step.step_group_count ,
2492 X_MODE => 'R'
2493 );
2494 END IF;
2495 END LOOP;
2496
2497 EXCEPTION
2498 WHEN OTHERS THEN
2499 fnd_message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
2500 fnd_message.set_token('NAME','IGS_TR_GEN_002.sync_trk_type_grplmt'||'-'||SQLERRM);
2501 IGS_GE_MSG_STACK.ADD;
2502 App_Exception.Raise_Exception;
2503
2504 END sync_trk_type_grplmt;
2505
2506 PROCEDURE sync_trk_item_grplmt ( p_tracking_id IGS_TR_STEP_V.tracking_id %TYPE,
2507 p_execute VARCHAR2
2508 )
2509 IS
2510
2511 /***********************************************************************************************************
2512
2513 Created By: Arun Iyer
2514
2515 Date Created By: 11-Feb-2002
2516
2517 Purpose: This procedure would be called from the post_forms commit trigger of the Form IGSTR007 (Tracking Items ).
2518 This procedure will synchronise the content of the table IGS_TR_STEP_GRP_LMT with the content
2519 of IGS_TR_STEP_V.
2520 1. If that step group being deleted in form IGSTR007 ( block tracking step) is the last one of its
2521 kind for a particular tracking id, the step group id will be deleted from IGS_TR_STEP_GRP_LMT
2522
2523 2. Any new Step Group ID being created in the form IGSTR007 ( block tracking step) will also be created
2524 in the table IGS_TR_STEP_GRP_LMT and the Step Group Limit would be defaulted to 1.
2525
2526 3. In case IGS_TR_STEP_GRP_LMT.STEP_GROUP_LIMIT is greater than the count of step_group_id's for tracking_id and step_group_id
2527 combination in IGS_TR_STEP_V view then set it equal to the lower value (i.e count of step_group_id's in the IGS_TR_STEP_V).
2528
2529
2530 Known limitations,enhancements,remarks:
2531
2532 Change History
2533
2534 Who When What
2535 ************************************************************************************************************/
2536
2537
2538
2539 -- Fetch all the distinct step group id's for the given tracking id which are there in the table IGS_TR_STEP_GRP_LMT and not in the view IGS_TR_STEP_V
2540 -- IF such records are found then delete them from the table igs_tr_step_grp_lmt
2541 -- ssawhney view reference changed to table
2542 CURSOR c_decrement_step_grp_lmt
2543 IS
2544 SELECT
2545 step_group_id
2546 FROM
2547 igs_tr_step_grp_lmt
2548 WHERE
2549 tracking_id = p_tracking_id
2550 MINUS
2551 SELECT
2552 step_group_id
2553 FROM
2554 igs_tr_step sv
2555 WHERE
2556 tracking_id = p_tracking_id
2557 AND
2558 sv.step_group_id IS NOT NULL
2559 GROUP BY
2560 sv.step_group_id;
2561
2562
2563 -- Fetch all the distinct step group id's for the given tracking id which are there in IGS_TR_STEP and not in the table IGS_TR_STEP_GRP_LMT
2564 -- IF such records are found then insert them from the table igs_tr_step_grp_lmt with a default group_limit of 1.
2565 -- ssawhney view reference changed to table
2566 CURSOR c_insert_step_grp_lmt
2567 IS
2568 SELECT
2569 step_group_id
2570 FROM
2571 igs_tr_step sv
2572 WHERE
2573 tracking_id = p_tracking_id
2574 AND
2575 sv.step_group_id IS NOT NULL
2576 GROUP BY
2577 sv.step_group_id
2578 MINUS
2579 SELECT
2580 step_group_id
2581 FROM
2582 igs_tr_step_grp_lmt
2583 WHERE
2584 tracking_id = p_tracking_id;
2585
2586
2587 -- Cursor to fetch the Rowid from the igs_tr_step_grp_lmt table for a tracking_id, step_group_id combination
2588
2589 CURSOR c_rowid ( p_tracking_id IGS_TR_STEP_GRP_LMT.TRACKING_ID%TYPE ,
2590 p_step_group_id IGS_TR_STEP_GRP_LMT.STEP_GROUP_ID%TYPE
2591 )
2592 IS
2593 SELECT
2594 ROWID
2595 FROM
2596 igs_tr_step_grp_lmt
2597 WHERE
2598 tracking_id = p_tracking_id
2599 AND
2600 step_group_id = p_step_group_id;
2601
2602
2603 -- Cursor to get the count of step_group_id's for a tracking item step
2604 -- ssawhney view reference changed to table
2605 CURSOR c_item_step ( p_tracking_id IGS_TR_STEP_V.TRACKING_ID%TYPE )
2606 IS
2607 SELECT
2608 step_group_id,
2609 COUNT(step_group_id) step_group_count
2610 FROM
2611 igs_tr_step
2612 WHERE
2613 tracking_id = p_tracking_id
2614 AND
2615 step_group_id IS NOT NULL
2616 GROUP BY
2617 step_group_id;
2618
2619
2620 -- Cursor to get the step group limit from igs_tr_step_grp_lmt table for a combination of tracking_id and step_group_id
2621
2622 CURSOR c_grp_lmt (p_tracking_id igs_tr_step_grp_lmt.tracking_id%TYPE,
2623 p_step_group_id igs_tr_step_grp_lmt.step_group_id%TYPE)
2624 IS
2625 SELECT
2626 step_group_limit
2627 FROM
2628 igs_tr_step_grp_lmt
2629 WHERE
2630 tracking_id = p_tracking_id
2631 AND
2632 step_group_id = p_step_group_id;
2633
2634
2635 lv_rowid VARCHAR2(25);
2636 ln_step_group_limit igs_tr_step_grp_lmt.step_group_limit%TYPE;
2637 ln_grp_lmt igs_tr_step_grp_lmt.step_group_limit%TYPE;
2638
2639 BEGIN
2640
2641 /************************************ Validation 1 ***********************************************************/
2642
2643 -- Do not execute the code if the records are being updated in group limit block
2644 IF p_execute = 'N' THEN
2645 RETURN;
2646 END IF;
2647
2648
2649 /************************************ Validation 2 ***********************************************************/
2650 -- Fetch all the distinct step group id's for the given tracking id which are there in the table IGS_TR_STEP_GRP_LMT
2651 -- and not in the view IGS_TR_STEP_V.
2652 -- IF such records are found then delete them from the table igs_tr_step_grp_lmt
2653
2654 FOR rec_decrement_step_grp_lmt IN c_decrement_step_grp_lmt LOOP
2655 OPEN c_rowid(p_tracking_id, rec_decrement_step_grp_lmt.step_group_id );
2656 FETCH c_rowid INTO lv_rowid;
2657 CLOSE c_rowid;
2658 igs_tr_step_grp_lmt_pkg.delete_row ( X_ROWID => lv_rowid );
2659 END LOOP;
2660
2661
2662
2663 /************************************* Validation 3 ***********************************************************/
2664 -- Insert records into IGS_TR_STEP_GRP_LMT when tracking_id and step_group_id combination exists in IGS_TR_STEP_V
2665 -- but not in IGS_TR_STEP_GRP_LMT.
2666 -- Default the value of Step_group_limit to 1.
2667
2668 FOR rec_insert_step_grp_lmt IN c_insert_step_grp_lmt LOOP
2669 igs_tr_step_grp_lmt_pkg.insert_row (
2670 X_ROWID => lv_rowid ,
2671 X_TRACKING_ID => p_tracking_id ,
2672 X_STEP_GROUP_ID => rec_insert_step_grp_lmt.step_group_id ,
2673 X_STEP_GROUP_LIMIT => 1 ,
2674 X_MODE => 'R'
2675 );
2676 END LOOP;
2677
2678
2679 /*************************************** Validation 4 ***********************************************************/
2680 -- Check the step group limit for a combination of Tracking_Id and Step_group_id in the table IGS_TR_STEP_GRP_LMT
2681 -- and the view IGS_TR_STEP_V.
2682 -- In case IGS_TR_STEP_GRP_LMT.STEP_GROUP_LIMIT is greater than the count of step_group_id's for tracking_id and step_group_id
2683 -- combination in IGS_TR_STEP_V view then set it equal to the count of step_group_id's in the IGS_TR_STEP_V.
2684
2685 FOR rec_item_step IN c_item_step (p_tracking_id)
2686 LOOP
2687 OPEN c_grp_lmt (p_tracking_id, rec_item_step.step_group_id);
2688 FETCH c_grp_lmt INTO ln_grp_lmt;
2689 CLOSE c_grp_lmt;
2690
2691 OPEN c_rowid(p_tracking_id, rec_item_step.step_group_id );
2692 FETCH c_rowid INTO lv_rowid;
2693 CLOSE c_rowid;
2694
2695 -- Check whether Step Group limit is greater than the count of Tracking item steps for a step group id and tracking_id combination
2696 -- then set the step group limit equal to the count of step group id's in the igs)tr)step_v view for a step group id and tracking_id combination.
2697
2698 IF ln_grp_lmt > rec_item_step.step_group_count THEN
2699 igs_tr_step_grp_lmt_pkg.update_row (
2700 X_ROWID => lv_rowid ,
2701 X_TRACKING_ID => p_tracking_id ,
2702 X_STEP_GROUP_ID => rec_item_step.step_group_id ,
2703 X_STEP_GROUP_LIMIT => rec_item_step.step_group_count ,
2704 X_MODE => 'R'
2705 );
2706 END IF;
2707 END LOOP;
2708
2709 EXCEPTION
2710 WHEN OTHERS THEN
2711 fnd_message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2712 fnd_message.set_token('NAME','IGS_TR_GEN_002.sync_trk_item_grplmt'||'-'||SQLERRM);
2713 IGS_GE_MSG_STACK.ADD;
2714 App_Exception.Raise_Exception;
2715
2716 END sync_trk_item_grplmt;
2717
2718
2719 FUNCTION validate_completion_status ( p_tracking_id IN IGS_TR_STEP_V.TRACKING_ID%TYPE,
2720 p_tracking_status IN IGS_TR_ITEM_V.TRACKING_STATUS%TYPE,
2721 p_sequence_ind IN IGS_TR_ITEM_V.SEQUENCE_IND%TYPE,
2722 p_message_name OUT NOCOPY VARCHAR2
2723 )
2724 RETURN BOOLEAN
2725 IS
2726 /***********************************************************************************************************
2727
2728 Created By: Arun Iyer
2729
2730 Date Created By: 13-Feb-2002
2731
2732 Purpose: This procedure would be called from the post forms commit trigger of the form IGSTR007 (Tracking Items)
2733
2734 1. IF the user has checked the sequential flag (value = Y' ) for the current tracking id then it would
2735 checks whether all the previous steps have been completed or not. In case they have not been completed
2736 it suitable raises an error.
2737 2. If in the form the tracking status has been set to COMPLETE then this procedure validate whether
2738 the tracking status changed to COMPLETE is correct or not.
2739 In case it is incorrect then a suitable error message is returned back to the calling form
2740
2741 Known limitations,enhancements,remarks:
2742
2743 Change History
2744
2745 Who When What
2746 ************************************************************************************************************/
2747
2748 -- ssawhney view reference changed to table
2749 CURSOR c_tracking_step
2750 IS
2751 SELECT
2752 tracking_id,
2753 tracking_step_number,
2754 step_group_id
2755 FROM
2756 igs_tr_step
2757 WHERE
2758 tracking_id = p_tracking_id
2759 AND step_completion_ind = 'Y';
2760
2761 -- ssawhney view reference changed to table
2762 CURSOR
2763 c_check_item_cmpltn
2764 IS
2765 SELECT
2766 DISTINCT TO_NUMBER('1'),
2767 1
2768 FROM
2769 igs_tr_step trst
2770 WHERE
2771 trst.tracking_id = p_tracking_id
2772 AND
2773 trst.step_group_id IS NULL
2774 AND
2775 trst.step_completion_ind = 'N'
2776 AND
2777 trst.by_pass_ind = 'N'
2778 UNION
2779 (
2780 SELECT tab.step_group_id , tab.count_step
2781 FROM (
2782 SELECT
2783 DISTINCT trst.step_group_id step_group_id,
2784 COUNT(*) count_step
2785 FROM
2786 igs_tr_step trst
2787 WHERE
2788 trst.tracking_id = p_tracking_id
2789 AND
2790 trst.step_group_id IS NOT NULL
2791 AND
2792 (trst.step_completion_ind = 'Y'
2793 OR
2794 trst.by_pass_ind = 'Y')
2795 GROUP BY
2796 trst.step_group_id
2797 -- trst.step_completion_ind
2798 MINUS
2799 SELECT
2800 DISTINCT trgl.step_group_id step_group_id,
2801 step_group_limit count_step
2802 FROM
2803 igs_tr_step_grp_lmt trgl
2804 WHERE
2805 trgl.tracking_id = p_tracking_id
2806 ) tab,
2807 igs_tr_step_grp_lmt trg
2808 WHERE tab.step_group_id = trg.step_group_id
2809 AND
2810 trg.tracking_id = p_tracking_id
2811 AND
2812 tab.count_step < trg.step_group_limit
2813 );
2814
2815
2816 rec_check_item_cmpltn c_check_item_cmpltn%ROWTYPE;
2817
2818 BEGIN
2819
2820 /******************************************* Validation 1 ************************************************/
2821 -- IF the Sequence_ind check box is checked then check if all the previous steps have been completed
2822
2823 IF p_sequence_ind = 'Y' THEN
2824 FOR rec_tracking_step IN c_tracking_step LOOP
2825
2826 IF NOT trkp_prev_step_cmplt (
2827 rec_tracking_step.tracking_id,
2828 rec_tracking_step.tracking_step_number,
2829 rec_tracking_step.step_group_id,
2830 p_message_name
2831 ) THEN
2832 RETURN (FALSE);
2833 END IF;
2834 END LOOP;
2835 END IF;
2836
2837 /******************************************* Validation 2 ************************************************/
2838 -- Validate whether the tracking status changed to COMPLETE is correct or not.
2839
2840 IF p_tracking_status = 'COMPLETE' THEN
2841 OPEN c_check_item_cmpltn;
2842 FETCH c_check_item_cmpltn INTO rec_check_item_cmpltn ;
2843 IF (C_CHECK_ITEM_CMPLTN%FOUND) THEN
2844 CLOSE c_check_item_cmpltn;
2845 p_message_name := 'IGS_TR_ITST_NOT_COMPLETE';
2846 RETURN (FALSE);
2847 END IF;
2848 CLOSE c_check_item_cmpltn;
2849 END IF;
2850
2851 RETURN (TRUE);
2852
2853 EXCEPTION
2854 WHEN OTHERS THEN
2855 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2856 fnd_message.set_token('NAME','IGS_TR_GEN_002.validate_completion_status'||'-'||SQLERRM);
2857 igs_ge_msg_stack.ADD;
2858 app_exception.raise_exception;
2859
2860 END validate_completion_status;
2861
2862 END igs_tr_gen_002;
[Home] [Help]