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;
[Home] [Help]