[Home] [Help]
PACKAGE BODY: APPS.POS_PARTY_MERGE_PKG
Source
1 PACKAGE BODY pos_party_merge_pkg AS
2 /*$Header: POSPTYMB.pls 120.7 2006/10/12 19:51:44 gdwivedi noship $ */
3
4 g_log_module CONSTANT VARCHAR2(30) := 'POS.PLS.POS_PARTY_MERGE_PKG.';
5
6 FUNCTION is_enterprise_party (p_party_id IN NUMBER) RETURN BOOLEAN
7 IS PRAGMA AUTONOMOUS_TRANSACTION;
8 CURSOR l_cur IS
9 SELECT 1
10 FROM hz_code_assignments
11 WHERE owner_table_name = 'HZ_PARTIES'
12 AND owner_table_id = p_party_id
13 AND class_category = 'POS_PARTICIPANT_TYPE'
14 AND class_code = 'ENTERPRISE'
15 AND status = 'A'
16 AND ROWNUM < 2;
17
18 l_number NUMBER;
19 l_found BOOLEAN;
20 BEGIN
21 OPEN l_cur;
22 FETCH l_cur INTO l_number;
23 l_found := l_cur%found;
24 CLOSE l_cur;
25 RETURN l_found;
26
27 END is_enterprise_party;
28
29 FUNCTION has_pending_change (p_party_id IN NUMBER) RETURN BOOLEAN
30 IS PRAGMA AUTONOMOUS_TRANSACTION;
31
32 l_change BOOLEAN;
33
34 CURSOR l_address IS
35 SELECT 1
36 FROM pos_address_requests par, pos_supplier_mappings psm
37 WHERE psm.party_id = p_party_id
38 AND psm.mapping_id = par.mapping_id
39 AND par.request_status = 'PENDING';
40
41 CURSOR l_bus_class IS
42 SELECT 1
43 FROM pos_bus_class_reqs pbcr, pos_supplier_mappings psm
44 WHERE psm.party_id = p_party_id
45 AND psm.mapping_id = pbcr.mapping_id
46 AND pbcr.request_status = 'PENDING';
47
48 CURSOR l_contacts IS
49 SELECT 1
50 FROM pos_contact_requests pcr, pos_supplier_mappings psm
51 WHERE psm.party_id = p_party_id
52 AND psm.mapping_id = pcr.mapping_id
53 AND pcr.request_status = 'PENDING';
54
55 CURSOR l_cont_addr IS
56 SELECT 1
57 FROM pos_cont_addr_requests pcar, pos_supplier_mappings psm
58 WHERE psm.party_id = p_party_id
59 AND psm.mapping_id = pcar.mapping_id
60 AND pcar.request_status = 'PENDING';
61
62 CURSOR l_product_service IS
63 SELECT 1
64 FROM pos_product_service_requests ppsr, pos_supplier_mappings psm
65 WHERE psm.party_id = p_party_id
66 AND psm.mapping_id = ppsr.mapping_id
67 AND ppsr.request_status = 'PENDING';
68
69 CURSOR l_acnt_cur IS
70
71 SELECT 1
72 from POS_ACNT_GEN_REQ req, IBY_TEMP_EXT_BANK_ACCTS temp, POS_SUPPLIER_MAPPINGS pmap
73 where req.mapping_id = pmap.mapping_id
74 and pmap.party_id = p_party_id
75 and temp.status in ('CORRECTED', 'NEW', 'IN_VERIFICATION', 'CHANGE_PENDING')
76 and req.temp_ext_bank_acct_id = temp.temp_ext_bank_acct_id
77 and rownum = 1
78
79 UNION
80
81 select 1
82 from pos_acnt_addr_req req, pos_supplier_mappings pmap, hz_party_sites hps
83 where req.request_status = 'PENDING'
84 and req.request_type = 'ADDRESS'
85 and req.mapping_id = pmap.mapping_id
86 and hps.party_site_id = req.party_site_id
87 and hps.status = 'A'
88 and pmap.party_id = p_party_id
89 and rownum = 1
90
91 UNION
92
93 select 1
94 from pos_acnt_addr_req req, pos_supplier_mappings pmap
95 where req.request_status = 'PENDING'
96 and req.request_type = 'SUPPLIER'
97 and req.mapping_id = pmap.mapping_id
98 and req.party_site_id is null
99 and pmap.party_id = p_party_id
100 and rownum = 1;
101
102 l_number NUMBER; -- dummy variable for cursors
103 l_found BOOLEAN;
104
105 BEGIN
106
107 OPEN l_address;
108 FETCH l_address INTO l_number;
109 l_found:= l_address%found;
110 CLOSE l_address;
111
112 IF (l_found) THEN
113 RETURN l_found;
114 END IF;
115
116 OPEN l_bus_class;
117 FETCH l_bus_class INTO l_number;
118 l_found:= l_bus_class%found;
119 CLOSE l_bus_class;
120
121 IF (l_found) THEN
122 RETURN l_found;
123 END IF;
124
125 OPEN l_contacts;
126 FETCH l_contacts INTO l_number;
127 l_found := l_contacts%found;
128 CLOSE l_contacts;
129
130 IF (l_found) THEN
131 RETURN l_found;
132 END IF;
133
134 OPEN l_cont_addr;
135 FETCH l_cont_addr INTO l_number;
136 l_found := l_cont_addr%found;
137 CLOSE l_cont_addr;
138
139 IF (l_found) THEN
140 RETURN l_found;
141 END IF;
142
143 OPEN l_acnt_cur;
144 FETCH l_acnt_cur INTO l_number;
145 l_found := l_acnt_cur%found;
146 CLOSE l_acnt_cur;
147
148 IF (l_found) THEN
149 RETURN l_found;
150 END IF;
151
152 OPEN l_product_service;
153 FETCH l_product_service INTO l_number;
154 l_found := l_product_service%found;
155 CLOSE l_product_service;
156
157 RETURN l_found;
158
159 END has_pending_change;
160
161 PROCEDURE get_party_info
162 (p_party_id IN NUMBER,
163 x_found OUT nocopy BOOLEAN,
164 x_party_type OUT nocopy VARCHAR2,
165 x_party_name OUT nocopy VARCHAR2)
166 IS
167 CURSOR l_cur IS
168 SELECT party_type, party_name
169 FROM hz_parties
170 WHERE party_id = p_party_id;
171 BEGIN
172 OPEN l_cur;
173 FETCH l_cur INTO x_party_type, x_party_name;
174 IF l_cur%found THEN
175 CLOSE l_cur;
176 x_found := TRUE;
177 RETURN;
178 ELSE
179 CLOSE l_cur;
180 x_found := FALSE;
181 x_party_type := NULL;
182 x_party_name := NULL;
183 RETURN;
184 END IF;
185 END get_party_info;
186
187 PROCEDURE check_party_for_veto
188 (p_party_id IN NUMBER,
189 p_is_from_party IN VARCHAR2,
190 x_return_status OUT nocopy VARCHAR2)
191 IS
192 l_party_type hz_parties.party_type%TYPE;
193 l_party_name hz_parties.party_name%TYPE;
194 l_found BOOLEAN;
195 BEGIN
196 get_party_info(p_party_id, l_found, l_party_type, l_party_name);
197
198 IF l_found = FALSE THEN
199 fnd_message.set_name('POS','POS_PTYM_INVALID_PARTY_ID');
200 fnd_message.set_token('PARTY_ID', p_party_id);
201 fnd_msg_pub.ADD;
202 x_return_status := fnd_api.g_ret_sts_unexp_error;
203 RETURN;
204 END IF;
205
206 -- check parties for pending changes; if pending, fail merge
207 IF (p_is_from_party = 'Y' AND has_pending_change(p_party_id)) THEN
208 fnd_message.set_name('POS', 'POS_PENDING_CHANGE');
209 fnd_message.set_token('PARTY_NAME', l_party_name);
210 fnd_msg_pub.ADD;
211 x_return_status := fnd_api.g_ret_sts_error;
212 RETURN;
213 END IF;
214
215 IF l_party_type = 'ORGANIZATION' THEN
216
217 IF is_enterprise_party(p_party_id) THEN
218
219 fnd_message.set_name('POS','POS_PTYM_IS_ENTERPRISE');
220 fnd_message.set_token('PARTY_NAME', l_party_name);
221 fnd_msg_pub.ADD;
222 x_return_status := fnd_api.g_ret_sts_error;
223 RETURN;
224
225 ELSE
226
227 x_return_status := fnd_api.g_ret_sts_success;
228 RETURN;
229
230 END IF;
231
232 ELSE
233 -- party type is not ORGANIZATION
234 x_return_status := fnd_api.g_ret_sts_success;
235 RETURN;
236
237 END IF;
238
239 END check_party_for_veto;
240
241 PROCEDURE update_supplier_mapping(
242 p_old_party_id IN NUMBER,
243 p_new_party_id IN NUMBER,
244 x_return_status IN OUT nocopy VARCHAR2
245 )
246 IS
247
248 BEGIN
249
250 UPDATE pos_supplier_mappings
251 SET party_id = p_new_party_id,
252 last_updated_by = FND_GLOBAL.user_id,
253 last_update_date = SYSDATE,
254 last_update_login = FND_GLOBAL.login_id
255 WHERE mapping_id IN
256 (SELECT mapping_id
257 FROM pos_supplier_mappings
258 WHERE party_id = p_old_party_id
259 );
260
261 x_return_status := fnd_api.g_ret_sts_success;
262
263 END update_supplier_mapping;
264
265 -- public method called by party merge program
266 PROCEDURE party_merge_routine
267 (p_entity_name IN VARCHAR2,
268 p_from_id IN NUMBER,
269 p_to_id IN OUT nocopy NUMBER,
270 p_from_fk_id IN NUMBER,
271 p_to_fk_id IN NUMBER,
272 p_parent_entity_name IN VARCHAR2,
273 p_batch_id IN VARCHAR2,
274 p_batch_party_id IN VARCHAR2,
275 x_return_status IN OUT nocopy VARCHAR2
276 )
277 IS
278 l_from_party_id NUMBER;
279 l_to_party_id NUMBER;
280
281 CURSOR l_party_id_cur(p_party_site_id NUMBER) IS
282 SELECT party_id
283 FROM hz_party_sites
284 WHERE party_site_id = p_party_site_id;
285
286 l_return_status VARCHAR2(2);
287 l_module fnd_log_messages.module%TYPE;
288 l_invalid_param VARCHAR2(30);
289
290 CURSOR l_party_site_cur(p_party_site_id NUMBER) IS
291 SELECT party_id
292 FROM hz_party_sites
293 WHERE party_site_id = p_party_site_id;
294
295 CURSOR l_party_cur(p_party_id NUMBER) IS
296 SELECT party_id
297 FROM hz_parties
298 WHERE party_id = p_party_id;
299 BEGIN
300
301 l_module := g_log_module || 'PARTY_MERGE_ROUTINE';
302
303 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
304 fnd_log.string(fnd_log.level_procedure, l_module, 'p_parent_entity_name ' || p_parent_entity_name);
305
306 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
307 fnd_log.string(fnd_log.level_procedure, l_module, 'p_entity_name ' || p_entity_name);
308 END IF;
309
310
311 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
312 fnd_log.string(fnd_log.level_procedure, l_module, 'p_from_fk_id ' || p_from_fk_id);
313 END IF;
314
315
316 IF ( fnd_log.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
317 fnd_log.string(fnd_log.level_procedure, l_module, 'p_to_fk_id ' || p_to_fk_id);
318 END IF;
319
320 END IF;
321
322 -- make sure critical input parameters are not null
323 IF p_parent_entity_name IS NULL OR p_from_fk_id IS NULL OR p_to_fk_id IS NULL THEN
324
325 IF p_parent_entity_name IS NULL THEN
326 l_invalid_param := 'p_parent_entity_name';
327 ELSIF p_from_fk_id IS NULL THEN
328 l_invalid_param := 'p_from_fk_id';
329 ELSE
330 l_invalid_param := 'p_to_fk_id';
331 END IF;
332
333 fnd_message.set_name('POS','POS_PTYM_NULL_PARAM');
334 fnd_message.set_token('INPUT_PARAMETER',l_invalid_param);
335 fnd_msg_pub.ADD;
336 x_return_status := fnd_api.g_ret_sts_unexp_error;
337 RETURN;
338
339 END IF;
340
341 IF p_parent_entity_name NOT IN ('HZ_PARTIES', 'HZ_PARTY_SITES') THEN
342 fnd_message.set_name('POS','POS_PTYM_BAD_PARENT_ENTITY');
343 fnd_message.set_token('PARENT_ENTITY_NAME', p_parent_entity_name);
344 fnd_msg_pub.ADD;
345 x_return_status := fnd_api.g_ret_sts_unexp_error;
346 RETURN;
347 END IF;
348
349 -- validate p_from_fk_id and p_to_fk_id
350 IF p_parent_entity_name = 'HZ_PARTIES' THEN
351
352 IF p_from_fk_id IS NULL THEN
353 fnd_message.set_name('POS','POS_PTYM_BAD_PARTY_ID');
354 fnd_message.set_token('PARTY_ID', p_from_fk_id);
355 fnd_msg_pub.ADD;
356 x_return_status := fnd_api.g_ret_sts_error;
357 RETURN;
358 END IF;
359
360 OPEN l_party_cur(p_from_fk_id);
361 FETCH l_party_cur INTO l_from_party_id;
362 IF l_party_cur%notfound THEN
363 CLOSE l_party_cur;
364 fnd_message.set_name('POS','POS_PTYM_BAD_PARTY_ID');
365 fnd_message.set_token('PARTY_SITE_ID', p_to_fk_id);
366 fnd_msg_pub.ADD;
367 x_return_status := fnd_api.g_ret_sts_error;
368 RETURN;
369 END IF;
370 CLOSE l_party_cur;
371
372 IF p_to_fk_id IS NULL THEN
373 fnd_message.set_name('POS','POS_PTYM_BAD_PARTY_ID');
374 fnd_message.set_token('PARTY_ID', p_to_fk_id);
375 fnd_msg_pub.ADD;
376 x_return_status := fnd_api.g_ret_sts_error;
377 RETURN;
378 END IF;
379
380 OPEN l_party_cur(p_to_fk_id);
381 FETCH l_party_cur INTO l_to_party_id;
382 IF l_party_cur%notfound THEN
383 CLOSE l_party_cur;
384 fnd_message.set_name('POS','POS_PTYM_BAD_PARTY_ID');
385 fnd_message.set_token('PARTY_ID', p_to_fk_id);
386 fnd_msg_pub.ADD;
387 x_return_status := fnd_api.g_ret_sts_error;
388 RETURN;
389 END IF;
390 CLOSE l_party_cur;
391
392 ELSIF p_parent_entity_name = 'HZ_PARTY_SITES' THEN
393
394 OPEN l_party_site_cur(p_from_fk_id);
395 FETCH l_party_site_cur INTO l_from_party_id;
396 IF l_party_site_cur%notfound THEN
397 CLOSE l_party_site_cur;
398 fnd_message.set_name('POS','POS_PTYM_BAD_PARTY_SITE_ID');
399 fnd_message.set_token('PARTY_SITE_ID', p_from_fk_id);
400 fnd_msg_pub.ADD;
401 x_return_status := fnd_api.g_ret_sts_error;
402 RETURN;
403 END IF;
404 CLOSE l_party_site_cur;
405
406 OPEN l_party_site_cur(p_to_fk_id);
407 FETCH l_party_site_cur INTO l_to_party_id;
408 IF l_party_site_cur%notfound THEN
409 CLOSE l_party_site_cur;
410 fnd_message.set_name('POS','POS_PTYM_BAD_PARTY_SITE_ID');
411 fnd_message.set_token('PARTY_SITE_ID', p_to_fk_id);
412 fnd_msg_pub.ADD;
413 x_return_status := fnd_api.g_ret_sts_error;
414 RETURN;
415 END IF;
416 CLOSE l_party_site_cur;
417
418 END IF;
419
420 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
421 fnd_log.string(fnd_log.level_statement, l_module, 'l_from_party_id ' || l_from_party_id);
422
423 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
424 fnd_log.string(fnd_log.level_statement, l_module, 'l_to_party_id ' || l_to_party_id);
425 END IF;
426
427 END IF;
428
429 check_party_for_veto(l_from_party_id, 'Y', l_return_status);
430
431 -- If the check fails then veto the merge.
432 if (l_return_status = fnd_api.g_ret_sts_error) then
433 x_return_status := fnd_api.g_ret_sts_error;
434 RETURN;
435 END IF;
436
437 update_supplier_mapping(p_from_fk_id, p_to_fk_id, l_return_status);
438
439 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
440 fnd_log.string(fnd_log.level_statement, l_module, 'return_status for from party ' || l_return_status);
441 END IF;
442
443 IF l_return_status = fnd_api.g_ret_sts_success THEN
444 check_party_for_veto(l_to_party_id, 'N', l_return_status);
445 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
446 fnd_log.string(fnd_log.level_statement, l_module, 'return_status for from party ' || l_return_status);
447 END IF;
448 END IF;
449
450 x_return_status := l_return_status;
451
452 EXCEPTION
453 WHEN OTHERS THEN
454 fnd_message.set_name('POS','POS_PTYM_UNEXP_ERR');
455 fnd_message.set_token('ERROR',Sqlerrm);
456 fnd_msg_pub.ADD;
457 x_return_status := fnd_api.g_ret_sts_unexp_error;
458
459 END party_merge_routine;
460
461 END pos_party_merge_pkg;