eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ARP_CMERGE_ARCUS dependencies on RA_CUSTOMER_MERGES

Line 285: FROM hz_cust_site_uses_all cust, ra_customer_merges merge --SSUptake

281: CURSOR c1 IS
282: SELECT merge.duplicate_site_id,merge.customer_site_id,
283: cust.bill_to_site_use_id,merge.duplicate_site_code,
284: merge.customer_createsame,cust.org_id --SSUptake
285: FROM hz_cust_site_uses_all cust, ra_customer_merges merge --SSUptake
286: WHERE merge.request_id = req_id
287: AND merge.set_number = set_num
288: AND merge.process_flag = 'N'
289: AND cust.site_use_id = merge.duplicate_site_id

Line 314: from ra_customer_merges

310: IF l_site_use_code = 'SHIP_TO' and l_create_same_site = 'Y' THEN
311: IF l_bill_to_site_use_id IS NOT NULL THEN
312: BEGIN
313: select customer_site_id into l_ra_bill_to_site_use_id
314: from ra_customer_merges
315: where duplicate_site_id = l_bill_to_site_use_id
316: and process_flag = 'N'
317: and request_id = req_id
318: and set_number = set_num;

Line 343: FROM ra_customer_merges merge

339: AND su.site_use_code='SHIP_TO'
340: AND su.bill_to_site_use_id = l_dup_site_id
341: AND site.cust_account_id in (
342: SELECT unique(customer_id)
343: FROM ra_customer_merges merge
344: WHERE merge.process_flag = 'N'
345: and merge.request_id = req_id
346: and merge.set_number = set_num
347: and merge.org_id = site.org_id --SSUptake

Line 354: from ra_customer_merges merge

350: FROM hz_cust_acct_relate_all rel --SSUptake
351: WHERE rel.org_id = l_org_id --SSUptake
352: AND rel.cust_account_id in (
353: select unique(customer_id)
354: from ra_customer_merges merge
355: where merge.process_flag = 'N'
356: and merge.request_id = req_id
357: and merge.set_number = set_num
358: and merge.org_id = rel.org_id ) --SSUptake

Line 426: FROM hz_cust_site_uses_all su, ra_customer_merges m --SSUptake

422: x_to_site_id NUMBER;
423:
424: CURSOR c1 IS
425: SELECT site_use_id
426: FROM hz_cust_site_uses_all su, ra_customer_merges m --SSUptake
427: WHERE m.request_id = req_id
428: AND m.process_flag = 'N'
429: AND m.set_number = set_num
430: AND m.org_id = su.org_id --SSUptake

Line 437: FROM ra_customer_merges m

433:
434:
435: CURSOR c2 is
436: SELECT distinct(m.customer_site_id), m.duplicate_site_id
437: FROM ra_customer_merges m
438: WHERE m.process_flag = 'N'
439: AND m.request_id = req_id
440: AND m.set_number = set_num
441: AND (m.delete_duplicate_flag = 'N' OR m.delete_duplicate_flag = 'Y');

Line 476: FROM ra_customer_merges m

472: program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
473: program_update_date = sysdate
474: WHERE EXISTS (
475: SELECT 'Y'
476: FROM ra_customer_merges m
477: WHERE m.duplicate_site_id = yt.site_use_id
478: AND m.org_id = yt.org_id
479: AND m.process_flag = 'N'
480: AND m.request_id = req_id

Line 504: FROM ra_customer_merges m

500: program_application_id = arp_standard.PROFILE.program_application_id,
501: program_id = arp_standard.PROFILE.program_id,
502: program_update_date = SYSDATE
503: WHERE asu.site_use_id IN( SELECT customer_site_id
504: FROM ra_customer_merges m
505: WHERE m.request_id = req_id
506: AND m.set_number = set_num
507: AND m.customer_createsame = 'N'
508: AND m.org_id = asu.org_id

Line 545: FROM ra_customer_merges m

541: program_id = arp_standard.profile.program_id,
542: program_update_date = sysdate
543: WHERE cust_acct_site_id IN (
544: SELECT m.duplicate_address_id
545: FROM ra_customer_merges m
546: WHERE m.process_flag = 'N'
547: AND m.request_id = req_id
548: AND m.set_number = set_num
549: AND m.delete_duplicate_flag = 'N' )

Line 578: FROM ra_customer_merges m

574: UPDATE HZ_CUST_SITE_USES_ALL yt
575: SET status = 'D'
576: WHERE EXISTS (
577: SELECT 'Y'
578: FROM ra_customer_merges m
579: WHERE m.duplicate_site_id = yt.site_use_id
580: AND m.org_id = yt.org_id
581: AND m.process_flag = 'N'
582: AND m.request_id = req_id

Line 604: FROM ra_customer_merges m

600: UPDATE HZ_CUST_SITE_USES su1
601: SET status = 'D'
602: WHERE cust_acct_site_id IN (
603: SELECT m.duplicate_address_id
604: FROM ra_customer_merges m
605: WHERE m.process_flag = 'N'
606: AND m.request_id = req_id
607: AND m.set_number = set_num
608: AND m.delete_duplicate_flag = 'Y' )

Line 728: FROM hz_cust_acct_sites_all yt, ra_customer_merges m --SSUptake

724:
725: --cursor c1 is used in 'inactivate' mode.
726: CURSOR c1 IS
727: SELECT yt.cust_acct_site_id
728: FROM hz_cust_acct_sites_all yt, ra_customer_merges m --SSUptake
729: WHERE yt.cust_acct_site_id = m.duplicate_address_id
730: AND m.org_id = yt.org_id --SSUptake
731: AND m.request_id = req_id
732: AND m.process_flag = 'N'

Line 746: FROM hz_cust_acct_sites_all yt, ra_customer_merges m --SSUptake

742:
743: --cursor c2 is used in 'delete' mode.
744: CURSOR c2 IS
745: SELECT yt.cust_acct_site_id
746: FROM hz_cust_acct_sites_all yt, ra_customer_merges m --SSUptake
747: WHERE m.request_id = req_id
748: AND m.process_flag = 'N'
749: AND m.set_number = set_num
750: AND m.delete_duplicate_flag = 'Y'

Line 763: FROM ra_customer_merges m

759: FOR UPDATE NOWAIT;
760:
761: cursor c3 is
762: SELECT distinct(m.customer_address_id), m.duplicate_address_id
763: FROM ra_customer_merges m
764: WHERE m.process_flag = 'N'
765: AND m.request_id = req_id
766: AND m.set_number = set_num
767: AND (m.delete_duplicate_flag = 'N' or m.delete_duplicate_flag = 'Y') --5571559

Line 812: FROM ra_customer_merges m

808: program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
809: program_update_date = sysdate
810: WHERE EXISTS (
811: SELECT 'Y'
812: FROM ra_customer_merges m
813: WHERE m.process_flag = 'N'
814: AND m.request_id = req_id
815: AND m.set_number = set_num
816: AND m.delete_duplicate_flag = 'N'

Line 847: FROM ra_customer_merges m

843: program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
844: program_update_date = sysdate
845: WHERE EXISTS (
846: SELECT 'Y'
847: FROM ra_customer_merges m
848: WHERE m.process_flag = 'N'
849: AND m.request_id = req_id
850: AND m.set_number = set_num
851: AND m.duplicate_address_id = yt.cust_acct_site_id

Line 884: FROM ra_customer_merges m

880: program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
881: program_update_date = sysdate
882: WHERE EXISTS (
883: SELECT 'Y'
884: FROM ra_customer_merges m
885: WHERE m.process_flag = 'N'
886: AND m.request_id = req_id
887: AND m.set_number = set_num
888: AND m.delete_duplicate_flag = 'N'

Line 921: FROM ra_customer_merges m

917: program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
918: program_update_date = sysdate
919: WHERE EXISTS (
920: SELECT 'Y'
921: FROM ra_customer_merges m
922: WHERE m.process_flag = 'N'
923: AND m.request_id = req_id
924: AND m.set_number = set_num
925: AND m.delete_duplicate_flag = 'N'

Line 954: FROM hz_cust_site_uses_all su, ra_customer_merges m

950: program_application_id = arp_standard.profile.program_application_id,
951: program_id = arp_standard.profile.program_id,
952: program_update_date = sysdate
953: WHERE EXISTS ( SELECT 1
954: FROM hz_cust_site_uses_all su, ra_customer_merges m
955: WHERE m.request_id = req_id
956: AND m.set_number = set_num
957: AND m.customer_createsame = 'N'
958: AND m.process_flag = 'N'

Line 987: FROM hz_cust_site_uses_all su, ra_customer_merges m

983: program_application_id = arp_standard.profile.program_application_id,
984: program_id = arp_standard.profile.program_id,
985: program_update_date = sysdate
986: WHERE EXISTS ( SELECT 1
987: FROM hz_cust_site_uses_all su, ra_customer_merges m
988: WHERE m.request_id = req_id
989: AND m.set_number = set_num
990: AND m.customer_createsame = 'N'
991: AND m.process_flag = 'N'

Line 1020: FROM hz_cust_site_uses_all su, ra_customer_merges m

1016: program_application_id = arp_standard.profile.program_application_id,
1017: program_id = arp_standard.profile.program_id,
1018: program_update_date = sysdate
1019: WHERE EXISTS ( SELECT 1
1020: FROM hz_cust_site_uses_all su, ra_customer_merges m
1021: WHERE m.request_id = req_id
1022: AND m.set_number = set_num
1023: AND m.customer_createsame = 'N'
1024: AND m.process_flag = 'N'

Line 1055: FROM ra_customer_merges m

1051: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
1052: SET status = 'D'
1053: WHERE EXISTS (
1054: SELECT 'Y'
1055: FROM ra_customer_merges m
1056: WHERE m.process_flag = 'N'
1057: AND m.request_id = req_id
1058: AND m.set_number = set_num
1059: AND m.delete_duplicate_flag = 'Y'

Line 1182: FROM hz_cust_accounts yt, ra_customer_merges m

1178:
1179: --cursor c1 is used in 'inactivate' mode.
1180: CURSOR c1 IS
1181: SELECT yt.cust_account_id
1182: FROM hz_cust_accounts yt, ra_customer_merges m
1183: WHERE cust_account_id = m.duplicate_id
1184: AND m.process_flag = 'N'
1185: AND m.request_id = req_id
1186: AND m.set_number = set_num

Line 1199: FROM hz_cust_accounts yt, ra_customer_merges m

1195:
1196: --cursor c2 is used in 'delete' mode.
1197: CURSOR c2 IS
1198: SELECT cust_account_id
1199: FROM hz_cust_accounts yt, ra_customer_merges m
1200: WHERE cust_account_id = m.duplicate_id
1201: AND m.process_flag = 'N'
1202: AND m.request_id = req_id
1203: AND m.set_number = set_num

Line 1215: FROM ra_customer_merges m

1211: FOR UPDATE NOWAIT;
1212:
1213: cursor c3 is
1214: SELECT distinct(m.customer_id), m.duplicate_id
1215: FROM ra_customer_merges m
1216: WHERE m.process_flag = 'N'
1217: AND m.request_id =req_id
1218: AND m.set_number = set_num
1219: AND (m.delete_duplicate_flag = 'N' OR m.delete_duplicate_flag = 'Y')

Line 1263: FROM ra_customer_merges m

1259: program_id = hz_utility_v2pub.program_id,--arp_standard.profile.program_id,
1260: program_update_date = sysdate
1261: WHERE cust_account_id IN (
1262: SELECT m.duplicate_id
1263: FROM ra_customer_merges m
1264: WHERE m.process_flag = 'N'
1265: AND m.request_id = req_id
1266: AND m.set_number = set_num
1267: AND m.delete_duplicate_flag = 'N' )

Line 1294: FROM ra_customer_merges m

1290: UPDATE HZ_CUST_ACCOUNTS yt
1291: SET status = 'D'
1292: WHERE cust_account_id IN (
1293: SELECT m.duplicate_id
1294: FROM ra_customer_merges m
1295: WHERE m.process_flag = 'N'
1296: AND m.request_id = req_id
1297: AND m.set_number = set_num
1298: AND m.delete_duplicate_flag = 'Y' )

Line 1416: FROM hz_cust_acct_relate_all yt, ra_customer_merges m --SSUptake

1412:
1413: --cursor c1 is for from cust account
1414: CURSOR c1 IS
1415: SELECT yt.cust_account_id
1416: FROM hz_cust_acct_relate_all yt, ra_customer_merges m --SSUptake
1417: WHERE
1418: --NOT EXISTS (
1419: -- SELECT 'active accounts exist'
1420: -- FROM hz_cust_accounts acct

Line 1433: FROM hz_cust_acct_relate_all yt, ra_customer_merges m --SSUptake

1429:
1430: --cursor c2 is for to cust account
1431: CURSOR c2 IS
1432: SELECT yt.related_cust_account_id
1433: FROM hz_cust_acct_relate_all yt, ra_customer_merges m --SSUptake
1434: WHERE
1435: --NOT EXISTS (
1436: -- SELECT 'active accounts exist'
1437: -- FROM hz_cust_accounts acct

Line 1453: FROM hz_cust_acct_relate_all yt, ra_customer_merges cm

1449: CURSOR c_from_rel_cust_id IS
1450: SELECT unique yt.cust_acct_relate_id,cm.customer_id,yt.cust_account_id,yt.related_cust_account_id,
1451: yt.customer_reciprocal_flag,nvl(yt.bill_to_flag,'N') bill_to_flag,
1452: nvl(yt.ship_to_flag,'N') ship_to_flag,yt.rowid,yt.org_id
1453: FROM hz_cust_acct_relate_all yt, ra_customer_merges cm
1454: WHERE cm.request_id = req_id
1455: AND cm.process_flag = 'N'
1456: AND cm.set_number = set_num
1457: AND cm.duplicate_id <> cm.customer_id --merging sites for same customer

Line 1466: FROM hz_cust_acct_relate_all yt, ra_customer_merges cm

1462: CURSOR c_from_cust_rel_id IS
1463: SELECT unique yt.cust_acct_relate_id,cm.customer_id,yt.cust_account_id,yt.related_cust_account_id,
1464: yt.customer_reciprocal_flag,nvl(yt.bill_to_flag,'N') bill_to_flag,
1465: nvl(yt.ship_to_flag,'N') ship_to_flag,yt.rowid,yt.org_id
1466: FROM hz_cust_acct_relate_all yt, ra_customer_merges cm
1467: WHERE cm.request_id = req_id
1468: AND cm.process_flag = 'N'
1469: AND cm.set_number = set_num
1470: AND cm.duplicate_id <> cm.customer_id --merging sites for same customer

Line 1664: FROM ra_customer_merges m

1660: program_id = arp_standard.profile.program_id,
1661: program_update_date = sysdate
1662: WHERE EXISTS (
1663: SELECT 'relationship to self not allowed'
1664: FROM ra_customer_merges m
1665: WHERE m.customer_id = yt.related_cust_account_id
1666: AND m.duplicate_id = yt.cust_account_id
1667: AND m.process_flag = 'N'
1668: AND m.request_id = req_id

Line 1674: FROM ra_customer_merges m

1670: AND m.org_id = yt.org_id
1671: AND m.delete_duplicate_flag = 'N' )
1672: OR EXISTS (
1673: SELECT 'relationship to self not allowed'
1674: FROM ra_customer_merges m
1675: WHERE m.customer_id = yt.cust_account_id
1676: AND m.duplicate_id = yt.related_cust_account_id
1677: AND m.process_flag = 'N'
1678: AND m.request_id = req_id

Line 1696: FROM RA_CUSTOMER_MERGES M

1692: WHERE (RELATED_CUST_ACCOUNT_ID,CUST_ACCOUNT_ID,ORG_ID ) IN
1693: (SELECT CUSTOMER_ID,
1694: DUPLICATE_ID,
1695: ORG_ID
1696: FROM RA_CUSTOMER_MERGES M
1697: WHERE M.PROCESS_FLAG = 'N'
1698: AND M.REQUEST_ID = req_id
1699: AND M.SET_NUMBER = set_num
1700: AND M.DELETE_DUPLICATE_FLAG = 'N' );

Line 1717: FROM RA_CUSTOMER_MERGES M

1713: WHERE (CUST_ACCOUNT_ID,RELATED_CUST_ACCOUNT_ID,ORG_ID ) IN
1714: (SELECT CUSTOMER_ID,
1715: DUPLICATE_ID,
1716: ORG_ID
1717: FROM RA_CUSTOMER_MERGES M
1718: WHERE M.PROCESS_FLAG = 'N'
1719: AND M.REQUEST_ID = req_id
1720: AND M.SET_NUMBER = set_num
1721: AND M.DELETE_DUPLICATE_FLAG = 'N' );

Line 1753: FROM ra_customer_merges m

1749: -- WHERE acct.cust_account_id = yt.cust_account_id
1750: -- AND acct.status <> 'D' )
1751: EXISTS ( --SSUptake
1752: SELECT 'Y'
1753: FROM ra_customer_merges m
1754: WHERE m.duplicate_id = yt.cust_account_id
1755: AND m.process_flag = 'N'
1756: AND m.request_id = req_id
1757: AND m.set_number = set_num

Line 1763: ra_customer_merges m

1759: AND m.org_id = yt.org_id ) --SSUptake
1760: AND ( EXISTS (
1761: SELECT 'relationship already exists, cannot update'
1762: FROM HZ_CUST_ACCT_RELATE_ALL r, --SSUptake
1763: ra_customer_merges m
1764: WHERE m.customer_id = r.cust_account_id
1765: AND m.duplicate_id = yt.cust_account_id
1766: AND r.related_cust_account_id = yt.related_cust_account_id
1767: AND r.org_id = yt.org_id --SSUptake

Line 1775: FROM ra_customer_merges m

1771: AND m.delete_duplicate_flag = 'Y'
1772: AND m.org_id = r.org_id ) --SSUptake
1773: OR EXISTS (
1774: SELECT 'relationship to self not allowed'
1775: FROM ra_customer_merges m
1776: WHERE m.customer_id = yt.related_cust_account_id
1777: AND m.duplicate_id = yt.cust_account_id
1778: AND m.process_flag = 'N'
1779: AND m.request_id = req_id

Line 1786: from ra_customer_merges m

1782: AND m.org_id = yt.org_id ) ) --SSUptake
1783: --Bug fix 2909303
1784: AND NOT EXISTS(
1785: select 'merging sites for same customer, cannot update'
1786: from ra_customer_merges m
1787: where m.duplicate_id = m.customer_id
1788: and m.duplicate_id = yt.cust_account_id
1789: AND m.process_flag = 'N'
1790: AND m.request_id = req_id

Line 1814: FROM ra_customer_merges m

1810: -- WHERE acct.cust_account_id = yt.related_cust_account_id
1811: -- AND acct.status <> 'D' )
1812: EXISTS (
1813: SELECT 'Y'
1814: FROM ra_customer_merges m
1815: WHERE yt.related_cust_account_id = m.duplicate_id
1816: AND m.org_id = yt.org_id --SSUptake
1817: AND m.process_flag = 'N'
1818: AND m.request_id = req_id

Line 1824: ra_customer_merges m

1820: AND m.delete_duplicate_flag = 'Y')
1821: AND ( EXISTS (
1822: SELECT 'relationship already exists, cannot update'
1823: FROM HZ_CUST_ACCT_RELATE_ALL r,
1824: ra_customer_merges m
1825: WHERE m.customer_id = r.related_cust_account_id
1826: AND m.duplicate_id = yt.related_cust_account_id
1827: AND r.cust_account_id = yt.cust_account_id
1828: AND r.org_id = yt.org_id --SSUptake

Line 1836: FROM ra_customer_merges m

1832: AND m.set_number = set_num
1833: AND m.delete_duplicate_flag = 'Y')--SSUptake
1834: OR EXISTS (
1835: SELECT 'relationship to self not allowed'
1836: FROM ra_customer_merges m
1837: WHERE m.customer_id = yt.cust_account_id
1838: AND m.duplicate_id = yt.related_cust_account_id
1839: AND m.org_id = yt.org_id --SSUptake
1840: AND m.process_flag = 'N'

Line 1847: from ra_customer_merges m

1843: AND m.delete_duplicate_flag = 'Y' ) )
1844: --Bug fix 2909303
1845: AND NOT EXISTS(
1846: select 'merging sites for same customer, cannot update'
1847: from ra_customer_merges m
1848: where m.duplicate_id = m.customer_id
1849: and m.duplicate_id = yt.related_cust_account_id
1850: AND m.org_id = yt.org_id --SSUptake
1851: AND m.process_flag = 'N'

Line 1931: FROM RA_CUST_RECEIPT_METHODS ra, ra_customer_merges m

1927:
1928: --cursor c1 and c2 are used in 'inactivate' mode
1929: CURSOR c1 IS
1930: SELECT CUST_RECEIPT_METHOD_ID
1931: FROM RA_CUST_RECEIPT_METHODS ra, ra_customer_merges m
1932: WHERE ra.customer_id = m.duplicate_id
1933: AND site_use_id = m.duplicate_site_id
1934: AND m.process_flag = 'N'
1935: AND m.request_id = req_id

Line 1942: FROM RA_CUST_RECEIPT_METHODS yt, ra_customer_merges m

1938: FOR UPDATE NOWAIT;
1939:
1940: CURSOR c2 IS
1941: SELECT CUST_RECEIPT_METHOD_ID
1942: FROM RA_CUST_RECEIPT_METHODS yt, ra_customer_merges m
1943: WHERE yt.customer_id = m.duplicate_id
1944: AND m.process_flag = 'N'
1945: AND m.request_id = req_id
1946: AND m.set_number = set_num

Line 1956: TYPE customer_merge_header_id_tab IS TABLE OF RA_CUSTOMER_MERGES.CUSTOMER_MERGE_HEADER_ID%TYPE INDEX BY BINARY_INTEGER;

1952: WHERE acct.cust_account_id = yt.customer_id
1953: AND acct.status = 'A' )
1954: FOR UPDATE NOWAIT;
1955:
1956: TYPE customer_merge_header_id_tab IS TABLE OF RA_CUSTOMER_MERGES.CUSTOMER_MERGE_HEADER_ID%TYPE INDEX BY BINARY_INTEGER;
1957: TYPE cust_receipt_method_id_tab IS TABLE OF RA_CUST_RECEIPT_METHODS.CUST_RECEIPT_METHOD_ID%TYPE INDEX BY BINARY_INTEGER;
1958: TYPE end_date_tab IS TABLE OF RA_CUST_RECEIPT_METHODS.END_DATE%TYPE INDEX BY BINARY_INTEGER;
1959:
1960: header_id customer_merge_header_id_tab;

Line 1993: FROM RA_CUST_RECEIPT_METHODS yt, ra_customer_merges m

1989: SELECT distinct CUSTOMER_MERGE_HEADER_ID,
1990: CUST_RECEIPT_METHOD_ID,
1991: END_DATE
1992: BULK COLLECT INTO header_id,receipt_id,end_date
1993: FROM RA_CUST_RECEIPT_METHODS yt, ra_customer_merges m
1994: WHERE (yt.CUSTOMER_ID = m.DUPLICATE_ID
1995: AND ( ( yt.SITE_USE_ID IS NULL
1996: AND NOT EXISTS (
1997: SELECT 'active accounts exist'

Line 2091: FROM RA_CUST_RECEIPT_METHODS yt, ra_customer_merges m

2087: FROM (
2088: SELECT distinct CUSTOMER_MERGE_HEADER_ID,
2089: CUST_RECEIPT_METHOD_ID,
2090: END_DATE
2091: FROM RA_CUST_RECEIPT_METHODS yt, ra_customer_merges m
2092: WHERE (yt.CUSTOMER_ID = m.DUPLICATE_ID
2093: AND ( ( yt.SITE_USE_ID IS NULL
2094: AND NOT EXISTS (
2095: SELECT 'active accounts exist'

Line 2121: | FROM ra_customer_merges m

Line 2146: FROM HZ_CUSTOMER_MERGE_LOG l1, RA_CUSTOMER_MERGES h

2142: , PROGRAM_ID=arp_standard.profile.program_id
2143: , PROGRAM_UPDATE_DATE=SYSDATE
2144: WHERE (CUST_RECEIPT_METHOD_ID) in (
2145: SELECT PRIMARY_KEY_ID
2146: FROM HZ_CUSTOMER_MERGE_LOG l1, RA_CUSTOMER_MERGES h
2147: WHERE h.CUSTOMER_MERGE_HEADER_ID = l1.MERGE_HEADER_ID
2148: AND l1.TABLE_NAME = 'RA_CUST_RECEIPT_METHODS'
2149: AND l1.REQUEST_ID = req_id
2150: AND h.set_number = set_num);

Line 2191: | FROM ra_customer_merges m

Line 2271: FROM ra_customer_merges

2267: from_account_status VARCHAR2(1);
2268:
2269: CURSOR accounts IS
2270: SELECT DISTINCT duplicate_id, customer_id
2271: FROM ra_customer_merges
2272: WHERE process_flag = 'N'
2273: AND request_id = req_id
2274: AND set_number = set_num
2275: AND duplicate_id <> customer_id;

Line 2410: FROM ra_customer_merges

2406:
2407: CURSOR sites IS
2408: SELECT DISTINCT duplicate_id, customer_id,
2409: duplicate_address_id, customer_address_id
2410: FROM ra_customer_merges
2411: WHERE process_flag = 'N'
2412: AND request_id = req_id
2413: AND set_number = set_num
2414: AND duplicate_address_id <> -1; --4693912

Line 2525: FROM hz_cust_account_roles yt, ra_customer_merges m

2521: l_party_id HZ_CUST_ACCOUNT_ROLES.PARTY_ID%TYPE;
2522:
2523: CURSOR c1 IS
2524: SELECT cust_account_role_id
2525: FROM hz_cust_account_roles yt, ra_customer_merges m
2526: WHERE cust_acct_site_id = m.duplicate_address_id
2527: AND m.process_flag = 'N'
2528: AND m.request_id = req_id
2529: AND m.set_number = set_num

Line 2535: FROM hz_cust_account_roles yt, ra_customer_merges m

2531: FOR UPDATE NOWAIT;
2532:
2533: CURSOR c2 IS
2534: SELECT cust_account_role_id
2535: FROM hz_cust_account_roles yt, ra_customer_merges m
2536: WHERE cust_account_id = m.duplicate_id
2537: AND m.process_flag = 'N'
2538: AND m.request_id = req_id
2539: AND m.set_number = set_num

Line 2548: FROM hz_cust_account_roles yt, ra_customer_merges m, hz_cust_accounts c

2544: -- Begin Bug No : 14201229
2545:
2546: CURSOR c2_1 IS
2547: SELECT cust_account_role_id
2548: FROM hz_cust_account_roles yt, ra_customer_merges m, hz_cust_accounts c
2549: WHERE yt.cust_account_id = m.duplicate_id
2550: AND c.cust_account_id = m.duplicate_id
2551: AND c.status <> 'A'
2552: AND m.process_flag = 'N'

Line 2561: FROM HZ_CUST_ACCOUNT_ROLES yt,ra_customer_merges m,HZ_CUST_ACCOUNTS ca,hz_relationships rel

2557: FOR UPDATE NOWAIT;
2558:
2559: CURSOR c3_1 is
2560: SELECT yt.party_id
2561: FROM HZ_CUST_ACCOUNT_ROLES yt,ra_customer_merges m,HZ_CUST_ACCOUNTS ca,hz_relationships rel
2562: WHERE m.customer_id=ca.cust_account_id
2563: AND ((ca.status = 'A' and yt.cust_acct_site_id is not null) or (ca.status <> 'A'))
2564: AND m.duplicate_id = yt.cust_account_id
2565: AND rel.party_id = yt.party_id

Line 2578: FROM HZ_CUST_ACCOUNT_ROLES yt,ra_customer_merges m,HZ_CUST_ACCOUNTS ca,hz_relationships rel

2574: -- End Bug No : 14201229
2575: ----Bug No: 5067291
2576: CURSOR c3 is
2577: SELECT yt.party_id
2578: FROM HZ_CUST_ACCOUNT_ROLES yt,ra_customer_merges m,HZ_CUST_ACCOUNTS ca,hz_relationships rel
2579: WHERE m.customer_id=ca.cust_account_id
2580: AND m.duplicate_id = yt.cust_account_id
2581: AND rel.party_id = yt.party_id
2582: AND rel.subject_type = 'PERSON'

Line 2658: FROM ra_customer_merges m

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)
2658: FROM ra_customer_merges m
2659: WHERE yt.cust_account_id = m.duplicate_id
2660: AND yt.cust_acct_site_id = m.duplicate_address_id
2661: AND m.request_id = req_id
2662: AND m.set_number = set_num

Line 2673: FROM ra_customer_merges m

2669: program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
2670: program_update_date = sysdate
2671: WHERE cust_acct_site_id IN (
2672: SELECT m.duplicate_address_id
2673: FROM ra_customer_merges m
2674: WHERE m.process_flag = 'N'
2675: AND m.request_id = req_id
2676: AND m.set_number = set_num )
2677: AND role_type = 'CONTACT'

Line 2693: FROM ra_customer_merges m

2689:
2690: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2691: SET cust_account_id = (
2692: SELECT m.customer_id
2693: FROM ra_customer_merges m
2694: WHERE yt.cust_account_id = m.duplicate_id
2695: AND m.request_id = req_id
2696: AND m.process_flag = 'N'
2697: AND m.set_number = set_num

Line 2708: FROM ra_customer_merges m

2704: program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
2705: program_update_date = sysdate
2706: WHERE cust_account_id IN (
2707: SELECT m.duplicate_id
2708: FROM ra_customer_merges m
2709: WHERE m.process_flag = 'N'
2710: AND m.request_id = req_id
2711: AND m.set_number = set_num)
2712: AND cust_acct_site_id IS NULL

Line 2721: FROM hz_cust_account_roles r, ra_customer_merges m

2717: -- Start Bug 4712462
2718: FOR rec IN (
2719: SELECT min(cust_account_role_id) cust_account_role_id, cust_account_id,
2720: nvl(cust_acct_site_id,-1) cust_acct_site_id,party_id
2721: FROM hz_cust_account_roles r, ra_customer_merges m
2722: where m.request_id = req_id AND m.set_number = set_num
2723: AND r.cust_account_id = m.customer_id AND m.process_flag = 'N'
2724: GROUP BY cust_account_id,cust_acct_site_id,status,party_id
2725: HAVING NVL(STATUS,'A') ='A' AND count(1) > 1) LOOP

Line 2781: FROM ra_customer_merges m

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)
2781: FROM ra_customer_merges m
2782: WHERE yt.cust_account_id = m.duplicate_id
2783: AND yt.cust_acct_site_id = m.duplicate_address_id
2784: AND m.request_id = req_id
2785: AND m.set_number = set_num

Line 2796: FROM ra_customer_merges m

2792: program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
2793: program_update_date = sysdate
2794: WHERE cust_acct_site_id IN (
2795: SELECT m.duplicate_address_id
2796: FROM ra_customer_merges m
2797: WHERE M.PROCESS_FLAG = 'N'
2798: AND m.request_id = req_id
2799: AND M.SET_NUMBER = SET_NUM )
2800: AND role_type = 'CONTACT'

Line 2816: FROM ra_customer_merges m

2812:
2813: UPDATE HZ_CUST_ACCOUNT_ROLES yt
2814: SET cust_account_id = (
2815: SELECT m.customer_id
2816: FROM ra_customer_merges m
2817: WHERE yt.cust_account_id = m.duplicate_id
2818: AND m.request_id = req_id
2819: AND m.process_flag = 'N'
2820: AND m.set_number = set_num

Line 2831: FROM ra_customer_merges m, hz_cust_accounts c

2827: program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
2828: program_update_date = sysdate
2829: WHERE cust_account_id IN (
2830: SELECT m.duplicate_id
2831: FROM ra_customer_merges m, hz_cust_accounts c
2832: WHERE m.process_flag = 'N'
2833: AND m.request_id = req_id
2834: AND m.set_number = set_num
2835: AND c.cust_account_id = m.duplicate_id

Line 2845: FROM hz_cust_account_roles r, ra_customer_merges m

2841: -- Start Bug 4712462
2842: FOR rec IN (
2843: SELECT min(cust_account_role_id) cust_account_role_id, cust_account_id,
2844: nvl(cust_acct_site_id,-1) cust_acct_site_id,party_id
2845: FROM hz_cust_account_roles r, ra_customer_merges m
2846: where m.request_id = req_id AND m.set_number = set_num
2847: AND r.cust_account_id = m.customer_id AND m.process_flag = 'N'
2848: GROUP BY cust_account_id,cust_acct_site_id,status,party_id
2849: HAVING NVL(STATUS,'A') ='A' AND count(1) > 1) LOOP

Line 2927: FROM hz_cust_contact_points yt, ra_customer_merges m

2923:
2924: --cursor c1 and c2 are used in 'inactivate' mode
2925: CURSOR c1 IS
2926: SELECT cust_contact_point_id
2927: FROM hz_cust_contact_points yt, ra_customer_merges m
2928: WHERE cust_account_site_id = m.duplicate_address_id
2929: AND m.process_flag = 'N'
2930: AND m.request_id = req_id
2931: AND m.set_number = set_num

Line 2936: FROM hz_cust_contact_points yt, ra_customer_merges m

2932: FOR UPDATE NOWAIT;
2933:
2934: CURSOR c2 is
2935: SELECT cust_contact_point_id
2936: FROM hz_cust_contact_points yt, ra_customer_merges m
2937: WHERE cust_account_id = m.duplicate_id
2938: AND m.process_flag = 'N'
2939: AND m.request_id = req_id
2940: AND m.set_number = set_num

Line 2970: FROM ra_customer_merges m

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)
2970: FROM ra_customer_merges m
2971: WHERE yt.cust_account_id = m.duplicate_id
2972: AND yt.cust_account_site_id = m.duplicate_address_id
2973: AND m.request_id = req_id
2974: AND m.set_number = set_num

Line 2985: FROM ra_customer_merges m

2981: program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
2982: program_update_date = sysdate
2983: WHERE cust_account_site_id IN (
2984: SELECT m.duplicate_address_id
2985: FROM ra_customer_merges m
2986: WHERE m.process_flag = 'N'
2987: AND m.request_id = req_id
2988: AND m.set_number = set_num );
2989:

Line 3003: FROM ra_customer_merges m

2999:
3000: UPDATE HZ_CUST_CONTACT_POINTS yt
3001: SET cust_account_id = (
3002: SELECT DISTINCT m.customer_id
3003: FROM ra_customer_merges m
3004: WHERE yt.cust_account_id = m.duplicate_id
3005: AND m.request_id = req_id
3006: AND m.process_flag = 'N'
3007: AND m.set_number = set_num),

Line 3017: FROM ra_customer_merges m

3013: program_id =hz_utility_v2pub.program_id,-- arp_standard.profile.program_id,
3014: program_update_date = sysdate
3015: WHERE cust_account_id IN (
3016: SELECT m.duplicate_id
3017: FROM ra_customer_merges m
3018: WHERE m.process_flag = 'N'
3019: AND m.request_id = req_id
3020: AND m.set_number = set_num)
3021: AND cust_account_site_id IS NULL;

Line 3092: FROM ra_customer_merges m, hz_cust_accounts c1

3088: SELECT party_usg_assignment_id
3089: FROM hz_party_usg_assignments pu
3090: WHERE party_usage_code = 'CUSTOMER'
3091: AND EXISTS (SELECT 'Y'
3092: FROM ra_customer_merges m, hz_cust_accounts c1
3093: WHERE m.duplicate_id = c1.cust_account_id
3094: AND c1.party_id = pu.party_id
3095: AND m.process_flag = 'N'
3096: AND m.request_id = req_id

Line 3142: FROM ra_customer_merges m, hz_cust_accounts c1

3138: WHERE c.party_id = pu.party_id
3139: AND c.status ='A'
3140: )
3141: AND EXISTS (SELECT 'Y'
3142: FROM ra_customer_merges m, hz_cust_accounts c1
3143: WHERE m.request_id = req_id
3144: AND m.process_flag = 'N'
3145: AND m.set_number = set_num
3146: AND m.delete_duplicate_flag = 'N'

Line 3201: FROM ra_customer_merges m, hz_cust_accounts c1

3197: and rownum=1)
3198:
3199:
3200: AND EXISTS (SELECT 'Y'
3201: FROM ra_customer_merges m, hz_cust_accounts c1
3202: WHERE m.request_id = req_id
3203: AND m.process_flag = 'N'
3204: AND m.set_number = set_num
3205: AND m.delete_duplicate_flag = 'Y'--Bug No. 4558392

Line 3257: FROM ra_customer_merges m, hz_cust_accounts c1

3253: AND c.status = 'A')
3254: )*/
3255: ----Bug No.4558392
3256: AND EXISTS (SELECT 'Y'
3257: FROM ra_customer_merges m, hz_cust_accounts c1
3258: WHERE m.request_id = req_id
3259: AND m.process_flag = 'N'
3260: AND m.set_number = set_num
3261: AND m.delete_duplicate_flag = 'Y'

Line 3548: FROM ra_customer_merges

3544: --merge context.
3545:
3546: CURSOR d IS
3547: SELECT DISTINCT duplicate_address_id --, customer_address_id
3548: FROM ra_customer_merges
3549: WHERE duplicate_id = p_from_account_id
3550: AND customer_id = p_to_account_id
3551: AND process_flag = 'N'
3552: AND request_id = p_req_id

Line 4635: FROM HZ_CUST_SITE_USES_ALL su, ra_customer_merges m --SSUptake

4631: ) IS
4632:
4633: CURSOR cust_site_uses IS
4634: SELECT site_use_id
4635: FROM HZ_CUST_SITE_USES_ALL su, ra_customer_merges m --SSUptake
4636: WHERE cust_acct_site_id = m.duplicate_address_id
4637: AND m.org_id = su.org_id --SSUptake
4638: AND m.process_flag = 'N'
4639: AND m.request_id = req_id

Line 4647: FROM HZ_CUST_ACCT_SITES_ALL addr, ra_customer_merges m --SSUptake

4643: FOR UPDATE NOWAIT;
4644:
4645: CURSOR cust_sites IS
4646: SELECT cust_acct_site_id
4647: FROM HZ_CUST_ACCT_SITES_ALL addr, ra_customer_merges m --SSUptake
4648: WHERE cust_acct_site_id = m.duplicate_address_id
4649: AND m.org_id = addr.org_id --SSUptake
4650: AND m.process_flag = 'N'
4651: AND m.request_id = req_id

Line 4659: FROM HZ_CUST_ACCOUNTS acct, ra_customer_merges m

4655: FOR UPDATE NOWAIT;
4656:
4657: CURSOR cust_accounts IS
4658: SELECT cust_account_id
4659: FROM HZ_CUST_ACCOUNTS acct, ra_customer_merges m
4660: WHERE cust_account_id = m.duplicate_id
4661: AND m.process_flag = 'N'
4662: AND m.request_id = req_id
4663: AND m.set_number = set_num

Line 4670: FROM HZ_CUST_ACCT_RELATE_ALL rel, ra_customer_merges m --SSUptake

4666: FOR UPDATE NOWAIT;
4667:
4668: CURSOR cust_rel1 IS
4669: SELECT rel.cust_account_id
4670: FROM HZ_CUST_ACCT_RELATE_ALL rel, ra_customer_merges m --SSUptake
4671: WHERE cust_account_id = m.duplicate_id
4672: AND m.org_id = rel.org_id --SSUptake
4673: AND m.process_flag = 'N'
4674: AND m.request_id = req_id

Line 4682: FROM HZ_CUST_ACCT_RELATE_ALL rel, ra_customer_merges m --SSUptake

4678: FOR UPDATE NOWAIT;
4679:
4680: CURSOR cust_rel2 IS
4681: SELECT rel.related_cust_account_id
4682: FROM HZ_CUST_ACCT_RELATE_ALL rel, ra_customer_merges m --SSUptake
4683: WHERE related_cust_account_id = m.duplicate_id
4684: AND m.org_id = rel.org_id --SSUptake
4685: AND m.process_flag = 'N'
4686: AND m.request_id = req_id

Line 4694: FROM RA_CUST_RECEIPT_METHODS yt, ra_customer_merges m

4690: FOR UPDATE NOWAIT;
4691:
4692: CURSOR cust_receipt_methods_site IS
4693: SELECT CUST_RECEIPT_METHOD_ID
4694: FROM RA_CUST_RECEIPT_METHODS yt, ra_customer_merges m
4695: WHERE yt.customer_id = m.duplicate_id
4696: AND yt.site_use_id = m.duplicate_site_id
4697: AND m.process_flag = 'N'
4698: AND m.request_id = req_id

Line 4705: FROM RA_CUST_RECEIPT_METHODS yt, ra_customer_merges m

4701: FOR UPDATE NOWAIT;
4702:
4703: CURSOR cust_receipt_methods_acct IS
4704: SELECT CUST_RECEIPT_METHOD_ID
4705: FROM RA_CUST_RECEIPT_METHODS yt, ra_customer_merges m
4706: WHERE yt.customer_id = m.duplicate_id
4707: AND m.process_flag = 'N'
4708: AND m.request_id = req_id
4709: AND m.set_number = set_num

Line 4725: AND party_id in (SELECT DISTINCT c.party_id from hz_cust_accounts c, ra_customer_merges m

4721: SELECT party_usg_assignment_id
4722: FROM hz_party_usg_assignments u
4723: WHERE party_usage_code = 'CUSTOMER'
4724: AND status_flag = 'D'
4725: AND party_id in (SELECT DISTINCT c.party_id from hz_cust_accounts c, ra_customer_merges m
4726: WHERE c.cust_account_id = m.duplicate_id
4727: AND m.process_flag = 'N'
4728: AND m.request_id = req_id
4729: AND m.set_number = set_num

Line 4749: AND party_id in (SELECT DISTINCT c.party_id from hz_cust_accounts c, ra_customer_merges m

4745:
4746: DELETE FROM hz_party_usg_assignments u
4747: WHERE party_usage_code = 'CUSTOMER'
4748: AND status_flag = 'D'
4749: AND party_id in (SELECT DISTINCT c.party_id from hz_cust_accounts c, ra_customer_merges m
4750: WHERE c.cust_account_id = m.duplicate_id
4751: AND m.process_flag = 'N'
4752: AND m.request_id = req_id
4753: AND m.set_number = set_num

Line 4776: FROM ra_customer_merges m

4772: owner_table_id in (
4773: select site_use_id from hz_cust_site_uses_all su
4774: where status = 'D'
4775: and exists ( SELECT 'Y'
4776: FROM ra_customer_merges m
4777: WHERE su.cust_acct_site_id = m.duplicate_address_id
4778: AND su.org_id = m.org_id
4779: AND m.process_flag = 'N'
4780: AND m.request_id = req_id

Line 4789: FROM ra_customer_merges m

4785:
4786: DELETE FROM HZ_CUST_SITE_USES_ALL su
4787: WHERE EXISTS (
4788: SELECT 'Y'
4789: FROM ra_customer_merges m
4790: WHERE m.duplicate_address_id = su.cust_acct_site_id
4791: AND m.org_id = su.org_id
4792: AND m.process_flag = 'N'
4793: AND m.request_id = req_id

Line 4820: FROM ra_customer_merges m

4816: select cust_acct_site_id from hz_cust_acct_sites_all sites --SSuptake
4817: where status = 'D'
4818: and EXISTS
4819: ( SELECT 'Y'
4820: FROM ra_customer_merges m
4821: WHERE m.duplicate_address_id = sites.cust_acct_site_id
4822: AND m.org_id = sites.org_id
4823: AND m.process_flag = 'N'
4824: AND m.request_id = req_id

Line 4831: FROM ra_customer_merges m

4827:
4828: DELETE FROM HZ_CUST_ACCT_SITES_ALL yt --SSUptake
4829: WHERE EXISTS (
4830: SELECT 'Y'
4831: FROM ra_customer_merges m
4832: WHERE m.duplicate_address_id = yt.cust_acct_site_id
4833: AND m.org_id = yt.org_id
4834: AND m.process_flag = 'N'
4835: AND m.request_id = req_id

Line 4858: FROM ra_customer_merges m

4854: owner_table_id in (
4855: select cust_account_id from hz_cust_accounts where
4856: status = 'D' and cust_account_id in
4857: ( SELECT m.duplicate_id
4858: FROM ra_customer_merges m
4859: WHERE m.process_flag = 'N'
4860: AND m.request_id = req_id
4861: AND m.set_number = set_num
4862: AND m.delete_duplicate_flag = 'Y' ));

Line 4867: FROM ra_customer_merges m

4863:
4864: DELETE FROM HZ_CUST_ACCOUNTS
4865: WHERE cust_account_id IN (
4866: SELECT m.duplicate_id
4867: FROM ra_customer_merges m
4868: WHERE m.process_flag = 'N'
4869: AND m.request_id = req_id
4870: AND m.set_number = set_num
4871: AND m.delete_duplicate_flag = 'Y' )

Line 4891: FROM ra_customer_merges m

4887:
4888: DELETE FROM HZ_CUST_ACCT_RELATE_ALL rel --SSUptake
4889: WHERE EXISTS (
4890: SELECT 'Y'
4891: FROM ra_customer_merges m
4892: WHERE m.duplicate_id = rel.cust_account_id
4893: AND m.org_id = rel.org_id
4894: AND m.process_flag = 'N'
4895: AND m.request_id = req_id

Line 4916: FROM ra_customer_merges m

4912:
4913: DELETE FROM HZ_CUST_ACCT_RELATE_ALL rel --SSUptake
4914: WHERE EXISTS (
4915: SELECT 'Y'
4916: FROM ra_customer_merges m
4917: WHERE m.duplicate_id = rel.related_cust_account_id
4918: AND m.org_id = rel.org_id
4919: AND m.process_flag = 'N'
4920: AND m.request_id = req_id

Line 4943: FROM ra_customer_merges m

4939:
4940: DELETE FROM RA_CUST_RECEIPT_METHODS yt
4941: WHERE (customer_id, site_use_id) in (
4942: SELECT m.duplicate_id, m.duplicate_site_id
4943: FROM ra_customer_merges m
4944: WHERE m.process_flag = 'N'
4945: AND m.request_id = req_id
4946: AND m.set_number = set_num
4947: AND m.delete_duplicate_flag = 'Y');

Line 4965: FROM ra_customer_merges m

4961:
4962: DELETE FROM RA_CUST_RECEIPT_METHODS yt
4963: WHERE customer_id in (
4964: SELECT m.duplicate_id
4965: FROM ra_customer_merges m
4966: WHERE m.process_flag = 'N'
4967: AND m.request_id = req_id
4968: AND m.set_number = set_num )
4969: AND site_use_id IS NULL

Line 5067: | we need to fill out the columns of ra_customer_merges for later query.

5063: | So we should not update, for example, merge-to's party site with merge-from's
5064: | party site if there exists a party site on the location for merge-to's party.
5065: |
5066: | After create account site for merge-to customer,
5067: | we need to fill out the columns of ra_customer_merges for later query.
5068: | -- customer_address_id <-- cust_acct_site_id
5069: | -- customer_site_id <-- site_use_id
5070: | -- customer_ref <-- orig_system_reference
5071: | -- customer_primary_flag <-- primary_flag

Line 5141: | in ra_customer_merges.

5137: | flag is checked. Added a call to
5138: | hz_cust_account_merge_v2pvt.create_party_site_use
5139: | in procedure create_same_sites.
5140: | S.V.Sowjanya 02-DEC-04 Bug No: 3959776. Updated column customer_site_number
5141: | in ra_customer_merges.
5142: | S.V.Sowjanya 04-JAN-05 Bug No: 4018346. Assigned null values to l_customer_location,
5143: | l_customer_site_id in the beginning of the loop for
5144: | cursor sites_need_to_create and removed nvl condition
5145: | for l_customer_location in the update statement of ra_customer_merges

Line 5145: | for l_customer_location in the update statement of ra_customer_merges

5141: | in ra_customer_merges.
5142: | S.V.Sowjanya 04-JAN-05 Bug No: 4018346. Assigned null values to l_customer_location,
5143: | l_customer_site_id in the beginning of the loop for
5144: | cursor sites_need_to_create and removed nvl condition
5145: | for l_customer_location in the update statement of ra_customer_merges
5146: | at the end of the loop for cursor sites_need_to_create.
5147: | S V Sowjanya 10-AUG-05 Bug No:4492628. Moved code that copies party_site_uses
5148: | to copy party_site_use after the creation of account_site
5149: | Kalyana 12-Oct-07 Bug No: 6469732 Modified the procedure create_same_site so that if already an Active Customer

Line 5166: FROM ra_customer_merges

5162: --new sites.
5163: CURSOR sites_need_to_create IS
5164: SELECT duplicate_id, duplicate_address_id, duplicate_site_id,
5165: duplicate_site_code, customer_id,org_id, delete_duplicate_flag --SSUptake
5166: FROM ra_customer_merges
5167: WHERE duplicate_id <> customer_id
5168: AND process_flag = 'N'
5169: AND request_id = req_id
5170: AND set_number = set_num

Line 6514: FROM ra_customer_merges

6510: --since location is madatory. We have to enforce system generate.
6511: --Only if the customer_location value is NULL.
6512:
6513: SELECT customer_location into l_customer_location
6514: FROM ra_customer_merges
6515: WHERE customer_id = l_customer_id
6516: AND duplicate_site_id = l_duplicate_site_id
6517: --Bug Fix 2929527
6518: AND ROWNUM=1;

Line 6846: --Update ra_customer_merges table with the new info. we created.

6842: END; --end of creating
6843:
6844: --Will migrate the contacts (phones and emails) during merge process
6845:
6846: --Update ra_customer_merges table with the new info. we created.
6847: --Select customer orig system reference.
6848: SELECT orig_system_reference
6849: INTO l_customer_ref
6850: FROM hz_cust_acct_sites_all

Line 6870: UPDATE ra_customer_merges

6866: FROM hz_party_sites
6867: WHERE party_site_id = ( SELECT party_site_id
6868: FROM hz_cust_acct_sites_all
6869: WHERE cust_acct_site_id = l_customer_address_id);
6870: UPDATE ra_customer_merges
6871: SET customer_address_id = l_customer_address_id,
6872: customer_ref = l_customer_ref,
6873: customer_primary_flag = nvl(l_customer_primary_flag,'N'),
6874: customer_site_id = nvl(l_customer_site_id,-99),

Line 6942: l_customer_merge_header_id RA_CUSTOMER_MERGES.customer_merge_header_id%TYPE;

6938: PROCEDURE merge_history(req_id NUMBER,
6939: set_num NUMBER,
6940: status OUT NOCOPY NUMBER) IS
6941:
6942: l_customer_merge_header_id RA_CUSTOMER_MERGES.customer_merge_header_id%TYPE;
6943:
6944:
6945: BEGIN
6946:

Line 7192: from ra_customer_merges cm

7188: c.duns_extension ,
7189: c.advance_payment_indicator,
7190: req_id
7191: FROM (select distinct duplicate_id , customer_merge_header_id , customer_id
7192: from ra_customer_merges cm
7193: where cm.process_flag = 'N'
7194: and cm.request_id = req_id
7195: and cm.set_number = set_num) , HZ_CUST_ACCOUNTS c
7196: WHERE c.cust_account_id = duplicate_id

Line 7352: from ra_customer_merges cm

7348: ar.created_by_module ,
7349: ar.application_id ,
7350: req_id
7351: FROM(select distinct duplicate_id,duplicate_address_id,customer_merge_header_id
7352: from ra_customer_merges cm
7353: where cm.process_flag = 'N'
7354: and cm.request_id = req_id
7355: and cm.set_number = set_num
7356: and cm.duplicate_id <> cm.customer_id), hz_cust_account_roles ar

Line 7590: from ra_customer_merges cm

7586: cp.last_credit_review_date ,
7587: req_id,
7588: cp.automatch_set_id --bug 8477178
7589: FROM(select distinct duplicate_id,duplicate_site_id,customer_merge_header_id
7590: from ra_customer_merges cm
7591: where cm.process_flag = 'N'
7592: and cm.request_id = req_id
7593: and cm.set_number = set_num
7594: and cm.duplicate_id <> cm.customer_id ), hz_customer_profiles cp

Line 7777: from ra_customer_merges cm

7773: pa.created_by_module ,
7774: pa.application_id ,
7775: req_id
7776: FROM (select distinct duplicate_id,duplicate_site_id,customer_merge_header_id
7777: from ra_customer_merges cm
7778: where cm.process_flag = 'N'
7779: and cm.request_id = req_id
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)

Line 7947: from ra_customer_merges cm

7943: acs.created_by_module ,
7944: acs.application_id ,
7945: req_id
7946: FROM (select distinct duplicate_id,duplicate_address_id,customer_merge_header_id,org_id
7947: from ra_customer_merges cm
7948: where cm.process_flag = 'N'
7949: and cm.request_id = req_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

Line 8199: from ra_customer_merges cm

8195: su.created_by_module ,
8196: su.application_id ,
8197: req_id
8198: FROM (select distinct duplicate_site_id,customer_merge_header_id,org_id
8199: from ra_customer_merges cm
8200: where cm.process_flag = 'N'
8201: and cm.request_id = req_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

Line 8293: from ra_customer_merges cm

8289: yt.application_id ,
8290: req_id ,
8291: cust_acct_relate_id
8292: FROM (select distinct duplicate_id, customer_merge_header_id,org_id
8293: from ra_customer_merges cm
8294: where cm.process_flag = 'N'
8295: and cm.request_id = req_id
8296: and cm.set_number = set_num
8297: and cm.duplicate_id <> cm.customer_id) m,hz_cust_acct_relate_all yt --SSUptake