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 HZ_CUST_ACCOUNTS

Line 1132: | merge in HZ_CUST_ACCOUNTS.

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 1235: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );

1231: /* locking tables by opening and closing cursors */
1232: IF process_mode = 'LOCK' THEN
1233:
1234: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
1235: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1236:
1237: OPEN c1;
1238: CLOSE c1;
1239:

Line 1248: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );

1244:
1245: /*************** 'inactivate' mode ***************/
1246:
1247: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1248: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1249:
1250: --inactivate customer account
1251:
1252: UPDATE HZ_CUST_ACCOUNTS yt

Line 1252: UPDATE HZ_CUST_ACCOUNTS yt

1248: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1249:
1250: --inactivate customer account
1251:
1252: UPDATE HZ_CUST_ACCOUNTS yt
1253: SET status = 'I',
1254: last_update_date = sysdate,
1255: last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
1256: last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,

Line 1286: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );

1282: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
1283: --Physically delete them after merge.
1284:
1285: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1286: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1287:
1288: --'delete' customer account
1289:
1290: UPDATE HZ_CUST_ACCOUNTS yt

Line 1290: UPDATE HZ_CUST_ACCOUNTS yt

1286: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1287:
1288: --'delete' customer account
1289:
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

Line 1322: p_owner_table_name =>'HZ_CUST_ACCOUNTS',

1318: p_init_msg_list => FND_API.G_FALSE,
1319: p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1320: p_old_owner_table_id => x_from_cust_id,
1321: p_new_owner_table_id => x_to_cust_id,
1322: p_owner_table_name =>'HZ_CUST_ACCOUNTS',
1323: p_orig_system => null,
1324: p_orig_system_reference => null,
1325: p_reason_code => 'MERGED',
1326: x_return_status => x_return_status,

Line 1420: -- FROM hz_cust_accounts acct

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
1421: -- WHERE acct.cust_account_id = yt.cust_account_id
1422: -- AND acct.status = 'A' )
1423: m.request_id = req_id
1424: AND m.process_flag = 'N'

Line 1437: -- FROM hz_cust_accounts acct

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
1438: -- WHERE acct.cust_account_id = yt.related_cust_account_id
1439: -- AND acct.status = 'A' )
1440: m.request_id = req_id
1441: AND m.process_flag = 'N'

Line 1485: SELECT account_number FROM hz_cust_accounts

1481: AND yt.status = 'A'
1482: AND yt.org_id = p_org_id
1483: AND ROWNUM =1;
1484: CURSOR c_acct_num(p_acct_id NUMBER) IS
1485: SELECT account_number FROM hz_cust_accounts
1486: WHERE cust_account_id=p_acct_id;
1487:
1488: l_to_cust_account_id NUMBER(15);
1489: l_to_related_cust_account_id NUMBER(15);

Line 1748: -- FROM hz_cust_accounts acct

1744: SET status = 'D'
1745: WHERE
1746: --NOT EXISTS (
1747: -- SELECT 'accounts exist'
1748: -- FROM hz_cust_accounts acct
1749: -- WHERE acct.cust_account_id = yt.cust_account_id
1750: -- AND acct.status <> 'D' )
1751: EXISTS ( --SSUptake
1752: SELECT 'Y'

Line 1809: -- FROM hz_cust_accounts acct

1805: SET status = 'D'
1806: WHERE
1807: --NOT EXISTS (
1808: -- SELECT 'accounts exist'
1809: -- FROM hz_cust_accounts acct
1810: -- WHERE acct.cust_account_id = yt.related_cust_account_id
1811: -- AND acct.status <> 'D' )
1812: EXISTS (
1813: SELECT 'Y'

Line 1951: FROM hz_cust_accounts acct

1947: AND m.delete_duplicate_flag = 'N'
1948: AND site_use_id IS NULL
1949: AND NOT EXISTS (
1950: SELECT 'active accounts exist'
1951: FROM hz_cust_accounts acct
1952: WHERE acct.cust_account_id = yt.customer_id
1953: AND acct.status = 'A' )
1954: FOR UPDATE NOWAIT;
1955:

Line 1998: FROM hz_cust_accounts acct

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'
1998: FROM hz_cust_accounts acct
1999: WHERE acct.cust_account_id = yt.customer_id
2000: AND acct.status = 'A' )
2001: )
2002: OR (yt.site_use_id = m.DUPLICATE_SITE_ID)

Line 2096: FROM hz_cust_accounts acct

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'
2096: FROM hz_cust_accounts acct
2097: WHERE acct.cust_account_id = yt.customer_id
2098: AND acct.status = 'A' )
2099: )
2100: OR (yt.site_use_id = m.DUPLICATE_SITE_ID)

Line 2199: | FROM hz_cust_accounts acct

Line 2296: FROM hz_cust_accounts

2292: to_account_id );
2293: **/
2294:
2295: SELECT party_id, status into from_party_id, from_account_status
2296: FROM hz_cust_accounts
2297: WHERE cust_account_id = from_account_id;
2298:
2299: SELECT party_id into to_party_id
2300: FROM hz_cust_accounts

Line 2300: FROM hz_cust_accounts

2296: FROM hz_cust_accounts
2297: WHERE cust_account_id = from_account_id;
2298:
2299: SELECT party_id into to_party_id
2300: FROM hz_cust_accounts
2301: WHERE cust_account_id = to_account_id;
2302:
2303: /**
2304: arp_message.set_line(

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 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 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 3137: AND NOT EXISTS( SELECT 'Y' FROM hz_cust_accounts c

3133: --Bug No. 4558392
3134: WHERE party_usage_code = 'CUSTOMER'
3135: AND nvl(effective_end_date,sysdate+1)>=sysdate
3136: AND status_flag = 'A'
3137: AND NOT EXISTS( SELECT 'Y' FROM hz_cust_accounts c
3138: WHERE c.party_id = pu.party_id
3139: AND c.status ='A'
3140: )
3141: AND EXISTS (SELECT 'Y'

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 3181: AND EXISTS( SELECT 'Y' FROM hz_cust_accounts c

3177:
3178: WHERE party_usage_code = 'CUSTOMER'
3179: AND nvl(effective_end_date,sysdate+1)>=sysdate
3180: AND status_flag = 'A'
3181: AND EXISTS( SELECT 'Y' FROM hz_cust_accounts c
3182: WHERE c.party_id = pu.party_id
3183: AND c.status ='I'
3184: AND rownum=1
3185: )

Line 3186: AND NOT EXISTS( SELECT 'Y' FROM hz_cust_accounts c

3182: WHERE c.party_id = pu.party_id
3183: AND c.status ='I'
3184: AND rownum=1
3185: )
3186: AND NOT EXISTS( SELECT 'Y' FROM hz_cust_accounts c
3187: WHERE c.party_id = pu.party_id
3188: AND c.status ='A'
3189: AND rownum=1
3190: )

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 3234: FROM hz_cust_accounts c --delete if from account is the only account for merge-from party

3230: WHERE pu.party_usage_code = 'CUSTOMER'
3231:
3232: AND (
3233: NOT EXISTS (SELECT 'Y'
3234: FROM hz_cust_accounts c --delete if from account is the only account for merge-from party
3235: WHERE c.party_id = pu.party_id
3236: AND c.status in ('A','I')
3237: )
3238: --Bug No.4558392

Line 3251: AND NOT EXISTS (SELECT 'Y' FROM hz_cust_accounts c

3247: )
3248:
3249:
3250: /* OR (pu.status_flag = 'A' AND trunc(sysdate) < pu.effective_end_date --delete only active usage if merge-from party has inactive accounts
3251: AND NOT EXISTS (SELECT 'Y' FROM hz_cust_accounts c
3252: WHERE c.party_id = pu.party_id
3253: AND c.status = 'A')
3254: )*/
3255: ----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 3369: hz_cust_accounts acct

3365: org.org_contact_id
3366: FROM hz_cust_account_roles acct_role,
3367: hz_org_contacts org,
3368: hz_relationships rel,
3369: hz_cust_accounts acct
3370: WHERE acct_role.role_type = 'CONTACT'
3371: AND acct_role.cust_account_id = p_from_account_id
3372: AND acct_role.cust_acct_site_id IS NULL
3373: AND acct_role.party_id = rel.party_id

Line 3528: hz_cust_accounts acct

3524: -- org.party_site_id
3525: FROM hz_cust_account_roles acct_role,
3526: hz_org_contacts org,
3527: hz_relationships rel,
3528: hz_cust_accounts acct
3529: WHERE acct_role.role_type = 'CONTACT'
3530: AND acct_role.cust_account_id = p_from_account_id
3531: AND acct_role.cust_acct_site_id = from_site_id
3532: AND acct_role.party_id = rel.party_id

