[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rank
FROM hz_dss_groups_b
WHERE dss_group_code= p_dss_group_code ;
SELECT NVL(MAX(RANK),0)+ 1 FROM HZ_DSS_GROUPS_B;
PROCEDURE resequence_ranks_to_create ( p_insert_before_group_rank IN NUMBER )
-- Resequence ranks in hz_dss_groups_b in lieu of creating a new DSG
IS
BEGIN
update hz_dss_groups_b set rank = rank + 1
where rank >= p_insert_before_group_rank ;
PROCEDURE resequence_ranks_to_update ( p_group_to_be_updated_rank IN NUMBER,
p_insert_before_group_rank IN NUMBER,
p_to_be_upd_group_code IN VARCHAR2,
p_order_before_group_code IN VARCHAR2)
-- Resequence ranks in hz_dss_groups_b in lieu of updating the rank of a DSG
IS
BEGIN
-- NOTE: IN THIS PROCEDURE WE DO NOT UPDATE THE RANK OF THE GROUP WHOSE RANK
-- NEEDS TO BE UPDATED. WE RESEQUENCE EVERYTHING ELSE OTHER THAN THAT.
-- INSERT BEFORE AN EXISTING GROUP CODE
IF p_order_before_group_code IS NOT NULL AND
p_order_before_group_code <> FND_API.G_MISS_CHAR
THEN
-----------------------------------------------------------------------------------
-- RESEQUENCE 1: STARTS FROM (INCLUDING) LEVEL OF INSERT BEFORE GROUP AND GOES DOWN.
-- THE GROUP TO BE UPDATED IS LEFT ALONE
-----------------------------------------------------------------------------------
-- FREEZE THE RANK OF GROUP TO BE UPDATED AND INCREMENT EVERY RANK THAT IS BIGGER
-- THAN THE RANK THAT WE WOULD WANT OUR GROUP TO MOVE INTO, AFTER THE UPDATE.
update hz_dss_groups_b set rank = rank + 1
where rank >= p_insert_before_group_rank and dss_group_code <> p_to_be_upd_group_code ;
update hz_dss_groups_b set rank = rank - 1
where rank > p_group_to_be_updated_rank ;
update hz_dss_groups_b set rank = rank - 1
where rank > p_group_to_be_updated_rank ;
END resequence_ranks_to_update;
HZ_DSS_GROUPS_PKG.Insert_Row (
x_rowid => row_id ,
x_dss_group_code => p_dss_group.dss_group_code,
x_rank => rank ,
x_status => nvl(p_dss_group.status,'A'),
x_dss_group_name => p_dss_group.dss_group_name,
x_description => p_dss_group.description,
x_bes_enable_flag => nvl(p_dss_group.bes_enable_flag,'Y'),
x_object_version_number => 1);
* PROCEDURE update_group
*
* DESCRIPTION
* Updates a data sharing group.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
*
* ARGUMENTS
*
*
* NOTES
*
* MODIFICATION HISTORY
*
* 08-13-2002 Colathur Vijayan ("VJN") o Created.
*
*/
PROCEDURE update_group (
p_init_msg_list IN VARCHAR2,
p_dss_group IN dss_group_rec_type,
x_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
rank NUMBER;
SAVEPOINT update_group;
FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
SELECT object_version_number, rowid, rank, status
INTO l_object_version_number, l_rowid, l_rank, l_status
FROM hz_dss_groups_b
WHERE dss_group_code = p_dss_group.dss_group_code
FOR UPDATE NOWAIT;
resequence_ranks_to_update(
temp1, temp2, p_dss_group.dss_group_code,
p_dss_group.order_before_group_code);
resequence_ranks_to_update(
temp1, temp2, p_dss_group.dss_group_code,
p_dss_group.order_before_group_code);
hz_dss_grants_pub.update_grant (
p_dss_group_code => p_dss_group.dss_group_code,
p_dss_group_status => p_dss_group.status,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
HZ_DSS_GROUPS_PKG.Update_Row (
x_rowid => l_rowid ,
x_rank => rank ,
x_status => p_dss_group.status,
x_dss_group_name => p_dss_group.dss_group_name,
x_description => p_dss_group.description,
x_bes_enable_flag => p_dss_group.bes_enable_flag,
x_object_version_number => x_object_version_number
);
ROLLBACK TO update_group ;
ROLLBACK TO update_group ;
ROLLBACK TO update_group ;
END update_group ;
select count(*) into l_dup_count
from HZ_DSS_CRITERIA
where dss_group_code = p_dss_secured_module.dss_group_code
and owner_table_name = 'AR_LOOKUPS'
and owner_table_id1 = 'HZ_CREATED_BY_MODULES'
and owner_table_id2 = p_dss_secured_module.created_by_module;
HZ_DSS_CRITERIA_PKG.Insert_Row (
x_rowid => row_id,
x_secured_item_id => x_secured_item_id,
x_status => nvl(p_dss_secured_module.status,'A'),
x_dss_group_code => p_dss_secured_module.dss_group_code,
x_owner_table_name => 'AR_LOOKUPS',
x_owner_table_id1 => 'HZ_CREATED_BY_MODULES',
x_owner_table_id2 => p_dss_secured_module.created_by_module ,
x_object_version_number => 1 );
* PROCEDURE update_secured_module
*
* DESCRIPTION
* Updates a created_by_module based criterion
* for a data sharing sharing group
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
*
* ARGUMENTS
*
*
* NOTES
*
* MODIFICATION HISTORY
*
* 10-15-2002 Jyoti Pandey o Created.
*
------------------------------------------------------------------------*/
PROCEDURE update_secured_module (
p_init_msg_list IN VARCHAR2,
p_dss_secured_module IN dss_secured_module_type,
x_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_object_version_number NUMBER;
SAVEPOINT update_secured_module ;
SELECT object_version_number, rowid,dss_group_code,owner_table_id2
INTO l_object_version_number, l_rowid,l_dss_group_code,l_created_by_module
FROM HZ_DSS_CRITERIA
WHERE secured_item_id = p_dss_secured_module.secured_item_id
FOR UPDATE NOWAIT;
HZ_DSS_CRITERIA_PKG.Update_Row (
x_rowid => l_rowid,
x_status => p_dss_secured_module.status,
x_dss_group_code => p_dss_secured_module.dss_group_code,
x_owner_table_name => 'AR_LOOKUPS',
x_owner_table_id1 => 'HZ_CREATED_BY_MODULES',
x_owner_table_id2 => p_dss_secured_module.created_by_module ,
x_object_version_number => x_object_version_number);
ROLLBACK TO update_secured_module ;
ROLLBACK TO update_secured_module ;
ROLLBACK TO update_secured_module ;
END update_secured_module ;
HZ_DSS_CRITERIA_PKG.Insert_Row (
x_rowid => row_id,
x_secured_item_id => x_secured_item_id,
x_status => nvl(p_dss_secured_criterion.status,'A'),
x_dss_group_code => p_dss_secured_criterion.dss_group_code,
x_owner_table_name => p_dss_secured_criterion.owner_table_name,
x_owner_table_id1 => p_dss_secured_criterion.owner_table_id1 ,
x_owner_table_id2 => p_dss_secured_criterion.owner_table_id2 ,
x_object_version_number => 1);
* PROCEDURE update_secured_criterion
*
* DESCRIPTION
* Updates a criterion that determines how a data sharing sharing group
* should be assigned to an entity.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
*
* ARGUMENTS
*
*
* NOTES
*
* MODIFICATION HISTORY
*
* 08-13-2002 Colathur Vijayan ("VJN") o Created.
*
*/
PROCEDURE update_secured_criterion (
p_init_msg_list IN VARCHAR2,
p_dss_secured_criterion IN dss_secured_criterion_type,
x_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_object_version_number NUMBER;
SAVEPOINT update_secured_criterion ;
SELECT object_version_number, rowid
INTO l_object_version_number, l_rowid
FROM HZ_DSS_CRITERIA
WHERE secured_item_id = p_dss_secured_criterion.secured_item_id
FOR UPDATE NOWAIT;
update_secured_module(p_init_msg_list,
l_dss_secured_module,
x_object_version_number,
x_return_status,
x_msg_count,
x_msg_data);
HZ_DSS_CRITERIA_PKG.Update_Row (
x_rowid => l_rowid,
x_status => p_dss_secured_criterion.status,
x_dss_group_code => p_dss_secured_criterion.dss_group_code,
x_owner_table_name => p_dss_secured_criterion.owner_table_name,
x_owner_table_id1 => p_dss_secured_criterion.owner_table_id1 ,
x_owner_table_id2 => p_dss_secured_criterion.owner_table_id2 ,
x_object_version_number => x_object_version_number);
ROLLBACK TO update_secured_criterion ;
ROLLBACK TO update_secured_criterion ;
ROLLBACK TO update_secured_criterion ;
END update_secured_criterion ;
select count(*) into l_dup_count
from HZ_DSS_CRITERIA
where dss_group_code = p_dss_secured_class.dss_group_code
and owner_table_name = 'FND_LOOKUP_VALUES'
and owner_table_id1 = p_dss_secured_class.class_category
and owner_table_id2 = p_dss_secured_class.class_code;
HZ_DSS_CRITERIA_PKG.Insert_Row (
x_rowid => row_id,
x_secured_item_id => x_secured_item_id,
x_status => nvl(p_dss_secured_class.status,'A'),
x_dss_group_code => p_dss_secured_class.dss_group_code,
x_owner_table_name => 'FND_LOOKUP_VALUES',
x_owner_table_id1 => p_dss_secured_class.class_category,
x_owner_table_id2 => p_dss_secured_class.class_code,
x_object_version_number => 1);
* PROCEDURE update_secured_classification
*
* DESCRIPTION
* Updates a criterion that determines how a data sharing sharing group
* should be assigned to an entity.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
*
* ARGUMENTS
*
*
* NOTES
*
* MODIFICATION HISTORY
*
* 08-13-2002 Colathur Vijayan ("VJN") o Created.
*
*/
PROCEDURE update_secured_classification (
-- input parameters
p_init_msg_list IN VARCHAR2,
p_dss_secured_class IN dss_secured_class_type,
-- in/out parameters
x_object_version_number IN OUT NOCOPY NUMBER,
-- output parameters
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_object_version_number NUMBER;
SAVEPOINT update_secured_classification ;
SELECT object_version_number, rowid , dss_group_code,
owner_table_id1,owner_table_id2
INTO l_object_version_number, l_rowid ,l_dss_group_code ,
l_class_category , l_class_code
FROM HZ_DSS_CRITERIA
WHERE secured_item_id = p_dss_secured_class.secured_item_id
FOR UPDATE NOWAIT;
HZ_DSS_CRITERIA_PKG.Update_Row (
x_rowid => l_rowid,
x_status => p_dss_secured_class.status,
x_dss_group_code => p_dss_secured_class.dss_group_code,
x_owner_table_name => 'AR_LOOKUPS',
x_owner_table_id1 => p_dss_secured_class.class_category,
x_owner_table_id2 => p_dss_secured_class.class_code,
x_object_version_number => x_object_version_number
);
ROLLBACK TO update_secured_classification ;
ROLLBACK TO update_secured_classification ;
ROLLBACK TO update_secured_classification ;
END update_secured_classification ;
select count(*) into l_dup_count
from HZ_DSS_CRITERIA
where dss_group_code = p_dss_secured_rel_type.dss_group_code
and owner_table_name = 'HZ_RELATIONSHIP_TYPES'
and owner_table_id1 = TO_CHAR(p_dss_secured_rel_type.relationship_type_id);
select relationship_type into l_rel_type
from HZ_RELATIONSHIP_TYPES
where relationship_type_id = p_dss_secured_rel_type.relationship_type_id;
HZ_DSS_CRITERIA_PKG.Insert_Row (
x_rowid => row_id,
x_secured_item_id => x_secured_item_id,
x_status => nvl(p_dss_secured_rel_type.status,'A'),
x_dss_group_code => p_dss_secured_rel_type.dss_group_code,
x_owner_table_name => 'HZ_RELATIONSHIP_TYPES',
x_owner_table_id1 => p_dss_secured_rel_type.relationship_type_id,
x_object_version_number => 1);
* PROCEDURE update_secured_rel_type
*
* DESCRIPTION
* The UPDATE_SECURED_REL_TYPE procedure updates a record in HZ_DSS_CRITERIA that identifies
* a Relationship Type to be used as a criterion to determine if data falls under the Data Sharing
* Group. Currently, only the STATUS column can be updated at this time.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
*
* ARGUMENTS
*
*
* NOTES
*
* MODIFICATION HISTORY
*
* 08-13-2002 Colathur Vijayan ("VJN") o Created.
*
*/
PROCEDURE update_secured_rel_type (
p_init_msg_list IN VARCHAR2,
p_dss_secured_rel_type IN dss_secured_rel_type,
x_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_object_version_number NUMBER;
SAVEPOINT update_secured_rel_type ;
SELECT object_version_number, rowid , dss_group_code, owner_table_id1
INTO l_object_version_number, l_rowid,l_dss_group_code,
l_relationship_type_id
FROM HZ_DSS_CRITERIA
WHERE secured_item_id = p_dss_secured_rel_type.secured_item_id
FOR UPDATE NOWAIT;
HZ_DSS_CRITERIA_PKG.Update_Row (
x_rowid => l_rowid,
x_status => p_dss_secured_rel_type.status,
x_dss_group_code => p_dss_secured_rel_type.dss_group_code,
x_owner_table_name => 'HZ_RELATIONSHIP_TYPES',
x_owner_table_id1 => p_dss_secured_rel_type.relationship_type_id,
x_object_version_number => x_object_version_number);
ROLLBACK TO update_secured_rel_type ;
ROLLBACK TO update_secured_rel_type ;
ROLLBACK TO update_secured_rel_type ;
END update_secured_rel_type ;
HZ_DSS_ASSIGNMENTS_PKG.Insert_Row (
x_rowid => row_id,
x_assignment_id => x_assignment_id,
x_status => p_dss_assignment.status,
x_owner_table_name => p_dss_assignment.owner_table_name,
x_owner_table_id1 => p_dss_assignment.owner_table_id1,
x_owner_table_id2 => p_dss_assignment.owner_table_id2,
x_owner_table_id3 => p_dss_assignment.owner_table_id3,
x_owner_table_id4 => p_dss_assignment.owner_table_id4,
x_owner_table_id5 => p_dss_assignment.owner_table_id5,
x_dss_group_code => p_dss_assignment.dss_group_code,
x_object_version_number => 1
);
* PROCEDURE delete_assignment
*
* DESCRIPTION
* The delete_assignment procedure deletes a Data Sharing Group assignment (HZ_DSS_ASSIGNMENTS).
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
*
* ARGUMENTS
*
*
* NOTES
*
* MODIFICATION HISTORY
*
* 08-15-2002 Colathur Vijayan ("VJN") o Created.
*
*/
PROCEDURE delete_assignment (
p_init_msg_list IN VARCHAR2,
p_assignment_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
row_id varchar2(64);
SAVEPOINT delete_assignment ;
HZ_DSS_ASSIGNMENTS_PKG.Delete_Row (
x_assignment_id => p_assignment_id
);
ROLLBACK TO delete_assignment ;
ROLLBACK TO delete_assignment ;
ROLLBACK TO delete_assignment ;
END delete_assignment ;
select fnd_object_instance_sets_s.nextval
into l_dss_instance_set_id
from sys.dual;
select count(*) into l_dup_ois_cnt from fnd_object_instance_sets
where instance_set_name like l_dss_ois_name || '%';
FND_OBJECT_INSTANCE_SETS_PKG.INSERT_ROW (
x_rowid => row_id,
x_instance_set_id => l_dss_instance_set_id,
x_instance_set_name => l_dss_ois_name,
x_object_id => l_object_id,
x_predicate => l_predicate,
x_display_name => l_dss_ois_name,
x_description => l_dss_ois_name,
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 );
HZ_DSS_SECURED_ENTITIES_PKG.Insert_Row (
x_rowid => row_id ,
x_dss_group_code => p_dss_secured_entity.dss_group_code,
x_entity_id => p_dss_secured_entity.entity_id,
x_status => nvl(p_dss_secured_entity.status,'A'),
x_dss_instance_set_id => l_dss_instance_set_id,
x_object_version_number => 1
);
* PROCEDURE update_secured_entity
*
* DESCRIPTION
* The update_secured_entity procedure updates a Secured Entity record.
* Currently, the only data updateable is the STATUS column.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
*
* ARGUMENTS
*
*
* NOTES
*
* MODIFICATION HISTORY
*
* 08-13-2002 Colathur Vijayan ("VJN") o Created.
*
*/
PROCEDURE update_secured_entity (
p_init_msg_list IN VARCHAR2,
p_dss_secured_entity IN dss_secured_entity_type,
x_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_object_version_number NUMBER;
SAVEPOINT update_secured_entity ;
SELECT object_version_number, dss_instance_set_id, rowid, status
INTO l_object_version_number, l_dss_instance_set_id , l_rowid, l_status
FROM hz_dss_secured_entities
WHERE dss_group_code = p_dss_secured_entity.dss_group_code
AND entity_id = p_dss_secured_entity.entity_id
FOR UPDATE NOWAIT;
hz_dss_grants_pub.update_grant (
p_dss_group_code => p_dss_secured_entity.dss_group_code,
p_dss_instance_set_id => l_dss_instance_set_id,
p_secured_entity_status => p_dss_secured_entity.status,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
HZ_DSS_SECURED_ENTITIES_PKG.Update_Row (
x_rowid => l_rowid,
x_status => p_dss_secured_entity.status,
x_dss_instance_set_id => l_dss_instance_set_id ,
x_object_version_number => x_object_version_number
) ;
ROLLBACK TO update_secured_entity ;
ROLLBACK TO update_secured_entity ;
ROLLBACK TO update_secured_entity ;
END update_secured_entity ;