[Home] [Help]
PACKAGE BODY: APPS.BOM_VALIDATE_OP_SEQ
Source
1 PACKAGE BODY BOM_Validate_Op_Seq AS
2 /* $Header: BOMLOPSB.pls 120.8.12020000.2 2012/08/06 22:41:45 umajumde 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) --Changed the condition for bug 14286614: when disable_date of one operation is the same as effective date of another it is not a true overlap
3005
3006 OR
3007 ( p_start_effective_date <
3008 NVL(disable_date, effectivity_date + 1)
3009 AND NVL(p_disable_date, effectivity_date+ 1)
3010 > effectivity_date) --Changed the condition for bug 14286614
3011 /** Changed for bug 2647027 AND ((TRUNC(effectivity_date) <
3012 NVL(TRUNC(p_disable_date), TRUNC(p_start_effective_date + 1))
3013 AND NVL(TRUNC(disable_date), TRUNC(p_start_effective_date) + 1)
3014 >= TRUNC(p_start_effective_date))
3015 OR
3016 ( TRUNC(p_start_effective_date) <
3017 NVL(TRUNC(disable_date) , TRUNC(effectivity_date + 1))
3018 AND NVL(TRUNC(p_disable_date) , TRUNC(effectivity_date)+ 1)
3019 >= TRUNC(effectivity_date))
3020 **/ )
3021 AND implementation_date IS NOT NULL
3022 AND routing_sequence_id = p_rtg_seq_id
3023 AND operation_seq_num = p_op_seq_num
3024 AND operation_sequence_id <> p_op_seq_id
3025 ) ;
3026
3027 -- Check if there is no overlapping revised operations in another eco.
3028 CURSOR l_rev_overlap_csr( p_op_seq_id NUMBER
3029 , p_rtg_seq_id NUMBER
3030 , p_op_seq_num NUMBER
3031 , p_op_type NUMBER
3032 , p_start_effective_date DATE
3033 , p_disable_date DATE )
3034 IS
3035 SELECT 'Overlapping'
3036 FROM DUAL
3037 WHERE EXISTS (SELECT NULL
3038 FROM BOM_OPERATION_SEQUENCES
3039 WHERE NVL(operation_type, l_EVENT) = NVL(p_op_type, l_EVENT)
3040 AND ((effectivity_date <
3041 NVL(p_disable_date, p_start_effective_date + 1)
3042 AND NVL(disable_date, p_start_effective_date + 1)
3043 >= p_start_effective_date)
3044 OR
3045 ( p_start_effective_date <
3046 NVL(disable_date, effectivity_date + 1)
3047 AND NVL(p_disable_date, effectivity_date+ 1)
3048 >= effectivity_date)
3049 /** Changed for bug 2647027 AND ((TRUNC(effectivity_date) <
3050 NVL(TRUNC(p_disable_date) , TRUNC(p_start_effective_date + 1))
3051 AND NVL(TRUNC(disable_date) , TRUNC(p_start_effective_date) + 1)
3052 >= TRUNC(p_start_effective_date))
3053 OR
3054 ( TRUNC(p_start_effective_date) <
3055 NVL(TRUNC(disable_date) , TRUNC(effectivity_date + 1))
3056 AND NVL(TRUNC(p_disable_date) , TRUNC(effectivity_date)+ 1)
3057 >= TRUNC(effectivity_date))
3058 **/ )
3059 AND implementation_date IS NULL
3060 AND NVL(acd_type, l_ACD_ADD) IN (l_ACD_ADD, l_ACD_CHANGE)
3061 AND routing_sequence_id = p_rtg_seq_id
3062 AND operation_seq_num = p_op_seq_num
3063 AND operation_sequence_id <> p_op_seq_id
3064 ) ;
3065
3066
3067
3068
3069 -- Check if Department is valid
3070 CURSOR l_dept_csr ( p_organization_id NUMBER
3071 , p_dept_id NUMBER
3072 , p_start_effective_date DATE
3073 )
3074 IS
3075 SELECT 'Dept is invalid'
3076 FROM DUAL
3077 WHERE NOT EXISTS (SELECT NULL
3078 FROM BOM_DEPARTMENTS bd
3079 WHERE NVL(TRUNC(bd.disable_date) , TRUNC(p_start_effective_date) + 1)
3080 > TRUNC(p_start_effective_date)
3081 AND bd.organization_id = p_organization_id
3082 AND bd.department_id = p_dept_id
3083 ) ;
3084
3085
3086
3087 -- Added by MK on 02/02/2001
3088 -- Form has this validation LOV for Operation Sequence Number
3089 l_eco_for_production NUMBER ;
3090
3091 CURSOR l_val_old_op_seq_csr
3092 ( p_rtg_seq_id NUMBER ,
3093 p_effectivity_date DATE ,
3094 p_eco_name VARCHAR2,
3095 p_rev_item_seq_id NUMBER ,
3096 p_old_op_seq_id NUMBER ,
3097 p_eco_for_production NUMBER )
3098 IS
3099 SELECT 'Old Op Seq is invalid'
3100 FROM SYS.DUAL
3101 WHERE NOT EXISTS ( SELECT NULL
3102 FROM BOM_OPERATION_SEQUENCES bos
3103 WHERE routing_sequence_id =
3104 ( SELECT common_routing_sequence_id
3105 FROM bom_operational_routings bor
3106 WHERE routing_sequence_id = p_rtg_seq_id
3107 )
3108 AND bos.effectivity_date <= p_effectivity_date
3109 AND NVL(bos.disable_date,p_effectivity_date+1)
3110 > p_effectivity_date
3111 /** Changed for bug 2647027 AND TRUNC(bos.effectivity_date) <=
3112 TRUNC(p_effectivity_date)
3113 AND NVL(bos.disable_date,TRUNC(p_effectivity_date)+1)
3114 > p_effectivity_date
3115 **/ AND NVL(bos.disable_date , SYSDATE + 1) > SYSDATE
3116 AND (( p_eco_for_production = 2
3117 AND NVL(bos.eco_for_production, 2) <> 1
3118 )
3119 OR
3120 ( p_eco_for_production = 1
3121 AND bos.implementation_date IS NOT NULL
3122 )
3123 )
3124 AND NVL(bos.revised_item_sequence_id, -999)
3125 <> nvl(p_rev_item_seq_id, -100)
3126 AND NOT EXISTS (SELECT NULL
3127 FROM BOM_OPERATION_SEQUENCES bos2
3128 WHERE bos2.revised_item_sequence_id
3129 = NVL(p_rev_item_seq_id, -888)
3130 AND decode(bos2.implementation_date,
3131 null,
3132 bos2.old_operation_sequence_id,
3133 bos2.operation_sequence_id) =
3134 decode(bos.implementation_date,
3135 null,
3136 bos.old_operation_sequence_id,
3137 bos.operation_sequence_id)
3138 )
3139 AND bos.operation_sequence_id = p_old_op_seq_id
3140 ) ;
3141 CURSOR get_ssos_csr (p_rtg_seq_id NUMBER) IS
3142 SELECT serialization_start_op
3143 FROM BOM_OPERATIONAL_ROUTINGS
3144 WHERE routing_sequence_id = p_rtg_seq_id
3145 AND serialization_start_op IS NOT NULL;
3146
3147 BEGIN
3148 --
3149 -- Initialize Common Record and Status
3150 --
3151
3152 l_com_operation_rec := p_com_operation_rec ;
3153 l_com_op_unexp_rec := p_com_op_unexp_rec ;
3154 l_return_status := FND_API.G_RET_STS_SUCCESS ;
3155
3156 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3157 ('Performing Operation Check Entitity Validation . . .') ;
3158 END IF ;
3159
3160
3161 --
3162 -- Set the 1st token of Token Table to Revised Operation value
3163 --
3164 l_Token_Tbl(1).token_name := 'OP_SEQ_NUMBER';
3165 l_Token_Tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
3166
3167
3168
3169 -- First Query all the attributes for the Assembly item used Entity Validation
3170 FOR l_item_rec IN l_item_cur( p_org_id => l_com_op_unexp_rec.organization_id
3171 , p_item_id => l_com_op_unexp_rec.revised_item_id
3172 )
3173 LOOP
3174
3175 l_bom_item_type := l_item_rec.bom_item_type ;
3176 l_pto_flag := l_item_rec.pick_components_flag ;
3177 l_eng_item_flag := l_item_rec.eng_item_flag ;
3178 l_bom_enabled_flag := l_item_rec.bom_enabled_flag ;
3179 END LOOP ;
3180
3181 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3182 Error_Handler.Write_Debug('Business Object is') ;
3183 Error_Handler.Write_Debug(BOM_Rtg_Globals.Get_Bo_Identifier) ;
3184 END IF;
3185
3186
3187 --
3188 -- Performing Entity Validation in Revised Operatin(ECO BO)
3189 --
3190 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
3191 THEN
3192 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3193 ('Performing Entitity Validation for Eco Routing :ACD Type, Old Op Seq Num, Old Effect Date etc. . .') ;
3194 END IF;
3195
3196 --
3197 -- Check Revised Item Attributes for Revised Operation
3198 -- For CREATE and ACD Type : Add
3199 --
3200 IF l_com_operation_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
3201 AND ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD ) = l_ACD_ADD )
3202 THEN
3203
3204 --
3205 -- Verify that the Parent has BOM Enabled
3206 --
3207 IF l_bom_enabled_flag <> 'Y'
3208 THEN
3209 l_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
3210 l_token_tbl(1).token_value := l_com_operation_rec.revised_item_name;
3211
3212 Error_Handler.Add_Error_Token
3213 ( p_message_name => 'BOM_OP_RITEM_BOM_NOT_ALLOWED'
3214 , p_mesg_token_tbl => l_Mesg_Token_Tbl
3215 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3216 , p_token_tbl => l_token_tbl
3217 );
3218
3219 l_return_status := FND_API.G_RET_STS_ERROR;
3220 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3221 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
3222 END IF ;
3223
3224 --
3225 -- Verify that the Parent Item on Routing is not PTO Item
3226 --
3227 IF l_pto_flag <> 'N'
3228 THEN
3229 l_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
3230 l_token_tbl(1).token_value := l_com_operation_rec.revised_item_name;
3231
3232 Error_Handler.Add_Error_Token
3233 ( p_message_name => 'BOM_OP_RITEM_PTO_ITEM'
3234 , p_mesg_token_tbl => l_Mesg_Token_Tbl
3235 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3236 , p_token_tbl => l_token_tbl
3237 );
3238
3239 l_return_status := FND_API.G_RET_STS_ERROR;
3240 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3241 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
3242 END IF ;
3243
3244 --
3245 -- Verify that the Parent BOM Item Type is not 3:Planning Item
3246 --
3247 IF l_bom_item_type = l_PLANNING
3248 THEN
3249 l_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
3250 l_token_tbl(1).token_value := l_com_operation_rec.revised_item_name;
3251
3252 Error_Handler.Add_Error_Token
3253 ( p_message_name => 'BOM_OP_RITEM_PLANNING_ITEM'
3254 , p_mesg_token_tbl => l_Mesg_Token_Tbl
3255 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3256 , p_token_tbl => l_token_tbl
3257 );
3258
3259 l_return_status := FND_API.G_RET_STS_ERROR;
3260 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3261 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
3262 END IF ;
3263
3264 END IF ; -- For CREATE and ACD Type : Add
3265
3266 --
3267 -- If the revised operation would be created with an Acd_Type of Change or Disable
3268 -- the old operation must already be implemented.
3269 -- ( Find old operation record using Old_Operation_Sequence_Id.
3270 -- Implemention_Date must not be null in the record.)
3271 --
3272 /* Comment out by MK.
3273 -- This is allowed in Rev Component and should be allowed in Rev Op as well
3274 */
3275
3276
3277
3278 /********************************************************************
3279 -- Added by MK on 02/02/2001
3280 -- If the rev operation is created with an Acd_Type of Change or Disable
3281 -- then operation pointed to by old_operation_sequence_id should
3282 -- be valid against cusror l_val_old_op_seq_csr's conditions.
3283 *********************************************************************/
3284 IF l_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE AND
3285 l_com_operation_rec.acd_type IN (2, 3)
3286 THEN
3287
3288 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3289 Error_Handler.Write_Debug('Checking old operation : '|| to_char(l_com_op_unexp_rec.old_operation_sequence_id));
3290 END IF;
3291
3292 l_eco_for_production := NVL(BOM_Rtg_Globals.Get_Eco_For_Production,2) ;
3293
3294 FOR old_op_seq_rec IN l_val_old_op_seq_csr
3295 ( p_rtg_seq_id => l_com_op_unexp_rec.routing_sequence_id ,
3296 p_effectivity_date => l_com_operation_rec.start_effective_date ,
3297 p_eco_name => l_com_operation_rec.eco_name ,
3298 p_rev_item_seq_id => l_com_op_unexp_rec.revised_item_sequence_id ,
3299 p_old_op_seq_id => l_com_op_unexp_rec.old_operation_sequence_id ,
3300 p_eco_for_production => l_eco_for_production
3301 )
3302 LOOP
3303 -- operation is invalid
3304 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3305 THEN
3306 Error_Handler.Add_Error_Token
3307 ( p_message_name => 'BOM_OP_OLD_OPSEQ_INVALID'
3308 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3309 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3310 , p_Token_Tbl => l_Token_Tbl
3311 );
3312 END IF;
3313 l_return_status := FND_API.G_RET_STS_ERROR;
3314 END LOOP ;
3315
3316 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3317 Error_Handler.Write_Debug('After checking old operation. Return status is '|| l_return_status);
3318 END IF;
3319 END IF ;
3320
3321
3322 IF NOT Check_Primary_Routing( p_revised_item_id => l_com_op_unexp_rec.revised_item_id
3323 , p_organization_id => l_com_op_unexp_rec.organization_id )
3324 THEN
3325
3326
3327 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3328 ('ACD type to be add if primary routing does not exist . . .') ;
3329 END IF;
3330
3331 IF NVL(l_com_operation_rec.acd_type, l_ACD_ADD) <> l_ACD_ADD AND
3332 BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
3333 THEN
3334
3335 --
3336 -- If the primary routing does not exist then the acd type
3337 -- of the operation must not be Add.
3338 --
3339
3340 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3341 THEN
3342 l_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
3343 l_token_tbl(1).token_value := l_com_operation_rec.revised_item_name;
3344
3345 Error_Handler.Add_Error_Token
3346 ( p_message_name => 'BOM_OP_ACD_TYPE_ADD'
3347 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3348 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3349 , p_token_tbl => l_token_tbl
3350 ) ;
3351
3352 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3353 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
3354 END IF ;
3355 l_return_status := FND_API.G_RET_STS_ERROR ;
3356 END IF ;
3357 END IF ;
3358
3359 --
3360 -- Verify the ECO Effectivity, If ECO by WO, Lot Num, Or Cum Qty, then
3361 -- Check if the operation exist in the WO or Lot Num.
3362 --
3363 IF l_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
3364 AND l_com_operation_rec.acd_type IN (l_ACD_CHANGE, l_ACD_DISABLE)
3365 THEN
3366 IF NOT Check_ECO_By_WO_Effectivity
3367 ( p_revised_item_sequence_id => l_com_op_unexp_rec.revised_item_sequence_id
3368 , p_operation_seq_num => l_com_operation_rec.old_operation_sequence_number
3369 , p_organization_id => l_com_op_unexp_rec.organization_id
3370 , p_rev_item_id => l_com_op_unexp_rec.revised_item_id
3371 )
3372 THEN
3373 l_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
3374 l_token_tbl(1).token_value := l_com_operation_rec.revised_item_name;
3375 l_token_tbl(2).token_name := 'OP_SEQ_NUMBER';
3376 l_token_tbl(2).token_value := l_com_operation_rec.operation_sequence_number ;
3377
3378 Error_Handler.Add_Error_Token
3379 ( p_message_name => 'BOM_OP_RIT_ECO_WO_EFF_INVALID'
3380 , p_mesg_token_tbl => l_Mesg_Token_Tbl
3381 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3382 , p_token_tbl => l_token_tbl
3383 );
3384 l_return_status := FND_API.G_RET_STS_ERROR;
3385
3386 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3387 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
3388 END IF ;
3389 END IF ;
3390
3391 --
3392 -- Entity Validation for UPDATEs ONLY in ECO Bo
3393 --
3394 IF l_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
3395 THEN
3396
3397 --
3398 -- Verify that the user is not trying to update an operation which
3399 -- is Disabled on the ECO
3400 --
3401 IF p_old_com_operation_rec.acd_type = l_ACD_DISABLE
3402 THEN
3403 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3404 THEN
3405 Error_Handler.Add_Error_Token
3406 ( p_message_name => 'BOM_OP_DISABLED'
3407 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3408 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3409 , p_token_tbl => l_token_tbl
3410 ) ;
3411 END IF ;
3412 l_return_status := FND_API.G_RET_STS_ERROR ;
3413 END IF ;
3414
3415 --
3416 -- For UPDATE, ACD Type not updateable
3417 --
3418 IF l_com_operation_rec.acd_type <> p_old_com_operation_rec.acd_type
3419 THEN
3420 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3421 THEN
3422 Error_Handler.Add_Error_Token
3423 ( p_message_name => 'BOM_OP_ACD_TYPE_NOT_UPDATEABLE'
3424 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3425 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3426 , p_token_tbl => l_token_tbl
3427 ) ;
3428 END IF ;
3429 l_return_status := FND_API.G_RET_STS_ERROR ;
3430 END IF ;
3431
3432 --
3433 -- For UPDATE, Old Operatin Number not updateable
3434 --
3435 IF l_com_operation_rec.old_operation_sequence_number <>
3436 p_old_com_operation_rec.old_operation_sequence_number
3437 THEN
3438 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3439 THEN
3440 Error_Handler.Add_Error_Token
3441 ( p_message_name => 'BOM_OP_OLDOPSQNM_NT_UPDATEABLE'
3442 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3443 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3444 , p_token_tbl => l_token_tbl
3445 ) ;
3446 END IF ;
3447 l_return_status := FND_API.G_RET_STS_ERROR ;
3448 END IF ;
3449
3450 --
3451 -- For UPDATE, Old Effectivity Date not updateable
3452 --
3453 IF l_com_operation_rec.old_start_effective_date <>
3454 p_old_com_operation_rec.old_start_effective_date
3455 THEN
3456 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3457 THEN
3458 Error_Handler.Add_Error_Token
3459 ( p_message_name => 'BOM_OP_OLDEFFDT_NOT_UPDATEABLE'
3460 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3461 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3462 , p_token_tbl => l_token_tbl
3463 ) ;
3464 END IF ;
3465 l_return_status := FND_API.G_RET_STS_ERROR ;
3466 END IF ;
3467 END IF ; -- Validation for Update Only
3468
3469 --
3470 -- For CANCEL,
3471 -- If there is an unimplemented revised component referencing this
3472 -- operation squence number, cannot cancel this reivised operation.
3473 --
3474 --
3475 IF l_com_operation_rec.transaction_type IN ( BOM_Rtg_Globals.G_OPR_CANCEL
3476 ,BOM_Rtg_Globals.G_OPR_DELETE )
3477 AND l_com_operation_rec.acd_type = l_ACD_ADD
3478 THEN
3479
3480 IF NOT Check_Ref_Rev_Comp
3481 ( p_operation_seq_num => l_com_operation_rec.operation_sequence_number
3482 , p_start_effective_date => l_com_operation_rec.start_effective_date
3483 , p_rev_item_seq_id => l_com_op_unexp_rec.revised_item_sequence_id
3484 )
3485 THEN
3486
3487 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3488 THEN
3489 Error_Handler.Add_Error_Token
3490 ( p_message_name => 'BOM_OP_CANNOT_CANCL_FOR_REVCMP'
3491 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3492 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3493 , p_Token_Tbl => l_Token_Tbl
3494 ) ;
3495 END IF ;
3496 l_return_status := FND_API.G_RET_STS_ERROR;
3497
3498 END IF ;
3499 END IF ; -- For Cancel
3500
3501 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3502 ('End of Validation specific to ECO BO : ' || l_return_status) ;
3503 END IF ;
3504
3505
3506 END IF ; -- ECO BO Validation
3507
3508
3509 --
3510 -- For UPDATE or ACD type : Change.
3511 -- Validation specific to the Transaction Type of Update
3512 --
3513 IF l_com_operation_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_UPDATE
3514 OR ( l_com_operation_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
3515 AND l_com_operation_rec.acd_type = l_ACD_CHANGE )
3516 THEN
3517 --
3518 -- If Start Effective Date or New Start Effective Date is past,
3519 -- Effective_Date is not updatable
3520 --
3521 -- Added condition to check start_eff_date <> new_start_eff_date for bug 4666512
3522 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_RTG_BO
3523 AND l_com_operation_rec.new_start_effective_date IS NOT NULL
3524 AND l_com_operation_rec.new_start_effective_date <> FND_API.G_MISS_DATE
3525 AND l_com_operation_rec.new_start_effective_date <>
3526 l_com_operation_rec.start_effective_date
3527 AND ( l_com_operation_rec.start_effective_date < sysdate
3528 OR l_com_operation_rec.new_start_effective_date < sysdate
3529 /** Changed for bug 2647027
3530 AND ( trunc(l_com_operation_rec.start_effective_date) < trunc(sysdate)
3531 OR trunc(l_com_operation_rec.new_start_effective_date) < trunc(sysdate)
3532 **/ )
3533 THEN
3534
3535 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3536 THEN
3537 Error_Handler.Add_Error_Token
3538 ( p_message_name => 'BOM_OP_EFFDATE_NOT_UPDATABLE'
3539 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3540 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3541 , p_Token_Tbl => l_Token_Tbl
3542 ) ;
3543 END IF ;
3544 l_return_status := FND_API.G_RET_STS_ERROR;
3545 END IF ;
3546
3547
3548 --
3549 -- If Standard Operation Id is not null, Department Id is not updatable
3550 --
3551 -- For eAM enhancenment, added Eam Item condidtions for this validation.
3552 -- Also in maintenace routing, we allow eam users to update the dept code
3553 -- if the dept resources that have been assigned to the current operation
3554 -- should also exist in the dept to which the user changes.
3555
3556 IF p_old_com_op_unexp_rec.department_id <> l_com_op_unexp_rec.department_id
3557 AND (l_com_op_unexp_rec.standard_operation_id IS NOT NULL
3558 OR Check_ResExists
3559 ( p_op_seq_id => l_com_op_unexp_rec.operation_sequence_id
3560 , p_old_op_seq_id => l_com_op_unexp_rec.old_operation_sequence_id
3561 , p_acd_type => l_com_operation_rec.acd_type
3562 )
3563 )
3564 AND BOM_Rtg_Globals.Get_Eam_Item_Type <> BOM_Rtg_Globals.G_ASSET_ACTIVITY
3565 THEN
3566
3567 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3568 THEN
3569 Error_Handler.Add_Error_Token
3570 ( p_message_name => 'BOM_OP_DEPT_NOT_UPDATABLE'
3571 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3572 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3573 , p_Token_Tbl => l_Token_Tbl
3574 ) ;
3575 END IF ;
3576 l_return_status := FND_API.G_RET_STS_ERROR;
3577
3578 ELSIF p_old_com_op_unexp_rec.department_id <> l_com_op_unexp_rec.department_id
3579 AND BOM_Rtg_Globals.Get_Eam_Item_Type = BOM_Rtg_Globals.G_ASSET_ACTIVITY
3580 AND NOT Bom_Rtg_Eam_Util.Check_UpdateDept
3581 ( p_op_seq_id => l_com_op_unexp_rec.operation_sequence_id
3582 , p_org_id => l_com_op_unexp_rec.organization_id
3583 , p_dept_id => l_com_op_unexp_rec.department_id
3584 )
3585 THEN
3586
3587 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3588 THEN
3589 Error_Handler.Add_Error_Token
3590 ( p_message_name => 'BOM_EAM_OP_DEPT_NOT_UPDATABLE'
3591 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3592 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3593 , p_Token_Tbl => l_Token_Tbl
3594 ) ;
3595 END IF ;
3596 l_return_status := FND_API.G_RET_STS_ERROR;
3597
3598 END IF ;
3599
3600 --
3601 -- Reference Flag Validation.
3602 -- If once reference flag is set to NO, cannot update to Yes
3603 -- for reference to copied operation.
3604 --
3605 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3606 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3607 AND p_old_com_operation_rec.reference_flag = 2 -- 2:No
3608 AND l_com_operation_rec.reference_flag = 1 -- 1:Yes
3609 THEN
3610
3611 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3612 THEN
3613 Error_Handler.Add_Error_Token
3614 ( p_message_name => 'BOM_OP_REF_NOT_ALLOWED'
3615 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3616 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3617 , p_Token_Tbl => l_Token_Tbl
3618 ) ;
3619 END IF ;
3620 l_return_status := FND_API.G_RET_STS_ERROR;
3621 END IF ;
3622 END IF ; -- Transation: UPDATE
3623
3624 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3625 ('End of Validation specific to the Transaction Type of Update : ' || l_return_status) ;
3626 END IF ;
3627
3628 --
3629 -- Validateion for Transaction Type : Create and Update
3630 --
3631 IF BOM_Rtg_Globals.Get_Debug = 'Y'
3632 THEN
3633 Error_Handler.Write_Debug ('The Transaction Type is ');
3634 Error_Handler.Write_Debug (l_com_operation_rec.transaction_type);
3635 END IF ;
3636
3637 IF l_com_operation_rec.transaction_type IN
3638 (BOM_Rtg_Globals.G_OPR_CREATE, BOM_Rtg_Globals.G_OPR_UPDATE)
3639 THEN
3640
3641 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3642 ('Common Validateion for Transaction Type : Create and Update . . . . ' || l_return_status) ;
3643 END IF ;
3644
3645 --
3646 -- Reference Flag Validation.
3647 -- If standard operation id is null, It is unable to reference
3648 -- missing stanadrd op. So, It must be 2: No.
3649 --
3650 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3651 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3652 AND l_com_op_unexp_rec.standard_operation_id IS NULL
3653 AND l_com_operation_rec.reference_flag <> 2 -- 2:No
3654 THEN
3655
3656 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3657 THEN
3658 Error_Handler.Add_Error_Token
3659 ( p_message_name => 'BOM_OP_REFFLAG_MUST_BE_NO'
3660 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3661 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3662 , p_Token_Tbl => l_Token_Tbl
3663 ) ;
3664 END IF ;
3665 l_return_status := FND_API.G_RET_STS_ERROR ;
3666
3667 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3668 ('Reference Flag Validation, If Std Op is null, It must be No . . . ' || l_return_status) ;
3669 END IF ;
3670
3671 END IF ;
3672
3673 -- Validate Standard Operation and Reference flag
3674 -- If Standard Operation has changed to not null value
3675 -- If OK, Copy Std Operation and Std Resource
3676 -- Info regarding with Reference Flag.
3677 --
3678 Check_Ref_Std_Operation
3679 ( p_com_operation_rec => l_com_operation_rec
3680 , p_com_op_unexp_rec => l_com_op_unexp_rec
3681 , p_control_rec => Bom_Rtg_Pub.G_DEFAULT_CONTROL_REC
3682 , p_old_com_operation_rec => p_old_com_operation_rec
3683 , p_old_com_op_unexp_rec => p_old_com_op_unexp_rec
3684 , x_com_operation_rec => l_com_operation_rec
3685 , x_com_op_unexp_rec => l_com_op_unexp_rec
3686 , x_return_status => l_temp_return_status
3687 , x_mesg_token_tbl => l_temp_mesg_token_tbl
3688 ) ;
3689
3690 IF l_temp_return_status = FND_API.G_RET_STS_ERROR
3691 THEN
3692 l_mesg_token_tbl := l_temp_mesg_token_tbl ;
3693 l_return_status := FND_API.G_RET_STS_ERROR ;
3694 ELSIF l_temp_mesg_token_tbl.COUNT > 0 -- if warnings are logged
3695 THEN
3696 l_mesg_token_tbl := l_temp_mesg_token_tbl ;
3697 END IF ;
3698
3699 --
3700 -- Operation Type
3701 -- Only Events(Operation Type 1) have parents
3702 --
3703 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_FLOW_RTG AND
3704 l_com_operation_rec.operation_type <> l_EVENT AND
3705 ( l_com_op_unexp_rec.process_op_seq_id IS NOT NULL OR
3706 l_com_op_unexp_rec.line_op_seq_id IS NOT NULL )
3707 THEN
3708 l_token_tbl(2).token_name := 'OPERATION_TYPE';
3709 l_token_tbl(2).token_value := l_com_operation_rec.operation_type ;
3710
3711 Error_Handler.Add_Error_Token
3712 ( p_message_name => 'BOM_FLM_OP_CANNOT_HAVE_PARENTS'
3713 , p_mesg_token_tbl => l_Mesg_Token_Tbl
3714 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3715 , p_token_tbl => l_token_tbl
3716 ) ;
3717
3718 l_return_status := FND_API.G_RET_STS_ERROR;
3719
3720
3721 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3722 ('In Flow Routing Only Events(Operation Type 1) have parents. . . . ' || l_return_status) ;
3723 END IF ;
3724
3725 END IF ;
3726
3727 --
3728 -- Disable Date and Start Effective Date(New Effective Date)
3729 -- Effective_Date must be past or equal Disable_Date.
3730 --
3731 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3732 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3733 AND l_com_operation_rec.disable_date <
3734 NVL( l_com_operation_rec.new_start_effective_date
3735 , l_com_operation_rec.start_effective_date)
3736 THEN
3737
3738 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3739 THEN
3740 Error_Handler.Add_Error_Token
3741 ( p_message_name => 'BOM_OP_DISABLE_DATE_INVALID'
3742 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3743 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3744 , p_Token_Tbl => l_Token_Tbl
3745 ) ;
3746 END IF ;
3747 l_return_status := FND_API.G_RET_STS_ERROR;
3748
3749 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3750 ('Effective_Date must be past than or equal to Disable_Date. . . . ' || l_return_status) ;
3751 END IF ;
3752
3753 END IF ;
3754
3755
3756 --
3757 -- Backfluch Flag Validation.
3758 -- If Count Point Type : 3, Backflush Flag must be 1:Yes
3759 --
3760 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3761 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3762 AND l_com_operation_rec.count_point_type = 3 -- 3:Non-Direct
3763 AND l_com_operation_rec.backflush_flag <> 1 -- 1:Yes
3764 THEN
3765
3766 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3767 THEN
3768 Error_Handler.Add_Error_Token
3769 ( p_message_name => 'BOM_OP_BKFFLAG_CPNTYPE_INVALID'
3770 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3771 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3772 , p_Token_Tbl => l_Token_Tbl
3773 ) ;
3774 END IF ;
3775 l_return_status := FND_API.G_RET_STS_ERROR;
3776
3777 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3778 (' Backfluch Flag Validation. If Count Point Type : 3, Backflush Flag must be 1:Yes . . . '
3779 || l_return_status) ;
3780 END IF ;
3781
3782 END IF ;
3783
3784 --
3785 -- Option Dependent Flag Validation.
3786 -- If Rev Item or Assem Item's BOM Item Type is Standard,
3787 -- Operation Dependent Flag must be 2: No.
3788 --
3789
3790 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3791 Error_Handler.Write_Debug('Option Dependent Flag : ' || to_char(l_com_operation_rec.option_dependent_flag)) ;
3792 END IF ;
3793
3794 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3795 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3796 AND l_bom_item_type = l_STANDARD -- 4:Standard
3797 AND l_com_operation_rec.option_dependent_flag <> 2 -- 2:No
3798 THEN
3799
3800 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3801 THEN
3802 Error_Handler.Add_Error_Token
3803 ( p_message_name => 'BOM_OP_DPTFLAG_MUST_BE_NO'
3804 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3805 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3806 , p_Token_Tbl => l_Token_Tbl
3807 ) ;
3808 END IF ;
3809 l_return_status := FND_API.G_RET_STS_ERROR;
3810
3811 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3812 ('If Item : BOM Item Type is Std, Option Dependent Flag must be 2 - No . . .'
3813 || l_return_status) ;
3814 END IF ;
3815
3816 END IF ;
3817
3818 --
3819 -- Check if Department is valid
3820 --
3821 IF l_com_operation_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_CREATE
3822 AND ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3823 OR ( l_com_operation_rec.acd_type = l_ACD_CHANGE
3824 AND l_com_op_unexp_rec.department_id <>
3825 p_old_com_op_unexp_rec.department_id
3826 )
3827 )
3828 THEN
3829 if BOM_Rtg_GLobals.Get_CFM_Rtg_Flag <> BOM_Rtg_Globals.G_Lot_Rtg then --for bug 3132411
3830 FOR l_dept_rec IN l_dept_csr
3831 ( p_organization_id=> l_com_op_unexp_rec.organization_id
3832 , p_dept_id => l_com_op_unexp_rec.department_id
3833 , p_start_effective_date => l_com_operation_rec.start_effective_date
3834 )
3835
3836 LOOP
3837
3838 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3839 THEN
3840 Error_Handler.Add_Error_Token
3841 ( p_message_name => 'BOM_OP_DEPT_ID_INVALID'
3842 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3843 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3844 , p_Token_Tbl => l_Token_Tbl
3845 ) ;
3846 END IF ;
3847 l_return_status := FND_API.G_RET_STS_ERROR ;
3848 END LOOP ;
3849 end if;
3850
3851 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3852 ('Check if Department is valid. ' || l_return_status) ;
3853 END IF ;
3854
3855 END IF ;
3856
3857
3858 --
3859 -- Process Op Seq Id Validation.
3860 -- Check if process operation does not have resources
3861 --
3862 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3863 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3864 AND l_com_op_unexp_rec.process_op_seq_id IS NOT NULL
3865 THEN
3866 FOR l_parenets_rec IN l_parents_csr(p_parent_seq_id
3867 => l_com_op_unexp_rec.process_op_seq_id )
3868 LOOP
3869
3870 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3871 THEN
3872 Error_Handler.Add_Error_Token
3873 ( p_message_name => 'BOM_FLM_OP_PRT_PCSOP_INVALID'
3874 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3875 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3876 , p_Token_Tbl => l_Token_Tbl
3877 ) ;
3878 END IF ;
3879 l_return_status := FND_API.G_RET_STS_ERROR ;
3880 END LOOP ;
3881
3882 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3883 ('Check if process operation does not have resources. . . ' || l_return_status) ;
3884 END IF ;
3885
3886 END IF ;
3887
3888 --
3889 -- Line Op Seq Id Validation.
3890 -- Check if line operation does not have resources
3891 --
3892 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3893 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3894 AND l_com_op_unexp_rec.line_op_seq_id IS NOT NULL
3895 THEN
3896 FOR l_parenets_rec IN l_parents_csr(p_parent_seq_id
3897 => l_com_op_unexp_rec.line_op_seq_id)
3898 LOOP
3899
3900 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3901 THEN
3902 Error_Handler.Add_Error_Token
3903 ( p_message_name => 'BOM_FLM_OP_PRT_LINEOP_INVALID'
3904 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3905 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3906 , p_Token_Tbl => l_Token_Tbl
3907 ) ;
3908 END IF ;
3909 l_return_status := FND_API.G_RET_STS_ERROR ;
3910 END LOOP ;
3911
3912 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3913 ('Check if line operation does not have resources. . . ' || l_return_status) ;
3914 END IF ;
3915
3916 END IF ;
3917
3918 --
3919 -- Check uniquness of the operation.
3920 --
3921 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3922 OR l_com_operation_rec.acd_type = l_ACD_CHANGE)
3923 THEN
3924
3925 FOR l_duplicate_rec IN l_duplicate_csr
3926 ( p_op_seq_id => l_com_op_unexp_rec.operation_sequence_id
3927 , p_rtg_seq_id => l_com_op_unexp_rec.routing_sequence_id
3928 , p_op_seq_num => NVL( l_com_operation_rec.new_operation_sequence_number
3929 , l_com_operation_rec.operation_sequence_number)
3930 , p_op_type => l_com_operation_rec.operation_type
3931 , p_start_effective_date => NVL( l_com_operation_rec.new_start_effective_date
3932 , l_com_operation_rec.start_effective_date)
3933 )
3934 LOOP
3935 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3936 l_token_tbl(1).token_value := NVL( l_com_operation_rec.new_operation_sequence_number
3937 , l_com_operation_rec.operation_sequence_number) ;
3938
3939 Error_Handler.Add_Error_Token
3940 ( p_message_name => 'BOM_OP_NOT_UNIQUE'
3941 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3942 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3943 , p_Token_Tbl => l_Token_Tbl
3944 ) ;
3945 l_return_status := FND_API.G_RET_STS_ERROR ;
3946
3947 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3948 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
3949 END LOOP ;
3950
3951 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
3952 ('Check uniqueness of the operation. . . ' || l_return_status) ;
3953 END IF ;
3954
3955 END IF ;
3956
3957
3958 --
3959 -- Check if there is no overlapping operations
3960 --
3961 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) = l_ACD_ADD
3962 AND l_com_operation_rec.operation_type NOT IN (l_PROCESS, l_LINE_OP)
3963 -- OR l_com_operation_rec.acd_type = l_ACD_CHANGE
3964 )
3965 THEN
3966
3967
3968 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
3969 Error_Handler.Write_Debug('Rtg Seq Id : ' || to_char(l_com_op_unexp_rec.routing_sequence_id) ) ;
3970 Error_Handler.Write_Debug('Op Seq Id : ' || to_char(l_com_op_unexp_rec.operation_sequence_id) ) ;
3971 Error_Handler.Write_Debug('Op Type : ' || to_char(l_com_operation_rec.operation_type) ) ;
3972 Error_Handler.Write_Debug('New Op Seq : ' || to_char(l_com_operation_rec.new_operation_sequence_number) ) ;
3973 Error_Handler.Write_Debug('Op Seq Num : ' || to_char(l_com_operation_rec.operation_sequence_number) ) ;
3974 Error_Handler.Write_Debug('New Effect Date : ' || to_char(l_com_operation_rec.new_start_effective_date) ) ;
3975 Error_Handler.Write_Debug('Effect Date : ' || to_char(l_com_operation_rec.start_effective_date) ) ;
3976 Error_Handler.Write_Debug('Disable Date: ' || to_char(l_com_operation_rec.disable_date) ) ;
3977 END IF ;
3978
3979
3980 IF NVL(BOM_Rtg_Globals.Get_Eco_For_Production,2) <> 1 THEN
3981
3982 FOR l_overlap_rec IN l_overlap_csr
3983 ( p_op_seq_id => l_com_op_unexp_rec.operation_sequence_id
3984 , p_rtg_seq_id => l_com_op_unexp_rec.routing_sequence_id
3985 , p_op_seq_num => NVL( l_com_operation_rec.new_operation_sequence_number
3986 , l_com_operation_rec.operation_sequence_number)
3987 , p_op_type => l_com_operation_rec.operation_type
3988 , p_start_effective_date => NVL( l_com_operation_rec.new_start_effective_date
3989 , l_com_operation_rec.start_effective_date)
3990 , p_disable_date => l_com_operation_rec.disable_date
3991 )
3992 LOOP
3993 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
3994 l_token_tbl(1).token_value := NVL( l_com_operation_rec.new_operation_sequence_number
3995 , l_com_operation_rec.operation_sequence_number) ;
3996
3997 Error_Handler.Add_Error_Token
3998 ( p_message_name => 'BOM_OP_OVERLAP'
3999 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4000 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4001 , p_Token_Tbl => l_Token_Tbl
4002 ) ;
4003 l_return_status := FND_API.G_RET_STS_ERROR ;
4004
4005 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4006 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
4007 END LOOP ;
4008
4009 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
4010 ('Check if there is no overlapping operations. . . ' || l_return_status) ;
4011 END IF ;
4012 END IF ;
4013
4014 END IF ;
4015
4016
4017 --
4018 -- Check if there is a unimplemented revised operation that
4019 -- has same op seqnumber
4020 -- If so, Generate Warning
4021 --
4022 /* If necessary, remove comment out, and create new message
4023 */
4024
4025 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
4026 ('Check uniqueness for unimplemented revised operations. . . ' || l_return_status) ;
4027 END IF ;
4028
4029 --
4030 -- Check if there is no overlapping for unimplemented revised operations
4031 --
4032 --
4033 IF ( NVL(l_com_operation_rec.acd_type,l_ACD_ADD) IN (l_ACD_CHANGE, l_ACD_ADD)
4034 AND BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO )
4035 THEN
4036
4037 IF NVL(BOM_Rtg_Globals.Get_Eco_For_Production,2) <> 1 THEN
4038
4039
4040 FOR l_rev_overlap_rec IN l_rev_overlap_csr
4041 ( p_op_seq_id => l_com_op_unexp_rec.operation_sequence_id
4042 , p_rtg_seq_id => l_com_op_unexp_rec.routing_sequence_id
4043 , p_op_seq_num => NVL( l_com_operation_rec.new_operation_sequence_number
4044 , l_com_operation_rec.operation_sequence_number)
4045 , p_op_type => l_com_operation_rec.operation_type
4046 , p_start_effective_date => NVL( l_com_operation_rec.new_start_effective_date
4047 , l_com_operation_rec.start_effective_date)
4048 , p_disable_date => l_com_operation_rec.disable_date
4049 )
4050 LOOP
4051 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4052 l_token_tbl(1).token_value := NVL( l_com_operation_rec.new_operation_sequence_number
4053 , l_com_operation_rec.operation_sequence_number) ;
4054
4055 Error_Handler.Add_Error_Token
4056 ( p_message_name => 'BOM_REV_OP_OVERLAP'
4057 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4058 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4059 , p_Token_Tbl => l_Token_Tbl
4060 , p_message_type => 'W'
4061 ) ;
4062
4063 -- l_return_status := FND_API.G_RET_STS_ERROR ;
4064
4065 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4066 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number ;
4067 END LOOP ;
4068
4069
4070 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
4071 ('Check if there is no overlapping operations for unimplemented revised operations. . . '
4072 || l_return_status) ;
4073 END IF ;
4074 END IF ;
4075 END IF ;
4076
4077
4078 END IF ; -- Transaction Type : Create and Update
4079
4080 -- The ECO can be updated but a warning needs to be generated and
4081 -- scheduled revised items need to be update to Open
4082 -- and the ECO status need to be changed to Not Submitted for Approval
4083
4084 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_ECO_BO
4085 THEN
4086
4087 BOM_Rtg_Globals.Check_Approved_For_Process
4088 ( p_change_notice => l_com_operation_rec.eco_name
4089 , p_organization_id => l_com_op_unexp_rec.organization_id
4090 , x_processed => l_eco_processed
4091 , x_err_text => l_err_text
4092 ) ;
4093
4094 IF l_eco_processed THEN
4095 -- If the above process returns true then set the ECO approval.
4096 BOM_Rtg_Globals.Set_Request_For_Approval
4097 ( p_change_notice => l_com_operation_rec.eco_name
4098 , p_organization_id => l_com_op_unexp_rec.organization_id
4099 , x_err_text => l_err_text
4100 ) ;
4101
4102 END IF ;
4103
4104 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
4105 ('Check if ECO has been approved and has a workflow process. . . ' || l_return_status) ;
4106 END IF ;
4107
4108 END IF;
4109
4110 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_RTG_BO AND
4111 p_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_DELETE
4112 THEN
4113 IF p_com_operation_rec.Delete_Group_Name IS NULL OR
4114 p_com_operation_rec.Delete_Group_Name = FND_API.G_MISS_CHAR
4115 THEN
4116
4117 Error_Handler.Add_Error_Token
4118 ( p_message_name => 'BOM_DG_NAME_MISSING'
4119 , p_mesg_token_tbl => l_mesg_token_tbl
4120 , x_mesg_token_tbl => l_mesg_token_tbl
4121 );
4122 l_return_status := FND_API.G_RET_STS_ERROR;
4123 END IF;
4124 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
4125 ('Check if Delete Group is missing . . . ' || l_return_status) ;
4126 END IF ;
4127
4128 END IF ;
4129
4130 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
4131 ('Entity Validation was processed. . . ' || l_return_status);
4132 END IF ;
4133
4134 -- Check if an operation designated as SSOS is being deleted -- Added for SSOS (bug 2689249)
4135 -- or its op seq num is being changed. This is not allowed
4136 IF BOM_Rtg_Globals.Get_Bo_Identifier = BOM_Rtg_Globals.G_RTG_BO THEN
4137 FOR get_ssos_rec IN get_ssos_csr(p_rtg_seq_id => l_com_op_unexp_rec.routing_Sequence_id) LOOP
4138 IF p_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_DELETE THEN
4139 IF l_com_operation_rec.operation_sequence_number = get_ssos_rec.serialization_start_op THEN
4140 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4141 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number;
4142
4143 Error_Handler.Add_Error_Token
4144 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
4145 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4146 , p_message_name => 'BOM_OP_SSOS'
4147 , p_token_tbl => l_token_tbl
4148 );
4149 l_return_status := FND_API.G_RET_STS_ERROR;
4150 END IF;
4151 ELSIF p_com_operation_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE THEN
4152 IF l_com_operation_rec.operation_sequence_number = get_ssos_rec.serialization_start_op AND
4153 l_com_operation_rec.new_operation_sequence_number <> l_com_operation_rec.operation_sequence_number THEN
4154 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4155 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number;
4156
4157 Error_Handler.Add_Error_Token
4158 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
4159 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4160 , p_message_name => 'BOM_OP_SSOS'
4161 , p_token_tbl => l_token_tbl
4162 );
4163 l_return_status := FND_API.G_RET_STS_ERROR;
4164 END IF;
4165 END IF;
4166 END LOOP;
4167 END IF;
4168
4169 --For checking that OSFM operation is not a po_move
4170 IF BOM_Rtg_Globals.Get_CFM_Rtg_Flag = BOM_Rtg_Globals.G_Lot_Rtg
4171 AND WSMPUTIL.CHECK_PO_MOVE(p_sequence_id => p_com_op_unexp_rec.Operation_Sequence_Id,
4172 p_sequence_id_type => 'O',
4173 p_routing_rev_date => SYSDATE,
4174 x_err_code => l_err_code,
4175 x_err_msg => l_err_text)
4176 THEN
4177 Error_Handler.Add_Error_Token(p_message_name => 'WSM_ROUTING_PO_MOVE',
4178 p_mesg_token_tbl => l_mesg_token_tbl,
4179 x_mesg_token_tbl => l_mesg_token_tbl,
4180 p_token_tbl => l_token_tbl);
4181 END IF;
4182 --End of PO_MOVE changes for OSFM operation
4183
4184 --For Delete Operation OSFM constraint
4185 IF p_com_operation_rec.Transaction_Type = BOM_Rtg_Globals.G_OPR_DELETE
4186 AND BOM_RTG_Globals.Is_Osfm_NW_Calc_Flag
4187 AND
4188 WSMPUTIL.JOBS_WITH_QTY_AT_FROM_OP (x_err_code => l_err_code,
4189 x_err_msg => l_err_text,
4190 p_operation_sequence_id => p_com_op_unexp_rec.Operation_Sequence_Id)
4191 THEN
4192 l_token_tbl(1).token_name := 'OP_SEQ_NUMBER';
4193 l_token_tbl(1).token_value := l_com_operation_rec.operation_sequence_number;
4194 Error_Handler.Add_Error_Token(p_message_name => 'BOM_WSM_OP_ACTIVE_JOB',
4195 p_mesg_token_tbl => l_mesg_token_tbl,
4196 p_token_tbl => l_token_tbl,
4197 x_mesg_token_tbl => l_mesg_token_tbl);
4198 l_return_status := Error_Handler.G_Status_Error;
4199
4200 END IF;
4201 --End of Delete Operation OSFM constraint
4202
4203 --
4204 -- Return Common Operation Record
4205 --
4206 x_com_operation_rec := l_com_operation_rec ;
4207 x_com_op_unexp_rec := l_com_op_unexp_rec ;
4208
4209 --
4210 -- Return Error Status
4211 --
4212 x_return_status := l_return_status;
4213 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4214
4215
4216 EXCEPTION
4217 WHEN OTHERS THEN
4218 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
4219 ('Some unknown error in Entity Validation . . .' || SQLERRM );
4220 END IF ;
4221
4222
4223 l_err_text := G_PKG_NAME || ' Validation (Entity Validation) '
4224 || substrb(SQLERRM,1,200);
4225 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
4226
4227 Error_Handler.Add_Error_Token
4228 ( p_message_name => NULL
4229 , p_message_text => l_err_text
4230 , p_mesg_token_tbl => l_mesg_token_tbl
4231 , x_mesg_token_tbl => l_mesg_token_tbl
4232 ) ;
4233
4234 -- Return the status and message table.
4235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4236 x_mesg_token_tbl := l_mesg_token_tbl ;
4237 END Check_Entity ;
4238
4239
4240
4241 /*************************************************************
4242 * Procedure : Check_Access
4243 * Parameters IN : Revised Item Unique Key
4244 * Revised Operation unique key
4245 * Parameters out: Mesg_Token_Tbl
4246 * Return_Status
4247 * Purpose : Procedure will verify that the revised item and the
4248 * revised operation is accessible to the user.
4249 ********************************************************************/
4250 PROCEDURE Check_Access
4251 ( p_revised_item_name IN VARCHAR2
4252 , p_revised_item_id IN NUMBER
4253 , p_organization_id IN NUMBER
4254 , p_change_notice IN VARCHAR2
4255 , p_new_item_revision IN VARCHAR2
4256 , p_effectivity_date IN DATE
4257 , p_new_routing_revsion IN VARCHAR2 -- Added by MK on 11/02/00
4258 , p_from_end_item_number IN VARCHAR2 -- Added by MK on 11/02/00
4259 , p_operation_seq_num IN NUMBER
4260 , p_routing_sequence_id IN NUMBER
4261 , p_operation_type IN NUMBER
4262 , p_Mesg_Token_Tbl IN Error_Handler.Mesg_Token_Tbl_Type
4263 , p_entity_processed IN VARCHAR2
4264 , p_resource_seq_num IN NUMBER
4265 , p_sub_resource_code IN VARCHAR2
4266 , p_sub_group_num IN NUMBER
4267 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
4268 , x_Return_Status IN OUT NOCOPY VARCHAR2
4269 )
4270 IS
4271 l_Token_Tbl Error_Handler.Token_Tbl_Type;
4272 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type :=
4273 p_Mesg_Token_Tbl;
4274 l_return_status VARCHAR2(1);
4275 l_error_name VARCHAR2(30);
4276 l_is_comp_unit_controlled BOOLEAN := FALSE;
4277 l_is_item_unit_controlled BOOLEAN := FALSE;
4278
4279
4280 CURSOR c_CheckDisabled IS
4281 SELECT NULL
4282 FROM BOM_OPERATION_SEQUENCES
4283 WHERE NVL(operation_type, 1) = NVL(p_operation_type, 1)
4284 AND effectivity_date = p_effectivity_date -- Changed for bug 2647027
4285 -- AND TRUNC(effectivity_date) = TRUNC(p_effectivity_date)
4286 AND routing_sequence_id = p_routing_sequence_id
4287 AND operation_seq_num = p_operation_seq_num
4288 AND acd_type = 3;
4289
4290 BEGIN
4291 l_return_status := FND_API.G_RET_STS_SUCCESS;
4292
4293 /* The code has been moved to ENGLRITB.pls because of ODF dependany
4294 unnecessarily created.
4295 Commenting the following code so that we can reuse in release 12 */
4296
4297 /****************************************************************
4298 --
4299 -- Check if the revised operation is not cancelled.
4300 -- This check will not prove useful for the revised item itself,
4301 -- since the check existence for a cancelled operation would fail.
4302 -- But this procedure can be called by the
4303 -- child records of the revised operation and make sure that the
4304 -- parent record is not cancelled.
4305 --
4306
4307 ********************************************************************/
4308
4309
4310
4311 /**************************************************************
4312 -- Added by MK on 11/01/2000
4313 -- If routing sequence id is null(Trans Type : CREATE) and this
4314 -- revised item does not have primary routing, verify that parent revised
4315 -- item does not have bill sequence id which has alternate code.
4316 -- (Verify this eco is not only for alternate bill )
4317 --
4318 **************************************************************/
4319
4320
4321 /**************************************************************
4322 --
4323 -- If the Entity being processed is Rev Operation Resource
4324 -- or Rev Sub Operation then check if the parent Rev Operation is
4325 -- disabled. If it is then Error this record and also all the
4326 -- siblings
4327 --
4328 **************************************************************/
4329 IF p_entity_processed IN ('RES', 'SR')
4330 THEN
4331 FOR isDisabled IN c_CheckDisabled LOOP
4332
4333 IF p_entity_processed = 'RES'
4334 THEN
4335 l_error_name := 'BOM_RES_OP_ACD_TYPE_DISABLE';
4336 l_token_tbl(1).token_name := 'RES_SEQ_NUMBER';
4337 l_token_tbl(1).token_value := p_resource_seq_num ;
4338 l_token_tbl(2).token_name := 'OP_SEQ_NUMBER';
4339 l_token_tbl(2).token_value := p_operation_seq_num;
4340
4341 ELSE
4342 l_error_name := 'BOM_SUB_RES_OP_ACDTYPE_DISABLE';
4343 l_token_tbl(1).token_name := 'SUB_RESOURCE_CODE';
4344 l_token_tbl(1).token_value := p_sub_resource_code ;
4345 l_token_tbl(2).token_name := 'SCHEDULE_SEQ_NUMBER';
4346 l_token_tbl(2).token_value := p_sub_group_num ;
4347 l_token_tbl(3).token_name := 'OP_SEQ_NUMBER';
4348 l_token_tbl(3).token_value := p_operation_seq_num;
4349
4350 END IF;
4351
4352 l_return_status := FND_API.G_RET_STS_ERROR;
4353
4354 Error_Handler.Add_Error_Token
4355 ( p_Message_Name => l_error_name
4356 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4357 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4358 , p_Token_Tbl => l_token_tbl
4359 );
4360 END LOOP;
4361 END IF;
4362
4363 x_Return_Status := l_return_status;
4364 x_Mesg_Token_Tbl := l_mesg_token_tbl;
4365 END Check_Access;
4366
4367
4368 END BOM_Validate_Op_Seq ;