[Home] [Help]
PACKAGE BODY: APPS.JTY_MIGRATION_PVT
Source
1 PACKAGE BODY JTY_MIGRATION_PVT AS
2 /* $Header: jtftrmgb.pls 120.6 2006/07/24 19:11:55 solin noship $ */
3 -- Start of Comments
4 -- ---------------------------------------------------
5 -- PACKAGE NAME: JTY_MIGRATION_PVT
6 -- PURPOSE
7 --
8 -- Procedures:
9 -- (see below for specification)
10 -- This package covers migration of territory data
11 --
12 -- The various procedures are for
13 -- 1. Territory Template Migration (add_seeded_territory_types) :
14 -- This procedure will create the
15 -- default templates for all the usages and for all the
16 -- existing organizations and also the Named account territory
17 -- template with territory type id -1
18 -- 2. Assign territory templates to existing territories (update_terr_type_for_terr) :
19 -- For the territories where the territory_type_id is null,
20 -- this procedure will assign default territory template or
21 -- named account territory template accordingly.
22 -- 3. Migrate the territory end-dates (update_terr_enddate_active) :
23 -- This procedure will update the territory and resource end_date_active
24 -- to start_date_active + 10 years
25 -- 4. update the self-service flag (update_self_service_flag) :
26 -- This procedure will update the self-service flag for exiting geography
27 -- and Named account territories
28 -- 5. Migration of escalation territories
29 -- 6. Update the access at the transaction type level
30 -- for every resource. Until 11.5.10 this was set at resource level
31 -- 7. Need to update party_site_id in JTF_TTY_NAMED_ACCTS for existing NAs.
32 --
33 -- NOTES
34 --
35 --
36 -- HISTORY
37 -- 08/08/05 JRADHAKR CREATED
38 -- 01/10/06 ACHANDA Fix bug # 4886227
39 --
40 -- End of Comments
41 --
42
43 -- This procedure will update the enabled_flag to 'Y' for all
44 -- active extisting templates.
45 -- In 11.5.10, the enabled_flag column was not used.
46
47 Procedure enable_existing_template
48 IS
49
50 BEGIN
51
52 update jtf_terr_types_all
53 set enabled_flag = 'Y'
54 where sysdate between start_date_active and nvl(end_date_active, sysdate);
55
56 commit;
57
58 EXCEPTION
59 when FND_API.G_EXC_ERROR then
60 -- Add proper error logging
61 -- dbms_output.put_line('1 ' || sqlerrm );
62 NULL;
63
64 when FND_API.G_EXC_UNEXPECTED_ERROR then
65 -- Add proper error logging
66 -- dbms_output.put_line('2 ' || sqlerrm );
67 NULL;
68 when others then
69 -- Add proper error logging
70 -- dbms_output.put_line('3 ' || sqlerrm );
71 NULL;
72 END enable_existing_template;
73
74
75
76 -- This procedure will update the access at the transaction type level
77 -- for every resource. Until 11.5.10 this was set at resource level.
78
79 /* this is replaced by the script jtftjtra.sql */
80 /*
81 Procedure update_rsc_access
82 IS
83
84 BEGIN
85
86 update jtf_terr_rsc_access_all jtra
87 set jtra.trans_access_code = (
88 select decode(jtr1.full_access_flag,'Y','FULL_ACCESS','VIEW')
89 from jtf_terr_rsc_all jtr1
90 where jtr1.TERR_RSC_ID = jtra.terr_rsc_id)
91 where jtra.terr_rsc_id in
92 (select jtr2.terr_rsc_id
93 from jtf_terr_rsc_all jtr2
94 , jtf_terr_usgs_all jtu
95 where jtr2.terr_id = jtu.terr_id
96 and jtu.source_id = -1001)
97 and jtra.trans_access_code is null;
98
99
100 update jtf_terr_rsc_access_all jtra
101 set jtra.trans_access_code = (
102 select decode(jtr1.primary_contact_flag,'Y','TEAM_LEADER','DEFAULT')
103 from jtf_terr_rsc_all jtr1
104 where jtr1.TERR_RSC_ID = jtra.terr_rsc_id)
105 where jtra.terr_rsc_id in
106 (select jtr2.terr_rsc_id
107 from jtf_terr_rsc_all jtr2
108 , jtf_terr_usgs_all jtu
109 where jtr2.terr_id = jtu.terr_id
110 and jtu.source_id = -1002)
111 and jtra.trans_access_code is null;
112
113
114 update jtf_terr_rsc_access_all jtra
115 set jtra.trans_access_code = (
116 select decode(jtr1.primary_contact_flag,'Y','PRIMARY_CONTACT','DEFAULT')
117 from jtf_terr_rsc_all jtr1
118 where jtr1.TERR_RSC_ID = jtra.terr_rsc_id)
119 where jtra.terr_rsc_id in
120 (select jtr2.terr_rsc_id
121 from jtf_terr_rsc_all jtr2
122 , jtf_terr_usgs_all jtu
123 where jtr2.terr_id = jtu.terr_id
124 and jtu.source_id = -1003)
125 and jtra.trans_access_code is null;
126
127
128 update jtf_terr_rsc_access_all jtra
129 set jtra.trans_access_code = 'DEFAULT'
130 where jtra.trans_access_code is null;
131
132 commit;
133
134 EXCEPTION
135 when FND_API.G_EXC_ERROR then
136 NULL;
137
138 when FND_API.G_EXC_UNEXPECTED_ERROR then
139 NULL;
140
141 when others then
142 NULL;
143 END update_rsc_access;
144 */
145
146 -- This procedure will update the territory and resource end_date_active
147 -- to start_date_active + 10 years
148
149 /* replaced by the script jtftterr.sql(jtf_terr_all) and jtftjtrr.sql(jtf_terr_rsc_all) */
150 /*
151 Procedure update_terr_enddate_active
152 IS
153
154 BEGIN
155
156 Update jtf_terr_all jterr
157 set jterr.end_date_active = jterr.start_date_active + 3652
158 where jterr.end_date_active is null;
159
160 Update jtf_terr_rsc_all jtr
161 set jtr.end_date_active = jtr.start_date_active + 3652
162 where jtr.end_date_active is null;
163
164 commit;
165
166 EXCEPTION
167 when FND_API.G_EXC_ERROR then
168 NULL;
169
170 when FND_API.G_EXC_UNEXPECTED_ERROR then
171 NULL;
172 when others then
173 NULL;
174 END update_terr_enddate_active;
175 */
176
177 -- This procedure will update the self-service flag for exiting geography
178 -- and Named account territories
179
180 /* replaced by the script jtftterr.sql */
181 /*
182 Procedure update_self_service_flag
183 IS
184
185 BEGIN
186
187 Update jtf_terr_all jtabs
188 set enable_self_service = 'Y'
189 where (GEO_TERR_FLAG = 'Y' or NAMED_ACCOUNT_FLAG = 'Y')
190 and enable_self_service is null;
191
192 commit;
193
194 EXCEPTION
195 when FND_API.G_EXC_ERROR then
196 NULL;
197 when FND_API.G_EXC_UNEXPECTED_ERROR then
198 NULL;
199 when others then
200 NULL;
201 END update_self_service_flag;
202 */
203
204
205 -- For the territories where the territory_type_id is null,
206 -- this procedure will assign default territory template or
207 -- named account territory template accordingly.
208
209 /* replaced by the script jtftterr.sql */
210 /*
211 Procedure update_terr_type_for_terr
212 IS
213
214 BEGIN
215
216 -- Update the named account terrtories with territory template
217
218 update jtf_terr_all jt
219 set jt.territory_type_id = -1
220 where jt.named_account_flag = 'Y'
221 and jt.territory_type_id is null;
222
223 -- Update the self service geo terrtories with territory template
224
225 update jtf_terr_all jt
226 set jt.territory_type_id = -2
227 where jt.GEO_TERR_FLAG = 'Y'
228 and jt.territory_type_id is null;
229
230 -- Update non named account terrtories with territory template
231
232 update jtf_terr_all jt
233 set jt.territory_type_id =
234 (select jttu.terr_type_id
235 from jtf_terr_type_usgs_all jttu
236 , jtf_terr_usgs_all jtu
237 where jttu.source_id = jtu.source_id
238 and jt.terr_id = jtu.terr_id
239 and jttu.created_by = 2 -- territory templates shipped by Oracle
240 and jttu.terr_type_id not in (-1, -2) -- Eliminate named account
241 and jtu.org_id = jttu.org_id)
242 where jt.territory_type_id is null
243 and jt.terr_id <> 1;
244
245 commit;
246
247 EXCEPTION
248 when FND_API.G_EXC_ERROR then
249 NULL;
250 when FND_API.G_EXC_UNEXPECTED_ERROR then
251 NULL;
252 when others then
253 NULL;
254 END update_terr_type_for_terr;
255 */
256
257
258 -- Delete the Resource transaction type
259
260 Procedure delete_resource_trx_type
261 IS
262
263 BEGIN
264
265 -- delete from jtf_qual_usgs_all where qual_type_usg_id
266 -- in (select qual_type_usg_id
267 -- from jtf_qual_type_usgs_all
268 -- where qual_type_id = -1001);
269
270 delete from jtf_qual_type_usgs_all
271 where qual_type_id = -1001;
272
273 delete from jtf_qual_types_all
274 where qual_type_id = -1001;
275
276 commit;
277
278 EXCEPTION
279 when FND_API.G_EXC_ERROR then
280 -- Add proper error logging
281 -- dbms_output.put_line('10' || sqlerrm );
282 NULL;
286 NULL;
283 when FND_API.G_EXC_UNEXPECTED_ERROR then
284 -- Add proper error logging
285 -- dbms_output.put_line('11' || sqlerrm );
287 when others then
288 -- Add proper error logging
289 -- dbms_output.put_line('12' || sqlerrm );
290 NULL;
291
292 END delete_resource_trx_type;
293
294 -- This procedure will create the
295 -- default templates for all the usages and for all the
296 -- existing organizations and also the Named account territory
297 -- template with territory type id -1
298
299 Procedure add_seeded_territory_types
300 IS
301 l_migration_complete varchar2(1);
302 BEGIN
303
304
305 -- sql to check whethe migration script already ran
306 -- on this environment
307
308 BEGIN
309 select 'Y'
310 into l_migration_complete
311 from jtf_terr_types_all
312 where substr(name,1,7) = 'General'
313 and created_by = 2
314 and rownum < 2;
315 EXCEPTION
316 when FND_API.G_EXC_ERROR then
317 -- Add proper error logging
318 -- dbms_output.put_line('21' || sqlerrm );
319 NULL;
320 when FND_API.G_EXC_UNEXPECTED_ERROR then
321 -- Add proper error logging
322 -- dbms_output.put_line('22' || sqlerrm );
323 NULL;
324 when others then
325 -- Add proper error logging
326 -- dbms_output.put_line('23' || sqlerrm );
327 l_migration_complete := 'N';
328 NULL;
329 END;
330
331 -- If the migration script already executed on this env
332 -- then delete the records.
333
334 if l_migration_complete <> 'Y' then
335
336 -- Insert territory types for all the usages
337 -- and org_id
338
339 BEGIN
340 insert into jtf_terr_types_all
341 ( TERR_TYPE_ID
342 , LAST_UPDATED_BY
343 , LAST_UPDATE_DATE
344 , CREATED_BY
345 , CREATION_DATE
346 , APPLICATION_SHORT_NAME
347 , NAME
348 , ENABLED_FLAG
349 , DESCRIPTION
350 , START_DATE_ACTIVE
351 , ORG_ID
352 , ORIG_SYSTEM_REFERENCE_ID)
353 select jtf_terr_types_s.nextval
354 , 2
355 , sysdate
356 , 2
357 , sysdate
358 , 'JTF'
359 , 'General ' || meaning -- Will work with NLS team to check how to do translation.
360 , 'Y'
361 , 'General ' || meaning
362 , sysdate
363 , org_id
364 , source_id
365 from (select distinct jtu.org_id
366 , jtu.source_id
367 , jsa.meaning
368 from jtf_terr_usgs_all jtu
369 , jtf_sources_all jsa
370 where jsa.source_id = jtu.source_id);
371 EXCEPTION
372 when FND_API.G_EXC_ERROR then
373 -- Add proper error logging
374 -- dbms_output.put_line('24' || sqlerrm );
375 NULL;
376 when FND_API.G_EXC_UNEXPECTED_ERROR then
377 -- Add proper error logging
378 -- dbms_output.put_line('26' || sqlerrm );
379 NULL;
380 when others then
381 -- Add proper error logging
382 -- dbms_output.put_line('27' || sqlerrm );
383 NULL;
384 END;
385
386 BEGIN
387
388 -- insert territory templates for
389 -- named accounts
390 insert into jtf_terr_types_all
391 ( TERR_TYPE_ID
392 , LAST_UPDATED_BY
393 , LAST_UPDATE_DATE
394 , CREATED_BY
395 , CREATION_DATE
396 , APPLICATION_SHORT_NAME
397 , NAME
398 , ENABLED_FLAG
399 , DESCRIPTION
400 , START_DATE_ACTIVE
401 , ORG_ID
402 , ORIG_SYSTEM_REFERENCE_ID)
403 select -1
404 , 2
405 , sysdate
406 , 2
407 , sysdate
408 , 'JTF'
409 , 'Named Account'
410 , 'Y'
411 , 'Named Account Territories'
412 , sysdate
413 , organization_id
414 , -1001
415 from (select distinct organization_id
416 from hr_operating_units);
417 -- SOLIN, bug 5117193
418 -- Don't insert org_id -3113, so jtfmorsd.sql won't replicate Named Account
419 -- to other Orgs.
420 -- union
421 -- select -3113
422 -- from sys.dual);
423
424
425 insert into jtf_terr_types_all
426 ( TERR_TYPE_ID
427 , LAST_UPDATED_BY
428 , LAST_UPDATE_DATE
429 , CREATED_BY
430 , CREATION_DATE
431 , APPLICATION_SHORT_NAME
432 , NAME
433 , ENABLED_FLAG
434 , DESCRIPTION
435 , START_DATE_ACTIVE
436 , ORG_ID
437 , ORIG_SYSTEM_REFERENCE_ID)
438 select -2
439 , 2
440 , sysdate
441 , 2
442 , sysdate
443 , 'JTF'
444 , 'Geography'
445 , 'Y'
446 , 'Geography Territories'
447 , sysdate
448 , org_id
449 , -1001
450 from (select distinct org_id
451 from jtf_terr_all
452 where geo_terr_flag = 'Y');
453
454 EXCEPTION
455 when FND_API.G_EXC_ERROR then
456 -- Add proper error logging
457 -- dbms_output.put_line('28' || sqlerrm );
458 NULL;
459 when FND_API.G_EXC_UNEXPECTED_ERROR then
460 -- Add proper error logging
461 -- dbms_output.put_line('29' || sqlerrm );
462 NULL;
463 when others then
464 -- Add proper error logging
468
465 -- dbms_output.put_line('30' || sqlerrm );
466 NULL;
467 END;
469 BEGIN
470 -- For every row in jtf_terr_type_all table
471 -- insert a corresponding row in
472 -- jtf_terr_type_usgs_all table
473
474 insert into jtf_terr_type_usgs_all
475 ( TERR_TYPE_USG_ID
476 , LAST_UPDATED_BY
477 , LAST_UPDATE_DATE
478 , CREATED_BY
479 , CREATION_DATE
480 , TERR_TYPE_ID
481 , SOURCE_ID
482 , ORG_ID
483 )
484 select jtf_terr_type_usgs_s.nextval
485 , 2
486 , sysdate
487 , 2
488 , sysdate
489 , terr_type_id
490 , ORIG_SYSTEM_REFERENCE_ID -- Used this column to temp store source_id
491 , org_id
492 from jtf_terr_types_all
493 where ORIG_SYSTEM_REFERENCE_ID is not null
494 and CREATED_BY = 2;
495
496 EXCEPTION
497 when FND_API.G_EXC_ERROR then
498 -- Add proper error logging
499 -- dbms_output.put_line('31' || sqlerrm );
500 NULL;
501 when FND_API.G_EXC_UNEXPECTED_ERROR then
502 -- Add proper error logging
503 -- dbms_output.put_line('32' || sqlerrm );
504 NULL;
505 when others then
506 -- Add proper error logging
507 -- dbms_output.put_line('33' || sqlerrm );
508 NULL;
509 END;
510
511 BEGIN
512
513 -- populate jtf_type_qtype_usgs_all table with
514 -- qual_type_usg_id and terr_type_id
515 -- intersection table for jtf_qual_type_usgs_all and
516 -- jtf_terr_types_all tables
517
518 insert into jtf_type_qtype_usgs_all
519 ( TYPE_QTYPE_USG_ID
520 , LAST_UPDATED_BY
521 , LAST_UPDATE_DATE
522 , CREATED_BY
523 , CREATION_DATE
524 , last_update_login
525 , TERR_TYPE_ID
526 , QUAL_TYPE_USG_ID
527 , ORG_ID)
528 select jtf_type_qtype_usgs_s.nextval
529 , 2
530 , sysdate
531 , 2
532 , sysdate
533 , 2
534 , jttu.terr_type_id
535 , jqtu.qual_type_usg_id
536 , jttu.org_id
537 from jtf_terr_type_usgs_all jttu
538 , jtf_qual_type_usgs_all jqtu
539 where jqtu.source_id = jttu.source_id
540 and jqtu.qual_type_usg_id <> -1005 -- Exclude service account transaction type
541 and jttu.created_by = 2;
542
543 EXCEPTION
544 when FND_API.G_EXC_ERROR then
545 -- Add proper error logging
546 -- dbms_output.put_line('34' || sqlerrm );
547 NULL;
548 when FND_API.G_EXC_UNEXPECTED_ERROR then
549 -- Add proper error logging
550 -- dbms_output.put_line('35' || sqlerrm );
551 NULL;
552 when others then
553 -- Add proper error logging
554 -- dbms_output.put_line('36' || sqlerrm );
555 NULL;
556 END;
557
558 BEGIN
559
560 -- populate jtf_terr_type_qual_all table with
561 -- QUAL_USG_ID and terr_type_id
562 -- intersection table for jtf_qual_usgs_all and
563 -- jtf_terr_types_all tables
564 insert into jtf_terr_type_qual_all
565 (TERR_TYPE_QUAL_ID
566 , LAST_UPDATED_BY
567 , LAST_UPDATE_DATE
568 , CREATED_BY
569 , CREATION_DATE
570 , QUAL_USG_ID
571 , TERR_TYPE_ID
572 , ORG_ID)
573 select jtf_terr_type_qual_s.nextval
574 , 2
575 , sysdate
576 , 2
577 , sysdate
578 , jqu.QUAL_USG_ID
579 , jttu.terr_type_id
580 , jttu.org_id
581 from jtf_terr_type_usgs_all jttu
582 , jtf_qual_type_usgs_all jqtu
583 , jtf_qual_usgs_all jqu
584 where jttu.created_by = 2
585 and jqu.QUAL_TYPE_USG_ID = jqtu.qual_type_usg_id
586 and jqtu.source_id = jttu.source_id
587 and jttu.org_id = jqu.org_id
588 and jttu.terr_type_id <> -1 -- For NA territories this table is not populated
589 and jqu.ENABLED_FLAG = 'Y';
590
591 EXCEPTION
592 when FND_API.G_EXC_ERROR then
593 -- Add proper error logging
594 -- dbms_output.put_line('37' || sqlerrm );
595 NULL;
596 when FND_API.G_EXC_UNEXPECTED_ERROR then
597 -- Add proper error logging
598 -- dbms_output.put_line('38' || sqlerrm );
599 NULL;
600 when others then
601 -- Add proper error logging
602 -- dbms_output.put_line('39' || sqlerrm );
603 NULL;
604 END;
605
606 end if;
607
608 commit;
609
610 EXCEPTION
611 when FND_API.G_EXC_ERROR then
612 -- Add proper error logging
613 -- dbms_output.put_line('13' || sqlerrm );
614 NULL;
615 when FND_API.G_EXC_UNEXPECTED_ERROR then
616 -- Add proper error logging
617 -- dbms_output.put_line('14' || sqlerrm );
618 NULL;
619 when others then
620 -- Add proper error logging
621 -- dbms_output.put_line('15' || sqlerrm );
622 NULL;
623
624 END add_seeded_territory_types;
625
626 -- added 07/10/2006, bug 5193133
627 -- removed lead/opportunity expected purchase
628 Procedure remove_expected_purchase_qual
629 IS
630
631 l_count number := 0;
632 BEGIN
633
634 select count(*) into l_count
635 from jtf_terr_qual_all
636 where qual_usg_id in (-1023,-1018);
637
638 if (l_count = 0) then
639 delete from jtf_seeded_qual_all_b
640 where seeded_qual_id in (-1024, -1019);
641
642 delete from jtf_seeded_qual_all_tl
643 where seeded_qual_id in (-1024, -1019);
644
645 delete from jtf_qual_usgs_all
646 where qual_usg_id in (-1023,-1018);
647 end if;
648
649 commit;
650
651 EXCEPTION
652 when FND_API.G_EXC_ERROR then
653 -- Add proper error logging
654 -- dbms_output.put_line('1 ' || sqlerrm );
655 NULL;
656
657 when FND_API.G_EXC_UNEXPECTED_ERROR then
658 -- Add proper error logging
659 -- dbms_output.put_line('2 ' || sqlerrm );
660 NULL;
661 when others then
662 -- Add proper error logging
663 -- dbms_output.put_line('3 ' || sqlerrm );
664 NULL;
665 END remove_expected_purchase_qual;
666
667
668 -- Main procedure that will run all the
669 -- necessary procedures for R12 migration.
670
671 Procedure run_r12_migation_procedures
672 IS
673
674 BEGIN
675
676 -- dbms_output.put_line('Inside Migration Script' );
677
678 enable_existing_template;
679
680 delete_resource_trx_type;
681
682 --update_terr_enddate_active;
683
684 --update_self_service_flag;
685
686 add_seeded_territory_types;
687
688 -- added 07/10/2006, bug 5193133
689 remove_expected_purchase_qual;
690
691 --update_terr_type_for_terr;
692
693 -- update_rsc_access;
694
695 commit;
696
697 EXCEPTION
698 when FND_API.G_EXC_ERROR then
699 -- Add proper error logging
700 -- dbms_output.put_line('16' || sqlerrm );
701 NULL;
702 when FND_API.G_EXC_UNEXPECTED_ERROR then
703 -- Add proper error logging
704 -- dbms_output.put_line('17' || sqlerrm );
705 NULL;
706 when others then
707 -- Add proper error logging
708 -- dbms_output.put_line('18' || sqlerrm );
709 NULL;
710 END run_r12_migation_procedures;
711
712 END JTY_MIGRATION_PVT;