DBA Data[Home] [Help]

APPS.HZ_IMP_LOAD_SSM_MATCHING_PKG dependencies on HZ_PARTY_SITES

Line 321: nvl(party_site_id, hz_party_sites_s.nextval),

317: (
318: pid, pos, posr,
319: psos, psosr,
320: int_row_id, action_flag,
321: nvl(party_site_id, hz_party_sites_s.nextval),
322: error_flag, party_action_flag,
323: old_psosr, overwrite_osr_flag,
324: P_BATCH_ID, P_BATCH_MODE_FLAG,
325: primary_flag

Line 339: ( 'HZ_PARTY_SITES',

335: ENTITY_ID,
336: BATCH_ID,
337: PARTY_ID
338: ) values
339: ( 'HZ_PARTY_SITES',
340: overwrite_osr_flag,
341: old_psosr,
342: psosr,
343: nvl(party_site_id, hz_party_sites_s.nextval),

Line 343: nvl(party_site_id, hz_party_sites_s.nextval),

339: ( 'HZ_PARTY_SITES',
340: overwrite_osr_flag,
341: old_psosr,
342: psosr,
343: nvl(party_site_id, hz_party_sites_s.nextval),
344: P_BATCH_ID, pid
345: )
346: select /*+ leading(ps_int_w_psosr) use_nl(psosr_mosr,hz_ps) index(hz_ps, hz_party_sites_n4) */
347: ps_int_w_psosr.pid, ps_int_w_psosr.pos, ps_int_w_psosr.posr,

Line 346: select /*+ leading(ps_int_w_psosr) use_nl(psosr_mosr,hz_ps) index(hz_ps, hz_party_sites_n4) */

342: psosr,
343: nvl(party_site_id, hz_party_sites_s.nextval),
344: P_BATCH_ID, pid
345: )
346: select /*+ leading(ps_int_w_psosr) use_nl(psosr_mosr,hz_ps) index(hz_ps, hz_party_sites_n4) */
347: ps_int_w_psosr.pid, ps_int_w_psosr.pos, ps_int_w_psosr.posr,
348: ps_int_w_psosr.psos, ps_int_w_psosr.psosr,
349: ps_int_w_psosr.int_row_id,
350: nvl2(psosr_mosr.owner_table_id,decode(ps_int_w_psosr.owning_pty_chg_flag,'Y','I','U'),'I') action_flag,

Line 352: --nvl(psosr_mosr.owner_table_id, hz_party_sites_s.nextval) party_site_id,

348: ps_int_w_psosr.psos, ps_int_w_psosr.psosr,
349: ps_int_w_psosr.int_row_id,
350: nvl2(psosr_mosr.owner_table_id,decode(ps_int_w_psosr.owning_pty_chg_flag,'Y','I','U'),'I') action_flag,
351: nvl2(ps_int_w_psosr.owning_pty_chg_flag,null,psosr_mosr.owner_table_id) party_site_id,
352: --nvl(psosr_mosr.owner_table_id, hz_party_sites_s.nextval) party_site_id,
353: --psosr_mosr.owner_table_id party_site_id, -- Bug6082657
354: DECODE (ps_int_w_psosr.ps_int_pid, NULL, nvl2(nullif(psosr_mosr.party_id, ps_int_w_psosr.pid), 2, null),
355: NVL2(p.party_id, NULL, 3) ) error_flag,
356: ps_int_w_psosr.party_action_flag,

Line 367: hz_party_sites hz_ps,

363: ps_int_w_psosr.primary_flag
364: desc nulls last),
365: 1, 'Y', null)) primary_flag
366: from hz_orig_sys_references psosr_mosr,
367: hz_party_sites hz_ps,
368: (
369: select /*+ no_merge leading(ps_int) index_asc(ps_int) use_hash(psg) use_nl(mosr) */
370: ps_int.rowid int_row_id,
371: -- note osr_change.entity_id must come before mosr

Line 418: and psosr_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'

414: hz_parties p
415: where ps_int_w_psosr.psos = psosr_mosr.orig_system (+)
416: and ps_int_w_psosr.final_psosr =
417: psosr_mosr.orig_system_reference (+)
418: and psosr_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
419: and psosr_mosr.status (+) = 'A'
420: and ps_int_w_psosr.pid = hz_ps.party_id (+)
421: and 'Y' = hz_ps.identifying_address_flag (+)
422: and 'A' = hz_ps.status (+)

Line 451: nvl(party_site_id, hz_party_sites_s.nextval),

447: (
448: pid, pos, posr,
449: psos, psosr,
450: int_row_id, action_flag,
451: nvl(party_site_id, hz_party_sites_s.nextval),
452: error_flag, party_action_flag,
453: old_psosr, overwrite_osr_flag,
454: P_BATCH_ID, P_BATCH_MODE_FLAG,
455: primary_flag

Line 468: ( 'HZ_PARTY_SITES',

464: NEW_ORIG_SYSTEM_REFERENCE,
465: ENTITY_ID,
466: BATCH_ID, PARTY_ID
467: ) values
468: ( 'HZ_PARTY_SITES',
469: overwrite_osr_flag,
470: old_psosr,
471: psosr,
472: nvl(party_site_id, hz_party_sites_s.nextval),

Line 472: nvl(party_site_id, hz_party_sites_s.nextval),

468: ( 'HZ_PARTY_SITES',
469: overwrite_osr_flag,
470: old_psosr,
471: psosr,
472: nvl(party_site_id, hz_party_sites_s.nextval),
473: P_BATCH_ID, pid
474: )
475: select /*+ leading(ps_int_w_psosr) use_nl(psosr_mosr,hz_ps) index(hz_ps, hz_party_sites_n4) */
476: ps_int_w_psosr.pid, ps_int_w_psosr.pos, ps_int_w_psosr.posr,

Line 475: select /*+ leading(ps_int_w_psosr) use_nl(psosr_mosr,hz_ps) index(hz_ps, hz_party_sites_n4) */

471: psosr,
472: nvl(party_site_id, hz_party_sites_s.nextval),
473: P_BATCH_ID, pid
474: )
475: select /*+ leading(ps_int_w_psosr) use_nl(psosr_mosr,hz_ps) index(hz_ps, hz_party_sites_n4) */
476: ps_int_w_psosr.pid, ps_int_w_psosr.pos, ps_int_w_psosr.posr,
477: ps_int_w_psosr.psos, ps_int_w_psosr.psosr,
478: ps_int_w_psosr.int_row_id,
479: nvl2(psosr_mosr.owner_table_id,decode(ps_int_w_psosr.owning_pty_chg_flag,'Y','I','U'),'I') action_flag,

Line 481: --nvl(psosr_mosr.owner_table_id, hz_party_sites_s.nextval) party_site_id,

477: ps_int_w_psosr.psos, ps_int_w_psosr.psosr,
478: ps_int_w_psosr.int_row_id,
479: nvl2(psosr_mosr.owner_table_id,decode(ps_int_w_psosr.owning_pty_chg_flag,'Y','I','U'),'I') action_flag,
480: nvl2(ps_int_w_psosr.owning_pty_chg_flag,null,psosr_mosr.owner_table_id) party_site_id,
481: --nvl(psosr_mosr.owner_table_id, hz_party_sites_s.nextval) party_site_id,
482: --psosr_mosr.owner_table_id party_site_id, -- Bug6082657
483: DECODE (ps_int_w_psosr.ps_int_pid, NULL, nvl2(nullif(psosr_mosr.party_id, ps_int_w_psosr.pid), 2, null),
484: NVL2(p.party_id, NULL, 3) ) error_flag,
485: ps_int_w_psosr.party_action_flag,

Line 496: hz_party_sites hz_ps,

492: ps_int_w_psosr.primary_flag
493: desc nulls last),
494: 1, 'Y', null)) primary_flag
495: from hz_orig_sys_references psosr_mosr,
496: hz_party_sites hz_ps,
497: (
498: select /*+ no_merge leading(ps_int) index_asc(ps_int) use_hash(psg) use_nl(mosr) */
499: ps_int.rowid int_row_id,
500: -- note osr_change.entity_id must come before mosr

Line 547: and psosr_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'

543: hz_parties p
544: where ps_int_w_psosr.psos = psosr_mosr.orig_system (+)
545: and ps_int_w_psosr.final_psosr =
546: psosr_mosr.orig_system_reference (+)
547: and psosr_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
548: and psosr_mosr.status (+) = 'A'
549: and ps_int_w_psosr.pid = hz_ps.party_id (+)
550: and 'Y' = hz_ps.identifying_address_flag (+)
551: and 'A' = hz_ps.status (+)

Line 658: and addr_osr_ch_tbl.entity_name (+) = 'HZ_PARTY_SITES'

654: ) cpi_cosr
655: where cpi_cosr.new_losr = asg.site_orig_system_reference (+)
656: and cpi_cosr.los = asg.site_orig_system (+)
657: and cpi_cosr.batch_id = asg.batch_id (+)
658: and addr_osr_ch_tbl.entity_name (+) = 'HZ_PARTY_SITES'
659: and addr_osr_ch_tbl.batch_id (+) = P_BATCH_ID
660: and addr_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = cpi_cosr.new_losr
661: and asg.batch_mode_flag (+) = P_BATCH_MODE_FLAG
662: and asg.action_flag (+) = 'I'

