1 PACKAGE BODY igs_tr_gen_001 AS
2 /* $Header: IGSTR01B.pls 115.8 2002/11/29 04:18:08 nsidana ship $ */
3
4 FUNCTION trkp_clc_action_dt(
5 p_tracking_id IN NUMBER ,
6 p_tracking_step_number IN NUMBER ,
7 p_start_dt IN DATE ,
8 p_sequence_ind IN VARCHAR2 DEFAULT 'N',
9 p_business_days_ind IN VARCHAR2 DEFAULT 'N')
10 RETURN DATE IS
11 gv_other_detail VARCHAR2(255);
12 v_start_dt igs_tr_item.start_dt%TYPE;
13 v_action_days igs_tr_step.action_days%TYPE;
14 v_action_dt DATE;
15 v_prev_completion_dt DATE;
16 -- new variables added during tracking dld for nov 2001 release bug(1837257)
17 -- added a new cursor to get the override offset indicator value for the item
18 l_override_offset_clc_ind igs_tr_item_all.override_offset_clc_ind%TYPE ;
19 l_completion_due_dt igs_tr_item_all.completion_due_dt%TYPE ;
20 BEGIN
21
22 DECLARE
23
24 CURSOR c_tracking_step( cp_tracking_id igs_tr_step.tracking_id%TYPE, cp_tracking_step_number igs_tr_step.tracking_step_number%TYPE) IS
25 SELECT ts.action_days, ts.completion_dt, ts.by_pass_ind
26 FROM igs_tr_step ts
27 WHERE ts.tracking_id = cp_tracking_id
28 AND ts.tracking_step_number <= cp_tracking_step_number
29 ORDER BY ts.tracking_step_number;
30
31 CURSOR c_tracking_step_1( cp_tracking_id igs_tr_step.tracking_id%TYPE, cp_tracking_step_number igs_tr_step.tracking_step_number%TYPE) IS
32 SELECT ts.action_days
33 FROM igs_tr_step ts
34 WHERE ts.tracking_id = cp_tracking_id
35 AND ts.tracking_step_number = cp_tracking_step_number;
36 --added for tracking dld - nov 2001
37 CURSOR c_tracking_item IS
38 SELECT NVL(override_offset_clc_ind , 'N'), completion_due_dt
39 FROM igs_tr_item
40 WHERE tracking_id = p_tracking_id ;
41
42 BEGIN
43 OPEN c_tracking_item ;
44 FETCH c_tracking_item INTO l_override_offset_clc_ind , l_completion_due_dt ;
45 CLOSE c_tracking_item ;
46 -- if override offset clc ind is yes then action date is equal to completion due date
47 IF l_override_offset_clc_ind = 'Y' THEN
48 v_action_dt := l_completion_due_dt ;
49
50 -- Calculates the action date for a tracking step.
51 ELSIF (p_sequence_ind = 'Y') THEN
52 v_start_dt := p_start_dt;
53 v_action_dt := NULL;
54 v_prev_completion_dt := NULL;
55
56 FOR v_tracking_step_rec IN c_tracking_step(
57 p_tracking_id,
58 p_tracking_step_number) LOOP
59
60 -- If the previous steps completion date has been set
61 -- then need to calculate the action date from that date.
62 IF v_prev_completion_dt IS NOT NULL THEN
63 v_start_dt := v_prev_completion_dt;
64 END IF;
65
66 v_action_dt := trkp_clc_dt_offset(
67 v_start_dt,
68 v_tracking_step_rec.action_days,
69 p_business_days_ind);
70
71 IF v_tracking_step_rec.by_pass_ind = 'N' THEN
72 v_prev_completion_dt := v_tracking_step_rec.completion_dt;
73 v_start_dt := v_action_dt;
74 END IF;
75 END LOOP;
76
77 ELSE
78 OPEN c_tracking_step_1(
79 p_tracking_id,
80 p_tracking_step_number);
81 FETCH c_tracking_step_1 INTO v_action_days;
82 CLOSE c_tracking_step_1;
83 v_action_dt := trkp_clc_dt_offset(
84 p_start_dt,
85 v_action_days,
86 p_business_days_ind);
87 END IF;
88
89 RETURN v_action_dt;
90
91 EXCEPTION WHEN OTHERS THEN
92 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
93
94 END;
95
96 END trkp_clc_action_dt;
97
98 FUNCTION trkp_clc_bus_dt(
99 p_start_dt IN DATE ,
100 p_business_days IN NUMBER )
101 RETURN DATE IS
102 BEGIN
103
104 DECLARE
105 v_return_dt DATE;
106 v_start_dt DATE;
107 v_other_detail VARCHAR(255);
108 v_message_name VARCHAR2(30);
109 v_cntr NUMBER;
110
111 BEGIN
112
113 -- This module based on an initial date plus a number of business days
114 -- determines the new date
115 IF(p_business_days = 0) THEN
116 RETURN p_start_dt;
117 END IF;
118
119 v_return_dt := p_start_dt;
120 v_start_dt := p_start_dt + 1;
121 v_cntr := 1;
122
123 WHILE v_cntr <= p_business_days LOOP
124 IF(igs_tr_val_tri.genp_val_bus_day(v_start_dt,'N','N', v_message_name)) THEN
125 v_return_dt := v_start_dt;
126 v_cntr := v_cntr + 1;
127 END IF;
128 v_start_dt := v_start_dt + 1;
129 END LOOP;
130
131 WHILE NOT igs_tr_val_tri.genp_val_bus_day(v_return_dt,'N','N', v_message_name) LOOP
132 v_return_dt := v_return_dt + 1;
133 END LOOP;
134
135 RETURN v_return_dt;
136
137 END;
138
139 END trkp_clc_bus_dt;
140
141 FUNCTION trkp_clc_days_ovrdue(
142 p_action_dt IN DATE ,
143 p_completion_dt IN DATE ,
144 p_business_days_ind IN VARCHAR2 DEFAULT 'N'
145 )RETURN NUMBER IS
146
147 gv_other_detail VARCHAR2(255);
148
149 BEGIN
150
151 DECLARE
152 v_completion_dt igs_tr_step.completion_dt%TYPE;
153 v_ovrdue_days NUMBER;
154 v_message_name VARCHAR2(30);
155
156 BEGIN
157
158 -- Determine the number of days that a tracking object is overdue.
159 IF p_completion_dt IS NULL THEN
160 v_completion_dt := SYSDATE;
161 ELSE
162 v_completion_dt := p_completion_dt;
163 END IF;
164
165 IF v_completion_dt <= p_action_dt THEN
166 RETURN 0;
167 END IF;
168
169 IF p_business_days_ind = 'Y' THEN
170 v_ovrdue_days := trkp_clc_num_bus_day( p_action_dt, v_completion_dt, v_message_name);
171 ELSE
172 v_ovrdue_days := TO_NUMBER(TO_CHAR(v_completion_dt, 'J')) - TO_NUMBER(TO_CHAR(p_action_dt, 'J'));
173 END IF;
174 RETURN v_ovrdue_days;
175
176 END;
177
178 END trkp_clc_days_ovrdue;
179
180 FUNCTION trkp_clc_dt_offset(
181 p_start_dt IN DATE ,
182 p_offset_days IN NUMBER ,
183 p_business_days_ind IN VARCHAR2 DEFAULT 'N' ,
184 -- Tracking dld nov 2001 , bug#1837257 added new parameter p_override_offset_clc_ind
185 -- if override ind is set then no need to calculate offset date
186 p_override_offset_clc_ind IN varchar2 DEFAULT 'N'
187 )RETURN DATE IS
188
189 gv_other_detail VARCHAR2(255);
190
191 BEGIN
192
193 DECLARE
194 BEGIN
195 -- do not process if override offset clc ind is yes
196 IF (p_override_offset_clc_ind = 'N' ) THEN
197 -- Determine the date from a start date, plus and offset, allowing
198 -- for business days only if the p_business_days_ind is set.
199 IF (p_business_days_ind = 'Y') THEN
200 RETURN trkp_clc_bus_dt( p_start_dt, p_offset_days);
201 ELSE
202 RETURN p_start_dt + p_offset_days;
203 END IF;
204 ELSE
205 RETURN NULL ;
206 END IF;
207
208 END;
209
210 END trkp_clc_dt_offset;
211
212 FUNCTION trkp_clc_num_bus_day(
213 p_start_dt IN DATE ,
214 p_end_dt IN DATE ,
215 p_message_name OUT NOCOPY VARCHAR2
216 ) RETURN NUMBER IS
217
218 BEGIN
219
220 DECLARE
221 v_end_dt DATE;
222 v_message_name VARCHAR2(30);
223 v_day_diff NUMBER;
224 v_day_count NUMBER;
225 v_other_detail VARCHAR(255);
226
227 BEGIN
228
229 -- This module determines the number of business days between
230 -- two dates. (The start date is not inclusive where the end date is
231 -- inclusive)
232 p_message_name := NULL;
233 IF(p_start_dt = p_end_dt) THEN
234 RETURN 0;
235 END IF;
236
237 IF(p_start_dt > p_end_dt) THEN
238 p_message_name := 'IGS_GE_INVALID_DATE';
239 RETURN 0;
240 END IF;
241
242 v_end_dt := TRUNC(p_end_dt);
243 v_day_diff := TRUNC(p_end_dt) - TRUNC(p_start_dt);
244 v_day_count := 0;
245
246 FOR i IN 1..v_day_diff LOOP
247 IF(igs_tr_val_tri.genp_val_bus_day(v_end_dt,'N', 'N', v_message_name)) THEN
248 v_day_count := v_day_count + 1;
249 END IF;
250 v_end_dt := v_end_dt - 1;
251 END LOOP;
252
253 RETURN v_day_count;
254
255 EXCEPTION
256 WHEN OTHERS THEN
257 RAISE;
258
259 END;
260
261 END trkp_clc_num_bus_day;
262
263 FUNCTION trkp_clc_tri_cmp_dt(
264 p_tracking_id IN NUMBER ,
265 p_start_dt IN DATE )
266 RETURN DATE IS
267 gv_other_detail VARCHAR2(255);
268
269 BEGIN
270
271 DECLARE
272 cst_active VARCHAR2(6) := 'ACTIVE';
273 v_completion_dt igs_tr_step.completion_dt%TYPE;
274 CURSOR c_tracking_step( cp_tracking_id igs_tr_step.tracking_id%TYPE) IS
275 SELECT MAX(ts.completion_dt)
276 FROM igs_tr_step ts
277 WHERE ts.tracking_id = cp_tracking_id;
278
279 BEGIN
280
281 -- If the tracking item is still active then there is no completion date for
282 -- the item.
283
284 IF (igs_tr_gen_002.trkp_get_item_status( p_tracking_id) = cst_active) THEN
285 RETURN NULL;
286
287 ELSE
288 -- Determine the maximum completion date of the steps for the item.
289 -- If a null date, then all steps may have been by-passed or no steps exist.
290 -- Return the start date.
291 OPEN c_tracking_step(p_tracking_id);
292 FETCH c_tracking_step INTO v_completion_dt;
293 CLOSE c_tracking_step;
294 IF v_completion_dt IS NULL THEN
295 RETURN p_start_dt;
296 ELSE
297 RETURN v_completion_dt;
298 END IF;
299 END IF;
300
301 END;
302
303 END trkp_clc_tri_cmp_dt;
304
305 END igs_tr_gen_001;
[Home] [Help]