[Home] [Help]
1316: l_bind_count := l_bind_count + 1;
1317: l_bind_var_tbl(l_bind_count) := l_source_id;
1318:
1319: l_where := l_where || ' ,hz_relationships INDIRECT_TO_VAD, hz_relationships INDIRECT_TO_VENDOR, '
1320: || ' hz_relationships CONTACT_TO_VAD, hz_organization_profiles HZOP, hz_parties PARTNER '
1321: || ' where CONTACT_TO_VAD.party_id = :bv' || l_bind_count
1322: || ' and CONTACT_TO_VAD.object_id = INDIRECT_TO_VAD.object_id '
1323: || ' and CONTACT_TO_VAD.directional_flag = ''F'' '
1324: || ' and CONTACT_TO_VAD.subject_table_name = ''HZ_PARTIES'' '
2123: vend.party_name,
2124: hzop_vend.internal_flag vend_int_flag
2125: from hz_parties hzp , pv_partner_profiles pvpp , hz_parties vend,
2126: hz_relationships hzr,
2127: hz_organization_profiles HZOP_pt,
2128: hz_organization_profiles hzop_vend
2129: where hzr.party_id = pvpp.partner_id
2130: and pvpp.partner_party_id = hzr.subject_id
2131: and hzr.subject_id = hzp.party_id
2124: hzop_vend.internal_flag vend_int_flag
2125: from hz_parties hzp , pv_partner_profiles pvpp , hz_parties vend,
2126: hz_relationships hzr,
2127: hz_organization_profiles HZOP_pt,
2128: hz_organization_profiles hzop_vend
2129: where hzr.party_id = pvpp.partner_id
2130: and pvpp.partner_party_id = hzr.subject_id
2131: and hzr.subject_id = hzp.party_id
2132: and hzr.subject_table_name = 'HZ_PARTIES'
2168: else 'I'
2169: end ) active_flag
2170: from hz_parties hzp , pv_partner_profiles pvpp ,
2171: pv_attribute_codes_vl pvac, hz_relationships hzr ,
2172: hz_organization_profiles HZOP,
2173: (SELECT rownum idx, column_value
2174: FROM (SELECT column_value FROM TABLE (CAST(p_matched_id AS JTF_NUMBER_TABLE)))) x_partner
2175: where pvpp_partner_id in (SELECT * FROM THE(select CAST(p_matched_id AS JTF_NUMBER_TABLE) from dual))
2176: and pvpp.partner_id = x_partner.column_value
2210: IS
2211: select distinct party_name
2212: from hz_relationships hzr,
2213: hz_parties hzp,
2214: hz_organization_profiles HZOP
2215: where hzr.subject_id = hzp.party_id
2216: and hzr.object_id = HZOP.party_id
2217: and HZOP.internal_flag = 'Y'
2218: and hzr.subject_table_name = 'HZ_PARTIES'
2262: -- CASE WHEN is supported in 9i. Once the local databases are upgraded to 9i
2263: -- this SQL should be a static cursor instead of dynamic.
2264: -- =============================================================================
2265: -- Modified by pklin for 11.5.10. No need to join to hz_relationships and
2266: -- hz_organization_profiles to get the "active_flag". pv_partner_profile.status
2267: -- now contains the denormalized data that indicate whether a partner record
2268: -- is active or not.
2269: --
2270: -- Modified by pklin on 8/6/04 to add additional tables to the use_nl hint.