Line 671: and ps_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'

667: and decode(cpi_cosr.old_posr, null, cpi_cosr.new_cosr,
668: cpi_cosr.old_posr || substr(cpi_cosr.new_cosr, instr(
669: cpi_cosr.new_cosr, '-'))) = cp_mosr.orig_system_reference (+)
670: and cpi_cosr.cos = cp_mosr.orig_system (+)
671: and ps_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
672: and ps_mosr.status (+) = 'A'
673: and cp_mosr.owner_table_name (+) = 'HZ_CONTACT_POINTS'
674: and cp_mosr.status (+) = 'A'
675: and hz_cpt_pri.owner_table_name (+) = 'HZ_PARTIES'

Line 769: and addr_osr_ch_tbl.entity_name (+) = 'HZ_PARTY_SITES'

765: ) cpi_cosr
766: where cpi_cosr.new_losr = asg.site_orig_system_reference (+)
767: and cpi_cosr.los = asg.site_orig_system (+)
768: and cpi_cosr.batch_id = asg.batch_id (+)
769: and addr_osr_ch_tbl.entity_name (+) = 'HZ_PARTY_SITES'
770: and addr_osr_ch_tbl.batch_id (+) = P_BATCH_ID
771: and addr_osr_ch_tbl.NEW_ORIG_SYSTEM_REFERENCE (+) = cpi_cosr.new_losr
772: and asg.batch_mode_flag (+) = P_BATCH_MODE_FLAG
773: and asg.action_flag (+) = 'I'

