[Home] [Help]
PACKAGE BODY: APPS.INV_EBI_ITEM_HELPER
Source
1 PACKAGE BODY INV_EBI_ITEM_HELPER AS
2 /* $Header: INVEIHITB.pls 120.35.12010000.9 2009/02/13 09:42:50 prepatel ship $ */
3 /************************************************************************************
4 -- API name : populate_err_msg
5 -- Type : Public
6 -- Function :
7 -- This API is used to retrieve the err message.
8 --
9 ************************************************************************************/
10 PROCEDURE populate_err_msg(p_orgid IN NUMBER
11 ,p_invid IN NUMBER
12 ,p_org_code IN VARCHAR2
13 ,p_item_name IN VARCHAR2
14 ,p_part_err_msg IN VARCHAR2
15 ,x_err_msg IN OUT NOCOPY VARCHAR2
16 )
17 IS
18 l_ovrflw_msg VARCHAR2(1):='N';
19 l_part_item_msg VARCHAR2(32000);
20 l_part_org_msg VARCHAR2(32000);
21 l_part_msgtxt VARCHAR2(32000);
22 BEGIN
23 IF (p_item_name IS NOT NULL) THEN
24 l_part_item_msg := ' Item Name: '|| p_item_name ;
25 ELSIF (p_item_name IS NULL AND p_invid IS NOT NULL) THEN
26 l_part_item_msg := ' Item Id: '|| p_invid ;
27 END IF;
28 IF (p_org_code IS NOT NULL) THEN
29 l_part_org_msg := ' Org Code: ' || p_org_code ;
30 ELSIF (p_org_code IS NULL AND p_orgid IS NOT NULL) THEN
31 l_part_org_msg := ' Org Id: ' || p_orgId ;
32 END IF;
33 l_part_msgtxt := l_part_org_msg || l_part_item_msg || ' Err Msg: ' || p_part_err_msg ;
34 IF (x_err_msg IS NULL) THEN
35 x_err_msg := l_part_msgtxt;
36 ELSE
37 IF (LENGTH(x_err_msg ||l_part_msgtxt) < 31000) THEN
38 x_err_msg := x_err_msg ||' , ' || l_part_msgtxt;
39 ELSE
40 l_ovrflw_msg := 'Y';
41 END IF;
42 END IF;
43 IF (l_ovrflw_msg = 'Y') AND SUBSTR(x_err_msg,length(x_err_msg)-2) <> '...' THEN
44 x_err_msg := x_err_msg || '...';
45 END IF;
46 EXCEPTION
47 WHEN OTHERS THEN
48 NULL;
49 END populate_err_msg;
50 /************************************************************************************
51 -- API name : id_col_value
52 -- Type : Public
53 -- Function :
54 ************************************************************************************/
55 FUNCTION id_col_value(
56 p_col_name IN VARCHAR2
57 ,p_pk_col_name_val_pairs IN INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl
58 ) RETURN VARCHAR2 IS
59 l_pkval varchar2(150);
60 BEGIN
61 IF ( (p_pk_col_name_val_pairs IS NOT NULL AND p_pk_col_name_val_pairs.COUNT >0)
62 AND p_col_name IS NOT NULL) THEN
63 FOR i IN 1..p_pk_col_name_val_pairs.COUNT
64 LOOP
65 IF LOWER(p_col_name) = LOWER(p_pk_col_name_val_pairs(i).name)
66 THEN
67 l_pkval := p_pk_col_name_val_pairs(i).value;
68 EXIT;
69 END IF;
70 END LOOP;
71 END IF;
72 RETURN l_pkval;
73 EXCEPTION WHEN OTHERS THEN
74 RETURN NULL;
75 END id_col_value;
76 /************************************************************************************
77 -- API name : value_to_id
78 -- Type : Public
79 -- Function :
80 ************************************************************************************/
81 FUNCTION value_to_id(
82 p_pk_col_name_val_pairs IN INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl
83 ,p_entity_name IN VARCHAR2
84 ) RETURN NUMBER IS
85 l_table_name VARCHAR2(30);
86 l_col_name VARCHAR2(30);
87 l_sql VARCHAR2(32000);
88 l_id NUMBER;
89 l_pk1 VARCHAR2(150);
90 l_pk2 VARCHAR2(150);
91 BEGIN
92 IF ( p_entity_name is NOT NULL )
93 THEN
94 CASE p_entity_name
95 WHEN G_TEMPLATE THEN
96 l_pk1 := ID_COL_VALUE('template_name',p_pk_col_name_val_pairs);
97 SELECT template_id INTO l_id FROM mtl_item_templates
98 WHERE template_name =l_pk1;
99 WHEN G_INVENTORY_ITEM THEN
100 l_pk1 := ID_COL_VALUE('concatenated_segments',p_pk_col_name_val_pairs);
101 l_pk2 := ID_COL_VALUE('organization_id',p_pk_col_name_val_pairs);
102 SELECT inventory_item_id INTO l_id FROM mtl_system_items_kfv
103 WHERE concatenated_segments = l_pk1
104 AND organization_id = l_pk2;
105 WHEN G_ORGANIZATION THEN
106 l_pk1 := ID_COL_VALUE('organization_code',p_pk_col_name_val_pairs);
107 SELECT organization_id INTO l_id FROM mtl_parameters
108 WHERE organization_code = l_pk1;
109 WHEN G_ITEM_CATALOG_GROUP THEN
110 l_pk1 := ID_COL_VALUE('concatenated_segments',p_pk_col_name_val_pairs);
111 SELECT item_catalog_group_id INTO l_id FROM mtl_item_catalog_groups_kfv
112 WHERE concatenated_segments = l_pk1;
113 WHEN G_LIFECYCLE THEN
114 l_pk1 := ID_COL_VALUE('name',p_pk_col_name_val_pairs);
115 SELECT proj_element_id INTO l_id FROM pa_ego_lifecycles_v
116 WHERE name = l_pk1;
117 WHEN G_CURRENT_PHASE THEN
118 l_pk1 := ID_COL_VALUE('name',p_pk_col_name_val_pairs);
119 l_pk2 := ID_COL_VALUE('parent_structure_id',p_pk_col_name_val_pairs);
120 SELECT proj_element_id INTO l_id from pa_ego_phases_v
121 WHERE name = l_pk1
122 AND parent_structure_id = l_pk2;
123 WHEN G_REVISION THEN
124 l_pk1 := ID_COL_VALUE('organization_id',p_pk_col_name_val_pairs);
125 l_pk2 := ID_COL_VALUE('inventory_item_id',p_pk_col_name_val_pairs);
126 SELECT revision_id INTO l_id from mtl_item_rev_highdate_v MIRVH
127 WHERE organization_id = l_pk1
128 AND inventory_item_id = l_pk2
129 AND MIRVH.EFFECTIVITY_DATE < SYSDATE
130 AND decode(MIRVH.HIGH_DATE,SYSDATE,SYSDATE+1) > SYSDATE;
131 WHEN G_HAZARD_CLASS THEN
132 l_pk1 := ID_COL_VALUE('hazard_class',p_pk_col_name_val_pairs);
133 SELECT hazard_class_id INTO l_id from po_hazard_classes_vl
134 WHERE hazard_class = l_pk1;
135 WHEN G_ASSET_CATEGORY THEN
136 l_pk1 := ID_COL_VALUE('concatenated_segments',p_pk_col_name_val_pairs);
137 SELECT category_id INTO l_id FROM fa_categories_b_kfv
138 WHERE concatenated_segments = l_pk1;
139 WHEN G_MANUFACTURER THEN
140 l_pk1 := ID_COL_VALUE('manufacturer_name',p_pk_col_name_val_pairs);
141 SELECT manufacturer_id INTO l_id FROM mtl_manufacturers
142 WHERE manufacturer_name = l_pk1;
143 WHEN G_CATEGORY_SET THEN
144 l_pk1 := ID_COL_VALUE('category_set_name',p_pk_col_name_val_pairs);
145 SELECT category_set_id INTO l_id FROM mtl_category_sets_vl
146 WHERE category_set_name = l_pk1;
147 WHEN G_CATEGORY THEN
148 l_pk1 := ID_COL_VALUE('concatenated_segments',p_pk_col_name_val_pairs);
149 SELECT category_id INTO l_id FROM mtl_categories_kfv
150 WHERE concatenated_segments = l_pk1;
151 END CASE;
152 END IF;
153 RETURN l_id;
154 EXCEPTION
155 WHEN OTHERS THEN
156 RETURN NULL;
157 END value_to_id;
158 /************************************************************************************
159 -- API name : id_to_value
160 -- Type : Public
161 -- Function : returns the code equivalent of a given id
162 ************************************************************************************/
163 FUNCTION id_to_value(
164 p_pk_col_name_val_pairs IN INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl
165 ,p_entity_name IN VARCHAR2
166 ) RETURN VARCHAR2 IS
167 l_table_name VARCHAR2(30);
168 l_col_name VARCHAR2(30);
169 l_sql VARCHAR2(32000);
170 l_code VARCHAR2(100);
171 l_pk1 VARCHAR2(150);
172 l_pk2 VARCHAR2(150);
173 BEGIN
174 IF ( p_entity_name is NOT NULL )
175 THEN
176 CASE p_entity_name
177 WHEN G_TEMPLATE THEN
178 l_pk1 := ID_COL_VALUE('template_id',p_pk_col_name_val_pairs);
179 SELECT template_name INTO l_code
180 FROM mtl_item_templates
181 WHERE template_id = l_pk1;
182 WHEN G_ORGANIZATION THEN
183 l_pk1 := ID_COL_VALUE('organization_id',p_pk_col_name_val_pairs);
184 SELECT organization_code INTO l_code
185 FROM mtl_parameters
186 WHERE organization_id = l_pk1;
187 WHEN G_ITEM_CATALOG_GROUP THEN
188 l_pk1 := ID_COL_VALUE('item_catalog_group_id',p_pk_col_name_val_pairs);
189 SELECT concatenated_segments INTO l_code
190 FROM mtl_item_catalog_groups_kfv
191 WHERE item_catalog_group_id = l_pk1;
192 WHEN G_LIFECYCLE THEN
193 l_pk1 := ID_COL_VALUE('proj_element_id',p_pk_col_name_val_pairs);
194 SELECT name INTO l_code
195 FROM pa_ego_lifecycles_v
196 WHERE proj_element_id = l_pk1;
197 WHEN G_CURRENT_PHASE THEN
198 l_pk1 := ID_COL_VALUE('proj_element_id',p_pk_col_name_val_pairs);
199 SELECT phase_code INTO l_code
200 FROM pa_ego_phases_v
201 WHERE proj_element_id = l_pk1;
202 WHEN G_REVISION THEN
203 l_pk1 := ID_COL_VALUE('revision_id',p_pk_col_name_val_pairs);
204 SELECT revision_label INTO l_code
205 FROM mtl_item_revisions
206 WHERE revision_id = l_pk1;
207 WHEN G_HAZARD_CLASS THEN
208 l_pk1 := ID_COL_VALUE('hazard_class_id',p_pk_col_name_val_pairs);
209 SELECT hazard_class INTO l_code
210 FROM po_hazard_classes_vl
211 WHERE hazard_class_id = l_pk1;
212 WHEN G_ASSET_CATEGORY THEN
213 l_pk1 := ID_COL_VALUE('category_id',p_pk_col_name_val_pairs);
214 SELECT concatenated_segments INTO l_code
215 FROM fa_categories_b_kfv
216 WHERE category_id = l_pk1 ;
217 WHEN G_MANUFACTURER THEN
218 l_pk1 := ID_COL_VALUE('manufacturer_id',p_pk_col_name_val_pairs);
219 SELECT manufacturer_name INTO l_code
220 FROM mtl_manufacturers
221 WHERE manufacturer_id = l_pk1;
222 WHEN G_CATEGORY_SET THEN
223 l_pk1 := ID_COL_VALUE('cat_set_id',p_pk_col_name_val_pairs);
224 SELECT category_set_name INTO l_code
225 FROM mtl_category_sets_vl
226 WHERE CATEGORY_SET_ID = l_pk1;
227 WHEN G_CATEGORY THEN
228 l_pk1 := ID_COL_VALUE('cat_id',p_pk_col_name_val_pairs);
229 SELECT concatenated_segments INTO l_code
230 FROM mtl_categories_kfv
231 WHERE CATEGORY_ID =l_pk1;
232 WHEN G_INVENTORY_ITEM THEN
233 l_pk1 := ID_COL_VALUE('organization_id',p_pk_col_name_val_pairs);
234 l_pk2 := ID_COL_VALUE('inventory_item_id',p_pk_col_name_val_pairs);
235 SELECT concatenated_segments INTO l_code
236 FROM mtl_system_items_b_kfv
237 WHERE organization_id = l_pk1
238 AND inventory_item_id = l_pk2;
239 END CASE;
240 END IF;
241 RETURN l_code;
242 EXCEPTION
243 WHEN OTHERS THEN
244 RETURN NULL;
245 END id_to_value;
246 /************************************************************************************
247 -- API name : populate_item_ids
248 -- Type : Public
249 -- Function :
250 ************************************************************************************/
251 PROCEDURE populate_item_ids(
252 p_item IN inv_ebi_item_obj
253 ,x_out OUT NOCOPY inv_ebi_item_output_obj
254 ,x_item OUT NOCOPY inv_ebi_item_obj
255 ) IS
256 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
257 l_output_status inv_ebi_output_status;
258 BEGIN
259 x_item := p_item;
260 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
261 x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL);
262 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
263 IF ( (p_item.main_obj_type.organization_id IS NULL OR p_item.main_obj_type.organization_id = fnd_api.g_miss_num)
264 AND p_item.main_obj_type.organization_code IS NOT NULL ) THEN
265 l_pk_col_name_val_pairs.EXTEND(1);
266 l_pk_col_name_val_pairs(1).name := 'organization_code';
267 l_pk_col_name_val_pairs(1).value := p_Item.main_obj_type.organization_code;
268 x_item.main_obj_type.organization_id := INV_EBI_ITEM_HELPER.value_to_id(
269 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
270 ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
271 );
272 l_pk_col_name_val_pairs.TRIM(1);
273 IF (x_item.main_obj_type.organization_id IS NULL) THEN
274 FND_MESSAGE.set_name('INV','INV_EBI_ORG_CODE_INVALID');
275 FND_MESSAGE.set_token('COL_VALUE', p_Item.main_obj_type.organization_code);
276 FND_MSG_PUB.add;
277 RAISE FND_API.g_exc_error;
278 END IF;
279 END IF;
280 IF ( (p_item.main_obj_type.inventory_item_id IS NULL OR p_item.main_obj_type.inventory_item_id= fnd_api.g_miss_num)
281 AND p_item.main_obj_type.item_number IS NOT NULL ) THEN
282 l_pk_col_name_val_pairs.EXTEND(2);
283 l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
284 l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.item_number;
285 l_pk_col_name_val_pairs(2).name := 'organization_id';
286 l_pk_col_name_val_pairs(2).value := p_item.main_obj_type.organization_id;
287 x_item.main_obj_type.inventory_item_id := INV_EBI_ITEM_HELPER.value_to_id(
288 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
289 ,p_entity_name => INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
290 );
291 l_pk_col_name_val_pairs.TRIM(2);
292 END IF;
293 IF ( (p_item.main_obj_type.template_id IS NULL OR p_item.main_obj_type.template_id =fnd_api.g_miss_num)
294 AND p_item.main_obj_type.template_name IS NOT NULL) THEN
295 l_pk_col_name_val_pairs.EXTEND(1);
296 l_pk_col_name_val_pairs(1).name := 'template_name';
297 l_pk_col_name_val_pairs(1).value := p_Item.main_obj_type.template_name;
298 x_item.main_obj_type.template_id := INV_EBI_ITEM_HELPER.value_to_id(
299 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
300 ,p_entity_name => INV_EBI_ITEM_HELPER.G_TEMPLATE
301 );
302 l_pk_col_name_val_pairs.TRIM(1);
303 END IF;
304 IF (p_item.org_id_obj_type IS NOT NULL AND p_item.org_id_obj_type.COUNT > 0 ) THEN
305 FOR i IN 1..p_item.org_id_obj_type.COUNT LOOP
306 IF (p_item.org_id_obj_type(i).org_id IS NULL AND p_item.org_id_obj_type(i).org_code IS NOT NULL) THEN
307 l_pk_col_name_val_pairs.EXTEND(1);
308 l_pk_col_name_val_pairs(1).name := 'organization_code';
309 l_pk_col_name_val_pairs(1).value := p_item.org_id_obj_type(i).org_code;
310 x_item.org_id_obj_type(i).org_id := INV_EBI_ITEM_HELPER.value_to_id(
311 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
312 ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
313 );
314 l_pk_col_name_val_pairs.TRIM(1);
315 IF (x_item.org_id_obj_type(i).org_id IS NULL) THEN
316 FND_MESSAGE.set_name('INV','INV_EBI_ORG_CODE_INVALID');
317 FND_MESSAGE.set_token('COL_VALUE', p_item.org_id_obj_type(i).org_code);
318 FND_MSG_PUB.add;
319 RAISE FND_API.g_exc_error;
320 END IF;
321 END IF;
322 END LOOP;
323 END IF;
324 IF ( (p_item.main_obj_type.item_catalog_group_id IS NULL OR p_item.main_obj_type.item_catalog_group_id=fnd_api.g_miss_num)
325 AND p_item.main_obj_type.item_catalog_group_code IS NOT NULL
326 AND p_item.main_obj_type.item_catalog_group_code <> fnd_api.g_miss_char) THEN
327 l_pk_col_name_val_pairs.EXTEND(1);
328 l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
329 l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.item_catalog_group_code;
330 x_item.main_obj_type.item_catalog_group_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_ITEM_CATALOG_GROUP
333 );
334 l_pk_col_name_val_pairs.TRIM(1);
335 IF (x_item.main_obj_type.item_catalog_group_id IS NULL ) THEN
336 FND_MESSAGE.set_name('INV','INV_EBI_ITEM_INVALID');
337 FND_MESSAGE.set_token('COL_VALUE',p_item.main_obj_type.item_catalog_group_code);
338 FND_MSG_PUB.add;
339 RAISE FND_API.g_exc_error;
340 END IF;
341 END IF;
342 IF ( (p_item.main_obj_type.lifecycle_id IS NULL OR p_item.main_obj_type.lifecycle_id =fnd_api.g_miss_num)
343 AND p_item.main_obj_type.lifecycle_name IS NOT NULL) THEN
344 l_pk_col_name_val_pairs.EXTEND(1);
345 l_pk_col_name_val_pairs(1).name := 'name';
346 l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.lifecycle_name;
347 x_item.main_obj_type.lifecycle_id := INV_EBI_ITEM_HELPER.value_to_id (
348 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
349 ,p_entity_name => INV_EBI_ITEM_HELPER.G_LIFECYCLE
350 );
351 l_pk_col_name_val_pairs.TRIM(1);
352 END IF;
353 IF ( (p_item.main_obj_type.current_phase_id IS NULL OR p_item.main_obj_type.current_phase_id=fnd_api.g_miss_num)
354 AND (p_item.main_obj_type.current_phase_name IS NOT NULL AND x_item.main_obj_type.lifecycle_id is NOT NULL)) THEN
355 l_pk_col_name_val_pairs.EXTEND(2);
356 l_pk_col_name_val_pairs(1).name := 'name';
357 l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.current_phase_name;
358 l_pk_col_name_val_pairs(2).name := 'parent_structure_id';
359 l_pk_col_name_val_pairs(2).value := x_item.main_obj_type.lifecycle_id ;
360 x_item.main_obj_type.current_phase_id := INV_EBI_ITEM_HELPER.value_to_id(
361 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
362 ,p_entity_name => INV_EBI_ITEM_HELPER.G_CURRENT_PHASE
363 );
364 l_pk_col_name_val_pairs.TRIM(2);
365 END IF;
366 IF ( (p_item.main_obj_type.revision_id IS NULL OR p_item.main_obj_type.revision_id= fnd_api.g_miss_num)
367 AND (x_item.main_obj_type.organization_id IS NOT NULL AND x_item.main_obj_type.inventory_item_id IS NOT NULL)
368 ) THEN
369 l_pk_col_name_val_pairs.EXTEND(2);
370 l_pk_col_name_val_pairs(1).name := 'organization_id';
371 l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.organization_id;
372 l_pk_col_name_val_pairs(2).name := 'inventory_item_id';
373 l_pk_col_name_val_pairs(2).value := x_item.main_obj_type.inventory_item_id;
374 x_item.main_obj_type.revision_id := INV_EBI_ITEM_HELPER.value_to_id(
375 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
376 ,p_entity_name => INV_EBI_ITEM_HELPER.G_REVISION
377 );
378 l_pk_col_name_val_pairs.TRIM(2);
379 END IF;
380 IF ( (p_item.purchasing_obj_type.hazard_class_id IS NULL OR p_item.purchasing_obj_type.hazard_class_id = fnd_api.g_miss_num)
381 AND p_item.purchasing_obj_type.hazard_class_code IS NOT NULL) THEN
382 l_pk_col_name_val_pairs.EXTEND(1);
383 l_pk_col_name_val_pairs(1).name := 'hazard_class';
384 l_pk_col_name_val_pairs(1).value := p_item.purchasing_obj_type.hazard_class_code ;
385 x_item.purchasing_obj_type.hazard_class_id := INV_EBI_ITEM_HELPER.value_to_id(
386 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
387 ,p_entity_name => INV_EBI_ITEM_HELPER.G_HAZARD_CLASS
388 );
389 l_pk_col_name_val_pairs.TRIM(1);
390 END IF;
391 IF ( (p_item.purchasing_obj_type.asset_category_id IS NULL OR p_item.purchasing_obj_type.asset_category_id =fnd_api.g_miss_num)
392 AND p_item.purchasing_obj_type.asset_category_code IS NOT NULL ) THEN
393 l_pk_col_name_val_pairs.EXTEND(1);
394 l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
395 l_pk_col_name_val_pairs(1).value := p_item.purchasing_obj_type.asset_category_code;
396 x_item.purchasing_obj_type.asset_category_id := INV_EBI_ITEM_HELPER.value_to_id(
397 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
398 ,p_entity_name => INV_EBI_ITEM_HELPER.G_ASSET_CATEGORY
399 );
400 l_pk_col_name_val_pairs.TRIM(1);
401 END IF;
402 IF ( (p_item.bom_obj_type.base_item_id IS NULL OR p_item.bom_obj_type.base_item_id= fnd_api.g_miss_num)
403 AND p_item.bom_obj_type.base_item_number IS NOT NULL )THEN
404 l_pk_col_name_val_pairs.EXTEND(2);
405 l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
406 l_pk_col_name_val_pairs(1).value := p_item.bom_obj_type.base_item_number;
407 l_pk_col_name_val_pairs(2).name := 'organization_id';
408 l_pk_col_name_val_pairs(2).value := p_item.main_obj_type.organization_id;
409 x_item.bom_obj_type.base_item_id := INV_EBI_ITEM_HELPER.value_to_id(
410 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
411 ,p_entity_name => INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
412 );
413 l_pk_col_name_val_pairs.TRIM(2);
414 END IF;
415 IF ( p_item.part_num_obj_tbl_type IS NOT NULL AND p_item.part_num_obj_tbl_type.COUNT > 0 ) THEN
416 FOR i in 1..p_item.part_num_obj_tbl_type.COUNT LOOP
417 IF ((p_item.part_num_obj_tbl_type(i).manufacturer_id IS NULL OR p_item.part_num_obj_tbl_type(i).manufacturer_id = fnd_api.g_miss_num)
418 AND (p_item.part_num_obj_tbl_type(i).manufacturer_name IS NOT NULL OR p_item.part_num_obj_tbl_type(i).manufacturer_name <> fnd_api.g_miss_char))THEN
419 l_pk_col_name_val_pairs.EXTEND(1);
420 l_pk_col_name_val_pairs(1).name := 'manufacturer_name';
421 l_pk_col_name_val_pairs(1).value := p_item.part_num_obj_tbl_type(i).manufacturer_name;
422 x_item.part_num_obj_tbl_type(i).manufacturer_id := INV_EBI_ITEM_HELPER.value_to_id (
423 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
424 ,p_entity_name => INV_EBI_ITEM_HELPER.G_MANUFACTURER
425 );
426 l_pk_col_name_val_pairs.TRIM(1);
427 END IF;
428 END LOOP;
429 END IF;
430 IF ( p_item.category_id_obj_tbl_type IS NOT NULL AND p_item.category_id_obj_tbl_type.COUNT > 0) THEN
431 FOR i IN 1..p_item.category_id_obj_tbl_type.COUNT LOOP
432 IF ((p_item.category_id_obj_tbl_type(i).cat_set_id IS NULL OR p_item.category_id_obj_tbl_type(i).cat_set_id = fnd_api.g_miss_num)
433 AND (p_item.category_id_obj_tbl_type(i).cat_set_name IS NOT NULL OR p_item.category_id_obj_tbl_type(i).cat_set_name <> fnd_api.g_miss_char) ) THEN
434 l_pk_col_name_val_pairs.EXTEND(1);
435 l_pk_col_name_val_pairs(1).name := 'category_set_name';
436 l_pk_col_name_val_pairs(1).value := p_item.category_id_obj_tbl_type(i).cat_set_name;
437 x_item.category_id_obj_tbl_type(i).cat_set_id := INV_EBI_ITEM_HELPER.value_to_id(
438 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
439 ,p_entity_name => INV_EBI_ITEM_HELPER.G_CATEGORY_SET
440 );
441 l_pk_col_name_val_pairs.TRIM(1);
442 END IF;
443 IF ( (p_item.category_id_obj_tbl_type(i).cat_id IS NULL OR p_item.category_id_obj_tbl_type(i).cat_id =fnd_api.g_miss_num)
444 AND ( p_item.category_id_obj_tbl_type(i).cat_name IS NOT NULL OR p_item.category_id_obj_tbl_type(i).cat_name <> fnd_api.g_miss_char)) THEN
445 l_pk_col_name_val_pairs.EXTEND(1);
446 l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
447 l_pk_col_name_val_pairs(1).value := p_item.category_id_obj_tbl_type(i).cat_name;
448 x_item.category_id_obj_tbl_type(i).cat_id := INV_EBI_ITEM_HELPER.value_to_id (
449 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
450 ,p_entity_name => INV_EBI_ITEM_HELPER.G_CATEGORY
451 );
452 l_pk_col_name_val_pairs.TRIM(1);
453 END IF;
454 END LOOP;
455 END IF;
456 IF ( (p_item.main_obj_type.rev_lifecycle_id IS NULL OR p_item.main_obj_type.rev_lifecycle_id= fnd_api.g_miss_num)
457 AND p_item.main_obj_type.rev_lifecycle_name IS NOT NULL) THEN
458 l_pk_col_name_val_pairs.EXTEND(1);
459 l_pk_col_name_val_pairs(1).name := 'name';
460 l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.rev_lifecycle_name;
461 x_item.main_obj_type.rev_lifecycle_id := INV_EBI_ITEM_HELPER.value_to_id(
462 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
463 ,p_entity_name => INV_EBI_ITEM_HELPER.G_LIFECYCLE
464 );
465 l_pk_col_name_val_pairs.TRIM(1);
466 END IF;
467 IF ( (p_item.main_obj_type.rev_current_phase_id IS NULL OR p_item.main_obj_type.rev_current_phase_id= fnd_api.g_miss_num)
468 AND (p_item.main_obj_type.rev_current_phase_name IS NOT NULL AND x_item.main_obj_type.rev_lifecycle_id IS NOT NULL )
469 ) THEN
470 l_pk_col_name_val_pairs.EXTEND(2);
471 l_pk_col_name_val_pairs(1).name := 'name';
472 l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.rev_current_phase_name;
473 l_pk_col_name_val_pairs(2).name := 'parent_structure_id';
474 l_pk_col_name_val_pairs(2).value := x_item.main_obj_type.rev_lifecycle_id;
475 x_item.main_obj_type.rev_current_phase_id := INV_EBI_ITEM_HELPER.value_to_id(
476 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
477 ,p_entity_name => INV_EBI_ITEM_HELPER.G_CURRENT_PHASE
478 );
479 l_pk_col_name_val_pairs.TRIM(2);
480 END IF;
481 EXCEPTION
482 WHEN FND_API.g_exc_error THEN
483 x_out.output_status.return_status := FND_API.g_ret_sts_error;
484 IF(x_out.output_status.msg_data IS NULL) THEN
485 fnd_msg_pub.count_and_get(
486 p_encoded => fnd_api.g_false
487 ,p_count => x_out.output_status.msg_count
488 ,p_data => x_out.output_status.msg_data
489 );
490 END IF;
491 WHEN OTHERS THEN
492 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
493 IF (x_out.output_status.msg_data IS NOT NULL) THEN
494 x_out.output_status.msg_data := x_out.output_status.msg_data||' -> INV_EBI_ITEM_PUB.populate_item_ids ';
495 ELSE
496 x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_PUB.populate_item_ids ';
497 END IF;
498 END populate_item_ids;
499 /************************************************************************************
500 -- API name : get_default_master_org
501 -- Type : Public
502 -- Function :
503 ************************************************************************************/
504 FUNCTION get_default_master_org(
505 p_config IN inv_ebi_name_value_tbl
506 ) RETURN NUMBER IS
507 l_master_org NUMBER;
508 l_master_org_count NUMBER;
509 l_default_master_org_id NUMBER;
510 l_master_org_code VARCHAR2(3);
511 CURSOR c_master_org IS
512 SELECT master_organization_id
513 FROM mtl_parameters
514 WHERE organization_id = master_organization_id;
515 BEGIN
516 OPEN c_master_org;
517 LOOP
518 FETCH c_master_org INTO l_master_org;
519 IF (c_master_org%ROWCOUNT > 1) THEN
520 l_master_org_code := INV_EBI_UTIL.get_config_param_value(
521 p_config_tbl => p_config
522 ,p_config_param_name => 'Default_Master_Organization_For_Item'
523 );
524 l_master_org := get_organization_id(
525 p_organization_code => l_master_org_code
526 );
527 EXIT;
528 END IF;
529 END LOOP;
530 CLOSE c_master_org;
531 RETURN l_master_org;
532 EXCEPTION
533 WHEN OTHERS THEN
534 RETURN NULL;
535 END get_default_master_org;
536 /************************************************************************************
537 -- API name : initialize_item
538 -- Type : Private
539 -- Function :
540 -- This API is used to
541 --
542 ************************************************************************************/
543 PROCEDURE initialize_item(
544 x_item IN OUT NOCOPY inv_ebi_item_obj
545 )
546 IS
547 BEGIN
548 IF (x_item.physical_obj_type IS NULL) THEN
549 x_item.physical_obj_type := inv_ebi_item_physical_obj(
550 fnd_api.g_miss_char,
551 fnd_api.g_miss_num,
552 fnd_api.g_miss_char,
553 fnd_api.g_miss_num,
554 fnd_api.g_miss_char,
555 fnd_api.g_miss_char,
556 fnd_api.g_miss_char,
557 fnd_api.g_miss_num,
558 fnd_api.g_miss_num,
559 fnd_api.g_miss_num,
560 fnd_api.g_miss_num,
561 fnd_api.g_miss_char,
562 fnd_api.g_miss_char,
563 fnd_api.g_miss_char,
564 fnd_api.g_miss_char,
565 fnd_api.g_miss_char,
566 fnd_api.g_miss_num,
567 fnd_api.g_miss_num,
568 fnd_api.g_miss_num
569 );
570 END IF;
571 IF (x_item.inventory_obj_type IS NULL) THEN
572 x_item.inventory_obj_type := inv_ebi_item_inventory_obj(
573 fnd_api.g_miss_char,
574 fnd_api.g_miss_char,
575 fnd_api.g_miss_char,
576 fnd_api.g_miss_char,
577 fnd_api.g_miss_num,
578 fnd_api.g_miss_num,
579 fnd_api.g_miss_num,
580 fnd_api.g_miss_num,
581 fnd_api.g_miss_char,
582 fnd_api.g_miss_num,
583 fnd_api.g_miss_num,
584 fnd_api.g_miss_num,
585 fnd_api.g_miss_char,
586 fnd_api.g_miss_char,
587 fnd_api.g_miss_num,
588 fnd_api.g_miss_char,
589 fnd_api.g_miss_char,
590 fnd_api.g_miss_num,
591 fnd_api.g_miss_num,
592 fnd_api.g_miss_num,
593 fnd_api.g_miss_char,
594 fnd_api.g_miss_char,
595 fnd_api.g_miss_char,
596 fnd_api.g_miss_char,
597 fnd_api.g_miss_char,
598 fnd_api.g_miss_char,
599 fnd_api.g_miss_char,
600 fnd_api.g_miss_char,
601 fnd_api.g_miss_char,
602 fnd_api.g_miss_num,
603 fnd_api.g_miss_char,
604 fnd_api.g_miss_char,
605 fnd_api.g_miss_char,
606 fnd_api.g_miss_char,
607 fnd_api.g_miss_num,
608 fnd_api.g_miss_char,
609 fnd_api.g_miss_num,
610 fnd_api.g_miss_char,
611 fnd_api.g_miss_num,
612 fnd_api.g_miss_char,
613 fnd_api.g_miss_num
614 );
615 END IF;
616 IF (x_item.purchasing_obj_type IS NULL) THEN
617 x_item.purchasing_obj_type := inv_ebi_item_purchasing_obj(
618 fnd_api.g_miss_char,
619 fnd_api.g_miss_char,
620 fnd_api.g_miss_char,
621 fnd_api.g_miss_char,
622 fnd_api.g_miss_char,
623 fnd_api.g_miss_char,
624 fnd_api.g_miss_char,
625 fnd_api.g_miss_char,
626 fnd_api.g_miss_char,
627 fnd_api.g_miss_char,
628 fnd_api.g_miss_char,
629 fnd_api.g_miss_num,
630 fnd_api.g_miss_char,
631 fnd_api.g_miss_num,
632 fnd_api.g_miss_num,
633 fnd_api.g_miss_num,
634 fnd_api.g_miss_num,
635 fnd_api.g_miss_char,
636 fnd_api.g_miss_num,
637 fnd_api.g_miss_num,
638 fnd_api.g_miss_num,
639 fnd_api.g_miss_num,
640 fnd_api.g_miss_num,
641 fnd_api.g_miss_num,
642 fnd_api.g_miss_num,
643 fnd_api.g_miss_char,
644 fnd_api.g_miss_num
645 );
646 END IF;
647 IF (x_item.receiving_obj_type IS NULL) THEN
648 x_item.receiving_obj_type := inv_ebi_item_receiving_obj(
649 fnd_api.g_miss_char,
650 fnd_api.g_miss_num,
651 fnd_api.g_miss_num,
652 fnd_api.g_miss_char,
653 fnd_api.g_miss_char,
654 fnd_api.g_miss_char,
655 fnd_api.g_miss_char,
656 fnd_api.g_miss_num,
657 fnd_api.g_miss_num,
658 fnd_api.g_miss_char
659 );
660 END IF;
661 IF (x_item.gplanning_obj_type IS NULL) THEN
662 x_item.gplanning_obj_type := inv_ebi_item_gplanning_obj(
663 fnd_api.g_miss_num,
664 fnd_api.g_miss_char,
665 fnd_api.g_miss_num,
666 fnd_api.g_miss_num,
667 fnd_api.g_miss_num,
668 fnd_api.g_miss_num,
669 fnd_api.g_miss_num,
670 fnd_api.g_miss_num,
671 fnd_api.g_miss_num,
672 fnd_api.g_miss_num,
673 fnd_api.g_miss_num,
674 fnd_api.g_miss_char,
675 fnd_api.g_miss_num,
676 fnd_api.g_miss_num,
677 fnd_api.g_miss_num,
678 fnd_api.g_miss_num,
679 fnd_api.g_miss_num,
680 fnd_api.g_miss_num,
681 fnd_api.g_miss_num,
682 fnd_api.g_miss_num,
683 fnd_api.g_miss_num,
684 fnd_api.g_miss_num,
685 fnd_api.g_miss_num,
686 fnd_api.g_miss_num,
687 fnd_api.g_miss_num,
688 fnd_api.g_miss_num,
689 fnd_api.g_miss_num,
690 fnd_api.g_miss_num,
691 fnd_api.g_miss_num
692 );
693 END IF;
694 IF (x_item.mrp_obj_type IS NULL) THEN
695 x_item.mrp_obj_type := inv_ebi_item_mrp_obj(
696 fnd_api.g_miss_num,
697 fnd_api.g_miss_num,
698 fnd_api.g_miss_char,
699 fnd_api.g_miss_char,
700 fnd_api.g_miss_num,
701 fnd_api.g_miss_num,
702 fnd_api.g_miss_num,
703 fnd_api.g_miss_char,
704 fnd_api.g_miss_num,
705 fnd_api.g_miss_num,
706 fnd_api.g_miss_num,
707 fnd_api.g_miss_char,
708 fnd_api.g_miss_num,
709 fnd_api.g_miss_num,
710 fnd_api.g_miss_num,
711 fnd_api.g_miss_num,
712 fnd_api.g_miss_num,
713 fnd_api.g_miss_num,
714 fnd_api.g_miss_num,
715 fnd_api.g_miss_char,
716 fnd_api.g_miss_char,
717 fnd_api.g_miss_num,
718 fnd_api.g_miss_num,
719 fnd_api.g_miss_num,
720 fnd_api.g_miss_num,
721 fnd_api.g_miss_num,
722 fnd_api.g_miss_num,
723 fnd_api.g_miss_num,
724 fnd_api.g_miss_num,
725 fnd_api.g_miss_num,
726 fnd_api.g_miss_num,
727 fnd_api.g_miss_num,
728 fnd_api.g_miss_num,
729 fnd_api.g_miss_num,
730 fnd_api.g_miss_num,
731 fnd_api.g_miss_char,
732 fnd_api.g_miss_num
733 );
734 END IF;
735 IF (x_item.order_obj_type IS NULL) THEN
736 x_item.order_obj_type := inv_ebi_item_order_obj(
737 fnd_api.g_miss_char,
738 fnd_api.g_miss_char,
739 fnd_api.g_miss_char,
740 fnd_api.g_miss_char,
741 fnd_api.g_miss_char,
742 fnd_api.g_miss_char,
743 fnd_api.g_miss_char,
744 fnd_api.g_miss_char,
745 fnd_api.g_miss_char,
746 fnd_api.g_miss_num,
747 fnd_api.g_miss_char,
748 fnd_api.g_miss_char,
749 fnd_api.g_miss_num,
750 fnd_api.g_miss_char,
751 fnd_api.g_miss_num,
752 fnd_api.g_miss_char,
753 fnd_api.g_miss_num,
754 fnd_api.g_miss_num,
755 fnd_api.g_miss_num,
756 fnd_api.g_miss_num,
757 fnd_api.g_miss_num,
758 fnd_api.g_miss_char,
759 fnd_api.g_miss_char,
760 fnd_api.g_miss_char
761 );
762 END IF;
763 IF (x_item.service_obj_type IS NULL) THEN
764 x_item.service_obj_type := inv_ebi_item_service_obj(
765 fnd_api.g_miss_num,
766 fnd_api.g_miss_num,
767 fnd_api.g_miss_char,
768 fnd_api.g_miss_char,
769 fnd_api.g_miss_num,
770 fnd_api.g_miss_char,
771 fnd_api.g_miss_char,
772 fnd_api.g_miss_char,
773 fnd_api.g_miss_char,
774 fnd_api.g_miss_char,
775 fnd_api.g_miss_char
776 );
777 END IF;
778 IF (x_item.bom_obj_type IS NULL) THEN
779 x_item.bom_obj_type := inv_ebi_item_bom_obj(
780 fnd_api.g_miss_char,
781 fnd_api.g_miss_num,
782 fnd_api.g_miss_num,
783 fnd_api.g_miss_char,
784 fnd_api.g_miss_num,
785 fnd_api.g_miss_char,
786 fnd_api.g_miss_char,
787 fnd_api.g_miss_char,
788 fnd_api.g_miss_char,
789 fnd_api.g_miss_char
790 );
791 END IF;
792 IF (x_item.costing_obj_type IS NULL) THEN
793 x_item.costing_obj_type := inv_ebi_item_costing_obj(
794 fnd_api.g_miss_char,
795 fnd_api.g_miss_char,
796 fnd_api.g_miss_char,
797 fnd_api.g_miss_num,
798 fnd_api.g_miss_num,
799 fnd_api.g_miss_num
800 );
801 END IF;
802 IF (x_item.lead_time_obj_type IS NULL) THEN
803 x_item.lead_time_obj_type := inv_ebi_item_lead_time_obj(
804 fnd_api.g_miss_num,
805 fnd_api.g_miss_num,
806 fnd_api.g_miss_num,
807 fnd_api.g_miss_num,
808 fnd_api.g_miss_num,
809 fnd_api.g_miss_num,
810 fnd_api.g_miss_num,
811 fnd_api.g_miss_num
812 );
813 END IF;
814 IF (x_item.wip_obj_type IS NULL) THEN
815 x_item.wip_obj_type := inv_ebi_item_wip_obj(
816 fnd_api.g_miss_char,
817 fnd_api.g_miss_num,
818 fnd_api.g_miss_char,
819 fnd_api.g_miss_num,
820 fnd_api.g_miss_num,
821 fnd_api.g_miss_num,
822 fnd_api.g_miss_num,
823 fnd_api.g_miss_num
824 );
825 END IF;
826 IF (x_item.invoice_obj_type IS NULL) THEN
827 x_item.invoice_obj_type := inv_ebi_item_invoice_obj(
828 fnd_api.g_miss_char,
829 fnd_api.g_miss_char,
830 fnd_api.g_miss_num,
831 fnd_api.g_miss_num,
832 fnd_api.g_miss_char,
833 fnd_api.g_miss_num,
834 fnd_api.g_miss_num
835 );
836 END IF;
837 IF (x_item.web_option_obj_type IS NULL) THEN
838 x_item.web_option_obj_type := inv_ebi_item_web_option_obj(
839 fnd_api.g_miss_char,
840 fnd_api.g_miss_char,
841 fnd_api.g_miss_char,
842 fnd_api.g_miss_num
843 );
844 END IF;
845 IF (x_item.asset_obj_type IS NULL) THEN
846 x_item.asset_obj_type := inv_ebi_item_asset_obj(
847 fnd_api.g_miss_num,
848 fnd_api.g_miss_char,
849 fnd_api.g_miss_char,
850 fnd_api.g_miss_char,
851 fnd_api.g_miss_char,
852 fnd_api.g_miss_char,
853 fnd_api.g_miss_char,
854 fnd_api.g_miss_char,
855 fnd_api.g_miss_char
856 );
857 END IF;
858 IF (x_item.deprecated_obj_type IS NULL) THEN
859 x_item.deprecated_obj_type := inv_ebi_item_deprecated_obj(
860 fnd_api.g_miss_char,
861 fnd_api.g_miss_num,
862 fnd_api.g_miss_num,
863 fnd_api.g_miss_char,
864 fnd_api.g_miss_num,
865 fnd_api.g_miss_date,
866 fnd_api.g_miss_num,
867 fnd_api.g_miss_char,
868 fnd_api.g_miss_char,
869 fnd_api.g_miss_char,
870 fnd_api.g_miss_char,
871 fnd_api.g_miss_char,
872 fnd_api.g_miss_char,
873 fnd_api.g_miss_num,
874 fnd_api.g_miss_num,
875 fnd_api.g_miss_num,
876 fnd_api.g_miss_num,
877 fnd_api.g_miss_char,
878 fnd_api.g_miss_num,
879 fnd_api.g_miss_num,
880 fnd_api.g_miss_num,
881 fnd_api.g_miss_date,
882 fnd_api.g_miss_char,
883 fnd_api.g_miss_char,
884 fnd_api.g_miss_char,
885 fnd_api.g_miss_num,
886 fnd_api.g_miss_char,
887 fnd_api.g_miss_num,
888 fnd_api.g_miss_char,
889 fnd_api.g_miss_num,
890 fnd_api.g_miss_num,
891 fnd_api.g_miss_char,
892 fnd_api.g_miss_char
893 );
894 END IF;
895 IF (x_item.process_manufacturing_obj IS NULL) THEN
896 x_item.process_manufacturing_obj := inv_ebi_item_processmfg_obj(
897 fnd_api.g_miss_char,
898 fnd_api.g_miss_char,
899 fnd_api.g_miss_char,
900 fnd_api.g_miss_char,
901 fnd_api.g_miss_char,
902 fnd_api.g_miss_num,
903 fnd_api.g_miss_char,
904 fnd_api.g_miss_num,
905 fnd_api.g_miss_char,
906 fnd_api.g_miss_char
907 );
908 END IF;
909 END initialize_item;
910 /************************************************************************************
911 -- API name : is_new_item_request_reqd
912 -- Type : Public
913 -- Function :
914 ************************************************************************************/
915 FUNCTION is_new_item_request_reqd(
916 p_item_catalog_group_id IN NUMBER
917 ) RETURN VARCHAR IS
918 l_is_new_item_request_reqd VARCHAR2(3);
919 BEGIN
920 IF (p_item_catalog_group_id IS NOT NULL AND p_item_catalog_group_id <> fnd_api.g_miss_num) THEN
921 SELECT new_item_request_reqd INTO l_is_new_item_request_reqd
922 FROM mtl_item_catalog_groups_vl
923 WHERE item_catalog_group_id = p_item_catalog_group_id;
924 IF(l_is_new_item_request_reqd = 'Y') THEN
925 RETURN FND_API.g_true;
926 ELSE
927 RETURN FND_API.g_false;
928 END IF;
929 END IF;
930 RETURN FND_API.g_false;
931 EXCEPTION
932 WHEN OTHERS THEN
933 RETURN FND_API.g_false;
934 END is_new_item_request_reqd;
935 /************************************************************************************
936 -- API name : get_organization_id
937 -- Type : Public
938 -- Function :
939 ************************************************************************************/
940 FUNCTION get_organization_id ( p_organization_code IN VARCHAR2 ) RETURN NUMBER
941 IS
942 l_org_id NUMBER;
943 BEGIN
944 SELECT organization_id
945 INTO l_org_id
946 FROM mtl_parameters
947 WHERE organization_code = p_organization_code;
948 RETURN l_org_id;
949 EXCEPTION
950 WHEN OTHERS THEN
951 RETURN NULL;
952 END Get_Organization_Id;
953 /************************************************************************************
954 -- API name : get_inventory_item_id
955 -- Type : Public
956 -- Function :
957 ************************************************************************************/
958 FUNCTION get_inventory_item_id(
959 p_organization_id IN NUMBER
960 ,p_item_number IN VARCHAR2
961 ) RETURN NUMBER IS
962 l_inventory_item_id NUMBER;
963 BEGIN
964 SELECT inventory_item_id INTO l_inventory_item_id
965 FROM mtl_system_items_kfv
966 WHERE concatenated_segments = p_item_number
967 AND organization_id = p_organization_id;
968 RETURN l_inventory_item_id;
969 EXCEPTION
970 WHEN OTHERS THEN
971 RETURN FND_API.g_miss_num;
972 END get_inventory_item_id;
973 /************************************************************************************
974 -- API name : get_item_num
975 -- Type : Private
976 -- Function :
977 -- This API is used to
978 --
979 ************************************************************************************/
980 FUNCTION get_item_num(
981 p_segment1 IN VARCHAR2
982 ,p_segment2 IN VARCHAR2
983 ,p_segment3 IN VARCHAR2
984 ,p_segment4 IN VARCHAR2
985 ,p_segment5 IN VARCHAR2
986 ,p_segment6 IN VARCHAR2
987 ,p_segment7 IN VARCHAR2
988 ,p_segment8 IN VARCHAR2
989 ,p_segment9 IN VARCHAR2
990 ,p_segment10 IN VARCHAR2
991 ,p_segment11 IN VARCHAR2
992 ,p_segment12 IN VARCHAR2
993 ,p_segment13 IN VARCHAR2
994 ,p_segment14 IN VARCHAR2
995 ,p_segment15 IN VARCHAR2
996 ,p_segment16 IN VARCHAR2
997 ,p_segment17 IN VARCHAR2
998 ,p_segment18 IN VARCHAR2
999 ,p_segment19 IN VARCHAR2
1000 ,p_segment20 IN VARCHAR2
1001 ) RETURN VARCHAR2 IS
1002 l_item_number VARCHAR2(2000);
1003 BEGIN
1004 SELECT DECODE(p_segment1,fnd_api.g_miss_char,'',NULL,'',p_segment1) ||
1005 DECODE(p_segment2,fnd_api.g_miss_char,'',NULL,'',p_segment2) ||
1006 DECODE(p_segment3,fnd_api.g_miss_char,'',NULL,'',p_segment3) ||
1007 DECODE(p_segment4,fnd_api.g_miss_char,'',NULL,'',p_segment4) ||
1008 DECODE(p_segment5,fnd_api.g_miss_char,'',NULL,'',p_segment5) ||
1009 DECODE(p_segment6,fnd_api.g_miss_char,'',NULL,'',p_segment6) ||
1010 DECODE(p_segment7,fnd_api.g_miss_char,'',NULL,'',p_segment7) ||
1011 DECODE(p_segment8,fnd_api.g_miss_char,'',NULL,'',p_segment8) ||
1012 DECODE(p_segment9,fnd_api.g_miss_char,'',NULL,'',p_segment9) ||
1013 DECODE(p_segment10,fnd_api.g_miss_char,'',NULL,'',p_segment10) ||
1014 DECODE(p_segment11,fnd_api.g_miss_char,'',NULL,'',p_segment11) ||
1015 DECODE(p_segment12,fnd_api.g_miss_char,'',NULL,'',p_segment12) ||
1016 DECODE(p_segment13,fnd_api.g_miss_char,'',NULL,'',p_segment13) ||
1017 DECODE(p_segment14,fnd_api.g_miss_char,'',NULL,'',p_segment14) ||
1018 DECODE(p_segment15,fnd_api.g_miss_char,'',NULL,'',p_segment15) ||
1019 DECODE(p_segment16,fnd_api.g_miss_char,'',NULL,'',p_segment16) ||
1020 DECODE(p_segment17,fnd_api.g_miss_char,'',NULL,'',p_segment17) ||
1021 DECODE(p_segment18,fnd_api.g_miss_char,'',NULL,'',p_segment18) ||
1022 DECODE(p_segment19,fnd_api.g_miss_char,'',NULL,'',p_segment19) ||
1023 DECODE(p_segment20,fnd_api.g_miss_char,'',NULL,'',p_segment20)
1024 INTO l_item_number
1025 FROM DUAL;
1026 RETURN l_item_number;
1027 EXCEPTION
1028 WHEN OTHERS THEN
1029 RETURN NULL;
1030 END get_item_num;
1031 /************************************************************************************
1032 -- API name : is_item_engg
1033 -- Type : Public
1034 -- Function :
1035 --
1036 ************************************************************************************/
1037 FUNCTION is_engineering_item(
1038 p_organization_id IN NUMBER
1039 ,p_item_number IN VARCHAR2
1040 ) RETURN VARCHAR IS
1041 l_item_flag VARCHAR2(1);
1042 BEGIN
1043 SELECT eng_item_flag INTO l_item_flag
1044 FROM mtl_system_items_kfv
1045 WHERE concatenated_segments = NVL(p_item_number,FND_API.G_MISS_CHAR)
1046 AND organization_id =p_organization_id;
1047 IF(l_item_flag='Y') THEN
1048 RETURN FND_API.g_true;
1049 ELSE
1050 RETURN FND_API.g_false;
1051 END IF;
1052 EXCEPTION
1053 WHEN OTHERS THEN
1054 RETURN FND_API.g_false;
1055 END is_engineering_item;
1056 /************************************************************************************
1057 -- API name : is_item_exists
1058 -- Type : Public
1059 -- Function :
1060 --
1061 --Check if the concatenated segment numbers have to be unique or Is the
1062 --below condition sufficient
1063 --some information
1064 ************************************************************************************/
1065 FUNCTION is_item_exists (
1066 p_organization_id IN NUMBER
1067 ,p_item_number IN VARCHAR2
1068 ) RETURN VARCHAR IS
1069 l_item_count NUMBER;
1070 BEGIN
1071 SELECT COUNT(1) INTO l_item_count
1072 FROM mtl_system_items_kfv
1073 WHERE concatenated_segments = p_item_number
1074 AND organization_id = p_organization_id;
1075 IF(l_item_count=0) THEN
1076 RETURN FND_API.g_false;
1077 ELSE
1078 RETURN FND_API.g_true;
1079 END IF;
1080 EXCEPTION
1081 WHEN OTHERS THEN
1082 RETURN FND_API.g_false;
1083 END is_item_exists;
1084
1085 /************************************************************************************
1086 -- API name : is_revision_exists
1087 -- Type : private
1088 -- Function :
1089 --
1090 --Check if the revision is already created
1091 ************************************************************************************/
1092
1093 FUNCTION is_revision_exists (
1094 p_organization_id IN NUMBER
1095 ,p_item_number IN VARCHAR2
1096 ,p_revision IN VARCHAR2
1097 ) RETURN VARCHAR IS
1098
1099 l_count NUMBER;
1100 BEGIN
1101
1102 SELECT COUNT(1) INTO l_count
1103 FROM
1104 mtl_item_revisions_b mir ,
1105 mtl_system_items_kfv msi
1106 WHERE
1107 msi.concatenated_segments = p_item_number AND
1108 msi.organization_id = p_organization_id AND
1109 msi.inventory_item_id = mir.inventory_item_id AND
1110 msi.organization_id = mir.organization_id AND
1111 mir.revision = p_revision;
1112
1113 IF(l_count=0) THEN
1114 RETURN FND_API.g_false;
1115 ELSE
1116 RETURN FND_API.g_true;
1117 END IF;
1118 EXCEPTION
1119 WHEN OTHERS THEN
1120 RETURN FND_API.g_false;
1121 END is_revision_exists;
1122
1123
1124 /************************************************************************************
1125 -- API name : get_desc_gen_method
1126 -- Type : private
1127 -- Function : Return description generation method of ICC
1128 ************************************************************************************/
1129 FUNCTION get_desc_gen_method(p_item_catalog_group_id NUMBER)
1130 RETURN VARCHAR2
1131 IS
1132 l_parent_catalog_group_id NUMBER;
1133 t_parent_catalog_group_id NUMBER;
1134 l_item_desc_gen_method VARCHAR2(2);
1135 BEGIN
1136 SELECT item_desc_gen_method, parent_catalog_group_id
1137 INTO l_item_desc_gen_method, l_parent_catalog_group_id
1138 FROM MTL_ITEM_CATALOG_GROUPS_VL
1139 WHERE item_catalog_group_id = p_item_catalog_group_id;
1140 IF UPPER(l_item_desc_gen_method)='I' THEN
1141 WHILE UPPER(l_item_desc_gen_method) = 'I' LOOP
1142 SELECT item_desc_gen_method,
1143 parent_catalog_group_id
1144 INTO l_item_desc_gen_method,
1145 t_parent_catalog_group_id
1146 FROM mtl_item_catalog_groups_vl
1147 WHERE item_catalog_group_id = l_parent_catalog_group_id;
1148 EXIT WHEN t_parent_catalog_group_id IS NULL;
1149 l_parent_catalog_group_id := t_parent_catalog_group_id;
1150 END LOOP;
1151 END IF;
1152 RETURN l_item_desc_gen_method;
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155 RETURN NULL;
1156 END;
1157
1158 /************************************************************************************
1159 -- API name : process_item_pvt
1160 -- Type : Public
1161 -- Function :
1162 -- This API is used to
1163 --
1164 ************************************************************************************/
1165 PROCEDURE process_item_pvt(
1166 p_commit IN VARCHAR2 := FND_API.g_false
1167 ,p_operation IN VARCHAR2
1168 ,p_item IN inv_ebi_item_obj
1169 ,x_out OUT NOCOPY inv_ebi_item_output_obj
1170 )
1171 IS
1172 l_transaction_type VARCHAR2(20);
1173 l_item_exits VARCHAR2(3);
1174 l_item inv_ebi_item_obj;
1175 l_xref_id NUMBER;
1176 l_item_number VARCHAR2(2000);
1177 l_description VARCHAR2(240);
1178 l_item_desc_gen_method VARCHAR2(3) := 'U';
1179 l_is_master_org VARCHAR2(3) := 'N';
1180 l_is_new_item_request_reqd VARCHAR2(3) := 'N';
1181 l_eng_item_flag VARCHAR2(3);
1182 l_output_status inv_ebi_output_status;
1183 l_count NUMBER;
1184 l_master_org NUMBER;
1185 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
1186 l_apply_template_update VARCHAR2(2000);
1187 l_validate_revised_itm_rev VARCHAR2(1);
1188 l_revised_item_exists NUMBER := 0;
1189 l_effectivity_date DATE;
1190 CURSOR c_item_description(
1191 p_item_number IN VARCHAR2
1192 ,p_organization_id IN NUMBER
1193 ) IS
1194 SELECT description
1195 FROM mtl_system_items_kfv
1196 WHERE concatenated_segments = p_item_number
1197 AND organization_id = p_organization_id;
1198 BEGIN
1199 SAVEPOINT inv_ebi_pvt_item_save_pnt;
1200 INV_EBI_UTIL.debug_line('STEP: 10 '||'START INSIDE INV_EBI_ITEM_HELPER.process_item_pvt ');
1201 l_item := p_item;
1202 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
1203 x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL);
1204 l_item_number := l_item.main_obj_type.item_number;
1205 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
1206 IF(l_item_number IS NULL OR l_item_number = fnd_api.g_miss_char) THEN
1207 l_item_number := get_item_num(
1208 p_segment1 => l_item.main_obj_type.segment1
1209 ,p_segment2 => l_item.main_obj_type.segment2
1210 ,p_segment3 => l_item.main_obj_type.segment3
1211 ,p_segment4 => l_item.main_obj_type.segment4
1212 ,p_segment5 => l_item.main_obj_type.segment5
1213 ,p_segment6 => l_item.main_obj_type.segment6
1214 ,p_segment7 => l_item.main_obj_type.segment7
1215 ,p_segment8 => l_item.main_obj_type.segment8
1216 ,p_segment9 => l_item.main_obj_type.segment9
1217 ,p_segment10 => l_item.main_obj_type.segment10
1218 ,p_segment11 => l_item.main_obj_type.segment11
1219 ,p_segment12 => l_item.main_obj_type.segment12
1220 ,p_segment13 => l_item.main_obj_type.segment13
1221 ,p_segment14 => l_item.main_obj_type.segment14
1222 ,p_segment15 => l_item.main_obj_type.segment15
1223 ,p_segment16 => l_item.main_obj_type.segment16
1224 ,p_segment17 => l_item.main_obj_type.segment17
1225 ,p_segment18 => l_item.main_obj_type.segment18
1226 ,p_segment19 => l_item.main_obj_type.segment19
1227 ,p_segment20 => l_item.main_obj_type.segment20
1228 );
1229 END IF;
1230 IF l_item_number IS NULL THEN
1231 FND_MESSAGE.set_name('INV','INV_EBI_ITEM_NUM_NULL');
1232 FND_MSG_PUB.add;
1233 RAISE FND_API.g_exc_error;
1234 END IF;
1235 IF p_operation = INV_EBI_ITEM_PUB.g_otype_sync THEN
1236 IF( is_item_exists (
1237 p_organization_id => l_item.main_obj_type.organization_id
1238 ,p_item_number => l_item_number
1239 ) = FND_API.g_true ) THEN
1240 l_transaction_type := INV_EBI_ITEM_PUB.g_otype_update;
1241 ELSE
1242 l_transaction_type := INV_EBI_ITEM_PUB.g_otype_create;
1243 l_item.main_obj_type.item_number := l_item_number;
1244 END IF;
1245 ELSE
1246 l_transaction_type := p_operation;
1247 l_item.main_obj_type.item_number := l_item_number;
1248 END IF;
1249 initialize_item(x_item => l_item);
1250
1251 l_validate_revised_itm_rev := INV_EBI_UTIL.get_config_param_value(
1252 p_config_tbl => l_item.name_value_tbl
1253 ,p_config_param_name => 'VALIDATE_REVISED_ITEM_REVISION'
1254 );
1255 IF (l_validate_revised_itm_rev = fnd_api.g_true ) AND (l_item.main_obj_type.revision_code IS NOT NULL)
1256 AND (l_item.main_obj_type.revision_code <> fnd_api.g_miss_char)
1257 AND ( is_item_exists (
1258 p_organization_id => l_item.main_obj_type.organization_id
1259 ,p_item_number => l_item_number
1260 ) = FND_API.g_true )
1261 THEN
1262 IF( l_item.main_obj_type.inventory_item_id IS NULL) THEN
1263 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
1264 l_pk_col_name_val_pairs.EXTEND(2);
1265 l_pk_col_name_val_pairs(1).name := 'organization_id';
1266 l_pk_col_name_val_pairs(1).value := l_item.main_obj_type.organization_id;
1267 l_pk_col_name_val_pairs(2).name := 'concatenated_segments';
1268 l_pk_col_name_val_pairs(2).value := l_item.main_obj_type.item_number;
1269 l_item.main_obj_type.inventory_item_id := INV_EBI_ITEM_HELPER.value_to_id (
1270 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
1271 ,p_entity_name=> INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
1272 );
1273 END IF;
1274
1275 SELECT COUNT(1) into l_revised_item_exists
1276 FROM mtl_item_revisions_b ir
1277 WHERE ir.inventory_item_id = l_item.main_obj_type.inventory_item_id
1278 AND ir.organization_id = l_item.main_obj_type.organization_id
1279 AND ir.revision = l_item.main_obj_type.revision_code
1280 AND(ir.effectivity_date IN
1281 (SELECT first_value(ir2.effectivity_date) over(ORDER BY ir2.effectivity_date DESC)
1282 FROM mtl_item_revisions_b ir2
1283 WHERE ir2.organization_id = ir.organization_id
1284 AND ir2.inventory_item_id = ir.inventory_item_id
1285 AND ir2.effectivity_date <= sysdate
1286 AND ir2.implementation_date IS NOT NULL)
1287 OR ir.effectivity_date > sysdate);
1288 IF l_revised_item_exists = 0 then
1289 FND_MESSAGE.set_name('INV','INV_EBI_INVALD_REV_CODE');
1290 FND_MESSAGE.set_token('ITEM_NUMBER', l_item_number);
1291 FND_MESSAGE.set_token('REVISION_CODE',l_item.main_obj_type.revision_code);
1292 FND_MESSAGE.set_token('ORG_CODE',l_item.main_obj_type.organization_code);
1293 FND_MSG_PUB.add;
1294 RAISE FND_API.g_exc_error;
1295 END IF;
1296 END IF;
1297 IF (l_item.main_obj_type.template_id IS NULL AND l_item.main_obj_type.template_name IS NULL AND
1298 l_item.main_obj_type.item_catalog_group_id IS NOT NULL AND l_item.main_obj_type.item_catalog_group_id <> fnd_api.g_miss_num) THEN
1299 SELECT default_template_id INTO l_item.main_obj_type.template_id
1300 FROM ego_catalog_groups_v T
1301 WHERE catalog_group_id = l_item.main_obj_type.item_catalog_group_id;
1302 END IF;
1303 IF ((l_item.main_obj_type.apply_template IS NULL OR l_item.main_obj_type.apply_template = fnd_api.g_miss_char) AND (l_item.main_obj_type.template_id IS NOT NULL OR l_item.main_obj_type.template_name IS NOT NULL)) THEN
1304 l_item.main_obj_type.apply_template :='ALL';
1305 END IF;
1306 IF(l_transaction_type = INV_EBI_ITEM_PUB.g_otype_create AND l_item.main_obj_type.description IS NULL OR l_item.main_obj_type.description = fnd_api.g_miss_char) THEN
1307 IF(l_item.main_obj_type.item_catalog_group_id IS NOT NULL AND l_item.main_obj_type.item_catalog_group_id <> fnd_api.g_miss_num) THEN
1308 l_item_desc_gen_method := get_desc_gen_method(l_item.main_obj_type.item_catalog_group_id);
1309 END IF;
1310 IF(l_item_desc_gen_method = 'U' OR l_item_desc_gen_method IS NULL OR l_item.main_obj_type.item_catalog_group_id IS NULL OR l_item.main_obj_type.item_catalog_group_id = fnd_api.g_miss_num) THEN
1311 l_is_master_org := INV_EBI_UTIL.is_master_org(l_item.main_obj_type.organization_id);
1312 IF(l_is_master_org = fnd_api.g_true) THEN
1313 l_item.main_obj_type.description := l_item_number;
1314 ELSE
1315 l_master_org := INV_EBI_UTIL.get_master_organization(
1316 p_organization_id => l_item.main_obj_type.organization_id
1317 );
1318 OPEN c_item_description(
1319 p_item_number => l_item_number
1320 ,p_organization_id => l_master_org
1321 );
1322 FETCH c_item_description INTO l_description;
1323 IF(c_item_description%NOTFOUND) THEN
1324 FND_MESSAGE.set_name('INV','INV_EBI_ITEM_NO_MASTER_ORG');
1325 FND_MESSAGE.set_token('ITEM_NUMBER', l_item_number);
1326 FND_MSG_PUB.add;
1327 RAISE FND_API.g_exc_error;
1328 END IF;
1329 l_item.main_obj_type.description := l_description;
1330 CLOSE c_item_description;
1331 END IF;
1332 END IF;
1333 IF(l_item.bom_obj_type.eng_item_flag = fnd_api.g_miss_char OR l_item.bom_obj_type.eng_item_flag IS NULL ) THEN
1334 l_is_new_item_request_reqd := is_new_item_request_reqd(
1335 p_item_catalog_group_id => l_item.main_obj_type.item_catalog_group_id
1336 );
1337 IF(l_is_new_item_request_reqd = fnd_api.g_false) THEN
1338 l_item.bom_obj_type.eng_item_flag := 'N';
1339 END IF;
1340 END IF;
1341 END IF;
1342
1343
1344
1345 IF(l_transaction_type = INV_EBI_ITEM_PUB.g_otype_update ) THEN
1346 l_apply_template_update := INV_EBI_UTIL.get_config_param_value(
1347 p_config_tbl => l_item.name_value_tbl
1348 ,p_config_param_name => 'TEMPLATE_FOR_ITEM_UPDATE_ALLOWED'
1349 );
1350 IF( UPPER(l_apply_template_update ) = fnd_api.g_false ) THEN
1351 l_item.main_obj_type.template_id := fnd_api.g_miss_num;
1352 l_item.main_obj_type.template_name := fnd_api.g_miss_char;
1353 END IF;
1354
1355 --Bug 7601514 : To create new revision coming in while updating item only if incoming revision is not null
1356 IF(l_item.main_obj_type.revision_code IS NOT NULL AND l_item.main_obj_type.revision_code <> fnd_api.g_miss_char) THEN
1357 --Bug 7412466 : To create new revision coming in while updating item
1358 IF( is_revision_exists (p_organization_id => l_item.main_obj_type.organization_id
1359 ,p_item_number => l_item.main_obj_type.item_number
1360 ,p_revision => l_item.main_obj_type.revision_code
1361 ) = FND_API.g_false ) THEN
1362 IF( l_item.main_obj_type.effectivity_date IS NULL OR
1363 l_item.main_obj_type.effectivity_date = fnd_api.g_miss_date ) THEN
1364 l_effectivity_date := SYSDATE;
1365 ELSE
1366 l_effectivity_date := l_item.main_obj_type.effectivity_date;
1367 END IF;
1368 EGO_ITEM_PUB.Process_Item_Revision(
1369 p_api_version => 1.0
1370 ,p_init_msg_list => FND_API.g_false
1371 ,p_commit => FND_API.g_false
1372 ,p_transaction_type => INV_EBI_ITEM_PUB.g_otype_create
1373 ,p_inventory_item_id => l_item.main_obj_type.inventory_item_id
1374 ,p_item_number => l_item.main_obj_type.item_number
1375 ,p_organization_id => l_item.main_obj_type.organization_id
1376 ,p_organization_code => l_item.main_obj_type.organization_code
1377 ,p_revision => l_item.main_obj_type.revision_code
1378 ,p_description => l_item.main_obj_type.revision_description
1379 ,p_effectivity_date => l_effectivity_date
1380 ,p_revision_label => l_item.main_obj_type.revision_label
1381 ,p_revision_reason => l_item.main_obj_type.revision_reason --This parameter is added in inv_ebi_item_main_obj
1382 ,p_lifecycle_id => l_item.main_obj_type.rev_lifecycle_id
1383 ,p_current_phase_id => l_item.main_obj_type.rev_current_phase_id
1384 ,p_template_id => l_item.main_obj_type.template_id
1385 ,p_template_name => l_item.main_obj_type.template_name
1386 ,p_attribute_category => l_item.custom_obj_type.rev_attribute_category
1387 ,p_attribute1 => l_item.custom_obj_type.rev_attribute1
1388 ,p_attribute2 => l_item.custom_obj_type.rev_attribute2
1389 ,p_attribute3 => l_item.custom_obj_type.rev_attribute3
1390 ,p_attribute4 => l_item.custom_obj_type.rev_attribute4
1391 ,p_attribute5 => l_item.custom_obj_type.rev_attribute5
1392 ,p_attribute6 => l_item.custom_obj_type.rev_attribute6
1393 ,p_attribute7 => l_item.custom_obj_type.rev_attribute7
1394 ,p_attribute8 => l_item.custom_obj_type.rev_attribute8
1395 ,p_attribute9 => l_item.custom_obj_type.rev_attribute9
1396 ,p_attribute10 => l_item.custom_obj_type.rev_attribute10
1397 ,p_attribute11 => l_item.custom_obj_type.rev_attribute11
1398 ,p_attribute12 => l_item.custom_obj_type.rev_attribute12
1399 ,p_attribute13 => l_item.custom_obj_type.rev_attribute13
1400 ,p_attribute14 => l_item.custom_obj_type.rev_attribute14
1401 ,p_attribute15 => l_item.custom_obj_type.rev_attribute15
1402 ,x_return_status => x_out.output_status.return_status
1403 ,x_msg_count => x_out.output_status.msg_count
1404 ,x_msg_data => x_out.output_status.msg_data
1405 );
1406 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
1407 RAISE FND_API.g_exc_unexpected_error;
1408 END IF;
1409 END IF; -- Bug 7412466
1410 END IF; -- End of Bug 7601514
1411 END IF;
1412
1413 INV_EBI_UTIL.debug_line('STEP: 20 '||'START CALLING EGO_ITEM_PUB.process_item ');
1414 EGO_ITEM_PUB.process_item(
1415 p_api_version => 1.0
1416 ,p_init_msg_list => l_item.main_obj_type.init_msg_list
1417 ,p_commit => FND_API.g_false
1418 ,p_transaction_type => l_transaction_type
1419 ,p_language_code => l_item.main_obj_type.language_code
1420 ,p_template_id => l_item.main_obj_type.template_id
1421 ,p_template_name => l_item.main_obj_type.template_name
1422 ,p_copy_inventory_item_id => l_item.main_obj_type.copy_inventory_item_id
1423 ,p_inventory_item_id => l_item.main_obj_type.inventory_item_id
1424 ,p_organization_id => l_item.main_obj_type.organization_id
1425 ,p_master_organization_id => l_item.main_obj_type.master_organization_id
1426 ,p_description => l_item.main_obj_type.description
1427 ,p_long_description => l_item.main_obj_type.long_description
1428 ,p_primary_uom_code => l_item.main_obj_type.primary_uom_code
1429 ,p_primary_unit_of_measure => l_item.main_obj_type.primary_unit_of_measure
1430 ,p_item_type => l_item.main_obj_type.item_type
1431 ,p_inventory_item_status_code => l_item.main_obj_type.inventory_item_status_code
1432 ,p_allowed_units_lookup_code => l_item.main_obj_type.allowed_units_lookup_code
1433 ,p_item_catalog_group_id => l_item.main_obj_type.item_catalog_group_id
1434 ,p_catalog_status_flag => l_item.deprecated_obj_type.catalog_status_flag
1435 ,p_inventory_item_flag => l_item.inventory_obj_type.inventory_item_flag
1436 ,p_stock_enabled_flag => l_item.inventory_obj_type.stock_enabled_flag
1437 ,p_mtl_transactions_enabled_fl => l_item.inventory_obj_type.mtl_transactions_enabled_fl
1438 ,p_check_shortages_flag => l_item.inventory_obj_type.check_shortages_flag
1439 ,p_revision_qty_control_code => l_item.inventory_obj_type.revision_qty_control_code
1440 ,p_reservable_type => l_item.inventory_obj_type.reservable_type
1441 ,p_shelf_life_code => l_item.inventory_obj_type.shelf_life_code
1442 ,p_shelf_life_days => l_item.inventory_obj_type.shelf_life_days
1443 ,p_cycle_count_enabled_flag => l_item.inventory_obj_type.cycle_count_enabled_flag
1444 ,p_negative_measurement_error => l_item.inventory_obj_type.negative_measurement_error
1445 ,p_positive_measurement_error => l_item.inventory_obj_type.positive_measurement_error
1446 ,p_lot_control_code => l_item.inventory_obj_type.lot_control_code
1447 ,p_auto_lot_alpha_prefix => l_item.inventory_obj_type.auto_lot_alpha_prefix
1448 ,p_start_auto_lot_number => l_item.inventory_obj_type.start_auto_lot_number
1449 ,p_serial_number_control_code => l_item.inventory_obj_type.serial_number_control_code
1450 ,p_auto_serial_alpha_prefix => l_item.inventory_obj_type.auto_serial_alpha_prefix
1451 ,p_start_auto_serial_number => l_item.inventory_obj_type.start_auto_serial_number
1452 ,p_location_control_code => l_item.inventory_obj_type.location_control_code
1453 ,p_restrict_subinventories_cod => l_item.inventory_obj_type.restrict_subinventories_cod
1454 ,p_restrict_locators_code => l_item.inventory_obj_type.restrict_locators_code
1455 ,p_bom_enabled_flag => l_item.bom_obj_type.bom_enabled_flag
1456 ,p_bom_item_type => l_item.bom_obj_type.bom_item_type
1457 ,p_base_item_id => l_item.bom_obj_type.base_item_id
1458 ,p_effectivity_control => l_item.bom_obj_type.effectivity_control
1459 ,p_eng_item_flag => l_item.bom_obj_type.eng_item_flag
1460 ,p_engineering_ecn_code => l_item.deprecated_obj_type.engineering_ecn_code
1461 ,p_engineering_item_id => l_item.deprecated_obj_type.engineering_item_id
1462 ,p_engineering_date => l_item.deprecated_obj_type.engineering_date
1463 ,p_product_family_item_id => l_item.deprecated_obj_type.product_family_item_id
1464 ,p_auto_created_config_flag => l_item.bom_obj_type.auto_created_config_flag
1465 ,p_model_config_clause_name => l_item.deprecated_obj_type.model_config_clause_name
1466 ,p_new_revision_code => l_item.deprecated_obj_type.new_revision_code
1467 ,p_costing_enabled_flag => l_item.costing_obj_type.costing_enabled_flag
1468 ,p_inventory_asset_flag => l_item.costing_obj_type.inventory_asset_flag
1469 ,p_default_include_in_rollup_f => l_item.costing_obj_type.default_include_in_rollup_f
1470 ,p_cost_of_sales_account => l_item.costing_obj_type.cost_of_sales_account
1471 ,p_std_lot_size => l_item.costing_obj_type.std_lot_size
1472 ,p_purchasing_item_flag => l_item.purchasing_obj_type.purchasing_item_flag
1473 ,p_purchasing_enabled_flag => l_item.purchasing_obj_type.purchasing_enabled_flag
1474 ,p_must_use_approved_vendor_fl => l_item.purchasing_obj_type.must_use_approved_vendor_fl
1475 ,p_allow_item_desc_update_flag => l_item.purchasing_obj_type.allow_item_desc_update_flag
1476 ,p_rfq_required_flag => l_item.purchasing_obj_type.rfq_required_flag
1477 ,p_outside_operation_flag => l_item.purchasing_obj_type.outside_operation_flag
1478 ,p_outside_operation_uom_type => l_item.purchasing_obj_type.outside_operation_uom_type
1479 ,p_taxable_flag => l_item.purchasing_obj_type.taxable_flag
1480 ,p_purchasing_tax_code => l_item.purchasing_obj_type.purchasing_tax_code
1481 ,p_receipt_required_flag => l_item.purchasing_obj_type.receipt_required_flag
1482 ,p_inspection_required_flag => l_item.purchasing_obj_type.inspection_required_flag
1483 ,p_buyer_id => l_item.purchasing_obj_type.buyer_id
1484 ,p_unit_of_issue => l_item.purchasing_obj_type.unit_of_issue
1485 ,p_receive_close_tolerance => l_item.purchasing_obj_type.receive_close_tolerance
1486 ,p_invoice_close_tolerance => l_item.purchasing_obj_type.invoice_close_tolerance
1487 ,p_un_number_id => l_item.purchasing_obj_type.un_number_id
1488 ,p_hazard_class_id => l_item.purchasing_obj_type.hazard_class_id
1489 ,p_list_price_per_unit => l_item.purchasing_obj_type.list_price_per_unit
1490 ,p_market_price => l_item.purchasing_obj_type.market_price
1491 ,p_price_tolerance_percent => l_item.purchasing_obj_type.price_tolerance_percent
1492 ,p_rounding_factor => l_item.purchasing_obj_type.rounding_factor
1493 ,p_encumbrance_account => l_item.purchasing_obj_type.encumbrance_account
1494 ,p_expense_account => l_item.purchasing_obj_type.expense_account
1495 ,p_expense_billable_flag => l_item.deprecated_obj_type.expense_billable_flag
1496 ,p_asset_category_id => l_item.purchasing_obj_type.asset_category_id
1497 ,p_receipt_days_exception_code => l_item.receiving_obj_type.receipt_days_exception_code
1498 ,p_days_early_receipt_allowed => l_item.receiving_obj_type.days_early_receipt_allowed
1499 ,p_days_late_receipt_allowed => l_item.receiving_obj_type.days_late_receipt_allowed
1500 ,p_allow_substitute_receipts_f => l_item.receiving_obj_type.allow_substitute_receipts_f
1501 ,p_allow_unordered_receipts_fl => l_item.receiving_obj_type.allow_unordered_receipts_fl
1502 ,p_allow_express_delivery_flag => l_item.receiving_obj_type.allow_express_delivery_flag
1503 ,p_qty_rcv_exception_code => l_item.receiving_obj_type.qty_rcv_exception_code
1504 ,p_qty_rcv_tolerance => l_item.receiving_obj_type.qty_rcv_tolerance
1505 ,p_receiving_routing_id => l_item.receiving_obj_type.receiving_routing_id
1506 ,p_enforce_ship_to_location_c => l_item.receiving_obj_type.enforce_ship_to_location_c
1507 ,p_weight_uom_code => l_item.physical_obj_type.weight_uom_code
1508 ,p_unit_weight => l_item.physical_obj_type.unit_weight
1509 ,p_volume_uom_code => l_item.physical_obj_type.volume_uom_code
1510 ,p_unit_volume => l_item.physical_obj_type.unit_volume
1511 ,p_container_item_flag => l_item.physical_obj_type.container_item_flag
1512 ,p_vehicle_item_flag => l_item.physical_obj_type.vehicle_item_flag
1513 ,p_container_type_code => l_item.physical_obj_type.container_type_code
1514 ,p_internal_volume => l_item.physical_obj_type.internal_volume
1515 ,p_maximum_load_weight => l_item.physical_obj_type.maximum_load_weight
1516 ,p_minimum_fill_percent => l_item.physical_obj_type.minimum_fill_percent
1517 ,p_inventory_planning_code => l_item.gplanning_obj_type.inventory_planning_code
1518 ,p_planner_code => l_item.gplanning_obj_type.planner_code
1519 ,p_planning_make_buy_code => l_item.gplanning_obj_type.planning_make_buy_code
1520 ,p_min_minmax_quantity => l_item.gplanning_obj_type.min_minmax_quantity
1521 ,p_max_minmax_quantity => l_item.gplanning_obj_type.max_minmax_quantity
1522 ,p_minimum_order_quantity => l_item.gplanning_obj_type.minimum_order_quantity
1523 ,p_maximum_order_quantity => l_item.gplanning_obj_type.maximum_order_quantity
1524 ,p_order_cost => l_item.gplanning_obj_type.order_cost
1525 ,p_carrying_cost => l_item.gplanning_obj_type.carrying_cost
1526 ,p_source_type => l_item.gplanning_obj_type.source_type
1527 ,p_source_organization_id => l_item.gplanning_obj_type.source_organization_id
1528 ,p_source_subinventory => l_item.gplanning_obj_type.source_subinventory
1529 ,p_mrp_safety_stock_code => l_item.gplanning_obj_type.mrp_safety_stock_code
1530 ,p_safety_stock_bucket_days => l_item.gplanning_obj_type.safety_stock_bucket_days
1531 ,p_mrp_safety_stock_percent => l_item.gplanning_obj_type.mrp_safety_stock_percent
1532 ,p_fixed_order_quantity => l_item.gplanning_obj_type.fixed_order_quantity
1533 ,p_fixed_days_supply => l_item.gplanning_obj_type.fixed_days_supply
1534 ,p_fixed_lot_multiplier => l_item.gplanning_obj_type.fixed_lot_multiplier
1535 ,p_mrp_planning_code => l_item.mrp_obj_type.mrp_planning_code
1536 ,p_ato_forecast_control => l_item.mrp_obj_type.ato_forecast_control
1537 ,p_planning_exception_set => l_item.mrp_obj_type.planning_exception_set
1538 ,p_end_assembly_pegging_flag => l_item.mrp_obj_type.end_assembly_pegging_flag
1539 ,p_shrinkage_rate => l_item.mrp_obj_type.shrinkage_rate
1540 ,p_rounding_control_type => l_item.mrp_obj_type.rounding_control_type
1541 ,p_acceptable_early_days => l_item.mrp_obj_type.acceptable_early_days
1542 ,p_repetitive_planning_flag => l_item.mrp_obj_type.repetitive_planning_flag
1543 ,p_overrun_percentage => l_item.mrp_obj_type.overrun_percentage
1544 ,p_acceptable_rate_increase => l_item.mrp_obj_type.acceptable_rate_increase
1545 ,p_acceptable_rate_decrease => l_item.mrp_obj_type.acceptable_rate_decrease
1546 ,p_mrp_calculate_atp_flag => l_item.mrp_obj_type.mrp_calculate_atp_flag
1547 ,p_auto_reduce_mps => l_item.mrp_obj_type.auto_reduce_mps
1548 ,p_planning_time_fence_code => l_item.mrp_obj_type.planning_time_fence_code
1549 ,p_planning_time_fence_days => l_item.mrp_obj_type.planning_time_fence_days
1550 ,p_demand_time_fence_code => l_item.mrp_obj_type.demand_time_fence_code
1551 ,p_demand_time_fence_days => l_item.mrp_obj_type.demand_time_fence_days
1552 ,p_release_time_fence_code => l_item.mrp_obj_type.release_time_fence_code
1553 ,p_release_time_fence_days => l_item.mrp_obj_type.release_time_fence_days
1554 ,p_preprocessing_lead_time => l_item.lead_time_obj_type.preprocessing_lead_time
1555 ,p_full_lead_time => l_item.lead_time_obj_type.full_lead_time
1556 ,p_postprocessing_lead_time => l_item.lead_time_obj_type.postprocessing_lead_time
1557 ,p_fixed_lead_time => l_item.lead_time_obj_type.fixed_lead_time
1558 ,p_variable_lead_time => l_item.lead_time_obj_type.variable_lead_time
1559 ,p_cum_manufacturing_lead_time => l_item.lead_time_obj_type.cum_manufacturing_lead_time
1560 ,p_cumulative_total_lead_time => l_item.lead_time_obj_type.cumulative_total_lead_time
1561 ,p_lead_time_lot_size => l_item.lead_time_obj_type.lead_time_lot_size
1562 ,p_build_in_wip_flag => l_item.wip_obj_type.build_in_wip_flag
1563 ,p_wip_supply_type => l_item.wip_obj_type.wip_supply_type
1564 ,p_wip_supply_subinventory => l_item.wip_obj_type.wip_supply_subinventory
1565 ,p_wip_supply_locator_id => l_item.wip_obj_type.wip_supply_locator_id
1566 ,p_overcompletion_tolerance_ty => l_item.wip_obj_type.overcompletion_tolerance_ty
1567 ,p_overcompletion_tolerance_va => l_item.wip_obj_type.overcompletion_tolerance_va
1568 ,p_customer_order_flag => l_item.order_obj_type.customer_order_flag
1569 ,p_customer_order_enabled_flag => l_item.order_obj_type.customer_order_enabled_flag
1570 ,p_shippable_item_flag => l_item.order_obj_type.shippable_item_flag
1571 ,p_internal_order_flag => l_item.order_obj_type.internal_order_flag
1572 ,p_internal_order_enabled_flag => l_item.order_obj_type.internal_order_enabled_flag
1573 ,p_so_transactions_flag => l_item.order_obj_type.so_transactions_flag
1574 ,p_pick_components_flag => l_item.order_obj_type.pick_components_flag
1575 ,p_atp_flag => l_item.order_obj_type.atp_flag
1576 ,p_replenish_to_order_flag => l_item.order_obj_type.replenish_to_order_flag
1577 ,p_atp_rule_id => l_item.order_obj_type.atp_rule_id
1578 ,p_atp_components_flag => l_item.order_obj_type.atp_components_flag
1579 ,p_ship_model_complete_flag => l_item.order_obj_type.ship_model_complete_flag
1580 ,p_picking_rule_id => l_item.order_obj_type.picking_rule_id
1581 ,p_collateral_flag => l_item.order_obj_type.collateral_flag
1582 ,p_default_shipping_org => l_item.order_obj_type.default_shipping_org
1583 ,p_returnable_flag => l_item.order_obj_type.returnable_flag
1584 ,p_return_inspection_requireme => l_item.order_obj_type.return_inspection_requireme
1585 ,p_over_shipment_tolerance => l_item.order_obj_type.over_shipment_tolerance
1586 ,p_under_shipment_tolerance => l_item.order_obj_type.under_shipment_tolerance
1587 ,p_over_return_tolerance => l_item.order_obj_type.over_return_tolerance
1588 ,p_under_return_tolerance => l_item.order_obj_type.under_return_tolerance
1589 ,p_invoiceable_item_flag => l_item.invoice_obj_type.invoiceable_item_flag
1590 ,p_invoice_enabled_flag => l_item.invoice_obj_type.invoice_enabled_flag
1591 ,p_accounting_rule_id => l_item.invoice_obj_type.accounting_rule_id
1592 ,p_invoicing_rule_id => l_item.invoice_obj_type.invoicing_rule_id
1593 ,p_tax_code => l_item.invoice_obj_type.tax_code
1594 ,p_sales_account => l_item.invoice_obj_type.sales_account
1595 ,p_payment_terms_id => l_item.invoice_obj_type.payment_terms_id
1596 ,p_coverage_schedule_id => l_item.service_obj_type.coverage_schedule_id
1597 ,p_service_duration => l_item.service_obj_type.service_duration
1598 ,p_service_duration_period_cod => l_item.service_obj_type.service_duration_period_cod
1599 ,p_serviceable_product_flag => l_item.service_obj_type.serviceable_product_flag
1600 ,p_service_starting_delay => l_item.service_obj_type.service_starting_delay
1601 ,p_material_billable_flag => l_item.service_obj_type.material_billable_flag
1602 ,p_serviceable_component_flag => l_item.deprecated_obj_type.serviceable_component_flag
1603 ,p_preventive_maintenance_flag => l_item.deprecated_obj_type.preventive_maintenance_flag
1604 ,p_prorate_service_flag => l_item.deprecated_obj_type.prorate_service_flag
1605 ,p_serviceable_item_class_id => l_item.deprecated_obj_type.serviceable_item_class_id
1606 ,p_base_warranty_service_id => l_item.deprecated_obj_type.base_warranty_service_id
1607 ,p_warranty_vendor_id => l_item.deprecated_obj_type.warranty_vendor_id
1608 ,p_max_warranty_amount => l_item.deprecated_obj_type.max_warranty_amount
1609 ,p_response_time_period_code => l_item.deprecated_obj_type.response_time_period_code
1610 ,p_response_time_value => l_item.deprecated_obj_type.response_time_value
1611 ,p_primary_specialist_id => l_item.deprecated_obj_type.primary_specialist_id
1612 ,p_secondary_specialist_id => l_item.deprecated_obj_type.secondary_specialist_id
1613 ,p_wh_update_date => l_item.deprecated_obj_type.wh_update_date
1614 ,p_equipment_type => l_item.physical_obj_type.equipment_type
1615 ,p_recovered_part_disp_code => l_item.service_obj_type.recovered_part_disp_code
1616 ,p_defect_tracking_on_flag => l_item.service_obj_type.defect_tracking_on_flag
1617 ,p_event_flag => l_item.physical_obj_type.event_flag
1618 ,p_electronic_flag => l_item.physical_obj_type.electronic_flag
1619 ,p_downloadable_flag => l_item.physical_obj_type.downloadable_flag
1620 ,p_vol_discount_exempt_flag => l_item.deprecated_obj_type.vol_discount_exempt_flag
1621 ,p_coupon_exempt_flag => l_item.deprecated_obj_type.coupon_exempt_flag
1622 ,p_comms_nl_trackable_flag => l_item.service_obj_type.comms_nl_trackable_flag
1623 ,p_asset_creation_code => l_item.service_obj_type.asset_creation_code
1624 ,p_comms_activation_reqd_flag => l_item.deprecated_obj_type.comms_activation_reqd_flag
1625 ,p_orderable_on_web_flag => l_item.web_option_obj_type.orderable_on_web_flag
1626 ,p_back_orderable_flag => l_item.web_option_obj_type.back_orderable_flag
1627 ,p_web_status => l_item.web_option_obj_type.web_status
1628 ,p_indivisible_flag => l_item.physical_obj_type.indivisible_flag
1629 ,p_dimension_uom_code => l_item.physical_obj_type.dimension_uom_code
1630 ,p_unit_length => l_item.physical_obj_type.unit_length
1631 ,p_unit_width => l_item.physical_obj_type.unit_width
1632 ,p_unit_height => l_item.physical_obj_type.unit_height
1633 ,p_bulk_picked_flag => l_item.inventory_obj_type.bulk_picked_flag
1634 ,p_lot_status_enabled => l_item.inventory_obj_type.lot_status_enabled
1635 ,p_default_lot_status_id => l_item.deprecated_obj_type.default_lot_status_id
1636 ,p_serial_status_enabled => l_item.inventory_obj_type.serial_status_enabled
1637 ,p_default_serial_status_id => l_item.deprecated_obj_type.default_serial_status_id
1638 ,p_lot_split_enabled => l_item.inventory_obj_type.lot_split_enabled
1639 ,p_lot_merge_enabled => l_item.inventory_obj_type.lot_merge_enabled
1640 ,p_inventory_carry_penalty => l_item.wip_obj_type.inventory_carry_penalty
1641 ,p_operation_slack_penalty => l_item.wip_obj_type.operation_slack_penalty
1642 ,p_financing_allowed_flag => l_item.order_obj_type.financing_allowed_flag
1643 ,p_eam_item_type => l_item.asset_obj_type.eam_item_type
1644 ,p_eam_activity_type_code => l_item.asset_obj_type.eam_activity_type_code
1645 ,p_eam_activity_cause_code => l_item.asset_obj_type.eam_activity_cause_code
1646 ,p_eam_act_notification_flag => l_item.asset_obj_type.eam_act_notification_flag
1647 ,p_eam_act_shutdown_status => l_item.asset_obj_type.eam_act_shutdown_status
1648 ,p_dual_uom_control => l_item.deprecated_obj_type.dual_uom_control
1649 ,p_secondary_uom_code => l_item.main_obj_type.secondary_uom_code
1650 ,p_dual_uom_deviation_high => l_item.main_obj_type.dual_uom_deviation_high
1651 ,p_dual_uom_deviation_low => l_item.main_obj_type.dual_uom_deviation_low
1652 ,p_contract_item_type_code => l_item.asset_obj_type.contract_item_type_code
1653 ,p_subscription_depend_flag => l_item.deprecated_obj_type.subscription_depend_flag
1654 ,p_serv_req_enabled_code => l_item.asset_obj_type.serv_req_enabled_code
1655 ,p_serv_billing_enabled_flag => l_item.asset_obj_type.serv_billing_enabled_flag
1656 ,p_serv_importance_level => l_item.deprecated_obj_type.serv_importance_level
1657 ,p_planned_inv_point_flag => l_item.mrp_obj_type.planned_inv_point_flag
1658 ,p_lot_translate_enabled => l_item.inventory_obj_type.lot_translate_enabled
1659 ,p_default_so_source_type => l_item.order_obj_type.default_so_source_type
1660 ,p_create_supply_flag => l_item.mrp_obj_type.create_supply_flag
1661 ,p_substitution_window_code => l_item.mrp_obj_type.substitution_window_code
1662 ,p_substitution_window_days => l_item.mrp_obj_type.substitution_window_days
1663 ,p_ib_item_instance_class => l_item.service_obj_type.ib_item_instance_class
1664 ,p_config_model_type => l_item.bom_obj_type.config_model_type
1665 ,p_lot_substitution_enabled => l_item.inventory_obj_type.lot_substitution_enabled
1666 ,p_minimum_license_quantity => l_item.web_option_obj_type.minimum_license_quantity
1667 ,p_eam_activity_source_code => l_item.asset_obj_type.eam_activity_source_code
1668 ,p_approval_status => l_item.deprecated_obj_type.approval_status
1669 ,p_tracking_quantity_ind => l_item.main_obj_type.tracking_quantity_ind
1670 ,p_ont_pricing_qty_source => l_item.main_obj_type.ont_pricing_qty_source
1671 ,p_secondary_default_ind => l_item.main_obj_type.secondary_default_ind
1672 ,p_option_specific_sourced => l_item.deprecated_obj_type.option_specific_sourced
1673 ,p_vmi_minimum_units => l_item.gplanning_obj_type.vmi_minimum_units
1674 ,p_vmi_minimum_days => l_item.gplanning_obj_type.vmi_minimum_days
1675 ,p_vmi_maximum_units => l_item.gplanning_obj_type.vmi_maximum_units
1676 ,p_vmi_maximum_days => l_item.gplanning_obj_type.vmi_maximum_days
1677 ,p_vmi_fixed_order_quantity => l_item.gplanning_obj_type.vmi_fixed_order_quantity
1678 ,p_so_authorization_flag => l_item.gplanning_obj_type.so_authorization_flag
1679 ,p_consigned_flag => l_item.gplanning_obj_type.consigned_flag
1680 ,p_asn_autoexpire_flag => l_item.gplanning_obj_type.asn_autoexpire_flag
1681 ,p_vmi_forecast_type => l_item.gplanning_obj_type.vmi_forecast_type
1682 ,p_forecast_horizon => l_item.gplanning_obj_type.forecast_horizon
1683 ,p_exclude_from_budget_flag => l_item.mrp_obj_type.exclude_from_budget_flag
1684 ,p_days_tgt_inv_supply => l_item.mrp_obj_type.days_tgt_inv_supply
1685 ,p_days_tgt_inv_window => l_item.mrp_obj_type.days_tgt_inv_window
1686 ,p_days_max_inv_supply => l_item.mrp_obj_type.days_max_inv_supply
1687 ,p_days_max_inv_window => l_item.mrp_obj_type.days_max_inv_window
1688 ,p_drp_planned_flag => l_item.mrp_obj_type.drp_planned_flag
1689 ,p_critical_component_flag => l_item.mrp_obj_type.critical_component_flag
1690 ,p_continous_transfer => l_item.mrp_obj_type.continous_transfer
1691 ,p_convergence => l_item.mrp_obj_type.convergence
1692 ,p_divergence => l_item.mrp_obj_type.divergence
1693 ,p_config_orgs => l_item.bom_obj_type.config_orgs
1694 ,p_config_match => l_item.bom_obj_type.config_match
1695 ,p_item_number => l_item.main_obj_type.item_number
1696 ,p_segment1 => l_item.main_obj_type.segment1
1697 ,p_segment2 => l_item.main_obj_type.segment2
1698 ,p_segment3 => l_item.main_obj_type.segment3
1699 ,p_segment4 => l_item.main_obj_type.segment4
1700 ,p_segment5 => l_item.main_obj_type.segment5
1701 ,p_segment6 => l_item.main_obj_type.segment6
1702 ,p_segment7 => l_item.main_obj_type.segment7
1703 ,p_segment8 => l_item.main_obj_type.segment8
1704 ,p_segment9 => l_item.main_obj_type.segment9
1705 ,p_segment10 => l_item.main_obj_type.segment10
1706 ,p_segment11 => l_item.main_obj_type.segment11
1707 ,p_segment12 => l_item.main_obj_type.segment12
1708 ,p_segment13 => l_item.main_obj_type.segment13
1709 ,p_segment14 => l_item.main_obj_type.segment14
1710 ,p_segment15 => l_item.main_obj_type.segment15
1711 ,p_segment16 => l_item.main_obj_type.segment16
1712 ,p_segment17 => l_item.main_obj_type.segment17
1713 ,p_segment18 => l_item.main_obj_type.segment18
1714 ,p_segment19 => l_item.main_obj_type.segment19
1715 ,p_segment20 => l_item.main_obj_type.segment20
1716 ,p_summary_flag => l_item.main_obj_type.summary_flag
1717 ,p_enabled_flag => l_item.main_obj_type.enabled_flag
1718 ,p_start_date_active => l_item.main_obj_type.start_date_active
1719 ,p_end_date_active => l_item.main_obj_type.end_date_active
1720 ,p_attribute_category => l_item.custom_obj_type.attribute_category
1721 ,p_attribute1 => l_item.custom_obj_type.attribute1
1722 ,p_attribute2 => l_item.custom_obj_type.attribute2
1723 ,p_attribute3 => l_item.custom_obj_type.attribute3
1724 ,p_attribute4 => l_item.custom_obj_type.attribute4
1725 ,p_attribute5 => l_item.custom_obj_type.attribute5
1726 ,p_attribute6 => l_item.custom_obj_type.attribute6
1727 ,p_attribute7 => l_item.custom_obj_type.attribute7
1728 ,p_attribute8 => l_item.custom_obj_type.attribute8
1729 ,p_attribute9 => l_item.custom_obj_type.attribute9
1730 ,p_attribute10 => l_item.custom_obj_type.attribute10
1731 ,p_attribute11 => l_item.custom_obj_type.attribute11
1732 ,p_attribute12 => l_item.custom_obj_type.attribute12
1733 ,p_attribute13 => l_item.custom_obj_type.attribute13
1734 ,p_attribute14 => l_item.custom_obj_type.attribute14
1735 ,p_attribute15 => l_item.custom_obj_type.attribute15
1736 ,p_attribute16 => l_item.custom_obj_type.attribute16
1737 ,p_attribute17 => l_item.custom_obj_type.attribute17
1738 ,p_attribute18 => l_item.custom_obj_type.attribute18
1739 ,p_attribute19 => l_item.custom_obj_type.attribute19
1740 ,p_attribute20 => l_item.custom_obj_type.attribute20
1741 ,p_attribute21 => l_item.custom_obj_type.attribute21
1742 ,p_attribute22 => l_item.custom_obj_type.attribute22
1743 ,p_attribute23 => l_item.custom_obj_type.attribute23
1744 ,p_attribute24 => l_item.custom_obj_type.attribute24
1745 ,p_attribute25 => l_item.custom_obj_type.attribute25
1746 ,p_attribute26 => l_item.custom_obj_type.attribute26
1747 ,p_attribute27 => l_item.custom_obj_type.attribute27
1748 ,p_attribute28 => l_item.custom_obj_type.attribute28
1749 ,p_attribute29 => l_item.custom_obj_type.attribute29
1750 ,p_attribute30 => l_item.custom_obj_type.attribute30
1751 ,p_global_attribute_category => l_item.custom_obj_type.global_attribute_category
1752 ,p_global_attribute1 => l_item.custom_obj_type.global_attribute1
1753 ,p_global_attribute2 => l_item.custom_obj_type.global_attribute2
1754 ,p_global_attribute3 => l_item.custom_obj_type.global_attribute3
1755 ,p_global_attribute4 => l_item.custom_obj_type.global_attribute4
1756 ,p_global_attribute5 => l_item.custom_obj_type.global_attribute5
1757 ,p_global_attribute6 => l_item.custom_obj_type.global_attribute6
1758 ,p_global_attribute7 => l_item.custom_obj_type.global_attribute7
1759 ,p_global_attribute8 => l_item.custom_obj_type.global_attribute8
1760 ,p_global_attribute9 => l_item.custom_obj_type.global_attribute9
1761 ,p_global_attribute10 => l_item.custom_obj_type.global_attribute10
1762 ,p_creation_date => l_item.main_obj_type.creation_date
1763 ,p_created_by => l_item.main_obj_type.created_by
1764 ,p_last_update_date => l_item.main_obj_type.last_update_date
1765 ,p_last_updated_by => l_item.main_obj_type.last_updated_by
1766 ,p_last_update_login => l_item.main_obj_type.last_update_login
1767 ,p_request_id => l_item.main_obj_type.request_id
1768 ,p_program_application_id => l_item.main_obj_type.program_application_id
1769 ,p_program_id => l_item.main_obj_type.program_id
1770 ,p_program_update_date => l_item.main_obj_type.program_update_date
1771 ,p_lifecycle_id => l_item.main_obj_type.lifecycle_id
1772 ,p_current_phase_id => l_item.main_obj_type.current_phase_id
1773 ,p_revision_id => l_item.main_obj_type.revision_id
1774 ,p_revision_code => l_item.main_obj_type.revision_code
1775 ,p_revision_label => l_item.main_obj_type.revision_label
1776 ,p_revision_description => l_item.main_obj_type.revision_description
1777 ,p_effectivity_date => l_item.main_obj_type.effectivity_date
1778 ,p_rev_lifecycle_id => l_item.main_obj_type.rev_lifecycle_id
1779 ,p_rev_current_phase_id => l_item.main_obj_type.rev_current_phase_id
1780 ,p_rev_attribute_category => l_item.custom_obj_type.rev_attribute_category
1781 ,p_rev_attribute1 => l_item.custom_obj_type.rev_attribute1
1782 ,p_rev_attribute2 => l_item.custom_obj_type.rev_attribute2
1783 ,p_rev_attribute3 => l_item.custom_obj_type.rev_attribute3
1784 ,p_rev_attribute4 => l_item.custom_obj_type.rev_attribute4
1785 ,p_rev_attribute5 => l_item.custom_obj_type.rev_attribute5
1786 ,p_rev_attribute6 => l_item.custom_obj_type.rev_attribute6
1787 ,p_rev_attribute7 => l_item.custom_obj_type.rev_attribute7
1788 ,p_rev_attribute8 => l_item.custom_obj_type.rev_attribute8
1789 ,p_rev_attribute9 => l_item.custom_obj_type.rev_attribute9
1790 ,p_rev_attribute10 => l_item.custom_obj_type.rev_attribute10
1791 ,p_rev_attribute11 => l_item.custom_obj_type.rev_attribute11
1792 ,p_rev_attribute12 => l_item.custom_obj_type.rev_attribute12
1793 ,p_rev_attribute13 => l_item.custom_obj_type.rev_attribute13
1794 ,p_rev_attribute14 => l_item.custom_obj_type.rev_attribute14
1795 ,p_rev_attribute15 => l_item.custom_obj_type.rev_attribute15
1796 ,p_apply_template => l_item.main_obj_type.apply_template
1797 ,p_object_version_number => l_item.deprecated_obj_type.object_version_number
1798 ,p_process_control => 'PLM_UI:N'
1799 ,x_inventory_item_id => x_out.inventory_item_id
1800 ,x_organization_id => x_out.organization_id
1801 ,x_return_status => x_out.output_status.return_status
1802 ,x_msg_count => x_out.output_status.msg_count
1803 ,x_msg_data => x_out.output_status.msg_data
1804 ,p_cas_number => l_item.process_manufacturing_obj.cas_number
1805 ,p_child_lot_flag => l_item.inventory_obj_type.child_lot_flag
1806 ,p_child_lot_prefix => l_item.inventory_obj_type.child_lot_prefix
1807 ,p_child_lot_starting_number => l_item.inventory_obj_type.child_lot_starting_number
1808 ,p_child_lot_validation_flag => l_item.inventory_obj_type.child_lot_validation_flag
1809 ,p_copy_lot_attribute_flag => l_item.inventory_obj_type.copy_lot_attribute_flag
1810 ,p_default_grade => l_item.inventory_obj_type.default_grade
1811 ,p_expiration_action_code => l_item.inventory_obj_type.expiration_action_code
1812 ,p_expiration_action_interval => l_item.inventory_obj_type.expiration_action_interval
1813 ,p_grade_control_flag => l_item.inventory_obj_type.grade_control_flag
1814 ,p_hazardous_material_flag => l_item.process_manufacturing_obj.hazardous_material_flag
1815 ,p_hold_days => l_item.inventory_obj_type.hold_days
1816 ,p_lot_divisible_flag => l_item.inventory_obj_type.lot_divisible_flag
1817 ,p_maturity_days => l_item.inventory_obj_type.maturity_days
1818 ,p_parent_child_generation_flag => l_item.inventory_obj_type.parent_child_generation_flag
1819 ,p_process_costing_enabled_flag => l_item.process_manufacturing_obj.process_costing_enabled_flag
1820 ,p_process_execution_enabled_fl => l_item.process_manufacturing_obj.process_execution_enabled_flag
1821 ,p_process_quality_enabled_flag => l_item.process_manufacturing_obj.process_quality_enabled_flag
1822 ,p_process_supply_locator_id => l_item.process_manufacturing_obj.process_supply_locator_id
1823 ,p_process_supply_subinventory => l_item.process_manufacturing_obj.process_supply_subinventory
1824 ,p_process_yield_locator_id => l_item.process_manufacturing_obj.process_yield_locator_id
1825 ,p_process_yield_subinventory => l_item.process_manufacturing_obj.process_yield_subinventory
1826 ,p_recipe_enabled_flag => l_item.process_manufacturing_obj.recipe_enabled_flag
1827 ,p_retest_interval => l_item.inventory_obj_type.retest_interval
1828 ,p_charge_periodicity_code => l_item.order_obj_type.charge_periodicity_code
1829 ,p_repair_leadtime => l_item.mrp_obj_type.repair_leadtime
1830 ,p_repair_yield => l_item.mrp_obj_type.repair_yield
1831 ,p_preposition_point => l_item.mrp_obj_type.preposition_point
1832 ,p_repair_program => l_item.mrp_obj_type.repair_program
1833 ,p_subcontracting_component => l_item.gplanning_obj_type.subcontracting_component
1834 ,p_outsourced_assembly => l_item.purchasing_obj_type.outsourced_assembly
1835 );
1836 INV_EBI_UTIL.debug_line('STEP: 30 '||'END CALLING EGO_ITEM_PUB.process_item ');
1837
1838 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
1839 RAISE FND_API.g_exc_unexpected_error;
1840 END IF;
1841 l_pk_col_name_val_pairs.EXTEND(1);
1842 l_pk_col_name_val_pairs(1).name := 'organization_id';
1843 l_pk_col_name_val_pairs(1).value := l_item.main_obj_type.organization_id;
1844 x_out.organization_code := id_to_value(
1845 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
1846 ,p_entity_name => G_ORGANIZATION
1847 );
1848 SELECT concatenated_segments INTO x_out.item_number
1849 FROM mtl_system_items_kfv
1850 WHERE organization_id = x_out.organization_id
1851 AND inventory_item_id = x_out.inventory_item_id;
1852
1853 get_Operating_unit
1854 (p_oranization_id => x_out.organization_id
1855 ,x_operating_unit => x_out.operating_unit
1856 ,x_ouid => x_out.operating_unit_id
1857 );
1858
1859
1860 IF(l_transaction_type = INV_EBI_ITEM_PUB.g_otype_create) THEN
1861 MTL_CROSS_REFERENCES_PKG.insert_row(
1862 p_source_system_id => NULL,
1863 p_start_date_active => SYSDATE,
1864 p_end_date_active => NULL,
1865 p_object_version_number => NULL,
1866 p_uom_code => NULL,
1867 p_revision_id => NULL,
1868 p_epc_gtin_serial => NULL,
1869 p_inventory_item_id => x_out.inventory_item_id,
1870 p_organization_id => x_out.organization_id,
1871 p_cross_reference_type => l_item.main_obj_type.cross_reference_type,
1872 p_cross_reference => l_item.main_obj_type.item_number, -- p_source_system_reference,
1873 p_org_independent_flag => 'N',
1874 p_request_id => NULL,
1875 p_attribute1 => NULL,
1876 p_attribute2 => NULL,
1877 p_attribute3 => NULL,
1878 p_attribute4 => NULL,
1879 p_attribute5 => NULL,
1880 p_attribute6 => NULL,
1881 p_attribute7 => NULL,
1882 p_attribute8 => NULL,
1883 p_attribute9 => NULL,
1884 p_attribute10 => NULL,
1885 p_attribute11 => NULL,
1886 p_attribute12 => NULL,
1887 p_attribute13 => NULL,
1888 p_attribute14 => NULL,
1889 p_attribute15 => NULL,
1890 p_attribute_category => NULL,
1891 p_description => l_item.main_obj_type.description,
1892 p_creation_date => SYSDATE,
1893 p_created_by => FND_GLOBAL.user_id,
1894 p_last_update_date => SYSDATE,
1895 p_last_updated_by => FND_GLOBAL.user_id,
1896 p_last_update_login => FND_GLOBAL.login_id,
1897 p_program_application_id => NULL,
1898 p_program_id => NULL,
1899 p_program_update_date => SYSDATE,
1900 x_cross_reference_id => l_xref_id);
1901 END IF;
1902 IF FND_API.to_boolean( p_commit ) THEN
1903 COMMIT;
1904 END IF;
1905 INV_EBI_UTIL.debug_line('STEP: 40 '||'END INSIDE INV_EBI_ITEM_HELPER.process_item_pvt ');
1906 EXCEPTION
1907 WHEN FND_API.g_exc_error THEN
1908 ROLLBACK TO inv_ebi_pvt_item_save_pnt;
1909 x_out.output_status.return_status := FND_API.g_ret_sts_error;
1910 IF(x_out.output_status.msg_data IS NULL) THEN
1911 fnd_msg_pub.count_and_get(
1912 p_encoded => fnd_api.g_false
1913 ,p_count => x_out.output_status.msg_count
1914 ,p_data => x_out.output_status.msg_data
1915 );
1916 END IF;
1917 WHEN FND_API.g_exc_unexpected_error THEN
1918 ROLLBACK TO inv_ebi_pvt_item_save_pnt;
1919 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
1920 IF(x_out.output_status.msg_data IS NULL) THEN
1921 fnd_msg_pub.count_and_get(
1922 p_encoded => fnd_api.g_false
1923 ,p_count => x_out.output_status.msg_count
1924 ,p_data => x_out.output_status.msg_data
1925 );
1926 END IF;
1927 WHEN OTHERS THEN
1928 ROLLBACK TO inv_ebi_pvt_item_save_pnt;
1929 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
1930 IF (x_out.output_status.msg_data IS NOT NULL) THEN
1931 x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_ITEM_HELPER.process_item_pvt ';
1932 ELSE
1933 x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.process_item_pvt ';
1934 END IF;
1935 END process_item_pvt;
1936 /************************************************************************************
1937 -- API name : process_item_uda
1938 -- Type : Public
1939 -- Function :
1940 --
1941 ************************************************************************************/
1942 PROCEDURE process_item_uda (
1943 p_api_version IN NUMBER DEFAULT 1.0
1944 ,p_inventory_item_id IN NUMBER
1945 ,p_organization_id IN NUMBER
1946 ,p_item_catalog_group_id IN NUMBER DEFAULT NULL
1947 ,p_revision_id IN NUMBER DEFAULT NULL
1948 ,p_revision_code IN VARCHAR2 DEFAULT NULL
1949 ,p_uda_input_obj IN inv_ebi_uda_input_obj
1950 ,p_commit IN VARCHAR2 := fnd_api.g_false
1951 ,x_uda_output_obj OUT NOCOPY inv_ebi_item_output_obj
1952 )
1953 IS
1954 l_uda_out inv_ebi_uda_output_obj;
1955 l_attributes_row_table ego_user_attr_row_table;
1956 l_attributes_data_table ego_user_attr_data_table;
1957 l_attributes_row_obj ego_user_attr_row_obj;
1958 l_transaction_type VARCHAR2(20);
1959 l_data_level VARCHAR2(25);
1960 l_revision_id NUMBER;
1961 l_output_status inv_ebi_output_status;
1962 BEGIN
1963 SAVEPOINT inv_ebi_item_uda_save_pnt;
1964 l_uda_out := inv_ebi_uda_output_obj(NULL,NULL);
1965 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
1966 x_uda_output_obj := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,l_uda_out,NULL,NULL,NULL,NULL);
1967 INV_EBI_UTIL.transform_uda (
1968 p_uda_input_obj => p_uda_input_obj
1969 ,x_attributes_row_table => l_attributes_row_table
1970 ,x_attributes_data_table => l_attributes_data_table
1971 ,x_return_status => x_uda_output_obj.output_status.return_status
1972 ,x_msg_count => x_uda_output_obj.output_status.msg_count
1973 ,x_msg_data => x_uda_output_obj.output_status.msg_data
1974 );
1975
1976 IF (x_uda_output_obj.output_status.return_status <> FND_API.g_ret_sts_success) THEN
1977 RAISE FND_API.g_exc_unexpected_error;
1978 END IF;
1979 FOR i in 1..l_attributes_row_table.COUNT
1980 LOOP
1981 l_attributes_row_obj := l_attributes_row_table(i);
1982 IF(l_attributes_row_table(i).attr_group_id IS NOT NULL AND p_item_catalog_group_id IS NOT NULL ) THEN
1983 SELECT data_level_int_name INTO l_data_level
1984 FROM ego_obj_attr_grp_assocs_v
1985 WHERE attr_group_id = l_attributes_row_table(i).attr_group_id
1986 AND classification_code = TO_CHAR(p_item_catalog_group_id);
1987 END IF;
1988 IF (l_data_level = INV_EBI_ITEM_PUB.g_data_level_item_rev ) THEN
1989
1990 IF p_revision_code IS NOT NULL AND p_revision_code <> fnd_api.g_miss_char THEN
1991 SELECT revision_id INTO l_revision_id
1992 FROM mtl_item_revisions
1993 WHERE inventory_item_id = p_inventory_item_id
1994 AND organization_id = p_organization_id
1995 AND revision = p_revision_code;
1996 ELSE
1997 FND_MESSAGE.set_name('INV','INV_EBI_ITEM_REVISION_CODE_NULL');
1998 FND_MSG_PUB.add;
1999 RAISE FND_API.g_exc_error;
2000 END IF;
2001 ELSE
2002 l_revision_id := l_attributes_row_obj.data_level_1;
2003 END IF;
2004 IF(l_attributes_row_table(i).transaction_type IS NULL) THEN
2005 l_transaction_type := ego_user_attrs_data_pvt.g_sync_mode;
2006 ELSE
2007 l_transaction_type := l_attributes_row_table(i).transaction_type;
2008 END IF;
2009 l_attributes_row_obj := EGO_USER_ATTRS_DATA_PUB.build_attr_group_row_object(
2010 p_row_identifier => i
2011 ,p_attr_group_id => l_attributes_row_obj.attr_group_id
2012 ,p_attr_group_app_id => l_attributes_row_obj.attr_group_app_id
2013 ,p_attr_group_type => l_attributes_row_obj.attr_group_type
2014 ,p_attr_group_name => l_attributes_row_obj.attr_group_name
2015 ,p_data_level => l_data_level
2016 ,p_data_level_1 => l_revision_id
2017 ,p_data_level_2 => l_attributes_row_obj.data_level_2
2018 ,p_data_level_3 => l_attributes_row_obj.data_level_3
2019 ,p_data_level_4 => l_attributes_row_obj.data_level_4
2020 ,p_data_level_5 => l_attributes_row_obj.data_level_5
2021 ,p_transaction_type => l_transaction_type
2022 );
2023 l_attributes_row_table(i) := l_attributes_row_obj;
2024 END LOOP;
2025 EGO_ITEM_PUB.process_user_attrs_for_item(
2026 p_api_version => p_api_version
2027 ,p_inventory_item_id => p_inventory_item_id
2028 ,p_organization_id => p_organization_id
2029 ,p_attributes_row_table => l_attributes_row_table
2030 ,p_attributes_data_table => l_attributes_data_table
2031 ,p_entity_id => p_uda_input_obj.entity_id
2032 ,p_entity_index => p_uda_input_obj.entity_index
2033 ,p_entity_code => p_uda_input_obj.entity_code
2034 ,p_debug_level => p_uda_input_obj.debug_level
2035 ,p_init_error_handler => p_uda_input_obj.init_error_handler
2036 ,p_write_to_concurrent_log => p_uda_input_obj.write_to_concurrent_log
2037 ,p_init_fnd_msg_list => p_uda_input_obj.init_fnd_msg_list
2038 ,p_log_errors => p_uda_input_obj.log_errors
2039 ,p_add_errors_to_fnd_stack => p_uda_input_obj.add_errors_to_fnd_stack
2040 ,p_commit => FND_API.g_false
2041 ,x_failed_row_id_list => x_uda_output_obj.uda_output.failed_row_id_list
2042 ,x_return_status => x_uda_output_obj.output_status.return_status
2043 ,x_errorcode => x_uda_output_obj.uda_output.errorcode
2044 ,x_msg_count => x_uda_output_obj.output_status.msg_count
2045 ,x_msg_data => x_uda_output_obj.output_status.msg_data
2046 );
2047
2048 IF (x_uda_output_obj.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2049 RAISE FND_API.g_exc_unexpected_error;
2050 END IF;
2051 IF FND_API.to_boolean(p_commit) THEN
2052 COMMIT;
2053 END IF;
2054 EXCEPTION
2055 WHEN FND_API.g_exc_error THEN
2056 ROLLBACK TO inv_ebi_item_uda_save_pnt;
2057 x_uda_output_obj.output_status.return_status := FND_API.g_ret_sts_error;
2058 IF(x_uda_output_obj.output_status.msg_data IS NULL) THEN
2059 FND_MSG_PUB.count_and_get(
2060 p_encoded => FND_API.g_false
2061 ,p_count => x_uda_output_obj.output_status.msg_count
2062 ,p_data => x_uda_output_obj.output_status.msg_data
2063 );
2064 END IF;
2065 WHEN FND_API.g_exc_unexpected_error THEN
2066 ROLLBACK TO inv_ebi_item_uda_save_pnt;
2067 x_uda_output_obj.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2068 IF(x_uda_output_obj.output_status.msg_data IS NULL) THEN
2069 FND_MSG_PUB.count_and_get(
2070 p_encoded => FND_API.g_false
2071 ,p_count => x_uda_output_obj.output_status.msg_count
2072 ,p_data => x_uda_output_obj.output_status.msg_data
2073 );
2074 END IF;
2075 WHEN OTHERS THEN
2076 ROLLBACK TO inv_ebi_item_uda_save_pnt;
2077 x_uda_output_obj.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2078 IF (x_uda_output_obj.output_status.msg_data IS NOT NULL) THEN
2079 x_uda_output_obj.output_status.msg_data := x_uda_output_obj.output_status.msg_data ||' -> INV_EBI_ITEM_HELPER.process_item_uda ';
2080 ELSE
2081 x_uda_output_obj.output_status.msg_data := SQLERRM||' at INV_EBI_ITEM_HELPER.process_item_uda ';
2082 END IF;
2083 END process_item_uda;
2084 /************************************************************************************
2085 -- API name : process_org_id_assignments
2086 -- Type : Public
2087 -- Function :
2088 -- ************************************************************************************/
2089 PROCEDURE process_org_id_assignments(
2090 p_init_msg_list IN VARCHAR2
2091 ,p_commit IN VARCHAR2 := fnd_api.g_false
2092 ,p_inventory_item_id IN NUMBER
2093 ,p_item_number IN VARCHAR2
2094 ,p_org_id_tbl IN inv_ebi_org_tbl
2095 ,x_out OUT NOCOPY inv_ebi_item_output_obj
2096 )
2097 IS
2098 l_item_org_assignment_rec ego_item_pub.item_org_assignment_rec_type;
2099 l_item_org_assignment_tbl ego_item_pub.item_org_assignment_tbl_type;
2100 l_output_status inv_ebi_output_status;
2101 l_api_version NUMBER:=1.0;
2102 l_item_org_tbl_count NUMBER := 1;
2103 BEGIN
2104 SAVEPOINT inv_ebi_org_id_save_pnt;
2105 INV_EBI_UTIL.debug_line('STEP: 10 '||'START INSIDE INV_EBI_ITEM_HELPER.process_org_id_assignments ');
2106 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
2107 x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL);
2108 IF p_org_id_tbl.COUNT > 0 THEN
2109 FOR i IN 1..p_org_id_tbl.COUNT LOOP
2110 l_item_org_assignment_rec.inventory_item_id := p_inventory_item_id;
2111 l_item_org_assignment_rec.item_number := p_item_number;
2112 l_item_org_assignment_rec.organization_id := p_org_id_tbl(i).org_id;
2113 l_item_org_assignment_tbl(i) := l_item_org_assignment_rec;
2114 END LOOP;
2115 INV_EBI_UTIL.debug_line('STEP: 20 '||'START CALLING EGO_ITEM_PUB.process_item_org_assignments ');
2116 EGO_ITEM_PUB.process_item_org_assignments(
2117 p_api_version => l_api_version --don't pass as of now
2118 ,p_init_msg_list => p_init_msg_list
2119 ,p_commit => FND_API.g_false
2120 ,p_item_org_assignment_tbl => l_item_org_assignment_tbl
2121 ,x_return_status => x_out.output_status.return_status
2122 ,x_msg_count => x_out.output_status.msg_count
2123 );
2124 INV_EBI_UTIL.debug_line('STEP: 30 '||'END CALLING EGO_ITEM_PUB.process_item_org_assignments ');
2125 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2126 RAISE FND_API.g_exc_unexpected_error;
2127 END IF;
2128 END IF;
2129 IF FND_API.to_boolean( p_commit ) THEN
2130 COMMIT;
2131 END IF;
2132 INV_EBI_UTIL.debug_line('STEP: 40 '||'END INSIDE INV_EBI_ITEM_HELPER.process_org_id_assignments ');
2133 EXCEPTION
2134 WHEN FND_API.g_exc_unexpected_error THEN
2135 ROLLBACK TO inv_ebi_org_id_save_pnt;
2136 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2137 IF(x_out.output_status.msg_data IS NULL) THEN
2138 FND_MSG_PUB.count_and_get(
2139 p_encoded => FND_API.g_false
2140 ,p_count => x_out.output_status.msg_count
2141 ,p_data => x_out.output_status.msg_data
2142 );
2143 END IF;
2144 WHEN OTHERS THEN
2145 ROLLBACK TO inv_ebi_org_id_save_pnt;
2146 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2147 IF (x_out.output_status.msg_data IS NOT NULL) THEN
2148 x_out.output_status.msg_data := x_out.output_status.msg_data||' -> INV_EBI_ITEM_HELPER.process_org_id_assignments ';
2149 ELSE
2150 x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.process_org_id_assignments ';
2151 END IF;
2152 END process_org_id_assignments;
2153 /************************************************************************************
2154 -- API name : process_category_assignments
2155 -- Type : Public
2156 -- Function :
2157 --
2158 ************************************************************************************/
2159 PROCEDURE process_category_assignments(
2160 p_api_version IN NUMBER DEFAULT 1.0
2161 ,p_init_msg_list IN VARCHAR2
2162 ,p_commit IN VARCHAR2 := fnd_api.g_false
2163 ,p_inventory_item_id IN NUMBER
2164 ,p_organization_id IN NUMBER
2165 ,p_category_id_tbl IN inv_ebi_category_obj_tbl_type
2166 ,x_out OUT NOCOPY inv_ebi_item_output_obj
2167 )
2168 IS
2169 l_transaction_type VARCHAR2(20):=ego_item_pub.g_ttype_create;
2170 l_category_output inv_ebi_category_output_obj;
2171 l_output_status inv_ebi_output_status;
2172 BEGIN
2173 SAVEPOINT inv_ebi_cat_id_save_pnt;
2174 INV_EBI_UTIL.debug_line('STEP: 10 '||'START INSIDE INV_EBI_ITEM_HELPER.process_category_assignments ');
2175 l_category_output := inv_ebi_category_output_obj(NULL);
2176 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
2177 x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,l_category_output,NULL,NULL,NULL);
2178 INV_EBI_UTIL.debug_line('STEP: 20 '||'START CALLING EGO_ITEM_PUB.process_item_cat_assignment ');
2179 FOR i IN 1..p_category_id_tbl.COUNT LOOP
2180 EGO_ITEM_PUB.process_item_cat_assignment(
2181 p_api_version => p_api_version
2182 ,p_init_msg_list => p_init_msg_list
2183 ,p_commit => FND_API.g_false
2184 ,p_category_id => p_category_id_tbl(i).cat_id
2185 ,p_category_set_id => p_category_id_tbl(i).cat_set_id
2186 ,p_inventory_item_id => p_inventory_item_id
2187 ,p_organization_id => p_organization_id
2188 ,p_transaction_type => l_transaction_type
2189 ,x_return_status => x_out.output_status.return_status
2190 ,x_errorcode => x_out.category_output.error_code
2191 ,x_msg_count => x_out.output_status.msg_count
2192 ,x_msg_data => x_out.output_status.msg_data
2193 );
2194 END LOOP;
2195 INV_EBI_UTIL.debug_line('STEP: 30 '||'END CALLING EGO_ITEM_PUB.process_item_cat_assignment ');
2196 IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2197 RAISE FND_API.g_exc_unexpected_error;
2198 END IF;
2199 IF FND_API.to_boolean( p_commit ) THEN
2200 COMMIT;
2201 END IF;
2202 INV_EBI_UTIL.debug_line('STEP: 40 '||'END INSIDE INV_EBI_ITEM_HELPER.process_category_assignments ');
2203 EXCEPTION
2204 WHEN FND_API.g_exc_unexpected_error THEN
2205 ROLLBACK TO inv_ebi_cat_id_save_pnt;
2206 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2207 IF(x_out.output_status.msg_data IS NULL) THEN
2208 FND_MSG_PUB.count_and_get(
2209 p_encoded => FND_API.g_false
2210 ,p_count => x_out.output_status.msg_count
2211 ,p_data => x_out.output_status.msg_data
2212 );
2213 END IF;
2214 WHEN OTHERS THEN
2215 ROLLBACK TO inv_ebi_cat_id_save_pnt;
2216 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2217 IF (x_out.output_status.msg_data IS NOT NULL) THEN
2218 x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_ITEM_HELPER.process_category_assignments ';
2219 ELSE
2220 x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.process_category_assignments ';
2221 END IF;
2222 END process_category_assignments;
2223 /************************************************************************************
2224 -- API name : process_part_num_association
2225 -- Type : Public
2226 -- Function :
2227 -- This API is used to
2228 --
2229 ************************************************************************************/
2230 PROCEDURE process_part_num_association(
2231 p_commit IN VARCHAR2 := fnd_api.g_false
2232 ,p_organization_id IN NUMBER
2233 ,p_inventory_item_id IN NUMBER
2234 ,p_mfg_part_obj IN inv_ebi_manufacturer_part_obj
2235 ,x_out OUT NOCOPY inv_ebi_item_output_obj
2236 )
2237 IS
2238 l_count NUMBER := 0;
2239 l_rowid VARCHAR2(100);
2240 l_manufacturer_id mtl_mfg_part_numbers.manufacturer_id%TYPE;
2241 l_mfg_part_num mtl_mfg_part_numbers.mfg_part_num%TYPE;
2242 l_inventory_item_id mtl_mfg_part_numbers.inventory_item_id%TYPE;
2243 l_organization_id mtl_mfg_part_numbers.organization_id%TYPE;
2244 l_description mtl_mfg_part_numbers.description%TYPE;
2245 l_attribute_category mtl_mfg_part_numbers.attribute_category%TYPE;
2246 l_attribute1 mtl_mfg_part_numbers.attribute1%TYPE;
2247 l_attribute2 mtl_mfg_part_numbers.attribute2%TYPE;
2248 l_attribute3 mtl_mfg_part_numbers.attribute3%TYPE;
2249 l_attribute4 mtl_mfg_part_numbers.attribute4%TYPE;
2250 l_attribute5 mtl_mfg_part_numbers.attribute5%TYPE;
2251 l_attribute6 mtl_mfg_part_numbers.attribute6%TYPE;
2252 l_attribute7 mtl_mfg_part_numbers.attribute7%TYPE;
2253 l_attribute8 mtl_mfg_part_numbers.attribute8%TYPE;
2254 l_attribute9 mtl_mfg_part_numbers.attribute9%TYPE;
2255 l_attribute10 mtl_mfg_part_numbers.attribute10%TYPE;
2256 l_attribute11 mtl_mfg_part_numbers.attribute11%TYPE;
2257 l_attribute12 mtl_mfg_part_numbers.attribute12%TYPE;
2258 l_attribute13 mtl_mfg_part_numbers.attribute13%TYPE;
2259 l_attribute14 mtl_mfg_part_numbers.attribute14%TYPE;
2260 l_attribute15 mtl_mfg_part_numbers.attribute15%TYPE;
2261 l_output_status inv_ebi_output_status;
2262 CURSOR c_mfg_part_num(
2263 p_manufacturer_id IN NUMBER
2264 ,p_mfg_part_num IN VARCHAR2
2265 ,P_organization_id IN NUMBER
2266 ,p_inventory_item_id IN NUMBER
2267 ) IS
2268 SELECT
2269 rowid
2270 ,manufacturer_id
2271 ,mfg_part_num
2272 ,inventory_item_id
2273 ,organization_id
2274 ,description
2275 ,attribute_category
2276 ,attribute1
2277 ,attribute2
2278 ,attribute3
2279 ,attribute4
2280 ,attribute5
2281 ,attribute6
2282 ,attribute7
2283 ,attribute8
2284 ,attribute9
2285 ,attribute10
2286 ,attribute11
2287 ,attribute12
2288 ,attribute13
2289 ,attribute14
2290 ,attribute15
2291 FROM mtl_mfg_part_numbers
2292 WHERE manufacturer_id = p_manufacturer_id
2293 AND mfg_part_num = p_mfg_part_num
2294 AND organization_id = p_organization_id
2295 AND inventory_item_id = p_inventory_item_id;
2296 BEGIN
2297 SAVEPOINT inv_ebi_part_num_save_pnt;
2298 INV_EBI_UTIL.debug_line('STEP: 10 '||'START INSIDE INV_EBI_ITEM_HELPER.process_part_num_association ');
2299 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
2300 x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL);
2301 IF p_mfg_part_obj.mfg_part_num IS NOT NULL THEN
2302 OPEN c_mfg_part_num(
2303 p_manufacturer_id => p_mfg_part_obj.manufacturer_id
2304 ,p_mfg_part_num => p_mfg_part_obj.mfg_part_num
2305 ,P_organization_id => p_organization_id
2306 ,p_inventory_item_id => p_inventory_item_id
2307 );
2308 FETCH c_mfg_part_num INTO
2309 l_rowid
2310 ,l_manufacturer_id
2311 ,l_mfg_part_num
2312 ,l_inventory_item_id
2313 ,l_organization_id
2314 ,l_description
2315 ,l_attribute_category
2316 ,l_attribute1
2317 ,l_attribute2
2318 ,l_attribute3
2319 ,l_attribute4
2320 ,l_attribute5
2321 ,l_attribute6
2322 ,l_attribute7
2323 ,l_attribute8
2324 ,l_attribute9
2325 ,l_attribute10
2326 ,l_attribute11
2327 ,l_attribute12
2328 ,l_attribute13
2329 ,l_attribute14
2330 ,l_attribute15 ;
2331 IF (p_inventory_item_id <> FND_API.G_MISS_NUM ) THEN
2332 l_inventory_item_id := p_inventory_item_id;
2333 END IF;
2334 IF (p_organization_id <> FND_API.G_MISS_NUM ) THEN
2335 l_organization_id := p_organization_id;
2336 END IF;
2337 IF (p_mfg_part_obj.description <> FND_API.G_MISS_CHAR) THEN
2338 l_description := p_mfg_part_obj.description;
2339 END IF;
2340 IF (p_mfg_part_obj.attribute_category <> FND_API.G_MISS_CHAR) THEN
2341 l_attribute_category := p_mfg_part_obj.attribute_category;
2342 END IF;
2343 IF (p_mfg_part_obj.attribute1 <> FND_API.G_MISS_CHAR) THEN
2344 l_attribute1 := p_mfg_part_obj.attribute1;
2345 END IF;
2346 IF (p_mfg_part_obj.attribute2 <> FND_API.G_MISS_CHAR) THEN
2347 l_attribute2 := p_mfg_part_obj.attribute2;
2348 END IF;
2349 IF (p_mfg_part_obj.attribute3 <> FND_API.G_MISS_CHAR ) THEN
2350 l_attribute3 := p_mfg_part_obj.attribute3;
2351 END IF;
2352 IF (p_mfg_part_obj.attribute4 <> FND_API.G_MISS_CHAR) THEN
2353 l_attribute4 := p_mfg_part_obj.attribute4;
2354 END IF;
2355 IF (p_mfg_part_obj.attribute5 <> FND_API.G_MISS_CHAR) THEN
2356 l_attribute5 := p_mfg_part_obj.attribute5;
2357 END IF;
2358 IF (p_mfg_part_obj.attribute6 <> FND_API.G_MISS_CHAR) THEN
2359 l_attribute6 := p_mfg_part_obj.attribute6;
2360 END IF;
2361 IF (p_mfg_part_obj.attribute7 <> FND_API.G_MISS_CHAR) THEN
2362 l_attribute7 := p_mfg_part_obj.attribute7;
2363 END IF;
2364 IF (p_mfg_part_obj.attribute8 <> FND_API.G_MISS_CHAR) THEN
2365 l_attribute8 := p_mfg_part_obj.attribute8;
2366 END IF;
2367 IF (p_mfg_part_obj.attribute9 <> FND_API.G_MISS_CHAR) THEN
2368 l_attribute9 := p_mfg_part_obj.attribute9;
2369 END IF;
2370 IF (p_mfg_part_obj.attribute10 <> FND_API.G_MISS_CHAR) THEN
2371 l_attribute10 := p_mfg_part_obj.attribute10;
2372 END IF;
2373 IF (p_mfg_part_obj.attribute11 <> FND_API.G_MISS_CHAR) THEN
2374 l_attribute11 := p_mfg_part_obj.attribute11;
2375 END IF;
2376 IF (p_mfg_part_obj.attribute12 <> FND_API.G_MISS_CHAR ) THEN
2377 l_attribute12 := p_mfg_part_obj.attribute12;
2378 END IF;
2379 IF (p_mfg_part_obj.attribute13 <> FND_API.G_MISS_CHAR) THEN
2380 l_attribute13 := p_mfg_part_obj.attribute13;
2381 END IF;
2382 IF (p_mfg_part_obj.attribute14 <> FND_API.G_MISS_CHAR) THEN
2383 l_attribute14 := p_mfg_part_obj.attribute14;
2384 END IF;
2385 IF (p_mfg_part_obj.attribute15 <> FND_API.G_MISS_CHAR) THEN
2386 l_attribute15 := p_mfg_part_obj.attribute15;
2387 END IF;
2388
2389 IF UPPER(p_mfg_part_obj.transaction_type) = ENG_GLOBALS.G_OPR_CREATE THEN
2390 INV_EBI_UTIL.debug_line('STEP: 20 '||'START CALLING MTL_MFG_PART_NUMBERS_PKG.insert_row ');
2391 MTL_MFG_PART_NUMBERS_PKG.insert_row(
2392 x_rowid => l_rowid
2393 ,x_manufacturer_id => p_mfg_part_obj.manufacturer_id
2394 ,x_mfg_part_num => p_mfg_part_obj.mfg_part_num
2395 ,x_inventory_item_id => p_inventory_item_id
2396 ,x_last_update_date => SYSDATE
2397 ,x_last_updated_by => fnd_global.user_id
2398 ,x_creation_date => SYSDATE
2399 ,x_created_by => fnd_global.user_id
2400 ,x_last_update_login => fnd_global.login_id
2401 ,x_organization_id => p_organization_id
2402 ,x_description => p_mfg_part_obj.description
2403 ,x_attribute_category => p_mfg_part_obj.attribute_category
2404 ,x_attribute1 => p_mfg_part_obj.attribute1
2405 ,x_attribute2 => p_mfg_part_obj.attribute2
2406 ,x_attribute3 => p_mfg_part_obj.attribute3
2407 ,x_attribute4 => p_mfg_part_obj.attribute4
2408 ,x_attribute5 => p_mfg_part_obj.attribute5
2409 ,x_attribute6 => p_mfg_part_obj.attribute6
2410 ,x_attribute7 => p_mfg_part_obj.attribute7
2411 ,x_attribute8 => p_mfg_part_obj.attribute8
2412 ,x_attribute9 => p_mfg_part_obj.attribute9
2413 ,x_attribute10 => p_mfg_part_obj.attribute10
2414 ,x_attribute11 => p_mfg_part_obj.attribute11
2415 ,x_attribute12 => p_mfg_part_obj.attribute12
2416 ,x_attribute13 => p_mfg_part_obj.attribute13
2417 ,x_attribute14 => p_mfg_part_obj.attribute14
2418 ,x_attribute15 => p_mfg_part_obj.attribute15
2419 );
2420 INV_EBI_UTIL.debug_line('STEP: 30 '||'END CALLING MTL_MFG_PART_NUMBERS_PKG.insert_row ');
2421 ELSIF UPPER(p_mfg_part_obj.transaction_type) = ENG_GLOBALS.G_OPR_UPDATE THEN
2422 INV_EBI_UTIL.debug_line('STEP: 40 '||'START CALLING MTL_MFG_PART_NUMBERS_PKG.update_row ');
2423 MTL_MFG_PART_NUMBERS_PKG.update_row(
2424 x_rowid => l_rowid
2425 ,x_manufacturer_id => l_manufacturer_id
2426 ,x_mfg_part_num => l_mfg_part_num
2427 ,x_inventory_item_id => l_inventory_item_id
2428 ,x_last_update_date => SYSDATE
2429 ,x_last_updated_by => fnd_global.user_id
2430 ,x_last_update_login => fnd_global.login_id
2431 ,x_organization_id => l_organization_id
2432 ,x_description => l_description
2433 ,x_attribute_category => l_attribute_category
2434 ,x_attribute1 => l_attribute1
2435 ,x_attribute2 => l_attribute2
2436 ,x_attribute3 => l_attribute3
2437 ,x_attribute4 => l_attribute4
2438 ,x_attribute5 => l_attribute5
2439 ,x_attribute6 => l_attribute6
2440 ,x_attribute7 => l_attribute7
2441 ,x_attribute8 => l_attribute8
2442 ,x_attribute9 => l_attribute9
2443 ,x_attribute10 => l_attribute10
2444 ,x_attribute11 => l_attribute11
2445 ,x_attribute12 => l_attribute12
2446 ,x_attribute13 => l_attribute13
2447 ,x_attribute14 => l_attribute14
2448 ,x_attribute15 => l_attribute15
2449 );
2450 INV_EBI_UTIL.debug_line('STEP: 50 '||'END CALLING MTL_MFG_PART_NUMBERS_PKG.update_row ');
2451 ELSIF UPPER(p_mfg_part_obj.transaction_type) = ENG_GLOBALS.G_OPR_DELETE THEN
2452 INV_EBI_UTIL.debug_line('STEP: 60 '||'START CALLING MTL_MFG_PART_NUMBERS_PKG.delete_row ');
2453 MTL_MFG_PART_NUMBERS_PKG.Delete_Row(l_Rowid);
2454 INV_EBI_UTIL.debug_line('STEP: 70 '||'END CALLING MTL_MFG_PART_NUMBERS_PKG.delete_row ');
2455 ELSIF (p_mfg_part_obj.transaction_type IS NULL) THEN
2456 IF (c_mfg_part_num%NOTFOUND) THEN
2457 INV_EBI_UTIL.debug_line('STEP: 80 '||'START CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.insert_row ');
2458 MTL_MFG_PART_NUMBERS_PKG.insert_row(
2459 x_rowid => l_rowid
2460 ,x_manufacturer_id => p_mfg_part_obj.manufacturer_id
2461 ,x_mfg_part_num => p_mfg_part_obj.mfg_part_num
2462 ,x_inventory_item_id => p_inventory_item_id
2463 ,x_last_update_date => SYSDATE
2464 ,x_last_updated_by => fnd_global.user_id
2465 ,x_creation_date => SYSDATE
2466 ,x_created_by => fnd_global.user_id
2467 ,x_last_update_login => fnd_global.login_id
2468 ,x_organization_id => p_organization_id
2469 ,x_description => p_mfg_part_obj.description
2470 ,x_attribute_category => p_mfg_part_obj.attribute_category
2471 ,x_attribute1 => p_mfg_part_obj.attribute1
2472 ,x_attribute2 => p_mfg_part_obj.attribute2
2473 ,x_attribute3 => p_mfg_part_obj.attribute3
2474 ,x_attribute4 => p_mfg_part_obj.attribute4
2475 ,x_attribute5 => p_mfg_part_obj.attribute5
2476 ,x_attribute6 => p_mfg_part_obj.attribute6
2477 ,x_attribute7 => p_mfg_part_obj.attribute7
2478 ,x_attribute8 => p_mfg_part_obj.attribute8
2479 ,x_attribute9 => p_mfg_part_obj.attribute9
2480 ,x_attribute10 => p_mfg_part_obj.attribute10
2481 ,x_attribute11 => p_mfg_part_obj.attribute11
2482 ,x_attribute12 => p_mfg_part_obj.attribute12
2483 ,x_attribute13 => p_mfg_part_obj.attribute13
2484 ,x_attribute14 => p_mfg_part_obj.attribute14
2485 ,x_attribute15 => p_mfg_part_obj.attribute15
2486 );
2487 INV_EBI_UTIL.debug_line('STEP: 90 '||'END CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.insert_row ');
2488 ELSE
2489 INV_EBI_UTIL.debug_line('STEP: 100 '||'START CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.update_row ');
2490 MTL_MFG_PART_NUMBERS_PKG.update_row(
2491 x_rowid => l_rowid
2492 ,x_manufacturer_id => l_manufacturer_id
2493 ,x_mfg_part_num => l_mfg_part_num
2494 ,x_inventory_item_id => l_inventory_item_id
2495 ,x_last_update_date => SYSDATE
2496 ,x_last_updated_by => fnd_global.user_id
2497 ,x_last_update_login => fnd_global.login_id
2498 ,x_organization_id => l_organization_id
2499 ,x_description => l_description
2500 ,x_attribute_category => l_attribute_category
2501 ,x_attribute1 => l_attribute1
2502 ,x_attribute2 => l_attribute2
2503 ,x_attribute3 => l_attribute3
2504 ,x_attribute4 => l_attribute4
2505 ,x_attribute5 => l_attribute5
2506 ,x_attribute6 => l_attribute6
2507 ,x_attribute7 => l_attribute7
2508 ,x_attribute8 => l_attribute8
2509 ,x_attribute9 => l_attribute9
2510 ,x_attribute10 => l_attribute10
2511 ,x_attribute11 => l_attribute11
2512 ,x_attribute12 => l_attribute12
2513 ,x_attribute13 => l_attribute13
2514 ,x_attribute14 => l_attribute14
2515 ,x_attribute15 => l_attribute15
2516 );
2517 INV_EBI_UTIL.debug_line('STEP: 110 '||'END CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.update_row ');
2518 END IF;
2519 END IF;
2520 CLOSE c_mfg_part_num;
2521 ELSE
2522 FND_MESSAGE.set_name('INV_EBI','INV_EBI_PART_NUM_NULL');
2523 FND_MESSAGE.set_token('PART_NUM', p_mfg_part_obj.mfg_part_num);
2524 FND_MSG_PUB.add;
2525 RAISE FND_API.g_exc_error;
2526 END IF;
2527 IF FND_API.to_boolean( p_commit ) THEN
2528 COMMIT;
2529 END IF;
2530 INV_EBI_UTIL.debug_line('STEP: 120 '||'END INSIDE INV_EBI_ITEM_HELPER.process_part_num_association ');
2531 EXCEPTION
2532 WHEN FND_API.g_exc_error THEN
2533 ROLLBACK TO inv_ebi_part_num_save_pnt;
2534 x_out.output_status.return_status := FND_API.g_ret_sts_error;
2535 IF(x_out.output_status.msg_data IS NULL) THEN
2536 FND_MSG_PUB.count_and_get(
2537 p_encoded => FND_API.g_false
2538 ,p_count => x_out.output_status.msg_count
2539 ,p_data => x_out.output_status.msg_data
2540 );
2541 END IF;
2542 WHEN FND_API.g_exc_unexpected_error THEN
2543 ROLLBACK TO inv_ebi_part_num_save_pnt;
2544 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2545 IF(x_out.output_status.msg_data IS NULL) THEN
2546 FND_MSG_PUB.count_and_get(
2547 p_encoded => FND_API.g_false
2548 ,p_count => x_out.output_status.msg_count
2549 ,p_data => x_out.output_status.msg_data
2550 );
2551 END IF;
2552 WHEN OTHERS THEN
2553 ROLLBACK TO inv_ebi_part_num_save_pnt;
2554 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2555 IF (x_out.output_status.msg_data IS NOT NULL) THEN
2556 x_out.output_status.msg_data := x_out.output_status.msg_data||' -> INV_EBI_ITEM_HELPER.process_part_num_association ';
2557 ELSE
2558 x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.process_part_num_association ';
2559 END IF;
2560 END process_part_num_association;
2561
2562 /************************************************************************************
2563 -- API name : sync_item
2564 -- Type : Public
2565 -- Function :
2566 --
2567 --Should include API calls for Item alternative Catalog ,create Manufacture,Manufacture part
2568 --Template Ids??
2569 --Check if Organization Ids are there and how assignments to multiple orgs have to be handled
2570 ************************************************************************************/
2571 Procedure sync_item (
2572 p_commit IN VARCHAR2 := FND_API.g_false
2573 ,p_operation IN VARCHAR2
2574 ,p_item IN inv_ebi_item_obj
2575 ,x_out OUT NOCOPY inv_ebi_item_output_obj
2576 ) IS
2577 l_out inv_ebi_item_output_obj;
2578 l_api_version NUMBER:=1.0;
2579 l_inventory_item_id NUMBER;
2580 l_organization_id NUMBER;
2581 l_organization_code VARCHAR2(3);
2582 l_item_number VARCHAR2(2000);
2583 l_output_status inv_ebi_output_status;
2584 l_category_output inv_ebi_category_output_obj;
2585
2586 l_mfg_part_num_obj inv_ebi_manufacturer_part_obj;
2587 l_master_org_id NUMBER;
2588 l_manufacturer_count NUMBER := 0;
2589 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
2590 l_operating_unit VARCHAR2(240);
2591 l_operating_unit_id NUMBER;
2592 BEGIN
2593 SAVEPOINT inv_ebi_sync_item_save_pnt;
2594 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
2595 x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL);
2596 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
2597
2598 --Create or update item
2599 INV_EBI_UTIL.debug_line('STEP: 10 '||'START INSIDE INV_EBI_ITEM_HELPER.sync_item '||
2600 'ORGANIZATION CODE: '||p_item.main_obj_type.organization_code||
2601 'Item Number: '||p_item.main_obj_type.item_number
2602 );
2603 INV_EBI_UTIL.debug_line('STEP: 20 '||'START CALLING INV_EBI_ITEM_HELPER.process_item_pvt ');
2604 process_item_pvt (
2605 p_item => p_item
2606 ,p_operation => p_operation
2607 ,p_commit => FND_API.g_false
2608 ,x_out => l_out
2609 );
2610 INV_EBI_UTIL.debug_line('STEP: 30 '||'END CALLING INV_EBI_ITEM_HELPER.process_item_pvt ');
2611 IF (l_out.output_status.return_status <> fnd_api.g_ret_sts_success) THEN
2612 x_out.output_status.msg_data := l_out.output_status.msg_data;
2613 RAISE fnd_api.g_exc_unexpected_error;
2614 END IF;
2615 l_inventory_item_id := l_out.inventory_item_id;
2616 l_organization_id := l_out.organization_id;
2617 l_organization_code := l_out.organization_code;
2618 l_item_number := l_out.item_number;
2619 l_operating_unit := l_out.operating_unit;
2620 l_operating_unit_id := l_out.operating_unit_id;
2621 --Assign item to all the orgs sent in the list.
2622 IF (p_item.org_id_obj_type IS NOT NULL AND p_item.org_id_obj_type.COUNT > 0) THEN
2623 INV_EBI_UTIL.debug_line('STEP: 40 '||'START CALLING INV_EBI_ITEM_HELPER.process_org_id_assignments ');
2624 process_org_id_assignments(
2625 p_init_msg_list => p_item.main_obj_type.init_msg_list
2626 ,p_commit => fnd_api.g_false
2627 ,p_inventory_item_id => l_inventory_item_id
2628 ,p_item_number => p_item.main_obj_type.item_number
2629 ,p_org_id_tbl => p_item.org_id_obj_type
2630 ,x_out => l_out
2631 );
2632 INV_EBI_UTIL.debug_line('STEP: 40 '||'END CALLING INV_EBI_ITEM_HELPER.process_org_id_assignments ');
2633 END IF;
2634 IF (l_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2635 x_out.output_status.msg_data := l_out.output_status.msg_data;
2636 RAISE fnd_api.g_exc_unexpected_error;
2637 END IF;
2638 --Catalog Category assignment
2639 IF (p_item.category_id_obj_tbl_type IS NOT NULL AND p_item.category_id_obj_tbl_type.COUNT > 0) THEN
2640 INV_EBI_UTIL.debug_line('STEP: 50 '||'START CALLING INV_EBI_ITEM_HELPER.process_category_assignments ');
2641 process_category_assignments(
2642 p_api_version => l_api_version
2643 ,p_init_msg_list => p_item.main_obj_type.init_msg_list
2644 ,p_commit => fnd_api.g_false
2645 ,p_inventory_item_id => l_inventory_item_id
2646 ,p_organization_id => p_item.main_obj_type.organization_id
2647 ,p_category_id_tbl => p_item.category_id_obj_tbl_type
2648 ,x_out => l_out
2649 );
2650 INV_EBI_UTIL.debug_line('STEP: 60 '||'END CALLING INV_EBI_ITEM_HELPER.process_category_assignments ');
2651 END IF;
2652 IF (l_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2653 x_out.output_status.msg_data := l_out.output_status.msg_data;
2654 RAISE fnd_api.g_exc_unexpected_error;
2655 END IF;
2656 l_category_output := inv_ebi_category_output_obj(l_out.category_output.error_code);
2657 l_master_org_id := INV_EBI_UTIL.get_master_organization(
2658 p_organization_id => p_item.main_obj_type.organization_id
2659 );
2660
2661 --Manufacturer Part Num association needs to be done for master org only.
2662 IF(l_master_org_id = p_item.main_obj_type.organization_id) THEN
2663 IF (p_item.part_num_obj_tbl_type IS NOT NULL AND p_item.part_num_obj_tbl_type.COUNT > 0) THEN
2664 INV_EBI_UTIL.debug_line('STEP: 70 '||'START CALLING INV_EBI_ITEM_HELPER.process_part_num_association');
2665 FOR i IN p_item.part_num_obj_tbl_type.FIRST..p_item.part_num_obj_tbl_type.LAST
2666 LOOP
2667 l_mfg_part_num_obj := p_item.part_num_obj_tbl_type(i);
2668 IF(l_mfg_part_num_obj.manufacturer_id IS NOT NULL AND l_mfg_part_num_obj.manufacturer_id <> fnd_api.g_miss_num) THEN
2669 SELECT COUNT(1) INTO l_manufacturer_count
2670 FROM mtl_manufacturers
2671 WHERE manufacturer_id = l_mfg_part_num_obj.manufacturer_id;
2672 END IF;
2673 IF(l_manufacturer_count > 0 ) THEN
2674 process_part_num_association(
2675 p_commit => FND_API.g_false
2676 ,p_organization_id => p_item.main_obj_type.organization_id
2677 ,p_inventory_item_id => l_inventory_item_id
2678 ,p_mfg_part_obj => l_mfg_part_num_obj
2679 ,x_out => l_out
2680 );
2681 ELSE
2682
2683 --If manufcaturer does not exist raise exception
2684 FND_MESSAGE.set_name('INV','INV_EBI_MFG_NOT_EXIST');
2685 IF l_mfg_part_num_obj.manufacturer_name IS NULL THEN
2686 l_pk_col_name_val_pairs.EXTEND(1);
2687 l_pk_col_name_val_pairs(1).name := 'manufacturer_id';
2688 l_pk_col_name_val_pairs(1).value := l_mfg_part_num_obj.manufacturer_id;
2689 l_mfg_part_num_obj.manufacturer_name := id_to_value(
2690 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
2691 ,p_entity_name => G_MANUFACTURER
2692 );
2693 l_pk_col_name_val_pairs.TRIM(1);
2694 END IF;
2695 FND_MESSAGE.set_token('MFG_NAME', l_mfg_part_num_obj.manufacturer_name);
2696 FND_MSG_PUB.add;
2697 RAISE FND_API.g_exc_error;
2698 END IF;
2699 IF (l_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2700 x_out.output_status.msg_data := l_out.output_status.msg_data ;
2701 RAISE FND_API.g_exc_unexpected_error;
2702 END IF;
2703 END LOOP;
2704 INV_EBI_UTIL.debug_line('STEP: 80 '||'END CALLING INV_EBI_ITEM_HELPER.process_part_num_association');
2705 END IF;
2706 END IF;
2707 x_out := inv_ebi_item_output_obj(l_inventory_item_id,l_organization_id,l_organization_code,l_item_number,l_out.output_status,NULL,l_category_output,NULL,l_operating_unit,l_operating_unit_id);
2708
2709
2710
2711 IF fnd_api.to_boolean(p_commit) THEN
2712 COMMIT;
2713 END IF;
2714 INV_EBI_UTIL.debug_line('STEP: 90 '||'END INSIDE INV_EBI_ITEM_HELPER.sync_item '||
2715 'ORGANIZATION CODE: '||p_item.main_obj_type.organization_code||
2716 'Item Number: '||p_item.main_obj_type.item_number
2717 );
2718 EXCEPTION
2719 WHEN FND_API.g_exc_error THEN
2720 ROLLBACK TO inv_ebi_sync_item_save_pnt;
2721 x_out.output_status.return_status := FND_API.g_ret_sts_error;
2722 IF(x_out.output_status.msg_data IS NULL) THEN
2723 FND_MSG_PUB.count_and_get(
2724 p_encoded => FND_API.g_false
2725 ,p_count => x_out.output_status.msg_count
2726 ,p_data => x_out.output_status.msg_data
2727 );
2728 END IF;
2729 WHEN FND_API.g_exc_unexpected_error THEN
2730 ROLLBACK TO inv_ebi_sync_item_save_pnt;
2731 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2732 IF(x_out.output_status.msg_data IS NULL) THEN
2733 FND_MSG_PUB.count_and_get(
2734 p_encoded => FND_API.g_false
2735 ,p_count => x_out.output_status.msg_count
2736 ,p_data => x_out.output_status.msg_data
2737 );
2738 END IF;
2739 WHEN OTHERS THEN
2740 ROLLBACK TO inv_ebi_sync_item_save_pnt;
2741 x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2742 IF (x_out.output_status.msg_data IS NOT NULL) THEN
2743 x_out.output_status.msg_data := l_out.output_status.msg_data ||' -> INV_EBI_ITEM_HELPER.sync_item ';
2744 ELSE
2745 x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.sync_item ';
2746 END IF;
2747 END sync_item;
2748 /************************************************************************************
2749 -- API name : get_item_balance
2750 -- Type : Public
2751 -- Function :
2752 ************************************************************************************/
2753 PROCEDURE get_item_balance(
2754 p_item_balance_input IN inv_ebi_item_bal_input_list
2755 ,x_item_balance_output OUT NOCOPY inv_ebi_item_bal_output_list
2756 ,x_return_status OUT NOCOPY VARCHAR2
2757 ,x_msg_count OUT NOCOPY NUMBER
2758 ,x_msg_data OUT NOCOPY VARCHAR2
2759 )
2760 IS
2761 l_locator_id NUMBER;
2762 l_cur_index NUMBER;
2763 l_item_bal_output inv_ebi_item_balance_obj;
2764 l_item_balance_loc_tbl inv_ebi_item_bal_loc_tbl;
2765 l_item_balance_output_tbl inv_ebi_item_bal_output_tbl;
2766 l_item_balance_loc_obj inv_ebi_item_bal_loc_obj;
2767 l_item_balance_output_obj inv_ebi_item_bal_output_obj;
2768 l_is_revision_control BOOLEAN;
2769 l_is_lot_control BOOLEAN;
2770 l_is_serial_control BOOLEAN;
2771 l_return_status VARCHAR2(1);
2772 l_msg_count NUMBER;
2773 l_msg_data VARCHAR2(1000);
2774 l_qty_on_hand NUMBER;
2775 l_res_qty_on_hand NUMBER;
2776 l_qty_reserved NUMBER;
2777 l_qty_suggested NUMBER;
2778 l_avail_to_transact NUMBER;
2779 l_avail_to_reserve NUMBER;
2780 l_sec_qty_on_hand NUMBER;
2781 l_sec_res_qty_on_hand NUMBER;
2782 l_sec_qty_reserved NUMBER;
2783 l_sec_qty_suggested NUMBER;
2784 l_sec_avail_to_transact NUMBER;
2785 l_sec_avail_to_reserve NUMBER;
2786 l_organization_code VARCHAR2(3000);
2787 l_item_name VARCHAR2(30);
2788 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
2789 CURSOR c_locator_id(p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER,
2790 p_revision IN VARCHAR2, p_subinventory_code IN VARCHAR2
2791 )IS
2792 SELECT
2793 DISTINCT(locator_id)
2794 FROM
2795 mtl_onhand_locator_v
2796 WHERE
2797 locator_id IS NOT NULL AND
2798 inventory_item_id = p_inventory_item_id AND
2799 organization_id = p_organization_id AND
2800 (revision IS NOT NULL OR (revision = p_revision)) AND
2801 (subinventory_code IS NOT NULL OR (subinventory_code = p_subinventory_code));
2802 BEGIN
2803 x_return_status := fnd_api.g_ret_sts_success;
2804 l_item_balance_output_tbl := inv_ebi_item_bal_output_tbl();
2805 Inv_Quantity_Tree_Pub.clear_quantity_cache;
2806 IF p_item_balance_input.item_input_table IS NOT NULL THEN
2807 FOR i IN p_item_balance_input.item_input_table.FIRST..p_item_balance_input.item_input_table.LAST LOOP
2808 BEGIN
2809 l_item_balance_output_tbl.EXTEND();
2810 IF p_item_balance_input.item_input_table(i).is_revision_control = FND_API.G_TRUE THEN
2811 l_is_revision_control := TRUE;
2812 ELSE
2813 l_is_revision_control := FALSE;
2814 END IF;
2815 IF p_item_balance_input.item_input_table(i).is_lot_control = FND_API.G_TRUE THEN
2816 l_is_lot_control := TRUE;
2817 ELSE
2818 l_is_lot_control := FALSE;
2819 END IF;
2820 IF p_item_balance_input.item_input_table(i).is_serial_control = FND_API.G_TRUE THEN
2821 l_is_serial_control := TRUE;
2822 ELSE
2823 l_is_serial_control := FALSE;
2824 END IF;
2825 INV_QUANTITY_TREE_PUB.query_quantities(
2826 p_api_version_number => p_item_balance_input.item_input_table(i).api_version_number
2827 ,p_init_msg_lst => p_item_balance_input.item_input_table(i).init_msg_lst
2828 ,p_organization_id => p_item_balance_input.item_input_table(i).organization_id
2829 ,p_inventory_item_id => p_item_balance_input.item_input_table(i).inventory_item_id
2830 ,p_tree_mode => p_item_balance_input.item_input_table(i).tree_mode
2831 ,p_is_revision_control => l_is_revision_control
2832 ,p_is_lot_control => l_is_lot_control
2833 ,p_is_serial_control => l_is_serial_control
2834 ,p_grade_code => p_item_balance_input.item_input_table(i).grade_code
2835 ,p_demand_source_type_id => p_item_balance_input.item_input_table(i).demand_source_type_id
2836 ,p_demand_source_header_id => p_item_balance_input.item_input_table(i).demand_source_header_id
2837 ,p_demand_source_line_id => p_item_balance_input.item_input_table(i).demand_source_line_id
2838 ,p_demand_source_name => p_item_balance_input.item_input_table(i).demand_source_name
2839 ,p_lot_expiration_date => p_item_balance_input.item_input_table(i).lot_expiration_date
2840 ,p_revision => p_item_balance_input.item_input_table(i).revision
2841 ,p_lot_number => p_item_balance_input.item_input_table(i).lot_number
2842 ,p_subinventory_code => p_item_balance_input.item_input_table(i).subinventory_code
2843 ,p_locator_id => p_item_balance_input.item_input_table(i).locator_id
2844 ,p_onhand_source => p_item_balance_input.item_input_table(i).onhand_source
2845 ,p_transfer_subinventory_code => p_item_balance_input.item_input_table(i).transfer_subinventory_code
2846 ,p_cost_group_id => p_item_balance_input.item_input_table(i).cost_group_id
2847 ,p_lpn_id => p_item_balance_input.item_input_table(i).lpn_id
2848 ,p_transfer_locator_id => p_item_balance_input.item_input_table(i).transfer_locator_id
2849 ,x_qoh => l_qty_on_hand
2850 ,x_rqoh => l_res_qty_on_hand
2851 ,x_qr => l_qty_reserved
2852 ,x_qs => l_qty_suggested
2853 ,x_att => l_avail_to_transact
2854 ,x_atr => l_avail_to_reserve
2855 ,x_sqoh => l_sec_qty_on_hand
2856 ,x_srqoh => l_sec_res_qty_on_hand
2857 ,x_sqr => l_sec_qty_reserved
2858 ,x_sqs => l_sec_qty_suggested
2859 ,x_satt => l_sec_avail_to_transact
2860 ,x_satr => l_sec_avail_to_reserve
2861 ,x_return_status => l_return_status
2862 ,x_msg_count => l_msg_count
2863 ,x_msg_data => l_msg_data
2864 );
2865 l_item_bal_output := inv_ebi_item_balance_obj(
2866 l_return_status
2867 ,l_msg_count
2868 ,l_msg_data
2869 ,l_qty_on_hand
2870 ,l_res_qty_on_hand
2871 ,l_qty_reserved
2872 ,l_qty_suggested
2873 ,l_avail_to_transact
2874 ,l_avail_to_reserve
2875 ,l_sec_qty_on_hand
2876 ,l_sec_res_qty_on_hand
2877 ,l_sec_qty_reserved
2878 ,l_sec_qty_suggested
2879 ,l_sec_avail_to_transact
2880 ,l_sec_avail_to_reserve
2881 );
2882 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
2883 l_pk_col_name_val_pairs.EXTEND(1);
2884 l_pk_col_name_val_pairs(1).name := 'organization_id';
2885 l_pk_col_name_val_pairs(1).value := p_item_balance_input.item_input_table(i).organization_id;
2886 l_organization_code := INV_EBI_ITEM_HELPER.id_to_value (
2887 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
2888 ,p_entity_name=> G_ORGANIZATION
2889 );
2890 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
2891 l_pk_col_name_val_pairs.EXTEND(1);
2892 l_pk_col_name_val_pairs(1).name := 'organization_id';
2893 l_pk_col_name_val_pairs(1).value := p_item_balance_input.item_input_table(i).organization_id;
2894 l_pk_col_name_val_pairs.EXTEND(1);
2895 l_pk_col_name_val_pairs(2).name := 'inventory_item_id';
2896 l_pk_col_name_val_pairs(2).value := p_item_balance_input.item_input_table(i).inventory_item_id;
2897 l_item_name := INV_EBI_ITEM_HELPER.id_to_value(
2898 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
2899 ,p_entity_name=> G_INVENTORY_ITEM
2900 );
2901 l_item_balance_output_obj := inv_ebi_item_bal_output_obj(
2902 p_item_balance_input.item_input_table(i).organization_id
2903 ,p_item_balance_input.item_input_table(i).inventory_item_id
2904 ,l_organization_code
2905 ,l_item_name
2906 ,p_item_balance_input.item_input_table(i).demand_source_type_id
2907 ,p_item_balance_input.item_input_table(i).demand_source_header_id
2908 ,p_item_balance_input.item_input_table(i).demand_source_line_id
2909 ,p_item_balance_input.item_input_table(i).revision
2910 ,p_item_balance_input.item_input_table(i).lot_number
2911 ,p_item_balance_input.item_input_table(i).subinventory_code
2912 ,p_item_balance_input.item_input_table(i).locator_id
2913 ,p_item_balance_input.item_input_table(i).onhand_source
2914 ,p_item_balance_input.item_input_table(i).transfer_subinventory_code
2915 ,p_item_balance_input.item_input_table(i).cost_group_id
2916 ,p_item_balance_input.item_input_table(i).lpn_id
2917 ,p_item_balance_input.item_input_table(i).transfer_locator_id
2918 ,l_item_bal_output
2919 ,NULL
2920 ,NULL
2921 ,NULL);
2922 l_item_balance_output_tbl(i) := l_item_balance_output_obj;
2923 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2924 RAISE FND_API.g_exc_unexpected_error;
2925 END IF;
2926 --If Locator Information is required then get the item balance based on location
2927 IF p_item_balance_input.item_input_table(i).is_loc_info_required = fnd_api.g_true THEN
2928 l_item_balance_loc_tbl :=inv_ebi_item_bal_loc_tbl();
2929 l_cur_index :=1;
2930 FOR cer IN c_locator_id(p_item_balance_input.item_input_table(i).inventory_item_id
2931 ,p_item_balance_input.item_input_table(i).organization_id
2932 ,p_item_balance_input.item_input_table(i).revision
2933 ,p_item_balance_input.item_input_table(i).subinventory_code)
2934 LOOP
2935 l_locator_id := cer.locator_id;
2936 l_item_balance_loc_tbl.extend();
2937 INV_Quantity_Tree_PUB.Query_Quantities(
2938 p_api_version_number => p_item_balance_input.item_input_table(i).api_version_number
2939 ,p_init_msg_lst => p_item_balance_input.item_input_table(i).init_msg_lst
2940 ,p_organization_id => p_item_balance_input.item_input_table(i).organization_id
2941 ,p_inventory_item_id => p_item_balance_input.item_input_table(i).inventory_item_id
2942 ,p_tree_mode => p_item_balance_input.item_input_table(i).tree_mode
2943 ,p_is_revision_control => l_is_revision_control
2944 ,p_is_lot_control => l_is_lot_control
2945 ,p_is_serial_control => l_is_serial_control
2946 ,p_grade_code => p_item_balance_input.item_input_table(i).grade_code
2947 ,p_demand_source_type_id => p_item_balance_input.item_input_table(i).demand_source_type_id
2948 ,p_demand_source_header_id => p_item_balance_input.item_input_table(i).demand_source_header_id
2949 ,p_demand_source_line_id => p_item_balance_input.item_input_table(i).demand_source_line_id
2950 ,p_demand_source_name => p_item_balance_input.item_input_table(i).demand_source_name
2951 ,p_lot_expiration_date => p_item_balance_input.item_input_table(i).lot_expiration_date
2952 ,p_revision => p_item_balance_input.item_input_table(i).revision
2953 ,p_lot_number => p_item_balance_input.item_input_table(i).lot_number
2954 ,p_subinventory_code => p_item_balance_input.item_input_table(i).subinventory_code
2955 ,p_locator_id => l_locator_id
2956 ,p_onhand_source => p_item_balance_input.item_input_table(i).onhand_source
2957 ,p_transfer_subinventory_code => p_item_balance_input.item_input_table(i).transfer_subinventory_code
2958 ,p_cost_group_id => p_item_balance_input.item_input_table(i).cost_group_id
2959 ,p_lpn_id => p_item_balance_input.item_input_table(i).lpn_id
2960 ,p_transfer_locator_id => p_item_balance_input.item_input_table(i).transfer_locator_id
2961 ,x_qoh => l_qty_on_hand
2962 ,x_rqoh => l_res_qty_on_hand
2963 ,x_qr => l_qty_reserved
2964 ,x_qs => l_qty_suggested
2965 ,x_att => l_avail_to_transact
2966 ,x_atr => l_avail_to_reserve
2967 ,x_sqoh => l_sec_qty_on_hand
2968 ,x_srqoh => l_sec_res_qty_on_hand
2969 ,x_sqr => l_sec_qty_reserved
2970 ,x_sqs => l_sec_qty_suggested
2971 ,x_satt => l_sec_avail_to_transact
2972 ,x_satr => l_sec_avail_to_reserve
2973 ,x_return_status => l_return_status
2974 ,x_msg_count => l_msg_count
2975 ,x_msg_data => l_msg_data
2976 );
2977 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2978 RAISE FND_API.g_exc_unexpected_error;
2979 END IF;
2980 l_item_bal_output := inv_ebi_item_balance_obj(
2981 l_return_status
2982 ,l_msg_count
2983 ,l_msg_data
2984 ,l_qty_on_hand
2985 ,l_res_qty_on_hand
2986 ,l_qty_reserved
2987 ,l_qty_suggested
2988 ,l_avail_to_transact
2989 ,l_avail_to_reserve
2990 ,l_sec_qty_on_hand
2991 ,l_sec_res_qty_on_hand
2992 ,l_sec_qty_reserved
2993 ,l_sec_qty_suggested
2994 ,l_sec_avail_to_transact
2995 ,l_sec_avail_to_reserve
2996 );
2997 l_item_balance_loc_obj := inv_ebi_item_bal_loc_obj(l_locator_id,l_item_bal_output);
2998 l_item_balance_loc_tbl(l_cur_index) := l_item_balance_loc_obj;
2999 l_cur_index := l_cur_index + 1;
3000 END LOOP;
3001 END IF; -- IF p_item_balance_input.item_input_table(i).is_loc_info_required is true
3002 l_item_balance_output_tbl(i).item_balance_loc_tbl := l_item_balance_loc_tbl;
3003 get_Operating_unit
3004 (p_oranization_id => p_item_balance_input.item_input_table(i).organization_id
3005 ,x_operating_unit => l_item_balance_output_tbl(i).operating_unit
3006 ,x_ouid => l_item_balance_output_tbl(i).operating_unit_id
3007 );
3008 EXCEPTION
3009 WHEN FND_API.g_exc_unexpected_error THEN
3010 x_return_status := FND_API.g_ret_sts_error;
3011 IF l_msg_data IS NULL THEN
3012 FND_MSG_PUB.count_and_get(
3013 p_encoded => FND_API.g_false
3014 ,p_count => l_item_balance_output_tbl(i).item_balance_output.msg_count
3015 ,p_data => l_item_balance_output_tbl(i).item_balance_output.msg_data
3016 );
3017 END IF;
3018 populate_err_msg (p_orgid => p_item_balance_input.item_input_table(i).organization_id
3019 ,p_invid => p_item_balance_input.item_input_table(i).inventory_item_id
3020 ,p_org_code => l_organization_code
3021 ,p_item_name => l_item_name
3022 ,p_part_err_msg => l_item_balance_output_tbl(i).item_balance_output.msg_data
3023 ,x_err_msg => x_msg_data
3024 );
3025 WHEN OTHERS THEN
3026 x_return_status := FND_API.g_ret_sts_error;
3027 populate_err_msg(p_orgid => p_item_balance_input.item_input_table(i).organization_id
3028 ,p_invid => p_item_balance_input.item_input_table(i).inventory_item_id
3029 ,p_org_code => l_organization_code
3030 ,p_item_name => l_item_name
3031 ,p_part_err_msg => SQLERRM||'-> at inv_ebi_item_helper.get_item_balance'
3032 ,x_err_msg => x_msg_data
3033 );
3034 END;
3035
3036 END LOOP;
3037 x_item_balance_output :=inv_ebi_item_bal_output_list(l_item_balance_output_tbl);
3038 END IF; --p_item_balance_input IS NOT NULL
3039 EXCEPTION
3040 WHEN OTHERS THEN
3041 x_return_status := FND_API.g_ret_sts_error;
3042 IF (x_msg_data IS NOT NULL) THEN
3043 x_msg_data := x_msg_data ||' -> at INV_EBI_ITEM_HELPER.get_item_balance';
3044 ELSE
3045 x_msg_data := SQLERRM||' at INV_EBI_ITEM_HELPER.get_item_balance ';
3046 END IF;
3047 END get_item_balance;
3048 /************************************************************************************
3049 -- API name : validate_get_item_request
3050 -- Type : Public
3051 -- Function :
3052 -- This API is used to validate the Item Request Inputs
3053 --
3054 ************************************************************************************/
3055 PROCEDURE validate_get_item_request(
3056 p_get_opr_attrs_rec IN inv_ebi_get_operational_attrs,
3057 x_status OUT NOCOPY VARCHAR2,
3058 x_msg_count OUT NOCOPY NUMBER,
3059 x_msg_data OUT NOCOPY VARCHAR2
3060 )
3061 IS
3062 BEGIN
3063 FND_MSG_PUB.initialize();
3064 IF((p_get_opr_attrs_rec.item_id IS NULL) AND
3065 (p_get_opr_attrs_rec.item_name IS NULL)) THEN
3066 FND_MESSAGE.set_name('INV','INV_EBI_NO_ITEM_ID_NAME');
3067 FND_MSG_PUB.add;
3068 RAISE FND_API.G_EXC_ERROR;
3069 ELSIF((p_get_opr_attrs_rec.organization_id IS NULL) AND
3070 (p_get_opr_attrs_rec.organization_code IS NULL))THEN
3071 FND_MESSAGE.set_name('INV','INV_EBI_NO_ORGID_ORGCODE');
3072 FND_MSG_PUB.add;
3073 RAISE FND_API.G_EXC_ERROR;
3074 ELSE
3075 x_status := FND_API.G_RET_STS_SUCCESS;
3076 END IF;
3077 EXCEPTION
3078 WHEN FND_API.G_EXC_ERROR THEN
3079 x_status := FND_API.G_RET_STS_ERROR;
3080 FND_MSG_PUB.count_and_get(
3081 p_encoded => FND_API.g_false
3082 ,p_count => x_msg_count
3083 ,p_data => x_msg_data
3084 );
3085 WHEN OTHERS THEN
3086 x_status := FND_API.G_RET_STS_ERROR;
3087 IF (x_msg_data IS NOT NULL) THEN
3088 x_msg_data := x_msg_data||' -> INV_EBI_ITEM_HELPER.validate_get_item_request ';
3089 ELSE
3090 x_msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.validate_get_item_request ';
3091 END IF;
3092 END validate_get_item_request;
3093 /************************************************************************************
3094 -- API name : get_uda_attributes
3095 -- Type : Public
3096 -- Function :
3097 -- Bug 7240247
3098 ************************************************************************************/
3099
3100 PROCEDURE get_uda_attributes(
3101 p_classification_id IN NUMBER,
3102 p_attr_group_type IN VARCHAR2,
3103 p_application_id IN NUMBER,
3104 p_attr_grp_id_tbl IN FND_TABLE_OF_NUMBER,
3105 p_data_level IN VARCHAR2,
3106 p_revision_id IN NUMBER,
3107 p_object_name IN VARCHAR2,
3108 p_pk_data IN EGO_COL_NAME_VALUE_PAIR_ARRAY,
3109 x_uda_obj OUT NOCOPY inv_ebi_uda_input_obj,
3110 x_uda_output_obj OUT NOCOPY inv_ebi_eco_output_obj
3111 ) IS
3112 l_attr_grp_req_tbl ego_attr_group_request_table;
3113 l_attr_grp_req_obj ego_attr_group_request_obj;
3114 l_attr_grp ego_user_attr_row_table;
3115 l_attr ego_user_attr_data_table;
3116 l_count NUMBER := 0;
3117 l_new_attr_in_tbl VARCHAR2(1) := fnd_api.g_false;
3118 l_uda_out inv_ebi_uda_output_obj;
3119 l_output_status inv_ebi_output_status;
3120 l_attr_group_name VARCHAR2(30);
3121 l_data_level VARCHAR2(20) := NULL;
3122 l_data_level1 VARCHAR2(20) := NULL;
3123
3124 CURSOR c_attr_cursor(
3125 p_classification_id IN NUMBER,
3126 p_attr_group_type IN VARCHAR2,
3127 p_application_id IN NUMBER,
3128 p_attr_group_name IN VARCHAR2,
3129 p_data_level_int_name IN VARCHAR2
3130 ) IS
3131 SELECT
3132 att.attr_name,
3133 ass.attr_group_id,
3134 ass.data_level_int_name
3135 FROM
3136 ego_obj_attr_grp_assocs_v ass,
3137 ego_attrs_v att
3138 WHERE ass.classification_code = TO_CHAR(p_classification_id)
3139 AND ass.attr_group_type = att.attr_group_type
3140 AND att.attr_group_type = p_attr_group_type
3141 AND ass.application_id = att.application_id
3142 AND att.application_id = p_application_id
3143 AND ass.attr_group_name = att.attr_group_name
3144 AND att.attr_group_name = p_attr_group_name
3145 AND ass.data_level_int_name = p_data_level_int_name;
3146
3147 BEGIN
3148 l_uda_out := inv_ebi_uda_output_obj(NULL,NULL);
3149 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
3150 x_uda_output_obj := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,l_uda_out);
3151
3152 l_attr_grp_req_tbl := EGO_ATTR_GROUP_REQUEST_TABLE();
3153
3154 l_count := 1;
3155
3156
3157 FOR i IN 1..p_attr_grp_id_tbl.COUNT LOOP
3158
3159 IF(p_attr_group_type = INV_EBI_UTIL.G_EGO_ITEMMGMT_GROUP) THEN
3160
3161 SELECT data_level_int_name INTO l_data_level
3162 FROM ego_obj_attr_grp_assocs_v
3163 WHERE attr_group_id = p_attr_grp_id_tbl(i)
3164 AND classification_code = TO_CHAR(p_classification_id);
3165
3166 ELSE
3167
3168 l_data_level := p_data_level;
3169
3170 END IF;
3171
3172 SELECT attr_group_name INTO l_attr_group_name
3173 FROM ego_obj_attr_grp_assocs_v
3174 WHERE
3175 attr_group_id = p_attr_grp_id_tbl(i) AND
3176 classification_code = TO_CHAR(p_classification_id) AND
3177 data_level_int_name = l_data_level;
3178
3179
3180 FOR attr_cursor IN c_attr_cursor(
3181 p_classification_id => p_classification_id ,
3182 p_attr_group_type => p_attr_group_type,
3183 p_application_id => p_application_id,
3184 p_attr_group_name => l_attr_group_name,
3185 p_data_level_int_name => l_data_level
3186 )
3187 LOOP
3188 l_new_attr_in_tbl := FND_API.G_FALSE;
3189
3190 IF(l_count <>1 ) THEN
3191 FOR ctr1 IN l_attr_grp_req_tbl.FIRST..l_attr_grp_req_tbl.LAST LOOP
3192 IF (l_attr_grp_req_tbl(ctr1).attr_group_id = attr_cursor.attr_group_id AND
3193 l_attr_grp_req_tbl(ctr1).data_level = attr_cursor.data_level_int_name ) THEN
3194 l_attr_grp_req_tbl(ctr1).ATTR_NAME_LIST := l_attr_grp_req_tbl(ctr1).ATTR_NAME_LIST || ',' || attr_cursor.attr_name;
3195 l_new_attr_in_tbl := FND_API.G_TRUE;
3196 END IF;
3197 END LOOP;
3198 END IF;
3199
3200 IF l_new_attr_in_tbl <> FND_API.G_TRUE THEN
3201
3202 l_attr_grp_req_tbl.extend();
3203
3204 IF(attr_cursor.data_level_int_name = INV_EBI_ITEM_PUB.g_data_level_item_rev) THEN
3205 l_data_level1 := p_revision_id;
3206 ELSE
3207 l_data_level1 := NULL;
3208 END IF;
3209
3210
3211 l_attr_grp_req_tbl(l_count) := EGO_ATTR_GROUP_REQUEST_OBJ(attr_cursor.attr_group_id
3212 ,NULL
3213 ,NULL
3214 ,NULL
3215 ,l_data_level
3216 ,l_data_level1
3217 ,NULL
3218 ,NULL
3219 ,NULL
3220 ,NULL
3221 ,attr_cursor.attr_name);
3222
3223 l_count := l_count + 1;
3224 END IF;
3225 END LOOP;
3226 END LOOP;
3227 EGO_USER_ATTRS_DATA_PUB.Get_User_Attrs_Data(
3228 p_api_version => 1.0
3229 ,p_object_name => p_object_name
3230 ,p_pk_column_name_value_pairs => p_pk_data
3231 ,p_attr_group_request_table => l_attr_grp_req_tbl
3232 ,x_attributes_row_table => l_attr_grp
3233 ,x_attributes_data_table => l_attr
3234 ,x_return_status => x_uda_output_obj.output_status.return_status
3235 ,x_errorcode => x_uda_output_obj.uda_output.errorcode
3236 ,x_msg_count => x_uda_output_obj.output_status.msg_count
3237 ,x_msg_data => x_uda_output_obj.output_status.msg_data
3238 );
3239
3240 IF(x_uda_output_obj.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3241 RAISE FND_API.g_exc_unexpected_error;
3242 END IF;
3243
3244 INV_EBI_UTIL.transform_attr_rowdata_uda(
3245 p_attributes_row_table => l_attr_grp
3246 ,p_attributes_data_table => l_attr
3247 ,x_uda_input_obj => x_uda_obj
3248 ,x_return_status => x_uda_output_obj.output_status.return_status
3249 ,x_msg_count => x_uda_output_obj.output_status.msg_count
3250 ,x_msg_data => x_uda_output_obj.output_status.msg_data
3251
3252 );
3253
3254 IF(x_uda_output_obj.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3255 RAISE FND_API.g_exc_unexpected_error;
3256 END IF;
3257
3258
3259 EXCEPTION
3260 WHEN FND_API.g_exc_unexpected_error THEN
3261
3262 x_uda_output_obj.output_status.return_status := FND_API.g_ret_sts_unexp_error;
3263
3264 IF(x_uda_output_obj.output_status.msg_data IS NULL) THEN
3265 fnd_msg_pub.count_and_get(
3266 p_encoded => FND_API.g_false
3267 ,p_count => x_uda_output_obj.output_status.msg_count
3268 ,p_data => x_uda_output_obj.output_status.msg_data
3269 );
3270 END IF;
3271
3272 WHEN OTHERS THEN
3273
3274 x_uda_output_obj.output_status.return_status := FND_API.g_ret_sts_unexp_error;
3275 IF (x_uda_output_obj.output_status.msg_data IS NOT NULL) THEN
3276 x_uda_output_obj.output_status.msg_data := x_uda_output_obj.output_status.msg_data||' ->INV_EBI_CHANGE_ORDER_HELPER.get_uda_attributes ';
3277 ELSE
3278 x_uda_output_obj.output_status.msg_data := SQLERRM||'INV_EBI_CHANGE_ORDER_HELPER.get_uda_attributes ';
3279 END IF;
3280 END get_uda_attributes;
3281
3282
3283 /************************************************************************************
3284 -- API name : get_item_uda
3285 -- Type : Private
3286 -- Function :
3287 -- This API is used to retrieve the uda's of the requested Items.
3288 -- Bug 7240247
3289 ************************************************************************************/
3290
3291 PROCEDURE get_item_uda(
3292 p_inventory_item_id IN NUMBER,
3293 p_organization_id IN NUMBER,
3294 p_item_classification_id IN NUMBER,
3295 p_revision_id IN NUMBER,
3296 x_item_uda OUT NOCOPY inv_ebi_uda_input_obj,
3297 x_return_status OUT NOCOPY VARCHAR2,
3298 x_msg_count OUT NOCOPY NUMBER,
3299 x_msg_data OUT NOCOPY VARCHAR2
3300 ) IS
3301
3302 l_count NUMBER :=0;
3303 l_item_uda_count NUMBER :=0;
3304 l_attr_group_count NUMBER :=0;
3305 l_application_id NUMBER;
3306 l_attr_group_id_tbl FND_TABLE_OF_NUMBER;
3307 l_pkdata EGO_COL_NAME_VALUE_PAIR_ARRAY;
3308 l_uda_output_obj inv_ebi_eco_output_obj;
3309 l_output_status inv_ebi_output_status;
3310
3311 CURSOR c_attr_group_id IS
3312 SELECT DISTINCT ems.attr_group_id
3313 FROM
3314 ego_mtl_sy_items_ext_vl ems,
3315 ego_obj_attr_grp_assocs_v ass
3316 WHERE
3317 ems.inventory_item_id = p_inventory_item_id AND
3318 ems.organization_id = p_organization_id AND
3319 ems.item_catalog_group_id = p_item_classification_id AND
3320 ems.attr_group_id = ass.attr_group_id;
3321
3322 BEGIN
3323
3324 l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
3325 l_uda_output_obj := inv_ebi_eco_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL);
3326
3327
3328 IF( p_inventory_item_id IS NOT NULL AND
3329 p_organization_id IS NOT NULL AND
3330 p_item_classification_id IS NOT NULL) THEN
3331
3332 SELECT COUNT(1) INTO l_count
3333 FROM mtl_system_items_b
3334 WHERE
3335 inventory_item_id = p_inventory_item_id AND
3336 organization_id = p_organization_id AND
3337 item_catalog_group_id = p_item_classification_id;
3338
3339 END IF;
3340
3341 IF(l_count > 0) THEN
3342
3343 IF c_attr_group_id%ISOPEN THEN
3344 CLOSE c_attr_group_id;
3345 END IF;
3346
3347 OPEN c_attr_group_id ;
3348 FETCH c_attr_group_id BULK COLLECT INTO l_attr_group_id_tbl ;
3349 CLOSE c_attr_group_id;
3350
3351
3352 IF(l_attr_group_id_tbl IS NOT NULL AND l_attr_group_id_tbl.COUNT > 0) THEN
3353
3354 l_pkdata := EGO_COL_NAME_VALUE_PAIR_ARRAY();
3355 l_pkdata.extend(2);
3356 l_pkdata(1) := EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID',p_inventory_item_id);
3357 l_pkdata(2) := EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID',p_organization_id);
3358
3359 l_application_id:= INV_EBI_UTIL.get_application_id(
3360 p_application_short_name => 'EGO'
3361 );
3362
3363 IF(l_application_id IS NULL ) THEN
3364
3365 FND_MESSAGE.set_name('INV','INV_EBI_APP_INVALID');
3366 FND_MESSAGE.set_token('COL_VALUE', 'EGO');
3367 FND_MSG_PUB.add;
3368 RAISE FND_API.g_exc_error;
3369 END IF;
3370
3371 get_uda_attributes(
3372 p_classification_id => p_item_classification_id,
3373 p_attr_group_type => INV_EBI_UTIL.G_EGO_ITEMMGMT_GROUP,
3374 p_application_id => l_application_id,
3375 p_attr_grp_id_tbl => l_attr_group_id_tbl,
3376 p_data_level => NULL,
3377 p_revision_id => p_revision_id,
3378 p_object_name => INV_EBI_UTIL.G_EGO_ITEM,
3379 p_pk_data => l_pkdata,
3380 x_uda_obj => x_item_uda,
3381 x_uda_output_obj => l_uda_output_obj
3382 );
3383
3384 IF(l_uda_output_obj.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3385 x_msg_data := l_uda_output_obj.output_status.msg_data ;
3386 RAISE FND_API.g_exc_unexpected_error;
3387 END IF;
3388 END IF;
3389 END IF;
3390
3391 EXCEPTION
3392 WHEN FND_API.g_exc_unexpected_error THEN
3393
3394 IF c_attr_group_id%ISOPEN THEN
3395 CLOSE c_attr_group_id;
3396 END IF;
3397
3398 x_return_status := FND_API.g_ret_sts_unexp_error;
3399 IF(x_msg_data IS NULL) THEN
3400 fnd_msg_pub.count_and_get(
3401 p_encoded => FND_API.g_false
3402 ,p_count => x_msg_count
3403 ,p_data => x_msg_data
3404 );
3405 END IF;
3406 WHEN OTHERS THEN
3407
3408 IF c_attr_group_id%ISOPEN THEN
3409 CLOSE c_attr_group_id;
3410 END IF;
3411
3412 x_return_status := FND_API.g_ret_sts_unexp_error;
3413 IF (x_msg_data IS NOT NULL) THEN
3414 x_msg_data := x_msg_data||' ->INV_EBI_ITEM_HELPER.get_item_uda ';
3415 ELSE
3416 x_msg_data := SQLERRM||'INV_EBI_ITEM_HELPER.get_item_uda';
3417 END IF;
3418 END get_item_uda;
3419 /************************************************************************************
3420 -- API name : get_item_attributes
3421 -- Type : Public
3422 -- Function :
3423 -- This API is used to retrieve the operational attributes for the requested Items.
3424 --
3425 ************************************************************************************/
3426 PROCEDURE get_item_attributes(
3427 p_get_item_inp_obj IN inv_ebi_get_item_input,
3428 x_item_tbl_obj OUT NOCOPY inv_ebi_item_attr_tbl_obj,
3429 x_return_status OUT NOCOPY VARCHAR2,
3430 x_msg_count OUT NOCOPY NUMBER,
3431 x_msg_data OUT NOCOPY VARCHAR2
3432 )
3433 IS
3434 l_item_phy_obj inv_ebi_item_physical_obj;
3435 l_item_inv_obj inv_ebi_item_inventory_obj;
3436 l_item_pur_obj inv_ebi_item_purchasing_obj;
3437 l_item_recving_obj inv_ebi_item_receiving_obj;
3438 l_item_gplan_obj inv_ebi_item_gplanning_obj;
3439 l_item_mrp_obj inv_ebi_item_mrp_obj;
3440 l_item_order_obj inv_ebi_item_order_obj;
3441 l_item_service_obj inv_ebi_item_service_obj;
3442 l_item_bom_obj inv_ebi_item_bom_obj;
3443 l_item_costing_obj inv_ebi_item_costing_obj;
3444 l_item_lead_time_obj inv_ebi_item_lead_time_obj;
3445 l_item_wip_obj inv_ebi_item_wip_obj;
3446 l_item_invoice_obj inv_ebi_item_invoice_obj;
3447 l_item_web_opiton inv_ebi_item_web_option_obj;
3448 l_item_asset_obj inv_ebi_item_asset_obj;
3449 l_item_process_obj inv_ebi_item_processmfg_obj;
3450 l_item_mfr_part_obj inv_ebi_manufacturer_part_obj;
3451 l_item_core_obj inv_ebi_item_main_obj;
3452 l_item_custom_obj inv_ebi_item_custom_obj;
3453 l_uda_obj inv_ebi_uda_input_obj;
3454 l_item_obj inv_ebi_item_obj;
3455 l_getassetmgmtattrs VARCHAR2(1) := FND_API.G_FALSE;
3456 l_getbomattrs VARCHAR2(1) := FND_API.G_FALSE;
3457 l_getcostingattrs VARCHAR2(1) := FND_API.G_FALSE;
3458 l_getgeneralplanningattrs VARCHAR2(1) := FND_API.G_FALSE;
3459 l_getinventoryattrs VARCHAR2(1) := FND_API.G_FALSE;
3460 l_getinvoicingattrs VARCHAR2(1) := FND_API.G_FALSE;
3461 l_getleadtimeattrs VARCHAR2(1) := FND_API.G_FALSE;
3462 l_getmpsmrpplanningattrs VARCHAR2(1) := FND_API.G_FALSE;
3463 l_getorderattrs VARCHAR2(1) := FND_API.G_FALSE;
3464 l_getphysicalattrs VARCHAR2(1) := FND_API.G_FALSE;
3465 l_getprocessattrs VARCHAR2(1) := FND_API.G_FALSE;
3466 l_getpurchasingattrs VARCHAR2(1) := FND_API.G_FALSE;
3467 l_getrecevingattrs VARCHAR2(1) := FND_API.G_FALSE;
3468 l_getserviceattrs VARCHAR2(1) := FND_API.G_FALSE;
3469 l_getweboptionattrs VARCHAR2(1) := FND_API.G_FALSE;
3470 l_getwipattrs VARCHAR2(1) := FND_API.G_FALSE;
3471 l_getitemoprattrs VARCHAR2(1) := FND_API.G_FALSE;
3472 l_org_id NUMBER;
3473 l_item_id NUMBER;
3474 l_msg_data VARCHAR2(100);
3475 l_msg_count NUMBER;
3476 l_item_attr_tbl inv_ebi_item_attr_tbl;
3477 l_mfr_part_table inv_ebi_mfg_part_obj_tbl_type;
3478 l_return_status VARCHAR2(1);
3479 l_msg VARCHAR2(100);
3480 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
3481 ctr NUMBER;
3482 l_error_code NUMBER;
3483 l_default_cost_group_id VARCHAR2(2000) :=NULL;
3484 l_default_cost_type_id VARCHAR2(2000) :=NULL;
3485
3486 CURSOR c_mfr_part_cursor(p_item_id IN NUMBER,p_org_id IN NUMBER) IS
3487 SELECT
3488 manufacturer_id,mfg_part_num
3489 FROM mtl_mfg_part_numbers
3490 WHERE inventory_item_id = p_item_id AND organization_id=p_org_id;
3491
3492 BEGIN
3493 l_item_attr_tbl := inv_ebi_item_attr_tbl();
3494 x_return_status := FND_API.G_RET_STS_SUCCESS;
3495 IF p_get_item_inp_obj.p_config_flags IS NULL THEN
3496 l_getitemoprattrs := FND_API.G_TRUE;
3497 ELSE
3498 FOR i IN p_get_item_inp_obj.p_config_flags.FIRST..p_get_item_inp_obj.p_config_flags.LAST
3499 LOOP
3500 CASE p_get_item_inp_obj.p_config_flags(i).param_name
3501 WHEN G_ASSET_MGMT_ATTRS THEN
3502 l_getassetmgmtattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3503 WHEN G_BOM_ATTRS THEN
3504 l_getbomattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3505 WHEN G_COSTING_ATTRS THEN
3506 l_getcostingattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3507 WHEN G_GPLAN_ATTRS THEN
3508 l_getgeneralplanningattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3509 WHEN G_INVENTORY_ATTRS THEN
3510 l_getinventoryattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3511 WHEN G_INVOICE_ATTRS THEN
3512 l_getinvoicingattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3513 WHEN G_LEAD_TIME_ATTRS THEN
3514 l_getleadtimeattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3515 WHEN G_MPSMRP_ATTRS THEN
3516 l_getmpsmrpplanningattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3517 WHEN G_ORDER_ATTRS THEN
3518 l_getorderattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3519 WHEN G_PHYSICAL_ATTRS THEN
3520 l_getphysicalattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3521 WHEN G_PROCESS_ATTRS THEN
3522 l_getprocessattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3523 WHEN G_PURCHASING_ATTRS THEN
3524 l_getpurchasingattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3525 WHEN G_RECEVING_ATTRS THEN
3526 l_getrecevingattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3527 WHEN G_SERVICE_ATTRS THEN
3528 l_getserviceattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3529 WHEN G_WEB_OPTION_ATTRS THEN
3530 l_getweboptionattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3531 WHEN G_WIP_ATTRS THEN
3532 l_getwipattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3533 WHEN G_ITEM_ATTRS THEN
3534 l_getitemoprattrs := p_get_item_inp_obj.p_config_flags(i).param_value;
3535 WHEN G_DEFAULT_COST_GROUP_ID THEN
3536 l_default_cost_group_id := p_get_item_inp_obj.p_config_flags(i).param_value;
3537 WHEN G_DEFAULT_COST_TYPE_ID THEN
3538 l_default_cost_type_id := p_get_item_inp_obj.p_config_flags(i).param_value;
3539 END CASE;
3540 END LOOP;
3541 END IF;
3542 FOR i IN p_get_item_inp_obj.p_get_opr_attrs_tbl_type.FIRST..p_get_item_inp_obj.p_get_opr_attrs_tbl_type.LAST
3543 LOOP
3544 BEGIN
3545 l_item_attr_tbl.extend();
3546 l_mfr_part_table := inv_ebi_mfg_part_obj_tbl_type();
3547 l_item_attr_tbl(i) := inv_ebi_get_item_output_obj(NULL,FND_API.G_RET_STS_SUCCESS,NULL,NULL);
3548 l_item_core_obj := inv_ebi_item_main_obj(
3549 NULL,NULL,NULL,NULL,NULL,NULL,
3550 NULL,NULL,NULL,NULL,NULL,NULL,
3551 NULL,NULL,NULL,NULL,NULL,NULL,
3552 NULL,NULL,NULL,NULL,NULL,NULL,
3553 NULL,NULL,NULL,NULL,NULL,NULL,
3554 NULL,NULL,NULL,NULL,NULL,NULL,
3555 NULL,NULL,NULL,NULL,NULL,NULL,
3556 NULL,NULL,NULL,NULL,NULL,NULL,
3557 NULL,NULL,NULL,NULL,NULL,NULL,
3558 NULL,NULL,NULL,NULL,NULL,NULL,
3559 NULL,NULL,NULL,NULL,NULL,NULL,
3560 NULL,NULL,NULL,NULL,NULL,NULL,
3561 NULL,NULL,NULL,NULL,NULL,NULL,
3562 NULL,NULL,NULL,NULL,NULL
3563 );
3564 l_item_custom_obj := inv_ebi_item_custom_obj(NULL,NULL,NULL,NULL,NULL,NULL,
3565 NULL,NULL,NULL,NULL,NULL,NULL,
3566 NULL,NULL,NULL,NULL,NULL,NULL,
3567 NULL,NULL,NULL,NULL,NULL,NULL,
3568 NULL,NULL,NULL,NULL,NULL,NULL,
3569 NULL,NULL,NULL,NULL,NULL,NULL,
3570 NULL,NULL,NULL,NULL,NULL,NULL,
3571 NULL,NULL,NULL,NULL,NULL,NULL,
3572 NULL,NULL,NULL,NULL,NULL,NULL,
3573 NULL,NULL,NULL,NULL);
3574
3575
3576 l_item_phy_obj := inv_ebi_item_physical_obj(
3577 NULL,NULL,NULL,NULL,NULL,
3578 NULL,NULL,NULL,NULL,NULL,
3579 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
3580 );
3581 l_item_inv_obj := inv_ebi_item_inventory_obj(
3582 NULL,NULL,NULL,NULL,NULL,
3583 NULL,NULL,NULL,NULL,NULL,
3584 NULL,NULL,NULL,NULL,NULL,
3585 NULL,NULL,NULL,NULL,NULL,
3586 NULL,NULL,NULL,NULL,NULL,
3587 NULL,NULL,NULL,NULL,NULL,
3588 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
3589 );
3590 l_item_pur_obj := inv_ebi_item_purchasing_obj(
3591 NULL,NULL,NULL,NULL,NULL,
3592 NULL,NULL,NULL,NULL,NULL,NULL,
3593 NULL,NULL,NULL,NULL,NULL,NULL,
3594 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
3595 );
3596 l_item_recving_obj := inv_ebi_item_receiving_obj(
3597 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
3598 );
3599 l_item_gplan_obj := inv_ebi_item_gplanning_obj(
3600 NULL,NULL,NULL,NULL,NULL,
3601 NULL,NULL,NULL,NULL,NULL,NULL,
3602 NULL,NULL,NULL,NULL,NULL,NULL,
3603 NULL,NULL,NULL,NULL,NULL,NULL,
3604 NULL,NULL,NULL,NULL,NULL,NULL
3605 );
3606 l_item_mrp_obj := inv_ebi_item_mrp_obj(
3607 NULL,NULL,NULL,NULL,NULL,NULL,NULL,
3608 NULL,NULL,NULL,NULL,NULL,NULL,NULL,
3609 NULL,NULL,NULL,NULL,NULL,NULL,NULL,
3610 NULL,NULL,NULL,NULL,NULL,NULL,NULL,
3611 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
3612 );
3613 l_item_order_obj := inv_ebi_item_order_obj(
3614 NULL,NULL,NULL,NULL,NULL,NULL,
3615 NULL,NULL,NULL,NULL,NULL,NULL,NULL,
3616 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
3617 );
3618 l_item_service_obj := inv_ebi_item_service_obj(
3619 NULL,NULL,NULL,NULL,NULL,NULL,
3620 NULL,NULL,NULL,NULL,NULL
3621 );
3622 l_item_bom_obj := inv_ebi_item_bom_obj(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
3623 l_item_costing_obj := inv_ebi_item_costing_obj(
3624 NULL,NULL,NULL,NULL,NULL,NULL
3625 );
3626 l_item_lead_time_obj := inv_ebi_item_lead_time_obj(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
3627 l_item_wip_obj := inv_ebi_item_wip_obj(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
3628 l_item_invoice_obj := inv_ebi_item_invoice_obj(NULL,NULL,NULL,NULL,NULL,NULL,NULL);
3629 l_item_web_opiton := inv_ebi_item_web_option_obj(NULL,NULL,NULL,NULL);
3630 l_item_asset_obj := inv_ebi_item_asset_obj(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
3631 l_item_process_obj := inv_ebi_item_processmfg_obj(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
3632 l_uda_obj := inv_ebi_uda_input_obj(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
3633 validate_get_item_request(
3634 p_get_opr_attrs_rec => p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i)
3635 ,x_status => l_return_status
3636 ,x_msg_count => l_item_attr_tbl(i).msg_count
3637 ,x_msg_data => l_item_attr_tbl(i).msg_data
3638 );
3639 l_org_id := p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).organization_id;
3640 l_item_id := p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).item_id;
3641 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3642 RAISE FND_API.G_EXC_ERROR;
3643 END IF;
3644 IF((l_org_id IS NULL) )THEN
3645 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
3646 l_pk_col_name_val_pairs.EXTEND(1);
3647 l_pk_col_name_val_pairs(1).name := 'organization_code';
3648 l_pk_col_name_val_pairs(1).value := p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).organization_code;
3649 l_org_id := INV_EBI_ITEM_HELPER.value_to_id (
3650 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
3651 ,p_entity_name=> INV_EBI_ITEM_HELPER.G_ORGANIZATION
3652 );
3653 IF (l_org_id IS NULL) THEN
3654 FND_MESSAGE.set_name('INV','INV_EBI_ORG_CODE_INVALID');
3655 FND_MESSAGE.set_token('COL_VALUE', p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).organization_code);
3656 FND_MSG_PUB.add;
3657 RAISE FND_API.G_EXC_ERROR;
3658 END IF;
3659 END IF;
3660 IF( l_item_id IS NULL) THEN
3661 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
3662 l_pk_col_name_val_pairs.EXTEND(2);
3663 l_pk_col_name_val_pairs(1).name := 'organization_id';
3664 l_pk_col_name_val_pairs(1).value := l_org_id;
3665 l_pk_col_name_val_pairs(2).name := 'concatenated_segments';
3666 l_pk_col_name_val_pairs(2).value := p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).item_name;
3667 l_item_id := INV_EBI_ITEM_HELPER.value_to_id (
3668 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
3669 ,p_entity_name=> INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
3670 );
3671 IF (l_item_id IS NULL ) THEN
3672 FND_MESSAGE.set_name('INV','INV_EBI_ITEM_INVALID');
3673 FND_MESSAGE.set_token('COL_VALUE', p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).item_name);
3674 FND_MSG_PUB.add;
3675 RAISE FND_API.G_EXC_ERROR;
3676 END IF;
3677 END IF;
3678 SELECT inventory_item_id
3679 ,organization_id
3680 ,description
3681 ,item_catalog_group_id
3682 ,end_date_active
3683 ,start_date_active
3684 ,primary_uom_code
3685 ,tracking_quantity_ind
3686 ,ont_pricing_qty_source
3687 ,secondary_uom_code
3688 ,secondary_default_ind
3689 ,dual_uom_deviation_high
3690 ,dual_uom_deviation_low
3691 ,allowed_units_lookup_code
3692 ,item_type
3693 ,description
3694 ,inventory_item_status_code
3695 ,primary_unit_of_measure
3696 ,lifecycle_id
3697 ,current_phase_id
3698 ,eam_item_type
3699 ,eam_activity_type_code
3700 ,eam_activity_cause_code
3701 ,eam_activity_source_code
3702 ,eam_act_notification_flag
3703 ,eam_act_shutdown_status
3704 ,bom_enabled_flag
3705 ,bom_item_type
3706 ,base_item_id
3707 ,auto_created_config_flag
3708 ,effectivity_control
3709 ,config_model_type
3710 ,config_orgs
3711 ,config_match
3712 ,eng_item_flag
3713 ,costing_enabled_flag
3714 ,inventory_asset_flag
3715 ,default_include_in_rollup_flag
3716 ,cost_of_sales_account
3717 ,std_lot_size
3718 ,inventory_planning_code
3719 ,planner_code
3720 ,planning_make_buy_code
3721 ,min_minmax_quantity
3722 ,max_minmax_quantity
3723 ,minimum_order_quantity
3724 ,maximum_order_quantity
3725 ,order_cost
3726 ,carrying_cost
3727 ,source_type
3728 ,source_organization_id
3729 ,source_subinventory
3730 ,mrp_safety_stock_code
3731 ,safety_stock_bucket_days
3732 ,mrp_safety_stock_percent
3733 ,fixed_order_quantity
3734 ,fixed_days_supply
3735 ,fixed_lot_multiplier
3736 ,vmi_minimum_units
3737 ,vmi_minimum_days
3738 ,vmi_maximum_units
3739 ,vmi_maximum_days
3740 ,vmi_fixed_order_quantity
3741 ,so_authorization_flag
3742 ,consigned_flag
3743 ,asn_autoexpire_flag
3744 ,vmi_forecast_type
3745 ,forecast_horizon
3746 ,inventory_item_flag
3747 ,stock_enabled_flag
3748 ,mtl_transactions_enabled_flag
3749 ,check_shortages_flag
3750 ,revision_qty_control_code
3751 ,reservable_type
3752 ,shelf_life_code
3753 ,shelf_life_days
3754 ,cycle_count_enabled_flag
3755 ,negative_measurement_error
3756 ,positive_measurement_error
3757 ,lot_control_code
3758 ,auto_lot_alpha_prefix
3759 ,start_auto_lot_number
3760 ,serial_number_control_code
3761 ,auto_serial_alpha_prefix
3762 ,start_auto_serial_number
3763 ,location_control_code
3764 ,restrict_subinventories_code
3765 ,restrict_locators_code
3766 ,bulk_picked_flag
3767 ,lot_status_enabled
3768 ,serial_status_enabled
3769 ,lot_split_enabled
3770 ,lot_merge_enabled
3771 ,lot_translate_enabled
3772 ,lot_substitution_enabled
3773 ,invoiceable_item_flag
3774 ,invoice_enabled_flag
3775 ,accounting_rule_id
3776 ,invoicing_rule_id
3777 ,tax_code
3778 ,sales_account
3779 ,payment_terms_id
3780 ,preprocessing_lead_time
3781 ,full_lead_time
3782 ,postprocessing_lead_time
3783 ,fixed_lead_time
3784 ,variable_lead_time
3785 ,cum_manufacturing_lead_time
3786 ,cumulative_total_lead_time
3787 ,lead_time_lot_size
3788 ,mrp_planning_code
3789 ,ato_forecast_control
3790 ,planning_exception_set
3791 ,end_assembly_pegging_flag
3792 ,shrinkage_rate
3793 ,rounding_control_type
3794 ,acceptable_early_days
3795 ,repetitive_planning_flag
3796 ,overrun_percentage
3797 ,acceptable_rate_increase
3798 ,acceptable_rate_decrease
3799 ,mrp_calculate_atp_flag
3800 ,auto_reduce_mps
3801 ,planning_time_fence_code
3802 ,planning_time_fence_days
3803 ,demand_time_fence_code
3804 ,demand_time_fence_days
3805 ,release_time_fence_code
3806 ,release_time_fence_days
3807 ,substitution_window_code
3808 ,substitution_window_days
3809 ,exclude_from_budget_flag
3810 ,days_tgt_inv_supply
3811 ,days_tgt_inv_window
3812 ,days_max_inv_supply
3813 ,days_max_inv_window
3814 ,drp_planned_flag
3815 ,critical_component_flag
3816 ,continous_transfer
3817 ,convergence
3818 ,divergence
3819 ,customer_order_flag
3820 ,customer_order_enabled_flag
3821 ,shippable_item_flag
3822 ,internal_order_flag
3823 ,internal_order_enabled_flag
3824 ,so_transactions_flag
3825 ,pick_components_flag
3826 ,atp_flag
3827 ,replenish_to_order_flag
3828 ,atp_rule_id
3829 ,atp_components_flag
3830 ,ship_model_complete_flag
3831 ,picking_rule_id
3832 ,collateral_flag
3833 ,default_shipping_org
3834 ,returnable_flag
3835 ,return_inspection_requirement
3836 ,over_shipment_tolerance
3837 ,under_shipment_tolerance
3838 ,over_return_tolerance
3839 ,under_return_tolerance
3840 ,financing_allowed_flag
3841 ,default_so_source_type
3842 ,weight_uom_code
3843 ,unit_weight
3844 ,volume_uom_code
3845 ,unit_volume
3846 ,container_item_flag
3847 ,vehicle_item_flag
3848 ,container_type_code
3849 ,internal_volume
3850 ,maximum_load_weight
3851 ,minimum_fill_percent
3852 ,equipment_type
3853 ,event_flag
3854 ,electronic_flag
3855 ,downloadable_flag
3856 ,indivisible_flag
3857 ,dimension_uom_code
3858 ,unit_length
3859 ,unit_width
3860 ,unit_height
3861 ,recipe_enabled_flag
3862 ,process_costing_enabled_flag
3863 ,process_quality_enabled_flag
3864 ,process_execution_enabled_flag
3865 ,process_supply_subinventory
3866 ,process_supply_locator_id
3867 ,process_yield_subinventory
3868 ,process_yield_locator_id
3869 ,hazardous_material_flag
3870 ,cas_number
3871 ,purchasing_item_flag
3872 ,purchasing_enabled_flag
3873 ,must_use_approved_vendor_flag
3874 ,allow_item_desc_update_flag
3875 ,rfq_required_flag
3876 ,outside_operation_flag
3877 ,outside_operation_uom_type
3878 ,taxable_flag
3879 ,purchasing_tax_code
3880 ,receipt_required_flag
3881 ,inspection_required_flag
3882 ,buyer_id
3883 ,unit_of_issue
3884 ,receive_close_tolerance
3885 ,invoice_close_tolerance
3886 ,un_number_id
3887 ,hazard_class_id
3888 ,list_price_per_unit
3889 ,market_price
3890 ,price_tolerance_percent
3891 ,rounding_factor
3892 ,encumbrance_account
3893 ,expense_account
3894 ,asset_category_id
3895 ,receipt_days_exception_code
3896 ,days_early_receipt_allowed
3897 ,days_late_receipt_allowed
3898 ,allow_substitute_receipts_flag
3899 ,allow_unordered_receipts_flag
3900 ,allow_express_delivery_flag
3901 ,qty_rcv_exception_code
3902 ,qty_rcv_tolerance
3903 ,receiving_routing_id
3904 ,enforce_ship_to_location_code
3905 ,coverage_schedule_id
3906 ,service_duration
3907 ,service_duration_period_code
3908 ,serviceable_product_flag
3909 ,service_starting_delay
3910 ,material_billable_flag
3911 ,recovered_part_disp_code
3912 ,defect_tracking_on_flag
3913 ,comms_nl_trackable_flag
3914 ,asset_creation_code
3915 ,ib_item_instance_class
3916 ,orderable_on_web_flag
3917 ,back_orderable_flag
3918 ,web_status
3919 ,minimum_license_quantity
3920 ,build_in_wip_flag
3921 ,wip_supply_type
3922 ,wip_supply_subinventory
3923 ,wip_supply_locator_id
3924 ,overcompletion_tolerance_type
3925 ,overcompletion_tolerance_value
3926 ,inventory_carry_penalty
3927 ,operation_slack_penalty
3928 ,ATTRIBUTE_CATEGORY
3929 ,ATTRIBUTE1
3930 ,ATTRIBUTE2
3931 ,ATTRIBUTE3
3932 ,ATTRIBUTE4
3933 ,ATTRIBUTE5
3934 ,ATTRIBUTE6
3935 ,ATTRIBUTE7
3936 ,ATTRIBUTE8
3937 ,ATTRIBUTE9
3938 ,ATTRIBUTE10
3939 ,ATTRIBUTE11
3940 ,ATTRIBUTE12
3941 ,ATTRIBUTE13
3942 ,ATTRIBUTE14
3943 ,ATTRIBUTE15
3944 ,ATTRIBUTE16
3945 ,ATTRIBUTE17
3946 ,ATTRIBUTE18
3947 ,ATTRIBUTE19
3948 ,ATTRIBUTE20
3949 ,ATTRIBUTE21
3950 ,ATTRIBUTE22
3951 ,ATTRIBUTE23
3952 ,ATTRIBUTE24
3953 ,ATTRIBUTE25
3954 ,ATTRIBUTE26
3955 ,ATTRIBUTE27
3956 ,ATTRIBUTE28
3957 ,ATTRIBUTE29
3958 ,ATTRIBUTE30
3959 ,GLOBAL_ATTRIBUTE_CATEGORY
3960 ,GLOBAL_ATTRIBUTE1
3961 ,GLOBAL_ATTRIBUTE2
3962 ,GLOBAL_ATTRIBUTE3
3963 ,GLOBAL_ATTRIBUTE4
3964 ,GLOBAL_ATTRIBUTE5
3965 ,GLOBAL_ATTRIBUTE6
3966 ,GLOBAL_ATTRIBUTE7
3967 ,GLOBAL_ATTRIBUTE8
3968 ,GLOBAL_ATTRIBUTE9
3969 ,GLOBAL_ATTRIBUTE10
3970 INTO l_item_core_obj.inventory_item_id
3971 ,l_item_core_obj.organization_id
3972 ,l_item_core_obj.description
3973 ,l_item_core_obj.item_catalog_group_id
3974 ,l_item_core_obj.end_date_active
3975 ,l_item_core_obj.start_date_active
3976 ,l_item_core_obj.primary_uom_code
3977 ,l_item_core_obj.tracking_quantity_ind
3978 ,l_item_core_obj.ont_pricing_qty_source
3979 ,l_item_core_obj.secondary_uom_code
3980 ,l_item_core_obj.secondary_default_ind
3981 ,l_item_core_obj.dual_uom_deviation_high
3982 ,l_item_core_obj.dual_uom_deviation_low
3983 ,l_item_core_obj.allowed_units_lookup_code
3984 ,l_item_core_obj.item_type
3985 ,l_item_core_obj.description
3986 ,l_item_core_obj.inventory_item_status_code
3987 ,l_item_core_obj.primary_unit_of_measure
3988 ,l_item_core_obj.lifecycle_id
3989 ,l_item_core_obj.current_phase_id
3990 ,l_item_asset_obj.eam_item_type
3991 ,l_item_asset_obj.eam_activity_type_code
3992 ,l_item_asset_obj.eam_activity_cause_code
3993 ,l_item_asset_obj.eam_activity_source_code
3994 ,l_item_asset_obj.eam_act_notification_flag
3995 ,l_item_asset_obj.eam_act_shutdown_status
3996 ,l_item_bom_obj.bom_enabled_flag
3997 ,l_item_bom_obj.bom_item_type
3998 ,l_item_bom_obj.base_item_id
3999 ,l_item_bom_obj.auto_created_config_flag
4000 ,l_item_bom_obj.effectivity_control
4001 ,l_item_bom_obj.config_model_type
4002 ,l_item_bom_obj.config_orgs
4003 ,l_item_bom_obj.config_match
4004 ,l_item_bom_obj.eng_item_flag
4005 ,l_item_costing_obj.costing_enabled_flag
4006 ,l_item_costing_obj.inventory_asset_flag
4007 ,l_item_costing_obj.default_include_in_rollup_f
4008 ,l_item_costing_obj.cost_of_sales_account
4009 ,l_item_costing_obj.std_lot_size
4010 ,l_item_gplan_obj.inventory_planning_code
4011 ,l_item_gplan_obj.planner_code
4012 ,l_item_gplan_obj.planning_make_buy_code
4013 ,l_item_gplan_obj.min_minmax_quantity
4014 ,l_item_gplan_obj.max_minmax_quantity
4015 ,l_item_gplan_obj.minimum_order_quantity
4016 ,l_item_gplan_obj.maximum_order_quantity
4017 ,l_item_gplan_obj.order_cost
4018 ,l_item_gplan_obj.carrying_cost
4019 ,l_item_gplan_obj.source_type
4020 ,l_item_gplan_obj.source_organization_id
4021 ,l_item_gplan_obj.source_subinventory
4022 ,l_item_gplan_obj.mrp_safety_stock_code
4023 ,l_item_gplan_obj.safety_stock_bucket_days
4024 ,l_item_gplan_obj.mrp_safety_stock_percent
4025 ,l_item_gplan_obj.fixed_order_quantity
4026 ,l_item_gplan_obj.fixed_days_supply
4027 ,l_item_gplan_obj.fixed_lot_multiplier
4028 ,l_item_gplan_obj.vmi_minimum_units
4029 ,l_item_gplan_obj.vmi_minimum_days
4030 ,l_item_gplan_obj.vmi_maximum_units
4031 ,l_item_gplan_obj.vmi_maximum_days
4032 ,l_item_gplan_obj.vmi_fixed_order_quantity
4033 ,l_item_gplan_obj.so_authorization_flag
4034 ,l_item_gplan_obj.consigned_flag
4035 ,l_item_gplan_obj.asn_autoexpire_flag
4036 ,l_item_gplan_obj.vmi_forecast_type
4037 ,l_item_gplan_obj.forecast_horizon
4038 ,l_item_inv_obj.inventory_item_flag
4039 ,l_item_inv_obj.stock_enabled_flag
4040 ,l_item_inv_obj.mtl_transactions_enabled_fl
4041 ,l_item_inv_obj.check_shortages_flag
4042 ,l_item_inv_obj.revision_qty_control_code
4043 ,l_item_inv_obj.reservable_type
4044 ,l_item_inv_obj.shelf_life_code
4045 ,l_item_inv_obj.shelf_life_days
4046 ,l_item_inv_obj.cycle_count_enabled_flag
4047 ,l_item_inv_obj.negative_measurement_error
4048 ,l_item_inv_obj.positive_measurement_error
4049 ,l_item_inv_obj.lot_control_code
4050 ,l_item_inv_obj.auto_lot_alpha_prefix
4051 ,l_item_inv_obj.start_auto_lot_number
4052 ,l_item_inv_obj.serial_number_control_code
4053 ,l_item_inv_obj.auto_serial_alpha_prefix
4054 ,l_item_inv_obj.start_auto_serial_number
4055 ,l_item_inv_obj.location_control_code
4056 ,l_item_inv_obj.restrict_subinventories_cod
4057 ,l_item_inv_obj.restrict_locators_code
4058 ,l_item_inv_obj.bulk_picked_flag
4059 ,l_item_inv_obj.lot_status_enabled
4060 ,l_item_inv_obj.serial_status_enabled
4061 ,l_item_inv_obj.lot_split_enabled
4062 ,l_item_inv_obj.lot_merge_enabled
4063 ,l_item_inv_obj.lot_translate_enabled
4064 ,l_item_inv_obj.lot_substitution_enabled
4065 ,l_item_invoice_obj.invoiceable_item_flag
4066 ,l_item_invoice_obj.invoice_enabled_flag
4067 ,l_item_invoice_obj.accounting_rule_id
4068 ,l_item_invoice_obj.invoicing_rule_id
4069 ,l_item_invoice_obj.tax_code
4070 ,l_item_invoice_obj.sales_account
4071 ,l_item_invoice_obj.payment_terms_id
4072 ,l_item_lead_time_obj.preprocessing_lead_time
4073 ,l_item_lead_time_obj.full_lead_time
4074 ,l_item_lead_time_obj.postprocessing_lead_time
4075 ,l_item_lead_time_obj.fixed_lead_time
4076 ,l_item_lead_time_obj.variable_lead_time
4077 ,l_item_lead_time_obj.cum_manufacturing_lead_time
4078 ,l_item_lead_time_obj.cumulative_total_lead_time
4079 ,l_item_lead_time_obj.lead_time_lot_size
4080 ,l_item_mrp_obj.mrp_planning_code
4081 ,l_item_mrp_obj.ato_forecast_control
4082 ,l_item_mrp_obj.planning_exception_set
4083 ,l_item_mrp_obj.end_assembly_pegging_flag
4084 ,l_item_mrp_obj.shrinkage_rate
4085 ,l_item_mrp_obj.rounding_control_type
4086 ,l_item_mrp_obj.acceptable_early_days
4087 ,l_item_mrp_obj.repetitive_planning_flag
4088 ,l_item_mrp_obj.overrun_percentage
4089 ,l_item_mrp_obj.acceptable_rate_increase
4090 ,l_item_mrp_obj.acceptable_rate_decrease
4091 ,l_item_mrp_obj.mrp_calculate_atp_flag
4092 ,l_item_mrp_obj.auto_reduce_mps
4093 ,l_item_mrp_obj.planning_time_fence_code
4094 ,l_item_mrp_obj.planning_time_fence_days
4095 ,l_item_mrp_obj.demand_time_fence_code
4096 ,l_item_mrp_obj.demand_time_fence_days
4097 ,l_item_mrp_obj.release_time_fence_code
4098 ,l_item_mrp_obj.release_time_fence_days
4099 ,l_item_mrp_obj.substitution_window_code
4100 ,l_item_mrp_obj.substitution_window_days
4101 ,l_item_mrp_obj.exclude_from_budget_flag
4102 ,l_item_mrp_obj.days_tgt_inv_supply
4103 ,l_item_mrp_obj.days_tgt_inv_window
4104 ,l_item_mrp_obj.days_max_inv_supply
4105 ,l_item_mrp_obj.days_max_inv_window
4106 ,l_item_mrp_obj.drp_planned_flag
4107 ,l_item_mrp_obj.critical_component_flag
4108 ,l_item_mrp_obj.continous_transfer
4109 ,l_item_mrp_obj.convergence
4110 ,l_item_mrp_obj.divergence
4111 ,l_item_order_obj.customer_order_flag
4112 ,l_item_order_obj.customer_order_enabled_flag
4113 ,l_item_order_obj.shippable_item_flag
4114 ,l_item_order_obj.internal_order_flag
4115 ,l_item_order_obj.internal_order_enabled_flag
4116 ,l_item_order_obj.so_transactions_flag
4117 ,l_item_order_obj.pick_components_flag
4118 ,l_item_order_obj.atp_flag
4119 ,l_item_order_obj.replenish_to_order_flag
4120 ,l_item_order_obj.atp_rule_id
4121 ,l_item_order_obj.atp_components_flag
4122 ,l_item_order_obj.ship_model_complete_flag
4123 ,l_item_order_obj.picking_rule_id
4124 ,l_item_order_obj.collateral_flag
4125 ,l_item_order_obj.default_shipping_org
4126 ,l_item_order_obj.returnable_flag
4127 ,l_item_order_obj.return_inspection_requireme
4128 ,l_item_order_obj.over_shipment_tolerance
4129 ,l_item_order_obj.under_shipment_tolerance
4130 ,l_item_order_obj.over_return_tolerance
4131 ,l_item_order_obj.under_return_tolerance
4132 ,l_item_order_obj.financing_allowed_flag
4133 ,l_item_order_obj.default_so_source_type
4134 ,l_item_phy_obj.weight_uom_code
4135 ,l_item_phy_obj.unit_weight
4136 ,l_item_phy_obj.volume_uom_code
4137 ,l_item_phy_obj.unit_volume
4138 ,l_item_phy_obj.container_item_flag
4139 ,l_item_phy_obj.vehicle_item_flag
4140 ,l_item_phy_obj.container_type_code
4141 ,l_item_phy_obj.internal_volume
4142 ,l_item_phy_obj.maximum_load_weight
4143 ,l_item_phy_obj.minimum_fill_percent
4144 ,l_item_phy_obj.equipment_type
4145 ,l_item_phy_obj.event_flag
4146 ,l_item_phy_obj.electronic_flag
4147 ,l_item_phy_obj.downloadable_flag
4148 ,l_item_phy_obj.indivisible_flag
4149 ,l_item_phy_obj.dimension_uom_code
4150 ,l_item_phy_obj.unit_length
4151 ,l_item_phy_obj.unit_width
4152 ,l_item_phy_obj.unit_height
4153 ,l_item_process_obj.recipe_enabled_flag
4154 ,l_item_process_obj.process_costing_enabled_flag
4155 ,l_item_process_obj.process_quality_enabled_flag
4156 ,l_item_process_obj.process_execution_enabled_flag
4157 ,l_item_process_obj.process_supply_subinventory
4158 ,l_item_process_obj.process_supply_locator_id
4159 ,l_item_process_obj.process_yield_subinventory
4160 ,l_item_process_obj.process_yield_locator_id
4161 ,l_item_process_obj.hazardous_material_flag
4162 ,l_item_process_obj.cas_number
4163 ,l_item_pur_obj.purchasing_item_flag
4164 ,l_item_pur_obj.purchasing_enabled_flag
4165 ,l_item_pur_obj.must_use_approved_vendor_fl
4166 ,l_item_pur_obj.allow_item_desc_update_flag
4167 ,l_item_pur_obj.rfq_required_flag
4168 ,l_item_pur_obj.outside_operation_flag
4169 ,l_item_pur_obj.outside_operation_uom_type
4170 ,l_item_pur_obj.taxable_flag
4171 ,l_item_pur_obj.purchasing_tax_code
4172 ,l_item_pur_obj.receipt_required_flag
4173 ,l_item_pur_obj.inspection_required_flag
4174 ,l_item_pur_obj.buyer_id
4175 ,l_item_pur_obj.unit_of_issue
4176 ,l_item_pur_obj.receive_close_tolerance
4177 ,l_item_pur_obj.invoice_close_tolerance
4178 ,l_item_pur_obj.un_number_id
4179 ,l_item_pur_obj.hazard_class_id
4180 ,l_item_pur_obj.list_price_per_unit
4181 ,l_item_pur_obj.market_price
4182 ,l_item_pur_obj.price_tolerance_percent
4183 ,l_item_pur_obj.rounding_factor
4184 ,l_item_pur_obj.encumbrance_account
4185 ,l_item_pur_obj.expense_account
4186 ,l_item_pur_obj.asset_category_id
4187 ,l_item_recving_obj.receipt_days_exception_code
4188 ,l_item_recving_obj.days_early_receipt_allowed
4189 ,l_item_recving_obj.days_late_receipt_allowed
4190 ,l_item_recving_obj.allow_substitute_receipts_f
4191 ,l_item_recving_obj.allow_unordered_receipts_fl
4192 ,l_item_recving_obj.allow_express_delivery_flag
4193 ,l_item_recving_obj.qty_rcv_exception_code
4194 ,l_item_recving_obj.qty_rcv_tolerance
4195 ,l_item_recving_obj.receiving_routing_id
4196 ,l_item_recving_obj.enforce_ship_to_location_c
4197 ,l_item_service_obj.coverage_schedule_id
4198 ,l_item_service_obj.service_duration
4199 ,l_item_service_obj.service_duration_period_cod
4200 ,l_item_service_obj.serviceable_product_flag
4201 ,l_item_service_obj.service_starting_delay
4202 ,l_item_service_obj.material_billable_flag
4203 ,l_item_service_obj.recovered_part_disp_code
4204 ,l_item_service_obj.defect_tracking_on_flag
4205 ,l_item_service_obj.comms_nl_trackable_flag
4206 ,l_item_service_obj.asset_creation_code
4207 ,l_item_service_obj.ib_item_instance_class
4208 ,l_item_web_opiton.orderable_on_web_flag
4209 ,l_item_web_opiton.back_orderable_flag
4210 ,l_item_web_opiton.web_status
4211 ,l_item_web_opiton.minimum_license_quantity
4212 ,l_item_wip_obj.build_in_wip_flag
4213 ,l_item_wip_obj.wip_supply_type
4214 ,l_item_wip_obj.wip_supply_subinventory
4215 ,l_item_wip_obj.wip_supply_locator_id
4216 ,l_item_wip_obj.overcompletion_tolerance_ty
4217 ,l_item_wip_obj.overcompletion_tolerance_va
4218 ,l_item_wip_obj.inventory_carry_penalty
4219 ,l_item_wip_obj.operation_slack_penalty
4220 ,l_item_custom_obj.ATTRIBUTE_CATEGORY
4221 ,l_item_custom_obj.ATTRIBUTE1
4222 ,l_item_custom_obj.ATTRIBUTE2
4223 ,l_item_custom_obj.ATTRIBUTE3
4224 ,l_item_custom_obj.ATTRIBUTE4
4225 ,l_item_custom_obj.ATTRIBUTE5
4226 ,l_item_custom_obj.ATTRIBUTE6
4227 ,l_item_custom_obj.ATTRIBUTE7
4228 ,l_item_custom_obj.ATTRIBUTE8
4229 ,l_item_custom_obj.ATTRIBUTE9
4230 ,l_item_custom_obj.ATTRIBUTE10
4231 ,l_item_custom_obj.ATTRIBUTE11
4232 ,l_item_custom_obj.ATTRIBUTE12
4233 ,l_item_custom_obj.ATTRIBUTE13
4234 ,l_item_custom_obj.ATTRIBUTE14
4235 ,l_item_custom_obj.ATTRIBUTE15
4236 ,l_item_custom_obj.ATTRIBUTE16
4237 ,l_item_custom_obj.ATTRIBUTE17
4238 ,l_item_custom_obj.ATTRIBUTE18
4239 ,l_item_custom_obj.ATTRIBUTE19
4240 ,l_item_custom_obj.ATTRIBUTE20
4241 ,l_item_custom_obj.ATTRIBUTE21
4242 ,l_item_custom_obj.ATTRIBUTE22
4243 ,l_item_custom_obj.ATTRIBUTE23
4244 ,l_item_custom_obj.ATTRIBUTE24
4245 ,l_item_custom_obj.ATTRIBUTE25
4246 ,l_item_custom_obj.ATTRIBUTE26
4247 ,l_item_custom_obj.ATTRIBUTE27
4248 ,l_item_custom_obj.ATTRIBUTE28
4249 ,l_item_custom_obj.ATTRIBUTE29
4250 ,l_item_custom_obj.ATTRIBUTE30
4251 ,l_item_custom_obj.GLOBAL_ATTRIBUTE_CATEGORY
4252 ,l_item_custom_obj.GLOBAL_ATTRIBUTE1
4253 ,l_item_custom_obj.GLOBAL_ATTRIBUTE2
4254 ,l_item_custom_obj.GLOBAL_ATTRIBUTE3
4255 ,l_item_custom_obj.GLOBAL_ATTRIBUTE4
4256 ,l_item_custom_obj.GLOBAL_ATTRIBUTE5
4257 ,l_item_custom_obj.GLOBAL_ATTRIBUTE6
4258 ,l_item_custom_obj.GLOBAL_ATTRIBUTE7
4259 ,l_item_custom_obj.GLOBAL_ATTRIBUTE8
4260 ,l_item_custom_obj.GLOBAL_ATTRIBUTE9
4261 ,l_item_custom_obj.GLOBAL_ATTRIBUTE10
4262 FROM mtl_system_items_b
4263 WHERE inventory_item_id=l_item_id AND organization_id=l_org_id;
4264
4265 IF ( (l_item_core_obj.revision_id IS NULL OR l_item_core_obj.revision_id= fnd_api.g_miss_num)
4266 AND (l_org_id IS NOT NULL AND l_item_id IS NOT NULL)
4267 ) THEN
4268 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4269 l_pk_col_name_val_pairs.EXTEND(2);
4270 l_pk_col_name_val_pairs(1).name := 'organization_id';
4271 l_pk_col_name_val_pairs(1).value := l_org_id;
4272 l_pk_col_name_val_pairs(2).name := 'inventory_item_id';
4273 l_pk_col_name_val_pairs(2).value := l_item_id;
4274 l_item_core_obj.revision_id := INV_EBI_ITEM_HELPER.value_to_id(
4275 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4276 ,p_entity_name => INV_EBI_ITEM_HELPER.G_REVISION
4277 );
4278 l_pk_col_name_val_pairs.TRIM(2);
4279 END IF;
4280 ctr := 1;
4281 FOR c IN c_mfr_part_cursor(l_item_id,l_org_id)
4282 LOOP
4283 l_item_mfr_part_obj := inv_ebi_manufacturer_part_obj(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
4284 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
4285 l_mfr_part_table.extend();
4286 l_item_mfr_part_obj.manufacturer_id := c.manufacturer_id;
4287 l_item_mfr_part_obj.mfg_part_num := c.mfg_part_num;
4288 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4289 l_pk_col_name_val_pairs.EXTEND(1);
4290 l_pk_col_name_val_pairs(1).name := 'manufacturer_id';
4291 l_pk_col_name_val_pairs(1).value := l_item_mfr_part_obj.manufacturer_id;
4292 l_item_mfr_part_obj.manufacturer_name := INV_EBI_ITEM_HELPER.id_to_value (
4293 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4294 ,p_entity_name=> G_MANUFACTURER
4295 );
4296 l_mfr_part_table(ctr) := l_item_mfr_part_obj;
4297 ctr := ctr + 1;
4298 END LOOP;
4299
4300 --Bug 7240247 To get Item udas
4301 get_item_uda(
4302 p_inventory_item_id => l_item_core_obj.inventory_item_id,
4303 p_organization_id => l_item_core_obj.organization_id,
4304 p_item_classification_id => l_item_core_obj.item_catalog_group_id,
4305 p_revision_id => l_item_core_obj.revision_id,
4306 x_item_uda => l_uda_obj,
4307 x_return_status => l_return_status,
4308 x_msg_count => l_msg_count,
4309 x_msg_data => l_msg_data
4310 );
4311 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4312 l_item_attr_tbl(i).msg_data := l_msg_data;
4313 RAISE FND_API.G_EXC_ERROR;
4314 END IF;
4315
4316 -- To populate unit_cost of l_item_costing_obj
4317 l_item_costing_obj.unit_cost := CST_COST_API.get_item_cost (
4318 p_api_version => 1,
4319 p_inventory_item_id => l_item_id,
4320 p_organization_id => l_org_id,
4321 p_cost_group_id => l_default_cost_group_id,
4322 p_cost_type_id => l_default_cost_type_id
4323 );
4324
4325 IF(l_item_core_obj.organization_id IS NOT NULL) THEN
4326 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4327 l_pk_col_name_val_pairs.EXTEND(1);
4328 l_pk_col_name_val_pairs(1).name := 'organization_id';
4329 l_pk_col_name_val_pairs(1).value := l_item_core_obj.organization_id;
4330 l_item_core_obj.organization_code := INV_EBI_ITEM_HELPER.id_to_value (
4331 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4332 ,p_entity_name=> G_ORGANIZATION
4333 );
4334 END IF;
4335
4336 IF(l_item_core_obj.template_id IS NOT NULL) THEN
4337 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4338 l_pk_col_name_val_pairs.EXTEND(1);
4339 l_pk_col_name_val_pairs(1).name := 'template_id';
4340 l_pk_col_name_val_pairs(1).value := l_item_core_obj.template_id;
4341 l_item_core_obj.template_name := INV_EBI_ITEM_HELPER.id_to_value(
4342 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4343 ,p_entity_name=> G_TEMPLATE
4344 );
4345 END IF;
4346
4347 IF(l_item_core_obj.item_catalog_group_id IS NOT NULL) THEN
4348 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4349 l_pk_col_name_val_pairs.EXTEND(1);
4350 l_pk_col_name_val_pairs(1).name := 'item_catalog_group_id';
4351 l_pk_col_name_val_pairs(1).value := l_item_core_obj.item_catalog_group_id;
4352 l_item_core_obj.item_catalog_group_code := INV_EBI_ITEM_HELPER.id_to_value(
4353 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4354 ,p_entity_name=> G_ITEM_CATALOG_GROUP
4355 );
4356 END IF;
4357
4358 IF(l_item_core_obj.lifecycle_id IS NOT NULL) THEN
4359 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4360 l_pk_col_name_val_pairs.EXTEND(1);
4361 l_pk_col_name_val_pairs(1).name := 'proj_element_id'; -- Column name not available project_element_id
4362 l_pk_col_name_val_pairs(1).value := l_item_core_obj.lifecycle_id;
4363 l_item_core_obj.lifecycle_name := INV_EBI_ITEM_HELPER.id_to_value(
4364 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4365 ,p_entity_name=> G_LIFECYCLE
4366 );
4367 END IF;
4368 IF(l_item_core_obj.current_phase_id IS NOT NULL) THEN
4369 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4370 l_pk_col_name_val_pairs.EXTEND(1);
4371 l_pk_col_name_val_pairs(1).name := 'proj_element_id';
4372 l_pk_col_name_val_pairs(1).value := l_item_core_obj.current_phase_id;
4373 l_item_core_obj.current_phase_name := INV_EBI_ITEM_HELPER.id_to_value(
4374 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4375 ,p_entity_name=> G_CURRENT_PHASE
4376 );
4377 END IF;
4378 IF (l_item_core_obj.revision_id IS NOT NULL) THEN
4379 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4380 l_pk_col_name_val_pairs.EXTEND(1);
4381 l_pk_col_name_val_pairs(1).name := 'revision_id';
4382 l_pk_col_name_val_pairs(1).value := l_item_core_obj.revision_id ;
4383 l_item_core_obj.revision_code := INV_EBI_ITEM_HELPER.id_to_value(
4384 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4385 ,p_entity_name=> G_REVISION
4386 );
4387 END IF;
4388 IF(l_item_pur_obj.hazard_class_id IS NOT NULL) THEN
4389 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4390 l_pk_col_name_val_pairs.EXTEND(1);
4391 l_pk_col_name_val_pairs(1).name := 'hazard_class_id';
4392 l_pk_col_name_val_pairs(1).value := l_item_pur_obj.hazard_class_id;
4393 l_item_pur_obj.hazard_class_code := INV_EBI_ITEM_HELPER.id_to_value(
4394 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4395 ,p_entity_name=> G_HAZARD_CLASS
4396 );
4397 END IF;
4398 IF (l_item_pur_obj.asset_category_id IS NOT NULL) THEN
4399 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4400 l_pk_col_name_val_pairs.EXTEND(1);
4401 l_pk_col_name_val_pairs(1).name := 'category_id';
4402 l_pk_col_name_val_pairs(1).value := l_item_pur_obj.asset_category_id ;
4403 l_item_pur_obj.asset_category_code := INV_EBI_ITEM_HELPER.id_to_value(
4404 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4405 ,p_entity_name=> G_ASSET_CATEGORY
4406 );
4407 END IF;
4408 IF (l_item_bom_obj.base_item_id IS NOT NULL) THEN
4409 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4410 l_pk_col_name_val_pairs.EXTEND(2);
4411 l_pk_col_name_val_pairs(1).name := 'inventory_item_id';
4412 l_pk_col_name_val_pairs(1).value := l_item_bom_obj.base_item_id;
4413 l_pk_col_name_val_pairs(2).name := 'organization_id';
4414 l_pk_col_name_val_pairs(2).value := l_item_core_obj.organization_id;
4415 l_item_bom_obj.base_item_number := INV_EBI_ITEM_HELPER.id_to_value(
4416 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4417 ,p_entity_name=> G_INVENTORY_ITEM
4418 );
4419 END IF;
4420 -- gets the item number
4421 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4422 l_pk_col_name_val_pairs.EXTEND(1);
4423 l_pk_col_name_val_pairs(1).name := 'organization_id';
4424 l_pk_col_name_val_pairs(1).value := l_item_core_obj.organization_id;
4425 l_pk_col_name_val_pairs.EXTEND(1);
4426 l_pk_col_name_val_pairs(2).name := 'inventory_item_id';
4427 l_pk_col_name_val_pairs(2).value := l_item_core_obj.inventory_item_id;
4428 l_item_core_obj.item_name := INV_EBI_ITEM_HELPER.id_to_value(
4429 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4430 ,p_entity_name=> G_INVENTORY_ITEM
4431 );
4432 SELECT MASTER_ORGANIZATION_ID INTO l_item_core_obj.MASTER_ORGANIZATION_ID
4433 FROM mtl_parameters WHERE organization_id=l_org_id;
4434 -- converts the master org id to master org code
4435 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4436 l_pk_col_name_val_pairs.EXTEND(1);
4437 l_pk_col_name_val_pairs(1).name := 'organization_id';
4438 l_pk_col_name_val_pairs(1).value := l_item_core_obj.master_organization_id;
4439 l_item_core_obj.master_organization_code := INV_EBI_ITEM_HELPER.id_to_value(
4440 p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4441 ,p_entity_name=> INV_EBI_ITEM_HELPER.G_ORGANIZATION
4442 );
4443 IF(NOT (l_getassetmgmtattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE )) THEN
4444 l_item_asset_obj := NULL;
4445 END IF;
4446 IF(NOT(l_getbomattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE )) THEN
4447 l_item_bom_obj := NULL;
4448 END IF;
4449 IF(NOT(l_getcostingattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE )) THEN
4450 l_item_costing_obj := NULL;
4451 END IF;
4452 IF(NOT(l_getgeneralplanningattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE ))THEN
4453 l_item_gplan_obj := NULL;
4454 END IF;
4455 IF(NOT(l_getinventoryattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE )) THEN
4456 l_item_inv_obj := NULL;
4457 END IF;
4458 IF(NOT(l_getinvoicingattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE )) THEN
4459 l_item_invoice_obj := NULL;
4460 END IF;
4461 IF(NOT(l_getleadtimeattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE )) THEN
4462 l_item_lead_time_obj := NULL;
4463 END IF;
4464 IF(NOT(l_getmpsmrpplanningattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE )) THEN
4465 l_item_mrp_obj := NULL;
4466 END IF;
4467 IF(NOT(l_getorderattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE )) THEN
4468 l_item_order_obj := NULL;
4469 END IF;
4470 IF(NOT(l_getphysicalattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE )) THEN
4471 l_item_phy_obj := NULL;
4472 END IF;
4473 IF(NOT(l_getprocessattrs=FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE )) THEN
4474 l_item_process_obj := NULL;
4475 END IF;
4476 IF(NOT(l_getpurchasingattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE )) THEN
4477 l_item_pur_obj := NULL;
4478 END IF;
4479 IF(NOT(l_getrecevingattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE ))THEN
4480 l_item_recving_obj := NULL;
4481 END IF;
4482 IF(NOT(l_getserviceattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE ))THEN
4483 l_item_service_obj := NULL;
4484 END IF;
4485 IF(NOT(l_getweboptionattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE ))THEN
4486 l_item_web_opiton := NULL;
4487 END IF;
4488 IF(NOT(l_getwipattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE ))THEN
4489 l_item_wip_obj := NULL;
4490 END IF;
4491 l_item_obj := inv_ebi_item_obj(l_item_core_obj
4492 ,l_item_phy_obj
4493 ,l_item_inv_obj
4494 ,l_item_pur_obj
4495 ,l_item_recving_obj
4496 ,l_item_gplan_obj
4497 ,l_item_mrp_obj
4498 ,l_item_order_obj
4499 ,l_item_service_obj
4500 ,l_item_bom_obj
4501 ,l_item_costing_obj
4502 ,l_item_lead_time_obj
4503 ,l_item_wip_obj
4504 ,l_item_invoice_obj
4505 ,l_item_web_opiton
4506 ,l_item_asset_obj
4507 ,NULL
4508 ,l_item_process_obj
4509 ,l_item_custom_obj
4510 ,NULL
4511 ,NULL
4512 ,l_mfr_part_table
4513 ,l_uda_obj
4514 ,NULL
4515 ,NULL
4516 ,NULL
4517 ,NULL
4518 );
4519 get_Operating_unit
4520 (p_oranization_id => l_org_id
4521 ,x_operating_unit => l_item_obj.operating_unit
4522 ,x_ouid => l_item_obj.operating_unit_id
4523 );
4524 l_item_attr_tbl(i).item_obj := l_item_obj;
4525 EXCEPTION
4526 WHEN FND_API.G_EXC_ERROR THEN
4527 x_return_status := FND_API.G_RET_STS_ERROR;
4528 l_item_attr_tbl(i).return_status := FND_API.G_RET_STS_ERROR;
4529 IF l_item_attr_tbl(i).msg_data IS NOT NULL THEN
4530 FND_MSG_PUB.count_and_get(
4531 p_encoded => FND_API.g_false
4532 ,p_count => l_item_attr_tbl(i).msg_count
4533 ,p_data => l_item_attr_tbl(i).msg_data
4534 );
4535 END IF;
4536 populate_err_msg(p_orgid => l_org_id
4537 ,p_invid => l_item_id
4538 ,p_org_code => p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).organization_code
4539 ,p_item_name => p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).item_name
4540 ,p_part_err_msg => l_item_attr_tbl(i).msg_data
4541 ,x_err_msg => x_msg_data
4542 );
4543 WHEN OTHERS THEN
4544 x_return_status := FND_API.G_RET_STS_ERROR;
4545 l_item_attr_tbl(i).return_status := FND_API.G_RET_STS_ERROR;
4546 populate_err_msg(p_orgid => l_org_id
4547 ,p_invid => l_item_id
4548 ,p_org_code => p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).organization_code
4549 ,p_item_name => p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).item_name
4550 ,p_part_err_msg => SQLERRM||' -> at inv_ebi_item_helper.get_item_attributes'
4551 ,x_err_msg => x_msg_data
4552 );
4553 END;
4554 END LOOP;
4555 x_item_tbl_obj:=inv_ebi_item_attr_tbl_obj(l_item_attr_tbl);
4556 EXCEPTION
4557 WHEN OTHERS THEN
4558 x_return_status := FND_API.G_RET_STS_ERROR;
4559 IF (x_msg_data IS NOT NULL) THEN
4560 x_msg_data := x_msg_data ||' -> at INV_EBI_ITEM_HELPER.get_item_attributes';
4561 ELSE
4562 x_msg_data := SQLERRM||' at INV_EBI_ITEM_HELPER.get_item_attributes';
4563 END IF;
4564 END get_item_attributes;
4565
4566
4567 /***************************************************************************************************
4568 -- API name : get_last_run_date
4569 -- Type : Private For Internal Use Only
4570 -- Purpose : To get the last run date of the concurrent program
4571 *****************************************************************************************************/
4572
4573 FUNCTION get_last_run_date( p_conc_prog_id IN NUMBER
4574 ,p_appl_id IN NUMBER
4575 ) RETURN DATE
4576 IS
4577 l_date DATE :=NULL;
4578 CURSOR c_last_sche_comp_date
4579 IS
4580 SELECT actual_start_date FROM (
4581 SELECT actual_start_date
4582 FROM fnd_concurrent_requests
4583 WHERE program_application_id = p_appl_id
4584 AND concurrent_program_id = p_conc_prog_id
4585 AND UPPER(phase_code) = 'C'
4586 AND (root_request_id is not null OR resubmit_interval is not null)
4587 AND actual_start_date is not null
4588 ORDER BY actual_start_date DESC)
4589 WHERE ROWNUM = 1;
4590
4591 CURSOR c_last_comp_date
4592 IS
4593 SELECT actual_start_date INTO l_date FROM (
4594 SELECT actual_start_date
4595 FROM fnd_concurrent_requests
4596 WHERE program_application_id = p_appl_id
4597 AND concurrent_program_id = p_conc_prog_id
4598 AND UPPER(phase_code) = 'C'
4599 AND actual_start_date is not null
4600 ORDER BY actual_start_date DESC)
4601 WHERE ROWNUM = 1;
4602
4603 BEGIN
4604 IF c_last_sche_comp_date%ISOPEN THEN
4605 CLOSE c_last_sche_comp_date;
4606 END IF;
4607
4608 OPEN c_last_sche_comp_date;
4609 FETCH c_last_sche_comp_date into l_date;
4610 CLOSE c_last_sche_comp_date;
4611
4612 IF l_date IS NULL THEN
4613 IF c_last_comp_date%ISOPEN THEN
4614 CLOSE c_last_comp_date;
4615 END IF;
4616 OPEN c_last_comp_date;
4617 FETCH c_last_comp_date into l_date;
4618 CLOSE c_last_comp_date;
4619 END IF;
4620
4621 IF l_date IS NULL THEN
4622 l_date := SYSDATE-30;
4623 END IF;
4624
4625 RETURN l_date;
4626 EXCEPTION
4627 WHEN OTHERS THEN
4628 IF c_last_sche_comp_date%ISOPEN THEN
4629 CLOSE c_last_sche_comp_date;
4630 END IF;
4631 IF c_last_comp_date%ISOPEN THEN
4632 CLOSE c_last_comp_date;
4633 END IF;
4634 END get_last_run_date;
4635
4636 /************************************************************************************
4637 -- API name : parse_input_String
4638 -- Type : Public
4639 -- Function : To parse the input string
4640 ************************************************************************************/
4641
4642 FUNCTION parse_input_string(
4643 p_input_string IN VARCHAR2
4644 )
4645 RETURN FND_TABLE_OF_VARCHAR2_255
4646 IS
4647 l_input_string VARCHAR2(240);
4648 l_count NUMBER:=0;
4649 l_length NUMBER:=0;
4650 l_parsed_tbl FND_TABLE_OF_VARCHAR2_255;
4651 BEGIN
4652 l_input_string := p_input_string;
4653 l_parsed_tbl := FND_TABLE_OF_VARCHAR2_255();
4654 l_length := LENGTH(l_input_string);
4655
4656 IF (SUBSTR(l_input_string,l_length-1) <> ';;') THEN
4657 l_input_string := l_input_string || ';;';
4658 END IF;
4659
4660 WHILE INSTR(l_input_string,';;') > 0 LOOP
4661 l_parsed_tbl.EXTEND(1);
4662 l_count := l_count+1;
4663 l_parsed_tbl(l_count) := SUBSTR(l_input_string,1,INSTR(l_input_string,';;')-1) ;
4664 l_input_string := SUBSTR(l_input_string,INSTR(l_input_string,';;')+2);
4665 END LOOP;
4666
4667 RETURN l_parsed_tbl;
4668 END parse_input_string;
4669 /************************************************************************************
4670 -- API name : filter_items_based_on_org
4671 -- Type : Public
4672 -- Function : To filter items based on the given organization
4673 ************************************************************************************/
4674
4675 PROCEDURE filter_items_based_on_org(
4676 p_org_codes IN VARCHAR2
4677 ,p_item_tbl IN inv_ebi_get_opr_attrs_tbl
4678 ,x_item_tbl OUT NOCOPY inv_ebi_get_opr_attrs_tbl
4679 ,x_return_status OUT NOCOPY VARCHAR2
4680 ,x_msg_count OUT NOCOPY NUMBER
4681 ,x_msg_data OUT NOCOPY VARCHAR2)
4682 IS
4683 l_org_tbl FND_TABLE_OF_VARCHAR2_255;
4684 l_item_output_tbl inv_ebi_get_opr_attrs_tbl;
4685 l_counter NUMBER:=0;
4686 BEGIN
4687 x_return_status := FND_API.g_ret_sts_success;
4688 l_item_output_tbl := inv_ebi_get_opr_attrs_tbl();
4689
4690 IF p_org_codes IS NOT NULL THEN
4691 l_org_tbl := parse_input_string(p_org_codes);
4692 END IF;
4693
4694 IF p_item_tbl IS NOT NULL AND p_item_tbl.COUNT>0 THEN
4695 FOR i in p_item_tbl.FIRST..p_item_tbl.LAST LOOP
4696 IF l_org_tbl IS NOT NULL AND l_org_tbl.COUNT>0 THEN
4697 FOR j in l_org_tbl.FIRST..l_org_tbl.LAST LOOP
4698 IF (p_item_tbl(i).organization_code = l_org_tbl(j)) THEN
4699 l_counter := l_counter + 1;
4700 l_item_output_tbl.EXTEND(1);
4701 l_item_output_tbl(l_counter) := p_item_tbl(i);
4702 EXIT;
4703 END IF;
4704 END LOOP;
4705 END IF;
4706 END LOOP;
4707 END IF;
4708
4709 x_item_tbl := l_item_output_tbl;
4710
4711 EXCEPTION
4712 WHEN OTHERS THEN
4713 x_return_status := FND_API.g_ret_sts_unexp_error;
4714 x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.filter_items_based_on_org';
4715 END filter_items_based_on_org;
4716
4717 /************************************************************************************
4718 -- API name : parse_and_get_item
4719 -- Type : Private For Internal Use Only
4720 -- Function : To parse the input string and get list of items
4721 ************************************************************************************/
4722 PROCEDURE parse_and_get_item(
4723 p_item_names IN VARCHAR2
4724 ,p_org_codes IN VARCHAR2
4725 ,x_item_tbl OUT NOCOPY inv_ebi_get_opr_attrs_tbl
4726 ,x_return_status OUT NOCOPY VARCHAR2
4727 ,x_msg_count OUT NOCOPY NUMBER
4728 ,x_msg_data OUT NOCOPY VARCHAR2)
4729 IS
4730 l_return_status VARCHAR2(2);
4731 l_msg_data VARCHAR2(2000);
4732 l_count NUMBER:=0;
4733 l_counter NUMBER := 0;
4734 l_entity_exist NUMBER :=0;
4735 l_entity_count NUMBER :=0;
4736 l_org_id NUMBER;
4737 l_item_id NUMBER;
4738 l_item_output_tbl inv_ebi_get_opr_attrs_tbl;
4739 l_item_obj inv_ebi_get_operational_attrs;
4740 l_item_tbl FND_TABLE_OF_VARCHAR2_255;
4741 l_org_tbl FND_TABLE_OF_VARCHAR2_255;
4742 l_valid_item_tbl FND_TABLE_OF_VARCHAR2_255;
4743 l_valid_org_tbl FND_TABLE_OF_VARCHAR2_255;
4744 l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl ;
4745 CURSOR c_get_all_orgs(p_item_name VARCHAR2) IS
4746 SELECT msik.inventory_item_id,msik.organization_id,mp.organization_code
4747 FROM mtl_system_items_kfv msik,mtl_parameters mp
4748 WHERE msik.concatenated_segments = p_item_name
4749 AND msik.organization_id = mp.organization_id;
4750 BEGIN
4751 x_return_status := FND_API.g_ret_sts_success;
4752 l_item_output_tbl := inv_ebi_get_opr_attrs_tbl();
4753
4754 IF p_item_names IS NOT NULL THEN
4755 l_item_tbl := parse_input_string(p_item_names);
4756 END IF;
4757
4758 IF p_org_codes IS NOT NULL THEN
4759 l_org_tbl := parse_input_string(p_org_codes);
4760 END IF;
4761 IF l_item_tbl IS NOT NULL AND l_item_tbl.COUNT > 0 THEN
4762 l_valid_item_tbl := FND_TABLE_OF_VARCHAR2_255();
4763 l_entity_count :=0;
4764 FOR i in l_item_tbl.FIRST..l_item_tbl.LAST LOOP
4765 BEGIN
4766 FND_MSG_PUB.initialize();
4767 SELECT COUNT(1) into l_entity_exist
4768 FROM mtl_system_items_kfv
4769 WHERE concatenated_segments = l_item_tbl(i);
4770 IF l_entity_exist>0 THEN
4771 l_entity_count := l_entity_count +1;
4772 l_valid_item_tbl.EXTEND();
4773 l_valid_item_tbl(l_entity_count) := l_item_tbl(i);
4774 ELSE
4775 FND_MESSAGE.set_name('INV','INV_EBI_ITEM_INVALID');
4776 FND_MESSAGE.set_token('COL_VALUE', l_item_tbl(i));
4777 FND_MSG_PUB.add;
4778 RAISE FND_API.G_EXC_ERROR;
4779 END IF;
4780 EXCEPTION
4781 WHEN FND_API.G_EXC_ERROR THEN
4782 x_return_status := FND_API.g_ret_sts_error;
4783 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
4784 ,p_count => x_msg_count
4785 ,p_data => l_msg_data
4786 );
4787 x_msg_data := x_msg_data || l_msg_data ||' , ' ;
4788 WHEN OTHERS THEN
4789 x_return_status := FND_API.g_ret_sts_error;
4790 x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.parse_and_get_item';
4791 END;
4792 END LOOP;
4793 END IF;
4794 IF l_org_tbl IS NOT NULL AND l_org_tbl.COUNT > 0 THEN
4795 l_valid_org_tbl := FND_TABLE_OF_VARCHAR2_255();
4796 l_entity_count :=0;
4797 FOR i in l_org_tbl.FIRST..l_org_tbl.LAST LOOP
4798 BEGIN
4799 FND_MSG_PUB.initialize();
4800 SELECT COUNT(1) into l_entity_exist
4801 FROM mtl_parameters
4802 WHERE organization_code = l_org_tbl(i);
4803 IF l_entity_exist>0 THEN
4804 l_entity_count := l_entity_count +1;
4805 l_valid_org_tbl.EXTEND();
4806 l_valid_org_tbl(l_entity_count) := l_org_tbl(i);
4807 ELSE
4808 FND_MESSAGE.set_name('INV','INV_EBI_ORG_CODE_INVALID');
4809 FND_MESSAGE.set_token('COL_VALUE', l_org_tbl(i));
4810 FND_MSG_PUB.add;
4811 RAISE FND_API.G_EXC_ERROR;
4812 END IF;
4813 EXCEPTION
4814 WHEN FND_API.G_EXC_ERROR THEN
4815 x_return_status := FND_API.g_ret_sts_error;
4816 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
4817 ,p_count => x_msg_count
4818 ,p_data => l_msg_data
4819 );
4820 x_msg_data := x_msg_data || l_msg_data ||' , ' ;
4821 WHEN OTHERS THEN
4822 x_return_status := FND_API.g_ret_sts_error;
4823 x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.parse_and_get_item';
4824 END;
4825 END LOOP;
4826 END IF;
4827 IF l_valid_item_tbl IS NOT NULL AND l_valid_item_tbl.COUNT > 0 THEN
4828 FOR i in l_valid_item_tbl.FIRST..l_valid_item_tbl.LAST LOOP
4829 BEGIN
4830 FND_MSG_PUB.initialize();
4831 l_count :=0;
4832 IF l_valid_org_tbl IS NOT NULL AND l_valid_org_tbl.COUNT > 0 THEN
4833 FOR j in l_valid_org_tbl.FIRST..l_valid_org_tbl.LAST LOOP
4834 l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4835 l_pk_col_name_val_pairs.EXTEND();
4836 l_pk_col_name_val_pairs(1).name := 'organization_code';
4837 l_pk_col_name_val_pairs(1).value := l_valid_org_tbl(j);
4838 l_org_id := INV_EBI_ITEM_HELPER.value_to_id( p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4839 ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
4840 );
4841 l_pk_col_name_val_pairs.TRIM(1);
4842 l_pk_col_name_val_pairs.EXTEND(2);
4843 l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
4844 l_pk_col_name_val_pairs(1).value := l_valid_item_tbl(i);
4845 l_pk_col_name_val_pairs(2).name := 'organization_id';
4846 l_pk_col_name_val_pairs(2).value := l_org_id;
4847 l_item_id := INV_EBI_ITEM_HELPER.value_to_id( p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4848 ,p_entity_name => INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
4849 );
4850 l_pk_col_name_val_pairs.TRIM(2);
4851
4852 IF l_item_id IS NOT NULL THEN
4853 l_counter := l_counter + 1;
4854 l_item_obj := inv_ebi_get_operational_attrs( l_item_id, l_valid_item_tbl(i) , l_org_id, l_valid_org_tbl(j),NULL,NULL);
4855 l_item_output_tbl.EXTEND(1);
4856 l_item_output_tbl(l_counter) := l_item_obj;
4857 l_count := 1;
4858 END IF;
4859
4860 END LOOP;
4861 ELSE
4862 FOR cur IN c_get_all_orgs(l_valid_item_tbl(i)) LOOP
4863 l_counter := l_counter + 1;
4864 l_item_obj := inv_ebi_get_operational_attrs( cur.inventory_item_id, l_valid_item_tbl(i) , cur.organization_id, cur.organization_code,NULL,NULL);
4865 l_item_output_tbl.EXTEND(1);
4866 l_item_output_tbl(l_counter) := l_item_obj;
4867 l_count := 1;
4868 END LOOP;
4869 END IF;
4870
4871 IF l_count = 0 THEN
4872 FND_MESSAGE.set_name('INV','INV_EBI_INVALID_USER_INPUT');
4873 FND_MESSAGE.set_token('USER_INPUT', l_valid_item_tbl(i));
4874 FND_MSG_PUB.add;
4875 RAISE FND_API.G_EXC_ERROR;
4876 END IF;
4877 EXCEPTION
4878 WHEN FND_API.G_EXC_ERROR THEN
4879 x_return_status := FND_API.g_ret_sts_error;
4880 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false
4881 ,p_count => x_msg_count
4882 ,p_data => l_msg_data
4883 );
4884 x_msg_data := x_msg_data || l_msg_data ;
4885 WHEN OTHERS THEN
4886 x_return_status := FND_API.g_ret_sts_unexp_error;
4887 x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.parse_and_get_item';
4888 END;
4889 END LOOP;
4890 END IF;
4891 x_item_tbl := l_item_output_tbl;
4892 EXCEPTION
4893 WHEN OTHERS THEN
4894 x_return_status := FND_API.g_ret_sts_unexp_error;
4895 x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.parse_and_get_item';
4896 END parse_and_get_item;
4897
4898 /************************************************************************************
4899 -- API name : get_item_attributes_list
4900 -- Type : Public For Internal Use Only
4901 -- Function :
4902 ************************************************************************************/
4903 PROCEDURE get_item_attributes_list(
4904 p_name_value_list IN inv_ebi_name_value_tbl
4905 ,p_prog_id IN NUMBER
4906 ,p_appl_id IN NUMBER
4907 ,p_cross_reference_type IN VARCHAR2
4908 ,x_items OUT NOCOPY inv_ebi_get_opr_attrs_tbl
4909 ,x_return_status OUT NOCOPY VARCHAR2
4910 ,x_msg_count OUT NOCOPY NUMBER
4911 ,x_msg_data OUT NOCOPY VARCHAR2
4912 )
4913 IS
4914 l_return_status VARCHAR2(2);
4915 l_item_string VARCHAR2(32000);
4916 l_org_string VARCHAR2(32000);
4917 l_from_date DATE := NULL;
4918 l_to_date DATE := NULL;
4919 l_from_date_str VARCHAR2(30);
4920 l_to_date_str VARCHAR2(30);
4921 l_last_x_hrs NUMBER;
4922 l_item_org_output_tbl inv_ebi_get_opr_attrs_tbl;
4923 l_item_tbl inv_ebi_get_opr_attrs_tbl;
4924 l_item_output_tbl inv_ebi_get_opr_attrs_tbl;
4925 l_item_tbl_flst inv_ebi_get_opr_attrs_tbl;
4926 l_msg_data VARCHAR2(2000);
4927 l_msg_count NUMBER;
4928 CURSOR c_get_item_list_pim IS
4929 SELECT inv_ebi_get_operational_attrs(item_pim.inventory_item_id,item_pim.concatenated_segments,item_pim.organization_id,item_pim.organization_code,NULL,NULL)
4930 FROM
4931 (SELECT mcr.inventory_item_id, msik.concatenated_segments, mcr.organization_id, mp.organization_code
4932 FROM mtl_cross_references_b mcr, mtl_system_items_b msi, mtl_parameters mp, mtl_system_items_kfv msik
4933 WHERE mcr.inventory_item_id = msi.inventory_item_id
4934 AND mcr.organization_id = msi.organization_id
4935 AND mcr.cross_reference_type = p_cross_reference_type
4936 AND mcr.organization_id = mp.organization_id
4937 AND msik.organization_id = msi.organization_id
4938 AND msik.inventory_item_id = msi.inventory_item_id
4939 AND msi.last_update_date <> msi.creation_date
4940 AND msi.last_update_date >= l_from_date
4941 AND msi.last_update_date <= l_to_date
4942 UNION
4943 SELECT cic.inventory_item_id, msik.concatenated_segments, cic.organization_id, mp.organization_code
4944 FROM mtl_cross_references_b mcr, cst_item_costs cic,mtl_parameters mp, mtl_system_items_kfv msik
4945 WHERE mcr.inventory_item_id = cic.inventory_item_id
4946 AND mcr.organization_id = cic.organization_id
4947 AND mcr.organization_id = mp.organization_id
4948 AND msik.organization_id = cic.organization_id
4949 AND msik.inventory_item_id = cic.inventory_item_id
4950 AND cic.last_update_date <> cic.creation_date
4951 AND mcr.cross_reference_type = p_cross_reference_type
4952 AND cic.last_update_date >= l_from_date
4953 AND cic.last_update_date <= l_to_date
4954 UNION
4955 SELECT cql.inventory_item_id, msik.concatenated_segments, cql.organization_id, mp.organization_code
4956 FROM mtl_cross_references_b mcr, cst_quantity_layers cql, mtl_parameters mp, mtl_system_items_kfv msik
4957 WHERE mcr.inventory_item_id = cql.inventory_item_id
4958 AND mcr.organization_id = cql.organization_id
4959 AND mcr.organization_id = mp.organization_id
4960 AND mcr.cross_reference_type = p_cross_reference_type
4961 AND msik.organization_id = cql.organization_id
4962 AND msik.inventory_item_id = cql.inventory_item_id
4963 AND cql.last_update_date >= l_from_date
4964 AND cql.last_update_date <= l_to_date) item_pim;
4965
4966 CURSOR c_get_item_list IS
4967 SELECT inv_ebi_get_operational_attrs(item_npim.inventory_item_id,item_npim.concatenated_segments,item_npim.organization_id,item_npim.organization_code,NULL,NULL)
4968 FROM
4969 (SELECT msi.inventory_item_id, msik.concatenated_segments, msi.organization_id, mp.organization_code
4970 FROM mtl_system_items_b msi, mtl_parameters mp, mtl_system_items_kfv msik
4971 WHERE msi.organization_id = mp.organization_id
4972 AND msik.organization_id = msi.organization_id
4973 AND msik.inventory_item_id = msi.inventory_item_id
4974 AND msi.last_update_date <> msi.creation_date
4975 AND msi.last_update_date >= l_from_date AND msi.last_update_date <= l_to_date
4976 UNION
4977 SELECT cic.inventory_item_id, msik.concatenated_segments, cic.organization_id, mp.organization_code
4978 FROM cst_item_costs cic, mtl_parameters mp, mtl_system_items_kfv msik
4979 WHERE cic.organization_id = mp.organization_id
4980 AND msik.organization_id = cic.organization_id
4981 AND msik.inventory_item_id = cic.inventory_item_id
4982 AND cic.last_update_date <> cic.creation_date
4983 AND cic.last_update_date >= l_from_date
4984 AND cic.last_update_date <= l_to_date
4985 UNION
4986 SELECT cql.inventory_item_id, msik.concatenated_segments, cql.organization_id, mp.organization_code
4987 FROM cst_quantity_layers cql,mtl_parameters mp, mtl_system_items_kfv msik
4988 WHERE cql.organization_id=mp.organization_id
4989 AND msik.organization_id = cql.organization_id
4990 AND msik.inventory_item_id = cql.inventory_item_id
4991 AND cql.last_update_date >= l_from_date
4992 AND cql.last_update_date <= l_to_date
4993 ) item_npim;
4994
4995 CURSOR c_get_item_flist IS
4996 SELECT inv_ebi_get_operational_attrs(item_flst.item_id,item_flst.item_name,item_flst.organization_id,item_flst.organization_code,NULL,NULL)
4997 FROM (
4998 SELECT a.item_id,a.item_name, a.organization_id,a.organization_code
4999 FROM THE (SELECT CAST( l_item_output_tbl as inv_ebi_get_opr_attrs_tbl)
5000 FROM dual ) a
5001 INTERSECT
5002 SELECT b.item_id,b.item_name, b.organization_id,b.organization_code
5003 FROM THE (SELECT CAST( l_item_tbl as inv_ebi_get_opr_attrs_tbl)
5004 FROM dual ) b
5005 ) item_flst;
5006 BEGIN
5007 FND_MSG_PUB.initialize();
5008 x_return_status := FND_API.g_ret_sts_success;
5009
5010 --Getting the values for the parameters passed from CP
5011 IF (p_name_value_list IS NOT NULL AND p_name_value_list.COUNT > 0) THEN
5012 l_item_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Item Name');
5013 l_org_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Organization Code');
5014 l_from_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'From Date');
5015 l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
5016 l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
5017
5018 IF l_from_date_str IS NOT NULL THEN
5019 l_from_date := TO_DATE(l_from_date_str,'YYYY/MM/DD HH24:MI:SS');
5020 END IF;
5021
5022 IF l_to_date_str IS NOT NULL THEN
5023 l_to_date := TO_DATE(l_to_date_str,'YYYY/MM/DD HH24:MI:SS');
5024 END IF;
5025
5026 IF l_last_x_hrs IS NOT NULL THEN
5027 l_from_date := SYSDATE-( l_last_x_hrs/24);
5028 l_to_date := SYSDATE ;
5029 END IF;
5030 END IF;
5031
5032 -- If all the parameter values are null then fetch items that got updated
5033 -- from the last successfull completiopn date of CP
5034 IF (l_item_string IS NULL AND l_from_date IS NULL AND l_to_date IS NULL AND l_last_x_hrs IS NULL) THEN
5035 l_from_date :=INV_EBI_ITEM_HELPER.get_last_run_date( p_conc_prog_id => p_prog_id
5036 ,p_appl_id => p_appl_id
5037 );
5038 l_to_date := SYSDATE;
5039 END IF;
5040
5041 IF l_from_date IS NOT NULL AND l_to_date IS NULL THEN
5042 l_to_date := SYSDATE;
5043 END IF;
5044
5045 -- Get the valid combination of Items and Organizations
5046 IF ( l_item_string IS NOT NULL ) THEN
5047 parse_and_get_item( p_item_names => l_item_string
5048 ,p_org_codes => l_org_string
5049 ,x_item_tbl => l_item_output_tbl
5050 ,x_return_status => l_return_status
5051 ,x_msg_count => l_msg_count
5052 ,x_msg_data => l_msg_data);
5053 END IF;
5054
5055
5056 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
5057 x_return_status := l_return_status;
5058 IF l_msg_data IS NOT NULL THEN
5059 x_msg_data := l_msg_data;
5060 END IF;
5061 END IF;
5062
5063 x_items := l_item_output_tbl;
5064
5065 -- Filtering the Items that got updated within the given dates
5066 IF (l_from_date IS NOT NULL AND l_to_date IS NOT NULL) THEN
5067 IF (c_get_item_list%ISOPEN) THEN
5068 CLOSE c_get_item_list;
5069 END IF;
5070 OPEN c_get_item_list;
5071 FETCH c_get_item_list BULK COLLECT INTO l_item_tbl;
5072 CLOSE c_get_item_list;
5073 IF (l_item_string IS NOT NULL) THEN
5074 IF (c_get_item_flist%ISOPEN) THEN
5075 CLOSE c_get_item_flist;
5076 END IF;
5077 OPEN c_get_item_flist;
5078 FETCH c_get_item_flist BULK COLLECT INTO l_item_tbl_flst;
5079 CLOSE c_get_item_flist;
5080 x_items := l_item_tbl_flst;
5081 -- Filtering the Items that got updated in the specified time in the given Orgs
5082 ELSIF (l_org_string IS NOT NULL) THEN
5083 filter_items_based_on_org( p_org_codes => l_org_string
5084 ,p_item_tbl => l_item_tbl
5085 ,x_item_tbl => l_item_org_output_tbl
5086 ,x_return_status => l_return_status
5087 ,x_msg_count => l_msg_count
5088 ,x_msg_data => l_msg_data);
5089 IF (l_return_status = FND_API.g_ret_sts_success) THEN
5090 x_items := l_item_org_output_tbl;
5091 ELSE
5092 x_return_status := l_return_status;
5093 IF l_msg_data IS NOT NULL THEN
5094 x_msg_data := l_msg_data;
5095 END IF;
5096 END IF;
5097 ELSE
5098 x_items := l_item_tbl;
5099 END IF;
5100 END IF;
5101 IF x_items is NOT NULL AND x_items.COUNT > 0 then
5102 FOR i IN 1..x_items.COUNT
5103 LOOP
5104 get_Operating_unit
5105 (p_oranization_id => x_items(i).organization_id
5106 ,x_operating_unit => x_items(i).operating_unit
5107 ,x_ouid => x_items(i).operating_unit_id
5108 );
5109 END LOOP;
5110 END IF;
5111 EXCEPTION
5112 WHEN OTHERS THEN
5113 x_return_status := FND_API.g_ret_sts_unexp_error;
5114 x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.get_item_attributes_list';
5115 IF (c_get_item_flist%ISOPEN) THEN
5116 CLOSE c_get_item_flist;
5117 END IF;
5118 IF (c_get_item_list%ISOPEN) THEN
5119 CLOSE c_get_item_list;
5120 END IF;
5121 END get_item_attributes_list;
5122
5123 /************************************************************************************
5124 -- API name : get_item_balance_list
5125 -- Type : Public
5126 -- Function :
5127 ************************************************************************************/
5128 PROCEDURE get_item_balance_list(
5129 p_name_value_list IN inv_ebi_name_value_tbl
5130 ,p_prog_id IN NUMBER
5131 ,p_appl_id IN NUMBER
5132 ,p_cross_reference_type IN VARCHAR2
5133 ,x_items OUT NOCOPY inv_ebi_get_opr_attrs_tbl
5134 ,x_return_status OUT NOCOPY VARCHAR2
5135 ,x_msg_count OUT NOCOPY NUMBER
5136 ,x_msg_data OUT NOCOPY VARCHAR2
5137 )
5138 IS
5139 l_return_status VARCHAR2(2);
5140 l_item_string VARCHAR2(32000);
5141 l_org_string VARCHAR2(32000);
5142 l_item_name VARCHAR2(40);
5143 l_org_code VARCHAR2(40);
5144 l_from_date DATE := NULL;
5145 l_to_date DATE := NULL;
5146 l_from_date_str VARCHAR2(30);
5147 l_to_date_str VARCHAR2(30);
5148 l_last_x_hrs NUMBER;
5149 l_item_tbl inv_ebi_get_opr_attrs_tbl;
5150 l_item_output_tbl inv_ebi_get_opr_attrs_tbl;
5151 l_item_org_output_tbl inv_ebi_get_opr_attrs_tbl;
5152 l_item_tbl_flst inv_ebi_get_opr_attrs_tbl;
5153 l_msg_data VARCHAR2(2000);
5154 l_msg_count NUMBER;
5155 CURSOR c_get_bal_item_lst_pim IS
5156 SELECT inv_ebi_get_operational_attrs(gibp.inventory_item_id,gibp.concatenated_segments, gibp.organization_id,gibp.organization_code,NULL,NULL)
5157 FROM (
5158 SELECT DISTINCT mcr.inventory_item_id,msik.concatenated_segments,mcr.organization_id,mp.organization_code
5159 FROM mtl_onhand_quantities_detail moq,mtl_cross_references_b mcr, mtl_parameters mp, mtl_system_items_kfv msik
5160 WHERE mcr.inventory_item_id = moq.inventory_item_id
5161 AND mcr.organization_id = moq.organization_id
5162 AND mcr.organization_id = mp.organization_id
5163 AND msik.organization_id = moq.organization_id
5164 AND msik.inventory_item_id = moq.inventory_item_id
5165 AND mcr.cross_reference_type = p_cross_reference_type
5166 AND moq.last_update_date >= l_from_date
5167 AND moq.last_update_date <= l_to_date) gibp;
5168
5169 CURSOR c_get_bal_item_lst IS
5170 SELECT inv_ebi_get_operational_attrs(gib.inventory_item_id,gib.concatenated_segments, gib.organization_id,gib.organization_code,NULL,NULL)
5171 FROM (SELECT DISTINCT moq.inventory_item_id,msik.concatenated_segments,moq.organization_id,mp.organization_code
5172 FROM mtl_onhand_quantities_detail moq, mtl_parameters mp, mtl_system_items_kfv msik
5173 WHERE moq.organization_id = mp.organization_id
5174 AND msik.organization_id = moq.organization_id
5175 AND msik.inventory_item_id = moq.inventory_item_id
5176 AND moq.last_update_date >= l_from_date
5177 AND moq.last_update_date <= l_to_date) gib;
5178
5179 CURSOR c_get_bal_item_flst IS
5180 SELECT inv_ebi_get_operational_attrs(gibf.item_id,gibf.item_name, gibf.organization_id,gibf.organization_code,NULL,NULL)
5181 FROM (
5182 SELECT a.item_id,a.item_name, a.organization_id,a.organization_code
5183 FROM THE (SELECT cast( l_item_output_tbl as inv_ebi_get_opr_attrs_tbl)
5184 FROM dual ) a
5185 INTERSECT
5186 SELECT b.item_id,b.item_name, b.organization_id,b.organization_code
5187 FROM THE (SELECT cast( l_item_tbl as inv_ebi_get_opr_attrs_tbl)
5188 FROM dual ) b ) gibf;
5189
5190 BEGIN
5191 FND_MSG_PUB.initialize();
5192 x_return_status := FND_API.g_ret_sts_success;
5193
5194 IF (p_name_value_list IS NOT NULL AND p_name_value_list.COUNT > 0) THEN
5195 l_item_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Item Name');
5196 l_org_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Organization Code');
5197 l_from_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'From Date');
5198 l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
5199 l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
5200
5201 IF l_from_date_str IS NOT NULL THEN
5202 l_from_date := TO_DATE(l_from_date_str,'YYYY/MM/DD HH24:MI:SS');
5203 END IF;
5204
5205 IF l_to_date_str IS NOT NULL THEN
5206 l_to_date := TO_DATE(l_to_date_str,'YYYY/MM/DD HH24:MI:SS');
5207 END IF;
5208
5209 IF l_last_x_hrs IS NOT NULL THEN
5210 l_from_date := SYSDATE-( l_last_x_hrs/24);
5211 l_to_date := SYSDATE ;
5212 END IF;
5213 END IF;
5214
5215 IF (l_item_string IS NULL AND l_from_date IS NULL AND l_to_date IS NULL AND l_last_x_hrs IS NULL) THEN
5216 l_from_date :=get_last_run_date( p_conc_prog_id => p_prog_id
5217 ,p_appl_id => p_appl_id
5218 );
5219 l_to_date := SYSDATE;
5220 END IF;
5221
5222 IF l_from_date IS NOT NULL AND l_to_date IS NULL THEN
5223 l_to_date := SYSDATE;
5224 END IF;
5225
5226 IF ( l_item_string IS NOT NULL ) THEN
5227 parse_and_get_item( p_item_names => l_item_string
5228 ,p_org_codes => l_org_string
5229 ,x_item_tbl => l_item_output_tbl
5230 ,x_return_status => l_return_status
5231 ,x_msg_count => l_msg_count
5232 ,x_msg_data => l_msg_data);
5233 END IF;
5234
5235 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
5236 x_return_status := l_return_status;
5237 IF l_msg_data IS NOT NULL THEN
5238 x_msg_data := l_msg_data;
5239 END IF;
5240 END IF;
5241
5242 x_items := l_item_output_tbl;
5243
5244 IF (l_from_date IS NOT NULL AND l_to_date IS NOT NULL) THEN
5245 IF (c_get_bal_item_lst%ISOPEN) THEN
5246 CLOSE c_get_bal_item_lst;
5247 END IF;
5248 OPEN c_get_bal_item_lst;
5249 FETCH c_get_bal_item_lst BULK COLLECT INTO l_item_tbl;
5250 CLOSE c_get_bal_item_lst;
5251 IF (l_item_string IS NOT NULL) THEN
5252 IF (c_get_bal_item_flst%ISOPEN) THEN
5253 CLOSE c_get_bal_item_flst;
5254 END IF;
5255 OPEN c_get_bal_item_flst;
5256 FETCH c_get_bal_item_flst BULK COLLECT INTO l_item_tbl_flst;
5257 CLOSE c_get_bal_item_flst;
5258 x_items := l_item_tbl_flst;
5259 ELSIF (l_org_string IS NOT NULL) THEN
5260 filter_items_based_on_org( p_org_codes => l_org_string
5261 ,p_item_tbl => l_item_tbl
5262 ,x_item_tbl => l_item_org_output_tbl
5263 ,x_return_status => l_return_status
5264 ,x_msg_count => l_msg_count
5265 ,x_msg_data => l_msg_data);
5266 IF (l_return_status = FND_API.g_ret_sts_success) THEN
5267 x_items := l_item_org_output_tbl;
5268 ELSE
5269 x_return_status := l_return_status;
5270 IF x_msg_data IS NOT NULL THEN
5271 x_msg_data := x_msg_data || l_msg_data;
5272 ELSE
5273 x_msg_data := l_msg_data;
5274 END IF;
5275 END IF;
5276 ELSE
5277 x_items := l_item_tbl;
5278 END IF;
5279 END IF;
5280 -- for Operating unit Pouplation
5281 IF x_items is NOT NULL AND x_items.COUNT > 0 then
5282 FOR i IN 1..x_items.COUNT
5283 LOOP
5284 get_Operating_unit
5285 (p_oranization_id => x_items(i).organization_id
5286 ,x_operating_unit => x_items(i).operating_unit
5287 ,x_ouid => x_items(i).operating_unit_id
5288 );
5289 END LOOP;
5290 END IF;
5291 EXCEPTION
5292 WHEN OTHERS THEN
5293 x_return_status := FND_API.g_ret_sts_unexp_error;
5294 x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.get_item_balance_list';
5295 IF (c_get_bal_item_flst%ISOPEN) THEN
5296 CLOSE c_get_bal_item_flst;
5297 END IF;
5298 IF (c_get_bal_item_lst%ISOPEN) THEN
5299 CLOSE c_get_bal_item_lst;
5300 END IF;
5301 END get_item_balance_list;
5302 /************************************************************************************
5303 -- API name : get_Operating_unit
5304 -- Type : Public
5305 -- Function :
5306 ************************************************************************************/
5307 PROCEDURE get_Operating_unit
5308 (p_oranization_id IN NUMBER
5309 ,x_operating_unit OUT NOCOPY VARCHAR2
5310 ,x_ouid OUT NOCOPY NUMBER
5311 )
5312 IS
5313 CURSOR c_operating_unit(cp_organization_id NUMBER)
5314 IS
5315 SELECT operating_unit,name
5316 FROM ORG_ORGANIZATION_DEFINITIONS orgdef,
5317 HR_OPERATING_UNITS hrou
5318 WHERE orgdef.organization_id = cp_organization_id
5319 AND hrou.organization_id=orgdef.operating_unit;
5320 BEGIN
5321 IF (c_operating_unit%ISOPEN) THEN
5322 CLOSE c_operating_unit;
5323 END IF;
5324 OPEN c_operating_unit(p_oranization_id);
5325 FETCH c_operating_unit into x_ouid,x_operating_unit;
5326 CLOSE c_operating_unit;
5327 EXCEPTION WHEN OTHERS
5328 THEN
5329 IF (c_operating_unit%ISOPEN) THEN
5330 CLOSE c_operating_unit;
5331 END IF;
5332 END get_Operating_unit;
5333
5334 /************************************************************************************
5335 -- API name : set_server_time_zone
5336 -- Type : Public
5337 -- Function :
5338 ************************************************************************************/
5339 PROCEDURE set_server_time_zone
5340 IS
5341 l_server_tz VARCHAR2(50);
5342 l_tzoffset VARCHAR2(10);
5343 BEGIN
5344
5345 INV_EBI_ITEM_HELPER.G_TIME_ZONE_OFFSET := NULL;
5346
5347 SELECT timezone_code
5348 INTO l_server_tz
5349 FROM fnd_timezones_b
5350 WHERE upgrade_tz_id = fnd_profile.value('SERVER_TIMEZONE_ID')
5351 AND UPPER(enabled_flag)='Y';
5352
5353 SELECT TZ_OFFSET(l_server_tz)
5354 INTO l_tzoffset
5355 FROM DUAL;
5356
5357 INV_EBI_ITEM_HELPER.G_TIME_ZONE_OFFSET := l_tzoffset;
5358
5359 EXCEPTION
5360 WHEN OTHERS THEN
5361 NULL;
5362 END set_server_time_zone;
5363 /************************************************************************************
5364 -- API name : convert_date_str
5365 -- Type : Public
5366 -- Function :
5367 ************************************************************************************/
5368 FUNCTION convert_date_str(p_datetime IN DATE)
5369 RETURN VARCHAR2
5370 IS
5371 l_ret_tz VARCHAR2(50);
5372 BEGIN
5373 IF(p_datetime IS NOT NULL AND p_datetime<>FND_API.G_MISS_DATE) THEN
5374 l_ret_tz := SUBSTR(TO_CHAR(p_datetime,'YYYY-MM-DD"T"HH24:MI:SS')||INV_EBI_ITEM_HELPER.G_TIME_ZONE_OFFSET,1,25);
5375 END IF;
5376 RETURN l_ret_tz;
5377 EXCEPTION
5378 WHEN OTHERS THEN
5379 NULL;
5380 END convert_date_str;
5381 END INV_EBI_ITEM_HELPER;