[Home] [Help]
1: PACKAGE BODY INV_EBI_ITEM_HELPER AS
2: /* $Header: INVEIHITB.pls 120.76.12020000.2 2012/07/19 05:09:40 smukka ship $ */
3:
4: /************************************************************************************
5: -- API name : set_nls
12: PROCEDURE set_nls(p_nls_language in VARCHAR2)
13: IS
14: PRAGMA AUTONOMOUS_TRANSACTION;
15: BEGIN
16: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.set_nls');
17: FND_GLOBAL.SET_NLS_CONTEXT(p_nls_language);
18: INV_EBI_UTIL.debug_line('STEP 20 :END INSIDE INV_EBI_ITEM_HELPER.set_nls');
19: commit;
20: END ;
14: PRAGMA AUTONOMOUS_TRANSACTION;
15: BEGIN
16: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.set_nls');
17: FND_GLOBAL.SET_NLS_CONTEXT(p_nls_language);
18: INV_EBI_UTIL.debug_line('STEP 20 :END INSIDE INV_EBI_ITEM_HELPER.set_nls');
19: commit;
20: END ;
21:
22: /************************************************************************************
72: -- Function :
73: ************************************************************************************/
74: FUNCTION id_col_value(
75: p_col_name IN VARCHAR2
76: ,p_pk_col_name_val_pairs IN INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl
77: ) RETURN VARCHAR2 IS
78: l_pkval varchar2(150);
79: BEGIN
80: IF ( (p_pk_col_name_val_pairs IS NOT NULL AND p_pk_col_name_val_pairs.COUNT >0)
97: -- Type : Public
98: -- Function :
99: ************************************************************************************/
100: FUNCTION value_to_id(
101: p_pk_col_name_val_pairs IN INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl
102: ,p_entity_name IN VARCHAR2
103: ) RETURN NUMBER IS
104: l_table_name VARCHAR2(30);
105: l_col_name VARCHAR2(30);
182: -- Type : Public
183: -- Function : returns the code equivalent of a given id
184: ************************************************************************************/
185: FUNCTION id_to_value(
186: p_pk_col_name_val_pairs IN INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl
187: ,p_entity_name IN VARCHAR2
188: ) RETURN VARCHAR2 IS
189: l_table_name VARCHAR2(30);
190: l_col_name VARCHAR2(30);
276: p_item IN inv_ebi_item_obj
277: ,x_out OUT NOCOPY inv_ebi_item_output_obj
278: ,x_item OUT NOCOPY inv_ebi_item_obj
279: ) IS
280: l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
281: l_output_status inv_ebi_output_status;
282: BEGIN
283: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.populate_item_ids');
284: x_item := p_item;
279: ) IS
280: l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
281: l_output_status inv_ebi_output_status;
282: BEGIN
283: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.populate_item_ids');
284: x_item := p_item;
285: l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
286: x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL,NULL);
287: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
283: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.populate_item_ids');
284: x_item := p_item;
285: l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
286: x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL,NULL);
287: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
288: IF ( (p_item.main_obj_type.organization_id IS NULL OR p_item.main_obj_type.organization_id = fnd_api.g_miss_num)
289: AND p_item.main_obj_type.organization_code IS NOT NULL ) THEN
290: l_pk_col_name_val_pairs.EXTEND(1);
291: l_pk_col_name_val_pairs(1).name := 'organization_code';
289: AND p_item.main_obj_type.organization_code IS NOT NULL ) THEN
290: l_pk_col_name_val_pairs.EXTEND(1);
291: l_pk_col_name_val_pairs(1).name := 'organization_code';
292: l_pk_col_name_val_pairs(1).value := p_Item.main_obj_type.organization_code;
293: x_item.main_obj_type.organization_id := INV_EBI_ITEM_HELPER.value_to_id(
294: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
295: ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
296: );
297: INV_EBI_UTIL.debug_line('STEP 20: ORG ID '|| x_item.main_obj_type.organization_id);
291: l_pk_col_name_val_pairs(1).name := 'organization_code';
292: l_pk_col_name_val_pairs(1).value := p_Item.main_obj_type.organization_code;
293: x_item.main_obj_type.organization_id := INV_EBI_ITEM_HELPER.value_to_id(
294: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
295: ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
296: );
297: INV_EBI_UTIL.debug_line('STEP 20: ORG ID '|| x_item.main_obj_type.organization_id);
298: l_pk_col_name_val_pairs.TRIM(1);
299: IF (x_item.main_obj_type.organization_id IS NULL) THEN
309: l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
310: l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.item_number;
311: l_pk_col_name_val_pairs(2).name := 'organization_id';
312: l_pk_col_name_val_pairs(2).value := x_item.main_obj_type.organization_id;
313: x_item.main_obj_type.inventory_item_id := INV_EBI_ITEM_HELPER.value_to_id(
314: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
315: ,p_entity_name => INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
316: );
317: l_pk_col_name_val_pairs.TRIM(2);
311: l_pk_col_name_val_pairs(2).name := 'organization_id';
312: l_pk_col_name_val_pairs(2).value := x_item.main_obj_type.organization_id;
313: x_item.main_obj_type.inventory_item_id := INV_EBI_ITEM_HELPER.value_to_id(
314: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
315: ,p_entity_name => INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
316: );
317: l_pk_col_name_val_pairs.TRIM(2);
318: INV_EBI_UTIL.debug_line('STEP 30: INV ITEM ID '|| x_item.main_obj_type.inventory_item_id);
319: END IF;
321: AND p_item.main_obj_type.template_name IS NOT NULL AND p_item.main_obj_type.template_name <> fnd_api.g_miss_char) THEN
322: l_pk_col_name_val_pairs.EXTEND(1);
323: l_pk_col_name_val_pairs(1).name := 'template_name';
324: l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.template_name;
325: x_item.main_obj_type.template_id := INV_EBI_ITEM_HELPER.value_to_id(
326: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
327: ,p_entity_name => INV_EBI_ITEM_HELPER.G_TEMPLATE
328: );
329: INV_EBI_UTIL.debug_line('STEP 40: TEMPLATE ID '|| x_item.main_obj_type.template_id);
323: l_pk_col_name_val_pairs(1).name := 'template_name';
324: l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.template_name;
325: x_item.main_obj_type.template_id := INV_EBI_ITEM_HELPER.value_to_id(
326: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
327: ,p_entity_name => INV_EBI_ITEM_HELPER.G_TEMPLATE
328: );
329: INV_EBI_UTIL.debug_line('STEP 40: TEMPLATE ID '|| x_item.main_obj_type.template_id);
330: l_pk_col_name_val_pairs.TRIM(1);
331:
340: 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
341: l_pk_col_name_val_pairs.EXTEND(1);
342: l_pk_col_name_val_pairs(1).name := 'organization_code';
343: l_pk_col_name_val_pairs(1).value := p_item.org_id_obj_type(i).org_code;
344: x_item.org_id_obj_type(i).org_id := INV_EBI_ITEM_HELPER.value_to_id(
345: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
346: ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
347: );
348: INV_EBI_UTIL.debug_line('STEP 50: ORG ID '|| x_item.org_id_obj_type(i).org_id);
342: l_pk_col_name_val_pairs(1).name := 'organization_code';
343: l_pk_col_name_val_pairs(1).value := p_item.org_id_obj_type(i).org_code;
344: x_item.org_id_obj_type(i).org_id := INV_EBI_ITEM_HELPER.value_to_id(
345: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
346: ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
347: );
348: INV_EBI_UTIL.debug_line('STEP 50: ORG ID '|| x_item.org_id_obj_type(i).org_id);
349: l_pk_col_name_val_pairs.TRIM(1);
350: IF (x_item.org_id_obj_type(i).org_id IS NULL) THEN
361: AND p_item.main_obj_type.item_catalog_group_code <> fnd_api.g_miss_char) THEN
362: l_pk_col_name_val_pairs.EXTEND(1);
363: l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
364: l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.item_catalog_group_code;
365: x_item.main_obj_type.item_catalog_group_id := INV_EBI_ITEM_HELPER.value_to_id(
366: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
367: ,p_entity_name => INV_EBI_ITEM_HELPER.G_ITEM_CATALOG_GROUP
368: );
369: INV_EBI_UTIL.debug_line('STEP 60: CATALOG GROUP ID '|| x_item.main_obj_type.item_catalog_group_id);
363: l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
364: l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.item_catalog_group_code;
365: x_item.main_obj_type.item_catalog_group_id := INV_EBI_ITEM_HELPER.value_to_id(
366: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
367: ,p_entity_name => INV_EBI_ITEM_HELPER.G_ITEM_CATALOG_GROUP
368: );
369: INV_EBI_UTIL.debug_line('STEP 60: CATALOG GROUP ID '|| x_item.main_obj_type.item_catalog_group_id);
370: l_pk_col_name_val_pairs.TRIM(1);
371: IF (x_item.main_obj_type.item_catalog_group_id IS NULL ) THEN
379: AND p_item.main_obj_type.lifecycle_name IS NOT NULL) THEN
380: l_pk_col_name_val_pairs.EXTEND(1);
381: l_pk_col_name_val_pairs(1).name := 'name';
382: l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.lifecycle_name;
383: x_item.main_obj_type.lifecycle_id := INV_EBI_ITEM_HELPER.value_to_id (
384: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
385: ,p_entity_name => INV_EBI_ITEM_HELPER.G_LIFECYCLE
386: );
387: INV_EBI_UTIL.debug_line('STEP 70: LIFE CYCLE ID '|| x_item.main_obj_type.lifecycle_id);
381: l_pk_col_name_val_pairs(1).name := 'name';
382: l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.lifecycle_name;
383: x_item.main_obj_type.lifecycle_id := INV_EBI_ITEM_HELPER.value_to_id (
384: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
385: ,p_entity_name => INV_EBI_ITEM_HELPER.G_LIFECYCLE
386: );
387: INV_EBI_UTIL.debug_line('STEP 70: LIFE CYCLE ID '|| x_item.main_obj_type.lifecycle_id);
388: l_pk_col_name_val_pairs.TRIM(1);
389: END IF;
393: l_pk_col_name_val_pairs(1).name := 'name';
394: l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.current_phase_name;
395: l_pk_col_name_val_pairs(2).name := 'parent_structure_id';
396: l_pk_col_name_val_pairs(2).value := x_item.main_obj_type.lifecycle_id ;
397: x_item.main_obj_type.current_phase_id := INV_EBI_ITEM_HELPER.value_to_id(
398: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
399: ,p_entity_name => INV_EBI_ITEM_HELPER.G_CURRENT_PHASE
400: );
401: INV_EBI_UTIL.debug_line('STEP 80: CURRENT PHASE ID '|| x_item.main_obj_type.current_phase_id);
395: l_pk_col_name_val_pairs(2).name := 'parent_structure_id';
396: l_pk_col_name_val_pairs(2).value := x_item.main_obj_type.lifecycle_id ;
397: x_item.main_obj_type.current_phase_id := INV_EBI_ITEM_HELPER.value_to_id(
398: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
399: ,p_entity_name => INV_EBI_ITEM_HELPER.G_CURRENT_PHASE
400: );
401: INV_EBI_UTIL.debug_line('STEP 80: CURRENT PHASE ID '|| x_item.main_obj_type.current_phase_id);
402: l_pk_col_name_val_pairs.TRIM(2);
403: END IF;
408: l_pk_col_name_val_pairs(1).name := 'organization_id';
409: l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.organization_id;
410: l_pk_col_name_val_pairs(2).name := 'inventory_item_id';
411: l_pk_col_name_val_pairs(2).value := x_item.main_obj_type.inventory_item_id;
412: x_item.main_obj_type.revision_id := INV_EBI_ITEM_HELPER.value_to_id(
413: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
414: ,p_entity_name => INV_EBI_ITEM_HELPER.G_REVISION
415: );
416: INV_EBI_UTIL.debug_line('STEP 90: REVISION ID '|| x_item.main_obj_type.revision_id);
410: l_pk_col_name_val_pairs(2).name := 'inventory_item_id';
411: l_pk_col_name_val_pairs(2).value := x_item.main_obj_type.inventory_item_id;
412: x_item.main_obj_type.revision_id := INV_EBI_ITEM_HELPER.value_to_id(
413: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
414: ,p_entity_name => INV_EBI_ITEM_HELPER.G_REVISION
415: );
416: INV_EBI_UTIL.debug_line('STEP 90: REVISION ID '|| x_item.main_obj_type.revision_id);
417: l_pk_col_name_val_pairs.TRIM(2);
418: END IF;
420: AND p_item.purchasing_obj_type.hazard_class_code IS NOT NULL) THEN
421: l_pk_col_name_val_pairs.EXTEND(1);
422: l_pk_col_name_val_pairs(1).name := 'hazard_class';
423: l_pk_col_name_val_pairs(1).value := p_item.purchasing_obj_type.hazard_class_code ;
424: x_item.purchasing_obj_type.hazard_class_id := INV_EBI_ITEM_HELPER.value_to_id(
425: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
426: ,p_entity_name => INV_EBI_ITEM_HELPER.G_HAZARD_CLASS
427: );
428: INV_EBI_UTIL.debug_line('STEP 100: HAZARD CLASS ID '|| x_item.purchasing_obj_type.hazard_class_id);
422: l_pk_col_name_val_pairs(1).name := 'hazard_class';
423: l_pk_col_name_val_pairs(1).value := p_item.purchasing_obj_type.hazard_class_code ;
424: x_item.purchasing_obj_type.hazard_class_id := INV_EBI_ITEM_HELPER.value_to_id(
425: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
426: ,p_entity_name => INV_EBI_ITEM_HELPER.G_HAZARD_CLASS
427: );
428: INV_EBI_UTIL.debug_line('STEP 100: HAZARD CLASS ID '|| x_item.purchasing_obj_type.hazard_class_id);
429: l_pk_col_name_val_pairs.TRIM(1);
430: END IF;
432: AND p_item.purchasing_obj_type.asset_category_code IS NOT NULL ) THEN
433: l_pk_col_name_val_pairs.EXTEND(1);
434: l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
435: l_pk_col_name_val_pairs(1).value := p_item.purchasing_obj_type.asset_category_code;
436: x_item.purchasing_obj_type.asset_category_id := INV_EBI_ITEM_HELPER.value_to_id(
437: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
438: ,p_entity_name => INV_EBI_ITEM_HELPER.G_ASSET_CATEGORY
439: );
440: INV_EBI_UTIL.debug_line('STEP 110: ASSET CATEGORY ID '|| x_item.purchasing_obj_type.asset_category_id);
434: l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
435: l_pk_col_name_val_pairs(1).value := p_item.purchasing_obj_type.asset_category_code;
436: x_item.purchasing_obj_type.asset_category_id := INV_EBI_ITEM_HELPER.value_to_id(
437: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
438: ,p_entity_name => INV_EBI_ITEM_HELPER.G_ASSET_CATEGORY
439: );
440: INV_EBI_UTIL.debug_line('STEP 110: ASSET CATEGORY ID '|| x_item.purchasing_obj_type.asset_category_id);
441: l_pk_col_name_val_pairs.TRIM(1);
442: END IF;
446: l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
447: l_pk_col_name_val_pairs(1).value := p_item.bom_obj_type.base_item_number;
448: l_pk_col_name_val_pairs(2).name := 'organization_id';
449: l_pk_col_name_val_pairs(2).value := p_item.main_obj_type.organization_id;
450: x_item.bom_obj_type.base_item_id := INV_EBI_ITEM_HELPER.value_to_id(
451: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
452: ,p_entity_name => INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
453: );
454: INV_EBI_UTIL.debug_line('STEP 120: BASE ITEM ID '|| x_item.bom_obj_type.base_item_id);
448: l_pk_col_name_val_pairs(2).name := 'organization_id';
449: l_pk_col_name_val_pairs(2).value := p_item.main_obj_type.organization_id;
450: x_item.bom_obj_type.base_item_id := INV_EBI_ITEM_HELPER.value_to_id(
451: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
452: ,p_entity_name => INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
453: );
454: INV_EBI_UTIL.debug_line('STEP 120: BASE ITEM ID '|| x_item.bom_obj_type.base_item_id);
455: l_pk_col_name_val_pairs.TRIM(2);
456: END IF;
460: 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
461: l_pk_col_name_val_pairs.EXTEND(1);
462: l_pk_col_name_val_pairs(1).name := 'manufacturer_name';
463: l_pk_col_name_val_pairs(1).value := p_item.part_num_obj_tbl_type(i).manufacturer_name;
464: x_item.part_num_obj_tbl_type(i).manufacturer_id := INV_EBI_ITEM_HELPER.value_to_id (
465: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
466: ,p_entity_name => INV_EBI_ITEM_HELPER.G_MANUFACTURER
467: );
468: INV_EBI_UTIL.debug_line('STEP 130: MANUFACTURER NAME' || p_item.part_num_obj_tbl_type(i).manufacturer_name || 'MFG ID: ' || x_item.part_num_obj_tbl_type(i).manufacturer_id);
462: l_pk_col_name_val_pairs(1).name := 'manufacturer_name';
463: l_pk_col_name_val_pairs(1).value := p_item.part_num_obj_tbl_type(i).manufacturer_name;
464: x_item.part_num_obj_tbl_type(i).manufacturer_id := INV_EBI_ITEM_HELPER.value_to_id (
465: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
466: ,p_entity_name => INV_EBI_ITEM_HELPER.G_MANUFACTURER
467: );
468: INV_EBI_UTIL.debug_line('STEP 130: MANUFACTURER NAME' || p_item.part_num_obj_tbl_type(i).manufacturer_name || 'MFG ID: ' || x_item.part_num_obj_tbl_type(i).manufacturer_id);
469: l_pk_col_name_val_pairs.TRIM(1);
470: END IF;
476: 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
477: l_pk_col_name_val_pairs.EXTEND(1);
478: l_pk_col_name_val_pairs(1).name := 'category_set_name';
479: l_pk_col_name_val_pairs(1).value := p_item.category_id_obj_tbl_type(i).cat_set_name;
480: x_item.category_id_obj_tbl_type(i).cat_set_id := INV_EBI_ITEM_HELPER.value_to_id(
481: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
482: ,p_entity_name => INV_EBI_ITEM_HELPER.G_CATEGORY_SET
483: );
484: INV_EBI_UTIL.debug_line('STEP 140: CATEGORY SET NAME: '|| p_item.category_id_obj_tbl_type(i).cat_set_name ||
478: l_pk_col_name_val_pairs(1).name := 'category_set_name';
479: l_pk_col_name_val_pairs(1).value := p_item.category_id_obj_tbl_type(i).cat_set_name;
480: x_item.category_id_obj_tbl_type(i).cat_set_id := INV_EBI_ITEM_HELPER.value_to_id(
481: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
482: ,p_entity_name => INV_EBI_ITEM_HELPER.G_CATEGORY_SET
483: );
484: INV_EBI_UTIL.debug_line('STEP 140: CATEGORY SET NAME: '|| p_item.category_id_obj_tbl_type(i).cat_set_name ||
485: 'CATEGORY SET ID: '|| x_item.category_id_obj_tbl_type(i).cat_set_id);
486: l_pk_col_name_val_pairs.TRIM(1);
491: l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
492: l_pk_col_name_val_pairs(1).value := p_item.category_id_obj_tbl_type(i).cat_name;
493: l_pk_col_name_val_pairs(2).name := 'category_set_name';
494: l_pk_col_name_val_pairs(2).value := p_item.category_id_obj_tbl_type(i).cat_set_name;
495: x_item.category_id_obj_tbl_type(i).cat_id := INV_EBI_ITEM_HELPER.value_to_id (
496: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
497: ,p_entity_name => INV_EBI_ITEM_HELPER.G_CATEGORY
498: );
499: INV_EBI_UTIL.debug_line('STEP 150: CATEGORY ID: '|| x_item.category_id_obj_tbl_type(i).cat_id);
493: l_pk_col_name_val_pairs(2).name := 'category_set_name';
494: l_pk_col_name_val_pairs(2).value := p_item.category_id_obj_tbl_type(i).cat_set_name;
495: x_item.category_id_obj_tbl_type(i).cat_id := INV_EBI_ITEM_HELPER.value_to_id (
496: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
497: ,p_entity_name => INV_EBI_ITEM_HELPER.G_CATEGORY
498: );
499: INV_EBI_UTIL.debug_line('STEP 150: CATEGORY ID: '|| x_item.category_id_obj_tbl_type(i).cat_id);
500: l_pk_col_name_val_pairs.TRIM(1);
501: END IF;
505: AND p_item.main_obj_type.rev_lifecycle_name IS NOT NULL) THEN
506: l_pk_col_name_val_pairs.EXTEND(1);
507: l_pk_col_name_val_pairs(1).name := 'name';
508: l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.rev_lifecycle_name;
509: x_item.main_obj_type.rev_lifecycle_id := INV_EBI_ITEM_HELPER.value_to_id(
510: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
511: ,p_entity_name => INV_EBI_ITEM_HELPER.G_LIFECYCLE
512: );
513: INV_EBI_UTIL.debug_line('STEP 160: REV LIFECYCLE ID: '|| x_item.main_obj_type.rev_lifecycle_id);
507: l_pk_col_name_val_pairs(1).name := 'name';
508: l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.rev_lifecycle_name;
509: x_item.main_obj_type.rev_lifecycle_id := INV_EBI_ITEM_HELPER.value_to_id(
510: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
511: ,p_entity_name => INV_EBI_ITEM_HELPER.G_LIFECYCLE
512: );
513: INV_EBI_UTIL.debug_line('STEP 160: REV LIFECYCLE ID: '|| x_item.main_obj_type.rev_lifecycle_id);
514: l_pk_col_name_val_pairs.TRIM(1);
515: END IF;
520: l_pk_col_name_val_pairs(1).name := 'name';
521: l_pk_col_name_val_pairs(1).value := p_item.main_obj_type.rev_current_phase_name;
522: l_pk_col_name_val_pairs(2).name := 'parent_structure_id';
523: l_pk_col_name_val_pairs(2).value := x_item.main_obj_type.rev_lifecycle_id;
524: x_item.main_obj_type.rev_current_phase_id := INV_EBI_ITEM_HELPER.value_to_id(
525: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
526: ,p_entity_name => INV_EBI_ITEM_HELPER.G_CURRENT_PHASE
527: );
528: INV_EBI_UTIL.debug_line('STEP 170: REV CURR PHASE ID: '|| x_item.main_obj_type.rev_current_phase_id);
522: l_pk_col_name_val_pairs(2).name := 'parent_structure_id';
523: l_pk_col_name_val_pairs(2).value := x_item.main_obj_type.rev_lifecycle_id;
524: x_item.main_obj_type.rev_current_phase_id := INV_EBI_ITEM_HELPER.value_to_id(
525: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
526: ,p_entity_name => INV_EBI_ITEM_HELPER.G_CURRENT_PHASE
527: );
528: INV_EBI_UTIL.debug_line('STEP 170: REV CURR PHASE ID: '|| x_item.main_obj_type.rev_current_phase_id);
529: l_pk_col_name_val_pairs.TRIM(2);
530: END IF;
527: );
528: INV_EBI_UTIL.debug_line('STEP 170: REV CURR PHASE ID: '|| x_item.main_obj_type.rev_current_phase_id);
529: l_pk_col_name_val_pairs.TRIM(2);
530: END IF;
531: INV_EBI_UTIL.debug_line('STEP 180: END INSIDE INV_EBI_ITEM_HELPER.populate_item_ids STATUS: '|| x_out.output_status.return_status);
532:
533: EXCEPTION
534: WHEN FND_API.g_exc_error THEN
535: x_out.output_status.return_status := FND_API.g_ret_sts_error;
1545: l_eng_item_flag VARCHAR2(3);
1546: l_output_status inv_ebi_output_status;
1547: l_count NUMBER := 0;
1548: l_master_org NUMBER;
1549: l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
1550: l_apply_template_update VARCHAR2(2000);
1551: l_validate_revised_itm_rev VARCHAR2(1);
1552: l_revised_item_exists NUMBER := 0;
1553: l_effectivity_date DATE;
1608:
1609: BEGIN
1610: SAVEPOINT inv_ebi_pvt_item_save_pnt;
1611: l_item := p_item;
1612: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.process_item_pvt ');
1613: l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
1614: x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL,NULL);
1615: l_item_number := l_item.main_obj_type.item_number;
1616: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
1612: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.process_item_pvt ');
1613: l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
1614: x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL,NULL);
1615: l_item_number := l_item.main_obj_type.item_number;
1616: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
1617: IF(l_item_number IS NULL OR l_item_number = fnd_api.g_miss_char) THEN
1618: l_item_number := get_item_num(
1619: p_segment1 => l_item.main_obj_type.segment1
1620: ,p_segment2 => l_item.main_obj_type.segment2
1670: ,p_item_number => l_item_number
1671: ) = FND_API.g_true )
1672: THEN
1673: IF( l_item.main_obj_type.inventory_item_id IS NULL) THEN
1674: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
1675: l_pk_col_name_val_pairs.EXTEND(2);
1676: l_pk_col_name_val_pairs(1).name := 'organization_id';
1677: l_pk_col_name_val_pairs(1).value := l_item.main_obj_type.organization_id;
1678: l_pk_col_name_val_pairs(2).name := 'concatenated_segments';
1676: l_pk_col_name_val_pairs(1).name := 'organization_id';
1677: l_pk_col_name_val_pairs(1).value := l_item.main_obj_type.organization_id;
1678: l_pk_col_name_val_pairs(2).name := 'concatenated_segments';
1679: l_pk_col_name_val_pairs(2).value := l_item.main_obj_type.item_number;
1680: l_item.main_obj_type.inventory_item_id := INV_EBI_ITEM_HELPER.value_to_id (
1681: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
1682: ,p_entity_name=> INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
1683: );
1684: END IF;
1678: l_pk_col_name_val_pairs(2).name := 'concatenated_segments';
1679: l_pk_col_name_val_pairs(2).value := l_item.main_obj_type.item_number;
1680: l_item.main_obj_type.inventory_item_id := INV_EBI_ITEM_HELPER.value_to_id (
1681: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
1682: ,p_entity_name=> INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
1683: );
1684: END IF;
1685:
1686: SELECT COUNT(1) into l_revised_item_exists
1995: l_item.main_obj_type.secondary_default_ind := fnd_api.g_miss_char;
1996: END IF;
1997: */
1998:
1999: INV_EBI_UTIL.debug_line('STEP 60:In INV_EBI_ITEM_HELPER.process_item_pvt,before calling EGO_ITEM_PUB.process_item');
2000: INV_EBI_UTIL.debug_line('STEP 70: Engineering_date before defaulting to fnd_api.g_miss_date ' || to_char(l_item.deprecated_obj_type.engineering_date,'YYYY/MM/DD HH24:MI:SS'));
2001: --OAAdapter is not setting engineering_date to fnd_api.g_miss_date on Cummins soa env.
2002: --as a temporary fix we are hardcoding the value.
2003: l_item.deprecated_obj_type.engineering_date := fnd_api.g_miss_date; -- added for bug 11884886
2523: INTO x_out.item_number,x_out.description
2524: FROM mtl_system_items_kfv
2525: WHERE organization_id = x_out.organization_id
2526: AND inventory_item_id = x_out.inventory_item_id;
2527: INV_EBI_UTIL.debug_line('STEP 140: BEFORE CALLING INV_EBI_ITEM_HELPER.get_Operating_unit ORG ID: '|| x_out.organization_id);
2528: get_Operating_unit
2529: (p_oranization_id => x_out.organization_id
2530: ,x_operating_unit => x_out.operating_unit
2531: ,x_ouid => x_out.operating_unit_id
2529: (p_oranization_id => x_out.organization_id
2530: ,x_operating_unit => x_out.operating_unit
2531: ,x_ouid => x_out.operating_unit_id
2532: );
2533: INV_EBI_UTIL.debug_line('STEP 150: AFTER CALLING INV_EBI_ITEM_HELPER.get_Operating_unit OPERATING UNIT: '|| x_out.operating_unit);
2534: BEGIN
2535: SELECT orig_system_id
2536: INTO l_source_system_id
2537: FROM HZ_ORIG_SYSTEMS_B
2604: END IF;
2605: IF FND_API.to_boolean( p_commit ) THEN
2606: COMMIT;
2607: END IF;
2608: INV_EBI_UTIL.debug_line('STEP 190: END INSIDE INV_EBI_ITEM_HELPER.process_item_pvt STATUS: '|| x_out.output_status.return_status);
2609: EXCEPTION
2610: WHEN FND_API.g_exc_error THEN
2611: ROLLBACK TO inv_ebi_pvt_item_save_pnt;
2612: x_out.output_status.return_status := FND_API.g_ret_sts_error;
2642: END IF;
2643:
2644: x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2645: IF (x_out.output_status.msg_data IS NOT NULL) THEN
2646: x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_ITEM_HELPER.process_item_pvt ';
2647: x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Item Number:' || l_item.main_obj_type.item_number || ' -> Org Code: ' || l_item.main_obj_type.organization_code;
2648: ELSE
2649: x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.process_item_pvt ';
2650: x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Item Number:' || l_item.main_obj_type.item_number || ' -> Org Code: ' || l_item.main_obj_type.organization_code;
2645: IF (x_out.output_status.msg_data IS NOT NULL) THEN
2646: x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_ITEM_HELPER.process_item_pvt ';
2647: x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Item Number:' || l_item.main_obj_type.item_number || ' -> Org Code: ' || l_item.main_obj_type.organization_code;
2648: ELSE
2649: x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.process_item_pvt ';
2650: x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Item Number:' || l_item.main_obj_type.item_number || ' -> Org Code: ' || l_item.main_obj_type.organization_code;
2651: END IF;
2652: END process_item_pvt;
2653: /************************************************************************************
2696: -- End of Bug 10378371
2697:
2698: BEGIN
2699: SAVEPOINT inv_ebi_item_uda_save_pnt;
2700: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.process_item_uda');
2701: INV_EBI_UTIL.debug_line('STEP 20: INVENTORY ITEM ID: '|| p_inventory_item_id || 'ORG ID: '||p_organization_id);
2702: l_uda_out := inv_ebi_uda_output_obj(NULL,NULL);
2703: l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
2704: x_uda_output_obj := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,l_uda_out,NULL,NULL,NULL,NULL,NULL);
2701: INV_EBI_UTIL.debug_line('STEP 20: INVENTORY ITEM ID: '|| p_inventory_item_id || 'ORG ID: '||p_organization_id);
2702: l_uda_out := inv_ebi_uda_output_obj(NULL,NULL);
2703: l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
2704: x_uda_output_obj := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,l_uda_out,NULL,NULL,NULL,NULL,NULL);
2705: INV_EBI_UTIL.debug_line('STEP 30: BEFORE CALLING INV_EBI_ITEM_HELPER.transform_uda');
2706: INV_EBI_UTIL.transform_uda (
2707: p_uda_input_obj => p_uda_input_obj
2708: ,x_attributes_row_table => l_attributes_row_table
2709: ,x_attributes_data_table => l_attributes_data_table
2710: ,x_return_status => x_uda_output_obj.output_status.return_status
2711: ,x_msg_count => x_uda_output_obj.output_status.msg_count
2712: ,x_msg_data => x_uda_output_obj.output_status.msg_data
2713: );
2714: INV_EBI_UTIL.debug_line('STEP 40: AFTER CALLING INV_EBI_ITEM_HELPER.transform_uda STATUS: '|| x_uda_output_obj.output_status.return_status);
2715: IF (x_uda_output_obj.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2716: RAISE FND_API.g_exc_unexpected_error;
2717: END IF;
2718:
2825: END IF;
2826: IF FND_API.to_boolean(p_commit) THEN
2827: COMMIT;
2828: END IF;
2829: INV_EBI_UTIL.debug_line('STEP 100: END INSIDE INV_EBI_ITEM_HELPER.process_item_uda');
2830: EXCEPTION
2831: WHEN FND_API.g_exc_error THEN
2832: ROLLBACK TO inv_ebi_item_uda_save_pnt;
2833: IF c_data_level%ISOPEN THEN
2860: CLOSE c_data_level;
2861: END IF;
2862: x_uda_output_obj.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2863: IF (x_uda_output_obj.output_status.msg_data IS NOT NULL) THEN
2864: x_uda_output_obj.output_status.msg_data := x_uda_output_obj.output_status.msg_data ||' -> INV_EBI_ITEM_HELPER.process_item_uda ';
2865: ELSE
2866: x_uda_output_obj.output_status.msg_data := SQLERRM||' at INV_EBI_ITEM_HELPER.process_item_uda ';
2867: END IF;
2868: END process_item_uda;
2862: x_uda_output_obj.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2863: IF (x_uda_output_obj.output_status.msg_data IS NOT NULL) THEN
2864: x_uda_output_obj.output_status.msg_data := x_uda_output_obj.output_status.msg_data ||' -> INV_EBI_ITEM_HELPER.process_item_uda ';
2865: ELSE
2866: x_uda_output_obj.output_status.msg_data := SQLERRM||' at INV_EBI_ITEM_HELPER.process_item_uda ';
2867: END IF;
2868: END process_item_uda;
2869: /************************************************************************************
2870: -- API name : process_org_id_assignments
2886: l_api_version NUMBER:=1.0;
2887: l_item_org_tbl_count NUMBER := 1;
2888: BEGIN
2889: SAVEPOINT inv_ebi_org_id_save_pnt;
2890: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.process_org_id_assignments ');
2891: INV_EBI_UTIL.debug_line('STEP 20: ITEM ID: '|| p_inventory_item_id || 'ITEM NUMBER: ' || p_item_number);
2892: l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
2893: x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL,NULL);
2894: IF p_org_id_tbl.COUNT > 0 THEN
2916: END IF;
2917: IF FND_API.to_boolean( p_commit ) THEN
2918: COMMIT;
2919: END IF;
2920: INV_EBI_UTIL.debug_line('STEP 60: END INSIDE INV_EBI_ITEM_HELPER.process_org_id_assignments ');
2921: EXCEPTION
2922: WHEN FND_API.g_exc_unexpected_error THEN
2923: ROLLBACK TO inv_ebi_org_id_save_pnt;
2924: x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2932: WHEN OTHERS THEN
2933: ROLLBACK TO inv_ebi_org_id_save_pnt;
2934: x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2935: IF (x_out.output_status.msg_data IS NOT NULL) THEN
2936: x_out.output_status.msg_data := x_out.output_status.msg_data||' -> INV_EBI_ITEM_HELPER.process_org_id_assignments ';
2937: ELSE
2938: x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.process_org_id_assignments ';
2939: END IF;
2940: END process_org_id_assignments;
2934: x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
2935: IF (x_out.output_status.msg_data IS NOT NULL) THEN
2936: x_out.output_status.msg_data := x_out.output_status.msg_data||' -> INV_EBI_ITEM_HELPER.process_org_id_assignments ';
2937: ELSE
2938: x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.process_org_id_assignments ';
2939: END IF;
2940: END process_org_id_assignments;
2941: /* Bug 9755814 - Added the below Functions for the Update Category issue */
2942: FUNCTION is_category_set_multi_assign (
3084: END IF;
3085: WHEN OTHERS THEN
3086: x_return_status := FND_API.g_ret_sts_unexp_error;
3087: IF (x_msg_data IS NOT NULL) THEN
3088: x_msg_data := x_msg_data ||' -> INV_EBI_ITEM_HELPER.update_category_assignments ';
3089: ELSE
3090: x_msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.update_category_assignments ';
3091: END IF;
3092: END update_category_assignments;
3086: x_return_status := FND_API.g_ret_sts_unexp_error;
3087: IF (x_msg_data IS NOT NULL) THEN
3088: x_msg_data := x_msg_data ||' -> INV_EBI_ITEM_HELPER.update_category_assignments ';
3089: ELSE
3090: x_msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.update_category_assignments ';
3091: END IF;
3092: END update_category_assignments;
3093:
3094:
3117: l_old_item_category NUMBER;
3118: l_no_multi_assign_cat_set_tbl FND_TABLE_OF_NUMBER;
3119: BEGIN
3120: SAVEPOINT inv_ebi_cat_id_save_pnt;
3121: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.process_category_assignments ');
3122: INV_EBI_UTIL.debug_line('STEP 20: ITEM ID: '|| p_inventory_item_id || 'ORG ID: '|| p_organization_id);
3123: l_category_output := inv_ebi_category_output_obj(NULL);
3124: l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
3125: x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,l_category_output,NULL,NULL,NULL,NULL);
3217: END LOOP;
3218: IF FND_API.to_boolean( p_commit ) THEN
3219: COMMIT;
3220: END IF;
3221: INV_EBI_UTIL.debug_line('STEP 60: END INSIDE INV_EBI_ITEM_HELPER.process_category_assignments ');
3222: EXCEPTION
3223: WHEN FND_API.g_exc_unexpected_error THEN
3224: ROLLBACK TO inv_ebi_cat_id_save_pnt;
3225: x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
3233: WHEN OTHERS THEN
3234: ROLLBACK TO inv_ebi_cat_id_save_pnt;
3235: x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
3236: IF (x_out.output_status.msg_data IS NOT NULL) THEN
3237: x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_ITEM_HELPER.process_category_assignments ';
3238: ELSE
3239: x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.process_category_assignments ';
3240: END IF;
3241: END process_category_assignments;
3235: x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
3236: IF (x_out.output_status.msg_data IS NOT NULL) THEN
3237: x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_ITEM_HELPER.process_category_assignments ';
3238: ELSE
3239: x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.process_category_assignments ';
3240: END IF;
3241: END process_category_assignments;
3242: /************************************************************************************
3243: -- API name : process_part_num_association
3313: AND organization_id = p_organization_id
3314: AND inventory_item_id = p_inventory_item_id;
3315: BEGIN
3316: SAVEPOINT inv_ebi_part_num_save_pnt;
3317: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.process_part_num_association ');
3318: l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
3319: x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL,NULL);
3320: IF p_mfg_part_obj.mfg_part_num IS NOT NULL THEN
3321: INV_EBI_UTIL.debug_line('STEP 20: ORGANIZATION ID: '|| p_organization_id || 'MFG PART NUMBER: '|| p_mfg_part_obj.mfg_part_num ||
3547: END IF;
3548: IF FND_API.to_boolean( p_commit ) THEN
3549: COMMIT;
3550: END IF;
3551: INV_EBI_UTIL.debug_line('STEP 130: END INSIDE INV_EBI_ITEM_HELPER.process_part_num_association STATUS: '|| x_out.output_status.return_status);
3552: EXCEPTION
3553: WHEN FND_API.g_exc_error THEN
3554: ROLLBACK TO inv_ebi_part_num_save_pnt;
3555: x_out.output_status.return_status := FND_API.g_ret_sts_error;
3575: WHEN OTHERS THEN
3576: ROLLBACK TO inv_ebi_part_num_save_pnt;
3577: x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
3578: IF (x_out.output_status.msg_data IS NOT NULL) THEN
3579: x_out.output_status.msg_data := x_out.output_status.msg_data||' -> INV_EBI_ITEM_HELPER.process_part_num_association ';
3580: x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Inventory Item Id:' || p_inventory_item_id || ' -> Org Id: ' || p_organization_id;
3581: ELSE
3582: x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.process_part_num_association ';
3583: x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Inventory Item Id:' || p_inventory_item_id || ' -> Org Id: ' || p_organization_id;
3578: IF (x_out.output_status.msg_data IS NOT NULL) THEN
3579: x_out.output_status.msg_data := x_out.output_status.msg_data||' -> INV_EBI_ITEM_HELPER.process_part_num_association ';
3580: x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Inventory Item Id:' || p_inventory_item_id || ' -> Org Id: ' || p_organization_id;
3581: ELSE
3582: x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.process_part_num_association ';
3583: x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Inventory Item Id:' || p_inventory_item_id || ' -> Org Id: ' || p_organization_id;
3584: END IF;
3585: END process_part_num_association;
3586:
3674: BEGIN
3675:
3676: SAVEPOINT inv_ebi_proc_upd_item_lc_pnt;
3677:
3678: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.process_update_item_lifecycle');
3679: l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
3680: x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL,NULL);
3681:
3682: IF(p_update_item_tbl IS NOT NULL AND p_update_item_tbl.COUNT > 0) THEN
3971: x_out.output_status.msg_data := INV_EBI_UTIL.get_error_table_msgtxt(x_out.output_status.error_table);
3972: END IF;
3973:
3974: IF (x_out.output_status.msg_data IS NOT NULL) THEN
3975: x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_ITEM_HELPER.process_update_item_lifecycle ';
3976: ELSE
3977: x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.process_update_item_lifecycle ';
3978: END IF;
3979: END process_update_item_lifecycle;
3973:
3974: IF (x_out.output_status.msg_data IS NOT NULL) THEN
3975: x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_ITEM_HELPER.process_update_item_lifecycle ';
3976: ELSE
3977: x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.process_update_item_lifecycle ';
3978: END IF;
3979: END process_update_item_lifecycle;
3980:
3981:
4005:
4006: l_mfg_part_num_obj inv_ebi_manufacturer_part_obj;
4007: l_master_org_id NUMBER;
4008: l_manufacturer_count NUMBER := 0;
4009: l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
4010: l_operating_unit VARCHAR2(240);
4011: l_operating_unit_id NUMBER;
4012: l_description VARCHAR2(240);
4013: l_ret VARCHAR2(1);
4014: BEGIN
4015: SAVEPOINT inv_ebi_sync_item_save_pnt;
4016: l_output_status := inv_ebi_output_status(fnd_api.g_ret_sts_success,NULL,NULL,NULL);
4017: x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL,NULL);
4018: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4019:
4020: --Create or update item
4021: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.sync_item ');
4022: INV_EBI_UTIL.debug_line('STEP 20: ORGANIZATION CODE: '|| p_item.main_obj_type.organization_code ||' Item Number: '|| p_item.main_obj_type.item_number);
4017: x_out := inv_ebi_item_output_obj(NULL,NULL,NULL,NULL,l_output_status,NULL,NULL,NULL,NULL,NULL,NULL);
4018: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4019:
4020: --Create or update item
4021: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.sync_item ');
4022: INV_EBI_UTIL.debug_line('STEP 20: ORGANIZATION CODE: '|| p_item.main_obj_type.organization_code ||' Item Number: '|| p_item.main_obj_type.item_number);
4023: INV_EBI_UTIL.debug_line('STEP 30: BEFORE CALLING INV_EBI_ITEM_HELPER.process_item_pvt ');
4024: process_item_pvt (
4025: p_item => p_item
4019:
4020: --Create or update item
4021: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.sync_item ');
4022: INV_EBI_UTIL.debug_line('STEP 20: ORGANIZATION CODE: '|| p_item.main_obj_type.organization_code ||' Item Number: '|| p_item.main_obj_type.item_number);
4023: INV_EBI_UTIL.debug_line('STEP 30: BEFORE CALLING INV_EBI_ITEM_HELPER.process_item_pvt ');
4024: process_item_pvt (
4025: p_item => p_item
4026: ,p_operation => p_operation
4027: ,p_commit => FND_API.g_false
4026: ,p_operation => p_operation
4027: ,p_commit => FND_API.g_false
4028: ,x_out => l_out
4029: );
4030: INV_EBI_UTIL.debug_line('STEP 40: AFTER CALLING INV_EBI_ITEM_HELPER.process_item_pvt STATUS: '|| l_out.output_status.return_status);
4031: IF (l_out.output_status.return_status <> fnd_api.g_ret_sts_success) THEN
4032: x_out.output_status.msg_data := l_out.output_status.msg_data;
4033: RAISE fnd_api.g_exc_unexpected_error;
4034: END IF;
4041: l_description := l_out.description;
4042:
4043: --Assign item to all the orgs sent in the list.
4044: IF (p_item.org_id_obj_type IS NOT NULL AND p_item.org_id_obj_type.COUNT > 0) THEN
4045: INV_EBI_UTIL.debug_line('STEP 50: BEFORE CALLING INV_EBI_ITEM_HELPER.process_org_id_assignments' );
4046: INV_EBI_UTIL.debug_line('STEP 60: INVENTORY ITEM ID: '|| l_inventory_item_id );
4047: process_org_id_assignments(
4048: p_init_msg_list => p_item.main_obj_type.init_msg_list
4049: ,p_commit => fnd_api.g_false
4051: ,p_item_number => p_item.main_obj_type.item_number
4052: ,p_org_id_tbl => p_item.org_id_obj_type
4053: ,x_out => l_out
4054: );
4055: INV_EBI_UTIL.debug_line('STEP 70: AFTER CALLING INV_EBI_ITEM_HELPER.process_org_id_assignments STATUS: '|| l_out.output_status.return_status);
4056: END IF;
4057: IF (l_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
4058: x_out.output_status.msg_data := l_out.output_status.msg_data;
4059: RAISE fnd_api.g_exc_unexpected_error;
4059: RAISE fnd_api.g_exc_unexpected_error;
4060: END IF;
4061: --Catalog Category assignment
4062: IF (p_item.category_id_obj_tbl_type IS NOT NULL AND p_item.category_id_obj_tbl_type.COUNT > 0) THEN
4063: INV_EBI_UTIL.debug_line('STEP 80: BEFORE CALLING INV_EBI_ITEM_HELPER.process_category_assignments ');
4064: process_category_assignments(
4065: p_api_version => l_api_version
4066: ,p_init_msg_list => p_item.main_obj_type.init_msg_list
4067: ,p_commit => fnd_api.g_false
4069: ,p_organization_id => p_item.main_obj_type.organization_id
4070: ,p_category_id_tbl => p_item.category_id_obj_tbl_type
4071: ,x_out => l_out
4072: );
4073: INV_EBI_UTIL.debug_line('STEP 90: AFTER CALLING INV_EBI_ITEM_HELPER.process_category_assignments STATUS: '|| l_out.output_status.return_status);
4074: END IF;
4075: IF (l_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
4076: x_out.output_status.msg_data := l_out.output_status.msg_data;
4077: RAISE fnd_api.g_exc_unexpected_error;
4092: FROM mtl_manufacturers
4093: WHERE manufacturer_id = l_mfg_part_num_obj.manufacturer_id;
4094: END IF;
4095: IF(l_manufacturer_count > 0 ) THEN
4096: INV_EBI_UTIL.debug_line('STEP 100: BEFORE CALLING INV_EBI_ITEM_HELPER.process_part_num_association');
4097: process_part_num_association(
4098: p_commit => FND_API.g_false
4099: ,p_organization_id => p_item.main_obj_type.organization_id
4100: ,p_inventory_item_id => l_inventory_item_id
4100: ,p_inventory_item_id => l_inventory_item_id
4101: ,p_mfg_part_obj => l_mfg_part_num_obj
4102: ,x_out => l_out
4103: );
4104: INV_EBI_UTIL.debug_line('STEP 110: AFTER CALLING INV_EBI_ITEM_HELPER.process_part_num_association STATUS: '|| l_out.output_status.return_status);
4105: ELSE
4106:
4107: --If manufcaturer does not exist raise exception
4108: FND_MESSAGE.set_name('INV','INV_EBI_MFG_NOT_EXIST');
4124: x_out.output_status.msg_data := l_out.output_status.msg_data ;
4125: RAISE FND_API.g_exc_unexpected_error;
4126: END IF;
4127: END LOOP;
4128: INV_EBI_UTIL.debug_line('STEP 120: END CALLING INV_EBI_ITEM_HELPER.process_part_num_association');
4129: END IF;
4130: END IF;
4131:
4132: IF ( p_item.uom_conversion_obj_tbl_type IS NOT NULL AND p_item.uom_conversion_obj_tbl_type.Count>0) THEN
4143:
4144: IF fnd_api.to_boolean(p_commit) THEN
4145: COMMIT;
4146: END IF;
4147: INV_EBI_UTIL.debug_line('STEP 130: END INSIDE INV_EBI_ITEM_HELPER.sync_item STATUS: '|| x_out.output_status.return_status);
4148: EXCEPTION
4149: WHEN FND_API.g_exc_error THEN
4150: ROLLBACK TO inv_ebi_sync_item_save_pnt;
4151: x_out.output_status.return_status := FND_API.g_ret_sts_error;
4169: WHEN OTHERS THEN
4170: ROLLBACK TO inv_ebi_sync_item_save_pnt;
4171: x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
4172: IF (x_out.output_status.msg_data IS NOT NULL) THEN
4173: x_out.output_status.msg_data := l_out.output_status.msg_data ||' -> INV_EBI_ITEM_HELPER.sync_item ';
4174: ELSE
4175: x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.sync_item ';
4176: END IF;
4177: END sync_item;
4171: x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
4172: IF (x_out.output_status.msg_data IS NOT NULL) THEN
4173: x_out.output_status.msg_data := l_out.output_status.msg_data ||' -> INV_EBI_ITEM_HELPER.sync_item ';
4174: ELSE
4175: x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.sync_item ';
4176: END IF;
4177: END sync_item;
4178: /************************************************************************************
4179: -- API name : get_item_balance
4214: l_sec_avail_to_transact NUMBER;
4215: l_sec_avail_to_reserve NUMBER;
4216: l_organization_code VARCHAR2(3000);
4217: l_item_name VARCHAR2(30);
4218: l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
4219: CURSOR c_locator_id(p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER,
4220: p_revision IN VARCHAR2, p_subinventory_code IN VARCHAR2
4221: )IS
4222: SELECT
4229: organization_id = p_organization_id AND
4230: (revision IS NOT NULL OR (revision = p_revision)) AND
4231: (subinventory_code IS NOT NULL OR (subinventory_code = p_subinventory_code));
4232: BEGIN
4233: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.get_item_balance');
4234: x_return_status := fnd_api.g_ret_sts_success;
4235: l_item_balance_output_tbl := inv_ebi_item_bal_output_tbl();
4236: Inv_Quantity_Tree_Pub.clear_quantity_cache;
4237: IF p_item_balance_input.item_input_table IS NOT NULL THEN
4311: ,l_sec_qty_suggested
4312: ,l_sec_avail_to_transact
4313: ,l_sec_avail_to_reserve
4314: );
4315: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4316: l_pk_col_name_val_pairs.EXTEND(1);
4317: l_pk_col_name_val_pairs(1).name := 'organization_id';
4318: l_pk_col_name_val_pairs(1).value := p_item_balance_input.item_input_table(i).organization_id;
4319: l_organization_code := INV_EBI_ITEM_HELPER.id_to_value (
4315: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4316: l_pk_col_name_val_pairs.EXTEND(1);
4317: l_pk_col_name_val_pairs(1).name := 'organization_id';
4318: l_pk_col_name_val_pairs(1).value := p_item_balance_input.item_input_table(i).organization_id;
4319: l_organization_code := INV_EBI_ITEM_HELPER.id_to_value (
4320: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4321: ,p_entity_name=> G_ORGANIZATION
4322: );
4323: INV_EBI_UTIL.debug_line('STEP 40: ORG CODE: '||l_organization_code);
4320: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4321: ,p_entity_name=> G_ORGANIZATION
4322: );
4323: INV_EBI_UTIL.debug_line('STEP 40: ORG CODE: '||l_organization_code);
4324: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
4325: l_pk_col_name_val_pairs.EXTEND(1);
4326: l_pk_col_name_val_pairs(1).name := 'organization_id';
4327: l_pk_col_name_val_pairs(1).value := p_item_balance_input.item_input_table(i).organization_id;
4328: l_pk_col_name_val_pairs.EXTEND(1);
4327: l_pk_col_name_val_pairs(1).value := p_item_balance_input.item_input_table(i).organization_id;
4328: l_pk_col_name_val_pairs.EXTEND(1);
4329: l_pk_col_name_val_pairs(2).name := 'inventory_item_id';
4330: l_pk_col_name_val_pairs(2).value := p_item_balance_input.item_input_table(i).inventory_item_id;
4331: l_item_name := INV_EBI_ITEM_HELPER.id_to_value(
4332: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
4333: ,p_entity_name=> G_INVENTORY_ITEM
4334: );
4335: INV_EBI_UTIL.debug_line('STEP 50: ITEMNAME: '||l_item_name);
4437: l_cur_index := l_cur_index + 1;
4438: END LOOP;
4439: END IF; -- IF p_item_balance_input.item_input_table(i).is_loc_info_required is true
4440: l_item_balance_output_tbl(i).item_balance_loc_tbl := l_item_balance_loc_tbl;
4441: INV_EBI_UTIL.debug_line('STEP 80: BEFORE CALLING INV_EBI_ITEM_HELPER.get_operating_unit');
4442: get_Operating_unit
4443: (p_oranization_id => p_item_balance_input.item_input_table(i).organization_id
4444: ,x_operating_unit => l_item_balance_output_tbl(i).operating_unit
4445: ,x_ouid => l_item_balance_output_tbl(i).operating_unit_id
4443: (p_oranization_id => p_item_balance_input.item_input_table(i).organization_id
4444: ,x_operating_unit => l_item_balance_output_tbl(i).operating_unit
4445: ,x_ouid => l_item_balance_output_tbl(i).operating_unit_id
4446: );
4447: INV_EBI_UTIL.debug_line('STEP 90: AFTER CALLING INV_EBI_ITEM_HELPER.get_operating_unit');
4448: EXCEPTION
4449: WHEN FND_API.g_exc_unexpected_error THEN
4450: x_return_status := FND_API.g_ret_sts_error;
4451: IF l_msg_data IS NULL THEN
4467: populate_err_msg(p_orgid => p_item_balance_input.item_input_table(i).organization_id
4468: ,p_invid => p_item_balance_input.item_input_table(i).inventory_item_id
4469: ,p_org_code => l_organization_code
4470: ,p_item_name => l_item_name
4471: ,p_part_err_msg => SQLERRM||'-> at inv_ebi_item_helper.get_item_balance'
4472: ,x_err_msg => x_msg_data
4473: );
4474: END;
4475:
4475:
4476: END LOOP;
4477: x_item_balance_output :=inv_ebi_item_bal_output_list(l_item_balance_output_tbl);
4478: END IF; --p_item_balance_input IS NOT NULL
4479: INV_EBI_UTIL.debug_line('STEP 100: AFTER CALLING INV_EBI_ITEM_HELPER.get_item_balance STATUS: '||x_return_status);
4480: EXCEPTION
4481: WHEN OTHERS THEN
4482: x_return_status := FND_API.g_ret_sts_error;
4483: IF (x_msg_data IS NOT NULL) THEN
4480: EXCEPTION
4481: WHEN OTHERS THEN
4482: x_return_status := FND_API.g_ret_sts_error;
4483: IF (x_msg_data IS NOT NULL) THEN
4484: x_msg_data := x_msg_data ||' -> at INV_EBI_ITEM_HELPER.get_item_balance';
4485: ELSE
4486: x_msg_data := SQLERRM||' at INV_EBI_ITEM_HELPER.get_item_balance ';
4487: END IF;
4488: END get_item_balance;
4482: x_return_status := FND_API.g_ret_sts_error;
4483: IF (x_msg_data IS NOT NULL) THEN
4484: x_msg_data := x_msg_data ||' -> at INV_EBI_ITEM_HELPER.get_item_balance';
4485: ELSE
4486: x_msg_data := SQLERRM||' at INV_EBI_ITEM_HELPER.get_item_balance ';
4487: END IF;
4488: END get_item_balance;
4489: /************************************************************************************
4490: -- API name : validate_get_item_request
4525: );
4526: WHEN OTHERS THEN
4527: x_status := FND_API.G_RET_STS_ERROR;
4528: IF (x_msg_data IS NOT NULL) THEN
4529: x_msg_data := x_msg_data||' -> INV_EBI_ITEM_HELPER.validate_get_item_request ';
4530: ELSE
4531: x_msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.validate_get_item_request ';
4532: END IF;
4533: END validate_get_item_request;
4527: x_status := FND_API.G_RET_STS_ERROR;
4528: IF (x_msg_data IS NOT NULL) THEN
4529: x_msg_data := x_msg_data||' -> INV_EBI_ITEM_HELPER.validate_get_item_request ';
4530: ELSE
4531: x_msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.validate_get_item_request ';
4532: END IF;
4533: END validate_get_item_request;
4534: /************************************************************************************
4535: -- API name : get_uda_attributes
4926: END IF;
4927:
4928: x_return_status := FND_API.g_ret_sts_unexp_error;
4929: IF (x_msg_data IS NOT NULL) THEN
4930: x_msg_data := x_msg_data||' ->INV_EBI_ITEM_HELPER.get_item_uda ';
4931: ELSE
4932: x_msg_data := SQLERRM||'INV_EBI_ITEM_HELPER.get_item_uda';
4933: END IF;
4934: END get_item_uda;
4928: x_return_status := FND_API.g_ret_sts_unexp_error;
4929: IF (x_msg_data IS NOT NULL) THEN
4930: x_msg_data := x_msg_data||' ->INV_EBI_ITEM_HELPER.get_item_uda ';
4931: ELSE
4932: x_msg_data := SQLERRM||'INV_EBI_ITEM_HELPER.get_item_uda';
4933: END IF;
4934: END get_item_uda;
4935: /************************************************************************************
4936: -- API name : get_item_attributes
4992: l_item_attr_tbl inv_ebi_item_attr_tbl;
4993: l_mfr_part_table inv_ebi_mfg_part_obj_tbl_type;
4994: l_return_status VARCHAR2(1);
4995: l_msg VARCHAR2(100);
4996: l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl;
4997: ctr NUMBER;
4998: l_error_code NUMBER;
4999: l_default_cost_group_id VARCHAR2(2000) :=NULL;
5000: l_default_cost_type_id VARCHAR2(2000) :=NULL;
5005: FROM mtl_mfg_part_numbers
5006: WHERE inventory_item_id = p_item_id AND organization_id=p_org_id;
5007:
5008: BEGIN
5009: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.get_item_attributes');
5010: l_item_attr_tbl := inv_ebi_item_attr_tbl();
5011: x_return_status := FND_API.G_RET_STS_SUCCESS;
5012: IF p_get_item_inp_obj.p_config_flags IS NULL THEN
5013: l_getitemoprattrs := FND_API.G_TRUE;
5149: l_item_web_opiton := inv_ebi_item_web_option_obj(NULL,NULL,NULL,NULL);
5150: l_item_asset_obj := inv_ebi_item_asset_obj(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
5151: l_item_process_obj := inv_ebi_item_processmfg_obj(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
5152: l_uda_obj := inv_ebi_uda_input_obj(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
5153: INV_EBI_UTIL.debug_line('STEP 20: BEFORE CALLING INV_EBI_ITEM_HELPER.validate_get_item_request');
5154: validate_get_item_request(
5155: p_get_opr_attrs_rec => p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i)
5156: ,x_status => l_return_status
5157: ,x_msg_count => l_item_attr_tbl(i).msg_count
5156: ,x_status => l_return_status
5157: ,x_msg_count => l_item_attr_tbl(i).msg_count
5158: ,x_msg_data => l_item_attr_tbl(i).msg_data
5159: );
5160: INV_EBI_UTIL.debug_line('STEP 30: AFTER CALLING INV_EBI_ITEM_HELPER.validate_get_item_request STATUS: ' ||l_return_status);
5161: l_org_id := p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).organization_id;
5162: l_item_id := p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).item_id;
5163: IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5164: RAISE FND_API.G_EXC_ERROR;
5163: IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5164: RAISE FND_API.G_EXC_ERROR;
5165: END IF;
5166: IF((l_org_id IS NULL) )THEN
5167: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5168: l_pk_col_name_val_pairs.EXTEND(1);
5169: l_pk_col_name_val_pairs(1).name := 'organization_code';
5170: l_pk_col_name_val_pairs(1).value := p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).organization_code;
5171: l_org_id := INV_EBI_ITEM_HELPER.value_to_id (
5167: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5168: l_pk_col_name_val_pairs.EXTEND(1);
5169: l_pk_col_name_val_pairs(1).name := 'organization_code';
5170: l_pk_col_name_val_pairs(1).value := p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).organization_code;
5171: l_org_id := INV_EBI_ITEM_HELPER.value_to_id (
5172: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5173: ,p_entity_name=> INV_EBI_ITEM_HELPER.G_ORGANIZATION
5174: );
5175: IF (l_org_id IS NULL) THEN
5169: l_pk_col_name_val_pairs(1).name := 'organization_code';
5170: l_pk_col_name_val_pairs(1).value := p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).organization_code;
5171: l_org_id := INV_EBI_ITEM_HELPER.value_to_id (
5172: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5173: ,p_entity_name=> INV_EBI_ITEM_HELPER.G_ORGANIZATION
5174: );
5175: IF (l_org_id IS NULL) THEN
5176: FND_MESSAGE.set_name('INV','INV_EBI_ORG_CODE_INVALID');
5177: FND_MESSAGE.set_token('COL_VALUE', p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).organization_code);
5179: RAISE FND_API.G_EXC_ERROR;
5180: END IF;
5181: END IF;
5182: IF( l_item_id IS NULL) THEN
5183: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5184: l_pk_col_name_val_pairs.EXTEND(2);
5185: l_pk_col_name_val_pairs(1).name := 'organization_id';
5186: l_pk_col_name_val_pairs(1).value := l_org_id;
5187: l_pk_col_name_val_pairs(2).name := 'concatenated_segments';
5185: l_pk_col_name_val_pairs(1).name := 'organization_id';
5186: l_pk_col_name_val_pairs(1).value := l_org_id;
5187: l_pk_col_name_val_pairs(2).name := 'concatenated_segments';
5188: l_pk_col_name_val_pairs(2).value := p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).item_name;
5189: l_item_id := INV_EBI_ITEM_HELPER.value_to_id (
5190: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5191: ,p_entity_name=> INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
5192: );
5193: IF (l_item_id IS NULL ) THEN
5187: l_pk_col_name_val_pairs(2).name := 'concatenated_segments';
5188: l_pk_col_name_val_pairs(2).value := p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).item_name;
5189: l_item_id := INV_EBI_ITEM_HELPER.value_to_id (
5190: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5191: ,p_entity_name=> INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
5192: );
5193: IF (l_item_id IS NULL ) THEN
5194: FND_MESSAGE.set_name('INV','INV_EBI_ITEM_INVALID');
5195: FND_MESSAGE.set_token('COL_VALUE', p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).item_name);
5786:
5787: IF ( (l_item_core_obj.revision_id IS NULL OR l_item_core_obj.revision_id= fnd_api.g_miss_num)
5788: AND (l_org_id IS NOT NULL AND l_item_id IS NOT NULL)
5789: ) THEN
5790: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5791: l_pk_col_name_val_pairs.EXTEND(2);
5792: l_pk_col_name_val_pairs(1).name := 'organization_id';
5793: l_pk_col_name_val_pairs(1).value := l_org_id;
5794: l_pk_col_name_val_pairs(2).name := 'inventory_item_id';
5792: l_pk_col_name_val_pairs(1).name := 'organization_id';
5793: l_pk_col_name_val_pairs(1).value := l_org_id;
5794: l_pk_col_name_val_pairs(2).name := 'inventory_item_id';
5795: l_pk_col_name_val_pairs(2).value := l_item_id;
5796: l_item_core_obj.revision_id := INV_EBI_ITEM_HELPER.value_to_id(
5797: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5798: ,p_entity_name => INV_EBI_ITEM_HELPER.G_REVISION
5799: );
5800: l_pk_col_name_val_pairs.TRIM(2);
5794: l_pk_col_name_val_pairs(2).name := 'inventory_item_id';
5795: l_pk_col_name_val_pairs(2).value := l_item_id;
5796: l_item_core_obj.revision_id := INV_EBI_ITEM_HELPER.value_to_id(
5797: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5798: ,p_entity_name => INV_EBI_ITEM_HELPER.G_REVISION
5799: );
5800: l_pk_col_name_val_pairs.TRIM(2);
5801: END IF;
5802: ctr := 1;
5806: ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
5807: l_mfr_part_table.extend();
5808: l_item_mfr_part_obj.manufacturer_id := c.manufacturer_id;
5809: l_item_mfr_part_obj.mfg_part_num := c.mfg_part_num;
5810: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5811: l_pk_col_name_val_pairs.EXTEND(1);
5812: l_pk_col_name_val_pairs(1).name := 'manufacturer_id';
5813: l_pk_col_name_val_pairs(1).value := l_item_mfr_part_obj.manufacturer_id;
5814: l_item_mfr_part_obj.manufacturer_name := INV_EBI_ITEM_HELPER.id_to_value (
5810: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5811: l_pk_col_name_val_pairs.EXTEND(1);
5812: l_pk_col_name_val_pairs(1).name := 'manufacturer_id';
5813: l_pk_col_name_val_pairs(1).value := l_item_mfr_part_obj.manufacturer_id;
5814: l_item_mfr_part_obj.manufacturer_name := INV_EBI_ITEM_HELPER.id_to_value (
5815: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5816: ,p_entity_name=> G_MANUFACTURER
5817: );
5818: INV_EBI_UTIL.debug_line('STEP 40: MFG NAME: '||l_item_mfr_part_obj.manufacturer_name);
5821: END LOOP;
5822:
5823: IF (INV_EBI_UTIL.is_pim_installed) THEN -- Bug 8369900 To check is_pim_installed for reverse flow
5824: --Bug 7240247 To get Item udas
5825: INV_EBI_UTIL.debug_line('STEP 50: BEFORE CALLING INV_EBI_ITEM_HELPER.get_item_uda');
5826: get_item_uda(
5827: p_inventory_item_id => l_item_core_obj.inventory_item_id,
5828: p_organization_id => l_item_core_obj.organization_id,
5829: p_item_classification_id => l_item_core_obj.item_catalog_group_id,
5832: x_return_status => l_return_status,
5833: x_msg_count => l_msg_count,
5834: x_msg_data => l_msg_data
5835: );
5836: INV_EBI_UTIL.debug_line('STEP 60: AFTER CALLING INV_EBI_ITEM_HELPER.get_item_uda STATUS: '||l_return_status);
5837: IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5838: l_item_attr_tbl(i).msg_data := l_msg_data;
5839: RAISE FND_API.G_EXC_ERROR;
5840: END IF;
5849: p_cost_type_id => l_default_cost_type_id
5850: );
5851:
5852: IF(l_item_core_obj.organization_id IS NOT NULL) THEN
5853: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5854: l_pk_col_name_val_pairs.EXTEND(1);
5855: l_pk_col_name_val_pairs(1).name := 'organization_id';
5856: l_pk_col_name_val_pairs(1).value := l_item_core_obj.organization_id;
5857: l_item_core_obj.organization_code := INV_EBI_ITEM_HELPER.id_to_value (
5853: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5854: l_pk_col_name_val_pairs.EXTEND(1);
5855: l_pk_col_name_val_pairs(1).name := 'organization_id';
5856: l_pk_col_name_val_pairs(1).value := l_item_core_obj.organization_id;
5857: l_item_core_obj.organization_code := INV_EBI_ITEM_HELPER.id_to_value (
5858: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5859: ,p_entity_name=> G_ORGANIZATION
5860: );
5861: INV_EBI_UTIL.debug_line('STEP 70: ORG CODE: '||l_item_core_obj.organization_code);
5861: INV_EBI_UTIL.debug_line('STEP 70: ORG CODE: '||l_item_core_obj.organization_code);
5862: END IF;
5863:
5864: IF(l_item_core_obj.template_id IS NOT NULL) THEN
5865: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5866: l_pk_col_name_val_pairs.EXTEND(1);
5867: l_pk_col_name_val_pairs(1).name := 'template_id';
5868: l_pk_col_name_val_pairs(1).value := l_item_core_obj.template_id;
5869: l_item_core_obj.template_name := INV_EBI_ITEM_HELPER.id_to_value(
5865: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5866: l_pk_col_name_val_pairs.EXTEND(1);
5867: l_pk_col_name_val_pairs(1).name := 'template_id';
5868: l_pk_col_name_val_pairs(1).value := l_item_core_obj.template_id;
5869: l_item_core_obj.template_name := INV_EBI_ITEM_HELPER.id_to_value(
5870: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5871: ,p_entity_name=> G_TEMPLATE
5872: );
5873: INV_EBI_UTIL.debug_line('STEP 80: TEMPLATE NAME: '||l_item_core_obj.template_name);
5873: INV_EBI_UTIL.debug_line('STEP 80: TEMPLATE NAME: '||l_item_core_obj.template_name);
5874: END IF;
5875:
5876: IF(l_item_core_obj.item_catalog_group_id IS NOT NULL) THEN
5877: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5878: l_pk_col_name_val_pairs.EXTEND(1);
5879: l_pk_col_name_val_pairs(1).name := 'item_catalog_group_id';
5880: l_pk_col_name_val_pairs(1).value := l_item_core_obj.item_catalog_group_id;
5881: l_item_core_obj.item_catalog_group_code := INV_EBI_ITEM_HELPER.id_to_value(
5877: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5878: l_pk_col_name_val_pairs.EXTEND(1);
5879: l_pk_col_name_val_pairs(1).name := 'item_catalog_group_id';
5880: l_pk_col_name_val_pairs(1).value := l_item_core_obj.item_catalog_group_id;
5881: l_item_core_obj.item_catalog_group_code := INV_EBI_ITEM_HELPER.id_to_value(
5882: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5883: ,p_entity_name=> G_ITEM_CATALOG_GROUP
5884: );
5885: INV_EBI_UTIL.debug_line('STEP 90: CATALOG GROUP CODE: '||l_item_core_obj.item_catalog_group_code);
5885: INV_EBI_UTIL.debug_line('STEP 90: CATALOG GROUP CODE: '||l_item_core_obj.item_catalog_group_code);
5886: END IF;
5887:
5888: IF(l_item_core_obj.lifecycle_id IS NOT NULL) THEN
5889: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5890: l_pk_col_name_val_pairs.EXTEND(1);
5891: l_pk_col_name_val_pairs(1).name := 'proj_element_id'; -- Column name not available project_element_id
5892: l_pk_col_name_val_pairs(1).value := l_item_core_obj.lifecycle_id;
5893: l_item_core_obj.lifecycle_name := INV_EBI_ITEM_HELPER.id_to_value(
5889: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5890: l_pk_col_name_val_pairs.EXTEND(1);
5891: l_pk_col_name_val_pairs(1).name := 'proj_element_id'; -- Column name not available project_element_id
5892: l_pk_col_name_val_pairs(1).value := l_item_core_obj.lifecycle_id;
5893: l_item_core_obj.lifecycle_name := INV_EBI_ITEM_HELPER.id_to_value(
5894: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5895: ,p_entity_name=> G_LIFECYCLE
5896: );
5897: INV_EBI_UTIL.debug_line('STEP 100: LIFE CYCLE NAME: '||l_item_core_obj.lifecycle_name);
5896: );
5897: INV_EBI_UTIL.debug_line('STEP 100: LIFE CYCLE NAME: '||l_item_core_obj.lifecycle_name);
5898: END IF;
5899: IF(l_item_core_obj.current_phase_id IS NOT NULL) THEN
5900: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5901: l_pk_col_name_val_pairs.EXTEND(1);
5902: l_pk_col_name_val_pairs(1).name := 'proj_element_id';
5903: l_pk_col_name_val_pairs(1).value := l_item_core_obj.current_phase_id;
5904: l_item_core_obj.current_phase_name := INV_EBI_ITEM_HELPER.id_to_value(
5900: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5901: l_pk_col_name_val_pairs.EXTEND(1);
5902: l_pk_col_name_val_pairs(1).name := 'proj_element_id';
5903: l_pk_col_name_val_pairs(1).value := l_item_core_obj.current_phase_id;
5904: l_item_core_obj.current_phase_name := INV_EBI_ITEM_HELPER.id_to_value(
5905: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5906: ,p_entity_name=> G_CURRENT_PHASE
5907: );
5908: END IF;
5906: ,p_entity_name=> G_CURRENT_PHASE
5907: );
5908: END IF;
5909: IF (l_item_core_obj.revision_id IS NOT NULL) THEN
5910: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5911: l_pk_col_name_val_pairs.EXTEND(1);
5912: l_pk_col_name_val_pairs(1).name := 'revision_id';
5913: l_pk_col_name_val_pairs(1).value := l_item_core_obj.revision_id ;
5914: l_item_core_obj.revision_code := INV_EBI_ITEM_HELPER.id_to_value(
5910: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5911: l_pk_col_name_val_pairs.EXTEND(1);
5912: l_pk_col_name_val_pairs(1).name := 'revision_id';
5913: l_pk_col_name_val_pairs(1).value := l_item_core_obj.revision_id ;
5914: l_item_core_obj.revision_code := INV_EBI_ITEM_HELPER.id_to_value(
5915: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5916: ,p_entity_name=> G_REVISION
5917: );
5918: INV_EBI_UTIL.debug_line('STEP 110: REVISION CODE: '||l_item_core_obj.revision_code);
5917: );
5918: INV_EBI_UTIL.debug_line('STEP 110: REVISION CODE: '||l_item_core_obj.revision_code);
5919: END IF;
5920: IF(l_item_pur_obj.hazard_class_id IS NOT NULL) THEN
5921: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5922: l_pk_col_name_val_pairs.EXTEND(1);
5923: l_pk_col_name_val_pairs(1).name := 'hazard_class_id';
5924: l_pk_col_name_val_pairs(1).value := l_item_pur_obj.hazard_class_id;
5925: l_item_pur_obj.hazard_class_code := INV_EBI_ITEM_HELPER.id_to_value(
5921: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5922: l_pk_col_name_val_pairs.EXTEND(1);
5923: l_pk_col_name_val_pairs(1).name := 'hazard_class_id';
5924: l_pk_col_name_val_pairs(1).value := l_item_pur_obj.hazard_class_id;
5925: l_item_pur_obj.hazard_class_code := INV_EBI_ITEM_HELPER.id_to_value(
5926: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5927: ,p_entity_name=> G_HAZARD_CLASS
5928: );
5929: END IF;
5927: ,p_entity_name=> G_HAZARD_CLASS
5928: );
5929: END IF;
5930: IF (l_item_pur_obj.asset_category_id IS NOT NULL) THEN
5931: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5932: l_pk_col_name_val_pairs.EXTEND(1);
5933: l_pk_col_name_val_pairs(1).name := 'category_id';
5934: l_pk_col_name_val_pairs(1).value := l_item_pur_obj.asset_category_id ;
5935: l_item_pur_obj.asset_category_code := INV_EBI_ITEM_HELPER.id_to_value(
5931: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5932: l_pk_col_name_val_pairs.EXTEND(1);
5933: l_pk_col_name_val_pairs(1).name := 'category_id';
5934: l_pk_col_name_val_pairs(1).value := l_item_pur_obj.asset_category_id ;
5935: l_item_pur_obj.asset_category_code := INV_EBI_ITEM_HELPER.id_to_value(
5936: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5937: ,p_entity_name=> G_ASSET_CATEGORY
5938: );
5939: END IF;
5937: ,p_entity_name=> G_ASSET_CATEGORY
5938: );
5939: END IF;
5940: IF (l_item_bom_obj.base_item_id IS NOT NULL) THEN
5941: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5942: l_pk_col_name_val_pairs.EXTEND(2);
5943: l_pk_col_name_val_pairs(1).name := 'inventory_item_id';
5944: l_pk_col_name_val_pairs(1).value := l_item_bom_obj.base_item_id;
5945: l_pk_col_name_val_pairs(2).name := 'organization_id';
5943: l_pk_col_name_val_pairs(1).name := 'inventory_item_id';
5944: l_pk_col_name_val_pairs(1).value := l_item_bom_obj.base_item_id;
5945: l_pk_col_name_val_pairs(2).name := 'organization_id';
5946: l_pk_col_name_val_pairs(2).value := l_item_core_obj.organization_id;
5947: l_item_bom_obj.base_item_number := INV_EBI_ITEM_HELPER.id_to_value(
5948: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5949: ,p_entity_name=> G_INVENTORY_ITEM
5950: );
5951: END IF;
5949: ,p_entity_name=> G_INVENTORY_ITEM
5950: );
5951: END IF;
5952: -- gets the item number
5953: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5954: l_pk_col_name_val_pairs.EXTEND(1);
5955: l_pk_col_name_val_pairs(1).name := 'organization_id';
5956: l_pk_col_name_val_pairs(1).value := l_item_core_obj.organization_id;
5957: l_pk_col_name_val_pairs.EXTEND(1);
5956: l_pk_col_name_val_pairs(1).value := l_item_core_obj.organization_id;
5957: l_pk_col_name_val_pairs.EXTEND(1);
5958: l_pk_col_name_val_pairs(2).name := 'inventory_item_id';
5959: l_pk_col_name_val_pairs(2).value := l_item_core_obj.inventory_item_id;
5960: l_item_core_obj.item_name := INV_EBI_ITEM_HELPER.id_to_value(
5961: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5962: ,p_entity_name=> G_INVENTORY_ITEM
5963: );
5964: SELECT MASTER_ORGANIZATION_ID INTO l_item_core_obj.MASTER_ORGANIZATION_ID
5963: );
5964: SELECT MASTER_ORGANIZATION_ID INTO l_item_core_obj.MASTER_ORGANIZATION_ID
5965: FROM mtl_parameters WHERE organization_id=l_org_id;
5966: -- converts the master org id to master org code
5967: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5968: l_pk_col_name_val_pairs.EXTEND(1);
5969: l_pk_col_name_val_pairs(1).name := 'organization_id';
5970: l_pk_col_name_val_pairs(1).value := l_item_core_obj.master_organization_id;
5971: l_item_core_obj.master_organization_code := INV_EBI_ITEM_HELPER.id_to_value(
5967: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
5968: l_pk_col_name_val_pairs.EXTEND(1);
5969: l_pk_col_name_val_pairs(1).name := 'organization_id';
5970: l_pk_col_name_val_pairs(1).value := l_item_core_obj.master_organization_id;
5971: l_item_core_obj.master_organization_code := INV_EBI_ITEM_HELPER.id_to_value(
5972: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5973: ,p_entity_name=> INV_EBI_ITEM_HELPER.G_ORGANIZATION
5974: );
5975: IF(NOT (l_getassetmgmtattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE )) THEN
5969: l_pk_col_name_val_pairs(1).name := 'organization_id';
5970: l_pk_col_name_val_pairs(1).value := l_item_core_obj.master_organization_id;
5971: l_item_core_obj.master_organization_code := INV_EBI_ITEM_HELPER.id_to_value(
5972: p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
5973: ,p_entity_name=> INV_EBI_ITEM_HELPER.G_ORGANIZATION
5974: );
5975: IF(NOT (l_getassetmgmtattrs = FND_API.G_TRUE) AND NOT(l_getitemoprattrs = FND_API.G_TRUE )) THEN
5976: l_item_asset_obj := NULL;
5977: END IF;
6079: populate_err_msg(p_orgid => l_org_id
6080: ,p_invid => l_item_id
6081: ,p_org_code => p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).organization_code
6082: ,p_item_name => p_get_item_inp_obj.p_get_opr_attrs_tbl_type(i).item_name
6083: ,p_part_err_msg => SQLERRM||' -> at inv_ebi_item_helper.get_item_attributes'
6084: ,x_err_msg => x_msg_data
6085: );
6086: END;
6087: END LOOP;
6085: );
6086: END;
6087: END LOOP;
6088: x_item_tbl_obj:=inv_ebi_item_attr_tbl_obj(l_item_attr_tbl);
6089: INV_EBI_UTIL.debug_line('STEP 120: END INSIDE INV_EBI_ITEM_HELPER.get_item_attributes STATUS: '||x_return_status);
6090: EXCEPTION
6091: WHEN OTHERS THEN
6092: x_return_status := FND_API.G_RET_STS_ERROR;
6093: IF (x_msg_data IS NOT NULL) THEN
6090: EXCEPTION
6091: WHEN OTHERS THEN
6092: x_return_status := FND_API.G_RET_STS_ERROR;
6093: IF (x_msg_data IS NOT NULL) THEN
6094: x_msg_data := x_msg_data ||' -> at INV_EBI_ITEM_HELPER.get_item_attributes';
6095: ELSE
6096: x_msg_data := SQLERRM||' at INV_EBI_ITEM_HELPER.get_item_attributes';
6097: END IF;
6098: END get_item_attributes;
6092: x_return_status := FND_API.G_RET_STS_ERROR;
6093: IF (x_msg_data IS NOT NULL) THEN
6094: x_msg_data := x_msg_data ||' -> at INV_EBI_ITEM_HELPER.get_item_attributes';
6095: ELSE
6096: x_msg_data := SQLERRM||' at INV_EBI_ITEM_HELPER.get_item_attributes';
6097: END IF;
6098: END get_item_attributes;
6099:
6100:
6218: l_item_output_tbl inv_ebi_get_opr_attrs_tbl;
6219: l_counter NUMBER:=0;
6220: BEGIN
6221: x_return_status := FND_API.g_ret_sts_success;
6222: INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.filter_items_based_on_org');
6223: l_item_output_tbl := inv_ebi_get_opr_attrs_tbl();
6224:
6225: IF p_org_codes IS NOT NULL THEN
6226: l_org_tbl := parse_input_string(p_org_codes);
6243: END LOOP;
6244: END IF;
6245:
6246: x_item_tbl := l_item_output_tbl;
6247: INV_EBI_UTIL.debug_line('STEP 40: END INSIDE INV_EBI_ITEM_HELPER.filter_items_based_on_org RETURN STATUS: '|| x_return_status);
6248: EXCEPTION
6249: WHEN OTHERS THEN
6250: x_return_status := FND_API.g_ret_sts_unexp_error;
6251: x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.filter_items_based_on_org';
6247: INV_EBI_UTIL.debug_line('STEP 40: END INSIDE INV_EBI_ITEM_HELPER.filter_items_based_on_org RETURN STATUS: '|| x_return_status);
6248: EXCEPTION
6249: WHEN OTHERS THEN
6250: x_return_status := FND_API.g_ret_sts_unexp_error;
6251: x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.filter_items_based_on_org';
6252: END filter_items_based_on_org;
6253:
6254: /************************************************************************************
6255: -- API name : parse_and_get_item
6277: l_item_tbl FND_TABLE_OF_VARCHAR2_255;
6278: l_org_tbl FND_TABLE_OF_VARCHAR2_255;
6279: l_valid_item_tbl FND_TABLE_OF_VARCHAR2_255;
6280: l_valid_org_tbl FND_TABLE_OF_VARCHAR2_255;
6281: l_pk_col_name_val_pairs INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl ;
6282: CURSOR c_get_all_orgs(p_item_name VARCHAR2) IS
6283: SELECT msik.inventory_item_id,msik.organization_id,mp.organization_code
6284: FROM mtl_system_items_kfv msik,mtl_parameters mp
6285: WHERE msik.concatenated_segments = p_item_name
6285: WHERE msik.concatenated_segments = p_item_name
6286: AND msik.organization_id = mp.organization_id;
6287: BEGIN
6288: x_return_status := FND_API.g_ret_sts_success;
6289: INV_EBI_UTIL.debug_line('STEP 10: START CALLING INSIDE INV_EBI_ITEM_HELPER.parse_and_get_item ');
6290: l_item_output_tbl := inv_ebi_get_opr_attrs_tbl();
6291: INV_EBI_UTIL.debug_line('STEP 20: ITEM NAMES '|| p_item_names);
6292: INV_EBI_UTIL.debug_line('STEP 30: ORG CODES '|| p_org_codes);
6293: IF p_item_names IS NOT NULL THEN
6325: );
6326: x_msg_data := x_msg_data || l_msg_data ||' , ' ;
6327: WHEN OTHERS THEN
6328: x_return_status := FND_API.g_ret_sts_error;
6329: x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.parse_and_get_item';
6330: END;
6331: END LOOP;
6332: END IF;
6333: IF l_org_tbl IS NOT NULL AND l_org_tbl.COUNT > 0 THEN
6358: );
6359: x_msg_data := x_msg_data || l_msg_data ||' , ' ;
6360: WHEN OTHERS THEN
6361: x_return_status := FND_API.g_ret_sts_error;
6362: x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.parse_and_get_item';
6363: END;
6364: END LOOP;
6365: END IF;
6366: IF l_valid_item_tbl IS NOT NULL AND l_valid_item_tbl.COUNT > 0 THEN
6369: FND_MSG_PUB.initialize();
6370: l_count :=0;
6371: IF l_valid_org_tbl IS NOT NULL AND l_valid_org_tbl.COUNT > 0 THEN
6372: FOR j in l_valid_org_tbl.FIRST..l_valid_org_tbl.LAST LOOP
6373: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
6374: l_pk_col_name_val_pairs.EXTEND();
6375: l_pk_col_name_val_pairs(1).name := 'organization_code';
6376: l_pk_col_name_val_pairs(1).value := l_valid_org_tbl(j);
6377: l_org_id := INV_EBI_ITEM_HELPER.value_to_id( p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
6373: l_pk_col_name_val_pairs := INV_EBI_ITEM_HELPER.inv_ebi_name_value_pair_tbl();
6374: l_pk_col_name_val_pairs.EXTEND();
6375: l_pk_col_name_val_pairs(1).name := 'organization_code';
6376: l_pk_col_name_val_pairs(1).value := l_valid_org_tbl(j);
6377: l_org_id := INV_EBI_ITEM_HELPER.value_to_id( p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
6378: ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
6379: );
6380: l_pk_col_name_val_pairs.TRIM(1);
6381: l_pk_col_name_val_pairs.EXTEND(2);
6374: l_pk_col_name_val_pairs.EXTEND();
6375: l_pk_col_name_val_pairs(1).name := 'organization_code';
6376: l_pk_col_name_val_pairs(1).value := l_valid_org_tbl(j);
6377: l_org_id := INV_EBI_ITEM_HELPER.value_to_id( p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
6378: ,p_entity_name => INV_EBI_ITEM_HELPER.G_ORGANIZATION
6379: );
6380: l_pk_col_name_val_pairs.TRIM(1);
6381: l_pk_col_name_val_pairs.EXTEND(2);
6382: l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
6382: l_pk_col_name_val_pairs(1).name := 'concatenated_segments';
6383: l_pk_col_name_val_pairs(1).value := l_valid_item_tbl(i);
6384: l_pk_col_name_val_pairs(2).name := 'organization_id';
6385: l_pk_col_name_val_pairs(2).value := l_org_id;
6386: l_item_id := INV_EBI_ITEM_HELPER.value_to_id( p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
6387: ,p_entity_name => INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
6388: );
6389: l_pk_col_name_val_pairs.TRIM(2);
6390:
6383: l_pk_col_name_val_pairs(1).value := l_valid_item_tbl(i);
6384: l_pk_col_name_val_pairs(2).name := 'organization_id';
6385: l_pk_col_name_val_pairs(2).value := l_org_id;
6386: l_item_id := INV_EBI_ITEM_HELPER.value_to_id( p_pk_col_name_val_pairs => l_pk_col_name_val_pairs
6387: ,p_entity_name => INV_EBI_ITEM_HELPER.G_INVENTORY_ITEM
6388: );
6389: l_pk_col_name_val_pairs.TRIM(2);
6390:
6391: IF l_item_id IS NOT NULL THEN
6422: );
6423: x_msg_data := x_msg_data || l_msg_data ;
6424: WHEN OTHERS THEN
6425: x_return_status := FND_API.g_ret_sts_unexp_error;
6426: x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.parse_and_get_item';
6427: END;
6428: END LOOP;
6429: END IF;
6430: x_item_tbl := l_item_output_tbl;
6435: INV_EBI_UTIL.debug_line('STEP 50: ORGANIZATION CODE FROM OUT TABLE '|| x_item_tbl(i).organization_code);
6436: INV_EBI_UTIL.debug_line('STEP 60: OPERATING UNIT FROM OUT TABLE '|| x_item_tbl(i).operating_unit);
6437: END LOOP;
6438: END IF;
6439: INV_EBI_UTIL.debug_line('STEP 70: END INSIDE INV_EBI_ITEM_HELPER.parse_and_get_item RETURN STATUS: '|| x_return_status);
6440: EXCEPTION
6441: WHEN OTHERS THEN
6442: x_return_status := FND_API.g_ret_sts_unexp_error;
6443: x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.parse_and_get_item';
6439: INV_EBI_UTIL.debug_line('STEP 70: END INSIDE INV_EBI_ITEM_HELPER.parse_and_get_item RETURN STATUS: '|| x_return_status);
6440: EXCEPTION
6441: WHEN OTHERS THEN
6442: x_return_status := FND_API.g_ret_sts_unexp_error;
6443: x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.parse_and_get_item';
6444: END parse_and_get_item;
6445:
6446: /************************************************************************************
6447: -- API name : parse_source_system_string
6586: ) item_flst;
6587: BEGIN
6588: FND_MSG_PUB.initialize();
6589: INV_EBI_UTIL.setup();
6590: INV_EBI_UTIL.debug_line('STEP 10: START CALLING INV_EBI_ITEM_HELPER.GET_ITEM_ATTRIBUTES_LIST');
6591: x_return_status := FND_API.g_ret_sts_success;
6592:
6593: IF (p_cross_reference_type IS NOT NULL AND p_cross_reference_type <> FND_API.G_MISS_CHAR) THEN
6594: l_parsed_tbl := parse_source_system_string(p_cross_reference_type);
6628:
6629: -- If all the parameter values are null then fetch items that got updated
6630: -- from the last successfull completiopn date of CP
6631: 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
6632: l_from_date :=INV_EBI_ITEM_HELPER.get_last_run_date( p_conc_prog_id => p_prog_id
6633: ,p_appl_id => p_appl_id
6634: );
6635: INV_EBI_UTIL.debug_line('STEP 110: FROM DATE IF ALL THE PARAMETERS ARE NULL '|| l_from_date);
6636: l_to_date := SYSDATE;
6727: INV_EBI_UTIL.debug_line('STEP 200: ITEM NAME '|| x_items(i).item_name ||' ORGANIZATION ID '|| x_items(i).organization_id || ' ORGANIZATION CODE '|| x_items(i).organization_code);
6728: END LOOP;
6729: END IF;
6730: IF x_items is NOT NULL AND x_items.COUNT > 0 then
6731: INV_EBI_UTIL.debug_line('STEP 210: BEFORE CALLING INV_EBI_ITEM_HELPER.get_Operating_unit');
6732: FOR i IN 1..x_items.COUNT
6733: LOOP
6734: get_Operating_unit
6735: (p_oranization_id => x_items(i).organization_id
6736: ,x_operating_unit => x_items(i).operating_unit
6737: ,x_ouid => x_items(i).operating_unit_id
6738: );
6739: END LOOP;
6740: INV_EBI_UTIL.debug_line('STEP 220: AFTER CALLING INV_EBI_ITEM_HELPER.get_Operating_unit');
6741: END IF;
6742: INV_EBI_UTIL.debug_line('STEP 230: END CALLING INV_EBI_ITEM_HELPER.get_item_attributes_list RETURN STATUS: '|| x_return_status);
6743: INV_EBI_UTIL.wrapup;
6744: EXCEPTION
6738: );
6739: END LOOP;
6740: INV_EBI_UTIL.debug_line('STEP 220: AFTER CALLING INV_EBI_ITEM_HELPER.get_Operating_unit');
6741: END IF;
6742: INV_EBI_UTIL.debug_line('STEP 230: END CALLING INV_EBI_ITEM_HELPER.get_item_attributes_list RETURN STATUS: '|| x_return_status);
6743: INV_EBI_UTIL.wrapup;
6744: EXCEPTION
6745: WHEN OTHERS THEN
6746: x_return_status := FND_API.g_ret_sts_unexp_error;
6743: INV_EBI_UTIL.wrapup;
6744: EXCEPTION
6745: WHEN OTHERS THEN
6746: x_return_status := FND_API.g_ret_sts_unexp_error;
6747: x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.get_item_attributes_list';
6748: IF (c_get_item_flist%ISOPEN) THEN
6749: CLOSE c_get_item_flist;
6750: END IF;
6751: IF (c_get_item_list%ISOPEN) THEN
6828:
6829: BEGIN
6830: FND_MSG_PUB.initialize();
6831: INV_EBI_UTIL.setup();
6832: INV_EBI_UTIL.debug_line('STEP 10: INSIDE INV_EBI_ITEM_HELPER.get_item_balance_list');
6833: x_return_status := FND_API.g_ret_sts_success;
6834:
6835: IF (p_cross_reference_type IS NOT NULL AND p_cross_reference_type <> FND_API.G_MISS_CHAR) THEN
6836: l_parsed_tbl := parse_source_system_string(p_cross_reference_type);
6974: );
6975: END LOOP;
6976: INV_EBI_UTIL.debug_line('STEP 220: AFTER CALLING GET OPERATING UNIT');
6977: END IF;
6978: INV_EBI_UTIL.debug_line('STEP 230: END CALLING INV_EBI_ITEM_HELPER.get_item_balance_list STATUS: '|| x_return_status);
6979: INV_EBI_UTIL.wrapup;
6980: EXCEPTION
6981: WHEN OTHERS THEN
6982: x_return_status := FND_API.g_ret_sts_unexp_error;
6979: INV_EBI_UTIL.wrapup;
6980: EXCEPTION
6981: WHEN OTHERS THEN
6982: x_return_status := FND_API.g_ret_sts_unexp_error;
6983: x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.get_item_balance_list';
6984: IF (c_get_bal_item_flst%ISOPEN) THEN
6985: CLOSE c_get_bal_item_flst;
6986: END IF;
6987: IF (c_get_bal_item_lst%ISOPEN) THEN
7033: l_server_tz VARCHAR2(50);
7034: l_tzoffset VARCHAR2(10);
7035: BEGIN
7036:
7037: INV_EBI_ITEM_HELPER.G_TIME_ZONE_OFFSET := NULL;
7038:
7039: SELECT timezone_code
7040: INTO l_server_tz
7041: FROM fnd_timezones_b
7045: SELECT TZ_OFFSET(l_server_tz)
7046: INTO l_tzoffset
7047: FROM DUAL;
7048:
7049: INV_EBI_ITEM_HELPER.G_TIME_ZONE_OFFSET := l_tzoffset;
7050:
7051: EXCEPTION
7052: WHEN OTHERS THEN
7053: NULL;
7062: IS
7063: l_ret_tz VARCHAR2(50);
7064: BEGIN
7065: IF(p_datetime IS NOT NULL AND p_datetime<>FND_API.G_MISS_DATE) THEN
7066: 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);
7067: END IF;
7068: RETURN l_ret_tz;
7069: EXCEPTION
7070: WHEN OTHERS THEN
7069: EXCEPTION
7070: WHEN OTHERS THEN
7071: NULL;
7072: END convert_date_str;
7073: END INV_EBI_ITEM_HELPER;