[Home] [Help]
PACKAGE BODY: APPS.BOM_VALIDATE_OP_SEQ
Source
1 PACKAGE BODY BOM_Validate_Op_Seq AS
2 /* $Header: BOMLOPSB.pls 120.7.12010000.2 2008/10/15 11:43:24 tbhande ship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMLOPSB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Validate_Op_Seq
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 07-AUG-00 Masanori Kimizuka Initial Creation
21 --
22 ****************************************************************************/
23
24 G_Pkg_Name VARCHAR2(30) := 'BOM_Validate_Op_Seq';
25
26
27 l_MODEL CONSTANT NUMBER := 1 ;
28 l_OPTION_CLASS CONSTANT NUMBER := 2 ;
29 l_PLANNING CONSTANT NUMBER := 3 ;
30 l_STANDARD CONSTANT NUMBER := 4 ;
31 l_PRODFAMILY CONSTANT NUMBER := 5 ;
32 l_EVENT CONSTANT NUMBER := 1 ;
33 l_PROCESS CONSTANT NUMBER := 2 ;
34 l_LINE_OP CONSTANT NUMBER := 3 ;
35 l_ACD_ADD CONSTANT NUMBER := 1 ;
36 l_ACD_CHANGE CONSTANT NUMBER := 2 ;
37 l_ACD_DISABLE CONSTANT NUMBER := 3 ;
38
39
40
41
42 /******************************************************************
43 * OTHER LOCAL FUNCTION AND PROCEDURES
44 * Purpose : Called by Check_Entity or something
45 *********************************************************************/
46 --
47 -- Function Check Prrimary Routing
48 --
49 FUNCTION Check_Primary_Routing( p_revised_item_id IN NUMBER
50 , p_organization_id IN NUMBER )
51 RETURN BOOLEAN
52 IS
53 CURSOR l_primary_rtg_csr ( p_revised_item_id NUMBER
54 ,p_organization_id NUMBER )
55 IS
56 SELECT 'Primary not Exists'
57 FROM bom_operational_routings
58 WHERE assembly_item_id = p_revised_item_id
59 AND organization_id = p_organization_id
60 AND NVL(alternate_routing_designator, 'NONE') = 'NONE' ;
61
62 l_ret_status BOOLEAN := FALSE ;
63
64 BEGIN
65 FOR l_primary_rtg_rec IN l_primary_rtg_csr( p_revised_item_id
66 , p_organization_id )
67 LOOP
68 l_ret_status := TRUE ;
69 END LOOP;
70
71 -- If the loop does not execute then
72 -- return false
73 RETURN l_ret_status ;
74
75 END Check_Primary_Routing ;
76
77
78 --
79 -- Function: Check if unimplemented Rev Comp referencing Op Seq Num
80 -- exists for Transaction Type : Cancel
81 --
82 FUNCTION Check_Ref_Rev_Comp
83 ( p_operation_seq_num IN NUMBER
84 , p_start_effective_date IN DATE
85 , p_rev_item_seq_id IN NUMBER
86 )
87 RETURN BOOLEAN
88 IS
89 CURSOR l_ref_rev_cmp_csr ( p_operation_seq_num NUMBER
90 , p_start_effective_date DATE
91 , p_rev_item_seq_id NUMBER
92 )
93
94 IS
95 SELECT 'Rev Comp referencing Seq Num exists'
96 FROM SYS.DUAL
97 WHERE EXISTS (SELECT NULL
98 FROM BOM_INVENTORY_COMPONENTS bic
99 , ENG_REVISED_ITEMS eri
100 WHERE TRUNC(bic.effectivity_date) >= TRUNC(p_start_effective_date)
101 AND bic.implementation_date IS NULL
102 AND bic.operation_seq_num = p_operation_seq_num
103 AND bic.bill_sequence_id = eri.bill_sequence_id
104 AND eri.revised_item_sequence_id = p_rev_item_seq_id
105 ) ;
106
107 l_ret_status BOOLEAN := TRUE ;
108
109 BEGIN
110 FOR l_ref_rev_cmp_rec IN l_ref_rev_cmp_csr
111 ( p_operation_seq_num
112 , p_start_effective_date
113 , p_rev_item_seq_id
114 )
115 LOOP
116 l_ret_status := FALSE ;
117 END LOOP;
118
119 -- If the loop does not execute then
120 -- return false
121 RETURN l_ret_status ;
122
123 END Check_Ref_Rev_Comp ;
124
125
126 --
127 -- Function: Check if Op Seq Num exists in Work Order
128 -- in ECO by Lot, Wo, Cum Qty
129 --
130 FUNCTION Check_ECO_By_WO_Effectivity
131 ( p_revised_item_sequence_id IN NUMBER
132 , p_operation_seq_num IN NUMBER
133 , p_organization_id IN NUMBER
134 , p_rev_item_id IN NUMBER
135 )
136
137 RETURN BOOLEAN
138 IS
139 l_ret_status BOOLEAN := TRUE ;
140
141 l_lot_number varchar2(30) := NULL;
142 l_from_wip_entity_id NUMBER :=0;
143 l_to_wip_entity_id NUMBER :=0;
144 l_from_cum_qty NUMBER :=0;
145
146
147 CURSOR l_check_lot_num_csr ( p_lot_number VARCHAR2
148 , p_operation_seq_num NUMBER
149 , p_organization_id NUMBER
150 , p_rev_item_id NUMBER
151 )
152 IS
153 SELECT 'Op Seq does not exist'
154 FROM SYS.DUAL
155 WHERE EXISTS (SELECT NULL
156 FROM WIP_DISCRETE_JOBS wdj
157 WHERE (wdj.status_type <> 1
158 OR
159 NOT EXISTS(SELECT NULL
160 FROM WIP_OPERATIONS wo
161 WHERE operation_seq_num = p_operation_seq_num
162 AND wip_entity_id = wdj.wip_entity_id)
163 )
164 AND wdj.lot_number = p_lot_number
165 AND wdj.organization_id = p_organization_id
166 AND wdj.primary_item_id = p_rev_item_id
167 ) ;
168
169 CURSOR l_check_wo_csr ( p_from_wip_entity_id NUMBER
170 , p_to_wip_entity_id NUMBER
171 , p_operation_seq_num NUMBER
172 , p_organization_id NUMBER )
173 IS
174 SELECT 'Op Seq does not exist'
175 FROM SYS.DUAL
176 WHERE EXISTS (SELECT NULL
177 FROM WIP_DISCRETE_JOBS wdj
178 , WIP_ENTITIES we
179 , WIP_ENTITIES we1
180 , WIP_ENTITIES we2
181 WHERE (wdj.status_type <> 1
182 OR
183 NOT EXISTS (SELECT NULL
184 FROM WIP_OPERATIONS wo
185 WHERE operation_seq_num = p_operation_seq_num
186 AND wip_entity_id = wdj.wip_entity_id )
187 )
188 AND wdj.wip_entity_id = we.wip_entity_id
189 AND we.organization_Id = p_organization_id
190 AND we.wip_entity_name >= we1.wip_entity_name
191 AND we.wip_entity_name <= we2.wip_entity_name
192 AND we1.wip_entity_id = p_from_wip_entity_id
193 AND we2.wip_entity_id = NVL(p_to_wip_entity_id, p_from_wip_entity_id)
194 ) ;
195
196 CURSOR l_check_cum_csr ( p_from_wip_entity_id NUMBER
197 , p_operation_seq_num NUMBER)
198 IS
199 SELECT 'Op Seq does not exist'
200 FROM SYS.DUAL
201 WHERE EXISTS (SELECT NULL
202 FROM WIP_DISCRETE_JOBS wdj
203 WHERE (wdj.status_type <> 1
204 OR
205 NOT EXISTS(SELECT NULL
206 FROM WIP_OPERATIONS wo
207 WHERE operation_seq_num = p_operation_seq_num
208 AND wip_entity_id = wdj.wip_entity_id )
209 )
210 AND wdj.wip_entity_id = p_from_wip_entity_id
211 ) ;
212
213 BEGIN
214
215
216 l_lot_number := BOM_Rtg_Globals.Get_Lot_Number;
217 l_from_wip_entity_id := BOM_Rtg_Globals.Get_From_Wip_Entity_Id;
218 l_to_wip_entity_id := BOM_Rtg_Globals.Get_To_Wip_Entity_Id;
219 l_from_cum_qty := BOM_Rtg_Globals.Get_From_Cum_Qty;
220
221
222 -- Check if Op Seq Num is exist in ECO by Lot
223 IF l_lot_number IS NOT NULL
224 AND l_from_wip_entity_id IS NULL
225 AND l_to_wip_entity_id IS NULL
226 AND l_from_cum_qty IS NULL
227 THEN
228
229 -- Modified bug for 1611803
230 FOR l_lot_num_rec IN l_check_lot_num_csr
231 ( p_lot_number => l_lot_number
232 , p_operation_seq_num => p_operation_seq_num
233 , p_organization_Id => p_organization_id
234 , p_rev_item_id => p_rev_item_id
235 )
236 LOOP
237 l_ret_status := FALSE ;
238 END LOOP ;
239
240 -- Check if Op Seq Num is exist in ECO by Cum
241 ELSIF l_lot_number IS NULL
242 AND l_from_wip_entity_id IS NOT NULL
243 AND l_to_wip_entity_id IS NULL
244 AND l_from_cum_qty IS NOT NULL
245 THEN
246
247 FOR l_cum_rec IN l_check_cum_csr
248 ( p_from_wip_entity_id => l_from_wip_entity_id
249 , p_operation_seq_num => p_operation_seq_num )
250 LOOP
251 l_ret_status := FALSE ;
252 END LOOP ;
253
254 -- Check if Op Seq Num is exist in ECO by WO
255 ELSIF l_lot_number IS NULL
256 AND l_from_wip_entity_id IS NOT NULL
257 AND l_from_cum_qty IS NULL
258 THEN
259
260 FOR l_wo_rec IN l_check_wo_csr
261 ( p_from_wip_entity_id => l_from_wip_entity_id
262 , p_to_wip_entity_id => l_to_wip_entity_id
263 , p_operation_seq_num => p_operation_seq_num
264 , p_organization_id => p_organization_id)
265 LOOP
266 l_ret_status := FALSE ;
267 END LOOP ;
268
269 ELSIF l_lot_number IS NULL
270 AND l_from_wip_entity_id IS NULL
271 AND l_to_wip_entity_id IS NULL
272 AND l_from_cum_qty IS NULL
273 THEN
274 NULL ;
275
276 -- ELSE
277 -- l_ret_status := FALSE ;
278 --
279
280 END IF ;
281
282 RETURN l_ret_status ;
283
284 END Check_ECO_By_WO_Effectivity ;
285
286
287 -- Function Check_RevItem_Alternate
288 -- Added by MK on 11/01/2000
289 -- Called from Check_Access
290 --
291 -- Comment out to resolve Odf dependency
292
293 --
294 -- Function Check_ResExists
295 -- Added by MK on 11/28/2000
296 -- Called from Check_Entity
297 FUNCTION Check_ResExists ( p_op_seq_id IN NUMBER
298 , p_old_op_seq_id IN NUMBER
299 , p_acd_type IN NUMBER
300 )
301 RETURN BOOLEAN
302 IS
303
304
305 -- Check if the operatin already has resources
306 CURSOR l_exist_res_cur (x_op_seq_id NUMBER)
307 IS
308 SELECT 'Resource Exist'
309 FROM DUAL
310 WHERE EXISTS ( SELECT NULL
311 FROM BOM_OPERATION_RESOURCES
312 WHERE operation_sequence_id = p_op_seq_id
313 ) ;
314 BEGIN
315
316 FOR l_exist_res_rec IN l_exist_res_cur(x_op_seq_id => p_op_seq_id)
317 LOOP
318 RETURN TRUE ;
319 END LOOP ;
320
321 IF NVL(p_acd_type, l_ACD_ADD) = l_ACD_CHANGE
322 THEN
323
324 FOR l_exist_res_rec IN l_exist_res_cur(x_op_seq_id => p_old_op_seq_id)
325 LOOP
326 RETURN TRUE ;
327 END LOOP ;
328 END IF ;
329
330 RETURN FALSE ;
331
332 END Check_ResExists ;
333
334
335
336 /******************************************************************
337 * Procedure : Check_Existence used by RTG BO
338 * Parameters IN : Operation exposed column record
339 * Operation unexposed column record
340 * Parameters out: Old Operation exposed column record
341 * Old Operation unexposed column record
342 * Mesg Token Table
343 * Return Status
344 * Purpose : Convert Routing Operation to Common Operation and
345 * Call Check_Existence for Common Operation.
346 * After calling Check_Existence, convert old Common
347 * operation record back to Routing Operation Record
348 *********************************************************************/
349 PROCEDURE Check_Existence
350 ( p_operation_rec IN Bom_Rtg_Pub.Operation_Rec_Type
351 , p_op_unexp_rec IN Bom_Rtg_Pub.Op_Unexposed_Rec_Type
352 , x_old_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Operation_Rec_Type
353 , x_old_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Unexposed_Rec_Type
354 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
355 , x_return_status IN OUT NOCOPY VARCHAR2
356 )
357 IS
358 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
359 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
360 l_old_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
361 l_old_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
362
363 BEGIN
364 -- Convert Routing Operation to Common Operation
365 Bom_Rtg_Pub.Convert_RtgOp_To_ComOp
366 ( p_rtg_operation_rec => p_operation_rec
367 , p_rtg_op_unexp_rec => p_op_unexp_rec
368 , x_com_operation_rec => l_com_operation_rec
369 , x_com_op_unexp_rec => l_com_op_unexp_rec
370 ) ;
371
372 -- Call Check_Existence
373 Bom_Validate_Op_Seq.Check_Existence
374 ( p_com_operation_rec => l_com_operation_rec
375 , p_com_op_unexp_rec => l_com_op_unexp_rec
376 , x_old_com_operation_rec => l_old_com_operation_rec
377 , x_old_com_op_unexp_rec => l_old_com_op_unexp_rec
378 , x_return_status => x_return_status
379 , x_mesg_token_tbl => x_mesg_token_tbl
380 ) ;
381
382 -- Convert old Common Opeartion Record back to Routing Operation
383 Bom_Rtg_Pub.Convert_ComOp_To_RtgOp
384 ( p_com_operation_rec => l_old_com_operation_rec
385 , p_com_op_unexp_rec => l_old_com_op_unexp_rec
386 , x_rtg_operation_rec => x_old_operation_rec
387 , x_rtg_op_unexp_rec => x_old_op_unexp_rec
388 ) ;
389
390 END Check_Existence ;
391
392
393 /******************************************************************
394 * Procedure : Check_Existence used by ECO BO
395 * Parameters IN : Revised Operation exposed column record
396 * Revised Operation unexposed column record
397 * Parameters out: Revised Old Operation exposed column record
398 * Revised Old Operation unexposed column record
399 * Mesg Token Table
400 * Return Status
401 * Purpose : Convert Revised Operation to Common Operation and
402 * Call Check_Existence for Common Operation.
403 * After calling Check_Existence, convert old Common
404 * operation record back to Revised Operation Record
405 *********************************************************************/
406 PROCEDURE Check_Existence
407 ( p_rev_operation_rec IN Bom_Rtg_Pub.Rev_Operation_Rec_Type
408 , p_rev_op_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
409 , x_old_rev_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Operation_Rec_Type
410 , x_old_rev_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
411 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
412 , x_return_status IN OUT NOCOPY VARCHAR2
413 )
414 IS
415 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
416 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
417 l_old_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
418 l_old_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
419
420 BEGIN
421 -- Convert Routing Operation to Common Operation
422 Bom_Rtg_Pub.Convert_EcoOp_To_ComOp
423 ( p_rev_operation_rec => p_rev_operation_rec
424 , p_rev_op_unexp_rec => p_rev_op_unexp_rec
425 , x_com_operation_rec => l_com_operation_rec
426 , x_com_op_unexp_rec => l_com_op_unexp_rec
427 ) ;
428
429 -- Call Check_Existence
430 Bom_Validate_Op_Seq.Check_Existence
431 ( p_com_operation_rec => l_com_operation_rec
432 , p_com_op_unexp_rec => l_com_op_unexp_rec
433 , x_old_com_operation_rec => l_old_com_operation_rec
434 , x_old_com_op_unexp_rec => l_old_com_op_unexp_rec
435 , x_return_status => x_return_status
436 , x_mesg_token_tbl => x_mesg_token_tbl
437 ) ;
438
439 -- Convert old Common Opeartion Record back to Routing Operation
440 Bom_Rtg_Pub.Convert_ComOp_To_EcoOp
441 ( p_com_operation_rec => l_old_com_operation_rec
442 , p_com_op_unexp_rec => l_old_com_op_unexp_rec
443 , x_rev_operation_rec => x_old_rev_operation_rec
444 , x_rev_op_unexp_rec => x_old_rev_op_unexp_rec
445 ) ;
446
447 END Check_Existence ;
448
449
450 /******************************************************************
451 * Procedure : Check_Existence called by RTG BO and by ECO BO
452 *
453 * Parameters IN : Common operation exposed column record
454 * Common operation unexposed column record
455 * Parameters out: Old Common operation exposed column record
456 * Old Common operation unexposed column record
457 * Mesg Token Table
458 * Return Status
459 * Purpose : Check_Existence will query using the primary key
460 * information and return a success if the operation is
461 * CREATE and the record EXISTS or will return an
462 * error if the operation is UPDATE and record DOES NOT
463 * EXIST.
464 * In case of UPDATE if record exists, then the procedure
465 * will return old record in the old entity parameters
466 * with a success status.
467 *********************************************************************/
468
469 PROCEDURE Check_Existence
470 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
471 , p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
472 , x_old_com_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Operation_Rec_Type
473 , x_old_com_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
474 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
475 , x_return_status IN OUT NOCOPY VARCHAR2
476 )
477 IS
478 l_token_tbl Error_Handler.Token_Tbl_Type;
479 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
480 l_return_status VARCHAR2(1);
481
482 BEGIN
483
484 l_Token_Tbl(1).Token_Name := 'OP_SEQ_NUMBER';
485 l_Token_Tbl(1).Token_Value := p_com_operation_rec.operation_sequence_number;
486 l_Token_Tbl(2).Token_Name := 'REVISED_ITEM_NAME';
487 l_Token_Tbl(2).Token_Value := p_com_operation_rec.revised_item_name;
488
489 Bom_Op_Seq_Util.Query_Row
490 ( p_operation_sequence_number => p_com_operation_rec.operation_sequence_number
491 , p_effectivity_date => p_com_operation_rec.start_effective_date
492 , p_routing_sequence_id => p_com_op_unexp_rec.routing_sequence_id
493 , p_operation_type => p_com_operation_rec.operation_type
494 , p_mesg_token_tbl => l_mesg_token_tbl
495 , x_com_operation_rec => x_old_com_operation_rec
496 , x_com_op_unexp_rec => x_old_com_op_unexp_rec
497 , x_mesg_token_tbl => l_mesg_token_tbl
498 , x_return_status => l_return_status
499 ) ;
500
501 IF l_return_status = BOM_Rtg_Globals.G_RECORD_FOUND AND
502 p_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
503 THEN
504 Error_Handler.Add_Error_Token
505 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
506 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
507 , p_message_name => 'BOM_OP_ALREADY_EXISTS'
508 , p_token_tbl => l_token_tbl
509 ) ;
510 l_return_status := FND_API.G_RET_STS_ERROR ;
511
512 ELSIF l_return_status = BOM_Rtg_Globals.G_RECORD_NOT_FOUND AND
513 p_com_operation_rec.transaction_type IN
514 (BOM_Rtg_Globals.G_OPR_UPDATE, BOM_Rtg_Globals.G_OPR_DELETE, BOM_Rtg_Globals.G_OPR_CANCEL)
515 THEN
516 Error_Handler.Add_Error_Token
517 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
518 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
519 , p_message_name => 'BOM_OP_DOESNOT_EXIST'
520 , p_token_tbl => l_token_tbl
521 ) ;
522 l_return_status := FND_API.G_RET_STS_ERROR ;
523
524 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
525 THEN
526 Error_Handler.Add_Error_Token
527 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
528 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
529 , p_message_name => NULL
530 , p_message_text => 'Unexpected error while existence verification of '
531 || 'Operation Sequences '
532 || p_com_operation_rec.operation_sequence_number
533 , p_token_tbl => l_token_tbl
534 ) ;
535 ELSE
536 l_return_status := FND_API.G_RET_STS_SUCCESS;
537 END IF ;
538
539 x_return_status := l_return_status;
540 x_mesg_token_tbl := l_Mesg_Token_Tbl;
541
542 END Check_Existence;
543
544
545 /******************************************************************
546 * Prcoedure : Check_Lineage used by ECO BO
547 * Parameters IN : Revised Operation exposed column record
548 * Revised Operation unexposed column record
549 * Parameters out: Mesg_Token_Tbl
550 * Return_Status
551 * Purpose : Check_Lineage procedure will verify that the entity
552 * record that the user has passed is for the right
553 * parent and that the parent exists.
554 *********************************************************************/
555 PROCEDURE Check_Lineage
556 ( p_routing_sequence_id IN NUMBER
557 , p_operation_sequence_number IN NUMBER
558 , p_effectivity_date IN DATE
559 , p_operation_type IN NUMBER
560 , p_revised_item_sequence_id IN NUMBER
561 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
562 , x_return_status IN OUT NOCOPY VARCHAR2 )
563
564 IS
565
566 CURSOR l_ger_rev_op_cur( p_routing_sequence_id NUMBER
567 , p_operation_sequence_number NUMBER
568 , p_effectivity_date DATE
569 , p_operation_type NUMBER )
570 IS
571
572 SELECT revised_item_sequence_id
573 FROM BOM_OPERATION_SEQUENCES
574 WHERE NVL(OPERATION_TYPE, 1) = NVL(p_operation_type , 1)
575 AND OPERATION_SEQ_NUM = p_operation_sequence_number
576 AND EFFECTIVITY_DATE = p_effectivity_date -- Changed for bug 2647027
577 -- AND TRUNC(EFFECTIVITY_DATE) = TRUNC(p_effectivity_date)
578 AND routing_sequence_id = p_routing_sequence_id
579 ;
580
581 l_return_status VARCHAR2(1);
582 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
583 l_err_text VARCHAR(2000) ;
584
585 BEGIN
586 l_return_status := FND_API.G_RET_STS_SUCCESS;
587
588 /*************************************************************
589 --
590 -- In case of an update, based on the revised item information
591 -- Routing Sequence Id and Revised Item Sequence Id is queried from
592 -- the database. The revised item sequence id can however be
593 -- different from that in the database and should be checked
594 -- and given an error.
595 *************************************************************/
596 FOR l_get_rev_op_rec IN l_ger_rev_op_cur
597 ( p_routing_sequence_id => p_routing_sequence_id
598 , p_operation_sequence_number => p_operation_sequence_number
599 , p_effectivity_date => p_effectivity_date
600 , p_operation_type => p_operation_type )
601 LOOP
602 IF NVL(l_get_rev_op_rec.revised_item_sequence_id, 0) <>
603 NVL(p_revised_item_sequence_id,0)
604 THEN
605 l_return_status := FND_API.G_RET_STS_ERROR;
606 END IF;
607 END LOOP;
608
609 x_return_status := l_return_status;
610 x_mesg_token_tbl := l_mesg_token_tbl;
611
612 EXCEPTION
613 WHEN OTHERS THEN
614
615 l_err_text := G_PKG_NAME || ' Validation (Check Lineage) '
616 || substrb(SQLERRM,1,200);
617 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
618
619 Error_Handler.Add_Error_Token
620 ( p_message_name => NULL
621 , p_message_text => l_err_text
622 , p_mesg_token_tbl => l_mesg_token_tbl
623 , x_mesg_token_tbl => l_mesg_token_tbl
624 ) ;
625
626 -- Return the status and message table.
627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
628 x_mesg_token_tbl := l_mesg_token_tbl ;
629
630
631 END Check_Lineage;
632
633
634 /******************************************************************
635 * Prcoedure : Check_CommonRtg used by ECO Bo and Rtg Bo
636 * Parameters IN : Routing Sequence Id
637 * Parameters out: Mesg_Token_Tbl
638 * Return_Status
639 * Purpose : Check_CommonRtg procedure will verify that the parent
640 * routing does not have a common.
641 *********************************************************************/
642
643 PROCEDURE Check_CommonRtg
644 ( p_routing_sequence_id IN NUMBER
645 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
646 , x_return_status IN OUT NOCOPY VARCHAR2
647 )
648 IS
649
650 CURSOR l_get_common_cur (p_routing_sequence_id NUMBER) IS
651 SELECT 'common exists'
652 FROM bom_operational_routings
653 WHERE common_routing_sequence_id <> routing_sequence_id
654 AND routing_sequence_id = p_routing_sequence_id ;
655
656
657 l_Token_Tbl Error_Handler.Token_Tbl_Type ;
658 l_return_status VARCHAR2(1) ;
659 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
660 l_err_text VARCHAR(2000) ;
661
662 BEGIN
663 l_return_status := FND_API.G_RET_STS_SUCCESS;
664
665 FOR l_get_common_rec IN l_get_common_cur(p_routing_sequence_id)
666 LOOP
667 l_return_status := FND_API.G_RET_STS_ERROR ;
668 END LOOP;
669
670 x_return_status := l_return_status;
671 x_mesg_token_tbl := l_mesg_token_tbl;
672
673 EXCEPTION
674 WHEN OTHERS THEN
675
676 l_err_text := G_PKG_NAME || ' Validation (Check CommonRouting) '
677 || substrb(SQLERRM,1,200);
678 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
679
680 Error_Handler.Add_Error_Token
681 ( p_message_name => NULL
682 , p_message_text => l_err_text
683 , p_mesg_token_tbl => l_mesg_token_tbl
684 , x_mesg_token_tbl => l_mesg_token_tbl
685 ) ;
686
687 -- Return the status and message table.
688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
689 x_mesg_token_tbl := l_mesg_token_tbl ;
690
691 END Check_CommonRtg ;
692
693
694 /******************************************************************
695 * Procedure : Check_Required used by RTG BO
696 * Parameters IN : Operation exposed column record
697 * Parameters out: Mesg Token Table
698 * Return Status
699 * Purpose : Convert Routing Operation to Common Operation and
700 * Call Check_Required for Common Operation.
701 *
702 *********************************************************************/
703 PROCEDURE Check_Required
704 ( p_operation_rec IN Bom_Rtg_Pub.Operation_Rec_Type
705 , x_return_status IN OUT NOCOPY VARCHAR2
706 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
707 )
708
709 IS
710 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
711 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
712
713 p_op_unexp_rec Bom_Rtg_Pub.Op_Unexposed_Rec_Type ;
714
715 BEGIN
716 -- Convert Routing Operation to Common Operation
717 Bom_Rtg_Pub.Convert_RtgOp_To_ComOp
718 ( p_rtg_operation_rec => p_operation_rec
719 , p_rtg_op_unexp_rec => p_op_unexp_rec
720 , x_com_operation_rec => l_com_operation_rec
721 , x_com_op_unexp_rec => l_com_op_unexp_rec
722 ) ;
723
724 Bom_Validate_Op_Seq.Check_required
725 ( p_com_operation_rec => l_com_operation_rec
726 , x_return_status => x_return_status
727 , x_mesg_token_tbl => x_mesg_token_tbl
728 );
729
730 END Check_Required ;
731
732 /******************************************************************
733 * Procedure : Check_Required used by ECO BO
734 * Parameters IN : Operation exposed column record
735 * Parameters out: Mesg Token Table
736 * Return Status
737 * Purpose : Convert Revised Operation to Common Operation and
738 * Call Check_Required for Common Operation.
739 *
740 *********************************************************************/
741 PROCEDURE Check_Required
742 ( p_rev_operation_rec IN Bom_Rtg_Pub.Rev_Operation_Rec_Type
743 , x_return_status IN OUT NOCOPY VARCHAR2
744 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
745 )
746
747 IS
748 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
749 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
750
751 p_rev_op_unexp_rec Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type ;
752
753 BEGIN
754 -- Convert Revised Operation to Common Operation
755 Bom_Rtg_Pub.Convert_EcoOp_To_ComOp
756 ( p_rev_operation_rec => p_rev_operation_rec
757 , p_rev_op_unexp_rec => p_rev_op_unexp_rec
758 , x_com_operation_rec => l_com_operation_rec
759 , x_com_op_unexp_rec => l_com_op_unexp_rec
760 ) ;
761
762 Bom_Validate_Op_Seq.Check_required
763 ( p_com_operation_rec => l_com_operation_rec
764 , x_return_status => x_return_status
765 , x_mesg_token_tbl => x_mesg_token_tbl
766 );
767
768 END Check_Required ;
769
770 /*****************************************************************
771 * Procedure : Check_Required for Common
772 * internally called by RTG BO and ECO BO
773 * Parameters IN : Revised Operation exposed column record
774 * Paramaters out: Return Status
775 * Mesg Token Table
776 * Purpose : Procedure will check if the user has given all the
777 * required columns for the type of operation user is
778 * trying to perform. If the required columns are not
779 * filled in, then the record would get an error.
780 ********************************************************************/
781 PROCEDURE Check_Required
782 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
783 , x_return_status IN OUT NOCOPY VARCHAR2
784 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
785 )
786 IS
787
788 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
789 l_err_text VARCHAR(2000) ;
790 l_Token_Tbl Error_Handler.Token_Tbl_Type;
791
792 BEGIN
793 x_return_status := FND_API.G_RET_STS_SUCCESS;
794 l_Token_Tbl(1).token_name := 'OP_SEQ_NUMBER';
795 l_Token_Tbl(1).token_value := p_com_operation_rec.operation_sequence_number ;
796
797
798 -- ACD_TYPE
799 IF ( p_com_operation_rec.acd_type IS NULL OR
800 p_com_operation_rec.acd_type = FND_API.G_MISS_NUM
801 ) AND
802 BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
803 THEN
804 Error_Handler.Add_Error_Token
805 ( p_message_name => 'BOM_OP_ACD_TYPE_MISSING'
806 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
807 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
808 , p_Token_Tbl => l_Token_Tbl
809 ) ;
810
811 x_return_status := FND_API.G_RET_STS_ERROR ;
812
813 END IF;
814
815 -- Standard Operation Id and Operation Type
816 IF ( p_com_operation_rec.operation_type IN (2, 3)
817 AND p_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
818 AND ( p_com_operation_rec.standard_operation_code IS NULL OR
819 p_com_operation_rec.standard_operation_code = FND_API.G_MISS_CHAR )
820 )
821 THEN
822 Error_Handler.Add_Error_Token
823 ( p_message_name => 'BOM_FLM_OP_STDOP_REQUIRED'
824 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
825 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
826 , p_Token_Tbl => l_Token_Tbl
827 ) ;
828
829 x_return_status := FND_API.G_RET_STS_ERROR ;
830
831
832 -- Department Code for CREATEs
833 ELSIF
834 ( p_com_operation_rec.department_code IS NULL OR
835 p_com_operation_rec.department_code = FND_API.G_MISS_CHAR
836 )
837 AND p_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
838 AND NVL(p_com_operation_rec.acd_type, 1) = l_ACD_ADD
839 AND ( p_com_operation_rec.standard_operation_code IS NULL OR
840 p_com_operation_rec.standard_operation_code = FND_API.G_MISS_CHAR )
841 AND BOM_Rtg_Globals.Get_CFM_Rtg_Flag <> BOM_Rtg_Globals.G_Lot_Rtg
842 THEN
843 Error_Handler.Add_Error_Token
844 ( p_message_name => 'BOM_OP_DEPT_REQUIRED'
845 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
846 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
847 , p_Token_Tbl => l_Token_Tbl
848 ) ;
849
850 x_return_status := FND_API.G_RET_STS_ERROR ;
851
852 END IF;
853
854
855 -- Return the message table.
856 x_mesg_token_tbl := l_Mesg_Token_Tbl ;
857
858
859 EXCEPTION
860 WHEN OTHERS THEN
861
862 l_err_text := G_PKG_NAME || ' Validation (Check Required) '
863 || substrb(SQLERRM,1,200);
864 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
865
866 Error_Handler.Add_Error_Token
867 ( p_message_name => NULL
868 , p_message_text => l_err_text
869 , p_mesg_token_tbl => l_mesg_token_tbl
870 , x_mesg_token_tbl => l_mesg_token_tbl
871 ) ;
872
873 -- Return the status and message table.
874 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
875 x_mesg_token_tbl := l_mesg_token_tbl ;
876
877 END Check_Required ;
878
879
880 /********************************************************************
881 * Procedure : Check_Attributes used by RTG BO
882 * Parameters IN : Operation exposed column record
883 * Operation unexposed column record
884 * Parameters out: Return Status
885 * Message Token Table
886 * Purpose : Convert Routing Operation to Common Operation and
887 * Call Check_Attributes for Common.
888 * Check_Attributes will verify the exposed attributes
889 * of the operation record in their own entirety. No
890 * cross entity validations will be performed.
891 ********************************************************************/
892 PROCEDURE Check_Attributes
893 ( p_operation_rec IN Bom_Rtg_Pub.Operation_Rec_Type
894 , p_op_unexp_rec IN Bom_Rtg_Pub.Op_Unexposed_Rec_Type
895 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
896 , x_return_status IN OUT NOCOPY VARCHAR2
897 )
898 IS
899 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
900 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
901
902 BEGIN
903 -- Convert Routing Operation to Common Operation
904 Bom_Rtg_Pub.Convert_RtgOp_To_ComOp
905 ( p_rtg_operation_rec => p_operation_rec
906 , p_rtg_op_unexp_rec => p_op_unexp_rec
907 , x_com_operation_rec => l_com_operation_rec
908 , x_com_op_unexp_rec => l_com_op_unexp_rec
909 ) ;
910
911 -- Call Check Attributes procedure
912 Bom_Validate_Op_Seq.Check_Attributes
913 ( p_com_operation_rec => l_com_operation_rec
914 , p_com_op_unexp_rec => l_com_op_unexp_rec
915 , x_return_status => x_return_status
916 , x_mesg_token_tbl => x_mesg_token_tbl
917 ) ;
918
919 END Check_Attributes ;
920
921
922 /********************************************************************
923 * Procedure : Check_Attributes used by ECO BO
924 * Parameters IN : Revised Operation exposed column record
925 * Revised Operation unexposed column record
926 * Parameters out: Return Status
927 * Message Token Table
928 * Purpose : Convert Revised Operation to Common Operation and
929 * Call Check_Attributes for Common.
930 * Check_Attributes will verify the exposed attributes
931 * of the operation record in their own entirety. No
932 * cross entity validations will be performed.
933 ********************************************************************/
934 PROCEDURE Check_Attributes
935 ( p_rev_operation_rec IN Bom_Rtg_Pub.Rev_Operation_Rec_Type
936 , p_rev_op_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
937 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
938 , x_return_status IN OUT NOCOPY VARCHAR2
939 )
940 IS
941 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
942 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
943
944 BEGIN
945 -- Convert Revised Operation to Common Operation
946 Bom_Rtg_Pub.Convert_EcoOp_To_ComOp
947 ( p_rev_operation_rec => p_rev_operation_rec
948 , p_rev_op_unexp_rec => p_rev_op_unexp_rec
949 , x_com_operation_rec => l_com_operation_rec
950 , x_com_op_unexp_rec => l_com_op_unexp_rec
951 ) ;
952
953 -- Call Check Attributes procedure
954 Bom_Validate_Op_Seq.Check_Attributes
955 ( p_com_operation_rec => l_com_operation_rec
956 , p_com_op_unexp_rec => l_com_op_unexp_rec
957 , x_return_status => x_return_status
958 , x_mesg_token_tbl => x_mesg_token_tbl
959 ) ;
960
961 END Check_Attributes ;
962
963
964
965 /***************************************************************
966 * Procedure : Check_Attribute (Validation) for CREATE and UPDATE
967 * internally called by RTG BO and ECO BO
968 * Parameters IN : Common Operation exposed column record
969 * Common Operation unexposed column record
970 * Parameters out: Return Status
971 * Message Token Table
972 * Purpose : Attribute validation procedure will validate each
973 * attribute of operation in its entirety. If
974 * the validation of a column requires looking at some
975 * other columns value then the validation is done at
976 * the Entity level instead.
977 * All errors in the attribute validation are accumulated
978 * before the procedure returns with a Return_Status
979 * of 'E'.
980 *********************************************************************/
981 PROCEDURE Check_Attributes
982 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
983 , p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
984 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
985 , x_return_status IN OUT NOCOPY VARCHAR2
986 )
987 IS
988
989 l_return_status VARCHAR2(1);
990 l_err_text VARCHAR2(2000) ;
991 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
992 l_Token_Tbl Error_Handler.Token_Tbl_Type;
993
994 BEGIN
995
996 l_return_status := FND_API.G_RET_STS_SUCCESS;
997 x_return_status := FND_API.G_RET_STS_SUCCESS;
998
999 -- Set the first token to be equal to the operation sequence number
1000 l_Token_Tbl(1).token_name := 'OP_SEQ_NUMBER';
1001 l_Token_Tbl(1).token_value := p_com_operation_rec.operation_sequence_number ;
1002
1003 --
1004 -- Check if the user is trying to update a record with
1005 -- missing value when the column value is required.
1006 --
1007
1008 IF p_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
1009 THEN
1010
1011
1012 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1013 ('Operation Attr Validation: Missing Value. . . ' || l_return_status) ;
1014 END IF;
1015
1016 -- Operation Type
1017 IF p_com_operation_rec.operation_type = FND_API.G_MISS_NUM
1018 THEN
1019 Error_Handler.Add_Error_Token
1020 ( p_Message_Name => 'BOM_OP_OPTYPE_MISSING'
1021 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1022 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1023 , p_Token_Tbl => l_Token_Tbl
1024 );
1025 l_return_status := FND_API.G_RET_STS_ERROR;
1026 END IF ;
1027
1028
1029 -- Department Code
1030 IF p_com_operation_rec.department_code = FND_API.G_MISS_CHAR
1031 THEN
1032 Error_Handler.Add_Error_Token
1033 ( p_Message_Name => 'BOM_OP_DEPT_MISSING'
1034 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1035 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1036 , p_Token_Tbl => l_Token_Tbl
1037 );
1038 l_return_status := FND_API.G_RET_STS_ERROR;
1039 END IF;
1040
1041
1042 -- Count Point Type
1043 IF p_com_operation_rec.count_point_type = FND_API.G_MISS_NUM
1044 THEN
1045 Error_Handler.Add_Error_Token
1046 ( p_Message_Name => 'BOM_OP_CNTPOINT_TYPE_MISSING'
1047 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1048 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1049 , p_Token_Tbl => l_Token_Tbl
1050 );
1051 l_return_status := FND_API.G_RET_STS_ERROR;
1052 END IF;
1053
1054
1055 -- Option Dependent Flag
1056 IF p_com_operation_rec.option_dependent_flag = FND_API.G_MISS_NUM
1057 THEN
1058 Error_Handler.Add_Error_Token
1059 ( p_Message_Name => 'BOM_OP_DPTFLAG_INVALID'
1060 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1061 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1062 , p_Token_Tbl => l_Token_Tbl
1063 );
1064 l_return_status := FND_API.G_RET_STS_ERROR;
1065 END IF;
1066
1067 -- Reference Flag
1068 IF p_com_operation_rec.reference_flag = FND_API.G_MISS_NUM
1069 THEN
1070 Error_Handler.Add_Error_Token
1071 ( p_Message_Name => 'BOM_OP_REFERENCE_MISSING'
1072 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1073 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1074 , p_Token_Tbl => l_Token_Tbl
1075 );
1076 l_return_status := FND_API.G_RET_STS_ERROR;
1077 END IF;
1078
1079 -- Include In RollUp
1080 IF p_com_operation_rec.include_in_rollup = FND_API.G_MISS_NUM
1081 THEN
1082 Error_Handler.Add_Error_Token
1083 ( p_Message_Name => 'BOM_OP_ICDROLLUP_MISSING'
1084 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1085 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1086 , p_Token_Tbl => l_Token_Tbl
1087 );
1088 l_return_status := FND_API.G_RET_STS_ERROR;
1089 END IF;
1090
1091
1092 -- Op Yild Enabled Flag
1093 IF p_com_operation_rec.op_yield_enabled_flag = FND_API.G_MISS_NUM
1094 THEN
1095 Error_Handler.Add_Error_Token
1096 ( p_Message_Name => 'BOM_OP_YIELDENABLED_MISSING'
1097 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1098 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1099 , p_Token_Tbl => l_Token_Tbl
1100 );
1101 l_return_status := FND_API.G_RET_STS_ERROR;
1102 END IF ;
1103
1104
1105 -- Backfluch Flag
1106 IF p_com_operation_rec.backflush_flag = FND_API.G_MISS_NUM
1107 THEN
1108 Error_Handler.Add_Error_Token
1109 ( p_Message_Name => 'BOM_OP_BKFLUSH_FLAG_MISSING'
1110 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1111 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1112 , p_Token_Tbl => l_Token_Tbl
1113 );
1114 l_return_status := FND_API.G_RET_STS_ERROR;
1115 END IF ;
1116
1117
1118
1119 -- Standard Operation Code
1120 IF p_com_operation_rec.standard_operation_code = FND_API.G_MISS_CHAR
1121 THEN
1122 Error_Handler.Add_Error_Token
1123 ( p_Message_Name => 'BOM_OP_STD_OP_NOTUPDATABLE'
1124 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1125 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1126 , p_Token_Tbl => l_Token_Tbl
1127 );
1128 l_return_status := FND_API.G_RET_STS_ERROR;
1129 END IF ;
1130
1131
1132
1133 -- New Operation Sequence Number
1134 IF p_com_operation_rec.new_operation_sequence_number = FND_API.G_MISS_NUM
1135 THEN
1136 Error_Handler.Add_Error_Token
1137 ( p_Message_Name => 'BOM_OP_SEQNUM_MISSING'
1138 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1139 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1140 , p_Token_Tbl => l_Token_Tbl
1141 );
1142 l_return_status := FND_API.G_RET_STS_ERROR;
1143 END IF ;
1144
1145 -- New Start Effective Date for RTG_Bo Only
1146 IF p_com_operation_rec.new_start_effective_date = FND_API.G_MISS_DATE
1147 AND BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_Rtg_BO
1148 THEN
1149 Error_Handler.Add_Error_Token
1150 ( p_Message_Name => 'BOM_OP_EFFECTDATE_MISSING'
1151 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1152 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1153 , p_Token_Tbl => l_Token_Tbl
1154 );
1155 l_return_status := FND_API.G_RET_STS_ERROR;
1156 END IF ;
1157
1158 END IF ;
1159
1160 --
1161 -- Check if the user is trying to create/update a record with
1162 -- invalid value.
1163 --
1164 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1165 ('Operation Attr Validation: Invalid Value. . . ' || l_return_status) ;
1166 END IF;
1167
1168 -- Start Effective Date
1169 /* Commented entire code for BUG 5282656, as this check is no longer reqd after we started
1170 allowing past effective operations through BUG 4666512.
1171 IF p_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
1172 AND p_com_operation_rec.start_effective_date < SYSDATE -- Changed for bug 2647027
1173 -- AND TRUNC(p_com_operation_rec.start_effective_date) < TRUNC(SYSDATE)
1174 AND nvl(Bom_Globals.get_caller_type(),'') <> 'MIGRATION' -- Bug 2871039
1175 AND BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_Rtg_BO
1176 THEN
1177 IF TRUNC(p_com_operation_rec.start_effective_date) >= TRUNC(SYSDATE) THEN
1178 /*Error_Handler.Add_Error_Token
1179 ( p_Message_Name => 'BOM_OP_EFFECTIVE_DATE_PAST'
1180 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1181 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1182 , p_Token_Tbl => l_Token_Tbl
1183 );
1184
1185 l_return_status := FND_API.G_RET_STS_ERROR;
1186 ELSE Commented for bug 4666512 Also changed the condition to >= instead of <
1187 BOM_RTG_Globals.G_Init_Eff_Date_Op_Num_Flag := TRUE; -- Added for bug 2767019
1188 END IF;
1189 END IF ;
1190 */
1191
1192
1193 -- ACD Type
1194 IF p_com_operation_rec.acd_type IS NOT NULL
1195 AND p_com_operation_rec.acd_type NOT IN
1196 (l_ACD_ADD, l_ACD_CHANGE, l_ACD_DISABLE)
1197 AND BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
1198 THEN
1199
1200 l_token_tbl(2).token_name := 'ACD_TYPE';
1201 l_token_tbl(2).token_value := p_com_operation_rec.acd_type;
1202
1203 Error_Handler.Add_Error_Token
1204 ( p_Message_Name => 'BOM_OP_ACD_TYPE_INVALID'
1205 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1206 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1207 , p_Token_Tbl => l_Token_Tbl
1208 );
1209 l_return_status := FND_API.G_RET_STS_ERROR ;
1210 l_token_tbl.DELETE(2) ;
1211
1212 END IF ;
1213
1214
1215 -- Operation Type
1216 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_FLOW_RTG
1217 THEN
1218 IF (p_com_operation_rec.operation_type IS NULL
1219 OR p_com_operation_rec.operation_type = FND_API.G_MISS_NUM )
1220 OR p_com_operation_rec.operation_type NOT IN (1, 2, 3)
1221 THEN
1222 -- Flow Routing
1223 IF p_com_operation_rec.operation_type <> FND_API.G_MISS_NUM
1224 THEN
1225 l_token_tbl(2).token_name := 'OPERATION_TYPE';
1226 l_token_tbl(2).token_value := p_com_operation_rec.operation_type;
1227
1228 ELSIF p_com_operation_rec.operation_type IS NULL
1229 THEN
1230 l_token_tbl(2).token_name := 'OPERATION_TYPE';
1231 l_token_tbl(2).token_value := '';
1232
1233 END IF ;
1234
1235 Error_Handler.Add_Error_Token
1236 ( p_Message_Name => 'BOM_FLM_OP_OPTYPE_INVALID'
1237 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1238 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1239 , p_Token_Tbl => l_Token_Tbl
1240 );
1241 l_return_status := FND_API.G_RET_STS_ERROR ;
1242 l_token_tbl.DELETE(2) ;
1243
1244 END IF ;
1245 ELSE
1246 -- Non Flow Routing
1247 IF p_com_operation_rec.operation_type IS NOT NULL
1248 AND p_com_operation_rec.operation_type <> FND_API.G_MISS_NUM
1249 AND p_com_operation_rec.operation_type <> 1
1250 THEN
1251
1252 IF p_com_operation_rec.operation_type <> FND_API.G_MISS_NUM
1253 THEN
1254 l_token_tbl(2).token_name := 'OPERATION_TYPE';
1255 l_token_tbl(2).token_value := p_com_operation_rec.operation_type;
1256 END IF ;
1257
1258 Error_Handler.Add_Error_Token
1259 ( p_Message_Name => 'BOM_STD_OP_OPTYPE_INVALID'
1260 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1261 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1262 , p_Token_Tbl => l_Token_Tbl
1263 );
1264 l_return_status := FND_API.G_RET_STS_ERROR ;
1265 l_token_tbl.DELETE(2) ;
1266
1267 END IF ;
1268 END IF ;
1269
1270
1271 -- Operation Sequence Number and New Operation Sequence Number
1272 IF p_com_operation_rec.operation_sequence_number IS NOT NULL
1273 AND( p_com_operation_rec.operation_sequence_number <= 0
1274 OR p_com_operation_rec.operation_sequence_number > 9999
1275 )
1276 THEN
1277
1278 Error_Handler.Add_Error_Token
1279 ( p_Message_Name => 'BOM_OP_SEQNUM_LESSTHAN_ZERO'
1280 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1281 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1282 , p_Token_Tbl => l_Token_Tbl
1283 );
1284 l_return_status := FND_API.G_RET_STS_ERROR ;
1285 END IF;
1286
1287 IF p_com_operation_rec.new_operation_sequence_number IS NOT NULL
1288 AND p_com_operation_rec.new_operation_sequence_number <> FND_API.G_MISS_NUM
1289 AND ( p_com_operation_rec.new_operation_sequence_number <= 0
1290 OR p_com_operation_rec.new_operation_sequence_number > 9999
1291 )
1292 THEN
1293
1294 Error_Handler.Add_Error_Token
1295 ( p_Message_Name => 'BOM_OP_SEQNUM_LESSTHAN_ZERO'
1296 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1297 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1298 , p_Token_Tbl => l_Token_Tbl
1299 );
1300 l_return_status := FND_API.G_RET_STS_ERROR ;
1301 END IF ;
1302
1303 /*
1304 -- Moved to BOMRVIDB.pls
1305 -- Old Operation Sequence Number
1306 */
1307
1308 -- Operation Lead Time Percent
1309 IF p_com_operation_rec.op_lead_time_percent IS NOT NULL
1310 AND p_com_operation_rec.op_lead_time_percent <> FND_API.G_MISS_NUM
1311 AND (p_com_operation_rec.op_lead_time_percent < 0
1312 OR p_com_operation_rec.op_lead_time_percent > 100 )
1313 THEN
1314 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1315 THEN
1316 Error_Handler.Add_Error_Token
1317 ( p_Message_Name => 'BOM_OP_LT_PERCENT_INVALID'
1318 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1319 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1320 , p_Token_Tbl => l_Token_Tbl
1321 ) ;
1322 END IF ;
1323 l_return_status := FND_API.G_RET_STS_ERROR;
1324 END IF ;
1325
1326
1327 -- Minimum Transfer Quantity
1328 IF p_com_operation_rec.minimum_transfer_quantity IS NOT NULL
1329 AND p_com_operation_rec.minimum_transfer_quantity < 0
1330 AND p_com_operation_rec.minimum_transfer_quantity <> FND_API.G_MISS_NUM
1331 THEN
1332 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1333 THEN
1334 Error_Handler.Add_Error_Token
1335 ( p_Message_Name => 'BOM_OP_MINI_TRANS_QTY_INVALID'
1336 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1337 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1338 , p_Token_Tbl => l_Token_Tbl
1339 ) ;
1340 END IF ;
1341 l_return_status := FND_API.G_RET_STS_ERROR;
1342 END IF ;
1343
1344
1345 -- Count Point Type
1346 IF p_com_operation_rec.count_point_type IS NOT NULL
1347 AND p_com_operation_rec.count_point_type NOT IN (1,2,3)
1348 AND p_com_operation_rec.count_point_type <> FND_API.G_MISS_NUM
1349 THEN
1350 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1351 THEN
1352 Error_Handler.Add_Error_Token
1353 ( p_Message_Name => 'BOM_OP_CNTPOINT_TYPE_INVALID'
1354 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1355 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1356 , p_Token_Tbl => l_Token_Tbl
1357 ) ;
1358 END IF ;
1359 l_return_status := FND_API.G_RET_STS_ERROR;
1360 END IF ;
1361
1362
1363
1364 -- Backflush Flag
1365 IF p_com_operation_rec.backflush_flag IS NOT NULL
1366 AND p_com_operation_rec.backflush_flag NOT IN (1,2)
1367 AND p_com_operation_rec.backflush_flag <> FND_API.G_MISS_NUM
1368 THEN
1369 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1370 THEN
1371 Error_Handler.Add_Error_Token
1372 ( p_Message_Name => 'BOM_OP_BKFLUSH_FLAG_INVALID'
1373 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1374 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1375 , p_Token_Tbl => l_Token_Tbl
1376 ) ;
1377 END IF ;
1378 l_return_status := FND_API.G_RET_STS_ERROR;
1379 END IF ;
1380
1381
1382 -- Option Dependent Flag
1383 IF p_com_operation_rec.option_dependent_flag IS NOT NULL
1384 AND p_com_operation_rec.option_dependent_flag NOT IN (1,2)
1385 AND p_com_operation_rec.option_dependent_flag <> FND_API.G_MISS_NUM
1386 THEN
1387 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1388 THEN
1389 Error_Handler.Add_Error_Token
1390 ( p_Message_Name => 'BOM_OP_DPTFLAG_INVALID'
1391 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1392 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1393 , p_Token_Tbl => l_Token_Tbl
1394 ) ;
1395 END IF ;
1396 l_return_status := FND_API.G_RET_STS_ERROR;
1397 END IF ;
1398
1399
1400 -- Reference Flag
1401 IF p_com_operation_rec.reference_flag IS NOT NULL
1402 AND p_com_operation_rec.reference_flag NOT IN (1,2)
1403 AND p_com_operation_rec.reference_flag <> FND_API.G_MISS_NUM
1404 THEN
1405 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1406 THEN
1407 Error_Handler.Add_Error_Token
1408 ( p_Message_Name => 'BOM_OP_REFERENCE_INVALID'
1409 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1410 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1411 , p_Token_Tbl => l_Token_Tbl
1412 ) ;
1413 END IF ;
1414 l_return_status := FND_API.G_RET_STS_ERROR;
1415 END IF ;
1416
1417
1418 -- Include In Rollup
1419 IF p_com_operation_rec.include_in_rollup IS NOT NULL
1420 AND p_com_operation_rec.include_in_rollup NOT IN (1,2)
1421 AND p_com_operation_rec.include_in_rollup <> FND_API.G_MISS_NUM
1422 THEN
1423 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1424 THEN
1425 Error_Handler.Add_Error_Token
1426 ( p_Message_Name => 'BOM_OP_ICDROLLUP_INVALID'
1427 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1428 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1429 , p_Token_Tbl => l_Token_Tbl
1430 ) ;
1431 END IF ;
1432 l_return_status := FND_API.G_RET_STS_ERROR;
1433 END IF ;
1434
1435
1436 -- Op Yield Enabled Flag
1437 IF p_com_operation_rec.op_yield_enabled_flag IS NOT NULL
1438 AND p_com_operation_rec.op_yield_enabled_flag NOT IN (1,2)
1439 AND p_com_operation_rec.op_yield_enabled_flag <> FND_API.G_MISS_NUM
1440 THEN
1441 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1442 THEN
1443 Error_Handler.Add_Error_Token
1444 ( p_Message_Name => 'BOM_OP_YILEDENABLED_INVALID'
1445 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1446 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1447 , p_Token_Tbl => l_Token_Tbl
1448 ) ;
1449 END IF ;
1450 l_return_status := FND_API.G_RET_STS_ERROR;
1451 END IF ;
1452
1453
1454 -- Yield
1455 IF p_com_operation_rec.yield IS NOT NULL AND
1456 (p_com_operation_rec.yield <= 0
1457 OR p_com_operation_rec.yield > 1 )
1458 AND p_com_operation_rec.yield <> FND_API.G_MISS_NUM
1459 THEN
1460 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1461 THEN
1462 Error_Handler.Add_Error_Token
1463 ( p_Message_Name => 'BOM_OP_YIELD_INVALID'
1464 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1465 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1466 , p_Token_Tbl => l_Token_Tbl
1467 ) ;
1468 END IF ;
1469 l_return_status := FND_API.G_RET_STS_ERROR;
1470 END IF ;
1471
1472
1473 -- Cumulative Yield
1474 IF p_com_operation_rec.cumulative_yield IS NOT NULL AND
1475 (p_com_operation_rec.cumulative_yield < 0
1476 OR p_com_operation_rec.cumulative_yield > 1 )
1477 AND p_com_operation_rec.cumulative_yield <> FND_API.G_MISS_NUM
1478 THEN
1479 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1480 THEN
1481 Error_Handler.Add_Error_Token
1482 ( p_Message_Name => 'BOM_OP_CUM_YIELD_INVALID'
1483 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1484 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1485 , p_Token_Tbl => l_Token_Tbl
1486 ) ;
1487 END IF ;
1488 l_return_status := FND_API.G_RET_STS_ERROR;
1489 END IF ;
1490
1491
1492 -- Reverse Cumulative Yield
1493 IF p_com_operation_rec.reverse_cum_yield IS NOT NULL AND
1494 (p_com_operation_rec.reverse_cum_yield < 0
1495 OR p_com_operation_rec.reverse_cum_yield > 1 )
1496 AND p_com_operation_rec.reverse_cum_yield <> FND_API.G_MISS_NUM
1497 THEN
1498 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1499 THEN
1500 Error_Handler.Add_Error_Token
1501 ( p_Message_Name => 'BOM_OP_REVCUM_YIELD_INVALID'
1502 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1503 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1504 , p_Token_Tbl => l_Token_Tbl
1505 ) ;
1506 END IF ;
1507 l_return_status := FND_API.G_RET_STS_ERROR;
1508 END IF ;
1509
1510
1511 -- Net Planning Percent
1512 IF p_com_operation_rec.net_planning_percent IS NOT NULL AND
1513 (p_com_operation_rec.net_planning_percent < 0
1514 OR p_com_operation_rec.net_planning_percent > 100 )
1515 AND p_com_operation_rec.net_planning_percent <> FND_API.G_MISS_NUM
1516 THEN
1517 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1518 THEN
1519 Error_Handler.Add_Error_Token
1520 ( p_Message_Name => 'BOM_FLM_OP_NETPLNPCT_INVALID'
1521 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1522 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1523 , p_Token_Tbl => l_Token_Tbl
1524 ) ;
1525 END IF ;
1526 l_return_status := FND_API.G_RET_STS_ERROR;
1527 END IF ;
1528
1529 -- For eAM enhancement
1530 -- Shutdown Type
1531 IF p_com_operation_rec.shutdown_type IS NOT NULL
1532 AND p_com_operation_rec.shutdown_type <> FND_API.G_MISS_CHAR
1533 AND BOM_Rtg_Globals.Get_Eam_Item_Type = BOM_Rtg_Globals.G_ASSET_ACTIVITY
1534 AND NOT Bom_Rtg_Eam_Util.CheckShutdownType
1535 (p_shutdown_type => p_com_operation_rec.shutdown_type )
1536 THEN
1537 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1538 THEN
1539 Error_Handler.Add_Error_Token
1540 ( p_Message_Name => 'BOM_EAM_SHUTDOWN_TYPE_INVALID'
1541 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1542 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1543 , p_Token_Tbl => l_Token_Tbl
1544 ) ;
1545 END IF ;
1546 l_return_status := FND_API.G_RET_STS_ERROR;
1547 END IF ;
1548
1549 -- BUG 5330942
1550 -- Validation to ensure that a pending routing header cannot be modified without an ECO
1551 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN
1552 Error_Handler.Write_Debug ('Checking if the Routing header is implemented . . .') ;
1553 END IF;
1554
1555 IF BOM_RTG_GLOBALS.Get_Routing_Header_ECN(p_com_op_unexp_rec.Routing_Sequence_Id) IS NOT NULL
1556 AND (p_com_operation_rec.eco_name IS NULL OR p_com_operation_rec.eco_name = FND_API.G_MISS_CHAR)
1557 THEN
1558 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1559 THEN
1560 l_Token_Tbl(1).token_name := 'ALTERNATE';
1561 l_Token_Tbl(1).token_value := nvl(p_com_operation_rec.Alternate_Routing_Code, bom_globals.retrieve_message('BOM', 'BOM_PRIMARY'));
1562 l_Token_Tbl(2).token_name := 'ASSY_ITEM';
1563 l_Token_Tbl(2).token_value := p_com_operation_rec.Revised_Item_Name;
1564 l_Token_Tbl(3).token_name := 'CHANGE_NOTICE';
1565 l_Token_Tbl(3).token_value := BOM_RTG_GLOBALS.Get_Routing_Header_ECN(p_com_op_unexp_rec.Routing_Sequence_Id);
1566
1567 Error_Handler.Add_Error_Token
1568 ( p_Message_Name => 'BOM_RTG_HEADER_UNIMPLEMENTED'
1569 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1570 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1571 , p_Token_Tbl => l_Token_Tbl
1572 ) ;
1573 l_return_status := FND_API.G_RET_STS_ERROR;
1574 END IF ;
1575 END IF;
1576
1577 -- Done validating attributes
1578 IF Bom_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1579 ('Operation Attr Validation completed with return_status: ' || l_return_status) ;
1580 END IF;
1581
1582 x_return_status := l_return_status;
1583 x_mesg_token_tbl := l_Mesg_Token_Tbl;
1584
1585 EXCEPTION
1586 WHEN OTHERS THEN
1587 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1588 ('Some unknown error in Attribute Validation . . .' || SQLERRM );
1589 END IF ;
1590
1591
1592 l_err_text := G_PKG_NAME || ' Validation (Attr. Validation) '
1593 || substrb(SQLERRM,1,200);
1594 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
1595
1596 Error_Handler.Add_Error_Token
1597 ( p_message_name => NULL
1598 , p_message_text => l_err_text
1599 , p_mesg_token_tbl => l_mesg_token_tbl
1600 , x_mesg_token_tbl => l_mesg_token_tbl
1601 ) ;
1602
1603 -- Return the status and message table.
1604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1605 x_mesg_token_tbl := l_mesg_token_tbl ;
1606
1607
1608 END Check_Attributes ;
1609
1610 /******************************************************************
1611 * Procedure : Check_Conditionally_Required used by RTG BO
1612 * Parameters IN : Operation exposed column record
1613 * Operation Unexposed column record
1614 * Parameters out: Mesg Token Table
1615 * Return Status
1616 * Purpose : Convert Routing Operation to Common Operation and
1617 * Call Check_Required for Common.
1618 *
1619 *********************************************************************/
1620 PROCEDURE Check_Conditionally_Required
1621 ( p_operation_rec IN Bom_Rtg_Pub.Operation_Rec_Type
1622 , p_op_unexp_rec IN Bom_Rtg_Pub.Op_Unexposed_Rec_Type
1623 , x_return_status IN OUT NOCOPY VARCHAR2
1624 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1625 )
1626
1627 IS
1628 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
1629 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
1630
1631 BEGIN
1632 -- Convert Routing Operation to Common Operation
1633 Bom_Rtg_Pub.Convert_RtgOp_To_ComOp
1634 ( p_rtg_operation_rec => p_operation_rec
1635 , p_rtg_op_unexp_rec => p_op_unexp_rec
1636 , x_com_operation_rec => l_com_operation_rec
1637 , x_com_op_unexp_rec => l_com_op_unexp_rec
1638 ) ;
1639
1640 Bom_Validate_Op_Seq.Check_Conditionally_Required
1641 ( p_com_operation_rec => l_com_operation_rec
1642 , p_com_op_unexp_rec => l_com_op_unexp_rec
1643 , x_return_status => x_return_status
1644 , x_mesg_token_tbl => x_mesg_token_tbl
1645 ) ;
1646
1647 END Check_Conditionally_Required ;
1648
1649 /******************************************************************
1650 * Procedure : Check_Conditionally_Required used by ECO BO
1651 * Parameters IN : Revised Operation exposed column record
1652 * Revised Operation Unexposed column record
1653 * Parameters out: Mesg Token Table
1654 * Return Status
1655 * Purpose : Convert Revised Operation to Common Operation and
1656 * Call Check_Required for Common.
1657 *
1658 *********************************************************************/
1659 PROCEDURE Check_Conditionally_Required
1660 ( p_rev_operation_rec IN Bom_Rtg_Pub.Rev_Operation_Rec_Type
1661 , p_rev_op_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
1662 , x_return_status IN OUT NOCOPY VARCHAR2
1663 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1664 )
1665
1666 IS
1667 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
1668 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
1669
1670 BEGIN
1671 -- Convert Revised Operation to Common Operation
1672 Bom_Rtg_Pub.Convert_EcoOp_To_ComOp
1673 ( p_rev_operation_rec => p_rev_operation_rec
1674 , p_rev_op_unexp_rec => p_rev_op_unexp_rec
1675 , x_com_operation_rec => l_com_operation_rec
1676 , x_com_op_unexp_rec => l_com_op_unexp_rec
1677 ) ;
1678
1679 Bom_Validate_Op_Seq.Check_Conditionally_Required
1680 ( p_com_operation_rec => l_com_operation_rec
1681 , p_com_op_unexp_rec => l_com_op_unexp_rec
1682 , x_return_status => x_return_status
1683 , x_mesg_token_tbl => x_mesg_token_tbl
1684 ) ;
1685
1686 END Check_Conditionally_Required ;
1687
1688 /*****************************************************************
1689 * Procedure : Check_Conditionally_Required for Common
1690 * internally called by RTG BO and ECO BO
1691 * Parameters IN : Common Operation exposed column record
1692 * Common Operation Unexposed column record
1693 * Paramaters out: Return Status
1694 * Mesg Token Table
1695 * Purpose : Check Conditionally Required Columns
1696 *
1697 ********************************************************************/
1698 PROCEDURE Check_Conditionally_Required
1699 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
1700 , p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
1701 , x_return_status IN OUT NOCOPY VARCHAR2
1702 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1703 )
1704 IS
1705
1706 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
1707 l_err_text VARCHAR(2000) ;
1708 l_token_tbl Error_Handler.Token_Tbl_Type;
1709
1710 BEGIN
1711 x_return_status := FND_API.G_RET_STS_SUCCESS;
1712 l_Token_Tbl(1).token_name := 'OP_SEQ_NUMBER';
1713 l_Token_Tbl(1).token_value := p_com_operation_rec.operation_sequence_number ;
1714
1715 /* Moved to Check_Required
1716 -- Standard Operation Id and Operation Type
1717 */
1718
1719 -- Return the message table.
1720 x_mesg_token_tbl := l_Mesg_Token_Tbl ;
1721
1722
1723 EXCEPTION
1724 WHEN OTHERS THEN
1725
1726 l_err_text := G_PKG_NAME || ' Validation (Check Conditionally Required) '
1727 || substrb(SQLERRM,1,200);
1728 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
1729
1730 Error_Handler.Add_Error_Token
1731 ( p_message_name => NULL
1732 , p_message_text => l_err_text
1733 , p_mesg_token_tbl => l_mesg_token_tbl
1734 , x_mesg_token_tbl => l_mesg_token_tbl
1735 ) ;
1736
1737 -- Return the status and message table.
1738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1739 x_mesg_token_tbl := l_mesg_token_tbl ;
1740
1741 END Check_Conditionally_Required ;
1742
1743
1744 /******************************************************************
1745 * Procedure : Check_NonOperated_Attribute used by RTG BO
1746 * Parameters IN : Operation exposed column record
1747 * Operation unexposed column record
1748 * Parameters out: Operation exposed column record
1749 * Operation unexposed column record
1750 * Mesg Token Table
1751 * Return Status
1752 * Purpose : Convert Routing Operation to Common Operation and
1753 * Call Check_NonOperated_Attribute for common opeartion
1754 * After calling Check_NonOperated_Attribute,
1755 * convert Common record back to Routing Operation Record
1756 *
1757 *********************************************************************/
1758 PROCEDURE Check_NonOperated_Attribute
1759 ( p_operation_rec IN Bom_Rtg_Pub.Operation_Rec_Type
1760 , p_op_unexp_rec IN Bom_Rtg_Pub.Op_Unexposed_Rec_Type
1761 , x_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Operation_Rec_Type
1762 , x_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Unexposed_Rec_Type
1763 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1764 , x_return_status IN OUT NOCOPY VARCHAR2
1765 )
1766 IS
1767 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
1768 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
1769
1770 BEGIN
1771 -- Convert Routing Operation to Common Operation
1772 Bom_Rtg_Pub.Convert_RtgOp_To_ComOp
1773 ( p_rtg_operation_rec => p_operation_rec
1774 , p_rtg_op_unexp_rec => p_op_unexp_rec
1775 , x_com_operation_rec => l_com_operation_rec
1776 , x_com_op_unexp_rec => l_com_op_unexp_rec
1777 ) ;
1778
1779
1780 -- Call Check_NonOperated_Attribute
1781 Bom_Validate_Op_Seq.Check_NonOperated_Attribute
1782 ( p_com_operation_rec => l_com_operation_rec
1783 , p_com_op_unexp_rec => l_com_op_unexp_rec
1784 , x_com_operation_rec => l_com_operation_rec
1785 , x_com_op_unexp_rec => l_com_op_unexp_rec
1786 , x_return_status => x_return_status
1787 , x_mesg_token_tbl => x_mesg_token_tbl
1788 ) ;
1789
1790 -- Convert old Eco Opeartion Record back to Routing Operation
1791 Bom_Rtg_Pub.Convert_ComOp_To_RtgOp
1792 ( p_com_operation_rec => l_com_operation_rec
1793 , p_com_op_unexp_rec => l_com_op_unexp_rec
1794 , x_rtg_operation_rec => x_operation_rec
1795 , x_rtg_op_unexp_rec => x_op_unexp_rec
1796 ) ;
1797
1798 END Check_NonOperated_Attribute ;
1799
1800
1801 /******************************************************************
1802 * Procedure : Check_NonOperated_Attribute used by ECO BO
1803 * Parameters IN : Revised Operation exposed column record
1804 * Revised Operation unexposed column record
1805 * Parameters out: Revised Operation exposed column record
1806 * Revised Operation unexposed column record
1807 * Mesg Token Table
1808 * Return Status
1809 * Purpose : Convert Revised Operation to Common Operation and
1810 * Call Check_NonOperated_Attribute for common opeartion
1811 * After calling Check_NonOperated_Attribute,
1812 * convert Common record back to Revised Operation Record
1813 *
1814 *********************************************************************/
1815 PROCEDURE Check_NonOperated_Attribute
1816 ( p_rev_operation_rec IN Bom_Rtg_Pub.Rev_Operation_Rec_Type
1817 , p_rev_op_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
1818 , x_rev_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Operation_Rec_Type
1819 , x_rev_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
1820 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1821 , x_return_status IN OUT NOCOPY VARCHAR2
1822 )
1823 IS
1824 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
1825 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
1826
1827 BEGIN
1828 -- Convert Revised Operation to Common Operation
1829 Bom_Rtg_Pub.Convert_EcoOp_To_ComOp
1830 ( p_rev_operation_rec => p_rev_operation_rec
1831 , p_rev_op_unexp_rec => p_rev_op_unexp_rec
1832 , x_com_operation_rec => l_com_operation_rec
1833 , x_com_op_unexp_rec => l_com_op_unexp_rec
1834 ) ;
1835
1836 -- Call Check_NonOperated_Attribute
1837 Bom_Validate_Op_Seq.Check_NonOperated_Attribute
1838 ( p_com_operation_rec => l_com_operation_rec
1839 , p_com_op_unexp_rec => l_com_op_unexp_rec
1840 , x_com_operation_rec => l_com_operation_rec
1841 , x_com_op_unexp_rec => l_com_op_unexp_rec
1842 , x_return_status => x_return_status
1843 , x_mesg_token_tbl => x_mesg_token_tbl
1844 ) ;
1845
1846 -- Convert the Common record to Revised Operation record
1847 Bom_Rtg_Pub.Convert_ComOp_To_EcoOp
1848 ( p_com_operation_rec => l_com_operation_rec
1849 , p_com_op_unexp_rec => l_com_op_unexp_rec
1850 , x_rev_operation_rec => x_rev_operation_rec
1851 , x_rev_op_unexp_rec => x_rev_op_unexp_rec
1852 ) ;
1853
1854 END Check_NonOperated_Attribute ;
1855
1856
1857 /******************************************************************
1858 * Procedure : Check_NonOperated_Attribute for Common
1859 * internally called by RTG BO and ECO BO
1860 * Parameters IN : Common Operation exposed column record
1861 * Common Operation unexposed column record
1862 * Parameters out: Common Operation exposed column record
1863 * Common Operation unexposed column record
1864 * Mesg Token Table
1865 * Return Status
1866 * Purpose : Check_NonOperated_Attribute will check parent routing's
1867 * CFM Routing Flag and if non-operated column is null.
1868 * If so, the procedure set the values to Null and
1869 * and warning messages
1870 *
1871 *********************************************************************/
1872 PROCEDURE Check_NonOperated_Attribute
1873 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
1874 , p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
1875 , x_com_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Operation_Rec_Type
1876 , x_com_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
1877 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1878 , x_return_status IN OUT NOCOPY VARCHAR2
1879 )
1880 IS
1881
1882 l_return_status VARCHAR2(1);
1883 l_err_text VARCHAR2(2000) ;
1884 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1885 l_token_tbl Error_Handler.Token_Tbl_Type;
1886
1887 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
1888 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
1889
1890 BEGIN
1891
1892 l_return_status := FND_API.G_RET_STS_SUCCESS;
1893 x_return_status := FND_API.G_RET_STS_SUCCESS;
1894
1895 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1896 ('Peforming the Operation Check NonOperated Attr . . .') ;
1897 END IF ;
1898
1899 -- Initialize operation exp and unexp record
1900 l_com_operation_rec := p_com_operation_rec ;
1901 l_com_op_unexp_rec := p_com_op_unexp_rec ;
1902
1903 -- Set the first token to be equal to the operation sequence number
1904 l_Token_Tbl(1).token_name := 'OP_SEQ_NUMBER';
1905 l_Token_Tbl(1).token_value := p_com_operation_rec.operation_sequence_number ;
1906
1907 --
1908 -- Following Fields are available in only Flow Routing.
1909 --
1910 -- Start Effective Date and Disable Date
1911 IF ( BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_FLOW_RTG
1912 AND l_com_operation_rec.operation_type IN (l_PROCESS, l_LINE_OP))
1913 AND((l_com_operation_rec.start_effective_date IS NOT NULL OR
1914 l_com_operation_rec.start_effective_date <> FND_API.G_MISS_DATE)
1915 OR
1916 (l_com_operation_rec.disable_date IS NOT NULL OR
1917 l_com_operation_rec.disable_date <> FND_API.G_MISS_DATE)
1918 OR
1919 (l_com_operation_rec.New_Start_Effective_Date IS NOT NULL OR
1920 l_com_operation_rec.New_Start_Effective_Date <> FND_API.G_MISS_DATE)
1921 )
1922 THEN
1923
1924 l_com_operation_rec.start_effective_date := '' ;
1925 l_com_operation_rec.new_start_effective_date := '' ;
1926 l_com_operation_rec.disable_date := '' ;
1927
1928 Error_Handler.Add_Error_Token
1929 ( p_message_name => 'BOM_FLOW_OP_DATE_IGNORED'
1930 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1931 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1932 , p_Token_Tbl => l_Token_Tbl
1933 , p_message_type => 'W'
1934 ) ;
1935 END IF ;
1936
1937
1938 -- Process Seq Num and Code
1939 IF ( BOM_Rtg_Globals.Get_CFM_Rtg_Flag <> BOM_Rtg_Globals.G_FLOW_RTG
1940 OR (BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_FLOW_RTG
1941 AND l_com_operation_rec.operation_type IN (l_PROCESS, l_LINE_OP))
1942 )
1943 AND
1944 (l_com_operation_rec.process_seq_number IS NOT NULL OR
1945 l_com_operation_rec.process_seq_number <> FND_API.G_MISS_NUM)
1946 THEN
1947
1948 l_com_operation_rec.process_seq_number := '' ;
1949 l_com_op_unexp_rec.Process_Op_Seq_Id := '' ;
1950
1951 Error_Handler.Add_Error_Token
1952 ( p_message_name => 'BOM_STD_OP_PRCSEQNUM_IGNORED'
1953 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1954 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1955 , p_Token_Tbl => l_Token_Tbl
1956 , p_message_type => 'W'
1957 ) ;
1958 END IF ;
1959
1960 IF ( BOM_Rtg_Globals.Get_CFM_Rtg_Flag <> BOM_Rtg_Globals.G_FLOW_RTG
1961 OR (BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_FLOW_RTG
1962 AND l_com_operation_rec.operation_type IN (l_PROCESS, l_LINE_OP))
1963 )
1964 AND
1965 (l_com_operation_rec.process_code IS NOT NULL OR
1966 l_com_operation_rec.process_code <> FND_API.G_MISS_CHAR)
1967 THEN
1968
1969 l_com_operation_rec.process_code := '' ;
1970 l_com_op_unexp_rec.Process_Op_Seq_Id := '' ;
1971
1972 Error_Handler.Add_Error_Token
1973 ( p_message_name => 'BOM_STD_OP_PRCCODE_IGNORED'
1974 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1975 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1976 , p_Token_Tbl => l_Token_Tbl
1977 , p_message_type => 'W'
1978 ) ;
1979 END IF ;
1980
1981 -- Line Op Num and Code
1982 IF ( BOM_Rtg_Globals.Get_CFM_Rtg_Flag <> BOM_Rtg_Globals.G_FLOW_RTG
1983 OR (BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_FLOW_RTG
1984 AND l_com_operation_rec.operation_type IN (l_PROCESS, l_LINE_OP))
1985 )
1986 AND
1987 (l_com_operation_rec.line_op_seq_number IS NOT NULL OR
1988 l_com_operation_rec.line_op_seq_number <> FND_API.G_MISS_NUM)
1989 THEN
1990
1991 l_com_operation_rec.line_op_seq_number := '' ;
1992 l_com_op_unexp_rec.line_op_seq_Id := '' ;
1993
1994 Error_Handler.Add_Error_Token
1995 ( p_message_name => 'BOM_STD_OP_LNOPSEQNUM_IGNORED'
1996 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1997 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1998 , p_Token_Tbl => l_Token_Tbl
1999 , p_message_type => 'W'
2000 ) ;
2001 END IF ;
2002
2003 IF ( BOM_Rtg_Globals.Get_CFM_Rtg_Flag <> BOM_Rtg_Globals.G_FLOW_RTG
2004 OR (BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_FLOW_RTG
2005 AND l_com_operation_rec.operation_type IN (l_PROCESS, l_LINE_OP))
2006 )
2007 AND
2008 ( l_com_operation_rec.line_op_code IS NOT NULL OR
2009 l_com_operation_rec.line_op_code <> FND_API.G_MISS_CHAR )
2010 THEN
2011
2012 l_com_operation_rec.line_op_code := '' ;
2013 l_com_op_unexp_rec.line_op_seq_Id := '' ;
2014
2015 Error_Handler.Add_Error_Token
2016 ( p_message_name => 'BOM_STD_OP_LNOPCODE_IGNORED'
2017 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2018 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2019 , p_Token_Tbl => l_Token_Tbl
2020 , p_message_type => 'W'
2021 ) ;
2022 END IF ;
2023
2024
2025 -- User_Labor_Time
2026 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag <> BOM_Rtg_Globals.G_FLOW_RTG AND
2027 ( l_com_operation_rec.user_labor_time <> 0 AND
2028 (l_com_operation_rec.user_labor_time IS NOT NULL OR
2029 l_com_operation_rec.user_labor_time <> FND_API.G_MISS_NUM ))
2030 THEN
2031
2032 l_com_operation_rec.user_labor_time := '' ;
2033
2034 Error_Handler.Add_Error_Token
2035 ( p_message_name => 'BOM_STD_OP_USERLABTIME_IGNORED'
2036 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2037 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2038 , p_Token_Tbl => l_Token_Tbl
2039 , p_message_type => 'W'
2040 ) ;
2041 END IF ;
2042
2043 -- User_Machine_Time
2044 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag <> BOM_Rtg_Globals.G_FLOW_RTG AND
2045 ( l_com_operation_rec.user_machine_time <> 0 AND
2046 (l_com_operation_rec.user_machine_time IS NOT NULL OR
2047 l_com_operation_rec.user_machine_time <> FND_API.G_MISS_NUM ))
2048 THEN
2049
2050 l_com_operation_rec.user_machine_time := '' ;
2051
2052 Error_Handler.Add_Error_Token
2053 ( p_message_name => 'BOM_STD_OP_USERMCTIME_IGNORED'
2054 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2055 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2056 , p_Token_Tbl => l_Token_Tbl
2057 , p_message_type => 'W'
2058 ) ;
2059 END IF ;
2060
2061
2062 /* Comment Out Following calc op times columns adn user elapsed time
2063 -- Calculated_Labor_Time
2064 -- Calculated_Machine_Time
2065 -- Calculated_Elapsed_Time
2066 -- User_Elapsed_Time
2067 */ -- End of Comment out
2068
2069
2070 -- Net_Planning_Percent
2071 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_STD_RTG AND
2072 ( l_com_operation_rec.net_planning_percent IS NOT NULL OR
2073 l_com_operation_rec.net_planning_percent <> FND_API.G_MISS_NUM )
2074 THEN
2075
2076 l_com_operation_rec.net_planning_percent := '' ;
2077
2078 Error_Handler.Add_Error_Token
2079 ( p_message_name => 'BOM_STD_OP_NETPLNPCT_IGNORED'
2080 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2081 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2082 , p_Token_Tbl => l_Token_Tbl
2083 , p_message_type => 'W'
2084 ) ;
2085 END IF ;
2086
2087
2088 --
2089 -- Following Fields are available in only Lot Based Routing.
2090 --
2091
2092 -- Include_In_Rollup
2093 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag NOT IN (BOM_Rtg_Globals.G_LOT_RTG, BOM_Rtg_Globals.G_STD_RTG) AND
2094 /*Bug 6523550 even for standard routings the value shouldnt be defaulted to 1*/
2095 ( l_com_operation_rec.include_in_rollup IS NOT NULL OR
2096 l_com_operation_rec.include_in_rollup <> FND_API.G_MISS_NUM )
2097 AND l_com_operation_rec.include_in_rollup <> 1 -- Not default. Bug1744254
2098 THEN
2099
2100 l_com_operation_rec.include_in_rollup := 1 ; -- Set default 1. Bug1744254
2101
2102 Error_Handler.Add_Error_Token
2103 ( p_message_name => 'BOM_STD_OP_ICDROLLUP_IGNORED'
2104 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2105 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2106 , p_Token_Tbl => l_Token_Tbl
2107 , p_message_type => 'W'
2108 ) ;
2109 END IF ;
2110
2111 -- Op_Yield_Enabled_Flag
2112 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag <> BOM_Rtg_Globals.G_LOT_RTG AND
2113 ( l_com_operation_rec.op_yield_enabled_flag IS NOT NULL OR
2114 l_com_operation_rec.op_yield_enabled_flag <> FND_API.G_MISS_NUM )
2115 AND l_com_operation_rec.op_yield_enabled_flag <> 1 -- Not default. Bug1744254
2116 THEN
2117
2118 l_com_operation_rec.op_yield_enabled_flag := 1 ; -- Set default 1. Bug1744254
2119
2120 Error_Handler.Add_Error_Token
2121 ( p_message_name => 'BOM_STD_OP_YLD_ENABLED_IGNORED'
2122 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2123 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2124 , p_Token_Tbl => l_Token_Tbl
2125 , p_message_type => 'W'
2126 ) ;
2127 END IF ;
2128
2129
2130 --
2131 -- Following Fields are not available in Flow Routing.
2132 -- And In Std Routing, The Values in Yield and Cum Yield would be
2133 -- ignored if Profile MRP: i2 Check if FP installed is not Yes.
2134 --
2135
2136
2137 /* Comment Out because followings are based on Routing OIF program
2138 -- Yield
2139 -- Cumulative_Yield
2140 -- Reverse_CUM_Yield
2141 -- End of Comment Out */
2142
2143 -- Yield
2144 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_STD_RTG AND
2145 ( l_com_operation_rec.yield IS NOT NULL OR
2146 l_com_operation_rec.yield <> FND_API.G_MISS_NUM )
2147 THEN
2148 /*IF NVL(FND_PROFILE.VALUE('MRP_I2_P_CHECK_FOR_FP'), 'N') <> 'Y' -- BUG 4729535
2149 THEN
2150 l_com_operation_rec.yield := '' ;
2151
2152 Error_Handler.Add_Error_Token
2153 ( p_message_name => 'BOM_STD_OP_YIELD_IGNORED'
2154 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2155 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2156 , p_Token_Tbl => l_Token_Tbl
2157 , p_message_type => 'W'
2158 ) ;
2159 END IF ;*/
2160 NULL;
2161
2162
2163 END IF ;
2164
2165 -- Cumulative_Yield
2166 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_STD_RTG AND
2167 ( l_com_operation_rec.cumulative_yield IS NOT NULL OR
2168 l_com_operation_rec.cumulative_yield <> FND_API.G_MISS_NUM )
2169 THEN
2170 NULL;
2171 /*IF NVL(FND_PROFILE.VALUE('MRP_I2_P_CHECK_FOR_FP'), 'N') <> 'Y' -- BUG 4729535
2172 THEN
2173 l_com_operation_rec.cumulative_yield := '' ;
2174
2175 Error_Handler.Add_Error_Token
2176 ( p_message_name => 'BOM_STD_OP_CUM_YIELD_IGNORED'
2177 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2178 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2179 , p_Token_Tbl => l_Token_Tbl
2180 , p_message_type => 'W'
2181 ) ;
2182 END IF ;*/
2183 END IF ;
2184
2185 -- Reverse_CUM_Yield
2186 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_STD_RTG AND
2187 ( l_com_operation_rec.reverse_cum_yield IS NOT NULL OR
2188 l_com_operation_rec.reverse_cum_yield <> FND_API.G_MISS_NUM )
2189 THEN
2190
2191 l_com_operation_rec.reverse_cum_yield := '' ;
2192
2193 Error_Handler.Add_Error_Token
2194 ( p_message_name => 'BOM_STD_OP_REVCUM_YLD_IGNORED'
2195 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2196 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2197 , p_Token_Tbl => l_Token_Tbl
2198 , p_message_type => 'W'
2199 ) ;
2200 END IF ;
2201
2202
2203 -- For eAM enhancement
2204 -- Followings are not operated in maintenance routings for eAM
2205 -- and Shutdown Type is only for maintenance routings for eAM
2206 IF BOM_Rtg_Globals.Get_Eam_Item_Type = BOM_Rtg_Globals.G_ASSET_ACTIVITY
2207 THEN
2208 -- BACKFLUSH_FLAG and MINIMUM_TRANSFER_QUANTITY
2209 -- are not enterable in maintenance routings for eAM
2210
2211 IF ( NVL(l_com_operation_rec.Minimum_Transfer_Quantity,0) <> 0 OR
2212 l_com_operation_rec.Minimum_Transfer_Quantity <> FND_API.G_MISS_NUM )
2213 OR ( NVL(l_com_operation_rec.Backflush_Flag,1) <> 1 OR
2214 l_com_operation_rec.Backflush_Flag <> FND_API.G_MISS_NUM )
2215 THEN
2216
2217 l_com_operation_rec.Minimum_Transfer_Quantity := 0 ;
2218 l_com_operation_rec.Backflush_Flag := 1 ;
2219
2220 Error_Handler.Add_Error_Token
2221 ( p_message_name => 'BOM_EAM_WIP_ATTR_IGNORED'
2222 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2223 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2224 , p_Token_Tbl => l_Token_Tbl
2225 , p_message_type => 'W'
2226 ) ;
2227
2228 END IF ;
2229
2230 ELSE
2231
2232 -- Shutdown Type is not operated in other routings
2233 IF ( l_com_operation_rec.shutdown_type IS NOT NULL OR
2234 l_com_operation_rec.shutdown_Type <> FND_API.G_MISS_CHAR )
2235 THEN
2236
2237 l_com_operation_rec.shutdown_Type := '' ;
2238
2239 Error_Handler.Add_Error_Token
2240 ( p_message_name => 'BOM_EAM_SHUTDOWN_TYPE_IGNORED'
2241 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2242 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2243 , p_Token_Tbl => l_Token_Tbl
2244 , p_message_type => 'W'
2245 ) ;
2246
2247
2248 END IF ;
2249 --- Added for long description project (Bug 2689249)
2250 -- Long description is not used by other routings
2251 IF ( l_com_operation_rec.long_description IS NOT NULL OR
2252 l_com_operation_rec.long_description <> FND_API.G_MISS_CHAR )
2253 THEN
2254
2255 l_com_operation_rec.long_description := '' ;
2256 Error_Handler.Add_Error_Token
2257 ( p_message_name => 'BOM_EAM_LONG_DESC_IGNORED'
2258 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2259 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2260 , p_Token_Tbl => l_Token_Tbl
2261 , p_message_type => 'W'
2262 ) ;
2263
2264 END IF ;
2265
2266 END IF ;
2267
2268
2269 -- Return operation exp and unexp record
2270 x_com_operation_rec := l_com_operation_rec ;
2271 x_com_op_unexp_rec := l_com_op_unexp_rec ;
2272
2273 -- Return the status and message table.
2274 x_return_status := l_return_status ;
2275 x_mesg_token_tbl := l_mesg_token_tbl ;
2276
2277 EXCEPTION
2278 WHEN OTHERS THEN
2279 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2280 ('Some unknown error in Check NonOperated Attr. . . .' || SQLERRM );
2281 END IF ;
2282
2283
2284 l_err_text := G_PKG_NAME || ' Validation (NonOperated Attr.) '
2285 || substrb(SQLERRM,1,200);
2286 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
2287
2288 Error_Handler.Add_Error_Token
2289 ( p_message_name => NULL
2290 , p_message_text => l_err_text
2291 , p_mesg_token_tbl => l_mesg_token_tbl
2292 , x_mesg_token_tbl => l_mesg_token_tbl
2293 ) ;
2294
2295 -- Return the status and message table.
2296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2297 x_mesg_token_tbl := l_mesg_token_tbl ;
2298
2299 -- Return operation exp and unexp record
2300 x_com_operation_rec := l_com_operation_rec ;
2301 x_com_op_unexp_rec := l_com_op_unexp_rec ;
2302
2303
2304 END Check_NonOperated_Attribute ;
2305
2306
2307 /******************************************************************
2308 * Procedure : Check_Ref_Std_Operation internally
2309 * called by RTG BO and by ECO BO
2310 * Parameters IN : Common Operation exposed column record
2311 * Common Operation unexposed column record
2312 * Common Old Operation exposed column record
2313 * Common Old Operation unexposed column record
2314 * Parameters out: Return Status
2315 * Message Token Table
2316 * Purpose : Check_Ref_Operation validate Reference flag and
2317 * Standard Operation. If Std Op is not null
2318 * Set Standard Operation infor to exposed and unexposed
2319 * coulumns regarding reference flag
2320 **********************************************************************/
2321 PROCEDURE Check_Ref_Std_Operation
2322 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
2323 , p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
2324 , p_old_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
2325 , p_old_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
2326 , p_control_rec IN Bom_Rtg_Pub.Control_Rec_Type
2327 , x_com_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Operation_Rec_Type
2328 , x_com_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
2329 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2330 , x_return_status IN OUT NOCOPY VARCHAR2
2331 )
2332 IS
2333 -- Variables
2334 --l_std_op_found BOOLEAN ; -- Indicate Std OP is found
2335 l_copy_std_op BOOLEAN ; -- Indicate Copy Std Op has been proceeded
2336 l_std_op_invalid BOOLEAN ; -- Indicate Std OP is invalid
2337
2338 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type;
2339 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type;
2340
2341 -- Error Handlig Variables
2342 l_return_status VARCHAR2(1);
2343 l_err_text VARCHAR2(2000) ;
2344 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
2345 l_token_tbl Error_Handler.Token_Tbl_Type;
2346
2347 -- Exception
2348 EXIT_CHECK_REF_STD_OP EXCEPTION ;
2349
2350 -- Check if the operatin already has resources
2351 CURSOR l_exist_res_cur (p_op_seq_id NUMBER)
2352 IS
2353 SELECT 'Resource Exist'
2354 FROM DUAL
2355 WHERE EXISTS ( SELECT NULL
2356 FROM BOM_OPERATION_RESOURCES
2357 WHERE operation_sequence_id = p_op_seq_id
2358 ) ;
2359
2360 CURSOR c_std_sub_res (p_std_op_id NUMBER)
2361 IS
2362 SELECT 'Sub Resources Exist'
2363 FROM DUAL
2364 WHERE EXISTS (SELECT NULL
2365 FROM BOM_STD_SUB_OP_RESOURCES
2366 WHERE standard_operation_id = p_std_op_id
2367 AND Schedule_Seq_Num IS NULL); -- Bug 7370692
2368
2369 -- Get Standard Operation Info.
2370 CURSOR l_stdop_csr( p_std_op_id NUMBER
2371 , p_op_type NUMBER
2372 , p_rtg_seq_id NUMBER
2373 , p_org_id NUMBER
2374 , p_dept_id NUMBER
2375 , p_rit_seq_id NUMBER
2376 )
2377 IS
2378 SELECT 'Std Op Invalid'
2379 FROM DUAL
2380 WHERE NOT EXISTS (SELECT NULL
2381 FROM BOM_STANDARD_OPERATIONS bso
2382 , bom_operational_routings bor
2383 WHERE NVL(bso.operation_type,1 )
2384 = DECODE( p_op_type, FND_API.G_MISS_NUM, 1
2385 , NVL(p_op_type, 1))
2386 AND NVL(bso.line_id, FND_API.G_MISS_NUM)
2387 = NVL(bor.line_id, FND_API.G_MISS_NUM)
2388 AND bor.routing_sequence_id = p_rtg_seq_id
2389 AND bso.department_id = p_dept_id
2390 AND bso.organization_id = p_org_id
2391 AND bso.standard_operation_id = p_std_op_id
2392 UNION
2393 SELECT NULL
2394 FROM BOM_STANDARD_OPERATIONS bso
2395 , ENG_REVISED_ITEMS eri
2396 WHERE NVL(bso.operation_type, 1)
2397 = DECODE( p_op_type, FND_API.G_MISS_NUM, 1
2398 , NVL(p_op_type, 1 ) )
2399 -- AND NVL(bso.line_id, FND_API.G_MISS_NUM)
2400 -- = NVL(eri.line_id, FND_API.G_MISS_NUM)
2401 -- AND BOM_Rtg_Globals.Get_Routing_Sequence_Id IS NULL
2402 AND NOT EXISTS -- Added for bug 3578057, to check if it is a new routing being created through the ECO
2403 (SELECT 1 FROM BOM_OPERATIONAL_ROUTINGS
2404 WHERE routing_sequence_id = p_rtg_seq_id)
2405 AND eri.revised_item_sequence_id = p_rit_seq_id
2406 AND bso.department_id = p_dept_id
2407 AND bso.organization_id = p_org_id
2408 AND bso.standard_operation_id = p_std_op_id
2409 ) ;
2410 BEGIN
2411
2412 -- Initialize operation exp and unexp record
2413 l_com_operation_rec := p_com_operation_rec ;
2414 l_com_op_unexp_rec := p_com_op_unexp_rec ;
2415
2416 -- Set the first token to be equal to the operation sequence number
2417 l_Token_Tbl(1).token_name := 'OP_SEQ_NUMBER';
2418 l_Token_Tbl(1).token_value := p_com_operation_rec.operation_sequence_number ;
2419
2420 l_return_status := FND_API.G_RET_STS_SUCCESS;
2421
2422 --
2423 -- Standard Operation is not updatable
2424 --
2425 IF ( l_com_operation_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_UPDATE
2426 OR ( l_com_operation_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
2427 AND l_com_operation_rec.acd_type = l_ACD_CHANGE )
2428 )
2429 AND ( NVL(p_old_com_op_unexp_rec.standard_operation_id, FND_API.G_MISS_NUM ) <>
2430 NVL(l_com_op_unexp_rec.standard_operation_id, FND_API.G_MISS_NUM )
2431 )
2432 THEN
2433 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2434 THEN
2435
2436 Error_Handler.Add_Error_Token
2437 ( p_message_name => 'BOM_OP_STD_OP_NOTUPDATABLE'
2438 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2439 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2440 , p_Token_Tbl => l_Token_Tbl
2441 ) ;
2442 END IF ;
2443 l_return_status := FND_API.G_RET_STS_ERROR ;
2444
2445 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2446 ('Std operation is not updatable. Return Status is '|| l_return_status ) ;
2447 END IF ;
2448
2449 RAISE EXIT_CHECK_REF_STD_OP ;
2450
2451 END IF ;
2452
2453 --
2454 -- Entities of a referenced operation is not updatable -- Added for bug 2762681
2455 --
2456 IF ( l_com_operation_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_UPDATE
2457 OR ( l_com_operation_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
2458 AND l_com_operation_rec.acd_type = l_ACD_CHANGE )
2459 )
2460 AND ( l_com_operation_rec.reference_flag = 1
2461 )
2462 THEN
2463 IF (nvl(p_old_com_operation_rec.Minimum_Transfer_Quantity,-1) <>
2464 nvl(l_com_operation_rec.Minimum_Transfer_Quantity,-1))
2465 THEN
2466 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2467 THEN
2468
2469 Error_Handler.Add_Error_Token
2470 ( p_message_name => 'BOM_STD_MTQ_NOT_UPDATABLE'
2471 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2472 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2473 , p_Token_Tbl => l_Token_Tbl
2474 ) ;
2475 END IF ;
2476 l_return_status := FND_API.G_RET_STS_ERROR ;
2477 END IF;
2478
2479 IF (p_old_com_operation_rec.Count_Point_Type <> l_com_operation_rec.Count_Point_Type)
2480 THEN
2481 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2482 THEN
2483
2484 Error_Handler.Add_Error_Token
2485 ( p_message_name => 'BOM_STD_CPT_NOT_UPDATABLE'
2486 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2487 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2488 , p_Token_Tbl => l_Token_Tbl
2489 ) ;
2490 END IF ;
2491 l_return_status := FND_API.G_RET_STS_ERROR ;
2492 END IF;
2493
2494 IF (p_old_com_operation_rec.Backflush_Flag <> l_com_operation_rec.Backflush_Flag)
2495 THEN
2496 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2497 THEN
2498
2499 Error_Handler.Add_Error_Token
2500 ( p_message_name => 'BOM_STD_BFF_NOT_UPDATABLE'
2501 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2502 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2503 , p_Token_Tbl => l_Token_Tbl
2504 ) ;
2505 END IF ;
2506 l_return_status := FND_API.G_RET_STS_ERROR ;
2507 END IF;
2508
2509 IF (p_old_com_operation_rec.Option_Dependent_Flag <> l_com_operation_rec.Option_Dependent_Flag)
2510 THEN
2511 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2512 THEN
2513
2514 Error_Handler.Add_Error_Token
2515 ( p_message_name => 'BOM_STD_ODF_NOT_UPDATABLE'
2516 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2517 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2518 , p_Token_Tbl => l_Token_Tbl
2519 ) ;
2520 END IF ;
2521 l_return_status := FND_API.G_RET_STS_ERROR ;
2522 END IF;
2523
2524 IF (nvl(p_old_com_operation_rec.Operation_Description,'None') <>
2525 nvl(l_com_operation_rec.Operation_Description,'None'))
2526 THEN
2527 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2528 THEN
2529
2530 Error_Handler.Add_Error_Token
2531 ( p_message_name => 'BOM_STD_OPD_NOT_UPDATABLE'
2532 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2533 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2534 , p_Token_Tbl => l_Token_Tbl
2535 ) ;
2536 END IF ;
2537 l_return_status := FND_API.G_RET_STS_ERROR ;
2538 END IF;
2539
2540 IF (nvl(p_old_com_operation_rec.Long_Description,'NONE') <>
2541 nvl(l_com_operation_rec.Long_Description,'NONE'))
2542 THEN
2543 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2544 THEN
2545
2546 Error_Handler.Add_Error_Token
2547 ( p_message_name => 'BOM_STD_LD_NOT_UPDATABLE'
2548 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2549 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2550 , p_Token_Tbl => l_Token_Tbl
2551 ) ;
2552 END IF ;
2553 l_return_status := FND_API.G_RET_STS_ERROR ;
2554 END IF;
2555
2556 RAISE EXIT_CHECK_REF_STD_OP ;
2557
2558 END IF ;
2559
2560 --
2561 -- Standard Operation has changed to not null value
2562 --
2563 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
2564 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
2565 AND (( NVL(p_old_com_op_unexp_rec.standard_operation_id, -1) <>
2566 NVL(l_com_op_unexp_rec.standard_operation_id, -1)
2567 AND(p_old_com_op_unexp_rec.standard_operation_id <> FND_API.G_MISS_NUM
2568 OR p_old_com_op_unexp_rec.standard_operation_id IS NULL)
2569 ) OR
2570 ( p_old_com_op_unexp_rec.standard_operation_id = FND_API.G_MISS_NUM
2571 AND ( l_com_op_unexp_rec.standard_operation_id IS NOT NULL
2572 AND l_com_op_unexp_rec.standard_operation_id <> FND_API.G_MISS_NUM )
2573 ))
2574 THEN
2575
2576 --
2577 -- Check if the operation already has resources
2578 --
2579 l_copy_std_op := TRUE ;
2580 FOR l_exist_res_rec IN l_exist_res_cur
2581 ( p_op_seq_id => l_com_op_unexp_rec.operation_sequence_id )
2582 LOOP
2583 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2584 THEN
2585 Error_Handler.Add_Error_Token
2586 ( p_message_name => 'BOM_OP_CANNOT_COPY_STD_OP'
2587 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2588 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2589 , p_Token_Tbl => l_Token_Tbl
2590 ) ;
2591 END IF ;
2592
2593 l_return_status := FND_API.G_RET_STS_ERROR ;
2594 l_copy_std_op := FALSE ;
2595 END LOOP ;
2596
2597
2598 IF l_com_operation_rec.acd_type = l_ACD_CHANGE
2599 THEN
2600 FOR l_exist_res_rec IN l_exist_res_cur
2601 ( p_op_seq_id => l_com_op_unexp_rec.old_operation_sequence_id )
2602 LOOP
2603 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2604 THEN
2605 Error_Handler.Add_Error_Token
2606 ( p_message_name => 'BOM_OP_CANNOT_COPY_STD_OP'
2607 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2608 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2609 , p_Token_Tbl => l_Token_Tbl
2610 ) ;
2611 END IF ;
2612
2613 l_return_status := FND_API.G_RET_STS_ERROR ;
2614 l_copy_std_op := FALSE ;
2615 END LOOP ;
2616 END IF ;
2617
2618
2619 IF l_copy_std_op
2620 THEN
2621 --l_std_op_found := FALSE ;
2622
2623 --
2624 -- Get Standard Operatin Information
2625 --
2626
2627 FOR l_stdop_rec IN l_stdop_csr
2628 ( p_std_op_id => l_com_op_unexp_rec.standard_operation_id
2629 , p_op_type => l_com_operation_rec.operation_type
2630 , p_rtg_seq_id => l_com_op_unexp_rec.routing_sequence_id
2631 , p_org_id => l_com_op_unexp_rec.organization_id
2632 , p_dept_id => l_com_op_unexp_rec.department_id
2633 , p_rit_seq_id => l_com_op_unexp_rec.revised_item_sequence_id
2634 )
2635 LOOP
2636
2637 -- invalid standard op code
2638 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2639 THEN
2640 Error_Handler.Add_Error_Token
2641 ( p_message_name => 'BOM_OP_STD_OP_INVALID'
2642 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2643 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2644 , p_Token_Tbl => l_Token_Tbl
2645 ) ;
2646 END IF ;
2647
2648 l_return_status := FND_API.G_RET_STS_ERROR ;
2649
2650 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2651 ('Std operation is invalid. Return Status is '|| l_return_status ) ;
2652 END IF ;
2653
2654
2655 END LOOP ; -- copy standard operation
2656 FOR l_std_sub_res_rec IN c_std_sub_res (p_std_op_id => l_com_op_unexp_rec.standard_operation_id)
2657 LOOP
2658 -- The standard operation contains alternate resources
2659 -- We need to warn the user to change the schedule sequence number which will be defaulted to 0
2660 -- We will also unreference this standard operation as the user still has to enter the SSN
2661 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2662 THEN
2663 Error_Handler.Add_Error_Token
2664 ( p_message_name => 'BOM_SSN_ZERO_VALUE'
2665 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2666 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2667 , p_Token_Tbl => l_Token_Tbl
2668 , p_message_type => 'W'
2669 ) ;
2670 END IF ;
2671 l_com_operation_rec.reference_flag := 2;
2672
2673 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2674 ('Std operation has alternate resources');
2675 END IF;
2676 END LOOP;
2677 END IF ;
2678 END IF ;
2679
2680
2681 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2682 ('Check Reference Standard Operation was processed. . . ' || l_return_status);
2683 END IF ;
2684
2685 --
2686 -- Return Common Operation Record
2687 --
2688 x_com_operation_rec := l_com_operation_rec ;
2689 x_com_op_unexp_rec := l_com_op_unexp_rec ;
2690
2691 --
2692 -- Return Error Status
2693 --
2694 x_return_status := l_return_status;
2695 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2696
2697
2698 EXCEPTION
2699 WHEN EXIT_CHECK_REF_STD_OP THEN
2700
2701 --
2702 -- Return Common Operation Record
2703 --
2704 x_com_operation_rec := l_com_operation_rec ;
2705 x_com_op_unexp_rec := l_com_op_unexp_rec ;
2706
2707 --
2708 -- Return Error Status
2709 --
2710 x_return_status := l_return_status;
2711 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2712
2713 WHEN OTHERS THEN
2714 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2715 ('Some unknown error in Entity Validation(Check Ref Std Op) . . .' || SQLERRM );
2716 END IF ;
2717
2718
2719 l_err_text := G_PKG_NAME || ' Validation (Entity Validation(Check Ref Std Op)) '
2720 || substrb(SQLERRM,1,200);
2721 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
2722
2723 Error_Handler.Add_Error_Token
2724 ( p_message_name => NULL
2725 , p_message_text => l_err_text
2726 , p_mesg_token_tbl => l_mesg_token_tbl
2727 , x_mesg_token_tbl => l_mesg_token_tbl
2728 ) ;
2729
2730 -- Return the status and message table.
2731 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2732 x_mesg_token_tbl := l_mesg_token_tbl ;
2733
2734 END Check_Ref_Std_Operation ;
2735
2736
2737
2738 /*******************************************************************
2739 * Procedure : Check_Entity used by RTG BO
2740 * Parameters IN : Operation exposed column record
2741 * Operation unexposed column record
2742 * Old Operation exposed column record
2743 * Old Operation unexposed column record
2744 * Parameters out: Return Status
2745 * Message Token Table
2746 * Purpose : Convert Routing Operation to Common Operation and
2747 * Call Check_Entity for Common Operation.
2748 * Procedure will execute the business logic and will
2749 * also perform any required cross entity validations
2750 *******************************************************************/
2751 PROCEDURE Check_Entity
2752 ( p_operation_rec IN Bom_Rtg_Pub.Operation_Rec_Type
2753 , p_op_unexp_rec IN Bom_Rtg_Pub.Op_Unexposed_Rec_Type
2754 , p_old_operation_rec IN Bom_Rtg_Pub.Operation_Rec_Type
2755 , p_old_op_unexp_rec IN Bom_Rtg_Pub.Op_Unexposed_Rec_Type
2756 , x_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Operation_Rec_Type
2757 , x_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Unexposed_Rec_Type
2758 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2759 , x_return_status IN OUT NOCOPY VARCHAR2
2760 )
2761 IS
2762 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
2763 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
2764 l_old_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
2765 l_old_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
2766
2767 BEGIN
2768 -- Convert Routing Operation to Common Operation
2769 Bom_Rtg_Pub.Convert_RtgOp_To_ComOp
2770 ( p_rtg_operation_rec => p_operation_rec
2771 , p_rtg_op_unexp_rec => p_op_unexp_rec
2772 , x_com_operation_rec => l_com_operation_rec
2773 , x_com_op_unexp_rec => l_com_op_unexp_rec
2774 ) ;
2775
2776 -- Also Convert Old Routing Operation to Old Common Operation
2777 Bom_Rtg_Pub.Convert_RtgOp_To_ComOp
2778 ( p_rtg_operation_rec => p_old_operation_rec
2779 , p_rtg_op_unexp_rec => p_old_op_unexp_rec
2780 , x_com_operation_rec => l_old_com_operation_rec
2781 , x_com_op_unexp_rec => l_old_com_op_unexp_rec
2782 ) ;
2783
2784
2785 -- Call Check_Entity
2786 Bom_Validate_Op_Seq.Check_Entity
2787 ( p_com_operation_rec => l_com_operation_rec
2788 , p_com_op_unexp_rec => l_com_op_unexp_rec
2789 , p_control_rec => Bom_Rtg_Pub.G_DEFAULT_CONTROL_REC
2790 , p_old_com_operation_rec => l_old_com_operation_rec
2791 , p_old_com_op_unexp_rec => l_old_com_op_unexp_rec
2792 , x_com_operation_rec => l_com_operation_rec
2793 , x_com_op_unexp_rec => l_com_op_unexp_rec
2794 , x_return_status => x_return_status
2795 , x_mesg_token_tbl => x_mesg_token_tbl
2796 ) ;
2797
2798 -- Convert the Common record to Routing Record
2799 Bom_Rtg_Pub.Convert_ComOp_To_RtgOp
2800 ( p_com_operation_rec => l_com_operation_rec
2801 , p_com_op_unexp_rec => l_com_op_unexp_rec
2802 , x_rtg_operation_rec => x_operation_rec
2803 , x_rtg_op_unexp_rec => x_op_unexp_rec
2804 ) ;
2805
2806 END Check_Entity ;
2807
2808
2809 /*******************************************************************
2810 * Procedure : Check_Entity used by ECO BO
2811 * Parameters IN : Revised Operation exposed column record
2812 * Revised Operation unexposed column record
2813 * Revised Old Operation exposed column record
2814 * Revised Old Operation unexposed column record
2815 * Parameters out: Return Status
2816 * Message Token Table
2817 * Purpose : Convert ECO Operation to Common Operation and
2818 * Call Check_Entity for Common Operation.
2819 * Procedure will execute the business logic and will
2820 * also perform any required cross entity validations
2821 *******************************************************************/
2822 PROCEDURE Check_Entity
2823 ( p_rev_operation_rec IN Bom_Rtg_Pub.Rev_Operation_Rec_Type
2824 , p_rev_op_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
2825 , p_old_rev_operation_rec IN Bom_Rtg_Pub.Rev_Operation_Rec_Type
2826 , p_old_rev_op_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
2827 , p_control_rec IN Bom_Rtg_Pub.Control_Rec_Type
2828 , x_rev_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Operation_Rec_Type
2829 , x_rev_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Unexposed_Rec_Type
2830 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2831 , x_return_status IN OUT NOCOPY VARCHAR2
2832 )
2833
2834 IS
2835 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
2836 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
2837 l_old_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
2838 l_old_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
2839
2840 BEGIN
2841 -- Convert Revised Operation to Common Operation
2842 Bom_Rtg_Pub.Convert_EcoOp_To_ComOp
2843 ( p_rev_operation_rec => p_rev_operation_rec
2844 , p_rev_op_unexp_rec => p_rev_op_unexp_rec
2845 , x_com_operation_rec => l_com_operation_rec
2846 , x_com_op_unexp_rec => l_com_op_unexp_rec
2847 ) ;
2848
2849 -- Also Convert Old Revised Operation to Old Common Operation
2850 Bom_Rtg_Pub.Convert_EcoOp_To_ComOp
2851 ( p_rev_operation_rec => p_old_rev_operation_rec
2852 , p_rev_op_unexp_rec => p_old_rev_op_unexp_rec
2853 , x_com_operation_rec => l_old_com_operation_rec
2854 , x_com_op_unexp_rec => l_old_com_op_unexp_rec
2855 ) ;
2856
2857 -- Call Check_Entity
2858 Bom_Validate_Op_Seq.Check_Entity
2859 ( p_com_operation_rec => l_com_operation_rec
2860 , p_com_op_unexp_rec => l_com_op_unexp_rec
2861 , p_control_rec => Bom_Rtg_Pub.G_DEFAULT_CONTROL_REC
2862 , p_old_com_operation_rec => l_old_com_operation_rec
2863 , p_old_com_op_unexp_rec => l_old_com_op_unexp_rec
2864 , x_com_operation_rec => l_com_operation_rec
2865 , x_com_op_unexp_rec => l_com_op_unexp_rec
2866 , x_return_status => x_return_status
2867 , x_mesg_token_tbl => x_mesg_token_tbl
2868 ) ;
2869
2870 -- Convert the Common record to Revised Operation record
2871 Bom_Rtg_Pub.Convert_ComOp_To_EcoOp
2872 ( p_com_operation_rec => l_com_operation_rec
2873 , p_com_op_unexp_rec => l_com_op_unexp_rec
2874 , x_rev_operation_rec => x_rev_operation_rec
2875 , x_rev_op_unexp_rec => x_rev_op_unexp_rec
2876 ) ;
2877
2878 END Check_Entity ;
2879
2880
2881 /******************************************************************
2882 * Procedure : Check_Entity internally called by RTG BO and by ECO BO
2883 * Parameters IN : Common Operation exposed column record
2884 * Common Operation unexposed column record
2885 * Common Old Operation exposed column record
2886 * Common Old Operation unexposed column record
2887 * Parameters out: Return Status
2888 * Message Token Table
2889 * Purpose : Check_Entity validate the entity for the correct
2890 * business logic. It will verify the values by running
2891 * checks on inter-dependent columns.
2892 * It will also verify that changes in one column value
2893 * does not invalidate some other columns.
2894 **********************************************************************/
2895 PROCEDURE Check_Entity
2896 ( p_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
2897 , p_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
2898 , p_old_com_operation_rec IN Bom_Rtg_Pub.Com_Operation_Rec_Type
2899 , p_old_com_op_unexp_rec IN Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
2900 , p_control_rec IN Bom_Rtg_Pub.Control_Rec_Type
2901 , x_com_operation_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Operation_Rec_Type
2902 , x_com_op_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type
2903 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2904 , x_return_status IN OUT NOCOPY VARCHAR2
2905 )
2906 IS
2907 -- Variables
2908 l_eco_processed BOOLEAN ; -- Indicate ECO has been processed
2909 l_bom_item_type NUMBER ; -- Bom_Item_Type of Assembly
2910 l_pto_flag CHAR ; -- PTO flag for Assembly
2911 l_eng_item_flag CHAR ; -- Is assembly an Engineering Item
2912 l_bom_enabled_flag CHAR ; -- Assembly's bom_enabled_flag
2913
2914 l_com_operation_rec Bom_Rtg_Pub.Com_Operation_Rec_Type ;
2915 l_com_op_unexp_rec Bom_Rtg_Pub.Com_Op_Unexposed_Rec_Type ;
2916
2917 -- Error Handlig Variables
2918 l_return_status VARCHAR2(1);
2919 l_temp_return_status VARCHAR2(1);
2920 l_err_text VARCHAR2(2000) ;
2921 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
2922 l_temp_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type ;
2923 l_token_tbl Error_Handler.Token_Tbl_Type;
2924 l_err_code NUMBER;
2925
2926 -- Get Assembly or Revised Item Attr. Value
2927 CURSOR l_item_cur (p_org_id NUMBER, p_item_id NUMBER) IS
2928 SELECT bom_item_type
2929 , pick_components_flag
2930 , bom_enabled_flag
2931 , eng_item_flag
2932 FROM MTL_SYSTEM_ITEMS
2933 WHERE organization_id = p_org_id
2934 AND inventory_item_id = p_item_id
2935 ;
2936
2937 -- Check if Old Op Seq is not implemented
2938 CURSOR l_old_op_seq_csr (p_old_op_seq_id NUMBER) IS
2939 SELECT 'implemented'
2940 FROM BOM_OPERATION_SEQUENCES
2941 WHERE implementation_date IS NULL
2942 AND operation_sequence_id = p_old_op_seq_id
2943 ;
2944
2945 -- Check if Rtg is referencing another a rtg as a common
2946 CURSOR l_rtg_cur (p_rtg_seq_id NUMBER) IS
2947 SELECT 'implemented'
2948 FROM DUAL
2949 WHERE EXISTS ( SELECT NULL
2950 FROM bom_operational_routings
2951 WHERE common_routing_sequence_id <> p_rtg_seq_id
2952 ) ;
2953
2954 -- Check if Parent Operation has resource
2955 CURSOR l_parents_csr(p_parent_seq_id NUMBER) IS
2956 SELECT 'resources exist' dummy
2957 FROM BOM_OPERATION_RESOURCES
2958 WHERE operation_sequence_id = p_parent_seq_id ;
2959
2960
2961 -- Check if Uniqueness
2962 CURSOR l_duplicate_csr( p_op_seq_id NUMBER
2963 , p_rtg_seq_id NUMBER
2964 , p_op_seq_num NUMBER
2965 , p_op_type NUMBER
2966 , p_start_effective_date DATE )
2967 IS
2968 SELECT 'Duplicate Operation'
2969 FROM DUAL
2970 WHERE EXISTS (SELECT NULL
2971 FROM BOM_OPERATION_SEQUENCES
2972 WHERE ( ( effectivity_date = p_start_effective_date -- Changed for bug 2647027
2973 -- WHERE ( ( TRUNC(effectivity_date) = TRUNC(p_start_effective_date)
2974 AND operation_type = l_EVENT )
2975 OR p_op_type <> l_EVENT
2976 )
2977 AND NVL(operation_type, l_EVENT) = NVL(p_op_type, l_EVENT)
2978 -- AND implementation_date IS NOT NULL
2979 AND operation_seq_num = p_op_seq_num
2980 AND operation_sequence_id <> p_op_seq_id
2981 AND routing_sequence_id = p_rtg_seq_id
2982 ) ;
2983
2984 /* No Loger Used ----------------------------------------
2985 -- Check if Uniqueness in unimplemented revised operations
2986 ---------------------------------------------------------- */
2987
2988 -- Check if there is no overlapping operations
2989 CURSOR l_overlap_csr( p_op_seq_id NUMBER
2990 , p_rtg_seq_id NUMBER
2991 , p_op_seq_num NUMBER
2992 , p_op_type NUMBER
2993 , p_start_effective_date DATE
2994 , p_disable_date DATE )
2995 IS
2996 SELECT 'Overlapping'
2997 FROM DUAL
2998 WHERE EXISTS (SELECT NULL
2999 FROM BOM_OPERATION_SEQUENCES
3000 WHERE NVL(operation_type, l_EVENT) = NVL(p_op_type, l_EVENT)
3001 AND ((effectivity_date <
3002 NVL(p_disable_date, p_start_effective_date + 1)
3003 AND NVL(disable_date, p_start_effective_date + 1)
3004 >= p_start_effective_date)
3005 OR
3006 ( p_start_effective_date <
3007 NVL(disable_date, effectivity_date + 1)
3008 AND NVL(p_disable_date, effectivity_date+ 1)
3009 >= effectivity_date)
3010 /** Changed for bug 2647027 AND ((TRUNC(effectivity_date) <
3011 NVL(TRUNC(p_disable_date), TRUNC(p_start_effective_date + 1))
3012 AND NVL(TRUNC(disable_date), TRUNC(p_start_effective_date) + 1)
3013 >= TRUNC(p_start_effective_date))
3014 OR
3015 ( TRUNC(p_start_effective_date) <
3016 NVL(TRUNC(disable_date) , TRUNC(effectivity_date + 1))
3017 AND NVL(TRUNC(p_disable_date) , TRUNC(effectivity_date)+ 1)
3018 >= TRUNC(effectivity_date))
3019 **/ )
3020 AND implementation_date IS NOT NULL
3021 AND routing_sequence_id = p_rtg_seq_id
3022 AND operation_seq_num = p_op_seq_num
3023 AND operation_sequence_id <> p_op_seq_id
3024 ) ;
3025
3026 -- Check if there is no overlapping revised operations in another eco.
3027 CURSOR l_rev_overlap_csr( p_op_seq_id NUMBER
3028 , p_rtg_seq_id NUMBER
3029 , p_op_seq_num NUMBER
3030 , p_op_type NUMBER
3031 , p_start_effective_date DATE
3032 , p_disable_date DATE )
3033 IS
3034 SELECT 'Overlapping'
3035 FROM DUAL
3036 WHERE EXISTS (SELECT NULL
3037 FROM BOM_OPERATION_SEQUENCES
3038 WHERE NVL(operation_type, l_EVENT) = NVL(p_op_type, l_EVENT)
3039 AND ((effectivity_date <
3040 NVL(p_disable_date, p_start_effective_date + 1)
3041 AND NVL(disable_date, p_start_effective_date + 1)
3042 >= p_start_effective_date)
3043 OR
3044 ( p_start_effective_date <
3045 NVL(disable_date, effectivity_date + 1)
3046 AND NVL(p_disable_date, effectivity_date+ 1)
3047 >= effectivity_date)
3048 /** Changed for bug 2647027 AND ((TRUNC(effectivity_date) <
3049 NVL(TRUNC(p_disable_date) , TRUNC(p_start_effective_date + 1))
3050 AND NVL(TRUNC(disable_date) , TRUNC(p_start_effective_date) + 1)
3051 >= TRUNC(p_start_effective_date))
3052 OR
3053 ( TRUNC(p_start_effective_date) <
3054 NVL(TRUNC(disable_date) , TRUNC(effectivity_date + 1))
3055 AND NVL(TRUNC(p_disable_date) , TRUNC(effectivity_date)+ 1)
3056 >= TRUNC(effectivity_date))
3057 **/ )
3058 AND implementation_date IS NULL
3059 AND NVL(acd_type, l_ACD_ADD) IN (l_ACD_ADD, l_ACD_CHANGE)
3060 AND routing_sequence_id = p_rtg_seq_id
3061 AND operation_seq_num = p_op_seq_num
3062 AND operation_sequence_id <> p_op_seq_id
3063 ) ;
3064
3065
3066
3067
3068 -- Check if Department is valid
3069 CURSOR l_dept_csr ( p_organization_id NUMBER
3070 , p_dept_id NUMBER
3071 , p_start_effective_date DATE
3072 )
3073 IS
3074 SELECT 'Dept is invalid'
3075 FROM DUAL
3076 WHERE NOT EXISTS (SELECT NULL
3077 FROM BOM_DEPARTMENTS bd
3078 WHERE NVL(TRUNC(bd.disable_date) , TRUNC(p_start_effective_date) + 1)
3079 > TRUNC(p_start_effective_date)
3080 AND bd.organization_id = p_organization_id
3081 AND bd.department_id = p_dept_id
3082 ) ;
3083
3084
3085
3086 -- Added by MK on 02/02/2001
3087 -- Form has this validation LOV for Operation Sequence Number
3088 l_eco_for_production NUMBER ;
3089
3090 CURSOR l_val_old_op_seq_csr
3091 ( p_rtg_seq_id NUMBER ,
3092 p_effectivity_date DATE ,
3093 p_eco_name VARCHAR2,
3094 p_rev_item_seq_id NUMBER ,
3095 p_old_op_seq_id NUMBER ,
3096 p_eco_for_production NUMBER )
3097 IS
3098 SELECT 'Old Op Seq is invalid'
3099 FROM SYS.DUAL
3100 WHERE NOT EXISTS ( SELECT NULL
3101 FROM BOM_OPERATION_SEQUENCES bos
3102 WHERE routing_sequence_id =
3103 ( SELECT common_routing_sequence_id
3104 FROM bom_operational_routings bor
3105 WHERE routing_sequence_id = p_rtg_seq_id
3106 )
3107 AND bos.effectivity_date <= p_effectivity_date
3108 AND NVL(bos.disable_date,p_effectivity_date+1)
3109 > p_effectivity_date
3110 /** Changed for bug 2647027 AND TRUNC(bos.effectivity_date) <=
3111 TRUNC(p_effectivity_date)
3112 AND NVL(bos.disable_date,TRUNC(p_effectivity_date)+1)
3113 > p_effectivity_date
3114 **/ AND NVL(bos.disable_date , SYSDATE + 1) > SYSDATE
3115 AND (( p_eco_for_production = 2
3116 AND NVL(bos.eco_for_production, 2) <> 1
3117 )
3118 OR
3119 ( p_eco_for_production = 1
3120 AND bos.implementation_date IS NOT NULL
3121 )
3122 )
3123 AND NVL(bos.revised_item_sequence_id, -999)
3124 <> nvl(p_rev_item_seq_id, -100)
3125 AND NOT EXISTS (SELECT NULL
3126 FROM BOM_OPERATION_SEQUENCES bos2
3127 WHERE bos2.revised_item_sequence_id
3128 = NVL(p_rev_item_seq_id, -888)
3129 AND decode(bos2.implementation_date,
3130 null,
3131 bos2.old_operation_sequence_id,
3132 bos2.operation_sequence_id) =
3133 decode(bos.implementation_date,
3134 null,
3135 bos.old_operation_sequence_id,
3136 bos.operation_sequence_id)
3137 )
3138 AND bos.operation_sequence_id = p_old_op_seq_id
3139 ) ;
3140 CURSOR get_ssos_csr (p_rtg_seq_id NUMBER) IS
3141 SELECT serialization_start_op
3142 FROM BOM_OPERATIONAL_ROUTINGS
3143 WHERE routing_sequence_id = p_rtg_seq_id
3144 AND serialization_start_op IS NOT NULL;
3145
3146 BEGIN
3147 --
3148 -- Initialize Common Record and Status
3149 --
3150
3151 l_com_operation_rec := p_com_operation_rec ;
3152 l_com_op_unexp_rec := p_com_op_unexp_rec ;
3153 l_return_status := FND_API.G_RET_STS_SUCCESS ;
3154
3155 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3156 ('Performing Operation Check Entitity Validation . . .') ;
3157 END IF ;
3158
3159
3160 --
3161 -- Set the 1st token of Token Table to Revised Operation value
3162 --
3163 l_Token_Tbl(1).token_name := 'OP_SEQ_NUMBER';
3164 l_Token_Tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
3165
3166
3167
3168 -- First Query all the attributes for the Assembly item used Entity Validation
3169 FOR l_item_rec IN l_item_cur( p_org_id => l_com_op_unexp_rec.organization_id
3170 , p_item_id => l_com_op_unexp_rec.revised_item_id
3171 )
3172 LOOP
3173
3174 l_bom_item_type := l_item_rec.bom_item_type ;
3175 l_pto_flag := l_item_rec.pick_components_flag ;
3176 l_eng_item_flag := l_item_rec.eng_item_flag ;
3177 l_bom_enabled_flag := l_item_rec.bom_enabled_flag ;
3178 END LOOP ;
3179
3180 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3181 Error_Handler.Write_Debug('Business Object is') ;
3182 Error_Handler.Write_Debug(BOM_Rtg_Globals.Get_Bo_Identifier) ;
3183 END IF;
3184
3185
3186 --
3187 -- Performing Entity Validation in Revised Operatin(ECO BO)
3188 --
3189 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
3190 THEN
3191 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3192 ('Performing Entitity Validation for Eco Routing :ACD Type, Old Op Seq Num, Old Effect Date etc. . .') ;
3193 END IF;
3194
3195 --
3196 -- Check Revised Item Attributes for Revised Operation
3197 -- For CREATE and ACD Type : Add
3198 --
3199 IF l_com_operation_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
3200 AND ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD ) = l_ACD_ADD )
3201 THEN
3202
3203 --
3204 -- Verify that the Parent has BOM Enabled
3205 --
3206 IF l_bom_enabled_flag <> 'Y'
3207 THEN
3208 l_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
3209 l_token_tbl(1).token_value := l_com_operation_rec.revised_item_name;
3210
3211 Error_Handler.Add_Error_Token
3212 ( p_message_name => 'BOM_OP_RITEM_BOM_NOT_ALLOWED'
3213 , p_mesg_token_tbl => l_Mesg_Token_Tbl
3214 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3215 , p_token_tbl => l_token_tbl
3216 );
3217
3218 l_return_status := FND_API.G_RET_STS_ERROR;
3219 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3220 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
3221 END IF ;
3222
3223 --
3224 -- Verify that the Parent Item on Routing is not PTO Item
3225 --
3226 IF l_pto_flag <> 'N'
3227 THEN
3228 l_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
3229 l_token_tbl(1).token_value := l_com_operation_rec.revised_item_name;
3230
3231 Error_Handler.Add_Error_Token
3232 ( p_message_name => 'BOM_OP_RITEM_PTO_ITEM'
3233 , p_mesg_token_tbl => l_Mesg_Token_Tbl
3234 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3235 , p_token_tbl => l_token_tbl
3236 );
3237
3238 l_return_status := FND_API.G_RET_STS_ERROR;
3239 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3240 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
3241 END IF ;
3242
3243 --
3244 -- Verify that the Parent BOM Item Type is not 3:Planning Item
3245 --
3246 IF l_bom_item_type = l_PLANNING
3247 THEN
3248 l_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
3249 l_token_tbl(1).token_value := l_com_operation_rec.revised_item_name;
3250
3251 Error_Handler.Add_Error_Token
3252 ( p_message_name => 'BOM_OP_RITEM_PLANNING_ITEM'
3253 , p_mesg_token_tbl => l_Mesg_Token_Tbl
3254 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3255 , p_token_tbl => l_token_tbl
3256 );
3257
3258 l_return_status := FND_API.G_RET_STS_ERROR;
3259 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3260 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
3261 END IF ;
3262
3263 END IF ; -- For CREATE and ACD Type : Add
3264
3265 --
3266 -- If the revised operation would be created with an Acd_Type of Change or Disable
3267 -- the old operation must already be implemented.
3268 -- ( Find old operation record using Old_Operation_Sequence_Id.
3269 -- Implemention_Date must not be null in the record.)
3270 --
3271 /* Comment out by MK.
3272 -- This is allowed in Rev Component and should be allowed in Rev Op as well
3273 */
3274
3275
3276
3277 /********************************************************************
3278 -- Added by MK on 02/02/2001
3279 -- If the rev operation is created with an Acd_Type of Change or Disable
3280 -- then operation pointed to by old_operation_sequence_id should
3281 -- be valid against cusror l_val_old_op_seq_csr's conditions.
3282 *********************************************************************/
3283 IF l_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE AND
3284 l_com_operation_rec.acd_type IN (2, 3)
3285 THEN
3286
3287 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3288 Error_Handler.Write_Debug('Checking old operation : '|| to_char(l_com_op_unexp_rec.old_operation_sequence_id));
3289 END IF;
3290
3291 l_eco_for_production := NVL(BOM_Rtg_Globals.Get_Eco_For_Production,2) ;
3292
3293 FOR old_op_seq_rec IN l_val_old_op_seq_csr
3294 ( p_rtg_seq_id => l_com_op_unexp_rec.routing_sequence_id ,
3295 p_effectivity_date => l_com_operation_rec.start_effective_date ,
3296 p_eco_name => l_com_operation_rec.eco_name ,
3297 p_rev_item_seq_id => l_com_op_unexp_rec.revised_item_sequence_id ,
3298 p_old_op_seq_id => l_com_op_unexp_rec.old_operation_sequence_id ,
3299 p_eco_for_production => l_eco_for_production
3300 )
3301 LOOP
3302 -- operation is invalid
3303 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3304 THEN
3305 Error_Handler.Add_Error_Token
3306 ( p_message_name => 'BOM_OP_OLD_OPSEQ_INVALID'
3307 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3308 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3309 , p_Token_Tbl => l_Token_Tbl
3310 );
3311 END IF;
3312 l_return_status := FND_API.G_RET_STS_ERROR;
3313 END LOOP ;
3314
3315 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3316 Error_Handler.Write_Debug('After checking old operation. Return status is '|| l_return_status);
3317 END IF;
3318 END IF ;
3319
3320
3321 IF NOT Check_Primary_Routing( p_revised_item_id => l_com_op_unexp_rec.revised_item_id
3322 , p_organization_id => l_com_op_unexp_rec.organization_id )
3323 THEN
3324
3325
3326 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3327 ('ACD type to be add if primary routing does not exist . . .') ;
3328 END IF;
3329
3330 IF NVL(l_com_operation_rec.acd_type, l_ACD_ADD) <> l_ACD_ADD AND
3331 BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
3332 THEN
3333
3334 --
3335 -- If the primary routing does not exist then the acd type
3336 -- of the operation must not be Add.
3337 --
3338
3339 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3340 THEN
3341 l_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
3342 l_token_tbl(1).token_value := l_com_operation_rec.revised_item_name;
3343
3344 Error_Handler.Add_Error_Token
3345 ( p_message_name => 'BOM_OP_ACD_TYPE_ADD'
3346 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3347 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3348 , p_token_tbl => l_token_tbl
3349 ) ;
3350
3351 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3352 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
3353 END IF ;
3354 l_return_status := FND_API.G_RET_STS_ERROR ;
3355 END IF ;
3356 END IF ;
3357
3358 --
3359 -- Verify the ECO Effectivity, If ECO by WO, Lot Num, Or Cum Qty, then
3360 -- Check if the operation exist in the WO or Lot Num.
3361 --
3362 IF l_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
3363 AND l_com_operation_rec.acd_type IN (l_ACD_CHANGE, l_ACD_DISABLE)
3364 THEN
3365 IF NOT Check_ECO_By_WO_Effectivity
3366 ( p_revised_item_sequence_id => l_com_op_unexp_rec.revised_item_sequence_id
3367 , p_operation_seq_num => l_com_operation_rec.old_operation_sequence_number
3368 , p_organization_id => l_com_op_unexp_rec.organization_id
3369 , p_rev_item_id => l_com_op_unexp_rec.revised_item_id
3370 )
3371 THEN
3372 l_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
3373 l_token_tbl(1).token_value := l_com_operation_rec.revised_item_name;
3374 l_token_tbl(2).token_name := 'OP_SEQ_NUMBER';
3375 l_token_tbl(2).token_value := l_com_operation_rec.operation_sequence_number ;
3376
3377 Error_Handler.Add_Error_Token
3378 ( p_message_name => 'BOM_OP_RIT_ECO_WO_EFF_INVALID'
3379 , p_mesg_token_tbl => l_Mesg_Token_Tbl
3380 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3381 , p_token_tbl => l_token_tbl
3382 );
3383 l_return_status := FND_API.G_RET_STS_ERROR;
3384
3385 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3386 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
3387 END IF ;
3388 END IF ;
3389
3390 --
3391 -- Entity Validation for UPDATEs ONLY in ECO Bo
3392 --
3393 IF l_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
3394 THEN
3395
3396 --
3397 -- Verify that the user is not trying to update an operation which
3398 -- is Disabled on the ECO
3399 --
3400 IF p_old_com_operation_rec.acd_type = l_ACD_DISABLE
3401 THEN
3402 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3403 THEN
3404 Error_Handler.Add_Error_Token
3405 ( p_message_name => 'BOM_OP_DISABLED'
3406 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3407 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3408 , p_token_tbl => l_token_tbl
3409 ) ;
3410 END IF ;
3411 l_return_status := FND_API.G_RET_STS_ERROR ;
3412 END IF ;
3413
3414 --
3415 -- For UPDATE, ACD Type not updateable
3416 --
3417 IF l_com_operation_rec.acd_type <> p_old_com_operation_rec.acd_type
3418 THEN
3419 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3420 THEN
3421 Error_Handler.Add_Error_Token
3422 ( p_message_name => 'BOM_OP_ACD_TYPE_NOT_UPDATEABLE'
3423 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3424 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3425 , p_token_tbl => l_token_tbl
3426 ) ;
3427 END IF ;
3428 l_return_status := FND_API.G_RET_STS_ERROR ;
3429 END IF ;
3430
3431 --
3432 -- For UPDATE, Old Operatin Number not updateable
3433 --
3434 IF l_com_operation_rec.old_operation_sequence_number <>
3435 p_old_com_operation_rec.old_operation_sequence_number
3436 THEN
3437 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3438 THEN
3439 Error_Handler.Add_Error_Token
3440 ( p_message_name => 'BOM_OP_OLDOPSQNM_NT_UPDATEABLE'
3441 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3442 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3443 , p_token_tbl => l_token_tbl
3444 ) ;
3445 END IF ;
3446 l_return_status := FND_API.G_RET_STS_ERROR ;
3447 END IF ;
3448
3449 --
3450 -- For UPDATE, Old Effectivity Date not updateable
3451 --
3452 IF l_com_operation_rec.old_start_effective_date <>
3453 p_old_com_operation_rec.old_start_effective_date
3454 THEN
3455 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3456 THEN
3457 Error_Handler.Add_Error_Token
3458 ( p_message_name => 'BOM_OP_OLDEFFDT_NOT_UPDATEABLE'
3459 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3460 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3461 , p_token_tbl => l_token_tbl
3462 ) ;
3463 END IF ;
3464 l_return_status := FND_API.G_RET_STS_ERROR ;
3465 END IF ;
3466 END IF ; -- Validation for Update Only
3467
3468 --
3469 -- For CANCEL,
3470 -- If there is an unimplemented revised component referencing this
3471 -- operation squence number, cannot cancel this reivised operation.
3472 --
3473 --
3474 IF l_com_operation_rec.transaction_type IN ( BOM_Rtg_Globals.G_OPR_CANCEL
3475 ,BOM_Rtg_Globals.G_OPR_DELETE )
3476 AND l_com_operation_rec.acd_type = l_ACD_ADD
3477 THEN
3478
3479 IF NOT Check_Ref_Rev_Comp
3480 ( p_operation_seq_num => l_com_operation_rec.operation_sequence_number
3481 , p_start_effective_date => l_com_operation_rec.start_effective_date
3482 , p_rev_item_seq_id => l_com_op_unexp_rec.revised_item_sequence_id
3483 )
3484 THEN
3485
3486 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3487 THEN
3488 Error_Handler.Add_Error_Token
3489 ( p_message_name => 'BOM_OP_CANNOT_CANCL_FOR_REVCMP'
3490 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3491 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3492 , p_Token_Tbl => l_Token_Tbl
3493 ) ;
3494 END IF ;
3495 l_return_status := FND_API.G_RET_STS_ERROR;
3496
3497 END IF ;
3498 END IF ; -- For Cancel
3499
3500 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3501 ('End of Validation specific to ECO BO : ' || l_return_status) ;
3502 END IF ;
3503
3504
3505 END IF ; -- ECO BO Validation
3506
3507
3508 --
3509 -- For UPDATE or ACD type : Change.
3510 -- Validation specific to the Transaction Type of Update
3511 --
3512 IF l_com_operation_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_UPDATE
3513 OR ( l_com_operation_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
3514 AND l_com_operation_rec.acd_type = l_ACD_CHANGE )
3515 THEN
3516 --
3517 -- If Start Effective Date or New Start Effective Date is past,
3518 -- Effective_Date is not updatable
3519 --
3520 -- Added condition to check start_eff_date <> new_start_eff_date for bug 4666512
3521 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_RTG_BO
3522 AND l_com_operation_rec.new_start_effective_date IS NOT NULL
3523 AND l_com_operation_rec.new_start_effective_date <> FND_API.G_MISS_DATE
3524 AND l_com_operation_rec.new_start_effective_date <>
3525 l_com_operation_rec.start_effective_date
3526 AND ( l_com_operation_rec.start_effective_date < sysdate
3527 OR l_com_operation_rec.new_start_effective_date < sysdate
3528 /** Changed for bug 2647027
3529 AND ( trunc(l_com_operation_rec.start_effective_date) < trunc(sysdate)
3530 OR trunc(l_com_operation_rec.new_start_effective_date) < trunc(sysdate)
3531 **/ )
3532 THEN
3533
3534 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3535 THEN
3536 Error_Handler.Add_Error_Token
3537 ( p_message_name => 'BOM_OP_EFFDATE_NOT_UPDATABLE'
3538 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3539 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3540 , p_Token_Tbl => l_Token_Tbl
3541 ) ;
3542 END IF ;
3543 l_return_status := FND_API.G_RET_STS_ERROR;
3544 END IF ;
3545
3546
3547 --
3548 -- If Standard Operation Id is not null, Department Id is not updatable
3549 --
3550 -- For eAM enhancenment, added Eam Item condidtions for this validation.
3551 -- Also in maintenace routing, we allow eam users to update the dept code
3552 -- if the dept resources that have been assigned to the current operation
3553 -- should also exist in the dept to which the user changes.
3554
3555 IF p_old_com_op_unexp_rec.department_id <> l_com_op_unexp_rec.department_id
3556 AND (l_com_op_unexp_rec.standard_operation_id IS NOT NULL
3557 OR Check_ResExists
3558 ( p_op_seq_id => l_com_op_unexp_rec.operation_sequence_id
3559 , p_old_op_seq_id => l_com_op_unexp_rec.old_operation_sequence_id
3560 , p_acd_type => l_com_operation_rec.acd_type
3561 )
3562 )
3563 AND BOM_Rtg_Globals.Get_Eam_Item_Type <> BOM_Rtg_Globals.G_ASSET_ACTIVITY
3564 THEN
3565
3566 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3567 THEN
3568 Error_Handler.Add_Error_Token
3569 ( p_message_name => 'BOM_OP_DEPT_NOT_UPDATABLE'
3570 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3571 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3572 , p_Token_Tbl => l_Token_Tbl
3573 ) ;
3574 END IF ;
3575 l_return_status := FND_API.G_RET_STS_ERROR;
3576
3577 ELSIF p_old_com_op_unexp_rec.department_id <> l_com_op_unexp_rec.department_id
3578 AND BOM_Rtg_Globals.Get_Eam_Item_Type = BOM_Rtg_Globals.G_ASSET_ACTIVITY
3579 AND NOT Bom_Rtg_Eam_Util.Check_UpdateDept
3580 ( p_op_seq_id => l_com_op_unexp_rec.operation_sequence_id
3581 , p_org_id => l_com_op_unexp_rec.organization_id
3582 , p_dept_id => l_com_op_unexp_rec.department_id
3583 )
3584 THEN
3585
3586 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3587 THEN
3588 Error_Handler.Add_Error_Token
3589 ( p_message_name => 'BOM_EAM_OP_DEPT_NOT_UPDATABLE'
3590 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3591 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3592 , p_Token_Tbl => l_Token_Tbl
3593 ) ;
3594 END IF ;
3595 l_return_status := FND_API.G_RET_STS_ERROR;
3596
3597 END IF ;
3598
3599 --
3600 -- Reference Flag Validation.
3601 -- If once reference flag is set to NO, cannot update to Yes
3602 -- for reference to copied operation.
3603 --
3604 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3605 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3606 AND p_old_com_operation_rec.reference_flag = 2 -- 2:No
3607 AND l_com_operation_rec.reference_flag = 1 -- 1:Yes
3608 THEN
3609
3610 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3611 THEN
3612 Error_Handler.Add_Error_Token
3613 ( p_message_name => 'BOM_OP_REF_NOT_ALLOWED'
3614 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3615 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3616 , p_Token_Tbl => l_Token_Tbl
3617 ) ;
3618 END IF ;
3619 l_return_status := FND_API.G_RET_STS_ERROR;
3620 END IF ;
3621 END IF ; -- Transation: UPDATE
3622
3623 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3624 ('End of Validation specific to the Transaction Type of Update : ' || l_return_status) ;
3625 END IF ;
3626
3627 --
3628 -- Validateion for Transaction Type : Create and Update
3629 --
3630 IF BOM_Rtg_Globals.Get_Debug = 'Y'
3631 THEN
3632 Error_Handler.Write_Debug ('The Transaction Type is ');
3633 Error_Handler.Write_Debug (l_com_operation_rec.transaction_type);
3634 END IF ;
3635
3636 IF l_com_operation_rec.transaction_type IN
3637 (BOM_Rtg_Globals.G_OPR_CREATE, BOM_Rtg_Globals.G_OPR_UPDATE)
3638 THEN
3639
3640 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3641 ('Common Validateion for Transaction Type : Create and Update . . . . ' || l_return_status) ;
3642 END IF ;
3643
3644 --
3645 -- Reference Flag Validation.
3646 -- If standard operation id is null, It is unable to reference
3647 -- missing stanadrd op. So, It must be 2: No.
3648 --
3649 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3650 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3651 AND l_com_op_unexp_rec.standard_operation_id IS NULL
3652 AND l_com_operation_rec.reference_flag <> 2 -- 2:No
3653 THEN
3654
3655 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3656 THEN
3657 Error_Handler.Add_Error_Token
3658 ( p_message_name => 'BOM_OP_REFFLAG_MUST_BE_NO'
3659 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3660 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3661 , p_Token_Tbl => l_Token_Tbl
3662 ) ;
3663 END IF ;
3664 l_return_status := FND_API.G_RET_STS_ERROR ;
3665
3666 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3667 ('Reference Flag Validation, If Std Op is null, It must be No . . . ' || l_return_status) ;
3668 END IF ;
3669
3670 END IF ;
3671
3672 -- Validate Standard Operation and Reference flag
3673 -- If Standard Operation has changed to not null value
3674 -- If OK, Copy Std Operation and Std Resource
3675 -- Info regarding with Reference Flag.
3676 --
3677 Check_Ref_Std_Operation
3678 ( p_com_operation_rec => l_com_operation_rec
3679 , p_com_op_unexp_rec => l_com_op_unexp_rec
3680 , p_control_rec => Bom_Rtg_Pub.G_DEFAULT_CONTROL_REC
3681 , p_old_com_operation_rec => p_old_com_operation_rec
3682 , p_old_com_op_unexp_rec => p_old_com_op_unexp_rec
3683 , x_com_operation_rec => l_com_operation_rec
3684 , x_com_op_unexp_rec => l_com_op_unexp_rec
3685 , x_return_status => l_temp_return_status
3686 , x_mesg_token_tbl => l_temp_mesg_token_tbl
3687 ) ;
3688
3689 IF l_temp_return_status = FND_API.G_RET_STS_ERROR
3690 THEN
3691 l_mesg_token_tbl := l_temp_mesg_token_tbl ;
3692 l_return_status := FND_API.G_RET_STS_ERROR ;
3693 ELSIF l_temp_mesg_token_tbl.COUNT > 0 -- if warnings are logged
3694 THEN
3695 l_mesg_token_tbl := l_temp_mesg_token_tbl ;
3696 END IF ;
3697
3698 --
3699 -- Operation Type
3700 -- Only Events(Operation Type 1) have parents
3701 --
3702 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_FLOW_RTG AND
3703 l_com_operation_rec.operation_type <> l_EVENT AND
3704 ( l_com_op_unexp_rec.process_op_seq_id IS NOT NULL OR
3705 l_com_op_unexp_rec.line_op_seq_id IS NOT NULL )
3706 THEN
3707 l_token_tbl(2).token_name := 'OPERATION_TYPE';
3708 l_token_tbl(2).token_value := l_com_operation_rec.operation_type ;
3709
3710 Error_Handler.Add_Error_Token
3711 ( p_message_name => 'BOM_FLM_OP_CANNOT_HAVE_PARENTS'
3712 , p_mesg_token_tbl => l_Mesg_Token_Tbl
3713 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3714 , p_token_tbl => l_token_tbl
3715 ) ;
3716
3717 l_return_status := FND_API.G_RET_STS_ERROR;
3718
3719
3720 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3721 ('In Flow Routing Only Events(Operation Type 1) have parents. . . . ' || l_return_status) ;
3722 END IF ;
3723
3724 END IF ;
3725
3726 --
3727 -- Disable Date and Start Effective Date(New Effective Date)
3728 -- Effective_Date must be past or equal Disable_Date.
3729 --
3730 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3731 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3732 AND l_com_operation_rec.disable_date <
3733 NVL( l_com_operation_rec.new_start_effective_date
3734 , l_com_operation_rec.start_effective_date)
3735 THEN
3736
3737 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3738 THEN
3739 Error_Handler.Add_Error_Token
3740 ( p_message_name => 'BOM_OP_DISABLE_DATE_INVALID'
3741 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3742 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3743 , p_Token_Tbl => l_Token_Tbl
3744 ) ;
3745 END IF ;
3746 l_return_status := FND_API.G_RET_STS_ERROR;
3747
3748 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3749 ('Effective_Date must be past than or equal to Disable_Date. . . . ' || l_return_status) ;
3750 END IF ;
3751
3752 END IF ;
3753
3754
3755 --
3756 -- Backfluch Flag Validation.
3757 -- If Count Point Type : 3, Backflush Flag must be 1:Yes
3758 --
3759 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3760 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3761 AND l_com_operation_rec.count_point_type = 3 -- 3:Non-Direct
3762 AND l_com_operation_rec.backflush_flag <> 1 -- 1:Yes
3763 THEN
3764
3765 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3766 THEN
3767 Error_Handler.Add_Error_Token
3768 ( p_message_name => 'BOM_OP_BKFFLAG_CPNTYPE_INVALID'
3769 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3770 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3771 , p_Token_Tbl => l_Token_Tbl
3772 ) ;
3773 END IF ;
3774 l_return_status := FND_API.G_RET_STS_ERROR;
3775
3776 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3777 (' Backfluch Flag Validation. If Count Point Type : 3, Backflush Flag must be 1:Yes . . . '
3778 || l_return_status) ;
3779 END IF ;
3780
3781 END IF ;
3782
3783 --
3784 -- Option Dependent Flag Validation.
3785 -- If Rev Item or Assem Item's BOM Item Type is Standard,
3786 -- Operation Dependent Flag must be 2: No.
3787 --
3788
3789 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3790 Error_Handler.Write_Debug('Option Dependent Flag : ' || to_char(l_com_operation_rec.option_dependent_flag)) ;
3791 END IF ;
3792
3793 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3794 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3795 AND l_bom_item_type = l_STANDARD -- 4:Standard
3796 AND l_com_operation_rec.option_dependent_flag <> 2 -- 2:No
3797 THEN
3798
3799 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3800 THEN
3801 Error_Handler.Add_Error_Token
3802 ( p_message_name => 'BOM_OP_DPTFLAG_MUST_BE_NO'
3803 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3804 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3805 , p_Token_Tbl => l_Token_Tbl
3806 ) ;
3807 END IF ;
3808 l_return_status := FND_API.G_RET_STS_ERROR;
3809
3810 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3811 ('If Item : BOM Item Type is Std, Option Dependent Flag must be 2 - No . . .'
3812 || l_return_status) ;
3813 END IF ;
3814
3815 END IF ;
3816
3817 --
3818 -- Check if Department is valid
3819 --
3820 IF l_com_operation_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
3821 AND ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3822 OR ( l_com_operation_rec.acd_type = l_ACD_CHANGE
3823 AND l_com_op_unexp_rec.department_id <>
3824 p_old_com_op_unexp_rec.department_id
3825 )
3826 )
3827 THEN
3828 if BOM_Rtg_GLobals.Get_CFM_Rtg_Flag <> BOM_Rtg_Globals.G_Lot_Rtg then --for bug 3132411
3829 FOR l_dept_rec IN l_dept_csr
3830 ( p_organization_id=> l_com_op_unexp_rec.organization_id
3831 , p_dept_id => l_com_op_unexp_rec.department_id
3832 , p_start_effective_date => l_com_operation_rec.start_effective_date
3833 )
3834
3835 LOOP
3836
3837 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3838 THEN
3839 Error_Handler.Add_Error_Token
3840 ( p_message_name => 'BOM_OP_DEPT_ID_INVALID'
3841 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3842 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3843 , p_Token_Tbl => l_Token_Tbl
3844 ) ;
3845 END IF ;
3846 l_return_status := FND_API.G_RET_STS_ERROR ;
3847 END LOOP ;
3848 end if;
3849
3850 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3851 ('Check if Department is valid. ' || l_return_status) ;
3852 END IF ;
3853
3854 END IF ;
3855
3856
3857 --
3858 -- Process Op Seq Id Validation.
3859 -- Check if process operation does not have resources
3860 --
3861 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3862 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3863 AND l_com_op_unexp_rec.process_op_seq_id IS NOT NULL
3864 THEN
3865 FOR l_parenets_rec IN l_parents_csr(p_parent_seq_id
3866 => l_com_op_unexp_rec.process_op_seq_id )
3867 LOOP
3868
3869 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3870 THEN
3871 Error_Handler.Add_Error_Token
3872 ( p_message_name => 'BOM_FLM_OP_PRT_PCSOP_INVALID'
3873 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3874 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3875 , p_Token_Tbl => l_Token_Tbl
3876 ) ;
3877 END IF ;
3878 l_return_status := FND_API.G_RET_STS_ERROR ;
3879 END LOOP ;
3880
3881 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3882 ('Check if process operation does not have resources. . . ' || l_return_status) ;
3883 END IF ;
3884
3885 END IF ;
3886
3887 --
3888 -- Line Op Seq Id Validation.
3889 -- Check if line operation does not have resources
3890 --
3891 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3892 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3893 AND l_com_op_unexp_rec.line_op_seq_id IS NOT NULL
3894 THEN
3895 FOR l_parenets_rec IN l_parents_csr(p_parent_seq_id
3896 => l_com_op_unexp_rec.line_op_seq_id)
3897 LOOP
3898
3899 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3900 THEN
3901 Error_Handler.Add_Error_Token
3902 ( p_message_name => 'BOM_FLM_OP_PRT_LINEOP_INVALID'
3903 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3904 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3905 , p_Token_Tbl => l_Token_Tbl
3906 ) ;
3907 END IF ;
3908 l_return_status := FND_API.G_RET_STS_ERROR ;
3909 END LOOP ;
3910
3911 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3912 ('Check if line operation does not have resources. . . ' || l_return_status) ;
3913 END IF ;
3914
3915 END IF ;
3916
3917 --
3918 -- Check uniquness of the operation.
3919 --
3920 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3921 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3922 THEN
3923
3924 FOR l_duplicate_rec IN l_duplicate_csr
3925 ( p_op_seq_id => l_com_op_unexp_rec.operation_sequence_id
3926 , p_rtg_seq_id => l_com_op_unexp_rec.routing_sequence_id
3927 , p_op_seq_num => NVL( l_com_operation_rec.new_operation_sequence_number
3928 , l_com_operation_rec.operation_sequence_number)
3929 , p_op_type => l_com_operation_rec.operation_type
3930 , p_start_effective_date => NVL( l_com_operation_rec.new_start_effective_date
3931 , l_com_operation_rec.start_effective_date)
3932 )
3933 LOOP
3934 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3935 l_token_tbl(1).token_value := NVL( l_com_operation_rec.new_operation_sequence_number
3936 , l_com_operation_rec.operation_sequence_number) ;
3937
3938 Error_Handler.Add_Error_Token
3939 ( p_message_name => 'BOM_OP_NOT_UNIQUE'
3940 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3941 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3942 , p_Token_Tbl => l_Token_Tbl
3943 ) ;
3944 l_return_status := FND_API.G_RET_STS_ERROR ;
3945
3946 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3947 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
3948 END LOOP ;
3949
3950 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3951 ('Check uniqueness of the operation. . . ' || l_return_status) ;
3952 END IF ;
3953
3954 END IF ;
3955
3956
3957 --
3958 -- Check if there is no overlapping operations
3959 --
3960 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3961 AND l_com_operation_rec.operation_type NOT IN (l_PROCESS, l_LINE_OP)
3962 -- OR l_com_operation_rec.acd_type = l_ACD_CHANGE
3963 )
3964 THEN
3965
3966
3967 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3968 Error_Handler.Write_Debug('Rtg Seq Id : ' || to_char(l_com_op_unexp_rec.routing_sequence_id) ) ;
3969 Error_Handler.Write_Debug('Op Seq Id : ' || to_char(l_com_op_unexp_rec.operation_sequence_id) ) ;
3970 Error_Handler.Write_Debug('Op Type : ' || to_char(l_com_operation_rec.operation_type) ) ;
3971 Error_Handler.Write_Debug('New Op Seq : ' || to_char(l_com_operation_rec.new_operation_sequence_number) ) ;
3972 Error_Handler.Write_Debug('Op Seq Num : ' || to_char(l_com_operation_rec.operation_sequence_number) ) ;
3973 Error_Handler.Write_Debug('New Effect Date : ' || to_char(l_com_operation_rec.new_start_effective_date) ) ;
3974 Error_Handler.Write_Debug('Effect Date : ' || to_char(l_com_operation_rec.start_effective_date) ) ;
3975 Error_Handler.Write_Debug('Disable Date: ' || to_char(l_com_operation_rec.disable_date) ) ;
3976 END IF ;
3977
3978
3979 IF NVL(BOM_Rtg_Globals.Get_Eco_For_Production,2) <> 1 THEN
3980
3981 FOR l_overlap_rec IN l_overlap_csr
3982 ( p_op_seq_id => l_com_op_unexp_rec.operation_sequence_id
3983 , p_rtg_seq_id => l_com_op_unexp_rec.routing_sequence_id
3984 , p_op_seq_num => NVL( l_com_operation_rec.new_operation_sequence_number
3985 , l_com_operation_rec.operation_sequence_number)
3986 , p_op_type => l_com_operation_rec.operation_type
3987 , p_start_effective_date => NVL( l_com_operation_rec.new_start_effective_date
3988 , l_com_operation_rec.start_effective_date)
3989 , p_disable_date => l_com_operation_rec.disable_date
3990 )
3991 LOOP
3992 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3993 l_token_tbl(1).token_value := NVL( l_com_operation_rec.new_operation_sequence_number
3994 , l_com_operation_rec.operation_sequence_number) ;
3995
3996 Error_Handler.Add_Error_Token
3997 ( p_message_name => 'BOM_OP_OVERLAP'
3998 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3999 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4000 , p_Token_Tbl => l_Token_Tbl
4001 ) ;
4002 l_return_status := FND_API.G_RET_STS_ERROR ;
4003
4004 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4005 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
4006 END LOOP ;
4007
4008 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
4009 ('Check if there is no overlapping operations. . . ' || l_return_status) ;
4010 END IF ;
4011 END IF ;
4012
4013 END IF ;
4014
4015
4016 --
4017 -- Check if there is a unimplemented revised operation that
4018 -- has same op seqnumber
4019 -- If so, Generate Warning
4020 --
4021 /* If necessary, remove comment out, and create new message
4022 */
4023
4024 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
4025 ('Check uniqueness for unimplemented revised operations. . . ' || l_return_status) ;
4026 END IF ;
4027
4028 --
4029 -- Check if there is no overlapping for unimplemented revised operations
4030 --
4031 --
4032 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) IN (l_ACD_CHANGE, l_ACD_ADD)
4033 AND BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO )
4034 THEN
4035
4036 IF NVL(BOM_Rtg_Globals.Get_Eco_For_Production,2) <> 1 THEN
4037
4038
4039 FOR l_rev_overlap_rec IN l_rev_overlap_csr
4040 ( p_op_seq_id => l_com_op_unexp_rec.operation_sequence_id
4041 , p_rtg_seq_id => l_com_op_unexp_rec.routing_sequence_id
4042 , p_op_seq_num => NVL( l_com_operation_rec.new_operation_sequence_number
4043 , l_com_operation_rec.operation_sequence_number)
4044 , p_op_type => l_com_operation_rec.operation_type
4045 , p_start_effective_date => NVL( l_com_operation_rec.new_start_effective_date
4046 , l_com_operation_rec.start_effective_date)
4047 , p_disable_date => l_com_operation_rec.disable_date
4048 )
4049 LOOP
4050 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4051 l_token_tbl(1).token_value := NVL( l_com_operation_rec.new_operation_sequence_number
4052 , l_com_operation_rec.operation_sequence_number) ;
4053
4054 Error_Handler.Add_Error_Token
4055 ( p_message_name => 'BOM_REV_OP_OVERLAP'
4056 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4057 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4058 , p_Token_Tbl => l_Token_Tbl
4059 , p_message_type => 'W'
4060 ) ;
4061
4062 -- l_return_status := FND_API.G_RET_STS_ERROR ;
4063
4064 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4065 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
4066 END LOOP ;
4067
4068
4069 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
4070 ('Check if there is no overlapping operations for unimplemented revised operations. . . '
4071 || l_return_status) ;
4072 END IF ;
4073 END IF ;
4074 END IF ;
4075
4076
4077 END IF ; -- Transaction Type : Create and Update
4078
4079 -- The ECO can be updated but a warning needs to be generated and
4080 -- scheduled revised items need to be update to Open
4081 -- and the ECO status need to be changed to Not Submitted for Approval
4082
4083 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
4084 THEN
4085
4086 BOM_Rtg_Globals.Check_Approved_For_Process
4087 ( p_change_notice => l_com_operation_rec.eco_name
4088 , p_organization_id => l_com_op_unexp_rec.organization_id
4089 , x_processed => l_eco_processed
4090 , x_err_text => l_err_text
4091 ) ;
4092
4093 IF l_eco_processed THEN
4094 -- If the above process returns true then set the ECO approval.
4095 BOM_Rtg_Globals.Set_Request_For_Approval
4096 ( p_change_notice => l_com_operation_rec.eco_name
4097 , p_organization_id => l_com_op_unexp_rec.organization_id
4098 , x_err_text => l_err_text
4099 ) ;
4100
4101 END IF ;
4102
4103 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
4104 ('Check if ECO has been approved and has a workflow process. . . ' || l_return_status) ;
4105 END IF ;
4106
4107 END IF;
4108
4109 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_RTG_BO AND
4110 p_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_DELETE
4111 THEN
4112 IF p_com_operation_rec.Delete_Group_Name IS NULL OR
4113 p_com_operation_rec.Delete_Group_Name = FND_API.G_MISS_CHAR
4114 THEN
4115
4116 Error_Handler.Add_Error_Token
4117 ( p_message_name => 'BOM_DG_NAME_MISSING'
4118 , p_mesg_token_tbl => l_mesg_token_tbl
4119 , x_mesg_token_tbl => l_mesg_token_tbl
4120 );
4121 l_return_status := FND_API.G_RET_STS_ERROR;
4122 END IF;
4123 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
4124 ('Check if Delete Group is missing . . . ' || l_return_status) ;
4125 END IF ;
4126
4127 END IF ;
4128
4129 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
4130 ('Entity Validation was processed. . . ' || l_return_status);
4131 END IF ;
4132
4133 -- Check if an operation designated as SSOS is being deleted -- Added for SSOS (bug 2689249)
4134 -- or its op seq num is being changed. This is not allowed
4135 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_RTG_BO THEN
4136 FOR get_ssos_rec IN get_ssos_csr(p_rtg_seq_id => l_com_op_unexp_rec.routing_Sequence_id) LOOP
4137 IF p_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_DELETE THEN
4138 IF l_com_operation_rec.operation_sequence_number = get_ssos_rec.serialization_start_op THEN
4139 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4140 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number;
4141
4142 Error_Handler.Add_Error_Token
4143 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
4144 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4145 , p_message_name => 'BOM_OP_SSOS'
4146 , p_token_tbl => l_token_tbl
4147 );
4148 l_return_status := FND_API.G_RET_STS_ERROR;
4149 END IF;
4150 ELSIF p_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE THEN
4151 IF l_com_operation_rec.operation_sequence_number = get_ssos_rec.serialization_start_op AND
4152 l_com_operation_rec.new_operation_sequence_number <> l_com_operation_rec.operation_sequence_number THEN
4153 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4154 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number;
4155
4156 Error_Handler.Add_Error_Token
4157 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
4158 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4159 , p_message_name => 'BOM_OP_SSOS'
4160 , p_token_tbl => l_token_tbl
4161 );
4162 l_return_status := FND_API.G_RET_STS_ERROR;
4163 END IF;
4164 END IF;
4165 END LOOP;
4166 END IF;
4167
4168 --For checking that OSFM operation is not a po_move
4169 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_Lot_Rtg
4170 AND WSMPUTIL.CHECK_PO_MOVE(p_sequence_id => p_com_op_unexp_rec.Operation_Sequence_Id,
4171 p_sequence_id_type => 'O',
4172 p_routing_rev_date => SYSDATE,
4173 x_err_code => l_err_code,
4174 x_err_msg => l_err_text)
4175 THEN
4176 Error_Handler.Add_Error_Token(p_message_name => 'WSM_ROUTING_PO_MOVE',
4177 p_mesg_token_tbl => l_mesg_token_tbl,
4178 x_mesg_token_tbl => l_mesg_token_tbl,
4179 p_token_tbl => l_token_tbl);
4180 END IF;
4181 --End of PO_MOVE changes for OSFM operation
4182
4183 --For Delete Operation OSFM constraint
4184 IF p_com_operation_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_DELETE
4185 AND BOM_RTG_Globals.Is_Osfm_NW_Calc_Flag
4186 AND
4187 WSMPUTIL.JOBS_WITH_QTY_AT_FROM_OP (x_err_code => l_err_code,
4188 x_err_msg => l_err_text,
4189 p_operation_sequence_id => p_com_op_unexp_rec.Operation_Sequence_Id)
4190 THEN
4191 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4192 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number;
4193 Error_Handler.Add_Error_Token(p_message_name => 'BOM_WSM_OP_ACTIVE_JOB',
4194 p_mesg_token_tbl => l_mesg_token_tbl,
4195 p_token_tbl => l_token_tbl,
4196 x_mesg_token_tbl => l_mesg_token_tbl);
4197 l_return_status := Error_Handler.G_Status_Error;
4198
4199 END IF;
4200 --End of Delete Operation OSFM constraint
4201
4202 --
4203 -- Return Common Operation Record
4204 --
4205 x_com_operation_rec := l_com_operation_rec ;
4206 x_com_op_unexp_rec := l_com_op_unexp_rec ;
4207
4208 --
4209 -- Return Error Status
4210 --
4211 x_return_status := l_return_status;
4212 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4213
4214
4215 EXCEPTION
4216 WHEN OTHERS THEN
4217 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
4218 ('Some unknown error in Entity Validation . . .' || SQLERRM );
4219 END IF ;
4220
4221
4222 l_err_text := G_PKG_NAME || ' Validation (Entity Validation) '
4223 || substrb(SQLERRM,1,200);
4224 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
4225
4226 Error_Handler.Add_Error_Token
4227 ( p_message_name => NULL
4228 , p_message_text => l_err_text
4229 , p_mesg_token_tbl => l_mesg_token_tbl
4230 , x_mesg_token_tbl => l_mesg_token_tbl
4231 ) ;
4232
4233 -- Return the status and message table.
4234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4235 x_mesg_token_tbl := l_mesg_token_tbl ;
4236 END Check_Entity ;
4237
4238
4239
4240 /*************************************************************
4241 * Procedure : Check_Access
4242 * Parameters IN : Revised Item Unique Key
4243 * Revised Operation unique key
4244 * Parameters out: Mesg_Token_Tbl
4245 * Return_Status
4246 * Purpose : Procedure will verify that the revised item and the
4247 * revised operation is accessible to the user.
4248 ********************************************************************/
4249 PROCEDURE Check_Access
4250 ( p_revised_item_name IN VARCHAR2
4251 , p_revised_item_id IN NUMBER
4252 , p_organization_id IN NUMBER
4253 , p_change_notice IN VARCHAR2
4254 , p_new_item_revision IN VARCHAR2
4255 , p_effectivity_date IN DATE
4256 , p_new_routing_revsion IN VARCHAR2 -- Added by MK on 11/02/00
4257 , p_from_end_item_number IN VARCHAR2 -- Added by MK on 11/02/00
4258 , p_operation_seq_num IN NUMBER
4259 , p_routing_sequence_id IN NUMBER
4260 , p_operation_type IN NUMBER
4261 , p_Mesg_Token_Tbl IN Error_Handler.Mesg_Token_Tbl_Type
4262 , p_entity_processed IN VARCHAR2
4263 , p_resource_seq_num IN NUMBER
4264 , p_sub_resource_code IN VARCHAR2
4265 , p_sub_group_num IN NUMBER
4266 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
4267 , x_Return_Status IN OUT NOCOPY VARCHAR2
4268 )
4269 IS
4270 l_Token_Tbl Error_Handler.Token_Tbl_Type;
4271 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type :=
4272 p_Mesg_Token_Tbl;
4273 l_return_status VARCHAR2(1);
4274 l_error_name VARCHAR2(30);
4275 l_is_comp_unit_controlled BOOLEAN := FALSE;
4276 l_is_item_unit_controlled BOOLEAN := FALSE;
4277
4278
4279 CURSOR c_CheckDisabled IS
4280 SELECT NULL
4281 FROM BOM_OPERATION_SEQUENCES
4282 WHERE NVL(operation_type, 1) = NVL(p_operation_type, 1)
4283 AND effectivity_date = p_effectivity_date -- Changed for bug 2647027
4284 -- AND TRUNC(effectivity_date) = TRUNC(p_effectivity_date)
4285 AND routing_sequence_id = p_routing_sequence_id
4286 AND operation_seq_num = p_operation_seq_num
4287 AND acd_type = 3;
4288
4289 BEGIN
4290 l_return_status := FND_API.G_RET_STS_SUCCESS;
4291
4292 /* The code has been moved to ENGLRITB.pls because of ODF dependany
4293 unnecessarily created.
4294 Commenting the following code so that we can reuse in release 12 */
4295
4296 /****************************************************************
4297 --
4298 -- Check if the revised operation is not cancelled.
4299 -- This check will not prove useful for the revised item itself,
4300 -- since the check existence for a cancelled operation would fail.
4301 -- But this procedure can be called by the
4302 -- child records of the revised operation and make sure that the
4303 -- parent record is not cancelled.
4304 --
4305
4306 ********************************************************************/
4307
4308
4309
4310 /**************************************************************
4311 -- Added by MK on 11/01/2000
4312 -- If routing sequence id is null(Trans Type : CREATE) and this
4313 -- revised item does not have primary routing, verify that parent revised
4314 -- item does not have bill sequence id which has alternate code.
4315 -- (Verify this eco is not only for alternate bill )
4316 --
4317 **************************************************************/
4318
4319
4320 /**************************************************************
4321 --
4322 -- If the Entity being processed is Rev Operation Resource
4323 -- or Rev Sub Operation then check if the parent Rev Operation is
4324 -- disabled. If it is then Error this record and also all the
4325 -- siblings
4326 --
4327 **************************************************************/
4328 IF p_entity_processed IN ('RES', 'SR')
4329 THEN
4330 FOR isDisabled IN c_CheckDisabled LOOP
4331
4332 IF p_entity_processed = 'RES'
4333 THEN
4334 l_error_name := 'BOM_RES_OP_ACD_TYPE_DISABLE';
4335 l_token_tbl(1).token_name := 'RES_SEQ_NUMBER';
4336 l_token_tbl(1).token_value := p_resource_seq_num ;
4337 l_token_tbl(2).token_name := 'OP_SEQ_NUMBER';
4338 l_token_tbl(2).token_value := p_operation_seq_num;
4339
4340 ELSE
4341 l_error_name := 'BOM_SUB_RES_OP_ACDTYPE_DISABLE';
4342 l_token_tbl(1).token_name := 'SUB_RESOURCE_CODE';
4343 l_token_tbl(1).token_value := p_sub_resource_code ;
4344 l_token_tbl(2).token_name := 'SCHEDULE_SEQ_NUMBER';
4345 l_token_tbl(2).token_value := p_sub_group_num ;
4346 l_token_tbl(3).token_name := 'OP_SEQ_NUMBER';
4347 l_token_tbl(3).token_value := p_operation_seq_num;
4348
4349 END IF;
4350
4351 l_return_status := FND_API.G_RET_STS_ERROR;
4352
4353 Error_Handler.Add_Error_Token
4354 ( p_Message_Name => l_error_name
4355 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4356 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4357 , p_Token_Tbl => l_token_tbl
4358 );
4359 END LOOP;
4360 END IF;
4361
4362 x_Return_Status := l_return_status;
4363 x_Mesg_Token_Tbl := l_mesg_token_tbl;
4364 END Check_Access;
4365
4366
4367 END BOM_Validate_Op_Seq ;