[Home] [Help]
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: /************************************************************************************
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();
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
300: FND_MESSAGE.set_name('INV','INV_EBI_ORG_CODE_INVALID');
301: FND_MESSAGE.set_token('COL_VALUE', p_Item.main_obj_type.organization_code);
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;
320: IF ( (p_item.main_obj_type.template_id IS NULL OR p_item.main_obj_type.template_id =fnd_api.g_miss_num)
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);
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:
332: IF (x_item.main_obj_type.template_id IS NULL) THEN
333: FND_MESSAGE.set_name('INV','INV_TEMPLATE_ERROR');
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
351: FND_MESSAGE.set_name('INV','INV_EBI_ORG_CODE_INVALID');
352: FND_MESSAGE.set_token('COL_VALUE', p_item.org_id_obj_type(i).org_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
372: FND_MESSAGE.set_name('INV','INV_EBI_ITEM_INVALID');
373: FND_MESSAGE.set_token('COL_VALUE',p_item.main_obj_type.item_catalog_group_code);
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;
390: 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)
391: AND (p_item.main_obj_type.current_phase_name IS NOT NULL AND x_item.main_obj_type.lifecycle_id is NOT NULL)) THEN
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;
404: IF ( (p_item.main_obj_type.revision_id IS NULL OR p_item.main_obj_type.revision_id= fnd_api.g_miss_num)
405: AND (x_item.main_obj_type.organization_id IS NOT NULL AND x_item.main_obj_type.inventory_item_id IS NOT NULL)
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;
419: 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)
420: AND p_item.purchasing_obj_type.hazard_class_code IS NOT NULL) THEN
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;
431: 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)
432: AND p_item.purchasing_obj_type.asset_category_code IS NOT NULL ) THEN
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;
443: 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)
444: AND p_item.bom_obj_type.base_item_number IS NOT NULL )THEN
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;
457: IF ( p_item.part_num_obj_tbl_type IS NOT NULL AND p_item.part_num_obj_tbl_type.COUNT > 0 ) THEN
458: FOR i in 1..p_item.part_num_obj_tbl_type.COUNT LOOP
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;
471: END LOOP;
472: END IF;
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);
487: END IF;
488: 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)
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;
502: END LOOP;
503: END IF;
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;
516: 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)
517: 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 )
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;
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:
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;
568: OPEN c_master_org;
569: LOOP
570: FETCH c_master_org INTO l_master_org;
571: IF (c_master_org%ROWCOUNT > 1) THEN
572: l_master_org_code := INV_EBI_UTIL.get_config_param_value(
573: p_config_tbl => p_config
574: ,p_config_param_name => 'Default_Master_Organization_For_Item'
575: );
576: l_master_org := get_organization_id(
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();
1658: l_item.main_obj_type.item_number := l_item_number;
1659: END IF;
1660: initialize_item(x_item => l_item);
1661:
1662: l_validate_revised_itm_rev := INV_EBI_UTIL.get_config_param_value(
1663: p_config_tbl => l_item.name_value_tbl
1664: ,p_config_param_name => 'VALIDATE_REVISED_ITEM_REVISION'
1665: );
1666: IF (l_validate_revised_itm_rev = fnd_api.g_true ) AND (l_item.main_obj_type.revision_code IS NOT NULL)
1706: END IF;
1707: END IF;
1708:
1709: -- Bug 9709331
1710: l_assign_def_temp_from_icc := INV_EBI_UTIL.get_config_param_value(
1711: p_config_tbl => l_item.name_value_tbl
1712: ,p_config_param_name => 'ASSIGN_DEFAULT_TEMPLATE_FROM_ICC'
1713: );
1714:
1744: l_item_desc_gen_method := get_desc_gen_method(l_item.main_obj_type.item_catalog_group_id);
1745: END IF;
1746:
1747: 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
1748: l_is_master_org := INV_EBI_UTIL.is_master_org(l_item.main_obj_type.organization_id);
1749:
1750: IF(l_is_master_org = fnd_api.g_true) THEN
1751: l_item.main_obj_type.description := l_item_number;
1752: ELSE
1749:
1750: IF(l_is_master_org = fnd_api.g_true) THEN
1751: l_item.main_obj_type.description := l_item_number;
1752: ELSE
1753: l_master_org := INV_EBI_UTIL.get_master_organization(
1754: p_organization_id => l_item.main_obj_type.organization_id
1755: );
1756: OPEN c_item_description(
1757: p_item_number => l_item_number
1840: END IF;
1841:
1842:
1843: IF(l_transaction_type = INV_EBI_ITEM_PUB.g_otype_update ) THEN
1844: l_apply_template_update := INV_EBI_UTIL.get_config_param_value(
1845: p_config_tbl => l_item.name_value_tbl
1846: ,p_config_param_name => 'TEMPLATE_FOR_ITEM_UPDATE_ALLOWED'
1847: );
1848: IF( UPPER(l_apply_template_update ) = fnd_api.g_false ) THEN
1864: l_effectivity_date := SYSDATE;
1865: ELSE
1866: l_effectivity_date := l_item.main_obj_type.effectivity_date;
1867: END IF;
1868: INV_EBI_UTIL.debug_line('STEP 20: BEFORE CALLING EGO_ITEM_PUB.Process_Item_Revision ');
1869: INV_EBI_UTIL.debug_line('STEP 30: ITEM NUMBER '|| l_item.main_obj_type.item_number || 'ORG CODE: '|| l_item.main_obj_type.organization_code);
1870: EGO_ITEM_PUB.Process_Item_Revision(
1871: p_api_version => 1.0
1872: ,p_init_msg_list => FND_API.g_false
1865: ELSE
1866: l_effectivity_date := l_item.main_obj_type.effectivity_date;
1867: END IF;
1868: INV_EBI_UTIL.debug_line('STEP 20: BEFORE CALLING EGO_ITEM_PUB.Process_Item_Revision ');
1869: INV_EBI_UTIL.debug_line('STEP 30: ITEM NUMBER '|| l_item.main_obj_type.item_number || 'ORG CODE: '|| l_item.main_obj_type.organization_code);
1870: EGO_ITEM_PUB.Process_Item_Revision(
1871: p_api_version => 1.0
1872: ,p_init_msg_list => FND_API.g_false
1873: ,p_commit => FND_API.g_false
1904: ,x_return_status => x_out.output_status.return_status
1905: ,x_msg_count => x_out.output_status.msg_count
1906: ,x_msg_data => x_out.output_status.msg_data
1907: );
1908: INV_EBI_UTIL.debug_line('STEP 40: AFTER CALLING EGO_ITEM_PUB.Process_Item_Revision STATUS: '|| x_out.output_status.return_status);
1909: IF (x_out.output_status.return_status NOT IN (FND_API.g_ret_sts_success,'0')) THEN
1910: 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;
1911: RAISE FND_API.g_exc_unexpected_error;
1912: END IF;
1985: --End of Bug 9688120
1986:
1987: END IF;
1988:
1989: INV_EBI_UTIL.debug_line('STEP 50: BEFORE CALLING EGO_ITEM_PUB.process_item');
1990:
1991: -- Following code is commented out for Bug 13618550 ,it may cause issue to PLM4P PIP
1992: /*
1993: IF( (l_item.main_obj_type.tracking_quantity_ind = 'P' OR l_item.main_obj_type.tracking_quantity_ind IS NULL OR l_item.main_obj_type.tracking_quantity_ind = fnd_api.g_miss_char) AND
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
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
2004: INV_EBI_UTIL.debug_line('STEP 80:Before calling EGO_ITEM_PUB.process_item after defaulting to fnd_api.g_miss_date ' || to_char(l_item.deprecated_obj_type.engineering_date,'YYYY/MM/DD HH24:MI:SS'));
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
2004: INV_EBI_UTIL.debug_line('STEP 80:Before calling EGO_ITEM_PUB.process_item after defaulting to fnd_api.g_miss_date ' || to_char(l_item.deprecated_obj_type.engineering_date,'YYYY/MM/DD HH24:MI:SS'));
2005: EGO_ITEM_PUB.process_item(
2006: p_api_version => 1.0
2007: ,p_init_msg_list => l_item.main_obj_type.init_msg_list
2008: ,p_commit => FND_API.g_false
2423: ,p_repair_program => l_item.mrp_obj_type.repair_program
2424: ,p_subcontracting_component => l_item.gplanning_obj_type.subcontracting_component
2425: ,p_outsourced_assembly => l_item.purchasing_obj_type.outsourced_assembly
2426: );
2427: INV_EBI_UTIL.debug_line('STEP 90: AFTER CALLING EGO_ITEM_PUB.process_item STATUS: '|| x_out.output_status.return_status);
2428: IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2429: 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;
2430: RAISE FND_API.g_exc_unexpected_error;
2431: END IF;
2453: l_nls_language := r_get_nls_language.nls_language;
2454: --FND_GLOBAL.SET_NLS_CONTEXT(p_nls_language => l_nls_language);Commented for bug14210113
2455:
2456: -- Added for bug 14210113
2457: INV_EBI_UTIL.debug_line('STEP 100: BEFORE CALLING set_nls for language ' || l_nls_language);
2458: set_nls(l_nls_language);
2459:
2460: INV_EBI_UTIL.debug_line('STEP 110: BEFORE CALLING EGO_ITEM_PUB.process_item IN UPDATE MODE');
2461: EGO_ITEM_PUB.process_item(
2456: -- Added for bug 14210113
2457: INV_EBI_UTIL.debug_line('STEP 100: BEFORE CALLING set_nls for language ' || l_nls_language);
2458: set_nls(l_nls_language);
2459:
2460: INV_EBI_UTIL.debug_line('STEP 110: BEFORE CALLING EGO_ITEM_PUB.process_item IN UPDATE MODE');
2461: EGO_ITEM_PUB.process_item(
2462: p_api_version => 1.0
2463: ,p_init_msg_list => l_item.main_obj_type.init_msg_list
2464: ,p_commit => FND_API.g_false
2474: ,x_msg_count => x_out.output_status.msg_count
2475: ,x_msg_data => x_out.output_status.msg_data
2476: );
2477:
2478: INV_EBI_UTIL.debug_line('STEP 110: AFTER CALLING EGO_ITEM_PUB.process_item IN UPDATE MODE: STATUS: '|| x_out.output_status.return_status);
2479:
2480: IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2481:
2482: INV_EBI_UTIL.debug_line('Error while updating Item ' || l_item.main_obj_type.item_number || ' description using language code :' || l_item.main_obj_type.item_desc_lang_obj_tbl_type(i).lang_code);
2478: INV_EBI_UTIL.debug_line('STEP 110: AFTER CALLING EGO_ITEM_PUB.process_item IN UPDATE MODE: STATUS: '|| x_out.output_status.return_status);
2479:
2480: IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2481:
2482: INV_EBI_UTIL.debug_line('Error while updating Item ' || l_item.main_obj_type.item_number || ' description using language code :' || l_item.main_obj_type.item_desc_lang_obj_tbl_type(i).lang_code);
2483: x_out.output_status.msg_data := x_out.output_status.msg_data || ' ->Item Number:' || l_item.main_obj_type.item_number || '-> Language code:' || l_item.main_obj_type.item_desc_lang_obj_tbl_type(i).lang_code;
2484:
2485: -- To set back the language to base language
2486:
2486:
2487: --FND_GLOBAL.SET_NLS_CONTEXT(p_nls_language => l_orig_nls_lang); Commented for bug14210113
2488:
2489: -- Added for bug 14210113
2490: INV_EBI_UTIL.debug_line('STEP 120: BEFORE CALLING set_nls for setting to original language in case of exception' || l_orig_nls_lang);
2491: set_nls(l_orig_nls_lang);
2492:
2493: RAISE FND_API.g_exc_unexpected_error;
2494:
2503:
2504: --FND_GLOBAL.SET_NLS_CONTEXT(p_nls_language => l_orig_nls_lang); --Commented for bug 14210113
2505:
2506: -- Added for bug 14210113
2507: INV_EBI_UTIL.debug_line('STEP 130: BEFORE CALLING set_nls for setting to original language in case of success' || l_orig_nls_lang);
2508: set_nls(l_orig_nls_lang);
2509:
2510: /*End of Bug 13370136 */ -- ramukher
2511:
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
2535: SELECT orig_system_id
2536: INTO l_source_system_id
2537: FROM HZ_ORIG_SYSTEMS_B
2538: WHERE orig_system = l_item.main_obj_type.cross_reference_type;
2539: INV_EBI_UTIL.debug_line('STEP 160: SOURCE SYSTEM ID: '|| l_source_system_id);
2540: EXCEPTION
2541: WHEN NO_DATA_FOUND THEN
2542: NULL;
2543: END;
2557: l_count:=0;
2558: END;
2559:
2560: IF(l_transaction_type = INV_EBI_ITEM_PUB.g_otype_create) AND l_count=0 THEN
2561: INV_EBI_UTIL.debug_line('STEP 170: BEFORE CALLING MTL_CROSS_REFERENCES_PKG.insert_row');
2562:
2563: MTL_CROSS_REFERENCES_PKG.insert_row(
2564: p_source_system_id => l_source_system_id,
2565: p_start_date_active => SYSDATE,
2599: p_program_application_id => NULL,
2600: p_program_id => NULL,
2601: p_program_update_date => SYSDATE,
2602: x_cross_reference_id => l_xref_id);
2603: INV_EBI_UTIL.debug_line('STEP 180: AFTER CALLING MTL_CROSS_REFERENCES_PKG.insert_row');
2604: END IF;
2605: IF FND_API.to_boolean( p_commit ) THEN
2606: COMMIT;
2607: END IF;
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;
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);
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);
2705: INV_EBI_UTIL.debug_line('STEP 30: BEFORE CALLING INV_EBI_ITEM_HELPER.transform_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);
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
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
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:
2730: p_item_catalog_group_id,
2731: l_attributes_row_table(i).attr_group_id
2732: )
2733: LOOP
2734: INV_EBI_UTIL.debug_line('STEP 50: INSIDE Cursor Loop');
2735: l_data_level := cur_data_level.data_level_int_name;
2736: INV_EBI_UTIL.debug_line('STEP 60: l_data_level'|| l_data_level);
2737:
2738: IF(l_data_level = INV_EBI_ITEM_PUB.G_DATA_LEVEL_ITEM) THEN
2732: )
2733: LOOP
2734: INV_EBI_UTIL.debug_line('STEP 50: INSIDE Cursor Loop');
2735: l_data_level := cur_data_level.data_level_int_name;
2736: INV_EBI_UTIL.debug_line('STEP 60: l_data_level'|| l_data_level);
2737:
2738: IF(l_data_level = INV_EBI_ITEM_PUB.G_DATA_LEVEL_ITEM) THEN
2739:
2740: l_is_item_level := fnd_api.g_true;
2770: l_transaction_type := ego_user_attrs_data_pvt.g_sync_mode;
2771: ELSE
2772: l_transaction_type := l_attributes_row_table(i).transaction_type;
2773: END IF;
2774: INV_EBI_UTIL.debug_line('STEP 70: BEFORE CALLING EGO_USER_ATTRS_DATA_PUB.build_attr_group_row_object');
2775:
2776: -- Start of Bug 10378371
2777: -- Only Item Rev level and Item level UDAs are supported by the PIP
2778: IF(l_is_item_level = fnd_api.g_true OR l_is_item_revision_level = fnd_api.g_true ) THEN
2794: l_attributes_row_table(i) := l_attributes_row_obj;
2795: END IF;
2796:
2797: END LOOP;
2798: INV_EBI_UTIL.debug_line('STEP 80: BEFORE CALLING EGO_ITEM_PUB.process_user_attrs_for_item ');
2799: EGO_ITEM_PUB.process_user_attrs_for_item(
2800: p_api_version => p_api_version
2801: ,p_inventory_item_id => p_inventory_item_id
2802: ,p_organization_id => p_organization_id
2817: ,x_errorcode => x_uda_output_obj.uda_output.errorcode
2818: ,x_msg_count => x_uda_output_obj.output_status.msg_count
2819: ,x_msg_data => x_uda_output_obj.output_status.msg_data
2820: );
2821: INV_EBI_UTIL.debug_line('STEP 90: AFETR CALLING EGO_ITEM_PUB.process_user_attrs_for_item STATUS: '||x_uda_output_obj.output_status.return_status);
2822: IF (x_uda_output_obj.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2823: x_uda_output_obj.output_status.msg_data := x_uda_output_obj.output_status.msg_data || ' -> Inventory Item Id:' || p_inventory_item_id || ' -> Org Id: ' || p_organization_id;
2824: RAISE FND_API.g_exc_unexpected_error;
2825: END IF;
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
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
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
2895: FOR i IN 1..p_org_id_tbl.COUNT LOOP
2896: l_item_org_assignment_rec.inventory_item_id := p_inventory_item_id;
2897: l_item_org_assignment_rec.item_number := p_item_number;
2898: l_item_org_assignment_rec.organization_id := p_org_id_tbl(i).org_id;
2899: l_item_org_assignment_tbl(i) := l_item_org_assignment_rec;
2900: INV_EBI_UTIL.debug_line('STEP 30: ORGANIZATION ID: '||p_org_id_tbl(i).org_id);
2901: END LOOP;
2902: INV_EBI_UTIL.debug_line('STEP 40: BEFORE CALLING EGO_ITEM_PUB.process_item_org_assignments ');
2903: EGO_ITEM_PUB.process_item_org_assignments(
2904: p_api_version => l_api_version --don't pass as of now
2898: l_item_org_assignment_rec.organization_id := p_org_id_tbl(i).org_id;
2899: l_item_org_assignment_tbl(i) := l_item_org_assignment_rec;
2900: INV_EBI_UTIL.debug_line('STEP 30: ORGANIZATION ID: '||p_org_id_tbl(i).org_id);
2901: END LOOP;
2902: INV_EBI_UTIL.debug_line('STEP 40: BEFORE CALLING EGO_ITEM_PUB.process_item_org_assignments ');
2903: EGO_ITEM_PUB.process_item_org_assignments(
2904: p_api_version => l_api_version --don't pass as of now
2905: ,p_init_msg_list => p_init_msg_list
2906: ,p_commit => FND_API.g_false
2907: ,p_item_org_assignment_tbl => l_item_org_assignment_tbl
2908: ,x_return_status => x_out.output_status.return_status
2909: ,x_msg_count => x_out.output_status.msg_count
2910: );
2911: INV_EBI_UTIL.debug_line('STEP 50: AFTER CALLING EGO_ITEM_PUB.process_item_org_assignments STATUS: '|| x_out.output_status.return_status);
2912: IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
2913: x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Item Name:' || p_item_number;
2914: RAISE FND_API.g_exc_unexpected_error;
2915: END IF;
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;
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);
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);
3126: INV_EBI_UTIL.debug_line('STEP 30: BEFORE CALLING EGO_ITEM_PUB.process_item_cat_assignment');
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);
3126: INV_EBI_UTIL.debug_line('STEP 30: BEFORE CALLING EGO_ITEM_PUB.process_item_cat_assignment');
3127: l_no_multi_assign_cat_set_tbl :=FND_TABLE_OF_NUMBER();
3128: FOR i IN 1..p_category_id_tbl.COUNT LOOP
3129: l_category_id := p_category_id_tbl(i).cat_id;
3130:
3144: ,x_errorcode => x_out.category_output.error_code
3145: ,x_msg_count => x_out.output_status.msg_count
3146: ,x_msg_data => x_out.output_status.msg_data
3147: );
3148: INV_EBI_UTIL.debug_line('STEP 50: AFTER CALLING EGO_ITEM_PUB.process_item_cat_assignment STATUS: '|| x_out.output_status.return_status);
3149: IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
3150: x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Inventory Item Id:' || p_inventory_item_id || ' -> Org Id: ' || p_organization_id;
3151: RAISE FND_API.g_exc_unexpected_error;
3152: END IF;
3169: ,x_msg_count => x_out.output_status.msg_count
3170: ,x_msg_data => x_out.output_status.msg_data
3171: );
3172:
3173: INV_EBI_UTIL.debug_line('STEP 50: AFTER CALLING EGO_ITEM_PUB.process_item_cat_assignment STATUS: '|| x_out.output_status.return_status);
3174: IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
3175: x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Inventory Item Id:' || p_inventory_item_id || ' -> Org Id: ' || p_organization_id;
3176: RAISE FND_API.g_exc_unexpected_error;
3177: END IF;
3197: ,x_msg_count => x_out.output_status.msg_count
3198: ,x_msg_data => x_out.output_status.msg_data
3199: );
3200:
3201: INV_EBI_UTIL.debug_line('STEP 50: AFTER CALLING EGO_ITEM_PUB.process_item_cat_assignment STATUS: '|| x_out.output_status.return_status);
3202: IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
3203: RAISE FND_API.g_exc_unexpected_error;
3204: END IF;
3205: l_no_multi_assign_cat_set_tbl.EXTEND() ;
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;
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 ||
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 ||
3322: ' MANUFACTURER ID : '|| p_mfg_part_obj.manufacturer_id);
3323: OPEN c_mfg_part_num(
3324: p_manufacturer_id => p_mfg_part_obj.manufacturer_id
3325: ,p_mfg_part_num => p_mfg_part_obj.mfg_part_num
3407: l_attribute15 := p_mfg_part_obj.attribute15;
3408: END IF;
3409:
3410: IF UPPER(p_mfg_part_obj.transaction_type) = ENG_GLOBALS.G_OPR_CREATE THEN
3411: INV_EBI_UTIL.debug_line('STEP 30: BEFORE CALLING MTL_MFG_PART_NUMBERS_PKG.insert_row ');
3412: MTL_MFG_PART_NUMBERS_PKG.insert_row(
3413: x_rowid => l_rowid
3414: ,x_manufacturer_id => p_mfg_part_obj.manufacturer_id
3415: ,x_mfg_part_num => p_mfg_part_obj.mfg_part_num
3437: ,x_attribute13 => p_mfg_part_obj.attribute13
3438: ,x_attribute14 => p_mfg_part_obj.attribute14
3439: ,x_attribute15 => p_mfg_part_obj.attribute15
3440: );
3441: INV_EBI_UTIL.debug_line('STEP 40: AFTER CALLING MTL_MFG_PART_NUMBERS_PKG.insert_row ');
3442: ELSIF UPPER(p_mfg_part_obj.transaction_type) = ENG_GLOBALS.G_OPR_UPDATE THEN
3443: INV_EBI_UTIL.debug_line('STEP 50: BEFORE CALLING MTL_MFG_PART_NUMBERS_PKG.update_row ');
3444: MTL_MFG_PART_NUMBERS_PKG.update_row(
3445: x_rowid => l_rowid
3439: ,x_attribute15 => p_mfg_part_obj.attribute15
3440: );
3441: INV_EBI_UTIL.debug_line('STEP 40: AFTER CALLING MTL_MFG_PART_NUMBERS_PKG.insert_row ');
3442: ELSIF UPPER(p_mfg_part_obj.transaction_type) = ENG_GLOBALS.G_OPR_UPDATE THEN
3443: INV_EBI_UTIL.debug_line('STEP 50: BEFORE CALLING MTL_MFG_PART_NUMBERS_PKG.update_row ');
3444: MTL_MFG_PART_NUMBERS_PKG.update_row(
3445: x_rowid => l_rowid
3446: ,x_manufacturer_id => l_manufacturer_id
3447: ,x_mfg_part_num => l_mfg_part_num
3467: ,x_attribute13 => l_attribute13
3468: ,x_attribute14 => l_attribute14
3469: ,x_attribute15 => l_attribute15
3470: );
3471: INV_EBI_UTIL.debug_line('STEP 60: AFTER CALLING MTL_MFG_PART_NUMBERS_PKG.update_row ');
3472: ELSIF UPPER(p_mfg_part_obj.transaction_type) = ENG_GLOBALS.G_OPR_DELETE THEN
3473: INV_EBI_UTIL.debug_line('STEP 70: BEFORE CALLING MTL_MFG_PART_NUMBERS_PKG.delete_row ');
3474: MTL_MFG_PART_NUMBERS_PKG.Delete_Row(l_Rowid);
3475: INV_EBI_UTIL.debug_line('STEP 80: AFTER CALLING MTL_MFG_PART_NUMBERS_PKG.delete_row ');
3469: ,x_attribute15 => l_attribute15
3470: );
3471: INV_EBI_UTIL.debug_line('STEP 60: AFTER CALLING MTL_MFG_PART_NUMBERS_PKG.update_row ');
3472: ELSIF UPPER(p_mfg_part_obj.transaction_type) = ENG_GLOBALS.G_OPR_DELETE THEN
3473: INV_EBI_UTIL.debug_line('STEP 70: BEFORE CALLING MTL_MFG_PART_NUMBERS_PKG.delete_row ');
3474: MTL_MFG_PART_NUMBERS_PKG.Delete_Row(l_Rowid);
3475: INV_EBI_UTIL.debug_line('STEP 80: AFTER CALLING MTL_MFG_PART_NUMBERS_PKG.delete_row ');
3476: ELSIF (p_mfg_part_obj.transaction_type IS NULL) THEN
3477: IF (c_mfg_part_num%NOTFOUND) THEN
3471: INV_EBI_UTIL.debug_line('STEP 60: AFTER CALLING MTL_MFG_PART_NUMBERS_PKG.update_row ');
3472: ELSIF UPPER(p_mfg_part_obj.transaction_type) = ENG_GLOBALS.G_OPR_DELETE THEN
3473: INV_EBI_UTIL.debug_line('STEP 70: BEFORE CALLING MTL_MFG_PART_NUMBERS_PKG.delete_row ');
3474: MTL_MFG_PART_NUMBERS_PKG.Delete_Row(l_Rowid);
3475: INV_EBI_UTIL.debug_line('STEP 80: AFTER CALLING MTL_MFG_PART_NUMBERS_PKG.delete_row ');
3476: ELSIF (p_mfg_part_obj.transaction_type IS NULL) THEN
3477: IF (c_mfg_part_num%NOTFOUND) THEN
3478: INV_EBI_UTIL.debug_line('STEP 90: BEFORE CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.insert_row ');
3479: MTL_MFG_PART_NUMBERS_PKG.insert_row(
3474: MTL_MFG_PART_NUMBERS_PKG.Delete_Row(l_Rowid);
3475: INV_EBI_UTIL.debug_line('STEP 80: AFTER CALLING MTL_MFG_PART_NUMBERS_PKG.delete_row ');
3476: ELSIF (p_mfg_part_obj.transaction_type IS NULL) THEN
3477: IF (c_mfg_part_num%NOTFOUND) THEN
3478: INV_EBI_UTIL.debug_line('STEP 90: BEFORE CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.insert_row ');
3479: MTL_MFG_PART_NUMBERS_PKG.insert_row(
3480: x_rowid => l_rowid
3481: ,x_manufacturer_id => p_mfg_part_obj.manufacturer_id
3482: ,x_mfg_part_num => p_mfg_part_obj.mfg_part_num
3504: ,x_attribute13 => p_mfg_part_obj.attribute13
3505: ,x_attribute14 => p_mfg_part_obj.attribute14
3506: ,x_attribute15 => p_mfg_part_obj.attribute15
3507: );
3508: INV_EBI_UTIL.debug_line('STEP 100: AFTER CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.insert_row ');
3509: ELSE
3510: INV_EBI_UTIL.debug_line('STEP 110: BEFORE CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.update_row ');
3511: MTL_MFG_PART_NUMBERS_PKG.update_row(
3512: x_rowid => l_rowid
3506: ,x_attribute15 => p_mfg_part_obj.attribute15
3507: );
3508: INV_EBI_UTIL.debug_line('STEP 100: AFTER CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.insert_row ');
3509: ELSE
3510: INV_EBI_UTIL.debug_line('STEP 110: BEFORE CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.update_row ');
3511: MTL_MFG_PART_NUMBERS_PKG.update_row(
3512: x_rowid => l_rowid
3513: ,x_manufacturer_id => l_manufacturer_id
3514: ,x_mfg_part_num => l_mfg_part_num
3534: ,x_attribute13 => l_attribute13
3535: ,x_attribute14 => l_attribute14
3536: ,x_attribute15 => l_attribute15
3537: );
3538: INV_EBI_UTIL.debug_line('STEP 120: AFTER CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.update_row ');
3539: END IF;
3540: END IF;
3541: CLOSE c_mfg_part_num;
3542: ELSE
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;
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
3703: l_new_phase_id := p_update_item_tbl(i).item_obj.main_obj_type.current_phase_id;
3704: l_new_status_code := p_update_item_tbl(i).item_obj.main_obj_type.inventory_item_status_code;
3705:
3706:
3707: INV_EBI_UTIL.debug_line('STEP 20: l_new_lifecycle_id ' || l_new_lifecycle_id);
3708: INV_EBI_UTIL.debug_line('STEP 30: l_new_phase_id ' || l_new_phase_id);
3709: INV_EBI_UTIL.debug_line('STEP 40: l_new_status_code ' || l_new_status_code);
3710: INV_EBI_UTIL.debug_line('STEP 50: l_curr_lifecycle_id ' || l_curr_lifecycle_id);
3711: INV_EBI_UTIL.debug_line('STEP 60: l_curr_phase_id ' || l_curr_phase_id);
3704: l_new_status_code := p_update_item_tbl(i).item_obj.main_obj_type.inventory_item_status_code;
3705:
3706:
3707: INV_EBI_UTIL.debug_line('STEP 20: l_new_lifecycle_id ' || l_new_lifecycle_id);
3708: INV_EBI_UTIL.debug_line('STEP 30: l_new_phase_id ' || l_new_phase_id);
3709: INV_EBI_UTIL.debug_line('STEP 40: l_new_status_code ' || l_new_status_code);
3710: INV_EBI_UTIL.debug_line('STEP 50: l_curr_lifecycle_id ' || l_curr_lifecycle_id);
3711: INV_EBI_UTIL.debug_line('STEP 60: l_curr_phase_id ' || l_curr_phase_id);
3712: INV_EBI_UTIL.debug_line('STEP 70: l_curr_status_code ' || l_curr_status_code);
3705:
3706:
3707: INV_EBI_UTIL.debug_line('STEP 20: l_new_lifecycle_id ' || l_new_lifecycle_id);
3708: INV_EBI_UTIL.debug_line('STEP 30: l_new_phase_id ' || l_new_phase_id);
3709: INV_EBI_UTIL.debug_line('STEP 40: l_new_status_code ' || l_new_status_code);
3710: INV_EBI_UTIL.debug_line('STEP 50: l_curr_lifecycle_id ' || l_curr_lifecycle_id);
3711: INV_EBI_UTIL.debug_line('STEP 60: l_curr_phase_id ' || l_curr_phase_id);
3712: INV_EBI_UTIL.debug_line('STEP 70: l_curr_status_code ' || l_curr_status_code);
3713:
3706:
3707: INV_EBI_UTIL.debug_line('STEP 20: l_new_lifecycle_id ' || l_new_lifecycle_id);
3708: INV_EBI_UTIL.debug_line('STEP 30: l_new_phase_id ' || l_new_phase_id);
3709: INV_EBI_UTIL.debug_line('STEP 40: l_new_status_code ' || l_new_status_code);
3710: INV_EBI_UTIL.debug_line('STEP 50: l_curr_lifecycle_id ' || l_curr_lifecycle_id);
3711: INV_EBI_UTIL.debug_line('STEP 60: l_curr_phase_id ' || l_curr_phase_id);
3712: INV_EBI_UTIL.debug_line('STEP 70: l_curr_status_code ' || l_curr_status_code);
3713:
3714: IF(l_new_lifecycle_id IS NOT NULL AND
3707: INV_EBI_UTIL.debug_line('STEP 20: l_new_lifecycle_id ' || l_new_lifecycle_id);
3708: INV_EBI_UTIL.debug_line('STEP 30: l_new_phase_id ' || l_new_phase_id);
3709: INV_EBI_UTIL.debug_line('STEP 40: l_new_status_code ' || l_new_status_code);
3710: INV_EBI_UTIL.debug_line('STEP 50: l_curr_lifecycle_id ' || l_curr_lifecycle_id);
3711: INV_EBI_UTIL.debug_line('STEP 60: l_curr_phase_id ' || l_curr_phase_id);
3712: INV_EBI_UTIL.debug_line('STEP 70: l_curr_status_code ' || l_curr_status_code);
3713:
3714: IF(l_new_lifecycle_id IS NOT NULL AND
3715: l_new_lifecycle_id <> fnd_api.g_miss_num AND
3708: INV_EBI_UTIL.debug_line('STEP 30: l_new_phase_id ' || l_new_phase_id);
3709: INV_EBI_UTIL.debug_line('STEP 40: l_new_status_code ' || l_new_status_code);
3710: INV_EBI_UTIL.debug_line('STEP 50: l_curr_lifecycle_id ' || l_curr_lifecycle_id);
3711: INV_EBI_UTIL.debug_line('STEP 60: l_curr_phase_id ' || l_curr_phase_id);
3712: INV_EBI_UTIL.debug_line('STEP 70: l_curr_status_code ' || l_curr_status_code);
3713:
3714: IF(l_new_lifecycle_id IS NOT NULL AND
3715: l_new_lifecycle_id <> fnd_api.g_miss_num AND
3716: (l_new_phase_id IS NULL OR l_new_phase_id = fnd_api.g_miss_num)) THEN
3735: /*To skip lifecycle phases we need to invoke EGO_ITEM_PUB.Process_item_phase_and_status
3736: in loop till desired phase is reached.This api promotes or demotes lifecycle phases as per the
3737: Transaction type being passed*/
3738:
3739: l_allow_lcp_skip := INV_EBI_UTIL.get_config_param_value(
3740: p_config_tbl => p_update_item_tbl(i).item_obj.name_value_tbl
3741: ,p_config_param_name => G_ALLOW_LIFECYCLE_PHASE_SKIP
3742: );
3743:
3749: IF(l_allow_lcp_skip = FND_API.G_TRUE) THEN
3750:
3751: IF(l_new_phase_id <> l_curr_phase_id) THEN
3752:
3753: INV_EBI_UTIL.debug_line('STEP 80: Current Phase and Incoming Phase is different');
3754: OPEN c_get_phase_seq(cp_phase_id => l_curr_phase_id);
3755: FETCH c_get_phase_seq INTO l_curr_phase_seq;
3756:
3757: IF c_get_phase_seq%NOTFOUND THEN
3758: l_curr_phase_seq := -1;
3759: END IF;
3760:
3761: CLOSE c_get_phase_seq;
3762: INV_EBI_UTIL.debug_line('STEP 90: CURRENT PHASE SEQUENCE IS ' || l_curr_phase_seq);
3763: OPEN c_get_phase_seq(cp_phase_id => l_new_phase_id);
3764: FETCH c_get_phase_seq INTO l_new_phase_seq;
3765:
3766: IF c_get_phase_seq%NOTFOUND THEN
3767: l_new_phase_seq := -1;
3768: END IF;
3769:
3770: CLOSE c_get_phase_seq;
3771: INV_EBI_UTIL.debug_line('STEP 100: INCOMING PHASE SEQUENCE IS ' || l_new_phase_seq);
3772:
3773: IF l_curr_phase_seq < l_new_phase_seq THEN
3774: INV_EBI_UTIL.debug_line('STEP 110: CURRENT PHASE SEQUENCE IS LESS THAN INCOMING PHASE SEQUENCE');
3775: FOR cur_next_phase IN c_get_next_phase(
3770: CLOSE c_get_phase_seq;
3771: INV_EBI_UTIL.debug_line('STEP 100: INCOMING PHASE SEQUENCE IS ' || l_new_phase_seq);
3772:
3773: IF l_curr_phase_seq < l_new_phase_seq THEN
3774: INV_EBI_UTIL.debug_line('STEP 110: CURRENT PHASE SEQUENCE IS LESS THAN INCOMING PHASE SEQUENCE');
3775: FOR cur_next_phase IN c_get_next_phase(
3776: cp_lifecycle_id => l_curr_lifecycle_id,
3777: cp_phase_id => l_curr_phase_id
3778: )
3777: cp_phase_id => l_curr_phase_id
3778: )
3779: LOOP
3780: IF(cur_next_phase.display_sequence < l_new_phase_seq) THEN
3781: INV_EBI_UTIL.debug_line('STEP 120: START CALLING EGO_ITEM_PUB.PROCESS_ITEM_PHASE_AND_STATUS FOR PROMOTING PHASE ');
3782: EGO_ITEM_PUB.Process_item_phase_and_status(
3783: p_api_version => 1.0
3784: ,p_commit => fnd_api.g_false
3785: ,p_init_msg_list => fnd_api.g_true
3799: ,x_return_status => x_out.output_status.return_status
3800: ,x_msg_count => x_out.output_status.msg_count
3801: ,x_msg_data => x_out.output_status.msg_data
3802: ) ;
3803: INV_EBI_UTIL.debug_line('STEP 130: END CALLING EGO_ITEM_PUB.PROCESS_ITEM_PHASE_AND_STATUS status is '|| x_out.output_status.return_status );
3804: IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
3805: x_out.item_number := p_update_item_tbl(i).item_obj.main_obj_type.item_number;
3806: x_out.organization_code := p_update_item_tbl(i).item_obj.main_obj_type.organization_code;
3807: x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Item Number:' || p_update_item_tbl(i).item_obj.main_obj_type.item_number || ' -> Org Code: ' || p_update_item_tbl(i).item_obj.main_obj_type.organization_code;
3811: END IF;
3812: END LOOP;
3813:
3814: ELSIF l_curr_phase_seq > l_new_phase_seq THEN
3815: INV_EBI_UTIL.debug_line('STEP 140: INCOMING PHASE SEQUENCE IS LESS THAN CURRENT PHASE SEQUENCE');
3816: FOR cur_priv_phase IN c_get_priv_phase(
3817: cp_lifecycle_id => l_curr_lifecycle_id,
3818: cp_phase_id => l_curr_phase_id
3819: )
3819: )
3820: LOOP
3821:
3822: IF(cur_priv_phase.display_sequence > l_new_phase_seq) THEN
3823: INV_EBI_UTIL.debug_line('STEP 150: START CALLING EGO_ITEM_PUB.PROCESS_ITEM_PHASE_AND_STATUS FOR DEMOTING PHASE ');
3824: EGO_ITEM_PUB.Process_item_phase_and_status(
3825: p_api_version => 1.0
3826: ,p_commit => fnd_api.g_false
3827: ,p_init_msg_list => fnd_api.g_true
3841: ,x_return_status => x_out.output_status.return_status
3842: ,x_msg_count => x_out.output_status.msg_count
3843: ,x_msg_data => x_out.output_status.msg_data
3844: ) ;
3845: INV_EBI_UTIL.debug_line('STEP 160: END CALLING EGO_ITEM_PUB.PROCESS_ITEM_PHASE_AND_STATUS status is '|| x_out.output_status.return_status );
3846: IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
3847: x_out.item_number := p_update_item_tbl(i).item_obj.main_obj_type.item_number;
3848: x_out.organization_code := p_update_item_tbl(i).item_obj.main_obj_type.organization_code;
3849: x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Item Number:' || p_update_item_tbl(i).item_obj.main_obj_type.item_number || ' -> Org Code: ' || p_update_item_tbl(i).item_obj.main_obj_type.organization_code;
3892: l_item_tbl(1).current_phase_id := l_new_phase_id;
3893: l_item_tbl(1).inventory_item_status_code := l_new_status_code;
3894: l_item_tbl(1).item_catalog_group_id := p_update_item_tbl(i).item_obj.main_obj_type.item_catalog_group_id;
3895:
3896: INV_EBI_UTIL.debug_line('STEP 170 : l_item_tbl(1).transaction_type ' || l_item_tbl(1).transaction_type);
3897: INV_EBI_UTIL.debug_line('STEP 180 : l_item_tbl(1).inventory_item_id ' || l_item_tbl(1).inventory_item_id );
3898: INV_EBI_UTIL.debug_line('STEP 190: l_item_tbl(1).organization_id ' || l_item_tbl(1).organization_id);
3899: INV_EBI_UTIL.debug_line('STEP 200: l_item_tbl(1).lifecycle_id ' || l_item_tbl(1).lifecycle_id);
3900: INV_EBI_UTIL.debug_line('STEP 210: l_item_tbl(1).current_phase_id ' || l_item_tbl(1).current_phase_id );
3893: l_item_tbl(1).inventory_item_status_code := l_new_status_code;
3894: l_item_tbl(1).item_catalog_group_id := p_update_item_tbl(i).item_obj.main_obj_type.item_catalog_group_id;
3895:
3896: INV_EBI_UTIL.debug_line('STEP 170 : l_item_tbl(1).transaction_type ' || l_item_tbl(1).transaction_type);
3897: INV_EBI_UTIL.debug_line('STEP 180 : l_item_tbl(1).inventory_item_id ' || l_item_tbl(1).inventory_item_id );
3898: INV_EBI_UTIL.debug_line('STEP 190: l_item_tbl(1).organization_id ' || l_item_tbl(1).organization_id);
3899: INV_EBI_UTIL.debug_line('STEP 200: l_item_tbl(1).lifecycle_id ' || l_item_tbl(1).lifecycle_id);
3900: INV_EBI_UTIL.debug_line('STEP 210: l_item_tbl(1).current_phase_id ' || l_item_tbl(1).current_phase_id );
3901: INV_EBI_UTIL.debug_line('STEP 220: l_item_tbl(1).inventory_item_status_code ' || l_item_tbl(1).inventory_item_status_code);
3894: l_item_tbl(1).item_catalog_group_id := p_update_item_tbl(i).item_obj.main_obj_type.item_catalog_group_id;
3895:
3896: INV_EBI_UTIL.debug_line('STEP 170 : l_item_tbl(1).transaction_type ' || l_item_tbl(1).transaction_type);
3897: INV_EBI_UTIL.debug_line('STEP 180 : l_item_tbl(1).inventory_item_id ' || l_item_tbl(1).inventory_item_id );
3898: INV_EBI_UTIL.debug_line('STEP 190: l_item_tbl(1).organization_id ' || l_item_tbl(1).organization_id);
3899: INV_EBI_UTIL.debug_line('STEP 200: l_item_tbl(1).lifecycle_id ' || l_item_tbl(1).lifecycle_id);
3900: INV_EBI_UTIL.debug_line('STEP 210: l_item_tbl(1).current_phase_id ' || l_item_tbl(1).current_phase_id );
3901: INV_EBI_UTIL.debug_line('STEP 220: l_item_tbl(1).inventory_item_status_code ' || l_item_tbl(1).inventory_item_status_code);
3902: INV_EBI_UTIL.debug_line('STEP 230: l_item_tbl(1).item_catalog_group_id ' || l_item_tbl(1).item_catalog_group_id);
3895:
3896: INV_EBI_UTIL.debug_line('STEP 170 : l_item_tbl(1).transaction_type ' || l_item_tbl(1).transaction_type);
3897: INV_EBI_UTIL.debug_line('STEP 180 : l_item_tbl(1).inventory_item_id ' || l_item_tbl(1).inventory_item_id );
3898: INV_EBI_UTIL.debug_line('STEP 190: l_item_tbl(1).organization_id ' || l_item_tbl(1).organization_id);
3899: INV_EBI_UTIL.debug_line('STEP 200: l_item_tbl(1).lifecycle_id ' || l_item_tbl(1).lifecycle_id);
3900: INV_EBI_UTIL.debug_line('STEP 210: l_item_tbl(1).current_phase_id ' || l_item_tbl(1).current_phase_id );
3901: INV_EBI_UTIL.debug_line('STEP 220: l_item_tbl(1).inventory_item_status_code ' || l_item_tbl(1).inventory_item_status_code);
3902: INV_EBI_UTIL.debug_line('STEP 230: l_item_tbl(1).item_catalog_group_id ' || l_item_tbl(1).item_catalog_group_id);
3903: EGO_ITEM_PUB.PROCESS_ITEMS(
3896: INV_EBI_UTIL.debug_line('STEP 170 : l_item_tbl(1).transaction_type ' || l_item_tbl(1).transaction_type);
3897: INV_EBI_UTIL.debug_line('STEP 180 : l_item_tbl(1).inventory_item_id ' || l_item_tbl(1).inventory_item_id );
3898: INV_EBI_UTIL.debug_line('STEP 190: l_item_tbl(1).organization_id ' || l_item_tbl(1).organization_id);
3899: INV_EBI_UTIL.debug_line('STEP 200: l_item_tbl(1).lifecycle_id ' || l_item_tbl(1).lifecycle_id);
3900: INV_EBI_UTIL.debug_line('STEP 210: l_item_tbl(1).current_phase_id ' || l_item_tbl(1).current_phase_id );
3901: INV_EBI_UTIL.debug_line('STEP 220: l_item_tbl(1).inventory_item_status_code ' || l_item_tbl(1).inventory_item_status_code);
3902: INV_EBI_UTIL.debug_line('STEP 230: l_item_tbl(1).item_catalog_group_id ' || l_item_tbl(1).item_catalog_group_id);
3903: EGO_ITEM_PUB.PROCESS_ITEMS(
3904: p_api_version => 1.0
3897: INV_EBI_UTIL.debug_line('STEP 180 : l_item_tbl(1).inventory_item_id ' || l_item_tbl(1).inventory_item_id );
3898: INV_EBI_UTIL.debug_line('STEP 190: l_item_tbl(1).organization_id ' || l_item_tbl(1).organization_id);
3899: INV_EBI_UTIL.debug_line('STEP 200: l_item_tbl(1).lifecycle_id ' || l_item_tbl(1).lifecycle_id);
3900: INV_EBI_UTIL.debug_line('STEP 210: l_item_tbl(1).current_phase_id ' || l_item_tbl(1).current_phase_id );
3901: INV_EBI_UTIL.debug_line('STEP 220: l_item_tbl(1).inventory_item_status_code ' || l_item_tbl(1).inventory_item_status_code);
3902: INV_EBI_UTIL.debug_line('STEP 230: l_item_tbl(1).item_catalog_group_id ' || l_item_tbl(1).item_catalog_group_id);
3903: EGO_ITEM_PUB.PROCESS_ITEMS(
3904: p_api_version => 1.0
3905: ,p_init_msg_list => fnd_api.g_true
3898: INV_EBI_UTIL.debug_line('STEP 190: l_item_tbl(1).organization_id ' || l_item_tbl(1).organization_id);
3899: INV_EBI_UTIL.debug_line('STEP 200: l_item_tbl(1).lifecycle_id ' || l_item_tbl(1).lifecycle_id);
3900: INV_EBI_UTIL.debug_line('STEP 210: l_item_tbl(1).current_phase_id ' || l_item_tbl(1).current_phase_id );
3901: INV_EBI_UTIL.debug_line('STEP 220: l_item_tbl(1).inventory_item_status_code ' || l_item_tbl(1).inventory_item_status_code);
3902: INV_EBI_UTIL.debug_line('STEP 230: l_item_tbl(1).item_catalog_group_id ' || l_item_tbl(1).item_catalog_group_id);
3903: EGO_ITEM_PUB.PROCESS_ITEMS(
3904: p_api_version => 1.0
3905: ,p_init_msg_list => fnd_api.g_true
3906: ,p_commit => fnd_api.g_false
3909: ,x_item_tbl => x_item_tbl
3910: ,x_return_status => x_out.output_status.return_status
3911: ,x_msg_count => x_out.output_status.msg_count
3912: );
3913: INV_EBI_UTIL.debug_line('STEP 240: END CALLING EGO_ITEM_PUB.PROCESS_ITEMS STATUS: '|| x_out.output_status.return_status);
3914: IF (x_out.output_status.return_status <> FND_API.g_ret_sts_success) THEN
3915: x_out.item_number := p_update_item_tbl(i).item_obj.main_obj_type.item_number;
3916: x_out.organization_code := p_update_item_tbl(i).item_obj.main_obj_type.organization_code;
3917: x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Item Number:' || p_update_item_tbl(i).item_obj.main_obj_type.item_number || ' -> Org Code: ' || p_update_item_tbl(i).item_obj.main_obj_type.organization_code;
3938: END IF;
3939:
3940: x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
3941: IF(x_out.output_status.msg_data IS NULL) THEN
3942: x_out.output_status.error_table := INV_EBI_UTIL.get_error_table();
3943:
3944: IF (x_out.output_status.error_table IS NOT NULL AND x_out.output_status.error_table.COUNT > 0) THEN
3945:
3946: x_out.output_status.msg_data := INV_EBI_UTIL.get_error_table_msgtxt(x_out.output_status.error_table);
3942: x_out.output_status.error_table := INV_EBI_UTIL.get_error_table();
3943:
3944: IF (x_out.output_status.error_table IS NOT NULL AND x_out.output_status.error_table.COUNT > 0) THEN
3945:
3946: x_out.output_status.msg_data := INV_EBI_UTIL.get_error_table_msgtxt(x_out.output_status.error_table);
3947: IF(x_out.output_status.msg_data IS NULL) THEN
3948:
3949: fnd_msg_pub.count_and_get(
3950: p_encoded => fnd_api.g_false
3964: CLOSE c_get_phase_seq;
3965: END IF;
3966:
3967: x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
3968: x_out.output_status.error_table := INV_EBI_UTIL.get_error_table();
3969:
3970: IF (x_out.output_status.error_table IS NOT NULL AND x_out.output_status.error_table.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;
3967: x_out.output_status.return_status := FND_API.g_ret_sts_unexp_error;
3968: x_out.output_status.error_table := INV_EBI_UTIL.get_error_table();
3969:
3970: IF (x_out.output_status.error_table IS NOT NULL AND x_out.output_status.error_table.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 ';
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
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
4026: ,p_operation => p_operation
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
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
4050: ,p_inventory_item_id => l_inventory_item_id
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;
4076: x_out.output_status.msg_data := l_out.output_status.msg_data;
4077: RAISE fnd_api.g_exc_unexpected_error;
4078: END IF;
4079: l_category_output := inv_ebi_category_output_obj(l_out.category_output.error_code);
4080: l_master_org_id := INV_EBI_UTIL.get_master_organization(
4081: p_organization_id => p_item.main_obj_type.organization_id
4082: );
4083:
4084: --Manufacturer Part Num association needs to be done for master org only.
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;
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
4252: l_is_serial_control := TRUE;
4253: ELSE
4254: l_is_serial_control := FALSE;
4255: END IF;
4256: INV_EBI_UTIL.debug_line('STEP 20: BEFORE CALLING INV_QUANTITY_TREE_PUB.query_quantities');
4257: INV_QUANTITY_TREE_PUB.query_quantities(
4258: p_api_version_number => p_item_balance_input.item_input_table(i).api_version_number
4259: ,p_init_msg_lst => p_item_balance_input.item_input_table(i).init_msg_lst
4260: ,p_organization_id => p_item_balance_input.item_input_table(i).organization_id
4293: ,x_return_status => l_return_status
4294: ,x_msg_count => l_msg_count
4295: ,x_msg_data => l_msg_data
4296: );
4297: INV_EBI_UTIL.debug_line('STEP 30: AFTER CALLING INV_QUANTITY_TREE_PUB.query_quantities STATUS: '||l_return_status);
4298: l_item_bal_output := inv_ebi_item_balance_obj(
4299: l_return_status
4300: ,l_msg_count
4301: ,l_msg_data
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);
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;
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);
4336: l_item_balance_output_obj := inv_ebi_item_bal_output_obj(
4337: p_item_balance_input.item_input_table(i).organization_id
4338: ,p_item_balance_input.item_input_table(i).inventory_item_id
4339: ,l_organization_code
4368: ,p_item_balance_input.item_input_table(i).subinventory_code)
4369: LOOP
4370: l_locator_id := cer.locator_id;
4371: l_item_balance_loc_tbl.extend();
4372: INV_EBI_UTIL.debug_line('STEP 60: BEFORE CALLING INV_QUANTITY_TREE_PUB.query_quantities');
4373: INV_Quantity_Tree_PUB.Query_Quantities(
4374: p_api_version_number => p_item_balance_input.item_input_table(i).api_version_number
4375: ,p_init_msg_lst => p_item_balance_input.item_input_table(i).init_msg_lst
4376: ,p_organization_id => p_item_balance_input.item_input_table(i).organization_id
4409: ,x_return_status => l_return_status
4410: ,x_msg_count => l_msg_count
4411: ,x_msg_data => l_msg_data
4412: );
4413: INV_EBI_UTIL.debug_line('STEP 70: AFTER CALLING INV_QUANTITY_TREE_PUB.query_quantities STATUS: '||l_return_status);
4414: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4415: l_msg_data := l_msg_data || ' -> Inventory Item Id:' || p_item_balance_input.item_input_table(i).inventory_item_id || ' -> Org Id: ' || p_item_balance_input.item_input_table(i).organization_id;
4416: RAISE FND_API.g_exc_unexpected_error;
4417: END IF;
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
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
4628:
4629:
4630: FOR i IN 1..p_attr_grp_id_tbl.COUNT LOOP
4631:
4632: IF(p_attr_group_type = INV_EBI_UTIL.G_EGO_ITEMMGMT_GROUP) THEN
4633:
4634: -- Commented out for bug 10397048
4635: /*SELECT data_level_int_name INTO l_data_level
4636: FROM ego_obj_attr_grp_assocs_v
4741: IF(x_uda_output_obj.output_status.return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4742: RAISE FND_API.g_exc_unexpected_error;
4743: END IF;
4744:
4745: INV_EBI_UTIL.transform_attr_rowdata_uda(
4746: p_attributes_row_table => l_attr_grp
4747: ,p_attributes_data_table => l_attr
4748: ,x_uda_input_obj => x_uda_obj
4749: ,x_return_status => x_uda_output_obj.output_status.return_status
4871: l_pkdata.extend(2);
4872: l_pkdata(1) := EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID',p_inventory_item_id);
4873: l_pkdata(2) := EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID',p_organization_id);
4874:
4875: l_application_id:= INV_EBI_UTIL.get_application_id(
4876: p_application_short_name => 'EGO'
4877: );
4878:
4879: IF(l_application_id IS NULL ) THEN
4885: END IF;
4886:
4887: get_uda_attributes(
4888: p_classification_id => p_item_classification_id,
4889: p_attr_group_type => INV_EBI_UTIL.G_EGO_ITEMMGMT_GROUP,
4890: p_application_id => l_application_id,
4891: p_attr_grp_id_tbl => l_attr_group_id_tbl,
4892: p_data_level => NULL,
4893: p_revision_id => p_revision_id,
4890: p_application_id => l_application_id,
4891: p_attr_grp_id_tbl => l_attr_group_id_tbl,
4892: p_data_level => NULL,
4893: p_revision_id => p_revision_id,
4894: p_object_name => INV_EBI_UTIL.G_EGO_ITEM,
4895: p_pk_data => l_pkdata,
4896: x_uda_obj => x_item_uda,
4897: x_uda_output_obj => l_uda_output_obj
4898: );
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;
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);
5819: l_mfr_part_table(ctr) := l_item_mfr_part_obj;
5820: ctr := ctr + 1;
5821: END LOOP;
5822:
5819: l_mfr_part_table(ctr) := l_item_mfr_part_obj;
5820: ctr := ctr + 1;
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,
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;
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);
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();
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);
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();
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);
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();
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);
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);
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);
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);
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
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);
6230: FOR i in p_item_tbl.FIRST..p_item_tbl.LAST LOOP
6231: IF l_org_tbl IS NOT NULL AND l_org_tbl.COUNT>0 THEN
6232: FOR j in l_org_tbl.FIRST..l_org_tbl.LAST LOOP
6233: IF (p_item_tbl(i).organization_code = l_org_tbl(j)) THEN
6234: INV_EBI_UTIL.debug_line('STEP 20: ITEM NAME IS '|| p_item_tbl(i).item_name);
6235: INV_EBI_UTIL.debug_line('STEP 30: ORGANIZATION CODE IS '|| l_org_tbl(j));
6236: l_counter := l_counter + 1;
6237: l_item_output_tbl.EXTEND(1);
6238: l_item_output_tbl(l_counter) := p_item_tbl(i);
6231: IF l_org_tbl IS NOT NULL AND l_org_tbl.COUNT>0 THEN
6232: FOR j in l_org_tbl.FIRST..l_org_tbl.LAST LOOP
6233: IF (p_item_tbl(i).organization_code = l_org_tbl(j)) THEN
6234: INV_EBI_UTIL.debug_line('STEP 20: ITEM NAME IS '|| p_item_tbl(i).item_name);
6235: INV_EBI_UTIL.debug_line('STEP 30: ORGANIZATION CODE IS '|| l_org_tbl(j));
6236: l_counter := l_counter + 1;
6237: l_item_output_tbl.EXTEND(1);
6238: l_item_output_tbl(l_counter) := p_item_tbl(i);
6239: EXIT;
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';
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
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
6294: l_item_tbl := parse_input_string(p_item_names);
6295: END IF;
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
6294: l_item_tbl := parse_input_string(p_item_names);
6295: END IF;
6296:
6430: x_item_tbl := l_item_output_tbl;
6431: IF (x_item_tbl.count > 0) THEN
6432: FOR i IN 1 .. x_item_tbl.count
6433: LOOP
6434: INV_EBI_UTIL.debug_line('STEP 40: ITEM NAME FROM OUT TABLE '|| x_item_tbl(i).item_name);
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;
6431: IF (x_item_tbl.count > 0) THEN
6432: FOR i IN 1 .. x_item_tbl.count
6433: LOOP
6434: INV_EBI_UTIL.debug_line('STEP 40: ITEM NAME FROM OUT TABLE '|| x_item_tbl(i).item_name);
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);
6432: FOR i IN 1 .. x_item_tbl.count
6433: LOOP
6434: INV_EBI_UTIL.debug_line('STEP 40: ITEM NAME FROM OUT TABLE '|| x_item_tbl(i).item_name);
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
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';
6585: FROM dual ) b
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
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);
6595: END IF;
6596:
6597: --Getting the values for the parameters passed from CP
6598: IF (p_name_value_list IS NOT NULL AND p_name_value_list.COUNT > 0) THEN
6599: l_item_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Item Name');
6600: l_org_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Organization Code');
6601: l_from_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'From Date');
6602: l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
6603: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6596:
6597: --Getting the values for the parameters passed from CP
6598: IF (p_name_value_list IS NOT NULL AND p_name_value_list.COUNT > 0) THEN
6599: l_item_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Item Name');
6600: l_org_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Organization Code');
6601: l_from_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'From Date');
6602: l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
6603: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6604:
6597: --Getting the values for the parameters passed from CP
6598: IF (p_name_value_list IS NOT NULL AND p_name_value_list.COUNT > 0) THEN
6599: l_item_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Item Name');
6600: l_org_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Organization Code');
6601: l_from_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'From Date');
6602: l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
6603: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6604:
6605: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6598: IF (p_name_value_list IS NOT NULL AND p_name_value_list.COUNT > 0) THEN
6599: l_item_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Item Name');
6600: l_org_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Organization Code');
6601: l_from_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'From Date');
6602: l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
6603: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6604:
6605: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6606: INV_EBI_UTIL.debug_line('STEP 30: INPUT PARAMETER FOR ORG IS ' || l_org_string);
6599: l_item_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Item Name');
6600: l_org_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Organization Code');
6601: l_from_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'From Date');
6602: l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
6603: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6604:
6605: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6606: INV_EBI_UTIL.debug_line('STEP 30: INPUT PARAMETER FOR ORG IS ' || l_org_string);
6607: INV_EBI_UTIL.debug_line('STEP 40: INPUT PARAMETER FOR FROM DATE IS ' || l_from_date_str);
6601: l_from_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'From Date');
6602: l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
6603: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6604:
6605: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6606: INV_EBI_UTIL.debug_line('STEP 30: INPUT PARAMETER FOR ORG IS ' || l_org_string);
6607: INV_EBI_UTIL.debug_line('STEP 40: INPUT PARAMETER FOR FROM DATE IS ' || l_from_date_str);
6608: INV_EBI_UTIL.debug_line('STEP 50: INPUT PARAMETER FOR TO DATE IS ' || l_to_date_str);
6609: INV_EBI_UTIL.debug_line('STEP 60: INPUT PARAMETER FOR LAST UPDATED HRS IS '|| l_last_x_hrs);
6602: l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
6603: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6604:
6605: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6606: INV_EBI_UTIL.debug_line('STEP 30: INPUT PARAMETER FOR ORG IS ' || l_org_string);
6607: INV_EBI_UTIL.debug_line('STEP 40: INPUT PARAMETER FOR FROM DATE IS ' || l_from_date_str);
6608: INV_EBI_UTIL.debug_line('STEP 50: INPUT PARAMETER FOR TO DATE IS ' || l_to_date_str);
6609: INV_EBI_UTIL.debug_line('STEP 60: INPUT PARAMETER FOR LAST UPDATED HRS IS '|| l_last_x_hrs);
6610:
6603: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6604:
6605: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6606: INV_EBI_UTIL.debug_line('STEP 30: INPUT PARAMETER FOR ORG IS ' || l_org_string);
6607: INV_EBI_UTIL.debug_line('STEP 40: INPUT PARAMETER FOR FROM DATE IS ' || l_from_date_str);
6608: INV_EBI_UTIL.debug_line('STEP 50: INPUT PARAMETER FOR TO DATE IS ' || l_to_date_str);
6609: INV_EBI_UTIL.debug_line('STEP 60: INPUT PARAMETER FOR LAST UPDATED HRS IS '|| l_last_x_hrs);
6610:
6611: IF l_from_date_str IS NOT NULL THEN
6604:
6605: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6606: INV_EBI_UTIL.debug_line('STEP 30: INPUT PARAMETER FOR ORG IS ' || l_org_string);
6607: INV_EBI_UTIL.debug_line('STEP 40: INPUT PARAMETER FOR FROM DATE IS ' || l_from_date_str);
6608: INV_EBI_UTIL.debug_line('STEP 50: INPUT PARAMETER FOR TO DATE IS ' || l_to_date_str);
6609: INV_EBI_UTIL.debug_line('STEP 60: INPUT PARAMETER FOR LAST UPDATED HRS IS '|| l_last_x_hrs);
6610:
6611: IF l_from_date_str IS NOT NULL THEN
6612: l_from_date := TO_DATE(l_from_date_str,'YYYY/MM/DD HH24:MI:SS');
6605: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6606: INV_EBI_UTIL.debug_line('STEP 30: INPUT PARAMETER FOR ORG IS ' || l_org_string);
6607: INV_EBI_UTIL.debug_line('STEP 40: INPUT PARAMETER FOR FROM DATE IS ' || l_from_date_str);
6608: INV_EBI_UTIL.debug_line('STEP 50: INPUT PARAMETER FOR TO DATE IS ' || l_to_date_str);
6609: INV_EBI_UTIL.debug_line('STEP 60: INPUT PARAMETER FOR LAST UPDATED HRS IS '|| l_last_x_hrs);
6610:
6611: IF l_from_date_str IS NOT NULL THEN
6612: l_from_date := TO_DATE(l_from_date_str,'YYYY/MM/DD HH24:MI:SS');
6613: INV_EBI_UTIL.debug_line('STEP 70: FROM DATE CONVERSION IF IT IS NOT NULL ' || l_from_date);
6609: INV_EBI_UTIL.debug_line('STEP 60: INPUT PARAMETER FOR LAST UPDATED HRS IS '|| l_last_x_hrs);
6610:
6611: IF l_from_date_str IS NOT NULL THEN
6612: l_from_date := TO_DATE(l_from_date_str,'YYYY/MM/DD HH24:MI:SS');
6613: INV_EBI_UTIL.debug_line('STEP 70: FROM DATE CONVERSION IF IT IS NOT NULL ' || l_from_date);
6614: END IF;
6615:
6616: IF l_to_date_str IS NOT NULL THEN
6617: l_to_date := TO_DATE(l_to_date_str,'YYYY/MM/DD HH24:MI:SS');
6614: END IF;
6615:
6616: IF l_to_date_str IS NOT NULL THEN
6617: l_to_date := TO_DATE(l_to_date_str,'YYYY/MM/DD HH24:MI:SS');
6618: INV_EBI_UTIL.debug_line('STEP 80: TO DATE CONVERSION IF IT IS NOT NULL ' || l_to_date);
6619: END IF;
6620:
6621: IF l_last_x_hrs IS NOT NULL THEN
6622: l_from_date := SYSDATE-( l_last_x_hrs/24);
6620:
6621: IF l_last_x_hrs IS NOT NULL THEN
6622: l_from_date := SYSDATE-( l_last_x_hrs/24);
6623: l_to_date := SYSDATE ;
6624: INV_EBI_UTIL.debug_line('STEP 90: FROM DATE IF THE LAST X HRS PARAMATER IS GIVEN ' || l_from_date);
6625: INV_EBI_UTIL.debug_line('STEP 100: TO DATE IF THE LAST X HRS PARAMATER IS GIVEN ' || l_to_date);
6626: END IF;
6627: END IF;
6628:
6621: IF l_last_x_hrs IS NOT NULL THEN
6622: l_from_date := SYSDATE-( l_last_x_hrs/24);
6623: l_to_date := SYSDATE ;
6624: INV_EBI_UTIL.debug_line('STEP 90: FROM DATE IF THE LAST X HRS PARAMATER IS GIVEN ' || l_from_date);
6625: INV_EBI_UTIL.debug_line('STEP 100: TO DATE IF THE LAST X HRS PARAMATER IS GIVEN ' || l_to_date);
6626: END IF;
6627: END IF;
6628:
6629: -- If all the parameter values are null then fetch items that got updated
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;
6637: END IF;
6638:
6639: IF l_from_date IS NOT NULL AND l_to_date IS NULL THEN
6641: END IF;
6642:
6643: -- Get the valid combination of Items and Organizations
6644: IF ( l_item_string IS NOT NULL ) THEN
6645: INV_EBI_UTIL.debug_line('STEP 120: BEFORE CALLING PARSE_AND_GET_ITEM');
6646: parse_and_get_item( p_item_names => l_item_string
6647: ,p_org_codes => l_org_string
6648: ,x_item_tbl => l_item_output_tbl
6649: ,x_return_status => l_return_status
6648: ,x_item_tbl => l_item_output_tbl
6649: ,x_return_status => l_return_status
6650: ,x_msg_count => l_msg_count
6651: ,x_msg_data => l_msg_data);
6652: INV_EBI_UTIL.debug_line('STEP 130: AFTER CALLING PARSE_AND_GET_ITEM RETURN STATUS: '|| l_return_status);
6653: END IF;
6654:
6655:
6656: IF (l_return_status <> FND_API.g_ret_sts_success) THEN
6663: x_items := l_item_output_tbl;
6664:
6665: -- Filtering the Items that got updated within the given dates
6666: IF (l_from_date IS NOT NULL AND l_to_date IS NOT NULL) THEN
6667: IF(INV_EBI_UTIL.is_pim_installed AND l_parsed_tbl IS NOT NULL AND l_parsed_tbl.COUNT>0) THEN
6668: INV_EBI_UTIL.debug_line('STEP 140: INSIDE THE IF PART OF FILTERING THE ITEMS WITH IN GIVEN DATES');
6669: --Fetched the data based on the source system reference
6670: IF (c_get_item_list_pim%ISOPEN) THEN
6671: CLOSE c_get_item_list_pim;
6664:
6665: -- Filtering the Items that got updated within the given dates
6666: IF (l_from_date IS NOT NULL AND l_to_date IS NOT NULL) THEN
6667: IF(INV_EBI_UTIL.is_pim_installed AND l_parsed_tbl IS NOT NULL AND l_parsed_tbl.COUNT>0) THEN
6668: INV_EBI_UTIL.debug_line('STEP 140: INSIDE THE IF PART OF FILTERING THE ITEMS WITH IN GIVEN DATES');
6669: --Fetched the data based on the source system reference
6670: IF (c_get_item_list_pim%ISOPEN) THEN
6671: CLOSE c_get_item_list_pim;
6672: END IF;
6674: FETCH c_get_item_list_pim BULK COLLECT INTO l_item_tbl;
6675: CLOSE c_get_item_list_pim;
6676: ELSE
6677: --Fetch all the records irrespective of source system reference
6678: INV_EBI_UTIL.debug_line('STEP 150: INSIDE THE ELSE PART OF FILTERING THE ITEMS WITH IN GIVEN DATES');
6679: IF (c_get_item_list%ISOPEN) THEN
6680: CLOSE c_get_item_list;
6681: END IF;
6682: OPEN c_get_item_list;
6685: END IF;
6686: IF (l_item_tbl.count > 0 ) THEN
6687: FOR i IN 1 .. l_item_tbl.count
6688: LOOP
6689: INV_EBI_UTIL.debug_line('STEP 160: ITEM NAME FROM ITEM LIST IS '|| l_item_tbl(i).item_name);
6690: INV_EBI_UTIL.debug_line('STEP 170: ORG CODE FROM ITEM LIST IS '|| l_item_tbl(i).organization_code);
6691: END LOOP;
6692: END IF;
6693:
6686: IF (l_item_tbl.count > 0 ) THEN
6687: FOR i IN 1 .. l_item_tbl.count
6688: LOOP
6689: INV_EBI_UTIL.debug_line('STEP 160: ITEM NAME FROM ITEM LIST IS '|| l_item_tbl(i).item_name);
6690: INV_EBI_UTIL.debug_line('STEP 170: ORG CODE FROM ITEM LIST IS '|| l_item_tbl(i).organization_code);
6691: END LOOP;
6692: END IF;
6693:
6694: IF (l_item_string IS NOT NULL) THEN
6700: CLOSE c_get_item_flist;
6701: x_items := l_item_tbl_flst;
6702: -- Filtering the Items that got updated in the specified time in the given Orgs
6703: ELSIF (l_org_string IS NOT NULL) THEN
6704: INV_EBI_UTIL.debug_line('STEP 180: BEFORE CALLING FILTER ITEMS BASED ON ORG');
6705: filter_items_based_on_org( p_org_codes => l_org_string
6706: ,p_item_tbl => l_item_tbl
6707: ,x_item_tbl => l_item_org_output_tbl
6708: ,x_return_status => l_return_status
6707: ,x_item_tbl => l_item_org_output_tbl
6708: ,x_return_status => l_return_status
6709: ,x_msg_count => l_msg_count
6710: ,x_msg_data => l_msg_data);
6711: INV_EBI_UTIL.debug_line('STEP 190: AFTER CALLING FILTER ITEMS BASED ON ORG RETURN STATUS: '|| l_return_status);
6712: IF (l_return_status = FND_API.g_ret_sts_success) THEN
6713: x_items := l_item_org_output_tbl;
6714: ELSE
6715: x_return_status := l_return_status;
6723: END IF;
6724: IF (x_items.count > 0) THEN
6725: FOR i IN 1 .. x_items.count
6726: LOOP
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');
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;
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;
6747: x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.get_item_attributes_list';
6827: FROM dual ) b ) gibf;
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
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);
6836: l_parsed_tbl := parse_source_system_string(p_cross_reference_type);
6837: END IF;
6838:
6839: IF (p_name_value_list IS NOT NULL AND p_name_value_list.COUNT > 0) THEN
6840: l_item_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Item Name');
6841: l_org_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Organization Code');
6842: l_from_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'From Date');
6843: l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
6844: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6837: END IF;
6838:
6839: IF (p_name_value_list IS NOT NULL AND p_name_value_list.COUNT > 0) THEN
6840: l_item_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Item Name');
6841: l_org_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Organization Code');
6842: l_from_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'From Date');
6843: l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
6844: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6845:
6838:
6839: IF (p_name_value_list IS NOT NULL AND p_name_value_list.COUNT > 0) THEN
6840: l_item_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Item Name');
6841: l_org_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Organization Code');
6842: l_from_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'From Date');
6843: l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
6844: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6845:
6846: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6839: IF (p_name_value_list IS NOT NULL AND p_name_value_list.COUNT > 0) THEN
6840: l_item_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Item Name');
6841: l_org_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Organization Code');
6842: l_from_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'From Date');
6843: l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
6844: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6845:
6846: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6847: INV_EBI_UTIL.debug_line('STEP 30: INPUT PARAMETER FOR ORG IS ' || l_org_string);
6840: l_item_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Item Name');
6841: l_org_string := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Organization Code');
6842: l_from_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'From Date');
6843: l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
6844: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6845:
6846: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6847: INV_EBI_UTIL.debug_line('STEP 30: INPUT PARAMETER FOR ORG IS ' || l_org_string);
6848: INV_EBI_UTIL.debug_line('STEP 40: INPUT PARAMETER FOR FROM DATE IS ' || l_from_date_str);
6842: l_from_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'From Date');
6843: l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
6844: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6845:
6846: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6847: INV_EBI_UTIL.debug_line('STEP 30: INPUT PARAMETER FOR ORG IS ' || l_org_string);
6848: INV_EBI_UTIL.debug_line('STEP 40: INPUT PARAMETER FOR FROM DATE IS ' || l_from_date_str);
6849: INV_EBI_UTIL.debug_line('STEP 50: INPUT PARAMETER FOR TO DATE IS ' || l_to_date_str);
6850: INV_EBI_UTIL.debug_line('STEP 60: INPUT PARAMETER FOR LAST UPDATED HRS IS '|| l_last_x_hrs);
6843: l_to_date_str := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'To Date');
6844: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6845:
6846: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6847: INV_EBI_UTIL.debug_line('STEP 30: INPUT PARAMETER FOR ORG IS ' || l_org_string);
6848: INV_EBI_UTIL.debug_line('STEP 40: INPUT PARAMETER FOR FROM DATE IS ' || l_from_date_str);
6849: INV_EBI_UTIL.debug_line('STEP 50: INPUT PARAMETER FOR TO DATE IS ' || l_to_date_str);
6850: INV_EBI_UTIL.debug_line('STEP 60: INPUT PARAMETER FOR LAST UPDATED HRS IS '|| l_last_x_hrs);
6851:
6844: l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
6845:
6846: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6847: INV_EBI_UTIL.debug_line('STEP 30: INPUT PARAMETER FOR ORG IS ' || l_org_string);
6848: INV_EBI_UTIL.debug_line('STEP 40: INPUT PARAMETER FOR FROM DATE IS ' || l_from_date_str);
6849: INV_EBI_UTIL.debug_line('STEP 50: INPUT PARAMETER FOR TO DATE IS ' || l_to_date_str);
6850: INV_EBI_UTIL.debug_line('STEP 60: INPUT PARAMETER FOR LAST UPDATED HRS IS '|| l_last_x_hrs);
6851:
6852: IF l_from_date_str IS NOT NULL THEN
6845:
6846: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6847: INV_EBI_UTIL.debug_line('STEP 30: INPUT PARAMETER FOR ORG IS ' || l_org_string);
6848: INV_EBI_UTIL.debug_line('STEP 40: INPUT PARAMETER FOR FROM DATE IS ' || l_from_date_str);
6849: INV_EBI_UTIL.debug_line('STEP 50: INPUT PARAMETER FOR TO DATE IS ' || l_to_date_str);
6850: INV_EBI_UTIL.debug_line('STEP 60: INPUT PARAMETER FOR LAST UPDATED HRS IS '|| l_last_x_hrs);
6851:
6852: IF l_from_date_str IS NOT NULL THEN
6853: l_from_date := TO_DATE(l_from_date_str,'YYYY/MM/DD HH24:MI:SS');
6846: INV_EBI_UTIL.debug_line('STEP 20: INPUT PARAMETER FOR ITEM IS ' || l_item_string);
6847: INV_EBI_UTIL.debug_line('STEP 30: INPUT PARAMETER FOR ORG IS ' || l_org_string);
6848: INV_EBI_UTIL.debug_line('STEP 40: INPUT PARAMETER FOR FROM DATE IS ' || l_from_date_str);
6849: INV_EBI_UTIL.debug_line('STEP 50: INPUT PARAMETER FOR TO DATE IS ' || l_to_date_str);
6850: INV_EBI_UTIL.debug_line('STEP 60: INPUT PARAMETER FOR LAST UPDATED HRS IS '|| l_last_x_hrs);
6851:
6852: IF l_from_date_str IS NOT NULL THEN
6853: l_from_date := TO_DATE(l_from_date_str,'YYYY/MM/DD HH24:MI:SS');
6854: INV_EBI_UTIL.debug_line('STEP 70: INSIDE IF OF FROM DATE FORMAT :'|| l_from_date);
6850: INV_EBI_UTIL.debug_line('STEP 60: INPUT PARAMETER FOR LAST UPDATED HRS IS '|| l_last_x_hrs);
6851:
6852: IF l_from_date_str IS NOT NULL THEN
6853: l_from_date := TO_DATE(l_from_date_str,'YYYY/MM/DD HH24:MI:SS');
6854: INV_EBI_UTIL.debug_line('STEP 70: INSIDE IF OF FROM DATE FORMAT :'|| l_from_date);
6855: END IF;
6856:
6857: IF l_to_date_str IS NOT NULL THEN
6858: l_to_date := TO_DATE(l_to_date_str,'YYYY/MM/DD HH24:MI:SS');
6855: END IF;
6856:
6857: IF l_to_date_str IS NOT NULL THEN
6858: l_to_date := TO_DATE(l_to_date_str,'YYYY/MM/DD HH24:MI:SS');
6859: INV_EBI_UTIL.debug_line('STEP 80: INSIDE IF OF TO DATE FORMAT '|| l_to_date);
6860: END IF;
6861:
6862: IF l_last_x_hrs IS NOT NULL THEN
6863: l_from_date := SYSDATE-( l_last_x_hrs/24);
6861:
6862: IF l_last_x_hrs IS NOT NULL THEN
6863: l_from_date := SYSDATE-( l_last_x_hrs/24);
6864: l_to_date := SYSDATE ;
6865: INV_EBI_UTIL.debug_line('STEP 90: FROM DATE IF LAST X HRS IS GIVEN '|| l_from_date);
6866: INV_EBI_UTIL.debug_line('STEP 100: TO DATE IF LAST X HRS IS GIVEN '|| l_to_date);
6867: END IF;
6868: END IF;
6869:
6862: IF l_last_x_hrs IS NOT NULL THEN
6863: l_from_date := SYSDATE-( l_last_x_hrs/24);
6864: l_to_date := SYSDATE ;
6865: INV_EBI_UTIL.debug_line('STEP 90: FROM DATE IF LAST X HRS IS GIVEN '|| l_from_date);
6866: INV_EBI_UTIL.debug_line('STEP 100: TO DATE IF LAST X HRS IS GIVEN '|| l_to_date);
6867: END IF;
6868: END IF;
6869:
6870: 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
6870: 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
6871: l_from_date :=get_last_run_date( p_conc_prog_id => p_prog_id
6872: ,p_appl_id => p_appl_id
6873: );
6874: INV_EBI_UTIL.debug_line('STEP 110: FROM DATE WHEN ALL THE PARAMETERS ARE PASSED AS NULL '||l_from_date);
6875: l_to_date := SYSDATE;
6876: END IF;
6877:
6878: IF l_from_date IS NOT NULL AND l_to_date IS NULL THEN
6879: l_to_date := SYSDATE;
6880: END IF;
6881:
6882: IF ( l_item_string IS NOT NULL ) THEN
6883: INV_EBI_UTIL.debug_line('STEP 120: BEFORE CALLING PARSE AND GET ITEM');
6884: parse_and_get_item( p_item_names => l_item_string
6885: ,p_org_codes => l_org_string
6886: ,x_item_tbl => l_item_output_tbl
6887: ,x_return_status => l_return_status
6887: ,x_return_status => l_return_status
6888: ,x_msg_count => l_msg_count
6889: ,x_msg_data => l_msg_data);
6890: END IF;
6891: INV_EBI_UTIL.debug_line('STEP 130: AFTER CALLING PARSE AND GET ITEM STATUS: '|| l_return_status);
6892: IF (l_return_status <> FND_API.g_ret_sts_success) THEN
6893: x_return_status := l_return_status;
6894: IF l_msg_data IS NOT NULL THEN
6895: x_msg_data := l_msg_data;
6898:
6899: x_items := l_item_output_tbl;
6900:
6901: IF (l_from_date IS NOT NULL AND l_to_date IS NOT NULL) THEN
6902: IF(INV_EBI_UTIL.is_pim_installed AND l_parsed_tbl IS NOT NULL AND l_parsed_tbl.COUNT>0) THEN
6903: INV_EBI_UTIL.debug_line('STEP 140: INSIDE IF BLOCK IF FROM DATE AND TO DATE IS NOT NULL');
6904: --Fetched the data based on the source system reference
6905: IF (c_get_bal_item_lst_pim%ISOPEN) THEN
6906: CLOSE c_get_bal_item_lst_pim;
6899: x_items := l_item_output_tbl;
6900:
6901: IF (l_from_date IS NOT NULL AND l_to_date IS NOT NULL) THEN
6902: IF(INV_EBI_UTIL.is_pim_installed AND l_parsed_tbl IS NOT NULL AND l_parsed_tbl.COUNT>0) THEN
6903: INV_EBI_UTIL.debug_line('STEP 140: INSIDE IF BLOCK IF FROM DATE AND TO DATE IS NOT NULL');
6904: --Fetched the data based on the source system reference
6905: IF (c_get_bal_item_lst_pim%ISOPEN) THEN
6906: CLOSE c_get_bal_item_lst_pim;
6907: END IF;
6908: OPEN c_get_bal_item_lst_pim;
6909: FETCH c_get_bal_item_lst_pim BULK COLLECT INTO l_item_tbl;
6910: CLOSE c_get_bal_item_lst_pim;
6911: ELSE
6912: INV_EBI_UTIL.debug_line('STEP 150: INSIDE ELSE BLOCK IF FROM DATE AND TO DATE IS NOT NULL');
6913: IF (c_get_bal_item_lst%ISOPEN) THEN
6914: CLOSE c_get_bal_item_lst;
6915: END IF;
6916: OPEN c_get_bal_item_lst;
6919: END IF;
6920: IF (l_item_tbl.count >0) THEN
6921: FOR i IN 1 .. l_item_tbl.count
6922: LOOP
6923: INV_EBI_UTIL.debug_line('STEP 160: ITEM NAME FROM ITEM LIST IS '|| l_item_tbl(i).item_name);
6924: INV_EBI_UTIL.debug_line('STEP 170: ORG CODE FROM ITEM LIST IS '|| l_item_tbl(i).organization_code);
6925: END LOOP;
6926: END IF;
6927:
6920: IF (l_item_tbl.count >0) THEN
6921: FOR i IN 1 .. l_item_tbl.count
6922: LOOP
6923: INV_EBI_UTIL.debug_line('STEP 160: ITEM NAME FROM ITEM LIST IS '|| l_item_tbl(i).item_name);
6924: INV_EBI_UTIL.debug_line('STEP 170: ORG CODE FROM ITEM LIST IS '|| l_item_tbl(i).organization_code);
6925: END LOOP;
6926: END IF;
6927:
6928: IF (l_item_string IS NOT NULL) THEN
6933: FETCH c_get_bal_item_flst BULK COLLECT INTO l_item_tbl_flst;
6934: CLOSE c_get_bal_item_flst;
6935: x_items := l_item_tbl_flst;
6936: ELSIF (l_org_string IS NOT NULL) THEN
6937: INV_EBI_UTIL.debug_line('STEP 180: BEFORE CALLING FILTER ITEMS BASED ON ORG');
6938: filter_items_based_on_org( p_org_codes => l_org_string
6939: ,p_item_tbl => l_item_tbl
6940: ,x_item_tbl => l_item_org_output_tbl
6941: ,x_return_status => l_return_status
6940: ,x_item_tbl => l_item_org_output_tbl
6941: ,x_return_status => l_return_status
6942: ,x_msg_count => l_msg_count
6943: ,x_msg_data => l_msg_data);
6944: INV_EBI_UTIL.debug_line('STEP 190: AFTER CALLING FILTER ITEMS BASED ON ORG STATUS: '|| l_return_status);
6945: IF (l_return_status = FND_API.g_ret_sts_success) THEN
6946: x_items := l_item_org_output_tbl;
6947: ELSE
6948: x_return_status := l_return_status;
6958: END IF;
6959: IF (x_items.count > 0) THEN
6960: FOR i IN 1 .. x_items.count
6961: LOOP
6962: INV_EBI_UTIL.debug_line('STEP 200: ITEM NAME '|| x_items(i).item_name ||' ORGANIZATION CODE '|| x_items(i).organization_code);
6963: END LOOP;
6964: END IF;
6965: -- for Operating unit Pouplation
6966: IF x_items is NOT NULL AND x_items.COUNT > 0 then
6963: END LOOP;
6964: END IF;
6965: -- for Operating unit Pouplation
6966: IF x_items is NOT NULL AND x_items.COUNT > 0 then
6967: INV_EBI_UTIL.debug_line('STEP 210: BEFORE CALLING GET OPERATING UNIT');
6968: FOR i IN 1..x_items.COUNT
6969: LOOP
6970: get_Operating_unit
6971: (p_oranization_id => x_items(i).organization_id
6972: ,x_operating_unit => x_items(i).operating_unit
6973: ,x_ouid => x_items(i).operating_unit_id
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
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;
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;
6983: x_msg_data := SQLERRM ||' at INV_EBI_ITEM_HELPER.get_item_balance_list';