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