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 997: | merge in HZ_CUST_ACCOUNTS.

Line 1047: FROM hz_cust_accounts yt, ra_customer_merges m

1043:
1044: --cursor c1 is used in 'inactivate' mode.
1045: CURSOR c1 IS
1046: SELECT yt.cust_account_id
1047: FROM hz_cust_accounts yt, ra_customer_merges m
1048: WHERE cust_account_id = m.duplicate_id
1049: AND m.process_flag = 'N'
1050: AND m.request_id = req_id
1051: AND m.set_number = set_num

Line 1064: FROM hz_cust_accounts yt, ra_customer_merges m

1060:
1061: --cursor c2 is used in 'delete' mode.
1062: CURSOR c2 IS
1063: SELECT cust_account_id
1064: FROM hz_cust_accounts yt, ra_customer_merges m
1065: WHERE cust_account_id = m.duplicate_id
1066: AND m.process_flag = 'N'
1067: AND m.request_id = req_id
1068: AND m.set_number = set_num

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

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:

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

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

Line 1117: UPDATE HZ_CUST_ACCOUNTS yt

1113: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1114:
1115: --inactivate customer account
1116:
1117: UPDATE HZ_CUST_ACCOUNTS yt
1118: SET status = 'I',
1119: last_update_date = sysdate,
1120: last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
1121: last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,

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

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

Line 1155: UPDATE HZ_CUST_ACCOUNTS yt

