[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.67.12020000.2 2012/07/19 05:00:36 smukka 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_item_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_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL,NULL);
22 INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_CHANGE_ORDER_PUB.populate_item_attributes');
23 IF(p_revised_item.item IS NOT NULL ) THEN
24 x_item := p_revised_item.item;
25 ELSE
26 l_main_item_obj := inv_ebi_item_main_obj(fnd_api.g_miss_num,fnd_api.g_miss_char,
27 fnd_api.g_miss_char,fnd_api.g_miss_num,
28 fnd_api.g_miss_char,fnd_api.g_miss_num,
29 fnd_api.g_miss_num,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_char,
32 fnd_api.g_miss_char,fnd_api.g_miss_num,
33 fnd_api.g_miss_num,fnd_api.g_miss_char,
34 fnd_api.g_miss_char,fnd_api.g_miss_num,
35 fnd_api.g_miss_num,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_char,
48 fnd_api.g_miss_char,fnd_api.g_miss_date,
49 fnd_api.g_miss_date,fnd_api.g_miss_date,
50 fnd_api.g_miss_num,fnd_api.g_miss_date,
51 fnd_api.g_miss_num,fnd_api.g_miss_num,
52 fnd_api.g_miss_num,fnd_api.g_miss_num,
53 fnd_api.g_miss_num,fnd_api.g_miss_date,
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_num,fnd_api.g_miss_char,
57 fnd_api.g_miss_char,fnd_api.g_miss_char,
58 fnd_api.g_miss_date,fnd_api.g_miss_num,
59 fnd_api.g_miss_char,fnd_api.g_miss_num,
60 fnd_api.g_miss_char,fnd_api.g_miss_char,
61 fnd_api.g_miss_num,fnd_api.g_miss_char,
62 fnd_api.g_miss_num,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,fnd_api.g_miss_char,
67 fnd_api.g_miss_char,
68 NULL -- Added for Bug 13370136 -- ramukher
69 );
70 x_item:=inv_ebi_Item_Obj(l_main_item_obj,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
71 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
72 END IF;
73 IF(x_item.main_obj_type.item_number IS NULL OR x_item.main_obj_type.item_number = fnd_api.g_miss_char )THEN
74 x_item.main_obj_type.item_number := p_revised_item.revised_item_name;
75 INV_EBI_UTIL.debug_line('STEP 20: ITEM NUMBER: '|| x_item.main_obj_type.item_number);
76 END IF;
77 IF(x_item.main_obj_type.organization_id IS NULL OR x_item.main_obj_type.organization_id = fnd_api.g_miss_num )THEN
78 IF((p_change_order_obj.organization_id IS NULL
79 OR p_change_order_obj.organization_id = fnd_api.g_miss_num)
80 AND (p_change_order_obj.organization_code IS NOT NULL
81 OR p_change_order_obj.organization_code <> fnd_api.g_miss_char)) THEN
82 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
83 l_pk_col_name_val_pairs.EXTEND(1);
84 l_pk_col_name_val_pairs(1).name := 'organization_code';
85 l_pk_col_name_val_pairs(1).value := p_change_order_obj.organization_code;
86 x_item.main_obj_type.organization_id := INV_EBI_ITEM_HELPER.value_to_id(
87 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
88 ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
89 );
90 INV_EBI_UTIL.debug_line('STEP 30: ORG ID: '|| x_item.main_obj_type.organization_id);
91 l_pk_col_name_val_pairs.TRIM(1);
92 IF (x_item.main_obj_type.organization_id IS NULL) THEN
93 FND_MESSAGE.set_name('INV','INV_EBI_ORG_CODE_INVALID');
94 FND_MESSAGE.set_token('COL_VALUE', p_change_order_obj.organization_code);
95 FND_MSG_PUB.add;
96 RAISE FND_API.G_EXC_ERROR;
97 END IF;
98 ELSE
99 x_item.main_obj_type.organization_id := p_change_order_obj.organization_id;
100 END IF;
101 END IF;
102
103 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
104 x_item.main_obj_type.init_msg_list := FND_API.G_TRUE;
105 END IF;
106 INV_EBI_UTIL.debug_line('STEP 40: END INSIDE INV_EBI_CHANGE_ORDER_PUB.populate_item_attributes STATUS: '||x_out.output_status.return_status);
107 EXCEPTION
108 WHEN FND_API.g_exc_error THEN
109 x_out.output_status.return_status := FND_API.g_ret_sts_error;
110 IF(x_out.output_status.msg_data IS NULL) THEN
111 FND_MSG_PUB.count_and_get(
112 p_encoded => FND_API.g_false
113 ,p_count => x_out.output_status.msg_count
114 ,p_data => x_out.output_status.msg_data
115 );
116 END IF;
117 WHEN OTHERS THEN
118 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
119 IF (x_out.output_status.msg_data IS NOT NULL) THEN
120 x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_CHANGE_ORDER_PUB.populate_item_attributes ';
121 ELSE
122 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_PUB.populate_item_attributes ';
123 END IF;
124 END populate_item_attributes;
125
126 /************************************************************************************
127 -- API name : transfer_engg_item_mfg
128 -- Type : Private
129 -- Function :
130 -- This API is used to
131 --
132 ************************************************************************************/
133 PROCEDURE transfer_engg_item_mfg(
134 p_item IN inv_ebi_item_obj
135 ,p_alt_bom_designator IN VARCHAR2
136 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
137 ) IS
138 l_item_approval_status VARCHAR2(30);
139 l_item_number VARCHAR2(2000);
140 l_is_engineering_item VARCHAR2(3);
141 l_output_status inv_ebi_output_status;
142 l_designator_option NUMBER;
143 l_alt_bom_designator VARCHAR2(10);
144 l_inventory_item_id NUMBER;
145 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
146
147 BEGIN
148 SAVEPOINT inv_ebi_engg_item_save_pnt;
149 INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_CHANGE_ORDER_PUB.transfer_engg_item_mfg');
150 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
151 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
152
153 IF(p_item.main_obj_type.inventory_item_id IS NULL OR
154 p_item.main_obj_type.inventory_item_id = fnd_api.g_miss_num) THEN
155
156 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
157 l_pk_col_name_val_pairs.EXTEND(2);
158 l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
159 l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.item_number;
160 l_pk_col_name_val_pairs(2).name := 'organization_id';
161 l_pk_col_name_val_pairs(2).value := p_item.main_obj_type.organization_id;
162 INV_EBI_UTIL.debug_line('STEP 20: ITEM NUMBER: '|| p_item.main_obj_type.item_number || ' ORG ID: '|| p_item.main_obj_type.organization_id);
163 l_inventory_item_id := INV_EBI_ITEM_HELPER.value_to_id(
164 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
165 ,p_entity_name => INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
166 );
167 INV_EBI_UTIL.debug_line('STEP 30: INV ITEM ID '|| l_inventory_item_id);
168 l_pk_col_name_val_pairs.TRIM(2);
169 IF (l_inventory_item_id IS NULL ) THEN
170 FND_MESSAGE.set_name('INV','INV_EBI_ITEM_INVALID');
171 FND_MESSAGE.set_token('COL_VALUE', p_item.main_obj_type.item_number);
172 FND_MSG_PUB.add;
173 RAISE FND_API.g_exc_unexpected_error;
174 END IF;
175 ELSE
176 l_inventory_item_id := p_item.main_obj_type.inventory_item_id;
177
178 END IF;
179
180 IF((l_inventory_item_id IS NOT NULL
181 AND l_inventory_item_id <> fnd_api.g_miss_num)
182 AND (p_item.main_obj_type.organization_id IS NOT NULL
183 AND p_item.main_obj_type.organization_id <> fnd_api.g_miss_num)) THEN
184
185 SELECT approval_status INTO l_item_approval_status
186 FROM mtl_system_items_b
187 WHERE inventory_item_id = l_inventory_item_id
188 AND organization_id = p_item.main_obj_type.organization_id;
189
190 END IF;
191
192 /* Start of Bug 8299853 FDD says if CO item is not new and approval status of the item is not approved
193 we should error out */
194
195 IF(NVL(l_item_approval_status, 'A') <> 'A') THEN
196
197 FND_MESSAGE.set_name('INV','INV_EBI_ITEM_NOT_APPROVED');
198 FND_MESSAGE.set_token('ITEM', p_item.main_obj_type.item_number);
199 FND_MSG_PUB.add;
200 RAISE FND_API.g_exc_error;
201
202 ELSE --End of Bug 8299853
203
204 l_is_engineering_item := INV_EBI_ITEM_HELPER.is_engineering_item(
205 p_organization_id => p_item.main_obj_type.organization_id
206 ,p_item_number => p_item.main_obj_type.item_number
207 );
208
209 IF(l_is_engineering_item = FND_API.g_true
210 AND p_item.bom_obj_type.eng_item_flag = 'N'
211 ) THEN
212
213 --Transfer Engg Item to Manufacturing
214 -- If alt_bom_code is null then transfer all boms,if not transfer only that particular bom
215
216 IF(p_alt_bom_designator IS NULL OR p_alt_bom_designator = fnd_api.g_miss_char ) THEN
217 l_designator_option := 1;
218 l_alt_bom_designator := NULL;
219 ELSE
220 l_designator_option := 3;
221 l_alt_bom_designator := p_alt_bom_designator;
222 END IF;
223 INV_EBI_UTIL.debug_line('STEP 40: BEFORE CALLING ENG_BOM_RTG_TRANSFER_PKG.eng_bom_rtg_transfer');
224 ENG_BOM_RTG_TRANSFER_PKG.eng_bom_rtg_transfer(
225 x_org_id => p_item.main_obj_type.organization_id,
226 x_eng_item_id => l_inventory_item_id,
227 x_mfg_item_id => l_inventory_item_id,
228 x_transfer_option => 1,
229 x_designator_option => l_designator_option,
230 x_alt_bom_designator => l_alt_bom_designator,
231 x_alt_rtg_designator => NULL,
232 x_effectivity_date => NULL,
233 x_last_login_id => p_item.main_obj_type.last_updated_by,
234 x_bom_rev_starting => NULL,
235 x_rtg_rev_starting => NULL,
236 x_ecn_name => NULL,
237 x_item_code => 1, --to transfer item to Mfg
238 x_bom_code => 1, --to transfer Bom to Mfg
239 x_rtg_code => 2,
240 x_mfg_description => p_item.main_obj_type.description,
241 x_segment1 => p_item.main_obj_type.segment1,
242 x_segment2 => p_item.main_obj_type.segment2,
243 x_segment3 => p_item.main_obj_type.segment3,
244 x_segment4 => p_item.main_obj_type.segment4,
245 x_segment5 => p_item.main_obj_type.segment5,
246 x_segment6 => p_item.main_obj_type.segment6,
247 x_segment7 => p_item.main_obj_type.segment7,
248 x_segment8 => p_item.main_obj_type.segment8,
249 x_segment9 => p_item.main_obj_type.segment9,
250 x_segment10 => p_item.main_obj_type.segment10,
251 x_segment11 => p_item.main_obj_type.segment11,
252 x_segment12 => p_item.main_obj_type.segment12,
253 x_segment13 => p_item.main_obj_type.segment13,
254 x_segment14 => p_item.main_obj_type.segment14,
255 x_segment15 => p_item.main_obj_type.segment15,
256 x_segment16 => p_item.main_obj_type.segment16,
257 x_segment17 => p_item.main_obj_type.segment17,
258 x_segment18 => p_item.main_obj_type.segment18,
259 x_segment19 => p_item.main_obj_type.segment19,
260 x_segment20 => p_item.main_obj_type.segment20,
261 x_implemented_only => NULL,
262 x_commit => FALSE
263 );
264 END IF;
265 INV_EBI_UTIL.debug_line('STEP 50: AFTER CALLING ENG_BOM_RTG_TRANSFER_PKG.eng_bom_rtg_transfer');
266 END IF;
267 INV_EBI_UTIL.debug_line('STEP 60: END INSIDE INV_EBI_CHANGE_ORDER_PUB.transfer_engg_item_mfg STATUS: '|| x_out.output_status.return_status);
268 EXCEPTION
269 WHEN FND_API.g_exc_unexpected_error THEN
270 ROLLBACK TO inv_ebi_engg_item_save_pnt;
271 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
272 IF(x_out.output_status.msg_data IS NULL) THEN
273 FND_MSG_PUB.count_and_get(
274 p_encoded => FND_API.g_false
275 ,p_count => x_out.output_status.msg_count
276 ,p_data => x_out.output_status.msg_data
277 );
278 END IF;
279 x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Item Number:' || p_item.main_obj_type.item_number || ' -> Org Code: ' || p_item.main_obj_type.organization_code;
280 WHEN FND_API.g_exc_error THEN
281 ROLLBACK TO inv_ebi_engg_item_save_pnt;
282 x_out.output_status.return_status := FND_API.g_ret_sts_error;
283 IF(x_out.output_status.msg_data IS NULL) THEN
284 FND_MSG_PUB.count_and_get(
285 p_encoded => FND_API.g_false
286 ,p_count => x_out.output_status.msg_count
287 ,p_data => x_out.output_status.msg_data
288 );
289 END IF;
290 x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Item Number:' || p_item.main_obj_type.item_number || ' -> Org Code: ' || p_item.main_obj_type.organization_code;
291 WHEN OTHERS THEN
292 ROLLBACK TO inv_ebi_engg_item_save_pnt;
293 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
294 IF (x_out.output_status.msg_data IS NOT NULL) THEN
295 x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_CHANGE_ORDER_PUB.transfer_engg_item_manufacturing ';
296 x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Item Number:' || p_item.main_obj_type.item_number || ' -> Org Code: ' || p_item.main_obj_type.organization_code;
297 ELSE
298 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_PUB.transfer_engg_item_manufacturing ';
299 x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Item Number:' || p_item.main_obj_type.item_number || ' -> Org Code: ' || p_item.main_obj_type.organization_code;
300 END IF;
301 END transfer_engg_item_mfg;
302
303 /************************************************************************************
304 -- API name : validate_component_items
305 -- Type : Private
306 -- Function :
307 -- This API is used to
308 -- Changed API Signature and Name Bug 8397083
309 ************************************************************************************/
310
311 PROCEDURE validate_component_items(
312 p_organization_code IN VARCHAR2
313 ,p_component_item_tbl IN inv_ebi_rev_comp_tbl
314 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
315 ) IS
316 l_is_master_org VARCHAR2(3);
317 l_organization_id NUMBER;
318 l_component_item_name VARCHAR2(240);
319 l_is_component_item_exists VARCHAR2(3);
320 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
321 l_output_status inv_ebi_output_status;
322 BEGIN
323 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
324 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
325 INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_CHANGE_ORDER_PUB.validate_component_items ORG CODE: '|| p_organization_code);
326 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
327 l_pk_col_name_val_pairs.EXTEND(1);
328 l_pk_col_name_val_pairs(1).name := 'organization_code';
329 l_pk_col_name_val_pairs(1).value := p_organization_code;
330 l_organization_id := INV_EBI_ITEM_HELPER.value_to_id(
331 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
332 ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
333 );
334 INV_EBI_UTIL.debug_line('STEP 20: ORG ID: '|| l_organization_id);
335 IF (l_organization_id IS NULL) THEN
336 FND_MESSAGE.set_name('INV','INV_EBI_ORG_CODE_INVALID');
337 FND_MESSAGE.set_token('COL_VALUE',p_organization_code);
338 FND_MSG_PUB.add;
339 RAISE FND_API.g_exc_error;
340 END IF;
341 l_is_master_org := INV_EBI_UTIL.is_master_org(l_organization_id);
342 IF(p_component_item_tbl IS NOT NULL AND p_component_item_tbl.COUNT > 0) THEN
343 FOR i IN 1.. p_component_item_tbl.COUNT LOOP
344 IF(p_component_item_tbl(i).transaction_type = INV_EBI_ITEM_PUB.g_otype_create ) THEN
345 l_component_item_name := p_component_item_tbl(i).component_item_name;
346 INV_EBI_UTIL.debug_line('STEP 30: COMP ITEM NAME: '|| l_component_item_name);
347 l_is_component_item_exists := INV_EBI_ITEM_HELPER.is_item_exists(
348 p_organization_id => l_organization_id
349 ,p_item_number => l_component_item_name
350 );
351 IF(l_is_master_org = fnd_api.g_true AND l_is_component_item_exists = fnd_api.g_false) THEN
352 FND_MESSAGE.set_name('INV','INV_EBI_ITEM_NO_MASTER_ORG');
353 FND_MESSAGE.set_token('COMP_ITEM', l_component_item_name);
354 FND_MSG_PUB.add;
355 RAISE FND_API.g_exc_error;
356 END IF;
357 END IF;
358 END LOOP;
359 END IF;
360 INV_EBI_UTIL.debug_line('STEP 40: END INSIDE INV_EBI_CHANGE_ORDER_PUB.validate_component_items STATUS: '|| x_out.output_status.return_status);
361 EXCEPTION
362 WHEN FND_API.g_exc_error THEN
363 x_out.output_status.return_status := FND_API.g_ret_sts_error;
364 IF(x_out.output_status.msg_data IS NULL) THEN
365 FND_MSG_PUB.count_and_get(
366 p_encoded => FND_API.g_false
367 ,p_count => x_out.output_status.msg_count
368 ,p_data => x_out.output_status.msg_data
369 );
370 END IF;
371 WHEN OTHERS THEN
372 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
373 IF (x_out.output_status.msg_data IS NOT NULL) THEN
374 x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_CHANGE_ORDER_PUB.validate_component_items ';
375 ELSE
376 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_PUB.validate_component_items ';
377 END IF;
378 END validate_component_items;
379
380 /************************************************************************************
381 -- API name : validate_item
382 -- Type : Private
383 -- Function :
384 -- This API is used to
385 --
386 ************************************************************************************/
387 PROCEDURE validate_item(
388 p_item IN inv_ebi_item_obj
389 ,x_out OUT NOCOPY inv_ebi_item_output_obj
390 ) IS
391
392 l_output_status inv_ebi_output_status;
393 l_transaction_type VARCHAR2(20);
394 l_item_catalog_group_code VARCHAR2(40);
395 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
396 l_item_output inv_ebi_item_output_obj;
397
398 BEGIN
399 INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_CHANGE_ORDER_PUB.validate_item');
400 INV_EBI_UTIL.debug_line('STEP 20: ITEM NUMBER TO VALIDATE : '|| p_item.main_obj_type.item_number ||
401 ' ORGANIZATION ID : '|| p_item.main_obj_type.organization_id);
402 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
403 x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL,NULL);
404 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
405 FND_MSG_PUB.initialize;
406
407 IF(INV_EBI_ITEM_HELPER.is_item_exists (
408 p_organization_id => p_item.main_obj_type.organization_id
409 ,p_item_number => p_item.main_obj_type.item_number
410 ) = FND_API.g_false ) THEN
411
412 /* Start of Bug 8299853 FDD says if CO item is new ICC is configured for NIR then
413 we should error out */
414
415 IF(p_item.main_obj_type.organization_id IS NOT NULL AND p_item.main_obj_type.organization_id <> FND_API.g_miss_num) THEN
416
417 IF(INV_EBI_UTIL.is_master_org(
418 p_organization_id => p_item.main_obj_type.organization_id
419 ) = FND_API.g_true) THEN
420
421 IF( p_item.main_obj_type.item_catalog_group_id IS NOT NULL
422 AND p_item.main_obj_type.item_catalog_group_id <> fnd_api.g_miss_num) THEN
423
424 IF(INV_EBI_ITEM_HELPER.is_new_item_request_reqd(
425 p_item_catalog_group_id => p_item.main_obj_type.item_catalog_group_id
426 ) = FND_API.g_true AND
427 p_item.bom_obj_type.eng_item_flag = 'N') THEN
428
429 FND_MESSAGE.set_name('INV','INV_EBI_ICC_CONFG_FOR_NIR');
430
431 IF ( p_item.main_obj_type.item_catalog_group_code IS NULL
432 OR p_item.main_obj_type.item_catalog_group_code = fnd_api.g_miss_char)THEN
433
434 l_pk_col_name_val_pairs.EXTEND(1);
435 l_pk_col_name_val_pairs(1).name := 'item_catalog_group_id';
436 l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.item_catalog_group_id;
437 l_item_catalog_group_code := INV_EBI_ITEM_HELPER.id_to_value(
438 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
439 ,p_entity_name => INV_EBI_ITEM_HELPER.G_ITEM_CATALOG_GROUP
440 );
441 l_pk_col_name_val_pairs.TRIM(1);
442
443 ELSE
444
445 l_item_catalog_group_code := p_item.main_obj_type.item_catalog_group_code;
446
447 END IF;
448
449 FND_MESSAGE.set_token('ITEM',p_item.main_obj_type.item_number);
450 FND_MESSAGE.set_token('ITEM_CATALOG', l_item_catalog_group_code);
451 FND_MSG_PUB.add;
452 RAISE FND_API.g_exc_error;
453
454 END IF;
455 END IF;
456 END IF;
457 END IF; --End of Bug 8299853
458 INV_EBI_UTIL.debug_line('STEP 30: BEFORE CALLING INV_EBI_ITEM_PUB.validate_item');
459 INV_EBI_ITEM_PUB.validate_item (
460 p_transaction_type => l_transaction_type
461 ,p_item => p_item
462 ,x_out => l_item_output
463 ) ;
464 INV_EBI_UTIL.debug_line('STEP 40: AFTER CALLING INV_EBI_ITEM_PUB.validate_item RETURN STATUS: '|| l_item_output.output_status.return_status);
465 IF(l_item_output.output_status.return_status <> FND_API.g_ret_sts_success) THEN
466 RAISE FND_API.g_exc_unexpected_error;
467 END IF;
468 END IF;
469 INV_EBI_UTIL.debug_line('STEP 50: END CALLING INV_EBI_CHANGE_ORDER_PUB.validate_item RETURN STATUS: '|| x_out.output_status.return_status);
470 EXCEPTION
471 WHEN FND_API.g_exc_unexpected_error THEN
472 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
473 IF(x_out.output_status.msg_data IS NULL) THEN
474 FND_MSG_PUB.count_and_get(
475 p_encoded => FND_API.g_false
476 ,p_count => x_out.output_status.msg_count
477 ,p_data => x_out.output_status.msg_data
478 );
479 END IF;
480 WHEN FND_API.g_exc_error THEN
481 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
482 IF(x_out.output_status.msg_data IS NULL) THEN
483 FND_MSG_PUB.count_and_get(
484 p_encoded => FND_API.g_false
485 ,p_count => x_out.output_status.msg_count
486 ,p_data => x_out.output_status.msg_data
487 );
488 END IF;
489 WHEN OTHERS THEN
490 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
491 IF (x_out.output_status.msg_data IS NOT NULL) THEN
492 x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_CHANGE_ORDER_PUB.validate_eco ';
493 ELSE
494 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_PUB.validate_eco ';
495 END IF;
496 END validate_item;
497 /************************************************************************************
498 -- API name : populate_revised_items_out
499 -- Type : Private
500 -- Function :
501 -- This API is used to populate Revised item details after eco creation.
502 ************************************************************************************/
503 PROCEDURE populate_revised_items_out
504 (p_change_order IN inv_ebi_change_order_obj
505 ,p_revised_item_type_tbl IN inv_ebi_revised_item_tbl
506 ,x_revised_item_type_tbl OUT NOCOPY inv_ebi_revitem_output_obj_tbl
507 )IS
508 l_organization_id NUMBER;
509 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
510 l_revised_item_type_tbl inv_ebi_revitem_output_obj_tbl;
511 l_ouid NUMBER;
512 l_operating_unit VARCHAR2(240);
513 BEGIN
514 INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_CHANGE_ORDER_PUB.populate_revised_items_out');
515 INV_EBI_UTIL.debug_line('STEP 20: ECO: '|| p_change_order.eco_name || ' ORG CODE: '|| p_change_order.organization_code);
516 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
517 l_revised_item_type_tbl := inv_ebi_revitem_output_obj_tbl();
518 l_pk_col_name_val_pairs.EXTEND(1);
519 l_pk_col_name_val_pairs(1).name := 'organization_code';
520 l_pk_col_name_val_pairs(1).value := p_change_order.organization_code;
521
522 l_organization_id := INV_EBI_ITEM_HELPER.value_to_id(
523 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
524 ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
525 );
526 l_pk_col_name_val_pairs.TRIM(1);
527 INV_EBI_UTIL.debug_line('STEP 30: BEFORE CALLING INV_EBI_ITEM_HELPER.get_Operating_unit ORG ID: '|| l_organization_id);
528 INV_EBI_ITEM_HELPER.get_Operating_unit
529 (p_oranization_id => l_organization_id
530 ,x_operating_unit => l_operating_unit
531 ,x_ouid => l_ouid
532 );
533 INV_EBI_UTIL.debug_line('STEP 40: AFTER CALLING INV_EBI_ITEM_HELPER.get_Operating_unit OPERATING UNIT: '|| l_operating_unit);
534 IF(p_revised_item_type_tbl IS NOT NULL AND p_revised_item_type_tbl.COUNT > 0) THEN
535 FOR i IN 1..p_revised_item_type_tbl.COUNT
536 LOOP
537 l_revised_item_type_tbl.extend();
538 l_revised_item_type_tbl(i) := INV_EBI_REVITEM_OUTPUT_OBJ(NULL,NULL);
539 l_revised_item_type_tbl(i).revised_item := INV_EBI_ITEM_OUTPUT_OBJ(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
540 l_revised_item_type_tbl(i).revised_item.ORGANIZATION_CODE := p_change_order.organization_code ;
541 l_revised_item_type_tbl(i).revised_item.ORGANIZATION_ID := l_organization_id;
542 l_revised_item_type_tbl(i).revised_item.ITEM_NUMBER := p_revised_item_type_tbl(i).revised_item_name;
543 l_revised_item_type_tbl(i).revised_item.INVENTORY_ITEM_ID := INV_EBI_ITEM_HELPER.get_inventory_item_id
544 ( p_organization_id => l_organization_id
545 ,p_item_number => p_revised_item_type_tbl(i).revised_item_name);
546 l_revised_item_type_tbl(i).revised_item.operating_unit := l_operating_unit;
547 l_revised_item_type_tbl(i).revised_item.operating_unit_id := l_ouid;
548 l_revised_item_type_tbl(i).rev_component :=INV_EBI_COMP_OUTPUT_OBJ_TBL();
549 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
550 FOR j IN 1..p_revised_item_type_tbl(i).component_item_tbl.COUNT
551 LOOP
552 l_revised_item_type_tbl(i).rev_component.extend();
553 l_revised_item_type_tbl(i).rev_component(j) := INV_EBI_COMP_OUTPUT_OBJ(NULL,NULL);
554 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,NULL);
555 l_revised_item_type_tbl(i).rev_component(j).component.ORGANIZATION_CODE := p_change_order.organization_code ;
556 l_revised_item_type_tbl(i).rev_component(j).component.ORGANIZATION_ID := l_organization_id;
557 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;
558 l_revised_item_type_tbl(i).rev_component(j).component.INVENTORY_ITEM_ID := INV_EBI_ITEM_HELPER.get_inventory_item_id
559 ( p_organization_id => l_organization_id
560 ,p_item_number => p_revised_item_type_tbl(i).component_item_tbl(j).component_item_name);
561 l_revised_item_type_tbl(i).rev_component(j).component.operating_unit := l_operating_unit;
562 l_revised_item_type_tbl(i).rev_component(j).component.operating_unit_id := l_ouid;
563 l_revised_item_type_tbl(i).rev_component(j).subcomponent := INV_EBI_ITEM_OUTPUT_TBL();
564
565 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
566 FOR k IN 1..p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl.COUNT
567 LOOP
568 l_revised_item_type_tbl(i).rev_component(j).subcomponent.extend();
569 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,NULL);
570 l_revised_item_type_tbl(i).rev_component(j).subcomponent(k).ORGANIZATION_CODE := p_change_order.organization_code ;
571 l_revised_item_type_tbl(i).rev_component(j).subcomponent(k).ORGANIZATION_ID := l_organization_id;
572 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;
573 l_revised_item_type_tbl(i).rev_component(j).subcomponent(k).INVENTORY_ITEM_ID :=INV_EBI_ITEM_HELPER.get_inventory_item_id
574 ( p_organization_id => l_organization_id
575 ,p_item_number => p_revised_item_type_tbl(i).component_item_tbl(j).substitute_component_tbl(k).substitute_component_name);
576 l_revised_item_type_tbl(i).rev_component(j).subcomponent(k).operating_unit := l_operating_unit;
577 l_revised_item_type_tbl(i).rev_component(j).subcomponent(k).operating_unit_id := l_ouid;
578 END LOOP;
579 END IF;
580 END LOOP;
581 END IF;
582 END LOOP;
583 END IF;
584 x_revised_item_type_tbl := l_revised_item_type_tbl;
585 INV_EBI_UTIL.debug_line('STEP 50: END INSIDE INV_EBI_CHANGE_ORDER_PUB.populate_revised_items_out');
586 END populate_revised_items_out;
587
588 /************************************************************************************
589 -- API name : get_eco
590 -- Type : Public
591 -- Function :
592 -- This API is used to retrieve all the change order attributes
593 ************************************************************************************/
594 PROCEDURE get_eco (
595 p_change_id IN NUMBER
596 ,p_last_update_status IN VARCHAR2
597 ,p_revised_item_sequence_id IN NUMBER
598 ,p_name_val_list IN inv_ebi_name_value_list
599 ,x_eco_obj OUT NOCOPY inv_ebi_eco_obj
600 ,x_return_status OUT NOCOPY VARCHAR2
601 ,x_msg_count OUT NOCOPY NUMBER
602 ,x_msg_data OUT NOCOPY VARCHAR2
603 )
604 IS
605 l_eco_obj inv_ebi_eco_obj;
606 l_return_status VARCHAR2(1);
607 l_msg_count NUMBER;
608 l_msg_data VARCHAR2(1000);
609 l_eco_change_order_obj inv_ebi_change_order_obj;
610 l_revised_item_tbl inv_ebi_revised_item_tbl;
611 l_revised_item_obj inv_ebi_revised_item_obj;
612 l_only_status_info VARCHAR2(1):= fnd_api.g_false;
613 BEGIN
614 INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_CHANGE_ORDER_PUB.get_eco ');
615 INV_EBI_UTIL.debug_line('STEP 20: CHANGE ID: '|| p_change_id);
616 x_return_status := FND_API.G_RET_STS_SUCCESS;
617 l_eco_change_order_obj := inv_ebi_change_order_obj(
618 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
619 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
620 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
621 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
622 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
623 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
624 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
625 );
626 l_revised_item_tbl := inv_ebi_revised_item_tbl();
627 l_revised_item_obj := inv_ebi_revised_item_obj(
628 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
629 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
630 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
631 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
632 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
633 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
634 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
635 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
636 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
637 ,NULL,NULL,NULL,NULL
638 );
639 INV_EBI_UTIL.debug_line('STEP 30: BEFORE CALLING INV_EBI_CHANGE_ORDER_HELPER.get_eco');
640 INV_EBI_CHANGE_ORDER_HELPER.get_eco (
641 p_change_id => p_change_id
642 ,p_last_update_status => p_last_update_status
643 ,p_revised_item_sequence_id => p_revised_item_sequence_id
644 ,p_name_val_list => p_name_val_list
645 ,x_eco_obj => l_eco_obj
646 ,x_return_status => l_return_status
647 ,x_msg_count => l_msg_count
648 ,x_msg_data => l_msg_data
649 );
650 INV_EBI_UTIL.debug_line('STEP 40: END CALLING INV_EBI_CHANGE_ORDER_HELPER.get_eco RETURN STATUS: '||l_return_status);
651 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
652 RAISE FND_API.g_exc_unexpected_error;
653 END IF;
654 IF p_name_val_list.name_value_table IS NOT NULL THEN
655 FOR i in p_name_val_list.name_value_table.FIRST..p_name_val_list.name_value_table.LAST LOOP
656 IF UPPER(p_name_val_list.name_value_table(i).param_name) = G_ONLY_STATUS_INFO THEN
657 l_only_status_info := p_name_val_list.name_value_table(i).param_value;
658 END IF;
659 END LOOP;
660 END IF;
661 IF l_only_status_info = fnd_api.g_true THEN
662 l_eco_change_order_obj.status_name := l_eco_obj.eco_change_order_type.status_name;
663 l_eco_change_order_obj.implementation_date := l_eco_obj.eco_change_order_type.implementation_date;
664 l_eco_change_order_obj.cancellation_date := l_eco_obj.eco_change_order_type.cancellation_date;
665 FOR i in l_eco_obj.eco_revised_item_type.FIRST..l_eco_obj.eco_revised_item_type.LAST LOOP
666 l_revised_item_tbl.extend();
667 l_revised_item_obj.status_name := l_eco_obj.eco_revised_item_type(i).status_name;
668 l_revised_item_obj.new_effective_date := l_eco_obj.eco_revised_item_type(i).new_effective_date;
669 l_revised_item_tbl(i) := l_revised_item_obj;
670 END LOOP;
671 x_eco_obj := inv_ebi_eco_obj(l_eco_change_order_obj,NULL,l_revised_item_tbl,NULL);
672 ELSE
673 x_eco_obj := l_eco_obj;
674 END IF;
675 INV_EBI_UTIL.debug_line('STEP 50: END INSIDE INV_EBI_CHANGE_ORDER_PUB.get_eco STATUS: '|| x_return_status);
676 EXCEPTION
677 WHEN FND_API.g_exc_unexpected_error THEN
678 x_return_status := FND_API.g_ret_sts_error;
679 IF(x_msg_data IS NULL) THEN
680 FND_MSG_PUB.count_and_get(
681 p_encoded => FND_API.g_false
682 ,p_count => x_msg_count
683 ,p_data => x_msg_data
684 );
685 END IF;
686 WHEN OTHERS THEN
687 x_return_status := FND_API.g_ret_sts_error;
688 IF (x_msg_data IS NOT NULL) THEN
689 x_msg_data := x_msg_data ||' -> at inv_ebi_change_order_pub.get_eco';
690 ELSE
691 x_msg_data := SQLERRM||' at inv_ebi_change_order_pub.get_eco ';
692 END IF;
693 END get_eco;
694
695 /************************************************************************************
696 -- API name : Convert_date_str_eco
697 -- Type : Public
698 -- Function :
699 --
700 ************************************************************************************/
701
702 PROCEDURE Convert_date_str_eco(p_eco_lst_obj IN inv_ebi_eco_out_obj_tbl
703 ,x_eco_lst_obj OUT NOCOPY inv_ebi_eco_out_obj_tbl)
704 IS
705 BEGIN
706
707 x_eco_lst_obj := inv_ebi_eco_out_obj_tbl();
708
709 IF(p_eco_lst_obj IS NOT NULL AND p_eco_lst_obj.COUNT>0) THEN
710 x_eco_lst_obj.EXTEND(p_eco_lst_obj.COUNT);
711 x_eco_lst_obj := p_eco_lst_obj;
712 END IF;
713
714 IF p_eco_lst_obj IS NOT NULL AND p_eco_lst_obj.COUNT>0 THEN
715 FOR i IN p_eco_lst_obj.FIRST..p_eco_lst_obj.LAST LOOP
716 ------------------------------------------------------------------------------
717 -- To Convert Date Fields in eco_change_order_type (INV_EBI_CHANGE_ORDER_OBJ)
718 ------------------------------------------------------------------------------
719 x_eco_lst_obj(i).eco_attr.eco_change_order_type.approval_date_str :=
720 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_change_order_type.approval_date);
721
722 x_eco_lst_obj(i).eco_attr.eco_change_order_type.approval_request_date_str :=
723 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_change_order_type.approval_request_date);
724
725 x_eco_lst_obj(i).eco_attr.eco_change_order_type.need_by_date_str :=
726 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_change_order_type.need_by_date);
727
728 x_eco_lst_obj(i).eco_attr.eco_change_order_type.implementation_date_str :=
729 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_change_order_type.implementation_date);
730
731 x_eco_lst_obj(i).eco_attr.eco_change_order_type.cancellation_date_str :=
732 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_change_order_type.cancellation_date);
733 ------------------------------------------------------------------------------
734 -- To Convert Date Fields in eco_change_order_type (INV_EBI_UDA_ATTR_OBJ)
735 ------------------------------------------------------------------------------
736
737 IF(p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl IS NOT NULL AND
738 p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl.COUNT>0) THEN
739 FOR j IN p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl.FIRST..
740 p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl.LAST LOOP
741 IF(p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl(j).attributes_tbl IS NOT NULL
742 AND p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl(j).attributes_tbl.COUNT>0) THEN
743 FOR k IN p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl(j).attributes_tbl.FIRST..
744 p_eco_lst_obj(i).eco_attr.eco_change_order_type.change_order_uda.attribute_group_tbl(j).attributes_tbl.LAST LOOP
745 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 :=
746 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);
747 END LOOP;
748 END IF;
749 END LOOP;
750 END IF;
751
752 ----------------------------------------------------------------------------
753 -- To Convert Date Fields in eco_revised_item_type(INV_EBI_REVISED_ITEM_TBL)
754 ----------------------------------------------------------------------------
755 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
756 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
757 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).start_effective_date_str :=
758 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).start_effective_date);
759
760 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).new_effective_date_str :=
761 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).new_effective_date);
762
763 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).earliest_effective_date_str :=
764 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).earliest_effective_date);
765
766 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).selection_date_str :=
767 INV_EBI_ITEM_HELPER.convert_date_str(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).selection_date);
768
769
770 -------------------------------------------------------
771 -- Converting Dates For ORIGNAL_BOM_REFERENCE
772 -------------------------------------------------------
773
774 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).orignal_bom_reference IS NOT NULL) THEN
775 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).orignal_bom_reference.as_of_date_str :=
776 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);
777 END IF;
778
779
780 -------------------------------------------------------
781 -- Converting Dates For ITEM_REVISION_UDA
782 -------------------------------------------------------
783
784 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda IS NOT NULL) THEN
785 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda.attribute_group_tbl IS NOT NULL AND
786 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda.attribute_group_tbl.COUNT >0) THEN
787 FOR k IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda.attribute_group_tbl.FIRST..
788 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda.attribute_group_tbl.LAST LOOP
789 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
790 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..
791 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item_revision_uda.attribute_group_tbl(k).attributes_tbl.LAST LOOP
792 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 :=
793 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);
794 END LOOP;
795 END IF;
796 END LOOP;
797 END IF;
798 END IF;
799
800 ----------------------------------------------------------------------------------------------------------------
801 -- Converting Dates For INV_EBI_ITEM_OBJ(inv_ebi_item_obj)
802 ----------------------------------------------------------------------------------------------------------------
803
804
805 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type IS NOT NULL) THEN
806
807 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.start_date_active_str :=
808 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);
809
810 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.end_date_active_str :=
811 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);
812
813 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.creation_date_str :=
814 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);
815
816 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.last_update_date_str :=
817 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);
818
819 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.program_update_date_str :=
820 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);
821
822 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.main_obj_type.effectivity_date_str :=
823 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);
824
825 END IF;
826
827 ------------------------------------------------------
828 -- Converting Dates For INV_EBI_ITEM_DEPRECATED_OBJ
829 ------------------------------------------------------
830
831 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.deprecated_obj_type IS NOT NULL) THEN
832
833 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.deprecated_obj_type.engineering_date_str :=
834 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);
835
836 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.deprecated_obj_type.wh_update_date_str :=
837 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);
838
839 END IF;
840
841 --------------------------------------------------------
842 -- Converting Dates For INV_EBI_UDA_INPUT_OBJ
843 --------------------------------------------------------
844
845 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl IS NOT NULL AND
846 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl.COUNT>0) THEN
847 FOR k IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl.FIRST..
848 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl.LAST LOOP
849 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
850 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl(k).attributes_tbl.COUNT>0) THEN
851 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..
852 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).item.uda_type.attribute_group_tbl(k).attributes_tbl.LAST LOOP
853 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 :=
854 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);
855 END LOOP;
856 END IF;
857 END LOOP;
858 END IF;
859
860
861 ------------------------------------------------------------------------------
862 -- Converting Dates For structure_header (INV_EBI_STRUCTURE_HEADER_OBJ)
863 ------------------------------------------------------------------------------
864
865 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
866 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).structure_header.structure_header_uda.attribute_group_tbl.COUNT>0) THEN
867 FOR k IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).structure_header.structure_header_uda.attribute_group_tbl.FIRST..
868 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).structure_header.structure_header_uda.attribute_group_tbl.LAST LOOP
869 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
870 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
871 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..
872 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
873 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 :=
874 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);
875 END LOOP;
876 END IF;
877 END LOOP;
878 END IF;
879
880 --------------------------------------------------------------------------------
881 -- Converting Dates For component_item_tbl (INV_EBI_REV_COMP_TBL)
882 --------------------------------------------------------------------------------
883
884 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl IS NOT NULL AND
885 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl.COUNT>0 ) THEN
886 FOR k IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl.FIRST..
887 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl.LAST LOOP
888
889 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).start_effective_date_str :=
890 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);
891
892 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).new_effectivity_date_str :=
893 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);
894
895 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).disable_date_str :=
896 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);
897
898 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).old_effectivity_date_str :=
899 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);
900
901 --------------------------------------------------------------------------
902 -- Converting Dates For substitute_component_tbl( INV_EBI_SUB_COMP_TBL )
903 --------------------------------------------------------------------------
904
905 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).substitute_component_tbl IS NOT NULL AND
906 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).substitute_component_tbl.COUNT>0) THEN
907 FOR l IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).substitute_component_tbl.FIRST..
908 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).substitute_component_tbl.LAST LOOP
909 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).substitute_component_tbl(l).start_effective_date_str :=
910 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);
911 END LOOP;
912 END IF;
913
914 ----------------------------------------------------------------------------
915 -- Converting Dates for reference_designator_tbl( INV_EBI_REF_DESG_TBL )
916 ----------------------------------------------------------------------------
917
918 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).reference_designator_tbl IS NOT NULL AND
919 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).reference_designator_tbl.COUNT>0) THEN
920 FOR l IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).reference_designator_tbl.FIRST..
921 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).reference_designator_tbl.LAST LOOP
922 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).reference_designator_tbl(l).start_effective_date_str :=
923 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);
924 END LOOP;
925 END IF;
926
927 ----------------------------------------------------------------------------
928 -- Converting Dates for component_revision_uda ( INV_EBI_UDA_INPUT_OBJ )
929 ----------------------------------------------------------------------------
930
931 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
932 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
933 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..
934 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).component_item_tbl(k).component_revision_uda.attribute_group_tbl.LAST LOOP
935 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
936 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
937 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..
938 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
939 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 :=
940 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);
941 END LOOP;
942 END IF;
943 END LOOP;
944 END IF;
945
946 --------------------------------------------------------------------------------
947 -- Converting Dates For revision_operation_tbl ( INV_EBI_REV_OP_TBL )
948 --------------------------------------------------------------------------------
949
950 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl IS NOT NULL AND
951 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl.COUNT>0) THEN
952 FOR k IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl.FIRST..
953 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl.LAST LOOP
954
955 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl(k).start_effective_date_str :=
956 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);
957
958 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl(k).old_start_effective_number_str :=
959 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);
960
961 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_operation_tbl(k).disable_number_str :=
962 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);
963 END LOOP;
964 END IF;
965
966 ----------------------------------------------------------------------------------
967 -- Converting Dates For revision_op_resource_tbl (INV_EBI_REV_OP_RES_TBL)
968 ----------------------------------------------------------------------------------
969
970 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_op_resource_tbl IS NOT NULL AND
971 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_op_resource_tbl.COUNT>0) THEN
972 FOR k IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_op_resource_tbl.FIRST..
973 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_op_resource_tbl.LAST LOOP
974 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_op_resource_tbl(k).op_start_effective_date_str :=
975 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);
976 END LOOP;
977 END IF;
978
979 ----------------------------------------------------------------------------------------------------------------
980 -- Converting Dates For revision_sub_resource_tbl (INV_EBI_REV_SUB_RES_TBL)
981 ----------------------------------------------------------------------------------------------------------------
982
983 IF(p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_sub_resource_tbl IS NOT NULL AND
984 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_sub_resource_tbl.COUNT>0) THEN
985 FOR k IN p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_sub_resource_tbl.FIRST..
986 p_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_sub_resource_tbl.LAST LOOP
987 x_eco_lst_obj(i).eco_attr.eco_revised_item_type(j).revision_sub_resource_tbl(k).op_start_effective_date_str :=
988 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);
989 END LOOP;
990 END IF;
991 END LOOP;
992 END IF;
993 END LOOP;
994 END IF;
995 END LOOP;
996 END IF;
997 EXCEPTION
998 WHEN OTHERS THEN
999 x_eco_lst_obj := p_eco_lst_obj;
1000 END Convert_date_str_eco;
1001
1002 /************************************************************************************
1003 -- API name : get_eco_list_attr
1004 -- Type : Public
1005 -- Function : This API to retrive change order for multiple change id
1006 --
1007 ************************************************************************************/
1008 PROCEDURE get_eco_list_attr(
1009 p_change_lst IN inv_ebi_change_id_obj_tbl
1010 ,p_name_val_list IN inv_ebi_name_value_list
1011 ,x_eco_lst_obj OUT NOCOPY inv_ebi_eco_out_obj_tbl
1012 ,x_return_status OUT NOCOPY VARCHAR2
1013 ,x_msg_count OUT NOCOPY NUMBER
1014 ,x_msg_data OUT NOCOPY VARCHAR2
1015 )
1016 IS
1017 l_msg_data VARCHAR2(32000);
1018 l_return_status VARCHAR2(1);
1019 l_ovrflw_msg VARCHAR2(1):='N';
1020 l_count NUMBER := 0;
1021 l_part_msgtxt VARCHAR2(32000);
1022 l_name_val_list inv_ebi_name_value_list;
1023 l_eco_obj inv_ebi_eco_obj;
1024 l_eco_obj_tbl inv_ebi_eco_out_obj_tbl;
1025 l_eco_lst_obj inv_ebi_eco_out_obj;
1026 l_mult_org_chg_id_temp inv_ebi_change_id_obj_tbl;
1027 l_mult_org_chg_id_tbl inv_ebi_change_id_obj_tbl;
1028 l_eco_output_tbl_lst inv_ebi_change_id_obj_tbl;
1029 l_get_multiorg_change VARCHAR2(1) := NULL;-- added for bug 13874263
1030
1031 CURSOR c_get_multi_org_chg_id(p_chg_id NUMBER)
1032 IS
1033 SELECT inv_ebi_change_id_obj(geco.change_id,'N')
1034 FROM (SELECT eec1.change_id
1035 FROM eng_engineering_changes eec,eng_engineering_changes eec1
1036 where eec.change_id=p_chg_id
1037 and eec.change_notice=eec1.change_notice
1038 MINUS
1039 SELECT b.change_id
1040 FROM THE (SELECT CAST(p_change_lst as inv_ebi_change_id_obj_tbl)
1041 FROM dual) b ) geco;
1042
1043 CURSOR c_get_final_eco_list IS
1044 SELECT inv_ebi_change_id_obj(geco.change_id,geco.last_update_status)
1045 FROM (SELECT b.change_id,b.last_update_status
1046 FROM THE (SELECT CAST( p_change_lst as inv_ebi_change_id_obj_tbl)
1047 FROM dual ) b
1048 UNION
1049 SELECT c.change_id,c.last_update_status
1050 FROM THE (SELECT CAST( l_mult_org_chg_id_tbl as inv_ebi_change_id_obj_tbl)
1051 FROM dual ) c ) geco;
1052 BEGIN
1053 l_eco_obj_tbl := inv_ebi_eco_out_obj_tbl();
1054 l_name_val_list := p_name_val_list;
1055 /* --BUG 8712091
1056 IF l_name_val_list.name_value_table IS NOT NULL AND l_name_val_list.name_value_table.COUNT>0
1057 THEN
1058 INV_EBI_UTIL.set_apps_context(l_name_val_list.name_value_table);
1059 END IF;
1060 */
1061 x_return_status := FND_API.G_RET_STS_SUCCESS;
1062 l_mult_org_chg_id_tbl := inv_ebi_change_id_obj_tbl();
1063 l_mult_org_chg_id_temp := inv_ebi_change_id_obj_tbl();
1064
1065 INV_EBI_ITEM_HELPER.set_server_time_zone;
1066
1067 INV_EBI_UTIL.setup();
1068
1069 INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_CHANGE_ORDER_PUB.get_eco_list_attr');
1070 INV_EBI_UTIL.debug_line( ' **************** Apps Context Details ****************' );
1071 INV_EBI_UTIL.debug_line(' User Id: ' || FND_GLOBAL.USER_ID || '; Responsibility Application id: ' ||
1072 FND_GLOBAL.RESP_APPL_ID || '; Responsibility Id: ' || FND_GLOBAL.RESP_ID ||
1073 '; Security Group id: '|| FND_GLOBAL.SECURITY_GROUP_ID ||'; User Lang: '|| USERENV('LANG') );
1074 INV_EBI_UTIL.debug_line( ' **************** End of Apps Context ****************' );
1075
1076 --Start of Bug 13874263
1077 IF l_name_val_list.name_value_table IS NOT NULL THEN
1078 FOR i in l_name_val_list.name_value_table.FIRST..l_name_val_list.name_value_table.LAST LOOP
1079 IF UPPER(l_name_val_list.name_value_table(i).param_name) = G_INCLUDE_MULTIORG_CHANGE THEN
1080 l_get_multiorg_change := l_name_val_list.name_value_table(i).param_value;
1081 END IF;
1082 END LOOP;
1083 END IF;
1084
1085 IF(l_get_multiorg_change IS NULL OR l_get_multiorg_change = fnd_api.g_miss_char) THEN
1086 l_get_multiorg_change := INV_EBI_FLAGS.G_INCLUDE_MULTIORG_CHANGE;
1087 END IF;
1088 --End of Bug 13874263
1089 INV_EBI_UTIL.debug_line( 'Get Multi Org Change Details ' || l_get_multiorg_change);
1090
1091 IF(l_get_multiorg_change = FND_API.G_TRUE) THEN -- added if condition for bug 13874263
1092 IF p_change_lst IS NOT NULL AND p_change_lst.COUNT > 0 THEN
1093 FOR i in 1..p_change_lst.COUNT LOOP
1094 OPEN c_get_multi_org_chg_id(p_change_lst(i).change_id);
1095 FETCH c_get_multi_org_chg_id BULK COLLECT INTO l_mult_org_chg_id_temp;
1096 IF l_mult_org_chg_id_temp IS NOT NULL AND l_mult_org_chg_id_temp.COUNT>0 THEN
1097 l_mult_org_chg_id_tbl.EXTEND(l_mult_org_chg_id_temp.COUNT);
1098 l_mult_org_chg_id_tbl := l_mult_org_chg_id_temp;
1099 END IF;
1100 CLOSE c_get_multi_org_chg_id;
1101 END LOOP;
1102 END IF;
1103
1104 IF c_get_final_eco_list%ISOPEN THEN
1105 CLOSE c_get_final_eco_list;
1106 END IF;
1107 OPEN c_get_final_eco_list;
1108 FETCH c_get_final_eco_list BULK COLLECT INTO l_eco_output_tbl_lst;
1109 CLOSE c_get_final_eco_list;
1110 ELSE -- else block added for bug 13874263
1111 l_eco_output_tbl_lst := inv_ebi_change_id_obj_tbl();
1112 l_eco_output_tbl_lst := p_change_lst;
1113 END IF;
1114 INV_EBI_UTIL.debug_line( 'Size of Change id table ' || l_eco_output_tbl_lst.COUNT);
1115
1116 IF (l_eco_output_tbl_lst IS NOT NULL) THEN
1117 INV_EBI_UTIL.debug_line('STEP 20: BEFORE CALLING INV_EBI_CHANGE_ORDER_HELPER.get_eco ');
1118 FOR l_cnt_cid IN 1..l_eco_output_tbl_lst.COUNT LOOP
1119 INV_EBI_CHANGE_ORDER_HELPER.get_eco (
1120 p_change_id => l_eco_output_tbl_lst(l_cnt_cid).change_id
1121 ,p_last_update_status => l_eco_output_tbl_lst(l_cnt_cid).last_update_status
1122 ,p_revised_item_sequence_id => NULL
1123 ,p_name_val_list => l_name_val_list
1124 ,x_eco_obj => l_eco_obj
1125 ,x_return_status => l_return_status
1126 ,x_msg_count => l_count
1127 ,x_msg_data => l_msg_data
1128 );
1129 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1130 x_return_status := FND_API.g_ret_sts_error;
1131 l_part_msgtxt := ' Change Id: '|| p_change_lst(l_cnt_cid).change_id||' Err Msg: '||l_msg_data ;
1132 IF (x_msg_data IS NOT NULL) THEN
1133 IF (LENGTH(x_msg_data||' , '||l_part_msgtxt) < 31000) THEN
1134 x_msg_data :=x_msg_data||' , '||l_part_msgtxt;
1135 ELSE
1136 l_ovrflw_msg :='Y';
1137 EXIT;
1138 END IF;
1139 ELSE
1140 x_msg_data := l_part_msgtxt;
1141 END IF;
1142 END IF;
1143 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));
1144 l_eco_obj_tbl.EXTEND();
1145 l_eco_obj_tbl(l_cnt_cid) := l_eco_lst_obj;
1146 END LOOP;
1147 IF (l_ovrflw_msg='Y') AND SUBSTR(x_msg_data,length(x_msg_data)-2) <> '...' THEN
1148 x_msg_data :=x_msg_data||' , '||'...';
1149 END IF;
1150 INV_EBI_UTIL.debug_line('STEP 30: END CALLING INV_EBI_CHANGE_ORDER_HELPER.get_eco STATUS: '|| l_return_status);
1151 END IF;
1152
1153 IF (l_eco_obj_tbl IS NOT NULL AND l_eco_obj_tbl.COUNT > 0) THEN
1154 x_eco_lst_obj := l_eco_obj_tbl;
1155 Convert_date_str_eco(p_eco_lst_obj => l_eco_obj_tbl
1156 ,x_eco_lst_obj => x_eco_lst_obj);
1157 END IF;
1158
1159 INV_EBI_UTIL.debug_line('STEP 40: END INSIDE INV_EBI_CHANGE_ORDER_PUB.get_eco_list_attr STATUS: '|| x_return_status);
1160
1161 INV_EBI_UTIL.wrapup;
1162
1163 EXCEPTION
1164 WHEN OTHERS THEN
1165 x_return_status := FND_API.G_RET_STS_ERROR;
1166 IF (x_msg_data IS NOT NULL) THEN
1167 x_msg_data := x_msg_data ||' -> at INV_EBI_CHANGE_ORDER_PUB.get_eco_list_attr';
1168 ELSE
1169 x_msg_data := SQLERRM||' at INV_EBI_CHANGE_ORDER_PUB.get_eco_list_attr ';
1170 END IF;
1171 END get_eco_list_attr;
1172
1173 /************************************************************************************
1174 -- API name : process_change_order_items
1175 -- Type : Private
1176 -- Procedure :
1177 -- This API is used to create Items coming in the Change Order if item
1178 -- does not exist
1179 -- Added this API for Bug 8397083
1180 ************************************************************************************/
1181
1182 PROCEDURE process_change_order_items(
1183 p_commit IN VARCHAR2 := FND_API.g_false
1184 ,p_eco_obj IN inv_ebi_eco_obj
1185 ,p_update_item_tbl IN inv_ebi_item_attr_tbl
1186 ,x_update_item_tbl OUT NOCOPY inv_ebi_item_attr_tbl
1187 ,x_out OUT NOCOPY inv_ebi_item_output_obj
1188 ,x_eco_obj OUT NOCOPY inv_ebi_eco_obj
1189 ) IS
1190
1191 l_item inv_ebi_item_obj;
1192 l_revised_item_tbl inv_ebi_revised_item_tbl;
1193 l_eco_obj inv_ebi_eco_obj;
1194 l_master_org NUMBER;
1195 l_inventory_item_id NUMBER;
1196 l_current_revision VARCHAR2(3);
1197 l_revision VARCHAR2(3);
1198 l_revision_set BOOLEAN := FALSE;
1199 l_cnt_nmval NUMBER := 0;
1200 l_item_output_obj inv_ebi_item_output_obj;
1201 l_eco_output_obj inv_ebi_eco_output_obj;
1202 l_item_count NUMBER :=0;
1203 l_update_item_count NUMBER :=0;
1204 l_output_status inv_ebi_output_status;
1205
1206 CURSOR c_effectivity_date(
1207 p_organization_id IN NUMBER,
1208 p_inventory_item_id IN NUMBER
1209 )
1210 IS
1211 SELECT revision
1212 FROM mtl_item_revisions_b
1213 WHERE
1214 organization_id = p_organization_id AND
1215 inventory_item_id = p_inventory_item_id;
1216
1217 BEGIN
1218 SAVEPOINT inv_ebi_chg_items_save_pnt;
1219 FND_MSG_PUB.initialize;
1220
1221 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
1222 x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL,NULL);
1223
1224 INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_CHANGE_ORDER_PUB.process_change_order_items');
1225
1226 x_update_item_tbl := inv_ebi_item_attr_tbl();
1227
1228 x_update_item_tbl := p_update_item_tbl;
1229
1230 l_eco_obj := p_eco_obj;
1231
1232 IF(l_eco_obj.eco_revised_item_type IS NOT NULL AND l_eco_obj.eco_change_order_type IS NOT NULL) THEN
1233
1234 FOR i IN 1 .. l_eco_obj.eco_revised_item_type.COUNT
1235 LOOP
1236
1237 --To populate item attributes from revised item values.
1238 INV_EBI_UTIL.debug_line('STEP 20: BEFORE CALLING INV_EBI_CHANGE_ORDER_PUB.populate_item_attributes FOR ' || ' ECO : ' || l_eco_obj.eco_change_order_type.eco_name ||
1239 ' ORG CODE : '|| l_eco_obj.eco_change_order_type.organization_code || ' REVISED ITEM : '||l_eco_obj.eco_revised_item_type(i).revised_item_name );
1240 populate_item_attributes(
1241 p_change_order_obj => l_eco_obj.eco_change_order_type
1242 ,p_revised_item => l_eco_obj.eco_revised_item_type(i)
1243 ,x_item => l_item
1244 ,x_out => l_item_output_obj
1245 );
1246 INV_EBI_UTIL.debug_line('STEP 30: END CALLING INV_EBI_CHANGE_ORDER_PUB.populate_item_attributes STATUS: '|| l_item_output_obj.output_status.return_status);
1247
1248 IF (l_item_output_obj.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1249 x_out.output_status.msg_data := l_item_output_obj.output_status.msg_data;
1250 x_out.output_status.msg_count := l_item_output_obj.output_status.msg_count;
1251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1252
1253 END IF;
1254 INV_EBI_UTIL.debug_line('STEP 40: BEFORE CALLING INV_EBI_CHANGE_ORDER_PUB.populate_item_ids');
1255
1256 INV_EBI_ITEM_HELPER.populate_item_ids(
1257 p_item => l_item
1258 ,x_out => l_item_output_obj
1259 ,x_item => l_eco_obj.eco_revised_item_type(i).item
1260 );
1261 INV_EBI_UTIL.debug_line('STEP 50: AFTER CALLING INV_EBI_CHANGE_ORDER_PUB.populate_item_ids STATUS: '|| l_item_output_obj.output_status.return_status);
1262 IF (l_item_output_obj.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1263 x_out.output_status.msg_data := l_item_output_obj.output_status.msg_data;
1264 x_out.output_status.msg_count := l_item_output_obj.output_status.msg_count;
1265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1266 END IF;
1267
1268
1269 INV_EBI_UTIL.debug_line('STEP 60: START CALLING INV_EBI_CHANGE_ORDER_PUB.validate_item');
1270
1271 validate_item(
1272 p_item => l_item
1273 ,x_out => l_item_output_obj
1274 );
1275
1276 INV_EBI_UTIL.debug_line('STEP 70: END CALLING INV_EBI_CHANGE_ORDER_PUB.validate_item STATUS: '|| l_item_output_obj.output_status.return_status);
1277
1278 IF (l_item_output_obj.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1279 x_out.output_status.msg_data := l_item_output_obj.output_status.msg_data;
1280 x_out.output_status.msg_count := l_item_output_obj.output_status.msg_count;
1281 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1282 END IF;
1283
1284
1285 l_eco_obj.eco_revised_item_type(i).item.NAME_VALUE_TBL := INV_EBI_NAME_VALUE_TBL();
1286 l_cnt_nmval := 0;
1287
1288 IF l_eco_obj.NAME_VALUE_TBL IS NOT NULL AND l_eco_obj.NAME_VALUE_TBL.COUNT >0 THEN
1289
1290 FOR x in 1..l_eco_obj.NAME_VALUE_TBL.COUNT
1291 LOOP
1292
1293 IF l_eco_obj.NAME_VALUE_TBL(x).PARAM_NAME in ('VALIDATE_REVISED_ITEM_REVISION','TEMPLATE_FOR_ITEM_UPDATE_ALLOWED','ASSIGN_DEFAULT_TEMPLATE_FROM_ICC','ALLOW_LIFECYCLE_PHASE_SKIP') THEN
1294
1295 l_eco_obj.eco_revised_item_type(i).item.NAME_VALUE_TBL.extend();
1296 l_cnt_nmval := l_cnt_nmval+1;
1297 l_eco_obj.eco_revised_item_type(i).item.NAME_VALUE_TBL(l_cnt_nmval) := INV_EBI_NAME_VALUE_OBJ(l_eco_obj.NAME_VALUE_TBL(x).PARAM_NAME,l_eco_obj.NAME_VALUE_TBL(x).PARAM_VALUE);
1298
1299 END IF;
1300 END LOOP;
1301 END IF;
1302
1303 l_revision_set := FALSE;
1304
1305 IF( INV_EBI_ITEM_HELPER.is_item_exists (
1306 p_organization_id => l_eco_obj.eco_revised_item_type(i).item.main_obj_type.organization_id
1307 ,p_item_number => l_eco_obj.eco_revised_item_type(i).item.main_obj_type.item_number
1308 ) = FND_API.g_false ) THEN
1309
1310 /* Bug 7132835 After subsequent release of ecos in master org ,If Change order is released in Child Org
1311 then we should create initial rev,current rev of the item in master org through process_item API
1312 and incoming revision through process_eco API */
1313
1314 IF(INV_EBI_CHANGE_ORDER_HELPER.is_child_org (
1315 p_organization_id => l_eco_obj.eco_revised_item_type(i).item.main_obj_type.organization_id
1316 ) = fnd_api.g_true) THEN
1317
1318 l_master_org := INV_EBI_UTIL.get_master_organization(
1319 p_organization_id => l_eco_obj.eco_revised_item_type(i).item.main_obj_type.organization_id
1320 );
1321
1322 l_inventory_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id (
1323 p_organization_id => l_master_org
1324 ,p_item_number => l_eco_obj.eco_revised_item_type(i).item.main_obj_type.item_number
1325 );
1326
1327 l_current_revision := INV_EBI_CHANGE_ORDER_HELPER.get_current_item_revision(
1328 p_inventory_item_id => l_inventory_item_id,
1329 p_organization_id => l_master_org,
1330 p_date => sysdate
1331 );
1332 INV_EBI_UTIL.debug_line('STEP 80: MASTER ORG: '|| l_master_org || 'INV ITEM ID: '||l_inventory_item_id || 'CURRENT REVISION: '|| l_current_revision);
1333 IF(l_current_revision = l_eco_obj.eco_revised_item_type(i).new_revised_item_revision ) THEN
1334
1335 l_eco_obj.eco_revised_item_type(i).item.main_obj_type.revision_code := fnd_api.g_miss_char;
1336
1337 ELSIF( l_current_revision <> l_eco_obj.eco_revised_item_type(i).new_revised_item_revision AND
1338 l_current_revision < l_eco_obj.eco_revised_item_type(i).new_revised_item_revision ) THEN
1339
1340 l_eco_obj.eco_revised_item_type(i).item.main_obj_type.revision_code := l_current_revision;
1341
1342 --Bug 7132835 If 2 revisions are to be created then intial rev gets created with sysdate +1 ,to have effectivity_date of
1343 -- next rev greater than that of initial rev we added 0.5 sec
1344
1345 l_eco_obj.eco_revised_item_type(i).item.main_obj_type.effectivity_date := sysdate+ 2/86400;
1346 l_revision_set := TRUE;
1347
1348 END IF;
1349 END IF;
1350
1351 --call Create New Production Item API .This will also process existing item information.
1352
1353 INV_EBI_UTIL.debug_line('STEP 90: BEFORE CALLING INV_EBI_ITEM_HELPER.sync_item');
1354 INV_EBI_ITEM_HELPER.sync_item(
1355 p_commit => FND_API.g_false
1356 ,p_operation => INV_EBI_ITEM_PUB.g_otype_create
1357 ,p_item => l_eco_obj.eco_revised_item_type(i).item
1358 ,x_out => l_item_output_obj
1359 );
1360
1361 INV_EBI_UTIL.debug_line('STEP 100: END CALLING INV_EBI_ITEM_HELPER.sync_item STATUS: '|| l_item_output_obj.output_status.return_status);
1362
1363 l_item_count := l_item_count + 1;
1364
1365 IF (l_item_output_obj.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1366 x_out.output_status.msg_data := l_item_output_obj.output_status.msg_data;
1367 x_out.output_status.msg_count := l_item_output_obj.output_status.msg_count;
1368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1369 END IF;
1370
1371 IF(l_revision_set = TRUE ) THEN
1372
1373 /* Bug 7132835 When we create 2 revison thru EGO_ITEM_PUB.process_item API
1374 they are adding sysdate + 1 sec to initial rev and given rev gets created
1375 only if effectivty date being passed is greater than sysdate+1 sec.
1376 By this eco errors out as it does not have current implemnted rev for the
1377 revised item.So updating implementaion date and effectivity date of initial
1378 rev to sysdate.*/
1379
1380 IF c_effectivity_date%ISOPEN THEN
1381 CLOSE c_effectivity_date;
1382 END IF;
1383
1384 OPEN c_effectivity_date (
1385 p_organization_id => l_item_output_obj.organization_id,
1386 p_inventory_item_id => l_item_output_obj.inventory_item_id
1387 );
1388
1389 FETCH c_effectivity_date INTO l_revision;
1390 CLOSE c_effectivity_date;
1391
1392 INV_EBI_UTIL.debug_line('STEP 110: START update mtl_item_revisions_b');
1393
1394 UPDATE mtl_item_revisions_b
1395 SET
1396 implementation_date = sysdate,
1397 effectivity_date = sysdate,
1398 creation_date = sysdate,
1399 last_update_date = sysdate,
1400 last_updated_by = DECODE(last_updated_by, NULL, fnd_global.user_id,last_updated_by),
1401 created_by = DECODE(last_updated_by, NULL, fnd_global.user_id,last_updated_by)
1402 WHERE
1403 inventory_item_id = l_item_output_obj.inventory_item_id AND
1404 organization_id = l_item_output_obj.organization_id AND
1405 revision = l_revision;
1406
1407 INV_EBI_UTIL.debug_line('STEP 120: END update mtl_item_revisions_b');
1408 END IF;
1409
1410 ELSE
1411 x_update_item_tbl.EXTEND(1);
1412
1413 l_update_item_count := x_update_item_tbl.COUNT;
1414 l_item_count := l_item_count + 1;
1415
1416 x_update_item_tbl(l_update_item_count) := INV_EBI_GET_ITEM_OUTPUT_OBJ(NULL,NULL,NULL,NULL);
1417 x_update_item_tbl(l_update_item_count).item_obj := l_eco_obj.eco_revised_item_type(i).item;
1418
1419 --Transfer Engg Item to Manufacturing
1420 INV_EBI_UTIL.debug_line('STEP 130: BEFORE CALLING transfer_engg_item_mfg');
1421 transfer_engg_item_mfg(
1422 p_item => l_eco_obj.eco_revised_item_type(i).item
1423 ,p_alt_bom_designator => l_eco_obj.eco_revised_item_type(i).alternate_bom_code
1424 ,x_out => l_eco_output_obj
1425 );
1426
1427 INV_EBI_UTIL.debug_line('STEP 140: AFTER CALLING transfer_engg_item_mfg STATUS: '||l_eco_output_obj.output_status.return_status);
1428
1429 IF (l_eco_output_obj.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1430 x_out.output_status.msg_data := l_eco_output_obj.output_status.msg_data;
1431 x_out.output_status.msg_count := l_eco_output_obj.output_status.msg_count;
1432 RAISE fnd_api.g_exc_unexpected_error;
1433 END IF;
1434
1435 END IF;
1436 END LOOP;
1437 END IF;
1438 x_eco_obj := l_eco_obj;
1439 IF FND_API.to_boolean(p_commit) THEN
1440 COMMIT;
1441 END IF;
1442
1443 INV_EBI_UTIL.debug_line('STEP 150: END INSIDE INV_EBI_CHANGE_ORDER_PUB.process_change_order_items STATUS: '|| x_out.output_status.return_status);
1444
1445
1446 EXCEPTION
1447 WHEN FND_API.g_exc_unexpected_error THEN
1448 ROLLBACK TO inv_ebi_chg_items_save_pnt;
1449
1450 IF c_effectivity_date%ISOPEN THEN
1451 CLOSE c_effectivity_date;
1452 END IF;
1453
1454 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
1455
1456 IF(x_out.output_status.msg_data IS NULL) THEN
1457
1458 x_out.output_status.error_table := INV_EBI_UTIL.get_error_table();
1459
1460 IF (x_out.output_status.error_table IS NOT NULL AND x_out.output_status.error_table.COUNT > 0) THEN
1461
1462 x_out.output_status.msg_data := INV_EBI_UTIL.get_error_table_msgtxt(x_out.output_status.error_table);
1463
1464 IF(x_out.output_status.msg_data IS NULL) THEN
1465 FND_MSG_PUB.count_and_get(
1466 p_encoded => FND_API.g_false
1467 ,p_count => x_out.output_status.msg_data
1468 ,p_data => x_out.output_status.msg_data
1469 );
1470 END IF;
1471 END IF;
1472 END IF;
1473
1474 WHEN OTHERS THEN
1475 ROLLBACK TO inv_ebi_chg_items_save_pnt;
1476
1477 IF c_effectivity_date%ISOPEN THEN
1478 CLOSE c_effectivity_date;
1479 END IF;
1480
1481 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
1482 x_out.output_status.error_table := INV_EBI_UTIL.get_error_table();
1483
1484 IF (x_out.output_status.error_table IS NOT NULL AND x_out.output_status.error_table.COUNT > 0) THEN
1485 x_out.output_status.msg_data := INV_EBI_UTIL.get_error_table_msgtxt(x_out.output_status.error_table);
1486 END IF;
1487
1488 IF (x_out.output_status.msg_data IS NOT NULL) THEN
1489 x_out.output_status.msg_data := x_out.output_status.msg_data||' -> INV_EBI_CHANGE_ORDER_PUB.process_change_order_items ';
1490 ELSE
1491 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_PUB.process_change_order_items ';
1492 END IF;
1493
1494 END process_change_order_items;
1495
1496 /************************************************************************************
1497 -- API name : process_update_items
1498 -- Type : Private
1499 -- Function :
1500 -- This API is used to update chnage order items
1501 -- Added this API for Bug 8397083
1502 ************************************************************************************/
1503 PROCEDURE process_update_items(
1504 p_commit IN VARCHAR2 := FND_API.g_false
1505 ,p_update_item_tbl IN inv_ebi_item_attr_tbl
1506 ,x_out OUT NOCOPY inv_ebi_item_output_obj
1507 ) IS
1508
1509 l_output_status inv_ebi_output_status;
1510
1511 BEGIN
1512 SAVEPOINT inv_ebi_proc_upd_item_save_pnt;
1513 FND_MSG_PUB.initialize;
1514 INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_CHANGE_ORDER_PUB.process_update_items');
1515 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
1516 x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL,NULL);
1517
1518 IF(p_update_item_tbl IS NOT NULL AND p_update_item_tbl.COUNT > 0) THEN
1519
1520 FOR i IN 1..p_update_item_tbl.COUNT LOOP
1521
1522 INV_EBI_UTIL.debug_line('STEP 20: BEFORE CALLING INV_EBI_ITEM_HELPER.sync_item');
1523
1524 INV_EBI_ITEM_HELPER.sync_item(
1525 p_commit => FND_API.g_false
1526 ,p_operation => INV_EBI_ITEM_PUB.g_otype_update
1527 ,p_item => p_update_item_tbl(i).item_obj
1528 ,x_out => x_out
1529 );
1530 INV_EBI_UTIL.debug_line('STEP 30: AFTER CALLING INV_EBI_ITEM_HELPER.sync_item for item updation STATUS: '|| x_out.output_status.return_status);
1531 IF (x_out.output_status.return_status <> fnd_api.g_ret_sts_success) THEN
1532 x_out.item_number := p_update_item_tbl(i).item_obj.main_obj_type.item_number;
1533 x_out.organization_code := p_update_item_tbl(i).item_obj.main_obj_type.organization_code;
1534 RAISE fnd_api.g_exc_unexpected_error;
1535 END IF;
1536 END LOOP;
1537 END IF;
1538 INV_EBI_UTIL.debug_line('STEP 40: END INSIDE INV_EBI_CHANGE_ORDER_PUB.process_update_items');
1539 EXCEPTION
1540 WHEN FND_API.g_exc_unexpected_error THEN
1541 ROLLBACK TO inv_ebi_proc_upd_item_save_pnt;
1542 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
1543 IF(x_out.output_status.msg_data IS NULL) THEN
1544
1545 x_out.output_status.error_table := INV_EBI_UTIL.get_error_table();
1546
1547 IF (x_out.output_status.error_table IS NOT NULL AND x_out.output_status.error_table.COUNT > 0) THEN
1548
1549 x_out.output_status.msg_data := INV_EBI_UTIL.get_error_table_msgtxt(x_out.output_status.error_table);
1550
1551 IF(x_out.output_status.msg_data IS NULL) THEN
1552 FND_MSG_PUB.count_and_get(
1553 p_encoded => FND_API.g_false
1554 ,p_count => x_out.output_status.msg_count
1555 ,p_data => x_out.output_status.msg_data
1556 );
1557 END IF;
1558 END IF;
1559 END IF;
1560
1561 WHEN OTHERS THEN
1562 ROLLBACK TO inv_ebi_proc_upd_item_save_pnt;
1563 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
1564
1565 x_out.output_status.error_table := INV_EBI_UTIL.get_error_table();
1566
1567 IF (x_out.output_status.error_table IS NOT NULL AND x_out.output_status.error_table.COUNT > 0) THEN
1568 x_out.output_status.msg_data := INV_EBI_UTIL.get_error_table_msgtxt(x_out.output_status.error_table);
1569 END IF;
1570
1571 IF (x_out.output_status.msg_data IS NOT NULL) THEN
1572 x_out.output_status.msg_data := x_out.output_status.msg_data||' -> INV_EBI_CHANGE_ORDER_PUB.process_update_items ';
1573 ELSE
1574 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_PUB.process_update_items ';
1575 END IF;
1576 END process_update_items;
1577
1578 /************************************************************************************
1579 -- API name : process_bom
1580 -- Type : Private
1581 -- Function :
1582 -- This API is used create BOM
1583 -- Added this API for Bug 8397083
1584 ************************************************************************************/
1585 PROCEDURE process_bom(
1586 p_commit IN VARCHAR2 := FND_API.g_false
1587 ,p_eco_obj_list IN inv_ebi_eco_obj_tbl
1588 ,x_eco_obj_list OUT NOCOPY inv_ebi_eco_obj_tbl
1589 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
1590 ) IS
1591
1592 l_eco_obj inv_ebi_eco_obj;
1593 l_replicate_bom_item_obj inv_ebi_revised_item_obj;
1594 l_common_assy_item_tbl inv_ebi_revised_item_tbl;
1595 l_replicate_bom_tbl inv_ebi_revised_item_tbl;
1596 l_common_assy_item_cnt NUMBER := 0;
1597 l_common_org_tbl FND_TABLE_OF_VARCHAR2_30;
1598 l_replicate_bom_org_tbl FND_TABLE_OF_VARCHAR2_30;
1599 l_common_eco_tbl FND_TABLE_OF_VARCHAR2_30;
1600 l_replicate_bom_eco_tbl FND_TABLE_OF_VARCHAR2_30;
1601 l_replicate_bom_cnt NUMBER := 0;
1602 l_common_bom_found VARCHAR2(1);
1603 l_replicate_bom_found VARCHAR2(1);
1604 l_output_status inv_ebi_output_status;
1605 l_out inv_ebi_eco_output_obj;
1606 l_eco_idx NUMBER;
1607 l_item_idx NUMBER;
1608 l_name_value_tbl inv_ebi_name_value_tbl;
1609 l_is_bom_exists VARCHAR2(1);
1610
1611
1612 BEGIN
1613 SAVEPOINT inv_ebi_process_bom_save_pnt;
1614
1615 FND_MSG_PUB.initialize;
1616
1617 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
1618 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
1619
1620 l_common_assy_item_tbl := inv_ebi_revised_item_tbl();
1621 l_replicate_bom_tbl := inv_ebi_revised_item_tbl();
1622 l_common_org_tbl := FND_TABLE_OF_VARCHAR2_30();
1623 l_replicate_bom_org_tbl := FND_TABLE_OF_VARCHAR2_30();
1624 l_common_eco_tbl := FND_TABLE_OF_VARCHAR2_30();
1625 l_replicate_bom_eco_tbl := FND_TABLE_OF_VARCHAR2_30();
1626 x_eco_obj_list := inv_ebi_eco_obj_tbl();
1627 INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_CHANGE_ORDER_PUB.process_bom');
1628 IF(p_eco_obj_list IS NOT NULL AND p_eco_obj_list.COUNT > 0) THEN
1629 x_eco_obj_list.EXTEND(p_eco_obj_list.COUNT);
1630
1631 FOR i in 1..p_eco_obj_list.count
1632 LOOP
1633
1634 l_eco_obj := p_eco_obj_list(i);
1635 x_eco_obj_list(i) := p_eco_obj_list(i);
1636
1637 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
1638
1639 IF( l_eco_obj.eco_revised_item_type IS NOT NULL AND
1640 l_eco_obj.eco_revised_item_type.COUNT > 0) THEN
1641
1642 FOR j IN 1..l_eco_obj.eco_revised_item_type.COUNT LOOP
1643
1644 l_common_bom_found := FND_API.g_false;
1645 l_replicate_bom_found := FND_API.g_false;
1646
1647
1648 IF(l_eco_obj.eco_revised_item_type(j).structure_header IS NOT NULL) THEN
1649 IF(l_eco_obj.eco_revised_item_type(j).structure_header.common_assembly_item_name IS NOT NULL
1650 AND l_eco_obj.eco_revised_item_type(j).structure_header.common_assembly_item_name <> fnd_api.g_miss_char
1651 AND l_eco_obj.eco_revised_item_type(j).structure_header.common_organization_code IS NOT NULL
1652 AND l_eco_obj.eco_revised_item_type(j).structure_header.common_organization_code <> fnd_api.g_miss_char
1653 ) THEN
1654 INV_EBI_UTIL.debug_line('STEP 20: Commoning Needed '||' ECO NAME: '|| l_eco_obj.eco_change_order_type.eco_name ||
1655 'ORG CODE : '|| l_eco_obj.eco_change_order_type.organization_code);
1656 l_common_assy_item_tbl.EXTEND(1);
1657 l_common_org_tbl.EXTEND(1);
1658 l_common_eco_tbl.EXTEND(1);
1659 l_common_assy_item_cnt := l_common_assy_item_cnt+1;
1660 l_common_assy_item_tbl(l_common_assy_item_cnt) := l_eco_obj.eco_revised_item_type(j);
1661 l_common_org_tbl(l_common_assy_item_cnt) := l_eco_obj.eco_change_order_type.organization_code;
1662 l_common_eco_tbl(l_common_assy_item_cnt) := l_eco_obj.eco_change_order_type.eco_name;
1663 l_common_bom_found := fnd_api.g_true;
1664
1665 END IF;
1666 END IF;
1667
1668 IF(l_eco_obj.eco_revised_item_type(j).orignal_bom_reference IS NOT NULL) THEN
1669
1670
1671 IF( l_eco_obj.eco_revised_item_type(j).orignal_bom_reference.item_name IS NOT NULL
1672 AND l_eco_obj.eco_revised_item_type(j).orignal_bom_reference.item_name <> fnd_api.g_miss_char
1673 AND l_eco_obj.eco_revised_item_type(j).orignal_bom_reference.ORGANIZATION_CODE IS NOT NULL
1674 AND l_eco_obj.eco_revised_item_type(j).orignal_bom_reference.ORGANIZATION_CODE <> fnd_api.g_miss_char
1675 ) THEN
1676
1677 l_is_bom_exists := INV_EBI_CHANGE_ORDER_HELPER.is_bom_exists(
1678 p_item_number => l_eco_obj.eco_revised_item_type(j).revised_item_name,
1679 p_organization_code => l_eco_obj.eco_change_order_type.organization_code,
1680 p_alternate_bom_code => l_eco_obj.eco_revised_item_type(j).alternate_bom_code
1681 );
1682
1683 IF(l_is_bom_exists = FND_API.g_false) THEN
1684 INV_EBI_UTIL.debug_line('STEP 30: Replication Needed');
1685 l_replicate_bom_tbl.EXTEND(1);
1686 l_replicate_bom_org_tbl.EXTEND(1);
1687 l_replicate_bom_eco_tbl.EXTEND(1);
1688 l_replicate_bom_cnt := l_replicate_bom_cnt+1;
1689
1690 l_replicate_bom_org_tbl(l_replicate_bom_cnt) := l_eco_obj.eco_change_order_type.organization_code;
1691 l_replicate_bom_eco_tbl(l_replicate_bom_cnt) := l_eco_obj.eco_change_order_type.eco_name;
1692
1693 INV_EBI_UTIL.debug_line('STEP 40: BEFORE CALLING INV_EBI_CHANGE_ORDER_HELPER.transform_replicate_bom_info');
1694 INV_EBI_CHANGE_ORDER_HELPER.transform_replicate_bom_info(
1695 p_eco_obj_list => p_eco_obj_list
1696 ,p_revised_item_obj => l_eco_obj.eco_revised_item_type(j)
1697 ,x_revised_item_obj => l_eco_obj.eco_revised_item_type(j)
1698 ,x_out => l_out
1699 );
1700 INV_EBI_UTIL.debug_line('STEP 50: AFTER CALLING INV_EBI_CHANGE_ORDER_HELPER.transform_replicate_bom_info STATUS: '|| l_out.output_status.return_status);
1701 IF(l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1702 x_out.change_notice := l_eco_obj.eco_change_order_type.eco_name;
1703 x_out.organization_code := l_eco_obj.eco_change_order_type.organization_code;
1704 x_out.output_status.msg_data := l_out.output_status.msg_data;
1705 x_out.output_status.msg_count := l_out.output_status.msg_count;
1706 RAISE FND_API.g_exc_unexpected_error;
1707 END IF;
1708
1709 l_replicate_bom_tbl(l_replicate_bom_cnt) := l_eco_obj.eco_revised_item_type(j);
1710 l_replicate_bom_found := fnd_api.g_true;
1711
1712
1713 END IF;
1714 END IF;
1715 END IF;
1716
1717 IF( l_common_bom_found = FND_API.g_false
1718 AND l_replicate_bom_found = FND_API.g_false
1719 AND l_eco_obj.eco_revised_item_type(j).structure_header IS NOT NULL) THEN
1720
1721 INV_EBI_UTIL.debug_line('STEP 60: BEFORE CALLING INV_EBI_CHANGE_ORDER_HELPER.process_structure_header ' ||
1722 ' FOR Normal BOM '||' REVISED ITEM NAME: '|| l_eco_obj.eco_revised_item_type(j).revised_item_name);
1723 INV_EBI_CHANGE_ORDER_HELPER.process_structure_header(
1724 p_commit => FND_API.g_false
1725 ,p_organization_code => l_eco_obj.eco_change_order_type.organization_code
1726 ,p_assembly_item_name => l_eco_obj.eco_revised_item_type(j).revised_item_name
1727 ,p_alternate_bom_code => l_eco_obj.eco_revised_item_type(j).alternate_bom_code
1728 ,p_structure_header => l_eco_obj.eco_revised_item_type(j).structure_header
1729 ,p_component_item_tbl => l_eco_obj.eco_revised_item_type(j).component_item_tbl -- Bug 7192021
1730 ,p_name_val_list => inv_ebi_name_value_list(l_eco_obj.name_value_tbl)
1731 ,x_out => l_out
1732 );
1733 INV_EBI_UTIL.debug_line('STEP 70: AFTER CALLING INV_EBI_CHANGE_ORDER_HELPER.process_structure_header STATUS: '|| l_out.output_status.return_status);
1734 IF(l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1735 x_out.change_notice := l_eco_obj.eco_change_order_type.eco_name;
1736 x_out.organization_code := l_eco_obj.eco_change_order_type.organization_code;
1737 x_out.output_status.msg_data := l_out.output_status.msg_data;
1738 x_out.output_status.msg_count := l_out.output_status.msg_count;
1739 RAISE FND_API.g_exc_unexpected_error;
1740 END IF;
1741
1742 END IF;
1743 END LOOP;
1744 END IF;
1745 END LOOP;
1746 END IF;
1747
1748 IF(l_common_assy_item_tbl IS NOT NULL AND l_common_assy_item_tbl.COUNT > 0) THEN
1749 FOR l_common_assy_item_cnt IN 1..l_common_assy_item_tbl.COUNT LOOP
1750 FOR l_count IN 1..p_eco_obj_list.COUNT LOOP
1751
1752 IF( p_eco_obj_list(l_count).eco_change_order_type.eco_name = l_common_eco_tbl(l_common_assy_item_cnt)
1753 AND p_eco_obj_list(l_count).eco_change_order_type.organization_code = l_common_org_tbl(l_common_assy_item_cnt)
1754 ) THEN
1755 l_name_value_tbl := p_eco_obj_list(l_count).name_value_tbl;
1756
1757 END IF;
1758
1759 END LOOP;
1760 INV_EBI_UTIL.debug_line('STEP 80: BEFORE CALLING INV_EBI_CHANGE_ORDER_HELPER.process_structure_header '|| 'FOR COMMON BOM '||
1761 ' REVISED ITEM NAME: '|| l_common_assy_item_tbl(l_common_assy_item_cnt).revised_item_name ||
1762 ' COMMON ORG CODE: '|| l_common_assy_item_tbl(l_common_assy_item_cnt).structure_header.common_organization_code ||
1763 ' COMMON ASSY ITEM NAME: '|| l_common_assy_item_tbl(l_common_assy_item_cnt).structure_header.common_assembly_item_name);
1764 INV_EBI_CHANGE_ORDER_HELPER.process_structure_header(
1765 p_commit => FND_API.g_false
1766 ,p_organization_code => l_common_org_tbl(l_common_assy_item_cnt)
1767 ,p_assembly_item_name => l_common_assy_item_tbl(l_common_assy_item_cnt).revised_item_name
1768 ,p_alternate_bom_code => l_common_assy_item_tbl(l_common_assy_item_cnt).alternate_bom_code
1769 ,p_structure_header => l_common_assy_item_tbl(l_common_assy_item_cnt).structure_header
1770 ,p_component_item_tbl => l_common_assy_item_tbl(l_common_assy_item_cnt).component_item_tbl -- Bug 7192021
1771 ,p_name_val_list => inv_ebi_name_value_list(l_name_value_tbl)
1772 ,x_out => l_out
1773 );
1774 INV_EBI_UTIL.debug_line('STEP 90: AFTER CALLING INV_EBI_CHANGE_ORDER_HELPER.process_structure_header '
1775 || 'FOR COMMON BOM STATUS: '|| l_out.output_status.return_status);
1776 IF(l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1777 x_out.change_notice := l_common_eco_tbl(l_common_assy_item_cnt);
1778 x_out.organization_code := l_common_org_tbl(l_common_assy_item_cnt);
1779 x_out.output_status.msg_data := l_out.output_status.msg_data;
1780 x_out.output_status.msg_count := l_out.output_status.msg_count;
1781
1782 RAISE FND_API.g_exc_unexpected_error;
1783 END IF;
1784 END LOOP;
1785 END IF;
1786
1787 IF(l_replicate_bom_tbl IS NOT NULL AND l_replicate_bom_tbl.COUNT > 0) THEN
1788
1789 FOR l_replicate_bom_cnt IN 1..l_replicate_bom_tbl.COUNT LOOP
1790
1791 FOR l_count IN 1..p_eco_obj_list.COUNT LOOP
1792
1793 IF( p_eco_obj_list(l_count).eco_change_order_type.eco_name = l_replicate_bom_eco_tbl(l_replicate_bom_cnt)
1794 AND p_eco_obj_list(l_count).eco_change_order_type.organization_code = l_replicate_bom_org_tbl(l_replicate_bom_cnt)
1795 ) THEN
1796 l_name_value_tbl := p_eco_obj_list(l_count).name_value_tbl;
1797 l_eco_idx := l_count;
1798 FOR l_count1 IN 1..p_eco_obj_list(l_count).eco_revised_item_type.COUNT LOOP
1799 IF(p_eco_obj_list(l_count).eco_revised_item_type(l_count1).revised_item_name = l_replicate_bom_tbl(l_replicate_bom_cnt).revised_item_name) THEN
1800 l_item_idx := l_count1;
1801 END IF;
1802 END LOOP;
1803 END IF;
1804 END LOOP;
1805
1806 INV_EBI_UTIL.debug_line('STEP 100: BEFORE CALLING INV_EBI_CHANGE_ORDER_HELPER.process_replicate_bom '
1807 || 'FOR REPLICATE BOM ');
1808 INV_EBI_CHANGE_ORDER_HELPER.process_replicate_bom(
1809 p_organization_code => l_replicate_bom_org_tbl(l_replicate_bom_cnt)
1810 ,p_revised_item_obj => l_replicate_bom_tbl(l_replicate_bom_cnt)
1811 ,p_name_value_tbl => l_name_value_tbl
1812 ,x_revised_item_obj => l_replicate_bom_item_obj
1813 ,x_out => l_out
1814 );
1815 INV_EBI_UTIL.debug_line('STEP 110: AFTER CALLING INV_EBI_CHANGE_ORDER_HELPER.process_replicate_bom ' ||
1816 ' FOR REPLICATE BOM STATUS: '|| l_out.output_status.return_status);
1817
1818 x_eco_obj_list(l_eco_idx).eco_revised_item_type(l_item_idx) := l_replicate_bom_item_obj;
1819
1820 IF(l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1821
1822 x_out.change_notice := l_replicate_bom_eco_tbl(l_replicate_bom_cnt);
1823 x_out.organization_code := l_replicate_bom_org_tbl(l_replicate_bom_cnt);
1824 x_out.output_status.msg_data := l_out.output_status.msg_data;
1825 x_out.output_status.msg_count := l_out.output_status.msg_count;
1826
1827
1828 RAISE FND_API.g_exc_unexpected_error;
1829 END IF;
1830
1831 IF(l_replicate_bom_tbl(l_replicate_bom_cnt).structure_header IS NOT NULL) THEN
1832 INV_EBI_UTIL.debug_line('STEP 120: BEFORE CALLING INV_EBI_CHANGE_ORDER_HELPER.process_structure_header ' ||
1833 ' FOR REPLICATE BOM '||' REVISED ITEM NAME: '|| l_replicate_bom_tbl(l_replicate_bom_cnt).revised_item_name ||
1834 ' COMMON ASSY ITEM NAME: '|| l_replicate_bom_tbl(l_replicate_bom_cnt).structure_header.common_assembly_item_name ||
1835 ' COMMON ORG CODE: '|| l_replicate_bom_tbl(l_replicate_bom_cnt).structure_header.common_organization_code);
1836 INV_EBI_CHANGE_ORDER_HELPER.process_structure_header(
1837 p_commit => FND_API.g_false
1838 ,p_organization_code => l_replicate_bom_org_tbl(l_replicate_bom_cnt)
1839 ,p_assembly_item_name => l_replicate_bom_tbl(l_replicate_bom_cnt).revised_item_name
1840 ,p_alternate_bom_code => l_replicate_bom_tbl(l_replicate_bom_cnt).alternate_bom_code
1841 ,p_structure_header => l_replicate_bom_tbl(l_replicate_bom_cnt).structure_header
1842 ,p_component_item_tbl => l_replicate_bom_tbl(l_replicate_bom_cnt).component_item_tbl -- Bug 7192021
1843 ,p_name_val_list => inv_ebi_name_value_list(l_name_value_tbl)
1844 ,x_out => l_out
1845 );
1846
1847 INV_EBI_UTIL.debug_line('STEP 130: AFTER CALLING INV_EBI_CHANGE_ORDER_HELPER.process_structure_header STATUS: '|| l_out.output_status.return_status);
1848
1849 IF(l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1850
1851 x_out.change_notice := l_replicate_bom_eco_tbl(l_replicate_bom_cnt);
1852 x_out.organization_code := l_replicate_bom_org_tbl(l_replicate_bom_cnt);
1853 x_out.output_status.msg_data := l_out.output_status.msg_data;
1854 x_out.output_status.msg_count := l_out.output_status.msg_count;
1855
1856 RAISE FND_API.g_exc_unexpected_error;
1857 END IF;
1858 END IF;
1859 END LOOP;
1860 END IF;
1861 INV_EBI_UTIL.debug_line('STEP 140: END OF INV_EBI_CHANGE_ORDER_PUB.process_bom STATUS: '|| x_out.output_status.return_status);
1862 IF FND_API.to_boolean(p_commit) THEN
1863 COMMIT;
1864 END IF;
1865
1866 EXCEPTION
1867
1868 WHEN FND_API.g_exc_unexpected_error THEN
1869 ROLLBACK TO inv_ebi_process_bom_save_pnt;
1870 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
1871
1872 IF(x_out.output_status.msg_data IS NULL) THEN
1873
1874 x_out.output_status.error_table := INV_EBI_UTIL.get_error_table();
1875
1876 IF (x_out.output_status.error_table IS NOT NULL AND x_out.output_status.error_table.COUNT > 0) THEN
1877
1878 x_out.output_status.msg_data := INV_EBI_UTIL.get_error_table_msgtxt(x_out.output_status.error_table);
1879
1880 IF(x_out.output_status.msg_data IS NULL) THEN
1881 FND_MSG_PUB.count_and_get(
1882 p_encoded => FND_API.g_false
1883 ,p_count => x_out.output_status.msg_count
1884 ,p_data => x_out.output_status.msg_data
1885 );
1886 END IF;
1887
1888 END IF;
1889 END IF;
1890
1891 WHEN OTHERS THEN
1892 ROLLBACK TO inv_ebi_process_bom_save_pnt;
1893 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
1894
1895 x_out.output_status.error_table := INV_EBI_UTIL.get_error_table();
1896
1897 IF (x_out.output_status.error_table IS NOT NULL AND
1898 x_out.output_status.error_table.COUNT > 0) THEN
1899
1900 x_out.output_status.msg_data := INV_EBI_UTIL.get_error_table_msgtxt(x_out.output_status.error_table);
1901 END IF;
1902
1903 IF (x_out.output_status.msg_data IS NOT NULL) THEN
1904 x_out.output_status.msg_data := x_out.output_status.msg_data||' -> INV_EBI_CHANGE_ORDER_PUB.process_bom ';
1905 ELSE
1906 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_PUB.process_bom ';
1907 END IF;
1908
1909 END process_bom;
1910
1911 /************************************************************************************
1912 -- API name : process_change_order
1913 -- Type : Public
1914 -- Function :
1915 -- This API is used to
1916 -- Changed this API for Bug 8397083
1917 ************************************************************************************/
1918
1919 PROCEDURE process_change_order(
1920 p_commit IN VARCHAR2 := FND_API.g_false
1921 ,p_eco_obj IN inv_ebi_eco_obj
1922 ,x_out OUT NOCOPY inv_ebi_eco_output_obj
1923 ) IS
1924
1925 l_change_id NUMBER;
1926 l_change_notice VARCHAR2(10);
1927 l_organization_id NUMBER;
1928 l_organization_code VARCHAR2(3);
1929 l_output_status inv_ebi_output_status;
1930 l_curr_status_code NUMBER;
1931 l_status_code NUMBER;
1932 l_is_wf_Set BOOLEAN;
1933 l_is_plmwf_Set BOOLEAN;
1934 l_revitem_output_tbl inv_ebi_revitem_output_obj_tbl;
1935 l_revision VARCHAR2(3);
1936 l_change_type_code NUMBER;
1937 l_status_name VARCHAR2(30);
1938 l_out inv_ebi_eco_output_obj;
1939 l_item_output inv_ebi_item_output_obj;
1940 l_uda_output inv_ebi_uda_output_obj;
1941 l_pkdata ego_col_name_value_pair_array;
1942 l_pkcode ego_col_name_value_pair_array;
1943 l_change_order_type_id NUMBER;
1944 l_inventory_item_id NUMBER;
1945 l_api_version NUMBER:=1.0;
1946 l_is_task_template_set BOOLEAN := FALSE;
1947 BEGIN
1948 SAVEPOINT inv_ebi_chg_order_save_pnt;
1949 FND_MSG_PUB.initialize;
1950
1951 INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_CHANGE_ORDER_PUB.process_change_order' );
1952 INV_EBI_UTIL.debug_line('STEP 20: CHANGE NOTICE: '|| p_eco_obj.eco_change_order_type.eco_name ||
1953 ' ORG CODE: '|| p_eco_obj.eco_change_order_type.organization_code );
1954 l_uda_output := inv_ebi_uda_output_obj(NULL,NULL);
1955 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
1956 x_out := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
1957
1958 l_revitem_output_tbl := inv_ebi_revitem_output_obj_tbl();
1959
1960 bom_globals.set_bo_identifier(bom_globals.g_eco_bo);
1961
1962 INV_EBI_UTIL.debug_line('STEP 30: START CALLING INV_EBI_CHANGE_ORDER_HELPER.process_eco');
1963 INV_EBI_CHANGE_ORDER_HELPER.process_eco(
1964 p_commit => FND_API.g_false
1965 ,p_change_order => p_eco_obj.eco_change_order_type
1966 ,p_revision_type_tbl => p_eco_obj.eco_revision_type
1967 ,p_revised_item_type_tbl => p_eco_obj.eco_revised_item_type
1968 ,p_name_val_list => inv_ebi_name_value_list(p_eco_obj.name_value_tbl)
1969 ,x_out => l_out
1970 );
1971 INV_EBI_UTIL.debug_line('STEP 40: AFTER CALLING INV_EBI_CHANGE_ORDER_HELPER.process_eco STATUS: '|| l_out.output_status.return_status);
1972 IF (l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1973 x_out.output_status.msg_data := l_out.output_status.msg_data;
1974 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1975 END IF;
1976
1977 l_change_id := l_out.change_id;
1978 l_change_notice := l_out.change_notice;
1979 l_organization_id := l_out.organization_id;
1980 l_organization_code := l_out.organization_code;
1981
1982 l_is_wf_Set := INV_EBI_CHANGE_ORDER_HELPER.Check_Workflow_Process(p_change_order_type_id => p_eco_obj.eco_change_order_type.change_type_id
1983 ,p_priority_code => p_eco_obj.eco_change_order_type.priority_code
1984 );
1985
1986 --added for bug 13893810
1987 l_is_plmwf_Set := INV_EBI_CHANGE_ORDER_HELPER.Check_Workflow_In_PLM(p_change_id => l_change_id
1988 );
1989
1990 SELECT status_code
1991 INTO l_curr_status_code
1992 FROM eng_engineering_changes
1993 WHERE change_id = l_change_id;
1994
1995 l_status_name := p_eco_obj.eco_change_order_type.status_name;
1996
1997 IF( p_eco_obj.eco_change_order_type.status_code IS NOT NULL AND
1998 p_eco_obj.eco_change_order_type.status_code <> fnd_api.g_miss_num) THEN
1999
2000 l_status_name := INV_EBI_CHANGE_ORDER_HELPER.get_eco_status_name(p_eco_obj.eco_change_order_type.status_code);
2001
2002 END IF;
2003
2004 IF (LOWER(l_status_name) = 'implemented' ) THEN
2005 SELECT status_code
2006 INTO l_status_code
2007 FROM eng_change_statuses_vl
2008 WHERE LOWER(status_name) = 'scheduled'; -- Scheduled
2009 ELSE
2010 l_status_code := p_eco_obj.eco_change_order_type.status_code;
2011 END IF;
2012
2013 /*Bug 7218542:If some task is associated at organization level for the change order type
2014 we cannot promote it to scheduled status.*/
2015
2016 l_is_task_template_set := INV_EBI_CHANGE_ORDER_HELPER.is_task_template_set(
2017 p_change_order_type_id => p_eco_obj.eco_change_order_type.change_type_id
2018 ,p_organization_id => l_organization_id
2019 ,p_status_code => l_status_code
2020 );
2021
2022 IF ( (NOT l_is_wf_Set) AND
2023 (NOT l_is_task_template_set) AND
2024 (NOT l_is_plmwf_Set ) AND
2025 (l_status_code > l_curr_status_code) ) THEN
2026 --Bug 6833363
2027
2028 INV_EBI_UTIL.debug_line('STEP 50: START CALLING ENG_CHANGE_LIFECYCLE_UTIL.Change_Phase');
2029 ENG_CHANGE_LIFECYCLE_UTIL.Change_Phase(
2030 p_api_version => 1.0
2031 ,p_commit => FND_API.g_false
2032 ,p_object_name => 'ENG_CHANGE'
2033 ,p_change_id => l_change_id
2034 ,p_status_code => l_status_code
2035 ,p_action_type => ENG_CHANGE_LIFECYCLE_UTIL.G_ENG_PROMOTE -- promote/demote
2036 ,x_return_status => l_out.output_status.return_status
2037 ,x_msg_count => l_out.output_status.msg_count
2038 ,x_msg_data => l_out.output_status.msg_data
2039 );
2040 INV_EBI_UTIL.debug_line('STEP 60: AFTER CALLING ENG_CHANGE_LIFECYCLE_UTIL.Change_Phase STATUS: '|| l_out.output_status.return_status);
2041 IF (l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2042 x_out.output_status.msg_data := l_out.output_status.msg_data;
2043 x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> ECO Name:' || l_change_notice || ' -> Org Code: ' || l_organization_code;
2044 RAISE FND_API.g_exc_unexpected_error;
2045 END IF;
2046 END IF;
2047
2048 --Processing of Udas
2049 IF (INV_EBI_UTIL.is_pim_installed) THEN
2050 IF ((p_eco_obj.eco_change_order_type IS NOT NULL AND p_eco_obj.eco_change_order_type.change_order_uda IS NOT NULL)
2051 AND p_eco_obj.eco_change_order_type.change_order_uda.attribute_group_tbl.COUNT > 0) THEN
2052 IF ((p_eco_obj.eco_change_order_type.eco_name IS NOT NULL)
2053 AND (p_eco_obj.eco_change_order_type.organization_id IS NULL OR p_eco_obj.eco_change_order_type.organization_id =FND_API.g_miss_num)
2054 AND (p_eco_obj.eco_change_order_type.organization_code IS NOT NULL OR p_eco_obj.eco_change_order_type.organization_code <> FND_API.g_miss_char)) THEN
2055
2056 SELECT change_order_type_id INTO l_change_order_type_id
2057 FROM eng_engineering_changes
2058 WHERE change_notice = p_eco_obj.eco_change_order_type.eco_name
2059 AND organization_id = l_organization_id;
2060
2061 END IF;
2062 l_pkdata := ego_col_name_value_pair_array();
2063 l_pkdata.extend();
2064 l_pkdata(1) := ego_col_name_value_pair_obj('CHANGE_ID',l_change_id);
2065 l_pkcode := ego_col_name_value_pair_array();
2066 l_pkcode.extend();
2067 l_pkcode(1) := ego_col_name_value_pair_obj('CHANGE_TYPE_ID',l_change_order_type_id);
2068
2069 --To process Change order header udas
2070 INV_EBI_UTIL.debug_line('STEP 70: BEFORE CALLING INV_EBI_CHANGE_ORDER_HELPER.process_uda');
2071 INV_EBI_CHANGE_ORDER_HELPER.process_uda(
2072 p_uda_input_obj => p_eco_obj.eco_change_order_type.change_order_uda
2073 ,p_commit => fnd_api.g_false
2074 ,p_object_name => 'ENG_CHANGE'
2075 ,p_data_level => 'CHANGE_LEVEL'
2076 ,p_pk_column_name_value_pairs => l_pkdata
2077 ,p_class_code_name_value_pairs => l_pkcode
2078 ,x_uda_output_obj => l_out
2079 );
2080 INV_EBI_UTIL.debug_line('STEP 80: AFTER CALLING INV_EBI_CHANGE_ORDER_HELPER.process_uda STATUS: '|| l_out.output_status.return_status);
2081 END IF;
2082 IF(l_out.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2083 x_out.output_status.msg_data := l_out.output_status.msg_data;
2084 RAISE fnd_api.g_exc_unexpected_error;
2085 END IF;
2086
2087 l_uda_output := inv_ebi_uda_output_obj(l_out.uda_output.failed_row_id_list,l_out.uda_output.errorcode);
2088
2089 IF(p_eco_obj.eco_revised_item_type IS NOT NULL AND p_eco_obj.eco_revised_item_type.COUNT > 0) THEN
2090 FOR i IN 1..p_eco_obj.eco_revised_item_type.COUNT
2091 LOOP
2092
2093 --Commented for bug 10416673
2094 /*IF ( p_eco_obj.eco_revised_item_type(i).item.uda_type IS NOT NULL AND
2095 p_eco_obj.eco_revised_item_type(i).item.uda_type.attribute_group_tbl.COUNT > 0) THEN
2096
2097 -- To process Revised Item udas
2098
2099 l_inventory_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id (
2100 p_organization_id => l_organization_id
2101 ,p_item_number => p_eco_obj.eco_revised_item_type(i).revised_item_name
2102 );
2103 INV_EBI_UTIL.debug_line('STEP 90: START CALLING INV_EBI_ITEM_HELPER.process_item_uda'||'INV ITEM ID: '||l_inventory_item_id ||
2104 'ORG ID: '||l_organization_id|| 'CATALOG GROUP ID: '||p_eco_obj.eco_revised_item_type(i).item.main_obj_type.item_catalog_group_id ||
2105 'REVISED ITEM REVISION: '||p_eco_obj.eco_revised_item_type(i).new_revised_item_revision);
2106 INV_EBI_ITEM_HELPER.process_item_uda(
2107 p_api_version => l_api_version
2108 ,p_inventory_item_id => l_inventory_item_id
2109 ,p_organization_id => l_organization_id
2110 ,p_item_catalog_group_id => p_eco_obj.eco_revised_item_type(i).item.main_obj_type.item_catalog_group_id
2111 ,p_revision_id => NULL
2112 ,p_revision_code => p_eco_obj.eco_revised_item_type(i).new_revised_item_revision
2113 ,p_uda_input_obj => p_eco_obj.eco_revised_item_type(i).item.uda_type
2114 ,p_commit => fnd_api.g_false
2115 ,x_uda_output_obj => l_item_output
2116 );
2117 INV_EBI_UTIL.debug_line('STEP 100: AFTER CALLING INV_EBI_ITEM_HELPER.process_item_uda STATUS: '|| l_item_output.output_status.return_status);
2118 END IF;
2119
2120 IF (l_item_output.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2121 x_out.output_status.return_status := l_item_output.output_status.msg_data;
2122 RAISE FND_API.g_exc_unexpected_error;
2123 END IF;
2124
2125 l_uda_output := inv_ebi_uda_output_obj(l_item_output.uda_output.failed_row_id_list,l_item_output.uda_output.errorcode);*/
2126
2127 -- To process Component level udas and structure header level udas
2128 INV_EBI_UTIL.debug_line('STEP 90: START CALLING INV_EBI_CHANGE_ORDER_HELPER.process_change_order_uda');
2129 INV_EBI_CHANGE_ORDER_HELPER.process_change_order_uda(
2130 p_commit => FND_API.g_false
2131 ,p_organization_code => p_eco_obj.eco_change_order_type.organization_code
2132 ,p_eco_name => p_eco_obj.eco_change_order_type.eco_name
2133 ,p_alternate_bom_code => p_eco_obj.eco_revised_item_type(i).alternate_bom_code
2134 ,p_revised_item_name => p_eco_obj.eco_revised_item_type(i).revised_item_name
2135 ,p_component_tbl => p_eco_obj.eco_revised_item_type(i).component_item_tbl
2136 ,p_structure_header => p_eco_obj.eco_revised_item_type(i).structure_header
2137 ,x_out => l_out
2138 );
2139 INV_EBI_UTIL.debug_line('STEP 100: AFTER CALLING INV_EBI_CHANGE_ORDER_HELPER.process_change_order_uda STATUS: '|| l_out.output_status.return_status);
2140 IF (l_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2141 x_out.output_status.msg_data := l_out.output_status.msg_data;
2142 RAISE FND_API.g_exc_unexpected_error;
2143 END IF;
2144 END LOOP;
2145 END IF;
2146 l_uda_output := inv_ebi_uda_output_obj(l_out.uda_output.failed_row_id_list,l_out.uda_output.errorcode);
2147 END IF;
2148 --End of Udas processing
2149 INV_EBI_UTIL.debug_line('STEP 110: END Processing UDAS');
2150 x_out := inv_ebi_eco_output_obj( l_change_notice,l_change_id,l_organization_id,l_organization_code,
2151 l_out.output_status,NULL,l_uda_output);
2152
2153 IF FND_API.to_boolean(p_commit) THEN
2154 COMMIT;
2155 END IF;
2156 INV_EBI_UTIL.debug_line('STEP 120: END INSIDE INV_EBI_CHANGE_ORDER_PUB.process_change_order'||'RETURN STATUS: '||x_out.output_status.return_status);
2157 EXCEPTION
2158 WHEN FND_API.g_exc_unexpected_error THEN
2159 ROLLBACK TO inv_ebi_chg_order_save_pnt;
2160
2161 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2162 IF(x_out.output_status.msg_data IS NULL) THEN
2163 x_out.output_status.error_table := INV_EBI_UTIL.get_error_table();
2164
2165 IF (x_out.output_status.error_table IS NOT NULL AND x_out.output_status.error_table.COUNT > 0) THEN
2166 x_out.output_status.msg_data := INV_EBI_UTIL.get_error_table_msgtxt(x_out.output_status.error_table);
2167
2168 IF(x_out.output_status.msg_data IS NULL) THEN
2169 FND_MSG_PUB.count_and_get(
2170 p_encoded => FND_API.g_false
2171 ,p_count => x_out.output_status.msg_count
2172 ,p_data => x_out.output_status.msg_data
2173 );
2174 END IF;
2175
2176 END IF;
2177 END IF;
2178 WHEN OTHERS THEN
2179 ROLLBACK TO inv_ebi_chg_order_save_pnt;
2180
2181 x_out.output_status.error_table := INV_EBI_UTIL.get_error_table();
2182 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2183
2184 IF (x_out.output_status.error_table IS NOT NULL AND x_out.output_status.error_table.COUNT > 0) THEN
2185 x_out.output_status.msg_data := INV_EBI_UTIL.get_error_table_msgtxt(x_out.output_status.error_table);
2186 END IF;
2187 IF (x_out.output_status.msg_data IS NOT NULL) THEN
2188 x_out.output_status.msg_data := x_out.output_status.msg_data||' -> INV_EBI_CHANGE_ORDER_PUB.process_change_order ';
2189 ELSE
2190 x_out.output_status.msg_data := SQLERRM||' INV_EBI_CHANGE_ORDER_PUB.process_change_order ';
2191 END IF;
2192
2193 END process_change_order;
2194
2195 /************************************************************************************
2196 -- API name : process_change_order_list_core
2197 -- Type : Public
2198 -- Function :
2199 -- This API is used to get list of change orders
2200 -- Changed this API for Bug 8397083
2201 ************************************************************************************/
2202
2203 PROCEDURE process_change_order_list_core(
2204 p_commit IN VARCHAR2 := fnd_api.g_false
2205 ,p_eco_obj_list IN inv_ebi_eco_obj_tbl
2206 ,x_out OUT NOCOPY inv_ebi_eco_output_obj_tbl
2207 ,x_return_status OUT NOCOPY VARCHAR2
2208 ,x_msg_count OUT NOCOPY NUMBER
2209 ,x_msg_data OUT NOCOPY VARCHAR2
2210 )
2211 IS
2212 l_inv_ebi_eco_obj inv_ebi_eco_obj;
2213 l_eco_obj inv_ebi_eco_obj;
2214 l_inv_ebi_eco_output_obj inv_ebi_eco_output_obj;
2215 l_part_err_msg VARCHAR2(32000);
2216 l_eco_name VARCHAR2(10);
2217 l_org_code VARCHAR2(3);
2218 l_is_master_org VARCHAR2(1);
2219 l_updated_item_tbl inv_ebi_item_attr_tbl;
2220 l_upd_item_tbl inv_ebi_item_attr_tbl;
2221 l_assign_item_to_child_org VARCHAR2(1):= fnd_api.g_false;
2222 l_item_output_obj inv_ebi_item_output_obj;
2223 l_eco_obj_list inv_ebi_eco_obj_tbl;
2224 l_eco_obj_tbl inv_ebi_eco_obj_tbl;
2225 l_upd_eco_name_tbl FND_TABLE_OF_VARCHAR2_30;
2226 l_upd_eco_idx_tbl FND_TABLE_OF_NUMBER;
2227 l_index NUMBER ;
2228 l_revitem_output_tbl inv_ebi_revitem_output_obj_tbl;
2229 l_output_status inv_ebi_output_status;
2230 l_count NUMBER;
2231 l_upd_item_cnt NUMBER := 0;
2232 l_upd_child_item_cnt NUMBER := 0;
2233 l_item_name mtl_system_items_kfv.concatenated_segments%TYPE;
2234 l_organization_code mtl_parameters.organization_code%TYPE;
2235 l_organization_id NUMBER;
2236 l_inventory_item_id NUMBER;
2237 l_api_version NUMBER:=1.0;
2238
2239 BEGIN
2240 -- This Part of Code to set the APPS Context BUG 8712091
2241 /* IF p_eco_obj_list IS NOT NULL AND p_eco_obj_list.COUNT>0 THEN
2242 FOR i IN p_eco_obj_list.FIRST..p_eco_obj_list.LAST
2243 LOOP
2244 IF p_eco_obj_list(i).NAME_VALUE_TBL IS NOT NULL AND p_eco_obj_list(i).NAME_VALUE_TBL.COUNT>0
2245 THEN
2246 INV_EBI_UTIL.set_apps_context(p_eco_obj_list(i).NAME_VALUE_TBL);
2247 END IF;
2248 EXIT;
2249 END LOOP;
2250 END IF;
2251 */
2252
2253 SAVEPOINT inv_ebi_prc_chg_ord_save_pnt;
2254 ERROR_HANDLER.Initialize;
2255 FND_MSG_PUB.initialize;
2256 INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_CHANGE_ORDER_PUB.process_change_order_list_core');
2257 INV_EBI_UTIL.debug_line( ' **************** Apps Context Details ****************' );
2258 INV_EBI_UTIL.debug_line(' User Id: ' || FND_GLOBAL.USER_ID || '; Responsibility Application id: ' ||
2259 FND_GLOBAL.RESP_APPL_ID || '; Responsibility Id: ' || FND_GLOBAL.RESP_ID ||
2260 '; Security Group id: '|| FND_GLOBAL.SECURITY_GROUP_ID ||'; User Lang: '|| USERENV('LANG') );
2261 INV_EBI_UTIL.debug_line( ' **************** End of Apps Context ****************' );
2262
2263 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2264 x_out := inv_ebi_eco_output_obj_tbl();
2265 l_revitem_output_tbl := inv_ebi_revitem_output_obj_tbl();
2266 l_eco_obj_tbl := inv_ebi_eco_obj_tbl();
2267 l_eco_obj_tbl := p_eco_obj_list;
2268
2269 IF (l_eco_obj_tbl IS NOT NULL AND l_eco_obj_tbl.count > 0) THEN
2270
2271 x_out.extend(l_eco_obj_tbl.count);
2272
2273 l_upd_eco_name_tbl := FND_TABLE_OF_VARCHAR2_30();
2274 l_upd_item_tbl := inv_ebi_item_attr_tbl();
2275 l_updated_item_tbl := inv_ebi_item_attr_tbl(); -- Bug 8830143
2276 l_upd_eco_idx_tbl := FND_TABLE_OF_NUMBER();
2277
2278
2279 FOR i in 1..l_eco_obj_tbl.count
2280 LOOP
2281 l_inv_ebi_eco_obj := l_eco_obj_tbl(i);
2282 l_eco_name := l_eco_obj_tbl(i).eco_change_order_type.eco_name;
2283 l_org_code := l_eco_obj_tbl(i).eco_change_order_type.organization_code;
2284
2285 l_is_master_org := INV_EBI_UTIL.is_master_org(l_org_code);
2286
2287 IF( l_is_master_org = fnd_api.g_true ) THEN
2288
2289 INV_EBI_UTIL.debug_line('STEP 20: BEFORE CALLING INV_EBI_CHANGE_ORDER_PUB.process_change_order_items for Master ORG'||
2290 ' Organization Code: '||l_eco_obj_tbl(i).eco_change_order_type.organization_code||
2291 ' ECO Name: '||l_eco_obj_tbl(i).eco_change_order_type.eco_name);
2292
2293
2294 --To Create Items in Master Org
2295 INV_EBI_CHANGE_ORDER_PUB.process_change_order_items(
2296 p_commit => p_commit
2297 ,p_eco_obj => l_inv_ebi_eco_obj
2298 ,p_update_item_tbl => l_upd_item_tbl
2299 ,x_update_item_tbl => l_updated_item_tbl
2300 ,x_out => l_item_output_obj
2301 ,x_eco_obj => l_eco_obj
2302 );
2303
2304 IF l_item_output_obj.output_status.return_status <> FND_API.G_RET_STS_SUCCESS THEN
2305 x_out(i) := inv_ebi_eco_output_obj( l_eco_name,NULL,NULL,l_org_code,l_item_output_obj.output_status,NULL,NULL);
2306 RAISE FND_API.g_exc_unexpected_error;
2307 END IF;
2308
2309 l_eco_obj_tbl(i) := l_eco_obj;
2310
2311 IF(l_updated_item_tbl IS NOT NULL AND l_updated_item_tbl.COUNT> 0) THEN
2312
2313 FOR l_count IN 1..l_updated_item_tbl.COUNT LOOP
2314
2315 l_upd_item_cnt := l_upd_item_cnt + 1;
2316
2317 l_upd_eco_name_tbl.EXTEND(1);
2318 l_upd_eco_name_tbl(l_upd_item_cnt) := l_eco_name;
2319
2320 l_upd_eco_idx_tbl.EXTEND(1);
2321 l_upd_eco_idx_tbl(l_upd_item_cnt) := i;
2322
2323 END LOOP;
2324 END IF;
2325
2326 INV_EBI_UTIL.debug_line('STEP 30: AFTER CALLING INV_EBI_CHANGE_ORDER_PUB.process_change_order_items for Master ORG'||
2327 ' Organization Code: '||l_eco_obj_tbl(i).eco_change_order_type.organization_code||
2328 ' ECO Name: '||l_eco_obj_tbl(i).eco_change_order_type.eco_name||' RETURN STATUS: '||l_item_output_obj.output_status.return_status);
2329
2330 END IF;
2331 END LOOP;
2332
2333 FOR i in 1..l_eco_obj_tbl.count
2334 LOOP
2335
2336 l_inv_ebi_eco_obj := l_eco_obj_tbl(i);
2337 l_eco_name := l_eco_obj_tbl(i).eco_change_order_type.eco_name;
2338 l_org_code := l_eco_obj_tbl(i).eco_change_order_type.organization_code;
2339 l_is_master_org := INV_EBI_UTIL.is_master_org(l_org_code);
2340
2341 IF( l_is_master_org = fnd_api.g_false ) THEN
2342
2343 INV_EBI_UTIL.debug_line('STEP 40: BEFORE CALLING INV_EBI_CHANGE_ORDER_PUB.process_change_order_items for Child ORG'||
2344 ' Organization Code: '||l_eco_obj_tbl(i).eco_change_order_type.organization_code||
2345 ' ECO Name: '||l_eco_obj_tbl(i).eco_change_order_type.eco_name);
2346
2347 --To Create Items in Child Org
2348
2349 l_upd_item_tbl := l_updated_item_tbl;
2350
2351 INV_EBI_CHANGE_ORDER_PUB.process_change_order_items(
2352 p_commit => p_commit
2353 ,p_eco_obj => l_inv_ebi_eco_obj
2354 ,p_update_item_tbl => l_upd_item_tbl
2355 ,x_update_item_tbl => l_updated_item_tbl
2356 ,x_out => l_item_output_obj
2357 ,x_eco_obj => l_eco_obj
2358 );
2359
2360
2361 IF l_item_output_obj.output_status.return_status <> FND_API.G_RET_STS_SUCCESS THEN
2362 x_out(i) := inv_ebi_eco_output_obj( l_eco_name,NULL,NULL,l_org_code,l_item_output_obj.output_status,NULL,NULL);
2363 RAISE FND_API.g_exc_unexpected_error;
2364 END IF;
2365
2366 l_eco_obj_tbl(i) := l_eco_obj;
2367
2368 IF(l_updated_item_tbl IS NOT NULL AND l_updated_item_tbl.COUNT> 0) THEN
2369
2370 FOR l_count IN 1..l_updated_item_tbl.COUNT LOOP
2371
2372 l_upd_item_cnt := l_upd_item_cnt + 1;
2373
2374 l_upd_eco_name_tbl.EXTEND(1);
2375 l_upd_eco_name_tbl(l_upd_item_cnt) := l_eco_name;
2376
2377 l_upd_eco_idx_tbl.EXTEND(1);
2378 l_upd_eco_idx_tbl(l_upd_item_cnt) := i;
2379
2380
2381 END LOOP;
2382
2383 END IF;
2384
2385 INV_EBI_UTIL.debug_line('STEP 50: AFTER CALLING INV_EBI_CHANGE_ORDER_PUB.process_change_order_items for Child ORG'||
2386 ' Organization Code: '||l_eco_obj_tbl(i).eco_change_order_type.organization_code||
2387 ' ECO Name: '||l_eco_obj_tbl(i).eco_change_order_type.eco_name||' RETURN STATUS: '||l_item_output_obj.output_status.return_status);
2388
2389
2390 END IF;
2391 END LOOP;
2392
2393 IF l_inv_ebi_eco_obj.name_value_tbl IS NOT NULL THEN
2394 FOR i in l_inv_ebi_eco_obj.name_value_tbl.FIRST..l_inv_ebi_eco_obj.name_value_tbl.LAST LOOP
2395 IF (UPPER(l_inv_ebi_eco_obj.name_value_tbl(i).param_name) = INV_EBI_CHANGE_ORDER_HELPER.G_ASSIGN_ITEM_TO_CHILD_ORG) THEN
2396 l_assign_item_to_child_org := l_inv_ebi_eco_obj.name_value_tbl(i).param_value;
2397 INV_EBI_CHANGE_ORDER_HELPER.set_assign_item(
2398 p_assign_item => l_assign_item_to_child_org
2399 );
2400 END IF;
2401 END LOOP;
2402 END IF;
2403
2404 -- Start of bug 10416391
2405 -- Call the api to update item life cycle /phase/status before creating Change Order as in Ebiz,updation of these
2406 -- attributes is not allowed when there are pending Change Orders.
2407 -- This issue was encountered when SUN AIT created an item using NPR ,through which lifecylce/phase/status values
2408 -- are null and then released a CO to update these 3 attributes,as item updation was done after CO creation,there will
2409 -- be a pending CO while trying to update the item,as it is not possible to update lc/lcp/status when there are pending
2410 -- CO's,so callout to process_update_item_lifecycle api is made to update these 3 attributes before CO is created.
2411 -- If user wants to update these values again,all the pending CO's should be implemented/cancelled.
2412
2413 INV_EBI_UTIL.debug_line('STEP 60: BEFORE CALLING INV_EBI_ITEM_HELPER.process_update_item_lifecycle');
2414 INV_EBI_ITEM_HELPER.process_update_item_lifecycle(
2415 p_commit => FND_API.g_false
2416 ,p_update_item_tbl => l_updated_item_tbl
2417 ,x_out => l_item_output_obj
2418 );
2419 INV_EBI_UTIL.debug_line('STEP 70: AFTER CALLING INV_EBI_ITEM_HELPER.process_update_item_lifecycle for item updation STATUS: '|| l_item_output_obj.output_status.return_status);
2420
2421 --End of bug 10416391
2422
2423 IF (l_item_output_obj.output_status.return_status <> fnd_api.g_ret_sts_success) THEN
2424 l_item_name := l_item_output_obj.item_number;
2425 l_org_code := l_item_output_obj.organization_code;
2426
2427 FOR i IN 1..l_updated_item_tbl.COUNT LOOP
2428 IF( l_updated_item_tbl(i).item_obj.main_obj_type.item_number = l_item_name
2429 AND l_updated_item_tbl(i).item_obj.main_obj_type.organization_code = l_org_code) THEN
2430
2431 l_eco_name := l_upd_eco_name_tbl(i);
2432 l_output_status := l_item_output_obj.output_status;
2433 l_index := l_upd_eco_idx_tbl(i);
2434 x_out(l_index) := inv_ebi_eco_output_obj( l_eco_name,NULL,NULL,l_org_code,l_output_status,NULL,NULL);
2435
2436 END IF;
2437 END LOOP;
2438 RAISE fnd_api.g_exc_unexpected_error;
2439 END IF;
2440
2441 INV_EBI_UTIL.debug_line('STEP 80: BEFORE CALLING INV_EBI_CHANGE_ORDER_PUB.process_bom');
2442 process_bom(
2443 p_commit => p_commit
2444 ,p_eco_obj_list => l_eco_obj_tbl
2445 ,x_eco_obj_list => l_eco_obj_list
2446 ,x_out => l_inv_ebi_eco_output_obj
2447 );
2448
2449 INV_EBI_UTIL.debug_line('STEP 90: AFTER CALLING INV_EBI_CHANGE_ORDER_PUB.process_bom status is ' || l_inv_ebi_eco_output_obj.output_status.return_status);
2450 IF (l_inv_ebi_eco_output_obj.output_status.return_status <> FND_API.g_ret_sts_success )THEN
2451 l_eco_name := l_inv_ebi_eco_output_obj.change_notice;
2452 l_org_code := l_inv_ebi_eco_output_obj.organization_code;
2453 FOR i IN 1..l_eco_obj_tbl.COUNT LOOP
2454
2455 IF( l_eco_obj_tbl(i).eco_change_order_type.eco_name = l_eco_name AND
2456 l_eco_obj_tbl(i).eco_change_order_type.organization_code = l_org_code ) THEN
2457 x_out(i) := l_inv_ebi_eco_output_obj;
2458 END IF;
2459
2460 END LOOP;
2461 RAISE FND_API.g_exc_unexpected_error;
2462 END IF;
2463
2464 FOR i in 1..l_eco_obj_list.COUNT
2465 LOOP
2466 l_inv_ebi_eco_obj := l_eco_obj_list(i);
2467 l_eco_name := l_eco_obj_list(i).eco_change_order_type.eco_name;
2468 l_org_code := l_eco_obj_list(i).eco_change_order_type.organization_code;
2469 INV_EBI_UTIL.debug_line('STEP 100: BEFORE CALLING INV_EBI_CHANGE_ORDER_PUB.process_change_order');
2470
2471 process_change_order(
2472 p_commit => p_commit
2473 ,p_eco_obj => l_inv_ebi_eco_obj
2474 ,x_out => l_inv_ebi_eco_output_obj
2475 );
2476 INV_EBI_UTIL.debug_line('STEP 110: AFTER CALLING INV_EBI_CHANGE_ORDER_PUB.process_change_order STATUS '|| l_inv_ebi_eco_output_obj.output_status.return_status);
2477 x_out(i) := l_inv_ebi_eco_output_obj;
2478
2479 IF x_out(i).output_status.return_status <> FND_API.G_RET_STS_SUCCESS THEN
2480
2481 RAISE FND_API.g_exc_unexpected_error;
2482
2483 END IF;
2484 END LOOP;
2485 END IF;
2486
2487 --Item updation to be done after change Order creation
2488
2489 -- update items
2490 INV_EBI_UTIL.debug_line('STEP 120: BEFORE CALLING INV_EBI_CHANGE_ORDER_PUB.process_update_items ');
2491 process_update_items(
2492 p_commit => p_commit
2493 ,p_update_item_tbl => l_updated_item_tbl
2494 ,x_out => l_item_output_obj
2495 );
2496
2497 INV_EBI_UTIL.debug_line('STEP 130: AFTER CALLING INV_EBI_CHANGE_ORDER_PUB.process_update_items STATUS: '|| l_item_output_obj.output_status.return_status);
2498 IF (l_item_output_obj.output_status.return_status <> fnd_api.g_ret_sts_success) THEN
2499 l_item_name := l_item_output_obj.item_number;
2500 l_org_code := l_item_output_obj.organization_code;
2501
2502 FOR i IN 1..l_updated_item_tbl.COUNT LOOP
2503 IF( l_updated_item_tbl(i).item_obj.main_obj_type.item_number = l_item_name
2504 AND l_updated_item_tbl(i).item_obj.main_obj_type.organization_code = l_org_code) THEN
2505
2506 l_eco_name := l_upd_eco_name_tbl(i);
2507 l_output_status := l_item_output_obj.output_status;
2508 l_index := l_upd_eco_idx_tbl(i);
2509 x_out(l_index) := inv_ebi_eco_output_obj( l_eco_name,NULL,NULL,l_org_code,l_output_status,NULL,NULL);
2510
2511 END IF;
2512 END LOOP;
2513 RAISE fnd_api.g_exc_unexpected_error;
2514 END IF;
2515
2516 -- Start of Bug 10416673
2517 IF (INV_EBI_UTIL.is_pim_installed) THEN
2518 FOR i in 1..l_eco_obj_list.COUNT
2519 LOOP
2520
2521 IF(l_eco_obj_list(i).eco_revised_item_type IS NOT NULL AND l_eco_obj_list(i).eco_revised_item_type.COUNT > 0) THEN
2522 FOR j IN 1..l_eco_obj_list(i).eco_revised_item_type.COUNT
2523 LOOP
2524 INV_EBI_UTIL.debug_line('STEP 140: Processing Revised Item udas ');
2525 IF ( l_eco_obj_list(i).eco_revised_item_type(j).item.uda_type IS NOT NULL AND
2526 l_eco_obj_list(i).eco_revised_item_type(j).item.uda_type.attribute_group_tbl.COUNT > 0) THEN
2527
2528 -- To process Revised Item udas
2529
2530 l_organization_id := INV_EBI_ITEM_HELPER.get_organization_id(
2531 p_organization_code => l_eco_obj_list(i).eco_change_order_type.organization_code
2532 );
2533
2534 l_inventory_item_id := INV_EBI_ITEM_HELPER.get_inventory_item_id (
2535 p_organization_id => l_organization_id
2536 ,p_item_number => l_eco_obj_list(i).eco_revised_item_type(j).revised_item_name
2537 );
2538
2539
2540 INV_EBI_UTIL.debug_line('STEP 150: START CALLING INV_EBI_ITEM_HELPER.process_item_uda'||'INV ITEM ID: '||l_inventory_item_id ||
2541 'ORG ID: '||l_organization_id|| 'CATALOG GROUP ID: '||l_eco_obj_list(i).eco_revised_item_type(j).item.main_obj_type.item_catalog_group_id ||
2542 'REVISED ITEM REVISION: '||l_eco_obj_list(i).eco_revised_item_type(j).new_revised_item_revision);
2543 INV_EBI_ITEM_HELPER.process_item_uda(
2544 p_api_version => l_api_version
2545 ,p_inventory_item_id => l_inventory_item_id
2546 ,p_organization_id => l_organization_id
2547 ,p_item_catalog_group_id => l_eco_obj_list(i).eco_revised_item_type(j).item.main_obj_type.item_catalog_group_id
2548 ,p_revision_id => NULL
2549 ,p_revision_code => l_eco_obj_list(i).eco_revised_item_type(j).new_revised_item_revision
2550 ,p_uda_input_obj => l_eco_obj_list(i).eco_revised_item_type(j).item.uda_type
2551 ,p_commit => fnd_api.g_false
2552 ,x_uda_output_obj => l_item_output_obj
2553 );
2554
2555 INV_EBI_UTIL.debug_line('STEP 160: AFTER CALLING INV_EBI_ITEM_HELPER.process_item_uda STATUS: '|| l_item_output_obj.output_status.return_status);
2556 END IF;
2557
2558 IF (l_item_output_obj.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2559 x_out(i) := inv_ebi_eco_output_obj( l_eco_obj_list(i).eco_change_order_type.eco_name,NULL,NULL,l_eco_obj_list(i).eco_change_order_type.organization_code
2560 ,l_item_output_obj.output_status,NULL,NULL);
2561 RAISE FND_API.g_exc_unexpected_error;
2562 END IF;
2563 END LOOP;
2564 END IF;
2565 END LOOP;
2566 END IF;
2567 -- End of Bug 10416673
2568 INV_EBI_UTIL.debug_line('STEP 170: START CALLING populate_revised_items_out');
2569
2570 FOR i in 1..l_eco_obj_list.COUNT LOOP
2571
2572 l_inv_ebi_eco_obj := l_eco_obj_list(i);
2573 populate_revised_items_out(
2574 p_change_order => l_inv_ebi_eco_obj.eco_change_order_type
2575 ,p_revised_item_type_tbl => l_inv_ebi_eco_obj.eco_revised_item_type
2576 ,x_revised_item_type_tbl => l_revitem_output_tbl
2577 );
2578 x_out(i).item_output_tbl := l_revitem_output_tbl;
2579 END LOOP;
2580
2581 IF FND_API.to_boolean(p_commit) THEN
2582 COMMIT;
2583 END IF;
2584
2585 INV_EBI_UTIL.debug_line('STEP 180: END INSIDE INV_EBI_CHANGE_ORDER_PUB.process_change_order_list STATUS: '|| x_return_status);
2586
2587 EXCEPTION
2588 WHEN FND_API.g_exc_unexpected_error THEN
2589 ROLLBACK TO inv_ebi_prc_chg_ord_save_pnt;
2590 x_return_status := FND_API.G_RET_STS_ERROR;
2591 IF (x_out IS NOT NULL AND x_out.COUNT > 0) THEN
2592 FOR i in 1..x_out.COUNT
2593 LOOP
2594 x_msg_count := x_out(i).output_status.msg_count;
2595 IF x_out(i).output_status.msg_data IS NOT NULL THEN
2596 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;
2597 END IF;
2598 END LOOP;
2599 END IF;
2600
2601 WHEN OTHERS THEN
2602 ROLLBACK TO inv_ebi_prc_chg_ord_save_pnt;
2603 x_out.EXTEND();
2604 x_out(1).output_status.return_status := FND_API.g_ret_sts_unexp_error;
2605 IF (x_out(1).output_status.msg_data IS NOT NULL) THEN
2606 x_out(1).output_status.msg_data := x_out(1).output_status.msg_data||' -> INV_EBI_CHANGE_ORDER_PUB.process_change_order_list';
2607 ELSE
2608 x_out(1).output_status.msg_data := SQLERRM|| 'INV_EBI_CHANGE_ORDER_PUB.process_change_order_list';
2609 END IF;
2610 x_return_status := FND_API.G_RET_STS_ERROR;
2611 x_msg_count := x_out(1).output_status.msg_count;
2612
2613 IF (x_out IS NOT NULL AND x_out.COUNT > 0) THEN
2614 FOR i in 1..x_out.COUNT
2615 LOOP
2616 IF x_out(i).output_status.msg_data IS NOT NULL THEN
2617 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;
2618 END IF;
2619 END LOOP;
2620 END IF;
2621 END process_change_order_list_core;
2622
2623
2624 /************************************************************************************
2625 -- API name : process_change_order_list
2626 -- Type : Public
2627 -- Function :
2628 -- This API is used to process list of change orders
2629 --
2630 ************************************************************************************/
2631
2632 PROCEDURE process_change_order_list(
2633 p_commit IN VARCHAR2 := fnd_api.g_false
2634 ,p_eco_obj_list IN inv_ebi_eco_obj_tbl
2635 ,x_out OUT NOCOPY inv_ebi_eco_output_obj_tbl
2636 ,x_return_status OUT NOCOPY VARCHAR2
2637 ,x_msg_count OUT NOCOPY NUMBER
2638 ,x_msg_data OUT NOCOPY VARCHAR2
2639 )
2640 IS
2641 BEGIN
2642 SAVEPOINT inv_ebi_prc_chg_lst_save_pnt;
2643 ERROR_HANDLER.Initialize;
2644 FND_MSG_PUB.initialize;
2645 INV_EBI_UTIL.setup();
2646 INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_CHANGE_ORDER_PUB.process_change_order_list');
2647 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2648
2649 IF (p_eco_obj_list IS NOT NULL AND p_eco_obj_list.count > 0) THEN
2650 INV_EBI_UTIL.debug_line('STEP 20: START CALLING INV_EBI_CHANGE_ORDER_PUB.process_change_order_list_core');
2651 process_change_order_list_core( p_commit => fnd_api.g_false
2652 ,p_eco_obj_list => p_eco_obj_list
2653 ,x_out => x_out
2654 ,x_return_status => x_return_status
2655 ,x_msg_count => x_msg_count
2656 ,x_msg_data => x_msg_data);
2657 INV_EBI_UTIL.debug_line('STEP 30: END CALLING INV_EBI_CHANGE_ORDER_PUB.process_change_order_list_core STATUS: '|| x_return_status);
2658 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2659 RAISE FND_API.g_exc_unexpected_error;
2660 END IF;
2661 END IF;
2662
2663 INV_EBI_UTIL.debug_line('STEP 40: END INSIDE INV_EBI_CHANGE_ORDER_PUB.process_change_order_list');
2664 INV_EBI_UTIL.wrapup;
2665
2666 EXCEPTION
2667 WHEN FND_API.g_exc_unexpected_error THEN
2668 ROLLBACK TO inv_ebi_prc_chg_lst_save_pnt;
2669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2670 WHEN OTHERS THEN
2671 ROLLBACK TO inv_ebi_prc_chg_lst_save_pnt;
2672 x_return_status := FND_API.G_RET_STS_ERROR;
2673 IF x_msg_data IS NULL THEN
2674 x_msg_data := SQLERRM|| 'INV_EBI_CHANGE_ORDER_PUB.process_change_order_list';
2675 END IF;
2676 END process_change_order_list;
2677 /************************************************************************************
2678 -- API name : validate_change_order_list
2679 -- Type : Public
2680 -- This API is used to validate change order list
2681 --
2682 ************************************************************************************/
2683 PROCEDURE validate_change_order_list(
2684 p_commit IN VARCHAR2 := fnd_api.g_false
2685 ,p_eco_obj_list IN inv_ebi_eco_obj_tbl
2686 ,x_out OUT NOCOPY inv_ebi_eco_output_obj_tbl
2687 ,x_return_status OUT NOCOPY VARCHAR2
2688 ,x_msg_count OUT NOCOPY NUMBER
2689 ,x_msg_data OUT NOCOPY VARCHAR2
2690 )
2691 IS
2692 BEGIN
2693 --SAVEPOINT inv_ebi_val_chg_lst_save_pnt;
2694 ERROR_HANDLER.Initialize;
2695 FND_MSG_PUB.initialize;
2696 INV_EBI_UTIL.setup();
2697 INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_CHANGE_ORDER_PUB.validate_change_order_list');
2698 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2699
2700 IF (p_eco_obj_list IS NOT NULL AND p_eco_obj_list.count > 0) THEN
2701 INV_EBI_UTIL.debug_line('STEP 20: BEFORE CALLING INV_EBI_CHANGE_ORDER_PUB.process_change_order_list_core');
2702 process_change_order_list_core( p_commit => fnd_api.g_false
2703 ,p_eco_obj_list => p_eco_obj_list
2704 ,x_out => x_out
2705 ,x_return_status => x_return_status
2706 ,x_msg_count => x_msg_count
2707 ,x_msg_data => x_msg_data);
2708 INV_EBI_UTIL.debug_line('STEP 30: AFTER CALLING INV_EBI_CHANGE_ORDER_PUB.process_change_order_list_core'||'RETURN STATUS: '||x_return_status);
2709 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2710 RAISE FND_API.g_exc_unexpected_error;
2711 END IF;
2712 END IF;
2713
2714 -- ROLLBACK TO inv_ebi_val_chg_lst_save_pnt;
2715 ROLLBACK;
2716 INV_EBI_UTIL.debug_line('STEP 40: END INSIDE INV_EBI_CHANGE_ORDER_PUB.validate_change_order_list');
2717 INV_EBI_UTIL.wrapup;
2718
2719 EXCEPTION
2720 WHEN FND_API.g_exc_unexpected_error THEN
2721 --ROLLBACK TO inv_ebi_val_chg_lst_save_pnt;
2722 ROLLBACK;
2723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2724 WHEN OTHERS THEN
2725 -- ROLLBACK TO inv_ebi_val_chg_lst_save_pnt;
2726 ROLLBACK;
2727 x_return_status := FND_API.G_RET_STS_ERROR;
2728 IF x_msg_data IS NULL THEN
2729 x_msg_data := SQLERRM|| 'INV_EBI_CHANGE_ORDER_PUB.validate_change_order_list';
2730 END IF;
2731 END validate_change_order_list;
2732
2733 END INV_EBI_CHANGE_ORDER_PUB;