[Home] [Help]
2: /* $Header: ECXPMRGB.pls 120.1.12020000.3 2013/01/22 12:38:36 dhragarw ship $ */
3: --
4: -- Procedure: ecx_party_merge
5: --
6: -- Description: This routine takes care of the party merge for ECX_TP_HEADERS
7: -- table
8: procedure ecx_party_merge(
9: p_entity_name in varchar2,
10: p_from_id in number,
22: if (p_from_fk_id = p_to_fk_id) then
23: x_to_id := p_from_id;
24: return;
25: end if;
26: -- update ECX_TP_HEADERS for the merge
27: update ecx_tp_headers
28: set party_id = p_to_fk_id,
29: last_updated_by = hz_utility_pub.user_id,
30: last_update_login = hz_utility_pub.last_update_login
23: x_to_id := p_from_id;
24: return;
25: end if;
26: -- update ECX_TP_HEADERS for the merge
27: update ecx_tp_headers
28: set party_id = p_to_fk_id,
29: last_updated_by = hz_utility_pub.user_id,
30: last_update_login = hz_utility_pub.last_update_login
31: where party_id = p_from_fk_id
71: table of t_tp_details_rec;
72: v_tp_details_source_tab t_tp_details_tab;
73: v_tp_details_target_tab t_tp_details_tab;
74: l_exists pls_integer;
75: v_target_header_id ecx_tp_headers.tp_header_id%type;
76: begin
77: x_return_status := fnd_api.g_ret_sts_success;
78: -- if parent has not changed
79: if (p_from_fk_id = p_to_fk_id) then
82: end if;
83:
84: select count(1), max(tp_header_id)--though there will be a single tp_header_id for
85: into l_exists, v_target_header_id--a party_site_id, max is used to retrieve tp_header_id
86: from ecx_tp_headers --in the same query if it exists.
87: where party_site_id =p_to_fk_id
88: and party_type in ('C', 'E', 'CARRIER') ;
89:
90: if l_exists = 1 then
90: if l_exists = 1 then
91:
92: select count(1)
93: into l_exists
94: from ecx_tp_headers
95: where party_site_id =p_from_fk_id
96: and party_type in ('C', 'E', 'CARRIER') ;
97:
98: if l_exists =1 then
99:
100: select etd.ext_process_id,
101: etd.source_tp_location_code bulk collect
102: into v_tp_details_target_tab
103: from ecx_tp_headers eth,
104: ecx_tp_details etd
105: where eth.tp_header_id= etd.tp_header_id
106: and eth.party_site_id =p_to_fk_id;
107:
107:
108: select etd.ext_process_id,
109: etd.source_tp_location_code bulk collect
110: into v_tp_details_source_tab
111: from ecx_tp_headers eth,
112: ecx_tp_details etd
113: where eth.tp_header_id= etd.tp_header_id
114: and eth.party_site_id =p_from_fk_id;
115:
131: update ecx_tp_details
132: set tp_header_id = v_target_header_id
133: where tp_header_id in
134: (select tp_header_id
135: from ecx_tp_headers
136: where party_site_id = p_from_fk_id
137: and party_type in ('C', 'E', 'CARRIER')
138: );
139: delete
136: where party_site_id = p_from_fk_id
137: and party_type in ('C', 'E', 'CARRIER')
138: );
139: delete
140: from ecx_tp_headers
141: where party_site_id = p_from_fk_id
142: and party_type in ('C', 'E', 'CARRIER');
143: else
144: -- update ecx_tp_headers for the merge
140: from ecx_tp_headers
141: where party_site_id = p_from_fk_id
142: and party_type in ('C', 'E', 'CARRIER');
143: else
144: -- update ecx_tp_headers for the merge
145: update ecx_tp_headers
146: set party_site_id = p_to_fk_id,
147: last_updated_by = hz_utility_pub.user_id,
148: last_update_login = hz_utility_pub.last_update_login
141: where party_site_id = p_from_fk_id
142: and party_type in ('C', 'E', 'CARRIER');
143: else
144: -- update ecx_tp_headers for the merge
145: update ecx_tp_headers
146: set party_site_id = p_to_fk_id,
147: last_updated_by = hz_utility_pub.user_id,
148: last_update_login = hz_utility_pub.last_update_login
149: where party_site_id = p_from_fk_id