Line 4355: FROM HZ_RELATIONSHIPS,HZ_CUST_ACCOUNTS ACCT

4351: AND TO_CHAR(SUBJECT_ID) ||
4352: RELATIONSHIP_CODE = (
4353: SELECT TO_CHAR(SUBJECT_ID) ||
4354: RELATIONSHIP_CODE
4355: FROM HZ_RELATIONSHIPS,HZ_CUST_ACCOUNTS ACCT
4356: WHERE RELATIONSHIP_ID =
4357: p_from_party_rel_id
4358: AND OBJECT_ID = ACCT.PARTY_ID
4359: AND ACCT.CUST_ACCOUNT_ID = p_from_account_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 4714: FROM hz_cust_accounts acct

4710: AND m.delete_duplicate_flag = 'Y'
4711: AND site_use_id IS NULL
4712: AND NOT EXISTS (
4713: SELECT 'accounts exist'
4714: FROM hz_cust_accounts acct
4715: WHERE acct.cust_account_id = yt.customer_id
4716: AND acct.status <> 'D' )
4717: FOR UPDATE NOWAIT;
4718:

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 4846: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );

4842:
4843: /*****************************************************/
4844:
4845: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4846: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
4847:
4848: --lock rows
4849: OPEN cust_accounts;
4850: CLOSE cust_accounts;

Line 4853: owner_table_name = 'HZ_CUST_ACCOUNTS' and

4849: OPEN cust_accounts;
4850: CLOSE cust_accounts;
4851:
4852: delete from hz_orig_sys_references where
4853: owner_table_name = 'HZ_CUST_ACCOUNTS' and
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

Line 4855: select cust_account_id from hz_cust_accounts where

4851:
4852: delete from hz_orig_sys_references where
4853: owner_table_name = 'HZ_CUST_ACCOUNTS' and
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'

Line 4864: DELETE FROM HZ_CUST_ACCOUNTS

4860: AND m.request_id = req_id
4861: AND m.set_number = set_num
4862: AND m.delete_duplicate_flag = 'Y' ));
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'

Line 4972: FROM hz_cust_accounts acct

4968: AND m.set_number = set_num )
4969: AND site_use_id IS NULL
4970: AND NOT EXISTS (
4971: SELECT 'accounts exist'
4972: FROM hz_cust_accounts acct
4973: WHERE acct.cust_account_id = yt.customer_id
4974: AND status <> 'D' );
4975:
4976: g_count := sql%rowcount;

Line 5397: FROM hz_cust_accounts

5393: WHERE party_site_id = l_duplicate_party_site_id;
5394:
5395: --Select merge-to's party
5396: SELECT party_id INTO l_merge_to_party_id
5397: FROM hz_cust_accounts
5398: WHERE cust_account_id = l_customer_id;
5399:
5400: --Check if user tries to create new address which he/she already has.
5401: --Since account sites has been stripped by org, we do this check in

Line 6949: ---------Insert into HZ_CUST_ACCOUNTS_M--------------

6945: BEGIN
6946:
6947: arp_message.set_line( 'ARP_CMERGE_ARCUS.Merge_History()+' );
6948:
6949: ---------Insert into HZ_CUST_ACCOUNTS_M--------------
6950: INSERT INTO HZ_CUST_ACCOUNTS_M(
6951: customer_merge_header_id,
6952: cust_account_id,
6953: party_id ,

Line 6950: INSERT INTO HZ_CUST_ACCOUNTS_M(

6946:
6947: arp_message.set_line( 'ARP_CMERGE_ARCUS.Merge_History()+' );
6948:
6949: ---------Insert into HZ_CUST_ACCOUNTS_M--------------
6950: INSERT INTO HZ_CUST_ACCOUNTS_M(
6951: customer_merge_header_id,
6952: cust_account_id,
6953: party_id ,
6954: last_update_date ,

Line 7195: and cm.set_number = set_num) , HZ_CUST_ACCOUNTS c

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
7197: AND duplicate_id <> customer_id;
7198:
7199: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCOUNTS_M');

Line 7199: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCOUNTS_M');

7195: and cm.set_number = set_num) , HZ_CUST_ACCOUNTS c
7196: WHERE c.cust_account_id = duplicate_id
7197: AND duplicate_id <> customer_id;
7198:
7199: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCOUNTS_M');
7200:
7201:
7202:
7203: ---------Insert into hz_cust_account_roles_m--------------