eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MRP_MAP_REG_SITE dependencies on PO_VENDOR_SITES_ALL

Line 1: PACKAGE BODY MRP_MAP_REG_SITE AS FUNCTION MAP_REGION_TO_SITE (p_last_update_date in DATE) RETURN NUMBER IS Cursor regions_update is select max(LAST_UPDATE_DATE)from WSH_REGIONS; Cursor all_vendor_sites is select vendor_site_id,country,state,city,zip from PO_VENDOR_SITES_ALL; Cursor new_vendor_sites(p_date DATE) is select vendor_site_id,country,state,city,zip from PO_VENDOR_SITES_ALL where last_update_date >= p_date; TYPE VendorSiteTblTyp IS TABLE OF PO_VENDOR_SITES_ALL.VENDOR_SITE_ID%TYPE; TYPE CountryTblTyp IS TABLE OF PO_VENDOR_SITES_ALL.COUNTRY%TYPE; TYPE StateTblTyp IS TABLE OF PO_VENDOR_SITES_ALL.STATE%TYPE; TYPE CityTblTyp IS TABLE OF PO_VENDOR_SITES_ALL.CITY%TYPE; TYPE ZipTblTyp IS TABLE OF PO_VENDOR_SITES_ALL.ZIP%TYPE; TYPE NumTblTyp IS TABLE OF NUMBER; l_vendor_site_tab VendorSiteTblTyp ; l_country_tab CountryTblTyp; l_state_tab StateTblTyp; l_city_tab CityTblTyp; l_postal_tab ZipTblTyp; l_region_id_tab NumTblTyp := NumTblTyp(); l_region_type_tab NumTblTyp := NumTblTyp(); l_zone_level_tab NumTblTyp := NumTblTyp(); l_regions WSH_REGIONS_SEARCH_PKG.region_table; region_last_update DATE := NULL; l_status NUMBER; v_current_date DATE; v_current_user NUMBER;BEGIN SELECT SYSDATE, FND_GLOBAL.USER_ID INTO v_current_date, v_current_user FROM DUAL; OPEN regions_update; FETCH regions_update into region_last_update; CLOSE regions_update;IF region_last_update is NULL THEN Return(1); NULL;END IF; IF (p_last_update_date is NULL) OR (region_last_update >= p_last_update_date) THEN DELETE FROM MRP_REGION_SITES; OPEN all_vendor_sites; FETCH all_vendor_sites BULK COLLECT INTO l_vendor_site_tab,l_country_tab,l_state_tab,l_city_tab,l_postal_tab; CLOSE all_vendor_sites; ELSE OPEN new_vendor_sites(p_last_update_date); FETCH new_vendor_sites BULK COLLECT INTO l_vendor_site_tab,l_country_tab,l_state_tab,l_city_tab, l_postal_tab; CLOSE new_vendor_sites;END IF;FOR i IN 1..l_vendor_site_tab.COUNT LOOP BEGIN WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches(p_country => null, p_country_region => null,p_state => null, p_city => l_city_tab(i), p_postal_code_from => l_postal_tab(i), p_postal_code_to => l_postal_tab(i), p_country_code => l_country_tab(i), p_country_region_code => null, p_state_code => l_state_tab(i), p_city_code => null, p_lang_code => userenv('LANG'), p_location_id => null, p_zone_flag => 'Y', x_status => l_status,x_regions => l_regions);EXCEPTION WHEN OTHERS THEN MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG(SQLERRM); NULL;END;IF l_regions.count > 0 THEN l_region_id_tab.EXTEND(l_regions.count);l_region_type_tab.EXTEND(l_regions.count);l_zone_level_tab.EXTEND(l_regions.count);FOR j IN 1..l_regions.COUNT LOOP l_region_id_tab(j) := l_regions(j).region_id;IF(l_regions(j).region_type = 10) THEN l_region_type_tab(j) := (10 * (10 - l_regions(j).zone_level))+ 1;ELSE l_region_type_tab(j) := (10 * (10 - l_regions(j).zone_level))+ 0;END IF;l_zone_level_tab(j) := l_regions(j).zone_level;END LOOP; FORALL k IN 1..l_regions.count INSERT INTO MRP_REGION_SITES(region_id,vendor_site_id, region_type, zone_level, last_update_date, last_updated_by, creation_date, created_by) VALUES (l_region_id_tab(k), l_vendor_site_tab(i),l_region_type_tab(k),l_zone_level_tab(k), v_current_date, v_current_user, v_current_date, v_current_user);END IF;END LOOP;COMMIT;Return(1);EXCEPTION WHEN OTHERS THEN MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG(SQLERRM);MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('There was an error in Mapping Region to Sites');Return(0);END MAP_REGION_TO_SITE ; END MRP_MAP_REG_SITE;

