[Home] [Help]
285: where rownum < 24) x, (
286: select a, count(*) ct from (
287: select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
288: 1, nvl2(NVL(rel.relationship_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 2, 3),24)) a
289: from hz_imp_tmp_errors e, hz_imp_relships_sg sg,hz_relationships rel, HZ_IMP_RELSHIPS_INT hzint
290: where sg.batch_mode_flag = p_batch_mode_flag
291: and sg.batch_id = p_batch_id
292: and sg.sub_orig_system = p_original_system
293: and e.int_row_id (+) = sg.int_row_id
300: and hzint.interface_status(+) = 'D'
301: union all
302: select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
303: 1, nvl2(NVL(fnn.financial_number_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 4, 5),24)) a
304: from hz_imp_tmp_errors e, hz_imp_finnumbers_sg sg,hz_financial_numbers fnn, HZ_IMP_FINNUMBERS_INT hzint
305: where sg.batch_mode_flag = p_batch_mode_flag
306: and sg.batch_id = p_batch_id
307: and sg.party_orig_system = p_original_system
308: and e.int_row_id (+) = sg.int_row_id
314: and hzint.interface_status(+) = 'D'
315: union all
316: select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
317: 1, nvl2(NVL(fnr.financial_report_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 6, 7),24)) a
318: from hz_imp_tmp_errors e, hz_imp_finreports_sg sg,hz_financial_reports fnr, HZ_IMP_FINREPORTS_INT hzint
319: where sg.batch_mode_flag = p_batch_mode_flag
320: and sg.batch_id = p_batch_id
321: and sg.party_orig_system = p_original_system
322: and e.int_row_id (+) = sg.int_row_id
328: and hzint.interface_status(+) = 'D'
329: union all
330: select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
331: 1, nvl2(NVL(hcp.contact_point_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 8, 9),24)) a
332: from hz_imp_tmp_errors e, hz_imp_contactpts_sg sg,hz_contact_points hcp, HZ_IMP_CONTACTPTS_INT hzint
333: where sg.batch_mode_flag = p_batch_mode_flag
334: and sg.batch_id = p_batch_id
335: and sg.party_orig_system = p_original_system
336: and e.int_row_id (+) = sg.int_row_id
342: and hzint.interface_status(+) = 'D'
343: union all
344: select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
345: 1, nvl2(NVL(loc.location_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 10, 11),24)) a
346: from hz_imp_tmp_errors e, hz_imp_addresses_sg sg,hz_party_sites hps,
347: hz_orig_sys_references hosr,hz_locations loc, HZ_IMP_ADDRESSES_INT hzint
348: where sg.batch_mode_flag = p_batch_mode_flag
349: and sg.batch_id = p_batch_id
350: and sg.party_orig_system = p_original_system
363: and hzint.interface_status(+) = 'D'
364: union all
365: select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
366: 1, nvl2(NVL(hp.party_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 12, 13),24)) a
367: from hz_imp_tmp_errors e, hz_imp_parties_sg sg,hz_parties hp, HZ_IMP_PARTIES_INT hzint
368: where sg.batch_mode_flag = p_batch_mode_flag
369: and sg.batch_id = p_batch_id
370: and sg.party_orig_system = p_original_system
371: and e.int_row_id (+) = sg.int_row_id
377: and hzint.interface_status(+) = 'D'
378: union all
379: select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
380: 1, nvl2(NVL(hocr.org_contact_role_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 14, 15),24)) a
381: from hz_imp_tmp_errors e, hz_imp_contactroles_sg sg,hz_org_contact_roles hocr, HZ_IMP_CONTACTROLES_INT hzint
382: where sg.batch_mode_flag = p_batch_mode_flag
383: and sg.batch_id = p_batch_id
384: and sg.sub_orig_system = p_original_system
385: and e.int_row_id (+) = sg.int_row_id
391: and hzint.interface_status(+) = 'D'
392: union all
393: select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
394: 1, nvl2(NVL(hpsu.party_site_use_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 16, 17),24)) a
395: from hz_imp_tmp_errors e, hz_imp_addressuses_sg sg, hz_party_site_uses hpsu, HZ_IMP_ADDRESSUSES_INT hzint
396: where sg.batch_mode_flag = p_batch_mode_flag
397: and sg.batch_id = p_batch_id
398: and sg.party_orig_system = p_original_system
399: and e.int_row_id (+) = sg.int_row_id
405: and hzint.interface_status(+) = 'D'
406: union all
407: select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
408: 1, nvl2(NVL(hoc.org_contact_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 18, 19),24)) a
409: from hz_imp_tmp_errors e, hz_imp_contacts_sg sg, hz_org_contacts hoc, HZ_IMP_CONTACTS_INT hzint
410: where sg.batch_mode_flag = p_batch_mode_flag
411: and sg.batch_id = p_batch_id
412: and sg.contact_orig_system = p_original_system
413: and e.int_row_id (+) = sg.int_row_id
419: and hzint.interface_status(+) = 'D'
420: union all
421: select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
422: 1, nvl2(NVL(hcr.credit_rating_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 20, 21),24)) a
423: from hz_imp_tmp_errors e, hz_imp_creditrtngs_sg sg,hz_credit_ratings hcr, HZ_IMP_CREDITRTNGS_INT hzint
424: where sg.batch_mode_flag = p_batch_mode_flag
425: and sg.batch_id = p_batch_id
426: and sg.party_orig_system = p_original_system
427: and e.int_row_id (+) = sg.int_row_id
433: and hzint.interface_status(+) = 'D'
434: union all
435: select /*+ full(sg) parallel(sg) */ nvl2(e.int_row_id,
436: 1, nvl2(NVL(hca.code_assignment_id,hzint.BATCH_ID),decode(sg.action_flag, 'I', 22, 23),24)) a
437: from hz_imp_tmp_errors e, hz_imp_classifics_sg sg,hz_code_assignments hca, HZ_IMP_CLASSIFICS_INT hzint
438: where sg.batch_mode_flag = p_batch_mode_flag
439: and sg.batch_id = p_batch_id
440: and sg.party_orig_system = p_original_system
441: and e.int_row_id (+) = sg.int_row_id