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