[Home] [Help]
PACKAGE BODY: APPS.GMD_ROUTINGS_PVT
Source
1 PACKAGE BODY GMD_ROUTINGS_PVT AS
2 /* $Header: GMDVROUB.pls 120.6 2010/11/04 20:12:18 rnalla ship $ */
3
4
5 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
6 --Forward declaration.
7 FUNCTION set_debug_flag RETURN VARCHAR2;
8 l_debug VARCHAR2(1) := set_debug_flag;
9
10 FUNCTION set_debug_flag RETURN VARCHAR2 IS
11 l_debug VARCHAR2(1):= 'N';
12 BEGIN
13 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
14 l_debug := 'Y';
15 END IF;
16 RETURN l_debug;
17 END set_debug_flag;
18 --Bug 3222090, NSRIVAST 20-FEB-2004, END
19
20 /* =============================================================== */
21 /* Procedure: */
22 /* insert_routing */
23 /* */
24 /* DESCRIPTION: */
25 /* */
26 /* API returns (x_return_code) = 'S' if the insert into routing */
27 /* header (fm_rout_hdr or gmd_routings) table is successfully. */
28 /* */
29 /* History : */
30 /* Shyam 07/29/2002 Initial implementation */
31 /* =============================================================== */
32 PROCEDURE insert_routing
33 ( p_routings IN gmd_routings%ROWTYPE
34 , x_message_count OUT NOCOPY NUMBER
35 , x_message_list OUT NOCOPY VARCHAR2
36 , x_return_status OUT NOCOPY VARCHAR2
37 ) IS
38
39 /* Local variable section */
40 l_row_id ROWID;
41 l_routing_id NUMBER;
42 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROUTING';
43
44 /* get routing id sequence */
45 CURSOR Get_routing_id_seq IS
46 SELECT gem5_routing_id_s.NEXTVAL
47 FROM sys.dual;
48
49 /* Define Exceptions */
50 routing_creation_failure EXCEPTION;
51 invalid_version EXCEPTION;
52 setup_failure EXCEPTION;
53
54 BEGIN
55
56 /* Intialize the setup fields */
57 IF NOT gmd_api_grp.setup_done THEN
58 gmd_api_grp.setup_done := gmd_api_grp.setup;
59 END IF;
60 IF NOT gmd_api_grp.setup_done THEN
61 RAISE setup_failure;
62 END IF;
63 /* Set the return status to success initially */
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65
66
67 IF p_routings.routing_id IS NOT NULL THEN
68
69 /* Step : Create Routing header */
70 IF (l_debug = 'Y') THEN
71 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
72 ||'Inserting the routing header with routing id '||l_routing_id);
73 END IF;
74
75 GMD_ROUTINGS_PKG.insert_row(
76 x_rowid => l_row_id,
77 x_routing_id => p_routings.routing_id,
78 x_routing_no => p_routings.routing_no,
79 x_routing_vers => p_routings.routing_vers,
80 x_routing_status => '100',
81 x_routing_desc => p_routings.routing_desc,
82 x_routing_class => p_routings.routing_class,
83 x_routing_qty => p_routings.routing_qty,
84 x_routing_uom => p_routings.routing_uom,
85 x_owner_organization_id => p_routings.owner_organization_id,
86 x_delete_mark => 0,
87 x_text_code => p_routings.text_code,
88 x_inactive_ind => 0,
89 x_enforce_step_dependency => p_routings.enforce_step_dependency,
90 x_contiguous_ind => p_routings.contiguous_ind,
91 x_in_use => p_routings.in_use,
92 x_attribute1 => p_routings.attribute1,
93 x_attribute2 => p_routings.attribute2,
94 x_attribute3 => p_routings.attribute3,
95 x_attribute4 => p_routings.attribute4,
96 x_attribute5 => p_routings.attribute5,
97 x_attribute6 => p_routings.attribute6,
98 x_attribute7 => p_routings.attribute7,
99 x_attribute8 => p_routings.attribute8,
100 x_attribute9 => p_routings.attribute9,
101 x_attribute10 => p_routings.attribute10,
102 x_attribute11 => p_routings.attribute11,
103 x_attribute12 => p_routings.attribute12,
104 x_attribute13 => p_routings.attribute13,
105 x_attribute14 => p_routings.attribute14,
106 x_attribute15 => p_routings.attribute15,
107 x_attribute16 => p_routings.attribute16,
108 x_attribute17 => p_routings.attribute17,
109 x_attribute18 => p_routings.attribute18,
110 x_attribute19 => p_routings.attribute19,
111 x_attribute20 => p_routings.attribute20,
112 x_attribute21 => p_routings.attribute21,
113 x_attribute22 => p_routings.attribute22,
114 x_attribute23 => p_routings.attribute23,
115 x_attribute24 => p_routings.attribute24,
116 x_attribute25 => p_routings.attribute25,
117 x_attribute26 => p_routings.attribute26,
118 x_attribute27 => p_routings.attribute27,
119 x_attribute28 => p_routings.attribute28,
120 x_attribute29 => p_routings.attribute29,
121 x_attribute30 => p_routings.attribute30,
122 x_attribute_category => p_routings.attribute_category,
123 x_effective_start_date => p_routings.effective_start_date,
124 x_effective_end_date => p_routings.effective_end_date,
125 x_owner_id => p_routings.owner_id,
126 x_project_id => p_routings.project_id,
127 x_process_loss => p_routings.process_loss,
128 x_creation_date => NVL(p_routings.creation_date,SYSDATE),
129 x_created_by => gmd_api_grp.user_id,
130 x_last_update_date => NVL(p_routings.last_update_date,SYSDATE),
131 x_last_updated_by => gmd_api_grp.user_id,
132 x_last_update_login => p_routings.last_update_login,
133 x_fixed_process_loss => p_routings.fixed_process_loss, /* RLNAGARA B6997624*/
134 x_fixed_process_loss_uom => p_routings.fixed_process_loss_uom /* RLNAGARA B6997624*/
135 );
136
137 END IF; /* l_routing_id IS NOT NULL */
138
139 -- Check if routing header was created
140 IF (l_debug = 'Y') THEN
141 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||
142 'Row id value after inserting routing is '||l_row_id);
143 END IF;
144 IF l_row_id IS NULL THEN
145 RAISE routing_creation_failure;
146 END IF;
147 /* Get the messgae list and count generated by this API */
148 fnd_msg_pub.count_and_get (
149 p_count => x_message_count
150 ,p_encoded => FND_API.g_false
151 ,p_data => x_message_list);
152
153 IF (l_debug = 'Y') THEN
154 gmd_debug.put_line('Completed '||l_api_name ||' at '
155 ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
156 END IF;
157 EXCEPTION
158 WHEN routing_creation_failure OR invalid_version THEN
159 IF (l_debug = 'Y') THEN
160 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
161 END IF;
162 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
163 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
164 FND_MSG_PUB.ADD;
165 x_return_status := FND_API.G_RET_STS_ERROR;
166 fnd_msg_pub.count_and_get (
167 p_count => x_message_count
168 ,p_encoded => FND_API.g_false
169 ,p_data => x_message_list);
170 WHEN setup_failure THEN
171 x_return_status := FND_API.G_RET_STS_ERROR;
172 fnd_msg_pub.count_and_get (
173 p_count => x_message_count
174 ,p_encoded => FND_API.g_false
175 ,p_data => x_message_list);
176 WHEN OTHERS THEN
177 IF (l_debug = 'Y') THEN
178 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
179 END IF;
180 fnd_msg_pub.add_exc_msg (gmd_routings_PUB.m_pkg_name, l_api_name);
181 x_return_status := FND_API.g_ret_sts_unexp_error;
182 fnd_msg_pub.count_and_get (
183 p_count => x_message_count
184 ,p_encoded => FND_API.g_false
185 ,p_data => x_message_list);
186 END insert_routing;
187
188 /* =============================================================== */
189 /* Procedure: */
190 /* update_routing */
191 /* */
192 /* DESCRIPTION: */
193 /* */
194 /* API returns (x_return_code) = 'S' if the update into routing */
195 /* header (fm_rout_hdr or gmd_routings) table is successfully. */
196 /* */
197 /* History : */
198 /* Shyam 07/29/2002 Initial implementation */
199 /* Kalyani 06/06/2006 BUG 5197863 Moved existing code to new */
200 /* function validate dates */
201 /* RLNAGARA 25-Apr-2008 B6997624 Added Fixed Process Loss and UOM */
202 /* =============================================================== */
203 PROCEDURE update_routing
204 ( p_routing_id IN gmd_routings.routing_id%TYPE := NULL
205 , p_update_table IN gmd_routings_pub.update_tbl_type
206 , x_message_count OUT NOCOPY NUMBER
207 , x_message_list OUT NOCOPY VARCHAR2
208 , x_return_status OUT NOCOPY VARCHAR2
209 ) IS
210
211 /* Local variable section */
212 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ROUTING';
213 l_routingStep_id fm_rout_dtl.routingStep_id%TYPE;
214 l_steprow NUMBER := 0;
215 l_db_date DATE;
216
217 l_oprn_start_date DATE;
218 l_oprn_end_date DATE;
219 l_vr_start_date DATE;
220 l_vr_end_date DATE;
221
222 /* Define record type that hold the routing data */
223 l_old_routing_rec gmd_routings%ROWTYPE;
224
225 /* Table type defn */
226 l_stepupdate_table gmd_routings_pub.update_tbl_type;
227
228 /* BUG 5197863 Added l_ret */
229 l_ret NUMBER;
230
231 /* Define Exceptions */
232 routing_update_failure EXCEPTION;
233 last_update_date_failure EXCEPTION;
234 invalid_version EXCEPTION;
235 setup_failure EXCEPTION;
236
237 /* Define cursor section */
238 CURSOR get_old_routing_rec(vRouting_id gmd_routings.routing_id%TYPE) IS
239 Select *
240 From gmd_routings
241 Where Routing_id = vRouting_id;
242
243 CURSOR get_nonmanual_step_release(vRouting_id gmd_routings.routing_id%TYPE) IS
244 Select routingstep_id
245 From fm_rout_dtl
246 Where Routing_id = vRouting_id
247 And steprelease_type <> 1;
248
249 CURSOR Get_db_last_update_date(vRouting_id gmd_routings.routing_id%TYPE) IS
250 Select last_update_date
251 From gmd_routings_b
252 Where Routing_id = vRouting_id;
253
254 CURSOR Get_oprn_start_end_dates(vRouting_id NUMBER) IS
255 SELECT max(effective_start_date) effective_start_date
256 , min(effective_end_date) effective_end_date
257 FROM gmd_operations_b o, fm_rout_dtl d
258 WHERE o.oprn_id = d.oprn_id
259 AND d.routing_id = vRouting_id
260 AND o.delete_mark = 0;
261
262 CURSOR Get_vr_start_end_dates(vRouting_id NUMBER) IS
263 Select min(vr.Start_Date) Start_Date ,
264 max(NVL(vr.End_Date, trunc(SYSDATE + 999999) ) ) End_Date
265 From gmd_routings_b rt, gmd_recipes_b rc,
266 gmd_recipe_validity_rules vr
267 Where vr.recipe_id = rc.recipe_id AND
268 ((rc.routing_id IS NOT NULL) AND (rc.routing_id = rt.routing_id)) AND
269 rt.routing_id = vRouting_id AND
270 vr.delete_mark = 0;
271
272 BEGIN
273 /* Intialize the setup fields */
274 IF NOT gmd_api_grp.setup_done THEN
275 gmd_api_grp.setup_done := gmd_api_grp.setup;
276 END IF;
277 IF NOT gmd_api_grp.setup_done THEN
278 RAISE setup_failure;
279 END IF;
280
281 /* Set the return status to success initially */
282 x_return_status := FND_API.G_RET_STS_SUCCESS;
283 /* Get the old routing rec value */
284 OPEN get_old_routing_rec(p_routing_id);
285 FETCH get_old_routing_rec INTO l_old_routing_rec;
286 IF get_old_routing_rec%NOTFOUND THEN
287 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
288 FND_MSG_PUB.ADD;
289 CLOSE get_old_routing_rec;
290 RAISE routing_update_failure;
291 END IF;
292 CLOSE get_old_routing_rec;
293
294 /* Loop thro' every column in p_update_table table and for each column name
295 assign or replace the old value with the table value */
296 FOR i IN 1 .. p_update_table.count LOOP
297 IF (UPPER(p_update_table(i).p_col_to_update) = 'OWNER_ORGANIZATION_ID') THEN
298 l_old_routing_rec.owner_organization_id := p_update_table(i).p_value;
299 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'OWNER_ID') THEN
300 l_old_routing_rec.OWNER_ID := p_update_table(i).p_value;
301 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ROUTING_CLASS') THEN
302 l_old_routing_rec.ROUTING_CLASS := p_update_table(i).p_value;
303 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ROUTING_QTY') THEN
304 l_old_routing_rec.ROUTING_QTY := p_update_table(i).p_value;
305 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ROUTING_UOM') THEN
306 l_old_routing_rec.routing_uom := p_update_table(i).p_value;
307 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'TEXT_CODE') THEN
308 l_old_routing_rec.TEXT_CODE := p_update_table(i).p_value;
309 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'INACTIVE_IND') THEN
310 l_old_routing_rec.INACTIVE_IND := p_update_table(i).p_value;
311 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'CONTIGUOUS_IND') THEN
312 l_old_routing_rec.CONTIGUOUS_IND := p_update_table(i).p_value;
313 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ENFORCE_STEP_DEPENDENCY') THEN
314 l_old_routing_rec.ENFORCE_STEP_DEPENDENCY := p_update_table(i).p_value;
315 /* Validation: If the updated value for Enforce Step Dependency is 1,
316 and if the step release is not set to manual then we need to call
317 the update routing step API to update the step release type for
318 all its routing steps */
319 IF l_old_routing_rec.ENFORCE_STEP_DEPENDENCY = 1 THEN
320 FOR step_release_rec IN get_nonmanual_step_release(p_routing_id) LOOP
321 l_steprow := l_steprow + 1;
322 l_routingStep_id := step_release_rec.routingstep_id;
323 l_stepupdate_table(l_steprow).P_COL_TO_UPDATE := 'STEPRELEASE_TYPE';
324 l_stepupdate_table(l_steprow).P_VALUE := '1';
325 END LOOP;
326 IF l_steprow > 0 THEN
327 GMD_ROUTING_STEPS_PVT.update_routing_steps
328 ( p_routingstep_id => l_routingStep_id
329 , p_update_table => l_stepupdate_table
330 , x_return_status => x_return_status
331 );
332 END IF; /* l_steprow > 0 */
333 END IF; /* l_old_routing_rec.ENFORCE_STEP_DEPENDENCY := 1 */
334 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'IN_USE') THEN
335 l_old_routing_rec.IN_USE := p_update_table(i).p_value;
336 ELSIF (UPPER(p_update_table(i).p_col_to_update) like '%START_DATE%') THEN
337 IF (l_debug = 'Y') THEN
338 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
339 ||'The eff_start_date for Routing prior to update = '||
340 p_update_table(i).p_value);
341 END IF;
342 l_old_routing_rec.EFFECTIVE_START_DATE
343 := FND_DATE.canonical_to_date(p_update_table(i).p_value);
344 ELSIF (UPPER(p_update_table(i).p_col_to_update) like '%END_DATE%') THEN
345 IF (l_debug = 'Y') THEN
346 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
347 ||'The eff_end_date for Routing prior to update = '||
348 p_update_table(i).p_value);
349 END IF;
350
351 l_old_routing_rec.EFFECTIVE_END_DATE
352 := FND_DATE.canonical_to_date(p_update_table(i).p_value);
353 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'PROCESS_LOSS') THEN
354 l_old_routing_rec.PROCESS_LOSS := p_update_table(i).p_value;
355 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'FIXED_PROCESS_LOSS') THEN --RLNAGARA B6997624
356 l_old_routing_rec.FIXED_PROCESS_LOSS := p_update_table(i).p_value;
357 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'FIXED_PROCESS_LOSS_UOM') THEN --RLNAGARA B6997624
358 l_old_routing_rec.FIXED_PROCESS_LOSS_UOM := p_update_table(i).p_value;
359 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ROUTING_DESC') THEN
360 l_old_routing_rec.ROUTING_DESC := p_update_table(i).p_value;
361 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'DELETE_MARK') THEN
362 l_old_routing_rec.DELETE_MARK := p_update_table(i).p_value;
363 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATED_BY') THEN
364 l_old_routing_rec.LAST_UPDATED_BY := gmd_api_grp.user_id;
365 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATE_DATE') THEN
366 l_old_routing_rec.LAST_UPDATE_DATE := FND_DATE.canonical_to_date(p_update_table(i).p_value);
367 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATE_LOGIN') THEN
368 l_old_routing_rec.LAST_UPDATE_LOGIN := gmd_api_grp.user_id;
369 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE1') THEN
370 l_old_routing_rec.ATTRIBUTE1 := p_update_table(i).p_value;
371 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE2') THEN
372 l_old_routing_rec.ATTRIBUTE2 := p_update_table(i).p_value;
373 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE3') THEN
374 l_old_routing_rec.ATTRIBUTE3 := p_update_table(i).p_value;
375 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE4') THEN
376 l_old_routing_rec.ATTRIBUTE4 := p_update_table(i).p_value;
377 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE5') THEN
378 l_old_routing_rec.ATTRIBUTE5 := p_update_table(i).p_value;
379 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE6') THEN
380 l_old_routing_rec.ATTRIBUTE6 := p_update_table(i).p_value;
381 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE7') THEN
382 l_old_routing_rec.ATTRIBUTE7 := p_update_table(i).p_value;
383 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE8') THEN
384 l_old_routing_rec.ATTRIBUTE8 := p_update_table(i).p_value;
385 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE9') THEN
386 l_old_routing_rec.ATTRIBUTE9 := p_update_table(i).p_value;
387 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE10') THEN
388 l_old_routing_rec.ATTRIBUTE10 := p_update_table(i).p_value;
389 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE11') THEN
390 l_old_routing_rec.ATTRIBUTE11 := p_update_table(i).p_value;
391 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE12') THEN
392 l_old_routing_rec.ATTRIBUTE12 := p_update_table(i).p_value;
393 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE13') THEN
394 l_old_routing_rec.ATTRIBUTE13 := p_update_table(i).p_value;
395 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE14') THEN
396 l_old_routing_rec.ATTRIBUTE14 := p_update_table(i).p_value;
397 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE15') THEN
398 l_old_routing_rec.ATTRIBUTE15 := p_update_table(i).p_value;
399 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE16') THEN
400 l_old_routing_rec.ATTRIBUTE16 := p_update_table(i).p_value;
401 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE17') THEN
402 l_old_routing_rec.ATTRIBUTE17 := p_update_table(i).p_value;
403 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE18') THEN
404 l_old_routing_rec.ATTRIBUTE18 := p_update_table(i).p_value;
405 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE19') THEN
406 l_old_routing_rec.ATTRIBUTE19 := p_update_table(i).p_value;
407 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE20') THEN
408 l_old_routing_rec.ATTRIBUTE20 := p_update_table(i).p_value;
409 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE21') THEN
410 l_old_routing_rec.ATTRIBUTE21 := p_update_table(i).p_value;
411 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE22') THEN
412 l_old_routing_rec.ATTRIBUTE22 := p_update_table(i).p_value;
413 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE23') THEN
414 l_old_routing_rec.ATTRIBUTE23 := p_update_table(i).p_value;
415 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE24') THEN
416 l_old_routing_rec.ATTRIBUTE24 := p_update_table(i).p_value;
417 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE25') THEN
418 l_old_routing_rec.ATTRIBUTE25 := p_update_table(i).p_value;
419 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE26') THEN
420 l_old_routing_rec.ATTRIBUTE26 := p_update_table(i).p_value;
421 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE27') THEN
422 l_old_routing_rec.ATTRIBUTE27 := p_update_table(i).p_value;
423 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE28') THEN
424 l_old_routing_rec.ATTRIBUTE28 := p_update_table(i).p_value;
425 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE29') THEN
426 l_old_routing_rec.ATTRIBUTE29 := p_update_table(i).p_value;
427 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE30') THEN
428 l_old_routing_rec.ATTRIBUTE30 := p_update_table(i).p_value;
429 ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE_CATEGORY') THEN
430 l_old_routing_rec.ATTRIBUTE_CATEGORY := p_update_table(i).p_value;
431 END IF;
432
433 /* Compare Dates - if the last update date passed in via the API is less than
434 the last update in the db - it indicates someelse has updated this row after this
435 row was selected */
436 OPEN Get_db_last_update_date(p_Routing_id);
437 FETCH Get_db_last_update_date INTO l_db_date;
438 IF Get_db_last_update_date%NOTFOUND THEN
439 CLOSE Get_db_last_update_date;
440 RAISE routing_update_failure;
441 END IF;
442 CLOSE Get_db_last_update_date;
443
444 IF l_old_routing_rec.LAST_UPDATE_DATE < l_db_date THEN
445 RAISE last_update_date_failure;
446 END IF;
447
448 -- BUG 5197863 Moved the existing code to new function validate_dates
449 l_ret := Validate_dates(p_routing_id,l_old_routing_rec.effective_start_date,l_old_routing_rec.effective_end_date);
450 IF l_ret < 0 THEN
451 RAISE routing_update_failure;
452 END IF;
453 -- Comaring Routing and VAlidity Rules Dates
454 OPEN Get_vr_start_end_dates(p_routing_id);
455 FETCH Get_vr_start_end_dates INTO l_vr_start_date, l_vr_end_date;
456 IF l_vr_start_date IS NOT NULL THEN
457 IF (l_debug = 'Y') THEN
458 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
459 ||'Comparing VR and Routing Start Dates '||
460 'Routing Start Date = '||l_old_routing_rec.effective_start_date||
461 ' VR Start Date = '||l_vr_start_date);
462 END IF;
463
464 IF l_vr_start_date < l_old_routing_rec.effective_start_date THEN
465 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_DATE_IN_VR_DATE');
466 FND_MSG_PUB.ADD;
467 RAISE routing_update_failure;
468 END IF;
469
470 IF (l_vr_end_date = trunc(SYSDATE + 999999) ) THEN
471 l_vr_end_date := Null;
472 END IF;
473
474 IF (l_debug = 'Y') THEN
475 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
476 ||'Comparing VR and Routing End Dates '||
477 'Routing end Date = '||l_old_routing_rec.effective_end_date||
478 ' VR end Date = '||l_vr_end_date);
479 END IF;
480
481 IF (l_vr_end_date IS NULL) AND
482 (l_old_routing_rec.effective_end_date IS NOT NULL) THEN
483 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_DATE_IN_VR_DATE');
484 FND_MSG_PUB.ADD;
485 RAISE routing_update_failure;
486 END IF;
487
488 IF l_vr_end_date > l_old_routing_rec.effective_end_date THEN
489 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_DATE_IN_VR_DATE');
490 FND_MSG_PUB.ADD;
491 RAISE routing_update_failure;
492 END IF;
493 END IF;
494 CLOSE Get_vr_start_end_dates;
495
496 /* Number of times this routine is equal to number of rows in the p_update_table */
497 GMD_ROUTINGS_PKG.update_row(
498 x_routing_id => p_routing_id,
499 x_owner_organization_id => l_old_routing_rec.owner_organization_id,
500 x_routing_no => l_old_routing_rec.routing_no,
501 x_routing_vers => l_old_routing_rec.routing_vers,
502 x_routing_class => l_old_routing_rec.routing_class,
503 x_routing_qty => l_old_routing_rec.routing_qty,
504 x_routing_uom => l_old_routing_rec.routing_uom,
505 x_delete_mark => l_old_routing_rec.delete_mark,
506 x_text_code => l_old_routing_rec.text_code,
507 x_inactive_ind => l_old_routing_rec.inactive_ind,
508 x_enforce_step_dependency => l_old_routing_rec.enforce_step_dependency,
509 /* Bug 4603035 - Added the contiguous ind to be passed to the update */
510 x_contiguous_ind => l_old_routing_rec.contiguous_ind,
511 x_in_use => l_old_routing_rec.in_use,
512 x_attribute1 => l_old_routing_rec.attribute1,
513 x_attribute2 => l_old_routing_rec.attribute2,
514 x_attribute3 => l_old_routing_rec.attribute3,
515 x_attribute4 => l_old_routing_rec.attribute4,
516 x_attribute5 => l_old_routing_rec.attribute5,
517 x_attribute6 => l_old_routing_rec.attribute6,
518 x_attribute7 => l_old_routing_rec.attribute7,
519 x_attribute8 => l_old_routing_rec.attribute8,
520 x_attribute9 => l_old_routing_rec.attribute9,
521 x_attribute10 => l_old_routing_rec.attribute10,
522 x_attribute11 => l_old_routing_rec.attribute11,
523 x_attribute12 => l_old_routing_rec.attribute12,
524 x_attribute13 => l_old_routing_rec.attribute13,
525 x_attribute14 => l_old_routing_rec.attribute14,
526 x_attribute15 => l_old_routing_rec.attribute15,
527 x_attribute16 => l_old_routing_rec.attribute16,
528 x_attribute17 => l_old_routing_rec.attribute17,
529 x_attribute18 => l_old_routing_rec.attribute18,
530 x_attribute19 => l_old_routing_rec.attribute19,
531 x_attribute20 => l_old_routing_rec.attribute20,
532 x_attribute21 => l_old_routing_rec.attribute21,
533 x_attribute22 => l_old_routing_rec.attribute22,
534 x_attribute23 => l_old_routing_rec.attribute23,
535 x_attribute24 => l_old_routing_rec.attribute24,
536 x_attribute25 => l_old_routing_rec.attribute25,
537 x_attribute26 => l_old_routing_rec.attribute26,
538 x_attribute27 => l_old_routing_rec.attribute27,
539 x_attribute28 => l_old_routing_rec.attribute28,
540 x_attribute29 => l_old_routing_rec.attribute29,
541 x_attribute30 => l_old_routing_rec.attribute30,
542 x_attribute_category => l_old_routing_rec.attribute_category,
543 x_effective_start_date => l_old_routing_rec.effective_start_date,
544 x_effective_end_date => l_old_routing_rec.effective_end_date,
545 x_owner_id => l_old_routing_rec.owner_id,
546 x_project_id => l_old_routing_rec.project_id,
547 x_process_loss => l_old_routing_rec.process_loss,
548 x_routing_status => l_old_routing_rec.routing_status,
549 x_routing_desc => l_old_routing_rec.routing_desc,
550 x_last_update_date => NVL(l_old_routing_rec.last_update_date,SYSDATE),
551 x_last_updated_by => gmd_api_grp.user_id,
552 x_last_update_login => l_old_routing_rec.last_update_login,
553 x_fixed_process_loss => l_old_routing_rec.fixed_process_loss, --RLNAGARA B6997624
554 x_fixed_process_loss_uom => l_old_routing_rec.fixed_process_loss_uom --RLNAGARA B6997624
555 );
556 END LOOP;
557
558 /* Check if work was done */
559 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
560 RAISE routing_update_failure;
561 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
562
563 /* Get the messgae list and count generated by this API */
564 fnd_msg_pub.count_and_get (
565 p_count => x_message_count
566 ,p_encoded => FND_API.g_false
567 ,p_data => x_message_list);
568
569 IF (l_debug = 'Y') THEN
570 gmd_debug.put_line('Completed '||l_api_name ||' at '
571 ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
572 END IF;
573 EXCEPTION
574 WHEN routing_update_failure OR invalid_version THEN
575 IF (l_debug = 'Y') THEN
576 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
577 END IF;
578 x_return_status := FND_API.G_RET_STS_ERROR;
579 fnd_msg_pub.count_and_get (
580 p_count => x_message_count
581 ,p_encoded => FND_API.g_false
582 ,p_data => x_message_list);
583 WHEN last_update_date_failure THEN
584 x_return_status := FND_API.G_RET_STS_ERROR;
585 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
586 FND_MSG_PUB.ADD;
587 fnd_msg_pub.count_and_get (
588 p_count => x_message_count
589 ,p_encoded => FND_API.g_false
590 ,p_data => x_message_list);
591 WHEN setup_failure THEN
592 x_return_status := FND_API.G_RET_STS_ERROR;
593 fnd_msg_pub.count_and_get (
594 p_count => x_message_count
595 ,p_encoded => FND_API.g_false
596 ,p_data => x_message_list);
597 WHEN OTHERS THEN
598 IF (l_debug = 'Y') THEN
599 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
600 END IF;
601 fnd_msg_pub.add_exc_msg (gmd_routings_PUB.m_pkg_name, l_api_name);
602 x_return_status := FND_API.g_ret_sts_unexp_error;
603 fnd_msg_pub.count_and_get (
604 p_count => x_message_count
605 ,p_encoded => FND_API.g_false
606 ,p_data => x_message_list);
607 END update_routing;
608 /* =============================================================== */
609 /* Procedure: */
610 /* Validate_dates */
611 /* */
612 /* DESCRIPTION: */
613 /* */
614 /* Returns -1 if the validation fails */
615 /* */
616 /* */
617 /* History : */
618 /* Kalyani 06/06/2006 BUG 5197863 Added */
619 /* =============================================================== */
620 FUNCTION Validate_dates(
621 p_routing_id IN gmd_routings.routing_id%TYPE
622 ,p_effective_start_date IN DATE
623 ,p_effective_end_date IN DATE ) RETURN NUMBER is
624
625 l_api_name CONSTANT VARCHAR2(30) := 'Validate_dates';
626 l_oprn_start_date DATE;
627 l_oprn_end_date DATE;
628
629 CURSOR Get_oprn_start_end_dates(vRouting_id NUMBER) IS
630 SELECT max(effective_start_date) effective_start_date
631 , min(effective_end_date) effective_end_date
632 FROM gmd_operations_b o, fm_rout_dtl d
633 WHERE o.oprn_id = d.oprn_id
634 AND d.routing_id = vRouting_id
635 AND o.delete_mark = 0;
636 BEGIN
637 -- Validating Routing dates
638 /* Effective end date must be greater than start date, otherwise give error */
639 IF p_effective_start_date
640 > p_effective_end_date THEN
641 IF (l_debug = 'Y') THEN
642 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
643 ||'Effective start date ('||
644 p_effective_start_date||' ) '||
645 'must be less then end date ( '||
646 p_effective_end_date||' ) ');
647 END IF;
648 FND_MESSAGE.SET_NAME('GMD', 'QC_MIN_MAX_DATE');
649 FND_MSG_PUB.ADD;
650 RETURN -1;
651 END IF;
652
653 -- Comparing Routing and Operation Dates
654 OPEN Get_oprn_start_end_dates(p_routing_id);
655 FETCH Get_oprn_start_end_dates INTO l_oprn_start_date, l_oprn_end_date;
656 IF l_oprn_start_date IS NOT NULL THEN
657 IF (l_debug = 'Y') THEN
658 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
659 ||'Comparing Oprn and Routing Start Dates '||
660 'Routing Start Date = '||p_effective_start_date||
661 ' Oprn Start Date = '||l_oprn_start_date);
662 END IF;
663 IF l_oprn_start_date > p_effective_start_date THEN
664 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_DATE_IN_OPRN_DATE');
665 FND_MSG_PUB.ADD;
666 RETURN -1;
667 END IF;
668
669 IF (l_debug = 'Y') THEN
670 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
671 ||'Comparing Oprn and Routing End Dates '||
672 'Routing end Date = '||
673 p_effective_end_date||
674 ' Oprn end Date = '||l_oprn_end_date);
675 END IF;
676 IF (l_oprn_end_date IS NOT NULL) AND
677 (p_effective_end_date IS NULL) THEN
678 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_EFF_END_DATE');
679 FND_MSG_PUB.ADD;
680 RETURN -1;
681 END IF;
682
683 IF l_oprn_end_date < p_effective_end_date THEN
684 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_DATE_IN_OPRN_DATE');
685 FND_MSG_PUB.ADD;
686 RETURN -1;
687 END IF;
688 END IF;
689 CLOSE Get_oprn_start_end_dates;
690 RETURN 1;
691 END validate_dates;
692
693
694 END GMD_ROUTINGS_PVT;