[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE do_update_geo_rel_type(
p_relationship_type_id IN NUMBER,
p_status IN VARCHAR2,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE update_geo_rel_type(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_relationship_type_id IN NUMBER,
p_status IN VARCHAR2,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
SELECT parent_geography_type,geography_element_column
FROM HZ_GEO_STRUCTURE_LEVELS
WHERE geography_type = p_geography_type
AND geography_id = p_geography_id;
SELECT 1
INTO l_count
FROM hz_geography_types_b
WHERE GEOGRAPHY_TYPE = l_geography_type;
hz_geography_types_PKG.Insert_Row (
x_rowid => l_rowid,
x_geography_type => UPPER(l_geography_type),
x_geography_type_name => l_geography_type_name,
x_object_version_number => 1,
x_geography_use => 'MASTER_REF',
x_postal_code_range_flag => 'N',
x_limited_by_geography_id => NULL,
x_created_by_module => p_geography_type_rec.created_by_module,
x_application_id => p_geography_type_rec.application_id,
x_program_login_id => NULL
);
SELECT object_id into l_object_id
FROM FND_OBJECTS
WHERE obj_name='HZ_GEOGRAPHIES';
SELECT count(*)
INTO l_count
FROM FND_OBJECT_INSTANCE_SETS
WHERE INSTANCE_SET_NAME = l_geography_type;
SELECT FND_OBJECT_INSTANCE_SETS_S.nextval INTO l_instance_set_id FROM dual;
FND_OBJECT_INSTANCE_SETS_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_INSTANCE_SET_ID => l_instance_set_id,
X_INSTANCE_SET_NAME => l_geography_type,
X_OBJECT_ID => l_object_id,
X_PREDICATE => l_predicate,
X_DISPLAY_NAME => l_geography_type,
X_DESCRIPTION => l_geography_type,
X_CREATION_DATE => HZ_UTILITY_V2PUB.creation_date,
X_CREATED_BY => HZ_UTILITY_V2PUB.created_by,
X_LAST_UPDATE_DATE => HZ_UTILITY_V2PUB.last_update_date,
X_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.last_updated_by,
X_LAST_UPDATE_LOGIN => HZ_UTILITY_V2PUB.last_update_login
) ;
p_create_update_flag => 'C',
p_geo_rel_type_rec => p_geo_rel_type_rec,
x_return_status => x_return_status
);
SELECT GEOGRAPHY_USE
INTO l_geography_use
FROM hz_geography_types_b
WHERE GEOGRAPHY_TYPE = l_parent_geography_type;
SELECT count(*) INTO l_count FROM hz_relationship_types
WHERE relationship_type=l_geography_use
AND subject_type=l_geography_type
AND object_type=l_parent_geography_type
AND status = 'A'
AND forward_rel_code='PARENT_OF'
AND backward_rel_code = 'CHILD_OF';
PROCEDURE do_update_geo_rel_type(
p_relationship_type_id IN NUMBER,
p_status IN VARCHAR2,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_relationship_type_rec HZ_RELATIONSHIP_TYPE_V2PUB.RELATIONSHIP_TYPE_REC_TYPE;
select 1 into l_count from
hz_relationship_types
where relationship_type_id=p_relationship_type_id;
p_create_update_flag => 'U',
p_column => 'object_version_number',
p_column_value => p_object_version_number,
x_return_status => x_return_status
);
HZ_RELATIONSHIP_TYPE_V2PUB.update_relationship_type(
p_init_msg_list =>'F',
p_relationship_type_rec =>l_relationship_type_rec,
p_object_version_number =>p_object_version_number,
x_return_status =>x_return_status,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data
);
END do_update_geo_rel_type;
p_create_update_flag => 'C',
p_geo_structure_rec => p_geo_structure_rec,
x_return_status => x_return_status
);
SELECT country_code
INTO l_country_code
FROM HZ_GEOGRAPHIES
WHERE geography_id = p_geo_structure_rec.geography_id;
SELECT count(*) INTO l_count FROM hz_geo_structure_levels
WHERE geography_element_column = p_geo_structure_rec.geography_element_column
AND geography_type <> p_geo_structure_rec.geography_type
AND geography_id = p_geo_structure_rec.geography_id
AND rownum < 2;
SELECT geography_element_column into l_geo_element_col FROM hz_geo_structure_levels
WHERE geography_id=p_geo_structure_rec.geography_id
AND geography_type=p_geo_structure_rec.geography_type
AND rownum < 2;
SELECT 'GEOGRAPHY_ELEMENT'||NVL(max(substr(geography_element_column,18))+1,2) geo_element_col
into l_geo_element_col
FROM hz_geo_structure_levels
WHERE geography_id = p_geo_structure_rec.geography_id;
SELECT relationship_type_id,object_version_number,status
INTO l_relationship_type_id,l_object_version_number,l_status
FROM HZ_RELATIONSHIP_TYPES
WHERE subject_type = p_geo_structure_rec.parent_geography_type
AND object_type= p_geo_structure_rec.geography_type
AND forward_rel_code = 'PARENT_OF'
AND backward_rel_code = 'CHILD_OF'
AND relationship_type='MASTER_REF';
update_geo_rel_type(
p_init_msg_list => 'F',
p_relationship_type_id => l_relationship_type_id,
p_status => 'A',
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
HZ_GEO_STRUCTURE_LEVELS_PKG.Insert_Row (
x_rowid => l_rowid,
x_geography_id => p_geo_structure_rec.geography_id,
x_geography_type => p_geo_structure_rec.geography_type,
x_parent_geography_type => p_geo_structure_rec.parent_geography_type,
x_object_version_number => 1,
x_relationship_type_id => l_relationship_type_id,
x_country_code => l_country_code,
x_geography_element_column => l_geo_element_col,
x_created_by_module => p_geo_structure_rec.created_by_module,
x_application_id => p_geo_structure_rec.application_id,
x_program_login_id => NULL,
x_addr_val_level => p_geo_structure_rec.addr_val_level
);
HZ_GEO_STRUCTURE_LEVELS_PKG.Insert_Row (
x_rowid => l_rowid,
x_geography_id => p_geo_structure_rec.geography_id,
x_geography_type => p_geo_structure_rec.geography_type,
x_parent_geography_type => p_geo_structure_rec.parent_geography_type,
x_object_version_number => 1,
x_relationship_type_id => x_relationship_type_id,
x_country_code => l_country_code,
x_geography_element_column => l_geo_element_col,
x_created_by_module => p_geo_structure_rec.created_by_module,
x_application_id => p_geo_structure_rec.application_id,
x_program_login_id => NULL,
x_addr_val_level => p_geo_structure_rec.addr_val_level
);
PROCEDURE do_update_geo_structure(
p_geography_id IN NUMBER,
p_geography_type IN VARCHAR2,
p_parent_geography_type IN VARCHAR2,
p_geography_element_column IN VARCHAR2,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_relation_count NUMBER;
SELECT distinct map.map_id
FROM hz_geo_struct_map map, hz_geo_struct_map_dtl dtl
WHERE map.country_code = l_country_code
AND map.map_id = dtl.map_id;
p_create_update_flag => 'U',
p_geo_structure_rec => l_geo_structure_rec,
x_return_status => x_return_status
);
p_create_update_flag => 'U',
p_column => 'object_version_number',
p_column_value => p_object_version_number,
x_return_status => x_return_status
);
SELECT count(*) INTO l_count FROM hz_geo_structure_levels
WHERE geography_element_column = p_geography_element_column
AND geography_type <> p_geography_type
AND geography_id = p_geography_id
AND rownum < 2;
SELECT country_code,geography_element_column,rowid,object_version_number
INTO l_country_code,l_geo_element_col,l_rowid,l_object_version_number
FROM hz_geo_structure_levels
WHERE geography_id = p_geography_id
AND geography_type = p_geography_type
AND parent_geography_type = p_parent_geography_type;
/* EXECUTE IMMEDIATE 'SELECT 1 FROM hz_geographies WHERE '||l_geo_element_col||' IS NOT NULL AND country_code='||''''||l_country_code||''''||
' AND rownum <2'; */
/* SELECT 1 into l_count from
hz_relationships hrl
WHERE
hrl.relationship_type='MASTER_REF'
AND hrl.subject_type= p_parent_geography_type
AND hrl.object_type=p_geography_type
AND hrl.relationship_code='PARENT_OF'
AND hrl.status = 'A'
AND hrl.subject_id in ( SELECT geography_id from hz_geographies where
country_code = l_country_code )
AND rownum <2;
FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_ELEMENT_NONUPDATEABLE' );
HZ_GEO_STRUCTURE_LEVELS_PKG.Update_Row (
x_rowid => l_rowid,
x_geography_id => p_geography_id,
x_geography_type => p_geography_type,
x_parent_geography_type => p_parent_geography_type,
x_object_version_number => p_object_version_number,
x_relationship_type_id => NULL,
x_country_code => NULL,
x_geography_element_column => p_geography_element_column,
x_created_by_module => NULL,
x_application_id => NULL,
x_program_login_id => NULL
);
UPDATE hz_geo_struct_map_dtl
SET geo_element_col=p_geography_element_column
WHERE geography_type= p_geography_type
AND map_id = l_geo_structure_map.map_id;
END do_update_geo_structure;
PROCEDURE do_delete_geo_structure(
p_geography_id IN NUMBER,
p_geography_type IN VARCHAR2,
p_parent_geography_type IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
)IS
l_column VARCHAR2(30);
Select map_id
from hz_geo_struct_map
where country_code = l_country_code;
SELECT usage_id
FROM hz_address_usages
WHERE map_id = l_map_id;
SELECT geography_id
FROM hz_geographies
WHERE geography_type = p_geography_type
AND country_code = l_country_code;
SELECT location_id,Location_table_name
FROM hz_geo_name_references
WHERE geography_id = l_geography_id;
SELECT geography_id,master_ref_geography_id,geography_from,
start_date
FROM hz_geography_ranges hgr
WHERE (SELECT country_code
FROM hz_geographies hg
WHERE hg.geography_id = hgr.master_ref_geography_id ) = l_country_code;
SELECT geography_type
FROM hz_geography_types_b
WHERE limited_by_geography_id = l_geography_id
AND geography_use = 'TAX';
SELECT relationship_type_id,country_code INTO l_relationship_type_id,l_country_code
FROM hz_geo_structure_levels
WHERE geography_id = p_geography_id
AND geography_type = p_geography_type
AND parent_geography_type = p_parent_geography_type;
SELECT geography_type into l_child_geography_type
FROM hz_geo_structure_levels
WHERE geography_id = p_geography_id
AND parent_geography_type = p_geography_type;
SELECT relationship_type_id,object_version_number,status
INTO l_new_relationship_type_id,l_object_version_number,l_status
FROM HZ_RELATIONSHIP_TYPES
WHERE subject_type = p_parent_geography_type
AND object_type= l_child_geography_type
AND forward_rel_code = 'PARENT_OF'
AND backward_rel_code = 'CHILD_OF'
AND relationship_type='MASTER_REF';
update_geo_rel_type(
p_init_msg_list => 'F',
p_relationship_type_id => l_new_relationship_type_id,
p_status => 'A',
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
UPDATE hz_geo_structure_levels
set parent_geography_type = p_parent_geography_type,
relationship_type_id = l_new_relationship_type_id
where geography_id = p_geography_id
and parent_geography_type = p_geography_type;
UPDATE hz_geo_structure_levels
set parent_geography_type = p_parent_geography_type,
relationship_type_id = l_new_relationship_type_id
where geography_id = p_geography_id
and parent_geography_type = p_geography_type;
HZ_GEO_STRUCTURE_LEVELS_PKG.Delete_Row (
x_geography_id => p_geography_id,
x_geography_type => p_geography_type,
x_parent_geography_type => p_parent_geography_type
);
SELECT count(*) INTO l_count
FROM hz_geo_structure_levels
WHERE country_code <> l_country_code
AND relationship_type_id=l_relationship_type_id
AND rownum <3;
SELECT object_version_number into l_object_version_number
FROM hz_relationship_types
WHERE relationship_type_id = l_relationship_type_id;
update_geo_rel_type(
p_init_msg_list => 'F',
p_relationship_type_id => l_relationship_type_id,
p_status => 'I',
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT object_version_number,relationship_type_id into l_object_version_number,l_relationship_type_id
FROM hz_relationship_types
WHERE subject_type=p_geography_type
AND object_type=p_parent_geography_type
AND relationship_type='MASTER_REF'
AND forward_rel_code = 'CHILD_OF'
AND backward_rel_code = 'PARENT_OF';
update_geo_rel_type(
p_init_msg_list => 'F',
p_relationship_type_id => l_relationship_type_id,
p_status => 'I',
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
delete hz_relationship_types
where relationship_type = 'TAX'
and ((object_type = p_geography_type
and subject_type in (select hgt.geography_type from
hz_geography_types_b hgt,hz_geographies hg
where hgt.limited_by_geography_id = hg.geography_id
and hgt.geography_use = 'TAX'
and hg.country_code = l_country_code ))
or (subject_type = p_geography_type
and object_type in (select hgt.geography_type from
hz_geography_types_b hgt,hz_geographies hg
where hgt.limited_by_geography_id = hg.geography_id
and hgt.geography_use = 'TAX'
and hg.country_code = l_country_code )));
delete hz_geographies
where geography_id = l_geography_id;
delete hz_geography_identifiers
where geography_id = l_geography_id;
delete hz_geo_name_references
where location_id = l_location_id
and location_table_name = l_loc_tbl_name;
delete hz_geo_name_reference_log
where location_id = l_location_id
and location_table_name = l_loc_tbl_name;
-- if postal_code is deleted from structure delete all ranges record
-- for this country
if p_geography_type = 'POSTAL_CODE' then
open c_get_ranges;
delete hz_geography_ranges hgr
where geography_id = l_zone_id
and geography_from = l_geography_from
and start_date = l_start_date;
-- delete master_ref_geogrpahy for postal code range
delete hz_relationships
where subject_id = l_zone_id
and object_id = l_master_ref_geography_id
and subject_table_name = 'HZ_GEOGRAPHIES'
and object_table_name = 'HZ_GEOGRAPHIES'
and directional_flag = 'F'
and relationship_type = 'TAX' ;
delete hz_relationships
where subject_id = l_master_ref_geography_id
and object_id = l_zone_id
and subject_table_name = 'HZ_GEOGRAPHIES'
and object_table_name = 'HZ_GEOGRAPHIES'
and directional_flag = 'B'
and relationship_type = 'TAX';
update hz_geography_types_b hgt
set postal_code_range_flag = 'N'
where geography_use = 'TAX'
and limited_by_geography_id is not null
and (select country_code
from hz_geographies
where geography_id = hgt.limited_by_geography_id)
= l_country_code;
-- delete geogrpahy ranges if any master_ref_geo is deleted
delete hz_geography_ranges
where master_ref_geography_id = l_geography_id;
-- delete relationships record both for tax and master_ref relationship_type
delete hz_relationships
where (object_id = l_geography_id
or subject_id = l_geography_id )
and subject_table_name = 'HZ_GEOGRAPHIES'
and object_table_name = 'HZ_GEOGRAPHIES'
and (relationship_type = 'TAX'
or relationship_type = 'MASTER_REF');
-- delete hierarchy nodes record for master_ref geos
delete hz_hierarchy_nodes
where (parent_id = l_geography_id
or child_id = l_geography_id)
and parent_table_name = 'HZ_GEOGRAPHIES'
and child_table_name = 'HZ_GEOGRAPHIES'
and hierarchy_type = 'MASTER_REF';
-- delete tax zone type whose limited by geo id is deleted
OPEN c_get_zone_types;
DELETE hz_geography_types_b
WHERE geography_type = l_zone_type
AND geography_use = 'TAX';
DELETE hz_geography_identifiers
WHERE geography_id IN (SELECT geography_id
FROM hz_geographies
WHERE geography_type = l_zone_type
AND geography_use = 'TAX');
DELETE hz_relationships
WHERE subject_id in (SELECT geography_id
FROM hz_geographies
WHERE geography_type = l_zone_type
AND geography_use = 'TAX')
AND subject_type = l_zone_type
AND subject_table_name = 'HZ_GEOGRAPHIES'
AND relationship_type = 'TAX';
DELETE hz_relationships
WHERE object_id in (SELECT geography_id
FROM hz_geographies
WHERE geography_type = l_zone_type
AND geography_use = 'TAX')
AND object_type = l_zone_type
AND object_table_name = 'HZ_GEOGRAPHIES'
AND relationship_type = 'TAX';
-- delete tax zone assosiated with this tax zone type
DELETE hz_geographies
WHERE geography_type = l_zone_type
AND geography_use = 'TAX' ;
select map_id,loc_tbl_name,country_code,
address_style
into l_map_id,l_location_table_name,l_country,
l_address_style
from hz_geo_struct_map
where map_id = l_map_id;
select loc_seq_num,loc_component,geography_type
bulk collect into l_geo_struct_map_dtl_tbl
from hz_geo_struct_map_dtl
where map_id = l_map_id
and geography_type = p_geography_type;
HZ_GEO_STRUCT_MAP_PUB.delete_geo_struct_mapping(l_map_id,
l_location_table_name,
l_country,
l_address_style,
l_geo_struct_map_dtl_tbl,
FND_API.G_FALSE,
x_return_status,
x_msg_count,
x_msg_data);
delete hz_geo_struct_map_dtl
where map_id = l_map_id
and geography_type = p_geography_type;
delete hz_address_usage_dtls
where usage_id = l_usage_id
and geography_type = p_geography_type;
END do_delete_geo_structure;
p_create_update_flag => 'C',
x_return_status => x_return_status
);
hz_geography_types_PKG.Insert_Row (
x_rowid => l_rowid,
x_geography_type => p_zone_type_rec.geography_type,
x_geography_type_name => l_geography_type_name,
x_object_version_number => 1,
x_geography_use => p_zone_type_rec.geography_use,
x_postal_code_range_flag => p_zone_type_rec.postal_code_range_flag,
x_limited_by_geography_id => p_zone_type_rec.limited_by_geography_id,
x_created_by_module => p_zone_type_rec.created_by_module,
x_application_id => p_zone_type_rec.application_id,
x_program_login_id => NULL
);
SELECT object_id into l_object_id
FROM FND_OBJECTS
WHERE obj_name='HZ_GEOGRAPHIES';
SELECT count(*)
INTO l_count
FROM FND_OBJECT_INSTANCE_SETS
WHERE INSTANCE_SET_NAME = p_zone_type_rec.geography_type;
SELECT FND_OBJECT_INSTANCE_SETS_S.nextval INTO l_instance_set_id FROM dual;
FND_OBJECT_INSTANCE_SETS_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_INSTANCE_SET_ID => l_instance_set_id,
X_INSTANCE_SET_NAME => p_zone_type_rec.geography_type,
X_OBJECT_ID => l_object_id,
X_PREDICATE => l_predicate,
X_DISPLAY_NAME => p_zone_type_rec.geography_type,
X_DESCRIPTION => p_zone_type_rec.geography_type,
X_CREATION_DATE => HZ_UTILITY_V2PUB.creation_date,
X_CREATED_BY => HZ_UTILITY_V2PUB.created_by,
X_LAST_UPDATE_DATE => HZ_UTILITY_V2PUB.last_update_date,
X_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.last_updated_by,
X_LAST_UPDATE_LOGIN => HZ_UTILITY_V2PUB.last_update_login
) ;
SELECT geography_type, country_code
INTO l_limited_by_geo_type, l_country_code
FROM hz_geographies
WHERE geography_id = p_zone_type_rec.limited_by_geography_id;
SELECT geography_type
INTO l_valid_geo_type
FROM hz_geo_structure_levels
WHERE country_code = l_country_code
AND geography_type = p_zone_type_rec.included_geography_type(i)
START WITH parent_geography_type = l_limited_by_geo_type
AND country_code = l_country_code
CONNECT BY PRIOR geography_type = parent_geography_type
AND country_code = l_country_code;
SELECT status, relationship_type_id, object_version_number
INTO l_rel_status, x_relationship_type_id, l_object_version_number
FROM HZ_RELATIONSHIP_TYPES
WHERE subject_type = p_zone_type_rec.geography_type
AND object_type= p_zone_type_rec.included_geography_type(i)
AND forward_rel_code = 'PARENT_OF'
AND backward_rel_code = 'CHILD_OF'
AND relationship_type = p_zone_type_rec.geography_use;
HZ_GEOGRAPHY_STRUCTURE_PUB.update_geo_rel_type(
p_init_msg_list => 'F',
p_relationship_type_id => x_relationship_type_id,
p_status => 'A',
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PROCEDURE do_update_zone_type(
p_zone_type_rec IN ZONE_TYPE_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_postal_code_range_flag VARCHAR2(1);
select relationship_type_id, object_version_number, object_type
from HZ_RELATIONSHIP_TYPES
where subject_type = p_zone_type_rec.geography_type
and forward_rel_code = 'PARENT_OF'
and backward_rel_code = 'CHILD_OF'
and relationship_type = 'TAX'
and status = 'A';
p_create_update_flag => 'U',
p_column => 'object_version_number',
p_column_value => p_object_version_number,
x_return_status => x_return_status
);
SELECT OBJECT_VERSION_NUMBER,
ROWID,
GEOGRAPHY_TYPE,
POSTAL_CODE_RANGE_FLAG,
LIMITED_BY_GEOGRAPHY_ID
INTO l_object_version_number,
l_rowid,
l_geography_type,
l_postal_code_range_flag,
l_limited_by_geo_id
FROM HZ_GEOGRAPHY_TYPES_B
WHERE GEOGRAPHY_TYPE = p_GEOGRAPHY_TYPE
FOR UPDATE OF GEOGRAPHY_TYPE NOWAIT;
p_create_update_flag => 'U',
x_return_status => x_return_status
);
SELECT count(*) INTO l_count
FROM hz_geography_ranges
WHERE geography_id in (SELECT geography_id from hz_geographies
WHERE geography_type=p_geography_type
AND end_date > sysdate)
AND rownum <2;
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
SELECT count(*) INTO l_count
FROM hz_geographies
WHERE geography_type = p_geography_type
AND end_date > SYSDATE
AND rownum <3;
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
hz_geography_types_PKG.update_row(
x_rowid => l_rowid,
x_geography_type => p_geography_type,
x_geography_type_name => NULL,
x_object_version_number => p_object_version_number,
x_geography_use => NULL,
x_postal_code_range_flag => p_postal_code_range_flag,
x_limited_by_geography_id => p_limited_by_geography_id,
x_created_by_module => NULL,
x_application_id => NULL,
x_program_login_id => NULL
);
SELECT status, relationship_type_id, object_version_number
INTO l_rel_status, x_relationship_type_id, l_object_version_number
FROM HZ_RELATIONSHIP_TYPES
WHERE subject_type = p_zone_type_rec.geography_type
AND object_type= p_zone_type_rec.included_geography_type(i)
AND forward_rel_code = 'PARENT_OF'
AND backward_rel_code = 'CHILD_OF'
AND relationship_type = p_zone_type_rec.geography_use;
HZ_GEOGRAPHY_STRUCTURE_PUB.update_geo_rel_type(
p_init_msg_list => 'F',
p_relationship_type_id => x_relationship_type_id,
p_status => 'A',
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
HZ_GEOGRAPHY_STRUCTURE_PUB.update_geo_rel_type(
p_init_msg_list => 'F',
p_relationship_type_id => x_relationship_type_id,
p_status => 'I',
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END do_update_zone_type;
* PROCEDURE update_geography_rel_type
*
* DESCRIPTION
* Updates only Status of geography relationship type.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_geography_rel_type_rec Geography Relationship type record.
* IN/OUT:
* p_object_version_number object version number of the row being updated
* OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 11-13-2002 Rekha Nalluri o Created.
*
*/
PROCEDURE update_geo_rel_type(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_relationship_type_id IN NUMBER,
p_status IN VARCHAR2,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
--l_geo_rel_type_rec geo_rel_type_REC_TYPE := p_geo_rel_type_rec;
SAVEPOINT update_geo_rel_type;
do_update_geo_rel_type(
p_relationship_type_id => p_relationship_type_id,
p_status => p_status,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status
);
ROLLBACK TO update_geo_rel_type;
ROLLBACK TO update_geo_rel_type;
ROLLBACK TO update_geo_rel_type;
END UPDATE_GEO_REL_TYPE;
* PROCEDURE update_geo_structure
*
* DESCRIPTION
* Updates geography_element_column in a Geography Structure - geography_element_column can be updated for
* a geography_id and relationship_type_id only when there exists no geographies that have used this
* structure.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_geo_structure_rec Geography structure type record.
* IN/OUT:
* p_object_version_number object version number of the row being updated
* OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 12-18-2002 Rekha Nalluri o Created.
*
*/
/* Obsoleting as it is no more needed ( bug 2911108)
PROCEDURE update_geo_structure(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_geography_id IN NUMBER,
p_geography_type IN VARCHAR2,
p_parent_geography_type IN VARCHAR2,
p_geography_element_column IN VARCHAR2,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
-- Standard start of API savepoint
SAVEPOINT update_geo_structure;
do_update_geo_structure(
p_geography_id =>p_geography_id,
p_geography_type =>p_geography_type,
p_parent_geography_type => p_parent_geography_type,
p_geography_element_column =>p_geography_element_column,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status
);
ROLLBACK TO update_geo_structure;
ROLLBACK TO update_geo_structure;
ROLLBACK TO update_geo_structure;
END update_geo_structure;
* PROCEDURE delete_geo_structure
*
* DESCRIPTION
* Deletes the row in the structure. Disables the relationship_type if it is not used by any other structure.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_geo_structure_rec Geography structure type record.
* IN/OUT:
*
* OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 12-19-2002 Rekha Nalluri o Created.
*
*/
PROCEDURE delete_geo_structure(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_geography_id IN NUMBER,
p_geography_type IN VARCHAR2,
p_parent_geography_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
BEGIN
-- Standard start of API savepoint
SAVEPOINT delete_geo_structure;
do_delete_geo_structure(
p_geography_id => p_geography_id,
p_geography_type => p_geography_type,
p_parent_geography_type => p_parent_geography_type,
x_return_status => x_return_status
);
ROLLBACK TO delete_geo_structure;
ROLLBACK TO delete_geo_structure;
ROLLBACK TO delete_geo_structure;
END delete_geo_structure;
* PROCEDURE update_zone_type
*
* DESCRIPTION
* Updates zone type.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_geographytype Geography type.
* p_limited_by_geography_id
* p_postal_code_range_flag
* IN/OUT:
* p_object_version_number object version number of the row being updated
* OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 01-13-2003 Rekha Nalluri o Created.
*
*/
PROCEDURE update_zone_type(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_zone_type_rec IN ZONE_TYPE_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
-- Standard start of API savepoint
SAVEPOINT update_zone_type;
do_update_zone_type(
p_zone_type_rec => p_zone_type_rec,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO update_zone_type;
ROLLBACK TO update_zone_type;
ROLLBACK TO update_zone_type;
END update_zone_type;