[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.12020000.2 2012/07/05 09:33:08 ntungare ship $*/
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
2263 -- to avoid conditions where record at first index may be null in l_token_tbl
2264 -- 2863724 IF p_com_operation_rec.line_op_code <> FND_API.G_MISS_CHAR
2265 -- 2863724 THEN
2266 l_token_tbl(1).token_name := 'LINE_OP_CODE';
2267 l_token_tbl(1).token_value :=
2268 p_com_operation_rec.line_op_code;
2269 -- 2863724 END IF ;
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 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
2339 g_token_tbl(1).token_value :=
2340 p_com_operation_rec.operation_sequence_number ;
2341
2342 Error_Handler.Add_Error_Token
2343 ( p_Message_Name => 'BOM_OP_OLD_OPSEQ_INVALID'
2344 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2345 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2346 , p_Token_Tbl => g_Token_Tbl
2347 );
2348 l_Return_Status := FND_API.G_RET_STS_ERROR;
2349
2350 END IF ;
2351
2352 l_com_op_unexp_rec.old_operation_sequence_id :=
2353 Old_Operation_Sequence
2354 ( p_old_effective_date => p_com_operation_rec.old_start_effective_date
2355 , p_old_op_seq_num => l_old_op_seq_number
2356 -- p_com_operation_rec.old_operation_sequence_number
2357 , p_operation_type => p_com_operation_rec.operation_type
2358 , p_routing_sequence_id => l_com_op_unexp_rec.routing_sequence_id
2359 );
2360
2361 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug ('Converted Old Operation Seq Id. . .');
2362 END IF;
2363
2364 IF l_com_op_unexp_rec.old_operation_sequence_id IS NULL
2365 THEN
2366 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
2367 g_token_tbl(1).token_value :=
2368 p_com_operation_rec.operation_sequence_number ;
2369 g_token_tbl(2).token_name := 'OLD_EFFECTIVITY_DATE';
2370 g_token_tbl(2).token_value :=
2371 p_com_operation_rec.old_start_effective_date;
2372 g_token_tbl(3).token_name := 'OLD_OP_SEQ_NUMBER';
2373 g_token_tbl(3).token_value :=
2374 p_com_operation_rec.old_operation_sequence_number;
2375
2376 Error_Handler.Add_Error_Token
2377 ( p_Message_Name => 'BOM_OP_OLD_OP_SEQ_INVALID'
2378 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2379 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2380 , p_Token_Tbl => g_Token_Tbl
2381 );
2382 l_Return_Status := FND_API.G_RET_STS_ERROR;
2383 ELSIF l_com_op_unexp_rec.old_operation_sequence_id = FND_API.G_MISS_NUM
2384 THEN
2385 Error_Handler.Add_Error_Token
2386 ( p_Message_Name => NULL
2387 , p_Message_Text =>
2388 'Unexpected Error while converting old operation sequence id'
2389 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2390 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2391
2392 );
2393 l_Return_Status := FND_API.G_RET_STS_ERROR;
2394
2395 END IF;
2396
2397
2398 END IF;
2399
2400
2401 x_return_status := l_return_status;
2402
2403 IF Bom_Rtg_Globals.Get_Debug = 'Y'
2404 THEN Error_Handler.Write_Debug
2405 ('Return status of operation VID: ' || l_return_status );
2406 END IF;
2407
2408 x_com_op_unexp_rec := l_com_op_unexp_rec;
2409 x_mesg_token_tbl := l_mesg_token_tbl;
2410
2411 END Com_Operation_VID;
2412
2413
2414 /*********************************************************************
2415 * Procedure : Op_Resource_UUI_To_UI
2416 * Returns : None
2417 * Parameters IN : Operation Resource Exposed Record
2418 * Operation Resource Unexposed Record
2419 * Parameters OUT: Operation Resource unexposed Record
2420 * Message Token Table
2421 * Return Status
2422 * Purpose : Convert Routing Op Resource to Revised Op Resource and
2423 * Call Rev_Op_resource_UUI_To_UI for ECO Bo.
2424 * After calling Rev_Op_resource_UUI_To_UI, convert Revised
2425 * Op Resource record back to Routing Op Resource
2426 *********************************************************************/
2427 PROCEDURE Op_Resource_UUI_To_UI
2428 ( p_op_resource_rec IN Bom_Rtg_Pub.Op_Resource_Rec_Type
2429 , p_op_res_unexp_rec IN Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
2430 , x_op_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
2431 , x_return_status IN OUT NOCOPY VARCHAR2
2432 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2433 )
2434
2435 IS
2436 l_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
2437 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type ;
2438 l_op_resource_rec Bom_Rtg_Pub.Op_Resource_Rec_Type ;
2439
2440 BEGIN
2441 -- Convert Routing Operation to ECO Operation
2442 Bom_Rtg_Pub.Convert_RtgRes_To_EcoRes
2443 ( p_rtg_op_resource_rec => p_op_resource_rec
2444 , p_rtg_op_res_unexp_rec => p_op_res_unexp_rec
2445 , x_rev_op_resource_rec => l_rev_op_resource_rec
2446 , x_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2447 ) ;
2448
2449 -- Call Rev_Op_Resource_UUI_To_UI
2450 Bom_Rtg_Val_To_Id.Rev_Op_Resource_UUI_To_UI
2451 ( p_rev_op_resource_rec => l_rev_op_resource_rec
2452 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2453 , x_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2454 , x_return_status => x_return_status
2455 , x_mesg_token_tbl => x_mesg_token_tbl
2456 ) ;
2457
2458 -- Convert old Eco Opeartion Record back to Routing Operation
2459 Bom_Rtg_Pub.Convert_EcoRes_To_RtgRes
2460 ( p_rev_op_resource_rec => l_rev_op_resource_rec
2461 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2462 , x_rtg_op_resource_rec => l_op_resource_rec
2463 , x_rtg_op_res_unexp_rec => x_op_res_unexp_rec
2464 ) ;
2465
2466 END Op_Resource_UUI_To_UI ;
2467
2468 /*********************************************************************
2469 * Procedure : Rev_Op_Resource_UUI_To_UI
2470 * Returns : None
2471 * Parameters IN : Revised Operation Resource Exposed Record
2472 * Revised Operation Resource Unexposed Record
2473 * Parameters OUT: Revised Operation Resource Unexposed Record
2474 * Message Token Table
2475 * Return Status
2476 * Purpose : This procedure will perform all the required
2477 * User unique to Unique index conversions for
2478 * resource record. Any errors will be logged in the
2479 * Message table and a return satus of success or
2480 * failure will be returned to the calling program.
2481 *********************************************************************/
2482 PROCEDURE Rev_Op_Resource_UUI_To_UI
2483 ( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
2484 , p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
2485 , x_rev_op_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
2486 , x_return_status IN OUT NOCOPY VARCHAR2
2487 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2488 )
2489
2490
2491
2492 IS
2493 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2494 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type ;
2495 l_return_status VARCHAR2(1);
2496 l_err_text VARCHAR2(2000);
2497 l_cfm_flag NUMBER;
2498 l_err_text_diff VARCHAR2(1);
2499
2500 CURSOR c_Check_Alternate( p_alt_designator VARCHAR2,
2501 p_organization_id NUMBER ) IS
2502 SELECT 1
2503 FROM bom_alternate_designators
2504 WHERE alternate_designator_code = p_alt_designator
2505 AND organization_id = p_organization_id;
2506
2507 BEGIN
2508 x_return_status := FND_API.G_RET_STS_SUCCESS;
2509 l_return_status := FND_API.G_RET_STS_SUCCESS;
2510 l_rev_op_res_unexp_rec := p_rev_op_res_unexp_rec;
2511
2512 If BOM_Rtg_Globals.Get_Debug = 'Y'
2513 THEN Error_Handler.Write_Debug
2514 ('Operation resource record UUI-UI Conversion . . ');
2515 END IF;
2516
2517 --
2518 -- Assembly Item name cannot be NULL or missing.
2519 --
2520 IF p_rev_op_resource_rec.revised_item_name IS NULL OR
2521 p_rev_op_resource_rec.revised_item_name = FND_API.G_MISS_CHAR
2522 THEN
2523 Error_Handler.Add_Error_Token
2524 ( p_message_name => 'BOM_RES_AITEM_KEYCOL_NULL'
2525 , p_mesg_token_tbl => l_mesg_token_tbl
2526 , x_mesg_token_tbl => l_mesg_token_tbl
2527 );
2528
2529 l_return_status := FND_API.G_RET_STS_ERROR;
2530 END IF;
2531
2532 --
2533 -- Resource sequence number cannot be NULL or missing.
2534 --
2535 IF p_rev_op_resource_rec.resource_sequence_number IS NULL OR
2536 p_rev_op_resource_rec.resource_sequence_number = FND_API.G_MISS_NUM
2537 THEN
2538 Error_Handler.Add_Error_Token
2539 ( p_Message_Name => 'BOM_RES_SEQNUM_KEYCOL_NULL'
2540 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2541 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2542 );
2543 l_Return_Status := FND_API.G_RET_STS_ERROR;
2544 END IF;
2545
2546 --
2547 -- Operation sequence number cannot be NULL or missing.
2548 --
2549 IF p_rev_op_resource_rec.operation_sequence_number IS NULL OR
2550 p_rev_op_resource_rec.operation_sequence_number =
2551 FND_API.G_MISS_NUM
2552 THEN
2553 Error_Handler.Add_Error_Token
2554 ( p_Message_Name => 'BOM_RES_OP_SEQNUM_KEYCOL_NULL'
2555 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2556 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2557 );
2558 l_Return_Status := FND_API.G_RET_STS_ERROR;
2559 END IF;
2560
2561 --
2562 -- Start effective date cannot be NULL or missing.
2563 --
2564 IF p_rev_op_resource_rec.op_start_effective_date IS NULL OR
2565 p_rev_op_resource_rec.op_start_effective_date = FND_API.G_MISS_DATE
2566 THEN
2567 Error_Handler.Add_Error_Token
2568 ( p_Message_Name => 'BOM_RES_EFF_DATE_KEYCOL_NULL'
2569 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2570 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2571 );
2572 l_Return_Status := FND_API.G_RET_STS_ERROR;
2573 END IF;
2574
2575 --
2576 -- If key columns are NULL, then return.
2577 --
2578 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2579 THEN
2580 x_Return_Status := FND_API.G_RET_STS_ERROR;
2581 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2582 RETURN;
2583 END IF;
2584
2585
2586 --
2587 -- Assembly item name must be successfully converted to id.
2588 --
2589
2590 l_rev_op_res_unexp_rec.revised_item_id :=
2591 Revised_Item ( p_revised_item_num =>
2592 p_rev_op_resource_rec.revised_item_name
2593 , p_organization_id =>
2594 l_rev_op_res_unexp_rec.organization_id
2595 , x_err_text => l_err_text
2596 );
2597
2598 IF l_rev_op_res_unexp_rec.revised_item_id IS NULL
2599 THEN
2600 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
2601 g_token_tbl(1).token_value :=
2602 p_rev_op_resource_rec.revised_item_name;
2603 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
2604 g_token_tbl(2).token_value :=
2605 p_rev_op_resource_rec.organization_code;
2606 Error_Handler.Add_Error_Token
2607 ( p_Message_Name => 'BOM_RTG_AITEM_DOESNOT_EXIST'
2608 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2609 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2610 , p_Token_Tbl => g_Token_Tbl
2611 );
2612 l_Return_Status := FND_API.G_RET_STS_ERROR;
2613 ELSIF l_err_text IS NOT NULL AND
2614 ( l_rev_op_res_unexp_rec.revised_item_id IS NULL OR
2615 l_rev_op_res_unexp_rec.revised_item_id = FND_API.G_MISS_NUM)
2616 THEN
2617 -- This is an unexpected error.
2618 Error_Handler.Add_Error_Token
2619 ( p_Message_Name => NULL
2620 , p_Message_Text => l_err_text || ' in ' ||
2621 G_PKG_NAME
2622 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2623 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2624 );
2625 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2626 END IF;
2627
2628 IF BOM_Rtg_Globals.Get_Debug = 'Y'
2629 THEN Error_Handler.Write_Debug
2630 ('After converting Assembly Item Id : '
2631 || to_char(l_rev_op_res_unexp_rec.revised_item_id)
2632 || ' Status ' || l_return_status);
2633 END IF;
2634
2635 IF p_rev_op_resource_rec.alternate_routing_code IS NOT NULL AND
2636 p_rev_op_resource_rec.alternate_routing_code <> FND_API.G_MISS_CHAR
2637 THEN
2638 /*l_err_text_diff*/l_err_text := FND_API.G_RET_STS_ERROR;
2639
2640 FOR check_alternate IN
2641 c_Check_Alternate
2642 ( p_alt_designator =>
2643 p_rev_op_resource_rec.alternate_routing_code,
2644 p_organization_id =>
2645 l_rev_op_res_unexp_rec.organization_id )
2646 LOOP
2647 /*l_err_text_diff*/l_err_text := FND_API.G_RET_STS_SUCCESS;
2648 END LOOP;
2649
2650 IF /*l_err_text_diff*/l_err_text <> FND_API.G_RET_STS_SUCCESS
2651 THEN
2652 g_token_tbl(1).token_name :='ALTERNATE_ROUTING_CODE';
2653 g_token_tbl(1).token_value :=
2654 p_rev_op_resource_rec.alternate_routing_code;
2655 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
2656 g_token_tbl(2).token_value :=
2657 p_rev_op_resource_rec.organization_code;
2658 Error_Handler.Add_Error_Token
2659 ( P_Message_Name => 'BOM_RTG_ALT_DESIGNATOR_INVALID'
2660 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2661 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2662 , p_token_tbl => g_token_tbl
2663 );
2664
2665 l_return_status := FND_API.G_RET_STS_ERROR;
2666 END IF;
2667
2668 END IF;
2669
2670
2671 /****************************************************************
2672 --
2673 -- Using the revised item key information, get the routing_sequence_id
2674 -- and revised item sequence id
2675 --
2676 ****************************************************************/
2677
2678 IF BOM_Rtg_Globals.Get_Bo_Identifier <> BOM_Rtg_Globals.G_RTG_BO
2679 THEN
2680
2681 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
2682 Error_Handler.Write_Debug('Processing UUI_UI for operation resources and retrieving rev item seq id . . . ');
2683 END IF;
2684
2685 NULL ;
2686
2687 /****************************************************************
2688 -- Comment out by MK on 12/04/00 to resolve Eco dependency
2689 l_rev_op_res_unexp_rec.revised_item_sequence_id :=
2690 RtgAndRevItemSeq
2691 ( p_revised_item_id => l_rev_op_res_unexp_rec.revised_item_id
2692 , p_item_revision => p_rev_op_resource_rec.new_revised_item_revision
2693 , p_effective_date => p_rev_op_resource_rec.op_start_effective_date
2694 , p_change_notice => p_rev_op_resource_rec.eco_name
2695 , p_organization_id => l_rev_op_res_unexp_rec.organization_id
2696 , p_new_routing_revision => p_rev_op_resource_rec.new_routing_revision
2697 , p_from_end_item_number => p_rev_op_resource_rec.from_end_item_unit_number
2698 , x_routing_sequence_id => l_rev_op_res_unexp_rec.routing_sequence_id
2699 );
2700
2701 IF l_rev_op_res_unexp_rec.revised_item_Sequence_id IS NULL
2702 THEN
2703 g_Token_Tbl(1).Token_Name := 'RES_SEQ_NUMBER';
2704 g_Token_Tbl(1).Token_Value := p_rev_op_resource_rec.resource_sequence_number;
2705 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
2706 g_Token_Tbl(2).Token_Value := p_rev_op_resource_rec.revised_item_name;
2707 g_token_tbl(3).token_name := 'ECO_NAME';
2708 g_token_tbl(3).token_value := p_rev_op_resource_rec.eco_name;
2709
2710 Error_Handler.Add_Error_Token
2711 ( p_Message_Name => 'BOM_RES_RIT_SEQUENCE_NOT_FOUND'
2712 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2713 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2714 , p_Token_Tbl => g_Token_Tbl
2715 );
2716
2717 l_Return_Status := FND_API.G_RET_STS_ERROR;
2718 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2719 x_rev_op_res_unexp_rec := l_rev_op_res_unexp_rec;
2720 x_Return_Status := l_Return_Status;
2721
2722 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('And this call returned with ' || l_Return_Status); END IF;
2723
2724 RETURN;
2725
2726 END IF;
2727
2728 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
2729 Error_Handler.Write_Debug('Revised Item Sequence Id : ' || to_char(l_rev_op_res_unexp_rec.revised_item_sequence_id)) ;
2730 END IF ;
2731
2732 ****************************************************************/
2733
2734 ELSE
2735 --
2736 -- If the calling BO is RTG then get the routing sequence id
2737 --
2738 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
2739 Error_Handler.Write_Debug('Processing UUI_UI for operation resources. . . ');
2740 END IF;
2741 l_rev_op_res_unexp_rec.routing_sequence_id :=
2742 Routing_Sequence_id
2743 ( p_assembly_item_id => l_rev_op_res_unexp_rec.revised_item_id
2744 , p_organization_id => l_rev_op_res_unexp_rec.organization_id
2745 , p_alternate_routing_designator =>
2746 p_rev_op_resource_rec.alternate_routing_code
2747 , x_err_text => l_err_text
2748 );
2749
2750 IF l_rev_op_res_unexp_rec.routing_sequence_id IS NULL
2751 THEN
2752 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
2753 g_token_tbl(1).token_value :=
2754 p_rev_op_resource_rec.revised_item_name;
2755 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
2756 g_token_tbl(2).token_value :=
2757 p_rev_op_resource_rec.organization_code;
2758 Error_Handler.Add_Error_Token
2759 ( p_Message_Name => 'BOM_RES_RTG_NOT_FOUND'
2760 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2761 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2762 , p_Token_Tbl => g_Token_Tbl
2763 );
2764 l_Return_Status := FND_API.G_RET_STS_ERROR;
2765 ELSIF l_err_text IS NOT NULL AND
2766 (l_rev_op_res_unexp_rec.routing_sequence_id IS NULL OR
2767 l_rev_op_res_unexp_rec.routing_sequence_id = FND_API.G_MISS_NUM)
2768 THEN
2769 -- This is an unexpected error.
2770 Error_Handler.Add_Error_Token
2771 ( p_Message_Name => NULL
2772 , p_Message_Text => l_err_text || ' in ' ||
2773 G_PKG_NAME
2774 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2775 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2776 );
2777 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2778 END IF;
2779 END IF ;
2780
2781 -- Added by MK on 12/04/00
2782 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_RTG_BO
2783 THEN
2784
2785 IF BOM_Rtg_Globals.Get_Debug = 'Y'
2786 THEN Error_Handler.Write_Debug
2787 ('After converting, routing sequence id is '
2788 || to_char(l_rev_op_res_unexp_rec.routing_sequence_id )
2789 || ' Status ' || l_return_status);
2790 END IF;
2791
2792 --
2793 -- For flow routing, operatoin Type should be set in (1, 2, 3)
2794 --
2795 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag IS NULL OR
2796 BOM_Rtg_Globals.Get_CFM_Rtg_Flag = FND_API.G_MISS_NUM
2797 THEN
2798 l_cfm_flag := Bom_Rtg_Validate.Get_Flow_Routing_Flag(
2799 l_rev_op_res_unexp_rec.routing_sequence_id) ;
2800 BOM_Rtg_Globals.Set_CFM_Rtg_Flag(p_cfm_rtg_type => l_cfm_flag) ;
2801 ELSE l_cfm_flag := BOM_Rtg_Globals.Get_CFM_Rtg_Flag ;
2802 END IF;
2803
2804 IF l_cfm_flag = BOM_Rtg_Globals.G_FLOW_RTG
2805 THEN
2806 IF p_rev_op_resource_rec.operation_type IS NULL
2807 OR p_rev_op_resource_rec.operation_type = FND_API.G_MISS_NUM
2808 OR p_rev_op_resource_rec.operation_type NOT IN (1, 2, 3)
2809 THEN
2810
2811 IF p_rev_op_resource_rec.operation_type <> FND_API.G_MISS_NUM
2812 THEN
2813 g_token_tbl(1).token_name := 'OPERATION_TYPE';
2814 g_token_tbl(1).token_value :=
2815 p_rev_op_resource_rec.operation_type ;
2816 END IF ;
2817
2818 Error_Handler.Add_Error_Token
2819 ( p_Message_Name => 'BOM_FLM_RES_OPTYPE_INVALID'
2820 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2821 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2822 , p_Token_Tbl => g_Token_Tbl
2823 );
2824 l_return_status := FND_API.G_RET_STS_ERROR ;
2825 END IF ;
2826 ELSE
2827 IF p_rev_op_resource_rec.operation_type IS NOT NULL
2828 AND p_rev_op_resource_rec.operation_type <> FND_API.G_MISS_NUM
2829 AND p_rev_op_resource_rec.operation_type <> 1
2830 THEN
2831 g_token_tbl(1).token_name := 'OPERATION_TYPE';
2832 g_token_tbl(1).token_value :=
2833 p_rev_op_resource_rec.operation_type;
2834
2835 Error_Handler.Add_Error_Token
2836 ( p_Message_Name => 'BOM_STD_RES_OPTYPE_INVALID'
2837 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2838 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2839 , p_Token_Tbl => g_Token_Tbl
2840 );
2841 l_return_status := FND_API.G_RET_STS_ERROR ;
2842 END IF ;
2843
2844 END IF;
2845
2846 l_rev_op_res_unexp_rec.operation_sequence_id :=
2847 Operation_Sequence_id
2848 ( p_routing_sequence_id =>
2849 l_rev_op_res_unexp_rec.routing_sequence_id
2850 , p_operation_type =>
2851 p_rev_op_resource_rec.operation_type
2852 , p_operation_seq_num =>
2853 p_rev_op_resource_rec.operation_sequence_number
2854 , p_effectivity_date =>
2855 p_rev_op_resource_rec.op_start_effective_date
2856 , x_err_text => l_err_text
2857 );
2858
2859 IF l_rev_op_res_unexp_rec.operation_sequence_id IS NULL
2860 THEN
2861 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
2862 g_token_tbl(1).token_value :=
2863 p_rev_op_resource_rec.operation_sequence_number ;
2864 Error_Handler.Add_Error_Token
2865 ( p_Message_Name => 'BOM_RES_OP_NOT_FOUND'
2866 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2867 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2868 , p_Token_Tbl => g_Token_Tbl
2869 );
2870 l_Return_Status := FND_API.G_RET_STS_ERROR;
2871 ELSIF l_err_text IS NOT NULL AND
2872 (l_rev_op_res_unexp_rec.operation_sequence_id IS NULL OR
2873 l_rev_op_res_unexp_rec.operation_sequence_id = FND_API.G_MISS_NUM
2874 )
2875 THEN
2876 -- This is an unexpected error.
2877 Error_Handler.Add_Error_Token
2878 ( p_Message_Name => NULL
2879 , p_Message_Text => l_err_text || ' in ' ||
2880 G_PKG_NAME
2881 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2882 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2883 );
2884 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2885 END IF;
2886
2887 IF BOM_Rtg_Globals.Get_Debug = 'Y'
2888 THEN Error_Handler.Write_Debug
2889 ('After converting, operation sequence id is '
2890 || to_char(l_rev_op_res_unexp_rec.operation_sequence_id )
2891 || ' Status ' || l_return_status);
2892 END IF;
2893
2894 END IF ; -- Added by MK on 12/04/00 Rtg BO specific
2895
2896 x_return_status := l_return_status;
2897 x_rev_op_res_unexp_rec := l_rev_op_res_unexp_rec;
2898 x_mesg_token_tbl := l_mesg_token_tbl;
2899
2900 END Rev_Op_Resource_UUI_To_UI;
2901
2902
2903
2904 /*********************************************************************
2905 * Procedure : Op_Resource_VID
2906 * Returns : None
2907 * Parameters IN : Operation resource exposed Record
2908 * Operation resource Unexposed Record
2909 * Parameters OUT: Operation resource unexposed record
2910 * Message Token Table
2911 * Return Status
2912 * Purpose : Convert Routing Op Resource to Revised Op Resource and
2913 * Call Rev_Op_resource_VID for ECO Bo.
2914 * After calling Rev_Op_resource_VID, convert Revised
2915 * Op Resource record back to Routing Op Resource
2916 *********************************************************************/
2917 PROCEDURE Op_Resource_VID
2918 ( p_op_resource_rec IN Bom_Rtg_Pub.Op_Resource_Rec_Type
2919 , p_op_res_unexp_rec IN Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
2920 , x_op_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
2921 , x_return_status IN OUT NOCOPY VARCHAR2
2922 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2923 )
2924
2925 IS
2926 l_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
2927 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type ;
2928 l_op_resource_rec Bom_Rtg_Pub.Op_Resource_Rec_Type ;
2929
2930 BEGIN
2931 -- Convert Routing Operation to ECO Operation
2932 Bom_Rtg_Pub.Convert_RtgRes_To_EcoRes
2933 ( p_rtg_op_resource_rec => p_op_resource_rec
2934 , p_rtg_op_res_unexp_rec => p_op_res_unexp_rec
2935 , x_rev_op_resource_rec => l_rev_op_resource_rec
2936 , x_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2937 ) ;
2938
2939 -- Call Rev_Op_resource_UUI_To_UI
2940 Bom_Rtg_Val_To_Id.Rev_Op_Resource_VID
2941 ( p_rev_op_resource_rec => l_rev_op_resource_rec
2942 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2943 , x_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2944 , x_return_status => x_return_status
2945 , x_mesg_token_tbl => x_mesg_token_tbl
2946 ) ;
2947
2948 -- Convert old Eco Opeartion Record back to Routing Operation
2949 Bom_Rtg_Pub.Convert_EcoRes_To_RtgRes
2950 ( p_rev_op_resource_rec => l_rev_op_resource_rec
2951 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2952 , x_rtg_op_resource_rec => l_op_resource_rec
2953 , x_rtg_op_res_unexp_rec => x_op_res_unexp_rec
2954 ) ;
2955
2956
2957 END Op_Resource_VID ;
2958
2959
2960 /*********************************************************************
2961 * Procedure : Rev_Op_Resource_VID
2962 * Returns : None
2963 * Parameters IN : Operation resource exposed Record
2964 * Operation resource Unexposed Record
2965 * Parameters OUT: Operation resoruce Unexposed Record
2966 * Return Status
2967 * Message Token Table
2968 * Purpose : This is the access procedure which the private API
2969 * will call to perform the operation record value to ID
2970 * conversions. If any of the conversions fail then the
2971 * the procedure will return with an error status and
2972 * the messsage token table filled with appropriate
2973 * error message.
2974 *********************************************************************/
2975 PROCEDURE Rev_Op_Resource_VID
2976 ( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
2977 , p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
2978 , x_rev_op_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
2979 , x_Return_Status IN OUT NOCOPY VARCHAR2
2980 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2981 )
2982 IS
2983 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
2984 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2985 l_err_text VARCHAR2(2000);
2986 l_Token_Tbl Error_Handler.Token_Tbl_Type;
2987 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
2988 := p_rev_op_res_unexp_rec ;
2989 BEGIN
2990
2991 IF BOM_Rtg_Globals.Get_Debug = 'Y'
2992 THEN Error_Handler.Write_Debug('Resource VID conversion . . .');
2993 END IF;
2994
2995 --
2996 -- Convert resource code to reosurce id
2997 --
2998 IF p_rev_op_resource_rec.resource_code IS NOT NULL AND
2999 p_rev_op_resource_rec.resource_code <>
3000 FND_API.G_MISS_CHAR
3001 THEN
3002
3003 l_rev_op_res_unexp_rec.resource_id :=
3004 Resource_Id
3005 ( p_resource_code =>
3006 p_rev_op_resource_rec.resource_code
3007 , p_organization_id =>
3008 l_rev_op_res_unexp_rec.organization_id
3009 , x_err_text => l_err_text
3010 );
3011
3012
3013 IF l_rev_op_res_unexp_rec.resource_id IS NULL
3014 THEN
3015 l_token_tbl(1).token_name := 'RESOURCE_CODE';
3016 l_token_tbl(1).token_value :=
3017 p_rev_op_resource_rec.resource_code;
3018 Error_Handler.Add_Error_Token
3019 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3020 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3021 , p_Message_name =>
3022 'BOM_RES_RESCODE_INVALID'
3023 , p_token_tbl => l_token_tbl
3024 );
3025 l_return_status := FND_API.G_RET_STS_ERROR;
3026 ELSIF l_err_text IS NOT NULL AND
3027 l_rev_op_res_unexp_rec.resource_id IS NULL
3028 THEN
3029 Error_Handler.Add_Error_Token
3030 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3031 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3032 , p_Message_text =>
3033 'Unexpected Error ' || l_err_text || ' in ' ||
3034 G_PKG_NAME
3035 , p_token_tbl => l_token_tbl
3036 );
3037 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
3038 END IF;
3039
3040 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3041 ('Converted resource code . . .');
3042 END IF;
3043
3044 END IF;
3045
3046
3047 --
3048 -- Convert activity to activity id
3049 --
3050
3051 IF p_rev_op_resource_rec.activity IS NOT NULL AND
3052 p_rev_op_resource_rec.activity <> FND_API.G_MISS_CHAR
3053 THEN
3054 l_rev_op_res_unexp_rec.activity_id :=
3055 Activity_Id
3056 ( p_activity =>
3057 p_rev_op_resource_rec.activity
3058 , p_organization_id =>
3059 l_rev_op_res_unexp_rec.organization_id
3060 , x_err_text => l_err_text
3061 );
3062
3063 IF l_rev_op_res_unexp_rec.activity_id IS NULL
3064 THEN
3065 l_token_tbl(1).token_name := 'ACTIVITY';
3066 l_token_tbl(1).token_value :=
3067 p_rev_op_resource_rec.activity;
3068 Error_Handler.Add_Error_Token
3069 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3070 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3071 , p_Message_name =>
3072 'BOM_RES_ACTIVITY_CODE_INVALID'
3073 , p_token_tbl => l_token_tbl
3074 );
3075 l_return_status := FND_API.G_RET_STS_ERROR;
3076 ELSIF l_err_text IS NOT NULL AND
3077 l_rev_op_res_unexp_rec.activity_id IS NULL
3078 THEN
3079 Error_Handler.Add_Error_Token
3080 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3081 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3082 , p_Message_text =>
3083 'Unexpected Error ' || l_err_text || ' in ' ||
3084 G_PKG_NAME
3085 , p_token_tbl => l_token_tbl
3086 );
3087 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
3088 END IF;
3089
3090 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3091 ('Converted activity. . .');
3092 END IF;
3093
3094
3095 END IF;
3096
3097 --
3098 -- Convert setup code to setup id
3099 --
3100 IF p_rev_op_resource_rec.setup_type IS NOT NULL AND
3101 p_rev_op_resource_rec.setup_type <> FND_API.G_MISS_CHAR
3102 THEN
3103 l_rev_op_res_unexp_rec.setup_id :=
3104 Setup_Id
3105 ( p_setup_type =>
3106 p_rev_op_resource_rec.setup_type
3107 , p_organization_id =>
3108 l_rev_op_res_unexp_rec.organization_id
3109 , x_err_text => l_err_text
3110 );
3111
3112 IF l_rev_op_res_unexp_rec.setup_id IS NULL
3113 THEN
3114 l_token_tbl(1).token_name := 'SETUP_CODE';
3115 l_token_tbl(1).token_value :=
3116 p_rev_op_resource_rec.setup_type ;
3117 Error_Handler.Add_Error_Token
3118 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3119 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3120 , p_Message_name =>
3121 'BOM_RES_SETUP_CODE_INVALID'
3122 , p_token_tbl => l_token_tbl
3123 );
3124 l_return_status := FND_API.G_RET_STS_ERROR;
3125 ELSIF l_err_text IS NOT NULL AND
3126 l_rev_op_res_unexp_rec.setup_id IS NULL
3127 THEN
3128 Error_Handler.Add_Error_Token
3129 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3130 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3131 , p_Message_text =>
3132 'Unexpected Error ' || l_err_text || ' in ' ||
3133 G_PKG_NAME
3134 , p_token_tbl => l_token_tbl
3135 );
3136 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
3137 END IF;
3138
3139 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3140 ('Converted setup code . . .');
3141 END IF;
3142
3143 END IF;
3144
3145
3146 x_return_status := l_return_status;
3147
3148 IF Bom_Rtg_Globals.Get_Debug = 'Y'
3149 THEN Error_Handler.Write_Debug
3150 ('Return status of resource VID: ' || l_return_status );
3151 END IF;
3152
3153 x_rev_op_res_unexp_rec := l_rev_op_res_unexp_rec;
3154 x_mesg_token_tbl := l_mesg_token_tbl;
3155
3156 END Rev_Op_Resource_VID;
3157
3158
3159 /*********************************************************************
3160 * Procedure : Sub_Resource_UUI_To_UI
3161 * Returns : None
3162 * Parameters IN : Substitute resource exposed Record
3163 * Substitute resource Unexposed Record
3164 * Parameters OUT: Substitute resource unexposed record
3165 * Message Token Table
3166 * Return Status
3167 * Purpose : Convert Routing Sub Op Resource to Revised Sub Op
3168 * Resource and Call Rev_Sub_resource_UUI_To_UI for ECO Bo.
3169 * After calling Rev_Sub_resource_UUI_To_UI, convert
3170 * Revised Op Resource record back to Routing Op Resource
3171 *********************************************************************/
3172 PROCEDURE Sub_Resource_UUI_To_UI
3173 ( p_sub_resource_rec IN Bom_Rtg_Pub.Sub_Resource_Rec_Type
3174 , p_sub_res_unexp_rec IN Bom_Rtg_Pub.Sub_Res_Unexposed_Rec_Type
3175 , x_sub_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Sub_Res_Unexposed_Rec_Type
3176 , x_return_status IN OUT NOCOPY VARCHAR2
3177 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3178 )
3179
3180 IS
3181 l_rev_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type ;
3182 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type ;
3183 l_sub_resource_rec Bom_Rtg_Pub.Sub_Resource_Rec_Type ;
3184
3185 BEGIN
3186 -- Convert Routing Operation to ECO Operation
3187 Bom_Rtg_Pub.Convert_RtgSubRes_To_EcoSubRes
3188 ( p_rtg_sub_resource_rec => p_sub_resource_rec
3189 , p_rtg_sub_res_unexp_rec => p_sub_res_unexp_rec
3190 , x_rev_sub_resource_rec => l_rev_sub_resource_rec
3191 , x_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3192 ) ;
3193
3194 -- Call Rev_Sub_Resource_UUI_To_UI
3195 Bom_Rtg_Val_To_Id.Rev_Sub_Resource_UUI_To_UI
3196 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
3197 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3198 , x_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3199 , x_return_status => x_return_status
3200 , x_mesg_token_tbl => x_mesg_token_tbl
3201 ) ;
3202
3203 -- Convert Eco Sub Resource Record back to Routing Sub Resource
3204 Bom_Rtg_Pub.Convert_EcoSubRes_To_RtgSubRes
3205 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
3206 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3207 , x_rtg_sub_resource_rec => l_sub_resource_rec
3208 , x_rtg_sub_res_unexp_rec => x_sub_res_unexp_rec
3209 ) ;
3210
3211
3212 END Sub_Resource_UUI_To_UI ;
3213
3214
3215
3216 /*********************************************************************
3217 * Procedure : Rev_Sub_Resource_UUI_To_UI
3218 * Returns : None
3219 * Parameters IN : Revised Substitute Resource Exposed Record
3220 * Revised Substitute Resource Unexposed Record
3221 * Parameters OUT: Revised Substitute Resource unexposed record
3222 * Message Token Table
3223 * Return Status
3224 * Purpose : This procedure will perform all the required
3225 * User unique to Unique index conversions for
3226 * substitute resource record. Any errors will be
3227 * logged in the Message table and a return satus of
3228 * success or failure will be returned to the calling
3229 * program.
3230 *********************************************************************/
3231 PROCEDURE Rev_Sub_Resource_UUI_To_UI
3232 ( p_rev_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type
3233 , p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
3234 , x_rev_sub_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
3235 , x_return_status IN OUT NOCOPY VARCHAR2
3236 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3237 )
3238 IS
3239 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3240 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type;
3241 l_return_status VARCHAR2(1);
3242 l_err_text VARCHAR2(2000);
3243 l_cfm_flag NUMBER;
3244 l_Token_Tbl Error_Handler.Token_Tbl_Type;
3245 l_err_text_diff VARCHAR2(1);
3246
3247 CURSOR c_Check_Alternate( p_alt_designator VARCHAR2,
3248 p_organization_id NUMBER ) IS
3249 SELECT 1
3250 FROM bom_alternate_designators
3251 WHERE alternate_designator_code = p_alt_designator
3252 AND organization_id = p_organization_id;
3253
3254 BEGIN
3255 x_return_status := FND_API.G_RET_STS_SUCCESS;
3256 l_return_status := FND_API.G_RET_STS_SUCCESS;
3257 l_rev_sub_res_unexp_rec := p_rev_sub_res_unexp_rec ;
3258
3259 IF BOM_Rtg_Globals.Get_Debug = 'Y'
3260 THEN Error_Handler.Write_Debug
3261 ('Substitute resource record UUI-UI Conversion . . ');
3262 END IF;
3263
3264 --
3265 -- Assembly Item name cannot be NULL or missing.
3266 --
3267 IF p_rev_sub_resource_rec.revised_item_name IS NULL OR
3268 p_rev_sub_resource_rec.revised_item_name = FND_API.G_MISS_CHAR
3269 THEN
3270 Error_Handler.Add_Error_Token
3271 ( p_message_name => 'BOM_SUB_RES_AITEM_KEYCOL_NULL'
3272 , p_mesg_token_tbl => l_mesg_token_tbl
3273 , x_mesg_token_tbl => l_mesg_token_tbl
3274 );
3275
3276 l_return_status := FND_API.G_RET_STS_ERROR;
3277 END IF;
3278
3279 /* bug:4714714 - Commented the below check on Alternate routing code as
3280 creation/updation of alternate resources should be allowed for alternate routings also.
3281 --
3282 -- Alternate routing code should be NULL or missing.
3283 --
3284 IF p_rev_sub_resource_rec.alternate_routing_code IS NOT NULL AND
3285 p_rev_sub_resource_rec.alternate_routing_code <> FND_API.G_MISS_CHAR
3286 THEN
3287 Error_Handler.Add_Error_Token
3288 ( p_Message_Name => 'BOM_SUB_RES_ALTER_CD_NOT_NULL'
3289 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3290 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3291 );
3292 l_Return_Status := FND_API.G_RET_STS_ERROR;
3293 END IF;
3294 */
3295
3296 --
3297 --
3298 -- Sub resource code cannot be NULL or missing.
3299 -- Sub resource code should exist in BOM_RESOURCE
3300 --
3301 IF p_rev_sub_resource_rec.sub_resource_code IS NULL OR
3302 p_rev_sub_resource_rec.sub_resource_code = FND_API.G_MISS_CHAR
3303 THEN
3304 Error_Handler.Add_Error_Token
3305 ( p_message_name => 'BOM_SUB_RES_CODE_KEYCOL_NULL'
3306 , p_mesg_token_tbl => l_mesg_token_tbl
3307 , x_mesg_token_tbl => l_mesg_token_tbl
3308 );
3309
3310 l_return_status := FND_API.G_RET_STS_ERROR;
3311
3312 END IF;
3313
3314 --
3315 --
3316 -- Schedule sequence number cannot be NULL or missing.
3317 --
3318 -- IF nvl(BOM_Globals.Get_Caller_Type,'') <> 'MIGRATION' THEN
3319 IF nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number) IS NULL OR
3320 nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number)
3321 = FND_API.G_MISS_NUM
3322 THEN
3323 Error_Handler.Add_Error_Token
3324 ( p_message_name => 'BOM_SUB_RES_SCHDNM_KEYCOL_NULL'
3325 , p_mesg_token_tbl => l_mesg_token_tbl
3326 , x_mesg_token_tbl => l_mesg_token_tbl
3327 );
3328
3329 l_return_status := FND_API.G_RET_STS_ERROR;
3330 END IF;
3331 -- END IF; --migration
3332
3333 --
3334 --
3335 -- Operation sequence number cannot be NULL or missing.
3336 --
3337 IF p_rev_sub_resource_rec.operation_sequence_number IS NULL OR
3338 p_rev_sub_resource_rec.operation_sequence_number
3339 = FND_API.G_MISS_NUM
3340 THEN
3341 Error_Handler.Add_Error_Token
3342 ( p_message_name => 'BOM_SUB_RES_OP_SQNM_KEYCOL_NULL'
3343 , p_mesg_token_tbl => l_mesg_token_tbl
3344 , x_mesg_token_tbl => l_mesg_token_tbl
3345 );
3346
3347 l_return_status := FND_API.G_RET_STS_ERROR;
3348 END IF;
3349
3350 --
3351 --
3352 -- Start effective date cannot be NULL or missing.
3353 --
3354 IF p_rev_sub_resource_rec.op_start_effective_date IS NULL OR
3355 p_rev_sub_resource_rec.op_start_effective_date
3356 = FND_API.G_MISS_DATE
3357 THEN
3358 Error_Handler.Add_Error_Token
3359 ( p_message_name =>
3360 'BOM_SUB_RES_EFFDT_KEYCOL_NULL'
3361 , p_mesg_token_tbl => l_mesg_token_tbl
3362 , x_mesg_token_tbl => l_mesg_token_tbl
3363 );
3364
3365 l_return_status := FND_API.G_RET_STS_ERROR;
3366 END IF;
3367
3368 IF BOM_Rtg_Globals.Get_Debug = 'Y'
3369 THEN Error_Handler.Write_Debug
3370 ('Finish Substitute resource record Key Col check. . ');
3371 END IF;
3372
3373 --
3374 -- If key columns are NULL, then return.
3375 --
3376 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3377 THEN
3378 x_Return_Status := FND_API.G_RET_STS_ERROR;
3379 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3380 RETURN;
3381 END IF;
3382
3383 --
3384 -- Assembly item name must be successfully converted to id.
3385 --
3386
3387 l_rev_sub_res_unexp_rec.revised_item_id :=
3388 Revised_Item ( p_revised_item_num =>
3389 p_rev_sub_resource_rec.revised_item_name
3390 , p_organization_id =>
3391 l_rev_sub_res_unexp_rec.organization_id
3392 , x_err_text => l_err_text
3393 );
3394
3395 IF l_rev_sub_res_unexp_rec.revised_item_id IS NULL
3396 THEN
3397 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
3398 g_token_tbl(1).token_value :=
3399 p_rev_sub_resource_rec.revised_item_name;
3400 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
3401 g_token_tbl(2).token_value :=
3402 p_rev_sub_resource_rec.organization_code;
3403 Error_Handler.Add_Error_Token
3404 ( p_Message_Name => 'BOM_RTG_AITEM_DOESNOT_EXIST'
3405 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3406 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3407 , p_Token_Tbl => g_Token_Tbl
3408 );
3409 l_Return_Status := FND_API.G_RET_STS_ERROR;
3410 ELSIF l_err_text IS NOT NULL AND
3411 ( l_rev_sub_res_unexp_rec.revised_item_id IS NULL OR
3412 l_rev_sub_res_unexp_rec.revised_item_id = FND_API.G_MISS_NUM)
3413 THEN
3414 -- This is an unexpected error.
3415 Error_Handler.Add_Error_Token
3416 ( p_Message_Name => NULL
3417 , p_Message_Text => l_err_text || ' in ' ||
3418 G_PKG_NAME
3419 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3420 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3421 );
3422 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3423 END IF;
3424
3425 IF BOM_Rtg_Globals.Get_Debug = 'Y'
3426 THEN Error_Handler.Write_Debug
3427 ('After converting Assembly Item Id : '
3428 || to_char(l_rev_sub_res_unexp_rec.revised_item_id)
3429 || ' Status ' || l_return_status);
3430 END IF;
3431
3432 IF p_rev_sub_resource_rec.alternate_routing_code IS NOT NULL AND
3433 p_rev_sub_resource_rec.alternate_routing_code <> FND_API.G_MISS_CHAR
3434 THEN
3435 /*l_err_text_diff*/l_err_text := FND_API.G_RET_STS_ERROR;
3436
3437 FOR check_alternate IN
3438 c_Check_Alternate
3439 ( p_alt_designator =>
3440 p_rev_sub_resource_rec.alternate_routing_code,
3441 p_organization_id =>
3442 l_rev_sub_res_unexp_rec.organization_id )
3443 LOOP
3444 /*l_err_text_diff*/l_err_text := FND_API.G_RET_STS_SUCCESS;
3445 END LOOP;
3446
3447 IF /*l_err_text_diff*/l_err_text <> FND_API.G_RET_STS_SUCCESS
3448 THEN
3449 g_token_tbl(1).token_name :='ALTERNATE_ROUTING_CODE';
3450 g_token_tbl(1).token_value :=
3451 p_rev_sub_resource_rec.alternate_routing_code;
3452 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
3453 g_token_tbl(2).token_value :=
3454 p_rev_sub_resource_rec.organization_code;
3455 Error_Handler.Add_Error_Token
3456 ( P_Message_Name =>
3457 'BOM_RTG_ALT_DESIGNATOR_INVALID'
3458 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3459 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3460 , p_token_tbl => g_token_tbl
3461 );
3462
3463 l_return_status := FND_API.G_RET_STS_ERROR;
3464 END IF;
3465
3466 END IF;
3467
3468
3469 /****************************************************************
3470 --
3471 -- Using the revised item key information, get the routing_sequence_id
3472 -- and revised item sequence id
3473 --
3474 ****************************************************************/
3475 IF BOM_Rtg_Globals.Get_Bo_Identifier <> BOM_Rtg_Globals.G_RTG_BO
3476 THEN
3477 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3478 Error_Handler.Write_Debug('Processing UUI_UI for sub op resources and retrieving rev item seq id . . . ');
3479 END IF;
3480
3481 NULL ;
3482
3483 /****************************************************************
3484 -- Comment out by MK on 12/04/00 to resolve Eco dependency
3485 l_rev_sub_res_unexp_rec.revised_item_sequence_id :=
3486 RtgAndRevItemSeq
3487 ( p_revised_item_id => l_rev_sub_res_unexp_rec.revised_item_id
3488 , p_item_revision => p_rev_sub_resource_rec.new_revised_item_revision
3489 , p_effective_date => p_rev_sub_resource_rec.op_start_effective_date
3490 , p_change_notice => p_rev_sub_resource_rec.eco_name
3491 , p_organization_id => l_rev_sub_res_unexp_rec.organization_id
3492 , p_new_routing_revision => p_rev_sub_resource_rec.new_routing_revision
3493 , p_from_end_item_number => p_rev_sub_resource_rec.from_end_item_unit_number
3494 , x_routing_sequence_id => l_rev_sub_res_unexp_rec.routing_sequence_id
3495 );
3496
3497 IF l_rev_sub_res_unexp_rec.revised_item_sequence_id IS NULL
3498 THEN
3499 g_Token_Tbl(1).token_name := 'SUB_RESOURCE_CODE';
3500 g_Token_Tbl(1).token_value := p_rev_sub_resource_rec.sub_resource_code ;
3501 g_Token_Tbl(2).token_name := 'SCHEDULE_SEQ_NUMBER';
3502 g_Token_Tbl(2).token_value := p_rev_sub_resource_rec.schedule_sequence_number ;
3503 g_Token_Tbl(3).Token_Name := 'REVISED_ITEM_NAME';
3504 g_Token_Tbl(3).Token_Value := p_rev_sub_resource_rec.revised_item_name;
3505 g_token_tbl(4).token_name := 'ECO_NAME';
3506 g_token_tbl(4).token_value := p_rev_sub_resource_rec.eco_name;
3507
3508 Error_Handler.Add_Error_Token
3509 ( p_Message_Name => 'BOM_SUB_RES_RIT_SEQ_NOT_FOUND'
3510 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3511 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3512 , p_Token_Tbl => g_Token_Tbl
3513 );
3514
3515 l_Return_Status := FND_API.G_RET_STS_ERROR;
3516 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3517 x_rev_sub_res_unexp_rec := l_rev_sub_res_unexp_rec;
3518 x_Return_Status := l_Return_Status;
3519
3520 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('And this call returned with ' || l_Return_Status); END IF;
3521
3522 RETURN;
3523
3524 END IF;
3525
3526 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3527 Error_Handler.Write_Debug('Revised Item Sequence Id : ' || to_char(l_rev_sub_res_unexp_rec.revised_item_sequence_id)) ;
3528 END IF ;
3529 ****************************************************************/
3530
3531 ELSE
3532
3533 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3534 Error_Handler.Write_Debug('Processing UUI_UI for sub op res . . . ');
3535 END IF;
3536 --
3537 -- If the calling BO is RTG then get the routing sequence id
3538 --
3539
3540
3541 l_rev_sub_res_unexp_rec.routing_sequence_id :=
3542 Routing_Sequence_id
3543 ( p_assembly_item_id =>
3544 l_rev_sub_res_unexp_rec.revised_item_id
3545 , p_organization_id =>
3546 l_rev_sub_res_unexp_rec.organization_id
3547 , p_alternate_routing_designator =>
3548 p_rev_sub_resource_rec.alternate_routing_code
3549 , x_err_text => l_err_text
3550 );
3551
3552 IF l_rev_sub_res_unexp_rec.routing_sequence_id IS NULL
3553 THEN
3554 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
3555 g_token_tbl(1).token_value :=
3556 p_rev_sub_resource_rec.revised_item_name;
3557 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
3558 g_token_tbl(2).token_value :=
3559 p_rev_sub_resource_rec.organization_code;
3560 Error_Handler.Add_Error_Token
3561 ( p_Message_Name => 'BOM_SUB_RES_RTG_NOT_FOUND'
3562 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3563 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3564 , p_Token_Tbl => g_Token_Tbl
3565 );
3566 l_Return_Status := FND_API.G_RET_STS_ERROR;
3567 ELSIF l_err_text IS NOT NULL AND
3568 (l_rev_sub_res_unexp_rec.routing_sequence_id IS NULL OR
3569 l_rev_sub_res_unexp_rec.routing_sequence_id = FND_API.G_MISS_NUM)
3570 THEN
3571 -- This is an unexpected error.
3572 Error_Handler.Add_Error_Token
3573 ( p_Message_Name => NULL
3574 , p_Message_Text => l_err_text || ' in ' ||
3575 G_PKG_NAME
3576 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3577 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3578 );
3579 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3580 END IF;
3581
3582 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3583 Error_Handler.Write_Debug('After converting, routing sequence id is '
3584 || to_char(l_rev_sub_res_unexp_rec.routing_sequence_id )
3585 || ' Status ' || l_return_status);
3586 END IF;
3587
3588 END IF ;
3589
3590 -- Added by MK on 12/04/00
3591 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_RTG_BO
3592 THEN
3593
3594 --
3595 -- For flow routing, operatoin Type should be set in (1, 2, 3)
3596 -- For non flow routing,
3597
3598 l_cfm_flag := BOM_Rtg_Globals.Get_CFM_Rtg_Flag ;
3599 IF l_cfm_flag IS NULL OR
3600 l_cfm_flag = FND_API.G_MISS_NUM
3601 THEN
3602 l_cfm_flag := Bom_Rtg_Validate.Get_Flow_Routing_Flag(
3603 p_routing_sequence_id =>
3604 l_rev_sub_res_unexp_rec.routing_sequence_id ) ;
3605 BOM_Rtg_Globals.Set_CFM_Rtg_Flag(p_cfm_rtg_type => l_cfm_flag) ;
3606 END IF;
3607
3608 IF l_cfm_flag = BOM_Rtg_Globals.G_FLOW_RTG
3609 THEN
3610 IF p_rev_sub_resource_rec.operation_type IS NULL
3611 OR p_rev_sub_resource_rec.operation_type = FND_API.G_MISS_NUM
3612 OR p_rev_sub_resource_rec.operation_type NOT IN (1, 2, 3)
3613 THEN
3614 IF p_rev_sub_resource_rec.operation_type <> FND_API.G_MISS_NUM
3615 THEN
3616 g_token_tbl(1).token_name := 'OPERATION_TYPE';
3617 g_token_tbl(1).token_value :=
3618 p_rev_sub_resource_rec.operation_type;
3619 END IF ;
3620
3621 Error_Handler.Add_Error_Token
3622 ( p_Message_Name => 'BOM_SUB_RES_FLM_OP_TYP_INVALID'
3623 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3624 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3625 , p_Token_Tbl => g_Token_Tbl
3626 );
3627 l_return_status := FND_API.G_RET_STS_ERROR ;
3628
3629 END IF ;
3630 ELSE
3631 IF p_rev_sub_resource_rec.operation_type IS NOT NULL
3632 AND p_rev_sub_resource_rec.operation_type <> FND_API.G_MISS_NUM
3633 AND p_rev_sub_resource_rec.operation_type <> 1
3634 THEN
3635 g_token_tbl(1).token_name := 'OPERATION_TYPE';
3636 g_token_tbl(1).token_value :=
3637 p_rev_sub_resource_rec.operation_type;
3638 Error_Handler.Add_Error_Token
3639 ( p_Message_Name => 'BOM_SUB_RES_STD_OP_TYP_IGNORED'
3640 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3641 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3642 , p_Token_Tbl => g_Token_Tbl
3643 );
3644
3645 l_return_status := FND_API.G_RET_STS_ERROR ;
3646 END IF ;
3647
3648 END IF;
3649
3650 --
3651 -- convert to operation sequence id
3652 --
3653
3654 l_rev_sub_res_unexp_rec.operation_sequence_id :=
3655 Operation_Sequence_id
3656 ( p_routing_sequence_id =>
3657 l_rev_sub_res_unexp_rec.routing_sequence_id
3658 , p_operation_type =>
3659 p_rev_sub_resource_rec.operation_type
3660 , p_operation_seq_num =>
3661 p_rev_sub_resource_rec.operation_sequence_number
3662 , p_effectivity_date =>
3663 p_rev_sub_resource_rec.op_start_effective_date
3664 , x_err_text => l_err_text
3665 );
3666
3667 IF l_rev_sub_res_unexp_rec.operation_sequence_id IS NULL
3668 THEN
3669 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3670 g_token_tbl(1).token_value :=
3671 p_rev_sub_resource_rec.operation_sequence_number;
3672
3673 Error_Handler.Add_Error_Token
3674 ( p_Message_Name => 'BOM_SUB_RES_OP_NOT_FOUND'
3675 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3676 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3677 , p_Token_Tbl => g_Token_Tbl
3678 );
3679 l_Return_Status := FND_API.G_RET_STS_ERROR;
3680 ELSIF l_err_text IS NOT NULL AND
3681 (
3682 l_rev_sub_res_unexp_rec.operation_sequence_id IS NULL OR
3683 l_rev_sub_res_unexp_rec.operation_sequence_id =FND_API.G_MISS_NUM
3684 )
3685 THEN
3686 -- This is an unexpected error.
3687 Error_Handler.Add_Error_Token
3688 ( p_Message_Name => NULL
3689 , p_Message_Text => l_err_text || ' in ' ||
3690 G_PKG_NAME
3691 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3692 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3693 );
3694 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3695 END IF;
3696
3697 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3698 Error_Handler.Write_Debug('After converting, op sequence id is '
3699 || to_char(l_rev_sub_res_unexp_rec.operation_sequence_id )
3700 || ' Status ' || l_return_status);
3701 END IF;
3702
3703 --
3704 -- convert resource code to resource id
3705 --
3706 IF p_rev_sub_resource_rec.sub_resource_code IS NOT NULL AND
3707 p_rev_sub_resource_rec.sub_resource_code <> FND_API.G_MISS_CHAR
3708 THEN
3709 l_rev_sub_res_unexp_rec.resource_id :=
3710 Resource_Id
3711 ( p_resource_code =>
3712 p_rev_sub_resource_rec.sub_resource_code
3713 , p_organization_id =>
3714 l_rev_sub_res_unexp_rec.organization_id
3715 , x_err_text => l_err_text
3716 );
3717
3718 IF l_rev_sub_res_unexp_rec.resource_id IS NULL
3719 THEN
3720 l_token_tbl(1).token_name :=
3721 'RESOURCE_CODE';
3722 l_token_tbl(1).token_value :=
3723 p_rev_sub_resource_rec.sub_resource_code;
3724 Error_Handler.Add_Error_Token
3725 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3726 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3727 , p_Message_name =>
3728 'BOM_SUB_RES_SUB_RES_CD_INVALID'
3729 , p_token_tbl => l_token_tbl
3730 );
3731 l_return_status := FND_API.G_RET_STS_ERROR;
3732 ELSIF l_err_text IS NOT NULL AND
3733 l_rev_sub_res_unexp_rec.resource_id IS NULL
3734 THEN
3735 Error_Handler.Add_Error_Token
3736 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3737 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3738 , p_Message_text =>
3739 'Unexpected Error ' || l_err_text || ' in ' ||
3740 G_PKG_NAME
3741 , p_token_tbl => l_token_tbl
3742 );
3743 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
3744 END IF;
3745 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3746 Error_Handler.Write_Debug('After converting, Resource Id is '
3747 || to_char(l_rev_sub_res_unexp_rec.resource_id)
3748 || ' Status ' || l_return_status);
3749 END IF;
3750 END IF;
3751
3752 --
3753 -- Set substitute group number
3754 --
3755 --l_rev_sub_res_unexp_rec.substitute_group_number :=
3756 -- p_rev_sub_resource_rec.schedule_sequence_number;
3757
3758 IF nvl(Bom_Globals.Get_Caller_Type,'') <> 'MIGRATION' THEN
3759 IF not Bom_Rtg_Validate.group_num_exist_In_Op_Res
3760 ( p_substitute_group_number =>
3761 p_rev_sub_resource_rec.substitute_group_number
3762 , p_operation_sequence_id =>
3763 l_rev_sub_res_unexp_rec.operation_sequence_id )
3764 THEN
3765 l_token_tbl(1).token_name :=
3766 'SUBSTITUTE_GROUP_NUMBER';
3767 l_token_tbl(1).token_value :=
3768 p_rev_sub_resource_rec.substitute_group_number ;
3769 Error_Handler.Add_Error_Token
3770 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3771 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3772 , p_Message_name =>
3773 'BOM_SUB_RES_RELRES_NOT_FOUND'
3774 , p_token_tbl => l_token_tbl
3775 );
3776 l_return_status := FND_API.G_RET_STS_ERROR;
3777 END IF;
3778 END IF;
3779
3780 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3781 Error_Handler.Write_Debug('After set substitute group num '
3782 || to_char(nvl(p_rev_sub_resource_rec.substitute_group_number, l_rev_sub_res_unexp_rec.substitute_group_number))
3783 || ' verify it. Status ' || l_return_status);
3784 END IF;
3785
3786 END IF; -- Added by MK on 12/04/00 BOM_Rtg_Globals.G_RTG_BO specific
3787
3788 x_return_status := l_return_status;
3789 x_rev_sub_res_unexp_rec := l_rev_sub_res_unexp_rec;
3790 x_mesg_token_tbl := l_mesg_token_tbl;
3791
3792 END Rev_Sub_Resource_UUI_To_UI;
3793
3794
3795 /*********************************************************************
3796 * Procedure : Sub_resource_VID
3797 * Returns : None
3798 * Parameters IN : Substitute resource exposed Record
3799 * Substitute resource Unexposed Record
3800 * Parameters OUT: Substitute resoruce Unexposed Record
3801 * Return Status
3802 * Message Token Table
3803 * Purpose : Convert Routing Sub Op Resource to Revised Sub Op
3804 * Resource and Call Rev_Sub_resource_VID for ECO Bo.
3805 * After calling Rev_Sub_resource_VID, convert
3806 * Revised Sub Op Resource record back to Routing Sub Op Resource
3807 *********************************************************************/
3808 PROCEDURE Sub_Resource_VID
3809 ( p_sub_resource_rec IN Bom_Rtg_Pub.Sub_Resource_Rec_Type
3810 , p_sub_res_unexp_rec IN Bom_Rtg_Pub.Sub_Res_Unexposed_Rec_Type
3811 , x_sub_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Sub_Res_Unexposed_Rec_Type
3812 , x_Return_Status IN OUT NOCOPY VARCHAR2
3813 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3814 )
3815
3816 IS
3817 l_rev_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type ;
3818 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type ;
3819 l_sub_resource_rec Bom_Rtg_Pub.Sub_Resource_Rec_Type ;
3820
3821 BEGIN
3822 -- Convert Routing Operation to ECO Operation
3823 Bom_Rtg_Pub.Convert_RtgSubRes_To_EcoSubRes
3824 ( p_rtg_sub_resource_rec => p_sub_resource_rec
3825 , p_rtg_sub_res_unexp_rec => p_sub_res_unexp_rec
3826 , x_rev_sub_resource_rec => l_rev_sub_resource_rec
3827 , x_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3828 ) ;
3829
3830 -- Call Rev_Sub_resource_UUI_To_UI
3831 Bom_Rtg_Val_To_Id.Rev_Sub_resource_VID
3832 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
3833 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3834 , x_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3835 , x_return_status => x_return_status
3836 , x_mesg_token_tbl => x_mesg_token_tbl
3837 ) ;
3838
3839 -- Convert Eco Sub Resource Record back to Routing Sub Resource
3840 Bom_Rtg_Pub.Convert_EcoSubRes_To_RtgSubRes
3841 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
3842 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
3843 , x_rtg_sub_resource_rec => l_sub_resource_rec
3844 , x_rtg_sub_res_unexp_rec => x_sub_res_unexp_rec
3845 ) ;
3846
3847
3848 END Sub_Resource_VID ;
3849
3850
3851 /*********************************************************************
3852 * Procedure : Rev_Sub_resource_VID
3853 * Returns : None
3854 * Parameters IN : Rev Substitute resource exposed Record
3855 * Rev Substitute resource Unexposed Record
3856 * Parameters OUT: Rev Substitute resoruce Unexposed Record
3857 * Return Status
3858 * Message Token Table
3859 * Purpose : This is the access procedure which the private API
3860 * will call to perform the substitute resource record
3861 * value to ID conversions. If any of the conversions
3862 * fail then the the procedure will return with an error
3863 * status and the messsage token table filled with
3864 * appropriate error message.
3865 *********************************************************************/
3866 PROCEDURE Rev_Sub_Resource_VID
3867 ( p_rev_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type
3868 , p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
3869 , x_rev_sub_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
3870 , x_Return_Status IN OUT NOCOPY VARCHAR2
3871 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3872 )
3873 IS
3874 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
3875 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3876 l_err_text VARCHAR2(2000);
3877 l_Token_Tbl Error_Handler.Token_Tbl_Type;
3878 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
3879 := p_rev_sub_res_unexp_rec ;
3880 BEGIN
3881
3882 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3883 Error_Handler.Write_Debug('Substitute resource VID conversion . . .');
3884 END IF;
3885
3886 --
3887 -- Convert activity to activity id
3888 --
3889
3890 IF p_rev_sub_resource_rec.activity IS NOT NULL AND
3891 p_rev_sub_resource_rec.activity <> FND_API.G_MISS_CHAR
3892 THEN
3893
3894 l_rev_sub_res_unexp_rec.activity_id :=
3895 Activity_Id
3896 ( p_activity =>
3897 p_rev_sub_resource_rec.activity
3898 , p_organization_id =>
3899 l_rev_sub_res_unexp_rec.organization_id
3900 , x_err_text => l_err_text
3901 );
3902
3903 IF l_rev_sub_res_unexp_rec.activity_id IS NULL
3904 THEN
3905 l_token_tbl(1).token_name := 'ACTIVITY';
3906 l_token_tbl(1).token_value :=
3907 p_rev_sub_resource_rec.activity;
3908 Error_Handler.Add_Error_Token
3909 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3910 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3911 , p_Message_name =>
3912 'BOM_SUB_RES_ACT_CD_INVALID'
3913 , p_token_tbl => l_token_tbl
3914 );
3915 l_return_status := FND_API.G_RET_STS_ERROR;
3916 ELSIF l_err_text IS NOT NULL AND
3917 l_rev_sub_res_unexp_rec.activity_id IS NULL
3918 THEN
3919 Error_Handler.Add_Error_Token
3920 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3921 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3922 , p_Message_text =>
3923 'Unexpected Error ' || l_err_text || ' in ' ||
3924 G_PKG_NAME
3925 , p_token_tbl => l_token_tbl
3926 );
3927 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
3928 END IF;
3929 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3930 Error_Handler.Write_Debug('Converted activity id. '|| to_char(l_rev_sub_res_unexp_rec.activity_id) );
3931 END IF;
3932
3933 END IF;
3934
3935
3936 --
3937 -- Convert new resource code to new reosurce id
3938 --
3939 IF p_rev_sub_resource_rec.new_sub_resource_code IS NOT NULL AND
3940 p_rev_sub_resource_rec.new_sub_resource_code <>
3941 FND_API.G_MISS_CHAR
3942 THEN
3943
3944 l_rev_sub_res_unexp_rec.new_resource_id :=
3945 Resource_Id
3946 ( p_resource_code =>
3947 p_rev_sub_resource_rec.new_sub_resource_code
3948 , p_organization_id =>
3949 l_rev_sub_res_unexp_rec.organization_id
3950 , x_err_text => l_err_text
3951 );
3952
3953
3954 IF l_rev_sub_res_unexp_rec.new_resource_id IS NULL
3955 THEN
3956 l_token_tbl(1).token_name := 'RESOURCE_CODE';
3957 l_token_tbl(1).token_value :=
3958 p_rev_sub_resource_rec.new_sub_resource_code ;
3959 Error_Handler.Add_Error_Token
3960 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3961 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3962 , p_Message_name =>
3963 'BOM_SUB_RES_SUB_RES_CD_INVALID'
3964 , p_token_tbl => l_token_tbl
3965 );
3966 l_return_status := FND_API.G_RET_STS_ERROR;
3967 ELSIF l_err_text IS NOT NULL AND
3968 l_rev_sub_res_unexp_rec.new_resource_id IS NULL
3969 THEN
3970 Error_Handler.Add_Error_Token
3971 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
3972 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3973 , p_Message_text =>
3974 'Unexpected Error ' || l_err_text || ' in ' ||
3975 G_PKG_NAME
3976 , p_token_tbl => l_token_tbl
3977 );
3978 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
3979 END IF;
3980
3981 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3982 ('Converted new sub resource id. ' || to_char(l_rev_sub_res_unexp_rec.new_resource_id) ) ;
3983 END IF;
3984
3985 END IF;
3986
3987 --
3988 -- Convert setup code to setup id
3989 --
3990 IF p_rev_sub_resource_rec.setup_type IS NOT NULL AND
3991 p_rev_sub_resource_rec.setup_type <> FND_API.G_MISS_CHAR
3992 THEN
3993 l_rev_sub_res_unexp_rec.setup_id :=
3994 Setup_Id
3995 ( p_setup_type =>
3996 p_rev_sub_resource_rec.setup_type
3997 , p_organization_id =>
3998 l_rev_sub_res_unexp_rec.organization_id
3999 , x_err_text => l_err_text
4000 );
4001
4002 IF l_rev_sub_res_unexp_rec.setup_id IS NULL
4003 THEN
4004 l_token_tbl(1).token_name := 'SETUP_CODE';
4005 l_token_tbl(1).token_value :=
4006 p_rev_sub_resource_rec.setup_type ;
4007 Error_Handler.Add_Error_Token
4008 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
4009 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4010 , p_Message_name =>
4011 'BOM_SUB_RES_SETUP_CODE_INVALID'
4012 , p_token_tbl => l_token_tbl
4013 );
4014 l_return_status := FND_API.G_RET_STS_ERROR;
4015 ELSIF l_err_text IS NOT NULL AND
4016 l_rev_sub_res_unexp_rec.setup_id IS NULL
4017 THEN
4018 Error_Handler.Add_Error_Token
4019 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
4020 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4021 , p_Message_text =>
4022 'Unexpected Error ' || l_err_text || ' in ' ||
4023 G_PKG_NAME
4024 , p_token_tbl => l_token_tbl
4025 );
4026 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
4027 END IF;
4028
4029 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
4030 Error_Handler.Write_Debug ('Converted setup code . . .');
4031 END IF;
4032
4033 END IF;
4034
4035
4036 x_return_status := l_return_status;
4037 x_rev_sub_res_unexp_rec := l_rev_sub_res_unexp_rec;
4038 x_mesg_token_tbl := l_mesg_token_tbl;
4039
4040 END Rev_Sub_Resource_VID;
4041
4042
4043 -- Network
4044 /*********************************************************************
4045 * Procedure : OP_Network_UUI_To_UI
4046 * Returns : None
4047 * Parameters IN : Operation Network exposed exposed Record
4048 * Operation Network Unexposed Record
4049 * Parameters OUT: Operation Network unexposed record
4050 * Message Token Table
4051 * Return Status
4052 * Purpose : This procedure will perform all the required
4053 * User unique to Unique index conversions for
4054 * operation network record. Any errors will be
4055 * logged in the Message table and a return satus of
4056 * success or failure will be returned to the calling
4057 * program.
4058 *********************************************************************/
4059 PROCEDURE OP_Network_UUI_To_UI
4060 ( p_op_network_rec IN Bom_Rtg_Pub.Op_Network_Rec_Type
4061 , p_op_network_unexp_rec IN Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
4062 , x_op_network_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type
4063 , x_return_status IN OUT NOCOPY VARCHAR2
4064 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
4065 )
4066 IS
4067 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
4068 l_op_network_unexp_rec Bom_Rtg_Pub.Op_Network_Unexposed_Rec_Type;
4069 l_return_status VARCHAR2(1);
4070 l_err_text VARCHAR2(2000);
4071 l_cfm_flag NUMBER;
4072 l_err_text_diff VARCHAR2(1);
4073
4074 CURSOR c_Check_Alternate( p_alt_designator VARCHAR2,
4075 p_organization_id NUMBER ) IS
4076 SELECT 1
4077 FROM bom_alternate_designators
4078 WHERE alternate_designator_code = p_alt_designator
4079 AND organization_id = p_organization_id;
4080
4081 /***BEGIN 1838261***/
4082 x_temp_op_rec BOM_RTG_Globals.Temp_Op_Rec_Type;
4083 /***END 1838261***/
4084 l_temp_op_rec_tbl_test BOM_RTG_Globals.Temp_Op_Rec_Tbl_Type;--for testing by Dev
4085 BEGIN
4086 x_return_status := FND_API.G_RET_STS_SUCCESS;
4087 l_return_status := FND_API.G_RET_STS_SUCCESS;
4088 l_op_network_unexp_rec := p_op_network_unexp_rec;
4089
4090 IF BOM_Rtg_Globals.Get_Debug = 'Y'
4091 THEN Error_Handler.Write_Debug
4092 ('Operation network record UUI-UI Conversion . . ');
4093 END IF;
4094
4095 --
4096 -- Assembly Item name cannot be NULL or missing.
4097 --
4098 IF p_op_network_rec.assembly_item_name IS NULL OR
4099 p_op_network_rec.assembly_item_name = FND_API.G_MISS_CHAR
4100 THEN
4101 Error_Handler.Add_Error_Token
4102 ( p_message_name => 'BOM_OP_NWK_AITEM_KEYCOL_NULL'
4103 , p_mesg_token_tbl => l_mesg_token_tbl
4104 , x_mesg_token_tbl => l_mesg_token_tbl
4105 );
4106
4107 l_return_status := FND_API.G_RET_STS_ERROR;
4108 END IF;
4109
4110 --
4111 -- From operation sequence number cannot be NULL or missing.
4112 --
4113 IF p_op_network_rec.from_op_seq_number IS NULL OR
4114 p_op_network_rec.from_op_seq_number
4115 = FND_API.G_MISS_NUM
4116 THEN
4117 Error_Handler.Add_Error_Token
4118 ( p_Message_Name => 'BOM_OP_NWK_SEQNUM_KEYCOL_NULL'
4119 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4120 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4121 );
4122 l_Return_Status := FND_API.G_RET_STS_ERROR;
4123 END IF;
4124
4125 --
4126 -- To operation sequence number cannot be NULL or missing.
4127 --
4128 IF p_op_network_rec.to_op_seq_number IS NULL OR
4129 p_op_network_rec.to_op_seq_number
4130 = FND_API.G_MISS_NUM
4131 THEN
4132 Error_Handler.Add_Error_Token
4133 ( p_Message_Name => 'BOM_OP_NWK_SEQNUM_KEYCOL_NULL'
4134 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4135 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4136 );
4137 l_Return_Status := FND_API.G_RET_STS_ERROR;
4138 END IF;
4139
4140 --
4141 -- From start effective date cannot be NULL or missing.
4142 --
4143 IF ( p_op_network_rec.from_start_effective_date IS NULL OR
4144 p_op_network_rec.from_start_effective_date
4145 = FND_API.G_MISS_DATE)
4146 AND ( NVL(p_op_network_rec.operation_type,1) = 1
4147 OR p_op_network_rec.operation_type = FND_API.G_MISS_NUM )
4148 THEN
4149 Error_Handler.Add_Error_Token
4150 ( p_Message_Name => 'BOM_OP_NWK_EFFDT_KEYCOL_NULL'
4151 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4152 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4153 );
4154 l_Return_Status := FND_API.G_RET_STS_ERROR;
4155 END IF;
4156
4157 --
4158 -- To start effective date cannot be NULL or missing.
4159 --
4160 IF ( p_op_network_rec.to_start_effective_date IS NULL OR
4161 p_op_network_rec.to_start_effective_date
4162 = FND_API.G_MISS_DATE)
4163 AND ( NVL(p_op_network_rec.operation_type,1) = 1
4164 OR p_op_network_rec.operation_type = FND_API.G_MISS_NUM )
4165 THEN
4166 Error_Handler.Add_Error_Token
4167 ( p_Message_Name => 'BOM_OP_NWK_EFFDT_KEYCOL_NULL'
4168 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4169 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4170 );
4171 l_Return_Status := FND_API.G_RET_STS_ERROR;
4172 END IF;
4173
4174 --
4175 -- If key columns are NULL, then return.
4176 --
4177
4178 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4179 THEN
4180 x_Return_Status := FND_API.G_RET_STS_ERROR;
4181 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4182 RETURN;
4183 END IF;
4184
4185
4186 --
4187 -- Assembly item name must be successfully converted to id.
4188 --
4189
4190 l_op_network_unexp_rec.assembly_item_id :=
4191 Assembly_Item ( p_assembly_item_name =>
4192 p_op_network_rec.assembly_item_name
4193 , p_organization_id =>
4194 l_op_network_unexp_rec.organization_id
4195 , x_err_text => l_err_text
4196 );
4197
4198 IF l_op_network_unexp_rec.assembly_item_id IS NULL
4199 THEN
4200 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
4201 g_token_tbl(1).token_value :=
4202 p_op_network_rec.assembly_item_name;
4203 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
4204 g_token_tbl(2).token_value :=
4205 p_op_network_rec.organization_code;
4206 Error_Handler.Add_Error_Token
4207 ( p_Message_Name => 'BOM_RTG_AITEM_DOESNOT EXIST'
4208 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4209 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4210 , p_Token_Tbl => g_Token_Tbl
4211 );
4212 l_Return_Status := FND_API.G_RET_STS_ERROR;
4213 ELSIF l_err_text IS NOT NULL AND
4214 ( l_op_network_unexp_rec.assembly_item_id IS NULL OR
4215 l_op_network_unexp_rec.assembly_item_id = FND_API.G_MISS_NUM
4216 )
4217 THEN
4218 -- This is an unexpected error.
4219 Error_Handler.Add_Error_Token
4220 ( p_Message_Name => NULL
4221 , p_Message_Text => l_err_text || ' in ' ||
4222 G_PKG_NAME
4223 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4224 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4225 );
4226 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4227 END IF;
4228
4229 IF BOM_Rtg_Globals.Get_Debug = 'Y'
4230 THEN Error_Handler.Write_Debug
4231 ('After converting Assembly Item Id : '
4232 || to_char(l_op_network_unexp_rec.assembly_item_id)
4233 || ' Status ' || l_return_status);
4234 END IF;
4235
4236 IF p_op_network_rec.alternate_routing_code IS NOT NULL AND
4237 p_op_network_rec.alternate_routing_code <> FND_API.G_MISS_CHAR
4238 THEN
4239 /*l_err_text_diff*/l_err_text := FND_API.G_RET_STS_ERROR;
4240
4241 FOR check_alternate IN
4242 c_Check_Alternate
4243 ( p_alt_designator =>
4244 p_op_network_rec.alternate_routing_code,
4245 p_organization_id =>
4246 l_op_network_unexp_rec.organization_id )
4247 LOOP
4248 /*l_err_text_diff*/l_err_text := FND_API.G_RET_STS_SUCCESS;
4249 END LOOP;
4250
4251 IF /*l_err_text_diff*/l_err_text <> FND_API.G_RET_STS_SUCCESS
4252 THEN
4253 g_token_tbl(1).token_name :='ALTERNATE_ROUTING_CODE';
4254 g_token_tbl(1).token_value :=
4255 p_op_network_rec.alternate_routing_code;
4256 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
4257 g_token_tbl(2).token_value :=
4258 p_op_network_rec.organization_code;
4259 Error_Handler.Add_Error_Token
4260 ( P_Message_Name =>
4261 'BOM_RTG_ALT_DESIGNATOR_INVALID'
4262 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4263 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4264 , p_token_tbl => g_token_tbl
4265 );
4266
4267 l_return_status := FND_API.G_RET_STS_ERROR;
4268 END IF;
4269
4270 END IF;
4271
4272 l_op_network_unexp_rec.routing_sequence_id :=
4273 Routing_Sequence_id
4274 ( p_assembly_item_id =>
4275 l_op_network_unexp_rec.assembly_item_id
4276 , p_organization_id =>
4277 l_op_network_unexp_rec.organization_id
4278 , p_alternate_routing_designator =>
4279 p_op_network_rec.alternate_routing_code
4280 , x_err_text => l_err_text
4281 );
4282
4283 IF l_op_network_unexp_rec.routing_sequence_id IS NULL
4284 THEN
4285 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
4286 g_token_tbl(1).token_value :=
4287 p_op_network_rec.assembly_item_name;
4288 Error_Handler.Add_Error_Token
4289 ( p_Message_Name => 'BOM_OP_NWK_RTG_NOT_FOUND'
4290 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4291 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4292 , p_Token_Tbl => g_Token_Tbl
4293 );
4294 l_Return_Status := FND_API.G_RET_STS_ERROR;
4295 ELSIF l_err_text IS NOT NULL AND
4296 (l_op_network_unexp_rec.routing_sequence_id IS NULL OR
4297 l_op_network_unexp_rec.routing_sequence_id
4298 = FND_API.G_MISS_NUM)
4299 THEN
4300 -- This is an unexpected error.
4301 Error_Handler.Add_Error_Token
4302 ( p_Message_Name => NULL
4303 , p_Message_Text => l_err_text || ' in ' ||
4304 G_PKG_NAME
4305 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4306 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4307 );
4308 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4309 END IF;
4310
4311 IF BOM_Rtg_Globals.Get_Debug = 'Y'
4312 THEN Error_Handler.Write_Debug
4313 ('After converting, routing sequence id is '
4314 || to_char(l_op_network_unexp_rec.routing_sequence_id )
4315 || ' Status ' || l_return_status);
4316 END IF;
4317
4318 --
4319 -- For operation network, CFM flag should be in (1, 3)
4320 --
4321
4322 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag IS NULL OR
4323 BOM_Rtg_Globals.Get_CFM_Rtg_Flag = FND_API.G_MISS_NUM
4324 THEN
4325 l_cfm_flag := Bom_Rtg_Validate.Get_Flow_Routing_Flag(
4326 l_op_network_unexp_rec.routing_sequence_id) ;
4327 BOM_Rtg_Globals.Set_CFM_Rtg_Flag(p_cfm_rtg_type => l_cfm_flag) ;
4328
4329 ELSE l_cfm_flag := BOM_Rtg_Globals.Get_CFM_Rtg_Flag ;
4330 END IF;
4331
4332 /*
4333 -- For eAM enhancement, following cfm routing flag validation
4334 -- is moved to BOM_Validate_Op_Network.Check_Access procedure
4335 IF l_cfm_flag <> BOM_Rtg_Globals.G_FLOW_RTG
4336 AND l_cfm_flag <> BOM_Rtg_Globals.G_LOT_RTG
4337 THEN
4338 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
4339 g_token_tbl(1).token_value :=
4340 p_op_network_rec.assembly_item_name;
4341 Error_Handler.Add_Error_Token
4342 ( p_Message_Name => 'BOM_OP_NWK_RTG_INVALID'
4343 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4344 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4345 , p_Token_Tbl => g_Token_Tbl
4346 );
4347 l_return_status := FND_API.G_RET_STS_ERROR ;
4348 END IF ;
4349 */
4350
4351 --
4352 -- For flow routing, operatoin Type should be set in (2, 3)
4353 --
4354
4355 IF l_cfm_flag = BOM_Rtg_Globals.G_FLOW_RTG
4356 THEN
4357 IF p_op_network_rec.operation_type IS NULL
4358 OR p_op_network_rec.operation_type = FND_API.G_MISS_NUM
4359 OR p_op_network_rec.operation_type NOT IN (2, 3)
4360 THEN
4361 IF p_op_network_rec.operation_type <> FND_API.G_MISS_NUM
4362 THEN
4363 g_token_tbl(1).token_name := 'OPERATION_TYPE';
4364 g_token_tbl(1).token_value :=
4365 p_op_network_rec.operation_type;
4366 END IF ;
4367
4368 Error_Handler.Add_Error_Token
4369 ( p_Message_Name => 'BOM_FLM_OP_NWK_TYPE_INVALID'
4370 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4371 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4372 , p_Token_Tbl => g_Token_Tbl
4373 );
4374 l_return_status := FND_API.G_RET_STS_ERROR ;
4375
4376 END IF ;
4377
4378 ELSIF l_cfm_flag = BOM_Rtg_Globals.G_LOT_RTG
4379 OR l_cfm_flag = BOM_Rtg_Globals.G_STD_RTG
4380 THEN
4381 IF p_op_network_rec.operation_type IS NOT NULL
4382 AND p_op_network_rec.operation_type <> FND_API.G_MISS_NUM
4383 AND p_op_network_rec.operation_type <> 1
4384 THEN
4385 g_token_tbl(1).token_name := 'OPERATION_TYPE';
4386 g_token_tbl(1).token_value :=
4387 p_op_network_rec.operation_type;
4388 Error_Handler.Add_Error_Token
4389 ( p_Message_Name => 'BOM_OP_NWK_OP_TYPE_IGNORED'
4390 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4391 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4392 , p_Token_Tbl => g_Token_Tbl
4393 );
4394 l_return_status := FND_API.G_RET_STS_ERROR ;
4395 END IF ;
4396
4397 END IF;
4398
4399 --
4400 -- If error in CFM routing check, then return.
4401 --
4402
4403 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4404 THEN
4405 x_Return_Status := FND_API.G_RET_STS_ERROR;
4406 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4407 RETURN;
4408 END IF;
4409
4410
4411 --
4412 -- Get From_operation_sequence_id
4413 --
4414 -- BOM_RTG_Globals.Set_Temp_Op_Tbl(l_temp_op_rec_tbl_test);--for testing by Dev
4415
4416 l_op_network_unexp_rec.from_op_seq_id :=
4417 Operation_Sequence_id
4418 ( p_routing_sequence_id =>
4419 l_op_network_unexp_rec.routing_sequence_id
4420 , p_operation_type =>
4421 p_op_network_rec.operation_type
4422 , p_operation_seq_num =>
4423 p_op_network_rec.from_op_seq_number
4424 , p_effectivity_date =>
4425 p_op_network_rec.from_start_effective_date
4426 , x_err_text => l_err_text
4427 );
4428 /***BEGIN 1838261***/
4429
4430 IF l_op_network_unexp_rec.from_op_seq_id IS NULL THEN
4431
4432 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_Lot_Rtg THEN
4433
4434 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
4435
4436 l_op_network_unexp_rec.from_op_seq_id :=
4437 Operation_Sequence_id
4438 ( p_routing_sequence_id =>
4439 l_op_network_unexp_rec.routing_sequence_id
4440 , p_operation_type =>
4441 p_op_network_rec.operation_type
4442 , p_operation_seq_num =>
4443 x_temp_op_rec.new_op_seq_num
4444 , p_effectivity_date =>
4445 x_temp_op_rec.new_start_eff_date
4446 , x_err_text => l_err_text
4447 );
4448 END IF;
4449 ELSE
4450 IF BOM_RTG_Globals.Get_Temp_Op_Rec(p_op_network_rec.from_op_seq_number, x_temp_op_rec) THEN
4451 l_op_network_unexp_rec.from_op_seq_id :=
4452 Operation_Sequence_id
4453 ( p_routing_sequence_id =>
4454 l_op_network_unexp_rec.routing_sequence_id
4455 , p_operation_type =>
4456 p_op_network_rec.operation_type
4457 , p_operation_seq_num =>
4458 x_temp_op_rec.new_op_seq_num
4459 , p_effectivity_date =>
4460 x_temp_op_rec.new_start_eff_date
4461 , x_err_text => l_err_text
4462 );
4463 END IF;
4464 END IF;
4465 END IF;
4466 /***END 1838261***/
4467
4468 IF l_op_network_unexp_rec.from_op_seq_id IS NULL
4469 THEN
4470 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4471 g_token_tbl(1).token_value :=
4472 p_op_network_rec.from_op_seq_number;
4473 Error_Handler.Add_Error_Token
4474 ( p_Message_Name => 'BOM_OP_NWK_FROM_OP_NOT_FOUND'
4475 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4476 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4477 , p_Token_Tbl => g_Token_Tbl
4478 );
4479 l_Return_Status := FND_API.G_RET_STS_ERROR;
4480 ELSIF l_err_text IS NOT NULL AND
4481 (l_op_network_unexp_rec.from_op_seq_id IS NULL OR
4482 l_op_network_unexp_rec.from_op_seq_id
4483 = FND_API.G_MISS_NUM )
4484 THEN
4485 -- This is an unexpected error.
4486 Error_Handler.Add_Error_Token
4487 ( p_Message_Name => NULL
4488 , p_Message_Text => l_err_text || ' in ' ||
4489 G_PKG_NAME
4490 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4491 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4492 );
4493 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4494 END IF;
4495
4496 IF BOM_Rtg_Globals.Get_Debug = 'Y'
4497 THEN Error_Handler.Write_Debug
4498 ('After converting, From Op Seq Id is '
4499 || to_char(l_op_network_unexp_rec.from_op_seq_id)
4500 || ' Status ' || l_return_status);
4501 END IF;
4502
4503 --
4504 -- Get To_operation_sequence_id
4505 --
4506 l_op_network_unexp_rec.to_op_seq_id :=
4507 Operation_Sequence_Id
4508 ( p_routing_sequence_id =>
4509 l_op_network_unexp_rec.routing_sequence_id
4510 , p_operation_type =>
4511 p_op_network_rec.operation_type
4512 , p_operation_seq_num =>
4513 p_op_network_rec.to_op_seq_number
4514 , p_effectivity_date =>
4515 p_op_network_rec.to_start_effective_date
4516 , x_err_text => l_err_text
4517 );
4518
4519 /***BEGIN 1838261***/
4520
4521 IF l_op_network_unexp_rec.to_op_seq_id IS NULL THEN
4522 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_Lot_Rtg THEN
4523 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
4524 l_op_network_unexp_rec.to_op_seq_id :=
4525 Operation_Sequence_id
4526 ( p_routing_sequence_id =>
4527 l_op_network_unexp_rec.routing_sequence_id
4528 , p_operation_type =>
4529 p_op_network_rec.operation_type
4530 , p_operation_seq_num =>
4531 x_temp_op_rec.new_op_seq_num
4532 , p_effectivity_date =>
4533 x_temp_op_rec.new_start_eff_date
4534 , x_err_text => l_err_text
4535 );
4536 END IF;
4537 ELSE
4538 IF BOM_RTG_Globals.Get_Temp_Op_Rec(p_op_network_rec.to_op_seq_number, x_temp_op_rec) THEN
4539 l_op_network_unexp_rec.to_op_seq_id :=
4540 Operation_Sequence_id
4541 ( p_routing_sequence_id =>
4542 l_op_network_unexp_rec.routing_sequence_id
4543 , p_operation_type =>
4544 p_op_network_rec.operation_type
4545 , p_operation_seq_num =>
4546 x_temp_op_rec.new_op_seq_num
4547 , p_effectivity_date =>
4548 x_temp_op_rec.new_start_eff_date
4549 , x_err_text => l_err_text
4550 );
4551 END IF;
4552 END IF;
4553 END IF;
4554 /***END 1838261***/
4555
4556 IF l_op_network_unexp_rec.to_op_seq_id IS NULL
4557 THEN
4558 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4559 g_token_tbl(1).token_value :=
4560 p_op_network_rec.to_op_seq_number;
4561 Error_Handler.Add_Error_Token
4562 ( p_Message_Name => 'BOM_OP_NWK_TO_OP_NOT_FOUND'
4563 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4564 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4565 , p_Token_Tbl => g_Token_Tbl
4566 );
4567 l_Return_Status := FND_API.G_RET_STS_ERROR;
4568
4569 ELSIF l_err_text IS NOT NULL AND
4570 (l_op_network_unexp_rec.to_op_seq_id IS NULL OR
4571 l_op_network_unexp_rec.to_op_seq_id
4572 = FND_API.G_MISS_NUM )
4573 THEN
4574 -- This is an unexpected error.
4575 Error_Handler.Add_Error_Token
4576 ( p_Message_Name => NULL
4577 , p_Message_Text => l_err_text || ' in ' ||
4578 G_PKG_NAME
4579 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4580 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4581 );
4582 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4583 END IF;
4584
4585 IF BOM_Rtg_Globals.Get_Debug = 'Y'
4586 THEN Error_Handler.Write_Debug
4587 ('After converting, To Op Seq Id is '
4588 || to_char(l_op_network_unexp_rec.to_op_seq_id)
4589 || ' Status ' || l_return_status);
4590 END IF;
4591
4592 --
4593 -- Get new_from_operation_sequence_id
4594 --
4595 IF ( p_op_network_rec.new_from_op_seq_number IS NOT NULL AND
4596 p_op_network_rec.new_from_op_seq_number <> FND_API.G_MISS_NUM )
4597 OR ( p_op_network_rec.new_from_start_effective_date IS NOT NULL AND
4598 p_op_network_rec.new_from_start_effective_date <> FND_API.G_MISS_DATE )
4599 THEN
4600
4601 l_op_network_unexp_rec.new_from_op_seq_id :=
4602 Operation_Sequence_id
4603 ( p_routing_sequence_id =>
4604 l_op_network_unexp_rec.routing_sequence_id
4605 , p_operation_type =>
4606 p_op_network_rec.operation_type
4607 , p_operation_seq_num =>
4608 p_op_network_rec.new_from_op_seq_number
4609 , p_effectivity_date =>
4610 p_op_network_rec.new_from_start_effective_date
4611 , x_err_text => l_err_text
4612 );
4613
4614 /***BEGIN 1838261***/
4615 IF l_op_network_unexp_rec.new_from_op_seq_id IS NULL THEN
4616 IF BOM_RTG_Globals.Get_Temp_Op_Rec(p_op_network_rec.new_from_op_seq_number, x_temp_op_rec) THEN
4617 l_op_network_unexp_rec.new_from_op_seq_id :=
4618 Operation_Sequence_id
4619 ( p_routing_sequence_id =>
4620 l_op_network_unexp_rec.routing_sequence_id
4621 , p_operation_type =>
4622 p_op_network_rec.operation_type
4623 , p_operation_seq_num =>
4624 x_temp_op_rec.new_op_seq_num
4625 , p_effectivity_date =>
4626 x_temp_op_rec.new_start_eff_date
4627 , x_err_text => l_err_text
4628 );
4629 END IF;
4630 END IF;
4631 /***END 1838261***/
4632 IF l_op_network_unexp_rec.new_from_op_seq_id IS NULL
4633 THEN
4634 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4635 g_token_tbl(1).token_value :=
4636 p_op_network_rec.new_from_op_seq_number;
4637 Error_Handler.Add_Error_Token
4638 ( p_Message_Name => 'BOM_OP_NWK_FROM_OP_NOT_FOUND'
4639 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4640 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4641 , p_Token_Tbl => g_Token_Tbl
4642 );
4643 l_Return_Status := FND_API.G_RET_STS_ERROR;
4644
4645 ELSIF l_err_text IS NOT NULL AND
4646 (l_op_network_unexp_rec.new_from_op_seq_id IS NULL OR
4647 l_op_network_unexp_rec.new_from_op_seq_id
4648 = FND_API.G_MISS_NUM )
4649 THEN
4650 -- This is an unexpected error.
4651 Error_Handler.Add_Error_Token
4652 ( p_Message_Name => NULL
4653 , p_Message_Text => l_err_text || ' in ' ||
4654 G_PKG_NAME
4655 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4656 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4657 );
4658 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4659 END IF;
4660
4661 IF BOM_Rtg_Globals.Get_Debug = 'Y'
4662 THEN Error_Handler.Write_Debug
4663 ('After converting, New From Op Seq Id is '
4664 || to_char(l_op_network_unexp_rec.new_from_op_seq_id)
4665 || ' Status ' || l_return_status);
4666 END IF;
4667
4668 END IF ; -- new_from_op_seq_id
4669
4670 --
4671 -- Get new_to_operation_sequence_id
4672 --
4673 IF ( p_op_network_rec.new_to_op_seq_number IS NOT NULL AND
4674 p_op_network_rec.new_to_op_seq_number <> FND_API.G_MISS_NUM )
4675 OR ( p_op_network_rec.new_to_start_effective_date IS NOT NULL AND
4676 p_op_network_rec.new_to_start_effective_date <> FND_API.G_MISS_DATE )
4677 THEN
4678
4679 l_op_network_unexp_rec.new_to_op_seq_id :=
4680 Operation_Sequence_id
4681 ( p_routing_sequence_id =>
4682 l_op_network_unexp_rec.routing_sequence_id
4683 , p_operation_type =>
4684 p_op_network_rec.operation_type
4685 , p_operation_seq_num =>
4686 p_op_network_rec.new_to_op_seq_number
4687 , p_effectivity_date =>
4688 p_op_network_rec.new_to_start_effective_date
4689 , x_err_text => l_err_text
4690 );
4691
4692 /***BEGIN 1838261***/
4693 IF l_op_network_unexp_rec.new_to_op_seq_id IS NULL THEN
4694 IF BOM_RTG_Globals.Get_Temp_Op_Rec(p_op_network_rec.new_to_op_seq_number, x_temp_op_rec) THEN
4695 l_op_network_unexp_rec.new_to_op_seq_id :=
4696 Operation_Sequence_id
4697 ( p_routing_sequence_id =>
4698 l_op_network_unexp_rec.routing_sequence_id
4699 , p_operation_type =>
4700 p_op_network_rec.operation_type
4701 , p_operation_seq_num =>
4702 x_temp_op_rec.new_op_seq_num
4703 , p_effectivity_date =>
4704 x_temp_op_rec.new_start_eff_date
4705 , x_err_text => l_err_text
4706 );
4707 END IF;
4708 END IF;
4709 /***END 1838261***/
4710 IF l_op_network_unexp_rec.new_to_op_seq_id IS NULL
4711 THEN
4712 g_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4713 g_token_tbl(1).token_value :=
4714 p_op_network_rec.new_to_op_seq_number;
4715 Error_Handler.Add_Error_Token
4716 ( p_Message_Name => 'BOM_OP_NWK_TO_OP_NOT_FOUND'
4717 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4718 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4719 , p_Token_Tbl => g_Token_Tbl
4720 );
4721 l_Return_Status := FND_API.G_RET_STS_ERROR;
4722
4723 ELSIF l_err_text IS NOT NULL AND
4724 (l_op_network_unexp_rec.new_to_op_seq_id IS NULL OR
4725 l_op_network_unexp_rec.new_to_op_seq_id
4726 = FND_API.G_MISS_NUM )
4727 THEN
4728
4729 -- This is an unexpected error.
4730 Error_Handler.Add_Error_Token
4731 ( p_Message_Name => NULL
4732 , p_Message_Text => l_err_text || ' in ' ||
4733 G_PKG_NAME
4734 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4735 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4736 );
4737 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4738 END IF;
4739
4740 IF BOM_Rtg_Globals.Get_Debug = 'Y'
4741 THEN Error_Handler.Write_Debug
4742 ('After converting, New To Op Seq Id is '
4743 || to_char(l_op_network_unexp_rec.new_to_op_seq_id)
4744 || ' Status ' || l_return_status);
4745 END IF;
4746
4747 END IF ; -- to_from_op_seq_id
4748
4749
4750 x_return_status := l_return_status;
4751 x_op_network_unexp_rec := l_op_network_unexp_rec;
4752 x_mesg_token_tbl := l_mesg_token_tbl;
4753
4754 END OP_Network_UUI_To_UI;
4755
4756
4757
4758 END BOM_Rtg_Val_To_Id;