[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAP_ID
FROM hz_geo_struct_map
WHERE COUNTRY_CODE = p_country
AND LOC_TBL_NAME = p_loc_table
AND NVL(ADDRESS_STYLE,'X_NOSTYLE_X') = NVL(p_address_style,'X_NOSTYLE_X');
SELECT MAP_ID
FROM hz_geo_struct_map
WHERE COUNTRY_CODE = p_country
AND LOC_TBL_NAME = p_loc_table
AND ADDRESS_STYLE IS NULL;
SELECT hgo.identifier_value
FROM hz_geography_identifiers hgo
WHERE hgo.identifier_subtype = 'ISO_COUNTRY_CODE'
AND hgo.identifier_type = 'CODE'
AND hgo.geography_use = 'MASTER_REF'
AND hgo.geography_type = 'COUNTRY'
AND hgo.primary_flag = 'Y' -- fix for bug 5400607 (Nishant 20-Jul-2006)
AND EXISTS ( SELECT '1'
FROM hz_geography_identifiers hgi
WHERE hgi.geography_use = 'MASTER_REF'
AND hgi.geography_type = 'COUNTRY'
AND hgi.geography_id = hgo.geography_id
AND UPPER(hgi.identifier_value) = l_country
);
SELECT COUNT(*)
INTO l_run_type_count
FROM ar_lookups
WHERE lookup_type = 'HZ_GEO_GNR_RUN_TYPE'
AND lookup_code = p_run_type
AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,SYSDATE) AND NVL(end_date_active, SYSDATE+1)
AND enabled_flag = 'Y';
SELECT COUNT(*)
INTO l_usage_count
FROM ar_lookups
WHERE lookup_type = 'HZ_GEOGRAPHY_USAGE'
AND lookup_code = p_usage_code
AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,SYSDATE) AND NVL(end_date_active, SYSDATE+1)
AND enabled_flag = 'Y';
SELECT COUNT(*)
INTO l_country_code_count
FROM fnd_territories
WHERE territory_code = p_country_code
AND obsolete_flag = 'N';
* for the records selected from the respective table name
* The records selected by this worker will be determined by
* the worker number of this worker and total number of
* workers. MOD function will be used for this.
* mod(location_id, p_num_workers) = p_worker_number
* location id 200 to 210. then
* number of workers 5. Worker number = 1
* This worker will pick up 201 and 206
* number of workers 5. Worker number = 2
* This worker will pick up 202 and 207
* number of workers 5. Worker number = 3
* This worker will pick up 203 and 208
* number of workers 5. Worker number = 4
* This worker will pick up 204 and 209
* number of workers 5. Worker number=5 will be assigned worker number=0
* This worker will pick up 200 and 210
*
* RELATED PACKAGES
*
* PUBLIC VARIABLES
*
* PUBLIC FUNCTIONS
*
* NOTES
*
* MODIFICATION HISTORY
*
* 20-JAN-2003 Satyadeep o Created.
* Chandrashekar
* 29-JUN-2006 Nishant Singhai Bug 5257371
*
* 25-JAN-2008 Neeraj Shinde Bug 6750566
*/
PROCEDURE process_gnr_worker (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_worker_number IN VARCHAR2,
p_location_table_name IN VARCHAR2,
p_run_type IN VARCHAR2,
p_usage_code IN VARCHAR2,
p_country_code IN VARCHAR2,
p_from_location_id IN VARCHAR2,
p_to_location_id IN VARCHAR2,
p_start_date IN VARCHAR2,
p_end_date IN VARCHAR2,
p_num_workers IN VARCHAR2
) IS
l_return_status VARCHAR2(30);
SELECT loc.location_id
FROM hr_locations_all loc
WHERE (((p_from_location_id IS NOT NULL and p_to_location_id IS NOT NULL) and
(loc.location_id BETWEEN p_from_location_id and p_to_location_id )) or
((p_from_location_id IS NOT NULL and p_to_location_id IS NULL) and
(loc.location_id >= p_from_location_id)) or
(p_from_location_id IS NULL and p_to_location_id IS NULL))
AND ((p_start_date IS NULL and p_end_date IS NULL) or
(p_start_date IS NOT NULL and p_end_date IS NULL and
trunc(loc.creation_date) >= p_start_date) or
((p_start_date IS NOT NULL and p_end_date IS NOT NULL) and
(trunc(loc.creation_date) BETWEEN p_start_date and p_end_date)))
AND ((p_country_code IS NULL) OR
((p_country_code IS NOT NULL) AND
(loc.country = UPPER(p_country_code))) OR
((p_country_code IS NOT NULL) AND
(loc.country IN (SELECT hgo.identifier_value
FROM hz_geography_identifiers hgo
WHERE EXISTS ( SELECT '1'
FROM hz_geography_identifiers hgi
WHERE hgi.identifier_subtype = 'ISO_COUNTRY_CODE'
AND hgi.identifier_type = 'CODE'
AND hgi.geography_use = 'MASTER_REF'
AND hgi.geography_type = 'COUNTRY'
AND hgi.geography_id = hgo.geography_id
AND UPPER(hgi.identifier_value) = p_country_code
)
)
))
)
AND NOT EXISTS (
SELECT '1'
FROM hz_geo_name_reference_log gnr
WHERE gnr.location_table_name = p_table_name
AND gnr.usage_code = DECODE(p_usage_code,'ALL',gnr.usage_code,p_usage_code)
AND loc.location_id = gnr.location_id
)
AND mod(loc.location_id, p_num_workers) = p_worker_number
ORDER BY loc.location_id;
SELECT loc.location_id
FROM hr_locations_all loc
WHERE (((p_from_location_id IS NOT NULL and p_to_location_id IS NOT NULL) and
(loc.location_id BETWEEN p_from_location_id and p_to_location_id )) or
((p_from_location_id IS NOT NULL and p_to_location_id IS NULL) and
(loc.location_id >= p_from_location_id)) or
(p_from_location_id IS NULL and p_to_location_id IS NULL))
AND ((p_start_date IS NULL and p_end_date IS NULL) or
(p_start_date IS NOT NULL and p_end_date IS NULL and
trunc(loc.creation_date) >= p_start_date) or
((p_start_date IS NOT NULL and p_end_date IS NOT NULL) and
(trunc(loc.creation_date) BETWEEN p_start_date and p_end_date)))
AND ((p_country_code IS NULL) OR
((p_country_code IS NOT NULL) AND
(loc.country = UPPER(p_country_code))) OR
((p_country_code IS NOT NULL) AND
(loc.country IN (SELECT hgo.identifier_value
FROM hz_geography_identifiers hgo
WHERE EXISTS ( SELECT '1'
FROM hz_geography_identifiers hgi
WHERE hgi.identifier_subtype = 'ISO_COUNTRY_CODE'
AND hgi.identifier_type = 'CODE'
AND hgi.geography_use = 'MASTER_REF'
AND hgi.geography_type = 'COUNTRY'
AND hgi.geography_id = hgo.geography_id
AND UPPER(hgi.identifier_value) = p_country_code
)
)
))
)
AND EXISTS (
SELECT '1'
FROM hz_geo_name_reference_log gnr
WHERE gnr.location_table_name = p_table_name
AND gnr.usage_code = DECODE(p_usage_code,'ALL',gnr.usage_code,p_usage_code)
AND gnr.map_status = 'E'
AND loc.location_id = gnr.location_id
)
AND mod(loc.location_id, p_num_workers) = p_worker_number
ORDER BY loc.location_id;
SELECT loc.location_id
FROM hr_locations_all loc
WHERE (((p_from_location_id IS NOT NULL and p_to_location_id IS NOT NULL) and
(loc.location_id BETWEEN p_from_location_id and p_to_location_id )) or
((p_from_location_id IS NOT NULL and p_to_location_id IS NULL) and
(loc.location_id >= p_from_location_id)) or
(p_from_location_id IS NULL and p_to_location_id IS NULL))
AND ((p_start_date IS NULL and p_end_date IS NULL) or
(p_start_date IS NOT NULL and p_end_date IS NULL and
trunc(loc.creation_date) >= p_start_date) or
((p_start_date IS NOT NULL and p_end_date IS NOT NULL) and
(trunc(loc.creation_date) BETWEEN p_start_date and p_end_date)))
AND ((p_country_code IS NULL) OR
((p_country_code IS NOT NULL) AND
(loc.country = UPPER(p_country_code))) OR
((p_country_code IS NOT NULL) AND
(loc.country IN (SELECT hgo.identifier_value
FROM hz_geography_identifiers hgo
WHERE EXISTS ( SELECT '1'
FROM hz_geography_identifiers hgi
WHERE hgi.identifier_subtype = 'ISO_COUNTRY_CODE'
AND hgi.identifier_type = 'CODE'
AND hgi.geography_use = 'MASTER_REF'
AND hgi.geography_type = 'COUNTRY'
AND hgi.geography_id = hgo.geography_id
AND UPPER(hgi.identifier_value) = p_country_code
)
)
))
)
AND mod(loc.location_id, p_num_workers) = p_worker_number
ORDER BY loc.location_id;
SELECT LOCATION_ID,
ADDRESS_STYLE,
COUNTRY,
STATE,
PROVINCE,
COUNTY,
CITY,
POSTAL_CODE,
POSTAL_PLUS4_CODE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10
FROM hz_locations loc
WHERE (((p_from_location_id IS NOT NULL and p_to_location_id IS NOT NULL) and
(location_id BETWEEN p_from_location_id and p_to_location_id )) or
((p_from_location_id IS NOT NULL and p_to_location_id IS NULL) and
( location_id >= p_from_location_id)) or
(p_from_location_id IS NULL and p_to_location_id IS NULL))
AND ((p_start_date IS NULL and p_end_date IS NULL) or
(p_start_date IS NOT NULL and p_end_date IS NULL and
trunc(creation_date) >= p_start_date) or
((p_start_date IS NOT NULL and p_end_date IS NOT NULL) and
(trunc(creation_date) BETWEEN p_start_date and p_end_date)))
AND ((p_country_code IS NULL) OR
((p_country_code IS NOT NULL) AND
(country = p_country_code)) OR
((p_country_code IS NOT NULL) AND
(country IN (SELECT hgo.identifier_value
FROM hz_geography_identifiers hgo
WHERE EXISTS ( SELECT '1'
FROM hz_geography_identifiers hgi
WHERE hgi.identifier_subtype = 'ISO_COUNTRY_CODE'
AND hgi.identifier_type = 'CODE'
AND hgi.geography_use = 'MASTER_REF'
AND hgi.geography_type = 'COUNTRY'
AND hgi.geography_id = hgo.geography_id
AND UPPER(hgi.identifier_value) = p_country_code
)
)
))
)
AND NOT EXISTS (
SELECT '1'
FROM hz_geo_name_reference_log gnr
WHERE gnr.location_table_name = p_table_name
AND gnr.usage_code = DECODE(p_usage_code,'ALL',gnr.usage_code,p_usage_code)
AND loc.location_id = gnr.location_id
)
AND mod(location_id, p_num_workers) = p_worker_number
ORDER BY location_id;
SELECT LOCATION_ID,
ADDRESS_STYLE,
COUNTRY,
STATE,
PROVINCE,
COUNTY,
CITY,
POSTAL_CODE,
POSTAL_PLUS4_CODE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10
FROM hz_locations loc
WHERE (((p_from_location_id IS NOT NULL and p_to_location_id IS NOT NULL) and
(location_id BETWEEN p_from_location_id and p_to_location_id )) or
((p_from_location_id IS NOT NULL and p_to_location_id IS NULL) and
( location_id >= p_from_location_id)) or
(p_from_location_id IS NULL and p_to_location_id IS NULL))
AND ((p_start_date IS NULL and p_end_date IS NULL) or
(p_start_date IS NOT NULL and p_end_date IS NULL and
trunc(creation_date) >= p_start_date) or
((p_start_date IS NOT NULL and p_end_date IS NOT NULL) and
(trunc(creation_date) BETWEEN p_start_date and p_end_date)))
AND ((p_country_code IS NULL) OR
((p_country_code IS NOT NULL) AND
(country = p_country_code)) OR
((p_country_code IS NOT NULL) AND
(country IN (SELECT hgo.identifier_value
FROM hz_geography_identifiers hgo
WHERE EXISTS ( SELECT '1'
FROM hz_geography_identifiers hgi
WHERE hgi.identifier_subtype = 'ISO_COUNTRY_CODE'
AND hgi.identifier_type = 'CODE'
AND hgi.geography_use = 'MASTER_REF'
AND hgi.geography_type = 'COUNTRY'
AND hgi.geography_id = hgo.geography_id
AND UPPER(hgi.identifier_value) = p_country_code
)
)
))
)
AND EXISTS (
SELECT '1'
FROM hz_geo_name_reference_log gnr
WHERE gnr.location_table_name = p_table_name
AND gnr.usage_code = DECODE(p_usage_code,'ALL',gnr.usage_code,p_usage_code)
AND gnr.map_status = 'E'
AND loc.location_id = gnr.location_id
)
AND mod(location_id, p_num_workers) = p_worker_number
ORDER BY location_id;
SELECT LOCATION_ID,
ADDRESS_STYLE,
COUNTRY,
STATE,
PROVINCE,
COUNTY,
CITY,
POSTAL_CODE,
POSTAL_PLUS4_CODE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10
FROM hz_locations loc
WHERE (((p_from_location_id IS NOT NULL and p_to_location_id IS NOT NULL) and
(location_id BETWEEN p_from_location_id and p_to_location_id )) or
((p_from_location_id IS NOT NULL and p_to_location_id IS NULL) and
( location_id >= p_from_location_id)) or
(p_from_location_id IS NULL and p_to_location_id IS NULL))
AND ((p_start_date IS NULL and p_end_date IS NULL) or
(p_start_date IS NOT NULL and p_end_date IS NULL and
trunc(creation_date) >= p_start_date) or
((p_start_date IS NOT NULL and p_end_date IS NOT NULL) and
(trunc(creation_date) BETWEEN p_start_date and p_end_date)))
AND ((p_country_code IS NULL) OR
((p_country_code IS NOT NULL) AND
(country = p_country_code)) OR
((p_country_code IS NOT NULL) AND
(country IN (SELECT hgo.identifier_value
FROM hz_geography_identifiers hgo
WHERE EXISTS ( SELECT '1'
FROM hz_geography_identifiers hgi
WHERE hgi.identifier_subtype = 'ISO_COUNTRY_CODE'
AND hgi.identifier_type = 'CODE'
AND hgi.geography_use = 'MASTER_REF'
AND hgi.geography_type = 'COUNTRY'
AND hgi.geography_id = hgo.geography_id
AND UPPER(hgi.identifier_value) = p_country_code
)
)
))
)
AND mod(location_id, p_num_workers) = p_worker_number
ORDER BY location_id;
SELECT loc.LOCATION_ID,
loc.ADDRESS_STYLE,
loc.COUNTRY,
loc.STATE,
loc.PROVINCE,
loc.COUNTY,
loc.CITY,
loc.POSTAL_CODE,
loc.POSTAL_PLUS4_CODE,
loc.ATTRIBUTE1,
loc.ATTRIBUTE2,
loc.ATTRIBUTE3,
loc.ATTRIBUTE4,
loc.ATTRIBUTE5,
loc.ATTRIBUTE6,
loc.ATTRIBUTE7,
loc.ATTRIBUTE8,
loc.ATTRIBUTE9,
loc.ATTRIBUTE10
FROM hz_locations loc
WHERE
-- Only locations which had Loc_assignments record before
/*Bug 6750566 Changes Start
EXISTS (
SELECT NULL FROM hz_loc_assignments_obs hlo
WHERE loc.location_id = hlo.location_id
)
*/
EXISTS (
SELECT NULL FROM hz_party_sites hps,
hz_cust_acct_sites_all hcasa
WHERE loc.location_id = hps.location_id
AND hps.party_site_id = hcasa.party_site_id
)
--Bug 6750566 Changes End
AND MOD(loc.location_id, lp_num_workers) = lp_worker_number
--ORDER BY loc.location_id
;
SELECT DISTINCT country_code
FROM hz_geo_struct_map
WHERE loc_tbl_name = 'HZ_LOCATIONS';
HZ_GNR_PKG.delete_gnr(p_locId => l_cur_location_id,
p_locTbl => p_location_table_name,
x_status => l_return_status
);
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
FND_MSG_PUB.Delete_Msg;
PROCEDURE delete_gnr(
p_locId IN NUMBER,
p_locTbl IN VARCHAR2,
x_status OUT NOCOPY VARCHAR2
) IS
BEGIN
delete from hz_geo_name_references
where location_table_name = p_locTbl
and location_id = p_locId;
delete from hz_geo_name_reference_log
where location_table_name = p_locTbl
and location_id = p_locId;
END delete_gnr;
SELECT country,style
FROM hr_locations_all
WHERE location_id = p_loc_id;
SELECT MAP_ID,USAGE_ID,USAGE_CODE
FROM hz_address_usages
WHERE map_id = p_map_id
AND status_flag = 'A'
ORDER BY usage_id;