[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE do_update_class_category(
p_class_cat_rec IN OUT NOCOPY CLASS_CATEGORY_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE do_update_class_code_relation(
p_class_code_rel_rec IN OUT NOCOPY CLASS_CODE_RELATION_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE do_update_code_assignment(
p_code_assignment_rec IN OUT NOCOPY CODE_ASSIGNMENT_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE do_update_class_category_use(
p_class_category_use_rec IN OUT NOCOPY CLASS_CATEGORY_USE_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
);
SELECT 'Y'
FROM hz_code_assignments
WHERE owner_table_name = 'HZ_CLASS_CATEGORIES'
AND class_category = 'CLASS_CATEGORY_GROUP'
AND class_code = 'INDUSTRIAL_GROUP'
AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE+1)
AND NVL(status,'A') = 'A'
AND owner_table_key_1 = l_class_category;
update hz_class_categories
set frozen_flag = 'N'
where class_category = p_class_category
and (frozen_flag = 'Y' or frozen_flag is null);
HZ_CLASS_CATEGORIES_PKG.Insert_Row (
X_CLASS_CATEGORY => p_class_cat_rec.class_category,
X_ALLOW_MULTI_PARENT_FLAG => p_class_cat_rec.allow_multi_parent_flag,
X_ALLOW_MULTI_ASSIGN_FLAG => p_class_cat_rec.allow_multi_assign_flag,
X_ALLOW_LEAF_NODE_ONLY_FLAG => p_class_cat_rec.allow_leaf_node_only_flag,
X_OBJECT_VERSION_NUMBER => 1,
X_CREATED_BY_MODULE => p_class_cat_rec.created_by_module,
X_APPLICATION_ID => p_class_cat_rec.application_id,
X_DELIMITER => p_class_cat_rec.delimiter
);
| do_update_class_category
|
| DESCRIPTION
| Updates class category
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/ OUT:
| p_class_cat_rec
| p_last_update_date
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
+===========================================================================*/
PROCEDURE do_update_class_category(
p_class_cat_rec IN OUT NOCOPY CLASS_CATEGORY_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_object_version_number NUMBER;
SELECT OBJECT_VERSION_NUMBER,
ROWID, allow_leaf_node_only_flag,delimiter,allow_multi_parent_flag
INTO l_object_version_number,
l_rowid,l_allow_leaf_node_only_flag,l_delimiter,l_allow_multi_parent_flag
FROM HZ_CLASS_CATEGORIES
WHERE CLASS_CATEGORY = p_class_cat_rec.class_category
FOR UPDATE OF CLASS_CATEGORY NOWAIT;
HZ_CLASS_CATEGORIES_PKG.Update_Row (
X_CLASS_CATEGORY => p_class_cat_rec.class_category,
X_ALLOW_MULTI_PARENT_FLAG => p_class_cat_rec.allow_multi_parent_flag,
X_ALLOW_MULTI_ASSIGN_FLAG => p_class_cat_rec.allow_multi_assign_flag,
X_ALLOW_LEAF_NODE_ONLY_FLAG => p_class_cat_rec.allow_leaf_node_only_flag,
X_OBJECT_VERSION_NUMBER => p_object_version_number,
X_CREATED_BY_MODULE => p_class_cat_rec.created_by_module,
X_APPLICATION_ID => p_class_cat_rec.application_id,
X_DELIMITER => p_class_cat_rec.delimiter
);
HZ_CLASS_CODE_RELATIONS_PKG.Insert_Row (
X_CLASS_CATEGORY => p_class_code_rel_rec.class_category,
X_CLASS_CODE => p_class_code_rel_rec.class_code,
X_SUB_CLASS_CODE => p_class_code_rel_rec.sub_class_code,
X_START_DATE_ACTIVE => p_class_code_rel_rec.start_date_active,
X_END_DATE_ACTIVE => p_class_code_rel_rec.end_date_active,
X_OBJECT_VERSION_NUMBER => 1,
X_CREATED_BY_MODULE => p_class_code_rel_rec.created_by_module,
X_APPLICATION_ID => p_class_code_rel_rec.application_id
);
| do_update_class_code_relation
|
| DESCRIPTION
| Updates class code relation
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/ OUT:
| p_class_code_rel_rec
| p_last_update_date
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
+===========================================================================*/
PROCEDURE do_update_class_code_relation(
p_class_code_rel_rec IN OUT NOCOPY CLASS_CODE_RELATION_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_object_version_number NUMBER;
SELECT OBJECT_VERSION_NUMBER,
ROWID,END_DATE_ACTIVE
INTO l_object_version_number,
l_rowid,l_end_date_active
FROM HZ_CLASS_CODE_RELATIONS
WHERE CLASS_CATEGORY = p_class_code_rel_rec.class_category
AND CLASS_CODE = p_class_code_rel_rec.class_code
AND SUB_CLASS_CODE = p_class_code_rel_rec.sub_class_code
AND START_DATE_ACTIVE = p_class_code_rel_rec.START_DATE_ACTIVE
FOR UPDATE OF CLASS_CATEGORY, CLASS_CODE, SUB_CLASS_CODE NOWAIT;
HZ_CLASS_CODE_RELATIONS_PKG.Update_Row (
X_CLASS_CATEGORY => p_class_code_rel_rec.class_category,
X_CLASS_CODE => p_class_code_rel_rec.class_code,
X_SUB_CLASS_CODE => p_class_code_rel_rec.sub_class_code,
X_START_DATE_ACTIVE => p_class_code_rel_rec.start_date_active,
X_END_DATE_ACTIVE => p_class_code_rel_rec.end_date_active,
X_OBJECT_VERSION_NUMBER => p_object_version_number,
X_CREATED_BY_MODULE => p_class_code_rel_rec.created_by_module,
X_APPLICATION_ID => p_class_code_rel_rec.application_id
);
SELECT party_type,object_version_number,
sic_code_type,sic_code /* Bug 4156312 */
FROM HZ_PARTIES
WHERE party_id=c_party_id;
SELECT lookup_code
INTO p_code_assignment_rec.class_code
FROM fnd_lookup_values
WHERE replace(lookup_code, '.', '') = replace(p_code_assignment_rec.class_code, '.', '')
AND lookup_type='NACE'
AND rownum = 1;
HZ_CODE_ASSIGNMENTS_PKG.Insert_Row (
X_CODE_ASSIGNMENT_ID => p_code_assignment_rec.code_assignment_id,
X_OWNER_TABLE_NAME => p_code_assignment_rec.owner_table_name,
X_OWNER_TABLE_ID => p_code_assignment_rec.owner_table_id,
X_OWNER_TABLE_KEY_1 => p_code_assignment_rec.owner_table_key_1,
X_OWNER_TABLE_KEY_2 => p_code_assignment_rec.owner_table_key_2,
X_OWNER_TABLE_KEY_3 => p_code_assignment_rec.owner_table_key_3,
X_OWNER_TABLE_KEY_4 => p_code_assignment_rec.owner_table_key_4,
X_OWNER_TABLE_KEY_5 => p_code_assignment_rec.owner_table_key_5,
X_CLASS_CATEGORY => p_code_assignment_rec.class_category,
X_CLASS_CODE => p_code_assignment_rec.class_code,
X_PRIMARY_FLAG => p_code_assignment_rec.primary_flag,
X_CONTENT_SOURCE_TYPE => p_code_assignment_rec.content_source_type,
X_START_DATE_ACTIVE => p_code_assignment_rec.start_date_active,
X_END_DATE_ACTIVE => p_code_assignment_rec.end_date_active,
X_STATUS => p_code_assignment_rec.status,
X_OBJECT_VERSION_NUMBER => 1,
X_CREATED_BY_MODULE => p_code_assignment_rec.created_by_module,
X_RANK => p_code_assignment_rec.rank,
X_APPLICATION_ID => p_code_assignment_rec.application_id,
-- SSM SST Integration and Extension
X_ACTUAL_CONTENT_SOURCE => p_code_assignment_rec.actual_content_source
);
p_operation_code => 'INSERT',
p_db_object_name => 'HZ_CODE_ASSIGNMENTS',
p_instance_pk1_value => p_code_assignment_rec.code_assignment_id,
p_user_name => fnd_global.user_name,
x_return_status => dss_return_status,
x_msg_count => dss_msg_count,
x_msg_data => dss_msg_data);
FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_INSERT_PRIVILEGE');
HZ_PARTY_V2PUB.update_organization(
'T',
l_organization_rec,
l_object_version_number,
x_profile_id,
x_return_status,
dss_msg_count,
dss_msg_data);
update hz_parties
set category_code = p_code_assignment_rec.class_code,
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login
where party_id = p_code_assignment_rec.owner_table_id;
| do_update_code_assignment
|
| DESCRIPTION
| Updates code assignment
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/ OUT:
| p_code_assignment_rec
| p_last_update_date
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
| 20-Nov-2003 Ramesh Ch Bug No: 3216842. Denormalized SIC_CODE and SIC_CODE_TYPE columns into
| HZ_PARTIES and HZ_ORGANIZATION_PROFILES tables for ORGANIZATION party
| primary code assignments of 1972 SIC,1977 SIC,1987 SIC and NAICS_1997
| class category type.
| 05-Jan-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
| Passed the value of actual_content_source to
| table_handler.
+===========================================================================*/
PROCEDURE do_update_code_assignment(
p_code_assignment_rec IN OUT NOCOPY CODE_ASSIGNMENT_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_object_version_number NUMBER;
SELECT owner_table_id,owner_table_name,class_category,
class_code,primary_flag, start_date_active, end_date_active
FROM hz_code_assignments
WHERE code_assignment_id = c_code_assignment_id;
SELECT party_type,object_version_number,
sic_code_type,sic_code /* Bug 4156312 */
FROM HZ_PARTIES
WHERE party_id=c_party_id;
SELECT class_category,class_code
from (
select code_assignment_id,class_category,class_code
from hz_code_assignments a
--where class_category in ('1972 SIC','1977 SIC','1987 SIC','NAICS_1997') -- Bug 6059383
WHERE owner_table_id = p_party_id
and sysdate between start_date_active and nvl(end_date_active,sysdate+1)
and primary_flag='Y'
and code_assignment_id < p_code_id
-- Added for Bug 6059383 (remove hard coding for industrial classification)
AND EXISTS (SELECT NULL FROM hz_code_assignments b
WHERE a.class_category = b.owner_table_key_1
AND b.owner_table_name = 'HZ_CLASS_CATEGORIES'
AND b.class_category = 'CLASS_CATEGORY_GROUP'
AND b.class_code = 'INDUSTRIAL_GROUP'
AND SYSDATE BETWEEN b.start_date_active AND NVL(b.end_date_active,SYSDATE+1)
AND NVL(b.status,'A') = 'A'
)
order by code_assignment_id desc
)
where rownum=1;
SELECT OBJECT_VERSION_NUMBER,owner_table_id, owner_table_name,
class_category, class_code,primary_flag, start_date_active,
end_date_active,ROWID,actual_content_source
INTO l_object_version_number,l_owner_table_id,l_owner_table_name,
l_class_category,l_class_code,l_primary_flag, l_start_date,
l_end_date,l_rowid,l_actual_content_src
FROM HZ_CODE_ASSIGNMENTS
WHERE CODE_ASSIGNMENT_ID = p_code_assignment_rec.code_assignment_id
FOR UPDATE OF CODE_ASSIGNMENT_ID NOWAIT;
SELECT lookup_code
INTO p_code_assignment_rec.class_code
FROM fnd_lookup_values
WHERE replace(lookup_code, '.', '') = replace(nvl(p_code_assignment_rec.class_code,l_class_code), '.', '')
AND lookup_type='NACE'
AND rownum = 1;
IF HZ_UTILITY_V2PUB.G_UPDATE_ACS = 'Y' THEN
l_acs := nvl(p_code_assignment_rec.actual_content_source, 'USER_ENTERED');
HZ_CODE_ASSIGNMENTS_PKG.Update_Row (
X_Rowid => l_rowid,
X_CODE_ASSIGNMENT_ID => p_code_assignment_rec.code_assignment_id,
X_OWNER_TABLE_NAME => p_code_assignment_rec.owner_table_name,
X_OWNER_TABLE_ID => p_code_assignment_rec.owner_table_id,
X_OWNER_TABLE_KEY_1 => p_code_assignment_rec.owner_table_key_1,
X_OWNER_TABLE_KEY_2 => p_code_assignment_rec.owner_table_key_2,
X_OWNER_TABLE_KEY_3 => p_code_assignment_rec.owner_table_key_3,
X_OWNER_TABLE_KEY_4 => p_code_assignment_rec.owner_table_key_4,
X_OWNER_TABLE_KEY_5 => p_code_assignment_rec.owner_table_key_5,
X_CLASS_CATEGORY => p_code_assignment_rec.class_category,
X_CLASS_CODE => p_code_assignment_rec.class_code,
X_PRIMARY_FLAG => p_code_assignment_rec.primary_flag,
X_CONTENT_SOURCE_TYPE => p_code_assignment_rec.content_source_type,
X_START_DATE_ACTIVE => p_code_assignment_rec.start_date_active,
X_END_DATE_ACTIVE => p_code_assignment_rec.end_date_active,
X_STATUS => p_code_assignment_rec.status,
X_OBJECT_VERSION_NUMBER => p_object_version_number,
X_CREATED_BY_MODULE => p_code_assignment_rec.created_by_module,
X_RANK => p_code_assignment_rec.rank,
X_APPLICATION_ID => p_code_assignment_rec.application_id,
-- SSM SST Integration and Extension
-- Bug 4693719 : Pass correct value for ACS
X_ACTUAL_CONTENT_SOURCE => l_acs
);
HZ_PARTY_V2PUB.update_organization(
'T',
l_organization_rec,
l_object_version_number,
x_profile_id,
x_return_status,
dss_msg_count,
dss_msg_data);
HZ_PARTY_V2PUB.update_organization(
'T',
l_organization_rec,
l_object_version_number,
x_profile_id,
x_return_status,
dss_msg_count,
dss_msg_data);
UPDATE HZ_PARTIES
SET CATEGORY_CODE = NULL,
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login
WHERE PARTY_ID = nvl(p_code_assignment_rec.owner_table_id,l_owner_table_id)
AND CATEGORY_CODE = nvl(p_code_assignment_rec.class_code,l_class_code);
update hz_parties
set category_code = nvl(p_code_assignment_rec.class_code,l_class_code),
last_update_date = hz_utility_v2pub.last_update_date,
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login
where party_id = l_owner_table_id;
| p_last_update_date
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
+===========================================================================*/
PROCEDURE do_create_class_category_use(
p_class_category_use_rec IN OUT NOCOPY CLASS_CATEGORY_USE_REC_TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_rowid ROWID := NULL;
HZ_CLASS_CATEGORY_USES_PKG.Insert_Row (
X_CLASS_CATEGORY => p_class_category_use_rec.class_category,
X_OWNER_TABLE => p_class_category_use_rec.owner_table,
X_COLUMN_NAME => p_class_category_use_rec.column_name,
X_ADDITIONAL_WHERE_CLAUSE => p_class_category_use_rec.additional_where_clause,
X_OBJECT_VERSION_NUMBER => 1,
X_CREATED_BY_MODULE => p_class_category_use_rec.created_by_module,
X_APPLICATION_ID => p_class_category_use_rec.application_id
);
| do_update_class_category_use
|
| DESCRIPTION
| Updates class category use
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| OUT:
| IN/ OUT:
| p_class_category_use_rec
| p_last_update_date
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
+===========================================================================*/
PROCEDURE do_update_class_category_use(
p_class_category_use_rec IN OUT NOCOPY CLASS_CATEGORY_USE_REC_TYPE,
p_object_version_number IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2)
IS
l_object_version_number NUMBER;
SELECT OBJECT_VERSION_NUMBER,
ROWID
INTO l_object_version_number,
l_rowid
FROM HZ_CLASS_CATEGORY_USES
WHERE CLASS_CATEGORY = p_class_category_use_rec.class_category
AND OWNER_TABLE = p_class_category_use_rec.owner_table
FOR UPDATE OF CLASS_CATEGORY, OWNER_TABLE, COLUMN_NAME, ADDITIONAL_WHERE_CLAUSE NOWAIT;
HZ_CLASS_CATEGORY_USES_PKG.Update_Row (
X_CLASS_CATEGORY => p_class_category_use_rec.class_category,
X_OWNER_TABLE => p_class_category_use_rec.owner_table,
X_COLUMN_NAME => p_class_category_use_rec.column_name,
X_ADDITIONAL_WHERE_CLAUSE => p_class_category_use_rec.additional_where_clause,
X_OBJECT_VERSION_NUMBER => p_object_version_number,
X_CREATED_BY_MODULE => p_class_category_use_rec.created_by_module,
X_APPLICATION_ID => p_class_category_use_rec.application_id
);
END do_update_class_category_use;
* PROCEDURE update_class_category
*
* DESCRIPTION
* Updates class category.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
* HZ_BUSINESS_EVENT_V2PVT.update_class_category_event
*
* 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_class_category_rec Class category record.
* IN/OUT:
* p_object_version_number Used for locking the being updated record.
* 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
*
* 07-23-2001 Indrajit Sen o Created.
*
*/
PROCEDURE update_class_category (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_class_category_rec IN CLASS_CATEGORY_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
l_class_cat_rec CLASS_CATEGORY_REC_TYPE := p_class_category_rec;
SAVEPOINT update_class_category;
do_update_class_category(
l_class_cat_rec,
p_object_version_number,
x_return_status);
HZ_BUSINESS_EVENT_V2PVT.update_class_category_event (
l_class_cat_rec,
l_old_class_cat_rec );
ROLLBACK TO update_class_category;
ROLLBACK TO update_class_category;
ROLLBACK TO update_class_category;
END update_class_category;
* PROCEDURE update_class_code_relation
*
* DESCRIPTION
* Updates class code relation.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
* HZ_BUSINESS_EVENT_V2PVT.update_class_code_rel_event
*
* 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_class_code_relation_rec Class code relation record.
* IN/OUT:
* p_object_version_number Used for locking the being updated record.
* 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
*
* 07-23-2001 Indrajit Sen o Created.
*
*/
PROCEDURE update_class_code_relation(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_class_code_relation_rec IN CLASS_CODE_RELATION_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
l_class_code_rel_rec CLASS_CODE_RELATION_REC_TYPE:= p_class_code_relation_rec;
SAVEPOINT update_class_code_relation;
do_update_class_code_relation(
l_class_code_rel_rec,
p_object_version_number,
x_return_status);
HZ_BUSINESS_EVENT_V2PVT.update_class_code_rel_event (
l_class_code_rel_rec,
l_old_class_code_rel_rec );
ROLLBACK TO update_class_code_relation;
ROLLBACK TO update_class_code_relation;
ROLLBACK TO update_class_code_relation;
END update_class_code_relation;
l_is_datasource_selected VARCHAR2(1);
p_selected_datasources => NULL,
p_content_source_type => l_code_assignment_rec.content_source_type,
p_actual_content_source => l_code_assignment_rec.actual_content_source,
x_is_datasource_selected => l_is_datasource_selected,
x_return_status => x_return_status,
p_api_version => 'V2'
);
* PROCEDURE update_code_assignment
*
* DESCRIPTION
* Updates code assignment.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
* HZ_BUSINESS_EVENT_V2PVT.update_code_assignment_event
*
* 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_code_assignment_rec Code assignment record.
* IN/OUT:
* p_object_version_number Used for locking the being updated record.
* 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
*
* 07-23-2001 Indrajit Sen o Created.
* 29-MAY-2003 Jyoti Pandey o Make the API protected by Data security Bug 2963010
*/
PROCEDURE update_code_assignment (
p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
p_code_assignment_rec IN CODE_ASSIGNMENT_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
l_code_assignment_rec CODE_ASSIGNMENT_REC_TYPE := p_code_assignment_rec;
SAVEPOINT update_code_assignment;
p_operation_code => 'UPDATE',
p_db_object_name => 'HZ_CODE_ASSIGNMENTS',
p_instance_pk1_value => l_code_assignment_rec.code_assignment_id,
p_user_name => fnd_global.user_name,
x_return_status => dss_return_status,
x_msg_count => dss_msg_count,
x_msg_data => dss_msg_data);
FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_UPDATE_PRIVILEGE');
do_update_code_assignment(
l_code_assignment_rec,
p_object_version_number,
x_return_status);
HZ_BUSINESS_EVENT_V2PVT.update_code_assignment_event (
l_code_assignment_rec,
l_old_code_assignment_rec );
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
ROLLBACK TO update_code_assignment;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
ROLLBACK TO update_code_assignment;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
ROLLBACK TO update_code_assignment;
HZ_UTILITY_V2PUB.G_UPDATE_ACS := NULL;
END update_code_assignment;
IS SELECT * FROM HZ_CODE_ASSIGNMENTS
WHERE
owner_table_name = p_owner_table_name AND
owner_table_id = p_owner_table_id AND
class_category = p_class_category AND
actual_content_source = NVL(p_content_source_type, HZ_PARTY_V2PUB.G_MISS_CONTENT_SOURCE_TYPE) AND
(end_date_active is null
OR sysdate between start_date_active and end_date_active);
UPDATE HZ_CODE_ASSIGNMENTS SET
--end_date_active = l_rec.start_date_active
-- Bug 3614582 : end date with sysdate - (1 second)
end_date_active = SYSDATE - 1/(24*60*60)
WHERE code_assignment_id = l_rec.code_assignment_id;
UPDATE HZ_CODE_ASSIGNMENTS SET
--end_date_active = l_rec.start_date_active
-- Bug 3614582 : end date with sysdate - (1 second)
end_date_active = SYSDATE - 1/(24*60*60)
WHERE code_assignment_id = l_rec.code_assignment_id;
* PROCEDURE update_class_category_use
*
* DESCRIPTION
* Updates class category use.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
* HZ_BUSINESS_EVENT_V2PVT.update_class_cat_use_event
*
* 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_class_category_use_rec Class category use record.
* IN/OUT:
* p_object_version_number Used for locking the being updated record.
* 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
*
* 07-23-2001 Indrajit Sen o Created.
*
*/
PROCEDURE update_class_category_use(
p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
p_class_category_use_rec IN CLASS_CATEGORY_USE_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
l_class_cat_use_rec CLASS_CATEGORY_USE_REC_TYPE := p_class_category_use_rec;
SAVEPOINT update_class_category_use;
do_update_class_category_use(
l_class_cat_use_rec,
p_object_version_number,
x_return_status);
HZ_BUSINESS_EVENT_V2PVT.update_class_cat_use_event (
l_class_cat_use_rec,
l_old_class_cat_use_rec );
ROLLBACK TO update_class_category_use;
ROLLBACK TO update_class_category_use;
ROLLBACK TO update_class_category_use;
END update_class_category_use;
SELECT
class_category,
allow_multi_assign_flag,
allow_multi_parent_flag,
allow_leaf_node_only_flag
INTO
x_class_cat_rec.class_category,
x_class_cat_rec.allow_multi_assign_flag,
x_class_cat_rec.allow_multi_parent_flag,
x_class_cat_rec.allow_leaf_node_only_flag
FROM hz_class_categories
WHERE class_category = p_class_category;
SELECT
class_category,
class_code,
sub_class_code,
start_date_active,
end_date_active
INTO
x_class_code_rel_rec.class_category,
x_class_code_rel_rec.class_code,
x_class_code_rel_rec.sub_class_code,
x_class_code_rel_rec.start_date_active,
x_class_code_rel_rec.end_date_active
FROM hz_class_code_relations
WHERE
class_category = p_class_code_rel_rec.class_category AND
class_code = p_class_code_rel_rec.class_code AND
sub_class_code = p_class_code_rel_rec.sub_class_code AND
start_date_active = p_class_code_rel_rec.start_date_active;
SELECT class_category,
owner_table,
additional_where_clause
INTO x_class_category_use_rec.class_category,
x_class_category_use_rec.owner_table,
x_class_category_use_rec.additional_where_clause
FROM hz_class_category_uses
WHERE class_category = p_class_category_use_rec.class_category
AND owner_table = p_class_category_use_rec.owner_table;
SELECT
code_assignment_id,
owner_table_name,
owner_table_id,
class_category,
class_code,
primary_flag,
content_source_type,
start_date_active,
end_date_active,
rank
INTO
x_code_assignment_rec.code_assignment_id ,
x_code_assignment_rec.owner_table_name,
x_code_assignment_rec.owner_table_id,
x_code_assignment_rec.class_category,
x_code_assignment_rec.class_code,
x_code_assignment_rec.primary_flag,
x_code_assignment_rec.content_source_type,
x_code_assignment_rec.start_date_active,
x_code_assignment_rec.end_date_active,
x_code_assignment_rec.rank
FROM hz_code_assignments
WHERE code_assignment_id = p_code_assignment_id;
* HZ_CLASS_CATEGORIES_PKG.Select_Row
*
* 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_class_category Class category name.
* IN/OUT:
* OUT:
* x_class_category_rec Returned class category record.
* 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
*
* 07-23-2001 Indrajit Sen o Created.
*
*/
PROCEDURE get_class_category_rec (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_class_category IN VARCHAR2,
x_class_category_rec OUT NOCOPY CLASS_CATEGORY_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
--Initialize message list if p_init_msg_list is set to TRUE.
IF FND_API.to_Boolean(p_init_msg_list) THEN
FND_MSG_PUB.initialize;
HZ_CLASS_CATEGORIES_PKG.Select_Row (
X_CLASS_CATEGORY => x_class_category_rec.class_category,
X_ALLOW_MULTI_PARENT_FLAG => x_class_category_rec.allow_multi_parent_flag,
X_ALLOW_MULTI_ASSIGN_FLAG => x_class_category_rec.allow_multi_assign_flag,
X_ALLOW_LEAF_NODE_ONLY_FLAG => x_class_category_rec.allow_leaf_node_only_flag,
X_CREATED_BY_MODULE => x_class_category_rec.created_by_module,
X_APPLICATION_ID => x_class_category_rec.application_id,
X_DELIMITER => x_class_category_rec.delimiter
);
* HZ_CLASS_CATEGORY_USES_PKG.Select_Row
*
* 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_class_category Class category name.
* p_owner_table Owner table name.
* IN/OUT:
* OUT:
* x_class_category_use_rec Returned class category use record.
* 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
*
* 07-23-2001 Indrajit Sen o Created.
*
*/
PROCEDURE get_class_category_use_rec(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_class_category IN VARCHAR2,
p_owner_table IN VARCHAR2,
x_class_category_use_rec OUT NOCOPY CLASS_CATEGORY_USE_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
--Initialize message list if p_init_msg_list is set to TRUE.
IF FND_API.to_Boolean(p_init_msg_list) THEN
FND_MSG_PUB.initialize;
HZ_CLASS_CATEGORY_USES_PKG.Select_Row (
X_CLASS_CATEGORY => x_class_category_use_rec.class_category,
X_OWNER_TABLE => x_class_category_use_rec.owner_table,
X_COLUMN_NAME => x_class_category_use_rec.column_name,
X_ADDITIONAL_WHERE_CLAUSE => x_class_category_use_rec.additional_where_clause,
X_CREATED_BY_MODULE => x_class_category_use_rec.created_by_module,
X_APPLICATION_ID => x_class_category_use_rec.application_id
);
* HZ_CLASS_CODE_RELATIONS_PKG.Select_Row
*
* 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_class_category Class category name.
* p_class_code Class code.
* p_sub_class_code Sub class code.
* p_start_date_active Start date active.
* IN/OUT:
* OUT:
* x_class_code_relation_rec Returned class code relation record.
* 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
*
* 07-23-2001 Indrajit Sen o Created.
*
*/
PROCEDURE get_class_code_relation_rec(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_class_category IN VARCHAR2,
p_class_code IN VARCHAR2,
p_sub_class_code IN VARCHAR2,
p_start_date_active IN DATE,
x_class_code_relation_rec OUT NOCOPY CLASS_CODE_RELATION_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
--Initialize message list if p_init_msg_list is set to TRUE.
IF FND_API.to_Boolean(p_init_msg_list) THEN
FND_MSG_PUB.initialize;
HZ_CLASS_CODE_RELATIONS_PKG.Select_Row (
X_CLASS_CATEGORY => x_class_code_relation_rec.class_category,
X_CLASS_CODE => x_class_code_relation_rec.class_code,
X_SUB_CLASS_CODE => x_class_code_relation_rec.sub_class_code,
X_START_DATE_ACTIVE => x_class_code_relation_rec.start_date_active,
X_END_DATE_ACTIVE => x_class_code_relation_rec.end_date_active,
X_CREATED_BY_MODULE => x_class_code_relation_rec.created_by_module,
X_APPLICATION_ID => x_class_code_relation_rec.application_id
);
* HZ_CODE_ASSIGNMENTS_PKG.Select_Row
*
* 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_code_assignment_id Code assignment ID.
* IN/OUT:
* OUT:
* x_code_assignment_rec Returned code assignment record.
* 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
*
* 07-23-2001 Indrajit Sen o Created.
* 01-05-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
* Added actual_content_source in call to select_row
*
*
*/
PROCEDURE get_code_assignment_rec (
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_code_assignment_id IN NUMBER,
x_code_assignment_rec OUT NOCOPY CODE_ASSIGNMENT_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
--Initialize message list if p_init_msg_list is set to TRUE.
IF FND_API.to_Boolean(p_init_msg_list) THEN
FND_MSG_PUB.initialize;
HZ_CODE_ASSIGNMENTS_PKG.Select_Row (
X_CODE_ASSIGNMENT_ID => x_code_assignment_rec.code_assignment_id,
X_OWNER_TABLE_NAME => x_code_assignment_rec.owner_table_name,
X_OWNER_TABLE_ID => x_code_assignment_rec.owner_table_id,
X_OWNER_TABLE_KEY_1 => x_code_assignment_rec.owner_table_key_1,
X_OWNER_TABLE_KEY_2 => x_code_assignment_rec.owner_table_key_2,
X_OWNER_TABLE_KEY_3 => x_code_assignment_rec.owner_table_key_3,
X_OWNER_TABLE_KEY_4 => x_code_assignment_rec.owner_table_key_4,
X_OWNER_TABLE_KEY_5 => x_code_assignment_rec.owner_table_key_5,
X_CLASS_CATEGORY => x_code_assignment_rec.class_category,
X_CLASS_CODE => x_code_assignment_rec.class_code,
X_PRIMARY_FLAG => x_code_assignment_rec.primary_flag,
X_CONTENT_SOURCE_TYPE => x_code_assignment_rec.content_source_type,
X_START_DATE_ACTIVE => x_code_assignment_rec.start_date_active,
X_END_DATE_ACTIVE => x_code_assignment_rec.end_date_active,
X_STATUS => x_code_assignment_rec.status,
X_CREATED_BY_MODULE => x_code_assignment_rec.created_by_module,
X_RANK => X_code_assignment_rec.rank,
X_APPLICATION_ID => x_code_assignment_rec.application_id,
-- SSM SST Integration and Extension
X_ACTUAL_CONTENT_SOURCE => x_code_assignment_rec.actual_content_source
);
SELECT h.owner_table,
upper(trim(h.additional_where_clause)),
f.pk1_column_name,
f.pk2_column_name,
f.pk3_column_name,
f.pk4_column_name,
f.pk5_column_name
FROM hz_class_category_uses h,
fnd_objects f
WHERE upper(f.database_object_name) = upper(h.owner_table)
AND class_category = p_class_category
AND owner_table = p_owner_table;
l_sql := 'SELECT ''T'' ' ||
' FROM ' || p_owner_table ||
' WHERE ' || l_additional_where_clause ||
' AND ' || l_pk1_column_name || '=:1 ' ||
' AND ' || l_pk2_column_name || '=:2 ' ||
' AND ' || l_pk3_column_name || '=:3 ' ||
' AND ' || l_pk4_column_name || '=:4 ' ||
' AND ' || l_pk5_column_name || '=:5 ' ||
' AND ROWNUM = 1';
l_sql := 'SELECT ''T'' ' ||
' FROM ' || p_owner_table ||
' WHERE ' || l_pk1_column_name || '=:1 ' ||
' AND ' || l_pk2_column_name || '=:2 ' ||
' AND ' || l_pk3_column_name || '=:3 ' ||
' AND ' || l_pk4_column_name || '=:4 ' ||
' AND ' || l_pk5_column_name || '=:5 ' ||
' AND ROWNUM = 1';
l_sql := 'SELECT ''T'' ' ||
' FROM ' || p_owner_table ||
' WHERE ' || l_additional_where_clause ||
' AND ' || l_pk1_column_name || '=:1 ' ||
' AND ' || l_pk2_column_name || '=:2 ' ||
' AND ' || l_pk3_column_name || '=:3 ' ||
' AND ' || l_pk4_column_name || '=:4 ' ||
' AND ROWNUM = 1';
l_sql := 'SELECT ''T'' ' ||
' FROM ' || p_owner_table ||
' WHERE ' || l_pk1_column_name || '=:1 ' ||
' AND ' || l_pk2_column_name || '=:2 ' ||
' AND ' || l_pk3_column_name || '=:3 ' ||
' AND ' || l_pk4_column_name || '=:4 ' ||
' AND ROWNUM = 1';
l_sql := 'SELECT ''T'' ' ||
' FROM ' || p_owner_table ||
' WHERE ' || l_additional_where_clause ||
' AND ' || l_pk1_column_name || '=:1 ' ||
' AND ' || l_pk2_column_name || '=:2 ' ||
' AND ' || l_pk3_column_name || '=:3 ' ||
' AND ROWNUM = 1';
l_sql := 'SELECT ''T'' ' ||
' FROM ' || p_owner_table ||
' WHERE ' || l_pk1_column_name || '=:1 ' ||
' AND ' || l_pk2_column_name || '=:2 ' ||
' AND ' || l_pk3_column_name || '=:3 ' ||
' AND ROWNUM = 1';
l_sql := 'SELECT ''T'' ' ||
' FROM ' || p_owner_table ||
' WHERE ' || l_additional_where_clause ||
' AND ' || l_pk1_column_name || '=:1 ' ||
' AND ' || l_pk2_column_name || '=:2 ' ||
' AND ROWNUM = 1';
l_sql := 'SELECT ''T'' ' ||
' FROM ' || p_owner_table ||
' WHERE ' || l_pk1_column_name || '=:1 ' ||
' AND ' || l_pk2_column_name || '=:2 ' ||
' AND ROWNUM = 1';
l_sql := 'SELECT ''T'' ' ||
' FROM ' || p_owner_table ||
' WHERE ' || l_additional_where_clause ||
' AND ' || l_pk1_column_name || '=:1 ' ||
' AND ROWNUM = 1';
l_sql := 'SELECT ''T'' ' ||
' FROM ' || p_owner_table ||
' WHERE ' || l_pk1_column_name || '=:1 ' ||
' AND ROWNUM = 1';
SELECT upper(trim(additional_where_clause)), column_name
FROM hz_class_category_uses
WHERE class_category = p_class_category
AND owner_table = p_owner_table;
l_sql := 'SELECT ''T'' ' ||
' FROM '|| p_owner_table ||
' WHERE '||l_column_name||' = :1 and '||
l_additional_where_clause||' and rownum = 1';
l_sql := 'SELECT ''T'' ' ||
'FROM '|| p_owner_table || ' ' ||
'WHERE '||l_column_name||'= :1 and rownum = 1';
* This is a wrapper on top of FND_LOOKUP_VALUES_PKG.insert_row. It also
* updates frozen flag and validate class code meaning.
*
* 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_class_code_rec Lookup value related columns
* 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
*
* 05-28-2002 Amy Wu o Created.
* 07-01-2003 Dhaval Mehta Bug 2960224 : Added validation to TYPE against active
* class categories.
* 20-Sep-2007 Manivannan J Bug 6158794 : Added validation to TYPE against
* classification code and classification meaning.
*/
PROCEDURE create_class_code(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_class_code_rec IN CLASS_CODE_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) is
row_id varchar2(64);
Fnd_Lookup_Values_Pkg.Insert_Row(
X_ROWID => row_id,
X_LOOKUP_TYPE => l_class_code_rec.type,
X_SECURITY_GROUP_ID => 0,
X_VIEW_APPLICATION_ID => 222,
X_LOOKUP_CODE => l_class_code_rec.code,
X_TAG => null,
X_ATTRIBUTE_CATEGORY => l_class_code_rec.attribute_category,
X_ATTRIBUTE1 => l_class_code_rec.attribute1,
X_ATTRIBUTE2 => l_class_code_rec.attribute2,
X_ATTRIBUTE3 => l_class_code_rec.attribute3,
X_ATTRIBUTE4 => l_class_code_rec.attribute4,
X_ENABLED_FLAG => l_class_code_rec.enabled_flag,
X_START_DATE_ACTIVE => l_class_code_rec.start_date_active,
X_END_DATE_ACTIVE => l_class_code_rec.end_date_active,
X_TERRITORY_CODE => null,
X_ATTRIBUTE5 => l_class_code_rec.attribute5,
X_ATTRIBUTE6 => l_class_code_rec.attribute6,
X_ATTRIBUTE7 => l_class_code_rec.attribute7,
X_ATTRIBUTE8 => l_class_code_rec.attribute8,
X_ATTRIBUTE9 => l_class_code_rec.attribute9,
X_ATTRIBUTE10 => l_class_code_rec.attribute10,
X_ATTRIBUTE11 => l_class_code_rec.attribute11,
X_ATTRIBUTE12 => l_class_code_rec.attribute12,
X_ATTRIBUTE13 => l_class_code_rec.attribute13,
X_ATTRIBUTE14 => l_class_code_rec.attribute14,
X_ATTRIBUTE15 => l_class_code_rec.attribute15,
X_MEANING => l_class_code_rec.meaning,
X_DESCRIPTION => l_class_code_rec.description,
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);
* PROCEDURE update_class_code
*
* DESCRIPTION
* This is a wrapper on top of FND_LOOKUP_VALUES_PKG.update_row. It also
* updates frozen flag and validate class code meaning.
*
* 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_class_code_rec Lookup value related columns
* IN/OUT:
* p_object_version_number Used for locking the being updated record.
* 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
*
* 05-28-2002 Amy Wu o Created.
*
*/
PROCEDURE update_class_code(
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
p_class_code_rec IN CLASS_CODE_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
l_class_code_rec CLASS_CODE_REC_TYPE := p_class_code_rec;
savepoint update_class_code;
SELECT start_date_active,end_date_active into l_start_date_active,l_end_date_active
FROM fnd_lookup_values_vl val
WHERE val.lookup_type = l_class_code_rec.type
AND val.lookup_code = l_class_code_rec.code
AND rownum = 1
FOR UPDATE OF LOOKUP_CODE NOWAIT;
Fnd_Lookup_Values_Pkg.Update_Row(
X_LOOKUP_TYPE => l_class_code_rec.type,
X_SECURITY_GROUP_ID => 0,
X_VIEW_APPLICATION_ID => 222,
X_LOOKUP_CODE => l_class_code_rec.code,
X_TAG => null,
X_ATTRIBUTE_CATEGORY => l_class_code_rec.attribute_category,
X_ATTRIBUTE1 => l_class_code_rec.attribute1,
X_ATTRIBUTE2 => l_class_code_rec.attribute2,
X_ATTRIBUTE3 => l_class_code_rec.attribute3,
X_ATTRIBUTE4 => l_class_code_rec.attribute4,
X_ENABLED_FLAG => l_class_code_rec.enabled_flag,
X_START_DATE_ACTIVE => l_class_code_rec.start_date_active,
X_END_DATE_ACTIVE => l_class_code_rec.end_date_active,
X_TERRITORY_CODE => null,
X_ATTRIBUTE5 => l_class_code_rec.attribute5,
X_ATTRIBUTE6 => l_class_code_rec.attribute6,
X_ATTRIBUTE7 => l_class_code_rec.attribute7,
X_ATTRIBUTE8 => l_class_code_rec.attribute8,
X_ATTRIBUTE9 => l_class_code_rec.attribute9,
X_ATTRIBUTE10 => l_class_code_rec.attribute10,
X_ATTRIBUTE11 => l_class_code_rec.attribute11,
X_ATTRIBUTE12 => l_class_code_rec.attribute12,
X_ATTRIBUTE13 => l_class_code_rec.attribute13,
X_ATTRIBUTE14 => l_class_code_rec.attribute14,
X_ATTRIBUTE15 => l_class_code_rec.attribute15,
X_MEANING => l_class_code_rec.meaning,
X_DESCRIPTION => l_class_code_rec.description,
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_BUSINESS_EVENT_V2PVT.update_class_code_event (
p_class_code_rec => l_class_code_rec,
p_old_class_code_rec => NULL );
ROLLBACK TO update_class_code;
ROLLBACK TO update_class_code ;
ROLLBACK TO update_class_code;
end update_class_code;
SELECT LOOKUP_CODE,
MEANING,
DESCRIPTION,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
ENABLED_FLAG
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = p_class_category;
SELECT CLASS_CODE,
SUB_CLASS_CODE,
START_DATE_ACTIVE,
END_DATE_ACTIVE
FROM HZ_CLASS_CODE_RELATIONS
WHERE CLASS_CATEGORY = p_class_category;