[Home] [Help]
PACKAGE BODY: APPS.GMD_ROUTING_DESIGNER_PKG
Source
1 PACKAGE BODY GMD_ROUTING_DESIGNER_PKG AS
2 /* $Header: GMDRSDDB.pls 120.9 2006/08/17 12:42:57 kmotupal noship $ */
3 /*============================================================================
4 | Copyright (c) 2001 Oracle Corporation
5 | Redwood Shores, California, USA
6 | All rights reserved
7 =============================================================================
8 | FILENAME
9 | GMDRSDDB.pls
10 |
11 | DESCRIPTION
12 | Package body containing the procedures used by the Routing Designer
13 | to create/update/delete routing step dependencies.
14 |
15 |
16 | NOTES
17 |
18 | HISTORY
19 | 12-APR-2001 Eddie Oumerretane Created.
20 | 24-APR-2002 Eddie Oumerretane Calculate transfer quantity prior to
21 | updating the database.
22 | 14-JUN-2002 Eddie Oumerretane Added a procedure to update routing
23 | header.
24 | 01-JUL-2002 Eddie Oumerretane. Implemented various enhancements for
25 | the Rapid Recipe Project.
26 | 27-APR-2004 S.Sriram Bug# 3408799
27 | Added SET_DEFAULT_STATUS procedure for Default Status Build
28 | 23-SEP-2004 S.Sriram Routing Security build
29 | Added CHECK_ROUT_ORGN_ACCESS procedure for Rout. Security Build
30 | 29-Dec-2005 TDaniel Bug# 4603035
31 | Added code for contiguous_ind and enforce_step_dep.
32 =============================================================================
33 */
34
35
36 /* Api start of comments
37 +============================================================================
38 | PROCEDURE NAME
39 | Delete_Step_Dependency
40 |
41 | DESCRIPTION
42 | Delete a specific step depdendency.
43 |
44 | INPUT PARAMETERS
45 | p_routing_id NUMBER
46 | p_dep_routingstep_no NUMBER
47 | p_routingstep_no NUMBER
48 | p_last_update_date DATE
49 |
50 | OUTPUT PARAMETERS
51 | x_return_code VARCHAR2(1)
52 | x_error_msg VARCHAR2(100)
53 |
54 | HISTORY
55 | 12-APR-2001 Eddie Oumerretane Created.
56 |
57 +=============================================================================
58 Api end of comments
59 */
60
61 PROCEDURE Delete_Step_Dependency ( p_routing_id IN NUMBER,
62 p_dep_routingstep_no IN NUMBER,
63 p_routingstep_no IN NUMBER,
64 p_last_update_date IN DATE,
65 x_return_code OUT NOCOPY VARCHAR2,
66 x_error_msg OUT NOCOPY VARCHAR2) IS
67
68 l_return_status VARCHAR2(2);
69 l_message_count NUMBER;
70 l_message_list VARCHAR2(2000);
71 l_message VARCHAR2(1000);
72 l_dummy NUMBER;
73 DELETE_STEP_DEP_EXCEPTION EXCEPTION;
74 RECORD_CHANGED_EXCEPTION EXCEPTION;
75
76 CURSOR Cur_Get_Step_Dep IS
77 SELECT *
78 FROM fm_rout_dep
79 WHERE
80 routingstep_no = p_routingstep_no AND
81 dep_routingstep_no = p_dep_routingstep_no AND
82 routing_id = p_routing_id AND
83 last_update_date = p_last_update_date;
84
85 l_rec Cur_Get_Step_Dep%ROWTYPE;
86
87 BEGIN
88
89 x_return_code := 'S';
90 x_error_msg := '';
91
92
93 OPEN Cur_Get_STep_Dep;
94 FETCH Cur_Get_Step_Dep INTO l_rec;
95
96 IF Cur_Get_Step_Dep%NOTFOUND THEN
97 CLOSE Cur_Get_Step_Dep;
98 RAISE RECORD_CHANGED_EXCEPTION;
99 END IF;
100
101 CLOSE Cur_Get_Step_Dep;
102
103 GMD_ROUTING_STEPS_PUB.delete_step_dependencies
104 ( p_api_version => 1
105 , p_init_msg_list => TRUE
106 , p_commit => FALSE
107 , p_routingstep_no => p_routingstep_no
108 , p_dep_routingstep_no => p_dep_routingstep_no
109 , p_routing_id => p_routing_id
110 , p_routing_no => NULL
111 , p_routing_vers => NULL
112 , x_message_count => l_message_count
113 , x_message_list => l_message_list
114 , x_return_status => l_return_status);
115
116 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
117 RAISE DELETE_STEP_DEP_EXCEPTION;
118 END IF;
119
120 EXCEPTION
121 WHEN DELETE_STEP_DEP_EXCEPTION THEN
122 FND_MSG_PUB.GET( p_msg_index => 1,
123 p_data => l_message,
124 p_encoded => 'F',
125 p_msg_index_out => l_dummy);
126
127 x_return_code := 'F';
128 x_error_msg := l_message;
129
130 WHEN RECORD_CHANGED_EXCEPTION THEN
131 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
132 x_return_code := 'F';
133 x_error_msg := FND_MESSAGE.GET;
134 WHEN OTHERS THEN
135 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
136 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
137 x_return_code := 'F';
138 x_error_msg := FND_MESSAGE.GET;
139
140 END Delete_Step_Dependency;
141
142
143 /* Api start of comments
144 +============================================================================
145 | FUNCTION NAME
146 | Calculate_Transfer_Quantity
147 |
148 | DESCRIPTION
149 | Calculate transfer qty given the transfer pct for the given routing step
150 |
151 | INPUT PARAMETERS
152 | p_routing_id NUMBER
153 | p_routingstep_no NUMBER
154 | p_transfer_pct NUMBER
155 |
156 | RETUEN VALUE
157 | x_transfer_qty NUMBER
158 |
159 | HISTORY
160 | 24-APR-2002 Eddie Oumerretane Created.
161 |
162 +=============================================================================
163 Api end of comments
164 */
165
166 FUNCTION Calculate_Transfer_Qty (p_routing_id NUMBER,
167 p_routingstep_no NUMBER,
168 p_transfer_pct NUMBER) RETURN NUMBER IS
169
170 l_transfer_qty NUMBER;
171
172 BEGIN
173
174 SELECT
175 nvl(step_qty, 0) * p_transfer_pct / 100 INTO l_transfer_qty
176 FROM
177 fm_rout_dtl
178 WHERE
179 routing_id = p_routing_id AND
180 routingstep_no = p_routingstep_no;
181
182
183 IF SQL%FOUND THEN
184 RETURN l_transfer_qty;
185 ELSE
186 RETURN 0;
187 END IF;
188
189 END Calculate_Transfer_Qty;
190
191 /* Api start of comments
192 +============================================================================
193 | PROCEDURE NAME
194 | Create_Step_Dependency
195 |
196 | DESCRIPTION
197 | Create an entry in FM_ROUT_DEP representing a dependency between two
198 | routing steps.
199 |
200 | INPUT PARAMETERS
201 | p_dep_routingstep_no NUMBER
202 | p_routing_id NUMBER
203 | p_dep_type NUMBER
204 | p_rework_code VARCHAR2
205 | p_standard_delay NUMBER
206 | p_minimum_delay NUMBER
207 | p_max_delay NUMBER
208 | p_transfer_qty NUMBER
209 | p_transfer_um VARCHAR2
210 | p_user_id NUMBER
211 | p_transfer_pct NUMBER
212 |
213 | OUTPUT PARAMETERS
214 | x_return_code VARCHAR2(1)
215 | x_error_msg VARCHAR2(100)
216 |
217 | HISTORY
218 | 12-APR-2001 Eddie Oumerretane Created.
219 |
220 +=============================================================================
221 Api end of comments
222 */
223
224 PROCEDURE Create_Step_Dependency ( p_routingstep_no IN NUMBER,
225 p_dep_routingstep_no IN NUMBER,
226 p_routing_id IN NUMBER,
227 p_dep_type IN NUMBER,
228 p_rework_code IN VARCHAR2,
229 p_standard_delay IN NUMBER,
230 p_minimum_delay IN NUMBER,
231 p_max_delay IN NUMBER,
232 p_transfer_qty IN NUMBER,
233 p_item_um IN VARCHAR2,
234 p_user_id IN NUMBER,
235 p_transfer_pct IN NUMBER,
236 p_last_update_date IN DATE,
237 x_return_code OUT NOCOPY VARCHAR2,
238 x_error_msg OUT NOCOPY VARCHAR2) IS
239
240 l_transfer_qty NUMBER;
241 l_return_status VARCHAR2(2);
242 l_message_count NUMBER;
243 l_message_list VARCHAR2(2000);
244 l_message VARCHAR2(1000);
245 l_dummy NUMBER;
246 INSERT_STEP_DEP_EXCEPTION EXCEPTION;
247 l_routings_step_dep_tbl GMD_ROUTINGS_PUB.gmd_routings_step_dep_tab;
248
249 BEGIN
250
251 x_return_code := 'S';
252 x_error_msg := '';
253
254 l_transfer_qty := Calculate_Transfer_Qty (p_routing_id,
255 p_dep_routingstep_no,
256 p_transfer_pct);
257
258 l_routings_step_dep_tbl(1).ROUTINGSTEP_NO := p_routingstep_no;
259 l_routings_step_dep_tbl(1).DEP_ROUTINGSTEP_NO := p_dep_routingstep_no;
260 l_routings_step_dep_tbl(1).ROUTING_ID := p_routing_id;
261 l_routings_step_dep_tbl(1).DEP_TYPE := p_dep_type;
262 l_routings_step_dep_tbl(1).REWORK_CODE := p_rework_code;
263 l_routings_step_dep_tbl(1).STANDARD_DELAY := p_standard_delay;
264 l_routings_step_dep_tbl(1).MINIMUM_DELAY := p_minimum_delay;
265 l_routings_step_dep_tbl(1).MAX_DELAY := p_max_delay;
266 l_routings_step_dep_tbl(1).TRANSFER_QTY := l_transfer_qty;
267 l_routings_step_dep_tbl(1).ROUTINGSTEP_NO_UOM := p_item_um;
268 l_routings_step_dep_tbl(1).TEXT_CODE := NULL;
269 l_routings_step_dep_tbl(1).LAST_UPDATED_BY := p_user_id;
270 l_routings_step_dep_tbl(1).CREATED_BY := p_user_id;
271 l_routings_step_dep_tbl(1).LAST_UPDATE_DATE := p_last_update_date;
272 l_routings_step_dep_tbl(1).CREATION_DATE := p_last_update_date;
273 l_routings_step_dep_tbl(1).LAST_UPDATE_LOGIN := p_user_id;
274 l_routings_step_dep_tbl(1).TRANSFER_PCT := p_transfer_pct;
275
276 GMD_ROUTING_STEPS_PUB.insert_step_dependencies
277 (
278 p_api_version => 1
279 , p_init_msg_list => TRUE
280 , p_commit => FALSE
281 , p_routing_id => p_routing_id
282 , p_routing_no => NULL
283 , p_routing_vers => NULL
284 , p_routingstep_id => NULL
285 , p_routingstep_no => p_routingstep_no
286 , p_routings_step_dep_tbl => l_routings_step_dep_tbl
287 , x_message_count => l_message_count
288 , x_message_list => l_message_list
289 , x_return_status => l_return_status);
290
291 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
292 RAISE INSERT_STEP_DEP_EXCEPTION;
293 END IF;
294
295 EXCEPTION
296 WHEN INSERT_STEP_DEP_EXCEPTION THEN
297 FND_MSG_PUB.GET( p_msg_index => 1,
298 p_data => l_message,
299 p_encoded => 'F',
300 p_msg_index_out => l_dummy);
301
302 x_return_code := 'F';
303 x_error_msg := l_message;
304 WHEN OTHERS THEN
305 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
306 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
307 x_return_code := 'F';
308 x_error_msg := FND_MESSAGE.GET;
309
310 END Create_Step_Dependency;
311
312
313 /* Api start of comments
314 +============================================================================
315 | PROCEDURE NAME
316 | Update_Step_Dependency
317 |
318 | DESCRIPTION
319 | Update an entry in FM_ROUT_DEP representing a dependency between two
320 | routing steps.
321 |
322 | INPUT PARAMETERS
323 | p_routing_id NUMBER
324 |
325 | OUTPUT PARAMETERS
326 | x_return_code VARCHAR2(1)
327 | x_error_msg VARCHAR2(100)
328 |
329 | HISTORY
330 | 12-APR-2001 Eddie Oumerretane Created.
331 |
332 +=============================================================================
333 Api end of comments
334 */
335
336 PROCEDURE Update_Step_Dependency ( p_routingstep_no IN NUMBER,
337 p_dep_routingstep_no IN NUMBER,
338 p_routing_id IN NUMBER,
339 p_dep_type IN NUMBER,
340 p_rework_code IN VARCHAR2,
341 p_standard_delay IN NUMBER,
342 p_minimum_delay IN NUMBER,
343 p_max_delay IN NUMBER,
344 p_transfer_qty IN NUMBER,
345 p_user_id IN NUMBER,
346 p_transfer_pct IN NUMBER,
347 p_last_update_date IN DATE,
348 p_last_update_date_origin IN DATE,
349 x_return_code OUT NOCOPY VARCHAR2,
350 x_error_msg OUT NOCOPY VARCHAR2) IS
351
352
353 l_transfer_qty NUMBER;
354 l_return_status VARCHAR2(2);
355 l_message_count NUMBER;
356 l_message_list VARCHAR2(2000);
357 l_message VARCHAR2(1000);
358 l_dummy NUMBER;
359 UPDATE_STEP_DEP_EXCEPTION EXCEPTION;
360 RECORD_CHANGED_EXCEPTION EXCEPTION;
361 l_update_table GMD_ROUTINGS_PUB.update_tbl_type;
362
363 CURSOR Cur_Get_Step_Dep IS
364 SELECT *
365 FROM fm_rout_dep
366 WHERE
367 routingstep_no = p_routingstep_no AND
368 dep_routingstep_no = p_dep_routingstep_no AND
369 routing_id = p_routing_id AND
370 last_update_date = p_last_update_date_origin;
371
372 l_rec Cur_Get_Step_Dep%ROWTYPE;
373
374 BEGIN
375
376 x_return_code := 'S';
377 x_error_msg := '';
378
379 OPEN Cur_Get_STep_Dep;
380 FETCH Cur_Get_Step_Dep INTO l_rec;
381
382 IF Cur_Get_Step_Dep%NOTFOUND THEN
383 CLOSE Cur_Get_Step_Dep;
384 RAISE RECORD_CHANGED_EXCEPTION;
385 END IF;
386
387 CLOSE Cur_Get_Step_Dep;
388
389 l_transfer_qty := Calculate_Transfer_Qty (p_routing_id,
390 p_dep_routingstep_no,
391 p_transfer_pct);
392
393 l_update_table(1).p_col_to_update := 'DEP_TYPE';
394 l_update_table(1).p_value := p_dep_type;
395 l_update_table(2).p_col_to_update := 'REWORK_CODE';
396 l_update_table(2).p_value := p_rework_code;
397 l_update_table(3).p_col_to_update := 'STANDARD_DELAY';
398 l_update_table(3).p_value := p_standard_delay;
399 l_update_table(4).p_col_to_update := 'MINIMUM_DELAY';
400 l_update_table(4).p_value := p_minimum_delay;
401 l_update_table(5).p_col_to_update := 'MAX_DELAY';
402 l_update_table(5).p_value := p_max_delay;
403 l_update_table(6).p_col_to_update := 'TRANSFER_QTY';
404 l_update_table(6).p_value := l_transfer_qty;
405 l_update_table(7).p_col_to_update := 'LAST_UPDATED_BY';
406 l_update_table(7).p_value := p_user_id;
407 l_update_table(8).p_col_to_update := 'CREATED_BY';
408 l_update_table(8).p_value := p_user_id;
409 l_update_table(9).p_col_to_update := 'LAST_UPDATE_DATE';
410 l_update_table(9).p_value := fnd_date.date_to_canonical(p_last_update_date);
411 l_update_table(10).p_col_to_update := 'LAST_UPDATE_LOGIN';
412 l_update_table(10).p_value := p_user_id;
413 l_update_table(11).p_col_to_update := 'TRANSFER_PCT';
414 l_update_table(11).p_value := p_transfer_pct;
415
416 GMD_ROUTING_STEPS_PUB.update_step_dependencies
417 ( p_api_version => 1
418 , p_init_msg_list => TRUE
419 , p_commit => FALSE
420 , p_routingstep_no => p_routingstep_no
421 , p_routingstep_id => NULL
422 , p_dep_routingstep_no => p_dep_routingstep_no
423 , p_routing_id => p_routing_id
424 , p_routing_no => NULL
425 , p_routing_vers => NULL
426 , p_update_table => l_update_table
427 , x_message_count => l_message_count
428 , x_message_list => l_message_list
429 , x_return_status => l_return_status);
430
431
432 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
433 RAISE UPDATE_STEP_DEP_EXCEPTION;
434 END IF;
435
436 EXCEPTION
437 WHEN UPDATE_STEP_DEP_EXCEPTION THEN
438 FND_MSG_PUB.GET( p_msg_index => 1,
439 p_data => l_message,
440 p_encoded => 'F',
441 p_msg_index_out => l_dummy);
442
443 x_return_code := 'F';
444 x_error_msg := l_message;
445 WHEN RECORD_CHANGED_EXCEPTION THEN
446 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
447 x_return_code := 'F';
448 x_error_msg := FND_MESSAGE.GET;
449 WHEN OTHERS THEN
450 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
451 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
452 x_return_code := 'F';
453 x_error_msg := FND_MESSAGE.GET;
454
455 END Update_Step_Dependency;
456
457 /* Api start of comments
458 +============================================================================
459 | PROCEDURE NAME
460 | Get_Routing_Mode
461 |
462 | DESCRIPTION
463 | Determine whether this routing is in update or query mode
464 |
465 | INPUT PARAMETERS
466 | p_routing_id NUMBER
467 |
468 | OUTPUT PARAMETERS
469 | x_routing_mode VARCHAR2
470 | x_return_code VARCHAR2
471 | x_error_msg VARCHAR2
472 |
473 | HISTORY
474 | 15-OCT-2001 Eddie Oumerretane Created.
475 |
476 +=============================================================================
477 Api end of comments
478 */
479 PROCEDURE Get_Routing_Mode ( p_routing_id IN NUMBER,
480 x_routing_mode OUT NOCOPY VARCHAR2,
481 x_return_code OUT NOCOPY VARCHAR2,
482 x_error_msg OUT NOCOPY VARCHAR2) IS
483
484 l_return_code VARCHAR2(1);
485 l_status VARCHAR2(30);
486
487 BEGIN
488
489 x_return_code := 'S';
490 x_error_msg := '';
491
492 IF GMD_COMMON_VAL.Update_Allowed(entity => 'ROUTING',
493 entity_id => p_routing_id) THEN
494 x_routing_mode := 'U';
495 ELSE
496 x_routing_mode := 'Q';
497
498 END IF;
499
500 END Get_Routing_Mode;
501
502 /* Api start of comments
503 +============================================================================
504 | PROCEDURE NAME
505 | Is_Routing_Used_In_Recipes
506 |
507 | DESCRIPTION
508 | Determine whether the routing is used in one or more recipes.
509 |
510 | INPUT PARAMETERS
511 | p_routing_id NUMBER
512 |
513 | OUTPUT PARAMETERS
514 | x_used_in_recipes VARCHAR2(1)
515 | x_return_code VARCHAR2(1)
516 | x_error_msg VARCHAR2(100)
517 |
518 | HISTORY
519 | 22-NOV-2001 Eddie Oumerretane Created.
520 |
521 +=============================================================================
522 Api end of comments
523 */
524 PROCEDURE Is_Routing_Used_In_Recipes (p_routing_id IN NUMBER,
525 x_used_in_recipes OUT NOCOPY VARCHAR2,
526 x_return_code OUT NOCOPY VARCHAR2,
527 x_error_msg OUT NOCOPY VARCHAR2) IS
528
529 BEGIN
530
531 x_return_code := 'S';
532 x_error_msg := '';
533
534 x_used_in_recipes := 'N';
535
536 -- Return TRUE if this routing is used by one or more recipes
537 IF NOT GMD_STATUS_CODE.Check_Parent_Status('ROUTING', p_routing_id) THEN
538 x_used_in_recipes := 'Y';
539 END IF;
540
541 EXCEPTION
542 WHEN OTHERS THEN
543 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
544 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
545 x_return_code := 'F';
546 x_error_msg := FND_MESSAGE.GET;
547
548 END Is_Routing_Used_In_Recipes;
549
550 /* Api start of comments
551 +============================================================================
552 | PROCEDURE NAME
553 | Update_Routing_Header
554 |
555 | DESCRIPTION
556 | Update routing header
557 |
558 | INPUT PARAMETERS
559 | p_routing_id IN NUMBER,
560 | p_routing_no IN VARCHAR2
561 | p_routing_vers IN NUMBER,
562 | p_routing_desc IN VARCHAR2
563 | p_routing_class IN VARCHAR2
564 | p_effective_start_date IN DATE
565 | p_effective_end_date IN DATE
566 | p_routing_qty IN NUMBER
567 | p_routing_uom IN VARCHAR2
568 | p_process_loss IN NUMBER
569 | p_owner_id IN NUMBER
570 | p_owner_orgn_id IN NUMBER
571 | p_enforce_step_dep IN NUMBER
572 | p_last_update_date IN DATE
573 | p_user_id IN NUMBER
574 | p_last_update_date_orig IN DATE
575 | p_update_release_type IN NUMBER
576 |
577 | OUTPUT PARAMETERS
578 | x_return_code VARCHAR2(1)
579 | x_error_msg VARCHAR2(100)
580 |
581 | HISTORY
582 | 13-JUN-2002 Eddie Oumerretane Created.
583 | 20-APR-2004 kkillams Bug 3545196,Replaced TO_CHAR function with
584 | FND_DATE.DATE_TO_CANONICAL function while converting
585 | Routing Effective Start and End dates.
586 +=============================================================================
587 Api end of comments
588 */
589 PROCEDURE Update_Routing_Header ( p_routing_id IN NUMBER,
590 p_routing_no IN VARCHAR2,
591 p_routing_vers IN NUMBER,
592 p_routing_desc IN VARCHAR2,
593 p_routing_class IN VARCHAR2,
594 p_effective_start_date IN DATE,
595 p_effective_end_date IN DATE,
596 p_routing_qty IN NUMBER,
597 p_routing_uom IN VARCHAR2,
598 p_process_loss IN NUMBER,
599 p_owner_id IN NUMBER,
600 p_owner_orgn_id IN NUMBER,
601 p_enforce_step_dep IN NUMBER,
602 p_contiguous_ind IN NUMBER,
603 p_last_update_date IN DATE,
604 p_user_id IN NUMBER,
605 p_last_update_date_orig IN DATE,
606 p_update_release_type IN NUMBER,
607 x_return_code OUT NOCOPY VARCHAR2,
608 x_error_msg OUT NOCOPY VARCHAR2) IS
609 CURSOR Cur_get_routing IS
610 SELECT *
611 FROM gmd_routings
612 WHERE routing_id = p_routing_id AND
613 last_update_date = p_last_update_date_orig;
614
615 UPDATE_ROUTING_EXCEPTION EXCEPTION;
616 RECORD_CHANGED_EXCEPTION EXCEPTION;
617 l_rec Cur_get_routing%ROWTYPE;
618 l_enforce_step_dep NUMBER;
619 l_update_table GMD_ROUTINGS_PUB.update_tbl_type;
620 l_return_status VARCHAR2(2);
621 l_msg_count NUMBER;
622 l_msg_data VARCHAR2(2000);
623 l_message_count NUMBER;
624 l_message_list VARCHAR2(2000);
625 l_message VARCHAR2(1000);
626 l_dummy NUMBER;
627
628 BEGIN
629
630 x_error_msg := '';
631 x_return_code := FND_API.G_RET_STS_SUCCESS;
632
633
634 OPEN Cur_get_routing;
635 FETCH Cur_get_routing INTO l_rec;
636
637 IF Cur_get_routing%NOTFOUND THEN
638 CLOSE Cur_get_routing;
639 RAISE RECORD_CHANGED_EXCEPTION;
640 END IF;
641
642 CLOSE Cur_get_routing;
643
644
645 l_update_table(1).p_col_to_update := 'routing_id';
646 l_update_table(1).p_value := p_routing_id;
647
648 l_update_table(2).p_col_to_update := 'owner_organization_id';
649 l_update_table(2).p_value := p_owner_orgn_id;
650
651 l_update_table(3).p_col_to_update := 'routing_no';
652 l_update_table(3).p_value := p_routing_no;
653
654 l_update_table(4).p_col_to_update := 'routing_vers';
655 l_update_table(4).p_value := p_routing_vers;
656
657 l_update_table(5).p_col_to_update := 'routing_class';
658 l_update_table(5).p_value := p_routing_class;
659
660 l_update_table(6).p_col_to_update := 'routing_qty';
661 l_update_table(6).p_value := p_routing_qty;
662
663 l_update_table(7).p_col_to_update := 'routing_uom';
664 l_update_table(7).p_value := p_routing_uom;
665
666 l_update_table(8).p_col_to_update := 'enforce_step_dependency';
667 l_update_table(8).p_value := p_enforce_step_dep;
668
669 l_update_table(9).p_col_to_update := 'CONTIGUOUS_IND';
670 l_update_table(9).p_value := NVL(p_contiguous_ind, 0);
671
672 l_update_table(10).p_col_to_update := 'effective_start_date';
673 l_update_table(10).p_value := FND_DATE.DATE_TO_CANONICAL(p_effective_start_date); -- Bug 3545196
674
675 l_update_table(11).p_col_to_update := 'effective_end_date';
676 l_update_table(11).p_value := FND_DATE.DATE_TO_CANONICAL(p_effective_end_date); --Bug 3545196
677
678 l_update_table(12).p_col_to_update := 'owner_id';
679 l_update_table(12).p_value := p_owner_id;
680
681 l_update_table(13).p_col_to_update := 'process_loss';
682 l_update_table(13).p_value := p_process_loss;
683
684 l_update_table(14).p_col_to_update := 'routing_desc';
685 l_update_table(14).p_value := p_routing_desc;
686
687 l_update_table(15).p_col_to_update := 'last_update_date';
688 l_update_table(15).p_value := FND_DATE.DATE_TO_CANONICAL(p_last_update_date);
689
690 l_update_table(16).p_col_to_update := 'last_updated_by';
691 l_update_table(16).p_value := p_user_id;
692
693 l_update_table(17).p_col_to_update := 'last_update_login';
694 l_update_table(17).p_value := p_user_id;
695
696
697 GMD_ROUTINGS_PUB.update_routing
698 ( p_api_version => 1
699 , p_init_msg_list => TRUE
700 , p_commit => FALSE
701 , p_routing_id => p_routing_id
702 , p_routing_no => NULL
703 , p_routing_vers => NULL
704 , p_update_table => l_update_table
705 , x_message_count => l_message_count
706 , x_message_list => l_message_list
707 , x_return_status => l_return_status);
708
709 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
710 RAISE UPDATE_ROUTING_EXCEPTION;
711 END IF;
712
713 --- If routing number and/or version have changed, we need to update them. This
714 --- happens when creating a new routing, because a dummy routing header is created in
715 --- the database. User is then prompted to enter a valid routing number/version prior
716 --- to saving.
717
718 IF l_rec.routing_no <> p_routing_no OR
719 l_rec.routing_vers <> p_routing_vers THEN
720
721 UPDATE
722 GMD_ROUTINGS_B
723 SET
724 routing_no = p_routing_no,
725 routing_vers = p_routing_vers
726 WHERE
727 routing_id = p_routing_id AND
728 last_update_date = p_last_update_date;
729
730 --4504794, this will defaults the PI instructions.
731 GMD_PROCESS_INSTR_UTILS.COPY_PROCESS_INSTR(
732 p_entity_name => 'ROUTING',
733 p_entity_id => p_routing_id,
734 x_return_status => l_return_status,
735 x_msg_count => l_msg_count,
736 x_msg_data => l_msg_data);
737 IF SQL%NOTFOUND THEN
738 RAISE RECORD_CHANGED_EXCEPTION;
739 END IF;
740
741 END IF;
742
743
744 -- Set step release type to Manual if Enforce Step Dependency flag is
745 -- turned on. This is done here only if steps have not been loaded into
746 -- the Designer. Otherwise, the Designer is responsible for updating the
747 -- steps.
748
749 IF (p_update_release_type = 1 AND
750 p_enforce_step_dep = 1) THEN
751
752 UPDATE fm_rout_dtl
753 SET steprelease_type = 1,
754 last_update_date = p_last_update_date,
755 last_updated_by = p_user_id,
756 last_update_login = p_user_id
757 WHERE
758 routing_id = p_routing_id;
759
760 END IF;
761
762
763 EXCEPTION
764 WHEN UPDATE_ROUTING_EXCEPTION THEN
765 FND_MSG_PUB.GET( p_msg_index => 1,
766 p_data => l_message,
767 p_encoded => 'F',
768 p_msg_index_out => l_dummy);
769
770 x_return_code := 'F';
771 x_error_msg := l_message;
772 WHEN RECORD_CHANGED_EXCEPTION THEN
773 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
774 x_return_code := 'F';
775 x_error_msg := FND_MESSAGE.GET;
776 WHEN OTHERS THEN
777 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
778 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
779 x_return_code := 'F';
780 x_error_msg := FND_MESSAGE.GET;
781
782 END Update_Routing_Header;
783
784 /* Api start of comments
785 +============================================================================
786 | PROCEDURE NAME
787 | Check_Version_Control
788 |
789 | DESCRIPTION
790 | Determine whether version control is enabled.
791 |
792 | INPUT PARAMETERS
793 | p_entity_type VARCHAR2
794 | p_entity_id NUMBER
795 |
796 | OUTPUT PARAMETERS
797 | x_version_control VARCHAR2(1)
798 | x_return_code VARCHAR2(1)
799 | x_error_msg VARCHAR2(100)
800 |
801 | HISTORY
802 | 24-JUN-2002 Eddie Oumerretane Created.
803 |
804 +=============================================================================
805 Api end of comments
806 */
807 PROCEDURE Check_Version_Control ( p_entity_type IN VARCHAR2,
808 p_entity_id IN NUMBER,
809 x_version_control OUT NOCOPY VARCHAR2,
810 x_return_code OUT NOCOPY VARCHAR2,
811 x_error_msg OUT NOCOPY VARCHAR2) IS
812
813 BEGIN
814
815 x_return_code := 'S';
816 x_error_msg := '';
817
818 x_version_control := gmd_common_val.version_control_state(p_entity_type,
819 p_entity_id);
820
821 END Check_Version_Control;
822
823 /* Api start of comments
824 +============================================================================
825 | PROCEDURE NAME
826 | Check_Function
827 |
828 | DESCRIPTION
829 | Determine whether user has access to the given function.
830 |
831 | INPUT PARAMETERS
832 | p_function_name VARCHAR2
833 |
834 | OUTPUT PARAMETERS
835 | x_access VARCHAR2(1)
836 |
837 | HISTORY
838 | 25-JUN-2002 Eddie Oumerretane Created.
839 |
840 +=============================================================================
841 Api end of comments
842 */
843 PROCEDURE Check_Function ( p_function_name IN VARCHAR2,
844 x_access OUT NOCOPY VARCHAR2) IS
845
846 BEGIN
847
848 IF FND_FUNCTION.TEST(p_function_name) THEN
849 x_access := 'Y';
850 ELSE
851 x_access := 'N';
852 END IF;
853
854 END Check_Function;
855
856 /* Api start of comments
857 +============================================================================
858 | PROCEDURE NAME
859 | Create_Routing_Step
860 |
861 | DESCRIPTION
862 | Create routing step
863 |
864 | INPUT PARAMETERS
865 | p_routing_id IN NUMBER
866 | p_routingstep_no IN NUMBER
867 | p_routingstep_id IN NUMBER
868 | p_oprn_id IN NUMBER
869 | p_step_qty IN NUMBER
870 | p_release_type IN NUMBER
871 | p_text_code IN NUMBER
872 | p_coordx IN NUMBER
873 | p_coordy IN NUMBER
874 | p_last_update_date IN DATE
875 | p_user_id IN NUMBER
876 |
877 | OUTPUT PARAMETERS
878 | x_return_code VARCHAR2(1)
879 | x_error_msg VARCHAR2(100)
880 |
881 | HISTORY
882 | 02-JUL-2002 Eddie Oumerretane Created.
883 |
884 +=============================================================================
885 Api end of comments
886 */
887 PROCEDURE Insert_Routing_Step ( p_routing_id IN NUMBER,
888 p_routingstep_no IN NUMBER,
889 p_routingstep_id IN NUMBER,
890 p_oprn_id IN NUMBER,
891 p_step_qty IN NUMBER,
892 p_release_type IN NUMBER,
893 p_text_code IN NUMBER,
894 p_last_update_date IN DATE,
895 p_user_id IN NUMBER,
896 p_coordx IN NUMBER,
897 p_coordy IN NUMBER,
898 x_return_code OUT NOCOPY VARCHAR2,
899 x_error_msg OUT NOCOPY VARCHAR2) IS
900
901 l_text_code NUMBER(10);
902 l_routing_step_rec fm_rout_dtl%ROWTYPE;
903 l_return_status VARCHAR2(2);
904 l_message_count NUMBER;
905 l_message_list VARCHAR2(2000);
906 l_message VARCHAR2(1000);
907 l_dummy NUMBER;
908 INSERT_STEP_EXCEPTION EXCEPTION;
909 l_routings_step_dep_tbl GMD_ROUTINGS_PUB.gmd_routings_step_dep_tab;
910
911 BEGIN
912
913 x_error_msg := '';
914 x_return_code := FND_API.G_RET_STS_SUCCESS;
915
916 IF p_text_code <= 0 THEN
917 l_text_code := NULL;
918 ELSE
919 l_text_code := p_text_code;
920 END IF;
921
922 l_routing_step_rec.routing_id := p_routing_id;
923 l_routing_step_rec.routingstep_no := p_routingstep_no;
924 l_routing_step_rec.routingstep_id := p_routingstep_id;
925 l_routing_step_rec.oprn_id := p_oprn_id;
926 l_routing_step_rec.step_qty := p_step_qty;
927 l_routing_step_rec.steprelease_type := p_release_type;
928 l_routing_step_rec.text_code := l_text_code;
929 l_routing_step_rec.x_coordinate := p_coordx;
930 l_routing_step_rec.y_coordinate := p_coordy;
931 l_routing_step_rec.last_updated_by := p_user_id;
932 l_routing_step_rec.created_by := p_user_id;
933 l_routing_step_rec.last_update_date := p_last_update_date;
934 l_routing_step_rec.creation_date := p_last_update_date;
935 l_routing_step_rec.last_update_login := p_user_id;
936
937 GMD_ROUTING_STEPS_PUB.insert_routing_steps (
938 p_api_version => 1
939 , p_init_msg_list => TRUE
940 , p_commit => FALSE
941 , p_routing_id => p_routing_id
942 , p_routing_no => NULL
943 , p_routing_vers => NULL
944 , p_routing_step_rec => l_routing_step_rec
945 , p_routings_step_dep_tbl => l_routings_step_dep_tbl
946 , x_message_count => l_message_count
947 , x_message_list => l_message_list
948 , x_return_status => l_return_status);
949
950 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
951 RAISE INSERT_STEP_EXCEPTION;
952 END IF;
953
954 EXCEPTION
955 WHEN INSERT_STEP_EXCEPTION THEN
956 FND_MSG_PUB.GET( p_msg_index => 1,
957 p_data => l_message,
958 p_encoded => 'F',
959 p_msg_index_out => l_dummy);
960
961 x_return_code := 'F';
962 x_error_msg := l_message;
963 WHEN OTHERS THEN
964 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
965 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
966 x_return_code := 'F';
967 x_error_msg := FND_MESSAGE.GET;
968
969 END Insert_Routing_Step;
970
971 /* Api start of comments
972 +============================================================================
973 | PROCEDURE NAME
974 | Update_Routing_Step
975 |
976 | DESCRIPTION
977 | Update routing step
978 |
979 | INPUT PARAMETERS
980 | p_routingstep_id IN NUMBER
981 | p_release_type IN NUMBER
982 | p_step_qty IN NUMBER
983 | p_text_code IN NUMBER
984 | p_last_update_date IN DATE
985 | p_user_id IN NUMBER
986 | p_last_update_date_orig IN DATE
987 |
988 | OUTPUT PARAMETERS
989 | x_return_code VARCHAR2(1)
990 | x_error_msg VARCHAR2(100)
991 |
992 | HISTORY
993 | 02-JUL-2002 Eddie Oumerretane Created.
994 |
995 +=============================================================================
996 Api end of comments
997 */
998 PROCEDURE Update_Routing_Step ( p_routingstep_id IN NUMBER,
999 p_release_type IN NUMBER,
1000 p_step_qty IN NUMBER,
1001 p_text_code IN NUMBER,
1002 p_coordx IN NUMBER,
1003 p_coordy IN NUMBER,
1004 p_last_update_date IN DATE,
1005 p_user_id IN NUMBER,
1006 p_last_update_date_orig IN DATE,
1007 x_return_code OUT NOCOPY VARCHAR2,
1008 x_error_msg OUT NOCOPY VARCHAR2) IS
1009
1010 l_text_code NUMBER(10);
1011 l_return_status VARCHAR2(2);
1012 l_message_count NUMBER;
1013 l_message_list VARCHAR2(2000);
1014 l_message VARCHAR2(1000);
1015 l_dummy NUMBER;
1016 l_update_table GMD_ROUTINGS_PUB.update_tbl_type;
1017 UPDATE_STEP_EXCEPTION EXCEPTION;
1018 RECORD_CHANGED_EXCEPTION EXCEPTION;
1019
1020 CURSOR Cur_get_step IS
1021 SELECT *
1022 FROM fm_rout_dtl
1023 WHERE routingstep_id = p_routingstep_id AND
1024 last_update_date = p_last_update_date_orig;
1025
1026 l_step_rec Cur_get_step%ROWTYPE;
1027
1028 BEGIN
1029
1030 x_error_msg := '';
1031 x_return_code := FND_API.G_RET_STS_SUCCESS;
1032
1033
1034 IF p_text_code <= 0 THEN
1035 l_text_code := NULL;
1036 ELSE
1037 l_text_code := p_text_code;
1038 END IF;
1039
1040 OPEN Cur_get_step;
1041 FETCH Cur_get_step INTO l_step_rec;
1042
1043 IF Cur_get_step%NOTFOUND THEN
1044 CLOSE Cur_get_step;
1045 RAISE RECORD_CHANGED_EXCEPTION;
1046 END IF;
1047
1048 CLOSE Cur_get_step;
1049
1050 l_update_table(1).p_col_to_update := 'step_qty';
1051 l_update_table(1).p_value := p_step_qty;
1052 l_update_table(2).p_col_to_update := 'steprelease_type';
1053 l_update_table(2).p_value := p_release_type;
1054 l_update_table(3).p_col_to_update := 'text_code';
1055 l_update_table(3).p_value := l_text_code;
1056 l_update_table(4).p_col_to_update := 'x_coordinate';
1057 l_update_table(4).p_value := p_coordx;
1058 l_update_table(5).p_col_to_update := 'y_coordinate';
1059 l_update_table(5).p_value := p_coordy;
1060 l_update_table(6).p_col_to_update := 'last_updated_by';
1061 l_update_table(6).p_value := p_user_id;
1062 l_update_table(7).p_col_to_update := 'last_update_date';
1063 l_update_table(7).p_value := fnd_date.date_to_canonical(p_last_update_date);
1064 l_update_table(8).p_col_to_update := 'last_update_login';
1065 l_update_table(8).p_value := p_user_id;
1066
1067 GMD_ROUTING_STEPS_PUB.update_routing_steps
1068 ( p_api_version => 1
1069 , p_init_msg_list => TRUE
1070 , p_commit => FALSE
1071 , p_routingstep_id => p_routingstep_id
1072 , p_routingstep_no => NULL
1073 , p_routing_id => l_step_rec.routing_id
1074 , p_routing_no => NULL
1075 , p_routing_vers => NULL
1076 , p_update_table => l_update_table
1077 , x_message_count => l_message_count
1078 , x_message_list => l_message_list
1079 , x_return_status => l_return_status);
1080
1081 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1082 RAISE UPDATE_STEP_EXCEPTION;
1083 END IF;
1084
1085 EXCEPTION
1086 WHEN UPDATE_STEP_EXCEPTION THEN
1087 FND_MSG_PUB.GET( p_msg_index => 1,
1088 p_data => l_message,
1089 p_encoded => 'F',
1090 p_msg_index_out => l_dummy);
1091
1092 x_return_code := 'F';
1093 x_error_msg := l_message;
1094
1095 WHEN RECORD_CHANGED_EXCEPTION THEN
1096 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
1097 x_return_code := 'F';
1098 x_error_msg := FND_MESSAGE.GET;
1099
1100 WHEN OTHERS THEN
1101 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1102 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1103 x_return_code := 'F';
1104 x_error_msg := FND_MESSAGE.GET;
1105
1106 END Update_Routing_Step;
1107
1108 /* Api start of comments
1109 +============================================================================
1110 | PROCEDURE NAME
1111 | Create_Routing_Header
1112 |
1113 | DESCRIPTION
1114 | Create routing header
1115 |
1116 | INPUT PARAMETERS
1117 | p_routing_no IN VARCHAR2
1118 | p_routing_vers IN NUMBER,
1119 | p_routing_desc IN VARCHAR2
1120 | p_routing_class IN VARCHAR2
1121 | p_effective_start_date IN DATE
1122 | p_effective_end_date IN DATE
1123 | p_routing_qty IN NUMBER
1124 | p_routing_uom IN VARCHAR2
1125 | p_process_loss IN NUMBER
1126 | p_owner_id IN NUMBER
1127 | p_owner_orgn_id IN NUMBER
1128 | p_enforce_step_dep IN NUMBER
1129 | p_last_update_date IN DATE
1130 | p_user_id IN NUMBER
1131 |
1132 | OUTPUT PARAMETERS
1133 | x_routing_id NUMBER
1134 | x_return_code VARCHAR2(1)
1135 | x_error_msg VARCHAR2(100)
1136 |
1137 | HISTORY
1138 | 06-JUL-2002 Eddie Oumerretane Created.
1139 |
1140 +=============================================================================
1141 Api end of comments
1142 */
1143 PROCEDURE Create_Routing_Header ( p_routing_no IN VARCHAR2,
1144 p_routing_vers IN NUMBER,
1145 p_routing_desc IN VARCHAR2,
1146 p_routing_class IN VARCHAR2,
1147 p_effective_start_date IN DATE,
1148 p_effective_end_date IN DATE,
1149 p_routing_qty IN NUMBER,
1150 p_routing_uom IN VARCHAR2,
1151 p_process_loss IN NUMBER,
1152 p_owner_id IN NUMBER,
1153 p_owner_orgn_id IN NUMBER,
1154 p_enforce_step_dep IN NUMBER,
1155 p_contiguous_ind IN NUMBER,
1156 p_last_update_date IN DATE,
1157 p_user_id IN NUMBER,
1158 x_routing_id OUT NOCOPY NUMBER,
1159 x_return_code OUT NOCOPY VARCHAR2,
1160 x_error_msg OUT NOCOPY VARCHAR2) IS
1161
1162
1163 CURSOR Cur_routing_id IS
1164 SELECT gem5_routing_id_s.NEXTVAL
1165 FROM FND_DUAL;
1166
1167 l_return_status VARCHAR2(5);
1168 l_owner_orgn_id VARCHAR2(4);
1169 l_timestamp DATE;
1170 l_routing_no VARCHAR2(32);
1171 l_enforce_step_dependency NUMBER;
1172 l_routing_rec GMD_ROUTINGS%ROWTYPE;
1173 INSERT_ROUTING_EXCEPTION EXCEPTION;
1174
1175 l_message_count NUMBER;
1176 l_message_list VARCHAR2(2000);
1177 l_message VARCHAR2(1000);
1178 l_dummy NUMBER;
1179
1180 BEGIN
1181
1182 x_return_code := FND_API.G_RET_STS_SUCCESS;
1183 x_error_msg := FND_MESSAGE.GET;
1184 OPEN Cur_routing_id;
1185 FETCH Cur_routing_id INTO x_routing_id;
1186 CLOSE Cur_routing_id;
1187
1188 IF p_enforce_step_dep IS NULL THEN
1189 l_enforce_step_dependency := FND_PROFILE.VALUE('GMD_ENFORCE_STEP_DEPENDENCY');
1190 ELSE
1191 l_enforce_step_dependency := p_enforce_step_dep;
1192 END IF;
1193
1194 IF (l_enforce_step_dependency IS NULL) THEN
1195 l_enforce_step_dependency := 0;
1196 END IF;
1197
1198 IF p_routing_no IS NOT NULL THEN
1199 l_routing_rec.routing_no := p_routing_no;
1200 l_routing_rec.routing_vers := p_routing_vers;
1201 l_routing_rec.routing_desc := p_routing_desc;
1202 ELSE
1203 l_timestamp := SYSDATE;
1204 --- Create a unique routing number. This is used to initialize the routing
1205 --- in the database. User will be prompted to enter a valid routing number
1206 --- prior to saving.
1207
1208 l_routing_no := x_routing_id || '#' ||
1209 TO_CHAR(l_timestamp, 'YYYYMMDDHH24MISS');
1210 l_routing_rec.routing_no := l_routing_no;
1211 l_routing_rec.routing_vers := 1;
1212 l_routing_rec.routing_desc := 'New';
1213 END IF;
1214
1215 l_routing_rec.routing_id := x_routing_id;
1216 l_routing_rec.owner_organization_id := p_owner_orgn_id;
1217 l_routing_rec.routing_class := p_routing_class;
1218 l_routing_rec.routing_qty := p_routing_qty;
1219 l_routing_rec.routing_uom := p_routing_uom;
1220 l_routing_rec.delete_mark := 0;
1221 l_routing_rec.text_code := NULL;
1222 l_routing_rec.inactive_ind := 0;
1223 l_routing_rec.enforce_step_dependency := l_enforce_step_dependency;
1224 l_routing_rec.contiguous_ind := NVL(p_contiguous_ind, 0);
1225 l_routing_rec.in_use := 0;
1226 l_routing_rec.attribute1 := '';
1227 l_routing_rec.attribute2 := '';
1228 l_routing_rec.attribute3 := '';
1229 l_routing_rec.attribute4 := '';
1230 l_routing_rec.attribute5 := '';
1231 l_routing_rec.attribute6 := '';
1232 l_routing_rec.attribute7 := '';
1233 l_routing_rec.attribute8 := '';
1234 l_routing_rec.attribute9 := '';
1235 l_routing_rec.attribute10 := '';
1236 l_routing_rec.attribute11 := '';
1237 l_routing_rec.attribute12 := '';
1238 l_routing_rec.attribute13 := '';
1239 l_routing_rec.attribute14 := '';
1240 l_routing_rec.attribute15 := '';
1241 l_routing_rec.attribute16 := '';
1242 l_routing_rec.attribute17 := '';
1243 l_routing_rec.attribute18 := '';
1244 l_routing_rec.attribute19 := '';
1245 l_routing_rec.attribute20 := '';
1246 l_routing_rec.attribute21 := '';
1247 l_routing_rec.attribute22 := '';
1248 l_routing_rec.attribute23 := '';
1249 l_routing_rec.attribute24 := '';
1250 l_routing_rec.attribute25 := '';
1251 l_routing_rec.attribute26 := '';
1252 l_routing_rec.attribute27 := '';
1253 l_routing_rec.attribute28 := '';
1254 l_routing_rec.attribute29 := '';
1255 l_routing_rec.attribute30 := '';
1256 l_routing_rec.attribute_category := '';
1257 l_routing_rec.effective_start_date := p_effective_start_date;
1258 l_routing_rec.effective_end_date := p_effective_end_date;
1259 l_routing_rec.owner_id := p_owner_id;
1260 l_routing_rec.project_id := NULL;
1261 l_routing_rec.process_loss := -1;
1262 l_routing_rec.routing_status := '100';
1263 l_routing_rec.creation_date := p_last_update_date;
1264 l_routing_rec.created_by := p_user_id;
1265 l_routing_rec.last_update_date := p_last_update_date;
1266 l_routing_rec.last_updated_by := p_user_id;
1267 l_routing_rec.last_update_login := p_user_id;
1268
1269 GMD_ROUTINGS_PVT.insert_routing ( p_routings => l_routing_rec
1270 , x_message_count => l_message_count
1271 , x_message_list => l_message_list
1272 , x_return_status => l_return_status);
1273
1274 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1275 RAISE INSERT_ROUTING_EXCEPTION;
1276 END IF;
1277
1278 EXCEPTION
1279 WHEN INSERT_ROUTING_EXCEPTION THEN
1280 FND_MSG_PUB.GET( p_msg_index => 1,
1281 p_data => l_message,
1282 p_encoded => 'F',
1283 p_msg_index_out => l_dummy);
1284
1285 x_return_code := 'F';
1286 x_error_msg := l_message;
1287
1288 WHEN OTHERS THEN
1289 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1290 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1291 x_return_code := 'F';
1292 x_error_msg := FND_MESSAGE.GET;
1293
1294 END Create_Routing_Header;
1295
1296
1297 /* Api start of comments
1298 +============================================================================
1299 | PROCEDURE NAME
1300 | Generate_Step_Dependencies
1301 |
1302 | DESCRIPTION
1303 | Generate sequential step dependencies
1304 |
1305 | INPUT PARAMETERS
1306 | p_routing_id IN NUMBER
1307 | p_dependency_type IN NUMBER
1308 |
1309 | OUTPUT PARAMETERS
1310 | x_return_code VARCHAR2(1)
1311 | x_error_msg VARCHAR2(100)
1312 |
1313 | HISTORY
1314 | 09-JUL-2002 Eddie Oumerretane Created.
1315 |
1316 +=============================================================================
1317 Api end of comments
1318
1319 */
1320 PROCEDURE Generate_Step_Dependencies(p_routing_id IN NUMBER,
1321 p_dependency_type IN NUMBER,
1322 x_return_code OUT NOCOPY VARCHAR2,
1323 x_error_msg OUT NOCOPY VARCHAR2) IS
1324 l_status VARCHAR2(2);
1325 l_message VARCHAR2(1000);
1326 l_dummy NUMBER;
1327
1328 BEGIN
1329
1330 ---FND_GLOBAL.APPS_INITIALIZE(3713,52854, 552);
1331
1332 gmdrtval_pub.generate_step_dependencies (p_routing_id,
1333 p_dependency_type,
1334 l_status);
1335 x_return_code := 'S';
1336
1337 IF l_status <> 'S' THEN
1338
1339 FND_MSG_PUB.GET( p_msg_index => 1,
1340 p_data => l_message,
1341 p_encoded => 'F',
1342 p_msg_index_out => l_dummy);
1343
1344 x_return_code := 'F';
1345 x_error_msg := l_message;
1346
1347 END IF;
1348
1349 END Generate_Step_Dependencies;
1350
1351
1352 /* Api start of comments
1353 +============================================================================
1354 | PROCEDURE NAME
1355 | Delete_Routing_Step
1356 |
1357 | DESCRIPTION
1358 | Delete a step
1359 |
1360 | INPUT PARAMETERS
1361 | p_routing_id NUMBER
1362 | p_routingstep_id NUMBER
1363 | p_last_update_date DATE
1364 |
1365 | OUTPUT PARAMETERS
1366 | x_return_code VARCHAR2(1)
1367 | x_error_msg VARCHAR2(100)
1368 |
1369 | HISTORY
1370 | 16-JUL-2002 Eddie Oumerretane Created.
1371 |
1372 +=============================================================================
1373 Api end of comments
1374 */
1375
1376 PROCEDURE Delete_Routing_Step ( p_routing_id IN NUMBER,
1377 p_routingstep_id IN NUMBER,
1378 p_last_update_date IN DATE,
1379 x_return_code OUT NOCOPY VARCHAR2,
1380 x_error_msg OUT NOCOPY VARCHAR2) IS
1381
1382 l_return_status VARCHAR2(2);
1383 l_message_count NUMBER;
1384 l_message_list VARCHAR2(2000);
1385 l_message VARCHAR2(1000);
1386 l_dummy NUMBER;
1387
1388 DELETE_STEP_EXCEPTION EXCEPTION;
1389 RECORD_CHANGED_EXCEPTION EXCEPTION;
1390
1391 BEGIN
1392
1393 x_return_code := 'S';
1394 x_error_msg := '';
1395
1396 SELECT
1397 routingstep_id INTO l_dummy
1398 FROM
1399 fm_rout_dtl
1400 WHERE
1401 routing_id = p_routing_id AND
1402 routingstep_id = p_routingstep_id AND
1403 last_update_date = p_last_update_date;
1404
1405 IF SQL%NOTFOUND THEN
1406 RAISE RECORD_CHANGED_EXCEPTION;
1407 END IF;
1408
1409 GMD_ROUTING_STEPS_PUB.delete_routing_step
1410 ( p_api_version => 1
1411 , p_init_msg_list => TRUE
1412 , p_commit => FALSE
1413 , p_routingstep_id => p_routingstep_id
1414 , p_routingstep_no => NULL
1415 , p_routing_id => p_routing_id
1416 , p_routing_no => NULL
1417 , p_routing_vers => NULL
1418 , x_message_count => l_message_count
1419 , x_message_list => l_message_list
1420 , x_return_status => l_return_status);
1421
1422 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1423 RAISE DELETE_STEP_EXCEPTION;
1424 END IF;
1425
1426 EXCEPTION
1427 WHEN DELETE_STEP_EXCEPTION THEN
1428 FND_MSG_PUB.GET( p_msg_index => 1,
1429 p_data => l_message,
1430 p_encoded => 'F',
1431 p_msg_index_out => l_dummy);
1432
1433 x_return_code := 'F';
1434 x_error_msg := l_message;
1435
1436 WHEN RECORD_CHANGED_EXCEPTION THEN
1437 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
1438 x_return_code := 'F';
1439 x_error_msg := FND_MESSAGE.GET;
1440 WHEN OTHERS THEN
1441 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1442 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1443 x_return_code := 'F';
1444 x_error_msg := FND_MESSAGE.GET;
1445 END Delete_Routing_Step;
1446
1447 /* Api start of comments
1448 +============================================================================
1449 | PROCEDURE NAME
1450 | Copy_Routing
1451 |
1452 | DESCRIPTION
1453 | Copy the given routing
1454 |
1455 | INPUT PARAMETERS
1456 | p_copy_from_routing_id NUMBER
1457 | p_routing_no VARCHAR2
1458 | p_routing_vers NUMBER
1459 | p_routing_desc VARCHAR2
1460 |
1461 | OUTPUT PARAMETERS
1462 | x_routing_id NUMBER
1463 | x_return_code VARCHAR2(1)
1464 | x_error_msg VARCHAR2(100)
1465 |
1466 | HISTORY
1467 | 18-JUL-2002 Eddie Oumerretane Created.
1468 | 08-AUG-2006 Removed orgn_id for bug# 5206623
1469 |
1470 +=============================================================================
1471 Api end of comments
1472 */
1473 PROCEDURE Copy_Routing ( p_copy_from_routing_id IN NUMBER,
1474 p_routing_no IN VARCHAR2,
1475 p_routing_vers IN NUMBER,
1476 p_routing_desc IN VARCHAR2,
1477 x_routing_id OUT NOCOPY NUMBER,
1478 x_return_code OUT NOCOPY VARCHAR2,
1479 x_error_msg OUT NOCOPY VARCHAR2) IS
1480
1481 CURSOR Cur_routing_id IS
1482 SELECT gem5_routing_id_s.NEXTVAL
1483 FROM FND_DUAL;
1484
1485 CURSOR Cur_get_hdr IS
1486 SELECT *
1487 FROM gmd_routings
1488 WHERE routing_id = p_copy_from_routing_id;
1489
1490 CURSOR Cur_get_dtl IS
1491 SELECT *
1492 FROM fm_rout_dtl
1493 WHERE routing_id = p_copy_from_routing_id;
1494
1495 CURSOR Cur_get_dep IS
1496 SELECT *
1497 FROM fm_rout_dep
1498 WHERE routing_id = p_copy_from_routing_id;
1499
1500 CURSOR Get_Text (p_text_code NUMBER) IS
1501 SELECT *
1502 FROM fm_text_tbl
1503 WHERE text_code = p_text_code AND
1504 line_no <> -1;
1505
1506 CURSOR Get_text_code IS
1507 SELECT gem5_text_code_s.NEXTVAL
1508 FROM sys.dual;
1509
1510 CURSOR Get_RoutingStepId IS
1511 SELECT gem5_routingstep_id_s.NEXTVAL
1512 FROM sys.dual;
1513
1514 TYPE detail_tab IS TABLE OF Cur_get_dtl%ROWTYPE INDEX BY BINARY_INTEGER;
1515 TYPE dep_tab IS TABLE OF Cur_get_dep%ROWTYPE INDEX BY BINARY_INTEGER;
1516 TYPE text_tab IS TABLE OF Get_Text%ROWTYPE INDEX BY BINARY_INTEGER;
1517
1518 X_hdr_rec Cur_get_hdr%ROWTYPE;
1519 X_dtl_tbl detail_tab;
1520 X_dep_tbl dep_tab;
1521 l_dtl_text_tbl text_tab;
1522 l_hdr_text_tbl text_tab;
1523 l_row NUMBER := 0;
1524 l_txt_ind NUMBER;
1525 l_rowid VARCHAR2(32);
1526 l_text_code NUMBER(10);
1527 l_error_msg VARCHAR2(2000);
1528 l_return_code VARCHAR2(2);
1529 l_table_lnk VARCHAR2(80);
1530 l_routingstep_id NUMBER(10);
1531 COPY_HEADER_TEXT_EXCEPTION EXCEPTION;
1532
1533 BEGIN
1534
1535 x_error_msg := '';
1536 x_return_code := FND_API.G_RET_STS_SUCCESS;
1537
1538 -- Load routing header
1539 OPEN Cur_get_hdr;
1540 FETCH Cur_get_hdr INTO X_hdr_rec;
1541 CLOSE Cur_get_hdr;
1542
1543 -- Load routing header text
1544
1545 IF (x_hdr_rec.text_code IS NOT NULL) THEN
1546
1547 l_txt_ind := 0;
1548
1549 FOR get_txt_rec IN Get_Text (x_hdr_rec.text_code) LOOP
1550 l_txt_ind := l_txt_ind + 1;
1551 l_hdr_text_tbl(l_txt_ind) := get_txt_rec;
1552 END LOOP;
1553
1554 END IF;
1555
1556 l_txt_ind := 0;
1557
1558 -- Load routing details
1559 FOR get_rec IN Cur_get_dtl LOOP
1560
1561 l_row := l_row + 1;
1562 X_dtl_tbl(l_row) := get_rec;
1563
1564 IF (get_rec.text_code IS NOT NULL) THEN
1565
1566 -- Load text for this step
1567 FOR get_txt_rec IN Get_Text (get_rec.text_code) LOOP
1568 l_txt_ind := l_txt_ind + 1;
1569 l_dtl_text_tbl(l_txt_ind) := get_txt_rec;
1570 END LOOP;
1571
1572 END IF;
1573
1574 END LOOP;
1575
1576 l_row := 0;
1577
1578 -- Load routing step dependencies
1579 FOR get_dep IN Cur_get_dep LOOP
1580 l_row := l_row + 1;
1581 X_dep_tbl(l_row) := get_dep;
1582 END LOOP;
1583
1584 -- Do not commit pending changes to the original routing
1585 ROLLBACK;
1586
1587 OPEN Cur_routing_id;
1588 FETCH Cur_routing_id INTO x_routing_id;
1589 CLOSE Cur_routing_id;
1590
1591 l_text_code := NULL;
1592
1593 IF (l_hdr_text_tbl.COUNT > 0) THEN
1594
1595 OPEN Get_Text_Code;
1596 FETCH Get_Text_Code INTO l_text_code;
1597 CLOSE Get_Text_Code;
1598
1599 l_txt_ind := 0;
1600
1601 FOR i IN 1..l_hdr_text_tbl.COUNT LOOP
1602
1603 l_txt_ind := l_txt_ind + 1;
1604 l_table_lnk := 'gmd_routings' || '|' || x_routing_id;
1605
1606 -- Create routing header text
1607 GMD_RECIPE_DESIGNER_PKG.Create_Text_Row
1608 (p_text_code => l_text_code,
1609 p_lang_code => l_hdr_text_tbl(l_txt_ind).lang_code,
1610 p_text => l_hdr_text_tbl(l_txt_ind).text,
1611 p_line_no => l_hdr_text_tbl(l_txt_ind).line_no,
1612 p_paragraph_code => l_hdr_text_tbl(l_txt_ind).paragraph_code,
1613 p_sub_paracode => l_hdr_text_tbl(l_txt_ind).sub_paracode,
1614 p_table_lnk => l_table_lnk,
1615 p_user_id => g_created_by,
1616 x_row_id => l_rowid,
1617 x_return_code => l_return_code,
1618 x_error_msg => l_error_msg);
1619
1620 IF (l_return_code <> 'S') THEN
1621 RAISE COPY_HEADER_TEXT_EXCEPTION;
1622 END IF;
1623
1624 END LOOP;
1625
1626 END IF;
1627
1628 -- Insert routing header record
1629
1630 GMD_ROUTINGS_PKG.INSERT_ROW(
1631 X_ROWID => l_rowid,
1632 X_ROUTING_ID => x_routing_id,
1633 X_OWNER_ORGANIZATION_ID => X_HDR_rec.owner_organization_id,
1634 X_ROUTING_NO => p_routing_no,
1635 X_ROUTING_VERS => p_routing_vers,
1636 X_ROUTING_CLASS => X_HDR_rec.ROUTING_CLASS,
1637 X_ROUTING_QTY => X_HDR_rec.ROUTING_QTY,
1638 X_ROUTING_UOM => X_HDR_rec.routing_uom,
1639 X_DELETE_MARK => 0,
1640 X_TEXT_CODE => l_text_code,
1641 X_INACTIVE_IND => 0,
1642 X_ENFORCE_STEP_DEPENDENCY => X_HDR_rec.ENFORCE_STEP_DEPENDENCY,
1643 X_CONTIGUOUS_IND => null,
1644 X_IN_USE => 0,
1645 X_ATTRIBUTE1 => X_HDR_rec.ATTRIBUTE1,
1646 X_ATTRIBUTE2 => X_HDR_rec.ATTRIBUTE2,
1647 X_ATTRIBUTE3 => X_HDR_rec.ATTRIBUTE3,
1648 X_ATTRIBUTE4 => X_HDR_rec.ATTRIBUTE4,
1649 X_ATTRIBUTE5 => X_HDR_rec.ATTRIBUTE5,
1650 X_ATTRIBUTE6 => X_HDR_rec.ATTRIBUTE6,
1651 X_ATTRIBUTE7 => X_HDR_rec.ATTRIBUTE7,
1652 X_ATTRIBUTE8 => X_HDR_rec.ATTRIBUTE8,
1653 X_ATTRIBUTE9 => X_HDR_rec.ATTRIBUTE9,
1654 X_ATTRIBUTE10 => X_HDR_rec.ATTRIBUTE10,
1655 X_ATTRIBUTE11 => X_HDR_rec.ATTRIBUTE11,
1656 X_ATTRIBUTE12 => X_HDR_rec.ATTRIBUTE12,
1657 X_ATTRIBUTE13 => X_HDR_rec.ATTRIBUTE13,
1658 X_ATTRIBUTE14 => X_HDR_rec.ATTRIBUTE14,
1659 X_ATTRIBUTE15 => X_HDR_rec.ATTRIBUTE15,
1660 X_ATTRIBUTE16 => X_HDR_rec.ATTRIBUTE16,
1661 X_ATTRIBUTE17 => X_HDR_rec.ATTRIBUTE17,
1662 X_ATTRIBUTE18 => X_HDR_rec.ATTRIBUTE18,
1663 X_ATTRIBUTE19 => X_HDR_rec.ATTRIBUTE19,
1664 X_ATTRIBUTE20 => X_HDR_rec.ATTRIBUTE20,
1665 X_ATTRIBUTE21 => X_HDR_rec.ATTRIBUTE21,
1666 X_ATTRIBUTE22 => X_HDR_rec.ATTRIBUTE22,
1667 X_ATTRIBUTE23 => X_HDR_rec.ATTRIBUTE23,
1668 X_ATTRIBUTE24 => X_HDR_rec.ATTRIBUTE24,
1669 X_ATTRIBUTE25 => X_HDR_rec.ATTRIBUTE25,
1670 X_ATTRIBUTE26 => X_HDR_rec.ATTRIBUTE26,
1671 X_ATTRIBUTE27 => X_HDR_rec.ATTRIBUTE27,
1672 X_ATTRIBUTE28 => X_HDR_rec.ATTRIBUTE28,
1673 X_ATTRIBUTE29 => X_HDR_rec.ATTRIBUTE29,
1674 X_ATTRIBUTE30 => X_HDR_rec.ATTRIBUTE30,
1675 X_ATTRIBUTE_CATEGORY => X_HDR_rec.ATTRIBUTE_CATEGORY,
1676 X_EFFECTIVE_START_DATE => X_HDR_rec.EFFECTIVE_START_DATE,
1677 X_EFFECTIVE_END_DATE => X_HDR_rec.EFFECTIVE_END_DATE,
1678 X_OWNER_ID => G_created_by,
1679 X_PROJECT_ID => X_HDR_rec.PROJECT_ID,
1680 X_PROCESS_LOSS => X_HDR_rec.PROCESS_LOSS,
1681 X_ROUTING_STATUS => 100,
1682 X_ROUTING_DESC => p_routing_desc,
1683 X_CREATION_DATE => SYSDATE,
1684 X_CREATED_BY => G_created_by,
1685 X_LAST_UPDATE_DATE => SYSDATE,
1686 X_LAST_UPDATED_BY => G_created_by,
1687 X_LAST_UPDATE_LOGIN => G_login_id);
1688
1689
1690 -- Insert routing detail records
1691 l_txt_ind := 1;
1692
1693 FOR i IN 1..X_dtl_tbl.count LOOP
1694
1695 l_text_code := NULL;
1696 OPEN Get_RoutingStepId;
1697 FETCH Get_RoutingStepId INTO l_routingstep_id;
1698 CLOSE Get_RoutingStepId;
1699
1700 IF (x_dtl_tbl(i).text_code > 0) THEN
1701
1702 OPEN Get_Text_Code;
1703 FETCH Get_Text_Code INTO l_text_code;
1704 CLOSE Get_Text_Code;
1705
1706 l_table_lnk := 'fm_rout_dtl' || '|' || x_routing_id || '|' || l_routingstep_id;
1707
1708 WHILE (l_txt_ind <= l_dtl_text_tbl.COUNT AND
1709 l_dtl_text_tbl(l_txt_ind).text_code = x_dtl_tbl(i).text_code) LOOP
1710
1711 -- Create routing step text
1712 GMD_RECIPE_DESIGNER_PKG.Create_Text_Row
1713 (p_text_code => l_text_code,
1714 p_lang_code => l_dtl_text_tbl(l_txt_ind).lang_code,
1715 p_text => l_dtl_text_tbl(l_txt_ind).text,
1716 p_line_no => l_dtl_text_tbl(l_txt_ind).line_no,
1717 p_paragraph_code => l_dtl_text_tbl(l_txt_ind).paragraph_code,
1718 p_sub_paracode => l_dtl_text_tbl(l_txt_ind).sub_paracode,
1719 p_table_lnk => l_table_lnk,
1720 p_user_id => g_created_by,
1721 x_row_id => l_rowid,
1722 x_return_code => l_return_code,
1723 x_error_msg => l_error_msg);
1724
1725
1726 IF (l_return_code <> 'S') THEN
1727 RAISE COPY_HEADER_TEXT_EXCEPTION;
1728 END IF;
1729
1730 l_txt_ind := l_txt_ind + 1;
1731
1732 END LOOP;
1733
1734 END IF;
1735
1736
1737 INSERT INTO fm_rout_dtl
1738 (routing_id, routingstep_no, routingstep_id, oprn_id,
1739 step_qty, steprelease_type,
1740 text_code, creation_date, created_by, last_update_login,
1741 last_update_date,
1742 last_updated_by, attribute1, attribute2, attribute3,
1743 attribute4, attribute5,
1744 attribute6, attribute7, attribute8, attribute9, attribute10,
1745 attribute11, attribute12, attribute13, attribute14, attribute15,
1746 attribute16, attribute17, attribute18, attribute19, attribute20,
1747 attribute21, attribute22, attribute23, attribute24, attribute25,
1748 attribute26, attribute27, attribute28, attribute29, attribute30,
1749 attribute_category, x_coordinate, y_coordinate)
1750 VALUES (x_routing_id, X_dtl_tbl(i).routingstep_no,
1751 l_routingstep_id,
1752 X_dtl_tbl(i).oprn_id,
1753 X_dtl_tbl(i).step_qty, X_dtl_tbl(i).steprelease_type,
1754 l_text_code, SYSDATE, G_created_by,
1755 G_login_id, SYSDATE, G_created_by,
1756 X_dtl_tbl(i).attribute1, X_dtl_tbl(i).attribute2,
1757 X_dtl_tbl(i).attribute3,
1758 X_dtl_tbl(i).attribute4, X_dtl_tbl(i).attribute5,
1759 X_dtl_tbl(i).attribute6,
1760 X_dtl_tbl(i).attribute7, X_dtl_tbl(i).attribute8,
1761 X_dtl_tbl(i).attribute9,
1762 X_dtl_tbl(i).attribute10,
1763 X_dtl_tbl(i).attribute11, X_dtl_tbl(i).attribute12,
1764 X_dtl_tbl(i).attribute13,
1765 X_dtl_tbl(i).attribute14, X_dtl_tbl(i).attribute15,
1766 X_dtl_tbl(i).attribute16,
1767 X_dtl_tbl(i).attribute17, X_dtl_tbl(i).attribute18,
1768 X_dtl_tbl(i).attribute19,
1769 X_dtl_tbl(i).attribute20, X_dtl_tbl(i).attribute21,
1770 X_dtl_tbl(i).attribute22,
1771 X_dtl_tbl(i).attribute23, X_dtl_tbl(i).attribute24,
1772 X_dtl_tbl(i).attribute25, X_dtl_tbl(i).attribute26,
1773 X_dtl_tbl(i).attribute27,
1774 X_dtl_tbl(i).attribute28, X_dtl_tbl(i).attribute29,
1775 X_dtl_tbl(i).attribute30,
1776 X_dtl_tbl(i).attribute_category,
1777 X_dtl_tbl(i).x_coordinate,
1778 X_dtl_tbl(i).y_coordinate);
1779 END LOOP;
1780
1781 -- Insert routing step dependencies records
1782
1783 FOR i IN 1..X_dep_tbl.count LOOP
1784
1785 INSERT INTO fm_rout_dep
1786 (ROUTINGSTEP_NO,
1787 DEP_ROUTINGSTEP_NO,
1788 ROUTING_ID,
1789 DEP_TYPE,
1790 REWORK_CODE,
1791 STANDARD_DELAY,
1792 MINIMUM_DELAY,
1793 MAX_DELAY,
1794 TRANSFER_QTY,
1795 ROUTINGSTEP_NO_UOM,
1796 TEXT_CODE,
1797 LAST_UPDATED_BY,
1798 CREATED_BY,
1799 LAST_UPDATE_DATE,
1800 CREATION_DATE,
1801 LAST_UPDATE_LOGIN,
1802 TRANSFER_PCT)
1803 VALUES (X_dep_tbl(i).ROUTINGSTEP_NO,
1804 X_dep_tbl(i).DEP_ROUTINGSTEP_NO,
1805 x_routing_id,
1806 X_dep_tbl(i).DEP_TYPE,
1807 X_dep_tbl(i).REWORK_CODE,
1808 X_dep_tbl(i).STANDARD_DELAY,
1809 X_dep_tbl(i).MINIMUM_DELAY,
1810 X_dep_tbl(i).MAX_DELAY,
1811 X_dep_tbl(i).TRANSFER_QTY,
1812 X_dep_tbl(i).ROUTINGSTEP_NO_UOM,
1813 X_dep_tbl(i).TEXT_CODE,
1814 G_created_by,
1815 G_created_by,
1816 SYSDATE,
1817 SYSDATE,
1818 G_login_id,
1819 X_dep_tbl(i).TRANSFER_PCT);
1820
1821 END LOOP;
1822
1823 COMMIT;
1824
1825 EXCEPTION
1826 WHEN COPY_HEADER_TEXT_EXCEPTION THEN
1827 x_return_code := 'F';
1828 x_error_msg := l_error_msg;
1829
1830 WHEN OTHERS THEN
1831 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1832 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1833 x_return_code := 'F';
1834 x_error_msg := FND_MESSAGE.GET;
1835
1836 END Copy_Routing;
1837
1838 /* Api start of comments
1839 +============================================================================
1840 | PROCEDURE NAME
1841 | Save_Profile_Value
1842 |
1843 | DESCRIPTION
1844 | Save the given profile option
1845 |
1846 | INPUT PARAMETERS
1847 | p_profile_name VARCHAR2
1848 | p_profile_value VARCHAR2
1849 |
1850 | OUTPUT PARAMETERS
1851 | x_return_code VARCHAR2(1)
1852 | x_error_msg VARCHAR2(100)
1853 |
1854 | HISTORY
1855 | 18-JUL-2002 Eddie Oumerretane Created.
1856 |
1857 +=============================================================================
1858 Api end of comments
1859 */
1860 PROCEDURE Save_Profile_Value ( p_profile_name IN VARCHAR2,
1861 p_profile_value IN VARCHAR2,
1862 x_return_code OUT NOCOPY VARCHAR2,
1863 x_error_msg OUT NOCOPY VARCHAR2) IS
1864
1865 l_return BOOLEAN;
1866
1867 BEGIN
1868
1869 x_error_msg := '';
1870
1871 l_return := FND_PROFILE.Save_User(p_profile_name,
1872 p_profile_value);
1873
1874 IF l_return THEN
1875 x_return_code := 'S';
1876 ELSE
1877 x_return_code := 'F';
1878 END IF;
1879
1880 EXCEPTION
1881 WHEN OTHERS THEN
1882 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1883 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1884 x_return_code := 'F';
1885 x_error_msg := FND_MESSAGE.GET;
1886
1887 END Save_Profile_Value;
1888
1889 /* Api start of comments
1890 +============================================================================
1891 | PROCEDURE NAME
1892 | Get_Profile_Value
1893 |
1894 | DESCRIPTION
1895 | Get the value of the given profile option
1896 |
1897 | INPUT PARAMETERS
1898 | p_profile_name VARCHAR2
1899 |
1900 | OUTPUT PARAMETERS
1901 | x_profile_value VARCHAR2
1902 | x_return_code VARCHAR2(1)
1903 | x_error_msg VARCHAR2(100)
1904 |
1905 | HISTORY
1906 | 18-JUL-2002 Eddie Oumerretane Created.
1907 |
1908 +=============================================================================
1909 Api end of comments
1910 */
1911 PROCEDURE Get_Profile_Value ( p_profile_name IN VARCHAR2,
1912 x_profile_value OUT NOCOPY VARCHAR2,
1913 x_return_code OUT NOCOPY VARCHAR2,
1914 x_error_msg OUT NOCOPY VARCHAR2) IS
1915
1916 BEGIN
1917
1918 x_error_msg := '';
1919 x_return_code := 'S';
1920
1921 x_profile_value := FND_PROFILE.VALUE(p_profile_name);
1922
1923
1924 IF x_profile_value IS NULL THEN
1925 x_return_code := 'F';
1926 x_error_msg := 'Profile option ' || p_profile_name || ' not found.';
1927 ELSE
1928 x_return_code := 'S';
1929 END IF;
1930
1931 EXCEPTION
1932 WHEN OTHERS THEN
1933 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1934 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1935 x_return_code := 'F';
1936 x_error_msg := FND_MESSAGE.GET;
1937
1938 END Get_Profile_Value;
1939
1940
1941
1942
1943 /* Api start of comments
1944 +============================================================================
1945 | PROCEDURE NAME
1946 | Validate_Routing_Details
1947 |
1948 | DESCRIPTION
1949 | Validate routing details
1950 |
1951 | INPUT PARAMETERS
1952 | p_routing_id NUMBER
1953 |
1954 | OUTPUT PARAMETERS
1955 | x_return_code VARCHAR2(1)
1956 | x_error_msg VARCHAR2(100)
1957 |
1958 | HISTORY
1959 | 24-JUL-2002 Eddie Oumerretane Created.
1960 |
1961 +=============================================================================
1962 Api end of comments
1963 */
1964 PROCEDURE Validate_Routing_Details ( p_routing_id IN VARCHAR2,
1965 x_return_code OUT NOCOPY VARCHAR2,
1966 x_error_msg OUT NOCOPY VARCHAR2) IS
1967
1968 l_return_status VARCHAR2(10);
1969 l_msg_count NUMBER;
1970 l_msg_index NUMBER;
1971 l_msg_data VARCHAR2(240);
1972
1973 BEGIN
1974
1975 x_error_msg := '';
1976 x_return_code := 'S';
1977
1978 GMDRTVAL_PUB.Validate_Routing_Details(prouting_id => p_routing_id,
1979 x_msg_count => l_msg_count,
1980 x_msg_stack => l_msg_data,
1981 x_return_status => l_return_status);
1982
1983 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1984 FND_MSG_PUB.GET(p_msg_index => 1,
1985 p_data => x_error_msg,
1986 p_encoded => 'F',
1987 p_msg_index_out => l_msg_index);
1988 x_return_code := 'F';
1989 END IF;
1990
1991
1992 EXCEPTION
1993 WHEN OTHERS THEN
1994 FND_MSG_PUB.GET(p_msg_index => 1,
1995 p_data => x_error_msg,
1996 p_encoded => 'F',
1997 p_msg_index_out => l_msg_index);
1998 x_return_code := 'F';
1999
2000 END Validate_Routing_Details;
2001
2002 /* Api start of comments
2003 +============================================================================
2004 | PROCEDURE NAME
2005 | Validate_Routing_VR_Dates
2006 |
2007 | DESCRIPTION
2008 | Verify that the routing effective dates falls within all recipe validity
2009
2010
2011 | rules that are using the routing.
2012 |
2013 | INPUT PARAMETERS
2014 | p_routing_id NUMBER
2015 |
2016 | OUTPUT PARAMETERS
2017 | x_update_vr VARCHAR2(1)
2018 | x_return_code VARCHAR2(1)
2019 | x_error_msg VARCHAR2(100)
2020 |
2021 | HISTORY
2022 | 24-JUL-2002 Eddie Oumerretane Created.
2023 |
2024 +=============================================================================
2025 Api end of comments
2026 */
2027 PROCEDURE Validate_Routing_VR_Dates ( p_routing_id IN VARCHAR2,
2028 x_update_vr OUT NOCOPY VARCHAR2,
2029 x_return_code OUT NOCOPY VARCHAR2,
2030 x_error_msg OUT NOCOPY VARCHAR2) IS
2031
2032 l_return_status VARCHAR2(10);
2033 l_msg_count NUMBER;
2034 l_msg_index NUMBER;
2035 l_msg_data VARCHAR2(240);
2036
2037 UNEXPECTED_ERROR EXCEPTION;
2038 BEGIN
2039
2040 x_error_msg := '';
2041 x_return_code := 'S';
2042 x_update_vr := 'N';
2043
2044 GMDRTVAL_PUB.Validate_Routing_VR_Dates(prouting_id => p_routing_id,
2045 x_msg_count => l_msg_count,
2046 x_msg_stack => l_msg_data,
2047 x_return_status => l_return_status);
2048
2049 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2050
2051 FND_MSG_PUB.GET(p_msg_index => 1,
2052 p_data => x_error_msg,
2053 p_encoded => 'F',
2054 p_msg_index_out => l_msg_index);
2055
2056 x_update_vr := 'Y';
2057 ELSE
2058 RAISE UNEXPECTED_ERROR;
2059 END IF;
2060
2061 EXCEPTION
2062 WHEN OTHERS THEN
2063 FND_MSG_PUB.GET(p_msg_index => 1,
2064 p_data => x_error_msg,
2065 p_encoded => 'F',
2066 p_msg_index_out => l_msg_index);
2067 x_return_code := 'F';
2068 x_update_vr := 'F';
2069
2070 END Validate_Routing_VR_Dates;
2071
2072 /* Api start of comments
2073 +============================================================================
2074 | PROCEDURE NAME
2075 | Update_VR_With_RT_Dates
2076 |
2077 | DESCRIPTION
2078 | Update validity rules with routing from/to dates
2079 |
2080 | INPUT PARAMETERS
2081 | p_routing_id NUMBER
2082 |
2083 | OUTPUT PARAMETERS
2084 | x_return_code VARCHAR2(1)
2085 | x_error_msg VARCHAR2(100)
2086 |
2087 | HISTORY
2088 | 24-JUL-2002 Eddie Oumerretane Created.
2089 |
2090 +=============================================================================
2091 Api end of comments
2092 */
2093 PROCEDURE Update_VR_With_RT_Dates ( p_routing_id IN VARCHAR2,
2094 x_return_code OUT NOCOPY VARCHAR2,
2095 x_error_msg OUT NOCOPY VARCHAR2) IS
2096
2097 l_return_status VARCHAR2(10);
2098 l_msg_count NUMBER;
2099 l_msg_index NUMBER;
2100 l_msg_data VARCHAR2(240);
2101
2102 UNEXPECTED_ERROR EXCEPTION;
2103
2104 BEGIN
2105
2106 x_error_msg := '';
2107 x_return_code := 'S';
2108
2109 GMDRTVAL_PUB.Update_VR_With_RT_Dates (prouting_id => p_routing_id,
2110 x_msg_count => l_msg_count,
2111 x_msg_stack => l_msg_data,
2112 x_return_status => l_return_status);
2113
2114 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2115 RAISE UNEXPECTED_ERROR;
2116 END IF;
2117
2118 EXCEPTION
2119 WHEN OTHERS THEN
2120 FND_MSG_PUB.GET(p_msg_index => 1,
2121 p_data => x_error_msg,
2122 p_encoded => 'F',
2123 p_msg_index_out => l_msg_index);
2124 x_return_code := 'F';
2125
2126 END Update_VR_With_RT_Dates;
2127
2128
2129 /* Api start of comments
2130 +============================================================================
2131 | PROCEDURE NAME
2132 | Get_Theoretical_Process_Loss
2133 |
2134 | DESCRIPTION
2135 | Retrieve theoretical process loss
2136 |
2137 | INPUT PARAMETERS
2138 | p_routing_qty NUMBER
2139 | p_routing_um VARCHAR2
2140 | p_routing_class VARCHAR2
2141 |
2142 | OUTPUT PARAMETERS
2143 | x_theoretical_loss VARCHAR2(1)
2144 | x_return_code VARCHAR2(1)
2145 | x_error_msg VARCHAR2(100)
2146 |
2147 | HISTORY
2148 | 02-AUG-2002 Eddie Oumerretane Created.
2149 |
2150 +=============================================================================
2151 Api end of comments
2152 */
2153 PROCEDURE Get_Theoretical_Process_Loss (p_routing_qty IN NUMBER,
2154 p_routing_um IN VARCHAR2,
2155 p_routing_class IN VARCHAR2,
2156 x_theoretical_loss OUT NOCOPY NUMBER,
2157 x_return_code OUT NOCOPY VARCHAR2,
2158 x_error_msg OUT NOCOPY VARCHAR2) IS
2159
2160 -- Bug# 5206623 Kapil M
2161 -- Changed the cursor to fetch records from new R12 tables
2162 CURSOR Cur_uom_type (p_um_code VARCHAR2) IS
2163 SELECT UOM_CLASS
2164 FROM mtl_units_of_measure
2165 WHERE UOM_CODE =p_um_code;
2166
2167 CURSOR Cur_Rtg_Class_Um IS
2168 SELECT ROUTING_CLASS_UOM
2169 FROM gmd_routing_class
2170 WHERE routing_class = p_routing_class;
2171
2172 l_routing_class_um VARCHAR2(4);
2173 l_routing_class_um_type VARCHAR2(10);
2174 l_routing_um_type VARCHAR2(10);
2175 l_new_qty NUMBER;
2176 UOM_TYPE_ERROR EXCEPTION;
2177
2178 BEGIN
2179
2180 x_error_msg := '';
2181 x_return_code := 'S';
2182 x_theoretical_loss := -1;
2183
2184 OPEN Cur_Rtg_Class_Um;
2185 FETCH Cur_Rtg_Class_Um INTO l_routing_class_um;
2186 CLOSE Cur_Rtg_Class_Um;
2187
2188
2189 IF (p_routing_class IS NOT NULL AND
2190 l_routing_class_um IS NOT NULL AND
2191 p_routing_qty IS NOT NULL AND
2192 p_routing_um IS NOT NULL) THEN
2193
2194 OPEN Cur_uom_type (l_routing_class_um);
2195 FETCH Cur_uom_type INTO l_routing_class_um_type;
2196 CLOSE Cur_uom_type;
2197
2198 OPEN Cur_uom_type (p_routing_um);
2199 FETCH Cur_uom_type INTO l_routing_um_type;
2200 CLOSE Cur_uom_type;
2201
2202 IF (l_routing_um_type = l_routing_class_um_type) THEN
2203 l_new_qty := INV_CONVERT.inv_um_convert( item_id => 0
2204 ,precision => 5
2205 ,from_quantity => p_routing_qty
2206 ,from_unit => p_routing_um
2207 ,to_unit => l_routing_class_um
2208 ,from_name => NULL
2209 ,to_name => NULL);
2210 /*GMICUOM.icuomcv(0,
2211 0,
2212 p_routing_qty,
2213 p_routing_um,
2214 l_routing_class_um,
2215 l_new_qty);
2216 */
2217
2218 x_theoretical_loss :=
2219 GMDRTVAL_PUB.Get_Theoretical_Process_Loss(p_routing_class,
2220 l_new_qty);
2221 ELSE
2222 RAISE UOM_TYPE_ERROR;
2223 END IF;
2224
2225 END IF;
2226
2227 EXCEPTION
2228 WHEN UOM_TYPE_ERROR THEN
2229 FND_MESSAGE.SET_NAME('GMD', 'GMD_RTG_CLS_VS_RTG_UM_TYPE');
2230 x_return_code := 'F';
2231 x_error_msg := FND_MESSAGE.GET;
2232
2233 WHEN OTHERS THEN
2234 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2235 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2236 x_return_code := 'F';
2237 x_error_msg := FND_MESSAGE.GET;
2238
2239 END Get_Theoretical_Process_Loss;
2240
2241
2242 /* Api start of comments
2243 +============================================================================
2244 | PROCEDURE NAME
2245 | Delete_Routing
2246 |
2247 | DESCRIPTION
2248 | Delete routing header
2249 |
2250 | INPUT PARAMETERS
2251 | p_routing_id IN NUMBER
2252 | p_last_update_date_orig IN DATE
2253 |
2254 | OUTPUT PARAMETERS
2255 | x_return_code VARCHAR2(1)
2256 | x_error_msg VARCHAR2(100)
2257 |
2258 | HISTORY
2259 | 14-AUG-2002 Eddie Oumerretane Created.
2260 |
2261 +=============================================================================
2262 Api end of comments
2263 */
2264 PROCEDURE Delete_Routing ( p_routing_id IN NUMBER,
2265 p_last_update_date_orig IN DATE,
2266 x_return_code OUT NOCOPY VARCHAR2,
2267 x_error_msg OUT NOCOPY VARCHAR2) IS
2268
2269 CURSOR Cur_get_routing IS
2270 SELECT *
2271 FROM gmd_routings
2272 WHERE routing_id = p_routing_id AND
2273 last_update_date = p_last_update_date_orig;
2274
2275 DELETE_ROUTING_EXCEPTION EXCEPTION;
2276 RECORD_CHANGED_EXCEPTION EXCEPTION;
2277 l_rec Cur_get_routing%ROWTYPE;
2278 l_return_status VARCHAR2(2);
2279 l_message_count NUMBER;
2280 l_message_list VARCHAR2(2000);
2281 l_message VARCHAR2(1000);
2282 l_dummy NUMBER;
2283
2284 BEGIN
2285
2286 x_error_msg := '';
2287 x_return_code := FND_API.G_RET_STS_SUCCESS;
2288
2289
2290 OPEN Cur_get_routing;
2291 FETCH Cur_get_routing INTO l_rec;
2292
2293 IF Cur_get_routing%NOTFOUND THEN
2294 CLOSE Cur_get_routing;
2295 RAISE RECORD_CHANGED_EXCEPTION;
2296 END IF;
2297
2298 CLOSE Cur_get_routing;
2299
2300 GMD_ROUTINGS_PUB.delete_routing
2301 ( p_api_version => 1
2302 , p_init_msg_list => TRUE
2303 , p_commit => TRUE
2304 , p_routing_id => p_routing_id
2305 , p_routing_no => NULL
2306 , p_routing_vers => NULL
2307 , x_message_count => l_message_count
2308 , x_message_list => l_message_list
2309 , x_return_status => l_return_status);
2310
2311 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2312 RAISE DELETE_ROUTING_EXCEPTION;
2313 END IF;
2314
2315 --- This should be removed when the API does the commit
2316 COMMIT;
2317
2318 EXCEPTION
2319 WHEN DELETE_ROUTING_EXCEPTION THEN
2320 FND_MSG_PUB.GET( p_msg_index => 1,
2321 p_data => l_message,
2322 p_encoded => 'F',
2323 p_msg_index_out => l_dummy);
2324
2325 x_return_code := 'F';
2326 x_error_msg := l_message;
2327 WHEN RECORD_CHANGED_EXCEPTION THEN
2328 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
2329 x_return_code := 'F';
2330 x_error_msg := FND_MESSAGE.GET;
2331
2332 WHEN OTHERS THEN
2333 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2334 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2335 x_return_code := 'F';
2336 x_error_msg := FND_MESSAGE.GET;
2337
2338
2339 END Delete_Routing;
2340
2341 /* Api start of comments
2342 +============================================================================
2343 | PROCEDURE NAME
2344 | Undelete_Routing
2345 |
2346 | DESCRIPTION
2347 | Unelete routing header
2348 |
2349 | INPUT PARAMETERS
2350 | p_routing_id IN NUMBER
2351 | p_last_update_date_orig IN DATE
2352 |
2353 | OUTPUT PARAMETERS
2354 | x_return_code VARCHAR2(1)
2355 | x_error_msg VARCHAR2(100)
2356 |
2357 | HISTORY
2358 | 14-AUG-2002 Eddie Oumerretane Created.
2359 |
2360 +=============================================================================
2361 Api end of comments
2362 */
2363 PROCEDURE Undelete_Routing ( p_routing_id IN NUMBER,
2364 p_last_update_date_orig IN DATE,
2365 x_return_code OUT NOCOPY VARCHAR2,
2366 x_error_msg OUT NOCOPY VARCHAR2) IS
2367
2368 CURSOR Cur_get_routing IS
2369 SELECT *
2370 FROM gmd_routings
2371 WHERE routing_id = p_routing_id AND
2372 last_update_date = p_last_update_date_orig;
2373
2374 UNDELETE_ROUTING_EXCEPTION EXCEPTION;
2375 RECORD_CHANGED_EXCEPTION EXCEPTION;
2376 l_rec Cur_get_routing%ROWTYPE;
2377 l_return_status VARCHAR2(2);
2378 l_message_count NUMBER;
2379 l_message_list VARCHAR2(2000);
2380 l_message VARCHAR2(1000);
2381 l_dummy NUMBER;
2382
2383 BEGIN
2384
2385 x_error_msg := '';
2386 x_return_code := FND_API.G_RET_STS_SUCCESS;
2387
2388 OPEN Cur_get_routing;
2389 FETCH Cur_get_routing INTO l_rec;
2390
2391 IF Cur_get_routing%NOTFOUND THEN
2392 CLOSE Cur_get_routing;
2393 RAISE RECORD_CHANGED_EXCEPTION;
2394 END IF;
2395 CLOSE Cur_get_routing;
2396
2397 GMD_ROUTINGS_PUB.undelete_routing
2398 ( p_api_version => 1
2399 , p_init_msg_list => TRUE
2400 , p_commit => TRUE
2401 , p_routing_id => p_routing_id
2402 , p_routing_no => NULL
2403 , p_routing_vers => NULL
2404 , x_message_count => l_message_count
2405 , x_message_list => l_message_list
2406 , x_return_status => l_return_status);
2407
2408 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2409 RAISE UNDELETE_ROUTING_EXCEPTION;
2410 END IF;
2411
2412 --- This should be removed when the API does the commit
2413 COMMIT;
2414
2415 EXCEPTION
2416 WHEN UNDELETE_ROUTING_EXCEPTION THEN
2417 FND_MSG_PUB.GET( p_msg_index => 1,
2418 p_data => l_message,
2419 p_encoded => 'F',
2420 p_msg_index_out => l_dummy);
2421
2422 x_return_code := 'F';
2423 x_error_msg := l_message;
2424 WHEN RECORD_CHANGED_EXCEPTION THEN
2425 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
2426 x_return_code := 'F';
2427 x_error_msg := FND_MESSAGE.GET;
2428
2429 WHEN OTHERS THEN
2430 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2431 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2432 x_return_code := 'F';
2433 x_error_msg := FND_MESSAGE.GET;
2434
2435
2436 END Undelete_Routing;
2437
2438 /* Api start of comments
2439 +============================================================================
2440 | PROCEDURE NAME
2441 | CHECK_ROUT_ORGN_ACCESS
2442 |
2443 | DESCRIPTION
2444 | Procedure to chk if user has accesss to the Rout Orgn.
2445 |
2446 | INPUT PARAMETERS
2447 | p_routing_id NUMBER
2448 |
2449 | OUTPUT PARAMETERS
2450 | x_return_code VARCHAR2
2451 |
2452 | HISTORY
2453 | 23-SEP-2004 S.Sriram Created for Routing Security Build
2454 |
2455 +=============================================================================
2456 Api end of comments
2457 */
2458 PROCEDURE CHECK_ROUT_ORGN_ACCESS(p_routing_id IN NUMBER,
2459 x_return_code OUT NOCOPY VARCHAR2) IS
2460
2461 CURSOR Cur_get_rout_orgn IS
2462 SELECT owner_organization_id
2463 FROM gmd_routings_b
2464 WHERE routing_id = p_routing_id;
2465
2466 l_orgn_id NUMBER;
2467 l_return_status VARCHAR2(10);
2468
2469 BEGIN
2470
2471 OPEN Cur_get_rout_orgn;
2472 FETCH Cur_get_rout_orgn INTO l_orgn_id;
2473 CLOSE Cur_get_rout_orgn;
2474
2475
2476 IF (l_orgn_id IS NOT NULL) THEN
2477 IF (GMD_API_GRP.setup AND GMD_API_GRP.OrgnAccessible(l_orgn_id) ) THEN
2478 x_return_code := 'S';
2479 ELSE
2480 x_return_code := 'F';
2481 END IF;
2482 ELSE
2483 x_return_code := 'S';
2484 END IF;
2485
2486 EXCEPTION
2487 WHEN OTHERS THEN
2488 x_return_code := FND_API.G_RET_STS_UNEXP_ERROR;
2489
2490 END CHECK_ROUT_ORGN_ACCESS;
2491
2492
2493 /* Api start of comments
2494 +============================================================================
2495 | PROCEDURE NAME
2496 | Get_Label_name
2497 |
2498 | DESCRIPTION
2499 | Get the value of the given profile option
2500 |
2501 | INPUT PARAMETERS
2502 | p_message_name VARCHAR2(100)
2503 | OUTPUT PARAMETERS
2504 | x_message_text VARCHAR2(240)
2505 |
2506 | HISTORY
2507 | 18-JUL-2005 Shyam S Created.
2508 |
2509 +=============================================================================
2510 Api end of comments
2511 */
2512 PROCEDURE Get_label_name (p_message_name IN VARCHAR2
2513 ,x_message_text OUT NOCOPY VARCHAR2) IS
2514
2515 BEGIN
2516 FND_MESSAGE.SET_NAME('GMD', p_message_name);
2517 x_message_text := FND_MESSAGE.GET;
2518 END Get_label_name;
2519
2520
2521
2522 END GMD_ROUTING_DESIGNER_PKG;