[Home] [Help]
PACKAGE BODY: APPS.INV_EBI_CHANGE_ORDER_PUB
Source
1 PACKAGE BODY INV_EBI_CHANGE_ORDER_PUB AS
2 /* $Header: INVEIPCOB.pls 120.34.12010000.11 2009/02/23 16:25:43 prepatel ship $ */
3 /************************************************************************************
4 -- API name : populate_item_attributes
5 -- Type : Private
6 -- Function :
7 -- This API is used to
8 --
9 ************************************************************************************/
10 PROCEDURE populate_item_attributes(
11 p_change_order_obj IN inv_ebi_change_order_obj
12 ,p_revised_item IN inv_ebi_revised_item_obj
13 ,x_item OUT NOCOPY inv_ebi_item_obj
14 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
15 ) IS
16 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
17 l_main_item_obj inv_ebi_item_main_obj;
18 l_output_status inv_ebi_output_status;
19 BEGIN
20 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
21 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
22 IF(p_revised_item.item IS NOT NULL ) THEN
23 x_item := p_revised_item.item;
24 ELSE
25 l_main_item_obj := inv_ebi_item_main_obj(fnd_api.g_miss_num,fnd_api.g_miss_char,
26 fnd_api.g_miss_char,fnd_api.g_miss_num,
27 fnd_api.g_miss_char,fnd_api.g_miss_num,
28 fnd_api.g_miss_num,fnd_api.g_miss_char,
29 fnd_api.g_miss_char,fnd_api.g_miss_char,
30 fnd_api.g_miss_char,fnd_api.g_miss_char,
31 fnd_api.g_miss_char,fnd_api.g_miss_num,
32 fnd_api.g_miss_num,fnd_api.g_miss_char,
33 fnd_api.g_miss_char,fnd_api.g_miss_num,
34 fnd_api.g_miss_num,fnd_api.g_miss_char,
35 fnd_api.g_miss_char,fnd_api.g_miss_char,
36 fnd_api.g_miss_char,fnd_api.g_miss_char,
37 fnd_api.g_miss_char,fnd_api.g_miss_char,
38 fnd_api.g_miss_char,fnd_api.g_miss_char,
39 fnd_api.g_miss_char,fnd_api.g_miss_char,
40 fnd_api.g_miss_char,fnd_api.g_miss_char,
41 fnd_api.g_miss_char,fnd_api.g_miss_char,
42 fnd_api.g_miss_char,fnd_api.g_miss_char,
43 fnd_api.g_miss_char,fnd_api.g_miss_char,
44 fnd_api.g_miss_char,fnd_api.g_miss_char,
45 fnd_api.g_miss_char,fnd_api.g_miss_char,
46 fnd_api.g_miss_char,fnd_api.g_miss_char,
47 fnd_api.g_miss_char,fnd_api.g_miss_date,
48 fnd_api.g_miss_date,fnd_api.g_miss_date,
49 fnd_api.g_miss_num,fnd_api.g_miss_date,
50 fnd_api.g_miss_num,fnd_api.g_miss_num,
51 fnd_api.g_miss_num,fnd_api.g_miss_num,
52 fnd_api.g_miss_num,fnd_api.g_miss_date,
53 fnd_api.g_miss_num,fnd_api.g_miss_char,
54 fnd_api.g_miss_num,fnd_api.g_miss_char,
55 fnd_api.g_miss_num,fnd_api.g_miss_char,
56 fnd_api.g_miss_char,fnd_api.g_miss_char,
57 fnd_api.g_miss_date,fnd_api.g_miss_num,
58 fnd_api.g_miss_char,fnd_api.g_miss_num,
59 fnd_api.g_miss_char,fnd_api.g_miss_char,
60 fnd_api.g_miss_num,fnd_api.g_miss_char,
61 fnd_api.g_miss_num,fnd_api.g_miss_char,
62 fnd_api.g_miss_char,fnd_api.g_miss_char,
63 fnd_api.g_miss_char,fnd_api.g_miss_char,
64 fnd_api.g_miss_char,fnd_api.g_miss_char,
65 fnd_api.g_miss_char,fnd_api.g_miss_char,
66 fnd_api.g_miss_char);
67 x_item:=inv_ebi_Item_Obj(l_main_item_obj,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
68 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
69 END IF;
70 IF(x_item.main_obj_type.item_number IS NULL OR x_item.main_obj_type.item_number = fnd_api.g_miss_char )THEN
71 x_item.main_obj_type.item_number := p_revised_item.revised_item_name;
72 END IF;
73 IF(x_item.main_obj_type.organization_id IS NULL OR x_item.main_obj_type.organization_id = fnd_api.g_miss_num )THEN
74 IF((p_change_order_obj.organization_id IS NULL
75 OR p_change_order_obj.organization_id = fnd_api.g_miss_num)
76 AND (p_change_order_obj.organization_code IS NOT NULL
77 OR p_change_order_obj.organization_code <> fnd_api.g_miss_char)) THEN
78 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
79 l_pk_col_name_val_pairs.EXTEND(1);
80 l_pk_col_name_val_pairs(1).name := 'organization_code';
81 l_pk_col_name_val_pairs(1).value := p_change_order_obj.organization_code;
82 x_item.main_obj_type.organization_id := INV_EBI_ITEM_HELPER.value_to_id(
83 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
84 ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
85 );
86 l_pk_col_name_val_pairs.TRIM(1);
87 IF (x_item.main_obj_type.organization_id IS NULL) THEN
88 FND_MESSAGE.set_name('INV','INV_EBI_ORG_CODE_INVALID');
89 FND_MESSAGE.set_token('COL_VALUE', p_change_order_obj.organization_code);
90 FND_MSG_PUB.add;
91 RAISE FND_API.G_EXC_ERROR;
92 END IF;
93 ELSE
94 x_item.main_obj_type.organization_id := p_change_order_obj.organization_id;
95 END IF;
96 END IF;
97
98 IF(x_item.main_obj_type.init_msg_list IS NULL OR x_item.main_obj_type.init_msg_list = fnd_api.g_miss_char )THEN
99 x_item.main_obj_type.init_msg_list := FND_API.G_TRUE;
100 END IF;
101 EXCEPTION
102 WHEN FND_API.g_exc_error THEN
103 x_out.output_status.return_status := FND_API.g_ret_sts_error;
104 IF(x_out.output_status.msg_data IS NULL) THEN
105 FND_MSG_PUB.count_and_get(
106 p_encoded => FND_API.g_false
107 ,p_count => x_out.output_status.msg_count
108 ,p_data => x_out.output_status.msg_data
109 );
110 END IF;
111 WHEN OTHERS THEN
112 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
113 IF (x_out.output_status.msg_data IS NOT NULL) THEN
114 x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_CHANGE_ORDER_PUB.populate_item_attributes ';
115 ELSE
116 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_PUB.populate_item_attributes ';
117 END IF;
118 END populate_item_attributes;
119 /************************************************************************************
120 -- API name : transfer_engg_item_mfg
121 -- Type : Private
122 -- Function :
123 -- This API is used to
124 --
125 ************************************************************************************/
126 PROCEDURE transfer_engg_item_mfg(
127 p_item IN inv_ebi_item_obj
128 ,p_alt_bom_designator IN VARCHAR2
129 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
130 ) IS
131 l_approval_status VARCHAR2(30);
132 l_item_number VARCHAR2(2000);
133 l_is_engineering_item VARCHAR2(3);
134 l_output_status inv_ebi_output_status;
135 l_designator_option NUMBER;
136 l_alt_bom_designator VARCHAR2(10);
137 l_inventory_item_id NUMBER;
138 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
139
140 BEGIN
141 SAVEPOINT inv_ebi_engg_item_save_pnt;
142 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
143 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
144
145 IF(p_item.main_obj_type.inventory_item_id IS NULL OR
146 p_item.main_obj_type.inventory_item_id = fnd_api.g_miss_num) THEN
147
148 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
149 l_pk_col_name_val_pairs.EXTEND(2);
150 l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
151 l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.item_number;
152 l_pk_col_name_val_pairs(2).name := 'organization_id';
153 l_pk_col_name_val_pairs(2).value := p_item.main_obj_type.organization_id;
154
155 l_inventory_item_id := INV_EBI_ITEM_HELPER.value_to_id(
156 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
157 ,p_entity_name => INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
158 );
159 l_pk_col_name_val_pairs.TRIM(2);
160 IF (l_inventory_item_id IS NULL ) THEN
161 FND_MESSAGE.set_name('INV','INV_EBI_ITEM_INVALID');
162 FND_MESSAGE.set_token('COL_VALUE', p_item.main_obj_type.item_number);
163 FND_MSG_PUB.add;
164 RAISE FND_API.g_exc_unexpected_error;
165 END IF;
166 ELSE
167 l_inventory_item_id := p_item.main_obj_type.inventory_item_id;
168
169 END IF;
170
171 IF((l_inventory_item_id IS NOT NULL
172 AND l_inventory_item_id <> fnd_api.g_miss_num)
173 AND (p_item.main_obj_type.organization_id IS NOT NULL
174 AND p_item.main_obj_type.organization_id <> fnd_api.g_miss_num)) THEN
175
176 SELECT approval_status INTO l_approval_status
177 FROM mtl_system_items_b
178 WHERE inventory_item_id = l_inventory_item_id
179 AND organization_id = p_item.main_obj_type.organization_id;
180
181 END IF;
182 l_is_engineering_item := INV_EBI_ITEM_HELPER.is_engineering_item(
183 p_organization_id => p_item.main_obj_type.organization_id
184 ,p_item_number => p_item.main_obj_type.item_number
185 );
186
187 IF(l_is_engineering_item = FND_API.g_true
188 AND l_approval_status = 'A'
189 AND p_item.bom_obj_type.eng_item_flag = 'N'
190 ) THEN
191
192 --Transfer Engg Item to Manufacturing
193 -- If alt_bom_code is null then transfer all boms,if not transfer only that particular bom
194 IF(p_alt_bom_designator IS NULL OR p_alt_bom_designator = fnd_api.g_miss_char ) THEN
195 l_designator_option := 1;
196 l_alt_bom_designator := NULL;
197 ELSE
198 l_designator_option := 3;
199 l_alt_bom_designator := p_alt_bom_designator;
200 END IF;
201
202 ENG_BOM_RTG_TRANSFER_PKG.eng_bom_rtg_transfer(
203 x_org_id => p_item.main_obj_type.organization_id,
204 x_eng_item_id => l_inventory_item_id,
205 x_mfg_item_id => l_inventory_item_id,
206 x_transfer_option => 1,
207 x_designator_option => l_designator_option,
208 x_alt_bom_designator => l_alt_bom_designator,
209 x_alt_rtg_designator => NULL,
210 x_effectivity_date => NULL,
211 x_last_login_id => p_item.main_obj_type.last_updated_by,
212 x_bom_rev_starting => NULL,
213 x_rtg_rev_starting => NULL,
214 x_ecn_name => NULL,
215 x_item_code => 1, --to transfer item to Mfg
216 x_bom_code => 1, --to transfer Bom to Mfg
217 x_rtg_code => 2,
218 x_mfg_description => p_item.main_obj_type.description,
219 x_segment1 => p_item.main_obj_type.segment1,
220 x_segment2 => p_item.main_obj_type.segment2,
221 x_segment3 => p_item.main_obj_type.segment3,
222 x_segment4 => p_item.main_obj_type.segment4,
223 x_segment5 => p_item.main_obj_type.segment5,
224 x_segment6 => p_item.main_obj_type.segment6,
225 x_segment7 => p_item.main_obj_type.segment7,
226 x_segment8 => p_item.main_obj_type.segment8,
227 x_segment9 => p_item.main_obj_type.segment9,
228 x_segment10 => p_item.main_obj_type.segment10,
229 x_segment11 => p_item.main_obj_type.segment11,
230 x_segment12 => p_item.main_obj_type.segment12,
231 x_segment13 => p_item.main_obj_type.segment13,
232 x_segment14 => p_item.main_obj_type.segment14,
233 x_segment15 => p_item.main_obj_type.segment15,
234 x_segment16 => p_item.main_obj_type.segment16,
235 x_segment17 => p_item.main_obj_type.segment17,
236 x_segment18 => p_item.main_obj_type.segment18,
237 x_segment19 => p_item.main_obj_type.segment19,
238 x_segment20 => p_item.main_obj_type.segment20,
239 x_implemented_only => NULL,
240 x_commit => FALSE
241 );
242 END IF;
243 EXCEPTION
244 WHEN FND_API.g_exc_unexpected_error THEN
245 ROLLBACK TO inv_ebi_engg_item_save_pnt;
246 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
247 IF(x_out.output_status.msg_data IS NULL) THEN
248 FND_MSG_PUB.count_and_get(
249 p_encoded => FND_API.g_false
250 ,p_count => x_out.output_status.msg_count
251 ,p_data => x_out.output_status.msg_data
252 );
253 END IF;
254 WHEN OTHERS THEN
255 ROLLBACK TO inv_ebi_engg_item_save_pnt;
256 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
257 IF (x_out.output_status.msg_data IS NOT NULL) THEN
258 x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_CHANGE_ORDER_PUB.transfer_engg_item_manufacturing ';
259 ELSE
260 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_PUB.transfer_engg_item_manufacturing ';
261 END IF;
262 END transfer_engg_item_mfg;
263 /************************************************************************************
264 -- API name : validate_component_items
265 -- Type : Private
266 -- Function :
267 -- This API is used to
268 --
269 ************************************************************************************/
270 PROCEDURE validate_component_items(
271 p_organization_code IN VARCHAR2
272 ,p_component_item_tbl IN inv_ebi_rev_comp_tbl
273 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
274 ) IS
275 l_is_master_org VARCHAR2(3);
276 l_organization_id NUMBER;
277 l_component_item_name VARCHAR2(240);
278 l_is_component_item_exists VARCHAR2(3);
279 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
280 l_output_status inv_ebi_output_status;
281 BEGIN
282 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
283 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
284 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
285 l_pk_col_name_val_pairs.EXTEND(1);
286 l_pk_col_name_val_pairs(1).name := 'organization_code';
287 l_pk_col_name_val_pairs(1).value := p_organization_code;
288 l_organization_id := INV_EBI_ITEM_HELPER.value_to_id(
289 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
290 ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
291 );
292 IF (l_organization_id IS NULL) THEN
293 FND_MESSAGE.set_name('INV','INV_EBI_ORG_CODE_INVALID');
294 FND_MESSAGE.set_token('COL_VALUE',p_organization_code);
295 FND_MSG_PUB.add;
296 RAISE FND_API.g_exc_error;
297 END IF;
298 l_is_master_org := INV_EBI_UTIL.is_master_org(l_organization_id);
299 IF(p_component_item_tbl IS NOT NULL AND p_component_item_tbl.COUNT > 0) THEN
300 FOR i IN 1.. p_component_item_tbl.COUNT LOOP
301 IF(p_component_item_tbl(i).transaction_type = INV_EBI_ITEM_PUB.g_otype_create ) THEN
302 l_component_item_name := p_component_item_tbl(i).component_item_name;
303 l_is_component_item_exists := INV_EBI_ITEM_HELPER.is_item_exists(
304 p_organization_id => l_organization_id
305 ,p_item_number => l_component_item_name
306 );
307 IF(l_is_master_org = fnd_api.g_true AND l_is_component_item_exists = fnd_api.g_false) THEN
308 FND_MESSAGE.set_name('INV','INV_EBI_ITEM_NO_MASTER_ORG');
309 FND_MESSAGE.set_token('COMP_ITEM', l_component_item_name);
310 FND_MSG_PUB.add;
311 RAISE FND_API.g_exc_error;
312 END IF;
313 END IF;
314 END LOOP;
315 END IF;
316 EXCEPTION
317 WHEN FND_API.g_exc_error THEN
318 x_out.output_status.return_status := FND_API.g_ret_sts_error;
319 IF(x_out.output_status.msg_data IS NULL) THEN
320 FND_MSG_PUB.count_and_get(
321 p_encoded => FND_API.g_false
322 ,p_count => x_out.output_status.msg_count
323 ,p_data => x_out.output_status.msg_data
324 );
325 END IF;
326 WHEN OTHERS THEN
327 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
328 IF (x_out.output_status.msg_data IS NOT NULL) THEN
329 x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_CHANGE_ORDER_PUB.validate_component_items ';
330 ELSE
331 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_PUB.validate_component_items ';
332 END IF;
333 END validate_component_items;
334
335 /************************************************************************************
336 -- API name : validate_eco
337 -- Type : Private
338 -- Function :
339 -- This API is used to
340 --
341 ************************************************************************************/
342 PROCEDURE validate_eco(
343 p_eco_obj IN inv_ebi_eco_obj
344 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
345 ) IS
346 l_output_status inv_ebi_output_status;
347 l_item_output inv_ebi_item_output_obj;
348 l_transaction_type VARCHAR2(20);
349 l_organization_id NUMBER;
350 BEGIN
351 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
352 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
353 FND_MSG_PUB.initialize;
354 IF p_eco_obj.eco_revised_item_type IS NOT NULL THEN
355 FOR i IN 1..p_eco_obj.eco_revised_item_type.COUNT LOOP
356 IF p_eco_obj.eco_revised_item_type(i).item IS NOT NULL THEN
357 l_organization_id := INV_EBI_ITEM_HELPER.get_organization_id (
358 p_organization_code => p_eco_obj.eco_change_order_type.organization_code
359 );
360 IF(INV_EBI_ITEM_HELPER.is_item_exists (
361 p_organization_id => l_organization_id
362 ,p_item_number => p_eco_obj.eco_revised_item_type(i).revised_item_name
363 ) = FND_API.g_false ) THEN
364 l_transaction_type := INV_EBI_ITEM_PUB.g_otype_create;
365 INV_EBI_ITEM_PUB.validate_item (
366 p_transaction_type => l_transaction_type
367 ,p_item => p_eco_obj.eco_revised_item_type(i).item
368 ,x_out => l_item_output
369 ) ;
370 IF(l_item_output.output_status.return_status <> FND_API.g_ret_sts_success) THEN
371 RAISE FND_API.g_exc_unexpected_error;
372 END IF;
373 END IF;
374 END IF;
375 END LOOP;
376 END IF;
377 EXCEPTION
378 WHEN FND_API.g_exc_unexpected_error THEN
379 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
380 IF(x_out.output_status.msg_data IS NULL) THEN
381 FND_MSG_PUB.count_and_get(
382 p_encoded => FND_API.g_false
383 ,p_count => x_out.output_status.msg_count
384 ,p_data => x_out.output_status.msg_data
385 );
386 END IF;
387 WHEN OTHERS THEN
388 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
389 IF (x_out.output_status.msg_data IS NOT NULL) THEN
390 x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_CHANGE_ORDER_PUB.validate_eco ';
391 ELSE
392 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_PUB.validate_eco ';
393 END IF;
394 END validate_eco;
395 /************************************************************************************
396 -- API name : populate_revised_items_out
397 -- Type : Private
398 -- Function :
399 -- This API is used to populate Revised item details after eco creation.
400 ************************************************************************************/
401 PROCEDURE populate_revised_items_out
402 (p_change_order IN inv_ebi_change_order_obj
403 ,p_revised_item_type_tbl IN inv_ebi_revised_item_tbl
404 ,x_revised_item_type_tbl OUT NOCOPY inv_ebi_revitem_output_obj_tbl
405 )IS
406 l_organization_id NUMBER;
407 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
408 l_revised_item_type_tbl inv_ebi_revitem_output_obj_tbl;
409 l_ouid NUMBER;
410 l_operating_unit VARCHAR2(240);
411 BEGIN
412 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
413 l_revised_item_type_tbl := inv_ebi_revitem_output_obj_tbl();
414 l_pk_col_name_val_pairs.EXTEND(1);
415 l_pk_col_name_val_pairs(1).name := 'organization_code';
416 l_pk_col_name_val_pairs(1).value := p_change_order.organization_code;
417
418 l_organization_id := INV_EBI_ITEM_HELPER.value_to_id(
419 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
420 ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
421 );
422 l_pk_col_name_val_pairs.TRIM(1);
423
424 INV_EBI_ITEM_HELPER.get_Operating_unit
425 (p_oranization_id => l_organization_id
426 ,x_operating_unit => l_operating_unit
427 ,x_ouid => l_ouid
428 );
429 IF(p_revised_item_type_tbl IS NOT NULL AND p_revised_item_type_tbl.COUNT > 0) THEN
430 FOR i IN 1..p_revised_item_type_tbl.COUNT
431 LOOP
432 l_revised_item_type_tbl.extend();
433 l_revised_item_type_tbl(i) := INV_EBI_REVITEM_OUTPUT_OBJ(NULL,NULL);
434 l_revised_item_type_tbl(i).revised_item := INV_EBI_ITEM_OUTPUT_OBJ(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
435 l_revised_item_type_tbl(i).revised_item.ORGANIZATION_CODE := p_change_order.organization_code ;
436 l_revised_item_type_tbl(i).revised_item.ORGANIZATION_ID := l_organization_id;
437 l_revised_item_type_tbl(i).revised_item.ITEM_NUMBER := p_revised_item_type_tbl(i).revised_item_name;
438 l_revised_item_type_tbl(i).revised_item.INVENTORY_ITEM_ID := INV_EBI_ITEM_HELPER.get_inventory_item_id
439 ( p_organization_id => l_organization_id
440 ,p_item_number => p_revised_item_type_tbl(i).revised_item_name);
441 l_revised_item_type_tbl(i).revised_item.operating_unit := l_operating_unit;
442 l_revised_item_type_tbl(i).revised_item.operating_unit_id := l_ouid;
443 l_revised_item_type_tbl(i).rev_component :=INV_EBI_COMP_OUTPUT_OBJ_TBL();
444 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
445 FOR j IN 1..p_revised_item_type_tbl(i).component_item_tbl.COUNT
446 LOOP
447 l_revised_item_type_tbl(i).rev_component.extend();
448 l_revised_item_type_tbl(i).rev_component(j) := INV_EBI_COMP_OUTPUT_OBJ(NULL,NULL);
449 l_revised_item_type_tbl(i).rev_component(j).component := INV_EBI_ITEM_OUTPUT_OBJ(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
450 l_revised_item_type_tbl(i).rev_component(j).component.ORGANIZATION_CODE := p_change_order.organization_code ;
451 l_revised_item_type_tbl(i).rev_component(j).component.ORGANIZATION_ID := l_organization_id;
452 l_revised_item_type_tbl(i).rev_component(j).component.ITEM_NUMBER := p_revised_item_type_tbl(i).component_item_tbl(j).component_item_name;
453 l_revised_item_type_tbl(i).rev_component(j).component.INVENTORY_ITEM_ID := INV_EBI_ITEM_HELPER.get_inventory_item_id
454 ( p_organization_id => l_organization_id
455 ,p_item_number => p_revised_item_type_tbl(i).component_item_tbl(j).component_item_name);
456 l_revised_item_type_tbl(i).rev_component(j).component.operating_unit := l_operating_unit;
457 l_revised_item_type_tbl(i).rev_component(j).component.operating_unit_id := l_ouid;
458 l_revised_item_type_tbl(i).rev_component(j).subcomponent := INV_EBI_ITEM_OUTPUT_TBL();
459
460 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
461 FOR k IN 1..p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl.COUNT
462 LOOP
463 l_revised_item_type_tbl(i).rev_component(j).subcomponent.extend();
464 l_revised_item_type_tbl(i).rev_component(j).subcomponent(k) :=INV_EBI_ITEM_OUTPUT_OBJ(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
465 l_revised_item_type_tbl(i).rev_component(j).subcomponent(k).ORGANIZATION_CODE := p_change_order.organization_code ;
466 l_revised_item_type_tbl(i).rev_component(j).subcomponent(k).ORGANIZATION_ID := l_organization_id;
467 l_revised_item_type_tbl(i).rev_component(j).subcomponent(k).ITEM_NUMBER := p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).substitute_component_name;
468 l_revised_item_type_tbl(i).rev_component(j).subcomponent(k).INVENTORY_ITEM_ID :=INV_EBI_ITEM_HELPER.get_inventory_item_id
469 ( p_organization_id => l_organization_id
470 ,p_item_number => p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).substitute_component_name);
471 l_revised_item_type_tbl(i).rev_component(j).subcomponent(k).operating_unit := l_operating_unit;
472 l_revised_item_type_tbl(i).rev_component(j).subcomponent(k).operating_unit_id := l_ouid;
473 END LOOP;
474 END IF;
475 END LOOP;
476 END IF;
477 END LOOP;
478 END IF;
479 x_revised_item_type_tbl := l_revised_item_type_tbl;
480 END populate_revised_items_out;
481
482 /************************************************************************************
483 -- API name : process_change_order
484 -- Type : Public
485 -- Function :
486 -- This API is used to Create Change Order
487 --
488 ************************************************************************************/
489
490 PROCEDURE process_change_order(
491 p_commit IN VARCHAR2 := FND_API.g_false
492 ,p_eco_obj IN inv_ebi_eco_obj
493 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
494 ) IS
495 l_change_id NUMBER;
496 l_change_notice VARCHAR2(10);
497 l_organization_id NUMBER;
498 l_organization_code VARCHAR2(3);
499 l_uda_output inv_ebi_uda_output_obj;
500 l_item inv_ebi_item_obj;
501 l_category_output inv_ebi_category_output_obj;
502 l_pkdata ego_col_name_value_pair_array;
503 l_pkcode ego_col_name_value_pair_array;
504 l_eco_obj inv_ebi_eco_obj;
505 l_eco_obj_validate_eco inv_ebi_eco_obj;
506 l_out inv_ebi_eco_output_obj;
507 l_output_status inv_ebi_output_status;
508 l_item_output inv_ebi_item_output_obj;
509 l_item_output_tbl inv_ebi_item_output_tbl;
510 l_curr_status_code NUMBER;
511 l_status_code NUMBER;
512 l_change_order_type_id NUMBER;
513 l_is_wf_Set BOOLEAN;
514 l_item_obj_tbl inv_ebi_item_attr_tbl;
515 l_item_count NUMBER :=0;
516 l_update_item_count NUMBER :=0;
517 l_assign_item_to_child_org VARCHAR2(1):= fnd_api.g_false;
518 l_update_item_tab FND_TABLE_OF_NUMBER;
519 l_update_item_index NUMBER := 0;
520 l_cnt_nmval NUMBER := 0;
521 l_revitem_output_tbl inv_ebi_revitem_output_obj_tbl;
522 l_master_org NUMBER;
523 l_inventory_item_id NUMBER;
524 l_current_revision VARCHAR2(3);
525 l_revision VARCHAR2(3);
526 l_revision_set BOOLEAN := FALSE;
527 l_api_version NUMBER:=1.0;
528 l_is_task_template_set BOOLEAN := FALSE;
529 l_change_type_code NUMBER;
530 l_status_name VARCHAR2(30);
531
532 CURSOR c_effectivity_date(
533 p_organization_id IN NUMBER,
534 p_inventory_item_id IN NUMBER
535 )
536 IS
537 SELECT revision
538 FROM mtl_item_revisions_b
539 WHERE
540 organization_id = p_organization_id AND
541 inventory_item_id = p_inventory_item_id;
542
543 BEGIN
544 SAVEPOINT inv_ebi_chg_order_save_pnt;
545 FND_MSG_PUB.initialize;
546 --fnd_global.apps_initialize(1068,56229,724,0);
547 l_uda_output := inv_ebi_uda_output_obj(NULL,NULL);
548 INV_EBI_UTIL.debug_line('STEP: 10 START INSIDE INV_EBI_CHANGE_ORDER_PUB.process_change_order');
549 l_category_output := inv_ebi_category_output_obj(NULL);
550 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
551 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
552 l_item_output_tbl := inv_ebi_item_output_tbl();
553 l_item_obj_tbl := inv_ebi_item_attr_tbl();
554 l_update_item_tab := FND_TABLE_OF_NUMBER();
555
556 l_eco_obj_validate_eco := p_eco_obj;
557 l_revitem_output_tbl := inv_ebi_revitem_output_obj_tbl();
558 IF(p_eco_obj.eco_revised_item_type IS NOT NULL AND p_eco_obj.eco_change_order_type IS NOT NULL) THEN
559 FOR i IN 1 .. l_eco_obj_validate_eco.eco_revised_item_type.COUNT
560 LOOP
561 --To populate item attributes from revised item values.
562 populate_item_attributes(
563 p_change_order_obj => l_eco_obj_validate_eco.eco_change_order_type
564 ,p_revised_item => l_eco_obj_validate_eco.eco_revised_item_type(i)
565 ,x_item => l_item
566 ,x_out => l_out
567 );
568 IF (l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
569 x_out.output_status.msg_data := l_out.output_status.msg_data;
570 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
571 END IF;
572 INV_EBI_ITEM_HELPER.populate_item_ids(
573 p_item => l_item
574 ,x_out => l_item_output
575 ,x_item => l_eco_obj_validate_eco.eco_revised_item_type(i).item
576 );
577 IF (l_item_output.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
578 x_out.output_status.msg_data := l_item_output.OUTPUT_STATUS.MSG_DATA;
579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
580 END IF;
581 END LOOP;
582 END IF;
583 INV_EBI_UTIL.debug_line('STEP: 20 START CALLING INV_EBI_CHANGE_ORDER_PUB.validate_eco');
584
585 validate_eco(
586 p_eco_obj => l_eco_obj_validate_eco
587 ,x_out => l_out
588 );
589 INV_EBI_UTIL.debug_line('STEP: 30 END CALLING INV_EBI_CHANGE_ORDER_PUB.validate_eco');
590 IF (l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
591 x_out.output_status.msg_data := l_out.output_Status.msg_data;
592 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
593 END IF;
594 IF(l_eco_obj_validate_eco.eco_revised_item_type IS NOT NULL AND l_eco_obj_validate_eco.eco_change_order_type IS NOT NULL) THEN
595 FOR i IN 1 .. l_eco_obj_validate_eco.eco_revised_item_type.COUNT
596 LOOP
597 l_eco_obj_validate_eco.eco_revised_item_type(i).item.NAME_VALUE_TBL := INV_EBI_NAME_VALUE_TBL();
598 l_cnt_nmval := 0;
599 IF l_eco_obj_validate_eco.NAME_VALUE_TBL IS NOT NULL AND l_eco_obj_validate_eco.NAME_VALUE_TBL.COUNT >0 THEN
600 FOR x in 1..l_eco_obj_validate_eco.NAME_VALUE_TBL.COUNT
601 LOOP
602 IF l_eco_obj_validate_eco.NAME_VALUE_TBL(x).PARAM_NAME in ('VALIDATE_REVISED_ITEM_REVISION','TEMPLATE_FOR_ITEM_UPDATE_ALLOWED') THEN
603 l_eco_obj_validate_eco.eco_revised_item_type(i).item.NAME_VALUE_TBL.extend();
604 l_cnt_nmval := l_cnt_nmval+1;
605 l_eco_obj_validate_eco.eco_revised_item_type(i).item.NAME_VALUE_TBL(l_cnt_nmval) := INV_EBI_NAME_VALUE_OBJ(l_eco_obj_validate_eco.NAME_VALUE_TBL(x).PARAM_NAME,l_eco_obj_validate_eco.NAME_VALUE_TBL(x).PARAM_VALUE);
606 END IF;
607 END LOOP;
608 END IF;
609
610 l_revision_set := FALSE;
611 IF( INV_EBI_ITEM_HELPER.is_item_exists (
612 p_organization_id => l_eco_obj_validate_eco.eco_revised_item_type(i).item.main_obj_type.organization_id
613 ,p_item_number => l_eco_obj_validate_eco.eco_revised_item_type(i).item.main_obj_type.item_number
614 ) = FND_API.g_false ) THEN
615
616 /* Bug 7197943 After subsequent release of ecos in master org ,If Chnage Order is released in Child Org
617 then we should create initial rev,current rev of the item in master org through process_item API
618 and incoming revision through process_eco API */
619
620 IF(INV_EBI_CHANGE_ORDER_HELPER.is_child_org (
621 p_organization_id => l_eco_obj_validate_eco.eco_revised_item_type(i).item.main_obj_type.organization_id
622 ) = fnd_api.g_true) THEN
623
624 l_master_org := INV_EBI_UTIL.get_master_organization(
625 p_organization_id => l_eco_obj_validate_eco.eco_revised_item_type(i).item.main_obj_type.organization_id
626 );
627
628 l_inventory_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id (
629 p_organization_id => l_master_org
630 ,p_item_number => l_eco_obj_validate_eco.eco_revised_item_type(i).item.main_obj_type.item_number
631 );
632
633 l_current_revision := INV_EBI_CHANGE_ORDER_HELPER.get_current_item_revision(
634 p_inventory_item_id => l_inventory_item_id,
635 p_organization_id => l_master_org,
636 p_date => sysdate
637 );
638
639 IF(l_current_revision = l_eco_obj_validate_eco.eco_revised_item_type(i).new_revised_item_revision ) THEN
640 l_eco_obj_validate_eco.eco_revised_item_type(i).item.main_obj_type.revision_code := fnd_api.g_miss_char;
641 ELSIF( l_current_revision <> l_eco_obj_validate_eco.eco_revised_item_type(i).new_revised_item_revision AND
642 l_current_revision < l_eco_obj_validate_eco.eco_revised_item_type(i).new_revised_item_revision ) THEN
643
644 l_eco_obj_validate_eco.eco_revised_item_type(i).item.main_obj_type.revision_code := l_current_revision;
645 --Bug 7197943 If 2 revisions are to be created then intial rev gets created with sysdate +1 ,to have effectivity_date of
646 -- next rev greater than that of initial rev we added 2 sec
647 l_eco_obj_validate_eco.eco_revised_item_type(i).item.main_obj_type.effectivity_date := sysdate+ 2/86400;
648 l_revision_set := TRUE;
649
650 END IF;
651 END IF;
652
653 --call Create New Production Item API .This will also process existing item information.
654 INV_EBI_UTIL.debug_line('STEP: 40 START CALLING INV_EBI_ITEM_HELPER.sync_item');
655 INV_EBI_ITEM_HELPER.sync_item(
656 p_commit => FND_API.g_false
657 ,p_operation => INV_EBI_ITEM_PUB.g_otype_create
658 ,p_item => l_eco_obj_validate_eco.eco_revised_item_type(i).item
659 ,x_out => l_item_output
660 );
661 INV_EBI_UTIL.debug_line('STEP: 50 END CALLING INV_EBI_ITEM_HELPER.sync_item');
662 l_item_count := l_item_count + 1;
663 l_item_output_tbl.EXTEND(1);
664 l_item_output_tbl(l_item_count) := l_item_output;
665
666 IF (l_item_output.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
667 x_out.output_status.msg_data := l_item_output.output_status.msg_data;
668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
669 END IF;
670
671 /* Bug 7197943 When we create 2 revison thru EGO_ITEM_pUB.process_item API
672 they are adding sysdate + 1 sec to initial rev and given rev gets created
673 only if effectivty date being passed is greater than sysdate+1 sec.
674 By this eco errors out as it does not have current implemnted rev for the
675 revised ite,.So updating implementaion date and effectivity date of initial
676 rev to sysdate.*/
677 IF(l_revision_set = TRUE ) THEN
678
679 IF c_effectivity_date%ISOPEN THEN
680 CLOSE c_effectivity_date;
681 END IF;
682 OPEN c_effectivity_date (
683 p_organization_id => l_item_output.organization_id,
684 p_inventory_item_id => l_item_output.inventory_item_id
685 );
686 FETCH c_effectivity_date INTO l_revision;
687 CLOSE c_effectivity_date;
688 INV_EBI_UTIL.debug_line('STEP: 60 START update mtl_item_revisions_b');
689 UPDATE mtl_item_revisions_b
690 SET
691 implementation_date = sysdate,
692 effectivity_date = sysdate,
693 creation_date = sysdate,
694 last_update_date = sysdate,
695 last_updated_by = DECODE(last_updated_by, NULL, fnd_global.user_id,last_updated_by),
696 created_by = DECODE(last_updated_by, NULL, fnd_global.user_id,last_updated_by)
697 WHERE
698 inventory_item_id = l_item_output.inventory_item_id AND
699 organization_id = l_item_output.organization_id AND
700 revision = l_revision;
701 INV_EBI_UTIL.debug_line('STEP: 70 END update mtl_item_revisions_b');
702 END IF;
703
704 ELSE
705
706 l_item_obj_tbl.EXTEND(1);
707 l_update_item_count := l_item_obj_tbl.COUNT;
708 l_item_count := l_item_count + 1;
709 l_item_obj_tbl(l_update_item_count) := INV_EBI_GET_ITEM_OUTPUT_OBJ(NULL,NULL,NULL,NULL);
710 l_item_obj_tbl(l_update_item_count).item_obj := l_eco_obj_validate_eco.eco_revised_item_type(i).item;
711 l_update_item_tab.EXTEND(1);
712 l_update_item_tab(l_update_item_count) := l_item_count;
713 l_item_output_tbl.EXTEND(1);
714
715 --Transfer Engg Item to Manufacturing
716 INV_EBI_UTIL.debug_line('STEP: 80 START CALLING transfer_engg_item_mfg');
717 transfer_engg_item_mfg(
718 p_item => l_eco_obj_validate_eco.eco_revised_item_type(i).item
719 ,p_alt_bom_designator => l_eco_obj_validate_eco.eco_revised_item_type(i).alternate_bom_code
720 ,x_out => l_out
721 );
722 INV_EBI_UTIL.debug_line('STEP: 90 END CALLING transfer_engg_item_mfg');
723 IF (l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
724 x_out.output_status.msg_data := l_out.output_status.msg_data;
725 RAISE fnd_api.g_exc_unexpected_error;
726 END IF;
727
728 END IF;
729 END LOOP;
730 END IF;
731
732 IF l_eco_obj_validate_eco.name_value_tbl IS NOT NULL THEN
733 FOR i in l_eco_obj_validate_eco.name_value_tbl.FIRST..l_eco_obj_validate_eco.name_value_tbl.LAST LOOP
734 IF (UPPER(l_eco_obj_validate_eco.name_value_tbl(i).param_name) = INV_EBI_CHANGE_ORDER_HELPER.G_ASSIGN_ITEM_TO_CHILD_ORG) THEN
735 l_assign_item_to_child_org := l_eco_obj_validate_eco.name_value_tbl(i).param_value;
736 INV_EBI_CHANGE_ORDER_HELPER.set_assign_item(
737 p_assign_item => l_assign_item_to_child_org
738 );
739 END IF;
740 END LOOP;
741 END IF;
742 INV_EBI_UTIL.debug_line('STEP: 100 START CALLING INV_EBI_CHANGE_ORDER_HELPER.process_replicate_bom');
743 --Processing of Replicate Bom
744 INV_EBI_CHANGE_ORDER_HELPER.process_replicate_bom(
745 p_eco_obj => l_eco_obj_validate_eco
746 ,x_eco_obj => l_eco_obj
747 ,x_out => l_out
748 );
749 INV_EBI_UTIL.debug_line('STEP: 110 END CALLING INV_EBI_CHANGE_ORDER_HELPER.process_replicate_bom');
750 IF (l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
751 x_out.output_status.msg_data := l_out.output_status.msg_data;
752 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
753 END IF;
754
755 IF(l_eco_obj.eco_revised_item_type IS NOT NULL AND l_eco_obj.eco_change_order_type IS NOT NULL) THEN
756 FOR i IN 1 .. l_eco_obj.eco_revised_item_type.COUNT
757 LOOP
758 IF(l_eco_obj.eco_revised_item_type(i).structure_header IS NOT NULL) THEN
759 --Structure Header creation and Common Bom processing
760 INV_EBI_UTIL.debug_line('STEP: 120 START CALLING INV_EBI_CHANGE_ORDER_HELPER.process_structure_header'||
761 ' For Revised Item Name: '||l_eco_obj.eco_revised_item_type(i).revised_item_name);
762
763 INV_EBI_CHANGE_ORDER_HELPER.process_structure_header(
764 p_commit => FND_API.g_false
765 ,p_organization_code => l_eco_obj.eco_change_order_type.organization_code
766 ,p_assembly_item_name => l_eco_obj.eco_revised_item_type(i).revised_item_name
767 ,p_alternate_bom_code => l_eco_obj.eco_revised_item_type(i).alternate_bom_code
768 ,p_structure_header => l_eco_obj.eco_revised_item_type(i).structure_header
769 ,p_component_item_tbl => l_eco_obj.eco_revised_item_type(i).component_item_tbl -- Bug 7196996
770 ,p_name_val_list => inv_ebi_name_value_list(l_eco_obj.name_value_tbl)
771 ,x_out => l_out
772 );
773 INV_EBI_UTIL.debug_line('STEP: 130 END CALLING INV_EBI_CHANGE_ORDER_HELPER.process_structure_header'||
774 ' For Revised Item Name: '||l_eco_obj.eco_revised_item_type(i).revised_item_name);
775 END IF;
776 IF (l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
777 x_out.output_status.msg_data := l_out.output_status.msg_data;
778 RAISE FND_API.g_exc_unexpected_error;
779 END IF;
780 END LOOP;
781 END IF;
782 bom_globals.set_bo_identifier(bom_globals.g_eco_bo);
783 --Change Order Creation
784 INV_EBI_UTIL.debug_line('STEP: 140 START CALLING INV_EBI_CHANGE_ORDER_HELPER.process_eco');
785 INV_EBI_CHANGE_ORDER_HELPER.process_eco(
786 p_commit => FND_API.g_false
787 ,p_change_order => l_eco_obj.eco_change_order_type
788 ,p_revision_type_tbl => l_eco_obj.eco_revision_type
789 ,p_revised_item_type_tbl => l_eco_obj.eco_revised_item_type
790 ,p_name_val_list => inv_ebi_name_value_list(l_eco_obj.name_value_tbl)
791 ,x_out => l_out
792 );
793 INV_EBI_UTIL.debug_line('STEP: 150 END CALLING INV_EBI_CHANGE_ORDER_HELPER.process_eco');
794 IF (l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
795 x_out.output_status.msg_data := l_out.output_status.msg_data;
796 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
797 END IF;
798
799 l_change_id := l_out.change_id;
800 l_change_notice := l_out.change_notice;
801 l_organization_id := l_out.organization_id;
802 l_organization_code := l_out.organization_code;
803
804 l_is_wf_Set := INV_EBI_CHANGE_ORDER_HELPER.Check_Workflow_Process(p_change_order_type_id => l_eco_obj.eco_change_order_type.change_type_id
805 ,p_priority_code => l_eco_obj.eco_change_order_type.priority_code
806 );
807 SELECT status_code
808 INTO l_curr_status_code
809 FROM eng_engineering_changes
810 WHERE change_id = l_change_id;
811
812 l_status_name := INV_EBI_CHANGE_ORDER_HELPER.get_eco_status_name(l_eco_obj.eco_change_order_type.status_code);
813
814 IF (LOWER(l_status_name) = 'implemented' ) THEN
815 SELECT status_code
816 INTO l_status_code
817 FROM eng_change_statuses_vl
818 WHERE LOWER(status_name) = 'scheduled'; -- Scheduled
819 ELSE
820 l_status_code := l_eco_obj.eco_change_order_type.status_code;
821 END IF;
822
823 /*Bug 7218542:If some task is associated at organization level for the change order type
824 we cannot promote it to scheduled status.*/
825
826 l_is_task_template_set := INV_EBI_CHANGE_ORDER_HELPER.is_task_template_set(
827 p_change_order_type_id => l_eco_obj.eco_change_order_type.change_type_id
828 ,p_organization_id => l_organization_id
829 ,p_status_code => l_status_code
830 );
831
832 IF ( (NOT l_is_wf_Set) AND
833 (NOT l_is_task_template_set) AND
834 (l_status_code > l_curr_status_code) ) THEN
835 --Bug 6833363
836 INV_EBI_UTIL.debug_line('STEP: 160 START CALLING ENG_CHANGE_LIFECYCLE_UTIL.Change_Phase');
837
838 ENG_CHANGE_LIFECYCLE_UTIL.Change_Phase(
839 p_api_version => 1.0
840 ,p_commit => FND_API.g_false
841 ,p_object_name => 'ENG_CHANGE'
842 ,p_change_id => l_change_id
843 ,p_status_code => l_status_code
844 ,p_action_type => ENG_CHANGE_LIFECYCLE_UTIL.G_ENG_PROMOTE -- promote/demote
845 ,x_return_status => l_out.output_status.return_status
846 ,x_msg_count => l_out.output_status.msg_count
847 ,x_msg_data => l_out.output_status.msg_data
848 );
849 INV_EBI_UTIL.debug_line('STEP: 170 END CALLING ENG_CHANGE_LIFECYCLE_UTIL.Change_Phase');
850 IF (l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
851 x_out.output_status.msg_data := l_out.output_status.msg_data;
852 RAISE FND_API.g_exc_unexpected_error;
853 END IF;
854 END IF;
855
856 --Processing of Udas
857 IF (INV_EBI_UTIL.is_pim_installed) THEN
858 IF ((l_eco_obj.eco_change_order_type IS NOT NULL AND l_eco_obj.eco_change_order_type.change_order_uda IS NOT NULL)
859 AND l_eco_obj.eco_change_order_type.change_order_uda.attribute_group_tbl.COUNT > 0) THEN
860 IF ((l_eco_obj.eco_change_order_type.eco_name IS NOT NULL)
861 AND (l_eco_obj.eco_change_order_type.organization_id IS NULL OR l_eco_obj.eco_change_order_type.organization_id =FND_API.g_miss_num)
862 AND (l_eco_obj.eco_change_order_type.organization_code IS NOT NULL OR l_eco_obj.eco_change_order_type.organization_code <> FND_API.g_miss_char)) THEN
863
864 SELECT change_order_type_id INTO l_change_order_type_id
865 FROM eng_engineering_changes
866 WHERE change_notice = l_eco_obj.eco_change_order_type.eco_name
867 AND organization_id = l_organization_id;
868
869 END IF;
870 l_pkdata := ego_col_name_value_pair_array();
871 l_pkdata.extend();
872 l_pkdata(1) := ego_col_name_value_pair_obj('CHANGE_ID',l_change_id);
873 l_pkcode := ego_col_name_value_pair_array();
874 l_pkcode.extend();
875 l_pkcode(1) := ego_col_name_value_pair_obj('CHANGE_TYPE_ID',l_change_order_type_id);
876
877 --To process Change order header udas
878 INV_EBI_CHANGE_ORDER_HELPER.process_uda(
879 p_uda_input_obj => l_eco_obj.eco_change_order_type.change_order_uda
880 ,p_commit => fnd_api.g_false
881 ,p_object_name => 'ENG_CHANGE'
882 ,p_data_level => 'CHANGE_LEVEL'
883 ,p_pk_column_name_value_pairs => l_pkdata
884 ,p_class_code_name_value_pairs => l_pkcode
885 ,x_uda_output_obj => l_out
886 );
887 END IF;
888 IF(l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
889 x_out.output_status.msg_data := l_out.output_status.msg_data;
890 RAISE fnd_api.g_exc_unexpected_error;
891 END IF;
892 l_uda_output := inv_ebi_uda_output_obj(l_out.uda_output.failed_row_id_list,l_out.uda_output.errorcode);
893 IF(l_eco_obj.eco_revised_item_type IS NOT NULL AND l_eco_obj.eco_revised_item_type.COUNT > 0) THEN
894 FOR i IN 1..l_eco_obj.eco_revised_item_type.COUNT
895 LOOP
896
897 IF ( l_eco_obj.eco_revised_item_type(i).item.uda_type IS NOT NULL AND
898 l_eco_obj.eco_revised_item_type(i).item.uda_type.attribute_group_tbl.COUNT > 0) THEN
899
900 -- To process Revised Item udas
901
902 l_inventory_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id (
903 p_organization_id => l_organization_id
904 ,p_item_number => l_eco_obj.eco_revised_item_type(i).revised_item_name
905 );
906
907 INV_EBI_ITEM_HELPER.process_item_uda(
908 p_api_version => l_api_version
909 ,p_inventory_item_id => l_inventory_item_id
910 ,p_organization_id => l_organization_id
911 ,p_item_catalog_group_id => l_eco_obj.eco_revised_item_type(i).item.main_obj_type.item_catalog_group_id
912 ,p_revision_id => NULL
913 ,p_revision_code => l_eco_obj.eco_revised_item_type(i).new_revised_item_revision
914 ,p_uda_input_obj => l_eco_obj.eco_revised_item_type(i).item.uda_type
915 ,p_commit => fnd_api.g_false
916 ,x_uda_output_obj => l_item_output
917 );
918 END IF;
919
920 IF (l_item_output.output_status.return_status <> FND_API.g_ret_sts_success) THEN
921 x_out.output_status.return_status := l_item_output.output_status.msg_data;
922 RAISE FND_API.g_exc_unexpected_error;
923 END IF;
924
925 l_uda_output := inv_ebi_uda_output_obj(l_item_output.uda_output.failed_row_id_list,l_item_output.uda_output.errorcode);
926 -- To process Component level udas and structure header level udas
927 INV_EBI_CHANGE_ORDER_HELPER.process_change_order_uda(
928 p_commit => FND_API.g_false
929 ,p_organization_code => l_eco_obj.eco_change_order_type.organization_code
930 ,p_eco_name => l_eco_obj.eco_change_order_type.eco_name
931 ,p_alternate_bom_code => l_eco_obj.eco_revised_item_type(i).alternate_bom_code
932 ,p_revised_item_name => l_eco_obj.eco_revised_item_type(i).revised_item_name
933 ,p_component_tbl => l_eco_obj.eco_revised_item_type(i).component_item_tbl
934 ,p_structure_header => l_eco_obj.eco_revised_item_type(i).structure_header
935 ,x_out => l_out
936 );
937 IF (l_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
938 x_out.output_status.return_status := l_out.output_status.msg_data;
939 RAISE FND_API.g_exc_unexpected_error;
940 END IF;
941 END LOOP;
942 END IF;
943 l_uda_output := inv_ebi_uda_output_obj(l_out.uda_output.failed_row_id_list,l_out.uda_output.errorcode);
944 END IF;
945 --End of Udas processing
946
947 --Revised Item updation should be done after eco creation Bug 7025686
948 FOR j in 1..l_item_obj_tbl.COUNT LOOP
949 l_update_item_index := l_update_item_tab(j);
950 INV_EBI_UTIL.debug_line('STEP: 180 START CALLING INV_EBI_ITEM_HELPER.sync_item');
951 INV_EBI_ITEM_HELPER.sync_item(
952 p_commit => FND_API.g_false
953 ,p_operation => INV_EBI_ITEM_PUB.g_otype_update
954 ,p_item => l_item_obj_tbl(j).item_obj
955 ,x_out => l_item_output
956 );
957 INV_EBI_UTIL.debug_line('STEP: 190 END CALLING INV_EBI_ITEM_HELPER.sync_item');
958 l_item_output_tbl(l_update_item_index) := l_item_output;
959
960 IF (l_item_output.output_status.return_status <> fnd_api.g_ret_sts_success) THEN
961 x_out.output_status.msg_data := l_item_output.output_status.msg_data;
962 RAISE fnd_api.g_exc_unexpected_error;
963 END IF;
964 END LOOP;
965 INV_EBI_UTIL.debug_line('STEP: 200 START CALLING populate_revised_items_out');
966
967 --To populate revised item,componnet item,substitute components ids
968 populate_revised_items_out(
969 p_change_order => p_eco_obj.eco_change_order_type
970 ,p_revised_item_type_tbl => p_eco_obj.eco_revised_item_type
971 ,x_revised_item_type_tbl => l_revitem_output_tbl
972 );
973
974 x_out := inv_ebi_eco_output_obj(l_change_notice,l_change_id,l_organization_id,l_organization_code,
975 l_item_output.output_status,l_revitem_output_tbl,l_uda_output);
976 INV_EBI_UTIL.debug_line('STEP: 200 END CALLING populate_revised_items_out');
977 IF FND_API.to_boolean(p_commit) THEN
978 COMMIT;
979 END IF;
980 INV_EBI_UTIL.debug_line('STEP: 210 END INSIDE INV_EBI_CHANGE_ORDER_PUB.process_change_order');
981 EXCEPTION
982 WHEN FND_API.g_exc_unexpected_error THEN
983 ROLLBACK TO inv_ebi_chg_order_save_pnt;
984 IF c_effectivity_date%ISOPEN THEN
985 CLOSE c_effectivity_date;
986 END IF;
987 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
988 x_out.output_status.error_table := INV_EBI_UTIL.get_error_table();
989 IF (x_out.output_status.error_table IS NOT NULL AND x_out.output_status.error_table.COUNT > 0) THEN
990 x_out.output_status.msg_data := INV_EBI_UTIL.get_error_table_msgtxt(x_out.output_status.error_table);
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 END IF;
999 WHEN OTHERS THEN
1000 ROLLBACK TO inv_ebi_chg_order_save_pnt;
1001 IF c_effectivity_date%ISOPEN THEN
1002 CLOSE c_effectivity_date;
1003 END IF;
1004
1005 x_out.output_status.error_table := INV_EBI_UTIL.get_error_table();
1006 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
1007 IF (x_out.output_status.error_table IS NOT NULL AND x_out.output_status.error_table.COUNT > 0) THEN
1008 x_out.output_status.msg_data := INV_EBI_UTIL.get_error_table_msgtxt(x_out.output_status.error_table);
1009 END IF;
1010 IF (x_out.output_status.msg_data IS NOT NULL) THEN
1011 x_out.output_status.msg_data := x_out.output_status.msg_data||' -> INV_EBI_CHANGE_ORDER_PUB.process_change_order ';
1012 ELSE
1013 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_PUB.process_change_order ';
1014 END IF;
1015
1016 END process_change_order;
1017 /************************************************************************************
1018 -- API name : get_eco
1019 -- Type : Public
1020 -- Function :
1021 -- This API is used to retrieve all the change order attributes
1022 ************************************************************************************/
1023 PROCEDURE get_eco (
1024 p_change_id IN NUMBER
1025 ,p_last_update_status IN VARCHAR2
1026 ,p_revised_item_sequence_id IN NUMBER
1027 ,p_name_val_list IN inv_ebi_name_value_list
1028 ,x_eco_obj OUT NOCOPY inv_ebi_eco_obj
1029 ,x_return_status OUT NOCOPY VARCHAR2
1030 ,x_msg_count OUT NOCOPY NUMBER
1031 ,x_msg_data OUT NOCOPY VARCHAR2
1032 )
1033 IS
1034 l_eco_obj inv_ebi_eco_obj;
1035 l_return_status VARCHAR2(1);
1036 l_msg_count NUMBER;
1037 l_msg_data VARCHAR2(1000);
1038 l_eco_change_order_obj inv_ebi_change_order_obj;
1039 l_revised_item_tbl inv_ebi_revised_item_tbl;
1040 l_revised_item_obj inv_ebi_revised_item_obj;
1041 l_only_status_info VARCHAR2(1):= fnd_api.g_false;
1042 BEGIN
1043 x_return_status := FND_API.G_RET_STS_SUCCESS;
1044 l_eco_change_order_obj := inv_ebi_change_order_obj(
1045 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1046 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1047 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1048 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1049 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1050 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1051 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1052 );
1053 l_revised_item_tbl := inv_ebi_revised_item_tbl();
1054 l_revised_item_obj := inv_ebi_revised_item_obj(
1055 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1056 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1057 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1058 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1059 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1060 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1061 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1062 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1063 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
1064 ,NULL,NULL,NULL,NULL
1065 );
1066
1067 INV_EBI_CHANGE_ORDER_HELPER.get_eco (
1068 p_change_id => p_change_id
1069 ,p_last_update_status => p_last_update_status
1070 ,p_revised_item_sequence_id => p_revised_item_sequence_id
1071 ,p_name_val_list => p_name_val_list
1072 ,x_eco_obj => l_eco_obj
1073 ,x_return_status => l_return_status
1074 ,x_msg_count => l_msg_count
1075 ,x_msg_data => l_msg_data
1076 );
1077 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1078 RAISE FND_API.g_exc_unexpected_error;
1079 END IF;
1080 IF p_name_val_list.name_value_table IS NOT NULL THEN
1081 FOR i in p_name_val_list.name_value_table.FIRST..p_name_val_list.name_value_table.LAST LOOP
1082 IF UPPER(p_name_val_list.name_value_table(i).param_name) = G_ONLY_STATUS_INFO THEN
1083 l_only_status_info := p_name_val_list.name_value_table(i).param_value;
1084 END IF;
1085 END LOOP;
1086 END IF;
1087 IF l_only_status_info = fnd_api.g_true THEN
1088 l_eco_change_order_obj.status_name := l_eco_obj.eco_change_order_type.status_name;
1089 l_eco_change_order_obj.implementation_date := l_eco_obj.eco_change_order_type.implementation_date;
1090 l_eco_change_order_obj.cancellation_date := l_eco_obj.eco_change_order_type.cancellation_date;
1091 FOR i in l_eco_obj.eco_revised_item_type.FIRST..l_eco_obj.eco_revised_item_type.LAST LOOP
1092 l_revised_item_tbl.extend();
1093 l_revised_item_obj.status_name := l_eco_obj.eco_revised_item_type(i).status_name;
1094 l_revised_item_obj.new_effective_date := l_eco_obj.eco_revised_item_type(i).new_effective_date;
1095 l_revised_item_tbl(i) := l_revised_item_obj;
1096 END LOOP;
1097 x_eco_obj := inv_ebi_eco_obj(l_eco_change_order_obj,NULL,l_revised_item_tbl,NULL);
1098 ELSE
1099 x_eco_obj := l_eco_obj;
1100 END IF;
1101 EXCEPTION
1102 WHEN FND_API.g_exc_unexpected_error THEN
1103 x_return_status := FND_API.g_ret_sts_error;
1104 IF(x_msg_data IS NULL) THEN
1105 FND_MSG_PUB.count_and_get(
1106 p_encoded => FND_API.g_false
1107 ,p_count => x_msg_count
1108 ,p_data => x_msg_data
1109 );
1110 END IF;
1111 WHEN OTHERS THEN
1112 x_return_status := FND_API.g_ret_sts_error;
1113 IF (x_msg_data IS NOT NULL) THEN
1114 x_msg_data := x_msg_data ||' -> at inv_ebi_change_order_pub.get_eco';
1115 ELSE
1116 x_msg_data := SQLERRM||' at inv_ebi_change_order_pub.get_eco ';
1117 END IF;
1118 END get_eco;
1119
1120 /************************************************************************************
1121 -- API name : Convert_date_str_eco
1122 -- Type : Public
1123 -- Function :
1124 --
1125 ************************************************************************************/
1126
1127 PROCEDURE Convert_date_str_eco(p_eco_lst_obj IN inv_ebi_eco_out_obj_tbl
1128 ,x_eco_lst_obj OUT NOCOPY inv_ebi_eco_out_obj_tbl)
1129 IS
1130 BEGIN
1131
1132 x_eco_lst_obj := inv_ebi_eco_out_obj_tbl();
1133
1134 IF(p_eco_lst_obj IS NOT NULL AND p_eco_lst_obj.COUNT>0) THEN
1135 x_eco_lst_obj.EXTEND(p_eco_lst_obj.COUNT);
1136 x_eco_lst_obj := p_eco_lst_obj;
1137 END IF;
1138
1139 IF p_eco_lst_obj IS NOT NULL AND p_eco_lst_obj.COUNT>0 THEN
1140 FOR i IN p_eco_lst_obj.FIRST..p_eco_lst_obj.LAST LOOP
1141 ------------------------------------------------------------------------------
1142 -- To Convert Date Fields in eco_change_order_type (INV_EBI_CHANGE_ORDER_OBJ)
1143 ------------------------------------------------------------------------------
1144 x_eco_lst_obj(i).eco_attr.eco_change_order_type.approval_date_str :=
1145 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_change_order_type.approval_date);
1146
1147 x_eco_lst_obj(i).eco_attr.eco_change_order_type.approval_request_date_str :=
1148 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_change_order_type.approval_request_date);
1149
1150 x_eco_lst_obj(i).eco_attr.eco_change_order_type.need_by_date_str :=
1151 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_change_order_type.need_by_date);
1152
1153 x_eco_lst_obj(i).eco_attr.eco_change_order_type.implementation_date_str :=
1154 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_change_order_type.implementation_date);
1155
1156 x_eco_lst_obj(i).eco_attr.eco_change_order_type.cancellation_date_str :=
1157 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_change_order_type.cancellation_date);
1158 ------------------------------------------------------------------------------
1159 -- To Convert Date Fields in eco_change_order_type (INV_EBI_UDA_ATTR_OBJ)
1160 ------------------------------------------------------------------------------
1161
1162 IF(p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl IS NOT NULL AND
1163 p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl.COUNT>0) THEN
1164 FOR j IN p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl.FIRST..
1165 p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl.LAST LOOP
1166 IF(p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl(j).attributes_tbl IS NOT NULL
1167 AND p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl(j).attributes_tbl.COUNT>0) THEN
1168 FOR k IN p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl(j).attributes_tbl.FIRST..
1169 p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl(j).attributes_tbl.LAST LOOP
1170 x_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl(j).attributes_tbl(k).attr_value_date_str :=
1171 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl(j).attributes_tbl(k).attr_value_date);
1172 END LOOP;
1173 END IF;
1174 END LOOP;
1175 END IF;
1176
1177 ----------------------------------------------------------------------------
1178 -- To Convert Date Fields in eco_revised_item_type(INV_EBI_REVISED_ITEM_TBL)
1179 ----------------------------------------------------------------------------
1180 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type IS NOT NULL AND p_eco_lst_obj(i).eco_attr.eco_revised_item_type.COUNT>0) THEN
1181 FOR j IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type.FIRST..p_eco_lst_obj(i).eco_attr.eco_revised_item_type.LAST LOOP
1182 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).start_effective_date_str :=
1183 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).start_effective_date);
1184
1185 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).new_effective_date_str :=
1186 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).new_effective_date);
1187
1188 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).earliest_effective_date_str :=
1189 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).earliest_effective_date);
1190
1191 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).selection_date_str :=
1192 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).selection_date);
1193
1194
1195 -------------------------------------------------------
1196 -- Converting Dates For ORIGNAL_BOM_REFERENCE
1197 -------------------------------------------------------
1198
1199 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).orignal_bom_reference IS NOT NULL) THEN
1200 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).orignal_bom_reference.as_of_date_str :=
1201 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).orignal_bom_reference.as_of_date);
1202 END IF;
1203
1204
1205 -------------------------------------------------------
1206 -- Converting Dates For ITEM_REVISION_UDA
1207 -------------------------------------------------------
1208
1209 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda IS NOT NULL) THEN
1210 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda.attribute_group_tbl IS NOT NULL AND
1211 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda.attribute_group_tbl.COUNT >0) THEN
1212 FOR k IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda.attribute_group_tbl.FIRST..
1213 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda.attribute_group_tbl.LAST LOOP
1214 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda.attribute_group_tbl(k).attributes_tbl IS NOT NULL) THEN
1215 FOR l IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda.attribute_group_tbl(k).attributes_tbl.FIRST..
1216 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda.attribute_group_tbl(k).attributes_tbl.LAST LOOP
1217 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda.attribute_group_tbl(k).attributes_tbl(l).attr_value_date_str :=
1218 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda.attribute_group_tbl(k).attributes_tbl(l).attr_value_date);
1219 END LOOP;
1220 END IF;
1221 END LOOP;
1222 END IF;
1223 END IF;
1224
1225 ----------------------------------------------------------------------------------------------------------------
1226 -- Converting Dates For INV_EBI_ITEM_OBJ(inv_ebi_item_obj)
1227 ----------------------------------------------------------------------------------------------------------------
1228
1229
1230 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type IS NOT NULL) THEN
1231
1232 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.start_date_active_str :=
1233 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.start_date_active);
1234
1235 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.end_date_active_str :=
1236 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.end_date_active);
1237
1238 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.creation_date_str :=
1239 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.creation_date);
1240
1241 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.last_update_date_str :=
1242 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.last_update_date);
1243
1244 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.program_update_date_str :=
1245 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.program_update_date);
1246
1247 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.effectivity_date_str :=
1248 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.effectivity_date);
1249
1250 END IF;
1251
1252 ------------------------------------------------------
1253 -- Converting Dates For INV_EBI_ITEM_DEPRECATED_OBJ
1254 ------------------------------------------------------
1255
1256 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.deprecated_obj_type IS NOT NULL) THEN
1257
1258 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.deprecated_obj_type.engineering_date_str :=
1259 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.deprecated_obj_type.engineering_date);
1260
1261 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.deprecated_obj_type.wh_update_date_str :=
1262 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.deprecated_obj_type.wh_update_date_str);
1263
1264 END IF;
1265
1266 --------------------------------------------------------
1267 -- Converting Dates For INV_EBI_UDA_INPUT_OBJ
1268 --------------------------------------------------------
1269
1270 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl IS NOT NULL AND
1271 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl.COUNT>0) THEN
1272 FOR k IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl.FIRST..
1273 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl.LAST LOOP
1274 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl(k).attributes_tbl IS NOT NULL AND
1275 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl(k).attributes_tbl.COUNT>0) THEN
1276 FOR l IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl(k).attributes_tbl.FIRST..
1277 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl(k).attributes_tbl.LAST LOOP
1278 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl(k).attributes_tbl(l).attr_value_date_str :=
1279 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl(k).attributes_tbl(l).attr_value_date);
1280 END LOOP;
1281 END IF;
1282 END LOOP;
1283 END IF;
1284
1285
1286 ------------------------------------------------------------------------------
1287 -- Converting Dates For structure_header (INV_EBI_STRUCTURE_HEADER_OBJ)
1288 ------------------------------------------------------------------------------
1289
1290 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).structure_header.structure_header_uda.attribute_group_tbl IS NOT NULL AND
1291 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).structure_header.structure_header_uda.attribute_group_tbl.COUNT>0) THEN
1292 FOR k IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).structure_header.structure_header_uda.attribute_group_tbl.FIRST..
1293 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).structure_header.structure_header_uda.attribute_group_tbl.LAST LOOP
1294 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).structure_header.structure_header_uda.attribute_group_tbl(k).attributes_tbl IS NOT NULL AND
1295 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).structure_header.structure_header_uda.attribute_group_tbl(k).attributes_tbl.COUNT>0) THEN
1296 FOR l IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).structure_header.structure_header_uda.attribute_group_tbl(k).attributes_tbl.FIRST..
1297 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).structure_header.structure_header_uda.attribute_group_tbl(k).attributes_tbl.LAST LOOP
1298 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).structure_header.structure_header_uda.attribute_group_tbl(k).attributes_tbl(l).attr_value_date_str :=
1299 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).structure_header.structure_header_uda.attribute_group_tbl(k).attributes_tbl(l).attr_value_date);
1300 END LOOP;
1301 END IF;
1302 END LOOP;
1303 END IF;
1304
1305 --------------------------------------------------------------------------------
1306 -- Converting Dates For component_item_tbl (INV_EBI_REV_COMP_TBL)
1307 --------------------------------------------------------------------------------
1308
1309 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl IS NOT NULL AND
1310 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl.COUNT>0 ) THEN
1311 FOR k IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl.FIRST..
1312 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl.LAST LOOP
1313
1314 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).start_effective_date_str :=
1315 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).start_effective_date);
1316
1317 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).new_effectivity_date_str :=
1318 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).new_effectivity_date);
1319
1320 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).disable_date_str :=
1321 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).disable_date);
1322
1323 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).old_effectivity_date_str :=
1324 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).old_effectivity_date);
1325
1326 --------------------------------------------------------------------------
1327 -- Converting Dates For substitute_component_tbl( INV_EBI_SUB_COMP_TBL )
1328 --------------------------------------------------------------------------
1329
1330 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).substitute_component_tbl IS NOT NULL AND
1331 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).substitute_component_tbl.COUNT>0) THEN
1332 FOR l IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).substitute_component_tbl.FIRST..
1333 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).substitute_component_tbl.LAST LOOP
1334 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).substitute_component_tbl(l).start_effective_date_str :=
1335 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).substitute_component_tbl(l).start_effective_date);
1336 END LOOP;
1337 END IF;
1338
1339 ----------------------------------------------------------------------------
1340 -- Converting Dates for reference_designator_tbl( INV_EBI_REF_DESG_TBL )
1341 ----------------------------------------------------------------------------
1342
1343 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).reference_designator_tbl IS NOT NULL AND
1344 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).reference_designator_tbl.COUNT>0) THEN
1345 FOR l IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).reference_designator_tbl.FIRST..
1346 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).reference_designator_tbl.LAST LOOP
1347 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).reference_designator_tbl(l).start_effective_date_str :=
1348 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).reference_designator_tbl(l).start_effective_date);
1349 END LOOP;
1350 END IF;
1351
1352 ----------------------------------------------------------------------------
1353 -- Converting Dates for component_revision_uda ( INV_EBI_UDA_INPUT_OBJ )
1354 ----------------------------------------------------------------------------
1355
1356 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).component_revision_uda.attribute_group_tbl IS NOT NULL AND
1357 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).component_revision_uda.attribute_group_tbl.COUNT>0) THEN
1358 FOR l IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).component_revision_uda.attribute_group_tbl.FIRST..
1359 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).component_revision_uda.attribute_group_tbl.LAST LOOP
1360 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).component_revision_uda.attribute_group_tbl(l).attributes_tbl IS NOT NULL AND
1361 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).component_revision_uda.attribute_group_tbl(l).attributes_tbl.COUNT>0) THEN
1362 FOR m IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).component_revision_uda.attribute_group_tbl(l).attributes_tbl.FIRST..
1363 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).component_revision_uda.attribute_group_tbl(l).attributes_tbl.LAST LOOP
1364 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).component_revision_uda.attribute_group_tbl(l).attributes_tbl(m).attr_value_date_str :=
1365 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).component_revision_uda.attribute_group_tbl(l).attributes_tbl(m).attr_value_date);
1366 END LOOP;
1367 END IF;
1368 END LOOP;
1369 END IF;
1370
1371 --------------------------------------------------------------------------------
1372 -- Converting Dates For revision_operation_tbl ( INV_EBI_REV_OP_TBL )
1373 --------------------------------------------------------------------------------
1374
1375 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl IS NOT NULL AND
1376 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl.COUNT>0) THEN
1377 FOR k IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl.FIRST..
1378 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl.LAST LOOP
1379
1380 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl(k).start_effective_date_str :=
1381 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl(k).start_effective_date);
1382
1383 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl(k).old_start_effective_number_str :=
1384 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl(k).old_start_effective_number);
1385
1386 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl(k).disable_number_str :=
1387 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl(k).disable_number);
1388 END LOOP;
1389 END IF;
1390
1391 ----------------------------------------------------------------------------------
1392 -- Converting Dates For revision_op_resource_tbl (INV_EBI_REV_OP_RES_TBL)
1393 ----------------------------------------------------------------------------------
1394
1395 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_op_resource_tbl IS NOT NULL AND
1396 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_op_resource_tbl.COUNT>0) THEN
1397 FOR k IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_op_resource_tbl.FIRST..
1398 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_op_resource_tbl.LAST LOOP
1399 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_op_resource_tbl(k).op_start_effective_date_str :=
1400 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_op_resource_tbl(k).op_start_effective_date);
1401 END LOOP;
1402 END IF;
1403
1404 ----------------------------------------------------------------------------------------------------------------
1405 -- Converting Dates For revision_sub_resource_tbl (INV_EBI_REV_SUB_RES_TBL)
1406 ----------------------------------------------------------------------------------------------------------------
1407
1408 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_sub_resource_tbl IS NOT NULL AND
1409 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_sub_resource_tbl.COUNT>0) THEN
1410 FOR k IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_sub_resource_tbl.FIRST..
1411 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_sub_resource_tbl.LAST LOOP
1412 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_sub_resource_tbl(k).op_start_effective_date_str :=
1413 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_sub_resource_tbl(k).op_start_effective_date);
1414 END LOOP;
1415 END IF;
1416 END LOOP;
1417 END IF;
1418 END LOOP;
1419 END IF;
1420 END LOOP;
1421 END IF;
1422 EXCEPTION
1423 WHEN OTHERS THEN
1424 x_eco_lst_obj := p_eco_lst_obj;
1425 END Convert_date_str_eco;
1426
1427 /************************************************************************************
1428 -- API name : get_eco_list_attr
1429 -- Type : Public
1430 -- Function : This API to retrive change order for multiple change id
1431 --
1432 ************************************************************************************/
1433 PROCEDURE get_eco_list_attr(
1434 p_change_lst IN inv_ebi_change_id_obj_tbl
1435 ,p_name_val_list IN inv_ebi_name_value_list
1436 ,x_eco_lst_obj OUT NOCOPY inv_ebi_eco_out_obj_tbl
1437 ,x_return_status OUT NOCOPY VARCHAR2
1438 ,x_msg_count OUT NOCOPY NUMBER
1439 ,x_msg_data OUT NOCOPY VARCHAR2
1440 )
1441 IS
1442 l_msg_data VARCHAR2(32000);
1443 l_return_status VARCHAR2(1);
1444 l_ovrflw_msg VARCHAR2(1):='N';
1445 l_count NUMBER := 0;
1446 l_part_msgtxt VARCHAR2(32000);
1447 l_name_val_list inv_ebi_name_value_list;
1448 l_eco_obj inv_ebi_eco_obj;
1449 l_eco_obj_tbl inv_ebi_eco_out_obj_tbl;
1450 l_eco_lst_obj inv_ebi_eco_out_obj;
1451 l_mult_org_chg_id_temp inv_ebi_change_id_obj_tbl;
1452 l_mult_org_chg_id_tbl inv_ebi_change_id_obj_tbl;
1453 l_eco_output_tbl_lst inv_ebi_change_id_obj_tbl;
1454
1455 CURSOR c_get_multi_org_chg_id(p_chg_id NUMBER)
1456 IS
1457 SELECT inv_ebi_change_id_obj(geco.change_id,'N')
1458 FROM (SELECT eec1.change_id
1459 FROM eng_engineering_changes eec,eng_engineering_changes eec1
1460 where eec.change_id=p_chg_id
1461 and eec.change_notice=eec1.change_notice
1462 MINUS
1463 SELECT b.change_id
1464 FROM THE (SELECT CAST(p_change_lst as inv_ebi_change_id_obj_tbl)
1465 FROM dual) b ) geco;
1466
1467 CURSOR c_get_final_eco_list IS
1468 SELECT inv_ebi_change_id_obj(geco.change_id,geco.last_update_status)
1469 FROM (SELECT b.change_id,b.last_update_status
1470 FROM THE (SELECT CAST( p_change_lst as inv_ebi_change_id_obj_tbl)
1471 FROM dual ) b
1472 UNION
1473 SELECT c.change_id,c.last_update_status
1474 FROM THE (SELECT CAST( l_mult_org_chg_id_tbl as inv_ebi_change_id_obj_tbl)
1475 FROM dual ) c ) geco;
1476 BEGIN
1477 l_eco_obj_tbl := inv_ebi_eco_out_obj_tbl();
1478 l_name_val_list := p_name_val_list;
1479 x_return_status := FND_API.G_RET_STS_SUCCESS;
1480 l_mult_org_chg_id_tbl := inv_ebi_change_id_obj_tbl();
1481 l_mult_org_chg_id_temp := inv_ebi_change_id_obj_tbl();
1482
1483 INV_EBI_ITEM_HELPER.set_server_time_zone;
1484
1485 IF p_change_lst IS NOT NULL AND p_change_lst.COUNT > 0 THEN
1486 FOR i in 1..p_change_lst.COUNT LOOP
1487 OPEN c_get_multi_org_chg_id(p_change_lst(i).change_id);
1488 FETCH c_get_multi_org_chg_id BULK COLLECT INTO l_mult_org_chg_id_temp;
1489 IF l_mult_org_chg_id_temp IS NOT NULL AND l_mult_org_chg_id_temp.COUNT>0 THEN
1490 l_mult_org_chg_id_tbl.EXTEND(l_mult_org_chg_id_temp.COUNT);
1491 l_mult_org_chg_id_tbl := l_mult_org_chg_id_temp;
1492 END IF;
1493 CLOSE c_get_multi_org_chg_id;
1494 END LOOP;
1495 END IF;
1496
1497 IF c_get_final_eco_list%ISOPEN THEN
1498 CLOSE c_get_final_eco_list;
1499 END IF;
1500 OPEN c_get_final_eco_list;
1501 FETCH c_get_final_eco_list BULK COLLECT INTO l_eco_output_tbl_lst;
1502 CLOSE c_get_final_eco_list;
1503 IF (l_eco_output_tbl_lst IS NOT NULL) THEN
1504 FOR l_cnt_cid IN 1..l_eco_output_tbl_lst.COUNT LOOP
1505 INV_EBI_CHANGE_ORDER_HELPER.get_eco (
1506 p_change_id => l_eco_output_tbl_lst(l_cnt_cid).change_id
1507 ,p_last_update_status => l_eco_output_tbl_lst(l_cnt_cid).last_update_status
1508 ,p_revised_item_sequence_id => NULL
1509 ,p_name_val_list => l_name_val_list
1510 ,x_eco_obj => l_eco_obj
1511 ,x_return_status => l_return_status
1512 ,x_msg_count => l_count
1513 ,x_msg_data => l_msg_data
1514 );
1515 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1516 x_return_status := FND_API.g_ret_sts_error;
1517 l_part_msgtxt := ' Change Id: '|| p_change_lst(l_cnt_cid).change_id||' Err Msg: '||l_msg_data ;
1518 IF (x_msg_data IS NOT NULL) THEN
1519 IF (LENGTH(x_msg_data||' , '||l_part_msgtxt) < 31000) THEN
1520 x_msg_data :=x_msg_data||' , '||l_part_msgtxt;
1521 ELSE
1522 l_ovrflw_msg :='Y';
1523 EXIT;
1524 END IF;
1525 ELSE
1526 x_msg_data := l_part_msgtxt;
1527 END IF;
1528 END IF;
1529 l_eco_lst_obj := inv_ebi_eco_out_obj(l_eco_obj,inv_ebi_output_status(l_return_status,l_count,l_msg_data,NULL));
1530 l_eco_obj_tbl.EXTEND();
1531 l_eco_obj_tbl(l_cnt_cid) := l_eco_lst_obj;
1532 END LOOP;
1533 IF (l_ovrflw_msg='Y') AND SUBSTR(x_msg_data,length(x_msg_data)-2) <> '...' THEN
1534 x_msg_data :=x_msg_data||' , '||'...';
1535 END IF;
1536 END IF;
1537
1538 IF (l_eco_obj_tbl IS NOT NULL AND l_eco_obj_tbl.COUNT > 0) THEN
1539 x_eco_lst_obj := l_eco_obj_tbl;
1540 Convert_date_str_eco(p_eco_lst_obj => l_eco_obj_tbl
1541 ,x_eco_lst_obj => x_eco_lst_obj);
1542 END IF;
1543
1544 EXCEPTION
1545 WHEN OTHERS THEN
1546 x_return_status := FND_API.G_RET_STS_ERROR;
1547 IF (x_msg_data IS NOT NULL) THEN
1548 x_msg_data := x_msg_data ||' -> at INV_EBI_CHANGE_ORDER_PUB.get_eco_list_attr';
1549 ELSE
1550 x_msg_data := SQLERRM||' at INV_EBI_CHANGE_ORDER_PUB.get_eco_list_attr ';
1551 END IF;
1552 END get_eco_list_attr;
1553
1554 /************************************************************************************
1555 -- API name : process_change_order_list
1556 -- Type : Public
1557 -- Function :
1558 -- This API is used to get list of change orders
1559 --
1560 ************************************************************************************/
1561
1562 PROCEDURE process_change_order_list(
1563 p_commit IN VARCHAR2 := fnd_api.g_false
1564 ,p_eco_obj_list IN inv_ebi_eco_obj_tbl
1565 ,x_out OUT NOCOPY inv_ebi_eco_output_obj_tbl
1566 ,x_return_status OUT NOCOPY VARCHAR2
1567 ,x_msg_count OUT NOCOPY NUMBER
1568 ,x_msg_data OUT NOCOPY VARCHAR2
1569 )
1570 IS
1571 l_inv_ebi_eco_obj inv_ebi_eco_obj;
1572 l_inv_ebi_eco_output_obj inv_ebi_eco_output_obj;
1573 l_part_err_msg VARCHAR2(32000);
1574 l_eco_name VARCHAR2(10);
1575 l_org_code VARCHAR2(3);
1576 l_is_master_org VARCHAR2(1);
1577 BEGIN
1578 SAVEPOINT inv_ebi_prc_chg_ord_save_pnt;
1579 ERROR_HANDLER.Initialize;
1580 FND_MSG_PUB.initialize;
1581 INV_EBI_UTIL.setup();
1582 INV_EBI_UTIL.debug_line('STEP: 10 START INSIDE INV_EBI_CHANGE_ORDER_PUB.process_change_order_list');
1583 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1584 x_out := inv_ebi_eco_output_obj_tbl();
1585 IF (p_eco_obj_list IS NOT NULL AND p_eco_obj_list.count > 0) THEN
1586 x_out.extend(p_eco_obj_list.count);
1587 FOR i in 1..p_eco_obj_list.count
1588 LOOP
1589 l_inv_ebi_eco_obj := p_eco_obj_list(i);
1590 l_eco_name := p_eco_obj_list(i).eco_change_order_type.eco_name;
1591 l_org_code := p_eco_obj_list(i).eco_change_order_type.organization_code;
1592 l_is_master_org := INV_EBI_UTIL.is_master_org(l_org_code);
1593 IF( l_is_master_org = fnd_api.g_true ) THEN
1594 INV_EBI_UTIL.debug_line('STEP: 20 START CALLING INV_EBI_CHANGE_ORDER_PUB.process_change_order for Master ORG'||
1595 ' Organization Code: '||p_eco_obj_list(i).eco_change_order_type.organization_code||
1596 ' ECO Name: '||p_eco_obj_list(i).eco_change_order_type.eco_name);
1597
1598 INV_EBI_CHANGE_ORDER_PUB.process_change_order( p_commit => p_commit
1599 ,p_eco_obj => l_inv_ebi_eco_obj
1600 ,x_out => l_inv_ebi_eco_output_obj
1601 );
1602 INV_EBI_UTIL.debug_line('STEP: 30 END CALLING INV_EBI_CHANGE_ORDER_PUB.process_change_order for Master ORG'||
1603 'Organization Code: '||p_eco_obj_list(i).eco_change_order_type.organization_code||
1604 'ECO Name: '||p_eco_obj_list(i).eco_change_order_type.eco_name);
1605 x_out(i) := l_inv_ebi_eco_output_obj;
1606 IF x_out(i).output_status.return_status <> FND_API.G_RET_STS_SUCCESS THEN
1607 RAISE FND_API.g_exc_unexpected_error;
1608 END IF;
1609 END IF;
1610 END LOOP;
1611 END IF;
1612 IF (p_eco_obj_list IS NOT NULL AND p_eco_obj_list.count > 0) THEN
1613 FOR i in 1..p_eco_obj_list.count
1614 LOOP
1615 l_inv_ebi_eco_obj := p_eco_obj_list(i);
1616 l_eco_name := p_eco_obj_list(i).eco_change_order_type.eco_name;
1617 l_org_code := p_eco_obj_list(i).eco_change_order_type.organization_code;
1618 l_is_master_org := INV_EBI_UTIL.is_master_org(l_org_code);
1619 IF( l_is_master_org = fnd_api.g_false ) THEN
1620 INV_EBI_UTIL.debug_line('STEP: 40 START CALLING INV_EBI_CHANGE_ORDER_PUB.process_change_order for Child ORG'||
1621 ' Organization Code: '||p_eco_obj_list(i).eco_change_order_type.organization_code||
1622 ' ECO Name: '||p_eco_obj_list(i).eco_change_order_type.eco_name);
1623 INV_EBI_CHANGE_ORDER_PUB.process_change_order( p_commit => p_commit
1624 ,p_eco_obj => l_inv_ebi_eco_obj
1625 ,x_out => l_inv_ebi_eco_output_obj
1626 );
1627 INV_EBI_UTIL.debug_line('STEP: 50 END CALLING INV_EBI_CHANGE_ORDER_PUB.process_change_order for Child ORG'||
1628 ' Organization Code: '||p_eco_obj_list(i).eco_change_order_type.organization_code||
1629 ' ECO Name: '||p_eco_obj_list(i).eco_change_order_type.eco_name);
1630 x_out(i) := l_inv_ebi_eco_output_obj;
1631 IF x_out(i).output_status.return_status <> FND_API.G_RET_STS_SUCCESS THEN
1632 RAISE FND_API.g_exc_unexpected_error;
1633 END IF;
1634 END IF;
1635 END LOOP;
1636 END IF;
1637
1638 INV_EBI_UTIL.debug_line('STEP: 60 END INSIDE INV_EBI_CHANGE_ORDER_PUB.process_change_order_list');
1639 INV_EBI_UTIL.wrapup;
1640 EXCEPTION
1641 WHEN FND_API.g_exc_unexpected_error THEN
1642 ROLLBACK TO inv_ebi_prc_chg_ord_save_pnt;
1643 x_return_status := FND_API.G_RET_STS_ERROR;
1644 IF(x_out IS NOT NULL AND x_out.COUNT > 0) THEN
1645 FOR i in 1..x_out.COUNT
1646 LOOP
1647 x_msg_count := x_out(i).output_status.msg_count;
1648 IF x_out(i).output_status.msg_data IS NOT NULL THEN
1649 x_msg_data := x_msg_data ||'Org Code: '||l_org_code||' ECO Name: '||l_eco_name || ' Err Msg: ' || x_out(i).output_status.msg_data;
1650 END IF;
1651 END LOOP;
1652 END IF;
1653 WHEN OTHERS THEN
1654 x_out.EXTEND();
1655 x_out(1).output_status.return_status := FND_API.g_ret_sts_unexp_error;
1656 IF (x_out(1).output_status.msg_data IS NOT NULL) THEN
1657 x_out(1).output_status.msg_data := x_out(1).output_status.msg_data||' -> INV_EBI_CHANGE_ORDER_PUB.process_change_order_list';
1658 ELSE
1659 x_out(1).output_status.msg_data := SQLERRM|| 'INV_EBI_CHANGE_ORDER_PUB.process_change_order_list';
1660 END IF;
1661 x_return_status := FND_API.G_RET_STS_ERROR;
1662 x_msg_count := x_out(1).output_status.msg_count;
1663 IF (x_out IS NOT NULL AND x_out.COUNT > 0) THEN
1664 FOR i in 1..x_out.COUNT
1665 LOOP
1666 IF x_out(i).output_status.msg_data IS NOT NULL THEN
1667 x_msg_data := x_msg_data||' Org Code: '||l_org_code||' ECO Name: '||l_eco_name || ' Err Msg: ' || x_out(i).output_status.msg_data;
1668 END IF;
1669 END LOOP;
1670 END IF;
1671 END process_change_order_list;
1672
1673 END INV_EBI_CHANGE_ORDER_PUB;