[Home] [Help]
PACKAGE BODY: APPS.BOM_VAL_TO_ID
Source
1 PACKAGE BODY BOM_Val_To_Id AS
2 /* $Header: BOMSVIDB.pls 120.3.12010000.3 2010/04/29 01:09:37 umajumde ship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMSVIDB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Val_To_Id
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 01-JUL-99 Rahul Chitko Initial Creation
21 --
22 -- 07-MAY-2001 Refai Farook EAM related changes
23 --
24 -- 21-AUG-01 Refai Farook One To Many support changes
25 --
26 -- 06-May-05 Abhishek Rudresh Common BOM Attr Updates
27 -- 13-JUL-06 Bhavnesh Patel Added support for Structure Type
28 ****************************************************************************/
29 G_Pkg_Name VARCHAR2(30) := 'BOM_Val_To_Id';
30 g_token_tbl Error_Handler.Token_Tbl_Type;
31
32 function parse_item_name (org_id IN Number,
33 item_name IN Varchar2,
34 id OUT NOCOPY Number,
35 err_text out NOCOPY Varchar2) return Number;
36 FUNCTION Get_BOM_Implementation_Date(p_bill_seq_id IN NUMBER)
37 RETURN DATE
38 IS
39 CURSOR c1 IS SELECT implementation_date FROM
40 bom_bill_of_materials WHERE
41 bill_sequence_id = p_bill_seq_id;
42 BEGIN
43 FOR r1 IN c1
44 LOOP
45 Return r1.implementation_date;
46 END LOOP;
47 EXCEPTION WHEN OTHERS THEN
48 Return NULL;
49 END;
50
51
52 --Bug 8850425 begin
53 FUNCTION Comp_Operation_Seq_Id( p_component_sequence_id IN NUMBER
54 , p_operation_sequence_number IN NUMBER
55 ) RETURN NUMBER
56 IS
57 l_id NUMBER;
58 BEGIN
59
60 select comp_operation_seq_id
61 into l_id
62 from bom_component_operations
63 where component_sequence_id = p_component_sequence_id
64 and operation_seq_num = p_operation_sequence_number;
65
66 RETURN l_id;
67
68 EXCEPTION
69 WHEN OTHERS THEN
70 RETURN NULL;
71
72 END Comp_Operation_Seq_Id;
73
74 --Bug 8850425 end
75
76
77 /********************************************************************
78 * Function : Get_EnforceInteger_Code
79 * Returns : NUMBER
80 * Purpose : Will convert the value of enforce integer requirements value
81 * into enforce integer requirements code
82 * If the conversion fails then the function will return
83 * a NULL otherwise will return the code.
84 * For an unexpected error function will return a
85 * missing value.
86 *********************************************************************/
87 FUNCTION Get_EnforceInteger_Code
88 ( p_enforce_integer IN VARCHAR2 )
89 RETURN NUMBER
90 IS
91 l_enforce_int_reqcode NUMBER;
92 BEGIN
93 SELECT lookup_code INTO l_enforce_int_reqcode FROM mfg_lookups WHERE
94 lookup_type = 'BOM_ENFORCE_INT_REQUIREMENTS' AND
95 upper(meaning) = upper(p_enforce_integer);
96 Return l_enforce_int_reqcode;
97 EXCEPTION WHEN OTHERS THEN
98 Return NULL;
99 END;
100
101
102 /********************************************************************
103 * Function : Operation Sequence Id
104 * Returns : NUMBER
105 * Purpose : Will convert the value of operation sequence number to
106 * operation sequence id.
107 * If the conversion fails then the function will return
108 * a NULL otherwise will return the opseq_id.
109 * For an unexpected error function will return a
110 * missing value.
111 *********************************************************************/
112 FUNCTION Operation_Sequence_Id
113 ( p_organization_id IN NUMBER
114 ,p_assembly_item_id IN NUMBER
115 ,p_alternate_bom_designator IN VARCHAR2
116 ,p_operation_sequence_number IN NUMBER
117 ,x_err_text IN OUT NOCOPY VARCHAR2) RETURN NUMBER
118 IS
119 l_id NUMBER;
120
121 BEGIN
122
123 SELECT operation_sequence_id
124 INTO l_id
125 FROM bom_operation_sequences bos
126 WHERE routing_sequence_id =
127 (SELECT common_routing_sequence_id
128 FROM bom_operational_routings bor
129 WHERE assembly_item_id = p_assembly_item_id
130 and organization_id = p_organization_id
131 and nvl(alternate_routing_designator, nvl(p_alternate_bom_designator, 'NONE')) =
132 nvl(p_alternate_bom_designator, 'NONE')
133 and ( p_alternate_bom_designator is null
134 or (p_alternate_bom_designator is not null
135 and (alternate_routing_designator = p_alternate_bom_designator
136 or not exists (SELECT null
137 FROM bom_operational_routings bor2
138 WHERE bor2.assembly_item_id = p_assembly_item_id
139 and bor2.organization_id = p_organization_id
140 and bor2.alternate_routing_designator =
141 p_alternate_bom_designator
142 )
143 )
144 )
145 )
146 )
147 and nvl(trunc(disable_date), trunc(sysdate)+1) > trunc(sysdate) and nvl(operation_type,1) = 1 and
148 operation_seq_num = p_operation_sequence_number;
149
150 RETURN l_id;
151
152 EXCEPTION WHEN NO_DATA_FOUND THEN
153 x_err_text := 'Id not found';
154 RETURN NULL;
155
156 WHEN TOO_MANY_ROWS THEN
157 x_err_text := 'Too many rows';
158 RETURN NULL;
159
160 WHEN OTHERS THEN
161 x_err_text := sqlerrm;
162 RETURN FND_API.G_MISS_NUM;
163
164 END Operation_Sequence_Id;
165
166
167 /********************************************************************
168 * Function : Organization
169 * Returns : NUMBER
170 * Purpose : Will convert the value of organization_code to
171 * organization_id using MTL_PARAMETERS.
172 * If the conversion fails then the function will return
173 * a NULL otherwise will return the org_id.
174 * For an unexpected error function will return a
175 * missing value.
176 *********************************************************************/
177 FUNCTION Organization
178 ( p_organization IN VARCHAR2
179 , x_err_text IN OUT NOCOPY VARCHAR2) RETURN NUMBER
180 IS
181 l_id NUMBER;
182 ret_code NUMBER;
183 l_err_text VARCHAR2(2000);
184 BEGIN
185 SELECT organization_id
186 INTO l_id
187 FROM mtl_parameters
188 WHERE organization_code = p_organization;
189
190 RETURN l_id;
191
192 EXCEPTION
193
194 WHEN NO_DATA_FOUND THEN
195 RETURN NULL;
196
197 WHEN OTHERS THEN
198 RETURN FND_API.G_MISS_NUM;
199
200 END Organization;
201
202 /**********************************************************************
203 * Function : Revised_Item
204 * Parameters IN : Revised Item Name
205 * Organization ID
206 * Parameters OUT: Error_Text
207 * Returns : Revised Item Id
208 * Purpose : This function will get the ID for the revised item and
209 * return the ID. If the revised item is invalid then the
210 * ID will returned as NULL.
211 **********************************************************************/
212 FUNCTION Revised_Item( p_revised_item_num IN VARCHAR2,
213 p_organization_id IN NUMBER,
214 x_err_text IN OUT NOCOPY VARCHAR2 )
215 RETURN NUMBER
216 IS
217 l_id NUMBER;
218 ret_code NUMBER;
219 l_err_text VARCHAR2(2000);
220 BEGIN
221
222 /* ret_code := INVPUOPI.mtl_pr_parse_flex_name(
223 org_id => p_organization_id,
224 flex_code => 'MSTK',
225 flex_name => p_revised_item_num,
226 flex_id => l_id,
227 set_id => -1,
228 err_text => x_err_text);*/
229 ret_code := parse_item_name(
230 org_id => p_organization_id,
231 item_name => p_revised_item_num,
232 id => l_id,
233 err_text => x_err_text);
234
235 IF (ret_code <> 0) THEN
236 RETURN NULL;
237 ELSE
238 RETURN l_id;
239 END IF;
240
241 END Revised_Item;
242
243 /*******************************************************************
244 * Function : Component_Item
245 * Parameters IN : Component Item Name
246 * Organization ID
247 * Parameters OUT: Error Message
248 * Returns : Component_Item_Id
249 * Purpose : Function will convert the component item name to its
250 * corresponsind ID and return the value.
251 * If the component is invalid, then a NULL is returned.
252 *********************************************************************/
253 FUNCTION COMPONENT_ITEM( p_organization_id IN NUMBER,
254 p_component_item_num IN VARCHAR2,
255 x_err_text IN OUT NOCOPY VARCHAR2)
256 return NUMBER
257 IS
258 l_id NUMBER;
259 ret_code NUMBER;
260 BEGIN
261
262 /* ret_code := INVPUOPI.mtl_pr_parse_flex_name(
263 org_id => p_organization_id,
264 flex_code => 'MSTK',
265 flex_name => p_component_item_num,
266 flex_id => l_id,
267 set_id => -1,
268 err_text => x_err_text); */
269 ret_code := parse_item_name(
270 org_id => p_organization_id,
271 item_name => p_component_item_num,
272 id => l_id,
273 err_text => x_err_text);
274
275 IF (ret_code <> 0) THEN
276 RETURN NULL;
277 END IF;
278
279 RETURN l_id;
280
281 END COMPONENT_ITEM;
282
283 /********************************************************************
284 * Function : Assembly_Item
285 * Returns : Number
286 * Parameters IN : Assembly Item Name
287 * Organization_Id
288 * Purpose : This function will get ID for the assembly item and
289 * return the ID. If the assembly item is invalid then
290 * ID will returned as NULL.
291 *********************************************************************/
292 FUNCTION Assembly_Item
293 ( p_assembly_item_name IN VARCHAR2
294 , p_organization_id IN NUMBER
295 , x_err_text IN OUT NOCOPY VARCHAR2) RETURN NUMBER
296 IS
297 BEGIN
298 RETURN Bom_Val_To_Id.Revised_Item
299 ( p_revised_item_num => p_assembly_item_name
300 , p_organization_id => p_organization_id
301 , x_err_text => x_err_text
302 );
303
304 END Assembly_Item;
305
306
307 FUNCTION Bill_Sequence( p_assembly_item_id IN NUMBER,
308 p_alternate_bom_designator IN VARCHAR2,
309 p_organization_id IN NUMBER,
310 x_err_text IN OUT NOCOPY VARCHAR2
311 )
312 RETURN NUMBER
313 IS
314 l_id NUMBER;
315 l_err_text VARCHAR2(2000);
316 BEGIN
317
318 SELECT bill_sequence_id
319 INTO l_id
320 FROM bom_bill_of_materials
321 WHERE assembly_item_id = p_assembly_item_id
322 AND NVL(alternate_bom_designator, 'NONE') =
323 -- NVL(p_alternate_bom_designator, 'NONE')
324 decode(p_alternate_bom_designator,FND_API.G_MISS_CHAR,'NONE',NULL,'NONE',p_alternate_bom_designator) --2783251
325 AND organization_id = p_organization_id;
326
327 RETURN l_id;
328
329 EXCEPTION
330 WHEN OTHERS THEN
331 RETURN NULL;
332 END Bill_Sequence;
333
334 /********************************************************************
335 * Function : Bill_Sequence_Id
336 * Returns : Number
337 * Parameters IN : Assemby_Item_Id
338 * Organization_Id
339 * Alternate_Bom_Code
340 * Parameters OUT: Error Text
341 * Purpose : Function will use the input parameters to find the
342 * bill sequence_id and return a NULL if an error
343 * occured or the bill sequence_id could not be obtained
344 ********************************************************************/
345 FUNCTION Bill_Sequence_Id
346 ( p_assembly_item_id IN NUMBER
347 , p_alternate_bom_code IN VARCHAR2
348 , p_organization_id IN NUMBER
349 , x_err_text IN OUT NOCOPY VARCHAR2
350 ) RETURN NUMBER
351 IS
352 BEGIN
353 RETURN Bill_Sequence
354 ( p_assembly_item_id => p_assembly_item_id
355 , p_alternate_bom_designator => p_alternate_bom_code
356 , p_organization_id => p_organization_id
357 , x_err_text => x_err_text
358 );
359 END Bill_Sequence_Id;
360
361 /**********************************************************************
362 * Function : Structure_Type
363 * Parameters IN : Structure Type Name
364 * Parameters OUT: Error_Text
365 * Returns : Structure_Type_ID
366 * Purpose : This function will get the ID for the structure type name and
367 * return the ID. If the structure type name is invalid then the
368 * ID will be returned as NULL.
369 **********************************************************************/
370 FUNCTION Structure_Type( p_structure_type_name IN VARCHAR2,
371 x_err_text IN OUT NOCOPY VARCHAR2 )
372 RETURN NUMBER
373 IS
374 l_structure_type_id BOM_STRUCTURE_TYPES_B.STRUCTURE_TYPE_ID%TYPE;
375 BEGIN
376 SELECT STRUCTURE_TYPE_ID
377 INTO l_structure_type_id
378 FROM BOM_STRUCTURE_TYPES_B
379 WHERE STRUCTURE_TYPE_NAME = p_structure_type_name
380 AND ( DISABLE_DATE IS NULL OR DISABLE_DATE > SYSDATE );
381
382 RETURN l_structure_type_id;
383 EXCEPTION
384 WHEN NO_DATA_FOUND THEN
385 RETURN NULL;
386
387 WHEN OTHERS THEN
388 x_err_text := SQLERRM;
389 RETURN NULL;
390
391 END Structure_Type;
392
393
394
395 /*********************************************************************
396 * Procedure : BOM_Header_UUI_To_UI
397 * Returns : None
398 * Parameters IN : Assembly Item Record
399 * Assembly Item Unexposed Record
400 * Parameters OUT: Assembly Item unexposed record
401 * Message Token Table
402 * Return Status
403 * Purpose : This procedure will perform all the required
404 * User unique to Unique index conversions for Assembly
405 * item. Any errors will be logged in the Message table
406 * and a return satus of success or failure will be
407 * returned to the calling program.
408 *********************************************************************/
409 PROCEDURE BOM_Header_UUI_To_UI
410 ( p_bom_header_Rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
411 , p_bom_header_unexp_Rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
412 , x_bom_header_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
413 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
414 , x_Return_Status IN OUT NOCOPY VARCHAR2
415 )
416 IS
417 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
418 l_bom_header_unexp_rec Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type;
419 l_return_status VARCHAR2(1);
420 l_err_text VARCHAR2(2000);
421
422 CURSOR c_Check_Alternate( p_alt_designator VARCHAR2,
423 p_organization_id NUMBER ) IS
424 SELECT 1
425 FROM bom_alternate_designators
426 WHERE alternate_designator_code = p_alt_designator
427 AND organization_id = p_organization_id;
428
429 BEGIN
430 x_return_status := FND_API.G_RET_STS_SUCCESS;
431 l_bom_header_unexp_rec := p_bom_header_unexp_rec;
432
433
434 If Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Bom Header UUI-UI Conversion . . '); END IF;
435
436
437 --
438 -- Assembly Item name cannot be NULL or missing.
439 --
440 IF p_bom_header_rec.assembly_item_name IS NULL OR
441 p_bom_header_rec.assembly_item_name = FND_API.G_MISS_CHAR
442 THEN
443 Error_Handler.Add_Error_Token
444 ( p_message_name => 'BOM_AITEM_NAME_KEYCOL_NULL'
445 , p_mesg_token_tbl => l_mesg_token_tbl
446 , x_mesg_token_tbl => l_mesg_token_tbl
447 );
448
449 l_return_status := FND_API.G_RET_STS_ERROR;
450 END IF;
451
452 --
453 -- Assembly item name must be successfully converted to id.
454 --
455
456 l_bom_header_unexp_rec.assembly_item_id :=
457 Assembly_Item ( p_assembly_item_name =>
458 p_bom_header_rec.assembly_item_name
459 , p_organization_id =>
460 l_bom_header_unexp_rec.organization_id
461 , x_err_text => l_err_text
462 );
463
464 IF l_bom_header_unexp_rec.assembly_item_id IS NULL
465 THEN
466 g_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
467 g_token_tbl(1).token_value :=
468 p_bom_header_rec.assembly_item_name;
469 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
470 g_token_tbl(2).token_value :=
471 p_bom_header_rec.organization_code;
472 Error_Handler.Add_Error_Token
473 ( p_Message_Name => 'BOM_ASSEMBLY_ITEM_INVALID'
474 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
475 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
476 , p_Token_Tbl => g_Token_Tbl
477 );
478 l_Return_Status := FND_API.G_RET_STS_ERROR;
479 ELSIF l_err_text IS NOT NULL AND
480 (l_bom_header_unexp_rec.assembly_item_id IS NULL OR
481 l_bom_header_unexp_rec.assembly_item_id = FND_API.G_MISS_NUM)
482 THEN
483 -- This is an unexpected error.
484 Error_Handler.Add_Error_Token
485 ( p_Message_Name => NULL
486 , p_Message_Text => l_err_text || ' in ' ||
487 G_PKG_NAME
488 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
489 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
490 );
491 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
492 END IF;
493
494 If Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('After converting Assembly name ' || to_char(l_bom_header_unexp_rec.assembly_item_id) || ' Status ' || l_return_status); END IF;
495
496 IF p_bom_header_rec.alternate_bom_code IS NOT NULL AND
497 p_bom_header_rec.alternate_bom_code <> FND_API.G_MISS_CHAR
498 THEN
499 l_err_text := FND_API.G_RET_STS_ERROR;
500
501 FOR check_alternate IN
502 c_Check_Alternate
503 ( p_alt_designator => p_bom_header_rec.alternate_bom_code,
504 p_organization_id => l_bom_header_unexp_rec.organization_id )
505 LOOP
506 l_err_text := FND_API.G_RET_STS_SUCCESS;
507 END LOOP;
508
509 IF l_err_text <> FND_API.G_RET_STS_SUCCESS
510 THEN
511 g_token_tbl(1).token_name := 'ALTERNATE_BOM_CODE';
512 g_token_tbl(1).token_value :=
513 p_bom_header_rec.alternate_bom_code;
514 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
515 g_token_tbl(2).token_value := p_bom_header_rec.organization_code;
516 Error_Handler.Add_Error_Token
517 ( p_Message_Name => 'BOM_ALT_DESIGNATOR_INVALID'
518 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
519 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
520 , p_token_tbl => g_token_tbl
521 );
522
523 l_return_status := FND_API.G_RET_STS_ERROR;
524 END IF;
525 END IF;
526
527 x_return_status := l_return_status;
528 x_bom_header_unexp_rec := l_bom_header_unexp_rec;
529 x_mesg_token_tbl := l_mesg_token_tbl;
530
531 END BOM_Header_UUI_To_UI;
532
533
534 /*********************************************************************
535 * Procedure : Bom_Header_VID
536 * Returns : None
537 * Parameters IN : BOM Header exposed Record
538 * BOM Header Unexposed Record
539 * Parameters OUT: BOM Header Unexposed Record
540 * Return Status
541 * Message Token Table
542 * Purpose : This is the access procedure which the private API
543 * will call to perform the BOM Header value to ID
544 * conversions. If any of the conversions fail then the
545 * the procedure will return with an error status and
546 * the messsage token table filled with appropriate
547 * error message.
548 *********************************************************************/
549 PROCEDURE Bom_Header_VID
550 ( x_Return_Status IN OUT NOCOPY VARCHAR2
551 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
552 , p_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
553 , x_bom_head_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
554 , p_bom_header_Rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
555 )
556 IS
557 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
558 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
559 l_err_text VARCHAR2(2000);
560 l_Token_Tbl Error_Handler.Token_Tbl_Type;
561 l_bom_head_unexp_rec Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
562 := p_bom_head_unexp_rec;
563 l_src_bill_sequence_id NUMBER;
564
565 BEGIN
566
567 If Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Header VID conversion . . . ');
568 END IF;
569 IF p_bom_header_rec.common_organization_code IS NOT NULL AND
570 p_bom_header_rec.common_organization_code <>
571 FND_API.G_MISS_CHAR
572 THEN
573 l_bom_head_unexp_rec.common_organization_id :=
574 Organization( p_organization =>
575 p_bom_header_rec.common_organization_code , x_err_text => l_err_text
576 );
577
578 IF l_bom_head_unexp_rec.common_organization_id IS NULL
579 THEN
580 l_token_tbl(1).token_name:= 'ORGANIZATION_CODE';
581 l_token_tbl(1).token_value :=
582 p_bom_header_rec.common_organization_code;
583 Error_Handler.Add_Error_Token
584 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
585 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
586 , p_Message_name =>
587 'BOM_COMMON_ORG_INVALID'
588 , p_token_tbl => l_token_tbl
589 );
590 l_return_status := FND_API.G_RET_STS_ERROR;
591 END IF;
592 ELSIF p_bom_header_rec.common_organization_code IS NULL AND
593 p_bom_header_rec.common_assembly_item_name IS NOT NULL
594 THEN
595 --
596 -- If common organization code is not specified then
597 -- use the current organization;similar to the form.
598 --
599 l_bom_head_unexp_rec.common_organization_id :=
600 l_bom_head_unexp_rec.organization_id;
601 END IF;
602
603 If Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Common Org Converted . . .'); END IF;
604
605 IF p_bom_header_rec.common_assembly_item_name IS NOT NULL AND
606 p_bom_header_rec.common_assembly_item_name <>
607 FND_API.G_MISS_CHAR
608 THEN
609 l_bom_head_unexp_rec.common_assembly_item_id :=
610 Assembly_Item
611 ( p_assembly_item_name =>
612 p_bom_header_rec.common_assembly_item_name
613 , p_organization_id =>
614 l_bom_head_unexp_rec.common_organization_id
615 , x_err_text => l_err_text
616 );
617 IF l_bom_head_unexp_rec.common_assembly_item_id IS NULL
618 THEN
619 l_token_tbl(1).token_name :=
620 'BOM_COMMON_ASSEMBLY_ITEM_NAME';
621 l_token_tbl(2).token_value :=
622 p_bom_header_rec.common_assembly_item_name;
623 Error_Handler.Add_Error_Token
624 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
625 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
626 , p_Message_name =>
627 'BOM_COMMON_ASSY_INVALID'
628 , p_token_tbl => l_token_tbl
629 );
630 l_return_status := FND_API.G_RET_STS_ERROR;
631 ELSIF l_err_text IS NOT NULL AND
632 l_bom_head_unexp_rec.common_assembly_item_id
633 IS NULL
634 THEN
635 Error_Handler.Add_Error_Token
636 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
637 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
638 , p_Message_text =>
639 'Unexpected Error ' || l_err_text || ' in ' ||
640 G_PKG_NAME
641 , p_token_tbl => l_token_tbl
642 );
643 l_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
644 END IF;
645 END IF;
646
647 If Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Converted common assembly name . . .'); end if;
648
649 --
650 -- Convert common org code and common assembly name information
651 -- into common bill sequence_id
652 --
653 IF l_bom_head_unexp_rec.common_organization_id IS NOT NULL AND
654 l_bom_head_unexp_rec.common_assembly_item_id IS NOT NULL AND
655 l_bom_head_unexp_rec.common_organization_id <>
656 FND_API.G_MISS_NUM AND
657 l_bom_head_unexp_rec.common_assembly_item_id <>
658 FND_API.G_MISS_NUM
659 THEN
660 l_src_bill_sequence_id := Bill_Sequence_Id
661 ( p_assembly_item_id =>
662 l_bom_head_unexp_rec.common_assembly_item_id
663 , p_alternate_bom_code => p_bom_header_rec.alternate_bom_code
664 , p_organization_id =>
665 l_bom_head_unexp_rec.common_organization_id
666 , x_err_text => l_err_text
667 );
668
669
670 IF p_bom_header_Rec.enable_attrs_update = 'Y'
671 THEN
672 l_bom_head_unexp_rec.common_bill_sequence_id := l_bom_head_unexp_rec.bill_sequence_id;
673 l_bom_head_unexp_rec.source_bill_sequence_id := l_src_bill_sequence_id;
674 ELSE
675 l_bom_head_unexp_rec.common_bill_sequence_id := l_src_bill_sequence_id;
676 l_bom_head_unexp_rec.source_bill_sequence_id := l_src_bill_sequence_id;
677 END IF;
678
679
680 IF /*l_bom_head_unexp_rec.common_bill_sequence_id*/l_src_bill_sequence_id IS NULL
681 THEN
682 --
683 -- Common bill sequence was not found
684 --
685 l_token_tbl.Delete;
686 l_token_tbl(1).token_name :=
687 'COMMON_ASSEMBLY_ITEM_NAME';
688 l_token_tbl(1).token_value :=
689 p_bom_header_rec.common_assembly_item_name;
690 l_token_tbl(2).token_name :=
691 'COMMON_ORGANIZATION_CODE';
692 l_token_tbl(2).token_value :=
693 p_bom_header_rec.common_organization_code;
694 Error_Handler.Add_Error_Token
695 ( p_message_name =>
696 'BOM_COMMON_BILL_SEQ_NOT_FOUND'
697 , p_token_tbl => l_token_tbl
698 , p_mesg_token_tbl => l_mesg_token_tbl
699 , x_mesg_token_tbl => l_mesg_token_tbl
700 );
701 l_return_status := FND_API.G_RET_STS_ERROR;
702 END IF;
703 END IF;
704 If Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Converted common bill sequence id. . .'); end if;
705
706 IF Bom_Globals.Get_Debug = 'Y' THEN
707 Error_Handler.Write_Debug('Structure Type Name VID conversion . . . ');
708 END IF;
709
710 IF ( p_bom_header_rec.structure_type_name IS NOT NULL AND
711 p_bom_header_rec.structure_type_name <> FND_API.G_MISS_CHAR )
712 THEN
713 l_bom_head_unexp_rec.structure_type_id := Structure_Type
714 ( p_structure_type_name => p_bom_header_rec.structure_type_name
715 , x_err_text => l_err_text
716 );
717
718 IF ( l_bom_head_unexp_rec.structure_type_id IS NULL )
719 THEN
720 Error_Handler.Add_Error_Token
721 ( p_mesg_token_tbl => l_Mesg_Token_Tbl
722 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
723 , p_Message_name => 'BOM_STRUCTURE_TYPE_INVALID'
724 , p_token_tbl => l_token_tbl
725 );
726
727 l_return_status := FND_API.G_RET_STS_ERROR;
728 END IF; -- end if l_bom_head_unexp_rec.structure_type_id IS NULL
729 END IF; -- end if p_bom_header_rec.structure_type_name IS NOT NULL
730
731 If Bom_Globals.Get_Debug = 'Y' THEN
732 Error_Handler.Write_Debug('Converted Structure Type ID . . . ');
733 END IF;
734
735 x_return_status := l_return_status;
736
737 If Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Return status of Header VID: ' || l_return_status ); end if;
738
739 x_bom_head_unexp_rec := l_bom_head_unexp_rec;
740 x_mesg_token_tbl := l_mesg_token_tbl;
741
742 END Bom_Header_VID;
743
744 /*******************************************************************
745 * Procedure : Bom_Revision_UUI_To_UI2
746 * Parameters IN : Bom Revisions exposed Record
747 * Bom Revisions unexposed record
748 * Parameters OUT: Bom revisions unexposed record
749 * Message Token Table
750 * Return Status
751 * Purpose : User Unique to Unique Index conversion will convert
752 * convert the user friendly values for the primary key
753 * of Revisions entity columns.
754 *******************************************************************/
755 PROCEDURE Bom_Revision_UUI_To_UI2
756 ( p_bom_revision_rec IN Bom_Bo_Pub.Bom_Revision_Rec_Type
757 , p_bom_rev_unexp_rec IN Bom_Bo_Pub.Bom_Rev_Unexposed_Rec_Type
758 , x_bom_rev_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Rev_Unexposed_Rec_Type
759 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
760 , x_return_status IN OUT NOCOPY VARCHAR2
761 )
762 IS
763 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
764 l_bom_rev_unexp_rec Bom_Bo_Pub.Bom_Rev_Unexposed_Rec_Type;
765 l_err_text VARCHAR2(2000);
766 BEGIN
767 x_bom_rev_unexp_rec := p_bom_rev_unexp_rec;
768 l_bom_rev_unexp_rec := p_bom_rev_unexp_rec;
769
770 x_return_status := FND_API.G_RET_STS_SUCCESS;
771
772 IF p_bom_revision_rec.assembly_item_name IS NULL OR
773 p_bom_revision_rec.assembly_item_name = FND_API.G_MISS_CHAR
774 THEN
775 Error_Handler.Add_Error_Token
776 ( p_message_name => 'BOM_REV_ASSY_KEYCOL_NULL'
777 , x_mesg_token_tbl => l_mesg_token_tbl
778 );
779 x_return_status := FND_API.G_RET_STS_ERROR;
780 END IF;
781
782 --
783 -- Convert Assembly Item Name
784 --
785
786 g_Token_Tbl(1).Token_Name := 'ASSEMBLY_ITEM_NAME';
787 g_Token_Tbl(1).Token_Value :=
788 p_bom_revision_Rec.assembly_item_name;
789 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
790 g_token_tbl(2).token_value :=
791 p_bom_revision_Rec.organization_code;
792
793 l_bom_rev_unexp_rec.assembly_item_id :=
794 Revised_Item( p_revised_item_num =>
795 p_bom_revision_rec.assembly_item_name
796 , p_organization_id =>
797 l_bom_rev_unexp_rec.organization_id
798 , x_err_text => l_err_text
799 );
800
801 IF l_bom_rev_unexp_rec.assembly_item_id IS NULL
802 THEN
803 Error_Handler.Add_Error_Token
804 ( p_Message_Name => 'BOM_ASSEMBLY_ITEM_INVALID'
805 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
806 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
807 , p_Token_Tbl => g_Token_Tbl
808 );
809 x_Return_Status := FND_API.G_RET_STS_ERROR;
810 END IF;
811
812 x_bom_rev_unexp_rec := l_bom_rev_unexp_rec;
813
814 END Bom_Revision_UUI_To_UI2;
815
816
817 /*********************************************************************
818 * Function : Locator_Id
819 * Returns : NUMBER
820 * Purpose : Convert Location Name to locator_id. If the
821 * conversion fails the function will return a NULL else
822 * the locator_id. If an unexpected error is encountered
823 * then the function will return an unexpected error.
824 **********************************************************************/
825 FUNCTION locator_id (p_location_name IN VARCHAR2,
826 p_organization_id IN NUMBER
827 )
828 RETURN NUMBER
829 IS
830 supply_locator_id NUMBER;
831 ret_code NUMBER;
832 l_err_text VARCHAR2(240);
833 BEGIN
834 /* Commented for Bug 2804151
835
836 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
837 org_id => p_organization_id,
838 flex_code => 'MTLL',
839 flex_name => p_location_name,
840 flex_id => supply_locator_id,
841 set_id => -1,
842 err_text => l_err_text);
843
844 IF (ret_code <> 0) THEN
845 RETURN NULL;
846 ELSE
847 RETURN supply_locator_id;
848 END IF;
849 */
850 Begin
851
852 select inventory_location_id
853 into supply_locator_id
854 from mtl_item_locations_kfv
855 where concatenated_segments = p_location_name and
856 organization_id = p_organization_id;
857
858 RETURN supply_locator_id;
859
860 Exception
861 WHEN NO_DATA_FOUND Then
862 RETURN NULL;
863 End;
864
865
866 END locator_id;
867
868 /**************************************************************************
869 * Function : Old_Component_Sequence
870 * Returns : NUMBER
871 * Putpose : Using the input parameters the function will retrieve the
872 * old component sequence id of the component and return.
873 * If the function fails to find a record then it will return
874 * a NULL value. In case of an unexpected error the function
875 * will return a missing value.
876 ****************************************************************************/
877 FUNCTION Old_Component_Sequence( p_component_item_id IN NUMBER
878 , p_old_effective_date IN DATE
879 , p_old_op_seq_num IN NUMBER
880 , p_bill_sequence_id IN NUMBER
881 )
882 RETURN NUMBER
883 IS
884 l_id NUMBER;
885 BEGIN
886
887 IF Bom_Globals.Get_Debug = 'Y' THEN
888 Error_Handler.Write_Debug('Old Operation: ' || to_char(p_old_op_seq_num));
889 Error_Handler.Write_Debug('Bill Sequence: ' || to_char(p_bill_sequence_id));
890 Error_Handler.Write_Debug('Old Effective: ' || to_char(p_old_effective_date));
891 END IF;
892
893 SELECT component_sequence_id
894 INTO l_id
895 FROM bom_inventory_components
896 WHERE component_item_id = p_component_item_id
897 AND bill_sequence_id = p_bill_sequence_id
898 AND effectivity_date = p_old_effective_date
899 AND operation_seq_num = p_old_op_seq_num;
900
901 RETURN l_id;
902
903 EXCEPTION
904
905 WHEN NO_DATA_FOUND THEN
906 RETURN NULL;
907
908 WHEN OTHERS THEN
909 RETURN FND_API.G_MISS_NUM;
910
911 END Old_Component_Sequence;
912
913
914 /*****************************************************************
915 *
916 * Function Revised Item Sequence
917 *
918 * Following Revsied_Item_Sequence is moved to Engineering Space
919 * by MK on 12/03/00
920 *
921 FUNCTION Revised_Item_Sequence
922 ( p_revised_item_id IN NUMBER
923 , p_change_notice IN VARCHAR2
924 , p_organization_id IN NUMBER
925 , p_new_item_revision IN VARCHAR2
926 , p_new_routing_revision IN VARCHAR2
927 , p_effective_date IN DATE
928 , p_from_end_item_number IN VARCHAR2 := NULL
929 )
930 RETURN NUMBER
931 IS
932 l_id NUMBER;
933 BEGIN
934
935 -- Modified by MK on 11/02/00
936 -- Modified by MK on 11/20/00
937 -- Bug #1454568, User may set Miss Char to Primary Keys
938 -- It will be regarded as Null
939 SELECT revised_item_sequence_id
940 INTO l_id
941 FROM Eng_revised_items
942 WHERE NVL(from_end_item_unit_number,FND_API.G_MISS_CHAR )
943 = NVL(p_from_end_item_number, FND_API.G_MISS_CHAR)
944 AND NVL(new_item_revision, FND_API.G_MISS_CHAR) =
945 NVL(p_new_item_revision, FND_API.G_MISS_CHAR)
946 AND NVL(new_routing_revision, FND_API.G_MISS_CHAR) =
947 NVL(p_new_routing_revision, FND_API.G_MISS_CHAR)
948 AND TRUNC(scheduled_date) = TRUNC(p_effective_date )
949 AND change_notice = p_change_notice
950 AND organization_id = p_organization_id
951 AND revised_item_id = p_revised_item_id ;
952
953
954 RETURN l_id;
955
956 EXCEPTION
957
958 WHEN NO_DATA_FOUND THEN
959
960 RETURN NULL;
961
962 WHEN OTHERS THEN
963 RETURN FND_API.G_MISS_NUM;
964
965 END Revised_Item_Sequence;
966 ******************************************************************/
967
968
969 /*************************************************************
970 * Function : BillAndRevItemSeq
971 * Parameters IN : Revised Item Unique Key information
972 * Parameters OUT: Bill Sequence ID
973 * Returns : Revised Item Sequence
974 * Purpose : Will use the revised item information to find the bill
975 * sequence and the revised item sequence.
976 * History : Added p_new_routing_revsion and
977 * p_from_end_item_number in argument
978 *
979 * Following Revsied_Item_Sequence is moved to Engineering Space
980 * by MK on 12/03/00
981
982 FUNCTION BillAndRevItemSeq( p_revised_item_id IN NUMBER
983 , p_item_revision IN VARCHAR2
984 , p_effective_date IN DATE
985 , p_change_notice IN VARCHAR2
986 , p_organization_id IN NUMBER
987 , p_new_routing_revision IN VARCHAR2
988 , p_from_end_item_number IN VARCHAR2 := NULL
989 , x_Bill_Sequence_Id IN OUT NOCOPY NUMBER
990 )
991 RETURN NUMBER
992 IS
993 l_Bill_Seq NUMBER;
994 l_Rev_Item_Seq NUMBER;
995 BEGIN
996
997 -- Modified by MK on 11/02/00
998 -- Modified by MK on 11/20/00
999 -- Bug #1454568, User may set Miss Char to Primary Keys
1000 -- It will be regarded as Null
1001 SELECT bill_sequence_id, revised_item_Sequence_id
1002 INTO l_Bill_Seq, l_Rev_Item_Seq
1003 FROM eng_revised_items
1004 WHERE NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR )
1005 = NVL(p_from_end_item_number,FND_API.G_MISS_CHAR)
1006 AND NVL(new_routing_revision,FND_API.G_MISS_CHAR)
1007 = NVL(p_new_routing_revision,FND_API.G_MISS_CHAR)
1008 AND NVL(new_item_revision, FND_API.G_MISS_CHAR)
1009 = NVL(p_item_revision , FND_API.G_MISS_CHAR)
1010 AND TRUNC(scheduled_date) = TRUNC(p_effective_date)
1011 AND change_notice = p_change_notice
1012 AND organization_id = p_organization_id
1013 AND revised_item_id = p_revised_item_id ;
1014
1015
1016 x_Bill_Sequence_Id := l_Bill_Seq;
1017 RETURN l_Rev_Item_Seq;
1018
1019 EXCEPTION
1020 WHEN OTHERS THEN
1021 x_Bill_Sequence_Id := NULL;
1022 RETURN NULL;
1023 END BillAndRevItemSeq;
1024 ******************************************************************/
1025
1026
1027 -- Substitute_Component
1028
1029 FUNCTION Substitute_Component(p_substitute_component IN VARCHAR2,
1030 p_organization_id IN NUMBER,
1031 x_err_text IN OUT NOCOPY VARCHAR2 )
1032 RETURN NUMBER
1033 IS
1034 l_id NUMBER;
1035 ret_code NUMBER;
1036 BEGIN
1037
1038 /* ret_code := INVPUOPI.mtl_pr_parse_flex_name(
1039 org_id => p_organization_id,
1040 flex_code => 'MSTK',
1041 flex_name => p_substitute_component,
1042 flex_id => l_id,
1043 set_id => -1,
1044 err_text => x_err_text); */
1045 ret_code := parse_item_name(
1046 org_id => p_organization_id,
1047 item_name => p_substitute_component,
1048 id => l_id,
1049 err_text => x_err_text);
1050 IF (ret_code <> 0) THEN
1051 RETURN NULL;
1052 ELSE
1053 RETURN l_id;
1054 END IF;
1055
1056 END Substitute_Component;
1057
1058 -- New_Sub_Comp
1059
1060 FUNCTION New_Sub_Comp(p_new_sub_comp IN VARCHAR2,
1061 p_organization_id IN NUMBER,
1062 x_err_text IN OUT NOCOPY VARCHAR2 )
1063 RETURN NUMBER
1064 IS
1065 l_id NUMBER;
1066 ret_code NUMBER;
1067 l_err_text VARCHAR2(2000);
1068 BEGIN
1069 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
1070 org_id => p_organization_id,
1071 flex_code => 'MSTK',
1072 flex_name => p_new_sub_comp,
1073 flex_id => l_id,
1074 set_id => -1,
1075 err_text => x_err_text);
1076
1077 IF (ret_code <> 0) THEN
1078 RETURN NULL;
1079 ELSE
1080 RETURN l_id;
1081 END IF;
1082
1083 END New_Sub_Comp;
1084
1085 /*****************************************************************
1086 * Function : Component_Sequence
1087 * Parameters IN : Revised Component unique index information
1088 * Parameters OUT: Error Text
1089 * Returns : Component_Sequence_Id
1090 * Purpose : Function will query the component sequence id using
1091 * alternate unique key information. If unsuccessfull
1092 * function will return a NULL.
1093 ********************************************************************/
1094 FUNCTION Component_Sequence(p_component_item_id IN NUMBER,
1095 p_operation_sequence_num IN VARCHAR2,
1096 p_effectivity_date IN DATE,
1097 p_bill_sequence_id IN NUMBER,
1098 p_from_unit_number IN VARCHAR2 := NULL,
1099 x_err_text IN OUT NOCOPY VARCHAR2 )
1100 RETURN NUMBER
1101 IS
1102 l_id NUMBER;
1103 ret_code NUMBER;
1104 l_err_text VARCHAR2(2000);
1105 BEGIN
1106
1107 select component_sequence_id
1108 into l_id
1109 from bom_inventory_components
1110 where bill_sequence_id = p_bill_sequence_id
1111 and component_item_id = p_component_item_id
1112 and operation_seq_num = p_operation_sequence_num
1113 and effectivity_date = p_effectivity_date
1114 and
1115 (p_from_unit_number IS NULL
1116 or
1117 p_from_unit_number = FND_API.G_MISS_CHAR
1118 or
1119 from_end_item_unit_number = p_from_unit_number);
1120
1121 RETURN l_id;
1122
1123 EXCEPTION
1124
1125 WHEN OTHERS THEN
1126 RETURN NULL;
1127
1128 END Component_Sequence;
1129
1130 /*****************************************************************
1131 * Function : Vendor_Id
1132 * Parameters IN : Vendor Name
1133 * Parameters OUT:
1134 * Returns : Vendor_Id
1135 * Purpose : Function will query the vendor_id for the vendor
1136 * name passed and returns the same
1137 ********************************************************************/
1138 FUNCTION Vendor_Id (p_Vendor_Name IN VARCHAR2)
1139 RETURN NUMBER
1140 IS
1141 l_id NUMBER;
1142 BEGIN
1143
1144 select vendor_id
1145 into l_id
1146 from po_vendors
1147 where vendor_name = p_Vendor_Name
1148 and enabled_flag = 'Y'
1149 and sysdate between nvl(start_date_active,sysdate-1) AND nvl(end_date_active,sysdate+1);
1150
1151 RETURN l_id;
1152 EXCEPTION
1153 WHEN OTHERS THEN
1154 RETURN NULL;
1155 END Vendor_Id;
1156
1157 /******************************************************************
1158 * Procedure : Rev_Component_VID
1159 * Parameters IN : Revised Component exposed column record
1160 * Revised component unexposed column record
1161 * Parameters OUT: Revised component unexposed column record after the
1162 * conversion
1163 * Purpose : The procedure will convert the columns that need
1164 * value to id conversion by calling there respective
1165 * procedures.
1166 ********************************************************************/
1167 PROCEDURE Rev_Component_VID
1168 ( x_Return_Status IN OUT NOCOPY Varchar2
1169 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1170 , p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1171 , x_Rev_Comp_Unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1172 , p_Rev_Component_Rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
1173 ) IS
1174 l_return_value NUMBER;
1175 l_Return_Status VARCHAR2(1);
1176 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1177 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1178 l_Rev_Comp_Unexp_Rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type;
1179
1180 BEGIN
1181 l_Return_Status := FND_API.G_RET_STS_SUCCESS;
1182 l_Rev_Comp_Unexp_Rec := p_Rev_Comp_Unexp_Rec;
1183
1184 --
1185 -- Convert Location_Name to Location_Id
1186 --
1187 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Executing Location Val-ID conversion . . .'); END IF;
1188
1189 IF p_rev_component_rec.location_name IS NOT NULL AND
1190 p_rev_component_rec.location_name <> FND_API.G_MISS_CHAR
1191 THEN
1192 l_Return_Value := Locator_Id
1193 ( p_Location_name =>
1194 p_rev_component_rec.location_name
1195 , p_organization_id =>
1196 l_rev_comp_unexp_rec.organization_id
1197 );
1198 IF l_Return_Value IS NULL THEN
1199 l_token_tbl(1).token_name := 'LOCATION_NAME';
1200 l_token_tbl(1).token_value :=
1201 p_rev_component_rec.location_name;
1202 l_token_tbl(2).token_name := 'REVISED_COMPONENT_NAME';
1203 l_token_tbl(2).token_value :=
1204 p_rev_component_rec.component_item_name;
1205
1206 Error_Handler.Add_Error_Token
1207 ( p_Message_Name => 'BOM_LOCATION_NAME_INVALID'
1208 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1209 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1210 , p_Token_Tbl => l_Token_Tbl
1211 );
1212 l_Return_Status := FND_API.G_RET_STS_ERROR;
1213 ELSIF l_Return_Value = FND_API.G_MISS_NUM
1214 THEN
1215 Error_Handler.Add_Error_Token
1216 ( p_Message_Name => NULL
1217 , p_Message_Text =>
1218 'Unexpected error while converting location name'
1219 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1220 , x_Mesg_Token_Tbl => l_MEsg_Token_Tbl
1221 );
1222 l_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1223 ELSE
1224 l_Rev_Comp_Unexp_Rec.supply_locator_id :=
1225 l_Return_Value;
1226 END IF;
1227 END IF;
1228
1229 --
1230 -- Using old_component information, get the old_component_sequence_id
1231 --
1232 IF Bom_Globals.Get_Debug = 'Y' THEN
1233 Error_Handler.Write_Debug('Executing old_Comp_seqid Val-ID conversion . . .');
1234 Error_Handler.Write_Debug('Bill Sequence: ' || to_char(l_rev_comp_unexp_rec.bill_sequence_id));
1235 END IF;
1236
1237
1238 IF p_Rev_Component_Rec.old_effectivity_date IS NOT NULL AND
1239 p_Rev_Component_Rec.old_effectivity_date <> FND_API.G_MISS_DATE AND
1240 p_Rev_component_rec.old_operation_sequence_number IS NOT NULL AND
1241 p_Rev_component_rec.old_operation_sequence_number <>
1242 FND_API.G_MISS_NUM
1243 THEN
1244 l_Return_Value :=
1245 Old_Component_Sequence
1246 ( p_component_item_id =>
1247 l_rev_comp_unexp_rec.component_item_id
1248 , p_old_effective_date =>
1249 p_rev_component_rec.old_effectivity_date
1250 , p_old_op_seq_num =>
1251 p_rev_component_rec.old_operation_sequence_number
1252 , p_bill_sequence_id =>
1253 l_rev_comp_unexp_rec.bill_sequence_id
1254 );
1255
1256 IF l_Return_Value IS NULL
1257 THEN
1258 l_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
1259 l_token_tbl(1).token_value :=
1260 p_rev_component_rec.component_item_name;
1261 l_token_tbl(2).token_name := 'OLD_EFFECTIVITY_DATE';
1262 l_token_tbl(2).token_value :=
1263 p_rev_component_rec.old_effectivity_date;
1264 l_token_tbl(3).token_name :=
1265 'OLD_OPERATION_SEQUENCE_NUMBER';
1266 l_token_tbl(3).token_value :=
1267 p_rev_component_rec.old_operation_sequence_number;
1268
1269 Error_Handler.Add_Error_Token
1270 ( p_Message_Name => 'BOM_OLD_COMP_VID_INVALID'
1271 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1272 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1273 , p_Token_Tbl => l_Token_Tbl
1274 );
1275 l_Return_Status := FND_API.G_RET_STS_ERROR;
1276 ELSIF l_Return_Value = FND_API.G_MISS_NUM
1277 THEN
1278 Error_Handler.Add_Error_Token
1279 ( p_Message_Name => NULL
1280 , p_Message_Text =>
1281 'Unexpected Error while converting old_comp_seq_id'
1282 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1283 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1284 , p_Token_Tbl => l_Token_Tbl
1285 );
1286 l_Return_Status := FND_API.G_RET_STS_ERROR;
1287 ELSE
1288 l_rev_comp_unexp_rec.old_component_sequence_id :=
1289 l_Return_Value;
1290
1291 -- dbms_output.put_line('Old Sequence: ' || to_char(l_return_value));
1292
1293 END IF;
1294 END IF;
1295
1296 --
1297 -- Get the Revised Item Sequence_Id using the revised item
1298 -- information when the calling object is ECO
1299
1300 -- dbms_output.put_line('Executing revised item seq Val-ID conversion . . .');
1301 -- dbms_output.put_line('Revised item: ' ||
1302 -- to_char(l_rev_comp_unexp_rec.revised_item_id));
1303 -- dbms_output.put_line('Item Revision: ' ||
1304 -- p_rev_component_rec.new_revised_item_revision);
1305 -- dbms_output.put_line('Change Notice: ' || p_rev_component_rec.eco_name);
1306
1307 IF Bom_Globals.Get_Bo_Identifier <> 'BOM'
1308 THEN
1309
1310 NULL ;
1311
1312 /* Following getting revised_item_sequence_id moved to Engineering
1313 -- space to resolve dependency.
1314 -- by MK on 12/03/00
1315
1316 -- Modified by MK 11/02/00
1317 l_Return_Value := Revised_Item_Sequence
1318 ( p_revised_item_id =>
1319 l_rev_comp_unexp_rec.revised_item_id
1320 , p_change_notice => p_rev_component_rec.eco_name
1321 , p_organization_id =>
1322 l_rev_comp_unexp_rec.organization_id
1323 , p_new_item_revision =>
1324 p_rev_component_rec.new_revised_item_revision
1325 , p_new_routing_revision =>
1326 p_rev_component_rec.new_routing_revision
1327 , p_effective_date =>
1328 p_rev_component_rec.start_effective_date
1329 , p_from_end_item_number =>
1330 p_rev_component_rec.from_end_item_unit_number
1331 );
1332
1333
1334 IF l_Return_Value IS NULL
1335 THEN
1336 Error_Handler.Add_Error_Token
1337 ( p_Message_Name => 'ENG_REVISED_ITEM_INVALID'
1338 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1339 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1340 , p_Token_Tbl => l_Token_Tbl
1341 );
1342 l_Return_Status := FND_API.G_RET_STS_ERROR;
1343 ELSIF l_Return_Value = FND_API.G_MISS_NUM
1344 THEN
1345 Error_Handler.Add_Error_Token
1346 ( p_Message_Name => NULL
1347 , p_Message_Text =>
1348 'Unexpected Error while converting revised_item_sequence_id'
1349 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1350 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1351 , p_Token_Tbl => l_Token_Tbl
1352 );
1353 l_Return_Status := FND_API.G_RET_STS_ERROR;
1354 ELSE
1355 l_rev_comp_unexp_rec.revised_item_sequence_id :=
1356 l_Return_Value;
1357 -- dbms_output.put_line('Revised item sequence: ' || to_char(l_Return_Value));
1358
1359 END IF;
1360
1361 */ -- Comment out by MK on 12/03/00
1362
1363 END IF;
1364
1365 --
1366 -- Convert Suggested_Vendor_Name to Vendor_Id
1367 --
1368
1369 IF p_rev_component_rec.Suggested_Vendor_Name IS NOT NULL AND
1370 p_rev_component_rec.Suggested_Vendor_Name <> FND_API.G_MISS_CHAR
1371 THEN
1372 l_Return_Value := Vendor_Id
1373 ( p_vendor_name =>
1374 p_rev_component_rec.Suggested_Vendor_Name
1375 );
1376 IF l_Return_Value IS NULL THEN
1377 l_token_tbl(1).token_name := 'VENDOR_NAME';
1378 l_token_tbl(1).token_value :=
1379 p_rev_component_rec.Suggested_Vendor_Name;
1380
1381 Error_Handler.Add_Error_Token
1382 ( p_Message_Name => 'BOM_NEW_VENDOR_NAME'
1383 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1384 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1385 , p_Token_Tbl => l_Token_Tbl
1386 );
1387 END IF;
1388 l_Rev_Comp_Unexp_Rec.vendor_id := l_Return_Value;
1389 END IF;
1390
1391 x_return_status := l_Return_status;
1392
1393 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('returning from comp vid with ' || l_Return_status); END IF;
1394
1395 x_mesg_token_tbl := l_Mesg_Token_Tbl;
1396 x_rev_comp_unexp_rec := l_rev_comp_unexp_rec;
1397
1398 END Rev_Component_VID;
1399
1400 /*****************************************************************
1401 * Procedure : Rev_Component_UUI_To_UI
1402 * Parameters IN : Revised component exposed columns record
1403 * Revised component unexposed columns record
1404 * Parameters OUT: Revised component unexposed columns record after the
1405 * conversion
1406 * Mesg_Token_Tbl
1407 * Return_Status
1408 * Purpose : This procedure will perform value to id conversion
1409 * for all the revised component columns that form the
1410 * unique key for this entity.
1411 *********************************************************************/
1412 PROCEDURE Rev_Component_UUI_To_UI
1413 ( p_rev_component_Rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
1414 , p_rev_comp_unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1415 , x_rev_comp_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1416 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1417 , x_Return_Status IN OUT NOCOPY VARCHAR2
1418 )
1419 IS
1420 l_err_text VARCHAR2(2000);
1421 l_rev_comp_unexp_rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type;
1422 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1423 l_Return_status VARCHAR2(1);
1424 BEGIN
1425 l_rev_comp_unexp_rec := p_rev_comp_unexp_Rec;
1426 l_Return_status := FND_API.G_RET_STS_SUCCESS;
1427
1428 /******************************************************
1429 --
1430 -- Verify that the unique key columns are not empty
1431 --
1432 ********************************************************/
1433 IF p_rev_component_rec.component_item_name IS NULL OR
1434 p_rev_component_rec.component_item_name = FND_API.G_MISS_CHAR
1435 THEN
1436 Error_Handler.Add_Error_Token
1437 ( p_Message_Name => 'BOM_RCOMP_NAME_KEYCOL_NULL'
1438 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1439 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1440 );
1441 l_Return_Status := FND_API.G_RET_STS_ERROR;
1442 END IF;
1443
1444 IF p_rev_component_rec.revised_item_name IS NULL OR
1445 p_rev_component_rec.revised_item_name = FND_API.G_MISS_CHAR
1446 THEN
1447 Error_Handler.Add_Error_Token
1448 ( p_Message_Name => 'BOM_RCOMP_RITEM_KEYCOL_NULL'
1449 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1450 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1451 );
1452 l_Return_Status := FND_API.G_RET_STS_ERROR;
1453 END IF;
1454
1455 IF (p_rev_component_rec.transaction_type <> BOM_globals.G_OPR_CREATE) AND
1456 (p_rev_component_rec.operation_sequence_number IS NULL OR
1457 p_rev_component_rec.operation_sequence_number = FND_API.G_MISS_NUM)
1458 THEN
1459 Error_Handler.Add_Error_Token
1460 ( p_Message_Name => 'BOM_RCOMP_OPSEQ_KEYCOL_NULL'
1461 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1462 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1463 );
1464 l_Return_Status := FND_API.G_RET_STS_ERROR;
1465 END IF;
1466
1467 IF p_rev_component_rec.start_effective_date IS NULL OR
1468 p_rev_component_rec.start_effective_date = FND_API.G_MISS_DATE
1469 THEN
1470 Error_Handler.Add_Error_Token
1471 ( p_Message_Name => 'BOM_RCOMP_DATE_KEYCOL_NULL'
1472 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1473 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1474 );
1475 l_Return_Status := FND_API.G_RET_STS_ERROR;
1476
1477 END IF;
1478
1479 --
1480 -- If key columns are NULL, then return.
1481 --
1482 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1483 THEN
1484 x_Return_Status := FND_API.G_RET_STS_ERROR;
1485 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1486 RETURN;
1487 END IF;
1488
1489 /***********************************************************
1490 --
1491 -- Convert Component Item Name to Component Item ID
1492 --
1493 ************************************************************/
1494 l_rev_comp_unexp_rec.component_item_id :=
1495 Component_Item( p_organization_id =>
1496 l_rev_comp_unexp_rec.organization_id
1497 , p_component_item_num =>
1498 p_rev_component_rec.component_item_name
1499 , x_err_text => l_err_text
1500 );
1501
1502 IF l_rev_comp_unexp_rec.component_item_id IS NULL
1503 THEN
1504 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
1505 g_Token_Tbl(1).Token_Value :=
1506 p_rev_component_rec.component_item_name;
1507 g_Token_Tbl(2).Token_Name := 'ORGANIZATION_CODE';
1508 g_Token_Tbl(2).Token_Value :=
1509 p_rev_component_rec.organization_code;
1510
1511 Error_Handler.Add_Error_Token
1512 ( p_Message_Name => 'BOM_REVISED_COMP_INVALID'
1513 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1514 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1515 , p_Token_Tbl => g_Token_Tbl
1516 );
1517 l_Return_Status := FND_API.G_RET_STS_ERROR;
1518
1519 END IF;
1520
1521 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1522 x_rev_comp_unexp_rec := l_rev_comp_unexp_rec;
1523 x_Return_Status := l_Return_Status;
1524
1525 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Returning from Rev_Component_UUI_To_UI with ' || l_Return_Status); END IF;
1526
1527 END Rev_Component_UUI_To_UI;
1528
1529 /*****************************************************************
1530 * Procedure : Rev_Component_UUI_to_UI2
1531 * Purpose : This procedure is similar to the UUI-UI conversion
1532 * procedure, except that the calling program will be
1533 * able to determine the scope of the Error.
1534 * This procedure will convert all those values which
1535 * will cause the siblings to error out.
1536 ********************************************************************/
1537 PROCEDURE Rev_Component_UUI_to_UI2
1538 ( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
1539 , p_rev_comp_unexp_rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1540 , x_rev_comp_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1541 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1542 , x_other_message IN OUT NOCOPY VARCHAR2
1543 , x_other_token_tbl IN OUT NOCOPY Error_Handler.Token_Tbl_Type
1544 , x_Return_Status IN OUT NOCOPY VARCHAR2
1545 )
1546 IS
1547 l_return_status VARCHAR2(1);
1548 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
1549 l_rev_comp_unexp_rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1550 := p_rev_comp_unexp_rec;
1551 l_err_text VARCHAR2(2000);
1552 BEGIN
1553
1554 --
1555 -- IF revised item key columns is NULL, then set the other
1556 -- message and return with an error.
1557 --
1558 IF (p_rev_component_rec.revised_item_name IS NULL OR
1559 p_rev_component_rec.revised_item_name = FND_API.G_MISS_CHAR)
1560 THEN
1561 Error_Handler.Add_Error_Token
1562 ( p_Message_Name => 'BOM_RCOMP_RITEM_KEYCOL_NULL'
1563 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1564 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1565 );
1566 l_Return_Status := FND_API.G_RET_STS_ERROR;
1567 END IF;
1568
1569 IF p_rev_component_rec.start_effective_date IS NULL OR
1570 p_rev_component_rec.start_effective_date = FND_API.G_MISS_DATE
1571 THEN
1572 Error_Handler.Add_Error_Token
1573 ( p_Message_Name => 'BOM_RCOMP_DATE_KEYCOL_NULL'
1574 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1575 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1576 );
1577 l_Return_Status := FND_API.G_RET_STS_ERROR;
1578
1579 END IF;
1580
1581 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1582 THEN
1583 x_return_status := l_return_status;
1584 x_other_message := 'BOM_REV_ITEM_KEY_NULL';
1585 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1586 x_rev_comp_unexp_rec := l_rev_comp_unexp_rec;
1587 END IF;
1588
1589 /******************************************************
1590 --
1591 -- Convert Revised Item Name to Revised Item ID
1592 --
1593 ********************************************************/
1594 l_return_status := FND_API.G_RET_STS_SUCCESS;
1595 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
1596 g_Token_Tbl(1).Token_Value := p_rev_component_rec.revised_item_name;
1597 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
1598 g_token_tbl(2).token_value := p_rev_component_rec.organization_code;
1599
1600 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Converting assembly item ' || p_rev_component_rec.revised_item_name); END IF;
1601
1602 l_rev_comp_unexp_rec.revised_item_id :=
1603 Revised_Item( p_revised_item_num =>
1604 p_rev_component_rec.revised_item_name
1605 , p_organization_id =>
1606 l_rev_comp_unexp_rec.organization_id
1607 , x_err_text => l_err_text
1608 );
1609
1610
1611 IF l_rev_comp_unexp_rec.revised_item_id IS NULL OR
1612 l_rev_comp_unexp_rec.revised_item_id = FND_API.G_MISS_NUM
1613 THEN
1614 Error_Handler.Add_Error_Token
1615 ( p_Message_Name => 'ENG_REVISED_ITEM_INVALID'
1616 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1617 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1618 , p_Token_Tbl => g_Token_Tbl
1619 );
1620 l_Return_Status := FND_API.G_RET_STS_ERROR;
1621
1622 /* Added this code segment (including RETURN) so there
1623 is no error collection. UUI conversion code must exit
1624 as soon as a conversion error occurs.
1625 Fix added to all conversion error code from this point
1626 on in this package
1627 -- Code added by AS on 03/22/99 to fix bug 853138
1628 */
1629
1630 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1631 x_rev_comp_unexp_rec := l_rev_comp_unexp_rec;
1632 x_Return_Status := l_Return_Status;
1633 x_other_message := 'BOM_CMP_UUI_SEV_ERROR';
1634 x_other_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
1635 x_other_token_tbl(1).token_value :=
1636 p_rev_component_rec.component_item_name;
1637
1638 RETURN;
1639 END IF;
1640
1641 /* Set the system information record values for assembly_item_id
1642 and org_id. These values will be used for validating serial effective
1643 assemblies */
1644
1645 Bom_Globals.Set_Org_Id (l_rev_comp_unexp_rec.organization_id);
1646 Bom_Globals.Set_Assembly_Item_Id (l_rev_comp_unexp_rec.revised_item_id);
1647
1648 /****************************************************************
1649 --
1650 -- Using the revised item key information, get the bill_sequence_id
1651 -- and revised item sequence id
1652 --
1653 ****************************************************************/
1654 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Processing UUI_UI2 for components and retrieving rev item seq id . . . '); END IF;
1655
1656 IF Bom_Globals.Get_Bo_Identifier <> 'BOM'
1657 THEN
1658 NULL ;
1659
1660 /* Following getting revised_item_sequence_id moved to Engineering
1661 -- space to resolve dependency.
1662 -- by MK on 12/03/00
1663
1664 l_rev_comp_unexp_rec.revised_item_sequence_id :=
1665 -- Modifed by MK 11/02/00
1666 BillAndRevItemSeq( p_revised_item_id =>
1667 l_rev_comp_unexp_rec.revised_item_id
1668 , p_item_revision =>
1669 p_rev_component_rec.new_revised_item_revision
1670 , p_effective_date =>
1671 p_rev_component_rec.start_effective_date
1672 , p_change_notice =>
1673 p_rev_component_rec.eco_name
1674 , p_organization_id =>
1675 l_rev_comp_unexp_rec.organization_id
1676 , p_new_routing_revision =>
1677 p_rev_component_rec.new_routing_revision
1678 , p_from_end_item_number =>
1679 p_rev_component_rec.from_end_item_unit_number
1680 , x_Bill_Sequence_Id =>
1681 l_rev_comp_unexp_rec.bill_sequence_id
1682 );
1683
1684
1685
1686 IF l_rev_comp_unexp_rec.revised_item_Sequence_id IS NULL
1687 THEN
1688 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
1689 g_Token_Tbl(1).Token_Value :=
1690 p_rev_component_rec.component_item_name;
1691 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
1692 g_Token_Tbl(2).Token_Value :=
1693 p_rev_component_rec.revised_item_name;
1694 g_token_tbl(3).token_name := 'ECO_NAME';
1695 g_token_tbl(3).token_value :=
1696 p_rev_component_rec.eco_name;
1697
1698 Error_Handler.Add_Error_Token
1699 ( p_Message_Name => 'BOM_REV_SEQUENCE_NOT_FOUND'
1700 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1701 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1702 , p_Token_Tbl => g_Token_Tbl
1703 );
1704 l_Return_Status := FND_API.G_RET_STS_ERROR;
1705 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1706 x_rev_comp_unexp_rec := l_rev_comp_unexp_rec;
1707 x_Return_Status := l_Return_Status;
1708 x_other_message := 'BOM_CMP_UUI_SEV_ERROR';
1709 x_other_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
1710 x_other_token_tbl(1).token_value :=
1711 p_rev_component_rec.component_item_name;
1712
1713 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('And this call returned with ' || l_Return_Status); END IF;
1714
1715 RETURN;
1716
1717 END IF;
1718 */ -- Comment out by MK on 12/03/00
1719
1720 ELSE
1721 --
1722 -- If the calling BO is BOM then get the bill sequence id
1723 --
1724 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Getting bill_seq for assembly item ' || l_rev_comp_unexp_rec.revised_item_id); END IF;
1725
1726 l_rev_comp_unexp_rec.bill_sequence_id :=
1727 bill_sequence_id(p_assembly_item_id =>
1728 l_rev_comp_unexp_rec.revised_item_id,
1729 p_organization_id =>
1730 l_rev_comp_unexp_rec.organization_id,
1731 p_alternate_bom_code =>
1732 p_rev_component_rec.alternate_bom_code,
1733 x_err_text => l_err_text
1734 );
1735
1736 IF l_rev_comp_unexp_rec.bill_Sequence_id IS NULL
1737 THEN
1738 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
1739 g_Token_Tbl(1).Token_Value :=
1740 p_rev_component_rec.component_item_name;
1741 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
1742 g_Token_Tbl(2).Token_Value :=
1743 p_rev_component_rec.revised_item_name;
1744
1745 Error_Handler.Add_Error_Token
1746 ( p_Message_Name => 'BOM_BOM_SEQUENCE_NOT_FOUND'
1747 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1748 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1749 , p_Token_Tbl => g_Token_Tbl
1750 );
1751 l_Return_Status := FND_API.G_RET_STS_ERROR;
1752 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1753 x_rev_comp_unexp_rec := l_rev_comp_unexp_rec;
1754 x_Return_Status := l_Return_Status;
1755 x_other_message := 'BOM_CMP_UUI_SEV_ERROR';
1756 x_other_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
1757 x_other_token_tbl(1).token_value :=
1758 p_rev_component_rec.component_item_name;
1759 RETURN;
1760 END IF;
1761
1762 END IF;
1763
1764 /* Get the BOM Implementation date */
1765
1766 IF Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_BOM_BO
1767 THEN
1768 l_rev_comp_unexp_rec.bom_implementation_date :=
1769 Get_BOM_Implementation_Date
1770 ( p_bill_seq_id => l_rev_comp_unexp_rec.bill_Sequence_id);
1771 ELSE
1772 l_rev_comp_unexp_rec.bom_implementation_date := SYSDATE;
1773 END IF;
1774
1775 Error_Handler.Write_Debug('BOM Implementation date is '||l_rev_comp_unexp_rec.bom_implementation_date);
1776
1777 IF p_rev_component_rec.transaction_type IN
1778 ( BOM_Globals.G_OPR_UPDATE, BOM_globals.G_OPR_DELETE,
1779 BOM_Globals.G_OPR_CANCEL
1780 ) AND
1781 l_rev_comp_unexp_rec.bill_sequence_id IS NULL
1782 AND Bom_Globals.Get_Bo_Identifier <> Bom_Globals.G_ECO_BO -- Added by MK on 12/03/00
1783 THEN
1784 l_return_status := FND_API.G_RET_STS_ERROR;
1785
1786 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
1787 g_Token_Tbl(1).Token_Value :=
1788 p_rev_component_rec.revised_item_name;
1789
1790 Error_Handler.Add_Error_Token
1791 ( p_Message_Name => 'BOM_BILL_SEQUENCE_NOT_FOUND'
1792 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1793 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1794 , p_Token_Tbl => g_Token_Tbl
1795 );
1796 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1797 x_rev_comp_unexp_rec := l_rev_comp_unexp_rec;
1798 x_Return_Status := l_Return_Status;
1799 x_other_message := 'BOM_CMP_UUI_SEV_ERROR';
1800 x_other_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
1801 x_other_token_tbl(1).token_value :=
1802 p_rev_component_rec.component_item_name;
1803
1804 RETURN;
1805 END IF;
1806
1807 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Processing UUI_UI2 for components and retrieving enforce integer requirements code . . . '); END IF;
1808
1809 IF p_rev_component_rec.enforce_int_requirements IS NOT NULL AND
1810 p_rev_component_rec.enforce_int_requirements <> FND_API.G_MISS_CHAR
1811 THEN
1812 l_rev_comp_unexp_rec.enforce_int_requirements_code :=
1813 Get_EnforceInteger_Code(
1814 p_enforce_integer => p_rev_component_rec.enforce_int_requirements);
1815 IF l_rev_comp_unexp_rec.enforce_int_requirements_code IS NULL AND
1816 l_rev_comp_unexp_rec.enforce_int_requirements_code = FND_API.G_MISS_NUM
1817 THEN
1818 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
1819 g_Token_Tbl(1).Token_Value :=
1820 p_rev_component_rec.revised_item_name;
1821 g_Token_Tbl(2).Token_Name := 'ENFORCE_INTEGER';
1822 g_Token_Tbl(2).Token_Value :=
1823 p_rev_component_rec.enforce_int_requirements;
1824
1825 Error_Handler.Add_Error_Token
1826 ( p_Message_Name => 'BOM_ENFORCE_INTCODE_NOTFOUND'
1827 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1828 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1829 , p_Token_Tbl => g_Token_Tbl
1830 );
1831 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1832 x_rev_comp_unexp_rec := l_rev_comp_unexp_rec;
1833 x_Return_Status := l_Return_Status;
1834 x_other_message := 'BOM_CMP_UUI_SEV_ERROR';
1835 x_other_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
1836 x_other_token_tbl(1).token_value :=
1837 p_rev_component_rec.component_item_name;
1838
1839 RETURN;
1840 END IF;
1841 END IF;
1842
1843
1844 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1845 x_rev_comp_unexp_rec := l_rev_comp_unexp_rec;
1846 x_Return_Status := l_Return_Status;
1847
1848 END Rev_Component_UUI_to_UI2;
1849
1850
1851 /******************************************************************
1852 * Procedure : Sub_Component_UUI_To_UI
1853 * Parameters IN : Substitute component exposed columns record
1854 * Substitute component unexposed columns record
1855 * Parameters OUT: Substitute component unexposed columns record
1856 * Mesg_Token_Tbl
1857 * Return_Status
1858 * Purpose : This procedure will perform val-id conversion for all
1859 * the Substitute component columns that form the unique
1860 * key for this entity.
1861 ********************************************************************/
1862 PROCEDURE Sub_Component_UUI_To_UI
1863 ( p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
1864 , p_sub_comp_unexp_rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
1865 , x_sub_Comp_unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
1866 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1867 , x_Return_Status IN OUT NOCOPY VARCHAR2
1868 )
1869 IS
1870 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
1871 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1872 l_Err_Text VARCHAR2(2000);
1873 l_return_status VARCHAR2(1);
1874 l_token_tbl Error_Handler.Token_Tbl_Type;
1875 BEGIN
1876 l_sub_comp_unexp_rec := p_sub_comp_unexp_rec;
1877 l_return_status := FND_API.G_RET_STS_SUCCESS;
1878
1879 /*************************************************************
1880 --
1881 -- Verify ACD Type is Add or Disable
1882 --
1883 *************************************************************/
1884
1885 IF ( p_sub_component_rec.acd_type IS NULL OR
1886 p_sub_component_rec.acd_type NOT IN (1, 3)
1887 ) AND
1888 Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
1889 THEN
1890 Error_Handler.Add_Error_Token
1891 ( p_Message_Name => 'BOM_SBC_ACD_TYPE_INVALID'
1892 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1893 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1894 );
1895 l_Return_Status := FND_API.G_RET_STS_ERROR;
1896 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1897 x_sub_comp_unexp_rec := l_sub_comp_unexp_rec;
1898 x_Return_Status := l_Return_Status;
1899 RETURN;
1900 END IF;
1901
1902 /*************************************************************
1903 --
1904 -- Verify that the substitute component unique key columns are
1905 -- not null
1906 --
1907 ****************************************************************/
1908 IF p_sub_component_rec.substitute_component_name IS NULL OR
1909 p_sub_component_rec.substitute_component_name = FND_API.G_MISS_CHAR
1910 THEN
1911 Error_Handler.Add_Error_Token
1912 ( p_Message_Name => 'BOM_SCOMP_NAME_KEYCOL_NULL'
1913 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1914 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1915 );
1916 l_Return_Status := FND_API.G_RET_STS_ERROR;
1917 END IF;
1918 IF p_sub_component_rec.component_item_name IS NULL OR
1919 p_sub_component_rec.component_item_name = FND_API.G_MISS_CHAR
1920 THEN
1921 Error_Handler.Add_Error_Token
1922 ( p_Message_Name => 'BOM_SCOMP_RCOMP_KEYCOL_NULL'
1923 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1924 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1925 );
1926 l_Return_Status := FND_API.G_RET_STS_ERROR;
1927 END IF;
1928 IF p_sub_component_rec.revised_item_name IS NULL OR
1929 p_sub_component_rec.revised_item_name = FND_API.G_MISS_CHAR
1930 THEN
1931 Error_Handler.Add_Error_Token
1932 ( p_Message_Name => 'BOM_SCOMP_RITEM_KEYCOL_NULL'
1933 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1934 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1935 );
1936 l_Return_Status := FND_API.G_RET_STS_ERROR;
1937 END IF;
1938
1939 IF p_sub_component_rec.operation_sequence_number IS NULL OR
1940 p_sub_component_rec.operation_sequence_number = FND_API.G_MISS_NUM
1941 THEN
1942 Error_Handler.Add_Error_Token
1943 ( p_Message_Name => 'BOM_SCOMP_OPSEQ_KEYCOL_NULL'
1944 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1945 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1946 );
1947 l_Return_Status := FND_API.G_RET_STS_ERROR;
1948 END IF;
1949
1950 IF p_sub_component_rec.start_effective_date IS NULL OR
1951 p_sub_component_rec.start_effective_date = FND_API.G_MISS_DATE
1952 THEN
1953 Error_Handler.Add_Error_Token
1954 ( p_Message_Name => 'BOM_SCOMP_DATE_KEYCOL_NULL'
1955 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1956 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1957 );
1958 l_Return_Status := FND_API.G_RET_STS_ERROR;
1959 END IF;
1960
1961 --
1962 -- If key columns are NULL, then return.
1963 --
1964 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1965 THEN
1966 x_return_status := l_return_status;
1967 x_mesg_token_tbl := l_mesg_token_tbl;
1968 RETURN;
1969 END IF;
1970
1971 /***************************************************************
1972 --
1973 -- Convert substitute item name to Substitute Item ID
1974 --
1975 ****************************************************************/
1976 l_sub_comp_unexp_rec.substitute_component_id :=
1977 Substitute_Component( p_substitute_component =>
1978 p_sub_component_rec.substitute_component_name
1979 , p_organization_id =>
1980 l_sub_comp_unexp_rec.organization_id
1981 , x_err_text => l_err_text
1982 );
1983 IF l_sub_comp_unexp_rec.substitute_component_id IS NULL
1984 THEN
1985 g_Token_Tbl(1).Token_Name := 'SUBSTITUTE_ITEM_NAME';
1986 g_Token_Tbl(1).Token_Value :=
1987 p_sub_component_rec.substitute_component_name;
1988 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
1989 g_token_tbl(2).token_value :=
1990 p_sub_component_rec.organization_code;
1991
1992 Error_Handler.Add_Error_Token
1993 ( p_Message_Name => 'BOM_SUBSTITUTE_ITEM_INVALID'
1994 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1995 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1996 , p_Token_Tbl => g_Token_Tbl
1997 );
1998 l_Return_Status := FND_API.G_RET_STS_ERROR;
1999 END IF;
2000
2001 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2002 x_sub_comp_unexp_rec := l_sub_comp_unexp_rec;
2003 x_return_Status := l_return_status;
2004
2005 /********************************************************************
2006 -- If Transaction Type is Update and New substitute component is given,
2007 -- Convert New substitute item name to New Substitute Item ID
2008 --
2009 *********************************************************************/
2010
2011 IF ( p_Sub_Component_Rec.new_substitute_component_name is not null
2012 AND p_sub_component_rec.substitute_component_name <> FND_API.G_MISS_CHAR
2013 and p_sub_component_rec.transaction_type = Bom_Globals.G_OPR_UPDATE)
2014 THEN
2015 l_sub_comp_unexp_rec.new_substitute_component_id :=
2016 Substitute_Component( p_substitute_component =>
2017 p_sub_component_rec.new_substitute_component_name
2018 , p_organization_id =>
2019 l_sub_comp_unexp_rec.organization_id
2020 , x_err_text => l_err_text
2021 );
2022 IF l_sub_comp_unexp_rec.new_substitute_component_id IS NULL
2023 THEN
2024 g_Token_Tbl(1).Token_Name := 'SUBSTITUTE_ITEM_NAME';
2025 g_Token_Tbl(1).Token_Value :=
2026 p_sub_component_rec.new_substitute_component_name;
2027 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
2028 g_token_tbl(2).token_value :=
2029 p_sub_component_rec.organization_code;
2030
2031 Error_Handler.Add_Error_Token
2032 ( p_Message_Name => 'BOM_SUBSTITUTE_ITEM_INVALID'
2033 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2034 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2035 , p_Token_Tbl => g_Token_Tbl
2036 );
2037 l_Return_Status := FND_API.G_RET_STS_ERROR;
2038 END IF;
2039
2040 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2041 x_sub_comp_unexp_rec := l_sub_comp_unexp_rec;
2042 x_return_Status := l_return_status;
2043 END IF;
2044
2045 END Sub_Component_UUI_To_UI;
2046
2047 /**************************************************************************
2048 * Procedure : Sub_Component_UUI_To_UI2
2049 * Purpose : This procedure is similar to the UUI-UI conversion
2050 * The only reason that this procedure is seperated is that
2051 * the calling program will be able to distinguish between
2052 * the scope of the error when conversion in this procedure
2053 * fail and would then error all the siblings.
2054 ***************************************************************************/
2055 PROCEDURE Sub_Component_UUI_To_UI2
2056 ( p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
2057 , p_sub_comp_unexp_rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
2058 , x_sub_Comp_unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
2059 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2060 , x_other_message IN OUT NOCOPY VARCHAR2
2061 , x_other_token_tbl IN OUT NOCOPY Error_Handler.Token_Tbl_Type
2062 , x_Return_Status IN OUT NOCOPY VARCHAR2
2063 )
2064 IS
2065 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
2066 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2067 l_Err_Text VARCHAR2(2000);
2068 l_return_status VARCHAR2(1);
2069 l_dummy NUMBER;
2070 BEGIN
2071 l_sub_comp_unexp_rec := p_sub_comp_unexp_rec;
2072 l_return_status := FND_API.G_RET_STS_SUCCESS;
2073
2074
2075 --
2076 -- If any of the revised item key columns are NULL, then
2077 -- return with an other message
2078 --
2079 IF p_sub_component_rec.revised_item_name IS NULL OR
2080 p_sub_component_rec.revised_item_name = FND_API.G_MISS_CHAR
2081 THEN
2082 Error_Handler.Add_Error_Token
2083 ( p_Message_Name => 'BOM_SCOMP_RITEM_KEYCOL_NULL'
2084 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2085 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2086 );
2087 l_Return_Status := FND_API.G_RET_STS_ERROR;
2088 END IF;
2089
2090 --
2091 -- If key any of the parent key columns are NULL, then return.
2092 --
2093 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2094 THEN
2095 x_return_status := l_return_status;
2096 x_mesg_token_tbl := l_mesg_token_tbl;
2097 x_sub_comp_unexp_rec := l_sub_comp_unexp_rec;
2098 x_other_message := 'BOM_REV_ITEM_KEY_NULL';
2099 RETURN;
2100 END IF;
2101
2102 IF p_sub_component_rec.component_item_name IS NULL OR
2103 p_sub_component_rec.component_item_name = FND_API.G_MISS_CHAR
2104 THEN
2105 Error_Handler.Add_Error_Token
2106 ( p_Message_Name => 'BOM_SCOMP_RCOMP_KEYCOL_NULL'
2107 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2108 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2109 );
2110 l_Return_Status := FND_API.G_RET_STS_ERROR;
2111 END IF;
2112
2113 IF p_sub_component_rec.operation_sequence_number IS NULL OR
2114 p_sub_component_rec.operation_sequence_number = FND_API.G_MISS_NUM
2115 THEN
2116 Error_Handler.Add_Error_Token
2117 ( p_Message_Name => 'BOM_SCOMP_OPSEQ_KEYCOL_NULL'
2118 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2119 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2120 );
2121 l_Return_Status := FND_API.G_RET_STS_ERROR;
2122 END IF;
2123
2124 IF p_sub_component_rec.start_effective_date IS NULL OR
2125 p_sub_component_rec.start_effective_date = FND_API.G_MISS_DATE
2126 THEN
2127 Error_Handler.Add_Error_Token
2128 ( p_Message_Name => 'BOM_SCOMP_DATE_KEYCOL_NULL'
2129 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2130 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2131 );
2132 l_Return_Status := FND_API.G_RET_STS_ERROR;
2133 END IF;
2134
2135 --
2136 -- If any of the revised component key columns are NULL, then
2137 -- return with an other message.
2138 --
2139 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2140 THEN
2141 x_return_status := l_return_status;
2142 x_mesg_token_tbl := l_mesg_token_tbl;
2143 x_other_message := 'BOM_REV_COMP_KEY_NULL';
2144 RETURN;
2145 END IF;
2146
2147 /****************************************************************
2148 --
2149 -- Convert revised item name to revised item ID
2150 --
2151 ******************************************************************/
2152 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
2153 g_Token_Tbl(1).Token_Value := p_sub_component_rec.revised_item_name;
2154 g_Token_Tbl(1).Token_Name := 'ORGANIZATION_CODE';
2155 g_Token_Tbl(1).Token_Value := p_sub_component_rec.organization_code;
2156
2157 -- dbms_output.put_line('Revised Item: ' || p_sub_component_rec.revised_item_name);
2158
2159 l_sub_comp_unexp_rec.revised_item_id :=
2160 Revised_Item( p_revised_item_num =>
2161 p_sub_component_rec.revised_item_name
2162 , p_organization_id =>
2163 l_sub_comp_unexp_rec.organization_id
2164 , x_err_text => l_err_text
2165 );
2166
2167 IF l_sub_comp_unexp_rec.revised_item_id IS NULL
2168 THEN
2169 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
2170 g_Token_Tbl(1).Token_Value :=
2171 p_sub_component_rec.revised_item_name;
2172 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
2173 g_token_tbl(2).token_value :=
2174 p_sub_component_rec.organization_code;
2175 Error_Handler.Add_Error_Token
2176 ( p_Message_Name => 'ENG_REVISED_ITEM_INVALID'
2177 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2178 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2179 , p_Token_Tbl => g_Token_Tbl
2180 );
2181 l_Return_Status := FND_API.G_RET_STS_ERROR;
2182 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2183 x_sub_comp_unexp_rec := l_sub_comp_unexp_rec;
2184 --
2185 -- Set the other message and its tokens
2186 --
2187 x_other_message := 'BOM_SBC_UUI_SEV_ERROR';
2188 x_other_token_tbl(1).token_name := 'SUBSTITUTE_ITEM_NAME';
2189 x_other_token_tbl(1).token_value :=
2190 p_sub_component_rec.substitute_component_name;
2191
2192 x_Return_Status := l_Return_Status;
2193 RETURN;
2194 END IF;
2195
2196 /***********************************************************
2197 --
2198 -- Convert Component Item Name to Component Item ID
2199 --
2200 ************************************************************/
2201 l_sub_comp_unexp_rec.component_item_id :=
2202 Component_Item( p_organization_id =>
2203 l_sub_comp_unexp_rec.organization_id
2204 , p_component_item_num =>
2205 p_sub_component_rec.component_item_name
2206 , x_err_text => l_err_text
2207 );
2208
2209 IF l_sub_comp_unexp_rec.component_item_id IS NULL
2210 THEN
2211 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
2212 g_Token_Tbl(1).Token_Value :=
2213 p_sub_component_rec.component_item_name;
2214 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
2215 g_token_tbl(2).token_value :=
2216 p_sub_component_rec.organization_code;
2217
2218 Error_Handler.Add_Error_Token
2219 ( p_Message_Name => 'BOM_REVISED_COMP_INVALID'
2220 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2221 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2222 , p_Token_Tbl => g_Token_Tbl
2223 );
2224 l_Return_Status := FND_API.G_RET_STS_ERROR;
2225 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2226 x_sub_comp_unexp_rec := l_sub_comp_unexp_rec;
2227 --
2228 -- Set the other message and its tokens
2229 --
2230 x_other_message := 'BOM_SBC_UUI_SEV_ERROR';
2231 x_other_token_tbl(1).token_name := 'SUBSTITUTE_ITEM_NAME';
2232 x_other_token_tbl(1).token_value :=
2233 p_sub_component_rec.substitute_component_name;
2234
2235 x_Return_Status := l_Return_Status;
2236 RETURN;
2237 END IF;
2238
2239
2240 /****************************************************************
2241 --
2242 -- Convert revised item information to bill_sequence_id
2243 --
2244 *****************************************************************/
2245 /*
2246 dbms_output.put_line('Revised Id: ' || l_sub_comp_unexp_rec.revised_item_id);
2247 dbms_output.put_line('Component Id: '|| l_sub_comp_unexp_rec.component_item_id);
2248 dbms_output.put_line('Rev: ' || p_sub_component_rec.new_revised_item_revision);
2249 dbms_output.put_line('Eff.Date: ' || p_sub_component_rec.start_effective_date);
2250 */
2251 IF Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
2252 THEN
2253 NULL ;
2254
2255 /* Following getting revised_item_sequence_id moved to Engineering
2256 -- space to resolve dependency.
2257 -- by MK on 12/03/00
2258
2259 l_sub_comp_unexp_rec.revised_item_sequence_id :=
2260 -- Modifed by MK 11/02/00
2261 BillAndRevItemSeq( p_revised_item_id =>
2262 l_sub_comp_unexp_rec.revised_item_id
2263 , p_item_revision =>
2264 p_sub_component_rec.new_revised_item_revision
2265 , p_effective_date =>
2266 p_sub_component_rec.start_effective_date
2267 , p_change_notice =>
2268 p_sub_component_rec.eco_name
2269 , p_organization_id =>
2270 l_sub_comp_unexp_rec.organization_id
2271 , p_new_routing_revision =>
2272 p_sub_component_rec.new_routing_revision
2273 , p_from_end_item_number =>
2274 p_sub_component_rec.from_end_item_unit_number
2275 , x_Bill_Sequence_Id =>
2276 l_sub_comp_unexp_rec.bill_sequence_id
2277 );
2278
2279
2280
2281 IF l_sub_comp_unexp_rec.revised_item_Sequence_id IS NULL
2282 THEN
2283 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
2284 g_Token_Tbl(1).Token_Value :=
2285 p_sub_component_rec.component_item_name;
2286 g_Token_Tbl(2).Token_Name := 'SUBSTITUTE_ITEM_NAME';
2287 g_Token_Tbl(2).Token_Value :=
2288 p_sub_component_rec.substitute_component_name;
2289 g_token_tbl(3).token_name := 'ECO_NAME';
2290 g_token_tbl(3).token_value :=
2291 p_sub_component_rec.eco_name;
2292 Error_Handler.Add_Error_Token
2293 ( p_Message_Name => 'BOM_SBC_REV_SEQ_NOT_FOUND'
2294 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2295 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2296 , p_Token_Tbl => g_Token_Tbl
2297 );
2298 l_Return_Status := FND_API.G_RET_STS_ERROR;
2299 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2300 x_sub_comp_unexp_rec := l_sub_comp_unexp_rec;
2301 --
2302 -- Set the other message and its tokens
2303 --
2304 x_other_message := 'BOM_SBC_UUI_SEV_ERROR';
2305 x_other_token_tbl(1).token_name := 'SUBSTITUTE_ITEM_NAME';
2306 x_other_token_tbl(1).token_value :=
2307 p_sub_component_rec.substitute_component_name;
2308
2309 x_Return_Status := l_Return_Status;
2310 RETURN;
2311 END IF;
2312
2313 */ -- Comment out by MK on 12/03/00
2314
2315 ELSE
2316 --
2317 -- If the calling BO is BOM then get the bill sequence id
2318 --
2319 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Getting bill_seq for assembly item ' || l_sub_comp_unexp_rec.revised_item_id); END IF;
2320
2321 l_sub_comp_unexp_rec.bill_sequence_id :=
2322 bill_sequence_id(p_assembly_item_id =>
2323 l_sub_comp_unexp_rec.revised_item_id,
2324 p_organization_id =>
2325 l_sub_comp_unexp_rec.organization_id,
2326 p_alternate_bom_code =>
2327 p_sub_component_rec.alternate_bom_code,
2328 x_err_text => l_err_text
2329 );
2330
2331 IF l_sub_comp_unexp_rec.bill_Sequence_id IS NULL
2332 THEN
2333 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
2334 g_Token_Tbl(1).Token_Value :=
2335 p_sub_component_rec.component_item_name;
2336 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
2337 g_Token_Tbl(2).Token_Value :=
2338 p_sub_component_rec.revised_item_name;
2339
2340 Error_Handler.Add_Error_Token
2341 ( p_Message_Name => 'BOM_BOM_SEQUENCE_NOT_FOUND'
2342 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2343 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2344 , p_Token_Tbl => g_Token_Tbl
2345 );
2346 l_Return_Status := FND_API.G_RET_STS_ERROR;
2347 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2348 x_sub_comp_unexp_rec := l_sub_comp_unexp_rec;
2349 x_Return_Status := l_Return_Status;
2350 x_other_message := 'BOM_CMP_UUI_SEV_ERROR';
2351 x_other_token_tbl(1).token_name := 'SUBSTITUTE_COMPONENT_NAME';
2352 x_other_token_tbl(1).token_value :=
2353 p_sub_component_rec.component_item_name;
2354 RETURN;
2355 END IF;
2356 -- END IF; -- If-Else BO Is ECO Ends -- Comment out by MK on 12/03/00
2357
2358 /*****************************************************************
2359 --
2360 -- Convert component information to component_sequence_id
2361 --
2362 ******************************************************************/
2363 /*
2364 dbms_output.put_line('Bill Seq: ' ||
2365 to_char(l_sub_comp_unexp_rec.bill_sequence_id));
2366 dbms_output.put_line('Op Seq: ' ||
2367 to_char(p_sub_component_rec.operation_sequence_number));
2368 */
2369
2370 l_sub_comp_unexp_rec.component_sequence_id :=
2371 Component_Sequence( p_component_item_id =>
2372 l_sub_comp_unexp_rec.component_item_id
2373 , p_operation_sequence_num =>
2374 p_sub_component_rec.operation_sequence_number
2375 , p_effectivity_date =>
2376 p_sub_component_rec.start_effective_date
2377 , p_bill_sequence_id =>
2378 l_sub_comp_unexp_rec.bill_sequence_id
2379 , x_err_text => l_Err_Text
2380 );
2381
2382 if bom_globals.get_debug = 'Y' then Error_Handler.write_debug('Sub Comp: Component sequence ' || l_sub_comp_unexp_rec.component_sequence_id); END IF;
2383
2384 IF l_sub_comp_unexp_rec.component_sequence_id IS NULL
2385 THEN
2386 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
2387 g_Token_Tbl(1).Token_Value :=
2388 p_sub_component_rec.component_item_name;
2389 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
2390 g_Token_Tbl(2).Token_Value :=
2391 p_sub_component_rec.revised_item_name;
2392 g_Token_Tbl(3).Token_Name := 'SUBSTITUTE_ITEM_NAME';
2393 g_Token_Tbl(3).Token_Value :=
2394 p_sub_component_rec.substitute_component_name;
2395
2396
2397 Error_Handler.Add_Error_Token
2398 ( p_Message_Name => 'BOM_SBC_COMP_SEQ_NOT_FOUND'
2399 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2400 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2401 , p_Token_Tbl => g_Token_Tbl
2402 );
2403 --
2404 -- Set the other message and its tokens
2405 --
2406 x_other_message := 'BOM_SBC_UUI_SEV_ERROR';
2407 x_other_token_tbl(1).token_name := 'SUBSTITUTE_ITEM_NAME';
2408 x_other_token_tbl(1).token_value :=
2409 p_sub_component_rec.substitute_component_name;
2410
2411 l_Return_Status := FND_API.G_RET_STS_ERROR;
2412 g_Token_Tbl.Delete;
2413 END IF;
2414 END IF; -- If-Else BO Is ECO Ends -- Added by MK on 12/03/00
2415
2416 /*if bom_globals.get_debug = 'Y' then Error_Handler.write_debug('Sub Comp: Checking for editable common bill...'); END IF;
2417
2418 BEGIN
2419 SELECT 1
2420 INTO l_dummy
2421 FROM bom_bill_of_materials
2422 WHERE bill_sequence_id = source_bill_sequence_id
2423 AND bill_sequence_id = l_sub_comp_unexp_rec.bill_Sequence_id;
2424 EXCEPTION
2425 WHEN NO_DATA_FOUND THEN
2426 Error_Handler.Add_Error_Token
2427 ( p_Message_Name => 'BOM_COMMON_SUB_COMP'
2428 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2429 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2430 , p_Token_Tbl => g_Token_Tbl
2431 );
2432 l_Return_Status := FND_API.G_RET_STS_ERROR;
2433 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2434 x_sub_comp_unexp_rec := l_sub_comp_unexp_rec;
2435 x_Return_Status := l_Return_Status;
2436 x_other_message := 'BOM_CMP_UUI_SEV_ERROR';
2437 x_other_token_tbl(1).token_name := 'SUBSTITUTE_COMPONENT_NAME';
2438 x_other_token_tbl(1).token_value := p_sub_component_rec.component_item_name;
2439 RETURN;
2440 END;
2441
2442 */
2443
2444 if bom_globals.get_debug = 'Y' then Error_Handler.write_debug('Sub Comp: Enforce Int Requirements ' || p_sub_component_rec.enforce_int_requirements); END IF;
2445
2446 IF p_sub_component_rec.enforce_int_requirements IS NOT NULL AND
2447 p_sub_component_rec.enforce_int_requirements <> FND_API.G_MISS_CHAR
2448 THEN
2449
2450 l_sub_comp_unexp_rec.enforce_int_requirements_code :=
2451 Get_EnforceInteger_Code(
2452 p_enforce_integer => p_sub_component_rec.enforce_int_requirements);
2453
2454 if bom_globals.get_debug = 'Y' then Error_Handler.write_debug('Sub Comp: Enforce Int Requirements code ' || to_char(l_sub_comp_unexp_rec.enforce_int_requirements_code)); END IF;
2455
2456 IF l_sub_comp_unexp_rec.enforce_int_requirements_code IS NULL AND
2457 l_sub_comp_unexp_rec.enforce_int_requirements_code = FND_API.G_MISS_NUM
2458 THEN
2459 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
2460 g_Token_Tbl(1).Token_Value :=
2461 p_sub_component_rec.component_item_name;
2462 g_Token_Tbl(2).Token_Name := 'SUBSTITUTE_ITEM_NAME';
2463 g_Token_Tbl(2).Token_Value :=
2464 p_sub_component_rec.substitute_component_name;
2465 g_Token_Tbl(3).Token_Name := 'ENFORCE_INTEGER';
2466 g_Token_Tbl(3).Token_Value :=
2467 p_sub_component_rec.enforce_int_requirements;
2468
2469 Error_Handler.Add_Error_Token
2470 ( p_Message_Name => 'BOM_ENFORCE_INTCODE_NOTFOUND'
2471 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2472 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2473 , p_Token_Tbl => g_Token_Tbl
2474 );
2475 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2476 x_sub_comp_unexp_rec := l_sub_comp_unexp_rec;
2477 x_Return_Status := l_Return_Status;
2478 x_other_message := 'BOM_SBC_UUI_SEV_ERROR';
2479 x_other_token_tbl(1).token_name := 'SUBSTITUTE_COMPONENT_NAME';
2480 x_other_token_tbl(1).token_value :=
2481 p_sub_component_rec.substitute_component_name;
2482
2483 RETURN;
2484 END IF;
2485 END IF;
2486
2487
2488 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2489 x_sub_comp_unexp_rec := l_sub_comp_unexp_rec;
2490 x_return_status := l_return_status;
2491
2492 END Sub_Component_UUI_To_UI2;
2493
2494 /***************************************************************
2495 * Procedure : Ref_Designator_UUI_To_UI
2496 * Parameters IN : Reference Designator exposed column record
2497 * Reference designator unexposed column record
2498 * Parameters OUT: Reference Designator unxposed column record
2499 * Mesg Token Tbl
2500 * Return Status
2501 * Purpose : This procedure will convert user unique idx columns
2502 * into unique id columns.
2503 ********************************************************************/
2504 PROCEDURE Ref_Designator_UUI_To_UI
2505 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
2506 , p_ref_desg_unexp_rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
2507 , x_ref_desg_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
2508 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2509 , x_Return_Status IN OUT NOCOPY VARCHAR2
2510 )
2511 IS
2512 l_ref_desg_unexp_rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type;
2513 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2514 l_Err_Text VARCHAR2(2000);
2515 l_Return_Status VARCHAR2(1);
2516 l_token_tbl Error_Handler.Token_Tbl_Type;
2517
2518 BEGIN
2519
2520 l_return_status := FND_API.G_RET_STS_SUCCESS;
2521
2522 /***************************************************************
2523 --
2524 -- Verify that ACD_Type is Valid
2525 --
2526 ****************************************************************/
2527 IF Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO AND
2528 ( p_ref_designator_rec.acd_type IS NULL OR
2529 p_ref_designator_rec.acd_type NOT IN (1, 3)
2530 )
2531 THEN
2532 --added for bug 9647673 (begin)
2533 l_token_tbl.delete;
2534 l_token_tbl(1).token_name := 'REF_DESG_NAME';
2535 l_token_tbl(1).token_value := p_ref_designator_rec.Reference_Designator_Name;
2536
2537 l_token_tbl(2).token_name := 'COMP_ITEM_NAME';
2538 l_token_tbl(2).token_value := p_ref_designator_rec.Component_Item_Name;
2539
2540 l_token_tbl(3).token_name := 'REVISED_ITEM_NAME';
2541 l_token_tbl(3).token_value := p_ref_designator_rec.Revised_Item_Name;
2542
2543 l_token_tbl(4).token_name := 'ACD_TYPE';
2544 l_token_tbl(4).token_value := p_ref_designator_rec.acd_type;
2545
2546 --added for bug 9647673 (end)
2547
2548 Error_Handler.Add_Error_Token
2549 ( p_Message_Name => 'BOM_RFD_ACD_TYPE_INVALID'
2550 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2551 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2552 , p_Token_Tbl => l_Token_Tbl --added for bug 9647673
2553
2554 );
2555 l_Return_Status := FND_API.G_RET_STS_ERROR;
2556 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2557 x_ref_desg_unexp_rec := l_ref_desg_unexp_rec;
2558 x_Return_Status := l_Return_Status;
2559 RETURN;
2560 END IF;
2561
2562 /****************************************************************
2563 --
2564 -- Convert revised item name to revised item ID
2565 --
2566 ******************************************************************/
2567
2568 g_Token_Tbl(1).Token_Name := 'REV_ITEM';
2569 g_Token_Tbl(1).Token_Value := p_ref_designator_rec.revised_item_name;
2570 l_ref_desg_unexp_rec := p_ref_desg_unexp_rec;
2571
2572 /*************************************************************
2573 --
2574 -- Verify that the reference designator unique key columns are
2575 -- not null
2576 --
2577 ****************************************************************/
2578 IF p_ref_designator_rec.reference_designator_name IS NULL OR
2579 p_ref_designator_rec.reference_designator_name = FND_API.G_MISS_CHAR
2580 THEN
2581 Error_Handler.Add_Error_Token
2582 ( p_Message_Name => 'BOM_RDESG_NAME_KEYCOL_NULL'
2583 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2584 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2585 );
2586 l_Return_Status := FND_API.G_RET_STS_ERROR;
2587
2588 END IF;
2589
2590 IF p_ref_designator_rec.component_item_name IS NULL OR
2591 p_ref_designator_rec.component_item_name = FND_API.G_MISS_CHAR
2592 THEN
2593 Error_Handler.Add_Error_Token
2594 ( p_Message_Name => 'BOM_RDESG_RCOMP_KEYCOL_NULL'
2595 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2596 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2597 );
2598 l_Return_Status := FND_API.G_RET_STS_ERROR;
2599
2600 END IF;
2601
2602 IF p_ref_designator_rec.revised_item_name IS NULL OR
2603 p_ref_designator_rec.revised_item_name = FND_API.G_MISS_CHAR
2604 THEN
2605 Error_Handler.Add_Error_Token
2606 ( p_Message_Name => 'BOM_RDESG_RITEM_KEYCOL_NULL'
2607 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2608 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2609 );
2610 l_Return_Status := FND_API.G_RET_STS_ERROR;
2611
2612 END IF;
2613
2614 IF p_ref_designator_rec.operation_sequence_number IS NULL OR
2615 p_ref_designator_rec.operation_sequence_number = FND_API.G_MISS_NUM
2616 THEN
2617 Error_Handler.Add_Error_Token
2618 ( p_Message_Name => 'BOM_RDESG_OPSEQ_KEYCOL_NULL'
2619 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2620 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2621 );
2622 l_Return_Status := FND_API.G_RET_STS_ERROR;
2623
2624 END IF;
2625
2626 IF p_ref_designator_rec.start_effective_date IS NULL OR
2627 p_ref_designator_rec.start_effective_date = FND_API.G_MISS_DATE
2628 THEN
2629 Error_Handler.Add_Error_Token
2630 ( p_Message_Name => 'BOM_RDESG_DATE_KEYCOL_NULL'
2631 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2632 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2633 );
2634 l_Return_Status := FND_API.G_RET_STS_ERROR;
2635 END IF;
2636
2637 --
2638 -- If key columns are NULL then return
2639 --
2640 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2641 THEN
2642 x_return_status := l_return_status;
2643 x_mesg_token_tbl := l_mesg_token_tbl;
2644 RETURN;
2645 END IF;
2646
2647 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2648 x_ref_desg_unexp_rec := l_ref_desg_unexp_rec;
2649 x_Return_Status := l_Return_Status;
2650
2651 END Ref_Designator_UUI_To_UI;
2652
2653 /****************************************************************
2654 * Procedure : Ref_Designator_UUI_To_UI2
2655 * Purpose : This procedure is similar to the UUI-UI. The calling
2656 * program can decide on the scope of the error if the
2657 * conversion in this procedure fails.
2658 ******************************************************************/
2659 PROCEDURE Ref_Designator_UUI_To_UI2
2660 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
2661 , p_ref_desg_unexp_rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
2662 , x_ref_desg_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
2663 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2664 , x_other_message IN OUT NOCOPY VARCHAR2
2665 , x_other_token_tbl IN OUT NOCOPY Error_Handler.Token_Tbl_Type
2666 , x_Return_Status IN OUT NOCOPY VARCHAR2
2667 )
2668 IS
2669 l_ref_desg_unexp_rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type :=
2670 p_ref_desg_unexp_rec;
2671 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2672 l_Err_Text VARCHAR2(2000);
2673 l_Return_Status VARCHAR2(1);
2674 l_dummy NUMBER;
2675 BEGIN
2676
2677 --
2678 -- If any of the revised item key columns are NULL, then set the
2679 -- other message and its token and return.
2680 --
2681 IF p_ref_designator_rec.revised_item_name IS NULL OR
2682 p_ref_designator_rec.revised_item_name = FND_API.G_MISS_CHAR
2683 THEN
2684 Error_Handler.Add_Error_Token
2685 ( p_Message_Name => 'BOM_RDESG_RITEM_KEYCOL_NULL'
2686 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2687 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2688 );
2689 l_Return_Status := FND_API.G_RET_STS_ERROR;
2690
2691 END IF;
2692
2693 --
2694 -- Return if revised item key is NULL
2695 --
2696 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2697 THEN
2698 x_return_status := l_return_status;
2699 x_ref_desg_unexp_rec := l_ref_desg_unexp_rec;
2700 x_mesg_token_tbl := l_mesg_token_tbl;
2701 x_other_message := 'BOM_REV_ITEM_KEY_NULL';
2702 RETURN;
2703 END IF;
2704
2705 IF p_ref_designator_rec.component_item_name IS NULL OR
2706 p_ref_designator_rec.component_item_name = FND_API.G_MISS_CHAR
2707 THEN
2708 Error_Handler.Add_Error_Token
2709 ( p_Message_Name => 'BOM_RDESG_RCOMP_KEYCOL_NULL'
2710 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2711 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2712 );
2713 l_Return_Status := FND_API.G_RET_STS_ERROR;
2714
2715 END IF;
2716
2717 IF p_ref_designator_rec.operation_sequence_number IS NULL OR
2718 p_ref_designator_rec.operation_sequence_number = FND_API.G_MISS_NUM
2719 THEN
2720 Error_Handler.Add_Error_Token
2721 ( p_Message_Name => 'BOM_RDESG_OPSEQ_KEYCOL_NULL'
2722 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2723 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2724 );
2725 l_Return_Status := FND_API.G_RET_STS_ERROR;
2726
2727 END IF;
2728
2729 IF p_ref_designator_rec.start_effective_date IS NULL OR
2730 p_ref_designator_rec.start_effective_date = FND_API.G_MISS_DATE
2731 THEN
2732 Error_Handler.Add_Error_Token
2733 ( p_Message_Name => 'BOM_RDESG_DATE_KEYCOL_NULL'
2734 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2735 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2736 );
2737 l_Return_Status := FND_API.G_RET_STS_ERROR;
2738 END IF;
2739
2740 --
2741 -- If key columns are NULL then return
2742 --
2743 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2744 THEN
2745 x_return_status := l_return_status;
2746 x_mesg_token_tbl := l_mesg_token_tbl;
2747 x_other_message := 'BOM_REV_COMP_KEY_NULL';
2748 x_ref_desg_unexp_rec := l_ref_desg_unexp_rec;
2749 RETURN;
2750 END IF;
2751
2752 l_ref_desg_unexp_rec.revised_item_id :=
2753 Revised_Item( p_revised_item_num =>
2754 p_ref_designator_rec.revised_item_name
2755 , p_organization_id =>
2756 l_ref_desg_unexp_rec.organization_id
2757 , x_err_text => l_err_text
2758 );
2759
2760 IF l_ref_desg_unexp_rec.revised_item_id IS NULL
2761 THEN
2762 g_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
2763 g_token_tbl(1).token_value :=
2764 p_ref_designator_rec.revised_item_name;
2765 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
2766 g_token_tbl(2).token_value :=
2767 p_ref_designator_rec.organization_code;
2768
2769 Error_Handler.Add_Error_Token
2770 ( p_Message_Name => 'ENG_REVISED_ITEM_INVALID'
2771 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2772 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2773 , p_Token_Tbl => g_Token_Tbl
2774 );
2775 g_token_tbl.delete(2);
2776
2777 l_Return_Status := FND_API.G_RET_STS_ERROR;
2778 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2779 x_ref_desg_unexp_rec := l_ref_desg_unexp_rec;
2780 --
2781 -- Set the other message
2782 --
2783 x_other_message := 'BOM_RFD_UUI_SEV_ERROR';
2784 x_other_token_tbl(1).token_name := 'REFERENCE_DESIGNATOR_NAME';
2785 x_other_token_tbl(1).token_value :=
2786 p_ref_designator_rec.reference_designator_name;
2787
2788 x_Return_Status := l_Return_Status;
2789 RETURN;
2790 END IF;
2791
2792 /***********************************************************
2793 --
2794 -- Convert Component Item Name to Component Item ID
2795 --
2796 ************************************************************/
2797 l_ref_desg_unexp_rec.component_item_id :=
2798 Component_Item( p_organization_id =>
2799 l_ref_desg_unexp_rec.organization_id
2800 , p_component_item_num =>
2801 p_ref_designator_rec.component_item_name
2802 , x_err_text => l_err_text
2803 );
2804
2805 IF l_ref_desg_unexp_rec.component_item_id IS NULL
2806 THEN
2807
2808 g_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
2809 g_token_tbl(1).token_value :=
2810 p_ref_designator_rec.component_item_name;
2811 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
2812 g_token_tbl(2).token_value :=
2813 p_ref_designator_rec.organization_code;
2814
2815 Error_Handler.Add_Error_Token
2816 ( p_Message_Name => 'BOM_REVISED_COMP_INVALID'
2817 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2818 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2819 , p_Token_Tbl => g_Token_Tbl
2820 );
2821 l_Return_Status := FND_API.G_RET_STS_ERROR;
2822 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2823 x_ref_desg_unexp_rec := l_ref_desg_unexp_rec;
2824 --
2825 -- Set the other message
2826 --
2827 x_other_message := 'BOM_RFD_UUI_SEV_ERROR';
2828 x_other_token_tbl(1).token_name := 'REFERENCE_DESIGNATOR_NAME';
2829 x_other_token_tbl(1).token_value :=
2830 p_ref_designator_rec.reference_designator_name;
2831
2832 x_Return_Status := l_Return_Status;
2833 RETURN;
2834 END IF;
2835
2836 /****************************************************************
2837 --
2838 -- Convert revised item information to bill_sequence_id
2839 --
2840 *****************************************************************/
2841 IF Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
2842 THEN
2843 NULL ;
2844
2845 /* Following getting revised_item_sequence_id moved to Engineering
2846 -- space to resolve dependency.
2847 -- by MK on 12/03/00
2848
2849 l_ref_desg_unexp_rec.revised_item_sequence_id :=
2850 -- Modified by MK 11/02/00
2851 BillAndRevItemSeq( p_revised_item_id =>
2852 l_ref_desg_unexp_rec.revised_item_id
2853 , p_item_revision =>
2854 p_ref_designator_rec.new_revised_item_revision
2855 , p_effective_date =>
2856 p_ref_designator_rec.start_effective_date
2857 , p_change_notice =>
2858 p_ref_designator_rec.eco_name
2859 , p_organization_id =>
2860 l_ref_desg_unexp_rec.organization_id
2861 , p_new_routing_revision =>
2862 p_ref_designator_rec.new_routing_revision
2863 , p_from_end_item_number =>
2864 p_ref_designator_rec.from_end_item_unit_number
2865 , x_Bill_Sequence_Id =>
2866 l_ref_desg_unexp_rec.bill_sequence_id
2867 );
2868
2869
2870
2871 IF l_ref_desg_unexp_rec.revised_item_Sequence_id IS NULL
2872 THEN
2873 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
2874 g_Token_Tbl(1).Token_Value :=
2875 p_ref_designator_rec.revised_item_name;
2876 g_Token_Tbl(2).Token_Name := 'REFERENCE_DESIGNATOR_NAME';
2877 g_Token_Tbl(2).Token_Value :=
2878 p_ref_designator_rec.reference_designator_name;
2879 g_Token_Tbl(3).Token_Name := 'ECO_NAME';
2880 g_Token_Tbl(3).Token_Value :=
2881 p_ref_designator_rec.eco_name;
2882
2883 Error_Handler.Add_Error_Token
2884 ( p_Message_Name => 'BOM_RFD_REV_SEQ_NOT_FOUND'
2885 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2886 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2887 , p_Token_Tbl => g_Token_Tbl
2888 );
2889 l_Return_Status := FND_API.G_RET_STS_ERROR;
2890 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2891 x_ref_desg_unexp_rec := l_ref_desg_unexp_rec;
2892 --
2893 -- Set the other message
2894 --
2895 x_other_message := 'BOM_RFD_UUI_SEV_ERROR';
2896 x_other_token_tbl(1).token_name := 'REFERENCE_DESIGNATOR_NAME';
2897 x_other_token_tbl(1).token_value :=
2898 p_ref_designator_rec.reference_designator_name;
2899
2900 x_Return_Status := l_Return_Status;
2901 RETURN;
2902 END IF;
2903
2904 */ -- Comment out by MK on 12/03/00
2905
2906 ELSE
2907 --
2908 -- If the calling BO is BOM then get the bill sequence id
2909 --
2910 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Getting bill_seq f
2911 or assembly item ' || l_ref_desg_unexp_rec.revised_item_id); END IF;
2912
2913 l_ref_desg_unexp_rec.bill_sequence_id :=
2914 bill_sequence_id(p_assembly_item_id =>
2915 l_ref_desg_unexp_rec.revised_item_id,
2916 p_organization_id =>
2917 l_ref_desg_unexp_rec.organization_id,
2918 p_alternate_bom_code =>
2919 p_ref_designator_rec.alternate_bom_code,
2920 x_err_text => l_err_text
2921 );
2922 IF l_ref_desg_unexp_rec.bill_Sequence_id IS NULL
2923 THEN
2924 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
2925 g_Token_Tbl(1).Token_Value :=
2926 p_ref_designator_rec.component_item_name;
2927 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
2928 g_Token_Tbl(2).Token_Value :=
2929 p_ref_designator_rec.revised_item_name;
2930
2931 Error_Handler.Add_Error_Token
2932 ( p_Message_Name => 'BOM_BOM_SEQUENCE_NOT_FOUND'
2933 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2934 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2935 , p_Token_Tbl => g_Token_Tbl
2936 );
2937 l_Return_Status := FND_API.G_RET_STS_ERROR;
2938 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2939 x_ref_desg_unexp_rec := l_ref_desg_unexp_rec;
2940 x_Return_Status := l_Return_Status;
2941 x_other_message := 'BOM_CMP_UUI_SEV_ERROR';
2942 x_other_token_tbl(1).token_name := 'SUBSTITUTE_COMPONENT_
2943 NAME';
2944 x_other_token_tbl(1).token_value :=
2945 p_ref_designator_rec.component_item_name;
2946 RETURN;
2947 END IF;
2948 -- END IF; -- Comment out by MK on 12/04/00
2949 -- if ECO BO or BOM BO Ends
2950
2951 --
2952 -- Check Bill Sequence Id is found
2953 --
2954 IF l_ref_desg_unexp_rec.bill_sequence_id IS NULL
2955 THEN
2956 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
2957 g_Token_Tbl(1).Token_Value :=
2958 p_ref_designator_rec.revised_item_name;
2959 g_Token_Tbl(2).Token_Name := 'REFERENCE_DESIGNATOR_NAME';
2960 g_Token_Tbl(2).Token_Value :=
2961 p_ref_designator_rec.reference_designator_name;
2962
2963 Error_Handler.Add_Error_Token
2964 ( p_Message_Name => 'BOM_RFD_BILL_SEQ_NOT_FOUND'
2965 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2966 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2967 , p_Token_Tbl => g_Token_Tbl
2968 );
2969 l_Return_Status := FND_API.G_RET_STS_ERROR;
2970 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2971 x_ref_desg_unexp_rec := l_ref_desg_unexp_rec;
2972 --
2973 -- Set the other message
2974 --
2975 x_other_message := 'BOM_RFD_UUI_SEV_ERROR';
2976 x_other_token_tbl(1).token_name := 'REFERENCE_DESIGNATOR_NAME';
2977 x_other_token_tbl(1).token_value :=
2978 p_ref_designator_rec.reference_designator_name;
2979
2980 x_Return_Status := l_Return_Status;
2981 RETURN;
2982 END IF;
2983 /*****************************************************************
2984 --
2985 -- Convert component information to component_sequence_id
2986 --
2987 ******************************************************************/
2988
2989 l_ref_desg_unexp_rec.component_sequence_id :=
2990 Component_Sequence( p_component_item_id =>
2991 l_ref_desg_unexp_rec.component_item_id
2992 , p_operation_sequence_num =>
2993 p_ref_designator_rec.operation_sequence_number
2994 , p_effectivity_date =>
2995 p_ref_designator_rec.start_effective_date
2996 , p_bill_sequence_id =>
2997 l_ref_desg_unexp_rec.bill_sequence_id
2998 , x_err_text => l_Err_Text
2999 );
3000 IF l_ref_desg_unexp_rec.component_sequence_id IS NULL
3001 THEN
3002 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
3003 g_Token_Tbl(1).Token_Value :=
3004 p_ref_designator_rec.revised_item_name;
3005 g_Token_Tbl(2).Token_Name := 'REFERENCE_DESIGNATOR_NAME';
3006 g_Token_Tbl(2).Token_Value :=
3007 p_ref_designator_rec.reference_designator_name;
3008 g_Token_Tbl(3).Token_Name := 'REVISED_COMPONENT_NAME';
3009 g_Token_Tbl(3).Token_Value :=
3010 p_ref_designator_rec.component_item_name;
3011
3012 Error_Handler.Add_Error_Token
3013 ( p_Message_Name => 'BOM_RFD_COMP_SEQ_NOT_FOUND'
3014 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3015 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3016 , p_Token_Tbl => g_Token_Tbl
3017 );
3018 --
3019 -- Set the other message
3020 --
3021 x_other_message := 'BOM_RFD_UUI_SEV_ERROR';
3022 x_other_token_tbl(1).token_name := 'REFERENCE_DESIGNATOR_NAME';
3023 x_other_token_tbl(1).token_value :=
3024 p_ref_designator_rec.reference_designator_name;
3025
3026 l_Return_Status := FND_API.G_RET_STS_ERROR;
3027 g_Token_Tbl.Delete;
3028 END IF;
3029 END IF; -- Added by MK on 12/04/00
3030
3031 /*if bom_globals.get_debug = 'Y' then Error_Handler.write_debug('Ref Desg: Checking for editable common bill...'); END IF;
3032
3033 BEGIN
3034 SELECT 1
3035 INTO l_dummy
3036 FROM bom_bill_of_materials
3037 WHERE bill_sequence_id = source_bill_sequence_id
3038 AND bill_sequence_id = l_ref_desg_unexp_rec.bill_sequence_id;
3039 EXCEPTION
3040 WHEN NO_DATA_FOUND THEN
3041 Error_Handler.Add_Error_Token
3042 ( p_Message_Name => 'BOM_COMMON_REF_DESG'
3043 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3044 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3045 , p_Token_Tbl => g_Token_Tbl
3046 );
3047 l_Return_Status := FND_API.G_RET_STS_ERROR;
3048 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3049 x_ref_desg_unexp_rec := l_ref_desg_unexp_rec;
3050 x_Return_Status := l_Return_Status;
3051 x_other_message := 'BOM_RFD_UUI_SEV_ERROR';
3052 x_other_token_tbl(1).token_name := 'REFERENCE_DESIGNATOR_NAME';
3053 x_other_token_tbl(1).token_value := l_ref_desg_unexp_rec.bill_sequence_id;
3054 RETURN;
3055 END;
3056 */
3057 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3058 x_ref_desg_unexp_rec := l_ref_desg_unexp_rec;
3059 x_Return_Status := l_Return_Status;
3060
3061 END Ref_Designator_UUI_To_UI2;
3062
3063
3064
3065 /*
3066 ** Procedures used by BOM Business Object
3067 */
3068
3069 PROCEDURE Bom_Component_UUI_To_UI
3070 ( p_bom_component_Rec IN Bom_Bo_Pub.Bom_Comps_Rec_Type
3071 , p_bom_comp_unexp_Rec IN Bom_Bo_Pub.Bom_Comps_Unexposed_Rec_Type
3072 , x_bom_comp_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comps_Unexposed_Rec_Type
3073 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3074 , x_Return_Status IN OUT NOCOPY VARCHAR2
3075 )
3076 IS
3077 l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type;
3078 l_rev_comp_unexp_rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type;
3079 l_bom_component_rec Bom_Bo_Pub.Bom_Comps_Rec_Type;
3080 BEGIN
3081
3082 --
3083 -- Convert the BOM Record to ECO
3084 --
3085 Bom_Bo_Pub.Convert_BomComp_To_EcoComp
3086 ( p_bom_component_rec => p_bom_component_rec
3087 , p_bom_comp_unexp_rec => p_bom_comp_unexp_rec
3088 , x_rev_component_rec => l_rev_component_rec
3089 , x_rev_comp_unexp_rec => l_rev_comp_unexp_rec
3090 );
3091
3092 Bom_Val_To_Id.Rev_Component_UUI_To_UI
3093 ( p_rev_component_rec => l_rev_component_rec
3094 , p_rev_comp_unexp_rec => l_rev_comp_unexp_rec
3095 , x_rev_comp_unexp_rec => l_rev_comp_unexp_rec
3096 , x_mesg_token_tbl => x_mesg_token_tbl
3097 , x_return_status => x_return_status
3098 );
3099
3100 --
3101 -- Convert the Eco Component back to BOM
3102 --
3103 Bom_Bo_Pub.Convert_EcoComp_To_BomComp
3104 ( p_rev_component_rec => l_rev_component_rec
3105 , p_rev_comp_unexp_rec => l_rev_comp_unexp_rec
3106 , x_bom_component_rec => l_bom_component_rec
3107 , x_bom_comp_unexp_rec => x_bom_comp_unexp_rec
3108 );
3109
3110 END Bom_Component_UUI_To_UI;
3111
3112
3113 PROCEDURE Bom_Component_UUI_to_UI2
3114 ( p_Bom_component_rec IN Bom_Bo_Pub.Bom_Comps_Rec_Type
3115 , p_Bom_comp_unexp_rec IN Bom_Bo_Pub.Bom_Comps_Unexposed_Rec_Type
3116 , x_Bom_comp_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comps_Unexposed_Rec_Type
3117 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3118 , x_other_message IN OUT NOCOPY VARCHAR2
3119 , x_other_token_tbl IN OUT NOCOPY Error_Handler.Token_Tbl_Type
3120 , x_Return_Status IN OUT NOCOPY VARCHAR2
3121 )
3122 IS
3123 l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type;
3124 l_rev_comp_unexp_rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type;
3125 l_bom_component_rec Bom_Bo_Pub.Bom_Comps_Rec_Type;
3126 BEGIN
3127
3128 --
3129 -- Convert the BOM Record to ECO
3130 --
3131 Bom_Bo_Pub.Convert_BomComp_To_EcoComp
3132 ( p_bom_component_rec => p_bom_component_rec
3133 , p_bom_comp_unexp_rec => p_bom_comp_unexp_rec
3134 , x_rev_component_rec => l_rev_component_rec
3135 , x_rev_comp_unexp_rec => l_rev_comp_unexp_rec
3136 );
3137
3138 Rev_Component_UUI_To_UI2
3139 ( p_rev_component_rec => l_rev_component_rec
3140 , p_rev_comp_unexp_rec => l_rev_comp_unexp_rec
3141 , x_rev_comp_unexp_rec => l_rev_comp_unexp_rec
3142 , x_other_message => x_other_message
3143 , x_other_token_tbl => x_other_token_tbl
3144 , x_mesg_token_tbl => x_mesg_token_tbl
3145 , x_return_status => x_return_status
3146 );
3147
3148 --
3149 -- Convert the Eco Component back to BOM
3150 --
3151 Bom_Bo_Pub.Convert_EcoComp_To_BomComp
3152 ( p_rev_component_rec => l_rev_component_rec
3153 , p_rev_comp_unexp_rec => l_rev_comp_unexp_rec
3154 , x_bom_component_rec => l_bom_component_rec
3155 , x_bom_comp_unexp_rec => x_bom_comp_unexp_rec
3156 );
3157
3158 END Bom_Component_UUI_to_UI2;
3159
3160
3161 PROCEDURE Bom_Component_VID
3162 ( x_return_status IN OUT NOCOPY VARCHAR2
3163 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3164 , x_bom_comp_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comps_Unexposed_Rec_Type
3165 , p_bom_component_rec IN Bom_Bo_Pub.Bom_Comps_Rec_Type
3166 , p_bom_comp_unexp_rec IN Bom_Bo_Pub.Bom_Comps_Unexposed_Rec_Type
3167 )
3168 IS
3169 l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type;
3170 l_rev_comp_unexp_rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type;
3171 l_bom_component_rec Bom_Bo_Pub.Bom_Comps_Rec_Type;
3172 BEGIN
3173
3174 --
3175 -- Convert the BOM Record to ECO
3176 --
3177 Bom_Bo_Pub.Convert_BomComp_To_EcoComp
3178 ( p_bom_component_rec => p_bom_component_rec
3179 , p_bom_comp_unexp_rec => p_bom_comp_unexp_rec
3180 , x_rev_component_rec => l_rev_component_rec
3181 , x_rev_comp_unexp_rec => l_rev_comp_unexp_rec
3182 );
3183
3184 Rev_Component_VID
3185 ( p_rev_component_rec => l_rev_component_rec
3186 , p_rev_comp_unexp_rec => l_rev_comp_unexp_rec
3187 , x_rev_comp_unexp_rec => l_rev_comp_unexp_rec
3188 , x_mesg_token_tbl => x_mesg_token_tbl
3189 , x_return_status => x_return_status
3190 );
3191
3192 --
3193 -- Convert the Eco Component back to BOM
3194 --
3195 Bom_Bo_Pub.Convert_EcoComp_To_BomComp
3196 ( p_rev_component_rec => l_rev_component_rec
3197 , p_rev_comp_unexp_rec => l_rev_comp_unexp_rec
3198 , x_bom_component_rec => l_bom_component_rec
3199 , x_bom_comp_unexp_rec => x_bom_comp_unexp_rec
3200 );
3201
3202 END Bom_Component_VID;
3203
3204 PROCEDURE Sub_Component_UUI_To_UI
3205 ( p_bom_sub_component_rec IN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
3206 , p_bom_sub_comp_unexp_rec IN Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
3207 , x_bom_sub_Comp_unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
3208 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3209 , x_Return_Status IN OUT NOCOPY VARCHAR2
3210 )
3211 IS
3212 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
3213 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
3214 l_bom_sub_component_rec Bom_Bo_Pub.Bom_Sub_Component_Rec_Type;
3215 BEGIN
3216 --
3217 -- Convert the BOM Substitute Component to ECO
3218 --
3219
3220 Bom_Bo_Pub.Convert_BomSComp_To_EcoSComp
3221 ( p_bom_sub_component_rec => p_bom_sub_component_rec
3222 , p_bom_sub_comp_unexp_rec => p_bom_sub_comp_unexp_rec
3223 , x_sub_component_rec => l_sub_component_rec
3224 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
3225 );
3226
3227 -- Call the UUI Conversion routine
3228
3229 Sub_Component_UUI_To_UI
3230 ( p_sub_component_rec => l_sub_component_rec
3231 , p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
3232 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
3233 , x_mesg_token_tbl => x_mesg_token_tbl
3234 , x_return_Status => x_return_status
3235 );
3236
3237 -- Convert the ECO Record back to BOM
3238
3239 Bom_Bo_Pub.Convert_EcoSComp_to_BomSComp
3240 ( p_sub_component_rec => l_sub_component_rec
3241 , p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
3242 , x_bom_sub_component_rec => l_bom_sub_component_rec
3243 , x_bom_sub_comp_unexp_rec => x_bom_sub_comp_unexp_rec
3244 );
3245
3246 END Sub_Component_UUI_To_UI;
3247
3248 PROCEDURE Sub_Component_UUI_To_UI2
3249 ( p_bom_sub_component_rec IN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
3250 , p_bom_sub_comp_unexp_rec IN Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
3251 , x_bom_sub_Comp_unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
3252 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3253 , x_other_message IN OUT NOCOPY VARCHAR2
3254 , x_other_token_tbl IN OUT NOCOPY Error_Handler.Token_Tbl_Type
3255 , x_Return_Status IN OUT NOCOPY VARCHAR2
3256 )
3257 IS
3258 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
3259 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
3260 l_bom_sub_component_rec Bom_Bo_Pub.Bom_Sub_Component_Rec_Type;
3261 BEGIN
3262 --
3263 -- Convert the BOM Substitute Component to ECO
3264 --
3265
3266 Bom_Bo_Pub.Convert_BomSComp_To_EcoSComp
3267 ( p_bom_sub_component_rec => p_bom_sub_component_rec
3268 , p_bom_sub_comp_unexp_rec => p_bom_sub_comp_unexp_rec
3269 , x_sub_component_rec => l_sub_component_rec
3270 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
3271 );
3272
3273 -- Call the UUI Conversion routine
3274
3275 Sub_Component_UUI_To_UI2
3276 ( p_sub_component_rec => l_sub_component_rec
3277 , p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
3278 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
3279 , x_other_message => x_other_message
3280 , x_other_token_tbl => x_other_token_tbl
3281 , x_mesg_token_tbl => x_mesg_token_tbl
3282 , x_return_Status => x_return_status
3283 );
3284
3285 -- Convert the ECO Record back to BOM
3286
3287 Bom_Bo_Pub.Convert_EcoSComp_to_BomSComp
3288 ( p_sub_component_rec => l_sub_component_rec
3289 , p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
3290 , x_bom_sub_component_rec => l_bom_sub_component_rec
3291 , x_bom_sub_comp_unexp_rec => x_bom_sub_comp_unexp_rec
3292 );
3293
3294 END Sub_Component_UUI_To_UI2;
3295
3296
3297 PROCEDURE Ref_Designator_UUI_To_UI
3298 ( p_bom_ref_designator_rec IN Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type
3299 , p_bom_ref_desg_unexp_rec IN Bom_Bo_Pub.Bom_Ref_Desg_Unexp_Rec_Type
3300 , x_bom_ref_desg_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Ref_Desg_Unexp_Rec_Type
3301 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3302 , x_Return_Status IN OUT NOCOPY VARCHAR2
3303 )
3304 IS
3305 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_Rec_Type;
3306 l_ref_desg_unexp_rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type;
3307 l_bom_ref_designator_rec Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type;
3308 BEGIN
3309 --
3310 -- Convert the BOM reference designator record to ECO
3311 --
3312 Bom_Bo_Pub.Convert_BomDesg_To_EcoDesg
3313 ( p_bom_ref_designator_rec => p_bom_ref_designator_rec
3314 , p_bom_ref_desg_unexp_rec => p_bom_ref_desg_unexp_rec
3315 , x_ref_designator_rec => l_ref_designator_rec
3316 , x_ref_desg_unexp_rec => l_ref_desg_unexp_rec
3317 );
3318
3319 -- Call the Ref. Designator UUI Conversion
3320
3321 Ref_Designator_UUI_To_UI
3322 ( p_ref_designator_rec => l_ref_designator_rec
3323 , p_ref_desg_unexp_rec => l_ref_desg_unexp_rec
3324 , x_ref_desg_unexp_rec => l_ref_desg_unexp_rec
3325 , x_mesg_token_tbl => x_mesg_token_tbl
3326 , x_return_status => x_return_status
3327 );
3328
3329 -- Convert the ECO Reference Designator back to BOM
3330
3331 Bom_Bo_Pub.Convert_EcoDesg_To_BomDesg
3332 ( p_ref_designator_rec => l_ref_designator_rec
3333 , p_ref_desg_unexp_rec => l_ref_desg_unexp_rec
3334 , x_bom_ref_designator_rec => l_bom_ref_designator_rec
3335 , x_bom_ref_desg_unexp_rec => x_bom_ref_desg_unexp_rec
3336 );
3337
3338 END Ref_Designator_UUI_To_UI;
3339
3340 PROCEDURE Ref_Designator_UUI_To_UI2
3341 ( p_Bom_ref_designator_rec IN Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type
3342 , p_Bom_ref_desg_unexp_rec IN Bom_Bo_Pub.Bom_Ref_Desg_Unexp_Rec_Type
3343 , x_Bom_ref_desg_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Ref_Desg_Unexp_Rec_Type
3344 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3345 , x_other_message IN OUT NOCOPY VARCHAR2
3346 , x_other_token_tbl IN OUT NOCOPY Error_Handler.Token_Tbl_Type
3347 , x_Return_Status IN OUT NOCOPY VARCHAR2
3348 )
3349 IS
3350 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_Rec_Type;
3351 l_ref_desg_unexp_rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type;
3352 l_bom_ref_designator_rec Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type;
3353 BEGIN
3354 --
3355 -- Convert the BOM reference designator record to ECO
3356 --
3357 Bom_Bo_Pub.Convert_BomDesg_To_EcoDesg
3358 ( p_bom_ref_designator_rec => p_bom_ref_designator_rec
3359 , p_bom_ref_desg_unexp_rec => p_bom_ref_desg_unexp_rec
3360 , x_ref_designator_rec => l_ref_designator_rec
3361 , x_ref_desg_unexp_rec => l_ref_desg_unexp_rec
3362 );
3363
3364 -- Call the Ref. Designator UUI Conversion
3365
3366 Ref_Designator_UUI_To_UI2
3367 ( p_ref_designator_rec => l_ref_designator_rec
3368 , p_ref_desg_unexp_rec => l_ref_desg_unexp_rec
3369 , x_ref_desg_unexp_rec => l_ref_desg_unexp_rec
3370 , x_other_message => x_other_message
3371 , x_other_token_tbl => x_other_token_tbl
3372 , x_mesg_token_tbl => x_mesg_token_tbl
3373 , x_return_status => x_return_status
3374 );
3375
3376 -- Convert the ECO Reference Designator back to BOM
3377
3378 Bom_Bo_Pub.Convert_EcoDesg_To_BomDesg
3379 ( p_ref_designator_rec => l_ref_designator_rec
3380 , p_ref_desg_unexp_rec => l_ref_desg_unexp_rec
3381 , x_bom_ref_designator_rec => l_bom_ref_designator_rec
3382 , x_bom_ref_desg_unexp_rec => x_bom_ref_desg_unexp_rec
3383 );
3384
3385
3386 END Ref_Designator_UUI_To_UI2;
3387
3388 /***************************************************************
3389 * Procedure : Bom_Comp_Operation_UUI_To_UI
3390 * Parameters IN : Component_Operation exposed column record
3391 * Component Operation unexposed column record
3392 * Parameters OUT: Component Operation unxposed column record
3393 * Mesg Token Tbl
3394 * Return Status
3395 * Purpose : This procedure will convert user unique idx columns
3396 * into unique id columns.
3397 ********************************************************************/
3398 PROCEDURE Bom_Comp_Operation_UUI_To_UI
3399 ( p_bom_comp_ops_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
3400 , p_bom_comp_ops_unexp_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
3401 , x_bom_comp_ops_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
3402 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3403 , x_Return_Status IN OUT NOCOPY VARCHAR2
3404 )
3405 IS
3406 l_bom_comp_ops_unexp_rec Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type;
3407 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3408 l_Err_Text VARCHAR2(2000);
3409 l_Return_Status VARCHAR2(1);
3410 l_token_tbl Error_Handler.Token_Tbl_Type;
3411 BEGIN
3412
3413 l_return_status := FND_API.G_RET_STS_SUCCESS;
3414
3415 /****************************************************************
3416 --
3417 -- Convert assembly item name to assembly item ID
3418 --
3419 ******************************************************************/
3420
3421 g_Token_Tbl(1).Token_Name := 'REV_ITEM';
3422 g_Token_Tbl(1).Token_Value := p_bom_comp_ops_rec.assembly_item_name;
3423 l_bom_comp_ops_unexp_rec := p_bom_comp_ops_unexp_rec;
3424
3425 /*************************************************************
3426 --
3427 -- Verify that the component ooperation unique key columns are
3428 -- not null
3429 --
3430 ****************************************************************/
3431 IF p_bom_comp_ops_rec.operation_sequence_number IS NULL OR
3432 p_bom_comp_ops_rec.operation_sequence_number = FND_API.G_MISS_NUM
3433 THEN
3434 Error_Handler.Add_Error_Token
3435 ( p_Message_Name => 'BOM_COPS_OPSEQ_KEYCOL_NULL'
3436 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3437 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3438 );
3439 l_Return_Status := FND_API.G_RET_STS_ERROR;
3440
3441 END IF;
3442
3443 IF p_bom_comp_ops_rec.additional_operation_seq_num IS NULL OR
3444 p_bom_comp_ops_rec.additional_operation_seq_num = FND_API.G_MISS_NUM
3445 THEN
3446 Error_Handler.Add_Error_Token
3447 ( p_Message_Name => 'BOM_COPS_AOPSEQ_KEYCOL_NULL'
3448 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3449 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3450 );
3451 l_Return_Status := FND_API.G_RET_STS_ERROR;
3452
3453 END IF;
3454
3455 IF p_bom_comp_ops_rec.component_item_name IS NULL OR
3456 p_bom_comp_ops_rec.component_item_name = FND_API.G_MISS_CHAR
3457 THEN
3458 Error_Handler.Add_Error_Token
3459 ( p_Message_Name => 'BOM_COPS_COMP_KEYCOL_NULL'
3460 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3461 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3462 );
3463 l_Return_Status := FND_API.G_RET_STS_ERROR;
3464
3465 END IF;
3466
3467 IF p_bom_comp_ops_rec.assembly_item_name IS NULL OR
3468 p_bom_comp_ops_rec.assembly_item_name = FND_API.G_MISS_CHAR
3469 THEN
3470 Error_Handler.Add_Error_Token
3471 ( p_Message_Name => 'BOM_COPS_ITEM_KEYCOL_NULL'
3472 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3473 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3474 );
3475 l_Return_Status := FND_API.G_RET_STS_ERROR;
3476
3477 END IF;
3478
3479 IF p_bom_comp_ops_rec.organization_code IS NULL OR
3480 p_bom_comp_ops_rec.organization_code = FND_API.G_MISS_CHAR
3481 THEN
3482 Error_Handler.Add_Error_Token
3483 ( p_Message_Name => 'BOM_COPS_ORG_KEYCOL_NULL'
3484 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3485 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3486 );
3487 l_Return_Status := FND_API.G_RET_STS_ERROR;
3488
3489 END IF;
3490
3491 IF p_bom_comp_ops_rec.start_effective_date IS NULL OR
3492 p_bom_comp_ops_rec.start_effective_date = FND_API.G_MISS_DATE
3493 THEN
3494 Error_Handler.Add_Error_Token
3495 ( p_Message_Name => 'BOM_COPS_DATE_KEYCOL_NULL'
3496 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3497 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3498 );
3499 l_Return_Status := FND_API.G_RET_STS_ERROR;
3500 END IF;
3501
3502 --
3503 -- If key columns are NULL then return
3504 --
3505 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3506 THEN
3507 x_return_status := l_return_status;
3508 x_mesg_token_tbl := l_mesg_token_tbl;
3509 x_bom_comp_ops_unexp_rec := l_bom_comp_ops_unexp_rec;
3510 RETURN;
3511 END IF;
3512
3513 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3514 x_bom_comp_ops_unexp_rec := l_bom_comp_ops_unexp_rec;
3515 x_Return_Status := l_Return_Status;
3516
3517
3518 END Bom_Comp_Operation_UUI_To_UI;
3519
3520 /****************************************************************
3521 * Procedure : Bom_Comp_Operation_UUI_To_UI2
3522 * Purpose : This procedure is similar to the UUI-UI. The calling
3523 * program can decide on the scope of the error if the
3524 * conversion in this procedure fails.
3525 ******************************************************************/
3526 PROCEDURE Bom_Comp_Operation_UUI_To_UI2
3527 ( p_bom_comp_ops_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
3528 , p_bom_comp_ops_unexp_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
3529 , x_bom_comp_ops_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
3530 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3531 , x_other_message IN OUT NOCOPY VARCHAR2
3532 , x_other_token_tbl IN OUT NOCOPY Error_Handler.Token_Tbl_Type
3533 , x_Return_Status IN OUT NOCOPY VARCHAR2
3534 )
3535 IS
3536 l_bom_comp_ops_unexp_rec Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type :=
3537 p_bom_comp_ops_unexp_rec;
3538 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3539 l_Err_Text VARCHAR2(2000);
3540 l_Return_Status VARCHAR2(1);
3541 l_dummy NUMBER;
3542 BEGIN
3543
3544
3545 l_bom_comp_ops_unexp_rec.assembly_item_id :=
3546 Revised_Item( p_revised_item_num =>
3547 p_bom_comp_ops_rec.assembly_item_name
3548 , p_organization_id =>
3549 p_bom_comp_ops_unexp_rec.organization_id
3550 , x_err_text => l_err_text
3551 );
3552
3553 IF l_bom_comp_ops_unexp_rec.assembly_item_id IS NULL
3554 THEN
3555 g_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
3556 g_token_tbl(1).token_value :=
3557 p_bom_comp_ops_rec.assembly_item_name;
3558 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
3559 g_token_tbl(2).token_value :=
3560 p_bom_comp_ops_rec.organization_code;
3561
3562 Error_Handler.Add_Error_Token
3563 ( p_Message_Name => 'ENG_REVISED_ITEM_INVALID'
3564 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3565 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3566 , p_Token_Tbl => g_Token_Tbl
3567 );
3568 g_token_tbl.delete(2);
3569
3570 l_Return_Status := FND_API.G_RET_STS_ERROR;
3571 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3572 x_bom_comp_ops_unexp_rec := l_bom_comp_ops_unexp_rec;
3573 --
3574 -- Set the other message
3575 --
3576 x_other_message := 'BOM_COPS_UUI2_SEV_ERROR';
3577 x_other_token_tbl(1).token_name := 'COMPONENT_ITEM_NAME';
3578 x_other_token_tbl(1).token_value :=
3579 p_bom_comp_ops_rec.component_item_name;
3580 x_other_token_tbl(2).token_name := 'OPERATION_SEQ_NUM';
3581 x_other_token_tbl(2).token_value :=
3582 p_bom_comp_ops_rec.additional_operation_seq_num;
3583
3584 x_Return_Status := l_Return_Status;
3585 RETURN;
3586 END IF;
3587
3588 /***********************************************************
3589 --
3590 -- Convert Component Item Name to Component Item ID
3591 --
3592 ************************************************************/
3593 l_bom_comp_ops_unexp_rec.component_item_id :=
3594 Component_Item( p_organization_id =>
3595 l_bom_comp_ops_unexp_rec.organization_id
3596 , p_component_item_num =>
3597 p_bom_comp_ops_rec.component_item_name
3598 , x_err_text => l_err_text
3599 );
3600
3601 IF l_bom_comp_ops_unexp_rec.component_item_id IS NULL
3602 THEN
3603
3604 g_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
3605 g_token_tbl(1).token_value :=
3606 p_bom_comp_ops_rec.component_item_name;
3607 g_token_tbl(2).token_name := 'ORGANIZATION_CODE';
3608 g_token_tbl(2).token_value :=
3609 p_bom_comp_ops_rec.organization_code;
3610
3611 Error_Handler.Add_Error_Token
3612 ( p_Message_Name => 'BOM_REVISED_COMP_INVALID'
3613 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3614 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3615 , p_Token_Tbl => g_Token_Tbl
3616 );
3617
3618 l_Return_Status := FND_API.G_RET_STS_ERROR;
3619 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3620 x_bom_comp_ops_unexp_rec := l_bom_comp_ops_unexp_rec;
3621 --
3622 -- Set the other message
3623 --
3624 x_other_message := 'BOM_COPS_UUI2_SEV_ERROR';
3625 x_other_token_tbl(1).token_name := 'COMPONENT_ITEM_NAME';
3626 x_other_token_tbl(1).token_value :=
3627 p_bom_comp_ops_rec.component_item_name;
3628 x_other_token_tbl(2).token_name := 'OPERATION_SEQ_NUM';
3629 x_other_token_tbl(2).token_value :=
3630 p_bom_comp_ops_rec.additional_operation_seq_num;
3631
3632 x_Return_Status := l_Return_Status;
3633 RETURN;
3634 END IF;
3635
3636 /****************************************************************
3637 --
3638 -- Convert revised item information to bill_sequence_id
3639 --
3640 *****************************************************************/
3641 --
3642 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Getting bill_seq f
3643 or assembly item ' || l_bom_comp_ops_unexp_rec.assembly_item_id); END IF;
3644
3645 l_bom_comp_ops_unexp_rec.bill_sequence_id :=
3646 bill_sequence_id(p_assembly_item_id =>
3647 l_bom_comp_ops_unexp_rec.assembly_item_id,
3648 p_organization_id =>
3649 l_bom_comp_ops_unexp_rec.organization_id,
3650 p_alternate_bom_code =>
3651 p_bom_comp_ops_rec.alternate_bom_code,
3652 x_err_text => l_err_text
3653 );
3654
3655 IF l_bom_comp_ops_unexp_rec.bill_Sequence_id IS NULL
3656 THEN
3657 g_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
3658 g_Token_Tbl(1).Token_Value :=
3659 p_bom_comp_ops_rec.component_item_name;
3660 g_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
3661 g_Token_Tbl(2).Token_Value :=
3662 p_bom_comp_ops_rec.assembly_item_name;
3663
3664 Error_Handler.Add_Error_Token
3665 ( p_Message_Name => 'BOM_BOM_SEQUENCE_NOT_FOUND'
3666 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3667 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3668 , p_Token_Tbl => g_Token_Tbl
3669 );
3670
3671 l_Return_Status := FND_API.G_RET_STS_ERROR;
3672 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3673 x_bom_comp_ops_unexp_rec := l_bom_comp_ops_unexp_rec;
3674
3675 x_other_message := 'BOM_COPS_UUI2_SEV_ERROR';
3676 x_other_token_tbl(1).token_name := 'COMPONENT_ITEM_NAME';
3677 x_other_token_tbl(1).token_value :=
3678 p_bom_comp_ops_rec.component_item_name;
3679 x_other_token_tbl(2).token_name := 'OPERATION_SEQ_NUM';
3680 x_other_token_tbl(2).token_value :=
3681 p_bom_comp_ops_rec.additional_operation_seq_num;
3682 x_Return_Status := l_Return_Status;
3683 RETURN;
3684 END IF;
3685
3686 /*if bom_globals.get_debug = 'Y' then Error_Handler.write_debug('Comp OP: Checking for editable common bill...'); END IF;
3687
3688 BEGIN
3689 SELECT 1
3690 INTO l_dummy
3691 FROM bom_bill_of_materials
3692 WHERE bill_sequence_id = source_bill_sequence_id
3693 AND bill_sequence_id = l_bom_comp_ops_unexp_rec.bill_Sequence_id;
3694 EXCEPTION
3695 WHEN NO_DATA_FOUND THEN
3696 Error_Handler.Add_Error_Token
3697 ( p_Message_Name => 'BOM_COMMON_COMP_OP'
3698 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3699 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3700 , p_Token_Tbl => g_Token_Tbl
3701 );
3702 l_Return_Status := FND_API.G_RET_STS_ERROR;
3703 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3704 x_bom_comp_ops_unexp_rec := l_bom_comp_ops_unexp_rec;
3705 x_Return_Status := l_Return_Status;
3706 x_other_message := 'BOM_COPS_UUI2_SEV_ERROR';
3707 x_other_token_tbl(1).token_name := 'COMPONENT_ITEM_NAME';
3708 x_other_token_tbl(1).token_value :=
3709 p_bom_comp_ops_rec.component_item_name;
3710 x_other_token_tbl(2).token_name := 'OPERATION_SEQ_NUM';
3711 x_other_token_tbl(2).token_value := p_bom_comp_ops_rec.additional_operation_seq_num;
3712 RETURN;
3713 END;
3714 */
3715 /*****************************************************************
3716 --
3717 -- Convert component information to component_sequence_id
3718 --
3719 ******************************************************************/
3720
3721 l_bom_comp_ops_unexp_rec.component_sequence_id :=
3722 Component_Sequence( p_component_item_id =>
3723 l_bom_comp_ops_unexp_rec.component_item_id
3724 , p_operation_sequence_num =>
3725 p_bom_comp_ops_rec.operation_sequence_number
3726 , p_effectivity_date =>
3727 p_bom_comp_ops_rec.start_effective_date
3728 , p_bill_sequence_id =>
3729 l_bom_comp_ops_unexp_rec.bill_sequence_id
3730 , p_from_unit_number =>
3731 p_bom_comp_ops_rec.from_end_item_unit_number
3732 , x_err_text => l_Err_Text
3733 );
3734 IF l_bom_comp_ops_unexp_rec.component_sequence_id IS NULL
3735 THEN
3736 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
3737 g_Token_Tbl(1).Token_Value :=
3738 p_bom_comp_ops_rec.assembly_item_name;
3739 g_Token_Tbl(2).Token_Name := 'OPERATION_SEQUENCE_NUMBER';
3740 g_Token_Tbl(2).Token_Value :=
3741 p_bom_comp_ops_rec.additional_operation_seq_num;
3742
3743 Error_Handler.Add_Error_Token
3744 ( p_Message_Name => 'BOM_COPS_COMP_SEQ_NOT_FOUND'
3745 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3746 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3747 , p_Token_Tbl => g_Token_Tbl
3748 );
3749
3750 l_Return_Status := FND_API.G_RET_STS_ERROR;
3751 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3752 x_bom_comp_ops_unexp_rec := l_bom_comp_ops_unexp_rec;
3753
3754 --
3755 -- Set the other message
3756 --
3757 x_other_message := 'BOM_COPS_UUI2_SEV_ERROR';
3758 x_other_token_tbl(1).token_name := 'COMPONENT_ITEM_NAME';
3759 x_other_token_tbl(1).token_value :=
3760 p_bom_comp_ops_rec.component_item_name;
3761 x_other_token_tbl(2).token_name := 'COMP_OPERATION_SEQ_NUM';
3762 x_other_token_tbl(2).token_value :=
3763 p_bom_comp_ops_rec.operation_sequence_number;
3764
3765 g_Token_Tbl.Delete;
3766 x_Return_Status := l_Return_Status;
3767 RETURN;
3768 END IF;
3769
3770 /*****************************************************************
3771 --
3772 -- Convert operation_seq_num information to operation_sequence_id
3773 --
3774 ******************************************************************/
3775
3776 l_bom_comp_ops_unexp_rec.additional_operation_seq_id :=
3777 Operation_Sequence_Id( p_organization_id =>
3778 l_bom_comp_ops_unexp_rec.organization_id
3779 , p_assembly_item_id =>
3780 l_bom_comp_ops_unexp_rec.assembly_item_id
3781 , p_alternate_bom_designator =>
3782 p_bom_comp_ops_rec.alternate_bom_code
3783 , p_operation_sequence_number =>
3784 p_bom_comp_ops_rec.additional_operation_seq_num
3785 , x_err_text => l_Err_Text
3786 );
3787 IF l_bom_comp_ops_unexp_rec.additional_operation_seq_id IS NULL
3788 THEN
3789 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
3790 g_Token_Tbl(1).Token_Value :=
3791 p_bom_comp_ops_rec.assembly_item_name;
3792 g_Token_Tbl(2).Token_Name := 'OPERATION_SEQUENCE_NUMBER';
3793 g_Token_Tbl(2).Token_Value :=
3794 p_bom_comp_ops_rec.additional_operation_seq_num;
3795
3796 Error_Handler.Add_Error_Token
3797 ( p_Message_Name => 'BOM_COPS_OPSEQID_NOT_FOUND'
3798 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3799 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3800 , p_Token_Tbl => g_Token_Tbl
3801 );
3802
3803
3804 l_Return_Status := FND_API.G_RET_STS_ERROR;
3805 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3806 x_bom_comp_ops_unexp_rec := l_bom_comp_ops_unexp_rec;
3807
3808 --
3809 -- Set the other message
3810 --
3811 x_other_message := 'BOM_COPS_UUI2_SEV_ERROR';
3812 x_other_token_tbl(1).token_name := 'COMPONENT_ITEM_NAME';
3813 x_other_token_tbl(1).token_value :=
3814 p_bom_comp_ops_rec.component_item_name;
3815 x_other_token_tbl(2).token_name := 'OPERATION_SEQ_NUM';
3816 x_other_token_tbl(2).token_value :=
3817 p_bom_comp_ops_rec.additional_operation_seq_num;
3818
3819 g_Token_Tbl.Delete;
3820 x_Return_Status := l_Return_Status;
3821
3822 RETURN;
3823 END IF;
3824
3825
3826 --bug 8850425 fix begin
3827 /*****************************************************************
3828 --
3829 -- Convert additional_operation_seq_num information to comp_operation_seq_id
3830 --
3831 ******************************************************************/
3832 IF(p_bom_comp_ops_rec.transaction_type IN (BOM_globals.G_OPR_UPDATE,
3833 BOM_globals.G_OPR_DELETE)) THEN
3834 l_bom_comp_ops_unexp_rec.comp_operation_seq_id :=
3835 Comp_Operation_Seq_Id( p_component_sequence_id =>
3836 l_bom_comp_ops_unexp_rec.component_sequence_id
3837 , p_operation_sequence_number =>
3838 p_bom_comp_ops_rec.additional_operation_seq_num
3839 );
3840
3841 IF l_bom_comp_ops_unexp_rec.comp_operation_seq_id IS NULL
3842 THEN
3843 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
3844 g_Token_Tbl(1).Token_Value :=
3845 p_bom_comp_ops_rec.assembly_item_name;
3846 g_Token_Tbl(2).Token_Name := 'OPERATION_SEQUENCE_NUMBER';
3847 g_Token_Tbl(2).Token_Value :=
3848 p_bom_comp_ops_rec.additional_operation_seq_num;
3849
3850 Error_Handler.Add_Error_Token
3851 ( p_Message_Name => 'BOM_COPS_OPSEQID_NOT_FOUND'
3852 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3853 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3854 , p_Token_Tbl => g_Token_Tbl
3855 );
3856
3857
3858 l_Return_Status := FND_API.G_RET_STS_ERROR;
3859 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3860 x_bom_comp_ops_unexp_rec := l_bom_comp_ops_unexp_rec;
3861
3862 --
3863 -- Set the other message
3864 --
3865 x_other_message := 'BOM_COPS_UUI2_SEV_ERROR';
3866 x_other_token_tbl(1).token_name := 'COMPONENT_ITEM_NAME';
3867 x_other_token_tbl(1).token_value := p_bom_comp_ops_rec.component_item_name;
3868 x_other_token_tbl(2).token_name := 'OPERATION_SEQ_NUM';
3869 x_other_token_tbl(2).token_value :=
3870 p_bom_comp_ops_rec.additional_operation_seq_num;
3871
3872 g_Token_Tbl.Delete;
3873 x_Return_Status := l_Return_Status;
3874
3875 RETURN;
3876 END IF;
3877 END IF;
3878
3879 --bug 8850425 fix end
3880
3881
3882 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3883 x_bom_comp_ops_unexp_rec := l_bom_comp_ops_unexp_rec;
3884 x_Return_Status := l_Return_Status;
3885
3886 /*****************************************************************
3887 --
3888 -- Convert new_additional_operation_seq_num information to operation_sequence_id
3889 --
3890 ******************************************************************/
3891 If(p_bom_comp_ops_rec.transaction_type = BOM_globals.G_OPR_UPDATE and
3892 p_bom_comp_ops_rec.new_additional_op_seq_num is not null and
3893 p_bom_comp_ops_rec.new_additional_op_seq_num <> FND_API.G_MISS_NUM) then
3894
3895 l_bom_comp_ops_unexp_rec.new_additional_op_seq_id :=
3896 Operation_Sequence_Id( p_organization_id =>
3897 l_bom_comp_ops_unexp_rec.organization_id
3898 , p_assembly_item_id =>
3899 l_bom_comp_ops_unexp_rec.assembly_item_id
3900 , p_alternate_bom_designator =>
3901 p_bom_comp_ops_rec.alternate_bom_code
3902 , p_operation_sequence_number =>
3903 p_bom_comp_ops_rec.new_additional_op_seq_num
3904 , x_err_text => l_Err_Text
3905 );
3906 IF l_bom_comp_ops_unexp_rec.new_additional_op_seq_id IS NULL
3907 THEN
3908 g_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
3909 g_Token_Tbl(1).Token_Value :=
3910 p_bom_comp_ops_rec.assembly_item_name;
3911 g_Token_Tbl(2).Token_Name := 'OPERATION_SEQUENCE_NUMBER';
3912 g_Token_Tbl(2).Token_Value :=
3913 p_bom_comp_ops_rec.new_additional_op_seq_num;
3914
3915 Error_Handler.Add_Error_Token
3916 ( p_Message_Name => 'BOM_COPS_OPSEQID_NOT_FOUND'
3917 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3918 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3919 , p_Token_Tbl => g_Token_Tbl
3920 );
3921
3922
3923 l_Return_Status := FND_API.G_RET_STS_ERROR;
3924 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3925 x_bom_comp_ops_unexp_rec := l_bom_comp_ops_unexp_rec;
3926
3927 --
3928 -- Set the other message
3929 --
3930 x_other_message := 'BOM_COPS_UUI2_SEV_ERROR';
3931 x_other_token_tbl(1).token_name := 'COMPONENT_ITEM_NAME';
3932 x_other_token_tbl(1).token_value :=
3933 p_bom_comp_ops_rec.component_item_name;
3934 x_other_token_tbl(2).token_name := 'OPERATION_SEQ_NUM';
3935 x_other_token_tbl(2).token_value :=
3936 p_bom_comp_ops_rec.additional_operation_seq_num;
3937
3938 g_Token_Tbl.Delete;
3939 x_Return_Status := l_Return_Status;
3940
3941 RETURN;
3942 END IF;
3943 End If;
3944 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3945 x_bom_comp_ops_unexp_rec := l_bom_comp_ops_unexp_rec;
3946 x_Return_Status := l_Return_Status;
3947
3948 END Bom_Comp_Operation_UUI_To_UI2;
3949
3950 function parse_item_name (org_id IN Number,
3951 item_name IN Varchar2,
3952 id OUT NOCOPY Number,
3953 err_text out NOCOPY Varchar2) return Number
3954 is
3955 Begin
3956
3957 select inventory_item_id into id from mtl_system_items_kfv
3958 where concatenated_segments = item_name
3959 and organization_id =org_id;
3960 return 0;
3961 exception
3962 when others then
3963 err_text := substrb(sqlerrm,1000);
3964 return 1;
3965 end;
3966
3967
3968 END BOM_Val_To_Id;