[Home] [Help]
PACKAGE BODY: APPS.BOM_VALIDATE_COMP_OPERATION
Source
1 PACKAGE BODY BOM_Validate_Comp_Operation AS
2 /* $Header: BOMLCOPB.pls 120.6.12010000.2 2010/02/03 17:06:40 umajumde ship $ */
3 /**********************************************************************
4 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
5 -- All rights reserved.
6 --
7 -- FILENAME
8 --
9 -- BOMLCOPB.pls
10 --
11 -- DESCRIPTION
12 --
13 -- Body of package BOM_Validate_Comp_Operation
14 --
15 -- NOTES
16 --
17 -- HISTORY
18 --
19 -- 27-AUG-2001 Refai Farook Initial Creation
20 --
21 --
22 **************************************************************************/
23 G_PKG_NAME CONSTANT VARCHAR2(30) := 'BOM_Validate_Comp_Operation';
24 ret_code NUMBER;
25 l_dummy VARCHAR2(80);
26
27
28
29 /*******************************************************************
30 * Function : Check_Overlap_Dates
31 * Parameter IN : Effectivity Date
32 * Disable Date
33 * Bill Sequence Id
34 * Component Item Id
35 * Return : True if dates are overlapping else false.
36 * Purpose : The function will check if the same component is
37 * entered with overlapping dates. Components with
38 * overlapping dates will get an error.
39 ******************************************************************/
40 FUNCTION Check_Overlap_Dates
41 ( p_Effectivity_Date DATE,
42 p_Disable_Date DATE,
43 p_Component_Item_Id NUMBER,
44 p_Bill_Sequence_Id NUMBER,
45 p_component_sequence_id IN NUMBER := NULL,
46 p_comp_operation_seq_id IN NUMBER := NULL,
47 p_Rowid VARCHAR2 := NULL,
48 p_Operation_Seq_Num NUMBER,
49 p_entity VARCHAR2 := 'COPS')
50 RETURN BOOLEAN
51 IS
52 l_Count NUMBER := 0;
53 CURSOR All_Dates IS
54 SELECT 'X' date_available FROM sys.dual
55 WHERE EXISTS (
56 SELECT 1 from BOM_Component_All_Operations_V
57 WHERE Component_Item_Id = p_Component_Item_Id
58 AND Bill_Sequence_Id = p_Bill_Sequence_Id
59 AND Operation_Seq_Num = p_Operation_Seq_Num
60 /* AND
61 (
62 ( p_entity = 'COPS'
63 AND
64 (p_comp_operation_seq_id IS NULL
65 OR
66 p_comp_operation_seq_id = FND_API.G_MISS_NUM
67 OR
68 comp_operation_seq_id <> p_comp_operation_seq_id)
69 )
70 OR
71 ( p_entity = 'RC'
72 AND
73 (p_component_sequence_id IS NULL
74 OR
75 p_component_sequence_id = FND_API.G_MISS_NUM
76 OR
77 comp_operation_seq_id <> 0 -- row belongs to comp ops
78 OR
79 component_sequence_id <> p_component_sequence_id)
80 )
81 )
82 */
83
84 /* AND
85 (
86 p_RowId IS NULL
87 or
88 p_Rowid = FND_API.G_MISS_CHAR
89 or
90 ( decode(p_entity,'COPS',bco_rowid,
91 'RC',bic_RowId,' ') <> p_RowID )
92 )
93 */
94 AND
95 (
96 p_RowId IS NULL
97 or
98 p_Rowid = FND_API.G_MISS_CHAR
99 or
100 row_id <> p_Rowid)
101 AND ( p_Disable_Date IS NULL
102 OR ( to_char(p_Disable_Date,'YYYY/MM/DD HH24:MI:SS') > to_char(Effectivity_Date,'YYYY/MM/DD HH24:MI:SS'))) -- 5954279
103 AND ( to_char(p_Effectivity_Date,'YYYY/MM/DD HH24:MI:SS') < to_char(Disable_Date,'YYYY/MM/DD HH24:MI:SS') -- 5954279
104 OR Disable_Date IS NULL
105 )
106 AND implementation_date IS NOT NULL -- Bug 3182080
107 );
108 BEGIN
109
110 FOR l_Date IN All_Dates LOOP
111 l_Count := l_Count + 1;
112 END LOOP;
113
114 -- If count <> 0 that means the current date is overlapping with
115 -- some record.
116 IF l_Count <> 0 THEN
117 RETURN TRUE;
118 ELSE
119 RETURN FALSE;
120 END IF;
121
122 END Check_Overlap_Dates;
123
124
125 /*******************************************************************
126 * Function : Check_Overlap_Numbers
127 * Parameter IN: from end item unit number
128 * to end item unit number
129 * Bill Sequence Id
130 * Component Item Id
131 * Return : True if unit numbers are overlapping, else false.
132 * Purpose : The function will check if the same component is entered
133 * with overlapping unit numbers. Components with
134 * overlapping unit numbers will get an error.
135 *********************************************************************/
136 FUNCTION Check_Overlap_Numbers
137 ( p_From_End_Item_Number VARCHAR2
138 , p_To_End_Item_Number VARCHAR2
139 , p_Component_Item_Id NUMBER
140 , p_Bill_Sequence_Id NUMBER
141 , p_component_sequence_id IN NUMBER := NULL
142 , p_comp_operation_seq_id IN NUMBER := NULL
143 , p_Rowid VARCHAR2 := NULL
144 , p_Operation_Seq_Num NUMBER
145 , p_entity VARCHAR2 := 'COPS')
146 RETURN BOOLEAN
147 IS
148 l_Count NUMBER := 0;
149 CURSOR All_Numbers_BIC IS
150 SELECT 'X' unit_available FROM sys.dual
151 WHERE EXISTS (
152 SELECT 1 from BOM_INVENTORY_COMPONENTS
153 WHERE Component_Item_Id = p_Component_Item_Id
154 AND Bill_Sequence_Id = p_Bill_Sequence_Id
155 AND Operation_Seq_Num = p_Operation_Seq_Num
156 /* AND
157 (
158 ( p_entity = 'COPS'
159 AND
160 (p_comp_operation_seq_id IS NULL
161 OR
162 p_comp_operation_seq_id = FND_API.G_MISS_NUM
163 OR
164 comp_operation_seq_id <> p_comp_operation_seq_id)
165 )
166 OR
167 ( p_entity = 'RC'
168 AND
169 (p_component_sequence_id IS NULL
170 OR
171 p_component_sequence_id = FND_API.G_MISS_NUM
172 OR
173 comp_operation_seq_id <> 0
174 OR
175 component_sequence_id <> p_component_sequence_id)
176 )
177 ) */
178
179 /*
180 AND
181 (
182 p_RowId IS NULL
183 or
184 p_Rowid = FND_API.G_MISS_CHAR
185 or
186 ( decode(p_entity,'COPS',bco_rowid,
187 'RC',bic_RowId,' ') <> p_RowID )
188 )
189 */
190 AND
191 (
192 p_RowId IS NULL
193 or
194 p_Rowid = FND_API.G_MISS_CHAR
195 or
196 rowid <> p_Rowid)
197 AND (p_To_End_Item_Number IS NULL
198 OR p_To_End_Item_Number >=
199 From_End_Item_Unit_Number)
200 AND (p_From_End_Item_Number <=
201 To_End_Item_Unit_Number
202 OR To_End_Item_Unit_Number IS NULL
203 )
204 AND ( IMPLEMENTATION_DATE IS NOT NULL )
205 AND ( DISABLE_DATE IS NULL ) --bug:5347036 Consider enabled components only
206 );
207
208 CURSOR All_Numbers_BCO IS
209 SELECT 'X' unit_available FROM sys.dual
210 WHERE EXISTS (
211 SELECT 1 from BOM_COMPONENT_OPERATIONS BCO,
212 BOM_INVENTORY_COMPONENTS BIC
213 WHERE BCO.COMPONENT_SEQUENCE_ID = BIC.COMPONENT_SEQUENCE_ID
214 AND BIC.Component_Item_Id = p_Component_Item_Id
215 AND BIC.Bill_Sequence_Id = p_Bill_Sequence_Id
216 AND BCO.Operation_Seq_Num = p_Operation_Seq_Num
217 /* AND
218 (
219 ( p_entity = 'COPS'
220 AND
221 (p_comp_operation_seq_id IS NULL
222 OR
223 p_comp_operation_seq_id = FND_API.G_MISS_NUM
224 OR
225 comp_operation_seq_id <> p_comp_operation_seq_id)
226 )
227 OR
228 ( p_entity = 'RC'
229 AND
230 (p_component_sequence_id IS NULL
231 OR
232 p_component_sequence_id = FND_API.G_MISS_NUM
233 OR
234 comp_operation_seq_id <> 0
235 OR
236 component_sequence_id <> p_component_sequence_id)
237 )
238 ) */
239
240 /*
241 AND
242 (
243 p_RowId IS NULL
244 or
245 p_Rowid = FND_API.G_MISS_CHAR
246 or
247 ( decode(p_entity,'COPS',bco_rowid,
248 'RC',bic_RowId,' ') <> p_RowID )
249 )
250 */
251 AND
252 (
253 p_RowId IS NULL
254 or
255 p_Rowid = FND_API.G_MISS_CHAR
256 or
257 bco.rowid <> p_Rowid)
258 AND (p_To_End_Item_Number IS NULL
259 OR p_To_End_Item_Number >=
260 BIC.From_End_Item_Unit_Number)
261 AND (p_From_End_Item_Number <=
262 BIC.To_End_Item_Unit_Number
263 OR BIC.To_End_Item_Unit_Number IS NULL
264 )
265 AND ( bic.IMPLEMENTATION_DATE IS NOT NULL )
266 AND ( bic.DISABLE_DATE IS NULL ) --bug:5347036 Consider enabled components only
267 );
268 BEGIN
269
270 FOR l_Unit IN All_Numbers_BIC LOOP
271 l_Count := l_Count + 1;
272 END LOOP;
273
274 IF (l_count <> 0) THEN
275 FOR l_Unit IN All_Numbers_BCO LOOP
276 l_Count := l_Count + 1;
277 END LOOP;
278 END IF;
279
280
281 -- If count <> 0 that means the unit numbers are overlapping
282 IF l_Count <> 0 THEN
283 RETURN TRUE;
284 ELSE
285 RETURN FALSE;
286 END IF;
287
288 END Check_Overlap_Numbers;
289
290 /********************************************************************
291 *
292 * Procedure : Check_Entity
293 * Parameters IN : Component Operation Record as given by the User
294 * Component Operation Unexposed Record
295 * Parameters OUT: Return_Status - Indicating success or faliure
296 * Mesg_Token_Tbl - Filled with any errors or warnings
297 * Purpose : Entity validate procedure will execute the business
298 * validations for the component operation entity
299 * Any errors are loaded in the Mesg_Token_Tbl and
300 * a return status value is set.
301 ********************************************************************/
302
303 PROCEDURE Check_Entity
304 ( x_return_status IN OUT NOCOPY VARCHAR2
305 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
306 , p_bom_comp_ops_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
307 , p_bom_comp_ops_Unexp_Rec IN Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
308 )
309 IS
310
311 l_disable_date Date;
312 l_token_tbl Error_Handler.Token_tbl_Type;
313 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
314 l_Additional_Op_Seq_Number NUMBER;
315 l_temp_var NUMBER :=0;
316 p_dummy NUMBER;
317 l_assy_bom_enabled VARCHAR2(1);
318
319 BEGIN
320
321 BEGIN
322 IF Bom_Globals.Get_Caller_Type <> 'MIGRATION' THEN
323 SELECT 1
324 INTO p_dummy
325 FROM bom_bill_of_materials
326 WHERE bill_sequence_id = source_bill_sequence_id
327 AND bill_sequence_id = p_bom_comp_ops_unexp_rec.bill_Sequence_id;
328 END IF;
329 EXCEPTION
330 WHEN NO_DATA_FOUND THEN
331 Error_Handler.Add_Error_Token
332 ( p_Message_Name => 'BOM_COMMON_COMP_OP'
333 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
334 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
335 , p_Token_Tbl => l_Token_Tbl
336 );
337 x_Return_Status := FND_API.G_RET_STS_ERROR;
338 END;
339
340 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Entity Validation for Comp. Operation begins . . .'); END IF;
341
342
343 SELECT msi.bom_enabled_flag
344 INTO l_assy_bom_enabled
345 FROM mtl_system_items_b msi,
346 bom_bill_of_materials bbom
347 WHERE bbom.bill_sequence_id = p_bom_comp_ops_Unexp_Rec.bill_sequence_id
348 AND bbom.assembly_item_id = msi.inventory_item_id
349 AND bbom.organization_id = msi.organization_id;
350
351 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Assy Bom Enabled flag : ' || l_assy_bom_enabled); END IF;
352
353 IF l_assy_bom_enabled <> 'Y'
354 THEN
355 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
356 l_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
357 l_token_tbl(1).token_value :=
358 p_bom_comp_ops_rec.assembly_Item_Name;
359 Error_Handler.Add_Error_Token
360 ( x_Mesg_Token_tbl => l_Mesg_Token_Tbl
361 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
362 , p_message_name => 'BOM_REV_ITEM_BOM_NOT_ENABLED'
363 , p_token_tbl => l_token_tbl
364 );
365 END IF;
366 RAISE FND_API.G_EXC_ERROR;
367 END IF;
368
369
370 /* Select the didsable date which is one of the key parameters */
371
372 SELECT disable_date INTO l_disable_date FROM bom_inventory_components WHERE
373 component_sequence_id = p_bom_comp_ops_unexp_rec.component_sequence_id;
374
375 /* Validate for Duplicate entries/Overlapping of the component */
376
377 /* While creating a new row, additional_operation_sequence_number should be checked and
378 while updating the existing additional_operation_sequence_number, new_addtional_op_sequence_number
379 should be checked for Overlapping entries */
380
381 l_Additional_Op_Seq_Number := p_bom_comp_ops_rec.additional_operation_seq_num;
382
383 If( p_bom_comp_ops_rec.transaction_type = BOM_globals.G_OPR_UPDATE and
384 p_bom_comp_ops_rec.new_additional_op_seq_num is not null
385 and p_bom_comp_ops_rec.new_additional_op_seq_num <> FND_API.G_MISS_NUM) then
386 l_Additional_Op_Seq_Number := p_bom_comp_ops_rec.new_additional_op_seq_num;
387 End if;
388
389 IF p_bom_comp_ops_rec.from_end_item_unit_number IS NULL or
390 p_bom_comp_ops_rec.from_end_item_unit_number = FND_API.G_MISS_CHAR THEN
391
392 IF Check_Overlap_Dates ( p_Effectivity_Date => p_bom_comp_ops_rec.start_effective_date,
393 p_Disable_Date => l_disable_date,
394 p_Component_Item_Id =>p_bom_comp_ops_unexp_rec.component_item_id,
395 p_Bill_Sequence_Id => p_bom_comp_ops_unexp_rec.bill_sequence_id,
396 p_comp_operation_seq_id => p_bom_comp_ops_unexp_rec.comp_operation_seq_id,
397 p_Rowid => p_bom_comp_ops_unexp_rec.rowid,
398 p_Operation_Seq_Num => l_Additional_Op_Seq_Number) THEN
399
400 l_token_tbl(1).token_name := 'COMPONENT_ITEM_NAME';
401 l_token_tbl(1).token_value := p_bom_comp_ops_rec.component_item_name;
402 l_token_tbl(2).token_name := 'OPERATION_SEQ_NUM';
403 l_token_tbl(2).token_value := p_bom_comp_ops_rec.additional_operation_seq_num;
404 Error_Handler.Add_Error_Token
405 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
406 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
407 , p_message_name => 'BOM_COPS_DATES_OVERLAP'
408 , p_token_tbl => l_token_tbl
409 );
410
411 RAISE FND_API.G_EXC_ERROR;
412 END IF;
413
414 ELSE
415
416 IF Check_Overlap_Numbers ( p_From_End_Item_Number => p_bom_comp_ops_rec.from_end_item_unit_number,
417 p_To_End_Item_Number => p_bom_comp_ops_rec.to_end_item_unit_number,
418 p_Component_Item_Id =>p_bom_comp_ops_unexp_rec.component_item_id,
419 p_Bill_Sequence_Id => p_bom_comp_ops_unexp_rec.bill_sequence_id,
420 p_comp_operation_seq_id => p_bom_comp_ops_unexp_rec.comp_operation_seq_id,
421 p_Rowid => p_bom_comp_ops_unexp_rec.rowid,
422 p_Operation_Seq_Num => l_Additional_Op_Seq_Number) THEN
423
424 l_token_tbl(1).token_name := 'COMPONENT_ITEM_NAME';
425 l_token_tbl(1).token_value := p_bom_comp_ops_rec.component_item_name;
426 l_token_tbl(2).token_name := 'OPERATION_SEQ_NUM';
427 l_token_tbl(2).token_value := p_bom_comp_ops_rec.additional_operation_seq_num;
428 Error_Handler.Add_Error_Token
429 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
430 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
431 , p_message_name => 'BOM_COPS_NUMBERS_OVERLAP'
432 , p_token_tbl => l_token_tbl
433 );
434 RAISE FND_API.G_EXC_ERROR;
435
436 END IF;
437
438 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
439 x_return_status := FND_API.G_RET_STS_SUCCESS;
440
441 END IF;
442
443 /* When the component operation is updated with new Component operation, It should be checked that
444 the New Component operation does not exists already */
445
446 IF ( p_bom_comp_ops_rec.new_additional_op_seq_num is not null
447 and p_bom_comp_ops_rec.new_additional_op_seq_num <> FND_API.G_MISS_NUM
448 and p_bom_comp_ops_rec.transaction_type = Bom_Globals.G_OPR_UPDATE) THEN
449
450 select count(*) into l_temp_var
451 FROM BOM_COMPONENT_OPERATIONS
452 WHERE OPERATION_SEQ_NUM = p_bom_comp_ops_rec.new_additional_op_seq_num
453 AND COMPONENT_SEQUENCE_ID = p_bom_Comp_ops_Unexp_Rec.component_sequence_id;
454
455 IF (l_temp_var <>0) then
456 l_Token_Tbl(1).Token_Name := 'OPERATION_SEQUENCE_NUMBER';
457 l_Token_Tbl(1).Token_Value :=
458 p_bom_comp_ops_rec.new_additional_op_seq_num;
459 l_token_tbl(2).token_name := 'REVISED_COMPONENT_NAME';
460 l_token_tbl(2).token_value := p_bom_comp_ops_rec.component_item_name;
461
462 Error_Handler.Add_Error_Token
463 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
464 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
465 , p_message_name => 'BOM_COMP_OPS_ALREADY_EXISTS'
466 , p_token_tbl => l_token_tbl
467 );
468
469 RAISE FND_API.G_EXC_ERROR;
470 END IF;
471 END IF;
472
473 IF BOm_GlobalS.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Verified New addtional Component operation ... '); END IF;
474
475 IF Bom_GlobalS.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Verifying Operation Seq Num in Editable common Bills ... '); END IF;
476 --The op seq num being used must be valid for the editable common bills commoning this bill.
477 IF NOT BOMPCMBM.Check_Op_Seq_In_Ref_Boms(p_src_bill_seq_id => p_bom_comp_ops_unexp_rec.bill_sequence_id,
478 p_op_seq => nvl(p_bom_comp_ops_rec.new_additional_op_seq_num,
479 p_bom_comp_ops_rec.additional_operation_seq_num)
480 )
481 THEN
482 Error_Handler.Add_Error_Token
483 ( p_Message_Name => 'BOM_COMMON_OP_SEQ_INVALID'
484 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
485 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
486 , p_Token_Tbl => l_Token_Tbl
487 );
488 RAISE FND_API.G_EXC_ERROR;
489 END IF;
490
491
492
493
494 EXCEPTION
495
496 WHEN FND_API.G_EXC_ERROR THEN
497
498 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Expected Error in Comp Operations. Entity Validation '); END IF;
499
500 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
501 x_return_status := FND_API.G_RET_STS_ERROR;
502
503 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
504
505 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('UNExpected Error in Comp. Operations Entity Validation '); END IF;
506 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
508
509 WHEN OTHERS THEN
510 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
512
513 END Check_Entity;
514
515 /********************************************************************
516 *
517 * Procedure : Check_Attributes
518 * Parameters IN : Component Operation Record as given by the User
519 * Parameters OUT: Return_Status - Indicating success or faliure
520 * Mesg_Token_Tbl - Filled with any errors or warnings
521 * Purpose : Attribute validation will validate individual attributes
522 * and any errors will be populated in the Mesg_Token_Tbl
523 * and returned with a return_status.
524 ********************************************************************/
525
526 PROCEDURE Check_Attributes
527 ( x_return_status IN OUT NOCOPY VARCHAR2
528 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
529 , p_bom_comp_ops_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
530 , p_bom_comp_ops_unexp_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
531 )
532 IS
533
534 l_original_routing VARCHAR2(1) := 'N';
535 l_valid Number := 0;
536 l_token_tbl Error_Handler.Token_tbl_Type;
537 l_Mesg_token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
538 l_err_text VARCHAR2(240);
539
540 CURSOR OpSeq_In_Original IS
541 SELECT operation_seq_num
542 FROM bom_operation_sequences bos
543 WHERE routing_sequence_id =
544 (SELECT common_routing_sequence_id
545 FROM bom_operational_routings bor
546 WHERE assembly_item_id = p_bom_comp_ops_unexp_rec.assembly_item_id
547 and organization_id = p_bom_comp_ops_unexp_rec.organization_id
548 and nvl(alternate_routing_designator,'NONE') =
549 nvl(p_bom_comp_ops_rec.alternate_bom_code, 'NONE')
550 )
551 and nvl(trunc(disable_date), trunc(sysdate)+1) > trunc(sysdate) and nvl(operation_type,1) = 1;
552
553 CURSOR Opseq_In_Primary IS
554 SELECT operation_seq_num
555 FROM bom_operation_sequences bos
556 WHERE routing_sequence_id =
557 (SELECT common_routing_sequence_id
558 FROM bom_operational_routings bor
559 WHERE assembly_item_id = p_bom_comp_ops_unexp_rec.assembly_item_id
560 and organization_id = p_bom_comp_ops_unexp_rec.organization_id
561 and alternate_routing_designator IS NULL
562 )
563 and nvl(trunc(disable_date), trunc(sysdate)+1) > trunc(sysdate) and nvl(operation_type,1) = 1;
564
565 BEGIN
566
567 x_return_status := FND_API.G_RET_STS_SUCCESS;
568
569 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Attribute Validation Starts . . . '); END IF;
570
571 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Additional Operation Sequence Number is '||to_char(p_bom_comp_ops_rec.additional_operation_seq_num)); END IF;
572
573 /* Check for the existence of OpSeq in the original routing which is defined for this item */
574
575 FOR r1 IN OpSeq_In_Original
576 LOOP
577 l_original_routing := 'Y';
578
579 IF r1.operation_seq_num = p_bom_comp_ops_rec.additional_operation_seq_num THEN
580 l_valid := 1;
581 Exit;
582 END IF;
583 END LOOP;
584
585 /* If there is no original routing, then check in the primary routing */
586
587 IF l_original_routing = 'N' THEN
588 FOR r2 IN OpSeq_In_Primary
589 LOOP
590
591 IF r2.operation_seq_num = p_bom_comp_ops_rec.additional_operation_seq_num THEN
592 l_valid := 1;
593 Exit;
594 END IF;
595
596 END LOOP;
597 END IF;
598
599 IF l_valid = 0 THEN
600 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
601 THEN
602 l_token_tbl(1).token_name := 'OPERATION_SEQ_NUM';
603 l_token_tbl(1).token_value := p_bom_comp_ops_rec.additional_operation_seq_num;
604
605 Error_Handler.Add_Error_Token
606 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
607 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
608 , p_message_name => 'BOM_COPS_OPSEQ_INVALID'
609 , p_token_tbl => l_token_tbl
610 );
611 END IF;
612 x_return_status := FND_API.G_RET_STS_ERROR;
613 END IF;
614
615 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
616
617
618 EXCEPTION
619
620 WHEN OTHERS THEN
621 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
622 THEN
623 l_err_text := G_PKG_NAME ||
624 'Attribute Validate (Component Operation)' ||
625 SUBSTR(SQLERRM, 1, 100);
626
627 Error_Handler.Add_Error_Token
628 ( p_Message_Name => NULL
629 , p_Message_Text => l_err_text
630 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
631 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
632 );
633 END IF;
634 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
636
637 END Check_Attributes;
638
639 /*****************************************************************************
640 * Procedure : Check_Existence
641 * Parameters IN : Component Operation exposed column record
642 * Component Operation unexposed column record
643 * Parameters OUT: Old Component Operation exposed column record
644 * Old Component Operation unexposed column record
645 * Mesg Token Table
646 * Return Status
647 * Purpose : Check_Existence will perform a query using the primary key
648 * information and will return a success if the operation is
649 * CREATE and the record EXISTS or will return an
650 * error if the operation is UPDATE and the record DOES NOT
651 * EXIST.
652 * In case of UPDATE if the record exists then the procedure
653 * will return the old record in the old entity parameters
654 * with a success status.
655 ****************************************************************************/
656 PROCEDURE Check_Existence
657 ( p_bom_comp_ops_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
658 , p_bom_comp_ops_unexp_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
659 , x_old_bom_comp_ops_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
660 , x_old_bom_comp_ops_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
661 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
662 , x_Return_Status IN OUT NOCOPY VARCHAR2
663 )
664 IS
665 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
666 l_Return_Status VARCHAR2(1);
667 l_Token_Tbl Error_Handler.Token_Tbl_Type;
668 BEGIN
669 l_Token_Tbl(1).Token_Name := 'OPERATION_SEQUENCE_NUMBER';
670 l_Token_Tbl(1).Token_Value :=
671 p_bom_comp_ops_rec.additional_operation_seq_num;
672 l_token_tbl(2).token_name := 'REVISED_COMPONENT_NAME';
673 l_token_tbl(2).token_value := p_bom_comp_ops_rec.component_item_name;
674
675 BOM_Comp_Operation_Util.Query_Row
676 ( p_component_sequence_id=>
677 p_bom_comp_ops_unexp_rec.component_sequence_id
678 ,p_additional_operation_seq_num =>p_bom_comp_ops_rec.additional_operation_seq_num
679 , x_bom_comp_ops_rec => x_old_bom_comp_ops_rec
680 , x_bom_comp_ops_unexp_rec => x_old_bom_comp_ops_unexp_rec
681 , x_Return_Status => l_return_status
682 );
683
684 IF l_return_status = Bom_Globals.G_RECORD_FOUND AND
685 p_bom_comp_ops_rec.transaction_type = Bom_Globals.G_OPR_CREATE
686 THEN
687 Error_Handler.Add_Error_Token
688 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
689 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
690 , p_message_name => 'BOM_COMP_OPS_ALREADY_EXISTS'
691 , p_token_tbl => l_token_tbl
692 );
693 l_return_status := FND_API.G_RET_STS_ERROR;
694 ELSIF l_return_status = Bom_Globals.G_RECORD_NOT_FOUND AND
695 p_bom_comp_ops_rec.transaction_type IN
696 (Bom_Globals.G_OPR_UPDATE, Bom_Globals.G_OPR_DELETE)
697 THEN
698 Error_Handler.Add_Error_Token
699 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
700 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
701 , p_message_name => 'BOM_COMP_OPS_DOESNOT_EXIST'
702 , p_token_tbl => l_token_tbl
703 );
704 l_return_status := FND_API.G_RET_STS_ERROR;
705 ELSIF l_Return_status = FND_API.G_RET_STS_UNEXP_ERROR
706 THEN
707 Error_Handler.Add_Error_Token
708 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
709 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
710 , p_message_name => NULL
711 , p_message_text =>
712 'Unexpected error while existence verification of ' ||
713 'Component Operation '||
714 p_bom_comp_ops_rec.operation_sequence_number
715 , p_token_tbl => l_token_tbl
716 );
717 ELSE
718
719 /* Assign the relevant transaction type for SYNC operations */
720
721 IF p_bom_comp_ops_rec.transaction_type = 'SYNC' THEN
722 IF l_return_status = Bom_Globals.G_RECORD_FOUND THEN
723 x_old_bom_comp_ops_rec.transaction_type :=
724 Bom_Globals.G_OPR_UPDATE;
725 ELSE
726 x_old_bom_comp_ops_rec.transaction_type :=
727 Bom_Globals.G_OPR_CREATE;
728 END IF;
729 END IF;
730 l_return_status := FND_API.G_RET_STS_SUCCESS;
731
732 END IF;
733
734 x_return_status := l_return_status;
735 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
736
737 END Check_Existence;
738
739 PROCEDURE Check_Lineage
740 ( p_bom_comp_ops_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
741 , p_bom_comp_ops_unexp_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
742 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
743 , x_Return_Status IN OUT NOCOPY VARCHAR2
744 )
745 IS
746 l_token_tbl Error_Handler.Token_Tbl_Type;
747 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
748
749 CURSOR c_GetComponent IS
750 SELECT component_sequence_id
751 FROM bom_inventory_components
752 WHERE component_item_id= p_bom_comp_ops_unexp_rec.component_item_id
753 AND operation_seq_num=p_bom_comp_ops_rec.operation_sequence_number
754 AND effectivity_date = p_bom_comp_ops_rec.start_effective_date
755 AND bill_sequence_id = p_bom_comp_ops_unexp_rec.bill_sequence_id;
756 BEGIN
757 x_return_status := FND_API.G_RET_STS_SUCCESS;
758
759 FOR Component IN c_GetComponent LOOP
760 IF Component.component_sequence_id <>
761 p_bom_comp_ops_unexp_rec.component_sequence_id
762 THEN
763 l_Token_Tbl(1).token_name :=
764 'REVISED_COMPONENT_NAME';
765 l_Token_Tbl(1).token_value :=
766 p_bom_comp_ops_rec.component_item_name;
767 l_Token_Tbl(2).token_name :=
768 'OPERATION_SEQUENCE_NUMBER';
769 l_Token_Tbl(2).token_value :=
770 p_bom_comp_ops_rec.operation_sequence_number;
771 l_Token_Tbl(3).token_name :=
772 'ASSEMBLY_ITEM_NAME';
773 l_Token_Tbl(3).token_value :=
774 p_bom_comp_ops_rec.assembly_item_name;
775
776 Error_Handler.Add_Error_Token
777 ( p_Message_Name => 'BOM_COPS_REV_ITEM_MISMATCH'
778 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
779 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
780 , p_Token_Tbl => l_Token_Tbl
781 );
782 x_return_status := FND_API.G_RET_STS_ERROR;
783 END IF;
784 END LOOP;
785
786 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
787
788 END CHECK_LINEAGE;
789
790
791
792 END BOM_Validate_Comp_Operation;