[Home] [Help]
PACKAGE BODY: APPS.BOM_VALIDATE_SUB_OP_RES
Source
1 PACKAGE BODY BOM_Validate_Sub_Op_Res AS
2 /* $Header: BOMLSORB.pls 120.4.12000000.2 2007/10/17 13:55:29 jiabraha ship $ */
3
4 /****************************************************************************
5 --
6 -- Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA
7 -- All rights reserved.
8 --
9 -- FILENAME
10 --
11 -- BOMLSORB.pls
12 --
13 -- DESCRIPTION
14 --
15 -- Body of package BOM_Validate_Sub_Op_Res
16 --
17 -- NOTES
18 --
19 -- HISTORY
20 --
21 -- 22-AUG-00 Masanori Kimizuka Initial Creation
22 -- 08-DEC-2005 Bhavnesh Patel 4689856:Validation for new column new_basis_type
23 ****************************************************************************/
24
25 G_Pkg_Name VARCHAR2(30) := 'BOM_Validate_Sub_Op_Res';
26
27 l_ACD_ADD CONSTANT NUMBER := 1 ;
28 l_ACD_CHANGE CONSTANT NUMBER := 2 ;
29 l_ACD_DISABLE CONSTANT NUMBER := 3 ;
30 l_NO_SCHEDULE CONSTANT NUMBER := 2 ;
31 l_PRIOR CONSTANT NUMBER := 3 ;
32 l_NEXT CONSTANT NUMBER := 4 ;
33 l_PO_RECEIPT CONSTANT NUMBER := 3 ;
34 l_PO_MOVE CONSTANT NUMBER := 4 ;
35
36
37 /******************************************************************
38 * OTHER LOCAL FUNCTION AND PROCEDURES
39 * Purpose : Called by Check_Entity or something
40 *********************************************************************/
41 --
42 -- Function: Check if Op Seq Num exists in Work Order
43 -- in ECO by Lot, Wo, Cum Qty
44 --
45 FUNCTION Check_ECO_By_WO_Effectivity
46 ( p_revised_item_sequence_id IN NUMBER
47 , p_operation_seq_num IN NUMBER
48 , p_resource_id IN NUMBER
49 , p_sub_group_num IN NUMBER )
50
51 RETURN BOOLEAN
52 IS
53 l_ret_status BOOLEAN := TRUE ;
54
55 l_lot_number varchar2(30) := NULL;
56 l_from_wip_entity_id NUMBER :=0;
57 l_to_wip_entity_id NUMBER :=0;
58 l_from_cum_qty NUMBER :=0;
59
60 /* Rewrote the cursor for BUG 4918694
61 CURSOR l_check_lot_num_csr ( p_lot_number NUMBER
62 , p_operation_seq_num NUMBER
63 , p_resource_id NUMBER
64 , p_sub_group_num NUMBER )
65 IS
66 SELECT 'Sub Res does not exist'
67 FROM SYS.DUAL
68 WHERE EXISTS (SELECT NULL
69 FROM WIP_DISCRETE_JOBS wdj
70 WHERE wdj.lot_number = p_lot_number
71 AND (wdj.status_type <> 1
72 OR
73 NOT EXISTS(SELECT NULL
74 FROM WIP_SUB_OPERATION_RESOURCES wsor
75 WHERE substitute_group_num = p_sub_group_num
76 AND resource_id = p_resource_id
77 AND operation_seq_num = p_operation_seq_num
78 AND wip_entity_id = wdj.wip_entity_id)
79 )
80 AND wdj.lot_number = p_lot_number
81 ) ;
82 */
83
84 CURSOR l_check_lot_num_csr ( p_lot_number NUMBER
85 , p_operation_seq_num NUMBER
86 , p_resource_id NUMBER
87 , p_sub_group_num NUMBER )
88 IS
89 SELECT 'Sub Res does not exist'
90 FROM DUAL
91 WHERE NOT EXISTS ( SELECT NULL
92 FROM WIP_DISCRETE_JOBS wdj
93 WHERE wdj.lot_number = p_lot_number
94 AND wdj.status_type = 1
95 AND EXISTS ( SELECT NULL
96 FROM WIP_SUB_OPERATION_RESOURCES wsor
97 WHERE substitute_group_num = p_sub_group_num
98 AND resource_id = p_resource_id
99 AND operation_seq_num = p_operation_seq_num
100 AND wip_entity_id = wdj.wip_entity_id
101 )
102 );
103
104 CURSOR l_check_wo_csr ( p_from_wip_entity_id NUMBER
105 , p_to_wip_entity_id NUMBER
106 , p_operation_seq_num NUMBER
107 , p_resource_id NUMBER
108 , p_sub_group_num NUMBER )
109 IS
110 SELECT 'Sub Res does not exist'
111 FROM SYS.DUAL
112 WHERE EXISTS (SELECT NULL
113 FROM WIP_DISCRETE_JOBS wdj
114 , WIP_ENTITIES we
115 , WIP_ENTITIES we1
116 , WIP_ENTITIES we2
117 WHERE (wdj.status_type <> 1
118 OR
119 NOT EXISTS (SELECT NULL
120 FROM WIP_SUB_OPERATION_RESOURCES wsor
121 WHERE substitute_group_num = p_sub_group_num
122 AND resource_id = p_resource_id
123 AND operation_seq_num = p_operation_seq_num
124 AND wip_entity_id = wdj.wip_entity_id)
125 )
126 AND wdj.wip_entity_id = we.wip_entity_id
127 AND we.wip_entity_name >= we1.wip_entity_name
128 AND we.wip_entity_name <= we2.wip_entity_name
129 AND we1.wip_entity_id = p_from_wip_entity_id
130 AND we2.wip_entity_id = NVL(p_to_wip_entity_id, p_from_wip_entity_id)
131 ) ;
132
133 CURSOR l_check_cum_csr ( p_from_wip_entity_id NUMBER
134 , p_operation_seq_num NUMBER
135 , p_resource_id NUMBER
136 , p_sub_group_num NUMBER )
137
138
139 IS
140 SELECT 'Sub Res does not exist'
141 FROM SYS.DUAL
142 WHERE EXISTS (SELECT NULL
143 FROM WIP_DISCRETE_JOBS wdj
144 WHERE (wdj.status_type <> 1
145 OR
146 NOT EXISTS(SELECT NULL
147 FROM WIP_SUB_OPERATION_RESOURCES wsor
148 WHERE substitute_group_num = p_sub_group_num
149 AND resource_id = p_resource_id
150 AND operation_seq_num = p_operation_seq_num
151 AND wip_entity_id = wdj.wip_entity_id)
152 )
153 AND wdj.wip_entity_id = p_from_wip_entity_id
154 ) ;
155
156 BEGIN
157
158
159 l_lot_number := BOM_Rtg_Globals.Get_Lot_Number;
160 l_from_wip_entity_id := BOM_Rtg_Globals.Get_From_Wip_Entity_Id;
161 l_to_wip_entity_id := BOM_Rtg_Globals.Get_To_Wip_Entity_Id;
162 l_from_cum_qty := BOM_Rtg_Globals.Get_From_Cum_Qty;
163
164
165 -- Check if Op Seq Num is exist in ECO by Lot
166 IF l_lot_number IS NOT NULL
167 AND l_from_wip_entity_id IS NULL
168 AND l_to_wip_entity_id IS NULL
169 AND l_from_cum_qty IS NULL
170 THEN
171
172 FOR l_lot_num_rec IN l_check_lot_num_csr
173 ( p_lot_number => l_lot_number
174 , p_operation_seq_num => p_operation_seq_num
175 , p_resource_id => p_resource_id
176 , p_sub_group_num => p_sub_group_num )
177 LOOP
178 l_ret_status := FALSE ;
179 END LOOP ;
180
181 -- Check if Op Seq Num is exist in ECO by Cum
182 ELSIF l_lot_number IS NULL
183 AND l_from_wip_entity_id IS NOT NULL
184 AND l_to_wip_entity_id IS NULL
185 AND l_from_cum_qty IS NOT NULL
186 THEN
187
188 FOR l_lot_num_rec IN l_check_cum_csr
189 ( p_from_wip_entity_id => l_from_wip_entity_id
190 , p_operation_seq_num => p_operation_seq_num
191 , p_resource_id => p_resource_id
192 , p_sub_group_num => p_sub_group_num )
193 LOOP
194 l_ret_status := FALSE ;
195 END LOOP ;
196
197 -- Check if Op Seq Num is exist in ECO by WO
198 ELSIF l_lot_number IS NULL
199 AND l_from_wip_entity_id IS NOT NULL
200 AND l_from_cum_qty IS NULL
201 THEN
202
203 FOR l_lot_num_rec IN l_check_wo_csr
204 ( p_from_wip_entity_id => l_from_wip_entity_id
205 , p_to_wip_entity_id => l_to_wip_entity_id
206 , p_operation_seq_num => p_operation_seq_num
207 , p_resource_id => p_resource_id
208 , p_sub_group_num => p_sub_group_num )
209 LOOP
210 l_ret_status := FALSE ;
211 END LOOP ;
212
213 ELSIF l_lot_number IS NULL
214 AND l_from_wip_entity_id IS NULL
215 AND l_to_wip_entity_id IS NULL
216 AND l_from_cum_qty IS NULL
217 THEN
218 NULL ;
219
220 -- ELSE
221 -- l_ret_status := FALSE ;
222 --
223
224 END IF ;
225
226 RETURN l_ret_status ;
227
228 END Check_ECO_By_WO_Effectivity ;
229
230
231 /*******************************************************************
232 *Others : Following Procedures and Functions are called by
233 * Check_Entity in Op Resource and Sub Op Resource
234 *Purpose : These Shared Logic validate the values on
235 * inter-dependent columns or get values to validate entity.
236 *******************************************************************/
237 PROCEDURE Val_Scheduled_Sub_Resource
238 ( p_op_seq_id IN NUMBER
239 , p_resource_id IN NUMBER
240 , p_sub_group_num IN NUMBER
241 , p_schedule_flag IN NUMBER
242 , x_return_status IN OUT NOCOPY VARCHAR2
243 )
244 IS
245
246 CURSOR l_rel_schedule_csr
247 ( p_op_seq_id NUMBER
248 , p_sub_group_num NUMBER
249 , p_schedule_flag NUMBER
250 )
251 IS
252 SELECT 'Related Schedule Resource does not exist'
253 FROM SYS.DUAL
254 WHERE NOT EXISTS( SELECT NULL
255 FROM BOM_OPERATION_RESOURCES
256 WHERE schedule_flag = p_schedule_flag
257 AND substitute_group_num = p_sub_group_num
258 AND operation_sequence_id = p_op_seq_id
259 ) ;
260
261 CURSOR l_sub_schedule_csr
262 ( p_op_seq_id NUMBER
263 , p_resource_id NUMBER
264 , p_sub_group_num NUMBER
265 , p_schedule_flag NUMBER
266 )
267 IS
268 SELECT 'Already exists'
269 FROM SYS.DUAL
270 WHERE EXISTS( SELECT NULL
271 FROM BOM_SUB_OPERATION_RESOURCES
272 WHERE schedule_flag = p_schedule_flag
273 AND resource_id <> p_resource_id
274 AND substitute_group_num = p_sub_group_num
275 AND operation_sequence_id = p_op_seq_id
276 ) ;
277
278
279 BEGIN
280
281 x_return_status := FND_API.G_RET_STS_SUCCESS ;
282
283 FOR l_rel_schedule_rec IN l_rel_schedule_csr
284 ( p_op_seq_id
285 , p_sub_group_num
286 , p_schedule_flag
287 )
288 LOOP
289 x_return_status := FND_API.G_RET_STS_ERROR ;
290 END LOOP ;
291
292 IF x_return_status <> FND_API.G_RET_STS_ERROR
293 THEN
294 FOR l_sub_schedule_rec IN l_sub_schedule_csr
295 ( p_op_seq_id
296 , p_resource_id
297 , p_sub_group_num
298 , p_schedule_flag
299 )
300 LOOP
301 x_return_status := FND_API.G_RET_STS_ERROR ;
302 END LOOP ;
303 END IF ;
304
305 END Val_Scheduled_Sub_Resource ;
306
307
308 PROCEDURE Val_Sub_PO_Move
309 ( p_op_seq_id IN NUMBER
310 , p_resource_id IN NUMBER
311 , p_sub_group_num IN NUMBER
312 , x_return_status IN OUT NOCOPY VARCHAR2
313 )
314 IS
315
316 CURSOR l_rel_pomove_csr
317 ( p_op_seq_id NUMBER
318 , p_sub_group_num NUMBER
319 )
320 IS
321 SELECT 'Related PO Move Resource does not exist'
322 FROM SYS.DUAL
323 WHERE NOT EXISTS( SELECT NULL
324 FROM BOM_OPERATION_RESOURCES
325 WHERE autocharge_type = l_PO_MOVE
326 AND substitute_group_num = p_sub_group_num
327 AND operation_sequence_id = p_op_seq_id
328 ) ;
329
330 CURSOR l_sub_pomove_csr( p_op_seq_id NUMBER
331 , p_resource_id NUMBER
332 , p_sub_group_num NUMBER )
333
334 IS
335 SELECT 'Already exists'
336 FROM SYS.DUAL
337 WHERE EXISTS( SELECT NULL
338 FROM BOM_SUB_OPERATION_RESOURCES
339 WHERE autocharge_type = l_PO_MOVE
340 AND resource_id <> p_resource_id
341 AND substitute_group_num = p_sub_group_num
342 AND operation_sequence_id = p_op_seq_id
343 ) ;
344
345
346 BEGIN
347
348 x_return_status := FND_API.G_RET_STS_SUCCESS ;
349
350 FOR l_rel_pomove_rec IN l_rel_pomove_csr
351 ( p_op_seq_id
352 , p_sub_group_num
353 )
354 LOOP
355 x_return_status := FND_API.G_RET_STS_ERROR ;
356 END LOOP ;
357
358 IF x_return_status <> FND_API.G_RET_STS_ERROR
359 THEN
360 FOR l_sub_pomove_rec IN l_sub_pomove_csr ( p_op_seq_id
361 , p_resource_id
362 , p_sub_group_num
363 )
364 LOOP
365 x_return_status := FND_API.G_RET_STS_ERROR ;
366 END LOOP ;
367 END IF ;
368
369 END Val_Sub_PO_Move ;
370
371 --
372 -- Function: Get_Old_Op_Seq_Id
373 --
374 FUNCTION Get_Old_Op_Seq_Id(p_op_seq_id IN NUMBER )
375 RETURN NUMBER
376 IS
377 l_old_op_seq_id NUMBER := NULL ;
378 BEGIN
379
380 SELECT old_operation_sequence_id
381 INTO l_old_op_seq_id
382 FROM BOM_OPERATION_SEQUENCES
383 WHERE operation_sequence_id = p_op_seq_id ;
384
385 RETURN l_old_op_seq_id ;
386
387 /* Error should be processed as Unexpected Error */
388
389 END ;
390
391 -- Added for bug 2689249
392 -- bug:4689856 Included a check on basis type for identifying sub resource
393 PROCEDURE Val_Principal_Sub_Res_Unique
394 ( p_op_seq_id IN NUMBER
395 , p_res_id IN NUMBER
396 , p_sub_group_num IN NUMBER
397 , p_rep_group_num IN NUMBER
398 , p_basis_type IN NUMBER
399 , x_return_status IN OUT NOCOPY VARCHAR2
400 )
401 IS
402 CURSOR l_principal_csr ( p_op_seq_id NUMBER
403 , p_res_id NUMBER
404 , p_sub_group_num NUMBER
405 , p_rep_group_num NUMBER
406 , p_basis_type NUMBER
407 )
408 IS
409 SELECT 'Already exists'
410 FROM SYS.DUAL
411 WHERE EXISTS( SELECT NULL
412 FROM BOM_SUB_OPERATION_RESOURCES
413 WHERE principle_flag = 1 -- Yes
414 AND NVL(acd_type, l_ACD_ADD) <> l_ACD_DISABLE
415 AND (
416 ( resource_id <> p_res_id )
417 OR ( ( resource_id = p_res_id ) AND ( basis_type <> p_basis_type ) )
418 )
419 AND substitute_group_num = p_sub_group_num
420 AND replacement_group_num = p_rep_group_num
421 AND operation_sequence_id = p_op_seq_id
422 ) ;
423
424 BEGIN
425
426 x_return_status := FND_API.G_RET_STS_SUCCESS ;
427
428 FOR l_principal_rec IN l_principal_csr ( p_op_seq_id
429 , p_res_id
430 , p_sub_group_num
431 , p_rep_group_num
432 , p_basis_type
433 )
434 LOOP
435 x_return_status := FND_API.G_RET_STS_ERROR ;
436 END LOOP ;
437
438 /*
439 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
440 THEN
441 null; -- Substitute resources cannot be added from ECOs
442 END IF ;
443 */
444
445 END Val_Principal_Sub_Res_Unique ;
446
447 /*Fix for bug 6074930 -Added below procedure Val_schedule_flag.
448 It is called by procedure Check_Entity.
449 Purpose: Scheduled simultaneous resources/sub-resources should have the
450 same scheduling flag. Resources/sub-resources with schedule flag 'No'
451 are unscheduled and hence exempt for this validation.*/
452
453 PROCEDURE Val_Schedule_Flag
454 ( p_op_seq_id IN NUMBER
455 , p_res_seq_num IN NUMBER
456 , p_sch_seq_num IN NUMBER
457 , p_sch_flag IN NUMBER
458 , p_sub_grp_num IN NUMBER
459 , p_rep_grp_num IN NUMBER
460 , p_basis_type IN NUMBER
461 , p_in_res_id IN NUMBER
462 , p_ret_res_id IN OUT NOCOPY NUMBER
463 , x_return_status IN OUT NOCOPY VARCHAR2
464 )
465 IS
466 l_resource_id number;
467
468 CURSOR l_sch_res_cur IS
469 SELECT resource_id
470 FROM bom_operation_resources
471 WHERE operation_sequence_id = p_op_seq_id
472 AND nvl(schedule_seq_num,resource_seq_num) = p_sch_seq_num
473 AND schedule_flag not in (p_sch_flag,l_NO_SCHEDULE)
474 AND rownum=1;
475
476 CURSOR l_sch_sub_res_cur IS
477 SELECT resource_id
478 FROM bom_sub_operation_resources
479 WHERE operation_sequence_id = p_op_seq_id
480 AND schedule_seq_num = p_sch_seq_num
481 AND schedule_flag not in (p_sch_flag,l_NO_SCHEDULE)
482 AND (
483 substitute_group_num <> p_sub_grp_num
484 OR replacement_group_num <> p_rep_grp_num
485 OR basis_type <> p_basis_type
486 OR resource_id <> p_in_res_id
487 )
488 AND rownum=1;
489
490 BEGIN
491 x_return_status := FND_API.G_RET_STS_SUCCESS;
492 l_resource_id := FND_API.G_MISS_NUM;
493
494 /* Verify whether the current sub-resource violates the validation w.r.t to
495 any existing resource. */
496 OPEN l_sch_res_cur;
497 FETCH l_sch_res_cur INTO l_resource_id;
498
499 /* Return error status if violation occurs */
500 IF l_sch_res_cur%FOUND THEN
501 p_ret_res_id := l_resource_id;
502 x_return_status := Error_Handler.G_STATUS_ERROR;
503 END IF;
504
505 IF l_sch_res_cur%ISOPEN THEN
506 CLOSE l_sch_res_cur;
507 END IF;
508
509 /* If no violated resource is found above, then verify whether the current sub-resource
510 violates the validation w.r.t to any existing sub-resource. */
511 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
512
513 OPEN l_sch_sub_res_cur;
514 FETCH l_sch_sub_res_cur INTO l_resource_id;
515
516 /* Return error status if violation occurs */
517 IF l_sch_sub_res_cur%FOUND THEN
518 p_ret_res_id := l_resource_id;
519 x_return_status := Error_Handler.G_STATUS_ERROR;
520 END IF;
521
522 IF l_sch_sub_res_cur%ISOPEN THEN
523 CLOSE l_sch_sub_res_cur;
524 END IF;
525
526 END IF;
527
528 END Val_Schedule_Flag;
529
530
531 /******************************************************************
532 * Procedure : Check_Existence used by RTG BO
533 * Parameters IN : Sub Operation Resource exposed column record
534 * Sub Operation Resource unexposed column record
535 * Parameters out: Old Sub Operation Resource exposed column record
536 * Old Sub Operation Resource unexposed column record
537 * Mesg Token Table
538 * Return Status
539 * Purpose : Convert Routing Sub Op Resource to Revised Sub Op
540 * Resource and Call Check_Existence for ECO Bo.
541 * After calling Check_Existence, convert old Revised
542 * Op Resource record back to Routing Op Resource
543 *********************************************************************/
544 PROCEDURE Check_Existence
545 ( p_sub_resource_rec IN Bom_Rtg_Pub.Sub_Resource_Rec_Type
546 , p_sub_res_unexp_rec IN Bom_Rtg_Pub.Sub_Res_Unexposed_Rec_Type
547 , x_old_sub_resource_rec IN OUT NOCOPY Bom_Rtg_Pub.Sub_Resource_Rec_Type
548 , x_old_sub_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Sub_Res_Unexposed_Rec_Type
549 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
550 , x_return_status IN OUT NOCOPY VARCHAR2
551 )
552
553 IS
554 l_rev_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type ;
555 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type ;
556 l_old_rev_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type ;
557 l_old_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type ;
558
559 BEGIN
560 -- Convert Routing Operation to ECO Operation
561 Bom_Rtg_Pub.Convert_RtgSubRes_To_EcoSubRes
562 ( p_rtg_sub_resource_rec => p_sub_resource_rec
563 , p_rtg_sub_res_unexp_rec => p_sub_res_unexp_rec
564 , x_rev_sub_resource_rec => l_rev_sub_resource_rec
565 , x_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
566 ) ;
567
568 -- Call Check_Existence
569 Bom_Validate_Sub_Op_Res.Check_Existence
570 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
571 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
572 , x_old_rev_sub_resource_rec => l_old_rev_sub_resource_rec
573 , x_old_rev_sub_res_unexp_rec => l_old_rev_sub_res_unexp_rec
574 , x_return_status => x_return_status
575 , x_mesg_token_tbl => x_mesg_token_tbl
576 ) ;
577
578 -- Convert old Eco Opeartion Record back to Routing Operation
579 Bom_Rtg_Pub.Convert_EcoSubRes_To_RtgSubRes
580 ( p_rev_sub_resource_rec => l_old_rev_sub_resource_rec
581 , p_rev_sub_res_unexp_rec => l_old_rev_sub_res_unexp_rec
582 , x_rtg_sub_resource_rec => x_old_sub_resource_rec
583 , x_rtg_sub_res_unexp_rec => x_old_sub_res_unexp_rec
584 ) ;
585
586
587 END Check_Existence ;
588
589
590 /******************************************************************
591 * Procedure : Check_Existence used by ECO BO
592 * and internally called by RTG BO
593 * Parameters IN : Sub Revised operation resource exposed column record
594 * Sub Revised operation resource unexposed column record
595 * Parameters out: Old Sub Revised operation resource exposed column record
596 * Old Sub Revised operation resource unexposed column record
597 * Mesg Token Table
598 * Return Status
599 * Purpose : Check_Existence will query using the primary key
600 * information and return a success if the operation
601 * resource is CREATE and the record EXISTS or will
602 * return an error if the substitute operation resource
603 * is UPDATE and record DOES NOT EXIST.
604 * In case of UPDATE if record exists, then the procedure
605 * will return old record in the old entity parameters
606 * with a success status.
607 *********************************************************************/
608
609 PROCEDURE Check_Existence
610 ( p_rev_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type
611 , p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
612 , x_old_rev_sub_resource_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type
613 , x_old_rev_sub_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
614 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
615 , x_return_status IN OUT NOCOPY VARCHAR2
616 )
617 IS
618 l_Token_Tbl Error_Handler.Token_Tbl_Type;
619 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
620 l_return_status VARCHAR2(1);
621 l_default_basis_type NUMBER;
622
623 BEGIN
624
625 l_return_status := FND_API.G_RET_STS_SUCCESS;
626 x_return_status := FND_API.G_RET_STS_SUCCESS;
627
628
629 l_Token_Tbl(1).token_name := 'SUB_RESOURCE_CODE';
630 l_Token_Tbl(1).token_value :=
631 p_rev_sub_resource_rec.sub_resource_code ;
632 l_Token_Tbl(2).token_name := 'SCHEDULE_SEQ_NUMBER';
633 l_Token_Tbl(2).token_value :=
634 nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number) ;
635 l_Token_Tbl(3).token_name := 'REVISED_ITEM_NAME';
636 l_Token_Tbl(3).token_value := p_rev_sub_resource_rec.revised_item_name;
637
638 -- If basis type is null then take the resource's default basis type
639 IF ( p_rev_sub_resource_rec.basis_type IS NULL
640 OR p_rev_sub_resource_rec.basis_type = FND_API.G_MISS_NUM )
641 THEN
642 BEGIN
643 SELECT br.DEFAULT_BASIS_TYPE
644 INTO l_default_basis_type
645 FROM BOM_RESOURCES br
646 WHERE br.RESOURCE_ID = p_rev_sub_res_unexp_rec.resource_id;
647 EXCEPTION
648 WHEN NO_DATA_FOUND THEN
649 l_default_basis_type := 1;
650 END;
651 ELSE
652 l_default_basis_type := p_rev_sub_resource_rec.basis_type;
653 END IF;
654
655 Bom_Sub_Op_Res_Util.Query_Row
656 ( p_resource_id => p_rev_sub_res_unexp_rec.resource_id
657 , p_substitute_group_number => nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number)
658 , p_operation_sequence_id => p_rev_sub_res_unexp_rec.operation_sequence_id
659 , p_replacement_group_number => p_rev_sub_resource_rec.replacement_group_number--bug 2489765
660 , p_basis_type => l_default_basis_type
661 , p_acd_type => p_rev_sub_resource_rec.acd_type
662 , p_mesg_token_tbl => l_mesg_token_tbl
663 , x_rev_sub_resource_rec => x_old_rev_sub_resource_rec
664 , x_rev_sub_res_unexp_rec => x_old_rev_sub_res_unexp_rec
665 , x_mesg_token_tbl => l_mesg_token_tbl
666 , x_return_status => l_return_status
667 ) ;
668
669 IF l_return_status = BOM_Rtg_Globals.G_RECORD_FOUND AND
670 p_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
671 THEN
672 Error_Handler.Add_Error_Token
673 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
674 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
675 , p_message_name => 'BOM_SUB_RES_ALREADY_EXISTS'
676 , p_token_tbl => l_token_tbl
677 ) ;
678 l_return_status := FND_API.G_RET_STS_ERROR ;
679
680 ELSIF l_return_status = BOM_Rtg_Globals.G_RECORD_NOT_FOUND AND
681 p_rev_sub_resource_rec.transaction_type IN
682 (BOM_Rtg_Globals.G_OPR_UPDATE, BOM_Rtg_Globals.G_OPR_DELETE)
683 THEN
684 Error_Handler.Add_Error_Token
685 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
686 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
687 , p_message_name => 'BOM_SUB_RES_DOESNOT_EXIST'
688 , p_token_tbl => l_token_tbl
689 ) ;
690 l_return_status := FND_API.G_RET_STS_ERROR ;
691
692 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
693 THEN
694 Error_Handler.Add_Error_Token
695 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
696 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
697 , p_message_name => NULL
698 , p_message_text => 'Unexpected error while existence verification of '
699 || 'Sub Operation Resources '
700 || p_rev_sub_resource_rec.sub_resource_code
701 || ': Schedule Seq Num '
702 || nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number)
703 , p_token_tbl => l_token_tbl
704 ) ;
705 ELSE
706 l_return_status := FND_API.G_RET_STS_SUCCESS;
707 END IF ;
708
709 x_return_status := l_return_status;
710 x_mesg_token_tbl := l_Mesg_Token_Tbl;
711
712 END Check_Existence;
713
714
715
716 /********************************************************************
717 * Procedure : Check_Attributes used by RTG BO
718 * Parameters IN : Sub Operation Resource exposed column record
719 * Sub Operation Resource unexposed column record
720 * Parameters out: Return Status
721 * Message Token Table
722 * Purpose : Convert Routing Sub Operation Resource to ECO Sub Operation
723 * Resource and Call Check_Attributes for ECO BO.
724 * Check_Attributes will verify the exposed attributes
725 * of the operation resource record in their own entirety.
726 * No cross entity validations will be performed.
727 ********************************************************************/
728 PROCEDURE Check_Attributes
729 ( p_sub_resource_rec IN Bom_Rtg_Pub.Sub_Resource_Rec_Type
730 , p_sub_res_unexp_rec IN Bom_Rtg_Pub.Sub_Res_Unexposed_Rec_Type
731 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
732 , x_return_status IN OUT NOCOPY VARCHAR2
733 )
734 IS
735
736 l_rev_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type ;
737 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type ;
738
739 BEGIN
740
741 -- Convert Routing Operation to ECO Operation
742 Bom_Rtg_Pub.Convert_RtgSubRes_To_EcoSubRes
743 ( p_rtg_sub_resource_rec => p_sub_resource_rec
744 , p_rtg_sub_res_unexp_rec => p_sub_res_unexp_rec
745 , x_rev_sub_resource_rec => l_rev_sub_resource_rec
746 , x_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
747 ) ;
748
749 -- Call Check Attributes procedure
750 Bom_Validate_Sub_Op_Res.Check_Attributes
751 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
752 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
753 , x_return_status => x_return_status
754 , x_mesg_token_tbl => x_mesg_token_tbl
755 ) ;
756
757 END Check_Attributes ;
758
759
760 /***************************************************************
761 * Procedure : Check_Attribute (Validation) for CREATE and UPDATE
762 * by ECO BO and internally called by RTG BO
763 * Parameters IN : Revised Sub Operation Resource exposed column record
764 * Revised Sub Operation Resource unexposed column record
765 * Parameters out: Return Status
766 * Message Token Table
767 * Purpose : Attribute validation procedure will validate each
768 * attribute of Sub Revised operation resource in its entirety.
769 * If the validation of a column requires looking at some
770 * other columns value then the validation is done at
771 * the Entity level instead.
772 * All errors in the attribute validation are accumulated
773 * before the procedure returns with a Return_Status
774 * of 'E'.
775 *********************************************************************/
776 PROCEDURE Check_Attributes
777 ( p_rev_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type
778 , p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
779 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
780 , x_return_status IN OUT NOCOPY VARCHAR2
781 )
782 IS
783
784 l_return_status VARCHAR2(1) ;
785 l_err_text VARCHAR2(2000) ;
786 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
787 l_Token_Tbl Error_Handler.Token_Tbl_Type ;
788
789 BEGIN
790
791 l_return_status := FND_API.G_RET_STS_SUCCESS;
792 x_return_status := FND_API.G_RET_STS_SUCCESS;
793
794 -- Set the first token to be equal to the operation sequence number
795 l_Token_Tbl(1).token_name := 'SUB_RESOURCE_CODE';
796 l_Token_Tbl(1).token_value :=
797 p_rev_sub_resource_rec.sub_resource_code ;
798 l_Token_Tbl(2).token_name := 'SCHEDULE_SEQ_NUMBER';
799 l_Token_Tbl(2).token_value :=
800 nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number) ;
801
802 --
803 -- Check if the user is trying to update a record with
804 -- missing value when the column value is required.
805 --
806 IF p_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
807 THEN
808
809 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
810 ('Sub Operation Resource Attr Validation: Missing Value. . . ' ) ;
811 END IF;
812
813 -- New Sub Resource Code
814 IF p_rev_sub_resource_rec.new_sub_resource_code = FND_API.G_MISS_CHAR
815 THEN
816 Error_Handler.Add_Error_Token
817 ( p_Message_Name => 'BOM_SUB_RESCODE_MISSING'
818 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
819 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
820 , p_Token_Tbl => l_Token_Tbl
821 );
822 l_return_status := FND_API.G_RET_STS_ERROR;
823 END IF ;
824
825
826
827 -- Replacement Group Number
828 IF p_rev_sub_resource_rec.replacement_group_number = FND_API.G_MISS_NUM
829 THEN
830 Error_Handler.Add_Error_Token
831 ( p_Message_Name => 'BOM_SUB_RES_RPLAC_GNUM_MISSING'
832 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
833 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
834 , p_Token_Tbl => l_Token_Tbl
835 );
836 l_return_status := FND_API.G_RET_STS_ERROR;
837 END IF ;
838
839
840 -- New Replacement Group Number -- bug 3741570
841 IF p_rev_sub_resource_rec.new_replacement_group_number = FND_API.G_MISS_NUM
842 THEN
843 Error_Handler.Add_Error_Token
844 ( p_Message_Name => 'BOM_SUB_RES_RPLAC_GNUM_MISSING'
845 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
846 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
847 , p_Token_Tbl => l_Token_Tbl
848 );
849 l_return_status := FND_API.G_RET_STS_ERROR;
850 END IF;
851
852
853 -- Standard Rate Flag
854 IF p_rev_sub_resource_rec.standard_rate_flag = FND_API.G_MISS_NUM
855 THEN
856 Error_Handler.Add_Error_Token
857 ( p_Message_Name => 'BOM_SUB_RES_STD_RATE_MISSING'
858 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
859 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
860 , p_Token_Tbl => l_Token_Tbl
861 );
862 l_return_status := FND_API.G_RET_STS_ERROR;
863 END IF;
864
865
866 -- Assigned Units
867 IF p_rev_sub_resource_rec.assigned_units = FND_API.G_MISS_NUM
868 THEN
869 Error_Handler.Add_Error_Token
870 ( p_Message_Name => 'BOM_SUB_RES_ASGND_UNTS_MISSING'
871 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
872 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
873 , p_Token_Tbl => l_Token_Tbl
874 );
875 l_return_status := FND_API.G_RET_STS_ERROR;
876 END IF;
877
878
879 -- Usage Rate or Amount
880 IF p_rev_sub_resource_rec.usage_rate_or_amount = FND_API.G_MISS_NUM
881 THEN
882 Error_Handler.Add_Error_Token
883 ( p_Message_Name => 'BOM_SUB_RES_RATE_AMT_MISSING'
884 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
885 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
886 , p_Token_Tbl => l_Token_Tbl
887 );
888 l_return_status := FND_API.G_RET_STS_ERROR;
889 END IF;
890
891 -- Usage Rate or Amount Inverse
892 IF p_rev_sub_resource_rec.usage_rate_or_amount_inverse = FND_API.G_MISS_NUM
893 THEN
894 Error_Handler.Add_Error_Token
895 ( p_Message_Name => 'BOM_SUB_RES_RTAMT_INVR_MISSING'
896 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
897 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
898 , p_Token_Tbl => l_Token_Tbl
899 );
900 l_return_status := FND_API.G_RET_STS_ERROR;
901 END IF;
902
903 -- Basis Type
904 IF p_rev_sub_resource_rec.basis_type = FND_API.G_MISS_NUM
905 THEN
906 Error_Handler.Add_Error_Token
907 ( p_Message_Name => 'BOM_SUB_RES_BASISTYPE_MISSING'
908 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
909 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
910 , p_Token_Tbl => l_Token_Tbl
911 );
912 l_return_status := FND_API.G_RET_STS_ERROR;
913 END IF;
914
915 -- New Basis Type
916 IF p_rev_sub_resource_rec.new_basis_type = FND_API.G_MISS_NUM
917 THEN
918 Error_Handler.Add_Error_Token
919 ( p_Message_Name => 'BOM_SUB_RES_BASISTYPE_MISSING'
920 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
921 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
922 , p_Token_Tbl => l_Token_Tbl
923 );
924 l_return_status := FND_API.G_RET_STS_ERROR;
925 END IF;
926
927 -- Schedule Flag
928 IF p_rev_sub_resource_rec.schedule_flag = FND_API.G_MISS_NUM
929 THEN
930 Error_Handler.Add_Error_Token
931 ( p_Message_Name => 'BOM_SUB_RES_SCHED_FLAG_MISSING'
932 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
933 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
934 , p_Token_Tbl => l_Token_Tbl
935 );
936 l_return_status := FND_API.G_RET_STS_ERROR;
937 END IF ;
938
939
940 -- Autocharge Type
941 IF p_rev_sub_resource_rec.autocharge_type = FND_API.G_MISS_NUM
942 THEN
943 Error_Handler.Add_Error_Token
944 ( p_Message_Name => 'BOM_SUB_RES_ACHRG_TYPE_MISSING'
945 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
946 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
947 , p_Token_Tbl => l_Token_Tbl
948 );
949 l_return_status := FND_API.G_RET_STS_ERROR;
950 END IF ;
951
952 END IF ;
953
954 --
955 -- Check if the user is trying to create/update a record with
956 -- invalid value.
957 --
958
959 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
960 ('Sub Operation Resource Attr Validation: Invalid Value. . . ' || l_return_status) ;
961 END IF;
962
963
964 -- New Sub Resource Code
965 IF p_rev_sub_resource_rec.new_sub_resource_code IS NOT NULL
966 AND p_rev_sub_resource_rec.new_sub_resource_code <> FND_API.G_MISS_CHAR
967 AND BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
968 THEN
969 Error_Handler.Add_Error_Token
970 ( p_Message_Name => 'BOM_SUB_RES_CODE_NOTUPDATE'
971 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
972 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
973 , p_Token_Tbl => l_Token_Tbl
974 );
975 l_return_status := FND_API.G_RET_STS_ERROR;
976 END IF ;
977
978
979 -- Replacement Group Num
980 IF p_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
981 AND (p_rev_sub_resource_rec.replacement_group_number IS NULL
982 OR p_rev_sub_resource_rec.replacement_group_number = FND_API.G_MISS_NUM)
983 THEN
984 Error_Handler.Add_Error_Token
985 ( p_Message_Name => 'BOM_SUB_RES_REPLCMNT_GNUM_NULL'
986 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
987 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
988 , p_Token_Tbl => l_Token_Tbl
989 );
990 l_return_status := FND_API.G_RET_STS_ERROR ;
991
992 ELSIF p_rev_sub_resource_rec.replacement_group_number IS NOT NULL AND
993 p_rev_sub_resource_rec.replacement_group_number <> FND_API.G_MISS_NUM AND
994 ( p_rev_sub_resource_rec.replacement_group_number < 1
995 OR p_rev_sub_resource_rec.replacement_group_number > 9999 )
996 THEN
997 Error_Handler.Add_Error_Token
998 ( p_Message_Name => 'BOM_SUB_RES_RPLMT_GNUM_INVALID'
999 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1000 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1001 , p_Token_Tbl => l_Token_Tbl
1002 );
1003 l_return_status := FND_API.G_RET_STS_ERROR ;
1004 END IF;
1005
1006
1007 -- New Replacement Group Number -- bug 3741570
1008 IF p_rev_sub_resource_rec.replacement_group_number IS NOT NULL
1009 AND p_rev_sub_resource_rec.replacement_group_number <> FND_API.G_MISS_NUM
1010 AND ( p_rev_sub_resource_rec.replacement_group_number < 1
1011 OR p_rev_sub_resource_rec.replacement_group_number > 9999 )
1012 THEN
1013 Error_Handler.Add_Error_Token
1014 ( p_Message_Name => 'BOM_SUB_RES_RPLMT_GNUM_INVALID'
1015 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1016 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1017 , p_Token_Tbl => l_Token_Tbl
1018 );
1019 l_return_status := FND_API.G_RET_STS_ERROR ;
1020 END IF;
1021
1022
1023 -- Standard Rate Flag
1024 IF p_rev_sub_resource_rec.standard_rate_flag IS NOT NULL
1025 AND p_rev_sub_resource_rec.standard_rate_flag NOT IN (1,2)
1026 AND p_rev_sub_resource_rec.standard_rate_flag <> FND_API.G_MISS_NUM
1027 THEN
1028 Error_Handler.Add_Error_Token
1029 ( p_Message_Name => 'BOM_SUB_RES_STD_RATE_INVALID'
1030 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1031 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1032 , p_Token_Tbl => l_Token_Tbl
1033 );
1034 l_return_status := FND_API.G_RET_STS_ERROR;
1035 END IF;
1036
1037
1038 -- Principle Flag
1039 IF p_rev_sub_resource_rec.principle_flag IS NOT NULL
1040 AND p_rev_sub_resource_rec.principle_flag NOT IN (1,2)
1041 AND p_rev_sub_resource_rec.principle_flag <> FND_API.G_MISS_NUM
1042 THEN
1043 Error_Handler.Add_Error_Token
1044 ( p_Message_Name => 'BOM_SUB_RES_PCLFLAG_INVALID'
1045 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1046 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1047 , p_Token_Tbl => l_Token_Tbl
1048 );
1049 l_return_status := FND_API.G_RET_STS_ERROR;
1050 END IF;
1051
1052 -- Resource Offset Percent
1053 IF p_rev_sub_resource_rec.resource_offset_percent IS NOT NULL
1054 AND (p_rev_sub_resource_rec.resource_offset_percent < 0
1055 OR p_rev_sub_resource_rec.resource_offset_percent > 100 )
1056 AND p_rev_sub_resource_rec.resource_offset_percent <> FND_API.G_MISS_NUM
1057 THEN
1058 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1059 THEN
1060 Error_Handler.Add_Error_Token
1061 ( p_Message_Name => 'BOM_SUB_RES_OFFSET_PCT_INVALID'
1062 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1063 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1064 , p_Token_Tbl => l_Token_Tbl
1065 ) ;
1066 END IF ;
1067 l_return_status := FND_API.G_RET_STS_ERROR;
1068 END IF ;
1069
1070
1071 -- Assigned Units
1072 IF p_rev_sub_resource_rec.assigned_units IS NOT NULL
1073 AND p_rev_sub_resource_rec.assigned_units <= 0.00001
1074 AND p_rev_sub_resource_rec.assigned_units <> FND_API.G_MISS_NUM
1075 THEN
1076 Error_Handler.Add_Error_Token
1077 ( p_Message_Name => 'BOM_SUB_RES_ASSGN_UNTS_INVALID'
1078 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1079 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1080 , p_Token_Tbl => l_Token_Tbl
1081 );
1082 l_return_status := FND_API.G_RET_STS_ERROR;
1083 END IF;
1084
1085
1086 -- Basis Type
1087 IF p_rev_sub_resource_rec.basis_type IS NOT NULL
1088 AND p_rev_sub_resource_rec.basis_type NOT IN (1,2)
1089 AND p_rev_sub_resource_rec.basis_type <> FND_API.G_MISS_NUM
1090 THEN
1091 Error_Handler.Add_Error_Token
1092 ( p_Message_Name => 'BOM_SUB_RES_BASISTYPE_INVALID'
1093 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1094 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1095 , p_Token_Tbl => l_Token_Tbl
1096 );
1097 l_return_status := FND_API.G_RET_STS_ERROR;
1098 END IF;
1099
1100 -- New Basis Type
1101 IF p_rev_sub_resource_rec.new_basis_type IS NOT NULL
1102 AND p_rev_sub_resource_rec.new_basis_type NOT IN (1,2)
1103 AND p_rev_sub_resource_rec.new_basis_type <> FND_API.G_MISS_NUM
1104 THEN
1105 Error_Handler.Add_Error_Token
1106 ( p_Message_Name => 'BOM_SUB_RES_BASISTYPE_INVALID'
1107 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1108 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1109 , p_Token_Tbl => l_Token_Tbl
1110 );
1111 l_return_status := FND_API.G_RET_STS_ERROR;
1112 END IF;
1113
1114 -- Schedule Flag
1115 IF p_rev_sub_resource_rec.schedule_flag IS NOT NULL
1116 AND p_rev_sub_resource_rec.schedule_flag NOT IN (1,2,3,4)
1117 AND p_rev_sub_resource_rec.schedule_flag <> FND_API.G_MISS_NUM
1118 THEN
1119 Error_Handler.Add_Error_Token
1120 ( p_Message_Name => 'BOM_SUB_RES_SCHED_FLAG_INVALID'
1121 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1122 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1123 , p_Token_Tbl => l_Token_Tbl
1124 );
1125 l_return_status := FND_API.G_RET_STS_ERROR;
1126 END IF ;
1127
1128
1129 -- Autocharge Type
1130 IF p_rev_sub_resource_rec.autocharge_type IS NOT NULL
1131 AND p_rev_sub_resource_rec.autocharge_type NOT IN (1,2,3,4)
1132 AND p_rev_sub_resource_rec.autocharge_type <> FND_API.G_MISS_NUM
1133 THEN
1134 Error_Handler.Add_Error_Token
1135 ( p_Message_Name => 'BOM_SUB_RES_ACHRG_TYPE_INVALID'
1136 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1137 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1138 , p_Token_Tbl => l_Token_Tbl
1139 );
1140 l_return_status := FND_API.G_RET_STS_ERROR;
1141 END IF ;
1142
1143
1144 -- ACD Type
1145 IF p_rev_sub_resource_rec.acd_type IS NOT NULL
1146 AND p_rev_sub_resource_rec.acd_type NOT IN
1147 (l_ACD_ADD, l_ACD_DISABLE)
1148 AND BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
1149 THEN
1150
1151 l_token_tbl(2).token_name := 'ACD_TYPE';
1152 l_token_tbl(2).token_value := p_rev_sub_resource_rec.acd_type;
1153
1154 Error_Handler.Add_Error_Token
1155 ( p_Message_Name => 'BOM_SUB_RES_ACD_TYPE_INVALID'
1156 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1157 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1158 , p_Token_Tbl => l_Token_Tbl
1159 );
1160 l_return_status := FND_API.G_RET_STS_ERROR ;
1161 END IF ;
1162
1163 -- Schedule Sequence Number
1164 IF (p_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
1165 AND p_rev_sub_resource_rec.schedule_sequence_number IS NULL)
1166 OR (p_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
1167 AND p_rev_sub_resource_rec.schedule_sequence_number = FND_API.G_MISS_NUM)
1168 OR p_rev_sub_resource_rec.schedule_sequence_number = 0
1169 THEN
1170 Error_Handler.Add_Error_Token
1171 ( p_Message_Name => 'BOM_SSN_ZERO_VALUE'
1172 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1173 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1174 --, p_Token_Tbl => l_Token_Tbl
1175 );
1176 l_return_status := FND_API.G_RET_STS_ERROR ;
1177 END IF;
1178
1179 -- Done validating attributes
1180 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1181 ('Sub Operation Resource Attr Validation completed with return_status: ' || l_return_status) ;
1182 END IF;
1183
1184 x_return_status := l_return_status;
1185 x_mesg_token_tbl := l_Mesg_Token_Tbl;
1186
1187 EXCEPTION
1188 WHEN OTHERS THEN
1189 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1190 ('Some unknown error in Attribute Validation . . .' || SQLERRM );
1191 END IF ;
1192
1193
1194 l_err_text := G_PKG_NAME || ' Validation (Attr. Validation) '
1195 || substrb(SQLERRM,1,200);
1196 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
1197
1198 Error_Handler.Add_Error_Token
1199 ( p_message_name => NULL
1200 , p_message_text => l_err_text
1201 , p_mesg_token_tbl => l_mesg_token_tbl
1202 , x_mesg_token_tbl => l_mesg_token_tbl
1203 ) ;
1204
1205 -- Return the status and message table.
1206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1207 x_mesg_token_tbl := l_mesg_token_tbl ;
1208
1209
1210 END Check_Attributes ;
1211
1212
1213 /*******************************************************************
1214 * Procedure : Check_Entity used by RTG BO
1215 * Parameters IN : Sub Operation Resource exposed column record
1216 * Sub Operation Resource unexposed column record
1217 * Old Sub Operation Resource exposed column record
1218 * Old Sub Operation Resource unexposed column record
1219 * Parameters out: Return Status
1220 * Message Token Table
1221 * Purpose : Convert Routing Op Resource to ECO Op Resource and
1222 * Call Check_Entity for ECO BO.
1223 * Procedure will execute the business logic and will
1224 * also perform any required cross entity validations
1225 *******************************************************************/
1226 PROCEDURE Check_Entity
1227 ( p_sub_resource_rec IN Bom_Rtg_Pub.Sub_Resource_Rec_Type
1228 , p_sub_res_unexp_rec IN Bom_Rtg_Pub.Sub_Res_Unexposed_Rec_Type
1229 , p_old_sub_resource_rec IN Bom_Rtg_Pub.Sub_Resource_Rec_Type
1230 , p_old_sub_res_unexp_rec IN Bom_Rtg_Pub.Sub_Res_Unexposed_Rec_Type
1231 , x_sub_resource_rec IN OUT NOCOPY Bom_Rtg_Pub.Sub_Resource_Rec_Type
1232 , x_sub_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Sub_Res_Unexposed_Rec_Type
1233 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1234 , x_return_status IN OUT NOCOPY VARCHAR2
1235 )
1236 IS
1237 l_rev_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type ;
1238 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type ;
1239 l_old_rev_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type ;
1240 l_old_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type ;
1241
1242 BEGIN
1243 -- Convert Routing Operation to ECO Operation
1244 Bom_Rtg_Pub.Convert_RtgSubRes_To_EcoSubRes
1245 ( p_rtg_sub_resource_rec => p_sub_resource_rec
1246 , p_rtg_sub_res_unexp_rec => p_sub_res_unexp_rec
1247 , x_rev_sub_resource_rec => l_rev_sub_resource_rec
1248 , x_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
1249 ) ;
1250
1251
1252 -- Also Convert Old Routing Operation to Old ECO Operation
1253 Bom_Rtg_Pub.Convert_RtgSubRes_To_EcoSubRes
1254 ( p_rtg_sub_resource_rec => p_old_sub_resource_rec
1255 , p_rtg_sub_res_unexp_rec => p_old_sub_res_unexp_rec
1256 , x_rev_sub_resource_rec => l_old_rev_sub_resource_rec
1257 , x_rev_sub_res_unexp_rec => l_old_rev_sub_res_unexp_rec
1258 ) ;
1259
1260 -- Call Check_Entity
1261 Bom_Validate_Sub_Op_Res.Check_Entity
1262 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
1263 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
1264 , p_old_rev_sub_resource_rec => l_old_rev_sub_resource_rec
1265 , p_old_rev_sub_res_unexp_rec => l_old_rev_sub_res_unexp_rec
1266 , p_control_rec => Bom_Rtg_Pub.G_DEFAULT_CONTROL_REC
1267 , x_rev_sub_resource_rec => l_rev_sub_resource_rec
1268 , x_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
1269 , x_return_status => x_return_status
1270 , x_mesg_token_tbl => x_mesg_token_tbl
1271 ) ;
1272
1273
1274 -- Convert Eco Op Resource Record back to Routing Op Resource
1275 Bom_Rtg_Pub.Convert_EcoSubRes_To_RtgSubRes
1276 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
1277 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
1278 , x_rtg_sub_resource_rec => x_sub_resource_rec
1279 , x_rtg_sub_res_unexp_rec => x_sub_res_unexp_rec
1280 ) ;
1281
1282
1283 END Check_Entity ;
1284
1285
1286 /*******************************************************************
1287 * Procedure : Check_Entity used by RTG BO and internally called by RTG BO
1288 * Parameters IN : Revised Sub Op Resource exposed column record
1289 * Revised Sub Op Resource unexposed column record
1290 * Old Revised Sub Op Resource exposed column record
1291 * Old Revised Sub Op Resource unexposed column record
1292 * Parameters out: Return Status
1293 * Message Token Table
1294 * Purpose : Check_Entity validate the entity for the correct
1295 * business logic. It will verify the values by running
1296 * checks on inter-dependent columns.
1297 * It will also verify that changes in one column value
1298 * does not invalidate some other columns.
1299 *******************************************************************/
1300 PROCEDURE Check_Entity
1301 ( p_rev_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type
1302 , p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
1303 , p_old_rev_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type
1304 , p_old_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
1305 , p_control_rec IN Bom_Rtg_Pub.Control_Rec_Type
1306 , x_rev_sub_resource_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type
1307 , x_rev_sub_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
1308 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1309 , x_return_status IN OUT NOCOPY VARCHAR2
1310 )
1311 IS
1312
1313 -- Variables
1314 l_eco_processed BOOLEAN ; -- Indicate ECO has been processed
1315
1316 l_hour_uom_code VARCHAR2(3) ; -- Hour UOM Code
1317 l_hour_uom_class VARCHAR2(10) ; -- Hour UOM Class
1318 l_res_uom_code VARCHAR2(3) ; -- Resource UOM Code
1319 l_res_uom_class VARCHAR2(10) ; -- Resource UOM Class
1320 l_temp_status VARCHAR2(1) ; -- Temp Error Status
1321 l_old_op_seq_id NUMBER := NULL ; -- Old Operation Sequence Id
1322 /* Added below 3 vars for fixing bug 6074930 */
1323 l_res_code VARCHAR2(10);
1324 l_res_code_2 VARCHAR2(10);
1325 l_res_id NUMBER;
1326
1327 l_rev_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_rec_Type ;
1328 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type ;
1329
1330 -- Error Handlig Variables
1331 l_return_status VARCHAR2(1);
1332 l_err_text VARCHAR2(2000) ;
1333 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
1334 l_token_tbl Error_Handler.Token_Tbl_Type;
1335
1336 l_get_setups NUMBER;
1337 l_batchable NUMBER;
1338
1339 CURSOR get_setups ( p_resource_id NUMBER
1340 , p_org_id NUMBER
1341 )
1342 IS
1343 SELECT count(setup_id)
1344 FROM bom_resource_setups
1345 WHERE resource_id = p_resource_id
1346 AND organization_id = p_org_id;
1347
1348
1349
1350 -- Check Rev Sub Op Resource exists
1351 CURSOR l_disable_subres_exist_csr
1352 ( p_resource_id NUMBER
1353 , p_sub_group_num NUMBER
1354 , p_op_seq_id NUMBER
1355 )
1356 IS
1357 SELECT 'Rev Sub Op Resource Not Exists'
1358 FROM DUAL
1359 WHERE NOT EXISTS (SELECT NULL
1360 FROM BOM_OPERATION_SEQUENCES bos
1361 , BOM_SUB_OPERATION_RESOURCES bsor
1362 WHERE bsor.substitute_group_num = p_sub_group_num
1363 AND bsor.resource_id = p_resource_id
1364 AND bsor.operation_sequence_id = bos.operation_sequence_id
1365 AND bos.operation_sequence_id = p_op_seq_id
1366 ) ;
1367
1368 -- Check Uniqueness
1369 CURSOR l_duplicate_csr ( p_resource_id NUMBER
1370 , p_substitute_group_number NUMBER
1371 , p_replacement_group_number NUMBER -- bug 3741570
1372 , p_op_seq_id NUMBER
1373 , p_acd_type NUMBER
1374 , p_basis_type NUMBER
1375 )
1376 IS
1377 SELECT 'Sub Res Duplicate'
1378 FROM DUAL
1379 WHERE EXISTS ( SELECT NULL
1380 FROM BOM_SUB_OPERATION_RESOURCES
1381 WHERE NVL(ACD_TYPE, 1) = NVL(p_acd_type, 1)
1382 AND BASIS_TYPE = p_basis_type
1383 AND RESOURCE_ID = p_resource_id
1384 AND SUBSTITUTE_GROUP_NUM = p_substitute_group_number
1385 AND REPLACEMENT_GROUP_NUM = p_replacement_group_number -- bug 3741570
1386 AND OPERATION_SEQUENCE_ID = p_op_seq_id
1387 ) ;
1388
1389
1390
1391 BEGIN
1392 --
1393 -- Initialize Common Record and Status
1394 --
1395
1396 l_rev_sub_resource_rec := p_rev_sub_resource_rec ;
1397 l_rev_sub_res_unexp_rec := p_rev_sub_res_unexp_rec ;
1398 l_return_status := FND_API.G_RET_STS_SUCCESS;
1399 x_return_status := FND_API.G_RET_STS_SUCCESS;
1400
1401 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1402 ('Performing Sub Op Resource Check Entitity Validation . . .') ;
1403 END IF ;
1404
1405 --
1406 -- Set the 1st token of Token Table to Revised Operation value
1407 --
1408 l_Token_Tbl(1).token_name := 'SUB_RESOURCE_CODE';
1409 l_Token_Tbl(1).token_value :=
1410 p_rev_sub_resource_rec.sub_resource_code ;
1411 l_Token_Tbl(2).token_name := 'SCHEDULE_SEQ_NUMBER';
1412 l_Token_Tbl(2).token_value :=
1413 nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number) ;
1414
1415
1416 -- The ECO can be updated but a warning needs to be generated and
1417 -- scheduled revised items need to be update to Open
1418 -- and the ECO status need to be changed to Not Submitted for Approval
1419
1420 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1421 ('Check if ECO has been approved and has a workflow process. . . ' || l_return_status) ;
1422 END IF ;
1423
1424 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
1425 THEN
1426 BOM_Rtg_Globals.Check_Approved_For_Process
1427 ( p_change_notice => l_rev_sub_resource_rec.eco_name
1428 , p_organization_id => l_rev_sub_res_unexp_rec.organization_id
1429 , x_processed => l_eco_processed
1430 , x_err_text => l_err_text
1431 ) ;
1432
1433 IF l_eco_processed THEN
1434 -- If the above process returns true then set the ECO approval.
1435 BOM_Rtg_Globals.Set_Request_For_Approval
1436 ( p_change_notice => l_rev_sub_resource_rec.eco_name
1437 , p_organization_id => l_rev_sub_res_unexp_rec.organization_id
1438 , x_err_text => l_err_text
1439 ) ;
1440
1441 END IF ;
1442 END IF;
1443
1444
1445 --
1446 -- Performing Entity Validation in Revised Sub Op Resource(ECO BO)
1447 --
1448 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
1449 THEN
1450 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1451 ('Performing Entitity Validation for Eco Routing :ACD Type. . .') ;
1452 END IF ;
1453
1454 --
1455 -- ACD Type
1456 -- If the Transaction Type is CREATE and the ACD_Type = Disable, then
1457 -- the sub operation resource should already exist for the revised operation.
1458 --
1459 IF l_rev_sub_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
1460 AND ( NVL(l_rev_sub_resource_rec.acd_type,l_ACD_ADD ) = l_ACD_DISABLE )
1461 THEN
1462
1463 FOR l_disable_subres_exist_rec IN l_disable_subres_exist_csr -- add replacement_group_num to this check??
1464 ( p_resource_id => l_rev_sub_res_unexp_rec.resource_id
1465 , p_sub_group_num => nvl(l_rev_sub_resource_rec.substitute_group_number, l_rev_sub_res_unexp_rec.substitute_group_number)
1466 , p_op_seq_id => l_rev_sub_res_unexp_rec.operation_sequence_id
1467 )
1468
1469 LOOP
1470 l_token_tbl(3).token_name := 'OP_SEQ_NUMBER';
1471 l_token_tbl(3).token_value := l_rev_sub_resource_rec.operation_sequence_number ;
1472
1473 Error_Handler.Add_Error_Token
1474 ( p_message_name => 'BOM_SUB_RES_DSBL_RES_NOT_FOUND'
1475 , p_mesg_token_tbl => l_Mesg_Token_Tbl
1476 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1477 , p_token_tbl => l_token_tbl
1478 ) ;
1479 l_token_tbl.delete(3) ;
1480 l_return_status := FND_API.G_RET_STS_ERROR ;
1481 END LOOP ;
1482 END IF ;
1483
1484
1485 --
1486 -- ACD Type,
1487 -- If the Transaction Type is CREATE and the ACD_Type of parent revised
1488 -- operation is Add then,the ACD_Type must be Add.
1489 -- Call BOM_Validate_Op_Res.Get_Rev_Op_ACD(p_op_seq_id to get parent revised
1490 -- operation's ACD Type
1491 --
1492 IF l_rev_sub_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
1493 THEN
1494 IF
1495 l_ACD_ADD =
1496 BOM_Validate_Op_Res.Get_Rev_Op_ACD(p_op_seq_id
1497 => l_rev_sub_res_unexp_rec.operation_sequence_id)
1498 AND NVL(l_rev_sub_resource_rec.acd_type,l_ACD_ADD ) <> l_ACD_ADD
1499 THEN
1500 l_token_tbl(3).token_name := 'OP_SEQ_NUMBER';
1501 l_token_tbl(3).token_value := l_rev_sub_resource_rec.operation_sequence_number ;
1502
1503 Error_Handler.Add_Error_Token
1504 ( p_message_name => 'BOM_SUB_RES_ACD_NOT_COMPATIBLE'
1505 , p_mesg_token_tbl => l_Mesg_Token_Tbl
1506 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1507 , p_token_tbl => l_token_tbl
1508 ) ;
1509
1510 l_token_tbl.delete(3) ;
1511 l_return_status := FND_API.G_RET_STS_ERROR ;
1512 END IF ;
1513 END IF ;
1514
1515
1516 --
1517 -- For UPDATE, ACD Type not updateable
1518 --
1519 IF l_rev_sub_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_UPDATE
1520 AND l_rev_sub_resource_rec.acd_type <> p_old_rev_sub_resource_rec.acd_type
1521 THEN
1522 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1523 THEN
1524 Error_Handler.Add_Error_Token
1525 ( p_message_name => 'BOM_SUB_RES_ACDTPNT_UPDATEABLE'
1526 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1527 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1528 , p_token_tbl => l_token_tbl
1529 ) ;
1530 END IF ;
1531 l_return_status := FND_API.G_RET_STS_ERROR ;
1532 END IF ;
1533
1534
1535 --
1536 -- Verify the ECO by WO Effectivity, If ECO by WO, Lot Num, Or Cum Qty, then
1537 -- Check if the operation resource exist in the WO or Lot Num.
1538 --
1539 IF l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
1540 THEN
1541 IF NOT Check_ECO_By_WO_Effectivity
1542 ( p_revised_item_sequence_id => l_rev_sub_res_unexp_rec.revised_item_sequence_id
1543 , p_operation_seq_num => l_rev_sub_resource_rec.operation_sequence_number
1544 , p_resource_id => l_rev_sub_res_unexp_rec.resource_id
1545 , p_sub_group_num => nvl(l_rev_sub_resource_rec.substitute_group_number, l_rev_sub_res_unexp_rec.substitute_group_number) )
1546 THEN
1547 l_token_tbl(1).token_name := 'SUB_RESOURCE_CODE';
1548 l_token_tbl(1).token_value :=
1549 p_rev_sub_resource_rec.sub_resource_code ;
1550 l_token_tbl(2).token_name := 'SCHEDULE_SEQ_NUMBER';
1551 l_token_tbl(2).token_value :=
1552 nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number) ;
1553 l_token_tbl(3).token_name := 'REVISED_ITEM_NAME';
1554 l_token_tbl(3).token_value := l_rev_sub_resource_rec.revised_item_name;
1555
1556 Error_Handler.Add_Error_Token
1557 ( p_message_name => 'BOM_SUB_RES_RITECOWOEF_INVALID'
1558 , p_mesg_token_tbl => l_Mesg_Token_Tbl
1559 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1560 , p_token_tbl => l_token_tbl
1561 );
1562 l_return_status := FND_API.G_RET_STS_ERROR;
1563
1564 l_Token_Tbl(1).token_name := 'SUB_RESOURCE_CODE';
1565 l_Token_Tbl(1).token_value :=
1566 p_rev_sub_resource_rec.sub_resource_code ;
1567 l_Token_Tbl(2).token_name := 'SCHEDULE_SEQ_NUMBER';
1568 l_Token_Tbl(2).token_value :=
1569 nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number) ;
1570 l_token_tbl.delete(3) ;
1571 END IF ;
1572 END IF ;
1573
1574
1575 END IF ; -- ECO BO Validation
1576
1577
1578 -- Validation for Assigned Units
1579 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
1580 Error_Handler.Write_Debug ('Validating the Assigned Units for a Batchable Alternate Resource . . .') ;
1581 END IF;
1582
1583 IF l_rev_sub_resource_rec.Transaction_Type IN
1584 (BOM_Rtg_Globals.G_OPR_CREATE, BOM_Rtg_Globals.G_OPR_UPDATE)
1585 THEN
1586 --
1587 -- APS Enhancement for Routings.
1588 -- Verify that if a resource has setups defined, or is Batchable then
1589 -- the Assigned Units for that Resource have to be 1.
1590 --
1591 IF p_rev_sub_resource_rec.assigned_units <> FND_API.G_MISS_NUM THEN
1592 OPEN get_setups (p_rev_sub_res_unexp_rec.resource_id, p_rev_sub_res_unexp_rec.organization_id);
1593 FETCH get_setups INTO l_get_setups;
1594 CLOSE get_setups;
1595 SELECT nvl(batchable,2) INTO l_batchable
1596 FROM bom_resources
1597 WHERE resource_id = p_rev_sub_res_unexp_rec.resource_id;
1598 IF (l_get_setups > 0 or l_batchable = 1) THEN
1599 IF p_rev_sub_resource_rec.assigned_units <> 1 THEN
1600 l_Token_Tbl(2).token_name := 'RES_SEQ_NUMBER';
1601 --l_Token_Tbl(2).token_value := p_rev_sub_resource_rec.Sub_Resource_Code;
1602 l_Token_Tbl(2).token_value := p_rev_sub_resource_rec.Schedule_Sequence_Number;
1603 Error_Handler.Add_Error_Token
1604 ( p_Message_Name => 'BOM_SUBRES_ASSIGND_UNITS_WRONG'
1605 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1606 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1607 , p_Token_Tbl => l_Token_Tbl
1608 );
1609 l_return_status := FND_API.G_RET_STS_ERROR ;
1610 END IF;
1611 END IF;
1612 END IF;
1613 END IF;
1614
1615
1616 --
1617 -- For UPDATE
1618 -- Validation specific to the Transaction Type of Update
1619 --
1620 IF l_rev_sub_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_UPDATE
1621
1622 -- In this release, Acd type : Change is not allowed.
1623 --
1624 -- OR
1625 -- (l_rev_sub_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
1626 -- AND l_rev_sub_resource_rec.acd_type = l_ACD_CHANGE
1627 -- )
1628 THEN
1629 NULL ;
1630 END IF ; -- Transation: UPDATE
1631
1632
1633 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1634 ('End of Validation specific to the Transaction Type of Update' || l_return_status) ;
1635 END IF ;
1636
1637 --
1638 -- Validation for Transaction Type : Create and Update
1639 --
1640 IF l_rev_sub_resource_rec.transaction_type IN
1641 ( BOM_Rtg_Globals.G_OPR_CREATE, BOM_Rtg_Globals.G_OPR_UPDATE )
1642 THEN
1643
1644 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1645 ('Common Validateion for Transaction Type : Create and Update . . . . ' || l_return_status) ;
1646 END IF ;
1647
1648 --
1649 -- Resource Id
1650 -- Check if valid resource id exists and belongs to depatment
1651 -- Call BOM_Validate_Op_Res.Val_Resource_Id
1652 --
1653
1654 IF ( ( NVL(l_rev_sub_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
1655 AND l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
1656 OR l_rev_sub_res_unexp_rec.resource_id <>
1657 NVL(l_rev_sub_res_unexp_rec.new_resource_id, l_rev_sub_res_unexp_rec.resource_id )
1658 )
1659 THEN
1660
1661
1662 /* Call Val_Resource_Id */
1663 BOM_Validate_Op_Res.Val_Resource_Id
1664 ( p_resource_id => NVL(l_rev_sub_res_unexp_rec.new_resource_id,
1665 l_rev_sub_res_unexp_rec.resource_id )
1666 , p_op_seq_id => l_rev_sub_res_unexp_rec.operation_sequence_id
1667 , x_return_status => l_temp_status
1668 ) ;
1669
1670
1671 IF l_temp_status = FND_API.G_RET_STS_ERROR
1672 THEN
1673 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1674 THEN
1675 Error_Handler.Add_Error_Token
1676 ( p_message_name => 'BOM_SUB_RES_RESID_INVALID'
1677 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1678 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1679 , p_Token_Tbl => l_Token_Tbl
1680 ) ;
1681 END IF ;
1682
1683 l_return_status := FND_API.G_RET_STS_ERROR ;
1684
1685 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1686 ('Check if Resource is enabled. . . . ' || l_return_status) ;
1687 END IF ;
1688
1689 END IF ;
1690 END IF ;
1691
1692 --
1693 -- Check Uniqueness of Sub Op Resource Record
1694 --
1695 IF ( ( NVL(l_rev_sub_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
1696 AND l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
1697 OR l_rev_sub_res_unexp_rec.resource_id <>
1698 NVL(l_rev_sub_res_unexp_rec.new_resource_id, l_rev_sub_res_unexp_rec.resource_id )
1699 OR l_rev_sub_resource_rec.replacement_Group_number <> -- bug 3741570
1700 NVL(l_rev_sub_resource_rec.new_replacement_Group_number, l_rev_sub_resource_rec.replacement_Group_number)
1701 OR l_rev_sub_resource_rec.basis_type <>
1702 NVL(l_rev_sub_resource_rec.new_basis_type, l_rev_sub_resource_rec.basis_type)
1703 )
1704 THEN
1705 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_RTG_BO
1706 AND l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
1707 THEN
1708
1709 FOR l_duplicate_rec IN l_duplicate_csr
1710 ( p_resource_id => NVL(l_rev_sub_res_unexp_rec.new_resource_id,
1711 l_rev_sub_res_unexp_rec.resource_id )
1712 , p_substitute_group_number => nvl(l_rev_sub_resource_rec.substitute_group_number,
1713 l_rev_sub_res_unexp_rec.substitute_group_number)
1714 , p_replacement_group_number => NVL(l_rev_sub_resource_rec.new_replacement_Group_number, -- bug 3741570
1715 l_rev_sub_resource_rec.replacement_Group_number)
1716 , p_op_seq_id => l_rev_sub_res_unexp_rec.operation_sequence_id
1717 , p_acd_type => l_rev_sub_resource_rec.acd_type
1718 , p_basis_type => NVL(l_rev_sub_resource_rec.new_basis_type,
1719 l_rev_sub_resource_rec.basis_type)
1720 )
1721
1722 LOOP
1723 l_Token_Tbl(1).token_name := 'SUB_RESOURCE_CODE';
1724 l_Token_Tbl(1).token_value :=
1725 p_rev_sub_resource_rec.new_sub_resource_code ;
1726 l_token_tbl(3).token_name := 'OP_SEQ_NUMBER';
1727 l_token_tbl(3).token_value := l_rev_sub_resource_rec.operation_sequence_number ;
1728
1729 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1730 THEN
1731 Error_Handler.Add_Error_Token
1732 ( p_message_name => 'BOM_SUB_RES_NOTUNIQUE'
1733 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1734 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1735 , p_Token_Tbl => l_Token_Tbl
1736 ) ;
1737 END IF ;
1738
1739 l_return_status := FND_API.G_RET_STS_ERROR ;
1740
1741 l_Token_Tbl(1).token_name := 'SUB_RESOURCE_CODE';
1742 l_Token_Tbl(1).token_value :=
1743 p_rev_sub_resource_rec.sub_resource_code ;
1744 l_token_tbl.delete(3) ;
1745 END LOOP ;
1746 ELSIF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
1747 THEN
1748 l_old_op_seq_id := Get_Old_Op_Seq_Id
1749 (p_op_seq_id =>
1750 l_rev_sub_res_unexp_rec.operation_sequence_id ) ;
1751
1752
1753 FOR l_duplicate_rec IN l_duplicate_csr
1754 ( p_resource_id => NVL(l_rev_sub_res_unexp_rec.new_resource_id,
1755 l_rev_sub_res_unexp_rec.resource_id )
1756 , p_substitute_group_number => nvl(l_rev_sub_resource_rec.substitute_group_number,
1757 l_rev_sub_res_unexp_rec.substitute_group_number)
1758 , p_replacement_group_number => l_rev_sub_resource_rec.replacement_Group_number -- bug 3741570
1759 , p_op_seq_id => l_old_op_seq_id
1760 , p_acd_type => l_rev_sub_resource_rec.acd_type
1761 , p_basis_type => l_rev_sub_resource_rec.basis_type
1762 )
1763
1764 LOOP
1765 l_Token_Tbl(1).token_name := 'SUB_RESOURCE_CODE';
1766 l_Token_Tbl(1).token_value :=
1767 NVL(p_rev_sub_resource_rec.new_sub_resource_code,
1768 p_rev_sub_resource_rec.sub_resource_code ) ;
1769 l_token_tbl(3).token_name := 'OP_SEQ_NUMBER';
1770 l_token_tbl(3).token_value := l_rev_sub_resource_rec.operation_sequence_number ;
1771
1772 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1773 THEN
1774 Error_Handler.Add_Error_Token
1775 ( p_message_name => 'BOM_SUB_RES_NOTUNIQUE'
1776 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1777 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1778 , p_Token_Tbl => l_Token_Tbl
1779 ) ;
1780 END IF ;
1781
1782 l_return_status := FND_API.G_RET_STS_ERROR ;
1783
1784 l_Token_Tbl(1).token_name := 'SUB_RESOURCE_CODE';
1785 l_Token_Tbl(1).token_value := p_rev_sub_resource_rec.sub_resource_code ;
1786
1787 l_token_tbl.delete(3) ;
1788
1789 END LOOP ;
1790
1791 IF l_old_op_seq_id IS NULL THEN
1792 -- This is an unexpected error.
1793 Error_Handler.Add_Error_Token
1794 ( p_Message_Name => NULL
1795 , p_Message_Text => 'Unexpected error occurred. Sinse
1796 Parent Revised Operation does not have old operation
1797 sequence id' || ' in ' ||
1798 G_PKG_NAME
1799 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1800 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1801 );
1802 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1803 END IF;
1804
1805 END IF ;
1806
1807 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1808 ('Check uniquness of Sub Op Resource Record. . . . ' || l_return_status) ;
1809 END IF ;
1810
1811 END IF ;
1812
1813 --
1814 -- Activity Id
1815 -- Check if Activity is enabled
1816 -- BOM_Validate_Op_Res.Val_Activity_Id
1817 --
1818 IF ( ( NVL(l_rev_sub_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
1819 AND l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
1820 OR NVL(l_rev_sub_res_unexp_rec.activity_id, FND_API.G_MISS_NUM)
1821 <> NVL(p_old_rev_sub_res_unexp_rec.activity_id, FND_API.G_MISS_NUM)
1822 )
1823 AND ( l_rev_sub_res_unexp_rec.activity_id IS NOT NULL AND
1824 l_rev_sub_res_unexp_rec.activity_id <> FND_API.G_MISS_NUM )
1825 THEN
1826
1827 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1828 ('Activity_Id : ' || to_char(l_rev_sub_res_unexp_rec.activity_id)) ;
1829 END IF ;
1830
1831
1832 /* Call Val_Activity_Id */
1833 BOM_Validate_Op_Res.Val_Activity_Id
1834 ( p_activity_id => l_rev_sub_res_unexp_rec.activity_id
1835 , p_op_seq_id => l_rev_sub_res_unexp_rec.operation_sequence_id
1836 , x_return_status => l_temp_status
1837 ) ;
1838
1839
1840 IF l_temp_status = FND_API.G_RET_STS_ERROR
1841 THEN
1842 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1843 THEN
1844 Error_Handler.Add_Error_Token
1845 ( p_message_name => 'BOM_SUB_RES_ACTID_INVALID'
1846 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1847 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1848 , p_Token_Tbl => l_Token_Tbl
1849 ) ;
1850 END IF ;
1851
1852 l_return_status := FND_API.G_RET_STS_ERROR ;
1853
1854 END IF ;
1855
1856 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1857 ('Check if Activity is enabled. . . . ' || l_return_status) ;
1858 END IF ;
1859
1860 END IF ;
1861
1862
1863 --
1864 -- Activity Id
1865 -- Check if Activity is enabled
1866 -- BOM_Validate_Op_Res.Val_Activity_Id
1867 --
1868 IF ( ( NVL(l_rev_sub_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
1869 AND l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
1870 OR NVL(l_rev_sub_res_unexp_rec.setup_Id , FND_API.G_MISS_NUM)
1871 <> NVL(p_old_rev_sub_res_unexp_rec.setup_id, FND_API.G_MISS_NUM)
1872 )
1873 AND ( l_rev_sub_res_unexp_rec.setup_id IS NOT NULL AND
1874 l_rev_sub_res_unexp_rec.setup_Id <> FND_API.G_MISS_NUM )
1875 THEN
1876
1877 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1878 ('Setup_Id : ' || to_char(l_rev_sub_res_unexp_rec.setup_id )) ;
1879 END IF ;
1880
1881
1882 /* Call Val_Activity_Id */
1883 BOM_Validate_Op_Res.Val_Setup_Id
1884 ( p_setup_id => l_rev_sub_res_unexp_rec.setup_id
1885 , p_resource_id => NVL(l_rev_sub_res_unexp_rec.new_resource_id,
1886 l_rev_sub_res_unexp_rec.resource_id )
1887 , p_organization_id => l_rev_sub_res_unexp_rec.organization_id
1888 , x_return_status => l_temp_status
1889 ) ;
1890
1891
1892 IF l_temp_status = FND_API.G_RET_STS_ERROR
1893 THEN
1894 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1895 THEN
1896
1897
1898 l_token_tbl(3).token_name := 'SETUP_CODE';
1899 l_token_tbl(3).token_value :=
1900 l_rev_sub_resource_rec.setup_type ;
1901
1902 Error_Handler.Add_Error_Token
1903 ( p_message_name => 'BOM_SUB_RES_SETUPID_INVALID'
1904 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1905 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1906 , p_Token_Tbl => l_Token_Tbl
1907 ) ;
1908 END IF ;
1909
1910 l_token_tbl.delete(3) ;
1911 l_return_status := FND_API.G_RET_STS_ERROR ;
1912
1913 END IF ;
1914
1915 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1916 ('Check if Setup is enabled. . . . ' || l_return_status) ;
1917 END IF ;
1918
1919 END IF ;
1920
1921
1922 --
1923 -- Schedule Flag
1924 -- Schedule Flag must be 2:No in following case
1925 -- 1. Resource UOM <> Hour UOM code(if they're the same, class would be
1926 -- same
1927 -- 2. Resource UOM class <> Hour UOM class
1928 -- 3. No conversion between resource UOM and Hour UOM
1929 --
1930 -- Call BOM_Validate_Op_Res.Get_Resource_Uom
1931 -- and BOM_Validate_Op_Res.Val_Res_UOM_For_Schedule
1932 --
1933 IF p_rev_sub_resource_rec.schedule_flag <> l_NO_SCHEDULE -- 2: No
1934 THEN
1935
1936 IF ( l_hour_uom_code IS NULL OR
1937 l_hour_uom_class IS NULL OR
1938 l_res_uom_code IS NULL OR
1939 l_res_uom_class IS NULL
1940 )
1941 THEN
1942 BOM_Validate_Op_Res.Get_Resource_Uom
1943 ( p_resource_id
1944 => NVL(l_rev_sub_res_unexp_rec.new_resource_id,
1945 l_rev_sub_res_unexp_rec.resource_id)
1946 , x_hour_uom_code => l_hour_uom_code
1947 , x_hour_uom_class => l_hour_uom_class
1948 , x_res_uom_code => l_res_uom_code
1949 , x_res_uom_class => l_res_uom_class ) ;
1950 END IF ;
1951
1952 /* Call Val_Scheduled_Resource */
1953 BOM_Validate_Op_Res.Val_Res_UOM_For_Schedule
1954 ( p_hour_uom_class => l_hour_uom_class
1955 , p_res_uom_class => l_res_uom_class
1956 , p_hour_uom_code => l_hour_uom_code
1957 , p_res_uom_code => l_res_uom_code
1958 , x_return_status => l_temp_status
1959 ) ;
1960
1961 IF l_temp_status = FND_API.G_RET_STS_ERROR
1962 THEN
1963 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1964 THEN
1965 Error_Handler.Add_Error_Token
1966 ( p_message_name => 'BOM_SUB_RES_SCHEDULE_MUSTBE_NO'
1967 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1968 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1969 , p_Token_Tbl => l_Token_Tbl
1970 ) ;
1971 END IF ;
1972
1973 l_return_status := FND_API.G_RET_STS_ERROR ;
1974
1975 END IF ;
1976
1977 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1978 ('Check if schedule flag is valid. . . . ' || l_return_status) ;
1979 END IF ;
1980
1981 END IF ;
1982
1983 --
1984 -- Scheduled Resource
1985 -- Cannot have more than one next or prior sheduled resource for
1986 -- an operation.
1987 -- Hence, there must be related one next or prior sheduled resource
1988 -- in operation resource
1989 -- and cannot have more than one next or prior sheduled sub resource
1990 -- whitin substitute group num.
1991 --
1992 IF ( ( NVL(l_rev_sub_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
1993 AND l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
1994 OR l_rev_sub_resource_rec.schedule_flag
1995 <> p_old_rev_sub_resource_rec.schedule_flag
1996 )
1997 THEN
1998
1999 IF p_rev_sub_resource_rec.schedule_flag = l_PRIOR -- 3: Prior
2000 THEN
2001 /* Call Val_Scheduled_Sub_Resource
2002 -- From does not have this validation, then comment out
2003 Val_Scheduled_Sub_Resource
2004 ( p_op_seq_id => l_rev_sub_res_unexp_rec.operation_sequence_id
2005 , p_resource_id => l_rev_sub_res_unexp_rec.resource_id
2006 , p_sub_group_num => l_rev_sub_res_unexp_rec.substitute_group_number
2007 , p_schedule_flag => l_rev_sub_resource_rec.schedule_flag
2008 , x_return_status => l_temp_status
2009 ) ;
2010 */
2011
2012
2013 IF l_temp_status = FND_API.G_RET_STS_ERROR
2014 THEN
2015 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2016 THEN
2017 Error_Handler.Add_Error_Token
2018 ( p_message_name => 'BOM_SUB_RES_PRIOR_INVALID'
2019 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2020 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2021 , p_Token_Tbl => l_Token_Tbl
2022 ) ;
2023 END IF ;
2024
2025 l_return_status := FND_API.G_RET_STS_ERROR ;
2026
2027 END IF ;
2028
2029 ELSIF p_rev_sub_resource_rec.schedule_flag = l_NEXT -- 4: Next
2030 THEN
2031
2032 /* Call Val_Scheduled_Sub_Resource
2033 -- From does not have this validation, then comment out
2034 Val_Scheduled_Sub_Resource
2035 ( p_op_seq_id => l_rev_sub_res_unexp_rec.operation_sequence_id
2036 , p_resource_id => l_rev_sub_res_unexp_rec.resource_id
2037 , p_sub_group_num => l_rev_sub_res_unexp_rec.substitute_group_number
2038 , p_schedule_flag => l_rev_sub_resource_rec.schedule_flag
2039 , x_return_status => l_temp_status
2040 ) ;
2041 */
2042
2043 IF l_temp_status = FND_API.G_RET_STS_ERROR
2044 THEN
2045 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2046 THEN
2047 Error_Handler.Add_Error_Token
2048 ( p_message_name => 'BOM_SUB_RES_NEXT_INVALID'
2049 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2050 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2051 , p_Token_Tbl => l_Token_Tbl
2052 ) ;
2053 END IF ;
2054
2055 l_return_status := FND_API.G_RET_STS_ERROR ;
2056
2057 END IF ;
2058
2059 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2060 ('Check next or prior scheduled resource. . . . ' || l_return_status) ;
2061 END IF ;
2062
2063 END IF ;
2064
2065 END IF ;
2066
2067 --
2068 -- Autocharge Type
2069 -- Autocharge type cannot be PO Receipt if the
2070 -- department has no location.
2071 -- Call BOM_Validate_Op_Res.Val_Dept_Has_Location
2072 --
2073 IF ( ( NVL(l_rev_sub_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
2074 AND l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
2075 OR l_rev_sub_resource_rec.autocharge_type <> p_old_rev_sub_resource_rec.autocharge_type
2076 )
2077 AND l_rev_sub_resource_rec.autocharge_type = l_PO_RECEIPT
2078 THEN
2079
2080 /* Call Val_Dept_Has_Location */
2081 BOM_Validate_Op_Res.Val_Dept_Has_Location
2082 ( p_op_seq_id => l_rev_sub_res_unexp_rec.operation_sequence_id
2083 , x_return_status => l_temp_status
2084 ) ;
2085
2086 IF l_temp_status = FND_API.G_RET_STS_ERROR
2087 THEN
2088 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2089 THEN
2090 Error_Handler.Add_Error_Token
2091 ( p_message_name => 'BOM_SUB_RES_POAUTO_LOC_INVALID'
2092 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2093 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2094 , p_Token_Tbl => l_Token_Tbl
2095 ) ;
2096 END IF ;
2097
2098 l_return_status := FND_API.G_RET_STS_ERROR ;
2099
2100 END IF ;
2101
2102 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2103 ('Check if Dept has a location for PO Receipt Res. . . . ' || l_return_status) ;
2104 END IF ;
2105
2106 END IF ;
2107
2108
2109 --
2110 -- Autocharge Type
2111 -- Autocharge Type cannot be PO Move or PO Receipt if the resource
2112 -- is non-OSP resource
2113 -- Call BOM_Validate_Op_Res.Val_Autocharge_for_OSP_Res
2114 --
2115 IF ( ( NVL(l_rev_sub_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
2116 AND l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
2117 OR l_rev_sub_resource_rec.autocharge_type <> p_old_rev_sub_resource_rec.autocharge_type
2118 )
2119 AND l_rev_sub_resource_rec.autocharge_type IN (l_PO_RECEIPT, l_PO_MOVE )
2120 THEN
2121
2122 /* Call Val_Autocharge_for_OSP_Res */
2123 BOM_Validate_Op_Res.Val_Autocharge_for_OSP_Res
2124 ( p_resource_id => NVL(l_rev_sub_res_unexp_rec.new_resource_id,
2125 l_rev_sub_res_unexp_rec.resource_id)
2126 , p_organization_id => l_rev_sub_res_unexp_rec.organization_id
2127 , x_return_status => l_temp_status
2128 ) ;
2129
2130
2131 IF l_temp_status = FND_API.G_RET_STS_ERROR
2132 THEN
2133 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2134 THEN
2135 Error_Handler.Add_Error_Token
2136 ( p_message_name => 'BOM_SUB_RES_AUTO_CSTCD_INVALID'
2137 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2138 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2139 , p_Token_Tbl => l_Token_Tbl
2140 ) ;
2141 END IF ;
2142
2143 l_return_status := FND_API.G_RET_STS_ERROR ;
2144
2145 END IF ;
2146
2147 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2148 ('Check if resource is OSP resource when autocharge is PO Move or PO Receipt. . . . ' || l_return_status) ;
2149 END IF ;
2150
2151 END IF ;
2152
2153 --
2154 -- Autocharge Type
2155 -- Cannnot have more than one PO Move per an operation
2156 -- Hence, there must be related PO Move resource in operation resource
2157 -- and cannot have more than one PO Move whitin substitute group num.
2158 --
2159 IF ( ( NVL(l_rev_sub_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
2160 AND l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
2161 OR l_rev_sub_resource_rec.autocharge_type <> p_old_rev_sub_resource_rec.autocharge_type
2162 )
2163 AND l_rev_sub_resource_rec.autocharge_type = l_PO_MOVE
2164 THEN
2165
2166 /* Call Val_Sub_PO_Move
2167 -- From does not have this validation, then comment out
2168 Val_Sub_PO_Move
2169 ( p_op_seq_id => l_rev_sub_res_unexp_rec.operation_sequence_id
2170 , p_resource_id => l_rev_sub_res_unexp_rec.resource_id
2171 , p_sub_group_num => l_rev_sub_res_unexp_rec.substitute_group_number
2172 , x_return_status => l_temp_status
2173 ) ;
2174 */
2175
2176 IF l_temp_status = FND_API.G_RET_STS_ERROR
2177 THEN
2178
2179 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2180 THEN
2181 Error_Handler.Add_Error_Token
2182 ( p_message_name => 'BOM_SUB_RES_POMOVE_INVALID'
2183 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2184 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2185 , p_Token_Tbl => l_Token_Tbl
2186 ) ;
2187 END IF ;
2188
2189 l_return_status := FND_API.G_RET_STS_ERROR ;
2190 END IF ;
2191
2192 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2193 ('Check if Autocharge Type is enabled. . . . ' || l_return_status) ;
2194 END IF ;
2195
2196
2197 END IF ;
2198
2199
2200 --
2201 -- Usage Rate or Amount
2202 -- Check round values for Usage Rate or Amount and the Inverse.
2203 -- Call BOM_Validate_Op_Res.Val_Usage_Rate_or_Amount
2204 --
2205 IF ( ( NVL(l_rev_sub_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
2206 AND l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
2207 OR l_rev_sub_resource_rec.usage_rate_or_amount
2208 <> p_old_rev_sub_resource_rec.usage_rate_or_amount
2209 OR l_rev_sub_resource_rec.usage_rate_or_amount_inverse
2210 <> p_old_rev_sub_resource_rec.usage_rate_or_amount_inverse
2211 )
2212 THEN
2213
2214 /* Call Val_Usage_Rate_or_Amount */
2215 BOM_Validate_Op_Res.Val_Usage_Rate_or_Amount
2216 ( p_usage_rate_or_amount => l_rev_sub_resource_rec.usage_rate_or_amount
2217 , p_usage_rate_or_amount_inverse => l_rev_sub_resource_rec.usage_rate_or_amount_inverse
2218 , x_return_status => l_temp_status
2219 ) ;
2220
2221
2222 IF l_temp_status = FND_API.G_RET_STS_ERROR
2223 THEN
2224 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2225 THEN
2226 Error_Handler.Add_Error_Token
2227 ( p_message_name => 'BOM_SUB_RES_RATEORAMT_INVALID'
2228 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2229 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2230 , p_Token_Tbl => l_Token_Tbl
2231 ) ;
2232 END IF ;
2233
2234 l_return_status := FND_API.G_RET_STS_ERROR ;
2235
2236 END IF ;
2237
2238 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2239 ('Check round value for Usage Rate or Amount and the Inverse . . . ' || l_return_status) ;
2240 END IF ;
2241
2242 END IF ;
2243
2244
2245 --
2246 -- Usage Rate or Amount
2247 -- Cannot have negative usage rate or amount in following case
2248 -- 1. Autocharge Type = 3: PO Receipt or 4: PO Move
2249 -- 2. Schedul Flag <> 2
2250 -- comment out 3. Resource UOM Class = Hour UOM Class
2251 -- Form allows No.3
2252 --
2253
2254 IF ( ( NVL(l_rev_sub_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
2255 AND l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
2256 OR l_rev_sub_resource_rec.usage_rate_or_amount
2257 <> p_old_rev_sub_resource_rec.usage_rate_or_amount
2258 OR l_rev_sub_resource_rec.usage_rate_or_amount_inverse
2259 <> p_old_rev_sub_resource_rec.usage_rate_or_amount_inverse
2260 OR l_rev_sub_resource_rec.schedule_flag <> p_old_rev_sub_resource_rec.schedule_flag
2261 OR l_rev_sub_resource_rec.autocharge_type <> p_old_rev_sub_resource_rec.autocharge_type
2262 )
2263 AND l_rev_sub_resource_rec.usage_rate_or_amount < 0
2264 THEN
2265 IF ( l_hour_uom_code IS NULL OR
2266 l_hour_uom_class IS NULL OR
2267 l_res_uom_code IS NULL OR
2268 l_res_uom_class IS NULL
2269 )
2270 THEN
2271 BOM_Validate_Op_Res.Get_Resource_Uom
2272 ( p_resource_id => NVL(l_rev_sub_res_unexp_rec.new_resource_id,
2273 l_rev_sub_res_unexp_rec.resource_id)
2274 , x_hour_uom_code => l_hour_uom_code
2275 , x_hour_uom_class => l_hour_uom_class
2276 , x_res_uom_code => l_res_uom_code
2277 , x_res_uom_class => l_res_uom_class ) ;
2278 END IF ;
2279
2280
2281 /* Call Val_Negative_Usage_Rate */
2282 BOM_Validate_Op_Res.Val_Negative_Usage_Rate
2283 ( p_autocharge_type => l_rev_sub_resource_rec.autocharge_type
2284 , p_schedule_flag => l_rev_sub_resource_rec.schedule_flag
2285 , p_hour_uom_class => l_hour_uom_class
2286 , p_res_uom_class => l_res_uom_class
2287 , x_return_status => l_temp_status
2288 ) ;
2289
2290
2291 IF l_temp_status = FND_API.G_RET_STS_ERROR
2292 THEN
2293 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2294 THEN
2295 Error_Handler.Add_Error_Token
2296 ( p_message_name => 'BOM_SUB_RES_NEG_USAGRT_INVALID'
2297 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2298 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2299 , p_Token_Tbl => l_Token_Tbl
2300 ) ;
2301 END IF ;
2302
2303 l_return_status := FND_API.G_RET_STS_ERROR ;
2304
2305 END IF ;
2306
2307 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2308 ('Check negative usage rate. . . . ' || l_return_status) ;
2309 END IF ;
2310
2311 END IF ;
2312
2313 -- Principal Flag
2314 -- Cannot have one more principal resource in a group of simulatenous
2315 -- resources
2316 --
2317 /* Added by deepu. Validation for Principal flag is required for patchset I Bug 2689249*/
2318
2319 IF ( ( NVL(l_rev_sub_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
2320 AND l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
2321 OR l_rev_sub_resource_rec.principle_flag
2322 <> p_old_rev_sub_resource_rec.principle_flag
2323 OR l_rev_sub_resource_rec.replacement_group_number
2324 <> p_old_rev_sub_resource_rec.replacement_group_number
2325 OR l_rev_sub_resource_rec.basis_type
2326 <> p_old_rev_sub_resource_rec.basis_type
2327 )
2328 AND l_rev_sub_resource_rec.principle_flag = 1 -- Yes
2329 THEN
2330 -- Call Val_Principal_Res_Unique
2331 Val_Principal_Sub_Res_Unique
2332 ( p_op_seq_id => l_rev_sub_res_unexp_rec.operation_sequence_id
2333 , p_res_id => l_rev_sub_res_unexp_rec.resource_id
2334 , p_sub_group_num => nvl(l_rev_sub_resource_rec.substitute_group_number, l_rev_sub_res_unexp_rec.substitute_group_number)
2335 , p_rep_group_num => l_rev_sub_resource_rec.replacement_group_number
2336 , p_basis_type => l_rev_sub_resource_rec.basis_type
2337 , x_return_status => l_temp_status
2338 ) ;
2339
2340
2341 IF l_temp_status = FND_API.G_RET_STS_ERROR
2342 THEN
2343 -- dbms_output.put_line('found error in principal flag for sub resources');
2344 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2345 THEN
2346 Error_Handler.Add_Error_Token
2347 ( p_message_name => 'BOM_SUB_RES_PCFLAG_DUPLICATE'
2348 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2349 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2350 , p_Token_Tbl => l_Token_Tbl
2351 ) ;
2352 END IF ;
2353
2354 l_return_status := FND_API.G_RET_STS_ERROR ;
2355
2356 END IF ;
2357
2358 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2359 ('Check principal flag . . . . ' || l_return_status) ;
2360 END IF ;
2361
2362 END IF ;
2363
2364 /*Fix for bug 6074930- Scheduled simultaneous resources/sub-resources must have the same scheduling flag.
2365 Added below code to do this validation. Sub-Resources with scheduling flag 'NO' are exempt
2366 for this validation. Call Val_Schedule_Flag procedure both while creating/updating a sub-resource.
2367 For sub-resources schedule_sequence_number is a mandatory column.*/
2368
2369 IF ( l_rev_sub_resource_rec.schedule_flag <> l_NO_SCHEDULE)
2370 AND
2371 ( ( NVL(l_rev_sub_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
2372 AND l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
2373 OR l_rev_sub_resource_rec.schedule_sequence_number <> p_old_rev_sub_resource_rec.schedule_sequence_number
2374 OR l_rev_sub_resource_rec.schedule_flag <> p_old_rev_sub_resource_rec.schedule_flag
2375 )
2376 THEN
2377 l_res_id := FND_API.G_MISS_NUM;
2378
2379 Val_Schedule_Flag
2380 ( p_op_seq_id => l_rev_sub_res_unexp_rec.operation_sequence_id
2381 , p_res_seq_num => null
2382 , p_sch_seq_num => l_rev_sub_resource_rec.schedule_sequence_number
2383 , p_sch_flag => l_rev_sub_resource_rec.schedule_flag
2384 , p_sub_grp_num => l_rev_sub_resource_rec.substitute_group_number
2385 , p_rep_grp_num => l_rev_sub_resource_rec.replacement_group_number
2386 , p_basis_type => l_rev_sub_resource_rec.basis_type
2387 , p_in_res_id => l_rev_sub_res_unexp_rec.resource_id
2388 , p_ret_res_id => l_res_id
2389 , x_return_status => l_temp_status
2390 );
2391
2392 IF l_temp_status = FND_API.G_RET_STS_ERROR THEN
2393 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2394 If (l_rev_sub_resource_rec.sub_resource_code is not null) Then
2395 l_res_code := l_rev_sub_resource_rec.sub_resource_code;
2396 Else
2397 Select resource_code into l_res_code
2398 from bom_resources_v
2399 where resource_id=l_rev_sub_res_unexp_rec.resource_id;
2400 End If;
2401
2402 Select resource_code into l_res_code_2
2403 from bom_resources_v
2404 where resource_id=l_res_id;
2405
2406 l_Token_Tbl(1).Token_Name := 'RES_SEQ_1';
2407 l_Token_Tbl(1).Token_Value:= l_res_code;
2408 l_Token_Tbl(2).Token_Name := 'RES_SEQ_2';
2409 l_Token_Tbl(2).Token_Value:= l_res_code_2;
2410 l_Token_Tbl(3).Token_Name := 'OP_SEQ';
2411 l_Token_Tbl(3).Token_Value := l_rev_sub_resource_rec.operation_sequence_number;
2412
2413 Error_Handler.Add_Error_Token
2414 ( p_message_name => 'BOM_SIM_RES_SAME_PRIOR_NEXT'
2415 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2416 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2417 , p_Token_Tbl => l_Token_Tbl
2418 );
2419 END IF; /* end of check_msg_level */
2420 l_return_status := FND_API.G_RET_STS_ERROR ;
2421 END IF; /* end of l_temp_status */
2422 END IF; /* end of validation on resource and ssn*/
2423 /*End of fix for bug 6074930 */
2424
2425
2426 END IF ; -- Transaction Type : Create and Update
2427
2428
2429 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2430 ('Entity Validation was processed. . . ' || l_return_status);
2431 END IF ;
2432
2433
2434
2435 --
2436 -- Return Records
2437 --
2438 x_rev_sub_resource_rec := l_rev_sub_resource_rec ;
2439 x_rev_sub_res_unexp_rec := l_rev_sub_res_unexp_rec ;
2440
2441 --
2442 -- Return Error Status
2443 --
2444 x_return_status := l_return_status;
2445 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2446
2447
2448 EXCEPTION
2449 WHEN OTHERS THEN
2450 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2451 ('Some unknown error in Entity Validation . . .' || SQLERRM );
2452 END IF ;
2453
2454
2455 l_err_text := G_PKG_NAME || ' Validation (Entity Validation) '
2456 || substrb(SQLERRM,1,200);
2457 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
2458
2459 Error_Handler.Add_Error_Token
2460 ( p_message_name => NULL
2461 , p_message_text => l_err_text
2462 , p_mesg_token_tbl => l_mesg_token_tbl
2463 , x_mesg_token_tbl => l_mesg_token_tbl
2464 ) ;
2465
2466 -- Return the status and message table.
2467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2468 x_mesg_token_tbl := l_mesg_token_tbl ;
2469 END Check_Entity ;
2470
2471 END BOM_Validate_Sub_Op_Res ;