1 package body ecx_tp_merge_pkg as
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,
11 x_to_id out nocopy number,
12 p_from_fk_id in number,
13 p_to_fk_id in number,
14 p_parent_entity_name in varchar2,
15 p_batch_id in number,
16 p_batch_party_id in number,
17 x_return_status out nocopy varchar2 )
18 is
19 begin
20 x_return_status := fnd_api.g_ret_sts_success;
21 -- if parent has not changed
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
31 where party_id = p_from_fk_id
32 and party_type in ('C', 'E', 'CARRIER');
33 -- update ecx_doclogs for the merge
34 update ecx_doclogs
35 set partyid = to_char(p_to_fk_id)
36 where partyid = to_char(p_from_fk_id)
37 and party_type in ('C', 'E', 'CARRIER');
38 -- update ECX_OUTBOUND_LOGS for the merge
39 update ecx_outbound_logs
40 set party_id = to_char(p_to_fk_id)
41 where party_id = to_char(p_from_fk_id)
42 and party_type in ('C', 'E', 'CARRIER');
43 exception
44 when others then
45 fnd_message.set_name('ECX', 'ECX_MERGE_UNEXPECTED_ERROR');
46 fnd_message.set_token('ERROR_CODE', SQLCODE);
47 fnd_message.set_token('ERROR_MESSAGE', SQLERRM);
48 fnd_msg_pub.add;
49 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
50 end ecx_party_merge;
51
52 procedure ecx_party_sites_merge(
53 p_entity_name in varchar2,
54 p_from_id in number,
55 x_to_id out nocopy number,
56 p_from_fk_id in number,
57 p_to_fk_id in number,
58 p_parent_entity_name in varchar2,
59 p_batch_id in number,
60 p_batch_party_id in number,
61 x_return_status out nocopy varchar2 )
62 is
63 type t_tp_details_rec
64 is
65 record
66 (
67 v_ext_process_id ecx_tp_details.ext_process_id%type,
68 v_source_tp_location_code ecx_tp_details.source_tp_location_code%type);
69 type t_tp_details_tab
70 is
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
80 x_to_id := p_from_id;
81 return;
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
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
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
116 for i in 1..v_tp_details_source_tab.count
117 loop
118 for j in 1..v_tp_details_target_tab.count
119 loop
120 if v_tp_details_source_tab(i).v_ext_process_id= v_tp_details_target_tab(j).v_ext_process_id and v_tp_details_source_tab(i).v_source_tp_location_code= v_tp_details_target_tab(j).v_source_tp_location_code then
121 fnd_message.set_name('ECX', 'ECX_MERGE_UNEXPECTED_ERROR');
122 -- fnd_message.set_token('ERROR_CODE', SQLCODE);
123 fnd_message.set_token('ERROR_MESSAGE', 'Source and Destination TPs with Common TP Details');
124 fnd_msg_pub.add;
125 x_return_status := fnd_api.g_ret_sts_unexp_error;
126 return; --abort the procedure execution
127 end if;
128 end loop;
129 end loop;
130 end if;
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
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
149 where party_site_id = p_from_fk_id
150 and party_type in ('C', 'E', 'CARRIER');
151 end if;
152 -- update ecx_doclogs for the merge
153 update ecx_doclogs
154 set party_site_id = to_char(p_to_fk_id)
155 where party_site_id = to_char(p_from_fk_id)
156 and party_type in ('C', 'E', 'CARRIER');
157 -- update ecx_outbound_logs for the merge
158 update ecx_outbound_logs
159 set party_site_id = to_char(p_to_fk_id)
160 where party_site_id = to_char(p_from_fk_id)
161 and party_type in ('C', 'E', 'CARRIER');
162 exception
163 when others then
164 FND_MESSAGE.SET_NAME('ECX', 'ECX_MERGE_UNEXPECTED_ERROR');
165 FND_MESSAGE.SET_TOKEN('ERROR_CODE', SQLCODE);
166 fnd_message.set_token('ERROR_MESSAGE', sqlerrm);
167 fnd_msg_pub.add;
168 x_return_status := fnd_api.g_ret_sts_unexp_error;
169 end ecx_party_sites_merge;
170 end ecx_tp_merge_pkg;
[Home] [Help]