[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
3690: TL.COUNTY,
3691: TL.POSTAL_CODE_FROM,
3692: TL.POSTAL_CODE_TO,
3693: TL.LANGUAGE
3694: FROM WSH_REGIONS_INTERFACE R,
3695: WSH_REGIONS_TL_INTERFACE TL
3696: WHERE R.REGION_ID = TL.REGION_ID
3697: AND TL.ZONE IS NULL -- We are not processing zones here .
3698: AND PROCESSED_FLAG is null
3986: l_del_region_id(l_del_count) := l_region_id_rec(i);
3987: END IF;
3988: END LOOP;
3989:
3990: -- Bulk Updation in WSH_REGIONS_INTERFACE table
3991: IF ( l_upd_count > 0 ) THEN
3992: l_upd_count := 0;
3993:
3994: FORALL i in l_upd_region_id.first..l_upd_region_id.last
3991: IF ( l_upd_count > 0 ) THEN
3992: l_upd_count := 0;
3993:
3994: FORALL i in l_upd_region_id.first..l_upd_region_id.last
3995: UPDATE wsh_regions_interface
3996: SET processed_flag = 'Y'
3997: WHERE region_id = l_upd_region_id(i);
3998:
3999: -- Deleting region ids from array
3999: -- Deleting region ids from array
4000: l_upd_region_id.DELETE;
4001: END IF;
4002:
4003: -- Bulk Deletion in WSH_REGIONS_INTERFACE table
4004: IF ( l_del_count > 0 ) THEN
4005: l_del_count := 0;
4006: FORALL i in l_del_region_id.first..l_del_region_id.last
4007: DELETE FROM WSH_REGIONS_INTERFACE WHERE REGION_ID = l_del_region_id(i);
4003: -- Bulk Deletion in WSH_REGIONS_INTERFACE table
4004: IF ( l_del_count > 0 ) THEN
4005: l_del_count := 0;
4006: FORALL i in l_del_region_id.first..l_del_region_id.last
4007: DELETE FROM WSH_REGIONS_INTERFACE WHERE REGION_ID = l_del_region_id(i);
4008:
4009: FORALL i in l_del_region_id.first..l_del_region_id.last
4010: DELETE FROM WSH_REGIONS_TL_INTERFACE WHERE REGION_ID = l_del_region_id(i);
4011:
4058: l_error_msg_text VARCHAR2(1000);
4059:
4060: CURSOR total_regions IS
4061: SELECT count(*)
4062: FROM wsh_Regions_interface
4063: WHERE processed_flag is null;
4064:
4065: CURSOR more_regions IS
4066: SELECT 1
4063: WHERE processed_flag is null;
4064:
4065: CURSOR more_regions IS
4066: SELECT 1
4067: FROM wsh_Regions_interface
4068: WHERE processed_flag is null;
4069:
4070: l_tmp NUMBER;
4071: l_good_count NUMBER := 0;