[Home] [Help]
PACKAGE BODY: APPS.PV_SLSTEAM_MIGRTN_PVT
Source
1 PACKAGE Body PV_SLSTEAM_MIGRTN_PVT AS
2 /* $Header: pvslmigb.pls 120.5 2005/09/22 11:22:04 vansub noship $ */
3
4
5 g_ret_code NUMBER := 0;
6 --=================================== Private Subroutines ================================
7 PROCEDURE printLog(p_message IN VARCHAR2);
8 PROCEDURE clean_log;
9
10 PROCEDURE printOutput(p_message IN VARCHAR2);
11 PROCEDURE printReport(p_mode IN VARCHAR2);
12 PROCEDURE delete_corrupt_partner;
13 PROCEDURE insert_cust_partner;
14 PROCEDURE insert_lead_partner;
15
16 PROCEDURE insert_opp_partner;
17 PROCEDURE insert_prefrd_partner;
18 PROCEDURE insert_saved_partners;
19 PROCEDURE insert_assigned_partners;
20 --=================================== Private Subroutines ================================
21 PROCEDURE EXT_SLSTEAM_MIGRTN
22 ( ERRBUF OUT NOCOPY VARCHAR2,
23 RETCODE OUT NOCOPY VARCHAR2,
24 P_MODE IN VARCHAR2
25 )
26 IS
27
28 l_api_name CONSTANT VARCHAR2(30) := 'EXT_SLSTEAM_MIGRTN';
29 l_api_version_number CONSTANT NUMBER := 1.0;
30
31 l_return_status VARCHAR2(1);
32
33 BEGIN
34 printlog('Batch Started at '|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
35
36 clean_log;
37 delete_corrupt_partner;
38 insert_cust_partner;
39 insert_lead_partner;
40
41 insert_opp_partner;
42 insert_prefrd_partner;
43 insert_saved_partners;
44 insert_assigned_partners;
45
46 printReport(p_mode);
47
48 IF p_mode = 'EVALUATE' THEN
49 ROLLBACK;
50 END IF;
51
52 printlog('Batch ended at '|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
53
54
55 RETCODE := 0;
56 ERRBUF := fnd_message.get;
57
58 EXCEPTION
59 WHEN OTHERS THEN
60 printOutput('Database Error:'||sqlcode||' '||sqlerrm);
61
62 IF g_ret_code > 0 THEN
63 RETCODE := g_ret_code;
64 ELSE
65 RETCODE := 0;
66 END IF;
67 ERRBUF := fnd_message.get;
68 END EXT_SLSTEAM_MIGRTN;
69
70 PROCEDURE delete_corrupt_partner
71 IS
72
73 BEGIN
74
75 /* Deleting records in as_accesses_all where partner_customer_id has partner's organization id */
76
77 INSERT INTO pv_access_migration_log
78 (
79 access_migration_log_id
80 ,access_id
81 ,action
82 ,creation_date
83 ,customer_id
84 ,address_id
85 ,salesforce_id
86 ,partner_customer_id
87 ,lead_id
88 ,org_id
89 ,open_flag
90 )
91 SELECT pv_access_migration_log_s.nextval access_migration_log_id,
92 z.access_id,
93 'DELTE_CORRUPT_PF_PARTNER',
94 sysdate,
95 z.customer_id,
96 z.address_id,
97 z.resource_id,
98 z.incumbent_partner_party_id,
99 z.lead_id,
100 z.org_id,
101 z.open_flag
102 FROM (SELECT DISTINCT d.access_id,
103 x.customer_id,
104 x.address_id,
105 x.resource_id,
106 x.incumbent_partner_party_id,
107 x.lead_id,
108 x.org_id,
109 d.open_flag
110 FROM (
111 SELECT distinct a.lead_id,
112 b.resource_id,
113 a.customer_id,
114 a.ADDRESS_ID,
115 a.org_id ,
116 a.incumbent_partner_party_id
117 FROM as_leads_all a, jtf_rs_resource_extns b
118 WHERE a.incumbent_partner_resource_id IS NOT NULL
119 AND b.source_id = a.incumbent_partner_party_id
120 MINUS
121 SELECT distinct a.lead_id,
122 c.resource_id ,
123 a.customer_id,
124 a.ADDRESS_ID,
125 a.org_id,
126 a.incumbent_partner_party_id
127 FROM as_leads_all a,
128 as_accesses_all b,
129 jtf_rs_resource_extns c
130 WHERE a.incumbent_partner_party_id = b.partner_customer_id
131 AND a.lead_id = b.lead_id
132 AND a.incumbent_partner_party_id is not null
133 AND c.source_id = a.incumbent_partner_party_id) x,
134 as_accesses_all d
135 WHERE x.lead_id = d.lead_id
136 AND d.salesforce_id = x.resource_id
137 AND x.customer_id = d.customer_id
138 AND x.address_id = d.address_id
139 AND x.org_id = d.org_id) z ;
140
141 -- ----------------------------------------------------------
142 -- Deleting the corrupted preferred partners
143 -- ----------------------------------------------------------
144 DELETE FROM as_accesses_all
145 WHERE access_id IN ( SELECT access_id
146 FROM pv_access_migration_log
147 WHERE action = 'DELTE_CORRUPT_PF_PARTNER');
148 /*
149 *
150 * Logging the delete activity of corrupted partners
151 */
152
153 INSERT INTO pv_access_migration_log
154 (
155 access_migration_log_id
156 ,access_id
157 ,action
158 ,creation_date
159 ,customer_id
160 ,address_id
161 ,salesforce_id
162 ,partner_customer_id
163 ,partner_address_id
164 ,lead_id
165 ,org_id
166 ,open_flag
167 )
168 SELECT pv_access_migration_log_s.nextval access_migration_log_id,
169 access_id,
170 'DELETE_CORRUPT_OPP_PARTNER',
171 sysdate,
172 customer_id,
173 address_id,
174 salesforce_id,
175 partner_customer_id,
176 partner_address_id,
177 lead_id,
178 org_id,
179 open_flag
180 FROM ( SELECT distinct access_id,
181 customer_id,
182 address_id,
183 salesforce_id,
184 partner_customer_id,
185 partner_address_id,
186 lead_id,
187 org_id,
188 open_flag
189 FROM as_accesses_all a ,
190 pv_partner_profiles pvp
191 WHERE a.partner_customer_id = pvp.partner_party_id
192 AND EXISTS ( SELECT partner_customer_id
193 FROM as_accesses_all acc,
194 hz_relationships b
195 WHERE sales_lead_id IS NULL
196 AND lead_id IS NOT NULL
197 AND a.customer_id = acc.customer_id
198 AND b.object_id = pvp.partner_party_id
199 AND b.party_id = acc.partner_cont_party_id
200 AND acc.partner_cont_party_id IS NOT NULL)
201 AND a.sales_lead_id IS NULL
202 AND a.lead_id IS NOT NULL
203 AND a.partner_cont_party_id IS NULL);
204
205
206 INSERT INTO pv_access_migration_log
207 (
208 access_migration_log_id
209 ,access_id
210 ,action
211 ,creation_date
212 ,customer_id
213 ,address_id
214 ,salesforce_id
215 ,partner_customer_id
216 ,partner_address_id
217 ,sales_lead_id
218 ,org_id
219 ,open_flag
220 )
221 SELECT pv_access_migration_log_s.nextval access_migration_log_id,
222 access_id,
223 'DELETE_CORRUPT_LEAD_PARTNER',
224 sysdate,
225 customer_id,
226 address_id,
227 salesforce_id,
228 partner_customer_id,
229 partner_address_id,
230 sales_lead_id,
231 org_id,
232 open_flag
233 FROM ( SELECT distinct access_id,
234 customer_id,
235 address_id,
236 salesforce_id,
237 partner_customer_id,
238 partner_address_id,
239 sales_lead_id,
240 org_id,
241 open_flag
242 FROM as_accesses_all a ,
243 pv_partner_profiles pvp
244 WHERE a.partner_customer_id = pvp.partner_party_id
245 AND EXISTS ( SELECT partner_customer_id
246 FROM as_accesses_all acc,
247 hz_relationships b
248 WHERE sales_lead_id IS NOT NULL
249 AND lead_id IS NULL
250 AND a.customer_id = acc.customer_id
251 AND b.object_id = pvp.partner_party_id
252 AND b.party_id = acc.partner_cont_party_id
253 AND acc.partner_cont_party_id IS NOT NULL)
254 AND a.lead_id IS NULL
255 AND a.sales_lead_id is not null
256 AND a.partner_cont_party_id IS NULL);
257
258
259 INSERT INTO pv_access_migration_log
260 (
261 access_migration_log_id
262 ,access_id
263 ,action
264 ,creation_date
265 ,customer_id
266 ,address_id
267 ,salesforce_id
268 ,partner_customer_id
269 ,partner_address_id
270 ,org_id
271 ,open_flag
272 )
273 SELECT pv_access_migration_log_s.nextval access_migration_log_id,
274 access_id,
275 'DELETE_CORRUPT_CUST_PARTNER',
276 sysdate,
277 customer_id,
278 address_id,
279 salesforce_id,
280 partner_customer_id,
281 partner_address_id,
282 org_id,
283 open_flag
284 FROM (SELECT distinct access_id,
285 customer_id,
286 address_id,
287 salesforce_id,
288 partner_customer_id,
289 partner_address_id,
290 org_id,
291 open_flag
292 FROM as_accesses_all a ,
293 pv_partner_profiles pvp
294 WHERE a.partner_customer_id = pvp.partner_party_id
295 AND EXISTS ( SELECT partner_customer_id
296 FROM as_accesses_all acc,
297 hz_relationships b
298 WHERE sales_lead_id IS NULL
299 AND lead_id IS NULL
300 AND a.customer_id = acc.customer_id
301 AND b.object_id = pvp.partner_party_id
302 AND b.party_id = acc.partner_cont_party_id
303 AND acc.partner_cont_party_id IS NOT NULL)
304 AND a.lead_id IS NULL
305 AND a.sales_lead_id is NULL
306 AND a.partner_cont_party_id IS NULL);
307
308 -- ----------------------------------------------------------
309 -- Deleting the corrupted partners
310 -- ----------------------------------------------------------
311 DELETE FROM as_accesses_all
312 WHERE access_id IN ( SELECT access_id
313 FROM pv_access_migration_log
314 WHERE action IN ('DELETE_CORRUPT_CUST_PARTNER','DELETE_CORRUPT_LEAD_PARTNER','DELETE_CORRUPT_OPP_PARTNER'));
315 EXCEPTION
316 WHEN OTHERS THEN
317 printOutput('Database Error in deleting from logs : '||sqlerrm);
318 g_ret_code := 2;
319 RAISE;
320 END delete_corrupt_partner;
321
322 PROCEDURE clean_log
323 IS
324
325 BEGIN
326 DELETE FROM pv_access_migration_log;
327 EXCEPTION
328 WHEN OTHERS THEN
329 printOutput('Database Error in deleting from logs : '||sqlerrm);
330 g_ret_code := 2;
331 RAISE;
332 END;
333
334
335
336
337
338 PROCEDURE insert_cust_partner
339 IS
340
341 BEGIN
342 BEGIN
343 INSERT INTO pv_access_migration_log
344 (
345 access_migration_log_id
346 ,access_id
347 ,action
348 ,creation_date
349 ,access_type
350 ,freeze_flag
351 ,reassign_flag
352 ,team_leader_flag
353 ,customer_id
354 ,address_id
355 ,salesforce_id
356 ,partner_customer_id
357 ,partner_address_id
358 ,lead_id
359 ,salesforce_role_code
360 ,org_id
361 ,sales_group_id
362 ,internal_update_access
363 ,sales_lead_id
364 ,partner_cont_party_id
365 ,owner_flag
366 ,created_by_tap_flag
367 ,prm_keep_flag
368 ,open_flag
369 )
370 SELECT pv_access_migration_log_s.nextval access_migration_log_id,
371 as_accesses_s.nextval access_id,
372 'INSERT_CUST_PARTY' action,
373 sysdate creation_date,
374 'X' access_type ,
375 'Y' freeze_flag,
376 'N' reassign_flag,
377 'Y' team_leader_flag,
378 customer_id,
379 address_id ,
380 resource_id salesforce_id,
381 partner_id partner_customer_id,
382 NULL partner_address_id,
383 NULL ,
384 NULL salesforce_role_code,
385 NVL(SUBSTRB(USERENV('CLIENT_INFO'),1,10),-99) ,
386 NULL salesgroup_id,
387 1 internal_update_access,
388 NULL sales_lead_id,
389 NULL parnter_cont_party_id,
390 'N' owner_flag,
391 'N' created_by_tap_flag,
392 'Y' prm_keep_flag ,
393 open_flag
394 FROM (
395 SELECT sales_lead_id,
396 open_flag,
397 partner_id,
398 x.customer_id,
399 x.address_id,
400 org_id,
401 resource_id
402 FROM (
403 SELECT distinct ACC.sales_lead_id,
404 acc.open_flag,
405 first_value(pvp.partner_id) over ( partition by ACC.lead_id, hz1.object_id order by pvp.status ASC,pvp.partner_id desc) partner_id,
406 acc.customer_id,
407 acc.address_id,
408 acc.org_id
409 FROM as_accesses_all ACC,
410 hz_relationships hz1,
411 pv_partner_profiles pvp
412 WHERE ACC.partner_cont_party_id IS NOT NULL
413 AND ACC.person_id IS NULL
414 AND ACC.lead_id is null
415 AND acc.sales_lead_id is null
416 AND not exists (SELECT NULL
417 FROM as_accesses_all acc2,
418 pv_partner_profiles PVPP,
419 hz_relationships hz
420 WHERE acc2.customer_id = acc.customer_id
421 AND acc2.partner_customer_id = PVPP.partner_id
422 AND hz.object_id = pvpp.partner_party_id
423 AND pvp.partner_party_id = pvpp.partner_party_id
424 AND acc.partner_cont_party_id = hz.party_id
425 AND ACC2.person_id IS NULL
426 AND ACC2.lead_id is null
427 AND acc2.sales_lead_id is null)
428 AND ACC.partner_cont_party_id = hz1.party_id
429 AND hz1.object_id = pvp.partner_party_id
430 ) x,
431 jtf_rs_resource_extns ext
432 WHERE x.partner_id = ext.source_id
433 AND ext.category = 'PARTNER');
434 printLog('No of Partners inserted into customer external sales team :'||SQL%ROWCOUNT ||'- insert into access');
435
436 EXCEPTION
437 WHEN OTHERS THEN
438 ROLLBACK;
439 printOutput('Database Error in insert partner logging to lead sales team : '||sqlerrm);
440 g_ret_code := 2;
441 RAISE;
442 END;
443
444 INSERT INTO as_accesses_all
445 ( access_id,
446 last_update_date,
447 last_updated_by,
448 creation_date,
449 created_by,
450 last_update_login,
451 access_type,
452 freeze_flag,
453 reassign_flag,
454 team_leader_flag,
455 customer_id,
456 address_id,
457 salesforce_id,
458 partner_customer_id,
459 partner_address_id,
460 lead_id,
461 salesforce_role_code,
462 org_id,
463 sales_group_id,
464 internal_update_access,
465 partner_cont_party_id,
466 owner_flag,
467 created_by_tap_flag,
468 prm_keep_flag,
469 open_flag,
470 object_version_number
471 )
472 SELECT access_id,
473 sysdate,
474 FND_GLOBAL.user_id,
475 sysdate,
476 FND_GLOBAL.user_id,
477 FND_GLOBAL.Conc_Login_Id,
478 access_type,
479 freeze_flag,
480 reassign_flag,
481 team_leader_flag,
482 customer_id,
483 address_id,
484 salesforce_id,
485 partner_customer_id,
486 partner_address_id,
487 lead_id,
488 salesforce_role_code,
489 org_id,
490 sales_group_id,
491 internal_update_access,
492 NULL parnter_cont_party_id,
493 owner_flag,
494 created_by_tap_flag,
495 prm_keep_flag,
496 open_flag,
500 EXCEPTION
497 NULL
498 FROM pv_access_migration_log
499 WHERE action = 'INSERT_CUST_PARTY';
501 WHEN OTHERS THEN
502 ROLLBACK;
503 printOutput('Database Error in inserting partners to customer external sales team '||sqlerrm);
504 g_ret_code := 2;
505 RAISE;
506 END insert_cust_partner;
507
508 PROCEDURE insert_lead_partner
509 IS
510
511 BEGIN
512 BEGIN
513 INSERT INTO pv_access_migration_log
514 (
515 access_migration_log_id
516 ,access_id
517 ,action
518 ,creation_date
519 ,access_type
520 ,freeze_flag
521 ,reassign_flag
522 ,team_leader_flag
523 ,customer_id
524 ,address_id
525 ,salesforce_id
526 ,partner_customer_id
527 ,partner_address_id
528 ,lead_id
529 ,salesforce_role_code
530 ,org_id
531 ,sales_group_id
532 ,internal_update_access
533 ,sales_lead_id
534 ,partner_cont_party_id
535 ,owner_flag
536 ,created_by_tap_flag
537 ,prm_keep_flag
538 ,open_flag
539 )
540 SELECT pv_access_migration_log_s.nextval access_migration_log_id,
541 as_accesses_s.nextval access_id,
542 'INSERT_LEAD_PARTY' action,
543 sysdate creation_date,
544 'X' access_type ,
545 'Y' freeze_flag,
546 'N' reassign_flag,
547 'Y' team_leader_flag,
548 customer_id,
549 address_id ,
550 resource_id salesforce_id,
551 partner_id partner_customer_id,
552 NULL partner_address_id,
553 NULL ,
554 NULL salesforce_role_code,
555 org_id ,
556 NULL salesgroup_id,
557 1 internal_update_access,
558 sales_lead_id sales_lead_id,
559 NULL parnter_cont_party_id,
560 'N' owner_flag,
561 'N' created_by_tap_flag,
562 'Y' prm_keep_flag ,
563 open_flag
564 FROM (
565 SELECT sales_lead_id,
566 open_flag,
567 partner_id,
568 x.customer_id,
569 x.address_id,
570 org_id,
571 resource_id
572 FROM ( SELECT distinct ACC.sales_lead_id,
573 acc.open_flag,
574 FIRST_VALUE(pvp.partner_id) OVER ( PARTITION BY ACC.lead_id, hz1.object_id ORDER BY pvp.status ASC, pvp.partner_id DESC) partner_id,
575 asl.customer_id,
576 asl.address_id,
577 acc.org_id
578 FROM as_accesses_all ACC,
579 hz_relationships hz1,
580 pv_partner_profiles pvp,
581 as_sales_leads asl
582 WHERE ACC.sales_lead_id is not null
583 AND ACC.partner_cont_party_id is not null
584 AND ACC.person_id is null
585 AND ASL.sales_lead_id = ACC.sales_lead_id
586 AND not exists (SELECT NULL
587 FROM as_accesses_all ACC2,
588 pv_partner_profiles PVPP,
589 hz_relationships HZ
590 WHERE ACC2.sales_lead_id = ACC.sales_lead_id
591 AND ACC2.partner_customer_id = PVPP.partner_id
592 AND HZ.object_id = PVPP.partner_party_id
593 AND PVP.partner_party_id = PVPP.partner_party_id
594 AND ACC.partner_cont_party_id = HZ.party_id
595 AND ACC2.person_id IS NULL
596 AND ACC2.lead_id is null
597 AND acc2.sales_lead_id is not null)
598 AND ACC.partner_cont_party_id = hz1.party_id
599 AND hz1.object_id = pvp.partner_party_id
600 ) x,
601 jtf_rs_resource_extns ext
602 WHERE x.partner_id = ext.source_id
603 AND ext.category = 'PARTNER');
604
605 printLog('No of Partners inserted into lead external sales team :'||SQL%ROWCOUNT ||'- insert into access');
606
607 EXCEPTION
608 WHEN OTHERS THEN
609 ROLLBACK;
610 printOutput('Database Error in insert partner logging to lead sales team : '||sqlerrm);
611 g_ret_code := 2;
612 RAISE;
613 END;
614
615 INSERT INTO as_accesses_all
616 ( access_id,
617 last_update_date,
618 last_updated_by,
619 creation_date,
620 created_by,
621 last_update_login,
622 access_type,
623 freeze_flag,
624 reassign_flag,
625 team_leader_flag,
626 customer_id,
627 address_id,
628 salesforce_id,
629 partner_customer_id,
633 org_id,
630 partner_address_id,
631 sales_lead_id,
632 salesforce_role_code,
634 sales_group_id,
635 internal_update_access,
636 partner_cont_party_id,
637 owner_flag,
638 created_by_tap_flag,
639 prm_keep_flag,
640 open_flag,
641 object_version_number
642 )
643 SELECT access_id,
644 sysdate,
645 FND_GLOBAL.user_id,
646 sysdate,
647 FND_GLOBAL.user_id,
648 FND_GLOBAL.Conc_Login_Id,
649 access_type,
650 freeze_flag,
651 reassign_flag,
652 team_leader_flag,
653 customer_id,
654 address_id,
655 salesforce_id,
656 partner_customer_id,
657 partner_address_id,
658 sales_lead_id,
659 salesforce_role_code,
660 org_id,
661 sales_group_id,
662 internal_update_access,
663 NULL,
664 owner_flag,
665 created_by_tap_flag,
666 prm_keep_flag,
667 open_flag,
668 NULL
669 FROM pv_access_migration_log
670 WHERE action = 'INSERT_LEAD_PARTY';
671 EXCEPTION
672 WHEN OTHERS THEN
673 ROLLBACK;
674 printOutput('Database Error in inserting partners to lead external sales team '||sqlerrm);
675 g_ret_code := 2;
676 RAISE;
677 END insert_lead_partner;
678
679 -- --
680 -- Inserting partners into opportunity External Sales team
681 -- when routing status of opportunity is active and
682 -- there are no partners associated with contacts in the sales team
683 -- --
684
685 PROCEDURE insert_opp_partner
686 IS
687
688 BEGIN
689 BEGIN
690 INSERT INTO pv_access_migration_log
691 (
692 access_migration_log_id
693 ,access_id
694 ,action
695 ,creation_date
696 ,access_type
697 ,freeze_flag
698 ,reassign_flag
699 ,team_leader_flag
700 ,customer_id
701 ,address_id
702 ,salesforce_id
703 ,partner_customer_id
704 ,partner_address_id
705 ,lead_id
706 ,salesforce_role_code
707 ,org_id
708 ,sales_group_id
709 ,internal_update_access
710 ,sales_lead_id
711 ,partner_cont_party_id
712 ,owner_flag
713 ,created_by_tap_flag
714 ,prm_keep_flag
715 ,open_flag
716 )
717 SELECT pv_access_migration_log_s.nextval access_migration_log_id,
718 as_accesses_s.nextval access_id,
719 'INSERT_OPP_PARTY' action,
720 sysdate creation_date,
721 'X' access_type ,
722 'Y' freeze_flag,
723 'N' reassign_flag,
724 'Y' team_leader_flag,
725 customer_id,
726 address_id ,
727 resource_id salesforce_id,
728 partner_id partner_customer_id,
729 NULL partner_address_id,
730 lead_id ,
731 NULL salesforce_role_code,
732 org_id ,
733 NULL salesgroup_id,
734 1 internal_update_access,
735 NULL sales_lead_id,
736 NULL parnter_cont_party_id,
737 'N' owner_flag,
738 'N' created_by_tap_flag,
739 'Y' prm_keep_flag ,
740 open_flag
741 FROM (
742 SELECT lead_id,
743 open_flag,
744 partner_id,
745 x.customer_id,
746 x.address_id,
747 org_id,
748 resource_id
749 FROM ( SELECT distinct ACC.lead_id,
750 acc.open_flag,
751 CASE WHEN ass.partner_id is null
752 THEN FIRST_VALUE(pvp.partner_id) OVER ( PARTITION BY ACC.lead_id, hz1.object_id ORDER BY pvp.status ASC, pvp.partner_id DESC)
753 WHEN ass.partner_id is not null
754 and ass.status in ('LOST_CHANCE','PT_REJECTED','PT_TIMEOUT','OFFER_WITHDRAWN','MATCH_WITHDRAWN','ACTIVE_WITHDRAWN')
755 THEN NULL
756 ELSE ass.partner_id
757 END partner_id,
758 asl.customer_id,
759 asl.address_id,
760 asl.org_id
761 FROM as_accesses_all ACC,
762 hz_relationships hz1,
763 pv_partner_profiles pvp,
764 as_leads_all asl,
765 pv_lead_assignments ass,
766 pv_lead_workflows pvw
767 WHERE ACC.lead_id is not null
768 AND ACC.partner_cont_party_id is not null
769 AND ACC.person_id is null
770 AND asl.lead_id = acc.lead_id
771 AND not exists (SELECT NULL
772 FROM as_accesses_all acc2,
773 pv_partner_profiles PVPP,
774 hz_relationships hz
778 AND pvp.partner_party_id = pvpp.partner_party_id
775 WHERE acc2.lead_id = acc.lead_id
776 AND acc2.partner_customer_id = PVPP.partner_id
777 AND hz.object_id = pvpp.partner_party_id
779 AND acc.partner_cont_party_id = hz.party_id
780 AND acc2.person_id IS NULL
781 AND acc2.lead_id is not null
782 AND acc2.sales_lead_id is null)
783 AND not exists ( SELECT NULL
784 FROM as_accesses_all acc3
785 WHERE acc3.partner_customer_id = ass.partner_id
786 AND acc3.lead_id = ass.lead_id)
787 AND ACC.partner_cont_party_id = hz1.party_id
788 AND hz1.object_id = pvp.partner_party_id
789 AND asl.lead_id = ass.lead_id(+)
790 AND asl.lead_id = pvw.lead_ID(+)
791 AND pvw.latest_routing_flag(+) = 'Y') x,
792 jtf_rs_resource_extns ext
793 WHERE x.partner_id = ext.source_id
794 AND ext.category = 'PARTNER'
795 AND x.partner_id is not null);
796
797 printLog('No of Partners inserted into active routed opportunity''s external sales team :'||SQL%ROWCOUNT ||'- insert into access');
798
799 EXCEPTION
800 WHEN OTHERS THEN
801 ROLLBACK;
802 printOutput('Database Error in insert partner logging to opportunity sales team : '||sqlerrm);
803 g_ret_code := 2;
804 RAISE;
805 END;
806
807 INSERT INTO as_accesses_all
808 ( access_id,
809 last_update_date,
810 last_updated_by,
811 creation_date,
812 created_by,
813 last_update_login,
814 access_type,
815 freeze_flag,
816 reassign_flag,
817 team_leader_flag,
818 customer_id,
819 address_id,
820 salesforce_id,
821 partner_customer_id,
822 partner_address_id,
823 lead_id,
824 salesforce_role_code,
825 org_id,
826 sales_group_id,
827 internal_update_access,
828 partner_cont_party_id,
829 owner_flag,
830 created_by_tap_flag,
831 prm_keep_flag,
832 open_flag,
833 object_version_number
834 )
835 SELECT access_id,
836 sysdate,
837 FND_GLOBAL.user_id,
838 sysdate,
839 FND_GLOBAL.user_id,
840 FND_GLOBAL.Conc_Login_Id,
841 access_type,
842 freeze_flag,
843 reassign_flag,
844 team_leader_flag,
845 customer_id,
846 address_id,
847 salesforce_id,
848 partner_customer_id,
849 partner_address_id,
850 lead_id,
851 salesforce_role_code,
852 org_id,
853 sales_group_id,
854 internal_update_access,
855 partner_cont_party_id,
856 owner_flag,
857 created_by_tap_flag,
858 prm_keep_flag,
859 open_flag,
860 NULL
861 FROM pv_access_migration_log
862 WHERE action = 'INSERT_OPP_PARTY';
863 EXCEPTION
864 WHEN OTHERS THEN
865 ROLLBACK;
866 printOutput('Database Error in inserting partners to opportunity external sales team '||sqlerrm);
867 g_ret_code := 2;
868 RAISE;
869 END insert_opp_partner;
870
871 -- --
872 -- Inserting preferred partners from as_leads_all into opportunity External Sales team
873 -- --
874
875 PROCEDURE insert_prefrd_partner
876 IS
877 l_lead_id number;
878 BEGIN
879 BEGIN
880 INSERT INTO pv_access_migration_log
881 (
882 access_migration_log_id
883 ,access_id
884 ,action
885 ,creation_date
886 ,access_type
887 ,freeze_flag
888 ,reassign_flag
889 ,team_leader_flag
890 ,customer_id
891 ,address_id
892 ,salesforce_id
893 ,partner_customer_id
894 ,partner_address_id
895 ,lead_id
896 ,salesforce_role_code
897 ,org_id
898 ,sales_group_id
899 ,internal_update_access
900 ,sales_lead_id
901 ,partner_cont_party_id
902 ,owner_flag
903 ,created_by_tap_flag
904 ,prm_keep_flag
905 ,open_flag
906 )
907 SELECT pv_access_migration_log_s.nextval access_migration_log_id,
908 as_accesses_s.nextval access_id,
909 'INSERT_OPP_PRFRD_PT' action,
910 sysdate creation_date,
911 'X' access_type ,
912 'Y' freeze_flag,
913 'N' reassign_flag,
914 'Y' team_leader_flag,
915 x.customer_id,
916 x.address_id ,
917 x.resource_id salesforce_id,
918 x.incumbent_partner_party_id partner_id,
919 NULL partner_address_id,
920 x.lead_id ,
921 NULL salesforce_role_code,
922 x.org_id ,
923 NULL salesgroup_id,
927 'N' owner_flag,
924 1 internal_update_access,
925 NULL sales_lead_id,
926 NULL parnter_cont_party_id,
928 'N' created_by_tap_flag,
929 'Y' prm_keep_flag ,
930 NULL
931 FROM (
932 SELECT distinct a.lead_id,
933 incumbent_partner_party_id,
934 c.resource_id ,
935 a.customer_id ,
936 a.address_id ,
937 a.org_id
938 FROM as_leads_all a,
939 jtf_rs_resource_extns c
940 WHERE a.incumbent_partner_party_id is not null
941 AND c.category = 'PARTNER'
942 AND c.source_id = a.incumbent_partner_party_id
943 MINUS
944 SELECT distinct a.lead_id,
945 incumbent_partner_party_id,
946 c.resource_id ,
947 a.customer_id ,
948 a.address_id ,
949 a.org_id
950 FROM as_leads_all a,
951 as_accesses_all b,
952 jtf_rs_resource_extns c
953 WHERE a.incumbent_partner_party_id = b.partner_customer_id
954 AND a.lead_id = b.lead_id
955 AND a.incumbent_partner_party_id is not null
956 AND c.category = 'PARTNER'
957 AND c.source_id = a.incumbent_partner_party_id ) x
958 WHERE NOT EXISTS ( SELECT NULL FROM as_accesses_all
959 where partner_customer_id = x.incumbent_partner_party_id
960 and lead_id = x.lead_id
961 and salesforce_id = x.resource_id);
962 EXCEPTION
963 WHEN OTHERS THEN
964 ROLLBACK;
965 printOutput('Database Error in insert preferred partner logging to opportunity sales team : '||sqlerrm);
966 g_ret_code := 2;
967 RAISE;
968 END;
969
970 INSERT INTO as_accesses_all
971 ( access_id,
972 last_update_date,
973 last_updated_by,
974 creation_date,
975 created_by,
976 last_update_login,
977 access_type,
978 freeze_flag,
979 reassign_flag,
980 team_leader_flag,
981 customer_id,
982 address_id,
983 salesforce_id,
984 partner_customer_id,
985 partner_address_id,
986 lead_id,
987 salesforce_role_code,
988 org_id,
989 sales_group_id,
990 internal_update_access,
991 partner_cont_party_id,
992 owner_flag,
993 created_by_tap_flag,
994 prm_keep_flag,
995 open_flag,
996 object_version_number
997 )
998 SELECT access_id,
999 sysdate,
1000 FND_GLOBAL.user_id,
1001 sysdate,
1002 FND_GLOBAL.user_id,
1003 FND_GLOBAL.Conc_Login_Id,
1004 access_type,
1005 freeze_flag,
1006 reassign_flag,
1007 team_leader_flag,
1008 customer_id,
1009 address_id,
1010 salesforce_id,
1011 partner_customer_id,
1012 partner_address_id,
1013 lead_id,
1014 salesforce_role_code,
1015 org_id,
1016 sales_group_id,
1017 internal_update_access,
1018 partner_cont_party_id,
1019 owner_flag,
1020 created_by_tap_flag,
1021 prm_keep_flag,
1022 open_flag,
1023 NULL
1024 FROM pv_access_migration_log
1025 WHERE action = 'INSERT_OPP_PRFRD_PT';
1026 EXCEPTION
1027 WHEN OTHERS THEN
1028 ROLLBACK;
1029 printOutput('Database Error in insert preferred partner logging to opportunity sales team : '||sqlerrm);
1030 g_ret_code := 2;
1031 RAISE;
1032 END insert_prefrd_partner;
1033 -- --
1034 -- Inserting matched partners into opportunity External Sales team
1035 -- --
1036 PROCEDURE insert_saved_partners
1037 IS
1038 BEGIN
1039
1040 BEGIN
1041 INSERT INTO pv_access_migration_log
1042 (
1043 access_migration_log_id
1044 ,access_id
1045 ,action
1046 ,creation_date
1047 ,access_type
1048 ,freeze_flag
1049 ,reassign_flag
1050 ,team_leader_flag
1051 ,customer_id
1052 ,address_id
1053 ,salesforce_id
1054 ,partner_customer_id
1055 ,partner_address_id
1056 ,lead_id
1057 ,salesforce_role_code
1058 ,org_id
1059 ,sales_group_id
1060 ,internal_update_access
1061 ,sales_lead_id
1062 ,partner_cont_party_id
1063 ,owner_flag
1064 ,created_by_tap_flag
1065 ,prm_keep_flag
1066 ,open_flag
1067 )
1068 SELECT pv_access_migration_log_s.nextval access_migration_log_id,
1069 as_accesses_s.nextval access_id,
1070 'INSERT_OPP_SAVED_PT' action,
1071 sysdate creation_date,
1072 'X' access_type ,
1073 'Y' freeze_flag,
1074 'N' reassign_flag,
1075 'Y' team_leader_flag,
1076 z.customer_id,
1077 z.address_id ,
1078 z.resource_id salesforce_id,
1079 z.partner_id,
1080 NULL partner_address_id,
1081 z.lead_id ,
1082 NULL salesforce_role_code,
1083 z.org_id ,
1087 NULL parnter_cont_party_id,
1084 NULL salesgroup_id,
1085 1 internal_update_access,
1086 NULL sales_lead_id,
1088 'N' owner_flag,
1089 'N' created_by_tap_flag,
1090 'Y' prm_keep_flag ,
1091 NULL
1092 FROM (SELECT distinct x.customer_id,
1093 x.address_id,
1094 y.resource_id,
1095 c.partner_id,
1096 x.lead_id,
1097 x.org_id
1098 FROM pv_lead_assignments c,
1099 as_leads_all x,
1100 jtf_rs_resource_extns y
1101 WHERE wf_item_type IS NULL
1102 AND NOT EXISTS ( SELECT NULL
1103 FROM pv_lead_assignments a, as_accesses_all b
1104 WHERE a.partner_id = b.partner_customer_id
1105 AND a.lead_id = b.lead_id
1106 AND c.lead_id = a.lead_id
1107 AND c.partner_id = b.partner_customer_id
1108 AND salesforce_id = y.resource_id)
1109 AND x.lead_id = c.lead_id
1110 AND c.partner_id = y.source_id
1111 AND y.category = 'PARTNER' ) z;
1112
1113 EXCEPTION
1114 WHEN OTHERS THEN
1115 ROLLBACK;
1116
1117 printOutput('Database Error in insert saved partner logging to opportunity sales team : '||sqlerrm);
1118 g_ret_code := 2;
1119 RAISE;
1120 END;
1121
1122 INSERT INTO as_accesses_all
1123 ( access_id,
1124 last_update_date,
1125 last_updated_by,
1126 creation_date,
1127 created_by,
1128 last_update_login,
1129 access_type,
1130 freeze_flag,
1131 reassign_flag,
1132 team_leader_flag,
1133 customer_id,
1134 address_id,
1135 salesforce_id,
1136 partner_customer_id,
1137 partner_address_id,
1138 lead_id,
1139 salesforce_role_code,
1140 org_id,
1141 sales_group_id,
1142 internal_update_access,
1143 partner_cont_party_id,
1144 owner_flag,
1145 created_by_tap_flag,
1146 prm_keep_flag,
1147 open_flag,
1148 object_version_number
1149 )
1150 SELECT access_id,
1151 sysdate,
1152 FND_GLOBAL.user_id,
1153 sysdate,
1154 FND_GLOBAL.user_id,
1155 FND_GLOBAL.Conc_Login_Id,
1156 access_type,
1157 freeze_flag,
1158 reassign_flag,
1159 team_leader_flag,
1160 customer_id,
1161 address_id,
1162 salesforce_id,
1163 partner_customer_id,
1164 partner_address_id,
1165 lead_id,
1166 salesforce_role_code,
1167 org_id,
1168 sales_group_id,
1169 internal_update_access,
1170 partner_cont_party_id,
1171 owner_flag,
1172 created_by_tap_flag,
1173 prm_keep_flag,
1174 open_flag,
1175 NULL
1176 FROM pv_access_migration_log
1177 WHERE action = 'INSERT_OPP_SAVED_PT';
1178 EXCEPTION
1179 WHEN OTHERS THEN
1180 ROLLBACK;
1181 printOutput('Database Error in insert saved partner to opportunity sales team : '||sqlcode||' : '||sqlerrm);
1182 g_ret_code := 2;
1183 RAISE;
1184 END insert_saved_partners;
1185
1186 PROCEDURE insert_assigned_partners
1187 IS
1188 BEGIN
1189
1190 BEGIN
1191 INSERT INTO pv_access_migration_log
1192 (
1193 access_migration_log_id
1194 ,access_id
1195 ,action
1196 ,creation_date
1197 ,access_type
1198 ,freeze_flag
1199 ,reassign_flag
1200 ,team_leader_flag
1201 ,customer_id
1202 ,address_id
1203 ,salesforce_id
1204 ,partner_customer_id
1205 ,partner_address_id
1206 ,lead_id
1207 ,salesforce_role_code
1208 ,org_id
1209 ,sales_group_id
1210 ,internal_update_access
1211 ,sales_lead_id
1212 ,partner_cont_party_id
1213 ,owner_flag
1214 ,created_by_tap_flag
1215 ,prm_keep_flag
1216 ,open_flag
1217 )
1218 SELECT pv_access_migration_log_s.nextval access_migration_log_id,
1219 as_accesses_s.nextval access_id,
1220 'INSERT_OPP_ASSIGNED_PT' action,
1221 sysdate creation_date,
1222 'X' access_type ,
1223 'Y' freeze_flag,
1224 'N' reassign_flag,
1225 'Y' team_leader_flag,
1226 z.customer_id,
1227 z.address_id ,
1228 z.resource_id salesforce_id,
1229 z.partner_id,
1230 NULL partner_address_id,
1231 z.lead_id ,
1232 NULL salesforce_role_code,
1233 z.org_id ,
1234 NULL salesgroup_id,
1235 1 internal_update_access,
1236 NULL sales_lead_id,
1237 NULL parnter_cont_party_id,
1238 'N' owner_flag,
1239 'N' created_by_tap_flag,
1240 'Y' prm_keep_flag ,
1241 NULL
1242 FROM (SELECT distinct x.customer_id,
1243 x.address_id,
1247 x.org_id
1244 y.resource_id,
1245 c.partner_id,
1246 x.lead_id,
1248 FROM pv_lead_assignments c,
1249 as_leads_all x,
1250 jtf_rs_resource_extns y
1251 WHERE c.status = 'ASSIGNED'
1252 AND NOT EXISTS ( SELECT NULL
1253 FROM as_accesses_all b
1254 WHERE c.lead_id = b.lead_id
1255 AND c.partner_id = b.partner_customer_id
1256 AND b.salesforce_id = y.resource_id)
1257 AND x.lead_id = c.lead_id
1258 AND c.partner_id = y.source_id
1259 AND y.category = 'PARTNER' ) z;
1260
1261 EXCEPTION
1262 WHEN OTHERS THEN
1263 ROLLBACK;
1264 printOutput('Database Error in insert saved partner logging to opportunity sales team : '||sqlerrm);
1265 g_ret_code := 2;
1266 RAISE;
1267 END;
1268
1269 INSERT INTO as_accesses_all
1270 ( access_id,
1271 last_update_date,
1272 last_updated_by,
1273 creation_date,
1274 created_by,
1275 last_update_login,
1276 access_type,
1277 freeze_flag,
1278 reassign_flag,
1279 team_leader_flag,
1280 customer_id,
1281 address_id,
1282 salesforce_id,
1283 partner_customer_id,
1284 partner_address_id,
1285 lead_id,
1286 salesforce_role_code,
1287 org_id,
1288 sales_group_id,
1289 internal_update_access,
1290 partner_cont_party_id,
1291 owner_flag,
1292 created_by_tap_flag,
1293 prm_keep_flag,
1294 open_flag,
1295 object_version_number
1296 )
1297 SELECT access_id,
1298 sysdate,
1299 FND_GLOBAL.user_id,
1300 sysdate,
1301 FND_GLOBAL.user_id,
1302 FND_GLOBAL.Conc_Login_Id,
1303 access_type,
1304 freeze_flag,
1305 reassign_flag,
1306 team_leader_flag,
1307 customer_id,
1308 address_id,
1309 salesforce_id,
1310 partner_customer_id,
1311 partner_address_id,
1312 lead_id,
1313 salesforce_role_code,
1314 org_id,
1315 sales_group_id,
1316 internal_update_access,
1317 partner_cont_party_id,
1318 owner_flag,
1319 created_by_tap_flag,
1320 prm_keep_flag,
1321 open_flag,
1322 NULL
1323 FROM pv_access_migration_log
1324 WHERE action = 'INSERT_OPP_ASSIGNED_PT';
1325 EXCEPTION
1326 WHEN OTHERS THEN
1327 ROLLBACK;
1328 printOutput('Database Error in insert assigned partner to opportunity sales team : '||sqlcode||' : '||sqlerrm);
1329 g_ret_code := 2;
1330 RAISE;
1331 END insert_assigned_partners;
1332
1333
1334 PROCEDURE printReport(p_mode IN VARCHAR2)
1335 IS
1336
1337 l_temp_msg VARCHAR2(2000);
1338 l_count NUMBER := 0;
1339 i NUMBER := 0;
1340 l_title VARCHAR2(200);
1341 l_migration_tbl migration_tbl_TYPE;
1342
1343
1344
1345 begin
1346 fnd_message.set_name('PV','PV_ACCESS_MIG_DESC');
1347 l_temp_msg := fnd_message.get;
1348 printOutput(RPAD('=',120,'='));
1349 printOutput(RPAD(' ',(120-length(l_temp_msg))/2,' ')||l_temp_msg);
1350 printOutput(RPAD('=',120,'='));
1351 printOutput(RPAD(' ',120,' '));
1352 -- Running Mode
1353
1354 FOR x IN (
1355 SELECT decode(lookup_code,'EVALUATE','Evaluation',
1356 'Execution') meaning
1357 FROM fnd_lookup_values
1358 WHERE lookup_type = 'PV_MIGRATION_RUN_MODE'
1359 AND lookup_code = p_mode)
1360 LOOP
1361 fnd_message.set_name('PV','PV_CONCURRENT_MODE');
1362 fnd_message.set_token('P_MODE',x.meaning);
1363 END LOOP;
1364
1365
1366 l_temp_msg := fnd_message.get ;
1367 printOutput(RPAD(' ',(120-length(l_temp_msg))/2,' ')||l_temp_msg);
1368 printOutput(RPAD(' ',120,' '));
1369 printOutput(RPAD(' ',120,' '));
1370 printOutput(RPAD(' ',120,' '));
1371 BEGIN
1372 SELECT count(action)
1373 INTO l_count
1374 FROM pv_access_migration_log
1375 WHERE action = 'DELTE_CORRUPT_PF_PARTNER';
1376
1377 fnd_message.set_name('PV','PV_DEL_CRPT_PF_PT');
1378 l_temp_msg := fnd_message.get ||' '||l_count;
1379
1380 printOutput(RPAD('',(120-length(l_temp_msg))/2,' ')||l_temp_msg);
1381 printOutput(RPAD('-',120,'-'));
1382 IF l_count > 0 THEN
1383 FOR x in
1384 ( select distinct asl.description entity,
1385 asl.lead_number entity_id,
1386 b.party_name ,
1387 a.salesforce_id
1388 from pv_access_migration_log a,
1389 hz_parties b,
1390 pv_partner_profiles pvpp ,
1391 as_leads_all asl
1392 where a.partner_customer_id = pvpp.partner_id
1393 and pvpp.partner_party_id = b.party_id
1394 and asl.lead_id = a.lead_id
1395 and a.action = 'DELTE_CORRUPT_PF_PARTNER' )
1396 loop
1397 i := i + 1;
1398 l_migration_tbl(i).entity := x.entity;
1399 l_migration_tbl(i).entity_id := x.entity_id;
1400 l_migration_tbl(i).party_name := x.party_name;
1404 l_title := RPAD(fnd_message.get,50,' ');
1401 l_migration_tbl(i).resource_id := x.salesforce_id;
1402 end loop;
1403 fnd_message.set_name('PV','PV_OPP_NAME_ATTR');
1405 fnd_message.set_name('PV','PV_OPP_NUMBER_ATTR');
1406 l_title := l_title || RPAD(fnd_message.get,18,' ');
1407 fnd_message.set_name('PV','PV_PARTNER_NAME');
1408 l_title := l_title || RPAD(fnd_message.get,40,' ');
1409 fnd_message.set_name('PV','PV_RESOURCE_ID_ATTR');
1410 l_title := l_title || RPAD(fnd_message.get,13,' ');
1411
1412 printOutput ( l_title);
1413 printOutput(RPAD('-',120,'-'));
1414
1415 FOR j in 1 .. l_migration_tbl.count
1416 LOOP
1417 printOutput( rpad(l_migration_tbl(j).entity,48,' ')||' '||rpad(l_migration_tbl(j).entity_id,16,' ')||' '||rpad(l_migration_tbl(j).party_name,38,' ')||' '||rpad(l_migration_tbl(j).resource_id,11,' '));
1418 END LOOP;
1419 printOutput(RPAD('=',120,'='));
1420 l_migration_tbl.delete;
1421 l_count := 0;
1422 i := 0;
1423 END IF;
1424 EXCEPTION
1425 WHEN OTHERS THEN
1426 printOutput('Database Error in generating report for deletion of preferred partners : '||sqlerrm);
1427 END;
1428 printOutput(RPAD(' ',120,' '));
1429 printOutput(RPAD(' ',120,' '));
1430 printOutput(RPAD(' ',120,' '));
1431 BEGIN
1432 SELECT count(action)
1433 INTO l_count
1434 FROM pv_access_migration_log
1435 WHERE action = 'DELETE_CORRUPT_CUST_PARTNER';
1436
1437 fnd_message.set_name('PV','PV_DEL_CUR_CUST_ESLSTM');
1438 l_temp_msg := fnd_message.get ||' '||l_count;
1439
1440 printOutput(RPAD('',(120-length(l_temp_msg))/2,' ')||l_temp_msg);
1441 printOutput(RPAD('-',120,'-'));
1442 IF l_count > 0 THEN
1443 FOR x in
1444 ( select d.party_name entity, d.party_number entity_id,
1445 b.party_name , a.salesforce_id
1446 from pv_access_migration_log a,
1447 hz_parties b,
1448 pv_partner_profiles pvpp ,
1449 hz_parties d
1450 where a.partner_customer_id = pvpp.partner_party_id
1451 and pvpp.partner_party_id = b.party_id
1452 and a.customer_id = d.party_id
1453 and a.action = 'DELETE_CORRUPT_CUST_PARTNER' )
1454 loop
1455 i := i + 1;
1456 l_migration_tbl(i).entity := x.entity;
1457 l_migration_tbl(i).entity_id := x.entity_id;
1458 l_migration_tbl(i).party_name := x.party_name;
1459 l_migration_tbl(i).resource_id := x.salesforce_id;
1460 end loop;
1461 fnd_message.set_name('PV','PV_CUSTOMER_NAME_ATTR');
1462 l_title := RPAD(fnd_message.get,50,' ');
1463 fnd_message.set_name('PV','PV_CUSTOMER_ID_ATTR');
1464 l_title := l_title || RPAD(fnd_message.get,18,' ');
1465 fnd_message.set_name('PV','PV_PARTNER_NAME');
1466 l_title := l_title || RPAD(fnd_message.get,40,' ');
1467 fnd_message.set_name('PV','PV_RESOURCE_ID_ATTR');
1468 l_title := l_title || RPAD(fnd_message.get,13,' ');
1469
1470 printOutput ( l_title);
1471 printOutput(RPAD('-',120,'-'));
1472
1473 FOR j in 1 .. l_migration_tbl.count
1474 LOOP
1475 printOutput( rpad(l_migration_tbl(j).entity,48,' ')||' '||rpad(l_migration_tbl(j).entity_id,16,' ')||' '||rpad(l_migration_tbl(j).party_name,38,' ')||' '||rpad(l_migration_tbl(j).resource_id,11,' '));
1476 END LOOP;
1477 printOutput(RPAD('=',120,'='));
1478
1479 l_migration_tbl.delete;
1480 l_count := 0;
1481 i := 0;
1482 END IF;
1483 EXCEPTION
1484 WHEN OTHERS THEN
1485 printOutput('Database Error in generating report for customer external sales team partners : '||sqlerrm);
1486 END;
1487 printOutput(RPAD(' ',120,' '));
1488 printOutput(RPAD(' ',120,' '));
1489 printOutput(RPAD(' ',120,' '));
1490 BEGIN
1491
1492 SELECT count(action)
1493 INTO l_count
1494 FROM pv_access_migration_log
1495 WHERE action = 'DELETE_CORRUPT_LEAD_PARTNER';
1496
1497 fnd_message.set_name('PV','PV_DEL_PTR_LEAD_ESLSTM');
1498 l_temp_msg := fnd_message.get ||' '||l_count;
1499
1500 printOutput(RPAD('',(120-length(l_temp_msg))/2,' ')||l_temp_msg);
1501 printOutput(RPAD('-',120,'-'));
1502 IF l_count > 0 THEN
1503 FOR x in
1504 ( select asl.description entity, asl.lead_number entity_id,
1505 b.party_name , a.salesforce_id
1506 from pv_access_migration_log a,
1507 hz_parties b,
1508 pv_partner_profiles pvpp ,
1509 as_sales_leads asl
1510 where a.partner_customer_id = pvpp.partner_party_id
1511 and pvpp.partner_party_id = b.party_id
1512 and asl.sales_lead_id = a.sales_lead_id
1513 and a.action = 'DELETE_CORRUPT_LEAD_PARTNER' )
1514 loop
1515 i := i + 1;
1516 l_migration_tbl(i).entity := x.entity;
1517 l_migration_tbl(i).entity_id := x.entity_id;
1518 l_migration_tbl(i).party_name := x.party_name;
1519 l_migration_tbl(i).resource_id := x.salesforce_id;
1520 end loop;
1521
1522 fnd_message.set_name('PV','PV_LEAD_NAME_ATTR');
1526 fnd_message.set_name('PV','PV_PARTNER_NAME');
1523 l_title := RPAD(fnd_message.get,50,' ');
1524 fnd_message.set_name('PV','PV_LEAD_ID_ATTR');
1525 l_title := l_title || RPAD(fnd_message.get,18,' ');
1527 l_title := l_title || RPAD(fnd_message.get,40,' ');
1528 fnd_message.set_name('PV','PV_RESOURCE_ID_ATTR');
1529 l_title := l_title || RPAD(fnd_message.get,13,' ');
1530
1531 printOutput ( l_title);
1532 printOutput(RPAD('-',120,'-'));
1533
1534 FOR j in 1 .. l_migration_tbl.count
1535 LOOP
1536 printOutput( rpad(l_migration_tbl(j).entity,48,' ')||' '||rpad(l_migration_tbl(j).entity_id,16,' ')||' '||rpad(l_migration_tbl(j).party_name,38,' ')||' '||rpad(l_migration_tbl(j).resource_id,11,' '));
1537 END LOOP;
1538 printOutput(RPAD('=',120,'='));
1539 l_migration_tbl.delete;
1540 l_count := 0;
1541 i := 0;
1542 END IF;
1543 EXCEPTION
1544 WHEN OTHERS THEN
1545 printOutput('Database Error in generating report for lead external sales team partners : '||sqlerrm);
1546 END;
1547
1548 printOutput(RPAD(' ',120,' '));
1549 printOutput(RPAD(' ',120,' '));
1550 printOutput(RPAD(' ',120,' '));
1551
1552 BEGIN
1553 SELECT count(action)
1554 INTO l_count
1555 FROM pv_access_migration_log
1556 WHERE action = 'DELETE_CORRUPT_OPP_PARTNER';
1557
1558 fnd_message.set_name('PV','PV_DEL_PTR_OPP_ESLSTM');
1559 l_temp_msg := fnd_message.get ||' '||l_count;
1560
1561
1562 printOutput(RPAD('',(120-length(l_temp_msg))/2,' ')||l_temp_msg);
1563 printOutput(RPAD('-',120,'-'));
1564
1565
1566 IF l_count > 0 THEN
1567 FOR x in
1568 ( select distinct asl.description entity,
1569 asl.lead_number entity_id,
1570 b.party_name ,
1571 a.salesforce_id
1572 from pv_access_migration_log a,
1573 hz_parties b,
1574 pv_partner_profiles pvpp ,
1575 as_leads_all asl
1576 where a.partner_customer_id = pvpp.partner_party_id
1577 and pvpp.partner_party_id = b.party_id
1578 and asl.lead_id = a.lead_id
1579 and a.action = 'DELETE_CORRUPT_OPP_PARTNER')
1580 loop
1581 i := i + 1;
1582 l_migration_tbl(i).entity := x.entity;
1583 l_migration_tbl(i).entity_id := x.entity_id;
1584 l_migration_tbl(i).party_name := x.party_name;
1585 l_migration_tbl(i).resource_id := x.salesforce_id;
1586 end loop;
1587
1588 fnd_message.set_name('PV','PV_OPP_NAME_ATTR');
1589 l_title := RPAD(fnd_message.get,50,' ');
1590 fnd_message.set_name('PV','PV_OPP_NUMBER_ATTR');
1591 l_title := l_title || RPAD(fnd_message.get,18,' ');
1592 fnd_message.set_name('PV','PV_PARTNER_NAME');
1593 l_title := l_title || RPAD(fnd_message.get,40,' ');
1594 fnd_message.set_name('PV','PV_RESOURCE_ID_ATTR');
1595 l_title := l_title || RPAD(fnd_message.get,13,' ');
1596
1597 printOutput ( l_title);
1598 printOutput(RPAD('-',120,'-'));
1599
1600 FOR j in 1 .. l_migration_tbl.count
1601 LOOP
1602 printOutput( rpad(l_migration_tbl(j).entity,48,' ')||' '||rpad(l_migration_tbl(j).entity_id,16,' ')||' '||rpad(l_migration_tbl(j).party_name,38,' ')||' '||rpad(l_migration_tbl(j).resource_id,11,' '));
1603 END LOOP;
1604 printOutput(RPAD('=',120,'='));
1605
1606 l_migration_tbl.delete;
1607 l_count := 0;
1608 i := 0;
1609 END IF;
1610 EXCEPTION
1611 WHEN OTHERS THEN
1612 printOutput('Database Error in generating report for Opportunity external sales team partners : '||sqlerrm);
1613 END;
1614 printOutput(RPAD(' ',120,' '));
1615 printOutput(RPAD(' ',120,' '));
1616 printOutput(RPAD(' ',120,' '));
1617
1618 BEGIN
1619 SELECT count(action)
1620 INTO l_count
1621 FROM pv_access_migration_log
1622 WHERE action = 'INSERT_CUST_PARTY';
1623
1624 fnd_message.set_name('PV','PV_INS_PTR_CUST_ESLSTM');
1625 l_temp_msg := fnd_message.get ||' '||l_count;
1626
1627 printOutput(RPAD('',(120-length(l_temp_msg))/2,' ')||l_temp_msg);
1628 printOutput(RPAD('-',120,'-'));
1629 IF l_count > 0 THEN
1630 FOR x in
1631 ( select d.party_name entity, d.party_number entity_id,
1632 b.party_name , a.salesforce_id
1633 from pv_access_migration_log a,
1634 hz_parties b,
1635 pv_partner_profiles pvpp ,
1636 hz_parties d
1637 where a.partner_customer_id = pvpp.partner_id
1638 and pvpp.partner_party_id = b.party_id
1639 and a.customer_id = d.party_id
1640 and a.action = 'INSERT_CUST_PARTY' )
1641 loop
1642 i := i + 1;
1643 l_migration_tbl(i).entity := x.entity;
1644 l_migration_tbl(i).entity_id := x.entity_id;
1645 l_migration_tbl(i).party_name := x.party_name;
1646 l_migration_tbl(i).resource_id := x.salesforce_id;
1647 end loop;
1648 fnd_message.set_name('PV','PV_CUSTOMER_NAME_ATTR');
1649 l_title := RPAD(fnd_message.get,50,' ');
1650 fnd_message.set_name('PV','PV_CUSTOMER_ID_ATTR');
1654 fnd_message.set_name('PV','PV_RESOURCE_ID_ATTR');
1651 l_title := l_title || RPAD(fnd_message.get,18,' ');
1652 fnd_message.set_name('PV','PV_PARTNER_NAME');
1653 l_title := l_title || RPAD(fnd_message.get,40,' ');
1655 l_title := l_title || RPAD(fnd_message.get,13,' ');
1656
1657 printOutput ( l_title);
1658 printOutput(RPAD('-',120,'-'));
1659
1660 FOR j in 1 .. l_migration_tbl.count
1661 LOOP
1662 printOutput( rpad(l_migration_tbl(j).entity,48,' ')||' '||rpad(l_migration_tbl(j).entity_id,16,' ')||' '||rpad(l_migration_tbl(j).party_name,38,' ')||' '||rpad(l_migration_tbl(j).resource_id,11,' '));
1663 END LOOP;
1664 printOutput(RPAD('=',120,'='));
1665
1666 l_migration_tbl.delete;
1667 l_count := 0;
1668 i := 0;
1669 END IF;
1670 EXCEPTION
1671 WHEN OTHERS THEN
1672 printOutput('Database Error in generating report for customer external sales team partners : '||sqlerrm);
1673 END;
1674 printOutput(RPAD(' ',120,' '));
1675 printOutput(RPAD(' ',120,' '));
1676 printOutput(RPAD(' ',120,' '));
1677 BEGIN
1678
1679 SELECT count(action)
1680 INTO l_count
1681 FROM pv_access_migration_log
1682 WHERE action = 'INSERT_LEAD_PARTY';
1683
1684 fnd_message.set_name('PV','PV_INS_PTR_LEAD_ESLSTM');
1685 l_temp_msg := fnd_message.get ||' '||l_count;
1686
1687 printOutput(RPAD('',(120-length(l_temp_msg))/2,' ')||l_temp_msg);
1688 printOutput(RPAD('-',120,'-'));
1689 IF l_count > 0 THEN
1690 FOR x in
1691 ( select asl.description entity, asl.lead_number entity_id,
1692 b.party_name , a.salesforce_id
1693 from pv_access_migration_log a,
1694 hz_parties b,
1695 pv_partner_profiles pvpp ,
1696 as_sales_leads asl
1697 where a.partner_customer_id = pvpp.partner_id
1698 and pvpp.partner_party_id = b.party_id
1699 and asl.sales_lead_id = a.sales_lead_id
1700 and a.action = 'INSERT_LEAD_PARTY' )
1701 loop
1702 i := i + 1;
1703 l_migration_tbl(i).entity := x.entity;
1704 l_migration_tbl(i).entity_id := x.entity_id;
1705 l_migration_tbl(i).party_name := x.party_name;
1706 l_migration_tbl(i).resource_id := x.salesforce_id;
1707 end loop;
1708
1709 fnd_message.set_name('PV','PV_LEAD_NAME_ATTR');
1710 l_title := RPAD(fnd_message.get,50,' ');
1711 fnd_message.set_name('PV','PV_LEAD_ID_ATTR');
1712 l_title := l_title || RPAD(fnd_message.get,18,' ');
1713 fnd_message.set_name('PV','PV_PARTNER_NAME');
1714 l_title := l_title || RPAD(fnd_message.get,40,' ');
1715 fnd_message.set_name('PV','PV_RESOURCE_ID_ATTR');
1716 l_title := l_title || RPAD(fnd_message.get,13,' ');
1717
1718 printOutput ( l_title);
1719 printOutput(RPAD('-',120,'-'));
1720
1721 FOR j in 1 .. l_migration_tbl.count
1722 LOOP
1723 printOutput( rpad(l_migration_tbl(j).entity,48,' ')||' '||rpad(l_migration_tbl(j).entity_id,16,' ')||' '||rpad(l_migration_tbl(j).party_name,38,' ')||' '||rpad(l_migration_tbl(j).resource_id,11,' '));
1724 END LOOP;
1725 printOutput(RPAD('=',120,'='));
1726 l_migration_tbl.delete;
1727 l_count := 0;
1728 i := 0;
1729 END IF;
1730 EXCEPTION
1731 WHEN OTHERS THEN
1732 printOutput('Database Error in generating report for lead external sales team partners : '||sqlerrm);
1733 END;
1734
1735 printOutput(RPAD(' ',120,' '));
1736 printOutput(RPAD(' ',120,' '));
1737 printOutput(RPAD(' ',120,' '));
1738
1739 BEGIN
1740 SELECT count(action)
1741 INTO l_count
1742 FROM pv_access_migration_log
1743 WHERE action IN ('INSERT_OPP_PARTY','INSERT_OPP_PRFRD_PT','INSERT_OPP_SAVED_PT','INSERT_OPP_ASSIGNED_PT');
1744
1745 fnd_message.set_name('PV','PV_INS_PTR_OPP_ESLSTM');
1746 l_temp_msg := fnd_message.get ||' '||l_count;
1747
1748
1749 printOutput(RPAD('',(120-length(l_temp_msg))/2,' ')||l_temp_msg);
1750 printOutput(RPAD('-',120,'-'));
1751
1752
1753 IF l_count > 0 THEN
1754 FOR x in
1755 ( select distinct asl.description entity,
1756 asl.lead_number entity_id,
1757 b.party_name ,
1758 a.salesforce_id
1759 from pv_access_migration_log a,
1760 hz_parties b,
1761 pv_partner_profiles pvpp ,
1762 as_leads_all asl
1763 where a.partner_customer_id = pvpp.partner_id
1764 and pvpp.partner_party_id = b.party_id
1765 and asl.lead_id = a.lead_id
1766 and a.action IN ('INSERT_OPP_PARTY','INSERT_OPP_PRFRD_PT','INSERT_OPP_SAVED_PT','INSERT_OPP_ASSIGNED_PT'))
1767 loop
1768 i := i + 1;
1769 l_migration_tbl(i).entity := x.entity;
1770 l_migration_tbl(i).entity_id := x.entity_id;
1771 l_migration_tbl(i).party_name := x.party_name;
1772 l_migration_tbl(i).resource_id := x.salesforce_id;
1773 end loop;
1774
1775 fnd_message.set_name('PV','PV_OPP_NAME_ATTR');
1776 l_title := RPAD(fnd_message.get,50,' ');
1777 fnd_message.set_name('PV','PV_OPP_NUMBER_ATTR');
1778 l_title := l_title || RPAD(fnd_message.get,18,' ');
1779 fnd_message.set_name('PV','PV_PARTNER_NAME');
1780 l_title := l_title || RPAD(fnd_message.get,40,' ');
1781 fnd_message.set_name('PV','PV_RESOURCE_ID_ATTR');
1782 l_title := l_title || RPAD(fnd_message.get,13,' ');
1783
1784 printOutput ( l_title);
1785 printOutput(RPAD('-',120,'-'));
1786
1787 FOR j in 1 .. l_migration_tbl.count
1788 LOOP
1789 printOutput( rpad(l_migration_tbl(j).entity,48,' ')||' '||rpad(l_migration_tbl(j).entity_id,16,' ')||' '||rpad(l_migration_tbl(j).party_name,38,' ')||' '||rpad(l_migration_tbl(j).resource_id,11,' '));
1790 END LOOP;
1791 printOutput(RPAD('=',120,'='));
1792 l_migration_tbl.delete;
1793 l_count := 0;
1794 i := 0;
1795 END IF;
1796 EXCEPTION
1797 WHEN OTHERS THEN
1798 printOutput('Database Error in generating report for Opportunity external sales team partners : '||sqlerrm);
1799 END;
1800
1801 END printReport;
1802
1803 PROCEDURE printLog(p_message IN VARCHAR2)
1804 IS
1805 BEGIN
1806 FND_FILE.PUT_LINE(FND_FILE.LOG,p_message);
1807 END printLog;
1808
1809 PROCEDURE printOutput(p_message IN VARCHAR2)
1810 IS
1811 BEGIN
1812 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_message);
1813 -- printOutput(p_message);
1814 END printOutput;
1815
1816 END PV_SLSTEAM_MIGRTN_PVT;