[Home] [Help]
PACKAGE BODY: APPS.OKS_PARTY_MERGE_PKG
Source
4 g_api_name constant varchar2(30) := 'OKS_PARTY_MERGE_PKG';
1 PACKAGE BODY OKS_PARTY_MERGE_PKG AS
2 /* $Header: OKSPYMGB.pls 120.0 2005/05/25 18:23:54 appldev noship $ */
3
5 g_user_id constant number(15) := arp_standard.profile.user_id;
6 g_login_id constant number(15) := arp_standard.profile.last_update_login;
7
8 /* Merge the records in OKS_BILLING_PROFILES_B */
9
10 PROCEDURE OKS_BILLING_PROFILES(
11 p_entity_name in hz_merge_dictionary.entity_name%type,
12 p_from_id in oks_billing_profiles_b.id%type,
13 x_to_id in out nocopy oks_billing_profiles_b.id%type,
14 p_from_fk_id in hz_merge_parties.from_party_id%type,
15 p_to_fk_id in hz_merge_parties.to_party_id%type,
16 p_parent_entity_name in hz_merge_dictionary.parent_entity_name%type,
17 p_batch_id in hz_merge_batch.batch_id%type,
18 p_batch_party_id in hz_merge_party_details.batch_party_id%type,
19 x_return_status out nocopy varchar2) IS
20
21 l_proc_name varchar2(30) := 'OKS_BILLING_PROFILES';
22 l_count number(10) := 0;
23
24 BEGIN
25 arp_message.set_line(g_api_name||'.'||l_proc_name);
26
27 x_return_status := FND_API.G_RET_STS_SUCCESS;
28
29 --If it is a Site Merge, nothing to be done. Return the x_to_id.
30
31 if p_from_fk_id = p_to_fk_id then
32 x_to_id := p_from_id;
33 return;
34 end if;
35
36 --If party_id 1000 is getting merged to party_id 2000,
37 --update the party_id to 2000 which were previously 1000.
38
39 if p_from_fk_id <> p_to_fk_id then
40 BEGIN
41
42 arp_message.set_line('Updating OKS_BILLING_PROFILES_B...');
43
44 update oks_billing_profiles_b
45 set owned_party_id1 = p_to_fk_id,
46 last_update_date = sysdate,
47 last_updated_by = g_user_id,
48 last_update_login = g_login_id,
49 object_version_number = object_version_number+1
50 where owned_party_id1 = p_from_fk_id;
51
52 l_count := sql%rowcount;
53 arp_message.set_name('AR','AR_ROWS_UPDATED');
54 arp_message.set_token('NUM_ROWS',to_char(l_count));
55
56 EXCEPTION
57 when OTHERS then
58 arp_message.set_line(g_api_name||'.'||l_proc_name||': '||sqlerrm);
59 x_return_status := FND_API.G_RET_STS_ERROR;
60 raise;
61 END;
62 end if;
63 END OKS_BILLING_PROFILES;
64
65
66 /* Merge the records in OKS_K_DEFAULTS */
67
68 PROCEDURE OKS_DEFAULTS(
69 p_entity_name in hz_merge_dictionary.entity_name%type,
70 p_from_id in oks_k_defaults.id%type,
71 x_to_id in out nocopy oks_k_defaults.id%type,
72 p_from_fk_id in hz_merge_parties.from_party_id%type,
73 p_to_fk_id in hz_merge_parties.to_party_id%type,
74 p_parent_entity_name in hz_merge_dictionary.parent_entity_name%type,
75 p_batch_id in hz_merge_batch.batch_id%type,
76 p_batch_party_id in hz_merge_party_details.batch_party_id%type,
77 x_return_status out nocopy varchar2) IS
78
79
80 l_proc_name varchar2(30) := 'OKS_DEFAULTS';
81 l_count number(10) := 0;
82
83 --Added by CK 04/03
84 l_from_start_date date;
85 l_from_end_date date;
86 l_from_end_date1 date;
87 l_to_start_date date;
88 l_to_start_date1 date;
89 l_to_end_date date;
90 l_to_end_date1 date;
91 l_row_count_exists boolean;
92 cursor default_cur(p_fk_id hz_merge_parties.from_party_id%type) IS
93 SELECT start_date,end_date
94 FROM oks_k_defaults
95 where segment_id1 =p_fk_id;
96 from_rec default_cur%ROWTYPE;
97 to_rec default_cur%ROWTYPE;
98
99 BEGIN
100 arp_message.set_line(g_api_name||'.'||l_proc_name);
101
102 x_return_status := FND_API.G_RET_STS_SUCCESS;
103
104 --If it is a Site Merge, nothing to be done. Return the x_to_id.
105
106 if p_from_fk_id = p_to_fk_id then
107 x_to_id := p_from_id;
108 return;
109 end if;
110
111 --If party_id 1000 is getting merged to party_id 2000,
112 --update the party_id to 2000 which were previously 1000.
113
114 -- Fetch start and end dates
115 OPEN default_cur(p_from_fk_id);
116 FETCH default_cur INTO from_rec;
117 IF default_cur%FOUND THEN
118 l_from_start_date:=from_rec.start_date;
119 l_from_end_date:=from_rec.end_date;
120 END IF;
121 CLOSE default_cur;
122
123 OPEN default_cur(p_to_fk_id);
124 FETCH default_cur INTO to_rec;
128 l_row_count_exists :=true;
125 IF default_cur%FOUND THEN
126 l_to_start_date:=to_rec.start_date;
127 l_to_end_date:=to_rec.end_date;
129 ELSE
130 l_row_count_exists := false;
131 END IF;
132 CLOSE default_cur;
133 l_to_start_date1 := least(l_from_start_date,l_to_start_date);
134 l_to_end_date1 := greatest(l_from_end_date,l_to_end_date);
135
136 -- If From StartDate is greater than or equal to sysdate, update to sysdate-1
137 -- If From EndDate is greater than or equal to sysdate, update to sysdate-1
138 IF l_from_start_date >= sysdate THEN
139 l_from_start_date:=sysdate-1;
140 END IF;
141 IF l_from_end_date >= sysdate
142 OR l_from_end_date is null THEN
143 l_from_end_date :=sysdate -1;
144 END IF;
145
146 if p_from_fk_id <> p_to_fk_id then
147 BEGIN
148 -- Updateing from(source) record -- Party getting merged
149
150 IF not l_row_count_exists THEN
151 update oks_k_defaults
152 set segment_id1 = p_to_fk_id,
153 last_update_date = sysdate,
154 last_updated_by = g_user_id,
155 object_version_number = object_version_number+1
156 where segment_id1 = p_from_fk_id
157 and jtot_object_code = 'OKX_PARTY';
158 ELSE
159 IF l_to_start_date1<> l_to_start_date
160 AND l_to_end_date1 <> l_to_end_date
161 THEN
162 update oks_k_defaults
163 set
164 start_date = l_to_start_date1,
165 end_date = l_to_end_date1,
166 last_update_date = sysdate,
167 last_updated_by = g_user_id,
168 object_version_number = object_version_number+1
169 where segment_id1 = p_to_fk_id
170 and jtot_object_code = 'OKX_PARTY';
171 ELSIF l_to_start_date1<> l_to_start_date
172 AND l_to_end_date1 = l_to_end_date THEN
173 update oks_k_defaults
174 set
175 start_date = l_to_start_date1,
176 last_update_date = sysdate,
177 last_updated_by = g_user_id,
178 object_version_number = object_version_number+1
179 where segment_id1 = p_to_fk_id
180 and jtot_object_code = 'OKX_PARTY';
181 ELSIF l_to_start_date1 = l_to_start_date
182 AND l_to_end_date1 <> l_to_end_date THEN
183 update oks_k_defaults
184 set
185 end_date = l_to_end_date1,
186 last_update_date = sysdate,
187 last_updated_by = g_user_id,
188 object_version_number = object_version_number+1
189 where segment_id1 = p_to_fk_id
190 and jtot_object_code = 'OKX_PARTY';
191
192 END IF;
193 l_count := sql%rowcount;
194 arp_message.set_name('AR','AR_ROWS_UPDATED');
195 arp_message.set_token('NUM_ROWS',to_char(l_count));
196 update oks_k_defaults
197 set segment_id1 = p_to_fk_id,
198 last_update_date = sysdate,
199 last_updated_by = g_user_id,
200 start_date = l_from_start_date,
201 end_date = l_from_end_date,
202 object_version_number = object_version_number+1
203 where segment_id1 = p_from_fk_id
204 and jtot_object_code = 'OKX_PARTY';
205 l_count := sql%rowcount;
206 arp_message.set_name('AR','AR_ROWS_UPDATED');
207 arp_message.set_token('NUM_ROWS',to_char(l_count));
208 arp_message.set_line('Updating OKS_K_DEFAULTS...');
209
210 END IF;
211
212 EXCEPTION
213 when OTHERS then
214 arp_message.set_line(g_api_name||'.'||l_proc_name||': '||sqlerrm);
215 x_return_status := FND_API.G_RET_STS_ERROR;
216 raise;
217 END;
218 end if;
219 END OKS_DEFAULTS;
220
221 /* Merge the records in OKS_SERV_AVAIL_EXCEPTS */
222 --chkrishn 04/16/03 modify code to delete service avail exceptions of both from and to parties during merge
223 PROCEDURE OKS_SERVICE_EXCEPTS(
224 p_entity_name in hz_merge_dictionary.entity_name%type,
225 p_from_id in oks_serv_avail_excepts.id%type,
226 x_to_id in out nocopy oks_serv_avail_excepts.id%type,
227 p_from_fk_id in hz_merge_parties.from_party_id%type,
228 p_to_fk_id in hz_merge_parties.to_party_id%type,
229 p_parent_entity_name in hz_merge_dictionary.parent_entity_name%type,
230 p_batch_id in hz_merge_batch.batch_id%type,
231 p_batch_party_id in hz_merge_party_details.batch_party_id%type,
232 x_return_status out nocopy varchar2) IS
233
234 l_proc_name varchar2(30) := 'OKS_SERVICE_EXCEPTS';
235 l_count number(10) := 0;
236
237 BEGIN
238 arp_message.set_line(g_api_name||'.'||l_proc_name);
239
240 x_return_status := FND_API.G_RET_STS_SUCCESS;
241
242 --If it is a Site Merge, nothing to be done. Return the x_to_id.
243
244 if p_from_fk_id = p_to_fk_id then
245 x_to_id := p_from_id;
246 return;
247 end if;
248
249 --If party_id 1000 is getting merged to party_id 2000,
250 --update the party_id to 2000 which were previously 1000.
251
252 if p_from_fk_id <> p_to_fk_id then
253 BEGIN
254
255 /* original code arp_message.set_line('Updating OKS_SERV_AVAIL_EXCEPTS...');
256
257 update oks_serv_avail_excepts
258 set object1_id1 = p_to_fk_id,
259 last_update_date = sysdate,
260 last_updated_by = g_user_id,
261 last_update_login = g_login_id,
262 object_version_number = object_version_number+1
263 where object1_id1 = p_from_fk_id
264 and jtot_object1_code = 'OKX_PARTY';
265
269
266 l_count := sql%rowcount;
267 arp_message.set_name('AR','AR_ROWS_UPDATED');
268 arp_message.set_token('NUM_ROWS',to_char(l_count));*/
270 arp_message.set_line('Deleting OKS_SERV_AVAIL_EXCEPTS...');
271
272 delete from oks_serv_avail_excepts
273 where object1_id1 in (p_from_fk_id,p_to_fk_id)
274 and jtot_object1_code = 'OKX_PARTY';
275
276 l_count := sql%rowcount;
277 arp_message.set_name('AR','AR_ROWS_DELETED');
278 arp_message.set_token('NUM_ROWS',to_char(l_count));
279
280 EXCEPTION
281 when OTHERS then
282 arp_message.set_line(g_api_name||'.'||l_proc_name||': '||sqlerrm);
283 x_return_status := FND_API.G_RET_STS_ERROR;
284 raise;
285 END;
286 end if;
287 END OKS_SERVICE_EXCEPTS;
288
289
290 PROCEDURE OKS_QUALIFIERS(
291 p_entity_name in hz_merge_dictionary.entity_name%type,
292 p_from_id in number,
293 x_to_id in out nocopy number,
294 p_from_fk_id in number,
295 p_to_fk_id in number,
296 p_parent_entity_name in hz_merge_dictionary.parent_entity_name%type,
297 p_batch_id in hz_merge_batch.batch_id%type,
298 p_batch_party_id in hz_merge_party_details.batch_party_id%type,
299 x_return_status out nocopy varchar2) IS
300
301 l_proc_name varchar2(30) := 'OKS_QUALIFIERS';
302 l_count number(10) := 0;
303 l_return_status VARCHAR2(1);
304
305 BEGIN
306 arp_message.set_line(g_api_name||'.'||l_proc_name);
307
308 x_return_status := FND_API.G_RET_STS_SUCCESS;
309
310 --If it is a Site Merge, nothing to be done. Return the x_to_id.
311
312 if p_from_fk_id = p_to_fk_id then
313 x_to_id := p_from_id;
314 return;
315 end if;
316
317 --If party_id 1000 is getting merged to party_id 2000,
318 --update the party_id to 2000 which were previously 1000.
319
320 if p_from_fk_id <> p_to_fk_id then
321 BEGIN
322
323 arp_message.set_line('Updating OKS_QUALIFIERS...');
324
325 OKS_QP_INT_PVT.QUALIFIER_PARTY_MERGE(
326 p_from_fk_id =>p_from_fk_id,
327 p_to_fk_id =>p_to_fk_id,
328 x_return_status =>l_return_status);
329
330 IF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
331 RAISE OKC_API.G_EXCEPTION_ERROR;
332 END IF;
333
334 EXCEPTION
335 when OTHERS then
336 arp_message.set_line(g_api_name||'.'||l_proc_name||': '||sqlerrm);
337 x_return_status := FND_API.G_RET_STS_ERROR;
338 raise;
339 END;
340 end if;
341 END OKS_QUALIFIERS;
342
343 END OKS_PARTY_MERGE_PKG;