[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR c1 is select 'Y'
from hz_match_rule_primary a
where match_rule_id = p_match_rule_id
and a.attribute_id in (
select attribute_id
from hz_trans_attributes_b
where entity_name = p_et_name
and attribute_name = p_attr_name)
union
select 'Y'
from hz_match_rule_secondary a
where match_rule_id = p_match_rule_id
and a.attribute_id in (
select attribute_id
from hz_trans_attributes_b
where entity_name = p_et_name
and attribute_name = p_attr_name);
select original_system into l_os from hz_imp_batch_summary where batch_id = p_batch_id;
select batch_dedup_flag, batch_dedup_match_rule_id, registry_dedup_match_rule_id, addr_val_flag
into l_a, l_c, l_d, l_e
from hz_imp_batch_summary
where batch_id = p_batch_id;
log( ' Update party_sites since address validation was not run');
update HZ_SRCH_PSITES c set party_site_id = ( select b.party_site_id
from hz_imp_addresses_int a, HZ_IMP_ADDRESSES_SG b
where a.rowid = b.int_row_id
and c.int_row_id = a.rowid
and a.batch_id = p_batch_id
and a.batch_id = c.batch_id
and b.action_flag = 'I' ),
party_id = ( select b.party_id
from hz_imp_addresses_int a, HZ_IMP_ADDRESSES_SG b
where a.rowid = b.int_row_id
and c.int_row_id = a.rowid
and a.batch_id = p_batch_id
and a.batch_id = c.batch_id
and b.action_flag = 'I'
) where batch_id = p_batch_id;
log( ' Update party as same match rule being used');
update hz_srch_parties c set party_id = ( select b.party_id
from hz_imp_parties_int a, hz_imp_parties_sg b
where a.rowid = b.int_row_id
and c.int_row_id = a.rowid
and a.batch_id = p_batch_id
and a.batch_id = c.batch_id
and b.action_flag = 'I' );
log( ' Update contacts as same match rule being used');
update HZ_SRCH_CONTACTS c set party_id = ( select b.sub_id
from HZ_IMP_CONTACTS_INT a, HZ_IMP_CONTACTS_SG b
where a.rowid = b.int_row_id
and c.int_row_id = a.rowid
and a.batch_id = p_batch_id
and a.batch_id = c.batch_id
and b.action_flag = 'I' ),
org_contact_id = (select b.contact_id
from HZ_IMP_CONTACTS_INT a, HZ_IMP_CONTACTS_SG b
where a.rowid = b.int_row_id
and a.batch_id = p_batch_id
and a.batch_id = c.batch_id
and b.action_flag = 'I' );
log( ' Update contact_points as same match rule being used');
update HZ_SRCH_CPTS c set party_id = ( select b.party_id
from HZ_IMP_CONTACTPTS_INT a, HZ_IMP_CONTACTPTS_SG b
where a.rowid = b.int_row_id
and c.int_row_id = a.rowid
and a.batch_id = p_batch_id
and a.batch_id = c.batch_id
and b.action_flag = 'I' ),
party_site_id = ( select b.party_site_id
from HZ_IMP_CONTACTPTS_INT a, HZ_IMP_CONTACTPTS_SG b
where a.rowid = b.int_row_id
and c.int_row_id = a.rowid
and a.batch_id = p_batch_id
and a.batch_id = c.batch_id
and b.action_flag = 'I' ),
contact_point_id = ( select b.contact_point_id
from HZ_IMP_CONTACTPTS_INT a, HZ_IMP_CONTACTPTS_SG b
where a.rowid = b.int_row_id
and c.int_row_id = a.rowid
and a.batch_id = p_batch_id
and a.batch_id = c.batch_id
and b.action_flag = 'I' );
l_sql := 'select ' || proc ||
'(:attrval,:lang,:attr,:entity,:ctx) from dual';
l_sql_stmt := ' select /*+ INDEX(a) */ count(batch_id) from ' || p_table_name || ' a where batch_id <> :1 and rownum < 2 ';
l_sql_stmt := ' delete from ' || p_table_name || ' where batch_id = :1 ' ;
delete from HZ_INT_DUP_RESULTS where batch_id = p_batch_id;
l_sql_stmt := ' delete from ' ||l_owner || '.' || p_table_name || ' where batch_id = :1 ';
delete from HZ_IMP_INT_DEDUP_RESULTS where batch_id = p_batch_id;
delete from HZ_IMP_DUP_PARTIES where batch_id = p_batch_id;
delete from HZ_IMP_DUP_DETAILS where batch_id = p_batch_id;
select owner into l_owner from sys.all_objects
where object_name = p_object_name and OBJECT_TYPE = p_object_type and owner = l_owner1;
delete from hz_srch_psites a
where a.party_os || a.party_osr in (
select a.party_os || a.party_osr
from HZ_IMP_ADDRESSES_SG b
where a.party_os = b.party_orig_system
and a.party_osr = b.party_orig_system_reference
and b.action_flag = 'U'
and a.batch_id = p_batch_id);
delete from HZ_SRCH_CONTACTS c where c.contact_os ||c.contact_osr || c.batch_id in(
select b.contact_orig_system || b.contact_orig_system_reference || b.batch_id
from HZ_IMP_CONTACTS_SG a, HZ_IMP_CONTACTS_INT b
where a.int_row_id = b.rowid
and a.action_flag = 'U'
and b.contact_orig_system = c.contact_os
and b.contact_orig_system_reference = c.contact_osr
and b.batch_id = p_batch_id);
delete from HZ_SRCH_CPTS a
where a.party_os || a.party_osr in (
select a.party_os || a.party_osr
from HZ_IMP_CONTACTPTS_SG b
where a.party_os = b.party_orig_system
and a.party_osr = b.party_orig_system_reference
and b.action_flag = 'U'
and a.batch_id = p_batch_id);
select batch_dedup_flag, registry_dedup_flag, batch_dedup_match_rule_id, registry_dedup_match_rule_id, addr_val_flag
into l_a, l_b, l_c, l_d, l_e
from hz_imp_batch_summary
where batch_id = p_batch_id;
/*select owner into l_owner from sys.all_objects
where object_name = 'HZ_SRCH_PSITES' and OBJECT_TYPE = 'TABLE' and owner = l_owner1 ;*/
l_sqlstr := 'select owner from sys.all_tables
where table_name = ''HZ_SRCH_PSITES'' and owner = :p_owner ';
UPDATE hz_imp_work_units SET BATCH_DEDUP_STATUS = 'P', BATCH_DEDUP_STAGE = 1
WHERE batch_id = p_batch_id and BATCH_DEDUP_STATUS is null and rownum = 1
RETURNING rowid, FROM_ORIG_SYSTEM_REF, TO_ORIG_SYSTEM_REF into l_row_id, l_from_osr, l_to_osr;
UPDATE hz_imp_work_units SET BATCH_DEDUP_STATUS = 'E' where rowid = l_row_id;
UPDATE hz_imp_work_units SET BATCH_DEDUP_STATUS = 'C' where rowid = l_row_id;
UPDATE hz_imp_work_units SET BATCH_DEDUP_STATUS = 'P', BATCH_DEDUP_STAGE = 2
WHERE batch_id = p_batch_id AND BATCH_DEDUP_STAGE = 1 and ROWNUM = 1
RETURNING rowid, FROM_ORIG_SYSTEM_REF, TO_ORIG_SYSTEM_REF into l_row_id, l_from_osr, l_to_osr;
UPDATE hz_imp_work_units SET BATCH_DEDUP_STATUS = 'E' WHERE ROWID = l_row_id;
UPDATE hz_imp_work_units SET BATCH_DEDUP_STATUS = 'C' WHERE ROWID = l_row_id;
UPDATE HZ_IMP_BATCH_SUMMARY set BATCH_DEDUP_STATUS = 'PROCESSING' where batch_id = p_batch_id;
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 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');
UPDATE HZ_IMP_BATCH_SUMMARY set BATCH_DEDUP_STATUS = 'COMPLETED' where batch_id = p_batch_id;
UPDATE HZ_IMP_BATCH_SUMMARY set BATCH_DEDUP_STATUS = 'ERROR' where batch_id = p_batch_id;
PROCEDURE get_select_str(
p_entity_name IN VARCHAR2,
p_rule_id IN NUMBER,
p_sql_str IN OUT NOCOPY VARCHAR2,
p_et_point IN VARCHAR2,
p_std_chk IN NUMBER
) IS
is_first BOOLEAN := TRUE;
l_procedure_name VARCHAR2(30) := '.GET_SELECT_STR' ;
FOR TX IN (select attribute_name
from hz_trans_attributes_b
where entity_name = p_entity_name
and HZ_IMP_DQM_STAGE.EXIST_COL(attribute_name, p_entity_name) = 'Y'
and custom_attribute_procedure is null
/* and attribute_name not in
('SIC_CODE', 'SIC_CODE_TYPE', 'TAX_NAME', 'CATEGORY_CODE', 'IDENTIFYING_ADDRESS_FLAG', 'STATUS', 'PRIMARY_FLAG', 'REFERENCE_USE_FLAG' ) */
and attribute_id in ( select attribute_id
from hz_match_rule_primary
where match_rule_id = p_rule_id
union
select attribute_id
from hz_match_rule_secondary
where match_rule_id = p_rule_id)
) LOOP
IF is_first THEN
is_first := false;
p_sql_str := ' select ' || chk_is_std(TX.attribute_name) ;
p_sql_str := ' select decode(a.party_type, ''ORGANIZATION'', a.organization_name, ''PERSON'', a.person_first_name || '' '' || a.person_last_name) as PARTY_NAME ';
p_sql_str := ' select a.' || TX.attribute_name ;
p_sql_str := 'select a.party_orig_system';
p_sql_str := 'select a.party_orig_system, a.party_orig_system_reference, a.cp_orig_system, a.cp_orig_system_reference, a.site_orig_system';
p_sql_str := 'select a.obj_orig_system, a.obj_orig_system_reference, a.contact_orig_system, a.contact_orig_system_reference, b.party_action_flag, a.rowid, b.obj_id';
p_sql_str := 'select a.party_orig_system';
p_sql_str := 'select a.party_orig_system, a.party_orig_system_reference, a.cp_orig_system, a.cp_orig_system_reference, a.site_orig_system, a.site_orig_system_reference, a.rowid, a.contact_point_type';
p_sql_str := 'select a.obj_orig_system, a.obj_orig_system_reference, a.contact_orig_system, a.contact_orig_system_reference, a.rowid';
END get_select_str;
FOR TX IN ( select attribute_id || 'E' as STAGED_ATTRIBUTE_COLUMN
from hz_trans_attributes_b
where attribute_id in (select attribute_id
from hz_match_rule_primary
where match_rule_id = p_rule_id
union
select attribute_id
from hz_match_rule_secondary
where match_rule_id = p_rule_id)
and entity_name = p_entity_name
and HZ_IMP_DQM_STAGE.EXIST_COL(attribute_name, p_entity_name) = 'Y'
and custom_attribute_procedure is null
/* and attribute_name not in
('SIC_CODE', 'SIC_CODE_TYPE', 'TAX_NAME', 'CATEGORY_CODE', 'IDENTIFYING_ADDRESS_FLAG', 'STATUS', 'PRIMARY_FLAG', 'REFERENCE_USE_FLAG' ) */
) LOOP
IF is_first THEN
is_first := false;
PROCEDURE get_cust_insert_str (
p_entity_name IN VARCHAR2,
p_match_rule_id IN NUMBER,
p_sql_str IN OUT NOCOPY VARCHAR2,
p_et_point IN VARCHAR2,
p_attr_name IN VARCHAR2,
p_purpose VARCHAR2 -- can be taken out
) IS
is_first BOOLEAN := TRUE;
l_procedure_name VARCHAR2(30) := '.GET_CUST_INSERT_STR' ;
FOR TX IN ( select STAGED_ATTRIBUTE_COLUMN
from hz_trans_functions_b
where attribute_id in (select attribute_id
from hz_trans_attributes_b
where attribute_name = p_attr_name
and entity_name = p_entity_name)
and function_id in (select function_id
from hz_match_rule_primary e, hz_primary_trans d
where match_rule_id = p_match_rule_id
and e.PRIMARY_ATTRIBUTE_ID = d.PRIMARY_ATTRIBUTE_ID
union
select function_id
from hz_match_rule_secondary g, hz_secondary_trans f
where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID
and match_rule_id = p_match_rule_id)
order by STAGED_ATTRIBUTE_COLUMN
) LOOP
IF is_first THEN
is_first := false;
END get_cust_insert_str;
PROCEDURE get_insert_str (
p_entity_name IN VARCHAR2,
p_rule_id IN NUMBER,
p_sql_str IN OUT NOCOPY VARCHAR2,
p_et_point IN VARCHAR2
) IS
is_first BOOLEAN := TRUE;
l_procedure_name VARCHAR2(30) := '.GET_INSERT_STR' ;
FOR TX IN ( select STAGED_ATTRIBUTE_COLUMN
from hz_trans_functions_b
where function_id in
(select function_id
from hz_match_rule_primary e, hz_primary_trans d
where match_rule_id = p_rule_id
and e.PRIMARY_ATTRIBUTE_ID = d.PRIMARY_ATTRIBUTE_ID
union
select function_id
from hz_match_rule_secondary g, hz_secondary_trans f
where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID
and match_rule_id = p_rule_id)
and nvl(active_flag, 'Y') <> 'N'
and staged_attribute_table = p_entity_name
and attribute_id not in (
select attribute_id
from hz_trans_attributes_b
where custom_attribute_procedure is not null
or HZ_IMP_DQM_STAGE.EXIST_COL(attribute_name, p_entity_name) = 'N'
/* or attribute_name in
('SIC_CODE', 'SIC_CODE_TYPE', 'TAX_NAME', 'CATEGORY_CODE', 'IDENTIFYING_ADDRESS_FLAG', 'STATUS', 'PRIMARY_FLAG', 'REFERENCE_USE_FLAG' ) */
) order by STAGED_ATTRIBUTE_COLUMN
) LOOP
IF is_first THEN
is_first := false;
get_cust_insert_str ('PARTY_SITES', p_rule_id, l_sql_str, 'PS', 'ADDRESS', null);
get_cust_insert_str ('CONTACT_POINTS', p_rule_id, l_sql_str, 'CP', 'RAW_PHONE_NUMBER', null);
get_cust_insert_str ('CONTACTS', p_rule_id, l_sql_str, NULL, 'CONTACT_NAME', null);
get_cust_insert_str ('PARTY_SITES', p_rule_id, l_sql_str, 'PS', 'ADDRESS', null);
get_cust_insert_str ('CONTACT_POINTS', p_rule_id, l_sql_str, 'CP', 'RAW_PHONE_NUMBER', null);
get_cust_insert_str ('CONTACTS', p_rule_id, l_sql_str, NULL, 'CONTACT_NAME', null);
END get_insert_str;
PROCEDURE get_cust_insert_val_str (
p_entity_name IN VARCHAR2,
p_rule_id IN NUMBER,
p_sql_str IN OUT NOCOPY VARCHAR2,
p_et_point IN VARCHAR2,
p_attr_name IN VARCHAR2
) IS
is_first BOOLEAN := TRUE;
l_procedure_name VARCHAR2(30) := '.GET_CUST_INSERT_VAL_STR' ;
FOR TX IN ( select STAGED_ATTRIBUTE_COLUMN
from hz_trans_functions_b
where attribute_id in (select attribute_id
from hz_trans_attributes_b
where attribute_name = p_attr_name
and entity_name = p_entity_name)
and function_id in (select function_id
from hz_match_rule_primary e, hz_primary_trans d
where match_rule_id = p_rule_id
and e.PRIMARY_ATTRIBUTE_ID = d.PRIMARY_ATTRIBUTE_ID
union
select function_id
from hz_match_rule_secondary g, hz_secondary_trans f
where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID
and match_rule_id = p_rule_id)
order by STAGED_ATTRIBUTE_COLUMN
) LOOP
IF is_first THEN
is_first := false;
END get_cust_insert_val_str;
PROCEDURE get_insert_val_str (
p_entity_name IN VARCHAR2,
p_rule_id IN NUMBER,
p_sql_str IN OUT NOCOPY VARCHAR2,
p_et_point IN VARCHAR2
) IS
is_first BOOLEAN := TRUE;
l_procedure_name VARCHAR2(30) := '.GET_INSERT_VAL_STR' ;
FOR TX IN ( select STAGED_ATTRIBUTE_COLUMN
from hz_trans_functions_b
where function_id in (select function_id
from hz_match_rule_primary e, hz_primary_trans d
where e.PRIMARY_ATTRIBUTE_ID = d.PRIMARY_ATTRIBUTE_ID
and match_rule_id = p_rule_id
union
select function_id
from hz_match_rule_secondary g, hz_secondary_trans f
where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID
and match_rule_id = p_rule_id)
and nvl(active_flag, 'Y') <> 'N'
and staged_attribute_table = p_entity_name
and attribute_id not in (
select attribute_id
from hz_trans_attributes_b
where custom_attribute_procedure is not null
or HZ_IMP_DQM_STAGE.EXIST_COL(attribute_name, p_entity_name) = 'N'
) order by STAGED_ATTRIBUTE_COLUMN
) LOOP
IF is_first THEN
is_first := false;
get_cust_insert_val_str ('PARTY_SITES', p_rule_id, l_sql_str, 'PS', 'ADDRESS');
get_cust_insert_val_str ('CONTACT_POINTS', p_rule_id, l_sql_str, 'CP', 'RAW_PHONE_NUMBER');
get_cust_insert_val_str ('CONTACTS', p_rule_id, l_sql_str, 'CT', 'CONTACT_NAME');
get_cust_insert_val_str ('PARTY_SITES', p_rule_id, l_sql_str, 'PS', 'ADDRESS');
get_cust_insert_val_str ('CONTACT_POINTS', p_rule_id, l_sql_str, 'CP', 'RAW_PHONE_NUMBER');
get_cust_insert_val_str ('CONTACTS', p_rule_id, l_sql_str, 'CT', 'CONTACT_NAME');
END get_insert_val_str;
CURSOR c1 is select 'Y'
from hz_match_rule_primary a, hz_match_rule_secondary b
where a.match_rule_id = b.match_rule_id
and a.match_rule_id = p_rule_id
-- check if one really needs the below condition
-- and a.attribute_id = b.attribute_id
and a.attribute_id in (
select attribute_id
from hz_trans_attributes_b
where entity_name = 'PARTY_SITES'
and attribute_name = 'ADDRESS');
CURSOR c1 is select 'Y'
from hz_match_rule_primary a, hz_match_rule_secondary b
where a.match_rule_id = b.match_rule_id
and a.match_rule_id = p_rule_id
-- check if one really needs the below condition
-- and a.attribute_id = b.attribute_id
and a.attribute_id in (
select attribute_id
from hz_trans_attributes_b
where entity_name = 'CONTACT_POINTS'
and attribute_name = 'RAW_PHONE_NUMBER');
CURSOR c1 is select 'Y'
from hz_match_rule_primary a, hz_match_rule_secondary b
where a.match_rule_id = b.match_rule_id
and a.match_rule_id = p_rule_id
-- check if one really needs the below condition
-- and a.attribute_id = b.attribute_id
and a.attribute_id in (
select attribute_id
from hz_trans_attributes_b
where entity_name = 'CONTACTS'
and attribute_name = 'CONTACT_NAME' );
CURSOR c1 is select 'Y'
from hz_match_rule_primary a, hz_match_rule_secondary b
where a.match_rule_id = b.match_rule_id
and a.match_rule_id = p_rule_id
and a.attribute_id = b.attribute_id
and a.attribute_id in (
select attribute_id
from hz_trans_attributes_b
where entity_name = 'CONTACTS'
and attribute_name = 'CONTACT_NAME' );
select ATTRIBUTE_NAME, USER_DEFINED_ATTRIBUTE_NAME, PROCEDURE_NAME, STAGED_ATTRIBUTE_COLUMN, b.attribute_id
from hz_trans_functions_b b, hz_trans_attributes_vl c
where b.attribute_id = c.attribute_id
--Fix for bug 4669257. Removing the hardcoded reference below.
--and userenv('LANG') = 'US'
and b.function_id in (select function_id
from hz_match_rule_primary d, hz_primary_trans e
where match_rule_id = p_rule_id
and d.primary_attribute_id = e.primary_attribute_id
union
select function_id
from hz_match_rule_secondary f, hz_secondary_trans g
where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID -- b.attribute_id = c.attribute_id
and match_rule_id = p_rule_id)
and nvl(active_flag, 'Y') <> 'N'
and entity_name = p_et_name
and attribute_name = p_attr_name;
select STAGED_ATTRIBUTE_COLUMN, b.attribute_id
from hz_trans_functions_b b, hz_trans_attributes_vl c
where b.attribute_id = c.attribute_id
--Fix for bug 4669257. Removing the hardcoded reference below.
--and userenv('LANG') = 'US'
and b.function_id in
(select function_id
from hz_match_rule_primary d, hz_primary_trans e
where match_rule_id = p_rule_id
and d.primary_attribute_id = e.primary_attribute_id
union
select function_id
from hz_match_rule_secondary f, hz_secondary_trans g
where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID -- b.attribute_id = c.attribute_id
and match_rule_id = p_rule_id)
and nvl(active_flag, 'Y') <> 'N'
and entity_name = p_entity_name
and custom_attribute_procedure is null
and HZ_IMP_DQM_STAGE.EXIST_COL(attribute_name, p_entity_name) = 'Y'
order by STAGED_ATTRIBUTE_COLUMN
) LOOP
-- DO THIS IF AND ONLY IF THIS ATTRIBUTE IS A CONDITION ATTRIBUTE
IF HZ_WORD_CONDITIONS_PKG.is_a_cond_attrib( TX.ATTRIBUTE_ID)
THEN
-- ONE TIME ONLY
IF NONE
THEN
l_trans_list(i) := '----------- SETTING GLOBAL CONDITION RECORD AT THE ' || p_entity_name || ' LEVEL ---------';
FOR TX IN ( select ATTRIBUTE_NAME, USER_DEFINED_ATTRIBUTE_NAME, PROCEDURE_NAME, STAGED_ATTRIBUTE_COLUMN, b.attribute_id
from hz_trans_functions_b b, hz_trans_attributes_vl c
where b.attribute_id = c.attribute_id
--Fix for bug 4669257. Removing the hardcoded reference below.
--and userenv('LANG') = 'US'
and b.function_id in
(select function_id
from hz_match_rule_primary d, hz_primary_trans e
where match_rule_id = p_rule_id
and d.primary_attribute_id = e.primary_attribute_id
union
select function_id
from hz_match_rule_secondary f, hz_secondary_trans g
where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID -- b.attribute_id = c.attribute_id
and match_rule_id = p_rule_id)
and nvl(active_flag, 'Y') <> 'N'
and entity_name = p_entity_name
and custom_attribute_procedure is null
and HZ_IMP_DQM_STAGE.EXIST_COL(attribute_name, p_entity_name) = 'Y'
-- and attribute_name not in
-- ('SIC_CODE', 'SIC_CODE_TYPE', 'TAX_NAME', 'CATEGORY_CODE', 'IDENTIFYING_ADDRESS_FLAG', 'STATUS', 'PRIMARY_FLAG', 'REFERENCE_USE_FLAG' )
order by STAGED_ATTRIBUTE_COLUMN
) LOOP
IF has_trx_context(TX.PROCEDURE_NAME) THEN
l_str := ' H_' || TX.STAGED_ATTRIBUTE_COLUMN || '(I) := ' || TX.PROCEDURE_NAME || '(H_' || TX.ATTRIBUTE_ID || 'E(I), NULL, '''|| TX.ATTRIBUTE_NAME || ''', '''|| p_entity_name || ''', ''SEARCH'' );' ;
get_select_str('PARTY', p_rule_id, l_sel_str, 'INT_INT',null);
l_trans_list.DELETE(I);
l(' INSERT INTO HZ_SRCH_PARTIES (');
get_insert_str('HZ_STAGED_PARTIES', p_rule_id, l_sel_str, 'INT_INT');
get_insert_val_str('HZ_STAGED_PARTIES', p_rule_id, l_sel_str, 'INT_INT');
get_select_str('PARTY', p_rule_id, l_sel_str, 'INT_TCA', NULL);
/* select a.organization_name, a.duns_number_c, a.tax_reference,
a.party_orig_system, a.party_orig_system_reference
*/
l(' from hz_imp_parties_int a ');
l_trans_list.DELETE(I);
l(' INSERT INTO HZ_SRCH_PARTIES ( ');
get_insert_str('HZ_STAGED_PARTIES', p_rule_id, l_sel_str, 'INT_TCA');
get_insert_val_str('HZ_STAGED_PARTIES', p_rule_id, l_sel_str, 'INT_TCA');
get_select_str('PARTY_SITES', p_rule_id, l_sel_str, 'INT_INT', DO_STD_CHK);
select a.party_orig_system, a.party_orig_system_reference,
a.site_orig_system, a.site_orig_system_reference,
b.party_id, b.party_site_id, a.address1, a.postal_code
*/
l(' from hz_imp_addresses_int a, hz_imp_addresses_sg b ');
get_select_str('PARTY_SITES', p_rule_id, l_sel_str, 'INT_INT', DO_STD_CHK);
dbms_output.put_line('select_str (PARTY_SITES) = ' || l_sel_str);
l_trans_list.DELETE(I);
l(' INSERT INTO HZ_SRCH_PSITES ( ');
get_insert_str('HZ_STAGED_PARTY_SITES', p_rule_id, l_sel_str, 'INT_INT');
get_insert_val_str('HZ_STAGED_PARTY_SITES', p_rule_id, l_sel_str, 'INT_INT');
get_select_str('PARTY_SITES', p_rule_id, l_sel_str, 'INT_TCA', null);
/* select a.party_orig_system, a.party_orig_system_reference,
a.site_orig_system, a.site_orig_system_reference,
a.address1, a.postal_code */
l(' from hz_imp_addresses_int a ');
l_trans_list.DELETE(I);
l(' INSERT INTO HZ_SRCH_PSITES ( ');
get_insert_str('HZ_STAGED_PARTY_SITES', p_rule_id, l_sel_str, 'INT_TCA');
get_insert_val_str('HZ_STAGED_PARTY_SITES', p_rule_id, l_sel_str, 'INT_TCA');
l(' select batch_dedup_flag, registry_dedup_flag, batch_dedup_match_rule_id, registry_dedup_match_rule_id, addr_val_flag ');
l(' CURSOR c1 is select ''Y'' ');
l(' select attribute_id ');
l(' select attribute_id ');
get_select_str('CONTACTS', p_rule_id, l_sel_str, 'INT_INT', NULL);
/* l(' select a.sub_orig_system, a.sub_orig_system_reference, ');
get_select_str('CONTACTS', p_rule_id, l_sel_str, 'INT_INT', NULL);
/* l(' select a.sub_orig_system, a.sub_orig_system_reference, ');
get_select_str('CONTACTS', p_rule_id, l_sel_str, 'INT_INT', NULL);
/* l(' select a.sub_orig_system, a.sub_orig_system_reference, ');
l(' CURSOR c1 is select ''Y'' ');
l(' select attribute_id ');
l(' select attribute_id ');
get_select_str('CONTACTS', p_rule_id, l_sel_str, 'INT_TCA', NULL);
/* select a.sub_orig_system, a.sub_orig_system_reference,
a.site_orig_system, a.site_orig_system_reference,
a.contact_orig_system, a.contact_orig_system_reference,
a.contact_number, a.title, c.person_first_name || ' ' || c.person_last_name as person_name
*/
l(' from HZ_IMP_CONTACTS_INT a, HZ_IMP_PARTIES_INT c ');
get_select_str('CONTACTS', p_rule_id, l_sel_str, 'INT_TCA', NULL);
dbms_output.put_line('get_select_str (CONTACTS int_tca)' || l_sel_str);
/* select a.sub_orig_system, a.sub_orig_system_reference,
a.site_orig_system, a.site_orig_system_reference,
a.contact_orig_system, a.contact_orig_system_reference,
a.contact_number, a.title, c.party_name as person_name */
/* l(' from HZ_IMP_CONTACTS_INT a, hz_parties c ');
get_select_str('CONTACTS', p_rule_id, l_sel_str, 'INT_TCA', NULL);
/* select a.sub_orig_system, a.sub_orig_system_reference,
a.site_orig_system, a.site_orig_system_reference,
a.contact_orig_system, a.contact_orig_system_reference,
a.contact_number, a.title, null person_name */
l(' from HZ_IMP_CONTACTS_INT a ');
l_trans_list.DELETE(I);
l(' INSERT INTO HZ_SRCH_CONTACTS ( ');
get_insert_str('HZ_STAGED_CONTACTS', p_rule_id, l_sel_str, 'INT_INT');
get_insert_val_str('HZ_STAGED_CONTACTS', p_rule_id, l_sel_str, 'INT_INT');
l_trans_list.DELETE(I);
l(' INSERT INTO HZ_SRCH_CONTACTS ( ');
get_insert_str('HZ_STAGED_CONTACTS', p_rule_id, l_sel_str, 'INT_TCA');
get_insert_val_str('HZ_STAGED_CONTACTS', p_rule_id, l_sel_str, 'INT_TCA');
get_select_str('CONTACT_POINTS', p_rule_id, l_sel_str, 'INT_INT', NULL);
/* select a.email_address, a.party_orig_system_reference,
a.party_orig_system, a.cp_orig_system, a.cp_orig_system_reference,
a.site_orig_system, a.site_orig_system_reference, b.party_site_id,
b.contact_point_id, b.party_id */
l(' from HZ_IMP_CONTACTPTS_INT a, HZ_IMP_CONTACTPTS_SG b --');
l_trans_list.DELETE(I);
l(' INSERT INTO HZ_SRCH_CPTS ( ');
get_insert_str('HZ_STAGED_CONTACT_POINTS', p_rule_id, l_sel_str, 'INT_INT');
get_insert_val_str('HZ_STAGED_CONTACT_POINTS', p_rule_id, l_sel_str, 'INT_INT');
get_select_str('CONTACT_POINTS', p_rule_id, l_sel_str, 'INT_TCA', NULL);
/* select a.email_address, a.party_orig_system_reference,
a.party_orig_system, a.cp_orig_system, a.cp_orig_system_reference,
a.site_orig_system, a.site_orig_system_reference
*/
l(' from HZ_IMP_CONTACTPTS_INT a ');
l_trans_list.DELETE(I);
l(' INSERT INTO HZ_SRCH_CPTS ( ');
get_insert_str('HZ_STAGED_CONTACT_POINTS', p_rule_id, l_sel_str, 'INT_TCA');
get_insert_val_str('HZ_STAGED_CONTACT_POINTS', p_rule_id, l_sel_str, 'INT_TCA');
FOR TX IN ( select STAGED_ATTRIBUTE_COLUMN
from hz_trans_functions_b
where attribute_id in (select attribute_id
from hz_match_rule_primary
where match_rule_id = p_rule_id)
union
select STAGED_ATTRIBUTE_COLUMN
from hz_trans_functions_b
where attribute_id in (select attribute_id
from hz_match_rule_secondary
where match_rule_id = p_rule_id)
order by STAGED_ATTRIBUTE_COLUMN
) LOOP
l( ' H_' || TX.STAGED_ATTRIBUTE_COLUMN || ' CharList2000;');
FOR TX2 IN ( select attribute_id
from hz_trans_attributes_vl
--Fix for bug 4669257. Removing the hardcoded reference below.
-- where userenv('LANG') = 'US'
where attribute_id in (select attribute_id
from hz_match_rule_primary
where match_rule_id = p_rule_id
union
select attribute_id
from hz_match_rule_secondary
where match_rule_id = p_rule_id)
and custom_attribute_procedure is null
) LOOP
l( ' H_' || TX2.attribute_id || 'E CharList2000;');
/* l_sql_stmt := ' select count(distinct batch_id) from ' || p_table_name || ' where batch_id <> :1';
FOR TX5 IN ( select STAGED_ATTRIBUTE_COLUMN
from hz_trans_functions_b
where attribute_id in (select attribute_id
from hz_trans_attributes_b
where attribute_name = 'CONTACT_NAME'
and entity_name = 'CONTACTS')
and function_id in (select function_id
from hz_match_rule_primary e, hz_primary_trans d
where match_rule_id = p_rule_id
and e.PRIMARY_ATTRIBUTE_ID = d.PRIMARY_ATTRIBUTE_ID
union
select function_id
from hz_match_rule_secondary g, hz_secondary_trans f
where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID
and match_rule_id = p_rule_id)
order by STAGED_ATTRIBUTE_COLUMN ) LOOP
-- l( ' H_' || TX.STAGED_ATTRIBUTE_COLUMN || ' CharList2000;');
FOR TX3 IN ( select STAGED_ATTRIBUTE_COLUMN
from hz_trans_functions_b
where attribute_id in (select attribute_id
from hz_trans_attributes_b
where attribute_name = 'RAW_PHONE_NUMBER'
and entity_name = 'CONTACT_POINTS')
and function_id in (select function_id
from hz_match_rule_primary e, hz_primary_trans d
where match_rule_id = p_rule_id
and e.PRIMARY_ATTRIBUTE_ID = d.PRIMARY_ATTRIBUTE_ID
union
select function_id
from hz_match_rule_secondary g, hz_secondary_trans f
where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID
and match_rule_id = p_rule_id)
order by STAGED_ATTRIBUTE_COLUMN ) LOOP
-- l( ' H_' || TX.STAGED_ATTRIBUTE_COLUMN || ' CharList2000;');
FOR TX4 IN ( select STAGED_ATTRIBUTE_COLUMN
from hz_trans_functions_b
where attribute_id in (select attribute_id
from hz_trans_attributes_b
where attribute_name = 'ADDRESS'
and entity_name = 'PARTY_SITES')
and function_id in (select function_id
from hz_match_rule_primary e, hz_primary_trans d
where match_rule_id = p_rule_id
and e.PRIMARY_ATTRIBUTE_ID = d.PRIMARY_ATTRIBUTE_ID
union
select function_id
from hz_match_rule_secondary g, hz_secondary_trans f
where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID
and match_rule_id = p_rule_id)
order by STAGED_ATTRIBUTE_COLUMN ) LOOP
-- l( ' H_' || TX.STAGED_ATTRIBUTE_COLUMN || ' CharList2000;');
CURSOR c1 is select 'Y'
from user_synonyms syn, dba_tab_columns col
where syn.synonym_name = 'HZ_IMP_ADDRESSES_INT'
and col.owner = syn.table_owner
and col.table_name = syn.table_name
and col.column_name = l_attribute_name
and col.owner = l_owner;
select distinct 'Y' into x_bool
from hz_trans_attributes_vl
--Fix for bug 4669257. Removing the hardcoded reference below.
--where userenv('LANG') = 'US'
where entity_name = p_entity_name
and custom_attribute_procedure is null
and HZ_IMP_DQM_STAGE.EXIST_COL(attribute_name, p_entity_name) = 'Y'
-- and attribute_name not in ('SIC_CODE', 'SIC_CODE_TYPE', 'TAX_NAME', 'CATEGORY_CODE', 'IDENTIFYING_ADDRESS_FLAG', 'STATUS', 'PRIMARY_FLAG', 'REFERENCE_USE_FLAG' )
and attribute_id in ( select attribute_id
from hz_match_rule_primary
where match_rule_id = p_rule_id
union
select attribute_id
from hz_match_rule_secondary
where match_rule_id = p_rule_id);
l_p_select_list VARCHAR2(1000);
l_ps_select_list VARCHAR2(1000);
l_c_select_list VARCHAR2(1000);
l_cpt_select_list VARCHAR2(1000);
EXECUTE IMMEDIATE 'select '||attr_name||' from HZ_IMP_PARTIES_INT where rownum=1';
EXECUTE IMMEDIATE 'select '||attr_name||' from HZ_IMP_ADDRESSES_INT where rownum=1';
EXECUTE IMMEDIATE 'select '||attr_name||' from HZ_IMP_CONTACTS_INT where rownum=1';
EXECUTE IMMEDIATE 'select '||attr_name||' from hz_imp_parties_int where rownum=1';
EXECUTE IMMEDIATE 'select '||attr_name||' from HZ_IMP_CONTACTPTS_INT where rownum=1';
SELECT 1
FROM hz_match_rule_primary p, hz_primary_trans pt, hz_trans_functions_vl f
WHERE p.match_rule_id = p_rule_id
AND pt.PRIMARY_ATTRIBUTE_ID = p.PRIMARY_ATTRIBUTE_ID
AND f.function_id = pt.function_id
--Fix for bug 4669257. Removing the hardcoded reference below.
--AND userenv('LANG') = 'US'
AND nvl(f.ACTIVE_FLAG,'Y') = 'N'
UNION
SELECT 1
FROM hz_match_rule_secondary s, hz_secondary_trans pt, hz_trans_functions_vl f
WHERE s.match_rule_id = p_rule_id
AND pt.SECONDARY_ATTRIBUTE_ID = s.SECONDARY_ATTRIBUTE_ID
AND f.function_id = pt.function_id
--Fix for bug 4669257. Removing the hardcoded reference below.
--AND userenv('LANG') = 'US'
AND nvl(f.ACTIVE_FLAG,'Y') = 'N';
SELECT 1 INTO l_batch_flag
FROM HZ_MATCH_RULES_VL
WHERE match_rule_id = l_rule_id;
UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'C' WHERE MATCH_RULE_ID = l_rule_id;
UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = l_rule_id;
UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = l_rule_id;
UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = l_rule_id;