[Home] [Help]
131: competitor_flag = decode(l_comp_flag(i),'Y','Y',competitor_flag),
132: reference_use_flag = decode(l_ref_flag(i) ,'Y','Y',reference_use_flag),
133: third_party_flag = decode(l_par_flag(i) ,'Y','Y',third_party_flag)
134: WHERE party_id = l_subject_id(i)
135: AND request_id IN (SELECT main_conc_req_id FROM hz_imp_batch_details
136: WHERE batch_id = p_batch_id);
137: END IF;
138: EXCEPTION
139: WHEN OTHERS THEN
197: ,p.known_as
198: ,p.person_first_name_phonetic
199: ,pf.middle_name_phonetic
200: ,p.person_last_name_phonetic
201: FROM HZ_PARTIES p, HZ_PERSON_PROFILES pf, HZ_IMP_PARTIES_SG ps, hz_imp_batch_details bd
202: WHERE p.request_id = bd.main_conc_req_id
203: AND bd.batch_id = ps.batch_id
204: AND p.party_type = ''PERSON''
205: AND p.party_id = pf.party_id
330: ,p.person_last_name
331: ,null record_id
332: ,''PARTY'' entity
333: ,decode(ps.action_flag, ''I'', ''C'', ps.action_flag) operation_flag
334: FROM hz_parties p, HZ_IMP_PARTIES_SG ps, hz_imp_batch_details bd
335: WHERE p.request_id = bd.main_conc_req_id
336: AND bd.batch_id = ps.batch_id
337: AND p.party_type IN (''ORGANIZATION'',''PERSON'',''GROUP'')
338: AND p.party_id = ps.party_id
468: ,''PARTY_SITES'' entity
469: ,decode(addr_sg.action_flag, ''I'', ''C'', addr_sg.action_flag) operation_flag
470: ,null party_type
471: FROM hz_locations l, hz_party_sites ps,
472: hz_imp_addresses_sg addr_sg, hz_imp_batch_details bd
473: WHERE l.request_id = bd.main_conc_req_id
474: and bd.batch_id = addr_sg.batch_id
475: and l.location_id = ps.location_id
476: and addr_sg.batch_id = :p_batch_id
876: AND cpint.timezone_code is NULL
877: 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)';
878:
879: l_rerun_sql varchar2(1000) := 'SELECT cp.contact_point_id, cp.phone_country_code, cp.phone_area_code
880: FROM hz_contact_points cp,hz_imp_contactpts_int cpint,hz_imp_contactpts_sg cpsg, hz_imp_batch_details bd
881: WHERE cpsg.batch_id = :p_batch_id
882: AND cpsg.party_orig_system = :p_os
883: AND cpsg.party_orig_system_reference between :p_from_osr and :p_to_osr
884: AND cpsg.batch_mode_flag = :p_batch_mode_flag
1001: cp.contact_point_id record_id,
1002: ''CONTACT_POINTS'' entity,
1003: decode(cps.action_flag, ''I'', ''C'', cps.action_flag) operation_flag,
1004: null party_type
1005: FROM hz_contact_points cp, hz_imp_contactpts_sg cps, hz_imp_batch_details bd
1006: WHERE cp.request_id = bd.main_conc_req_id
1007: AND bd.batch_id = cps.batch_id
1008: AND cp.contact_point_id = cps.contact_point_id
1009: and cps.batch_id = :p_batch_id
1176: ocsg.contact_id p_record_id,
1177: ''CONTACTS'' entity,
1178: decode(ocsg.action_flag, ''I'', ''C'', ocsg.action_flag) operation_flag,
1179: null party_type
1180: FROM hz_org_contacts oc, hz_imp_contacts_sg ocsg, hz_imp_batch_details bd
1181: WHERE ocsg.batch_mode_flag = :p_batch_mode_flag
1182: and ocsg.batch_id = :p_batch_id
1183: and ocsg.sub_orig_system = :p_os
1184: and ocsg.sub_orig_system_reference between :p_from_osr and :p_to_osr
1487: UPDATE hz_imp_batch_summary
1488: SET import_status = 'ERROR'
1489: WHERE batch_id = P_BATCH_ID;
1490:
1491: UPDATE hz_imp_batch_details
1492: SET import_status = 'ERROR'
1493: WHERE batch_id = P_BATCH_ID
1494: AND run_number = (SELECT max(run_number)
1495: FROM hz_imp_batch_details
1491: UPDATE hz_imp_batch_details
1492: SET import_status = 'ERROR'
1493: WHERE batch_id = P_BATCH_ID
1494: AND run_number = (SELECT max(run_number)
1495: FROM hz_imp_batch_details
1496: WHERE batch_id = P_BATCH_ID);
1497:
1498: COMMIT;
1499: