[Home] [Help]
478: select
479: P_DML_RECORD.REQUEST_ID,
480: P_DML_RECORD.BATCH_ID,
481: l_row_id(j),
482: 'HZ_IMP_CONTACTPTS_INT',
483: HZ_IMP_ERRORS_S.NextVal,
484: P_DML_RECORD.SYSDATE,
485: P_DML_RECORD.USER_ID,
486: P_DML_RECORD.SYSDATE,
544: p_prefix =>l_debug_prefix,
545: p_msg_level=>fnd_log.level_statement);
546: END IF;
547: ForAll j in 1..l_num_row_processed.count
548: update HZ_IMP_CONTACTPTS_INT
549: set error_id = null,
550: interface_status = null,
551: insert_update_flag = P_ACTION
552: where
631: (
632: select P_DML_RECORD.REQUEST_ID,
633: P_DML_RECORD.BATCH_ID,
634: p_sg.int_row_id,
635: 'HZ_IMP_CONTACTPTS_INT',
636: HZ_IMP_ERRORS_S.NextVal,
637: P_DML_RECORD.SYSDATE,
638: P_DML_RECORD.USER_ID,
639: P_DML_RECORD.SYSDATE,
648: 'Y','Y','Y',
649: 'Y','Y','Y','Y',
650: dup_val_exp_val,
651: other_exp_val
652: from hz_imp_contactpts_int int,hz_imp_contactpts_sg p_sg
653: where int.rowid = p_sg.int_row_id
654: and p_sg.action_flag = 'I'
655: and p_sg.batch_id = P_DML_RECORD.BATCH_ID
656: and int.party_orig_system = P_DML_RECORD.OS
827: :l_sysdate, :l_last_update_login,
828: :l_program_application_id,
829: :l_program_id, :l_sysdate,
830: HZ_IMP_ERRORS_S.nextval, :l_batch_id, :l_request_id,
831: row_id, ''HZ_IMP_CONTACTPTS_INT'',contact_point_type_error,
832: cp_purpose_error, edi_id_number_error,email_address_error,
833: email_format_error, phone_country_code_error,phone_line_type_error,
834: phone_number_error, telex_number_error,timezone_id_error,
835: url_error, web_type_error,flex_val_errors,
994: ''HZ_PARTIES''))) owner_table_error, */
995: ps.error_flag,
996: hp.party_id party_owner_table_id,
997: mosr_site.owner_table_id site_owner_table_id
998: from hz_imp_contactpts_int pi,
999: hz_imp_contactpts_sg ps,
1000: --hz_orig_sys_references mosr_party,
1001: hz_parties hp,
1002: hz_orig_sys_references mosr_site,
1191: and hz_cpt.CONTACT_POINT_TYPE = int.contact_point_type
1192: and rownum = 1
1193: ) contact_point_id
1194: from HZ_IMP_TMP_ERRORS err_table,
1195: hz_imp_contactpts_int int,
1196: hz_imp_contactpts_sg cpt_sg
1197: where err_table.request_id = :request_id
1198: and cpt_sg.batch_id = :batch_id
1199: and cpt_sg.batch_mode_flag = :batch_mode_flag
1196: hz_imp_contactpts_sg cpt_sg
1197: where err_table.request_id = :request_id
1198: and cpt_sg.batch_id = :batch_id
1199: and cpt_sg.batch_mode_flag = :batch_mode_flag
1200: and err_table.interface_table_name = ''HZ_IMP_CONTACTPTS_INT''
1201: and cpt_sg.party_orig_system = :orig_system
1202: and cpt_sg.party_orig_system_reference between :from_osr and :to_osr
1203: and cpt_sg.primary_flag = ''Y''
1204: and cpt_sg.int_row_id = err_table.int_row_id
1255: /* Note: for error case, the party with the id will just be not found */
1256: /* in update. Not necessary to filter out here. */
1257: OPEN de_norm_cursor FOR
1258: 'select cpt_sg.party_id, cpt_sg.contact_point_id, int.contact_point_type
1259: from hz_imp_contactpts_int int,hz_imp_contactpts_sg cpt_sg
1260: where int.rowid = cpt_sg.int_row_id
1261: and int.contact_point_type in(''WEB'',''EMAIL'')
1262: and cpt_sg.batch_id = :batch_id
1263: and cpt_sg.batch_mode_flag = :batch_mode_flag
1268: and cpt_sg.party_action_flag = ''U''
1269: and not exists (select tmp_err.INT_ROW_ID
1270: from hz_imp_tmp_errors tmp_err
1271: where tmp_err.INT_ROW_ID = int.rowid
1272: and tmp_err.INTERFACE_TABLE_NAME = ''HZ_IMP_CONTACTPTS_INT'')
1273: '
1274: using P_DML_RECORD.BATCH_ID,
1275: P_DML_RECORD.BATCH_MODE_FLAG, P_DML_RECORD.OS,
1276: P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR;
1480: ''Y''
1481: ),
1482: ''Y'') createdby_error
1483:
1484: FROM HZ_IMP_CONTACTPTS_INT pi, HZ_IMP_CONTACTPTS_SG ps,hz_contact_points hp,
1485: hz_orig_systems_b hos, /* Bug 4079902 */
1486: --(select distinct phone_country_code from hz_phone_country_codes) hpc,--3401319
1487: (select 0 a from dual union all select 1 a from dual) tc,--3401319
1488: fnd_timezones_b ht,fnd_lookup_values contact_point_type_l,