1 PACKAGE BODY ENG_Default_Eco AS
2 /* $Header: ENGDECOB.pls 120.0.12000000.4 2007/10/12 13:04:05 jiabraha ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_Default_Eco';
7
8 -- Package global used within the package.
9
10 g_ECO_rec ENG_Eco_PUB.Eco_Rec_Type;
11 g_Unexp_ECO_Rec ENG_Eco_PUB.Eco_unexposed_Rec_Type;
12
13 -- Get functions.
14
15 FUNCTION Get_Responsible_Org
16 RETURN NUMBER
17 IS
18 l_profile_value NUMBER := NULL;
19 BEGIN
20
21 IF fnd_profile.defined('ENG:ECO_DEPARTMENT')
22 THEN
23 l_profile_value := fnd_profile.value('ENG:ECO_DEPARTMENT');
24 END IF;
25
26 RETURN l_profile_value;
27
28 END Get_Responsible_Org;
29
30 FUNCTION Get_Change_Id
31 RETURN NUMBER
32 IS
33 l_change_id NUMBER := NULL;
34 BEGIN
35
36 SELECT ENG_ENGINEERING_CHANGES_S.NEXTVAL
37 INTO l_change_id
38 FROM DUAL;
39
40 RETURN l_change_id;
41
42 EXCEPTION
43
44 WHEN OTHERS THEN
45 RETURN NULL;
46
47 END Get_Change_Id;
48
49
50 PROCEDURE Get_Flex_Eco
51 ( p_ECO_rec IN ENG_ECO_PUB.ECO_Rec_Type
52 , x_ECO_rec OUT NOCOPY ENG_ECO_PUB.ECO_Rec_Type
53 )
54 IS
55 l_ECO_rec ENG_Eco_PUB.Eco_Rec_Type := p_ECO_rec;
56 BEGIN
57
58 -- In the future call Flex APIs for defaults
59
60 IF l_ECO_rec.attribute7 = FND_API.G_MISS_CHAR THEN
61 l_ECO_rec.attribute7 := NULL;
62 END IF;
63
64 IF l_ECO_rec.attribute8 = FND_API.G_MISS_CHAR THEN
65 l_ECO_rec.attribute8 := NULL;
66 END IF;
67
68 IF l_ECO_rec.attribute9 = FND_API.G_MISS_CHAR THEN
69 l_ECO_rec.attribute9 := NULL;
70 END IF;
71
72 IF l_ECO_rec.attribute10 = FND_API.G_MISS_CHAR THEN
73 l_ECO_rec.attribute10 := NULL;
74 END IF;
75
76 IF l_ECO_rec.attribute11 = FND_API.G_MISS_CHAR THEN
77 l_ECO_rec.attribute11 := NULL;
78 END IF;
79
80 IF l_ECO_rec.attribute12 = FND_API.G_MISS_CHAR THEN
81 l_ECO_rec.attribute12 := NULL;
82 END IF;
83
84 IF l_ECO_rec.attribute13 = FND_API.G_MISS_CHAR THEN
85 l_ECO_rec.attribute13 := NULL;
86 END IF;
87
88 IF l_ECO_rec.attribute14 = FND_API.G_MISS_CHAR THEN
89 l_ECO_rec.attribute14 := NULL;
90 END IF;
91
92 IF l_ECO_rec.attribute15 = FND_API.G_MISS_CHAR THEN
93 l_ECO_rec.attribute15 := NULL;
94 END IF;
95
96 IF l_ECO_rec.attribute_category = FND_API.G_MISS_CHAR THEN
97 l_ECO_rec.attribute_category := NULL;
98 END IF;
99
100 IF l_ECO_rec.attribute1 = FND_API.G_MISS_CHAR THEN
101 l_ECO_rec.attribute1 := NULL;
102 END IF;
103
104 IF l_ECO_rec.attribute2 = FND_API.G_MISS_CHAR THEN
105 l_ECO_rec.attribute2 := NULL;
106 END IF;
107
108 IF l_ECO_rec.attribute3 = FND_API.G_MISS_CHAR THEN
109 l_ECO_rec.attribute3 := NULL;
110 END IF;
111
112 IF l_ECO_rec.attribute4 = FND_API.G_MISS_CHAR THEN
113 l_ECO_rec.attribute4 := NULL;
114 END IF;
115
116 IF l_ECO_rec.attribute5 = FND_API.G_MISS_CHAR THEN
117 l_ECO_rec.attribute5 := NULL;
118 END IF;
119
120 IF l_ECO_rec.attribute6 = FND_API.G_MISS_CHAR THEN
121 l_ECO_rec.attribute6 := NULL;
122 END IF;
123
124 x_ECO_rec := l_ECO_rec;
125
126 END Get_Flex_Eco;
127
128 -- Procedure Attribute_Defaulting
129
130 PROCEDURE Attribute_Defaulting
131 ( p_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
132 , p_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_unexposed_Rec_Type
133 , x_ECO_rec IN OUT NOCOPY ENG_Eco_PUB.Eco_Rec_Type
134 , x_Unexp_ECO_rec IN OUT NOCOPY ENG_Eco_PUB.Eco_unexposed_Rec_Type
135 , x_return_status OUT NOCOPY VARCHAR2
136 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
137 )
138 IS
139 l_process_name VARCHAR2(30) := NULL;
140 l_err_text VARCHAR2(240) := NULL;
141 l_Token_Tbl Error_Handler.Token_Tbl_Type;
142 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
143
144 cursor status (p_change_order_type_id NUMBER
145 ) is
146 SELECT status_code ,status_type
147 from eng_change_statuses_vl
148 where status_code in
149 (select status_code from eng_lifecycle_statuses
150 where entity_name='ENG_CHANGE_TYPE'
151 and entity_id1 = p_change_order_type_id
152 and sequence_number =
153 (select min(sequence_number) from eng_lifecycle_statuses
154 where entity_name='ENG_CHANGE_TYPE'
155 and entity_id1 = p_change_order_type_id)
156 );
157
158
159 l_status status%ROWTYPE;
160
161 BEGIN
162
163 -- Initialize g_ECO_rec
164 g_ECO_rec := p_ECO_rec;
165 g_Unexp_ECO_rec := p_Unexp_ECO_rec;
166
167 -- Default missing attributes.
168
169 IF g_Unexp_ECO_rec.responsible_org_id IS NULL THEN
170
171 g_Unexp_ECO_rec.responsible_org_id := Get_Responsible_Org;
172
173 END IF;
174
175 /* IF g_Unexp_ECO_rec.status_type IS NULL THEN
176
177 -- Default to 'Open'
178
179 g_Unexp_ECO_rec.status_type := 1;
180
181 END IF; */
182
183 --11.5.10
184 IF g_ECO_rec.plm_or_erp_change='PLM' and g_Unexp_ECO_rec.status_code IS NULL THEN
185
186 OPEN status(g_Unexp_ECO_rec.Change_Order_Type_Id);
187 FETCH status INTO l_status;
188 IF status%FOUND THEN
189 g_Unexp_ECO_rec.status_type := l_status.status_type;
190 g_Unexp_ECO_rec.status_code := l_status.status_code;
191 ELSE
192 g_Unexp_ECO_rec.status_type := 1;
193 g_Unexp_ECO_rec.status_code := 1;
194 END IF;
195 CLOSE status;
196 ELSIF g_Unexp_ECO_rec.status_type IS NULL THEN
197 g_Unexp_ECO_rec.status_type := 1;
198 g_Unexp_ECO_rec.status_code := 1;
199 ELSIF g_Unexp_ECO_rec.status_code IS NULL -- Added for bug 3539102
200 THEN
201 g_Unexp_ECO_rec.status_code := g_Unexp_ECO_rec.status_type;
202 END IF;
203
204 --changed from g_Unexp_ECO_rec.initiation_date := SYSDATE to the follwing bug no:2738054
205
206 g_Unexp_ECO_rec.initiation_date := nvl(g_Unexp_ECO_rec.initiation_date,SYSDATE);
207
208 /* IF g_Unexp_ECO_rec.cancellation_date IS NULL AND
209 g_ECO_rec.status_type = 5
210 THEN
211 g_Unexp_ECO_rec.cancellation_date := SYSDATE;
212 END IF;
213 */
214
215 /* IF g_unexp_eco_rec.project_id = FND_API.G_MISS_NUM
216 THEN
217 g_unexp_eco_rec.project_id := NULL;
218 END IF;
219
220 IF g_unexp_eco_rec.task_id = FND_API.G_MISS_NUM
221 THEN
222 g_unexp_eco_rec.task_id := NULL;
223 END IF;
224 */
225 /* Added by MK on 11/29/00 Bug #1508078
226 -- Defaulting hierarchy_flag and organization_hierarchy
227 --
228 IF g_ECO_rec.hierarchy_flag IS NULL OR
229 g_ECO_rec.hierarchy_flag = FND_API.G_MISS_NUM
230 THEN
231 g_ECO_rec.hierarchy_flag := 2 ; -- 2 : No
232 END IF;
233 */
234
235 IF g_ECO_rec.organization_hierarchy = FND_API.G_MISS_CHAR THEN
236 g_ECO_rec.organization_hierarchy := NULL ;
237 END IF;
238
239
240 -- Eng Change
241 IF g_unexp_eco_rec.change_mgmt_type_code = FND_API.G_MISS_CHAR
242 OR g_unexp_eco_rec.change_mgmt_type_code IS NULL
243 THEN
244 g_unexp_eco_rec.change_mgmt_type_code := Eng_Globals.G_CHANGE_ORDER ;
245 END IF;
246
247 IF g_unexp_eco_rec.assignee_id = FND_API.G_MISS_NUM
248 THEN
249 g_unexp_eco_rec.assignee_id := NULL;
250 END IF;
251
252 IF g_unexp_eco_rec.source_type_code = FND_API.G_MISS_CHAR
253 OR g_unexp_eco_rec.source_type_code IS NULL
254 THEN
255 g_unexp_eco_rec.source_type_code := NULL ;
256 END IF;
257
258 IF g_unexp_eco_rec.source_id = FND_API.G_MISS_NUM
259 THEN
260 g_unexp_eco_rec.source_id := NULL;
261 END IF;
262
263 IF g_ECO_rec.internal_use_only IS NULL OR
264 g_ECO_rec.internal_use_only = FND_API.G_MISS_NUM
265 THEN
266 g_ECO_rec.internal_use_only := 1 ; -- 1 : Yes
267 END IF;
268
269 IF g_ECO_rec.need_by_date = FND_API.G_MISS_DATE THEN
270 g_ECO_rec.need_by_date := NULL ;
271 END IF;
272
273 IF g_ECO_rec.effort = FND_API.G_MISS_NUM
274 THEN
275 g_ECO_rec.effort := NULL ;
276 END IF;
277
278 g_unexp_eco_rec.change_id := Get_Change_Id;
279
280 -- Done defaulting attributes
281
282 x_ECO_rec := g_ECO_rec;
283 x_Unexp_ECO_rec := g_Unexp_ECO_Rec;
284 x_return_status := 'S';
285
286 END Attribute_Defaulting;
287
288 -- Procedure Entity_Defaulting
289
290 PROCEDURE Entity_Defaulting
291 ( p_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
292 , p_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_unexposed_Rec_Type
293 , p_Old_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
294 , p_Old_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_unexposed_Rec_Type
295 , p_control_rec IN BOM_BO_PUB.Control_Rec_Type :=
296 BOM_BO_PUB.G_DEFAULT_CONTROL_REC
297 , x_ECO_rec IN OUT NOCOPY ENG_Eco_PUB.Eco_Rec_Type
298 , x_Unexp_ECO_rec IN OUT NOCOPY ENG_Eco_PUB.Eco_unexposed_Rec_Type
299 , x_return_status OUT NOCOPY VARCHAR2
300 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
301 )
302 IS
303 l_process_name VARCHAR2(30) := NULL;
304 l_ECO_rec ENG_Eco_PUB.Eco_Rec_Type := p_ECO_rec;
305 l_Unexp_ECO_rec ENG_Eco_PUB.Eco_unexposed_Rec_Type := p_Unexp_ECO_rec;
306 l_processed BOOLEAN := FALSE;
307 l_Token_Tbl Error_Handler.Token_Tbl_Type;
308 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
309 l_err_text VARCHAR2(2000) := NULL;
310
311
312 -- Added for bug 3591945
313 l_sql_stmt VARCHAR2(2000);
314 l_default_hierarchy_flag NUMBER;
315
316 /* Cursor to fetch the default hierarchy set for the change header type */
317 CURSOR c_change_default_hierarchy (
318 cp_change_type_id IN NUMBER,
319 cp_organization_id IN NUMBER
320 ) IS
321 SELECT pos.organization_structure_id, pos.NAME
322 FROM per_organization_structures pos
323 WHERE pos.organization_structure_id in (SELECT hierarchy_id
324 FROM ENG_TYPE_ORG_HIERARCHIES
325 WHERE change_type_id = cp_change_type_id
326 AND organization_id = cp_organization_id
327 AND default_hierarchy_flag = 'Y');
328 BEGIN
329
330 l_token_tbl(1).token_name := 'ECO_NAME';
331 l_token_tbl(1).token_value := p_ECO_Rec.ECO_Name;
332
333 x_return_status := FND_API.G_RET_STS_SUCCESS;
334
335 IF l_ECO_rec.priority_code = FND_API.G_MISS_CHAR
336 THEN
337 l_ECO_rec.priority_code := NULL;
338 END IF;
339
340 IF l_ECO_rec.approval_list_name = FND_API.G_MISS_CHAR
341 THEN
342 l_ECO_rec.approval_list_name := NULL;
343 END IF;
344
345 IF l_ECO_rec.ECO_department_name = FND_API.G_MISS_CHAR
346 THEN
347 l_ECO_rec.ECO_Department_name := NULL;
348 END IF;
349
350 IF l_Unexp_ECO_rec.status_type = FND_API.G_MISS_NUM
351 THEN
352 l_Unexp_ECO_rec.status_type := NULL;
353 END IF;
354
355 IF l_Unexp_ECO_rec.approval_status_type = FND_API.G_MISS_NUM
356 THEN
357 l_Unexp_ECO_rec.approval_status_type := NULL;
358 END IF;
359
360 IF l_ECO_rec.reason_code = FND_API.G_MISS_CHAR
361 THEN
362 l_ECO_rec.reason_code := NULL;
363 END IF;
364
365 IF l_ECO_rec.eng_implementation_cost = FND_API.G_MISS_NUM
366 THEN
367 l_ECO_rec.eng_implementation_cost := NULL;
368 END IF;
369
370 IF l_ECO_rec.mfg_implementation_cost = FND_API.G_MISS_NUM
371 THEN
372 l_ECO_rec.mfg_implementation_cost := NULL;
373 END IF;
374
375 IF l_ECO_rec.cancellation_comments = FND_API.G_MISS_CHAR
376 THEN
377 l_ECO_rec.cancellation_comments := NULL;
378 END IF;
379
380 IF l_ECO_rec.requestor = FND_API.G_MISS_CHAR
381 THEN
382 l_ECO_rec.requestor := NULL;
383 END IF;
384
385 IF l_ECO_rec.description = FND_API.G_MISS_CHAR
386 THEN
387 l_ECO_rec.description := NULL;
388 END IF;
389
390 /* Added by MK on 11/29/00 Bug #1508078
391 -- Entity Defaulting
392 -- Set null to unexposed data columns
393 --
394 */
395 IF l_Unexp_ECO_rec.initiation_date = FND_API.G_MISS_DATE
396 THEN
397 l_Unexp_ECO_rec.initiation_date := NULL ;
398 END IF ;
399
400 IF l_Unexp_ECO_rec.implementation_date = FND_API.G_MISS_DATE
401 THEN
402 l_Unexp_ECO_rec.implementation_date := NULL ;
403 END IF ;
404
405 IF l_Unexp_ECO_rec.cancellation_date = FND_API.G_MISS_DATE
406 THEN
407 l_Unexp_ECO_rec.cancellation_date := NULL ;
408 END IF ;
409
410 IF l_ECO_rec.approval_date = FND_API.G_MISS_DATE
411 THEN
412 l_ECO_rec.approval_date := NULL ;
413 END IF ;
414
415 IF l_ECO_rec.approval_request_date = FND_API.G_MISS_DATE
416 THEN
417 l_ECO_rec.approval_request_date := NULL ;
418 END IF ;
419
420 -- Eng Change
421 IF l_ECO_rec.change_management_type = FND_API.G_MISS_CHAR
422 THEN
423 l_ECO_rec.change_management_type := NULL;
424 END IF;
425
426 -- Eng Change
427 IF l_Unexp_ECO_rec.change_mgmt_type_code = FND_API.G_MISS_CHAR
428 THEN
429 l_Unexp_ECO_rec.change_mgmt_type_code := NULL;
430 END IF;
431
432 -- Eng Change
433 IF l_ECO_rec.assignee = FND_API.G_MISS_CHAR
434 THEN
435 l_ECO_rec.assignee := NULL;
436 END IF;
437
438 /* -- Eng Change
439 IF l_ECO_rec.assignee_company_name = FND_API.G_MISS_CHAR
440 THEN
441 l_ECO_rec.assignee := NULL;
442 END IF;
443 */
444 -- Eng Change
445 IF l_Unexp_ECO_rec.assignee_id = FND_API.G_MISS_NUM
446 THEN
447 l_Unexp_ECO_rec.assignee_id := NULL;
448 END IF;
449
450 IF l_ECO_rec.source_type = FND_API.G_MISS_CHAR
451 THEN
452 l_ECO_rec.source_type := NULL;
453 END IF;
454
455 IF l_Unexp_ECO_rec.source_type_code = FND_API.G_MISS_CHAR
456 THEN
457 l_Unexp_ECO_rec.source_type_code := NULL ;
458 END IF;
459
460 IF l_ECO_rec.source_name = FND_API.G_MISS_CHAR
461 THEN
462 l_ECO_rec.source_name := NULL;
463 END IF;
464
465 IF g_unexp_eco_rec.source_id = FND_API.G_MISS_NUM
466 THEN
467 g_unexp_eco_rec.source_id := NULL;
468 END IF;
469
470 IF g_ECO_rec.effort = FND_API.G_MISS_NUM
471 THEN
472 g_ECO_rec.effort := NULL ;
473 END IF;
474
475 IF l_ECO_rec.internal_use_only IS NULL OR
476 l_ECO_rec.internal_use_only = FND_API.G_MISS_NUM
477 THEN
478 l_ECO_rec.internal_use_only := 1 ; -- 1 : Yes
479 END IF;
480
481 IF l_ECO_rec.need_by_date = FND_API.G_MISS_DATE THEN
482 l_ECO_rec.need_by_date := NULL ;
483 END IF;
484
485
486 g_ECO_rec := l_ECO_rec;
487 Get_Flex_Eco
488 ( p_ECO_rec => g_ECO_rec
489 , x_ECO_rec => l_ECO_rec
490 );
491
492 -- Initialize Workflow Process
493
494 ENG_Globals.Init_Process_Name
495 ( g_Unexp_ECO_rec.change_order_type_id
496 , g_ECO_rec.priority_code
497 , g_Unexp_ECO_rec.organization_id);
498
499 /*********************************************************************
500 -- If caller is the form, then perform this defaulting upon request.
501 -- If caller is the open interface, perform it for UPDATEs
502 -- Added by AS on 10/07/99 to facilitate ECO form re-architecture.
503 **********************************************************************/
504
505 IF (p_control_rec.caller_type = 'FORM' AND
506 NVL(p_control_rec.validation_controller, FND_API.G_MISS_CHAR) =
507 'PROCESS')
508 OR
509 l_ECO_rec.transaction_type = ENG_Globals.G_OPR_UPDATE
510 THEN
511 l_processed := FALSE;
512
513 IF p_control_rec.caller_type = 'OI'
514 THEN
515 ENG_Globals.Check_Approved_For_Process
516 ( p_change_notice => l_ECO_rec.ECO_name
517 , p_organization_id => l_Unexp_ECO_rec.organization_id
518 , x_processed => l_processed
519 , x_err_text => l_err_text
520 );
521 ELSIF (p_control_rec.caller_type = 'FORM' AND
522 NVL(p_control_rec.validation_controller, FND_API.G_MISS_CHAR) =
523 'PROCESS' AND
524 l_Unexp_ECO_rec.approval_status_type not in (1,3))
525 THEN
526 l_processed := TRUE;
527 END IF;
528
529 IF l_processed
530 THEN
531 -- Issue warning if calling thru open interface. If calling
532 -- thru form, user will have been asked for confirmation already
533
534 IF p_control_rec.caller_type = 'OI' AND
535 FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
536 THEN
537 Error_Handler.Add_Error_Token
538 ( p_Message_Name => 'ENG_APPROVE_WARNING'
539 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
540 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
541 , p_Token_Tbl => l_Token_Tbl
542 , p_message_type => 'W'
543 );
544 END IF;
545
546 l_Unexp_ECO_rec.approval_status_type := 1; -- Not Submitted For Approval
547 l_ECO_rec.approval_request_date := NULL;
548 l_ECO_rec.approval_date := NULL;
549
550 -- Set all "Scheduled" revised items to "Open"
551
552 BEGIN
553 UPDATE eng_revised_items
554 SET status_type = 1,
555 last_update_date = SYSDATE,
556 last_updated_by = 'XXX',
557 last_update_login = 'XXX'
558 WHERE organization_id = p_Unexp_ECO_rec.organization_id
559 AND change_notice = p_ECO_rec.ECO_name
560 AND status_type = 4;
561 EXCEPTION
562 WHEN NO_DATA_FOUND THEN
563 NULL;
564 WHEN OTHERS THEN
565 l_err_text := G_PKG_NAME || ' : (Entity Defaulting) '
566 || substrb(SQLERRM,1,200);
567 Error_Handler.Add_Error_Token
568 ( p_Message_Text => l_err_text
569 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
570 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
571 );
572 END;
573 END IF;
574 END IF;
575
576 IF l_Unexp_ECO_rec.cancellation_date IS NULL AND
577 l_Unexp_ECO_rec.status_type = 5
578 THEN
579 l_Unexp_ECO_rec.cancellation_date := SYSDATE;
580 END IF;
581
582 l_processed := FALSE;
583
584 IF l_ECO_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE OR
585 (l_ECO_rec.priority_code <>
586 p_old_ECO_rec.priority_code OR
587 (l_ECO_rec.priority_code IS NULL AND
588 p_old_ECO_rec.priority_code IS NOT NULL) OR
589 (p_old_ECO_rec.priority_code IS NULL AND
590 l_ECO_rec.priority_code IS NOT NULL))
591 THEN
592
593 -- If process found, null out Approval List and set other approval details accordingly.
594 -- Also issue warning.
595
596 ENG_Globals.Init_Process_Name
597 ( p_change_order_type_id => l_Unexp_ECO_rec.change_order_type_id
598 , p_priority_code => l_ECO_rec.priority_code
599 , p_organization_id => l_Unexp_ECO_rec.organization_id
600 );
601
602 -- Get new Workflow Process name
603
604 l_process_name := ENG_Globals.Get_Process_Name;
605
606 IF l_process_name IS NOT NULL
607 THEN
608 l_Unexp_ECO_rec.approval_list_id := NULL;
609 l_ECO_rec.approval_request_date := NULL;
610 l_ECO_rec.approval_date := NULL;
611 l_Unexp_ECO_rec.approval_status_type := 1; -- Not Submitted for Approval
612
613 IF p_control_rec.caller_type = 'OI' AND
614 FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
615 THEN
616 Error_Handler.Add_Error_Token
617 ( p_Message_Name => 'ENG_APPROV_DETAILS_CHANGED'
618 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
619 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
620 , p_Token_Tbl => l_Token_Tbl
621 , p_message_type => 'W'
622 );
623 END IF;
624 END IF;
625 l_processed := TRUE;
626 END IF;
627
628 IF l_ECO_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE OR
629 (l_Unexp_ECO_rec.change_order_type_id <>
630 p_old_Unexp_ECO_rec.change_order_type_id OR
631 (l_Unexp_ECO_rec.change_order_type_id IS NULL AND
632 p_old_Unexp_ECO_rec.change_order_type_id IS NOT NULL))
633 THEN
634 IF l_processed
635 THEN
636 NULL;
637 ELSE
638
639 -- Get new Workflow Process name
640
641 ENG_Globals.Init_Process_Name
642 ( p_change_order_type_id => l_Unexp_ECO_rec.change_order_type_id
643 , p_priority_code => l_ECO_rec.priority_code
644 , p_organization_id => l_Unexp_ECO_rec.organization_id
645 );
646
647 l_process_name := ENG_Globals.Get_Process_Name;
648
649 -- If process found, null out Approval List and set other approval details accordingly.
650 -- Also issue warning.
651
652 IF l_process_name IS NOT NULL
653 THEN
654 l_Unexp_ECO_rec.approval_list_id := NULL;
655 l_ECO_rec.approval_request_date := NULL;
656 l_ECO_rec.approval_date := NULL;
657 l_Unexp_ECO_rec.approval_status_type := 1; -- Not Submitted for Approval
658
659 IF p_control_rec.caller_type = 'OI' AND
660 FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
661 THEN
662 Error_Handler.Add_Error_Token
663 ( p_Message_Name => 'ENG_APPROV_DETAILS_CHANGED'
664 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
665 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
666 , p_Token_Tbl => l_Token_Tbl
667 , p_message_type => 'W'
668 );
669 END IF;
670 END IF;
671 END IF;
672 END IF;
673
674 IF l_ECO_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE OR
675 (l_Unexp_ECO_rec.approval_list_id <>
676 p_old_Unexp_ECO_rec.approval_list_id OR
677 (l_Unexp_ECO_rec.approval_list_id IS NULL AND
678 p_old_Unexp_ECO_rec.approval_list_id IS NOT NULL) OR
679 (p_old_Unexp_ECO_rec.approval_list_id IS NULL AND
680 l_Unexp_ECO_rec.approval_list_id IS NOT NULL))
681 THEN
682
683 -- No approval list or workflow process
684
685 IF l_Unexp_ECO_rec.approval_status_type IS NULL AND
686 l_process_name IS NULL AND
687 l_Unexp_ECO_rec.approval_list_id IS NULL
688 THEN
689 -- Changed to Not Submitted For Approval ,as a part of Approval Status Changes
690 /* Fix for bug 6413814 - Approval Status should be
691 - 'Not Submitted For Approval' for PLM COs
692 - 'Approved' for ERP ECOs
693 Added an If-Else condition to check for plm_or_erp_change column.
694 */
695 If nvl(l_ECO_rec.plm_or_erp_change,'PLM') = 'PLM' Then
696 l_Unexp_ECO_rec.approval_status_type := 1;
697 l_ECO_rec.approval_date := NULL;
698 Else /* for ERP Ecos*/
699 l_Unexp_ECO_rec.approval_status_type := 5; --Bug 5904664
700 l_ECO_rec.approval_date := SYSDATE; --Bug 5904664
701 END IF;
702 END IF;
703
704
705 -- Approval list or (workflow process and not(Approval Requested))
706
707 IF l_Unexp_ECO_rec.approval_status_type IS NULL AND
708 ((l_process_name IS NOT NULL AND
709 NVL(p_old_Unexp_ECO_rec.approval_status_type, 0) <> 3)
710 OR
711 l_Unexp_ECO_rec.approval_list_id IS NOT NULL)
712 THEN
713 l_Unexp_ECO_rec.approval_status_type := 1;
714 l_ECO_rec.approval_date := NULL;
715 l_ECO_rec.approval_request_date := NULL;
716 END IF;
717
718 END IF;
719
720 IF l_ECO_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE OR
721 (l_Unexp_ECO_rec.approval_status_type <>
722 p_old_Unexp_ECO_rec.approval_status_type OR
723 (l_Unexp_ECO_rec.approval_status_type IS NULL AND
724 p_old_Unexp_ECO_rec.approval_status_type IS NOT NULL) OR
725 (p_old_Unexp_ECO_rec.approval_status_type IS NULL AND
726 l_Unexp_ECO_rec.approval_status_type IS NOT NULL))
727 THEN
728
729 -- No Workflow Process or approval list id
730
731 IF l_process_name IS NULL AND
732 l_Unexp_ECO_rec.approval_list_id IS NULL AND
733 l_Unexp_ECO_rec.approval_status_type IS NULL
734 THEN
735 -- Changed to Not Submitted For Approval ,as a part of Approval Status Changes
736 /* Fix for bug 6413814 - Approval Status should be
737 - 'Not Submitted For Approval' for PLM COs
738 - 'Approved' for ERP ECOs
739 Added an If-Else condition to check for plm_or_erp_change column.
740 */
741
742 If nvl(l_ECO_rec.plm_or_erp_change,'PLM') = 'PLM' Then
743 l_Unexp_ECO_rec.approval_status_type := 1;
744 l_ECO_rec.approval_date := NULL;
745 Else /* for ERP Ecos*/
746 l_Unexp_ECO_rec.approval_status_type := 5; --Bug 5904664
747 l_ECO_rec.approval_date := SYSDATE; --Bug 5904664
748 END IF;
749 END IF;
750
751 -- Approval requested
752
753 IF l_Unexp_ECO_rec.approval_status_type = 3
754 THEN
755 l_ECO_rec.approval_request_date := SYSDATE;
756 l_ECO_rec.approval_date := NULL;
757 END IF;
758
759 -- Approved
760
761 IF l_Unexp_ECO_rec.approval_status_type = 5
762 THEN
763 l_ECO_rec.approval_date := SYSDATE;
764 ELSE
765 l_ECO_rec.approval_date := NULL;
766 END IF;
767
768 -- Not Submitted for Approval or Ready to Approve
769
770 IF l_Unexp_ECO_rec.approval_status_type IN (1,2)
771 THEN
772 l_ECO_rec.approval_request_date := NULL;
773 END IF;
774
775 END IF;
776
777
778 /* Added by MK on 11/29/00 Bug #1508078
779 -- Entity Defaulting hierarchy_flag and organization_hierarchy
780 --
781 IF l_ECO_rec.hierarchy_flag IS NULL OR
782 l_ECO_rec.hierarchy_flag = FND_API.G_MISS_NUM
783 THEN
784 l_ECO_rec.hierarchy_flag := 2 ; -- 2 : No
785 END IF ;
786
787 IF l_ECO_rec.hierarchy_flag <> 1
788 THEN
789 l_ECO_rec.organization_hierarchy := NULL ;
790 END IF ;
791 */
792
793 IF l_ECO_rec.organization_hierarchy = FND_API.G_MISS_CHAR
794 THEN
795 l_ECO_rec.organization_hierarchy := NULL ;
796 END IF ;
797
798 -- Changes for bug 3591945
799 -- Set the default hierarchy defined at the change type, if any.
800
801 IF l_ECO_rec.organization_hierarchy IS NULL
802 AND l_ECO_rec.plm_or_erp_change = 'PLM'
803 AND l_ECO_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE
804 THEN
805 l_sql_stmt := 'SELECT 1
806 FROM AK_CUSTOM_REGION_ITEMS AK_ITEMS, ENG_ATTRIBUTES_SECTIONS_TL ENG_ATTRIBUTES,
807 ENG_ATTRIBUTES_SECTIONS_B ENG_ATTRIBUTES_B, EGO_CUSTOMIZATION_EXT ATTRIBUTE_EXT
808 WHERE
809 AK_ITEMS.ATTRIBUTE_CODE = ENG_ATTRIBUTES.ATTRIBUTE_SECTION_CODE
810 AND ENG_ATTRIBUTES_B.ATTRIBUTE_SECTION_CODE = ENG_ATTRIBUTES.ATTRIBUTE_SECTION_CODE
811 AND ENG_ATTRIBUTES_B.ATTRIBUTE_SECTION_FLAG= ''A''
812 AND AK_ITEMS.PROPERTY_NAME= ''DISPLAY_SEQUENCE''
813 AND AK_ITEMS.CUSTOMIZATION_CODE=ATTRIBUTE_EXT.CUSTOMIZATION_CODE
814 AND ATTRIBUTE_EXT.CLASSIFICATION1 =:1
815 AND ATTRIBUTE_EXT.CLASSIFICATION2 = :2
816 AND ATTRIBUTE_EXT.REGION_CODE = ''ENG_ADMIN_CONFIGURATIONS''
817 AND ATTRIBUTE_EXT.REGION_APPLICATION_ID = ''703'' AND ENG_ATTRIBUTES.language = USERENV(''lang'')
818 AND AK_ITEMS.ATTRIBUTE_CODE = ''ORGANIZATION_HIERARCHY'' ';
819 BEGIN
820 l_default_hierarchy_flag := 2;
821 EXECUTE IMMEDIATE l_sql_stmt INTO l_default_hierarchy_flag USING l_Unexp_ECO_rec.Change_Mgmt_Type_Code, l_Unexp_ECO_rec.Change_Order_Type_Id;
822 EXCEPTION
823 WHEN OTHERS THEN
824 l_default_hierarchy_flag := 2;
825 END;
826 IF (l_default_hierarchy_flag = 1)
827 THEN
828 OPEN c_change_default_hierarchy(l_Unexp_ECO_rec.Change_Order_Type_Id, l_Unexp_ECO_rec.Organization_Id);
829 FETCH c_change_default_hierarchy INTO l_Unexp_ECO_rec.Hierarchy_Id, l_ECO_rec.organization_hierarchy;
830 CLOSE c_change_default_hierarchy;
831 END IF;
832 END IF;
833 -- End changes for bug 3591945
834 -- Load out record
835
836 x_ECO_rec := l_ECO_rec;
837 x_Unexp_ECO_rec := l_Unexp_ECO_rec;
838 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
839 END Entity_Defaulting;
840
841 -- Procedure Populate_NULL_Columns
842
843 PROCEDURE Populate_NULL_Columns
844 ( p_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
845 , p_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_unexposed_Rec_Type
846 , p_Old_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
847 , p_Old_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_unexposed_Rec_Type
848 , x_ECO_rec IN OUT NOCOPY ENG_Eco_PUB.Eco_Rec_Type
849 , x_Unexp_ECO_rec IN OUT NOCOPY ENG_Eco_PUB.Eco_unexposed_Rec_Type
850 )
851 IS
852 l_ECO_rec ENG_Eco_PUB.Eco_Rec_Type := p_ECO_rec;
853 l_Unexp_ECO_rec ENG_Eco_PUB.Eco_unexposed_Rec_Type := p_Unexp_ECO_rec;
854 BEGIN
855
856 l_Unexp_ECO_rec.initiation_date := p_old_Unexp_ECO_rec.initiation_date;
857 l_Unexp_ECO_rec.implementation_date := p_old_Unexp_ECO_rec.implementation_date;
858 l_Unexp_ECO_rec.cancellation_date := p_old_Unexp_ECO_rec.cancellation_date;
859 l_ECO_rec.approval_date := p_old_ECO_rec.approval_date;
860 l_ECO_rec.approval_request_date := p_old_ECO_rec.approval_request_date;
861
862 IF l_Unexp_ECO_rec.requestor_id IS NULL
863 THEN
864 l_Unexp_ECO_rec.requestor_id := p_Old_Unexp_ECO_rec.requestor_id;
865 END IF;
866
867 IF l_Unexp_ECO_rec.responsible_org_id IS NULL
868 THEN
869 l_Unexp_ECO_rec.responsible_org_id := p_Old_Unexp_ECO_rec.responsible_org_id;
870 END IF;
871
872 IF l_Unexp_ECO_rec.approval_list_id IS NULL
873 THEN
874 l_Unexp_ECO_rec.approval_list_id := p_Old_Unexp_ECO_rec.approval_list_id;
875 END IF;
876
877 IF l_Unexp_ECO_rec.change_order_type_id IS NULL
878 THEN
879 l_Unexp_ECO_rec.change_order_type_id := p_Old_Unexp_ECO_rec.change_order_type_id;
880 END IF;
881
882 IF l_ECO_rec.attribute7 IS NULL THEN
883 l_ECO_rec.attribute7 := p_old_ECO_rec.attribute7;
884 END IF;
885
886 IF l_ECO_rec.attribute8 IS NULL THEN
887 l_ECO_rec.attribute8 := p_old_ECO_rec.attribute8;
888 END IF;
889
890 IF l_ECO_rec.attribute9 IS NULL THEN
891 l_ECO_rec.attribute9 := p_old_ECO_rec.attribute9;
892 END IF;
893
894 IF l_ECO_rec.attribute10 IS NULL THEN
895 l_ECO_rec.attribute10 := p_old_ECO_rec.attribute10;
896 END IF;
897
898 IF l_ECO_rec.attribute11 IS NULL THEN
899 l_ECO_rec.attribute11 := p_old_ECO_rec.attribute11;
900 END IF;
901
902 IF l_ECO_rec.attribute12 IS NULL THEN
903 l_ECO_rec.attribute12 := p_old_ECO_rec.attribute12;
904 END IF;
905
906 IF l_ECO_rec.attribute13 IS NULL THEN
907 l_ECO_rec.attribute13 := p_old_ECO_rec.attribute13;
908 END IF;
909
910 IF l_ECO_rec.attribute14 IS NULL THEN
911 l_ECO_rec.attribute14 := p_old_ECO_rec.attribute14;
912 END IF;
913
914 IF l_ECO_rec.attribute15 IS NULL THEN
915 l_ECO_rec.attribute15 := p_old_ECO_rec.attribute15;
916 END IF;
917
918 IF l_Unexp_ECO_rec.approval_status_type IS NULL THEN
919 l_Unexp_ECO_rec.approval_status_type := p_old_Unexp_ECO_rec.approval_status_type;
920 END IF;
921
922 IF l_ECO_rec.description IS NULL THEN
923 l_ECO_rec.description := p_old_ECO_rec.description;
924 END IF;
925
926 --Bug 6378121, Add status_code in the entity defaulting
927 IF l_Unexp_ECO_rec.status_code IS NULL THEN
928 l_Unexp_ECO_rec.status_code := p_old_Unexp_ECO_rec.status_code;
929 END IF;
930
931 IF l_Unexp_ECO_rec.status_type IS NULL THEN
932 l_Unexp_ECO_rec.status_type := p_old_Unexp_ECO_rec.status_type;
933 END IF;
934
935 IF l_ECO_rec.ECO_Department_Name IS NULL THEN
936 l_ECO_rec.ECO_Department_Name := p_old_ECO_rec.ECO_Department_Name;
937 END IF;
938
939 IF l_ECO_rec.Approval_List_Name IS NULL THEN
940 l_ECO_rec.Approval_List_Name := p_old_ECO_rec.Approval_List_Name;
941 END IF;
942
943 IF l_ECO_rec.Requestor IS NULL THEN
944 l_ECO_rec.Requestor := p_old_ECO_rec.Requestor;
945 END IF;
946
947 IF l_ECO_rec.cancellation_comments IS NULL THEN
948 l_ECO_rec.cancellation_comments := p_old_ECO_rec.cancellation_comments;
949 END IF;
950
951 IF l_ECO_rec.priority_code IS NULL THEN
952 l_ECO_rec.priority_code := p_old_ECO_rec.priority_code;
953 END IF;
954
955 IF l_ECO_rec.reason_code IS NULL THEN
956 l_ECO_rec.reason_code := p_old_ECO_rec.reason_code;
957 END IF;
958
959 IF l_ECO_rec.ENG_implementation_cost IS NULL THEN
960 l_ECO_rec.ENG_implementation_cost := p_old_ECO_rec.ENG_implementation_cost;
961 END IF;
962
963 IF l_ECO_rec.MFG_implementation_cost IS NULL THEN
964 l_ECO_rec.MFG_implementation_cost := p_old_ECO_rec.MFG_implementation_cost;
965 END IF;
966
967 IF l_ECO_rec.attribute_category IS NULL THEN
968 l_ECO_rec.attribute_category := p_old_ECO_rec.attribute_category;
969 END IF;
970
971 IF l_ECO_rec.attribute1 IS NULL THEN
972 l_ECO_rec.attribute1 := p_old_ECO_rec.attribute1;
973 END IF;
974
975 IF l_ECO_rec.attribute2 IS NULL THEN
976 l_ECO_rec.attribute2 := p_old_ECO_rec.attribute2;
977 END IF;
978
979 IF l_ECO_rec.attribute3 IS NULL THEN
980 l_ECO_rec.attribute3 := p_old_ECO_rec.attribute3;
981 END IF;
982
983 IF l_ECO_rec.attribute4 IS NULL THEN
984 l_ECO_rec.attribute4 := p_old_ECO_rec.attribute4;
985 END IF;
986
987 IF l_ECO_rec.attribute5 IS NULL THEN
988 l_ECO_rec.attribute5 := p_old_ECO_rec.attribute5;
989 END IF;
990
991 IF l_ECO_rec.attribute6 IS NULL THEN
992 l_ECO_rec.attribute6 := p_old_ECO_rec.attribute6;
993 END IF;
994
995
996 /* Added by MK on 11/29/00 Bug #1508078
997 -- Modified populating hierarchy_flag and organization_hierarchy
998 -- when these are miss values
999
1000 IF l_ECO_rec.hierarchy_flag IS NULL
1001 OR l_ECO_rec.hierarchy_flag = FND_API.G_MISS_NUM
1002 THEN
1003 l_ECO_rec.hierarchy_flag := p_old_ECO_rec.hierarchy_flag;
1004 END IF;
1005 */
1006
1007 IF l_ECO_rec.organization_hierarchy IS NULL
1008 OR l_ECO_rec.organization_hierarchy = FND_API.G_MISS_CHAR
1009 THEN
1010 l_ECO_rec.organization_hierarchy := p_old_ECO_rec.organization_hierarchy;
1011 END IF;
1012
1013
1014 -- Eng Change
1015 IF l_Unexp_ECO_rec.change_mgmt_type_code IS NULL
1016 OR l_Unexp_ECO_rec.change_mgmt_type_code = FND_API.G_MISS_CHAR
1017 THEN
1018 l_Unexp_ECO_rec.change_mgmt_type_code := p_Old_Unexp_ECO_rec.change_mgmt_type_code ;
1019 END IF;
1020
1021 -- Eng Change
1022 -- User should be able to null out assignee
1023 IF l_Unexp_ECO_rec.assignee_id IS NULL
1024 OR ( l_Unexp_ECO_rec.assignee_id = FND_API.G_MISS_NUM
1025 AND p_Old_Unexp_ECO_rec.assignee_id IS NULL )
1026 THEN
1027 l_Unexp_ECO_rec.assignee_id := p_Old_Unexp_ECO_rec.assignee_id ;
1028 END IF;
1029
1030
1031 x_ECO_rec := l_ECO_rec;
1032 x_Unexp_ECO_rec := l_Unexp_ECO_rec;
1033
1034 END Populate_NULL_Columns;
1035
1036 END ENG_Default_Eco;
[Home] [Help]