1: PACKAGE BODY MRP_MAP_REG_SITE AS FUNCTION MAP_REGION_TO_SITE (p_last_update_date in DATE) RETURN NUMBER IS Cursor regions_update is select max(LAST_UPDATE_DATE)from WSH_REGIONS; Cursor all_vendor_sites is select vendor_site_id,country,state,city,zip from PO_VENDOR_SITES_ALL; Cursor new_vendor_sites(p_date DATE) is select vendor_site_id,country,state,city,zip from PO_VENDOR_SITES_ALL where last_update_date >= p_date; TYPE VendorSiteTblTyp IS TABLE OF PO_VENDOR_SITES_ALL.VENDOR_SITE_ID%TYPE; TYPE CountryTblTyp IS TABLE OF PO_VENDOR_SITES_ALL.COUNTRY%TYPE; TYPE StateTblTyp IS TABLE OF PO_VENDOR_SITES_ALL.STATE%TYPE; TYPE CityTblTyp IS TABLE OF PO_VENDOR_SITES_ALL.CITY%TYPE; TYPE ZipTblTyp IS TABLE OF PO_VENDOR_SITES_ALL.ZIP%TYPE; TYPE NumTblTyp IS TABLE OF NUMBER; l_vendor_site_tab VendorSiteTblTyp ; l_country_tab CountryTblTyp; l_state_tab StateTblTyp; l_city_tab CityTblTyp; l_postal_tab ZipTblTyp; l_region_id_tab NumTblTyp := NumTblTyp(); l_region_type_tab NumTblTyp := NumTblTyp(); l_zone_level_tab NumTblTyp := NumTblTyp(); l_regions WSH_REGIONS_SEARCH_PKG.region_table; region_last_update DATE := NULL; l_status NUMBER; v_current_date DATE; v_current_user NUMBER;BEGIN SELECT SYSDATE, FND_GLOBAL.USER_ID INTO v_current_date, v_current_user FROM DUAL; OPEN regions_update; FETCH regions_update into region_last_update; CLOSE regions_update;IF region_last_update is NULL THEN Return(1); NULL;END IF; IF (p_last_update_date is NULL) OR (region_last_update >= p_last_update_date) THEN DELETE FROM MRP_REGION_SITES; OPEN all_vendor_sites; FETCH all_vendor_sites BULK COLLECT INTO l_vendor_site_tab,l_country_tab,l_state_tab,l_city_tab,l_postal_tab; CLOSE all_vendor_sites; ELSE OPEN new_vendor_sites(p_last_update_date); FETCH new_vendor_sites BULK COLLECT INTO l_vendor_site_tab,l_country_tab,l_state_tab,l_city_tab, l_postal_tab; CLOSE new_vendor_sites;END IF;FOR i IN 1..l_vendor_site_tab.COUNT LOOP BEGIN WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches(p_country => null, p_country_region => null,p_state => null, p_city => l_city_tab(i), p_postal_code_from => l_postal_tab(i), p_postal_code_to => l_postal_tab(i), p_country_code => l_country_tab(i), p_country_region_code => null, p_state_code => l_state_tab(i), p_city_code => null, p_lang_code => userenv('LANG'), p_location_id => null, p_zone_flag => 'Y', x_status => l_status,x_regions => l_regions);EXCEPTION WHEN OTHERS THEN MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG(SQLERRM); NULL;END;IF l_regions.count > 0 THEN l_region_id_tab.EXTEND(l_regions.count);l_region_type_tab.EXTEND(l_regions.count);l_zone_level_tab.EXTEND(l_regions.count);FOR j IN 1..l_regions.COUNT LOOP l_region_id_tab(j) := l_regions(j).region_id;IF(l_regions(j).region_type = 10) THEN l_region_type_tab(j) := (10 * (10 - l_regions(j).zone_level))+ 1;ELSE l_region_type_tab(j) := (10 * (10 - l_regions(j).zone_level))+ 0;END IF;l_zone_level_tab(j) := l_regions(j).zone_level;END LOOP; FORALL k IN 1..l_regions.count INSERT INTO MRP_REGION_SITES(region_id,vendor_site_id, region_type, zone_level, last_update_date, last_updated_by, creation_date, created_by) VALUES (l_region_id_tab(k), l_vendor_site_tab(i),l_region_type_tab(k),l_zone_level_tab(k), v_current_date, v_current_user, v_current_date, v_current_user);END IF;END LOOP;COMMIT;Return(1);EXCEPTION WHEN OTHERS THEN MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG(SQLERRM);MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('There was an error in Mapping Region to Sites');Return(0);END MAP_REGION_TO_SITE ; END MRP_MAP_REG_SITE;