[Home] [Help]
PACKAGE BODY: APPS.ENG_VALIDATE_ECO
Source
1 PACKAGE BODY ENG_VALIDATE_ECO AS
2 /* $Header: ENGLECOB.pls 120.5 2007/06/01 09:14:20 pguharay 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 <> 11)
1383 OR (nvl(p_ECO_rec.plm_or_erp_change,'PLM') = 'PLM' AND p_Unexp_ECO_rec.status_type NOT IN (0,1,4,11)))
1384 THEN
1385 l_token_tbl(1).token_name := 'STATUS_TYPE';
1386 l_token_tbl(1).token_value := p_Unexp_ECO_Rec.Status_Type;
1387
1388 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1389 THEN
1390 Error_Handler.Add_Error_Token
1391 ( p_Message_Name => 'ENG_ECO_CREATE_STAT_INVALID'
1392 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1393 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1394 , p_Token_Tbl => l_Token_Tbl
1395 );
1396 END IF;
1397 x_return_status := FND_API.G_RET_STS_ERROR;
1398 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1399 END IF;
1400
1401 --Bug 2908248
1402 --Cancel /Schedule /Open/Completed is allowed in update mode
1403
1404 IF p_ECO_rec.transaction_type = 'UPDATE' and
1405 ( p_Unexp_ECO_rec.status_type <> 1 AND p_Unexp_ECO_rec.status_type <> 4 AND p_Unexp_ECO_rec.status_type <> 11
1406 and p_Unexp_ECO_rec.status_type <> 5 and
1407 p_Unexp_ECO_rec.status_type <> 7 ) --- Added for Bug 3108743
1408 THEN
1409 l_token_tbl(1).token_name := 'STATUS_TYPE';
1410 l_token_tbl(1).token_value := p_Unexp_ECO_Rec.Status_Type;
1411
1412 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1413 THEN
1414 Error_Handler.Add_Error_Token
1415 ( p_Message_Name => 'ENG_ECO_CREATE_STAT_INVALID'
1416 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1417 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1418 , p_Token_Tbl => l_Token_Tbl
1419 );
1420 END IF;
1421 x_return_status := FND_API.G_RET_STS_ERROR;
1422 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1423 END IF;
1424
1425 --Bug 2908248
1426
1427 --When trying to cancel/complete a ECO check if all lines/revised items are completed/cancelled/implemented
1428 l_change_id := Get_Change_Id(p_ECO_rec.eco_name, p_Unexp_ECO_rec.Organization_Id);
1429 IF( p_Unexp_ECO_rec.status_type = 11 OR p_Unexp_ECO_rec.status_type = 5 ) then
1430 IF p_change_line_tbl.Count <> 0 THEN
1431 --both for create and update
1432 FOR I IN 1..p_change_line_tbl.count LOOP
1433 l_change_line_rec := p_change_line_tbl(I);
1434 if
1435 ( (UPPER(l_change_line_rec.status_name) = UPPER('Completed') or UPPER(l_change_line_rec.status_name) = UPPER('Cancelled') )
1436 and l_change_line_rec.eco_name = p_ECO_rec.ECO_Name
1437 and upper(l_change_line_rec.transaction_type) = 'UPDATE'
1438 )
1439 then
1440 l_cl_cico_count :=l_cl_cico_count +1;
1441 end if;
1442 END LOOP;
1443 END IF;
1444
1445 --check for implemented revised items: bug:5414834
1446 for rec in revised_items_for_eco(l_change_id)
1447 loop
1448 if(rec.status_type = 6) then
1449 l_rev_item_cnt :=l_rev_item_cnt +1; -- no of implemented revised items
1450 end if;
1451 end loop;
1452
1453 --check for revised items :
1454 IF p_revised_item_tbl.count <> 0 THEN
1455 --both for create and update
1456 FOR I IN 1..p_revised_item_tbl.count LOOP
1457 l_revised_item_rec := p_revised_item_tbl(I);
1458 if
1459 ( (l_revised_item_rec.status_type = 6)
1460 and l_revised_item_rec.eco_name = p_ECO_rec.ECO_Name
1461 )
1462 then
1463 l_er_cico_count :=l_er_cico_count +1;
1464 end if;
1465 END LOOP;
1466 END IF;
1467
1468 -- variables required for lines validation
1469 l_cl_count :=0;
1470 l_cl_up_count :=0;
1471
1472 --variables required for revised items
1473 l_er_count :=0;
1474 l_er_up_count :=0;
1475
1476 if (UPPER(p_ECO_rec.transaction_type) = 'UPDATE') then
1477
1478 l_change_id := Get_Change_Id(p_ECO_rec.eco_name, p_Unexp_ECO_rec.Organization_Id);
1479
1480 --checking for lines.
1481 for lines_for_eco_rec in lines_for_eco(l_change_id) loop
1482 l_cl_count := l_cl_count+1;
1483 l_up_ch :=0;
1484 IF p_change_line_tbl.Count <> 0 THEN
1485 FOR I IN 1..p_change_line_tbl.count LOOP
1486 l_change_line_rec := p_change_line_tbl(I);
1487 if l_change_line_rec.sequence_number = lines_for_eco_rec.sequence_number
1488 and
1489 l_change_line_rec.name = lines_for_eco_rec.name
1490 then
1491 l_up_ch :=1; --we need not check in eng_change_lines as it being updated now
1492 end if;
1493 END LOOP;
1494 END IF; --p_change_line_tbl.Count <> 0
1495 if(
1496 ((l_up_ch = 0) AND (lines_for_eco_rec.status_code = 5))
1497
1498 OR
1499 ((l_up_ch = 0) AND(lines_for_eco_rec.status_code = 11) )
1500
1501 OR
1502 ((l_up_ch = 0) AND(lines_for_eco_rec.status_code = 6) )
1503
1504 )then
1505 l_cl_up_count :=l_cl_up_count+1;
1506 elsif l_up_ch =1 then
1507 l_cl_count:=l_cl_count -1;
1508 end if;
1509
1510 end loop;
1511 l_er_count :=0;
1512 l_er_up_count :=0;
1513
1514 --checking for revised items
1515
1516 for revised_items_for_eco_rec in revised_items_for_eco(l_change_id) loop
1517 l_er_count := l_er_count+1;
1518 l_up_cr :=0;
1519 IF p_revised_item_tbl.Count <> 0 THEN
1520 FOR I IN 1..p_revised_item_tbl.Count LOOP
1521 l_revised_item_rec := p_revised_item_tbl(I);
1522 if l_revised_item_rec .Start_Effective_Date = revised_items_for_eco_rec.scheduled_date
1523 then
1524 l_up_cr :=1; --we need not check in eng_change_lines as it being updated now
1525 end if;
1526 END LOOP;
1527 END IF; --p_change_line_tbl.Count <> 0
1528
1529 if(
1530 ((l_up_cr = 0) AND (revised_items_for_eco_rec.status_type = 5))
1531
1532 OR
1533 ((l_up_cr = 0) AND(revised_items_for_eco_rec.status_type = 11) )
1534 OR
1535 ((l_up_cr = 0) AND(revised_items_for_eco_rec.status_type = 6) )
1536
1537
1538 )then
1539 l_er_up_count :=l_er_up_count+1;
1540 elsif l_up_cr =1 then
1541 l_er_count:=l_er_count -1;
1542 end if;
1543
1544 end loop;
1545 end if; --UPPER(p_ECO_rec.transaction_type) = 'UPDATE'
1546
1547 --Fix for bug:5414834
1548 --if(l_cl_cico_count <> p_change_line_tbl.Count or l_cl_count <> l_cl_up_count or
1549 -- or l_er_count <> l_er_up_count
1550
1551 -- check for implemented revised items
1552 if (l_rev_item_cnt > 0 or l_er_cico_count >0)then
1553 l_token_tbl(1).token_name := 'STATUS_NAME';
1554 l_token_tbl(1).token_value := p_Unexp_ECO_Rec.Status_Type;
1555 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1556 THEN
1557 Error_Handler.Add_Error_Token
1558 ( p_Message_Name => 'ENG_ECO_CREATE_STAT_INVALID'
1559 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1560 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1561 , p_Token_Tbl => l_Token_Tbl
1562 );
1563 END IF;
1564 x_return_status := FND_API.G_RET_STS_ERROR;
1565 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1566 end if;
1567
1568 END IF; --p_Unexp_ECO_rec.status_type = 11
1569
1570 --End of Bug 2908248
1571
1572 -- Cannot implement ECOs through open interface
1573
1574 IF p_Unexp_ECO_rec.status_type = 6
1575 THEN
1576 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1577 THEN
1578 Error_Handler.Add_Error_Token
1579 ( p_Message_Name => 'ENG_ECO_STAT_CANNOT_BE_IMPL'
1580 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1581 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1582 );
1583 END IF;
1584 x_return_status := FND_API.G_RET_STS_ERROR;
1585 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1586 END IF;
1587
1588 END IF;
1589
1590 IF p_ECO_rec.priority_code IS NOT NULL AND
1591 ( p_ECO_rec.priority_code <>
1592 p_old_ECO_rec.priority_code OR
1593 p_old_ECO_rec.priority_code IS NULL )
1594 THEN
1595 IF NOT ENG_Validate.Priority
1596 ( p_priority_code => p_ECO_rec.priority_code
1597 , p_organization_id => p_Unexp_ECO_rec.organization_id
1598 , x_disable_date => l_disable_date
1599 , x_err_text => l_err_text
1600 )
1601 THEN
1602 IF l_err_text = ''
1603 THEN
1604 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1605 END IF;
1606
1607 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1608 THEN
1609 l_token_tbl(2).token_name := 'PRIORITY_CODE';
1610 l_token_tbl(2).token_value := p_ECO_Rec.Priority_Code;
1611 Error_Handler.Add_Error_Token
1612 ( p_Message_Name => 'ENG_PRIORITY_CODE_INVALID'
1613 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1614 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1615 , p_Token_Tbl => l_Token_Tbl
1616 );
1617 END IF;
1618 x_return_status := FND_API.G_RET_STS_ERROR;
1619 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1620 END IF;
1621
1622 IF NVL(l_disable_date, SYSDATE + 1) <= SYSDATE
1623 THEN
1624 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1625 THEN
1626 l_token_tbl(2).token_name := 'PRIORITY_CODE';
1627 l_token_tbl(2).token_value := p_ECO_Rec.Priority_Code;
1628 Error_Handler.Add_Error_Token
1629 ( p_Message_Name => 'ENG_PRIORITY_CODE_DISABLED'
1630 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1631 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1632 , p_Token_Tbl => l_Token_Tbl
1633 );
1634 END IF;
1635 x_return_status := FND_API.G_RET_STS_ERROR;
1636 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1637 END IF;
1638 IF p_ECO_rec.plm_or_erp_change ='PLM' then
1639
1640 l_valid_priority :=0;
1641
1642 for valid_prio_for_eco_type in GetValidPriorities(p_Unexp_ECO_rec.change_order_type_id) loop
1643 if valid_prio_for_eco_type.priority_code = p_ECO_rec.priority_code then
1644 l_valid_priority := 1;
1645 end if;
1646 end loop;
1647 if l_valid_priority = 0 then
1648 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1649 THEN
1650 l_token_tbl(2).token_name := 'PRIORITY_CODE';
1651 l_token_tbl(2).token_value := p_ECO_Rec.Priority_Code;
1652 Error_Handler.Add_Error_Token
1653 ( p_Message_Name => 'ENG_PRIORITY_CODE_INVALID'
1654 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1655 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1656 , p_Token_Tbl => l_Token_Tbl
1657 );
1658 END IF;
1659 x_return_status := FND_API.G_RET_STS_ERROR;
1660 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1661
1662 end if;
1663 END IF;
1664
1665 -- Commented out the following as this validation is not required for 11.5.10
1666 /*
1667 --Bug 2950311
1668 ELSIF (p_ECO_rec.priority_code IS NULL
1669 AND p_ECO_rec.Assignee IS NOT NULL) THEN
1670
1671
1672 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1673 THEN
1674 l_token_tbl(2).token_name := 'ECO_NAME';
1675 l_token_tbl(2).token_value := p_ECO_Rec.Eco_Name;
1676 Error_Handler.Add_Error_Token
1677 ( p_Message_Name => 'ENG_PRIORITY_CODE_INVALID'
1678 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1679 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1680 , p_Token_Tbl => l_Token_Tbl
1681 );
1682 END IF;
1683 x_return_status := FND_API.G_RET_STS_ERROR;
1684 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1685 */
1686 END IF;
1687
1688 IF p_ECO_rec.reason_code IS NOT NULL AND
1689 ( p_ECO_rec.reason_code <>
1690 p_old_ECO_rec.reason_code OR
1691 p_old_ECO_rec.reason_code IS NULL )
1692 THEN
1693 IF NOT ENG_Validate.Reason
1694 ( p_reason_code => p_ECO_rec.reason_code
1695 , p_organization_id => p_Unexp_ECO_rec.organization_id
1696 , x_disable_date => l_disable_date
1697 , x_err_text => l_err_text
1698 )
1699 THEN
1700 IF l_err_text = ''
1701 THEN
1702 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1703 END IF;
1704
1705 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1706 THEN
1707 l_token_tbl(2).token_name := 'REASON_CODE';
1708 l_token_tbl(2).token_value := p_ECO_Rec.Reason_Code;
1709 Error_Handler.Add_Error_Token
1710 ( p_Message_Name => 'ENG_REASON_CODE_INVALID'
1711 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1712 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1713 , p_Token_Tbl => l_Token_Tbl
1714 );
1715 END IF;
1716 x_return_status := FND_API.G_RET_STS_ERROR;
1717 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1718 END IF;
1719 IF NVL(l_disable_date, SYSDATE + 1) <= SYSDATE
1720 THEN
1721 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1722 THEN
1723 l_token_tbl(2).token_name := 'REASON_CODE';
1724 l_token_tbl(2).token_value := p_ECO_Rec.Reason_Code;
1725 Error_Handler.Add_Error_Token
1726 ( p_Message_Name => 'ENG_REASON_CODE_DISABLED'
1727 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1728 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1729 , p_Token_Tbl => l_Token_Tbl
1730 );
1731 END IF;
1732 x_return_status := FND_API.G_RET_STS_ERROR;
1733 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1734 END IF;
1735 --11.5.10
1736 IF p_ECO_rec.plm_or_erp_change ='PLM' then
1737
1738 l_valid_reason :=0;
1739
1740 for valid_rea_for_eco_type in GetValidReasons(p_Unexp_ECO_rec.change_order_type_id) loop
1741 if valid_rea_for_eco_type.Reason_Code = p_ECO_Rec.Reason_Code then
1742 l_valid_reason :=1;
1743 end if;
1744 end loop;
1745 if l_valid_reason = 0 then
1746
1747 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1748 THEN
1749 l_token_tbl(2).token_name := 'REASON_CODE';
1750 l_token_tbl(2).token_value := p_ECO_Rec.Reason_Code;
1751 Error_Handler.Add_Error_Token
1752 ( p_Message_Name => 'ENG_REASON_CODE_INVALID'
1753 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1754 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1755 , p_Token_Tbl => l_Token_Tbl
1756 );
1757 END IF;
1758 x_return_status := FND_API.G_RET_STS_ERROR;
1759 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1760
1761 end if;
1762 --11.5.10
1763 END IF;
1764
1765
1766 END IF;
1767
1768 /* Added by MK on 11/29/00 Bug #1508078
1769 -- Attribute validation for hierarchy_flag and organization_hierarchy
1770 --
1771 */
1772
1773
1774 /* User may not set null in Update,
1775 -- because hierarchy_flag does not exist interface table,
1776 -- Hence following logic is commented out.
1777 -- Set 2:No to hierarchy_flag in Entity Defaulting
1778 -- when hierarchy_flag = FND_API.G_MISS_NUM
1779 --
1780 IF p_ECO_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
1781 THEN
1782 IF p_ECO_rec.hierarchy_flag = FND_API.G_MISS_NUM
1783 THEN
1784 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1785 THEN
1786 Error_Handler.Add_Error_Token
1787 ( p_Message_Name => 'ENG_HIERARCHY_FLAG_MISSING'
1788 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1789 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1790 , p_Token_Tbl => l_Token_Tbl
1791 );
1792 END IF;
1793 x_return_status := FND_API.G_RET_STS_ERROR;
1794 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1795
1796 END IF ;
1797 END IF ;
1798
1799 IF NVL(p_ECO_rec.hierarchy_flag,2 ) NOT IN (1, 2 )
1800 AND p_ECO_rec.hierarchy_flag <> FND_API.G_MISS_NUM
1801 THEN
1802 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1803 THEN
1804 Error_Handler.Add_Error_Token
1805 ( p_Message_Name => 'ENG_HIERARCHY_FLAG_INVALID'
1806 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1807 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1808 , p_Token_Tbl => l_Token_Tbl
1809 );
1810 END IF;
1811 x_return_status := FND_API.G_RET_STS_ERROR;
1812 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1813 END IF;
1814 */
1815
1816
1817
1818 -- Eng Change
1819 IF NVL(p_ECO_rec.internal_use_only,1 ) NOT IN (1, 2 )
1820 AND p_ECO_rec.internal_use_only <> FND_API.G_MISS_NUM
1821 THEN
1822 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1823 THEN
1824 Error_Handler.Add_Error_Token
1825 ( p_Message_Name => 'ENG_INTL_USE_ONLY_FLAG_INVALID'
1826 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1827 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1828 , p_Token_Tbl => l_Token_Tbl
1829 );
1830 END IF;
1831 x_return_status := FND_API.G_RET_STS_ERROR;
1832 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1833 END IF;
1834
1835 IF p_ECO_rec.need_by_date < SYSDATE
1836 AND p_ECO_rec.need_by_date <> FND_API.G_MISS_DATE
1837 AND p_ECO_rec.need_by_date IS NOT NULL
1838 THEN
1839 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1840 THEN
1841 Error_Handler.Add_Error_Token
1842 ( p_Message_Name => 'ENG_NEED_BY_DATE_LESS_CURR'
1843 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1844 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1845 , p_Token_Tbl => l_Token_Tbl
1846 );
1847 END IF;
1848 x_return_status := FND_API.G_RET_STS_ERROR;
1849 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1850 END IF;
1851
1852 IF p_ECO_rec.effort < 0
1853 AND p_ECO_rec.effort <> FND_API.G_MISS_NUM
1854 AND p_ECO_rec.effort IS NOT NULL
1855 THEN
1856 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1857 THEN
1858 Error_Handler.Add_Error_Token
1859 ( p_Message_Name => 'ENG_EFFORT_LESS_ZERO'
1860 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1861 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1862 , p_Token_Tbl => l_Token_Tbl
1863 );
1864 END IF;
1865 x_return_status := FND_API.G_RET_STS_ERROR;
1866 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1867 END IF;
1868
1869
1870 -- These calls are temporarily commented out
1871
1872 /*
1873 IF (p_ECO_rec.attribute7 IS NOT NULL AND
1874 ( p_ECO_rec.attribute7 <>
1875 p_old_ECO_rec.attribute7 OR
1876 p_old_ECO_rec.attribute7 IS NULL ))
1877 OR (p_ECO_rec.attribute8 IS NOT NULL AND
1878 ( p_ECO_rec.attribute8 <>
1879 p_old_ECO_rec.attribute8 OR
1880 p_old_ECO_rec.attribute8 IS NULL ))
1881 OR (p_ECO_rec.attribute9 IS NOT NULL AND
1882 ( p_ECO_rec.attribute9 <>
1883 p_old_ECO_rec.attribute9 OR
1884 p_old_ECO_rec.attribute9 IS NULL ))
1885 OR (p_ECO_rec.attribute10 IS NOT NULL AND
1886 ( p_ECO_rec.attribute10 <>
1887 p_old_ECO_rec.attribute10 OR
1888 p_old_ECO_rec.attribute10 IS NULL ))
1889 OR (p_ECO_rec.attribute11 IS NOT NULL AND
1890 ( p_ECO_rec.attribute11 <>
1891 p_old_ECO_rec.attribute11 OR
1892 p_old_ECO_rec.attribute11 IS NULL ))
1893 OR (p_ECO_rec.attribute12 IS NOT NULL AND
1894 ( p_ECO_rec.attribute12 <>
1895 p_old_ECO_rec.attribute12 OR
1896 p_old_ECO_rec.attribute12 IS NULL ))
1897 OR (p_ECO_rec.attribute13 IS NOT NULL AND
1898 ( p_ECO_rec.attribute13 <>
1899 p_old_ECO_rec.attribute13 OR
1900 p_old_ECO_rec.attribute13 IS NULL ))
1901 OR (p_ECO_rec.attribute14 IS NOT NULL AND
1902 ( p_ECO_rec.attribute14 <>
1903 p_old_ECO_rec.attribute14 OR
1904 p_old_ECO_rec.attribute14 IS NULL ))
1905 OR (p_ECO_rec.attribute15 IS NOT NULL AND
1906 ( p_ECO_rec.attribute15 <>
1907 p_old_ECO_rec.attribute15 OR
1908 p_old_ECO_rec.attribute15 IS NULL ))
1909 OR (p_ECO_rec.attribute_category IS NOT NULL AND
1910 ( p_ECO_rec.attribute_category <>
1911 p_old_ECO_rec.attribute_category OR
1912 p_old_ECO_rec.attribute_category IS NULL ))
1913 OR (p_ECO_rec.attribute1 IS NOT NULL AND
1914 ( p_ECO_rec.attribute1 <>
1915 p_old_ECO_rec.attribute1 OR
1916 p_old_ECO_rec.attribute1 IS NULL ))
1917 OR (p_ECO_rec.attribute2 IS NOT NULL AND
1918 ( p_ECO_rec.attribute2 <>
1919 p_old_ECO_rec.attribute2 OR
1920 p_old_ECO_rec.attribute2 IS NULL ))
1921 OR (p_ECO_rec.attribute3 IS NOT NULL AND
1922 ( p_ECO_rec.attribute3 <>
1923 p_old_ECO_rec.attribute3 OR
1924 p_old_ECO_rec.attribute3 IS NULL ))
1925 OR (p_ECO_rec.attribute4 IS NOT NULL AND
1926 ( p_ECO_rec.attribute4 <>
1927 p_old_ECO_rec.attribute4 OR
1928 p_old_ECO_rec.attribute4 IS NULL ))
1929 OR (p_ECO_rec.attribute5 IS NOT NULL AND
1930 ( p_ECO_rec.attribute5 <>
1931 p_old_ECO_rec.attribute5 OR
1932 p_old_ECO_rec.attribute5 IS NULL ))
1933 OR (p_ECO_rec.attribute6 IS NOT NULL AND
1934 ( p_ECO_rec.attribute6 <>
1935 p_old_ECO_rec.attribute6 OR
1936 p_old_ECO_rec.attribute6 IS NULL ))
1937 THEN
1938
1939 FND_FLEX_DESC_VAL.Set_Column_Value
1940 ( column_name => 'ATTRIBUTE7'
1941 , column_value => p_ECO_rec.attribute7
1942 );
1943 FND_FLEX_DESC_VAL.Set_Column_Value
1944 ( column_name => 'ATTRIBUTE8'
1945 , column_value => p_ECO_rec.attribute8
1946 );
1947 FND_FLEX_DESC_VAL.Set_Column_Value
1948 ( column_name => 'ATTRIBUTE9'
1949 , column_value => p_ECO_rec.attribute9
1950 );
1951 FND_FLEX_DESC_VAL.Set_Column_Value
1952 ( column_name => 'ATTRIBUTE10'
1953 , column_value => p_ECO_rec.attribute10
1954 );
1955 FND_FLEX_DESC_VAL.Set_Column_Value
1956 ( column_name => 'ATTRIBUTE11'
1957 , column_value => p_ECO_rec.attribute11
1958 );
1959 FND_FLEX_DESC_VAL.Set_Column_Value
1960 ( column_name => 'ATTRIBUTE12'
1961 , column_value => p_ECO_rec.attribute12
1962 );
1963 FND_FLEX_DESC_VAL.Set_Column_Value
1964 ( column_name => 'ATTRIBUTE13'
1965 , column_value => p_ECO_rec.attribute13
1966 );
1967 FND_FLEX_DESC_VAL.Set_Column_Value
1968 ( column_name => 'ATTRIBUTE14'
1969 , column_value => p_ECO_rec.attribute14
1970 );
1971 FND_FLEX_DESC_VAL.Set_Column_Value
1972 ( column_name => 'ATTRIBUTE15'
1973 , column_value => p_ECO_rec.attribute15
1974 );
1975 FND_FLEX_DESC_VAL.Set_Column_Value
1976 ( column_name => 'ATTRIBUTE_CATEGORY'
1977 , column_value => p_ECO_rec.attribute_category
1978 );
1979 FND_FLEX_DESC_VAL.Set_Column_Value
1980 ( column_name => 'ATTRIBUTE1'
1981 , column_value => p_ECO_rec.attribute1
1982 );
1983 FND_FLEX_DESC_VAL.Set_Column_Value
1984 ( column_name => 'ATTRIBUTE2'
1985 , column_value => p_ECO_rec.attribute2
1986 );
1987 FND_FLEX_DESC_VAL.Set_Column_Value
1988 ( column_name => 'ATTRIBUTE3'
1989 , column_value => p_ECO_rec.attribute3
1990 );
1991 FND_FLEX_DESC_VAL.Set_Column_Value
1992 ( column_name => 'ATTRIBUTE4'
1993 , column_value => p_ECO_rec.attribute4
1994 );
1995 FND_FLEX_DESC_VAL.Set_Column_Value
1996 ( column_name => 'ATTRIBUTE5'
1997 , column_value => p_ECO_rec.attribute5
1998 );
1999 FND_FLEX_DESC_VAL.Set_Column_Value
2000 ( column_name => 'ATTRIBUTE6'
2001 , column_value => p_ECO_rec.attribute6
2002 );
2003
2004 -- Validate descriptive flexfield.
2005
2006 IF NOT ENG_Validate.Desc_Flex( 'ECO' ) THEN
2007 x_return_status := FND_API.G_RET_STS_ERROR;
2008 END IF;
2009
2010 END IF;
2011 */
2012
2013 -- Done validating attributes
2014
2015 EXCEPTION
2016
2017 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2018
2019 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2020 x_err_text := l_err_text;
2021
2022 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2023 THEN
2024 Error_Handler.Add_Error_Token
2025 ( p_Message_Text => l_err_text
2026 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2027 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
2028 );
2029 END IF;
2030 END Check_Attributes;
2031
2032 -- Procedure Check_Required
2033
2034 PROCEDURE Conditionally_Required
2035 ( x_return_status OUT NOCOPY VARCHAR2
2036 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2037 , p_ECO_rec IN ENG_ECO_PUB.Eco_Rec_Type
2038 , p_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_Unexposed_Rec_Type
2039 , p_old_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
2040 , p_old_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_Unexposed_Rec_Type
2041 )
2042 IS
2043 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2044 l_err_text VARCHAR2(2000) := NULL;
2045 l_Token_Tbl Error_Handler.Token_Tbl_Type;
2046 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2047 BEGIN
2048
2049 x_return_status := FND_API.G_RET_STS_SUCCESS;
2050
2051 l_token_tbl(1).token_name := 'ECO_NAME';
2052 l_token_tbl(1).token_value := p_ECO_Rec.ECO_Name;
2053
2054 -- responsible_org_id must not be null if profile option is set to yes
2055
2056
2057 -- Bug : 2516871
2058 -- If this function is called from MCO then, the below filter condition for
2059 -- validating the ENG:MANDATORY_ECO_DEPT should not be executed.
2060
2061 IF (Bom_globals.Get_Caller_Type = BOM_GLOBALS.G_MASS_CHANGE) THEN
2062 NULL ;
2063 ELSE
2064
2065 IF (FND_PROFILE.DEFINED('ENG:MANDATORY_ECO_DEPT') AND
2066 FND_PROFILE.VALUE('ENG:MANDATORY_ECO_DEPT') = '1')
2067 AND p_Unexp_ECO_rec.responsible_org_id IS NULL
2068 THEN
2069 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2070 THEN
2071 Error_Handler.Add_Error_Token
2072 ( p_Message_Name => 'ENG_RESP_ORG_MISSING'
2073 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2074 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
2075 , p_Token_Tbl => l_Token_Tbl
2076 );
2077 END IF;
2078 x_return_status := FND_API.G_RET_STS_ERROR;
2079 END IF;
2080
2081 END IF ;
2082
2083 /* Added by MK on 11/29/00 Bug #1508078
2084 -- Conditionally required validation for hierarchy_flag and organization_hierarchy
2085 --
2086 IF p_ECO_rec.hierarchy_flag = 1 AND
2087 NVL(p_ECO_rec.organization_hierarchy, FND_API.G_MISS_CHAR)
2088 = FND_API.G_MISS_CHAR
2089 THEN
2090
2091 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2092 THEN
2093 Error_Handler.Add_Error_Token
2094 ( p_Message_Name => 'ENG_ORG_HIERARCHY_MISSING'
2095 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2096 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
2097 , p_Token_Tbl => l_Token_Tbl
2098 );
2099 END IF;
2100 x_return_status := FND_API.G_RET_STS_ERROR;
2101 END IF;
2102 */
2103
2104
2105 EXCEPTION
2106
2107 WHEN OTHERS THEN
2108
2109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2110
2111 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2112 THEN
2113 l_err_text := G_PKG_NAME || ' : (Conditionally Required Fields Check) ' || substrb(SQLERRM,1,200);
2114 Error_Handler.Add_Error_Token
2115 ( p_Message_Text => l_err_text
2116 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2117 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
2118 );
2119 END IF;
2120
2121 END Conditionally_Required;
2122
2123 /***************************************************************************
2124 * Procedure : Check_Existence
2125 * Parameters IN : ECO Name
2126 * Organization Id
2127 * Parameters OUT: Old Eco exposed column record
2128 * Old ECO unexposed column record
2129 * Purpose : Check Existence will verify that the ECO record does not
2130 * already exist for creates and it does exist when the user
2131 * is performing an Update or Delete.
2132 * If Update or Delete the procedure will also return the old
2133 * database record.
2134 *****************************************************************************/
2135 PROCEDURE Check_Existence
2136 ( p_change_notice IN VARCHAR2
2137 , p_organization_id IN NUMBER
2138 , p_organization_code IN VARCHAR2
2139 , p_calling_entity IN VARCHAR2
2140 , p_transaction_type IN VARCHAR2
2141 , x_eco_rec OUT NOCOPY Eng_Eco_Pub.Eco_Rec_Type
2142 , x_eco_unexp_rec OUT NOCOPY Eng_Eco_Pub.Eco_Unexposed_Rec_Type
2143 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2144 , x_Return_Status OUT NOCOPY VARCHAR2
2145 )
2146 IS
2147 l_Mesg_token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2148 l_return_status VARCHAR2(1);
2149 l_err_text VARCHAR2(2000);
2150 l_Token_Tbl Error_Handler.Token_Tbl_Type;
2151 BEGIN
2152 l_return_status := FND_API.G_RET_STS_SUCCESS;
2153
2154 l_token_tbl(1).token_name := 'ECO_NAME';
2155 l_token_tbl(1).token_value := p_change_notice;
2156 l_token_tbl(2).token_name := 'ORGANIZATION_CODE';
2157 l_token_tbl(2).token_value := p_organization_code;
2158
2159 Eng_Eco_Util.Query_Row
2160 ( p_change_notice => p_change_notice
2161 , p_organization_id => p_organization_id
2162 , x_ECO_rec => x_eco_rec
2163 , x_ECO_Unexp_Rec => x_eco_unexp_rec
2164 , x_return_status => l_return_status
2165 , x_err_text => l_err_text
2166 );
2167
2168 IF l_return_status = Eng_Globals.G_RECORD_FOUND AND
2169 p_calling_entity = 'ECO' AND
2170 p_transaction_type = Eng_Globals.G_OPR_CREATE
2171 THEN
2172 l_return_status := FND_API.G_RET_STS_ERROR;
2173 Error_Handler.Add_Error_Token
2174 ( p_Message_Name => 'ENG_ECO_ALREADY_EXISTS'
2175 , p_Mesg_Token_Tbl => l_mesg_token_tbl
2176 , x_Mesg_Token_Tbl => l_mesg_token_tbl
2177 , p_Token_Tbl => l_token_tbl
2178 );
2179 /* Commenting the following Code for Bug 3127841 as per Mani's suggestion
2180 ELSIF l_return_status = Eng_Globals.G_RECORD_FOUND AND
2181 p_transaction_type = Eng_Globals.G_OPR_UPDATE AND
2182 x_eco_unexp_rec.approval_status_type in (3, 5) -- approved or approval requested
2183 THEN
2184 l_return_status := FND_API.G_RET_STS_ERROR;
2185 Error_Handler.Add_Error_Token
2186 ( p_Message_Name => 'ENG_ECO_CANNOT_UPDATE'
2187 , p_Mesg_Token_Tbl => l_mesg_token_tbl
2188 , x_Mesg_Token_Tbl => l_mesg_token_tbl
2189 , p_Token_Tbl => l_token_tbl
2190 );
2191 */
2192 ELSIF l_return_status = Eng_Globals.G_RECORD_NOT_FOUND AND
2193 p_calling_entity = 'ECO' AND
2194 p_transaction_type IN
2195 ( Eng_Globals.G_OPR_UPDATE, Eng_Globals.G_OPR_DELETE)
2196 THEN
2197 l_return_status := FND_API.G_RET_STS_ERROR;
2198 Error_Handler.Add_Error_Token
2199 ( p_Message_Name => 'ENG_ECO_DOES_NOT_EXIST'
2200 , p_Mesg_Token_Tbl => l_mesg_token_tbl
2201 , x_Mesg_Token_Tbl => l_mesg_token_tbl
2202 , p_Token_Tbl => l_token_tbl
2203 );
2204
2205 ELSIF l_return_status = Eng_Globals.G_RECORD_NOT_FOUND AND
2206 p_calling_entity = 'CHILD'
2207 THEN
2208 l_return_status := FND_API.G_RET_STS_ERROR;
2209
2210 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2211 THEN
2212 Error_Handler.Add_Error_Token
2213 ( p_Message_Name => NULL
2214 , p_Message_Text => l_err_text
2215 , p_Mesg_Token_Tbl => l_mesg_token_tbl
2216 , x_Mesg_Token_Tbl => l_mesg_token_tbl
2217 , p_Token_Tbl => l_token_tbl
2218 );
2219 ELSE
2220 l_return_status := FND_API.G_RET_STS_SUCCESS;
2221 END IF;
2222
2223 x_return_status := l_return_status;
2224 x_mesg_token_tbl := l_mesg_token_tbl;
2225
2226 END Check_Existence;
2227
2228
2229 /****************************************************************************
2230 * Function : Check_Workflow_Process
2231 * Pramaters IN : Change_Order_Type_Id
2232 * Priority Code
2233 * Organization_ID
2234 * Assignee_ID
2235 * Change_ID
2236 * Returns : TRUE if the ECO has a Workflow process associated with it.
2237 * Otherwise returns a False.
2238 * Purpose : Checks if there is worflow process for the ECO.
2239 *****************************************************************************/
2240 FUNCTION Check_Workflow_Process
2241 ( p_change_order_type_id IN NUMBER
2242 , p_priority_code IN VARCHAR2
2243 , p_organization_id IN NUMBER
2244 , p_assignee_id IN NUMBER
2245 , p_change_id IN NUMBER
2246 ) RETURN BOOLEAN
2247 IS
2248 CURSOR c_CheckProcess IS
2249 SELECT process_name
2250 FROM eng_change_type_processes
2251 WHERE change_order_type_id = p_change_order_type_id
2252 AND NVL(eng_change_priority_code,'X') = NVL(p_priority_code, 'X');
2253 -- Bug 2921534 ,processes are no more organization specific ,thus commenting out the below where condition
2254 -- AND organization_id = p_organization_id;
2255
2256 ---While bulkloading PLM records we will have to look at route_id
2257 CURSOR c_CheckProcess_PLM(p_change_id NUMBER) IS
2258 SELECT route_id
2259 FROM eng_engineering_changes
2260 WHERE change_id = p_change_id ;
2261
2262 l_route_id NUMBER;
2263
2264 BEGIN
2265
2266 if ( p_assignee_id is null)
2267 then
2268 FOR Process IN c_CheckProcess
2269 LOOP
2270 RETURN TRUE;
2271 END LOOP;
2272 else
2273 OPEN c_CheckProcess_PLM(p_change_id);
2274 FETCH c_CheckProcess_PLM INTO l_route_id;
2275 CLOSE c_CheckProcess_PLM;
2276 if(l_route_id is not null) then
2277 RETURN TRUE;
2278 else
2279 RETURN FALSE;
2280 end if;
2281 end if;
2282
2283 RETURN FALSE;
2284
2285 END Check_Workflow_Process;
2286
2287 /****************************************************************************
2288 * Procedure : Check_Access
2289 * Parameters IN : ECO Primary Key
2290 * Parameters OUT: Mesg Token Tbl
2291 * Return Status
2292 * Purpose : Procedure will verify if the user has access to the current
2293 * ECO byt checking that the eco is not canceled or implemented
2294 * or it does not have a workflow process.
2295 * Th procedure will also check if the user has access to the
2296 * Change order type.
2297 ****************************************************************************/
2298 PROCEDURE Check_Access
2299 ( p_change_notice IN VARCHAR2
2300 , p_organization_id IN NUMBER
2301 , p_change_type_code IN VARCHAR2 := NULL
2302 , p_change_order_type_id IN NUMBER := NULL
2303 , p_Mesg_Token_Tbl IN Error_Handler.Mesg_Token_Tbl_Type :=
2304 Error_Handler.G_MISS_MESG_TOKEN_TBL
2305 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2306 , x_Return_Status OUT NOCOPY VARCHAR2
2307 , p_check_scheduled_status IN BOOLEAN DEFAULT TRUE -- Added for Enhancement 5470261
2308 , p_status_check_required IN BOOLEAN DEFAULT TRUE -- Added for enhancement 5414834
2309 )
2310 IS
2311 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type :=
2312 p_Mesg_Token_Tbl;
2313 l_Token_Tbl Error_Handler.Token_Tbl_Type;
2314 l_return_status VARCHAR2(1);
2315 l_ProcessExists BOOLEAN;
2316 l_WorkflowInprogressExists BOOLEAN; -- bug no 3591968 by Rashmi
2317 l_Wkfl NUMBER; -- bug no 3591968 by Rashmi
2318 l_change_order_assembly_Type NUMBER;
2319 l_change_order_type_id NUMBER := NULL;
2320 --added status_code in select stmt for validation
2321 CURSOR c_CheckECO IS
2322 SELECT status_type, priority_code, change_order_type_id,status_code --bug no 3591968 by Rashmi
2323 ,approval_status_type,assignee_id ,change_id --PLM records we will only have to look for processes based on change_type_id
2324 , nvl(plm_or_erp_change, 'PLM') plm_or_erp_change
2325 FROM eng_engineering_changes
2326 WHERE change_notice = p_change_notice
2327 AND organization_id = p_organization_id;
2328 --bug no 3591968 by Rashmi
2329 /*CURSOR c_CheckProcessInProgress(cp_change_id NUMBER,cp_status_code NUMBER) IS
2330 select status_code
2331 from eng_lifecycle_statuses
2332 where entity_id1 = cp_change_id
2333 and status_code =cp_status_code
2334 and entity_name ='ENG_CHANGE'
2335 and active_flag='Y'
2336 and change_wf_route_id is not null
2337 and workflow_status = 'IN_PROGRESS';*/
2338 -- Bug 4033479
2339 CURSOR c_lifecycle_status(cp_change_id NUMBER,cp_status_code NUMBER) IS
2340 select els.status_code, els.workflow_status, els.change_wf_route_id,
2341 ecs.status_type orig_status_type, els.CHANGE_EDITABLE_FLAG ,
2342 ecs.status_name
2343 from eng_lifecycle_statuses els, eng_change_statuses_vl ecs
2344 where els.ENTITY_NAME = 'ENG_CHANGE'
2345 and els.ENTITY_ID1 = cp_change_id
2346 and els.STATUS_CODE = cp_STATUS_CODE
2347 and els.active_flag = 'Y'
2348 and els.STATUS_CODE = ecs.STATUS_CODE;
2349
2350 l_cls_rec c_lifecycle_status%ROWTYPE;
2351 l_update_allowed BOOLEAN;
2352 l_status_name eng_change_statuses_tl.status_name%TYPE;
2353 BEGIN
2354
2355 l_return_status := FND_API.G_RET_STS_SUCCESS;
2356 l_change_order_type_id := NULL;
2357
2358 l_Token_Tbl(1).token_name := 'ECO_NAME';
2359 l_Token_Tbl(1).token_value := p_change_notice;
2360 --
2361 -- Check that the ECO is not Implemented or Cancelled.
2362 --
2363 IF Eng_Globals.Is_Eco_Impl IS NULL AND
2364 Eng_Globals.Is_Eco_Cancl IS NULL AND
2365 Eng_Globals.Is_WKFL_Process IS NULL AND
2366 Eng_Globals.Is_ECO_Access IS NULL
2367 THEN
2368 FOR ECO IN c_CheckECO
2369 LOOP
2370 IF p_change_order_type_id IS NULL
2371 THEN
2372 l_change_order_type_id :=
2373 eco.change_order_type_id;
2374 END IF;
2375
2376 IF ECO.status_type = 6
2377 THEN
2378 Eng_Globals.Set_Eco_Impl
2379 ( p_eco_impl => TRUE);
2380 ELSIF ECO.status_type = 5
2381 THEN
2382 Eng_Globals.Set_Eco_Cancl
2383 ( p_eco_cancl => TRUE);
2384 ELSIF ECO.status_type NOT IN (5,6)
2385 THEN
2386 Eng_Globals.Set_Eco_Impl
2387 ( p_eco_impl => FALSE);
2388 Eng_Globals.Set_Eco_Cancl
2389 ( p_eco_cancl => FALSE);
2390
2391 --
2392 -- Check if the ECO has a process
2393 --
2394 l_ProcessExists :=
2395 Check_Workflow_Process
2396 ( p_change_order_type_id =>
2397 ECO.change_order_type_id
2398 , p_priority_code =>
2399 ECO.priority_code
2400 , p_organization_id =>
2401 p_organization_id
2402 , p_assignee_id =>
2403 ECO.assignee_id
2404 ,p_change_id =>
2405 ECO.change_id
2406 );
2407
2408 IF l_ProcessExists AND
2409 ECO.approval_status_type = 3
2410 THEN
2411 Eng_Globals.Set_WKFL_Process
2412 ( p_wkfl_process => TRUE);
2413 ELSE
2414 Eng_Globals.Set_WKFL_Process
2415 ( p_wkfl_process => FALSE);
2416 END IF;
2417
2418
2419 END IF;
2420 -- Check if ECO is not in progress --bug no 3591968 by Rashmi
2421 l_WorkflowInprogressExists := FALSE ;
2422 /*OPEN c_CheckProcessInProgress
2423 (cp_change_id => ECO.change_id
2424 ,cp_status_code => ECO.status_code );
2425 FETCH c_CheckProcessInProgress INTO l_Wkfl ;
2426 CLOSE c_CheckProcessInProgress;
2427 if( l_Wkfl is not null) then
2428 l_WorkflowInprogressExists := TRUE ;
2429 else
2430 l_WorkflowInprogressExists := FALSE ;
2431 end if;*/
2432 l_update_allowed := TRUE;
2433 IF(ECO.plm_or_erp_change = 'PLM')
2434 THEN
2435 OPEN c_lifecycle_status (cp_change_id => ECO.change_id
2436 ,cp_status_code => ECO.status_code );
2437 FETCH c_lifecycle_status INTO l_cls_rec ;
2438 IF (l_cls_rec.change_wf_route_id is not NULL
2439 AND l_cls_rec.workflow_status = 'IN_PROGRESS'
2440 AND l_cls_rec.CHANGE_EDITABLE_FLAG <> 'Y')
2441 THEN
2442 l_WorkflowInprogressExists := TRUE ;
2443 END IF;
2444 -- Added for enhancement 5414834
2445 IF(p_status_check_required)
2446 THEN
2447 -- Added for Bug 4033479
2448 IF (ECO.status_type IN (2, 4, 7, 8, 9,11)
2449 OR (ECO.status_type = 10 AND l_cls_rec.orig_status_type <> 1))
2450 THEN
2451 -- Added for Enhancement 5470261
2452 -- If status type is 4<- Scheduled, then check if the p_check_scheduled_status flag is true
2453 -- Only if the p_check_scheduled_status is true, set the flag to throw the error
2454 if(ECO.status_type <> 4 OR p_check_scheduled_status = TRUE) THEN
2455 l_update_allowed := FALSE;
2456 l_status_name := l_cls_rec.status_name;
2457 END if;
2458 -- Code changes for Enhancement 5470261 ends
2459 END IF;
2460 END IF;
2461 CLOSE c_lifecycle_status;
2462 END IF;
2463 END LOOP;
2464 END IF;
2465
2466 IF l_change_order_type_id IS NULL
2467 THEN
2468 l_change_order_type_id := p_change_order_type_id;
2469 END IF;
2470
2471 /****************************************************
2472 --
2473 -- Check if user has access to type of ECO. If the
2474 -- ECO's change order type is Engineering and the
2475 -- Profile value Eng:Engineering Change Order Type
2476 -- Access is NO, then the user cannot access this
2477 -- ECO.
2478 --
2479 *****************************************************/
2480 IF Eng_Globals.Is_ECO_Access IS NULL
2481 THEN
2482 SELECT assembly_type
2483 INTO l_change_order_assembly_Type
2484 FROM eng_change_order_types
2485 WHERE change_order_type_id =
2486 l_change_order_type_id;
2487
2488 IF l_change_order_assembly_type = 2 /* ENG */
2489 AND
2490 Fnd_Profile.Value
2491 ('ENG:ENG_ITEM_ECN_ACCESS')
2492 = 2
2493 THEN
2494 --
2495 -- User does not have access.
2496 --
2497 Eng_Globals.Set_Eco_Access
2498 ( p_eco_access => FALSE);
2499 ELSE
2500 Eng_Globals.Set_Eco_Access
2501 ( p_eco_access => TRUE);
2502 END IF;
2503 END IF;
2504
2505 IF NVL(Eng_Globals.Is_Eco_Impl, FALSE) = TRUE
2506 THEN
2507 l_return_status := FND_API.G_RET_STS_ERROR;
2508 Error_Handler.Add_Error_Token
2509 ( p_Message_Name => 'ENG_ECO_IMPLEMENTED'
2510 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2511 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2512 , p_Token_Tbl => l_Token_Tbl
2513 );
2514 ELSIF NVL(Eng_Globals.Is_Eco_Cancl, FALSE) = TRUE
2515 THEN
2516 l_return_status := FND_API.G_RET_STS_ERROR;
2517 Error_Handler.Add_Error_Token
2518 ( p_Message_Name => 'ENG_ECO_CANCELLED'
2519 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2520 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2521 , p_Token_Tbl => l_Token_Tbl
2522 );
2523 ELSIF NVL(Eng_Globals.Is_WKFL_Process, FALSE) = TRUE
2524 THEN
2525 l_return_status := FND_API.G_RET_STS_ERROR;
2526 Error_Handler.Add_Error_Token
2527 ( p_Message_Name => 'ENG_ECO_WKFL_EXISTS'
2528 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2529 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2530 , p_Token_Tbl => l_Token_Tbl
2531 );
2532 ELSIF NVL(Eng_Globals.Is_Eco_Access, TRUE) = FALSE
2533 THEN
2534 l_return_status := FND_API.G_RET_STS_ERROR;
2535 l_token_tbl(2).token_name := 'CHANGE_TYPE_CODE';
2536 l_token_tbl(2).token_value := p_change_type_code;
2537 Error_Handler.Add_Error_Token
2538 ( p_Message_Name => 'ENG_ECO_ACCESS_DENIED'
2539 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2540 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2541 , p_Token_Tbl => l_Token_Tbl
2542 );
2543 END IF;
2544 --Check if Workflow is in progress
2545 IF l_WorkflowInprogressExists = TRUE
2546 THEN
2547 l_return_status := FND_API.G_RET_STS_ERROR;
2548 Error_Handler.Add_Error_Token
2549 ( p_Message_Name => 'ENG_ECO_WKFL_INPROGRESS'
2550 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2551 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2552 , p_Token_Tbl => l_Token_Tbl
2553 );
2554 -- Added for Bug 4033479
2555 ELSIF nvl(l_update_allowed, TRUE) = FALSE
2556 THEN
2557 l_Token_Tbl(2).token_name := 'STATUS_NAME';
2558 l_Token_Tbl(2).token_value := l_status_name;
2559 l_return_status := FND_API.G_RET_STS_ERROR;
2560 Error_Handler.Add_Error_Token
2561 ( p_Message_Name => 'ENG_CHGUPD_NOTALLOWED'
2562 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2563 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2564 , p_Token_Tbl => l_Token_Tbl
2565 );
2566 END IF;
2567 x_return_status := l_return_status;
2568 x_mesg_token_tbl := l_mesg_token_tbl;
2569
2570 END Check_Access;
2571
2572 END ENG_Validate_Eco;
2573