1151: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCOUNTS', FALSE );
1152:
1153: --'delete' customer account
1154:
1155: UPDATE HZ_CUST_ACCOUNTS yt
1156: SET status = 'D'
1157: WHERE cust_account_id IN (
1158: SELECT m.duplicate_id
1159: FROM ra_customer_merges m

Line 1187: p_owner_table_name =>'HZ_CUST_ACCOUNTS',

1183: p_init_msg_list => FND_API.G_FALSE,
1184: p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1185: p_old_owner_table_id => x_from_cust_id,
1186: p_new_owner_table_id => x_to_cust_id,
1187: p_owner_table_name =>'HZ_CUST_ACCOUNTS',
1188: p_orig_system => null,
1189: p_orig_system_reference => null,
1190: p_reason_code => 'MERGED',
1191: x_return_status => x_return_status,

Line 1285: -- FROM hz_cust_accounts acct

1281: FROM hz_cust_acct_relate_all yt, ra_customer_merges m --SSUptake
1282: WHERE
1283: --NOT EXISTS (
1284: -- SELECT 'active accounts exist'
1285: -- FROM hz_cust_accounts acct
1286: -- WHERE acct.cust_account_id = yt.cust_account_id
1287: -- AND acct.status = 'A' )
1288: m.request_id = req_id
1289: AND m.process_flag = 'N'

Line 1302: -- FROM hz_cust_accounts acct

1298: FROM hz_cust_acct_relate_all yt, ra_customer_merges m --SSUptake
1299: WHERE
1300: --NOT EXISTS (
1301: -- SELECT 'active accounts exist'
1302: -- FROM hz_cust_accounts acct
1303: -- WHERE acct.cust_account_id = yt.related_cust_account_id
1304: -- AND acct.status = 'A' )
1305: m.request_id = req_id
1306: AND m.process_flag = 'N'

Line 1350: SELECT account_number FROM hz_cust_accounts

1346: AND yt.status = 'A'
1347: AND yt.org_id = p_org_id
1348: AND ROWNUM =1;
1349: CURSOR c_acct_num(p_acct_id NUMBER) IS
1350: SELECT account_number FROM hz_cust_accounts
1351: WHERE cust_account_id=p_acct_id;
1352:
1353: l_to_cust_account_id NUMBER(15);
1354: l_to_related_cust_account_id NUMBER(15);

Line 1613: -- FROM hz_cust_accounts acct

1609: SET status = 'D'
1610: WHERE
1611: --NOT EXISTS (
1612: -- SELECT 'accounts exist'
1613: -- FROM hz_cust_accounts acct
1614: -- WHERE acct.cust_account_id = yt.cust_account_id
1615: -- AND acct.status <> 'D' )
1616: EXISTS ( --SSUptake
1617: SELECT 'Y'

Line 1674: -- FROM hz_cust_accounts acct

1670: SET status = 'D'
1671: WHERE
1672: --NOT EXISTS (
1673: -- SELECT 'accounts exist'
1674: -- FROM hz_cust_accounts acct
1675: -- WHERE acct.cust_account_id = yt.related_cust_account_id
1676: -- AND acct.status <> 'D' )
1677: EXISTS (
1678: SELECT 'Y'

Line 1816: FROM hz_cust_accounts acct

1812: AND m.delete_duplicate_flag = 'N'
1813: AND site_use_id IS NULL
1814: AND NOT EXISTS (
1815: SELECT 'active accounts exist'
1816: FROM hz_cust_accounts acct
1817: WHERE acct.cust_account_id = yt.customer_id
1818: AND acct.status = 'A' )
1819: FOR UPDATE NOWAIT;
1820:

Line 1863: FROM hz_cust_accounts acct

1859: WHERE (yt.CUSTOMER_ID = m.DUPLICATE_ID
1860: AND ( ( yt.SITE_USE_ID IS NULL
1861: AND NOT EXISTS (
1862: SELECT 'active accounts exist'
1863: FROM hz_cust_accounts acct
1864: WHERE acct.cust_account_id = yt.customer_id
1865: AND acct.status = 'A' )
1866: )
1867: OR (yt.site_use_id = m.DUPLICATE_SITE_ID)

Line 1961: FROM hz_cust_accounts acct

1957: WHERE (yt.CUSTOMER_ID = m.DUPLICATE_ID
1958: AND ( ( yt.SITE_USE_ID IS NULL
1959: AND NOT EXISTS (
1960: SELECT 'active accounts exist'
1961: FROM hz_cust_accounts acct
1962: WHERE acct.cust_account_id = yt.customer_id
1963: AND acct.status = 'A' )
1964: )
1965: OR (yt.site_use_id = m.DUPLICATE_SITE_ID)

Line 2064: | FROM hz_cust_accounts acct

Line 2160: FROM hz_cust_accounts

2156: to_account_id );
2157: **/
2158:
2159: SELECT party_id into from_party_id
2160: FROM hz_cust_accounts
2161: WHERE cust_account_id = from_account_id;
2162:
2163: SELECT party_id into to_party_id
2164: FROM hz_cust_accounts

Line 2164: FROM hz_cust_accounts

2160: FROM hz_cust_accounts
2161: WHERE cust_account_id = from_account_id;
2162:
2163: SELECT party_id into to_party_id
2164: FROM hz_cust_accounts
2165: WHERE cust_account_id = to_account_id;
2166:
2167: /**
2168: arp_message.set_line(

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

2403: FOR UPDATE NOWAIT;
2404: ----Bug No: 5067291
2405: CURSOR c3 is
2406: SELECT yt.party_id
2407: FROM HZ_CUST_ACCOUNT_ROLES yt,ra_customer_merges m,HZ_CUST_ACCOUNTS ca,hz_relationships rel
2408: WHERE m.customer_id=ca.cust_account_id
2409: AND m.duplicate_id = yt.cust_account_id
2410: AND rel.party_id = yt.party_id
2411: AND rel.subject_type = 'PERSON'

Line 2784: FROM ra_customer_merges m, hz_cust_accounts c1

2780: SELECT party_usg_assignment_id
2781: FROM hz_party_usg_assignments pu
2782: WHERE party_usage_code = 'CUSTOMER'
2783: AND EXISTS (SELECT 'Y'
2784: FROM ra_customer_merges m, hz_cust_accounts c1
2785: WHERE m.duplicate_id = c1.cust_account_id
2786: AND c1.party_id = pu.party_id
2787: AND m.process_flag = 'N'
2788: AND m.request_id = req_id

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

2825: --Bug No. 4558392
2826: WHERE party_usage_code = 'CUSTOMER'
2827: AND nvl(effective_end_date,sysdate+1)>=sysdate
2828: AND status_flag = 'A'
2829: AND NOT EXISTS( SELECT 'Y' FROM hz_cust_accounts c
2830: WHERE c.party_id = pu.party_id
2831: AND c.status ='A'
2832: )
2833: AND EXISTS (SELECT 'Y'

Line 2834: FROM ra_customer_merges m, hz_cust_accounts c1

2830: WHERE c.party_id = pu.party_id
2831: AND c.status ='A'
2832: )
2833: AND EXISTS (SELECT 'Y'
2834: FROM ra_customer_merges m, hz_cust_accounts c1
2835: WHERE m.request_id = req_id
2836: AND m.process_flag = 'N'
2837: AND m.set_number = set_num
2838: AND m.delete_duplicate_flag = 'N'

Line 2873: AND EXISTS( SELECT 'Y' FROM hz_cust_accounts c

2869:
2870: WHERE party_usage_code = 'CUSTOMER'
2871: AND nvl(effective_end_date,sysdate+1)>=sysdate
2872: AND status_flag = 'A'
2873: AND EXISTS( SELECT 'Y' FROM hz_cust_accounts c
2874: WHERE c.party_id = pu.party_id
2875: AND c.status ='I'
2876: AND rownum=1
2877: )

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

2874: WHERE c.party_id = pu.party_id
2875: AND c.status ='I'
2876: AND rownum=1
2877: )
2878: AND NOT EXISTS( SELECT 'Y' FROM hz_cust_accounts c
2879: WHERE c.party_id = pu.party_id
2880: AND c.status ='A'
2881: AND rownum=1
2882: )

Line 2893: FROM ra_customer_merges m, hz_cust_accounts c1

2889: and rownum=1)
2890:
2891:
2892: AND EXISTS (SELECT 'Y'
2893: FROM ra_customer_merges m, hz_cust_accounts c1
2894: WHERE m.request_id = req_id
2895: AND m.process_flag = 'N'
2896: AND m.set_number = set_num
2897: AND m.delete_duplicate_flag = 'Y'--Bug No. 4558392

Line 2926: FROM hz_cust_accounts c --delete if from account is the only account for merge-from party

2922: WHERE pu.party_usage_code = 'CUSTOMER'
2923:
2924: AND (
2925: NOT EXISTS (SELECT 'Y'
2926: FROM hz_cust_accounts c --delete if from account is the only account for merge-from party
2927: WHERE c.party_id = pu.party_id
2928: AND c.status in ('A','I')
2929: )
2930: --Bug No.4558392

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

2939: )
2940:
2941:
2942: /* OR (pu.status_flag = 'A' AND trunc(sysdate) < pu.effective_end_date --delete only active usage if merge-from party has inactive accounts
2943: AND NOT EXISTS (SELECT 'Y' FROM hz_cust_accounts c
2944: WHERE c.party_id = pu.party_id
2945: AND c.status = 'A')
2946: )*/
2947: ----Bug No.4558392

Line 2949: FROM ra_customer_merges m, hz_cust_accounts c1

2945: AND c.status = 'A')
2946: )*/
2947: ----Bug No.4558392
2948: AND EXISTS (SELECT 'Y'
2949: FROM ra_customer_merges m, hz_cust_accounts c1
2950: WHERE m.request_id = req_id
2951: AND m.process_flag = 'N'
2952: AND m.set_number = set_num
2953: AND m.delete_duplicate_flag = 'Y'

Line 3061: hz_cust_accounts acct

3057: org.org_contact_id
3058: FROM hz_cust_account_roles acct_role,
3059: hz_org_contacts org,
3060: hz_relationships rel,
3061: hz_cust_accounts acct
3062: WHERE acct_role.role_type = 'CONTACT'
3063: AND acct_role.cust_account_id = p_from_account_id
3064: AND acct_role.cust_acct_site_id IS NULL
3065: AND acct_role.party_id = rel.party_id

Line 3220: hz_cust_accounts acct

3216: -- org.party_site_id
3217: FROM hz_cust_account_roles acct_role,
3218: hz_org_contacts org,
3219: hz_relationships rel,
3220: hz_cust_accounts acct
3221: WHERE acct_role.role_type = 'CONTACT'
3222: AND acct_role.cust_account_id = p_from_account_id
3223: AND acct_role.cust_acct_site_id = from_site_id
3224: AND acct_role.party_id = rel.party_id

Line 4047: FROM HZ_RELATIONSHIPS,HZ_CUST_ACCOUNTS ACCT

4043: AND TO_CHAR(SUBJECT_ID) ||
4044: RELATIONSHIP_CODE = (
4045: SELECT TO_CHAR(SUBJECT_ID) ||
4046: RELATIONSHIP_CODE
4047: FROM HZ_RELATIONSHIPS,HZ_CUST_ACCOUNTS ACCT
4048: WHERE RELATIONSHIP_ID =
4049: p_from_party_rel_id
4050: AND OBJECT_ID = ACCT.PARTY_ID
4051: AND ACCT.CUST_ACCOUNT_ID = p_from_account_id

Line 4349: FROM HZ_CUST_ACCOUNTS acct, ra_customer_merges m

4345: FOR UPDATE NOWAIT;
4346:
4347: CURSOR cust_accounts IS
4348: SELECT cust_account_id
4349: FROM HZ_CUST_ACCOUNTS acct, ra_customer_merges m
4350: WHERE cust_account_id = m.duplicate_id
4351: AND m.process_flag = 'N'
4352: AND m.request_id = req_id
4353: AND m.set_number = set_num

Line 4404: FROM hz_cust_accounts acct

4400: AND m.delete_duplicate_flag = 'Y'
4401: AND site_use_id IS NULL
4402: AND NOT EXISTS (
4403: SELECT 'accounts exist'
4404: FROM hz_cust_accounts acct
4405: WHERE acct.cust_account_id = yt.customer_id
4406: AND acct.status <> 'D' )
4407: FOR UPDATE NOWAIT;
4408:

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

4411: SELECT party_usg_assignment_id
4412: FROM hz_party_usg_assignments u
4413: WHERE party_usage_code = 'CUSTOMER'
4414: AND status_flag = 'D'
4415: AND party_id in (SELECT DISTINCT c.party_id from hz_cust_accounts c, ra_customer_merges m
4416: WHERE c.cust_account_id = m.duplicate_id
4417: AND m.process_flag = 'N'
4418: AND m.request_id = req_id
4419: AND m.set_number = set_num

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

4435:
4436: DELETE FROM hz_party_usg_assignments u
4437: WHERE party_usage_code = 'CUSTOMER'
4438: AND status_flag = 'D'
4439: AND party_id in (SELECT DISTINCT c.party_id from hz_cust_accounts c, ra_customer_merges m
4440: WHERE c.cust_account_id = m.duplicate_id
4441: AND m.process_flag = 'N'
4442: AND m.request_id = req_id
4443: AND m.set_number = set_num

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

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;

Line 4543: owner_table_name = 'HZ_CUST_ACCOUNTS' and

4539: OPEN cust_accounts;
4540: CLOSE cust_accounts;
4541:
4542: delete from hz_orig_sys_references where
4543: owner_table_name = 'HZ_CUST_ACCOUNTS' and
4544: owner_table_id in (
4545: select cust_account_id from hz_cust_accounts where
4546: status = 'D' and cust_account_id in
4547: ( SELECT m.duplicate_id

Line 4545: select cust_account_id from hz_cust_accounts where

4541:
4542: delete from hz_orig_sys_references where
4543: owner_table_name = 'HZ_CUST_ACCOUNTS' and
4544: owner_table_id in (
4545: select cust_account_id from hz_cust_accounts where
4546: status = 'D' and cust_account_id in
4547: ( SELECT m.duplicate_id
4548: FROM ra_customer_merges m
4549: WHERE m.process_flag = 'N'

Line 4554: DELETE FROM HZ_CUST_ACCOUNTS

4550: AND m.request_id = req_id
4551: AND m.set_number = set_num
4552: AND m.delete_duplicate_flag = 'Y' ));
4553:
4554: DELETE FROM HZ_CUST_ACCOUNTS
4555: WHERE cust_account_id IN (
4556: SELECT m.duplicate_id
4557: FROM ra_customer_merges m
4558: WHERE m.process_flag = 'N'

Line 4662: FROM hz_cust_accounts acct

4658: AND m.set_number = set_num )
4659: AND site_use_id IS NULL
4660: AND NOT EXISTS (
4661: SELECT 'accounts exist'
4662: FROM hz_cust_accounts acct
4663: WHERE acct.cust_account_id = yt.customer_id
4664: AND status <> 'D' );
4665:
4666: g_count := sql%rowcount;

Line 5084: FROM hz_cust_accounts

5080: WHERE party_site_id = l_duplicate_party_site_id;
5081:
5082: --Select merge-to's party
5083: SELECT party_id INTO l_merge_to_party_id
5084: FROM hz_cust_accounts
5085: WHERE cust_account_id = l_customer_id;
5086:
5087: --Check if user tries to create new address which he/she already has.
5088: --Since account sites has been stripped by org, we do this check in

Line 6606: ---------Insert into HZ_CUST_ACCOUNTS_M--------------

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,
6609: cust_account_id,
6610: party_id ,

Line 6607: INSERT INTO HZ_CUST_ACCOUNTS_M(

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,
6609: cust_account_id,
6610: party_id ,
6611: last_update_date ,

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

6840: FROM (select distinct duplicate_id , customer_merge_header_id , customer_id
6841: from ra_customer_merges cm
6842: where cm.process_flag = 'N'
6843: and cm.request_id = req_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');

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

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--------------