[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION multi_parent_update_val(
l_geo_id IN NUMBER,
l_element_col IN VARCHAR2,
l_element_col_type IN VARCHAR2 DEFAULT 'NAME'
) RETURN VARCHAR2;
FUNCTION multi_parent_update_val (l_geo_id NUMBER, l_element_col VARCHAR2,
l_element_col_type VARCHAR2 DEFAULT 'NAME')
RETURN VARCHAR2 IS
l_return_value VARCHAR2(100);
SELECT country_code
INTO l_country_code
FROM hz_geographies
WHERE geography_id = l_geo_id;
SELECT DECODE(l_element_col_type,'ID', geo_element_col||'_ID','CODE',
geo_element_col||'_CODE','NAME',geo_element_col,geo_element_col)
INTO l_return_value
FROM (
-- This select gives geography_element column name for those levels which
-- are not multiple. For levels at which multiple parents exist, it returns null
SELECT decode(no_of_parents, 1, DECODE(geo_temp.parent_object_type,'COUNTRY','GEOGRAPHY_ELEMENT1'
,geo_struct.geography_element_column), NULL) geo_element_col, ROWNUM level_number,
geo_temp.parent_object_type geography_type
FROM (
-- here grouping is based on number of parents at each level
SELECT COUNT(parent_object_type) no_of_parents , parent_object_type, level_number
FROM (
-- This query does the grouping based on parent id and geography type
-- Note : sysdate is not truncated in check because we want to not pick
-- up those records which are just now end dated (end dated in current flow)
SELECT parent_id, parent_object_type, level_number
FROM hz_hierarchy_nodes
WHERE hierarchy_type = 'MASTER_REF'
AND SYSDATE+0.0001 BETWEEN effective_start_date AND effective_end_date
AND child_table_name = 'HZ_GEOGRAPHIES'
AND NVL(status,'A') = 'A'
AND child_id = l_geo_id
GROUP BY parent_id, parent_object_type, level_number
)
GROUP BY parent_object_type, level_number
ORDER BY level_number desc
) geo_temp,
hz_geo_structure_levels geo_struct
WHERE geo_temp.parent_object_type = DECODE(geo_temp.parent_object_type,
'COUNTRY',geo_struct.parent_geography_type,
geo_struct.geography_type)
AND country_code = l_country_code
)
WHERE geo_element_col = l_element_col;
END multi_parent_update_val;
SELECT parent_id,parent_object_type
FROM HZ_HIERARCHY_NODES
WHERE child_id = p_geography_id
AND child_object_type = p_geography_type
AND child_table_name = 'HZ_GEOGRAPHIES'
AND hierarchy_type = 'MASTER_REF'
AND NVL(status,'A') = 'A'
AND (effective_end_date IS NULL
OR effective_end_date > sysdate
)
ORDER BY level_number;
SELECT geography_id, geography_name, geography_code
FROM hz_geographies
WHERE geography_type = 'COUNTRY'
AND geography_use = 'MASTER_REF'
AND country_code = l_country_code
AND SYSDATE BETWEEN START_DATE AND end_date;
SELECT country_code INTO l_country_code
FROM hz_geographies
WHERE geography_id= p_parent_geography_id;
SELECT geography_element_column INTO l_geo_element_col
FROM HZ_GEO_STRUCTURE_LEVELS
WHERE geography_type=l_get_all_parents.parent_object_type
AND country_code = l_country_code
AND rownum <2 ;
SELECT geography_name,geography_code INTO l_geography_name,l_geography_code
FROM HZ_GEOGRAPHIES
WHERE geography_id=l_get_all_parents.parent_id;
-- check if this is a multi parent column. If it is then multi_parent_update_val will return NULL
-- otherwise it will return back the column name.
l_geo_element_col_temp := multi_parent_update_val(p_geography_id,l_geo_element_col,'NAME');
EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_col||'= :l_geography_name '||
','||l_geo_element_id_col||'= :l_parent_id '||
','||l_geo_element_code_col||'= :l_geography_code '||
', multiple_parent_flag = ''N'''||
' where geography_id = :l_geography_id '
USING l_geography_name, l_get_all_parents.parent_id,l_geography_code,p_geography_id;
EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_col||'= :l_geography_name '||
','||l_geo_element_id_col||'= :l_parent_id '||
', multiple_parent_flag = ''N'''||
' where geography_id = :l_geography_id '
USING l_geography_name, to_char(l_get_all_parents.parent_id), to_char(p_geography_id);
ELSE -- its a multi parent record (update the flag to 'Y')
UPDATE HZ_GEOGRAPHIES
SET multiple_parent_flag = 'Y'
WHERE geography_id = p_geography_id
AND multiple_parent_flag <> 'Y';
SELECT child_id,child_object_type
FROM HZ_HIERARCHY_NODES
WHERE parent_id=p_geography_id
AND parent_object_type=p_geography_type
AND parent_table_name = 'HZ_GEOGRAPHIES'
AND hierarchy_type='MASTER_REF'
AND NVL(status,'A') = 'A'
AND (effective_end_date IS NULL
OR effective_end_date > sysdate);
SELECT distinct parent_object_type INTO l_parent_object_type
FROM hz_hierarchy_nodes
WHERE child_id = p_geography_id
AND child_object_type=p_geography_type
AND child_table_name = 'HZ_GEOGRAPHIES'
AND hierarchy_type='MASTER_REF'
AND NVL(status,'A') = 'A'
AND (effective_end_date IS NULL
OR effective_end_date > sysdate)
AND level_number = 1;
SELECT distinct geography_element_column INTO l_geo_element_col
FROM hz_geo_structure_levels
WHERE geography_type = l_parent_object_type
AND geography_id = (select geography_id from hz_geographies where country_code=
(select country_code from hz_geographies where geography_id=p_geography_id)
and geography_type = 'COUNTRY'); -- Bug4680789
SELECT count(distinct parent_id) INTO l_count
FROM hz_hierarchy_nodes
WHERE child_id in ( SELECT parent_id
FROM hz_hierarchy_nodes
WHERE child_id = p_geography_id
AND child_object_type = p_geography_type
AND child_table_name = 'HZ_GEOGRAPHIES'
AND hierarchy_type = 'MASTER_REF'
AND NVL(status,'A') = 'A'
AND (effective_end_date IS NULL
OR effective_end_date > sysdate)
AND parent_id <> child_id
AND level_number = 1)
AND child_table_name = 'HZ_GEOGRAPHIES'
AND hierarchy_type='MASTER_REF'
AND (effective_end_date IS NULL
OR effective_end_date > sysdate)
AND parent_id <> child_id
AND level_number = 1;
l_geography_element2 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT2','NAME');
l_geography_element3 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT3','NAME');
l_geography_element4 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT4','NAME');
l_geography_element5 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT5','NAME');
l_geography_element6 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT6','NAME');
l_geography_element7 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT7','NAME');
l_geography_element8 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT8','NAME');
l_geography_element9 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT9','NAME');
l_geography_element10 := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT10','NAME');
l_geography_element2_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT2','ID');
l_geography_element3_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT3','ID');
l_geography_element4_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT4','ID');
l_geography_element5_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT5','ID');
l_geography_element6_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT6','ID');
l_geography_element7_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT7','ID');
l_geography_element8_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT8','ID');
l_geography_element9_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT9','ID');
l_geography_element10_id := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT10','ID');
l_geography_element2_code := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT2','CODE');
l_geography_element3_code := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT3','CODE');
l_geography_element4_code := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT4','CODE');
l_geography_element5_code := multi_parent_update_val(l_get_all_children.child_id,'GEOGRAPHY_ELEMENT5','CODE');
UPDATE HZ_GEOGRAPHIES
-- SET multiple_parent_flag = decode(geography_id,p_geography_id,'Y',multiple_parent_flag),
SET multiple_parent_flag = 'Y',
-- geography_element1=NULL,
geography_element2= DECODE(l_geography_element2,NULL,NULL,geography_element2),
geography_element3= DECODE(l_geography_element3,NULL,NULL,geography_element3),
geography_element4= DECODE(l_geography_element4,NULL,NULL,geography_element4),
geography_element5= DECODE(l_geography_element5,NULL,NULL,geography_element5),
geography_element6= DECODE(l_geography_element6,NULL,NULL,geography_element6),
geography_element7= DECODE(l_geography_element7,NULL,NULL,geography_element7),
geography_element8= DECODE(l_geography_element8,NULL,NULL,geography_element8),
geography_element9= DECODE(l_geography_element9,NULL,NULL,geography_element9),
geography_element10= DECODE(l_geography_element10,NULL,NULL,geography_element10),
-- geography_element1_id=NULL,
geography_element2_id= DECODE(l_geography_element2_id,NULL,NULL,geography_element2_id),
geography_element3_id= DECODE(l_geography_element3_id,NULL,NULL,geography_element3_id),
geography_element4_id= DECODE(l_geography_element4_id,NULL,NULL,geography_element4_id),
geography_element5_id= DECODE(l_geography_element5_id,NULL,NULL,geography_element5_id),
geography_element6_id= DECODE(l_geography_element6_id,NULL,NULL,geography_element6_id),
geography_element7_id= DECODE(l_geography_element7_id,NULL,NULL,geography_element7_id),
geography_element8_id= DECODE(l_geography_element8_id,NULL,NULL,geography_element8_id),
geography_element9_id= DECODE(l_geography_element9_id,NULL,NULL,geography_element9_id),
geography_element10_id= DECODE(l_geography_element10_id,NULL,NULL,geography_element10_id),
-- geography_element1_code = NULL,
geography_element2_code = DECODE(l_geography_element2_code,NULL,NULL,geography_element2_code),
geography_element3_code = DECODE(l_geography_element3_code,NULL,NULL,geography_element3_code),
geography_element4_code = DECODE(l_geography_element4_code,NULL,NULL,geography_element4_code),
geography_element5_code = DECODE(l_geography_element5_code,NULL,NULL,geography_element5_code)
WHERE geography_id=l_get_all_children.child_id;
EXECUTE IMMEDIATE 'UPDATE hz_geographies SET multiple_parent_flag='||'''Y'''||','||l_geo_element_col||' = NULL,'||
l_geo_element_col_id||' = NULL,'||l_geo_element_col_code||'= NULL where geography_id = '||l_get_all_children.child_id;
EXECUTE IMMEDIATE 'UPDATE hz_geographies SET multiple_parent_flag='||'''Y'''||','||l_geo_element_col||' = NULL,'||
l_geo_element_col_id||' = NULL where geography_id = '||l_get_all_children.child_id;
SELECT count(*) INTO l_count
FROM hz_geography_identifiers
WHERE identifier_type='NAME'
AND identifier_subtype = p_child_identifier_subtype
AND language_code = p_child_language
AND UPPER(identifier_value) = UPPER(p_child_name)
AND geography_id IN (SELECT object_id
FROM hz_relationships
WHERE subject_id = p_parent_id
AND object_type = p_child_type
AND status = 'A'
AND relationship_type = 'MASTER_REF');
SELECT count(*) INTO l_count
FROM hz_geography_identifiers
WHERE identifier_type='CODE'
AND identifier_subtype = p_child_identifier_subtype
AND language_code = p_child_language
AND identifier_value = UPPER(p_child_code)
AND geography_id IN (SELECT object_id
FROM hz_relationships
WHERE subject_id = p_parent_id
AND object_type = p_child_type
AND status = 'A'
AND relationship_type = 'MASTER_REF');
p_create_update_flag => 'C',
x_return_status => x_return_status
);
SELECT geography_name,geography_code,geography_type INTO
l_geography_name,l_geography_code,l_geography_type
FROM hz_geographies
WHERE geography_id = p_master_relation_rec.geography_id;
SELECT identifier_subtype,language_code INTO
l_identifier_subtype,l_language_code
FROM hz_geography_identifiers
WHERE geography_id = p_master_relation_rec.geography_id
AND identifier_type = 'NAME'
AND primary_flag = 'Y'
AND geography_use = 'MASTER_REF';
SELECT identifier_subtype,language_code INTO
l_identifier_subtype,l_language_code
FROM hz_geography_identifiers
WHERE geography_id = p_master_relation_rec.geography_id
AND identifier_type = 'CODE'
AND primary_flag = 'Y'
AND geography_use = 'MASTER_REF';
SELECT count(*) INTO l_count FROM HZ_HIERARCHY_NODES
WHERE hierarchy_type='MASTER_REF'
AND child_id=p_master_relation_rec.geography_id
AND parent_object_type='COUNTRY'
AND NVL(status,'A') = 'A'
AND level_number = 1;
SELECT count(subject_id) INTO l_parent_count
FROM HZ_RELATIONSHIPS
WHERE object_id = p_master_relation_rec.geography_id
AND object_type=l_geography_type
AND object_table_name='HZ_GEOGRAPHIES'
AND subject_table_name = 'HZ_GEOGRAPHIES'
AND relationship_type='MASTER_REF'
AND relationship_code = 'PARENT_OF'
AND status = 'A'
AND rownum <3;
| Updates a relation between master geographies
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
|
| p_master_relation_rec
| p_object_version_number
| OUT:
|
| x_return_status
|
| IN/ OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
| 11-22-02 o Rekha Nalluri created.
| 12-14-12 o Nishant Singhai Modified cursor c_get_all_children
| for bug 14705367
|
+===========================================================================*/
PROCEDURE do_update_relationship(
p_relationship_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_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
l_update_flag VARCHAR2(1);
SELECT child_id, child_object_type
FROM hz_hierarchy_nodes
WHERE hierarchy_type='MASTER_REF'
AND parent_id=l_geography_id
AND child_table_name = 'HZ_GEOGRAPHIES'
AND NVL(status,'A') = 'A'
AND (effective_end_date IS NULL
OR effective_end_date > sysdate)
;
p_create_update_flag => 'U',
p_column => 'object_version_number',
p_column_value => p_object_version_number,
x_return_status => x_return_status
);
SELECT subject_id,object_id,relationship_type into l_parent_geography_id,l_geography_id,l_relationship_type
FROM hz_relationships
WHERE relationship_id = p_relationship_id
AND relationship_code = 'PARENT_OF';
HZ_RELATIONSHIP_V2PUB.update_relationship(
p_init_msg_list =>'F',
p_relationship_rec => l_relationship_rec,
p_object_version_number => p_object_version_number,
p_party_object_version_number => l_party_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT count(subject_id) INTO l_parent_count
FROM HZ_RELATIONSHIPS
WHERE object_id = l_geography_id
AND object_type=l_geography_type
AND object_table_name='HZ_GEOGRAPHIES'
AND subject_table_name = 'HZ_GEOGRAPHIES'
AND relationship_type='MASTER_REF'
AND relationship_code = 'PARENT_OF'
AND status = 'A';
l_update_flag := 'Y';
l_update_flag := 'Y';
l_update_flag := 'Y';
IF l_update_flag = 'Y' THEN
-- call relationship API for update
HZ_RELATIONSHIP_V2PUB.update_relationship(
p_init_msg_list =>'F',
p_relationship_rec => l_relationship_rec,
p_object_version_number => p_object_version_number,
p_party_object_version_number => l_party_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
UPDATE hz_geographies
SET multiple_parent_flag = 'N'
WHERE geography_id = l_geography_id;
UPDATE hz_geographies
SET multiple_parent_flag = 'Y'
WHERE geography_id = l_geography_id;
END do_update_relationship;
SELECT subject_id
FROM hz_relationships
WHERE object_id = p_geo_identifier_rec.geography_id
AND relationship_type = 'MASTER_REF'
AND status = 'A';
p_create_update_flag => 'C',
x_return_status => x_return_status
);
SELECT geography_use,geography_type INTO l_geography_use,l_geography_type
FROM HZ_GEOGRAPHIES
WHERE geography_id = p_geo_identifier_rec.geography_id;
SELECT count(*) INTO l_count
FROM hz_geography_identifiers
WHERE identifier_type = p_geo_identifier_rec.identifier_type
AND identifier_subtype = l_identifier_subtype
AND language_code = p_geo_identifier_rec.language_code
AND UPPER(identifier_value) = UPPER(p_geo_identifier_rec.identifier_value)
AND geography_type = 'COUNTRY';
SELECT count(*) INTO l_count from
hz_geography_identifiers
WHERE geography_id = p_geo_identifier_rec.geography_id
AND language_code = l_language_code;
SELECT count(*) INTO l_count
FROM hz_geography_identifiers
WHERE geography_id = p_geo_identifier_rec.geography_id
AND identifier_type = 'NAME'
AND identifier_subtype = 'STANDARD_NAME'
AND primary_flag = 'Y'
AND language_code = l_language_code;
UPDATE hz_geography_identifiers
SET primary_flag = 'N'
WHERE geography_id = p_geo_identifier_rec.geography_id
AND identifier_type = 'NAME'
AND identifier_subtype = 'STANDARD_NAME'
AND primary_flag = 'Y'
AND language_code = l_language_code;
SELECT count(*) INTO l_count
FROM HZ_GEOGRAPHY_IDENTIFIERS
WHERE geography_id = p_geo_identifier_rec.geography_id
AND identifier_type = p_geo_identifier_rec.identifier_type
AND primary_flag='Y';
UPDATE hz_geography_identifiers
SET primary_flag = 'N'
WHERE geography_id=p_geo_identifier_rec.geography_id
AND identifier_type = p_geo_identifier_rec.identifier_type
AND primary_flag = 'Y';
HZ_GEOGRAPHY_IDENTIFIERS_PKG.insert_row(
x_rowid => l_rowid,
x_geography_id => p_geo_identifier_rec.geography_id,
x_identifier_subtype => l_identifier_subtype,
x_identifier_value => l_identifier_value,
x_geo_data_provider => p_geo_identifier_rec.geo_data_provider,
x_object_version_number => 1,
x_identifier_type => p_geo_identifier_rec.identifier_type,
x_primary_flag => p_geo_identifier_rec.primary_flag,
x_language_code => UPPER(l_language_code),
x_geography_use => l_geography_use,
x_geography_type => UPPER(l_geography_type),
x_created_by_module => p_geo_identifier_rec.created_by_module,
x_application_id => p_geo_identifier_rec.application_id,
x_program_login_id => NULL
);
SELECT distinct geography_element_column,country_code
INTO l_geo_element_col,l_country_code
FROM HZ_GEO_STRUCTURE_LEVELS
WHERE geography_id = (SELECT geography_id FROM
HZ_GEOGRAPHIES WHERE COUNTRY_CODE=(SELECT country_code from hz_geographies
WHERE geography_id = l_geography_id)
AND geography_type='COUNTRY')
AND geography_type = l_geography_type;
UPDATE HZ_GEOGRAPHIES
SET geography_code = p_geo_identifier_rec.identifier_value,
country_code = p_geo_identifier_rec.identifier_value
WHERE geography_id = p_geo_identifier_rec.geography_id;
UPDATE HZ_GEOGRAPHIES
-- Bug 4579868 : ISSUE # 11
-- denormalize upper code and not identifier_value directly
-- SET geography_code = p_geo_identifier_rec.identifier_value
SET geography_code = l_identifier_value,
last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
last_update_date = hz_utility_pub.LAST_UPDATE_DATE
WHERE geography_id = p_geo_identifier_rec.geography_id;
EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_code||'= :l_identifier_value '||
' WHERE country_code= :l_country_code '||
' AND '||l_geo_element_id||'= :l_geography_id '
USING l_identifier_value, l_country_code, l_geography_id;
UPDATE HZ_GEOGRAPHIES
SET geography_name = p_geo_identifier_rec.identifier_value,
last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
last_update_date = hz_utility_pub.LAST_UPDATE_DATE
WHERE geography_id = p_geo_identifier_rec.geography_id;
EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_col||'= :l_identifier_value '||
' WHERE country_code= :l_country_code '||
' AND '||l_geo_element_id||'= :l_geography_id '
USING l_identifier_value, l_country_code, l_geography_id;
PROCEDURE do_update_geo_identifier(
p_geo_identifier_rec IN GEO_IDENTIFIER_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_cp_request_id OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
)IS
l_count NUMBER;
l_subtype_updated VARCHAR2(1);
l_name_updated VARCHAR2(1);
SELECT subject_id
FROM hz_relationships
WHERE object_id = p_geo_identifier_rec.geography_id
AND object_table_name = 'HZ_GEOGRAPHIES'
AND relationship_type = 'MASTER_REF'
AND status = 'A';
p_create_update_flag => 'U',
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 rowid,geography_type,geography_use,primary_flag,object_version_number
INTO l_rowid,l_geography_type,l_geography_use,l_old_primary_flag,l_object_version_number
FROM hz_geography_identifiers
WHERE geography_id = p_geo_identifier_rec.geography_id
AND identifier_type = p_geo_identifier_rec.identifier_type
AND identifier_subtype = p_geo_identifier_rec.identifier_subtype
AND identifier_value = p_geo_identifier_rec.identifier_value
AND language_code = p_geo_identifier_rec.language_code
FOR UPDATE of geography_id NOWAIT;
l_subtype_updated := 'Y';
ELSE -- new geo subtype is null (i.e. no need to update), use the old subtype
l_new_geo_subtype := l_geo_identifier_subtype;
l_subtype_updated := 'N';
l_subtype_updated := 'Y';
ELSE -- new geo subtype is null (i.e. no need to update), use the old subtype
l_new_geo_subtype := l_geo_identifier_subtype;
l_subtype_updated := 'N';
l_name_updated := 'Y';
ELSE -- not to be updated, so retain the old value
l_new_geo_value := p_geo_identifier_rec.identifier_value;
l_name_updated := 'N';
IF (l_name_updated = 'Y') THEN
IF l_geography_use = 'MASTER_REF' THEN
IF l_geography_type <> 'COUNTRY' THEN
-- check for the duplicate name/code with in the parents of the geography
OPEN c_get_all_parents;
SELECT count(*) INTO l_count
FROM hz_geography_identifiers
WHERE identifier_type='NAME'
AND identifier_subtype = l_new_geo_subtype
AND language_code = p_geo_identifier_rec.language_code
AND UPPER(identifier_value) = UPPER(l_new_geo_value)
AND geography_id IN (SELECT object_id
FROM hz_relationships
WHERE subject_id = l_get_all_parents.subject_id
AND object_type = l_geography_type
AND status = 'A'
AND relationship_type = 'MASTER_REF')
AND ROWID <> l_rowid ;
SELECT count(*) INTO l_count
FROM hz_geography_identifiers
WHERE identifier_type='CODE'
AND identifier_subtype = l_new_geo_subtype
AND language_code = p_geo_identifier_rec.language_code
AND identifier_value = UPPER(l_new_geo_value)
AND geography_id IN (SELECT object_id
FROM hz_relationships
WHERE subject_id = l_get_all_parents.subject_id
AND object_type = l_geography_type
AND status = 'A'
AND relationship_type = 'MASTER_REF')
AND ROWID <> l_rowid ;
SELECT count(*) INTO l_count
FROM hz_geography_identifiers
WHERE identifier_type = p_geo_identifier_rec.identifier_type
AND identifier_subtype = l_new_geo_subtype
AND language_code = p_geo_identifier_rec.language_code
AND UPPER(identifier_value) = UPPER(l_new_geo_value)
AND geography_type = 'COUNTRY'
AND rowid <> l_rowid;
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
SELECT count(*) INTO l_count
FROM HZ_GEOGRAPHY_IDENTIFIERS
WHERE geography_id = p_geo_identifier_rec.geography_id
AND identifier_type = p_geo_identifier_rec.identifier_type
AND primary_flag='Y'
AND language_code = p_geo_identifier_rec.language_code;
UPDATE hz_geography_identifiers
SET primary_flag = 'N'
WHERE geography_id=p_geo_identifier_rec.geography_id
AND identifier_type = p_geo_identifier_rec.identifier_type
AND primary_flag = 'Y'
AND language_code = p_geo_identifier_rec.language_code;
-- --dbms_output.put_line ( 'After update of primary from Y to N');
hz_geography_identifiers_pkg.update_row(
x_rowid => l_rowid,
x_geography_id => p_geo_identifier_rec.geography_id,
x_identifier_subtype => l_new_geo_subtype,
x_identifier_value => l_new_geo_value,
x_geo_data_provider => p_geo_identifier_rec.geo_data_provider,
x_object_version_number => p_object_version_number,
x_identifier_type => p_geo_identifier_rec.identifier_type,
x_primary_flag => p_geo_identifier_rec.primary_flag,
x_language_code => p_geo_identifier_rec.language_code,
x_geography_use => NULL,
x_geography_type => NULL,
x_created_by_module => NULL,
x_application_id => NULL,
x_program_login_id => NULL);
(l_name_updated = 'Y'))
)
)
THEN
x_cp_request_id := fnd_request.submit_request(
application => 'AR',
program => 'ARHGEOEU',
argument1 => p_geo_identifier_rec.geography_id,
argument2 => p_geo_identifier_rec.identifier_type,
argument3 => l_new_geo_value);
(l_name_updated = 'Y'))
)
)
THEN
-- (For TAX, Logic added by Nishant on 27-Oct-2005 for Bug 4578867)
-- FOR geography_use = 'TAX' we dont have any hierarchy (structure),
-- so coulmns geography_element1,geography_element1_name,geography_element1_code...
-- are all null.and the only columns which need to be modified in hz_geographies
-- are geography_name and geography_code
IF p_geo_identifier_rec.identifier_type = 'CODE' THEN
UPDATE HZ_GEOGRAPHIES
SET geography_code = l_new_geo_value,
last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
last_update_date = hz_utility_pub.LAST_UPDATE_DATE
WHERE geography_id = p_geo_identifier_rec.geography_id
AND geography_use = l_geography_use;
UPDATE HZ_GEOGRAPHIES
SET geography_name = l_new_geo_value,
last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
last_update_date = hz_utility_pub.LAST_UPDATE_DATE
WHERE geography_id = p_geo_identifier_rec.geography_id
AND geography_use = l_geography_use;
END do_update_geo_identifier;
PROCEDURE do_delete_geo_identifier(
p_geography_id IN NUMBER,
p_identifier_type IN VARCHAR2,
p_identifier_subtype IN VARCHAR2,
p_identifier_value IN VARCHAR2,
p_language_code IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_primary_flag VARCHAR2(1);
l_delete_flag VARCHAR2(1);
l_delete_flag := 'Y';
SELECT primary_flag INTO l_primary_flag
FROM hz_geography_identifiers
WHERE geography_id = p_geography_id
AND identifier_type = p_identifier_type
AND identifier_subtype = p_identifier_subtype
AND identifier_value = p_identifier_value
AND language_code = p_language_code;
l_delete_flag := 'N';
FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NONDELETEABLE' );
select count(*) INTO l_count
from hz_geography_identifiers
where geography_id = p_geography_id
AND language_code = p_language_code
AND identifier_type = 'NAME'
;
UPDATE hz_geography_identifiers
SET identifier_subtype = 'STANDARD_NAME'
WHERE geography_id = p_geography_id
AND identifier_type= p_identifier_type
AND identifier_subtype <> p_identifier_subtype
AND identifier_value <> p_identifier_value
AND language_code = p_language_code
AND rownum < 2;
l_delete_flag := 'Y';
l_delete_flag := 'Y';
IF l_delete_flag = 'Y' THEN
HZ_GEOGRAPHY_IDENTIFIERS_PKG.delete_row(
x_geography_id => p_geography_id,
x_identifier_subtype => p_identifier_subtype,
x_identifier_value => p_identifier_value,
x_language_code => p_language_code,
x_identifier_type => p_identifier_type
);
END do_delete_geo_identifier;
p_create_update_flag => 'C',
x_return_status => x_return_status
);
SELECT country_code INTO l_country_code
FROM HZ_GEOGRAPHIES
WHERE geography_id= l_parent_geography_tbl(1);
HZ_GEOGRAPHIES_PKG.insert_row(
x_rowid => l_rowid,
x_geography_id => x_geography_id,
x_object_version_number => 1,
x_geography_type => UPPER(p_master_geography_rec.geography_type),
x_geography_name => p_master_geography_rec.geography_name,
x_geography_use => 'MASTER_REF',
x_geography_code => UPPER(p_master_geography_rec.geography_code),
x_start_date => p_master_geography_rec.start_date,
x_end_date => p_master_geography_rec.end_date,
x_multiple_parent_flag => 'N',
x_created_by_module => p_master_geography_rec.created_by_module,
x_country_code => l_country_code,
x_geography_element1 => NULL,
x_geography_element1_id => NULL,
x_geography_element1_code => NULL,
x_geography_element2 => NULL,
x_geography_element2_id => NULL,
x_geography_element2_code => NULL,
x_geography_element3 => NULL,
x_geography_element3_id => NULL,
x_geography_element3_code => NULL,
x_geography_element4 => NULL,
x_geography_element4_id => NULL,
x_geography_element4_code => NULL,
x_geography_element5 => NULL,
x_geography_element5_id => NULL,
x_geography_element5_code => NULL,
x_geography_element6 => NULL,
x_geography_element6_id => NULL,
x_geography_element7 => NULL,
x_geography_element7_id => NULL,
x_geography_element8 => NULL,
x_geography_element8_id => NULL,
x_geography_element9 => NULL,
x_geography_element9_id => NULL,
x_geography_element10 => NULL,
x_geography_element10_id => NULL,
x_geometry => p_master_geography_rec.geometry,
x_timezone_code => p_master_geography_rec.timezone_code,
x_application_id => p_master_geography_rec.application_id,
x_program_login_id => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL
);
PROCEDURE do_update_geography(
p_geography_id IN NUMBER,
p_end_date IN DATE,
p_geometry IN MDSYS.SDO_GEOMETRY,
p_timezone_code IN VARCHAR2,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_rowid VARCHAR2(64);
SELECT distinct relationship_id,object_version_number
FROM HZ_RELATIONSHIPS
WHERE (subject_id = p_geography_id
OR object_id = p_geography_id)
AND relationship_type= l_geography_use
AND l_geography_use = 'MASTER_REF' --Bug5265511
AND status = 'A'; --Bug5454824
p_create_update_flag => 'U',
p_column => 'object_version_number',
p_column_value => p_object_version_number,
x_return_status => x_return_status
);
SELECT rowid,start_date,end_date,geography_use,object_version_number INTO l_rowid,l_start_date,l_end_date,
l_geography_use,l_object_version_number
FROM HZ_GEOGRAPHIES
WHERE geography_id=p_geography_id
FOR UPDATE of geography_id NOWAIT;
p_create_update_flag => 'U',
p_start_date_column_name => 'start_date',
p_start_date => l_start_date,
p_old_start_date => l_start_date,
p_end_date_column_name => 'end_date',
p_end_date => p_end_date,
p_old_end_date => l_end_date,
x_return_status => x_return_status
);
SELECT count(*) INTO l_count
FROM FND_TIMEZONES_B
WHERE timezone_code = p_timezone_code
AND rownum <2;
HZ_GEOGRAPHIES_PKG.update_row(
x_rowid => l_rowid,
x_geography_id => p_geography_id,
x_object_version_number => p_object_version_number,
x_geography_type => NULL,
x_geography_name => NULL,
x_geography_use => NULL,
x_geography_code => NULL,
x_start_date => NULL,
x_end_date => l_end_date,
x_multiple_parent_flag => NULL,
x_created_by_module => NULL,
x_country_code => NULL,
x_geography_element1 => NULL,
x_geography_element1_id => NULL,
x_geography_element1_code => NULL,
x_geography_element2 => NULL,
x_geography_element2_id => NULL,
x_geography_element2_code => NULL,
x_geography_element3 => NULL,
x_geography_element3_id => NULL,
x_geography_element3_code => NULL,
x_geography_element4 => NULL,
x_geography_element4_id => NULL,
x_geography_element4_code => NULL,
x_geography_element5 => NULL,
x_geography_element5_id => NULL,
x_geography_element5_code => NULL,
x_geography_element6 => NULL,
x_geography_element6_id => NULL,
x_geography_element7 => NULL,
x_geography_element7_id => NULL,
x_geography_element8 => NULL,
x_geography_element8_id => NULL,
x_geography_element9 => NULL,
x_geography_element9_id => NULL,
x_geography_element10 => NULL,
x_geography_element10_id => NULL,
x_geometry => p_geometry,
x_timezone_code => p_timezone_code,
x_application_id => NULL,
x_program_login_id => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL
);
HZ_RELATIONSHIP_V2PUB.update_relationship(
p_init_msg_list => 'F',
p_relationship_rec => l_relationship_rec,
p_object_version_number => l_get_all_relationships.object_version_number,
p_party_object_version_number => l_party_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
/* update hz_relationships
set status = l_status,
end_date = p_end_date
WHERE relationship_id=l_get_all_relationships.relationship_id;*/
/* update hz_hierarchy_nodes
set effective_end_date = p_end_date
where hierarchy_type=l_geography_use
and parent_id = p_geography_id
or child_id = p_geography_id ; */
END do_update_geography;
SELECT geography_type, country_code
INTO l_parent_geo_type, l_country_code
FROM hz_geographies
WHERE geography_id = l_geography_range_rec.master_ref_geography_id
AND geography_use = 'MASTER_REF'
AND TRUNC(SYSDATE) BETWEEN START_DATE AND end_date
;
SELECT st.geography_type
INTO l_child_geo_type
FROM hz_geo_structure_levels st
,hz_geography_types_b tp
WHERE st.country_code = l_country_code
AND st.parent_geography_type = l_parent_geo_type
AND st.geography_type = tp.geography_type
AND tp.geography_use = 'MASTER_REF'
AND tp.postal_code_range_flag = 'Y'
AND tp.geography_use = 'MASTER_REF'
AND ROWNUM < 2
;
SELECT COUNT(*)
INTO l_count
FROM hz_geography_identifiers id
WHERE UPPER(id.identifier_value) = l_master_geography_rec.geography_name
AND id.geography_use = 'MASTER_REF'
AND id.identifier_type = 'NAME'
AND id.identifier_subtype = 'STANDARD_NAME'
AND EXISTS ( SELECT '1'
FROM hz_relationships rel
WHERE rel.subject_id = l_geography_range_rec.master_ref_geography_id
AND rel.object_id = id.geography_id
AND rel.object_type = l_master_geography_rec.geography_type
AND rel.status = 'A'
AND rel.relationship_type = 'MASTER_REF');
/*SELECT count(*) INTO l_count from hz_geography_ranges
WHERE geography_id = p_geography_range_rec.zone_id
AND geography_from = p_geography_range_rec.geography_from
AND to_char(start_date,'DD-MON-YYYY') = to_char(p_geography_range_rec.start_date,'DD_MON-YYYY');
p_create_update_flag => 'C',
x_return_status => x_return_status
);
SELECT geography_use INTO l_geography_use
FROM hz_geography_types_b
WHERE geography_type=l_zone_type;
hz_geography_ranges_pkg.insert_row (
x_rowid => l_rowid,
x_geography_id => p_geography_range_rec.zone_id,
x_geography_from => p_geography_range_rec.geography_from,
x_start_date => p_geography_range_rec.start_date,
x_object_version_number => 1,
x_geography_to => p_geography_range_rec.geography_to,
x_identifier_type => p_geography_range_rec.identifier_type,
x_end_date => p_geography_range_rec.end_date,
-- Dhaval : Use queried geography_type
x_geography_type => l_zone_type,
x_geography_use => l_geography_use,
x_master_ref_geography_id => p_geography_range_rec.master_ref_geography_id,
x_created_by_module => p_geography_range_rec.created_by_module,
x_application_id => p_geography_range_rec.application_id,
x_program_login_id => NULL
);
PROCEDURE do_update_geography_range(
p_geography_id IN NUMBER,
p_geography_from IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_rowid ROWID;
p_create_update_flag => 'U',
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 rowid,start_date,end_date,object_version_number INTO l_rowid,l_start_date,l_end_date,l_object_version_number
FROM hz_geography_ranges
WHERE geography_id = p_geography_id
AND geography_from = p_geography_from
AND start_date = p_start_date
FOR UPDATE OF geography_id,geography_from,start_date NOWAIT;
p_create_update_flag => 'U',
p_start_date_column_name => 'start_date',
p_start_date => p_start_date,
p_old_start_date => l_start_date,
p_end_date_column_name => 'end_date',
p_end_date => p_end_date,
p_old_end_date => l_end_date,
x_return_status => x_return_status
);
HZ_GEOGRAPHY_RANGES_PKG.update_row(
x_rowid => l_rowid,
x_geography_id => p_geography_id,
x_geography_from => p_geography_from,
x_start_date => p_start_date,
x_object_version_number => p_object_version_number,
x_geography_to => NULL,
x_identifier_type => NULL,
x_end_date => p_end_date,
x_geography_type => NULL,
x_geography_use => NULL,
x_master_ref_geography_id => NULL,
x_created_by_module => NULL,
x_application_id => NULL,
x_program_login_id => NULL
);
END do_update_geography_range;
SELECT geography_use,limited_by_geography_id INTO l_geography_use,l_limited_by_geography_id
FROM hz_geography_types_b
WHERE geography_type = l_zone_type;
p_create_update_flag => 'C',
x_return_status => x_return_status
);
SELECT 1 INTO l_count
FROM hz_hierarchy_nodes
WHERE parent_id = l_limited_by_geography_id
AND child_id = p_zone_relation_tbl(i).included_geography_id
AND hierarchy_type = 'MASTER_REF'
AND NVL(status,'A') = 'A'
AND (effective_end_date IS NULL
OR effective_end_date > sysdate
)
AND rownum < 2;
SELECT count(*) INTO l_COUNT
FROM HZ_RELATIONSHIP_TYPES
WHERE subject_type = l_zone_type
AND object_type= l_incl_geo_type
AND forward_rel_code = 'PARENT_OF'
AND backward_rel_code = 'CHILD_OF'
AND relationship_type = 'TAX';
SELECT count(*) INTO l_count
FROM hz_relationships
WHERE relationship_type = l_geography_use
-- AND subject_type = l_zone_type
AND subject_id = p_geography_id
AND object_id = p_zone_relation_tbl(i).included_geography_id
AND sysdate between start_date and nvl(end_date, sysdate + 1)
AND status = 'A'
AND rownum < 2;
SELECT count(*) INTO l_count
FROM hz_relationships
WHERE relationship_type=l_geography_use
AND subject_type=l_zone_type
AND subject_id = p_geography_id
AND object_id = p_zone_relation_tbl(i).included_geography_id
AND sysdate between start_date and nvl(end_date, sysdate + 1)
AND status = 'A'
AND rownum <2;
p_create_update_flag =>'C',
p_column => 'zone_type',
p_column_value => p_zone_type,
p_restricted => 'N',
x_return_status => x_return_status
);
p_create_update_flag =>'C',
p_column => 'zone_name',
p_column_value => p_zone_name,
p_restricted => 'N',
x_return_status => x_return_status
);
SELECT geography_use,limited_by_geography_id INTO l_geography_use,l_limited_by_geography_id
FROM hz_geography_types_b
WHERE geography_type = p_zone_type;
SELECT count(*) INTO l_count
FROM hz_geographies
WHERE geography_name = p_zone_name
AND geography_type = p_zone_type
AND rownum <2;
SELECT count(*) INTO l_count
FROM hz_geographies
WHERE geography_code = upper(p_zone_code)
AND geography_type = p_zone_type
AND rownum <2;
SELECT count(*) INTO l_count
FROM fnd_timezones_b
WHERE timezone_code = p_timezone_code
AND rownum <2;
SELECT country_code INTO l_country_code
FROM hz_geographies
WHERE geography_id = p_zone_relation_tbl(i).included_geography_id;
HZ_GEOGRAPHIES_PKG.insert_row(
x_rowid => l_rowid,
x_geography_id => x_geography_id,
x_object_version_number => 1,
x_geography_type => p_zone_type,
x_geography_name => p_zone_name,
x_geography_use => l_geography_use,
x_geography_code => UPPER(p_zone_code),
x_start_date => NVL(p_start_date,SYSDATE),
x_end_date => NVL(p_end_date,l_end_date),
x_multiple_parent_flag => 'N',
x_created_by_module => p_created_by_module,
x_country_code => l_country_code,
x_geography_element1 => NULL,
x_geography_element1_id => NULL,
x_geography_element1_code => NULL,
x_geography_element2 => NULL,
x_geography_element2_id => NULL,
x_geography_element2_code => NULL,
x_geography_element3 => NULL,
x_geography_element3_id => NULL,
x_geography_element3_code => NULL,
x_geography_element4 => NULL,
x_geography_element4_id => NULL,
x_geography_element4_code => NULL,
x_geography_element5 => NULL,
x_geography_element5_id => NULL,
x_geography_element5_code => NULL,
x_geography_element6 => NULL,
x_geography_element6_id => NULL,
x_geography_element7 => NULL,
x_geography_element7_id => NULL,
x_geography_element8 => NULL,
x_geography_element8_id => NULL,
x_geography_element9 => NULL,
x_geography_element9_id => NULL,
x_geography_element10 => NULL,
x_geography_element10_id => NULL,
x_geometry => p_geometry,
x_timezone_code => p_timezone_code,
x_application_id => p_application_id,
x_program_login_id => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL
);
SELECT userenv('LANG') INTO l_language_code FROM dual;
* PROCEDURE update_relationship
*
* DESCRIPTION
* Updates Geography Relationships.
*
* 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_master_relation_rec Geography type record.
* p_object_version_number Object version number of the row
* 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
* 11-22-2002 Rekha Nalluri o Created.
*
*/
PROCEDURE update_relationship (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_relationship_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
BEGIN
-- Standard start of API savepoint
SAVEPOINT update_relationship;
do_update_relationship(
p_relationship_id => p_relationship_id,
p_status => p_status,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status
);
ROLLBACK TO update_relationship;
ROLLBACK TO update_relationship;
ROLLBACK TO update_relationship;
END update_relationship;
* PROCEDURE update_geo_identifier
*
* DESCRIPTION
* Creates Geography Identifiers.
*
* 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_identifier_rec Geo_identifier type record.
*
* IN/OUT:
* p_object_version_number
* OUT:
*
* x_cp_request_id Concurrent Program Request Id, whenever CP
* to update denormalized data gets kicked off.
* 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-03-2002 Rekha Nalluri o Created.
* 21-Oct-2005 Nishant Added x_cp_request_id OUT parameter
* for Bug 457886
*
*/
PROCEDURE update_geo_identifier (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_geo_identifier_rec IN GEO_IDENTIFIER_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_cp_request_id 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_identifier;
do_update_geo_identifier(
p_geo_identifier_rec => p_geo_identifier_rec,
p_object_version_number => p_object_version_number,
x_cp_request_id => x_cp_request_id,
x_return_status => x_return_status
);
ROLLBACK TO update_geo_identifier;
ROLLBACK TO update_geo_identifier;
ROLLBACK TO update_geo_identifier;
END update_geo_identifier;
* PROCEDURE delete_geo_identifier
*
* DESCRIPTION
* Deletes Geography Identifiers.
*
* 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_id geography id
* p_identifier_type
* p_identifier_subtype
* p_identifier_value
*
* 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-02-2003 Rekha Nalluri o Created.
*
*/
PROCEDURE delete_geo_identifier(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_geography_id IN NUMBER,
p_identifier_type IN VARCHAR2,
p_identifier_subtype IN VARCHAR2,
p_identifier_value IN VARCHAR2,
p_language_code 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_identifier;
do_delete_geo_identifier(
p_geography_id => p_geography_id,
p_identifier_type => p_identifier_type,
p_identifier_subtype => p_identifier_subtype,
p_identifier_value => p_identifier_value,
p_language_code => p_language_code,
x_return_status => x_return_status
);
ROLLBACK TO delete_geo_identifier;
ROLLBACK TO delete_geo_identifier;
ROLLBACK TO delete_geo_identifier;
END delete_geo_identifier;
* PROCEDURE update_geography
*
* DESCRIPTION
* Updates Geography
*
* 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_master_geography_rec Master Geography type record.
*
* IN/OUT:
* p_object_version_number
* 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-12-2002 Rekha Nalluri o Created.
*
*/
PROCEDURE update_geography (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_geography_id IN NUMBER,
p_end_date IN DATE,
p_geometry IN MDSYS.SDO_GEOMETRY,
p_timezone_code 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_geography;
do_update_geography(
p_geography_id => p_geography_id,
p_end_date => p_end_date,
p_geometry => p_geometry,
p_timezone_code => p_timezone_code,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status
);
ROLLBACK TO update_geography;
ROLLBACK TO update_geography;
ROLLBACK TO update_geography;
END update_geography;
* PROCEDURE update_geography_range
*
* DESCRIPTION
* Updates Geography range
*
* 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.
* geography_id
* geography_from
* start_date
* end_date
*
* IN/OUT:
* p_object_version_number
* 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-23-2003 Rekha Nalluri o Created.
*
*/
PROCEDURE update_geography_range (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_geography_id IN NUMBER,
p_geography_from IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
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_geography_range;
do_update_geography_range(
p_geography_id => p_geography_id,
p_geography_from => p_geography_from,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status
);
ROLLBACK TO update_geography_range;
ROLLBACK TO update_geography_range;
ROLLBACK TO update_geography_range;
END update_geography_range;