[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_insert_update_flag INSERT_UPDATE_FLAG;
l_operation INSERT_UPDATE_FLAG;
CURSOR c_denorm_rel IS select sub_id,
decode(rs.relationship_code, 'COMPETITOR_OF','Y','N') comp_flag,
decode(rs.relationship_code, 'REFERENCE_FOR' ,'Y','N') ref_flag,
decode(rs.relationship_code, 'PARTNER_OF' ,'Y','N') par_flag
from hz_imp_relships_sg rs
where rs.batch_mode_flag = p_batch_mode_flag
and rs.batch_id = p_batch_id
and rs.action_flag = 'I'
and rs.sub_orig_system = p_os
and rs.sub_orig_system_reference between p_from_osr and p_to_osr
and rs.relationship_code IN ('COMPETITOR_OF','REFERENCE_FOR','PARTNER_OF');
UPDATE hz_parties set
competitor_flag = decode(l_comp_flag(i),'Y','Y',competitor_flag),
reference_use_flag = decode(l_ref_flag(i) ,'Y','Y',reference_use_flag),
third_party_flag = decode(l_par_flag(i) ,'Y','Y',third_party_flag)
WHERE party_id = l_subject_id(i)
AND request_id = p_request_id;
UPDATE hz_parties set
competitor_flag = decode(l_comp_flag(i),'Y','Y',competitor_flag),
reference_use_flag = decode(l_ref_flag(i) ,'Y','Y',reference_use_flag),
third_party_flag = decode(l_par_flag(i) ,'Y','Y',third_party_flag)
WHERE party_id = l_subject_id(i)
AND request_id IN (SELECT main_conc_req_id FROM hz_imp_batch_details
WHERE batch_id = p_batch_id);
l_new_sql varchar2(1300) := 'SELECT p.party_id
,p.person_title
,p.person_first_name
,p.person_middle_name
,p.person_last_name
,p.person_name_suffix
,p.known_as
,p.person_first_name_phonetic
,pf.middle_name_phonetic
,p.person_last_name_phonetic
FROM HZ_PARTIES p, HZ_PERSON_PROFILES pf, HZ_IMP_PARTIES_SG ps
WHERE p.request_id = :p_request_id
AND p.party_type = ''PERSON''
AND p.party_id = pf.party_id
AND pf.effective_end_date is NULL
AND p.party_id = ps.party_id
AND ps.batch_id = :p_batch_id
AND ps.party_orig_system = :p_os
AND ps.party_orig_system_reference between :p_from_osr and :p_to_osr
AND ps.batch_mode_flag = :p_batch_mode_flag';
l_rerun_sql varchar2(1300) := 'SELECT p.party_id
,p.person_title
,p.person_first_name
,p.person_middle_name
,p.person_last_name
,p.person_name_suffix
,p.known_as
,p.person_first_name_phonetic
,pf.middle_name_phonetic
,p.person_last_name_phonetic
FROM HZ_PARTIES p, HZ_PERSON_PROFILES pf, HZ_IMP_PARTIES_SG ps, hz_imp_batch_details bd
WHERE p.request_id = bd.main_conc_req_id
AND bd.batch_id = ps.batch_id
AND p.party_type = ''PERSON''
AND p.party_id = pf.party_id
AND pf.effective_end_date is NULL
AND p.party_id = ps.party_id
AND ps.batch_id = :p_batch_id
AND ps.party_orig_system = :p_os
AND ps.party_orig_system_reference between :p_from_osr and :p_to_osr
AND ps.batch_mode_flag = :p_batch_mode_flag';
UPDATE hz_person_profiles
SET person_name = l_person_name
WHERE party_id = l_party_id(i)
AND effective_end_date is NULL;
UPDATE hz_parties
SET party_name = substrb(l_person_name,1,360)
WHERE party_id = l_party_id(i);
l_new_sql varchar2(1100) := 'SELECT p.party_id
,p.party_type
,p.party_name
,p.person_first_name
,p.person_last_name
,null record_id
,''PARTY'' entity
,decode(ps.action_flag, ''I'', ''C'', ps.action_flag) operation_flag
FROM hz_parties p, HZ_IMP_PARTIES_SG ps
WHERE p.request_id = :p_request_id
AND p.party_type IN (''ORGANIZATION'',''PERSON'',''GROUP'')
AND p.party_id = ps.party_id
AND ps.batch_id = :p_batch_id
AND ps.party_orig_system = :p_os
AND ps.party_orig_system_reference between :p_from_osr and :p_to_osr
AND ps.batch_mode_flag = :p_batch_mode_flag';
l_rerun_sql varchar2(1100) := 'SELECT p.party_id
,p.party_type
,p.party_name
,p.person_first_name
,p.person_last_name
,null record_id
,''PARTY'' entity
,decode(ps.action_flag, ''I'', ''C'', ps.action_flag) operation_flag
FROM hz_parties p, HZ_IMP_PARTIES_SG ps, hz_imp_batch_details bd
WHERE p.request_id = bd.main_conc_req_id
AND bd.batch_id = ps.batch_id
AND p.party_type IN (''ORGANIZATION'',''PERSON'',''GROUP'')
AND p.party_id = ps.party_id
AND ps.batch_id = :p_batch_id
AND ps.party_orig_system = :p_os
AND ps.party_orig_system_reference between :p_from_osr and :p_to_osr
AND ps.batch_mode_flag = :p_batch_mode_flag';
UPDATE HZ_PARTIES
SET customer_key = l_key
WHERE party_id = l_party_id(i);
l_new_sql varchar2(1300) := 'SELECT l.location_id
,l.address1
,l.address2
,l.address3
,l.address4
,l.postal_code
,null party_id
,ps.party_site_id record_id
,''PARTY_SITES'' entity
,decode(addr_sg.action_flag, ''I'', ''C'', addr_sg.action_flag) operation_flag
,null party_type
FROM hz_locations l, hz_party_sites ps,
hz_imp_addresses_sg addr_sg
WHERE l.request_id = :p_request_id
and l.location_id = ps.location_id
and addr_sg.batch_id = :p_batch_id
and addr_sg.batch_mode_flag = :p_batch_mode_flag
and addr_sg.party_orig_system = :p_os
and addr_sg.party_orig_system_reference between :p_from_osr and :p_to_osr
and addr_sg.party_site_id = ps.party_site_id';
l_rerun_sql varchar2(1300) := 'SELECT l.location_id
,l.address1
,l.address2
,l.address3
,l.address4
,l.postal_code
,null party_id
,ps.party_site_id record_id
,''PARTY_SITES'' entity
,decode(addr_sg.action_flag, ''I'', ''C'', addr_sg.action_flag) operation_flag
,null party_type
FROM hz_locations l, hz_party_sites ps,
hz_imp_addresses_sg addr_sg, hz_imp_batch_details bd
WHERE l.request_id = bd.main_conc_req_id
and bd.batch_id = addr_sg.batch_id
and l.location_id = ps.location_id
and addr_sg.batch_id = :p_batch_id
and addr_sg.batch_mode_flag = :p_batch_mode_flag
and addr_sg.party_orig_system = :p_os
and addr_sg.party_orig_system_reference between :p_from_osr and :p_to_osr
and addr_sg.party_site_id = ps.party_site_id';
UPDATE HZ_LOCATIONS
SET address_key = l_key
WHERE location_id = l_location_id(i);
SELECT r.object_id,r.subject_id, r.relationship_code
FROM hz_imp_parties_sg ps,hz_imp_relships_sg rs,hz_relationships r
WHERE ps.batch_id = p_batch_id
AND ps.batch_mode_flag = p_batch_mode_flag
AND ps.party_orig_system_reference BETWEEN p_from_osr AND p_to_osr
AND ps.party_orig_system = 'DNB'
AND ps.party_id = rs.obj_id
AND rs.batch_mode_flag = p_batch_mode_flag
AND rs.action_flag = 'I'
AND rs.batch_id = p_batch_id
AND r.subject_id = rs.sub_id
AND r.relationship_type IN ('HEADQUARTERS/DIVISION','PARENT/SUBSIDIARY',
'DOMESTIC_ULTIMATE','GLOBAL_ULTIMATE')
AND r.relationship_code IN ('PARENT_OF','HEADQUARTERS_OF',
'DOMESTIC_ULTIMATE_OF','GLOBAL_ULTIMATE_OF')
AND r.object_table_name = 'HZ_PARTIES'
AND r.relationship_id = rs.relationship_id
AND r.directional_flag = 'F'
ORDER BY r.object_id;
CURSOR c_loc IS SELECT ps.location_id,ps.created_by_module,site_sg.site_orig_system_reference
FROM hz_imp_addresses_sg site_sg,
hz_imp_addresses_int site_int,
hz_party_sites ps
WHERE site_sg.batch_id = p_batch_id
AND site_sg.batch_mode_flag = p_batch_mode_flag
AND site_sg.site_orig_system = p_os
AND site_sg.site_orig_system_reference between p_from_osr and p_to_osr
AND site_sg.action_flag = 'U'
AND site_sg.int_row_id = site_int.rowid
AND site_int.correct_move_indicator = 'C'
AND site_int.interface_status IS NULL /* check if any validation error */
AND site_sg.party_site_id = ps.party_site_id
AND exists (select 1 from hz_geo_name_references gnr
where gnr.location_id = ps.location_id
and gnr.location_table_name = 'HZ_LOCATIONS');
HZ_TAX_ASSIGNMENT_V2PUB.update_loc_assignment (
p_location_id => l_location_id(i),
p_created_by_module => l_created_by_module(i),
p_application_id => 222,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_loc_id => l_loc_id,
x_org_id => l_org_id );
select l.location_id, l.country, l.state, l.city, l.postal_code
from hz_locations l,hz_imp_addresses_int addr_int, hz_imp_addresses_sg addr_sg,
hz_party_sites ps
where l.location_id = ps.location_id
and addr_sg.batch_id = p_batch_id
and addr_sg.batch_mode_flag = p_batch_mode_flag
and addr_sg.party_orig_system = p_os
and addr_sg.party_orig_system_reference between p_from_osr and p_to_osr
and addr_sg.int_row_id = addr_int.rowid
and addr_sg.party_site_id = ps.party_site_id
AND addr_int.timezone_code IS NULL
AND addr_int.interface_status IS NULL
AND (decode(addr_int.COUNTRY,p_g_miss_char,NULL,addr_int.COUNTRY) IS NOT NULL OR
decode(addr_int.STATE,p_g_miss_char,NULL,addr_int.STATE) IS NOT NULL OR
decode(addr_int.CITY,p_g_miss_char,NULL,addr_int.CITY) IS NOT NULL OR
decode(addr_int.POSTAL_CODE,p_g_miss_char,NULL,addr_int.POSTAL_CODE) IS NOT NULL);
UPDATE hz_locations
SET timezone_id = l_timezone_id
WHERE location_id = l_location_id(i);
l_new_sql varchar2(1000) := 'SELECT cp.contact_point_id, cp.phone_country_code, cp.phone_area_code
FROM hz_contact_points cp,hz_imp_contactpts_int cpint,hz_imp_contactpts_sg cpsg
WHERE cpsg.batch_id = :p_batch_id
AND cpsg.party_orig_system = :p_os
AND cpsg.party_orig_system_reference between :p_from_osr and :p_to_osr
AND cpsg.batch_mode_flag = :p_batch_mode_flag
AND cp.contact_point_id = cpsg.contact_point_id
AND cp.request_id = :p_request_id
AND cpsg.contact_point_type = ''PHONE''
AND cpsg.int_row_id = cpint.rowid
AND cpint.timezone_code is NULL
AND (decode(cpint.phone_country_code,:p_g_miss_char,NULL,cpint.phone_country_code) IS NOT NULL OR decode(cpint.phone_area_code,:p_g_miss_char,NULL,cpint.phone_area_code) IS NOT NULL)';
l_rerun_sql varchar2(1000) := 'SELECT cp.contact_point_id, cp.phone_country_code, cp.phone_area_code
FROM hz_contact_points cp,hz_imp_contactpts_int cpint,hz_imp_contactpts_sg cpsg, hz_imp_batch_details bd
WHERE cpsg.batch_id = :p_batch_id
AND cpsg.party_orig_system = :p_os
AND cpsg.party_orig_system_reference between :p_from_osr and :p_to_osr
AND cpsg.batch_mode_flag = :p_batch_mode_flag
AND cp.contact_point_id = cpsg.contact_point_id
AND cp.request_id = bd.main_conc_req_id
AND bd.batch_id = cpsg.batch_id
AND cpsg.contact_point_type = ''PHONE''
AND cpsg.int_row_id = cpint.rowid
AND cpint.timezone_code is NULL
AND (decode(cpint.phone_country_code,:p_g_miss_char,NULL,cpint.phone_country_code) IS NOT NULL OR decode(cpint.phone_area_code,:p_g_miss_char,NULL,cpint.phone_area_code) IS NOT NULL)';
UPDATE hz_contact_points
SET timezone_id = l_timezone_id
WHERE contact_point_id = l_contact_point_id(i);
l_new_sql varchar2(1500) := 'SELECT cp.contact_point_id,cp.raw_phone_number,
cp.phone_country_code,cp.phone_area_code,
cp.phone_number,cp.owner_table_name,cp.owner_table_id,
cp.primary_flag,cp.primary_by_purpose,cp.phone_line_type,
cp.phone_extension,
cps.contact_point_type,
null party_id,
cp.contact_point_id record_id,
''CONTACT_POINTS'' entity,
decode(cps.action_flag, ''I'', ''C'', cps.action_flag) operation_flag,
null party_type
FROM hz_contact_points cp, hz_imp_contactpts_sg cps
WHERE cp.request_id = :p_request_id
AND cp.contact_point_id = cps.contact_point_id
and cps.batch_id = :p_batch_id
and cps.party_orig_system = :p_os
and cps.party_orig_system_reference between :p_from_osr and :p_to_osr
and cps.batch_mode_flag = :p_batch_mode_flag
and cps.action_flag is not null';
l_rerun_sql varchar2(1500) := 'SELECT cp.contact_point_id,cp.raw_phone_number,
cp.phone_country_code,cp.phone_area_code,
cp.phone_number,cp.owner_table_name,cp.owner_table_id,
cp.primary_flag,cp.primary_by_purpose,cp.phone_line_type,
cp.phone_extension,
cps.contact_point_type,
null party_id,
cp.contact_point_id record_id,
''CONTACT_POINTS'' entity,
decode(cps.action_flag, ''I'', ''C'', cps.action_flag) operation_flag,
null party_type
FROM hz_contact_points cp, hz_imp_contactpts_sg cps, hz_imp_batch_details bd
WHERE cp.request_id = bd.main_conc_req_id
AND bd.batch_id = cps.batch_id
AND cp.contact_point_id = cps.contact_point_id
and cps.batch_id = :p_batch_id
and cps.party_orig_system = :p_os
and cps.party_orig_system_reference between :p_from_osr and :p_to_osr
and cps.batch_mode_flag = :p_batch_mode_flag
and cps.action_flag is not null';
SELECT country
FROM hz_locations
WHERE location_id = (SELECT location_id
FROM hz_party_sites
WHERE party_site_id = p_site_id);
select territory_code into l1_country_code
from hz_phone_country_codes
where phone_country_code = l_country_code(i)
and rownum = 1;
Update hz_contact_points SET
phone_number = l_phone_number(i),
phone_area_code = l_phone_area_code(i),
phone_country_code = l_country_code(i),
transposed_phone_number = hz_phone_number_pkg.transpose(
l_country_code(i)||l_phone_area_code(i)||l_phone_number(i))
Where contact_point_id = l_contact_point_id(i);
Update hz_contact_points SET
raw_phone_number = l_phone_area_code(i) || '-' || l_phone_number(i),
transposed_phone_number = hz_phone_number_pkg.transpose(
l_country_code(i)||l_phone_area_code(i)||l_phone_number(i))
Where contact_point_id = l_contact_point_id(i);
UPDATE hz_parties set
primary_phone_contact_pt_id = l_contact_point_id(i),
primary_phone_purpose = l_primary_by_purpose(i),
primary_phone_line_type = l_phone_line_type(i),
primary_phone_country_code = l_country_code(i),
primary_phone_area_code = l_phone_area_code(i),
primary_phone_number = l_phone_number(i),
primary_phone_extension = l_phone_extension(i)
WHERE party_id = l_owner_table_id(i);
l_new_sql varchar2(1100) := 'SELECT null party_id,
ocsg.contact_id p_record_id,
''CONTACTS'' entity,
decode(ocsg.action_flag, ''I'', ''C'', ocsg.action_flag) operation_flag,
null party_type
FROM hz_org_contacts oc, hz_imp_contacts_sg ocsg
WHERE ocsg.batch_mode_flag = :p_batch_mode_flag
and ocsg.batch_id = :p_batch_id
and ocsg.sub_orig_system = :p_os
and ocsg.sub_orig_system_reference between :p_from_osr and :p_to_osr
and ocsg.contact_id = oc.org_contact_id
and oc.request_id = :p_request_id';
l_rerun_sql varchar2(1100) := 'SELECT null party_id,
ocsg.contact_id p_record_id,
''CONTACTS'' entity,
decode(ocsg.action_flag, ''I'', ''C'', ocsg.action_flag) operation_flag,
null party_type
FROM hz_org_contacts oc, hz_imp_contacts_sg ocsg, hz_imp_batch_details bd
WHERE ocsg.batch_mode_flag = :p_batch_mode_flag
and ocsg.batch_id = :p_batch_id
and ocsg.sub_orig_system = :p_os
and ocsg.sub_orig_system_reference between :p_from_osr and :p_to_osr
and ocsg.contact_id = oc.org_contact_id
and oc.request_id = bd.main_conc_req_id
and bd.batch_id = ocsg.batch_id';
/* Update status to Complete for the work unit that just finished */
UPDATE HZ_IMP_WORK_UNITS
SET POSTPROCESS_STATUS = 'C'
WHERE BATCH_ID = P_BATCH_ID
AND FROM_ORIG_SYSTEM_REF = P_FROM_OSR;
/* Update status to Complete for the work unit that just finished */
UPDATE HZ_IMP_WORK_UNITS
SET POSTPROCESS_STATUS = 'C'
WHERE BATCH_ID = P_BATCH_ID
AND FROM_ORIG_SYSTEM_REF = P_FROM_OSR;
UPDATE hz_imp_batch_summary
SET import_status = 'ERROR'
WHERE batch_id = P_BATCH_ID;
UPDATE hz_imp_batch_details
SET import_status = 'ERROR'
WHERE batch_id = P_BATCH_ID
AND run_number = (SELECT max(run_number)
FROM hz_imp_batch_details
WHERE batch_id = P_BATCH_ID);