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