[Home] [Help]
139: BEGIN
140: -- query for interface to tca
141: open l_party_site_cur for
142: select decode(accept_standardized_flag, 'Y', a.CITY_STD, a.CITY), decode(accept_standardized_flag, 'Y', a.POSTAL_CODE_STD, a.POSTAL_CODE), a.STATE, decode(accept_standardized_flag, 'Y', a.COUNTRY_STD, a.COUNTRY), a.party_orig_system, a.party_orig_system_reference, a.site_orig_system, a.site_orig_system_reference, b.party_id, b.party_site_id, b.party_action_flag, a.rowid, decode(accept_standardized_flag, 'Y', a.ADDRESS1_STD, a.ADDRESS1) || ' ' || decode(accept_standardized_flag, 'Y', a.ADDRESS2_STD, a.ADDRESS2) || ' ' || decode(accept_standardized_flag, 'Y', a.ADDRESS3_STD, a.ADDRESS3) || ' ' || decode(accept_standardized_flag, 'Y', a.ADDRESS4_STD, a.ADDRESS4) as address
143: from hz_imp_addresses_int a, hz_imp_addresses_sg b
144: where a.batch_id = p_batch_id
145: and b.action_flag = 'I'
146: and b.int_row_id = a.rowid
147: and a.party_orig_system_reference >= p_from_osr
365: BEGIN
366: l_os := HZ_IMP_DQM_STAGE.get_os(p_batch_id);
367: open l_party_site_cur for
368: select a.CITY, a.POSTAL_CODE, a.STATE, a.COUNTRY, a.party_orig_system, a.party_orig_system_reference, a.site_orig_system, a.site_orig_system_reference, a.rowid, a.address1 || ' ' || a.address2 || ' ' || a.address3 || ' ' || a.address4 as address , a.party_id
369: from hz_imp_addresses_int a
370: where a.batch_id = p_batch_id
371: and a.party_orig_system_reference >= p_from_osr
372: and a.party_orig_system_reference <= p_to_osr
373: and a.party_orig_system = l_os;