[Home] [Help]
189: ) IS
190:
191: BEGIN
192:
193: arp_message.set_line( 'ARP_CMERGE_ARCUS.MERGE()+' );
194: --merge account site uses
195: ra_su( req_id, set_num, process_mode );
196:
197: --merge account sites
241: --hz_cust_contact_points has been obsoleted.
242: --merge contact points: phone
243: --ra_ph( req_id, set_num, process_mode );
244:
245: arp_message.set_line( 'ARP_CMERGE_ARCUS.MERGE()-' );
246:
247: END merge;
248: /*===========================================================================+
249: | PROCEDURE
299: l_org_id NUMBER(15);
300:
301: BEGIN
302:
303: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_BILL_TO_LOCATION()+' );
304: OPEN c1;
305: LOOP
306: FETCH c1 INTO l_dup_site_id,l_cust_site_id,l_bill_to_site_use_id,
307: l_site_use_code,l_create_same_site,l_org_id; --SSUptake
362:
363:
364: END LOOP;
365: CLOSE c1;
366: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_BILL_TO_LOCATION()-' );
367: END;
368: /*===========================================================================+
369: | PROCEDURE
370: | ra_su
441: AND (m.delete_duplicate_flag = 'N' OR m.delete_duplicate_flag = 'Y');
442:
443: BEGIN
444:
445: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_SU()+' );
446:
447: /* locking tables by opening and closing cursors */
448: IF process_mode = 'LOCK' THEN
449:
446:
447: /* locking tables by opening and closing cursors */
448: IF process_mode = 'LOCK' THEN
449:
450: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
451: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
452:
453: OPEN c1;
454: CLOSE c1;
447: /* locking tables by opening and closing cursors */
448: IF process_mode = 'LOCK' THEN
449:
450: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
451: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
452:
453: OPEN c1;
454: CLOSE c1;
455:
456: ELSE
457:
458: /*************** 'inactivate' mode ***************/
459:
460: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
461: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
462:
463: --inactivate customer account site uses.
464:
457:
458: /*************** 'inactivate' mode ***************/
459:
460: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
461: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
462:
463: --inactivate customer account site uses.
464:
465: UPDATE HZ_CUST_SITE_USES_ALL yt --SSUptake
482: AND m.delete_duplicate_flag = 'N' );
483:
484: g_count := sql%rowcount;
485:
486: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
487: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
488:
489: --Bug 7758559
490:
483:
484: g_count := sql%rowcount;
485:
486: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
487: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
488:
489: --Bug 7758559
490:
491: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
487: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
488:
489: --Bug 7758559
490:
491: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
492: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
493:
494: UPDATE hz_cust_site_uses_all asu
495: SET primary_flag = 'Y',
488:
489: --Bug 7758559
490:
491: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
492: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
493:
494: UPDATE hz_cust_site_uses_all asu
495: SET primary_flag = 'Y',
496: last_update_date = SYSDATE,
517: AND su.site_use_code = m.duplicate_site_code
518: AND su.primary_flag = 'Y'
519: AND su.status = 'A'));
520: g_count := sql%rowcount;
521: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
522: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
523:
524:
525: /** Bug 1610924: comment out the following code because of new om enhancement:
518: AND su.primary_flag = 'Y'
519: AND su.status = 'A'));
520: g_count := sql%rowcount;
521: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
522: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
523:
524:
525: /** Bug 1610924: comment out the following code because of new om enhancement:
526: allow merging all of the site uses of a customer.
527:
528: --Inactivate non-transaction site uses if no active ship_to,
529: --bill_to or market site uses remain for the account site.
530:
531: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
532: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
533:
534: UPDATE HZ_CUST_SITE_USES su1
535: SET status = 'I',
528: --Inactivate non-transaction site uses if no active ship_to,
529: --bill_to or market site uses remain for the account site.
530:
531: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
532: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
533:
534: UPDATE HZ_CUST_SITE_USES su1
535: SET status = 'I',
536: last_update_date = sysdate,
557: AND status = 'A' );
558:
559: g_count := sql%rowcount;
560:
561: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
562: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
563:
564: **/
565:
558:
559: g_count := sql%rowcount;
560:
561: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
562: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
563:
564: **/
565:
566: /*************** 'delete' mode ***************/
567:
568: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
569: --Physically delete them after merge.
570:
571: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
572: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
573:
574: UPDATE HZ_CUST_SITE_USES_ALL yt
575: SET status = 'D'
568: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
569: --Physically delete them after merge.
570:
571: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
572: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
573:
574: UPDATE HZ_CUST_SITE_USES_ALL yt
575: SET status = 'D'
576: WHERE EXISTS (
584: AND m.delete_duplicate_flag = 'Y' );
585:
586: g_count := sql%rowcount;
587:
588: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
589: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
590:
591: /** Bug 1610924: comment out the following code because of new om enhancement:
592: allow merging all of the site uses of a customer.
585:
586: g_count := sql%rowcount;
587:
588: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
589: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
590:
591: /** Bug 1610924: comment out the following code because of new om enhancement:
592: allow merging all of the site uses of a customer.
593:
593:
594: --'Delete' non-transaction site uses if no ship_to,
595: --bill_to, market site uses remain for the account site.
596:
597: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
598: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
599:
600: UPDATE HZ_CUST_SITE_USES su1
601: SET status = 'D'
594: --'Delete' non-transaction site uses if no ship_to,
595: --bill_to, market site uses remain for the account site.
596:
597: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
598: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
599:
600: UPDATE HZ_CUST_SITE_USES su1
601: SET status = 'D'
602: WHERE cust_acct_site_id IN (
616: AND status <> 'D' );
617:
618: g_count := sql%rowcount;
619:
620: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
621: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
622: **/
623:
624: END IF;
617:
618: g_count := sql%rowcount;
619:
620: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
621: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
622: **/
623:
624: END IF;
625: open c2;
640: x_msg_data =>x_msg_data);
641: --Handle error message
642: IF x_msg_count = 1 THEN
643: x_msg_data := x_msg_data || '**remap internal id**';
644: arp_message.set_line(
645: 'MOSR:remap internal id ERROR '||
646: x_msg_data);
647: ELSIF x_msg_count > 1 THEN
648:
648:
649: FOR x IN 1..x_msg_count LOOP
650: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
651: x_msg_data := x_msg_data || '**remap internal id**';
652: arp_message.set_line(
653: 'MOSR:remap internal id ERROR ' ||
654: x_msg_data );
655: END LOOP;
656: END IF;
661:
662: end loop;
663: close c2;
664:
665: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_SU()-' );
666:
667: EXCEPTION
668:
669: WHEN OTHERS THEN
666:
667: EXCEPTION
668:
669: WHEN OTHERS THEN
670: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_SU' );
671: RAISE;
672:
673: END ra_su;
674:
774:
775:
776: BEGIN
777:
778: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_ADDR()+' );
779:
780: /* locking tables by opening and closing cursors */
781: IF process_mode = 'LOCK' THEN
782:
779:
780: /* locking tables by opening and closing cursors */
781: IF process_mode = 'LOCK' THEN
782:
783: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
784: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
785:
786: OPEN c1;
787: CLOSE c1;
780: /* locking tables by opening and closing cursors */
781: IF process_mode = 'LOCK' THEN
782:
783: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
784: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
785:
786: OPEN c1;
787: CLOSE c1;
788:
792: ELSE
793:
794: /*************** 'inactivate' mode ***************/
795:
796: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
797: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
798:
799: --inactivate customer account site
800:
793:
794: /*************** 'inactivate' mode ***************/
795:
796: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
797: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
798:
799: --inactivate customer account site
800:
801: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
824: AND su.status = 'A' );
825:
826: g_count := sql%rowcount;
827:
828: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
829: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
830:
831: --Update bill_to_flag
832:
825:
826: g_count := sql%rowcount;
827:
828: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
829: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
830:
831: --Update bill_to_flag
832:
833: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
829: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
830:
831: --Update bill_to_flag
832:
833: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
834: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
835:
836: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
837: SET bill_to_flag = null,
830:
831: --Update bill_to_flag
832:
833: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
834: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
835:
836: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
837: SET bill_to_flag = null,
838: last_update_date = sysdate,
861: AND NVL(bill_to_flag, 'N') <> 'N';
862:
863: g_count := sql%rowcount;
864:
865: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
866: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
867:
868: --Update ship_to_flag
869:
862:
863: g_count := sql%rowcount;
864:
865: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
866: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
867:
868: --Update ship_to_flag
869:
870: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
866: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
867:
868: --Update ship_to_flag
869:
870: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
871: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
872:
873: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
874: SET ship_to_flag = null,
867:
868: --Update ship_to_flag
869:
870: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
871: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
872:
873: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
874: SET ship_to_flag = null,
875: last_update_date = sysdate,
898: AND NVL(ship_to_flag, 'N') <> 'N';
899:
900: g_count := sql%rowcount;
901:
902: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
903: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
904:
905: --Update market_flag
906:
899:
900: g_count := sql%rowcount;
901:
902: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
903: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
904:
905: --Update market_flag
906:
907: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
903: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
904:
905: --Update market_flag
906:
907: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
908: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
909:
910: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
911: SET market_flag = null,
904:
905: --Update market_flag
906:
907: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
908: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
909:
910: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
911: SET market_flag = null,
912: last_update_date = sysdate,
935: AND NVL(market_flag, 'N') <> 'N';
936:
937: g_count := sql%rowcount;
938:
939: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
940: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
941:
942: --Bug 7758559
943:
936:
937: g_count := sql%rowcount;
938:
939: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
940: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
941:
942: --Bug 7758559
943:
944: UPDATE hz_cust_acct_sites_all yt
967: AND su.org_id = m.org_id);
968:
969: g_count := sql%rowcount;
970:
971: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
972: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
973:
974: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
975: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
968:
969: g_count := sql%rowcount;
970:
971: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
972: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
973:
974: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
975: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
976:
970:
971: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
972: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
973:
974: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
975: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
976:
977: UPDATE hz_cust_acct_sites_all yt
978: SET ship_to_flag = 'P',
971: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
972: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
973:
974: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
975: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
976:
977: UPDATE hz_cust_acct_sites_all yt
978: SET ship_to_flag = 'P',
979: last_update_date = sysdate,
1000: AND yt.org_id = m.org_id);
1001:
1002: g_count := sql%rowcount;
1003:
1004: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1005: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1006:
1007: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1008: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
1001:
1002: g_count := sql%rowcount;
1003:
1004: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1005: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1006:
1007: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1008: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
1009:
1003:
1004: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1005: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1006:
1007: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1008: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
1009:
1010: UPDATE hz_cust_acct_sites_all yt
1011: SET market_flag = 'P',
1004: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1005: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1006:
1007: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1008: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
1009:
1010: UPDATE hz_cust_acct_sites_all yt
1011: SET market_flag = 'P',
1012: last_update_date = sysdate,
1032: AND su.org_id = m.org_id
1033: AND yt.org_id = m.org_id);
1034:
1035: g_count := sql%rowcount;
1036: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1037: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1038:
1039: --Bug 7758559
1040:
1033: AND yt.org_id = m.org_id);
1034:
1035: g_count := sql%rowcount;
1036: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1037: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1038:
1039: --Bug 7758559
1040:
1041: /*************** 'delete' mode ***************/
1042:
1043: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
1044: --Physically delete them after merge.
1045:
1046: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1047: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
1048:
1049: --'delete' customer account site
1050:
1043: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
1044: --Physically delete them after merge.
1045:
1046: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1047: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
1048:
1049: --'delete' customer account site
1050:
1051: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
1067: AND su.status <> 'D' );
1068:
1069: g_count := sql%rowcount;
1070:
1071: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1072: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1073:
1074: END IF;
1075:
1068:
1069: g_count := sql%rowcount;
1070:
1071: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1072: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1073:
1074: END IF;
1075:
1076: open c3;
1091: x_msg_data =>x_msg_data);
1092: --Handle error message
1093: IF x_msg_count = 1 THEN
1094: x_msg_data := x_msg_data || '**remap internal id**';
1095: arp_message.set_line(
1096: 'MOSR:remap internal id ERROR '||
1097: x_msg_data);
1098: ELSIF x_msg_count > 1 THEN
1099:
1099:
1100: FOR x IN 1..x_msg_count LOOP
1101: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1102: x_msg_data := x_msg_data || '**remap internal id**';
1103: arp_message.set_line(
1104: 'MOSR:remap internal id ERROR ' ||
1105: x_msg_data );
1106: END LOOP;
1107: END IF;
1113: end loop;
1114: close c3;
1115:
1116:
1117: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_ADDR()-' );
1118:
1119: EXCEPTION
1120:
1121: WHEN OTHERS THEN
1118:
1119: EXCEPTION
1120:
1121: WHEN OTHERS THEN
1122: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_ADDR' );
1123: RAISE;
1124:
1125: END ra_addr;
1126:
1225:
1226:
1227: BEGIN
1228:
1229: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CUST()+' );
1230:
1231: /* locking tables by opening and closing cursors */
1232: IF process_mode = 'LOCK' THEN
1233:
1230:
1231: /* locking tables by opening and closing cursors */
1232: IF process_mode = 'LOCK' THEN
1233:
1234: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
1235: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1236:
1237: OPEN c1;
1238: CLOSE c1;
1231: /* locking tables by opening and closing cursors */
1232: IF process_mode = 'LOCK' THEN
1233:
1234: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
1235: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1236:
1237: OPEN c1;
1238: CLOSE c1;
1239:
1243: ELSE
1244:
1245: /*************** 'inactivate' mode ***************/
1246:
1247: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1248: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1249:
1250: --inactivate customer account
1251:
1244:
1245: /*************** 'inactivate' mode ***************/
1246:
1247: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1248: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1249:
1250: --inactivate customer account
1251:
1252: UPDATE HZ_CUST_ACCOUNTS yt
1273: AND addr.status = 'A' );
1274:
1275: g_count := sql%rowcount;
1276:
1277: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1278: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1279:
1280: /*************** 'delete' mode ***************/
1281:
1274:
1275: g_count := sql%rowcount;
1276:
1277: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1278: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1279:
1280: /*************** 'delete' mode ***************/
1281:
1282: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
1281:
1282: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
1283: --Physically delete them after merge.
1284:
1285: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1286: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1287:
1288: --'delete' customer account
1289:
1282: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
1283: --Physically delete them after merge.
1284:
1285: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1286: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1287:
1288: --'delete' customer account
1289:
1290: UPDATE HZ_CUST_ACCOUNTS yt
1304: AND addr.status <> 'D' );
1305:
1306: g_count := sql%rowcount;
1307:
1308: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1309: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1310:
1311: END IF;
1312:
1305:
1306: g_count := sql%rowcount;
1307:
1308: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1309: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1310:
1311: END IF;
1312:
1313: open c3;
1328: x_msg_data =>x_msg_data);
1329: --Handle error message
1330: IF x_msg_count = 1 THEN
1331: x_msg_data := x_msg_data || '**remap internal id**';
1332: arp_message.set_line(
1333: 'MOSR:remap internal id ERROR '||
1334: x_msg_data);
1335: ELSIF x_msg_count > 1 THEN
1336:
1336:
1337: FOR x IN 1..x_msg_count LOOP
1338: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1339: x_msg_data := x_msg_data || '**remap internal id**';
1340: arp_message.set_line(
1341: 'MOSR:remap internal id ERROR ' ||
1342: x_msg_data );
1343: END LOOP;
1344: END IF;
1349:
1350: end loop;
1351: close c3;
1352:
1353: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CUST()-' );
1354:
1355: EXCEPTION
1356:
1357: WHEN OTHERS THEN
1354:
1355: EXCEPTION
1356:
1357: WHEN OTHERS THEN
1358: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_CUST' );
1359: RAISE;
1360:
1361: END ra_cust;
1362:
1496: --End of Bug No: 4527935
1497:
1498: BEGIN
1499:
1500: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CR()+' );
1501:
1502: /* locking tables by opening and closing cursors */
1503: IF process_mode = 'LOCK' THEN
1504:
1501:
1502: /* locking tables by opening and closing cursors */
1503: IF process_mode = 'LOCK' THEN
1504:
1505: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
1506: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1507:
1508: OPEN c1;
1509: CLOSE c1;
1502: /* locking tables by opening and closing cursors */
1503: IF process_mode = 'LOCK' THEN
1504:
1505: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
1506: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1507:
1508: OPEN c1;
1509: CLOSE c1;
1510:
1515:
1516: --Start of Bug No: 4558774
1517:
1518: /************** from account update ************/
1519: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1520: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1521:
1522: g_count := 0;
1523: FOR from_rec in c_from_rel_cust_id
1516: --Start of Bug No: 4558774
1517:
1518: /************** from account update ************/
1519: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1520: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1521:
1522: g_count := 0;
1523: FOR from_rec in c_from_rel_cust_id
1524: LOOP
1542: CLOSE c_acct_num;
1543: OPEN c_acct_num(l_to_cust_account_id);
1544: FETCH c_acct_num into l_to_acct_no;
1545: CLOSE c_acct_num;
1546: arp_message.set_name( 'AR', 'HZ_ACCT_MERGE_REL_WARNING' );
1547: arp_message.set_token( 'MERGE_FROM_ACCT',l_from_acct_no, FALSE );
1548: arp_message.set_token( 'MERGE_TO_ACCT',l_to_acct_no, FALSE );
1549: ELSE -- blend the two relationships
1550: IF(l_to_bill_to_flag <> 'Y' AND l_to_bill_to_flag <> from_rec.bill_to_flag) THEN
1543: OPEN c_acct_num(l_to_cust_account_id);
1544: FETCH c_acct_num into l_to_acct_no;
1545: CLOSE c_acct_num;
1546: arp_message.set_name( 'AR', 'HZ_ACCT_MERGE_REL_WARNING' );
1547: arp_message.set_token( 'MERGE_FROM_ACCT',l_from_acct_no, FALSE );
1548: arp_message.set_token( 'MERGE_TO_ACCT',l_to_acct_no, FALSE );
1549: ELSE -- blend the two relationships
1550: IF(l_to_bill_to_flag <> 'Y' AND l_to_bill_to_flag <> from_rec.bill_to_flag) THEN
1551: l_to_bill_to_flag := from_rec.bill_to_flag;
1544: FETCH c_acct_num into l_to_acct_no;
1545: CLOSE c_acct_num;
1546: arp_message.set_name( 'AR', 'HZ_ACCT_MERGE_REL_WARNING' );
1547: arp_message.set_token( 'MERGE_FROM_ACCT',l_from_acct_no, FALSE );
1548: arp_message.set_token( 'MERGE_TO_ACCT',l_to_acct_no, FALSE );
1549: ELSE -- blend the two relationships
1550: IF(l_to_bill_to_flag <> 'Y' AND l_to_bill_to_flag <> from_rec.bill_to_flag) THEN
1551: l_to_bill_to_flag := from_rec.bill_to_flag;
1552: l_update_flag := true;
1604: CLOSE c_acct_num;
1605: OPEN c_acct_num(l_to_related_cust_account_id);
1606: FETCH c_acct_num into l_to_acct_no;
1607: CLOSE c_acct_num;
1608: arp_message.set_name( 'AR', 'HZ_ACCT_MERGE_REL_WARNING' );
1609: arp_message.set_token( 'MERGE_FROM_ACCT',l_from_acct_no, FALSE );
1610: arp_message.set_token( 'MERGE_TO_ACCT',l_to_acct_no, FALSE );
1611: ELSE -- blend the two relationships
1612: IF(l_to_bill_to_flag <> 'Y' AND l_to_bill_to_flag <> from_rec.bill_to_flag) THEN
1605: OPEN c_acct_num(l_to_related_cust_account_id);
1606: FETCH c_acct_num into l_to_acct_no;
1607: CLOSE c_acct_num;
1608: arp_message.set_name( 'AR', 'HZ_ACCT_MERGE_REL_WARNING' );
1609: arp_message.set_token( 'MERGE_FROM_ACCT',l_from_acct_no, FALSE );
1610: arp_message.set_token( 'MERGE_TO_ACCT',l_to_acct_no, FALSE );
1611: ELSE -- blend the two relationships
1612: IF(l_to_bill_to_flag <> 'Y' AND l_to_bill_to_flag <> from_rec.bill_to_flag) THEN
1613: l_to_bill_to_flag := from_rec.bill_to_flag;
1606: FETCH c_acct_num into l_to_acct_no;
1607: CLOSE c_acct_num;
1608: arp_message.set_name( 'AR', 'HZ_ACCT_MERGE_REL_WARNING' );
1609: arp_message.set_token( 'MERGE_FROM_ACCT',l_from_acct_no, FALSE );
1610: arp_message.set_token( 'MERGE_TO_ACCT',l_to_acct_no, FALSE );
1611: ELSE -- blend the two relationships
1612: IF(l_to_bill_to_flag <> 'Y' AND l_to_bill_to_flag <> from_rec.bill_to_flag) THEN
1613: l_to_bill_to_flag := from_rec.bill_to_flag;
1614: l_update_flag := true;
1640: AND yt.related_cust_account_id = from_rec.related_cust_account_id
1641: AND yt.org_id = from_rec.org_id;
1642: g_count := g_count + sql%rowcount;
1643: END LOOP;
1644: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1645: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1646:
1647: /*************** 'inactivate' mode *************/
1648: --inactivate self relationships
1641: AND yt.org_id = from_rec.org_id;
1642: g_count := g_count + sql%rowcount;
1643: END LOOP;
1644: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1645: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1646:
1647: /*************** 'inactivate' mode *************/
1648: --inactivate self relationships
1649: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1645: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1646:
1647: /*************** 'inactivate' mode *************/
1648: --inactivate self relationships
1649: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1650: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1651:
1652: --Start bug 7192302
1653: /*UPDATE HZ_CUST_ACCT_RELATE_ALL yt
1646:
1647: /*************** 'inactivate' mode *************/
1648: --inactivate self relationships
1649: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1650: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1651:
1652: --Start bug 7192302
1653: /*UPDATE HZ_CUST_ACCT_RELATE_ALL yt
1654: SET status = 'I',
1721: AND M.DELETE_DUPLICATE_FLAG = 'N' );
1722:
1723: g_count :=g_count + sql%rowcount;
1724: --End bug 7192302
1725: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1726: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1727:
1728: --End of Bug No: 4527935
1729: /*************** for 'delete' mode *************/
1722:
1723: g_count :=g_count + sql%rowcount;
1724: --End bug 7192302
1725: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1726: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1727:
1728: --End of Bug No: 4527935
1729: /*************** for 'delete' mode *************/
1730: --delete those relationships that could not be updated
1734:
1735: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
1736: --Physically delete them after merge.
1737:
1738: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1739: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1740:
1741: /************** from account update ************/
1742:
1735: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
1736: --Physically delete them after merge.
1737:
1738: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1739: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1740:
1741: /************** from account update ************/
1742:
1743: UPDATE HZ_CUST_ACCT_RELATE_ALL yt --SSUptake
1792: AND m.org_id = yt.org_id); --SSUptake
1793:
1794: g_count := sql%rowcount;
1795:
1796: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1797: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1798:
1799: /************** to account update ************/
1800:
1793:
1794: g_count := sql%rowcount;
1795:
1796: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1797: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1798:
1799: /************** to account update ************/
1800:
1801: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1797: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1798:
1799: /************** to account update ************/
1800:
1801: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1802: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1803:
1804: UPDATE HZ_CUST_ACCT_RELATE_ALL yt
1805: SET status = 'D'
1798:
1799: /************** to account update ************/
1800:
1801: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1802: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1803:
1804: UPDATE HZ_CUST_ACCT_RELATE_ALL yt
1805: SET status = 'D'
1806: WHERE
1853: AND m.set_number = set_num );
1854:
1855: g_count := sql%rowcount;
1856:
1857: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1858: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1859:
1860: END IF;
1861:
1854:
1855: g_count := sql%rowcount;
1856:
1857: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1858: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1859:
1860: END IF;
1861:
1862: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CR()-' );
1858: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1859:
1860: END IF;
1861:
1862: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CR()-' );
1863:
1864: EXCEPTION
1865:
1866: WHEN OTHERS THEN
1863:
1864: EXCEPTION
1865:
1866: WHEN OTHERS THEN
1867: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_CR' );
1868: RAISE;
1869:
1870: END ra_cr;
1871:
1962: end_date end_date_tab;
1963: new_date date;
1964: BEGIN
1965:
1966: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CRM()+' );
1967:
1968: /* locking tables by opening and closing cursors */
1969: IF process_mode = 'LOCK' THEN
1970:
1967:
1968: /* locking tables by opening and closing cursors */
1969: IF process_mode = 'LOCK' THEN
1970:
1971: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1972: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
1973:
1974: OPEN c1;
1975: CLOSE c1;
1968: /* locking tables by opening and closing cursors */
1969: IF process_mode = 'LOCK' THEN
1970:
1971: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1972: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
1973:
1974: OPEN c1;
1975: CLOSE c1;
1976:
1980: ELSE
1981:
1982: /************** account site level inactivate ************/
1983:
1984: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1985: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
1986:
1987: --- bug 3786802
1988:
1981:
1982: /************** account site level inactivate ************/
1983:
1984: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1985: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
1986:
1987: --- bug 3786802
1988:
1989: SELECT distinct CUSTOMER_MERGE_HEADER_ID,
2167: WHERE CUST_RECEIPT_METHOD_ID = receipt_id(i);
2168:
2169: g_count := sql%rowcount;
2170:
2171: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2172: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2173:
2174: /************** account level inactivate ************/
2175:
2168:
2169: g_count := sql%rowcount;
2170:
2171: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2172: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2173:
2174: /************** account level inactivate ************/
2175:
2176: /* arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2172: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2173:
2174: /************** account level inactivate ************/
2175:
2176: /* arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2177: | arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
2178:
2179: |
2180: | UPDATE RA_CUST_RECEIPT_METHODS yt
2173:
2174: /************** account level inactivate ************/
2175:
2176: /* arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2177: | arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
2178:
2179: |
2180: | UPDATE RA_CUST_RECEIPT_METHODS yt
2181: | set end_date = sysdate,
2201: | AND acct.status = 'A' );
2202:
2203: | g_count := sql%rowcount;
2204:
2205: | arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2206: | arp_message.set_token( 'NUM_ROWS', to_char(g_count) ); */
2207:
2208: END IF;
2209:
2202:
2203: | g_count := sql%rowcount;
2204:
2205: | arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2206: | arp_message.set_token( 'NUM_ROWS', to_char(g_count) ); */
2207:
2208: END IF;
2209:
2210: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CRM()-' );
2206: | arp_message.set_token( 'NUM_ROWS', to_char(g_count) ); */
2207:
2208: END IF;
2209:
2210: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CRM()-' );
2211:
2212: EXCEPTION
2213:
2214: WHEN OTHERS THEN
2211:
2212: EXCEPTION
2213:
2214: WHEN OTHERS THEN
2215: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_CRM' );
2216: RAISE;
2217:
2218: END ra_crm;
2219:
2275: AND duplicate_id <> customer_id;
2276:
2277: BEGIN
2278:
2279: arp_message.set_line( 'ARP_CMERGE_ARCUS.copy_contacts()+' );
2280:
2281: OPEN accounts;
2282: LOOP
2283: FETCH accounts INTO from_account_id, to_account_id;
2283: FETCH accounts INTO from_account_id, to_account_id;
2284: EXIT WHEN accounts%NOTFOUND;
2285:
2286: /**
2287: arp_message.set_line(
2288: 'ARP_CMERGE_ARCUS:copy_contacts():from_account_id ' ||
2289: from_account_id );
2290: arp_message.set_line(
2291: 'ARP_CMERGE_ARCUS:copy_contacts():to_account_id ' ||
2286: /**
2287: arp_message.set_line(
2288: 'ARP_CMERGE_ARCUS:copy_contacts():from_account_id ' ||
2289: from_account_id );
2290: arp_message.set_line(
2291: 'ARP_CMERGE_ARCUS:copy_contacts():to_account_id ' ||
2292: to_account_id );
2293: **/
2294:
2300: FROM hz_cust_accounts
2301: WHERE cust_account_id = to_account_id;
2302:
2303: /**
2304: arp_message.set_line(
2305: 'ARP_CMERGE_ARCUS:copy_contacts():from_party_id '||
2306: from_party_id );
2307: arp_message.set_line(
2308: 'ARP_CMERGE_ARCUS:copy_contacts():to_party_id '||
2303: /**
2304: arp_message.set_line(
2305: 'ARP_CMERGE_ARCUS:copy_contacts():from_party_id '||
2306: from_party_id );
2307: arp_message.set_line(
2308: 'ARP_CMERGE_ARCUS:copy_contacts():to_party_id '||
2309: to_party_id );
2310: **/
2311:
2316:
2317: IF to_party_id = from_party_id THEN
2318:
2319: /**
2320: arp_message.set_line (
2321: 'ARP_CMERGE_ARCUS:copy_contacts(): ' ||
2322: 'merge_from party and merge_to party are same. ' ||
2323: 'Do not need copy org contact and contact points in account level.' );
2324: **/
2348:
2349: END LOOP;
2350: CLOSE accounts;
2351:
2352: arp_message.set_line( 'ARP_CMERGE_ARCUS.copy_contacts()-' );
2353:
2354: EXCEPTION
2355:
2356: WHEN OTHERS THEN
2353:
2354: EXCEPTION
2355:
2356: WHEN OTHERS THEN
2357: arp_message.set_error( 'ARP_CMERGE_ARCUS.copy_contacts' );
2358: RAISE;
2359:
2360: END copy_contacts;
2361:
2414: AND duplicate_address_id <> -1; --4693912
2415:
2416: BEGIN
2417:
2418: arp_message.set_line( 'ARP_CMERGE_ARCUS.copy_contacts_in_sites()+' );
2419:
2420: OPEN sites;
2421: LOOP
2422: FETCH sites INTO
2423: from_account_id, to_account_id, from_site_id, to_site_id;
2424: EXIT WHEN sites%NOTFOUND;
2425:
2426: /**
2427: arp_message.set_line(
2428: 'ARP_CMERGE_ARCUS:copy_contacts_in_sites():from_site_id ' ||
2429: from_site_id );
2430: arp_message.set_line(
2431: 'ARP_CMERGE_ARCUS:copy_contacts_in_sites():to_site_id ' ||
2426: /**
2427: arp_message.set_line(
2428: 'ARP_CMERGE_ARCUS:copy_contacts_in_sites():from_site_id ' ||
2429: from_site_id );
2430: arp_message.set_line(
2431: 'ARP_CMERGE_ARCUS:copy_contacts_in_sites():to_site_id ' ||
2432: to_site_id );
2433: **/
2434:
2440: FROM hz_cust_acct_sites_all
2441: WHERE cust_acct_site_id = to_site_id;
2442:
2443: /**
2444: arp_message.set_line(
2445: 'ARP_CMERGE_ARCUS:copy_contacts():from_party_site_id '||
2446: from_party_site_id );
2447: arp_message.set_line(
2448: 'ARP_CMERGE_ARCUS:copy_contacts():to_party_site_id '||
2443: /**
2444: arp_message.set_line(
2445: 'ARP_CMERGE_ARCUS:copy_contacts():from_party_site_id '||
2446: from_party_site_id );
2447: arp_message.set_line(
2448: 'ARP_CMERGE_ARCUS:copy_contacts():to_party_site_id '||
2449: to_party_site_id );
2450: **/
2451:
2456:
2457: IF to_party_site_id = from_party_site_id THEN
2458:
2459: /**
2460: arp_message.set_line (
2461: 'ARP_CMERGE_ARCUS:copy_contacts(): ' ||
2462: 'merge_from party and merge_to party site are same. ' ||
2463: 'Do not need copy contact points.' );
2464: **/
2475:
2476: END LOOP;
2477: CLOSE sites;
2478:
2479: arp_message.set_line( 'ARP_CMERGE_ARCUS.copy_contacts_in_sites()-' );
2480:
2481: EXCEPTION
2482:
2483: WHEN OTHERS THEN
2480:
2481: EXCEPTION
2482:
2483: WHEN OTHERS THEN
2484: arp_message.set_error( 'ARP_CMERGE_ARCUS.copy_contacts_in_sites' );
2485: RAISE;
2486:
2487: END copy_contacts_in_sites;
2488:
2590: ---Bug NO: 5067291
2591:
2592: BEGIN
2593:
2594: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CONT()+' );
2595:
2596: /* locking tables by opening and closing cursors */
2597: IF process_mode = 'LOCK' then
2598:
2595:
2596: /* locking tables by opening and closing cursors */
2597: IF process_mode = 'LOCK' then
2598:
2599: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
2600: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2601:
2602: OPEN c1;
2603: CLOSE c1;
2596: /* locking tables by opening and closing cursors */
2597: IF process_mode = 'LOCK' then
2598:
2599: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
2600: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2601:
2602: OPEN c1;
2603: CLOSE c1;
2604:
2621: OPEN c3;--Bug NO: 5067291
2622: FETCH c3 INTO l_party_id;
2623: IF c3%FOUND THEN
2624: /*********************Inactivate account site/account role...Bug No. 5067291*********/
2625: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2626: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2627: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2628: SET status = 'I',
2629: last_update_date = sysdate,
2622: FETCH c3 INTO l_party_id;
2623: IF c3%FOUND THEN
2624: /*********************Inactivate account site/account role...Bug No. 5067291*********/
2625: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2626: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2627: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2628: SET status = 'I',
2629: last_update_date = sysdate,
2630: last_updated_by = hz_utility_v2pub.user_id,
2635: program_update_date = sysdate
2636: WHERE party_id = l_party_id
2637: AND nvl(status,'A') ='A';
2638:
2639: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2640: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2641:
2642:
2643: g_count := sql%rowcount;
2636: WHERE party_id = l_party_id
2637: AND nvl(status,'A') ='A';
2638:
2639: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2640: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2641:
2642:
2643: g_count := sql%rowcount;
2644:
2641:
2642:
2643: g_count := sql%rowcount;
2644:
2645: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2646: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2647:
2648: END IF;
2649:
2642:
2643: g_count := sql%rowcount;
2644:
2645: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2646: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2647:
2648: END IF;
2649:
2650: /************** account site level update ************/
2648: END IF;
2649:
2650: /************** account site level update ************/
2651:
2652: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2653: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2654:
2655: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2656: SET (cust_account_id, cust_acct_site_id) = (
2649:
2650: /************** account site level update ************/
2651:
2652: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2653: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2654:
2655: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2656: SET (cust_account_id, cust_acct_site_id) = (
2657: SELECT MIN(m.customer_id), MIN(m.customer_address_id)
2678: AND party_id <> nvl(l_party_id,-99);---Bug No. 5067291
2679:
2680: g_count := sql%rowcount;
2681:
2682: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2683: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2684:
2685: /************** account level update ************/
2686:
2679:
2680: g_count := sql%rowcount;
2681:
2682: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2683: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2684:
2685: /************** account level update ************/
2686:
2687: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2683: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2684:
2685: /************** account level update ************/
2686:
2687: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2688: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2689:
2690: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2691: SET cust_account_id = (
2684:
2685: /************** account level update ************/
2686:
2687: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2688: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2689:
2690: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2691: SET cust_account_id = (
2692: SELECT m.customer_id
2735: -- End Bug 4712462
2736:
2737: g_count := sql%rowcount;
2738:
2739: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2740: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2741: CLOSE C3;--5067291
2742:
2743: elsif g_transfer_account_contacts = 'Y' THEN
2736:
2737: g_count := sql%rowcount;
2738:
2739: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2740: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2741: CLOSE C3;--5067291
2742:
2743: elsif g_transfer_account_contacts = 'Y' THEN
2744:
2745: OPEN c3_1;--Bug NO: 5067291
2746: FETCH c3_1 INTO l_party_id;
2747: IF c3_1%FOUND THEN
2748: /*********************Inactivate account site/account role...Bug No. 5067291*********/
2749: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2750: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2751: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2752: SET status = 'I',
2753: last_update_date = sysdate,
2746: FETCH c3_1 INTO l_party_id;
2747: IF c3_1%FOUND THEN
2748: /*********************Inactivate account site/account role...Bug No. 5067291*********/
2749: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2750: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2751: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2752: SET status = 'I',
2753: last_update_date = sysdate,
2754: last_updated_by = hz_utility_v2pub.user_id,
2759: program_update_date = sysdate
2760: WHERE party_id = l_party_id
2761: AND nvl(status,'A') ='A';
2762:
2763: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2764: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2765:
2766: g_count := sql%rowcount;
2767:
2760: WHERE party_id = l_party_id
2761: AND nvl(status,'A') ='A';
2762:
2763: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2764: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2765:
2766: g_count := sql%rowcount;
2767:
2768: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2764: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2765:
2766: g_count := sql%rowcount;
2767:
2768: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2769: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2770:
2771: END IF;
2772:
2765:
2766: g_count := sql%rowcount;
2767:
2768: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2769: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2770:
2771: END IF;
2772:
2773: /************** account site level update ************/
2771: END IF;
2772:
2773: /************** account site level update ************/
2774:
2775: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2776: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2777:
2778: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2779: SET (cust_account_id, cust_acct_site_id) = (
2772:
2773: /************** account site level update ************/
2774:
2775: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2776: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2777:
2778: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2779: SET (cust_account_id, cust_acct_site_id) = (
2780: SELECT MIN(m.customer_id), MIN(m.customer_address_id)
2801: AND party_id <> nvl(l_party_id,-99);---Bug No. 5067291
2802:
2803: g_count := sql%rowcount;
2804:
2805: ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_UPDATED' );
2806: ARP_MESSAGE.SET_TOKEN( 'NUM_ROWS', TO_CHAR(G_COUNT) );
2807:
2808: /************** account level update ************/
2809:
2802:
2803: g_count := sql%rowcount;
2804:
2805: ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_UPDATED' );
2806: ARP_MESSAGE.SET_TOKEN( 'NUM_ROWS', TO_CHAR(G_COUNT) );
2807:
2808: /************** account level update ************/
2809:
2810: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2806: ARP_MESSAGE.SET_TOKEN( 'NUM_ROWS', TO_CHAR(G_COUNT) );
2807:
2808: /************** account level update ************/
2809:
2810: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2811: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2812:
2813: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2814: SET cust_account_id = (
2807:
2808: /************** account level update ************/
2809:
2810: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2811: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2812:
2813: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2814: SET cust_account_id = (
2815: SELECT m.customer_id
2859: -- End Bug 4712462
2860:
2861: g_count := sql%rowcount;
2862:
2863: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2864: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2865: CLOSE C3_1;--5067291
2866:
2867: END IF; --g_tranfer_account_contacts
2860:
2861: g_count := sql%rowcount;
2862:
2863: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2864: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2865: CLOSE C3_1;--5067291
2866:
2867: END IF; --g_tranfer_account_contacts
2868: END IF; --process_mode
2865: CLOSE C3_1;--5067291
2866:
2867: END IF; --g_tranfer_account_contacts
2868: END IF; --process_mode
2869: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CONT()-' );
2870:
2871: EXCEPTION
2872:
2873: WHEN OTHERS THEN
2870:
2871: EXCEPTION
2872:
2873: WHEN OTHERS THEN
2874: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_CONT' );
2875: RAISE;
2876:
2877: END ra_cont;
2878:
2942: FOR UPDATE NOWAIT;
2943:
2944: BEGIN
2945:
2946: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_PH()+' );
2947:
2948: /* locking tables by opening and closing cursors */
2949: IF process_mode = 'LOCK' THEN
2950:
2947:
2948: /* locking tables by opening and closing cursors */
2949: IF process_mode = 'LOCK' THEN
2950:
2951: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
2952: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_CONTACT_POINTS', FALSE );
2953:
2954: OPEN c1;
2955: CLOSE c1;
2948: /* locking tables by opening and closing cursors */
2949: IF process_mode = 'LOCK' THEN
2950:
2951: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
2952: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_CONTACT_POINTS', FALSE );
2953:
2954: OPEN c1;
2955: CLOSE c1;
2956:
2960: ELSE
2961:
2962: /************** account site level inactivate ************/
2963:
2964: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2965: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_CONTACT_POINTS', FALSE );
2966:
2967: UPDATE HZ_CUST_CONTACT_POINTS yt
2968: SET (cust_account_id, cust_account_site_id) = (
2961:
2962: /************** account site level inactivate ************/
2963:
2964: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2965: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_CONTACT_POINTS', FALSE );
2966:
2967: UPDATE HZ_CUST_CONTACT_POINTS yt
2968: SET (cust_account_id, cust_account_site_id) = (
2969: SELECT min(m.customer_id), min(m.customer_address_id)
2988: AND m.set_number = set_num );
2989:
2990: g_count := sql%rowcount;
2991:
2992: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2993: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2994:
2995: /************** account level inactivate ************/
2996:
2989:
2990: g_count := sql%rowcount;
2991:
2992: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2993: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2994:
2995: /************** account level inactivate ************/
2996:
2997: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2993: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2994:
2995: /************** account level inactivate ************/
2996:
2997: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2998: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_CONTACT_POINTS', FALSE );
2999:
3000: UPDATE HZ_CUST_CONTACT_POINTS yt
3001: SET cust_account_id = (
2994:
2995: /************** account level inactivate ************/
2996:
2997: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2998: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_CONTACT_POINTS', FALSE );
2999:
3000: UPDATE HZ_CUST_CONTACT_POINTS yt
3001: SET cust_account_id = (
3002: SELECT DISTINCT m.customer_id
3021: AND cust_account_site_id IS NULL;
3022:
3023: g_count := sql%rowcount;
3024:
3025: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3026: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3027:
3028: END IF;
3029:
3022:
3023: g_count := sql%rowcount;
3024:
3025: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3026: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3027:
3028: END IF;
3029:
3030: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_PH()-' );
3026: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3027:
3028: END IF;
3029:
3030: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_PH()-' );
3031:
3032: EXCEPTION
3033:
3034: WHEN OTHERS THEN
3031:
3032: EXCEPTION
3033:
3034: WHEN OTHERS THEN
3035: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_PH' );
3036: RAISE;
3037:
3038: END ra_ph;
3039:
3099:
3100:
3101: BEGIN
3102:
3103: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_USG()+' );
3104:
3105: /* locking tables by opening and closing cursors */
3106: IF process_mode = 'LOCK' THEN
3107:
3104:
3105: /* locking tables by opening and closing cursors */
3106: IF process_mode = 'LOCK' THEN
3107:
3108: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
3109: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
3110:
3111: OPEN c1;
3112: CLOSE c1;
3105: /* locking tables by opening and closing cursors */
3106: IF process_mode = 'LOCK' THEN
3107:
3108: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
3109: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
3110:
3111: OPEN c1;
3112: CLOSE c1;
3113:
3113:
3114: ELSE
3115: /*************** 'inactivate' mode ***************/
3116:
3117: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
3118: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
3119:
3120: --inactivate active 'CUSTOMER' usage of merge-from party if the merge-from account is the only active account
3121:
3114: ELSE
3115: /*************** 'inactivate' mode ***************/
3116:
3117: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
3118: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
3119:
3120: --inactivate active 'CUSTOMER' usage of merge-from party if the merge-from account is the only active account
3121:
3122:
3150: AND c1.status = 'I'
3151: );
3152: g_count := sql%rowcount;
3153:
3154: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3155: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3156:
3157: /*************** 'delete' mode ***************/
3158: ---Bug No. 4558392
3151: );
3152: g_count := sql%rowcount;
3153:
3154: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3155: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3156:
3157: /*************** 'delete' mode ***************/
3158: ---Bug No. 4558392
3159: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
3155: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3156:
3157: /*************** 'delete' mode ***************/
3158: ---Bug No. 4558392
3159: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
3160: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
3161:
3162: --inactivate active 'CUSTOMER' usage of merge-from party when delete after merge is checked if
3163: -- There exists any other Inactive accounts AND doesnot exist any active accounts associated with the merge-from
3156:
3157: /*************** 'delete' mode ***************/
3158: ---Bug No. 4558392
3159: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
3160: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
3161:
3162: --inactivate active 'CUSTOMER' usage of merge-from party when delete after merge is checked if
3163: -- There exists any other Inactive accounts AND doesnot exist any active accounts associated with the merge-from
3164: --party AND there is no other customer usage associated with that party.
3209: AND c1.status = 'D'--Bug No. 4558392
3210: );
3211: g_count := sql%rowcount;
3212:
3213: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3214: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3215:
3216: -----Bug No. 4558392
3217: /*************** 'delete' mode ***************/
3210: );
3211: g_count := sql%rowcount;
3212:
3213: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3214: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3215:
3216: -----Bug No. 4558392
3217: /*************** 'delete' mode ***************/
3218:
3217: /*************** 'delete' mode ***************/
3218:
3219:
3220:
3221: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
3222: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
3223:
3224: --Delete all 'CUSTOMER' usage of merge-from party
3225: --if the merge-from account is the only account for merge-from party OR there are any other customer usages associated with that party.
3218:
3219:
3220:
3221: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
3222: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
3223:
3224: --Delete all 'CUSTOMER' usage of merge-from party
3225: --if the merge-from account is the only account for merge-from party OR there are any other customer usages associated with that party.
3226:
3266: );
3267:
3268: g_count := sql%rowcount;
3269:
3270: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3271: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3272:
3273:
3274: END IF;
3267:
3268: g_count := sql%rowcount;
3269:
3270: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3271: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3272:
3273:
3274: END IF;
3275: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_USG()-' );
3271: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3272:
3273:
3274: END IF;
3275: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_USG()-' );
3276:
3277: EXCEPTION
3278:
3279: WHEN OTHERS THEN
3276:
3277: EXCEPTION
3278:
3279: WHEN OTHERS THEN
3280: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_USG' );
3281: RAISE;
3282: END ra_usg;
3283: --4307679
3284:
3380: /* AND rel.directional_flag = 'F'; */ /* Bug No : 2359461 */
3381:
3382: BEGIN
3383:
3384: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_cust_merge_contacts()+' );
3385:
3386: /* Copy org contact */
3387: OPEN c;
3388: LOOP
3412:
3413: END LOOP;
3414: CLOSE c;
3415:
3416: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_cust_merge_contacts()-' );
3417:
3418: EXCEPTION
3419:
3420: WHEN OTHERS THEN
3417:
3418: EXCEPTION
3419:
3420: WHEN OTHERS THEN
3421: arp_message.set_error( 'ARP_CMERGE_ARCUS.do_cust_merge_contacts' );
3422: RAISE;
3423:
3424: END do_cust_merge_contacts;
3425:
3553: AND set_number = p_set_num;
3554:
3555: BEGIN
3556:
3557: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_site_merge_contacts()+' );
3558:
3559: /* For each site level org contact do the following */
3560: OPEN d;
3561: LOOP
3618:
3619: END LOOP;
3620: CLOSE d;
3621:
3622: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_site_merge_contacts()-' );
3623:
3624: EXCEPTION
3625:
3626: WHEN OTHERS THEN
3623:
3624: EXCEPTION
3625:
3626: WHEN OTHERS THEN
3627: arp_message.set_error( 'ARP_CMERGE_ARCUS.do_site_merge_contacts' );
3628: RAISE;
3629:
3630: END do_site_merge_contacts;
3631:
3713: l_party_type varchar2(30);
3714: --End of Bug No: 4387523
3715: BEGIN
3716:
3717: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_merge_contacts()+' );
3718:
3719: --check if the org contact has been migrated
3720: BEGIN
3721: l_sql := 'SELECT new_id ' ||
3743: p_to_party_id,
3744: x_org_contact_id ,
3745: p_from_account_id);
3746:
3747: --arp_message.set_line( '****' || to_char(x_org_contact_id) ||
3748: --'****' || l_dup_exists );
3749:
3750: IF l_dup_exists = FND_API.G_TRUE THEN
3751: l_insert := FND_API.G_TRUE;
3782: x_msg_data );
3783:
3784: IF x_msg_count = 1 THEN
3785: x_msg_data := x_msg_data || '**GET_CURRENT_ORG_CONTACT**';
3786: arp_message.set_line(
3787: 'do_merge_contacts:get_org_contact_rec ERROR ' ||
3788: x_msg_data );
3789: ELSIF x_msg_count > 1 THEN
3790:
3790:
3791: FOR x IN 1..x_msg_count LOOP
3792: x_msg_data := FND_MSG_PUB.GET(p_encoded => fnd_api.g_false);
3793: x_msg_data := x_msg_data || '**GET_CURRENT_ORG_CONTACT**';
3794: arp_message.set_line(
3795: 'do_merge_contacts:get_org_contact_rec ERROR ' ||
3796: x_msg_data );
3797: END LOOP;
3798: END IF;
3853: x_msg_data => x_msg_data);
3854:
3855: IF x_msg_count = 1 THEN
3856: x_msg_data := x_msg_data || '**CREATE_ORG_CONTACT**';
3857: arp_message.set_line(
3858: 'do_merge_contacts:create_org_contact ERROR ' ||
3859: x_msg_data );
3860: ELSIF x_msg_count > 1 THEN
3861:
3861:
3862: FOR x IN 1..x_msg_count LOOP
3863: x_msg_data := FND_MSG_PUB.GET(p_encoded => fnd_api.g_false);
3864: x_msg_data := x_msg_data || '**CREATE_ORG_CONTACT**';
3865: arp_message.set_line(
3866: 'do_cust_merge_contacts:create_org_contact ERROR ' ||
3867: x_msg_data );
3868: END LOOP;
3869: END IF;
3908: p_org_party_id,
3909: x_org_party_id,
3910: p_from_account_id );
3911:
3912: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_merge_contacts()-' );
3913:
3914: EXCEPTION
3915:
3916: WHEN OTHERS THEN
3913:
3914: EXCEPTION
3915:
3916: WHEN OTHERS THEN
3917: arp_message.set_error( 'ARP_CMERGE_ARCUS.do_merge_contacts' );
3918: RAISE;
3919:
3920: END do_merge_contacts;
3921:
3966: ) IS
3967:
3968: BEGIN
3969:
3970: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_cust_merge_cpoint()+' );
3971:
3972: do_copy_contact_points ( 'HZ_PARTIES',
3973: p_from_party_id,
3974: p_to_party_id,
3973: p_from_party_id,
3974: p_to_party_id,
3975: p_from_account_id );
3976:
3977: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_cust_merge_cpoint()-' );
3978:
3979: EXCEPTION
3980:
3981: WHEN OTHERS THEN
3978:
3979: EXCEPTION
3980:
3981: WHEN OTHERS THEN
3982: arp_message.set_error( 'ARP_CMERGE_ARCUS.do_cust_merge_cpoint' );
3983: RAISE;
3984:
3985: END do_cust_merge_cpoint;
3986:
4031: ) IS
4032:
4033: BEGIN
4034:
4035: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_site_merge_cpoint()+' );
4036:
4037: do_copy_contact_points ( 'HZ_PARTY_SITES',
4038: p_from_party_site_id,
4039: p_to_party_site_id,
4038: p_from_party_site_id,
4039: p_to_party_site_id,
4040: p_from_account_id );
4041:
4042: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_site_merge_cpoint()-' );
4043:
4044: EXCEPTION
4045:
4046: WHEN OTHERS THEN
4043:
4044: EXCEPTION
4045:
4046: WHEN OTHERS THEN
4047: arp_message.set_error( 'ARP_CMERGE_ARCUS.do_site_merge_cpoint' );
4048: RAISE;
4049:
4050: END do_site_merge_cpoint;
4051:
4128: l_dup_exists VARCHAR2(10) := fnd_api.g_false;
4129:
4130: BEGIN
4131:
4132: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_copy_contact_points ()+' );
4133:
4134: OPEN c;
4135: LOOP
4136: FETCH c INTO l_contact_point_id;
4140: l_dup_exists := check_contact_point_dup (l_contact_point_id,
4141: p_to_id,
4142: x_contact_point_id);
4143:
4144: --arp_message.set_line( '****' || to_char(x_contact_point_id) ||
4145: --'****' || l_dup_exists );
4146:
4147: IF l_dup_exists <> fnd_api.g_true THEN
4148:
4162: x_web_rec => l_web_rec,
4163: x_return_status => x_return_status,
4164: x_msg_count => x_msg_count,
4165: x_msg_data => x_msg_data);
4166: arp_message.set_line('ID ' || l_contact_point_rec.owner_table_id);
4167:
4168: IF x_msg_count = 1 THEN
4169: x_msg_data := x_msg_data || '**GET_CURRENT_CONTACT_POINTS**';
4170: arp_message.set_line(
4166: arp_message.set_line('ID ' || l_contact_point_rec.owner_table_id);
4167:
4168: IF x_msg_count = 1 THEN
4169: x_msg_data := x_msg_data || '**GET_CURRENT_CONTACT_POINTS**';
4170: arp_message.set_line(
4171: 'do_copy_contact_points:get_current_contact_points ERROR ' ||
4172: x_msg_data );
4173: ELSIF x_msg_count > 1 THEN
4174:
4174:
4175: FOR x IN 1..x_msg_count LOOP
4176: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
4177: x_msg_data := x_msg_data || '**GET_CURRENT_CONTACT_POINTS**';
4178: arp_message.set_line(
4179: 'do_copy_contact_points:get_current_contact_points ERROR ' ||
4180: x_msg_data );
4181: END LOOP;
4182: END IF;
4190: l_contact_point_rec.contact_point_id := NULL;
4191: l_contact_point_rec.primary_flag := NULL;
4192: l_contact_point_rec.owner_table_id := p_to_id;
4193:
4194: arp_message.set_line('ID ' || l_contact_point_rec.owner_table_id);
4195:
4196: --We should not copy the following columns. They are supposed to be
4197: --unique
4198: l_edi_rec.edi_tp_header_id := NULL;
4216: x_msg_data);
4217:
4218: IF x_msg_count = 1 THEN
4219: x_msg_data := x_msg_data || '**CREATE_CONTACT_POINTS**';
4220: arp_message.set_line(
4221: 'do_copy_contact_points:create_contact_points Error '||
4222: x_msg_data);
4223: ELSIF x_msg_count > 1 THEN
4224:
4224:
4225: FOR x IN 1..x_msg_count LOOP
4226: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
4227: x_msg_data := x_msg_data || '**CREATE_CONTACT_POINTS**';
4228: arp_message.set_line(
4229: 'do_copy_contact_points:create_contact_points ERROR ' ||
4230: x_msg_data );
4231: END LOOP;
4232: END IF;
4237: RAISE fnd_api.g_exc_error;
4238: END IF;
4239:
4240: /**
4241: arp_message.set_line(
4242: 'ARP_CMERGE_ARCUS:do_copy_contact_points():old contact point id =' ||
4243: l_contact_point_id );
4244:
4245: arp_message.set_line(
4241: arp_message.set_line(
4242: 'ARP_CMERGE_ARCUS:do_copy_contact_points():old contact point id =' ||
4243: l_contact_point_id );
4244:
4245: arp_message.set_line(
4246: 'ARP_CMERGE_ARCUS.do_copy_contact_points:contact_point_id = ' ||
4247: x_contact_point_id );
4248: **/
4249:
4260: **/
4261: END LOOP;
4262: CLOSE c;
4263:
4264: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_copy_contact_points()-' );
4265:
4266: EXCEPTION
4267:
4268: WHEN OTHERS THEN
4265:
4266: EXCEPTION
4267:
4268: WHEN OTHERS THEN
4269: arp_message.set_error( 'ARP_CMERGE_ARCUS.do_copy_contact_points' );
4270: RAISE;
4271:
4272: END do_copy_contact_points;
4273:
4381:
4382: EXCEPTION
4383:
4384: WHEN OTHERS THEN
4385: arp_message.set_error( 'ARP_CMERGE_ARCUS.check_org_contact_dup' );
4386: RAISE;
4387:
4388: END check_org_contact_dup;
4389:
4514: RETURN FND_API.G_TRUE;
4515:
4516: EXCEPTION
4517: WHEN OTHERS THEN
4518: arp_message.set_error( 'ARP_CMERGE_ARCUS.check_contact_point_dup' );
4519: RAISE;
4520:
4521: END check_contact_point_dup;
4522:
4552: l_sql VARCHAR2(1000);
4553:
4554: BEGIN
4555:
4556: arp_message.set_line( 'ARP_CMERGE_ARCUS.create_temporary_table ( ' ||
4557: g_table_name || ' )()+' );
4558:
4559: BEGIN
4560:
4584: END IF;
4585:
4586: EXECUTE IMMEDIATE l_sql;
4587:
4588: arp_message.set_line( 'ARP_CMERGE_ARCUS.create_temporary_table ( ' ||
4589: g_table_name || ' )()-' );
4590:
4591: EXCEPTION
4592:
4590:
4591: EXCEPTION
4592:
4593: WHEN OTHERS THEN
4594: arp_message.set_error( 'ARP_CMERGE_ARCUS.create_temporary_table ( ' ||
4595: g_table_name || ' )' );
4596: RAISE;
4597:
4598: END create_temporary_table;
4730: AND m.delete_duplicate_flag = 'Y');
4731:
4732: BEGIN
4733:
4734: arp_message.set_line( 'ARP_CMERGE_ARCUS.delete_rows()+' );
4735:
4736: /*****************************************************/
4737:
4738: --4307679
4735:
4736: /*****************************************************/
4737:
4738: --4307679
4739: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4740: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
4741:
4742: --lock rows
4743: OPEN cust_usage;
4736: /*****************************************************/
4737:
4738: --4307679
4739: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4740: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
4741:
4742: --lock rows
4743: OPEN cust_usage;
4744: CLOSE cust_usage;
4754: AND m.delete_duplicate_flag = 'Y');
4755:
4756: g_count := sql%rowcount;
4757:
4758: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4759: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4760:
4761: --4397679
4762:
4755:
4756: g_count := sql%rowcount;
4757:
4758: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4759: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4760:
4761: --4397679
4762:
4763: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4759: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4760:
4761: --4397679
4762:
4763: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4764: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
4765:
4766: --lock rows
4767: OPEN cust_site_uses;
4760:
4761: --4397679
4762:
4763: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4764: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
4765:
4766: --lock rows
4767: OPEN cust_site_uses;
4768: CLOSE cust_site_uses;
4797:
4798:
4799: g_count := sql%rowcount;
4800:
4801: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4802: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4803:
4804: /*****************************************************/
4805:
4798:
4799: g_count := sql%rowcount;
4800:
4801: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4802: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4803:
4804: /*****************************************************/
4805:
4806: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4802: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4803:
4804: /*****************************************************/
4805:
4806: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4807: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
4808:
4809: --lock rows
4810: OPEN cust_sites;
4803:
4804: /*****************************************************/
4805:
4806: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4807: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
4808:
4809: --lock rows
4810: OPEN cust_sites;
4811: CLOSE cust_sites;
4836: AND m.set_number = set_num
4837: AND m.delete_duplicate_flag = 'Y' )
4838: AND status = 'D';
4839:
4840: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4841: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4842:
4843: /*****************************************************/
4844:
4837: AND m.delete_duplicate_flag = 'Y' )
4838: AND status = 'D';
4839:
4840: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4841: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4842:
4843: /*****************************************************/
4844:
4845: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4841: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4842:
4843: /*****************************************************/
4844:
4845: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4846: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
4847:
4848: --lock rows
4849: OPEN cust_accounts;
4842:
4843: /*****************************************************/
4844:
4845: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4846: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
4847:
4848: --lock rows
4849: OPEN cust_accounts;
4850: CLOSE cust_accounts;
4872: AND status = 'D';
4873:
4874: g_count := sql%rowcount;
4875:
4876: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4877: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4878:
4879: /*****************************************************/
4880:
4873:
4874: g_count := sql%rowcount;
4875:
4876: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4877: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4878:
4879: /*****************************************************/
4880:
4881: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4877: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4878:
4879: /*****************************************************/
4880:
4881: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4882: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
4883:
4884: --lock rows
4885: OPEN cust_rel1;
4878:
4879: /*****************************************************/
4880:
4881: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4882: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
4883:
4884: --lock rows
4885: OPEN cust_rel1;
4886: CLOSE cust_rel1;
4899: AND status = 'D';
4900:
4901: g_count := sql%rowcount;
4902:
4903: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4904: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4905:
4906: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4907: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
4900:
4901: g_count := sql%rowcount;
4902:
4903: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4904: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4905:
4906: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4907: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
4908:
4902:
4903: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4904: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4905:
4906: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4907: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
4908:
4909: --lock rows
4910: OPEN cust_rel2;
4903: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4904: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4905:
4906: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4907: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
4908:
4909: --lock rows
4910: OPEN cust_rel2;
4911: CLOSE cust_rel2;
4923: AND status = 'D';
4924:
4925: g_count := sql%rowcount;
4926:
4927: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4928: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4929:
4930: /************** account site level delete ************/
4931:
4924:
4925: g_count := sql%rowcount;
4926:
4927: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4928: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4929:
4930: /************** account site level delete ************/
4931:
4932: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4928: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4929:
4930: /************** account site level delete ************/
4931:
4932: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4933: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
4934:
4935: OPEN cust_receipt_methods_site;
4936: CLOSE cust_receipt_methods_site;
4929:
4930: /************** account site level delete ************/
4931:
4932: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4933: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
4934:
4935: OPEN cust_receipt_methods_site;
4936: CLOSE cust_receipt_methods_site;
4937:
4947: AND m.delete_duplicate_flag = 'Y');
4948:
4949: g_count := sql%rowcount;
4950:
4951: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4952: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4953:
4954: /************** account level delete ************/
4955:
4948:
4949: g_count := sql%rowcount;
4950:
4951: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4952: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4953:
4954: /************** account level delete ************/
4955:
4956: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4952: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4953:
4954: /************** account level delete ************/
4955:
4956: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4957: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
4958:
4959: OPEN cust_receipt_methods_acct;
4960: CLOSE cust_receipt_methods_acct;
4953:
4954: /************** account level delete ************/
4955:
4956: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4957: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
4958:
4959: OPEN cust_receipt_methods_acct;
4960: CLOSE cust_receipt_methods_acct;
4961:
4974: AND status <> 'D' );
4975:
4976: g_count := sql%rowcount;
4977:
4978: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4979: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4980:
4981: arp_message.set_line( 'ARP_CMERGE_ARCUS.delete_rows()-' );
4982:
4975:
4976: g_count := sql%rowcount;
4977:
4978: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4979: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4980:
4981: arp_message.set_line( 'ARP_CMERGE_ARCUS.delete_rows()-' );
4982:
4983: EXCEPTION
4977:
4978: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4979: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4980:
4981: arp_message.set_line( 'ARP_CMERGE_ARCUS.delete_rows()-' );
4982:
4983: EXCEPTION
4984:
4985: WHEN OTHERS THEN
4982:
4983: EXCEPTION
4984:
4985: WHEN OTHERS THEN
4986: arp_message.set_error( 'ARP_CMERGE_ARCUS.delete_rows' );
4987: RAISE;
4988:
4989: END delete_rows;
4990:
5361: l_error_text VARCHAR2(2000); --Bug 11824312
5362:
5363: BEGIN
5364:
5365: arp_message.set_line( 'ARP_CMERGE_ARCUS.create_same_sites()+' );
5366:
5367: OPEN sites_need_to_create;
5368: LOOP
5369: FETCH sites_need_to_create INTO
5445:
5446: --Need to create new party site.
5447: IF l_exist = 'N' THEN
5448:
5449: arp_message.set_line('create_same_sites:create_party_site');
5450:
5451: --Need to create new party site.
5452: --Build party site records
5453:
5465: x_msg_data => x_msg_data );
5466:
5467: IF x_msg_count = 1 THEN
5468: x_msg_data := x_msg_data || '**GET_CURRENT_PARTY_SITE**';
5469: arp_message.set_line(
5470: 'do_merge_contacts:get_current_party_site ERROR ' ||
5471: x_msg_data );
5472: ELSIF x_msg_count > 1 THEN
5473:
5473:
5474: FOR x IN 1..x_msg_count LOOP
5475: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
5476: x_msg_data := x_msg_data || '**GET_CURRENT_PARTY_SITE**';
5477: arp_message.set_line(
5478: 'do_merge_contacts:get_current_party_site ERROR ' ||
5479: x_msg_data );
5480: END LOOP;
5481: END IF;
5523:
5524: --Handle error message
5525: IF x_msg_count = 1 THEN
5526: x_msg_data := x_msg_data || '**CREATE_PARTY_SITE**';
5527: arp_message.set_line(
5528: 'create_same_sites:create_party_site ERROR '||
5529: x_msg_data);
5530: ELSIF x_msg_count > 1 THEN
5531:
5531:
5532: FOR x IN 1..x_msg_count LOOP
5533: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
5534: x_msg_data := x_msg_data || '**CREATE_PARTY_SITE**';
5535: arp_message.set_line(
5536: 'create_same_sites:create_party_site ERROR ' ||
5537: x_msg_data );
5538: END LOOP;
5539: END IF;
5591: /*--Commented out for bug 4492628
5592: IF l_create_party_site_use = 'Y' THEN
5593: BEGIN
5594:
5595: arp_message.set_line('create_same_sites:create_party_site_use');
5596:
5597: -- Build party site use record.
5598:
5599: select party_site_use_id into l_duplicate_party_site_use_id
5625: --Handle error message.
5626:
5627: IF x_msg_count = 1 THEN
5628: x_msg_data := x_msg_data || '**CREATE_PARTY_SITE_USE**';
5629: arp_message.set_line( 'create_same_sites:create_party_site_use ERROR '|| x_msg_data);
5630: ELSIF x_msg_count > 1 THEN
5631:
5632: FOR x IN 1..x_msg_count LOOP
5633: x_msg_data := FND_MSG_PUB.GET(p_encoded => fnd_api.g_false);
5631:
5632: FOR x IN 1..x_msg_count LOOP
5633: x_msg_data := FND_MSG_PUB.GET(p_encoded => fnd_api.g_false);
5634: x_msg_data := x_msg_data || '**CREATE_PARTY_SITE_USE**';
5635: arp_message.set_line( 'create_same_sites:create_party_site_use ERROR ' || x_msg_data );
5636: END LOOP;
5637: END IF;
5638:
5639: -- After call create_* API, if return_status is not
5654: END IF;
5655: -- END of create_party_site_use
5656: */--Commented out for bug 4492628
5657:
5658: arp_message.set_line('create_same_sites:create_account_site');
5659:
5660: --Create account site.
5661: --Build account site records
5662: SELECT --Bug:2098728 obsoleted wh_update_date,
5815:
5816: --Handle error message
5817: IF x_msg_count = 1 THEN
5818: x_msg_data := x_msg_data || '**CREATE_ACCOUNT_SITE**';
5819: arp_message.set_line(
5820: 'create_same_sites:create_account_site ERROR '||
5821: x_msg_data);
5822: ELSIF x_msg_count > 1 THEN
5823:
5823:
5824: FOR x IN 1..x_msg_count LOOP
5825: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
5826: x_msg_data := x_msg_data || '**CREATE_ACCOUNT_SITE**';
5827: arp_message.set_line(
5828: 'create_same_sites:create_account_site ERROR ' ||
5829: x_msg_data );
5830: END LOOP;
5831: END IF;
5926:
5927: IF l_create_party_site_use = 'Y' THEN
5928: BEGIN
5929:
5930: arp_message.set_line('create_same_sites:create_party_site_use');
5931:
5932: -- Build party site use record.
5933:
5934: select party_site_use_id into l_duplicate_party_site_use_id
5960: --Handle error message.
5961:
5962: IF x_msg_count = 1 THEN
5963: x_msg_data := x_msg_data || '**CREATE_PARTY_SITE_USE**';
5964: arp_message.set_line( 'create_same_sites:create_party_site_use ERROR '|| x_msg_data);
5965: ELSIF x_msg_count > 1 THEN
5966:
5967: FOR x IN 1..x_msg_count LOOP
5968: x_msg_data := FND_MSG_PUB.GET(p_encoded => fnd_api.g_false);
5966:
5967: FOR x IN 1..x_msg_count LOOP
5968: x_msg_data := FND_MSG_PUB.GET(p_encoded => fnd_api.g_false);
5969: x_msg_data := x_msg_data || '**CREATE_PARTY_SITE_USE**';
5970: arp_message.set_line( 'create_same_sites:create_party_site_use ERROR ' || x_msg_data );
5971: END LOOP;
5972: END IF;
5973:
5974: -- After call create_* API, if return_status is not
5993: -----Bug:2376975 create only if duplicate_site_id <> -99
5994: IF l_create_acct_site_use = 'Y' AND l_duplicate_site_id <> -99 THEN
5995: BEGIN
5996:
5997: arp_message.set_line('create_same_sites:create_account_site_use');
5998:
5999:
6000: --Create customer profile and customer profile amts.
6001: --at site levels. profile is mandatory in account level,
6527: l_gen_loc := hz_mo_global_cache.get_auto_site_numbering(cust_site_use_rec.org_id);
6528: --Bug Fix 2183072
6529: EXCEPTION
6530: WHEN NO_DATA_FOUND THEN
6531: --arp_message.set_name( 'AR','AR_NO_ROW_IN_SYSTEM_PARAMETERS');
6532: RAISE fnd_api.g_exc_error;
6533: END ;
6534:
6535: IF l_gen_loc = 'N' THEN
6602:
6603: --Handle error message.
6604: IF x_msg_count = 1 THEN
6605: x_msg_data := x_msg_data || '**CREATE_ACCOUNT_SITE_USE**';
6606: arp_message.set_line(
6607: 'create_same_sites:create_acct_site_uses ERROR '||
6608: x_msg_data);
6609: ELSIF x_msg_count > 1 THEN
6610:
6610:
6611: FOR x IN 1..x_msg_count LOOP
6612: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
6613: x_msg_data := x_msg_data || '**CREATE_ACCOUNT_SITE_USE**';
6614: arp_message.set_line(
6615: 'create_same_sites:create_acct_site_uses ERROR ' ||
6616: x_msg_data );
6617: END LOOP;
6618: END IF;
6626:
6627: -- Find out the customer profile id.
6628: IF l_create_profile = 'Y' THEN
6629:
6630: arp_message.set_line('create_same_sites:create_profile_amount');
6631:
6632: SELECT cust_account_profile_id INTO l_to_cust_account_profile_id
6633: FROM hz_customer_profiles
6634: WHERE cust_account_id = l_customer_id
6737: x_msg_data => x_msg_data);
6738:
6739: IF x_msg_count = 1 THEN
6740: x_msg_data := x_msg_data || '**CREATE_CUST_PROF_AMT**';
6741: arp_message.set_line(
6742: 'create_same_sites:create_cust_prof_amt ERROR '||
6743: x_msg_data);
6744: ELSIF x_msg_count > 1 THEN
6745:
6745:
6746: FOR x IN 1..x_msg_count LOOP
6747: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
6748: x_msg_data := x_msg_data || '**CREATE_CUST_PROF_AMT**';
6749: arp_message.set_line(
6750: 'create_same_sites:create_cust_prof_amt ERROR ' ||
6751: x_msg_data );
6752: END LOOP;
6753: END IF;
6765: END IF; --there is profile exist for this site use.
6766:
6767: /* Bug:2241033; Creating Payment Method for customer site use */
6768: begin
6769: arp_message.set_line('create_same_sites:create_cust_site_payment_method');
6770: savepoint merge_from_pay_method_point;
6771:
6772: open merge_from_pay_method;
6773: loop
6828: exception
6829: when others then
6830: rollback to merge_from_pay_method_point;
6831:
6832: arp_message.set_line('create_same_sites:create_cust_site_payment_method error');
6833: arp_message.set_line('Error : '||SQLERRM);
6834: status := -1;
6835: l_error_text := substr(SQLERRM,1,2000); --Bug 11824312
6836: RAISE fnd_api.g_exc_error;
6829: when others then
6830: rollback to merge_from_pay_method_point;
6831:
6832: arp_message.set_line('create_same_sites:create_cust_site_payment_method error');
6833: arp_message.set_line('Error : '||SQLERRM);
6834: status := -1;
6835: l_error_text := substr(SQLERRM,1,2000); --Bug 11824312
6836: RAISE fnd_api.g_exc_error;
6837: end;
6893: CLOSE sites_need_to_create;
6894:
6895: status := 0;
6896:
6897: arp_message.set_line( 'ARP_CMERGE_ARCUS.create_same_sites()-' );
6898:
6899: EXCEPTION
6900:
6901: WHEN OTHERS THEN
6898:
6899: EXCEPTION
6900:
6901: WHEN OTHERS THEN
6902: arp_message.set_error( 'ARP_CMERGE_ARCUS.create_same_sites' );
6903: IF l_error_text IS NULL THEN
6904: error_text := substr(SQLERRM,1,2000);
6905: ELSE
6906: error_text := l_error_text;
6943:
6944:
6945: BEGIN
6946:
6947: arp_message.set_line( 'ARP_CMERGE_ARCUS.Merge_History()+' );
6948:
6949: ---------Insert into HZ_CUST_ACCOUNTS_M--------------
6950: INSERT INTO HZ_CUST_ACCOUNTS_M(
6951: customer_merge_header_id,
7195: and cm.set_number = set_num) , HZ_CUST_ACCOUNTS c
7196: WHERE c.cust_account_id = duplicate_id
7197: AND duplicate_id <> customer_id;
7198:
7199: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCOUNTS_M');
7200:
7201:
7202:
7203: ---------Insert into hz_cust_account_roles_m--------------
7357: WHERE ( ar.cust_account_id = duplicate_id OR
7358: ar.cust_acct_site_id = duplicate_address_id )
7359: AND ar.role_type = 'CONTACT';
7360:
7361: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCOUNT_ROLES_M');
7362:
7363: ---------Insert into hz_customer_profiles_m--------------
7364: --Because profiles can be set up at acct and site use level, we need to select
7365: --distict duplicate and customer_merge_header_id if acct with multiple sites
7594: and cm.duplicate_id <> cm.customer_id ), hz_customer_profiles cp
7595: WHERE ( cp.cust_account_id = duplicate_id AND cp.site_use_id is NULL)
7596: OR (cp.site_use_id = duplicate_site_id );
7597:
7598: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUSTOMER_PROFILES_M');
7599:
7600: ---------Insert into hz_cust_profile_amts_m--------------
7601: --Because profiles can be set up at acct and site use level, we need to select
7602: --distict duplicate and customer_merge_header_id if acct with multiple sites
7780: and cm.set_number = set_num ),hz_cust_profile_amts pa
7781: WHERE (pa.cust_account_id = duplicate_id and pa.site_use_id is NULL)
7782: OR (pa.site_use_id = duplicate_site_id);
7783:
7784: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_PROFILE_AMTS_M');
7785:
7786: ---------Insert into hz_cust_acct_sites_all_m--------------
7787: INSERT INTO hz_cust_acct_sites_all_m(
7788: customer_merge_header_id,
7950: and cm.set_number = set_num ) m,hz_cust_acct_sites_all acs
7951: WHERE acs.cust_acct_site_id = duplicate_address_id
7952: AND acs.org_id = m.org_id ;
7953:
7954: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCT_SITES_ALL_M');
7955:
7956: ---------Insert into hz_cust_site_uses_all_m--------------
7957: INSERT INTO hz_cust_site_uses_all_m(
7958: customer_merge_header_id,
8202: and cm.set_number = set_num ) m,hz_cust_site_uses_all su --SSUptake
8203: WHERE su.site_use_id = duplicate_site_id
8204: AND su.org_id = m.org_id; --SSUptake
8205:
8206: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_SITE_USES_ALL_M');
8207:
8208: ---------Insert into hz_cust_acct_relate_all_m--------------
8209: INSERT INTO hz_cust_acct_relate_all_m(
8210: customer_merge_header_id,
8298: WHERE ( yt.cust_account_id = duplicate_id OR
8299: yt.related_cust_account_id = duplicate_id )
8300: AND m.org_id = yt.org_id ; --SSUptake
8301:
8302: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCT_RELATE_ALL_M');
8303:
8304:
8305: ---After storing the merge data sucessfully initialize the status to 0
8306: status := 0;
8304:
8305: ---After storing the merge data sucessfully initialize the status to 0
8306: status := 0;
8307:
8308: arp_message.set_line( 'ARP_CMERGE_ARCUS.Merge_History()-' );
8309:
8310: EXCEPTION
8311: WHEN NO_DATA_FOUND THEN
8312: NULL;
8311: WHEN NO_DATA_FOUND THEN
8312: NULL;
8313:
8314: WHEN DUP_VAL_ON_INDEX THEN
8315: arp_message.set_line('Index Violation: ' || SQLERRM );
8316:
8317:
8318: WHEN OTHERS THEN
8319: arp_message.set_error('ARP_CMERGE_ARCUS.Merge_History');
8315: arp_message.set_line('Index Violation: ' || SQLERRM );
8316:
8317:
8318: WHEN OTHERS THEN
8319: arp_message.set_error('ARP_CMERGE_ARCUS.Merge_History');
8320: status := -1;
8321:
8322:
8323: END merge_history;