[Home] [Help]
PACKAGE BODY: APPS.ENG_VALIDATE_ECO
Source
1 PACKAGE BODY ENG_VALIDATE_ECO AS
2 /* $Header: ENGLECOB.pls 120.8 2011/08/19 09:45:08 gliang ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_Validate_Eco';
7
8 PROCEDURE grant_role_guid
9 (
10 p_api_version IN NUMBER,
11 p_role_name IN VARCHAR2,
12 p_object_name IN VARCHAR2,
13 p_instance_type IN VARCHAR2,
14 p_instance_set_id IN NUMBER,
15 p_instance_pk1_value IN VARCHAR2,
16 p_instance_pk2_value IN VARCHAR2,
17 p_instance_pk3_value IN VARCHAR2,
18 p_instance_pk4_value IN VARCHAR2,
19 p_instance_pk5_value IN VARCHAR2,
20 p_party_id IN NUMBER,
21 p_start_date IN DATE,
22 p_end_date IN DATE,
23 x_return_status OUT NOCOPY VARCHAR2,
24 x_errorcode OUT NOCOPY NUMBER,
25 x_grant_guid OUT NOCOPY RAW
26 )
27 IS
28
29 --x_grant_guid fnd_grants.grant_guid%TYPE;
30 l_grantee_type hz_parties.party_type%TYPE;
31 l_instance_type fnd_grants.instance_type%TYPE;
32 l_grantee_key fnd_grants.grantee_key%TYPE;
33 l_dummy VARCHAR2(1);
34 CURSOR get_party_type (cp_party_id NUMBER)
35 IS
36 SELECT party_type
37 FROM hz_parties
38 WHERE party_id=cp_party_id;
39 --Changing NULL to '*NULL*' as FND is upgrading their grants data model
40 CURSOR check_fnd_grant_exist (cp_grantee_key VARCHAR2,
41 cp_grantee_type VARCHAR2,
42 cp_menu_name VARCHAR2,
43 cp_object_name VARCHAR2,
44 cp_instance_type VARCHAR2,
45 cp_instance_pk1_value VARCHAR2,
46 cp_instance_pk2_value VARCHAR2,
47 cp_instance_pk3_value VARCHAR2,
48 cp_instance_pk4_value VARCHAR2,
49 cp_instance_pk5_value VARCHAR2,
50 cp_instance_set_id NUMBER,
51 cp_start_date DATE,
52 cp_end_date DATE) IS
53
54 SELECT 'X'
55 FROM fnd_grants grants,
56 fnd_objects obj,
57 fnd_menus menus
58 WHERE grants.grantee_key=cp_grantee_key
59 AND grants.grantee_type=cp_grantee_type
60 AND grants.menu_id=menus.menu_id
61 AND menus.menu_name=cp_menu_name
62 AND grants.object_id = obj.object_id
63 AND obj.obj_name=cp_object_name
64 AND grants.instance_type=cp_instance_type
65 AND ((grants.instance_pk1_value=cp_instance_pk1_value )
66 OR((grants.instance_pk1_value = ' *NULL*' ) AND (cp_instance_pk1_value IS NULL)))
67 AND ((grants.instance_pk2_value=cp_instance_pk2_value )
68 OR((grants.instance_pk2_value = ' *NULL*' ) AND (cp_instance_pk2_value IS NULL)))
69 AND ((grants.instance_pk3_value=cp_instance_pk3_value )
70 OR((grants.instance_pk3_value = ' *NULL*' ) AND (cp_instance_pk3_value IS NULL)))
71 AND ((grants.instance_pk4_value=cp_instance_pk4_value )
72 OR((grants.instance_pk4_value = ' *NULL*' ) AND (cp_instance_pk4_value IS NULL)))
73 AND ((grants.instance_pk5_value=cp_instance_pk5_value )
74 OR((grants.instance_pk5_value = ' *NULL*' ) AND (cp_instance_pk5_value IS NULL)))
75 AND ((grants.instance_set_id=cp_instance_set_id )
76 OR((grants.instance_set_id = ' *NULL*' ) AND (cp_instance_set_id IS NULL)))
77 AND (((grants.start_date<=cp_start_date )
78 AND (( grants.end_date = '*NULL*') OR (cp_start_date <=grants.end_date )))
79 OR ((grants.start_date >= cp_start_date )
80 AND (( cp_end_date IS NULL) OR (cp_end_date >=grants.start_date))));
81
82 v_start_date DATE := sysdate;
83
84 BEGIN
85 if (p_start_date IS NULL) THEN
86 v_start_date := sysdate;
87 else
88 v_start_date := p_start_date;
89 end if;
90 IF( p_instance_type <> 'INSTANCE') THEN
91 l_instance_type:='SET';
92 ELSE
93 l_instance_type:=p_instance_type;
94 END IF;
95
96
97 OPEN get_party_type (cp_party_id =>p_party_id);
98 FETCH get_party_type INTO l_grantee_type;
99 CLOSE get_party_type;
100 IF( p_party_id = -1000) THEN
101 l_grantee_type :='GLOBAL';
102 l_grantee_key:='HZ_GLOBAL:'||p_party_id;
103 ELSIF (l_grantee_type ='PERSON') THEN
104 l_grantee_type:='USER';
105 l_grantee_key:='HZ_PARTY:'||p_party_id;
106 ELSIF (l_grantee_type ='GROUP') THEN
107 l_grantee_type:='GROUP';
108 l_grantee_key:='HZ_GROUP:'||p_party_id;
109 ELSIF (l_grantee_type ='ORGANIZATION') THEN
110 l_grantee_type:='COMPANY';
111 l_grantee_key:='HZ_COMPANY:'||p_party_id;
112 ELSE
113 null;
114 END IF;
115
116 OPEN check_fnd_grant_exist(cp_grantee_key => l_grantee_key,
117 cp_grantee_type => l_grantee_type,
118 cp_menu_name => p_role_name,
119 cp_object_name => p_object_name,
120 cp_instance_type => l_instance_type,
121 cp_instance_pk1_value => p_instance_pk1_value,
122 cp_instance_pk2_value => p_instance_pk2_value,
123 cp_instance_pk3_value => p_instance_pk3_value,
124 cp_instance_pk4_value => p_instance_pk4_value,
125 cp_instance_pk5_value => p_instance_pk5_value,
126 cp_instance_set_id => p_instance_set_id,
127 cp_start_date => v_start_date,
128 cp_end_date => p_end_date);
129
130 FETCH check_fnd_grant_exist INTO l_dummy;
131 IF( check_fnd_grant_exist%NOTFOUND) THEN
132 fnd_grants_pkg.grant_function(
133 p_api_version => 1.0,
134 p_menu_name => p_role_name ,
135 p_object_name => p_object_name,
136 p_instance_type => l_instance_type,
137 p_instance_set_id => p_instance_set_id,
138 p_instance_pk1_value => p_instance_pk1_value,
139 p_instance_pk2_value => p_instance_pk2_value,
140 p_instance_pk3_value => p_instance_pk3_value,
141 p_instance_pk4_value => p_instance_pk4_value,
142 p_instance_pk5_value => p_instance_pk5_value,
143 p_grantee_type => l_grantee_type,
144 p_grantee_key => l_grantee_key,
145 p_start_date => v_start_date,
146 p_end_date => p_end_date,
147 p_program_name => null,
148 p_program_tag => null,
149 x_grant_guid => x_grant_guid,
150 x_success => x_return_status,
151 x_errorcode => x_errorcode
152 );
153 ELSE
154 x_return_status:='F';
155 END IF;
156
157 CLOSE check_fnd_grant_exist;
158
159 END grant_role_guid;
160
161
162
163 -- Function Compatible_Change_Order_Type
164 -- The new change order type must be compatible (or same as) with the old change order type
165
166 PROCEDURE Compatible_Change_Order_Type
167 ( p_new_change_order_type_id IN NUMBER
168 , p_change_notice IN VARCHAR2
169 , p_organization_id IN NUMBER
170 , x_change_order_type_same OUT NOCOPY NUMBER
171 , x_err_text OUT NOCOPY VARCHAR2
172 )
173 IS
174 l_new_assembly_type NUMBER := 0;
175 l_assembly_type NUMBER := 0;
176 l_err_text VARCHAR2(2000) := NULL;
177 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
178 l_eng_item_flag VARCHAR2(1) := 'n';
179 CURSOR eng_item_cur IS
180 SELECT 'y'
181 FROM mtl_system_items
182 WHERE inventory_item_id =
183 (select revised_item_id from eng_revised_items
184 where change_notice = p_change_notice
185 and organization_id = organization_id)
186 AND organization_id = p_organization_id
187 AND eng_item_flag = 'Y';
188 BEGIN
189 l_assembly_type := ENG_Globals.Get_ECO_Assembly_Type ( p_change_notice => p_change_notice
190 , p_organization_id => p_organization_id
191 );
192 select assembly_type
193 into l_new_assembly_type
194 from eng_change_order_types
195 where change_order_type_id =
196 p_new_change_order_type_id;
197
198 IF l_new_assembly_type = l_assembly_type
199 THEN
200 x_change_order_type_same := 1;
201 ELSE
202 IF l_new_assembly_type = 1
203 THEN
204 OPEN eng_item_cur;
205 FETCH eng_item_cur into l_eng_item_flag;
206 CLOSE eng_item_cur;
207
208 IF l_eng_item_flag = 'y'
209 THEN
210 x_change_order_type_same := 0;
211 ELSE
212 x_change_order_type_same := 1;
213 END IF;
214 END IF;
215 END IF;
216
217 EXCEPTION
218
219 WHEN OTHERS THEN
220 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
221 THEN
222 l_err_text := G_PKG_NAME || ' : (Compatible_Change_Order_Type) -
223 Change_Notice ' || substrb(SQLERRM,1,200);
224 Error_Handler.Add_Error_Token
225 ( p_Message_Text => l_err_text
226 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
227 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
228 );
229 END IF;
230
231 x_change_order_type_same := -1;
232
233 END Compatible_Change_Order_Type;
234
235 --Procedure Check_Delete
236
237 PROCEDURE Check_Delete
238 ( p_eco_rec IN ENG_ECO_PUB.Eco_Rec_Type
239 , p_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_Unexposed_Rec_Type
240 , x_return_status OUT NOCOPY VARCHAR2
241 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
242 )
243 IS
244 l_Token_Tbl Error_Handler.Token_Tbl_Type;
245 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
246 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
247
248 l_ri_exists NUMBER := 0;
249 CURSOR GetRevisedItems IS
250 SELECT 'x'
251 FROM eng_revised_items
252 WHERE change_notice = p_ECO_rec.ECO_Name
253 AND organization_id = p_Unexp_ECO_rec.organization_id;
254 BEGIN
255
256 l_token_tbl(1).token_name := 'ECO_NAME';
257 l_token_tbl(1).token_value := p_ECO_Rec.ECO_Name;
258
259 FOR l_ritem_exists IN GetRevisedItems LOOP
260 l_ri_exists := 1;
261 END LOOP;
262
263 -- ECO cannot be deleted if revised items exist
264
265 IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_DELETE AND
266 (l_ri_exists = 1 OR p_unexp_eco_rec.approval_status_type in (2,3,5))
267 THEN
268 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
269 THEN
270 Error_Handler.Add_Error_Token
271 ( p_Message_Name => 'ENG_ECO_CANNOT_DELETE'
272 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
273 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
274 , p_Token_Tbl => l_Token_Tbl
275 );
276 END IF;
277 l_return_status := FND_API.G_RET_STS_ERROR;
278 END IF;
279
280 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
281 x_return_status := l_return_status;
282
283 END Check_Delete;
284
285
286 -- Added by MK on 09/01/2000
287 -- Function: Check if other ECO's unimplemented Rev Comp referencing Op Seq Num
288 -- exists in this ECO for Cancel
289 --
290 FUNCTION Check_Ref_Rev_Comp_For_ECO
291 ( p_eco_name IN VARCHAR2
292 , p_organization_id IN NUMBER
293 )
294
295 RETURN BOOLEAN
296
297 IS
298 -- Modified query for performance bug 4251776
299 CURSOR l_ref_rev_cmp_csr ( p_eco_name VARCHAR2
300 , p_organization_id NUMBER
301 )
302
303 IS
304 SELECT 'Rev Comp referencing Seq Num exists'
305 FROM SYS.DUAL
306 WHERE EXISTS (SELECT NULL
307 FROM ENG_ENGINEERING_CHANGES eec1
308 , ENG_REVISED_ITEMS eri1
309 , ENG_REVISED_ITEMS eri2
310 WHERE eri1.revised_item_id = eri2.revised_item_id
311 AND eri1.organization_id = eec1.organization_id
312 AND eri1.change_notice = eec1.change_notice
313 AND eec1.change_notice <> p_eco_name
314 AND eec1.organization_id = p_organization_id
315 AND eri2.organization_id = p_organization_id
316 AND eri2.change_notice = p_eco_name
317 AND EXISTS (SELECT NULL
318 FROM BOM_INVENTORY_COMPONENTS bic
319 , BOM_OPERATION_SEQUENCES bos
320 WHERE bic.implementation_date IS NULL
321 AND bic.operation_seq_num = bos.operation_seq_num
322 AND bic.bill_sequence_id = eri1.bill_sequence_id
323 AND bos.revised_item_sequence_id = eri2.revised_item_sequence_id
324 AND bos.routing_sequence_id = eri2.routing_sequence_id
325 )
326 ) ;
327
328 l_ret_status BOOLEAN := TRUE ;
329
330 BEGIN
331 FOR l_ref_rev_cmp_rec IN l_ref_rev_cmp_csr
332 ( p_eco_name
333 , p_organization_id
334 )
335 LOOP
336 l_ret_status := FALSE ;
337 END LOOP;
338
339 -- If the loop does not execute then
340 -- return false
341 RETURN l_ret_status ;
342
343
344 END Check_Ref_Rev_Comp_For_ECO ;
345
346 -- Added by MK on 11/29/2000
347 -- Function: Check if Org Hierarchy is valid
348 --
349 FUNCTION Val_Org_Hierarchy
350 ( p_org_hierarchy IN VARCHAR2
351 , p_org_id IN NUMBER
352 )
353
354 RETURN BOOLEAN
355 IS
356
357 CURSOR l_org_hierarchy_csr ( p_org_hierarchy VARCHAR2
358 --, l_org_name VARCHAR2
359 )
360
361 IS
362
363
364 SELECT 'Valid'
365 FROM SYS.DUAL
366 WHERE EXISTS ( SELECT 'Valid'
367 FROM per_organization_structures
368 WHERE inv_orghierarchy_pvt.org_hierarchy_access
369 (p_org_hierarchy) = 'Y'
370 AND inv_orghierarchy_pvt.org_hierarchy_level_access
371 (p_org_hierarchy,p_org_id) = 'Y'
372 ) ;
373
374
375 l_ret_status BOOLEAN := FALSE ;
376 -- l_org_name VARCHAR2(60) := NULL ;
377
378 BEGIN
379 /* begin
380 SELECT organization_name INTO l_org_name
381 FROM org_organization_definitions
382 WHERE organization_id = p_org_id ;
383 end ;
384 */
385
386 IF Bom_Globals.Get_Debug = 'Y' THEN
387 Error_Handler.Write_Debug('Check if Org Hierarchy is valid in org : ' || p_org_id );
388 END IF;
389
390
391 FOR l_org_hierarchy_rec IN l_org_hierarchy_csr
392 ( p_org_hierarchy
393 --, l_org_name
394 )
395 LOOP
396
397 IF Bom_Globals.Get_Debug = 'Y' THEN
398 Error_Handler.Write_Debug('Org hierarchy is valid' );
399 END IF;
400
401 l_ret_status := TRUE ;
402 END LOOP;
403
404 -- If the loop does not execute then
405 -- return false
406 RETURN l_ret_status ;
407
408 END Val_Org_Hierarchy ;
409
410
411 --Bug 2921474
412
413
414 FUNCTION Get_Change_Id
415 ( p_change_notice IN VARCHAR2
416 , p_org_id IN NUMBER
417 )
418 RETURN NUMBER
419 IS
420 l_id NUMBER;
421 BEGIN
422
423 SELECT change_id
424 INTO l_id
425 FROM eng_engineering_changes
426 WHERE change_notice = p_change_notice
427 AND organization_id = p_org_id;
428
429 RETURN l_id;
430
431 EXCEPTION
432
433 WHEN NO_DATA_FOUND THEN
434 RETURN NULL;
435
436 WHEN OTHERS THEN
437 RETURN FND_API.G_MISS_NUM;
438
439 END Get_Change_Id;
440
441
442
443
444
445
446
447
448
449
450
451
452
453 -- Procedure Entity
454
455 PROCEDURE Check_Entity
456 ( x_return_status OUT NOCOPY VARCHAR2
457 , x_err_text OUT NOCOPY VARCHAR2
458 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
459 , p_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
460 , p_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_Unexposed_Rec_Type
461 , p_old_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
462 , p_old_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_Unexposed_Rec_Type
463 , p_control_rec IN BOM_BO_PUB.Control_Rec_Type :=
464 BOM_BO_PUB.G_DEFAULT_CONTROL_REC
465
466 )
467 IS
468 l_err_text VARCHAR2(2000) := NULL;
469 l_Token_Tbl Error_Handler.Token_Tbl_Type;
470 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
471 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
472 l_dummy VARCHAR2(10) := NULL;
473 l_process_name VARCHAR2(30) := NULL;
474 l_rev_items_scheduled BOOLEAN := FALSE;
475 l_change_order_type_same NUMBER := 0;
476 l_change_order_access BOOLEAN := FALSE;
477 -- requestor_role_id ,assignee_role_id now not in table ENG_CHANGE_ORDER_TYPES
478 /* l_requestor_role_id NUMBER;
479 l_assignee_role_id NUMBER; */
480 l_requestor_role_name VARCHAR2(30);
481 l_assignee_role_name VARCHAR2(30);
482 l_errorcode NUMBER;
483 l_grant_guid fnd_grants.grant_guid%TYPE;
484
485 stmt_num NUMBER := 0;
486
487 l_ri_exists NUMBER := 0;
488 CURSOR GetRevisedItems IS
489 SELECT 'x'
490 FROM eng_revised_items
491 WHERE change_notice = p_ECO_rec.ECO_Name
492 AND organization_id = p_Unexp_ECO_rec.organization_id;
493
494 l_ri_sched_exists NUMBER := 0;
495 CURSOR GetScheduledRevItems IS
496 SELECT 'x'
497 FROM eng_revised_items
498 WHERE change_notice = p_ECO_rec.ECO_Name
499 AND organization_id = p_Unexp_ECO_rec.organization_id
500 AND status_type = 4;
501
502 CURSOR GetRoleName(p_role_id NUMBER)
503 IS
504 SELECT menu_name FROM fnd_menus
505 WHERE menu_id = p_role_id;
506
507 --Bug 2921474
508 l_cl_exists NUMBER := 0;
509 CURSOR GetChangeLines(p_change_id NUMBER) IS
510 SELECT 'x'
511 FROM eng_change_lines
512 WHERE change_id = p_change_id;
513
514 l_er_exists NUMBER := 0;
515 CURSOR GetEcoRevisions IS
516 SELECT 'x'
517 FROM ENG_CHANGE_ORDER_REVISIONS
518 WHERE change_notice = p_ECO_rec.ECO_Name
519 AND organization_id = p_Unexp_ECO_rec.organization_id;
520
521 l_change_id NUMBER := 0;
522 --End of Bug 2921474
523
524 BEGIN
525
526 l_token_tbl(1).token_name := 'ECO_NAME';
527 l_token_tbl(1).token_value := p_ECO_Rec.ECO_Name;
528
529 -- Get Workflow Process name
530
531 stmt_num := 1;
532 ENG_GLOBALS.Init_Process_Name
533 ( p_change_order_type_id => p_unexp_ECO_rec.change_order_type_id
534 , p_priority_code => p_ECO_rec.priority_code
535 , p_organization_id => p_unexp_ECO_rec.organization_id
536 );
537
538 l_process_name := ENG_Globals.Get_Process_Name;
539
540 --
541 -- Check required attributes.
542 --
543
544 --
545 -- Entity Validation.
546 --
547
548 stmt_num := 5.5;
549
550 FOR l_ritem_exists IN GetRevisedItems LOOP
551 l_ri_exists := 1;
552 END LOOP;
553
554
555 --Bug 2921474
556 l_change_id := Get_Change_Id(p_ECO_Rec.ECO_Name,p_unexp_ECO_rec.organization_id);
557
558 FOR l_chl_exists IN GetChangeLines(l_change_id) LOOP
559 l_cl_exists := 1;
560 END LOOP;
561
562
563 FOR l_ecori_exists IN GetEcoRevisions LOOP
564 l_er_exists := 1;
565 END LOOP;
566
567 --End of Bug 2921474
568
569
570 -- ECO cannot be deleted if revised items/change line/change revision exist (irrespective of the CO status).
571
572 IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_DELETE AND
573 l_ri_exists = 1
574 THEN
575 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
576 THEN
577 Error_Handler.Add_Error_Token
578 ( p_Message_Name => 'ENG_ECO_CANNOT_DELETE'
579 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
580 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
581 , p_Token_Tbl => l_Token_Tbl
582 );
583 END IF;
584 l_return_status := FND_API.G_RET_STS_ERROR;
585 END IF;
586
587 --Bug 2921474
588 IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_DELETE AND
589 l_cl_exists =1
590 THEN
591 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
592 THEN
593 Error_Handler.Add_Error_Token
594 ( p_Message_Name => 'ENG_ECO_CANNOT_DELETE_CL'
595 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
596 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
597 , p_Token_Tbl => l_Token_Tbl
598 );
599 END IF;
600 l_return_status := FND_API.G_RET_STS_ERROR;
601 END IF;
602
603
604 IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_DELETE AND
605 l_er_exists =1
606 THEN
607 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
608 THEN
609 Error_Handler.Add_Error_Token
610 ( p_Message_Name => 'ENG_ECO_CANNOT_DELETE_ER'
611 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
612 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
613 , p_Token_Tbl => l_Token_Tbl
614 );
615 END IF;
616 l_return_status := FND_API.G_RET_STS_ERROR;
617 END IF;
618
619
620 --End of Bug 2921474
621
622 -- Put in for fix to bug 622498
623 -- Creates of records marked Cancelled are not allowed
624
625 IF p_Unexp_ECO_rec.status_type = 5 AND
626 p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_CREATE
627 THEN
628 l_Token_Tbl(2).token_name := 'STATUS_TYPE';
629 l_Token_Tbl(2).token_value := p_Unexp_ECO_rec.status_type;
630
631 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
632 THEN
633 Error_Handler.Add_Error_Token
634 ( p_Message_Name => 'ENG_ECO_STAT_MUST_NOT_BE_CNCL'
635 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
636 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
637 , p_Token_Tbl => l_Token_Tbl
638 );
639 END IF;
640 l_return_status := FND_API.G_RET_STS_ERROR;
641 END IF;
642
643
644 -- Added by MK on 09/01/2000
645 -- Put in to support ECO for Routing
646 -- Check if there is no revised operation which is referenced by
647 -- un-implemented revised component in other ECO
648 --
649
650 IF p_Unexp_ECO_rec.status_type = 5 AND
651 p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE
652 THEN
653
654 IF NOT Check_Ref_Rev_Comp_For_ECO( p_eco_name => p_eco_rec.ECO_Name
655 , p_organization_id => p_unexp_ECO_rec.organization_id
656 )
657 THEN
658 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
659 THEN
660 Error_Handler.Add_Error_Token
661 ( p_Message_Name => 'ENG_ECO_CANNOT_CNCL_FOR_REV_OP'
662 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
663 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
664 , p_Token_Tbl => l_Token_Tbl
665 );
666 END IF;
667 ---Bug 2921534
668 l_return_status := FND_API.G_RET_STS_ERROR;
669 END IF ;
670
671
672 END IF;
673
674
675
676 --
677 -- Validate attribute dependencies here.
678 --
679
680 -- Cannot have both a Workflow Process and approval list associated with the ECO
681
682 stmt_num := 9;
683 -- ERES Begin
684 /*
685 IF p_Unexp_ECO_rec.approval_list_id IS NOT NULL AND
686 l_process_name IS NOT NULL
687 THEN
688 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
689 THEN
690 Error_Handler.Add_Error_Token
691 ( p_Message_Name => 'ENG_APPROV_LIST_PROCESS_EXISTS'
692 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
693 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
694 , p_Token_Tbl => l_Token_Tbl
695 );
696 END IF;
697 l_return_status := FND_API.G_RET_STS_ERROR;
698 END IF;
699 */
700 -- ERES end
701
702 -- If there is no approval list or process associated, the approval status can only be rejected or approved
703
704 stmt_num := 10;
705 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('process : '|| l_process_name); END IF;
706 -- Added following Check as part of Fix to Bug 2815601
707 IF BOM_Globals.G_MASS_CHANGE <> 'MASSCHANGE' THEN
708 IF (p_control_rec.caller_type <> 'FORM' AND
709 p_Unexp_ECO_rec.approval_list_id IS NULL AND
710 l_process_name IS NULL AND
711 p_Unexp_ECO_rec.approval_status_type NOT IN (4,5))
712 THEN
713 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
714 THEN
715 Error_Handler.Add_Error_Token
716 ( p_Message_Name => 'ENG_APP_STATUS_REJ_APPROV'
717 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
718 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
719 , p_Token_Tbl => l_Token_Tbl
720 );
721 END IF;
722 l_return_status := FND_API.G_RET_STS_ERROR;
723 END IF;
724 END IF;
725 -- Are there any revised items that are scheduled ?
726
727 FOR l_ri_sched IN GetScheduledRevItems LOOP
728 l_ri_sched_exists := 1;
729 END LOOP;
730
731 -- ECO must be approved first for it or any of its revised items to be scheduled
732
733 IF p_Unexp_ECO_rec.approval_status_type <> 5 AND
734 (p_Unexp_ECO_rec.status_type = 4 OR
735 l_ri_sched_exists = 1)
736 THEN
737 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
738 THEN
739 l_token_tbl(1).token_name := 'ECO_NAME';
740 l_token_tbl(1).token_value := p_ECO_rec.eco_name;
741 Error_Handler.Add_Error_Token
742 ( p_Message_Name => 'ENG_ECO_MUST_BE_APPROVED'
743 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
744 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
745 , p_Token_Tbl => l_Token_Tbl
746 );
747 END IF;
748 l_return_status := FND_API.G_RET_STS_ERROR;
749 END IF;
750
751 --117
752 -- Approval list exists
753
754 IF p_Unexp_ECO_rec.approval_list_id IS NOT NULL
755 THEN
756
757 -- Approval status must be Not Submitted for Approval, Ready to Approve,
758 -- Approval Requested, Rejected, or Approved
759
760 IF p_Unexp_ECO_rec.approval_status_type NOT IN (1,2,3,4,5)
761 THEN
762 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
763 THEN
764 l_token_tbl(2).token_name := 'APPROVAL_STATUS_TYPE';
765 l_token_tbl(2).token_value := p_Unexp_ECO_rec.Approval_Status_Type;
766 Error_Handler.Add_Error_Token
767 ( p_Message_Name => 'ENG_APP_LIST_APP_STAT_INVALID'
768 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
769 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
770 , p_Token_Tbl => l_Token_Tbl
771 );
772 END IF;
773 l_return_status := FND_API.G_RET_STS_ERROR;
774 END IF;
775
776 -- Approval list must not be changed if Approval Requested
777
778 IF p_old_Unexp_ECO_rec.approval_status_type = 3 AND
779 (p_ECO_rec.Transaction_Type = ENG_GLOBALS.G_OPR_UPDATE AND
780 NVL(p_Unexp_ECO_rec.approval_list_id, 0) <> NVL(p_old_Unexp_ECO_rec.approval_list_id, 0))
781 THEN
782 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
783 THEN
784 Error_Handler.Add_Error_Token
785 ( p_Message_Name => 'ENG_APP_LIST_MUST_NOT_CHANGE'
786 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
787 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
788 , p_Token_Tbl => l_Token_Tbl
789 );
790 END IF;
791 l_return_status := FND_API.G_RET_STS_ERROR;
792 END IF;
793
794 -- Status Type must not be changed if Approval Requested
795 -- FROM ENGFMECO.pld (Procedure Initialize_Row)
796
797 IF p_old_Unexp_ECO_rec.approval_status_type = 3 AND
798 (p_ECO_rec.Transaction_Type = ENG_GLOBALS.G_OPR_UPDATE AND
799 p_Unexp_ECO_rec.status_type <> p_old_Unexp_ECO_rec.status_type)
800 THEN
801 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
802 THEN
803 Error_Handler.Add_Error_Token
804 ( p_Message_Name => 'ENG_STAT_TYPE_MUST_NOT_CHANGE'
805 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
806 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
807 , p_Token_Tbl => l_Token_Tbl
808 );
809 END IF;
810 l_return_status := FND_API.G_RET_STS_ERROR;
811 END IF;
812
813
814
815
816 END IF;
817
818 -- Workflow Process exists
819
820 IF l_process_name IS NOT NULL THEN
821
822 -- Approval status must not be Approval Requested, Rejected, Approved,
823 -- or Processing Error
824
825 IF p_control_rec.caller_type <> 'FORM' AND
826 (p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_CREATE
827 OR
828 (p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE AND
829 p_Unexp_ECO_rec.approval_status_type <> p_old_Unexp_ECO_rec.approval_status_type))
830 AND
831 p_Unexp_ECO_rec.approval_status_type IN (2,3,4,5,7)
832 THEN
833 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
834 THEN
835 l_token_tbl(2).token_name := 'APPROVAL_STATUS_TYPE';
836 l_token_tbl(2).token_value := p_Unexp_ECO_rec.Approval_Status_Type;
837 Error_Handler.Add_Error_Token
838 ( p_Message_Name => 'ENG_PROC_APP_STAT_INVALID'
839 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
840 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
841 , p_Token_Tbl => l_Token_Tbl
842 );
843 END IF;
844 l_return_status := FND_API.G_RET_STS_ERROR;
845 END IF;
846
847 -- Cannot update status to 'Scheduled'
848
849 IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE AND
850 p_Unexp_ECO_rec.status_type <> p_old_Unexp_ECO_rec.status_type AND
851 p_Unexp_ECO_rec.status_type = 4
852 THEN
853 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
854 THEN
855 Error_Handler.Add_Error_Token
856 ( p_Message_Name => 'ENG_PROC_CANNOT_SCHEDULE'
857 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
858 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
859 );
860 END IF;
861 l_return_status := FND_API.G_RET_STS_ERROR;
862 END IF;
863
864 -- Cannot update priority if the ECO or any of its revised items have been scheduled
865
866 IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE AND
867 NVL(p_ECO_rec.priority_code, 'NONE') <> NVL(p_old_ECO_rec.priority_code, 'NONE') AND
868 (p_old_Unexp_ECO_rec.status_type = 4
869 OR l_ri_sched_exists = 1)
870 THEN
871 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
872 THEN
873 l_token_tbl(1).token_value := 'ECO_NAME';
874 l_token_tbl(1).token_value := p_eco_rec.eco_name;
875 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('eco name: ' || p_eco_rec.eco_name); END IF;
876 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('priority_code: ' || p_Eco_rec.priority_code); END IF;
877 l_Token_Tbl(2).Token_Name := 'PRIORITY_CODE';
878 l_Token_Tbl(2).Token_Value := p_ECO_rec.priority_code;
879 Error_Handler.Add_Error_Token
880 ( p_Message_Name => 'ENG_ECO_REV_ITEMS_SCHED'
881 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
882 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
883 , p_Token_Tbl => l_Token_Tbl
884 );
885 END IF;
886 l_return_status := FND_API.G_RET_STS_ERROR;
887 END IF;
888
889 END IF;
890
891 -- Must not have cancellation details if ECO not cancelled
892
893 IF p_Unexp_ECO_rec.status_type <> 5 AND
894 p_ECO_rec.cancellation_comments IS NOT NULL
895 THEN
896 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
897 THEN
898 Error_Handler.Add_Error_Token
899 ( p_Message_Name => 'ENG_ECO_CANCL_DETAILS_EXIST'
900 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
901 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
902 , p_Token_Tbl => l_Token_Tbl
903 );
904 END IF;
905 l_return_status := FND_API.G_RET_STS_ERROR;
906 END IF;
907
908 IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE
909 AND
910 NVL(p_Unexp_ECO_rec.change_order_type_id, 0) <> p_old_Unexp_ECO_rec.change_order_type_id
911 THEN
912
913
914 Compatible_Change_Order_Type
915 ( p_new_change_order_type_id => p_Unexp_ECO_rec.change_order_type_id
916 , p_change_notice => p_ECO_rec.ECO_Name
917 , p_organization_id => p_Unexp_ECO_rec.organization_id
918 , x_change_order_type_same => l_change_order_type_same
919 , x_err_text => x_err_text
920 );
921 -- If there is a new change order type, its assembly type must be compatible with
922 -- the assembly type of any existing revised items
923
924 IF l_change_order_type_same = 0
925 THEN
926 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
927 THEN
928 Error_Handler.Add_Error_Token
929 ( p_Message_Name => 'ENG_ECO_CANCL_DETAILS_EXIST'
930 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
931 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
932 , p_Token_Tbl => l_Token_Tbl);
933 END IF;
934 l_return_status := FND_API.G_RET_STS_ERROR;
935 ELSIF l_change_order_type_same = -1
936 THEN
937 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
938 END IF;
939
940
941 END IF;
942
943
944 /* Added by MK on 11/29/00 Bug #1508078
945 -- Entity validation for hierarchy_flag and organization_hierarchy
946 -- If approval_status_type is 5:Approved, these columns are not updatable
947 */
948 IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE
949 -- AND p_ECO_rec.approval_status_type = 5
950 AND ( --NVL(p_ECO_rec.hierarchy_flag, 2) <> NVL(p_old_ECO_rec.hierarchy_flag,2) OR
951 NVL(p_ECO_rec.organization_hierarchy, FND_API.G_MISS_CHAR) <>
952 NVL(p_old_ECO_rec.organization_hierarchy, FND_API.G_MISS_CHAR)
953 )
954 THEN
955 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
956 THEN
957 Error_Handler.Add_Error_Token
958 ( p_Message_Name => 'ENG_HIERARCHY_MUST_NOT_CHANGE'
959 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
960 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
961 , p_Token_Tbl => l_Token_Tbl
962 );
963 END IF;
964
965 l_return_status := FND_API.G_RET_STS_ERROR;
966
967 END IF ;
968
969 IF p_ECO_rec.organization_hierarchy IS NOT NULL
970 AND ( p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_CREATE
971 OR (p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE
972 AND NVL(p_ECO_rec.organization_hierarchy, FND_API.G_MISS_CHAR) <>
973 NVL(p_old_ECO_rec.organization_hierarchy, FND_API.G_MISS_CHAR)
974 )
975 )
976 AND NOT Val_Org_Hierarchy( p_org_hierarchy => p_ECO_rec.organization_hierarchy
977 , p_org_id => p_Unexp_ECO_rec.organization_id )
978 THEN
979
980 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
981 THEN
982 Error_Handler.Add_Error_Token
983 ( p_Message_Name => 'ENG_ORG_HIERARCHY_INVALID'
984 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
985 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
986 , p_Token_Tbl => l_Token_Tbl
987 );
988 END IF;
989
990 l_return_status := FND_API.G_RET_STS_ERROR;
991
992 END IF ;
993
994 -- Eng Change New Validations for Change Mgmt Type and Assignee
995
996 -- Change Mgmt Type can not be changed thr BO
997 IF (p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE
998 AND NVL(p_Unexp_ECO_rec.Change_Mgmt_Type_Code, FND_API.G_MISS_CHAR) <>
999 NVL(p_old_Unexp_ECO_rec.Change_Mgmt_Type_Code, FND_API.G_MISS_CHAR)
1000 )
1001 THEN
1002
1003 IF Bom_Globals.Get_Debug = 'Y' THEN
1004 Error_Handler.Write_Debug('Validation that Change Mgmt Type cannot be chagned . . . ' );
1005 Error_Handler.Write_Debug('Old Change Mgmt Type: ' || p_old_Unexp_ECO_rec.Change_Mgmt_Type_Code);
1006 Error_Handler.Write_Debug('New Change Mgmt Type: ' || p_Unexp_ECO_rec.Change_Mgmt_Type_Code);
1007 END IF;
1008
1009 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1010 THEN
1011 Error_Handler.Add_Error_Token
1012 ( p_Message_Name => 'ENG_CHANGE_MGMT_MUST_NOT_UPD'
1013 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1014 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1015 , p_Token_Tbl => l_Token_Tbl
1016 );
1017 END IF;
1018
1019 l_return_status := FND_API.G_RET_STS_ERROR;
1020
1021 END IF ;
1022
1023 /* commented as assignee_role_id ,requestor_role_id don't exist in ENG_CHANGE_ORDER_TYPES table
1024 IF p_ECO_rec.transaction_type = Eng_Globals.G_OPR_CREATE
1025 THEN
1026
1027 IF p_Unexp_ECO_Rec.Requestor_Id IS NULL AND p_Unexp_ECO_Rec.Assignee_Id IS NULL
1028 THEN
1029 Error_Handler.Add_Error_Token
1030 ( p_Message_Name => 'ENG_CHANGE_BOTH_RESP_NULL'
1031 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1032 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1033 , p_Token_Tbl => l_Token_Tbl
1034 );
1035
1036 l_return_status := FND_API.G_RET_STS_ERROR;
1037 END IF;
1038
1039
1040 SELECT requestor_role_id, assignee_role_id
1041 INTO l_requestor_role_id, l_assignee_role_id
1042 FROM eng_change_order_types
1043 WHERE change_order_type_id = p_Unexp_ECO_Rec.change_order_type_id;
1044
1045 IF l_requestor_role_id IS NOT NULL
1046 THEN
1047 OPEN GetRoleName (p_role_id => l_requestor_role_id);
1048 FETCH GetRoleName INTO l_requestor_role_name;
1049 CLOSE GetRoleName;
1050
1051 -- assign requestor grant
1052 grant_role_guid
1053 ( p_api_version => 1.0
1054 ,p_role_name => l_requestor_role_name
1055 ,p_object_name => 'ENG_CHANGE'
1056 ,p_instance_type => 'INSTANCE'
1057 ,p_instance_set_id => NULL
1058 ,p_instance_pk1_value => to_char(p_Unexp_ECO_Rec.change_id)
1059 ,p_instance_pk2_value => NULL
1060 ,p_instance_pk3_value => NULL
1061 ,p_instance_pk4_value => NULL
1062 ,p_instance_pk5_value => NULL
1063 ,p_party_id => p_Unexp_ECO_Rec.Requestor_Id
1064 ,p_start_date => sysdate
1065 ,p_end_date => NULL
1066 ,x_return_status => l_return_status
1067 ,x_errorcode => l_errorcode
1068 ,x_grant_guid => l_grant_guid
1069 );
1070
1071 IF l_return_status = FND_API.G_TRUE
1072 OR l_return_status = FND_API.G_FALSE
1073 THEN
1074 l_return_status := FND_API.G_RET_STS_SUCCESS;
1075 ELSE
1076 Error_Handler.Add_Error_Token
1077 ( p_Message_Name => 'ENG_CHANGE_REQUESTOR_GRANT'
1078 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1079 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1080 , p_Token_Tbl => l_Token_Tbl
1081 );
1082 END IF;
1083 END IF;
1084
1085 IF l_assignee_role_id IS NOT NULL
1086 THEN
1087 OPEN GetRoleName (p_role_id => l_assignee_role_id);
1088 FETCH GetRoleName INTO l_assignee_role_name;
1089 CLOSE GetRoleName;
1090
1091 -- assign assignee grant
1092 grant_role_guid
1093 ( p_api_version => 1.0
1094 ,p_role_name => l_assignee_role_name
1095 ,p_object_name => 'ENG_CHANGE'
1096 ,p_instance_type => 'INSTANCE'
1097 ,p_instance_set_id => NULL
1098 ,p_instance_pk1_value => to_char(p_Unexp_ECO_Rec.change_id)
1099 ,p_instance_pk2_value => NULL
1100 ,p_instance_pk3_value => NULL
1101 ,p_instance_pk4_value => NULL
1102 ,p_instance_pk5_value => NULL
1103 ,p_party_id => p_Unexp_ECO_Rec.Assignee_Id
1104 ,p_start_date => sysdate
1105 ,p_end_date => NULL
1106 ,x_return_status => l_return_status
1107 ,x_errorcode => l_errorcode
1108 ,x_grant_guid => l_grant_guid
1109 );
1110
1111 IF l_return_status = FND_API.G_TRUE
1112 OR l_return_status = FND_API.G_FALSE
1113 THEN
1114 l_return_status := FND_API.G_RET_STS_SUCCESS;
1115 ELSE
1116 Error_Handler.Add_Error_Token
1117 ( p_Message_Name => 'ENG_CHANGE_ASSIGNEE_GRANT'
1118 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1119 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1120 , p_Token_Tbl => l_Token_Tbl
1121 );
1122 END IF;
1123 END IF;
1124 END IF;
1125 */
1126 -- Done validating entity
1127
1128 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1129 x_return_status := l_return_status;
1130
1131 EXCEPTION
1132
1133 WHEN OTHERS THEN
1134
1135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1136
1137 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1138 THEN
1139 l_err_text := G_PKG_NAME || ' : (Entity Validation) ' || substrb(SQLERRM,1,200);
1140 Error_Handler.Add_Error_Token
1141 ( p_Message_Text => l_err_text
1142 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1143 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1144 );
1145 END IF;
1146
1147 END Check_Entity;
1148
1149 -- Procedure Check_Attributes
1150
1151 PROCEDURE Check_Attributes
1152 ( x_return_status OUT NOCOPY VARCHAR2
1153 , x_err_text OUT NOCOPY VARCHAR2
1154 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1155 , p_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
1156 , p_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_Unexposed_Rec_Type
1157 , p_old_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
1158 , p_old_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_Unexposed_Rec_Type
1159 , p_change_line_tbl IN ENG_Eco_PUB.Change_Line_Tbl_Type ----Bug 2908248
1160 , p_revised_item_tbl IN ENG_Eco_PUB.Revised_Item_Tbl_Type --Bug 2908248
1161 )
1162 IS
1163 l_err_text VARCHAR2(2000) := '';
1164 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1165 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1166 l_disable_date DATE;
1167 ----Bug 2908248
1168 l_change_line_rec Eng_Eco_Pub.Change_Line_Rec_Type ;
1169 l_revised_item_rec ENG_Eco_PUB.Revised_Item_Rec_Type ;
1170 l_change_id NUMBER :=0;
1171
1172 l_cl_cico_count NUMBER :=0; --count of cancelled/implemented/completed chnage lines
1173 l_cl_count NUMBER :=0;
1174 l_cl_up_count NUMBER :=0;
1175 l_up_ch NUMBER :=0;
1176 l_rev_item_cnt NUMBER :=0; --count of implemented revised items
1177
1178
1179 l_up_cr NUMBER :=0;
1180 l_er_cico_count NUMBER :=0; --count of cancelled/implemented/completed revisd items
1181
1182 l_er_count NUMBER :=0;
1183 l_er_up_count NUMBER :=0;
1184
1185
1186
1187 CURSOR lines_for_eco( p_change_id NUMBER) IS
1188 SELECT status_code ,sequence_number , name
1189 FROM eng_change_lines_vl
1190 WHERE eng_change_lines_vl.change_id = p_change_id
1191 and sequence_number<> -1;
1192
1193 CURSOR revised_items_for_eco( p_change_id NUMBER) IS
1194 SELECT STATUS_TYPE ,scheduled_date
1195 FROM eng_revised_items
1196 WHERE eng_revised_items.change_id = p_change_id;
1197
1198
1199
1200 ----Bug 2908248
1201
1202
1203
1204 --11.5.10
1205
1206 cursor GetValidStatusCodes(p_change_order_type_id NUMBER) IS
1207 SELECT status_code
1208 FROM eng_lifecycle_statuses
1209 where ENTITY_NAME='CHANGE_TYPE'
1210 and entity_id1 = p_change_order_type_id;
1211
1212
1213
1214 cursor GetValidPriorities(p_change_order_type_id NUMBER) IS
1215 SELECT priority_code
1216 FROM eng_change_type_priorities
1217 where change_type_id = p_change_order_type_id;
1218
1219
1220 cursor GetValidReasons(p_change_order_type_id NUMBER) IS
1221 SELECT reason_code
1222 FROM eng_change_type_reasons
1223 where change_type_id = p_change_order_type_id;
1224
1225
1226
1227 l_valid_status NUMBER;
1228 l_valid_priority NUMBER;
1229 l_valid_reason NUMBER;
1230 l_base_change_mgmt_type_code ENG_CHANGE_ORDER_TYPES.base_change_mgmt_type_code%TYPE;
1231
1232 BEGIN
1233
1234 l_token_tbl(1).token_name := 'ECO_NAME';
1235 l_token_tbl(1).token_value := p_ECO_rec.ECO_Name;
1236
1237 x_return_status := FND_API.G_RET_STS_SUCCESS;
1238
1239 -- Validate ECO attributes
1240
1241 IF p_Unexp_ECO_rec.Approval_Status_Type = FND_API.G_MISS_NUM
1242 THEN
1243 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1244 THEN
1245 Error_Handler.Add_Error_Token
1246 ( p_Message_Name => 'ENG_APPROVAL_STAT_TYPE_NULL'
1247 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1248 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1249 , p_Token_Tbl => l_Token_Tbl
1250 );
1251 END IF;
1252 x_return_status := FND_API.G_RET_STS_ERROR;
1253 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1254 END IF;
1255
1256 IF p_Unexp_ECO_rec.approval_status_type IS NOT NULL AND
1257 ( p_Unexp_ECO_rec.approval_status_type <>
1258 p_old_Unexp_ECO_rec.approval_status_type OR
1259 p_old_Unexp_ECO_rec.approval_status_type IS NULL )
1260 THEN
1261
1262 IF NOT ENG_Validate.Approval_Status_Type
1263 ( p_Unexp_ECO_rec.approval_status_type
1264 , x_err_text => l_err_text
1265 ) OR
1266 p_Unexp_ECO_rec.approval_status_type = 6
1267 THEN
1268 IF l_err_text = ''
1269 THEN
1270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1271 END IF;
1272
1273 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1274 THEN
1275 l_token_tbl(2).token_name := 'APPROVAL_STATUS_TYPE';
1276 l_token_tbl(2).token_value := p_Unexp_ECO_Rec.Approval_Status_Type;
1277 Error_Handler.Add_Error_Token
1278 ( p_Message_Name => 'ENG_APPROVAL_STAT_INVALID'
1279 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1280 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1281 , p_Token_Tbl => l_Token_Tbl
1282 );
1283 END IF;
1284 x_return_status := FND_API.G_RET_STS_ERROR;
1285 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1286 END IF;
1287 END IF;
1288
1289 IF p_Unexp_ECO_rec.responsible_org_id IS NOT NULL AND
1290 ( p_Unexp_ECO_rec.responsible_org_id <>
1291 p_old_Unexp_ECO_rec.responsible_org_id OR
1292 p_old_Unexp_ECO_rec.responsible_org_id IS NULL )
1293 THEN
1294
1295 IF NOT ENG_Validate.Responsible_Org
1296 ( p_responsible_org_id => p_Unexp_ECO_rec.responsible_org_id
1297 , p_current_org_id => p_Unexp_ECO_rec.organization_id
1298 , x_err_text => l_err_text
1299 )
1300 THEN
1301 IF l_err_text = ''
1302 THEN
1303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1304 END IF;
1305
1306 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1307 THEN
1308 l_token_tbl(2).token_name := 'ECO_DEPARTMENT';
1309 l_token_tbl(2).token_value := p_ECO_Rec.ECO_Department_Name;
1310 Error_Handler.Add_Error_Token
1311 ( p_Message_Name => 'ENG_RESP_ORG_DISABLED'
1312 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1313 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1314 , p_Token_Tbl => l_Token_Tbl
1315 );
1316 END IF;
1317 x_return_status := FND_API.G_RET_STS_ERROR;
1318 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1319 END IF;
1320 END IF;
1321
1322 IF p_Unexp_ECO_Rec.Status_Type = FND_API.G_MISS_NUM
1323 THEN
1324 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1325 THEN
1326 Error_Handler.Add_Error_Token
1327 ( p_Message_Name => 'ENG_ECO_STAT_TYPE_MISSING'
1328 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1329 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1330 , p_Token_Tbl => l_Token_Tbl
1331 );
1332 END IF;
1333 x_return_status := FND_API.G_RET_STS_ERROR;
1334 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1335 END IF;
1336
1337 IF p_Unexp_ECO_rec.status_type IS NOT NULL AND
1338 ( p_Unexp_ECO_rec.status_type <>
1339 p_old_Unexp_ECO_rec.status_type OR
1340 p_old_Unexp_ECO_rec.status_type IS NULL )
1341 THEN
1342
1343 IF NOT ENG_Validate.Status_Type
1344 ( p_status_type => p_Unexp_ECO_rec.status_type
1345 , x_err_text => l_err_text
1346 )
1347 THEN
1348 IF l_err_text = ''
1349 THEN
1350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1351 END IF;
1352
1353 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1354 THEN
1355 l_token_tbl(2).token_name := 'STATUS_TYPE';
1356 l_token_tbl(2).token_value := p_Unexp_ECO_Rec.Status_Type;
1357 Error_Handler.Add_Error_Token
1358 ( p_Message_Name => 'ENG_STATUS_TYPE_INVALID'
1359 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1360 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1361 , p_Token_Tbl => l_Token_Tbl
1362 );
1363 END IF;
1364 x_return_status := FND_API.G_RET_STS_ERROR;
1365 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1366 END IF;
1367
1368 -- Cannot create ECOs that are not OPEN through open interface
1369 -- Also since now you can create a scheduled ECO an OR condition is
1370 -- now added to the code.
1371
1372 -- Schedule /Open/Completed is allowed in create mode
1373
1374 -- Bug : 5282713 Added p_ECO_rec.Base_Change_Management_Type = 'CHANGE_ORDER' condition
1375 -- Change objects other than change order and change order based can be created in any phase other than Open also.
1376 -- So we need to check the base change management type code for change order before checking this validation
1377
1378 -- Get the base change mgmt type code
1379 SELECT base_change_mgmt_type_code into l_base_change_mgmt_type_code from eng_change_order_types where change_order_type_id = p_Unexp_ECO_rec.Change_Order_Type_Id;
1380
1381 IF p_ECO_rec.transaction_type = 'CREATE' and l_base_change_mgmt_type_code = 'CHANGE_ORDER' and
1382 ((nvl(p_ECO_rec.plm_or_erp_change,'PLM') = 'ERP' AND p_Unexp_ECO_rec.status_type <> 1 AND p_Unexp_ECO_rec.status_type <> 4 AND p_Unexp_ECO_rec.status_type <> 7
1383 AND p_Unexp_ECO_rec.status_type <> 11)
1384 OR (nvl(p_ECO_rec.plm_or_erp_change,'PLM') = 'PLM' AND p_Unexp_ECO_rec.status_type NOT IN (0,1,4,7,11)))
1385 -- bug#12791511, eed the ability to create eco in released status(7) in ebs via the agile pip
1386 THEN
1387 l_token_tbl(1).token_name := 'STATUS_TYPE';
1388 l_token_tbl(1).token_value := p_Unexp_ECO_Rec.Status_Type;
1389
1390 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1391 THEN
1392 Error_Handler.Add_Error_Token
1393 ( p_Message_Name => 'ENG_ECO_CREATE_STAT_INVALID'
1394 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1395 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1396 , p_Token_Tbl => l_Token_Tbl
1397 );
1398 END IF;
1399 x_return_status := FND_API.G_RET_STS_ERROR;
1400 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1401 END IF;
1402
1403 --Bug 2908248
1404 --Cancel /Schedule /Open/Completed is allowed in update mode
1405
1406 IF p_ECO_rec.transaction_type = 'UPDATE' and
1407 ( p_Unexp_ECO_rec.status_type <> 1 AND p_Unexp_ECO_rec.status_type <> 4 AND p_Unexp_ECO_rec.status_type <> 11
1408 and p_Unexp_ECO_rec.status_type <> 5 and
1409 p_Unexp_ECO_rec.status_type <> 7 --- Added for Bug 3108743
1410 and p_Unexp_ECO_rec.status_type <> 2 ) --- Added for Bug 8823124
1411 THEN
1412 l_token_tbl(1).token_name := 'STATUS_TYPE';
1413 l_token_tbl(1).token_value := p_Unexp_ECO_Rec.Status_Type;
1414
1415 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1416 THEN
1417 Error_Handler.Add_Error_Token
1418 ( p_Message_Name => 'ENG_ECO_CREATE_STAT_INVALID'
1419 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1420 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1421 , p_Token_Tbl => l_Token_Tbl
1422 );
1423 END IF;
1424 x_return_status := FND_API.G_RET_STS_ERROR;
1425 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1426 END IF;
1427
1428 --Bug 2908248
1429
1430 --When trying to cancel/complete a ECO check if all lines/revised items are completed/cancelled/implemented
1431 l_change_id := Get_Change_Id(p_ECO_rec.eco_name, p_Unexp_ECO_rec.Organization_Id);
1432 IF( p_Unexp_ECO_rec.status_type = 11 OR p_Unexp_ECO_rec.status_type = 5 ) then
1433 IF p_change_line_tbl.Count <> 0 THEN
1434 --both for create and update
1435 FOR I IN 1..p_change_line_tbl.count LOOP
1436 l_change_line_rec := p_change_line_tbl(I);
1437 if
1438 ( (UPPER(l_change_line_rec.status_name) = UPPER('Completed') or UPPER(l_change_line_rec.status_name) = UPPER('Cancelled') )
1439 and l_change_line_rec.eco_name = p_ECO_rec.ECO_Name
1440 and upper(l_change_line_rec.transaction_type) = 'UPDATE'
1441 )
1442 then
1443 l_cl_cico_count :=l_cl_cico_count +1;
1444 end if;
1445 END LOOP;
1446 END IF;
1447
1448 --check for implemented revised items: bug:5414834
1449 for rec in revised_items_for_eco(l_change_id)
1450 loop
1451 if(rec.status_type = 6) then
1452 l_rev_item_cnt :=l_rev_item_cnt +1; -- no of implemented revised items
1453 end if;
1454 end loop;
1455
1456 --check for revised items :
1457 IF p_revised_item_tbl.count <> 0 THEN
1458 --both for create and update
1459 FOR I IN 1..p_revised_item_tbl.count LOOP
1460 l_revised_item_rec := p_revised_item_tbl(I);
1461 if
1462 ( (l_revised_item_rec.status_type = 6)
1463 and l_revised_item_rec.eco_name = p_ECO_rec.ECO_Name
1464 )
1465 then
1466 l_er_cico_count :=l_er_cico_count +1;
1467 end if;
1468 END LOOP;
1469 END IF;
1470
1471 -- variables required for lines validation
1472 l_cl_count :=0;
1473 l_cl_up_count :=0;
1474
1475 --variables required for revised items
1476 l_er_count :=0;
1477 l_er_up_count :=0;
1478
1479 if (UPPER(p_ECO_rec.transaction_type) = 'UPDATE') then
1480
1481 l_change_id := Get_Change_Id(p_ECO_rec.eco_name, p_Unexp_ECO_rec.Organization_Id);
1482
1483 --checking for lines.
1484 for lines_for_eco_rec in lines_for_eco(l_change_id) loop
1485 l_cl_count := l_cl_count+1;
1486 l_up_ch :=0;
1487 IF p_change_line_tbl.Count <> 0 THEN
1488 FOR I IN 1..p_change_line_tbl.count LOOP
1489 l_change_line_rec := p_change_line_tbl(I);
1490 if l_change_line_rec.sequence_number = lines_for_eco_rec.sequence_number
1491 and
1492 l_change_line_rec.name = lines_for_eco_rec.name
1493 then
1494 l_up_ch :=1; --we need not check in eng_change_lines as it being updated now
1495 end if;
1496 END LOOP;
1497 END IF; --p_change_line_tbl.Count <> 0
1498 if(
1499 ((l_up_ch = 0) AND (lines_for_eco_rec.status_code = 5))
1500
1501 OR
1502 ((l_up_ch = 0) AND(lines_for_eco_rec.status_code = 11) )
1503
1504 OR
1505 ((l_up_ch = 0) AND(lines_for_eco_rec.status_code = 6) )
1506
1507 )then
1508 l_cl_up_count :=l_cl_up_count+1;
1509 elsif l_up_ch =1 then
1510 l_cl_count:=l_cl_count -1;
1511 end if;
1512
1513 end loop;
1514 l_er_count :=0;
1515 l_er_up_count :=0;
1516
1517 --checking for revised items
1518
1519 for revised_items_for_eco_rec in revised_items_for_eco(l_change_id) loop
1520 l_er_count := l_er_count+1;
1521 l_up_cr :=0;
1522 IF p_revised_item_tbl.Count <> 0 THEN
1523 FOR I IN 1..p_revised_item_tbl.Count LOOP
1524 l_revised_item_rec := p_revised_item_tbl(I);
1525 if l_revised_item_rec .Start_Effective_Date = revised_items_for_eco_rec.scheduled_date
1526 then
1527 l_up_cr :=1; --we need not check in eng_change_lines as it being updated now
1528 end if;
1529 END LOOP;
1530 END IF; --p_change_line_tbl.Count <> 0
1531
1532 if(
1533 ((l_up_cr = 0) AND (revised_items_for_eco_rec.status_type = 5))
1534
1535 OR
1536 ((l_up_cr = 0) AND(revised_items_for_eco_rec.status_type = 11) )
1537 OR
1538 ((l_up_cr = 0) AND(revised_items_for_eco_rec.status_type = 6) )
1539
1540
1541 )then
1542 l_er_up_count :=l_er_up_count+1;
1543 elsif l_up_cr =1 then
1544 l_er_count:=l_er_count -1;
1545 end if;
1546
1547 end loop;
1548 end if; --UPPER(p_ECO_rec.transaction_type) = 'UPDATE'
1549
1550 --Fix for bug:5414834
1551 --if(l_cl_cico_count <> p_change_line_tbl.Count or l_cl_count <> l_cl_up_count or
1552 -- or l_er_count <> l_er_up_count
1553
1554 -- check for implemented revised items
1555 if (l_rev_item_cnt > 0 or l_er_cico_count >0)then
1556 l_token_tbl(1).token_name := 'STATUS_NAME';
1557 l_token_tbl(1).token_value := p_Unexp_ECO_Rec.Status_Type;
1558 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1559 THEN
1560 Error_Handler.Add_Error_Token
1561 ( p_Message_Name => 'ENG_ECO_CREATE_STAT_INVALID'
1562 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1563 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1564 , p_Token_Tbl => l_Token_Tbl
1565 );
1566 END IF;
1567 x_return_status := FND_API.G_RET_STS_ERROR;
1568 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1569 end if;
1570
1571 END IF; --p_Unexp_ECO_rec.status_type = 11
1572
1573 --End of Bug 2908248
1574
1575 -- Cannot implement ECOs through open interface
1576
1577 IF p_Unexp_ECO_rec.status_type = 6
1578 THEN
1579 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1580 THEN
1581 Error_Handler.Add_Error_Token
1582 ( p_Message_Name => 'ENG_ECO_STAT_CANNOT_BE_IMPL'
1583 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1584 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1585 );
1586 END IF;
1587 x_return_status := FND_API.G_RET_STS_ERROR;
1588 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1589 END IF;
1590
1591 END IF;
1592
1593 IF p_ECO_rec.priority_code IS NOT NULL AND
1594 ( p_ECO_rec.priority_code <>
1595 p_old_ECO_rec.priority_code OR
1596 p_old_ECO_rec.priority_code IS NULL )
1597 THEN
1598 IF NOT ENG_Validate.Priority
1599 ( p_priority_code => p_ECO_rec.priority_code
1600 , p_organization_id => p_Unexp_ECO_rec.organization_id
1601 , x_disable_date => l_disable_date
1602 , x_err_text => l_err_text
1603 )
1604 THEN
1605 IF l_err_text = ''
1606 THEN
1607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1608 END IF;
1609
1610 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1611 THEN
1612 l_token_tbl(2).token_name := 'PRIORITY_CODE';
1613 l_token_tbl(2).token_value := p_ECO_Rec.Priority_Code;
1614 Error_Handler.Add_Error_Token
1615 ( p_Message_Name => 'ENG_PRIORITY_CODE_INVALID'
1616 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1617 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1618 , p_Token_Tbl => l_Token_Tbl
1619 );
1620 END IF;
1621 x_return_status := FND_API.G_RET_STS_ERROR;
1622 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1623 END IF;
1624
1625 IF NVL(l_disable_date, SYSDATE + 1) <= SYSDATE
1626 THEN
1627 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1628 THEN
1629 l_token_tbl(2).token_name := 'PRIORITY_CODE';
1630 l_token_tbl(2).token_value := p_ECO_Rec.Priority_Code;
1631 Error_Handler.Add_Error_Token
1632 ( p_Message_Name => 'ENG_PRIORITY_CODE_DISABLED'
1633 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1634 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1635 , p_Token_Tbl => l_Token_Tbl
1636 );
1637 END IF;
1638 x_return_status := FND_API.G_RET_STS_ERROR;
1639 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1640 END IF;
1641 IF p_ECO_rec.plm_or_erp_change ='PLM' then
1642
1643 l_valid_priority :=0;
1644
1645 for valid_prio_for_eco_type in GetValidPriorities(p_Unexp_ECO_rec.change_order_type_id) loop
1646 if valid_prio_for_eco_type.priority_code = p_ECO_rec.priority_code then
1647 l_valid_priority := 1;
1648 end if;
1649 end loop;
1650 if l_valid_priority = 0 then
1651 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1652 THEN
1653 l_token_tbl(2).token_name := 'PRIORITY_CODE';
1654 l_token_tbl(2).token_value := p_ECO_Rec.Priority_Code;
1655 Error_Handler.Add_Error_Token
1656 ( p_Message_Name => 'ENG_PRIORITY_CODE_INVALID'
1657 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1658 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1659 , p_Token_Tbl => l_Token_Tbl
1660 );
1661 END IF;
1662 x_return_status := FND_API.G_RET_STS_ERROR;
1663 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1664
1665 end if;
1666 END IF;
1667
1668 -- Commented out the following as this validation is not required for 11.5.10
1669 /*
1670 --Bug 2950311
1671 ELSIF (p_ECO_rec.priority_code IS NULL
1672 AND p_ECO_rec.Assignee IS NOT NULL) THEN
1673
1674
1675 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1676 THEN
1677 l_token_tbl(2).token_name := 'ECO_NAME';
1678 l_token_tbl(2).token_value := p_ECO_Rec.Eco_Name;
1679 Error_Handler.Add_Error_Token
1680 ( p_Message_Name => 'ENG_PRIORITY_CODE_INVALID'
1681 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1682 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1683 , p_Token_Tbl => l_Token_Tbl
1684 );
1685 END IF;
1686 x_return_status := FND_API.G_RET_STS_ERROR;
1687 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1688 */
1689 END IF;
1690
1691 IF p_ECO_rec.reason_code IS NOT NULL AND
1692 ( p_ECO_rec.reason_code <>
1693 p_old_ECO_rec.reason_code OR
1694 p_old_ECO_rec.reason_code IS NULL )
1695 THEN
1696 IF NOT ENG_Validate.Reason
1697 ( p_reason_code => p_ECO_rec.reason_code
1698 , p_organization_id => p_Unexp_ECO_rec.organization_id
1699 , x_disable_date => l_disable_date
1700 , x_err_text => l_err_text
1701 )
1702 THEN
1703 IF l_err_text = ''
1704 THEN
1705 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1706 END IF;
1707
1708 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1709 THEN
1710 l_token_tbl(2).token_name := 'REASON_CODE';
1711 l_token_tbl(2).token_value := p_ECO_Rec.Reason_Code;
1712 Error_Handler.Add_Error_Token
1713 ( p_Message_Name => 'ENG_REASON_CODE_INVALID'
1714 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1715 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1716 , p_Token_Tbl => l_Token_Tbl
1717 );
1718 END IF;
1719 x_return_status := FND_API.G_RET_STS_ERROR;
1720 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1721 END IF;
1722 IF NVL(l_disable_date, SYSDATE + 1) <= SYSDATE
1723 THEN
1724 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1725 THEN
1726 l_token_tbl(2).token_name := 'REASON_CODE';
1727 l_token_tbl(2).token_value := p_ECO_Rec.Reason_Code;
1728 Error_Handler.Add_Error_Token
1729 ( p_Message_Name => 'ENG_REASON_CODE_DISABLED'
1730 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1731 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1732 , p_Token_Tbl => l_Token_Tbl
1733 );
1734 END IF;
1735 x_return_status := FND_API.G_RET_STS_ERROR;
1736 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1737 END IF;
1738 --11.5.10
1739 IF p_ECO_rec.plm_or_erp_change ='PLM' then
1740
1741 l_valid_reason :=0;
1742
1743 for valid_rea_for_eco_type in GetValidReasons(p_Unexp_ECO_rec.change_order_type_id) loop
1744 if valid_rea_for_eco_type.Reason_Code = p_ECO_Rec.Reason_Code then
1745 l_valid_reason :=1;
1746 end if;
1747 end loop;
1748 if l_valid_reason = 0 then
1749
1750 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1751 THEN
1752 l_token_tbl(2).token_name := 'REASON_CODE';
1753 l_token_tbl(2).token_value := p_ECO_Rec.Reason_Code;
1754 Error_Handler.Add_Error_Token
1755 ( p_Message_Name => 'ENG_REASON_CODE_INVALID'
1756 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1757 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1758 , p_Token_Tbl => l_Token_Tbl
1759 );
1760 END IF;
1761 x_return_status := FND_API.G_RET_STS_ERROR;
1762 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1763
1764 end if;
1765 --11.5.10
1766 END IF;
1767
1768
1769 END IF;
1770
1771 /* Added by MK on 11/29/00 Bug #1508078
1772 -- Attribute validation for hierarchy_flag and organization_hierarchy
1773 --
1774 */
1775
1776
1777 /* User may not set null in Update,
1778 -- because hierarchy_flag does not exist interface table,
1779 -- Hence following logic is commented out.
1780 -- Set 2:No to hierarchy_flag in Entity Defaulting
1781 -- when hierarchy_flag = FND_API.G_MISS_NUM
1782 --
1783 IF p_ECO_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
1784 THEN
1785 IF p_ECO_rec.hierarchy_flag = FND_API.G_MISS_NUM
1786 THEN
1787 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1788 THEN
1789 Error_Handler.Add_Error_Token
1790 ( p_Message_Name => 'ENG_HIERARCHY_FLAG_MISSING'
1791 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1792 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1793 , p_Token_Tbl => l_Token_Tbl
1794 );
1795 END IF;
1796 x_return_status := FND_API.G_RET_STS_ERROR;
1797 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1798
1799 END IF ;
1800 END IF ;
1801
1802 IF NVL(p_ECO_rec.hierarchy_flag,2 ) NOT IN (1, 2 )
1803 AND p_ECO_rec.hierarchy_flag <> FND_API.G_MISS_NUM
1804 THEN
1805 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1806 THEN
1807 Error_Handler.Add_Error_Token
1808 ( p_Message_Name => 'ENG_HIERARCHY_FLAG_INVALID'
1809 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1810 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1811 , p_Token_Tbl => l_Token_Tbl
1812 );
1813 END IF;
1814 x_return_status := FND_API.G_RET_STS_ERROR;
1815 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1816 END IF;
1817 */
1818
1819
1820
1821 -- Eng Change
1822 IF NVL(p_ECO_rec.internal_use_only,1 ) NOT IN (1, 2 )
1823 AND p_ECO_rec.internal_use_only <> FND_API.G_MISS_NUM
1824 THEN
1825 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1826 THEN
1827 Error_Handler.Add_Error_Token
1828 ( p_Message_Name => 'ENG_INTL_USE_ONLY_FLAG_INVALID'
1829 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1830 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1831 , p_Token_Tbl => l_Token_Tbl
1832 );
1833 END IF;
1834 x_return_status := FND_API.G_RET_STS_ERROR;
1835 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1836 END IF;
1837
1838 IF p_ECO_rec.need_by_date < SYSDATE
1839 AND p_ECO_rec.need_by_date <> FND_API.G_MISS_DATE
1840 AND p_ECO_rec.need_by_date IS NOT NULL
1841 THEN
1842 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1843 THEN
1844 Error_Handler.Add_Error_Token
1845 ( p_Message_Name => 'ENG_NEED_BY_DATE_LESS_CURR'
1846 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1847 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1848 , p_Token_Tbl => l_Token_Tbl
1849 );
1850 END IF;
1851 x_return_status := FND_API.G_RET_STS_ERROR;
1852 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1853 END IF;
1854
1855 IF p_ECO_rec.effort < 0
1856 AND p_ECO_rec.effort <> FND_API.G_MISS_NUM
1857 AND p_ECO_rec.effort IS NOT NULL
1858 THEN
1859 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1860 THEN
1861 Error_Handler.Add_Error_Token
1862 ( p_Message_Name => 'ENG_EFFORT_LESS_ZERO'
1863 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1864 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1865 , p_Token_Tbl => l_Token_Tbl
1866 );
1867 END IF;
1868 x_return_status := FND_API.G_RET_STS_ERROR;
1869 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1870 END IF;
1871
1872
1873 -- These calls are temporarily commented out
1874
1875 /*
1876 IF (p_ECO_rec.attribute7 IS NOT NULL AND
1877 ( p_ECO_rec.attribute7 <>
1878 p_old_ECO_rec.attribute7 OR
1879 p_old_ECO_rec.attribute7 IS NULL ))
1880 OR (p_ECO_rec.attribute8 IS NOT NULL AND
1881 ( p_ECO_rec.attribute8 <>
1882 p_old_ECO_rec.attribute8 OR
1883 p_old_ECO_rec.attribute8 IS NULL ))
1884 OR (p_ECO_rec.attribute9 IS NOT NULL AND
1885 ( p_ECO_rec.attribute9 <>
1886 p_old_ECO_rec.attribute9 OR
1887 p_old_ECO_rec.attribute9 IS NULL ))
1888 OR (p_ECO_rec.attribute10 IS NOT NULL AND
1889 ( p_ECO_rec.attribute10 <>
1890 p_old_ECO_rec.attribute10 OR
1891 p_old_ECO_rec.attribute10 IS NULL ))
1892 OR (p_ECO_rec.attribute11 IS NOT NULL AND
1893 ( p_ECO_rec.attribute11 <>
1894 p_old_ECO_rec.attribute11 OR
1895 p_old_ECO_rec.attribute11 IS NULL ))
1896 OR (p_ECO_rec.attribute12 IS NOT NULL AND
1897 ( p_ECO_rec.attribute12 <>
1898 p_old_ECO_rec.attribute12 OR
1899 p_old_ECO_rec.attribute12 IS NULL ))
1900 OR (p_ECO_rec.attribute13 IS NOT NULL AND
1901 ( p_ECO_rec.attribute13 <>
1902 p_old_ECO_rec.attribute13 OR
1903 p_old_ECO_rec.attribute13 IS NULL ))
1904 OR (p_ECO_rec.attribute14 IS NOT NULL AND
1905 ( p_ECO_rec.attribute14 <>
1906 p_old_ECO_rec.attribute14 OR
1907 p_old_ECO_rec.attribute14 IS NULL ))
1908 OR (p_ECO_rec.attribute15 IS NOT NULL AND
1909 ( p_ECO_rec.attribute15 <>
1910 p_old_ECO_rec.attribute15 OR
1911 p_old_ECO_rec.attribute15 IS NULL ))
1912 OR (p_ECO_rec.attribute_category IS NOT NULL AND
1913 ( p_ECO_rec.attribute_category <>
1914 p_old_ECO_rec.attribute_category OR
1915 p_old_ECO_rec.attribute_category IS NULL ))
1916 OR (p_ECO_rec.attribute1 IS NOT NULL AND
1917 ( p_ECO_rec.attribute1 <>
1918 p_old_ECO_rec.attribute1 OR
1919 p_old_ECO_rec.attribute1 IS NULL ))
1920 OR (p_ECO_rec.attribute2 IS NOT NULL AND
1921 ( p_ECO_rec.attribute2 <>
1922 p_old_ECO_rec.attribute2 OR
1923 p_old_ECO_rec.attribute2 IS NULL ))
1924 OR (p_ECO_rec.attribute3 IS NOT NULL AND
1925 ( p_ECO_rec.attribute3 <>
1926 p_old_ECO_rec.attribute3 OR
1927 p_old_ECO_rec.attribute3 IS NULL ))
1928 OR (p_ECO_rec.attribute4 IS NOT NULL AND
1929 ( p_ECO_rec.attribute4 <>
1930 p_old_ECO_rec.attribute4 OR
1931 p_old_ECO_rec.attribute4 IS NULL ))
1932 OR (p_ECO_rec.attribute5 IS NOT NULL AND
1933 ( p_ECO_rec.attribute5 <>
1934 p_old_ECO_rec.attribute5 OR
1935 p_old_ECO_rec.attribute5 IS NULL ))
1936 OR (p_ECO_rec.attribute6 IS NOT NULL AND
1937 ( p_ECO_rec.attribute6 <>
1938 p_old_ECO_rec.attribute6 OR
1939 p_old_ECO_rec.attribute6 IS NULL ))
1940 THEN
1941
1942 FND_FLEX_DESC_VAL.Set_Column_Value
1943 ( column_name => 'ATTRIBUTE7'
1944 , column_value => p_ECO_rec.attribute7
1945 );
1946 FND_FLEX_DESC_VAL.Set_Column_Value
1947 ( column_name => 'ATTRIBUTE8'
1948 , column_value => p_ECO_rec.attribute8
1949 );
1950 FND_FLEX_DESC_VAL.Set_Column_Value
1951 ( column_name => 'ATTRIBUTE9'
1952 , column_value => p_ECO_rec.attribute9
1953 );
1954 FND_FLEX_DESC_VAL.Set_Column_Value
1955 ( column_name => 'ATTRIBUTE10'
1956 , column_value => p_ECO_rec.attribute10
1957 );
1958 FND_FLEX_DESC_VAL.Set_Column_Value
1959 ( column_name => 'ATTRIBUTE11'
1960 , column_value => p_ECO_rec.attribute11
1961 );
1962 FND_FLEX_DESC_VAL.Set_Column_Value
1963 ( column_name => 'ATTRIBUTE12'
1964 , column_value => p_ECO_rec.attribute12
1965 );
1966 FND_FLEX_DESC_VAL.Set_Column_Value
1967 ( column_name => 'ATTRIBUTE13'
1968 , column_value => p_ECO_rec.attribute13
1969 );
1970 FND_FLEX_DESC_VAL.Set_Column_Value
1971 ( column_name => 'ATTRIBUTE14'
1972 , column_value => p_ECO_rec.attribute14
1973 );
1974 FND_FLEX_DESC_VAL.Set_Column_Value
1975 ( column_name => 'ATTRIBUTE15'
1976 , column_value => p_ECO_rec.attribute15
1977 );
1978 FND_FLEX_DESC_VAL.Set_Column_Value
1979 ( column_name => 'ATTRIBUTE_CATEGORY'
1980 , column_value => p_ECO_rec.attribute_category
1981 );
1982 FND_FLEX_DESC_VAL.Set_Column_Value
1983 ( column_name => 'ATTRIBUTE1'
1984 , column_value => p_ECO_rec.attribute1
1985 );
1986 FND_FLEX_DESC_VAL.Set_Column_Value
1987 ( column_name => 'ATTRIBUTE2'
1988 , column_value => p_ECO_rec.attribute2
1989 );
1990 FND_FLEX_DESC_VAL.Set_Column_Value
1991 ( column_name => 'ATTRIBUTE3'
1992 , column_value => p_ECO_rec.attribute3
1993 );
1994 FND_FLEX_DESC_VAL.Set_Column_Value
1995 ( column_name => 'ATTRIBUTE4'
1996 , column_value => p_ECO_rec.attribute4
1997 );
1998 FND_FLEX_DESC_VAL.Set_Column_Value
1999 ( column_name => 'ATTRIBUTE5'
2000 , column_value => p_ECO_rec.attribute5
2001 );
2002 FND_FLEX_DESC_VAL.Set_Column_Value
2003 ( column_name => 'ATTRIBUTE6'
2004 , column_value => p_ECO_rec.attribute6
2005 );
2006
2007 -- Validate descriptive flexfield.
2008
2009 IF NOT ENG_Validate.Desc_Flex( 'ECO' ) THEN
2010 x_return_status := FND_API.G_RET_STS_ERROR;
2011 END IF;
2012
2013 END IF;
2014 */
2015
2016 -- Done validating attributes
2017
2018 EXCEPTION
2019
2020 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2021
2022 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2023 x_err_text := l_err_text;
2024
2025 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2026 THEN
2027 Error_Handler.Add_Error_Token
2028 ( p_Message_Text => l_err_text
2029 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2030 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
2031 );
2032 END IF;
2033 END Check_Attributes;
2034
2035 -- Procedure Check_Required
2036
2037 PROCEDURE Conditionally_Required
2038 ( x_return_status OUT NOCOPY VARCHAR2
2039 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2040 , p_ECO_rec IN ENG_ECO_PUB.Eco_Rec_Type
2041 , p_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_Unexposed_Rec_Type
2042 , p_old_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
2043 , p_old_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_Unexposed_Rec_Type
2044 )
2045 IS
2046 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2047 l_err_text VARCHAR2(2000) := NULL;
2048 l_Token_Tbl Error_Handler.Token_Tbl_Type;
2049 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2050 BEGIN
2051
2052 x_return_status := FND_API.G_RET_STS_SUCCESS;
2053
2054 l_token_tbl(1).token_name := 'ECO_NAME';
2055 l_token_tbl(1).token_value := p_ECO_Rec.ECO_Name;
2056
2057 -- responsible_org_id must not be null if profile option is set to yes
2058
2059
2060 -- Bug : 2516871
2061 -- If this function is called from MCO then, the below filter condition for
2062 -- validating the ENG:MANDATORY_ECO_DEPT should not be executed.
2063
2064 IF (Bom_globals.Get_Caller_Type = BOM_GLOBALS.G_MASS_CHANGE) THEN
2065 NULL ;
2066 ELSE
2067
2068 IF (FND_PROFILE.DEFINED('ENG:MANDATORY_ECO_DEPT') AND
2069 FND_PROFILE.VALUE('ENG:MANDATORY_ECO_DEPT') = '1')
2070 AND p_Unexp_ECO_rec.responsible_org_id IS NULL
2071 THEN
2072 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2073 THEN
2074 Error_Handler.Add_Error_Token
2075 ( p_Message_Name => 'ENG_RESP_ORG_MISSING'
2076 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2077 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
2078 , p_Token_Tbl => l_Token_Tbl
2079 );
2080 END IF;
2081 x_return_status := FND_API.G_RET_STS_ERROR;
2082 END IF;
2083
2084 END IF ;
2085
2086 /* Added by MK on 11/29/00 Bug #1508078
2087 -- Conditionally required validation for hierarchy_flag and organization_hierarchy
2088 --
2089 IF p_ECO_rec.hierarchy_flag = 1 AND
2090 NVL(p_ECO_rec.organization_hierarchy, FND_API.G_MISS_CHAR)
2091 = FND_API.G_MISS_CHAR
2092 THEN
2093
2094 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2095 THEN
2096 Error_Handler.Add_Error_Token
2097 ( p_Message_Name => 'ENG_ORG_HIERARCHY_MISSING'
2098 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2099 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
2100 , p_Token_Tbl => l_Token_Tbl
2101 );
2102 END IF;
2103 x_return_status := FND_API.G_RET_STS_ERROR;
2104 END IF;
2105 */
2106
2107
2108 EXCEPTION
2109
2110 WHEN OTHERS THEN
2111
2112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2113
2114 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2115 THEN
2116 l_err_text := G_PKG_NAME || ' : (Conditionally Required Fields Check) ' || substrb(SQLERRM,1,200);
2117 Error_Handler.Add_Error_Token
2118 ( p_Message_Text => l_err_text
2119 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2120 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
2121 );
2122 END IF;
2123
2124 END Conditionally_Required;
2125
2126 /***************************************************************************
2127 * Procedure : Check_Existence
2128 * Parameters IN : ECO Name
2129 * Organization Id
2130 * Parameters OUT: Old Eco exposed column record
2131 * Old ECO unexposed column record
2132 * Purpose : Check Existence will verify that the ECO record does not
2133 * already exist for creates and it does exist when the user
2134 * is performing an Update or Delete.
2135 * If Update or Delete the procedure will also return the old
2136 * database record.
2137 *****************************************************************************/
2138 PROCEDURE Check_Existence
2139 ( p_change_notice IN VARCHAR2
2140 , p_organization_id IN NUMBER
2141 , p_organization_code IN VARCHAR2
2142 , p_calling_entity IN VARCHAR2
2143 , p_transaction_type IN VARCHAR2
2144 , x_eco_rec OUT NOCOPY Eng_Eco_Pub.Eco_Rec_Type
2145 , x_eco_unexp_rec OUT NOCOPY Eng_Eco_Pub.Eco_Unexposed_Rec_Type
2146 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2147 , x_Return_Status OUT NOCOPY VARCHAR2
2148 )
2149 IS
2150 l_Mesg_token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2151 l_return_status VARCHAR2(1);
2152 l_err_text VARCHAR2(2000);
2153 l_Token_Tbl Error_Handler.Token_Tbl_Type;
2154 BEGIN
2155 l_return_status := FND_API.G_RET_STS_SUCCESS;
2156
2157 l_token_tbl(1).token_name := 'ECO_NAME';
2158 l_token_tbl(1).token_value := p_change_notice;
2159 l_token_tbl(2).token_name := 'ORGANIZATION_CODE';
2160 l_token_tbl(2).token_value := p_organization_code;
2161
2162 Eng_Eco_Util.Query_Row
2163 ( p_change_notice => p_change_notice
2164 , p_organization_id => p_organization_id
2165 , x_ECO_rec => x_eco_rec
2166 , x_ECO_Unexp_Rec => x_eco_unexp_rec
2167 , x_return_status => l_return_status
2168 , x_err_text => l_err_text
2169 );
2170
2171 IF l_return_status = Eng_Globals.G_RECORD_FOUND AND
2172 p_calling_entity = 'ECO' AND
2173 p_transaction_type = Eng_Globals.G_OPR_CREATE
2174 THEN
2175 l_return_status := FND_API.G_RET_STS_ERROR;
2176 Error_Handler.Add_Error_Token
2177 ( p_Message_Name => 'ENG_ECO_ALREADY_EXISTS'
2178 , p_Mesg_Token_Tbl => l_mesg_token_tbl
2179 , x_Mesg_Token_Tbl => l_mesg_token_tbl
2180 , p_Token_Tbl => l_token_tbl
2181 );
2182 /* Commenting the following Code for Bug 3127841 as per Mani's suggestion
2183 ELSIF l_return_status = Eng_Globals.G_RECORD_FOUND AND
2184 p_transaction_type = Eng_Globals.G_OPR_UPDATE AND
2185 x_eco_unexp_rec.approval_status_type in (3, 5) -- approved or approval requested
2186 THEN
2187 l_return_status := FND_API.G_RET_STS_ERROR;
2188 Error_Handler.Add_Error_Token
2189 ( p_Message_Name => 'ENG_ECO_CANNOT_UPDATE'
2190 , p_Mesg_Token_Tbl => l_mesg_token_tbl
2191 , x_Mesg_Token_Tbl => l_mesg_token_tbl
2192 , p_Token_Tbl => l_token_tbl
2193 );
2194 */
2195 ELSIF l_return_status = Eng_Globals.G_RECORD_NOT_FOUND AND
2196 p_calling_entity = 'ECO' AND
2197 p_transaction_type IN
2198 ( Eng_Globals.G_OPR_UPDATE, Eng_Globals.G_OPR_DELETE)
2199 THEN
2200 l_return_status := FND_API.G_RET_STS_ERROR;
2201 Error_Handler.Add_Error_Token
2202 ( p_Message_Name => 'ENG_ECO_DOES_NOT_EXIST'
2203 , p_Mesg_Token_Tbl => l_mesg_token_tbl
2204 , x_Mesg_Token_Tbl => l_mesg_token_tbl
2205 , p_Token_Tbl => l_token_tbl
2206 );
2207
2208 ELSIF l_return_status = Eng_Globals.G_RECORD_NOT_FOUND AND
2209 p_calling_entity = 'CHILD'
2210 THEN
2211 l_return_status := FND_API.G_RET_STS_ERROR;
2212
2213 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2214 THEN
2215 Error_Handler.Add_Error_Token
2216 ( p_Message_Name => NULL
2217 , p_Message_Text => l_err_text
2218 , p_Mesg_Token_Tbl => l_mesg_token_tbl
2219 , x_Mesg_Token_Tbl => l_mesg_token_tbl
2220 , p_Token_Tbl => l_token_tbl
2221 );
2222 ELSE
2223 l_return_status := FND_API.G_RET_STS_SUCCESS;
2224 END IF;
2225
2226 x_return_status := l_return_status;
2227 x_mesg_token_tbl := l_mesg_token_tbl;
2228
2229 END Check_Existence;
2230
2231
2232 /****************************************************************************
2233 * Function : Check_Workflow_Process
2234 * Pramaters IN : Change_Order_Type_Id
2235 * Priority Code
2236 * Organization_ID
2237 * Assignee_ID
2238 * Change_ID
2239 * Returns : TRUE if the ECO has a Workflow process associated with it.
2240 * Otherwise returns a False.
2241 * Purpose : Checks if there is worflow process for the ECO.
2242 *****************************************************************************/
2243 FUNCTION Check_Workflow_Process
2244 ( p_change_order_type_id IN NUMBER
2245 , p_priority_code IN VARCHAR2
2246 , p_organization_id IN NUMBER
2247 , p_assignee_id IN NUMBER
2248 , p_change_id IN NUMBER
2249 ) RETURN BOOLEAN
2250 IS
2251 CURSOR c_CheckProcess IS
2252 SELECT process_name
2253 FROM eng_change_type_processes
2254 WHERE change_order_type_id = p_change_order_type_id
2255 AND NVL(eng_change_priority_code,'X') = NVL(p_priority_code, 'X');
2256 -- Bug 2921534 ,processes are no more organization specific ,thus commenting out the below where condition
2257 -- AND organization_id = p_organization_id;
2258
2259 ---While bulkloading PLM records we will have to look at route_id
2260 CURSOR c_CheckProcess_PLM(p_change_id NUMBER) IS
2261 SELECT route_id
2262 FROM eng_engineering_changes
2263 WHERE change_id = p_change_id ;
2264
2265 l_route_id NUMBER;
2266
2267 BEGIN
2268
2269 if ( p_assignee_id is null)
2270 then
2271 FOR Process IN c_CheckProcess
2272 LOOP
2273 RETURN TRUE;
2274 END LOOP;
2275 else
2276 OPEN c_CheckProcess_PLM(p_change_id);
2277 FETCH c_CheckProcess_PLM INTO l_route_id;
2278 CLOSE c_CheckProcess_PLM;
2279 if(l_route_id is not null) then
2280 RETURN TRUE;
2281 else
2282 RETURN FALSE;
2283 end if;
2284 end if;
2285
2286 RETURN FALSE;
2287
2288 END Check_Workflow_Process;
2289
2290 /****************************************************************************
2291 * Procedure : Check_Access
2292 * Parameters IN : ECO Primary Key
2293 * Parameters OUT: Mesg Token Tbl
2294 * Return Status
2295 * Purpose : Procedure will verify if the user has access to the current
2296 * ECO byt checking that the eco is not canceled or implemented
2297 * or it does not have a workflow process.
2298 * Th procedure will also check if the user has access to the
2299 * Change order type.
2300 ****************************************************************************/
2301 PROCEDURE Check_Access
2302 ( p_change_notice IN VARCHAR2
2303 , p_organization_id IN NUMBER
2304 , p_change_type_code IN VARCHAR2 := NULL
2305 , p_change_order_type_id IN NUMBER := NULL
2306 , p_Mesg_Token_Tbl IN Error_Handler.Mesg_Token_Tbl_Type :=
2307 Error_Handler.G_MISS_MESG_TOKEN_TBL
2308 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2309 , x_Return_Status OUT NOCOPY VARCHAR2
2310 , p_check_scheduled_status IN BOOLEAN DEFAULT TRUE -- Added for Enhancement 5470261
2311 , p_status_check_required IN BOOLEAN DEFAULT TRUE -- Added for enhancement 5414834
2312 )
2313 IS
2314 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type :=
2315 p_Mesg_Token_Tbl;
2316 l_Token_Tbl Error_Handler.Token_Tbl_Type;
2317 l_return_status VARCHAR2(1);
2318 l_ProcessExists BOOLEAN;
2319 l_WorkflowInprogressExists BOOLEAN; -- bug no 3591968 by Rashmi
2320 l_Wkfl NUMBER; -- bug no 3591968 by Rashmi
2321 l_change_order_assembly_Type NUMBER;
2322 l_change_order_type_id NUMBER := NULL;
2323 --added status_code in select stmt for validation
2324 CURSOR c_CheckECO IS
2325 SELECT status_type, priority_code, change_order_type_id,status_code --bug no 3591968 by Rashmi
2326 ,approval_status_type,assignee_id ,change_id --PLM records we will only have to look for processes based on change_type_id
2327 , nvl(plm_or_erp_change, 'PLM') plm_or_erp_change
2328 FROM eng_engineering_changes
2329 WHERE change_notice = p_change_notice
2330 AND organization_id = p_organization_id;
2331 --bug no 3591968 by Rashmi
2332 /*CURSOR c_CheckProcessInProgress(cp_change_id NUMBER,cp_status_code NUMBER) IS
2333 select status_code
2334 from eng_lifecycle_statuses
2335 where entity_id1 = cp_change_id
2336 and status_code =cp_status_code
2337 and entity_name ='ENG_CHANGE'
2338 and active_flag='Y'
2339 and change_wf_route_id is not null
2340 and workflow_status = 'IN_PROGRESS';*/
2341 -- Bug 4033479
2342 CURSOR c_lifecycle_status(cp_change_id NUMBER,cp_status_code NUMBER) IS
2343 select els.status_code, els.workflow_status, els.change_wf_route_id,
2344 ecs.status_type orig_status_type, els.CHANGE_EDITABLE_FLAG ,
2345 ecs.status_name
2346 from eng_lifecycle_statuses els, eng_change_statuses_vl ecs
2347 where els.ENTITY_NAME = 'ENG_CHANGE'
2348 and els.ENTITY_ID1 = cp_change_id
2349 and els.STATUS_CODE = cp_STATUS_CODE
2350 and els.active_flag = 'Y'
2351 and els.STATUS_CODE = ecs.STATUS_CODE;
2352
2353 l_cls_rec c_lifecycle_status%ROWTYPE;
2354 l_update_allowed BOOLEAN;
2355 l_status_name eng_change_statuses_tl.status_name%TYPE;
2356 BEGIN
2357
2358 l_return_status := FND_API.G_RET_STS_SUCCESS;
2359 l_change_order_type_id := NULL;
2360
2361 l_Token_Tbl(1).token_name := 'ECO_NAME';
2362 l_Token_Tbl(1).token_value := p_change_notice;
2363 --
2364 -- Check that the ECO is not Implemented or Cancelled.
2365 --
2366 IF Eng_Globals.Is_Eco_Impl IS NULL AND
2367 Eng_Globals.Is_Eco_Cancl IS NULL AND
2368 Eng_Globals.Is_WKFL_Process IS NULL AND
2369 Eng_Globals.Is_ECO_Access IS NULL
2370 THEN
2371 FOR ECO IN c_CheckECO
2372 LOOP
2373 IF p_change_order_type_id IS NULL
2374 THEN
2375 l_change_order_type_id :=
2376 eco.change_order_type_id;
2377 END IF;
2378
2379 IF ECO.status_type = 6
2380 THEN
2381 Eng_Globals.Set_Eco_Impl
2382 ( p_eco_impl => TRUE);
2383 ELSIF ECO.status_type = 5
2384 THEN
2385 Eng_Globals.Set_Eco_Cancl
2386 ( p_eco_cancl => TRUE);
2387 ELSIF ECO.status_type NOT IN (5,6)
2388 THEN
2389 Eng_Globals.Set_Eco_Impl
2390 ( p_eco_impl => FALSE);
2391 Eng_Globals.Set_Eco_Cancl
2392 ( p_eco_cancl => FALSE);
2393
2394 --
2395 -- Check if the ECO has a process
2396 --
2397 l_ProcessExists :=
2398 Check_Workflow_Process
2399 ( p_change_order_type_id =>
2400 ECO.change_order_type_id
2401 , p_priority_code =>
2402 ECO.priority_code
2403 , p_organization_id =>
2404 p_organization_id
2405 , p_assignee_id =>
2406 ECO.assignee_id
2407 ,p_change_id =>
2408 ECO.change_id
2409 );
2410
2411 IF l_ProcessExists AND
2412 ECO.approval_status_type = 3
2413 THEN
2414 Eng_Globals.Set_WKFL_Process
2415 ( p_wkfl_process => TRUE);
2416 ELSE
2417 Eng_Globals.Set_WKFL_Process
2418 ( p_wkfl_process => FALSE);
2419 END IF;
2420
2421
2422 END IF;
2423 -- Check if ECO is not in progress --bug no 3591968 by Rashmi
2424 l_WorkflowInprogressExists := FALSE ;
2425 /*OPEN c_CheckProcessInProgress
2426 (cp_change_id => ECO.change_id
2427 ,cp_status_code => ECO.status_code );
2428 FETCH c_CheckProcessInProgress INTO l_Wkfl ;
2429 CLOSE c_CheckProcessInProgress;
2430 if( l_Wkfl is not null) then
2431 l_WorkflowInprogressExists := TRUE ;
2432 else
2433 l_WorkflowInprogressExists := FALSE ;
2434 end if;*/
2435 l_update_allowed := TRUE;
2436 IF(ECO.plm_or_erp_change = 'PLM')
2437 THEN
2438 OPEN c_lifecycle_status (cp_change_id => ECO.change_id
2439 ,cp_status_code => ECO.status_code );
2440 FETCH c_lifecycle_status INTO l_cls_rec ;
2441 IF (l_cls_rec.change_wf_route_id is not NULL
2442 AND l_cls_rec.workflow_status = 'IN_PROGRESS'
2443 AND l_cls_rec.CHANGE_EDITABLE_FLAG <> 'Y')
2444 THEN
2445 l_WorkflowInprogressExists := TRUE ;
2446 END IF;
2447 -- Added for enhancement 5414834
2448 IF(p_status_check_required)
2449 THEN
2450 -- Added for Bug 4033479
2451 IF (ECO.status_type IN (2, 4, 7, 8, 9,11)
2452 OR (ECO.status_type = 10 AND l_cls_rec.orig_status_type <> 1))
2453 THEN
2454 -- Added for Enhancement 5470261
2455 -- If status type is 4<- Scheduled, then check if the p_check_scheduled_status flag is true
2456 -- Only if the p_check_scheduled_status is true, set the flag to throw the error
2457 if(ECO.status_type <> 4 OR p_check_scheduled_status = TRUE) THEN
2458 l_update_allowed := FALSE;
2459 l_status_name := l_cls_rec.status_name;
2460 END if;
2461 -- Code changes for Enhancement 5470261 ends
2462 END IF;
2463 END IF;
2464 CLOSE c_lifecycle_status;
2465 END IF;
2466 END LOOP;
2467 END IF;
2468
2469 IF l_change_order_type_id IS NULL
2470 THEN
2471 l_change_order_type_id := p_change_order_type_id;
2472 END IF;
2473
2474 /****************************************************
2475 --
2476 -- Check if user has access to type of ECO. If the
2477 -- ECO's change order type is Engineering and the
2478 -- Profile value Eng:Engineering Change Order Type
2479 -- Access is NO, then the user cannot access this
2480 -- ECO.
2481 --
2482 *****************************************************/
2483 IF Eng_Globals.Is_ECO_Access IS NULL
2484 THEN
2485 SELECT assembly_type
2486 INTO l_change_order_assembly_Type
2487 FROM eng_change_order_types
2488 WHERE change_order_type_id =
2489 l_change_order_type_id;
2490
2491 IF l_change_order_assembly_type = 2 /* ENG */
2492 AND
2493 Fnd_Profile.Value
2494 ('ENG:ENG_ITEM_ECN_ACCESS')
2495 = 2
2496 THEN
2497 --
2498 -- User does not have access.
2499 --
2500 Eng_Globals.Set_Eco_Access
2501 ( p_eco_access => FALSE);
2502 ELSE
2503 Eng_Globals.Set_Eco_Access
2504 ( p_eco_access => TRUE);
2505 END IF;
2506 END IF;
2507
2508 IF NVL(Eng_Globals.Is_Eco_Impl, FALSE) = TRUE
2509 THEN
2510 l_return_status := FND_API.G_RET_STS_ERROR;
2511 Error_Handler.Add_Error_Token
2512 ( p_Message_Name => 'ENG_ECO_IMPLEMENTED'
2513 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2514 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2515 , p_Token_Tbl => l_Token_Tbl
2516 );
2517 ELSIF NVL(Eng_Globals.Is_Eco_Cancl, FALSE) = TRUE
2518 THEN
2519 l_return_status := FND_API.G_RET_STS_ERROR;
2520 Error_Handler.Add_Error_Token
2521 ( p_Message_Name => 'ENG_ECO_CANCELLED'
2522 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2523 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2524 , p_Token_Tbl => l_Token_Tbl
2525 );
2526 ELSIF NVL(Eng_Globals.Is_WKFL_Process, FALSE) = TRUE
2527 THEN
2528 l_return_status := FND_API.G_RET_STS_ERROR;
2529 Error_Handler.Add_Error_Token
2530 ( p_Message_Name => 'ENG_ECO_WKFL_EXISTS'
2531 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2532 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2533 , p_Token_Tbl => l_Token_Tbl
2534 );
2535 ELSIF NVL(Eng_Globals.Is_Eco_Access, TRUE) = FALSE
2536 THEN
2537 l_return_status := FND_API.G_RET_STS_ERROR;
2538 l_token_tbl(2).token_name := 'CHANGE_TYPE_CODE';
2539 l_token_tbl(2).token_value := p_change_type_code;
2540 Error_Handler.Add_Error_Token
2541 ( p_Message_Name => 'ENG_ECO_ACCESS_DENIED'
2542 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2543 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2544 , p_Token_Tbl => l_Token_Tbl
2545 );
2546 END IF;
2547 --Check if Workflow is in progress
2548 IF l_WorkflowInprogressExists = TRUE
2549 THEN
2550 l_return_status := FND_API.G_RET_STS_ERROR;
2551 Error_Handler.Add_Error_Token
2552 ( p_Message_Name => 'ENG_ECO_WKFL_INPROGRESS'
2553 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2554 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2555 , p_Token_Tbl => l_Token_Tbl
2556 );
2557 -- Added for Bug 4033479
2558 ELSIF nvl(l_update_allowed, TRUE) = FALSE
2559 THEN
2560 l_Token_Tbl(2).token_name := 'STATUS_NAME';
2561 l_Token_Tbl(2).token_value := l_status_name;
2562 l_return_status := FND_API.G_RET_STS_ERROR;
2563 Error_Handler.Add_Error_Token
2564 ( p_Message_Name => 'ENG_CHGUPD_NOTALLOWED'
2565 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2566 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2567 , p_Token_Tbl => l_Token_Tbl
2568 );
2569 END IF;
2570 x_return_status := l_return_status;
2571 x_mesg_token_tbl := l_mesg_token_tbl;
2572
2573 END Check_Access;
2574
2575 END ENG_Validate_Eco;