Line 782: and ps_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'

778: and decode(cpi_cosr.old_posr, null, cpi_cosr.new_cosr,
779: cpi_cosr.old_posr || substr(cpi_cosr.new_cosr, instr(
780: cpi_cosr.new_cosr, '-'))) = cp_mosr.orig_system_reference (+)
781: and cpi_cosr.cos = cp_mosr.orig_system (+)
782: and ps_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
783: and ps_mosr.status (+) = 'A'
784: and cp_mosr.owner_table_name (+) = 'HZ_CONTACT_POINTS'
785: and cp_mosr.status (+) = 'A'
786: and hz_cpt_pri.owner_table_name (+) = 'HZ_PARTIES'

Line 2639: from hz_party_sites hz_ps,

2635: order by nvl2(hz_psuse.party_site_use_id, hz_psuse.primary_per_type,
2636: addruse_w_id.primary_flag) desc nulls last,
2637: hz_psuse.party_site_use_id nulls last), 1, decode((
2638: select count(*)
2639: from hz_party_sites hz_ps,
2640: hz_party_site_uses hz_ps_use
2641: where hz_ps.party_id = addruse_w_id.party_id
2642: and hz_ps.party_site_id = hz_ps_use.party_site_id
2643: and hz_ps_use.site_use_type = addruse_w_id.site_use_type

Line 2677: and addr_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'

2673: and party_sg.action_flag(+)='I'
2674: and addruse_int.batch_id = addr_sg.batch_id(+)
2675: and addr_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2676: and addr_sg.action_flag(+)='I'
2677: and addr_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
2678: and addr_mosr.status (+) = 'A'
2679: and addruse_int.site_orig_system_reference = addr_mosr.orig_system_reference (+)
2680: and addruse_int.site_orig_system = addr_mosr.orig_system (+)
2681: and addruse_int.site_orig_system_reference = addr_sg.site_orig_system_reference (+)

Line 2720: from hz_party_sites hz_ps,

2716: order by nvl2(hz_psuse.party_site_use_id, hz_psuse.primary_per_type,
2717: addruse_w_id.primary_flag) desc nulls last,
2718: hz_psuse.party_site_use_id nulls last), 1, decode((
2719: select count(*)
2720: from hz_party_sites hz_ps,
2721: hz_party_site_uses hz_ps_use
2722: where hz_ps.party_id = addruse_w_id.party_id
2723: and hz_ps.party_site_id = hz_ps_use.party_site_id
2724: and hz_ps_use.site_use_type = addruse_w_id.site_use_type

Line 2758: and addr_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'

2754: and party_sg.action_flag(+)='I'
2755: and addruse_int.batch_id = addr_sg.batch_id(+)
2756: and addr_sg.batch_mode_flag(+)=P_BATCH_MODE_FLAG
2757: and addr_sg.action_flag(+)='I'
2758: and addr_mosr.owner_table_name (+) = 'HZ_PARTY_SITES'
2759: and addr_mosr.status (+) = 'A'
2760: and addruse_int.site_orig_system_reference = addr_mosr.orig_system_reference (+)
2761: and addruse_int.site_orig_system = addr_mosr.orig_system (+)
2762: and addruse_int.site_orig_system_reference = addr_sg.site_orig_system_reference (+)