1 PACKAGE BODY JTF_TERR_NA_MERGE_PUB AS
2 /* $Header: jtftptnb.pls 120.5 2006/09/29 21:21:54 spai noship $ */
3 -- ---------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TERR_NA_MERGE_PUB
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- Joint task force named account territory manager public api's.
10 -- This package is a public API for party merge for named account
11 -- Territory
12 -- Procedures:
13 -- (see below for specification)
14 --
15 -- NOTES
16 -- This package is publicly available for use
17 --
18 -- HISTORY
19 -- 04/15/03 SGKUMAR Created
20 -- 08/24/06 JULOU Forward porting 5071732
21 -- 1: Merge From Named Party To Named Party (Same Territory Group).
22 -- 2: Merge From Named Party To Named Party (Distinct Territory Region).
23 -- 3: Merge From Non-Named Party To Named Party (No Impact).
24 -- 4: Merge From Named Party To Non-Named Party (Non-Named Party becomes Named).
25
26 -- End of Comments
27
28 FUNCTION get_party_name(p_party_site_id NUMBER) RETURN VARCHAR2
29 AS
30 p_party_name VARCHAR2(80);
31 BEGIN
32 select hzp.party_name
33 into p_party_name
34 from hz_parties hzp, hz_party_sites hzps
35 where hzp.party_id = hzps.party_id
36 and hzps.party_site_id = p_party_site_id;
37
38 return p_party_name;
39
40 EXCEPTION
41 WHEN OTHERS THEN
42 p_party_name := null;
43 return p_party_name;
44 END get_party_name;
45
46
47 PROCEDURE create_acct_mappings(p_acct_id IN NUMBER,
48 p_party_id IN NUMBER,
49 p_party_site_id IN NUMBER,
50 p_user_id IN NUMBER)
51 AS
52 p_business_name VARCHAR2(360);
53 p_trade_name VARCHAR2(240);
54 p_postal_code VARCHAR2(60);
55 p_party_count NUMBER;
56
57
58 BEGIN
59
60 p_business_name := null;
61 p_trade_name := null;
62 p_postal_code := null;
63
64 BEGIN
65
66 SELECT H3.party_name,
67 H3.known_as,
68 H1.postal_code
69 INTO p_business_name,
70 p_trade_name,
71 p_postal_code
72 FROM HZ_PARTIES H3,
73 HZ_LOCATIONS H1,
74 HZ_PARTY_SITES H2
75 WHERE h3.party_id = h2.party_id
76 AND h2.location_id = h1.location_id
77 AND h3.party_id = p_party_id
78 AND h2.party_site_id = p_party_site_id;
79
80 EXCEPTION
81 WHEN NO_DATA_FOUND THEN
82 NULL;
83 END;
84
85 IF (p_business_name IS NOT NULL) THEN
86 /* key name for business name */
87 INSERT INTO jtf_tty_acct_qual_maps
88 (ACCOUNT_QUAL_MAP_ID,
89 OBJECT_VERSION_NUMBER,
90 NAMED_ACCOUNT_ID,
91 QUAL_USG_ID,
92 COMPARISON_OPERATOR,
93 VALUE1_CHAR,
94 VALUE2_CHAR,
95 CREATED_BY,
96 CREATION_DATE,
97 LAST_UPDATED_BY,
98 LAST_UPDATE_DATE)
99 (
100 SELECT jtf_tty_acct_qual_maps_s.NEXTVAL,
101 1,
102 p_acct_id,
103 -1012,
104 '=',
105 UPPER(p_business_name),
106 NULL,
107 p_user_id,
108 SYSDATE,
109 p_user_id,
110 SYSDATE FROM dual);
111 END IF;
112
113 /* key name for trade name */
114 IF (p_trade_name IS NOT NULL) THEN
115 INSERT INTO jtf_tty_acct_qual_maps
116 (ACCOUNT_QUAL_MAP_ID,
117 OBJECT_VERSION_NUMBER,
118 NAMED_ACCOUNT_ID,
119 QUAL_USG_ID,
120 COMPARISON_OPERATOR,
121 VALUE1_CHAR,
122 VALUE2_CHAR,
123 CREATED_BY,
124 CREATION_DATE,
125 LAST_UPDATED_BY,
126 LAST_UPDATE_DATE)
127 (SELECT jtf_tty_acct_qual_maps_s.NEXTVAL,
128 1,
129 p_acct_id,
130 -1012,
131 '=',
132 UPPER(p_trade_name),
133 NULL,
134 p_user_id,
135 SYSDATE,
136 p_user_id,
137 SYSDATE FROM dual);
138 END IF;
139
140 /* key name for postal code */
141 IF (p_postal_code IS NOT NULL) THEN
142 INSERT INTO jtf_tty_acct_qual_maps
143 (ACCOUNT_QUAL_MAP_ID,
144 OBJECT_VERSION_NUMBER,
145 NAMED_ACCOUNT_ID,
146 QUAL_USG_ID,
147 COMPARISON_OPERATOR,
148 VALUE1_CHAR,
149 VALUE2_CHAR,
150 CREATED_BY,
151 CREATION_DATE,
152 LAST_UPDATED_BY,
153 LAST_UPDATE_DATE)
154 (SELECT jtf_tty_acct_qual_maps_s.NEXTVAL,
155 1,
156 p_acct_id,
157 -1007,
158 '=',
159 p_postal_code,
160 NULL,
161 p_user_id,
162 SYSDATE,
163 p_user_id,
164 SYSDATE FROM dual);
165 END IF;
166 END create_acct_mappings;
167
168 /* ---------------------------- PARTY MERGE ------------------------
169 ** procedure to merge parties: create exception if the from to
170 ** party or the source is a named account, otherwise do not do anything
171 ** ----------------------------------------------------------------- */
172 PROCEDURE party_merge( p_entity_name IN VARCHAR2,
173 p_from_id IN NUMBER,
174 x_to_id OUT NOCOPY NUMBER,
175 p_from_fk_id IN NUMBER,
176 p_to_fk_id IN NUMBER,
177 p_parent_entity_name IN VARCHAR2,
178 p_batch_id IN NUMBER,
179 p_batch_party_id IN NUMBER,
180 x_return_status OUT NOCOPY VARCHAR2 )
181 IS
182
183 BEGIN
184 x_return_status := FND_API.G_RET_STS_SUCCESS;
185 END party_merge;
186
187
188 /* ---------------------------- PARTY SITE MERGE ------------------------
189 ** procedure to merge party sites
190 ** ----------------------------------------------------------------- */
191 PROCEDURE party_site_merge( p_entity_name IN VARCHAR2,
192 p_from_id IN NUMBER,
193 x_to_id OUT NOCOPY NUMBER,
194 p_from_fk_id IN NUMBER,
195 p_to_fk_id IN NUMBER,
196 p_parent_entity_name IN VARCHAR2,
197 p_batch_id IN NUMBER,
198 p_batch_party_id IN NUMBER,
199 x_return_status OUT NOCOPY VARCHAR2 )
200 IS
201
202 l_api_name CONSTANT VARCHAR2(30) := 'TERR_NA_PARTY_SITE_MERGE';
203 p_from_party VARCHAR2(80);
204 p_to_party VARCHAR2(80);
205 l_dist_terr_region VARCHAR2(1);
206 l_cust_name_used_to VARCHAR2(1);
207 p_from_na_flag VARCHAR2(1);
208 p_to_na_flag VARCHAR2(1);
209 p_to_na_id NUMBER;
210 p_from_na_id NUMBER;
211 p_user_id NUMBER;
212 l_from_party_id NUMBER;
213 l_to_party_id NUMBER;
214 l_to_party_na_exists VARCHAR2(1);
215 l_to_na_exists VARCHAR2(1);
216 l_acct_qual_maps_exist VARCHAR2(1);
217 l_terr_group_id NUMBER;
218
219 CURSOR c_from_na_id(p_party_id NUMBER) IS
220 SELECT named_account_id na_id
221 FROM jtf_tty_named_accts
222 WHERE party_id = p_party_id;
223
224 CURSOR c_to_na_id(p_party_id NUMBER) IS
225 SELECT named_account_id na_id
226 FROM jtf_tty_named_accts
227 WHERE party_id = p_party_id;
228
229 CURSOR c_get_tga_details (p_na_id NUMBER) IS
230 SELECT tga.terr_group_account_id, tga.terr_group_id, tg.matching_rule_code
231 FROM jtf_tty_terr_grp_accts tga, jtf_tty_terr_groups tg
232 WHERE tga.terr_group_id = tg.terr_group_id
233 AND tga.named_account_id = p_na_id;
234
235 BEGIN
236
237 SAVEPOINT TERR_NA_PARTY_SITE_MERGE_PUB;
238
239 l_dist_terr_region := 'N';
240 l_cust_name_used_to := 'N';
241 p_from_na_flag := 'N';
242 p_to_na_flag := 'N';
243 l_to_na_exists := 'N';
244 l_to_party_na_exists := 'N';
245 l_acct_qual_maps_exist := 'N';
246 l_terr_group_id := -1;
247
248 FND_FILE.PUT_LINE(FND_FILE.LOG,'JTY PARTY SITE MERGE BEGIN: '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
249 p_user_id := fnd_global.user_id;
250
251 /* If from and To party sites are the same then do nothing.
252 We will not merge named accounts */
253 IF p_from_fk_id = p_to_fk_id THEN
254 x_to_id := p_from_id;
255 RETURN;
256 END IF;
257
258 x_return_status := fnd_api.g_ret_sts_success;
259
260 IF ( p_entity_name <> 'JTF_TTY_NAMED_ACCTS' OR p_parent_entity_name <> 'HZ_PARTY_SITES' ) THEN
261 FND_FILE.PUT_LINE(FND_FILE.LOG,' Entity error');
262 fnd_message.set_name ('JTF', 'JTF_TERR_ENTITY_NAME_ERR');
263 fnd_message.set_token('P_ENTITY',p_entity_name);
264 fnd_message.set_token('P_PARENT_ENTITY',p_parent_entity_name);
265 FND_MSG_PUB.add;
266
267 x_return_status := fnd_api.g_ret_sts_error;
268 RAISE fnd_api.g_exc_error;
269 END IF;
270
271 -- dbms_output.put_line (' Inside Party Site Merge ');
272
273 /* Check the from party is a Named Account */
274 BEGIN
275 FND_FILE.PUT_LINE(FND_FILE.LOG,' Check if merged FROM party site is a named account');
276
277 select 'Y', named_account_id
278 into p_from_na_flag, p_from_na_id
279 from jtf_tty_named_accts
280 where party_site_id = p_from_fk_id
281 and rownum < 2;
282 EXCEPTION
283 WHEN NO_DATA_FOUND THEN
284 p_from_na_flag := 'N';
285 -- dbms_output.put_line (' No Data Found : p_from_na_flag ' || p_from_fk_id);
286 END;
287
288 -- dbms_output.put_line (' p_from_na_flag ' || p_from_na_flag);
289
290 /* Check the To party is a Named Account */
291
292 BEGIN
293 FND_FILE.PUT_LINE(FND_FILE.LOG,' Check if merged to party is a named account');
294 select 'Y', named_account_id
295 into p_to_na_flag, p_to_na_id
296 from jtf_tty_named_accts
297 where party_site_id = p_to_fk_id
298 and rownum < 2;
299 EXCEPTION
300 WHEN NO_DATA_FOUND THEN
301 p_to_na_flag := 'N';
302 END;
303
304 IF (p_from_na_flag = 'Y' ) then
305
306 p_from_party := get_party_name(p_from_fk_id);
307
308 SELECT hzps.party_id
309 INTO l_from_party_id
310 FROM hz_party_sites hzps
311 WHERE hzps.party_site_id = p_from_fk_id
312 AND rownum < 2;
313
314 END IF;
315
316 IF (p_to_na_flag = 'Y' ) then
317
318 p_to_party := get_party_name(p_to_fk_id);
319
320 SELECT hzps.party_id
321 INTO l_to_party_id
322 FROM hz_party_sites hzps
323 WHERE hzps.party_site_id = p_to_fk_id
324 AND rownum < 2;
325
326 END IF;
327
328 -- dbms_output.put_line (' p_to_na_flag ' || p_to_na_flag);
329
330 IF (p_from_na_flag = 'Y' and p_to_na_flag = 'Y' ) then
331
332 /* Find all TGs that from Named Account belongs to */
333
334 FOR tga_rec IN c_get_tga_details( p_na_id => p_from_na_id )
335 LOOP
336
337 l_terr_group_id := tga_rec.terr_group_id;
338
339 /* find out whether any non-overlapping Territory groups exist for the from and to parties */
340 BEGIN
341 select 'N'
342 into l_dist_terr_region
343 from jtf_tty_terr_grp_accts jtga_outer
344 where jtga_outer.named_account_id = p_to_na_id
345 and jtga_outer.terr_group_id = tga_rec.terr_group_id
346 and rownum < 2;
347
348 EXCEPTION
349 WHEN NO_DATA_FOUND THEN
350 l_dist_terr_region := 'Y';
351 END;
352
353 /* S1: Merge From Named Party To Named Party (Same Territory Group). */
354 IF ( l_dist_terr_region = 'N' ) then
355
356 FND_FILE.PUT_LINE(FND_FILE.LOG,' S1: Merge From Named Account To Named Account (Same Territory Group)');
357 FND_FILE.PUT_LINE(FND_FILE.LOG,' S1: territory group id : ' || to_char(l_terr_group_id));
358
359 /* Delete records from JTF_TERR_... tables corresponding to the named account */
360 JTF_TTY_GEN_TERR_PVT.delete_TGA(
361 p_terr_grp_acct_id => tga_rec.terr_group_account_id,
362 p_terr_group_id => tga_rec.terr_group_id,
363 p_catchall_terr_id =>-1,
364 p_change_type =>'SALES_TEAM_UPDATE'
365 );
366
367 /* Delete the named account resources for the from party */
368 DELETE FROM jtf_tty_named_acct_rsc
369 WHERE terr_group_account_id = tga_rec.terr_group_account_id;
370
371 /* Delete the terr_group accounts for the from party */
372 DELETE FROM jtf_tty_terr_grp_accts
373 WHERE terr_group_account_id = tga_rec.terr_group_account_id;
374
375 if ( tga_rec.matching_rule_code = '1' ) then
376 UPDATE jtf_tty_acct_qual_maps
377 SET named_account_id = p_to_na_id
378 WHERE named_account_id = p_from_na_id;
379 end if;
380
381 ELSE
382
383 /* S2: Merge From Named Party To Named Party (Distinct Territory Region). */
384
385 FND_FILE.PUT_LINE(FND_FILE.LOG,' S2: Merge From Named Account To Named Account (Distinct Territory Region) ');
389 UPDATE jtf_tty_terr_grp_accts jtga
386 FND_FILE.PUT_LINE(FND_FILE.LOG,' S2: territory group id : ' || to_char(l_terr_group_id));
387
388 /* point the non-overlapping FROM named account to the TO named account */
390 SET jtga.named_account_id = p_to_na_id
391 WHERE jtga.terr_group_account_id = tga_rec.terr_group_account_id;
392
393 l_acct_qual_maps_exist := 'N';
394
395 BEGIN
396 SELECT 'Y'
397 INTO l_acct_qual_maps_exist
398 FROM jtf_tty_acct_qual_maps
399 WHERE named_account_id = p_to_na_id
400 AND rownum < 2;
401 EXCEPTION
402 WHEN NO_DATA_FOUND THEN
403 l_acct_qual_maps_exist := 'N';
404 END;
405
406 /* if to_named_acct has no values in acct qual maps */
407 if ((tga_rec.matching_rule_code = '1') and
408 ( l_acct_qual_maps_exist = 'N' )) then
409 create_acct_mappings(p_to_na_id, l_to_party_id, p_to_fk_id, p_user_id );
410 end if;
411
412 if ( tga_rec.matching_rule_code = '1' ) then
413 UPDATE jtf_tty_acct_qual_maps
414 SET named_account_id = p_to_na_id
415 WHERE named_account_id = p_from_na_id;
416 end if;
417
418 /* Recreate the from-NA territory as it needs new qualifier values */
419 JTF_TTY_GEN_TERR_PVT.create_terr_for_na(tga_rec.terr_group_account_id, tga_rec.terr_group_id );
420
421 END IF; /* Same or different TG */
422
423 END LOOP; /* Processed all TGs */
424
425 /* Do delete from jtf_tty_named_accts and acct_qual_maps after all processing is done */
426
427 DELETE FROM jtf_tty_acct_qual_maps
428 WHERE named_account_id = p_from_na_id;
429
430 DELETE FROM jtf_tty_named_accts
431 WHERE party_id = l_from_party_id
432 AND party_site_id = p_from_fk_id;
433
434 END IF; /* both Named accounts */
435
436 /* S3: Merge From Non-Named Party To Named Party (No Impact). */
437
438 IF (p_from_na_flag = 'N' and p_to_na_flag = 'Y') THEN
439 FND_FILE.PUT_LINE(FND_FILE.LOG,' S3: Merge From Non-Named Account To Named Account (No Impact)');
440 END IF;
441
442 /* S4: Merge From Named Account To Non-Named Account (Non-Named Account becomes Named Account). */
443
444 IF (p_from_na_flag = 'Y' and p_to_na_flag = 'N') THEN
445
446 FND_FILE.PUT_LINE(FND_FILE.LOG,' S4: Merge From Named Account To Non-Named Account (Non-Named Account becomes Named)');
447
448 BEGIN
449 SELECT hzps.party_id
450 INTO l_to_party_id
451 FROM hz_party_sites hzps
452 WHERE hzps.party_site_id = p_to_fk_id
453 AND rownum < 2;
454 EXCEPTION
455 WHEN NO_DATA_FOUND THEN
456 null;
457 END;
458
459
460 FOR tga_rec IN c_get_tga_details( p_na_id => p_from_na_id )
461 LOOP
462
463 l_terr_group_id := tga_rec.terr_group_id;
464
465 IF (tga_rec.matching_rule_code IN ( '3', '4')) then
466
467 l_to_party_na_exists := 'N';
468 BEGIN
469 SELECT 'Y'
470 INTO l_to_party_na_exists
471 FROM jtf_tty_terr_grp_accts tga,
472 jtf_tty_named_accts jna
473 WHERE jna.named_account_id = tga.named_account_id
474 AND jna.party_id = l_to_party_id
475 AND tga.terr_group_id = tga_rec.terr_group_id
476 AND rownum < 2;
477 EXCEPTION
478 WHEN NO_DATA_FOUND THEN
479 l_to_party_na_exists := 'N';
480 END;
481
482
483 IF ( l_to_party_na_exists = 'Y' ) then
484
485 FND_FILE.PUT_LINE(FND_FILE.LOG,' S4: territory group id :' || to_char(l_terr_group_id) );
486 FND_FILE.PUT_LINE(FND_FILE.LOG,' S4: Party Matching Rule of above TG is either Registry Id or DUNS' );
487 FND_FILE.PUT_LINE(FND_FILE.LOG,' S4: The Party corresponding to To Party Site is a NA in the above TG' );
488
489 /* Delete records from JTF_TERR_... tables corresponding to the from named account */
490 JTF_TTY_GEN_TERR_PVT.delete_TGA(
491 p_terr_grp_acct_id => tga_rec.terr_group_account_id,
492 p_terr_group_id => tga_rec.terr_group_id,
493 p_catchall_terr_id =>-1,
494 p_change_type =>'SALES_TEAM_UPDATE'
495 );
496
497
498 /* Delete the named account resources for the from party */
499 DELETE FROM jtf_tty_named_acct_rsc
500 WHERE terr_group_account_id = tga_rec.terr_group_account_id;
501
502 /* Delete the terr_group accounts for the from party */
503 DELETE FROM jtf_tty_terr_grp_accts
504 WHERE terr_group_account_id = tga_rec.terr_group_account_id;
505
506 /*
507 DELETE FROM jtf_tty_acct_qual_maps
508 WHERE named_account_id = p_from_na_id;
509 */
510
511 END IF; /* l_to_party_na_exists = 'N' */
512 END IF; /* l_matching_rule_code = 3 or 4 */
513
514
515 IF ( ( tga_rec.matching_rule_code NOT IN ( '3', '4' ))
516 OR ( l_to_party_na_exists = 'N' ) ) then
517
518 FND_FILE.PUT_LINE(FND_FILE.LOG,' S4: territory group id :' || to_char(l_terr_group_id) );
519 FND_FILE.PUT_LINE(FND_FILE.LOG,' S4: Party Matching Rule of above TG is neither Registry Id nor DUNS' );
520 FND_FILE.PUT_LINE(FND_FILE.LOG,' S4: The Party corresponding to To Party Site is not a NA in the above TG' );
521
522 l_to_na_exists := 'N';
523
524 /*
525 DELETE FROM jtf_tty_acct_qual_maps
526 WHERE named_account_id = p_from_na_id;
527 */
528
529 BEGIN
530 SELECT 'Y'
531 INTO l_to_na_exists
532 FROM jtf_tty_named_accts
533 WHERE party_id = l_to_party_id
534 AND party_site_id = p_to_fk_id;
535 EXCEPTION
536 WHEN NO_DATA_FOUND THEN
537 l_to_na_exists := 'N';
538 END;
539
540 IF ( l_to_na_exists = 'N' ) then
541
542 UPDATE jtf_tty_named_accts
543 SET party_id = l_to_party_id,
544 party_site_id = p_to_fk_id
545 WHERE party_id = l_from_party_id
546 AND party_site_id = p_from_fk_id;
547
548 /* l_cust_name_used_to is 'N' if p_to_na_flag = 'N' */
549 if ( tga_rec.matching_rule_code = '1' ) then
550
551 create_acct_mappings(p_from_na_id, l_to_party_id, p_to_fk_id, p_user_id );
552 end if;
553
554 END IF;
555
556 /* Recreate the from-NA territory as it needs new qualifier values */
557 JTF_TTY_GEN_TERR_PVT.create_terr_for_na(tga_rec.terr_group_account_id, tga_rec.terr_group_id );
558
559 END IF;
560 END LOOP;
561
562 /* Do delete from jtf_tty_named_accts and acct_qual_maps after all processing is done */
563
564 DELETE FROM jtf_tty_named_accts
565 WHERE party_id = l_from_party_id
566 AND party_site_id = p_from_fk_id;
567
568 END IF; -- end from_na_flag = 'Y'
569
570 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
571 RAISE fnd_api.g_exc_error;
572 END IF;
573
574 FND_FILE.PUT_LINE(FND_FILE.LOG,'JTY PARTY SITE MERGE END: '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
575
576 EXCEPTION
577
578 WHEN fnd_api.g_exc_error THEN
579 x_return_status := fnd_api.g_ret_sts_error;
580 -- ROLLBACK TO TERR_NA_PARTY_MERGE_PUB;
581 FND_FILE.PUT_LINE(FND_FILE.LOG,'JTY PARTY SITE MERGE END: '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
582 RAISE;
583
584 WHEN OTHERS THEN
585 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
586 fnd_message.set_token('P_SQLCODE', SQLCODE);
587 fnd_message.set_token('P_SQLERRM', SQLERRM);
588 fnd_message.set_token('P_API_NAME', l_api_name);
589 FND_MSG_PUB.add;
590 x_return_status := fnd_api.g_ret_sts_unexp_error;
591 -- ROLLBACK TO TERR_NA_PARTY_MERGE_PUB;
592 FND_FILE.PUT_LINE(FND_FILE.LOG,'JTY PARTY SITE MERGE END: '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
593 RAISE;
594 END party_site_merge;
595
596 END JTF_TERR_NA_MERGE_PUB;
[Home] [Help]