1 PACKAGE BODY igs_tr_val_trst AS
2 /* $Header: IGSTR04B.pls 115.15 2003/05/09 14:34:24 pkpatel ship $ */
3 /*
4 Who What
5 msrinivi Bug 1956374 duplicate removal Removed genp_prc_clear_rowid,genp_val_prsn_id
6 pkkpatel Bug 2858538(Tracking step type Enhancement)
7 Modified the procedure trkp_val_stst_stt
8 */
9 -- Validate that the tracking step completion date set correctly.
10 -- for tracking dld nov 2001 release (bug 1837257) modified the cursor
11 --c_tracking_step to consider step_group also . also override_offset_clc_ind
12 -- is checked now along with sequence_ind to perform the validation
13
14 FUNCTION trkp_val_trst_cd_set(
15 p_tracking_id IN NUMBER ,
16 p_tracking_step_number IN NUMBER ,
17 p_message_name OUT NOCOPY VARCHAR2 )
18 RETURN BOOLEAN IS
19 BEGIN
20
21 DECLARE
22
23 v_sequence_ind igs_tr_item.sequence_ind%TYPE;
24 v_completion_ind NUMBER;
25 -- added for tracking dld in nov 2001 release (bug 1837257)
26 l_override_offset_clc_ind igs_tr_item_all.override_offset_clc_ind%TYPE ;
27 -- modified this cursor to select override_offset_clc_ind which is added
28 -- in the tracking dld of nov2001 release
29 CURSOR c_tracking_item( cp_tracking_id igs_tr_item.tracking_id%TYPE) IS
30 SELECT sequence_ind , override_offset_clc_ind
31 FROM igs_tr_item
32 WHERE tracking_id = cp_tracking_id;
33
34 -- modified this cursor during tracking dld nov 2001 (bug 1837257)
35 --to include logic for step_group_id
36 CURSOR c_tracking_step_prev( cp_tracking_id igs_tr_step.tracking_id%TYPE,
37 cp_tracking_step_number igs_tr_step.tracking_step_number%TYPE) IS
38 SELECT distinct to_number('1')
39 FROM igs_tr_step trst
40 WHERE trst.tracking_id = cp_tracking_id
41 AND trst.tracking_step_number < cp_tracking_step_number
42 AND trst.step_group_id is null
43 AND trst.step_completion_ind = 'N'
44 AND trst.by_pass_ind = 'N'
45 UNION
46 (SELECT distinct trst.step_group_id
47 FROM igs_tr_step trst
48 WHERE trst.tracking_id = cp_tracking_id
49 AND trst.tracking_step_number < cp_tracking_step_number
50 AND trst.step_group_id is not null
51 AND trst.step_completion_ind = 'N'
52 AND trst.by_pass_ind = 'N'
53 GROUP BY trst.step_group_id , trst.step_completion_ind
54 MINUS
55 SELECT distinct trst.step_group_id
56 FROM igs_tr_step trst
57 WHERE trst.tracking_id = cp_tracking_id
58 AND trst.tracking_step_number < cp_tracking_step_number
59 AND trst.step_group_id is not null
60 AND trst.step_completion_ind = 'Y'
61 GROUP BY trst.step_group_id , trst.step_completion_ind
62 );
63
64 -- added this cursor during tracking dld nov 2001 (bug 1837257)
65 --checks if any of the next steps are completed or can be treated as
66 --complete then this step completion_dt cannot be updated
67 CURSOR c_tracking_step_next( cp_tracking_id igs_tr_step.tracking_id%TYPE,
68 cp_tracking_step_number igs_tr_step.tracking_step_number%TYPE) IS
69 SELECT distinct to_number('1')
70 FROM igs_tr_step trst
71 WHERE trst.tracking_id = cp_tracking_id
72 AND trst.tracking_step_number > cp_tracking_step_number
73 AND trst.step_group_id is null
74 AND trst.step_completion_ind = 'Y'
75 MINUS
76 SELECT distinct to_number('1')
77 FROM igs_tr_step trst
78 WHERE trst.tracking_id = cp_tracking_id
79 AND trst.tracking_step_number = cp_tracking_step_number
80 AND trst.step_group_id is not null
81 AND trst.step_completion_ind = 'N'
82 UNION
83 (SELECT distinct trst.step_group_id
84 FROM igs_tr_step trst
85 WHERE trst.tracking_id = cp_tracking_id
86 AND trst.tracking_step_number > cp_tracking_step_number
87 AND trst.step_group_id is not null
88 AND trst.step_completion_ind = 'Y'
89 GROUP BY trst.step_group_id , trst.step_completion_ind
90 MINUS
91 SELECT distinct trst.step_group_id
92 FROM igs_tr_step trst
93 WHERE trst.tracking_id = cp_tracking_id
94 AND trst.tracking_step_number < cp_tracking_step_number
95 AND trst.step_group_id is not null
96 GROUP BY trst.step_group_id , trst.step_completion_ind
97 );
98 v_other_detail VARCHAR(255);
99 lv_param_values VARCHAR2(1080);
100
101 BEGIN
102
103 -- This module validates step_completion_ind against the
104 -- completion_dt
105 p_message_name := NULL;
106 OPEN c_tracking_item( p_tracking_id);
107 FETCH c_tracking_item INTO v_sequence_ind , l_override_offset_clc_ind ;
108 CLOSE c_tracking_item;
109 -- added condition to check for override offset clc ind also
110 IF(v_sequence_ind = 'N') OR (l_override_offset_clc_ind = 'Y') THEN
111 RETURN TRUE;
112 END IF;
113
114 IF(p_tracking_step_number > 1) THEN
115 OPEN c_tracking_step_prev( p_tracking_id, p_tracking_step_number );
116 FETCH c_tracking_step_prev INTO v_completion_ind;
117 -- modified this condition during tracking dld nov 2001 (bug 1837257)
118 --to include logic for step_group_id
119 IF (c_tracking_step_prev%FOUND) THEN
120 CLOSE c_tracking_step_prev;
121 p_message_name := 'IGS_TR_CANNOT_SET_COMPL_DATE';
122 RETURN FALSE;
123 END IF;
124 CLOSE c_tracking_step_prev;
125 END IF;
126
127 -- modified this code during build of tracking dld for nov 2001 release
128 -- bug 1837257 - this is calling the new cursor added
129 OPEN c_tracking_step_next( p_tracking_id, p_tracking_step_number);
130 FETCH c_tracking_step_next INTO v_completion_ind;
131
132 IF (c_tracking_step_next%FOUND) THEN
133 CLOSE c_tracking_step_next;
134 p_message_name := 'IGS_TR_COMPL_DT_UPD_NOT_ALLOW';
135 RETURN FALSE;
136 END IF;
137 CLOSE c_tracking_step_next;
138 RETURN TRUE;
139
140 EXCEPTION
141 WHEN OTHERS THEN
142 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
143 fnd_message.set_token('NAME','IGS_TR_VAL_TRST.trkp_val_trst_cd_set');
144 igs_ge_msg_stack.add;
145 lv_param_values:= TO_CHAR(p_tracking_id)||','||TO_CHAR(p_tracking_step_number);
146 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
147 fnd_message.set_token('VALUE',lv_param_values);
148 igs_ge_msg_stack.add;
149 app_exception.raise_exception;
150 END;
151 END trkp_val_trst_cd_set;
152
153 -- Validate the step completion indicator against step completion date
154 FUNCTION trkp_val_trst_sci_cd(
155 p_step_completion_ind IN VARCHAR2 DEFAULT 'N',
156 p_completion_dt IN DATE ,
157 p_by_pass_ind IN VARCHAR2 DEFAULT 'N',
158 p_message_name OUT NOCOPY VARCHAR2 )
159 RETURN BOOLEAN IS
160 BEGIN
161 DECLARE
162
163 v_other_detail VARCHAR(255);
164
165 BEGIN
166
167 -- This module validates step_completion_ind against the
168 -- completion_dt
169
170 p_message_name := NULL;
171
172 IF(p_step_completion_ind = 'Y' AND p_completion_dt IS NULL) THEN
173 p_message_name := 'IGS_TR_COMPL_DT_MUST_BE_SET';
174 RETURN FALSE;
175 END IF;
176
177 IF(p_step_completion_ind = 'N' AND p_completion_dt IS NOT NULL) THEN
178 p_message_name := 'IGS_TR_CHECK_COMPL_DATE';
179 RETURN FALSE;
180 END IF;
181
182 IF(p_by_pass_ind = 'Y' AND p_step_completion_ind = 'Y') THEN
183 p_message_name := 'IGS_TR_INDICAT_CANNOT_BE_SET';
184 RETURN FALSE;
185 END IF;
186
187 RETURN TRUE;
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 NULL;
192 END;
193
194 END trkp_val_trst_sci_cd;
195
196
197 -- Validate the system tracking step type within the tracking type.
198 FUNCTION trkp_val_stst_stt(
199 p_s_tracking_step_type IN VARCHAR2 ,
200 p_tracking_type IN VARCHAR2 ,
201 p_message_name OUT NOCOPY VARCHAR2 )
202 RETURN BOOLEAN IS
203 /*
204 WHO WHEN WHAT
205 pkpatel 25-APR-2003 Bug 2858538(Tracking step type Enhancement)
206 Modified the cursor to validate system tracking step type within the tracking type.
207 */
208 gv_other_detail VARCHAR(255);
209 lv_param_values VARCHAR2(1080);
210 l_s_tracking_type igs_tr_type.s_tracking_type%TYPE;
211 l_dummy VARCHAR2(1);
212
213 CURSOR tracking_type_cur IS
214 SELECT s_tracking_type
215 FROM igs_tr_type
216 WHERE tracking_type = p_tracking_type;
217
218 CURSOR step_type_cur(cp_s_tracking_type igs_tr_type.s_tracking_type%TYPE) IS
219 SELECT 'X'
220 FROM igs_lookup_values
221 WHERE lookup_type = p_s_tracking_step_type AND
222 lookup_code = cp_s_tracking_type;
223
224 BEGIN
225
226 -- This module checks that s_tracking_step_type is valid for
227 -- the s_tracking_type.
228 p_message_name := NULL;
229
230 -- Find the System Tracking Type
231 OPEN tracking_type_cur;
232 FETCH tracking_type_cur INTO l_s_tracking_type;
233 IF tracking_type_cur%NOTFOUND THEN
234 CLOSE tracking_type_cur;
235 p_message_name := 'IGS_TR_STEP_TYPE_INVALID';
236 RETURN FALSE;
237 END IF;
238 CLOSE tracking_type_cur;
239
240 -- Validate whether the System Tracking Type and the system tracking step type are associated.
241 OPEN step_type_cur(l_s_tracking_type);
242 FETCH step_type_cur INTO l_dummy;
243 IF step_type_cur%NOTFOUND THEN
244 CLOSE step_type_cur;
245 p_message_name := 'IGS_TR_STEP_TYPE_INVALID';
246 RETURN FALSE;
247 END IF;
248 CLOSE step_type_cur;
249
250
251 RETURN TRUE;
252
253 EXCEPTION
254 WHEN OTHERS THEN
255 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
256 fnd_message.set_token('NAME','IGS_TR_VAL_TRST.trkp_val_stst_stt'||'-'||SQLERRM);
257 igs_ge_msg_stack.add;
258 lv_param_values:=p_s_tracking_step_type||','||p_tracking_type;
259 fnd_message.set_name('IGS','IGS_GE_PARAMETERS');
260 fnd_message.set_token('VALUE',lv_param_values);
261 igs_ge_msg_stack.add;
262 app_exception.raise_exception;
263 END trkp_val_stst_stt;
264
265 END igs_tr_val_trst;
[Home] [Help]