[Home] [Help]
PACKAGE BODY: APPS.ENG_ECO_UTIL
Source
1 PACKAGE BODY ENG_Eco_Util AS
2 /* $Header: ENGUECOB.pls 120.17.12020000.3 2012/10/16 03:25:38 ecchang ship $ */
3
4 -- Global variables and constants
5 -- ---------------------------------------------------------------------------
6 G_PKG_NAME VARCHAR2(30) := 'ENG_ECO_Util';
7 G_CONTROL_REC BOM_BO_PUB.Control_Rec_Type;
8
9 -- Global cursors
10 -- ---------------------------------------------------------------------------
11
12 -- For Debug
13 g_debug_file UTL_FILE.FILE_TYPE ;
14 g_debug_flag BOOLEAN := FALSE ; -- For TEST : FALSE ;
15 g_output_dir VARCHAR2(80) := NULL ;
16 g_debug_filename VARCHAR2(30) := 'eng.chgmt.eco.log' ;
17 g_debug_errmesg VARCHAR2(240);
18
19 -- BUG 3424007: Type for defaultung lifecycle phases for ERP ECOs
20 TYPE phase_list_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
21
22 PROCEDURE Org_Hierarchy_List
23 ( p_org_hierarch_name IN VARCHAR2,
24 p_org_hier_lvl_id IN NUMBER,
25 x_org_cod_list OUT NOCOPY ego_number_tbl_type)
26 IS
27
28 l_index BINARY_INTEGER;
29 i NUMBER ;
30 -- list to store the index organization list
31 -- where organization_id is the index of the table
32 eng_orgid_index_list INV_ORGHIERARCHY_PVT.OrgID_tbl_type;
33
34
35 BEGIN
36
37 inv_orghierarchy_pvt.Org_Hierarchy_List
38 ( p_org_hierarchy_name => p_org_hierarch_name,
39 p_org_hier_level_id => p_org_hier_lvl_id,
40 x_org_code_list => eng_orgid_index_list );
41
42 l_index := eng_orgid_index_list.FIRST;
43 i := 1;
44 x_org_cod_list := EGO_NUMBER_TBL_TYPE();
45 WHILE (l_index <= eng_orgid_index_list.LAST) LOOP
46 x_org_cod_list.EXTEND();
47 x_org_cod_list(i) := eng_orgid_index_list(l_index) ;
48 l_index := eng_orgid_index_list.NEXT(l_index);
49 i := i+1;
50 END LOOP;
51
52 END;
53
54
55 /********************************************************************
56 * API Type : Local APIs
57 * Purpose : Those APIs are private
58 *********************************************************************/
59
60 /** R12C Changes
61 * ENG Change order Proc implementation
62 * */
63 PROCEDURE Execute_ProcCP
64 (
65 p_api_version IN NUMBER := 1.0 --
66 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
67 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
68 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
69 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
70 ,p_output_dir IN VARCHAR2
71 ,p_debug_filename IN VARCHAR2
72 ,x_return_status OUT NOCOPY VARCHAR2 --
73 ,x_msg_count OUT NOCOPY NUMBER --
74 ,x_msg_data OUT NOCOPY VARCHAR2 --
75 ,p_change_id IN NUMBER --
76 ,p_change_notice IN VARCHAR2 --
77 ,p_rev_item_seq_id IN NUMBER := NULL
78 ,p_org_id IN NUMBER --
79 ,p_all_org_flag IN VARCHAR2
80 ,p_hierarchy_name IN VARCHAR2
81 ,x_request_id OUT NOCOPY NUMBER --
82 )
83 IS
84 l_api_name CONSTANT VARCHAR2(30) := 'Execute_ProcCP';
85 l_api_version CONSTANT NUMBER := 1.0;
86 l_return_status VARCHAR2(1);
87 l_dummy_counter NUMBER := 0;
88 --bug 11937314
89 l_resp_id number := null;
90 l_set_dg boolean;
91 -- Status Lookups
92 --CANCELLED CONSTANT NUMBER := 5;
93 --IMPLEMENTED CONSTANT NUMBER := 6;
94
95 X_Model NUMBER := 1;
96 X_OptionClass NUMBER := 2;
97 X_Planning NUMBER := 3;
98 X_Standard NUMBER := 4;
99 -- Added for bug 8732198
100 x_phase varchar2(80) := 'Pending';
101 x_dev_phase varchar2(15) := 'Pending';
102 x_status varchar2(80) := 'Pending';
103 x_dev_status varchar2(15) := 'Pending';
104 x_message varchar2(240) := 'Pending';
105 Call_Status boolean := FALSE;
106
107 -- Added for bug 9243978
108 l_status_type NUMBER := 0;
109
110 BEGIN
111 -- Standard Start of API savepoint
112 SAVEPOINT Implement_ECO_PUB;
113
114 -- Standard call to check for call compatibility
115 IF NOT FND_API.Compatible_API_Call ( l_api_version
116 ,p_api_version
117 ,l_api_name
118 ,G_PKG_NAME )
119 THEN
120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
121 END IF;
122
123 -- Initialize message list if p_init_msg_list is set to TRUE.
124 IF FND_API.to_Boolean( p_init_msg_list ) THEN
125 FND_MSG_PUB.initialize;
126 END IF ;
127
128 -- For Test/Debug
129 IF FND_API.to_Boolean( p_debug ) THEN
130 ENG_CHANGE_ACTIONS_UTIL.Open_Debug_Session
131 ( p_file_name => p_debug_filename
132 , p_output_dir => p_output_dir
133 );
134 END IF ;
135
136 -- Write debug message if debug mode is on
137 --IF FND_API.to_Boolean( p_debug ) THEN
138 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('ENG_Eco_Util.Execute_ProcCP log');
139 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('-----------------------------------------------------');
140 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_api_version ' || p_api_version);
141 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_init_msg_list '|| p_init_msg_list );
142 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_commit '|| p_commit);
143 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_validation_level '|| p_validation_level);
144 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' p_debug '|| p_debug);
145 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_change_id : ' || to_char(p_change_id) );
146 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_change_notice : ' || p_change_notice );
147 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_org_id : ' || to_char(p_org_id) );
148 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_all_org_flag : ' || p_all_org_flag );
149 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_hierarchy_name : ' || p_hierarchy_name );
150 IF (p_rev_item_seq_id IS NOT NULL) THEN
151 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_rev_item_seq_id : ' || to_char(p_rev_item_seq_id) );
152 ELSE
153 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_rev_item_seq_id : NULL' );
154 END IF;
155 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('-----------------------------------------------------');
156 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Initializing return status... ' );
157 --END IF ;
158
159 -- Initialize API return status to success
160 x_return_status := FND_API.G_RET_STS_SUCCESS;
161
162 -- Real pl/sql code starts here
163
164
165 -- If there is no open revised items, skip the concurrent request call
166 -- and return an error message
167
168 l_dummy_counter := 1;
169
170 IF l_dummy_counter <> 0 THEN
171 --bug 11937134
172 l_resp_id := FND_GLOBAL.resp_id;
173
174 if (l_resp_id is null or l_resp_id = -1) then
175 l_set_dg := fnd_request.set_options(
176 datagroup=> 'Standard');
177 end if;
178 -- submitting the concurrent request
179 x_request_id := Fnd_Request.Submit_Request (
180 application => 'ENG',
181 program => 'ENCACNC',
182 description => null,
183 -- start_time => sysdate, -- R12 comment out to use sysadte with timestamp
184 sub_request => false,
185 argument1 => to_char(p_org_id),
186 argument2 => p_all_org_flag,
187 argument3 => p_hierarchy_name,
188 argument4 => p_change_notice,
189 argument5 => p_rev_item_seq_id,
190 argument6 => null,
191 argument7 => CHR(0),
192 argument8 => null,
193 argument9 => null,
194 argument10 => null,
195 argument11 => null,
196 argument12 => null,
197 argument13 => null,
198 argument14 => null,
199 argument15 => null,
200 argument16 => null,
201 argument17 => null,
202 argument18 => null,
203 argument19 => null,
204 argument20 => null,
205 argument21 => null,
206 argument22 => null,
207 argument23 => null,
208 argument24 => null,
209 argument25 => null,
210 argument26 => null,
211 argument27 => null,
212 argument28 => null,
213 argument29 => null,
214 argument30 => null,
215 argument31 => null,
216 argument32 => null,
217 argument33 => null,
218 argument34 => null,
219 argument35 => null,
220 argument36 => null,
221 argument37 => null,
222 argument38 => null,
223 argument39 => null,
224 argument40 => null,
225 argument41 => null,
226 argument42 => null,
227 argument43 => null,
228 argument44 => null,
229 argument45 => null,
230 argument46 => null,
231 argument47 => null,
232 argument48 => null,
233 argument49 => null,
234 argument50 => null,
235 argument51 => null,
236 argument52 => null,
237 argument53 => null,
238 argument54 => null,
239 argument55 => null,
240 argument56 => null,
241 argument57 => null,
242 argument58 => null,
243 argument59 => null,
244 argument60 => null,
245 argument61 => null,
246 argument62 => null,
247 argument63 => null,
248 argument64 => null,
249 argument65 => null,
250 argument66 => null,
251 argument67 => null,
252 argument68 => null,
253 argument69 => null,
254 argument70 => null,
255 argument71 => null,
256 argument72 => null,
257 argument73 => null,
258 argument74 => null,
259 argument75 => null,
260 argument76 => null,
261 argument77 => null,
262 argument78 => null,
263 argument79 => null,
264 argument80 => null,
265 argument81 => null,
266 argument82 => null,
267 argument83 => null,
268 argument84 => null,
269 argument85 => null,
270 argument86 => null,
271 argument87 => null,
272 argument88 => null,
273 argument89 => null,
274 argument90 => null,
275 argument91 => null,
276 argument92 => null,
277 argument93 => null,
278 argument94 => null,
279 argument95 => null,
280 argument96 => null,
281 argument97 => null,
282 argument98 => null,
283 argument99 => null,
284 argument100 => null
285 );
286
287 IF (x_request_id <> 0) then -- Added for bug 8732198
288 COMMIT;
289 call_status :=
290 fnd_concurrent.wait_for_request(request_id => x_request_id,
291 interval => 10,
292 max_wait => 0,
293 phase => x_phase,
294 status => x_status,
295 dev_phase => x_dev_phase,
296 dev_status => x_dev_status,
297 message => x_message);
298
299 -- Code added for bug 9243978 Starts here
300 IF (x_status IS NOT NULL AND (x_status = 'Cancelled' OR x_status = 'Terminated' OR x_status = 'Error')) THEN
301 BEGIN
302 IF (p_change_id IS NULL) THEN
303 SELECT STATUS_TYPE INTO l_status_type FROM ENG_ENGINEERING_CHANGES WHERE CHANGE_NOTICE = p_change_notice AND ORGANIZATION_ID = p_org_id;
304 ELSE
305 SELECT STATUS_TYPE INTO l_status_type FROM ENG_ENGINEERING_CHANGES WHERE CHANGE_ID = p_change_id;
306 END IF;
307 EXCEPTION
308 WHEN OTHERS THEN
309 NULL;
310 END;
311
312 IF (l_status_type = ENG_CHANGE_LIFECYCLE_UTIL.G_ENG_IMP_IN_PROGRESS) THEN
313 IF (p_change_id IS NULL) THEN
314 ENGPKIMP.LOG_IMPLEMENT_FAILURE(p_change_notice, p_org_id, null);
315 ELSE
316 ENGPKIMP.LOG_IMPLEMENT_FAILURE(p_change_id, null);
317 END IF;
318 END IF;
319 END IF;
320 -- Code added for bug 9243978 Ends here
321 END IF;
322
323
324 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('After: calling Fnd_Request.Submit_Request' );
325 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' x_request_id = ' || to_char(x_request_id) );
326
327 IF (x_request_id = 0) THEN
328 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CONCURRENT_PRGM');
329 FND_MESSAGE.Set_Token('OBJECT_NAME', 'EN'||'G.ENCACNC(Implement ECO)');
330 -- concatenating to work around GSCC validation error without changing esisting behaviour
331 FND_MSG_PUB.Add;
332 RAISE FND_API.G_EXC_ERROR;
333 ELSE
334 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('setting x_request_id' );
335 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' x_request_id = ' || to_char(x_request_id) );
336 IF (p_rev_item_seq_id IS NOT NULL ) THEN
337 UPDATE eng_revised_items
338 SET implementation_req_id = x_request_id
339 WHERE revised_item_sequence_id = p_rev_item_seq_id;
340 ELSE
341 UPDATE eng_engineering_changes
342 SET implementation_req_id = x_request_id
343 WHERE change_notice = p_change_notice
344 AND organization_id = p_org_id;
345 END IF ;
346 x_return_status := FND_API.G_RET_STS_SUCCESS;
347 END IF;
348
349
350 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Successful: calling Fnd_Request.Submit_Request' );
351
352
353 ELSE
354 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('No implementable revised item found ... ' );
355 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Fnd_Request.Submit_Request not called ... ' );
356
357 FND_MESSAGE.Set_Name('ENG', 'ENG_CANT_IMPL_WO_REV_ITEMS');
358 FND_MSG_PUB.Add;
359 x_return_status := FND_API.G_RET_STS_ERROR ;
360 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Done - adding error message ... ' );
361
362 END IF ;
363
364
365 -- Standard ending code ------------------------------------------------
366 -- IF FND_API.To_Boolean ( p_commit ) THEN
367 --Always commit to save request id.
368 COMMIT ;
369 -- END IF;
370
371 FND_MSG_PUB.Count_And_Get
372 ( p_count => x_msg_count,
373 p_data => x_msg_data );
374
375 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Finish. Eng Of Proc') ;
376 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
377
378
379 EXCEPTION
380 WHEN FND_API.G_EXC_ERROR THEN
381 ROLLBACK TO Implement_ECO_PUB;
382 x_return_status := FND_API.G_RET_STS_ERROR;
383 FND_MSG_PUB.Count_And_Get
384 ( p_count => x_msg_count
385 ,p_data => x_msg_data );
386 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with expected error.') ;
387 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
388 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
389 ROLLBACK TO Implement_ECO_PUB;
390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
391 FND_MSG_PUB.Count_And_Get
392 ( p_count => x_msg_count
393 ,p_data => x_msg_data );
394 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with unexpected error.') ;
395 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
396 WHEN OTHERS THEN
397 ROLLBACK TO Implement_ECO_PUB;
398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
399 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
400 THEN
401 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
402 END IF;
403 FND_MSG_PUB.Count_And_Get
404 ( p_count => x_msg_count
405 ,p_data => x_msg_data );
406 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with other errors.') ;
407 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
408
409 END Execute_ProcCP;
410
411 /**
412 * ENG Change ECO Action
413 * @author HaiXin Tie
414 */
415
416 /** R12C Changes
417 * ENG Change order Rule invocation implementation.
418 * For R12C we have changed this so that for PLM/ERP Change order
419 * Implementation first rule CP will get fire if there exist any attribute changes
420 * Corresponding to it then Rule validation/assignment will happen.
421 * after successfull execution of rule Proc CP will get fire.
422 * ENG Change ECO Action.Just executable has been changed all other things are same.
423 * @author HaiXin Tie
424 */
425 PROCEDURE Implement_ECO
426 (
427 p_api_version IN NUMBER --
428 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
429 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
430 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
431 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
432 ,p_output_dir IN VARCHAR2
433 ,p_debug_filename IN VARCHAR2
434 ,x_return_status OUT NOCOPY VARCHAR2 --
435 ,x_msg_count OUT NOCOPY NUMBER --
436 ,x_msg_data OUT NOCOPY VARCHAR2 --
437 ,p_change_id IN NUMBER --
438 ,p_change_notice IN VARCHAR2 --
439 ,p_rev_item_seq_id IN NUMBER := NULL
440 ,p_org_id IN NUMBER --
441 ,x_request_id OUT NOCOPY NUMBER --
442 )
443 IS
444 l_api_name CONSTANT VARCHAR2(30) := 'Implement_ECO';
445 l_api_version CONSTANT NUMBER := 1.0;
446 l_return_status VARCHAR2(1);
447 l_dummy_counter NUMBER := 0;
448 --bug 11937314
449 l_resp_id number := null;
450 l_set_dg boolean;
451 -- Status Lookups
452 --CANCELLED CONSTANT NUMBER := 5;
453 --IMPLEMENTED CONSTANT NUMBER := 6;
454
455 X_Model NUMBER := 1;
456 X_OptionClass NUMBER := 2;
457 X_Planning NUMBER := 3;
458 X_Standard NUMBER := 4;
459
460 BEGIN
461
462 -- Standard Start of API savepoint
463 SAVEPOINT Implement_ECO_PUB;
464
465 -- Standard call to check for call compatibility
466 IF NOT FND_API.Compatible_API_Call ( l_api_version
467 ,p_api_version
468 ,l_api_name
469 ,G_PKG_NAME )
470 THEN
471 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
472 END IF;
473
474 -- Initialize message list if p_init_msg_list is set to TRUE.
475 IF FND_API.to_Boolean( p_init_msg_list ) THEN
476 FND_MSG_PUB.initialize;
477 END IF ;
478
479 -- For Test/Debug
480 IF FND_API.to_Boolean( p_debug ) THEN
481 ENG_CHANGE_ACTIONS_UTIL.Open_Debug_Session
482 ( p_file_name => p_debug_filename
483 , p_output_dir => p_output_dir
484 );
485 END IF ;
486
487 -- Write debug message if debug mode is on
488 IF FND_API.to_Boolean( p_debug ) THEN
489 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('ENG_Eco_Util.Implement_ECO log');
490 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('-----------------------------------------------------');
491 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_change_id : ' || to_char(p_change_id) );
492 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_change_notice : ' || p_change_notice );
493 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_org_id : ' || to_char(p_org_id) );
494 IF (p_rev_item_seq_id IS NOT NULL) THEN
495 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_rev_item_seq_id : ' || to_char(p_rev_item_seq_id) );
496 ELSE
497 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_rev_item_seq_id : NULL' );
498 END IF;
499 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('-----------------------------------------------------');
500 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Initializing return status... ' );
501 END IF ;
502
503 -- Initialize API return status to success
504 x_return_status := FND_API.G_RET_STS_SUCCESS;
505
506 -- Real pl/sql code starts here
507
508
509 -- If there is no open revised items, skip the concurrent request call
510 -- and return an error message
511 /*
512 Select count(*)
513 Into l_dummy_counter
514 From Eng_Revised_Items eri
515 Where eri.change_id = p_change_id
516 And eri.status_type not in ( 5, -- CANCELLED
517 6, -- IMPLEMENTED
518 9, -- IMPLEMENTATION_IN_PROGRESS
519 2 -- HOLD
520 )
521 And exists (
522 Select null
523 From mtl_system_items msi
524 Where msi.inventory_item_id = eri.revised_item_id
525 And msi.organization_id = eri.organization_Id
526 And msi.bom_item_type in (X_Model, X_OptionClass, X_Planning, X_Standard)
527 And rownum = 1
528 )
529 And rownum = 1;
530 */
531 l_dummy_counter := 1;
532
533 IF l_dummy_counter <> 0 THEN
534 -- submitting the concurrent request
535 --bug 11937134
536 l_resp_id := FND_GLOBAL.resp_id;
537
538 if (l_resp_id is null or l_resp_id = -1) then
539 l_set_dg := fnd_request.set_options(
540 datagroup=> 'Standard');
541 end if;
542 x_request_id := Fnd_Request.Submit_Request (
543 application => 'ENG',
544 program => 'ENCACN',
545 description => null,
546 -- start_time => sysdate, -- R12 comment out to use sysadte with timestamp
547 sub_request => false,
548 argument1 => to_char(p_org_id),
549 argument2 => to_char(2),
550 argument3 => null,
551 argument4 => p_change_notice,
552 argument5 => p_rev_item_seq_id,
553 argument6 => null,
554 argument7 => CHR(0),
555 argument8 => null,
556 argument9 => null,
557 argument10 => null,
558 argument11 => null,
559 argument12 => null,
560 argument13 => null,
561 argument14 => null,
562 argument15 => null,
563 argument16 => null,
564 argument17 => null,
565 argument18 => null,
566 argument19 => null,
567 argument20 => null,
568 argument21 => null,
569 argument22 => null,
570 argument23 => null,
571 argument24 => null,
572 argument25 => null,
573 argument26 => null,
574 argument27 => null,
575 argument28 => null,
576 argument29 => null,
577 argument30 => null,
578 argument31 => null,
579 argument32 => null,
580 argument33 => null,
581 argument34 => null,
582 argument35 => null,
583 argument36 => null,
584 argument37 => null,
585 argument38 => null,
586 argument39 => null,
587 argument40 => null,
588 argument41 => null,
589 argument42 => null,
590 argument43 => null,
591 argument44 => null,
592 argument45 => null,
593 argument46 => null,
594 argument47 => null,
595 argument48 => null,
596 argument49 => null,
597 argument50 => null,
598 argument51 => null,
599 argument52 => null,
600 argument53 => null,
601 argument54 => null,
602 argument55 => null,
603 argument56 => null,
604 argument57 => null,
605 argument58 => null,
606 argument59 => null,
607 argument60 => null,
608 argument61 => null,
609 argument62 => null,
610 argument63 => null,
611 argument64 => null,
612 argument65 => null,
613 argument66 => null,
614 argument67 => null,
615 argument68 => null,
616 argument69 => null,
617 argument70 => null,
618 argument71 => null,
619 argument72 => null,
620 argument73 => null,
621 argument74 => null,
622 argument75 => null,
623 argument76 => null,
624 argument77 => null,
625 argument78 => null,
626 argument79 => null,
627 argument80 => null,
628 argument81 => null,
629 argument82 => null,
630 argument83 => null,
631 argument84 => null,
632 argument85 => null,
633 argument86 => null,
634 argument87 => null,
635 argument88 => null,
636 argument89 => null,
637 argument90 => null,
638 argument91 => null,
639 argument92 => null,
640 argument93 => null,
641 argument94 => null,
642 argument95 => null,
643 argument96 => null,
644 argument97 => null,
645 argument98 => null,
646 argument99 => null,
647 argument100 => null
648 );
649
650 IF FND_API.to_Boolean( p_debug ) THEN
651 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('After: calling Fnd_Request.Submit_Request' );
652 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' x_request_id = ' || to_char(x_request_id) );
653 END IF ;
654
655 IF (x_request_id = 0) THEN
656 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CONCURRENT_PRGM');
657 FND_MESSAGE.Set_Token('OBJECT_NAME', 'EN'||'G.ENCACN(Implement ECO)');
658 -- concatenating to work around GSCC validation error without changing esisting behaviour
659 FND_MSG_PUB.Add;
660 RAISE FND_API.G_EXC_ERROR;
661 ELSE
662 x_return_status := FND_API.G_RET_STS_SUCCESS;
663 END IF;
664
665 IF FND_API.to_Boolean( p_debug ) THEN
666 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Successful: calling Fnd_Request.Submit_Request' );
667 END IF ;
668
669 ELSE
670 IF FND_API.to_Boolean( p_debug ) THEN
671 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('No implementable revised item found ... ' );
672 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Fnd_Request.Submit_Request not called ... ' );
673 END IF ;
674
675 FND_MESSAGE.Set_Name('ENG', 'ENG_CANT_IMPL_WO_REV_ITEMS');
676 FND_MSG_PUB.Add;
677 x_return_status := FND_API.G_RET_STS_ERROR ;
678
679 IF FND_API.to_Boolean( p_debug ) THEN
680 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Done - adding error message ... ' );
681 END IF ;
682
683 END IF ;
684
685
686 -- Standard ending code ------------------------------------------------
687 IF FND_API.To_Boolean ( p_commit ) THEN
688 COMMIT WORK;
689 END IF;
690
691 FND_MSG_PUB.Count_And_Get
692 ( p_count => x_msg_count,
693 p_data => x_msg_data );
694
695 IF FND_API.to_Boolean( p_debug ) THEN
696 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Finish. Eng Of Proc') ;
697 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
698 END IF ;
699
700
701 EXCEPTION
702 WHEN FND_API.G_EXC_ERROR THEN
703 ROLLBACK TO Implement_ECO_PUB;
704 x_return_status := FND_API.G_RET_STS_ERROR;
705 FND_MSG_PUB.Count_And_Get
706 ( p_count => x_msg_count
707 ,p_data => x_msg_data );
708 IF FND_API.to_Boolean( p_debug ) THEN
709 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with expected error.') ;
710 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
711 END IF ;
712 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
713 ROLLBACK TO Implement_ECO_PUB;
714 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
715 FND_MSG_PUB.Count_And_Get
716 ( p_count => x_msg_count
717 ,p_data => x_msg_data );
718 IF FND_API.to_Boolean( p_debug ) THEN
719 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with unexpected error.') ;
720 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
721 END IF ;
722 WHEN OTHERS THEN
723 ROLLBACK TO Implement_ECO_PUB;
724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
725 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
726 THEN
727 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
728 END IF;
729 FND_MSG_PUB.Count_And_Get
730 ( p_count => x_msg_count
731 ,p_data => x_msg_data );
732 IF FND_API.to_Boolean( p_debug ) THEN
733 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with other errors.') ;
734 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
735 END IF ;
736
737 END Implement_ECO;
738
739 -- Code changes for enhancement 6084027 start
740 /**
741 * Executes the Original ECO concurrent program
742 *
743 */
744 PROCEDURE Execute_ProcCP
745 (
746 p_api_version IN NUMBER --
747 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
748 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
749 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
750 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
751 ,p_output_dir IN VARCHAR2
752 ,p_debug_filename IN VARCHAR2
753 ,x_return_status OUT NOCOPY VARCHAR2 --
754 ,x_msg_count OUT NOCOPY NUMBER --
755 ,x_msg_data OUT NOCOPY VARCHAR2 --
756 ,p_change_id IN NUMBER --
757 ,p_change_notice IN VARCHAR2 --
758 ,p_rev_item_seq_id IN NUMBER := NULL
759 ,p_org_id IN NUMBER --
760 ,x_request_id OUT NOCOPY NUMBER --
761 )
762 IS
763 l_api_name CONSTANT VARCHAR2(30) := 'Implement_ECO';
764 l_api_version CONSTANT NUMBER := 1.0;
765 l_return_status VARCHAR2(1);
766 l_dummy_counter NUMBER := 0;
767 -- Status Lookups
768 --CANCELLED CONSTANT NUMBER := 5;
769 --IMPLEMENTED CONSTANT NUMBER := 6;
770
771 X_Model NUMBER := 1;
772 X_OptionClass NUMBER := 2;
773 X_Planning NUMBER := 3;
774 X_Standard NUMBER := 4;
775
776 BEGIN
777
778 -- Standard Start of API savepoint
779 SAVEPOINT Implement_ECO_PUB;
780
781 -- Standard call to check for call compatibility
782 IF NOT FND_API.Compatible_API_Call ( l_api_version
783 ,p_api_version
784 ,l_api_name
785 ,G_PKG_NAME )
786 THEN
787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
788 END IF;
789
790 -- Initialize message list if p_init_msg_list is set to TRUE.
791 IF FND_API.to_Boolean( p_init_msg_list ) THEN
792 FND_MSG_PUB.initialize;
793 END IF ;
794
795 -- For Test/Debug
796 IF FND_API.to_Boolean( p_debug ) THEN
797 ENG_CHANGE_ACTIONS_UTIL.Open_Debug_Session
798 ( p_file_name => p_debug_filename
799 , p_output_dir => p_output_dir
800 );
801 END IF ;
802
803 -- Write debug message if debug mode is on
804 IF FND_API.to_Boolean( p_debug ) THEN
805 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('ENG_Eco_Util.Execute_ProcCP log');
806 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('-----------------------------------------------------');
807 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_change_id : ' || to_char(p_change_id) );
808 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_change_notice : ' || p_change_notice );
809 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_org_id : ' || to_char(p_org_id) );
810 IF (p_rev_item_seq_id IS NOT NULL) THEN
811 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_rev_item_seq_id : ' || to_char(p_rev_item_seq_id) );
812 ELSE
813 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_rev_item_seq_id : NULL' );
814 END IF;
815 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('-----------------------------------------------------');
816 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Initializing return status... ' );
817 END IF ;
818
819 -- Initialize API return status to success
820 x_return_status := FND_API.G_RET_STS_SUCCESS;
821
822 -- Real pl/sql code starts here
823
824
825 -- If there is no open revised items, skip the concurrent request call
826 -- and return an error message
827 /*
828 Select count(*)
829 Into l_dummy_counter
830 From Eng_Revised_Items eri
831 Where eri.change_id = p_change_id
832 And eri.status_type not in ( 5, -- CANCELLED
833 6, -- IMPLEMENTED
834 9, -- IMPLEMENTATION_IN_PROGRESS
835 2 -- HOLD
836 )
837 And exists (
838 Select null
839 From mtl_system_items msi
840 Where msi.inventory_item_id = eri.revised_item_id
841 And msi.organization_id = eri.organization_Id
842 And msi.bom_item_type in (X_Model, X_OptionClass, X_Planning, X_Standard)
843 And rownum = 1
844 )
845 And rownum = 1;
846 */
847 l_dummy_counter := 1;
848
849 IF l_dummy_counter <> 0 THEN
850 -- submitting the concurrent request
851
852 x_request_id := Fnd_Request.Submit_Request (
853 application => 'ENG',
854 program => 'ENCACNC',
855 description => null,
856 start_time => sysdate,
857 sub_request => false,
858 argument1 => to_char(p_org_id),
859 argument2 => to_char(2),
860 argument3 => null,
861 argument4 => p_change_notice,
862 argument5 => p_rev_item_seq_id,
863 argument6 => null,
864 argument7 => CHR(0),
865 argument8 => null,
866 argument9 => null,
867 argument10 => null,
868 argument11 => null,
869 argument12 => null,
870 argument13 => null,
871 argument14 => null,
872 argument15 => null,
873 argument16 => null,
874 argument17 => null,
875 argument18 => null,
876 argument19 => null,
877 argument20 => null,
878 argument21 => null,
879 argument22 => null,
880 argument23 => null,
881 argument24 => null,
882 argument25 => null,
883 argument26 => null,
884 argument27 => null,
885 argument28 => null,
886 argument29 => null,
887 argument30 => null,
888 argument31 => null,
889 argument32 => null,
890 argument33 => null,
891 argument34 => null,
892 argument35 => null,
893 argument36 => null,
894 argument37 => null,
895 argument38 => null,
896 argument39 => null,
897 argument40 => null,
898 argument41 => null,
899 argument42 => null,
900 argument43 => null,
901 argument44 => null,
902 argument45 => null,
903 argument46 => null,
904 argument47 => null,
905 argument48 => null,
906 argument49 => null,
907 argument50 => null,
908 argument51 => null,
909 argument52 => null,
910 argument53 => null,
911 argument54 => null,
912 argument55 => null,
913 argument56 => null,
914 argument57 => null,
915 argument58 => null,
916 argument59 => null,
917 argument60 => null,
918 argument61 => null,
919 argument62 => null,
920 argument63 => null,
921 argument64 => null,
922 argument65 => null,
923 argument66 => null,
924 argument67 => null,
925 argument68 => null,
926 argument69 => null,
927 argument70 => null,
928 argument71 => null,
929 argument72 => null,
930 argument73 => null,
931 argument74 => null,
932 argument75 => null,
933 argument76 => null,
934 argument77 => null,
935 argument78 => null,
936 argument79 => null,
937 argument80 => null,
938 argument81 => null,
939 argument82 => null,
940 argument83 => null,
941 argument84 => null,
942 argument85 => null,
943 argument86 => null,
944 argument87 => null,
945 argument88 => null,
946 argument89 => null,
947 argument90 => null,
948 argument91 => null,
949 argument92 => null,
950 argument93 => null,
951 argument94 => null,
952 argument95 => null,
953 argument96 => null,
954 argument97 => null,
955 argument98 => null,
956 argument99 => null,
957 argument100 => null
958 );
959
960
961 IF FND_API.to_Boolean( p_debug ) THEN
962 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('After: calling Fnd_Request.Submit_Request' );
963 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' x_request_id = ' || to_char(x_request_id) );
964 END IF ;
965
966
967
968 IF (x_request_id = 0) THEN
969
970 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CONCURRENT_PRGM');
971 FND_MESSAGE.Set_Token('OBJECT_NAME', 'EN'||'G.ENCACN(Implement ECO)');
972 -- concatenating to work around GSCC validation error without changing esisting behaviour
973 FND_MSG_PUB.Add;
974 RAISE FND_API.G_EXC_ERROR;
975 ELSE
976
977 x_return_status := FND_API.G_RET_STS_SUCCESS;
978 END IF;
979
980 IF FND_API.to_Boolean( p_debug ) THEN
981 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Successful: calling Fnd_Request.Submit_Request' );
982 END IF ;
983
984 ELSE
985
986 IF FND_API.to_Boolean( p_debug ) THEN
987 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('No implementable revised item found ... ' );
988 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Fnd_Request.Submit_Request not called ... ' );
989 END IF ;
990
991 FND_MESSAGE.Set_Name('ENG', 'ENG_CANT_IMPL_WO_REV_ITEMS');
992 FND_MSG_PUB.Add;
993 x_return_status := FND_API.G_RET_STS_ERROR ;
994
995 IF FND_API.to_Boolean( p_debug ) THEN
996 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Done - adding error message ... ' );
997 END IF ;
998
999 END IF ;
1000
1001
1002
1003
1004 -- Standard ending code ------------------------------------------------
1005 IF FND_API.To_Boolean ( p_commit ) THEN
1006 COMMIT WORK;
1007 END IF;
1008
1009 FND_MSG_PUB.Count_And_Get
1010 ( p_count => x_msg_count,
1011 p_data => x_msg_data );
1012
1013 IF FND_API.to_Boolean( p_debug ) THEN
1014 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Finish. Eng Of Proc') ;
1015 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
1016 END IF ;
1017
1018
1019 EXCEPTION
1020 WHEN FND_API.G_EXC_ERROR THEN
1021
1022 ROLLBACK TO Implement_ECO_PUB;
1023 x_return_status := FND_API.G_RET_STS_ERROR;
1024 FND_MSG_PUB.Count_And_Get
1025 ( p_count => x_msg_count
1026 ,p_data => x_msg_data );
1027 IF FND_API.to_Boolean( p_debug ) THEN
1028 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with expected error.') ;
1029 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
1030 END IF ;
1031 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1032
1033 ROLLBACK TO Implement_ECO_PUB;
1034 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1035 FND_MSG_PUB.Count_And_Get
1036 ( p_count => x_msg_count
1037 ,p_data => x_msg_data );
1038 IF FND_API.to_Boolean( p_debug ) THEN
1039 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with unexpected error.') ;
1040 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
1041 END IF ;
1042 WHEN OTHERS THEN
1043
1044 ROLLBACK TO Implement_ECO_PUB;
1045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1046 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1047 THEN
1048 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
1049 END IF;
1050 FND_MSG_PUB.Count_And_Get
1051 ( p_count => x_msg_count
1052 ,p_data => x_msg_data );
1053 IF FND_API.to_Boolean( p_debug ) THEN
1054 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with other errors.') ;
1055 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
1056 END IF ;
1057
1058 END Execute_ProcCP;
1059 -- Code changes for enhancement 6084027 end
1060
1061 /**
1062 * ENG Change ECO Action
1063 * @author HaiXin Tie
1064 */
1065 PROCEDURE Propagate_ECO
1066 (
1067 p_api_version IN NUMBER --
1068 ,p_init_msg_list IN VARCHAR2 --
1069 ,p_commit IN VARCHAR2 --
1070 ,p_validation_level IN NUMBER --
1071 ,p_debug IN VARCHAR2 --
1072 ,p_output_dir IN VARCHAR2 --
1073 ,p_debug_filename IN VARCHAR2 --
1074 ,x_return_status OUT NOCOPY VARCHAR2 --
1075 ,x_msg_count OUT NOCOPY NUMBER --
1076 ,x_msg_data OUT NOCOPY VARCHAR2 --
1077 ,p_change_id IN NUMBER --
1078 ,p_change_notice IN VARCHAR2 --
1079 ,p_hierarchy_name IN VARCHAR2 --
1080 ,p_org_name IN VARCHAR2 --
1081 ,x_request_id OUT NOCOPY NUMBER --
1082 ,p_local_organization_id IN NUMBER := NULL -- -- Added for R12
1083 ,p_calling_API IN VARCHAR2 := NULL --R12
1084
1085 )
1086 IS
1087 l_api_name CONSTANT VARCHAR2(30) := 'Propagate_ECO';
1088 l_api_version CONSTANT NUMBER := 1.0;
1089 l_return_status VARCHAR2(1);
1090 BEGIN
1091
1092 -- Standard Start of API savepoint
1093 SAVEPOINT Propagate_ECO_PUB;
1094
1095 -- Standard call to check for call compatibility
1096 IF NOT FND_API.Compatible_API_Call ( l_api_version
1097 ,p_api_version
1098 ,l_api_name
1099 ,G_PKG_NAME )
1100 THEN
1101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1102 END IF;
1103
1104 -- Initialize message list if p_init_msg_list is set to TRUE.
1105 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1106 FND_MSG_PUB.initialize;
1107 END IF ;
1108
1109 -- For Test/Debug
1110 IF FND_API.to_Boolean( p_debug ) THEN
1111 ENG_CHANGE_ACTIONS_UTIL.Open_Debug_Session
1112 ( p_file_name => p_debug_filename
1113 , p_output_dir => p_output_dir
1114 );
1115 END IF ;
1116
1117 -- Write debug message if debug mode is on
1118 IF FND_API.to_Boolean( p_debug ) THEN
1119 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('ENG_Eco_Util.Propagate_ECO log');
1120 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('-----------------------------------------------------');
1121 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_change_id : ' || to_char(p_change_id) );
1122 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_change_notice : ' || p_change_notice );
1123 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_hierarchy_name : ' || p_hierarchy_name );
1124 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_org_name : ' || p_org_name );
1125 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_local_organization_id : ' || p_local_organization_id );
1126 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('-----------------------------------------------------');
1127 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Initializing return status... ' );
1128 END IF ;
1129
1130 -- Initialize API return status to success
1131 x_return_status := FND_API.G_RET_STS_SUCCESS;
1132
1133
1134 -- Real pl/sql code starts here
1135 x_request_id := Fnd_Request.Submit_Request (
1136 application => 'ENG',
1137 program => 'ENGECOBO',
1138 description => null,
1139 start_time => null,
1140 sub_request => false,
1141 argument1 => p_change_notice,
1142 argument2 => p_hierarchy_name,
1143 argument3 => p_org_name,
1144 argument4 => p_local_organization_id, -- Added for R12
1145 argument5 => p_calling_API,
1146 argument6 => chr(0),
1147 argument7 => null,
1148 argument8 => null,
1149 argument9 => null,
1150 argument10 => null,
1151 argument11 => null,
1152 argument12 => null,
1153 argument13 => null,
1154 argument14 => null,
1155 argument15 => null,
1156 argument16 => null,
1157 argument17 => null,
1158 argument18 => null,
1159 argument19 => null,
1160 argument20 => null,
1161 argument21 => null,
1162 argument22 => null,
1163 argument23 => null,
1164 argument24 => null,
1165 argument25 => null,
1166 argument26 => null,
1167 argument27 => null,
1168 argument28 => null,
1169 argument29 => null,
1170 argument30 => null,
1171 argument31 => null,
1172 argument32 => null,
1173 argument33 => null,
1174 argument34 => null,
1175 argument35 => null,
1176 argument36 => null,
1177 argument37 => null,
1178 argument38 => null,
1179 argument39 => null,
1180 argument40 => null,
1181 argument41 => null,
1182 argument42 => null,
1183 argument43 => null,
1184 argument44 => null,
1185 argument45 => null,
1186 argument46 => null,
1187 argument47 => null,
1188 argument48 => null,
1189 argument49 => null,
1190 argument50 => null,
1191 argument51 => null,
1192 argument52 => null,
1193 argument53 => null,
1194 argument54 => null,
1195 argument55 => null,
1196 argument56 => null,
1197 argument57 => null,
1198 argument58 => null,
1199 argument59 => null,
1200 argument60 => null,
1201 argument61 => null,
1202 argument62 => null,
1203 argument63 => null,
1204 argument64 => null,
1205 argument65 => null,
1206 argument66 => null,
1207 argument67 => null,
1208 argument68 => null,
1209 argument69 => null,
1210 argument70 => null,
1211 argument71 => null,
1212 argument72 => null,
1213 argument73 => null,
1214 argument74 => null,
1215 argument75 => null,
1216 argument76 => null,
1217 argument77 => null,
1218 argument78 => null,
1219 argument79 => null,
1220 argument80 => null,
1221 argument81 => null,
1222 argument82 => null,
1223 argument83 => null,
1224 argument84 => null,
1225 argument85 => null,
1226 argument86 => null,
1227 argument87 => null,
1228 argument88 => null,
1229 argument89 => null,
1230 argument90 => null,
1231 argument91 => null,
1232 argument92 => null,
1233 argument93 => null,
1234 argument94 => null,
1235 argument95 => null,
1236 argument96 => null,
1237 argument97 => null,
1238 argument98 => null,
1239 argument99 => null,
1240 argument100 => null);
1241
1242 IF FND_API.to_Boolean( p_debug ) THEN
1243 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('After: calling Fnd_Request.Submit_Request' );
1244 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' x_request_id = ' || to_char(x_request_id) );
1245 END IF ;
1246
1247 IF (x_request_id = 0) THEN
1248 FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CONCURRENT_PRGM');
1249 FND_MESSAGE.Set_Token('OBJECT_NAME', 'EN'||'G.ENGECOBO(Propagate ECO)');
1250 -- concatenating to work around GSCC validation error without changing esisting behaviour
1251 FND_MSG_PUB.Add;
1252 RAISE FND_API.G_EXC_ERROR;
1253 ELSE
1254 x_return_status := FND_API.G_RET_STS_SUCCESS;
1255 END IF;
1256
1257 IF FND_API.to_Boolean( p_debug ) THEN
1258 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Successful: calling Fnd_Request.Submit_Request' );
1259 END IF ;
1260
1261 ENGECOBO.PreProcess_Propagate_Request (
1262 p_api_version => 1.0
1263 , p_init_msg_list => FND_API.G_FALSE
1264 , p_commit => FND_API.G_FALSE
1265 , p_request_id => x_request_id
1266 , p_change_id => p_change_id
1267 , p_org_hierarchy_name => p_hierarchy_name
1268 , p_local_organization_id => p_local_organization_id
1269 , p_calling_API => p_calling_API
1270 , x_return_status => l_return_status
1271 , x_msg_count => x_msg_count
1272 , x_msg_data => x_msg_data
1273 ) ;
1274
1275 -- Standard ending code ------------------------------------------------
1276 IF FND_API.To_Boolean ( p_commit ) THEN
1277 COMMIT WORK;
1278 END IF;
1279
1280 FND_MSG_PUB.Count_And_Get
1281 ( p_count => x_msg_count,
1282 p_data => x_msg_data );
1283
1284 IF FND_API.to_Boolean( p_debug ) THEN
1285 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Finish. Eng Of Proc') ;
1286 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
1287 END IF ;
1288
1289
1290 EXCEPTION
1291 WHEN FND_API.G_EXC_ERROR THEN
1292 ROLLBACK TO Propagate_ECO_PUB;
1293 x_return_status := FND_API.G_RET_STS_ERROR;
1294 FND_MSG_PUB.Count_And_Get
1295 ( p_count => x_msg_count
1296 ,p_data => x_msg_data );
1297 IF FND_API.to_Boolean( p_debug ) THEN
1298 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with unexpected error.') ;
1299 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
1300 END IF ;
1301 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1302 ROLLBACK TO Propagate_ECO_PUB;
1303 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1304 FND_MSG_PUB.Count_And_Get
1305 ( p_count => x_msg_count
1306 ,p_data => x_msg_data );
1307 IF FND_API.to_Boolean( p_debug ) THEN
1308 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with unexpected error.') ;
1309 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
1310 END IF ;
1311 WHEN OTHERS THEN
1312 ROLLBACK TO Propagate_ECO_PUB;
1313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1314 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1315 THEN
1316 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
1317 END IF;
1318 FND_MSG_PUB.Count_And_Get
1319 ( p_count => x_msg_count
1320 ,p_data => x_msg_data );
1321 IF FND_API.to_Boolean( p_debug ) THEN
1322 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with unexpected error.') ;
1323 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
1324 END IF ;
1325
1326 END Propagate_ECO;
1327
1328
1329
1330
1331
1332
1333 PROCEDURE Reschedule_ECO
1334 (
1335 p_api_version IN NUMBER --
1336 ,p_init_msg_list IN VARCHAR2 --
1337 ,p_commit IN VARCHAR2 --
1338 ,p_validation_level IN NUMBER --
1339 ,p_debug IN VARCHAR2 --
1340 ,p_output_dir IN VARCHAR2 --
1341 ,p_debug_filename IN VARCHAR2 --
1342 ,x_return_status OUT NOCOPY VARCHAR2 --
1343 ,x_msg_count OUT NOCOPY NUMBER --
1344 ,x_msg_data OUT NOCOPY VARCHAR2 --
1345 ,p_change_id IN NUMBER --
1346 ,p_effectivity_date IN DATE --
1347 ,p_requestor_id IN NUMBER --
1348 ,p_comment IN VARCHAR2 --
1349 )
1350 IS
1351 l_api_name CONSTANT VARCHAR2(30) := 'Reschedule_ECO';
1352 l_api_version CONSTANT NUMBER := 1.0;
1353 l_return_status VARCHAR2(1);
1354
1355 x_user_id NUMBER := to_number(Fnd_Profile.Value('USER_ID'));
1356 x_login_id NUMBER := to_number(Fnd_Profile.Value('LOGIN_ID'));
1357 x_planning_item_access NUMBER := Fnd_Profile.Value('BOM:PLANNING_ITEM_ACCESS');
1358 x_model_item_access NUMBER := Fnd_Profile.Value('BOM:ITEM_ACCESS');
1359 x_standard_item_access NUMBER := Fnd_Profile.Value('BOM:STANDARD_ITEM_ACCESS');
1360 X_Model NUMBER := 1;
1361 X_OptionClass NUMBER := 2;
1362 X_Planning NUMBER := 3;
1363 X_Standard NUMBER := 4;
1364 x_change_notice VARCHAR2(10);
1365 x_organization_id NUMBER;
1366
1367 -- Status Lookups
1368 --CANCELLED CONSTANT NUMBER := 5;
1369 --IMPLEMENTED CONSTANT NUMBER := 6;
1370 x_is_Chg_Sch_Date_Allowed VARCHAR(1) := 'Y';
1371
1372 -- R12 Changes for common BOM
1373 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1374 -- Cursor to Fetch all source bill's component changes that are being updated
1375 -- by reschedule
1376 CURSOR c_source_components( cp_change_notice eng_engineering_changes.change_notice%TYPE) IS
1377 SELECT bcb.component_sequence_id
1378 FROM bom_components_b bcb
1379 WHERE bcb.CHANGE_NOTICE = cp_change_notice
1380 AND exists
1381 (select 'x' from bom_bill_of_materials
1382 where bill_sequence_id = bcb.bill_sequence_id
1383 and organization_id = x_organization_id )
1384 AND (bcb.common_component_sequence_id IS NULL
1385 OR bcb.common_component_sequence_id = bcb.component_sequence_id)
1386 AND bcb.IMPLEMENTATION_DATE IS NULL;
1387
1388 BEGIN
1389
1390 -- Standard Start of API savepoint
1391 SAVEPOINT Reschedule_ECO_PUB;
1392
1393 -- begin of vamohan changes
1394 is_Reschedule_ECO_Allowed(p_change_id, x_is_Chg_Sch_Date_Allowed);
1395 IF x_is_Chg_Sch_Date_Allowed = 'N'
1396 THEN
1397 FND_MESSAGE.Set_Name('ENG','ENG_DUP_REV_ITEM_WITH_NEW_REV'); -- create and use a new message
1398 --FND_MESSAGE.Set_Token('ITEM_NAMES', item_names);
1399 FND_MSG_PUB.Add;
1400 RAISE FND_API.G_EXC_ERROR;
1401 END IF;
1402 -- end of vamohan changes
1403
1404
1405 -- Standard call to check for call compatibility
1406 IF NOT FND_API.Compatible_API_Call ( l_api_version
1407 ,p_api_version
1408 ,l_api_name
1409 ,G_PKG_NAME )
1410 THEN
1411 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1412 END IF;
1413
1414 -- Initialize message list if p_init_msg_list is set to TRUE.
1415 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1416 FND_MSG_PUB.initialize;
1417 END IF ;
1418
1419 -- For Test/Debug
1420 IF FND_API.to_Boolean( p_debug ) THEN
1421 ENG_CHANGE_ACTIONS_UTIL.Open_Debug_Session
1422 ( p_file_name => p_debug_filename
1423 , p_output_dir => p_output_dir
1424 );
1425 END IF ;
1426
1427 -- Write debug message if debug mode is on
1428 IF FND_API.to_Boolean( p_debug ) THEN
1429 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('ENG_Eco_Util.Reschedule_ECO log');
1430 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('-----------------------------------------------------');
1431 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_change_id : ' || to_char(p_change_id) );
1432 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_effectivity_date : ' || to_char(p_effectivity_date) );
1433 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_requestor_id : ' || to_char(p_requestor_id) );
1434 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_comment : ' || substr(p_comment, 1, 240) );
1435 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('-----------------------------------------------------');
1436 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Initializing return status... ' );
1437 END IF ;
1438
1439 -- Initialize API return status to success
1440 x_return_status := FND_API.G_RET_STS_SUCCESS;
1441
1442
1443 -- Real pl/sql code starts here
1444 IF x_planning_item_access = 2 THEN
1445 X_Planning := null;
1446 END IF;
1447
1448 IF x_model_item_access = 2 THEN
1449 X_Model := null;
1450 X_OptionClass := null;
1451 END IF;
1452
1453 IF x_standard_item_access = 2 THEN
1454 X_Standard := null;
1455 END IF;
1456
1457 SELECT change_notice, organization_id
1458 INTO x_change_notice, x_organization_id
1459 FROM eng_engineering_changes
1460 WHERE change_id = p_change_id;
1461
1462 Update Eng_Revised_Items eri
1463 Set eri.scheduled_date = p_effectivity_date,
1464 eri.last_update_date = sysdate,
1465 eri.last_updated_by = x_user_id,
1466 eri.last_update_login = x_login_id
1467 Where eri.change_id = p_change_id
1468 And eri.status_type not in ( 5, -- CANCELLED
1469 6, -- IMPLEMENTED
1470 9, -- IMPLEMENTATION_IN_PROGRESS
1471 2 -- HOLD
1472 )
1473 And exists (
1474 Select null
1475 From mtl_system_items msi
1476 Where msi.inventory_item_id = eri.revised_item_id
1477 And msi.organization_id = eri.organization_Id
1478 And msi.bom_item_type in (X_Model, X_OptionClass, X_Planning, X_Standard)
1479 );
1480
1481 IF FND_API.to_Boolean( p_debug ) THEN
1482 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Eng_Revised_Items updated ... ' );
1483 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' SQL%ROWCOUNT = ' || to_char(SQL%ROWCOUNT));
1484 END IF ;
1485
1486 IF SQL%FOUND THEN
1487 IF FND_API.to_Boolean( p_debug ) THEN
1488 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('ENG_REVISED_ITEMS rows found, updating: ' );
1489 END IF ;
1490
1491 -- Insert records in the history table
1492 Insert into Eng_Current_Scheduled_Dates(
1493 change_id,
1494 change_notice,
1495 organization_id,
1496 revised_item_id,
1497 scheduled_date,
1498 last_update_date,
1499 last_updated_by,
1500 creation_date,
1501 created_by,
1502 last_update_login,
1503 schedule_id,
1504 employee_id,
1505 comments,
1506 revised_item_sequence_id)
1507 Select p_change_id,
1508 eri.change_notice,
1509 eri.organization_id,
1510 eri.revised_item_id,
1511 p_effectivity_date,
1512 sysdate,
1513 x_user_id,
1514 sysdate,
1515 x_user_id,
1516 x_login_id,
1517 eng_current_scheduled_dates_s.nextval,
1518 p_requestor_id,
1519 substr(p_comment, 1, 240),
1520 eri.revised_item_sequence_id
1521 From eng_revised_items eri,
1522 mtl_system_items msi
1523 Where eri.change_id = p_change_id
1524 And eri.revised_item_id = msi.inventory_item_id
1525 And eri.organization_id = msi.organization_id
1526 And eri.status_type not in ( 5, -- CANCELLED
1527 6, -- IMPLEMENTED
1528 9, -- IMPLEMENTATION_IN_PROGRESS
1529 2 -- HOLD
1530 )
1531 And msi.bom_item_type in
1532 (X_Model, X_OptionClass, X_Planning, X_Standard);
1533
1534 IF FND_API.to_Boolean( p_debug ) THEN
1535 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Eng_Current_Scheduled_Dates inserted ... ' );
1536 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' SQL%ROWCOUNT = ' || to_char(SQL%ROWCOUNT));
1537 END IF ;
1538
1539 -- update revised components EFFECTIVITY_DATE
1540 UPDATE BOM_INVENTORY_COMPONENTS bic
1541 SET bic.EFFECTIVITY_DATE = p_effectivity_date
1542 WHERE bic.CHANGE_NOTICE = x_change_notice
1543 AND (bic.common_component_sequence_id IS NULL
1544 OR bic.common_component_sequence_id = bic.component_sequence_id)
1545 -- This is to ensure that the destination bill's revised item
1546 -- reschedule doesnt affect its components effectivity date
1547 AND exists
1548 (select 'x' from bom_bill_of_materials
1549 where bill_sequence_id = bic.bill_sequence_id
1550 and organization_id = x_organization_id )
1551 AND bic.IMPLEMENTATION_DATE IS NULL;
1552
1553 IF FND_API.to_Boolean( p_debug ) THEN
1554 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_INVENTORY_COMPONENTS.EFFECTIVITY_DATE updated ... ' );
1555 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' SQL%ROWCOUNT = ' || to_char(SQL%ROWCOUNT));
1556 END IF ;
1557
1558 -- update revised components DISABLE_DATE
1559 UPDATE BOM_INVENTORY_COMPONENTS bic1
1560 SET bic1.DISABLE_DATE = p_effectivity_date
1561 WHERE bic1.CHANGE_NOTICE = x_change_notice
1562 AND bic1.ACD_TYPE = 3 -- ACD Type: Disable
1563 AND exists
1564 (select 'x' from bom_bill_of_materials
1565 where bill_sequence_id = bic1.bill_sequence_id
1566 and organization_id = x_organization_id )
1567 AND bic1.IMPLEMENTATION_DATE IS NULL;
1568
1569 -- R12 : Common BOM changes
1570 -- updating the replicated components for the pending changes
1571 FOR c_sc IN c_source_components(x_change_notice)
1572 LOOP
1573 BOMPCMBM.Update_Related_Components(
1574 p_src_comp_seq_id => c_sc.component_sequence_id
1575 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1576 , x_Return_Status => l_return_status);
1577 END LOOP;
1578 -- End changes for R12
1579
1580 IF FND_API.to_Boolean( p_debug ) THEN
1581 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_INVENTORY_COMPONENTS.DISABLE_DATE updated ... ' );
1582 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' SQL%ROWCOUNT = ' || to_char(SQL%ROWCOUNT));
1583 END IF ;
1584
1585 -- update operation sequences EFFECTIVITY_DATE
1586 UPDATE BOM_OPERATION_SEQUENCES bos
1587 SET bos.EFFECTIVITY_DATE = p_effectivity_date
1588 WHERE bos.CHANGE_NOTICE = x_change_notice
1589 AND exists
1590 (select 'x' from bom_operational_routings
1591 where routing_sequence_id = bos.routing_sequence_id
1592 and organization_id = x_organization_id )
1593 AND bos.IMPLEMENTATION_DATE IS NULL;
1594
1595 IF FND_API.to_Boolean( p_debug ) THEN
1596 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_OPERATION_SEQUENCES.EFFECTIVITY_DATE updated ... ' );
1597 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' SQL%ROWCOUNT = ' || to_char(SQL%ROWCOUNT));
1598 END IF ;
1599
1600 -- update operation sequences DISABLE_DATE
1601 UPDATE BOM_OPERATION_SEQUENCES bos1
1602 SET bos1.DISABLE_DATE = p_effectivity_date
1603 WHERE bos1.CHANGE_NOTICE = x_change_notice
1604 and bos1.ACD_TYPE = 3 -- ACD Type: Disable
1605 AND exists
1606 (select 'x' from bom_operational_routings
1607 where routing_sequence_id = bos1.routing_sequence_id
1608 and organization_id = x_organization_id )
1609 AND bos1.IMPLEMENTATION_DATE IS NULL;
1610
1611 IF FND_API.to_Boolean( p_debug ) THEN
1612 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_OPERATION_SEQUENCES.DISABLE_DATE updated ... ' );
1613 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' SQL%ROWCOUNT = ' || to_char(SQL%ROWCOUNT));
1614 END IF ;
1615
1616 -- Modified query for performance bug 4251776
1617 -- update rev item's new revision in MTL_ITEM_REVISIONS_B
1618 UPDATE MTL_ITEM_REVISIONS_B
1619 SET effectivity_date = p_effectivity_date,
1620 last_update_date = sysdate
1621 WHERE change_notice = x_change_notice
1622 AND organization_id = x_organization_id
1623 AND implementation_date is NULL
1624 AND (revised_item_sequence_id, revision_id) in (SELECT revised_item_sequence_id, new_item_revision_id
1625 FROM eng_revised_items eri
1626 WHERE change_id = p_change_id
1627 AND scheduled_date = p_effectivity_date
1628 AND new_item_revision is NOT NULL
1629 AND status_type not in ( 5, -- CANCELLED
1630 6, -- IMPLEMENTED
1631 9, -- IMPLEMENTATION_IN_PROGRESS
1632 2 -- HOLD
1633 )
1634 AND exists (SELECT null
1635 FROM mtl_system_items msi
1636 WHERE msi.inventory_item_id = eri.revised_item_id
1637 AND msi.organization_id = eri.organization_Id
1638 AND msi.bom_item_type in (X_Model, X_OptionClass,
1639 X_Planning, X_Standard)));
1640
1641
1642 -------not required to insert to MTL_ITEM_REVISIONS_TL as description is not there.
1643
1644
1645
1646
1647 IF FND_API.to_Boolean( p_debug ) THEN
1648 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('MTL_ITEM_REVISIONS updated ... ' );
1649 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' SQL%ROWCOUNT = ' || to_char(SQL%ROWCOUNT));
1650 END IF ;
1651
1652 -- update rev item's new revision in MTL_RTG_ITEM_REVISIONS
1653 UPDATE MTL_RTG_ITEM_REVISIONS
1654 SET effectivity_date = p_effectivity_date,
1655 last_update_date = sysdate
1656 WHERE change_notice = x_change_notice
1657 AND organization_id = x_organization_id
1658 AND implementation_date is NULL
1659 AND revised_item_sequence_id in (SELECT revised_item_sequence_id
1660 FROM eng_revised_items eri
1661 WHERE change_id = p_change_id
1662 AND scheduled_date = p_effectivity_date
1663 AND new_routing_revision is NOT NULL
1664 AND status_type not in ( 5, -- CANCELLED
1665 6, -- IMPLEMENTED
1666 9, -- IMPLEMENTATION_IN_PROGRESS
1667 2 -- HOLD
1668 )
1669 AND exists (SELECT null
1670 FROM mtl_system_items msi
1671 WHERE msi.inventory_item_id = eri.revised_item_id
1672 AND msi.organization_id = eri.organization_Id
1673 AND msi.bom_item_type in (X_Model, X_OptionClass,
1674 X_Planning, X_Standard)));
1675
1676 IF FND_API.to_Boolean( p_debug ) THEN
1677 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('MTL_RTG_ITEM_REVISIONS updated ... ' );
1678 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' SQL%ROWCOUNT = ' || to_char(SQL%ROWCOUNT));
1679 END IF ;
1680
1681
1682
1683 ELSE
1684 IF FND_API.to_Boolean( p_debug ) THEN
1685 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('ENG_REVISED_ITEMS rows NOT found. NO updates. ' );
1686 END IF ;
1687
1688 END IF;
1689
1690
1691 -- Standard ending code ------------------------------------------------
1692 IF FND_API.To_Boolean ( p_commit ) THEN
1693 COMMIT WORK;
1694 END IF;
1695
1696 FND_MSG_PUB.Count_And_Get
1697 ( p_count => x_msg_count,
1698 p_data => x_msg_data );
1699
1700 IF FND_API.to_Boolean( p_debug ) THEN
1701 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Finish. Eng Of Proc') ;
1702 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
1703 END IF ;
1704
1705
1706 EXCEPTION
1707 WHEN FND_API.G_EXC_ERROR THEN
1708 ROLLBACK TO Reschedule_ECO_PUB;
1709 x_return_status := FND_API.G_RET_STS_ERROR;
1710 FND_MSG_PUB.Count_And_Get
1711 ( p_count => x_msg_count
1712 ,p_data => x_msg_data );
1713 IF FND_API.to_Boolean( p_debug ) THEN
1714 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with unexpected error.') ;
1715 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
1716 END IF ;
1717 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1718 ROLLBACK TO Reschedule_ECO_PUB;
1719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1720 FND_MSG_PUB.Count_And_Get
1721 ( p_count => x_msg_count
1722 ,p_data => x_msg_data );
1723 IF FND_API.to_Boolean( p_debug ) THEN
1724 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with unexpected error.') ;
1725 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
1726 END IF ;
1727 WHEN OTHERS THEN
1728 ROLLBACK TO Reschedule_ECO_PUB;
1729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1730 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1731 THEN
1732 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
1733 END IF;
1734 FND_MSG_PUB.Count_And_Get
1735 ( p_count => x_msg_count
1736 ,p_data => x_msg_data );
1737 IF FND_API.to_Boolean( p_debug ) THEN
1738 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with unexpected error.') ;
1739 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
1740 END IF ;
1741
1742 END Reschedule_ECO;
1743
1744
1745
1746
1747 PROCEDURE Change_Effectivity_Date
1748 (
1749 p_api_version IN NUMBER --
1750 ,p_init_msg_list IN VARCHAR2 --
1751 ,p_commit IN VARCHAR2 --
1752 ,p_validation_level IN NUMBER --
1753 ,p_debug IN VARCHAR2 --
1754 ,p_output_dir IN VARCHAR2 --
1755 ,p_debug_filename IN VARCHAR2 --
1756 ,x_return_status OUT NOCOPY VARCHAR2 --
1757 ,x_msg_count OUT NOCOPY NUMBER --
1758 ,x_msg_data OUT NOCOPY VARCHAR2 --
1759 ,p_change_id IN NUMBER --
1760 ,p_effectivity_date IN DATE --
1761 ,p_comment IN VARCHAR2 --
1762 )
1763 IS
1764 l_api_name CONSTANT VARCHAR2(30) := 'Change_Effectivity_Date';
1765 l_api_version CONSTANT NUMBER := 1.0;
1766 l_return_status VARCHAR2(1);
1767
1768 x_user_id NUMBER := to_number(Fnd_Profile.Value('USER_ID'));
1769 x_login_id NUMBER := to_number(Fnd_Profile.Value('LOGIN_ID'));
1770 x_planning_item_access NUMBER := Fnd_Profile.Value('BOM:PLANNING_ITEM_ACCESS');
1771 x_model_item_access NUMBER := Fnd_Profile.Value('BOM:ITEM_ACCESS');
1772 x_standard_item_access NUMBER := Fnd_Profile.Value('BOM:STANDARD_ITEM_ACCESS');
1773 X_Model NUMBER := 1;
1774 X_OptionClass NUMBER := 2;
1775 X_Planning NUMBER := 3;
1776 X_Standard NUMBER := 4;
1777 x_change_notice VARCHAR2(10);
1778 x_organization_id NUMBER;
1779 -- bug 14548151
1780 l_organization_id eng_revised_items.organization_id%TYPE;
1781 l_revision eng_revised_items.new_item_revision%TYPE;
1782 l_item_id eng_revised_items.revised_item_id%TYPE;
1783 l_max_smaller_eff_date mtl_item_revisions_b.effectivity_date%TYPE;
1784 l_min_bigger_eff_date mtl_item_revisions_b.effectivity_date%TYPE;
1785 -- bug 14548151 end
1786
1787 -- Status Lookups
1788 --CANCELLED CONSTANT NUMBER := 5;
1789 --IMPLEMENTED CONSTANT NUMBER := 6;
1790 -- R12 Changes for common BOM
1791 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1792 -- Cursor to Fetch all source bill's component changes that are being updated
1793 -- by reschedule
1794 CURSOR c_source_components( cp_change_notice eng_engineering_changes.change_notice%TYPE) IS
1795 SELECT bcb.component_sequence_id
1796 FROM bom_components_b bcb
1797 WHERE bcb.CHANGE_NOTICE = cp_change_notice
1798 AND exists
1799 (select 'x' from bom_bill_of_materials
1800 where bill_sequence_id = bcb.bill_sequence_id
1801 and organization_id = x_organization_id )
1802 AND (bcb.common_component_sequence_id IS NULL
1803 OR bcb.common_component_sequence_id = bcb.component_sequence_id)
1804 AND bcb.IMPLEMENTATION_DATE IS NULL;
1805
1806 -- bug 14548151
1807 CURSOR c_item_revisions IS
1808 SELECT organization_id, new_item_revision, revised_item_id FROM eng_revised_items
1809 WHERE change_id = p_change_id
1810 AND implementation_date is NULL; -- fix bug 14620669, do not check the implemented Revised Item
1811 -- bug 14548151 end
1812
1813 BEGIN
1814
1815 -- Standard Start of API savepoint
1816 SAVEPOINT Change_Effectivity_Date_PUB;
1817
1818 -- Standard call to check for call compatibility
1819 IF NOT FND_API.Compatible_API_Call ( l_api_version
1820 ,p_api_version
1821 ,l_api_name
1822 ,G_PKG_NAME )
1823 THEN
1824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1825 END IF;
1826
1827 -- Initialize message list if p_init_msg_list is set to TRUE.
1828 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1829 FND_MSG_PUB.initialize;
1830 END IF ;
1831
1832 -- For Test/Debug
1833 IF FND_API.to_Boolean( p_debug ) THEN
1834 ENG_CHANGE_ACTIONS_UTIL.Open_Debug_Session
1835 ( p_file_name => p_debug_filename
1836 , p_output_dir => p_output_dir
1837 );
1838 END IF ;
1839
1840 -- Write debug message if debug mode is on
1841 IF FND_API.to_Boolean( p_debug ) THEN
1842 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('ENG_Eco_Util.Change_Effectivity_Date log');
1843 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('-----------------------------------------------------');
1844 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_change_id : ' || to_char(p_change_id) );
1845 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_effectivity_date : ' || to_char(p_effectivity_date) );
1846 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('p_comment : ' || substr(p_comment, 1, 240) );
1847 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('-----------------------------------------------------');
1848 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Initializing return status... ' );
1849 END IF ;
1850
1851 -- Initialize API return status to success
1852 x_return_status := FND_API.G_RET_STS_SUCCESS;
1853
1854
1855 -- Real pl/sql code starts here
1856 -- bug 14548151
1857 OPEN c_item_revisions;
1858 FETCH c_item_revisions INTO l_organization_id, l_revision, l_item_id;
1859 WHILE c_item_revisions%FOUND
1860 LOOP
1861 SELECT max(effectivity_date) into l_max_smaller_eff_date
1862 FROM mtl_item_revisions_b
1863 WHERE inventory_item_id = l_item_id and revision < l_revision
1864 AND organization_id = l_organization_id;
1865
1866 SELECT min(effectivity_date) into l_min_bigger_eff_date
1867 FROM mtl_item_revisions_b
1868 WHERE inventory_item_id = l_item_id and revision > l_revision
1869 AND organization_id = l_organization_id;
1870
1871 IF p_effectivity_date <= l_max_smaller_eff_date OR p_effectivity_date >= l_min_bigger_eff_date THEN
1872 FND_MESSAGE.Set_Name('ENG','ENG_REVISION_ORDER');
1873 FND_MSG_PUB.Add;
1874 RAISE FND_API.G_EXC_ERROR;
1875 END IF;
1876
1877 FETCH c_item_revisions INTO l_organization_id, l_revision, l_item_id;
1878 END LOOP;
1879 -- bug 14548151 end
1880
1881 IF x_planning_item_access = 2 THEN
1882 X_Planning := null;
1883 END IF;
1884
1885 IF x_model_item_access = 2 THEN
1886 X_Model := null;
1887 X_OptionClass := null;
1888 END IF;
1889
1890 IF x_standard_item_access = 2 THEN
1891 X_Standard := null;
1892 END IF;
1893
1894 SELECT change_notice, organization_id
1895 INTO x_change_notice, x_organization_id
1896 FROM eng_engineering_changes
1897 WHERE change_id = p_change_id;
1898
1899 Update Eng_Revised_Items eri
1900 Set eri.scheduled_date = p_effectivity_date,
1901 eri.last_update_date = sysdate,
1902 eri.last_updated_by = x_user_id,
1903 eri.last_update_login = x_login_id
1904 Where eri.change_id = p_change_id
1905 And eri.status_type not in ( 5, -- CANCELLED
1906 6, -- IMPLEMENTED
1907 9, -- IMPLEMENTATION_IN_PROGRESS
1908 2 -- HOLD
1909 )
1910 And exists (
1911 Select null
1912 From mtl_system_items msi
1913 Where msi.inventory_item_id = eri.revised_item_id
1914 And msi.organization_id = eri.organization_Id
1915 And msi.bom_item_type in (X_Model, X_OptionClass, X_Planning, X_Standard)
1916 );
1917
1918 IF FND_API.to_Boolean( p_debug ) THEN
1919 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Eng_Revised_Items updated ... ' );
1920 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' SQL%ROWCOUNT = ' || to_char(SQL%ROWCOUNT));
1921 END IF ;
1922
1923 IF SQL%FOUND THEN
1924 IF FND_API.to_Boolean( p_debug ) THEN
1925 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('ENG_REVISED_ITEMS rows found, updating: ' );
1926 END IF ;
1927
1928
1929 -- update revised components EFFECTIVITY_DATE
1930 UPDATE BOM_INVENTORY_COMPONENTS bic
1931 SET bic.EFFECTIVITY_DATE = p_effectivity_date
1932 WHERE bic.CHANGE_NOTICE = x_change_notice
1933 AND (bic.common_component_sequence_id IS NULL
1934 OR bic.common_component_sequence_id = bic.component_sequence_id)
1935 -- This is to ensure that the destination bill's revised item
1936 -- reschedule doesnt affect its components effectivity date
1937 AND exists
1938 (select 'x' from bom_bill_of_materials
1939 where bill_sequence_id = bic.bill_sequence_id
1940 and organization_id = x_organization_id )
1941 AND bic.IMPLEMENTATION_DATE IS NULL;
1942
1943 IF FND_API.to_Boolean( p_debug ) THEN
1944 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_INVENTORY_COMPONENTS.EFFECTIVITY_DATE updated ... ' );
1945 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' SQL%ROWCOUNT = ' || to_char(SQL%ROWCOUNT));
1946 END IF ;
1947
1948 -- update revised components DISABLE_DATE
1949 UPDATE BOM_INVENTORY_COMPONENTS bic1
1950 SET bic1.DISABLE_DATE = p_effectivity_date
1951 WHERE bic1.CHANGE_NOTICE = x_change_notice
1952 AND bic1.ACD_TYPE = 3 -- ACD Type: Disable
1953 AND exists
1954 (select 'x' from bom_bill_of_materials
1955 where bill_sequence_id = bic1.bill_sequence_id
1956 and organization_id = x_organization_id )
1957 AND bic1.IMPLEMENTATION_DATE IS NULL;
1958
1959 -- R12 : Common BOM changes
1960 -- updating the replicated components for the pending changes
1961 FOR c_sc IN c_source_components(x_change_notice)
1962 LOOP
1963 BOMPCMBM.Update_Related_Components(
1964 p_src_comp_seq_id => c_sc.component_sequence_id
1965 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1966 , x_Return_Status => l_return_status);
1967 END LOOP;
1968 -- End changes for R12
1969
1970 IF FND_API.to_Boolean( p_debug ) THEN
1971 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_INVENTORY_COMPONENTS.DISABLE_DATE updated ... ' );
1972 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' SQL%ROWCOUNT = ' || to_char(SQL%ROWCOUNT));
1973 END IF ;
1974
1975 -- update operation sequences EFFECTIVITY_DATE
1976 UPDATE BOM_OPERATION_SEQUENCES bos
1977 SET bos.EFFECTIVITY_DATE = p_effectivity_date
1978 WHERE bos.CHANGE_NOTICE = x_change_notice
1979 AND exists
1980 (select 'x' from bom_operational_routings
1981 where routing_sequence_id = bos.routing_sequence_id
1982 and organization_id = x_organization_id )
1983 AND bos.IMPLEMENTATION_DATE IS NULL;
1984
1985 IF FND_API.to_Boolean( p_debug ) THEN
1986 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_OPERATION_SEQUENCES.EFFECTIVITY_DATE updated ... ' );
1987 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' SQL%ROWCOUNT = ' || to_char(SQL%ROWCOUNT));
1988 END IF ;
1989
1990 -- update operation sequences DISABLE_DATE
1991 UPDATE BOM_OPERATION_SEQUENCES bos1
1992 SET bos1.DISABLE_DATE = p_effectivity_date
1993 WHERE bos1.CHANGE_NOTICE = x_change_notice
1994 and bos1.ACD_TYPE = 3 -- ACD Type: Disable
1995 AND exists
1996 (select 'x' from bom_operational_routings
1997 where routing_sequence_id = bos1.routing_sequence_id
1998 and organization_id = x_organization_id )
1999 AND bos1.IMPLEMENTATION_DATE IS NULL;
2000
2001 IF FND_API.to_Boolean( p_debug ) THEN
2002 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('BOM_OPERATION_SEQUENCES.DISABLE_DATE updated ... ' );
2003 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' SQL%ROWCOUNT = ' || to_char(SQL%ROWCOUNT));
2004 END IF ;
2005
2006 -- Modified query for performance bug 4251776
2007 -- update rev item's new revision in MTL_ITEM_REVISIONS _B
2008 UPDATE MTL_ITEM_REVISIONS_B
2009 SET effectivity_date = p_effectivity_date,
2010 last_update_date = sysdate
2011 WHERE change_notice = x_change_notice
2012 AND organization_id = x_organization_id
2013 AND implementation_date is NULL
2014 AND (revised_item_sequence_id, revision_id) in (SELECT revised_item_sequence_id, new_item_revision_id
2015 FROM eng_revised_items eri
2016 WHERE change_id = p_change_id
2017 AND scheduled_date = p_effectivity_date
2018 AND new_item_revision is NOT NULL
2019 AND status_type not in ( 5, -- CANCELLED
2020 6, -- IMPLEMENTED
2021 9, -- IMPLEMENTATION_IN_PROGRESS
2022 2 -- HOLD
2023 )
2024 AND exists (SELECT null
2025 FROM mtl_system_items msi
2026 WHERE msi.inventory_item_id = eri.revised_item_id
2027 AND msi.organization_id = eri.organization_Id
2028 AND msi.bom_item_type in (X_Model, X_OptionClass,
2029 X_Planning, X_Standard)));
2030 --no updation of mtl_item_revisions_tl
2031
2032
2033 IF FND_API.to_Boolean( p_debug ) THEN
2034 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('MTL_ITEM_REVISIONS updated ... ' );
2035 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' SQL%ROWCOUNT = ' || to_char(SQL%ROWCOUNT));
2036 END IF ;
2037
2038 -- update rev item's new revision in MTL_RTG_ITEM_REVISIONS
2039 UPDATE MTL_RTG_ITEM_REVISIONS
2040 SET effectivity_date = p_effectivity_date,
2041 last_update_date = sysdate
2042 WHERE change_notice = x_change_notice
2043 AND organization_id = x_organization_id
2044 AND implementation_date is NULL
2045 AND revised_item_sequence_id in (SELECT revised_item_sequence_id
2046 FROM eng_revised_items eri
2047 WHERE change_id = p_change_id
2048 AND scheduled_date = p_effectivity_date
2049 AND new_routing_revision is NOT NULL
2050 AND status_type not in ( 5, -- CANCELLED
2051 6, -- IMPLEMENTED
2052 9, -- IMPLEMENTATION_IN_PROGRESS
2053 2 -- HOLD
2054 )
2055 AND exists (SELECT null
2056 FROM mtl_system_items msi
2057 WHERE msi.inventory_item_id = eri.revised_item_id
2058 AND msi.organization_id = eri.organization_Id
2059 AND msi.bom_item_type in (X_Model, X_OptionClass,
2060 X_Planning, X_Standard)));
2061
2062 IF FND_API.to_Boolean( p_debug ) THEN
2063 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('MTL_RTG_ITEM_REVISIONS updated ... ' );
2064 ENG_CHANGE_ACTIONS_UTIL.Write_Debug(' SQL%ROWCOUNT = ' || to_char(SQL%ROWCOUNT));
2065 END IF ;
2066
2067
2068
2069 ELSE
2070 IF FND_API.to_Boolean( p_debug ) THEN
2071 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('ENG_REVISED_ITEMS rows NOT found. NO updates. ' );
2072 END IF ;
2073
2074 END IF;
2075
2076
2077 -- Standard ending code ------------------------------------------------
2078 IF FND_API.To_Boolean ( p_commit ) THEN
2079 COMMIT WORK;
2080 END IF;
2081
2082 FND_MSG_PUB.Count_And_Get
2083 ( p_count => x_msg_count,
2084 p_data => x_msg_data );
2085
2086 IF FND_API.to_Boolean( p_debug ) THEN
2087 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Finish. Eng Of Proc') ;
2088 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
2089 END IF ;
2090
2091
2092 EXCEPTION
2093 WHEN FND_API.G_EXC_ERROR THEN
2094 ROLLBACK TO Change_Effectivity_Date_PUB;
2095 x_return_status := FND_API.G_RET_STS_ERROR;
2096 FND_MSG_PUB.Count_And_Get
2097 ( p_count => x_msg_count
2098 ,p_data => x_msg_data );
2099 IF FND_API.to_Boolean( p_debug ) THEN
2100 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with unexpected error.') ;
2101 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
2102 END IF ;
2103 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2104 ROLLBACK TO Change_Effectivity_Date_PUB;
2105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2106 FND_MSG_PUB.Count_And_Get
2107 ( p_count => x_msg_count
2108 ,p_data => x_msg_data );
2109 IF FND_API.to_Boolean( p_debug ) THEN
2110 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with unexpected error.') ;
2111 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
2112 END IF ;
2113 WHEN OTHERS THEN
2114 ROLLBACK TO Change_Effectivity_Date_PUB;
2115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2116 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2117 THEN
2118 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
2119 END IF;
2120 FND_MSG_PUB.Count_And_Get
2121 ( p_count => x_msg_count
2122 ,p_data => x_msg_data );
2123 IF FND_API.to_Boolean( p_debug ) THEN
2124 ENG_CHANGE_ACTIONS_UTIL.Write_Debug('Rollback and Finish with unexpected error.') ;
2125 ENG_CHANGE_ACTIONS_UTIL.Close_Debug_Session ;
2126 END IF ;
2127
2128 END Change_Effectivity_Date;
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138 -- Added by MK on 09/01/2000 ECO for Routing
2139 PROCEDURE Cancel_Eco_Routing
2140 ( p_org_id IN NUMBER
2141 , p_eco_name IN VARCHAR2
2142 , p_cancel_comments IN VARCHAR2
2143 , p_user_id IN NUMBER
2144 , p_login_id IN NUMBER
2145 , p_prog_id IN NUMBER
2146 , p_prog_appid IN NUMBER
2147 , p_original_system_ref IN VARCHAR2
2148 )
2149 IS
2150
2151 BEGIN
2152
2153
2154 -- Delete substitute operation resources of all pending revised items on ECO
2155 DELETE FROM BOM_SUB_OPERATION_RESOURCES sor
2156 WHERE EXISTS (SELECT NULL
2157 FROM BOM_OPERATION_SEQUENCES bos
2158 , ENG_REVISED_ITEMS ri
2159 WHERE sor.operation_sequence_id = bos.operation_sequence_id
2160 AND bos.implementation_date IS NULL
2161 AND bos.revised_item_sequence_id = ri.revised_item_sequence_id
2162 AND ri.status_type = 5 -- Cancelled
2163 AND ri.organization_id = p_org_id
2164 AND ri.change_notice = p_eco_name
2165 ) ;
2166
2167 -- Delete operation resources of all pending revised items on ECO
2168
2169 DELETE FROM BOM_OPERATION_RESOURCES bor
2170 WHERE EXISTS (SELECT NULL
2171 FROM BOM_OPERATION_SEQUENCES bos
2172 , ENG_REVISED_ITEMS ri
2173 WHERE bor.operation_sequence_id = bos.operation_sequence_id
2174 AND bos.implementation_date IS NULL
2175 AND bos.revised_item_sequence_id = ri.revised_item_sequence_id
2176 AND ri.status_type = 5 -- Cancelled
2177 AND ri.organization_id = p_org_id
2178 AND ri.change_notice = p_eco_name
2179 ) ;
2180
2181 -- Insert the cancelled rev operations into eng_revised_operations
2182 INSERT INTO ENG_REVISED_OPERATIONS (
2183 operation_sequence_id
2184 , routing_sequence_id
2185 , operation_seq_num
2186 , last_update_date
2187 , last_updated_by
2188 , creation_date
2189 , created_by
2190 , last_update_login
2191 , standard_operation_id
2192 , department_id
2193 , operation_lead_time_percent
2194 , minimum_transfer_quantity
2195 , count_point_type
2196 , operation_description
2197 , effectivity_date
2198 , disable_date
2199 , backflush_flag
2200 , option_dependent_flag
2201 , attribute_category
2202 , attribute1
2203 , attribute2
2204 , attribute3
2205 , attribute4
2206 , attribute5
2207 , attribute6
2208 , attribute7
2209 , attribute8
2210 , attribute9
2211 , attribute10
2212 , attribute11
2213 , attribute12
2214 , attribute13
2215 , attribute14
2216 , attribute15
2217 , request_id
2218 , program_application_id
2219 , program_id
2220 , program_update_date
2221 , operation_type
2222 , reference_flag
2223 , process_op_seq_id
2224 , line_op_seq_id
2225 , yield
2226 , cumulative_yield
2227 , reverse_cumulative_yield
2228 , labor_time_calc
2229 , machine_time_calc
2230 , total_time_calc
2231 , labor_time_user
2232 , machine_time_user
2233 , total_time_user
2234 , net_planning_percent
2235 , x_coordinate
2236 , y_coordinate
2237 , include_in_rollup
2238 , operation_yield_enabled
2239 , change_notice
2240 , implementation_date
2241 , old_operation_sequence_id
2242 , acd_type
2243 , revised_item_sequence_id
2244 , cancellation_date
2245 , cancel_comments
2246 , original_system_reference )
2247 SELECT
2248 bos.OPERATION_SEQUENCE_ID
2249 , bos.ROUTING_SEQUENCE_ID
2250 , bos.OPERATION_SEQ_NUM
2251 , SYSDATE -- Last Update Date
2252 , p_user_id -- Last Updated By
2253 , SYSDATE -- Creation Date
2254 , p_user_id -- Created By
2255 , p_login_id -- Last Update Login
2256 , bos.STANDARD_OPERATION_ID
2257 , bos.DEPARTMENT_ID
2258 , bos.OPERATION_LEAD_TIME_PERCENT
2259 , bos.MINIMUM_TRANSFER_QUANTITY
2260 , bos.COUNT_POINT_TYPE
2261 , bos.OPERATION_DESCRIPTION
2262 , bos.EFFECTIVITY_DATE
2263 , bos.DISABLE_DATE
2264 , bos.BACKFLUSH_FLAG
2265 , bos.OPTION_DEPENDENT_FLAG
2266 , bos.ATTRIBUTE_CATEGORY
2267 , bos.ATTRIBUTE1
2268 , bos.ATTRIBUTE2
2269 , bos.ATTRIBUTE3
2270 , bos.ATTRIBUTE4
2271 , bos.ATTRIBUTE5
2272 , bos.ATTRIBUTE6
2273 , bos.ATTRIBUTE7
2274 , bos.ATTRIBUTE8
2275 , bos.ATTRIBUTE9
2276 , bos.ATTRIBUTE10
2277 , bos.ATTRIBUTE11
2278 , bos.ATTRIBUTE12
2279 , bos.ATTRIBUTE13
2280 , bos.ATTRIBUTE14
2281 , bos.ATTRIBUTE15
2282 , NULL -- Request Id
2283 , p_prog_appid -- Application Id
2284 , p_prog_id -- Program Id
2285 , SYSDATE -- program_update_date
2286 , bos.OPERATION_TYPE
2287 , bos.REFERENCE_FLAG
2288 , bos.PROCESS_OP_SEQ_ID
2289 , bos.LINE_OP_SEQ_ID
2290 , bos.YIELD
2291 , bos.CUMULATIVE_YIELD
2292 , bos.REVERSE_CUMULATIVE_YIELD
2293 , bos.LABOR_TIME_CALC
2294 , bos.MACHINE_TIME_CALC
2295 , bos.TOTAL_TIME_CALC
2296 , bos.LABOR_TIME_USER
2297 , bos.MACHINE_TIME_USER
2298 , bos.TOTAL_TIME_USER
2299 , bos.NET_PLANNING_PERCENT
2300 , bos.X_COORDINATE
2301 , bos.Y_COORDINATE
2302 , bos.INCLUDE_IN_ROLLUP
2303 , bos.OPERATION_YIELD_ENABLED
2304 , bos.CHANGE_NOTICE
2305 , bos.IMPLEMENTATION_DATE
2306 , bos.OLD_OPERATION_SEQUENCE_ID
2307 , bos.ACD_TYPE
2308 , bos.REVISED_ITEM_SEQUENCE_ID
2309 , SYSDATE -- Cancellation Date
2310 , substr(p_cancel_comments, 1, 240) -- Cancel Comments
2311 , p_original_system_ref
2312 FROM BOM_OPERATION_SEQUENCES bos
2313 , ENG_REVISED_ITEMS ri
2314 WHERE bos.implementation_date IS NULL
2315 AND bos.revised_item_sequence_id = ri.revised_item_sequence_id
2316 AND ri.status_type = 5 -- Cancelled
2317 AND ri.organization_id = p_org_id
2318 AND ri.change_notice = p_eco_name ;
2319
2320
2321 -- Delete the rows from bom_operation_sequences
2322
2323 DELETE FROM BOM_OPERATION_SEQUENCES bos
2324 WHERE EXISTS (SELECT NULL
2325 FROM ENG_REVISED_ITEMS ri
2326 WHERE bos.implementation_date IS NULL
2327 AND bos.revised_item_sequence_id = ri.revised_item_sequence_id
2328 AND ri.status_type = 5 -- Cancelled
2329 AND ri.organization_id = p_org_id
2330 AND ri.change_notice = p_eco_name
2331 ) ;
2332
2333
2334 -- Delete routing revisions created by revised items on ECO
2335
2336 DELETE FROM MTL_RTG_ITEM_REVISIONS rev
2337 WHERE EXISTS (SELECT NULL
2338 FROM ENG_REVISED_ITEMS ri
2339 WHERE rev.implementation_date IS NULL
2340 AND rev.revised_item_sequence_id = ri.revised_item_sequence_id
2341 AND ri.status_type = 5 -- Cancelled
2342 AND ri.organization_id = p_org_id
2343 AND ri.change_notice = p_eco_name
2344 ) ;
2345
2346 -- Delete the bom header if routing was created by this revised item and
2347 -- nothing else references this
2348
2349 DELETE FROM BOM_OPERATIONAL_ROUTINGS bor
2350 WHERE EXISTS ( SELECT NULL
2351 FROM ENG_REVISED_ITEMS ri
2352 WHERE bor.routing_sequence_id = ri.change_notice
2353 AND bor.routing_sequence_id = ri.routing_sequence_id
2354 AND TRUNC(ri.last_update_date) = TRUNC(SYSDATE)
2355 AND ri.status_type = 5 -- Cancelled
2356 AND ri.organization_id = p_org_id
2357 AND ri.change_notice = p_eco_name
2358 )
2359 AND NOT EXISTS (SELECT NULL
2360 FROM BOM_OPERATION_SEQUENCES bos
2361 WHERE bos.routing_sequence_id = bor.routing_sequence_id
2362 AND (bos.change_notice IS NULL
2363 OR bos.change_notice <> p_eco_name)
2364 )
2365 AND (( bor.alternate_routing_designator IS NULL
2366 AND NOT EXISTS( SELECT NULL
2367 FROM BOM_OPERATIONAL_ROUTINGS bor2
2368 WHERE bor2.organization_id = bor.organization_id
2369 AND bor2.assembly_item_id = bor.assembly_item_id
2370 AND bor2.alternate_routing_designator IS NOT NULL )
2371 )
2372 OR
2373 ( bor.alternate_routing_designator IS NOT NULL
2374 AND NOT EXISTS( SELECT NULL
2375 FROM ENG_REVISED_ITEMS ri2
2376 WHERE ri2.organization_id = bor.organization_id
2377 AND ri2.routing_sequence_id = bor.routing_sequence_id
2378 AND ri2.change_notice <> p_eco_name )
2379 )) ;
2380
2381
2382 -- If routing was deleted, then unset the routing_sequence_id on the revised items
2383 IF SQL%FOUND THEN
2384
2385 UPDATE ENG_REVISED_ITEMS ri
2386 SET routing_sequence_id = ''
2387 , program_id = p_prog_id
2388 , program_application_id = p_prog_appid
2389 , original_system_reference = p_original_system_ref
2390 , last_updated_by = p_user_id
2391 , last_update_login = p_login_id
2392 WHERE ri.organization_id = p_org_id
2393 AND ri.change_notice = p_eco_name
2394 AND ri.status_type = 5 -- Cancelled
2395 AND NOT EXISTS (SELECT 'No Rtg Header'
2396 FROM BOM_OPERATIONAL_ROUTINGS bor
2397 WHERE bor.routing_sequence_id = ri.routing_sequence_id
2398 ) ;
2399 END IF;
2400
2401 END Cancel_Eco_Routing;
2402
2403
2404
2405 -- Procedure Cancel_Eco
2406
2407 PROCEDURE Cancel_Eco
2408 ( org_id IN NUMBER
2409 , change_order IN VARCHAR2
2410 , user_id IN NUMBER
2411 , login IN NUMBER
2412 , req_id IN NUMBER
2413 , prog_id IN NUMBER
2414 , prog_appid IN NUMBER
2415 , orig_sysref IN VARCHAR2
2416 , p_cancel_comments IN VARCHAR2 -- Added by MK on 09/01/2000
2417 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2418 ) IS
2419 l_err_text VARCHAR2(2000);
2420 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2421 l_revision_id NUMBER;
2422 BEGIN
2423
2424 -- Set cancellation date of all pending revised items on ECO
2425
2426 UPDATE ENG_REVISED_ITEMS
2427 SET CANCELLATION_DATE = SYSDATE,
2428 STATUS_TYPE = 5,
2429 REQUEST_ID = request_id,
2430 PROGRAM_ID = prog_id,
2431 PROGRAM_APPLICATION_ID = prog_appid,
2432 ORIGINAL_SYSTEM_REFERENCE = orig_sysref,
2433 LAST_UPDATED_BY = user_id,
2434 LAST_UPDATE_LOGIN = login
2435 WHERE ORGANIZATION_ID = org_id
2436 AND CHANGE_NOTICE = change_order
2437 AND STATUS_TYPE NOT IN (5,6);
2438
2439 -- Delete substitute components of all pending revised items on ECO
2440
2441 DELETE FROM BOM_SUBSTITUTE_COMPONENTS SC
2442 WHERE SC.COMPONENT_SEQUENCE_ID IN
2443 (SELECT IC.COMPONENT_SEQUENCE_ID
2444 FROM BOM_INVENTORY_COMPONENTS IC, ENG_REVISED_ITEMS RI
2445 WHERE RI.ORGANIZATION_ID = org_id
2446 AND RI.CHANGE_NOTICE = change_order
2447 AND IC.REVISED_ITEM_SEQUENCE_ID = RI.REVISED_ITEM_SEQUENCE_ID
2448 AND IC.IMPLEMENTATION_DATE IS NULL);
2449
2450 -- Delete reference designators of all pending revised items on ECO
2451
2452 DELETE FROM BOM_REFERENCE_DESIGNATORS RD
2453 WHERE RD.COMPONENT_SEQUENCE_ID IN
2454 (SELECT IC.COMPONENT_SEQUENCE_ID
2455 FROM BOM_INVENTORY_COMPONENTS IC, ENG_REVISED_ITEMS RI
2456 WHERE RI.ORGANIZATION_ID = org_id
2457 AND RI.CHANGE_NOTICE = change_order
2458 AND IC.REVISED_ITEM_SEQUENCE_ID = RI.REVISED_ITEM_SEQUENCE_ID
2459 AND IC.IMPLEMENTATION_DATE IS NULL);
2460
2461 -- Insert the cancelled rev components into eng_revised_components
2462
2463 INSERT INTO ENG_REVISED_COMPONENTS (
2464 COMPONENT_SEQUENCE_ID,
2465 COMPONENT_ITEM_ID,
2466 OPERATION_SEQUENCE_NUM,
2467 BILL_SEQUENCE_ID,
2468 CHANGE_NOTICE,
2469 EFFECTIVITY_DATE,
2470 COMPONENT_QUANTITY,
2471 COMPONENT_YIELD_FACTOR,
2472 LAST_UPDATE_DATE,
2473 LAST_UPDATED_BY,
2474 CREATION_DATE,
2475 CREATED_BY,
2476 LAST_UPDATE_LOGIN,
2477 CANCELLATION_DATE,
2478 CANCEL_COMMENTS, -- Added by MK on 09/01/2000
2479 OLD_COMPONENT_SEQUENCE_ID,
2480 ITEM_NUM,
2481 WIP_SUPPLY_TYPE,
2482 COMPONENT_REMARKS,
2483 SUPPLY_SUBINVENTORY,
2484 SUPPLY_LOCATOR_ID,
2485 DISABLE_DATE,
2486 ACD_TYPE,
2487 PLANNING_FACTOR,
2488 QUANTITY_RELATED,
2489 SO_BASIS,
2490 OPTIONAL,
2491 MUTUALLY_EXCLUSIVE_OPTIONS,
2492 INCLUDE_IN_COST_ROLLUP,
2493 CHECK_ATP,
2494 SHIPPING_ALLOWED,
2495 REQUIRED_TO_SHIP,
2496 REQUIRED_FOR_REVENUE,
2497 INCLUDE_ON_SHIP_DOCS,
2498 LOW_QUANTITY,
2499 HIGH_QUANTITY,
2500 REVISED_ITEM_SEQUENCE_ID,
2501 ATTRIBUTE_CATEGORY,
2502 ATTRIBUTE1,
2503 ATTRIBUTE2,
2504 ATTRIBUTE3,
2505 ATTRIBUTE4,
2506 ATTRIBUTE5,
2507 ATTRIBUTE6,
2508 ATTRIBUTE7,
2509 ATTRIBUTE8,
2510 ATTRIBUTE9,
2511 ATTRIBUTE10,
2512 ATTRIBUTE11,
2513 ATTRIBUTE12,
2514 ATTRIBUTE13,
2515 ATTRIBUTE14,
2516 ATTRIBUTE15,
2517 REQUEST_ID,
2518 PROGRAM_ID,
2519 PROGRAM_APPLICATION_ID,
2520 ORIGINAL_SYSTEM_REFERENCE,
2521 BASIS_TYPE)
2522 SELECT
2523 IC.COMPONENT_SEQUENCE_ID,
2524 IC.COMPONENT_ITEM_ID,
2525 IC.OPERATION_SEQ_NUM,
2526 IC.BILL_SEQUENCE_ID,
2527 IC.CHANGE_NOTICE,
2528 IC.EFFECTIVITY_DATE,
2529 IC.COMPONENT_QUANTITY,
2530 IC. COMPONENT_YIELD_FACTOR,
2531 SYSDATE,
2532 user_id,
2533 SYSDATE,
2534 user_id,
2535 login,
2536 sysdate,
2537 substr(p_cancel_comments, 1, 240), -- Added by MK on 09/01/2000
2538 IC.OLD_COMPONENT_SEQUENCE_ID,
2539 IC.ITEM_NUM,
2540 IC.WIP_SUPPLY_TYPE,
2541 IC.COMPONENT_REMARKS,
2542 IC.SUPPLY_SUBINVENTORY,
2543 IC.SUPPLY_LOCATOR_ID,
2544 IC.DISABLE_DATE,
2545 IC.ACD_TYPE,
2546 IC.PLANNING_FACTOR,
2547 IC.QUANTITY_RELATED,
2548 IC.SO_BASIS,
2549 IC.OPTIONAL,
2550 IC.MUTUALLY_EXCLUSIVE_OPTIONS,
2551 IC.INCLUDE_IN_COST_ROLLUP,
2552 IC.CHECK_ATP,
2553 IC.SHIPPING_ALLOWED,
2554 IC.REQUIRED_TO_SHIP,
2555 IC.REQUIRED_FOR_REVENUE,
2556 IC.INCLUDE_ON_SHIP_DOCS,
2557 IC.LOW_QUANTITY,
2558 IC.HIGH_QUANTITY,
2559 IC.REVISED_ITEM_SEQUENCE_ID,
2560 IC.ATTRIBUTE_CATEGORY,
2561 IC.ATTRIBUTE1,
2562 IC.ATTRIBUTE2,
2563 IC.ATTRIBUTE3,
2564 IC.ATTRIBUTE4,
2565 IC.ATTRIBUTE5,
2566 IC.ATTRIBUTE6,
2567 IC.ATTRIBUTE7,
2568 IC.ATTRIBUTE8,
2569 IC.ATTRIBUTE9,
2570 IC.ATTRIBUTE10,
2571 IC.ATTRIBUTE11,
2572 IC.ATTRIBUTE12,
2573 IC.ATTRIBUTE13,
2574 IC.ATTRIBUTE14,
2575 IC.ATTRIBUTE15,
2576 req_id,
2577 prog_id,
2578 prog_appid,
2579 orig_sysref,
2580 IC.BASIS_TYPE
2581 FROM BOM_INVENTORY_COMPONENTS IC, ENG_REVISED_ITEMS RI
2582 WHERE RI.ORGANIZATION_ID = org_id
2583 AND RI.CHANGE_NOTICE = change_order
2584 AND IC.CHANGE_NOTICE = RI.CHANGE_NOTICE
2585 AND IC.REVISED_ITEM_SEQUENCE_ID = RI.REVISED_ITEM_SEQUENCE_ID
2586 AND RI.BILL_SEQUENCE_ID = IC.BILL_SEQUENCE_ID
2587 AND IC.IMPLEMENTATION_DATE IS NULL;
2588
2589 -- Delete the rows from bom_inventory_components
2590
2591 DELETE FROM BOM_INVENTORY_COMPONENTS IC
2592 WHERE CHANGE_NOTICE = change_order
2593 AND IMPLEMENTATION_DATE IS NULL
2594 AND REVISED_ITEM_SEQUENCE_ID IN (SELECT REVISED_ITEM_SEQUENCE_ID
2595 FROM ENG_REVISED_ITEMS ERI
2596 WHERE ERI.ORGANIZATION_ID = org_id
2597 AND ERI.CHANGE_NOTICE = change_order
2598 AND ERI.STATUS_TYPE = 5);
2599
2600 -- Delete item revisions created by revised items on ECO
2601
2602 delete from MTL_ITEM_REVISIONS_TL
2603 where revision_id in(select revision_id
2604 from MTL_ITEM_REVISIONS_B I
2605 WHERE CHANGE_NOTICE = change_order
2606 AND ORGANIZATION_ID = org_id
2607 AND IMPLEMENTATION_DATE IS NULL
2608 AND INVENTORY_ITEM_ID IN
2609 (SELECT REVISED_ITEM_ID
2610 FROM ENG_REVISED_ITEMS R
2611 WHERE R.CHANGE_NOTICE = change_order
2612 AND R.ORGANIZATION_ID = org_id
2613 AND R.REVISED_ITEM_SEQUENCE_ID = I.REVISED_ITEM_SEQUENCE_ID
2614 AND R.CANCELLATION_DATE IS NOT NULL));
2615
2616
2617 DELETE FROM MTL_ITEM_REVISIONS_B I
2618 WHERE CHANGE_NOTICE = change_order
2619 AND ORGANIZATION_ID = org_id
2620 AND IMPLEMENTATION_DATE IS NULL
2621 AND INVENTORY_ITEM_ID IN (SELECT REVISED_ITEM_ID
2622 FROM ENG_REVISED_ITEMS R
2623 WHERE R.CHANGE_NOTICE = change_order
2624 AND R.ORGANIZATION_ID = org_id
2625 AND R.REVISED_ITEM_SEQUENCE_ID = I.REVISED_ITEM_SEQUENCE_ID
2626 AND R.CANCELLATION_DATE IS NOT NULL);
2627
2628
2629
2630 -- Delete the bom header if bill was created by this revised item and
2631 -- nothing else references this
2632
2633 DELETE FROM BOM_BILL_OF_MATERIALS B
2634 WHERE B.BILL_SEQUENCE_ID in (SELECT BILL_SEQUENCE_ID
2635 FROM ENG_REVISED_ITEMS ERI
2636 WHERE ORGANIZATION_ID = org_id
2637 AND CHANGE_NOTICE = change_order
2638 AND STATUS_TYPE = 5
2639 AND TRUNC(LAST_UPDATE_DATE) = trunc(sysdate))
2640 AND B.PENDING_FROM_ECN = change_order
2641 AND NOT EXISTS (SELECT NULL
2642 FROM BOM_INVENTORY_COMPONENTS C
2643 WHERE C.BILL_SEQUENCE_ID = B.BILL_SEQUENCE_ID
2644 AND (C.CHANGE_NOTICE IS NULL
2645 OR C.CHANGE_NOTICE <> change_order))
2646 AND ((B.ALTERNATE_BOM_DESIGNATOR IS NULL
2647 AND NOT EXISTS (SELECT NULL
2648 FROM BOM_BILL_OF_MATERIALS B2
2649 WHERE B2.ORGANIZATION_ID = B.ORGANIZATION_ID
2650 AND B2.ASSEMBLY_ITEM_ID = B.ASSEMBLY_ITEM_ID
2651 AND B2.ALTERNATE_BOM_DESIGNATOR IS NOT NULL))
2652 OR
2653 (B.ALTERNATE_BOM_DESIGNATOR IS NOT NULL
2654 AND NOT EXISTS (SELECT NULL
2655 FROM ENG_REVISED_ITEMS R
2656 WHERE R.ORGANIZATION_ID = B.ORGANIZATION_ID
2657 AND R.BILL_SEQUENCE_ID = B.BILL_SEQUENCE_ID
2658 AND R.CHANGE_NOTICE <> change_order)));
2659
2660 -- If bill was deleted, then unset the bill_sequence_id on the revised items
2661 IF (SQL%ROWCOUNT > 0) THEN
2662 UPDATE ENG_REVISED_ITEMS R
2663 SET BILL_SEQUENCE_ID = '',
2664 REQUEST_ID = request_id,
2665 PROGRAM_ID = prog_id,
2666 PROGRAM_APPLICATION_ID = prog_appid,
2667 ORIGINAL_SYSTEM_REFERENCE = orig_sysref,
2668 LAST_UPDATED_BY = user_id,
2669 LAST_UPDATE_LOGIN = login
2670 WHERE R.ORGANIZATION_ID = org_id
2671 AND R.CHANGE_NOTICE = change_order
2672 AND R.STATUS_TYPE = 5
2673 AND NOT EXISTS (SELECT 'NO SUCH BILL'
2674 FROM BOM_BILL_OF_MATERIALS BOM
2675 WHERE BOM.BILL_SEQUENCE_ID = R.BILL_SEQUENCE_ID);
2676 END IF;
2677
2678
2679
2680 /****************************************************************
2681 * Added by MK on 09/01/2000
2682 * Cancel ECO for Routing
2683 ****************************************************************/
2684
2685 Cancel_Eco_Routing ( p_org_id => org_id
2686 , p_eco_name => change_order
2687 , p_cancel_comments => substr(p_cancel_comments, 1, 240)
2688 , p_user_id => user_id
2689 , p_login_id => login
2690 , p_prog_id => prog_id
2691 , p_prog_appid => prog_appid
2692 , p_original_system_ref => orig_sysref
2693 );
2694
2695
2696 EXCEPTION
2697 WHEN NO_DATA_FOUND THEN
2698 NULL;
2699
2700 WHEN OTHERS THEN
2701 IF G_CONTROL_REC.caller_type = 'FORM'
2702 THEN
2703 RAISE;
2704 END IF;
2705
2706 l_err_text := G_PKG_NAME || ' : (Cancel ECO) '
2707 || substrb(SQLERRM,1,200);
2708 Error_Handler.Add_Error_Token
2709 ( p_Message_Text => l_err_text
2710 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2711 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
2712 );
2713
2714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2715 END Cancel_ECO;
2716
2717 -- Procedure Update_Row
2718
2719 PROCEDURE Update_Row
2720 ( p_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
2721 , p_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_Unexposed_Rec_Type
2722 , p_old_ECO_rec IN ENG_ECO_PUB.ECO_Rec_Type
2723 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2724 , x_return_status OUT NOCOPY VARCHAR
2725 )
2726 IS
2727 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2728 l_err_text VARCHAR2(2000);
2729 l_user_id NUMBER;
2730 l_login_id NUMBER;
2731 l_prog_appid NUMBER;
2732 l_prog_id NUMBER;
2733 l_request_id NUMBER;
2734 l_std_item_access NUMBER := Eng_Globals.Get_STD_Item_Access;
2735 l_oc_item_access NUMBER := Eng_Globals.Get_OC_Item_Access;
2736 l_pln_item_access NUMBER := Eng_Globals.Get_PLN_Item_Access;
2737 l_mdl_item_access NUMBER := Eng_Globals.Get_MDL_Item_Access;
2738 l_change_name VARCHAR2(240); -- Bug 3032565
2739 BEGIN
2740
2741 x_return_status := FND_API.G_RET_STS_SUCCESS;
2742
2743 l_user_id := Eng_Globals.Get_User_Id;
2744 l_login_id := Eng_Globals.Get_Login_Id;
2745 l_request_id := ENG_GLOBALS.Get_request_id;
2746 l_prog_appid := ENG_GLOBALS.Get_prog_appid;
2747 l_prog_id := ENG_GLOBALS.Get_prog_id;
2748
2749 IF (g_control_rec.caller_type = 'FORM' AND
2750 g_control_rec.validation_controller = 'MAIN_EFFECTS')
2751 OR
2752 g_control_rec.caller_type <> 'FORM'
2753 THEN
2754
2755 BEGIN
2756 -- Bug 3032565 Defaulted Change Name
2757 IF (p_eco_rec.change_name is null OR p_eco_rec.change_name = '')
2758 THEN
2759 l_change_name := p_eco_rec.eco_name;
2760 Else
2761 l_change_name := p_eco_rec.change_name;
2762 END IF;
2763
2764
2765 UPDATE eng_engineering_changes
2766 SET attribute7 = p_eco_rec.attribute7,
2767 attribute8 = p_eco_rec.attribute8,
2768 attribute9 = p_eco_rec.attribute9,
2769 attribute10 = p_eco_rec.attribute10,
2770 attribute11 = p_eco_rec.attribute11,
2771 attribute12 = p_eco_rec.attribute12,
2772 attribute13 = p_eco_rec.attribute13,
2773 attribute14 = p_eco_rec.attribute14,
2774 attribute15 = p_eco_rec.attribute15,
2775 request_id = l_request_id,
2776 program_application_id = l_prog_appid,
2777 program_id = l_prog_id,
2778 approval_status_type = p_unexp_eco_rec.approval_status_type,
2779 approval_date = p_eco_rec.approval_date,
2780 approval_list_id = p_unexp_eco_rec.approval_list_id,
2781 change_order_type_id = p_unexp_eco_rec.change_order_type_id,
2782 responsible_organization_id = p_unexp_eco_rec.responsible_org_id,
2783 approval_request_date = p_eco_rec.approval_request_date,
2784 change_notice = p_eco_rec.eco_name,
2785 organization_id = p_unexp_eco_rec.organization_id,
2786 last_update_date = sysdate,
2787 last_updated_by = l_user_id,
2788 last_update_login = l_login_id,
2789 description = p_eco_rec.description,
2790 status_type = p_unexp_eco_rec.status_type,
2791 initiation_date = p_unexp_eco_rec.initiation_date,
2792 implementation_date = p_unexp_eco_rec.implementation_date,
2793 cancellation_date = p_unexp_eco_rec.cancellation_date,
2794 cancellation_comments = p_eco_rec.cancellation_comments,
2795 priority_code = p_eco_rec.priority_code,
2796 reason_code = p_eco_rec.reason_code,
2797 estimated_eng_cost = p_eco_rec.eng_implementation_cost,
2798 estimated_mfg_cost = p_eco_rec.mfg_implementation_cost,
2799 requestor_id = p_unexp_eco_rec.requestor_id,
2800 attribute_category = p_eco_rec.attribute_category,
2801 attribute1 = p_eco_rec.attribute1,
2802 attribute2 = p_eco_rec.attribute2,
2803 attribute3 = p_eco_rec.attribute3,
2804 attribute4 = p_eco_rec.attribute4,
2805 attribute5 = p_eco_rec.attribute5,
2806 attribute6 = p_eco_rec.attribute6,
2807 original_system_reference = p_eco_rec.original_system_reference,
2808 project_id = p_unexp_eco_rec.project_id,
2809 task_id = p_unexp_eco_rec.task_id,
2810 organization_hierarchy = p_eco_rec.organization_hierarchy,
2811 change_mgmt_type_code = p_unexp_eco_rec.change_mgmt_type_code, -- eng change,
2812 assignee_id = p_unexp_eco_rec.assignee_id, -- eng chagne,
2813 need_by_date = p_eco_rec.need_by_date, -- eng chagne,
2814 internal_use_only = p_eco_rec.internal_use_only, -- eng chagne,
2815 source_type_code = p_unexp_eco_rec.source_type_code, -- eng chagne,
2816 source_id = p_unexp_eco_rec.source_id, -- eng chagne,
2817 effort = p_eco_rec.effort, -- eng chagne,
2818 hierarchy_id = p_unexp_eco_rec.hierarchy_id, -- eng chagne
2819 -- Bug 2919076 // kamohan
2820 -- Start Changes
2821 change_name = l_change_name , -- Bug 3032565 p_eco_rec.change_name
2822 -- status_code = p_unexp_eco_rec.status_code
2823 status_code = nvl(p_unexp_eco_rec.status_code, p_unexp_eco_rec.status_type), -- Bug 3424007
2824 source_name = p_ECO_rec.Source_Name
2825 -- End Changes
2826 WHERE change_notice = p_eco_rec.eco_name
2827 AND organization_id = p_unexp_eco_rec.organization_id;
2828
2829 x_return_status := FND_API.G_RET_STS_SUCCESS;
2830
2831 EXCEPTION
2832 WHEN OTHERS THEN
2833
2834 IF G_CONTROL_REC.caller_type = 'FORM'
2835 THEN
2836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2837 RAISE;
2838 END IF;
2839
2840 l_err_text := G_PKG_NAME || ' : Utility (ECO Update) '
2841 || substrb(SQLERRM,1,200);
2842 Error_Handler.Add_Error_Token
2843 ( p_Message_Text => l_err_text
2844 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2845 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
2846 );
2847
2848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2849 RETURN;
2850 END;
2851 END IF;
2852
2853 -- If call if from form, execute this block of code only if side effects
2854 -- processing has been requested
2855 -- By AS on 10/13/99
2856
2857 IF (g_control_rec.caller_type = 'FORM' AND
2858 g_control_rec.validation_controller = 'SIDE_EFFECTS')
2859 OR
2860 g_control_rec.caller_type <> 'FORM'
2861 THEN
2862
2863 BEGIN
2864 IF p_Unexp_ECO_rec.status_type = 5
2865 THEN
2866 -- Mark ECO as 'Cancelled' and process children accordingly
2867
2868 Cancel_Eco ( org_id => p_Unexp_ECO_rec.organization_id
2869 , change_order => p_ECO_rec.ECO_Name
2870 , user_id => ENG_GLOBALS.Get_user_id
2871 , login => ENG_GLOBALS.Get_login_id
2872 , req_id => ENG_GLOBALS.Get_request_id
2873 , prog_id => ENG_GLOBALS.Get_prog_id
2874 , prog_appid => ENG_GLOBALS.Get_prog_appid
2875 , orig_sysref => p_ECO_rec.original_system_reference
2876 , p_cancel_comments => p_ECO_rec.cancellation_comments
2877 -- Added by MK on 09/01/2000
2878 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2879 );
2880
2881 ELSE
2882 -- From ENGFMECO.pld (Procedure After_Update)
2883
2884 -- Check that the user has access to the BOM Item Type
2885 -- of the revised item
2886 --
2887 IF Eng_Globals.Get_STD_Item_Access IS NULL AND
2888 Eng_Globals.Get_PLN_Item_Access IS NULL AND
2889 Eng_Globals.Get_MDL_Item_Access IS NULL
2890 THEN
2891 --
2892 -- Get respective profile values
2893 --
2894 IF fnd_profile.value('BOM:STANDARD_ITEM_ACCESS') = '1'
2895 THEN
2896 Eng_Globals.Set_STD_Item_Access
2897 ( p_std_item_access => 4);
2898 ELSE
2899 Eng_Globals.Set_STD_Item_Access
2900 ( p_std_item_access => NULL);
2901 END IF;
2902
2903 IF fnd_profile.value('BOM:MODEL_ITEM_ACCESS') = '1'
2904 THEN
2905 Eng_Globals.Set_MDL_Item_Access
2906 ( p_mdl_item_access => 1);
2907 Eng_Globals.Set_OC_Item_Access
2908 ( p_oc_item_access => 2);
2909 ELSE
2910 Eng_Globals.Set_MDL_Item_Access
2911 ( p_mdl_item_access => NULL);
2912 Eng_Globals.Set_OC_Item_Access
2913 ( p_oc_item_access => NULL);
2914 END IF;
2915
2916 IF fnd_profile.value('BOM:PLANNING_ITEM_ACCESS') = '1'
2917 THEN
2918 Eng_Globals.Set_PLN_Item_Access
2919 ( p_pln_item_access => 3);
2920 ELSE
2921 Eng_Globals.Set_PLN_Item_Access
2922 ( p_pln_item_access => NULL);
2923 END IF;
2924 END IF;
2925
2926 l_std_item_access := Eng_Globals.Get_STD_Item_Access;
2927 l_oc_item_access := Eng_Globals.Get_OC_Item_Access;
2928 l_pln_item_access := Eng_Globals.Get_PLN_Item_Access;
2929 l_mdl_item_access := Eng_Globals.Get_MDL_Item_Access;
2930
2931 UPDATE eng_revised_items eri
2932 SET eri.status_type = p_Unexp_ECO_rec.status_type,
2933 -- If ECO status is 'Scheduled', set Auto-Implement Date to SYSDATE, else NULL
2934 eri.auto_implement_date = decode(p_Unexp_ECO_rec.status_type, 4, SYSDATE, NULL),
2935 -- If ECO status is Hold, set MRP Active to No, else Yes
2936 eri.mrp_active = decode(p_Unexp_ECO_rec.status_type, 2, 2, 1),
2937 eri.last_update_date = SYSDATE,
2938 eri.last_updated_by = l_user_id,
2939 eri.last_update_login = l_login_id,
2940 eri.request_id = l_request_id,
2941 eri.program_id = l_prog_id,
2942 eri.program_application_id = l_prog_appid,
2943 eri.original_system_reference
2944 = p_ECO_rec.original_system_reference
2945 WHERE eri.change_notice = p_ECO_rec.ECO_name
2946 AND eri.organization_id = p_Unexp_ECO_rec.organization_id
2947 AND eri.status_type not in (5,6) -- Cancelled or Implemented
2948 AND exists
2949 -- modify only those items which the user has access to
2950 (SELECT null
2951 FROM mtl_system_items msi
2952 WHERE msi.inventory_item_id = eri.revised_item_id
2953 AND msi.organization_id = eri.organization_id
2954 AND msi.bom_item_type IN
2955 (l_STD_Item_Access
2956 ,l_OC_Item_Access
2957 ,l_PLN_Item_Access
2958 ,l_MDL_Item_Access));
2959 END IF;
2960
2961 x_return_status := FND_API.G_RET_STS_SUCCESS;
2962
2963 EXCEPTION
2964 WHEN NO_DATA_FOUND THEN
2965 NULL;
2966
2967 WHEN OTHERS THEN
2968
2969 IF G_CONTROL_REC.caller_type = 'FORM'
2970 THEN
2971 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2972 RAISE;
2973 END IF;
2974
2975 l_err_text := G_PKG_NAME || ' : Utility (ECO Update) '
2976 || substrb(SQLERRM,1,200);
2977 Error_Handler.Add_Error_Token
2978 ( p_message_name => NULL
2979 , p_Message_Text => l_err_text
2980 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2981 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
2982 );
2983
2984 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2985 RETURN;
2986 END;
2987 END IF;
2988
2989 END Update_Row;
2990
2991 -- Bug: 3424007
2992 -- Procedure to default lifecycle phases for ERP ECOs
2993 Procedure Default_Lifecycle_phases
2994 ( p_change_id IN NUMBER )
2995 IS
2996 l_user_id NUMBER := Eng_Globals.Get_User_Id;
2997 l_login_id NUMBER := Eng_Globals.Get_Login_Id;
2998 l_seq_no NUMBER := 0 ;
2999 l_lifecycle_phase_id NUMBER;
3000 phase_types phase_list_type;
3001
3002 BEGIN
3003
3004 phase_types(1) := 1; --'Open'
3005 -- bug: 3446554 Defaulting the lifecycle in order 1,7,4,6.
3006 -- Scheduled phase to be followed by implemented
3007 phase_types(2) := 7; --'Released'
3008 phase_types(3) := 4; --'Scheduled'
3009 phase_types(4) := 6; --'Implemented'
3010
3011 FOR lp IN phase_types.FIRST..phase_types.LAST
3012 LOOP
3013 SELECT eng_lifecycle_statuses_s.nextval
3014 INTO l_lifecycle_phase_id
3015 FROM dual;
3016
3017 l_seq_no := l_seq_no + 10;
3018
3019 insert into ENG_LIFECYCLE_STATUSES (
3020 CHANGE_LIFECYCLE_STATUS_ID
3021 , ENTITY_NAME
3022 , ENTITY_ID1
3023 , ENTITY_ID2
3024 , ENTITY_ID3
3025 , ENTITY_ID4
3026 , ENTITY_ID5
3027 , SEQUENCE_NUMBER
3028 , STATUS_CODE
3029 , START_DATE
3030 , COMPLETION_DATE
3031 , CHANGE_WF_ROUTE_ID
3032 , AUTO_PROMOTE_STATUS
3033 , AUTO_DEMOTE_STATUS
3034 , WORKFLOW_STATUS
3035 , CHANGE_EDITABLE_FLAG
3036 , CREATION_DATE
3037 , CREATED_BY
3038 , LAST_UPDATE_DATE
3039 , LAST_UPDATED_BY
3040 , LAST_UPDATE_LOGIN
3041 , ITERATION_NUMBER
3042 , ACTIVE_FLAG
3043 , WF_SIG_POLICY
3044 , CHANGE_WF_ROUTE_TEMPLATE_ID)
3045 values (
3046 l_lifecycle_phase_id
3047 , 'ENG_CHANGE'
3048 , p_change_id
3049 , null
3050 , null
3051 , null
3052 , null
3053 , l_seq_no
3054 , phase_types(lp)
3055 , null
3056 , null
3057 , null
3058 , null
3059 , null
3060 , null
3061 , null
3062 , sysdate
3063 , l_user_id
3064 , sysdate
3065 , l_user_id
3066 , l_login_id
3067 , 0
3068 , 'Y'
3069 , null
3070 , NULL );
3071 END LOOP;
3072
3073 END Default_Lifecycle_phases;
3074
3075 -- Procedure Insert_Row
3076
3077 PROCEDURE Insert_Row
3078 ( p_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
3079 , p_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_Unexposed_Rec_Type
3080 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3081 , x_return_status OUT NOCOPY VARCHAR
3082 )
3083 IS
3084 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3085 l_err_text VARCHAR2(2000);
3086 l_user_id NUMBER;
3087 l_login_id NUMBER;
3088 l_prog_appid NUMBER;
3089 l_prog_id NUMBER;
3090 l_request_id NUMBER;
3091 l_change_name VARCHAR2(240); -- Bug 3032565
3092 l_change_id NUMBER;
3093 BEGIN
3094
3095 x_return_status := FND_API.G_RET_STS_SUCCESS;
3096
3097 l_user_id := Eng_Globals.Get_User_Id;
3098 l_login_id := Eng_Globals.Get_Login_Id;
3099 l_request_id := ENG_GLOBALS.Get_request_id;
3100 l_prog_appid := ENG_GLOBALS.Get_prog_appid;
3101 l_prog_id := ENG_GLOBALS.Get_prog_id;
3102
3103
3104 -- Bug 3032565 Defaulted Change Name
3105 IF (p_ECO_rec.change_name is null OR p_ECO_rec.change_name = '')
3106 THEN
3107 l_change_name := p_ECO_rec.eco_name;
3108 Else
3109 l_change_name := p_ECO_rec.change_name;
3110 END IF;
3111
3112 IF BOM_Globals.get_debug = 'Y'
3113 Then
3114 Error_Handler.write_debug('Start to insert');
3115 END IF;
3116
3117 INSERT INTO ENG_ENGINEERING_CHANGES
3118 ( ATTRIBUTE7
3119 , ATTRIBUTE8
3120 , ATTRIBUTE9
3121 , ATTRIBUTE10
3122 , ATTRIBUTE11
3123 , ATTRIBUTE12
3124 , ATTRIBUTE13
3125 , ATTRIBUTE14
3126 , ATTRIBUTE15
3127 , REQUEST_ID
3128 , PROGRAM_APPLICATION_ID
3129 , PROGRAM_ID
3130 , PROGRAM_UPDATE_DATE
3131 , APPROVAL_STATUS_TYPE
3132 , APPROVAL_DATE
3133 , APPROVAL_LIST_ID
3134 , CHANGE_ORDER_TYPE_ID
3135 , RESPONSIBLE_ORGANIZATION_ID
3136 , APPROVAL_REQUEST_DATE
3137 , CHANGE_NOTICE
3138 , ORGANIZATION_ID
3139 , CHANGE_NAME
3140 , LAST_UPDATE_DATE
3141 , LAST_UPDATED_BY
3142 , CREATION_DATE
3143 , CREATED_BY
3144 , LAST_UPDATE_LOGIN
3145 , DESCRIPTION
3146 , STATUS_TYPE
3147 , INITIATION_DATE
3148 , IMPLEMENTATION_DATE
3149 , CANCELLATION_DATE
3150 , CANCELLATION_COMMENTS
3151 , PRIORITY_CODE
3152 , REASON_CODE
3153 , ESTIMATED_ENG_COST
3154 , ESTIMATED_MFG_COST
3155 , REQUESTOR_ID
3156 , ATTRIBUTE_CATEGORY
3157 , ATTRIBUTE1
3158 , ATTRIBUTE2
3159 , ATTRIBUTE3
3160 , ATTRIBUTE4
3161 , ATTRIBUTE5
3162 , ATTRIBUTE6
3163 , ORIGINAL_SYSTEM_REFERENCE
3164 , PROJECT_ID
3165 , TASK_ID
3166 , CHANGE_ID
3167 , ORGANIZATION_HIERARCHY
3168 , CHANGE_MGMT_TYPE_CODE
3169 , ASSIGNEE_ID
3170 , NEED_BY_DATE
3171 , INTERNAL_USE_ONLY
3172 , SOURCE_TYPE_CODE
3173 , SOURCE_ID
3174 , EFFORT
3175 , HIERARCHY_ID
3176 , PLM_OR_ERP_CHANGE --11.5.10
3177 , status_code
3178 , Change_Notice_Prefix --11.5.10
3179 , source_name
3180 )
3181 VALUES
3182 ( p_ECO_rec.attribute7
3183 , p_ECO_rec.attribute8
3184 , p_ECO_rec.attribute9
3185 , p_ECO_rec.attribute10
3186 , p_ECO_rec.attribute11
3187 , p_ECO_rec.attribute12
3188 , p_ECO_rec.attribute13
3189 , p_ECO_rec.attribute14
3190 , p_ECO_rec.attribute15
3191 , l_request_id
3192 , l_prog_appid
3193 , l_prog_id
3194 , SYSDATE
3195 , p_Unexp_ECO_rec.approval_status_type
3196 , p_ECO_rec.approval_date
3197 , p_Unexp_ECO_rec.approval_list_id
3198 , p_Unexp_ECO_rec.change_order_type_id
3199 , p_Unexp_ECO_rec.responsible_org_id
3200 , p_ECO_rec.approval_request_date
3201 , p_ECO_rec.ECO_name
3202 , p_Unexp_ECO_rec.organization_id
3203 , l_change_name -- Bug 3032565 nvl(p_ECO_rec.change_name, p_ECO_rec.ECO_name)
3204 , SYSDATE
3205 , l_user_id
3206 , SYSDATE
3207 , l_user_id
3208 , l_login_id
3209 , p_ECO_rec.description
3210 , p_Unexp_ECO_rec.status_type
3211 , p_Unexp_ECO_rec.initiation_date
3212 , p_Unexp_ECO_rec.implementation_date
3213 , p_Unexp_ECO_rec.cancellation_date
3214 , p_ECO_rec.cancellation_comments
3215 , p_ECO_rec.priority_code
3216 , p_ECO_rec.reason_code
3217 , p_ECO_rec.ENG_implementation_cost
3218 , p_ECO_rec.MFG_implementation_Cost
3219 , p_Unexp_ECO_rec.requestor_id
3220 , p_ECO_rec.attribute_category
3221 , p_ECO_rec.attribute1
3222 , p_ECO_rec.attribute2
3223 , p_ECO_rec.attribute3
3224 , p_ECO_rec.attribute4
3225 , p_ECO_rec.attribute5
3226 , p_ECO_rec.attribute6
3227 , p_ECO_rec.original_system_reference
3228 , p_Unexp_ECO_rec.project_id
3229 , p_Unexp_ECO_rec.task_id
3230 , p_Unexp_ECO_rec.change_id
3231 , p_ECO_rec.organization_hierarchy
3232 , p_Unexp_ECO_rec.change_mgmt_type_code -- Eng Change
3233 , p_Unexp_ECO_rec.assignee_id -- Eng Change
3234 , p_ECO_rec.need_by_date -- Eng Chagne
3235 , p_ECO_rec.internal_use_only -- Eng Chagne
3236 , p_Unexp_ECO_rec.source_type_code -- Eng Chagne
3237 , p_Unexp_ECO_rec.source_id -- Eng Chagne
3238 , p_ECO_rec.effort -- Eng Chagne
3239 , p_Unexp_ECO_rec.hierarchy_id -- Eng Chagne
3240 , p_Eco_rec.Plm_Or_Erp_Change --11.5.10
3241 , p_Unexp_ECO_rec.status_code
3242 , NULL --l_change_name --Bug 3570162
3243 , p_ECO_rec.Source_Name
3244 );
3245
3246 -- Bug: 3424007: Call Default_Lifecycle_phases
3247 IF (p_Eco_rec.Plm_Or_Erp_Change = 'ERP')
3248 THEN
3249 Default_Lifecycle_phases(p_Unexp_ECO_rec.change_id);
3250 END IF;
3251
3252 IF BOM_Globals.get_debug = 'Y'
3253 Then
3254 Error_Handler.write_debug('right after insert');
3255 end if;
3256
3257 BEGIN
3258 ENG_CHANGE_TEXT_UTIL.Insert_Update_Change ( p_change_id => p_Unexp_ECO_rec.change_id );
3259 EXCEPTION
3260 WHEN OTHERS THEN
3261 Error_Handler.write_debug('right after ENG_CHANGE_TEXT_UTIL.Insert_Update_Change');
3262
3263 END;
3264
3265 EXCEPTION
3266
3267 WHEN OTHERS THEN
3268
3269 IF BOM_Globals.get_debug = 'Y'
3270 Then
3271 Error_Handler.write_debug('error in insert');
3272 Error_Handler.write_debug(p_ECO_rec.ECO_name);
3273 Error_Handler.write_debug(to_char(p_Unexp_ECO_rec.organization_id));
3274 Error_Handler.write_debug(p_Unexp_ECO_rec.status_type);
3275 Error_Handler.write_debug(p_Unexp_ECO_rec.initiation_date);
3276 Error_Handler.write_debug(to_char(p_Unexp_ECO_rec.change_order_type_id));
3277 Error_Handler.write_debug(to_char(l_user_id));
3278 Error_Handler.write_debug(to_char(l_login_id));
3279
3280 END IF;
3281
3282
3283
3284 Error_Handler.Close_Debug_Session;
3285
3286 IF G_CONTROL_REC.caller_type = 'FORM'
3287 THEN
3288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3289 RAISE;
3290 END IF;
3291
3292 l_err_text := G_PKG_NAME || ' : Utility (ECO Insert) '
3293 || substrb(SQLERRM,1,200);
3294 Error_Handler.Add_Error_Token
3295 ( p_message_name => NULL
3296 , p_Message_Text => l_err_text
3297 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3298 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
3299 );
3300
3301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3302
3303
3304
3305
3306
3307 END Insert_Row;
3308
3309 -- Procedure Delete_Row
3310
3311 PROCEDURE Delete_Row
3312 ( p_change_notice IN VARCHAR2
3313 , p_organization_id IN NUMBER
3314 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3315 , x_return_status OUT NOCOPY VARCHAR
3316 )
3317 IS
3318 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3319 l_err_text VARCHAR2(2000);
3320 l_Token_Tbl Error_Handler.Token_Tbl_Type;
3321 BEGIN
3322
3323 l_token_tbl(1).token_name := 'ECO_NAME';
3324 l_token_tbl(1).token_value := p_change_notice;
3325
3326 x_return_status := FND_API.G_RET_STS_SUCCESS;
3327
3328 BEGIN
3329 DELETE FROM ENG_ENGINEERING_CHANGES
3330 WHERE CHANGE_NOTICE = p_change_notice
3331 AND ORGANIZATION_ID = p_organization_id;
3332
3333 EXCEPTION
3334 WHEN OTHERS THEN
3335
3336 IF G_CONTROL_REC.caller_type = 'FORM'
3337 THEN
3338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3339 RAISE;
3340 END IF;
3341
3342 l_err_text := G_PKG_NAME || ' : Utility (ECO Delete) '
3343 || substrb(SQLERRM,1,200);
3344 Error_Handler.Add_Error_Token
3345 ( p_Message_Text => l_err_text
3346 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3347 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
3348 );
3349
3350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3351 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3352 RETURN;
3353 END;
3354
3355
3356 BEGIN
3357 DELETE FROM ENG_CHANGE_ORDER_REVISIONS
3358 WHERE CHANGE_NOTICE = p_change_notice
3359 AND ORGANIZATION_ID = p_organization_id;
3360
3361 EXCEPTION
3362 WHEN NO_DATA_FOUND THEN
3363 NULL;
3364
3365 WHEN OTHERS THEN
3366
3367 IF G_CONTROL_REC.caller_type = 'FORM'
3368 THEN
3369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3370 RAISE;
3371 END IF;
3372
3373 l_err_text := G_PKG_NAME || ' : Utility (ECO Revisions Delete) '
3374 || substrb(SQLERRM,1,200);
3375 Error_Handler.Add_Error_Token
3376 ( p_Message_Text => l_err_text
3377 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3378 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
3379 );
3380
3381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3382 RETURN;
3383 END;
3384
3385 BEGIN
3386
3387 -- Delete associated Approval History records
3388
3389 DELETE FROM ENG_ECO_SUBMIT_REVISIONS
3390 WHERE CHANGE_NOTICE = p_change_notice
3391 AND ORGANIZATION_ID = p_organization_id;
3392
3393 -- log warning
3394
3395 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3396 THEN
3397 Error_Handler.Add_Error_Token
3398 ( p_Message_Name => 'ENG_ECO_APP_HISTORY_DELETED'
3399 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3400 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
3401 , p_Token_Tbl => l_Token_Tbl
3402 );
3403 END IF;
3404
3405 EXCEPTION
3406 WHEN NO_DATA_FOUND THEN
3407 NULL;
3408
3409 WHEN OTHERS THEN
3410
3411 l_err_text := G_PKG_NAME || ' : Utility (Approval History Delete) '
3412 || substrb(SQLERRM,1,200);
3413 Error_Handler.Add_Error_Token
3414 ( p_Message_Text => l_err_text
3415 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3416 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
3417 );
3418
3419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3420 RETURN;
3421 END;
3422 END Delete_Row;
3423
3424 -- Procedure Perform_Writes
3425
3426 PROCEDURE Perform_Writes
3427 ( p_ECO_rec IN ENG_ECO_PUB.Eco_Rec_Type
3428 , p_Unexp_ECO_rec IN ENG_ECO_PUB.ECO_Unexposed_Rec_Type
3429 , p_old_ECO_rec IN ENG_ECO_PUB.Eco_Rec_Type
3430 , p_control_rec IN BOM_BO_PUB.Control_Rec_Type
3431 := BOM_BO_PUB.G_DEFAULT_CONTROL_REC
3432 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3433 , x_return_status OUT NOCOPY VARCHAR
3434 )
3435 IS
3436 -- cursor to get the lines for a given eco: bug 5414834
3437 CURSOR lines_for_eco( p_change_id NUMBER) IS
3438 SELECT status_code ,sequence_number , name
3439 FROM eng_change_lines_vl
3440 WHERE eng_change_lines_vl.change_id = p_change_id
3441 and sequence_number<> -1;
3442
3443
3444 BEGIN
3445
3446 IF BOM_Globals.get_debug = 'Y'
3447 Then
3448 Error_Handler.write_debug('Start the perform writes..');
3449 END IF;
3450
3451 G_CONTROL_REC := p_control_rec;
3452
3453 IF p_ECO_rec.transaction_type = 'CREATE'
3454 THEN
3455 Insert_Row
3456 ( p_ECO_rec => p_ECO_rec
3457 , p_Unexp_ECO_rec => p_Unexp_ECO_rec
3458 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
3459 , x_return_status => x_return_status
3460 );
3461
3462 -- R12 Added condition to rasei PLM CM Event
3463 IF p_ECO_rec.plm_or_erp_change = 'PLM'
3464 THEN
3465
3466 ENG_CHANGE_BES_UTIL.Raise_Create_Change_Event
3467 ( p_change_id => p_Unexp_ECO_rec.change_id
3468 );
3469
3470
3471 IF BOM_Globals.get_debug = 'Y'
3472 Then
3473 Error_Handler.write_debug('Raised PLM CM create event ..');
3474 END IF;
3475
3476
3477 END IF ;
3478
3479 IF BOM_Globals.get_debug = 'Y'
3480 Then
3481 Error_Handler.write_debug('end of insert row..');
3482 END IF;
3483
3484
3485 ELSIF p_ECO_rec.transaction_type = 'UPDATE'
3486 THEN
3487 --change the status of all open lines bug:5414834
3488 if( p_Unexp_ECO_rec.status_type = 11 OR p_Unexp_ECO_rec.status_type = 5 ) then
3489 FOR line_rec IN lines_for_eco(p_Unexp_ECO_rec.Change_Id)
3490 LOOP
3491 UPDATE eng_change_lines SET status_code = p_Unexp_ECO_rec.status_type
3492 WHERE status_code=1 AND change_id = p_Unexp_ECO_rec.Change_Id;
3493 END LOOP;
3494 end if;
3495 Update_Row
3496 ( p_ECO_rec => p_ECO_rec
3497 , p_Unexp_ECO_rec => p_Unexp_ECO_rec
3498 , p_old_ECO_rec => p_old_ECO_rec
3499 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
3500 , x_return_status => x_return_status
3501 );
3502
3503 -- R12 Added condition to rasei PLM CM Event
3504 IF p_ECO_rec.plm_or_erp_change = 'PLM'
3505 THEN
3506
3507 ENG_CHANGE_BES_UTIL.Raise_Update_Change_Event
3508 ( p_change_id => p_Unexp_ECO_rec.change_id
3509 );
3510
3511 IF BOM_Globals.get_debug = 'Y'
3512 Then
3513 Error_Handler.write_debug('Raised PLM CM update event ..');
3514 END IF;
3515
3516
3517 END IF ;
3518
3519 IF BOM_Globals.get_debug = 'Y'
3520 Then
3521 Error_Handler.write_debug('end of update row..');
3522 END IF;
3523
3524
3525 ELSIF p_ECO_rec.transaction_type = 'DELETE'
3526 THEN
3527 Delete_Row
3528 ( p_change_notice => p_ECO_rec.ECO_name
3529 , p_organization_id => p_Unexp_ECO_rec.organization_id
3530 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
3531 , x_return_status => x_return_status
3532 );
3533
3534 IF BOM_Globals.get_debug = 'Y'
3535 Then
3536 Error_Handler.write_debug('end of delete row..');
3537 END IF;
3538
3539 END IF;
3540
3541 IF BOM_Globals.get_debug = 'Y'
3542 Then
3543 Error_Handler.write_debug('end of peform write..');
3544 END IF;
3545
3546 END Perform_Writes;
3547
3548 -- Function Query_Row
3549
3550 PROCEDURE Query_Row
3551 ( p_change_notice IN VARCHAR2
3552 , p_organization_id IN NUMBER
3553 , x_ECO_rec OUT NOCOPY ENG_Eco_PUB.Eco_Rec_Type
3554 , x_ECO_Unexp_Rec OUT NOCOPY ENG_Eco_PUB.Eco_Unexposed_Rec_Type
3555 , x_return_status OUT NOCOPY VARCHAR2
3556 , x_err_text OUT NOCOPY VARCHAR2)
3557 IS
3558 l_ECO_rec ENG_Eco_PUB.Eco_Rec_Type;
3559 l_ECO_Unexp_rec ENG_ECO_PUB.ECO_Unexposed_Rec_Type;
3560 l_err_text VARCHAR2(2000);
3561 BEGIN
3562
3563
3564
3565 SELECT ATTRIBUTE7
3566 , ATTRIBUTE8
3567 , ATTRIBUTE9
3568 , ATTRIBUTE10
3569 , ATTRIBUTE11
3570 , ATTRIBUTE12
3571 , ATTRIBUTE13
3572 , ATTRIBUTE14
3573 , ATTRIBUTE15
3574 , APPROVAL_STATUS_TYPE
3575 , APPROVAL_DATE
3576 , APPROVAL_LIST_ID
3577 , CHANGE_ORDER_TYPE_ID
3578 , RESPONSIBLE_ORGANIZATION_ID
3579 , APPROVAL_REQUEST_DATE
3580 , CHANGE_NOTICE
3581 , ORGANIZATION_ID
3582 , DESCRIPTION
3583 , STATUS_CODE
3584 , STATUS_TYPE
3585 , INITIATION_DATE
3586 , IMPLEMENTATION_DATE
3587 , CANCELLATION_DATE
3588 , CANCELLATION_COMMENTS
3589 , PRIORITY_CODE
3590 , REASON_CODE
3591 , ESTIMATED_ENG_COST
3592 , ESTIMATED_MFG_COST
3593 , REQUESTOR_ID
3594 , ATTRIBUTE_CATEGORY
3595 , ATTRIBUTE1
3596 , ATTRIBUTE2
3597 , ATTRIBUTE3
3598 , ATTRIBUTE4
3599 , ATTRIBUTE5
3600 , ATTRIBUTE6
3601 , PROJECT_ID
3602 , TASK_ID
3603 , CHANGE_ID
3604 , ORGANIZATION_HIERARCHY
3605 , CHANGE_MGMT_TYPE_CODE -- Eng Change
3606 , ASSIGNEE_ID -- Eng Change
3607 , NEED_BY_DATE -- Eng Chagne
3608 , INTERNAL_USE_ONLY -- Eng Chagne
3609 , SOURCE_TYPE_CODE -- Eng Chagne
3610 , SOURCE_ID -- Eng Change
3611 , EFFORT -- Eng Change
3612 , HIERARCHY_ID --bug 10108394
3613 INTO l_ECO_rec.attribute7
3614 , l_ECO_rec.attribute8
3615 , l_ECO_rec.attribute9
3616 , l_ECO_rec.attribute10
3617 , l_ECO_rec.attribute11
3618 , l_ECO_rec.attribute12
3619 , l_ECO_rec.attribute13
3620 , l_ECO_rec.attribute14
3621 , l_ECO_rec.attribute15
3622 , l_ECO_Unexp_rec.approval_status_type
3623 , l_ECO_rec.approval_date
3624 , l_ECO_Unexp_rec.approval_list_id
3625 , l_ECO_Unexp_rec.change_order_type_id
3626 , l_ECO_Unexp_rec.responsible_org_id
3627 , l_ECO_rec.approval_request_date
3628 , l_ECO_rec.ECO_Name
3629 , l_ECO_Unexp_rec.organization_id
3630 , l_ECO_rec.description
3631 , l_ECO_Unexp_rec.status_code
3632 , l_ECO_Unexp_rec.status_type
3633 , l_ECO_Unexp_rec.initiation_date
3634 , l_ECO_Unexp_rec.implementation_date
3635 , l_ECO_Unexp_rec.cancellation_date
3636 , l_ECO_rec.cancellation_comments
3637 , l_ECO_rec.priority_code
3638 , l_ECO_rec.reason_code
3639 , l_ECO_rec.ENG_implementation_cost
3640 , l_ECO_rec.MFG_implementation_cost
3641 , l_ECO_Unexp_rec.requestor_id
3642 , l_ECO_rec.attribute_category
3643 , l_ECO_rec.attribute1
3644 , l_ECO_rec.attribute2
3645 , l_ECO_rec.attribute3
3646 , l_ECO_rec.attribute4
3647 , l_ECO_rec.attribute5
3648 , l_ECO_rec.attribute6
3649 , l_ECO_Unexp_rec.project_id
3650 , l_ECO_Unexp_rec.task_id
3651 , l_ECO_Unexp_rec.change_id
3652 -- , l_ECO_rec.hierarchy_flag
3653 , l_ECO_rec.organization_hierarchy
3654 , l_ECO_Unexp_rec.change_mgmt_type_code -- Eng Change
3655 , l_ECO_Unexp_rec.assignee_id -- Eng Change
3656 , l_ECO_rec.need_by_date -- Eng Chagne
3657 , l_ECO_rec.internal_use_only -- Eng Chagne
3658 , l_ECO_Unexp_rec.source_type_code -- Eng Chagne
3659 , l_ECO_Unexp_rec.source_id -- Eng Chagne
3660 , l_ECO_rec.effort -- Eng Chagne
3661 , l_eco_unexp_rec.hierarchy_id --bug 10108394
3662 FROM ENG_ENGINEERING_CHANGES
3663 WHERE CHANGE_NOTICE = p_change_notice
3664 AND ORGANIZATION_ID = p_organization_id
3665 ;
3666
3667 x_ECO_rec := l_ECO_rec;
3668 x_ECO_Unexp_rec := l_ECO_Unexp_Rec;
3669 x_return_status := Eng_Globals.G_RECORD_FOUND;
3670
3671 EXCEPTION
3672
3673 WHEN NO_DATA_FOUND THEN
3674
3675 x_ECO_rec := l_ECO_rec;
3676 x_ECO_Unexp_rec := l_ECO_Unexp_Rec;
3677 x_return_status := Eng_Globals.G_RECORD_NOT_FOUND;
3678
3679 WHEN OTHERS THEN
3680
3681 x_err_text := G_PKG_NAME ||
3682 ' Utility (ECO Header Query_Row)' ||
3683 SUBSTR(SQLERRM, 1, 100);
3684 x_ECO_rec := l_ECO_rec;
3685 x_ECO_Unexp_rec := l_ECO_Unexp_Rec;
3686 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3687 END Query_Row;
3688
3689
3690 -- Procedure Perform_Approval_Status_Change
3691 -- to centraize business logic for Approval Status change
3692 PROCEDURE Perform_Approval_Status_Change
3693 ( p_change_id IN NUMBER
3694 , p_user_id IN NUMBER := NULL
3695 , p_approval_status_type IN NUMBER
3696 , p_caller_type IN VARCHAR2 := 'OI'
3697 , x_return_status OUT NOCOPY VARCHAR2
3698 , x_err_text OUT NOCOPY VARCHAR2
3699 )
3700 IS
3701
3702 l_user_id NUMBER;
3703 l_login_id NUMBER;
3704 l_request_id NUMBER;
3705
3706 BEGIN
3707
3708 x_return_status := FND_API.G_RET_STS_SUCCESS;
3709
3710 IF p_caller_type = 'WF' THEN
3711
3712 l_user_id := p_user_id ;
3713
3714 ELSE
3715
3716 l_user_id := Eng_Globals.Get_User_Id;
3717 l_login_id := Eng_Globals.Get_Login_Id;
3718 l_request_id := ENG_GLOBALS.Get_request_id;
3719
3720 END IF ;
3721
3722 -- Approve Change
3723 IF p_approval_status_type = 5 THEN
3724
3725 -- Approve ECO/Change Object
3726 UPDATE eng_engineering_changes
3727 SET approval_status_type = p_approval_status_type ,
3728 approval_date = sysdate ,
3729 request_id = l_request_id ,
3730 last_update_date = SYSDATE ,
3731 last_updated_by = l_user_id ,
3732 last_update_login = l_login_id
3733 WHERE change_id = p_change_id ;
3734
3735 -- Set Open Rev Item to Scheduled
3736 UPDATE eng_revised_items
3737 SET status_type = 4 , -- Set Rev Item Status: Scheduled
3738 request_id = l_request_id ,
3739 last_update_date = SYSDATE ,
3740 last_updated_by = l_user_id ,
3741 last_update_login = l_login_id
3742 WHERE change_id = p_change_id
3743 AND status_type = 1; -- Rev Item Status: Open
3744
3745 -- If ECO is Open, Set Status to Scheduled (bug 2307416)
3746 UPDATE eng_engineering_changes
3747 SET status_type = 4 , -- Scheduled
3748 request_id = l_request_id ,
3749 last_update_date = SYSDATE ,
3750 last_updated_by = l_user_id ,
3751 last_update_login = l_login_id
3752 WHERE change_id = p_change_id
3753 AND status_type = 1; -- Open
3754
3755 /* In case we need paticular business logic, put here
3756 -- Reject Change, Processing error or Timeout
3757 ELSIF p_approval_status_type IN (4, 7, 8) THEN
3758
3759
3760 -- Reject ECO/Change Object or set Processing Error
3761 UPDATE eng_engineering_changes
3762 SET approval_status_type = p_approval_status_type ,
3763 approval_date = NULL ,
3764 request_id = l_request_id ,
3765 last_update_date = SYSDATE ,
3766 last_updated_by = l_user_id ,
3767 last_update_login = l_login_id
3768 WHERE change_id = p_change_id ;
3769 */
3770
3771 -- Others
3772 ELSE
3773
3774 -- Update Approval Status
3775 UPDATE eng_engineering_changes
3776 SET approval_status_type = p_approval_status_type ,
3777 approval_date = NULL ,
3778 request_id = l_request_id ,
3779 last_update_date = SYSDATE ,
3780 last_updated_by = l_user_id ,
3781 last_update_login = l_login_id
3782 WHERE change_id = p_change_id ;
3783
3784
3785 END IF ;
3786
3787
3788 EXCEPTION
3789
3790 WHEN OTHERS THEN
3791 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3792 x_err_text := G_PKG_NAME ||
3793 ' Utility (ECO Header Perform_Approval_Status_Change)' ||
3794 SUBSTR(SQLERRM, 1, 100);
3795
3796 END Perform_Approval_Status_Change ;
3797
3798 PROCEDURE submit_ECO
3799 (
3800 p_api_version IN NUMBER --
3801 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
3802 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
3803 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3804 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
3805 ,p_output_dir IN VARCHAR2 --
3806 ,p_debug_filename IN VARCHAR2
3807 ,x_return_status OUT NOCOPY VARCHAR2 --
3808 ,x_msg_count OUT NOCOPY NUMBER --
3809 ,x_msg_data OUT NOCOPY VARCHAR2 --
3810 ,p_change_id IN NUMBER --
3811 )
3812 is
3813 BEGIN
3814
3815 update eng_revised_items
3816 set STATUS_TYPE = 1
3817 where CHANGE_ID = p_change_id;
3818
3819 if (p_commit = FND_API.G_TRUE )
3820 then commit;
3821 end if;
3822
3823 /*
3824 update eng_engineering_changes
3825 set STATUS_TYPE = 1
3826 where CHANGE_ID = p_change_id;
3827 */
3828
3829
3830 END submit_ECO;
3831
3832
3833
3834 -- Procedure lock_Row
3835 --
3836
3837 /*
3838 PROCEDURE Lock_Row
3839 ( x_return_status OUT NOCOPY VARCHAR2
3840 , x_err_text OUT NOCOPY VARCHAR2
3841 , p_ECO_rec IN ENG_Eco_PUB.Eco_Rec_Type
3842 , x_ECO_rec OUT NOCOPY ENG_Eco_PUB.Eco_Rec_Type
3843 )
3844 IS
3845 l_ECO_rec ENG_Eco_PUB.Eco_Rec_Type;
3846 BEGIN
3847
3848 SELECT ATTRIBUTE7
3849 , ATTRIBUTE8
3850 , ATTRIBUTE9
3851 , ATTRIBUTE10
3852 , ATTRIBUTE11
3853 , ATTRIBUTE12
3854 , ATTRIBUTE13
3855 , ATTRIBUTE14
3856 , ATTRIBUTE15
3857 , REQUEST_ID
3858 , PROGRAM_APPLICATION_ID
3859 , PROGRAM_ID
3860 , PROGRAM_UPDATE_DATE
3861 , APPROVAL_STATUS_TYPE
3862 , APPROVAL_DATE
3863 , APPROVAL_LIST_ID
3864 , CHANGE_ORDER_TYPE_ID
3865 , RESPONSIBLE_ORGANIZATION_ID
3866 , APPROVAL_REQUEST_DATE
3867 , CHANGE_NOTICE
3868 , ORGANIZATION_ID
3869 , LAST_UPDATE_DATE
3870 , LAST_UPDATED_BY
3871 , CREATION_DATE
3872 , CREATED_BY
3873 , LAST_UPDATE_LOGIN
3874 , DESCRIPTION
3875 , STATUS_TYPE
3876 , INITIATION_DATE
3877 , IMPLEMENTATION_DATE
3878 , CANCELLATION_DATE
3879 , CANCELLATION_COMMENTS
3880 , PRIORITY_CODE
3881 , REASON_CODE
3882 , ESTIMATED_ENG_COST
3883 , ESTIMATED_MFG_COST
3884 , REQUESTOR_ID
3885 , ATTRIBUTE_CATEGORY
3886 , ATTRIBUTE1
3887 , ATTRIBUTE2
3888 , ATTRIBUTE3
3889 , ATTRIBUTE4
3890 , ATTRIBUTE5
3891 , ATTRIBUTE6
3892 INTO l_ECO_rec.attribute7
3893 , l_ECO_rec.attribute8
3894 , l_ECO_rec.attribute9
3895 , l_ECO_rec.attribute10
3896 , l_ECO_rec.attribute11
3897 , l_ECO_rec.attribute12
3898 , l_ECO_rec.attribute13
3899 , l_ECO_rec.attribute14
3900 , l_ECO_rec.attribute15
3901 , l_ECO_rec.request_id
3902 , l_ECO_rec.program_application_id
3903 , l_ECO_rec.program_id
3904 , l_ECO_rec.program_update_date
3905 , l_ECO_rec.approval_status_type
3906 , l_ECO_rec.approval_date
3907 , l_ECO_rec.approval_list_id
3908 , l_ECO_rec.change_order_type_id
3909 , l_ECO_rec.responsible_org_id
3910 , l_ECO_rec.approval_request_date
3911 , l_ECO_rec.change_notice
3912 , l_ECO_rec.organization_id
3913 , l_ECO_rec.last_update_date
3914 , l_ECO_rec.last_updated_by
3915 , l_ECO_rec.creation_date
3916 , l_ECO_rec.created_by
3917 , l_ECO_rec.last_update_login
3918 , l_ECO_rec.description
3919 , l_ECO_rec.status_type
3920 , l_ECO_rec.initiation_date
3921 , l_ECO_rec.implementation_date
3922 , l_ECO_rec.cancellation_date
3923 , l_ECO_rec.cancellation_comments
3924 , l_ECO_rec.priority_code
3925 , l_ECO_rec.reason_code
3926 , l_ECO_rec.ENG_implementation_cost
3927 , l_ECO_rec.MFG_implementation_cost
3928 , l_ECO_rec.requestor_id
3929 , l_ECO_rec.attribute_category
3930 , l_ECO_rec.attribute1
3931 , l_ECO_rec.attribute2
3932 , l_ECO_rec.attribute3
3933 , l_ECO_rec.attribute4
3934 , l_ECO_rec.attribute5
3935 , l_ECO_rec.attribute6
3936 FROM ENG_ENGINEERING_CHANGES
3937 WHERE CHANGE_NOTICE = p_ECO_rec.change_notice
3938 AND ORGANIZATION_ID = p_ECO_rec.organization_id
3939 FOR UPDATE NOWAIT;
3940
3941 -- Row locked. Compare IN attributes to DB attributes.
3942
3943 IF ( (l_ECO_rec.attribute7 =
3944 p_ECO_rec.attribute7) OR
3945 ((p_ECO_rec.attribute7 = FND_API.G_MISS_CHAR) OR
3946 ( (l_ECO_rec.attribute7 IS NULL) AND
3947 (p_ECO_rec.attribute7 IS NULL))))
3948 AND ( (l_ECO_rec.attribute8 =
3949 p_ECO_rec.attribute8) OR
3950 ((p_ECO_rec.attribute8 = FND_API.G_MISS_CHAR) OR
3951 ( (l_ECO_rec.attribute8 IS NULL) AND
3952 (p_ECO_rec.attribute8 IS NULL))))
3953 AND ( (l_ECO_rec.attribute9 =
3954 p_ECO_rec.attribute9) OR
3955 ((p_ECO_rec.attribute9 = FND_API.G_MISS_CHAR) OR
3956 ( (l_ECO_rec.attribute9 IS NULL) AND
3957 (p_ECO_rec.attribute9 IS NULL))))
3958 AND ( (l_ECO_rec.attribute10 =
3959 p_ECO_rec.attribute10) OR
3960 ((p_ECO_rec.attribute10 = FND_API.G_MISS_CHAR) OR
3961 ( (l_ECO_rec.attribute10 IS NULL) AND
3962 (p_ECO_rec.attribute10 IS NULL))))
3963 AND ( (l_ECO_rec.attribute11 =
3964 p_ECO_rec.attribute11) OR
3965 ((p_ECO_rec.attribute11 = FND_API.G_MISS_CHAR) OR
3966 ( (l_ECO_rec.attribute11 IS NULL) AND
3967 (p_ECO_rec.attribute11 IS NULL))))
3968 AND ( (l_ECO_rec.attribute12 =
3969 p_ECO_rec.attribute12) OR
3970 ((p_ECO_rec.attribute12 = FND_API.G_MISS_CHAR) OR
3971 ( (l_ECO_rec.attribute12 IS NULL) AND
3972 (p_ECO_rec.attribute12 IS NULL))))
3973 AND ( (l_ECO_rec.attribute13 =
3974 p_ECO_rec.attribute13) OR
3975 ((p_ECO_rec.attribute13 = FND_API.G_MISS_CHAR) OR
3976 ( (l_ECO_rec.attribute13 IS NULL) AND
3977 (p_ECO_rec.attribute13 IS NULL))))
3978 AND ( (l_ECO_rec.attribute14 =
3979 p_ECO_rec.attribute14) OR
3980 ((p_ECO_rec.attribute14 = FND_API.G_MISS_CHAR) OR
3981 ( (l_ECO_rec.attribute14 IS NULL) AND
3982 (p_ECO_rec.attribute14 IS NULL))))
3983 AND ( (l_ECO_rec.attribute15 =
3984 p_ECO_rec.attribute15) OR
3985 ((p_ECO_rec.attribute15 = FND_API.G_MISS_CHAR) OR
3986 ( (l_ECO_rec.attribute15 IS NULL) AND
3987 (p_ECO_rec.attribute15 IS NULL))))
3988 AND ( (l_ECO_rec.request_id =
3989 p_ECO_rec.request_id) OR
3990 ((p_ECO_rec.request_id = FND_API.G_MISS_NUM) OR
3991 ( (l_ECO_rec.request_id IS NULL) AND
3992 (p_ECO_rec.request_id IS NULL))))
3993 AND ( (l_ECO_rec.program_application_id =
3994 p_ECO_rec.program_application_id) OR
3995 ((p_ECO_rec.program_application_id = FND_API.G_MISS_NUM) OR
3996 ( (l_ECO_rec.program_application_id IS NULL) AND
3997 (p_ECO_rec.program_application_id IS NULL))))
3998 AND ( (l_ECO_rec.program_id =
3999 p_ECO_rec.program_id) OR
4000 ((p_ECO_rec.program_id = FND_API.G_MISS_NUM) OR
4001 ( (l_ECO_rec.program_id IS NULL) AND
4002 (p_ECO_rec.program_id IS NULL))))
4003 AND ( (l_ECO_rec.program_update_date =
4004 p_ECO_rec.program_update_date) OR
4005 ((p_ECO_rec.program_update_date = FND_API.G_MISS_DATE) OR
4006 ( (l_ECO_rec.program_update_date IS NULL) AND
4007 (p_ECO_rec.program_update_date IS NULL))))
4008 AND ( (l_ECO_rec.approval_status_type =
4009 p_ECO_rec.approval_status_type) OR
4010 ((p_ECO_rec.approval_status_type = FND_API.G_MISS_NUM) OR
4011 ( (l_ECO_rec.approval_status_type IS NULL) AND
4012 (p_ECO_rec.approval_status_type IS NULL))))
4013 AND ( (l_ECO_rec.approval_date =
4014 p_ECO_rec.approval_date) OR
4015 ((p_ECO_rec.approval_date = FND_API.G_MISS_DATE) OR
4016 ( (l_ECO_rec.approval_date IS NULL) AND
4017 (p_ECO_rec.approval_date IS NULL))))
4018 AND ( (l_ECO_rec.approval_list_id =
4019 p_ECO_rec.approval_list_id) OR
4020 ((p_ECO_rec.approval_list_id = FND_API.G_MISS_NUM) OR
4021 ( (l_ECO_rec.approval_list_id IS NULL) AND
4022 (p_ECO_rec.approval_list_id IS NULL))))
4023 AND ( (l_ECO_rec.change_order_type_id =
4024 p_ECO_rec.change_order_type_id) OR
4025 ((p_ECO_rec.change_order_type_id = FND_API.G_MISS_NUM) OR
4026 ( (l_ECO_rec.change_order_type_id IS NULL) AND
4027 (p_ECO_rec.change_order_type_id IS NULL))))
4028 AND ( (l_ECO_rec.responsible_org_id =
4029 p_ECO_rec.responsible_org_id) OR
4030 ((p_ECO_rec.responsible_org_id = FND_API.G_MISS_NUM) OR
4031 ( (l_ECO_rec.responsible_org_id IS NULL) AND
4032 (p_ECO_rec.responsible_org_id IS NULL))))
4033 AND ( (l_ECO_rec.approval_request_date =
4034 p_ECO_rec.approval_request_date) OR
4035 ((p_ECO_rec.approval_request_date = FND_API.G_MISS_DATE) OR
4036 ( (l_ECO_rec.approval_request_date IS NULL) AND
4037 (p_ECO_rec.approval_request_date IS NULL))))
4038 AND ( (l_ECO_rec.change_notice =
4039 p_ECO_rec.change_notice) OR
4040 ((p_ECO_rec.change_notice = FND_API.G_MISS_CHAR) OR
4041 ( (l_ECO_rec.change_notice IS NULL) AND
4042 (p_ECO_rec.change_notice IS NULL))))
4043 AND ( (l_ECO_rec.organization_id =
4044 p_ECO_rec.organization_id) OR
4045 ((p_ECO_rec.organization_id = FND_API.G_MISS_NUM) OR
4046 ( (l_ECO_rec.organization_id IS NULL) AND
4047 (p_ECO_rec.organization_id IS NULL))))
4048 AND ( (l_ECO_rec.last_update_date =
4049 p_ECO_rec.last_update_date) OR
4050 ((p_ECO_rec.last_update_date = FND_API.G_MISS_DATE) OR
4051 ( (l_ECO_rec.last_update_date IS NULL) AND
4052 (p_ECO_rec.last_update_date IS NULL))))
4053 AND ( (l_ECO_rec.last_updated_by =
4054 p_ECO_rec.last_updated_by) OR
4055 ((p_ECO_rec.last_updated_by = FND_API.G_MISS_NUM) OR
4056 ( (l_ECO_rec.last_updated_by IS NULL) AND
4057 (p_ECO_rec.last_updated_by IS NULL))))
4058 AND ( (l_ECO_rec.creation_date =
4059 p_ECO_rec.creation_date) OR
4060 ((p_ECO_rec.creation_date = FND_API.G_MISS_DATE) OR
4061 ( (l_ECO_rec.creation_date IS NULL) AND
4062 (p_ECO_rec.creation_date IS NULL))))
4063 AND ( (l_ECO_rec.created_by =
4064 p_ECO_rec.created_by) OR
4065 ((p_ECO_rec.created_by = FND_API.G_MISS_NUM) OR
4066 ( (l_ECO_rec.created_by IS NULL) AND
4067 (p_ECO_rec.created_by IS NULL))))
4068 AND ( (l_ECO_rec.last_update_login =
4069 p_ECO_rec.last_update_login) OR
4070 ((p_ECO_rec.last_update_login = FND_API.G_MISS_NUM) OR
4071 ( (l_ECO_rec.last_update_login IS NULL) AND
4072 (p_ECO_rec.last_update_login IS NULL))))
4073 AND ( (l_ECO_rec.description =
4074 p_ECO_rec.description) OR
4075 ((p_ECO_rec.description = FND_API.G_MISS_CHAR) OR
4076 ( (l_ECO_rec.description IS NULL) AND
4077 (p_ECO_rec.description IS NULL))))
4078 AND ( (l_ECO_rec.status_type =
4079 p_ECO_rec.status_type) OR
4080 ((p_ECO_rec.status_type = FND_API.G_MISS_NUM) OR
4081 ( (l_ECO_rec.status_type IS NULL) AND
4082 (p_ECO_rec.status_type IS NULL))))
4083 AND ( (l_ECO_rec.initiation_date =
4084 p_ECO_rec.initiation_date) OR
4085 ((p_ECO_rec.initiation_date = FND_API.G_MISS_DATE) OR
4086 ( (l_ECO_rec.initiation_date IS NULL) AND
4087 (p_ECO_rec.initiation_date IS NULL))))
4088 AND ( (l_ECO_rec.implementation_date =
4089 p_ECO_rec.implementation_date) OR
4090 ((p_ECO_rec.implementation_date = FND_API.G_MISS_DATE) OR
4091 ( (l_ECO_rec.implementation_date IS NULL) AND
4092 (p_ECO_rec.implementation_date IS NULL))))
4093 AND ( (l_ECO_rec.cancellation_date =
4094 p_ECO_rec.cancellation_date) OR
4095 ((p_ECO_rec.cancellation_date = FND_API.G_MISS_DATE) OR
4096 ( (l_ECO_rec.cancellation_date IS NULL) AND
4097 (p_ECO_rec.cancellation_date IS NULL))))
4098 AND ( (l_ECO_rec.cancellation_comments =
4099 p_ECO_rec.cancellation_comments) OR
4100 ((p_ECO_rec.cancellation_comments = FND_API.G_MISS_CHAR) OR
4101 ( (l_ECO_rec.cancellation_comments IS NULL) AND
4102 (p_ECO_rec.cancellation_comments IS NULL))))
4103 AND ( (l_ECO_rec.priority_code =
4104 p_ECO_rec.priority_code) OR
4105 ((p_ECO_rec.priority_code = FND_API.G_MISS_CHAR) OR
4106 ( (l_ECO_rec.priority_code IS NULL) AND
4107 (p_ECO_rec.priority_code IS NULL))))
4108 AND ( (l_ECO_rec.reason_code =
4109 p_ECO_rec.reason_code) OR
4110 ((p_ECO_rec.reason_code = FND_API.G_MISS_CHAR) OR
4111 ( (l_ECO_rec.reason_code IS NULL) AND
4112 (p_ECO_rec.reason_code IS NULL))))
4113 AND ( (l_ECO_rec.estimated_eng_cost =
4114 p_ECO_rec.estimated_eng_cost) OR
4115 ((p_ECO_rec.estimated_eng_cost = FND_API.G_MISS_NUM) OR
4116 ( (l_ECO_rec.estimated_eng_cost IS NULL) AND
4117 (p_ECO_rec.estimated_eng_cost IS NULL))))
4118 AND ( (l_ECO_rec.estimated_mfg_cost =
4119 p_ECO_rec.estimated_mfg_cost) OR
4120 ((p_ECO_rec.estimated_mfg_cost = FND_API.G_MISS_NUM) OR
4121 ( (l_ECO_rec.estimated_mfg_cost IS NULL) AND
4122 (p_ECO_rec.estimated_mfg_cost IS NULL))))
4123 AND ( (l_ECO_rec.requestor_id =
4124 p_ECO_rec.requestor_id) OR
4125 ((p_ECO_rec.requestor_id = FND_API.G_MISS_NUM) OR
4126 ( (l_ECO_rec.requestor_id IS NULL) AND
4127 (p_ECO_rec.requestor_id IS NULL))))
4128 AND ( (l_ECO_rec.attribute_category =
4129 p_ECO_rec.attribute_category) OR
4130 ((p_ECO_rec.attribute_category = FND_API.G_MISS_CHAR) OR
4131 ( (l_ECO_rec.attribute_category IS NULL) AND
4132 (p_ECO_rec.attribute_category IS NULL))))
4133 AND ( (l_ECO_rec.attribute1 =
4134 p_ECO_rec.attribute1) OR
4135 ((p_ECO_rec.attribute1 = FND_API.G_MISS_CHAR) OR
4136 ( (l_ECO_rec.attribute1 IS NULL) AND
4137 (p_ECO_rec.attribute1 IS NULL))))
4138 AND ( (l_ECO_rec.attribute2 =
4139 p_ECO_rec.attribute2) OR
4140 ((p_ECO_rec.attribute2 = FND_API.G_MISS_CHAR) OR
4141 ( (l_ECO_rec.attribute2 IS NULL) AND
4142 (p_ECO_rec.attribute2 IS NULL))))
4143 AND ( (l_ECO_rec.attribute3 =
4144 p_ECO_rec.attribute3) OR
4145 ((p_ECO_rec.attribute3 = FND_API.G_MISS_CHAR) OR
4146 ( (l_ECO_rec.attribute3 IS NULL) AND
4147 (p_ECO_rec.attribute3 IS NULL))))
4148 AND ( (l_ECO_rec.attribute4 =
4149 p_ECO_rec.attribute4) OR
4150 ((p_ECO_rec.attribute4 = FND_API.G_MISS_CHAR) OR
4151 ( (l_ECO_rec.attribute4 IS NULL) AND
4152 (p_ECO_rec.attribute4 IS NULL))))
4153 AND ( (l_ECO_rec.attribute5 =
4154 p_ECO_rec.attribute5) OR
4155 ((p_ECO_rec.attribute5 = FND_API.G_MISS_CHAR) OR
4156 ( (l_ECO_rec.attribute5 IS NULL) AND
4157 (p_ECO_rec.attribute5 IS NULL))))
4158 AND ( (l_ECO_rec.attribute6 =
4159 p_ECO_rec.attribute6) OR
4160 ((p_ECO_rec.attribute6 = FND_API.G_MISS_CHAR) OR
4161 ( (l_ECO_rec.attribute6 IS NULL) AND
4162 (p_ECO_rec.attribute6 IS NULL))))
4163 THEN
4164
4165 -- Row has not changed. Set out parameter.
4166
4167 x_ECO_rec := l_ECO_rec;
4168
4169 -- Set return status
4170
4171 x_return_status := FND_API.G_RET_STS_SUCCESS;
4172 x_ECO_rec.return_status := FND_API.G_RET_STS_SUCCESS;
4173
4174 ELSE
4175
4176 -- Row has changed by another user.
4177
4178 x_return_status := FND_API.G_RET_STS_ERROR;
4179 x_ECO_rec.return_status := FND_API.G_RET_STS_ERROR;
4180
4181 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4182 THEN
4183
4184 FND_MESSAGE.SET_NAME('ENG','OE_LOCK_ROW_CHANGED');
4185 FND_MSG_PUB.Add;
4186
4187 END IF;
4188
4189 END IF;
4190
4191 EXCEPTION
4192
4193 WHEN NO_DATA_FOUND THEN
4194
4195 x_return_status := FND_API.G_RET_STS_ERROR;
4196 x_ECO_rec.return_status := FND_API.G_RET_STS_ERROR;
4197
4198 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4199 THEN
4200 Error_Handler.Log_Error ( p_who_rec => ENG_GLOBALS.G_WHO_REC
4201 , p_msg_name => 'OE_LOCK_ROW_DELETED'
4202 , x_err_text => x_err_text );
4203 END IF;
4204
4205 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
4206
4207 x_return_status := FND_API.G_RET_STS_ERROR;
4208 x_ECO_rec.return_status := FND_API.G_RET_STS_ERROR;
4209
4210 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4211 THEN
4212 Error_Handler.Log_Error( p_who_rec => ENG_GLOBALS.G_WHO_REC
4213 , p_msg_name => 'OE_LOCK_ROW_ALREADY_LOCKED'
4214 , x_err_text => x_err_text );
4215 END IF;
4216
4217 WHEN OTHERS THEN
4218
4219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4220 x_ECO_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4221
4222 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4223 THEN
4224 x_err_text := G_PKG_NAME || '(Lock Row) - ECO Header' || substrb(SQLERRM,1,60);
4225 END IF;
4226
4227 END Lock_Row;
4228
4229 */
4230
4231
4232
4233
4234
4235 PROCEDURE Change_Subjects
4236 ( p_eco_rec IN Eng_Eco_Pub.Eco_Rec_Type
4237 , p_ECO_Unexp_Rec IN Eng_Eco_Pub.Eco_Unexposed_Rec_Type
4238 , x_change_subject_unexp_rec IN OUT NOCOPY Eng_Eco_Pub.Change_Subject_Unexp_Rec_Type
4239 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type -- bug 3572721
4240 , x_return_status IN OUT NOCOPY VARCHAR2)
4241 IS
4242
4243 cursor Getsubject (p_change_type_id in NUMBER) is
4244
4245 select ect.type_name ,ect.subject_id ,ese.entity_name ,ese.parent_entity_name from
4246 eng_change_order_types_vl ect ,eng_subject_entities ese
4247 where ect.subject_id =ese.subject_id
4248 and change_order_type_id =p_change_type_id
4249 and subject_level=1 ;
4250
4251
4252
4253
4254 /*cursor getlifecycleid (item_id NUMBER ,revision VARCHAR2 , l_org_id NUMBER) is
4255 SELECT LP.PROJ_ELEMENT_ID -- into l_current_lifecycle_id
4256 FROM PA_EGO_LIFECYCLES_PHASES_V LP, MTL_ITEM_REVISIONS MIR
4257 WHERE LP.PROJ_ELEMENT_ID = MIR.CURRENT_PHASE_ID
4258 AND MIR.INVENTORY_ITEM_ID = item_id
4259 AND MIR.ORGANIZATION_ID = l_org_id
4260 AND MIR.REVISION = revision; */ -- Commented By LKASTURI
4261
4262
4263 cursor getcataloggroupid(item_id NUMBER, l_org_id NUMBER) is
4264 SELECT ITEM_CATALOG_GROUP_ID
4265 from mtl_system_items msi
4266 where msi.INVENTORY_ITEM_ID = item_id
4267 AND msi.ORGANIZATION_ID = l_org_id;
4268
4269
4270
4271 subject_type Getsubject%ROWTYPE;
4272 l_entity_name VARCHAR2(30);
4273 l_parent_entity_name VARCHAR2(30);
4274 l_subject_id NUMBER;
4275 l_change_subject_unexp_rec Eng_Eco_Pub.Change_Subject_Unexp_Rec_Type;
4276
4277 l_user_id NUMBER;
4278 l_login_id NUMBER;
4279 l_prog_appid NUMBER;
4280 l_prog_id NUMBER;
4281 l_request_id NUMBER;
4282 l_return_status VARCHAR2(1);
4283 l_org_id NUMBER;
4284 l_rev_id NUMBER;
4285 l_inv_item_id NUMBER;
4286 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
4287 l_Token_Tbl Error_Handler.Token_Tbl_Type;
4288 l_err_text VARCHAR2(2000);
4289 l_sub_id NUMBER;
4290 l_item_catalog_group_id NUMBER;
4291 BEGIN
4292
4293 l_return_status := FND_API.G_RET_STS_SUCCESS;
4294
4295 l_user_id := Eng_Globals.Get_User_Id;
4296 l_login_id := Eng_Globals.Get_Login_Id;
4297 l_request_id := ENG_GLOBALS.Get_request_id;
4298 l_prog_appid := ENG_GLOBALS.Get_prog_appid;
4299 l_prog_id := ENG_GLOBALS.Get_prog_id;
4300
4301 OPEN Getsubject (p_ECO_Unexp_Rec. Change_Order_Type_Id);
4302 FETCH Getsubject INTO subject_type;
4303 CLOSE Getsubject;
4304 l_entity_name := subject_type.entity_name;
4305 l_parent_entity_name := subject_type.parent_entity_name;
4306 l_subject_id := subject_type.subject_id;
4307 l_change_subject_unexp_rec.change_id := p_ECO_Unexp_Rec.change_id;
4308 l_change_subject_unexp_rec.ENTITY_NAME := l_entity_name;
4309 l_change_subject_unexp_rec.subject_level := 1;
4310
4311 l_org_id := p_ECO_Unexp_Rec.organization_id; -- Added for bug 3651713
4312
4313 IF (l_entity_name = 'EGO_ITEM_REVISION') THEN
4314 IF p_eco_rec.pk1_name IS NOT NULL
4315 --AND p_eco_rec.pk2_name IS NOT NULL
4316 --AND p_eco_rec.pk3_name IS NOT NULL
4317 THEN
4318 --l_org_id := ENG_Val_To_Id.Organization(p_eco_rec.pk2_name, l_err_text);
4319 l_change_subject_unexp_rec.pk2_value := l_org_id;
4320 IF (l_org_id IS NOT NULL AND l_org_id <> fnd_api.g_miss_num) THEN
4321 l_inv_item_id := ENG_Val_To_Id.revised_item (p_eco_rec.pk1_name,
4322 l_org_id,
4323 l_err_text);
4324 l_change_subject_unexp_rec.pk1_value := l_inv_item_id;
4325 IF l_inv_item_id IS NOT NULL
4326 AND l_inv_item_id <> fnd_api.g_miss_num
4327 THEN
4328 IF p_eco_rec.pk3_name IS NOT NULL -- bug 3572721 If the inventory_item_id and org_id is not null, then validate pk3value
4329 THEN
4330 l_rev_id := ENG_Val_To_Id.revised_item_code (l_inv_item_id,
4331 l_org_id,
4332 p_eco_rec.pk3_name);
4333 l_change_subject_unexp_rec.pk3_value := l_rev_id;
4334 IF (l_rev_id IS NOT NULL AND l_rev_id <> fnd_api.g_miss_num)
4335 THEN
4336 l_return_status := 'S'; --FND_API.G_RET_STS_SUCCESS;
4337 ELSE
4338 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE'; --token added for bug 3572721
4339 l_token_tbl (1).token_value := p_eco_rec.change_type_code;
4340 Error_Handler.add_error_token (
4341 p_message_name=> 'ENG_PK3_NAME_INVALID',
4342 p_mesg_token_tbl=> l_mesg_token_tbl,
4343 x_mesg_token_tbl=> l_mesg_token_tbl,
4344 p_token_tbl=> l_token_tbl
4345 );
4346 l_return_status := FND_API.G_RET_STS_ERROR;
4347 END IF; --end of l_rev_id IS NOT NULL
4348 END IF; -- end of pk3_name is not null
4349 ELSE
4350 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4351 l_token_tbl (1).token_value := p_eco_rec.change_type_code;
4352 Error_Handler.add_error_token (
4353 p_message_name=> 'ENG_PK1_NAME_INVALID',
4354 p_mesg_token_tbl=> l_mesg_token_tbl,
4355 x_mesg_token_tbl=> l_mesg_token_tbl,
4356 p_token_tbl=> l_token_tbl
4357 );
4358 l_return_status := FND_API.G_RET_STS_ERROR;
4359 END IF; -- l_inv_item_id IS NOT NULL
4360 ELSE
4361 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4362 l_token_tbl (1).token_value := p_eco_rec.change_type_code;
4363 Error_Handler.add_error_token (
4364 p_message_name=> 'ENG_PK2_NAME_INVALID',
4365 p_mesg_token_tbl=> l_mesg_token_tbl,
4366 x_mesg_token_tbl=> l_mesg_token_tbl,
4367 p_token_tbl=> l_token_tbl
4368 );
4369 l_return_status := FND_API.G_RET_STS_ERROR;
4370 END IF; --l_org_id IS NOT NULL
4371 ELSE
4372 -- Commented error handling code as pk values are not mandatory
4373 l_change_subject_unexp_rec.pk2_value := NULL; --org_id;
4374 l_change_subject_unexp_rec.pk1_value := NULL; --inv_item_id;
4375 l_change_subject_unexp_rec.pk3_value := NULL; --rev_id;
4376 /* IF p_eco_rec.pk1_name IS NULL
4377 OR p_eco_rec.pk1_name = fnd_api.g_miss_char THEN
4378 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4379 l_token_tbl (1).token_value := p_eco_rec.change_type_code;
4380 Error_Handler.add_error_token (
4381 p_message_name=> 'ENG_PK1_NAME_INVALID',
4382 p_mesg_token_tbl=> l_mesg_token_tbl,
4383 x_mesg_token_tbl=> l_mesg_token_tbl,
4384 p_token_tbl=> l_token_tbl
4385 );
4386 l_return_status := FND_API.G_RET_STS_ERROR;
4387 END IF;
4388 IF p_eco_rec.pk3_name IS NULL
4389 OR p_eco_rec.pk3_name = fnd_api.g_miss_char THEN
4390 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4391 l_token_tbl (1).token_value := p_eco_rec.change_type_code;
4392 Error_Handler.add_error_token (
4393 p_message_name=> 'ENG_PK3_NAME_INVALID',
4394 p_mesg_token_tbl=> l_mesg_token_tbl,
4395 x_mesg_token_tbl=> l_mesg_token_tbl,
4396 p_token_tbl=> l_token_tbl
4397 );
4398 l_return_status := FND_API.G_RET_STS_ERROR;
4399 END IF;
4400 IF p_eco_rec.pk2_name IS NULL
4401 OR p_eco_rec.pk2_name = fnd_api.g_miss_char THEN
4402 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4403 l_token_tbl (1).token_value := p_eco_rec.change_type_code;
4404 Error_Handler.add_error_token (
4405 p_message_name=> 'ENG_PK2_NAME_INVALID',
4406 p_mesg_token_tbl=> l_mesg_token_tbl,
4407 x_mesg_token_tbl=> l_mesg_token_tbl,
4408 p_token_tbl=> l_token_tbl
4409 );
4410 l_return_status := FND_API.G_RET_STS_ERROR;
4411 END IF;*/
4412
4413 END IF; -- p_eco_rec.Pk1_Name is not null
4414 ELSIF l_entity_name = 'EGO_ITEM' THEN
4415 --For Item and Catalog Category PK1_NAME,PK2_NAME Columns are mandatory
4416 IF p_eco_rec.pk1_name IS NOT NULL
4417 -- AND p_eco_rec.pk2_name IS NOT NULL
4418 THEN
4419 --l_org_id := ENG_Val_To_Id.ORGANIZATION (p_eco_rec.pk2_name, l_err_text);
4420 l_change_subject_unexp_rec.pk2_value := l_org_id;
4421 IF (l_org_id IS NOT NULL AND l_org_id <> fnd_api.g_miss_num) THEN
4422 l_rev_id := ENG_Val_To_Id.revised_item (p_eco_rec.pk1_name,
4423 l_org_id,
4424 l_err_text);
4425 l_change_subject_unexp_rec.pk1_value := l_rev_id;
4426 IF (l_rev_id IS NOT NULL AND l_rev_id <> fnd_api.g_miss_num) THEN
4427 l_return_status := 'S';
4428 ELSE
4429 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4430 l_token_tbl (1).token_value := p_eco_rec.change_type_code;
4431 Error_Handler.add_error_token (
4432 p_message_name=> 'ENG_PK1_NAME_INVALID',
4433 p_mesg_token_tbl=> l_mesg_token_tbl,
4434 x_mesg_token_tbl=> l_mesg_token_tbl,
4435 p_token_tbl=> l_token_tbl
4436 );
4437 l_return_status := FND_API.G_RET_STS_ERROR;
4438 END IF; --l_rev_id IS NOT NULL
4439 ELSE
4440 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4441 l_token_tbl (1).token_value := p_eco_rec.change_type_code;
4442 Error_Handler.add_error_token (
4443 p_message_name=> 'ENG_PK2_NAME_INVALID',
4444 p_mesg_token_tbl=> l_mesg_token_tbl,
4445 x_mesg_token_tbl=> l_mesg_token_tbl,
4446 p_token_tbl=> l_token_tbl
4447 );
4448 l_return_status := FND_API.G_RET_STS_ERROR;
4449 END IF; --l_org_id IS NOT NULL
4450 ELSE
4451 l_change_subject_unexp_rec.pk1_value := NULL;
4452 l_change_subject_unexp_rec.pk2_value := NULL;
4453 -- Commented out code as pk values are not mandatory
4454 /* IF p_eco_rec.pk1_name IS NULL
4455 OR p_eco_rec.pk1_name = fnd_api.g_miss_char THEN
4456 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4457 l_token_tbl (1).token_value := p_eco_rec.change_type_code;
4458 Error_Handler.add_error_token (
4459 p_message_name=> 'ENG_PK1_NAME_INVALID',
4460 p_mesg_token_tbl=> l_mesg_token_tbl,
4461 x_mesg_token_tbl=> l_mesg_token_tbl,
4462 p_token_tbl=> l_token_tbl
4463 );
4464 l_return_status := FND_API.G_RET_STS_ERROR;
4465 END IF;
4466
4467 IF p_eco_rec.pk2_name IS NULL
4468 OR p_eco_rec.pk2_name = fnd_api.g_miss_char THEN
4469 l_token_tbl (1).token_name := 'CHANGE_LINE_TYPE';
4470 l_token_tbl (1).token_value := p_eco_rec.change_type_code;
4471 Error_Handler.add_error_token (
4472 p_message_name=> 'ENG_PK3_NAME_INVALID',
4473 p_mesg_token_tbl=> l_mesg_token_tbl,
4474 x_mesg_token_tbl=> l_mesg_token_tbl,
4475 p_token_tbl=> l_token_tbl
4476 );
4477 l_return_status := FND_API.G_RET_STS_ERROR;
4478 END IF;*/
4479 END IF; -- p_eco_rec.Pk1_Name is not null
4480 END IF; --End Of If of check for l_entity_name
4481
4482 IF l_return_status= 'S' THEN
4483 /*OPEN getlifecycleid(l_change_subject_unexp_rec.pk1_value,
4484 p_eco_rec.pk1_name,
4485 l_change_subject_unexp_rec.pk2_value);
4486 FETCH getlifecycleid into l_change_subject_unexp_rec.lifecycle_state_id;*/ -- Commented By LKASTURI
4487 --
4488 -- Bug 3311072: Change the query to select item phase
4489 -- Added By LKASTURI
4490 --
4491 -- Exception handling added to take care of null pk values
4492 -- Also assuming that pk1 value will be inventory item id if not null
4493 -- and pk2 value organization id is not null
4494 IF (l_change_subject_unexp_rec.pk1_value IS NOT NULL AND
4495 l_change_subject_unexp_rec.pk2_value IS NOT NULL)
4496 THEN
4497 BEGIN
4498 SELECT CURRENT_PHASE_ID
4499 INTO l_change_subject_unexp_rec.lifecycle_state_id
4500 FROM MTL_System_items_vl
4501 WHERE INVENTORY_ITEM_ID = l_change_subject_unexp_rec.pk1_value
4502 AND ORGANIZATION_ID = l_change_subject_unexp_rec.pk2_value;
4503 EXCEPTION
4504 WHEN NO_DATA_FOUND THEN
4505 l_change_subject_unexp_rec.lifecycle_state_id := null;
4506 WHEN TOO_MANY_ROWS THEN
4507 l_change_subject_unexp_rec.lifecycle_state_id := null;
4508 END;
4509 ELSE
4510 l_change_subject_unexp_rec.lifecycle_state_id := null;
4511 END IF;
4512 -- End Changes
4513
4514 IF p_eco_rec.transaction_type = Eng_Globals.G_OPR_CREATE THEN
4515 SELECT eng_change_subjects_s.nextval INTO l_change_subject_unexp_rec.change_subject_id
4516 FROM SYS.DUAL;
4517
4518
4519
4520 Insert into eng_change_subjects
4521 (CHANGE_SUBJECT_ID,
4522 CHANGE_ID,
4523 CHANGE_LINE_ID,
4524 ENTITY_NAME,
4525 PK1_VALUE,
4526 PK2_VALUE,
4527 PK3_VALUE,
4528 PK4_VALUE,
4529 PK5_VALUE,
4530 SUBJECT_LEVEL,
4531 LIFECYCLE_STATE_ID,
4532 LAST_UPDATE_DATE,
4533 LAST_UPDATED_BY,
4534 CREATION_DATE,
4535 CREATED_BY,
4536 LAST_UPDATE_LOGIN,
4537 REQUEST_ID,
4538 PROGRAM_ID,
4539 PROGRAM_APPLICATION_ID,
4540 PROGRAM_UPDATE_DATE)
4541 values
4542 (l_change_subject_unexp_rec.change_subject_id,
4543 l_change_subject_unexp_rec.change_id,
4544 l_change_subject_unexp_rec.change_line_id,
4545 l_change_subject_unexp_rec.entity_name,
4546 l_change_subject_unexp_rec.pk1_value,
4547 l_change_subject_unexp_rec.pk2_value,
4548 l_change_subject_unexp_rec.pk3_value,
4549 l_change_subject_unexp_rec.pk4_value,
4550 l_change_subject_unexp_rec.pk5_value,
4551 l_change_subject_unexp_rec.subject_level,
4552 l_change_subject_unexp_rec.lifecycle_state_id,
4553 SYSDATE,
4554 l_User_Id,
4555 SYSDATE,
4556 l_User_Id,
4557 l_Login_Id,
4558 l_request_id,
4559 l_prog_id,
4560 l_prog_appid,
4561 SYSDATE) returning CHANGE_SUBJECT_ID into l_sub_id;
4562
4563
4564
4565 IF l_parent_entity_name = 'EGO_ITEM' THEN
4566 Insert into eng_change_subjects
4567 (CHANGE_SUBJECT_ID,
4568 CHANGE_ID,
4569 CHANGE_LINE_ID,
4570 ENTITY_NAME,
4571 PK1_VALUE,
4572 PK2_VALUE,
4573 PK3_VALUE,
4574 PK4_VALUE,
4575 PK5_VALUE,
4576 SUBJECT_LEVEL,
4577 LIFECYCLE_STATE_ID,
4578 LAST_UPDATE_DATE,
4579 LAST_UPDATED_BY,
4580 CREATION_DATE,
4581 CREATED_BY,
4582 LAST_UPDATE_LOGIN,
4583 REQUEST_ID,
4584 PROGRAM_ID,
4585 PROGRAM_APPLICATION_ID,
4586 PROGRAM_UPDATE_DATE)
4587 values
4588 (eng_change_subjects_s.nextval,
4589 l_change_subject_unexp_rec.change_id,
4590 null,
4591 l_parent_entity_name, -- bug 3572698
4592 l_change_subject_unexp_rec.pk1_value,
4593 l_change_subject_unexp_rec.pk2_value,
4594 null,
4595 null,
4596 null,
4597 2,
4598 null,
4599 SYSDATE,
4600 l_User_Id,
4601 SYSDATE,
4602 l_User_Id,
4603 l_Login_Id,
4604 l_request_id,
4605 l_prog_appid,
4606 l_prog_id,sysdate);
4607 elsif l_parent_entity_name = 'EGO_CATALOG_GROUP' THEN
4608 OPEN getcataloggroupid(l_change_subject_unexp_rec.pk1_value,
4609 l_change_subject_unexp_rec.pk2_value);
4610 FETCH getcataloggroupid into l_item_catalog_group_id;
4611 Insert into eng_change_subjects
4612 (CHANGE_SUBJECT_ID,
4613 CHANGE_ID,
4614 CHANGE_LINE_ID,
4615 ENTITY_NAME,
4616 PK1_VALUE,
4617 PK2_VALUE,
4618 PK3_VALUE,
4619 PK4_VALUE,
4620 PK5_VALUE,
4621 SUBJECT_LEVEL,
4622 LIFECYCLE_STATE_ID,
4623 LAST_UPDATE_DATE,
4624 LAST_UPDATED_BY,
4625 CREATION_DATE,
4626 CREATED_BY,
4627 LAST_UPDATE_LOGIN,
4628 REQUEST_ID,
4629 PROGRAM_ID,
4630 PROGRAM_APPLICATION_ID,
4631 PROGRAM_UPDATE_DATE)
4632 values
4633 (eng_change_subjects_s.nextval,
4634 l_change_subject_unexp_rec.change_id,
4635 null,
4636 l_parent_entity_name, -- bug 3572698
4637 l_item_catalog_group_id,
4638 null,
4639 null,
4640 null,
4641 null,
4642 2,
4643 null,
4644 SYSDATE,
4645 l_User_Id,
4646 SYSDATE,
4647 l_User_Id,
4648 l_Login_Id,
4649 l_request_id,
4650 l_prog_appid,
4651 l_prog_id,sysdate);
4652 END IF;
4653 ELSIF p_eco_rec.transaction_type = Eng_Globals.G_OPR_UPDATE THEN
4654 UPDATE eng_change_subjects SET
4655 pk1_value = l_change_subject_unexp_rec.pk1_value,
4656 pk2_value = l_change_subject_unexp_rec.pk2_value,
4657 pk3_value = l_change_subject_unexp_rec.pk3_value
4658 WHERE change_id = l_change_subject_unexp_rec.change_id
4659 AND subject_level = 1
4660 AND change_line_id is null;
4661
4662 IF l_parent_entity_name = 'EGO_ITEM' THEN
4663 UPDATE eng_change_subjects SET
4664 pk1_value = l_change_subject_unexp_rec.pk1_value,
4665 pk2_value = l_change_subject_unexp_rec.pk2_value
4666 WHERE change_id = l_change_subject_unexp_rec.change_id
4667 AND subject_level = 2
4668 AND change_line_id is null;
4669 ELSIF
4670 l_parent_entity_name = 'EGO_CATALOG_GROUP' THEN
4671 OPEN getcataloggroupid(l_change_subject_unexp_rec.pk1_value,
4672 l_change_subject_unexp_rec.pk2_value);
4673 FETCH getcataloggroupid into l_item_catalog_group_id;
4674 UPDATE eng_change_subjects SET
4675 pk1_value = l_item_catalog_group_id
4676 WHERE change_id = l_change_subject_unexp_rec.change_id
4677 AND subject_level = 2
4678 AND change_line_id is null;
4679 END IF;
4680 ELSE
4681 DELETE FROM eng_change_subjects
4682 WHERE change_line_id is null
4683 AND change_id = p_ECO_Unexp_Rec.change_id;
4684 END IF; -- if CREATE
4685
4686
4687 END IF; -- if return status is 'S'
4688
4689 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl; -- bug 3572721
4690 x_return_status := l_return_status;
4691
4692 END Change_Subjects;
4693
4694
4695 -- procedure to delete all changeheader related rows
4696 PROCEDURE delete_ECO
4697 (
4698 p_api_version IN NUMBER --
4699 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
4700 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
4701 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
4702 ,x_return_status OUT NOCOPY VARCHAR2 --
4703 ,x_msg_count OUT NOCOPY NUMBER --
4704 ,x_msg_data OUT NOCOPY VARCHAR2 --
4705 ,p_change_id IN NUMBER -- header's change_id
4706 ,p_api_caller IN VARCHAR2 := 'UI'
4707 )
4708 IS
4709 l_api_name CONSTANT VARCHAR2(30) := 'delete_ECO';
4710 l_api_version CONSTANT NUMBER := 1.0;
4711
4712 l_return_status VARCHAR2(1);
4713 l_msg_count NUMBER;
4714 l_msg_data VARCHAR2(2000);
4715
4716
4717
4718 l_pls_block VARCHAR2(5000);
4719 BEGIN
4720 -- Standard Start of API savepoint
4721 -- Standard Start of API savepoint
4722 SAVEPOINT Init_Lifecycle;
4723 -- Standard call to check for call compatibility
4724 IF NOT FND_API.Compatible_API_Call ( l_api_version
4725 ,p_api_version
4726 ,l_api_name
4727 ,G_PKG_NAME )
4728 THEN
4729 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4730 END IF;
4731 -- Initialize message list if p_init_msg_list is set to TRUE.
4732 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4733 FND_MSG_PUB.initialize;
4734 END IF ;
4735
4736 -- Initialize API return status to success
4737 x_return_status := FND_API.G_RET_STS_SUCCESS;
4738
4739
4740 -- Real code starts here -----------------------------------------------
4741 DELETE FROM ENG_CHANGE_ROUTE_ASSOCS
4742 WHERE ROUTE_PEOPLE_ID in
4743 ( select ecrp.ROUTE_PEOPLE_ID
4744 from ENG_CHANGE_ROUTE_PEOPLE ecrp , ENG_CHANGE_ROUTE_STEPS ecrs , ENG_LIFECYCLE_STATUSES els
4745 where ecrp.STEP_ID = ecrs.step_id
4746 AND ecrs.ROUTE_ID = els.CHANGE_WF_ROUTE_ID
4747 and els.ENTITY_ID1 = p_change_id
4748 and els.ENTITY_NAME = 'ENG_CHANGE'
4749 );
4750 --Fixed for bug 4958931
4751 /*( select ROUTE_PEOPLE_ID
4752 from ENG_CHANGE_ROUTE_PEOPLE
4753 where STEP_ID in
4754 ( select STEP_ID
4755 from ENG_CHANGE_ROUTE_STEPS
4756 where ROUTE_ID in
4757 ( select CHANGE_WF_ROUTE_ID
4758 from ENG_LIFECYCLE_STATUSES
4759 where ENTITY_ID1 = p_change_id
4760 and ENTITY_NAME = 'ENG_CHANGE'
4761 )
4762 )
4763 );*/
4764
4765 delete from ENG_CHANGE_ROUTE_PEOPLE_tl
4766 where ROUTE_PEOPLE_ID in
4767 ( select ecrp.ROUTE_PEOPLE_ID
4768 from ENG_CHANGE_ROUTE_PEOPLE ecrp , ENG_CHANGE_ROUTE_STEPS ecrs , ENG_LIFECYCLE_STATUSES els
4769 where ecrp.STEP_ID = ecrs.step_id
4770 AND ecrs.ROUTE_ID = els.CHANGE_WF_ROUTE_ID
4771 and els.ENTITY_ID1 = p_change_id
4772 and els.ENTITY_NAME = 'ENG_CHANGE'
4773 );
4774 --Fixed for bug 4958931
4775 /*( select ROUTE_PEOPLE_ID
4776 from ENG_CHANGE_ROUTE_PEOPLE
4777 where STEP_ID in
4778 ( select STEP_ID
4779 from ENG_CHANGE_ROUTE_STEPS
4780 where ROUTE_ID in
4781 ( select CHANGE_WF_ROUTE_ID
4782 from ENG_LIFECYCLE_STATUSES
4783 where ENTITY_ID1 = p_change_id
4784 and ENTITY_NAME = 'ENG_CHANGE'
4785 )
4786 )
4787 );*/
4788
4789
4790
4791 delete from ENG_CHANGE_ROUTE_PEOPLE
4792 where STEP_ID in
4793 ( select STEP_ID
4794 from ENG_CHANGE_ROUTE_STEPS
4795 where ROUTE_ID in
4796 ( select CHANGE_WF_ROUTE_ID
4797 from ENG_LIFECYCLE_STATUSES
4798 where ENTITY_ID1 = p_change_id
4799 and ENTITY_NAME = 'ENG_CHANGE'
4800 )
4801 );
4802
4803
4804 delete from ENG_CHANGE_ROUTE_STEPS_TL
4805 where STEP_ID in
4806 ( select STEP_ID
4807 from ENG_CHANGE_ROUTE_STEPS
4808 where ROUTE_ID in
4809 ( select CHANGE_WF_ROUTE_ID
4810 from ENG_LIFECYCLE_STATUSES
4811 where ENTITY_ID1 = p_change_id
4812 and ENTITY_NAME = 'ENG_CHANGE'
4813 )
4814 );
4815
4816 delete from ENG_CHANGE_ROUTE_STEPS
4817 where ROUTE_ID in
4818 ( select CHANGE_WF_ROUTE_ID
4819 from ENG_LIFECYCLE_STATUSES
4820 where ENTITY_ID1 = p_change_id
4821 and ENTITY_NAME = 'ENG_CHANGE'
4822 );
4823
4824
4825
4826 delete from ENG_CHANGE_ROUTES_tl
4827 where ROUTE_ID in
4828 ( select CHANGE_WF_ROUTE_ID
4829 from ENG_LIFECYCLE_STATUSES
4830 where ENTITY_ID1 = p_change_id
4831 and ENTITY_NAME = 'ENG_CHANGE'
4832 );
4833
4834
4835 delete from ENG_CHANGE_ROUTES
4836 where ROUTE_ID in
4837 ( select CHANGE_WF_ROUTE_ID
4838 from ENG_LIFECYCLE_STATUSES
4839 where ENTITY_ID1 = p_change_id
4840 and ENTITY_NAME = 'ENG_CHANGE' );
4841
4842
4843 delete from ENG_LIFECYCLE_STATUSES
4844 where ENTITY_ID1 = p_change_id
4845 and ENTITY_NAME = 'ENG_CHANGE' ;
4846
4847 delete from ENG_LIFECYCLE_STATUSES
4848 where ENTITY_ID1 = p_change_id
4849 and ENTITY_NAME = 'ENG_CHANGE' ;
4850
4851
4852 delete from eng_revised_items
4853 where change_id = p_change_id ;
4854
4855
4856 delete from eng_engineering_changes
4857 where change_id = p_change_id ;
4858
4859 IF FND_API.To_Boolean ( p_commit ) THEN
4860 COMMIT WORK;
4861 END IF;
4862
4863 EXCEPTION
4864 WHEN FND_API.G_EXC_ERROR THEN
4865 --ROLLBACK TO Init_Lifecycle;
4866 x_return_status := FND_API.G_RET_STS_ERROR;
4867 FND_MSG_PUB.Count_And_Get
4868 ( p_count => x_msg_count
4869 ,p_data => x_msg_data );
4870 --IF g_debug_flag THEN
4871 -- Write_Debug('Rollback and Finish with expected error.') ;
4872 --END IF ;
4873 --IF FND_API.to_Boolean( p_debug ) THEN
4874 -- Close_Debug_Session ;
4875 --END IF ;
4876 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4877 --ROLLBACK TO Init_Lifecycle;
4878 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4879 FND_MSG_PUB.Count_And_Get
4880 ( p_count => x_msg_count
4881 ,p_data => x_msg_data );
4882 /*
4883 IF g_debug_flag THEN
4884 Write_Debug('Rollback and Finish with unexpected error.') ;
4885 END IF ;
4886 IF FND_API.to_Boolean( p_debug ) THEN
4887 Close_Debug_Session ;
4888 END IF ;
4889 */
4890 WHEN OTHERS THEN
4891 --ROLLBACK TO Init_Lifecycle;
4892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4893 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
4894 THEN
4895 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
4896 END IF;
4897 FND_MSG_PUB.Count_And_Get
4898 ( p_count => x_msg_count
4899 ,p_data => x_msg_data );
4900 /*
4901 IF g_debug_flag THEN
4902 Write_Debug('Rollback and Finish with other error.') ;
4903 END IF ;
4904
4905 IF FND_API.to_Boolean( p_debug ) THEN
4906 Close_Debug_Session ;
4907 END IF ;
4908 */
4909 END delete_ECO;
4910
4911 -- procedure to validate if changing schedule date is permitted for this change order
4912 -- if there are 2 or more revised items (same item) with new revision specified
4913 -- then changing both scheduled dates to the same date causes CO imp. to fail
4914 PROCEDURE is_Reschedule_ECO_Allowed
4915 (
4916 p_change_id IN NUMBER --
4917 ,x_is_change_sch_date_allowed OUT NOCOPY VARCHAR2
4918 )
4919 IS
4920 -- begin of vamohan changes
4921 CURSOR chk_if_rev_item_occurs_twice IS
4922 select 'X'
4923 from eng_revised_items REV1, eng_revised_items REV2
4924 where REV1.change_id = p_change_id
4925 and REV2.change_id = p_change_id
4926 and REV1.organization_id = REV2.organization_id
4927 and REV1.revised_item_id = REV2.revised_item_id
4928 and REV1.revised_item_sequence_id <> REV2.revised_item_sequence_id
4929 -- fix bug 14620669, start: do not check the implemented Revised Item
4930 /*
4931 and REV1.status_type <> 5
4932 and REV2.status_type <> 5
4933 */
4934 and REV1.status_type not in (5, -- CANCELLED
4935 6, -- IMPLEMENTED
4936 9 -- IMPLEMENTATION_IN_PROGRESS
4937 )
4938 and REV2.status_type not in (5, -- CANCELLED
4939 6, -- IMPLEMENTED
4940 9 -- IMPLEMENTATION_IN_PROGRESS
4941 )
4942 -- fix bug 14620669, end: do not check the implemented Revised Item
4943 and REV1.new_item_revision is not null
4944 and REV2.new_item_revision is not null;
4945
4946 chk_rev_item_type_var VARCHAR2(1);
4947 BEGIN
4948 open chk_if_rev_item_occurs_twice;
4949 fetch chk_if_rev_item_occurs_twice into chk_rev_item_type_var;
4950 IF (chk_if_rev_item_occurs_twice%found)
4951 THEN
4952 x_is_change_sch_date_allowed := 'N';
4953 ELSE
4954 x_is_change_sch_date_allowed := 'Y';
4955 END IF;
4956 close chk_if_rev_item_occurs_twice;
4957 EXCEPTION
4958 WHEN OTHERS THEN
4959 x_is_change_sch_date_allowed := 'N';
4960 IF chk_if_rev_item_occurs_twice%ISOPEN THEN
4961 close chk_if_rev_item_occurs_twice;
4962 END IF;
4963 RAISE;
4964 END is_Reschedule_ECO_Allowed;
4965
4966 END ENG_Eco_Util;