[Home] [Help]
PACKAGE BODY: APPS.BOM_RTG_VAL_TO_ID
Source
1 PACKAGE BODY BOM_RTG_Val_To_Id AS
2 /* $Header: BOMRVIDB.pls 120.1 2005/11/16 22:58:12 bbpatel noship $*/
3 /****************************************************************************
4 --
5 -- Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMRVIDB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_RTG_Val_To_Id
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 04-AUG-00 Biao Zhang Initial Creation
21 -- 07-SEP-00 Masanori Kimizuka Modified to support ECO for Routing
22 --
23 ****************************************************************************/
24 G_Pkg_Name VARCHAR2(30) := 'RTG_Val_To_Id';
25 g_token_tbl Error_Handler.Token_Tbl_Type;
26
27
28 /********************************************************************
29 * Function : Organization
30 * Returns : NUMBER
31 * Purpose : Will convert the value of organization_code to
32 * organization_id using MTL_PARAMETERS.
33 * If the conversion fails then the function will return
34 * a NULL otherwise will return the org_id.
35 * For an unexpected error function will return a
36 * missing value.
37 *********************************************************************/
38 FUNCTION Organization
39 ( p_organization IN VARCHAR2
40 , x_err_text IN OUT NOCOPY VARCHAR2) RETURN NUMBER
41 IS
42 l_id NUMBER;
43 ret_code NUMBER;
44 l_err_text VARCHAR2(2000);
45 BEGIN
46 SELECT organization_id
47 INTO l_id
48 FROM mtl_parameters
49 WHERE organization_code = p_organization;
50
51 RETURN l_id;
52
53 EXCEPTION
54
55 WHEN NO_DATA_FOUND THEN
56 RETURN NULL;
57
58 WHEN OTHERS THEN
59 RETURN FND_API.G_MISS_NUM;
60
61 END Organization;
62
63
64 /**********************************************************************
65 * Function : Revised_Item
66 * Parameters IN : Revised Item Name
67 * Organization ID
68 * Parameters OUT: Error_Text
69 * Returns : Revised Item Id
70 * Purpose : This function will get the ID for the revised item and
71 * return the ID. If the revised item is invalid then the
72 * ID will returned as NULL.
73 **********************************************************************/
74 FUNCTION Revised_Item( p_revised_item_num IN VARCHAR2,
75 p_organization_id IN NUMBER,
76 x_err_text IN OUT NOCOPY VARCHAR2 )
77 RETURN NUMBER
78 IS
79 l_id NUMBER;
80 ret_code NUMBER;
81 l_err_text VARCHAR2(2000);
82 BEGIN
83
84 /* Bug 4040340. Using mtl_system_items_b_kfv to get the item id
85 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
86 org_id => p_organization_id,
87 flex_code => 'MSTK',
88 flex_name => p_revised_item_num,
89 flex_id => l_id,
90 set_id => -1,
91 err_text => x_err_text);
92
93 IF (ret_code <> 0) THEN
94 RETURN NULL;
95 ELSE
96 RETURN l_id;
97 END IF;*/
98
99 SELECT inventory_item_id
100 INTO l_id
101 FROM mtl_system_items_b_kfv
102 WHERE organization_id = p_organization_id
103 and concatenated_segments = p_revised_item_num;
104
105 RETURN l_id;
106
107 EXCEPTION
108
109 WHEN NO_DATA_FOUND THEN
110 RETURN NULL;
111
112 WHEN OTHERS THEN
113 RETURN FND_API.G_MISS_NUM;
114
115 END Revised_Item;
116
117
118 /********************************************************************
119 * Function : Assembly_Item
120 * Returns : Number
121 * Parameters IN : Assembly Item Name
122 * Organization_Id
123 * Purpose : This function will get ID for the assembly item and
124 * return the ID. If the assembly item is invalid then
125 * ID will returned as NULL.
126 *********************************************************************/
127 FUNCTION Assembly_Item
128 ( p_assembly_item_name IN VARCHAR2
129 , p_organization_id IN NUMBER
130 , x_err_text IN OUT NOCOPY VARCHAR2) RETURN NUMBER
131 IS
132 BEGIN
133 RETURN Bom_Rtg_Val_To_Id.Revised_Item
134 ( p_revised_item_num => p_assembly_item_name
135 , p_organization_id => p_organization_id
136 , x_err_text => x_err_text
137 );
138
139 END Assembly_Item;
140
141 /*******************************************************************
142 * Function : Common_Assembly_Item_Id
143 * Parameters IN : Common_assembly_item_name
144 * Organization ID
145 * Parameters OUT: Error Message
146 * Returns : Component_Item_Id
147 * Purpose : Function will convert the common assembly item name
148 * to its correspondent ID and return the value.
149 * If the component is invalid, then a NULL is returned.
150 *********************************************************************/
151 FUNCTION COMMON_ASSEMBLY_ITEM_ID
152 ( p_organization_id IN NUMBER,
153 p_common_assembly_item_name IN VARCHAR2,
154 x_err_text IN OUT NOCOPY VARCHAR2)
155
156 RETURN NUMBER
157 IS
158 l_id NUMBER;
159 ret_code NUMBER;
160 BEGIN
161
162 RETURN Bom_Rtg_Val_To_Id.Revised_Item
163 ( p_revised_item_num => p_common_assembly_item_name
164 , p_organization_id => p_organization_id
165 , x_err_text => x_err_text
166 );
167
168 END COMMON_ASSEMBLY_ITEM_ID;
169
170 /********************************************************************
171 * Function : Routing_Sequence_id
172 * Returns : Number
173 * Parameters IN : Assemby_Item_Id
174 * Organization_Id
175 * Alternate_routing_Code
176 * Parameters OUT: Error Text
177 * Purpose : Function will use the input parameters to find the
178 * routing sequence_id and return a NULL if an error
179 * occured or the routing sequence_id could not be
180 * obtained.
181 ********************************************************************/
182 FUNCTION Routing_Sequence_id
183 ( p_assembly_item_id IN NUMBER
184 , p_organization_id IN NUMBER
185 , p_alternate_routing_designator IN VARCHAR2
186 , x_err_text IN OUT NOCOPY VARCHAR2
187 ) RETURN NUMBER
188 IS
189 l_id NUMBER;
190 l_cfm_flag NUMBER;
191 l_com_rtg_seq_id NUMBER;
192 ret_code NUMBER;
193 l_err_text VARCHAR2(2000);
194
195 BEGIN
196 SELECT routing_sequence_id
197 , cfm_routing_flag
198 , common_routing_sequence_id
199 INTO l_id, l_cfm_flag, l_com_rtg_seq_id
200 FROM bom_operational_routings
201 WHERE organization_id = p_organization_id
202 AND assembly_item_id = p_assembly_item_id
203 AND NVL(alternate_routing_designator, FND_API.G_MISS_CHAR) =
204 NVL(p_alternate_routing_designator, FND_API.G_MISS_CHAR);
205
206 BOM_Rtg_Globals.Set_CFM_Rtg_Flag(p_cfm_rtg_type => l_cfm_flag) ;
207 BOM_Rtg_Globals.Set_Common_Rtg_Seq_id
208 ( p_common_Rtg_seq_id => l_com_rtg_seq_id );
209
210 RETURN l_id;
211
212
213 EXCEPTION
214
215 WHEN NO_DATA_FOUND THEN
216 RETURN NULL;
217
218 WHEN OTHERS THEN
219 RETURN FND_API.G_MISS_NUM;
220
221 END Routing_Sequence_id ;
222
223 /********************************************************************
224 * Function : Common_Routing_Sequence_id
225 * Returns : Number
226 * Parameters IN : Common_Assemby_Item_Id
227 * Organization_Id
228 * Alternate_routing_Code
229 * Parameters OUT: Error Text
230 * Purpose : Function will use the input parameters to find the
231 * common routing sequence_id and return a NULL if an
232 * error occured or the routing sequence_id could not be
233 * obtained.
234 ********************************************************************/
235 FUNCTION Common_Routing_Sequence_id
236 ( p_common_assembly_item_id IN NUMBER
237 , p_organization_id IN NUMBER
238 , p_alternate_routing_designator IN VARCHAR2
239 , x_err_text IN OUT NOCOPY VARCHAR2
240 ) RETURN NUMBER
241 IS
242 l_common_rtg_seq_id NUMBER;
243 BEGIN
244 l_common_rtg_seq_id := Bom_Rtg_Val_To_Id.Routing_Sequence_id
245 ( p_assembly_item_id => p_common_assembly_item_id
246 , p_organization_id => p_organization_id
247 , p_alternate_routing_designator => p_alternate_routing_designator
248 , x_err_text => x_err_text
249 ) ;
250
251 BOM_Rtg_Globals.Set_Common_Rtg_Seq_id
252 ( p_common_Rtg_seq_id => l_common_rtg_seq_id );
253 RETURN l_common_rtg_seq_id;
254 END Common_Routing_Sequence_id;
255
256
257
258 /*************************************************************
259 * Function : RtgAndRevItemSeq
260 * Parameters IN : Revised Item Unique Key information
261 * Parameters OUT: Routing Sequence ID
262 * Returns : Revised Item Sequence
263 * Purpose : Will use the revised item information to find the bill
264 * sequence and the revised item sequence.
265 * History : Added p_new_routing_revsion and
266 * p_from_end_item_number in argument by MK
267 * on 11/02/00
268 * Comment Out to resolve ECO depencency and move to ENGSVIDB.pls
269 * by MK on 12/04/00
270 *
271 FUNCTION RtgAndRevItemSeq( p_revised_item_id IN NUMBER
272 , p_item_revision IN VARCHAR2
273 , p_effective_date IN DATE
274 , p_change_notice IN VARCHAR2
275 , p_organization_id IN NUMBER
276 , p_new_routing_revision IN VARCHAR2
277 , p_from_end_item_number IN VARCHAR2 := NULL
278 , x_routing_sequence_id IN OUT NOCOPY NUMBER
279 )
280 RETURN NUMBER
281 IS
282 l_Rev_Item_Seq NUMBER;
283 BEGIN
284 -- Modified by MK on 11/02/00
285 SELECT routing_sequence_id, revised_item_Sequence_id
286 INTO x_routing_sequence_id, l_Rev_Item_Seq
287 FROM eng_revised_items
288 WHERE NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR )
289 = NVL(p_from_end_item_number, FND_API.G_MISS_CHAR)
290 AND NVL(new_routing_revision, FND_API.G_MISS_CHAR) =
291 NVL(p_new_routing_revision, FND_API.G_MISS_CHAR)
292 AND NVL(new_item_revision,FND_API.G_MISS_CHAR)= NVL(p_item_revision,FND_API.G_MISS_CHAR)
293 AND scheduled_date = p_effective_date
294 -- AND TRUNC(scheduled_date) = TRUNC(p_effective_date) -- time
295 AND change_notice = p_change_notice
296 AND organization_id = p_organization_id
297 AND revised_item_id = p_revised_item_id ;
298
299 RETURN l_Rev_Item_Seq;
300
301 EXCEPTION
302 WHEN OTHERS THEN
303 x_routing_sequence_id := NULL;
304 RETURN NULL;
305 END RtgAndRevItemSeq;
306
307 **************************************************************/
308
309 /********************************************************************
310 * Function : Completion_Locator_id
311 * Returns : Number
312 * Parameters IN : Completion_Name
313 * Organization_Id
314 * Parameters OUT: Error Text
315 * Purpose : Function will use the input parameters to find the
316 * Completion Locator ID return a NULL if an error
317 * occured or the completion id could not be
318 * obtained.
319 ********************************************************************/
320 FUNCTION Completion_locator_id
321 ( p_completion_location_name IN VARCHAR2
322 , p_organization_id IN NUMBER
323 , x_err_text IN OUT NOCOPY VARCHAR2) RETURN NUMBER
324 IS
325 supply_locator_id NUMBER;
326 ret_code NUMBER;
327 l_err_text VARCHAR2(240);
328 BEGIN
329 /* Bug 4040340. Using mtl_item_locations_kfv to get the locator id.
330 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
331 org_id => p_organization_id,
332 flex_code => 'MTLL',
333 flex_name => p_completion_location_name,
334 flex_id => supply_locator_id,
335 set_id => -1,
336 err_text => l_err_text);
337
338 IF (ret_code <> 0) THEN
339 RETURN NULL;
340 ELSE
341 RETURN supply_locator_id;
342 END IF; */
343
344 SELECT inventory_location_id
345 INTO supply_locator_id
346 FROM mtl_item_locations_kfv
347 WHERE organization_id = p_organization_id
348 and concatenated_segments = p_completion_location_name;
349
350 RETURN supply_locator_id;
351
352 EXCEPTION
353
354 WHEN NO_DATA_FOUND THEN
355 RETURN NULL;
356
357 WHEN OTHERS THEN
358 RETURN FND_API.G_MISS_NUM;
359
360 END Completion_locator_id;
361
362 /********************************************************************
363 * Function : Line_Id
364 * Returns : Number
365 * Parameters IN : line_code
366 * Organization_Id
367 * Parameters OUT: Error Text
368 * Purpose : Function will use the input parameters to find the
369 * line id and return a NULL if an error
370 * occured or the line id could not be obtained.
371 ********************************************************************/
372 FUNCTION Line_Id
373 ( p_line_code IN VARCHAR2
374 , p_organization_id IN NUMBER
375 , x_err_text IN OUT NOCOPY VARCHAR2
376 )
377 RETURN NUMBER
378 IS
379 l_id NUMBER;
380 ret_code NUMBER;
381 l_err_text VARCHAR2(2000);
382
383 BEGIN
384 SELECT line_id
385 INTO l_id
386 FROM wip_lines
387 WHERE organization_id = p_organization_id
388 AND line_code = p_line_code;
389
390 RETURN l_id;
391
392 EXCEPTION
393
394 WHEN NO_DATA_FOUND THEN
395 RETURN NULL;
396
397 WHEN OTHERS THEN
398 RETURN FND_API.G_MISS_NUM;
399 END Line_Id;
400
401 /********************************************************************
402 * Function : Standard_Operation_id for ECO BO
403 * Returns : Number
404 * Parameters IN : operation_type_code
405 * standard_operation_code
406 * Organization_Id
407 * Line_Id
408 * Parameters OUT: Error Text
409 * Purpose : Function will use the input parameters to find the
410 * Standard operation id and return a NULL if an error
411 * occured or the standard operation id could not be
412 * obtained.
413 ********************************************************************/
414 FUNCTION Standard_Operation_Id
415 ( p_operation_type IN NUMBER
416 , p_standard_operation_code IN VARCHAR2
417 , p_organization_id IN NUMBER
418 , p_rev_item_sequence_id IN NUMBER
419 , x_err_text IN OUT NOCOPY VARCHAR2
420 ) RETURN NUMBER
421 IS
422 l_id NUMBER;
423 ret_code NUMBER;
424 l_err_text VARCHAR2(2000);
425
426 BEGIN
427
428 SELECT standard_operation_id
429 INTO l_id
430 FROM bom_standard_operations bso
431 -- , eng_revised_items eri
432 WHERE NVL(bso.operation_type, 1)
433 = DECODE(p_operation_type, FND_API.G_MISS_NUM, 1
434 , NVL(p_operation_type, 1 ) )
435 -- AND NVL(bso.line_id, FND_API.G_MISS_NUM)
436 -- = NVL(eri.line_id, FND_API.G_MISS_NUM)
437 -- AND eri.revised_item_sequence_id = p_rev_item_sequence_id
438 AND NVL(bso.line_id, FND_API.G_MISS_NUM ) = FND_API.G_MISS_NUM
439 AND bso.organization_id = p_organization_id
440 AND bso.operation_code = p_standard_operation_code ;
441
442
443 RETURN l_id;
444
445 EXCEPTION
446
447 WHEN NO_DATA_FOUND THEN
448 RETURN NULL;
449
450 WHEN OTHERS THEN
451 RETURN FND_API.G_MISS_NUM;
452 END Standard_Operation_Id;
453
454
455 /********************************************************************
456 * Function : Standard_Operation_Id
457 * Returns : Number
458 * Parameters IN : operation_type_code
459 * standard_operation_code
460 * Organization_Id
461 * Line_Id
462 * Parameters OUT: Error Text
463 * Purpose : Function will use the input parameters to find the
464 * Standard operation id and return a NULL if an error
465 * occured or the standard operation id could not be
466 * obtained.
467 ********************************************************************/
468 FUNCTION Standard_Operation_Id
469 ( p_operation_type IN NUMBER
470 , p_standard_operation_code IN VARCHAR2
471 , p_organization_id IN NUMBER
472 , p_routing_sequence_id IN NUMBER
473 , x_err_text IN OUT NOCOPY VARCHAR2
474 ) RETURN NUMBER
475 IS
476 l_id NUMBER;
477 ret_code NUMBER;
478 l_err_text VARCHAR2(2000);
479
480 BEGIN
481
482 SELECT standard_operation_id
483 INTO l_id
484 FROM bom_standard_operations bso
485 , bom_operational_routings bor
486 WHERE NVL(bso.operation_type,1 )
487 = DECODE(p_operation_type, FND_API.G_MISS_NUM, 1
488 , NVL(p_operation_type, 1))
489 AND NVL(bso.line_id, FND_API.G_MISS_NUM)
490 = NVL(bor.line_id, FND_API.G_MISS_NUM)
491 AND bor.routing_sequence_id = p_routing_sequence_id
492 AND bso.organization_id = p_organization_id
493 AND bso.operation_code = p_standard_operation_code ;
494
495 RETURN l_id;
496
497 EXCEPTION
498
499 WHEN NO_DATA_FOUND THEN
500 RETURN NULL;
501
502 WHEN OTHERS THEN
503 RETURN FND_API.G_MISS_NUM;
504 END Standard_Operation_Id;
505
506 /********************************************************************
507 * Function : Department id
508 * Returns : Number
509 * Parameters IN : department_code
510 * Organization_Id
511 * Parameters OUT: Error Text
512 * Purpose : Function will use the input parameters to find the
513 * department id and return a NULL if an error
514 * occured or the department id could not be
515 * obtained.
516 ********************************************************************/
517 FUNCTION Department_Id
518 ( p_department_code IN VARCHAR2
519 , p_organization_id IN NUMBER
520 , x_err_text IN OUT NOCOPY VARCHAR2
521 ) RETURN NUMBER
522 IS
523 l_id NUMBER;
524 BEGIN
525 SELECT department_id
526 INTO l_id
527 FROM bom_departments
528 WHERE organization_id = p_organization_id
529 AND department_code = p_department_code;
530
531 RETURN l_id;
532
533 EXCEPTION
534
535 WHEN NO_DATA_FOUND THEN
536 RETURN NULL;
537
538 WHEN OTHERS THEN
539 RETURN FND_API.G_MISS_NUM;
540 END Department_Id;
541
542 /********************************************************************
543 * Function : Process_Op_Seq_Id
544 * Returns : Number
545 * Parameters IN : Process_code
546 * Process_seq_number
547 * Alternate_routing_Code
548 * Parameters OUT: Error Text
549 * Purpose : Function will use the input parameters to find the
550 * process operation id and return a NULL if an error
551 * occured or the process operation id could not be
552 * obtained.
553 ********************************************************************/
554 FUNCTION Process_Op_Seq_Id
555 ( p_process_code IN VARCHAR2
556 , p_organization_id IN NUMBER
557 , p_process_seq_number IN NUMBER
558 , p_routing_sequence_id IN NUMBER
559 , x_err_text IN OUT NOCOPY VARCHAR2
560 ) RETURN NUMBER
561 IS
562 l_id NUMBER;
563 BEGIN
564 IF p_process_code IS NULL THEN -- Added for bug 2758481
565 SELECT bos.operation_sequence_id
566 INTO l_id
567 FROM BOM_OPERATION_SEQUENCES bos
568 , BOM_OPERATIONAL_ROUTINGS bor
569 , BOM_STANDARD_OPERATIONS bso
570 WHERE bso.organization_id = p_organization_id
571 AND bso.standard_operation_id = bos.standard_operation_id
572 AND bos.operation_seq_num = p_process_seq_number
573 AND bos.operation_type = 2 -- Operation Type : Process
574 AND bos.routing_sequence_id = bor.common_routing_sequence_id
575 AND bor.routing_sequence_id = p_routing_sequence_id ;
576 ELSE
577 SELECT bos.operation_sequence_id
578 INTO l_id
579 FROM BOM_OPERATION_SEQUENCES bos
580 , BOM_OPERATIONAL_ROUTINGS bor
581 , BOM_STANDARD_OPERATIONS bso
582 WHERE bso.operation_code = p_process_code
583 AND bso.organization_id = p_organization_id
584 AND bso.standard_operation_id = bos.standard_operation_id
585 AND bos.operation_seq_num = p_process_seq_number
586 AND bos.operation_type = 2 -- Operation Type : Process
587 AND bos.routing_sequence_id = bor.common_routing_sequence_id
588 AND bor.routing_sequence_id = p_routing_sequence_id ;
589 END IF;
590
591 RETURN l_id;
592
593 EXCEPTION
594
595 WHEN NO_DATA_FOUND THEN
596 RETURN NULL;
597
598 WHEN OTHERS THEN
599 RETURN FND_API.G_MISS_NUM;
600
601 END Process_Op_Seq_Id;
602
603 /********************************************************************
604 * Function : Line_Op_Seq_Id
605 * Returns : Number
606 * Parameters IN : line_op_code
607 * line_seq_number
608 * Parameters OUT: Error Text
609 * Purpose : Function will use the input parameters to find the
610 * line operation sequence id and d return a NULL if
611 * an error oocured or the routing sequence_id could
612 * not be obtained.
613 ********************************************************************/
614 FUNCTION Line_Op_Seq_Id
615 ( p_line_code IN VARCHAR2
616 , p_organization_id IN NUMBER
617 , p_line_seq_number IN NUMBER
618 , p_routing_sequence_id IN NUMBER
619 , x_err_text IN OUT NOCOPY VARCHAR2
620 ) RETURN NUMBER
621 IS
622 l_id NUMBER;
623 BEGIN
624
625 /*
626 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
627 Error_Handler.Write_Debug('In Line Op Seq Id. . .');
628 Error_Handler.Write_Debug('Line OP Code '|| p_line_code );
629 Error_Handler.Write_Debug('Line OP Seq ' || to_char(p_line_seq_number ));
630 Error_Handler.Write_Debug('Org Id ' || to_char(p_organization_id) );
631 Error_Handler.Write_Debug('Rtg Id ' || to_char(p_routing_sequence_id) );
632 END IF;
633 */
634
635 IF p_line_code IS NULL THEN -- Added for bug 2758481
636 SELECT bos.operation_sequence_id
637 INTO l_id
638 FROM BOM_OPERATION_SEQUENCES bos
639 , BOM_OPERATIONAL_ROUTINGS bor
640 , BOM_STANDARD_OPERATIONS bso
641 WHERE bso.organization_id = p_organization_id
642 AND bso.standard_operation_id = bos.standard_operation_id
643 AND bos.operation_seq_num = p_line_seq_number
644 AND bos.operation_type = 3 -- Operation Type : Line Op
645 AND bos.routing_sequence_id = bor.common_routing_sequence_id
646 AND bor.routing_sequence_id = p_routing_sequence_id ;
647 ELSE
648 SELECT bos.operation_sequence_id
649 INTO l_id
650 FROM BOM_OPERATION_SEQUENCES bos
651 , BOM_OPERATIONAL_ROUTINGS bor
652 , BOM_STANDARD_OPERATIONS bso
653 WHERE bso.operation_code = p_line_code
654 AND bso.organization_id = p_organization_id
655 AND bso.standard_operation_id = bos.standard_operation_id
656 AND bos.operation_seq_num = p_line_seq_number
657 AND bos.operation_type = 3 -- Operation Type : Line Op
658 AND bos.routing_sequence_id = bor.common_routing_sequence_id
659 AND bor.routing_sequence_id = p_routing_sequence_id ;
660 END IF;
661
662 /*
663 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
664 Error_Handler.Write_Debug('Line Op Seq Id : '|| to_char(l_id) );
665 END IF ;
666 */
667 RETURN l_id;
668
669 EXCEPTION
670
671 WHEN NO_DATA_FOUND THEN
672 RETURN NULL;
673
674 WHEN OTHERS THEN
675 RETURN FND_API.G_MISS_NUM;
676
677 END Line_Op_Seq_Id;
678
679
680
681 /**************************************************************************
682 * Function : Old_Operation_Sequence
683 * Returns : NUMBER
684 * Purpose : Using the input parameters the function will retrieve the
685 * old operation sequence id of the operation and return.
686 * If the function fails to find a record then it will return
687 * a NULL value. In case of an unexpected error the function
688 * will return a missing value.
689 ****************************************************************************/
690 FUNCTION Old_Operation_Sequence
691 ( p_old_effective_date IN DATE
692 , p_old_op_seq_num IN NUMBER
693 , p_operation_type IN NUMBER
694 , p_routing_sequence_id IN NUMBER
695 )
696
697 RETURN NUMBER
698 IS
699 l_id NUMBER;
700 BEGIN
701
702 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
703 Error_Handler.Write_Debug('Old Operation: ' || to_char(p_old_op_seq_num));
704 Error_Handler.Write_Debug('Routing Sequence: ' || to_char(p_routing_sequence_id));
705 Error_Handler.Write_Debug('Old Effective: ' || to_char(p_old_effective_date));
706 END IF;
707
708 SELECT operation_sequence_id
709 INTO l_id
710 FROM BOM_OPERATION_SEQUENCES
711 WHERE NVL(operation_type, 1) = DECODE(p_operation_type,
712 FND_API.G_MISS_NUM, 1,
713 NVL(p_operation_type, 1)
714 )
715 AND routing_sequence_id = p_routing_sequence_id
716 AND effectivity_date = p_old_effective_date -- Changed for bug 2647027
717 -- /** time **/ AND TRUNC(effectivity_date) = TRUNC(p_old_effective_date)
718 AND operation_seq_num = p_old_op_seq_num;
719
720
721 RETURN l_id;
722
723 EXCEPTION
724
725 WHEN NO_DATA_FOUND THEN
726 RETURN NULL;
727
728 WHEN OTHERS THEN
729 RETURN FND_API.G_MISS_NUM;
730
731 END Old_Operation_Sequence;
732
733
734 /********************************************************************
735 * Function : Setup_Id
736 * Returns : Number
737 * Parameters IN : Setup_Type
738 * Organization_Id
739 * Parameters OUT: Error Text
740 * Purpose : Function will use the input parameters to find the
741 * setup id return a NULL if an error
742 * occured or the setup id could not be
743 * obtained.
744 ********************************************************************/
745 FUNCTION Setup_Id
746 ( p_setup_type IN VARCHAR2
747 , p_organization_id IN NUMBER
748 , x_err_text IN OUT NOCOPY VARCHAR2
749 ) RETURN NUMBER
750 IS
751 l_id NUMBER;
752 BEGIN
753 SELECT setup_id
754 INTO l_id
755 FROM bom_setup_types
756 WHERE organization_id = p_organization_id
757 AND setup_code = p_setup_type ;
758
759 RETURN l_id;
760
761 EXCEPTION
762
763 WHEN NO_DATA_FOUND THEN
764 RETURN NULL;
765
766 WHEN OTHERS THEN
767 RETURN FND_API.G_MISS_NUM;
768
769 END Setup_Id ;
770
771
772 /********************************************************************
773 * Function : Activity_Id
774 * Returns : Number
775 * Parameters IN : Activity
776 * Parameters OUT: Error Text
777 * Purpose : Function will use the input parameters to find the
778 * activity id return a NULL if an error
779 * occured or the activity id could not be
780 * obtained.
781 ********************************************************************/
782
783 FUNCTION Activity_Id
784 ( p_activity IN VARCHAR2
785 , p_organization_id IN NUMBER
786 , x_err_text IN OUT NOCOPY VARCHAR2
787 ) RETURN NUMBER
788 IS
789 l_id NUMBER;
790 BEGIN
791 SELECT activity_id
792 INTO l_id
793 FROM cst_activities
794 WHERE NVL(organization_id, p_organization_id ) = p_organization_id
795 AND activity = p_activity;
796
797 RETURN l_id;
798
799 EXCEPTION
800
801 WHEN NO_DATA_FOUND THEN
802 RETURN NULL;
803
804 WHEN OTHERS THEN
805 RETURN FND_API.G_MISS_NUM;
806
807 END Activity_Id;
808
809 /********************************************************************
810 * Function : Resource_Id
811 * Returns : Number
812 * Parameters IN : organization_id
813 * resource_code
814 * Parameters OUT: Error Text
815 * Purpose : Function will use the input parameters to find the
816 * resouce id and return a NULL if an error
817 * occured or the resorce id could not be
818 * obtained.
819 ********************************************************************/
820 FUNCTION Resource_Id
821 ( p_resource_code IN VARCHAR2
822 , p_organization_id IN NUMBER
823 , x_err_text IN OUT NOCOPY VARCHAR2
824 ) RETURN NUMBER
825 IS
826 l_id NUMBER;
827
828 BEGIN
829 SELECT resource_id
830 INTO l_id
831 FROM bom_resources
832 WHERE organization_id = p_organization_id
833 AND resource_code = p_resource_code;
834
835 RETURN l_id;
836
837 EXCEPTION
838
839 WHEN NO_DATA_FOUND THEN
840 RETURN NULL;
841
842 WHEN OTHERS THEN
843 RETURN FND_API.G_MISS_NUM;
844
845 END resource_id;
846
847 /********************************************************************
848 * Function : Operation_Sequence_Id
849 * Returns : Number
850 * Parameters IN : routing_sequence_id
851 * operation_type
852 * operation_seq_num
853 * effectivity_date
854 * Parameters OUT: Error Text
855 * Purpose : Function will use the input parameters to find the
856 * Operation_Sequecne_Id and return a NULL if an error
857 * occured or the resorce id could not be
858 * obtained.
859 ********************************************************************/
860 FUNCTION Operation_Sequence_Id
861 ( p_routing_sequence_id IN NUMBER
862 , p_operation_type IN NUMBER
863 , p_operation_seq_num IN NUMBER
864 , p_effectivity_date IN DATE
865 , x_err_text IN OUT NOCOPY VARCHAR2
866 ) RETURN NUMBER
867 IS
868 l_id NUMBER;
869 l_bo_id VARCHAR2(3) ;
870
871 BEGIN
872 l_bo_id := BOM_Rtg_Globals.Get_Bo_Identifier ;
873
874 SELECT operation_sequence_id
875 INTO l_id
876 FROM bom_operation_sequences
877 WHERE (( l_bo_id = BOM_Rtg_Globals.G_ECO_BO
878 AND implementation_date IS NULL )
879 OR (l_bo_id = BOM_Rtg_Globals.G_RTG_BO
880 AND implementation_date IS NOT NULL )
881 )
882 -- NVL check in operation type included for bug 3293381
883 AND (( NVL(operation_type, 1) = 1 AND
884 effectivity_date = p_effectivity_date) -- Changed for bug 2647027
885 -- /** time **/ TRUNC(effectivity_date) = TRUNC(p_effectivity_date))
886 OR p_operation_type IN (2, 3)
887 )
888 AND NVL(operation_type, 1 ) = DECODE(p_operation_type,
889 FND_API.G_MISS_NUM, 1,
890 NVL(p_operation_type, 1 ) )
891 AND operation_seq_num = p_operation_seq_num
892 AND routing_sequence_id = p_routing_sequence_id ;
893
894 RETURN l_id;
895
896 EXCEPTION
897
898 WHEN NO_DATA_FOUND THEN
899 RETURN NULL;
900
901 WHEN OTHERS THEN
902 RETURN FND_API.G_MISS_NUM;
903
904
905 END Operation_Sequence_Id;
906
907 /*********************************************************************
908 * Procedure : RTG_Header_UUI_To_UI
909 * Returns : None
910 * Parameters IN : Routing header Record
911 * Routing header Unexposed Record
912 * Parameters OUT: Routing header unexposed record
913 * Message Token Table
914 * Return Status
915 * Purpose : This procedure will perform all the required
916 * User unique to Unique index conversions for routing
917 * header. Any errors will be logged in the Message
918 * table and a return satus of success or failure will be
919 * returned to the calling program.
920 *********************************************************************/
921 PROCEDURE RTG_Header_UUI_To_UI
922 ( p_rtg_header_Rec IN Bom_Rtg_Pub.Rtg_Header_Rec_Type
923 , p_rtg_header_unexp_Rec IN Bom_Rtg_Pub.Rtg_Header_Unexposed_Rec_Type
924 , x_rtg_header_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rtg_Header_Unexposed_Rec_Type
925 , x_Return_Status IN OUT NOCOPY VARCHAR2
926 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
927 )
928 IS
929 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
930 l_rtg_header_unexp_rec Bom_Rtg_Pub.Rtg_Header_Unexposed_Rec_Type;
931 l_return_status VARCHAR2(1);
932 l_err_text VARCHAR2(2000);
933 -- l_err_text_diff VARCHAR2(1);
934
935 CURSOR c_Check_Alternate( p_alt_designator VARCHAR2,
936 p_organization_id NUMBER ) IS
937 SELECT 1
938 FROM bom_alternate_designators
939 WHERE alternate_designator_code = p_alt_designator
940 AND organization_id = p_organization_id;
941
942 BEGIN
943 x_return_status := FND_API.G_RET_STS_SUCCESS;
944 l_return_status := FND_API.G_RET_STS_SUCCESS;
945 l_rtg_header_unexp_rec := p_rtg_header_unexp_rec;
946
947
948 If Bom_Rtg_Globals.Get_Debug = 'Y'
949 THEN Error_Handler.Write_Debug
950 ('Rtg Header UUI-UI Conversion . . ');
951 END IF;
952
953 --
954 -- Assembly Item name cannot be NULL or missing.
955 --
956 IF p_rtg_header_rec.assembly_item_name IS NULL OR
957 p_rtg_header_rec.assembly_item_name = FND_API.G_MISS_CHAR
958 THEN
959 Error_Handler.Add_Error_Token
960 ( p_message_name => 'BOM_RTG_AITEM_NAME_KEYCOL_NULL'
961 , p_mesg_token_tbl => l_mesg_token_tbl
962 , x_mesg_token_tbl => l_mesg_token_tbl
963 );
964
965 l_return_status := FND_API.G_RET_STS_ERROR;
966 END IF;
967
968 --
969 -- Assembly item name must be successfully converted to id.
970 --
971
972 l_rtg_header_unexp_rec.assembly_item_id :=
973 Assembly_Item ( p_assembly_item_name =>
974 p_rtg_header_rec.assembly_item_name
975 , p_organization_id =>
976 l_rtg_header_unexp_rec.organization_id
977 , x_err_text => l_err_text
978 );
979
980 IF l_rtg_header_unexp_rec.assembly_item_id IS NULL
981 THEN
982 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
983 g_token_tbl(1).token_value :=
984 p_rtg_header_rec.assembly_item_name;
985 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
986 g_token_tbl(2).token_value :=
987 p_rtg_header_rec.organization_code;
988 Error_Handler.Add_Error_Token
989 ( p_Message_Name => 'BOM_RTG_AITEM_DOESNOT_EXIST'
990 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
991 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
992 , p_Token_Tbl => g_Token_Tbl
993 );
994 l_Return_Status := FND_API.G_RET_STS_ERROR;
995 ELSIF l_err_text IS NOT NULL AND
996 (l_rtg_header_unexp_rec.assembly_item_id IS NULL OR
997 l_rtg_header_unexp_rec.assembly_item_id = FND_API.G_MISS_NUM)
998 THEN
999 -- This is an unexpected error.
1000 Error_Handler.Add_Error_Token
1001 ( p_Message_Name => NULL
1002 , p_Message_Text => l_err_text || ' in ' ||
1003 G_PKG_NAME
1004 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1005 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1006 );
1007 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1008 END IF;
1009
1010 If BOM_Rtg_Globals.Get_Debug = 'Y'
1011 THEN Error_Handler.Write_Debug
1012 ('After converting Assembly Item Id : '
1013 || to_char(l_rtg_header_unexp_rec.assembly_item_id)
1014 || ' Status ' || l_return_status); END IF;
1015
1016
1017 IF p_rtg_header_rec.alternate_routing_code IS NOT NULL AND
1018 p_rtg_header_rec.alternate_routing_code <> FND_API.G_MISS_CHAR
1019 THEN
1020 l_err_text/*_diff*/ := FND_API.G_RET_STS_ERROR;
1021
1022 FOR check_alternate IN
1023 c_Check_Alternate
1024 ( p_alt_designator =>
1025 p_rtg_header_rec.alternate_routing_code,
1026 p_organization_id =>
1027 l_rtg_header_unexp_rec.organization_id )
1028 LOOP
1029 l_err_text/*_diff*/ := FND_API.G_RET_STS_SUCCESS;
1030 END LOOP;
1031
1032 IF l_err_text/*_diff*/ <> FND_API.G_RET_STS_SUCCESS
1033 THEN
1034 g_token_tbl(1).token_name :='ALTERNATE_ROUTING_CODE';
1035 g_token_tbl(1).token_value :=
1036 p_rtg_header_rec.alternate_routing_code;
1037 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
1038 g_token_tbl(2).token_value :=
1039 p_rtg_header_rec.organization_code;
1040 Error_Handler.Add_Error_Token
1041 ( P_Message_Name => 'BOM_RTG_ALT_DESIGNATOR_INVALID'
1042 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1043 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1044 , p_token_tbl => g_token_tbl
1045 );
1046
1047 l_return_status := FND_API.G_RET_STS_ERROR;
1048 END IF;
1049 END IF;
1050
1051 x_return_status := l_return_status;
1052 x_rtg_header_unexp_rec := l_rtg_header_unexp_rec;
1053 x_mesg_token_tbl := l_mesg_token_tbl;
1054
1055 END Rtg_Header_UUI_To_UI;
1056
1057
1058 /*********************************************************************
1059 * Procedure : Rtg_Head_Header_VID
1060 * Returns : None
1061 * Parameters IN : RTG Header exposed Record
1062 * RTG Header Unexposed Record
1063 * Parameters OUT: RTG Header Unexposed Record
1064 * Return Status
1065 * Message Token Table
1066 * Purpose : This is the access procedure which the private API
1067 * will call to perform the RTG Header value to ID
1068 * conversions. If any of the conversions fail then the
1069 * the procedure will return with an error status and
1070 * the messsage token table filled with appropriate
1071 * error message.
1072 *********************************************************************/
1073 PROCEDURE Rtg_Header_VID
1074 ( p_rtg_header_rec IN Bom_Rtg_Pub.Rtg_Header_Rec_Type
1075 , p_rtg_header_unexp_rec IN Bom_Rtg_Pub.Rtg_Header_Unexposed_Rec_Type
1076 , x_rtg_header_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rtg_Header_Unexposed_Rec_Type
1077 , x_Return_Status IN OUT NOCOPY VARCHAR2
1078 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1079 )
1080 IS
1081 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
1082 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1083 l_err_text VARCHAR2(2000);
1084 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1085 l_rtg_header_unexp_rec Bom_Rtg_Pub.Rtg_Header_Unexposed_Rec_Type
1086 := p_rtg_header_unexp_rec;
1087 BEGIN
1088
1089 If BOM_Rtg_Globals.Get_Debug = 'Y'
1090 THEN Error_Handler.Write_Debug('Header VID conversion . . . ');
1091 END IF;
1092
1093
1094 IF p_rtg_header_rec.common_assembly_item_name IS NOT NULL AND
1095 p_rtg_header_rec.common_assembly_item_name <>
1096 FND_API.G_MISS_CHAR
1097 THEN
1098 l_rtg_header_unexp_rec.common_assembly_item_id :=
1099 Assembly_Item
1100 ( p_assembly_item_name => p_rtg_header_rec.common_assembly_item_name
1101 , p_organization_id => l_rtg_header_unexp_rec.organization_id
1102 , x_err_text => l_err_text
1103 );
1104
1105 IF l_rtg_header_unexp_rec.common_assembly_item_id IS NULL
1106 THEN
1107 l_token_tbl(1).token_name :=
1108 'COMMON_ASSEMBLY_ITEM_NAME';
1109 l_token_tbl(1).token_value :=
1110 p_rtg_header_rec.common_assembly_item_name;
1111 Error_Handler.Add_Error_Token
1112 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
1113 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1114 , p_Message_name =>
1115 'BOM_RTG_COMMON_AITEM_INVALID'
1116 , p_token_tbl => l_token_tbl
1117 );
1118 l_return_status := FND_API.G_RET_STS_ERROR;
1119
1120 ELSIF l_err_text IS NOT NULL AND
1121 l_rtg_header_unexp_rec.common_assembly_item_id
1122 IS NULL
1123 THEN
1124 Error_Handler.Add_Error_Token
1125 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
1126 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1127 , p_Message_text =>
1128 'Unexpected Error ' || l_err_text || ' in ' ||
1129 G_PKG_NAME
1130 , p_token_tbl => l_token_tbl
1131 );
1132
1133 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
1134 END IF;
1135
1136 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1137 Error_Handler.Write_Debug('Converted common assembly name . . .');
1138 END IF;
1139
1140 END IF;
1141
1142
1143 --
1144 -- Convert common assembly name information, alternate routing
1145 -- code and organanization id into common routing sequence_id
1146 --
1147 IF l_rtg_header_unexp_rec.organization_id IS NOT NULL AND
1148 l_rtg_header_unexp_rec.organization_id <>FND_API.G_MISS_NUM AND
1149 l_rtg_header_unexp_rec.common_assembly_item_id IS NOT NULL AND
1150 l_rtg_header_unexp_rec.common_assembly_item_id <> FND_API.G_MISS_NUM
1151 -- p_rtg_header_rec.alternate_routing_code IS NOT NULL AND
1152 -- p_rtg_header_rec.alternate_routing_code <>FND_API.G_MISS_CHAR
1153 THEN
1154
1155 l_rtg_header_unexp_rec.common_routing_sequence_id :=
1156 Routing_Sequence_Id
1157 ( p_assembly_item_id =>
1158 l_rtg_header_unexp_rec.common_assembly_item_id
1159 , p_organization_id =>
1160 l_rtg_header_unexp_rec.organization_id
1161 , p_alternate_routing_designator =>
1162 p_rtg_header_rec.alternate_routing_code
1163 , x_err_text => l_err_text
1164 );
1165
1166 IF l_rtg_header_unexp_rec.common_routing_sequence_id
1167 IS NULL
1168 THEN
1169 --
1170 -- Common routing sequence was not found
1171 --
1172 l_token_tbl.Delete;
1173 l_token_tbl(1).token_name :=
1174 'COMMON_ASSEMBLY_ITEM_NAME';
1175 l_token_tbl(1).token_value :=
1176 p_rtg_header_rec.common_assembly_item_name;
1177 l_token_tbl(2).token_name :=
1178 'ALTERNATE_ROUTING_CODE';
1179 l_token_tbl(2).token_value :=
1180 p_rtg_header_rec.alternate_routing_code;
1181 Error_Handler.Add_Error_Token
1182 ( p_message_name =>
1183 'BOM_RTG_CMN_RTG_SEQ_NOT_FOUND'
1184 , p_token_tbl => l_token_tbl
1185 , p_mesg_token_tbl => l_mesg_token_tbl
1186 , x_mesg_token_tbl => l_mesg_token_tbl
1187 );
1188 l_return_status := FND_API.G_RET_STS_ERROR;
1189
1190 ELSIF l_err_text IS NOT NULL AND
1191 l_rtg_header_unexp_rec.common_routing_sequence_id
1192 IS NULL
1193 THEN
1194 Error_Handler.Add_Error_Token
1195 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
1196 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1197 , p_Message_text =>
1198 'Unexpected Error ' || l_err_text || ' in ' ||
1199 G_PKG_NAME
1200 , p_token_tbl => l_token_tbl
1201 );
1202 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
1203 END IF;
1204
1205 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1206 Error_Handler.Write_Debug('Converted common routing sequence id. . .');
1207 END IF ;
1208
1209 END IF;
1210
1211
1212
1213
1214 IF p_rtg_header_rec.completion_location_name IS NOT NULL AND
1215 p_rtg_header_rec.completion_location_name <>
1216 FND_API.G_MISS_CHAR
1217 THEN
1218 l_rtg_header_unexp_rec.completion_locator_id :=
1219
1220 Completion_locator_id
1221 ( p_completion_location_name =>
1222 p_rtg_header_rec.completion_location_name
1223 , p_organization_id =>
1224 l_rtg_header_unexp_rec.organization_id
1225 , x_err_text => l_err_text
1226 );
1227 IF l_rtg_header_unexp_rec.completion_locator_id IS NULL
1228 THEN
1229 l_token_tbl(1).token_name :=
1230 'ASSEMBLY_ITEM_NAME';
1231 l_token_tbl(1).token_value :=
1232 p_rtg_header_rec.common_assembly_item_name;
1233 l_token_tbl(2).token_name :=
1234 'LOCATION_NAME';
1235 l_token_tbl(2).token_value :=
1236 p_rtg_header_rec.completion_location_name;
1237 Error_Handler.Add_Error_Token
1238 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
1239 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1240 , p_Message_name =>
1241 'BOM_RTG_LOCATION_NAME_INVALID'
1242 , p_token_tbl => l_token_tbl
1243 );
1244 l_return_status := FND_API.G_RET_STS_ERROR;
1245 ELSIF l_err_text IS NOT NULL AND
1246 l_rtg_header_unexp_rec.completion_locator_id
1247 IS NULL
1248 THEN
1249 Error_Handler.Add_Error_Token
1250 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
1251 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1252 , p_Message_text =>
1253 'Unexpected Error ' || l_err_text || ' in ' ||
1254 G_PKG_NAME
1255 , p_token_tbl => l_token_tbl
1256 );
1257 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
1258 END IF;
1259
1260 IF BOM_Rtg_Globals.Get_Debug = 'Y'THEN
1261 Error_Handler.Write_Debug('Converted completion locator name ...');
1262 END IF;
1263
1264 END IF;
1265
1266
1267 IF p_rtg_header_rec.line_code IS NOT NULL AND
1268 p_rtg_header_rec.line_code <> FND_API.G_MISS_CHAR
1269 -- p_rtg_header_rec.cfm_routing_flag = 1
1270 THEN
1271 l_rtg_header_unexp_rec.line_id :=
1272 Line_Id
1273 ( p_line_code => p_rtg_header_rec.line_code
1274 , p_organization_id =>
1275 l_rtg_header_unexp_rec.organization_id
1276 , x_err_text => l_err_text
1277 );
1278 IF l_rtg_header_unexp_rec.line_id IS NULL
1279 THEN
1280 l_token_tbl(1).token_name :=
1281 'LINE_CODE';
1282 l_token_tbl(1).token_value :=
1283 p_rtg_header_rec.line_code;
1284 Error_Handler.Add_Error_Token
1285 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
1286 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1287 , p_Message_name =>
1288 'BOM_RTG_LINE_CODE_INVALID'
1289 , p_token_tbl => l_token_tbl
1290 );
1291 l_return_status := FND_API.G_RET_STS_ERROR;
1292 ELSIF l_err_text IS NOT NULL AND
1293 l_rtg_header_unexp_rec.line_id
1294 IS NULL
1295 THEN
1296 Error_Handler.Add_Error_Token
1297 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
1298 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1299 , p_Message_text =>
1300 'Unexpected Error ' || l_err_text
1301 || ' in ' || G_PKG_NAME
1302 , p_token_tbl => l_token_tbl
1303 );
1304 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
1305 END IF;
1306
1307 IF BOM_Rtg_Globals.Get_Debug = 'Y'THEN
1308 Error_Handler.Write_Debug('Converted line code ...');
1309 END IF;
1310
1311 END IF;
1312
1313
1314 x_return_status := l_return_status;
1315
1316 IF BOM_Rtg_Globals.Get_Debug = 'Y'
1317 THEN Error_Handler.Write_Debug
1318 ('Return status of Header VID: ' || l_return_status );
1319 END IF;
1320
1321 x_rtg_header_unexp_rec := l_rtg_header_unexp_rec;
1322 x_mesg_token_tbl := l_mesg_token_tbl;
1323
1324 END Rtg_Header_VID;
1325
1326 /*********************************************************************
1327 * Procedure : Rtg_revision_UUI_To_UI
1328 * Returns : None
1329 * Parameters IN : Routing revision Record
1330 * Routing revision Unexposed Record
1331 * Parameters OUT: Routing revision unexposed record
1332 * Message Token Table
1333 * Return Status
1334 * Purpose : This procedure will perform all the required
1335 * User unique to Unique index conversions for routing
1336 * revision. Any errors will be logged in the Message
1337 * table and a return satus of success or failure will be
1338 * returned to the calling program.
1339 *********************************************************************/
1340
1341 PROCEDURE Rtg_Revision_UUI_To_UI
1342 ( p_rtg_revision_rec IN Bom_Rtg_Pub.Rtg_Revision_Rec_Type
1343 , p_rtg_rev_unexp_rec IN Bom_Rtg_Pub.Rtg_Rev_Unexposed_Rec_Type
1344 , x_rtg_rev_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rtg_Rev_Unexposed_Rec_Type
1345 , x_return_status IN OUT NOCOPY VARCHAR2
1346 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1347 )
1348 IS
1349 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1350 l_rtg_rev_unexp_rec Bom_Rtg_Pub.Rtg_Rev_Unexposed_Rec_Type;
1351 l_return_status VARCHAR2(1);
1352 l_err_text VARCHAR2(2000);
1353
1354
1355
1356 BEGIN
1357 x_return_status := FND_API.G_RET_STS_SUCCESS;
1358 l_return_status := FND_API.G_RET_STS_SUCCESS;
1359 l_rtg_rev_unexp_rec := p_rtg_rev_unexp_rec;
1360
1361
1362 If BOM_Rtg_Globals.Get_Debug = 'Y'
1363 THEN Error_Handler.Write_Debug
1364 ('Rtg revision UUI-UI Conversion . . ');
1365 END IF;
1366
1367 /******************************************************
1368 --
1369 -- Verify that the unique key columns are not empty
1370 --
1371 ********************************************************/
1372 --
1373 -- Assembly Item name cannot be NULL or missing.
1374 --
1375 IF p_rtg_revision_rec.assembly_item_name IS NULL OR
1376 p_rtg_revision_rec.assembly_item_name = FND_API.G_MISS_CHAR
1377 THEN
1378 Error_Handler.Add_Error_Token
1379 ( p_message_name => 'BOM_RTG_REV_AITEM_KEYCOL_NULL'
1380 , p_mesg_token_tbl => l_mesg_token_tbl
1381 , x_mesg_token_tbl => l_mesg_token_tbl
1382 );
1383
1384 l_return_status := FND_API.G_RET_STS_ERROR;
1385 END IF;
1386
1387 --
1388 -- Assembly item name must be successfully converted to id.
1389 --
1390
1391 l_rtg_rev_unexp_rec.assembly_item_id :=
1392 Assembly_Item ( p_assembly_item_name =>
1393 p_rtg_revision_rec.assembly_item_name
1394 , p_organization_id =>
1395 l_rtg_rev_unexp_rec.organization_id
1396 , x_err_text => l_err_text
1397 );
1398
1399 IF l_rtg_rev_unexp_rec.assembly_item_id IS NULL
1400 THEN
1401 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1402 g_token_tbl(1).token_value :=
1403 p_rtg_revision_rec.assembly_item_name;
1404 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
1405 g_token_tbl(2).token_value :=
1406 p_rtg_revision_rec.organization_code;
1407 Error_Handler.Add_Error_Token
1408 ( p_Message_Name => 'BOM_RTG_AITEM_DOESNOT_EXIST'
1409 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1410 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1411 , p_Token_Tbl => g_Token_Tbl
1412 );
1413 l_Return_Status := FND_API.G_RET_STS_ERROR;
1414 ELSIF l_err_text IS NOT NULL AND
1415 (l_rtg_rev_unexp_rec.assembly_item_id IS NULL OR
1416 l_rtg_rev_unexp_rec.assembly_item_id = FND_API.G_MISS_NUM)
1417 THEN
1418 -- This is an unexpected error.
1419 Error_Handler.Add_Error_Token
1420 ( p_Message_Name => NULL
1421 , p_Message_Text => l_err_text || ' in ' ||
1422 G_PKG_NAME
1423 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1424 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1425 );
1426 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1427 END IF;
1428
1429 If BOM_Rtg_Globals.Get_Debug = 'Y'
1430 THEN Error_Handler.Write_Debug
1431 ('After converting Assembly Item Id : '
1432 || to_char(l_rtg_rev_unexp_rec.assembly_item_id)
1433 || ' Status ' || l_return_status); END IF;
1434
1435
1436 x_return_status := l_return_status;
1437 x_rtg_rev_unexp_rec := l_rtg_rev_unexp_rec;
1438 x_mesg_token_tbl := l_mesg_token_tbl;
1439
1440 END Rtg_Revision_UUI_To_UI;
1441
1442
1443 /*******************************************************************
1444 * Procedure : Operation_UUI_To_UI used by RTG BO
1445 * Parameters IN : Operation exposed column record
1446 * Operation unexposed column record
1447 * Parameters OUT: Operation unexposed column record
1448 * Return Status
1449 * Message Token Table
1450 * Purpose : Convert ECO Operation to Common Operation and
1451 * Call Check_Entity for Common Operation.
1452 * Procedure will convert UUI to UI.
1453 *******************************************************************/
1454 PROCEDURE Operation_UUI_To_UI
1455 ( p_operation_rec IN Bom_Rtg_Pub.Operation_Rec_Type
1456 , p_op_unexp_rec IN Bom_Rtg_Pub.Op_Unexposed_Rec_Type
1457 , x_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Unexposed_Rec_Type
1458 , x_return_status IN OUT NOCOPY VARCHAR2
1459 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1460 )
1461 IS
1462 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
1463 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
1464 l_operation_rec Bom_Rtg_Pub.Operation_Rec_Type ;
1465
1466 BEGIN
1467 -- Convert Routing Operation to Common Operation
1468 Bom_Rtg_Pub.Convert_RtgOp_To_ComOp
1469 ( p_rtg_operation_rec => p_operation_rec
1470 , p_rtg_op_unexp_rec => p_op_unexp_rec
1471 , x_com_operation_rec => l_com_operation_rec
1472 , x_com_op_unexp_rec => l_com_op_unexp_rec
1473 ) ;
1474
1475
1476 -- Call Com_Operation_UUI_To_UI
1477 Bom_Rtg_Val_To_Id.Com_Operation_UUI_To_UI
1478 ( p_com_operation_rec => l_com_operation_rec
1479 , p_com_op_unexp_rec => l_com_op_unexp_rec
1480 , x_com_op_unexp_rec => l_com_op_unexp_rec
1481 , x_return_status => x_return_status
1482 , x_mesg_token_tbl => x_mesg_token_tbl
1483 ) ;
1484
1485 -- Convert the Common record to Routing Record
1486 Bom_Rtg_Pub.Convert_ComOp_To_RtgOp
1487 ( p_com_operation_rec => l_com_operation_rec
1488 , p_com_op_unexp_rec => l_com_op_unexp_rec
1489 , x_rtg_operation_rec => l_operation_rec
1490 , x_rtg_op_unexp_rec => x_op_unexp_rec
1491 ) ;
1492
1493 END Operation_UUI_To_UI ;
1494
1495
1496 /*******************************************************************
1497 * Procedure : Rev_Operation_UUI_To_UI used by ECO BO
1498 * Parameters IN : Revised Operation exposed column record
1499 * Revised Operation unexposed column record
1500 * Parameters OUT: Revised Operation unexposed column record
1501 * Return Status
1502 * Message Token Table
1503 * Purpose : Convert ECO Operation to Common Operation and
1504 * Call Check_Entity for Common Operation.
1505 * Procedure will convert UUI to UI.
1506 *******************************************************************/
1507 PROCEDURE Rev_Operation_UUI_To_UI
1508 ( p_rev_operation_rec IN Bom_Rtg_Pub.Rev_Operation_Rec_Type
1509 , p_rev_op_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
1510 , x_rev_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
1511 , x_return_status IN OUT NOCOPY VARCHAR2
1512 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1513 )
1514
1515
1516 IS
1517 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
1518 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
1519 l_rev_op_com_operation_rec Bom_Rtg_Pub.Rev_Operation_Rec_Type ;
1520
1521 BEGIN
1522 -- Convert Revised Operation to Common Operation
1523 Bom_Rtg_Pub.Convert_EcoOp_To_ComOp
1524 ( p_rev_operation_rec => p_rev_operation_rec
1525 , p_rev_op_unexp_rec => p_rev_op_unexp_rec
1526 , x_com_operation_rec => l_com_operation_rec
1527 , x_com_op_unexp_rec => l_com_op_unexp_rec
1528 ) ;
1529
1530
1531 -- Call Com_Operation_UUI_To_UI
1532 Bom_Rtg_Val_To_Id.Com_Operation_UUI_To_UI
1533 ( p_com_operation_rec => l_com_operation_rec
1534 , p_com_op_unexp_rec => l_com_op_unexp_rec
1535 , x_com_op_unexp_rec => l_com_op_unexp_rec
1536 , x_return_status => x_return_status
1537 , x_mesg_token_tbl => x_mesg_token_tbl
1538 ) ;
1539
1540 -- Convert the Common record to Revised Operation record
1541 Bom_Rtg_Pub.Convert_ComOp_To_EcoOp
1542 ( p_com_operation_rec => l_com_operation_rec
1543 , p_com_op_unexp_rec => l_com_op_unexp_rec
1544 , x_rev_operation_rec => l_rev_op_com_operation_rec
1545 , x_rev_op_unexp_rec => x_rev_op_unexp_rec
1546 ) ;
1547
1548 END Rev_Operation_UUI_To_UI ;
1549
1550
1551 /*********************************************************************
1552 * Procedure : Com_Operation_UUI_To_UI
1553 * Returns : None
1554 * Parameters IN : Common Operation exposed Record
1555 * Common Operation Unexposed Record
1556 * Parameters OUT: Common OPeration Unexposed record
1557 * Message Token Table
1558 * Return Status
1559 * Purpose : This procedure will perform all the required
1560 * User unique to Unique index conversions for operation
1561 * record. Any errors will be logged in the Message table
1562 * and a return satus of success or failure will be
1563 * returned to the calling program.
1564 *********************************************************************/
1565 PROCEDURE Com_Operation_UUI_To_UI
1566 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
1567 , p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
1568 , x_com_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
1569 , x_return_status IN OUT NOCOPY VARCHAR2
1570 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1571 )
1572 IS
1573
1574 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1575 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type;
1576 l_return_status VARCHAR2(1);
1577 l_err_text VARCHAR2(2000);
1578 l_err_text_diff VARCHAR2(1);
1579
1580 CURSOR c_Check_Alternate( p_alt_designator VARCHAR2,
1581 p_organization_id NUMBER ) IS
1582 SELECT 1
1583 FROM bom_alternate_designators
1584 WHERE alternate_designator_code = p_alt_designator
1585 AND organization_id = p_organization_id;
1586
1587 BEGIN
1588 x_return_status := FND_API.G_RET_STS_SUCCESS;
1589 l_return_status := FND_API.G_RET_STS_SUCCESS;
1590 l_com_op_unexp_rec := p_com_op_unexp_rec;
1591
1592 If BOM_Rtg_Globals.Get_Debug = 'Y'
1593 THEN Error_Handler.Write_Debug
1594 ('Operation record UUI-UI Conversion . . ');
1595 END IF;
1596
1597 --
1598 -- Revised Item name cannot be NULL or missing.
1599 --
1600 IF p_com_operation_rec.revised_item_name IS NULL OR
1601 p_com_operation_rec.revised_item_name = FND_API.G_MISS_CHAR
1602 THEN
1603 Error_Handler.Add_Error_Token
1604 ( p_message_name => 'BOM_OP_AITEM_KEYCOL_NULL'
1605 , p_mesg_token_tbl => l_mesg_token_tbl
1606 , x_mesg_token_tbl => l_mesg_token_tbl
1607 );
1608
1609 l_return_status := FND_API.G_RET_STS_ERROR;
1610 END IF;
1611
1612 --
1613 -- Operation sequence number cannot be NULL or missing.
1614 --
1615 IF p_com_operation_rec.operation_sequence_number IS NULL OR
1616 p_com_operation_rec.operation_sequence_number = FND_API.G_MISS_NUM
1617 THEN
1618 Error_Handler.Add_Error_Token
1619 ( p_Message_Name => 'BOM_OP_SEQNUM_KEYCOL_NULL'
1620 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1621 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1622 );
1623 l_Return_Status := FND_API.G_RET_STS_ERROR;
1624 END IF;
1625
1626 --
1627 -- Start effective date cannot be NULL or missing.
1628 --
1629 IF ( p_com_operation_rec.start_effective_date IS NULL OR
1630 p_com_operation_rec.start_effective_date = FND_API.G_MISS_DATE)
1631 AND ( p_com_operation_rec.operation_type NOT IN (2, 3)
1632 OR p_com_operation_rec.operation_type IS NULL)
1633 THEN
1634 Error_Handler.Add_Error_Token
1635 ( p_Message_Name => 'BOM_OP_EFFECTIVITY_KEYCOL_NULL'
1636 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1637 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1638 );
1639 l_Return_Status := FND_API.G_RET_STS_ERROR;
1640 END IF;
1641
1642 --
1643 -- If key columns are NULL, then return.
1644 --
1645 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1646 THEN
1647 x_return_status := l_return_status;
1648 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1649 x_com_op_unexp_rec := l_com_op_unexp_rec;
1650 RETURN ;
1651 END IF;
1652
1653
1654 --
1655 -- Revised item name must be successfully converted to id.
1656 --
1657 l_com_op_unexp_rec.revised_item_id :=
1658 Revised_Item ( p_revised_item_num =>
1659 p_com_operation_rec.revised_item_name
1660 , p_organization_id =>
1661 l_com_op_unexp_rec.organization_id
1662 , x_err_text => l_err_text
1663 );
1664
1665 IF l_com_op_unexp_rec.revised_item_id IS NULL
1666 THEN
1667 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1668 g_token_tbl(1).token_value :=
1669 p_com_operation_rec.revised_item_name;
1670 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
1671 g_token_tbl(2).token_value :=
1672 p_com_operation_rec.organization_code;
1673 Error_Handler.Add_Error_Token
1674 ( p_Message_Name => 'BOM_RTG_AITEM_DOESNOT_EXIST'
1675 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1676 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1677 , p_Token_Tbl => g_Token_Tbl
1678 );
1679 l_Return_Status := FND_API.G_RET_STS_ERROR;
1680 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1681 x_com_op_unexp_rec := l_com_op_unexp_rec;
1682 x_Return_Status := l_Return_Status;
1683
1684
1685 ELSIF l_err_text IS NOT NULL AND
1686 (l_com_op_unexp_rec.revised_item_id IS NULL OR
1687 l_com_op_unexp_rec.revised_item_id = FND_API.G_MISS_NUM)
1688 THEN
1689 -- This is an unexpected error.
1690 Error_Handler.Add_Error_Token
1691 ( p_Message_Name => NULL
1692 , p_Message_Text => l_err_text || ' in ' ||
1693 G_PKG_NAME
1694 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1695 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1696 );
1697 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1698 END IF;
1699
1700
1701
1702 IF BOM_Rtg_Globals.Get_Debug = 'Y'
1703 THEN Error_Handler.Write_Debug
1704 ('After converting Assembly Item Id : '
1705 || to_char(l_com_op_unexp_rec.revised_item_id)
1706 || ' Status ' || l_return_status);
1707 END IF;
1708
1709 IF p_com_operation_rec.alternate_routing_code IS NOT NULL AND
1710 p_com_operation_rec.alternate_routing_code <> FND_API.G_MISS_CHAR
1711 THEN
1712 /*l_err_text_diff*/l_err_text := FND_API.G_RET_STS_ERROR;
1713
1714 FOR check_alternate IN
1715 c_Check_Alternate
1716 ( p_alt_designator =>
1717 p_com_operation_rec.alternate_routing_code,
1718 p_organization_id =>
1719 l_com_op_unexp_rec.organization_id )
1720 LOOP
1721 /*l_err_text_diff*/l_err_text := FND_API.G_RET_STS_SUCCESS;
1722 END LOOP;
1723
1724 IF /*l_err_text_diff*/l_err_text <> FND_API.G_RET_STS_SUCCESS
1725 THEN
1726 g_token_tbl(1).token_name :='ALTERNATE_ROUTING_CODE';
1727 g_token_tbl(1).token_value :=
1728 p_com_operation_rec.alternate_routing_code;
1729 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
1730 g_token_tbl(2).token_value :=
1731 p_com_operation_rec.organization_code;
1732 Error_Handler.Add_Error_Token
1733 ( P_Message_Name => 'BOM_RTG_ALT_DESIGNATOR_INVALID'
1734 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1735 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1736 , p_token_tbl => g_token_tbl
1737 );
1738
1739 l_return_status := FND_API.G_RET_STS_ERROR;
1740 END IF;
1741
1742 END IF;
1743
1744
1745 /****************************************************************
1746 --
1747 -- Using the revised item key information, get the routing_sequence_id
1748 -- and revised item sequence id
1749 --
1750 ****************************************************************/
1751
1752 IF BOM_Rtg_Globals.Get_Bo_Identifier <> BOM_Rtg_Globals.G_RTG_BO
1753 THEN
1754
1755 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1756 Error_Handler.Write_Debug('Processing UUI_UI for operations and retrieving rev item seq id . . . ');
1757 END IF;
1758 NULL ;
1759
1760 /****************************************************************
1761 -- Comment out by MK on 12/04/00 to resolve Eco dependency
1762 -- this logic moved to Eng_Val_To_ID package
1763 l_com_op_unexp_rec.revised_item_sequence_id :=
1764 RtgAndRevItemSeq
1765 ( p_revised_item_id => l_com_op_unexp_rec.revised_item_id
1766 , p_item_revision => p_com_operation_rec.new_revised_item_revision
1767 , p_effective_date => p_com_operation_rec.start_effective_date
1768 , p_change_notice => p_com_operation_rec.eco_name
1769 , p_organization_id => l_com_op_unexp_rec.organization_id
1770 , p_new_routing_revision => p_com_operation_rec.new_routing_revision
1771 , p_from_end_item_number => p_com_operation_rec.from_end_item_unit_number
1772 , x_routing_sequence_id => l_com_op_unexp_rec.routing_sequence_id
1773 );
1774
1775 IF l_com_op_unexp_rec.revised_item_sequence_id IS NULL
1776 THEN
1777 g_Token_Tbl(1).Token_Name := 'OP_SEQ_NUMBER';
1778 g_Token_Tbl(1).Token_Value := p_com_operation_rec.operation_sequence_number;
1779 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
1780 g_Token_Tbl(2).Token_Value := p_com_operation_rec.revised_item_name;
1781 g_token_tbl(3).token_name := 'ECO_NAME';
1782 g_token_tbl(3).token_value := p_com_operation_rec.eco_name;
1783
1784 Error_Handler.Add_Error_Token
1785 ( p_Message_Name => 'BOM_OP_RIT_SEQUENCE_NOT_FOUND'
1786 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1787 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1788 , p_Token_Tbl => g_Token_Tbl
1789 );
1790
1791 l_Return_Status := FND_API.G_RET_STS_ERROR;
1792 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1793 x_com_op_unexp_rec := l_com_op_unexp_rec;
1794 x_Return_Status := l_Return_Status;
1795
1796 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('And this call returned with ' || l_Return_Status); END IF;
1797
1798 RETURN;
1799
1800 END IF;
1801
1802 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1803 Error_Handler.Write_Debug('Revised Item Sequence Id : ' || to_char(l_com_op_unexp_rec.revised_item_sequence_id)) ;
1804 END IF ;
1805 ****************************************************************/
1806
1807 ELSE
1808
1809 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
1810 Error_Handler.Write_Debug('Processing UUI_UI for operations . . . ');
1811 END IF;
1812 --
1813 -- If the calling BO is RTG then get the routing sequence id
1814 --
1815 l_com_op_unexp_rec.routing_sequence_id :=
1816 Routing_Sequence_id
1817 ( p_assembly_item_id => l_com_op_unexp_rec.revised_item_id
1818 , p_organization_id => l_com_op_unexp_rec.organization_id
1819 , p_alternate_routing_designator =>
1820 p_com_operation_rec.alternate_routing_code
1821 , x_err_text => l_err_text
1822 );
1823
1824 IF l_com_op_unexp_rec.routing_sequence_id IS NULL
1825 THEN
1826 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
1827 g_token_tbl(1).token_value :=
1828 p_com_operation_rec.revised_item_name;
1829 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
1830 g_token_tbl(2).token_value :=
1831 p_com_operation_rec.organization_code;
1832 Error_Handler.Add_Error_Token
1833 ( p_Message_Name => 'BOM_OP_RTG_NOT_FOUND'
1834 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1835 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1836 , p_Token_Tbl => g_Token_Tbl
1837 );
1838 l_Return_Status := FND_API.G_RET_STS_ERROR;
1839 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1840 x_com_op_unexp_rec := l_com_op_unexp_rec;
1841 x_Return_Status := l_Return_Status;
1842
1843 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('And this call returned with ' || l_Return_Status); END IF;
1844
1845 RETURN;
1846
1847
1848 ELSIF l_err_text IS NOT NULL AND
1849 (l_com_op_unexp_rec.routing_sequence_id IS NULL OR
1850 l_com_op_unexp_rec.routing_sequence_id= FND_API.G_MISS_NUM)
1851 THEN
1852 -- This is an unexpected error.
1853 Error_Handler.Add_Error_Token
1854 ( p_Message_Name => NULL
1855 , p_Message_Text => l_err_text || ' in ' ||
1856 G_PKG_NAME
1857 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1858 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1859 );
1860 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1861 END IF;
1862 END IF ;
1863
1864 IF BOM_Rtg_Globals.Get_Debug = 'Y'
1865 THEN Error_Handler.Write_Debug
1866 ('After converting, Routing sequence id is '
1867 || to_char(l_com_op_unexp_rec.routing_sequence_id)
1868 || ' Status ' || l_return_status);
1869 END IF;
1870
1871 x_return_status := l_return_status;
1872 x_com_op_unexp_rec := l_com_op_unexp_rec;
1873 x_mesg_token_tbl := l_mesg_token_tbl;
1874
1875 END Com_Operation_UUI_To_UI;
1876
1877
1878 /*******************************************************************
1879 * Procedure : Operation_VID used by RTG BO
1880 * Parameters IN : Operation exposed column record
1881 * Operation unexposed column record
1882 * Parameters OUT: Operation unexposed column record
1883 * Return Status
1884 * Message Token Table
1885 * Purpose : Convert ECO Operation to Common Operation and
1886 * Call Check_Entity for Common Operation.
1887 * Procedure will perform the operation record value to ID
1888 * conversions.
1889 *******************************************************************/
1890 PROCEDURE Operation_VID
1891 ( p_operation_rec IN Bom_Rtg_Pub.Operation_Rec_Type
1892 , p_op_unexp_rec IN Bom_Rtg_Pub.Op_Unexposed_Rec_Type
1893 , x_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Unexposed_Rec_Type
1894 , x_Return_Status IN OUT NOCOPY VARCHAR2
1895 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1896 )
1897
1898 IS
1899 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
1900 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
1901 l_operation_rec Bom_Rtg_Pub.Operation_Rec_Type ;
1902
1903 BEGIN
1904 -- Convert Routing Operation to Common Operation
1905 Bom_Rtg_Pub.Convert_RtgOp_To_ComOp
1906 ( p_rtg_operation_rec => p_operation_rec
1907 , p_rtg_op_unexp_rec => p_op_unexp_rec
1908 , x_com_operation_rec => l_com_operation_rec
1909 , x_com_op_unexp_rec => l_com_op_unexp_rec
1910 ) ;
1911
1912
1913 -- Call Com_Operation_VID
1914 Bom_Rtg_Val_To_Id.Com_Operation_VID
1915 ( p_com_operation_rec => l_com_operation_rec
1916 , p_com_op_unexp_rec => l_com_op_unexp_rec
1917 , x_com_op_unexp_rec => l_com_op_unexp_rec
1918 , x_return_status => x_return_status
1919 , x_mesg_token_tbl => x_mesg_token_tbl
1920 ) ;
1921
1922 -- Convert the Common record to Routing Record
1923 Bom_Rtg_Pub.Convert_ComOp_To_RtgOp
1924 ( p_com_operation_rec => l_com_operation_rec
1925 , p_com_op_unexp_rec => l_com_op_unexp_rec
1926 , x_rtg_operation_rec => l_operation_rec
1927 , x_rtg_op_unexp_rec => x_op_unexp_rec
1928 ) ;
1929
1930 END Operation_VID ;
1931
1932
1933 /*******************************************************************
1934 * Procedure : Rev_Operation_VID used by ECO BO
1935 * Parameters IN : Revised Operation exposed column record
1936 * Revised Operation unexposed column record
1937 * Parameters OUT: Revised Operation unexposed column record
1938 * Return Status
1939 * Message Token Table
1940 * Purpose : Convert ECO Operation to Common Operation and
1941 * Call Check_Entity for Common Operation.
1942 * Procedure will perform the operation record value to ID
1943 * conversions.
1944 *******************************************************************/
1945 PROCEDURE Rev_Operation_VID
1946 ( p_rev_operation_rec IN Bom_Rtg_Pub.Rev_Operation_Rec_Type
1947 , p_rev_op_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
1948 , x_rev_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
1949 , x_return_status IN OUT NOCOPY VARCHAR2
1950 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1951 )
1952
1953
1954 IS
1955 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
1956 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
1957 l_rev_operation_rec Bom_Rtg_Pub.Rev_Operation_Rec_Type ;
1958
1959 BEGIN
1960 -- Convert Revised Operation to Common Operation
1961 Bom_Rtg_Pub.Convert_EcoOp_To_ComOp
1962 ( p_rev_operation_rec => p_rev_operation_rec
1963 , p_rev_op_unexp_rec => p_rev_op_unexp_rec
1964 , x_com_operation_rec => l_com_operation_rec
1965 , x_com_op_unexp_rec => l_com_op_unexp_rec
1966 ) ;
1967
1968
1969 -- Call Com_Operation_VID
1970 Bom_Rtg_Val_To_Id.Com_Operation_VID
1971 ( p_com_operation_rec => l_com_operation_rec
1972 , p_com_op_unexp_rec => l_com_op_unexp_rec
1973 , x_com_op_unexp_rec => l_com_op_unexp_rec
1974 , x_return_status => x_return_status
1975 , x_mesg_token_tbl => x_mesg_token_tbl
1976 ) ;
1977
1978 -- Convert the Common record to Revised Operation record
1979 Bom_Rtg_Pub.Convert_ComOp_To_EcoOp
1980 ( p_com_operation_rec => l_com_operation_rec
1981 , p_com_op_unexp_rec => l_com_op_unexp_rec
1982 , x_rev_operation_rec => l_rev_operation_rec
1983 , x_rev_op_unexp_rec => x_rev_op_unexp_rec
1984 ) ;
1985
1986 END Rev_Operation_VID ;
1987
1988
1989 /*********************************************************************
1990 * Procedure : Com_Operation_VID
1991 * Returns : None
1992 * Parameters IN : Common Operation exposed Record
1993 * Common Operation Unexposed Record
1994 * Parameters OUT: Common Operation Unexposed Record
1995 * Return Status
1996 * Message Token Table
1997 * Purpose : This is the access procedure which the private API
1998 * will call to perform the operation record value to ID
1999 * conversions. If any of the conversions fail then
2000 * the procedure will return with an error status and
2001 * the messsage token table filled with appropriate
2002 * error message.
2003 *********************************************************************/
2004 PROCEDURE Com_Operation_VID
2005 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
2006 , p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
2007 , x_com_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
2008 , x_Return_Status IN OUT NOCOPY VARCHAR2
2009 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2010 )
2011 IS
2012 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
2013 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2014 l_err_text VARCHAR2(2000);
2015 l_Token_Tbl Error_Handler.Token_Tbl_Type;
2016 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
2017 := p_com_op_unexp_rec;
2018 l_common_rtg_seq_id NUMBER;
2019 l_cfm_flag NUMBER;
2020 l_old_op_seq_number NUMBER;
2021 BEGIN
2022
2023 If BOM_Rtg_Globals.Get_Debug = 'Y'
2024 THEN Error_Handler.Write_Debug('Operaton VID conversion . . .');
2025 END IF;
2026 --
2027 -- Convert standard operation code to standard operation id
2028 --
2029 IF p_com_operation_rec.standard_operation_code IS NOT NULL AND
2030 p_com_operation_rec.standard_operation_code <> FND_API.G_MISS_CHAR
2031 THEN
2032 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_RTG_BO
2033 THEN
2034
2035 l_com_op_unexp_rec.Standard_Operation_Id :=
2036 Standard_Operation_Id
2037 ( p_operation_type => p_com_operation_rec.operation_type
2038 , p_standard_operation_code => p_com_operation_rec.standard_operation_code
2039 , p_organization_id => l_com_op_unexp_rec.organization_id
2040 , p_routing_sequence_id => l_com_op_unexp_rec.routing_sequence_id
2041 , x_err_text => l_err_text
2042 );
2043
2044 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2045 ('Converted standard operation code . . .');
2046 END IF;
2047
2048 ELSE
2049
2050 l_com_op_unexp_rec.Standard_Operation_Id :=
2051 Standard_Operation_Id
2052 ( p_operation_type => p_com_operation_rec.operation_type
2053 , p_standard_operation_code => p_com_operation_rec.standard_operation_code
2054 , p_organization_id => l_com_op_unexp_rec.organization_id
2055 , p_rev_item_sequence_id => l_com_op_unexp_rec.revised_item_sequence_id
2056 , x_err_text => l_err_text
2057 );
2058
2059 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2060 ('Converted standard operation code . . .');
2061 END IF;
2062
2063 END IF ;
2064
2065 IF l_com_op_unexp_rec.Standard_Operation_Id IS NULL
2066 THEN
2067 l_token_tbl(1).token_name := 'STD_OP_CODE';
2068 l_token_tbl(1).token_value :=
2069 p_com_operation_rec.standard_operation_code;
2070 Error_Handler.Add_Error_Token
2071 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
2072 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2073 , p_Message_name =>
2074 'BOM_OP_STD_OP_CODE_INVALID'
2075 , p_token_tbl => l_token_tbl
2076 );
2077 l_return_status := FND_API.G_RET_STS_ERROR;
2078 ELSIF l_err_text IS NOT NULL AND
2079 l_com_op_unexp_rec.Standard_Operation_Id IS NULL
2080 THEN
2081 Error_Handler.Add_Error_Token
2082 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
2083 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2084 , p_Message_text =>
2085 'Unexpected Error ' || l_err_text || ' in ' ||
2086 G_PKG_NAME
2087 , p_token_tbl => l_token_tbl
2088 );
2089 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
2090 END IF;
2091 END IF;
2092
2093
2094 --
2095 -- Convert department code to department ID
2096 --
2097
2098 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2099 ('Dept code : . .' || p_com_operation_rec.department_code );
2100 END IF;
2101
2102
2103 IF p_com_operation_rec.department_code IS NOT NULL AND
2104 p_com_operation_rec.department_code <> FND_API.G_MISS_CHAR
2105 THEN
2106 l_com_op_unexp_rec.department_id :=
2107 Department_Id
2108 ( p_department_code => p_com_operation_rec.department_code
2109 , p_organization_id => l_com_op_unexp_rec.organization_id
2110 , x_err_text => l_err_text
2111 );
2112
2113 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2114 ('Converted department code . . .');
2115 END IF;
2116
2117 IF l_com_op_unexp_rec.department_id IS NULL
2118 THEN
2119 l_token_tbl(1).token_name :='DEPARTMENT_CODE';
2120 l_token_tbl(1).token_value :=
2121 p_com_operation_rec.department_code;
2122 Error_Handler.Add_Error_Token
2123 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
2124 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2125 , p_Message_name =>'BOM_OP_DEPT_CODE_INVALID'
2126 , p_token_tbl => l_token_tbl
2127 );
2128 l_return_status := FND_API.G_RET_STS_ERROR;
2129 ELSIF l_err_text IS NOT NULL AND
2130 l_com_op_unexp_rec.department_id IS NULL
2131 THEN
2132 Error_Handler.Add_Error_Token
2133 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
2134 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2135 , p_Message_text =>
2136 'Unexpected Error ' || l_err_text || ' in '
2137 || G_PKG_NAME
2138 , p_token_tbl => l_token_tbl
2139 );
2140 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
2141 END IF;
2142 END IF;
2143
2144 --
2145 -- If routing is flow routing, convert process code to proces_op
2146 -- _seq_id
2147 --
2148 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag IS NULL OR
2149 BOM_Rtg_Globals.Get_CFM_Rtg_Flag = FND_API.G_MISS_NUM
2150 THEN
2151 l_cfm_flag := Bom_Rtg_Validate.Get_Flow_Routing_Flag
2152 (l_com_op_unexp_rec.routing_sequence_id) ;
2153 BOM_Rtg_Globals.Set_CFM_Rtg_Flag(p_cfm_rtg_type => l_cfm_flag) ;
2154
2155 ELSE l_cfm_flag := BOM_Rtg_Globals.Get_CFM_Rtg_Flag ;
2156 END IF;
2157
2158
2159 IF l_cfm_flag = BOM_Rtg_Globals.G_FLOW_RTG
2160 THEN
2161
2162 IF (p_com_operation_rec.process_code IS NOT NULL AND
2163 p_com_operation_rec.process_code <> FND_API.G_MISS_CHAR )
2164 OR
2165 (p_com_operation_rec.process_seq_number IS NOT NULL AND
2166 p_com_operation_rec.process_seq_number <> FND_API.G_MISS_NUM )
2167
2168 THEN
2169
2170 l_com_op_unexp_rec.process_op_seq_id:=
2171 Process_Op_Seq_Id
2172 ( p_process_code => p_com_operation_rec.process_code
2173 , p_process_seq_number =>
2174 p_com_operation_rec.process_seq_number
2175 , p_organization_id =>
2176 l_com_op_unexp_rec.organization_id
2177 , p_routing_sequence_id =>
2178 l_com_op_unexp_rec.routing_sequence_id
2179 , x_err_text => l_err_text
2180 );
2181
2182 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2183 ('Converted process code . . .');
2184 END IF;
2185
2186 IF l_com_op_unexp_rec.process_op_seq_id IS NULL
2187 THEN
2188 IF p_com_operation_rec.process_code
2189 <> FND_API.G_MISS_CHAR
2190 THEN
2191 l_token_tbl(1).token_name :='PROCESS_CODE';
2192 l_token_tbl(1).token_value :=
2193 p_com_operation_rec.process_code;
2194 END IF ;
2195
2196 IF p_com_operation_rec.process_seq_number
2197 <> FND_API.G_MISS_NUM
2198 THEN
2199 l_token_tbl(2).token_name :='PROCESS_SEQ_NUM';
2200 l_token_tbl(2).token_value :=
2201 p_com_operation_rec.process_seq_number ;
2202 END IF ;
2203
2204 Error_Handler.Add_Error_Token
2205 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
2206 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2207 , p_Message_name =>
2208 'BOM_FLM_OP_PSCODE_SQNM_INVALID'
2209 , p_token_tbl => l_token_tbl
2210 );
2211
2212 l_return_status := FND_API.G_RET_STS_ERROR;
2213 l_token_tbl.delete ;
2214
2215 ELSIF l_err_text IS NOT NULL AND
2216 l_com_op_unexp_rec.process_op_seq_id IS NULL
2217 THEN
2218 Error_Handler.Add_Error_Token
2219 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
2220 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2221 , p_Message_text =>
2222 'Unexpected Error ' || l_err_text || ' in '
2223 || G_PKG_NAME
2224 , p_token_tbl => l_token_tbl
2225 );
2226 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
2227 END IF;
2228 END IF ;
2229 END IF;
2230
2231
2232 -- If routing is flow routing, convert line operation code to
2233 -- lin_op_seq_id.
2234 --
2235 IF l_cfm_flag = BOM_Rtg_Globals.G_FLOW_RTG
2236 THEN
2237 IF (p_com_operation_rec.line_op_code IS NOT NULL AND
2238 p_com_operation_rec.line_op_code <> FND_API.G_MISS_CHAR )
2239 OR
2240 (p_com_operation_rec.line_op_seq_number IS NOT NULL AND
2241 p_com_operation_rec.line_op_seq_number <>FND_API.G_MISS_NUM )
2242
2243 THEN
2244 l_com_op_unexp_rec.line_op_seq_id :=
2245 Line_Op_Seq_Id
2246 ( p_line_code => p_com_operation_rec.line_op_code
2247 , p_line_seq_number =>
2248 p_com_operation_rec.line_op_seq_number
2249 , p_organization_id =>
2250 l_com_op_unexp_rec.organization_id
2251 , p_routing_sequence_id =>
2252 l_com_op_unexp_rec.routing_sequence_id
2253 , x_err_text => l_err_text
2254 );
2255
2256 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2257 ('Converted line op code . . .');
2258 END IF;
2259
2260 IF l_com_op_unexp_rec.line_op_seq_id IS NULL
2261 THEN
2262 IF p_com_operation_rec.line_op_code
2263 <> FND_API.G_MISS_CHAR
2264 THEN
2265 l_token_tbl(1).token_name := 'LINE_OP_CODE';
2266 l_token_tbl(1).token_value :=
2267 p_com_operation_rec.line_op_code;
2268 END IF ;
2269
2270 IF p_com_operation_rec.line_op_seq_number
2271 <> FND_API.G_MISS_NUM
2272 THEN
2273
2274 l_token_tbl(2).token_name := 'LINE_OP_SEQ_NUM';
2275 l_token_tbl(2).token_value :=
2276 p_com_operation_rec.line_op_seq_number ;
2277 END IF ;
2278
2279 Error_Handler.Add_Error_Token
2280 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
2281 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2282 , p_Message_name =>
2283 'BOM_FLM_OP_LNOPCD_SQNM_INVALID'
2284 , p_token_tbl => l_token_tbl
2285 );
2286
2287 l_return_status := FND_API.G_RET_STS_ERROR;
2288 l_token_tbl.delete ;
2289
2290 ELSIF l_err_text IS NOT NULL AND
2291 l_com_op_unexp_rec.line_op_seq_id IS NULL
2292 THEN
2293 Error_Handler.Add_Error_Token
2294 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
2295 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2296 , p_Message_text =>
2297 'Unexpected Error ' || l_err_text || ' in '
2298 || G_PKG_NAME
2299 , p_token_tbl => l_token_tbl
2300 );
2301 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
2302 END IF;
2303 END IF ;
2304 END IF;
2305
2306
2307
2308 --
2309 -- Using old_operation information, get the old_operation_sequence_id
2310 --
2311 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
2312 AND p_com_operation_rec.acd_type IN (2,3) -- Change or Disable
2313 AND p_com_operation_rec.old_start_effective_date IS NOT NULL
2314 AND p_com_operation_rec.old_start_effective_date <> FND_API.G_MISS_DATE
2315 -- p_com_operation_rec.old_operation_sequence_number IS NOT NULL AND
2316 -- p_com_operation_rec.old_operation_sequence_number <>
2317 -- FND_API.G_MISS_NUM
2318 THEN
2319
2320 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
2321 Error_Handler.Write_Debug('Executing old operation seq id Val-ID conversion . . .');
2322 Error_Handler.Write_Debug('Routing Sequence: ' || to_char(l_com_op_unexp_rec.routing_sequence_id));
2323 END IF;
2324
2325 IF (p_com_operation_rec.old_operation_sequence_number IS NULL
2326 OR p_com_operation_rec.old_operation_sequence_number =
2327 FND_API.G_MISS_NUM)
2328 THEN
2329 l_old_op_seq_number := p_com_operation_rec.operation_sequence_number ;
2330 ELSE
2331 l_old_op_seq_number := p_com_operation_rec.old_operation_sequence_number ;
2332 END IF ;
2333
2334
2335 IF l_old_op_seq_number <>
2336 p_com_operation_rec.operation_sequence_number
2337 THEN
2338
2339 Error_Handler.Add_Error_Token
2340 ( p_Message_Name => 'BOM_OP_OLD_SEQ_NUM_INVALID'
2341 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2342 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2343 , p_Token_Tbl => g_Token_Tbl
2344 );
2345 l_Return_Status := FND_API.G_RET_STS_ERROR;
2346
2347 END IF ;
2348
2349 l_com_op_unexp_rec.old_operation_sequence_id :=
2350 Old_Operation_Sequence
2351 ( p_old_effective_date => p_com_operation_rec.old_start_effective_date
2352 , p_old_op_seq_num => l_old_op_seq_number
2353 -- p_com_operation_rec.old_operation_sequence_number
2354 , p_operation_type => p_com_operation_rec.operation_type
2355 , p_routing_sequence_id => l_com_op_unexp_rec.routing_sequence_id
2356 );
2357
2358 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug ('Converted Old Operation Seq Id. . .');
2359 END IF;
2360
2361 IF l_com_op_unexp_rec.old_operation_sequence_id IS NULL
2362 THEN
2363 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
2364 g_token_tbl(1).token_value :=
2365 p_com_operation_rec.operation_sequence_number ;
2366 g_token_tbl(2).token_name := 'OLD_EFFECTIVITY_DATE';
2367 g_token_tbl(2).token_value :=
2368 p_com_operation_rec.old_start_effective_date;
2369 g_token_tbl(3).token_name := 'OLD_OP_SEQ_NUMBER';
2370 g_token_tbl(3).token_value :=
2371 p_com_operation_rec.old_operation_sequence_number;
2372
2373 Error_Handler.Add_Error_Token
2374 ( p_Message_Name => 'BOM_OP_OLD_OP_SEQ_INVALID'
2375 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2376 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2377 , p_Token_Tbl => g_Token_Tbl
2378 );
2379 l_Return_Status := FND_API.G_RET_STS_ERROR;
2380 ELSIF l_com_op_unexp_rec.old_operation_sequence_id = FND_API.G_MISS_NUM
2381 THEN
2382 Error_Handler.Add_Error_Token
2383 ( p_Message_Name => NULL
2384 , p_Message_Text =>
2385 'Unexpected Error while converting old operation sequence id'
2386 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2387 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2388
2389 );
2390 l_Return_Status := FND_API.G_RET_STS_ERROR;
2391
2392 END IF;
2393
2394
2395 END IF;
2396
2397
2398 x_return_status := l_return_status;
2399
2400 IF Bom_Rtg_Globals.Get_Debug = 'Y'
2401 THEN Error_Handler.Write_Debug
2402 ('Return status of operation VID: ' || l_return_status );
2403 END IF;
2404
2405 x_com_op_unexp_rec := l_com_op_unexp_rec;
2406 x_mesg_token_tbl := l_mesg_token_tbl;
2407
2408 END Com_Operation_VID;
2409
2410
2411 /*********************************************************************
2412 * Procedure : Op_Resource_UUI_To_UI
2413 * Returns : None
2414 * Parameters IN : Operation Resource Exposed Record
2415 * Operation Resource Unexposed Record
2416 * Parameters OUT: Operation Resource unexposed Record
2417 * Message Token Table
2418 * Return Status
2419 * Purpose : Convert Routing Op Resource to Revised Op Resource and
2420 * Call Rev_Op_resource_UUI_To_UI for ECO Bo.
2421 * After calling Rev_Op_resource_UUI_To_UI, convert Revised
2422 * Op Resource record back to Routing Op Resource
2423 *********************************************************************/
2424 PROCEDURE Op_Resource_UUI_To_UI
2425 ( p_op_resource_rec IN Bom_Rtg_Pub.Op_Resource_Rec_Type
2426 , p_op_res_unexp_rec IN Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
2427 , x_op_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
2428 , x_return_status IN OUT NOCOPY VARCHAR2
2429 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2430 )
2431
2432 IS
2433 l_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
2434 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type ;
2435 l_op_resource_rec Bom_Rtg_Pub.Op_Resource_Rec_Type ;
2436
2437 BEGIN
2438 -- Convert Routing Operation to ECO Operation
2439 Bom_Rtg_Pub.Convert_RtgRes_To_EcoRes
2440 ( p_rtg_op_resource_rec => p_op_resource_rec
2441 , p_rtg_op_res_unexp_rec => p_op_res_unexp_rec
2442 , x_rev_op_resource_rec => l_rev_op_resource_rec
2443 , x_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2444 ) ;
2445
2446 -- Call Rev_Op_Resource_UUI_To_UI
2447 Bom_Rtg_Val_To_Id.Rev_Op_Resource_UUI_To_UI
2448 ( p_rev_op_resource_rec => l_rev_op_resource_rec
2449 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2450 , x_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2451 , x_return_status => x_return_status
2452 , x_mesg_token_tbl => x_mesg_token_tbl
2453 ) ;
2454
2455 -- Convert old Eco Opeartion Record back to Routing Operation
2456 Bom_Rtg_Pub.Convert_EcoRes_To_RtgRes
2457 ( p_rev_op_resource_rec => l_rev_op_resource_rec
2458 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2459 , x_rtg_op_resource_rec => l_op_resource_rec
2460 , x_rtg_op_res_unexp_rec => x_op_res_unexp_rec
2461 ) ;
2462
2463 END Op_Resource_UUI_To_UI ;
2464
2465 /*********************************************************************
2466 * Procedure : Rev_Op_Resource_UUI_To_UI
2467 * Returns : None
2468 * Parameters IN : Revised Operation Resource Exposed Record
2469 * Revised Operation Resource Unexposed Record
2470 * Parameters OUT: Revised Operation Resource Unexposed Record
2471 * Message Token Table
2472 * Return Status
2473 * Purpose : This procedure will perform all the required
2474 * User unique to Unique index conversions for
2475 * resource record. Any errors will be logged in the
2476 * Message table and a return satus of success or
2477 * failure will be returned to the calling program.
2478 *********************************************************************/
2479 PROCEDURE Rev_Op_Resource_UUI_To_UI
2480 ( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
2481 , p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
2482 , x_rev_op_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
2483 , x_return_status IN OUT NOCOPY VARCHAR2
2484 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2485 )
2486
2487
2488
2489 IS
2490 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2491 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type ;
2492 l_return_status VARCHAR2(1);
2493 l_err_text VARCHAR2(2000);
2494 l_cfm_flag NUMBER;
2495 l_err_text_diff VARCHAR2(1);
2496
2497 CURSOR c_Check_Alternate( p_alt_designator VARCHAR2,
2498 p_organization_id NUMBER ) IS
2499 SELECT 1
2500 FROM bom_alternate_designators
2501 WHERE alternate_designator_code = p_alt_designator
2502 AND organization_id = p_organization_id;
2503
2504 BEGIN
2505 x_return_status := FND_API.G_RET_STS_SUCCESS;
2506 l_return_status := FND_API.G_RET_STS_SUCCESS;
2507 l_rev_op_res_unexp_rec := p_rev_op_res_unexp_rec;
2508
2509 If BOM_Rtg_Globals.Get_Debug = 'Y'
2510 THEN Error_Handler.Write_Debug
2511 ('Operation resource record UUI-UI Conversion . . ');
2512 END IF;
2513
2514 --
2515 -- Assembly Item name cannot be NULL or missing.
2516 --
2517 IF p_rev_op_resource_rec.revised_item_name IS NULL OR
2518 p_rev_op_resource_rec.revised_item_name = FND_API.G_MISS_CHAR
2519 THEN
2520 Error_Handler.Add_Error_Token
2521 ( p_message_name => 'BOM_RES_AITEM_KEYCOL_NULL'
2522 , p_mesg_token_tbl => l_mesg_token_tbl
2523 , x_mesg_token_tbl => l_mesg_token_tbl
2524 );
2525
2526 l_return_status := FND_API.G_RET_STS_ERROR;
2527 END IF;
2528
2529 --
2530 -- Resource sequence number cannot be NULL or missing.
2531 --
2532 IF p_rev_op_resource_rec.resource_sequence_number IS NULL OR
2533 p_rev_op_resource_rec.resource_sequence_number = FND_API.G_MISS_NUM
2534 THEN
2535 Error_Handler.Add_Error_Token
2536 ( p_Message_Name => 'BOM_RES_SEQNUM_KEYCOL_NULL'
2537 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2538 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2539 );
2540 l_Return_Status := FND_API.G_RET_STS_ERROR;
2541 END IF;
2542
2543 --
2544 -- Operation sequence number cannot be NULL or missing.
2545 --
2546 IF p_rev_op_resource_rec.operation_sequence_number IS NULL OR
2547 p_rev_op_resource_rec.operation_sequence_number =
2548 FND_API.G_MISS_NUM
2549 THEN
2550 Error_Handler.Add_Error_Token
2551 ( p_Message_Name => 'BOM_RES_OP_SEQNUM_KEYCOL_NULL'
2552 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2553 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2554 );
2555 l_Return_Status := FND_API.G_RET_STS_ERROR;
2556 END IF;
2557
2558 --
2559 -- Start effective date cannot be NULL or missing.
2560 --
2561 IF p_rev_op_resource_rec.op_start_effective_date IS NULL OR
2562 p_rev_op_resource_rec.op_start_effective_date = FND_API.G_MISS_DATE
2563 THEN
2564 Error_Handler.Add_Error_Token
2565 ( p_Message_Name => 'BOM_RES_EFF_DATE_KEYCOL_NULL'
2566 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2567 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2568 );
2569 l_Return_Status := FND_API.G_RET_STS_ERROR;
2570 END IF;
2571
2572 --
2573 -- If key columns are NULL, then return.
2574 --
2575 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2576 THEN
2577 x_Return_Status := FND_API.G_RET_STS_ERROR;
2578 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2579 RETURN;
2580 END IF;
2581
2582
2583 --
2584 -- Assembly item name must be successfully converted to id.
2585 --
2586
2587 l_rev_op_res_unexp_rec.revised_item_id :=
2588 Revised_Item ( p_revised_item_num =>
2589 p_rev_op_resource_rec.revised_item_name
2590 , p_organization_id =>
2591 l_rev_op_res_unexp_rec.organization_id
2592 , x_err_text => l_err_text
2593 );
2594
2595 IF l_rev_op_res_unexp_rec.revised_item_id IS NULL
2596 THEN
2597 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
2598 g_token_tbl(1).token_value :=
2599 p_rev_op_resource_rec.revised_item_name;
2600 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
2601 g_token_tbl(2).token_value :=
2602 p_rev_op_resource_rec.organization_code;
2603 Error_Handler.Add_Error_Token
2604 ( p_Message_Name => 'BOM_RTG_AITEM_DOESNOT_EXIST'
2605 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2606 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2607 , p_Token_Tbl => g_Token_Tbl
2608 );
2609 l_Return_Status := FND_API.G_RET_STS_ERROR;
2610 ELSIF l_err_text IS NOT NULL AND
2611 ( l_rev_op_res_unexp_rec.revised_item_id IS NULL OR
2612 l_rev_op_res_unexp_rec.revised_item_id = FND_API.G_MISS_NUM)
2613 THEN
2614 -- This is an unexpected error.
2615 Error_Handler.Add_Error_Token
2616 ( p_Message_Name => NULL
2617 , p_Message_Text => l_err_text || ' in ' ||
2618 G_PKG_NAME
2619 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2620 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2621 );
2622 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2623 END IF;
2624
2625 IF BOM_Rtg_Globals.Get_Debug = 'Y'
2626 THEN Error_Handler.Write_Debug
2627 ('After converting Assembly Item Id : '
2628 || to_char(l_rev_op_res_unexp_rec.revised_item_id)
2629 || ' Status ' || l_return_status);
2630 END IF;
2631
2632 IF p_rev_op_resource_rec.alternate_routing_code IS NOT NULL AND
2633 p_rev_op_resource_rec.alternate_routing_code <> FND_API.G_MISS_CHAR
2634 THEN
2635 /*l_err_text_diff*/l_err_text := FND_API.G_RET_STS_ERROR;
2636
2637 FOR check_alternate IN
2638 c_Check_Alternate
2639 ( p_alt_designator =>
2640 p_rev_op_resource_rec.alternate_routing_code,
2641 p_organization_id =>
2642 l_rev_op_res_unexp_rec.organization_id )
2643 LOOP
2644 /*l_err_text_diff*/l_err_text := FND_API.G_RET_STS_SUCCESS;
2645 END LOOP;
2646
2647 IF /*l_err_text_diff*/l_err_text <> FND_API.G_RET_STS_SUCCESS
2648 THEN
2649 g_token_tbl(1).token_name :='ALTERNATE_ROUTING_CODE';
2650 g_token_tbl(1).token_value :=
2651 p_rev_op_resource_rec.alternate_routing_code;
2652 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
2653 g_token_tbl(2).token_value :=
2654 p_rev_op_resource_rec.organization_code;
2655 Error_Handler.Add_Error_Token
2656 ( P_Message_Name => 'BOM_RTG_ALT_DESIGNATOR_INVALID'
2657 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2658 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2659 , p_token_tbl => g_token_tbl
2660 );
2661
2662 l_return_status := FND_API.G_RET_STS_ERROR;
2663 END IF;
2664
2665 END IF;
2666
2667
2668 /****************************************************************
2669 --
2670 -- Using the revised item key information, get the routing_sequence_id
2671 -- and revised item sequence id
2672 --
2673 ****************************************************************/
2674
2675 IF BOM_Rtg_Globals.Get_Bo_Identifier <> BOM_Rtg_Globals.G_RTG_BO
2676 THEN
2677
2678 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
2679 Error_Handler.Write_Debug('Processing UUI_UI for operation resources and retrieving rev item seq id . . . ');
2680 END IF;
2681
2682 NULL ;
2683
2684 /****************************************************************
2685 -- Comment out by MK on 12/04/00 to resolve Eco dependency
2686 l_rev_op_res_unexp_rec.revised_item_sequence_id :=
2687 RtgAndRevItemSeq
2688 ( p_revised_item_id => l_rev_op_res_unexp_rec.revised_item_id
2689 , p_item_revision => p_rev_op_resource_rec.new_revised_item_revision
2690 , p_effective_date => p_rev_op_resource_rec.op_start_effective_date
2691 , p_change_notice => p_rev_op_resource_rec.eco_name
2692 , p_organization_id => l_rev_op_res_unexp_rec.organization_id
2693 , p_new_routing_revision => p_rev_op_resource_rec.new_routing_revision
2694 , p_from_end_item_number => p_rev_op_resource_rec.from_end_item_unit_number
2695 , x_routing_sequence_id => l_rev_op_res_unexp_rec.routing_sequence_id
2696 );
2697
2698 IF l_rev_op_res_unexp_rec.revised_item_Sequence_id IS NULL
2699 THEN
2700 g_Token_Tbl(1).Token_Name := 'RES_SEQ_NUMBER';
2701 g_Token_Tbl(1).Token_Value := p_rev_op_resource_rec.resource_sequence_number;
2702 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
2703 g_Token_Tbl(2).Token_Value := p_rev_op_resource_rec.revised_item_name;
2704 g_token_tbl(3).token_name := 'ECO_NAME';
2705 g_token_tbl(3).token_value := p_rev_op_resource_rec.eco_name;
2706
2707 Error_Handler.Add_Error_Token
2708 ( p_Message_Name => 'BOM_RES_RIT_SEQUENCE_NOT_FOUND'
2709 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2710 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2711 , p_Token_Tbl => g_Token_Tbl
2712 );
2713
2714 l_Return_Status := FND_API.G_RET_STS_ERROR;
2715 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2716 x_rev_op_res_unexp_rec := l_rev_op_res_unexp_rec;
2717 x_Return_Status := l_Return_Status;
2718
2719 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('And this call returned with ' || l_Return_Status); END IF;
2720
2721 RETURN;
2722
2723 END IF;
2724
2725 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
2726 Error_Handler.Write_Debug('Revised Item Sequence Id : ' || to_char(l_rev_op_res_unexp_rec.revised_item_sequence_id)) ;
2727 END IF ;
2728
2729 ****************************************************************/
2730
2731 ELSE
2732 --
2733 -- If the calling BO is RTG then get the routing sequence id
2734 --
2735 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
2736 Error_Handler.Write_Debug('Processing UUI_UI for operation resources. . . ');
2737 END IF;
2738 l_rev_op_res_unexp_rec.routing_sequence_id :=
2739 Routing_Sequence_id
2740 ( p_assembly_item_id => l_rev_op_res_unexp_rec.revised_item_id
2741 , p_organization_id => l_rev_op_res_unexp_rec.organization_id
2742 , p_alternate_routing_designator =>
2743 p_rev_op_resource_rec.alternate_routing_code
2744 , x_err_text => l_err_text
2745 );
2746
2747 IF l_rev_op_res_unexp_rec.routing_sequence_id IS NULL
2748 THEN
2749 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
2750 g_token_tbl(1).token_value :=
2751 p_rev_op_resource_rec.revised_item_name;
2752 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
2753 g_token_tbl(2).token_value :=
2754 p_rev_op_resource_rec.organization_code;
2755 Error_Handler.Add_Error_Token
2756 ( p_Message_Name => 'BOM_RES_RTG_NOT_FOUND'
2757 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2758 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2759 , p_Token_Tbl => g_Token_Tbl
2760 );
2761 l_Return_Status := FND_API.G_RET_STS_ERROR;
2762 ELSIF l_err_text IS NOT NULL AND
2763 (l_rev_op_res_unexp_rec.routing_sequence_id IS NULL OR
2764 l_rev_op_res_unexp_rec.routing_sequence_id = FND_API.G_MISS_NUM)
2765 THEN
2766 -- This is an unexpected error.
2767 Error_Handler.Add_Error_Token
2768 ( p_Message_Name => NULL
2769 , p_Message_Text => l_err_text || ' in ' ||
2770 G_PKG_NAME
2771 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2772 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2773 );
2774 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2775 END IF;
2776 END IF ;
2777
2778 -- Added by MK on 12/04/00
2779 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_RTG_BO
2780 THEN
2781
2782 IF BOM_Rtg_Globals.Get_Debug = 'Y'
2783 THEN Error_Handler.Write_Debug
2784 ('After converting, routing sequence id is '
2785 || to_char(l_rev_op_res_unexp_rec.routing_sequence_id )
2786 || ' Status ' || l_return_status);
2787 END IF;
2788
2789 --
2790 -- For flow routing, operatoin Type should be set in (1, 2, 3)
2791 --
2792 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag IS NULL OR
2793 BOM_Rtg_Globals.Get_CFM_Rtg_Flag = FND_API.G_MISS_NUM
2794 THEN
2795 l_cfm_flag := Bom_Rtg_Validate.Get_Flow_Routing_Flag(
2796 l_rev_op_res_unexp_rec.routing_sequence_id) ;
2797 BOM_Rtg_Globals.Set_CFM_Rtg_Flag(p_cfm_rtg_type => l_cfm_flag) ;
2798 ELSE l_cfm_flag := BOM_Rtg_Globals.Get_CFM_Rtg_Flag ;
2799 END IF;
2800
2801 IF l_cfm_flag = BOM_Rtg_Globals.G_FLOW_RTG
2802 THEN
2803 IF p_rev_op_resource_rec.operation_type IS NULL
2804 OR p_rev_op_resource_rec.operation_type = FND_API.G_MISS_NUM
2805 OR p_rev_op_resource_rec.operation_type NOT IN (1, 2, 3)
2806 THEN
2807
2808 IF p_rev_op_resource_rec.operation_type <> FND_API.G_MISS_NUM
2809 THEN
2810 g_token_tbl(1).token_name := 'OPERATION_TYPE';
2811 g_token_tbl(1).token_value :=
2812 p_rev_op_resource_rec.operation_type ;
2813 END IF ;
2814
2815 Error_Handler.Add_Error_Token
2816 ( p_Message_Name => 'BOM_FLM_RES_OPTYPE_INVALID'
2817 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2818 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2819 , p_Token_Tbl => g_Token_Tbl
2820 );
2821 l_return_status := FND_API.G_RET_STS_ERROR ;
2822 END IF ;
2823 ELSE
2824 IF p_rev_op_resource_rec.operation_type IS NOT NULL
2825 AND p_rev_op_resource_rec.operation_type <> FND_API.G_MISS_NUM
2826 AND p_rev_op_resource_rec.operation_type <> 1
2827 THEN
2828 g_token_tbl(1).token_name := 'OPERATION_TYPE';
2829 g_token_tbl(1).token_value :=
2830 p_rev_op_resource_rec.operation_type;
2831
2832 Error_Handler.Add_Error_Token
2833 ( p_Message_Name => 'BOM_STD_RES_OPTYPE_INVALID'
2834 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2835 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2836 , p_Token_Tbl => g_Token_Tbl
2837 );
2838 l_return_status := FND_API.G_RET_STS_ERROR ;
2839 END IF ;
2840
2841 END IF;
2842
2843 l_rev_op_res_unexp_rec.operation_sequence_id :=
2844 Operation_Sequence_id
2845 ( p_routing_sequence_id =>
2846 l_rev_op_res_unexp_rec.routing_sequence_id
2847 , p_operation_type =>
2848 p_rev_op_resource_rec.operation_type
2849 , p_operation_seq_num =>
2850 p_rev_op_resource_rec.operation_sequence_number
2851 , p_effectivity_date =>
2852 p_rev_op_resource_rec.op_start_effective_date
2853 , x_err_text => l_err_text
2854 );
2855
2856 IF l_rev_op_res_unexp_rec.operation_sequence_id IS NULL
2857 THEN
2858 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
2859 g_token_tbl(1).token_value :=
2860 p_rev_op_resource_rec.operation_sequence_number ;
2861 Error_Handler.Add_Error_Token
2862 ( p_Message_Name => 'BOM_RES_OP_NOT_FOUND'
2863 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2864 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2865 , p_Token_Tbl => g_Token_Tbl
2866 );
2867 l_Return_Status := FND_API.G_RET_STS_ERROR;
2868 ELSIF l_err_text IS NOT NULL AND
2869 (l_rev_op_res_unexp_rec.operation_sequence_id IS NULL OR
2870 l_rev_op_res_unexp_rec.operation_sequence_id = FND_API.G_MISS_NUM
2871 )
2872 THEN
2873 -- This is an unexpected error.
2874 Error_Handler.Add_Error_Token
2875 ( p_Message_Name => NULL
2876 , p_Message_Text => l_err_text || ' in ' ||
2877 G_PKG_NAME
2878 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2879 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2880 );
2881 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2882 END IF;
2883
2884 IF BOM_Rtg_Globals.Get_Debug = 'Y'
2885 THEN Error_Handler.Write_Debug
2886 ('After converting, operation sequence id is '
2887 || to_char(l_rev_op_res_unexp_rec.operation_sequence_id )
2888 || ' Status ' || l_return_status);
2889 END IF;
2890
2891 END IF ; -- Added by MK on 12/04/00 Rtg BO specific
2892
2893 x_return_status := l_return_status;
2894 x_rev_op_res_unexp_rec := l_rev_op_res_unexp_rec;
2895 x_mesg_token_tbl := l_mesg_token_tbl;
2896
2897 END Rev_Op_Resource_UUI_To_UI;
2898
2899
2900
2901 /*********************************************************************
2902 * Procedure : Op_Resource_VID
2903 * Returns : None
2904 * Parameters IN : Operation resource exposed Record
2905 * Operation resource Unexposed Record
2906 * Parameters OUT: Operation resource unexposed record
2907 * Message Token Table
2908 * Return Status
2909 * Purpose : Convert Routing Op Resource to Revised Op Resource and
2910 * Call Rev_Op_resource_VID for ECO Bo.
2911 * After calling Rev_Op_resource_VID, convert Revised
2912 * Op Resource record back to Routing Op Resource
2913 *********************************************************************/
2914 PROCEDURE Op_Resource_VID
2915 ( p_op_resource_rec IN Bom_Rtg_Pub.Op_Resource_Rec_Type
2916 , p_op_res_unexp_rec IN Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
2917 , x_op_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
2918 , x_return_status IN OUT NOCOPY VARCHAR2
2919 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2920 )
2921
2922 IS
2923 l_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
2924 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type ;
2925 l_op_resource_rec Bom_Rtg_Pub.Op_Resource_Rec_Type ;
2926
2927 BEGIN
2928 -- Convert Routing Operation to ECO Operation
2929 Bom_Rtg_Pub.Convert_RtgRes_To_EcoRes
2930 ( p_rtg_op_resource_rec => p_op_resource_rec
2931 , p_rtg_op_res_unexp_rec => p_op_res_unexp_rec
2932 , x_rev_op_resource_rec => l_rev_op_resource_rec
2933 , x_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2934 ) ;
2935
2936 -- Call Rev_Op_resource_UUI_To_UI
2937 Bom_Rtg_Val_To_Id.Rev_Op_Resource_VID
2938 ( p_rev_op_resource_rec => l_rev_op_resource_rec
2939 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2940 , x_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2941 , x_return_status => x_return_status
2942 , x_mesg_token_tbl => x_mesg_token_tbl
2943 ) ;
2944
2945 -- Convert old Eco Opeartion Record back to Routing Operation
2946 Bom_Rtg_Pub.Convert_EcoRes_To_RtgRes
2947 ( p_rev_op_resource_rec => l_rev_op_resource_rec
2948 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2949 , x_rtg_op_resource_rec => l_op_resource_rec
2950 , x_rtg_op_res_unexp_rec => x_op_res_unexp_rec
2951 ) ;
2952
2953
2954 END Op_Resource_VID ;
2955
2956
2957 /*********************************************************************
2958 * Procedure : Rev_Op_Resource_VID
2959 * Returns : None
2960 * Parameters IN : Operation resource exposed Record
2961 * Operation resource Unexposed Record
2962 * Parameters OUT: Operation resoruce Unexposed Record
2963 * Return Status
2964 * Message Token Table
2965 * Purpose : This is the access procedure which the private API
2966 * will call to perform the operation record value to ID
2967 * conversions. If any of the conversions fail then the
2968 * the procedure will return with an error status and
2969 * the messsage token table filled with appropriate
2970 * error message.
2971 *********************************************************************/
2972 PROCEDURE Rev_Op_Resource_VID
2973 ( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
2974 , p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
2975 , x_rev_op_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
2976 , x_Return_Status IN OUT NOCOPY VARCHAR2
2977 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2978 )
2979 IS
2980 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
2981 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2982 l_err_text VARCHAR2(2000);
2983 l_Token_Tbl Error_Handler.Token_Tbl_Type;
2984 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
2985 := p_rev_op_res_unexp_rec ;
2986 BEGIN
2987
2988 IF BOM_Rtg_Globals.Get_Debug = 'Y'
2989 THEN Error_Handler.Write_Debug('Resource VID conversion . . .');
2990 END IF;
2991
2992 --
2993 -- Convert resource code to reosurce id
2994 --
2995 IF p_rev_op_resource_rec.resource_code IS NOT NULL AND
2996 p_rev_op_resource_rec.resource_code <>
2997 FND_API.G_MISS_CHAR
2998 THEN
2999
3000 l_rev_op_res_unexp_rec.resource_id :=
3001 Resource_Id
3002 ( p_resource_code =>
3003 p_rev_op_resource_rec.resource_code
3004 , p_organization_id =>
3005 l_rev_op_res_unexp_rec.organization_id
3006 , x_err_text => l_err_text
3007 );
3008
3009
3010 IF l_rev_op_res_unexp_rec.resource_id IS NULL
3011 THEN
3012 l_token_tbl(1).token_name := 'RESOURCE_CODE';
3013 l_token_tbl(1).token_value :=
3014 p_rev_op_resource_rec.resource_code;
3015 Error_Handler.Add_Error_Token
3016 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3017 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3018 , p_Message_name =>
3019 'BOM_RES_RESCODE_INVALID'
3020 , p_token_tbl => l_token_tbl
3021 );
3022 l_return_status := FND_API.G_RET_STS_ERROR;
3023 ELSIF l_err_text IS NOT NULL AND
3024 l_rev_op_res_unexp_rec.resource_id IS NULL
3025 THEN
3026 Error_Handler.Add_Error_Token
3027 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3028 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3029 , p_Message_text =>
3030 'Unexpected Error ' || l_err_text || ' in ' ||
3031 G_PKG_NAME
3032 , p_token_tbl => l_token_tbl
3033 );
3034 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
3035 END IF;
3036
3037 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3038 ('Converted resource code . . .');
3039 END IF;
3040
3041 END IF;
3042
3043
3044 --
3045 -- Convert activity to activity id
3046 --
3047
3048 IF p_rev_op_resource_rec.activity IS NOT NULL AND
3049 p_rev_op_resource_rec.activity <> FND_API.G_MISS_CHAR
3050 THEN
3051 l_rev_op_res_unexp_rec.activity_id :=
3052 Activity_Id
3053 ( p_activity =>
3054 p_rev_op_resource_rec.activity
3055 , p_organization_id =>
3056 l_rev_op_res_unexp_rec.organization_id
3057 , x_err_text => l_err_text
3058 );
3059
3060 IF l_rev_op_res_unexp_rec.activity_id IS NULL
3061 THEN
3062 l_token_tbl(1).token_name := 'ACTIVITY';
3063 l_token_tbl(1).token_value :=
3064 p_rev_op_resource_rec.activity;
3065 Error_Handler.Add_Error_Token
3066 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3067 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3068 , p_Message_name =>
3069 'BOM_RES_ACTIVITY_CODE_INVALID'
3070 , p_token_tbl => l_token_tbl
3071 );
3072 l_return_status := FND_API.G_RET_STS_ERROR;
3073 ELSIF l_err_text IS NOT NULL AND
3074 l_rev_op_res_unexp_rec.activity_id IS NULL
3075 THEN
3076 Error_Handler.Add_Error_Token
3077 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3078 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3079 , p_Message_text =>
3080 'Unexpected Error ' || l_err_text || ' in ' ||
3081 G_PKG_NAME
3082 , p_token_tbl => l_token_tbl
3083 );
3084 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
3085 END IF;
3086
3087 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3088 ('Converted activity. . .');
3089 END IF;
3090
3091
3092 END IF;
3093
3094 --
3095 -- Convert setup code to setup id
3096 --
3097 IF p_rev_op_resource_rec.setup_type IS NOT NULL AND
3098 p_rev_op_resource_rec.setup_type <> FND_API.G_MISS_CHAR
3099 THEN
3100 l_rev_op_res_unexp_rec.setup_id :=
3101 Setup_Id
3102 ( p_setup_type =>
3103 p_rev_op_resource_rec.setup_type
3104 , p_organization_id =>
3105 l_rev_op_res_unexp_rec.organization_id
3106 , x_err_text => l_err_text
3107 );
3108
3109 IF l_rev_op_res_unexp_rec.setup_id IS NULL
3110 THEN
3111 l_token_tbl(1).token_name := 'SETUP_CODE';
3112 l_token_tbl(1).token_value :=
3113 p_rev_op_resource_rec.setup_type ;
3114 Error_Handler.Add_Error_Token
3115 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3116 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3117 , p_Message_name =>
3118 'BOM_RES_SETUP_CODE_INVALID'
3119 , p_token_tbl => l_token_tbl
3120 );
3121 l_return_status := FND_API.G_RET_STS_ERROR;
3122 ELSIF l_err_text IS NOT NULL AND
3123 l_rev_op_res_unexp_rec.setup_id IS NULL
3124 THEN
3125 Error_Handler.Add_Error_Token
3126 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3127 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3128 , p_Message_text =>
3129 'Unexpected Error ' || l_err_text || ' in ' ||
3130 G_PKG_NAME
3131 , p_token_tbl => l_token_tbl
3132 );
3133 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
3134 END IF;
3135
3136 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3137 ('Converted setup code . . .');
3138 END IF;
3139
3140 END IF;
3141
3142
3143 x_return_status := l_return_status;
3144
3145 IF Bom_Rtg_Globals.Get_Debug = 'Y'
3146 THEN Error_Handler.Write_Debug
3147 ('Return status of resource VID: ' || l_return_status );
3148 END IF;
3149
3150 x_rev_op_res_unexp_rec := l_rev_op_res_unexp_rec;
3151 x_mesg_token_tbl := l_mesg_token_tbl;
3152
3153 END Rev_Op_Resource_VID;
3154
3155
3156 /*********************************************************************
3157 * Procedure : Sub_Resource_UUI_To_UI
3158 * Returns : None
3159 * Parameters IN : Substitute resource exposed Record
3160 * Substitute resource Unexposed Record
3161 * Parameters OUT: Substitute resource unexposed record
3162 * Message Token Table
3163 * Return Status
3164 * Purpose : Convert Routing Sub Op Resource to Revised Sub Op
3165 * Resource and Call Rev_Sub_resource_UUI_To_UI for ECO Bo.
3166 * After calling Rev_Sub_resource_UUI_To_UI, convert
3167 * Revised Op Resource record back to Routing Op Resource
3168 *********************************************************************/
3169 PROCEDURE Sub_Resource_UUI_To_UI
3170 ( p_sub_resource_rec IN Bom_Rtg_Pub.Sub_Resource_Rec_Type
3171 , p_sub_res_unexp_rec IN Bom_Rtg_Pub.Sub_Res_Unexposed_Rec_Type
3172 , x_sub_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Sub_Res_Unexposed_Rec_Type
3173 , x_return_status IN OUT NOCOPY VARCHAR2
3174 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3175 )
3176
3177 IS
3178 l_rev_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type ;
3179 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type ;
3180 l_sub_resource_rec Bom_Rtg_Pub.Sub_Resource_Rec_Type ;
3181
3182 BEGIN
3183 -- Convert Routing Operation to ECO Operation
3184 Bom_Rtg_Pub.Convert_RtgSubRes_To_EcoSubRes
3185 ( p_rtg_sub_resource_rec => p_sub_resource_rec
3186 , p_rtg_sub_res_unexp_rec => p_sub_res_unexp_rec
3187 , x_rev_sub_resource_rec => l_rev_sub_resource_rec
3188 , x_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3189 ) ;
3190
3191 -- Call Rev_Sub_Resource_UUI_To_UI
3192 Bom_Rtg_Val_To_Id.Rev_Sub_Resource_UUI_To_UI
3193 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
3194 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3195 , x_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3196 , x_return_status => x_return_status
3197 , x_mesg_token_tbl => x_mesg_token_tbl
3198 ) ;
3199
3200 -- Convert Eco Sub Resource Record back to Routing Sub Resource
3201 Bom_Rtg_Pub.Convert_EcoSubRes_To_RtgSubRes
3202 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
3203 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3204 , x_rtg_sub_resource_rec => l_sub_resource_rec
3205 , x_rtg_sub_res_unexp_rec => x_sub_res_unexp_rec
3206 ) ;
3207
3208
3209 END Sub_Resource_UUI_To_UI ;
3210
3211
3212
3213 /*********************************************************************
3214 * Procedure : Rev_Sub_Resource_UUI_To_UI
3215 * Returns : None
3216 * Parameters IN : Revised Substitute Resource Exposed Record
3217 * Revised Substitute Resource Unexposed Record
3218 * Parameters OUT: Revised Substitute Resource unexposed record
3219 * Message Token Table
3220 * Return Status
3221 * Purpose : This procedure will perform all the required
3222 * User unique to Unique index conversions for
3223 * substitute resource record. Any errors will be
3224 * logged in the Message table and a return satus of
3225 * success or failure will be returned to the calling
3226 * program.
3227 *********************************************************************/
3228 PROCEDURE Rev_Sub_Resource_UUI_To_UI
3229 ( p_rev_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type
3230 , p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
3231 , x_rev_sub_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
3232 , x_return_status IN OUT NOCOPY VARCHAR2
3233 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3234 )
3235 IS
3236 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3237 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type;
3238 l_return_status VARCHAR2(1);
3239 l_err_text VARCHAR2(2000);
3240 l_cfm_flag NUMBER;
3241 l_Token_Tbl Error_Handler.Token_Tbl_Type;
3242 l_err_text_diff VARCHAR2(1);
3243
3244 CURSOR c_Check_Alternate( p_alt_designator VARCHAR2,
3245 p_organization_id NUMBER ) IS
3246 SELECT 1
3247 FROM bom_alternate_designators
3248 WHERE alternate_designator_code = p_alt_designator
3249 AND organization_id = p_organization_id;
3250
3251 BEGIN
3252 x_return_status := FND_API.G_RET_STS_SUCCESS;
3253 l_return_status := FND_API.G_RET_STS_SUCCESS;
3254 l_rev_sub_res_unexp_rec := p_rev_sub_res_unexp_rec ;
3255
3256 IF BOM_Rtg_Globals.Get_Debug = 'Y'
3257 THEN Error_Handler.Write_Debug
3258 ('Substitute resource record UUI-UI Conversion . . ');
3259 END IF;
3260
3261 --
3262 -- Assembly Item name cannot be NULL or missing.
3263 --
3264 IF p_rev_sub_resource_rec.revised_item_name IS NULL OR
3265 p_rev_sub_resource_rec.revised_item_name = FND_API.G_MISS_CHAR
3266 THEN
3267 Error_Handler.Add_Error_Token
3268 ( p_message_name => 'BOM_SUB_RES_AITEM_KEYCOL_NULL'
3269 , p_mesg_token_tbl => l_mesg_token_tbl
3270 , x_mesg_token_tbl => l_mesg_token_tbl
3271 );
3272
3273 l_return_status := FND_API.G_RET_STS_ERROR;
3274 END IF;
3275
3276 /* bug:4714714 - Commented the below check on Alternate routing code as
3277 creation/updation of alternate resources should be allowed for alternate routings also.
3278 --
3279 -- Alternate routing code should be NULL or missing.
3280 --
3281 IF p_rev_sub_resource_rec.alternate_routing_code IS NOT NULL AND
3282 p_rev_sub_resource_rec.alternate_routing_code <> FND_API.G_MISS_CHAR
3283 THEN
3284 Error_Handler.Add_Error_Token
3285 ( p_Message_Name => 'BOM_SUB_RES_ALTER_CD_NOT_NULL'
3286 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3287 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3288 );
3289 l_Return_Status := FND_API.G_RET_STS_ERROR;
3290 END IF;
3291 */
3292
3293 --
3294 --
3295 -- Sub resource code cannot be NULL or missing.
3296 -- Sub resource code should exist in BOM_RESOURCE
3297 --
3298 IF p_rev_sub_resource_rec.sub_resource_code IS NULL OR
3299 p_rev_sub_resource_rec.sub_resource_code = FND_API.G_MISS_CHAR
3300 THEN
3301 Error_Handler.Add_Error_Token
3302 ( p_message_name => 'BOM_SUB_RES_CODE_KEYCOL_NULL'
3303 , p_mesg_token_tbl => l_mesg_token_tbl
3304 , x_mesg_token_tbl => l_mesg_token_tbl
3305 );
3306
3307 l_return_status := FND_API.G_RET_STS_ERROR;
3308
3309 END IF;
3310
3311 --
3312 --
3313 -- Schedule sequence number cannot be NULL or missing.
3314 --
3315 -- IF nvl(BOM_Globals.Get_Caller_Type,'') <> 'MIGRATION' THEN
3316 IF nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number) IS NULL OR
3317 nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number)
3318 = FND_API.G_MISS_NUM
3319 THEN
3320 Error_Handler.Add_Error_Token
3321 ( p_message_name => 'BOM_SUB_RES_SCHDNM_KEYCOL_NULL'
3322 , p_mesg_token_tbl => l_mesg_token_tbl
3323 , x_mesg_token_tbl => l_mesg_token_tbl
3324 );
3325
3326 l_return_status := FND_API.G_RET_STS_ERROR;
3327 END IF;
3328 -- END IF; --migration
3329
3330 --
3331 --
3332 -- Operation sequence number cannot be NULL or missing.
3333 --
3334 IF p_rev_sub_resource_rec.operation_sequence_number IS NULL OR
3335 p_rev_sub_resource_rec.operation_sequence_number
3336 = FND_API.G_MISS_NUM
3337 THEN
3338 Error_Handler.Add_Error_Token
3339 ( p_message_name => 'BOM_SUB_RES_OP_SQNM_KEYCOL_NULL'
3340 , p_mesg_token_tbl => l_mesg_token_tbl
3341 , x_mesg_token_tbl => l_mesg_token_tbl
3342 );
3343
3344 l_return_status := FND_API.G_RET_STS_ERROR;
3345 END IF;
3346
3347 --
3348 --
3349 -- Start effective date cannot be NULL or missing.
3350 --
3351 IF p_rev_sub_resource_rec.op_start_effective_date IS NULL OR
3352 p_rev_sub_resource_rec.op_start_effective_date
3353 = FND_API.G_MISS_DATE
3354 THEN
3355 Error_Handler.Add_Error_Token
3356 ( p_message_name =>
3357 'BOM_SUB_RES_EFFDT_KEYCOL_NULL'
3358 , p_mesg_token_tbl => l_mesg_token_tbl
3359 , x_mesg_token_tbl => l_mesg_token_tbl
3360 );
3361
3362 l_return_status := FND_API.G_RET_STS_ERROR;
3363 END IF;
3364
3365 IF BOM_Rtg_Globals.Get_Debug = 'Y'
3366 THEN Error_Handler.Write_Debug
3367 ('Finish Substitute resource record Key Col check. . ');
3368 END IF;
3369
3370 --
3371 -- If key columns are NULL, then return.
3372 --
3373 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3374 THEN
3375 x_Return_Status := FND_API.G_RET_STS_ERROR;
3376 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3377 RETURN;
3378 END IF;
3379
3380 --
3381 -- Assembly item name must be successfully converted to id.
3382 --
3383
3384 l_rev_sub_res_unexp_rec.revised_item_id :=
3385 Revised_Item ( p_revised_item_num =>
3386 p_rev_sub_resource_rec.revised_item_name
3387 , p_organization_id =>
3388 l_rev_sub_res_unexp_rec.organization_id
3389 , x_err_text => l_err_text
3390 );
3391
3392 IF l_rev_sub_res_unexp_rec.revised_item_id IS NULL
3393 THEN
3394 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
3395 g_token_tbl(1).token_value :=
3396 p_rev_sub_resource_rec.revised_item_name;
3397 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
3398 g_token_tbl(2).token_value :=
3399 p_rev_sub_resource_rec.organization_code;
3400 Error_Handler.Add_Error_Token
3401 ( p_Message_Name => 'BOM_RTG_AITEM_DOESNOT_EXIST'
3402 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3403 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3404 , p_Token_Tbl => g_Token_Tbl
3405 );
3406 l_Return_Status := FND_API.G_RET_STS_ERROR;
3407 ELSIF l_err_text IS NOT NULL AND
3408 ( l_rev_sub_res_unexp_rec.revised_item_id IS NULL OR
3409 l_rev_sub_res_unexp_rec.revised_item_id = FND_API.G_MISS_NUM)
3410 THEN
3411 -- This is an unexpected error.
3412 Error_Handler.Add_Error_Token
3413 ( p_Message_Name => NULL
3414 , p_Message_Text => l_err_text || ' in ' ||
3415 G_PKG_NAME
3416 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3417 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3418 );
3419 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3420 END IF;
3421
3422 IF BOM_Rtg_Globals.Get_Debug = 'Y'
3423 THEN Error_Handler.Write_Debug
3424 ('After converting Assembly Item Id : '
3425 || to_char(l_rev_sub_res_unexp_rec.revised_item_id)
3426 || ' Status ' || l_return_status);
3427 END IF;
3428
3429 IF p_rev_sub_resource_rec.alternate_routing_code IS NOT NULL AND
3430 p_rev_sub_resource_rec.alternate_routing_code <> FND_API.G_MISS_CHAR
3431 THEN
3432 /*l_err_text_diff*/l_err_text := FND_API.G_RET_STS_ERROR;
3433
3434 FOR check_alternate IN
3435 c_Check_Alternate
3436 ( p_alt_designator =>
3437 p_rev_sub_resource_rec.alternate_routing_code,
3438 p_organization_id =>
3439 l_rev_sub_res_unexp_rec.organization_id )
3440 LOOP
3441 /*l_err_text_diff*/l_err_text := FND_API.G_RET_STS_SUCCESS;
3442 END LOOP;
3443
3444 IF /*l_err_text_diff*/l_err_text <> FND_API.G_RET_STS_SUCCESS
3445 THEN
3446 g_token_tbl(1).token_name :='ALTERNATE_ROUTING_CODE';
3447 g_token_tbl(1).token_value :=
3448 p_rev_sub_resource_rec.alternate_routing_code;
3449 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
3450 g_token_tbl(2).token_value :=
3451 p_rev_sub_resource_rec.organization_code;
3452 Error_Handler.Add_Error_Token
3453 ( P_Message_Name =>
3454 'BOM_RTG_ALT_DESIGNATOR_INVALID'
3455 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3456 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3457 , p_token_tbl => g_token_tbl
3458 );
3459
3460 l_return_status := FND_API.G_RET_STS_ERROR;
3461 END IF;
3462
3463 END IF;
3464
3465
3466 /****************************************************************
3467 --
3468 -- Using the revised item key information, get the routing_sequence_id
3469 -- and revised item sequence id
3470 --
3471 ****************************************************************/
3472 IF BOM_Rtg_Globals.Get_Bo_Identifier <> BOM_Rtg_Globals.G_RTG_BO
3473 THEN
3474 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3475 Error_Handler.Write_Debug('Processing UUI_UI for sub op resources and retrieving rev item seq id . . . ');
3476 END IF;
3477
3478 NULL ;
3479
3480 /****************************************************************
3481 -- Comment out by MK on 12/04/00 to resolve Eco dependency
3482 l_rev_sub_res_unexp_rec.revised_item_sequence_id :=
3483 RtgAndRevItemSeq
3484 ( p_revised_item_id => l_rev_sub_res_unexp_rec.revised_item_id
3485 , p_item_revision => p_rev_sub_resource_rec.new_revised_item_revision
3486 , p_effective_date => p_rev_sub_resource_rec.op_start_effective_date
3487 , p_change_notice => p_rev_sub_resource_rec.eco_name
3488 , p_organization_id => l_rev_sub_res_unexp_rec.organization_id
3489 , p_new_routing_revision => p_rev_sub_resource_rec.new_routing_revision
3490 , p_from_end_item_number => p_rev_sub_resource_rec.from_end_item_unit_number
3491 , x_routing_sequence_id => l_rev_sub_res_unexp_rec.routing_sequence_id
3492 );
3493
3494 IF l_rev_sub_res_unexp_rec.revised_item_sequence_id IS NULL
3495 THEN
3496 g_Token_Tbl(1).token_name := 'SUB_RESOURCE_CODE';
3497 g_Token_Tbl(1).token_value := p_rev_sub_resource_rec.sub_resource_code ;
3498 g_Token_Tbl(2).token_name := 'SCHEDULE_SEQ_NUMBER';
3499 g_Token_Tbl(2).token_value := p_rev_sub_resource_rec.schedule_sequence_number ;
3500 g_Token_Tbl(3).Token_Name := 'REVISED_ITEM_NAME';
3501 g_Token_Tbl(3).Token_Value := p_rev_sub_resource_rec.revised_item_name;
3502 g_token_tbl(4).token_name := 'ECO_NAME';
3503 g_token_tbl(4).token_value := p_rev_sub_resource_rec.eco_name;
3504
3505 Error_Handler.Add_Error_Token
3506 ( p_Message_Name => 'BOM_SUB_RES_RIT_SEQ_NOT_FOUND'
3507 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3508 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3509 , p_Token_Tbl => g_Token_Tbl
3510 );
3511
3512 l_Return_Status := FND_API.G_RET_STS_ERROR;
3513 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3514 x_rev_sub_res_unexp_rec := l_rev_sub_res_unexp_rec;
3515 x_Return_Status := l_Return_Status;
3516
3517 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('And this call returned with ' || l_Return_Status); END IF;
3518
3519 RETURN;
3520
3521 END IF;
3522
3523 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3524 Error_Handler.Write_Debug('Revised Item Sequence Id : ' || to_char(l_rev_sub_res_unexp_rec.revised_item_sequence_id)) ;
3525 END IF ;
3526 ****************************************************************/
3527
3528 ELSE
3529
3530 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3531 Error_Handler.Write_Debug('Processing UUI_UI for sub op res . . . ');
3532 END IF;
3533 --
3534 -- If the calling BO is RTG then get the routing sequence id
3535 --
3536
3537
3538 l_rev_sub_res_unexp_rec.routing_sequence_id :=
3539 Routing_Sequence_id
3540 ( p_assembly_item_id =>
3541 l_rev_sub_res_unexp_rec.revised_item_id
3542 , p_organization_id =>
3543 l_rev_sub_res_unexp_rec.organization_id
3544 , p_alternate_routing_designator =>
3545 p_rev_sub_resource_rec.alternate_routing_code
3546 , x_err_text => l_err_text
3547 );
3548
3549 IF l_rev_sub_res_unexp_rec.routing_sequence_id IS NULL
3550 THEN
3551 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
3552 g_token_tbl(1).token_value :=
3553 p_rev_sub_resource_rec.revised_item_name;
3554 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
3555 g_token_tbl(2).token_value :=
3556 p_rev_sub_resource_rec.organization_code;
3557 Error_Handler.Add_Error_Token
3558 ( p_Message_Name => 'BOM_SUB_RES_RTG_NOT_FOUND'
3559 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3560 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3561 , p_Token_Tbl => g_Token_Tbl
3562 );
3563 l_Return_Status := FND_API.G_RET_STS_ERROR;
3564 ELSIF l_err_text IS NOT NULL AND
3565 (l_rev_sub_res_unexp_rec.routing_sequence_id IS NULL OR
3566 l_rev_sub_res_unexp_rec.routing_sequence_id = FND_API.G_MISS_NUM)
3567 THEN
3568 -- This is an unexpected error.
3569 Error_Handler.Add_Error_Token
3570 ( p_Message_Name => NULL
3571 , p_Message_Text => l_err_text || ' in ' ||
3572 G_PKG_NAME
3573 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3574 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3575 );
3576 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3577 END IF;
3578
3579 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3580 Error_Handler.Write_Debug('After converting, routing sequence id is '
3581 || to_char(l_rev_sub_res_unexp_rec.routing_sequence_id )
3582 || ' Status ' || l_return_status);
3583 END IF;
3584
3585 END IF ;
3586
3587 -- Added by MK on 12/04/00
3588 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_RTG_BO
3589 THEN
3590
3591 --
3592 -- For flow routing, operatoin Type should be set in (1, 2, 3)
3593 -- For non flow routing,
3594
3595 l_cfm_flag := BOM_Rtg_Globals.Get_CFM_Rtg_Flag ;
3596 IF l_cfm_flag IS NULL OR
3597 l_cfm_flag = FND_API.G_MISS_NUM
3598 THEN
3599 l_cfm_flag := Bom_Rtg_Validate.Get_Flow_Routing_Flag(
3600 p_routing_sequence_id =>
3601 l_rev_sub_res_unexp_rec.routing_sequence_id ) ;
3602 BOM_Rtg_Globals.Set_CFM_Rtg_Flag(p_cfm_rtg_type => l_cfm_flag) ;
3603 END IF;
3604
3605 IF l_cfm_flag = BOM_Rtg_Globals.G_FLOW_RTG
3606 THEN
3607 IF p_rev_sub_resource_rec.operation_type IS NULL
3608 OR p_rev_sub_resource_rec.operation_type = FND_API.G_MISS_NUM
3609 OR p_rev_sub_resource_rec.operation_type NOT IN (1, 2, 3)
3610 THEN
3611 IF p_rev_sub_resource_rec.operation_type <> FND_API.G_MISS_NUM
3612 THEN
3613 g_token_tbl(1).token_name := 'OPERATION_TYPE';
3614 g_token_tbl(1).token_value :=
3615 p_rev_sub_resource_rec.operation_type;
3616 END IF ;
3617
3618 Error_Handler.Add_Error_Token
3619 ( p_Message_Name => 'BOM_SUB_RES_FLM_OP_TYP_INVALID'
3620 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3621 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3622 , p_Token_Tbl => g_Token_Tbl
3623 );
3624 l_return_status := FND_API.G_RET_STS_ERROR ;
3625
3626 END IF ;
3627 ELSE
3628 IF p_rev_sub_resource_rec.operation_type IS NOT NULL
3629 AND p_rev_sub_resource_rec.operation_type <> FND_API.G_MISS_NUM
3630 AND p_rev_sub_resource_rec.operation_type <> 1
3631 THEN
3632 g_token_tbl(1).token_name := 'OPERATION_TYPE';
3633 g_token_tbl(1).token_value :=
3634 p_rev_sub_resource_rec.operation_type;
3635 Error_Handler.Add_Error_Token
3636 ( p_Message_Name => 'BOM_SUB_RES_STD_OP_TYP_IGNORED'
3637 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3638 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3639 , p_Token_Tbl => g_Token_Tbl
3640 );
3641
3642 l_return_status := FND_API.G_RET_STS_ERROR ;
3643 END IF ;
3644
3645 END IF;
3646
3647 --
3648 -- convert to operation sequence id
3649 --
3650
3651 l_rev_sub_res_unexp_rec.operation_sequence_id :=
3652 Operation_Sequence_id
3653 ( p_routing_sequence_id =>
3654 l_rev_sub_res_unexp_rec.routing_sequence_id
3655 , p_operation_type =>
3656 p_rev_sub_resource_rec.operation_type
3657 , p_operation_seq_num =>
3658 p_rev_sub_resource_rec.operation_sequence_number
3659 , p_effectivity_date =>
3660 p_rev_sub_resource_rec.op_start_effective_date
3661 , x_err_text => l_err_text
3662 );
3663
3664 IF l_rev_sub_res_unexp_rec.operation_sequence_id IS NULL
3665 THEN
3666 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3667 g_token_tbl(1).token_value :=
3668 p_rev_sub_resource_rec.operation_sequence_number;
3669
3670 Error_Handler.Add_Error_Token
3671 ( p_Message_Name => 'BOM_SUB_RES_OP_NOT_FOUND'
3672 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3673 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3674 , p_Token_Tbl => g_Token_Tbl
3675 );
3676 l_Return_Status := FND_API.G_RET_STS_ERROR;
3677 ELSIF l_err_text IS NOT NULL AND
3678 (
3679 l_rev_sub_res_unexp_rec.operation_sequence_id IS NULL OR
3680 l_rev_sub_res_unexp_rec.operation_sequence_id =FND_API.G_MISS_NUM
3681 )
3682 THEN
3683 -- This is an unexpected error.
3684 Error_Handler.Add_Error_Token
3685 ( p_Message_Name => NULL
3686 , p_Message_Text => l_err_text || ' in ' ||
3687 G_PKG_NAME
3688 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3689 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3690 );
3691 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3692 END IF;
3693
3694 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3695 Error_Handler.Write_Debug('After converting, op sequence id is '
3696 || to_char(l_rev_sub_res_unexp_rec.operation_sequence_id )
3697 || ' Status ' || l_return_status);
3698 END IF;
3699
3700 --
3701 -- convert resource code to resource id
3702 --
3703 IF p_rev_sub_resource_rec.sub_resource_code IS NOT NULL AND
3704 p_rev_sub_resource_rec.sub_resource_code <> FND_API.G_MISS_CHAR
3705 THEN
3706 l_rev_sub_res_unexp_rec.resource_id :=
3707 Resource_Id
3708 ( p_resource_code =>
3709 p_rev_sub_resource_rec.sub_resource_code
3710 , p_organization_id =>
3711 l_rev_sub_res_unexp_rec.organization_id
3712 , x_err_text => l_err_text
3713 );
3714
3715 IF l_rev_sub_res_unexp_rec.resource_id IS NULL
3716 THEN
3717 l_token_tbl(1).token_name :=
3718 'RESOURCE_CODE';
3719 l_token_tbl(1).token_value :=
3720 p_rev_sub_resource_rec.sub_resource_code;
3721 Error_Handler.Add_Error_Token
3722 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3723 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3724 , p_Message_name =>
3725 'BOM_SUB_RES_SUB_RES_CD_INVALID'
3726 , p_token_tbl => l_token_tbl
3727 );
3728 l_return_status := FND_API.G_RET_STS_ERROR;
3729 ELSIF l_err_text IS NOT NULL AND
3730 l_rev_sub_res_unexp_rec.resource_id IS NULL
3731 THEN
3732 Error_Handler.Add_Error_Token
3733 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3734 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3735 , p_Message_text =>
3736 'Unexpected Error ' || l_err_text || ' in ' ||
3737 G_PKG_NAME
3738 , p_token_tbl => l_token_tbl
3739 );
3740 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
3741 END IF;
3742 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3743 Error_Handler.Write_Debug('After converting, Resource Id is '
3744 || to_char(l_rev_sub_res_unexp_rec.resource_id)
3745 || ' Status ' || l_return_status);
3746 END IF;
3747 END IF;
3748
3749 --
3750 -- Set substitute group number
3751 --
3752 --l_rev_sub_res_unexp_rec.substitute_group_number :=
3753 -- p_rev_sub_resource_rec.schedule_sequence_number;
3754
3755 IF nvl(Bom_Globals.Get_Caller_Type,'') <> 'MIGRATION' THEN
3756 IF not Bom_Rtg_Validate.group_num_exist_In_Op_Res
3757 ( p_substitute_group_number =>
3758 p_rev_sub_resource_rec.substitute_group_number
3759 , p_operation_sequence_id =>
3760 l_rev_sub_res_unexp_rec.operation_sequence_id )
3761 THEN
3762 l_token_tbl(1).token_name :=
3763 'SUBSTITUTE_GROUP_NUMBER';
3764 l_token_tbl(1).token_value :=
3765 p_rev_sub_resource_rec.substitute_group_number ;
3766 Error_Handler.Add_Error_Token
3767 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3768 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3769 , p_Message_name =>
3770 'BOM_SUB_RES_RELRES_NOT_FOUND'
3771 , p_token_tbl => l_token_tbl
3772 );
3773 l_return_status := FND_API.G_RET_STS_ERROR;
3774 END IF;
3775 END IF;
3776
3777 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3778 Error_Handler.Write_Debug('After set substitute group num '
3779 || to_char(nvl(p_rev_sub_resource_rec.substitute_group_number, l_rev_sub_res_unexp_rec.substitute_group_number))
3780 || ' verify it. Status ' || l_return_status);
3781 END IF;
3782
3783 END IF; -- Added by MK on 12/04/00 BOM_Rtg_Globals.G_RTG_BO specific
3784
3785 x_return_status := l_return_status;
3786 x_rev_sub_res_unexp_rec := l_rev_sub_res_unexp_rec;
3787 x_mesg_token_tbl := l_mesg_token_tbl;
3788
3789 END Rev_Sub_Resource_UUI_To_UI;
3790
3791
3792 /*********************************************************************
3793 * Procedure : Sub_resource_VID
3794 * Returns : None
3795 * Parameters IN : Substitute resource exposed Record
3796 * Substitute resource Unexposed Record
3797 * Parameters OUT: Substitute resoruce Unexposed Record
3798 * Return Status
3799 * Message Token Table
3800 * Purpose : Convert Routing Sub Op Resource to Revised Sub Op
3801 * Resource and Call Rev_Sub_resource_VID for ECO Bo.
3802 * After calling Rev_Sub_resource_VID, convert
3803 * Revised Sub Op Resource record back to Routing Sub Op Resource
3804 *********************************************************************/
3805 PROCEDURE Sub_Resource_VID
3806 ( p_sub_resource_rec IN Bom_Rtg_Pub.Sub_Resource_Rec_Type
3807 , p_sub_res_unexp_rec IN Bom_Rtg_Pub.Sub_Res_Unexposed_Rec_Type
3808 , x_sub_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Sub_Res_Unexposed_Rec_Type
3809 , x_Return_Status IN OUT NOCOPY VARCHAR2
3810 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3811 )
3812
3813 IS
3814 l_rev_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type ;
3815 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type ;
3816 l_sub_resource_rec Bom_Rtg_Pub.Sub_Resource_Rec_Type ;
3817
3818 BEGIN
3819 -- Convert Routing Operation to ECO Operation
3820 Bom_Rtg_Pub.Convert_RtgSubRes_To_EcoSubRes
3821 ( p_rtg_sub_resource_rec => p_sub_resource_rec
3822 , p_rtg_sub_res_unexp_rec => p_sub_res_unexp_rec
3823 , x_rev_sub_resource_rec => l_rev_sub_resource_rec
3824 , x_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3825 ) ;
3826
3827 -- Call Rev_Sub_resource_UUI_To_UI
3828 Bom_Rtg_Val_To_Id.Rev_Sub_resource_VID
3829 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
3830 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3831 , x_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3832 , x_return_status => x_return_status
3833 , x_mesg_token_tbl => x_mesg_token_tbl
3834 ) ;
3835
3836 -- Convert Eco Sub Resource Record back to Routing Sub Resource
3837 Bom_Rtg_Pub.Convert_EcoSubRes_To_RtgSubRes
3838 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
3839 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3840 , x_rtg_sub_resource_rec => l_sub_resource_rec
3841 , x_rtg_sub_res_unexp_rec => x_sub_res_unexp_rec
3842 ) ;
3843
3844
3845 END Sub_Resource_VID ;
3846
3847
3848 /*********************************************************************
3849 * Procedure : Rev_Sub_resource_VID
3850 * Returns : None
3851 * Parameters IN : Rev Substitute resource exposed Record
3852 * Rev Substitute resource Unexposed Record
3853 * Parameters OUT: Rev Substitute resoruce Unexposed Record
3854 * Return Status
3855 * Message Token Table
3856 * Purpose : This is the access procedure which the private API
3857 * will call to perform the substitute resource record
3858 * value to ID conversions. If any of the conversions
3859 * fail then the the procedure will return with an error
3860 * status and the messsage token table filled with
3861 * appropriate error message.
3862 *********************************************************************/
3863 PROCEDURE Rev_Sub_Resource_VID
3864 ( p_rev_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type
3865 , p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
3866 , x_rev_sub_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
3867 , x_Return_Status IN OUT NOCOPY VARCHAR2
3868 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3869 )
3870 IS
3871 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
3872 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3873 l_err_text VARCHAR2(2000);
3874 l_Token_Tbl Error_Handler.Token_Tbl_Type;
3875 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
3876 := p_rev_sub_res_unexp_rec ;
3877 BEGIN
3878
3879 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3880 Error_Handler.Write_Debug('Substitute resource VID conversion . . .');
3881 END IF;
3882
3883 --
3884 -- Convert activity to activity id
3885 --
3886
3887 IF p_rev_sub_resource_rec.activity IS NOT NULL AND
3888 p_rev_sub_resource_rec.activity <> FND_API.G_MISS_CHAR
3889 THEN
3890
3891 l_rev_sub_res_unexp_rec.activity_id :=
3892 Activity_Id
3893 ( p_activity =>
3894 p_rev_sub_resource_rec.activity
3895 , p_organization_id =>
3896 l_rev_sub_res_unexp_rec.organization_id
3897 , x_err_text => l_err_text
3898 );
3899
3900 IF l_rev_sub_res_unexp_rec.activity_id IS NULL
3901 THEN
3902 l_token_tbl(1).token_name := 'ACTIVITY';
3903 l_token_tbl(1).token_value :=
3904 p_rev_sub_resource_rec.activity;
3905 Error_Handler.Add_Error_Token
3906 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3907 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3908 , p_Message_name =>
3909 'BOM_SUB_RES_ACT_CD_INVALID'
3910 , p_token_tbl => l_token_tbl
3911 );
3912 l_return_status := FND_API.G_RET_STS_ERROR;
3913 ELSIF l_err_text IS NOT NULL AND
3914 l_rev_sub_res_unexp_rec.activity_id IS NULL
3915 THEN
3916 Error_Handler.Add_Error_Token
3917 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3918 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3919 , p_Message_text =>
3920 'Unexpected Error ' || l_err_text || ' in ' ||
3921 G_PKG_NAME
3922 , p_token_tbl => l_token_tbl
3923 );
3924 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
3925 END IF;
3926 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3927 Error_Handler.Write_Debug('Converted activity id. '|| to_char(l_rev_sub_res_unexp_rec.activity_id) );
3928 END IF;
3929
3930 END IF;
3931
3932
3933 --
3934 -- Convert new resource code to new reosurce id
3935 --
3936 IF p_rev_sub_resource_rec.new_sub_resource_code IS NOT NULL AND
3937 p_rev_sub_resource_rec.new_sub_resource_code <>
3938 FND_API.G_MISS_CHAR
3939 THEN
3940
3941 l_rev_sub_res_unexp_rec.new_resource_id :=
3942 Resource_Id
3943 ( p_resource_code =>
3944 p_rev_sub_resource_rec.new_sub_resource_code
3945 , p_organization_id =>
3946 l_rev_sub_res_unexp_rec.organization_id
3947 , x_err_text => l_err_text
3948 );
3949
3950
3951 IF l_rev_sub_res_unexp_rec.new_resource_id IS NULL
3952 THEN
3953 l_token_tbl(1).token_name := 'RESOURCE_CODE';
3954 l_token_tbl(1).token_value :=
3955 p_rev_sub_resource_rec.new_sub_resource_code ;
3956 Error_Handler.Add_Error_Token
3957 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3958 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3959 , p_Message_name =>
3960 'BOM_SUB_RES_SUB_RES_CD_INVALID'
3961 , p_token_tbl => l_token_tbl
3962 );
3963 l_return_status := FND_API.G_RET_STS_ERROR;
3964 ELSIF l_err_text IS NOT NULL AND
3965 l_rev_sub_res_unexp_rec.new_resource_id IS NULL
3966 THEN
3967 Error_Handler.Add_Error_Token
3968 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3969 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3970 , p_Message_text =>
3971 'Unexpected Error ' || l_err_text || ' in ' ||
3972 G_PKG_NAME
3973 , p_token_tbl => l_token_tbl
3974 );
3975 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
3976 END IF;
3977
3978 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3979 ('Converted new sub resource id. ' || to_char(l_rev_sub_res_unexp_rec.new_resource_id) ) ;
3980 END IF;
3981
3982 END IF;
3983
3984 --
3985 -- Convert setup code to setup id
3986 --
3987 IF p_rev_sub_resource_rec.setup_type IS NOT NULL AND
3988 p_rev_sub_resource_rec.setup_type <> FND_API.G_MISS_CHAR
3989 THEN
3990 l_rev_sub_res_unexp_rec.setup_id :=
3991 Setup_Id
3992 ( p_setup_type =>
3993 p_rev_sub_resource_rec.setup_type
3994 , p_organization_id =>
3995 l_rev_sub_res_unexp_rec.organization_id
3996 , x_err_text => l_err_text
3997 );
3998
3999 IF l_rev_sub_res_unexp_rec.setup_id IS NULL
4000 THEN
4001 l_token_tbl(1).token_name := 'SETUP_CODE';
4002 l_token_tbl(1).token_value :=
4003 p_rev_sub_resource_rec.setup_type ;
4004 Error_Handler.Add_Error_Token
4005 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
4006 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4007 , p_Message_name =>
4008 'BOM_SUB_RES_SETUP_CODE_INVALID'
4009 , p_token_tbl => l_token_tbl
4010 );
4011 l_return_status := FND_API.G_RET_STS_ERROR;
4012 ELSIF l_err_text IS NOT NULL AND
4013 l_rev_sub_res_unexp_rec.setup_id IS NULL
4014 THEN
4015 Error_Handler.Add_Error_Token
4016 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
4017 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4018 , p_Message_text =>
4019 'Unexpected Error ' || l_err_text || ' in ' ||
4020 G_PKG_NAME
4021 , p_token_tbl => l_token_tbl
4022 );
4023 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
4024 END IF;
4025
4026 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
4027 Error_Handler.Write_Debug ('Converted setup code . . .');
4028 END IF;
4029
4030 END IF;
4031
4032
4033 x_return_status := l_return_status;
4034 x_rev_sub_res_unexp_rec := l_rev_sub_res_unexp_rec;
4035 x_mesg_token_tbl := l_mesg_token_tbl;
4036
4037 END Rev_Sub_Resource_VID;
4038
4039
4040 -- Network
4041 /*********************************************************************
4042 * Procedure : OP_Network_UUI_To_UI
4043 * Returns : None
4044 * Parameters IN : Operation Network exposed exposed Record
4045 * Operation Network Unexposed Record
4046 * Parameters OUT: Operation Network unexposed record
4047 * Message Token Table
4048 * Return Status
4049 * Purpose : This procedure will perform all the required
4050 * User unique to Unique index conversions for
4051 * operation network record. Any errors will be
4052 * logged in the Message table and a return satus of
4053 * success or failure will be returned to the calling
4054 * program.
4055 *********************************************************************/
4056 PROCEDURE OP_Network_UUI_To_UI
4057 ( p_op_network_rec IN Bom_Rtg_Pub.Op_Network_Rec_Type
4058 , p_op_network_unexp_rec IN Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
4059 , x_op_network_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
4060 , x_return_status IN OUT NOCOPY VARCHAR2
4061 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
4062 )
4063 IS
4064 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
4065 l_op_network_unexp_rec Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type;
4066 l_return_status VARCHAR2(1);
4067 l_err_text VARCHAR2(2000);
4068 l_cfm_flag NUMBER;
4069 l_err_text_diff VARCHAR2(1);
4070
4071 CURSOR c_Check_Alternate( p_alt_designator VARCHAR2,
4072 p_organization_id NUMBER ) IS
4073 SELECT 1
4074 FROM bom_alternate_designators
4075 WHERE alternate_designator_code = p_alt_designator
4076 AND organization_id = p_organization_id;
4077
4078 /***BEGIN 1838261***/
4079 x_temp_op_rec BOM_RTG_Globals.Temp_Op_Rec_Type;
4080 /***END 1838261***/
4081 l_temp_op_rec_tbl_test BOM_RTG_Globals.Temp_Op_Rec_Tbl_Type;--for testing by Dev
4082 BEGIN
4083 x_return_status := FND_API.G_RET_STS_SUCCESS;
4084 l_return_status := FND_API.G_RET_STS_SUCCESS;
4085 l_op_network_unexp_rec := p_op_network_unexp_rec;
4086
4087 IF BOM_Rtg_Globals.Get_Debug = 'Y'
4088 THEN Error_Handler.Write_Debug
4089 ('Operation network record UUI-UI Conversion . . ');
4090 END IF;
4091
4092 --
4093 -- Assembly Item name cannot be NULL or missing.
4094 --
4095 IF p_op_network_rec.assembly_item_name IS NULL OR
4096 p_op_network_rec.assembly_item_name = FND_API.G_MISS_CHAR
4097 THEN
4098 Error_Handler.Add_Error_Token
4099 ( p_message_name => 'BOM_OP_NWK_AITEM_KEYCOL_NULL'
4100 , p_mesg_token_tbl => l_mesg_token_tbl
4101 , x_mesg_token_tbl => l_mesg_token_tbl
4102 );
4103
4104 l_return_status := FND_API.G_RET_STS_ERROR;
4105 END IF;
4106
4107 --
4108 -- From operation sequence number cannot be NULL or missing.
4109 --
4110 IF p_op_network_rec.from_op_seq_number IS NULL OR
4111 p_op_network_rec.from_op_seq_number
4112 = FND_API.G_MISS_NUM
4113 THEN
4114 Error_Handler.Add_Error_Token
4115 ( p_Message_Name => 'BOM_OP_NWK_SEQNUM_KEYCOL_NULL'
4116 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4117 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4118 );
4119 l_Return_Status := FND_API.G_RET_STS_ERROR;
4120 END IF;
4121
4122 --
4123 -- To operation sequence number cannot be NULL or missing.
4124 --
4125 IF p_op_network_rec.to_op_seq_number IS NULL OR
4126 p_op_network_rec.to_op_seq_number
4127 = FND_API.G_MISS_NUM
4128 THEN
4129 Error_Handler.Add_Error_Token
4130 ( p_Message_Name => 'BOM_OP_NWK_SEQNUM_KEYCOL_NULL'
4131 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4132 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4133 );
4134 l_Return_Status := FND_API.G_RET_STS_ERROR;
4135 END IF;
4136
4137 --
4138 -- From start effective date cannot be NULL or missing.
4139 --
4140 IF ( p_op_network_rec.from_start_effective_date IS NULL OR
4141 p_op_network_rec.from_start_effective_date
4142 = FND_API.G_MISS_DATE)
4143 AND ( NVL(p_op_network_rec.operation_type,1) = 1
4144 OR p_op_network_rec.operation_type = FND_API.G_MISS_NUM )
4145 THEN
4146 Error_Handler.Add_Error_Token
4147 ( p_Message_Name => 'BOM_OP_NWK_EFFDT_KEYCOL_NULL'
4148 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4149 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4150 );
4151 l_Return_Status := FND_API.G_RET_STS_ERROR;
4152 END IF;
4153
4154 --
4155 -- To start effective date cannot be NULL or missing.
4156 --
4157 IF ( p_op_network_rec.to_start_effective_date IS NULL OR
4158 p_op_network_rec.to_start_effective_date
4159 = FND_API.G_MISS_DATE)
4160 AND ( NVL(p_op_network_rec.operation_type,1) = 1
4161 OR p_op_network_rec.operation_type = FND_API.G_MISS_NUM )
4162 THEN
4163 Error_Handler.Add_Error_Token
4164 ( p_Message_Name => 'BOM_OP_NWK_EFFDT_KEYCOL_NULL'
4165 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4166 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4167 );
4168 l_Return_Status := FND_API.G_RET_STS_ERROR;
4169 END IF;
4170
4171 --
4172 -- If key columns are NULL, then return.
4173 --
4174
4175 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4176 THEN
4177 x_Return_Status := FND_API.G_RET_STS_ERROR;
4178 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4179 RETURN;
4180 END IF;
4181
4182
4183 --
4184 -- Assembly item name must be successfully converted to id.
4185 --
4186
4187 l_op_network_unexp_rec.assembly_item_id :=
4188 Assembly_Item ( p_assembly_item_name =>
4189 p_op_network_rec.assembly_item_name
4190 , p_organization_id =>
4191 l_op_network_unexp_rec.organization_id
4192 , x_err_text => l_err_text
4193 );
4194
4195 IF l_op_network_unexp_rec.assembly_item_id IS NULL
4196 THEN
4197 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
4198 g_token_tbl(1).token_value :=
4199 p_op_network_rec.assembly_item_name;
4200 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
4201 g_token_tbl(2).token_value :=
4202 p_op_network_rec.organization_code;
4203 Error_Handler.Add_Error_Token
4204 ( p_Message_Name => 'BOM_RTG_AITEM_DOESNOT EXIST'
4205 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4206 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4207 , p_Token_Tbl => g_Token_Tbl
4208 );
4209 l_Return_Status := FND_API.G_RET_STS_ERROR;
4210 ELSIF l_err_text IS NOT NULL AND
4211 ( l_op_network_unexp_rec.assembly_item_id IS NULL OR
4212 l_op_network_unexp_rec.assembly_item_id = FND_API.G_MISS_NUM
4213 )
4214 THEN
4215 -- This is an unexpected error.
4216 Error_Handler.Add_Error_Token
4217 ( p_Message_Name => NULL
4218 , p_Message_Text => l_err_text || ' in ' ||
4219 G_PKG_NAME
4220 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4221 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4222 );
4223 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4224 END IF;
4225
4226 IF BOM_Rtg_Globals.Get_Debug = 'Y'
4227 THEN Error_Handler.Write_Debug
4228 ('After converting Assembly Item Id : '
4229 || to_char(l_op_network_unexp_rec.assembly_item_id)
4230 || ' Status ' || l_return_status);
4231 END IF;
4232
4233 IF p_op_network_rec.alternate_routing_code IS NOT NULL AND
4234 p_op_network_rec.alternate_routing_code <> FND_API.G_MISS_CHAR
4235 THEN
4236 /*l_err_text_diff*/l_err_text := FND_API.G_RET_STS_ERROR;
4237
4238 FOR check_alternate IN
4239 c_Check_Alternate
4240 ( p_alt_designator =>
4241 p_op_network_rec.alternate_routing_code,
4242 p_organization_id =>
4243 l_op_network_unexp_rec.organization_id )
4244 LOOP
4245 /*l_err_text_diff*/l_err_text := FND_API.G_RET_STS_SUCCESS;
4246 END LOOP;
4247
4248 IF /*l_err_text_diff*/l_err_text <> FND_API.G_RET_STS_SUCCESS
4249 THEN
4250 g_token_tbl(1).token_name :='ALTERNATE_ROUTING_CODE';
4251 g_token_tbl(1).token_value :=
4252 p_op_network_rec.alternate_routing_code;
4253 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
4254 g_token_tbl(2).token_value :=
4255 p_op_network_rec.organization_code;
4256 Error_Handler.Add_Error_Token
4257 ( P_Message_Name =>
4258 'BOM_RTG_ALT_DESIGNATOR_INVALID'
4259 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4260 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4261 , p_token_tbl => g_token_tbl
4262 );
4263
4264 l_return_status := FND_API.G_RET_STS_ERROR;
4265 END IF;
4266
4267 END IF;
4268
4269 l_op_network_unexp_rec.routing_sequence_id :=
4270 Routing_Sequence_id
4271 ( p_assembly_item_id =>
4272 l_op_network_unexp_rec.assembly_item_id
4273 , p_organization_id =>
4274 l_op_network_unexp_rec.organization_id
4275 , p_alternate_routing_designator =>
4276 p_op_network_rec.alternate_routing_code
4277 , x_err_text => l_err_text
4278 );
4279
4280 IF l_op_network_unexp_rec.routing_sequence_id IS NULL
4281 THEN
4282 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
4283 g_token_tbl(1).token_value :=
4284 p_op_network_rec.assembly_item_name;
4285 Error_Handler.Add_Error_Token
4286 ( p_Message_Name => 'BOM_OP_NWK_RTG_NOT_FOUND'
4287 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4288 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4289 , p_Token_Tbl => g_Token_Tbl
4290 );
4291 l_Return_Status := FND_API.G_RET_STS_ERROR;
4292 ELSIF l_err_text IS NOT NULL AND
4293 (l_op_network_unexp_rec.routing_sequence_id IS NULL OR
4294 l_op_network_unexp_rec.routing_sequence_id
4295 = FND_API.G_MISS_NUM)
4296 THEN
4297 -- This is an unexpected error.
4298 Error_Handler.Add_Error_Token
4299 ( p_Message_Name => NULL
4300 , p_Message_Text => l_err_text || ' in ' ||
4301 G_PKG_NAME
4302 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4303 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4304 );
4305 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4306 END IF;
4307
4308 IF BOM_Rtg_Globals.Get_Debug = 'Y'
4309 THEN Error_Handler.Write_Debug
4310 ('After converting, routing sequence id is '
4311 || to_char(l_op_network_unexp_rec.routing_sequence_id )
4312 || ' Status ' || l_return_status);
4313 END IF;
4314
4315 --
4316 -- For operation network, CFM flag should be in (1, 3)
4317 --
4318
4319 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag IS NULL OR
4320 BOM_Rtg_Globals.Get_CFM_Rtg_Flag = FND_API.G_MISS_NUM
4321 THEN
4322 l_cfm_flag := Bom_Rtg_Validate.Get_Flow_Routing_Flag(
4323 l_op_network_unexp_rec.routing_sequence_id) ;
4324 BOM_Rtg_Globals.Set_CFM_Rtg_Flag(p_cfm_rtg_type => l_cfm_flag) ;
4325
4326 ELSE l_cfm_flag := BOM_Rtg_Globals.Get_CFM_Rtg_Flag ;
4327 END IF;
4328
4329 /*
4330 -- For eAM enhancement, following cfm routing flag validation
4331 -- is moved to BOM_Validate_Op_Network.Check_Access procedure
4332 IF l_cfm_flag <> BOM_Rtg_Globals.G_FLOW_RTG
4333 AND l_cfm_flag <> BOM_Rtg_Globals.G_LOT_RTG
4334 THEN
4335 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
4336 g_token_tbl(1).token_value :=
4337 p_op_network_rec.assembly_item_name;
4338 Error_Handler.Add_Error_Token
4339 ( p_Message_Name => 'BOM_OP_NWK_RTG_INVALID'
4340 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4341 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4342 , p_Token_Tbl => g_Token_Tbl
4343 );
4344 l_return_status := FND_API.G_RET_STS_ERROR ;
4345 END IF ;
4346 */
4347
4348 --
4349 -- For flow routing, operatoin Type should be set in (2, 3)
4350 --
4351
4352 IF l_cfm_flag = BOM_Rtg_Globals.G_FLOW_RTG
4353 THEN
4354 IF p_op_network_rec.operation_type IS NULL
4355 OR p_op_network_rec.operation_type = FND_API.G_MISS_NUM
4356 OR p_op_network_rec.operation_type NOT IN (2, 3)
4357 THEN
4358 IF p_op_network_rec.operation_type <> FND_API.G_MISS_NUM
4359 THEN
4360 g_token_tbl(1).token_name := 'OPERATION_TYPE';
4361 g_token_tbl(1).token_value :=
4362 p_op_network_rec.operation_type;
4363 END IF ;
4364
4365 Error_Handler.Add_Error_Token
4366 ( p_Message_Name => 'BOM_FLM_OP_NWK_TYPE_INVALID'
4367 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4368 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4369 , p_Token_Tbl => g_Token_Tbl
4370 );
4371 l_return_status := FND_API.G_RET_STS_ERROR ;
4372
4373 END IF ;
4374
4375 ELSIF l_cfm_flag = BOM_Rtg_Globals.G_LOT_RTG
4376 OR l_cfm_flag = BOM_Rtg_Globals.G_STD_RTG
4377 THEN
4378 IF p_op_network_rec.operation_type IS NOT NULL
4379 AND p_op_network_rec.operation_type <> FND_API.G_MISS_NUM
4380 AND p_op_network_rec.operation_type <> 1
4381 THEN
4382 g_token_tbl(1).token_name := 'OPERATION_TYPE';
4383 g_token_tbl(1).token_value :=
4384 p_op_network_rec.operation_type;
4385 Error_Handler.Add_Error_Token
4386 ( p_Message_Name => 'BOM_OP_NWK_OP_TYPE_IGNORED'
4387 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4388 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4389 , p_Token_Tbl => g_Token_Tbl
4390 );
4391 l_return_status := FND_API.G_RET_STS_ERROR ;
4392 END IF ;
4393
4394 END IF;
4395
4396 --
4397 -- If error in CFM routing check, then return.
4398 --
4399
4400 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4401 THEN
4402 x_Return_Status := FND_API.G_RET_STS_ERROR;
4403 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4404 RETURN;
4405 END IF;
4406
4407
4408 --
4409 -- Get From_operation_sequence_id
4410 --
4411 -- BOM_RTG_Globals.Set_Temp_Op_Tbl(l_temp_op_rec_tbl_test);--for testing by Dev
4412
4413 l_op_network_unexp_rec.from_op_seq_id :=
4414 Operation_Sequence_id
4415 ( p_routing_sequence_id =>
4416 l_op_network_unexp_rec.routing_sequence_id
4417 , p_operation_type =>
4418 p_op_network_rec.operation_type
4419 , p_operation_seq_num =>
4420 p_op_network_rec.from_op_seq_number
4421 , p_effectivity_date =>
4422 p_op_network_rec.from_start_effective_date
4423 , x_err_text => l_err_text
4424 );
4425 /***BEGIN 1838261***/
4426
4427 IF l_op_network_unexp_rec.from_op_seq_id IS NULL THEN
4428
4429 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_Lot_Rtg THEN
4430
4431 IF BOM_RTG_Globals.Get_Temp_Op_Rec1(p_op_network_rec.from_op_seq_number, p_op_network_rec.from_start_effective_date, x_temp_op_rec) THEN
4432
4433 l_op_network_unexp_rec.from_op_seq_id :=
4434 Operation_Sequence_id
4435 ( p_routing_sequence_id =>
4436 l_op_network_unexp_rec.routing_sequence_id
4437 , p_operation_type =>
4438 p_op_network_rec.operation_type
4439 , p_operation_seq_num =>
4440 x_temp_op_rec.new_op_seq_num
4441 , p_effectivity_date =>
4442 x_temp_op_rec.new_start_eff_date
4443 , x_err_text => l_err_text
4444 );
4445 END IF;
4446 ELSE
4447 IF BOM_RTG_Globals.Get_Temp_Op_Rec(p_op_network_rec.from_op_seq_number, x_temp_op_rec) THEN
4448 l_op_network_unexp_rec.from_op_seq_id :=
4449 Operation_Sequence_id
4450 ( p_routing_sequence_id =>
4451 l_op_network_unexp_rec.routing_sequence_id
4452 , p_operation_type =>
4453 p_op_network_rec.operation_type
4454 , p_operation_seq_num =>
4455 x_temp_op_rec.new_op_seq_num
4456 , p_effectivity_date =>
4457 x_temp_op_rec.new_start_eff_date
4458 , x_err_text => l_err_text
4459 );
4460 END IF;
4461 END IF;
4462 END IF;
4463 /***END 1838261***/
4464
4465 IF l_op_network_unexp_rec.from_op_seq_id IS NULL
4466 THEN
4467 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4468 g_token_tbl(1).token_value :=
4469 p_op_network_rec.from_op_seq_number;
4470 Error_Handler.Add_Error_Token
4471 ( p_Message_Name => 'BOM_OP_NWK_FROM_OP_NOT_FOUND'
4472 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4473 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4474 , p_Token_Tbl => g_Token_Tbl
4475 );
4476 l_Return_Status := FND_API.G_RET_STS_ERROR;
4477 ELSIF l_err_text IS NOT NULL AND
4478 (l_op_network_unexp_rec.from_op_seq_id IS NULL OR
4479 l_op_network_unexp_rec.from_op_seq_id
4480 = FND_API.G_MISS_NUM )
4481 THEN
4482 -- This is an unexpected error.
4483 Error_Handler.Add_Error_Token
4484 ( p_Message_Name => NULL
4485 , p_Message_Text => l_err_text || ' in ' ||
4486 G_PKG_NAME
4487 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4488 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4489 );
4490 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4491 END IF;
4492
4493 IF BOM_Rtg_Globals.Get_Debug = 'Y'
4494 THEN Error_Handler.Write_Debug
4495 ('After converting, From Op Seq Id is '
4496 || to_char(l_op_network_unexp_rec.from_op_seq_id)
4497 || ' Status ' || l_return_status);
4498 END IF;
4499
4500 --
4501 -- Get To_operation_sequence_id
4502 --
4503 l_op_network_unexp_rec.to_op_seq_id :=
4504 Operation_Sequence_Id
4505 ( p_routing_sequence_id =>
4506 l_op_network_unexp_rec.routing_sequence_id
4507 , p_operation_type =>
4508 p_op_network_rec.operation_type
4509 , p_operation_seq_num =>
4510 p_op_network_rec.to_op_seq_number
4511 , p_effectivity_date =>
4512 p_op_network_rec.to_start_effective_date
4513 , x_err_text => l_err_text
4514 );
4515
4516 /***BEGIN 1838261***/
4517
4518 IF l_op_network_unexp_rec.to_op_seq_id IS NULL THEN
4519 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_Lot_Rtg THEN
4520 IF BOM_RTG_Globals.Get_Temp_Op_Rec1(p_op_network_rec.to_op_seq_number, p_op_network_rec.to_start_effective_date, x_temp_op_rec) THEN
4521 l_op_network_unexp_rec.to_op_seq_id :=
4522 Operation_Sequence_id
4523 ( p_routing_sequence_id =>
4524 l_op_network_unexp_rec.routing_sequence_id
4525 , p_operation_type =>
4526 p_op_network_rec.operation_type
4527 , p_operation_seq_num =>
4528 x_temp_op_rec.new_op_seq_num
4529 , p_effectivity_date =>
4530 x_temp_op_rec.new_start_eff_date
4531 , x_err_text => l_err_text
4532 );
4533 END IF;
4534 ELSE
4535 IF BOM_RTG_Globals.Get_Temp_Op_Rec(p_op_network_rec.to_op_seq_number, x_temp_op_rec) THEN
4536 l_op_network_unexp_rec.to_op_seq_id :=
4537 Operation_Sequence_id
4538 ( p_routing_sequence_id =>
4539 l_op_network_unexp_rec.routing_sequence_id
4540 , p_operation_type =>
4541 p_op_network_rec.operation_type
4542 , p_operation_seq_num =>
4543 x_temp_op_rec.new_op_seq_num
4544 , p_effectivity_date =>
4545 x_temp_op_rec.new_start_eff_date
4546 , x_err_text => l_err_text
4547 );
4548 END IF;
4549 END IF;
4550 END IF;
4551 /***END 1838261***/
4552
4553 IF l_op_network_unexp_rec.to_op_seq_id IS NULL
4554 THEN
4555 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4556 g_token_tbl(1).token_value :=
4557 p_op_network_rec.to_op_seq_number;
4558 Error_Handler.Add_Error_Token
4559 ( p_Message_Name => 'BOM_OP_NWK_TO_OP_NOT_FOUND'
4560 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4561 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4562 , p_Token_Tbl => g_Token_Tbl
4563 );
4564 l_Return_Status := FND_API.G_RET_STS_ERROR;
4565
4566 ELSIF l_err_text IS NOT NULL AND
4567 (l_op_network_unexp_rec.to_op_seq_id IS NULL OR
4568 l_op_network_unexp_rec.to_op_seq_id
4569 = FND_API.G_MISS_NUM )
4570 THEN
4571 -- This is an unexpected error.
4572 Error_Handler.Add_Error_Token
4573 ( p_Message_Name => NULL
4574 , p_Message_Text => l_err_text || ' in ' ||
4575 G_PKG_NAME
4576 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4577 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4578 );
4579 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4580 END IF;
4581
4582 IF BOM_Rtg_Globals.Get_Debug = 'Y'
4583 THEN Error_Handler.Write_Debug
4584 ('After converting, To Op Seq Id is '
4585 || to_char(l_op_network_unexp_rec.to_op_seq_id)
4586 || ' Status ' || l_return_status);
4587 END IF;
4588
4589 --
4590 -- Get new_from_operation_sequence_id
4591 --
4592 IF ( p_op_network_rec.new_from_op_seq_number IS NOT NULL AND
4593 p_op_network_rec.new_from_op_seq_number <> FND_API.G_MISS_NUM )
4594 OR ( p_op_network_rec.new_from_start_effective_date IS NOT NULL AND
4595 p_op_network_rec.new_from_start_effective_date <> FND_API.G_MISS_DATE )
4596 THEN
4597
4598 l_op_network_unexp_rec.new_from_op_seq_id :=
4599 Operation_Sequence_id
4600 ( p_routing_sequence_id =>
4601 l_op_network_unexp_rec.routing_sequence_id
4602 , p_operation_type =>
4603 p_op_network_rec.operation_type
4604 , p_operation_seq_num =>
4605 p_op_network_rec.new_from_op_seq_number
4606 , p_effectivity_date =>
4607 p_op_network_rec.new_from_start_effective_date
4608 , x_err_text => l_err_text
4609 );
4610
4611 /***BEGIN 1838261***/
4612 IF l_op_network_unexp_rec.new_from_op_seq_id IS NULL THEN
4613 IF BOM_RTG_Globals.Get_Temp_Op_Rec(p_op_network_rec.new_from_op_seq_number, x_temp_op_rec) THEN
4614 l_op_network_unexp_rec.new_from_op_seq_id :=
4615 Operation_Sequence_id
4616 ( p_routing_sequence_id =>
4617 l_op_network_unexp_rec.routing_sequence_id
4618 , p_operation_type =>
4619 p_op_network_rec.operation_type
4620 , p_operation_seq_num =>
4621 x_temp_op_rec.new_op_seq_num
4622 , p_effectivity_date =>
4623 x_temp_op_rec.new_start_eff_date
4624 , x_err_text => l_err_text
4625 );
4626 END IF;
4627 END IF;
4628 /***END 1838261***/
4629 IF l_op_network_unexp_rec.new_from_op_seq_id IS NULL
4630 THEN
4631 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4632 g_token_tbl(1).token_value :=
4633 p_op_network_rec.new_from_op_seq_number;
4634 Error_Handler.Add_Error_Token
4635 ( p_Message_Name => 'BOM_OP_NWK_FROM_OP_NOT_FOUND'
4636 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4637 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4638 , p_Token_Tbl => g_Token_Tbl
4639 );
4640 l_Return_Status := FND_API.G_RET_STS_ERROR;
4641
4642 ELSIF l_err_text IS NOT NULL AND
4643 (l_op_network_unexp_rec.new_from_op_seq_id IS NULL OR
4644 l_op_network_unexp_rec.new_from_op_seq_id
4645 = FND_API.G_MISS_NUM )
4646 THEN
4647 -- This is an unexpected error.
4648 Error_Handler.Add_Error_Token
4649 ( p_Message_Name => NULL
4650 , p_Message_Text => l_err_text || ' in ' ||
4651 G_PKG_NAME
4652 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4653 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4654 );
4655 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4656 END IF;
4657
4658 IF BOM_Rtg_Globals.Get_Debug = 'Y'
4659 THEN Error_Handler.Write_Debug
4660 ('After converting, New From Op Seq Id is '
4661 || to_char(l_op_network_unexp_rec.new_from_op_seq_id)
4662 || ' Status ' || l_return_status);
4663 END IF;
4664
4665 END IF ; -- new_from_op_seq_id
4666
4667 --
4668 -- Get new_to_operation_sequence_id
4669 --
4670 IF ( p_op_network_rec.new_to_op_seq_number IS NOT NULL AND
4671 p_op_network_rec.new_to_op_seq_number <> FND_API.G_MISS_NUM )
4672 OR ( p_op_network_rec.new_to_start_effective_date IS NOT NULL AND
4673 p_op_network_rec.new_to_start_effective_date <> FND_API.G_MISS_DATE )
4674 THEN
4675
4676 l_op_network_unexp_rec.new_to_op_seq_id :=
4677 Operation_Sequence_id
4678 ( p_routing_sequence_id =>
4679 l_op_network_unexp_rec.routing_sequence_id
4680 , p_operation_type =>
4681 p_op_network_rec.operation_type
4682 , p_operation_seq_num =>
4683 p_op_network_rec.new_to_op_seq_number
4684 , p_effectivity_date =>
4685 p_op_network_rec.new_to_start_effective_date
4686 , x_err_text => l_err_text
4687 );
4688
4689 /***BEGIN 1838261***/
4690 IF l_op_network_unexp_rec.new_to_op_seq_id IS NULL THEN
4691 IF BOM_RTG_Globals.Get_Temp_Op_Rec(p_op_network_rec.new_to_op_seq_number, x_temp_op_rec) THEN
4692 l_op_network_unexp_rec.new_to_op_seq_id :=
4693 Operation_Sequence_id
4694 ( p_routing_sequence_id =>
4695 l_op_network_unexp_rec.routing_sequence_id
4696 , p_operation_type =>
4697 p_op_network_rec.operation_type
4698 , p_operation_seq_num =>
4699 x_temp_op_rec.new_op_seq_num
4700 , p_effectivity_date =>
4701 x_temp_op_rec.new_start_eff_date
4702 , x_err_text => l_err_text
4703 );
4704 END IF;
4705 END IF;
4706 /***END 1838261***/
4707 IF l_op_network_unexp_rec.new_to_op_seq_id IS NULL
4708 THEN
4709 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4710 g_token_tbl(1).token_value :=
4711 p_op_network_rec.new_to_op_seq_number;
4712 Error_Handler.Add_Error_Token
4713 ( p_Message_Name => 'BOM_OP_NWK_TO_OP_NOT_FOUND'
4714 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4715 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4716 , p_Token_Tbl => g_Token_Tbl
4717 );
4718 l_Return_Status := FND_API.G_RET_STS_ERROR;
4719
4720 ELSIF l_err_text IS NOT NULL AND
4721 (l_op_network_unexp_rec.new_to_op_seq_id IS NULL OR
4722 l_op_network_unexp_rec.new_to_op_seq_id
4723 = FND_API.G_MISS_NUM )
4724 THEN
4725
4726 -- This is an unexpected error.
4727 Error_Handler.Add_Error_Token
4728 ( p_Message_Name => NULL
4729 , p_Message_Text => l_err_text || ' in ' ||
4730 G_PKG_NAME
4731 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4732 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4733 );
4734 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4735 END IF;
4736
4737 IF BOM_Rtg_Globals.Get_Debug = 'Y'
4738 THEN Error_Handler.Write_Debug
4739 ('After converting, New To Op Seq Id is '
4740 || to_char(l_op_network_unexp_rec.new_to_op_seq_id)
4741 || ' Status ' || l_return_status);
4742 END IF;
4743
4744 END IF ; -- to_from_op_seq_id
4745
4746
4747 x_return_status := l_return_status;
4748 x_op_network_unexp_rec := l_op_network_unexp_rec;
4749 x_mesg_token_tbl := l_mesg_token_tbl;
4750
4751 END OP_Network_UUI_To_UI;
4752
4753
4754
4755 END BOM_Rtg_Val_To_Id;