[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 1610924: comment out the following code because of new om enhancement:
490: allow merging all of the site uses of a customer.
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 1610924: comment out the following code because of new om enhancement:
490: allow merging all of the site uses of a customer.
491:
491:
492: --Inactivate non-transaction site uses if no active ship_to,
493: --bill_to or market site uses remain for the account site.
494:
495: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
496: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
497:
498: UPDATE HZ_CUST_SITE_USES su1
499: SET status = 'I',
492: --Inactivate non-transaction site uses if no active ship_to,
493: --bill_to or market site uses remain for the account site.
494:
495: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
496: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
497:
498: UPDATE HZ_CUST_SITE_USES su1
499: SET status = 'I',
500: last_update_date = sysdate,
521: AND status = 'A' );
522:
523: g_count := sql%rowcount;
524:
525: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
526: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
527:
528: **/
529:
522:
523: g_count := sql%rowcount;
524:
525: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
526: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
527:
528: **/
529:
530: /*************** 'delete' mode ***************/
531:
532: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
533: --Physically delete them after merge.
534:
535: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
536: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
537:
538: UPDATE HZ_CUST_SITE_USES_ALL yt
539: SET status = 'D'
532: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
533: --Physically delete them after merge.
534:
535: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
536: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
537:
538: UPDATE HZ_CUST_SITE_USES_ALL yt
539: SET status = 'D'
540: WHERE EXISTS (
548: AND m.delete_duplicate_flag = 'Y' );
549:
550: g_count := sql%rowcount;
551:
552: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
553: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
554:
555: /** Bug 1610924: comment out the following code because of new om enhancement:
556: allow merging all of the site uses of a customer.
549:
550: g_count := sql%rowcount;
551:
552: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
553: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
554:
555: /** Bug 1610924: comment out the following code because of new om enhancement:
556: allow merging all of the site uses of a customer.
557:
557:
558: --'Delete' non-transaction site uses if no ship_to,
559: --bill_to, market site uses remain for the account site.
560:
561: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
562: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
563:
564: UPDATE HZ_CUST_SITE_USES su1
565: SET status = 'D'
558: --'Delete' non-transaction site uses if no ship_to,
559: --bill_to, market site uses remain for the account site.
560:
561: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
562: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
563:
564: UPDATE HZ_CUST_SITE_USES su1
565: SET status = 'D'
566: WHERE cust_acct_site_id IN (
580: AND status <> 'D' );
581:
582: g_count := sql%rowcount;
583:
584: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
585: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
586: **/
587:
588: END IF;
581:
582: g_count := sql%rowcount;
583:
584: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
585: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
586: **/
587:
588: END IF;
589: open c2;
604: x_msg_data =>x_msg_data);
605: --Handle error message
606: IF x_msg_count = 1 THEN
607: x_msg_data := x_msg_data || '**remap internal id**';
608: arp_message.set_line(
609: 'MOSR:remap internal id ERROR '||
610: x_msg_data);
611: ELSIF x_msg_count > 1 THEN
612:
612:
613: FOR x IN 1..x_msg_count LOOP
614: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
615: x_msg_data := x_msg_data || '**remap internal id**';
616: arp_message.set_line(
617: 'MOSR:remap internal id ERROR ' ||
618: x_msg_data );
619: END LOOP;
620: END IF;
625:
626: end loop;
627: close c2;
628:
629: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_SU()-' );
630:
631: EXCEPTION
632:
633: WHEN OTHERS THEN
630:
631: EXCEPTION
632:
633: WHEN OTHERS THEN
634: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_SU' );
635: RAISE;
636:
637: END ra_su;
638:
738:
739:
740: BEGIN
741:
742: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_ADDR()+' );
743:
744: /* locking tables by opening and closing cursors */
745: IF process_mode = 'LOCK' THEN
746:
743:
744: /* locking tables by opening and closing cursors */
745: IF process_mode = 'LOCK' THEN
746:
747: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
748: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
749:
750: OPEN c1;
751: CLOSE c1;
744: /* locking tables by opening and closing cursors */
745: IF process_mode = 'LOCK' THEN
746:
747: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
748: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
749:
750: OPEN c1;
751: CLOSE c1;
752:
756: ELSE
757:
758: /*************** 'inactivate' mode ***************/
759:
760: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
761: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
762:
763: --inactivate customer account site
764:
757:
758: /*************** 'inactivate' mode ***************/
759:
760: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
761: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
762:
763: --inactivate customer account site
764:
765: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
788: AND su.status = 'A' );
789:
790: g_count := sql%rowcount;
791:
792: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
793: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
794:
795: --Update bill_to_flag
796:
789:
790: g_count := sql%rowcount;
791:
792: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
793: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
794:
795: --Update bill_to_flag
796:
797: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
793: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
794:
795: --Update bill_to_flag
796:
797: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
798: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
799:
800: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
801: SET bill_to_flag = null,
794:
795: --Update bill_to_flag
796:
797: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
798: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
799:
800: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
801: SET bill_to_flag = null,
802: last_update_date = sysdate,
825: AND NVL(bill_to_flag, 'N') <> 'N';
826:
827: g_count := sql%rowcount;
828:
829: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
830: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
831:
832: --Update ship_to_flag
833:
826:
827: g_count := sql%rowcount;
828:
829: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
830: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
831:
832: --Update ship_to_flag
833:
834: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
830: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
831:
832: --Update ship_to_flag
833:
834: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
835: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
836:
837: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
838: SET ship_to_flag = null,
831:
832: --Update ship_to_flag
833:
834: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
835: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
836:
837: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
838: SET ship_to_flag = null,
839: last_update_date = sysdate,
862: AND NVL(ship_to_flag, 'N') <> 'N';
863:
864: g_count := sql%rowcount;
865:
866: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
867: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
868:
869: --Update market_flag
870:
863:
864: g_count := sql%rowcount;
865:
866: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
867: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
868:
869: --Update market_flag
870:
871: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
867: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
868:
869: --Update market_flag
870:
871: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
872: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
873:
874: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
875: SET market_flag = null,
868:
869: --Update market_flag
870:
871: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
872: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
873:
874: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
875: SET market_flag = null,
876: last_update_date = sysdate,
899: AND NVL(market_flag, 'N') <> 'N';
900:
901: g_count := sql%rowcount;
902:
903: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
904: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
905:
906: /*************** 'delete' mode ***************/
907:
900:
901: g_count := sql%rowcount;
902:
903: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
904: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
905:
906: /*************** 'delete' mode ***************/
907:
908: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
907:
908: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
909: --Physically delete them after merge.
910:
911: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
912: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
913:
914: --'delete' customer account site
915:
908: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
909: --Physically delete them after merge.
910:
911: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
912: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
913:
914: --'delete' customer account site
915:
916: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
932: AND su.status <> 'D' );
933:
934: g_count := sql%rowcount;
935:
936: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
937: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
938:
939: END IF;
940:
933:
934: g_count := sql%rowcount;
935:
936: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
937: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
938:
939: END IF;
940:
941: open c3;
956: x_msg_data =>x_msg_data);
957: --Handle error message
958: IF x_msg_count = 1 THEN
959: x_msg_data := x_msg_data || '**remap internal id**';
960: arp_message.set_line(
961: 'MOSR:remap internal id ERROR '||
962: x_msg_data);
963: ELSIF x_msg_count > 1 THEN
964:
964:
965: FOR x IN 1..x_msg_count LOOP
966: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
967: x_msg_data := x_msg_data || '**remap internal id**';
968: arp_message.set_line(
969: 'MOSR:remap internal id ERROR ' ||
970: x_msg_data );
971: END LOOP;
972: END IF;
978: end loop;
979: close c3;
980:
981:
982: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_ADDR()-' );
983:
984: EXCEPTION
985:
986: WHEN OTHERS THEN
983:
984: EXCEPTION
985:
986: WHEN OTHERS THEN
987: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_ADDR' );
988: RAISE;
989:
990: END ra_addr;
991:
1090:
1091:
1092: BEGIN
1093:
1094: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CUST()+' );
1095:
1096: /* locking tables by opening and closing cursors */
1097: IF process_mode = 'LOCK' THEN
1098:
1095:
1096: /* locking tables by opening and closing cursors */
1097: IF process_mode = 'LOCK' THEN
1098:
1099: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
1100: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1101:
1102: OPEN c1;
1103: CLOSE c1;
1096: /* locking tables by opening and closing cursors */
1097: IF process_mode = 'LOCK' THEN
1098:
1099: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
1100: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1101:
1102: OPEN c1;
1103: CLOSE c1;
1104:
1108: ELSE
1109:
1110: /*************** 'inactivate' mode ***************/
1111:
1112: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1113: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1114:
1115: --inactivate customer account
1116:
1109:
1110: /*************** 'inactivate' mode ***************/
1111:
1112: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1113: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1114:
1115: --inactivate customer account
1116:
1117: UPDATE HZ_CUST_ACCOUNTS yt
1138: AND addr.status = 'A' );
1139:
1140: g_count := sql%rowcount;
1141:
1142: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1143: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1144:
1145: /*************** 'delete' mode ***************/
1146:
1139:
1140: g_count := sql%rowcount;
1141:
1142: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1143: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1144:
1145: /*************** 'delete' mode ***************/
1146:
1147: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
1146:
1147: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
1148: --Physically delete them after merge.
1149:
1150: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1151: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1152:
1153: --'delete' customer account
1154:
1147: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
1148: --Physically delete them after merge.
1149:
1150: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1151: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1152:
1153: --'delete' customer account
1154:
1155: UPDATE HZ_CUST_ACCOUNTS yt
1169: AND addr.status <> 'D' );
1170:
1171: g_count := sql%rowcount;
1172:
1173: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1174: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1175:
1176: END IF;
1177:
1170:
1171: g_count := sql%rowcount;
1172:
1173: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1174: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1175:
1176: END IF;
1177:
1178: open c3;
1193: x_msg_data =>x_msg_data);
1194: --Handle error message
1195: IF x_msg_count = 1 THEN
1196: x_msg_data := x_msg_data || '**remap internal id**';
1197: arp_message.set_line(
1198: 'MOSR:remap internal id ERROR '||
1199: x_msg_data);
1200: ELSIF x_msg_count > 1 THEN
1201:
1201:
1202: FOR x IN 1..x_msg_count LOOP
1203: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1204: x_msg_data := x_msg_data || '**remap internal id**';
1205: arp_message.set_line(
1206: 'MOSR:remap internal id ERROR ' ||
1207: x_msg_data );
1208: END LOOP;
1209: END IF;
1214:
1215: end loop;
1216: close c3;
1217:
1218: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CUST()-' );
1219:
1220: EXCEPTION
1221:
1222: WHEN OTHERS THEN
1219:
1220: EXCEPTION
1221:
1222: WHEN OTHERS THEN
1223: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_CUST' );
1224: RAISE;
1225:
1226: END ra_cust;
1227:
1361: --End of Bug No: 4527935
1362:
1363: BEGIN
1364:
1365: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CR()+' );
1366:
1367: /* locking tables by opening and closing cursors */
1368: IF process_mode = 'LOCK' THEN
1369:
1366:
1367: /* locking tables by opening and closing cursors */
1368: IF process_mode = 'LOCK' THEN
1369:
1370: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
1371: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1372:
1373: OPEN c1;
1374: CLOSE c1;
1367: /* locking tables by opening and closing cursors */
1368: IF process_mode = 'LOCK' THEN
1369:
1370: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
1371: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1372:
1373: OPEN c1;
1374: CLOSE c1;
1375:
1380:
1381: --Start of Bug No: 4558774
1382:
1383: /************** from account update ************/
1384: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1385: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1386:
1387: g_count := 0;
1388: FOR from_rec in c_from_rel_cust_id
1381: --Start of Bug No: 4558774
1382:
1383: /************** from account update ************/
1384: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1385: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1386:
1387: g_count := 0;
1388: FOR from_rec in c_from_rel_cust_id
1389: LOOP
1407: CLOSE c_acct_num;
1408: OPEN c_acct_num(l_to_cust_account_id);
1409: FETCH c_acct_num into l_to_acct_no;
1410: CLOSE c_acct_num;
1411: arp_message.set_name( 'AR', 'HZ_ACCT_MERGE_REL_WARNING' );
1412: arp_message.set_token( 'MERGE_FROM_ACCT',l_from_acct_no, FALSE );
1413: arp_message.set_token( 'MERGE_TO_ACCT',l_to_acct_no, FALSE );
1414: ELSE -- blend the two relationships
1415: IF(l_to_bill_to_flag <> 'Y' AND l_to_bill_to_flag <> from_rec.bill_to_flag) THEN
1408: OPEN c_acct_num(l_to_cust_account_id);
1409: FETCH c_acct_num into l_to_acct_no;
1410: CLOSE c_acct_num;
1411: arp_message.set_name( 'AR', 'HZ_ACCT_MERGE_REL_WARNING' );
1412: arp_message.set_token( 'MERGE_FROM_ACCT',l_from_acct_no, FALSE );
1413: arp_message.set_token( 'MERGE_TO_ACCT',l_to_acct_no, FALSE );
1414: ELSE -- blend the two relationships
1415: IF(l_to_bill_to_flag <> 'Y' AND l_to_bill_to_flag <> from_rec.bill_to_flag) THEN
1416: l_to_bill_to_flag := from_rec.bill_to_flag;
1409: FETCH c_acct_num into l_to_acct_no;
1410: CLOSE c_acct_num;
1411: arp_message.set_name( 'AR', 'HZ_ACCT_MERGE_REL_WARNING' );
1412: arp_message.set_token( 'MERGE_FROM_ACCT',l_from_acct_no, FALSE );
1413: arp_message.set_token( 'MERGE_TO_ACCT',l_to_acct_no, FALSE );
1414: ELSE -- blend the two relationships
1415: IF(l_to_bill_to_flag <> 'Y' AND l_to_bill_to_flag <> from_rec.bill_to_flag) THEN
1416: l_to_bill_to_flag := from_rec.bill_to_flag;
1417: l_update_flag := true;
1469: CLOSE c_acct_num;
1470: OPEN c_acct_num(l_to_related_cust_account_id);
1471: FETCH c_acct_num into l_to_acct_no;
1472: CLOSE c_acct_num;
1473: arp_message.set_name( 'AR', 'HZ_ACCT_MERGE_REL_WARNING' );
1474: arp_message.set_token( 'MERGE_FROM_ACCT',l_from_acct_no, FALSE );
1475: arp_message.set_token( 'MERGE_TO_ACCT',l_to_acct_no, FALSE );
1476: ELSE -- blend the two relationships
1477: IF(l_to_bill_to_flag <> 'Y' AND l_to_bill_to_flag <> from_rec.bill_to_flag) THEN
1470: OPEN c_acct_num(l_to_related_cust_account_id);
1471: FETCH c_acct_num into l_to_acct_no;
1472: CLOSE c_acct_num;
1473: arp_message.set_name( 'AR', 'HZ_ACCT_MERGE_REL_WARNING' );
1474: arp_message.set_token( 'MERGE_FROM_ACCT',l_from_acct_no, FALSE );
1475: arp_message.set_token( 'MERGE_TO_ACCT',l_to_acct_no, FALSE );
1476: ELSE -- blend the two relationships
1477: IF(l_to_bill_to_flag <> 'Y' AND l_to_bill_to_flag <> from_rec.bill_to_flag) THEN
1478: l_to_bill_to_flag := from_rec.bill_to_flag;
1471: FETCH c_acct_num into l_to_acct_no;
1472: CLOSE c_acct_num;
1473: arp_message.set_name( 'AR', 'HZ_ACCT_MERGE_REL_WARNING' );
1474: arp_message.set_token( 'MERGE_FROM_ACCT',l_from_acct_no, FALSE );
1475: arp_message.set_token( 'MERGE_TO_ACCT',l_to_acct_no, FALSE );
1476: ELSE -- blend the two relationships
1477: IF(l_to_bill_to_flag <> 'Y' AND l_to_bill_to_flag <> from_rec.bill_to_flag) THEN
1478: l_to_bill_to_flag := from_rec.bill_to_flag;
1479: l_update_flag := true;
1505: AND yt.related_cust_account_id = from_rec.related_cust_account_id
1506: AND yt.org_id = from_rec.org_id;
1507: g_count := g_count + sql%rowcount;
1508: END LOOP;
1509: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1510: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1511:
1512: /*************** 'inactivate' mode *************/
1513: --inactivate self relationships
1506: AND yt.org_id = from_rec.org_id;
1507: g_count := g_count + sql%rowcount;
1508: END LOOP;
1509: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1510: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1511:
1512: /*************** 'inactivate' mode *************/
1513: --inactivate self relationships
1514: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1510: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1511:
1512: /*************** 'inactivate' mode *************/
1513: --inactivate self relationships
1514: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1515: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1516:
1517: --Start bug 7192302
1518: /*UPDATE HZ_CUST_ACCT_RELATE_ALL yt
1511:
1512: /*************** 'inactivate' mode *************/
1513: --inactivate self relationships
1514: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1515: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1516:
1517: --Start bug 7192302
1518: /*UPDATE HZ_CUST_ACCT_RELATE_ALL yt
1519: SET status = 'I',
1586: AND M.DELETE_DUPLICATE_FLAG = 'N' );
1587:
1588: g_count :=g_count + sql%rowcount;
1589: --End bug 7192302
1590: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1591: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1592:
1593: --End of Bug No: 4527935
1594: /*************** for 'delete' mode *************/
1587:
1588: g_count :=g_count + sql%rowcount;
1589: --End bug 7192302
1590: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1591: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1592:
1593: --End of Bug No: 4527935
1594: /*************** for 'delete' mode *************/
1595: --delete those relationships that could not be updated
1599:
1600: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
1601: --Physically delete them after merge.
1602:
1603: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1604: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1605:
1606: /************** from account update ************/
1607:
1600: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
1601: --Physically delete them after merge.
1602:
1603: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1604: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1605:
1606: /************** from account update ************/
1607:
1608: UPDATE HZ_CUST_ACCT_RELATE_ALL yt --SSUptake
1657: AND m.org_id = yt.org_id); --SSUptake
1658:
1659: g_count := sql%rowcount;
1660:
1661: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1662: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1663:
1664: /************** to account update ************/
1665:
1658:
1659: g_count := sql%rowcount;
1660:
1661: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1662: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1663:
1664: /************** to account update ************/
1665:
1666: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1662: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1663:
1664: /************** to account update ************/
1665:
1666: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1667: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1668:
1669: UPDATE HZ_CUST_ACCT_RELATE_ALL yt
1670: SET status = 'D'
1663:
1664: /************** to account update ************/
1665:
1666: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1667: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
1668:
1669: UPDATE HZ_CUST_ACCT_RELATE_ALL yt
1670: SET status = 'D'
1671: WHERE
1718: AND m.set_number = set_num );
1719:
1720: g_count := sql%rowcount;
1721:
1722: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1723: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1724:
1725: END IF;
1726:
1719:
1720: g_count := sql%rowcount;
1721:
1722: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1723: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1724:
1725: END IF;
1726:
1727: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CR()-' );
1723: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1724:
1725: END IF;
1726:
1727: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CR()-' );
1728:
1729: EXCEPTION
1730:
1731: WHEN OTHERS THEN
1728:
1729: EXCEPTION
1730:
1731: WHEN OTHERS THEN
1732: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_CR' );
1733: RAISE;
1734:
1735: END ra_cr;
1736:
1827: end_date end_date_tab;
1828: new_date date;
1829: BEGIN
1830:
1831: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CRM()+' );
1832:
1833: /* locking tables by opening and closing cursors */
1834: IF process_mode = 'LOCK' THEN
1835:
1832:
1833: /* locking tables by opening and closing cursors */
1834: IF process_mode = 'LOCK' THEN
1835:
1836: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1837: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
1838:
1839: OPEN c1;
1840: CLOSE c1;
1833: /* locking tables by opening and closing cursors */
1834: IF process_mode = 'LOCK' THEN
1835:
1836: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1837: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
1838:
1839: OPEN c1;
1840: CLOSE c1;
1841:
1845: ELSE
1846:
1847: /************** account site level inactivate ************/
1848:
1849: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1850: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
1851:
1852: --- bug 3786802
1853:
1846:
1847: /************** account site level inactivate ************/
1848:
1849: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1850: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
1851:
1852: --- bug 3786802
1853:
1854: SELECT distinct CUSTOMER_MERGE_HEADER_ID,
2032: WHERE CUST_RECEIPT_METHOD_ID = receipt_id(i);
2033:
2034: g_count := sql%rowcount;
2035:
2036: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2037: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2038:
2039: /************** account level inactivate ************/
2040:
2033:
2034: g_count := sql%rowcount;
2035:
2036: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2037: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2038:
2039: /************** account level inactivate ************/
2040:
2041: /* arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2037: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2038:
2039: /************** account level inactivate ************/
2040:
2041: /* arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2042: | arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
2043:
2044: |
2045: | UPDATE RA_CUST_RECEIPT_METHODS yt
2038:
2039: /************** account level inactivate ************/
2040:
2041: /* arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2042: | arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
2043:
2044: |
2045: | UPDATE RA_CUST_RECEIPT_METHODS yt
2046: | set end_date = sysdate,
2066: | AND acct.status = 'A' );
2067:
2068: | g_count := sql%rowcount;
2069:
2070: | arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2071: | arp_message.set_token( 'NUM_ROWS', to_char(g_count) ); */
2072:
2073: END IF;
2074:
2067:
2068: | g_count := sql%rowcount;
2069:
2070: | arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2071: | arp_message.set_token( 'NUM_ROWS', to_char(g_count) ); */
2072:
2073: END IF;
2074:
2075: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CRM()-' );
2071: | arp_message.set_token( 'NUM_ROWS', to_char(g_count) ); */
2072:
2073: END IF;
2074:
2075: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CRM()-' );
2076:
2077: EXCEPTION
2078:
2079: WHEN OTHERS THEN
2076:
2077: EXCEPTION
2078:
2079: WHEN OTHERS THEN
2080: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_CRM' );
2081: RAISE;
2082:
2083: END ra_crm;
2084:
2139: AND duplicate_id <> customer_id;
2140:
2141: BEGIN
2142:
2143: arp_message.set_line( 'ARP_CMERGE_ARCUS.copy_contacts()+' );
2144:
2145: OPEN accounts;
2146: LOOP
2147: FETCH accounts INTO from_account_id, to_account_id;
2147: FETCH accounts INTO from_account_id, to_account_id;
2148: EXIT WHEN accounts%NOTFOUND;
2149:
2150: /**
2151: arp_message.set_line(
2152: 'ARP_CMERGE_ARCUS:copy_contacts():from_account_id ' ||
2153: from_account_id );
2154: arp_message.set_line(
2155: 'ARP_CMERGE_ARCUS:copy_contacts():to_account_id ' ||
2150: /**
2151: arp_message.set_line(
2152: 'ARP_CMERGE_ARCUS:copy_contacts():from_account_id ' ||
2153: from_account_id );
2154: arp_message.set_line(
2155: 'ARP_CMERGE_ARCUS:copy_contacts():to_account_id ' ||
2156: to_account_id );
2157: **/
2158:
2164: FROM hz_cust_accounts
2165: WHERE cust_account_id = to_account_id;
2166:
2167: /**
2168: arp_message.set_line(
2169: 'ARP_CMERGE_ARCUS:copy_contacts():from_party_id '||
2170: from_party_id );
2171: arp_message.set_line(
2172: 'ARP_CMERGE_ARCUS:copy_contacts():to_party_id '||
2167: /**
2168: arp_message.set_line(
2169: 'ARP_CMERGE_ARCUS:copy_contacts():from_party_id '||
2170: from_party_id );
2171: arp_message.set_line(
2172: 'ARP_CMERGE_ARCUS:copy_contacts():to_party_id '||
2173: to_party_id );
2174: **/
2175:
2180:
2181: IF to_party_id = from_party_id THEN
2182:
2183: /**
2184: arp_message.set_line (
2185: 'ARP_CMERGE_ARCUS:copy_contacts(): ' ||
2186: 'merge_from party and merge_to party are same. ' ||
2187: 'Do not need copy org contact and contact points in account level.' );
2188: **/
2209:
2210: END LOOP;
2211: CLOSE accounts;
2212:
2213: arp_message.set_line( 'ARP_CMERGE_ARCUS.copy_contacts()-' );
2214:
2215: EXCEPTION
2216:
2217: WHEN OTHERS THEN
2214:
2215: EXCEPTION
2216:
2217: WHEN OTHERS THEN
2218: arp_message.set_error( 'ARP_CMERGE_ARCUS.copy_contacts' );
2219: RAISE;
2220:
2221: END copy_contacts;
2222:
2275: AND duplicate_address_id <> -1; --4693912
2276:
2277: BEGIN
2278:
2279: arp_message.set_line( 'ARP_CMERGE_ARCUS.copy_contacts_in_sites()+' );
2280:
2281: OPEN sites;
2282: LOOP
2283: FETCH sites INTO
2284: from_account_id, to_account_id, from_site_id, to_site_id;
2285: EXIT WHEN sites%NOTFOUND;
2286:
2287: /**
2288: arp_message.set_line(
2289: 'ARP_CMERGE_ARCUS:copy_contacts_in_sites():from_site_id ' ||
2290: from_site_id );
2291: arp_message.set_line(
2292: 'ARP_CMERGE_ARCUS:copy_contacts_in_sites():to_site_id ' ||
2287: /**
2288: arp_message.set_line(
2289: 'ARP_CMERGE_ARCUS:copy_contacts_in_sites():from_site_id ' ||
2290: from_site_id );
2291: arp_message.set_line(
2292: 'ARP_CMERGE_ARCUS:copy_contacts_in_sites():to_site_id ' ||
2293: to_site_id );
2294: **/
2295:
2301: FROM hz_cust_acct_sites
2302: WHERE cust_acct_site_id = to_site_id;
2303:
2304: /**
2305: arp_message.set_line(
2306: 'ARP_CMERGE_ARCUS:copy_contacts():from_party_site_id '||
2307: from_party_site_id );
2308: arp_message.set_line(
2309: 'ARP_CMERGE_ARCUS:copy_contacts():to_party_site_id '||
2304: /**
2305: arp_message.set_line(
2306: 'ARP_CMERGE_ARCUS:copy_contacts():from_party_site_id '||
2307: from_party_site_id );
2308: arp_message.set_line(
2309: 'ARP_CMERGE_ARCUS:copy_contacts():to_party_site_id '||
2310: to_party_site_id );
2311: **/
2312:
2317:
2318: IF to_party_site_id = from_party_site_id THEN
2319:
2320: /**
2321: arp_message.set_line (
2322: 'ARP_CMERGE_ARCUS:copy_contacts(): ' ||
2323: 'merge_from party and merge_to party site are same. ' ||
2324: 'Do not need copy contact points.' );
2325: **/
2336:
2337: END LOOP;
2338: CLOSE sites;
2339:
2340: arp_message.set_line( 'ARP_CMERGE_ARCUS.copy_contacts_in_sites()-' );
2341:
2342: EXCEPTION
2343:
2344: WHEN OTHERS THEN
2341:
2342: EXCEPTION
2343:
2344: WHEN OTHERS THEN
2345: arp_message.set_error( 'ARP_CMERGE_ARCUS.copy_contacts_in_sites' );
2346: RAISE;
2347:
2348: END copy_contacts_in_sites;
2349:
2419: ---Bug NO: 5067291
2420:
2421: BEGIN
2422:
2423: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CONT()+' );
2424:
2425: /* locking tables by opening and closing cursors */
2426: IF process_mode = 'LOCK' then
2427:
2424:
2425: /* locking tables by opening and closing cursors */
2426: IF process_mode = 'LOCK' then
2427:
2428: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
2429: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2430:
2431: OPEN c1;
2432: CLOSE c1;
2425: /* locking tables by opening and closing cursors */
2426: IF process_mode = 'LOCK' then
2427:
2428: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
2429: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2430:
2431: OPEN c1;
2432: CLOSE c1;
2433:
2438: OPEN c3;--Bug NO: 5067291
2439: FETCH c3 INTO l_party_id;
2440: IF c3%FOUND THEN
2441: /*********************Inactivate account site/account role...Bug No. 5067291*********/
2442: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2443: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2444: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2445: SET status = 'I',
2446: last_update_date = sysdate,
2439: FETCH c3 INTO l_party_id;
2440: IF c3%FOUND THEN
2441: /*********************Inactivate account site/account role...Bug No. 5067291*********/
2442: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2443: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2444: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2445: SET status = 'I',
2446: last_update_date = sysdate,
2447: last_updated_by = arp_standard.profile.user_id,
2452: program_update_date = sysdate
2453: WHERE party_id = l_party_id
2454: AND nvl(status,'A') ='A';
2455:
2456: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2457: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2458:
2459:
2460: g_count := sql%rowcount;
2453: WHERE party_id = l_party_id
2454: AND nvl(status,'A') ='A';
2455:
2456: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2457: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2458:
2459:
2460: g_count := sql%rowcount;
2461:
2458:
2459:
2460: g_count := sql%rowcount;
2461:
2462: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2463: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2464:
2465: END IF;
2466:
2459:
2460: g_count := sql%rowcount;
2461:
2462: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2463: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2464:
2465: END IF;
2466:
2467: /************** account site level update ************/
2465: END IF;
2466:
2467: /************** account site level update ************/
2468:
2469: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2470: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2471:
2472: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2473: SET (cust_account_id, cust_acct_site_id) = (
2466:
2467: /************** account site level update ************/
2468:
2469: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2470: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2471:
2472: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2473: SET (cust_account_id, cust_acct_site_id) = (
2474: SELECT MIN(m.customer_id), MIN(m.customer_address_id)
2495: AND party_id <> nvl(l_party_id,-99);---Bug No. 5067291
2496:
2497: g_count := sql%rowcount;
2498:
2499: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2500: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2501:
2502: /************** account level update ************/
2503:
2496:
2497: g_count := sql%rowcount;
2498:
2499: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2500: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2501:
2502: /************** account level update ************/
2503:
2504: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2500: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2501:
2502: /************** account level update ************/
2503:
2504: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2505: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2506:
2507: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2508: SET cust_account_id = (
2501:
2502: /************** account level update ************/
2503:
2504: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2505: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNT_ROLES', FALSE );
2506:
2507: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2508: SET cust_account_id = (
2509: SELECT m.customer_id
2552: -- End Bug 4712462
2553:
2554: g_count := sql%rowcount;
2555:
2556: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2557: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2558: CLOSE c3;--5067291
2559: END IF;
2560:
2553:
2554: g_count := sql%rowcount;
2555:
2556: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2557: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2558: CLOSE c3;--5067291
2559: END IF;
2560:
2561: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CONT()-' );
2557: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2558: CLOSE c3;--5067291
2559: END IF;
2560:
2561: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_CONT()-' );
2562:
2563: EXCEPTION
2564:
2565: WHEN OTHERS THEN
2562:
2563: EXCEPTION
2564:
2565: WHEN OTHERS THEN
2566: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_CONT' );
2567: RAISE;
2568:
2569: END ra_cont;
2570:
2634: FOR UPDATE NOWAIT;
2635:
2636: BEGIN
2637:
2638: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_PH()+' );
2639:
2640: /* locking tables by opening and closing cursors */
2641: IF process_mode = 'LOCK' THEN
2642:
2639:
2640: /* locking tables by opening and closing cursors */
2641: IF process_mode = 'LOCK' THEN
2642:
2643: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
2644: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_CONTACT_POINTS', FALSE );
2645:
2646: OPEN c1;
2647: CLOSE c1;
2640: /* locking tables by opening and closing cursors */
2641: IF process_mode = 'LOCK' THEN
2642:
2643: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
2644: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_CONTACT_POINTS', FALSE );
2645:
2646: OPEN c1;
2647: CLOSE c1;
2648:
2652: ELSE
2653:
2654: /************** account site level inactivate ************/
2655:
2656: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2657: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_CONTACT_POINTS', FALSE );
2658:
2659: UPDATE HZ_CUST_CONTACT_POINTS yt
2660: SET (cust_account_id, cust_account_site_id) = (
2653:
2654: /************** account site level inactivate ************/
2655:
2656: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2657: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_CONTACT_POINTS', FALSE );
2658:
2659: UPDATE HZ_CUST_CONTACT_POINTS yt
2660: SET (cust_account_id, cust_account_site_id) = (
2661: SELECT min(m.customer_id), min(m.customer_address_id)
2680: AND m.set_number = set_num );
2681:
2682: g_count := sql%rowcount;
2683:
2684: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2685: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2686:
2687: /************** account level inactivate ************/
2688:
2681:
2682: g_count := sql%rowcount;
2683:
2684: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2685: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2686:
2687: /************** account level inactivate ************/
2688:
2689: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2685: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2686:
2687: /************** account level inactivate ************/
2688:
2689: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2690: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_CONTACT_POINTS', FALSE );
2691:
2692: UPDATE HZ_CUST_CONTACT_POINTS yt
2693: SET cust_account_id = (
2686:
2687: /************** account level inactivate ************/
2688:
2689: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2690: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_CONTACT_POINTS', FALSE );
2691:
2692: UPDATE HZ_CUST_CONTACT_POINTS yt
2693: SET cust_account_id = (
2694: SELECT DISTINCT m.customer_id
2713: AND cust_account_site_id IS NULL;
2714:
2715: g_count := sql%rowcount;
2716:
2717: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2718: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2719:
2720: END IF;
2721:
2714:
2715: g_count := sql%rowcount;
2716:
2717: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2718: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2719:
2720: END IF;
2721:
2722: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_PH()-' );
2718: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2719:
2720: END IF;
2721:
2722: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_PH()-' );
2723:
2724: EXCEPTION
2725:
2726: WHEN OTHERS THEN
2723:
2724: EXCEPTION
2725:
2726: WHEN OTHERS THEN
2727: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_PH' );
2728: RAISE;
2729:
2730: END ra_ph;
2731:
2791:
2792:
2793: BEGIN
2794:
2795: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_USG()+' );
2796:
2797: /* locking tables by opening and closing cursors */
2798: IF process_mode = 'LOCK' THEN
2799:
2796:
2797: /* locking tables by opening and closing cursors */
2798: IF process_mode = 'LOCK' THEN
2799:
2800: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
2801: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
2802:
2803: OPEN c1;
2804: CLOSE c1;
2797: /* locking tables by opening and closing cursors */
2798: IF process_mode = 'LOCK' THEN
2799:
2800: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
2801: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
2802:
2803: OPEN c1;
2804: CLOSE c1;
2805:
2805:
2806: ELSE
2807: /*************** 'inactivate' mode ***************/
2808:
2809: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2810: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
2811:
2812: --inactivate active 'CUSTOMER' usage of merge-from party if the merge-from account is the only active account
2813:
2806: ELSE
2807: /*************** 'inactivate' mode ***************/
2808:
2809: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2810: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
2811:
2812: --inactivate active 'CUSTOMER' usage of merge-from party if the merge-from account is the only active account
2813:
2814:
2842: AND c1.status = 'I'
2843: );
2844: g_count := sql%rowcount;
2845:
2846: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2847: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2848:
2849: /*************** 'delete' mode ***************/
2850: ---Bug No. 4558392
2843: );
2844: g_count := sql%rowcount;
2845:
2846: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2847: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2848:
2849: /*************** 'delete' mode ***************/
2850: ---Bug No. 4558392
2851: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2847: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2848:
2849: /*************** 'delete' mode ***************/
2850: ---Bug No. 4558392
2851: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2852: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
2853:
2854: --inactivate active 'CUSTOMER' usage of merge-from party when delete after merge is checked if
2855: -- There exists any other Inactive accounts AND doesnot exist any active accounts associated with the merge-from
2848:
2849: /*************** 'delete' mode ***************/
2850: ---Bug No. 4558392
2851: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2852: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
2853:
2854: --inactivate active 'CUSTOMER' usage of merge-from party when delete after merge is checked if
2855: -- There exists any other Inactive accounts AND doesnot exist any active accounts associated with the merge-from
2856: --party AND there is no other customer usage associated with that party.
2901: AND c1.status = 'D'--Bug No. 4558392
2902: );
2903: g_count := sql%rowcount;
2904:
2905: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2906: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2907:
2908: -----Bug No. 4558392
2909: /*************** 'delete' mode ***************/
2902: );
2903: g_count := sql%rowcount;
2904:
2905: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2906: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2907:
2908: -----Bug No. 4558392
2909: /*************** 'delete' mode ***************/
2910:
2909: /*************** 'delete' mode ***************/
2910:
2911:
2912:
2913: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2914: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
2915:
2916: --Delete all 'CUSTOMER' usage of merge-from party
2917: --if the merge-from account is the only account for merge-from party OR there are any other customer usages associated with that party.
2910:
2911:
2912:
2913: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2914: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
2915:
2916: --Delete all 'CUSTOMER' usage of merge-from party
2917: --if the merge-from account is the only account for merge-from party OR there are any other customer usages associated with that party.
2918:
2958: );
2959:
2960: g_count := sql%rowcount;
2961:
2962: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2963: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2964:
2965:
2966: END IF;
2959:
2960: g_count := sql%rowcount;
2961:
2962: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2963: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2964:
2965:
2966: END IF;
2967: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_USG()-' );
2963: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2964:
2965:
2966: END IF;
2967: arp_message.set_line( 'ARP_CMERGE_ARCUS.RA_USG()-' );
2968:
2969: EXCEPTION
2970:
2971: WHEN OTHERS THEN
2968:
2969: EXCEPTION
2970:
2971: WHEN OTHERS THEN
2972: arp_message.set_error( 'ARP_CMERGE_ARCUS.RA_USG' );
2973: RAISE;
2974: END ra_usg;
2975: --4307679
2976:
3072: /* AND rel.directional_flag = 'F'; */ /* Bug No : 2359461 */
3073:
3074: BEGIN
3075:
3076: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_cust_merge_contacts()+' );
3077:
3078: /* Copy org contact */
3079: OPEN c;
3080: LOOP
3104:
3105: END LOOP;
3106: CLOSE c;
3107:
3108: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_cust_merge_contacts()-' );
3109:
3110: EXCEPTION
3111:
3112: WHEN OTHERS THEN
3109:
3110: EXCEPTION
3111:
3112: WHEN OTHERS THEN
3113: arp_message.set_error( 'ARP_CMERGE_ARCUS.do_cust_merge_contacts' );
3114: RAISE;
3115:
3116: END do_cust_merge_contacts;
3117:
3245: AND set_number = p_set_num;
3246:
3247: BEGIN
3248:
3249: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_site_merge_contacts()+' );
3250:
3251: /* For each site level org contact do the following */
3252: OPEN d;
3253: LOOP
3310:
3311: END LOOP;
3312: CLOSE d;
3313:
3314: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_site_merge_contacts()-' );
3315:
3316: EXCEPTION
3317:
3318: WHEN OTHERS THEN
3315:
3316: EXCEPTION
3317:
3318: WHEN OTHERS THEN
3319: arp_message.set_error( 'ARP_CMERGE_ARCUS.do_site_merge_contacts' );
3320: RAISE;
3321:
3322: END do_site_merge_contacts;
3323:
3405: l_party_type varchar2(30);
3406: --End of Bug No: 4387523
3407: BEGIN
3408:
3409: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_merge_contacts()+' );
3410:
3411: --check if the org contact has been migrated
3412: BEGIN
3413: l_sql := 'SELECT new_id ' ||
3435: p_to_party_id,
3436: x_org_contact_id ,
3437: p_from_account_id);
3438:
3439: --arp_message.set_line( '****' || to_char(x_org_contact_id) ||
3440: --'****' || l_dup_exists );
3441:
3442: IF l_dup_exists = FND_API.G_TRUE THEN
3443: l_insert := FND_API.G_TRUE;
3474: x_msg_data );
3475:
3476: IF x_msg_count = 1 THEN
3477: x_msg_data := x_msg_data || '**GET_CURRENT_ORG_CONTACT**';
3478: arp_message.set_line(
3479: 'do_merge_contacts:get_org_contact_rec ERROR ' ||
3480: x_msg_data );
3481: ELSIF x_msg_count > 1 THEN
3482:
3482:
3483: FOR x IN 1..x_msg_count LOOP
3484: x_msg_data := FND_MSG_PUB.GET(p_encoded => fnd_api.g_false);
3485: x_msg_data := x_msg_data || '**GET_CURRENT_ORG_CONTACT**';
3486: arp_message.set_line(
3487: 'do_merge_contacts:get_org_contact_rec ERROR ' ||
3488: x_msg_data );
3489: END LOOP;
3490: END IF;
3545: x_msg_data => x_msg_data);
3546:
3547: IF x_msg_count = 1 THEN
3548: x_msg_data := x_msg_data || '**CREATE_ORG_CONTACT**';
3549: arp_message.set_line(
3550: 'do_merge_contacts:create_org_contact ERROR ' ||
3551: x_msg_data );
3552: ELSIF x_msg_count > 1 THEN
3553:
3553:
3554: FOR x IN 1..x_msg_count LOOP
3555: x_msg_data := FND_MSG_PUB.GET(p_encoded => fnd_api.g_false);
3556: x_msg_data := x_msg_data || '**CREATE_ORG_CONTACT**';
3557: arp_message.set_line(
3558: 'do_cust_merge_contacts:create_org_contact ERROR ' ||
3559: x_msg_data );
3560: END LOOP;
3561: END IF;
3600: p_org_party_id,
3601: x_org_party_id,
3602: p_from_account_id );
3603:
3604: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_merge_contacts()-' );
3605:
3606: EXCEPTION
3607:
3608: WHEN OTHERS THEN
3605:
3606: EXCEPTION
3607:
3608: WHEN OTHERS THEN
3609: arp_message.set_error( 'ARP_CMERGE_ARCUS.do_merge_contacts' );
3610: RAISE;
3611:
3612: END do_merge_contacts;
3613:
3658: ) IS
3659:
3660: BEGIN
3661:
3662: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_cust_merge_cpoint()+' );
3663:
3664: do_copy_contact_points ( 'HZ_PARTIES',
3665: p_from_party_id,
3666: p_to_party_id,
3665: p_from_party_id,
3666: p_to_party_id,
3667: p_from_account_id );
3668:
3669: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_cust_merge_cpoint()-' );
3670:
3671: EXCEPTION
3672:
3673: WHEN OTHERS THEN
3670:
3671: EXCEPTION
3672:
3673: WHEN OTHERS THEN
3674: arp_message.set_error( 'ARP_CMERGE_ARCUS.do_cust_merge_cpoint' );
3675: RAISE;
3676:
3677: END do_cust_merge_cpoint;
3678:
3723: ) IS
3724:
3725: BEGIN
3726:
3727: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_site_merge_cpoint()+' );
3728:
3729: do_copy_contact_points ( 'HZ_PARTY_SITES',
3730: p_from_party_site_id,
3731: p_to_party_site_id,
3730: p_from_party_site_id,
3731: p_to_party_site_id,
3732: p_from_account_id );
3733:
3734: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_site_merge_cpoint()-' );
3735:
3736: EXCEPTION
3737:
3738: WHEN OTHERS THEN
3735:
3736: EXCEPTION
3737:
3738: WHEN OTHERS THEN
3739: arp_message.set_error( 'ARP_CMERGE_ARCUS.do_site_merge_cpoint' );
3740: RAISE;
3741:
3742: END do_site_merge_cpoint;
3743:
3820: l_dup_exists VARCHAR2(10) := fnd_api.g_false;
3821:
3822: BEGIN
3823:
3824: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_copy_contact_points ()+' );
3825:
3826: OPEN c;
3827: LOOP
3828: FETCH c INTO l_contact_point_id;
3832: l_dup_exists := check_contact_point_dup (l_contact_point_id,
3833: p_to_id,
3834: x_contact_point_id);
3835:
3836: --arp_message.set_line( '****' || to_char(x_contact_point_id) ||
3837: --'****' || l_dup_exists );
3838:
3839: IF l_dup_exists <> fnd_api.g_true THEN
3840:
3854: x_web_rec => l_web_rec,
3855: x_return_status => x_return_status,
3856: x_msg_count => x_msg_count,
3857: x_msg_data => x_msg_data);
3858: arp_message.set_line('ID ' || l_contact_point_rec.owner_table_id);
3859:
3860: IF x_msg_count = 1 THEN
3861: x_msg_data := x_msg_data || '**GET_CURRENT_CONTACT_POINTS**';
3862: arp_message.set_line(
3858: arp_message.set_line('ID ' || l_contact_point_rec.owner_table_id);
3859:
3860: IF x_msg_count = 1 THEN
3861: x_msg_data := x_msg_data || '**GET_CURRENT_CONTACT_POINTS**';
3862: arp_message.set_line(
3863: 'do_copy_contact_points:get_current_contact_points ERROR ' ||
3864: x_msg_data );
3865: ELSIF x_msg_count > 1 THEN
3866:
3866:
3867: FOR x IN 1..x_msg_count LOOP
3868: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
3869: x_msg_data := x_msg_data || '**GET_CURRENT_CONTACT_POINTS**';
3870: arp_message.set_line(
3871: 'do_copy_contact_points:get_current_contact_points ERROR ' ||
3872: x_msg_data );
3873: END LOOP;
3874: END IF;
3882: l_contact_point_rec.contact_point_id := NULL;
3883: l_contact_point_rec.primary_flag := NULL;
3884: l_contact_point_rec.owner_table_id := p_to_id;
3885:
3886: arp_message.set_line('ID ' || l_contact_point_rec.owner_table_id);
3887:
3888: --We should not copy the following columns. They are supposed to be
3889: --unique
3890: l_edi_rec.edi_tp_header_id := NULL;
3908: x_msg_data);
3909:
3910: IF x_msg_count = 1 THEN
3911: x_msg_data := x_msg_data || '**CREATE_CONTACT_POINTS**';
3912: arp_message.set_line(
3913: 'do_copy_contact_points:create_contact_points Error '||
3914: x_msg_data);
3915: ELSIF x_msg_count > 1 THEN
3916:
3916:
3917: FOR x IN 1..x_msg_count LOOP
3918: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
3919: x_msg_data := x_msg_data || '**CREATE_CONTACT_POINTS**';
3920: arp_message.set_line(
3921: 'do_copy_contact_points:create_contact_points ERROR ' ||
3922: x_msg_data );
3923: END LOOP;
3924: END IF;
3929: RAISE fnd_api.g_exc_error;
3930: END IF;
3931:
3932: /**
3933: arp_message.set_line(
3934: 'ARP_CMERGE_ARCUS:do_copy_contact_points():old contact point id =' ||
3935: l_contact_point_id );
3936:
3937: arp_message.set_line(
3933: arp_message.set_line(
3934: 'ARP_CMERGE_ARCUS:do_copy_contact_points():old contact point id =' ||
3935: l_contact_point_id );
3936:
3937: arp_message.set_line(
3938: 'ARP_CMERGE_ARCUS.do_copy_contact_points:contact_point_id = ' ||
3939: x_contact_point_id );
3940: **/
3941:
3952: **/
3953: END LOOP;
3954: CLOSE c;
3955:
3956: arp_message.set_line( 'ARP_CMERGE_ARCUS.do_copy_contact_points()-' );
3957:
3958: EXCEPTION
3959:
3960: WHEN OTHERS THEN
3957:
3958: EXCEPTION
3959:
3960: WHEN OTHERS THEN
3961: arp_message.set_error( 'ARP_CMERGE_ARCUS.do_copy_contact_points' );
3962: RAISE;
3963:
3964: END do_copy_contact_points;
3965:
4073:
4074: EXCEPTION
4075:
4076: WHEN OTHERS THEN
4077: arp_message.set_error( 'ARP_CMERGE_ARCUS.check_org_contact_dup' );
4078: RAISE;
4079:
4080: END check_org_contact_dup;
4081:
4204: RETURN FND_API.G_TRUE;
4205:
4206: EXCEPTION
4207: WHEN OTHERS THEN
4208: arp_message.set_error( 'ARP_CMERGE_ARCUS.check_contact_point_dup' );
4209: RAISE;
4210:
4211: END check_contact_point_dup;
4212:
4242: l_sql VARCHAR2(1000);
4243:
4244: BEGIN
4245:
4246: arp_message.set_line( 'ARP_CMERGE_ARCUS.create_temporary_table ( ' ||
4247: g_table_name || ' )()+' );
4248:
4249: BEGIN
4250:
4274: END IF;
4275:
4276: EXECUTE IMMEDIATE l_sql;
4277:
4278: arp_message.set_line( 'ARP_CMERGE_ARCUS.create_temporary_table ( ' ||
4279: g_table_name || ' )()-' );
4280:
4281: EXCEPTION
4282:
4280:
4281: EXCEPTION
4282:
4283: WHEN OTHERS THEN
4284: arp_message.set_error( 'ARP_CMERGE_ARCUS.create_temporary_table ( ' ||
4285: g_table_name || ' )' );
4286: RAISE;
4287:
4288: END create_temporary_table;
4420: AND m.delete_duplicate_flag = 'Y');
4421:
4422: BEGIN
4423:
4424: arp_message.set_line( 'ARP_CMERGE_ARCUS.delete_rows()+' );
4425:
4426: /*****************************************************/
4427:
4428: --4307679
4425:
4426: /*****************************************************/
4427:
4428: --4307679
4429: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4430: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
4431:
4432: --lock rows
4433: OPEN cust_usage;
4426: /*****************************************************/
4427:
4428: --4307679
4429: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4430: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
4431:
4432: --lock rows
4433: OPEN cust_usage;
4434: CLOSE cust_usage;
4444: AND m.delete_duplicate_flag = 'Y');
4445:
4446: g_count := sql%rowcount;
4447:
4448: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4449: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4450:
4451: --4397679
4452:
4445:
4446: g_count := sql%rowcount;
4447:
4448: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4449: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4450:
4451: --4397679
4452:
4453: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4449: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4450:
4451: --4397679
4452:
4453: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4454: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
4455:
4456: --lock rows
4457: OPEN cust_site_uses;
4450:
4451: --4397679
4452:
4453: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4454: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
4455:
4456: --lock rows
4457: OPEN cust_site_uses;
4458: CLOSE cust_site_uses;
4487:
4488:
4489: g_count := sql%rowcount;
4490:
4491: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4492: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4493:
4494: /*****************************************************/
4495:
4488:
4489: g_count := sql%rowcount;
4490:
4491: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4492: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4493:
4494: /*****************************************************/
4495:
4496: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4492: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4493:
4494: /*****************************************************/
4495:
4496: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4497: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
4498:
4499: --lock rows
4500: OPEN cust_sites;
4493:
4494: /*****************************************************/
4495:
4496: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4497: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
4498:
4499: --lock rows
4500: OPEN cust_sites;
4501: CLOSE cust_sites;
4526: AND m.set_number = set_num
4527: AND m.delete_duplicate_flag = 'Y' )
4528: AND status = 'D';
4529:
4530: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4531: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4532:
4533: /*****************************************************/
4534:
4527: AND m.delete_duplicate_flag = 'Y' )
4528: AND status = 'D';
4529:
4530: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4531: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4532:
4533: /*****************************************************/
4534:
4535: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4531: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4532:
4533: /*****************************************************/
4534:
4535: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4536: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
4537:
4538: --lock rows
4539: OPEN cust_accounts;
4532:
4533: /*****************************************************/
4534:
4535: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4536: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
4537:
4538: --lock rows
4539: OPEN cust_accounts;
4540: CLOSE cust_accounts;
4562: AND status = 'D';
4563:
4564: g_count := sql%rowcount;
4565:
4566: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4567: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4568:
4569: /*****************************************************/
4570:
4563:
4564: g_count := sql%rowcount;
4565:
4566: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4567: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4568:
4569: /*****************************************************/
4570:
4571: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4567: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4568:
4569: /*****************************************************/
4570:
4571: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4572: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
4573:
4574: --lock rows
4575: OPEN cust_rel1;
4568:
4569: /*****************************************************/
4570:
4571: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4572: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
4573:
4574: --lock rows
4575: OPEN cust_rel1;
4576: CLOSE cust_rel1;
4589: AND status = 'D';
4590:
4591: g_count := sql%rowcount;
4592:
4593: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4594: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4595:
4596: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4597: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
4590:
4591: g_count := sql%rowcount;
4592:
4593: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4594: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4595:
4596: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4597: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
4598:
4592:
4593: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4594: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4595:
4596: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4597: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
4598:
4599: --lock rows
4600: OPEN cust_rel2;
4593: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4594: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4595:
4596: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4597: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_RELATE', FALSE );
4598:
4599: --lock rows
4600: OPEN cust_rel2;
4601: CLOSE cust_rel2;
4613: AND status = 'D';
4614:
4615: g_count := sql%rowcount;
4616:
4617: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4618: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4619:
4620: /************** account site level delete ************/
4621:
4614:
4615: g_count := sql%rowcount;
4616:
4617: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4618: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4619:
4620: /************** account site level delete ************/
4621:
4622: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4618: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4619:
4620: /************** account site level delete ************/
4621:
4622: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4623: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
4624:
4625: OPEN cust_receipt_methods_site;
4626: CLOSE cust_receipt_methods_site;
4619:
4620: /************** account site level delete ************/
4621:
4622: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4623: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
4624:
4625: OPEN cust_receipt_methods_site;
4626: CLOSE cust_receipt_methods_site;
4627:
4637: AND m.delete_duplicate_flag = 'Y');
4638:
4639: g_count := sql%rowcount;
4640:
4641: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4642: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4643:
4644: /************** account level delete ************/
4645:
4638:
4639: g_count := sql%rowcount;
4640:
4641: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4642: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4643:
4644: /************** account level delete ************/
4645:
4646: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4642: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4643:
4644: /************** account level delete ************/
4645:
4646: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4647: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
4648:
4649: OPEN cust_receipt_methods_acct;
4650: CLOSE cust_receipt_methods_acct;
4643:
4644: /************** account level delete ************/
4645:
4646: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4647: arp_message.set_token( 'TABLE_NAME', 'RA_CUST_RECEIPT_METHODS', FALSE );
4648:
4649: OPEN cust_receipt_methods_acct;
4650: CLOSE cust_receipt_methods_acct;
4651:
4664: AND status <> 'D' );
4665:
4666: g_count := sql%rowcount;
4667:
4668: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4669: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4670:
4671: arp_message.set_line( 'ARP_CMERGE_ARCUS.delete_rows()-' );
4672:
4665:
4666: g_count := sql%rowcount;
4667:
4668: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4669: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4670:
4671: arp_message.set_line( 'ARP_CMERGE_ARCUS.delete_rows()-' );
4672:
4673: EXCEPTION
4667:
4668: arp_message.set_name( 'AR', 'AR_ROWS_DELETED' );
4669: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4670:
4671: arp_message.set_line( 'ARP_CMERGE_ARCUS.delete_rows()-' );
4672:
4673: EXCEPTION
4674:
4675: WHEN OTHERS THEN
4672:
4673: EXCEPTION
4674:
4675: WHEN OTHERS THEN
4676: arp_message.set_error( 'ARP_CMERGE_ARCUS.delete_rows' );
4677: RAISE;
4678:
4679: END delete_rows;
4680:
5048: l_dun_exists VARCHAR2(1); --Added for Bug 6469732
5049:
5050: BEGIN
5051:
5052: arp_message.set_line( 'ARP_CMERGE_ARCUS.create_same_sites()+' );
5053:
5054: OPEN sites_need_to_create;
5055: LOOP
5056: FETCH sites_need_to_create INTO
5132:
5133: --Need to create new party site.
5134: IF l_exist = 'N' THEN
5135:
5136: arp_message.set_line('create_same_sites:create_party_site');
5137:
5138: --Need to create new party site.
5139: --Build party site records
5140:
5152: x_msg_data => x_msg_data );
5153:
5154: IF x_msg_count = 1 THEN
5155: x_msg_data := x_msg_data || '**GET_CURRENT_PARTY_SITE**';
5156: arp_message.set_line(
5157: 'do_merge_contacts:get_current_party_site ERROR ' ||
5158: x_msg_data );
5159: ELSIF x_msg_count > 1 THEN
5160:
5160:
5161: FOR x IN 1..x_msg_count LOOP
5162: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
5163: x_msg_data := x_msg_data || '**GET_CURRENT_PARTY_SITE**';
5164: arp_message.set_line(
5165: 'do_merge_contacts:get_current_party_site ERROR ' ||
5166: x_msg_data );
5167: END LOOP;
5168: END IF;
5209:
5210: --Handle error message
5211: IF x_msg_count = 1 THEN
5212: x_msg_data := x_msg_data || '**CREATE_PARTY_SITE**';
5213: arp_message.set_line(
5214: 'create_same_sites:create_party_site ERROR '||
5215: x_msg_data);
5216: ELSIF x_msg_count > 1 THEN
5217:
5217:
5218: FOR x IN 1..x_msg_count LOOP
5219: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
5220: x_msg_data := x_msg_data || '**CREATE_PARTY_SITE**';
5221: arp_message.set_line(
5222: 'create_same_sites:create_party_site ERROR ' ||
5223: x_msg_data );
5224: END LOOP;
5225: END IF;
5276: /*--Commented out for bug 4492628
5277: IF l_create_party_site_use = 'Y' THEN
5278: BEGIN
5279:
5280: arp_message.set_line('create_same_sites:create_party_site_use');
5281:
5282: -- Build party site use record.
5283:
5284: select party_site_use_id into l_duplicate_party_site_use_id
5310: --Handle error message.
5311:
5312: IF x_msg_count = 1 THEN
5313: x_msg_data := x_msg_data || '**CREATE_PARTY_SITE_USE**';
5314: arp_message.set_line( 'create_same_sites:create_party_site_use ERROR '|| x_msg_data);
5315: ELSIF x_msg_count > 1 THEN
5316:
5317: FOR x IN 1..x_msg_count LOOP
5318: x_msg_data := FND_MSG_PUB.GET(p_encoded => fnd_api.g_false);
5316:
5317: FOR x IN 1..x_msg_count LOOP
5318: x_msg_data := FND_MSG_PUB.GET(p_encoded => fnd_api.g_false);
5319: x_msg_data := x_msg_data || '**CREATE_PARTY_SITE_USE**';
5320: arp_message.set_line( 'create_same_sites:create_party_site_use ERROR ' || x_msg_data );
5321: END LOOP;
5322: END IF;
5323:
5324: -- After call create_* API, if return_status is not
5338: END IF;
5339: -- END of create_party_site_use
5340: */--Commented out for bug 4492628
5341:
5342: arp_message.set_line('create_same_sites:create_account_site');
5343:
5344: --Create account site.
5345: --Build account site records
5346: SELECT --Bug:2098728 obsoleted wh_update_date,
5488:
5489: --Handle error message
5490: IF x_msg_count = 1 THEN
5491: x_msg_data := x_msg_data || '**CREATE_ACCOUNT_SITE**';
5492: arp_message.set_line(
5493: 'create_same_sites:create_account_site ERROR '||
5494: x_msg_data);
5495: ELSIF x_msg_count > 1 THEN
5496:
5496:
5497: FOR x IN 1..x_msg_count LOOP
5498: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
5499: x_msg_data := x_msg_data || '**CREATE_ACCOUNT_SITE**';
5500: arp_message.set_line(
5501: 'create_same_sites:create_account_site ERROR ' ||
5502: x_msg_data );
5503: END LOOP;
5504: END IF;
5598:
5599: IF l_create_party_site_use = 'Y' THEN
5600: BEGIN
5601:
5602: arp_message.set_line('create_same_sites:create_party_site_use');
5603:
5604: -- Build party site use record.
5605:
5606: select party_site_use_id into l_duplicate_party_site_use_id
5632: --Handle error message.
5633:
5634: IF x_msg_count = 1 THEN
5635: x_msg_data := x_msg_data || '**CREATE_PARTY_SITE_USE**';
5636: arp_message.set_line( 'create_same_sites:create_party_site_use ERROR '|| x_msg_data);
5637: ELSIF x_msg_count > 1 THEN
5638:
5639: FOR x IN 1..x_msg_count LOOP
5640: x_msg_data := FND_MSG_PUB.GET(p_encoded => fnd_api.g_false);
5638:
5639: FOR x IN 1..x_msg_count LOOP
5640: x_msg_data := FND_MSG_PUB.GET(p_encoded => fnd_api.g_false);
5641: x_msg_data := x_msg_data || '**CREATE_PARTY_SITE_USE**';
5642: arp_message.set_line( 'create_same_sites:create_party_site_use ERROR ' || x_msg_data );
5643: END LOOP;
5644: END IF;
5645:
5646: -- After call create_* API, if return_status is not
5664: -----Bug:2376975 create only if duplicate_site_id <> -99
5665: IF l_create_acct_site_use = 'Y' AND l_duplicate_site_id <> -99 THEN
5666: BEGIN
5667:
5668: arp_message.set_line('create_same_sites:create_account_site_use');
5669:
5670:
5671: --Create customer profile and customer profile amts.
5672: --at site levels. profile is mandatory in account level,
6196: l_gen_loc := hz_mo_global_cache.get_auto_site_numbering(cust_site_use_rec.org_id);
6197: --Bug Fix 2183072
6198: EXCEPTION
6199: WHEN NO_DATA_FOUND THEN
6200: --arp_message.set_name( 'AR','AR_NO_ROW_IN_SYSTEM_PARAMETERS');
6201: RAISE fnd_api.g_exc_error;
6202: END ;
6203:
6204: IF l_gen_loc = 'N' THEN
6269:
6270: --Handle error message.
6271: IF x_msg_count = 1 THEN
6272: x_msg_data := x_msg_data || '**CREATE_ACCOUNT_SITE_USE**';
6273: arp_message.set_line(
6274: 'create_same_sites:create_acct_site_uses ERROR '||
6275: x_msg_data);
6276: ELSIF x_msg_count > 1 THEN
6277:
6277:
6278: FOR x IN 1..x_msg_count LOOP
6279: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
6280: x_msg_data := x_msg_data || '**CREATE_ACCOUNT_SITE_USE**';
6281: arp_message.set_line(
6282: 'create_same_sites:create_acct_site_uses ERROR ' ||
6283: x_msg_data );
6284: END LOOP;
6285: END IF;
6292:
6293: -- Find out the customer profile id.
6294: IF l_create_profile = 'Y' THEN
6295:
6296: arp_message.set_line('create_same_sites:create_profile_amount');
6297:
6298: SELECT cust_account_profile_id INTO l_to_cust_account_profile_id
6299: FROM hz_customer_profiles
6300: WHERE cust_account_id = l_customer_id
6403: x_msg_data => x_msg_data);
6404:
6405: IF x_msg_count = 1 THEN
6406: x_msg_data := x_msg_data || '**CREATE_CUST_PROF_AMT**';
6407: arp_message.set_line(
6408: 'create_same_sites:create_cust_prof_amt ERROR '||
6409: x_msg_data);
6410: ELSIF x_msg_count > 1 THEN
6411:
6411:
6412: FOR x IN 1..x_msg_count LOOP
6413: x_msg_data := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
6414: x_msg_data := x_msg_data || '**CREATE_CUST_PROF_AMT**';
6415: arp_message.set_line(
6416: 'create_same_sites:create_cust_prof_amt ERROR ' ||
6417: x_msg_data );
6418: END LOOP;
6419: END IF;
6430: END IF; --there is profile exist for this site use.
6431:
6432: /* Bug:2241033; Creating Payment Method for customer site use */
6433: begin
6434: arp_message.set_line('create_same_sites:create_cust_site_payment_method');
6435: savepoint merge_from_pay_method_point;
6436:
6437: open merge_from_pay_method;
6438: loop
6493: exception
6494: when others then
6495: rollback to merge_from_pay_method_point;
6496:
6497: arp_message.set_line('create_same_sites:create_cust_site_payment_method error');
6498: status := -1;
6499: RAISE fnd_api.g_exc_error;
6500: end;
6501: /* --end of creating payment method for customer site use */
6555: CLOSE sites_need_to_create;
6556:
6557: status := 0;
6558:
6559: arp_message.set_line( 'ARP_CMERGE_ARCUS.create_same_sites()-' );
6560:
6561: EXCEPTION
6562:
6563: WHEN OTHERS THEN
6560:
6561: EXCEPTION
6562:
6563: WHEN OTHERS THEN
6564: arp_message.set_error( 'ARP_CMERGE_ARCUS.create_same_sites' );
6565: status := -1;
6566:
6567: END create_same_sites;
6568:
6600:
6601:
6602: BEGIN
6603:
6604: arp_message.set_line( 'ARP_CMERGE_ARCUS.Merge_History()+' );
6605:
6606: ---------Insert into HZ_CUST_ACCOUNTS_M--------------
6607: INSERT INTO HZ_CUST_ACCOUNTS_M(
6608: customer_merge_header_id,
6844: and cm.set_number = set_num) , HZ_CUST_ACCOUNTS c
6845: WHERE c.cust_account_id = duplicate_id
6846: AND duplicate_id <> customer_id;
6847:
6848: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCOUNTS_M');
6849:
6850:
6851:
6852: ---------Insert into hz_cust_account_roles_m--------------
7006: WHERE ( ar.cust_account_id = duplicate_id OR
7007: ar.cust_acct_site_id = duplicate_address_id )
7008: AND ar.role_type = 'CONTACT';
7009:
7010: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCOUNT_ROLES_M');
7011:
7012: ---------Insert into hz_customer_profiles_m--------------
7013: --Because profiles can be set up at acct and site use level, we need to select
7014: --distict duplicate and customer_merge_header_id if acct with multiple sites
7241: and cm.duplicate_id <> cm.customer_id ), hz_customer_profiles cp
7242: WHERE ( cp.cust_account_id = duplicate_id AND cp.site_use_id is NULL)
7243: OR (cp.site_use_id = duplicate_site_id );
7244:
7245: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUSTOMER_PROFILES_M');
7246:
7247: ---------Insert into hz_cust_profile_amts_m--------------
7248: --Because profiles can be set up at acct and site use level, we need to select
7249: --distict duplicate and customer_merge_header_id if acct with multiple sites
7427: and cm.set_number = set_num ),hz_cust_profile_amts pa
7428: WHERE (pa.cust_account_id = duplicate_id and pa.site_use_id is NULL)
7429: OR (pa.site_use_id = duplicate_site_id);
7430:
7431: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_PROFILE_AMTS_M');
7432:
7433: ---------Insert into hz_cust_acct_sites_all_m--------------
7434: INSERT INTO hz_cust_acct_sites_all_m(
7435: customer_merge_header_id,
7597: and cm.set_number = set_num ) m,hz_cust_acct_sites_all acs
7598: WHERE acs.cust_acct_site_id = duplicate_address_id
7599: AND acs.org_id = m.org_id ;
7600:
7601: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCT_SITES_ALL_M');
7602:
7603: ---------Insert into hz_cust_site_uses_all_m--------------
7604: INSERT INTO hz_cust_site_uses_all_m(
7605: customer_merge_header_id,
7849: and cm.set_number = set_num ) m,hz_cust_site_uses_all su --SSUptake
7850: WHERE su.site_use_id = duplicate_site_id
7851: AND su.org_id = m.org_id; --SSUptake
7852:
7853: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_SITE_USES_ALL_M');
7854:
7855: ---------Insert into hz_cust_acct_relate_all_m--------------
7856: INSERT INTO hz_cust_acct_relate_all_m(
7857: customer_merge_header_id,
7943: WHERE ( yt.cust_account_id = duplicate_id OR
7944: yt.related_cust_account_id = duplicate_id )
7945: AND m.org_id = yt.org_id ; --SSUptake
7946:
7947: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCT_RELATE_ALL_M');
7948:
7949:
7950: ---After storing the merge data sucessfully initialize the status to 0
7951: status := 0;
7949:
7950: ---After storing the merge data sucessfully initialize the status to 0
7951: status := 0;
7952:
7953: arp_message.set_line( 'ARP_CMERGE_ARCUS.Merge_History()-' );
7954:
7955: EXCEPTION
7956: WHEN NO_DATA_FOUND THEN
7957: NULL;
7956: WHEN NO_DATA_FOUND THEN
7957: NULL;
7958:
7959: WHEN DUP_VAL_ON_INDEX THEN
7960: arp_message.set_line('Index Violation: ' || SQLERRM );
7961:
7962:
7963: WHEN OTHERS THEN
7964: arp_message.set_error('ARP_CMERGE_ARCUS.Merge_History');
7960: arp_message.set_line('Index Violation: ' || SQLERRM );
7961:
7962:
7963: WHEN OTHERS THEN
7964: arp_message.set_error('ARP_CMERGE_ARCUS.Merge_History');
7965: status := -1;
7966:
7967:
7968: END merge_history;