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