[Home] [Help]
1452: IS
1453:
1454: Cursor regions_update is
1455: select max(LAST_UPDATE_DATE)
1456: from WSH_REGIONS;
1457:
1458: Cursor po_vendors_update(p_date DATE) is
1459: select vendor_site_id
1460: from PO_VENDOR_SITES_ALL
1489: l_region_id_tab NumTblTyp := NumTblTyp();
1490: l_region_type_tab NumTblTyp := NumTblTyp();
1491: l_zone_level_tab NumTblTyp := NumTblTyp();
1492:
1493: l_regions WSH_REGIONS_SEARCH_PKG.region_table;
1494:
1495: region_last_update DATE := NULL;
1496: l_status NUMBER;
1497: lv_sql_stmt VARCHAR2(8000);
1516: IF region_last_update is NULL THEN
1517: Return(1);
1518: NULL;
1519: END IF;
1520: -- If wsh_regions has undergone change after last Collection, all mappings are re-established.
1521: IF (p_last_update_date is NULL) OR (region_last_update >= p_last_update_date) THEN
1522: -- delete mrp_region_sites completely.
1523: DELETE FROM MRP_REGION_SITES;
1524:
1549: NULL;
1550: END;
1551: END LOOP;
1552: --
1553: -- If wsh_regions has not undergone any change after the last Collection, map only new vendor sites.
1554: OPEN new_vendor_sites(p_last_update_date);
1555: FETCH new_vendor_sites BULK COLLECT
1556: INTO l_vendor_site_tab,
1557: l_country_tab,
1566: BEGIN
1567: MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('Vendor id passed to shippin API: '||l_vendor_site_tab(i) );
1568: MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('State code passed to shippin API: '||l_state_tab(i) );
1569: MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('Language code passed to shippin API: '||l_lang_tab(i) );
1570: WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches(
1571: p_country => null, -- p_country
1572: p_country_region => null, -- p_country_region
1573: p_state => l_state_tab(i), -- p_state
1574: p_city => l_city_tab(i), -- p_city
2159: wrl.region_type
2160: from
2161: wsh_region_locations wrl,
2162: wsh_zone_regions z,
2163: wsh_regions_v a ,
2164: HZ_PARTY_SITES l
2165: where wrl.region_id = z.region_id
2166: and z.parent_region_id = a.region_id
2167: and a.region_type = 10