1 PACKAGE BODY GMD_ROUTINGS_PUB AS
2 /* $Header: GMDPROUB.pls 120.4.12010000.2 2008/11/12 18:16:40 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 /* P.Raghu 08/27/2003 Bug#3068013 K is intialized with 1. */
32 /* kkillams23-03-2004 Added call to modify_status to set routing */
33 /* status to default status if default status is */
34 /* defined organization level w.r.t. bug 3408799 */
35 /* Uday Phadtare 13-MAR-2008 Bug 6871738. Select ROUTING_CLASS_UOM */
36 /* instead of UOM in Cursor Rout_cls_cur. */
37 /* ================================================================= */
38 PROCEDURE insert_routing
39 (
40 p_api_version IN NUMBER := 1
41 , p_init_msg_list IN BOOLEAN := TRUE
42 , p_commit IN BOOLEAN := FALSE
43 , p_routings IN gmd_routings%ROWTYPE
44 , p_routings_step_tbl IN GMD_ROUTINGS_PUB.gmd_routings_step_tab
45 , p_routings_step_dep_tbl IN GMD_ROUTINGS_PUB.gmd_routings_step_dep_tab
46 , x_message_count OUT NOCOPY NUMBER
47 , x_message_list OUT NOCOPY VARCHAR2
48 , x_return_status OUT NOCOPY VARCHAR2
49 ) IS
50
51 /* Local variable section */
52 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROUTING';
53 l_row_id ROWID;
54 k NUMBER := 1;
55 l_return_from_routing_step_dep VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
56 l_routing_id NUMBER;
57 l_owner_id NUMBER;
58 l_oprn_no gmd_operations.oprn_no%TYPE;
59 l_oprn_vers gmd_operations.oprn_vers%TYPE;
60 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
61 l_return_from_routing_hdr VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
62 l_return_from_routing_step VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
63 l_routing_qty gmd_routings.routing_qty%TYPE := 0;
64 l_process_loss gmd_routings.process_loss%TYPE := 0;
65 l_routing_class_um fm_rout_cls.uom%TYPE;
66 l_stepdep_tbl GMD_ROUTINGS_PUB.gmd_routings_step_dep_tab;
67 l_step_dep_tab GMD_ROUTINGS_PUB.gmd_routings_step_dep_tab;
68
69 --kkillams,bug 3408799
70 l_entity_status GMD_API_GRP.status_rec_type;
71
72 /* Define cursors */
73 /* gets the routing class uom */
74 --Bug 6871738. Select ROUTING_CLASS_UOM instead of UOM.
75 Cursor Rout_cls_cur(vRouting_class fm_rout_hdr.routing_class%TYPE) IS
76 Select ROUTING_CLASS_UOM
77 From fm_rout_cls
78 Where routing_class = vRouting_class
79 and delete_mark = 0;
80
81 /* gets the operation no and version associated to the routing detail/Step */
82 Cursor Get_oprn_details(vOprn_id fm_rout_dtl.oprn_id%TYPE) IS
83 Select oprn_no, oprn_vers
84 From gmd_operations_b
85 Where oprn_id = vOprn_id;
86
87 /* get routing id sequence */
88 CURSOR Get_routing_id_seq IS
89 SELECT gem5_routing_id_s.NEXTVAL
90 FROM sys.dual;
91
92 /* B5609637 UOM cursor to find the routing UOM class and the routing class UOM class */
93 CURSOR Cur_uom_class (p_uom_code VARCHAR2) IS
94 SELECT uom_class
95 FROM mtl_units_of_measure
96 WHERE uom_code = p_uom_code;
97
98 l_routing_class_um_class VARCHAR2(30);
99 l_routing_um_class VARCHAR2(30);
100 l_routing_qty_cnv NUMBER;
101
102
103 /* get a record type */
104 l_routings_rec gmd_routings%ROWTYPE;
105
106 /* Define Exceptions */
107 routing_creation_failure EXCEPTION;
108 routing_step_creation_failure EXCEPTION;
109 routing_step_dep_failure EXCEPTION;
110 invalid_version EXCEPTION;
111 setup_failure EXCEPTION;
112 default_status_err EXCEPTION;
113
114 BEGIN
115 SAVEPOINT create_routing;
116
117 /* Set the return status to success initially */
118 x_return_status := FND_API.G_RET_STS_SUCCESS;
119 l_routings_rec := p_routings;
120
121 /* B5609637 Initialize the routing qty and the process loss with the passed values */
122 l_process_loss := p_routings.process_loss;
123 l_routing_qty := p_routings.routing_qty;
124
125
126
127 /* Initialize message list and count if needed */
128 IF p_init_msg_list THEN
129 fnd_msg_pub.initialize;
130 END IF;
131
132 IF (l_debug = 'Y') THEN
133 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||'Begin of API');
134 END IF;
135
136
137 /* Intialize the setup fields */
138 IF NOT gmd_api_grp.setup_done THEN
139 gmd_api_grp.setup_done := gmd_api_grp.setup;
140 END IF;
141 IF NOT gmd_api_grp.setup_done THEN
142 RAISE setup_failure;
143 END IF;
144
145 /* Make sure we are call compatible */
146 IF NOT FND_API.compatible_api_call ( gmd_routings_PUB.m_api_version
147 ,p_api_version
148 ,'INSERT_ROUTING'
149 ,gmd_routings_PUB.m_pkg_name) THEN
150 RAISE invalid_version;
151 END IF;
152
153 IF p_routings.routing_no IS NULL THEN
154 IF (l_debug = 'Y') THEN
155 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||'Routing Number required');
156 END IF;
157 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
158 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTING_NO');
159 FND_MSG_PUB.ADD;
160 RAISE routing_creation_failure;
161 END IF;
162
163 IF p_routings.routing_vers IS NULL THEN
164 IF (l_debug = 'Y') THEN
165 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||'Routing Version required');
166 END IF;
167 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
168 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTING_VERS');
169 FND_MSG_PUB.ADD;
170 RAISE routing_creation_failure;
171 ELSIF p_routings.routing_vers IS NOT NULL THEN
172 IF (p_routings.routing_vers < 0 ) THEN
173 FND_MESSAGE.SET_NAME ('GMD', 'GMD_NEGATIVE_FIELDS');
174 FND_MSG_PUB.ADD;
175 RAISE routing_creation_failure;
176 END IF;
177 END IF;
178
179 IF p_routings.routing_desc IS NULL THEN
180 IF (l_debug = 'Y') THEN
181 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||'Routing Description required');
182 END IF;
183 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
184 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTING_DESC');
185 FND_MSG_PUB.ADD;
186 RAISE routing_creation_failure;
187 END IF;
188
189 /* routing_uom must be passed, otherwise give error */
190 IF p_routings.routing_uom IS NULL THEN
191 IF (l_debug = 'Y') THEN
192 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||'Item uom required');
193 END IF;
194 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
195 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTING_UOM');
196 FND_MSG_PUB.ADD;
197 RAISE routing_creation_failure;
198 /* call common function to check if um passed is valid */
199 ELSIF (NOT(gmd_api_grp.validate_um(p_routings.routing_uom))) THEN
200 IF (l_debug = 'Y') THEN
201 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||'Item uom invalid');
202 END IF;
203 FND_MESSAGE.SET_NAME('GMA', 'SY_INVALID_UM_CODE');
204 FND_MSG_PUB.ADD;
205 RAISE routing_creation_failure;
206 END IF;
207
208 /*
209 * Convergence related fix - Shyam S
210 *
211 */
212
213 --Check that organization id is not null if raise an error message
214 IF (p_routings.owner_organization_id IS NULL) THEN
215 FND_MESSAGE.SET_NAME('GMD', 'GMD_MISSING_ORGANIZATION_ID');
216 FND_MSG_PUB.Add;
217 RAISE routing_creation_failure;
218 END IF;
219
220 -- Check if the responsibility has access to the organization
221 IF NOT (GMD_API_GRP.OrgnAccessible (powner_orgn_id => p_routings.owner_organization_id) ) THEN
222 RAISE routing_creation_failure;
223 END IF;
224
225 --Check the organization id passed is process enabled if not raise an error message
226 IF NOT (gmd_api_grp.check_orgn_status(p_routings.owner_organization_id)) THEN
227 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ORGANIZATION_ID');
228 FND_MESSAGE.SET_TOKEN('ORGN_ID', p_routings.owner_organization_id);
229 FND_MSG_PUB.Add;
230 RAISE routing_creation_failure;
231 END IF;
232
233 /* Validation : Validate if the Routing start and end dates */
234 l_routings_rec.effective_start_date := TRUNC(NVL(p_routings.effective_start_date,SYSDATE));
235 IF l_routings_rec.effective_start_date IS NOT NULL AND
236 p_routings.effective_end_date IS NOT NULL THEN
237 /* Effective end date must be greater than start date, otherwise give error */
238 IF l_routings_rec.effective_start_date > p_routings.effective_end_date THEN
239 IF (l_debug = 'Y') THEN
240 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
241 ||'effective start date must be less then end date');
242 END IF;
243 FND_MESSAGE.SET_NAME('GMD', 'QC_MIN_MAX_DATE');
244 FND_MSG_PUB.ADD;
245 RAISE routing_creation_failure;
246 END IF;
247 END IF;
248
249 /* Validation 1. Check if this routing that is created does not exists
250 in the the database. The routing_id is the PK or Routing_no and version is
251 the unique key for this table (gmd_routings_b). */
252 GMDRTVAL_PUB.check_routing(pRouting_no => p_routings.routing_no
253 ,pRouting_vers => p_routings.routing_vers
254 ,xRouting_id => l_routing_id
255 ,xReturn_status => l_return_status);
256
257 IF l_return_status <> 'E' THEN /* it indicates that this routing exists */
258 FND_MESSAGE.SET_NAME('GMA', 'SY_INVALID_DUPLICATION');
259 FND_MSG_PUB.ADD;
260 RAISE routing_creation_failure;
261 ELSE
262 OPEN Get_routing_id_seq;
263 FETCH Get_routing_id_seq INTO l_routing_id;
264 IF Get_routing_id_seq%NOTFOUND then
265 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ROUT_SEQ');
266 FND_MSG_PUB.ADD;
267 RAISE routing_creation_failure;
268 END IF;
269 CLOSE Get_routing_id_seq;
270 END IF; /* l_return_status <> 'E' */
271
272 /* Validation : Validate if the Routing dates fall within the associated operation
273 start and end dates. */
274 FOR b IN 1 .. p_routings_step_tbl.count LOOP
275 OPEN Get_oprn_details(p_routings_step_tbl(b).oprn_id);
276 FETCH Get_oprn_details INTO l_oprn_no, l_oprn_vers;
277 IF (Get_oprn_details%NOTFOUND) THEN
278 FND_MESSAGE.SET_NAME('GMD', 'GMD_MISSING_OPRN');
279 FND_MSG_PUB.ADD;
280 CLOSE Get_oprn_details;
281 RAISE routing_creation_failure;
282 END IF;
283 CLOSE Get_oprn_details;
284
285 IF GMDRTVAL_PUB.check_oprn(poprn_no =>l_oprn_no
286 ,poprn_vers => l_oprn_vers
287 ,prouting_start_date => l_routings_rec.effective_start_date
288 ) <> 0 THEN
289 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_DATES_INVALID');
290 FND_MSG_PUB.ADD;
291 RAISE routing_creation_failure;
292 END IF;
293 END LOOP; /* loop to validate routing dates */
294
295 /* Assingning the owner_id,enforce_step_dependency if they are not passed */
296 l_routings_rec.owner_id := gmd_api_grp.user_id; -- Bug 4603060
297 l_routings_rec.enforce_step_dependency := NVL(p_routings.enforce_step_dependency,0);
298
299 /* Validation : Check if Routing class is valid */
300 IF p_routings.routing_class IS NOT NULL THEN
301 IF GMDRTVAL_PUB.check_routing_class(p_routings.routing_class) <> 0 THEN
302 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ROUT_CLS');
303 FND_MSG_PUB.ADD;
304 RAISE routing_creation_failure;
305 END IF;
306 END IF;
307
308 /* Calculations - Process loss. Based on the Routing qty, its uom and Routing class
309 calculate the planned process loss.
310 This is done only if the a NULL value was passed for this field */
311 IF (p_routings.process_loss IS NULL) THEN
312 /* Get the routing_qty in its routing_class uom */
313 IF (p_routings.Routing_class IS NOT NULL) THEN
314 OPEN Rout_cls_cur(p_routings.Routing_class);
315 FETCH Rout_cls_cur INTO l_routing_class_um;
316 IF Rout_cls_cur%NOTFOUND THEN
317 FND_MESSAGE.SET_NAME('GMD', 'GMD_TPL_WO_ROUT_CLS');
318 FND_MSG_PUB.Add;
319 CLOSE Rout_cls_cur;
320 Raise routing_creation_failure;
321 END IF;
322 CLOSE Rout_cls_cur;
323
324 /* Bug 5609637 , check for the uom class of routing and the routing class UOM */
325 OPEN Cur_uom_class(l_routing_class_um);
326 FETCH Cur_uom_class INTO l_routing_class_um_class;
327 CLOSE Cur_uom_class;
328
329 OPEN Cur_uom_class(p_routings.routing_uom);
330 FETCH Cur_uom_class INTO l_routing_um_class;
331 CLOSE Cur_uom_class;
332
333 IF l_routing_um_class = l_routing_class_um_class THEN
334
335 l_routing_qty_cnv := INV_CONVERT.inv_um_convert
336 ( item_id => null
337 ,precision => 5
338 ,from_quantity => p_routings.Routing_qty
339 ,from_unit => p_routings.routing_uom
340 ,to_unit => l_routing_class_um
341 ,from_name => NULL
342 ,to_name => NULL);
343
344 /* Calculate the process loss */
345 l_process_loss := GMDRTVAL_PUB.get_theoretical_process_loss
346 (prouting_class => p_routings.Routing_class,
347 pquantity => l_routing_qty_cnv);
348
349 ELSE
350 FND_MESSAGE.SET_NAME('GMD', 'GMD_RTG_CLS_VS_RTG_UM_TYPE');
351 FND_MSG_PUB.Add;
352 x_return_status := FND_API.G_RET_STS_ERROR;
353 Raise routing_creation_failure;
354
355 END IF; /* IF l_routing_um_class = l_routing_class_um_class */
356
357 END IF; /* if routing class is not null */
358 ELSE
359 l_process_loss := p_routings.process_loss;
360 l_routing_qty := p_routings.routing_qty;
361 END IF; /* if process loss is null */
362
363 /* Assign values that were derived in this API */
364 l_routings_rec.routing_id := l_routing_id;
365 l_routings_rec.process_loss := l_process_loss;
366 l_routings_rec.routing_qty := NVL(l_routing_qty,0);
367 l_routings_rec.contiguous_ind := NVL(p_routings.contiguous_ind,0);
368
369 /* Following steps are followed during creation of a routing
370 1. Business Rule : There must be at least one routing step for a
371 routing header to be created.
372 2. After routing steps are created, routing step dependencies can be
373 created for these steps. However, there need to be more than one routing steps
374 to create step dependencies for this routing. Routing details/Steps API
375 should take care of this. */
376 IF (p_routings_step_tbl.count > 0) THEN
377 /* Step 1 : Create Routing header */
378 GMD_ROUTINGS_PVT.insert_routing
379 ( p_routings => l_routings_rec
380 , x_message_count => x_message_count
381 , x_message_list => x_message_list
382 , x_return_status => x_return_status
383 );
384 /* After creating this routing pass the routing id */
385 /* to the function that creates the routing steps */
386
387 /* Step 2 : Create Routing steps */
388 IF (l_debug = 'Y') THEN
389 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
390 ||'Insert the routing steps for routing with routing id = '||l_routing_id);
391 END IF;
392
393 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
394 Raise routing_creation_failure;
395 END IF;
396
397 /* Looping each routing step detail row */
398 /* After creating routing steps pass the routing id */
399 /* and the routing step no to the function that generates */
400 /* the step dependencies */
401 FOR i IN 1 .. p_routings_step_tbl.count LOOP
402 GMD_ROUTING_STEPS_PUB.insert_routing_steps
403 (p_routing_id => l_routing_id
404 ,p_routing_step_rec => p_routings_step_tbl(i)
405 ,p_routings_step_dep_tbl => l_stepdep_tbl
406 ,p_commit => FALSE
407 ,x_message_count => x_message_count
408 ,x_message_list => x_message_list
409 ,x_return_status => l_return_from_routing_step
410 );
411 -- Check if routing detail was created
412 IF l_return_from_routing_step <> FND_API.G_RET_STS_SUCCESS THEN
413 RAISE routing_step_creation_failure;
414 END IF;
415 END LOOP; /* End loop for p_routings_step_tbl */
416
417 IF p_routings_step_dep_tbl.count > 0 THEN
418 IF (l_debug = 'Y') THEN
419 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
420 ||'Creating Routing Step dependencies ');
421 END IF;
422 FOR i IN 1 .. p_routings_step_tbl.count LOOP
423 -- Call the routing step dep function
424 -- For each routingStep_no, routing_id enter all the dependent
425 -- routing step nos
426 -- Construct a PL/SQL table that is specific only to this
427 -- routing step no and routing id.
428 l_step_dep_tab.delete;
429 /* Begin Bug#3068013 P.Raghu */
430 /* Initializing k */
431 k := 1;
432 /* End Bug#3068013 */
433 FOR j IN 1 .. p_routings_step_dep_tbl.count LOOP
434 IF (p_routings_step_tbl(i).ROUTINGSTEP_NO
435 = p_routings_step_dep_tbl(j).ROUTINGSTEP_NO) THEN
436 l_step_dep_tab(k).routingstep_no := p_routings_step_dep_tbl(j).routingstep_no ;
437 l_step_dep_tab(k).dep_routingstep_no := p_routings_step_dep_tbl(j).dep_routingstep_no ;
438 l_step_dep_tab(k).routing_id := l_routing_id ;
439 l_step_dep_tab(k).dep_type := p_routings_step_dep_tbl(j).dep_type ;
440 l_step_dep_tab(k).rework_code := p_routings_step_dep_tbl(j).rework_code ;
441 l_step_dep_tab(k).standard_delay := p_routings_step_dep_tbl(j).standard_delay ;
442 l_step_dep_tab(k).minimum_delay := p_routings_step_dep_tbl(j).minimum_delay ;
443 l_step_dep_tab(k).max_delay := p_routings_step_dep_tbl(j).max_delay ;
444 l_step_dep_tab(k).transfer_qty := p_routings_step_dep_tbl(j).transfer_qty ;
445 l_step_dep_tab(k).RoutingStep_No_uom := p_routings_step_dep_tbl(j).RoutingStep_No_uom ;
446 l_step_dep_tab(k).text_code := p_routings_step_dep_tbl(j).text_code ;
447 l_step_dep_tab(k).last_updated_by := p_routings_step_dep_tbl(j).last_updated_by ;
448 l_step_dep_tab(k).created_by := p_routings_step_dep_tbl(j).created_by ;
449 l_step_dep_tab(k).last_update_date := p_routings_step_dep_tbl(j).last_update_date ;
450 l_step_dep_tab(k).creation_date := p_routings_step_dep_tbl(j).creation_date ;
451 l_step_dep_tab(k).last_update_login := p_routings_step_dep_tbl(j).last_update_login ;
452 l_step_dep_tab(k).transfer_pct := p_routings_step_dep_tbl(j).transfer_pct ;
453
454 k := k + 1;
455 END IF;
456 END LOOP;
457
458 /* Since we call this procedure for each routingStep we dont have to reinitialize K
459 value after populating dependency PLSQL table. Call the step dependency function */
460
461 IF l_step_dep_tab.count > 0 THEN
462 GMD_ROUTING_STEPS_PUB.insert_step_dependencies
463 (
464 p_routing_id => l_routing_id
465 ,p_routingstep_no => p_routings_step_tbl(i).routingstep_no
466 ,p_routings_step_dep_tbl => l_step_dep_tab
467 ,p_commit => FALSE
468 ,x_message_count => x_message_count
469 ,x_message_list => x_message_list
470 ,x_return_status => l_return_from_routing_step_dep
471 );
472
473 /* Check if insert of step dependency was done */
474 IF l_return_from_routing_step_dep <> FND_API.G_RET_STS_SUCCESS THEN
475 RAISE routing_step_dep_failure;
476 END IF; /* IF l_return_from_routing_step_dep <> FND_API.G_RET_STS_SUCCESS */
477 END IF; /* when l_step_dep_tab.count > 0 */
478 END LOOP;
479 END IF; /* if p_routings_step_dep_tbl.count > 0 */
480 ELSE
481 IF (l_debug = 'Y') THEN
482 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
483 ||'Routing API needs atleast one step to create its header');
484 END IF;
485 RAISE routing_creation_failure;
486 END IF;
487
488 /* Check if work was done */
489 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
490 RAISE routing_creation_failure;
491 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
492
493 IF (P_commit) THEN
494 COMMIT;
495 --kkillams,bug 3408799
496 SAVEPOINT default_status_sp;
497 --Getting the default status for the owner orgn code or null orgn of recipe from parameters table
498 gmd_api_grp.get_status_details (V_entity_type => 'ROUTING',
499 V_orgn_id => p_routings.owner_organization_id, --W.r.t. bug 4004501
500 X_entity_status => l_entity_status);
501 --Add this code after the call to gmd_routings_pkg.insert_row.
502 IF (l_entity_status.entity_status > 100) THEN
503 Gmd_status_pub.modify_status ( p_api_version => 1
504 , p_init_msg_list => TRUE
505 , p_entity_name =>'ROUTING'
506 , p_entity_id => l_routings_rec.routing_id
507 , p_entity_no => NULL
508 , p_entity_version => NULL
509 , p_to_status => l_entity_status.entity_status
510 , p_ignore_flag => FALSE
511 , x_message_count => x_message_count
512 , x_message_list => x_message_list
513 , x_return_status => X_return_status);
514 IF x_return_status NOT IN (FND_API.g_ret_sts_success,'P') THEN
515 RAISE default_status_err;
516 END IF; --x_return_status NOT IN (FND_API.g_ret_sts_success,'P')
517 END IF; --l_entity_status <> 100
518 COMMIT;
519 END IF; --P_commit
520
521 fnd_msg_pub.count_and_get (
522 p_count => x_message_count
523 ,p_encoded => FND_API.g_false
524 ,p_data => x_message_list);
525
526 IF x_message_count = 0 THEN
527 IF (l_debug = 'Y') THEN
528 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
529 ||'Routing Header was created successfully');
530 END IF;
531 END IF;
532
533 IF (l_debug = 'Y') THEN
534 gmd_debug.put_line('Completed '||l_api_name ||' at '
535 ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
536 END IF;
537
538 EXCEPTION
539 WHEN routing_creation_failure OR invalid_version THEN
540 IF (l_debug = 'Y') THEN
541 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
542 END IF;
543 fnd_msg_pub.count_and_get (
544 p_count => x_message_count
545 ,p_encoded => FND_API.g_false
546 ,p_data => x_message_list);
547 x_return_status := FND_API.G_RET_STS_ERROR;
548 ROLLBACK TO SAVEPOINT create_routing;
549 WHEN setup_failure THEN
550 ROLLBACK TO SAVEPOINT create_routing;
551 x_return_status := FND_API.G_RET_STS_ERROR;
552 fnd_msg_pub.count_and_get (
553 p_count => x_message_count
554 ,p_encoded => FND_API.g_false
555 ,p_data => x_message_list);
556 WHEN default_status_err THEN
557 ROLLBACK TO default_status_sp;
558 x_return_status := FND_API.G_RET_STS_ERROR;
559 fnd_msg_pub.count_and_get (
560 p_count => x_message_count
561 ,p_encoded => FND_API.g_false
562 ,p_data => x_message_list);
563 WHEN OTHERS THEN
564 ROLLBACK TO SAVEPOINT create_routing;
565 x_return_status := FND_API.g_ret_sts_unexp_error;
566 fnd_msg_pub.add_exc_msg (gmd_routings_PUB.m_pkg_name, l_api_name);
567 IF (l_debug = 'Y') THEN
568 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
569 END IF;
570 fnd_msg_pub.count_and_get (
571 p_count => x_message_count
572 ,p_encoded => FND_API.g_false
573 ,p_data => x_message_list);
574 END insert_routing;
575
576 /* =============================================================== */
577 /* Procedure: */
578 /* update_routing */
579 /* */
580 /* DESCRIPTION: */
581 /* */
582 /* API returns (x_return_code) = 'S' if the update into routing */
583 /* header (fm_rout_hdr or gmd_routings) table is successfully. */
584 /* */
585 /* History : */
586 /* Shyam 07/29/2002 Initial implementation */
587 /* RLNAGARA 25-Apr-2008 B6997624 Check if the fixed process loss uom is valid*/
588 /* =============================================================== */
589 PROCEDURE update_routing
590 ( p_api_version IN NUMBER := 1
591 , p_init_msg_list IN BOOLEAN := TRUE
592 , p_commit IN BOOLEAN := FALSE
593 , p_routing_id IN gmd_routings.routing_id%TYPE := NULL
594 , p_routing_no IN gmd_routings.routing_no%TYPE := NULL
595 , p_routing_vers IN gmd_routings.routing_vers%TYPE := NULL
596 , p_update_table IN update_tbl_type
597 , x_message_count OUT NOCOPY NUMBER
598 , x_message_list OUT NOCOPY VARCHAR2
599 , x_return_status OUT NOCOPY VARCHAR2
600 ) IS
601
602 /* Local variable section */
603 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ROUTING';
604 l_routing_id gmd_routings.routing_id%TYPE;
605 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
606 l_cur_status gmd_status.status_code%TYPE;
607 l_owner_id gmd_routings.owner_id%TYPE;
608 l_owner_orgn_id NUMBER;
609
610 /* Define record type that hold the routing data */
611 l_old_routing_rec gmd_routings%ROWTYPE;
612
613 /* Cursor section */
614 Cursor get_cur_status(vRouting_id gmd_routings.routing_id%TYPE ) IS
615 Select routing_status
616 From gmd_routings
617 Where routing_id = vRouting_id;
618
619 CURSOR get_old_routing_rec(vRouting_id gmd_routings.routing_id%TYPE) IS
620 Select *
621 From gmd_routings
622 Where Routing_id = vRouting_id;
623
624 /* Define Exceptions */
625 routing_update_failure EXCEPTION;
626 invalid_version EXCEPTION;
627 setup_failure EXCEPTION;
628
629 BEGIN
630 SAVEPOINT update_routing;
631
632 /* Set the return status to success initially */
633 x_return_status := FND_API.G_RET_STS_SUCCESS;
634
635 /* Initialize message list and count if needed */
636 IF p_init_msg_list THEN
637 fnd_msg_pub.initialize;
638 END IF;
639
640 IF (l_debug = 'Y') THEN
641 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
642 ||'Begining of Update API ');
643 END IF;
644
645 /* Intialize the setup fields */
646 IF NOT gmd_api_grp.setup_done THEN
647 gmd_api_grp.setup_done := gmd_api_grp.setup;
648 END IF;
649 IF NOT gmd_api_grp.setup_done THEN
650 RAISE setup_failure;
651 END IF;
652
653 /* Make sure we are call compatible */
654 IF NOT FND_API.compatible_api_call ( GMD_ROUTINGS_PUB.m_api_version
655 ,p_api_version
656 ,l_api_name
657 ,GMD_ROUTINGS_PUB.m_pkg_name) THEN
658 RAISE invalid_version;
659 END IF;
660
661 /* Validation 1. Check if this routing that is updated does exists
662 in the the database. The routing_id is the PK or Routing_no and version is
663 the unique key for this table (gmd_routings_b). */
664 IF p_routing_id IS NOT NULL THEN
665 l_routing_id := p_routing_id;
666 ELSE
667 GMDRTVAL_PUB.check_routing(pRouting_no => p_routing_no
668 ,pRouting_vers => p_routing_vers
669 ,xRouting_id => l_routing_id
670 ,xReturn_status => l_return_status);
671
672 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
673 /* it indicates that this routing does'ntexists */
674 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
675 FND_MSG_PUB.ADD;
676 RAISE routing_update_failure;
677 END IF;
678 END IF;
679
680 /* Check the routing id is not null */
681 IF l_routing_id IS NULL THEN
682 IF (l_debug = 'Y') THEN
683 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||'Routing id is required');
684 END IF;
685 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
686 FND_MESSAGE.SET_TOKEN ('MISSING', 'ROUTING_ID');
687 FND_MSG_PUB.ADD;
688 RAISE routing_update_failure;
689 END IF;
690
691 /* Routing Security fix */
692 /* Validation: Check if for given user this routing can be modified */
693 IF (l_debug = 'Y') THEN
694 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'
695 ||'Validation of user - owner orgn ocde = '||gmd_api_grp.user_id);
696 END IF;
697
698 IF NOT GMD_API_GRP.Check_orgn_access(Entity => 'ROUTING'
699 ,Entity_id => l_routing_id) THEN
700 RAISE routing_update_failure;
701 END IF;
702
703 /* Validation: Operation status level should be higher or equal
704 the routing level status. For instance, if the routing status
705 is "Approved for Laboratory Use", operations with a status cannot be "New"
706 are not allowed. Therefore when the routing status is updated check
707 all the associated operation status */
708 OPEN get_cur_status(l_routing_id);
709 FETCH get_cur_status INTO l_cur_status;
710 CLOSE get_cur_status;
711
712 FOR a IN 1 .. p_update_table.count LOOP
713 /* Validation: status dependencies */
714 IF UPPER(p_update_table(a).p_col_to_update) = 'STATUS' THEN
715 IF NOT GMD_STATUS_CODE.CHECK_DEPENDENT_STATUS
716 ( P_Entity_Type => 4,
717 P_Entity_id => l_routing_id,
718 P_Current_Status => l_cur_status,
719 P_To_Status => p_update_table(a).p_value) THEN
720 FND_MESSAGE.SET_NAME('GMD', 'GMD_STATUS_DEPEND_NOT_APPROVED');
721 FND_MSG_PUB.ADD;
722 RAISE routing_update_failure;
723 END IF;
724 /* Validation : Check if owner_orgn_id is valid */
725 /* Routing Security fix */
726 ELSIF UPPER(p_update_table(a).p_col_to_update) = 'OWNER_ORGANIZATION_ID' THEN
727 l_owner_orgn_id := p_update_table(a).p_value;
728 IF NOT GMD_API_GRP.OrgnAccessible(l_owner_orgn_id) THEN
729 RAISE routing_update_failure;
730 END IF;
731 /* Validation : Check if Routing class is valid */
732 ELSIF UPPER(p_update_table(a).p_col_to_update) = 'ROUTING_CLASS' THEN
733 IF p_update_table(a).p_value IS NOT NULL THEN
734 IF GMDRTVAL_PUB.check_routing_class(p_update_table(a).p_value) <> 0 THEN
735 FND_MESSAGE.SET_NAME('GMD', 'GMD_INVALID_ROUT_CLS');
736 FND_MSG_PUB.ADD;
737 RAISE routing_update_failure;
738 END IF;
739 END IF;
740 /* Validation : Check if Routing uom is valid */
741 ELSIF UPPER(p_update_table(a).p_col_to_update) = 'ROUTING_UOM' THEN
742 IF p_update_table(a).p_value IS NOT NULL THEN
743 IF (NOT(gmd_api_grp.validate_um(p_update_table(a).p_value))) THEN
744 FND_MESSAGE.SET_NAME('GMA', 'SY_INVALID_UM_CODE');
745 FND_MSG_PUB.ADD;
746 RAISE routing_update_failure;
747 END IF;
748 END IF;
749 /* Validation: delete_mark validation */
750 ELSIF UPPER(p_update_table(a).p_col_to_update) = 'DELETE_MARK' THEN
751 GMDRTVAL_PUB.check_delete_mark ( Pdelete_mark => p_update_table(a).p_value,
752 x_return_status => l_return_status);
753 IF l_return_status <> 'S' THEN /* it indicates that invalid value has been passed */
754 FND_MESSAGE.SET_NAME('GMA', 'SY_BADDELETEMARK');
755 FND_MSG_PUB.ADD;
756 RAISE routing_update_failure;
757 END IF;
758 /*RLNAGARA B6997624 Check if the fixed process loss uom is valid*/
759 ELSIF UPPER(p_update_table(a).p_col_to_update) = 'FIXED_PROCESS_LOSS_UOM' THEN
760 IF p_update_table(a).p_value IS NOT NULL THEN
761 IF (NOT(gmd_api_grp.validate_um(p_update_table(a).p_value))) THEN
762 FND_MESSAGE.SET_NAME('GMA', 'SY_INVALID_UM_CODE');
763 FND_MSG_PUB.ADD;
764 RAISE routing_update_failure;
765 END IF;
766 END IF;
767 ELSIF UPPER(p_update_table(a).p_col_to_update) = 'OWNER_ID' THEN
768 l_owner_id := gmd_api_grp.user_id; -- Bug 4603060
769 END IF;
770
771
772 /* Validation : Routing status is not On Hold nor Obsolete/Archived
773 and Routing is not logically deleted */
774 IF NOT GMD_COMMON_VAL.UPDATE_ALLOWED
775 (Entity => 'ROUTING',
776 Entity_id => l_routing_id,
777 Update_Column_Name => p_update_table(a).p_col_to_update ) THEN
778 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_NOT_VALID');
779 FND_MSG_PUB.ADD;
780 RAISE routing_update_failure;
781 END IF;
782 END LOOP;
783
784 /* Call the Routing Pvt API */
785 GMD_ROUTINGS_PVT.update_routing
786 ( p_routing_id => l_routing_id
787 , p_update_table => p_update_table
788 , x_message_count => x_message_count
789 , x_message_list => x_message_list
790 , x_return_status => x_return_status
791 );
792
793 /* Check if work was done */
794 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
795 RAISE routing_update_failure;
796 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
797
798 fnd_msg_pub.count_and_get (
799 p_count => x_message_count
800 ,p_encoded => FND_API.g_false
801 ,p_data => x_message_list);
802
803 IF x_message_count = 0 THEN
804 IF (l_debug = 'Y') THEN
805 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||'Routing was updated successfullly');
806 END IF;
807 END IF;
808
809 IF (P_commit) THEN
810 COMMIT;
811 END IF;
812
813 IF (l_debug = 'Y') THEN
814 gmd_debug.put_line('Completed '||l_api_name ||' at '
815 ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
816 END IF;
817
818 EXCEPTION
819 WHEN routing_update_failure OR invalid_version THEN
820 ROLLBACK TO SAVEPOINT update_routing;
821 x_return_status := FND_API.G_RET_STS_ERROR;
822 IF (l_debug = 'Y') THEN
823 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
824 END IF;
825 fnd_msg_pub.count_and_get (
826 p_count => x_message_count
827 ,p_encoded => FND_API.g_false
828 ,p_data => x_message_list);
829 WHEN setup_failure THEN
830 ROLLBACK TO SAVEPOINT update_routing;
831 x_return_status := FND_API.G_RET_STS_ERROR;
832 fnd_msg_pub.count_and_get (
833 p_count => x_message_count
834 ,p_encoded => FND_API.g_false
835 ,p_data => x_message_list);
836 WHEN OTHERS THEN
837 ROLLBACK TO SAVEPOINT update_routing;
838 x_return_status := FND_API.g_ret_sts_unexp_error;
839 fnd_msg_pub.add_exc_msg (gmd_routings_PUB.m_pkg_name, l_api_name);
840 IF (l_debug = 'Y') THEN
841 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
842 END IF;
843 fnd_msg_pub.count_and_get (
844 p_count => x_message_count
845 ,p_encoded => FND_API.g_false
846 ,p_data => x_message_list);
847 END update_routing;
848
849 /* =============================================================== */
850 /* Procedure: */
851 /* delete_routing */
852 /* */
853 /* DESCRIPTION: */
854 /* */
855 /* API returns (x_return_code) = 'S' if the delete into routing */
856 /* header (fm_rout_hdr or gmd_routings) table is successfully. */
857 /* */
858 /* History : */
859 /* Shyam 07/29/2002 Initial implementation */
860 /* kkillams 02/17/2004 Added new validation which checks whether */
861 /* Routing is associated with any recipe or */
862 /* not w.r.t. bug 3355204 */
863 /* =============================================================== */
864 PROCEDURE delete_routing
865 ( p_api_version IN NUMBER := 1
866 , p_init_msg_list IN BOOLEAN := TRUE
867 , p_commit IN BOOLEAN := FALSE
868 , p_routing_id IN gmd_routings.routing_id%TYPE := NULL
869 , p_routing_no IN gmd_routings.routing_no%TYPE := NULL
870 , p_routing_vers IN gmd_routings.routing_vers%TYPE := NULL
871 , x_message_count OUT NOCOPY NUMBER
872 , x_message_list OUT NOCOPY VARCHAR2
873 , x_return_status OUT NOCOPY VARCHAR2
874 ) IS
875
876 /*Cursor verifies whether routing associated with any recipe or not*/
877 CURSOR Cur_check_rout(cp_routing_id gmd_recipes.routing_id%TYPE)
878 IS SELECT count(1) FROM gmd_recipes
879 WHERE routing_id = cp_routing_id
880 AND delete_mark = 0;
881 /* Local variable section */
882 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROUTING';
883 l_routing_id gmd_routings.routing_id%TYPE;
884 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
885 l_count NUMBER;
886
887 /* Define a table type */
888 l_update_table GMD_ROUTINGS_PUB.UPDATE_TBL_TYPE;
889
890
891 /* Define Exceptions */
892 routing_delete_failure EXCEPTION;
893 invalid_version EXCEPTION;
894 setup_failure EXCEPTION;
895 routing_used EXCEPTION;
896 BEGIN
897 SAVEPOINT delete_routing;
898 IF (l_debug = 'Y') THEN
899 gmd_debug.log_initialize('Dertpub');
900 END IF;
901
902 /* Set the return status to success initially */
903 x_return_status := FND_API.G_RET_STS_SUCCESS;
904
905 /* Initialize message list and count if needed */
906 IF p_init_msg_list THEN
907 fnd_msg_pub.initialize;
908 END IF;
909
910 /* Intialize the setup fields */
911 IF NOT gmd_api_grp.setup_done THEN
912 gmd_api_grp.setup_done := gmd_api_grp.setup;
913 END IF;
914 IF NOT gmd_api_grp.setup_done THEN
915 RAISE setup_failure;
916 END IF;
917
918 /* Make sure we are call compatible */
919 IF NOT FND_API.compatible_api_call ( GMD_ROUTINGS_PUB.m_api_version
920 ,p_api_version
921 ,l_api_name
922 ,GMD_ROUTINGS_PUB.m_pkg_name) THEN
923 x_return_status := FND_API.G_RET_STS_ERROR;
924 RAISE invalid_version;
925 END IF;
926
927 /* Validation :. Check if this routing that is deleted does exists
928 in the the database. The routing_id is the PK or Routing_no and version is
929 the unique key for this table (gmd_routings_b). */
930 IF p_routing_id IS NOT NULL THEN
931 l_routing_id := p_routing_id;
932 ELSE
933 GMDRTVAL_PUB.check_routing(pRouting_no => p_routing_no
934 ,pRouting_vers => p_routing_vers
935 ,xRouting_id => l_routing_id
936 ,xReturn_status => l_return_status);
937
938 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
939 /* it indicates that this routing does'ntexists */
940 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
941 FND_MSG_PUB.ADD;
942 RAISE routing_delete_failure;
943 END IF;
944 END IF;
945
946 /*Validation: Verifies whether routing associated with any recipe or not.
947 If yes, then system raises error and terminates remaining processes w.r.t. bug 3355204*/
948 OPEN Cur_check_rout(p_routing_id);
949 FETCH Cur_check_rout INTO l_count;
950 IF (l_count <> 0) THEN
951 x_return_status := FND_API.G_RET_STS_ERROR;
952 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_USED');
953 FND_MSG_PUB.ADD;
954 RAISE routing_used;
955 END IF;
956
957 l_update_table(1).P_COL_TO_UPDATE := 'DELETE_MARK';
958 l_update_table(1).P_VALUE := '1';
959
960 GMD_ROUTINGS_PUB.update_routing
961 ( p_routing_id => l_routing_id
962 , p_update_table => l_update_table
963 , p_commit => FALSE
964 , x_message_count => x_message_count
965 , x_message_list => x_message_list
966 , x_return_status => x_return_status
967 );
968
969 /* Check if work was done */
970 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
971 RAISE routing_delete_failure;
972 END IF; /* IF x_return_status <> FND_API.G_RET_STS_SUCCESS */
973
974 fnd_msg_pub.count_and_get (
975 p_count => x_message_count
976 ,p_encoded => FND_API.g_false
977 ,p_data => x_message_list);
978
979 IF x_message_count = 0 THEN
980 IF (l_debug = 'Y') THEN
981 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||'Routing was created successfullly');
982 END IF;
983 END IF;
984
985 IF (P_commit) THEN
986 COMMIT;
987 END IF;
988
989 IF (l_debug = 'Y') THEN
990 gmd_debug.put_line('Completed '||l_api_name ||' at '
991 ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
992 END IF;
993
994 EXCEPTION
995 WHEN routing_delete_failure OR invalid_version or routing_used THEN
996 ROLLBACK TO SAVEPOINT delete_routing;
997 x_return_status := FND_API.G_RET_STS_ERROR;
998 IF (l_debug = 'Y') THEN
999 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
1000 END IF;
1001 fnd_msg_pub.count_and_get (
1002 p_count => x_message_count
1003 ,p_encoded => FND_API.g_false
1004 ,p_data => x_message_list);
1005 WHEN setup_failure THEN
1006 ROLLBACK TO SAVEPOINT delete_routing;
1007 x_return_status := FND_API.G_RET_STS_ERROR;
1008 fnd_msg_pub.count_and_get (
1009 p_count => x_message_count
1010 ,p_encoded => FND_API.g_false
1011 ,p_data => x_message_list);
1012 WHEN OTHERS THEN
1013 ROLLBACK TO SAVEPOINT delete_routing;
1014 x_return_status := FND_API.g_ret_sts_unexp_error;
1015 fnd_msg_pub.add_exc_msg (gmd_routings_PUB.m_pkg_name, l_api_name);
1016 IF (l_debug = 'Y') THEN
1017 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
1018 END IF;
1019 fnd_msg_pub.count_and_get (
1020 p_count => x_message_count
1021 ,p_encoded => FND_API.g_false
1022 ,p_data => x_message_list);
1023 END delete_routing;
1024
1025 /* =============================================================== */
1026 /* Procedure: */
1027 /* undelete_routing */
1028 /* */
1029 /* DESCRIPTION: */
1030 /* */
1031 /* API returns (x_return_code) = 'S' if the delete into routing */
1032 /* header (fm_rout_hdr or gmd_routings) table is successfully. */
1033 /* */
1034 /* History : */
1035 /* Shyam 07/29/2002 Initial implementation */
1036 /* =============================================================== */
1037 PROCEDURE undelete_routing
1038 ( p_api_version IN NUMBER := 1
1039 , p_init_msg_list IN BOOLEAN := TRUE
1040 , p_commit IN BOOLEAN := FALSE
1041 , p_routing_id IN gmd_routings.routing_id%TYPE := NULL
1042 , p_routing_no IN gmd_routings.routing_no%TYPE := NULL
1043 , p_routing_vers IN gmd_routings.routing_vers%TYPE := NULL
1044 , x_message_count OUT NOCOPY NUMBER
1045 , x_message_list OUT NOCOPY VARCHAR2
1046 , x_return_status OUT NOCOPY VARCHAR2
1047 ) IS
1048
1049 /* Local variable section */
1050 l_api_name CONSTANT VARCHAR2(30) := 'UNDELETE_ROUTING';
1051 l_routing_id gmd_routings.routing_id%TYPE;
1052 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1053
1054 /* Define a table type */
1055 l_update_table GMD_ROUTINGS_PUB.UPDATE_TBL_TYPE;
1056
1057 /* Define Exceptions */
1058 routing_undelete_failure EXCEPTION;
1059 invalid_version EXCEPTION;
1060 setup_failure EXCEPTION;
1061 BEGIN
1062 SAVEPOINT undelete_routing;
1063 IF (l_debug = 'Y') THEN
1064 gmd_debug.log_initialize('Undrtpub');
1065 END IF;
1066
1067 /* Set the return status to success initially */
1068 x_return_status := FND_API.G_RET_STS_SUCCESS;
1069
1070 /* Initialize message list and count if needed */
1071 IF p_init_msg_list THEN
1072 fnd_msg_pub.initialize;
1073 END IF;
1074
1075 /* Intialize the setup fields */
1076 IF NOT gmd_api_grp.setup_done THEN
1077 gmd_api_grp.setup_done := gmd_api_grp.setup;
1078 END IF;
1079 IF NOT gmd_api_grp.setup_done THEN
1080 RAISE setup_failure;
1081 END IF;
1082
1083 /* Make sure we are call compatible */
1084 IF NOT FND_API.compatible_api_call ( GMD_ROUTINGS_PUB.m_api_version
1085 ,p_api_version
1086 ,l_api_name
1087 ,GMD_ROUTINGS_PUB.m_pkg_name) THEN
1088 x_return_status := FND_API.G_RET_STS_ERROR;
1089 RAISE invalid_version;
1090 END IF;
1091
1092 /* Validation :. Check if this routing that is deleted does exists
1093 in the the database. The routing_id is the PK or Routing_no and version is
1094 the unique key for this table (gmd_routings_b). */
1095 IF p_routing_id IS NOT NULL THEN
1096 l_routing_id := p_routing_id;
1097 ELSE
1098 GMDRTVAL_PUB.check_routing(pRouting_no => p_routing_no
1099 ,pRouting_vers => p_routing_vers
1100 ,xRouting_id => l_routing_id
1101 ,xReturn_status => l_return_status);
1102
1103 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1104 /* it indicates that this routing does'ntexists */
1105 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_INVALID');
1106 FND_MSG_PUB.ADD;
1107 RAISE routing_undelete_failure;
1108 END IF;
1109 END IF;
1110
1111 UPDATE gmd_routings_b
1112 SET delete_mark = 0
1113 WHERE routing_id = l_routing_id;
1114
1115 fnd_msg_pub.count_and_get (
1116 p_count => x_message_count
1117 ,p_encoded => FND_API.g_false
1118 ,p_data => x_message_list);
1119
1120 IF x_message_count = 0 THEN
1121 IF (l_debug = 'Y') THEN
1122 gmd_debug.put_line(m_pkg_name||'.'||l_api_name||':'||'Routing was undeleted successfullly');
1123 END IF;
1124 END IF;
1125
1126 IF (P_commit) THEN
1127 COMMIT;
1128 END IF;
1129
1130 IF (l_debug = 'Y') THEN
1131 gmd_debug.put_line('Completed '||l_api_name ||' at '
1132 ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
1133 END IF;
1134
1135 EXCEPTION
1136 WHEN routing_undelete_failure OR invalid_version THEN
1137 ROLLBACK TO SAVEPOINT undelete_routing;
1138 x_return_status := FND_API.G_RET_STS_ERROR;
1139 IF (l_debug = 'Y') THEN
1140 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
1141 END IF;
1142 fnd_msg_pub.count_and_get (
1143 p_count => x_message_count
1144 ,p_encoded => FND_API.g_false
1145 ,p_data => x_message_list);
1146 WHEN setup_failure THEN
1147 ROLLBACK TO SAVEPOINT undelete_routing;
1148 x_return_status := FND_API.G_RET_STS_ERROR;
1149 fnd_msg_pub.count_and_get (
1150 p_count => x_message_count
1151 ,p_encoded => FND_API.g_false
1152 ,p_data => x_message_list);
1153 WHEN OTHERS THEN
1154 ROLLBACK TO SAVEPOINT undelete_routing;
1155 x_return_status := FND_API.g_ret_sts_unexp_error;
1156 fnd_msg_pub.add_exc_msg (gmd_routings_PUB.m_pkg_name, l_api_name);
1157 IF (l_debug = 'Y') THEN
1158 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
1159 END IF;
1160 fnd_msg_pub.count_and_get (
1161 p_count => x_message_count
1162 ,p_encoded => FND_API.g_false
1163 ,p_data => x_message_list);
1164 END undelete_routing;
1165
1166
1167 END GMD_ROUTINGS_PUB;
[Home] [Help]