[Home] [Help]
PACKAGE BODY: APPS.BOM_VALIDATE_OP_RES
Source
1 PACKAGE BODY BOM_Validate_Op_Res AS
2 /* $Header: BOMLRESB.pls 120.8.12010000.2 2008/11/14 16:28:54 snandana ship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMLRESB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Validate_Op_Res
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 -- 18-AUG-2000 Masanori Kimizuka Initial Creation
20 --
21 ****************************************************************************/
22
23 G_Pkg_Name VARCHAR2(30) := 'BOM_Validate_Op_Res';
24
25 l_EVENT CONSTANT NUMBER := 1 ;
26 l_ACD_ADD CONSTANT NUMBER := 1 ;
27 l_ACD_CHANGE CONSTANT NUMBER := 2 ;
28 l_ACD_DISABLE CONSTANT NUMBER := 3 ;
29 l_YES_SCHEDULE CONSTANT NUMBER := 1 ;
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 l_OSP CONSTANT NUMBER := 4 ; -- 4 : Outside Processing
36
37
38
39 /******************************************************************
40 * OTHER LOCAL FUNCTION AND PROCEDURES
41 * Purpose : Called by Check_Entity or something
42 *********************************************************************/
43 --
44 -- Function: Check if Op Seq Num exists in Work Order
45 -- in ECO by Lot, Wo, Cum Qty
46 --
47 FUNCTION Check_ECO_By_WO_Effectivity
48 ( p_revised_item_sequence_id IN NUMBER
49 , p_operation_seq_num IN NUMBER
50 , p_resource_seq_num IN NUMBER
51 , p_organization_id IN NUMBER
52 , p_rev_item_id IN NUMBER
53 )
54
55 RETURN BOOLEAN
56 IS
57 l_ret_status BOOLEAN := TRUE ;
58
59 l_lot_number varchar2(30) := NULL;
60 l_from_wip_entity_id NUMBER :=0;
61 l_to_wip_entity_id NUMBER :=0;
62 l_from_cum_qty NUMBER :=0;
63
64
65 CURSOR l_check_lot_num_csr ( p_lot_number VARCHAR2
66 , p_operation_seq_num NUMBER
67 , p_resource_seq_num NUMBER
68 , p_organization_id NUMBER
69 , p_rev_item_id NUMBER
70 )
71 IS
72 SELECT 'Op Res does not exist'
73 FROM SYS.DUAL
74 WHERE EXISTS (SELECT NULL
75 FROM WIP_DISCRETE_JOBS wdj
76 WHERE (wdj.status_type <> 1
77 OR
78 NOT EXISTS(SELECT NULL
79 FROM WIP_OPERATION_RESOURCES wor
80 WHERE wor.resource_seq_num = p_resource_seq_num
81 AND wor.operation_seq_num = p_operation_seq_num
82 AND wor.wip_entity_id = wdj.wip_entity_id)
83 )
84 AND wdj.lot_number = p_lot_number
85 AND wdj.organization_id = p_organization_id
86 AND wdj.primary_item_id = p_rev_item_id
87 ) ;
88
89 CURSOR l_check_wo_csr ( p_from_wip_entity_id NUMBER
90 , p_to_wip_entity_id NUMBER
91 , p_operation_seq_num NUMBER
92 , p_resource_seq_num NUMBER
93 , p_organization_Id NUMBER )
94 IS
95 SELECT 'Op Res does not exist'
96 FROM SYS.DUAL
97 WHERE EXISTS (SELECT NULL
98 FROM WIP_DISCRETE_JOBS wdj
99 , WIP_ENTITIES we
100 , WIP_ENTITIES we1
101 , WIP_ENTITIES we2
102 WHERE (wdj.status_type <> 1
103 OR
104 NOT EXISTS (SELECT NULL
105 FROM WIP_OPERATION_RESOURCES wor
106 WHERE resource_seq_num = p_resource_seq_num
107 AND operation_seq_num = p_operation_seq_num
108 AND wip_entity_id = wdj.wip_entity_id)
109 )
110 AND wdj.wip_entity_id = we.wip_entity_id
111 AND we.organization_Id = p_organization_id
112 AND we.wip_entity_name >= we1.wip_entity_name
113 AND we.wip_entity_name <= we2.wip_entity_name
114 AND we1.wip_entity_id = p_from_wip_entity_id
115 AND we2.wip_entity_id = NVL(p_to_wip_entity_id, p_from_wip_entity_id)
116 ) ;
117
118 CURSOR l_check_cum_csr ( p_from_wip_entity_id NUMBER
119 , p_operation_seq_num NUMBER
120 , p_resource_seq_num NUMBER )
121
122
123 IS
124 SELECT 'Op Res does not exist'
125 FROM SYS.DUAL
126 WHERE EXISTS (SELECT NULL
127 FROM WIP_DISCRETE_JOBS wdj
128 WHERE (wdj.status_type <> 1
129 OR
130 NOT EXISTS(SELECT NULL
131 FROM WIP_OPERATION_RESOURCES wor
132 WHERE resource_seq_num = p_resource_seq_num
133 AND operation_seq_num = p_operation_seq_num
134 AND wip_entity_id = wdj.wip_entity_id)
135 )
136 AND wdj.wip_entity_id = p_from_wip_entity_id
137 ) ;
138
139 BEGIN
140
141 l_lot_number := BOM_Rtg_Globals.Get_Lot_Number;
142 l_from_wip_entity_id := BOM_Rtg_Globals.Get_From_Wip_Entity_Id;
143 l_to_wip_entity_id := BOM_Rtg_Globals.Get_To_Wip_Entity_Id;
144 l_from_cum_qty := BOM_Rtg_Globals.Get_From_Cum_Qty;
145
146 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
147 Error_Handler.Write_Debug('Check if the rev op resource is valid in Eco by Prod. . .' );
148 Error_Handler.Write_Debug('Lot Number in parent rev item : ' || l_lot_number );
149 Error_Handler.Write_Debug('From WIP Entity Id in parent rev item : ' || to_char(l_from_wip_entity_id) );
150 Error_Handler.Write_Debug('To WIP Entity Id in parent rev item : ' || to_char(l_to_wip_entity_id) );
151 Error_Handler.Write_Debug('Cum Qty in parent rev item : ' || to_char(l_from_cum_qty) );
152 END IF;
153
154
155 -- Check if Op Seq Num is exist in ECO by Lot
156 IF l_lot_number IS NOT NULL
157 AND l_from_wip_entity_id IS NULL
158 AND l_to_wip_entity_id IS NULL
159 AND l_from_cum_qty IS NULL
160 THEN
161
162 FOR l_lot_num_rec IN l_check_lot_num_csr
163 ( p_lot_number => l_lot_number
164 , p_operation_seq_num => p_operation_seq_num
165 , p_resource_seq_num => p_resource_seq_num
166 , p_organization_id => p_organization_id
167 , p_rev_item_id => p_rev_item_id
168 )
169 LOOP
170
171 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
172 Error_Handler.Write_Debug('Resource Seq Num : ' || to_char(p_resource_seq_num) );
173 Error_Handler.Write_Debug('Op Seq Num : ' || to_char(p_operation_seq_num) );
174 Error_Handler.Write_Debug('In Eco by Lot Number, this rev op res is invalid. . .' );
175 END IF;
176 l_ret_status := FALSE ;
177 END LOOP ;
178
179 -- Check if Op Seq Num is exist in ECO by Cum
180 ELSIF l_lot_number IS NULL
181 AND l_from_wip_entity_id IS NOT NULL
182 AND l_to_wip_entity_id IS NULL
183 AND l_from_cum_qty IS NOT NULL
184 THEN
185
186 FOR l_cum_rec IN l_check_cum_csr
187 ( p_from_wip_entity_id => l_from_wip_entity_id
188 , p_operation_seq_num => p_operation_seq_num
189 , p_resource_seq_num => p_resource_seq_num
190 )
191 LOOP
192 l_ret_status := FALSE ;
193 END LOOP ;
194
195 -- Check if Op Seq Num is exist in ECO by WO
196 ELSIF l_lot_number IS NULL
197 AND l_from_wip_entity_id IS NOT NULL
198 AND l_from_cum_qty IS NULL
199 THEN
200
201 FOR l_wo_rec IN l_check_wo_csr
202 ( p_from_wip_entity_id => l_from_wip_entity_id
203 , p_to_wip_entity_id => l_to_wip_entity_id
204 , p_operation_seq_num => p_operation_seq_num
205 , p_resource_seq_num => p_resource_seq_num
206 , p_organization_id => p_organization_id
207 )
208 LOOP
209 l_ret_status := FALSE ;
210 END LOOP ;
211
212 ELSIF l_lot_number IS NULL
213 AND l_from_wip_entity_id IS NULL
214 AND l_to_wip_entity_id IS NULL
215 AND l_from_cum_qty IS NULL
216 THEN
217 NULL ;
218
219 -- ELSE
220 -- l_ret_status := FALSE ;
221 --
222
223 END IF ;
224
225 RETURN l_ret_status ;
226
227 END Check_ECO_By_WO_Effectivity ;
228
229
230
231
232 /*******************************************************************
233 *Others : Following Procedures and Functions are called by
234 * Check_Entity in Op Resource and Sub Op Resource
235 *Purpose : These Shared Logic validate the values on
236 * inter-dependent columns or get values to validate entity.
237 *******************************************************************/
238
239 -- Get parent opertion Acd type.
240 FUNCTION Get_Rev_Op_ACD (p_op_seq_id IN NUMBER) RETURN NUMBER
241
242 IS
243 CURSOR l_get_acdtype_csr(p_op_seq_id NUMBER)
244 IS
245 SELECT acd_type
246 FROM BOM_OPERATION_SEQUENCES
247 WHERE operation_sequence_id = p_op_seq_id ;
248 BEGIN
249
250 FOR l_get_acdtype_rec IN l_get_acdtype_csr(p_op_seq_id => p_op_seq_id)
251 LOOP
252 RETURN l_get_acdtype_rec.acd_type ;
253 END LOOP ;
254 RETURN NULL ;
255 END Get_Rev_Op_ACD ;
256
257 /* No Longer used
258 -- Check if the operation resource's attribute is updated when ACD Type is changed
259 -- If updated, return False.
260 FUNCTION Check_Res_Attr_changed
261 ( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
262 , p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
263 , p_old_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
264 , p_old_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
265 ) RETURN BOOLEAN
266
267 IS
268
269 BEGIN
270
271 IF (p_rev_op_res_unexp_rec.resource_id = p_old_rev_op_res_unexp_rec.resource_id
272 OR ( p_rev_op_res_unexp_rec.resource_id IS NULL
273 AND p_old_rev_op_res_unexp_rec.resource_id IS NULL )
274 )
275 AND (p_rev_op_res_unexp_rec.activity_id = p_old_rev_op_res_unexp_rec.activity_id
276 OR ( p_rev_op_res_unexp_rec.activity_id IS NULL
277 AND p_old_rev_op_res_unexp_rec.activity_id IS NULL )
278 )
279 AND (p_rev_op_resource_rec.standard_rate_flag = p_old_rev_op_resource_rec.standard_rate_flag
280 OR ( p_rev_op_resource_rec.standard_rate_flag IS NULL
281 AND p_old_rev_op_resource_rec.standard_rate_flag IS NULL )
282 )
283 AND (p_rev_op_resource_rec.assigned_units = p_old_rev_op_resource_rec.assigned_units
284 OR ( p_rev_op_resource_rec.assigned_units IS NULL
285 AND p_old_rev_op_resource_rec.assigned_units IS NULL )
286 )
287 AND (p_rev_op_resource_rec.usage_rate_or_amount = p_old_rev_op_resource_rec.usage_rate_or_amount
288 OR ( p_rev_op_resource_rec.usage_rate_or_amount IS NULL
289 AND p_old_rev_op_resource_rec.usage_rate_or_amount IS NULL )
290 )
291 AND (p_rev_op_resource_rec.usage_rate_or_amount_inverse = p_old_rev_op_resource_rec.usage_rate_or_amount_inverse
292 OR ( p_rev_op_resource_rec.usage_rate_or_amount_inverse IS NULL
293 AND p_old_rev_op_resource_rec.usage_rate_or_amount_inverse IS NULL )
294 )
295 AND (p_rev_op_resource_rec.basis_type = p_old_rev_op_resource_rec.basis_type
296 OR ( p_rev_op_resource_rec.basis_type IS NULL
297 AND p_old_rev_op_resource_rec.basis_type IS NULL )
298 )
299 AND (p_rev_op_resource_rec.schedule_flag = p_old_rev_op_resource_rec.schedule_flag
300 OR ( p_rev_op_resource_rec.schedule_flag IS NULL
301 AND p_old_rev_op_resource_rec.schedule_flag IS NULL )
302 )
303 AND (p_rev_op_resource_rec.resource_offset_percent = p_old_rev_op_resource_rec.resource_offset_percent
304 OR ( p_rev_op_resource_rec.resource_offset_percent IS NULL
305 AND p_old_rev_op_resource_rec.resource_offset_percent IS NULL )
306 )
307 AND (p_rev_op_resource_rec.autocharge_type = p_old_rev_op_resource_rec.autocharge_type
308 OR ( p_rev_op_resource_rec.autocharge_type IS NULL
309 AND p_old_rev_op_resource_rec.autocharge_type IS NULL )
310 )
311 AND (p_rev_op_resource_rec.attribute_category = p_old_rev_op_resource_rec.attribute_category
312 OR ( p_rev_op_resource_rec.attribute_category IS NULL
313 AND p_old_rev_op_resource_rec.attribute_category IS NULL )
314 )
315 AND (p_rev_op_resource_rec.attribute1 = p_old_rev_op_resource_rec.attribute1
316 OR ( p_rev_op_resource_rec.attribute1 IS NULL
317 AND p_old_rev_op_resource_rec.attribute1 IS NULL)
318 )
319 AND (p_rev_op_resource_rec.attribute2 = p_old_rev_op_resource_rec.attribute2
320 OR ( p_rev_op_resource_rec.attribute2 IS NULL
321 AND p_old_rev_op_resource_rec.attribute2 IS NULL)
322 )
323 AND (p_rev_op_resource_rec.attribute3 = p_old_rev_op_resource_rec.attribute3
324 OR ( p_rev_op_resource_rec.attribute3 IS NULL
325 AND p_old_rev_op_resource_rec.attribute3 IS NULL)
326 )
327 AND (p_rev_op_resource_rec.attribute4 = p_old_rev_op_resource_rec.attribute4
328 OR ( p_rev_op_resource_rec.attribute4 IS NULL
329 AND p_old_rev_op_resource_rec.attribute4 IS NULL)
330 )
331 AND (p_rev_op_resource_rec.attribute5 = p_old_rev_op_resource_rec.attribute5
332 OR ( p_rev_op_resource_rec.attribute5 IS NULL
333 AND p_old_rev_op_resource_rec.attribute5 IS NULL)
334 )
335 AND (p_rev_op_resource_rec.attribute6 = p_old_rev_op_resource_rec.attribute6
336 OR ( p_rev_op_resource_rec.attribute6 IS NULL
337 AND p_old_rev_op_resource_rec.attribute6 IS NULL)
338 )
339 AND (p_rev_op_resource_rec.attribute7 = p_old_rev_op_resource_rec.attribute7
340 OR ( p_rev_op_resource_rec.attribute7 IS NULL
341 AND p_old_rev_op_resource_rec.attribute7 IS NULL)
342 )
343 AND (p_rev_op_resource_rec.attribute8 = p_old_rev_op_resource_rec.attribute8
344 OR ( p_rev_op_resource_rec.attribute8 IS NULL
345 AND p_old_rev_op_resource_rec.attribute8 IS NULL)
346 )
347 AND (p_rev_op_resource_rec.attribute9 = p_old_rev_op_resource_rec.attribute9
348 OR ( p_rev_op_resource_rec.attribute9 IS NULL
349 AND p_old_rev_op_resource_rec.attribute9 IS NULL)
350 )
351 AND (p_rev_op_resource_rec.attribute10 = p_old_rev_op_resource_rec.attribute10
352 OR ( p_rev_op_resource_rec.attribute10 IS NULL
353 AND p_old_rev_op_resource_rec.attribute10 IS NULL)
354 )
355 AND (p_rev_op_resource_rec.attribute11 = p_old_rev_op_resource_rec.attribute11
356 OR ( p_rev_op_resource_rec.attribute11 IS NULL
357 AND p_old_rev_op_resource_rec.attribute11 IS NULL)
358 )
359 AND (p_rev_op_resource_rec.attribute12 = p_old_rev_op_resource_rec.attribute12
360 OR ( p_rev_op_resource_rec.attribute12 IS NULL
361 AND p_old_rev_op_resource_rec.attribute12 IS NULL)
362 )
363 AND (p_rev_op_resource_rec.attribute13 = p_old_rev_op_resource_rec.attribute13
364 OR ( p_rev_op_resource_rec.attribute13 IS NULL
365 AND p_old_rev_op_resource_rec.attribute13 IS NULL)
366 )
367 AND (p_rev_op_resource_rec.attribute14 = p_old_rev_op_resource_rec.attribute14
368 OR ( p_rev_op_resource_rec.attribute14 IS NULL
369 AND p_old_rev_op_resource_rec.attribute14 IS NULL)
370 )
371 AND (p_rev_op_resource_rec.attribute15 = p_old_rev_op_resource_rec.attribute15
372 OR ( p_rev_op_resource_rec.attribute15 IS NULL
373 AND p_old_rev_op_resource_rec.attribute15 IS NULL)
374 )
375 AND (p_rev_op_resource_rec.schedule_sequence_number = p_old_rev_op_resource_rec.schedule_sequence_number
376 OR ( p_rev_op_resource_rec.schedule_sequence_number IS NULL
377 AND p_old_rev_op_resource_rec.schedule_sequence_number IS NULL)
378 AND (p_rev_op_resource_rec.substitute_group_number =
379 p_old_rev_op_resource_rec.substitute_group_number
380 OR (
381 p_rev_op_resource_rec.substitute_group_number
382 IS NULL
383 AND
384 p_old_rev_op_resource_rec.substitute_group_number
385 IS NULL)
386
387 )
388 THEN
389 RETURN TRUE ;
390
391 ELSE
392 RETURN FALSE ;
393 END IF ;
394
395 END Check_Res_Attr_changed ;
396 */ -- Comment out by MK
397
398 -- Validate resoruce id.
399 PROCEDURE Val_Resource_Id
400 ( p_resource_id IN NUMBER
401 , p_op_seq_id IN NUMBER
402 , x_return_status IN OUT NOCOPY VARCHAR2
403 )
404 IS
405 CURSOR l_resource_csr( p_resource_id NUMBER
406 , p_op_seq_id NUMBER
407 )
408 IS
409 SELECT 'Resource is invalid'
413 FROM BOM_OPERATION_SEQUENCES bos
410 FROM DUAL
411 WHERE NOT EXISTS(
412 SELECT NULL
414 , BOM_DEPARTMENT_RESOURCES bdr
415 , BOM_RESOURCES br
416 WHERE NVL(br.disable_date, bos.effectivity_date + 1)
417 > bos.effectivity_date
418 AND NVL(br.disable_date, sysdate + 1)
419 > trunc(sysdate)
420 AND bdr.department_id = bos.department_id
421 AND bos.operation_sequence_id = p_op_seq_id
422 AND bdr.resource_id = br.resource_id
423 AND br.resource_id = p_resource_id ) ;
424
425 BEGIN
426
427 x_return_status := FND_API.G_RET_STS_SUCCESS ;
428 FOR l_resource_rec IN l_resource_csr( p_resource_id
429 , p_op_seq_id )
430 LOOP
431 x_return_status := FND_API.G_RET_STS_ERROR ;
432 END LOOP ;
433
434 END Val_Resource_Id ;
435
436 -- Validate activity id.
437 PROCEDURE Val_Activity_Id
438 ( p_activity_id IN NUMBER
439 , p_op_seq_id IN NUMBER
440 , x_return_status IN OUT NOCOPY VARCHAR2
441 )
442 IS
443 CURSOR l_activity_csr( p_activity_id NUMBER
444 , p_op_seq_id NUMBER
445 )
446 IS
447 SELECT 'Activity is invalid'
448 FROM DUAL
449 WHERE NOT EXISTS(
450 SELECT NULL
451 FROM bom_operational_routings bor
452 , BOM_OPERATION_SEQUENCES bos
453 , CST_ACTIVITIES ca
454 WHERE bor.organization_id =
455 NVL(ca.organization_id, bor.organization_id)
456 AND NVL(TRUNC(ca.disable_date), TRUNC(bos.effectivity_date) + 1)
457 > TRUNC(bos.effectivity_date)
458 AND bor.routing_sequence_id = bos.routing_sequence_id
459 AND bos.operation_sequence_id = p_op_seq_id
460 AND ca.activity_id = p_activity_id ) ;
461
462 BEGIN
463
464 x_return_status := FND_API.G_RET_STS_SUCCESS ;
465 FOR l_activity_rec IN l_activity_csr( p_activity_id
466 , p_op_seq_id )
467 LOOP
468 x_return_status := FND_API.G_RET_STS_ERROR ;
469 END LOOP ;
470
471 END Val_Activity_Id ;
472
473
474 -- Validate setup id.
475 PROCEDURE Val_Setup_Id
476 ( p_setup_id IN NUMBER
477 , p_resource_id IN NUMBER
478 , p_organization_id IN NUMBER
479 , x_return_status IN OUT NOCOPY VARCHAR2
480 )
481 IS
482 CURSOR l_setup_csr( p_setup_id NUMBER
483 , p_resource_id NUMBER
484 , p_organization_id NUMBER
485 )
486 IS
487
488 SELECT 'Setup Id is invalid'
489 FROM DUAL
490 WHERE NOT EXISTS(
491 SELECT NULL
492 FROM BOM_RESOURCE_SETUPS brs
493 , BOM_SETUP_TYPES bst
494 WHERE brs.setup_id = bst.setup_id
495 AND brs.organization_id = bst.organization_id
496 AND brs.resource_id = p_resource_id
497 AND bst.organization_id = p_organization_id
498 AND bst.setup_id = p_setup_id
499 ) ;
500
501 BEGIN
502
503 x_return_status := FND_API.G_RET_STS_SUCCESS ;
504 FOR l_setup_rec IN l_setup_csr
505 ( p_setup_id
506 , p_resource_id
507 , p_organization_id
508 )
509 LOOP
510 x_return_status := FND_API.G_RET_STS_ERROR ;
511 END LOOP ;
512
513 END Val_Setup_Id ;
514
515
516 -- Validate usage rate or amount and inverse
517 PROCEDURE Val_Usage_Rate_or_Amount
518 ( p_usage_rate_or_amount IN NUMBER
519 , p_usage_rate_or_amount_inverse IN NUMBER
520 , x_return_status IN OUT NOCOPY VARCHAR2
521 )
522 IS
523
524
525 x_usage NUMBER := NULL ;
526 x_usage_inverse NUMBER := NULL ;
527 l_temp_status BOOLEAN := TRUE ;
528
529 -- Bug 2624883
530 x_usage_resiprocal NUMBER := NULL;
531 x_usage_inv_resiprocal NUMBER := NULL;
532 -- Bug 2624883
533
534 BEGIN
535
536 x_return_status := FND_API.G_RET_STS_SUCCESS ;
537
541 END IF ;
538 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
539 Error_Handler.Write_Debug ('Usage : ' || to_char(p_usage_rate_or_amount) );
540 Error_Handler.Write_Debug ('Usage Inv : ' || to_char(p_usage_rate_or_amount_inverse ) );
542
543 --
544 -- Bug 2624883
545 -- Major change in this validation
546 --
547
548 -- BUG 5896587
549 -- In ROUND function the decimal places have increased from 6 to 10 for Usage and Inverse Usage
550 /* For bug#7322996 , In ROUND function the decimal places rounding off value changed with G_round_off_val(profile value) */
551 x_usage := ROUND(p_usage_rate_or_amount, G_round_off_val);
552 x_usage_inverse := ROUND(p_usage_rate_or_amount_inverse, G_round_off_val);
553
554 if (p_usage_rate_or_amount = 0) then
555 x_usage_resiprocal := 0;
556 else
557 x_usage_resiprocal := ROUND((1/p_usage_rate_or_amount),G_round_off_val); /* Bug 7322996 */
558 end if;
559
560 if (p_usage_rate_or_amount_inverse = 0) then
561 x_usage_inv_resiprocal := 0;
562 else
563 x_usage_inv_resiprocal := ROUND((1/p_usage_rate_or_amount_inverse),G_round_off_val); /* Bug 7322996 */
564 end if;
565
566
567 -- Check usage rate and usage rate inverse
568 IF ( p_usage_rate_or_amount = 0 and p_usage_rate_or_amount_inverse = 0)
569 THEN
570 NULL;
571 ELSIF (p_usage_rate_or_amount = 0 AND
572 p_usage_rate_or_amount_inverse <> 0)
573 THEN
574 x_return_status := FND_API.G_RET_STS_ERROR ;
575 ELSIF (p_usage_rate_or_amount_inverse = 0 AND
576 p_usage_rate_or_amount <> 0)
577 THEN
578 x_return_status := FND_API.G_RET_STS_ERROR ;
579 ELSIF (round(p_usage_rate_or_amount,G_round_off_val) <> x_usage_inv_resiprocal) /* Bug 7322996 */
580 and
581 (x_usage_resiprocal <> round(p_usage_rate_or_amount_inverse,G_round_off_val)) /* Bug 7322996 */
582 THEN
583 x_return_status := FND_API.G_RET_STS_ERROR ;
584
585 END IF;
586 /*
587 x_usage := ROUND(p_usage_rate_or_amount, 6) ;
588 -- Check usage rate and usage rate inverse
589 IF x_usage = 0
590 AND p_usage_rate_or_amount_inverse <> 0
591 THEN
592 l_temp_status := FALSE ;
593
594 --
595 -- Usate Rate or Amound and Inverse 's length is 42 in FORM
596 --
597 ELSE
598 IF p_usage_rate_or_amount
599 <> to_number(SUBSTR(to_char(x_usage), 1, 42))
600 OR p_usage_rate_or_amount_inverse
601 <> to_number(SUBSTR(to_char(ROUND( 1/x_usage , 6)) , 1, 42))
602 THEN
603 l_temp_status := FALSE ;
604 END IF ;
605
606 END IF ;
607
608
609 x_usage_inverse := ROUND(p_usage_rate_or_amount_inverse , 6) ;
610
611 IF NOT l_temp_status
612 AND x_usage_inverse = 0
613 AND p_usage_rate_or_amount <> 0
614 THEN
615 x_return_status := FND_API.G_RET_STS_ERROR ;
616
617 --
618 -- Usate Rate or Amound and Inverse 's length is 42 in FORM
619 --
620 ELSIF NOT l_temp_status
621 THEN
622 IF p_usage_rate_or_amount_inverse
623 <> to_number(SUBSTR(to_char(x_usage_inverse), 1, 42))
624 OR p_usage_rate_or_amount
625 <> to_number( SUBSTR(to_char(ROUND( 1/ x_usage_inverse , 6 )), 1, 42 ))
626 THEN
627 x_return_status := FND_API.G_RET_STS_ERROR ;
628 END IF ;
629
630 END IF ;
631 */
632 /**** Comment out old validation for usage rate
633 -- Check usage rate and usage rate inverse
634 IF p_usage_rate_or_amount <> 0 THEN
635 IF ROUND(p_usage_rate_or_amount, 6) <>
636 ROUND((1 / p_usage_rate_or_amount_inverse), 6) THEN
637 x_return_status := FND_API.G_RET_STS_ERROR ;
638 END IF ;
639 ELSIF p_usage_rate_or_amount = 0 then
640 IF p_usage_rate_or_amount_inverse <> 0 THEN
641 x_return_status := FND_API.G_RET_STS_ERROR ;
642 END IF ;
643 END IF ;
644 ***************************************************/
645
646
647 END Val_Usage_Rate_or_Amount ;
648
649
650 -- Validate scheduled resource
651 PROCEDURE Val_Scheduled_Resource
652 ( p_op_seq_id IN NUMBER
653 , p_res_seq_num IN NUMBER
654 , p_schedule_flag IN NUMBER
655 , x_return_status IN OUT NOCOPY VARCHAR2
656 )
657 IS
658 CURSOR l_schedule_csr ( p_op_seq_id NUMBER
659 , p_res_seq_num NUMBER
660 , p_schedule_flag NUMBER
661 )
662 IS
663 SELECT 'Already exists'
664 FROM SYS.DUAL
665 WHERE EXISTS( SELECT NULL
666 FROM BOM_OPERATION_RESOURCES
667 WHERE schedule_flag = p_schedule_flag
668 AND resource_seq_num <> p_res_seq_num
669 AND operation_sequence_id = p_op_seq_id
670 ) ;
671
672
673 CURSOR l_rev_schedule_csr ( p_op_seq_id NUMBER
677 IS
674 , p_res_seq_num NUMBER
675 , p_schedule_flag NUMBER
676 )
678 SELECT 'Already exists'
679 FROM SYS.DUAL
680 WHERE EXISTS( SELECT NULL
681 FROM BOM_OPERATION_RESOURCES bor
682 , BOM_OPERATION_SEQUENCES bos
683 WHERE bor.schedule_flag = p_schedule_flag
684 AND bor.resource_seq_num <> p_res_seq_num
685 AND bor.operation_sequence_id = old_operation_sequence_id
686 AND bos.acd_type = l_ACD_CHANGE
687 AND bos.operation_sequence_Id = p_op_seq_id
688 ) ;
689
690
691
692 BEGIN
693
694 x_return_status := FND_API.G_RET_STS_SUCCESS ;
695
696 FOR l_schedule_rec IN l_schedule_csr ( p_op_seq_id
697 , p_res_seq_num
698 , p_schedule_flag
699 )
700 LOOP
701 x_return_status := FND_API.G_RET_STS_ERROR ;
702 END LOOP ;
703
704
705 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
706 THEN
707
708 FOR l_rev_schedule_rec IN l_rev_schedule_csr ( p_op_seq_id
709 , p_res_seq_num
710 , p_schedule_flag
711 )
712 LOOP
713 x_return_status := FND_API.G_RET_STS_ERROR ;
714 END LOOP ;
715 END IF ;
716
717
718 END Val_Scheduled_Resource ;
719
720
721 -- Validate scheduled resource
722 PROCEDURE Val_Scheduled_Resource
723 ( p_op_seq_id IN NUMBER
724 , p_res_seq_num IN NUMBER
725 , p_sch_seq_num IN NUMBER
726 , p_schedule_flag IN NUMBER
727 , x_return_status IN OUT NOCOPY VARCHAR2
728 )
729 IS
730 CURSOR l_schedule_csr ( p_op_seq_id NUMBER
731 , p_res_seq_num NUMBER
732 , p_sch_seq_num NUMBER
733 , p_schedule_flag NUMBER
734 )
735 IS
736 SELECT 'Already exists'
737 FROM SYS.DUAL
738 WHERE EXISTS( SELECT NULL
739 FROM BOM_OPERATION_RESOURCES
740 WHERE schedule_flag NOT IN (p_schedule_flag, l_NO_SCHEDULE)
741 AND operation_sequence_id = p_op_seq_id
742 AND resource_seq_num <> p_res_seq_num
743 AND schedule_seq_num = p_sch_seq_num
744 ) ;
745
746
747 CURSOR l_rev_schedule_csr ( p_op_seq_id NUMBER
748 , p_res_seq_num NUMBER
749 , p_sch_seq_num NUMBER
750 , p_schedule_flag NUMBER
751 )
752 IS
753 SELECT 'Already exists'
754 FROM SYS.DUAL
755 WHERE EXISTS( SELECT NULL
756 FROM BOM_OPERATION_RESOURCES bor
757 , BOM_OPERATION_SEQUENCES bos
758 WHERE bor.schedule_flag NOT IN (p_schedule_flag, l_NO_SCHEDULE)
759 AND bor.resource_seq_num <> p_res_seq_num
760 AND bor.operation_sequence_id = old_operation_sequence_id
761 AND bor.schedule_seq_num = p_sch_seq_num
762 AND bos.acd_type = l_ACD_CHANGE
763 AND bos.operation_sequence_Id = p_op_seq_id
764 ) ;
765
766 CURSOR l_yes_csr ( p_op_seq_id NUMBER
767 , p_res_seq_num NUMBER
768 , p_sch_seq_num NUMBER
769 , p_schedule_flag NUMBER
770 )
771 IS
772 SELECT 'Already exists'
773 FROM SYS.DUAL
774 WHERE EXISTS( SELECT NULL
775 FROM BOM_OPERATION_RESOURCES
776 WHERE schedule_flag IN (L_PRIOR, L_NEXT)
777 AND operation_sequence_id = p_op_seq_id
778 AND resource_seq_num <> p_res_seq_num
779 AND schedule_seq_num = p_sch_seq_num
780 ) ;
781
782
783 CURSOR l_rev_yes_csr ( p_op_seq_id NUMBER
784 , p_res_seq_num NUMBER
785 , p_sch_seq_num NUMBER
786 , p_schedule_flag NUMBER
787 )
788 IS
789 SELECT 'Already exists'
790 FROM SYS.DUAL
791 WHERE EXISTS( SELECT NULL
792 FROM BOM_OPERATION_RESOURCES bor
793 , BOM_OPERATION_SEQUENCES bos
797 AND bor.schedule_seq_num = p_sch_seq_num
794 WHERE bor.schedule_flag IN (L_PRIOR, L_NEXT)
795 AND bor.resource_seq_num <> p_res_seq_num
796 AND bor.operation_sequence_id = old_operation_sequence_id
798 AND bos.acd_type = l_ACD_CHANGE
799 AND bos.operation_sequence_Id = p_op_seq_id
800 ) ;
801
802 BEGIN
803
804 x_return_status := FND_API.G_RET_STS_SUCCESS ;
805 IF p_schedule_flag IN (L_PRIOR, L_NEXT) THEN
806 FOR l_schedule_rec IN l_schedule_csr ( p_op_seq_id
807 , p_res_seq_num
808 , p_sch_seq_num
809 , p_schedule_flag
810 )
811 LOOP
812 x_return_status := FND_API.G_RET_STS_ERROR ;
813 END LOOP ;
814
815 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
816 THEN
817
818 FOR l_rev_schedule_rec IN l_rev_schedule_csr ( p_op_seq_id
819 , p_res_seq_num
820 , p_sch_seq_num
821 , p_schedule_flag
822 )
823 LOOP
824 x_return_status := FND_API.G_RET_STS_ERROR ;
825 END LOOP ;
826 END IF ;
827 ELSIF p_schedule_flag = L_YES_SCHEDULE THEN
828 FOR l_schedule_rec IN l_yes_csr ( p_op_seq_id
829 , p_res_seq_num
830 , p_sch_seq_num
831 , p_schedule_flag
832 )
833 LOOP
834 x_return_status := FND_API.G_RET_STS_ERROR ;
835 END LOOP ;
836
837 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
838 THEN
839
840 FOR l_rev_schedule_rec IN l_rev_yes_csr ( p_op_seq_id
841 , p_res_seq_num
842 , p_sch_seq_num
843 , p_schedule_flag
844 )
845 LOOP
846 x_return_status := FND_API.G_RET_STS_ERROR ;
847 END LOOP ;
848 END IF ;
849 END IF;
850
851 END Val_Scheduled_Resource ;
852
853 -- Validate autocharge for OSP resource
854 PROCEDURE Val_Autocharge_for_OSP_Res
855 ( p_resource_id IN NUMBER
856 , p_organization_id IN NUMBER
857 , x_return_status IN OUT NOCOPY VARCHAR2
858 )
859 IS
860 CURSOR l_res_osp_csr ( p_resource_id NUMBER
861 , p_organization_id NUMBER
862 )
863 IS
864 SELECT 'Not OSP Resource'
865 FROM SYS.DUAL
866 WHERE EXISTS ( SELECT NULL
867 FROM BOM_RESOURCES
868 WHERE resource_id = p_resource_id
869 AND organization_id = p_organization_id
870 AND cost_code_type <> l_OSP -- 4 : Outside Processing
871 ) ;
872
873
874 BEGIN
875
876 x_return_status := FND_API.G_RET_STS_SUCCESS ;
877
878 FOR l_res_osp_rec IN l_res_osp_csr ( p_resource_id
879 , p_organization_id
880 )
881 LOOP
882 x_return_status := FND_API.G_RET_STS_ERROR ;
883 END LOOP ;
884
885
886 END Val_Autocharge_for_OSP_Res ;
887
888
889 -- Validate autocharge: PO Move
890 PROCEDURE Val_PO_Move
891 ( p_op_seq_id IN NUMBER
892 , p_res_seq_num IN NUMBER
893 , x_return_status IN OUT NOCOPY VARCHAR2
894 )
895 IS
896 CURSOR l_pomove_csr ( p_op_seq_id NUMBER
897 , p_res_seq_num NUMBER
898 )
899 IS
900 SELECT 'Already exists'
901 FROM SYS.DUAL
902 WHERE EXISTS( SELECT NULL
903 FROM BOM_OPERATION_RESOURCES
904 WHERE autocharge_type = l_PO_MOVE
905 AND resource_seq_num <> p_res_seq_num
906 AND operation_sequence_id = p_op_seq_id
907 ) ;
908
909
910 CURSOR l_rev_pomove_csr ( p_op_seq_id NUMBER
911 , p_res_seq_num NUMBER
912 )
913 IS
914 SELECT 'Already exists'
915 FROM SYS.DUAL
916 WHERE EXISTS( SELECT NULL
917 FROM BOM_OPERATION_RESOURCES bor
918 , BOM_OPERATION_SEQUENCES bos
919 WHERE bor.autocharge_type = l_PO_MOVE
920 AND bor.resource_seq_num <> p_res_seq_num
924 ) ;
921 AND bor.operation_sequence_id = old_operation_sequence_id
922 AND bos.acd_type = l_ACD_CHANGE
923 AND bos.operation_sequence_id = p_op_seq_id
925
926
927
928 BEGIN
929
930 x_return_status := FND_API.G_RET_STS_SUCCESS ;
931
932 FOR l_pomove_rec IN l_pomove_csr ( p_op_seq_id
933 , p_res_seq_num
934 )
935 LOOP
936 x_return_status := FND_API.G_RET_STS_ERROR ;
937 END LOOP ;
938
939
940 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
941 THEN
942
943 FOR l_rev_pomove_rec IN l_rev_pomove_csr ( p_op_seq_id
944 , p_res_seq_num
945 )
946 LOOP
947 x_return_status := FND_API.G_RET_STS_ERROR ;
948 END LOOP ;
949
950 END IF ;
951
952 END Val_PO_Move ;
953
954
955 -- Check if dept has location
956 PROCEDURE Val_Dept_Has_Location
957 ( p_op_seq_id IN NUMBER
958 , x_return_status IN OUT NOCOPY VARCHAR2
959 )
960 IS
961 CURSOR l_dept_loc_csr ( p_op_seq_id NUMBER)
962 IS
963 SELECT 'No Dept Location'
964 FROM SYS.DUAL
965 WHERE NOT EXISTS( SELECT NULL
966 FROM BOM_OPERATION_SEQUENCES bos
967 , BOM_DEPARTMENTS bd
968 WHERE bd.location_id IS NOT NULL
969 AND bd.department_id = bos.department_id
970 AND bos.operation_sequence_id = p_op_seq_id
971 ) ;
972
973
974 BEGIN
975 x_return_status := FND_API.G_RET_STS_SUCCESS ;
976
977 FOR l_dept_loc_rec IN l_dept_loc_csr ( p_op_seq_id )
978 LOOP
979 x_return_status := FND_API.G_RET_STS_ERROR ;
980 END LOOP ;
981
982
983 END Val_Dept_Has_Location ;
984
985
986 PROCEDURE Get_Resource_Uom
987 ( p_resource_id IN NUMBER
988 , x_hour_uom_code IN OUT NOCOPY VARCHAR2
989 , x_hour_uom_class IN OUT NOCOPY VARCHAR2
990 , x_res_uom_code IN OUT NOCOPY VARCHAR2
991 , x_res_uom_class IN OUT NOCOPY VARCHAR2
992 )
993 IS
994
995 CURSOR l_class_csr(p_uom_code VARCHAR2)
996 IS
997 SELECT uom_class
998 FROM MTL_UNITS_OF_MEASURE
999 WHERE uom_code = p_uom_code ;
1000
1001
1002 CURSOR l_uom_csr ( p_resource_id NUMBER )
1003 IS
1004 SELECT unit_of_measure
1005 FROM BOM_RESOURCES
1006 WHERE resource_id = p_resource_id ;
1007
1008 BEGIN
1009
1010 -- Get Hour UOM Code from Profile Opetion
1011 x_hour_uom_code := FND_PROFILE.VALUE('BOM:HOUR_UOM_CODE') ;
1012
1013 -- Get Hour UOM Class
1014 FOR l_class_rec IN l_class_csr(p_uom_code => x_hour_uom_code)
1015 LOOP
1016 x_hour_uom_class := l_class_rec.uom_class ;
1017 END LOOP ;
1018
1019 -- Get Resource UOM Code
1020 FOR l_uom_rec in l_uom_csr(p_resource_id)
1021 LOOP
1022 x_res_uom_code := l_uom_rec.unit_of_measure ;
1023 END LOOP ;
1024
1025 -- Get Resource UOM Class
1026 FOR l_class_rec IN l_class_csr(p_uom_code => x_res_uom_code)
1027 LOOP
1028 x_res_uom_class := l_class_rec.uom_class ;
1029 END LOOP ;
1030
1031 END Get_Resource_Uom ;
1032
1033
1034
1035 PROCEDURE Val_Res_UOM_For_Schedule
1036 ( p_hour_uom_class IN VARCHAR2
1037 , p_res_uom_class IN VARCHAR2
1038 , p_hour_uom_code IN VARCHAR2
1039 , p_res_uom_code IN VARCHAR2
1040 , x_return_status IN OUT NOCOPY VARCHAR2
1041 )
1042 IS
1043 CURSOR l_conversion_csr ( p_res_uom_code VARCHAR2
1044 , p_res_uom_class VARCHAR2
1045 , p_hour_uom_code VARCHAR2
1046 )
1047 IS
1048 SELECT 'Unable to convert'
1049 FROM SYS.DUAL
1050 WHERE NOT EXISTS(
1051 SELECT NULL
1052 FROM MTL_UOM_CONVERSIONS muc1,
1053 MTL_UOM_CONVERSIONS muc2
1054 WHERE muc1.uom_code = p_res_uom_code
1055 AND muc1.uom_class = p_res_uom_class
1056 AND muc1.inventory_item_id = 0
1057 AND NVL(muc1.disable_date, SYSDATE + 1) > SYSDATE
1058 AND muc2.uom_code = p_hour_uom_code
1059 AND muc2.inventory_item_id = 0
1060 AND muc2.uom_class = muc1.uom_class ) ;
1061
1062 BEGIN
1063
1064 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1065
1066
1067 IF p_hour_uom_class <> p_res_uom_class THEN
1071 ( p_res_uom_code
1068 x_return_status := FND_API.G_RET_STS_ERROR ;
1069 ELSE
1070 FOR l_conversion_rec IN l_conversion_csr
1072 , p_res_uom_class
1073 , p_hour_uom_code )
1074
1075 LOOP
1076 x_return_status := FND_API.G_RET_STS_ERROR ;
1077 END LOOP ;
1078 END IF ;
1079
1080 END Val_Res_UOM_For_Schedule ;
1081
1082
1083 PROCEDURE Val_Negative_Usage_Rate
1084 ( p_autocharge_type IN NUMBER
1085 , p_schedule_flag IN NUMBER
1086 , p_hour_uom_class IN VARCHAR2
1087 , p_res_uom_class IN VARCHAR2
1088 , x_return_status IN OUT NOCOPY VARCHAR2
1089 )
1090 IS
1091
1092 BEGIN
1093
1094 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1095
1096 IF ( p_autocharge_type IN (l_PO_RECEIPT, l_PO_MOVE)
1097 OR p_schedule_flag <> l_NO_SCHEDULE
1098 -- OR p_hour_uom_class = p_res_uom_class -- Form allows this case
1099 )
1100 THEN
1101 x_return_status := FND_API.G_RET_STS_ERROR ;
1102 END IF ;
1103
1104 END Val_Negative_Usage_Rate ;
1105
1106 PROCEDURE Val_Principal_Res_Unique
1107 ( p_op_seq_id IN NUMBER
1108 , p_res_seq_num IN NUMBER
1109 , p_sub_group_num IN NUMBER
1110 , x_return_status IN OUT NOCOPY VARCHAR2
1111 )
1112 IS
1113 CURSOR l_principal_csr ( p_op_seq_id NUMBER
1114 , p_res_seq_num NUMBER
1115 , p_sub_group_num NUMBER
1116 )
1117 IS
1118 SELECT 'Already exists'
1119 FROM SYS.DUAL
1120 WHERE EXISTS( SELECT NULL
1121 FROM BOM_OPERATION_RESOURCES
1122 WHERE principle_flag = 1 -- Yes
1123 AND NVL(acd_type, l_ACD_ADD) <> l_ACD_DISABLE
1124 AND nvl(substitute_group_num, resource_seq_num) = nvl(p_sub_group_num, p_res_seq_num)
1125 AND resource_seq_num <> p_res_seq_num
1126 AND operation_sequence_id = p_op_seq_id
1127 ) ;
1128
1129 CURSOR l_rev_principal_csr ( p_op_seq_id NUMBER
1130 , p_res_seq_num NUMBER
1131 , p_sub_group_num NUMBER
1132 )
1133 IS
1134 SELECT 'Already exists'
1135 FROM SYS.DUAL
1136 WHERE EXISTS( SELECT NULL
1137 FROM BOM_OPERATION_RESOURCES bor
1138 , BOM_OPERATION_SEQUENCES bos
1139 WHERE bor.principle_flag = 1 -- Yes
1140 AND bor.substitute_group_num = p_sub_group_num
1141 AND bor.resource_seq_num <> p_res_seq_num
1142 AND bor.operation_sequence_id = bos.old_operation_sequence_id
1143 AND bos.acd_type = l_ACD_CHANGE
1144 AND bos.operation_sequence_id = p_op_seq_id
1145 ) ;
1146
1147
1148
1149 BEGIN
1150
1151 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1152
1153 FOR l_principal_rec IN l_principal_csr ( p_op_seq_id
1154 , p_res_seq_num
1155 , p_sub_group_num
1156 )
1157 LOOP
1158 x_return_status := FND_API.G_RET_STS_ERROR ;
1159 END LOOP ;
1160
1161
1162 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
1163 THEN
1164
1165 FOR l_rev_principal_rec IN l_rev_principal_csr ( p_op_seq_id
1166 , p_res_seq_num
1167 , p_sub_group_num
1168 )
1169 LOOP
1170 x_return_status := FND_API.G_RET_STS_ERROR ;
1171 END LOOP ;
1172 END IF ;
1173 END Val_Principal_Res_Unique ;
1174
1175
1176 -- Simultaneous resources should have the same SGN to share the same alternates
1177 -- i.e for one SSN, there can be only one SGN associated
1178 /* Fix for bug 4506885 - Added parameter p_sub_grp_num to Val_Schedule_Seq_Num procedure. */
1179 PROCEDURE Val_Schedule_Seq_Num
1180 ( p_op_seq_id IN NUMBER
1181 , p_res_seq_num IN NUMBER
1182 , p_sch_seq_num IN NUMBER
1183 , p_sub_grp_num IN NUMBER
1184 , x_return_status IN OUT NOCOPY VARCHAR2
1185 )
1186 IS
1187 /* Fix for bug 4506885 - Modified the cursor c_same_sign to check for substitute_group_num <> p_sub_grp_num.
1188 Previously it was checking for substitute_group_num is not null. */
1189 cursor c_same_sgn is
1190 select 1 from dual
1191 where exists (select 1 --schedule_seq_num, count(distinct(substitute_group_num)) sgn_count
1192 from bom_operation_resources
1193 where operation_sequence_id = p_op_seq_id
1194 and schedule_seq_num = p_sch_seq_num
1198 x_return_status := FND_API.G_RET_STS_SUCCESS;
1195 and substitute_group_num <> p_sub_grp_num /* is not null*/
1196 and resource_seq_num <> p_res_seq_num);
1197 BEGIN
1199
1200 FOR c1 in c_same_sgn LOOP
1201 x_return_status := Error_Handler.G_STATUS_ERROR;
1202 END LOOP;
1203
1204 END Val_Schedule_Seq_Num;
1205
1206
1207 PROCEDURE Val_Sgn_Order
1208 ( p_op_seq_id IN NUMBER
1209 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1210 , x_return_status IN OUT NOCOPY VARCHAR2
1211 )
1212 IS
1213 Cursor Check1 is
1214 select substitute_group_num,
1215 min(schedule_seq_num) mn_ssn1, min(resource_seq_num) mn_rsn1,
1216 max(schedule_seq_num) mx_ssn1, max(resource_seq_num) mx_rsn1
1217 from bom_operation_resources
1218 where operation_sequence_id = p_op_seq_id
1219 and substitute_group_num is not null
1220 group by substitute_group_num
1221 order by substitute_group_num;
1222
1223 Cursor Check2 (l_sgn number) is
1224 select substitute_group_num,
1225 min(schedule_seq_num) mn_ssn2, --min(resource_seq_num) mn_rsn2,
1226 max(schedule_seq_num) mx_ssn2 --max(resource_seq_num) mx_rsn2
1227 from bom_sub_operation_resources
1228 where operation_sequence_id = p_op_seq_id
1229 and substitute_group_num = l_sgn
1230 group by substitute_group_num
1231 order by substitute_group_num;
1232
1233 first_row_outer boolean;
1234 first_row_inner boolean;
1235 temp_outer number;
1236 temp_inner number;
1237 init Check2%rowtype;
1238
1239 BEGIN
1240 first_row_outer := false;
1241 first_row_inner := false;
1242 temp_outer := 0;
1243 temp_inner := 0;
1244 x_return_status := FND_API.G_RET_STS_SUCCESS;
1245
1246 FOR i IN Check1 LOOP
1247 IF first_row_outer = TRUE THEN
1248 IF nvl(i.mn_ssn1, i.mn_rsn1) > temp_outer
1249 AND nvl(i.mn_ssn1, i.mn_rsn1) > temp_inner THEN
1250 FOR j IN Check2(i.substitute_group_num) LOOP
1251 IF j.mn_ssn2 <= temp_inner
1252 OR j.mn_ssn2 <= temp_outer THEN
1253 Error_Handler.Add_Error_Token
1254 ( p_Message_Name => 'BOM_LARGE_SGN_SSN'
1255 , p_mesg_token_tbl => x_mesg_token_tbl
1256 , x_mesg_token_tbl => x_mesg_token_tbl
1257 --, p_Token_Tbl => l_token_tbl
1258 ) ;
1259 x_return_status := Error_Handler.G_STATUS_ERROR;
1260 return;
1261 END IF; --nvl(j)
1262 temp_inner := nvl(j.mx_ssn2, 0);
1263 END LOOP;
1264 ELSE
1265 Error_Handler.Add_Error_Token
1266 ( p_Message_Name => 'BOM_LARGE_SGN_SSN'
1267 , p_mesg_token_tbl => x_mesg_token_tbl
1268 , x_mesg_token_tbl => x_mesg_token_tbl
1269 --, p_Token_Tbl => l_token_tbl
1270 ) ;
1271 x_return_status := Error_Handler.G_STATUS_ERROR;
1272 return;
1273 END IF; --nvl(i)
1274 temp_outer := nvl(i.mx_ssn1, i.mx_rsn1);
1275 ELSE
1276 temp_outer := nvl(i.mx_ssn1, i.mx_rsn1);
1277 first_row_outer := TRUE;
1278 OPEN Check2(i.substitute_group_num);
1279 FETCH Check2 INTO init;
1280 temp_inner := nvl(init.mx_ssn2, 0);
1281 CLOSE Check2;
1282 END IF;
1283 END LOOP;
1284 END Val_Sgn_Order;
1285
1286 /******************************************************************
1287 * Procedure : Check_Existence used by RTG BO
1288 * Parameters IN : Operation Resource exposed column record
1289 * Operation Resource unexposed column record
1290 * Parameters out: Old Operation Resource exposed column record
1291 * Old Operation Resource unexposed column record
1292 * Mesg Token Table
1293 * Return Status
1294 * Purpose : Convert Routing Op Resource to Revised Op Resource and
1295 * Call Check_Existence for ECO Bo.
1296 * After calling Check_Existence, convert old Revised
1297 * Op Resource record back to Routing Op Resource
1298 *********************************************************************/
1299 PROCEDURE Check_Existence
1300 ( p_op_resource_rec IN Bom_Rtg_Pub.Op_Resource_Rec_Type
1301 , p_op_res_unexp_rec IN Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
1302 , x_old_op_resource_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Resource_Rec_Type
1303 , x_old_op_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
1304 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1305 , x_return_status IN OUT NOCOPY VARCHAR2
1306 )
1307
1308 IS
1309 l_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
1310 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type ;
1311 l_old_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
1312 l_old_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type ;
1313
1314 BEGIN
1318 , p_rtg_op_res_unexp_rec => p_op_res_unexp_rec
1315 -- Convert Routing Operation to ECO Operation
1316 Bom_Rtg_Pub.Convert_RtgRes_To_EcoRes
1317 ( p_rtg_op_resource_rec => p_op_resource_rec
1319 , x_rev_op_resource_rec => l_rev_op_resource_rec
1320 , x_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
1321 ) ;
1322
1323 -- Call Check_Existence
1324 Bom_Validate_Op_Res.Check_Existence
1325 ( p_rev_op_resource_rec => l_rev_op_resource_rec
1326 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
1327 , x_old_rev_op_resource_rec => l_old_rev_op_resource_rec
1328 , x_old_rev_op_res_unexp_rec => l_old_rev_op_res_unexp_rec
1329 , x_return_status => x_return_status
1330 , x_mesg_token_tbl => x_mesg_token_tbl
1331 ) ;
1332
1333 -- Convert old Eco Opeartion Record back to Routing Operation
1334 Bom_Rtg_Pub.Convert_EcoRes_To_RtgRes
1335 ( p_rev_op_resource_rec => l_old_rev_op_resource_rec
1336 , p_rev_op_res_unexp_rec => l_old_rev_op_res_unexp_rec
1337 , x_rtg_op_resource_rec => x_old_op_resource_rec
1338 , x_rtg_op_res_unexp_rec => x_old_op_res_unexp_rec
1339 ) ;
1340
1341
1342 END Check_Existence ;
1343
1344
1345 /******************************************************************
1346 * Procedure : Check_Existence used by ECO BO
1347 * and internally called by RTG BO
1348 * Parameters IN : Revised operation resource exposed column record
1349 * Revised operation resource unexposed column record
1350 * Parameters out: Old Revised operation resource exposed column record
1351 * Old Revised operation resource unexposed column record
1352 * Mesg Token Table
1353 * Return Status
1354 * Purpose : Check_Existence will query using the primary key
1355 * information and return a success if the operation
1356 * resource is CREATE and the record EXISTS or will
1357 * return an error if the operation resource is UPDATE
1358 * and record DOES NOT EXIST.
1359 * In case of UPDATE if record exists, then the procedure
1360 * will return old record in the old entity parameters
1361 * with a success status.
1362 *********************************************************************/
1363
1364 PROCEDURE Check_Existence
1365 ( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
1366 , p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
1367 , x_old_rev_op_resource_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
1368 , x_old_rev_op_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
1369 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1370 , x_return_status IN OUT NOCOPY VARCHAR2
1371 )
1372 IS
1373 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1374 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
1375 l_return_status VARCHAR2(1);
1376
1377 BEGIN
1378
1379 l_Token_Tbl(1).Token_Name := 'RES_SEQ_NUMBER';
1380 l_Token_Tbl(1).Token_Value := p_rev_op_resource_rec.resource_sequence_number ;
1381 l_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
1382 l_Token_Tbl(2).Token_Value := p_rev_op_resource_rec.revised_item_name;
1383
1384 Bom_Op_Res_Util.Query_Row
1385 ( p_resource_sequence_number => p_rev_op_resource_rec.resource_sequence_number
1386 , p_operation_sequence_id => p_rev_op_res_unexp_rec.operation_sequence_id
1387 , p_acd_type => p_rev_op_resource_rec.acd_type
1388 , p_mesg_token_tbl => l_mesg_token_tbl
1389 , x_rev_op_resource_rec => x_old_rev_op_resource_rec
1390 , x_rev_op_res_unexp_rec => x_old_rev_op_res_unexp_rec
1391 , x_mesg_token_tbl => l_mesg_token_tbl
1392 , x_return_status => l_return_status
1393 ) ;
1394
1395 IF l_return_status = BOM_Rtg_Globals.G_RECORD_FOUND AND
1396 p_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE THEN
1397 Error_Handler.Add_Error_Token
1398 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
1399 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1400 , p_message_name => 'BOM_RES_ALREADY_EXISTS'
1401 , p_token_tbl => l_token_tbl
1402 ) ;
1403 l_return_status := FND_API.G_RET_STS_ERROR ;
1404
1405 ELSIF l_return_status = BOM_Rtg_Globals.G_RECORD_NOT_FOUND AND
1406 p_rev_op_resource_rec.transaction_type IN
1407 ( BOM_Rtg_Globals.G_OPR_UPDATE, BOM_Rtg_Globals.G_OPR_DELETE)
1408 THEN
1409 Error_Handler.Add_Error_Token
1410 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
1411 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1412 , p_message_name => 'BOM_RES_DOESNOT_EXIST'
1413 , p_token_tbl => l_token_tbl
1414 ) ;
1415 l_return_status := FND_API.G_RET_STS_ERROR ;
1416
1417 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1418 THEN
1422 , p_message_name => NULL
1419 Error_Handler.Add_Error_Token
1420 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
1421 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1423 , p_message_text => 'Unexpected error while existence verification of '
1424 || 'Operation Resources '
1425 || p_rev_op_resource_rec.resource_sequence_number
1426 , p_token_tbl => l_token_tbl
1427 ) ;
1428 ELSE
1429 l_return_status := FND_API.G_RET_STS_SUCCESS;
1430 END IF ;
1431
1432 x_return_status := l_return_status;
1433 x_mesg_token_tbl := l_Mesg_Token_Tbl;
1434
1435 END Check_Existence;
1436
1437
1438 /******************************************************************
1439 * Procedure : Check_NonRefEvent used by RTG BO and ECO BO
1440 * Parameters IN : Operation Sequence Id, Resource Seq Num, Op Seq Num
1441 * Operation Type
1442 * Parameters out: Error Code
1443 * Return Status
1444 * Purpose : Convert Routing Op Resource to Revised Op Resource and
1445 * Call Check_Existence for ECO Bo.
1446 * After calling Check_Existence, convert old Revised
1447 * Op Resource record back to Routing Op Resource
1448 *********************************************************************/
1449
1450 PROCEDURE Check_NonRefEvent
1451 ( p_operation_sequence_id IN NUMBER
1452 , p_operation_type IN NUMBER
1453 , p_entity_processed IN VARCHAR2
1454 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1455 , x_return_status IN OUT NOCOPY VARCHAR2
1456 )
1457 IS
1458
1459
1460 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
1461 l_return_status VARCHAR2(1);
1462 l_err_text VARCHAR2(2000) ;
1463
1464 -- Get ref flag and operation type
1465 CURSOR l_event_cur (p_op_seq_id NUMBER)
1466 IS
1467 SELECT reference_flag
1468 FROM BOM_OPERATION_SEQUENCES
1469 WHERE operation_sequence_id = p_op_seq_id ;
1470
1471 l_event_rec l_event_cur%ROWTYPE ;
1472
1473 PRT_OP_NOT_EVENT EXCEPTION ;
1474 EAM_SUB_RES_NOT_ACCESS EXCEPTION ; -- Added for eAM enhancement
1475
1476 BEGIN
1477 x_return_status := FND_API.G_RET_STS_SUCCESS;
1478 l_return_status := FND_API.G_RET_STS_SUCCESS;
1479
1480 -- For eAM enhancement, currently maintenance routings do not
1481 -- support sub operation resources fanctionality.
1482 -- This validation will be removed in future.
1483 IF BOM_Rtg_Globals.Get_Eam_Item_Type = BOM_Rtg_Globals.G_ASSET_ACTIVITY
1484 AND p_entity_processed = 'SR' -- called from sub resources entity
1485 THEN
1486
1487 RAISE EAM_SUB_RES_NOT_ACCESS ;
1488
1489 END IF ; -- end of eAM enhancement
1490
1491
1492 IF NVL(p_operation_type, 1) <> l_EVENT
1493 AND p_operation_type <> FND_API.G_MISS_NUM
1494 THEN
1495 RAISE PRT_OP_NOT_EVENT ;
1496 END IF ;
1497
1498 OPEN l_event_cur( p_op_seq_id => p_operation_sequence_id) ;
1499 FETCH l_event_cur INTO l_event_rec ;
1500
1501 IF l_event_cur%FOUND THEN
1502 IF l_event_rec.reference_flag = 1
1503 THEN
1504 l_return_status := FND_API.G_RET_STS_ERROR ;
1505 END IF ;
1506 ELSE
1507 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1508 END IF ;
1509
1510 CLOSE l_event_cur ;
1511
1512 x_return_status := l_return_status ;
1513 x_mesg_token_tbl := l_mesg_token_tbl ;
1514
1515 EXCEPTION
1516 -- Added for eAM enhancement
1517 WHEN EAM_SUB_RES_NOT_ACCESS THEN
1518
1519 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1520 ('Maintenance routings do not support sub operation resources fanctionality') ;
1521 END IF ;
1522
1523 -- Return the 'EAM'.
1524 x_return_status := 'EAM' ;
1525
1526
1527 WHEN PRT_OP_NOT_EVENT THEN
1528 -- Return the status and message table.
1529 x_return_status := FND_API.G_RET_STS_ERROR ;
1530
1531
1532 WHEN OTHERS THEN
1533 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1534 ('Some unknown error in Check non-ref operation . . .' || SQLERRM );
1535 END IF ;
1536
1537
1538 l_err_text := G_PKG_NAME || ' Validation (Check Non-Ref Op of Event) '
1539 || substrb(SQLERRM,1,200);
1540
1541 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
1542
1543 Error_Handler.Add_Error_Token
1544 ( p_message_name => NULL
1545 , p_message_text => l_err_text
1546 , p_mesg_token_tbl => l_mesg_token_tbl
1547 , x_mesg_token_tbl => l_mesg_token_tbl
1548 ) ;
1549
1550 -- Return the status and message table.
1551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1552 x_mesg_token_tbl := l_mesg_token_tbl ;
1553
1557 /********************************************************************
1554 END Check_NonRefEvent ;
1555
1556
1558 * Procedure : Check_Attributes used by RTG BO
1559 * Parameters IN : Operation Resource exposed column record
1560 * Operation Resource unexposed column record
1561 * Parameters out: Return Status
1562 * Message Token Table
1563 * Purpose : Convert Routing Operation Resource to ECO Operation
1564 * Resource and Call Check_Attributes for ECO BO.
1565 * Check_Attributes will verify the exposed attributes
1566 * of the operation resource record in their own entirety.
1567 * No cross entity validations will be performed.
1568 ********************************************************************/
1569 PROCEDURE Check_Attributes
1570 ( p_op_resource_rec IN Bom_Rtg_Pub.Op_Resource_Rec_Type
1571 , p_op_res_unexp_rec IN Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
1572 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1573 , x_return_status IN OUT NOCOPY VARCHAR2
1574 )
1575 IS
1576
1577 l_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
1578 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type ;
1579
1580 BEGIN
1581
1582 -- Convert Routing Operation to ECO Operation
1583 Bom_Rtg_Pub.Convert_RtgRes_To_EcoRes
1584 ( p_rtg_op_resource_rec => p_op_resource_rec
1585 , p_rtg_op_res_unexp_rec => p_op_res_unexp_rec
1586 , x_rev_op_resource_rec => l_rev_op_resource_rec
1587 , x_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
1588 ) ;
1589
1590 -- Call Check Attributes procedure
1591 Bom_Validate_Op_Res.Check_Attributes
1592 ( p_rev_op_resource_rec => l_rev_op_resource_rec
1593 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
1594 , x_return_status => x_return_status
1595 , x_mesg_token_tbl => x_mesg_token_tbl
1596 ) ;
1597
1598 END Check_Attributes ;
1599
1600
1601 /***************************************************************
1602 * Procedure : Check_Attribute (Validation) for CREATE and UPDATE
1603 * by ECO BO and internally called by RTG BO
1604 * Parameters IN : Revised Operation Resource exposed column record
1605 * Revised Operation Resource unexposed column record
1606 * Parameters out: Return Status
1607 * Message Token Table
1608 * Purpose : Attribute validation procedure will validate each
1609 * attribute of Revised operation resource in its entirety.
1610 * If the validation of a column requires looking at some
1611 * other columns value then the validation is done at
1612 * the Entity level instead.
1613 * All errors in the attribute validation are accumulated
1614 * before the procedure returns with a Return_Status
1615 * of 'E'.
1616 *********************************************************************/
1617 PROCEDURE Check_Attributes
1618 ( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
1619 , p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
1620 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1621 , x_return_status IN OUT NOCOPY VARCHAR2
1622 )
1623 IS
1624
1625 l_return_status VARCHAR2(1) ;
1626 l_err_text VARCHAR2(2000) ;
1627 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
1628 l_Token_Tbl Error_Handler.Token_Tbl_Type ;
1629
1630 BEGIN
1631
1632 l_return_status := FND_API.G_RET_STS_SUCCESS;
1633 x_return_status := FND_API.G_RET_STS_SUCCESS;
1634
1635 -- Set the first token to be equal to the operation sequence number
1636 l_Token_Tbl(1).token_name := 'RES_SEQ_NUMBER';
1637 l_Token_Tbl(1).token_value := p_rev_op_resource_rec.resource_sequence_number ;
1638
1639 --
1640 -- Check if the user is trying to update a record with
1641 -- missing value when the column value is required.
1642 --
1643
1644 IF p_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
1645 THEN
1646
1647 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1648 ('Operation Resource Attr Validation: Missing Value. . . ' || l_return_status) ;
1649 END IF;
1650
1651 -- Resource Code
1652 IF p_rev_op_resource_rec.resource_code = FND_API.G_MISS_CHAR
1653 THEN
1654 Error_Handler.Add_Error_Token
1655 ( p_Message_Name => 'BOM_RES_RESCODE_MISSING'
1656 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1657 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1658 , p_Token_Tbl => l_Token_Tbl
1659 );
1660 l_return_status := FND_API.G_RET_STS_ERROR;
1661 END IF ;
1662
1663
1664 -- Standard Rate Flag
1665 IF p_rev_op_resource_rec.standard_rate_flag = FND_API.G_MISS_NUM
1666 THEN
1667 Error_Handler.Add_Error_Token
1668 ( p_Message_Name => 'BOM_RES_STD_RATE_MISSING'
1669 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1670 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1674 END IF;
1671 , p_Token_Tbl => l_Token_Tbl
1672 );
1673 l_return_status := FND_API.G_RET_STS_ERROR;
1675
1676
1677 -- Assigned Units
1678 IF p_rev_op_resource_rec.assigned_units = FND_API.G_MISS_NUM
1679 THEN
1680 Error_Handler.Add_Error_Token
1681 ( p_Message_Name => 'BOM_RES_ASSIGNED_UNITS_MISSING'
1682 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1683 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1684 , p_Token_Tbl => l_Token_Tbl
1685 );
1686 l_return_status := FND_API.G_RET_STS_ERROR;
1687 END IF;
1688
1689
1690 -- Usage Rate or Amount
1691 IF p_rev_op_resource_rec.usage_rate_or_amount = FND_API.G_MISS_NUM
1692 THEN
1693 Error_Handler.Add_Error_Token
1694 ( p_Message_Name => 'BOM_RES_RATE_AMT_MISSING'
1695 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1696 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1697 , p_Token_Tbl => l_Token_Tbl
1698 );
1699 l_return_status := FND_API.G_RET_STS_ERROR;
1700 END IF;
1701
1702 -- Usage Rate or Amount Inverse
1703 IF p_rev_op_resource_rec.usage_rate_or_amount_inverse = FND_API.G_MISS_NUM
1704 THEN
1705 Error_Handler.Add_Error_Token
1706 ( p_Message_Name => 'BOM_RES_RATE_AMT_INVRS_MISSING'
1707 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1708 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1709 , p_Token_Tbl => l_Token_Tbl
1710 );
1711 l_return_status := FND_API.G_RET_STS_ERROR;
1712 END IF;
1713
1714 -- Basis Type
1715 IF p_rev_op_resource_rec.basis_type = FND_API.G_MISS_NUM
1716 THEN
1717 Error_Handler.Add_Error_Token
1718 ( p_Message_Name => 'BOM_RES_BASISTYPE_MISSING'
1719 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1720 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1721 , p_Token_Tbl => l_Token_Tbl
1722 );
1723 l_return_status := FND_API.G_RET_STS_ERROR;
1724 END IF;
1725
1726
1727 -- Schedule Flag
1728 IF p_rev_op_resource_rec.schedule_flag = FND_API.G_MISS_NUM
1729 THEN
1730 Error_Handler.Add_Error_Token
1731 ( p_Message_Name => 'BOM_RES_SCHEDULEFLAG_MISSING'
1732 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1733 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1734 , p_Token_Tbl => l_Token_Tbl
1735 );
1736 l_return_status := FND_API.G_RET_STS_ERROR;
1737 END IF ;
1738
1739
1740 -- Autocharge Type
1741 IF p_rev_op_resource_rec.autocharge_type = FND_API.G_MISS_NUM
1742 THEN
1743 Error_Handler.Add_Error_Token
1744 ( p_Message_Name => 'BOM_RES_ACHARGE_TYPE_MISSING'
1745 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1746 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1747 , p_Token_Tbl => l_Token_Tbl
1748 );
1749 l_return_status := FND_API.G_RET_STS_ERROR;
1750 END IF ;
1751
1752 END IF ;
1753
1754 --
1755 -- Check if the user is trying to create/update a record with
1756 -- invalid value.
1757 --
1758
1759 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1760 ('Operation Resource Attr Validation: Invalid Value. . . ' || l_return_status) ;
1761 END IF;
1762
1763 -- Resource Code or Resource Id
1764 IF p_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
1765 AND NVL(p_rev_op_resource_rec.acd_type, l_ACD_ADD) = l_ACD_ADD
1766 AND ( p_rev_op_res_unexp_rec.resource_id IS NULL
1767 OR p_rev_op_res_unexp_rec.resource_id = FND_API.G_MISS_NUM)
1768 THEN
1769
1770 Error_Handler.Add_Error_Token
1771 ( p_Message_Name => 'BOM_RES_RESCODE_NULL'
1772 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1773 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1774 , p_Token_Tbl => l_Token_Tbl
1775 );
1776 l_return_status := FND_API.G_RET_STS_ERROR;
1777 END IF;
1778
1779
1780 -- Standard Rate Flag
1781 IF p_rev_op_resource_rec.standard_rate_flag IS NOT NULL AND
1782 p_rev_op_resource_rec.standard_rate_flag NOT IN (1,2)
1783 AND p_rev_op_resource_rec.standard_rate_flag <> FND_API.G_MISS_NUM
1784 THEN
1785 Error_Handler.Add_Error_Token
1786 ( p_Message_Name => 'BOM_RES_STD_RATE_INVALID'
1787 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1788 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1789 , p_Token_Tbl => l_Token_Tbl
1790 );
1794
1791 l_return_status := FND_API.G_RET_STS_ERROR;
1792 END IF;
1793
1795 -- Principle Flag
1796 IF p_rev_op_resource_rec.principle_flag IS NOT NULL AND
1797 p_rev_op_resource_rec.principle_flag NOT IN (1,2)
1798 AND p_rev_op_resource_rec.principle_flag <> FND_API.G_MISS_NUM
1799 THEN
1800 Error_Handler.Add_Error_Token
1801 ( p_Message_Name => 'BOM_RES_PCLFLAG_INVALID'
1802 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1803 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1804 , p_Token_Tbl => l_Token_Tbl
1805 );
1806 l_return_status := FND_API.G_RET_STS_ERROR;
1807 END IF;
1808
1809 -- Resource Offset Percent
1810 IF p_rev_op_resource_rec.resource_offset_percent IS NOT NULL AND
1811 (p_rev_op_resource_rec.resource_offset_percent < 0
1812 OR p_rev_op_resource_rec.resource_offset_percent > 100 )
1813 AND p_rev_op_resource_rec.resource_offset_percent <> FND_API.G_MISS_NUM
1814 THEN
1815 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1816 THEN
1817 Error_Handler.Add_Error_Token
1818 ( p_Message_Name => 'BOM_RES_OFFSET_PCT_INVALID'
1819 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1820 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1821 , p_Token_Tbl => l_Token_Tbl
1822 ) ;
1823 END IF ;
1824 l_return_status := FND_API.G_RET_STS_ERROR;
1825 END IF ;
1826
1827
1828 -- Assigned Units
1829 IF p_rev_op_resource_rec.assigned_units IS NOT NULL AND
1830 p_rev_op_resource_rec.assigned_units <= 0.00001
1831 AND p_rev_op_resource_rec.assigned_units <> FND_API.G_MISS_NUM
1832 THEN
1833 Error_Handler.Add_Error_Token
1834 ( p_Message_Name => 'BOM_RES_ASSIGNED_UNITS_INVALID'
1835 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1836 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1837 , p_Token_Tbl => l_Token_Tbl
1838 );
1839 l_return_status := FND_API.G_RET_STS_ERROR;
1840 END IF;
1841
1842
1843 -- Basis Type
1844 IF p_rev_op_resource_rec.basis_type IS NOT NULL AND
1845 p_rev_op_resource_rec.basis_type NOT IN (1,2)
1846 AND p_rev_op_resource_rec.basis_type <> FND_API.G_MISS_NUM
1847 THEN
1848 Error_Handler.Add_Error_Token
1849 ( p_Message_Name => 'BOM_RES_BASISTYPE_INVALID'
1850 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1851 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1852 , p_Token_Tbl => l_Token_Tbl
1853 );
1854 l_return_status := FND_API.G_RET_STS_ERROR;
1855 END IF;
1856
1857
1858 -- Schedule Flag
1859 IF p_rev_op_resource_rec.schedule_flag IS NOT NULL AND
1860 p_rev_op_resource_rec.schedule_flag NOT IN (1,2,3,4)
1861 AND p_rev_op_resource_rec.schedule_flag <> FND_API.G_MISS_NUM
1862 THEN
1863 Error_Handler.Add_Error_Token
1864 ( p_Message_Name => 'BOM_RES_SCHEDULEFLAG_INVALID'
1865 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1866 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1867 , p_Token_Tbl => l_Token_Tbl
1868 );
1869 l_return_status := FND_API.G_RET_STS_ERROR;
1870 END IF ;
1871
1872
1873 -- Autocharge Type
1874 IF p_rev_op_resource_rec.autocharge_type IS NOT NULL AND
1875 p_rev_op_resource_rec.autocharge_type NOT IN (1,2,3,4)
1876 AND p_rev_op_resource_rec.autocharge_type <> FND_API.G_MISS_NUM
1877 THEN
1878 Error_Handler.Add_Error_Token
1879 ( p_Message_Name => 'BOM_RES_ACHARGE_TYPE_INVALID'
1880 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1881 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1882 , p_Token_Tbl => l_Token_Tbl
1883 );
1884 l_return_status := FND_API.G_RET_STS_ERROR;
1885 END IF ;
1886
1887
1888 -- ACD Type
1889 IF( ( p_rev_op_resource_rec.acd_type IS NOT NULL
1890 AND p_rev_op_resource_rec.acd_type NOT IN
1891 (l_ACD_ADD, l_ACD_CHANGE, l_ACD_DISABLE) )
1892 OR p_rev_op_resource_rec.acd_type IS NULL
1893 )
1894 AND BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
1895 THEN
1896
1897 l_token_tbl(2).token_name := 'ACD_TYPE';
1898
1899 IF p_rev_op_resource_rec.acd_type <> FND_API.G_MISS_NUM
1900 THEN
1901 l_token_tbl(2).token_value := p_rev_op_resource_rec.acd_type;
1902 ELSE
1903 l_token_tbl(2).token_value := '' ;
1904 END IF ;
1905
1906 Error_Handler.Add_Error_Token
1907 ( p_Message_Name => 'BOM_RES_ACD_TYPE_INVALID'
1911 );
1908 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1909 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1910 , p_Token_Tbl => l_Token_Tbl
1912 l_return_status := FND_API.G_RET_STS_ERROR ;
1913 END IF ;
1914
1915
1916
1917 -- Done validating attributes
1918 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1919 ('Operation Resource Attr Validation completed with return_status: ' || l_return_status) ;
1920 END IF;
1921
1922 x_return_status := l_return_status;
1923 x_mesg_token_tbl := l_Mesg_Token_Tbl;
1924
1925 EXCEPTION
1926 WHEN OTHERS THEN
1927 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1928 ('Some unknown error in Attribute Validation . . .' || SQLERRM );
1929 END IF ;
1930
1931
1932 l_err_text := G_PKG_NAME || ' Validation (Attr. Validation) '
1933 || substrb(SQLERRM,1,200);
1934 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
1935
1936 Error_Handler.Add_Error_Token
1937 ( p_message_name => NULL
1938 , p_message_text => l_err_text
1939 , p_mesg_token_tbl => l_mesg_token_tbl
1940 , x_mesg_token_tbl => l_mesg_token_tbl
1941 ) ;
1942
1943 -- Return the status and message table.
1944 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1945 x_mesg_token_tbl := l_mesg_token_tbl ;
1946
1947
1948 END Check_Attributes ;
1949
1950
1951
1952 /*******************************************************************
1953 * Procedure : Check_Entity used by RTG BO
1954 * Parameters IN : Operation Resource exposed column record
1955 * Operation Resource unexposed column record
1956 * Old Operation Resource exposed column record
1957 * Old Operation Resource unexposed column record
1958 * Parameters out: Return Status
1959 * Message Token Table
1960 * Purpose : Convert Routing Op Resource to ECO Op Resource and
1961 * Call Check_Entity for ECO BO.
1962 * Procedure will execute the business logic and will
1963 * also perform any required cross entity validations
1964 *******************************************************************/
1965 PROCEDURE Check_Entity
1966 ( p_op_resource_rec IN Bom_Rtg_Pub.Op_Resource_Rec_Type
1967 , p_op_res_unexp_rec IN Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
1968 , p_old_op_resource_rec IN Bom_Rtg_Pub.Op_Resource_Rec_Type
1969 , p_old_op_res_unexp_rec IN Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
1970 , x_op_resource_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Resource_Rec_Type
1971 , x_op_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
1972 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1973 , x_return_status IN OUT NOCOPY VARCHAR2
1974 )
1975 IS
1976 l_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
1977 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type ;
1978 l_old_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
1979 l_old_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type ;
1980
1981 BEGIN
1982 -- Convert Routing Operation to ECO Operation
1983 Bom_Rtg_Pub.Convert_RtgRes_To_EcoRes
1984 ( p_rtg_op_resource_rec => p_op_resource_rec
1985 , p_rtg_op_res_unexp_rec => p_op_res_unexp_rec
1986 , x_rev_op_resource_rec => l_rev_op_resource_rec
1987 , x_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
1988 ) ;
1989
1990
1991 -- Also Convert Old Routing Operation to Old ECO Operation
1992 Bom_Rtg_Pub.Convert_RtgRes_To_EcoRes
1993 ( p_rtg_op_resource_rec => p_old_op_resource_rec
1994 , p_rtg_op_res_unexp_rec => p_old_op_res_unexp_rec
1995 , x_rev_op_resource_rec => l_old_rev_op_resource_rec
1996 , x_rev_op_res_unexp_rec => l_old_rev_op_res_unexp_rec
1997 ) ;
1998
1999 -- Call Check_Entity
2000 Bom_Validate_Op_Res.Check_Entity
2001 ( p_rev_op_resource_rec => l_rev_op_resource_rec
2002 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2003 , p_old_rev_op_resource_rec => l_old_rev_op_resource_rec
2004 , p_old_rev_op_res_unexp_rec => l_old_rev_op_res_unexp_rec
2005 , p_control_rec => Bom_Rtg_Pub.G_DEFAULT_CONTROL_REC
2006 , x_rev_op_resource_rec => l_rev_op_resource_rec
2007 , x_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2008 , x_return_status => x_return_status
2009 , x_mesg_token_tbl => x_mesg_token_tbl
2010 ) ;
2011
2012
2013 -- Convert Eco Op Resource Record back to Routing Op Resource
2014 Bom_Rtg_Pub.Convert_EcoRes_To_RtgRes
2015 ( p_rev_op_resource_rec => l_rev_op_resource_rec
2016 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2017 , x_rtg_op_resource_rec => x_op_resource_rec
2018 , x_rtg_op_res_unexp_rec => x_op_res_unexp_rec
2019 ) ;
2020
2021
2022 END Check_Entity ;
2023
2024
2025 /*******************************************************************
2029 * Old Revised Op Resource exposed column record
2026 * Procedure : Check_Entity used by RTG BO and internally called by RTG BO
2027 * Parameters IN : Revised Op Resource exposed column record
2028 * Revised Op Resource unexposed column record
2030 * Old Revised Op Resource unexposed column record
2031 * Parameters out: Return Status
2032 * Message Token Table
2033 * Purpose : Check_Entity validate the entity for the correct
2034 * business logic. It will verify the values by running
2035 * checks on inter-dependent columns.
2036 * It will also verify that changes in one column value
2037 * does not invalidate some other columns.
2038 *******************************************************************/
2039 PROCEDURE Check_Entity
2040 ( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
2041 , p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
2042 , p_old_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
2043 , p_old_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
2044 , p_control_rec IN Bom_Rtg_Pub.Control_Rec_Type
2045 , x_rev_op_resource_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
2046 , x_rev_op_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
2047 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2048 , x_return_status IN OUT NOCOPY VARCHAR2
2049 )
2050 IS
2051
2052 -- Variables
2053 l_eco_processed BOOLEAN ; -- Indicate ECO has been processed
2054 l_hour_uom_code VARCHAR2(3) ; -- Hour UOM Code
2055 l_hour_uom_class VARCHAR2(10) ; -- Hour UOM Class
2056 l_res_uom_code VARCHAR2(3) ; -- Resource UOM Code
2057 l_res_uom_class VARCHAR2(10) ; -- Resource UOM Class
2058 l_temp_status VARCHAR2(1) ; -- Temp Error Status
2059 l_res_code BOM_RESOURCES_V.RESOURCE_CODE%TYPE;
2060
2061 l_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
2062 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type ;
2063
2064 -- Error Handlig Variables
2065 l_return_status VARCHAR2(1);
2066 l_err_text VARCHAR2(2000) ;
2067 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
2068 l_token_tbl Error_Handler.Token_Tbl_Type;
2069
2070 l_get_setups NUMBER; --APS Enhancement for Routings
2071 l_batchable NUMBER; --APS Enhancement for Routings
2072 /* Added below two variables for fixing bug 6074930*/
2073 l_res_code_2 VARCHAR2(10);
2074 l_res_id NUMBER;
2075
2076 CURSOR get_setups ( p_resource_id NUMBER --APS Enhancement for Routings
2077 , p_org_id NUMBER
2078 )
2079 IS
2080 SELECT count(setup_id)
2081 FROM bom_resource_setups
2082 WHERE resource_id = p_resource_id
2083 AND organization_id = p_org_id;
2084
2085
2086 -- Check Rev Op Resource exists
2087 CURSOR l_opres_exist_csr ( p_res_seq_num NUMBER
2088 , p_op_seq_id NUMBER
2089 )
2090 IS
2091 SELECT 'Rev Op Resource Not Exists'
2092 FROM DUAL
2093 WHERE NOT EXISTS (SELECT NULL
2094 FROM BOM_OPERATION_SEQUENCES bos
2095 , BOM_OPERATION_RESOURCES bor
2096 WHERE bor.resource_seq_num = p_res_seq_num
2097 AND bor.operation_sequence_id = bos.old_operation_sequence_id
2098 AND bos.operation_sequence_id = p_op_seq_id
2099 ) ;
2100
2101 -- Check if there is an associated sub resource
2102 CURSOR l_subres_exist_csr ( p_op_seq_id IN NUMBER
2103 , p_sub_group_num IN NUMBER
2104 , p_res_seq_num IN NUMBER)
2105 IS
2106 SELECT 'Sub Res Exists'
2107 FROM SYS.DUAL
2108 WHERE EXISTS ( SELECT NULL
2109 FROM BOM_SUB_OPERATION_RESOURCES
2110 WHERE operation_sequence_id = p_op_seq_id
2111 AND substitute_group_num = p_sub_group_num )
2112 AND NOT EXISTS ( SELECT NULL
2113 FROM BOM_OPERATION_RESOURCES
2114 WHERE substitute_group_num = p_sub_group_num
2115 AND resource_seq_num <> p_res_seq_num
2116 AND operation_sequence_id = p_op_seq_id ) ;
2117
2118
2119 CURSOR l_rev_subres_exist_csr ( p_op_seq_id IN NUMBER
2120 , p_sub_group_num IN NUMBER
2121 , p_res_seq_num IN NUMBER)
2122 IS
2123 SELECT 'Sub Res Exists'
2124 FROM SYS.DUAL
2125 WHERE EXISTS ( SELECT NULL
2126 FROM BOM_SUB_OPERATION_RESOURCES bsor
2127 , BOM_OPERATION_SEQUENCES bos
2128 WHERE bsor.substitute_group_num = p_sub_group_num
2129 AND bsor.operation_sequence_id = bos.old_operation_sequence_id
2130 AND bos.operation_sequence_id = p_op_seq_id )
2134 WHERE bor.substitute_group_num = p_sub_group_num
2131 AND NOT EXISTS (SELECT NULL
2132 FROM BOM_OPERATION_RESOURCES bor
2133 , BOM_OPERATION_SEQUENCES bos
2135 AND resource_seq_num <> p_res_seq_num
2136 AND bor.operation_sequence_id = bos.old_operation_sequence_id
2137 AND bos.operation_sequence_id = p_op_seq_id ) ;
2138
2139
2140 -- Check if there is an associated Sub PO Move Resource
2141 -- on this resource
2142 CURSOR l_subres_pomove_csr ( p_op_seq_id IN NUMBER
2143 , p_sub_group_num IN NUMBER )
2144 IS
2145 SELECT 'Sub PO Move Exists'
2146 FROM SYS.DUAL
2147 WHERE EXISTS ( SELECT NULL
2148 FROM BOM_SUB_OPERATION_RESOURCES
2149 WHERE autocharge_type = l_PO_MOVE
2150 AND substitute_group_num = p_sub_group_num
2151 AND operation_sequence_id = p_op_seq_id ) ;
2152
2153 CURSOR l_rev_subres_pomove_csr ( p_op_seq_id IN NUMBER
2154 , p_sub_group_num IN NUMBER )
2155 IS
2156 SELECT 'Sub PO Move Exists'
2157 FROM SYS.DUAL
2158 WHERE EXISTS ( SELECT NULL
2159 FROM BOM_SUB_OPERATION_RESOURCES bsor
2160 , BOM_OPERATION_SEQUENCES bos
2161 WHERE autocharge_type = l_PO_MOVE
2162 AND bsor.substitute_group_num = p_sub_group_num
2163 AND bsor.operation_sequence_id = bos.old_operation_sequence_id
2164 AND bos.operation_sequence_id = p_op_seq_id ) ;
2165
2166
2167 -- Check if there is an associated Sub Next or Prior resource
2168 -- on this resource
2169 CURSOR l_subres_schedule_csr ( p_op_seq_id IN NUMBER
2170 , p_sub_group_num IN NUMBER
2171 , p_schedule_flag IN NUMBER )
2172 IS
2173 SELECT 'Sub PO Move Exists'
2174 FROM SYS.DUAL
2175 WHERE EXISTS ( SELECT NULL
2176 FROM BOM_SUB_OPERATION_RESOURCES
2177 WHERE schedule_flag = p_schedule_flag
2178 AND substitute_group_num = p_sub_group_num
2179 AND operation_sequence_id = p_op_seq_id ) ;
2180
2181
2182 CURSOR l_rev_subres_schedule_csr ( p_op_seq_id IN NUMBER
2183 , p_sub_group_num IN NUMBER
2184 , p_schedule_flag IN NUMBER )
2185 IS
2186 SELECT 'Sub PO Move Exists'
2187 FROM SYS.DUAL
2188 WHERE EXISTS ( SELECT NULL
2189 FROM BOM_SUB_OPERATION_RESOURCES bsor
2190 , BOM_OPERATION_SEQUENCES bos
2191 WHERE bsor.schedule_flag = p_schedule_flag
2192 AND bsor.substitute_group_num = p_sub_group_num
2193 AND bsor.operation_sequence_id = bos.old_operation_sequence_id
2194 AND bos.operation_sequence_id = p_op_seq_id ) ;
2195
2196
2197
2198 BEGIN
2199 --
2200 -- Initialize Common Record and Status
2201 --
2202
2203 l_rev_op_resource_rec := p_rev_op_resource_rec ;
2204 l_rev_op_res_unexp_rec := p_rev_op_res_unexp_rec ;
2205 l_return_status := FND_API.G_RET_STS_SUCCESS ;
2206 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2207
2208 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2209 ('Performing Op Resource Check Entity Validation . . .') ;
2210 END IF ;
2211
2212 --
2213 -- Set the 1st token of Token Table to Revised Operation value
2214 --
2215 l_token_tbl(1).token_name := 'RES_SEQ_NUMBER';
2216 l_token_tbl(1).token_value := l_rev_op_resource_rec.resource_sequence_number ;
2217
2218
2219 -- The ECO can be updated but a warning needs to be generated and
2220 -- scheduled revised items need to be update to Open
2221 -- and the ECO status need to be changed to Not Submitted for Approval
2222
2223 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2224 ('Check if ECO has been approved and has a workflow process. . . ' || l_return_status) ;
2225 END IF ;
2226
2227 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
2228 THEN
2229 BOM_Rtg_Globals.Check_Approved_For_Process
2230 ( p_change_notice => l_rev_op_resource_rec.eco_name
2231 , p_organization_id => l_rev_op_res_unexp_rec.organization_id
2232 , x_processed => l_eco_processed
2233 , x_err_text => l_err_text
2234 ) ;
2235
2236 IF l_eco_processed THEN
2237 -- If the above process returns true then set the ECO approval.
2238 BOM_Rtg_Globals.Set_Request_For_Approval
2239 ( p_change_notice => l_rev_op_resource_rec.eco_name
2240 , p_organization_id => l_rev_op_res_unexp_rec.organization_id
2241 , x_err_text => l_err_text
2242 ) ;
2243
2244 END IF ;
2245 END IF;
2246
2247
2251 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
2248 --
2249 -- Performing Entity Validation in Revised Op Resource(ECO BO)
2250 --
2252 THEN
2253 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2254 ('Performing Entitity Validation for Eco Routing :ACD Type. . .') ;
2255 END IF ;
2256
2257 --
2258 -- ACD Type
2259 -- If the Transaction Type is CREATE and the ACD_Type = Disable, then
2260 -- the operation resource should already exist for the revised operation.
2261 --
2262 /* This validation has been done in Rev_Operation_Resource procedure
2263 IF l_rev_op_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
2264 AND ( NVL(l_rev_op_resource_rec.acd_type,l_ACD_ADD ) IN (l_ACD_CHANGE, l_ACD_DISABLE ))
2265 THEN
2266
2267 FOR l_opres_exist_rec IN l_opres_exist_csr
2268 ( p_res_seq_num => l_rev_op_resource_rec.resource_sequence_number
2269 , p_op_seq_id => l_rev_op_res_unexp_rec.operation_sequence_id
2270 )
2271 LOOP
2272 l_token_tbl(2).token_name := 'OP_SEQ_NUMBER';
2273 l_token_tbl(2).token_value := l_rev_op_resource_rec.operation_sequence_number ;
2274
2275 Error_Handler.Add_Error_Token
2276 ( p_message_name => 'BOM_RES_DISABLE_RES_NOT_FOUND'
2277 , p_mesg_token_tbl => l_Mesg_Token_Tbl
2278 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2279 , p_token_tbl => l_token_tbl
2280 ) ;
2281
2282 l_return_status := FND_API.G_RET_STS_ERROR ;
2283 END LOOP ;
2284 END IF ;
2285 */
2286
2287
2288
2289 --
2290 -- ACD Type,
2291 -- If the Transaction Type is CREATE and the ACD_Type of parent revised
2292 -- operation is Add then,the ACD_Type must be Add.
2293 --
2294 --
2295 IF l_rev_op_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
2296 THEN
2297 IF
2298 l_ACD_ADD =
2299 Get_Rev_Op_ACD(p_op_seq_id
2300 => l_rev_op_res_unexp_rec.operation_sequence_id)
2301 AND NVL(l_rev_op_resource_rec.acd_type,l_ACD_ADD ) <> l_ACD_ADD
2302 THEN
2303 l_token_tbl(2).token_name := 'OP_SEQ_NUMBER';
2304 l_token_tbl(2).token_value := l_rev_op_resource_rec.operation_sequence_number ;
2305
2306 Error_Handler.Add_Error_Token
2307 ( p_message_name => 'BOM_RES_ACD_NOT_COMPATIBLE'
2308 , p_mesg_token_tbl => l_Mesg_Token_Tbl
2309 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2310 , p_token_tbl => l_token_tbl
2311 ) ;
2312
2313 l_return_status := FND_API.G_RET_STS_ERROR ;
2314 END IF ;
2315 END IF ;
2316
2317
2318
2319 --
2320 -- For CREATE, ACD Type is CHANGE, Operation Resource's
2321 -- Attribute can not be update.
2322 --
2323 /* User is allowed to update res attributes
2324 IF l_rev_op_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
2325 AND l_rev_op_resource_rec.acd_type = l_ACD_CHANGE
2326 THEN
2327 IF NOT Check_Res_Attr_changed
2328 ( p_rev_op_resource_rec => l_rev_op_resource_rec
2329 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
2330 , p_old_rev_op_resource_rec => p_old_rev_op_resource_rec
2331 , p_old_rev_op_res_unexp_rec => p_old_rev_op_res_unexp_rec )
2332 THEN
2333 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2334 THEN
2335 Error_Handler.Add_Error_Token
2336 ( p_message_name => 'BOM_RES_NOT_UPDATE_IN_CHANGE'
2337 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2338 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2339 , p_token_tbl => l_token_tbl
2340 ) ;
2341 END IF ;
2342 l_return_status := FND_API.G_RET_STS_ERROR ;
2343 END IF ;
2344 END IF ;
2345 */
2346
2347 --
2348 -- For UPDATE, ACD Type not updateable
2349 --
2350 IF l_rev_op_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_UPDATE
2351 AND l_rev_op_resource_rec.acd_type <> p_old_rev_op_resource_rec.acd_type
2352 THEN
2353 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2354 THEN
2355 Error_Handler.Add_Error_Token
2356 ( p_message_name => 'BOM_RES_ACD_TYPENOT_UPDATEABLE'
2357 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2358 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2359 , p_token_tbl => l_token_tbl
2360 ) ;
2361 END IF ;
2362 l_return_status := FND_API.G_RET_STS_ERROR ;
2363 END IF ;
2364
2365 /* Moved the validation out of the If block as part of UT for R12.
2366 --
2367 -- APS Enhancement for Routings.
2371 OPEN get_setups (p_rev_op_res_unexp_rec.resource_id, p_rev_op_res_unexp_rec.organization_id);
2368 -- Verify that if a resource has setups defined, or is Batchable then
2369 -- the Assigned Units for that Resource have to be 1.
2370 --
2372 FETCH get_setups INTO l_get_setups;
2373 CLOSE get_setups;
2374 SELECT nvl(batchable,2) INTO l_batchable
2375 FROM bom_resources
2376 WHERE resource_id = p_rev_op_res_unexp_rec.resource_id;
2377 IF (l_get_setups IS NOT NULL or l_batchable = 1) THEN
2378 IF p_rev_op_resource_rec.assigned_units <> 1 THEN
2379 Error_Handler.Add_Error_Token
2380 ( p_Message_Name => 'BOM_RES_ASSIGNED_UNIT_INCORRECT'
2381 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2382 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2383 , p_Token_Tbl => l_Token_Tbl
2384 );
2385 END IF;
2386 END IF;
2387 */
2388
2389 --
2390 -- Verify the ECO by WO Effectivity, If ECO by WO, Lot Num, Or Cum Qty, then
2391 -- Check if the operation resource exist in the WO or Lot Num.
2392 --
2393 IF p_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
2394 AND l_rev_op_resource_rec.acd_type IN (l_ACD_CHANGE, l_ACD_DISABLE )
2395 THEN
2396
2397 IF NOT Check_ECO_By_WO_Effectivity
2398 ( p_revised_item_sequence_id => p_rev_op_res_unexp_rec.revised_item_sequence_id
2399 , p_operation_seq_num => p_rev_op_resource_rec.operation_sequence_number
2400 , p_resource_seq_num => p_rev_op_resource_rec.resource_sequence_number
2401 , p_organization_Id => p_rev_op_res_unexp_rec.organization_id
2402 , p_rev_item_id => p_rev_op_res_unexp_rec.revised_item_id
2403 )
2404 THEN
2405 l_token_tbl(2).token_name := 'REVISED_ITEM_NAME';
2406 l_token_tbl(2).token_value := p_rev_op_resource_rec.revised_item_name;
2407
2408 Error_Handler.Add_Error_Token
2409 ( p_message_name => 'BOM_RES_RIT_ECO_WO_EFF_INVALID'
2410 , p_mesg_token_tbl => l_Mesg_Token_Tbl
2411 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2412 , p_token_tbl => l_token_tbl
2413 );
2414 l_return_status := FND_API.G_RET_STS_ERROR;
2415
2416 END IF ;
2417 END IF ;
2418
2419
2420 END IF ; -- ECO BO Validation
2421
2422
2423 -- Modified validation for Assigned Units
2424 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
2425 Error_Handler.Write_Debug ('Validating the Assigned Units for a Batchable Resource . . .') ;
2426 END IF;
2427
2428 IF l_rev_op_resource_rec.transaction_type IN
2429 (BOM_Rtg_Globals.G_OPR_CREATE, BOM_Rtg_Globals.G_OPR_UPDATE)
2430 THEN
2431 --
2432 -- APS Enhancement for Routings.
2433 -- Verify that if a resource has setups defined, or is Batchable then
2434 -- the Assigned Units for that Resource have to be 1.
2435 --
2436 IF p_rev_op_resource_rec.assigned_units <> FND_API.G_MISS_NUM THEN
2437 OPEN get_setups (p_rev_op_res_unexp_rec.resource_id, p_rev_op_res_unexp_rec.organization_id);
2438 FETCH get_setups INTO l_get_setups;
2439 CLOSE get_setups;
2440 SELECT nvl(batchable,2) INTO l_batchable
2441 FROM bom_resources
2442 WHERE resource_id = p_rev_op_res_unexp_rec.resource_id;
2443 IF (l_get_setups > 0 or l_batchable = 1) THEN
2444 IF p_rev_op_resource_rec.assigned_units <> 1 THEN
2445 l_Token_Tbl(2).token_name := 'RES_SEQ_NUMBER';
2446 --l_Token_Tbl(2).token_value := p_rev_op_resource_rec.Resource_Code;
2447 l_Token_Tbl(2).token_value := p_rev_op_resource_rec.Resource_Sequence_Number;
2448 Error_Handler.Add_Error_Token
2449 ( p_Message_Name => 'BOM_RES_ASSIGNED_UNITS_WRONG'
2450 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2451 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2452 , p_Token_Tbl => l_Token_Tbl
2453 );
2454 l_return_status := FND_API.G_RET_STS_ERROR ;
2455 END IF;
2456 END IF;
2457 END IF;
2458 END IF;
2459 -- Modified validation for Assigned Units
2460
2461 --
2462 -- For UPDATE or ( For CREATE and acd type is change)
2463 -- Validation specific to the Transaction Type of Update
2464 --
2465 IF l_rev_op_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_UPDATE
2466 OR
2467 (l_rev_op_resource_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
2468 AND l_rev_op_resource_rec.acd_type = l_ACD_CHANGE
2469 )
2470 THEN
2471
2472
2473
2474 /**** This validation is not required ****
2475 --
2476 -- Scheduled Resource
2477 -- Check if there are associated sub Next or Prior resources on
2478 -- this resource
2479 --
2480 IF ( l_rev_op_resource_rec.schedule_flag <> l_NEXT
2481 OR ( l_rev_op_res_unexp_rec.substitute_group_number
2482 <> p_old_rev_op_res_unexp_rec.substitute_group_number ))
2483 AND p_old_rev_op_resource_rec.schedule_flag = l_NEXT
2484 THEN
2485
2486 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2487 ('Check if you can update Next or Prior Schedule Res to others. . . . ' || l_return_status) ;
2488 END IF ;
2492 , p_sub_group_num => p_old_rev_op_res_unexp_rec.substitute_group_number
2489
2490 FOR l_subres_schedule_rec IN l_subres_schedule_csr
2491 ( p_op_seq_id => p_rev_op_res_unexp_rec.operation_sequence_id
2493 , p_schedule_flag => l_NEXT )
2494 LOOP
2495
2496 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2497 THEN
2498 Error_Handler.Add_Error_Token
2499 ( p_message_name => 'BOM_RES_NEXTPRIOR_NOT_UPDATE'
2500 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2501 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2502 , p_Token_Tbl => l_Token_Tbl
2503 ) ;
2504 END IF ;
2505
2506 l_return_status := FND_API.G_RET_STS_ERROR ;
2507
2508 END LOOP ;
2509
2510 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
2511 AND l_rev_op_resource_rec.acd_type = l_ACD_CHANGE
2512 THEN
2513 FOR l_rev_subres_schedule_rec IN l_rev_subres_schedule_csr
2514 ( p_op_seq_id => p_rev_op_res_unexp_rec.operation_sequence_id
2515 , p_sub_group_num => p_old_rev_op_res_unexp_rec.substitute_group_number
2516 , p_schedule_flag => l_NEXT )
2517 LOOP
2518
2519 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2520 THEN
2521 Error_Handler.Add_Error_Token
2522 ( p_message_name => 'BOM_RES_NEXTPRIOR_NOT_UPDATE'
2523 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2524 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2525 , p_Token_Tbl => l_Token_Tbl
2526 ) ;
2527 END IF ;
2528
2529 l_return_status := FND_API.G_RET_STS_ERROR ;
2530
2531 END LOOP ;
2532 END IF ;
2533
2534 ELSIF ( l_rev_op_resource_rec.schedule_flag <> l_PRIOR
2535 OR ( l_rev_op_res_unexp_rec.substitute_group_number
2536 <> p_old_rev_op_res_unexp_rec.substitute_group_number ))
2537 AND p_old_rev_op_resource_rec.schedule_flag = l_PRIOR
2538 THEN
2539 FOR l_subres_schedule_rec IN l_subres_schedule_csr
2540 ( p_op_seq_id => p_rev_op_res_unexp_rec.operation_sequence_id
2541 , p_sub_group_num => p_old_rev_op_res_unexp_rec.substitute_group_number
2542 , p_schedule_flag => l_PRIOR )
2543 LOOP
2544
2545 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2546 THEN
2547 Error_Handler.Add_Error_Token
2548 ( p_message_name => 'BOM_RES_NEXTPRIOR_NOT_UPDATE'
2549 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2550 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2551 , p_Token_Tbl => l_Token_Tbl
2552 ) ;
2553 END IF ;
2554
2555 l_return_status := FND_API.G_RET_STS_ERROR ;
2556
2557 END LOOP ;
2558
2559 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
2560 AND l_rev_op_resource_rec.acd_type = l_ACD_CHANGE
2561 THEN
2562 FOR l_rev_subres_schedule_rec IN l_rev_subres_schedule_csr
2563 ( p_op_seq_id => p_rev_op_res_unexp_rec.operation_sequence_id
2564 , p_sub_group_num => p_old_rev_op_res_unexp_rec.substitute_group_number
2565 , p_schedule_flag => l_PRIOR)
2566 LOOP
2567
2568 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2569 THEN
2570 Error_Handler.Add_Error_Token
2571 ( p_message_name => 'BOM_RES_NEXTPRIOR_NOT_UPDATE'
2572 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2573 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2574 , p_Token_Tbl => l_Token_Tbl
2575 ) ;
2576 END IF ;
2577
2578 l_return_status := FND_API.G_RET_STS_ERROR ;
2579
2580 END LOOP ;
2581 END IF ;
2582
2583 END IF ;
2584 **** This validation is not required ****/
2585
2586
2587 /**** This validation is not required ****
2588 --
2589 -- Autocharge Type
2590 -- If you update Autocharge Type : PO Move to the orhters
2591 -- there must be no associated sub PO Move resource
2592 --
2593 IF ( l_rev_op_resource_rec.autocharge_type <> l_PO_MOVE
2594 OR ( l_rev_op_res_unexp_rec.substitute_group_number
2595 <> p_old_rev_op_res_unexp_rec.substitute_group_number ))
2596 AND p_old_rev_op_resource_rec.autocharge_type = l_PO_MOVE
2597 THEN
2598
2599 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2600 ('Check if you can update PO Move to others. . . . ' || l_return_status) ;
2604 ( p_op_seq_id => p_rev_op_res_unexp_rec.operation_sequence_id
2601 END IF ;
2602
2603 FOR l_subres_pomove_rec IN l_subres_pomove_csr
2605 , p_sub_group_num => p_old_rev_op_res_unexp_rec.substitute_group_number
2606 )
2607 LOOP
2608
2609 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2610 THEN
2611 Error_Handler.Add_Error_Token
2612 ( p_message_name => 'BOM_RES_POMOVE_NOT_UPDATE'
2613 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2614 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2615 , p_Token_Tbl => l_Token_Tbl
2616 ) ;
2617 END IF ;
2618
2619 l_return_status := FND_API.G_RET_STS_ERROR ;
2620
2621 END LOOP ;
2622
2623 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
2624 AND l_rev_op_resource_rec.acd_type = l_ACD_CHANGE
2625 THEN
2626
2627 FOR l_rev_subres_pomove_rec IN l_rev_subres_pomove_csr
2628 ( p_op_seq_id => p_rev_op_res_unexp_rec.operation_sequence_id
2629 , p_sub_group_num => p_old_rev_op_res_unexp_rec.substitute_group_number
2630 )
2631 LOOP
2632
2633 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2634 THEN
2635 Error_Handler.Add_Error_Token
2636 ( p_message_name => 'BOM_RES_POMOVE_NOT_UPDATE'
2637 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2638 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2639 , p_Token_Tbl => l_Token_Tbl
2640 ) ;
2641 END IF ;
2642 l_return_status := FND_API.G_RET_STS_ERROR ;
2643 END LOOP ;
2644 END IF ;
2645 END IF ;
2646 **** This validation is not required **** */
2647
2648
2649 --
2650 -- Schedule Sequence Number and Sub Group Num
2651 -- Check if there are associated sub resources to OLD
2652 -- Substitute Group Number
2653 --
2654 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2655 ('Check if you can change Schedule Seq Num. . . . ' || l_return_status) ;
2656 END IF ;
2657
2658 IF ( nvl(l_rev_op_resource_rec.substitute_group_number, l_rev_op_res_unexp_rec.substitute_group_number)
2659 <> nvl(p_old_rev_op_resource_rec.substitute_group_number, p_old_rev_op_res_unexp_rec.substitute_group_number) )
2660 THEN
2661
2662 FOR l_subres_exist_rec IN l_subres_exist_csr
2663 ( p_op_seq_id => l_rev_op_res_unexp_rec.operation_sequence_id
2664 , p_sub_group_num => nvl(p_old_rev_op_resource_rec.substitute_group_number, p_old_rev_op_res_unexp_rec.substitute_group_number)
2665 , p_res_seq_num => l_rev_op_resource_rec.resource_sequence_number
2666 )
2667 LOOP
2668
2669 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2670 THEN
2671 Error_Handler.Add_Error_Token
2672 ( p_message_name => 'BOM_RES_SUBRES_EXIST'
2673 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2674 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2675 , p_Token_Tbl => l_Token_Tbl
2676 ) ;
2677 END IF ;
2678
2679 l_return_status := FND_API.G_RET_STS_ERROR ;
2680
2681 END LOOP ;
2682
2683 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
2684 AND l_rev_op_resource_rec.acd_type = l_ACD_CHANGE
2685 THEN
2686 FOR l_rev_subres_exist_rec IN l_rev_subres_exist_csr
2687 ( p_op_seq_id => l_rev_op_res_unexp_rec.operation_sequence_id
2688 , p_sub_group_num => nvl(p_old_rev_op_resource_rec.substitute_group_number, p_old_rev_op_res_unexp_rec.substitute_group_number)
2689 , p_res_seq_num => l_rev_op_resource_rec.resource_sequence_number
2690 )
2691 LOOP
2692
2693 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2694 THEN
2695 Error_Handler.Add_Error_Token
2696 ( p_message_name => 'BOM_RES_SUBRES_EXIST'
2697 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2698 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2699 , p_Token_Tbl => l_Token_Tbl
2700 ) ;
2701 END IF ;
2702
2703 l_return_status := FND_API.G_RET_STS_ERROR ;
2704
2705 END LOOP ;
2706 END IF ;
2707
2708 END IF ;
2709
2710 END IF ; -- Transation: UPDATE
2711
2712
2713 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2714 ('End of Validation specific to the Transaction Type of Update : ' || l_return_status) ;
2715 END IF ;
2716
2717 --
2721 (BOM_Rtg_Globals.G_OPR_CREATE, BOM_Rtg_Globals.G_OPR_UPDATE)
2718 -- Validateion for Transaction Type : Create and Update
2719 --
2720 IF l_rev_op_resource_rec.transaction_type IN
2722 THEN
2723
2724 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2725 ('Common Validateion for Transaction Type : Create and Update . . . . ' || l_return_status) ;
2726 END IF ;
2727
2728 --
2729 -- Resource Id
2730 -- Check if valid resource id exists and belongs to depatment
2731 --
2732 IF ( ( NVL(l_rev_op_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
2733 AND l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
2734 OR l_rev_op_res_unexp_rec.resource_id <> p_old_rev_op_res_unexp_rec.resource_id
2735 )
2736 THEN
2737
2738 /* Call Val_Resource_Id */
2739 Val_Resource_Id ( p_resource_id => l_rev_op_res_unexp_rec.resource_id
2740 , p_op_seq_id => l_rev_op_res_unexp_rec.operation_sequence_id
2741 , x_return_status => l_temp_status
2742 ) ;
2743
2744
2745 IF l_temp_status = FND_API.G_RET_STS_ERROR
2746 THEN
2747 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2748 THEN
2749 Error_Handler.Add_Error_Token
2750 ( p_message_name => 'BOM_RES_RESID_INVALID'
2751 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2752 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2753 , p_Token_Tbl => l_Token_Tbl
2754 ) ;
2755 END IF ;
2756
2757 l_return_status := FND_API.G_RET_STS_ERROR ;
2758
2759 END IF ;
2760
2761 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2762 ('Check if Resource is valid. . . . ' || l_return_status) ;
2763 END IF ;
2764
2765 END IF ;
2766
2767
2768 --
2769 -- Activity Id
2770 -- Check if Activity is enabled
2771 --
2772 IF ( ( NVL(l_rev_op_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
2773 AND l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
2774 OR (NVL(l_rev_op_res_unexp_rec.activity_id, FND_API.G_MISS_NUM)
2775 <> NVL(p_old_rev_op_res_unexp_rec.activity_id, FND_API.G_MISS_NUM))
2776 )
2777 AND ( l_rev_op_res_unexp_rec.activity_id IS NOT NULL AND
2778 l_rev_op_res_unexp_rec.activity_id <> FND_API.G_MISS_NUM )
2779 THEN
2780
2781 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2782 ('Activity_Id : ' || to_char(l_rev_op_res_unexp_rec.activity_id)) ;
2783 END IF ;
2784
2785 /* Call Val_Activity_Id */
2786 Val_Activity_Id ( p_activity_id => l_rev_op_res_unexp_rec.activity_id
2787 , p_op_seq_id => l_rev_op_res_unexp_rec.operation_sequence_id
2788 , x_return_status => l_temp_status
2789 ) ;
2790
2791
2792 IF l_temp_status = FND_API.G_RET_STS_ERROR
2793 THEN
2794 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2795 THEN
2796 Error_Handler.Add_Error_Token
2797 ( p_message_name => 'BOM_RES_ACTID_INVALID'
2798 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2799 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2800 , p_Token_Tbl => l_Token_Tbl
2801 ) ;
2802 END IF ;
2803
2804 l_return_status := FND_API.G_RET_STS_ERROR ;
2805
2806 END IF ;
2807
2808 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2809 ('Check if Activity is valid. . . . ' || l_return_status) ;
2810 END IF ;
2811
2812 END IF ;
2813
2814
2815
2816 --
2817 -- Setup Id
2818 -- Check if Setup Id is valid on this operation resource
2819 --
2820 IF ( ( NVL(l_rev_op_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
2821 AND l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
2822 OR (NVL(l_rev_op_res_unexp_rec.setup_id , FND_API.G_MISS_NUM)
2823 <> NVL(p_old_rev_op_res_unexp_rec.setup_id, FND_API.G_MISS_NUM))
2824 )
2825 AND ( l_rev_op_res_unexp_rec.setup_id IS NOT NULL AND
2826 l_rev_op_res_unexp_rec.setup_id <> FND_API.G_MISS_NUM )
2827 THEN
2828
2829 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2830 ('Setup Id : ' || to_char(l_rev_op_res_unexp_rec.setup_id)) ;
2831 END IF ;
2832
2833 /* Call Val_Activity_Id */
2834 Val_Setup_Id ( p_setup_id => l_rev_op_res_unexp_rec.setup_id
2835 , p_resource_id => l_rev_op_res_unexp_rec.resource_id
2836 , p_organization_id => l_rev_op_res_unexp_rec.organization_id
2837 , x_return_status => l_temp_status
2838 ) ;
2839
2843 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2840
2841 IF l_temp_status = FND_API.G_RET_STS_ERROR
2842 THEN
2844 THEN
2845
2846 l_token_tbl(2).token_name := 'SETUP_CODE';
2847 l_token_tbl(2).token_value :=
2848 l_rev_op_resource_rec.setup_type ;
2849 l_token_tbl(3).token_name := 'RESOURCE_CODE';
2850 l_token_tbl(3).token_value :=
2851 l_rev_op_resource_rec.resource_code ;
2852
2853 Error_Handler.Add_Error_Token
2854 ( p_message_name => 'BOM_RES_SETUP_ID_INVALID'
2855 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2856 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2857 , p_Token_Tbl => l_Token_Tbl
2858 ) ;
2859 END IF ;
2860
2861 l_token_tbl.delete(2) ;
2862 l_token_tbl.delete(3) ;
2863
2864 l_return_status := FND_API.G_RET_STS_ERROR ;
2865
2866 END IF ;
2867
2868 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2869 ('Check if Setup is valid. . . . ' || l_return_status) ;
2870 END IF ;
2871
2872 END IF ;
2873
2874
2875
2876 --
2877 -- Schedule Flag
2878 -- Schedule Flag must be 2:No in following case
2879 -- 1. Resource UOM <> Hour UOM code(if they're the same, class would be
2880 -- same
2881 -- 2. Resource UOM class <> Hour UOM class
2882 -- 3. No conversion between resource UOM and Hour UOM
2883 --
2884
2885 IF p_rev_op_resource_rec.schedule_flag <> l_NO_SCHEDULE -- 2: No
2886 THEN
2887
2888 IF ( l_hour_uom_code IS NULL OR
2889 l_hour_uom_class IS NULL OR
2890 l_res_uom_code IS NULL OR
2891 l_res_uom_class IS NULL
2892 )
2893 THEN
2894 Get_Resource_Uom ( p_resource_id
2895 => l_rev_op_res_unexp_rec.resource_id
2896 , x_hour_uom_code => l_hour_uom_code
2897 , x_hour_uom_class => l_hour_uom_class
2898 , x_res_uom_code => l_res_uom_code
2899 , x_res_uom_class => l_res_uom_class ) ;
2900 END IF ;
2901
2902 /* Call Val_Res_UOM_For_Schedule */
2903 Val_Res_UOM_For_Schedule
2904 ( p_hour_uom_class => l_hour_uom_class
2905 , p_res_uom_class => l_res_uom_class
2906 , p_hour_uom_code => l_hour_uom_code
2907 , p_res_uom_code => l_res_uom_code
2908 , x_return_status => l_temp_status
2909 ) ;
2910
2911 IF l_temp_status = FND_API.G_RET_STS_ERROR
2912 THEN
2913 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2914 THEN
2915 Error_Handler.Add_Error_Token
2916 ( p_message_name => 'BOM_RES_SCHEDULE_MUSTBE_NO'
2917 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2918 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2919 , p_Token_Tbl => l_Token_Tbl
2920 ) ;
2921 END IF ;
2922
2923 l_return_status := FND_API.G_RET_STS_ERROR ;
2924
2925 END IF ;
2926
2927 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2928 ('Check if Schedule flag is valid. . . . ' || l_return_status) ;
2929 END IF ;
2930
2931 END IF ;
2932
2933 --
2934 -- Scheduled Resource
2935 -- Cannot have more than one next or prior sheduled resource for
2936 -- an operation
2937 --
2938
2939 IF ( ( NVL(l_rev_op_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
2940 AND l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
2941 OR l_rev_op_resource_rec.schedule_flag <> p_old_rev_op_resource_rec.schedule_flag
2942 )
2943 THEN
2944 /* Call Val_Scheduled_Resource */
2945 Val_Scheduled_Resource
2946 ( p_op_seq_id => l_rev_op_res_unexp_rec.operation_sequence_id
2947 , p_res_seq_num => l_rev_op_resource_rec.resource_sequence_number
2948 , p_sch_seq_num => l_rev_op_resource_rec.schedule_sequence_number
2949 , p_schedule_flag => l_rev_op_resource_rec.schedule_flag
2950 , x_return_status => l_temp_status
2951 ) ;
2952
2953 IF l_temp_status = FND_API.G_RET_STS_ERROR
2954 AND FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2955 THEN
2956 IF p_rev_op_resource_rec.schedule_flag = l_YES_SCHEDULE -- 1: Yes
2957 THEN
2958 Error_Handler.Add_Error_Token
2959 ( p_message_name => 'BOM_RES_YES_INVALID'
2960 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2964 ELSIF p_rev_op_resource_rec.schedule_flag = l_PRIOR -- 3: Prior
2961 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2962 , p_Token_Tbl => l_Token_Tbl
2963 ) ;
2965 THEN
2966 Error_Handler.Add_Error_Token
2967 ( p_message_name => 'BOM_RES_PRIOR_INVALID'
2968 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2969 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2970 , p_Token_Tbl => l_Token_Tbl
2971 ) ;
2972 ELSIF p_rev_op_resource_rec.schedule_flag = l_NEXT -- 4: Next
2973 THEN
2974 Error_Handler.Add_Error_Token
2975 ( p_message_name => 'BOM_RES_NEXT_INVALID'
2976 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2977 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2978 , p_Token_Tbl => l_Token_Tbl
2979 ) ;
2980 END IF ;
2981 l_return_status := FND_API.G_RET_STS_ERROR ;
2982 END IF ; -- If Error
2983
2984 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2985 ('Check next or prior scheduled resource. . . . ' || l_return_status) ;
2986 END IF ;
2987
2988 END IF ;
2989
2990 --
2991 -- Autocharge Type
2992 -- Autocharge type cannot be PO Recedipt if the
2993 -- department has no location.
2994 --
2995 IF ( ( NVL(l_rev_op_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
2996 AND l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
2997 OR l_rev_op_resource_rec.autocharge_type <> p_old_rev_op_resource_rec.autocharge_type
2998 )
2999 AND l_rev_op_resource_rec.autocharge_type = l_PO_RECEIPT
3000 THEN
3001
3002 -- Call Val_Dept_Has_Location
3003 Val_Dept_Has_Location
3004 ( p_op_seq_id => l_rev_op_res_unexp_rec.operation_sequence_id
3005 , x_return_status => l_temp_status
3006 ) ;
3007
3008 IF l_temp_status = FND_API.G_RET_STS_ERROR
3009 THEN
3010 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3011 THEN
3012 Error_Handler.Add_Error_Token
3013 ( p_message_name => 'BOM_RES_PO_ATHARGE_LOC_INVALID'
3014 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3015 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3016 , p_Token_Tbl => l_Token_Tbl
3017 ) ;
3018 END IF ;
3019
3020 l_return_status := FND_API.G_RET_STS_ERROR ;
3021
3022 END IF ;
3023
3024 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3025 ('Check if Autocharge type is valid. . . . ' || l_return_status) ;
3026 END IF ;
3027
3028 END IF ;
3029
3030
3031 --
3032 -- Autocharge Type
3033 -- Autocharge Type cannot be PO Move or PO Receipt if the resource
3034 -- is non-OSP resource
3035 --
3036 IF ( ( NVL(l_rev_op_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3037 AND l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
3038 OR l_rev_op_resource_rec.autocharge_type <> p_old_rev_op_resource_rec.autocharge_type
3039 )
3040 AND l_rev_op_resource_rec.autocharge_type IN (l_PO_RECEIPT, l_PO_MOVE )
3041 THEN
3042
3043 /* Call Val_Autocharge_for_OSP_Res */
3044 Val_Autocharge_for_OSP_Res
3045 ( p_resource_id => l_rev_op_res_unexp_rec.resource_id
3046 , p_organization_id => l_rev_op_res_unexp_rec.organization_id
3047 , x_return_status => l_temp_status
3048 ) ;
3049
3050
3051 IF l_temp_status = FND_API.G_RET_STS_ERROR
3052 THEN
3053 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3054 THEN
3055 Error_Handler.Add_Error_Token
3056 ( p_message_name => 'BOM_RES_ATCHRG_CSTCODE_INVALID'
3057 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3058 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3059 , p_Token_Tbl => l_Token_Tbl
3060 ) ;
3061 END IF ;
3062
3063 l_return_status := FND_API.G_RET_STS_ERROR ;
3064
3065 END IF ;
3066
3067 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3068 ('Check if resource is OSP resource when autocharge is PO Move or PO Receipt. . . . ' || l_return_status) ;
3069 END IF ;
3070
3071 END IF ;
3072
3073 --
3074 -- Autocharge Type
3075 -- Cannot have more than one PO Move per an operation
3076 --
3077 IF ( ( NVL(l_rev_op_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3078 AND l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
3082 THEN
3079 OR l_rev_op_resource_rec.autocharge_type <> p_old_rev_op_resource_rec.autocharge_type
3080 )
3081 AND l_rev_op_resource_rec.autocharge_type = l_PO_MOVE
3083
3084 /* Call Val_PO_Move */
3085 Val_PO_Move
3086 ( p_op_seq_id => l_rev_op_res_unexp_rec.operation_sequence_id
3087 , p_res_seq_num => l_rev_op_resource_rec.resource_sequence_number
3088 , x_return_status => l_temp_status
3089 ) ;
3090
3091 IF l_temp_status = FND_API.G_RET_STS_ERROR
3092 THEN
3093
3094 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3095 THEN
3096 Error_Handler.Add_Error_Token
3097 ( p_message_name => 'BOM_RES_POMOVE_INVALID'
3098 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3099 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3100 , p_Token_Tbl => l_Token_Tbl
3101 ) ;
3102 END IF ;
3103
3104 l_return_status := FND_API.G_RET_STS_ERROR ;
3105 END IF ;
3106
3107
3108 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3109 ('Check if parent operation can have the Po Move resource . . . . ' || l_return_status) ;
3110 END IF ;
3111
3112 END IF ;
3113
3114
3115 --
3116 -- Usage Rate or Amount
3117 -- Check round values for Usage Rate or Amount and the Inverse.
3118 --
3119 IF ( ( NVL(l_rev_op_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3120 AND l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
3121 OR l_rev_op_resource_rec.usage_rate_or_amount
3122 <> p_old_rev_op_resource_rec.usage_rate_or_amount
3123 OR l_rev_op_resource_rec.usage_rate_or_amount_inverse
3124 <> p_old_rev_op_resource_rec.usage_rate_or_amount_inverse
3125 )
3126 THEN
3127
3128 /* Call Val_Usage_Rate_or_Amount */
3129 Val_Usage_Rate_or_Amount
3130 ( p_usage_rate_or_amount => l_rev_op_resource_rec.usage_rate_or_amount
3131 , p_usage_rate_or_amount_inverse => l_rev_op_resource_rec.usage_rate_or_amount_inverse
3132 , x_return_status => l_temp_status
3133 ) ;
3134
3135
3136 IF l_temp_status = FND_API.G_RET_STS_ERROR
3137 THEN
3138 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3139 THEN
3140 Error_Handler.Add_Error_Token
3141 ( p_message_name => 'BOM_RES_RATEORAMOUNT_INVALID'
3142 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3143 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3144 , p_Token_Tbl => l_Token_Tbl
3145 ) ;
3146 END IF ;
3147
3148 l_return_status := FND_API.G_RET_STS_ERROR ;
3149
3150 END IF ;
3151
3152 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3153 ('Check round value for Usage Rate or Amount and the Inverse . . . ' || l_return_status) ;
3154 END IF ;
3155
3156 END IF ;
3157
3158
3159 --
3160 -- Usage Rate or Amount
3161 -- Cannot have negative usage rate or amount in following case
3162 -- 1. Autocharge Type = 3: PO Receipt or 4: PO Move
3163 -- 2. Schedul Flag <> 2
3164 -- comment out : 3. Resource UOM Class = Hour UOM Class
3165 -- Form is allowed case 3.
3166 --
3167 IF ( ( NVL(l_rev_op_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3168 AND l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
3169 OR l_rev_op_resource_rec.usage_rate_or_amount
3170 <> p_old_rev_op_resource_rec.usage_rate_or_amount
3171 OR l_rev_op_resource_rec.usage_rate_or_amount_inverse
3172 <> p_old_rev_op_resource_rec.usage_rate_or_amount_inverse
3173 OR l_rev_op_resource_rec.schedule_flag
3174 <> p_old_rev_op_resource_rec.schedule_flag
3175 OR l_rev_op_resource_rec.autocharge_type
3176 <> p_old_rev_op_resource_rec.autocharge_type
3177 )
3178 AND l_rev_op_resource_rec.usage_rate_or_amount < 0
3179 THEN
3180 IF ( l_hour_uom_code IS NULL OR
3181 l_hour_uom_class IS NULL OR
3182 l_res_uom_code IS NULL OR
3183 l_res_uom_class IS NULL
3184 )
3185 THEN
3186 Get_Resource_Uom ( p_resource_id
3187 => l_rev_op_res_unexp_rec.resource_id
3188 , x_hour_uom_code => l_hour_uom_code
3189 , x_hour_uom_class => l_hour_uom_class
3190 , x_res_uom_code => l_res_uom_code
3191 , x_res_uom_class => l_res_uom_class ) ;
3192 END IF ;
3193
3194
3195 /* Call Val_Negative_Usage_Rate */
3199 , p_hour_uom_class => l_hour_uom_class
3196 Val_Negative_Usage_Rate
3197 ( p_autocharge_type => l_rev_op_resource_rec.autocharge_type
3198 , p_schedule_flag => l_rev_op_resource_rec.schedule_flag
3200 , p_res_uom_class => l_res_uom_class
3201 , x_return_status => l_temp_status
3202 ) ;
3203
3204
3205 IF l_temp_status = FND_API.G_RET_STS_ERROR
3206 THEN
3207 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3208 THEN
3209 Error_Handler.Add_Error_Token
3210 ( p_message_name => 'BOM_RES_NEGATIVE_USAGE_INVALID'
3211 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3212 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3213 , p_Token_Tbl => l_Token_Tbl
3214 ) ;
3215 END IF ;
3216
3217 l_return_status := FND_API.G_RET_STS_ERROR ;
3218
3219 END IF ;
3220
3221 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3222 ('Check negative usage rate. . . . ' || l_return_status) ;
3223 END IF ;
3224
3225 END IF ;
3226
3227
3228
3229 --
3230 -- Principal Flag
3231 -- Cannot have one more principal resource in a group of simulatenous
3232 -- resources
3233 --
3234 /* Comment out by MK. This validation is not required.
3235 */ -- Comment Out validation for priciple flag
3236 /* Uncommented by deepu. Validation for Principal flag is required for patchset I Bug 2689249 */
3237
3238 IF ( ( NVL(l_rev_op_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3239 AND l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
3240 OR l_rev_op_resource_rec.principle_flag
3241 <> p_old_rev_op_resource_rec.principle_flag
3242 OR nvl(l_rev_op_resource_rec.substitute_group_number, l_rev_op_res_unexp_rec.substitute_group_number)
3243 <> nvl(p_old_rev_op_resource_rec.substitute_group_number, p_old_rev_op_res_unexp_rec.substitute_group_number)
3244 )
3245 AND l_rev_op_resource_rec.principle_flag = 1 -- Yes
3246 THEN
3247 -- Call Val_Principal_Res_Unique
3248 Val_Principal_Res_Unique
3249 ( p_op_seq_id => l_rev_op_res_unexp_rec.operation_sequence_id
3250 , p_res_seq_num => l_rev_op_resource_rec.resource_sequence_number
3251 , p_sub_group_num => nvl(l_rev_op_resource_rec.substitute_group_number, l_rev_op_res_unexp_rec.substitute_group_number)
3252 , x_return_status => l_temp_status
3253 ) ;
3254
3255
3256 IF l_temp_status = FND_API.G_RET_STS_ERROR
3257 THEN
3258 --dbms_output.put_line('found error in principal flag for resources');
3259 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3260 THEN
3261 Error_Handler.Add_Error_Token
3262 ( p_message_name => 'BOM_RES_PCFLAG_DUPLICATE'
3263 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3264 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3265 , p_Token_Tbl => l_Token_Tbl
3266 ) ;
3267 END IF ;
3268
3269 l_return_status := FND_API.G_RET_STS_ERROR ;
3270
3271 END IF ;
3272
3273 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3274 ('Check principal flag . . . . ' || l_return_status) ;
3275 END IF ;
3276
3277 END IF ;
3278
3279 --
3280 -- Validate SSN
3281 --
3282 IF ( ( NVL(l_rev_op_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3283 AND l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
3284 OR l_rev_op_resource_rec.schedule_sequence_number <> p_old_rev_op_resource_rec.schedule_sequence_number
3285 OR l_rev_op_resource_rec.substitute_group_number <> p_old_rev_op_resource_rec.substitute_group_number)
3286 THEN
3287 -- Call Val_schedule_seq_num
3288 /* Fix for bug 4506885 - Added parameter p_sub_grp_num to Val_Schedule_Seq_Num procedure call.*/
3289 Val_Schedule_Seq_Num
3290 ( p_op_seq_id => l_rev_op_res_unexp_rec.operation_sequence_id
3291 , p_res_seq_num => l_rev_op_resource_rec.resource_sequence_number
3292 , p_sch_seq_num => l_rev_op_resource_rec.schedule_sequence_number
3293 , p_sub_grp_num => l_rev_op_resource_rec.substitute_group_number
3294 , x_return_status => l_temp_status
3295 );
3296
3297 IF l_temp_status = FND_API.G_RET_STS_ERROR
3298 THEN
3299 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3300 THEN
3301 /* Fix for bug 4506885 - Changed the error msg shown to 'BOM_SAME_SUB_GRP_NUM' from 'BOM_LARGE_SGN_SSN'.
3302 Also set the appropriate token to be shown in the error. */
3303
3304 l_Token_Tbl(1).Token_Name := 'VALUE';
3305 l_Token_Tbl(1).Token_Value := l_rev_op_resource_rec.schedule_sequence_number;
3306
3307 Error_Handler.Add_Error_Token
3308 ( p_message_name => 'BOM_SAME_SUB_GRP_NUM'
3312 );
3309 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3310 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3311 , p_Token_Tbl => l_Token_Tbl
3313 END IF;
3314
3315 l_return_status := FND_API.G_RET_STS_ERROR ;
3316 END IF;
3317 END IF;
3318
3319 /* bug:4638695 For an operation, do not allow same resource to be added more than once with same SSN */
3320
3321 IF (
3322 ( NVL(l_rev_op_resource_rec.acd_type, l_ACD_ADD) = l_ACD_ADD
3323 AND l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
3324 )
3325 OR l_rev_op_resource_rec.schedule_sequence_number <> p_old_rev_op_resource_rec.schedule_sequence_number
3326 OR
3327 ( p_old_rev_op_resource_rec.schedule_sequence_number IS NULL
3328 AND l_rev_op_resource_rec.schedule_sequence_number IS NOT NULL
3329 )
3330 OR l_rev_op_res_unexp_rec.resource_id <> p_old_rev_op_res_unexp_rec.resource_id
3331 )
3332 THEN
3333 Val_Resource_SSN
3334 ( p_op_seq_id => l_rev_op_res_unexp_rec.operation_sequence_id
3335 , p_res_seq_num => l_rev_op_resource_rec.resource_sequence_number
3336 , p_sch_seq_num => l_rev_op_resource_rec.schedule_sequence_number
3337 , p_resource_id => l_rev_op_res_unexp_rec.resource_id
3338 , x_return_status => l_temp_status
3339 );
3340
3341 IF l_temp_status = FND_API.G_RET_STS_ERROR THEN
3342 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3343 IF ( l_rev_op_resource_rec.resource_code IS NOT NULL )
3344 THEN
3345 l_res_code := l_rev_op_resource_rec.resource_code;
3346 ELSE
3347 SELECT RESOURCE_CODE
3348 INTO l_res_code
3349 FROM BOM_RESOURCES_V
3350 WHERE RESOURCE_ID = l_rev_op_res_unexp_rec.resource_id;
3351 END IF;
3352
3353 l_Token_Tbl(1).Token_Name := 'RESOURCE_CODE';
3354 l_Token_Tbl(1).Token_Value:= l_res_code;
3355 l_Token_Tbl(2).Token_Name := 'SCH_SEQ_NUM';
3356 l_Token_Tbl(2).Token_Value := l_rev_op_resource_rec.schedule_sequence_number;
3357 l_Token_Tbl(3).Token_Name := 'OP_SEQ_NUM';
3358 l_Token_Tbl(3).Token_Value := l_rev_op_resource_rec.operation_sequence_number;
3359
3360 Error_Handler.Add_Error_Token
3361 ( p_message_name => 'BOM_RES_SSN_ALREADY_EXISTS'
3362 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3363 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3364 , p_Token_Tbl => l_Token_Tbl
3365 );
3366 END IF; /* end of check_msg_level */
3367 l_return_status := FND_API.G_RET_STS_ERROR ;
3368 END IF; /* end of l_temp_status */
3369
3370 END IF; /* end of validation on resource and ssn*/
3371
3372 /*Fix for bug 6074930- Scheduled simultaneous resources must have the same scheduling flag.
3373 Added below code to do this validation. Resources with scheduling flag 'NO' are exempt
3374 for this validation. Call Val_Schedule_Flag procedure both while creating/updating a resource.*/
3375
3376 IF ( l_rev_op_resource_rec.schedule_flag <> l_NO_SCHEDULE)
3377 AND
3378 ( ( NVL(l_rev_op_resource_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3379 AND l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE )
3380 OR l_rev_op_resource_rec.schedule_sequence_number <> p_old_rev_op_resource_rec.schedule_sequence_number
3381 OR (p_old_rev_op_resource_rec.schedule_sequence_number is null
3382 and l_rev_op_resource_rec.schedule_sequence_number is not null)
3383 OR (p_old_rev_op_resource_rec.schedule_sequence_number is not null
3384 and l_rev_op_resource_rec.schedule_sequence_number is null)
3385 OR ( l_rev_op_resource_rec.schedule_flag <> p_old_rev_op_resource_rec.schedule_flag)
3386 )
3387 THEN
3388 l_res_id := FND_API.G_MISS_NUM;
3389
3390 Val_Schedule_Flag
3391 ( p_op_seq_id => l_rev_op_res_unexp_rec.operation_sequence_id
3392 , p_res_seq_num => l_rev_op_resource_rec.resource_sequence_number
3393 , p_sch_seq_num => l_rev_op_resource_rec.schedule_sequence_number
3394 , p_sch_flag => l_rev_op_resource_rec.schedule_flag
3395 , p_ret_res_id => l_res_id
3396 , x_return_status => l_temp_status
3397 );
3398
3399 IF l_temp_status = FND_API.G_RET_STS_ERROR THEN
3400 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3401 If (l_rev_op_resource_rec.resource_code is not null) Then
3402 l_res_code := l_rev_op_resource_rec.resource_code;
3403 Else
3404 Select resource_code into l_res_code
3408
3405 from bom_resources_v
3406 where resource_id=l_rev_op_res_unexp_rec.resource_id;
3407 End If;
3409 Select resource_code into l_res_code_2
3410 from bom_resources_v
3411 where resource_id=l_res_id;
3412
3413 l_Token_Tbl(1).Token_Name := 'RES_SEQ_1';
3414 l_Token_Tbl(1).Token_Value:= l_res_code;
3415 l_Token_Tbl(2).Token_Name := 'RES_SEQ_2';
3416 l_Token_Tbl(2).Token_Value:= l_res_code_2;
3417 l_Token_Tbl(3).Token_Name := 'OP_SEQ';
3418 l_Token_Tbl(3).Token_Value := l_rev_op_resource_rec.operation_sequence_number;
3419
3420 Error_Handler.Add_Error_Token
3421 ( p_message_name => 'BOM_SIM_RES_SAME_PRIOR_NEXT'
3422 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3423 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3424 , p_Token_Tbl => l_Token_Tbl
3425 );
3426 END IF; /* end of check_msg_level */
3427 l_return_status := FND_API.G_RET_STS_ERROR ;
3428 END IF; /* end of l_temp_status */
3429 END IF; /* end of validation on resource and ssn*/
3430 /*End of fix for bug 6074930 */
3431
3432 END IF ; -- Transaction Type : Create and Update
3433
3434
3435 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3436 ('Entity Validation was processed. . . ' || l_return_status);
3437 END IF ;
3438
3439
3440 --
3441 -- Return revised operation records
3442 --
3443 x_rev_op_resource_rec := l_rev_op_resource_rec ;
3444 x_rev_op_res_unexp_rec := l_rev_op_res_unexp_rec ;
3445
3446 --
3447 -- Return Error Status
3448 --
3449 x_return_status := l_return_status;
3450 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3451
3452
3453 EXCEPTION
3454 WHEN OTHERS THEN
3455 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3456 ('Some unknown error in Entity Validation . . .' || SQLERRM );
3457 END IF ;
3458
3459
3460 l_err_text := G_PKG_NAME || ' Validation (Entity Validation) '
3461 || substrb(SQLERRM,1,200);
3462 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
3463
3464 Error_Handler.Add_Error_Token
3465 ( p_message_name => NULL
3466 , p_message_text => l_err_text
3467 , p_mesg_token_tbl => l_mesg_token_tbl
3468 , x_mesg_token_tbl => l_mesg_token_tbl
3469 ) ;
3470
3471 -- Return the status and message table.
3472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3473 x_mesg_token_tbl := l_mesg_token_tbl ;
3474 END Check_Entity ;
3475
3476 /* bug:4638695 For an operation do not allow same resource to be added more than once with same SSN */
3477 PROCEDURE Val_Resource_SSN
3478 ( p_op_seq_id IN NUMBER
3479 , p_res_seq_num IN NUMBER
3480 , p_sch_seq_num IN NUMBER
3481 , p_resource_id IN NUMBER
3482 , x_return_status IN OUT NOCOPY VARCHAR2
3483 )
3484 IS
3485 l_same_rsc_ssn NUMBER;
3486 BEGIN
3487 x_return_status := FND_API.G_RET_STS_SUCCESS;
3488 l_same_rsc_ssn := 0;
3489
3490 SELECT COUNT(1)
3491 INTO l_same_rsc_ssn
3492 FROM BOM_OPERATION_RESOURCES
3493 WHERE
3494 SCHEDULE_SEQ_NUM = p_sch_seq_num
3495 AND p_sch_seq_num IS NOT NULL
3496 AND SCHEDULE_SEQ_NUM IS NOT NULL
3497 AND RESOURCE_SEQ_NUM <> p_res_seq_num
3498 AND RESOURCE_ID = p_resource_id
3499 AND OPERATION_SEQUENCE_ID = p_op_seq_id ;
3500
3501 IF ( l_same_rsc_ssn > 0 ) THEN
3502 x_return_status := Error_Handler.G_STATUS_ERROR;
3503 END IF;
3504
3505 EXCEPTION
3506 WHEN NO_DATA_FOUND THEN
3507 NULL;
3508
3509 END Val_Resource_SSN;
3510
3511 /*Fix for bug 6074930 -Added below procedure Val_schedule_flag.
3512 It is called by procedure Check_Entity.
3513 Purpose: Scheduled simultaneous resources/sub-resources should have the
3514 same scheduling flag. Resources/sub-resources with schedule flag 'No'
3515 are unscheduled and hence exempt for this validation.*/
3516
3517 PROCEDURE Val_Schedule_Flag
3518 ( p_op_seq_id IN NUMBER
3519 , p_res_seq_num IN NUMBER
3520 , p_sch_seq_num IN NUMBER
3521 , p_sch_flag IN NUMBER
3522 , p_ret_res_id IN OUT NOCOPY NUMBER
3523 , x_return_status IN OUT NOCOPY VARCHAR2
3524 )
3525 IS
3526 l_resource_id number;
3527
3528 CURSOR l_sch_res_cur IS
3529 SELECT resource_id
3530 FROM bom_operation_resources
3531 WHERE operation_sequence_id = p_op_seq_id
3532 AND resource_seq_num <> p_res_seq_num
3533 AND nvl(schedule_seq_num,resource_seq_num) = nvl(p_sch_seq_num,p_res_seq_num)
3534 AND schedule_flag not in (p_sch_flag,l_NO_SCHEDULE)
3535 AND rownum=1;
3536
3537 CURSOR l_sch_sub_res_cur IS
3538 SELECT resource_id
3539 FROM bom_sub_operation_resources
3540 WHERE operation_sequence_id = p_op_seq_id
3541 AND schedule_seq_num = nvl(p_sch_seq_num,p_res_seq_num)
3542 AND schedule_flag not in (p_sch_flag,l_NO_SCHEDULE)
3543 AND rownum=1;
3544
3545 BEGIN
3546 x_return_status := FND_API.G_RET_STS_SUCCESS;
3547 l_resource_id := FND_API.G_MISS_NUM;
3548
3549 /* Verify whether the current resource violates the validation w.r.t to
3550 any existing resource. */
3551 OPEN l_sch_res_cur;
3552 FETCH l_sch_res_cur INTO l_resource_id;
3553
3554 /* Return error status if violation occurs */
3555 IF l_sch_res_cur%FOUND THEN
3556 p_ret_res_id := l_resource_id;
3557 x_return_status := Error_Handler.G_STATUS_ERROR;
3558 END IF;
3559
3560 IF l_sch_res_cur%ISOPEN THEN
3561 CLOSE l_sch_res_cur;
3562 END IF;
3563
3564 /* If no violated resource is found above, then verify whether the current resource
3565 violates the validation w.r.t to any existing sub-resource. */
3566 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3567
3568 OPEN l_sch_sub_res_cur;
3569 FETCH l_sch_sub_res_cur INTO l_resource_id;
3570
3571 /* Return error status if violation occurs */
3572 IF l_sch_sub_res_cur%FOUND THEN
3573 p_ret_res_id := l_resource_id;
3574 x_return_status := Error_Handler.G_STATUS_ERROR;
3575 END IF;
3576
3577 IF l_sch_sub_res_cur%ISOPEN THEN
3578 CLOSE l_sch_sub_res_cur;
3579 END IF;
3580
3581 END IF;
3582
3583 END Val_Schedule_Flag;
3584
3585
3586 END BOM_Validate_Op_Res ;