[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAP_STATUS
FROM HZ_GEO_NAME_REFERENCE_LOG
WHERE LOCATION_TABLE_NAME = p_location_table_name
AND LOCATION_ID = p_location_id;
Function to tell if the location can be updated or not. It directly calls
ARH_ADDR_PKG.check_tran_for_all_accts to do this validation. This function is
just a wrapper for ease of use in GNR code
EXTERNAL PROCEDURES/FUNCTIONS ACCESSED :
ARH_ADDR_PKG
ARGUMENTS :
IN p_location_id NUMBER
RETURNS : BOOLEAN
TRUE : Location updation is allowed
FALSE : Location updation is not allowed
MODIFICATION HISTORY:
16-FEB-2006 Nishant Singhai Created
**/
FUNCTION location_updation_allowed(p_location_id IN NUMBER) RETURN BOOLEAN IS
BEGIN
-- Tax location Validation
IF ARH_ADDR_PKG.check_tran_for_all_accts(p_location_id) THEN
-- Transaction exists
RETURN FALSE;
Procedure : pre_location_update
DESCRIPTION :
Procedure to do pre-update processing for a given location record. This will
be used in GNR program, where it updates the location components.
EXTERNAL PROCEDURES/FUNCTIONS ACCESSED :
HZ_LOCATION_V2PUB
hz_fuzzy_pub
hz_timezone_pub
ARGUMENTS :
IN p_old_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
IN OUT p_new_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
IN OUT p_other_location_params HZ_GNR_UTIL_PKG.location_other_param_rec_type
(extendible - for future use)
MODIFICATION HISTORY:
16-FEB-2006 Nishant Singhai Created
**/
PROCEDURE pre_location_update (
p_old_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
p_new_location_rec IN OUT NOCOPY HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
p_other_location_params IN OUT NOCOPY HZ_GNR_UTIL_PKG.loc_other_param_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_changed_flag VARCHAR2(10);
fnd_msg_pub.delete_msg(l_msg_count - l_message_count + 1 - i);
END pre_location_update;
Procedure : post_location_update
DESCRIPTION :
Procedure to do post-update processing for a given location record. This will
be used in GNR program, where it updates the location components.
EXTERNAL PROCEDURES/FUNCTIONS ACCESSED :
HZ_LOCATION_V2PUB
HZ_UTILITY_V2PUB
HZ_DQM_SYNC
HZ_BUSINESS_EVENT_V2PVT
HZ_POPULATE_BOT_PKG
ARGUMENTS :
IN p_old_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
IN OUT p_new_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
IN OUT p_other_location_params HZ_GNR_UTIL_PKG.location_other_param_rec_type
(extendible - for future use)
MODIFICATION HISTORY:
16-FEB-2006 Nishant Singhai Created
**/
PROCEDURE post_location_update (
p_old_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
p_new_location_rec IN OUT NOCOPY HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
p_other_location_params IN OUT NOCOPY HZ_GNR_UTIL_PKG.loc_other_param_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_last_updated_by NUMBER; -- hz_utility_v2pub.last_updated_by;
l_last_update_date DATE; -- hz_utility_v2pub.last_update_date;
l_last_update_login NUMBER; -- hz_utility_v2pub.last_update_login;
l_program_update_date DATE; -- hz_utility_v2pub.program_update_date;
l_last_updated_by := hz_utility_v2pub.last_updated_by;
l_last_update_date := hz_utility_v2pub.last_update_date;
l_last_update_login := hz_utility_v2pub.last_update_login;
l_program_update_date := hz_utility_v2pub.program_update_date;
SELECT hps.party_id
FROM hz_party_sites hps
WHERE hps.location_id = p_new_location_rec.location_id
AND hps.identifying_address_flag = 'Y';
SELECT party_id
INTO l_party_id
FROM hz_parties
WHERE party_id = l_party_id
FOR UPDATE NOWAIT;
UPDATE hz_parties
SET country = p_new_location_rec.country,
address1 = p_new_location_rec.address1,
address2 = p_new_location_rec.address2,
address3 = p_new_location_rec.address3,
address4 = p_new_location_rec.address4,
city = p_new_location_rec.city,
postal_code = p_new_location_rec.postal_code,
state = p_new_location_rec.state,
province = p_new_location_rec.province,
county = p_new_location_rec.county,
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login,
request_id = l_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
WHERE party_id = l_party_id;
HZ_BUSINESS_EVENT_V2PVT.update_location_event (
p_new_location_rec,
p_old_location_rec);
END post_location_update;
* This is to delete the rows from GNR table
* for a given combination of location id and table
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
* None. Only gnrins and gnrl in this package
* procedure use this.
* ARGUMENTS
* IN:
*
* p_locId Location Identifier
* p_locTbl Location Table
*
* OUT:
* x_status Y in case of success, otherwise error message name
*
* NOTES
*
*
* MODIFICATION HISTORY
*
*
*/
--------------------------------------
PROCEDURE delGNR (
p_locid IN number,
p_loctbl IN varchar2,
x_status OUT NOCOPY varchar2
) IS
BEGIN
-- initializing the return status
x_status := fnd_api.g_ret_sts_success;
DELETE FROM hz_geo_name_references
WHERE location_id = p_locid AND
location_table_name = p_loctbl;
l_sql1 := 'select map_id from hz_geo_struct_map where loc_tbl_name =:tbl and
country_code = :cntry and address_style is null';
l_sql1 := 'select map_id from hz_geo_struct_map where loc_tbl_name =:tbl and
country_code = :cntry and address_style = :style';
SELECT loc_seq_num, loc_component,
geography_type, geo_element_col
FROM hz_geo_struct_map_dtl
WHERE map_id = cp_map_id
ORDER BY loc_seq_num ASC;
l_sql_1 := 'select ';
l_sql_3 := 'select country, style from hr_locations_all where location_id = :id and rownum = 1 ';
l_sql_2 := 'select country, address_style from hz_locations where location_id = :id and rownum =1 ';
l_sql_4 := 'select 1 from fnd_territories f where f.territory_code = :code';
* this private procedure is used to insert or update the
* gnr table.
* this procedure will update if the same location id and
* geography id combination is existing otherwise this will insert.
*
* external procedures/functions accessed
*
* arguments
* in:
*
* p_locid location identifier
* p_loctbl location table
*
* p_maptbl table of records that has location sequence number,
* geo element, type and loc components and their values
*
* out:
*
* x_status procedure status
*
*
* exceptions raised
*
*
* notes
*
*
* modification history
*
*
*/
-----------------------------------------------------------
PROCEDURE gnrins (
p_locid IN number,
p_loctbl IN varchar2,
p_maptbl IN maploc_rec_tbl_type,
x_status OUT NOCOPY varchar2
) IS
l_debug_prefix VARCHAR2(30) := '';
l_last_updated_by NUMBER; -- hz_utility_v2pub.last_updated_by;
l_last_update_date DATE; -- hz_utility_v2pub.last_update_date;
l_last_update_login NUMBER; -- hz_utility_v2pub.last_update_login;
1. delete the locId and locTbl combination
2. loop through the table of records and insert
*/
-- Initialize variables (perf improvement bug 5130993)
l_last_updated_by := hz_utility_v2pub.last_updated_by;
l_last_update_date := hz_utility_v2pub.last_update_date;
l_last_update_login := hz_utility_v2pub.last_update_login;
INSERT INTO hz_geo_name_references
(location_id, geography_id, location_table_name,
object_version_number, geography_type, last_updated_by,
creation_date, created_by, last_update_date,
last_update_login, program_id, program_login_id,
program_application_id,request_id)
VALUES
(p_locid, p_maptbl(i).geography_id,p_loctbl,
1, p_maptbl(i).geography_type, l_last_updated_by,
l_creation_date, l_created_by,
l_last_update_date, l_last_update_login,
l_program_id, l_conc_login_id,
l_program_application_id, l_request_id);
* this private procedure is used to insert or update the
* gnr log table. this log table will be updated irrespective
* of whether the gnring of a location record is sucessfull or not.
*
* external procedures/functions accessed
*
* arguments
* in:
* p_locid location identifier
* p_loctbl location table
* p_mapstatus sucess, error or warning
* in out:
* x_status procedure return status/message name
* that must be logged along with map status.
*
*
* exceptions raised
*
*
* notes
*
*
* modification history
*
*
*/
PROCEDURE gnrl (
p_locid IN number,
p_loctbl IN varchar2,
p_mapStatus IN varchar2,
p_mesg IN varchar2
) IS
l_status varchar2(1);
l_last_updated_by NUMBER; -- hz_utility_v2pub.last_updated_by;
l_last_update_date DATE; -- hz_utility_v2pub.last_update_date;
l_last_update_login NUMBER; -- hz_utility_v2pub.last_update_login;
l_last_updated_by := hz_utility_v2pub.last_updated_by;
l_last_update_date := hz_utility_v2pub.last_update_date;
l_last_update_login := hz_utility_v2pub.last_update_login;
DELETE FROM hz_geo_name_reference_log
WHERE location_id = p_locid
AND location_table_name = p_loctbl;
INSERT INTO hz_geo_name_reference_log
(location_id, location_table_name,
message_text,
object_version_number, map_status,
last_updated_by, creation_date,
created_by, last_update_date,
last_update_login, program_id,
program_login_id,program_application_id,request_id)
VALUES
(p_locid, p_loctbl, p_mesg, 1, p_mapStatus,
l_last_updated_by, l_creation_date,
l_created_by, l_last_update_date,
l_last_update_login, l_program_id,
l_conc_login_id, l_program_application_id, l_request_id);
hz_utility_v2pub.debug(p_message=>'unable to insert into GNRL tbl',
p_prefix=>'ERROR',
p_msg_level=>fnd_log.level_error);
l_select varchar2(1000); -- (2000)
l_select := 'SELECT g.GEOGRAPHY_ID,g.MULTIPLE_PARENT_FLAG';
l_select := l_select||',GEOGRAPHY_ELEMENT1_ID';
l_select := l_select||','||P_MAP_DTLS_TBL(i).GEO_ELEMENT_COL||'_ID';
l_where := l_where||' AND EXISTS( SELECT NULL FROM HZ_GEOGRAPHY_IDENTIFIERS i'||i;
l_where := l_where||' AND (EXISTS( SELECT /*+ index(i'||i||',HZ_GEOGRAPHY_IDENTIFIERS_U1) */ NULL FROM HZ_GEOGRAPHY_IDENTIFIERS i'||i;
l_query := l_select || l_from || l_where;
l_select varchar2(1000); -- (2000)
l_select := 'SELECT g.GEOGRAPHY_ID,g.MULTIPLE_PARENT_FLAG';
l_select := l_select||',GEOGRAPHY_ELEMENT1_ID';
l_select := l_select||','||P_MAP_DTLS_TBL(i).GEO_ELEMENT_COL||'_ID';
l_where := l_where||' AND EXISTS( SELECT NULL FROM HZ_GEOGRAPHY_IDENTIFIERS i'||i;
l_where := l_where||' AND (EXISTS( SELECT /*+ index(i'||i||',HZ_GEOGRAPHY_IDENTIFIERS_U1) */ NULL FROM HZ_GEOGRAPHY_IDENTIFIERS i'||i;
l_query := l_select || l_from || l_where;
l_map_dtls_tbl.DELETE(i);
SELECT GEOGRAPHY_NAME, GEOGRAPHY_CODE
INTO x_map_dtls_tbl(i).LOC_COMPVAL, x_map_dtls_tbl(i).GEOGRAPHY_CODE
FROM HZ_GEOGRAPHIES
WHERE GEOGRAPHY_ID = x_map_dtls_tbl(i).GEOGRAPHY_ID;
PROCEDURE update_location (
p_location_id IN number,
p_loc_components_rec IN loc_components_rec_type,
p_lock_flag IN varchar2,
p_map_dtls_tbl IN maploc_rec_tbl_type,
x_status OUT NOCOPY varchar2
) IS
db_city VARCHAR2(60);
db_wh_update_date DATE;
l_wh_update_date DATE;
SELECT COUNTRY, CITY, STATE, COUNTY, PROVINCE, POSTAL_CODE,POSTAL_PLUS4_CODE,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
WH_UPDATE_DATE, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, ACTUAL_CONTENT_SOURCE,
TIMEZONE_ID, ADDRESS_KEY
INTO l_loc_components_rec.country, l_loc_components_rec.city, l_loc_components_rec.state,
l_loc_components_rec.county, l_loc_components_rec.province,
l_loc_components_rec.postal_code, l_loc_components_rec.postal_plus4_code,
l_loc_components_rec.attribute1,l_loc_components_rec.attribute2,
l_loc_components_rec.attribute3,l_loc_components_rec.attribute4,
l_loc_components_rec.attribute5, l_loc_components_rec.attribute6,
l_loc_components_rec.attribute7,l_loc_components_rec.attribute8,
l_loc_components_rec.attribute9,l_loc_components_rec.attribute10,
l_wh_update_date, l_address1, l_address2, l_address3, l_address4
, l_actual_content_source, l_time_zone_id, l_address_key
FROM HZ_LOCATIONS
WHERE LOCATION_ID = p_location_id
FOR UPDATE OF LOCATION_ID NOWAIT;
END; -- end of SELECT
SELECT COUNTRY, CITY, STATE, COUNTY, PROVINCE, POSTAL_CODE,POSTAL_PLUS4_CODE,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
WH_UPDATE_DATE, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, ACTUAL_CONTENT_SOURCE,
TIMEZONE_ID, ADDRESS_KEY
INTO l_loc_components_rec.country, l_loc_components_rec.city, l_loc_components_rec.state,
l_loc_components_rec.county, l_loc_components_rec.province,
l_loc_components_rec.postal_code, l_loc_components_rec.postal_plus4_code,
l_loc_components_rec.attribute1,l_loc_components_rec.attribute2,
l_loc_components_rec.attribute3,l_loc_components_rec.attribute4,
l_loc_components_rec.attribute5, l_loc_components_rec.attribute6,
l_loc_components_rec.attribute7,l_loc_components_rec.attribute8,
l_loc_components_rec.attribute9,l_loc_components_rec.attribute10,
l_wh_update_date, l_address1, l_address2, l_address3, l_address4
, l_actual_content_source, l_time_zone_id, l_address_key
FROM HZ_LOCATIONS
WHERE LOCATION_ID = p_location_id;
END; -- end of SELECT ;
db_wh_update_date := l_wh_update_date;
pre_location_update( p_old_location_rec => l_old_location_rec,
p_new_location_rec => l_new_location_rec,
p_other_location_params => l_other_param_rec,
x_return_status => ll_return_status,
x_msg_count => ll_msg_count,
x_msg_data => ll_msg_data );
UPDATE HZ_LOCATIONS
SET
COUNTRY = l_loc_components_rec.country,
CITY = l_loc_components_rec.city ,
STATE = l_loc_components_rec.state ,
COUNTY = l_loc_components_rec.county ,
PROVINCE = l_loc_components_rec.province,
POSTAL_CODE = l_loc_components_rec.postal_code,
ATTRIBUTE1 = l_loc_components_rec.attribute1,
ATTRIBUTE2 = l_loc_components_rec.attribute2,
ATTRIBUTE3 = l_loc_components_rec.attribute3,
ATTRIBUTE4 = l_loc_components_rec.attribute4,
ATTRIBUTE5 = l_loc_components_rec.attribute5,
ATTRIBUTE6 = l_loc_components_rec.attribute6,
ATTRIBUTE7 = l_loc_components_rec.attribute7,
ATTRIBUTE8 = l_loc_components_rec.attribute8,
ATTRIBUTE9 = l_loc_components_rec.attribute9,
ATTRIBUTE10 = l_loc_components_rec.attribute10,
TIMEZONE_ID = l_new_location_rec.timezone_id,
ADDRESS_KEY = l_new_location_rec.address_key
WHERE LOCATION_ID = p_location_id;
UPDATE hz_cust_acct_sites_all cas
SET cas.address_text = null
WHERE cas.address_text IS NOT NULL
AND EXISTS
( SELECT 1
FROM HZ_PARTY_SITES ps
WHERE ps.location_id = p_location_id
AND cas.party_site_id = ps.party_site_id );
hz_location_profile_pvt.update_location_profile (
p_location_profile_rec => l_location_profile_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
post_location_update( p_old_location_rec => l_old_location_rec,
p_new_location_rec => l_new_location_rec,
p_other_location_params => l_other_param_rec,
x_return_status => ll_return_status,
x_msg_count => ll_msg_count,
x_msg_data => ll_msg_data );
END update_location;
l_last_updated_by NUMBER; -- hz_utility_v2pub.last_updated_by;
l_last_update_date DATE; -- hz_utility_v2pub.last_update_date;
l_last_update_login NUMBER; -- hz_utility_v2pub.last_update_login;
l_gnr_deleted VARCHAR2(10);
SELECT 'Y'
FROM hz_geo_name_reference_log
WHERE location_id = p_location_id
AND location_table_name = p_location_table_name
AND usage_code = p_usage_code
;
SELECT 'Y'
FROM hz_geo_name_references
WHERE location_id = p_location_id
AND location_table_name = p_location_table_name
AND geography_type = p_geography_type
;
FUNCTION update_loc_yn(
p_loc_components_rec IN loc_components_rec_type,
p_map_dtls_tbl IN maploc_rec_tbl_type
) RETURN VARCHAR2 IS
l_map_dtls_tbl maploc_rec_tbl_type;
END update_loc_yn;
select map_status
from HZ_GEO_NAME_REFERENCE_LOG
where location_id = p_location_id
and map_status = 'S';
select geography_type
from HZ_GEO_NAME_REFERENCES
where location_id = p_location_id
and geography_id in (-99,-98);
l_last_updated_by := hz_utility_v2pub.last_updated_by;
l_last_update_date := hz_utility_v2pub.last_update_date;
l_last_update_login := hz_utility_v2pub.last_update_login;
(p_message => 'Before inserting record into hz_geo_name_reference_log with map status '||p_map_status,
p_prefix => l_debug_prefix,
p_msg_level => fnd_log.level_statement,
p_module_prefix => l_module_prefix,
p_module => l_module
);
INSERT INTO hz_geo_name_reference_log
(location_id, location_table_name,usage_code,
message_text,
object_version_number, map_status,
last_updated_by, creation_date,
created_by, last_update_date,
last_update_login, program_id,
program_login_id,program_application_id,request_id)
VALUES
(p_location_id, p_location_table_name, p_usage_code, NULL, 1, p_map_status,
l_last_updated_by, l_creation_date,
l_created_by, l_last_update_date,
l_last_update_login, l_program_id,
l_conc_login_id, l_program_application_id, l_request_id);
(p_message => 'After inserting record into hz_geo_name_reference_log ',
p_prefix => l_debug_prefix,
p_msg_level => fnd_log.level_statement,
p_module_prefix => l_module_prefix,
p_module => l_module
);
ELSE -- GNR Log already exists, we will update it
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
hz_utility_v2pub.debug
(p_message => 'Before updating record into hz_geo_name_reference_log with map status '||p_map_status,
p_prefix => l_debug_prefix,
p_msg_level => fnd_log.level_statement,
p_module_prefix => l_module_prefix,
p_module => l_module
);
UPDATE hz_geo_name_reference_log
SET map_status = p_map_status,
object_version_number = object_version_number + 1,
last_updated_by = l_last_updated_by,
last_update_date = l_last_update_date,
last_update_login = l_last_update_login
WHERE location_id = p_location_id
AND location_table_name = p_location_table_name
AND usage_code = p_usage_code;
l_gnr_deleted := 'Y'; -- set it to 'Y' because we just deleted it. Later we can avoid again doing this check
(p_message => 'EXCEPTION DUP_VAL_ON_INDEX during GNR Log insert/update for '||
'Location Id:'||p_location_id||',usage_code:'||p_usage_code||
' -'||SUBSTR(SQLERRM,1,100),
p_prefix => l_debug_prefix,
p_msg_level => fnd_log.level_statement,
p_module_prefix => l_module_prefix,
p_module => l_module
);
END; -- END of GNR Log Insert BEGIN Stmt
(p_message => 'Before inserting records into hz_geo_name_references ',
p_prefix => l_debug_prefix,
p_msg_level => fnd_log.level_statement,
p_module_prefix => l_module_prefix,
p_module => l_module
);
IF (l_gnr_deleted = 'Y' ) THEN
l_gnr_exist := 'N';
INSERT INTO hz_geo_name_references
(location_id, geography_id, location_table_name,
object_version_number, geography_type, last_updated_by,
creation_date, created_by, last_update_date,
last_update_login, program_id, program_login_id,
program_application_id,request_id)
VALUES
(p_location_id, l_map_dtls_tbl(i).geography_id,p_location_table_name,
1, p_map_dtls_tbl(i).geography_type, l_last_updated_by,
l_creation_date, l_created_by,
l_last_update_date, l_last_update_login,
l_program_id, l_conc_login_id,
l_program_application_id, l_request_id);
(p_message => 'EXCEPTION DUP_VAL_ON_INDEX during GNR insert/update for '||
'Location Id:'||p_location_id||',geo_type:'||p_map_dtls_tbl(i).geography_type||
' -'||SUBSTR(SQLERRM,1,100),
p_prefix => l_debug_prefix,
p_msg_level => fnd_log.level_statement,
p_module_prefix => l_module_prefix,
p_module => l_module
);
END; -- End of BEGIN for inserting in GNR Log
(p_message => 'After inserting records into hz_geo_name_references ',
p_prefix => l_debug_prefix,
p_msg_level => fnd_log.level_statement,
p_module_prefix => l_module_prefix,
p_module => l_module
);
END IF; -- END OF p_map_dtls_table.count > 0 check for inserting into GNR tables
update_loc_yn(p_loc_components_rec,l_map_dtls_tbl) = 'Y')) THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
hz_utility_v2pub.debug
(p_message => 'Before updating record into update_location ',
p_prefix => l_debug_prefix,
p_msg_level => fnd_log.level_statement,
p_module_prefix => l_module_prefix,
p_module => l_module
);
update_location (p_location_id, p_loc_components_rec,p_lock_flag, l_map_dtls_tbl,x_status);
(p_message => 'After updating record into update_location ',
p_prefix => l_debug_prefix,
p_msg_level => fnd_log.level_statement,
p_module_prefix => l_module_prefix,
p_module => l_module
);
END IF; -- END of update_location
SELECT GEOGRAPHY_ID
FROM HZ_GEO_NAME_REFERENCES
WHERE LOCATION_ID = p_location_id
AND GEOGRAPHY_TYPE = p_geography_type
AND LOCATION_TABLE_NAME = p_location_table_name;
SELECT MAP_STATUS,USAGE_CODE
FROm HZ_GEO_NAME_REFERENCE_LOG
WHERE LOCATION_ID = p_location_id
AND LOCATION_TABLE_NAME = p_location_table_name;
l_last_updated_by NUMBER; -- hz_utility_v2pub.last_updated_by;
l_last_update_date DATE; -- hz_utility_v2pub.last_update_date;
l_last_update_login NUMBER; -- hz_utility_v2pub.last_update_login;
l_last_updated_by := hz_utility_v2pub.last_updated_by;
l_last_update_date := hz_utility_v2pub.last_update_date;
l_last_update_login := hz_utility_v2pub.last_update_login;
UPDATE hz_geo_name_reference_log
SET map_status = 'S',
object_version_number = object_version_number + 1,
last_updated_by = l_last_updated_by,
last_update_date = l_last_update_date,
last_update_login = l_last_update_login
WHERE location_id = p_location_id
AND location_table_name = p_location_table_name
AND usage_code = p_usage_code;
INSERT INTO hz_geo_name_reference_log
(location_id, location_table_name,usage_code,
message_text,
object_version_number, map_status,
last_updated_by, creation_date,
created_by, last_update_date,
last_update_login, program_id,
program_login_id,program_application_id,request_id)
VALUES
(p_location_id, p_location_table_name, p_usage_code, NULL, 1, 'S',
l_last_updated_by,l_creation_date,
l_created_by, l_last_update_date,
l_last_update_login,l_program_id,
l_conc_login_id, l_program_application_id,
l_request_id);
SELECT g.GEOGRAPHY_ID
INTO x_map_dtls_tbl(i).GEOGRAPHY_ID
FROM HZ_GEOGRAPHIES g,HZ_HIERARCHY_NODES hn
WHERE g.GEOGRAPHY_ID = hn.CHILD_ID
AND g.GEOGRAPHY_TYPE = hn.CHILD_OBJECT_TYPE
AND hn.CHILD_TABLE_NAME = 'HZ_GEOGRAPHIES'
AND hn.HIERARCHY_TYPE = 'MASTER_REF'
AND hn.PARENT_TABLE_NAME = 'HZ_GEOGRAPHIES'
AND hn.PARENT_ID = l_parent_geography_id
AND hn.PARENT_OBJECT_TYPE = l_parent_geography_type
AND SYSDATE between hn.EFFECTIVE_START_DATE AND hn.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN g.START_DATE AND g.END_DATE
AND EXISTS (SELECT NULL
FROM HZ_GEOGRAPHY_IDENTIFIERS gi
WHERE g.GEOGRAPHY_ID = gi.GEOGRAPHY_ID
AND gi.GEOGRAPHY_TYPE = g.GEOGRAPHY_TYPE
AND gi.GEOGRAPHY_USE = 'MASTER_REF'
AND upper(gi.IDENTIFIER_VALUE) = upper(x_map_dtls_tbl(i).LOC_COMPVAL));
SELECT g.GEOGRAPHY_ID,g.GEOGRAPHY_NAME
INTO l_geography_id,l_geography_name
FROM HZ_GEOGRAPHIES g,HZ_HIERARCHY_NODES hn
WHERE g.GEOGRAPHY_ID = hn.PARENT_ID
AND g.GEOGRAPHY_TYPE = hn.PARENT_OBJECT_TYPE
AND hn.PARENT_TABLE_NAME = 'HZ_GEOGRAPHIES'
AND hn.HIERARCHY_TYPE = 'MASTER_REF'
AND hn.level_number = 1
AND hn.CHILD_TABLE_NAME = 'HZ_GEOGRAPHIES'
AND hn.CHILD_ID = l_child_geography_id
AND hn.CHILD_OBJECT_TYPE = l_child_geography_type
AND SYSDATE between hn.EFFECTIVE_START_DATE AND hn.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN g.START_DATE AND g.END_DATE;
SELECT g.GEOGRAPHY_ID,g.GEOGRAPHY_NAME
INTO l_geography_id,l_geography_name
FROM HZ_GEOGRAPHIES g,HZ_HIERARCHY_NODES hn
-- Nishant
--WHERE g.GEOGRAPHY_ID = hn.PARENT_ID
WHERE g.GEOGRAPHY_ID = hn.PARENT_ID+0
AND g.GEOGRAPHY_TYPE = hn.PARENT_OBJECT_TYPE
AND hn.PARENT_TABLE_NAME = 'HZ_GEOGRAPHIES'
AND hn.HIERARCHY_TYPE = 'MASTER_REF'
AND hn.level_number = 1
AND hn.CHILD_TABLE_NAME = 'HZ_GEOGRAPHIES'
AND hn.CHILD_ID = l_child_geography_id
AND hn.CHILD_OBJECT_TYPE = l_child_geography_type
AND SYSDATE BETWEEN hn.EFFECTIVE_START_DATE AND hn.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN g.START_DATE AND g.END_DATE
AND EXISTS (SELECT NULL
FROM HZ_GEOGRAPHY_IDENTIFIERS gi
WHERE g.GEOGRAPHY_ID = gi.GEOGRAPHY_ID
AND gi.GEOGRAPHY_TYPE = g.GEOGRAPHY_TYPE
AND gi.geography_type = x_map_dtls_tbl(p_iteration).GEOGRAPHY_TYPE
AND gi.GEOGRAPHY_USE = 'MASTER_REF'
AND upper(gi.IDENTIFIER_VALUE) = upper(x_map_dtls_tbl(p_iteration).LOC_COMPVAL));
SELECT g.GEOGRAPHY_ID,g.GEOGRAPHY_NAME
INTO l_geography_id,l_geography_name
FROM HZ_GEOGRAPHIES g,HZ_HIERARCHY_NODES hn
WHERE g.GEOGRAPHY_ID = hn.CHILD_ID
AND g.GEOGRAPHY_TYPE = hn.CHILD_OBJECT_TYPE
AND hn.CHILD_TABLE_NAME = 'HZ_GEOGRAPHIES'
AND hn.HIERARCHY_TYPE = 'MASTER_REF'
AND hn.level_number = x_map_dtls_tbl(p_iteration).LOC_SEQ_NUM - 1
AND hn.PARENT_TABLE_NAME = 'HZ_GEOGRAPHIES'
AND hn.PARENT_ID+0 = p_country_geo_id
AND hn.PARENT_OBJECT_TYPE = l_country_geo_type
AND SYSDATE BETWEEN hn.EFFECTIVE_START_DATE AND hn.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN g.START_DATE AND g.END_DATE
AND EXISTS (SELECT NULL
FROM HZ_GEOGRAPHY_IDENTIFIERS gi
WHERE g.GEOGRAPHY_ID = gi.GEOGRAPHY_ID
AND gi.GEOGRAPHY_TYPE = g.GEOGRAPHY_TYPE
AND gi.GEOGRAPHY_USE = 'MASTER_REF'
AND gi.geography_type = x_map_dtls_tbl(p_iteration).GEOGRAPHY_TYPE
AND upper(gi.IDENTIFIER_VALUE) = upper(x_map_dtls_tbl(p_iteration).LOC_COMPVAL));
SELECT hn.CHILD_ID
INTO x_map_dtls_tbl(i).GEOGRAPHY_ID
FROM HZ_HIERARCHY_NODES hn
WHERE hn.CHILD_OBJECT_TYPE = x_map_dtls_tbl(i).GEOGRAPHY_TYPE
AND hn.CHILD_TABLE_NAME = 'HZ_GEOGRAPHIES'
AND hn.HIERARCHY_TYPE = 'MASTER_REF'
AND hn.PARENT_TABLE_NAME = 'HZ_GEOGRAPHIES'
AND hn.PARENT_ID = l_parent_geography_id
AND hn.PARENT_OBJECT_TYPE = l_parent_geography_type
AND SYSDATE BETWEEN hn.EFFECTIVE_START_DATE AND hn.EFFECTIVE_END_DATE;
SELECT map_id
FROM hz_geo_struct_map;
SELECT count(*)
FROM Hz_address_usages usg, Hz_address_usage_dtls dtl
WHERE usg.map_id = l_map_id
AND usg.status_flag = 'A'
AND dtl.usage_id = usg.usage_id;