[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT b.staged_attribute_column as attrib_column
from HZ_TRANS_ATTRIBUTES_VL a, hz_trans_functions_vl b
where a.entity_name = 'PARTY_SITES'
and nvl(a.denorm_flag,'N') = 'Y'
and a.attribute_id = b.attribute_id
and nvl( b.active_flag, 'Y') = 'Y'
and b.staged_attribute_table = 'HZ_STAGED_PARTY_SITES'
)
LOOP
row_count := row_count + 1 ;
SELECT b.staged_attribute_column as attrib_column
from HZ_TRANS_ATTRIBUTES_VL a, hz_trans_functions_vl b
where a.entity_name = 'CONTACTS'
and nvl(a.denorm_flag,'N') = 'Y'
and a.attribute_id = b.attribute_id
and nvl( b.active_flag, 'Y') = 'Y'
and b.staged_attribute_table = 'HZ_STAGED_CONTACTS'
)
LOOP
row_count := row_count + 1 ;
SELECT b.staged_attribute_column as attrib_column
from HZ_TRANS_ATTRIBUTES_VL a, hz_trans_functions_vl b
where a.entity_name = 'CONTACT_POINTS'
and nvl(a.denorm_flag,'N') = 'Y'
and a.attribute_id = b.attribute_id
and nvl( b.active_flag, 'Y') = 'Y'
and b.staged_attribute_table = 'HZ_STAGED_CONTACT_POINTS'
)
LOOP
row_count := row_count + 1 ;
select prv_value into concat_pref_cols
from ctx_preference_values c
where prv_preference = 'DENORM_PS'
and prv_attribute = 'COLUMNS' ;
SELECT b.staged_attribute_column as attrib_column
from HZ_TRANS_ATTRIBUTES_VL a, hz_trans_functions_vl b
where a.entity_name = 'PARTY_SITES'
and nvl(a.denorm_flag,'N') = 'Y'
and a.attribute_id = b.attribute_id
and nvl( b.active_flag, 'Y') = 'Y'
and b.staged_attribute_table = 'HZ_STAGED_PARTY_SITES'
)
LOOP
-- if any attribute columns do not exist in the preference
IF instr(concat_pref_cols || ' ', col_cur.attrib_column || ' ') = 0
THEN
row_count := row_count + 1 ;
select prv_value into concat_pref_cols
from ctx_preference_values c
where prv_preference = 'DENORM_CT'
and prv_attribute = 'COLUMNS' ;
SELECT b.staged_attribute_column as attrib_column
from HZ_TRANS_ATTRIBUTES_VL a, hz_trans_functions_vl b
where a.entity_name = 'CONTACTS'
and nvl(a.denorm_flag,'N') = 'Y'
and a.attribute_id = b.attribute_id
and nvl( b.active_flag, 'Y') = 'Y'
and b.staged_attribute_table = 'HZ_STAGED_CONTACTS'
)
LOOP
-- if any attribute columns do not exist in the preference
IF instr(concat_pref_cols || ' ', col_cur.attrib_column || ' ') = 0
THEN
row_count := row_count + 1 ;
select prv_value into concat_pref_cols
from ctx_preference_values c
where prv_preference = 'DENORM_CPT'
and prv_attribute = 'COLUMNS' ;
SELECT b.staged_attribute_column as attrib_column
from HZ_TRANS_ATTRIBUTES_VL a, hz_trans_functions_vl b
where a.entity_name = 'CONTACT_POINTS'
and nvl(a.denorm_flag,'N') = 'Y'
and a.attribute_id = b.attribute_id
and nvl( b.active_flag, 'Y') = 'Y'
and b.staged_attribute_table = 'HZ_STAGED_CONTACT_POINTS'
)
LOOP
-- if any attribute columns do not exist in the preference
IF instr(concat_pref_cols || ' ', col_cur.attrib_column || ' ') = 0
THEN
row_count := row_count + 1 ;
SELECT count(1) into l_count
from hz_trans_attributes_vl a
where a.entity_name = p_entity
and exists (
SELECT 1 from hz_trans_functions_vl f
where a.attribute_id = f.attribute_id
and nvl(f.ACTIVE_FLAG,'Y') = 'Y'
and primary_flag = 'Y'
AND NVL(STAGED_FLAG,'N') ='N');
PROCEDURE delete_existing_data IS
l_owner VARCHAR2(255);
select owner into l_owner from sys.all_objects
where object_name = 'HZ_STAGED_PARTIES' and OBJECT_TYPE = 'TABLE' and owner = l_owner1;
select 1 from ctx_preference_values
where prv_preference =
decode(entity,'PARTY','HZ_PARTY_DS',
'PARTY_SITES','HZ_PARTY_SITE_DS',
'CONTACTS','HZ_CONTACT_DS',
'CONTACT_POINTS','HZ_CONTACT_POINT_DS',
'NOMATCH')
AND prv_owner = g_schema_name
AND prv_attribute='COLUMNS'
AND (upper(prv_value) like '%'||p_stg_col||' %'
OR upper(prv_value) like '%'||p_stg_col||'||%');
SELECT 1 FROM hz_trans_functions_vl f
where f.attribute_id = p_attribute_id
and nvl(f.ACTIVE_FLAG,'Y') = 'Y'
and primary_flag = 'Y'
AND NVL(STAGED_FLAG,'N') ='Y';
SELECT ATTRIBUTE_ID
FROM HZ_TRANS_ATTRIBUTES_VL a
WHERE ENTITY_NAME = entity
AND TEMP_SECTION IS NULL
ORDER BY ATTRIBUTE_ID) LOOP
prefattrs := '';
SELECT STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f
WHERE ATTRIBUTE_ID = ATTRS.ATTRIBUTE_ID
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND nvl(f.PRIMARY_FLAG,'Y') = 'Y') LOOP
FUNCSF:=TRUE;
SELECT ATTRIBUTE_ID, TEMP_SECTION
FROM HZ_TRANS_ATTRIBUTES_VL a
WHERE ENTITY_NAME = entity
ORDER BY ATTRIBUTE_ID) LOOP
prefattrs := '';
SELECT STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f
WHERE ATTRIBUTE_ID = ATTRS.ATTRIBUTE_ID
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND nvl(f.PRIMARY_FLAG,'Y') = 'Y') LOOP
IF ATTRS.TEMP_SECTION IS NULL THEN
OPEN check_any_tx(ATTRS.ATTRIBUTE_ID);
select 1 from ctx_preference_values
where prv_preference =
decode(p_entity,'PARTY','HZ_PARTY_DS',
'PARTY_SITES','HZ_PARTY_SITE_DS',
'CONTACTS','HZ_CONTACT_DS',
'CONTACT_POINTS','HZ_CONTACT_POINT_DS',
'NOMATCH')
AND prv_owner = g_schema_name
AND prv_attribute='COLUMNS'
AND upper(prv_value) like '% A'||p_attr_id||',%';
SELECT max(to_number(substrb(temp_section,2)))
INTO l_next_misc
from hz_trans_attributes_vl a
where a.entity_name = p_entity
and a.temp_section IS NOT NULL;
SELECT ATTRIBUTE_ID
from hz_trans_attributes_vl a
where a.entity_name = p_entity
and exists (
SELECT 1 from hz_trans_functions_vl f
where a.attribute_id = f.attribute_id
and nvl(f.ACTIVE_FLAG,'Y') = 'Y'
and primary_flag = 'Y'
AND NVL(STAGED_FLAG,'N') ='N')
AND TEMP_SECTION IS NULL) LOOP
OPEN check_ds_misc_proc(ATTRS.ATTRIBUTE_ID);
UPDATE HZ_TRANS_ATTRIBUTES_B
SET TEMP_SECTION='M'||l_next_misc
WHERE ATTRIBUTE_ID = ATTRS.ATTRIBUTE_ID;
SELECT COUNT(1)
FROM HZ_TRANS_ATTRIBUTES_VL a
WHERE ENTITY_NAME = cp_entity
AND EXISTS (SELECT 1 FROM HZ_TRANS_FUNCTIONS_VL f
WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND nvl(f.PRIMARY_FLAG,'Y') = 'Y');
SELECT 'A'||ATTRIBUTE_ID
FROM HZ_TRANS_ATTRIBUTES_VL a
WHERE ENTITY_NAME = cp_entity
AND EXISTS (SELECT 1 FROM HZ_TRANS_FUNCTIONS_VL f
WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND nvl(f.PRIMARY_FLAG,'Y') = 'Y');
log('Trying to update the temp_section of HZ_TRANS_ATTRIBUTES_B for ' || p_entity);
UPDATE HZ_TRANS_ATTRIBUTES_B set TEMP_SECTION=NULL
WHERE ENTITY_NAME='PARTY';
UPDATE HZ_TRANS_ATTRIBUTES_B set TEMP_SECTION=NULL
WHERE ENTITY_NAME='PARTY_SITES';
UPDATE HZ_TRANS_ATTRIBUTES_B set TEMP_SECTION=NULL
WHERE ENTITY_NAME='CONTACTS';
UPDATE HZ_TRANS_ATTRIBUTES_B set TEMP_SECTION=NULL
WHERE ENTITY_NAME='CONTACT_POINTS';
log('Update of HZ_TRANS_ATTRIBUTES_B successful for ' || p_entity);
SELECT OWNER||'.'||INDEX_NAME idx_name
FROM sys.all_indexes i, hz_trans_attributes_vl a, hz_trans_functions_vl f
WHERE f.attribute_id = a.attribute_id
AND i.owner = l_owner1
AND f.index_required_flag in ('Y','T')
AND i.INDEX_NAME = decode(a.entity_name,'PARTY','HZ_STAGED_PARTIES',
'PARTY_SITES','HZ_STAGED_PARTY_SITES','CONTACTS','HZ_STAGED_CONTACTS',
'CONTACT_POINTS','HZ_STAGED_CONTACT_POINTS')||'_N'||f.function_id) LOOP
EXECUTE IMMEDIATE 'DROP INDEX '||IDX.idx_name;
UPDATE hz_trans_functions_b set index_required_flag='N' where index_required_flag='T';
INSERT INTO HZ_DQM_STAGE_LOG (
OPERATION,
NUMBER_OF_WORKERS,
WORKER_NUMBER,
STEP,
START_FLAG,
START_TIME,
END_FLAG,
END_TIME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES (
substr(p_operation,1,30),
p_num_workers,
p_worker_number,
p_step,
NULL,
NULL,
NULL,
NULL,
hz_utility_pub.created_by,
hz_utility_pub.creation_date,
hz_utility_pub.last_update_login,
hz_utility_pub.last_update_date,
hz_utility_pub.user_id
);
SELECT blocks - empty_blocks
FROM sys.dba_tables
WHERE table_name = p_table_name and owner = l_owner1;
CURSOR c_db_block_size is SELECT value
FROM v$parameter
WHERE name = 'db_block_size' ;
SELECT count(1)
from hz_trans_attributes_vl a
where a.entity_name = p_entity
and exists (
SELECT 1 from hz_trans_functions_vl f
where a.attribute_id = f.attribute_id
and nvl(f.ACTIVE_FLAG,'Y') = 'Y'
and primary_flag = 'Y'
AND NVL(STAGED_FLAG,'N') ='N');
select 1 from ctx_preference_values
where prv_preference =
decode(p_entity,'PARTY','HZ_PARTY_DS',
'PARTY_SITES','HZ_PARTY_SITE_DS',
'CONTACTS','HZ_CONTACT_DS',
'CONTACT_POINTS','HZ_CONTACT_POINT_DS',
'NOMATCH')
AND prv_owner = g_schema_name
AND prv_attribute='COLUMNS'
AND upper(prv_value) like '%HZDQM.M%';
SELECT max(to_number(substrb(temp_section,2)))+l_num_primary
INTO l_next_misc_section
from hz_trans_attributes_vl a
where a.entity_name = p_entity
and a.temp_section IS NOT NULL;
SELECT decode(p_entity,'PARTY','HZ_STAGED_PARTIES',
'PARTY_SITES','HZ_STAGED_PARTIES',
'CONTACTS','HZ_STAGED_CONTACTS',
'CONTACT_POINTS','HZ_STAGED_CONTACT_POINTS','DUMMY')
INTO l_table_name
from DUAL;
'SELECT 1 FROM '||l_table_name||
' WHERE ROWNUM=1 AND CONTAINS(concat_col,''({CHECK} within M'||l_next_misc_section||')'')>0';
SELECT f.TRANSFORMATION_NAME, f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f, HZ_TRANS_ATTRIBUTES_VL a
WHERE PRIMARY_FLAG = 'Y'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = cp_entity_name;
CURSOR c_sync is select 1 from hz_dqm_sync_interface where staged_flag <> 'E' and rownum=1;
SELECT PAR_VALUE INTO l_idx_mem
FROM CTX_PARAMETERS
WHERE PAR_NAME = 'MAX_INDEX_MEMORY';
SELECT PAR_VALUE INTO l_idx_mem
FROM CTX_PARAMETERS
WHERE PAR_NAME = 'DEFAULT_INDEX_MEMORY';
SELECT PAR_VALUE INTO l_idx_mem
FROM CTX_PARAMETERS
WHERE PAR_NAME = 'MAX_INDEX_MEMORY';
SELECT PAR_VALUE INTO l_idx_mem
FROM CTX_PARAMETERS
WHERE PAR_NAME = 'DEFAULT_INDEX_MEMORY';
select tablespace, index_tablespace
into ctx_tbsp, ctx_index_tbsp
from fnd_product_installations
where application_id = '222';
SELECT number_of_workers INTO l_last_num_workers
FROM HZ_DQM_STAGE_LOG
WHERE operation = l_command
AND STEP = 'INIT';
DELETE from HZ_DQM_STAGE_LOG where operation = l_command;
DELETE from HZ_DQM_STAGE_LOG where operation = 'CREATE_INDEXES';
SELECT step INTO l_step
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'CREATE_INDEXES'
AND step like 'HZ_PARTIES%';
SELECT step INTO l_step
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'CREATE_INDEXES'
AND step like 'HZ_PARTY_SITES%';
SELECT step INTO l_step
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'CREATE_INDEXES'
AND step like 'HZ_ORG_CONTACTS%';
SELECT step INTO l_step
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'CREATE_INDEXES'
AND step like 'HZ_CONTACT_POINTS%';
SELECT number_of_workers INTO l_last_num_workers
FROM HZ_DQM_STAGE_LOG
WHERE operation = substr(l_command,1,30)
AND STEP = 'INIT';
DELETE from HZ_DQM_STAGE_LOG where operation = substr(l_command,1,30) ;
SELECT count(*) into l_is_wildchar from HZ_DQM_STAGE_LOG where operation = 'STAGE_FOR_WILDCHAR_SEARCH' and rownum = 1 ;
INSERT INTO HZ_DQM_STAGE_LOG(operation, number_of_workers, worker_number, step,
last_update_date, creation_date, created_by, last_updated_by)
VALUES ('STAGE_FOR_WILDCHAR_SEARCH', '-1', '-1', 'Y', SYSDATE, SYSDATE, 0, 0);
SELECT number_of_workers INTO l_last_num_workers
FROM HZ_DQM_STAGE_LOG
WHERE operation = l_command
AND STEP = 'INIT';
delete_existing_data;
UPDATE HZ_TRANS_FUNCTIONS_B SET STAGED_FLAG='N'; --Bug No: 3907584.
DELETE from HZ_DQM_STAGE_LOG where operation = l_command;
DELETE from HZ_DQM_STAGE_LOG where operation = 'CREATE_INDEXES';
DELETE from HZ_DQM_STAGE_LOG where operation = 'CREATE_INDEXES';
DELETE from HZ_DQM_STAGE_LOG where operation = 'CREATE_INDEXES';
SELECT 1 INTO T FROM HZ_STAGED_PARTIES
WHERE ROWNUM=1
AND CONTAINS (concat_col, 'dummy_string')>0;
UPDATE HZ_DQM_STAGE_LOG
SET START_FLAG ='Y', START_TIME=SYSDATE,END_FLAG = 'Y', END_TIME=SYSDATE
WHERE OPERATION = 'CREATE_INDEXES' AND step = 'HZ_PARTIES';
SELECT 1 INTO T FROM HZ_STAGED_PARTY_SITES
WHERE ROWNUM=1
AND CONTAINS (concat_col, 'dummy_string')>0;
UPDATE HZ_DQM_STAGE_LOG
SET START_FLAG ='Y', START_TIME=SYSDATE,END_FLAG = 'Y', END_TIME=SYSDATE
WHERE OPERATION = 'CREATE_INDEXES' AND step = 'HZ_PARTY_SITES';
SELECT 1 INTO T FROM HZ_STAGED_CONTACTS
WHERE ROWNUM=1
AND CONTAINS (concat_col, 'dummy_string')>0;
UPDATE HZ_DQM_STAGE_LOG
SET START_FLAG ='Y', START_TIME=SYSDATE,END_FLAG = 'Y', END_TIME=SYSDATE
WHERE OPERATION = 'CREATE_INDEXES' AND step = 'HZ_ORG_CONTACTS';
SELECT 1 INTO T FROM HZ_STAGED_CONTACT_POINTS
WHERE ROWNUM=1
AND CONTAINS (concat_col, 'dummy_string')>0;
UPDATE HZ_DQM_STAGE_LOG
SET START_FLAG ='Y', START_TIME=SYSDATE,END_FLAG = 'Y', END_TIME=SYSDATE
WHERE OPERATION = 'CREATE_INDEXES' AND step = 'HZ_CONTACT_POINTS';
Select request_id BULK COLLECT into l_sub_requests
from Fnd_Concurrent_Requests R
Where Parent_Request_Id = FND_GLOBAL.conc_request_id
and (phase_code<>'C' or status_code<>'C');
( SELECT start_flag, end_flag
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_step);
SELECT SYSDATE INTO l_startdate FROM DUAL;
UPDATE HZ_DQM_STAGE_LOG set start_flag='Y', start_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
HZ_STAGE_MAP_TRANSFORM.insert_stage_parties('N',l_party_cur);
HZ_STAGE_MAP_TRANSFORM.insert_stage_parties('Y',l_party_cur);
UPDATE HZ_DQM_STAGE_LOG set end_flag='Y', end_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
UPDATE HZ_DQM_STAGE_LOG set start_flag='Y', start_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
HZ_STAGE_MAP_TRANSFORM.insert_stage_parties('N',l_party_cur);
HZ_STAGE_MAP_TRANSFORM.insert_stage_parties('Y',l_party_cur);
UPDATE HZ_DQM_STAGE_LOG set end_flag='Y', end_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
UPDATE HZ_DQM_STAGE_LOG set start_flag='Y', start_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
HZ_STAGE_MAP_TRANSFORM.insert_stage_parties('N',l_party_cur);
HZ_STAGE_MAP_TRANSFORM.insert_stage_parties('Y',l_party_cur);
UPDATE HZ_DQM_STAGE_LOG set end_flag='Y', end_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE mod(PARTY_ID,l_num_workers) = l_worker_number
AND creation_date<=l_startdate;
UPDATE HZ_DQM_STAGE_LOG set start_flag='Y', start_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
HZ_STAGE_MAP_TRANSFORM_UPD.update_stage_parties(l_party_cur);
UPDATE HZ_DQM_STAGE_LOG set end_flag='Y', end_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
UPDATE HZ_DQM_STAGE_LOG set start_flag='Y', start_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
HZ_STAGE_MAP_TRANSFORM_UPD.update_stage_parties(l_party_cur);
UPDATE HZ_DQM_STAGE_LOG set end_flag='Y', end_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
UPDATE HZ_DQM_STAGE_LOG set start_flag='Y', start_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
HZ_STAGE_MAP_TRANSFORM_UPD.update_stage_parties(l_party_cur);
UPDATE HZ_DQM_STAGE_LOG set end_flag='Y', end_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
UPDATE HZ_DQM_STAGE_LOG set start_flag='Y', start_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
HZ_STAGE_MAP_TRANSFORM_UPD.update_stage_contacts(l_contact_cur);
UPDATE HZ_DQM_STAGE_LOG set end_flag='Y', end_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
UPDATE HZ_DQM_STAGE_LOG set start_flag='Y', start_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
HZ_STAGE_MAP_TRANSFORM_UPD.update_stage_party_sites(l_party_site_cur);
UPDATE HZ_DQM_STAGE_LOG set end_flag='Y', end_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
UPDATE HZ_DQM_STAGE_LOG set start_flag='Y', start_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
HZ_STAGE_MAP_TRANSFORM_UPD.update_stage_contact_pts(l_cpt_cur);
UPDATE HZ_DQM_STAGE_LOG set end_flag='Y', end_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
select 'Y'
from hz_trans_functions_vl t, hz_trans_attributes_b a
where a.attribute_id = t.attribute_id
and ENTITY_NAME = p_entity
and nvl(staged_flag, 'N') = 'N'
and primary_flag = 'Y'
and nvl(active_flag, 'Y') = 'Y'
and rownum = 1;
PROCEDURE update_word_replacements
IS
CURSOR c_delete IS SELECT 1 FROM HZ_WORD_REPLACEMENTS
WHERE DELETE_FLAG ='Y'
AND ROWNUM =1;
CURSOR c_staged IS SELECT 1 FROM HZ_WORD_REPLACEMENTS
WHERE STAGED_FLAG ='N'
AND ROWNUM =1;
OPEN c_delete;
FETCH c_delete INTO l_val;
CLOSE c_delete;
log('Deleting word replacements that are marked for delete..');
DELETE FROM HZ_WORD_REPLACEMENTS WHERE DELETE_FLAG = 'Y';
UPDATE HZ_WORD_REPLACEMENTS SET STAGED_FLAG = 'Y'
WHERE STAGED_FLAG = 'N';
END update_word_replacements;
SELECT count(1) FROM
(SELECT distinct f.staged_attribute_column
FROM HZ_TRANS_FUNCTIONS_VL f, HZ_TRANS_ATTRIBUTES_VL a
WHERE PRIMARY_FLAG = 'Y'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
and f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.entity_name = p_entity
);
select min(party_id), max(party_id)
into l_min_id, l_max_id
from hz_staged_parties;
select party_id into tmp
from (
select party_id, rownum rnum
from ( SELECT party_id
from hz_staged_parties
where party_id>l_min_id
and rownum<1001 ) a )
where rnum = 1000;
update hz_staged_parties set d_ps = 'SYNC', d_ct = 'SYNC', d_cpt = 'SYNC', concat_col = concat_col
where party_id between l_min_id and tmp;
update hz_staged_parties set concat_col = concat_col,
d_ps = 'SYNC', d_ct = 'SYNC', d_cpt = 'SYNC'
where party_id between l_min_id and l_max_id ;
select min(party_id), max(party_id)
into l_min_id, l_max_id
from hz_staged_parties;
select party_id into tmp
from (
select party_id, rownum rnum
from ( SELECT party_id
from hz_staged_parties
where party_id>l_min_id
and rownum<1001 ) a )
where rnum = 1000;
update hz_staged_parties set concat_col = concat_col
where party_id between l_min_id and tmp;
update hz_staged_parties set concat_col = concat_col
where party_id between l_min_id and l_max_id ;
UPDATE HZ_TRANS_FUNCTIONS_B
SET STAGED_FLAG='Y'
WHERE nvl(ACTIVE_FLAG,'Y') = 'Y' and nvl(staged_flag,'N')='N'
and attribute_id in (
select attribute_id from hz_trans_attributes_vl where entity_name='PARTY');
select min(party_site_id), max(party_site_id)
into l_min_id, l_max_id
from hz_staged_party_sites;
select party_site_id into tmp
from (
select party_site_id, rownum rnum
from ( SELECT party_site_id
from hz_staged_party_sites
where party_site_id > l_min_id
and rownum<1001 ) a )
where rnum = 1000;
update hz_staged_party_sites set concat_col = concat_col
where party_id between l_min_id and tmp;
update hz_staged_party_sites set concat_col = concat_col
where party_site_id between l_min_id and l_max_id ;
UPDATE HZ_TRANS_FUNCTIONS_B
SET STAGED_FLAG='Y'
WHERE nvl(ACTIVE_FLAG,'Y') = 'Y' and nvl(staged_flag,'N')='N'
and attribute_id in (
select attribute_id from hz_trans_attributes_vl where entity_name='PARTY_SITES');
select min(org_contact_id), max(org_contact_id)
into l_min_id, l_max_id
from hz_staged_contacts;
select org_contact_id into tmp
from (
select org_contact_id, rownum rnum
from ( SELECT org_contact_id
from hz_staged_contacts
where org_contact_id > l_min_id
and rownum<1001 ) a )
where rnum = 1000;
update hz_staged_contacts set concat_col = concat_col
where org_contact_id between l_min_id and tmp;
update hz_staged_contacts set concat_col = concat_col
where org_contact_id between l_min_id and l_max_id ;
UPDATE HZ_TRANS_FUNCTIONS_B
SET STAGED_FLAG='Y'
WHERE nvl(ACTIVE_FLAG,'Y') = 'Y' and nvl(staged_flag,'N')='N'
and attribute_id in (
select attribute_id from hz_trans_attributes_vl where entity_name='CONTACTS');
select min(contact_point_id), max(contact_point_id)
into l_min_id, l_max_id
from hz_staged_contact_points;
select contact_point_id into tmp
from (
select contact_point_id, rownum rnum
from ( SELECT contact_point_id
from hz_staged_contact_points
where contact_point_id > l_min_id
and rownum<1001 ) a )
where rnum = 1000;
update hz_staged_contact_points set concat_col = concat_col
where contact_point_id between l_min_id and tmp;
update hz_staged_contact_points set concat_col = concat_col
where contact_point_id between l_min_id and l_max_id ;
UPDATE HZ_TRANS_FUNCTIONS_B
SET STAGED_FLAG='Y'
WHERE nvl(ACTIVE_FLAG,'Y') = 'Y' and nvl(staged_flag,'N')='N'
and attribute_id in (
select attribute_id from hz_trans_attributes_vl where entity_name='CONTACT_POINTS');
UPDATE HZ_TRANS_FUNCTIONS_B
SET STAGED_FLAG='Y'
WHERE nvl(ACTIVE_FLAG,'Y') = 'Y'
AND nvl(staged_flag,'N')='N' ;
SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'CREATE_INDEXES'
AND step = 'HZ_PARTIES';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'CREATE_INDEXES' AND step ='HZ_PARTIES';
UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', START_TIME=SYSDATE
WHERE operation = 'CREATE_INDEXES' AND step ='HZ_PARTIES';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'CREATE_INDEXES' AND step ='HZ_PARTIES';
SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'CREATE_INDEXES'
AND step = 'HZ_PARTY_SITES';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'CREATE_INDEXES' AND step ='HZ_PARTY_SITES';
UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', START_TIME=SYSDATE
WHERE operation = 'CREATE_INDEXES' AND step ='HZ_PARTY_SITES';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'CREATE_INDEXES' AND step ='HZ_PARTY_SITES';
SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'CREATE_INDEXES'
AND step = 'HZ_ORG_CONTACTS';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'CREATE_INDEXES' AND step ='HZ_ORG_CONTACTS';
UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', START_TIME=SYSDATE
WHERE operation = 'CREATE_INDEXES' AND step ='HZ_ORG_CONTACTS';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'CREATE_INDEXES' AND step ='HZ_ORG_CONTACTS';
SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'CREATE_INDEXES'
AND step = 'HZ_CONTACT_POINTS';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'CREATE_INDEXES' AND step ='HZ_CONTACT_POINTS';
UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', START_TIME=SYSDATE
WHERE operation = 'CREATE_INDEXES' AND step ='HZ_CONTACT_POINTS';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'CREATE_INDEXES' AND step ='HZ_CONTACT_POINTS';
UPDATE HZ_TRANS_FUNCTIONS_B
SET STAGED_FLAG='Y' WHERE nvl(ACTIVE_FLAG,'Y') = 'Y'
AND nvl(staged_flag,'N')='N';
--DELETE THE WORD REPLACEMENTS THAT ARE MARKED FOR DELETE AND
--UPDATE THE STAGED FLAG OF WORD REPLACEMENTS TO Y.
update_word_replacements;
l_update_str VARCHAR2(4000);
l_update_str := null;
for ATTRS IN (SELECT ATTRIBUTE_ID, ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL
WHERE ENTITY_NAME = p_entity)
LOOP
for FUNCS IN (SELECT PROCEDURE_NAME, STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL
WHERE ATTRIBUTE_ID = ATTRS.ATTRIBUTE_ID
AND nvl(ACTIVE_FLAG,'Y') = 'Y'
AND NVL(STAGED_FLAG,'N') <> 'Y')
LOOP
l(' l_stage_rec.'||FUNCS.STAGED_ATTRIBUTE_COLUMN || ' := ');
l_update_str := ' '|| FUNCS.STAGED_ATTRIBUTE_COLUMN || ' = ' ||
' l_stage_rec.'||FUNCS.STAGED_ATTRIBUTE_COLUMN || ' ';
l_update_str := l_update_str || ','|| FUNCS.STAGED_ATTRIBUTE_COLUMN || ' = ' ||
' l_stage_rec.'||FUNCS.STAGED_ATTRIBUTE_COLUMN || ' ';
IF l_update_str IS NOT NULL THEN
IF p_entity = 'PARTY' THEN
l(' UPDATE HZ_STAGED_PARTIES SET ');
l(' ' || l_update_str);
l(' UPDATE HZ_STAGED_PARTY_SITES SET ');
l(' ' || l_update_str);
l(' UPDATE HZ_STAGED_CONTACTS SET ');
l(' ' || l_update_str);
l(' UPDATE HZ_STAGED_CONTACT_POINTS SET ');
l(' ' || l_update_str);
for ATTRS IN (SELECT ATTRIBUTE_ID, ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL
WHERE ENTITY_NAME = p_entity)
LOOP
for FUNCS IN (SELECT PROCEDURE_NAME, STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL
WHERE ATTRIBUTE_ID = ATTRS.ATTRIBUTE_ID
AND nvl(ACTIVE_FLAG,'Y') = 'Y')
LOOP
l(' l_stage_rec.'||FUNCS.STAGED_ATTRIBUTE_COLUMN || ' := ');
CURSOR l_ent_cur(l_ent_name VARCHAR2) IS (SELECT STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = l_ent_name
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND nvl(f.PRIMARY_FLAG,'Y') = 'Y'
AND nvl(a.DENORM_FLAG,'N') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID);
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') INTO uname
FROM DUAL;
l(' SELECT '||proc_cols||' FROM '||uname||'.HZ_STAGED_PARTIES WHERE ROWID=rid;');
l(' SELECT '||proc_cols||' FROM '||uname||'.HZ_STAGED_PARTY_SITES WHERE ROWID=rid;');
l(' SELECT '||proc_cols||' FROM '||uname||'.HZ_STAGED_CONTACTS WHERE ROWID=rid;');
l(' SELECT '||proc_cols||' FROM '||uname||'.HZ_STAGED_CONTACT_POINTS WHERE ROWID=rid;');
l(' SELECT distinct');
l(' SELECT distinct');
l(' SELECT distinct');
l(' PROCEDURE insert_dqm_sync_error_rec ( ');
l(' INSERT INTO hz_dqm_sync_interface ( ');
l(' LAST_UPDATE_LOGIN, ');
l(' LAST_UPDATE_DATE, ');
l(' LAST_UPDATED_BY, ');
l(' hz_utility_pub.last_update_login, ');
l(' hz_utility_pub.last_update_date, ');
l(' END insert_dqm_sync_error_rec; ');
l_org_select coltab;
l_per_select coltab;
l_oth_select coltab;
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_org_select(idx) := 'op.'||ATTRS.ATTRIBUTE_NAME;
l_per_select(idx) := 'NULL';
l_oth_select(idx) := 'NULL';
l_per_select(idx) := 'pe.'||ATTRS.ATTRIBUTE_NAME;
l_org_select(idx) := 'NULL';
l_oth_select(idx) := 'NULL';
l_org_select(idx) := 'op.'||ATTRS.ATTRIBUTE_NAME;
l_per_select(idx) := 'pe.'||ATTRS.ATTRIBUTE_NAME;
l_oth_select(idx) := 'NULL';
l_org_select(idx) := 'p.'||ATTRS.ATTRIBUTE_NAME;
l_per_select(idx) := 'p.'||ATTRS.ATTRIBUTE_NAME;
l_oth_select(idx) := 'p.'||ATTRS.ATTRIBUTE_NAME;
l_org_select(idx):='N';
l_per_select(idx):='N';
l_oth_select(idx):='N';
l_org_select(idx):='N';
l_per_select(idx):='N';
l_oth_select(idx):='N';
l_org_select(idx) := 'p.PARTY_NAME || '' '' || p.KNOWN_AS || '' '' || p.KNOWN_AS2 || '' '' || p.KNOWN_AS3 || '' ''|| p.KNOWN_AS4 || '' ''|| p.KNOWN_AS5';
l_per_select(idx) := 'p.PARTY_NAME || '' '' || p.KNOWN_AS || '' '' || p.KNOWN_AS2 || '' '' || p.KNOWN_AS3 || '' ''|| p.KNOWN_AS4 || '' ''|| p.KNOWN_AS5';
l_oth_select(idx) := 'p.PARTY_NAME || '' '' || p.KNOWN_AS || '' '' || p.KNOWN_AS2 || '' '' || p.KNOWN_AS3 || '' ''|| p.KNOWN_AS4 || '' ''|| p.KNOWN_AS5';
l(' SELECT p.PARTY_ID, p.STATUS, p.ROWID ');
FOR I in 1..l_org_select.COUNT LOOP
IF l_org_select(I) <> 'N' THEN
l(' ,' || l_org_select(I));
l(' SELECT p.PARTY_ID, p.STATUS, p.ROWID ');
FOR I in 1..l_per_select.COUNT LOOP
IF l_per_select(I) <> 'N' THEN
l(' ,' || l_per_select(I));
l(' SELECT p.PARTY_ID, p.STATUS, p.ROWID ');
FOR I in 1..l_oth_select.COUNT LOOP
IF l_oth_select(I) <> 'N' THEN
l(' ,' || l_oth_select(I));
l_org_select coltab;
l_per_select coltab;
l_oth_select coltab;
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_org_select(idx) := 'op.'||ATTRS.ATTRIBUTE_NAME;
l_per_select(idx) := 'NULL';
l_oth_select(idx) := 'NULL';
l_per_select(idx) := 'pe.'||ATTRS.ATTRIBUTE_NAME;
l_org_select(idx) := 'NULL';
l_oth_select(idx) := 'NULL';
l_org_select(idx) := 'op.'||ATTRS.ATTRIBUTE_NAME;
l_per_select(idx) := 'pe.'||ATTRS.ATTRIBUTE_NAME;
l_oth_select(idx) := 'NULL';
l_org_select(idx) := 'p.'||ATTRS.ATTRIBUTE_NAME;
l_per_select(idx) := 'p.'||ATTRS.ATTRIBUTE_NAME;
l_oth_select(idx) := 'p.'||ATTRS.ATTRIBUTE_NAME;
l_org_select(idx):='N';
l_per_select(idx):='N';
l_oth_select(idx):='N';
l_org_select(idx):='N';
l_per_select(idx):='N';
l_oth_select(idx):='N';
l_org_select(idx) := 'p.PARTY_NAME || '' '' || p.KNOWN_AS || '' '' || p.KNOWN_AS2 || '' '' || p.KNOWN_AS3 || '' ''|| p.KNOWN_AS4 || '' ''|| p.KNOWN_AS5';
l_per_select(idx) := 'p.PARTY_NAME || '' '' || p.KNOWN_AS || '' '' || p.KNOWN_AS2 || '' '' || p.KNOWN_AS3 || '' ''|| p.KNOWN_AS4 || '' ''|| p.KNOWN_AS5';
l_oth_select(idx) := 'p.PARTY_NAME || '' '' || p.KNOWN_AS || '' '' || p.KNOWN_AS2 || '' '' || p.KNOWN_AS3 || '' ''|| p.KNOWN_AS4 || '' ''|| p.KNOWN_AS5';
l(' SELECT p.PARTY_ID, p.STATUS, dsi.ROWID ');
FOR I in 1..l_org_select.COUNT LOOP
IF l_org_select(I) <> 'N' THEN
l(' ,' || l_org_select(I));
l(' SELECT p.PARTY_ID, p.STATUS, dsi.ROWID ');
FOR I in 1..l_per_select.COUNT LOOP
IF l_per_select(I) <> 'N' THEN
l(' ,' || l_per_select(I));
l(' SELECT p.PARTY_ID, p.STATUS, dsi.ROWID ');
FOR I in 1..l_oth_select.COUNT LOOP
IF l_oth_select(I) <> 'N' THEN
l(' ,' || l_oth_select(I));
l(' log (''Inserting Data into HZ_STAGED_PARTIES...'',FALSE); ');
l(' INSERT INTO HZ_STAGED_PARTIES (');
l(' log (''Exception DUP_VAL_ON_INDEX occured while inserting Party with PARTY_ID - ''||H_P_PARTY_ID(l_err_index)); ');
l(' DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY=''PARTY'' AND OPERATION=''C'' AND PARTY_ID=H_P_PARTY_ID(l_err_index); ');
l(' UPDATE hz_dqm_sync_interface ');
l(' -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table ');
l(' insert_dqm_sync_error_rec(H_P_PARTY_ID(l_err_index), NULL, NULL, NULL, ''PARTY'', p_operation, ''E'', ''N'', l_sql_errm); ');
l(' UPDATE HZ_STAGED_PARTIES SET ');
l(' log (''Exception DUP_VAL_ON_INDEX occured while inserting Party with PARTY_ID - ''||H_P_PARTY_ID(l_err_index)); ');
l(' DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY=''PARTY'' AND OPERATION=''U'' AND PARTY_ID=H_P_PARTY_ID(l_err_index); ');
l(' UPDATE hz_dqm_sync_interface ');
l(' -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table ');
l(' insert_dqm_sync_error_rec(H_P_PARTY_ID(l_err_index), NULL, NULL, NULL, ''PARTY'', p_operation, ''E'', ''N'', l_sql_errm); ');
l(' -- Bulk Insert the Import Parties into Shadow Sync Interface table ');
l(' -- Using the Merge instead of Insert statement ');
l(' -- so that duplicate records dont get inserted. ');
l(' SELECT ');
l(' INSERT ( ');
l(' LAST_UPDATE_LOGIN, ');
l(' LAST_UPDATE_DATE, ');
l(' LAST_UPDATED_BY, ');
l(' hz_utility_pub.last_update_login, ');
l(' hz_utility_pub.last_update_date, ');
l(' log (''Exception occured while inserting data into HZ_DQM_SH_SYNC_INTERFACE Table''); ');
l_org_select coltab;
l_per_select coltab;
l_oth_select coltab;
l(' p_select_type IN VARCHAR2,');
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM,
nvl(TAG,'C') column_data_type --Bug No: 4279469
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f,FND_LOOKUP_VALUES_VL lkp --Bug No: 4279469
WHERE ENTITY_NAME = 'PARTY'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND lkp.LOOKUP_TYPE = 'PARTY_LOGICAL_ATTRIBUTE_LIST' --Bug No: 4279469
AND lkp.LOOKUP_CODE = a.ATTRIBUTE_NAME --Bug No: 4279469
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_org_select(idx) := l_org_attr_name;
l_per_select(idx) := 'NULL';
l_oth_select(idx) := 'NULL';
l_per_select(idx) := l_per_attr_name;
l_org_select(idx) := 'NULL';
l_oth_select(idx) := 'NULL';
l_org_select(idx) := l_org_attr_name;
l_per_select(idx) := l_per_attr_name;
l_oth_select(idx) := 'NULL';
l_org_select(idx) := l_attr_name;
l_per_select(idx) := l_attr_name;
l_oth_select(idx) := l_attr_name;
l_org_select(idx):='N';
l_per_select(idx):='N';
l_oth_select(idx):='N';
l_org_select(idx):='N';
l_per_select(idx):='N';
l_oth_select(idx):='N';
l_org_select(idx) := 'p.PARTY_NAME || '' '' || p.KNOWN_AS || '' '' || p.KNOWN_AS2 || '' '' || p.KNOWN_AS3 || '' ''|| p.KNOWN_AS4 || '' ''|| p.KNOWN_AS5';
l_per_select(idx) := 'p.PARTY_NAME || '' '' || p.KNOWN_AS || '' '' || p.KNOWN_AS2 || '' '' || p.KNOWN_AS3 || '' ''|| p.KNOWN_AS4 || '' ''|| p.KNOWN_AS5';
l_oth_select(idx) := 'p.PARTY_NAME || '' '' || p.KNOWN_AS || '' '' || p.KNOWN_AS2 || '' '' || p.KNOWN_AS3 || '' ''|| p.KNOWN_AS4 || '' ''|| p.KNOWN_AS5';
l(' IF p_select_type = ''SINGLE_PARTY'' THEN');
l(' ELSIF p_select_type = ''ALL_PARTIES'' THEN');
l(' SELECT p.PARTY_ID, p.STATUS ');
FOR I in 1..l_org_select.COUNT LOOP
IF l_org_select(I) <> 'N' THEN
l(' ,' || l_org_select(I));
l(' SELECT p.PARTY_ID, p.STATUS ');
FOR I in 1..l_per_select.COUNT LOOP
IF l_per_select(I) <> 'N' THEN
l(' ,' || l_per_select(I));
l(' SELECT p.PARTY_ID, p.STATUS ');
FOR I in 1..l_oth_select.COUNT LOOP
IF l_oth_select(I) <> 'N' THEN
l(' ,' || l_oth_select(I));
l(' SELECT p.PARTY_ID, p.STATUS ');
FOR I in 1..l_org_select.COUNT LOOP
IF l_org_select(I) <> 'N' THEN
l(' ,' || l_org_select(I));
l(' AND NOT EXISTS (select 1 FROM HZ_STAGED_PARTIES sp ');
l(' SELECT p.PARTY_ID, p.STATUS ');
FOR I in 1..l_per_select.COUNT LOOP
IF l_per_select(I) <> 'N' THEN
l(' ,' || l_per_select(I));
l(' AND NOT EXISTS (select 1 FROM HZ_STAGED_PARTIES sp ');
l(' SELECT p.PARTY_ID, p.STATUS ');
FOR I in 1..l_oth_select.COUNT LOOP
IF l_oth_select(I) <> 'N' THEN
l(' ,' || l_oth_select(I));
l(' AND NOT EXISTS (select 1 FROM HZ_STAGED_PARTIES sp ');
l(' PROCEDURE insert_stage_parties ( ');
l(' INSERT INTO HZ_STAGED_PARTIES (');
l(' INSERT INTO HZ_DQM_STAGE_GT ( PARTY_ID, OWNER_ID, PARTY_INDEX) VALUES (');
l(' insert_stage_contacts;');
l(' insert_stage_party_sites;');
l(' insert_stage_contact_pts;');
l(' UPDATE HZ_STAGED_PARTIES SET ');
l(' SELECT p.PARTY_ID, p.STATUS ');
FOR I in 1..l_org_select.COUNT LOOP
IF l_org_select(I) <> 'N' THEN
l(' ,' || l_org_select(I));
l(' SELECT p.PARTY_ID, p.STATUS ');
FOR I in 1..l_per_select.COUNT LOOP
IF l_per_select(I) <> 'N' THEN
l(' ,' || l_per_select(I));
l(' SELECT p.PARTY_ID, p.STATUS ');
FOR I in 1..l_oth_select.COUNT LOOP
IF l_per_select(I) <> 'N' THEN
l(' ,' || l_oth_select(I));
l(' INSERT INTO HZ_STAGED_PARTIES (');
l(' UPDATE HZ_STAGED_PARTIES SET ');
l(' SELECT oc.org_contact_id ');
l(' SELECT party_type INTO l_party_type ');
l(' -- When the operation is an update ');
l(' insert_dqm_sync_error_rec(p_party_id,l_org_contact_id,null,null,''CONTACTS'',''U'',''E'',''Y'', l_sql_err_message); ');
l(' SELECT p.PARTY_ID, p.STATUS ');
FOR I in 1..l_org_select.COUNT LOOP
IF l_org_select(I) <> 'N' THEN
l(' ,' || l_org_select(I));
l(' SELECT p.PARTY_ID, p.STATUS ');
FOR I in 1..l_per_select.COUNT LOOP
IF l_per_select(I) <> 'N' THEN
l(' ,' || l_per_select(I));
l(' SELECT p.PARTY_ID, p.STATUS ');
FOR I in 1..l_oth_select.COUNT LOOP
IF l_per_select(I) <> 'N' THEN
l(' ,' || l_oth_select(I));
l(' INSERT INTO HZ_STAGED_PARTIES (');
l(' UPDATE HZ_STAGED_PARTIES SET ');
l(' -- insert a record into hz_dqm_sh_sync_interface table for each record ');
l(' HZ_DQM_SYNC.insert_sh_interface_rec(p_party_id,null,null,null,''PARTY'',p_operation); ');
l(' insert_dqm_sync_error_rec(p_party_id, NULL, NULL, NULL, ''PARTY'', p_operation, ''E'', ''Y'', l_sql_err_message); ');
l_select coltab;
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY_SITES'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY_SITES'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'l.'||ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'ps.'||ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'N';
l_select(idx) := 'N';
l_select(idx) := 'rtrim(l.address1 || '' '' || l.address2 || '' '' || l.address3 || '' '' || l.address4)';
l(' SELECT /*+ ORDERED USE_NL(ps l) */ ');
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l_select coltab;
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY_SITES'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY_SITES'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'l.'||ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'ps.'||ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'N';
l_select(idx) := 'N';
l_select(idx) := 'rtrim(l.address1 || '' '' || l.address2 || '' '' || l.address3 || '' '' || l.address4)';
l(' SELECT /*+ ORDERED USE_NL(ps l) */ ');
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' log (''Inserting Data into HZ_STAGED_PARTY_SITES...'',FALSE); ');
l(' INSERT INTO HZ_STAGED_PARTY_SITES (');
l(' log (''Exception DUP_VAL_ON_INDEX occured while inserting Party Site with PARTY_SITE_ID - ''||H_PARTY_SITE_ID(l_err_index)); ');
l(' DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY=''PARTY_SITES'' AND OPERATION=''C'' AND RECORD_ID=H_PARTY_SITE_ID(l_err_index); ');
l(' UPDATE hz_dqm_sync_interface ');
l(' -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table ');
l(' insert_dqm_sync_error_rec(H_PS_PARTY_ID(l_err_index), H_PARTY_SITE_ID(l_err_index), NULL, H_PS_ORG_CONTACT_ID(l_err_index), ''PARTY_SITES'', p_operation, ''E'', ''N'', l_sql_errm); ');
l(' UPDATE HZ_STAGED_PARTY_SITES SET ');
l(' log (''Exception DUP_VAL_ON_INDEX occured while inserting Party Site with PARTY_SITE_ID - ''||H_PARTY_SITE_ID(l_err_index)); ');
l(' DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY=''PARTY_SITES'' AND OPERATION=''U'' AND RECORD_ID=H_PARTY_SITE_ID(l_err_index); ');
l(' UPDATE hz_dqm_sync_interface ');
l(' -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table ');
l(' insert_dqm_sync_error_rec(H_PS_PARTY_ID(l_err_index), H_PARTY_SITE_ID(l_err_index), NULL, H_PS_ORG_CONTACT_ID(l_err_index), ''PARTY_SITES'', p_operation, ''E'', ''N'', l_sql_errm); ');
l(' -- Update HZ_STAGED_PARTIES, if corresponding child entity records ');
l(' -- PARTY_SITES (in this case), have been inserted/updated ');
l(' --Fix for bug 5048604, to update concat_col during update of denorm column ');
l(' UPDATE HZ_STAGED_PARTIES set ');
l(' -- Bulk Insert of Import Party Sites into Shadow Sync Interface table ');
l(' -- Using the Merge instead of Insert statement ');
l(' -- so that duplicate records dont get inserted. ');
l(' SELECT ');
l(' INSERT ( ');
l(' LAST_UPDATE_LOGIN, ');
l(' LAST_UPDATE_DATE, ');
l(' LAST_UPDATED_BY, ');
l(' hz_utility_pub.last_update_login, ');
l(' hz_utility_pub.last_update_date, ');
l(' log (''Exception occured while inserting data into HZ_DQM_SH_SYNC_INTERFACE Table''); ');
l_select coltab;
l(' PROCEDURE insert_stage_party_sites IS ');
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM,
nvl(lkp.tag,'C') column_data_type
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f,FND_LOOKUP_VALUES_VL lkp --Bug No: 4279469
WHERE ENTITY_NAME = 'PARTY_SITES'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND lkp.lookup_type = 'PARTY_SITE_LOGICAL_ATTRIB_LIST'
and lkp.lookup_code = a.ATTRIBUTE_NAME
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY_SITES'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_select(idx) := l_loc_attr_name;
l_select(idx) := l_ps_attr_name;
l_select(idx) := 'N';
l_select(idx) := 'N';
l_select(idx) := 'rtrim(l.address1 || '' '' || l.address2 || '' '' || l.address3 || '' '' || l.address4)';
l(' SELECT /*+ ORDERED USE_NL(ps l) */ ps.PARTY_SITE_ID, g.party_id, g.org_contact_id, g.PARTY_INDEX, ps.status '); --Bug No: 4299785
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY_SITES'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND nvl(a.DENORM_FLAG,'N') = 'Y') LOOP
IF FIRST THEN
-- Fix for bug 4872997
-- Wrapping the 'l_denorm' portion of code into a begin-excpetion block
-- and setting the denorm value to 'SYNC' if sqlcode-6502 error occurs
l(' BEGIN ');
l(' INSERT INTO HZ_STAGED_PARTY_SITES (');
l(' INSERT INTO HZ_DQM_STAGE_GT (PARTY_ID, OWNER_ID, OWNER_TABLE, PARTY_SITE_ID,');
l(' SELECT ps.PARTY_SITE_ID, d.party_id, d.org_contact_id, ps.STATUS '); --Bug No: 4299785
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' INSERT INTO HZ_STAGED_PARTY_SITES (');
l(' UPDATE HZ_STAGED_PARTY_SITES SET ');
l(' --Fix for bug 5048604, to update concat_col during update of denorm column ');
l(' UPDATE HZ_STAGED_PARTIES set ');
l(' SELECT ps.party_id,p.party_type INTO l_party_id1, l_party_type ');
l(' SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id ');
l(' SELECT ps.PARTY_SITE_ID, l_party_id, l_org_contact_id, ps.STATUS '); --Bug No: 4299785
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' INSERT INTO HZ_STAGED_PARTY_SITES (');
l(' UPDATE HZ_STAGED_PARTY_SITES SET ');
l(' --Fix for bug 5048604, to update concat_col during update of denorm column ');
l(' UPDATE HZ_STAGED_PARTIES set');
l(' -- insert a record into hz_dqm_sh_sync_interface table for each record ');
l(' HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_party_site_id,null,l_org_contact_id,''PARTY_SITES'',p_operation); ');
l(' insert_dqm_sync_error_rec(l_party_id, p_party_site_id, NULL, l_org_contact_id, ''PARTY_SITES'', p_operation, ''E'', ''Y'', l_sql_err_message); ');
l_select coltab;
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'pp.'||ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'oc.'||ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'r.'||ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'N';
l_select(idx) := 'N';
l_select(idx) := 'rtrim(pp.person_first_name || '' '' || pp.person_last_name)';
l(' SELECT ');
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' SELECT ');
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l_select coltab;
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'pp.'||ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'oc.'||ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'r.'||ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'N';
l_select(idx) := 'N';
l_select(idx) := 'rtrim(pp.person_first_name || '' '' || pp.person_last_name)';
l(' SELECT ');
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' log (''Inserting Data into HZ_STAGED_CONTACTS...'',FALSE); ');
l(' INSERT INTO HZ_STAGED_CONTACTS (');
l(' log (''Exception DUP_VAL_ON_INDEX occured while inserting a Contact with ORG_CONTACT_ID - ''||H_ORG_CONTACT_ID(l_err_index)); ');
l(' DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY=''CONTACTS'' AND OPERATION=''C'' AND RECORD_ID=H_ORG_CONTACT_ID(l_err_index); ');
l(' UPDATE hz_dqm_sync_interface ');
l(' -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table ');
l(' insert_dqm_sync_error_rec(H_C_PARTY_ID(l_err_index), H_ORG_CONTACT_ID(l_err_index), NULL, NULL, ''CONTACTS'', p_operation, ''E'', ''N'', l_sql_errm); ');
l(' UPDATE HZ_STAGED_CONTACTS SET ');
l(' log (''Exception DUP_VAL_ON_INDEX occured while inserting a Contact with ORG_CONTACT_ID - ''||H_ORG_CONTACT_ID(l_err_index)); ');
l(' DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY=''CONTACTS'' AND OPERATION=''U'' AND RECORD_ID=H_ORG_CONTACT_ID(l_err_index); ');
l(' UPDATE hz_dqm_sync_interface ');
l(' -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table ');
l(' insert_dqm_sync_error_rec(H_C_PARTY_ID(l_err_index), H_ORG_CONTACT_ID(l_err_index), NULL, NULL, ''CONTACTS'', p_operation, ''E'', ''N'', l_sql_errm); ');
l(' -- Update HZ_STAGED_PARTIES, if corresponding child entity records ');
l(' -- CONTACTS (in this case), have been inserted/updated ');
l(' --Fix for bug 5048604, to update concat_col during update of denorm column ');
l(' UPDATE HZ_STAGED_PARTIES set ');
l(' -- Bulk Insert of Import Contacts into Shadow Sync Interface table ');
l(' -- Using the Merge instead of Insert statement ');
l(' -- so that duplicate records dont get inserted. ');
l(' SELECT ');
l(' INSERT ( ');
l(' LAST_UPDATE_LOGIN, ');
l(' LAST_UPDATE_DATE, ');
l(' LAST_UPDATED_BY, ');
l(' hz_utility_pub.last_update_login, ');
l(' hz_utility_pub.last_update_date, ');
l(' log (''Exception occured while inserting data into HZ_DQM_SH_SYNC_INTERFACE Table''); ');
l_select coltab;
l(' PROCEDURE insert_stage_contacts IS ');
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM,
nvl(lkp.tag,'C') column_data_type
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f,FND_LOOKUP_VALUES_VL lkp --Bug No: 4279469
WHERE ENTITY_NAME = 'CONTACTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND lkp.LOOKUP_TYPE='CONTACT_LOGICAL_ATTRIB_LIST'
AND lkp.LOOKUP_CODE = a.ATTRIBUTE_NAME
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_select(idx) := l_pp_attr_name;
l_select(idx) := l_oc_attr_name;
l_select(idx) := l_attr_name;
l_select(idx) := 'N';
l_select(idx) := 'N';
l_select(idx) := 'rtrim(pp.person_first_name || '' '' || pp.person_last_name)';
l(' SELECT ');
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND nvl(a.DENORM_FLAG,'N') = 'Y') LOOP
IF FIRST THEN
-- Fix for bug 4872997
-- Wrapping the 'l_denorm' portion of code into a begin-excpetion block
-- and setting the denorm value to 'SYNC' if sqlcode-6502 error occurs
l(' BEGIN ');
l(' INSERT INTO HZ_STAGED_CONTACTS (');
l(' INSERT INTO HZ_DQM_STAGE_GT(PARTY_ID,OWNER_ID,ORG_CONTACT_ID,PARTY_INDEX) ');
l(' SELECT H_C_PARTY_ID(I), H_R_PARTY_ID(I), H_ORG_CONTACT_ID(I), H_PARTY_INDEX(I)');
l(' SELECT oc.ORG_CONTACT_ID, d.PARTY_ID, r.STATUS '); --Bug No: 4299785
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' INSERT INTO HZ_STAGED_CONTACTS (');
l(' UPDATE HZ_STAGED_CONTACTS SET ');
l(' --Fix for bug 5048604, to update concat_col during update of denorm column ');
l(' UPDATE HZ_STAGED_PARTIES set');
l(' SELECT r.object_id INTO l_party_id ');
l(' SELECT oc.ORG_CONTACT_ID, l_party_id, r.status '); --Bug No: 4299785
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' INSERT INTO HZ_STAGED_CONTACTS (');
l(' UPDATE HZ_STAGED_CONTACTS SET ');
l(' --Fix for bug 5048604, to update concat_col during update of denorm column ');
l(' UPDATE HZ_STAGED_PARTIES set');
l(' -- insert a record into hz_dqm_sh_sync_interface table for each record ');
l(' HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_org_contact_id,null,null,''CONTACTS'',p_operation); ');
l(' insert_dqm_sync_error_rec(l_party_id, p_org_contact_id, NULL, NULL, ''CONTACTS'', p_operation, ''E'', ''Y'', l_sql_err_message); ');
l_select coltab;
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACT_POINTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACT_POINTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'cp.'||ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'N';
l_select(idx) := 'N';
l_select(idx) := 'translate(phone_number,''0123456789ABCDEFGHIJKLMNOPQRSTUV'||
'WXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''''*+,-./:;<=>?@[\]^'||
l_select(idx) := 'translate(phone_country_code|| '' '' || phone_area_code||'' '' || phone_number,''0123456789ABCDEFGHIJKLMNOPQRSTUV'||
'WXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''''*+,-./:;<=>?@[\]^_'||
l(' SELECT ');
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l_select coltab;
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACT_POINTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACT_POINTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'cp.'||ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'N';
l_select(idx) := 'N';
l_select(idx) := 'translate(phone_number,''0123456789ABCDEFGHIJKLMNOPQRSTUV'||
'WXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''''*+,-./:;<=>?@[\]^'||
l_select(idx) := 'translate(phone_country_code|| '' '' || phone_area_code||'' '' || phone_number,''0123456789ABCDEFGHIJKLMNOPQRSTUV'||
'WXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''''*+,-./:;<=>?@[\]^_'||
l(' SELECT ');
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' log (''Inserting Data into HZ_STAGED_CONTACT_POINTS...'',FALSE); ');
l(' INSERT INTO HZ_STAGED_CONTACT_POINTS (');
l(' log (''Exception DUP_VAL_ON_INDEX occured while inserting a Contact Point with CONTACT_POINT_ID - ''||H_CONTACT_POINT_ID(l_err_index)); ');
l(' DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY=''CONTACT_POINTS'' AND OPERATION=''C'' AND RECORD_ID=H_CONTACT_POINT_ID(l_err_index); ');
l(' UPDATE hz_dqm_sync_interface ');
l(' -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table ');
l(' insert_dqm_sync_error_rec(H_CPT_PARTY_ID(l_err_index), H_CONTACT_POINT_ID(l_err_index), H_CPT_PARTY_SITE_ID(l_err_index), H_CPT_ORG_CONTACT_ID(l_err_index), ''CONTACT_POINTS'', p_operation, ''E'', ''N'', l_sql_errm); ');
l(' UPDATE HZ_STAGED_CONTACT_POINTS SET ');
l(' log (''Exception DUP_VAL_ON_INDEX occured while inserting a Contact Point with CONTACT_POINT_ID - ''||H_CONTACT_POINT_ID(l_err_index)); ');
l(' DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY=''CONTACT_POINTS'' AND OPERATION=''U'' AND RECORD_ID=H_CONTACT_POINT_ID(l_err_index); ');
l(' UPDATE hz_dqm_sync_interface ');
l(' -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table ');
l(' insert_dqm_sync_error_rec(H_CPT_PARTY_ID(l_err_index), H_CONTACT_POINT_ID(l_err_index), H_CPT_PARTY_SITE_ID(l_err_index), H_CPT_ORG_CONTACT_ID(l_err_index), ''CONTACT_POINTS'', p_operation, ''E'', ''N'', l_sql_errm); ');
l(' -- Update HZ_STAGED_PARTIES, if corresponding child entity records ');
l(' -- CONTACT_POINTS (in this case), have been inserted/updated ');
l(' --Fix for bug 5048604, to update concat_col during update of denorm column ');
l(' UPDATE HZ_STAGED_PARTIES set ');
l(' -- Bulk Insert the Import of Contact Points into Shadow Sync Interface table ');
l(' -- Using the Merge instead of Insert statement ');
l(' -- so that duplicate records dont get inserted. ');
l(' SELECT ');
l(' INSERT ( ');
l(' LAST_UPDATE_LOGIN, ');
l(' LAST_UPDATE_DATE, ');
l(' LAST_UPDATED_BY, ');
l(' hz_utility_pub.last_update_login, ');
l(' hz_utility_pub.last_update_date, ');
l(' log (''Exception occured while inserting data into HZ_DQM_SH_SYNC_INTERFACE Table''); ');
l_select coltab;
l(' PROCEDURE insert_stage_contact_pts IS ');
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM,
nvl(lkp.tag,'C') column_data_type
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f,FND_LOOKUP_VALUES_VL lkp --Bug No: 4279469
WHERE ENTITY_NAME = 'CONTACT_POINTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
-- REPURI. Added the 2 lines here for bug 4957189, which were missed in bug 4279469 fix.
AND lkp.LOOKUP_TYPE='CONTACT_PT_LOGICAL_ATTRIB_LIST'
AND lkp.LOOKUP_CODE = a.ATTRIBUTE_NAME
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACT_POINTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_select(idx) := l_attr_name;
l_select(idx) := 'N';
l_select(idx) := 'N';
l_select(idx) := 'translate(phone_number,''0123456789ABCDEFGHIJKLMNOPQRSTUV'||
'WXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''''*+,-./:;<=>?@[\]^'||
l_select(idx) := 'translate(phone_country_code|| '' '' || phone_area_code||'' '' || phone_number,''0123456789ABCDEFGHIJKLMNOPQRSTUV'||
'WXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''''*+,-./:;<=>?@[\]^_'||
l(' SELECT /*+ ORDERED USE_NL(cp) */ cp.CONTACT_POINT_ID, g.party_id, g.party_site_id, g.org_contact_id, cp.CONTACT_POINT_TYPE, PARTY_INDEX, cp.STATUS '); -- Bug No:4299785
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACT_POINTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND nvl(a.DENORM_FLAG,'N') = 'Y') LOOP
IF FIRST THEN
-- Fix for bug 4872997
-- Wrapping the 'l_denorm' portion of code into a begin-excpetion block
-- and setting the denorm value to 'SYNC' if sqlcode-6502 error occurs
l(' BEGIN ');
l(' INSERT INTO HZ_STAGED_CONTACT_POINTS (');
l(' SELECT cp.CONTACT_POINT_ID, d.PARTY_ID, d.PARTY_SITE_ID, d.ORG_CONTACT_ID, cp.CONTACT_POINT_TYPE, cp.STATUS '); --Bug No: 4299785
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' INSERT INTO HZ_STAGED_CONTACT_POINTS (');
l(' UPDATE HZ_STAGED_CONTACT_POINTS SET ');
l(' --Fix for bug 5048604, to update concat_col during update of denorm column ');
l(' UPDATE HZ_STAGED_PARTIES set');
l(' SELECT owner_table_name,owner_table_id INTO l_ot_table, l_ot_id ');
l(' SELECT p.party_id, ps.party_site_id, party_type ');
l(' SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id ');
l(' SELECT party_type INTO l_party_type ');
l(' SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id ');
l(' SELECT cp.CONTACT_POINT_ID, l_party_id, l_party_site_id, l_org_contact_id, cp.CONTACT_POINT_TYPE, cp.STATUS '); --Bug No: 4299785
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' INSERT INTO HZ_STAGED_CONTACT_POINTS (');
l(' UPDATE HZ_STAGED_CONTACT_POINTS SET ');
l(' --Fix for bug 5048604, to update concat_col during update of denorm column ');
l(' UPDATE HZ_STAGED_PARTIES set');
l(' -- insert a record into hz_dqm_sh_sync_interface table for each record ');
l(' HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_contact_point_id,l_party_site_id, l_org_contact_id, ''CONTACT_POINTS'',p_operation); ');
l(' insert_dqm_sync_error_rec(l_party_id, p_contact_point_id, l_party_site_id, l_org_contact_id, ''CONTACT_POINTS'', p_operation, ''E'', ''Y'', l_sql_err_message); ');
l_org_select coltab;
l_per_select coltab;
l_oth_select coltab;
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND f.STAGED_FLAG='N'
ORDER BY COLNUM) LOOP
IF cur_col_num
l_org_select(idx) := 'op.'||ATTRS.ATTRIBUTE_NAME;
l_per_select(idx) := 'NULL';
l_oth_select(idx) := 'NULL';
l_per_select(idx) := 'pe.'||ATTRS.ATTRIBUTE_NAME;
l_org_select(idx) := 'NULL';
l_oth_select(idx) := 'NULL';
l_org_select(idx) := 'op.'||ATTRS.ATTRIBUTE_NAME;
l_per_select(idx) := 'pe.'||ATTRS.ATTRIBUTE_NAME;
l_oth_select(idx) := 'NULL';
l_org_select(idx) := 'p.'||ATTRS.ATTRIBUTE_NAME;
l_per_select(idx) := 'p.'||ATTRS.ATTRIBUTE_NAME;
l_oth_select(idx) := 'p.'||ATTRS.ATTRIBUTE_NAME;
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME
AND f.STAGED_FLAG='N';
l_org_select(idx) := 'NULL';
l_oth_select(idx) := 'NULL';
l_per_select(idx) := 'NULL';
l(' SELECT p.PARTY_ID ');
FOR I in 1..l_org_select.COUNT LOOP
l(' ,' || l_org_select(I));
l(' SELECT p.PARTY_ID ');
FOR I in 1..l_per_select.COUNT LOOP
l(' ,' || l_per_select(I));
l(' SELECT p.PARTY_ID ');
FOR I in 1..l_oth_select.COUNT LOOP
l(' ,' || l_oth_select(I));
l(' PROCEDURE update_stage_parties ( ');
l(' UPDATE HZ_STAGED_PARTIES SET ');
l_select coltab;
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND f.staged_flag='N'
ORDER BY COLNUM) LOOP
IF cur_col_num
l_select(idx) := 'pp.'||ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'oc.'||ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'r.'||ATTRS.ATTRIBUTE_NAME;
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME
AND f.staged_flag='N';
l_select(idx) := 'NULL';
l(' SELECT oc.ORG_CONTACT_ID ');
FOR I in 1..l_select.COUNT LOOP
l(' ,' || l_select(I));
l(' PROCEDURE update_stage_contacts ( ');
l(' UPDATE HZ_STAGED_CONTACTS SET ');
l(' UPDATE HZ_STAGED_PARTIES');
l(' UPDATE HZ_STAGED_PARTIES');
l(' SELECT ct.PARTY_ID,');
l(' PROCEDURE update_stage_contacts ( ');
l(' UPDATE HZ_STAGED_PARTIES');
l(' UPDATE HZ_STAGED_PARTIES');
l_select coltab;
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY_SITES'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND f.staged_flag='N'
ORDER BY COLNUM) LOOP
IF cur_col_num
l_select(idx) := 'l.'||ATTRS.ATTRIBUTE_NAME;
l_select(idx) := 'ps.'||ATTRS.ATTRIBUTE_NAME;
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY_SITES'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME
AND f.staged_flag='N';
l_select(idx) := 'NULL';
l(' SELECT ps.PARTY_SITE_ID ');
FOR I in 1..l_select.COUNT LOOP
l(' ,' || l_select(I));
l(' PROCEDURE update_stage_party_sites ( ');
l(' UPDATE HZ_STAGED_PARTY_SITES SET');
l(' UPDATE HZ_STAGED_PARTIES');
l(' UPDATE HZ_STAGED_PARTIES');
l(' SELECT ps.PARTY_ID,');
l(' PROCEDURE update_stage_party_sites ( ');
l(' UPDATE HZ_STAGED_PARTIES');
l(' UPDATE HZ_STAGED_PARTIES');
l_select coltab;
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACT_POINTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND f.STAGED_FLAG='N'
ORDER BY COLNUM) LOOP
IF cur_col_num
l_select(idx) := 'cp.'||ATTRS.ATTRIBUTE_NAME;
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACT_POINTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME
AND f.STAGED_FLAG='N';
l_select(idx) := 'NULL';
l(' SELECT cp.CONTACT_POINT_ID ');
FOR I in 1..l_select.COUNT LOOP
l(' ,' || l_select(I));
l(' PROCEDURE update_stage_contact_pts ( ');
l(' UPDATE HZ_STAGED_CONTACT_POINTS SET');
l(' UPDATE HZ_STAGED_PARTIES');
l(' UPDATE HZ_STAGED_PARTIES');
l(' SELECT cpt.PARTY_ID,');
l(' PROCEDURE update_stage_contact_pts ( ');
l(' UPDATE HZ_STAGED_PARTIES');
l(' UPDATE HZ_STAGED_PARTIES');
SELECT decode(a.entity_name,'PARTY','HZ_STAGED_PARTIES',
'PARTY_SITES','HZ_STAGED_PARTY_SITES','CONTACTS','HZ_STAGED_CONTACTS',
'CONTACT_POINTS','HZ_STAGED_CONTACT_POINTS')||'_N0'||substrb(staged_attribute_column,3) index_name,
decode(a.entity_name,'PARTY','HZ_STAGED_PARTIES',
'PARTY_SITES','HZ_STAGED_PARTY_SITES','CONTACTS','HZ_STAGED_CONTACTS',
'CONTACT_POINTS','HZ_STAGED_CONTACT_POINTS') table_name,
decode(a.entity_name,'PARTY','HZ_SRCH_PARTIES',
'PARTY_SITES','HZ_SRCH_PSITES','CONTACTS','HZ_SRCH_CONTACTS',
'CONTACT_POINTS','HZ_SRCH_CPTS')||'_N0'||substrb(staged_attribute_column,3) srch_index_name,
decode(a.entity_name,'PARTY','HZ_SRCH_PARTIES',
'PARTY_SITES','HZ_SRCH_PSITES','CONTACTS','HZ_SRCH_CONTACTS',
'CONTACT_POINTS','HZ_SRCH_CPTS') srch_table_name,
f.staged_attribute_column column_name
FROM hz_trans_attributes_vl a, hz_trans_functions_vl f
WHERE f.attribute_id = a.attribute_id
AND f.index_required_flag = 'Y'
AND a.entity_name = p_entity;
SELECT 1 FROM sys.all_indexes
WHERE INDEX_NAME=cp_index_name
AND TABLE_NAME=cp_table_name and owner = l_index_owner;
select index_tablespace
into ar_index_tbsp
from fnd_product_installations
where application_id = '222';
l_sql := 'select ' || proc ||
'(:attrval,:lang,:attr,:entity,:ctx) from dual';
l_sql := 'select ' || proc ||
'(:record_id,:entity,:attr,:ctx) from dual';
FOR FUNCS IN (SELECT PROCEDURE_NAME, a.ENTITY_NAME, a.ATTRIBUTE_NAME, f.TRANSFORMATION_NAME
FROM HZ_TRANS_FUNCTIONS_VL f, HZ_TRANS_ATTRIBUTES_VL a
WHERE a.ATTRIBUTE_ID = f.ATTRIBUTE_ID
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y' )
LOOP
BEGIN
l_custom := FALSE;
l_sql := 'select ' || FUNCS.PROCEDURE_NAME ||
'(:attrval,:lang,:attr,:entity) from dual';
FOR FUNCS IN (SELECT custom_attribute_procedure, ENTITY_NAME, ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a
WHERE source_table = 'CUSTOM' OR
custom_attribute_procedure is NOT NULL
AND EXISTS (select 1 from HZ_TRANS_FUNCTIONS_VL f
WHERE f.attribute_id = a.attribute_id
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'))
LOOP
BEGIN
l_custom := TRUE;
l_sql := 'select ' || FUNCS.custom_attribute_procedure ||
'(:record_id,:entity,:attr) from dual';