[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT REPLACE(l_frmt_actual_content_source,':','_COLON_')
INTO l_frmt_actual_content_source
FROM DUAL;
SELECT REPLACE(l_frmt_actual_content_source,'.','_PERIOD_')
INTO l_frmt_actual_content_source
FROM DUAL;
SELECT REPLACE(l_frmt_actual_content_source,'-','_HYPHEN_')
INTO l_frmt_actual_content_source
FROM DUAL;
SELECT substr(l_frmt_actual_content_source,1,25)||substr(rn,1,3)
INTO l_frmt_actual_content_source
FROM
(SELECT ROWNUM rn,orig_system os FROM hz_orig_systems_b ) a
WHERE os = p_actual_content_source;
PROCEDURE getBulkUpdateWhereCondition
IS
BEGIN
li(' WHERE sst.'||'party_id BETWEEN p_from_party_id AND p_to_party_id');
END getBulkUpdateWhereCondition;
PROCEDURE getImportUpdateWhereCondition
IS
BEGIN
li(' WHERE sst.'||'party_id = sg.party_id ');
END getImportUpdateWhereCondition;
select aa.argument_name, aa.data_type, aa.data_length, 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_entity_name)||'_REC'
and a.type_subname = upper (p_entity_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 = l_apps_schema
and b.object_type = 'PACKAGE')) temp1, (
--Bug 15893080 - Remove direct access from all_tab_columns
/*
select column_name
from sys.all_tab_columns c
where c.table_name = 'HZ_PARTIES'
and c.owner = l_ar_schema
and exists (
select null
from sys.all_tab_columns c2
where c2.owner = l_ar_schema
and c2.column_name = c.column_name
and c2.table_name = 'HZ_' ||upper (p_entity_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')
*/
select col.column_name
from user_synonyms syn
, dba_tab_columns col
where syn.synonym_name = 'HZ_PARTIES'
and col.owner = syn.table_owner
and col.table_name = syn.table_name
and exists (
select null
from user_synonyms syn
, dba_tab_columns col2
where col2.owner = syn.table_owner
and col2.table_name = syn.table_name
and col.column_name = col2.column_name
and syn.synonym_name = 'HZ_' ||upper (p_entity_name) || '_PROFILES')
and col.column_name not like 'ATTRIBUTE%'
and col.column_name not like 'GLOBAL_ATTRIBUTE%'
and col.column_name not in ('APPLICATION_ID')
) party
where aa.object_name = 'GET_' ||upper (p_entity_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 = l_apps_schema
and b.object_type = 'PACKAGE')
and aa.argument_name = party.column_name (+)
union all
select 'BANK_CODE' argument_name, 'VARCHAR2' data_type,
30 data_length, null column_name
from dual
union all
select 'BANK_OR_BRANCH_NUMBER' argument_name, 'VARCHAR2' data_type,
60 data_length, null column_name
from dual
union all
select 'BRANCH_CODE' argument_name, 'VARCHAR2' data_type,
30 data_length, null column_name
from dual
order by argument_name;
SELECT attribute_group_name, attribute_name, entity_attr_id
FROM hz_entity_attributes
WHERE attribute_name IS NOT NULL
ORDER BY attribute_group_name;
li(p_prefix||' write_log(SQL%ROWCOUNT||'' records have been created / updated.'');');
p_insert_update_flag IN VARCHAR2,
p_option IN VARCHAR2,
p_prefix IN VARCHAR2,
p_extended_who_flag IN VARCHAR2
) IS
BEGIN
IF p_insert_update_flag = 'I' THEN
IF p_option = 'LIST' THEN
l(p_prefix||'created_by,');
l(p_prefix||'last_update_login,');
l(p_prefix||'last_update_date,');
ll(p_prefix||'last_updated_by');
ll(p_prefix||'program_update_date');
l(p_prefix||'g_last_update_login,');
ll(p_prefix||'g_last_updated_by');
ELSE -- IF p_insert_update_flag = 'I' THEN
l(p_prefix||'last_updated_by = g_last_updated_by,');
l(p_prefix||'last_update_login = g_last_update_login,');
ll(p_prefix||'last_update_date = SYSDATE');
ll(p_prefix||'program_update_date = SYSDATE');
* update_exception_table_date. This
* procedure is to populate exception
* table for 'By Date' attributes.
*/
PROCEDURE Gen_CommonProceduresForConc IS
l_procedure_name VARCHAR2(30);
l(fp('g_last_update_login')||'NUMBER;');
l(fp('g_last_updated_by')||'NUMBER;');
li('g_last_update_login := hz_utility_v2pub.last_update_login;');
li('g_last_updated_by := hz_utility_v2pub.last_updated_by;');
+ update_rel_party_name
+===============================================================*/
l_procedure_name := 'update_rel_party_name';
li(' SELECT r.party_id, r.object_id, o.party_name, r.subject_id, s.party_name');
li(' UPDATE hz_parties');
+ update_exception_table
+===============================================================*/
l_procedure_name := 'update_exception_table';
li(fp('p_create_update_flag')||'IN VARCHAR2,');
li('IF p_create_update_flag = ''C'' THEN');
li(' INSERT INTO hz_win_source_exceps (');
li(' DELETE hz_win_source_exceps');
li(' write_log(''Deleted ''||sql%ROWCOUNT||'' exceptions.'');');
li(' UPDATE hz_win_source_exceps');
li(' write_log(''Updated ''||sql%ROWCOUNT||'' exceptions.'');');
li(' INSERT INTO hz_win_source_exceps (');
li(' SELECT');
li(' SELECT ''Y''');
+ SSM SST Project : update_exception_table for Date Attributes
+===============================================================*/
l_procedure_name := 'update_exception_table_date';
li(fp('p_create_update_flag')||'IN VARCHAR2,');
li('IF p_create_update_flag = ''C'' THEN');
li(' INSERT INTO hz_win_source_exceps (');
li(' UPDATE hz_win_source_exceps');
li(' INSERT INTO hz_win_source_exceps (');
li(' write_log(''Updated ''||sql%ROWCOUNT||'' exceptions.'');');
li(' write_log(''update party name of relationship parties.'');');
li(' update_rel_party_name(i_party_id(i));');
PROCEDURE Gen_WriteOutputFileForUpdate IS
BEGIN
li(' FOR i IN create_start..create_end LOOP');
li(' FOR i IN update_start..update_end LOOP');
li(' write_out(''updated : party id = ''||i_party_id(i)||'', sst profile id = ''||i_profile_id(i));');
END Gen_WriteOutputFileForUpdate;
* Select the attribute and corresponding actual_content_source
* from the last updated profile among the visible data
* sources for this attribute.
*
CURSOR c_KNOWN_AS5 IS
SELECT user_entered.known_as5, user_entered.actual_content_source
FROM ( SELECT row_number() over
(partition by ue.party_id order by ue.last_update_date desc nulls last) rank,
ue.known_as5 , ue.actual_content_source
FROM hz_organization_profiles ue
WHERE ue.party_id BETWEEN p_from_party_id AND p_to_party_id
AND ue.effective_end_date IS NULL
AND ue.actual_content_source in ( 'DNB' ,'USER_ENTERED' ...)
AND NOT EXISTS (
USER_ENTERED profile
AND EXISTS (
NON SST, USER_ENTERED profile
) user_entered
WHERE user_entered.rank = 1
* MODIFICATION HISTORY
*
* 17-12-2004 Dhaval Metha SSM SST Project
* ~ Created.
*/
procedure date_cursor_create(attr_id IN NUMBER,
attr_name IN VARCHAR2,
p_entity_name IN VARCHAR2,
p_purpose IN VARCHAR2) IS
cursor data_sources is
select content_source_type
from hz_select_data_sources
where entity_attr_id = attr_id
and ranking <> 0;
li(' SELECT ');
li('( SELECT ');
ll(' (partition by ue.party_id order by ue.last_update_date desc nulls last) rank, ');
li(' SELECT ''Y''');
li(' SELECT ''Y''');
* PRIVATE PROCEDURE date_cursor_update
*
* DESCRIPTION
* This procedure is used to generate cursor for
* passed attribute_id setup as 'By Date' for
* BulkUpdateSST and ImportUpdateSST procedures.
* Select the attribute and corresponding actual_content_source
* from the last updated profile among the visible data
* sources for this attribute.
*
CURSOR c_KNOWN_AS5 IS
SELECT user_entered.known_as5, user_entered.actual_content_source
FROM ( SELECT decode(fnd_profile.value ('HZ_PROFILE_VERSION'),'NEW_VERSION','C',
'NO_VERSION','U',
decode(trunc(ue.effective_start_date),trunc(sysdate),'U','C')) create_update_flag,
ue.organization_profile_id,
ue.party_id,row_number() over
(partition by ue.party_id order by ue.last_update_date desc nulls last) rank,
ue.known_as5 , ue.actual_content_source
FROM hz_organization_profiles ue
WHERE ue.party_id BETWEEN p_from_party_id AND p_to_party_id
AND ue.effective_end_date IS NULL
AND ue.actual_content_source in ( 'DNB' ,'USER_ENTERED' ...)
AND EXISTS (
USER_ENTERED profile
AND EXISTS (
SST profile
) user_entered
WHERE user_entered.rank = 1
ORDER BY create_update_flag
* MODIFICATION HISTORY
*
* 17-12-2004 Dhaval Metha SSM SST Project
* ~ Created.
*/
procedure date_cursor_update(attr_id IN NUMBER,
attr_name IN VARCHAR2,
p_entity_name IN VARCHAR2,
p_purpose IN VARCHAR2) IS
cursor data_sources is
select content_source_type
from hz_select_data_sources
where entity_attr_id = attr_id
and ranking <> 0;
li(' SELECT ');
li(' SELECT decode(fnd_profile.value (''HZ_PROFILE_VERSION''),''NEW_VERSION'',''C'',''NO_VERSION'',''U'',decode(trunc(sst.effective_start_date),trunc(sysdate),''U'',''C'')) create_update_flag, ');
ll(' (partition by ue.party_id order by ue.last_update_date desc nulls last) rank, ');
li(' SELECT ''Y''');
li('ORDER BY create_update_flag,user_entered.party_id;');
END date_cursor_update;
* and use them in same insert statement as
* 'By Rank' attributes
* ~ populate the exception table for
* 'By Date' attributes.
*/
PROCEDURE Gen_BulkCreateSST (
p_entity_name IN VARCHAR2,
p_purpose IN VARCHAR2
) IS
CURSOR c_restricted_attributes IS
SELECT e.attribute_name, e.entity_attr_id,
e.group_flag, s.content_source_type
FROM
(SELECT e.attribute_name, e.entity_attr_id, 'N' group_flag
FROM hz_entity_attributes e
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
UNION
SELECT e.attribute_group_name,
MIN(entity_attr_id) entity_attr_id,
'Y' group_flag
FROM hz_entity_attributes e
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND NOT EXISTS (
SELECT 'Y'
FROM hz_entity_attributes e1
WHERE e.attribute_group_name = e1.attribute_name)
GROUP BY e.attribute_group_name) e,
hz_select_data_sources s
WHERE EXISTS (
SELECT 'Y'
FROM hz_select_data_sources s1
WHERE s1.entity_attr_id = e.entity_attr_id
AND s1.content_source_type <> 'USER_ENTERED'
AND s1.ranking > 0 )
AND s.entity_attr_id = e.entity_attr_id
AND s.ranking > 0
ORDER BY e.attribute_name, s.ranking;
SELECT UNIQUE s.content_source_type
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND e.entity_attr_id = s.entity_attr_id
AND s.ranking > 0;
SELECT e.attribute_group_name, e.attribute_name
FROM hz_entity_attributes e,
(SELECT UNIQUE attribute_group_name
FROM hz_entity_attributes
WHERE entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
GROUP BY attribute_group_name
HAVING COUNT(*) > 1) e1
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND e.attribute_group_name = e1.attribute_group_name
AND e.attribute_group_name <> e.attribute_name
AND EXISTS (
SELECT 'Y'
FROM hz_select_data_sources s1
WHERE s1.entity_attr_id = e.entity_attr_id
AND s1.content_source_type <> 'USER_ENTERED'
AND s1.ranking > 0 );
SELECT distinct e.attribute_name, e.entity_attr_id
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND EXISTS (
SELECT 'Y'
FROM hz_select_data_sources s1
WHERE s1.entity_attr_id = e.entity_attr_id
AND s1.ranking < 0 )
AND s.entity_attr_id = e.entity_attr_id
AND s.ranking < 0
ORDER BY e.attribute_name;
+ select for restricted columns (i.e. columns have been selected)
+
+ SELECT ...
+ --
+ -- processing branch_flag.
+ DECODE(dnb.branch_flag, NULL,
+ DECODE(user_entered.branch_flag, NULL,
+ NULL,
+ user_entered.branch_flag||'%#USER_ENTERED'),
+ dnb.branch_flag||'%#DNB') branch_flag,
+ --
+ -- processing ceo_name.
+ DECODE(user_entered.ceo_name, NULL,
+ DECODE(dnb.ceo_name, NULL,
+ NULL,
+ dnb.ceo_name||'%#DNB'),
+ user_entered.ceo_name||'%#USER_ENTERED') ceo_name,
+ --
+ ...
+===============================================================*/
l_cursor_name := 'c_entity';
li(' SELECT d_user_entered.'||p_entity_name||'_profile_id,');
+ select normal columns (i.e. columns not in setup tables and
+ columns not selected)
+
+ --
+ -- processing attribute1.
+ user_entered.attribute1,
+ --
+ -- processing attribute2.
+ user_entered.attribute2,
+ ...
+===============================================================*/
FOR i IN 1..i_normal_attributes.COUNT LOOP
l(',');
li(' SELECT ''Y''');
li(' SELECT ''Y''');
li(' UPDATE hz_'||p_entity_name||'_profiles ');
+ select attributes inside an attribute group
+===============================================================*/
OPEN c_groups;
li(' SELECT');
li(' -- insert sst profiles');
li(' INSERT INTO hz_'||p_entity_name||'_profiles (');
+ column list of insert statement
+
+ duns_number_c,
+ employees_total,
+ ...
+===============================================================*/
FOR i IN 1..i_uq_attribute_name.COUNT LOOP
l(',');
+ value list of insert statement
+
+ SUBSTRB(i_duns_number_c(i),1,INSTRB(i_duns_number_c(i),'%#')-1),
+ TO_NUMBER(SUBSTRB(i_employees_total(i),1, INSTRB(i_employees_total(i),'%#')-1)),
+ ...
+===============================================================*/
Gen_CodeForWhoColumns('I','',g_indent||' ','Y');
li(' write_log(''update denormalized columns in hz_parties.'');');
li(' -- update denormalized columns in hz_parties.');
li(' UPDATE hz_parties');
+ update party_name, customer_key in hz_parties
+===============================================================*/
IF l_has_party_name THEN
Gen_PartyName (p_entity_name);
+ update party_name of relationship parties
+===============================================================*/
IF l_has_party_name THEN
Gen_RelationshipPartyName;
+ update exception table to track attributes' data source
+===============================================================*/
li(' write_log(''update exception table.'');');
li(' update_exception_table(''C'','||
'i_party_id,'''||i_uq_attribute_name(i)||''','||
i_uq_entity_attr_id(i)||','||
'i_'||Format_AttributeName(i_uq_attribute_name(i))||','''||
i_uq_winner_source(i)||''');');
li(' update_exception_table_date(''C'','||
'i_party_id,'''||i_attribute_name_date(i)||''','||
i_entity_attr_id_date(i)||','||
Format_AttributeName(i_attribute_name_date(i))||');');
li(' delete hz_user_overwrite_rules where entity_attr_id = ' || i_entity_attr_id_date(i) || ' ;');
+ update DQM interface table
+
+ As per talk with Srini, right now DQM can not handle huge
+ data in interface table. User should run DQM sync program
+ after they run this program for mix-n-match.
+===============================================================*/
/*
IF hz_dqm_search_util.is_dqm_available = 'T' THEN
Gen_CodeForDQM(p_entity_name);
* and use them in same insert statement as
* 'By Rank' attributes
* ~ populate the exception table for
* 'By Date' attributes.
*/
PROCEDURE Gen_BulkUpdateSST (
p_entity_name IN VARCHAR2,
p_purpose IN VARCHAR2
) IS
CURSOR c_restricted_attributes IS
SELECT e.attribute_name, e.entity_attr_id,
e.group_flag, s.content_source_type
FROM
(SELECT e.attribute_name, e.entity_attr_id, 'N' group_flag
FROM hz_entity_attributes e
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND e.updated_flag = 'Y'
UNION
SELECT e.attribute_group_name,
MIN(entity_attr_id) entity_attr_id,
'Y' group_flag
FROM hz_entity_attributes e
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND e.updated_flag = 'Y'
AND NOT EXISTS (
SELECT 'Y'
FROM hz_entity_attributes e1
WHERE e.attribute_group_name = e1.attribute_name)
GROUP BY e.attribute_group_name) e,
hz_select_data_sources s
WHERE /*EXISTS (
SELECT 'Y'
FROM hz_select_data_sources s1
WHERE s1.entity_attr_id = e.entity_attr_id
AND s1.content_source_type <> 'USER_ENTERED'
AND s1.ranking > 0 )
AND */ s.entity_attr_id = e.entity_attr_id
AND s.ranking > 0
ORDER BY e.attribute_name, s.ranking;
SELECT e.attribute_name, e.entity_attr_id,
e.group_flag, s.content_source_type
FROM
(SELECT e.attribute_name, e.entity_attr_id, 'N' group_flag
FROM hz_entity_attributes e
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
UNION
SELECT e.attribute_group_name,
MIN(entity_attr_id) entity_attr_id,
'Y' group_flag
FROM hz_entity_attributes e
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND NOT EXISTS (
SELECT 'Y'
FROM hz_entity_attributes e1
WHERE e.attribute_group_name = e1.attribute_name)
GROUP BY e.attribute_group_name) e,
hz_select_data_sources s
WHERE /*EXISTS (
SELECT 'Y'
FROM hz_select_data_sources s1
WHERE s1.entity_attr_id = e.entity_attr_id
AND s1.content_source_type <> 'USER_ENTERED'
AND s1.ranking > 0 )
AND*/ s.entity_attr_id = e.entity_attr_id
AND s.ranking > 0
ORDER BY e.attribute_name, s.ranking;
SELECT UNIQUE s.content_source_type
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND e.updated_flag = 'Y'
AND e.entity_attr_id = s.entity_attr_id
AND s.ranking > 0;
SELECT UNIQUE s.content_source_type
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND e.entity_attr_id = s.entity_attr_id
AND s.ranking > 0;
CURSOR c_deselected_sources IS
SELECT s.entity_attr_id, s.content_source_type
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND e.updated_flag = 'Y'
AND e.entity_attr_id = s.entity_attr_id
AND s.ranking = 0
ORDER BY s.entity_attr_id;
i_deselected_sources INDEXVARCHAR30List;
SELECT e.attribute_group_name, e.attribute_name
FROM hz_entity_attributes e,
(SELECT UNIQUE attribute_group_name
FROM hz_entity_attributes
WHERE entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
GROUP BY attribute_group_name
HAVING COUNT(*) > 1) e1
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND e.attribute_group_name = e1.attribute_group_name
AND e.attribute_group_name <> e.attribute_name
AND e.updated_flag = 'Y'
AND EXISTS (
SELECT 'Y'
FROM hz_select_data_sources s1
WHERE s1.entity_attr_id = e.entity_attr_id
-- AND s1.content_source_type <> 'USER_ENTERED'
AND s1.ranking > 0 )
;
SELECT e.attribute_group_name, e.attribute_name
FROM hz_entity_attributes e,
(SELECT UNIQUE attribute_group_name
FROM hz_entity_attributes
WHERE entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
GROUP BY attribute_group_name
HAVING COUNT(*) > 1) e1
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND e.attribute_group_name = e1.attribute_group_name
AND e.attribute_group_name <> e.attribute_name
AND EXISTS (
SELECT 'Y'
FROM hz_select_data_sources s1
WHERE s1.entity_attr_id = e.entity_attr_id
-- AND s1.content_source_type <> 'USER_ENTERED'
AND s1.ranking > 0 );
SELECT distinct e.attribute_name, e.entity_attr_id
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND EXISTS (
SELECT 'Y'
FROM hz_select_data_sources s1
WHERE s1.entity_attr_id = e.entity_attr_id
AND s1.ranking < 0 )
AND s.entity_attr_id = e.entity_attr_id
AND e.updated_flag = 'Y'
AND s.ranking < 0
ORDER BY e.attribute_name;
SELECT distinct e.attribute_name, e.entity_attr_id
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND EXISTS (
SELECT 'Y'
FROM hz_select_data_sources s1
WHERE s1.entity_attr_id = e.entity_attr_id
AND s1.ranking < 0 )
AND s.entity_attr_id = e.entity_attr_id
AND s.ranking < 0
ORDER BY e.attribute_name;
l_procedure_name := p_purpose || 'UpdateOrgSST';
l_procedure_name := p_purpose || 'UpdatePersonSST';
li(fp('i_create_update_flag')||'INDEXVARCHARlist;');
li(fp('update_start')||'NUMBER := 0;');
li(fp('update_end')||'NUMBER := 0;');
+ select for restricted columns (i.e. columns have been selected)
+
+ SELECT ...
+ --
+ -- processing branch_flag.
+ DECODE(dnb.branch_flag, NULL,
+ DECODE(user_entered.branch_flag, NULL,
+ NULL,
+ user_entered.branch_flag||'%#USER_ENTERED'),
+ dnb.branch_flag||'%#DNB') branch_flag,
+ --
+ -- processing ceo_name.
+ DECODE(user_entered.ceo_name, NULL,
+ DECODE(dnb.ceo_name, NULL,
+ NULL,
+ dnb.ceo_name||'%#DNB'),
+ user_entered.ceo_name||'%#USER_ENTERED') ceo_name,
+ --
+ ...
+===============================================================*/
l_cursor_name := 'c_entity';
li(' SELECT /*+ leading(i) use_nl(sg) index(sg,hz_imp_parties_sg_n1) */');
li('decode(fnd_profile.value (''HZ_PROFILE_VERSION''),''NEW_VERSION'',''C'',''NO_VERSION'',''U'',decode(trunc(sst.effective_start_date),trunc(sysdate),''U'',''C'')) create_update_flag,');
+ select normal columns (i.e. columns not in setup tables and
+ columns not selected)
+
+ --
+ -- processing attribute1.
+ sst.attribute1,
+ --
+ -- processing attribute2.
+ sst.attribute2,
+ ...
+===============================================================*/
FOR i IN 1..i_normal_attributes.COUNT LOOP
l(',');
getBulkUpdateWhereCondition;
getImportUpdateWhereCondition;
li(' SELECT ''Y''');
li('ORDER BY create_update_flag, sst.party_id;');
date_cursor_update(i_entity_attr_id_date(i),
i_attribute_name_date(i),
p_entity_name,
p_purpose);
li(' SELECT ''Y'' ');
li(' WHERE e.updated_flag = ''Y'' ');
li(' i_create_update_flag,');
+ select attributes inside an attribute group
+===============================================================*/
IF ( UPPER(p_purpose) = 'BULK') THEN
OPEN c_groups;
li(' SELECT');
+ put profile ids into 2 table. 1 for create and another for update
+===============================================================*/
li(' create_start := 0; create_end := -1; ');
li(' update_start := 0; update_end := -1; ');
li(' IF i_create_update_flag(i) = ''C'' THEN');
li(' IF update_start = 0 THEN ');
li(' update_start := i; ');
li(' IF update_start > 0 AND update_end = -1 THEN update_end := subtotal; END IF;');
li(' write_log((update_end - update_start)||'' record(s) need to be updated.'');');
li(' UPDATE hz_'||p_entity_name||'_profiles ');
li(' -- insert sst profiles');
li(' INSERT INTO hz_'||p_entity_name||'_profiles (');
+ column list of insert statement
+
+ duns_number_c,
+ employees_total,
+ ...
+===============================================================*/
FOR i IN 1..i_uq_attribute_name.COUNT LOOP
l(',');
+ value list of insert statement
+
+ SUBSTRB(i_duns_number_c(i),1,INSTRB(i_duns_number_c(i),'%#')-1),
+ TO_NUMBER(SUBSTRB(i_employees_total(i),1, INSTRB(i_employees_total(i),'%#')-1)),
+ ...
+===============================================================*/
Gen_CodeForWhoColumns('I','',g_indent||' ','Y');
--In ImportUpdatePersonSST procedure person_name update is added.
IF UPPER(p_purpose) = 'IMPORT' AND UPPER(p_entity_name) = 'PERSON' THEN
l('');
li(' UPDATE hz_person_profiles');
+ update sst profiles
+===============================================================*/
li(' write_log(''update sst profiles.'');');
li(' -- update sst profiles');
li(' FORALL i IN update_start..update_end');
li(' UPDATE hz_'||p_entity_name||'_profiles');
--In ImportUpdatePersonSST API person_name update is added.
IF UPPER(p_purpose) = 'IMPORT' AND UPPER(p_entity_name) = 'PERSON' THEN
l('');
li(' FORALL i IN update_start..update_end');
li(' UPDATE hz_person_profiles');
li(' IF (update_end-update_start) > 0 THEN');
li(' write_log(''update denormalized columns in hz_parties.'');');
li(' -- update denormalized columns in hz_parties.');
li(' UPDATE hz_parties');
+ update party_name, customer_key in hz_parties
+===============================================================*/
IF l_has_party_name THEN
Gen_PartyName (p_entity_name);
--In ImportUpdatePersonSST API party_name update is added.
IF UPPER(p_purpose) = 'IMPORT' AND UPPER(p_entity_name) = 'PERSON' THEN
l('');
li(' UPDATE hz_parties');
+ update party_name of relationship parties
+===============================================================*/
IF l_has_party_name THEN
Gen_RelationshipPartyName;
+ update exception table to track attributes' data source
+===============================================================*/
li(' write_log(''update exception table.'');');
li(' -- We need to delete only if there is setup change. If no setup change');
li(' -- and call is from regular processing API, then no DELETE is required');
li(' Write_Log(''NO setup for any entity / attribute has been updated. DELETE hz_win_source_exceps call not required.''); ');
li(' DELETE hz_win_source_exceps ');
OPEN c_deselected_sources;
FETCH c_deselected_sources BULK COLLECT INTO i_deentity_attr_id, i_deselected_sources;
CLOSE c_deselected_sources;
/* Bug 4228765 : Donot delete MRR records here as it is
* deleted in main conc program earlier
li(' DELETE hz_win_source_exceps ');
li(' update_exception_table(''U'','||
'i_party_id,'''||i_uq_attribute_name(i)||''','||
i_uq_entity_attr_id(i)||','||
'i_'||Format_AttributeName(i_uq_attribute_name(i))||','''||
i_uq_winner_source(i)||''');');
li(' DELETE hz_win_source_exceps ');
str1 := str1||''''||i_deselected_sources(i)||'''';
li(' update_exception_table_date(''U'','||
'i_party_id,'''||i_attribute_name_date(i)||''','||
i_entity_attr_id_date(i)||','||
Format_AttributeName(i_attribute_name_date(i))||');');
li(' delete hz_user_overwrite_rules where entity_attr_id = ' || i_entity_attr_id_date(i)||' ;');
Gen_WriteOutputFileForUpdate;
+ update DQM interface table
+
+ As per talk with Srini, right now DQM can not handle huge
+ data in interface table. User should run DQM sync program
+ after they run this program for mix-n-match.
+===============================================================*/
/*
IF hz_dqm_search_util.is_dqm_available = 'T' THEN
Gen_CodeForDQM(p_entity_name);
li('write_out(total||'' sst profile(s) was(were) updated.'');');
li('write_log(total||'' sst profile(s) was(were) successfully updated.'');');
END Gen_BulkUpdateSST;
PROCEDURE Gen_UpdateSSTRecord (
p_entity_name IN VARCHAR2
) IS
CURSOR c_restricted_attributes IS
SELECT e.attribute_name, e.entity_attr_id,
s.content_source_type
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND e.attribute_name NOT IN ('BANK_CODE','BANK_OR_BRANCH_NUMBER','BRANCH_CODE')
AND EXISTS (
SELECT 'Y'
FROM hz_select_data_sources s1
WHERE s1.entity_attr_id = e.entity_attr_id
AND s1.content_source_type <> 'USER_ENTERED'
AND s1.ranking > 0 )
AND s.entity_attr_id = e.entity_attr_id
AND s.ranking > 0
ORDER BY e.attribute_name, s.ranking;
SELECT UNIQUE s.content_source_type
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND e.attribute_name NOT IN ('BANK_CODE','BANK_OR_BRANCH_NUMBER','BRANCH_CODE')
AND e.entity_attr_id = s.entity_attr_id
AND s.ranking > 0;
SELECT e.attribute_group_name,
DECODE(e.attribute_group_name, 'PERSON_NAME',
DECODE(e1.attribute_name, 'PERSON_LAST_NAME',
e1.entity_attr_id, null),
e1.attribute_name,
e1.entity_attr_id, null) primary_entity_attr_id,
e1.attribute_name,
e1.entity_attr_id
FROM
(SELECT attribute_group_name
FROM hz_entity_attributes
WHERE entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
GROUP BY attribute_group_name
HAVING COUNT(entity_attr_id) > 1
) e,
hz_entity_attributes e1
WHERE e1.attribute_group_name = e.attribute_group_name
AND e1.attribute_name NOT IN ('BANK_CODE','BANK_OR_BRANCH_NUMBER','BRANCH_CODE')
AND EXISTS (
SELECT 'Y'
FROM hz_select_data_sources s1
WHERE s1.entity_attr_id = e1.entity_attr_id
AND s1.content_source_type <> 'USER_ENTERED'
AND s1.ranking <> 0 )
ORDER BY e.attribute_group_name, primary_entity_attr_id;
SELECT distinct e.attribute_name, e.entity_attr_id
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
AND e.attribute_name NOT IN ('BANK_CODE','BANK_OR_BRANCH_NUMBER','BRANCH_CODE')
AND EXISTS (
SELECT 'Y'
FROM hz_select_data_sources s1
WHERE s1.entity_attr_id = e.entity_attr_id
AND s1.ranking < 0 )
AND s.entity_attr_id = e.entity_attr_id
AND s.ranking < 0
ORDER BY e.attribute_name;
SELECT e.attribute_group_name,
DECODE(e.attribute_group_name, 'PERSON_NAME',
DECODE(e1.attribute_name, 'PERSON_LAST_NAME',
e1.entity_attr_id, null),
e1.attribute_name,
e1.entity_attr_id, null) primary_entity_attr_id,
e1.attribute_name,
e1.entity_attr_id
FROM
(SELECT attribute_group_name
FROM hz_entity_attributes
WHERE entity_name = 'HZ_'||UPPER(p_entity_name)||'_PROFILES'
GROUP BY attribute_group_name
HAVING COUNT(entity_attr_id) > 1
) e,
hz_entity_attributes e1
WHERE e1.attribute_group_name = e.attribute_group_name
AND EXISTS (
SELECT 'Y'
FROM hz_select_data_sources s1
WHERE s1.entity_attr_id = e1.entity_attr_id
AND s1.ranking < 0 )
ORDER BY e.attribute_group_name, primary_entity_attr_id;
l_procedure_name := 'selectOrgHighestRankedValue';
l_procedure_name := 'selectPerHighestRankedValue';
li('SELECT ');
li(fp('p_create_update_flag')||'IN VARCHAR2,');
li('IF p_create_update_flag = ''C'' THEN');
li(fp('p_updateable_flag_list')||'IN OUT NOCOPY HZ_MIXNM_UTILITY.INDEXVARCHAR1List,');
li(' p_updateable_flag_list('||i_group_entity_attr_id(j)||') := ''N'';');
li('IF p_updateable_flag_list.EXISTS('||i_group_entity_attr_id(i)||') AND');
li(' p_updateable_flag_list('||i_group_entity_attr_id(i)||') = ''Y''');
li(' p_updateable_flag_list('||i_group_entity_attr_id(i)||') := ''Y'';');
li('IF p_updateable_flag_list.EXISTS('||i_uq_entity_attr_id(i)||') AND');
li(' p_updateable_flag_list('||i_uq_entity_attr_id(i)||') = ''Y''');
li('IF p_updateable_flag_list.EXISTS('||i_entity_attr_id_date(i)||') AND');
li(' p_updateable_flag_list('||i_entity_attr_id_date(i)||') = ''Y''');
l_procedure_name := 'updateSSTRecord';
li(fp('p_create_update_flag')||'IN VARCHAR2,');
li(fp('p_updateable_flag_list')||'IN OUT NOCOPY HZ_MIXNM_UTILITY.INDEXVARCHAR1List,');
li(' p_updateable_flag_list.COUNT > 0');
li(' selectOrgHighestRankedValue(');
li(' selectPerHighestRankedValue(');
li('IF p_updateable_flag_list.EXISTS('||i_uq_entity_attr_id(i)||') AND');
li(' p_updateable_flag_list('||i_uq_entity_attr_id(i)||') = ''Y''');
li('IF p_updateable_flag_list.EXISTS('||i_uq_entity_attr_id(l_index1)||') AND');
li(' p_updateable_flag_list('||i_uq_entity_attr_id(l_index1)||') = ''Y'' AND');
li(' (NOT p_updateable_flag_list.EXISTS('||i_uq_entity_attr_id(l_index2)||') OR');
li(' p_updateable_flag_list('||i_uq_entity_attr_id(l_index2)||') = ''N'')');
li(' p_updateable_flag_list('||i_uq_entity_attr_id(l_index2)||') := ''Y'';');
li('IF p_updateable_flag_list.EXISTS('||i_uq_entity_attr_id(l_index2)||') AND');
li(' p_updateable_flag_list('||i_uq_entity_attr_id(l_index2)||') = ''Y'' AND');
li(' (NOT p_updateable_flag_list.EXISTS('||i_uq_entity_attr_id(l_index1)||') OR');
li(' p_updateable_flag_list('||i_uq_entity_attr_id(l_index1)||') = ''N'')');
li(' p_updateable_flag_list('||i_uq_entity_attr_id(l_index1)||') := ''Y'';');
li('IF p_updateable_flag_list.EXISTS('||i_entity_attr_id_date(i)||') AND');
li(' p_updateable_flag_list('||i_entity_attr_id_date(i)||') = ''Y''');
li(' IF p_create_update_flag = ''U'' THEN');
END Gen_UpdateSSTRecord;
Gen_BulkUpdateSST('organization', 'Bulk');
Gen_BulkUpdateSST('person', 'Bulk');
Gen_BulkUpdateSST('organization', 'Import');
Gen_BulkUpdateSST('person', 'Import');
Gen_UpdateSSTRecord('organization');
Gen_UpdateSSTRecord('person');
li(' -- to update interface table so the affected parties');
li(' INSERT INTO hz_dqm_sync_interface (');
li(' SELECT');
li(' SELECT');