[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE updateExceptions (
p_create_update_sst_flag IN VARCHAR2,
p_party_id IN NUMBER,
p_data_source_type IN VARCHAR2,
p_name_list IN INDEXVARCHAR30List,
p_updatable_flag_list IN INDEXVARCHAR1List,
p_exception_type_list IN INDEXVARCHAR30List,
p_sst_value_is_not_null_list IN INDEXVARCHAR1List,
p_data_source_list IN INDEXVARCHAR30List
);
* is possible for selected data sources(Ranking method = date).
* 02-28-2005 Rajib Ranjan Borah o Bug 4156090. Changed the caching logic for
* G_ATTRIBUTE_DATA_SOURCE.
* Order by is removed from cursor c_data_source.
*/
PROCEDURE cacheSetupForPartyProfiles (
p_party_id IN NUMBER := NULL,
p_entity_name IN VARCHAR2 := NULL
) IS
/*
-- load last update date for party profiles' setup
CURSOR c_profile_last_update_date (
p_entity_name VARCHAR2
) IS
SELECT max(last_update_date) last_update_date
FROM hz_entity_attributes
WHERE entity_name = p_entity_name;
SELECT e.entity_attr_id, e.attribute_name
FROM hz_entity_attributes e
WHERE e.entity_name = p_entity_name
ORDER BY e.attribute_name;
SELECT s.entity_attr_id,
s.content_source_type,
s.ranking
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.entity_name = p_entity_name
AND s.entity_attr_id = e.entity_attr_id
AND s.ranking <> 0;
SELECT 'Y'
FROM hz_organization_profiles
WHERE party_id = p_party_id
AND actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
AND effective_end_date is NULL;
SELECT 'Y'
FROM hz_person_profiles
WHERE party_id = p_party_id
AND actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
AND effective_end_date is NULL;
SELECT e.entity_attr_id,
NVL(exp.content_source_type, s1.content_source_type),
exp.exception_type
FROM hz_entity_attributes e,
hz_select_data_sources s1,
(SELECT entity_attr_id,
content_source_type,
exception_type
FROM hz_win_source_exceps
WHERE party_id = p_party_id ) exp
WHERE e.entity_name = p_entity_name
AND e.entity_attr_id = s1.entity_attr_id
AND (s1.ranking = 1 or (s1.ranking = -1 and exp.content_source_type = s1.content_source_type))
AND exp.entity_attr_id (+) = e.entity_attr_id;
SELECT e.entity_attr_id,
NVL(exp.content_source_type, 'USER_ENTERED'),
exp.exception_type
FROM hz_entity_attributes e,
(SELECT entity_attr_id,
content_source_type,
exception_type
FROM hz_win_source_exceps
WHERE party_id = p_party_id ) exp
WHERE e.entity_name = p_entity_name
AND exp.entity_attr_id (+) = e.entity_attr_id
and exists (select 'Y' from hz_select_data_sources s1
where s1.entity_attr_id = e.entity_attr_id
and s1.ranking <> 0);
SELECT entity_attr_id,/* overwrite_flag*/
orig_system
FROM hz_user_overwrite_rules
WHERE rule_id = p_rule_id
AND overwrite_flag = 'Y'
ORDER BY entity_attr_id;
SELECT rule.entity_attr_id,
rule.orig_system
FROM hz_thirdparty_rule rule
WHERE rule.overwrite_flag = 'Y'
AND NOT EXISTS
(SELECT '1'
FROM hz_thirdparty_exceps exceps
WHERE exceps.party_id = p_party_id
AND exceps.entity_attr_id = rule.entity_attr_id)
ORDER BY rule.entity_attr_id;
SELECT orig_system
FROM hz_orig_systems_b
WHERE sst_flag = 'Y'
-- AND status = 'A'
ORDER BY orig_system;
OPEN c_profile_last_update_date(p_entity_name);
FETCH c_profile_last_update_date INTO l_last_update_date;
CLOSE c_profile_last_update_date;
IF l_last_update_date IS NULL THEN
RETURN;
IF G_ORG_SETUP_LAST_UPDATE_DATE IS NULL OR
G_ORG_SETUP_LAST_UPDATE_DATE <> l_last_update_date
THEN
G_ORG_SETUP_LAST_UPDATE_DATE := l_last_update_date;
IF G_PER_SETUP_LAST_UPDATE_DATE IS NULL OR
G_PER_SETUP_LAST_UPDATE_DATE <> l_last_update_date
THEN
G_PER_SETUP_LAST_UPDATE_DATE := l_last_update_date;
G_OVERWRITE_THIRD_PARTY.DELETE;
G_REAL_DATA_SOURCE.DELETE;
G_EXCEPTION_TYPE.DELETE;
G_OVERWRITE_USER_RULE.DELETE;
CURSOR c_datasource_last_update_date IS
SELECT max(last_update_date) last_update_date
FROM hz_entity_attributes;
SELECT e.entity_attr_id, e.entity_name
FROM hz_entity_attributes e
WHERE e.entity_name NOT IN ('HZ_ORGANIZATION_PROFILES','HZ_PERSON_PROFILES')
ORDER BY e.entity_name;
The concept of selected data sources for other entities is obsoleted.
-- load data source in setup table for other entities.
CURSOR c_data_source_other IS
SELECT s.entity_attr_id,
s.content_source_type
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.entity_name NOT IN ('HZ_ORGANIZATION_PROFILES','HZ_PERSON_PROFILES')
AND s.entity_attr_id = e.entity_attr_id
AND s.ranking > 0
ORDER BY s.entity_attr_id;
SELECT UNIQUE s.content_source_type
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.entity_name = p_entity_name
AND s.entity_attr_id = e.entity_attr_id
AND s.ranking <> 0; -- SSM SST Integration and Extension -->> ranking of -1 denotes MRR
SELECT entity_attr_id, creation_flag
FROM hz_user_create_rules
WHERE rule_id = p_rule_id;
SELECT entity_attr_id,
orig_system
FROM hz_user_overwrite_rules
WHERE overwrite_flag = 'Y'
AND rule_id = p_rule_id;
SELECT orig_system
FROM hz_orig_systems_b
WHERE sst_flag = 'Y'
-- AND status = 'A'
ORDER BY orig_system;
OPEN c_datasource_last_update_date;
FETCH c_datasource_last_update_date INTO l_last_update_date;
CLOSE c_datasource_last_update_date;
IF l_last_update_date IS NULL THEN
RETURN;
IF G_DATASOURCE_LAST_UPDATE_DATE IS NULL OR
G_DATASOURCE_LAST_UPDATE_DATE <> l_last_update_date
THEN
*/
IF G_DATASOURCE_LOADED = 'N' THEN
-- load entity names
OPEN c_entity_dict_other;
| The concept of selected data sources for other entities is obsoleted.
| Comment out the code below while handling other entities.
-- G_MIXNM_ENABLED_FLAG.DELETE;
i_content_source_type.DELETE;
G_CREATE_USER_ENTERED.DELETE;
G_OTHER_ENT_USER_OVERWRITE.DELETE;
l_update VARCHAR2(1) := 'N';
l_update := 'Y';
l_update := 'Y'; p_exception_type := 'Exception';
select entity_name into p_entity from hz_entity_attributes
where entity_attr_id = p_entity_attr_id;
select '1' into l_tmp_d from hz_organization_profiles where party_id = p_party_id
and actual_content_source = p_real_data_source_type
-- Bug 4482630 : query only active profiles
and EFFECTIVE_END_DATE is NULL;
l_update := 'Y'; p_exception_type := 'Migration';
select '1' into l_tmp_d from hz_person_profiles where party_id = p_party_id
and actual_content_source = p_real_data_source_type
and EFFECTIVE_END_DATE is NULL;
l_update := 'Y'; p_exception_type := 'Migration';
l_update := 'Y'; --x_exception_type := 'Migration';
l_update := 'Y';
l_update := 'Y';
RETURN l_update;
l_message_name := 'HZ_API_SST_NONUPDATEABLE_COL';
l_message_name := 'HZ_API_SST_NONUPDATEABLE_COLS';
* PRIVATE PROCEDURE updateExceptions
*
* DESCRIPTION
* Update exception table which is used to trace data source
* for each restricted attribute.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* IN:
* p_create_update_sst_flag Create / update SST profile flag.
* p_party_id Party id.
* p_data_source_type Data source type.
* p_updatable_flag_list A list of updatable property.
* p_exception_type_list A list of exception type.
* p_sst_value_is_not_null_list A 'Y'/'N' list to indicate if the attribute
* in the SST record is NULL.
* p_data_source_list Data source list.
*
* MODIFICATION HISTORY
*
* 04-30-2002 Jianying Huang o Created.
* 02-28-2005 Rajib Ranjan Borah o Bug 4156090. Caching logic for G_ATTRIBUTE_DATA_SOURCE
* is changed.
*/
PROCEDURE updateExceptions (
p_create_update_sst_flag IN VARCHAR2,
p_party_id IN NUMBER,
p_data_source_type IN VARCHAR2,
p_name_list IN INDEXVARCHAR30List,
p_updatable_flag_list IN INDEXVARCHAR1List,
p_exception_type_list IN INDEXVARCHAR30List,
p_sst_value_is_not_null_list IN INDEXVARCHAR1List,
p_data_source_list IN INDEXVARCHAR30List
) IS
i_entity_attr_id INDEXIDList;
hz_utility_v2pub.debug(p_message=>'updateExceptions (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
hz_utility_v2pub.debug(p_message=>'p_create_update_sst_flag = '||p_create_update_sst_flag,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_sst_flag = 'C' THEN
l_max := get_max(
p_sst_value_is_not_null_list.LAST,
p_name_list.LAST,
p_updatable_flag_list.LAST);
UPDATE hz_win_source_exceps
SET content_source_type = l_data_source_type,
-- exception_type = i_exception_type(i),
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
last_update_date = SYSDATE
WHERE party_id = p_party_id
AND entity_attr_id = i_entity_attr_id1(i);
INSERT INTO hz_win_source_exceps (
party_id,
entity_attr_id,
content_source_type,
exception_type,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by
) --VALUES (
-- Bug 4244112 : insert only if it is Rank attribute
SELECT
p_party_id,
i_entity_attr_id(i),
i_real_data_source(i),
i_exception_type(i),
hz_utility_v2pub.created_by,
SYSDATE,
hz_utility_v2pub.last_update_login,
SYSDATE,
hz_utility_v2pub.last_updated_by
FROM hz_select_data_sources
WHERE ranking > 0
and content_source_type = 'USER_ENTERED'
and entity_attr_id = i_entity_attr_id(i)
and i_exception_type(i) <> 'MRN';
ELSE -- p_create_update_sst_flag = 'U'
l_real_data_source := l_data_source_type;
DELETE hz_win_source_exceps
WHERE party_id = p_party_id
AND entity_attr_id = i_entity_attr_id(i)
AND i_real_data_source(i) = i_winner(i)
AND exception_type <> 'MRR';
hz_utility_v2pub.debug(p_message=>'Delete '||SQL%ROWCOUNT||' records',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
UPDATE hz_win_source_exceps
SET content_source_type = l_real_data_source,
exception_type = i_exception_type(i),
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
last_update_date = SYSDATE
WHERE party_id = p_party_id
AND entity_attr_id = i_entity_attr_id(i);
UPDATE hz_win_source_exceps
SET content_source_type = l_real_data_source,
exception_type = i_exception_type(i),
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
last_update_date = SYSDATE
WHERE party_id = p_party_id
AND entity_attr_id = i_entity_attr_id(i)
AND content_source_type <> i_real_data_source(i)
AND i_real_data_source(i) <> i_winner(i)
AND i_exception_type(i) <> 'MRN';
hz_utility_v2pub.debug(p_message=>'Update '||SQL%ROWCOUNT||' records',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
INSERT INTO hz_win_source_exceps (
party_id,
entity_attr_id,
content_source_type,
exception_type,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by
) SELECT
p_party_id,
i_entity_attr_id(i),
i_real_data_source(i),
i_exception_type(i),
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_win_source_exceps
WHERE party_id = p_party_id
AND entity_attr_id = i_entity_attr_id(i)
AND i_exception_type(i) <> 'MRN');
hz_utility_v2pub.debug(p_message=>'Insert '||SQL%ROWCOUNT||' records',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'updateExceptions (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END updateExceptions;
(p_create_update_flag => 'C',
p_new_rec => p_organization_rec,
p_old_rec => HZ_PARTY_V2PUB.G_MISS_ORGANIZATION_REC,
x_name_list => l_name_list,
x_new_value_is_null_list => l_null_list);
(p_create_update_flag => 'C',
p_new_rec => p_person_rec,
p_old_rec => HZ_PARTY_V2PUB.G_MISS_PERSON_REC,
x_name_list => l_name_list,
x_new_value_is_null_list => l_null_list);
INSERT INTO hz_win_source_exceps (
party_id,
entity_attr_id,
content_source_type,
exception_type,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by
) --VALUES (
-- Bug 4244112 : populate only for rank attributes
SELECT
p_party_id,
l_entity_attr_id,
G_MISS_CONTENT_SOURCE_TYPE,
'Migration',
hz_utility_v2pub.created_by,
SYSDATE,
hz_utility_v2pub.last_update_login,
SYSDATE,
hz_utility_v2pub.last_updated_by
FROM hz_select_data_sources
WHERE ranking > 1
and content_source_type = 'USER_ENTERED'
and entity_attr_id = l_entity_attr_id;
-- this third party is a selected data source but is not the highest rank.
INSERT INTO hz_win_source_exceps (
party_id,
entity_attr_id,
content_source_type,
exception_type,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by
) VALUES (
p_party_id,
l_entity_attr_id,
p_third_party_content_source,
'Migration',
hz_utility_v2pub.created_by,
SYSDATE,
hz_utility_v2pub.last_update_login,
SYSDATE,
hz_utility_v2pub.last_updated_by );
SELECT '1'
FROM hz_orig_systems_b
WHERE orig_system = p_content_source_type
AND sst_flag = 'Y'
AND status = 'A';
* FUNCTION CheckUserUpdatePrivilege
*
* DESCRIPTION
* Check if user has privilege to update a third party record.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_actual_content_source Actual content source.
* OUT:
* x_return_status Return FND_API.G_RET_STS_ERROR if the
* user under this site/application/
* responsibility is not allowed to create
* user-entered data for this entity.
*
* NOTES
* The procedure can only be called for other entities like HZ_CONTACT_POINTS,
* HZ_LOCATIONS etc.
*
* MODIFICATION HISTORY
*
* 03-01-2002 Jianying Huang o Created.
* 12-31-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
* Added parameters p_entity_name and p_new_actual_content_source.
* User overwrite rules will be checked now
* instead of profile option 'HZ_UPDATE_THIRD_PARTY_DATA'.
* o If this is a purchased source system throw error straightaway.
* o Call cacheSetupForOtherEntities first to
* load the other entities related setup.
*/
PROCEDURE CheckUserUpdatePrivilege (
p_actual_content_source IN VARCHAR2,
p_new_actual_content_source IN VARCHAR2,
p_entity_name IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_entity_attr_id NUMBER;
NVL(FND_PROFILE.value('HZ_UPDATE_THIRD_PARTY_DATA'), 'N') = 'N'
THEN
*/
/* new message */
/* FND_MESSAGE.SET_NAME('AR', 'HZ_NOTALLOW_UPDATE_THIRD_PARTY');
-- However users can update spoke source systems only if rules allow it to.
IF (p_new_actual_content_source <> G_MISS_CONTENT_SOURCE_TYPE
AND p_new_actual_content_source IS NOT NULL)
THEN
RETURN;
FND_MESSAGE.SET_NAME('AR', 'HZ_NOTALLOW_UPDATE_THIRD_PARTY');
END CheckUserUpdatePrivilege;
* FUNCTION isDataSourceSelected
*
* DESCRIPTION
* Internal use only!!!
* Return 'Y' if the data source has been selected.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_selected_datasources A list of selected data sources. You can
* get it via HZ_MIXNM_UTILITY.
* p_actual_content_source Actual content source.
*
* NOTES
*
* *** SSM SST Integration and Extension ***
* This function will only be called for profiles as for other entities, the
* concept of selected/ deselected data sources is obsoleted.
* *** ***
*
* MODIFICATION HISTORY
*
* 03-01-2002 Jianying Huang o Created.
* 07-07-2005 Dhaval Mehta o Bug 4376604. Changed the signature to p_entity_name
* instead of p_selected_datasources
*/
FUNCTION isDataSourceSelected(
-- p_selected_datasources IN VARCHAR2,
p_entity_name IN VARCHAR2,
p_actual_content_source IN VARCHAR2
) RETURN VARCHAR2 IS
l_ret VARCHAR2(1) := 'N';
/* IF INSTRB(p_selected_datasources, ''''||l_actual_content_source||'''') > 0 THEN
l_ret := 'Y';
END isDataSourceSelected;
* p_create_update_flag Create or update flag. 'C' is for create. 'U' is for
* update.
* p_check_update_privilege Check if user has privilege to update third party data.
* p_content_source_type Content source type.
* p_old_content_source_type Old content source type.
* p_actual_content_source Actual content source.
* p_old_actual_content_source Old actual content source.
* IN/OUT:
* x_return_status Return FND_API.G_RET_STS_ERROR if the
* user under this site/application/
* responsibility is not allowed to create
* user-entered data for this entity.
*
* NOTES
* The procedure can only be called for other entities like HZ_CONTACT_POINTS,
* HZ_LOCATIONS etc. It can not be called on party profiles HZ_ORGANIZATION_PROFILES,
* HZ_PERSON_PROFILES.
*
* MODIFICATION HISTORY
*
* 03-01-2002 Jianying Huang o Created.
* 12-31-2004 Rajib Ranjan Borah o SSM SST Integration and Extension.
* Added parameter p_entity_name as user update privileges
* vary for different entities with different content sources.
* o validate_nonupdateable for actual_content_source is commented out.
* o Call CheckUserUpdatePrivilege only if user_entered is trying to
* update and not if any other content source is trying to.
*/
PROCEDURE ValidateContentSource (
p_api_version IN VARCHAR2,
p_create_update_flag IN VARCHAR2,
p_check_update_privilege IN VARCHAR2,
p_content_source_type IN VARCHAR2,
p_old_content_source_type IN VARCHAR2,
p_actual_content_source IN VARCHAR2,
p_old_actual_content_source IN VARCHAR2,
p_entity_name IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_data_source_from VARCHAR2(30);
'p_create_update_flag = '||p_create_update_flag||', '||
'p_content_source_type = '||p_content_source_type||','||
'p_old_content_source_type = '||p_old_content_source_type||','||
'p_actual_content_source = '||p_actual_content_source||','||
'p_old_actual_content_source = '||p_old_actual_content_source||','||
'x_return_status = '||x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' AND
p_check_update_privilege = 'Y' AND
p_old_actual_content_source <> G_MISS_CONTENT_SOURCE_TYPE
THEN
CheckUserUpdatePrivilege (
p_actual_content_source => p_old_actual_content_source,
p_new_actual_content_source => p_actual_content_source,
p_entity_name => p_entity_name,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_column => 'content_source_type',
p_column_value => p_content_source_type,
p_default_value => G_MISS_CONTENT_SOURCE_TYPE,
p_old_column_value => p_old_content_source_type,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
-- Find real data source via comparing content_source_type
-- and actual_content_source.
IF (p_api_version = 'V1' AND
l_actual_content_source = FND_API.G_MISS_CHAR) OR
(p_api_version = 'V2' AND
l_actual_content_source IS NULL)
THEN
l_actual_content_source :=
FindDataSource (
p_content_source_type => l_content_source_type,
p_actual_content_source => NVL(l_actual_content_source,FND_API.G_MISS_CHAR),
p_def_actual_content_source => FND_API.G_MISS_CHAR,
x_data_source_from => l_data_source_from );
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'actual_content_source',
p_column_value => l_actual_content_source,
p_old_column_value => p_old_actual_content_source,
x_return_status => x_return_status);
* p_selected_datasources A list of selected data sources. You can
* get it via HZ_MIXNM_UTILITY.
* p_content_source_type Content source type.
* p_actual_content_source Actual content source.
* OUT:
* x_is_datasource_selected Return 'Y'/'N' to indicate if the data
* source is visible.
* x_return_status Return FND_API.G_RET_STS_ERROR if any
* validation fails.
*
* NOTES
* The procedure can only be called for other entities like HZ_CONTACT_POINTS,
* HZ_LOCATIONS etc. It can not be called on party profiles HZ_ORGANIZATION_PROFILES,
* HZ_PERSON_PROFILES.
*
* MODIFICATION HISTORY
*
* 03-01-2002 Jianying Huang o Created.
* 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
* x_is_datasource_selected will be set to 'Y'
* always.
* Actually parameters p_mixnmatch_enabled,
* p_selected_data_sources and x_is_datasource_selected
* are redundant and are retained for back-ward compatibility.
*/
PROCEDURE AssignDataSourceDuringCreation (
p_entity_name IN VARCHAR2,
p_entity_attr_id IN OUT NOCOPY NUMBER,
p_mixnmatch_enabled IN VARCHAR2,
p_selected_datasources IN VARCHAR2,
p_content_source_type IN OUT NOCOPY VARCHAR2,
p_actual_content_source IN OUT NOCOPY VARCHAR2,
x_is_datasource_selected OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2,
p_api_version IN VARCHAR2
) IS
l_data_source_from VARCHAR2(30);
p_create_update_flag => 'C',
p_column => 'content_source_type',
p_column_value => p_content_source_type,
p_default_value => G_MISS_CONTENT_SOURCE_TYPE,
p_old_column_value => null,
x_return_status => x_return_status);
x_is_datasource_selected :='Y';
/* isDataSourceSelected(
p_selected_datasources => p_selected_datasources,
p_actual_content_source => p_actual_content_source );
l_src_selected VARCHAR2(1) := 'N';
select 'Y' into l_src_selected
from hz_select_data_sources d, hz_entity_attributes e
where e.entity_attr_id = d.entity_attr_id
and UPPER(e.entity_name) = UPPER(p_entity_name)
and d.content_source_type <> G_MISS_CONTENT_SOURCE_TYPE
and d.ranking > 0
and rownum =1;
l_return := l_src_selected;
* PROCEDURE updateSSTProfile
*
* DESCRIPTION
* Return new SST record to create / update SST profile.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag.
* p_create_update_sst_flag Create update SST profile flag.
* p_raise_error_flag Raise error flag.
* p_party_type Party type.
* p_party_id Party Id.
* p_new_person_rec New person record.
* p_old_person_rec New person record.
* p_sst_person_rec Current SST person record.
* p_new_organization_rec New organization record.
* p_old_organization_rec New organization record.
* p_sst_organization_rec Current SST organization record.
* p_data_source_type Comming data source type.
* IN/OUT:
* p_new_sst_person_rec New SST person record.
* p_new_sst_organization_rec New SST organization record.
* x_return_status Return status.
*
* NOTES
* The procedure should only be called if the mix-n-match is enable for
* the entity.
*
* MODIFICATION HISTORY
*
* 05-01-2002 Jianying Huang o Created
*/
PROCEDURE updateSSTProfile (
p_create_update_flag IN VARCHAR2,
p_create_update_sst_flag IN VARCHAR2,
p_raise_error_flag IN VARCHAR2,
p_party_type IN VARCHAR2,
p_party_id IN NUMBER,
p_new_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
p_old_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
p_sst_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
p_new_sst_person_rec IN OUT NOCOPY HZ_PARTY_V2PUB.PERSON_REC_TYPE,
p_new_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
p_old_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
p_sst_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
p_new_sst_organization_rec IN OUT NOCOPY HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
p_data_source_type IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
BEGIN
IF p_party_type = 'PERSON' THEN
updatePerSSTProfile (
p_create_update_flag => p_create_update_flag,
p_create_update_sst_flag => p_create_update_sst_flag,
p_raise_error_flag => p_raise_error_flag,
p_party_id => p_party_id,
p_new_person_rec => p_new_person_rec,
p_old_person_rec => p_old_person_rec,
p_sst_person_rec => p_sst_person_rec,
p_new_sst_person_rec => p_new_sst_person_rec,
p_data_source_type => p_data_source_type,
x_return_status => x_return_status );
updateOrgSSTProfile (
p_create_update_flag => p_create_update_flag,
p_create_update_sst_flag => p_create_update_sst_flag,
p_raise_error_flag => p_raise_error_flag,
p_party_id => p_party_id,
p_new_organization_rec => p_new_organization_rec,
p_old_organization_rec => p_old_organization_rec,
p_sst_organization_rec => p_sst_organization_rec,
p_new_sst_organization_rec => p_new_sst_organization_rec,
p_data_source_type => p_data_source_type,
x_return_status => x_return_status );
END updateSSTProfile;
* PROCEDURE updateSSTPerProfile
*
* DESCRIPTION
* Return new SST record to create / update person SST profile.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag.
* p_create_update_sst_flag Create update SST profile flag.
* p_raise_error_flag Raise error flag.
* p_party_type Party type.
* p_party_id Party Id.
* p_new_person_rec New person record.
* p_old_person_rec New person record.
* p_sst_person_rec Current SST person record.
* p_data_source_type Comming data source type.
* IN/OUT:
* p_new_sst_person_rec New SST person record.
* x_return_status Return status.
*
* NOTES
* The procedure should only be called if the mix-n-match is enable for
* the entity.
*
* MODIFICATION HISTORY
*
* 05-01-2002 Jianying Huang o Created
*/
PROCEDURE updatePerSSTProfile (
p_create_update_flag IN VARCHAR2,
p_create_update_sst_flag IN VARCHAR2,
p_raise_error_flag IN VARCHAR2,
p_party_id IN NUMBER,
p_new_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
p_old_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
p_sst_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
p_new_sst_person_rec IN OUT NOCOPY HZ_PARTY_V2PUB.PERSON_REC_TYPE,
p_data_source_type IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_new_value_is_null_list INDEXVARCHAR1List;
hz_utility_v2pub.debug(p_message=>'updatePerSSTProfile (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
p_create_update_flag => p_create_update_flag,
p_new_rec => p_new_person_rec,
p_old_rec => p_old_person_rec,
x_name_list => l_name_list,
x_new_value_is_null_list => l_new_value_is_null_list);
hz_utility_v2pub.debug(p_message=>'updatePerSSTColumn (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
IF p_create_update_sst_flag = 'C' THEN
hz_mixnm_api_dynamic_pkg.createSSTRecord(
p_new_data_source => p_data_source_type,
p_new_rec => p_new_person_rec,
p_sst_rec => p_new_sst_person_rec,
p_updateable_flag_list => l_updatable_flag_list,
p_exception_type_list => l_exception_type_list);
hz_mixnm_api_dynamic_pkg.updateSSTRecord(
p_create_update_flag => p_create_update_flag,
p_new_data_source => p_data_source_type,
p_new_rec => p_new_person_rec,
p_sst_rec => p_new_sst_person_rec,
p_updateable_flag_list => l_updatable_flag_list,
p_exception_type_list => l_exception_type_list,
p_new_value_is_null_list => l_new_value_is_null_list,
x_data_source_list => l_data_source_list);
updateExceptions (
p_create_update_sst_flag => p_create_update_sst_flag,
p_party_id => p_party_id,
p_data_source_type => p_data_source_type,
p_name_list => l_name_list,
p_updatable_flag_list => l_updatable_flag_list,
p_exception_type_list => l_exception_type_list,
p_sst_value_is_not_null_list => l_sst_value_is_not_null_list,
p_data_source_list => l_data_source_list);
hz_utility_v2pub.debug(p_message=>'updatePerSSTProfile (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END updatePerSSTProfile;
* PROCEDURE updateSSTOrgProfile
*
* DESCRIPTION
* Return new SST record to create / update organization SST profile.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag.
* p_create_update_sst_flag Create update SST profile flag.
* p_raise_error_flag Raise error flag.
* p_party_type Party type.
* p_party_id Party Id.
* p_new_organization_rec New organization record.
* p_old_organization_rec New organization record.
* p_sst_organization_rec Current SST organization record.
* p_data_source_type Comming data source type.
* IN/OUT:
* p_new_sst_organization_rec New SST organization record.
* x_return_status Return status.
*
* NOTES
* The procedure should only be called if the mix-n-match is enable for
* the entity.
*
* MODIFICATION HISTORY
*
* 05-01-2002 Jianying Huang o Created
*/
PROCEDURE updateOrgSSTProfile (
p_create_update_flag IN VARCHAR2,
p_create_update_sst_flag IN VARCHAR2,
p_raise_error_flag IN VARCHAR2,
p_party_id IN NUMBER,
p_new_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
p_old_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
p_sst_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
p_new_sst_organization_rec IN OUT NOCOPY HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
p_data_source_type IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_new_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE := p_new_organization_rec;
hz_utility_v2pub.debug(p_message=>'updateOrgSSTProfile (+)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
hz_utility_v2pub.debug(p_message=>'p_create_update_sst_flag = '||p_create_update_sst_flag,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
p_create_update_flag => p_create_update_flag,
p_new_rec => p_new_organization_rec,
p_old_rec => p_old_organization_rec,
x_name_list => l_name_list,
x_new_value_is_null_list => l_new_value_is_null_list);
hz_utility_v2pub.debug(p_message=>'updatePerSSTColumn (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
IF p_create_update_sst_flag = 'C' THEN
hz_mixnm_api_dynamic_pkg.createSSTRecord(
p_new_data_source => p_data_source_type,
p_new_rec => p_new_organization_rec,
p_sst_rec => p_new_sst_organization_rec,
p_updateable_flag_list => l_updatable_flag_list,
p_exception_type_list => l_exception_type_list);
hz_mixnm_api_dynamic_pkg.updateSSTRecord(
p_create_update_flag => p_create_update_flag,
p_new_data_source => p_data_source_type,
p_new_rec => l_new_organization_rec,
p_sst_rec => p_new_sst_organization_rec,
p_updateable_flag_list => l_updatable_flag_list,
p_exception_type_list => l_exception_type_list,
p_new_value_is_null_list => l_new_value_is_null_list,
x_data_source_list => l_data_source_list);
updateExceptions (
p_create_update_sst_flag => p_create_update_sst_flag,
p_party_id => p_party_id,
p_data_source_type => p_data_source_type,
p_name_list => l_name_list,
p_updatable_flag_list => l_updatable_flag_list,
p_exception_type_list => l_exception_type_list,
p_sst_value_is_not_null_list => l_sst_value_is_not_null_list,
p_data_source_list => l_data_source_list);
hz_utility_v2pub.debug(p_message=>'updateOrgSSTProfile (-)',
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
END updateOrgSSTProfile;
* p_selected_datasources Select data sources for this entity.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 05-01-2002 Jianying Huang o Created
* 07-07-2005 Dhaval Mehta o Bug 4376604. p_selected_data_sources has been made
* redundant and has been retained for backward compatibility.
*/
PROCEDURE LoadDataSources (
p_entity_name IN VARCHAR2,
p_entity_attr_id IN OUT NOCOPY NUMBER,
p_mixnmatch_enabled IN OUT NOCOPY VARCHAR2,
p_selected_datasources IN OUT NOCOPY VARCHAR2,
p_called_from_policy_function IN VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
p_selected_datasources := G_ENTITY_DATA_SOURCE(p_entity_attr_id);
p_selected_datasources := ''''||G_MISS_CONTENT_SOURCE_TYPE||'''';
p_selected_datasources := NULL;
hz_utility_v2pub.debug(p_message=>'p_selected_datasources = '||p_selected_datasources,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
* FUNCTION getSelectedDataSources
*
* DESCRIPTION
* Return selected data sources for a given entity.
* Return selected data sources for a given entity. The
* function is created for policy function. For anywhere
* else, you should call LoadDataSources.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_entity_name Entity name.
* IN/OUT:
* p_entity_attr_id Entity Id.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 05-01-2002 Jianying Huang o Created
*/
FUNCTION getSelectedDataSources (
p_entity_name IN VARCHAR2,
p_entity_attr_id IN OUT NOCOPY NUMBER
) RETURN VARCHAR2 IS
-- Bug 4171892
l_selected_datasources VARCHAR2(1000);
l_selected_datasources,
'Y');
RETURN l_selected_datasources;
END getSelectedDataSources;
* PRIVATE PROCEDURE ResetUpdatedFlag
*
* DESCRIPTION
* Reset updated flag in hz_entity_attributes.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* MODIFICATION HISTORY
*
* 04-30-2002 Jianying Huang o Created.
*/
PROCEDURE ResetUpdatedFlag IS
BEGIN
UPDATE hz_entity_attributes
SET updated_flag = 'N',
last_updated_by = hz_utility_v2pub.last_updated_by,
last_update_login = hz_utility_v2pub.last_update_login,
last_update_date = SYSDATE,
request_id = hz_utility_v2pub.request_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id,
program_update_date = SYSDATE;
END ResetUpdatedFlag;
* x_process_org_mode C/U flag for create / update.
* x_org_total Total records need to be processed.
* x_org_id_count PL/SQL table to store organization profile id.
* x_org_id_start PL/SQL table to store the start position.
* x_org_id_end PL/SQL table to store the end position.
* x_process_person Flag to indicate if we need to
* process data in person profiles.
* x_process_person_mode C/U flag for create / update.
* x_per_total Total records need to be processed.
* x_per_id_count PL/SQL table to store organization profile id.
* x_per_id_start PL/SQL table to store the start position.
* x_per_id_end PL/SQL table to store the end position.
*
* MODIFICATION HISTORY
*
* 04-30-2002 Jianying Huang o Created.
*/
PROCEDURE ProcessPartyProfiles (
x_process_org OUT NOCOPY BOOLEAN,
x_process_org_mode OUT NOCOPY VARCHAR2,
x_org_total OUT NOCOPY NUMBER,
x_org_id_count OUT NOCOPY INDEXIDList,
x_org_id_start OUT NOCOPY INDEXIDList,
x_org_id_end OUT NOCOPY INDEXIDList,
x_process_person OUT NOCOPY BOOLEAN,
x_process_person_mode OUT NOCOPY VARCHAR2,
x_per_total OUT NOCOPY NUMBER,
x_per_id_count OUT NOCOPY INDEXIDList,
x_per_id_start OUT NOCOPY INDEXIDList,
x_per_id_end OUT NOCOPY INDEXIDList
) IS
CURSOR c_prof_setup (
p_entity_name VARCHAR2
) IS
SELECT 'Y'
FROM hz_entity_attributes e
WHERE e.updated_flag = 'Y'
AND e.entity_name = p_entity_name
AND rownum = 1;
SELECT distinct party_id
FROM hz_organization_profiles
WHERE effective_end_date IS NULL
AND actual_content_source <> G_MISS_ACTUAL_CONTENT_SOURCE
ORDER BY party_id;
SELECT distinct party_id
FROM hz_person_profiles
WHERE effective_end_date IS NULL
AND actual_content_source <> G_MISS_ACTUAL_CONTENT_SOURCE
ORDER BY party_id;
* PRIVATE PROCEDURE CreateUpdatePartyProfiles
*
* DESCRIPTION
* Submit sub-requests to process records in party profile.s
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* IN:
* p_entity_name Entity name.
* p_create_update_flag C/U flag for create / update.
* p_commit_size Commit size.
* p_party_per_worker Number of records per worker.
* p_id_count Number of records.
* p_id_start Start position.
* p_id_end End position.
*
* MODIFICATION HISTORY
*
* 04-30-2002 Jianying Huang o Created.
*/
PROCEDURE CreateUpdatePartyProfiles (
p_entity_name IN VARCHAR2,
p_create_update_flag IN VARCHAR2,
p_commit_size IN NUMBER,
p_party_per_worker IN NUMBER,
p_id_count IN INDEXIDList,
p_id_start IN INDEXIDList,
p_id_end IN INDEXIDList
) IS
l_subtotal NUMBER := 0;
'AR', 'HZ_THIRD_PARTY_UPDATE_SUB', '',
SYSDATE, FALSE,
p_create_update_flag||','||p_entity_name,
TO_CHAR(l_start), TO_CHAR(l_end),
TO_CHAR(p_commit_size));
END CreateUpdatePartyProfiles;
SELECT 'Y'
FROM hz_entity_attributes e
WHERE e.updated_flag = 'Y'
AND e.entity_name = p_entity_name;
SELECT 'Y'
FROM hz_entity_attributes e
WHERE e.updated_flag = 'Y'
AND rownum = 1;
SELECT 'Y'
FROM hz_entity_attributes e
WHERE e.updated_flag = 'Y'
AND e.entity_name IN ('HZ_ORGANIZATION_PROFILES','HZ_PERSON_PROFILES')
AND rownum = 1;
SELECT 'Y'
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.updated_flag = 'Y'
AND e.entity_name NOT IN ('HZ_ORGANIZATION_PROFILES','HZ_PERSON_PROFILES')
AND rownum = 1;
SELECT UNIQUE e.entity_name
FROM hz_entity_attributes e
WHERE e.updated_flag = 'Y'
-- SSM SST Integration and Extension
-- Only profile entities will be passed for policy function.
AND e.attribute_name is not null;
Write_Log('No setup for any entity / attribute has been updated.');
DELETE hz_win_source_exceps
WHERE entity_attr_id IN
(SELECT e.entity_attr_id
FROM hz_entity_attributes e, hz_select_data_sources s
WHERE e.entity_name = 'HZ_ORGANIZATION_PROFILES'
AND s.ranking > 0
AND s.content_source_type = 'USER_ENTERED'
AND e.entity_attr_id = s.entity_attr_id
)
and exception_type='MRR';
DELETE hz_win_source_exceps
WHERE entity_attr_id IN
(SELECT e.entity_attr_id
FROM hz_entity_attributes e, hz_select_data_sources s
WHERE e.entity_name = 'HZ_PERSON_PROFILES'
AND s.ranking > 0
AND s.content_source_type = 'USER_ENTERED'
AND e.entity_attr_id = s.entity_attr_id
)
and exception_type='MRR';
CreateUpdatePartyProfiles (
'HZ_ORGANIZATION_PROFILES',
l_process_org_mode,
l_commit_size,
l_org_party_per_worker,
l_org_id_count,
l_org_id_start,
l_org_id_end);
CreateUpdatePartyProfiles (
'HZ_PERSON_PROFILES',
l_process_person_mode,
l_commit_size,
l_per_party_per_worker,
l_per_id_count,
l_per_id_start,
l_per_id_end);
ResetUpdatedFlag;
Write_Log('HZ_MIXNM_CONC_DYNAMIC_PKG.BulkUpdateOrgSST('||l_from_id||', '||l_to_id||', '||l_commit_size||');');
HZ_MIXNM_CONC_DYNAMIC_PKG.BulkUpdateOrgSST(l_from_id, l_to_id, l_commit_size);
Write_Log('HZ_MIXNM_CONC_DYNAMIC_PKG.BulkUpdatePerSST('||l_from_id||', '||l_to_id||', '||l_commit_size||');');
HZ_MIXNM_CONC_DYNAMIC_PKG.BulkUpdatePersonSST(l_from_id, l_to_id, l_commit_size);
select e.entity_attr_id, s.content_source_type
from hz_entity_attributes e, hz_select_data_sources s
where s.ranking = -1
AND e.entity_name = p_entity_name
AND e.entity_attr_id = s.entity_attr_id
AND (s.content_source_type= p_data_source_type or
(s.content_source_type='USER_ENTERED' AND
NOT EXISTS(select 'Y' from hz_select_data_sources s1
where s1.ranking = -1 and s1.content_source_type = p_data_source_type
and s1.entity_attr_id = e.entity_attr_id)))
-- Bug 4244112 : populate only if does not exist
AND NOT EXISTS(select 'Y' from hz_win_source_exceps
where party_id = p_party_id
and entity_attr_id = e.entity_attr_id);
TYPE SOURCE_List IS TABLE OF hz_select_data_sources.content_source_type%TYPE;
INSERT INTO hz_win_source_exceps (
party_id,
entity_attr_id,
content_source_type,
exception_type,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by
) VALUES (
p_party_id,
I_ATTR_ID(i),
I_SOURCE(i),
'MRR',
hz_utility_v2pub.created_by,
SYSDATE,
hz_utility_v2pub.last_update_login,
SYSDATE,
hz_utility_v2pub.last_updated_by );
cursor update_allowed is
select v.orig_system_name
from hz_thirdparty_rule t, hz_orig_systems_vl v
where t.entity_attr_id = p_entity_attr_id
and t.orig_system = v.orig_system
and t.overwrite_flag = 'Y';
for content_source in update_allowed loop
IF LENGTHB(l_str) > 225 THEN
l_str := l_str || '....';
select v.orig_system_name
from hz_user_overwrite_rules u, hz_orig_systems_vl v
where u.entity_attr_id = p_entity_attr_id
and u.orig_system = v.orig_system
and u.overwrite_flag = 'Y'
and u.rule_id = p_rule_id;
select attribute_name
from hz_entity_attributes
where attribute_group_name = p_group
and entity_name = p_entity;