[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_create_update_flag IN VARCHAR2 := NULL,
p_entity_name IN VARCHAR2,
p_attribute_name IN VARCHAR2,
x_group_name OUT NOCOPY VARCHAR2,
x_group OUT NOCOPY VARCHARList,
x_group_id OUT NOCOPY INDEXIDList
);
PROCEDURE db_InsertEntityAttribute (
p_entity_attribute_rec IN ENTITY_ATTRIBUTE_REC_TYPE,
p_group_name IN VARCHAR2,
x_entity_attr_id OUT NOCOPY NUMBER
);
PROCEDURE db_InsertDataSource (
p_new_item_flag IN VARCHAR2,
p_entity_attr_id IN NUMBER,
p_data_source_tab IN DATA_SOURCE_TBL
);
SELECT entity_attr_id
FROM hz_entity_attributes
WHERE entity_name = p_entity_name
AND attribute_name = p_attribute_name;
l_create_update_flag VARCHAR2(1) := 'U';
SELECT entity_attr_id
FROM hz_entity_attributes
WHERE entity_name = p_entity_name
AND ((attribute_name IS NULL AND
(p_attribute_name IS NULL OR
p_attribute_name = FND_API.G_MISS_CHAR)) OR
(attribute_name = p_attribute_name));
l_create_update_flag := 'C';
p_create_update_flag => l_create_update_flag,
p_entity_name => p_entity_attribute_rec.entity_name,
p_attribute_name => p_entity_attribute_rec.attribute_name,
x_group_name => l_group_name,
x_group => l_group,
x_group_id => l_group_id);
IF l_create_update_flag = 'C' THEN
FOR i IN 1..l_total LOOP
l_entity_attribute_rec.attribute_name := l_group(i);
db_InsertEntityAttribute (
p_entity_attribute_rec => l_entity_attribute_rec,
p_group_name => l_group_name,
x_entity_attr_id => x_entity_attr_id);
db_InsertDataSource (
p_new_item_flag => l_new_item_flag,
p_entity_attr_id => x_entity_attr_id,
p_data_source_tab => p_data_source_tab);
db_InsertDataSource (
p_new_item_flag => l_new_item_flag,
p_entity_attr_id => l_entity_attr_id,
p_data_source_tab => p_data_source_tab);
* p_create_update_flag 'C' is for create.
* p_entity_name Entity name.
* p_attribute_name Attribute name.
* OUT:
* x_group_name Group name.
* x_group Attribute name list in a group.
* x_group_id Attribute id list in a group.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Jianying Huang o Created.
*
*/
PROCEDURE Find_NameListInAGroup (
p_create_update_flag IN VARCHAR2 := NULL,
p_entity_name IN VARCHAR2,
p_attribute_name IN VARCHAR2,
x_group_name OUT NOCOPY VARCHAR2,
x_group OUT NOCOPY VARCHARList,
x_group_id OUT NOCOPY INDEXIDList
) IS
BEGIN
IF p_entity_name = G_PERSON_ENTITY THEN
IF p_attribute_name = 'PERSON_NAME' OR
getIndex(G_PERSON_NAME_GROUP, p_attribute_name) > 0
THEN
x_group_name := 'PERSON_NAME';
IF p_create_update_flag = 'U' THEN
IF G_PERSON_NAME_GROUP.COUNT > G_PERSON_NAME_ID_GROUP.COUNT THEN
LoadGroupId(G_PERSON_ENTITY, x_group, G_PERSON_NAME_ID_GROUP);
IF p_create_update_flag = 'U' THEN
IF G_PERSON_IDENTIFIER_GROUP.COUNT > G_PERSON_IDENTIFIER_ID_GROUP.COUNT THEN
LoadGroupId(G_PERSON_ENTITY, x_group, G_PERSON_IDENTIFIER_ID_GROUP);
IF p_create_update_flag = 'U' THEN
IF G_HQ_BRANCH_IND_GROUP.COUNT > G_HQ_BRANCH_IND_ID_GROUP.COUNT THEN
LoadGroupId(G_ORG_ENTITY, x_group, G_HQ_BRANCH_IND_ID_GROUP);
IF p_create_update_flag = 'U' THEN
IF G_ORGANIZATION_NAME_GROUP.COUNT > G_ORGANIZATION_NAME_ID_GROUP.COUNT THEN
LoadGroupId(G_ORG_ENTITY, x_group, G_ORGANIZATION_NAME_ID_GROUP);
IF p_create_update_flag = 'U' THEN
IF G_LOCAL_ACTIVITY_CODE_GROUP.COUNT > G_LOCAL_ACTIVITY_CODE_ID_GROUP.COUNT THEN
LoadGroupId(G_ORG_ENTITY, x_group, G_LOCAL_ACTIVITY_CODE_ID_GROUP);
IF p_create_update_flag = 'U' THEN
IF G_LOCAL_BUS_IDEN_GROUP.COUNT > G_LOCAL_BUS_IDEN_ID_GROUP.COUNT THEN
LoadGroupId(G_ORG_ENTITY, x_group, G_LOCAL_BUS_IDEN_ID_GROUP);
IF p_create_update_flag = 'U' THEN
IF G_SIC_CODE_GROUP.COUNT > G_SIC_CODE_ID_GROUP.COUNT THEN
LoadGroupId(G_ORG_ENTITY, x_group, G_SIC_CODE_ID_GROUP);
IF p_create_update_flag = 'U' THEN
IF G_DUNS_NUMBER_GROUP.COUNT > G_DUNS_NUMBER_ID_GROUP.COUNT THEN
LoadGroupId(G_ORG_ENTITY, x_group, G_DUNS_NUMBER_ID_GROUP);
IF p_create_update_flag = 'U' THEN
IF G_CEO_GROUP.COUNT > G_CEO_ID_GROUP.COUNT THEN
LoadGroupId(G_ORG_ENTITY, x_group, G_CEO_ID_GROUP);
IF p_create_update_flag = 'U' THEN
IF G_PRINCIPAL_GROUP.COUNT > G_PRINCIPAL_ID_GROUP.COUNT THEN
LoadGroupId(G_ORG_ENTITY, x_group, G_PRINCIPAL_ID_GROUP);
IF p_create_update_flag = 'U' THEN
IF G_MINORITY_OWNED_GROUP.COUNT > G_MINORITY_OWNED_ID_GROUP.COUNT THEN
LoadGroupId(G_ORG_ENTITY, x_group, G_MINORITY_OWNED_ID_GROUP);
p_create_update_flag => 'C',
p_column => 'entity_name',
p_column_value => p_entity_attribute_rec.entity_name,
x_return_status => x_return_status );
p_create_update_flag => 'C',
p_column => 'attribute_name',
p_column_value => p_entity_attribute_rec.attribute_name,
x_return_status => x_return_status );
p_create_update_flag => 'C',
p_created_by_module => p_entity_attribute_rec.created_by_module,
p_old_created_by_module => null,
x_return_status => x_return_status);
SELECT '1'
FROM HZ_ORIG_SYSTEMS_B
WHERE orig_system = p_data_source_tab(i)
AND sst_flag = 'Y';
select aa.argument_name, aa.data_type, party.column_name
from sys.all_arguments aa, (
select min(a.sequence) id
from sys.all_arguments a
where a.object_name = 'GET_' ||upper (p_name)||'_REC'
and a.type_subname = upper (p_name) || '_REC_TYPE'
and a.data_level = 0
and a.object_id in (
select b.object_id
from sys.all_objects b
where b.object_name = 'HZ_PARTY_V2PUB'
and b.owner = p_apps_schema
and b.object_type = 'PACKAGE')) temp1, (
select column_name
from sys.all_tab_columns c
where c.table_name = 'HZ_PARTIES'
and c.owner = p_ar_schema
and exists (
select null
from sys.all_tab_columns c2
where c2.owner = p_ar_schema
and c2.column_name = c.column_name
and c2.table_name = 'HZ_' ||upper (p_name) || '_PROFILES')
and c.column_name not like 'ATTRIBUTE%'
and c.column_name not like 'GLOBAL_ATTRIBUTE%'
and c.column_name not in ('APPLICATION_ID')) party
where aa.object_name = 'GET_' ||upper (p_name)||'_REC'
and aa.data_level = 1
and aa.data_type <> 'PL/SQL RECORD'
and aa.argument_name not in ('CONTENT_SOURCE_TYPE',
'ACTUAL_CONTENT_SOURCE', 'APPLICATION_ID')
and aa.sequence > temp1.id
and aa.object_id in (
select b.object_id
from sys.all_objects b
where b.object_name = 'HZ_PARTY_V2PUB'
and b.owner = p_apps_schema
and b.object_type = 'PACKAGE')
and aa.argument_name = party.column_name (+)
order by argument_name;
* PRIVATE PROCEDURE db_InsertEntityAttribute
*
* DESCRIPTION
* Private procedure to insert entity / attribute into the table.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_entity_attribute_rec Entity Attribute record.
* OUT:
* x_entity_attr_id Dictionary ID.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 02-12-2002 Jianying Huang o Created.
* 11-24-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
* User Overwrite rule and Third Party Rule
* are orig_system specific. No default
* records will be created in these tables
* and records with overwrite_flag = 'N' will
* not be stored.
*
*/
PROCEDURE db_InsertEntityAttribute (
p_entity_attribute_rec IN ENTITY_ATTRIBUTE_REC_TYPE,
p_group_name IN VARCHAR2,
x_entity_attr_id OUT NOCOPY NUMBER
) IS
/*
CURSOR c_user_overwrite_rule IS
SELECT UNIQUE rule_id
FROM hz_user_overwrite_rules;
SELECT 'Y'
FROM hz_thirdparty_rule
WHERE ROWNUM = 1;
hz_utility_v2pub.debug(p_message=>'db_InsertEntityAttribute (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
INSERT INTO hz_entity_attributes (
entity_attr_id,
entity_name,
attribute_name,
attribute_group_name,
created_by_module,
application_id,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by
) VALUES (
--
-- entity_attr_id
hz_entity_attributes_s.NEXTVAL,
DECODE(p_entity_attribute_rec.entity_name,
FND_API.G_MISS_CHAR, NULL, p_entity_attribute_rec.entity_name),
DECODE(p_entity_attribute_rec.attribute_name,
FND_API.G_MISS_CHAR, NULL, p_entity_attribute_rec.attribute_name),
p_group_name,
DECODE(p_entity_attribute_rec.created_by_module,
FND_API.G_MISS_CHAR, NULL, p_entity_attribute_rec.created_by_module),
DECODE(p_entity_attribute_rec.application_id,
FND_API.G_MISS_NUM, NULL, p_entity_attribute_rec.application_id),
hz_utility_v2pub.created_by,
SYSDATE,
hz_utility_v2pub.last_update_login,
SYSDATE,
hz_utility_v2pub.last_updated_by )
RETURNING entity_attr_id INTO x_entity_attr_id;
INSERT INTO hz_user_overwrite_rules (
rule_id,
entity_attr_id,
overwrite_flag,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by
) VALUES (
i_rule_id(i),
x_entity_attr_id,
-- by default, user can overwrite third party data.
'Y',
hz_utility_v2pub.created_by,
SYSDATE,
hz_utility_v2pub.last_update_login,
SYSDATE,
hz_utility_v2pub.last_updated_by );
INSERT INTO hz_thirdparty_rule (
entity_attr_id,
overwrite_flag,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by
)
VALUES (
x_entity_attr_id,
-- by default, third party can not overwrite user data.
'N',
hz_utility_v2pub.created_by,
SYSDATE,
hz_utility_v2pub.last_update_login,
SYSDATE,
hz_utility_v2pub.last_updated_by
);
hz_utility_v2pub.debug(p_message=>'db_InsertEntityAttribute (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END db_InsertEntityAttribute;
* PRIVATE PROCEDURE db_InsertDataSource
*
* DESCRIPTION
* Private procedure to insert data source setup into the table.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_entity_attr_id Dictionary ID.
* p_data_source_tab PL/SQL table for data source setup.
* IN/OUT:
* OUT:
*
* NOTES
*
* MODIFICATION HISTORY
*
* 02-12-2002 Jianying Huang o Created.
* 12-12-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
* If p_new_item_flag = 'O'(i.e. other entity),
* set ranking to 1.
*
*/
PROCEDURE db_InsertDataSource (
p_new_item_flag IN VARCHAR2,
p_entity_attr_id IN NUMBER,
p_data_source_tab IN DATA_SOURCE_TBL
) IS
l_debug_prefix VARCHAR2(30) := '';
hz_utility_v2pub.debug(p_message=>'db_InsertDataSource (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
INSERT INTO hz_select_data_sources (
entity_attr_id,
content_source_type,
ranking,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by
)
SELECT
p_entity_attr_id,
p_data_source_tab(i),
--
-- ranking
DECODE(p_new_item_flag,
'Y', DECODE(p_data_source_tab(i), 'USER_ENTERED', 1, 0),
'O',1, -- For other entities.
0),
hz_utility_v2pub.created_by,
SYSDATE,
hz_utility_v2pub.last_update_login,
SYSDATE,
hz_utility_v2pub.last_updated_by
FROM dual
WHERE NOT EXISTS (
SELECT 'Y'
FROM hz_select_data_sources source2
WHERE source2.entity_attr_id = p_entity_attr_id
AND source2.content_source_type = p_data_source_tab(i));
hz_utility_v2pub.debug(p_message=>'db_InsertDataSource (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END db_InsertDataSource;
SELECT entity_attr_id
FROM hz_entity_attributes
WHERE entity_name = p_entity_name
AND ((attribute_name IS NULL AND
(p_attribute_name IS NULL OR
p_attribute_name = FND_API.G_MISS_CHAR)) OR
(attribute_name = p_attribute_name));
SELECT content_source_type
FROM hz_select_data_sources
WHERE entity_attr_id = p_entity_attr_id;
SELECT entity_attr_id
FROM hz_entity_attributes
WHERE entity_name = p_entity_name
AND ((attribute_name IS NULL AND
(p_attribute_name IS NULL OR
p_attribute_name = FND_API.G_MISS_CHAR)) OR
(attribute_name = p_attribute_name));
CURSOR c_selected_data_source (
p_entity_attr_id NUMBER
) IS
SELECT 'Y'
FROM hz_select_data_sources
WHERE entity_attr_id = p_entity_attr_id
AND ranking > 0
AND content_source_type <> 'USER_ENTERED'
AND ROWNUM = 1;
OPEN c_selected_data_source(l_entity_attr_id);
FETCH c_selected_data_source INTO l_dummy;
IF c_selected_data_source%NOTFOUND THEN
-- delete the data sources.
DELETE hz_select_data_sources
WHERE entity_attr_id = l_entity_attr_id;
DELETE hz_entity_attributes
WHERE entity_attr_id = l_entity_attr_id;
DELETE hz_user_overwrite_rules
WHERE entity_attr_id = l_entity_attr_id;
DELETE hz_thirdparty_rule
WHERE entity_attr_id = l_entity_attr_id;
fnd_message.set_name('AR', 'HZ_API_CANNOT_DELETE_ENTITY');
CLOSE c_selected_data_source;
* The data sources must be un-selected.
*
* 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_entity_name Entity Name
* p_attribute_name Attribute Name
* 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
*
* 02-12-2002 Jianying Huang o Created.
*/
PROCEDURE Remove_EntityAttrDataSource (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_entity_name IN VARCHAR2,
p_attribute_name IN VARCHAR2,
p_data_source_tbl IN DATA_SOURCE_TBL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_entity_attr_id NUMBER;
SELECT entity_attr_id
FROM hz_entity_attributes
WHERE entity_name = p_entity_name
AND ((attribute_name IS NULL AND
(p_attribute_name IS NULL OR
p_attribute_name = FND_API.G_MISS_CHAR)) OR
(attribute_name = p_attribute_name));
SELECT 'Y'
FROM hz_select_data_sources
WHERE entity_attr_id = p_entity_attr_id
AND content_source_type = p_data_source
AND ranking > 0;
DELETE hz_select_data_sources
WHERE entity_attr_id = l_entity_attr_id
AND content_source_type = p_data_source_tbl(i);
fnd_message.set_name('AR', 'HZ_CANNOT_DELETE_ENTITY_SOURCE');
DELETE hz_entity_attributes
WHERE entity_attr_id = l_entity_attr_id
AND NOT EXISTS (
SELECT 'Y'
FROM hz_select_data_sources
WHERE entity_attr_id = l_entity_attr_id)
RETURNING entity_attr_id BULK COLLECT INTO i_entity_attr_id;
DELETE hz_user_overwrite_rules
WHERE entity_attr_id = i_entity_attr_id(i);
DELETE hz_thirdparty_rule
WHERE entity_attr_id = i_entity_attr_id(i);