[Home] [Help]
PACKAGE BODY: APPS.INV_EBI_CHANGE_ORDER_HELPER
Source
1 PACKAGE BODY INV_EBI_CHANGE_ORDER_HELPER AS
2 /* $Header: INVEIHCOB.pls 120.32.12010000.9 2009/02/23 16:24:18 prepatel ship $ */
3
4 /************************************************************************************
5 -- API name : set_assign_item
6 -- Type : Private
7 -- Function :
8 ************************************************************************************/
9 PROCEDURE set_assign_item(
10 p_assign_item IN VARCHAR2
11 ) IS
12 BEGIN
13 G_ASSIGN_ITEM := p_assign_item;
14 END set_assign_item;
15
16 /************************************************************************************
17 -- API name : get_assign_item
18 -- Type : Private
19 -- Function :
20 ************************************************************************************/
21 FUNCTION get_assign_item RETURN VARCHAR2
22 IS
23 BEGIN
24 RETURN G_ASSIGN_ITEM ;
25 END get_assign_item;
26
27 /************************************************************************************
28 -- API name : get_change_order_uda
29 -- Type : Private
30 -- Function :
31 -- Bug 7240247
32 ************************************************************************************/
33 PROCEDURE get_change_order_uda(
34 p_change_id IN NUMBER ,
35 x_change_uda OUT NOCOPY inv_ebi_uda_input_obj,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2
39 ) IS
40 l_count NUMBER :=0;
41 l_attr_group_count NUMBER :=0;
42 l_change_order_type_id NUMBER;
43 l_application_id NUMBER;
44 l_attr_group_id_tbl FND_TABLE_OF_NUMBER;
45 l_pkdata EGO_COL_NAME_VALUE_PAIR_ARRAY;
46 l_uda_output_obj inv_ebi_eco_output_obj;
47 l_output_status inv_ebi_output_status;
48
49 CURSOR c_attr_group_id IS
50
51 SELECT DISTINCT attr_group_id
52 FROM ENG_CHANGES_EXT_B
53 WHERE change_id = p_change_id;
54
55 BEGIN
56 x_return_status := FND_API.g_ret_sts_success;
57 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
58 l_uda_output_obj := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
59
60 IF(p_change_id IS NOT NULL ) THEN
61
62 IF c_attr_group_id%ISOPEN THEN
63 CLOSE c_attr_group_id;
64 END IF;
65
66 OPEN c_attr_group_id ;
67 FETCH c_attr_group_id BULK COLLECT INTO l_attr_group_id_tbl ;
68 CLOSE c_attr_group_id;
69
70 IF(l_attr_group_id_tbl IS NOT NULL AND l_attr_group_id_tbl.COUNT > 0) THEN
71 l_pkdata := EGO_COL_NAME_VALUE_PAIR_ARRAY();
72 l_pkdata.extend(1);
73 l_pkdata(1) := EGO_COL_NAME_VALUE_PAIR_OBJ('CHANGE_ID',p_change_id);
74
75 SELECT change_order_type_id INTO l_change_order_type_id
76 FROM eng_engineering_changes
77 WHERE change_id = p_change_id;
78
79 l_application_id:= INV_EBI_UTIL.get_application_id(
80 p_application_short_name => 'ENG'
81 );
82
83 IF(l_application_id IS NULL ) THEN
84 FND_MESSAGE.set_name('INV','INV_EBI_APP_INVALID');
85 FND_MESSAGE.set_token('COL_VALUE', 'ENG');
86 FND_MSG_PUB.add;
87 RAISE FND_API.g_exc_error;
88 END IF;
89
90 INV_EBI_ITEM_HELPER.get_uda_attributes(
91 p_classification_id => l_change_order_type_id,
92 p_attr_group_type => INV_EBI_UTIL.G_ENG_CHANGEMGMT_GROUP,
93 p_application_id => l_application_id,
94 p_attr_grp_id_tbl => l_attr_group_id_tbl,
95 p_data_level => INV_EBI_UTIL.G_CHANGE_LEVEL,
96 p_revision_id => NULL,
97 p_object_name => INV_EBI_UTIL.G_CHANGE_OBJ_NAME,
98 p_pk_data => l_pkdata,
99 x_uda_obj => x_change_uda,
100 x_uda_output_obj => l_uda_output_obj );
101
102 IF(l_uda_output_obj.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
103 x_msg_data := l_uda_output_obj.output_status.msg_data ;
104 RAISE FND_API.g_exc_unexpected_error;
105 END IF;
106 END IF;
107 END IF;
108 EXCEPTION
109 WHEN FND_API.g_exc_unexpected_error THEN
110
111 IF c_attr_group_id%ISOPEN THEN
112 CLOSE c_attr_group_id;
113 END IF;
114
115 x_return_status := FND_API.g_ret_sts_unexp_error;
116 IF(x_msg_data IS NULL) THEN
117 fnd_msg_pub.count_and_get(
118 p_encoded => FND_API.g_false
119 ,p_count => x_msg_count
120 ,p_data => x_msg_data
121 );
122 END IF;
123 WHEN OTHERS THEN
124
125 IF c_attr_group_id%ISOPEN THEN
126 CLOSE c_attr_group_id;
127 END IF;
128
129 x_return_status := FND_API.g_ret_sts_unexp_error;
130 IF (x_msg_data IS NOT NULL) THEN
131 x_msg_data := x_msg_data||' ->INV_EBI_CHANGE_ORDER_HELPER.get_change_order_uda ';
132 ELSE
133 x_msg_data := SQLERRM||'INV_EBI_CHANGE_ORDER_HELPER.get_change_order_uda ';
134 END IF;
135 END get_change_order_uda;
136
137 /************************************************************************************
138 -- API name : get_structure_header_uda
139 -- Type : Private
140 -- Function :
141 -- Bug 7240247
142 ************************************************************************************/
143
144 PROCEDURE get_structure_header_uda(
145 p_assembly_item_id IN NUMBER ,
146 p_alternate_bom_code IN VARCHAR2,
147 p_organization_id IN NUMBER,
148 x_structure_header_uda OUT NOCOPY inv_ebi_uda_input_obj,
149 x_return_status OUT NOCOPY VARCHAR2,
150 x_msg_count OUT NOCOPY NUMBER,
151 x_msg_data OUT NOCOPY VARCHAR2
152 ) IS
153
154 l_bom_count NUMBER :=0;
155 l_uda_count NUMBER :=0;
156 l_attr_group_count NUMBER :=0;
157
158 l_application_id NUMBER;
159 l_attr_group_id_tbl FND_TABLE_OF_NUMBER;
160 l_pkdata EGO_COL_NAME_VALUE_PAIR_ARRAY;
161 l_uda_output_obj inv_ebi_eco_output_obj;
162 l_output_status inv_ebi_output_status;
163 l_structure_type_id NUMBER;
164 l_bill_sequence_id NUMBER;
165
166 CURSOR c_attr_group_id(
167 p_bill_sequence_id NUMBER,
168 p_structure_type_id NUMBER
169 ) IS
170 SELECT DISTINCT attr_group_id
171 FROM bom_structures_ext_b
172 WHERE
173 bill_sequence_id = p_bill_sequence_id AND
174 structure_type_id = p_structure_type_id;
175
176 CURSOR c_bom_count
177 IS
178
179 SELECT bill_sequence_id,structure_type_id
180 FROM bom_bill_of_materials
181 WHERE
182 assembly_item_id = p_assembly_item_id
183 AND organization_id = p_organization_id
184 AND NVL(alternate_bom_designator, 'NONE') = DECODE(p_alternate_bom_code,FND_API.G_MISS_CHAR,'NONE',NULL,'NONE',p_alternate_bom_code) ;
185
186
187 BEGIN
188 x_return_status := FND_API.g_ret_sts_success;
189 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
190 l_uda_output_obj := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
191
192 IF( p_assembly_item_id IS NOT NULL AND
193 p_organization_id IS NOT NULL) THEN
194
195 IF c_bom_count%ISOPEN THEN
196 CLOSE c_bom_count;
197 END IF;
198
199 OPEN c_bom_count;
200 FETCH c_bom_count INTO l_bill_sequence_id,l_structure_type_id;
201
202 IF(c_bom_count % ROWCOUNT > 0) THEN
203
204 IF c_attr_group_id%ISOPEN THEN
205 CLOSE c_attr_group_id;
206 END IF;
207
208 OPEN c_attr_group_id(l_bill_sequence_id,l_structure_type_id) ;
209 FETCH c_attr_group_id BULK COLLECT INTO l_attr_group_id_tbl ;
210
211 IF(c_attr_group_id % ROWCOUNT > 0) THEN
212
213
214 l_pkdata := EGO_COL_NAME_VALUE_PAIR_ARRAY();
215 l_pkdata.extend(1);
216 l_pkdata(1) := EGO_COL_NAME_VALUE_PAIR_OBJ('BILL_SEQUENCE_ID',l_bill_sequence_id);
217
218 l_application_id:= INV_EBI_UTIL.get_application_id(
219 p_application_short_name => 'BOM'
220 );
221 IF(l_application_id IS NULL ) THEN
222
223 FND_MESSAGE.set_name('INV','INV_EBI_APP_INVALID');
224 FND_MESSAGE.set_token('COL_VALUE', 'BOM');
225 FND_MSG_PUB.add;
226 RAISE FND_API.g_exc_error;
227 END IF;
228
229 INV_EBI_ITEM_HELPER.get_uda_attributes(
230 p_classification_id => l_structure_type_id,
231 p_attr_group_type => INV_EBI_UTIL.G_BOM_STRUCTUREMGMT_GROUP,
232 p_application_id => l_application_id,
233 p_attr_grp_id_tbl => l_attr_group_id_tbl,
234 p_data_level => INV_EBI_UTIL.G_STRUCTURES_LEVEL,
235 p_revision_id => NULL,
236 p_object_name => INV_EBI_UTIL.G_BOM_STRUCTURE_OBJ_NAME,
237 p_pk_data => l_pkdata,
238 x_uda_obj => x_structure_header_uda,
239 x_uda_output_obj => l_uda_output_obj
240 );
241
242 IF(l_uda_output_obj.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
243 x_msg_data := l_uda_output_obj.output_status.msg_data ;
244 RAISE FND_API.g_exc_unexpected_error;
245 END IF;
246 END IF;
247 CLOSE c_attr_group_id;
248 END IF;
249 CLOSE c_bom_count;
250 END IF;
251
252 EXCEPTION
253 WHEN FND_API.g_exc_unexpected_error THEN
254
255 IF c_attr_group_id%ISOPEN THEN
256 CLOSE c_attr_group_id;
257 END IF;
258
259 IF c_bom_count%ISOPEN THEN
260 CLOSE c_bom_count;
261 END IF;
262
263 x_return_status := FND_API.g_ret_sts_unexp_error;
264 IF(x_msg_data IS NULL) THEN
265 fnd_msg_pub.count_and_get(
266 p_encoded => FND_API.g_false
267 ,p_count => x_msg_count
268 ,p_data => x_msg_data
269 );
270 END IF;
271 WHEN OTHERS THEN
272
273 IF c_attr_group_id%ISOPEN THEN
274 CLOSE c_attr_group_id;
275 END IF;
276
277 IF c_bom_count%ISOPEN THEN
278 CLOSE c_bom_count;
279 END IF;
280
281 x_return_status := FND_API.g_ret_sts_unexp_error;
282 IF (x_msg_data IS NOT NULL) THEN
283 x_msg_data := x_msg_data||' ->INV_EBI_CHANGE_ORDER_HELPER.get_structure_header_uda ';
284 ELSE
285 x_msg_data := SQLERRM||'INV_EBI_CHANGE_ORDER_HELPER.get_structure_header_uda ';
286 END IF;
287 END get_structure_header_uda;
288
289 /************************************************************************************
290 -- API name : get_component_item_uda
291 -- Type : Private
292 -- Function :
293 -- Bug 7240247
294 ************************************************************************************/
295
296 PROCEDURE get_component_item_uda(
297 p_eco_name IN VARCHAR2,
298 p_revised_item_id IN NUMBER,
299 p_component_item_name IN VARCHAR2,
300 p_alternate_bom_code IN VARCHAR2,
301 p_organization_id IN NUMBER,
302 x_comp_item_uda OUT NOCOPY inv_ebi_uda_input_obj,
303 x_return_status OUT NOCOPY VARCHAR2,
304 x_msg_count OUT NOCOPY NUMBER,
305 x_msg_data OUT NOCOPY VARCHAR2
306 ) IS
307
308 l_bom_count NUMBER :=0;
309 l_uda_count NUMBER :=0;
310 l_attr_group_count NUMBER :=0;
311 l_application_id NUMBER;
312 l_attr_group_id_tbl FND_TABLE_OF_NUMBER;
313 l_pkdata EGO_COL_NAME_VALUE_PAIR_ARRAY;
314 l_uda_output_obj inv_ebi_eco_output_obj;
315 l_output_status inv_ebi_output_status;
316 l_structure_type_id NUMBER;
317 l_bill_sequence_id NUMBER;
318 l_component_item_id NUMBER;
319 l_component_sequence_id NUMBER;
320 l_component_count NUMBER := 0;
321
322
323 CURSOR c_attr_group_id(
324 p_bill_sequence_id NUMBER,
325 p_structure_type_id NUMBER
326 ) IS
327 SELECT DISTINCT attr_group_id
328 FROM bom_components_ext_b
329 WHERE bill_sequence_id = p_bill_sequence_id
330 AND structure_type_id = p_structure_type_id;
331
332 CURSOR c_bom_count
333 IS
334
335 SELECT bill_sequence_id,structure_type_id
336 FROM bom_bill_of_materials
337 WHERE
338 assembly_item_id = p_revised_item_id
339 AND organization_id = p_organization_id
340 AND NVL(alternate_bom_designator, 'NONE') = DECODE(p_alternate_bom_code,FND_API.G_MISS_CHAR,'NONE',NULL,'NONE',p_alternate_bom_code) ;
341
342 CURSOR c_comp_count(
343 p_bill_sequence_id NUMBER,
344 p_component_item_id NUMBER
345 ) IS
346 SELECT component_sequence_id
347 FROM bom_components_b
348 WHERE
349 bill_sequence_id = p_bill_sequence_id AND
350 component_item_id = p_component_item_id AND
351 change_notice = p_eco_name;
352
353 BEGIN
354 x_return_status := FND_API.g_ret_sts_success;
355 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
356 l_uda_output_obj := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
357
358 IF( p_revised_item_id IS NOT NULL AND
359 p_organization_id IS NOT NULL) THEN
360
361 IF c_bom_count%ISOPEN THEN
362 CLOSE c_bom_count;
363 END IF;
364
365 OPEN c_bom_count;
366 FETCH c_bom_count INTO l_bill_sequence_id,l_structure_type_id;
367
368 IF(c_bom_count % ROWCOUNT > 0) THEN
369
370 l_component_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id (
371 p_organization_id => p_organization_id
372 ,p_item_number => p_component_item_name
373 );
374 IF c_comp_count%ISOPEN THEN
375 CLOSE c_comp_count;
376 END IF;
377
378 OPEN c_comp_count(l_bill_sequence_id,l_component_item_id);
379 FETCH c_comp_count INTO l_component_sequence_id;
380
381 IF(c_comp_count % ROWCOUNT > 0) THEN
382
383 IF c_attr_group_id%ISOPEN THEN
384 CLOSE c_attr_group_id;
385 END IF;
386
387 OPEN c_attr_group_id(l_bill_sequence_id,l_structure_type_id) ;
388 FETCH c_attr_group_id BULK COLLECT INTO l_attr_group_id_tbl ;
389
390 IF(c_attr_group_id % ROWCOUNT > 0) THEN
391
392 l_pkdata := EGO_COL_NAME_VALUE_PAIR_ARRAY();
393 l_pkdata.extend(2);
394 l_pkdata(1) := EGO_COL_NAME_VALUE_PAIR_OBJ('BILL_SEQUENCE_ID',l_bill_sequence_id);
395 l_pkdata(2) := EGO_COL_NAME_VALUE_PAIR_OBJ('COMPONENT_SEQUENCE_ID',l_component_sequence_id);
396
397 l_application_id:= INV_EBI_UTIL.get_application_id(
398 p_application_short_name => 'BOM'
399 );
400
401 IF(l_application_id IS NULL ) THEN
402 FND_MESSAGE.set_name('INV','INV_EBI_APP_INVALID');
403 FND_MESSAGE.set_token('COL_VALUE', 'BOM');
404 FND_MSG_PUB.add;
405 RAISE FND_API.g_exc_error;
406 END IF;
407
408 INV_EBI_ITEM_HELPER.get_uda_attributes(
409 p_classification_id => l_structure_type_id,
410 p_attr_group_type => INV_EBI_UTIL.G_BOM_COMPONENTMGMT_GROUP,
411 p_application_id => l_application_id,
412 p_attr_grp_id_tbl => l_attr_group_id_tbl,
413 p_data_level => INV_EBI_UTIL.G_COMPONENTS_LEVEL,
414 p_revision_id => NULL,
415 p_object_name => INV_EBI_UTIL.G_BOM_COMPONENTS_OBJ_NAME,
416 p_pk_data => l_pkdata,
417 x_uda_obj => x_comp_item_uda,
418 x_uda_output_obj => l_uda_output_obj
419 );
420 IF(l_uda_output_obj.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
421 x_msg_data := l_uda_output_obj.output_status.msg_data ;
422 RAISE FND_API.g_exc_unexpected_error;
423 END IF;
424 END IF;
425 CLOSE c_attr_group_id;
426 END IF;
427 CLOSE c_comp_count;
428 END IF;
429 CLOSE c_bom_count;
430 END IF;
431
432 EXCEPTION
433 WHEN FND_API.g_exc_unexpected_error THEN
434
435 IF c_attr_group_id%ISOPEN THEN
436 CLOSE c_attr_group_id;
437 END IF;
438
439 IF c_bom_count%ISOPEN THEN
440 CLOSE c_bom_count;
441 END IF;
442
443 IF c_comp_count%ISOPEN THEN
444 CLOSE c_comp_count;
445 END IF;
446
447 x_return_status := FND_API.g_ret_sts_unexp_error;
448 IF(x_msg_data IS NULL) THEN
449 fnd_msg_pub.count_and_get(
450 p_encoded => FND_API.g_false
451 ,p_count => x_msg_count
452 ,p_data => x_msg_data
453 );
454 END IF;
455 WHEN OTHERS THEN
456
457 IF c_attr_group_id%ISOPEN THEN
458 CLOSE c_attr_group_id;
459 END IF;
460
461 IF c_bom_count%ISOPEN THEN
462 CLOSE c_bom_count;
463 END IF;
464
465 IF c_comp_count%ISOPEN THEN
466 CLOSE c_comp_count;
467 END IF;
468
469 x_return_status := FND_API.g_ret_sts_unexp_error;
470 IF (x_msg_data IS NOT NULL) THEN
471 x_msg_data := x_msg_data||' ->INV_EBI_CHANGE_ORDER_HELPER.get_component_item_uda ';
472 ELSE
473 x_msg_data := SQLERRM||'INV_EBI_CHANGE_ORDER_HELPER.get_component_item_uda ';
474 END IF;
475 END get_component_item_uda;
476 /************************************************************************************
477 -- API name : prepare_substitute_components
478 -- Type : Private
479 -- Function : Prepare substitute components for Replicate bom
480 ************************************************************************************/
481 PROCEDURE prepare_substitute_components(
482 p_component_item IN inv_ebi_rev_comp_obj
483 ,p_from_sequence_id IN NUMBER
484 ,p_reference_org_id IN NUMBER
485 ,x_component_item OUT NOCOPY inv_ebi_rev_comp_obj
486 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
487 ) IS
488
489 CURSOR c_comp_sequence_id
490 IS
491 SELECT
492 bic.component_sequence_id
493 FROM
494 bom_inventory_components bic,
495 mtl_system_items_kfv it
496 WHERE
497 bic.bill_sequence_id = p_from_sequence_id
498 AND bic.component_item_id = it.inventory_item_id
499 AND it.organization_id = p_reference_org_id
500 AND it.concatenated_segments = p_component_item.component_item_name;
501
502 CURSOR c_copied_substitute_comps (p_component_sequence_id IN NUMBER)
503 IS
504 SELECT
505 sc.substitute_component_id,
506 it.concatenated_segments substitute_component_name
507 FROM
508 bom_substitute_components sc,
509 mtl_system_items_kfv it
510 WHERE
511 sc.substitute_component_id = it.inventory_item_id
512 AND it.organization_id = p_reference_org_id
513 AND sc.component_sequence_id = p_component_sequence_id
514 AND NVL(sc.acd_type,1) = 1; --Only added components are taken
515
516 l_copied_substitute_comp c_copied_substitute_comps%ROWTYPE;
517
518 CURSOR c_merged_substitute_comp (
519 p_new_substitute_comp IN inv_ebi_sub_comp_obj
520 ,p_component_sequence_id IN NUMBER
521 ,p_substitute_component_name IN VARCHAR2
522 ) IS
523 SELECT
524 DECODE(p_new_substitute_comp ,NULL ,NVL(sc.acd_type,1) ,DECODE(p_new_substitute_comp.acd_type
525 ,fnd_api.g_miss_num ,NVL(sc.acd_type,1) ,p_new_substitute_comp.acd_type)) acd_type
526 ,DECODE(p_new_substitute_comp ,NULL ,sc.substitute_item_quantity ,DECODE(p_new_substitute_comp.substitute_item_quantity
527 ,fnd_api.g_miss_num ,sc.substitute_item_quantity ,p_new_substitute_comp.substitute_item_quantity)) substitute_item_quantity
528 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute_category ,DECODE(p_new_substitute_comp.attribute_category
529 ,fnd_api.g_miss_char ,sc.attribute_category ,p_new_substitute_comp.attribute_category )) attribute_category
530 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute1 ,DECODE(p_new_substitute_comp.attribute1
531 ,fnd_api.g_miss_char ,sc.attribute1 ,p_new_substitute_comp.attribute1 )) attribute1
532 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute2 ,DECODE(p_new_substitute_comp.attribute2
533 ,fnd_api.g_miss_char ,sc.attribute2 ,p_new_substitute_comp.attribute2 )) attribute2
534 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute3 ,DECODE(p_new_substitute_comp.attribute3
535 ,fnd_api.g_miss_char ,sc.attribute3 ,p_new_substitute_comp.attribute3 )) attribute3
536 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute4 ,DECODE(p_new_substitute_comp.attribute4
537 ,fnd_api.g_miss_char ,sc.attribute4 ,p_new_substitute_comp.attribute4 )) attribute4
538 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute5 ,DECODE(p_new_substitute_comp.attribute5
539 ,fnd_api.g_miss_char ,sc.attribute5 ,p_new_substitute_comp.attribute5 )) attribute5
540 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute6 ,DECODE(p_new_substitute_comp.attribute6
541 ,fnd_api.g_miss_char ,sc.attribute6 ,p_new_substitute_comp.attribute6 )) attribute6
542 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute7 ,DECODE(p_new_substitute_comp.attribute7
543 ,fnd_api.g_miss_char ,sc.attribute7 ,p_new_substitute_comp.attribute7 )) attribute7
544 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute8 ,DECODE(p_new_substitute_comp.attribute8
545 ,fnd_api.g_miss_char ,sc.attribute8 ,p_new_substitute_comp.attribute8 )) attribute8
546 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute9 ,DECODE(p_new_substitute_comp.attribute9
547 ,fnd_api.g_miss_char ,sc.attribute9 ,p_new_substitute_comp.attribute9 )) attribute9
548 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute10 ,DECODE(p_new_substitute_comp.attribute10
549 ,fnd_api.g_miss_char ,sc.attribute10 ,p_new_substitute_comp.attribute10 )) attribute10
550 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute11 ,DECODE(p_new_substitute_comp.attribute11
551 ,fnd_api.g_miss_char ,sc.attribute11 ,p_new_substitute_comp.attribute11 )) attribute11
552 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute12 ,DECODE(p_new_substitute_comp.attribute12
553 ,fnd_api.g_miss_char ,sc.attribute12 ,p_new_substitute_comp.attribute12 )) attribute12
554 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute13 ,DECODE(p_new_substitute_comp.attribute13
555 ,fnd_api.g_miss_char ,sc.attribute13 ,p_new_substitute_comp.attribute13 )) attribute13
556 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute14 ,DECODE(p_new_substitute_comp.attribute14
557 ,fnd_api.g_miss_char ,sc.attribute14 ,p_new_substitute_comp.attribute14 )) attribute14
558 ,DECODE(p_new_substitute_comp ,NULL ,sc.attribute15 ,DECODE(p_new_substitute_comp.attribute15
559 ,fnd_api.g_miss_char ,sc.attribute15 ,p_new_substitute_comp.attribute15 )) attribute15
560 ,DECODE(p_new_substitute_comp ,NULL ,sc.original_system_reference ,DECODE(p_new_substitute_comp.original_system_reference
561 ,fnd_api.g_miss_char ,sc.original_system_reference ,p_new_substitute_comp.original_system_reference)) original_system_reference
562 ,DECODE(p_new_substitute_comp ,NULL ,sc.enforce_int_requirements ,DECODE(p_new_substitute_comp.enforce_int_requirements
563 ,fnd_api.g_miss_num ,sc.enforce_int_requirements ,p_new_substitute_comp.enforce_int_requirements)) enforce_int_requirements
564 ,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.start_effective_date) start_effective_date
565 ,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.new_substitute_component_name ) new_substitute_component_name
566 ,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.from_end_item_unit_number) from_end_item_unit_number
567 ,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.new_routing_revision) new_routing_revision
568 ,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.return_status) return_status
569 ,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.inverse_quantity) inverse_quantity
570 ,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.row_identifier ) row_identifier
571 ,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.program_id) program_id
572 FROM
573 bom_substitute_components sc,
574 mtl_system_items_kfv it
575 WHERE
576 sc.substitute_component_id = it.inventory_item_id AND
577 it.organization_id = p_reference_org_id AND
578 it.concatenated_segments = p_substitute_component_name AND
579 sc.component_sequence_id = p_component_sequence_id;
580
581 l_component_sequence_id NUMBER;
582 i NUMBER := 0;
583 l_component_item inv_ebi_rev_comp_obj;
584 l_Found BOOLEAN;
585 l_merged_subst_comp c_Merged_Substitute_Comp%ROWTYPE;
586 l_substitute_comp_tbl inv_ebi_sub_comp_tbl;
587 l_sub_comp_count NUMBER := 1;
588 l_output_status inv_ebi_output_status;
589 BEGIN
590 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
591 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
592 INV_EBI_UTIL.debug_line('STEP: 10 START INSIDE INV_EBI_CHANGE_ORDER_HELPER.prepare_substitute_components');
593 OPEN c_comp_sequence_id;
594 FETCH c_comp_sequence_id INTO l_component_sequence_id;
595 CLOSE c_comp_sequence_id;
596
597 l_component_item := p_component_item;
598
599 IF p_component_item.substitute_component_tbl IS NOT NULL THEN
600 l_substitute_comp_tbl := inv_ebi_sub_comp_tbl();
601 FOR i IN 1..p_component_item.substitute_component_tbl.COUNT LOOP
602 IF p_component_item.substitute_component_tbl(i).transaction_type <> ENG_GLOBALS.g_opr_delete THEN
603 l_substitute_comp_tbl.EXTEND(1);
604 l_substitute_comp_tbl(l_sub_comp_count) := p_component_item.substitute_component_tbl(i);
605 l_sub_comp_count := l_sub_comp_count + 1;
606 END IF;
607 END LOOP;
608 l_component_item.substitute_component_tbl := l_Substitute_Comp_Tbl;
609 END IF;
610
611
612 OPEN c_copied_substitute_comps(l_Component_Sequence_Id);
613 LOOP
614 FETCH c_copied_substitute_comps INTO l_copied_substitute_comp;
615 EXIT WHEN c_copied_substitute_comps%NOTFOUND;
616 l_Found := FALSE;
617 IF p_component_item.substitute_component_tbl IS NOT NULL THEN
618 FOR i IN 1..p_component_item.substitute_component_tbl.COUNT LOOP
619 IF l_copied_substitute_comp.substitute_component_name = p_component_item.substitute_component_tbl(i).substitute_component_name THEN
620 l_Found := TRUE;
621 END IF;
622 END LOOP;
623 END IF;
624
625 IF NOT l_Found THEN
626 OPEN c_merged_substitute_comp(p_new_substitute_comp => NULL
627 ,p_substitute_component_name => l_copied_substitute_comp.substitute_component_name
628 ,p_component_sequence_id => l_Component_Sequence_Id
629 );
630 FETCH c_merged_substitute_comp INTO l_merged_subst_comp;
631 IF l_component_item.substitute_component_tbl IS NULL THEN
632 l_component_item.substitute_component_tbl := inv_ebi_sub_comp_tbl();
633 END IF;
634
635 l_component_item.substitute_component_tbl.EXTEND(1);
636 l_component_item.substitute_component_tbl(l_component_item.substitute_component_tbl.COUNT) := inv_ebi_sub_comp_obj(
637 l_component_item.start_effective_date
638 ,l_copied_substitute_comp.substitute_component_name
639 ,l_merged_subst_comp.new_substitute_component_name
640 ,l_merged_subst_comp.acd_type
641 ,l_merged_subst_comp.substitute_item_quantity
642 ,l_merged_subst_comp.attribute_category
643 ,l_merged_subst_comp.attribute1
644 ,l_merged_subst_comp.attribute2
645 ,l_merged_subst_comp.attribute3
646 ,l_merged_subst_comp.attribute4
647 ,l_merged_subst_comp.attribute5
648 ,l_merged_subst_comp.attribute6
649 ,l_merged_subst_comp.attribute7
650 ,l_merged_subst_comp.attribute8
651 ,l_merged_subst_comp.attribute9
652 ,l_merged_subst_comp.attribute10
653 ,l_merged_subst_comp.attribute11
654 ,l_merged_subst_comp.attribute12
655 ,l_merged_subst_comp.attribute13
656 ,l_merged_subst_comp.attribute14
657 ,l_merged_subst_comp.attribute15
658 ,l_merged_subst_comp.original_system_reference
659 ,l_merged_subst_comp.from_end_item_unit_number
660 ,l_merged_subst_comp.new_routing_revision
661 ,l_merged_subst_comp.enforce_int_requirements
662 ,l_merged_subst_comp.return_status
663 ,ENG_GLOBALS.g_opr_create
664 ,l_merged_subst_comp.row_identifier
665 ,l_merged_subst_comp.inverse_quantity
666 ,l_Merged_Subst_Comp.program_id
667 ,NULL);
668 CLOSE c_merged_substitute_comp;
669 END IF;
670 END LOOP;
671 CLOSE c_copied_substitute_comps;
672
673
674 IF l_component_item.substitute_component_tbl IS NOT NULL THEN
675 FOR i IN 1..l_component_item.substitute_component_tbl.COUNT LOOP
676 IF l_component_item.substitute_component_tbl(i).transaction_type = ENG_GLOBALS.g_opr_update THEN
677 OPEN c_merged_substitute_comp(p_new_substitute_comp => l_component_item.substitute_component_tbl(i)
678 ,p_substitute_component_name => l_component_item.substitute_component_tbl(i).substitute_component_name
679 ,p_Component_Sequence_Id => l_Component_Sequence_Id);
680 FETCH c_merged_substitute_comp INTO l_Merged_Subst_Comp;
681 IF c_merged_substitute_comp%FOUND THEN
682 l_component_item.substitute_component_tbl(i) := inv_ebi_sub_comp_obj(
683 l_merged_subst_comp.start_effective_date
684 ,l_component_item.substitute_component_tbl(i).substitute_component_name
685 ,l_merged_subst_comp.new_substitute_component_name
686 ,l_merged_subst_comp.acd_type
687 ,l_merged_subst_comp.substitute_item_quantity
688 ,l_merged_subst_comp.attribute_category
689 ,l_merged_subst_comp.attribute1
690 ,l_merged_subst_comp.attribute2
691 ,l_merged_subst_comp.attribute3
692 ,l_merged_subst_comp.attribute4
693 ,l_merged_subst_comp.attribute5
694 ,l_merged_subst_comp.attribute6
695 ,l_merged_subst_comp.attribute7
696 ,l_merged_subst_comp.attribute8
697 ,l_merged_subst_comp.attribute9
698 ,l_merged_subst_comp.attribute10
699 ,l_merged_subst_comp.attribute11
700 ,l_merged_subst_comp.attribute12
701 ,l_merged_subst_comp.attribute13
702 ,l_merged_subst_comp.attribute14
703 ,l_merged_subst_comp.attribute15
704 ,l_merged_subst_comp.original_system_reference
705 ,l_merged_subst_comp.from_end_item_unit_number
706 ,l_merged_subst_comp.new_routing_revision
707 ,l_merged_subst_comp.enforce_int_requirements
708 ,l_merged_subst_comp.return_status
709 ,ENG_GLOBALS.g_opr_create
710 ,l_merged_subst_comp.row_identifier
711 ,l_merged_subst_comp.inverse_quantity
712 ,l_merged_subst_comp.program_id
713 ,NULL);
714 END IF;
715 CLOSE c_merged_substitute_comp;
716 END IF;
717 END LOOP;
718 END IF;
719
720 x_component_item := l_component_item;
721 INV_EBI_UTIL.debug_line('STEP: 20 END INSIDE INV_EBI_CHANGE_ORDER_HELPER.prepare_substitute_components');
722
723 EXCEPTION
724 WHEN FND_API.g_exc_unexpected_error THEN
725 IF c_Merged_Substitute_Comp%ISOPEN THEN
726 CLOSE c_merged_substitute_comp;
727 END IF;
728 IF c_copied_substitute_comps%ISOPEN THEN
729 CLOSE c_copied_substitute_comps;
730 END IF;
731 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
732 IF(x_out.output_status.msg_data IS NULL) THEN
733 fnd_msg_pub.count_and_get(
734 p_encoded => FND_API.g_false
735 ,p_count => x_out.output_status.msg_count
736 ,p_data => x_out.output_status.msg_data
737 );
738 END IF;
739 WHEN OTHERS THEN
740 IF c_merged_substitute_comp%ISOPEN THEN
741 CLOSE c_merged_substitute_comp;
742 END IF;
743 IF c_copied_substitute_comps%ISOPEN THEN
744 CLOSE c_copied_substitute_comps;
745 END IF;
746 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
747 IF (x_out.output_status.msg_data IS NOT NULL) THEN
748 x_out.output_status.msg_data := x_out.output_status.msg_data||' ->INV_EBI_CHANGE_ORDER_HELPER.prepare_substitute_components ';
749 ELSE
750 x_out.output_status.msg_data := SQLERRM||'INV_EBI_CHANGE_ORDER_HELPER.prepare_substitute_components ';
751 END IF;
752 END prepare_substitute_components;
753
754 /************************************************************************************
755 -- API name : prepare_reference_designators
756 -- Type : Private
757 -- Function : Prepare reference_designators for Replicate bom
758 ************************************************************************************/
759 PROCEDURE prepare_reference_designators(
760 p_component_item IN inv_ebi_rev_comp_obj
761 ,p_from_sequence_id IN NUMBER
762 ,p_reference_org_id IN NUMBER
763 ,x_component_item OUT NOCOPY inv_ebi_rev_comp_obj
764 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
765 ) IS
766
767 CURSOR c_comp_sequence_id
768 IS
769 SELECT
770 bic.component_sequence_id
771 FROM
772 bom_inventory_components bic,
773 mtl_system_items_kfv it
774 WHERE
775 bic.bill_sequence_id = p_from_sequence_id
776 AND bic.component_item_id = it.inventory_item_id
777 AND it.organization_id = p_reference_org_id
778 AND it.concatenated_segments = p_component_item.component_item_name;
779
780 CURSOR c_copied_ref_designators (p_component_sequence_id IN NUMBER)
781 IS
782 SELECT
783 component_reference_designator reference_designator_name
784 FROM
785 bom_reference_designators
786 WHERE
787 component_sequence_id = p_component_sequence_id
788 AND NVL(acd_type,1) = 1; --Only added components are taken
789
790
791 CURSOR c_merged_ref_designators (
792 p_new_ref_designator IN inv_ebi_ref_desg_obj
793 ,p_component_sequence_id IN NUMBER
794 ,p_ref_designator_name IN VARCHAR2
795 ) IS
796 SELECT
797 DECODE(p_new_ref_designator ,NULL ,NVL(acd_type,1) ,DECODE(p_new_ref_designator.acd_type
798 ,fnd_api.g_miss_num ,NVL(acd_type,1) ,p_new_ref_designator.acd_type)) acd_type
799 ,DECODE(p_new_ref_designator ,NULL ,attribute_category ,DECODE(p_new_ref_designator.attribute_category
800 ,fnd_api.g_miss_char ,attribute_category ,p_new_ref_designator.attribute_category )) attribute_category
801 ,DECODE(p_new_ref_designator ,NULL ,attribute1 ,DECODE(p_new_ref_designator.attribute1
802 ,fnd_api.g_miss_char ,attribute1 ,p_new_ref_designator.attribute1 )) attribute1
803 ,DECODE(p_new_ref_designator ,NULL ,attribute2 ,DECODE(p_new_ref_designator.attribute2
804 ,fnd_api.g_miss_char ,attribute2 ,p_new_ref_designator.attribute2 )) attribute2
805 ,DECODE(p_new_ref_designator ,NULL ,attribute3 ,DECODE(p_new_ref_designator.attribute3
806 ,fnd_api.g_miss_char ,attribute3 ,p_new_ref_designator.attribute3 )) attribute3
807 ,DECODE(p_new_ref_designator ,NULL ,attribute4 ,DECODE(p_new_ref_designator.attribute4
808 ,fnd_api.g_miss_char ,attribute4 ,p_new_ref_designator.attribute4 )) attribute4
809 ,DECODE(p_new_ref_designator ,NULL ,attribute5 ,DECODE(p_new_ref_designator.attribute5
810 ,fnd_api.g_miss_char ,attribute5 ,p_new_ref_designator.attribute5 )) attribute5
811 ,DECODE(p_new_ref_designator ,NULL ,attribute6 ,DECODE(p_new_ref_designator.attribute6
812 ,fnd_api.g_miss_char ,attribute6 ,p_new_ref_designator.attribute6 )) attribute6
813 ,DECODE(p_new_ref_designator ,NULL ,attribute7 ,DECODE(p_new_ref_designator.attribute7
814 ,fnd_api.g_miss_char ,attribute7 ,p_new_ref_designator.attribute7 )) attribute7
815 ,DECODE(p_new_ref_designator ,NULL ,attribute8 ,DECODE(p_new_ref_designator.attribute8
816 ,fnd_api.g_miss_char ,attribute8 ,p_new_ref_designator.attribute8 )) attribute8
817 ,DECODE(p_new_ref_designator ,NULL ,attribute9 ,DECODE(p_new_ref_designator.attribute9
818 ,fnd_api.g_miss_char ,attribute9 ,p_new_ref_designator.attribute9 )) attribute9
819 ,DECODE(p_new_ref_designator ,NULL ,attribute10 ,DECODE(p_new_ref_designator.attribute10
820 ,fnd_api.g_miss_char ,attribute10 ,p_new_ref_designator.attribute10 )) attribute10
821 ,DECODE(p_new_ref_designator ,NULL ,attribute11 ,DECODE(p_new_ref_designator.attribute11
822 ,fnd_api.g_miss_char ,attribute11 ,p_new_ref_designator.attribute11 )) attribute11
823 ,DECODE(p_new_ref_designator ,NULL ,attribute12 ,DECODE(p_new_ref_designator.attribute12
824 ,fnd_api.g_miss_char ,attribute12 ,p_new_ref_designator.attribute12 )) attribute12
825 ,DECODE(p_new_ref_designator ,NULL ,attribute13 ,DECODE(p_new_ref_designator.attribute13
826 ,fnd_api.g_miss_char ,attribute13 ,p_new_ref_designator.attribute13 )) attribute13
827 ,DECODE(p_new_ref_designator ,NULL ,attribute14 ,DECODE(p_new_ref_designator.attribute14
828 ,fnd_api.g_miss_char ,attribute14 ,p_new_ref_designator.attribute14 )) attribute14
829 ,DECODE(p_new_ref_designator ,NULL ,attribute15 ,DECODE(p_new_ref_designator.attribute15
830 ,fnd_api.g_miss_char ,attribute15 ,p_new_ref_designator.attribute15 )) attribute15
831 ,DECODE(p_new_ref_designator ,NULL ,original_system_reference ,DECODE(p_new_ref_designator.original_system_reference
832 ,fnd_api.g_miss_char ,original_system_reference ,p_new_ref_designator.original_system_reference)) original_system_reference
833 ,DECODE(p_new_ref_designator ,NULL ,ref_designator_comment ,DECODE(p_new_ref_designator.ref_designator_comment
834 ,fnd_api.g_miss_char ,ref_designator_comment ,p_new_ref_designator.ref_designator_comment )) ref_designator_comment
835 ,DECODE(p_new_ref_designator ,NULL ,NULL ,p_new_ref_designator.start_effective_date) start_effective_date
836 ,DECODE(p_new_ref_designator ,NULL ,NULL ,p_new_ref_designator.new_reference_designator ) new_reference_designator
837 ,DECODE(p_new_ref_designator ,NULL ,NULL ,p_new_ref_designator.from_end_item_unit_number) from_end_item_unit_number
838 ,DECODE(p_new_ref_designator ,NULL ,NULL ,p_new_ref_designator.new_routing_revision) new_routing_revision
839 ,DECODE(p_new_ref_designator ,NULL ,NULL ,p_new_ref_designator.return_status) return_status
840 ,DECODE(p_new_ref_designator ,NULL ,NULL ,p_new_ref_designator.row_identifier ) row_identifier
841 FROM
842 bom_reference_designators
843 WHERE
844 component_reference_designator = p_ref_designator_name AND
845 component_sequence_id = p_component_sequence_id;
846
847 i NUMBER := 0;
848 l_merged_ref_designator c_merged_ref_designators%ROWTYPE;
849 l_copied_ref_designator c_copied_ref_designators%ROWTYPE;
850 l_component_sequence_id NUMBER;
851 l_component_item inv_ebi_rev_comp_obj;
852 l_Found BOOLEAN;
853 l_ref_designator_tbl inv_ebi_ref_desg_tbl;
854 l_ref_dsgn_count NUMBER := 1;
855 l_output_status inv_ebi_output_status;
856
857 BEGIN
858 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
859 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
860 INV_EBI_UTIL.debug_line('STEP: 10 START INSIDE INV_EBI_CHANGE_ORDER_HELPER.prepare_reference_designators');
861 OPEN c_comp_sequence_id;
862 FETCH c_comp_sequence_id INTO l_component_sequence_id;
863 CLOSE c_comp_sequence_id;
864
865 l_component_item := p_component_item;
866
867 IF p_component_item.reference_designator_tbl IS NOT NULL THEN
868 l_ref_designator_tbl := inv_ebi_ref_desg_tbl();
869 FOR i IN 1..p_component_item.reference_designator_tbl.COUNT LOOP
870 IF p_component_item.reference_designator_tbl(i).transaction_type <> ENG_GLOBALS.g_opr_delete THEN
871 l_ref_designator_tbl.EXTEND(1);
872 l_ref_designator_tbl(l_ref_dsgn_count) := p_component_item.reference_designator_tbl(i);
873 l_ref_dsgn_count := l_ref_dsgn_count + 1;
874 END IF;
875 END LOOP;
876 l_component_item.reference_designator_tbl := l_ref_designator_tbl;
877 END IF;
878
879 OPEN c_copied_ref_designators(l_Component_Sequence_Id);
880 LOOP
881 FETCH c_copied_ref_designators INTO l_copied_ref_designator;
882 EXIT WHEN c_copied_ref_designators%NOTFOUND;
883 l_Found := FALSE;
884 IF p_component_item.reference_designator_tbl IS NOT NULL THEN
885 FOR i IN 1..p_component_item.reference_designator_tbl.COUNT LOOP
886 IF l_copied_ref_designator.reference_designator_name = p_component_item.reference_designator_tbl(i).reference_designator_name THEN
887 l_Found := TRUE;
888 END IF;
889 END LOOP;
890 END IF;
891
892 IF NOT l_Found THEN
893 OPEN c_merged_ref_designators(p_New_Ref_Designator => NULL
894 ,p_ref_designator_name => l_copied_ref_designator.reference_designator_name
895 ,p_component_sequence_id => l_component_sequence_id
896 );
897 FETCH c_merged_ref_designators INTO l_merged_ref_designator;
898 IF l_component_item.reference_designator_tbl IS NULL THEN
899 l_component_item.reference_designator_tbl := inv_ebi_ref_desg_tbl();
900 END IF;
901 l_component_item.reference_designator_tbl.EXTEND(1);
902
903 l_component_item.reference_designator_tbl(l_component_item.reference_designator_tbl.COUNT) := inv_ebi_ref_desg_obj(
904 l_component_item.start_effective_date
905 ,l_copied_ref_designator.reference_designator_name
906 ,l_merged_ref_designator.acd_type
907 ,l_merged_ref_designator.ref_designator_comment
908 ,l_merged_ref_designator.attribute_category
909 ,l_merged_ref_designator.attribute1
910 ,l_merged_ref_designator.attribute2
911 ,l_merged_ref_designator.attribute3
912 ,l_merged_ref_designator.attribute4
913 ,l_merged_ref_designator.attribute5
914 ,l_merged_ref_designator.attribute6
915 ,l_merged_ref_designator.attribute7
916 ,l_merged_ref_designator.attribute8
917 ,l_merged_ref_designator.attribute9
918 ,l_merged_ref_designator.attribute10
919 ,l_merged_ref_designator.attribute11
920 ,l_merged_ref_designator.attribute12
921 ,l_merged_ref_designator.attribute13
922 ,l_merged_ref_designator.attribute14
923 ,l_merged_ref_designator.attribute15
924 ,l_merged_ref_designator.original_system_reference
925 ,l_merged_ref_designator.new_reference_designator
926 ,l_merged_ref_designator.from_end_item_unit_number
927 ,l_merged_ref_designator.new_routing_revision
928 ,l_merged_ref_designator.return_status
929 ,ENG_GLOBALS.g_opr_create
930 ,l_merged_ref_designator.row_identifier
931 ,NULL);
932 CLOSE c_merged_ref_designators;
933 END IF;
934 END LOOP;
935 CLOSE c_copied_ref_designators;
936
937 IF l_component_item.reference_designator_tbl IS NOT NULL THEN
938
939 FOR i IN 1..l_component_item.reference_designator_tbl.COUNT LOOP
940 IF l_component_item.reference_designator_tbl(i).transaction_type = ENG_GLOBALS.g_opr_update THEN
941 OPEN c_merged_ref_designators(p_new_ref_designator => l_component_item.reference_designator_tbl(i)
942 ,p_ref_designator_name => l_component_item.reference_designator_tbl(i).reference_designator_name
943 ,p_component_sequence_id => l_Component_Sequence_Id);
944 FETCH c_merged_ref_designators INTO l_merged_ref_designator;
945 l_component_item.reference_designator_tbl(i) := inv_ebi_ref_desg_obj(
946 l_merged_ref_designator.start_effective_date
947 ,l_component_item.reference_designator_tbl(i).reference_designator_name
948 ,l_merged_ref_designator.acd_type
949 ,l_merged_ref_designator.ref_designator_comment
950 ,l_merged_ref_designator.attribute_category
951 ,l_merged_ref_designator.attribute1
952 ,l_merged_ref_designator.attribute2
953 ,l_merged_ref_designator.attribute3
954 ,l_merged_ref_designator.attribute4
955 ,l_merged_ref_designator.attribute5
956 ,l_merged_ref_designator.attribute6
957 ,l_merged_ref_designator.attribute7
958 ,l_merged_ref_designator.attribute8
959 ,l_merged_ref_designator.attribute9
960 ,l_merged_ref_designator.attribute10
961 ,l_merged_ref_designator.attribute11
962 ,l_merged_ref_designator.attribute12
963 ,l_merged_ref_designator.attribute13
964 ,l_merged_ref_designator.attribute14
965 ,l_merged_ref_designator.attribute15
966 ,l_merged_ref_designator.original_system_reference
967 ,l_merged_ref_designator.new_reference_designator
968 ,l_merged_ref_designator.from_end_item_unit_number
969 ,l_merged_ref_designator.new_routing_revision
970 ,l_merged_ref_designator.return_status
971 ,ENG_GLOBALS.g_opr_create
972 ,l_merged_ref_designator.row_identifier
973 ,NULL);
974 CLOSE c_merged_ref_designators;
975 END IF;
976 END LOOP;
977 END IF;
978
979 x_component_item := l_component_item;
980 INV_EBI_UTIL.debug_line('STEP: 20 END INSIDE INV_EBI_CHANGE_ORDER_HELPER.prepare_reference_designators');
981
982 EXCEPTION
983 WHEN FND_API.g_exc_unexpected_error THEN
984 IF c_merged_ref_designators%ISOPEN THEN
985 CLOSE c_merged_ref_designators;
986 END IF;
987 IF c_copied_ref_designators%ISOPEN THEN
988 CLOSE c_copied_ref_designators;
989 END IF;
990 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
991 IF(x_out.output_status.msg_data IS NULL) THEN
992 fnd_msg_pub.count_and_get(
993 p_encoded => FND_API.g_false
994 ,p_count => x_out.output_status.msg_count
995 ,p_data => x_out.output_status.msg_data
996 );
997 END IF;
998 WHEN OTHERS THEN
999 IF c_merged_ref_designators%ISOPEN THEN
1000 CLOSE c_merged_ref_designators;
1001 END IF;
1002 IF c_copied_ref_designators%ISOPEN THEN
1003 CLOSE c_copied_ref_designators;
1004 END IF;
1005 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
1006 IF (x_out.output_status.msg_data IS NOT NULL) THEN
1007 x_out.output_status.msg_data := x_out.output_status.msg_data||' ->INV_EBI_CHANGE_ORDER_HELPER.prepare_reference_designators ';
1008 ELSE
1009 x_out.output_status.msg_data := SQLERRM||'INV_EBI_CHANGE_ORDER_HELPER.prepare_reference_designators ';
1010 END IF;
1011 END prepare_reference_designators;
1012
1013
1014 /************************************************************************************
1015 -- API name : prepare_component_items
1016 -- Type : Private
1017 -- Function : Prepare component_items for Replicate bom
1018 ************************************************************************************/
1019 PROCEDURE prepare_component_items(
1020 p_revised_item IN inv_ebi_revised_item_obj
1021 ,p_from_item_id IN NUMBER
1022 ,p_to_item_id IN NUMBER
1023 ,p_from_sequence_id IN NUMBER
1024 ,p_reference_org_id IN NUMBER
1025 ,p_view_scope IN VARCHAR2 := 'ALL'
1026 ,p_implementation_scope IN VARCHAR2 := 'ALL'
1027 ,p_as_of_date IN DATE
1028 ,x_revised_item OUT NOCOPY inv_ebi_revised_item_obj
1029 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
1030 ) IS
1031 l_unit_assembly VARCHAR2(1);
1032 l_bom_item_type NUMBER;
1033 l_base_item_flag NUMBER;
1034 i NUMBER := 0;
1035
1036 CURSOR c_copied_comps (
1037 p_unit_assembly IN VARCHAR2,
1038 p_itm_type IN NUMBER,
1039 p_base_item_flag IN NUMBER,
1040 p_unit_number IN VARCHAR2
1041 )
1042 IS
1043 SELECT
1044 msi.concatenated_segments component_item_name,
1045 bic.component_item_id,
1046 bic.operation_seq_num
1047 FROM bom_inventory_components bic,
1048 mtl_system_items_kfv msi
1049 WHERE bic.bill_sequence_id = p_from_sequence_id
1050 AND bic.component_item_id = msi.inventory_item_id
1051 AND bic.component_item_id <> p_to_item_id
1052 AND NVL (bic.eco_for_production, 2) = 2
1053 AND msi.organization_id = p_reference_org_id
1054 AND ((p_unit_assembly = 'N'
1055 AND ((UPPER(p_view_scope) = G_VIEW_SCOPE_ALL) -- ALL
1056 OR (UPPER(p_view_scope) = G_VIEW_SCOPE_CURRENT
1057 AND (effectivity_date <= p_as_of_date
1058 AND
1059 ( (disable_date > p_as_of_date
1060 AND disable_date > SYSDATE
1061 )
1062 OR disable_date IS NULL
1063 )
1064 )
1065 )
1066 OR -- CURRENT
1067 (UPPER(p_view_scope ) = G_VIEW_SCOPE_CURR_FUTURE
1068 AND
1069 ( (disable_date > p_as_of_date
1070 AND disable_date > SYSDATE
1071 )
1072 OR disable_date IS NULL
1073 )
1074 )
1075 ) -- CURRENT + FUTURE
1076 )
1077 OR (p_unit_assembly = 'Y'
1078 AND ((UPPER(p_view_scope) = G_VIEW_SCOPE_ALL) -- ALL
1079 OR (UPPER(p_view_scope) = G_VIEW_SCOPE_CURRENT
1080 AND disable_date IS NULL
1081 AND (from_end_item_unit_number <= p_unit_number
1082 AND (to_end_item_unit_number >=
1083 p_unit_number
1084 OR to_end_item_unit_number IS NULL
1085 )
1086 )
1087 )
1088 OR -- CURRENT
1089 (UPPER(p_view_scope) = G_VIEW_SCOPE_CURR_FUTURE
1090 AND disable_date IS NULL
1091 AND (to_end_item_unit_number >= p_unit_number
1092 OR to_end_item_unit_number IS NULL
1093 )
1094 )
1095 ) -- CURRENT + FUTURE
1096 )
1097 )
1098 AND ((p_base_item_flag = -1
1099 AND p_itm_type = 4
1100 AND msi.bom_item_type = 4
1101 )
1102 OR p_base_item_flag <> -1
1103 OR p_itm_type <> 4
1104 )
1105 AND (UPPER(p_implementation_scope) = G_VIEW_SCOPE_ALL OR
1106 (UPPER(p_implementation_scope) = G_IMPLEMENT_SCOPE_IMPLEMENT AND implementation_date IS NOT NULL) OR
1107 (UPPER(p_implementation_scope) = G_IMPLEMENT_SCOPE_UNIMPLEMENT AND implementation_date IS NULL));
1108
1109 CURSOR c_merged_component (
1110 p_new_comp_item IN inv_ebi_rev_comp_obj
1111 ,p_component_item_name IN VARCHAR2
1112 ,p_operation_sequence_number IN NUMBER
1113 ,p_bill_sequence_id IN NUMBER
1114 ) IS
1115 SELECT
1116 DECODE(p_new_comp_item ,NULL ,bic.disable_date ,DECODE(p_new_comp_item.disable_date
1117 ,fnd_api.g_miss_date ,bic.disable_date ,p_new_comp_item.disable_date)) disable_date
1118 ,DECODE(p_new_comp_item ,NULL ,NVL(bic.acd_type,1) ,DECODE(p_new_comp_item.acd_type
1119 ,fnd_api.g_miss_char ,NVL(bic.acd_type,1) ,p_new_comp_item.acd_type)) acd_type
1120 ,DECODE(p_new_comp_item ,NULL ,bic.basis_type ,DECODE(p_new_comp_item.basis_type
1121 ,fnd_api.g_miss_num ,bic.basis_type ,p_new_comp_item.basis_type)) basis_type
1122 ,DECODE(p_new_comp_item ,NULL ,bic.component_quantity ,DECODE(p_new_comp_item.quantity_per_assembly
1123 ,fnd_api.g_miss_num ,bic.component_quantity ,p_new_comp_item.quantity_per_assembly )) quantity_per_assembly
1124 ,DECODE(p_new_comp_item ,NULL ,bic.component_quantity ,DECODE(p_new_comp_item.inverse_quantity
1125 ,fnd_api.g_miss_num ,bic.component_quantity ,p_new_comp_item.inverse_quantity )) inverse_quantity
1126 ,DECODE(p_new_comp_item ,NULL ,bic.include_in_cost_rollup ,DECODE(p_new_comp_item.include_in_cost_rollup
1127 ,fnd_api.g_miss_num ,bic.include_in_cost_rollup ,p_new_comp_item.include_in_cost_rollup)) include_in_cost_rollup
1128 ,DECODE(p_new_comp_item ,NULL ,bic.wip_supply_type ,DECODE(p_new_comp_item.wip_supply_type
1129 ,fnd_api.g_miss_num ,bic.wip_supply_type ,p_new_comp_item.wip_supply_type)) wip_supply_type
1130 ,DECODE(p_new_comp_item ,NULL ,bic.so_basis ,DECODE(p_new_comp_item.so_basis
1131 ,fnd_api.g_miss_num ,bic.so_basis ,p_new_comp_item.so_basis)) so_basis
1132 ,DECODE(p_new_comp_item ,NULL ,bic.optional ,DECODE(p_new_comp_item.optional
1133 ,fnd_api.g_miss_num ,bic.optional ,p_new_comp_item.optional)) optional
1134 ,DECODE(p_new_comp_item ,NULL ,bic.mutually_exclusive_options ,DECODE(p_new_comp_item.mutually_exclusive
1135 ,fnd_api.g_miss_num ,bic.mutually_exclusive_options ,p_new_comp_item.mutually_exclusive)) mutually_exclusive
1136 ,DECODE(p_new_comp_item ,NULL ,bic.check_atp ,DECODE(p_new_comp_item.check_atp
1137 ,fnd_api.g_miss_num ,bic.check_atp ,p_new_comp_item.check_atp)) check_atp
1138 ,DECODE(p_new_comp_item ,NULL ,bic.shipping_allowed ,DECODE(p_new_comp_item.shipping_allowed
1139 ,fnd_api.g_miss_num ,bic.shipping_allowed ,p_new_comp_item.shipping_allowed)) shipping_allowed
1140 ,DECODE(p_new_comp_item ,NULL ,bic.required_to_ship ,DECODE(p_new_comp_item.required_to_ship
1141 ,fnd_api.g_miss_num ,bic.required_to_ship ,p_new_comp_item.required_to_ship)) required_to_ship
1142 ,DECODE(p_new_comp_item ,NULL ,bic.required_for_revenue ,DECODE(p_new_comp_item.required_for_revenue
1143 ,fnd_api.g_miss_num ,bic.required_for_revenue ,p_new_comp_item.required_for_revenue)) required_for_revenue
1144 ,DECODE(p_new_comp_item ,NULL ,bic.include_on_ship_docs ,DECODE(p_new_comp_item.include_on_ship_docs
1145 ,fnd_api.g_miss_num ,bic.include_on_ship_docs ,p_new_comp_item.include_on_ship_docs)) include_on_ship_docs
1146 ,DECODE(p_new_comp_item ,NULL ,bic.quantity_related ,DECODE(p_new_comp_item.quantity_related
1147 ,fnd_api.g_miss_num ,bic.quantity_related ,p_new_comp_item.quantity_related)) quantity_related
1148 ,DECODE(p_new_comp_item ,NULL ,bic.supply_subinventory ,DECODE(p_new_comp_item.supply_subinventory
1149 ,fnd_api.g_miss_char ,bic.supply_subinventory ,p_new_comp_item.supply_subinventory)) supply_subinventory
1150 ,DECODE(p_new_comp_item ,NULL ,bic.attribute_category ,DECODE(p_new_comp_item.attribute_category
1151 ,fnd_api.g_miss_char ,bic.attribute_category ,p_new_comp_item.attribute_category)) attribute_category
1152 ,DECODE(p_new_comp_item ,NULL ,bic.attribute1 ,DECODE(p_new_comp_item.attribute1
1153 ,fnd_api.g_miss_char ,bic.attribute1 ,p_new_comp_item.attribute1)) attribute1
1154 ,DECODE(p_new_comp_item ,NULL ,bic.attribute2 ,DECODE(p_new_comp_item.attribute2
1155 ,fnd_api.g_miss_char ,bic.attribute2 ,p_new_comp_item.attribute2)) attribute2
1156 ,DECODE(p_new_comp_item ,NULL ,bic.attribute3 ,DECODE(p_new_comp_item.attribute3
1157 ,fnd_api.g_miss_char ,bic.attribute3 ,p_new_comp_item.attribute3)) attribute3
1158 ,DECODE(p_new_comp_item ,NULL ,bic.attribute4 ,DECODE(p_new_comp_item.attribute4
1159 ,fnd_api.g_miss_char ,bic.attribute4 ,p_new_comp_item.attribute4)) attribute4
1160 ,DECODE(p_new_comp_item ,NULL ,bic.attribute5 ,DECODE(p_new_comp_item.attribute5
1161 ,fnd_api.g_miss_char ,bic.attribute5 ,p_new_comp_item.attribute5)) attribute5
1162 ,DECODE(p_new_comp_item ,NULL ,bic.attribute6 ,DECODE(p_new_comp_item.attribute6
1163 ,fnd_api.g_miss_char ,bic.attribute6 ,p_new_comp_item.attribute6)) attribute6
1164 ,DECODE(p_new_comp_item ,NULL ,bic.attribute7 ,DECODE(p_new_comp_item.attribute7
1165 ,fnd_api.g_miss_char ,bic.attribute7 ,p_new_comp_item.attribute7)) attribute7
1166 ,DECODE(p_new_comp_item ,NULL ,bic.attribute8 ,DECODE(p_new_comp_item.attribute8
1167 ,fnd_api.g_miss_char ,bic.attribute8 ,p_new_comp_item.attribute8)) attribute8
1168 ,DECODE(p_new_comp_item ,NULL ,bic.attribute9 ,DECODE(p_new_comp_item.attribute9
1169 ,fnd_api.g_miss_char ,bic.attribute9 ,p_new_comp_item.attribute9)) attribute9
1170 ,DECODE(p_new_comp_item ,NULL ,bic.attribute10 ,DECODE(p_new_comp_item.attribute10
1171 ,fnd_api.g_miss_char ,bic.attribute10 ,p_new_comp_item.attribute10)) attribute10
1172 ,DECODE(p_new_comp_item ,NULL ,bic.attribute11 ,DECODE(p_new_comp_item.attribute11
1173 ,fnd_api.g_miss_char ,bic.attribute11 ,p_new_comp_item.attribute11)) attribute11
1174 ,DECODE(p_new_comp_item ,NULL ,bic.attribute12 ,DECODE(p_new_comp_item.attribute12
1175 ,fnd_api.g_miss_char ,bic.attribute12 ,p_new_comp_item.attribute12)) attribute12
1176 ,DECODE(p_new_comp_item ,NULL ,bic.attribute13 ,DECODE(p_new_comp_item.attribute13
1177 ,fnd_api.g_miss_char ,bic.attribute13 ,p_new_comp_item.attribute13)) attribute13
1178 ,DECODE(p_new_comp_item ,NULL ,bic.attribute14 ,DECODE(p_new_comp_item.attribute14
1179 ,fnd_api.g_miss_char ,bic.attribute14 ,p_new_comp_item.attribute14)) attribute14
1180 ,DECODE(p_new_comp_item ,NULL ,bic.attribute15 ,DECODE(p_new_comp_item.attribute15
1181 ,fnd_api.g_miss_char ,bic.attribute15 ,p_new_comp_item.attribute15)) attribute15
1182 ,DECODE(p_new_comp_item ,NULL ,bic.from_end_item_unit_number ,DECODE(p_new_comp_item.from_end_item_unit_number
1183 ,fnd_api.g_miss_char ,bic.from_end_item_unit_number ,p_new_comp_item.from_end_item_unit_number)) from_end_item_unit_number
1184 ,DECODE(p_new_comp_item ,NULL ,bic.to_end_item_unit_number ,DECODE(p_new_comp_item.to_end_item_unit_number
1185 ,fnd_api.g_miss_char ,bic.to_end_item_unit_number ,p_new_comp_item.to_end_item_unit_number)) to_end_item_unit_number
1186 ,DECODE(p_new_comp_item ,NULL ,bic.enforce_int_requirements ,DECODE(p_new_comp_item.enforce_int_requirements
1187 ,fnd_api.g_miss_char ,bic.enforce_int_requirements ,p_new_comp_item.enforce_int_requirements)) enforce_int_requirements
1188 ,DECODE(p_new_comp_item ,NULL ,bic.auto_request_material ,DECODE(p_new_comp_item.auto_request_material
1189 ,fnd_api.g_miss_char ,bic.auto_request_material ,p_new_comp_item.auto_request_material)) auto_request_material
1190 ,DECODE(p_new_comp_item ,NULL ,bic.suggested_vendor_name ,DECODE(p_new_comp_item.suggested_vendor_name
1191 ,fnd_api.g_miss_char ,bic.suggested_vendor_name ,p_new_comp_item.suggested_vendor_name)) suggested_vendor_name
1192 ,DECODE(p_new_comp_item ,NULL ,bic.unit_price ,DECODE(p_new_comp_item.unit_price
1193 ,fnd_api.g_miss_num ,bic.unit_price ,p_new_comp_item.unit_price)) unit_price
1194 ,DECODE(p_new_comp_item ,NULL ,bic.original_system_reference ,DECODE(p_new_comp_item.original_system_reference
1195 ,fnd_api.g_miss_num ,bic.original_system_reference ,p_new_comp_item.original_system_reference)) original_system_reference
1196 ,DECODE(p_new_comp_item ,NULL ,SYSDATE ,DECODE(p_new_comp_item.start_effective_date
1197 ,fnd_api.g_miss_date ,bic.effectivity_date ,p_new_comp_item.start_effective_date)) start_effective_date
1198 ,DECODE(p_new_comp_item ,NULL ,bic.item_num ,DECODE(p_new_comp_item.item_sequence_number
1199 ,fnd_api.g_miss_num ,bic.item_num ,p_new_comp_item.item_sequence_number)) item_sequence_number
1200 ,DECODE(p_new_comp_item ,NULL ,bic.planning_factor ,DECODE(p_new_comp_item.planning_percent
1201 ,fnd_api.g_miss_num ,bic.planning_factor ,p_new_comp_item.planning_percent)) planning_percent
1202 ,DECODE(p_new_comp_item ,NULL ,bic.component_yield_factor ,DECODE(p_new_comp_item.projected_yield
1203 ,fnd_api.g_miss_num ,bic.component_yield_factor ,p_new_comp_item.projected_yield)) projected_yield
1204 ,DECODE(p_new_comp_item ,NULL ,bic.high_quantity ,DECODE(p_new_comp_item.maximum_allowed_quantity
1205 ,fnd_api.g_miss_num ,bic.high_quantity ,p_new_comp_item.maximum_allowed_quantity)) maximum_allowed_quantity
1206 ,DECODE(p_new_comp_item ,NULL ,bic.low_quantity ,DECODE(p_new_comp_item.minimum_allowed_quantity
1207 ,fnd_api.g_miss_num ,bic.low_quantity ,p_new_comp_item.minimum_allowed_quantity)) minimum_allowed_quantity
1208 ,DECODE(p_new_comp_item ,NULL ,bic.component_remarks ,DECODE(p_new_comp_item.comments
1209 ,fnd_api.g_miss_char ,component_remarks ,p_new_comp_item.comments)) comments
1210 ,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.new_effectivity_date) new_effectivity_date
1211 ,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.old_effectivity_date) old_effectivity_date
1212 ,DECODE(p_new_comp_item ,NULL ,1 ,p_new_comp_item.old_operation_sequence_number) old_operation_sequence_number
1213 ,DECODE(p_new_comp_item ,NULL ,1 ,p_new_comp_item.new_operation_sequence_number) new_operation_sequence_number
1214 ,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.location_name) location_name
1215 ,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.cancel_comments) cancel_comments
1216 ,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.old_from_end_item_unit_number) old_from_end_item_unit_number
1217 ,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.new_from_end_item_unit_number) new_from_end_item_unit_number
1218 ,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.new_routing_revision) new_routing_revision
1219 ,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.return_status) return_status
1220 FROM
1221 bom_inventory_components bic,
1222 mtl_system_items_kfv it
1223 WHERE
1224 bic.component_item_id = it.inventory_item_id AND
1225 it.organization_id = p_reference_org_id AND
1226 it.concatenated_segments = p_component_item_name AND
1227 bic.operation_seq_num = nvl(p_operation_sequence_number,1) AND
1228 bic.bill_sequence_id = p_bill_sequence_id;
1229
1230 l_merged_comp c_merged_component%ROWTYPE;
1231 l_copied_comp c_copied_comps%ROWTYPE;
1232 l_revised_item inv_ebi_revised_item_obj;
1233 l_found BOOLEAN;
1234 l_revised_comp_tbl inv_ebi_rev_comp_tbl;
1235 l_revised_comp_count NUMBER := 1;
1236 l_output_status inv_ebi_output_status;
1237 BEGIN
1238 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
1239 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
1240 INV_EBI_UTIL.debug_line('STEP: 10 START INSIDE INV_EBI_CHANGE_ORDER_HELPER.prepare_component_items');
1241 IF (bom_eamutil.enabled = 'Y'
1242 AND bom_eamutil.serial_effective_item (item_id => p_from_item_id,
1243 org_id => p_reference_org_id
1244 ) = 'Y'
1245 )
1246 OR (pjm_unit_eff.enabled = 'Y'
1247 AND pjm_unit_eff.unit_effective_item
1248 (x_item_id => p_from_item_id,
1249 x_organization_id => p_reference_org_id
1250 ) = 'Y'
1251 )
1252 THEN
1253 l_unit_assembly := 'Y';
1254 ELSE
1255 l_unit_assembly := 'N';
1256 END IF;
1257
1258
1259 SELECT
1260 bom_item_type
1261 ,DECODE (base_item_id, NULL, -1, 0) base_item_id
1262 INTO
1263 l_bom_item_type
1264 ,l_base_item_flag
1265 FROM
1266 mtl_system_items_b
1267 WHERE
1268 inventory_item_id = p_from_item_id AND
1269 organization_id = p_reference_org_id;
1270
1271 l_revised_item := p_revised_item;
1272 IF p_revised_item.component_item_tbl IS NOT NULL THEN
1273 l_revised_comp_tbl := inv_ebi_rev_comp_tbl();
1274 FOR i IN 1..p_revised_item.component_item_tbl.COUNT LOOP
1275 IF p_revised_item.component_item_tbl(i).transaction_type <> ENG_GLOBALS.g_opr_delete THEN
1276 l_revised_comp_tbl.EXTEND(1);
1277 l_revised_comp_tbl(l_revised_comp_count) := p_revised_item.component_item_tbl(i);
1278 l_revised_comp_count := l_revised_comp_count + 1;
1279 END IF;
1280 END LOOP;
1281 l_revised_item.component_item_tbl := l_revised_comp_tbl;
1282 END IF;
1283
1284
1285 OPEN c_copied_comps(p_itm_type => l_bom_item_type
1286 ,p_base_item_flag => l_base_item_flag
1287 ,p_unit_number => NULL
1288 ,p_unit_assembly => l_unit_assembly) ;
1289 LOOP
1290 FETCH c_copied_comps INTO l_copied_comp;
1291 EXIT WHEN c_copied_comps%NOTFOUND;
1292 l_Found := FALSE;
1293 IF p_revised_item.component_item_tbl IS NOT NULL THEN
1294 FOR i IN 1..p_revised_item.component_item_tbl.COUNT LOOP
1295 IF l_copied_comp.component_item_name = p_revised_item.component_item_tbl(i).component_item_name
1296 AND l_copied_comp.operation_seq_num = p_revised_item.component_item_tbl(i).operation_sequence_number THEN
1297 l_Found := TRUE;
1298 END IF;
1299 END LOOP;
1300 END IF;
1301
1302 IF NOT l_Found THEN
1303 OPEN c_merged_component(p_new_comp_item => NULL
1304 ,p_component_item_name => l_copied_comp.component_item_name
1305 ,p_operation_sequence_number => l_copied_comp.operation_seq_num
1306 ,p_bill_sequence_id => p_from_sequence_id);
1307 FETCH c_merged_component INTO l_Merged_Comp;
1308 IF l_revised_item.component_item_tbl IS NULL THEN
1309 l_revised_item.component_item_tbl := inv_ebi_rev_comp_tbl();
1310 END IF;
1311 l_revised_item.component_item_tbl.EXTEND(1);
1312
1313 l_revised_item.component_item_tbl(l_revised_item.component_item_tbl.COUNT) := inv_ebi_rev_comp_obj(
1314 p_revised_item.start_effective_date
1315 ,p_revised_item.start_effective_date
1316 ,l_merged_comp.disable_date
1317 ,l_copied_comp.operation_seq_num
1318 ,l_copied_comp.component_item_name
1319 ,NULL
1320 ,NULL
1321 ,l_merged_comp.acd_type
1322 ,l_merged_comp.old_effectivity_date
1323 ,l_merged_comp.old_operation_sequence_number
1324 ,l_merged_comp.new_operation_sequence_number
1325 ,NULL
1326 ,l_merged_comp.basis_type
1327 ,l_merged_comp.quantity_per_assembly
1328 ,l_merged_comp.inverse_quantity
1329 ,l_merged_comp.planning_percent
1330 ,l_merged_comp.projected_yield
1331 ,l_merged_comp.include_in_cost_rollup
1332 ,l_merged_comp.wip_supply_type
1333 ,l_merged_comp.so_basis
1334 ,l_merged_comp.optional
1335 ,l_merged_comp.mutually_exclusive
1336 ,l_merged_comp.check_atp
1337 ,l_merged_comp.shipping_allowed
1338 ,l_merged_comp.required_to_ship
1339 ,l_merged_comp.required_for_revenue
1340 ,l_merged_comp.include_on_ship_docs
1341 ,l_merged_comp.quantity_related
1342 ,l_merged_comp.supply_subinventory
1343 ,l_merged_comp.location_name
1344 ,l_merged_comp.minimum_allowed_quantity
1345 ,l_merged_comp.maximum_allowed_quantity
1346 ,l_merged_comp.comments
1347 ,l_merged_comp.cancel_comments
1348 ,l_merged_comp.attribute_category
1349 ,l_merged_comp.attribute1
1350 ,l_merged_comp.attribute2
1351 ,l_merged_comp.attribute3
1352 ,l_merged_comp.attribute4
1353 ,l_merged_comp.attribute5
1354 ,l_merged_comp.attribute6
1355 ,l_merged_comp.attribute7
1356 ,l_merged_comp.attribute8
1357 ,l_merged_comp.attribute9
1358 ,l_merged_comp.attribute10
1359 ,l_merged_comp.attribute11
1360 ,l_merged_comp.attribute12
1361 ,l_merged_comp.attribute13
1362 ,l_merged_comp.attribute14
1363 ,l_merged_comp.attribute15
1364 ,l_merged_comp.from_end_item_unit_number
1365 ,l_merged_comp.old_from_end_item_unit_number
1366 ,l_merged_comp.new_from_end_item_unit_number
1367 ,l_merged_comp.to_end_item_unit_number
1368 ,l_merged_comp.new_routing_revision
1369 ,l_merged_comp.enforce_int_requirements
1370 ,l_merged_comp.auto_request_material
1371 ,l_merged_comp.suggested_vendor_name
1372 ,l_merged_comp.unit_price
1373 ,l_merged_comp.original_system_reference
1374 ,l_merged_comp.return_status
1375 ,ENG_GLOBALS.g_opr_create
1376 ,NULL
1377 ,NULL
1378 ,NULL
1379 ,NULL
1380 ,NULL
1381 ,NULL);
1382
1383 CLOSE c_merged_component;
1384 END IF;
1385 END LOOP;
1386 CLOSE c_copied_comps;
1387
1388 IF l_revised_item.component_item_tbl IS NOT NULL THEN
1389 FOR i IN 1..l_revised_item.component_item_tbl.COUNT LOOP
1390 IF l_revised_item.component_item_tbl(i).transaction_type = ENG_GLOBALS.g_opr_update THEN
1391 OPEN c_merged_component(p_new_comp_item => l_revised_item.component_item_tbl(i)
1392 ,p_component_item_name => l_revised_item.component_item_tbl(i).component_item_name
1393 ,p_operation_sequence_number => l_revised_item.component_item_tbl(i).operation_sequence_number
1394 ,p_bill_sequence_id => p_from_sequence_id);
1395 FETCH c_merged_component INTO l_Merged_Comp;
1396 l_revised_item.component_item_tbl(i) := inv_ebi_rev_comp_obj(
1397 l_merged_comp.start_effective_date
1398 ,l_merged_comp.new_effectivity_date
1399 ,l_merged_comp.disable_date
1400 ,l_revised_item.component_item_tbl(i).operation_sequence_number
1401 ,l_revised_item.component_item_tbl(i).component_item_name
1402 ,l_revised_item.component_item_tbl(i).substitute_component_tbl
1403 ,l_revised_item.component_item_tbl(i).reference_designator_tbl
1404 ,l_merged_comp.acd_type
1405 ,l_merged_comp.old_effectivity_date
1406 ,l_merged_comp.old_operation_sequence_number
1407 ,l_merged_comp.new_operation_sequence_number
1408 ,l_merged_comp.item_sequence_number
1409 ,l_merged_comp.basis_type
1410 ,l_merged_comp.quantity_per_assembly
1411 ,l_merged_comp.inverse_quantity
1412 ,l_merged_comp.planning_percent
1413 ,l_merged_comp.projected_yield
1414 ,l_merged_comp.include_in_cost_rollup
1415 ,l_merged_comp.wip_supply_type
1416 ,l_merged_comp.so_basis
1417 ,l_merged_comp.optional
1418 ,l_merged_comp.mutually_exclusive
1419 ,l_merged_comp.check_atp
1420 ,l_merged_comp.shipping_allowed
1421 ,l_merged_comp.required_to_ship
1422 ,l_merged_comp.required_for_revenue
1423 ,l_merged_comp.include_on_ship_docs
1424 ,l_merged_comp.quantity_related
1425 ,l_merged_comp.supply_subinventory
1426 ,l_merged_comp.location_name
1427 ,l_merged_comp.minimum_allowed_quantity
1428 ,l_merged_comp.maximum_allowed_quantity
1429 ,l_merged_comp.comments
1430 ,l_merged_comp.cancel_comments
1431 ,l_merged_comp.attribute_category
1432 ,l_merged_comp.attribute1
1433 ,l_merged_comp.attribute2
1434 ,l_merged_comp.attribute3
1435 ,l_merged_comp.attribute4
1436 ,l_merged_comp.attribute5
1437 ,l_merged_comp.attribute6
1438 ,l_merged_comp.attribute7
1439 ,l_merged_comp.attribute8
1440 ,l_merged_comp.attribute9
1441 ,l_merged_comp.attribute10
1442 ,l_merged_comp.attribute11
1443 ,l_merged_comp.attribute12
1444 ,l_merged_comp.attribute13
1445 ,l_merged_comp.attribute14
1446 ,l_merged_comp.attribute15
1447 ,l_merged_comp.from_end_item_unit_number
1448 ,l_merged_comp.old_from_end_item_unit_number
1449 ,l_merged_comp.new_from_end_item_unit_number
1450 ,l_merged_comp.to_end_item_unit_number
1451 ,l_merged_comp.new_routing_revision
1452 ,l_merged_comp.enforce_int_requirements
1453 ,l_merged_comp.auto_request_material
1454 ,l_merged_comp.suggested_vendor_name
1455 ,l_merged_comp.unit_price
1456 ,l_merged_comp.original_system_reference
1457 ,l_merged_comp.return_status
1458 ,ENG_GLOBALS.g_opr_create
1459 ,NULL
1460 ,l_revised_item.component_item_tbl(i).component_revision_uda
1461 ,NULL
1462 ,NULL
1463 ,NULL
1464 ,NULL);
1465 CLOSE c_merged_component;
1466 END IF;
1467 END LOOP;
1468 END IF;
1469 INV_EBI_UTIL.debug_line('STEP: 20 END INSIDE INV_EBI_CHANGE_ORDER_HELPER.prepare_component_items');
1470 x_revised_item := l_revised_item;
1471
1472
1473 EXCEPTION
1474 WHEN FND_API.g_exc_unexpected_error THEN
1475 IF c_copied_comps%ISOPEN THEN
1476 CLOSE c_copied_comps;
1477 END IF;
1478 IF c_merged_component%ISOPEN THEN
1479 CLOSE c_merged_component;
1480 END IF;
1481 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
1482 IF(x_out.output_status.msg_data IS NULL) THEN
1483 fnd_msg_pub.count_and_get(
1484 p_encoded => FND_API.g_false
1485 ,p_count => x_out.output_status.msg_count
1486 ,p_data => x_out.output_status.msg_data
1487 );
1488 END IF;
1489 WHEN OTHERS THEN
1490 IF c_copied_comps%ISOPEN THEN
1491 CLOSE c_copied_comps;
1492 END IF;
1493 IF c_merged_component%ISOPEN THEN
1494 CLOSE c_merged_component;
1495 END IF;
1496 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
1497 IF (x_out.output_status.msg_data IS NOT NULL) THEN
1498 x_out.output_status.msg_data := x_out.output_status.msg_data||' ->INV_EBI_CHANGE_ORDER_HELPER.prepare_component_items ';
1499 ELSE
1500 x_out.output_status.msg_data := SQLERRM||'INV_EBI_CHANGE_ORDER_HELPER.prepare_component_items ';
1501 END IF;
1502 END prepare_component_items;
1503
1504 /************************************************************************************
1505 -- API name : Is_BOM_Exists
1506 -- Type : Private
1507 -- Function :
1508 ************************************************************************************/
1509
1510 FUNCTION Is_BOM_Exists(
1511 p_Item_Number IN VARCHAR2
1512 ,p_Organization_Code IN VARCHAR2
1513 ,p_alternate_bom_code IN VARCHAR2
1514 ) RETURN VARCHAR2 IS
1515 l_Count NUMBER := 0;
1516 BEGIN
1517
1518 SELECT
1519 COUNT(1)
1520 INTO
1521 l_Count
1522 FROM
1523 bom_bill_of_materials bb,
1524 mtl_system_items_kfv it,
1525 mtl_parameters mp
1526 WHERE
1527 bb.assembly_item_id = it.inventory_item_id AND
1528 it.organization_id = bb.organization_id AND
1529 bb.organization_id = mp.organization_id AND
1530 mp.organization_code = p_Organization_Code AND
1531 it.concatenated_segments = p_Item_Number AND
1532 ((p_alternate_bom_code IS NULL AND bb.alternate_bom_designator IS NULL) OR
1533 (bb.alternate_bom_designator = p_alternate_bom_code));
1534
1535 IF l_Count = 1 THEN
1536 RETURN FND_API.g_true;
1537 END IF;
1538
1539 RETURN FND_API.g_false;
1540 EXCEPTION
1541 WHEN OTHERS THEN
1542 RETURN FND_API.g_false;
1543 END Is_BOM_Exists;
1544
1545 /************************************************************************************
1546 -- API name : is_new_revision_exists
1547 -- Type : Private
1548 -- Function : This api is used to find if a change already exists for the
1549 -- revsied items revision.
1550 -- Bug 7119898
1551 ************************************************************************************/
1552
1553 FUNCTION is_new_revision_exists(
1554 p_item_number IN VARCHAR2
1555 ,p_revision IN VARCHAR2
1556 ,p_org_code IN VARCHAR2
1557 ) RETURN VARCHAR2 IS
1558
1559 l_Count NUMBER := 0;
1560
1561 BEGIN
1562
1563 SELECT COUNT(1) INTO l_Count
1564 FROM
1565 eng_revised_items eri,
1566 mtl_system_items_kfv msi,
1567 mtl_parameters mp
1568 WHERE
1569 eri.revised_item_id = msi.inventory_item_id AND
1570 msi.organization_id = eri.organization_id AND
1571 eri.organization_id = mp.organization_id AND
1572 mp.organization_code = p_org_code AND
1573 msi.concatenated_segments = p_item_number AND
1574 eri.new_item_revision = p_revision ;
1575
1576 IF l_Count >=1 THEN
1577 RETURN FND_API.g_true;
1578 END IF;
1579
1580 RETURN FND_API.g_false;
1581 EXCEPTION
1582 WHEN OTHERS THEN
1583 RETURN FND_API.g_false;
1584 END is_new_revision_exists;
1585
1586 /*******************************************************************************
1587 API name : is_child_org
1588 Type : Private
1589 Purpose : Checks if the organization is master org or child org.
1590 --Bug 7197943
1591 ********************************************************************************/
1592 FUNCTION is_child_org (
1593 p_organization_id IN NUMBER
1594 ) RETURN VARCHAR2 IS
1595
1596 l_master_org NUMBER;
1597
1598 BEGIN
1599
1600 SELECT master_organization_id INTO l_master_org
1601 FROM mtl_parameters
1602 WHERE organization_id= p_organization_id;
1603
1604 IF(l_master_org <> p_organization_id) THEN
1605 RETURN FND_API.g_true;
1606 ELSE
1607 RETURN FND_API.g_false;
1608 END IF;
1609
1610 EXCEPTION
1611 WHEN OTHERS THEN
1612 RETURN NULL;
1613 END is_child_org;
1614
1615 /************************************************************************************
1616 -- API name : get_bill_sequence_id
1617 -- Type : Private
1618 -- Function : This function returns the bill sequence id corresponding to
1619 -- the given assembly item id,org id and alternate bom code.
1620 ************************************************************************************/
1621
1622 FUNCTION get_bill_sequence_id(
1623 p_assembly_item_id IN NUMBER ,
1624 p_organization_id IN NUMBER,
1625 p_alternate_bom_code IN VARCHAR2
1626 ) RETURN NUMBER IS
1627
1628 l_bill_sequence_id NUMBER;
1629
1630 BEGIN
1631
1632 SELECT bill_sequence_id INTO l_bill_sequence_id
1633 FROM
1634 bom_bill_of_materials
1635 WHERE
1636 assembly_item_id = p_assembly_item_id AND
1637 organization_id = p_organization_id AND
1638 NVL(alternate_bom_designator, 'NONE') =
1639 decode(p_alternate_bom_code,FND_API.G_MISS_CHAR,'NONE',NULL,'NONE',p_alternate_bom_code);
1640
1641 RETURN l_bill_sequence_id;
1642
1643 EXCEPTION
1644 WHEN OTHERS THEN
1645 RETURN NULL;
1646 END get_bill_sequence_id;
1647
1648 /************************************************************************************
1649 -- API name : process_replicate_bom
1650 -- Type : Public
1651 -- Function :
1652 -- The following processing is done in this API
1653 -- 1. Check if the item has a BOM defined in the currentcontext organization
1654 -- 2. Also check that the reference org has a BOM defined.
1655 -- 3. Modify the component tbl as per the following logic
1656 * Remove the components which have transaction type as 'DELETE'
1657 * Find the components which are present only in the reference organization
1658 and add them to the list with transaction type as 'CREATE'
1659 * Modify the transaction type of the components which have 'UPDATE' as the
1660 transaction type to 'CREATE'
1661 -- 4. For each component item the above processing will be done for substitute
1662 components and reference designators
1663 ************************************************************************************/
1664 PROCEDURE process_replicate_bom(
1665 p_eco_obj IN inv_ebi_eco_obj
1666 ,x_eco_obj OUT NOCOPY inv_ebi_eco_obj
1667 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
1668 ) IS
1669 l_revised_item inv_ebi_revised_item_obj;
1670 l_revised_item1 inv_ebi_revised_item_obj;
1671 l_context_org_bom_exists VARCHAR2(1) := FND_API.g_false;
1672 l_ref_org_bom_exists VARCHAR2(1) := FND_API.g_false;
1673 l_context_org_code mtl_parameters.organization_code%TYPE;
1674 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
1675 l_component_item inv_ebi_rev_comp_obj;
1676 l_component_item1 inv_ebi_rev_comp_obj;
1677 l_eco_obj inv_ebi_eco_obj;
1678 l_from_item_id NUMBER;
1679 l_to_item_id NUMBER;
1680 l_from_sequence_id NUMBER;
1681 l_from_org_id NUMBER;
1682 l_to_org_id NUMBER;
1683 l_view_scope VARCHAR2(30) ;
1684 l_impl_scope VARCHAR2(30) ;
1685 l_config_view_scope VARCHAR2(30) := 'ALL';
1686 l_config_impl_scope VARCHAR2(30) := 'ALL';
1687 l_return_status VARCHAR2(10);
1688 l_msg_Count NUMBER;
1689 l_reference_item_num mtl_system_items_kfv.concatenated_segments%TYPE;
1690 l_reference_org_code mtl_parameters.organization_code%TYPE;
1691 l_as_of_date DATE;
1692 l_alternate_bom_code bom_bill_of_materials.alternate_bom_designator%TYPE;
1693 l_output_status inv_ebi_output_status;
1694
1695 BEGIN
1696 SAVEPOINT process_replicate_bom_save_pnt;
1697 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
1698 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
1699 INV_EBI_UTIL.debug_line('STEP: 10 START INSIDE INV_EBI_CHANGE_ORDER_HELPER.process_replicate_bom');
1700 l_context_org_code:= p_eco_obj.eco_change_order_type.organization_code;
1701 l_eco_obj := p_eco_obj;
1702
1703
1704 l_config_view_scope := INV_EBI_UTIL.get_config_param_value ( p_config_tbl => p_eco_obj.name_value_tbl
1705 ,p_config_param_name => 'REPLICATE_BOM_VIEW_SCOPE'
1706 );
1707
1708
1709 l_config_impl_scope := INV_EBI_UTIL.get_config_param_value ( p_config_tbl => p_eco_obj.name_value_tbl
1710 ,p_config_param_name => 'REPLICATE_BOM_IMPLEMENTATION_SCOPE'
1711 );
1712 IF l_eco_obj.eco_revised_item_type IS NOT NULL THEN
1713 FOR i IN 1..l_eco_obj.eco_revised_item_type.COUNT LOOP
1714 l_revised_item := l_eco_obj.eco_revised_item_type(i);
1715
1716 IF (l_revised_item.orignal_bom_reference IS NOT NULL AND
1717 (l_revised_item.orignal_bom_reference.organization_id IS NOT NULL OR
1718 l_revised_item.orignal_bom_reference.organization_code IS NOT NULL )) THEN
1719
1720 l_from_org_id := l_revised_item.orignal_bom_reference.organization_id;
1721 l_reference_org_code := l_revised_item.orignal_bom_reference.organization_code;
1722 IF l_from_org_id IS NULL OR l_from_org_id = fnd_api.g_miss_num THEN
1723 l_from_org_id := INV_EBI_ITEM_HELPER.get_organization_id( p_organization_code => l_reference_org_code);
1724 ELSIF l_reference_org_code IS NULL OR l_reference_org_code = fnd_api.g_miss_char THEN
1725 SELECT
1726 organization_code
1727 INTO
1728 l_reference_org_code
1729 FROM
1730 mtl_parameters
1731 WHERE
1732 organization_id = l_from_org_id;
1733 END IF;
1734
1735 l_reference_item_num := l_revised_item.revised_item_name;
1736 IF l_revised_item.orignal_bom_reference.item_name IS NOT NULL THEN
1737 l_reference_item_num := l_revised_item.orignal_bom_reference.item_name;
1738 ELSIF l_revised_item.orignal_bom_reference.inventory_item_id IS NOT NULL THEN
1739 l_from_item_id := l_revised_item.orignal_bom_reference.inventory_item_id;
1740 SELECT
1741 concatenated_segments
1742 INTO
1743 l_reference_item_num
1744 FROM
1745 mtl_system_items_kfv
1746 WHERE
1747 inventory_item_id = l_from_item_id AND
1748 organization_id = l_from_org_id;
1749 END IF;
1750
1751 IF l_from_item_id IS NULL THEN
1752 l_from_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id ( p_organization_id => l_from_org_id
1753 ,p_item_number => l_reference_item_num);
1754 END IF;
1755
1756 l_to_org_id := INV_EBI_ITEM_HELPER.get_organization_id ( p_organization_code => l_context_org_code);
1757 l_to_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id ( p_organization_id => l_to_org_id
1758 ,p_item_number => l_revised_item.revised_item_name) ;
1759 l_context_org_bom_exists := Is_BOM_Exists(
1760 p_item_number => l_revised_item.revised_item_name
1761 ,p_organization_code => l_context_org_code
1762 ,p_alternate_bom_code => l_revised_item.orignal_bom_reference.alternate_bom_code
1763 );
1764
1765 l_ref_org_bom_exists := Is_BOM_Exists(
1766 p_item_number => l_reference_item_num
1767 ,p_organization_code => l_reference_org_code
1768 ,p_alternate_bom_code => l_revised_item.orignal_bom_reference.alternate_bom_code
1769 );
1770 IF (NOT FND_API.To_Boolean(l_context_org_bom_exists)) AND FND_API.To_Boolean(l_ref_org_bom_exists) THEN
1771 SELECT
1772 bill_sequence_id
1773 INTO
1774 l_from_sequence_id
1775 FROM
1776 bom_bill_of_materials
1777 WHERE
1778 assembly_item_id = l_from_item_id AND
1779 organization_id = l_from_org_id AND
1780 ((l_revised_item.alternate_bom_code IS NULL AND alternate_bom_designator IS NULL) OR
1781 (alternate_bom_designator = l_revised_item.alternate_bom_code));
1782 l_view_scope := l_revised_item.orignal_bom_reference.view_scope;
1783 IF l_view_scope IS NULL OR l_view_scope = fnd_api.g_miss_char THEN
1784 l_view_scope := l_config_view_scope;
1785 END IF;
1786
1787 l_impl_scope := l_revised_item.orignal_bom_reference.implementation_scope;
1788 IF l_impl_scope IS NULL OR l_impl_scope = fnd_api.g_miss_char THEN
1789 l_impl_scope := l_config_impl_scope;
1790 END IF;
1791
1792 l_as_of_date := l_revised_item.orignal_bom_reference.as_of_date;
1793 IF l_as_of_date IS NULL OR l_as_of_date = fnd_api.g_miss_date THEN
1794 l_as_of_date := SYSDATE;
1795 END IF;
1796
1797 prepare_component_items (
1798 p_revised_item => l_eco_obj.eco_revised_item_type(i)
1799 ,p_from_item_id => l_from_item_id
1800 ,p_to_item_id => l_to_item_id
1801 ,p_from_sequence_id => l_from_sequence_id
1802 ,p_reference_org_id => l_from_org_id
1803 ,p_view_scope => l_view_scope
1804 ,p_implementation_scope => l_impl_scope
1805 ,p_as_of_date => l_as_of_date
1806 ,x_revised_item => l_revised_item
1807 ,x_out => x_out
1808 );
1809
1810 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
1811 RAISE FND_API.g_exc_unexpected_error;
1812 END IF;
1813
1814 IF l_revised_item.component_item_tbl IS NOT NULL THEN
1815 FOR j IN 1..l_revised_item.component_item_tbl.COUNT LOOP
1816 prepare_substitute_components (
1817 p_component_item => l_revised_item.component_item_tbl(j)
1818 ,p_from_sequence_id => l_from_sequence_id
1819 ,p_reference_org_id => l_from_org_id
1820 ,x_component_item => l_component_item
1821 ,x_out => x_out
1822 );
1823 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
1824 RAISE FND_API.g_exc_unexpected_error;
1825 END IF;
1826
1827 prepare_reference_designators (
1828 p_component_item => l_component_item
1829 ,p_from_sequence_id => l_from_sequence_id
1830 ,p_reference_org_id => l_from_org_id
1831 ,x_component_item => l_component_item1
1832 ,x_out => x_out
1833 );
1834 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
1835 RAISE FND_API.g_exc_unexpected_error;
1836 END IF;
1837
1838 l_revised_item.component_item_tbl(j) := l_component_item1;
1839 END LOOP;
1840 END IF;
1841 l_eco_obj.eco_revised_item_type(i) := l_revised_item;
1842 END IF;
1843 END IF;
1844 END LOOP;
1845 END IF;
1846 INV_EBI_UTIL.debug_line('STEP: 20 END INSIDE INV_EBI_CHANGE_ORDER_HELPER.process_replicate_bom');
1847 x_eco_obj := l_eco_obj;
1848
1849
1850 EXCEPTION
1851 WHEN FND_API.g_exc_unexpected_error THEN
1852 ROLLBACK TO process_replicate_bom_save_pnt;
1853 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
1854 IF(x_out.output_status.msg_data IS NULL) THEN
1855 fnd_msg_pub.count_and_get(
1856 p_encoded => FND_API.g_false
1857 ,p_count => x_out.output_status.msg_count
1858 ,p_data => x_out.output_status.msg_data
1859 );
1860 END IF;
1861 WHEN OTHERS THEN
1862 ROLLBACK TO process_replicate_bom_save_pnt;
1863 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
1864 IF (x_out.output_status.msg_data IS NOT NULL) THEN
1865 x_out.output_status.msg_data := x_out.output_status.msg_data||' ->INV_EBI_CHANGE_ORDER_HELPER.process_replicate_bom ';
1866 ELSE
1867 x_out.output_status.msg_data := SQLERRM||'INV_EBI_CHANGE_ORDER_HELPER.process_replicate_bom ';
1868 END IF;
1869 END process_replicate_bom;
1870
1871 /************************************************************************************
1872 -- API name : process_uda
1873 -- Type : Public
1874 -- Function :
1875 --
1876 ************************************************************************************/
1877 PROCEDURE process_uda (
1878 p_commit IN VARCHAR2 := FND_API.g_false
1879 ,p_api_version IN NUMBER DEFAULT 1.0
1880 ,p_uda_input_obj IN inv_ebi_uda_input_obj
1881 ,p_object_name IN VARCHAR2
1882 ,p_data_level IN VARCHAR2
1883 ,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1884 ,p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
1885 ,x_uda_output_obj OUT NOCOPY inv_ebi_eco_output_obj
1886 )
1887 IS
1888 l_attributes_row_table ego_user_attr_row_table;
1889 l_attributes_data_table ego_user_attr_data_table;
1890 l_attributes_row_obj ego_user_attr_row_obj;
1891 l_transaction_type VARCHAR2(20);
1892 l_uda_out inv_ebi_uda_output_obj;
1893 l_output_status inv_ebi_output_status;
1894 BEGIN
1895 SAVEPOINT inv_ebi_process_uda_save_pnt;
1896 l_uda_out := inv_ebi_uda_output_obj(NULL,NULL);
1897 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
1898 x_uda_output_obj := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,l_uda_out);
1899
1900 --To convert inv_ebi uda objects to ego uda compatible objects
1901 INV_EBI_UTIL.transform_uda (
1902 p_uda_input_obj => p_uda_input_obj
1903 ,x_attributes_row_table => l_attributes_row_table
1904 ,x_attributes_data_table => l_attributes_data_table
1905 ,x_return_status => x_uda_output_obj.output_status.return_status
1906 ,x_msg_count => x_uda_output_obj.output_status.msg_count
1907 ,x_msg_data => x_uda_output_obj.output_status.msg_data
1908 );
1909
1910 IF (x_uda_output_obj.output_status.return_status <> FND_API.g_ret_sts_success) THEN
1911 RAISE FND_API.g_exc_unexpected_error;
1912 END IF;
1913
1914 FOR i in 1..l_attributes_row_table.COUNT
1915 LOOP
1916 l_attributes_row_obj := l_attributes_row_table(i);
1917 IF(l_attributes_row_table(i).transaction_type IS NULL) THEN
1918 l_transaction_type := ego_user_attrs_data_pvt.g_sync_mode;
1919 ELSE
1920 l_transaction_type := l_attributes_row_table(i).transaction_type;
1921 END IF;
1922 l_attributes_row_obj := EGO_USER_ATTRS_DATA_PUB.build_attr_group_row_object(
1923 p_row_identifier => i
1924 ,p_attr_group_id => l_attributes_row_obj.attr_group_id
1925 ,p_attr_group_app_id => l_attributes_row_obj.attr_group_app_id
1926 ,p_attr_group_type => l_attributes_row_obj.attr_group_type
1927 ,p_attr_group_name => l_attributes_row_obj.attr_group_name
1928 ,p_data_level => p_data_level
1929 ,p_data_level_1 => l_attributes_row_obj.data_level_1
1930 ,p_data_level_2 => l_attributes_row_obj.data_level_2
1931 ,p_data_level_3 => l_attributes_row_obj.data_level_3
1932 ,p_data_level_4 => l_attributes_row_obj.data_level_4
1933 ,p_data_level_5 => l_attributes_row_obj.data_level_5
1934 ,p_transaction_type => l_attributes_row_obj.transaction_type
1935 );
1936
1937 l_attributes_row_table(i) := l_attributes_row_obj;
1938 END LOOP;
1939
1940 --To process uda
1941 ego_user_attrs_data_pub.process_user_attrs_data(
1942 p_api_version => p_api_version
1943 ,p_object_name => p_object_name
1944 ,p_attributes_row_table => l_attributes_row_table
1945 ,p_attributes_data_table => l_attributes_data_table
1946 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
1947 ,p_class_code_name_value_pairs => p_class_code_name_value_pairs
1948 ,p_user_privileges_on_object => p_uda_input_obj.user_privileges_on_object
1949 ,p_entity_id => p_uda_input_obj.entity_id
1950 ,p_entity_index => p_uda_input_obj.entity_index
1951 ,p_entity_code => p_uda_input_obj.entity_code
1952 ,p_debug_level => p_uda_input_obj.debug_level
1953 ,p_init_error_handler => p_uda_input_obj.init_error_handler
1954 ,p_write_to_concurrent_log => p_uda_input_obj.write_to_concurrent_log
1955 ,p_init_fnd_msg_list => p_uda_input_obj.init_fnd_msg_list
1956 ,p_log_errors => p_uda_input_obj.log_errors
1957 ,p_add_errors_to_fnd_stack => p_uda_input_obj.add_errors_to_fnd_stack
1958 ,p_commit => p_commit
1959 ,x_failed_row_id_list => x_uda_output_obj.uda_output.failed_row_id_list
1960 ,x_return_status => x_uda_output_obj.output_status.return_status
1961 ,x_errorcode => x_uda_output_obj.uda_output.errorcode
1962 ,x_msg_count => x_uda_output_obj.output_status.msg_count
1963 ,x_msg_data => x_uda_output_obj.output_status.msg_data
1964 );
1965
1966 IF (x_uda_output_obj.output_status.return_status <> FND_API.g_ret_sts_success) THEN
1967 RAISE FND_API.g_exc_unexpected_error;
1968 END IF;
1969
1970 IF FND_API.To_Boolean(p_commit) THEN
1971 COMMIT;
1972 END IF;
1973
1974 EXCEPTION
1975 WHEN FND_API.g_exc_unexpected_error THEN
1976 ROLLBACK TO inv_ebi_process_uda_save_pnt;
1977 x_uda_output_obj.output_status.return_status := FND_API.g_ret_sts_error;
1978
1979 IF(x_uda_output_obj.output_status.msg_data IS NULL) THEN
1980 fnd_msg_pub.count_and_get(
1981 p_encoded => FND_API.g_false
1982 ,p_count => x_uda_output_obj.output_status.msg_count
1983 ,p_data => x_uda_output_obj.output_status.msg_data
1984 );
1985 END IF;
1986
1987 WHEN OTHERS THEN
1988 ROLLBACK TO inv_ebi_process_uda_save_pnt;
1989 x_uda_output_obj.output_status.return_status := FND_API.g_ret_sts_unexp_error;
1990 IF (x_uda_output_obj.output_status.msg_data IS NOT NULL) THEN
1991 x_uda_output_obj.output_status.msg_data := x_uda_output_obj.output_status.msg_data ||' -> INV_EBI_CHANGE_ORDER_HELPER.process_uda ';
1992 ELSE
1993 x_uda_output_obj.output_status.msg_data := SQLERRM||' at INV_EBI_CHANGE_ORDER_HELPER.process_uda ';
1994 END IF;
1995 END process_uda;
1996 /************************************************************************************
1997 -- API name : process_change_order_uda
1998 -- Type : Private
1999 -- Function :
2000 -- This API is used to process Component Level and Structure header udas
2001 --
2002 ************************************************************************************/
2003 PROCEDURE process_change_order_uda(
2004 p_commit IN VARCHAR2
2005 ,p_organization_code IN VARCHAR2
2006 ,p_eco_name IN VARCHAR2
2007 ,p_alternate_bom_code IN VARCHAR2
2008 ,p_revised_item_name IN VARCHAR2
2009 ,p_component_tbl IN inv_ebi_rev_comp_tbl
2010 ,p_structure_header IN inv_ebi_structure_header_obj
2011 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
2012 )IS
2013 l_pkdata ego_col_name_value_pair_array;
2014 l_pkcode ego_col_name_value_pair_array;
2015 l_bill_sequence_id NUMBER;
2016 l_component_sequence_id NUMBER;
2017 l_assembly_item_id NUMBER;
2018 l_organization_id NUMBER;
2019 l_structure_type_id NUMBER;
2020 l_revised_item_sequence_id NUMBER;
2021 l_component_item_id NUMBER;
2022 l_msg_data VARCHAR2(32000);
2023 l_return_status VARCHAR2(3);
2024 l_Error_Table Error_Handler.Error_Tbl_Type;
2025 l_count NUMBER:=0;
2026 l_uda_out inv_ebi_uda_output_obj;
2027 l_output_status inv_ebi_output_status;
2028 BEGIN
2029 SAVEPOINT inv_ebi_chg_order_uda_save_pnt;
2030
2031 l_uda_out := inv_ebi_uda_output_obj(NULL,NULL);
2032 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
2033 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
2034
2035 Error_Handler.initialize;
2036 IF(p_organization_code IS NOT NULL) THEN
2037
2038 l_organization_id := INV_EBI_ITEM_HELPER.get_organization_id(
2039 p_organization_code => p_organization_code
2040 );
2041 END IF;
2042 IF (p_revised_item_name IS NOT NULL) THEN
2043 l_assembly_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id (
2044 p_organization_id => l_organization_id
2045 ,p_item_number => p_revised_item_name
2046 );
2047
2048 END IF;
2049 IF (p_component_tbl IS NOT NULL AND p_component_tbl.COUNT > 0) THEN
2050 FOR i in 1..p_component_tbl.COUNT
2051 LOOP
2052 IF(p_component_tbl(i).component_revision_uda IS NOT NULL AND p_component_tbl(i).component_revision_uda.attribute_group_tbl.COUNT > 0) THEN
2053 IF(p_component_tbl(i).component_item_name IS NOT NULL) THEN
2054
2055 l_component_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id (
2056 p_organization_id => l_organization_id
2057 ,p_item_number => p_component_tbl(i).component_item_name
2058 );
2059
2060 END IF;
2061
2062 SELECT bill_sequence_id,structure_type_id INTO l_bill_sequence_id,l_structure_type_id
2063 FROM bom_bill_of_materials
2064 WHERE assembly_item_id = l_assembly_item_id
2065 AND organization_id = l_organization_id
2066 AND NVL(alternate_bom_designator, 'NONE') = DECODE(p_alternate_bom_code,FND_API.G_MISS_CHAR,'NONE',NULL,'NONE',p_alternate_bom_code) ;
2067
2068 SELECT component_sequence_id INTO l_component_sequence_id
2069 FROM bom_components_b
2070 WHERE bill_sequence_id = l_bill_sequence_id
2071 AND component_item_id = l_component_item_id
2072 AND change_notice = p_eco_name;
2073
2074 l_pkdata := ego_col_name_value_pair_array();
2075 l_pkdata.extend(2);
2076 l_pkdata(1) := ego_col_name_value_pair_obj('COMPONENT_SEQUENCE_ID',l_component_sequence_id);
2077 l_pkdata(2) := ego_col_name_value_pair_obj('BILL_SEQUENCE_ID',l_bill_sequence_id);
2078 l_pkcode := ego_col_name_value_pair_array();
2079 l_pkcode.extend();
2080 l_pkcode(1) := ego_col_name_value_pair_obj('STRUCTURE_TYPE_ID',l_structure_type_id);
2081
2082 process_uda(
2083 p_commit => p_commit
2084 ,p_uda_input_obj => p_component_tbl(i).component_revision_uda
2085 ,p_object_name => 'BOM_COMPONENTS'
2086 ,p_data_level => 'COMPONENTS_LEVEL'
2087 ,p_pk_column_name_value_pairs => l_pkdata
2088 ,p_class_code_name_value_pairs => l_pkcode
2089 ,x_uda_output_obj => x_out
2090 );
2091
2092 IF(x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2093 RAISE FND_API.g_exc_unexpected_error;
2094 END IF;
2095 END IF;
2096 END LOOP;
2097 END IF;
2098
2099 IF(p_structure_header IS NOT NULL AND p_structure_header.structure_header_uda IS NOT NULL AND p_structure_header.structure_header_uda.attribute_group_tbl.COUNT > 0) THEN
2100
2101 SELECT bill_sequence_id,structure_type_id INTO l_bill_sequence_id,l_structure_type_id
2102 FROM bom_bill_of_materials
2103 WHERE assembly_item_id = l_assembly_item_id
2104 AND organization_id = l_organization_id
2105 AND NVL(alternate_bom_designator, 'NONE') = DECODE(p_alternate_bom_code,FND_API.G_MISS_CHAR,'NONE',NULL,'NONE',p_alternate_bom_code) ;
2106
2107 l_pkdata := ego_col_name_value_pair_array();
2108 l_pkdata.extend();
2109 l_pkdata(1) := ego_col_name_value_pair_obj('BILL_SEQUENCE_ID',l_bill_sequence_id);
2110 l_pkcode := ego_col_name_value_pair_array();
2111 l_pkcode.extend();
2112 l_pkcode(1) := ego_col_name_value_pair_obj('STRUCTURE_TYPE_ID',l_structure_type_id);
2113
2114 process_uda(
2115 p_uda_input_obj => p_structure_header.structure_header_uda
2116 ,p_commit => p_commit
2117 ,p_object_name => 'BOM_STRUCTURE'
2118 ,p_data_level => 'STRUCTURES_LEVEL'
2119 ,p_pk_column_name_value_pairs => l_pkdata
2120 ,p_class_code_name_value_pairs => l_pkcode
2121 ,x_uda_output_obj => x_out
2122 );
2123
2124 IF(x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2125 RAISE FND_API.g_exc_unexpected_error;
2126 END IF;
2127 END IF;
2128
2129 IF FND_API.To_Boolean(p_commit) THEN
2130 COMMIT;
2131 END IF;
2132 EXCEPTION
2133 WHEN FND_API.g_exc_unexpected_error THEN
2134 ROLLBACK TO inv_ebi_chg_order_uda_save_pnt;
2135 x_out.output_status.return_status := FND_API.g_ret_sts_error;
2136 IF(x_out.output_status.msg_data IS NULL) THEN
2137 fnd_msg_pub.count_and_get(
2138 p_encoded => FND_API.g_false
2139 ,p_count => x_out.output_status.msg_count
2140 ,p_data => x_out.output_status.msg_data
2141 );
2142 END IF;
2143 WHEN OTHERS THEN
2144 ROLLBACK TO inv_ebi_chg_order_uda_save_pnt;
2145 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2146 IF (x_out.output_status.msg_data IS NOT NULL) THEN
2147 x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_CHANGE_ORDER_HELPER.process_change_order_uda';
2148 ELSE
2149 x_out.output_status.msg_data := SQLERRM||' at INV_EBI_CHANGE_ORDER_HELPER.process_change_order_uda';
2150 END IF;
2151 END process_change_order_uda;
2152
2153 /*******************************************************************************
2154 API name : Check_Workflow_Process
2155 Type : Private
2156 Purpose : Checks if there is worflow process for the ECO.
2157 ********************************************************************************/
2158 FUNCTION Check_Workflow_Process(
2159 p_change_order_type_id IN NUMBER
2160 ,p_priority_code IN VARCHAR2 ) RETURN BOOLEAN
2161 IS
2162 l_count NUMBER;
2163 BEGIN
2164 SELECT count(1)
2165 INTO l_count
2166 FROM eng_change_type_processes
2167 WHERE change_order_type_id = p_change_order_type_id
2168 AND NVL(eng_change_priority_code,'X') = NVL(p_priority_code, 'X');
2169
2170 IF (l_count > 0) THEN
2171 RETURN TRUE;
2172 ELSE
2173 RETURN FALSE;
2174 END IF;
2175
2176 EXCEPTION
2177 WHEN OTHERS THEN
2178 RETURN FALSE;
2179 END Check_Workflow_Process;
2180 /*******************************************************************************
2181 API name : is_task_template_set
2182 Type : Public
2183 Purpose : Checks if there is task template associated .
2184 Bug 7218542
2185 ********************************************************************************/
2186
2187 FUNCTION is_task_template_set(
2188 p_change_order_type_id IN NUMBER
2189 ,p_organization_id IN NUMBER
2190 ,p_status_code IN NUMBER
2191 ) RETURN BOOLEAN
2192 IS
2193 l_count NUMBER;
2194 BEGIN
2195 SELECT COUNT(1) INTO l_count
2196 FROM
2197 eng_change_tasks_vl tsk,
2198 eng_change_type_org_tasks typtsk
2199 WHERE
2200 tsk.organization_id = typtsk.organization_id AND
2201 typtsk.organization_id = p_organization_id AND
2202 tsk.change_template_id = typtsk.change_template_or_task_id AND
2203 typtsk.template_or_task_flag ='E' AND
2204 typtsk.change_type_id = p_change_order_type_id AND
2205 typtsk.complete_before_status_code = p_status_code;
2206
2207 IF (l_count > 0) THEN
2208 RETURN TRUE;
2209 ELSE
2210 RETURN FALSE;
2211 END IF;
2212
2213 EXCEPTION
2214 WHEN OTHERS THEN
2215 RETURN FALSE;
2216 END is_task_template_set;
2217
2218 /*******************************************************************************
2219 API name : get_status_name
2220 Type : Private
2221 Purpose : Get the status name based on look up type and lookup code.
2222 ********************************************************************************/
2223 FUNCTION get_status_name(
2224 p_lookup_type IN VARCHAR2
2225 ,p_lookup_code IN VARCHAR2 ) RETURN VARCHAR2
2226 IS
2227 l_meaning varchar2(240);
2228 CURSOR c_lkp IS
2229 SELECT meaning
2230 FROM FND_LOOKUP_VALUES_VL
2231 WHERE lookup_type = p_lookup_type
2232 AND lookup_code = p_lookup_code;
2233 BEGIN
2234 IF (c_lkp%ISOPEN) THEN
2235 CLOSE c_lkp;
2236 END IF;
2237 OPEN c_lkp;
2238 FETCH c_lkp INTO l_meaning;
2239 CLOSE c_lkp;
2240 RETURN l_meaning;
2241 EXCEPTION
2242 WHEN OTHERS THEN
2243 IF (c_lkp%ISOPEN) THEN
2244 CLOSE c_lkp;
2245 END IF;
2246 NULL;
2247 END get_status_name;
2248 /*******************************************************************************
2249 API name : GET_EXISTING_COMPONENT_ATTR
2250 Type : Private
2251 Purpose : Get the ATTRIBUTE OF EXISTING COMPONENT IN CASE OF ACD TYPE(2,3).
2252 ********************************************************************************/
2253
2254 PROCEDURE get_existing_component_attr(
2255 p_organization_id IN NUMBER
2256 , p_revised_item_name IN VARCHAR2
2257 , p_component_item_name IN VARCHAR2
2258 , p_op_sequence_number IN VARCHAR2
2259 , p_alternate_bom_code IN VARCHAR2
2260 , x_old_effectivity_date OUT NOCOPY DATE
2261 , x_old_op_sequence_num OUT NOCOPY VARCHAR2
2262 , x_old_fm_end_item_unit OUT NOCOPY VARCHAR2
2263 )
2264 IS
2265 CURSOR C_Bill_seq (p_assembly_item_id NUMBER) IS
2266 SELECT bill_sequence_id
2267 FROM bom_bill_of_materials
2268 WHERE assembly_item_id = p_assembly_item_id
2269 AND organization_id = p_organization_id
2270 AND nvl(alternate_bom_designator,'x') = nvl(p_alternate_bom_code,'x');
2271
2272 --This cursor for redlining of BOM Components.
2273 Cursor c_component(p_rev_item_id NUMBER, p_component_item_id NUMBER, p_bill_sequence_id NUMBER) IS
2274 SELECT bic.effectivity_date, bic.operation_seq_num, bic.from_end_item_unit_number
2275 FROM bom_inventory_components bic
2276 ,eng_revised_items eri
2277 WHERE eri.revised_item_id = p_rev_item_id
2278 AND eri.organization_id = p_organization_id
2279 AND eri.bill_sequence_id = p_bill_sequence_id
2280 AND bic.component_item_id = p_component_item_id
2281 AND bic.operation_seq_num = p_op_sequence_number
2282 AND bic.bill_sequence_id = eri.bill_sequence_id
2283 AND bic.revised_item_sequence_id = eri.revised_item_sequence_id
2284 AND eri.implementation_date = (SELECT MAX(erj.implementation_date)
2285 FROM bom_inventory_components bcc
2286 ,eng_revised_items erj
2287 WHERE erj.revised_item_id = p_rev_item_id
2288 AND erj.organization_id = p_organization_id
2289 AND erj.bill_sequence_id = p_bill_sequence_id
2290 AND bcc.component_item_id = p_component_item_id
2291 AND bcc.operation_seq_num = p_op_sequence_number
2292 AND bcc.bill_sequence_id = erj.bill_sequence_id
2293 AND bcc.revised_item_sequence_id = erj.revised_item_sequence_id
2294 AND erj.implementation_date IS NOT NULL);
2295
2296 l_bill_sequence_id NUMBER;
2297 l_assembly_item_id NUMBER;
2298 l_organization_id NUMBER;
2299 l_component_item_id NUMBER;
2300 BEGIN
2301
2302 -- To Retrive revisied item id
2303 l_assembly_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id
2304 (p_organization_id => p_organization_id
2305 ,p_item_number => p_revised_item_name);
2306
2307 -- Cursor to retrive the bill sequence id
2308 FOR i in C_Bill_seq(l_assembly_item_id) LOOP
2309 l_bill_sequence_id := i.bill_sequence_id;
2310 EXIT;
2311 END LOOP;
2312
2313 --To Retrive Component Item Id
2314 l_component_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id
2315 ( p_organization_id => p_organization_id
2316 ,p_item_number => p_component_item_name);
2317
2318
2319 --To Retrive old effectivity date if BOM Components are implimented
2320 FOR j IN c_component(l_assembly_item_id, l_component_item_id, l_bill_sequence_id)
2321 LOOP
2322 x_old_effectivity_date := j.effectivity_date;
2323 x_old_op_sequence_num := j.operation_seq_num;
2324 x_old_fm_end_item_unit := j.from_end_item_unit_number;
2325 EXIT;
2326 END LOOP;
2327
2328 EXCEPTION
2329 WHEN OTHERS THEN
2330 NULL;
2331 END get_existing_component_attr;
2332
2333 /************************************************************************************
2334 -- API name : get_current_item_revision
2335 -- Type : Private
2336 -- Function :
2337 -- This API is used to return Current revision record of an item
2338 -- Bug 7197943
2339 ************************************************************************************/
2340 FUNCTION get_current_item_revision(
2341 p_inventory_item_id IN NUMBER,
2342 p_organization_id IN NUMBER,
2343 p_date IN DATE
2344 ) RETURN VARCHAR2 IS
2345 l_revision VARCHAR2(3);
2346 CURSOR c_item_rev(
2347 p_inventory_item_id IN NUMBER,
2348 p_organization_id IN NUMBER,
2349 p_revision_date IN DATE
2350 ) IS
2351 SELECT
2352 revision
2353 FROM
2354 mtl_item_revisions_b
2355 WHERE
2356 inventory_item_id = p_inventory_item_id AND
2357 organization_id = p_organization_id AND
2358 effectivity_date <= p_revision_date AND
2359 implementation_date IS NOT NULL
2360 ORDER BY
2361 effectivity_date DESC, revision DESC;
2362
2363 l_item_rev c_item_rev%ROWTYPE;
2364
2365 BEGIN
2366 IF c_item_rev%ISOPEN THEN
2367 CLOSE c_item_rev;
2368 END IF;
2369
2370 OPEN c_item_rev(
2371 p_inventory_item_id => p_inventory_item_id,
2372 p_organization_id => p_organization_id,
2373 p_revision_date => sysdate
2374 );
2375 FETCH c_item_rev INTO l_revision;
2376 CLOSE c_item_rev;
2377 RETURN l_revision;
2378 EXCEPTION
2379 WHEN OTHERS THEN
2380 IF c_item_rev%ISOPEN THEN
2381 CLOSE c_item_rev;
2382 END IF;
2383 NULL;
2384 END get_current_item_revision;
2385
2386 /************************************************************************************
2387 -- API name : get_latest_effectivity_date
2388 -- Type : Private
2389 -- Function :
2390 -- This API is used to return effectivity date of the lastest created revision
2391 -- Bug 7197943
2392 ************************************************************************************/
2393 FUNCTION get_latest_effectivity_date(
2394 p_inventory_item_id IN NUMBER,
2395 p_organization_id IN NUMBER
2396 ) RETURN DATE IS
2397
2398 l_effectivity_date DATE;
2399
2400 CURSOR c_efectivity_date IS
2401 SELECT
2402 effectivity_date
2403 FROM
2404 mtl_item_revisions_b
2405 WHERE
2406 inventory_item_id = p_inventory_item_id AND
2407 organization_id = p_organization_id
2408 ORDER BY
2409 effectivity_date DESC, revision DESC;
2410
2411 BEGIN
2412 IF c_efectivity_date%ISOPEN THEN
2413 CLOSE c_efectivity_date;
2414 END IF;
2415
2416 OPEN c_efectivity_date;
2417 FETCH c_efectivity_date INTO l_effectivity_date;
2418 CLOSE c_efectivity_date;
2419 RETURN l_effectivity_date;
2420 EXCEPTION
2421 WHEN OTHERS THEN
2422 IF c_efectivity_date%ISOPEN THEN
2423 CLOSE c_efectivity_date;
2424 END IF;
2425 NULL;
2426 END get_latest_effectivity_date;
2427
2428 /************************************************************************************
2429 -- API name : process_assign_items
2430 -- Type : Private
2431 -- Function :
2432 -- This API is used to Assign items to child org if it exists in master org
2433 -- Other wise raises an exception
2434 __ BUG 7143083
2435 ************************************************************************************/
2436
2437 PROCEDURE process_assign_items(
2438 p_organization_id IN NUMBER ,
2439 p_item_name IN VARCHAR2,
2440 x_return_status OUT NOCOPY VARCHAR2 ,
2441 x_msg_data OUT NOCOPY VARCHAR2,
2442 x_msg_count OUT NOCOPY NUMBER
2443 ) IS
2444 l_is_item_exists VARCHAR2(3);
2445 l_master_org NUMBER;
2446 l_approval_status VARCHAR2(30);
2447 l_inventory_item_id NUMBER;
2448 l_count NUMBER := 0;
2449 l_item_catalog_group_id NUMBER;
2450 l_effectivity_date DATE;
2451 CURSOR c_master_item_rev(
2452 p_inventory_item_id IN NUMBER,
2453 p_organization_id IN NUMBER,
2454 p_revision_date IN DATE
2455 ) IS
2456 SELECT
2457 revision,
2458 revision_id ,
2459 revision_label,
2460 revision_reason,
2461 description,
2462 attribute_category,
2463 attribute1 ,
2464 attribute2 ,
2465 attribute3 ,
2466 attribute4 ,
2467 attribute5 ,
2468 attribute6 ,
2469 attribute7 ,
2470 attribute8 ,
2471 attribute9 ,
2472 attribute10,
2473 attribute11,
2474 attribute12,
2475 attribute13,
2476 attribute14,
2477 attribute15
2478 FROM
2479 mtl_item_revisions_b
2480 WHERE
2481 inventory_item_id = p_inventory_item_id AND
2482 organization_id = p_organization_id AND
2483 effectivity_date <= p_revision_date AND
2484 implementation_date IS NOT NULL
2485 ORDER BY
2486 effectivity_date DESC, revision DESC;
2487
2488 l_master_item_rev c_master_item_rev%ROWTYPE;
2489
2490 BEGIN
2491 SAVEPOINT inv_ebi_assign_item_save_pnt;
2492 x_return_status := FND_API.G_RET_STS_SUCCESS;
2493 l_is_item_exists := INV_EBI_ITEM_HELPER.is_item_exists(
2494 p_organization_id => p_organization_id
2495 ,p_item_number => p_item_name
2496 );
2497 IF (l_is_item_exists = FND_API.g_false) THEN
2498
2499 l_master_org := INV_EBI_UTIL.get_master_organization(
2500 p_organization_id => p_organization_id
2501 );
2502 l_is_item_exists := INV_EBI_ITEM_HELPER.is_item_exists(
2503 p_organization_id => l_master_org
2504 ,p_item_number => p_item_name
2505 );
2506
2507 /* If Item does not exist in context org(child org) and it exists in master org
2508 and if it approved in master org and ASSIGN_ITEM_TO_CHILD_ORG is set
2509 to true then item should be assigned to context org */
2510
2511 IF(l_is_item_exists = FND_API.g_true ) THEN
2512 IF(get_assign_item = FND_API.g_true ) THEN
2513 IF(INV_EBI_UTIL.is_pim_installed) THEN
2514 SELECT item_catalog_group_id, approval_status
2515 INTO l_item_catalog_group_id, l_approval_status
2516 FROM mtl_system_items_kfv
2517 WHERE organization_id = l_master_org
2518 AND concatenated_segments = p_item_name;
2519 IF (INV_EBI_ITEM_HELPER.is_new_item_request_reqd( l_item_catalog_group_id ) = FND_API.g_true) AND l_approval_status <> 'A'
2520 THEN
2521 FND_MSG_PUB.initialize();
2522 FND_MESSAGE.set_name('INV','INV_EBI_INVALID_APROVAL_STS');
2523 FND_MESSAGE.set_token('ITEM_NUMBER',p_item_name);
2524 FND_MESSAGE.set_token('ORGANIZATION_ID',l_master_org);
2525 FND_MESSAGE.set_token('CHILD_ORGANIZATION_ID',p_organization_id);
2526 FND_MSG_PUB.add;
2527 RAISE FND_API.g_exc_unexpected_error;
2528 END IF;
2529 END IF;
2530
2531 EGO_ITEM_PUB.assign_item_to_org(
2532 p_api_version => 1.0
2533 ,p_commit => FND_API.g_false
2534 ,p_Item_Number => p_item_name
2535 ,p_Organization_Id => p_organization_id
2536 ,x_return_status => x_return_status
2537 ,x_msg_count => x_msg_count
2538 );
2539 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
2540 RAISE FND_API.g_exc_unexpected_error;
2541 END IF;
2542 l_inventory_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id (
2543 p_organization_id => l_master_org
2544 ,p_item_number => p_item_name
2545 );
2546 --To get effectivity date of recently created revision in context org
2547 l_effectivity_date := get_latest_effectivity_date(
2548 p_inventory_item_id => l_inventory_item_id,
2549 p_organization_id => p_organization_id
2550 );
2551
2552 --To get current revision from master org
2553 IF c_master_item_rev%ISOPEN THEN
2554 CLOSE c_master_item_rev;
2555 END IF;
2556
2557 OPEN c_master_item_rev(
2558 p_inventory_item_id => l_inventory_item_id,
2559 p_organization_id => l_master_org,
2560 p_revision_date => sysdate
2561 );
2562 FETCH c_master_item_rev INTO l_master_item_rev;
2563 CLOSE c_master_item_rev;
2564
2565 --To check if master orgs current reviison is already there in context org
2566 SELECT COUNT(1) INTO l_count
2567 FROM
2568 mtl_item_revisions_b mir,
2569 mtl_system_items_kfv msi
2570 WHERE
2571 mir.organization_id = msi.organization_id AND
2572 msi.organization_id = p_organization_id AND
2573 mir.inventory_item_id = msi.inventory_item_id AND
2574 msi.concatenated_segments = p_item_name AND
2575 mir.revision = l_master_item_rev.revision;
2576
2577 --If master orgs current revision is not there in context org,create it
2578 IF(l_count = 0) THEN
2579 l_effectivity_date := l_effectivity_date + 1/86400; -- To keep efectivity date of next rev 1 sec higher than earlier rev
2580
2581 EGO_ITEM_PUB.Process_Item_Revision(
2582 p_api_version => 1.0
2583 ,p_init_msg_list => FND_API.g_false
2584 ,p_commit => FND_API.g_false
2585 ,p_transaction_type => INV_EBI_ITEM_PUB.g_otype_create
2586 ,p_inventory_item_id => NULL
2587 ,p_item_number => p_item_name
2588 ,p_organization_id => p_organization_id
2589 ,p_Organization_Code => NULL
2590 ,p_revision => l_master_item_rev.revision
2591 ,p_description => l_master_item_rev.description
2592 ,p_effectivity_date => l_effectivity_date
2593 ,p_revision_label => l_master_item_rev.revision_label
2594 ,p_revision_reason => l_master_item_rev.revision_reason
2595 ,p_lifecycle_id => NULL
2596 ,p_current_phase_id => NULL
2597 ,p_attribute_category => l_master_item_rev.attribute_category
2598 ,p_attribute1 => l_master_item_rev.attribute1
2599 ,p_attribute2 => l_master_item_rev.attribute2
2600 ,p_attribute3 => l_master_item_rev.attribute3
2601 ,p_attribute4 => l_master_item_rev.attribute4
2602 ,p_attribute5 => l_master_item_rev.attribute5
2603 ,p_attribute6 => l_master_item_rev.attribute6
2604 ,p_attribute7 => l_master_item_rev.attribute7
2605 ,p_attribute8 => l_master_item_rev.attribute8
2606 ,p_attribute9 => l_master_item_rev.attribute9
2607 ,p_attribute10 => l_master_item_rev.attribute10
2608 ,p_attribute11 => l_master_item_rev.attribute11
2609 ,p_attribute12 => l_master_item_rev.attribute12
2610 ,p_attribute13 => l_master_item_rev.attribute13
2611 ,p_attribute14 => l_master_item_rev.attribute14
2612 ,p_attribute15 => l_master_item_rev.attribute15
2613 ,x_return_status => x_return_status
2614 ,x_msg_count => x_msg_data
2615 ,x_msg_data => x_msg_count
2616 ) ;
2617
2618 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
2619 RAISE FND_API.g_exc_unexpected_error;
2620 END IF;
2621 END IF;
2622 END IF;
2623 ELSE
2624 FND_MESSAGE.set_name('INV','INV_EBI_ITEM_NO_MASTER_ORG');
2625 FND_MESSAGE.set_token('ITEM', p_item_name);
2626 FND_MSG_PUB.add;
2627 RAISE FND_API.g_exc_error;
2628 END IF;
2629 END IF;
2630 INV_EBI_UTIL.debug_line('STEP: 20 END INSIDE INV_EBI_CHANGE_ORDER_HELPER.process_assign_items');
2631 EXCEPTION
2632 WHEN FND_API.g_exc_unexpected_error THEN
2633 ROLLBACK TO inv_ebi_assign_item_save_pnt;
2634 x_return_status := FND_API.g_ret_sts_unexp_error;
2635
2636 IF c_master_item_rev%ISOPEN THEN
2637 CLOSE c_master_item_rev;
2638 END IF;
2639 IF(x_msg_data IS NULL) THEN
2640 fnd_msg_pub.count_and_get(
2641 p_encoded => FND_API.g_false
2642 ,p_count => x_msg_count
2643 ,p_data => x_msg_data
2644 );
2645 END IF;
2646 WHEN FND_API.g_exc_error THEN
2647 ROLLBACK TO inv_ebi_assign_item_save_pnt;
2648 x_return_status := FND_API.g_ret_sts_error;
2649
2650 IF c_master_item_rev%ISOPEN THEN
2651 CLOSE c_master_item_rev;
2652 END IF;
2653 IF(x_msg_data IS NULL) THEN
2654 FND_MSG_PUB.count_and_get(
2655 p_encoded => FND_API.g_false
2656 ,p_count => x_msg_count
2657 ,p_data => x_msg_data
2658 );
2659 END IF;
2660 WHEN OTHERS THEN
2661 ROLLBACK TO inv_ebi_assign_item_save_pnt;
2662 x_return_status := FND_API.g_ret_sts_unexp_error;
2663
2664 IF c_master_item_rev%ISOPEN THEN
2665 CLOSE c_master_item_rev;
2666 END IF;
2667 IF (x_msg_data IS NOT NULL) THEN
2668 x_msg_data := x_msg_data||' -> INV_EBI_CHANGE_ORDER_HELPER.process_assign_items ';
2669 ELSE
2670 x_msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_HELPER.process_assign_items ';
2671 END IF;
2672 END process_assign_items;
2673
2674 /************************************************************************************
2675 -- API name : get_change_type_code
2676 -- Type : Public
2677 -- Function :
2678 -- This API is used to return change_type_code based on change_order_type_id
2679 -- From DVM
2680 ************************************************************************************/
2681 FUNCTION get_change_type_code(p_change_type_id IN NUMBER )
2682 RETURN VARCHAR2
2683 IS
2684 l_change_type_code VARCHAR2(80);
2685 BEGIN
2686
2687 SELECT type_name
2688 INTO l_change_type_code
2689 FROM eng_change_order_types_vl
2690 WHERE change_order_type_id = p_change_type_id
2691 AND change_mgmt_type_code = 'CHANGE_ORDER'
2692 AND type_classification='HEADER';
2693
2694 RETURN l_change_type_code;
2695 EXCEPTION
2696 WHEN OTHERS THEN
2697 RETURN NULL;
2698 END get_change_type_code;
2699
2700 /************************************************************************************
2701 -- API name : get_eco_status_name
2702 -- Type : Public
2703 -- Function :
2704 -- This API is used to return ECO status_name based on status_CODE
2705 -- From DVM
2706 ************************************************************************************/
2707
2708 FUNCTION get_eco_status_name(p_status_code IN NUMBER)
2709 RETURN VARCHAR2
2710 IS
2711 l_status_name VARCHAR2(30);
2712 BEGIN
2713
2714 SELECT status_name
2715 INTO l_status_name
2716 FROM eng_change_statuses_vl
2717 WHERE status_code = p_status_code;
2718
2719 RETURN l_status_name;
2720 EXCEPTION
2721 WHEN OTHERS THEN
2722 NULL;
2723 END get_eco_status_name;
2724
2725 /************************************************************************************
2726 -- API name : process_eco
2727 -- Type : Private
2728 -- Function :
2729 -- This API is used to create the change order.
2730 --
2731 ************************************************************************************/
2732 PROCEDURE process_eco(
2733 p_commit IN VARCHAR2 := FND_API.G_FALSE
2734 ,p_change_order IN inv_ebi_change_order_obj
2735 ,p_revision_type_tbl IN inv_ebi_eco_revision_tbl
2736 ,p_revised_item_type_tbl IN inv_ebi_revised_item_tbl
2737 ,p_name_val_list IN inv_ebi_name_value_list
2738 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
2739 ) IS
2740 l_eco_rec ENG_ECO_PUB.eco_rec_type;
2741 l_eco_revision_tbl ENG_ECO_PUB.eco_revision_tbl_type;
2742 l_revised_item_tbl ENG_ECO_PUB.revised_item_tbl_type;
2743 l_revised_item_tbl_count NUMBER := 1;
2744 l_rev_component_tbl BOM_BO_PUB.rev_component_tbl_type;
2745 l_rev_component_tbl_count NUMBER :=1;
2746 l_ref_designator_tbl BOM_BO_PUB.ref_designator_tbl_type;
2747 l_ref_designator_tbl_count NUMBER :=1;
2748 l_sub_component_tbl BOM_BO_PUB.sub_component_tbl_type;
2749 l_sub_component_tbl_count NUMBER :=1;
2750 l_rev_operation_tbl BOM_RTG_PUB.rev_operation_tbl_type;
2751 l_rev_operation_tbl_count NUMBER :=1;
2752 l_rev_op_resource_tbl BOM_RTG_PUB.rev_op_resource_tbl_type;
2753 l_rev_op_resource_tbl_count NUMBER :=1;
2754 l_rev_sub_resource_tbl BOM_RTG_PUB.rev_sub_resource_tbl_type;
2755 l_rev_sub_resource_tbl_count NUMBER :=1;
2756 l_change_line_tbl ENG_ECO_PUB.change_line_tbl_type;
2757 l_revision VARCHAR2(3);
2758 l_is_component_item_exists VARCHAR2(3);
2759 l_api_version NUMBER:=1.0;
2760 l_item_org_assignment_rec EGO_ITEM_PUB.item_org_assignment_rec_type;
2761 l_item_org_assignment_tbl EGO_ITEM_PUB.item_org_assignment_tbl_type;
2762 l_inventory_item_id NUMBER;
2763 l_organization_id NUMBER;
2764 l_component_item_name VARCHAR2(240);
2765 l_master_org NUMBER;
2766 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
2767 l_subst_comp_name mtl_system_items_kfv.concatenated_segments%TYPE;
2768 l_return_status VARCHAR2(10);
2769 l_msg_count NUMBER;
2770 l_output_status inv_ebi_output_status;
2771 l_status_type NUMBER;
2772 l_priority_code VARCHAR2(10);
2773 l_is_wf_Set BOOLEAN;
2774 l_approval_status NUMBER :=0;
2775 l_plm_or_erp VARCHAR2(1):=FND_API.G_TRUE;
2776 l_old_effectivity_date DATE;
2777 l_old_op_sequence_num NUMBER;
2778 l_old_fm_end_item_unit NUMBER;
2779 l_acd_update CONSTANT NUMBER :=2;
2780 l_acd_delete CONSTANT NUMBER :=3;
2781 l_revised_item_id NUMBER;
2782 l_effectivity_date DATE;
2783 l_change_type_code VARCHAR2(80);
2784 l_status_name VARCHAR2(30);
2785
2786 BEGIN
2787 SAVEPOINT inv_ebi_proc_eco_save_pnt;
2788 INV_EBI_UTIL.debug_line('STEP: 10 START INSIDE INV_EBI_CHANGE_ORDER_HELPER.process_eco');
2789
2790 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
2791 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
2792
2793 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
2794 l_pk_col_name_val_pairs.EXTEND(1);
2795 l_pk_col_name_val_pairs(1).name := 'organization_code';
2796 l_pk_col_name_val_pairs(1).value := p_change_order.organization_code;
2797
2798 l_organization_id := INV_EBI_ITEM_HELPER.value_to_id(
2799 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
2800 ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
2801 );
2802 l_pk_col_name_val_pairs.TRIM(1);
2803
2804 IF (l_organization_id IS NULL) THEN
2805 FND_MESSAGE.set_name('INV','INV_EBI_ORG_CODE_INVALID');
2806 FND_MESSAGE.set_token('COL_VALUE', p_change_order.organization_code);
2807 FND_MSG_PUB.add;
2808 RAISE FND_API.g_exc_unexpected_error;
2809 END IF;
2810
2811 -- Code to incorporate the DVM changes
2812 -- ECO_TYPECODE
2813 l_change_type_code := get_change_type_code(p_change_type_id => p_change_order.change_type_id);
2814
2815 -- ECO_STATUS_CODE
2816 l_status_name := get_eco_status_name(p_status_code => p_change_order.status_code);
2817
2818 IF p_name_val_list.name_value_table IS NOT NULL THEN
2819 FOR i in p_name_val_list.name_value_table.FIRST..p_name_val_list.name_value_table.LAST LOOP
2820 IF (UPPER(p_name_val_list.name_value_table(i).param_name) = G_PLM_OR_ERP_CHANGE) THEN
2821 l_plm_or_erp := p_name_val_list.name_value_table(i).param_value;
2822 END IF;
2823 END LOOP;
2824 END IF;
2825
2826 l_is_wf_Set := Check_Workflow_Process(p_change_order_type_id => p_change_order.change_type_id
2827 ,p_priority_code => p_change_order.priority_code
2828 );
2829 IF (p_change_order IS NOT NULL) THEN
2830 l_eco_rec.eco_name := p_change_order.eco_name;
2831 l_eco_rec.change_notice_prefix := p_change_order.change_notice_prefix;
2832 l_eco_rec.change_notice_number := p_change_order.change_notice_number;
2833 l_eco_rec.organization_code := p_change_order.organization_code ;
2834 l_eco_rec.change_name := p_change_order.change_name;
2835 l_eco_rec.description := p_change_order.description;
2836 l_eco_rec.cancellation_comments := p_change_order.cancellation_comments ;
2837 BEGIN
2838 IF (l_is_wf_Set) THEN
2839 SELECT status_name, status_type
2840 INTO l_eco_rec.status_name, l_status_type
2841 FROM eng_change_statuses_vl
2842 WHERE status_code = 1; -- ECO Status Set to 'Open';
2843 l_eco_rec.approval_status_name := get_status_name(p_lookup_type => 'ENG_ECN_APPROVAL_STATUS'
2844 ,p_lookup_code => 1 ); -- Not submitted for approval
2845 ELSE
2846 l_eco_rec.status_name := l_status_name;
2847 IF (l_eco_rec.status_name = 'Implemented' ) THEN
2848 SELECT status_name, status_type
2849 INTO l_eco_rec.status_name, l_status_type
2850 FROM eng_change_statuses_vl
2851 WHERE status_code = 4; -- Scheduled
2852 END IF;
2853 l_eco_rec.approval_status_name := get_status_name(p_lookup_type => 'ENG_ECN_APPROVAL_STATUS'
2854 ,p_lookup_code => 5 ); -- Approved
2855 END IF;
2856 EXCEPTION
2857 WHEN OTHERS THEN
2858 NULL;
2859 END;
2860 l_eco_rec.priority_code := p_change_order.priority_code ;
2861 l_eco_rec.reason_code := p_change_order.reason_code;
2862 l_eco_rec.eng_implementation_cost := p_change_order.eng_implementation_cost;
2863 l_eco_rec.mfg_implementation_cost := p_change_order.mfg_implementation_cost;
2864 l_eco_rec.requestor := p_change_order.requestor;
2865 l_eco_rec.attribute_category := p_change_order.attribute_category ;
2866 l_eco_rec.attribute1 := p_change_order.attribute1 ;
2867 l_eco_rec.attribute2 := p_change_order.attribute2 ;
2868 l_eco_rec.attribute3 := p_change_order.attribute3 ;
2869 l_eco_rec.attribute4 := p_change_order.attribute4 ;
2870 l_eco_rec.attribute5 := p_change_order.attribute5 ;
2871 l_eco_rec.attribute6 := p_change_order.attribute6 ;
2872 l_eco_rec.attribute7 := p_change_order.attribute7 ;
2873 l_eco_rec.attribute8 := p_change_order.attribute8 ;
2874 l_eco_rec.attribute9 := p_change_order.attribute9 ;
2875 l_eco_rec.attribute10 := p_change_order.attribute10;
2876 l_eco_rec.attribute11 := p_change_order.attribute11 ;
2877 l_eco_rec.attribute12 := p_change_order.attribute12;
2878 l_eco_rec.attribute13 := p_change_order.attribute13;
2879 l_eco_rec.attribute14 := p_change_order.attribute14;
2880 l_eco_rec.attribute15 := p_change_order.attribute15;
2881 l_eco_rec.ddf_context := p_change_order.ddf_context ;
2882 l_eco_rec.approval_list_name := p_change_order.approval_list_name ;
2883 l_eco_rec.approval_date := p_change_order.approval_date;
2884 l_eco_rec.approval_request_date := p_change_order.approval_request_date ;
2885 l_eco_rec.change_type_code := l_change_type_code ;
2886 l_eco_rec.change_management_type := p_change_order.change_management_type ;
2887 l_eco_rec.original_system_reference := p_change_order.original_system_reference;
2888 l_eco_rec.organization_hierarchy := p_change_order.organization_hierarchy;
2889 l_eco_rec.assignee := p_change_order.assignee ;
2890 l_eco_rec.project_name := p_change_order.project_name ;
2891 l_eco_rec.task_number := p_change_order.task_number;
2892 l_eco_rec.source_type := p_change_order.source_type;
2893 l_eco_rec.source_name := p_change_order.source_name ;
2894 l_eco_rec.need_by_date := p_change_order.need_by_date ;
2895 l_eco_rec.effort := p_change_order.effort;
2896 l_eco_rec.eco_department_name := p_change_order.eco_department_name;
2897 l_eco_rec.transaction_id := p_change_order.transaction_id;
2898 l_eco_rec.transaction_type := p_change_order.transaction_type ;
2899 l_eco_rec.internal_use_only := p_change_order.internal_use_only ;
2900 l_eco_rec.return_status := p_change_order.return_status ;
2901 IF (p_change_order.plm_or_erp_change IS NOT NULL AND p_change_order.plm_or_erp_change <> fnd_api.g_miss_char ) THEN
2902 l_eco_rec.plm_or_erp_change := p_change_order.plm_or_erp_change ;
2903 ELSE
2904 IF (l_plm_or_erp = FND_API.G_FALSE) THEN
2905 l_eco_rec.plm_or_erp_change := 'PLM';
2906 ELSE
2907 l_eco_rec.plm_or_erp_change := 'ERP';
2908 END IF;
2909 END IF;
2910 l_eco_rec.pk1_name := p_change_order.pk1_name ;
2911 l_eco_rec.pk2_name := p_change_order.pk2_name;
2912 l_eco_rec.pk3_name := p_change_order.pk3_name;
2913 l_eco_rec.employee_number := p_change_order.employee_number;
2914 END IF;
2915 IF(p_revision_type_tbl IS NOT NULL AND p_revision_type_tbl.COUNT > 0) THEN
2916 FOR i IN 1..p_revision_type_tbl.COUNT
2917 LOOP
2918 l_eco_revision_tbl(i).eco_name := p_change_order.eco_name ;
2919 l_eco_revision_tbl(i).organization_code := p_change_order.organization_code ;
2920 l_eco_revision_tbl(i).revision := p_revision_type_tbl(i).revision ;
2921 l_eco_revision_tbl(i).new_revision := p_revision_type_tbl(i).new_revision ;
2922 l_eco_revision_tbl(i).comments := p_revision_type_tbl(i).comments;
2923 l_eco_revision_tbl(i).attribute_category := p_revision_type_tbl(i).attribute_category ;
2924 l_eco_revision_tbl(i).attribute1 := p_revision_type_tbl(i).attribute1 ;
2925 l_eco_revision_tbl(i).attribute2 := p_revision_type_tbl(i).attribute2 ;
2926 l_eco_revision_tbl(i).attribute3 := p_revision_type_tbl(i).attribute3 ;
2927 l_eco_revision_tbl(i).attribute4 := p_revision_type_tbl(i).attribute4 ;
2928 l_eco_revision_tbl(i).attribute5 := p_revision_type_tbl(i).attribute5 ;
2929 l_eco_revision_tbl(i).attribute6 := p_revision_type_tbl(i).attribute6 ;
2930 l_eco_revision_tbl(i).attribute7 := p_revision_type_tbl(i).attribute7 ;
2931 l_eco_revision_tbl(i).attribute8 := p_revision_type_tbl(i).attribute8 ;
2932 l_eco_revision_tbl(i).attribute9 := p_revision_type_tbl(i).attribute9 ;
2933 l_eco_revision_tbl(i).attribute10 := p_revision_type_tbl(i).attribute10;
2934 l_eco_revision_tbl(i).attribute11 := p_revision_type_tbl(i).attribute11 ;
2935 l_eco_revision_tbl(i).attribute12 := p_revision_type_tbl(i).attribute12 ;
2936 l_eco_revision_tbl(i).attribute13 := p_revision_type_tbl(i).attribute13 ;
2937 l_eco_revision_tbl(i).attribute14 := p_revision_type_tbl(i).attribute14 ;
2938 l_eco_revision_tbl(i).attribute15 := p_revision_type_tbl(i).attribute15 ;
2939 l_eco_revision_tbl(i).change_management_type := p_revision_type_tbl(i).change_management_type ;
2940 l_eco_revision_tbl(i).original_system_reference := p_revision_type_tbl(i).original_system_reference ;
2941 l_eco_revision_tbl(i).return_status := p_revision_type_tbl(i).return_status ;
2942 l_eco_revision_tbl(i).transaction_type := p_revision_type_tbl(i).transaction_type ;
2943 l_eco_revision_tbl(i).transaction_id := p_revision_type_tbl(i).transaction_id ;
2944 END LOOP;
2945 END IF;
2946 IF(p_revised_item_type_tbl IS NOT NULL AND p_revised_item_type_tbl.COUNT > 0) THEN
2947 FOR i IN p_revised_item_type_tbl.FIRST..p_revised_item_type_tbl.LAST
2948 LOOP
2949 l_inventory_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id (
2950 p_organization_id => l_organization_id
2951 ,p_item_number => p_revised_item_type_tbl(i).revised_item_name
2952 );
2953 -- To get effectivty date of recently created revision Bug 7197943
2954
2955 l_effectivity_date := get_latest_effectivity_date(
2956 p_inventory_item_id => l_inventory_item_id,
2957 p_organization_id => l_organization_id );
2958
2959 -- Bug# 7662420
2960 -- If the effective date that we get from the DB is less then the sysdate
2961 -- If the incomming date from end system is null or miss date
2962 -- Then take the max of sysdate or effective date.
2963
2964 IF(l_effectivity_date < SYSDATE ) THEN
2965 l_effectivity_date := SYSDATE ;
2966 END IF;
2967
2968 l_revised_item_tbl(l_revised_item_tbl_count).eco_name := p_change_order.eco_name ;
2969 l_revised_item_tbl(l_revised_item_tbl_count).organization_code := p_change_order.organization_code ;
2970 l_revised_item_tbl(l_revised_item_tbl_count).revised_item_name := p_revised_item_type_tbl(i).revised_item_name ;
2971
2972 IF(p_revised_item_type_tbl(i).new_revised_item_revision = p_revised_item_type_tbl(i).from_item_revision) THEN
2973 --Bug 7119898 If a change order already exists for the revision being sent in, set the revision to NULL
2974 IF (is_new_revision_exists(
2975 p_item_number => l_revised_item_tbl(l_revised_item_tbl_count).revised_item_name,
2976 p_revision => p_revised_item_type_tbl(i).new_revised_item_revision,
2977 p_org_code => l_revised_item_tbl(l_revised_item_tbl_count).organization_code
2978 ) = FND_API.g_true ) THEN
2979 l_revised_item_tbl(l_revised_item_tbl_count).new_revised_item_revision := NULL;
2980 l_revised_item_tbl(l_revised_item_tbl_count).from_item_revision := p_revised_item_type_tbl(i).from_item_revision ;
2981 ELSE
2982 l_revised_item_tbl(l_revised_item_tbl_count).new_revised_item_revision := p_revised_item_type_tbl(i).new_revised_item_revision;
2983 l_revised_item_tbl(l_revised_item_tbl_count).from_item_revision := NULL ;
2984 END IF;
2985 ELSE
2986 l_revised_item_tbl(l_revised_item_tbl_count).new_revised_item_revision := p_revised_item_type_tbl(i).new_revised_item_revision;
2987 l_revised_item_tbl(l_revised_item_tbl_count).from_item_revision := p_revised_item_type_tbl(i).from_item_revision ;
2988 END IF;
2989
2990 l_revised_item_tbl(l_revised_item_tbl_count).new_revised_item_rev_desc := p_revised_item_type_tbl(i).new_revised_item_rev_desc;
2991 l_revised_item_tbl(l_revised_item_tbl_count).updated_revised_item_revision := p_revised_item_type_tbl(i).upd_revised_item_revision;
2992 IF (p_revised_item_type_tbl(i).start_effective_date IS NULL
2993 OR p_revised_item_type_tbl(i).start_effective_date = fnd_api.g_miss_date) THEN
2994 l_revised_item_tbl(l_revised_item_tbl_count).start_effective_date := l_effectivity_date + 1/86400; -- BUG 7197943 To keep efectivity date of next rev 1 sec higher than earlier rev
2995 ELSE
2996 l_revised_item_tbl(l_revised_item_tbl_count).start_effective_date := p_revised_item_type_tbl(i).start_effective_date;
2997 END IF;
2998 IF (p_revised_item_type_tbl(i).new_effective_date IS NULL
2999 OR p_revised_item_type_tbl(i).new_effective_date = fnd_api.g_miss_date) THEN
3000 l_revised_item_tbl(l_revised_item_tbl_count).new_effective_date := l_effectivity_date + 1/86400; --BUG 7197943 To keep efectivity date of next rev 1 sec higher than earlier rev
3001 ELSE
3002 l_revised_item_tbl(l_revised_item_tbl_count).new_effective_date := p_revised_item_type_tbl(i).new_effective_date ;
3003 END IF;
3004 l_revised_item_tbl(l_revised_item_tbl_count).alternate_bom_code := p_revised_item_type_tbl(i).alternate_bom_code ;
3005
3006 -- To set the status of revisied Item based on the ECO status:
3007 l_revised_item_tbl(l_revised_item_tbl_count).status_type := l_status_type;
3008
3009 l_revised_item_tbl(l_revised_item_tbl_count).mrp_active := p_revised_item_type_tbl(i).mrp_active ;
3010 l_revised_item_tbl(l_revised_item_tbl_count).earliest_effective_date := p_revised_item_type_tbl(i).earliest_effective_date ;
3011 l_revised_item_tbl(l_revised_item_tbl_count).use_up_item_name := p_revised_item_type_tbl(i).use_up_item_name;
3012 l_revised_item_tbl(l_revised_item_tbl_count).use_up_plan_name := p_revised_item_type_tbl(i).use_up_plan_name ;
3013 l_revised_item_tbl(l_revised_item_tbl_count).requestor := p_revised_item_type_tbl(i).requestor ;
3014 l_revised_item_tbl(l_revised_item_tbl_count).disposition_type := p_revised_item_type_tbl(i).disposition_type ;
3015 l_revised_item_tbl(l_revised_item_tbl_count).update_wip := p_revised_item_type_tbl(i).up_wip ;
3016 l_revised_item_tbl(l_revised_item_tbl_count).cancel_comments := p_revised_item_type_tbl(i).cancel_comments ;
3017 l_revised_item_tbl(l_revised_item_tbl_count).change_description := p_revised_item_type_tbl(i).change_description;
3018 l_revised_item_tbl(l_revised_item_tbl_count).attribute_category := p_revised_item_type_tbl(i).attribute_category;
3019 l_revised_item_tbl(l_revised_item_tbl_count).attribute1 := p_revised_item_type_tbl(i).attribute1;
3020 l_revised_item_tbl(l_revised_item_tbl_count).attribute2 := p_revised_item_type_tbl(i).attribute2 ;
3021 l_revised_item_tbl(l_revised_item_tbl_count).attribute3 := p_revised_item_type_tbl(i).attribute3 ;
3022 l_revised_item_tbl(l_revised_item_tbl_count).attribute4 := p_revised_item_type_tbl(i).attribute4;
3023 l_revised_item_tbl(l_revised_item_tbl_count).attribute5 := p_revised_item_type_tbl(i).attribute5;
3024 l_revised_item_tbl(l_revised_item_tbl_count).attribute6 := p_revised_item_type_tbl(i).attribute6;
3025 l_revised_item_tbl(l_revised_item_tbl_count).attribute7 := p_revised_item_type_tbl(i).attribute7;
3026 l_revised_item_tbl(l_revised_item_tbl_count).attribute8 := p_revised_item_type_tbl(i).attribute8;
3027 l_revised_item_tbl(l_revised_item_tbl_count).attribute9 := p_revised_item_type_tbl(i).attribute9 ;
3028 l_revised_item_tbl(l_revised_item_tbl_count).attribute10 := p_revised_item_type_tbl(i).attribute10;
3029 l_revised_item_tbl(l_revised_item_tbl_count).attribute11 := p_revised_item_type_tbl(i).attribute11;
3030 l_revised_item_tbl(l_revised_item_tbl_count).attribute12 := p_revised_item_type_tbl(i).attribute12;
3031 l_revised_item_tbl(l_revised_item_tbl_count).attribute13 := p_revised_item_type_tbl(i).attribute13;
3032 l_revised_item_tbl(l_revised_item_tbl_count).attribute14 := p_revised_item_type_tbl(i).attribute14;
3033 l_revised_item_tbl(l_revised_item_tbl_count).attribute15 := p_revised_item_type_tbl(i).attribute15 ;
3034 l_revised_item_tbl(l_revised_item_tbl_count).from_end_item_unit_number := p_revised_item_type_tbl(i).from_end_item_unit_number;
3035 l_revised_item_tbl(l_revised_item_tbl_count).new_from_end_item_unit_number := p_revised_item_type_tbl(i).new_from_end_item_unit_number ;
3036 l_revised_item_tbl(l_revised_item_tbl_count).original_system_reference := p_revised_item_type_tbl(i).original_system_reference ;
3037 l_revised_item_tbl(l_revised_item_tbl_count).return_status := p_revised_item_type_tbl(i).return_status ;
3038 l_revised_item_tbl(l_revised_item_tbl_count).transaction_type := p_revised_item_type_tbl(i).transaction_type ;
3039 l_revised_item_tbl(l_revised_item_tbl_count).transaction_id := p_revised_item_type_tbl(i).transaction_id ;
3040 l_revised_item_tbl(l_revised_item_tbl_count).from_work_order := p_revised_item_type_tbl(i).from_work_order ;
3041 l_revised_item_tbl(l_revised_item_tbl_count).to_work_order := p_revised_item_type_tbl(i).to_work_order;
3042 l_revised_item_tbl(l_revised_item_tbl_count).from_cumulative_quantity := p_revised_item_type_tbl(i).from_cumulative_quantity ;
3043 l_revised_item_tbl(l_revised_item_tbl_count).lot_number := p_revised_item_type_tbl(i).lot_number ;
3044 l_revised_item_tbl(l_revised_item_tbl_count).completion_subinventory := p_revised_item_type_tbl(i).completion_subinventory ;
3045 l_revised_item_tbl(l_revised_item_tbl_count).completion_location_name := p_revised_item_type_tbl(i).completion_location_name;
3046 l_revised_item_tbl(l_revised_item_tbl_count).priority := p_revised_item_type_tbl(i).priority ;
3047 l_revised_item_tbl(l_revised_item_tbl_count).ctp_flag := p_revised_item_type_tbl(i).ctp_flag;
3048 l_revised_item_tbl(l_revised_item_tbl_count).new_routing_revision := p_revised_item_type_tbl(i).new_routing_revision ;
3049 l_revised_item_tbl(l_revised_item_tbl_count).updated_routing_revision := p_revised_item_type_tbl(i).upd_routing_revision ;
3050 l_revised_item_tbl(l_revised_item_tbl_count).routing_comment := p_revised_item_type_tbl(i).routing_comment ;
3051 l_revised_item_tbl(l_revised_item_tbl_count).eco_for_production := p_revised_item_type_tbl(i).eco_for_production;
3052 l_revised_item_tbl(l_revised_item_tbl_count).change_management_type := p_revised_item_type_tbl(i).change_management_type;
3053 l_revised_item_tbl(l_revised_item_tbl_count).transfer_or_copy := p_revised_item_type_tbl(i).transfer_or_copy;
3054 l_revised_item_tbl(l_revised_item_tbl_count).transfer_or_copy_item := p_revised_item_type_tbl(i).transfer_or_copy_item ;
3055 l_revised_item_tbl(l_revised_item_tbl_count).transfer_or_copy_bill := p_revised_item_type_tbl(i).transfer_or_copy_bill ;
3056 l_revised_item_tbl(l_revised_item_tbl_count).transfer_or_copy_routing := p_revised_item_type_tbl(i).transfer_or_copy_routing;
3057 l_revised_item_tbl(l_revised_item_tbl_count).copy_to_item := p_revised_item_type_tbl(i).copy_to_item ;
3058 l_revised_item_tbl(l_revised_item_tbl_count).copy_to_item_desc := p_revised_item_type_tbl(i).copy_to_item_desc ;
3059 l_revised_item_tbl(l_revised_item_tbl_count).parent_revised_item_name := p_revised_item_type_tbl(i).parent_revised_item_name;
3060 l_revised_item_tbl(l_revised_item_tbl_count).parent_alternate_name := p_revised_item_type_tbl(i).parent_alternate_name;
3061 l_revised_item_tbl(l_revised_item_tbl_count).selection_option := p_revised_item_type_tbl(i).selection_option ;
3062 l_revised_item_tbl(l_revised_item_tbl_count).selection_date := p_revised_item_type_tbl(i).selection_date ;
3063 l_revised_item_tbl(l_revised_item_tbl_count).selection_unit_number := p_revised_item_type_tbl(i).selection_unit_number;
3064 l_revised_item_tbl(l_revised_item_tbl_count).current_lifecycle_phase_name := p_revised_item_type_tbl(i).current_lifecycle_phase_name ;
3065 l_revised_item_tbl(l_revised_item_tbl_count).new_lifecycle_phase_name := p_revised_item_type_tbl(i).new_lifecycle_phase_name ;
3066 l_revised_item_tbl(l_revised_item_tbl_count).from_end_item_revision := p_revised_item_type_tbl(i).from_end_item_revision;
3067 l_revised_item_tbl(l_revised_item_tbl_count).from_end_item_strc_rev := p_revised_item_type_tbl(i).from_end_item_strc_rev ;
3068 l_revised_item_tbl(l_revised_item_tbl_count).enable_item_in_local_org := p_revised_item_type_tbl(i).enable_item_in_local_org ;
3069 l_revised_item_tbl(l_revised_item_tbl_count).create_bom_in_local_org := p_revised_item_type_tbl(i).create_bom_in_local_org ;
3070 l_revised_item_tbl(l_revised_item_tbl_count).new_structure_revision := p_revised_item_type_tbl(i).new_structure_revision ;
3071 l_revised_item_tbl(l_revised_item_tbl_count).plan_level := p_revised_item_type_tbl(i).plan_level ;
3072 l_revised_item_tbl(l_revised_item_tbl_count).from_end_item_name := p_revised_item_type_tbl(i).from_end_item_name ;
3073 l_revised_item_tbl(l_revised_item_tbl_count).from_end_item_alternate := p_revised_item_type_tbl(i).from_end_item_alternate ;
3074 l_revised_item_tbl(l_revised_item_tbl_count).current_structure_rev_name := p_revised_item_type_tbl(i).current_structure_rev_name;
3075 l_revised_item_tbl(l_revised_item_tbl_count).reschedule_comments := p_revised_item_type_tbl(i).reschedule_comments ;
3076
3077 l_revised_item_tbl(l_revised_item_tbl_count).new_revision_label := p_revised_item_type_tbl(i).new_revision_label ;
3078 l_revised_item_tbl(l_revised_item_tbl_count).new_revision_reason := p_revised_item_type_tbl(i).new_revision_reason;
3079 l_revised_item_tbl(l_revised_item_tbl_count).structure_type_name := p_revised_item_type_tbl(i).structure_type_name ;
3080
3081
3082 IF(p_revised_item_type_tbl(i).component_item_tbl IS NOT NULL AND p_revised_item_type_tbl(i).component_item_tbl.COUNT > 0) THEN
3083 FOR j IN p_revised_item_type_tbl(i).component_item_tbl.FIRST..p_revised_item_type_tbl(i).component_item_tbl.LAST
3084 LOOP
3085 --If context org is child org and components does not exist,item assignment should be done
3086 process_assign_items(
3087 p_organization_id => l_organization_id,
3088 p_item_name => p_revised_item_type_tbl(i).component_item_tbl(j).component_item_name,
3089 x_return_status => x_out.output_status.return_status ,
3090 x_msg_data => x_out.output_status.msg_data ,
3091 x_msg_count => x_out.output_status.msg_count
3092 );
3093 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
3094 RAISE FND_API.g_exc_unexpected_error;
3095 END IF;
3096
3097 l_rev_component_tbl(l_rev_component_tbl_count).eco_name := p_change_order.eco_name;
3098 l_rev_component_tbl(l_rev_component_tbl_count).organization_code := p_change_order.organization_code ;
3099 l_rev_component_tbl(l_rev_component_tbl_count).revised_item_name := p_revised_item_type_tbl(i).revised_item_name;
3100 l_rev_component_tbl(l_rev_component_tbl_count).new_revised_item_revision := l_revised_item_tbl(l_revised_item_tbl_count).new_revised_item_revision;
3101 IF ( p_revised_item_type_tbl(i).component_item_tbl(j).start_effective_date IS NULL
3102 OR p_revised_item_type_tbl(i).component_item_tbl(j).start_effective_date = fnd_api.g_miss_date) THEN
3103 l_rev_component_tbl(l_rev_component_tbl_count).start_effective_date := l_effectivity_date + 1/86400 ; --BUG 7197943 To keep efectivity date of next rev 1 sec higher than earlier rev
3104 ELSE
3105 l_rev_component_tbl(l_rev_component_tbl_count).start_effective_date := p_revised_item_type_tbl(i).component_item_tbl(j).start_effective_date;
3106 END IF;
3107 IF (p_revised_item_type_tbl(i).component_item_tbl(j).new_effectivity_date IS NULL
3108 OR p_revised_item_type_tbl(i).component_item_tbl(j).new_effectivity_date = fnd_api.g_miss_date) THEN
3109 l_rev_component_tbl(l_rev_component_tbl_count).new_effectivity_date := l_effectivity_date + 1/86400; --BUG 7197943 To keep efectivity date of next rev 1 sec higher than earlier rev
3110 ELSE
3111 l_rev_component_tbl(l_rev_component_tbl_count).new_effectivity_date := p_revised_item_type_tbl(i).component_item_tbl(j).new_effectivity_date ;
3112 END IF;
3113 l_rev_component_tbl(l_rev_component_tbl_count).disable_date := p_revised_item_type_tbl(i).component_item_tbl(j).disable_date;
3114 l_rev_component_tbl(l_rev_component_tbl_count).operation_sequence_number := p_revised_item_type_tbl(i).component_item_tbl(j).operation_sequence_number ;
3115 l_rev_component_tbl(l_rev_component_tbl_count).component_item_name := p_revised_item_type_tbl(i).component_item_tbl(j).component_item_name ;
3116 l_rev_component_tbl(l_rev_component_tbl_count).alternate_bom_code := p_revised_item_type_tbl(i).alternate_bom_code ;
3117 l_rev_component_tbl(l_rev_component_tbl_count).acd_type := p_revised_item_type_tbl(i).component_item_tbl(j).acd_type ;
3118 l_rev_component_tbl(l_rev_component_tbl_count).old_effectivity_date := p_revised_item_type_tbl(i).component_item_tbl(j).old_effectivity_date ;
3119 l_rev_component_tbl(l_rev_component_tbl_count).old_operation_sequence_number := p_revised_item_type_tbl(i).component_item_tbl(j).old_operation_sequence_number;
3120 l_rev_component_tbl(l_rev_component_tbl_count).new_operation_sequence_number := p_revised_item_type_tbl(i).component_item_tbl(j).new_operation_sequence_number;
3121 l_rev_component_tbl(l_rev_component_tbl_count).item_sequence_number := p_revised_item_type_tbl(i).component_item_tbl(j).item_sequence_number ;
3122 l_rev_component_tbl(l_rev_component_tbl_count).basis_type := p_revised_item_type_tbl(i).component_item_tbl(j).basis_type ;
3123 l_rev_component_tbl(l_rev_component_tbl_count).quantity_per_assembly := p_revised_item_type_tbl(i).component_item_tbl(j).quantity_per_assembly ;
3124 l_rev_component_tbl(l_rev_component_tbl_count).inverse_quantity := p_revised_item_type_tbl(i).component_item_tbl(j).inverse_quantity ;
3125 l_rev_component_tbl(l_rev_component_tbl_count).planning_percent := p_revised_item_type_tbl(i).component_item_tbl(j).planning_percent ;
3126 l_rev_component_tbl(l_rev_component_tbl_count).projected_yield := p_revised_item_type_tbl(i).component_item_tbl(j).projected_yield ;
3127 l_rev_component_tbl(l_rev_component_tbl_count).include_in_cost_rollup := p_revised_item_type_tbl(i).component_item_tbl(j).include_in_cost_rollup ;
3128 l_rev_component_tbl(l_rev_component_tbl_count).wip_supply_type := p_revised_item_type_tbl(i).component_item_tbl(j).wip_supply_type ;
3129 l_rev_component_tbl(l_rev_component_tbl_count).so_basis := p_revised_item_type_tbl(i).component_item_tbl(j).so_basis ;
3130 l_rev_component_tbl(l_rev_component_tbl_count).optional := p_revised_item_type_tbl(i).component_item_tbl(j).optional ;
3131 l_rev_component_tbl(l_rev_component_tbl_count).mutually_exclusive := p_revised_item_type_tbl(i).component_item_tbl(j).mutually_exclusive ;
3132 l_rev_component_tbl(l_rev_component_tbl_count).check_atp := p_revised_item_type_tbl(i).component_item_tbl(j).check_atp ;
3133 l_rev_component_tbl(l_rev_component_tbl_count).shipping_allowed := p_revised_item_type_tbl(i).component_item_tbl(j).shipping_allowed ;
3134 l_rev_component_tbl(l_rev_component_tbl_count).required_to_ship := p_revised_item_type_tbl(i).component_item_tbl(j).required_to_ship ;
3135 l_rev_component_tbl(l_rev_component_tbl_count).required_for_revenue := p_revised_item_type_tbl(i).component_item_tbl(j).required_for_revenue;
3136 l_rev_component_tbl(l_rev_component_tbl_count).include_on_ship_docs := p_revised_item_type_tbl(i).component_item_tbl(j).include_on_ship_docs;
3137 l_rev_component_tbl(l_rev_component_tbl_count).quantity_related := p_revised_item_type_tbl(i).component_item_tbl(j).quantity_related;
3138 l_rev_component_tbl(l_rev_component_tbl_count).supply_subinventory := p_revised_item_type_tbl(i).component_item_tbl(j).supply_subinventory;
3139 l_rev_component_tbl(l_rev_component_tbl_count).location_name := p_revised_item_type_tbl(i).component_item_tbl(j).location_name ;
3140 l_rev_component_tbl(l_rev_component_tbl_count).minimum_allowed_quantity := p_revised_item_type_tbl(i).component_item_tbl(j).minimum_allowed_quantity ;
3141 l_rev_component_tbl(l_rev_component_tbl_count).maximum_allowed_quantity := p_revised_item_type_tbl(i).component_item_tbl(j).maximum_allowed_quantity;
3142 l_rev_component_tbl(l_rev_component_tbl_count).comments := p_revised_item_type_tbl(i).component_item_tbl(j).comments ;
3143 l_rev_component_tbl(l_rev_component_tbl_count).cancel_comments := p_revised_item_type_tbl(i).component_item_tbl(j).cancel_comments;
3144 l_rev_component_tbl(l_rev_component_tbl_count).attribute_category := p_revised_item_type_tbl(i).component_item_tbl(j).attribute_category;
3145 l_rev_component_tbl(l_rev_component_tbl_count).attribute1 := p_revised_item_type_tbl(i).component_item_tbl(j).attribute1 ;
3146 l_rev_component_tbl(l_rev_component_tbl_count).attribute2 := p_revised_item_type_tbl(i).component_item_tbl(j).attribute2 ;
3147 l_rev_component_tbl(l_rev_component_tbl_count).attribute3 := p_revised_item_type_tbl(i).component_item_tbl(j).attribute3 ;
3148 l_rev_component_tbl(l_rev_component_tbl_count).attribute4 := p_revised_item_type_tbl(i).component_item_tbl(j).attribute4 ;
3149 l_rev_component_tbl(l_rev_component_tbl_count).attribute5 := p_revised_item_type_tbl(i).component_item_tbl(j).attribute5 ;
3150 l_rev_component_tbl(l_rev_component_tbl_count).attribute6 := p_revised_item_type_tbl(i).component_item_tbl(j).attribute6 ;
3151 l_rev_component_tbl(l_rev_component_tbl_count).attribute7 := p_revised_item_type_tbl(i).component_item_tbl(j).attribute7 ;
3152 l_rev_component_tbl(l_rev_component_tbl_count).attribute8 := p_revised_item_type_tbl(i).component_item_tbl(j).attribute8 ;
3153 l_rev_component_tbl(l_rev_component_tbl_count).attribute9 := p_revised_item_type_tbl(i).component_item_tbl(j).attribute9 ;
3154 l_rev_component_tbl(l_rev_component_tbl_count).attribute10 := p_revised_item_type_tbl(i).component_item_tbl(j).attribute10;
3155 l_rev_component_tbl(l_rev_component_tbl_count).attribute11 := p_revised_item_type_tbl(i).component_item_tbl(j).attribute11;
3156 l_rev_component_tbl(l_rev_component_tbl_count).attribute12 := p_revised_item_type_tbl(i).component_item_tbl(j).attribute12;
3157 l_rev_component_tbl(l_rev_component_tbl_count).attribute13 := p_revised_item_type_tbl(i).component_item_tbl(j).attribute13;
3158 l_rev_component_tbl(l_rev_component_tbl_count).attribute14 := p_revised_item_type_tbl(i).component_item_tbl(j).attribute14;
3159 l_rev_component_tbl(l_rev_component_tbl_count).attribute15 := p_revised_item_type_tbl(i).component_item_tbl(j).attribute15;
3160 l_rev_component_tbl(l_rev_component_tbl_count).from_end_item_unit_number := p_revised_item_type_tbl(i).component_item_tbl(j).from_end_item_unit_number;
3161 l_rev_component_tbl(l_rev_component_tbl_count).old_from_end_item_unit_number := p_revised_item_type_tbl(i).component_item_tbl(j).old_from_end_item_unit_number ;
3162 l_rev_component_tbl(l_rev_component_tbl_count).new_from_end_item_unit_number := p_revised_item_type_tbl(i).component_item_tbl(j).new_from_end_item_unit_number;
3163 l_rev_component_tbl(l_rev_component_tbl_count).to_end_item_unit_number := p_revised_item_type_tbl(i).component_item_tbl(j).to_end_item_unit_number;
3164 l_rev_component_tbl(l_rev_component_tbl_count).new_routing_revision := p_revised_item_type_tbl(i).component_item_tbl(j).new_routing_revision ;
3165 l_rev_component_tbl(l_rev_component_tbl_count).enforce_int_requirements := p_revised_item_type_tbl(i).component_item_tbl(j).enforce_int_requirements;
3166 l_rev_component_tbl(l_rev_component_tbl_count).auto_request_material := p_revised_item_type_tbl(i).component_item_tbl(j).auto_request_material;
3167 l_rev_component_tbl(l_rev_component_tbl_count).suggested_vendor_name := p_revised_item_type_tbl(i).component_item_tbl(j).suggested_vendor_name;
3168 l_rev_component_tbl(l_rev_component_tbl_count).unit_price := p_revised_item_type_tbl(i).component_item_tbl(j).unit_price;
3169 l_rev_component_tbl(l_rev_component_tbl_count).original_system_reference := p_revised_item_type_tbl(i).component_item_tbl(j).original_system_reference;
3170 l_rev_component_tbl(l_rev_component_tbl_count).return_status := p_revised_item_type_tbl(i).component_item_tbl(j).return_status ;
3171 l_rev_component_tbl(l_rev_component_tbl_count).transaction_type := p_revised_item_type_tbl(i).component_item_tbl(j).transaction_type;
3172 l_rev_component_tbl(l_rev_component_tbl_count).row_identifier := p_revised_item_type_tbl(i).component_item_tbl(j).row_identifier ;
3173 IF l_rev_component_tbl(l_rev_component_tbl_count).acd_type IN (l_acd_update,l_acd_delete)
3174 THEN
3175 get_existing_component_attr(
3176 p_organization_id => l_organization_id
3177 ,p_revised_item_name => l_rev_component_tbl(l_rev_component_tbl_count).revised_item_name
3178 ,p_component_item_name => l_rev_component_tbl(l_rev_component_tbl_count).component_item_name
3179 ,p_op_sequence_number => l_rev_component_tbl(l_rev_component_tbl_count).operation_sequence_number
3180 ,p_alternate_bom_code => l_rev_component_tbl(l_rev_component_tbl_count).alternate_bom_code
3181 ,x_old_effectivity_date => l_old_effectivity_date
3182 ,x_old_op_sequence_num => l_old_op_sequence_num
3183 ,x_old_fm_end_item_unit => l_old_fm_end_item_unit
3184 );
3185
3186 IF l_rev_component_tbl(l_rev_component_tbl_count).old_effectivity_date IS NULL
3187 THEN
3188 l_rev_component_tbl(l_rev_component_tbl_count).old_effectivity_date := l_old_effectivity_date;
3189 END IF;
3190 IF l_rev_component_tbl(l_rev_component_tbl_count).old_operation_sequence_number IS NULL
3191 THEN
3192 l_rev_component_tbl(l_rev_component_tbl_count).old_operation_sequence_number := l_old_op_sequence_num;
3193 END IF;
3194 IF l_rev_component_tbl(l_rev_component_tbl_count).old_from_end_item_unit_number IS NULL
3195 THEN
3196 l_rev_component_tbl(l_rev_component_tbl_count).old_from_end_item_unit_number := l_old_fm_end_item_unit;
3197 END IF;
3198 END IF;
3199
3200 IF(p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl IS NOT NULL AND p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl.COUNT > 0) THEN
3201 FOR k IN 1..p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl.COUNT
3202 LOOP
3203 l_ref_designator_tbl(l_ref_designator_tbl_count).eco_name := p_change_order.eco_name;
3204 l_ref_designator_tbl(l_ref_designator_tbl_count).organization_code := p_change_order.organization_code ;
3205 l_ref_designator_tbl(l_ref_designator_tbl_count).revised_item_name := p_revised_item_type_tbl(i).revised_item_name;
3206 l_ref_designator_tbl(l_ref_designator_tbl_count).new_revised_item_revision := l_revised_item_tbl(l_revised_item_tbl_count).new_revised_item_revision;
3207 l_ref_designator_tbl(l_ref_designator_tbl_count).operation_sequence_number := p_revised_item_type_tbl(i).component_item_tbl(j).operation_sequence_number;
3208 l_ref_designator_tbl(l_ref_designator_tbl_count).component_item_name := p_revised_item_type_tbl(i).component_item_tbl(j).component_item_name ;
3209 l_ref_designator_tbl(l_ref_designator_tbl_count).alternate_bom_code := p_revised_item_type_tbl(i).alternate_bom_code;
3210 IF (p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).start_effective_date IS NULL
3211 OR p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).start_effective_date =fnd_api.g_miss_date) THEN
3212 l_ref_designator_tbl(l_ref_designator_tbl_count).start_effective_date := l_effectivity_date + 1/86400; --BUG 7197943 To keep efectivity date of next rev 1 sec higher than earlier rev
3213 ELSE
3214 l_ref_designator_tbl(l_ref_designator_tbl_count).start_effective_date := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).start_effective_date;
3215 END IF;
3216 l_ref_designator_tbl(l_ref_designator_tbl_count).reference_designator_name := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).reference_designator_name;
3217 l_ref_designator_tbl(l_ref_designator_tbl_count).acd_type := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).acd_type ;
3218 l_ref_designator_tbl(l_ref_designator_tbl_count).ref_designator_comment := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).ref_designator_comment ;
3219 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute_category := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute_category;
3220 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute1 := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute1 ;
3221 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute2 := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute2 ;
3222 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute3 := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute3 ;
3223 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute4 := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute4;
3224 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute5 := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute5;
3225 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute6 := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute6;
3226 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute7 := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute7;
3227 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute8 := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute8;
3228 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute9 := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute9;
3229 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute10 := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute10 ;
3230 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute11 := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute11;
3231 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute12 := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute12;
3232 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute13 := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute13;
3233 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute14 := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute14;
3234 l_ref_designator_tbl(l_ref_designator_tbl_count).attribute15 := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).attribute15;
3235 l_ref_designator_tbl(l_ref_designator_tbl_count).original_system_reference := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).original_system_reference;
3236 l_ref_designator_tbl(l_ref_designator_tbl_count).new_reference_designator := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).new_reference_designator;
3237 l_ref_designator_tbl(l_ref_designator_tbl_count).from_end_item_unit_number := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).from_end_item_unit_number;
3238 l_ref_designator_tbl(l_ref_designator_tbl_count).new_routing_revision := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).new_routing_revision;
3239 l_ref_designator_tbl(l_ref_designator_tbl_count).return_status := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).return_status;
3240 l_ref_designator_tbl(l_ref_designator_tbl_count).transaction_type := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).transaction_type ;
3241 l_ref_designator_tbl(l_ref_designator_tbl_count).row_identifier := p_revised_item_type_tbl(i).component_item_tbl(j).reference_designator_tbl(k).row_identifier ;
3242 l_ref_designator_tbl_count := l_ref_designator_tbl_count +1;
3243 END LOOP;
3244 END IF;
3245
3246 IF(p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl IS NOT NULL AND p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl.COUNT > 0) THEN
3247 FOR k IN 1..p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl.COUNT
3248 LOOP
3249 --If context org is child org and components does not exist,item assignment should be done
3250 process_assign_items(
3251 p_organization_id => l_organization_id,
3252 p_item_name => p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).substitute_component_name,
3253 x_return_status => x_out.output_status.return_status ,
3254 x_msg_data => x_out.output_status.msg_data ,
3255 x_msg_count => x_out.output_status.msg_count
3256 );
3257 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
3258 RAISE FND_API.g_exc_unexpected_error;
3259 END IF;
3260
3261 l_sub_component_tbl(l_sub_component_tbl_count).eco_name := p_change_order.eco_name;
3262 l_sub_component_tbl(l_sub_component_tbl_count).organization_code := p_change_order.organization_code ;
3263 l_sub_component_tbl(l_sub_component_tbl_count).revised_item_name := p_revised_item_type_tbl(i).revised_item_name;
3264 l_sub_component_tbl(l_sub_component_tbl_count).new_revised_item_revision := l_revised_item_tbl(l_revised_item_tbl_count).new_revised_item_revision;
3265 l_sub_component_tbl(l_sub_component_tbl_count).operation_sequence_number := p_revised_item_type_tbl(i).component_item_tbl(j).operation_sequence_number;
3266 l_sub_component_tbl(l_sub_component_tbl_count).component_item_name := p_revised_item_type_tbl(i).component_item_tbl(j).component_item_name ;
3267 l_sub_component_tbl(l_sub_component_tbl_count).alternate_bom_code := p_revised_item_type_tbl(i).alternate_bom_code;
3268 IF (p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).start_effective_date IS NULL
3269 OR p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).start_effective_date =fnd_api.g_miss_date) THEN
3270 l_sub_component_tbl(l_sub_component_tbl_count).start_effective_date := l_effectivity_date + 1/86400;
3271 ELSE
3272 l_sub_component_tbl(l_sub_component_tbl_count).start_effective_date := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).start_effective_date;
3273 END IF;
3274 l_sub_component_tbl(l_sub_component_tbl_count).substitute_component_name := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).substitute_component_name;
3275 l_sub_component_tbl(l_sub_component_tbl_count).new_substitute_component_name := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).new_substitute_component_name ;
3276 l_sub_component_tbl(l_sub_component_tbl_count).acd_type := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).acd_type ;
3277 l_sub_component_tbl(l_sub_component_tbl_count).substitute_item_quantity := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).substitute_item_quantity ;
3278 l_sub_component_tbl(l_sub_component_tbl_count).attribute_category := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute_category;
3279 l_sub_component_tbl(l_sub_component_tbl_count).attribute1 := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute1;
3280 l_sub_component_tbl(l_sub_component_tbl_count).attribute2 := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute2 ;
3281 l_sub_component_tbl(l_sub_component_tbl_count).attribute3 := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute3;
3282 l_sub_component_tbl(l_sub_component_tbl_count).attribute4 := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute4 ;
3283 l_sub_component_tbl(l_sub_component_tbl_count).attribute5 := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute5 ;
3284 l_sub_component_tbl(l_sub_component_tbl_count).attribute6 := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute6 ;
3285 l_sub_component_tbl(l_sub_component_tbl_count).attribute7 := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute7 ;
3286 l_sub_component_tbl(l_sub_component_tbl_count).attribute8 := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute8 ;
3287 l_sub_component_tbl(l_sub_component_tbl_count).attribute9 := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute9 ;
3288 l_sub_component_tbl(l_sub_component_tbl_count).attribute10 := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute10 ;
3289 l_sub_component_tbl(l_sub_component_tbl_count).attribute11 := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute11 ;
3290 l_sub_component_tbl(l_sub_component_tbl_count).attribute12 := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute12 ;
3291 l_sub_component_tbl(l_sub_component_tbl_count).attribute13 := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute13 ;
3292 l_sub_component_tbl(l_sub_component_tbl_count).attribute14 := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute14 ;
3293 l_sub_component_tbl(l_sub_component_tbl_count).attribute15 := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).attribute15 ;
3294 l_sub_component_tbl(l_sub_component_tbl_count).original_system_reference := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).original_system_reference ;
3295 l_sub_component_tbl(l_sub_component_tbl_count).from_end_item_unit_number := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).from_end_item_unit_number;
3296 l_sub_component_tbl(l_sub_component_tbl_count).new_routing_revision := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).new_routing_revision ;
3297 l_sub_component_tbl(l_sub_component_tbl_count).enforce_int_requirements := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).enforce_int_requirements ;
3298 l_sub_component_tbl(l_sub_component_tbl_count).return_status := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).return_status ;
3299 l_sub_component_tbl(l_sub_component_tbl_count).transaction_type := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).transaction_type ;
3300 l_sub_component_tbl(l_sub_component_tbl_count).row_identifier := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).row_identifier ;
3301 l_sub_component_tbl(l_sub_component_tbl_count).inverse_quantity := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).inverse_quantity ;
3302 l_sub_component_tbl(l_sub_component_tbl_count).program_id := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).program_id ;
3303 l_sub_component_tbl_count := l_sub_component_tbl_count + 1;
3304 END LOOP;
3305 END IF;
3306 l_rev_component_tbl_count := l_rev_component_tbl_count +1;
3307 END LOOP;
3308 END IF;
3309 l_revised_item_tbl_count := l_revised_item_tbl_count + 1;
3310 END LOOP;
3311 END IF;
3312 INV_EBI_UTIL.debug_line('STEP: 20 START CALLING ENG_ECO_PUB.process_eco');
3313
3314 --To create ECO
3315 ENG_ECO_PUB.process_eco (
3316 p_api_version_number => 1.0
3317 ,p_eco_rec => l_eco_rec
3318 ,p_eco_revision_tbl => l_eco_revision_tbl
3319 ,p_revised_item_tbl => l_revised_item_tbl
3320 ,p_rev_component_tbl => l_rev_component_tbl
3321 ,p_sub_component_tbl => l_sub_component_tbl
3322 ,p_ref_designator_tbl => l_ref_designator_tbl
3323 ,p_change_line_tbl => l_change_line_tbl
3324 ,p_rev_operation_tbl => l_rev_operation_tbl
3325 ,p_rev_op_resource_tbl => l_rev_op_resource_tbl
3326 ,p_rev_sub_resource_tbl => l_rev_sub_resource_tbl
3327 ,x_eco_rec => l_eco_rec
3328 ,x_eco_revision_tbl => l_eco_revision_tbl
3329 ,x_revised_item_tbl => l_revised_item_tbl
3330 ,x_rev_component_tbl => l_rev_component_tbl
3331 ,x_sub_component_tbl => l_sub_component_tbl
3332 ,x_ref_designator_tbl => l_ref_designator_tbl
3333 ,x_change_line_tbl => l_change_line_tbl
3334 ,x_rev_operation_tbl => l_rev_operation_tbl
3335 ,x_rev_op_resource_tbl => l_rev_op_resource_tbl
3336 ,x_rev_sub_resource_tbl => l_rev_sub_resource_tbl
3337 ,x_return_status => x_out.output_status.return_status
3338 ,x_msg_count => x_out.output_status.msg_count
3339 );
3340
3341 INV_EBI_UTIL.debug_line('STEP: 30 END CALLING ENG_ECO_PUB.process_eco');
3342 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
3343 RAISE FND_API.g_exc_unexpected_error;
3344 END IF;
3345
3346 SELECT change_id ,change_notice INTO x_out.change_id,x_out.change_notice
3347 FROM eng_engineering_changes
3348 WHERE change_notice = l_eco_rec.eco_name
3349 AND organization_id = l_organization_id;
3350
3351 x_out.organization_code := p_change_order.organization_code;
3352 x_out.organization_id := l_organization_id;
3353
3354 IF FND_API.to_boolean( p_commit ) THEN
3355 COMMIT;
3356 END IF;
3357 INV_EBI_UTIL.debug_line('STEP: 40 END INSIDE INV_EBI_CHANGE_ORDER_HELPER.process_eco');
3358 EXCEPTION
3359 WHEN FND_API.g_exc_unexpected_error THEN
3360 ROLLBACK TO inv_ebi_proc_eco_save_pnt;
3361 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
3362 IF(x_out.output_status.msg_data IS NULL) THEN
3363 fnd_msg_pub.count_and_get(
3364 p_encoded => FND_API.g_false
3365 ,p_count => x_out.output_status.msg_count
3366 ,p_data => x_out.output_status.msg_data
3367 );
3368 END IF;
3369 WHEN OTHERS THEN
3370 ROLLBACK TO inv_ebi_proc_eco_save_pnt;
3371 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
3372 IF (x_out.output_status.msg_data IS NOT NULL) THEN
3373 x_out.output_status.msg_data := x_out.output_status.msg_data||' -> INV_EBI_CHANGE_ORDER_HELPER.process_eco ';
3374 ELSE
3375 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_HELPER.process_eco ';
3376 END IF;
3377 END process_eco;
3378
3379 /************************************************************************************
3380 -- API name : process_common_comps
3381 -- Type : Private
3382 -- Function :
3383 -- This API is used to find the components and substitute comps attached to the
3384 -- common bom. Bug 7127027
3385 ************************************************************************************/
3386
3387 PROCEDURE process_common_comps(
3388 p_assembly_item_id IN NUMBER,
3389 p_organization_id IN NUMBER,
3390 p_src_organization_id IN NUMBER,
3391 p_src_bill_sequence_id IN NUMBER,
3392 x_return_status OUT NOCOPY VARCHAR2 ,
3393 x_msg_data OUT NOCOPY VARCHAR2,
3394 x_msg_count OUT NOCOPY NUMBER
3395 ) IS
3396
3397 l_bom_item_type NUMBER;
3398 l_base_item_flag NUMBER;
3399 l_replenish_to_order_flag VARCHAR2(1);
3400 l_pick_components_flag VARCHAR2(1);
3401 l_assm_type NUMBER;
3402 l_master_org NUMBER;
3403
3404 CURSOR c_common_comps (
3405 p_assy_type IN NUMBER,
3406 p_item_type IN NUMBER,
3407 p_base_item_flag IN NUMBER,
3408 p_pick_components_flag IN VARCHAR2,
3409 p_replenish_to_order_flag IN VARCHAR2
3410 ) IS
3411
3412 SELECT
3413 msi.concatenated_segments component_item_name ,
3414 bic.component_item_id
3415 FROM
3416 bom_inventory_components bic,
3417 mtl_system_items_kfv msi
3418 WHERE
3419 bic.bill_sequence_id = p_src_bill_sequence_id AND
3420 bic.component_item_id = msi.inventory_item_id AND
3421 msi.organization_id = p_src_organization_id AND
3422 NVL(bic.disable_date, sysdate + 1) >= sysdate AND
3423 NOT EXISTS
3424 ( SELECT 'x'
3425 FROM mtl_system_items_kfv s
3426 WHERE s.organization_id = p_organization_id
3427 AND s.inventory_item_id = bic.component_item_id
3428 AND ((p_assy_type = 1 AND s.eng_item_flag = 'N')
3429 OR (p_assy_type = 2)
3430 )
3431 AND s.inventory_item_id <> p_assembly_item_id
3432 AND ((p_item_type = 1 AND s.bom_item_type <> 3)
3433 OR (p_item_type = 2 AND s.bom_item_type <> 3)
3434 OR (p_item_type = 3)
3435 OR (p_item_type = 4
3436 AND (s.bom_item_type = 4
3437 OR(
3438 s.bom_item_type IN (2, 1)
3439 AND s.replenish_to_order_flag = 'Y'
3440 AND p_base_item_flag IS NOT NULL
3441 AND p_replenish_to_order_flag = 'Y'
3442 )
3443 )
3444 )
3445 )
3446 AND (p_item_type = 3
3447 OR
3448 p_pick_components_flag = 'Y'
3449 OR
3450 s.pick_components_flag = 'N'
3451 )
3452 AND (p_item_type = 3
3453 OR
3454 NVL(s.bom_item_type, 4) <> 2
3455 OR
3456 (s.bom_item_type = 2
3457 AND (( p_pick_components_flag = 'Y'
3458 AND s.pick_components_flag = 'Y'
3459 )
3460 OR ( p_replenish_to_order_flag = 'Y'
3461 AND s.replenish_to_order_flag = 'Y'
3462 )
3463 )
3464 )
3465 )
3466 AND NOT( p_item_type = 4
3467 AND p_pick_components_flag = 'Y'
3468 AND s.bom_item_type = 4
3469 AND s.replenish_to_order_flag = 'Y'
3470 )
3471 );
3472 l_common_comps c_common_comps%ROWTYPE;
3473
3474 CURSOR c_common_sub_comps(
3475 p_assy_type IN NUMBER,
3476 p_item_type IN NUMBER,
3477 p_base_item_flag IN NUMBER,
3478 p_pick_components_flag IN VARCHAR2,
3479 p_replenish_to_order_flag IN VARCHAR2
3480 ) IS
3481
3482 SELECT
3483 msi.concatenated_segments sub_comp_item_name ,
3484 bsc.substitute_component_id
3485 FROM bom_inventory_components bic,
3486 bom_substitute_components bsc,
3487 mtl_system_items_kfv msi
3488 WHERE bic.bill_sequence_id = p_src_bill_sequence_id
3489 AND bic.component_sequence_id = bsc.component_sequence_id
3490 AND bsc.substitute_component_id = msi.inventory_item_id
3491 AND msi.organization_id = p_src_organization_id
3492 AND bsc.substitute_component_id NOT IN
3493 (SELECT msi1.inventory_item_id
3494 FROM mtl_system_items msi1, mtl_system_items msi2
3495 WHERE msi1.organization_id = p_organization_id
3496 AND msi1.inventory_item_id = bsc.substitute_component_id
3497 AND msi2.organization_id = p_src_organization_id
3498 AND msi2.inventory_item_id = msi1.inventory_item_id
3499 AND ((p_assy_type = 1 AND msi1.eng_item_flag = 'N')
3500 OR (p_assy_type = 2)
3501 )
3502 AND msi1.inventory_item_id <> p_assembly_item_id
3503 AND ( (p_item_type = 1 AND msi1.bom_item_type <> 3)
3504 OR (p_item_type = 2 AND msi1.bom_item_type <> 3)
3505 OR (p_item_type = 3)
3506 OR ( p_item_type = 4
3507 AND ( msi1.bom_item_type = 4
3508 OR ( msi1.bom_item_type IN (2, 1)
3509 AND msi1.replenish_to_order_flag = 'Y'
3510 AND p_base_item_flag IS NOT NULL
3511 AND p_replenish_to_order_flag = 'Y'
3512 )
3513 )
3514 )
3515 )
3516 AND (p_item_type = 3
3517 OR
3518 p_pick_components_flag = 'Y'
3519 OR
3520 msi1.pick_components_flag = 'N'
3521 )
3522 AND (p_item_type = 3
3523 OR
3524 NVL(msi1.bom_item_type, 4) <> 2
3525 OR
3526 (msi1.bom_item_type = 2
3527 AND (( p_pick_components_flag = 'Y'
3528 AND msi1.pick_components_flag = 'Y'
3529 )
3530 OR ( p_replenish_to_order_flag = 'Y'
3531 AND msi1.replenish_to_order_flag = 'Y'
3532 )
3533 )
3534 )
3535 )
3536 AND NOT( p_item_type = 4
3537 AND p_pick_components_flag = 'Y'
3538 AND msi1.bom_item_type = 4
3539 AND msi1.replenish_to_order_flag = 'Y'
3540 )
3541 );
3542
3543 l_common_sub_comps c_common_sub_comps%ROWTYPE;
3544 BEGIN
3545 SAVEPOINT inv_ebi_comm_comp_save_pnt;
3546 x_return_status := FND_API.G_RET_STS_SUCCESS;
3547 INV_EBI_UTIL.debug_line('STEP: 10 START INSIDE INV_EBI_CHANGE_ORDER_HELPER.process_common_comps');
3548 SELECT
3549 bom_item_type,
3550 base_item_id,
3551 replenish_to_order_flag,
3552 pick_components_flag ,
3553 DECODE(eng_item_flag, 'Y', 2, 1)
3554 INTO
3555 l_bom_item_type,
3556 l_base_item_flag,
3557 l_replenish_to_order_flag,
3558 l_pick_components_flag,
3559 l_assm_type
3560 FROM
3561 mtl_system_items
3562 WHERE
3563 inventory_item_id = p_assembly_item_id AND
3564 organization_id = p_organization_id;
3565
3566 OPEN c_common_comps(
3567 p_assy_type => l_assm_type ,
3568 p_item_type => l_bom_item_type,
3569 p_base_item_flag => l_base_item_flag,
3570 p_pick_components_flag => l_pick_components_flag,
3571 p_replenish_to_order_flag => l_replenish_to_order_flag
3572 );
3573 LOOP
3574
3575 FETCH c_common_comps INTO l_common_comps;
3576 EXIT WHEN c_common_comps%NOTFOUND;
3577
3578 process_assign_items(
3579 p_organization_id => p_organization_id,
3580 p_item_name => l_common_comps.component_item_name,
3581 x_return_status => x_return_status ,
3582 x_msg_data => x_msg_data ,
3583 x_msg_count => x_msg_count
3584 );
3585
3586 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
3587 RAISE FND_API.g_exc_unexpected_error;
3588 END IF;
3589 END LOOP;
3590 CLOSE c_common_comps;
3591
3592 OPEN c_common_sub_comps(
3593 p_assy_type => l_assm_type ,
3594 p_item_type => l_bom_item_type,
3595 p_base_item_flag => l_base_item_flag,
3596 p_pick_components_flag => l_pick_components_flag,
3597 p_replenish_to_order_flag => l_replenish_to_order_flag
3598 );
3599 LOOP
3600
3601 FETCH c_common_sub_comps INTO l_common_sub_comps;
3602 EXIT WHEN c_common_sub_comps%NOTFOUND;
3603
3604 process_assign_items(
3605 p_organization_id => p_organization_id,
3606 p_item_name => l_common_sub_comps.sub_comp_item_name,
3607 x_return_status => x_return_status ,
3608 x_msg_data => x_msg_data ,
3609 x_msg_count => x_msg_count
3610 );
3611
3612 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
3613 RAISE FND_API.g_exc_unexpected_error;
3614 END IF;
3615 END LOOP;
3616 CLOSE c_common_sub_comps ;
3617 INV_EBI_UTIL.debug_line('STEP: 20 END INSIDE INV_EBI_CHANGE_ORDER_HELPER.process_common_comps');
3618 EXCEPTION
3619 WHEN FND_API.g_exc_unexpected_error THEN
3620 ROLLBACK TO inv_ebi_comm_comp_save_pnt;
3621 IF (c_common_comps%ISOPEN) THEN
3622 CLOSE c_common_comps;
3623 END IF;
3624 IF (c_common_sub_comps%ISOPEN) THEN
3625 CLOSE c_common_sub_comps;
3626 END IF;
3627 x_return_status := FND_API.g_ret_sts_error;
3628 IF(x_msg_data IS NULL) THEN
3629 fnd_msg_pub.count_and_get(
3630 p_encoded => FND_API.g_false
3631 ,p_count => x_msg_count
3632 ,p_data => x_msg_data
3633 );
3634 END IF;
3635 WHEN OTHERS THEN
3636 ROLLBACK TO inv_ebi_comm_comp_save_pnt;
3637 IF (c_common_comps%ISOPEN) THEN
3638 CLOSE c_common_comps;
3639 END IF;
3640 IF (c_common_sub_comps%ISOPEN) THEN
3641 CLOSE c_common_sub_comps;
3642 END IF;
3643 x_return_status := FND_API.g_ret_sts_unexp_error;
3644 IF (x_msg_data IS NOT NULL) THEN
3645 x_msg_data := x_msg_data||' -> INV_EBI_CHANGE_ORDER_HELPER.process_common_comps ';
3646 ELSE
3647 x_msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_HELPER.process_common_comps ';
3648 END IF;
3649 END process_common_comps;
3650
3651
3652 /************************************************************************************
3653 -- API name : process_common_bom
3654 -- Type : Private
3655 -- Function :
3656 -- This API is used to determine the coomon bill sequence id and detremine the
3657 -- Common comps and common susbstitute comps.
3658 -- Bug 7127027
3659 ************************************************************************************/
3660
3661 PROCEDURE process_common_bom(
3662 p_organization_code IN VARCHAR2
3663 ,p_assembly_item_name IN VARCHAR2
3664 ,p_alternate_bom_code IN VARCHAR2
3665 ,p_common_assembly_item_name IN VARCHAR2
3666 ,p_common_organization_code IN VARCHAR2
3667 ,x_return_status OUT NOCOPY VARCHAR2
3668 ,x_msg_data OUT NOCOPY VARCHAR2
3669 ,x_msg_count OUT NOCOPY NUMBER
3670 )
3671 IS
3672 l_src_bill_sequence_id NUMBER;
3673 l_src_assembly_item_id NUMBER;
3674 l_src_organization_id NUMBER;
3675 l_assembly_item_id NUMBER;
3676 l_organization_id NUMBER;
3677
3678
3679 BEGIN
3680 SAVEPOINT inv_ebi_comm_bom_save_pnt;
3681 x_return_status := FND_API.G_RET_STS_SUCCESS;
3682 INV_EBI_UTIL.debug_line('STEP: 10 START INSIDE INV_EBI_CHANGE_ORDER_HELPER.process_common_bom');
3683 l_src_organization_id := INV_EBI_ITEM_HELPER.get_organization_id(
3684 p_organization_code => p_common_organization_code
3685 );
3686 l_src_assembly_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id (
3687 p_organization_id => l_src_organization_id
3688 ,p_item_number => p_common_assembly_item_name
3689 );
3690 l_src_bill_sequence_id := get_bill_sequence_id(
3691 p_assembly_item_id => l_src_assembly_item_id,
3692 p_organization_id => l_src_organization_id,
3693 p_alternate_bom_code => p_alternate_bom_code
3694 );
3695 IF(l_src_bill_sequence_id IS NULL ) THEN
3696 FND_MESSAGE.set_name('INV','INV_EBI_COMMON_BILL_NOT_FOUND');
3697 FND_MESSAGE.set_token('ASSY_ITEM', p_common_assembly_item_name);
3698 FND_MSG_PUB.add;
3699 RAISE FND_API.g_exc_error;
3700 END IF;
3701
3702 l_organization_id := INV_EBI_ITEM_HELPER.get_organization_id(
3703 p_organization_code => p_organization_code
3704 );
3705
3706 l_assembly_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id (
3707 p_organization_id => l_organization_id
3708 ,p_item_number => p_assembly_item_name
3709 );
3710
3711 process_common_comps(
3712 p_assembly_item_id => l_assembly_item_id,
3713 p_organization_id => l_organization_id,
3714 p_src_organization_id => l_src_organization_id,
3715 p_src_bill_sequence_id => l_src_bill_sequence_id,
3716 x_return_status => x_return_status,
3717 x_msg_data => x_msg_data,
3718 x_msg_count => x_msg_count
3719 );
3720
3721 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
3722 RAISE FND_API.g_exc_unexpected_error;
3723 END IF;
3724 INV_EBI_UTIL.debug_line('STEP: 20 END INSIDE INV_EBI_CHANGE_ORDER_HELPER.process_common_bom');
3725 EXCEPTION
3726 WHEN FND_API.g_exc_unexpected_error THEN
3727 ROLLBACK TO inv_ebi_comm_bom_save_pnt;
3728 x_return_status := FND_API.g_ret_sts_unexp_error;
3729 IF(x_msg_data IS NULL) THEN
3730 fnd_msg_pub.count_and_get(
3731 p_encoded => FND_API.g_false
3732 ,p_count => x_msg_count
3733 ,p_data => x_msg_data
3734 );
3735 END IF;
3736 WHEN FND_API.g_exc_error THEN
3737 ROLLBACK TO inv_ebi_comm_bom_save_pnt;
3738 x_return_status := FND_API.g_ret_sts_error;
3739 IF(x_msg_data IS NULL) THEN
3740 FND_MSG_PUB.count_and_get(
3741 p_encoded => FND_API.g_false
3742 ,p_count => x_msg_count
3743 ,p_data => x_msg_data
3744 );
3745 END IF;
3746 WHEN OTHERS THEN
3747 ROLLBACK TO inv_ebi_comm_bom_save_pnt;
3748 x_return_status := FND_API.g_ret_sts_unexp_error;
3749 IF (x_msg_data IS NOT NULL) THEN
3750 x_msg_data := x_msg_data ||' -> INV_EBI_CHANGE_ORDER_HELPER.process_common_bom ';
3751 ELSE
3752 x_msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_HELPER.process_common_bom ';
3753 END IF;
3754 END process_common_bom;
3755
3756 /************************************************************************************
3757 -- API name : get_common_bom_orgs
3758 -- Type : Private
3759 -- Function :
3760 -- This API is used to get orgs list to which a bom is commoned
3761 -- Bug 7196996
3762 ************************************************************************************/
3763 PROCEDURE get_common_bom_orgs(
3764 p_assembly_item_name IN VARCHAR2,
3765 p_organization_code IN VARCHAR2,
3766 p_alternate_bom_code IN VARCHAR2,
3767 x_common_orgs OUT NOCOPY inv_ebi_org_tbl,
3768 x_return_status OUT NOCOPY VARCHAR2,
3769 x_msg_data OUT NOCOPY VARCHAR2,
3770 x_msg_count OUT NOCOPY NUMBER
3771 ) IS
3772 l_bill_sequence_id NUMBER;
3773 l_organization_id NUMBER;
3774 l_assembly_item_id NUMBER;
3775 l_org_count NUMBER := 0;
3776
3777 CURSOR c_common_orgs(
3778 p_bill_sequence_id IN NUMBER,
3779 p_common_assy_item_id IN NUMBER,
3780 p_common_org_id IN NUMBER
3781 ) IS
3782
3783 SELECT organization_id
3784 FROM bom_bill_of_materials
3785 WHERE
3786 common_bill_sequence_id = p_bill_sequence_id AND
3787 common_assembly_item_id = p_common_assy_item_id AND
3788 common_organization_id = p_common_org_id;
3789
3790 BEGIN
3791 x_return_status := FND_API.G_RET_STS_SUCCESS;
3792 l_organization_id := INV_EBI_ITEM_HELPER.get_organization_id(
3793 p_organization_code => p_organization_code
3794 );
3795
3796 l_assembly_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id (
3797 p_organization_id => l_organization_id
3798 ,p_item_number => p_assembly_item_name);
3799
3800 l_bill_sequence_id := get_bill_sequence_id(
3801 p_assembly_item_id => l_assembly_item_id,
3802 p_organization_id => l_organization_id,
3803 p_alternate_bom_code => p_alternate_bom_code
3804 );
3805 x_common_orgs := inv_ebi_org_tbl();
3806 FOR l_common_orgs IN c_common_orgs(
3807 p_bill_sequence_id => l_bill_sequence_id,
3808 p_common_assy_item_id => l_assembly_item_id,
3809 p_common_org_id => l_organization_id
3810 )
3811 LOOP
3812 IF(l_common_orgs.organization_id <> l_organization_id) THEN
3813 l_org_count := l_org_count + 1;
3814 x_common_orgs.extend();
3815 x_common_orgs(l_org_count) := inv_ebi_org_obj(NULL,NULL);
3816 x_common_orgs(l_org_count).org_id := l_common_orgs.organization_id;
3817 END IF;
3818 END LOOP;
3819
3820 EXCEPTION
3821 WHEN OTHERS THEN
3822 x_return_status := FND_API.g_ret_sts_unexp_error;
3823 IF (x_msg_data IS NOT NULL) THEN
3824 x_msg_data := x_msg_data ||' -> INV_EBI_CHANGE_ORDER_HELPER.get_common_bom_orgs ';
3825 ELSE
3826 x_msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_HELPER.get_common_bom_orgs ';
3827 END IF;
3828 END get_common_bom_orgs;
3829
3830 /************************************************************************************
3831 -- API name : process_common_bom_orgs
3832 -- Type : Private
3833 -- Function :
3834 -- This API is used to Process bom and common bom.
3835 -- Bug 7196996
3836 ************************************************************************************/
3837 PROCEDURE process_common_bom_orgs(
3838 p_assembly_item_name IN VARCHAR2,
3839 p_organization_code IN VARCHAR2,
3840 p_alternate_bom_code IN VARCHAR2,
3841 p_component_item_tbl IN inv_ebi_rev_comp_tbl,
3842 x_out OUT NOCOPY inv_ebi_eco_output_obj
3843 ) IS
3844 l_common_orgs inv_ebi_org_tbl;
3845 l_output_status inv_ebi_output_status;
3846 l_count NUMBER;
3847 BEGIN
3848 SAVEPOINT inv_ebi_common_orgs_save_pnt;
3849 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
3850 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
3851 l_common_orgs := inv_ebi_org_tbl();
3852
3853 --Get the list of orgs to which particular bom is commoned
3854 get_common_bom_orgs(
3855 p_assembly_item_name => p_assembly_item_name,
3856 p_organization_code => p_organization_code,
3857 p_alternate_bom_code => p_alternate_bom_code,
3858 x_common_orgs => l_common_orgs,
3859 x_return_status => x_out.output_status.return_status ,
3860 x_msg_data => x_out.output_status.msg_data ,
3861 x_msg_count => x_out.output_status.msg_count
3862 ) ;
3863 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
3864 RAISE FND_API.g_exc_unexpected_error;
3865 END IF;
3866
3867 FOR i IN 1..l_common_orgs.COUNT LOOP
3868 IF(p_component_item_tbl IS NOT NULL AND p_component_item_tbl.COUNT > 0) THEN
3869 FOR j IN 1..p_component_item_tbl.COUNT LOOP
3870 IF( p_component_item_tbl(j).acd_type = 1 ) THEN
3871
3872 /*After commoning a bom to other orgs if components or sub com are added
3873 to source ensure that those comps and sub comps exists in all the commoned orgs */
3874
3875 process_assign_items(
3876 p_organization_id => l_common_orgs(i).org_id,
3877 p_item_name => p_component_item_tbl(j).component_item_name,
3878 x_return_status => x_out.output_status.return_status ,
3879 x_msg_data => x_out.output_status.msg_data ,
3880 x_msg_count => x_out.output_status.msg_count
3881 );
3882 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
3883 RAISE FND_API.g_exc_unexpected_error;
3884 END IF;
3885 END IF;
3886
3887 IF( p_component_item_tbl(j).substitute_component_tbl IS NOT NULL AND
3888 p_component_item_tbl(j).substitute_component_tbl .COUNT > 0) THEN
3889 FOR k IN 1..p_component_item_tbl(j).substitute_component_tbl.COUNT LOOP
3890
3891 IF(p_component_item_tbl(j).substitute_component_tbl(k).acd_type = 1) THEN
3892 process_assign_items(
3893 p_organization_id => l_common_orgs(i).org_id,
3894 p_item_name => p_component_item_tbl(j).substitute_component_tbl(k).substitute_component_name,
3895 x_return_status => x_out.output_status.return_status ,
3896 x_msg_data => x_out.output_status.msg_data ,
3897 x_msg_count => x_out.output_status.msg_count
3898 );
3899 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
3900 RAISE FND_API.g_exc_unexpected_error;
3901 END IF;
3902 END IF;
3903 END LOOP;
3904 END IF;
3905 END LOOP;
3906 END IF;
3907 END LOOP;
3908
3909 EXCEPTION
3910 WHEN FND_API.g_exc_unexpected_error THEN
3911 ROLLBACK TO inv_ebi_common_orgs_save_pnt;
3912 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
3913 IF(x_out.output_status.msg_data IS NULL) THEN
3914 fnd_msg_pub.count_and_get(
3915 p_encoded => FND_API.g_false
3916 ,p_count => x_out.output_status.msg_count
3917 ,p_data => x_out.output_status.msg_data
3918 );
3919 END IF;
3920 WHEN OTHERS THEN
3921 ROLLBACK TO inv_ebi_common_orgs_save_pnt;
3922 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
3923 IF (x_out.output_status.msg_data IS NOT NULL) THEN
3924 x_out.output_status.msg_data := x_out.output_status.msg_data||' -> INV_EBI_CHANGE_ORDER_HELPER.process_common_bom_orgs ';
3925 ELSE
3926 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_HELPER.process_common_bom_orgs ';
3927 END IF;
3928 END process_common_bom_orgs;
3929
3930
3931 /************************************************************************************
3932 -- API name : process_structure_header
3933 -- Type : Private
3934 -- Function :
3935 -- This API is used to Process bom and common bom.
3936 --
3937 ************************************************************************************/
3938 PROCEDURE process_structure_header(
3939 p_commit IN VARCHAR2 := FND_API.G_FALSE
3940 ,p_organization_code IN VARCHAR2
3941 ,p_assembly_item_name IN VARCHAR2
3942 ,p_alternate_bom_code IN VARCHAR2
3943 ,p_structure_header IN inv_ebi_structure_header_obj
3944 ,p_component_item_tbl IN inv_ebi_rev_comp_tbl
3945 ,p_name_val_list IN inv_ebi_name_value_list
3946 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
3947 )
3948 IS
3949 l_bom_header_rec bom_bo_pub.bom_head_rec_type;
3950 l_bom_revision_tbl bom_bo_pub.bom_revision_tbl_type;
3951 l_bom_component_tbl bom_bo_pub.bom_comps_tbl_type;
3952 l_bom_ref_designator_tbl bom_bo_pub.bom_ref_designator_tbl_type;
3953 l_bom_sub_component_tbl bom_bo_pub.bom_sub_component_tbl_type;
3954 l_output_status inv_ebi_output_status;
3955 l_is_bom_exists VARCHAR2(3);
3956 l_transaction_type VARCHAR2(20);
3957
3958 BEGIN
3959 SAVEPOINT inv_ebi_proc_bom_save_pnt;
3960
3961 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
3962 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
3963 INV_EBI_UTIL.debug_line('STEP: 10 START INSIDE INV_EBI_CHANGE_ORDER_HELPER.process_structure_header');
3964
3965 l_is_bom_exists := is_bom_exists(
3966 p_item_number => p_assembly_item_name,
3967 p_organization_code => p_organization_code,
3968 p_alternate_bom_code => p_alternate_bom_code
3969 );
3970
3971 IF(l_is_bom_exists = fnd_api.g_false ) THEN
3972 l_transaction_type := INV_EBI_ITEM_PUB.g_otype_create ;
3973 ELSE
3974 l_transaction_type := INV_EBI_ITEM_PUB.g_otype_update;
3975
3976 --Bug 7196996
3977 process_common_bom_orgs(
3978 p_assembly_item_name => p_assembly_item_name,
3979 p_organization_code => p_organization_code,
3980 p_alternate_bom_code => p_alternate_bom_code,
3981 p_component_item_tbl => p_component_item_tbl,
3982 x_out => x_out
3983 );
3984 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
3985 RAISE FND_API.g_exc_unexpected_error;
3986 END IF;
3987 END IF;
3988
3989 IF(p_structure_header.common_assembly_item_name IS NOT NULL AND
3990 p_structure_header.common_assembly_item_name <> fnd_api.g_miss_char AND
3991 p_structure_header.common_organization_code IS NOT NULL AND
3992 p_structure_header.common_organization_code <> fnd_api.g_miss_char
3993 ) THEN
3994 --Bug 7127027
3995 process_common_bom(
3996 p_organization_code => p_organization_code
3997 ,p_assembly_item_name => p_assembly_item_name
3998 ,p_alternate_bom_code => p_alternate_bom_code
3999 ,p_common_assembly_item_name => p_structure_header.common_assembly_item_name
4000 ,p_common_organization_code => p_structure_header.common_organization_code
4001 ,x_return_status => x_out.output_status.return_status
4002 ,x_msg_data => x_out.output_status.msg_data
4003 ,x_msg_count => x_out.output_status.msg_count
4004 );
4005 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
4006 RAISE FND_API.g_exc_unexpected_error;
4007 END IF;
4008 END IF;
4009
4010 l_bom_header_rec.assembly_item_name := p_assembly_item_name ;
4011 l_bom_header_rec.organization_code := p_organization_code;
4012 l_bom_header_rec.alternate_bom_code := p_alternate_bom_code ;
4013 l_bom_header_rec.common_assembly_item_name := p_structure_header.common_assembly_item_name ;
4014 l_bom_header_rec.common_organization_code := p_structure_header.common_organization_code ;
4015 l_bom_header_rec.assembly_comment := p_structure_header.assembly_comment;
4016 l_bom_header_rec.assembly_type := p_structure_header.assembly_type ;
4017 l_bom_header_rec.transaction_type := l_transaction_type ;
4018 l_bom_header_rec.return_status := p_structure_header.return_status ;
4019 l_bom_header_rec.attribute_category := p_structure_header.attribute_category ;
4020 l_bom_header_rec.attribute1 := p_structure_header.attribute1 ;
4021 l_bom_header_rec.attribute2 := p_structure_header.attribute2 ;
4022 l_bom_header_rec.attribute3 := p_structure_header.attribute3 ;
4023 l_bom_header_rec.attribute4 := p_structure_header.attribute4 ;
4024 l_bom_header_rec.attribute5 := p_structure_header.attribute5 ;
4025 l_bom_header_rec.attribute6 := p_structure_header.attribute6 ;
4026 l_bom_header_rec.attribute7 := p_structure_header.attribute7 ;
4027 l_bom_header_rec.attribute8 := p_structure_header.attribute8 ;
4028 l_bom_header_rec.attribute9 := p_structure_header.attribute9 ;
4029 l_bom_header_rec.attribute10 := p_structure_header.attribute10;
4030 l_bom_header_rec.attribute11 := p_structure_header.attribute11;
4031 l_bom_header_rec.attribute12 := p_structure_header.attribute12;
4032 l_bom_header_rec.attribute13 := p_structure_header.attribute13;
4033 l_bom_header_rec.attribute14 := p_structure_header.attribute14;
4034 l_bom_header_rec.attribute15 := p_structure_header.attribute15;
4035 l_bom_header_rec.original_system_reference := p_structure_header.original_system_reference;
4036 l_bom_header_rec.delete_group_name := p_structure_header.delete_group_name ;
4037 l_bom_header_rec.dg_description := p_structure_header.dg_description ;
4038 l_bom_header_rec.row_identifier := p_structure_header.row_identifier ;
4039 l_bom_header_rec.bom_implementation_date := p_structure_header.bom_implementation_date ;
4040 l_bom_header_rec.enable_attrs_update := p_structure_header.enable_attrs_update ;
4041 l_bom_header_rec.structure_type_name := p_structure_header.structure_type_name ;
4042
4043 IF(l_bom_header_rec.structure_type_name IS NULL OR
4044 l_bom_header_rec.structure_type_name = fnd_api.g_miss_char )
4045 AND (INV_EBI_UTIL.is_pim_installed) THEN
4046 IF p_name_val_list.name_value_table IS NOT NULL THEN
4047 FOR i in p_name_val_list.name_value_table.FIRST..p_name_val_list.name_value_table.LAST LOOP
4048 IF UPPER(p_name_val_list.name_value_table(i).param_name) = G_DEFAULT_STRUCTURE_TYPE THEN
4049 l_bom_header_rec.structure_type_name := p_name_val_list.name_value_table(i).param_value;
4050 END IF;
4051 END LOOP;
4052 END IF;
4053 END IF;
4054 INV_EBI_UTIL.debug_line('STEP: 20 START CALLING BOM_BO_PUB.process_bom');
4055
4056 BOM_BO_PUB.process_bom
4057 (
4058 p_bom_header_rec => l_bom_header_rec
4059 ,p_bom_revision_tbl => l_bom_revision_tbl
4060 ,p_bom_component_tbl => l_bom_component_tbl
4061 ,p_bom_ref_designator_tbl => l_bom_ref_designator_tbl
4062 ,p_bom_sub_component_tbl => l_bom_sub_component_tbl
4063 ,x_bom_header_rec => l_bom_header_rec
4064 ,x_bom_revision_tbl => l_bom_revision_tbl
4065 ,x_bom_component_tbl => l_bom_component_tbl
4066 ,x_bom_ref_designator_tbl => l_bom_ref_designator_tbl
4067 ,x_bom_sub_component_tbl => l_bom_sub_component_tbl
4068 ,x_return_status => x_out.output_status.return_status
4069 ,x_msg_count => x_out.output_status.msg_count
4070 );
4071 INV_EBI_UTIL.debug_line('STEP: 30 END CALLING BOM_BO_PUB.process_bom');
4072 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
4073 RAISE FND_API.g_exc_unexpected_error;
4074 END IF;
4075 IF FND_API.to_boolean( p_commit ) THEN
4076 COMMIT;
4077 END IF;
4078 INV_EBI_UTIL.debug_line('STEP: 40 END INSIDE INV_EBI_CHANGE_ORDER_HELPER.process_structure_header');
4079 EXCEPTION
4080 WHEN FND_API.g_exc_unexpected_error THEN
4081 ROLLBACK TO inv_ebi_proc_bom_save_pnt;
4082 x_out.output_status.return_status := FND_API.g_ret_sts_error;
4083 IF(x_out.output_status.msg_data IS NULL) THEN
4084 fnd_msg_pub.count_and_get(
4085 p_encoded => FND_API.g_false
4086 ,p_count => x_out.output_status.msg_count
4087 ,p_data => x_out.output_status.msg_data
4088 );
4089 END IF;
4090 WHEN OTHERS THEN
4091 ROLLBACK TO inv_ebi_proc_bom_save_pnt;
4092 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
4093 IF (x_out.output_status.msg_data IS NOT NULL) THEN
4094 x_out.output_status.msg_data := x_out.output_status.msg_data||' -> INV_EBI_CHANGE_ORDER_HELPER.process_structure_header ';
4095 ELSE
4096 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_HELPER.process_structure_header ';
4097 END IF;
4098 END process_structure_header;
4099
4100 /************************************************************************************
4101 -- API name : get_eco
4102 -- Type : Public
4103 -- Function :
4104 -- This API is used to retrieve all the change order attributes
4105 ************************************************************************************/
4106 PROCEDURE get_eco (
4107 p_change_id IN NUMBER
4108 ,p_last_update_status IN VARCHAR2
4109 ,p_revised_item_sequence_id IN NUMBER
4110 ,p_name_val_list IN inv_ebi_name_value_list
4111 ,x_eco_obj OUT NOCOPY inv_ebi_eco_obj
4112 ,x_return_status OUT NOCOPY VARCHAR2
4113 ,x_msg_count OUT NOCOPY NUMBER
4114 ,x_msg_data OUT NOCOPY VARCHAR2
4115 )
4116 IS
4117 l_eco_change_order_obj inv_ebi_change_order_obj;
4118 l_eco_revision_tbl inv_ebi_eco_revision_tbl;
4119 l_eco_revision_obj inv_ebi_eco_revision_obj;
4120 l_substitute_component_tbl inv_ebi_sub_comp_tbl;
4121 l_substitute_component_obj inv_ebi_sub_comp_obj;
4122 l_reference_designator_tbl inv_ebi_ref_desg_tbl;
4123 l_reference_designator_obj inv_ebi_ref_desg_obj;
4124 l_revision_component_tbl inv_ebi_rev_comp_tbl;
4125 l_revision_component_obj inv_ebi_rev_comp_obj;
4126 l_revised_item_tbl inv_ebi_revised_item_tbl;
4127 l_revised_item_obj inv_ebi_revised_item_obj;
4128 l_structure_header_obj inv_ebi_structure_header_obj;
4129 l_current_index NUMBER;
4130 l_reviseditem_index NUMBER;
4131 l_comp_index NUMBER;
4132 l_sub_index NUMBER;
4133 l_ref_index NUMBER;
4134 l_change_id NUMBER := NULL;
4135 l_revised_item_sequence_id NUMBER := NULL;
4136 l_include_rev_items VARCHAR2(1):= fnd_api.g_true;
4137 l_include_comp_items VARCHAR2(1):= fnd_api.g_true;
4138 l_include_sub_comp VARCHAR2(1):= fnd_api.g_true;
4139 l_include_ref_designators VARCHAR2(1):= fnd_api.g_true;
4140 l_item_obj inv_ebi_item_obj;
4141
4142 CURSOR c_change_order_type(p_change_id IN VARCHAR2) IS
4143 SELECT
4144 eec.change_notice
4145 ,eec.change_order_type_id
4146 ,eec.change_notice_prefix
4147 ,eec.change_notice_number
4148 ,mp.organization_code
4149 ,mp.organization_id
4150 ,eec.change_name
4151 ,eec.description
4152 ,eec.cancellation_comments
4153 ,eec.status_code
4154 ,ecsv.status_name
4155 ,eec.priority_code
4156 ,eec.reason_code
4157 ,eec.estimated_eng_cost
4158 ,eec.estimated_mfg_cost
4159 ,eec.attribute_category
4160 ,eec.attribute1
4161 ,eec.attribute2
4162 ,eec.attribute3
4163 ,eec.attribute4
4164 ,eec.attribute5
4165 ,eec.attribute6
4166 ,eec.attribute7
4167 ,eec.attribute8
4168 ,eec.attribute9
4169 ,eec.attribute10
4170 ,eec.attribute11
4171 ,eec.attribute12
4172 ,eec.attribute13
4173 ,eec.attribute14
4174 ,eec.attribute15
4175 ,eec.ddf_context
4176 ,eeal.approval_list_name
4177 ,eec.approval_date
4178 ,eec.approval_request_date
4179 ,eec.change_mgmt_type_code
4180 ,eec.original_system_reference
4181 ,eec.organization_hierarchy
4182 ,hp.party_name assignee
4183 ,ppa.name project_name
4184 ,ppe.name task_number
4185 ,eec.source_type_code
4186 ,eec.source_name
4187 ,eec.need_by_date
4188 ,eec.effort
4189 ,haou.name eco_department_name
4190 ,eec.internal_use_only
4191 ,eec.plm_or_erp_change
4192 ,eec.status_type
4193 ,eec.implementation_date
4194 ,eec.cancellation_date
4195 ,ecot.type_name
4196 FROM
4197 eng_engineering_changes eec
4198 ,mtl_parameters mp
4199 ,eng_change_statuses_vl ecsv
4200 ,eng_ecn_approval_lists eeal
4201 ,hz_parties hp
4202 ,pa_projects_all ppa
4203 ,pa_proj_elements ppe
4204 ,hr_all_organization_units haou
4205 ,eng_change_order_types_vl ecot
4206 WHERE
4207 eec.change_id = p_change_id AND
4208 eec.change_order_type_id =ecot.change_order_type_id AND
4209 mp.organization_id = eec.organization_id AND
4210 ecsv.status_code(+) = eec.status_code AND
4211 eeal.approval_list_id(+) = eec.approval_list_id AND
4212 hp.party_id(+) = eec.assignee_id AND
4213 ppa.project_id(+) = eec.project_id AND
4214 ppe.proj_element_id(+) = eec.task_id AND
4215 haou.organization_id(+) = eec.responsible_organization_id;
4216
4217 c_eco_header c_change_order_type%ROWTYPE;
4218
4219 CURSOR c_eco_revision(p_change_id IN VARCHAR2) IS
4220 SELECT
4221 ecor.revision
4222 ,ecor.comments
4223 ,ecor.attribute_category
4224 ,ecor.attribute1
4225 ,ecor.attribute2
4226 ,ecor.attribute3
4227 ,ecor.attribute4
4228 ,ecor.attribute5
4229 ,ecor.attribute6
4230 ,ecor.attribute7
4231 ,ecor.attribute8
4232 ,ecor.attribute9
4233 ,ecor.attribute10
4234 ,ecor.attribute11
4235 ,ecor.attribute12
4236 ,ecor.attribute13
4237 ,ecor.attribute14
4238 ,ecor.attribute15
4239 ,eec.change_mgmt_type_code
4240 ,ecor.original_system_reference
4241 FROM
4242 eng_change_order_revisions ecor
4243 ,eng_engineering_changes eec
4244 WHERE
4245 ecor.change_id = eec.change_id AND
4246 ecor.change_id = p_change_id;
4247
4248 CURSOR c_substitute_component(p_change_id IN VARCHAR2,p_revised_item_id IN NUMBER,p_component_id IN NUMBER) IS
4249 SELECT
4250 mif.item_number substitute_component_name
4251 ,bsc.acd_type
4252 ,bsc.substitute_item_quantity
4253 ,bsc.attribute_category
4254 ,bsc.attribute1
4255 ,bsc.attribute2
4256 ,bsc.attribute3
4257 ,bsc.attribute4
4258 ,bsc.attribute5
4259 ,bsc.attribute6
4260 ,bsc.attribute7
4261 ,bsc.attribute8
4262 ,bsc.attribute9
4263 ,bsc.attribute10
4264 ,bsc.attribute11
4265 ,bsc.attribute12
4266 ,bsc.attribute13
4267 ,bsc.attribute14
4268 ,bsc.attribute15
4269 ,bsc.original_system_reference
4270 ,bsc.enforce_int_requirements
4271 ,bsc.program_id
4272 FROM
4273 bom_inventory_components bic
4274 ,eng_revised_items eri
4275 ,mtl_item_flexfields mif
4276 ,bom_substitute_components bsc
4277 WHERE
4278 eri.change_id = p_change_id AND
4279 eri.revised_item_id = p_revised_item_id AND
4280 bic.component_item_id = p_component_id AND
4281 bic.revised_item_sequence_id(+) = eri.revised_item_sequence_id AND
4282 bsc.component_sequence_id = bic.component_sequence_id AND
4283 mif.inventory_item_id = bsc.substitute_component_id AND
4284 mif.organization_id = eri.organization_id;
4285
4286 CURSOR c_reference_designator(p_change_id IN VARCHAR2,p_revised_item_id IN NUMBER,p_component_id IN NUMBER) IS
4287 SELECT
4288 brd.component_reference_designator reference_designator_name
4289 ,brd.acd_type
4290 ,brd.ref_designator_comment
4291 ,brd.attribute_category
4292 ,brd.attribute1
4293 ,brd.attribute2
4294 ,brd.attribute3
4295 ,brd.attribute4
4296 ,brd.attribute5
4297 ,brd.attribute6
4298 ,brd.attribute7
4299 ,brd.attribute8
4300 ,brd.attribute9
4301 ,brd.attribute10
4302 ,brd.attribute11
4303 ,brd.attribute12
4304 ,brd.attribute13
4305 ,brd.attribute14
4306 ,brd.attribute15
4307 ,brd.original_system_reference
4308 FROM
4309 bom_inventory_components bic
4310 ,eng_revised_items eri
4311 ,bom_reference_designators brd
4312 WHERE
4313 eri.change_id = p_change_id AND
4314 eri.revised_item_id = p_revised_item_id AND
4315 bic.component_item_id = p_component_id AND
4316 bic.revised_item_sequence_id(+)=eri.revised_item_sequence_id AND
4317 brd.component_sequence_id(+)=bic.component_sequence_id;
4318
4319 CURSOR c_revision_component(p_change_id IN VARCHAR2,p_revised_item_id IN NUMBER) IS
4320 SELECT
4321 bic.component_item_id
4322 ,bic.disable_date
4323 ,bic.operation_seq_num
4324 ,mif.item_number component_item_name
4325 ,bic.acd_type
4326 ,bic.basis_type
4327 ,bic.component_quantity
4328 ,bic.component_quantity inverse_quantity
4329 ,bic.include_in_cost_rollup
4330 ,bic.wip_supply_type
4331 ,bic.so_basis
4332 ,bic.optional
4333 ,bic.mutually_exclusive_options
4334 ,bic.check_atp
4335 ,bic.shipping_allowed
4336 ,bic.required_to_ship
4337 ,bic.required_for_revenue
4338 ,bic.include_on_ship_docs
4339 ,bic.quantity_related
4340 ,bic.supply_subinventory
4341 ,bic.attribute_category
4342 ,bic.attribute1
4343 ,bic.attribute2
4344 ,bic.attribute3
4345 ,bic.attribute4
4346 ,bic.attribute5
4347 ,bic.attribute6
4348 ,bic.attribute7
4349 ,bic.attribute8
4350 ,bic.attribute9
4351 ,bic.attribute10
4352 ,bic.attribute11
4353 ,bic.attribute12
4354 ,bic.attribute13
4355 ,bic.attribute14
4356 ,bic.attribute15
4357 ,bic.from_end_item_unit_number
4358 ,bic.to_end_item_unit_number
4359 ,bic.enforce_int_requirements
4360 ,bic.auto_request_material
4361 ,bic.suggested_vendor_name
4362 ,bic.unit_price
4363 ,bic.original_system_reference
4364 FROM
4365 bom_inventory_components bic
4366 ,eng_revised_items eri
4367 ,mtl_item_flexfields mif
4368 WHERE
4369 eri.change_id = p_change_id AND
4370 eri.revised_item_id = p_revised_item_id AND
4371 bic.revised_item_sequence_id(+)=eri.revised_item_sequence_id AND
4372 mif.inventory_item_id=bic.component_item_id AND
4373 mif.organization_id=eri.organization_id;
4374
4375 CURSOR c_revised_item(p_change_id IN VARCHAR2) IS
4376 SELECT
4377 eri.revised_item_id
4378 ,mif.item_number revised_item_name
4379 ,eri.new_item_revision
4380 ,eri.alternate_bom_designator
4381 ,eri.status_code
4382 ,eri.status_type
4383 ,eri.mrp_active
4384 ,mif1.item_number use_up_item_name
4385 ,eri.use_up_plan_name
4386 ,eri.disposition_type
4387 ,eri.update_wip
4388 ,eri.cancel_comments
4389 ,eri.attribute_category
4390 ,eri.attribute1
4391 ,eri.attribute2
4392 ,eri.attribute3
4393 ,eri.attribute4
4394 ,eri.attribute5
4395 ,eri.attribute6
4396 ,eri.attribute7
4397 ,eri.attribute8
4398 ,eri.attribute9
4399 ,eri.attribute10
4400 ,eri.attribute11
4401 ,eri.attribute12
4402 ,eri.attribute13
4403 ,eri.attribute14
4404 ,eri.attribute15
4405 ,eri.scheduled_date
4406 ,eri.from_end_item_unit_number
4407 ,eri.original_system_reference
4408 ,eri.from_cum_qty
4409 ,eri.lot_number
4410 ,eri.completion_subinventory
4411 ,eri.priority
4412 ,eri.ctp_flag
4413 ,eri.new_routing_revision
4414 ,eri.routing_comment
4415 ,eri.eco_for_production
4416 ,eri.transfer_or_copy
4417 ,eri.transfer_or_copy_item
4418 ,eri.transfer_or_copy_bill
4419 ,eri.transfer_or_copy_routing
4420 ,eri.copy_to_item
4421 ,eri.copy_to_item_desc
4422 ,eri.selection_option
4423 ,eri.selection_date
4424 ,eri.selection_unit_number
4425 ,eri.enable_item_in_local_org
4426 ,eri.create_bom_in_local_org
4427 ,eri.new_structure_revision
4428 ,eri.plan_level
4429 ,eri.new_revision_label
4430 ,eri.new_revision_reason
4431 ,eri.revised_item_sequence_id
4432 ,eriv.revised_item_status
4433 ,eri.organization_id
4434 FROM
4435 eng_revised_items eri
4436 ,mtl_item_flexfields mif
4437 ,mtl_item_flexfields mif1
4438 ,eng_revised_items_v eriv
4439 WHERE
4440 eri.change_id = p_change_id AND
4441 mif.inventory_item_id = eri.revised_item_id AND
4442 mif.organization_id = eri.organization_id AND
4443 mif1.inventory_item_id(+) = eri.use_up_item_id AND
4444 eri.revised_item_sequence_id = eriv.revised_item_sequence_id AND
4445 mif1.organization_id(+) = eri.organization_id;
4446
4447 CURSOR c_structure_header(p_change_id IN VARCHAR2,p_revised_item_id IN NUMBER) IS
4448 SELECT
4449 msl.concatenated_segments
4450 ,bev.common_organization_name
4451 ,bev.assembly_type
4452 ,bev.attribute1
4453 ,bev.attribute2
4454 ,bev.attribute3
4455 ,bev.attribute4
4456 ,bev.attribute5
4457 ,bev.attribute6
4458 ,bev.attribute7
4459 ,bev.attribute8
4460 ,bev.attribute9
4461 ,bev.attribute10
4462 ,bev.attribute11
4463 ,bev.attribute12
4464 ,bev.attribute13
4465 ,bev.attribute14
4466 ,bev.attribute15
4467 ,bev.bom_implementation_date
4468 ,bst.structure_type_name
4469 FROM
4470 bom_explosions_v bev
4471 ,eng_revised_items eri
4472 ,bom_structure_types_b bst
4473 ,mtl_system_items_vl msl
4474 WHERE
4475 eri.change_id = p_change_id AND
4476 bev.assembly_item_id = p_revised_item_id AND
4477 bev.access_flag = 'T' AND
4478 bev.organization_id = eri.organization_id(+) AND
4479 bst.structure_type_id = bev.structure_type_id AND
4480 msl.inventory_item_id(+) = bev.assembly_item_id AND
4481 msl.organization_id(+) = bev.organization_id;
4482
4483 c_bom_header c_structure_header%ROWTYPE;
4484
4485 CURSOR c_change_id(p_revised_item_sequence_id IN NUMBER) IS
4486 SELECT
4487 change_id
4488 FROM
4489 eng_Revised_items
4490 WHERE
4491 revised_item_sequence_id = p_revised_item_sequence_id;
4492
4493 BEGIN
4494 x_return_status := fnd_api.g_ret_sts_success;
4495 l_change_id := p_change_id ;
4496 l_revised_item_sequence_id := p_revised_item_sequence_id;
4497
4498 FND_MSG_PUB.initialize();
4499 IF (l_change_id IS NULL AND l_revised_item_sequence_id IS NULL) THEN
4500 FND_MESSAGE.set_name('INV','INV_EBI_CHG_ID_REV_SEQ_NULL');
4501 FND_MSG_PUB.add;
4502 RAISE FND_API.g_exc_error;
4503 END IF;
4504
4505 IF (l_revised_item_sequence_id IS NOT NULL AND l_change_id IS NULL) THEN
4506 OPEN c_change_id(l_revised_item_sequence_id);
4507 FETCH c_change_id INTO l_change_id;
4508 IF c_change_id%NOTFOUND THEN
4509 FND_MESSAGE.set_name('INV','INV_EBI_REV_SEQ_ID_INVALID');
4510 FND_MESSAGE.set_token('REV_SEQ_ID',l_revised_item_sequence_id);
4511 FND_MSG_PUB.add;
4512 RAISE FND_API.g_exc_error;
4513 END IF;
4514 CLOSE c_change_id;
4515 END IF;
4516
4517 IF p_name_val_list.name_value_table IS NOT NULL THEN
4518 FOR i in p_name_val_list.name_value_table.FIRST..p_name_val_list.name_value_table.LAST LOOP
4519 IF UPPER(p_name_val_list.name_value_table(i).param_name) = G_INCLUDE_REV_ITEMS THEN
4520 l_include_rev_items := p_name_val_list.name_value_table(i).param_value;
4521 END IF;
4522 IF UPPER(p_name_val_list.name_value_table(i).param_name) = G_INCLUDE_COMP_ITEMS THEN
4523 l_include_comp_items := p_name_val_list.name_value_table(i).param_value;
4524 END IF;
4525 IF UPPER(p_name_val_list.name_value_table(i).param_name) = G_INCLUDE_SUB_COMP THEN
4526 l_include_sub_comp := p_name_val_list.name_value_table(i).param_value;
4527 END IF;
4528 IF UPPER(p_name_val_list.name_value_table(i).param_name) = G_INCLUDE_REF_DESIGNATORS THEN
4529 l_include_ref_designators := p_name_val_list.name_value_table(i).param_value;
4530 END IF;
4531 END LOOP;
4532 END IF;
4533
4534 IF NVL(p_last_update_status,'Y') = 'N' THEN
4535 l_include_rev_items := fnd_api.g_false;
4536 l_include_comp_items := fnd_api.g_false;
4537 l_include_sub_comp := fnd_api.g_false;
4538 l_include_ref_designators := fnd_api.g_false;
4539 END IF;
4540 l_eco_revision_tbl := inv_ebi_eco_revision_tbl();
4541
4542 OPEN c_change_order_type (l_change_id);
4543 FETCH c_change_order_type INTO c_eco_header;
4544 IF c_change_order_type%NOTFOUND THEN
4545 FND_MESSAGE.set_name('INV','INV_EBI_CHG_ID_INVALID');
4546 FND_MESSAGE.set_token('CHG_ID',l_change_id);
4547 FND_MSG_PUB.add;
4548 RAISE FND_API.g_exc_error;
4549 END IF;
4550 l_eco_change_order_obj := inv_ebi_change_order_obj(
4551 c_eco_header.change_notice
4552 ,l_change_id
4553 ,c_eco_header.change_notice_prefix
4554 ,c_eco_header.change_notice_number
4555 ,c_eco_header.organization_code
4556 ,c_eco_header.organization_id
4557 ,c_eco_header.change_name
4558 ,c_eco_header.description
4559 ,c_eco_header.cancellation_comments
4560 ,c_eco_header.status_code
4561 ,c_eco_header.status_name
4562 ,c_eco_header.priority_code
4563 ,c_eco_header.reason_code
4564 ,c_eco_header.estimated_eng_cost
4565 ,c_eco_header.estimated_mfg_cost
4566 ,NULL
4567 ,c_eco_header.attribute_category
4568 ,c_eco_header.attribute1
4569 ,c_eco_header.attribute2
4570 ,c_eco_header.attribute3
4571 ,c_eco_header.attribute4
4572 ,c_eco_header.attribute5
4573 ,c_eco_header.attribute6
4574 ,c_eco_header.attribute7
4575 ,c_eco_header.attribute8
4576 ,c_eco_header.attribute9
4577 ,c_eco_header.attribute10
4578 ,c_eco_header.attribute11
4579 ,c_eco_header.attribute12
4580 ,c_eco_header.attribute13
4581 ,c_eco_header.attribute14
4582 ,c_eco_header.attribute15
4583 ,c_eco_header.ddf_context
4584 ,c_eco_header.approval_list_name
4585 ,NULL
4586 ,c_eco_header.approval_date
4587 ,c_eco_header.approval_request_date
4588 ,c_eco_header.change_order_type_id
4589 ,c_eco_header.type_name
4590 ,c_eco_header.change_mgmt_type_code
4591 ,c_eco_header.original_system_reference
4592 ,c_eco_header.organization_hierarchy
4593 ,NULL --c_eco_header.party_name --assignee
4594 ,NULL --c_eco_header.name --project_name
4595 ,NULL --c_eco_header.name --task_number
4596 ,c_eco_header.source_type_code
4597 ,c_eco_header.source_name
4598 ,c_eco_header.need_by_date
4599 ,c_eco_header.effort
4600 ,NULL --c_eco_header.name --eco_department_name
4601 ,NULL --transaction_id
4602 ,NULL --transaction_type
4603 ,c_eco_header.internal_use_only
4604 ,NULL --return_status
4605 ,c_eco_header.plm_or_erp_change
4606 ,NULL
4607 ,NULL
4608 ,NULL
4609 ,NULL
4610 ,NULL
4611 ,c_eco_header.status_type
4612 ,c_eco_header.implementation_date
4613 ,c_eco_header.cancellation_date
4614 ,NULL
4615 ,NULL
4616 ,NULL
4617 ,NULL
4618 ,NULL);
4619 CLOSE c_change_order_type;
4620
4621 l_revised_item_tbl := inv_ebi_revised_item_tbl();
4622
4623 l_current_index :=1;
4624 FOR cer IN c_eco_revision(l_change_id)
4625 LOOP
4626 l_eco_revision_tbl.extend();
4627 l_eco_revision_obj := inv_ebi_eco_revision_obj(
4628 cer.revision
4629 ,NULL
4630 ,cer.comments
4631 ,cer.attribute_category
4632 ,cer.attribute1
4633 ,cer.attribute2
4634 ,cer.attribute3
4635 ,cer.attribute4
4636 ,cer.attribute5
4637 ,cer.attribute6
4638 ,cer.attribute7
4639 ,cer.attribute8
4640 ,cer.attribute9
4641 ,cer.attribute10
4642 ,cer.attribute11
4643 ,cer.attribute12
4644 ,cer.attribute13
4645 ,cer.attribute14
4646 ,cer.attribute15
4647 ,cer.change_mgmt_type_code
4648 ,cer.original_system_reference
4649 ,NULL
4650 ,NULL
4651 ,NULL
4652 );
4653 l_eco_revision_tbl(l_current_index) := l_eco_revision_obj;
4654 l_current_index := l_current_index + 1;
4655 END LOOP;
4656
4657 IF (l_include_rev_items = fnd_api.g_true) THEN
4658 l_reviseditem_index := 1;
4659 FOR ri IN c_revised_item(l_change_id)
4660 LOOP
4661 OPEN c_structure_header(l_change_id,ri.revised_item_id);
4662 FETCH c_structure_header INTO c_bom_header;
4663 l_structure_header_obj := inv_ebi_structure_header_obj(
4664 c_bom_header.concatenated_segments
4665 ,c_bom_header.common_organization_name
4666 ,NULL
4667 ,c_bom_header.assembly_type
4668 ,NULL
4669 ,NULL
4670 ,NULL
4671 ,c_bom_header.attribute1
4672 ,c_bom_header.attribute2
4673 ,c_bom_header.attribute3
4674 ,c_bom_header.attribute4
4675 ,c_bom_header.attribute5
4676 ,c_bom_header.attribute6
4677 ,c_bom_header.attribute7
4678 ,c_bom_header.attribute8
4679 ,c_bom_header.attribute9
4680 ,c_bom_header.attribute10
4681 ,c_bom_header.attribute11
4682 ,c_bom_header.attribute12
4683 ,c_bom_header.attribute13
4684 ,c_bom_header.attribute14
4685 ,c_bom_header.attribute15
4686 ,NULL
4687 ,NULL
4688 ,NULL
4689 ,NULL
4690 ,c_bom_header.bom_implementation_date
4691 ,NULL
4692 ,c_bom_header.structure_type_name
4693 ,NULL
4694 );
4695 CLOSE c_structure_header;
4696 l_comp_index := 1;
4697 l_revision_component_tbl := inv_ebi_rev_comp_tbl();
4698 IF (l_include_comp_items = fnd_api.g_true) THEN
4699 FOR rc IN c_revision_component(l_change_id,ri.revised_item_id)
4700 LOOP
4701 l_ref_index := 1;
4702 l_reference_designator_tbl := inv_ebi_ref_desg_tbl();
4703 IF (l_include_ref_designators = fnd_api.g_true) THEN
4704 FOR rd IN c_reference_designator(l_change_id,ri.revised_item_id,rc.component_item_id)
4705 LOOP
4706 l_reference_designator_tbl.extend();
4707 l_reference_designator_obj := inv_ebi_ref_desg_obj(
4708 NULL
4709 ,rd.reference_designator_name
4710 ,rd.acd_type
4711 ,rd.ref_designator_comment
4712 ,rd.attribute_category
4713 ,rd.attribute1
4714 ,rd.attribute2
4715 ,rd.attribute3
4716 ,rd.attribute4
4717 ,rd.attribute5
4718 ,rd.attribute6
4719 ,rd.attribute7
4720 ,rd.attribute8
4721 ,rd.attribute9
4722 ,rd.attribute10
4723 ,rd.attribute11
4724 ,rd.attribute12
4725 ,rd.attribute13
4726 ,rd.attribute14
4727 ,rd.attribute15
4728 ,rd.original_system_reference
4729 ,NULL
4730 ,NULL
4731 ,NULL
4732 ,NULL
4733 ,NULL
4734 ,NULL
4735 ,NULL);
4736 l_reference_designator_tbl(l_ref_index) := l_reference_designator_obj;
4737 l_ref_index := l_ref_index + 1;
4738 END LOOP;
4739 END IF; -- IF (l_include_ref_designators = fnd_api.g_true) THEN
4740
4741 l_sub_index :=1;
4742 l_substitute_component_tbl := inv_ebi_sub_comp_tbl();
4743 IF (l_include_sub_comp = fnd_api.g_true) THEN
4744 FOR sc IN c_substitute_component(l_change_id,ri.revised_item_id,rc.component_item_id)
4745 LOOP
4746 l_substitute_component_tbl.extend();
4747 l_substitute_component_obj := inv_ebi_sub_comp_obj(
4748 NULL
4749 ,sc.substitute_component_name
4750 ,NULL
4751 ,sc.acd_type
4752 ,sc.substitute_item_quantity
4753 ,sc.attribute_category
4754 ,sc.attribute1
4755 ,sc.attribute2
4756 ,sc.attribute3
4757 ,sc.attribute4
4758 ,sc.attribute5
4759 ,sc.attribute6
4760 ,sc.attribute7
4761 ,sc.attribute8
4762 ,sc.attribute9
4763 ,sc.attribute10
4764 ,sc.attribute11
4765 ,sc.attribute12
4766 ,sc.attribute13
4767 ,sc.attribute14
4768 ,sc.attribute15
4769 ,sc.original_system_reference
4770 ,NULL
4771 ,NULL
4772 ,sc.enforce_int_requirements
4773 ,NULL
4774 ,NULL
4775 ,NULL
4776 ,NULL
4777 ,sc.program_id
4778 ,NULL);
4779 l_substitute_component_tbl(l_sub_index) := l_substitute_component_obj;
4780 l_sub_index := l_sub_index + 1;
4781 END LOOP;
4782 END IF; -- IF (l_include_sub_comp = fnd_api.g_true) THEN
4783
4784 l_revision_component_tbl.extend();
4785 l_revision_component_obj := inv_ebi_rev_comp_obj(
4786 NULL
4787 ,NULL
4788 ,rc.disable_date
4789 ,rc.operation_seq_num
4790 ,rc.component_item_name
4791 ,l_substitute_component_tbl
4792 ,l_reference_designator_tbl
4793 ,rc.acd_type
4794 ,NULL
4795 ,NULL
4796 ,NULL
4797 ,NULL
4798 ,rc.basis_type
4799 ,rc.component_quantity
4800 ,rc.inverse_quantity
4801 ,NULL
4802 ,NULL
4803 ,rc.include_in_cost_rollup
4804 ,rc.wip_supply_type
4805 ,rc.so_basis
4806 ,rc.optional
4807 ,rc.mutually_exclusive_options
4808 ,rc.check_atp
4809 ,rc.shipping_allowed
4810 ,rc.required_to_ship
4811 ,rc.required_for_revenue
4812 ,rc.include_on_ship_docs
4813 ,rc.quantity_related
4814 ,rc.supply_subinventory
4815 ,NULL
4816 ,NULL
4817 ,NULL
4818 ,NULL
4819 ,NULL
4820 ,rc.attribute_category
4821 ,rc.attribute1
4822 ,rc.attribute2
4823 ,rc.attribute3
4824 ,rc.attribute4
4825 ,rc.attribute5
4826 ,rc.attribute6
4827 ,rc.attribute7
4828 ,rc.attribute8
4829 ,rc.attribute9
4830 ,rc.attribute10
4831 ,rc.attribute11
4832 ,rc.attribute12
4833 ,rc.attribute13
4834 ,rc.attribute14
4835 ,rc.attribute15
4836 ,rc.from_end_item_unit_number
4837 ,NULL
4838 ,NULL
4839 ,rc.to_end_item_unit_number
4840 ,NULL
4841 ,rc.enforce_int_requirements
4842 ,rc.auto_request_material
4843 ,rc.suggested_vendor_name
4844 ,rc.unit_price
4845 ,rc.original_system_reference
4846 ,NULL
4847 ,NULL
4848 ,NULL
4849 ,NULL
4850 ,NULL
4851 ,NULL
4852 ,NULL
4853 ,NULL);
4854 l_revision_component_tbl(l_comp_index) := l_revision_component_obj;
4855 l_comp_index := l_comp_index + 1;
4856 END LOOP;
4857 END IF; -- IF (l_include_comp_items = fnd_api.g_true) THEN
4858
4859
4860 IF (l_revised_item_sequence_id IS NULL
4861 OR ri.revised_item_sequence_id = l_revised_item_sequence_id) THEN
4862
4863 l_revised_item_tbl.extend();
4864 l_item_obj :=inv_ebi_item_obj(NULL
4865 ,NULL
4866 ,NULL
4867 ,NULL
4868 ,NULL
4869 ,NULL
4870 ,NULL
4871 ,NULL
4872 ,NULL
4873 ,NULL
4874 ,NULL
4875 ,NULL
4876 ,NULL
4877 ,NULL
4878 ,NULL
4879 ,NULL
4880 ,NULL
4881 ,NULL
4882 ,NULL
4883 ,NULL
4884 ,NULL
4885 ,NULL
4886 ,NULL
4887 ,NULL
4888 ,NULL
4889 ,NULL
4890 ,NULL
4891 );
4892 INV_EBI_ITEM_HELPER.get_Operating_unit
4893 (p_oranization_id => ri.organization_id
4894 ,x_operating_unit => l_item_obj.operating_unit
4895 ,x_ouid => l_item_obj.operating_unit_id
4896 );
4897 l_revised_item_obj := inv_ebi_revised_item_obj(
4898 ri.revised_item_name
4899 ,ri.revised_item_id
4900 ,ri.new_item_revision
4901 ,NULL
4902 ,NULL
4903 ,NULL
4904 ,l_item_obj
4905 ,l_structure_header_obj
4906 ,l_revision_component_tbl
4907 ,NULL
4908 ,NULL
4909 ,NULL
4910 ,ri.scheduled_date
4911 ,ri.alternate_bom_designator
4912 ,ri.status_type
4913 ,ri.status_code
4914 ,ri.revised_item_status
4915 ,ri.mrp_active
4916 ,NULL
4917 ,ri.use_up_item_name
4918 ,ri.use_up_plan_name
4919 ,NULL
4920 ,ri.disposition_type
4921 ,ri.update_wip
4922 ,ri.cancel_comments
4923 ,NULL
4924 ,ri.attribute_category
4925 ,ri.attribute1
4926 ,ri.attribute2
4927 ,ri.attribute3
4928 ,ri.attribute4
4929 ,ri.attribute5
4930 ,ri.attribute6
4931 ,ri.attribute7
4932 ,ri.attribute8
4933 ,ri.attribute9
4934 ,ri.attribute10
4935 ,ri.attribute11
4936 ,ri.attribute12
4937 ,ri.attribute13
4938 ,ri.attribute14
4939 ,ri.attribute15
4940 ,ri.from_end_item_unit_number
4941 ,NULL
4942 ,ri.original_system_reference
4943 ,NULL
4944 ,NULL
4945 ,NULL
4946 ,NULL
4947 ,NULL
4948 ,ri.from_cum_qty
4949 ,ri.lot_number
4950 ,ri.completion_subinventory
4951 ,NULL
4952 ,ri.priority
4953 ,ri.ctp_flag
4954 ,ri.new_routing_revision
4955 ,NULL
4956 ,ri.routing_comment
4957 ,ri.eco_for_production
4958 ,NULL
4959 ,ri.transfer_or_copy
4960 ,ri.transfer_or_copy_item
4961 ,ri.transfer_or_copy_bill
4962 ,ri.transfer_or_copy_routing
4963 ,ri.copy_to_item
4964 ,ri.copy_to_item_desc
4965 ,NULL
4966 ,NULL
4967 ,ri.selection_option
4968 ,ri.selection_date
4969 ,ri.selection_unit_number
4970 ,NULL
4971 ,NULL
4972 ,NULL
4973 ,NULL
4974 ,ri.enable_item_in_local_org
4975 ,ri.create_bom_in_local_org
4976 ,ri.new_structure_revision
4977 ,ri.plan_level
4978 ,NULL
4979 ,NULL
4980 ,NULL
4981 ,NULL
4982 ,NULL
4983 ,ri.new_revision_label
4984 ,ri.new_revision_reason
4985 ,NULL
4986 ,NULL
4987 ,NULL
4988 ,NULL
4989 ,NULL
4990 ,NULL
4991 ,NULL);
4992
4993 l_revised_item_tbl(l_reviseditem_index) := l_revised_item_obj;
4994 l_reviseditem_index := l_reviseditem_index + 1;
4995 END IF;
4996 END LOOP;
4997 END IF; -- IF (l_include_rev_items = fnd_api.g_true) THEN
4998
4999 --Bug 7240247 To Retrieve Change order Header level Udas if any exists for this change_id
5000 get_change_order_uda(
5001 p_change_id => p_change_id,
5002 x_change_uda => l_eco_change_order_obj.change_order_uda,
5003 x_return_status => x_return_status,
5004 x_msg_count => x_msg_count,
5005 x_msg_data => x_msg_data
5006 );
5007
5008 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5009 RAISE FND_API.g_exc_unexpected_error;
5010 END IF;
5011
5012 --Bug 7240247 To Retrieve Structure Header level Udas if any exists for this change_id
5013 FOR i IN 1..l_revised_item_tbl.COUNT LOOP
5014 get_structure_header_uda(
5015 p_assembly_item_id => l_revised_item_tbl(i).revised_item_id,
5016 p_alternate_bom_code => l_revised_item_tbl(i).alternate_bom_code,
5017 p_organization_id => l_eco_change_order_obj.organization_id,
5018 x_structure_header_uda => l_revised_item_tbl(i).structure_header.structure_header_uda,
5019 x_return_status => x_return_status,
5020 x_msg_count => x_msg_count,
5021 x_msg_data => x_msg_data
5022 );
5023
5024 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5025 RAISE FND_API.g_exc_unexpected_error;
5026 END IF;
5027
5028 --Bug 7240247
5029 FOR j IN 1..l_revised_item_tbl(i).component_item_tbl.COUNT LOOP
5030 get_component_item_uda(
5031 p_eco_name => l_eco_change_order_obj.eco_name,
5032 p_revised_item_id => l_revised_item_tbl(i).revised_item_id,
5033 p_component_item_name => l_revised_item_tbl(i).component_item_tbl(j).component_item_name,
5034 p_alternate_bom_code => l_revised_item_tbl(i).alternate_bom_code,
5035 p_organization_id => l_eco_change_order_obj.organization_id,
5036 x_comp_item_uda => l_revised_item_tbl(i).component_item_tbl(j).component_revision_uda,
5037 x_return_status => x_return_status,
5038 x_msg_count => x_msg_count,
5039 x_msg_data => x_msg_data
5040 );
5041
5042 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5043 RAISE FND_API.g_exc_unexpected_error;
5044 END IF;
5045 END LOOP;
5046
5047 END LOOP;
5048
5049 x_eco_obj := inv_ebi_eco_obj(l_eco_change_order_obj,l_eco_revision_tbl,l_revised_item_tbl,NULL);
5050
5051 EXCEPTION
5052 WHEN FND_API.g_exc_error THEN
5053 x_return_status := FND_API.g_ret_sts_error;
5054 IF(x_msg_data IS NULL) THEN
5055 FND_MSG_PUB.count_and_get(
5056 p_encoded => FND_API.g_false
5057 ,p_count => x_msg_count
5058 ,p_data => x_msg_data
5059 );
5060 END IF;
5061 WHEN OTHERS THEN
5062 x_return_status := FND_API.g_ret_sts_error;
5063 IF (x_msg_data IS NOT NULL) THEN
5064 x_msg_data := x_msg_data ||' -> at INV_EBI_CHANGE_ORDER_HELPER.get_eco';
5065 ELSE
5066 x_msg_data := SQLERRM||' at INV_EBI_CHANGE_ORDER_HELPER.get_eco ';
5067 END IF;
5068 END get_eco;
5069
5070 /************************************************************************************
5071 -- API name : filter_ecos_based_on_org
5072 -- Type : Public
5073 -- Function : To filter eco's based on the given organization
5074 ************************************************************************************/
5075
5076 PROCEDURE filter_ecos_based_on_org(
5077 p_org_codes IN VARCHAR2
5078 ,p_eco_tbl IN inv_ebi_change_id_obj_tbl
5079 ,x_eco_tbl OUT NOCOPY inv_ebi_change_id_obj_tbl
5080 ,x_return_status OUT NOCOPY VARCHAR2
5081 ,x_msg_count OUT NOCOPY NUMBER
5082 ,x_msg_data OUT NOCOPY VARCHAR2)
5083 IS
5084 l_org_tbl FND_TABLE_OF_VARCHAR2_255;
5085 l_eco_output_tbl inv_ebi_change_id_obj_tbl;
5086 l_counter NUMBER:=0;
5087 l_org_code VARCHAR2(10);
5088 BEGIN
5089 x_return_status := FND_API.g_ret_sts_success;
5090 l_eco_output_tbl := inv_ebi_change_id_obj_tbl();
5091
5092 IF p_org_codes IS NOT NULL THEN
5093 l_org_tbl := INV_EBI_ITEM_HELPER.parse_input_string(p_org_codes);
5094 END IF;
5095
5096 IF p_eco_tbl IS NOT NULL AND p_eco_tbl.COUNT>0 THEN
5097 FOR i in p_eco_tbl.FIRST..p_eco_tbl.LAST LOOP
5098 l_org_code := NULL;
5099 SELECT mp.organization_code INTO l_org_code
5100 FROM eng_engineering_changes ec, mtl_parameters mp
5101 WHERE ec.change_id = p_eco_tbl(i).change_id
5102 AND ec.organization_id = mp.organization_id;
5103 IF l_org_tbl IS NOT NULL AND l_org_tbl.COUNT>0 THEN
5104 FOR j in l_org_tbl.FIRST..l_org_tbl.LAST LOOP
5105 IF (l_org_code = l_org_tbl(j)) THEN
5106 l_counter := l_counter + 1;
5107 l_eco_output_tbl.EXTEND(1);
5108 l_eco_output_tbl(l_counter) := p_eco_tbl(i);
5109 EXIT;
5110 END IF;
5111 END LOOP;
5112 END IF;
5113 END LOOP;
5114 END IF;
5115
5116 x_eco_tbl := l_eco_output_tbl;
5117
5118 EXCEPTION
5119 WHEN OTHERS THEN
5120 x_return_status := FND_API.g_ret_sts_unexp_error;
5121 x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.filter_ecos_based_on_org';
5122 END filter_ecos_based_on_org;
5123
5124 /***************************************************************************************************
5125 -- API name : parse_and_get_eco
5126 -- Type : Private For Internal Use Only
5127 -- Purpose : To parse the input string and get lis of eco
5128 *****************************************************************************************************/
5129 PROCEDURE parse_and_get_eco(
5130 p_eco_names IN VARCHAR2
5131 ,p_org_codes IN VARCHAR2
5132 ,x_eco_tbl OUT NOCOPY inv_ebi_change_id_obj_tbl
5133 ,x_return_status OUT NOCOPY VARCHAR2
5134 ,x_msg_count OUT NOCOPY NUMBER
5135 ,x_msg_data OUT NOCOPY VARCHAR2)
5136 IS
5137 l_return_status VARCHAR2(2);
5138 l_msg_data VARCHAR2(2000);
5139 l_chg_id NUMBER;
5140 l_org_id NUMBER;
5141 l_count NUMBER:=0;
5142 l_counter NUMBER := 0;
5143 l_entity_exist NUMBER :=0;
5144 l_entity_count NUMBER :=0;
5145 l_eco_obj inv_ebi_change_id_obj;
5146 l_eco_output_tbl inv_ebi_change_id_obj_tbl;
5147 l_eco_tbl FND_TABLE_OF_VARCHAR2_255;
5148 l_org_tbl FND_TABLE_OF_VARCHAR2_255;
5149 l_valid_eco_tbl FND_TABLE_OF_VARCHAR2_255;
5150 l_valid_org_tbl FND_TABLE_OF_VARCHAR2_255;
5151 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl ;
5152 CURSOR c_get_all_orgs( p_chg_notice VARCHAR2) IS
5153 SELECT change_id
5154 FROM eng_engineering_changes
5155 WHERE change_notice = p_chg_notice;
5156 BEGIN
5157 x_return_status := FND_API.g_ret_sts_success;
5158 l_eco_output_tbl :=inv_ebi_change_id_obj_tbl();
5159
5160 IF p_eco_names IS NOT NULL THEN
5161 l_eco_tbl := INV_EBI_ITEM_HELPER.parse_input_string(p_eco_names);
5162 END IF;
5163
5164 IF p_org_codes IS NOT NULL THEN
5165 l_org_tbl := INV_EBI_ITEM_HELPER.parse_input_string(p_org_codes);
5166 END IF;
5167
5168 IF l_eco_tbl IS NOT NULL AND l_eco_tbl.COUNT > 0 THEN
5169 l_valid_eco_tbl := FND_TABLE_OF_VARCHAR2_255();
5170 l_entity_count :=0;
5171 FOR i in l_eco_tbl.FIRST..l_eco_tbl.LAST LOOP
5172 BEGIN
5173 FND_MSG_PUB.initialize();
5174 SELECT COUNT(1) into l_entity_exist
5175 FROM eng_engineering_changes
5176 WHERE change_notice = l_eco_tbl(i);
5177 IF l_entity_exist>0 THEN
5178 l_entity_count := l_entity_count +1;
5179 l_valid_eco_tbl.EXTEND();
5180 l_valid_eco_tbl(l_entity_count) := l_eco_tbl(i);
5181 ELSE
5182 FND_MESSAGE.set_name('INV','INV_EBI_ITEM_INVALID');
5183 FND_MESSAGE.set_token('COL_VALUE', l_eco_tbl(i));
5184 FND_MSG_PUB.add;
5185 RAISE FND_API.G_EXC_ERROR;
5186 END IF;
5187 EXCEPTION
5188 WHEN FND_API.G_EXC_ERROR THEN
5189 x_return_status := FND_API.g_ret_sts_error;
5190 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
5191 ,p_count => x_msg_count
5192 ,p_data => l_msg_data
5193 );
5194 x_msg_data := x_msg_data || l_msg_data ||' , ' ;
5195 WHEN OTHERS THEN
5196 x_return_status := FND_API.g_ret_sts_error;
5197 x_msg_data := SQLERRM ||' at INV_EBI_CHANGE_ORDER_HELPER.parse_and_get_eco';
5198 END;
5199 END LOOP;
5200 END IF;
5201
5202 IF l_org_tbl IS NOT NULL AND l_org_tbl.COUNT > 0 THEN
5203 l_valid_org_tbl := FND_TABLE_OF_VARCHAR2_255();
5204 l_entity_count :=0;
5205 FOR i in l_org_tbl.FIRST..l_org_tbl.LAST LOOP
5206 BEGIN
5207 FND_MSG_PUB.initialize();
5208 SELECT COUNT(1) into l_entity_exist
5209 FROM mtl_parameters
5210 WHERE organization_code = l_org_tbl(i);
5211 IF l_entity_exist>0 THEN
5212 l_entity_count := l_entity_count +1;
5213 l_valid_org_tbl.EXTEND();
5214 l_valid_org_tbl(l_entity_count) := l_org_tbl(i);
5215 ELSE
5216 FND_MESSAGE.set_name('INV','INV_EBI_ORG_CODE_INVALID');
5217 FND_MESSAGE.set_token('COL_VALUE', l_org_tbl(i));
5218 FND_MSG_PUB.add;
5219 RAISE FND_API.G_EXC_ERROR;
5220 END IF;
5221 EXCEPTION
5222 WHEN FND_API.G_EXC_ERROR THEN
5223 x_return_status := FND_API.g_ret_sts_error;
5224 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
5225 ,p_count => x_msg_count
5226 ,p_data => l_msg_data
5227 );
5228 x_msg_data := x_msg_data || l_msg_data ||' , ' ;
5229 WHEN OTHERS THEN
5230 x_return_status := FND_API.g_ret_sts_error;
5231 x_msg_data := SQLERRM ||' at INV_EBI_CHANGE_ORDER_HELPER.parse_and_get_eco';
5232 END;
5233 END LOOP;
5234 END IF;
5235
5236 IF l_valid_eco_tbl IS NOT NULL AND l_valid_eco_tbl.COUNT > 0 THEN
5237 FOR i in l_valid_eco_tbl.FIRST..l_valid_eco_tbl.LAST LOOP
5238 BEGIN
5239 FND_MSG_PUB.initialize();
5240 l_count :=0;
5241 IF l_valid_org_tbl IS NOT NULL AND l_valid_org_tbl.COUNT > 0 THEN
5242 FOR j in l_valid_org_tbl.FIRST..l_valid_org_tbl.LAST LOOP
5243 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5244 l_pk_col_name_val_pairs.EXTEND();
5245 l_pk_col_name_val_pairs(1).name := 'organization_code';
5246 l_pk_col_name_val_pairs(1).value := l_valid_org_tbl(j);
5247 l_org_id := INV_EBI_ITEM_HELPER.value_to_id( p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5248 ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
5249 );
5250 l_pk_col_name_val_pairs.TRIM(1);
5251
5252 l_chg_id := NULL;
5253 BEGIN
5254 SELECT change_id
5255 INTO l_chg_id
5256 FROM eng_engineering_changes
5257 WHERE change_notice = l_valid_eco_tbl(i)
5258 AND organization_id = l_org_id;
5259 EXCEPTION
5260 WHEN OTHERS THEN
5261 NULL;
5262 END;
5263
5264 IF l_chg_id IS NOT NULL THEN
5265 l_counter := l_counter + 1;
5266 l_eco_obj := inv_ebi_change_id_obj( l_chg_id, 'Y');
5267 l_eco_output_tbl.EXTEND(1);
5268 l_eco_output_tbl(l_counter) := l_eco_obj;
5269 l_count := 1;
5270 END IF;
5271
5272 END LOOP;
5273 ELSE
5274 FOR cur IN c_get_all_orgs(l_valid_eco_tbl(i)) LOOP
5275 l_counter := l_counter + 1;
5276 l_eco_obj := inv_ebi_change_id_obj( cur.change_id, 'Y');
5277 l_eco_output_tbl.EXTEND(1);
5278 l_eco_output_tbl(l_counter) := l_eco_obj;
5279 l_count := 1;
5280 END LOOP;
5281 END IF;
5282
5283 IF l_count = 0 THEN
5284 FND_MESSAGE.set_name('INV','INV_EBI_INVALID_USER_INPUT');
5285 FND_MESSAGE.set_token('USER_INPUT', l_valid_eco_tbl(i));
5286 FND_MSG_PUB.add;
5287 RAISE FND_API.G_EXC_ERROR;
5288 END IF;
5289 EXCEPTION
5290 WHEN FND_API.G_EXC_ERROR THEN
5291 x_return_status := FND_API.g_ret_sts_error;
5292 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
5293 ,p_count => x_msg_count
5294 ,p_data => l_msg_data
5295 );
5296 x_msg_data := x_msg_data || l_msg_data ;
5297 WHEN OTHERS THEN
5298 x_return_status := FND_API.g_ret_sts_unexp_error;
5299 x_msg_data := SQLERRM ||' at INV_EBI_CHANGE_ORDER_HELPER.parse_and_get_eco';
5300 END;
5301 END LOOP;
5302 END IF;
5303 x_eco_tbl := l_eco_output_tbl;
5304 EXCEPTION
5305 WHEN OTHERS THEN
5306 x_return_status := FND_API.g_ret_sts_unexp_error;
5307 x_msg_data := SQLERRM ||' at INV_EBI_CHANGE_ORDER_HELPER.parse_and_get_eco';
5308 END parse_and_get_eco;
5309
5310 /************************************************************************************
5311 -- API name : get_eco_list
5312 -- Type : Public
5313 -- Function :
5314 -- Comments : This API to return list of change ids, prepatel
5315 ************************************************************************************/
5316 PROCEDURE get_eco_list(
5317 p_name_value_list IN inv_ebi_name_value_tbl
5318 ,p_prog_id IN NUMBER
5319 ,p_appl_id IN NUMBER
5320 ,x_eco OUT NOCOPY inv_ebi_change_id_obj_tbl
5321 ,x_return_status OUT NOCOPY VARCHAR2
5322 ,x_msg_count OUT NOCOPY NUMBER
5323 ,x_msg_data OUT NOCOPY VARCHAR2
5324 )
5325 IS
5326 l_eco inv_ebi_change_id_obj_tbl;
5327 l_eco_output_tbl inv_ebi_change_id_obj_tbl;
5328 l_eco_org_output_tbl inv_ebi_change_id_obj_tbl;
5329 l_eco_tbl inv_ebi_change_id_obj_tbl;
5330 l_eco_string VARCHAR2(32000);
5331 l_org_string VARCHAR2(2000);
5332 l_from_date_str VARCHAR2(30);
5333 l_to_date_str VARCHAR2(30);
5334 l_from_date DATE := NULL;
5335 l_to_date DATE := NULL;
5336 l_last_x_hrs NUMBER;
5337 l_return_status VARCHAR2(2);
5338 l_msg_data VARCHAR2(2000);
5339 l_msg_count NUMBER;
5340
5341 CURSOR c_get_eco_chgid IS
5342 SELECT inv_ebi_change_id_obj(eci.change_id, 'Y')
5343 FROM(
5344 SELECT eec.change_id
5345 FROM eng_engineering_changes eec
5346 WHERE eec.last_update_date <> eec.creation_date
5347 AND eec.last_update_date >= l_from_date
5348 AND eec.last_update_date <= l_to_date
5349 UNION
5350 SELECT eri.change_id
5351 FROM eng_revised_items eri
5352 WHERE eri.last_update_date <> eri.creation_date
5353 AND eri.last_update_date >= l_from_date
5354 AND eri.last_update_date <= l_to_date ) eci;
5355
5356 CURSOR c_get_final_eco_list IS
5357 SELECT inv_ebi_change_id_obj(geco.change_id,geco.last_update_status)
5358 FROM (SELECT b.change_id,b.last_update_status
5359 FROM THE (SELECT CAST( l_eco as inv_ebi_change_id_obj_tbl)
5360 FROM dual ) b
5361 INTERSECT
5362 SELECT c.change_id,c.last_update_status
5363 FROM THE (SELECT CAST( l_eco_output_tbl as inv_ebi_change_id_obj_tbl)
5364 FROM dual ) c ) geco;
5365 l_ind_val NUMBER :=1;
5366 BEGIN
5367 FND_MSG_PUB.initialize();
5368 x_return_status := FND_API.G_RET_STS_SUCCESS;
5369
5370 IF (p_name_value_list IS NOT NULL AND p_name_value_list.COUNT > 0) THEN
5371 l_eco_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Change Order Name');
5372 l_org_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Organization Code');
5373 l_from_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'From Date');
5374 l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
5375 l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
5376 IF l_from_date_str IS NOT NULL THEN
5377 l_from_date := TO_DATE(l_from_date_str,'YYYY/MM/DD HH24:MI:SS');
5378 END IF;
5379 IF l_to_date_str IS NOT NULL THEN
5380 l_to_date := TO_DATE(l_to_date_str,'YYYY/MM/DD HH24:MI:SS');
5381 END IF;
5382 IF l_last_x_hrs IS NOT NULL THEN
5383 l_from_date := SYSDATE-( l_last_x_hrs/24);
5384 l_to_date := SYSDATE ;
5385 END IF;
5386 END IF;
5387
5388 IF (l_eco_string IS NULL AND l_from_date IS NULL AND l_to_date IS NULL AND l_last_x_hrs IS NULL) THEN
5389 l_from_date :=INV_EBI_ITEM_HELPER.get_last_run_date( p_conc_prog_id => p_prog_id
5390 ,p_appl_id => p_appl_id
5391 );
5392 l_to_date := SYSDATE;
5393 END IF;
5394
5395 IF l_from_date IS NOT NULL AND l_to_date IS NULL THEN
5396 l_to_date := SYSDATE;
5397 END IF;
5398
5399 IF ( l_eco_string IS NOT NULL ) THEN
5400 parse_and_get_eco( p_eco_names => l_eco_string
5401 ,p_org_codes => l_org_string
5402 ,x_eco_tbl => l_eco_output_tbl
5403 ,x_return_status => l_return_status
5404 ,x_msg_count => l_msg_count
5405 ,x_msg_data => l_msg_data);
5406 END IF;
5407
5408 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
5409 x_return_status := l_return_status;
5410 IF l_msg_data IS NOT NULL THEN
5411 x_msg_data := l_msg_data;
5412 END IF;
5413 END IF;
5414
5415 x_eco := l_eco_output_tbl;
5416 l_eco := inv_ebi_change_id_obj_tbl();
5417
5418 IF (l_from_date IS NOT NULL AND l_to_date IS NOT NULL) THEN
5419 IF (c_get_eco_chgid%ISOPEN) THEN
5420 CLOSE c_get_eco_chgid;
5421 END IF;
5422 OPEN c_get_eco_chgid ;
5423 FETCH c_get_eco_chgid BULK COLLECT INTO l_eco ;
5424 CLOSE c_get_eco_chgid;
5425 IF( l_eco_string IS NOT NULL ) THEN
5426 IF(c_get_final_eco_list%ISOPEN) THEN
5427 CLOSE c_get_final_eco_list;
5428 END IF;
5429 OPEN c_get_final_eco_list ;
5430 FETCH c_get_final_eco_list BULK COLLECT INTO l_eco_tbl ;
5431 CLOSE c_get_final_eco_list;
5432 x_eco:=l_eco_tbl;
5433 ELSIF (l_org_string IS NOT NULL) THEN
5434 filter_ecos_based_on_org( p_org_codes => l_org_string
5435 ,p_eco_tbl => l_eco
5436 ,x_eco_tbl => l_eco_org_output_tbl
5437 ,x_return_status => l_return_status
5438 ,x_msg_count => l_msg_count
5439 ,x_msg_data => l_msg_data);
5440
5441 IF (l_return_status = FND_API.g_ret_sts_success) THEN
5442 x_eco := l_eco_org_output_tbl;
5443 ELSE
5444 x_return_status := l_return_status;
5445 IF x_msg_data IS NOT NULL THEN
5446 x_msg_data := x_msg_data || l_msg_data;
5447 ELSE
5448 x_msg_data := l_msg_data;
5449 END IF;
5450 END IF;
5451 ELSE
5452 x_eco:=l_eco;
5453 END IF;
5454 END IF;
5455
5456 EXCEPTION
5457 WHEN OTHERS THEN
5458 x_return_status := FND_API.g_ret_sts_unexp_error;
5459 x_msg_data := SQLERRM ||' at INV_EBI_CHANGE_ORDER_HELPER.get_eco_list';
5460 IF (c_get_final_eco_list%ISOPEN) THEN
5461 CLOSE c_get_final_eco_list;
5462 END IF;
5463
5464 END get_eco_list;
5465
5466 END INV_EBI_CHANGE_ORDER_HELPER;