1 PACKAGE BODY GMD_ROUTING_STEPS_PUB AS
2 /* $Header: GMDPRTSB.pls 120.3 2006/04/20 22:57:19 kmotupal noship $ */
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_steps */
23 /* */
24 /* DESCRIPTION: */
25 /* */
26 /* API returns (x_return_code) = 'S' if the insert into routing */
27 /* details (fm_rout_dtl) table is successfully. */
28 /* */
29 /* History : */
30 /* Shyam 07/29/2002 Initial implementation */
31 /* KMOTUPAL 21/4/2006 Bug# 3558478 Commented the code for */
32 /* validation of Operation */
33 /* =============================================================== */
34 PROCEDURE insert_routing_steps
35 (
36 p_api_version IN NUMBER := 1
37 , p_init_msg_list IN BOOLEAN := TRUE
38 , p_commit IN BOOLEAN := FALSE
39 , p_routing_id IN gmd_routings.routing_id%TYPE := NULL
40 , p_routing_no IN gmd_routings.routing_no%TYPE := NULL
41 , p_routing_vers IN gmd_routings.routing_vers%TYPE := NULL
42 , p_routing_step_rec IN fm_rout_dtl%ROWTYPE
43 , p_routings_step_dep_tbl IN GMD_ROUTINGS_PUB.gmd_routings_step_dep_tab
44 , x_message_count OUT NOCOPY NUMBER
45 , x_message_list OUT NOCOPY VARCHAR2
46 , x_return_status OUT NOCOPY VARCHAR2
47 ) IS
48
49 /* Local variable section */
50 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROUTING_STEPS';
51 l_row_id ROWID;
52 k NUMBER := 1;
53
54 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
55 l_return_from_routing_step_dep VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
56 l_routing_id gmd_routings.routing_id%TYPE;
57 l_routingStep_id fm_rout_dtl.routingStep_id%TYPE;
58 l_enforce_flag GMD_ROUTINGS.enforce_step_dependency%TYPE;
59 l_steprelease_type fm_rout_dtl.steprelease_type%TYPE;
60 l_oprn_no gmd_operations.oprn_no%TYPE;
61 l_oprn_vers gmd_operations.oprn_vers%TYPE;
62 l_rout_eff_start_date DATE;
63 l_rout_eff_end_date DATE;
64
65 /* define record type */
66 l_routing_step_rec fm_rout_dtl%ROWTYPE;
67
68 /* define table type */
69 l_step_dep_tab GMD_ROUTINGS_PUB.gmd_routings_step_dep_tab;
70
71 /* Cursor section */
72 CURSOR get_enforce_flag(vRouting_id NUMBER) IS
73 Select enforce_step_dependency
74 From gmd_routings_b
75 Where routing_id = vRouting_id;
76
77 /* gets the operation no and version associated to the routing detail/Step */
78 Cursor Get_oprn_details(vOprn_id fm_rout_dtl.oprn_id%TYPE) IS
79 Select oprn_no, oprn_vers
80 From gmd_operations_b
81 Where oprn_id = vOprn_id;
82
83 /* get routing start date */
84 Cursor get_rout_start_date(vRouting_id NUMBER) IS
85 Select effective_start_date, effective_end_date
86 From gmd_routings_b
87 Where routing_id = vRouting_id;
88
89 /* Exception section */
90 routing_creation_failure EXCEPTION;
91 routing_step_creation_failure EXCEPTION;
92 routing_step_dep_failure EXCEPTION;
93 invalid_version EXCEPTION;
94 setup_failure EXCEPTION;
95
96 BEGIN
97 SAVEPOINT create_routing_steps;
98
99 /* Set the return status to success initially */
100 x_return_status := FND_API.G_RET_STS_SUCCESS;
101
102 /* Initialize message list and count if needed */
103 IF p_init_msg_list THEN
104 fnd_msg_pub.initialize;
105 END IF;
106
107 /* Intialize the setup fields */
108 IF NOT gmd_api_grp.setup_done THEN
109 gmd_api_grp.setup_done := gmd_api_grp.setup;
110 END IF;
111 IF NOT gmd_api_grp.setup_done THEN
112 RAISE setup_failure;
113 END IF;
114
115 /* Make sure we are call compatible */
116 IF NOT FND_API.compatible_api_call ( gmd_routings_PUB.m_api_version
117 ,p_api_version
118 ,'INSERT_ROUTING_STEPS'
119 ,gmd_routing_steps_PUB.m_pkg_name) THEN
120 x_return_status := FND_API.G_RET_STS_ERROR;
121 RAISE invalid_version;
122 END IF;
123
124 /* Validations done prior to creation of routing steps */
125 /* Validation : Check if routing header exists in the database */
126 IF (l_debug = 'Y') THEN
127 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
128 ||'Begin of validations ');
129 END IF;
130
131 IF p_routing_id IS NOT NULL THEN
132 l_routing_id := p_routing_id;
133 GMDRTVAL_PUB.check_routing(pRouting_no => p_routing_no
134 ,pRouting_vers => p_routing_vers
135 ,xRouting_id => l_routing_id
136 ,xReturn_status => l_return_status);
137 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
138 /* it indicates that this routing does'ntexists */
139 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
140 FND_MSG_PUB.ADD;
141 RAISE routing_step_creation_failure;
142 END IF;
143 ELSE /* usually in this case user must have passed routing_no and version */
144 GMDRTVAL_PUB.check_routing(pRouting_no => p_routing_no
145 ,pRouting_vers => p_routing_vers
146 ,xRouting_id => l_routing_id
147 ,xReturn_status => l_return_status);
148
149 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
150 /* it indicates that this routing does'ntexists */
151 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
152 FND_MSG_PUB.ADD;
153 RAISE routing_step_creation_failure;
154 END IF;
155 END IF;
156
157 /* Check the routing id is not null */
158 IF l_routing_id IS NULL THEN
159 IF (l_debug = 'Y') THEN
160 gmd_debug.put_line('Routing id is required');
161 END IF;
162 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
163 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTING_ID');
164 FND_MSG_PUB.ADD;
165 RAISE routing_step_creation_failure;
166 END IF;
167
168 /* Routing Security Validation */
169 /* Validation: Check if for given user this routing can be modified */
170 IF (l_debug = 'Y') THEN
171 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
172 ||'Validation of user - owner orgn code = '||gmd_api_grp.user_id);
173 END IF;
174
175
176 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'ROUTING'
177 ,Entity_id => l_routing_id) THEN
178 RAISE routing_step_creation_failure;
179 END IF;
180
181 /* Check the routing step number is not null */
182 IF p_routing_step_rec.routingstep_no IS NULL THEN
183 IF (l_debug = 'Y') THEN
184 gmd_debug.put_line('Routing step number is required');
185 END IF;
186 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
187 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTINGSTEP_NO');
188 FND_MSG_PUB.ADD;
189 RAISE routing_step_creation_failure;
190 END IF;
191
192 /* Check the oprn id is not null */
193 IF p_routing_step_rec.oprn_id IS NULL THEN
194 IF (l_debug = 'Y') THEN
195 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||
196 'Operation id is required');
197 END IF;
198 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
199 FND_MESSAGE.SET_TOKEN ('MISSING', 'OPRN_ID');
200 FND_MSG_PUB.ADD;
201 RAISE routing_step_creation_failure;
202 END IF;
203
204 IF x_return_status <> FND_API.g_ret_sts_success THEN
205 RAISE routing_step_creation_failure;
206 END IF;
207
208 IF (l_debug = 'Y') THEN
209 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||
210 'Validation : Checking for routingStep existence ');
211 END IF;
212 /* Validation : check if this routing step exists in our system */
213 /* If this step exists indicate a duplication not allowed message */
214 IF p_routing_step_rec.routingstep_no IS NOT NULL THEN
215 IF GMDRTVAL_PUB.check_routingstep_no(proutingstep_no => p_routing_step_rec.routingstep_no
216 ,prouting_id => p_routing_id) <> 0 THEN
217 FND_MESSAGE.SET_NAME('GMD', 'FM_RTSTEPERR');
218 FND_MSG_PUB.ADD;
219 RAISE routing_step_creation_failure;
220 END IF;
221 END IF;
222
223 IF (l_debug = 'Y') THEN
224 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||
225 'Validation : Enforce flag check for routing id = '||l_routing_id);
226 END IF;
227
228 /* Validation : Enforcing step dependency
229 If this flag at Routing header level is set to Yes,
230 then fm_rout_dtl.steprelease type is set to manual = 1.*/
231 IF l_routing_id IS NOT NULL THEN
232 OPEN get_enforce_flag(l_routing_id);
233 FETCH get_enforce_flag INTO l_enforce_flag;
234 IF get_enforce_flag%NOTFOUND THEN
235 l_enforce_flag := 0;
236 END IF;
237 IF l_enforce_flag = 1 THEN
238 l_stepRelease_type := 1;
239 ELSE
240 l_stepRelease_type := p_routing_step_rec.steprelease_type;
241 END IF;
242 CLOSE get_enforce_flag;
243 END IF;
244
245 /* Validation : Routing status is not On Hold nor Obsolete/Archived
246 and Routing is not logically deleted */
247 IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'ROUTING',
248 Entity_id => l_routing_id ) THEN
249 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_NOT_VALID');
250 FND_MSG_PUB.ADD;
251 RAISE routing_step_creation_failure;
252 END IF;
253 -- Bug# 3558478 KMOTUPAL
254 -- Commented the code for validation of Operation
255 /* Validation : Operation status is not On Hold nor Obsolete/Archived
256 and Operation is not logically deleted */
257 /* IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'OPERATION',
258 Entity_id => p_routing_step_rec.oprn_id ) THEN
259 FND_MESSAGE.SET_NAME('GMD', 'GMD_OPRN_NOT_VALID');
260 FND_MSG_PUB.ADD;
261 RAISE routing_step_creation_failure;
262 END IF; */
263
264 /* Validation : Operation effective dates fall within the routing effective date range */
265 OPEN get_rout_start_date(l_Routing_id);
266 FETCH get_rout_start_date INTO l_rout_eff_start_date, l_rout_eff_end_date;
267 IF get_rout_start_date%NOTFOUND THEN
268 /* Routing has not been created correctly */
269 CLOSE get_rout_start_date;
270 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_DATES_INVALID');
271 FND_MSG_PUB.ADD;
272 RAISE routing_step_creation_failure;
273 END IF;
274 CLOSE get_rout_start_date;
275
276 OPEN Get_oprn_details(p_routing_step_rec.oprn_id);
277 FETCH Get_oprn_details INTO l_oprn_no, l_oprn_vers;
278 IF Get_oprn_details%NOTFOUND THEN
279 FND_MESSAGE.SET_NAME('GMD', 'GMD_MISSING_OPRN');
280 FND_MSG_PUB.ADD;
281 CLOSE Get_oprn_details;
282 RAISE routing_step_creation_failure;
283 END IF;
284 CLOSE Get_oprn_details;
285 IF GMDRTVAL_PUB.check_oprn(poprn_no =>l_oprn_no
286 ,poprn_vers => l_oprn_vers
287 ,prouting_start_date => l_rout_eff_start_date
288 ,prouting_end_date => l_rout_eff_end_date
289 ) <> 0 THEN
290 RAISE routing_step_creation_failure;
291 END IF;
292
293 /* Since values cannot be assigned to p_routing_step_rec
294 we create a another rec type to assign the derived values */
295 l_routing_step_rec := p_routing_step_rec;
296 l_routing_step_rec.stepRelease_type := NVL(l_stepRelease_type,1);
297 l_routing_step_rec.step_qty := NVL(p_routing_step_rec.step_qty,0);
298
299 /* Step : Create Routing steps */
300 IF (l_debug = 'Y') THEN
301 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||
302 'Insert the routing steps for routing with routing id = '||l_routing_id);
303 END IF;
304
305 GMD_ROUTING_STEPS_PVT.insert_routing_steps
306 (p_routing_id => l_routing_id
307 ,p_routing_step_rec => l_routing_step_rec
308 ,x_return_status => x_return_status
309 );
310
311 IF x_return_status <> FND_API.g_ret_sts_success THEN
312 RAISE routing_step_creation_failure;
313 END IF;
314
315 -- Check if routing detail was created
316 IF (l_debug = 'Y') THEN
317 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
318 ||'After calling the pvt insert step API the return status: '||x_return_status);
319 END IF;
320
321 /* After creating routing steps pass the routing id */
322 /* and the routing step no to the function that generates */
323 /* the step dependencies */
324
325 /* Create Routing Step dependencies */
326 IF p_routings_step_dep_tbl.count > 0 THEN
327 IF (l_debug = 'Y') THEN
328 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||
329 'Creating Routing Step dependencies ');
330 END IF;
331
332 -- Call the routing step dep function
333 -- For each routingStep_no, routing_id enter all the dependent
334 -- routing step nos
335 -- Construct a PL/SQL table that is specific only to this
336 -- routing step no and routing id.
337 FOR j IN 1 .. p_routings_step_dep_tbl.count LOOP
338 IF (p_routing_step_rec.ROUTINGSTEP_NO = p_routings_step_dep_tbl(j).ROUTINGSTEP_NO) AND
339 (P_ROUTING_ID = l_ROUTING_ID) THEN
340 l_step_dep_tab(k).routingstep_no := p_routings_step_dep_tbl(j).routingstep_no ;
341 l_step_dep_tab(k).dep_routingstep_no := p_routings_step_dep_tbl(j).dep_routingstep_no ;
342 l_step_dep_tab(k).routing_id := l_routing_id ;
343 l_step_dep_tab(k).dep_type := p_routings_step_dep_tbl(j).dep_type ;
344 l_step_dep_tab(k).rework_code := p_routings_step_dep_tbl(j).rework_code ;
345 l_step_dep_tab(k).standard_delay := p_routings_step_dep_tbl(j).standard_delay ;
346 l_step_dep_tab(k).minimum_delay := p_routings_step_dep_tbl(j).minimum_delay ;
347 l_step_dep_tab(k).max_delay := p_routings_step_dep_tbl(j).max_delay ;
348 l_step_dep_tab(k).transfer_qty := p_routings_step_dep_tbl(j).transfer_qty ;
349 l_step_dep_tab(k).routingstep_no_uom
350 := p_routings_step_dep_tbl(j).routingstep_no_uom ;
351 l_step_dep_tab(k).text_code := p_routings_step_dep_tbl(j).text_code ;
352 l_step_dep_tab(k).last_updated_by := p_routings_step_dep_tbl(j).last_updated_by ;
353 l_step_dep_tab(k).created_by := p_routings_step_dep_tbl(j).created_by ;
354 l_step_dep_tab(k).last_update_date := p_routings_step_dep_tbl(j).last_update_date ;
355 l_step_dep_tab(k).creation_date := p_routings_step_dep_tbl(j).creation_date ;
356 l_step_dep_tab(k).last_update_login := p_routings_step_dep_tbl(j).last_update_login ;
357 l_step_dep_tab(k).transfer_pct := p_routings_step_dep_tbl(j).transfer_pct ;
358
359 k := k + 1;
360 END IF;
361 END LOOP;
362
363 /* Since we call this procedure for each routingStep we dont have to reinitialize K
364 value after populating dependency PLSQL table. Call the step dependency function */
365 IF l_step_dep_tab.count > 0 THEN
366 GMD_ROUTING_STEPS_PUB.insert_step_dependencies
367 (
368 p_routing_id => l_routing_id
369 ,p_routingstep_no => p_routing_step_rec.routingstep_no
370 ,p_routings_step_dep_tbl => l_step_dep_tab
371 ,p_commit => FALSE
372 ,x_message_count => x_message_count
373 ,x_message_list => x_message_list
374 ,x_return_status => l_return_from_routing_step_dep
375 );
376
377 /* Check if insert of step dependency was done */
378 IF l_return_from_routing_step_dep <> FND_API.G_RET_STS_SUCCESS THEN
379 RAISE routing_step_dep_failure;
380 END IF; /* IF l_return_from_routing_step_dep <> FND_API.G_RET_STS_SUCCESS */
381 END IF; /* when l_step_dep_tab.count > 0 */
382 END IF; /* if p_routings_step_dep_tbl.count > 0 */
383
384 /* Check if work was done */
385 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
386 RAISE routing_step_creation_failure;
387 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
388
389 fnd_msg_pub.count_and_get (
390 p_count => x_message_count
391 ,p_encoded => FND_API.g_false
392 ,p_data => x_message_list);
393
394 IF x_message_count = 0 THEN
395 IF (l_debug = 'Y') THEN
396 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||
397 'Routing details/steps was created successfullly');
398 END IF;
399 END IF;
400
401 IF (P_commit) THEN
402 COMMIT;
403 END IF;
404
405 IF (l_debug = 'Y') THEN
406 gmd_debug.put_line('Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
407 END IF;
408
409 EXCEPTION
410 WHEN routing_step_creation_failure OR invalid_version THEN
411 ROLLBACK TO SAVEPOINT create_routing_steps;
412 IF (l_debug = 'Y') THEN
413 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete '||SQLERRM);
414 END IF;
415 fnd_msg_pub.count_and_get (
416 p_count => x_message_count
417 ,p_encoded => FND_API.g_false
418 ,p_data => x_message_list);
419 x_return_status := FND_API.G_RET_STS_ERROR;
420 WHEN setup_failure THEN
421 ROLLBACK TO SAVEPOINT create_routing_steps;
422 x_return_status := FND_API.G_RET_STS_ERROR;
423 fnd_msg_pub.count_and_get (
424 p_count => x_message_count
425 ,p_encoded => FND_API.g_false
426 ,p_data => x_message_list);
427 WHEN routing_step_dep_failure THEN
428 ROLLBACK TO SAVEPOINT create_routing_steps;
429 IF (l_debug = 'Y') THEN
430 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'failure due to insert step dep'||SQLERRM);
431 END IF;
432 fnd_msg_pub.count_and_get (
433 p_count => x_message_count
434 ,p_encoded => FND_API.g_false
435 ,p_data => x_message_list);
436 x_return_status := FND_API.G_RET_STS_ERROR;
437 WHEN OTHERS THEN
438 ROLLBACK TO SAVEPOINT create_routing_steps;
439 fnd_msg_pub.add_exc_msg (gmd_routing_steps_PUB.m_pkg_name, l_api_name);
440 IF (l_debug = 'Y') THEN
441 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
442 END IF;
443 fnd_msg_pub.count_and_get (
444 p_count => x_message_count
445 ,p_encoded => FND_API.g_false
446 ,p_data => x_message_list);
447 x_return_status := FND_API.g_ret_sts_unexp_error;
448 END insert_routing_steps;
449
450 /* =============================================================== */
451 /* Procedure: */
452 /* insert_step_dependencies */
453 /* */
454 /* DESCRIPTION: */
455 /* */
456 /* API returns (x_return_code) = 'S' if the insert into step */
457 /* dependency table is successfully. */
458 /* */
459 /* History : */
460 /* Shyam 07/29/2002 Initial implemenation */
461 /* =============================================================== */
462 PROCEDURE insert_step_dependencies
463 (
464 p_api_version IN NUMBER := 1
465 , p_init_msg_list IN BOOLEAN := TRUE
466 , p_commit IN BOOLEAN := FALSE
467 , p_routing_id IN gmd_routings.routing_id%TYPE := NULL
468 , p_routing_no IN gmd_routings.routing_no%TYPE := NULL
469 , p_routing_vers IN gmd_routings.routing_vers%TYPE := NULL
470 , p_routingstep_id IN fm_rout_dtl.routingstep_id%TYPE := NULL
471 , p_routingstep_no IN fm_rout_dtl.routingstep_no%TYPE := NULL
472 , p_routings_step_dep_tbl IN GMD_ROUTINGS_PUB.gmd_routings_step_dep_tab
473 , x_message_count OUT NOCOPY NUMBER
474 , x_message_list OUT NOCOPY VARCHAR2
475 , x_return_status OUT NOCOPY VARCHAR2
476 ) IS
477
478 /* Local variable section */
479 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_STEP_DEPENDENCIES';
480 l_routing_id gmd_routings.routing_id%TYPE;
481 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
482
483 /* Exception section */
484 routing_step_dep_failure EXCEPTION;
485 invalid_version EXCEPTION;
486 setup_failure EXCEPTION;
487
488 BEGIN
489 SAVEPOINT create_step_dependencies;
490
491 /* Set the return status to success initially */
492 x_return_status := FND_API.G_RET_STS_SUCCESS;
493
494 /* Initialize message list and count if needed */
495 IF p_init_msg_list THEN
496 fnd_msg_pub.initialize;
497 END IF;
498
499 /* Intialize the setup fields */
500 IF NOT gmd_api_grp.setup_done THEN
501 gmd_api_grp.setup_done := gmd_api_grp.setup;
502 END IF;
503 IF NOT gmd_api_grp.setup_done THEN
504 RAISE setup_failure;
505 END IF;
506
507 /* Make sure we are call compatible */
508 IF NOT FND_API.compatible_api_call ( gmd_routings_PUB.m_api_version
509 ,p_api_version
510 ,l_api_name
511 ,gmd_routing_steps_PUB.m_pkg_name) THEN
512 x_return_status := FND_API.G_RET_STS_ERROR;
513 RAISE invalid_version;
514 END IF;
515
516 /* Routingstp number must be passed, otherwise give error */
517 IF p_routingstep_no IS NULL THEN
518 IF (l_debug = 'Y') THEN
519 gmd_debug.put_line('routing step number required');
520 END IF;
521 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
522 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTINGSTEP_NO');
523 FND_MSG_PUB.ADD;
524 RAISE routing_step_dep_failure;
525 END IF;
526
527 /* Routingstp number must be passed, otherwise give error */
528 IF p_routings_step_dep_tbl(1).dep_routingstep_no IS NULL THEN
529 IF (l_debug = 'Y') THEN
530 gmd_debug.put_line('dep routing step number required');
531 END IF;
532 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
533 FND_MESSAGE.SET_TOKEN ('MISSING', 'DEP_ROUTINGSTEP_NO');
534 FND_MSG_PUB.ADD;
535 RAISE routing_step_dep_failure;
536 END IF;
537
538 /* routingstep_no_uom must be passed, otherwise give error */
539 IF p_routings_step_dep_tbl(1).routingstep_no_uom IS NULL THEN
540 IF (l_debug = 'Y') THEN
541 gmd_debug.put_line('Item uom required');
542 END IF;
543 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
544 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTINGSTEP_NO_UOM');
545 FND_MSG_PUB.ADD;
546 RAISE routing_step_dep_failure;
547 /* call common function to check if um passed is valid */
548 ELSIF (NOT(gmd_api_grp.validate_um(p_routings_step_dep_tbl(1).routingstep_no_uom))) THEN
549 IF (l_debug = 'Y') THEN
550 gmd_debug.put_line('Item uom invalid');
551 END IF;
552 FND_MESSAGE.SET_NAME('GMA', 'SY_INVALID_UM_CODE');
553 FND_MSG_PUB.ADD;
554 RAISE routing_step_dep_failure;
555 END IF;
556
557 /* transfer pct value should be in between 0 and 100 */
558 IF p_routings_step_dep_tbl(1).transfer_pct < 0 OR p_routings_step_dep_tbl(1).transfer_pct > 100 THEN
559 IF (l_debug = 'Y') THEN
560 gmd_debug.put_line('Transfer percent should be positive value');
561 END IF;
562 FND_MESSAGE.SET_NAME ('GMD', 'FM_INVALID');
563 FND_MSG_PUB.ADD;
564 RAISE routing_step_dep_failure;
565 END IF;
566
567 /* dep_type value should be either 0 or 1 */
568 IF p_routings_step_dep_tbl(1).dep_type NOT IN (0,1) THEN
569 IF (l_debug = 'Y') THEN
570 gmd_debug.put_line('Invalid value for dep_type field');
571 END IF;
572 FND_MESSAGE.SET_NAME ('GMD', 'GMD_DEP_TYPE_INVALID');
573 FND_MSG_PUB.ADD;
574 RAISE routing_step_dep_failure;
575 END IF;
576
577 IF (l_debug = 'Y') THEN
578 gmd_debug.put_line(' Validation : Check if the routing id exists in the db ');
579 END IF;
580 /* Validation : Check if routing header exists in the database */
581 IF p_routing_id IS NOT NULL THEN
582 l_routing_id := p_routing_id;
583 GMDRTVAL_PUB.check_routing(pRouting_no => p_routing_no
584 ,pRouting_vers => p_routing_vers
585 ,xRouting_id => l_routing_id
586 ,xReturn_status => l_return_status);
587
588 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
589 /* it indicates that this routing does'ntexists */
590 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
591 FND_MSG_PUB.ADD;
592 RAISE routing_step_dep_failure;
593 END IF;
594 ELSE /* usually in this case use must have passed routing_no and version */
595 GMDRTVAL_PUB.check_routing(pRouting_no => p_routing_no
596 ,pRouting_vers => p_routing_vers
597 ,xRouting_id => l_routing_id
598 ,xReturn_status => l_return_status);
599
600 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
601 /* it indicates that this routing does'ntexists */
602 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
603 FND_MSG_PUB.ADD;
604 RAISE routing_step_dep_failure;
605 END IF;
606 END IF;
607
608 /* Check the routing id is not null */
609 IF l_routing_id IS NULL THEN
610 IF (l_debug = 'Y') THEN
611 gmd_debug.put_line('Routing id is required');
612 END IF;
613 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
614 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTING_ID');
615 FND_MSG_PUB.ADD;
616 x_return_status := FND_API.g_ret_sts_error;
617 END IF;
618
619 /* Routing Security Validation */
620 /* Validation: Check if for given user this routing can be modified */
621 IF (l_debug = 'Y') THEN
622 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
623 ||'Validation of user - owner orgn code = '||gmd_api_grp.user_id);
624 END IF;
625 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'ROUTING'
626 ,Entity_id => l_routing_id) THEN
627 RAISE routing_step_dep_failure;
628 END IF;
629
630 /* Validation : Routing status is not On Hold nor Obsolete/Archived
631 and Routing is not logically deleted */
632 IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'ROUTING',
633 Entity_id => l_routing_id ) THEN
634 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_NOT_VALID');
635 FND_MSG_PUB.ADD;
636 RAISE routing_step_dep_failure;
637 END IF;
638
639 /* Validation : Check if this step dep exist */
640 /* The primary key is combination of RoutingStep_no, dep_RoutingStep_no
641 and Routing_id */
642 /* Validation : Check if routing header exists in the database */
643
644 FOR i IN 1 .. p_routings_step_dep_tbl.count LOOP
645 GMDRTVAL_PUB.check_deprouting(pRouting_id => l_routing_id
646 ,pRoutingstep_no => p_routingstep_no
647 ,pdeproutingstep_no => p_routings_step_dep_tbl(i).dep_routingstep_no
648 ,x_Return_status => l_return_status);
649
650 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
651 /* it indicates that this routing does'ntexists */
652 FND_MESSAGE.SET_NAME('GME', 'PC_RECORD_EXISTS');
653 FND_MSG_PUB.ADD;
654 RAISE routing_step_dep_failure;
655 END IF;
656 END LOOP; /* End loop for p_routings_step_dep_tbl.count */
657
658 /* Insert made into the step dependency table */
659 GMD_ROUTING_STEPS_PVT.insert_step_dependencies
660 ( p_routing_id => l_routing_id
661 , p_routingstep_no => p_routingstep_no
662 , p_routings_step_dep_tbl => p_routings_step_dep_tbl
663 , x_return_status => x_return_status
664 );
665
666 -- Check if routing step dependencies were created
667 IF (l_debug = 'Y') THEN
668 gmd_debug.put_line('After inserting routing step dependencies');
669 END IF;
670
671 /* Check if work was done */
672 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
673 RAISE routing_step_dep_failure;
674 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
675
676 fnd_msg_pub.count_and_get (
677 p_count => x_message_count
678 ,p_encoded => FND_API.g_false
679 ,p_data => x_message_list);
680
681 IF x_message_count = 0 THEN
682 IF (l_debug = 'Y') THEN
683 gmd_debug.put_line('Routing step dependencies were created successfullly');
684 END IF;
685 END IF;
686
687 IF (P_commit) THEN
688 COMMIT;
689 END IF;
690
691 IF (l_debug = 'Y') THEN
692 gmd_debug.put_line('Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
693 END IF;
694
695 EXCEPTION
696 WHEN routing_step_dep_failure OR invalid_version THEN
697 ROLLBACK TO SAVEPOINT create_step_dependencies;
698 IF (l_debug = 'Y') THEN
699 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
700 END IF;
701 fnd_msg_pub.count_and_get (
702 p_count => x_message_count
703 ,p_encoded => FND_API.g_false
704 ,p_data => x_message_list);
705 x_return_status := FND_API.G_RET_STS_ERROR;
706 WHEN setup_failure THEN
707 ROLLBACK TO SAVEPOINT create_step_dependencies;
708 x_return_status := FND_API.G_RET_STS_ERROR;
709 fnd_msg_pub.count_and_get (
710 p_count => x_message_count
711 ,p_encoded => FND_API.g_false
712 ,p_data => x_message_list);
713 WHEN OTHERS THEN
714 ROLLBACK TO SAVEPOINT create_step_dependencies;
715 fnd_msg_pub.add_exc_msg (gmd_routing_steps_PUB.m_pkg_name, l_api_name);
716 IF (l_debug = 'Y') THEN
717 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
718 END IF;
719 fnd_msg_pub.count_and_get (
720 p_count => x_message_count
721 ,p_encoded => FND_API.g_false
722 ,p_data => x_message_list);
723 x_return_status := FND_API.g_ret_sts_unexp_error;
724 END insert_step_dependencies;
725
726 /* =============================================================== */
727 /* Procedure: */
728 /* update_routing_steps */
729 /* */
730 /* DESCRIPTION: */
731 /* */
732 /* API returns (x_return_code) = 'S' if the update into routing */
733 /* details (fm_rout_dtl table) is success. */
734 /* */
735 /* History : */
736 /* Shyam 07/29/2002 Initial implementation */
737 /* =============================================================== */
738 PROCEDURE update_routing_steps
739 ( p_api_version IN NUMBER := 1
740 , p_init_msg_list IN BOOLEAN := TRUE
741 , p_commit IN BOOLEAN := FALSE
742 , p_routingstep_id IN fm_rout_dtl.routingstep_id%TYPE := NULL
743 , p_routingstep_no IN fm_rout_dtl.routingstep_no%TYPE := NULL
744 , p_routing_id IN gmd_routings.routing_id%TYPE := NULL
745 , p_routing_no IN gmd_routings.routing_no%TYPE := NULL
746 , p_routing_vers IN gmd_routings.routing_vers%TYPE := NULL
747 , p_update_table IN GMD_ROUTINGS_PUB.update_tbl_type
748 , x_message_count OUT NOCOPY NUMBER
749 , x_message_list OUT NOCOPY VARCHAR2
750 , x_return_status OUT NOCOPY VARCHAR2
751 ) IS
752
753 /* Local variable section */
754 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ROUTING_STEPS';
755 l_routing_id gmd_routings.routing_id%TYPE;
756 l_routingstep_id fm_rout_dtl.routingStep_id%TYPE;
757 l_routingstep_no fm_rout_dtl.routingStep_no%TYPE;
758 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
759 l_oprn_id gmd_operations.oprn_id%TYPE;
760
761 /* Define record type that hold the routing data */
762 l_old_routingStep_rec fm_rout_dtl%ROWTYPE;
763
764 /* Cursor defn section */
765 CURSOR get_oprn_id(vRoutingStep_id fm_rout_dtl.routingstep_id%TYPE) IS
766 Select oprn_id
767 From fm_rout_dtl
768 Where routingStep_id = vRoutingStep_id;
769
770 Cursor get_routing_owner_orgn_code(vRouting_id Number) IS
771 Select owner_orgn_code
772 From gmd_routings_b
773 Where routing_id = vRouting_id;
774
775 /* Define Exceptions */
776 routing_update_step_failure EXCEPTION;
777 invalid_version EXCEPTION;
778 setup_failure EXCEPTION;
779 -- KSHUKLA updated the api as per as 4376301
780 -- Declaration of the variables to be used.
781 l_opr_start_date DATE ;
782 l_opr_end_date DATE;
783 l_rout_start_date DATE;
784 l_rout_end_date DATE;
785 VALID_DATE_EXCEPTION EXCEPTION;
786
787 BEGIN
788 SAVEPOINT update_routing_details;
789
790 /* Set the return status to success initially */
791 x_return_status := FND_API.G_RET_STS_SUCCESS;
792
793 /* Initialize message list and count if needed */
794 IF p_init_msg_list THEN
795 fnd_msg_pub.initialize;
796 END IF;
797
798 /* Intialize the setup fields */
799 IF NOT gmd_api_grp.setup_done THEN
800 gmd_api_grp.setup_done := gmd_api_grp.setup;
801 END IF;
802 IF NOT gmd_api_grp.setup_done THEN
803 RAISE setup_failure;
804 END IF;
805
806 /* Make sure we are call compatible */
807 IF NOT FND_API.compatible_api_call ( gmd_routings_PUB.m_api_version
808 ,p_api_version
809 ,l_api_name
810 ,gmd_routing_steps_PUB.m_pkg_name) THEN
811 x_return_status := FND_API.G_RET_STS_ERROR;
812 RAISE invalid_version;
813 END IF;
814
815 /* Validation prior to Routings Steps update */
816
817 /* Validation : Check if the routing id exists in the db */
818 /* Validation : Check if routing header exists in the database */
819 IF (l_debug = 'Y') THEN
820 gmd_debug.put_line('Validation : check if the routing id is valid ');
821 END IF;
822 IF p_routing_id IS NOT NULL THEN
823 l_routing_id := p_routing_id;
824 GMDRTVAL_PUB.check_routing(pRouting_no => p_routing_no
825 ,pRouting_vers => p_routing_vers
826 ,xRouting_id => l_routing_id
827 ,xReturn_status => l_return_status);
828
829 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
830 /* it indicates that this routing does'ntexists */
831 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
832 FND_MSG_PUB.ADD;
833 RAISE routing_update_step_failure;
834 END IF;
835 ELSE /* usually in this case user must have passed routing_no and version */
836 GMDRTVAL_PUB.check_routing(pRouting_no => p_routing_no
837 ,pRouting_vers => p_routing_vers
838 ,xRouting_id => l_routing_id
839 ,xReturn_status => l_return_status);
840
841 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
842 /* it indicates that this routing does'ntexists */
843 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
844 FND_MSG_PUB.ADD;
845 RAISE routing_update_step_failure;
846 END IF;
847 END IF;
848
849 /* Check the routing id is not null */
850 IF l_routing_id IS NULL THEN
851 IF (l_debug = 'Y') THEN
852 gmd_debug.put_line('Routing id is required');
853 END IF;
854 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
855 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTING_ID');
856 FND_MSG_PUB.ADD;
857 RAISE routing_update_step_failure;
858 END IF;
859
860 /* Routing Security fix */
861 /* Validation: Check if for given user this routing can be modified */
862 IF (l_debug = 'Y') THEN
863 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
864 ||'Validation of user - owner orgn code = '||gmd_api_grp.user_id);
865 END IF;
866 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'ROUTING'
867 ,Entity_id => l_routing_id) THEN
868 RAISE routing_update_step_failure;
869 END IF;
870
871 /* get the RoutingStep_id - if it is not passed as a parameter */
872 IF (l_debug = 'Y') THEN
873 gmd_debug.put_line('Validation : get the routingstep_id with routing id = '
874 ||l_routing_id ||'RoutingStepNo = '||p_routingStep_no);
875 END IF;
876
877 IF p_routingStep_id IS NOT NULL THEN
878 l_routingstep_id := p_routingstep_id;
879 IF (l_debug = 'Y') THEN
880 gmd_debug.put_line(' get the RoutingStep_no- for rtstepid = '||l_routingStep_id);
881 END IF;
882 GMDRTVAL_PUB.get_routingstep_info(pxRoutingStep_no => l_routingstep_no
883 ,pxRoutingStep_id => l_routingstep_id
884 ,x_return_status => l_return_status );
885 IF (l_debug = 'Y') THEN
886 gmd_debug.put_line(' After get_rouingstep_info is called ret status = '||l_return_status);
887 END IF;
888 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
889 /* it indicates that this routing does'ntexists */
890 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTINGSTEP_INVALID');
891 FND_MSG_PUB.ADD;
892 RAISE routing_update_step_failure;
893 END IF;
894 ELSE
895 /* hopefully the Routing step no was passed in .. */
896 l_routingstep_no := p_routingstep_no;
897 GMDRTVAL_PUB.get_routingstep_info(pRouting_id => l_routing_id
898 ,pxRoutingStep_no => l_routingstep_no
899 ,pxRoutingStep_id => l_routingstep_id
900 ,x_return_status => l_return_status );
901 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
902 -- it indicates that this routing does not exists
903 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTINGSTEP_INVALID');
904 FND_MSG_PUB.ADD;
905 RAISE routing_update_step_failure;
906 END IF;
907 END IF;
908
909 /* Check the routing id is not null */
910 IF l_routingstep_id IS NULL THEN
911 IF (l_debug = 'Y') THEN
912 gmd_debug.put_line('Routing step id is required');
913 END IF;
914 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
915 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTINGSTEP_ID');
916 FND_MSG_PUB.ADD;
917 RAISE routing_update_step_failure;
918 END IF;
919
920 /* Validation : Routing status is not On Hold nor Obsolete/Archived
921 and Routing is not logically deleted */
922 IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'ROUTING',
923 Entity_id => l_routing_id ) THEN
924 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_NOT_VALID');
925 FND_MSG_PUB.ADD;
926 RAISE routing_update_step_failure;
927 END IF;
928
929 /* Validation : Operation status is not On Hold nor Obsolete/Archived
930 and Operation is not logically deleted */
931 OPEN get_oprn_id(l_routingStep_id);
932 FETCH get_oprn_id INTO l_oprn_id;
933 IF get_oprn_id%NOTFOUND THEN
934 RAISE routing_update_step_failure;
935 CLOSE get_oprn_id;
936 END IF;
937 CLOSE get_oprn_id;
938 IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'OPERATION',
939 Entity_id => l_oprn_id ) THEN
940 FND_MESSAGE.SET_NAME('GMD', 'GMD_OPRN_NOT_VALID');
941 FND_MSG_PUB.ADD;
942 RAISE routing_update_step_failure;
943 END IF;
944
945 /* KSHUKLA added the following validation as per as 4376301
946 This validation takes care of in case of an upgrade of an operation
947 for a recipe the operation whouls be valid through out the course of
948 Recipe. */
949
950 /* Check for the routing date and operation date validity
951 */
952 FOR a in 1..p_update_table.COUNT LOOP
953 if UPPER(p_update_table(a).p_col_to_update) = 'OPRN_ID' THEN
954
955 select effective_start_date,effective_end_date
956 into l_rout_start_date,l_rout_end_date
957 from fm_rout_hdr
958 where routing_id =l_routing_id;
959
960 IF GMDRTVAL_PUB.check_oprn(poprn_id =>p_update_table(a).p_value
961 ,prouting_start_date => l_rout_start_date
962 ,prouting_end_date => l_rout_end_date
963 ) <> 0 THEN
964 RAISE VALID_DATE_EXCEPTION;
965 END IF;
966 END IF;
967 END LOOP;
968
969 -- End of KSHUKLA VAlidation for 4376301
970 /* Validation: Operation status level should be higher or equal
971 the routing level status. For instance, if the routing status
972 is "Approved for Laboratory Use", operations with a status cannot be "New"
973 are not allowed. Therefore when the routing status is updated check
974 all the associated operation status */
975
976 /* Call the private API that does the actual update */
977 GMD_ROUTING_STEPS_PVT.update_routing_steps
978 ( p_routingstep_id => l_routingstep_id
979 , p_update_table => p_update_table
980 , x_return_status => x_return_status
981 );
982
983 /* Check if work was done */
984 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
985 RAISE routing_update_step_failure;
986 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
987
988 fnd_msg_pub.count_and_get (
989 p_count => x_message_count
990 ,p_encoded => FND_API.g_false
991 ,p_data => x_message_list);
992
993 IF x_message_count = 0 THEN
994 IF (l_debug = 'Y') THEN
995 gmd_debug.put_line('Routing step was updated successfullly');
996 END IF;
997 END IF;
998
999 IF (P_commit) THEN
1000 COMMIT;
1001 END IF;
1002
1003 IF (l_debug = 'Y') THEN
1004 gmd_debug.put_line('Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1005 END IF;
1006 EXCEPTION
1007 WHEN routing_update_step_failure OR invalid_version THEN
1008 ROLLBACK TO SAVEPOINT update_routing_details;
1009 IF (l_debug = 'Y') THEN
1010 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
1011 END IF;
1012 fnd_msg_pub.count_and_get (
1013 p_count => x_message_count
1014 ,p_encoded => FND_API.g_false
1015 ,p_data => x_message_list);
1016 x_return_status := FND_API.G_RET_STS_ERROR;
1017 WHEN setup_failure THEN
1018 ROLLBACK TO SAVEPOINT update_routing_details;
1019 x_return_status := FND_API.G_RET_STS_ERROR;
1020 fnd_msg_pub.count_and_get (
1021 p_count => x_message_count
1022 ,p_encoded => FND_API.g_false
1023 ,p_data => x_message_list);
1024 WHEN VALID_DATE_EXCEPTION THEN
1025 ROLLBACK TO SAVEPOINT update_routing_details;
1026 x_return_status := FND_API.G_RET_STS_ERROR;
1027 fnd_msg_pub.count_and_get (
1028 p_count => x_message_count
1029 ,p_encoded => FND_API.g_false
1030 ,p_data => x_message_list);
1031 WHEN OTHERS THEN
1032 ROLLBACK TO SAVEPOINT update_routing_details;
1033 fnd_msg_pub.add_exc_msg (gmd_routing_steps_PUB.m_pkg_name, l_api_name);
1034 IF (l_debug = 'Y') THEN
1035 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
1036 END IF;
1037 fnd_msg_pub.count_and_get (
1038 p_count => x_message_count
1039 ,p_encoded => FND_API.g_false
1040 ,p_data => x_message_list);
1041 x_return_status := FND_API.g_ret_sts_unexp_error;
1042 END update_routing_steps;
1043
1044 /* =============================================================== */
1045 /* Procedure: */
1046 /* update_step_dependencies */
1047 /* */
1048 /* DESCRIPTION: */
1049 /* */
1050 /* API returns (x_return_code) = 'S' if the update into routing */
1051 /* step dependency (fm_rout_dep table) is success. */
1052 /* */
1053 /* History : */
1054 /* Shyam 07/29/2002 Initial implementation */
1055 /* =============================================================== */
1056 PROCEDURE update_step_dependencies
1057 ( p_api_version IN NUMBER := 1
1058 , p_init_msg_list IN BOOLEAN := TRUE
1059 , p_commit IN BOOLEAN := FALSE
1060 , p_routingstep_no IN fm_rout_dep.routingstep_no%TYPE := NULL
1061 , p_routingstep_id IN fm_rout_dtl.routingstep_id%TYPE := NULL
1062 , p_dep_routingstep_no IN fm_rout_dep.routingstep_no%TYPE
1063 , p_routing_id IN fm_rout_dep.routing_id%TYPE := NULL
1064 , p_routing_no IN gmd_routings.routing_no%TYPE := NULL
1065 , p_routing_vers IN gmd_routings.routing_vers%TYPE := NULL
1066 , p_update_table IN GMD_ROUTINGS_PUB.update_tbl_type
1067 , x_message_count OUT NOCOPY NUMBER
1068 , x_message_list OUT NOCOPY VARCHAR2
1069 , x_return_status OUT NOCOPY VARCHAR2
1070 ) IS
1071
1072 /* Local variable section */
1073 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_STEP_DEPENDENCIES';
1074 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1075
1076 l_routingstep_no fm_rout_dtl.routingStep_no%TYPE;
1077 l_routingstep_id fm_rout_dtl.routingStep_id%TYPE;
1078 l_routing_id fm_rout_dep.routing_id%TYPE;
1079 l_transfer_pct NUMBER;
1080 l_dep_type NUMBER;
1081 l_std_delay NUMBER;
1082
1083 /* Define record type that hold the routing data */
1084 l_old_stepDep_rec fm_rout_dep%ROWTYPE;
1085
1086 /* Define Exceptions */
1087 routing_update_dep_failure EXCEPTION;
1088 invalid_version EXCEPTION;
1089 setup_failure EXCEPTION;
1090
1091 BEGIN
1092 SAVEPOINT update_step_dependency;
1093
1094 /* Set the return status to success initially */
1095 x_return_status := FND_API.G_RET_STS_SUCCESS;
1096
1097 /* Initialize message list and count if needed */
1098 IF p_init_msg_list THEN
1099 fnd_msg_pub.initialize;
1100 END IF;
1101
1102 /* Intialize the setup fields */
1103 IF NOT gmd_api_grp.setup_done THEN
1104 gmd_api_grp.setup_done := gmd_api_grp.setup;
1105 END IF;
1106 IF NOT gmd_api_grp.setup_done THEN
1107 RAISE setup_failure;
1108 END IF;
1109
1110 /* Make sure we are call compatible */
1111 IF NOT FND_API.compatible_api_call ( gmd_routings_PUB.m_api_version
1112 ,p_api_version
1113 ,l_api_name
1114 ,gmd_routing_steps_PUB.m_pkg_name) THEN
1115 x_return_status := FND_API.G_RET_STS_ERROR;
1116 RAISE invalid_version;
1117 END IF;
1118
1119 /* Validation prior to Routings Step dependency update */
1120 /* Validation : Impact with ASQC ON and change to transfer % */
1121 /* To be determined */
1122
1123
1124 FOR a IN 1 .. p_update_table.count LOOP
1125 /* Validation : Check if transfer percent value is valid */
1126 IF UPPER(p_update_table(a).p_col_to_update) = 'TRANSFER_PCT' THEN
1127 l_transfer_pct := p_update_table(a).p_value;
1128 /* Validation : Check if dependency type value is valid */
1129 ELSIF UPPER(p_update_table(a).p_col_to_update) = 'DEP_TYPE' THEN
1130 l_dep_type := p_update_table(a).p_value;
1131 ELSIF UPPER(p_update_table(a).p_col_to_update) = 'STANDARD_DELAY' THEN
1132 l_std_delay := p_update_table(a).p_value;
1133 END IF; /* UPPER(p_update_table(i).p_col_to_update) = 'TRANSFER_PCT' */
1134 END LOOP;
1135
1136 /* transfer pct value should be in between 0 and 100 */
1137 IF l_transfer_pct < 0 OR l_transfer_pct > 100 THEN
1138 IF (l_debug = 'Y') THEN
1139 gmd_debug.put_line ('Transfer pct value should be positive');
1140 END IF;
1141 FND_MESSAGE.SET_NAME ('GMD', 'FM_INVALID');
1142 FND_MSG_PUB.ADD;
1143 RAISE routing_update_dep_failure;
1144 END IF;
1145
1146 /* standard delay value should be in = or > 0 */
1147 IF l_std_delay < 0 THEN
1148 IF (l_debug = 'Y') THEN
1149 gmd_debug.put_line ('Transfer pct value should be positive');
1150 END IF;
1151 FND_MESSAGE.SET_NAME ('GMD', 'FM_INVALID');
1152 FND_MSG_PUB.ADD;
1153 RAISE routing_update_dep_failure;
1154 END IF;
1155
1156 /* dep_type value should be either 0 or 1 */
1157 IF l_dep_type NOT IN (0,1) THEN
1158 IF (l_debug = 'Y') THEN
1159 gmd_debug.put_line('Invalid value for dep_type field');
1160 END IF;
1161 FND_MESSAGE.SET_NAME ('GMD', 'GMD_DEP_TYPE_INVALID');
1162 FND_MSG_PUB.ADD;
1163 RAISE routing_update_dep_failure;
1164 END IF;
1165
1166 /* Validation : Check if routing header exists in the database */
1167 IF p_routing_id IS NOT NULL THEN
1168 l_routing_id := p_routing_id;
1169 GMDRTVAL_PUB.check_routing(pRouting_no => p_routing_no
1170 ,pRouting_vers => p_routing_vers
1171 ,xRouting_id => l_routing_id
1172 ,xReturn_status => l_return_status);
1173
1174 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1175 /* it indicates that this routing does'ntexists */
1176 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
1177 FND_MSG_PUB.ADD;
1178 RAISE routing_update_dep_failure;
1179 END IF;
1180 ELSE /* usually in this case user must have passed routing_no and version */
1181 GMDRTVAL_PUB.check_routing(pRouting_no => p_routing_no
1182 ,pRouting_vers => p_routing_vers
1183 ,xRouting_id => l_routing_id
1184 ,xReturn_status => l_return_status);
1185
1186 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1187 /* it indicates that this routing does'ntexists */
1188 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
1189 FND_MSG_PUB.ADD;
1190 RAISE routing_update_dep_failure;
1191 END IF;
1192 END IF;
1193
1194 /* Check the routing id is not null */
1195 IF l_routing_id IS NULL THEN
1196 IF (l_debug = 'Y') THEN
1197 gmd_debug.put_line('Routing id is required');
1198 END IF;
1199 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1200 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTING_ID');
1201 FND_MSG_PUB.ADD;
1202 RAISE routing_update_dep_failure;
1203 END IF;
1204
1205 /* Routing Security fix */
1206 /* Validation: Check if for given user this routing can be modified */
1207 IF (l_debug = 'Y') THEN
1208 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
1209 ||'Validation of user - owner orgn code = '||gmd_api_grp.user_id);
1210 END IF;
1211 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'ROUTING'
1212 ,Entity_id => l_routing_id) THEN
1213 RAISE routing_update_dep_failure;
1214 END IF;
1215
1216 /* get the RoutingStep_no - if it is not passed as a parameter */
1217 IF (l_debug = 'Y') THEN
1218 gmd_debug.put_line('Validation : get the routingstep_no with routing step id = '
1219 ||p_routingstep_id);
1220 END IF;
1221 IF p_routingStep_no IS NOT NULL THEN
1222 l_routingstep_no := p_routingstep_no;
1223 GMDRTVAL_PUB.get_routingstep_info(pRouting_id => l_routing_id
1224 ,pxRoutingStep_no => l_routingstep_no
1225 ,pxRoutingStep_id => l_routingstep_id
1226 ,x_return_status => l_return_status );
1227 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1228 /* it indicates that this routing does'ntexists */
1229 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTINGSTEP_INVALID');
1230 FND_MSG_PUB.ADD;
1231 RAISE routing_update_dep_failure;
1232 END IF;
1233 ELSE
1234 /* hopefully the Routing step id was passed in .. */
1235 l_routingstep_id := p_routingstep_id;
1236 GMDRTVAL_PUB.get_routingstep_info(pxRoutingStep_no => l_routingstep_no
1237 ,pxRoutingStep_id => l_routingstep_id
1238 ,x_return_status => l_return_status );
1239 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1240 /* it indicates that this routing does'ntexists */
1241 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTINGSTEP_INVALID');
1242 FND_MSG_PUB.ADD;
1243 RAISE routing_update_dep_failure;
1244 END IF;
1245 END IF;
1246
1247 /* Check the routing step no is not null */
1248 IF l_routingstep_no IS NULL THEN
1249 IF (l_debug = 'Y') THEN
1250 gmd_debug.put_line('Routing id is required');
1251 END IF;
1252 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1253 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTINGSTEP_NO');
1254 FND_MSG_PUB.ADD;
1255 RAISE routing_update_dep_failure;
1256 END IF;
1257
1258 IF x_return_status <> FND_API.g_ret_sts_success THEN
1259 RAISE routing_update_dep_failure;
1260 END IF;
1261
1262 /* Validation : Routing status is not On Hold nor Obsolete/Archived
1263 and Routing is not logically deleted */
1264 IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'ROUTING',
1265 Entity_id => l_routing_id ) THEN
1266 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_NOT_VALID');
1267 FND_MSG_PUB.ADD;
1268 RAISE routing_update_dep_failure;
1269 END IF;
1270
1271 GMD_ROUTING_STEPS_PVT.update_step_dependencies
1272 ( p_routingstep_no => l_routingstep_no
1273 , p_dep_routingstep_no => p_dep_routingstep_no
1274 , p_routing_id => l_routing_id
1275 , p_update_table => p_update_table
1276 , x_return_status => x_return_status
1277 );
1278 /* Check if work was done */
1279 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1280 RAISE routing_update_dep_failure;
1281 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
1282
1283 fnd_msg_pub.count_and_get (
1284 p_count => x_message_count
1285 ,p_encoded => FND_API.g_false
1286 ,p_data => x_message_list);
1287
1288 IF x_message_count = 0 THEN
1289 IF (l_debug = 'Y') THEN
1290 gmd_debug.put_line('Routing was updated successfullly');
1291 END IF;
1292 END IF;
1293
1294 IF (P_commit) THEN
1295 COMMIT;
1296 END IF;
1297
1298 IF (l_debug = 'Y') THEN
1299 gmd_debug.put_line('Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1300 END IF;
1301
1302 EXCEPTION
1303 WHEN routing_update_dep_failure OR invalid_version THEN
1304 ROLLBACK TO SAVEPOINT update_step_dependency;
1305 IF (l_debug = 'Y') THEN
1306 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
1307 END IF;
1308 fnd_msg_pub.count_and_get (
1309 p_count => x_message_count
1310 ,p_encoded => FND_API.g_false
1311 ,p_data => x_message_list);
1312 x_return_status := FND_API.G_RET_STS_ERROR;
1313 WHEN setup_failure THEN
1314 ROLLBACK TO SAVEPOINT update_step_dependency;
1315 x_return_status := FND_API.G_RET_STS_ERROR;
1316 fnd_msg_pub.count_and_get (
1317 p_count => x_message_count
1318 ,p_encoded => FND_API.g_false
1319 ,p_data => x_message_list);
1320 WHEN OTHERS THEN
1321 ROLLBACK TO SAVEPOINT update_routing_details;
1322 fnd_msg_pub.add_exc_msg (gmd_routing_steps_PUB.m_pkg_name, l_api_name);
1323 IF (l_debug = 'Y') THEN
1324 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
1325 END IF;
1326 fnd_msg_pub.count_and_get (
1327 p_count => x_message_count
1328 ,p_encoded => FND_API.g_false
1329 ,p_data => x_message_list);
1330 x_return_status := FND_API.g_ret_sts_unexp_error;
1331
1332 END update_step_dependencies;
1333
1334 /* =============================================================== */
1335 /* Procedure: */
1336 /* Delete_Routing_step */
1337 /* */
1338 /* DESCRIPTION: */
1339 /* */
1340 /* API returns (x_return_code) = 'S' if the delete into routing */
1341 /* step dependency (fm_rout_dep table) is success. */
1342 /* */
1343 /* History : */
1344 /* Shyam 07/29/2002 Initial implementation */
1345 /* =============================================================== */
1346 PROCEDURE delete_routing_step
1347 ( p_api_version IN NUMBER := 1
1348 , p_init_msg_list IN BOOLEAN := TRUE
1349 , p_commit IN BOOLEAN := FALSE
1350 , p_routingstep_id IN fm_rout_dtl.routingstep_id%TYPE := NULL
1351 , p_routingstep_no IN fm_rout_dtl.routingstep_no%TYPE := NULL
1352 , p_routing_id IN fm_rout_dtl.routing_id%TYPE := NULL
1353 , p_routing_no IN gmd_routings.routing_no%TYPE := NULL
1354 , p_routing_vers IN gmd_routings.routing_vers%TYPE := NULL
1355 , x_message_count OUT NOCOPY NUMBER
1356 , x_message_list OUT NOCOPY VARCHAR2
1357 , x_return_status OUT NOCOPY VARCHAR2
1358 ) IS
1359
1360 /* Local variable section */
1361 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROUTING_STEP';
1362 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1363 l_return_from_routing_step_dep VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1364 l_routingstep_no fm_rout_dep.routingStep_no%TYPE;
1365 l_routingStep_id fm_rout_dtl.routingStep_id%TYPE;
1366 l_routing_id gmd_routings.routing_id%TYPE;
1367 l_dep_routingstep_no fm_rout_dep.dep_routingStep_no%TYPE;
1368
1369 /* Define Cursors */
1370 /* Cursor that check if there any row in the step dependency table that
1371 needs to be deleted */
1372 Cursor Check_Step_dep_rec(vRoutingstep_no fm_rout_dep.routingStep_no%TYPE
1373 ,vRouting_id gmd_routings.Routing_id%TYPE) IS
1374 Select dep_routingstep_no
1375 From fm_rout_dep
1376 Where routingStep_no = vRoutingStep_no
1377 And routing_id = vrouting_id;
1378
1379 /* Define Exceptions */
1380 routing_delete_step_failure EXCEPTION;
1381 routing_delete_stepdep_failure EXCEPTION;
1382 invalid_version EXCEPTION;
1383 setup_failure EXCEPTION;
1384
1385 BEGIN
1386 SAVEPOINT delete_routing_step;
1387
1388 /* Set the return status to success initially */
1389 x_return_status := FND_API.G_RET_STS_SUCCESS;
1390
1391 /* Initialize message list and count if needed */
1392 IF p_init_msg_list THEN
1393 fnd_msg_pub.initialize;
1394 END IF;
1395
1396 /* Intialize the setup fields */
1397 IF NOT gmd_api_grp.setup_done THEN
1398 gmd_api_grp.setup_done := gmd_api_grp.setup;
1399 END IF;
1400 IF NOT gmd_api_grp.setup_done THEN
1401 RAISE setup_failure;
1402 END IF;
1403
1404 /* Make sure we are call compatible */
1405 IF NOT FND_API.compatible_api_call ( gmd_routings_PUB.m_api_version
1406 ,p_api_version
1407 ,l_api_name
1408 ,gmd_routing_steps_PUB.m_pkg_name) THEN
1409 x_return_status := FND_API.G_RET_STS_ERROR;
1410 RAISE invalid_version;
1411 END IF;
1412
1413 /* Get routing id if it is not passed in as a parameter */
1414 /* Routing id may be used to get the routingStep_id (PK for fm_rout_dtl) */
1415 /* Get the routing_id value */
1416 IF (l_debug = 'Y') THEN
1417 gmd_debug.put_line('Validation : Check if routing header exists in the database ');
1418 END IF;
1419 IF p_routing_id IS NOT NULL THEN
1420 l_routing_id := p_routing_id;
1421 GMDRTVAL_PUB.check_routing(pRouting_no => p_routing_no
1422 ,pRouting_vers => p_routing_vers
1423 ,xRouting_id => l_routing_id
1424 ,xReturn_status => l_return_status);
1425
1426 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1427 /* it indicates that this routing does'ntexists */
1428 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
1429 FND_MSG_PUB.ADD;
1430 RAISE routing_delete_step_failure;
1431 END IF;
1432 ELSE /* usually in this case user must have passed routing_no and version */
1433 GMDRTVAL_PUB.check_routing(pRouting_no => p_routing_no
1434 ,pRouting_vers => p_routing_vers
1435 ,xRouting_id => l_routing_id
1436 ,xReturn_status => l_return_status);
1437
1438 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1439 /* it indicates that this routing does'ntexists */
1440 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
1441 FND_MSG_PUB.ADD;
1442 RAISE routing_delete_step_failure;
1443 END IF;
1444 END IF;
1445
1446 /* Check the routing id is not null */
1447 IF l_routing_id IS NULL THEN
1448 IF (l_debug = 'Y') THEN
1449 gmd_debug.put_line('Routing id is required');
1450 END IF;
1451 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1452 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTING_ID');
1453 FND_MSG_PUB.ADD;
1454 x_return_status := FND_API.g_ret_sts_error;
1455 END IF;
1456
1457 /* Routing Security Validation */
1458 /* Validation: Check if for given user this routing can be modified */
1459 IF (l_debug = 'Y') THEN
1460 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
1461 ||'Validation of user - owner orgn code = '||gmd_api_grp.user_id);
1462 END IF;
1463 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'ROUTING'
1464 ,Entity_id => l_routing_id) THEN
1465 RAISE routing_delete_step_failure;
1466 END IF;
1467
1468
1469 /* Get the RoutingStep_id and routingstep_no (routingstep_no is used
1470 for the routing step dep delete */
1471
1472 IF p_routingStep_id IS NOT NULL THEN
1473 l_routingstep_id := p_routingstep_id;
1474 IF (l_debug = 'Y') THEN
1475 gmd_debug.put_line(' get the RoutingStep_no- for rtstepid = '||l_routingStep_id);
1476 END IF;
1477 GMDRTVAL_PUB.get_routingstep_info(pxRoutingStep_no => l_routingstep_no
1478 ,pxRoutingStep_id => l_routingstep_id
1479 ,x_return_status => l_return_status );
1480 IF (l_debug = 'Y') THEN
1481 gmd_debug.put_line(' After get_rouingstep_info is called ret status = '||l_return_status);
1482 END IF;
1483 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1484 /* it indicates that this routing does'ntexists */
1485 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTINGSTEP_INVALID');
1486 FND_MSG_PUB.ADD;
1487 RAISE routing_delete_step_failure;
1488 END IF;
1489 ELSE
1490 IF (l_debug = 'Y') THEN
1491 gmd_debug.put_line(' get the RoutingStep_id - if it is not passed as a parameter ');
1492 END IF;
1493 l_routingstep_no := p_routingstep_no;
1494 GMDRTVAL_PUB.get_routingstep_info(pRouting_id => l_routing_id
1495 ,pxRoutingStep_no => l_routingstep_no
1496 ,pxRoutingStep_id => l_routingstep_id
1497 ,x_return_status => l_return_status );
1498 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1499 /* it indicates that this routing does'ntexists */
1500 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTINGSTEP_INVALID');
1501 FND_MSG_PUB.ADD;
1502 RAISE routing_delete_step_failure;
1503 END IF;
1504 END IF;
1505
1506 /* Check the routing id is not null */
1507 IF l_routingstep_id IS NULL THEN
1508 IF (l_debug = 'Y') THEN
1509 gmd_debug.put_line('Routing step id is required');
1510 END IF;
1511 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1512 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTINGSTEP_ID');
1513 FND_MSG_PUB.ADD;
1514 RAISE routing_delete_step_failure;
1515 END IF;
1516
1517 /* Validation : Check if this step is used in recipe override table and
1518 step material association table. If it is then delete is not allowed */
1519 IF GMDRTVAL_PUB.Check_routing_override_exists(l_routingstep_id) THEN
1520 FND_MESSAGE.SET_NAME('GMD', 'GMD_STEP_USED_IN_RECIPE');
1521 FND_MSG_PUB.ADD;
1522 RAISE routing_delete_step_failure;
1523 END IF;
1524
1525 /* Validation : Routing status is not On Hold nor Obsolete/Archived
1526 and Routing is not logically deleted */
1527 IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'ROUTING',
1528 Entity_id => l_routing_id ) THEN
1529 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_NOT_VALID');
1530 FND_MSG_PUB.ADD;
1531 RAISE routing_delete_step_failure;
1532 END IF;
1533
1534 IF (l_debug = 'Y') THEN
1535 gmd_debug.put_line('RoutingStep_id = '||l_routingStep_id );
1536 END IF;
1537 /* Actual delete is performed */
1538 GMD_ROUTING_STEPS_PVT.delete_routing_step
1539 ( p_routingstep_id => l_routingstep_id
1540 , p_routing_id => l_routing_id
1541 , x_return_status => x_return_status
1542 );
1543
1544 fnd_msg_pub.count_and_get (
1545 p_count => x_message_count
1546 ,p_encoded => FND_API.g_false
1547 ,p_data => x_message_list);
1548
1549 IF x_message_count = 0 THEN
1550 IF (l_debug = 'Y') THEN
1551 gmd_debug.put_line('Routing step was deleted successfullly');
1552 END IF;
1553 END IF;
1554
1555 IF (P_commit) THEN
1556 COMMIT;
1557 END IF;
1558
1559 IF (l_debug = 'Y') THEN
1560 gmd_debug.put_line('Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1561 END IF;
1562
1563 EXCEPTION
1564 WHEN routing_delete_step_failure OR invalid_version THEN
1565 ROLLBACK TO SAVEPOINT delete_routing_step;
1566 IF (l_debug = 'Y') THEN
1567 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
1568 END IF;
1569 fnd_msg_pub.count_and_get (
1570 p_count => x_message_count
1571 ,p_encoded => FND_API.g_false
1572 ,p_data => x_message_list);
1573 x_return_status := FND_API.G_RET_STS_ERROR;
1574 WHEN setup_failure THEN
1575 ROLLBACK TO SAVEPOINT delete_routing_step;
1576 x_return_status := FND_API.G_RET_STS_ERROR;
1577 fnd_msg_pub.count_and_get (
1578 p_count => x_message_count
1579 ,p_encoded => FND_API.g_false
1580 ,p_data => x_message_list);
1581 WHEN routing_delete_stepdep_failure THEN
1582 ROLLBACK TO SAVEPOINT delete_routing_step;
1583 IF (l_debug = 'Y') THEN
1584 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'delete step dep API not complete');
1585 END IF;
1586 fnd_msg_pub.count_and_get (
1587 p_count => x_message_count
1588 ,p_encoded => FND_API.g_false
1589 ,p_data => x_message_list);
1590 x_return_status := FND_API.G_RET_STS_ERROR;
1591 WHEN OTHERS THEN
1592 ROLLBACK TO SAVEPOINT delete_routing_step;
1593 fnd_msg_pub.add_exc_msg (gmd_routing_steps_PUB.m_pkg_name, l_api_name);
1594 IF (l_debug = 'Y') THEN
1595 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
1596 END IF;
1597 fnd_msg_pub.count_and_get (
1598 p_count => x_message_count
1599 ,p_encoded => FND_API.g_false
1600 ,p_data => x_message_list);
1601 x_return_status := FND_API.g_ret_sts_unexp_error;
1602
1603 END delete_routing_step;
1604
1605 /* =============================================================== */
1606 /* Procedure: */
1607 /* delete_step_dependencies */
1608 /* */
1609 /* DESCRIPTION: */
1610 /* */
1611 /* API returns (x_return_code) = 'S' if the delete in routing */
1612 /* step dependency (fm_rout_dep table) is success. */
1613 /* */
1614 /* History : */
1615 /* Shyam 07/29/2002 Initial implementation */
1616 /* =============================================================== */
1617 PROCEDURE delete_step_dependencies
1618 ( p_api_version IN NUMBER := 1
1619 , p_init_msg_list IN BOOLEAN := TRUE
1620 , p_commit IN BOOLEAN := FALSE
1621 , p_routingstep_no IN fm_rout_dep.routingstep_no%TYPE
1622 , p_dep_routingstep_no IN fm_rout_dep.routingstep_no%TYPE := NULL
1623 , p_routing_id IN fm_rout_dep.routing_id%TYPE := NULL
1624 , p_routing_no IN gmd_routings.routing_no%TYPE := NULL
1625 , p_routing_vers IN gmd_routings.routing_vers%TYPE := NULL
1626 , x_message_count OUT NOCOPY NUMBER
1627 , x_message_list OUT NOCOPY VARCHAR2
1628 , x_return_status OUT NOCOPY VARCHAR2
1629 ) IS
1630
1631 /* Local variable section */
1632 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_STEP_DEPENDENCIES';
1633 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1634
1635 l_routingstep_no fm_rout_dep.routingStep_no%TYPE;
1636 l_routingStep_id fm_rout_dtl.routingStep_id%TYPE;
1637 l_dep_routingstep_no fm_rout_dep.dep_routingStep_no%TYPE;
1638 l_routing_id fm_rout_dep.routing_id%TYPE;
1639
1640 /* Define Exceptions */
1641 routing_delete_dep_failure EXCEPTION;
1642 invalid_version EXCEPTION;
1643 setup_failure EXCEPTION;
1644
1645 BEGIN
1646 SAVEPOINT delete_step_dependency;
1647
1648 /* Set the return status to success initially */
1649 x_return_status := FND_API.G_RET_STS_SUCCESS;
1650
1651 /* Initialize message list and count if needed */
1652 IF p_init_msg_list THEN
1653 fnd_msg_pub.initialize;
1654 END IF;
1655
1656 /* Intialize the setup fields */
1657 IF NOT gmd_api_grp.setup_done THEN
1658 gmd_api_grp.setup_done := gmd_api_grp.setup;
1659 END IF;
1660 IF NOT gmd_api_grp.setup_done THEN
1661 RAISE setup_failure;
1662 END IF;
1663
1664 /* Make sure we are call compatible */
1665 IF NOT FND_API.compatible_api_call ( gmd_routings_PUB.m_api_version
1666 ,p_api_version
1667 ,l_api_name
1668 ,gmd_routing_steps_PUB.m_pkg_name) THEN
1669 x_return_status := FND_API.G_RET_STS_ERROR;
1670 RAISE invalid_version;
1671 END IF;
1672
1673 /* Validation prior to Routings Step dependency delete */
1674
1675 /* Validation 1: Check if this step is being used in other tables */
1676 /* Tables to be checked are mainly gmd step material association
1677 and maybe batch table. Prevent delete if these steps are used in these tables */
1678 /* Get the routing_id value */
1679 IF (l_debug = 'Y') THEN
1680 gmd_Debug.put_line('Validation: In dep step API if routing header exists in the database ');
1681 END IF;
1682 IF p_routing_id IS NOT NULL THEN
1683 l_routing_id := p_routing_id;
1684 GMDRTVAL_PUB.check_routing(pRouting_no => p_routing_no
1685 ,pRouting_vers => p_routing_vers
1686 ,xRouting_id => l_routing_id
1687 ,xReturn_status => l_return_status);
1688
1689 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1690 /* it indicates that this routing does'ntexists */
1691 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
1692 FND_MSG_PUB.ADD;
1693 RAISE routing_delete_dep_failure;
1694 END IF;
1695 ELSE /* usually in this case user must have passed routing_no and version */
1696 GMDRTVAL_PUB.check_routing(pRouting_no => p_routing_no
1697 ,pRouting_vers => p_routing_vers
1698 ,xRouting_id => l_routing_id
1699 ,xReturn_status => l_return_status);
1700
1701 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1702 /* it indicates that this routing does'ntexists */
1703 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
1704 FND_MSG_PUB.ADD;
1705 RAISE routing_delete_dep_failure;
1706 END IF;
1707 END IF;
1708
1709 /* Check the routing id is not null */
1710 IF l_routing_id IS NULL THEN
1711 IF (l_debug = 'Y') THEN
1712 gmd_debug.put_line('Routing id is required');
1713 END IF;
1714 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1715 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTING_ID');
1716 FND_MSG_PUB.ADD;
1717 RAISE routing_delete_dep_failure;
1718 END IF;
1719
1720 /* Routing Security Validation */
1721 /* Validation: Check if for given user this routing can be modified */
1722 IF (l_debug = 'Y') THEN
1723 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
1724 ||'Validation of user - owner orgn code = '||gmd_api_grp.user_id);
1725 END IF;
1726 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'ROUTING'
1727 ,Entity_id => l_routing_id) THEN
1728 RAISE routing_delete_dep_failure;
1729 END IF;
1730
1731 IF p_routingStep_no IS NOT NULL THEN
1732 l_routingstep_no := p_routingstep_no;
1733 GMDRTVAL_PUB.get_routingstep_info(pRouting_id => l_routing_id
1734 ,pxRoutingStep_no => l_routingstep_no
1735 ,pxRoutingStep_id => l_routingstep_id
1736 ,x_return_status => l_return_status );
1737 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1738 /* it indicates that this routing does'ntexists */
1739 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTINGSTEP_INVALID');
1740 FND_MSG_PUB.ADD;
1741 RAISE routing_delete_dep_failure;
1742 END IF;
1743 END IF;
1744
1745 /* Check the routingstep no is not null */
1746 IF p_routingstep_no IS NULL THEN
1747 IF (l_debug = 'Y') THEN
1748 gmd_debug.put_line('Routing step number is required');
1749 END IF;
1750 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1751 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTINGSTEP_NO');
1752 FND_MSG_PUB.ADD;
1753 RAISE routing_delete_dep_failure;
1754 END IF;
1755
1756 /* Check the routingstep no is not null */
1757 IF p_dep_routingstep_no IS NULL THEN
1758 IF (l_debug = 'Y') THEN
1759 gmd_debug.put_line('Dep Routing step number is required');
1760 END IF;
1761 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
1762 FND_MESSAGE.SET_TOKEN ('MISSING', 'DEP_ROUTINGSTEP_NO');
1763 FND_MSG_PUB.ADD;
1764 RAISE routing_delete_dep_failure;
1765 END IF;
1766
1767 /* Actual delete in fm_rout_dep table */
1768 /* This delete can be specific to a dep_routingstep_no or a
1769 Routingstep_no */
1770 IF (l_debug = 'Y') THEN
1771 gmd_Debug.put_line('About to delete from step dep table - the routingstep no = '
1772 ||p_routingstep_no ||' and routing id = '||l_routing_id);
1773 END IF;
1774
1775
1776 /* Validation : Routing status is not On Hold nor Obsolete/Archived
1777 and Routing is not logically deleted */
1778 IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED(Entity => 'ROUTING',
1779 Entity_id => l_routing_id ) THEN
1780 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_NOT_VALID');
1781 FND_MSG_PUB.ADD;
1782 RAISE routing_delete_dep_failure;
1783 END IF;
1784
1785 GMD_ROUTING_STEPS_PVT.delete_step_dependencies
1786 ( p_routingstep_no => p_routingstep_no
1787 , p_dep_routingstep_no => p_dep_routingstep_no
1788 , p_routing_id => l_routing_id
1789 , x_return_status => x_return_status
1790 );
1791
1792 /* Check if work was done */
1793 IF SQL%ROWCOUNT = 0 THEN
1794 RAISE routing_delete_dep_failure;
1795 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
1796
1797 fnd_msg_pub.count_and_get (
1798 p_count => x_message_count
1799 ,p_encoded => FND_API.g_false
1800 ,p_data => x_message_list);
1801
1802 IF x_message_count = 0 THEN
1803 IF (l_debug = 'Y') THEN
1804 gmd_debug.put_line('Routing was deleted successfullly');
1805 END IF;
1806 END IF;
1807
1808 IF (P_commit) THEN
1809 COMMIT;
1810 END IF;
1811
1812 IF (l_debug = 'Y') THEN
1813 gmd_debug.put_line('Completed '||l_api_name ||' at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1814 END IF;
1815
1816 EXCEPTION
1817 WHEN routing_delete_dep_failure OR invalid_version THEN
1818 ROLLBACK TO SAVEPOINT delete_step_dependency;
1819 IF (l_debug = 'Y') THEN
1820 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
1821 END IF;
1822 fnd_msg_pub.count_and_get (
1823 p_count => x_message_count
1824 ,p_encoded => FND_API.g_false
1825 ,p_data => x_message_list);
1826 x_return_status := FND_API.G_RET_STS_ERROR;
1827 WHEN setup_failure THEN
1828 ROLLBACK TO SAVEPOINT delete_step_dependency;
1829 x_return_status := FND_API.G_RET_STS_ERROR;
1830 fnd_msg_pub.count_and_get (
1831 p_count => x_message_count
1832 ,p_encoded => FND_API.g_false
1833 ,p_data => x_message_list);
1834 WHEN OTHERS THEN
1835 ROLLBACK TO SAVEPOINT delete_step_dependency;
1836 fnd_msg_pub.add_exc_msg (gmd_routing_steps_PUB.m_pkg_name, l_api_name);
1837 IF (l_debug = 'Y') THEN
1838 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
1839 END IF;
1840 fnd_msg_pub.count_and_get (
1841 p_count => x_message_count
1842 ,p_encoded => FND_API.g_false
1843 ,p_data => x_message_list);
1844 x_return_status := FND_API.g_ret_sts_unexp_error;
1845
1846 END delete_step_dependencies;
1847
1848 END GMD_ROUTING_STEPS_PUB;
[Home] [Help]