[Home] [Help]
65: CURSOR get_region_id IS
66: SELECT WSH_REGIONS_s.nextval from dual;
67:
68: CURSOR get_interface_region_id IS
69: SELECT WSH_REGIONS_INTERFACE_S.nextval from dual;
70:
71: l_region_id NUMBER;
72:
73: --
163: IF (p_tl_only_flag <> 'Y') THEN
164:
165: -- fnd_file.put_line(fnd_file.Log, 'inserting into fte regions interface, interface and tl only flag both = Y');
166:
167: INSERT INTO WSH_REGIONS_INTERFACE (
168: REGION_ID,
169: REGION_TYPE,
170: PARENT_REGION_ID,
171: COUNTRY_CODE,
1724: IF (l_region_id <> -1) THEN
1725: x_region_id := l_region_id;
1726:
1727: IF (p_interface_flag = 'Y') THEN
1728: UPDATE WSH_REGIONS_INTERFACE
1729: SET COUNTRY_CODE = p_country_code,
1730: COUNTRY_REGION_CODE = p_country_region_code,
1731: STATE_CODE = p_state_code,
1732: CITY_CODE = p_city_code,
2047: x_error_msg OUT NOCOPY VARCHAR2) IS
2048:
2049: CURSOR lock_rows IS
2050: SELECT r.region_id
2051: FROM wsh_regions_interface r, wsh_regions_tl_interface t
2052: WHERE r.region_id = t.region_id AND
2053: r.region_id = p_region_id AND
2054: t.language = nvl(p_lang_code, t.language)
2055: FOR UPDATE OF r.region_id NOWAIT;
2088: FETCH lock_rows INTO l_region_id;
2089: CLOSE lock_rows;
2090:
2091: DELETE FROM wsh_regions_tl_interface WHERE region_id = l_region_id AND language= nvl(p_lang_code, language);
2092: DELETE FROM wsh_regions_interface WHERE region_id = l_region_id;
2093:
2094: ELSE
2095: x_status := 2;
2096: x_error_msg := 'Cannot delete region';
2379:
2380:
2381: CURSOR lock_row IS
2382: SELECT w.region_id, w.country_code, w.state_code, w.city_code
2383: FROM wsh_regions_interface w
2384: WHERE w.region_id = p_region_id
2385: FOR UPDATE OF w.region_id NOWAIT;
2386:
2387: CURSOR lock_row_tl IS
3695: TL.COUNTY,
3696: TL.POSTAL_CODE_FROM,
3697: TL.POSTAL_CODE_TO,
3698: TL.LANGUAGE
3699: FROM WSH_REGIONS_INTERFACE R,
3700: WSH_REGIONS_TL_INTERFACE TL
3701: WHERE R.REGION_ID = TL.REGION_ID
3702: AND TL.ZONE IS NULL -- We are not processing zones here .
3703: AND PROCESSED_FLAG is null
3995: l_del_region_id(l_del_count) := l_region_id_rec(i);
3996: END IF;
3997: END LOOP;
3998:
3999: -- Bulk Updation in WSH_REGIONS_INTERFACE table
4000: IF ( l_upd_count > 0 ) THEN
4001: l_upd_count := 0;
4002:
4003: FORALL i in l_upd_region_id.first..l_upd_region_id.last
4000: IF ( l_upd_count > 0 ) THEN
4001: l_upd_count := 0;
4002:
4003: FORALL i in l_upd_region_id.first..l_upd_region_id.last
4004: UPDATE wsh_regions_interface
4005: SET processed_flag = 'Y'
4006: WHERE region_id = l_upd_region_id(i);
4007:
4008: -- Deleting region ids from array
4008: -- Deleting region ids from array
4009: l_upd_region_id.DELETE;
4010: END IF;
4011:
4012: -- Bulk Deletion in WSH_REGIONS_INTERFACE table
4013: IF ( l_del_count > 0 ) THEN
4014: l_del_count := 0;
4015: FORALL i in l_del_region_id.first..l_del_region_id.last
4016: DELETE FROM WSH_REGIONS_INTERFACE WHERE REGION_ID = l_del_region_id(i);
4012: -- Bulk Deletion in WSH_REGIONS_INTERFACE table
4013: IF ( l_del_count > 0 ) THEN
4014: l_del_count := 0;
4015: FORALL i in l_del_region_id.first..l_del_region_id.last
4016: DELETE FROM WSH_REGIONS_INTERFACE WHERE REGION_ID = l_del_region_id(i);
4017:
4018: FORALL i in l_del_region_id.first..l_del_region_id.last
4019: DELETE FROM WSH_REGIONS_TL_INTERFACE WHERE REGION_ID = l_del_region_id(i);
4020:
4073: l_dummy_state_code VARCHAR2(3);
4074:
4075: CURSOR total_regions IS
4076: SELECT count(*)
4077: FROM wsh_Regions_interface
4078: WHERE processed_flag is null;
4079:
4080: CURSOR more_regions IS
4081: SELECT 1
4078: WHERE processed_flag is null;
4079:
4080: CURSOR more_regions IS
4081: SELECT 1
4082: FROM wsh_Regions_interface
4083: WHERE processed_flag is null;
4084:
4085: l_tmp NUMBER;
4086: l_good_count NUMBER := 0;