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_ACCT_SITES_ALL

Line 335: hz_cust_acct_sites_all site --SSUptake

331: where org_id = l_org_id --SSUptake
332: and site_use_id in (
333: SELECT site_use_id
334: FROM hz_cust_site_uses_all su, --SSUptake
335: hz_cust_acct_sites_all site --SSUptake
336: WHERE su.org_id = l_org_id --SSUptake
337: AND su.org_id = site.org_id --SSUptake
338: AND site.cust_acct_site_id = su.cust_acct_site_id
339: AND su.site_use_code='SHIP_TO'

Line 512: FROM hz_cust_acct_sites_all s, hz_cust_site_uses_all su

508: AND m.org_id = asu.org_id
509: AND m.process_flag = 'N'
510: AND m.duplicate_primary_flag = 'Y'
511: AND not exists (SELECT 'EXISTS'
512: FROM hz_cust_acct_sites_all s, hz_cust_site_uses_all su
513: WHERE s.cust_account_id = m.customer_id
514: AND su.cust_acct_site_id = s.cust_acct_site_id
515: AND s.org_id = m.org_id
516: AND su.org_id = s.org_id

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 801: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake

797: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
798:
799: --inactivate customer account site
800:
801: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
802: SET status = 'I',
803: last_update_date = sysdate,
804: last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
805: last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,

Line 836: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake

832:
833: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
834: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
835:
836: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
837: SET bill_to_flag = null,
838: last_update_date = sysdate,
839: last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
840: last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,

Line 873: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake

869:
870: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
871: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
872:
873: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
874: SET ship_to_flag = null,
875: last_update_date = sysdate,
876: last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
877: last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,

Line 910: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake

906:
907: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
908: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
909:
910: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
911: SET market_flag = null,
912: last_update_date = sysdate,
913: last_updated_by = hz_utility_v2pub.user_id,--arp_standard.profile.user_id,
914: last_update_login = hz_utility_v2pub.last_update_login,--arp_standard.profile.last_update_login,

Line 944: UPDATE hz_cust_acct_sites_all yt

940: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
941:
942: --Bug 7758559
943:
944: UPDATE hz_cust_acct_sites_all yt
945: SET bill_to_flag = 'P',
946: last_update_date = sysdate,
947: last_updated_by = arp_standard.profile.user_id,
948: last_update_login = arp_standard.profile.last_update_login,

Line 977: UPDATE hz_cust_acct_sites_all yt

973:
974: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
975: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
976:
977: UPDATE hz_cust_acct_sites_all yt
978: SET ship_to_flag = 'P',
979: last_update_date = sysdate,
980: last_updated_by = arp_standard.profile.user_id,
981: last_update_login = arp_standard.profile.last_update_login,

Line 1010: UPDATE hz_cust_acct_sites_all yt

1006:
1007: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
1008: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
1009:
1010: UPDATE hz_cust_acct_sites_all yt
1011: SET market_flag = 'P',
1012: last_update_date = sysdate,
1013: last_updated_by = arp_standard.profile.user_id,
1014: last_update_login = arp_standard.profile.last_update_login,

Line 1051: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake

1047: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_ACCT_SITES', FALSE );
1048:
1049: --'delete' customer account site
1050:
1051: UPDATE HZ_CUST_ACCT_SITES_ALL yt --SSUptake
1052: SET status = 'D'
1053: WHERE EXISTS (
1054: SELECT 'Y'
1055: FROM ra_customer_merges m

Line 1085: p_owner_table_name =>'HZ_CUST_ACCT_SITES_ALL',

1081: p_init_msg_list => FND_API.G_FALSE,
1082: p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1083: p_old_owner_table_id => x_from_address_id,
1084: p_new_owner_table_id => x_to_address_id,
1085: p_owner_table_name =>'HZ_CUST_ACCT_SITES_ALL',
1086: p_orig_system => null,
1087: p_orig_system_reference => null,
1088: p_reason_code => 'MERGED',
1089: x_return_status => x_return_status,

Line 1152: | HZ_CUST_ACCT_SITES_ALL.

1148: | MODIFICATION HISTORY
1149: | Jianying Huang 25-OCT-00 Customer account is global while account
1150: | site is stripped by operating unit. We need to
1151: | check if this account has (active)sites in
1152: | HZ_CUST_ACCT_SITES_ALL.
1153: | Jianying Huang 20-DEC-00 Bug 1535542: Since we need to change
1154: | the merging order, merge HZ tables before merging
1155: | products, we need to mark deleted rows here
1156: | first and physically delete them after merging one

Line 1191: FROM hz_cust_acct_sites_all addr

1187: AND m.delete_duplicate_flag = 'N'
1188: /* no active addresses */
1189: AND NOT EXISTS (
1190: SELECT 'active addresses exist'
1191: FROM hz_cust_acct_sites_all addr
1192: WHERE addr.cust_account_id = yt.cust_account_id
1193: AND addr.status = 'A' )
1194: FOR UPDATE NOWAIT;
1195:

Line 1208: FROM hz_cust_acct_sites_all addr

1204: AND m.delete_duplicate_flag = 'Y'
1205: /* no addresses */
1206: AND NOT EXISTS (
1207: SELECT 'addresses exist'
1208: FROM hz_cust_acct_sites_all addr
1209: WHERE addr.cust_account_id = yt.cust_account_id
1210: AND addr.status <> 'D' )
1211: FOR UPDATE NOWAIT;
1212:

Line 1222: FROM hz_cust_acct_sites_all addr

1218: AND m.set_number = set_num
1219: AND (m.delete_duplicate_flag = 'N' OR m.delete_duplicate_flag = 'Y')
1220: AND NOT EXISTS (
1221: SELECT 'addresses exist'
1222: FROM hz_cust_acct_sites_all addr
1223: WHERE addr.cust_account_id = m.duplicate_id
1224: AND addr.status = 'A');
1225:
1226:

Line 1271: FROM hz_cust_acct_sites_all addr

1267: AND m.delete_duplicate_flag = 'N' )
1268: /* no active addresses */
1269: AND NOT EXISTS (
1270: SELECT 'active addresses exist'
1271: FROM hz_cust_acct_sites_all addr
1272: WHERE addr.cust_account_id = yt.cust_account_id
1273: AND addr.status = 'A' );
1274:
1275: g_count := sql%rowcount;

Line 1302: FROM hz_cust_acct_sites_all addr

1298: AND m.delete_duplicate_flag = 'Y' )
1299: /* no addresses */
1300: AND NOT EXISTS (
1301: SELECT 'addresses exist'
1302: FROM hz_cust_acct_sites_all addr
1303: WHERE addr.cust_account_id = yt.cust_account_id
1304: AND addr.status <> 'D' );
1305:
1306: g_count := sql%rowcount;

Line 1394: | HZ_CUST_ACCT_SITES_ALL.

1390: | MODIFICATION HISTORY
1391: | Jianying Huang 25-OCT-00 Customer account is global while account
1392: | site is stripped by operating unit. We need to
1393: | check if this account has (active)sites in
1394: | HZ_CUST_ACCT_SITES_ALL.
1395: | Jianying Huang 12-DEC-00 Modified cursor c1 and c2. Replace 'active
1396: | account sites exist' with 'active accounts exist'
1397: | Jianying Huang 20-DEC-00 Bug 1535542: Since we need to change
1398: | the merging order, merge HZ tables before merging

Line 1897: | HZ_CUST_ACCT_SITES_ALL.

1893: | MODIFICATION HISTORY
1894: | Jianying Huang 25-OCT-00 Customer account is global while account
1895: | site is stripped by operating unit. We need to
1896: | check if this account has (active)sites in
1897: | HZ_CUST_ACCT_SITES_ALL.
1898: | Jianying Huang 12-DEC-00 Modified cursor c2. Replace 'active addresses
1899: | exist' with 'active accounts exist'
1900: | Jianying Huang 20-DEC-00 Bug 1535542: Since we need to change
1901: | the merging order, merge HZ tables before merging

Line 2436: FROM hz_cust_acct_sites_all

2432: to_site_id );
2433: **/
2434:
2435: SELECT party_site_id into from_party_site_id
2436: FROM hz_cust_acct_sites_all
2437: WHERE cust_acct_site_id = from_site_id;
2438:
2439: SELECT party_site_id into to_party_site_id
2440: FROM hz_cust_acct_sites_all

Line 2440: FROM hz_cust_acct_sites_all

2436: FROM hz_cust_acct_sites_all
2437: WHERE cust_acct_site_id = from_site_id;
2438:
2439: SELECT party_site_id into to_party_site_id
2440: FROM hz_cust_acct_sites_all
2441: WHERE cust_acct_site_id = to_site_id;
2442:
2443: /**
2444: arp_message.set_line(

Line 2904: | HZ_CUST_ACCT_SITES_ALL.

2900: | MODIFICATION HISTORY
2901: | Jianying Huang 25-OCT-00 Customer account is global while account
2902: | site is stripped by operating unit. We need to
2903: | check if this account has (active)sites in
2904: | HZ_CUST_ACCT_SITES_ALL.
2905: | Jianying Huang 12-DEC-00 Remove cursor c2 and c4 because we
2906: | do not delete rows in hz_cust_contact_points.
2907: | Jianying Huang 16-DEC-00 As per discussion with Gautam Prothia,
2908: | since we changed veiw RA_PHONES (see bug 1487607),

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 4814: owner_table_name = 'HZ_CUST_ACCT_SITES_ALL' and

4810: OPEN cust_sites;
4811: CLOSE cust_sites;
4812:
4813: delete from hz_orig_sys_references where
4814: owner_table_name = 'HZ_CUST_ACCT_SITES_ALL' and
4815: owner_table_id in (
4816: select cust_acct_site_id from hz_cust_acct_sites_all sites --SSuptake
4817: where status = 'D'
4818: and EXISTS

Line 4816: select cust_acct_site_id from hz_cust_acct_sites_all sites --SSuptake

4812:
4813: delete from hz_orig_sys_references where
4814: owner_table_name = 'HZ_CUST_ACCT_SITES_ALL' and
4815: owner_table_id in (
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

Line 4828: DELETE FROM HZ_CUST_ACCT_SITES_ALL yt --SSUptake

4824: AND m.request_id = req_id
4825: AND m.set_number = set_num
4826: AND m.delete_duplicate_flag = 'Y' ));
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

Line 5095: | column in hz_cust_acct_sites_all.

5091: | 'create site/site use' in different scenario.
5092: | Jianying Huang 07-DEC-00 Bug 1227593: Added column 'ADDRESSEE'
5093: | when we create new party site.
5094: | Jianying Huang 07-DEC-OO Should not copy tp_header_id. It is an unique
5095: | column in hz_cust_acct_sites_all.
5096: | Jianying Huang 12-DEC-00 Check 'x_return_status' after call
5097: | 'create_cust_prof_amt'
5098: | Jianying Huang 20-DEC-00 Bug 1535542: Since we will call customer merge
5099: | before merging prEoducts, we should move 'createSites'

Line 5386: FROM hz_cust_acct_sites_all --SSUptake

5382: l_gen_loc := 'Y';
5383: l_exist := 'N';
5384: --Select merge-from's party site id.
5385: SELECT party_site_id INTO l_duplicate_party_site_id
5386: FROM hz_cust_acct_sites_all --SSUptake
5387: WHERE cust_acct_site_id = l_duplicate_address_id
5388: and org_id = m_org_id; --SSUptake
5389:
5390: --Select merge-from's address

Line 5413: FROM hz_cust_acct_sites_all --SSUptake

5409:
5410: BEGIN
5411:
5412: SELECT 'Y' INTO l_exist
5413: FROM hz_cust_acct_sites_all --SSUptake
5414: WHERE cust_account_id = l_customer_id
5415: AND org_id = m_org_id --SSUptake
5416: AND party_site_id IN (
5417: SELECT party_site_id

Line 5715: --Should not copy tp related columns. They are unique columns in hz_cust_acct_sites_all.

5711: customer_category_code,
5712: language,
5713: key_account_flag,
5714:
5715: --Should not copy tp related columns. They are unique columns in hz_cust_acct_sites_all.
5716: -- tp_header_id,
5717: -- ece_tp_location_code,
5718:
5719: --Bug:2098728 obsoleted service_territory_id,

Line 5787: FROM hz_cust_acct_sites_all

5783: cust_site_rec.territory_id,
5784: cust_site_rec.territory,
5785: l_org_id
5786: -- cust_site_rec.translated_customer_name
5787: FROM hz_cust_acct_sites_all
5788: WHERE cust_account_id = l_duplicate_id
5789: AND cust_acct_site_id = l_duplicate_address_id;
5790:
5791: cust_site_rec.party_site_id := l_party_site_id;

Line 5850: FROM hz_cust_acct_sites_all cas --SSUptake

5846: --pick up the one with minum id and same business purpose, if exists
5847:
5848:
5849: SELECT MIN(cust_acct_site_id) INTO l_customer_address_id
5850: FROM hz_cust_acct_sites_all cas --SSUptake
5851: WHERE cust_account_id = l_customer_id
5852: AND org_id = m_org_id --SSUptake
5853: AND party_site_id IN (
5854: SELECT party_site_id

Line 5870: FROM hz_cust_acct_sites_all cas --SSUptake

5866:
5867: IF l_customer_address_id IS NULL THEN
5868:
5869: SELECT MIN(cust_acct_site_id) INTO l_customer_address_id
5870: FROM hz_cust_acct_sites_all cas --SSUptake
5871: WHERE cust_account_id = l_customer_id
5872: and org_id = m_org_id --SSUptake
5873: AND party_site_id IN (
5874: SELECT party_site_id

Line 6479: where CUST_ACCT_SITE_ID in (select CUST_ACCT_SITE_ID from hz_cust_acct_sites_all

6475: IF cust_site_use_rec.primary_flag = 'Y' THEN
6476: BEGIN
6477: Select NULL INTO cust_site_use_rec.primary_flag
6478: from hz_cust_site_uses_all
6479: where CUST_ACCT_SITE_ID in (select CUST_ACCT_SITE_ID from hz_cust_acct_sites_all
6480: Where cust_account_id = l_customer_id
6481: AND org_id = site_use_org_id)
6482: AND SITE_USE_CODE = cust_site_use_rec.site_use_code
6483: AND PRIMARY_FLAG = 'Y'

Line 6560: FROM hz_cust_acct_sites_all as1, hz_cust_site_uses_all asu

6556: l_dun_exists:='N';
6557: IF (l_duplicate_site_code = 'DUN' or l_duplicate_site_code = 'STMTS') and cust_site_use_rec.status = 'A' THEN
6558: BEGIN
6559: SELECT 'Y' INTO l_dun_exists
6560: FROM hz_cust_acct_sites_all as1, hz_cust_site_uses_all asu
6561: WHERE as1.cust_account_id = l_customer_id
6562: AND asu.cust_acct_site_id = as1.cust_acct_site_id
6563: AND as1.org_id = m_org_id
6564: AND asu.site_use_code = l_duplicate_site_code

Line 6850: FROM hz_cust_acct_sites_all

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
6851: WHERE cust_acct_site_id = l_customer_address_id;
6852:
6853: ---Bug: 2376975 Acct merge should happen even if there is no site use
6854: ---l_customer_site_id is null when site_use is not created

Line 6868: FROM hz_cust_acct_sites_all

6864: --bug 3959776
6865: SELECT party_site_number INTO l_party_site_number
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,

Line 7786: ---------Insert into hz_cust_acct_sites_all_m--------------

7782: OR (pa.site_use_id = duplicate_site_id);
7783:
7784: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_PROFILE_AMTS_M');
7785:
7786: ---------Insert into hz_cust_acct_sites_all_m--------------
7787: INSERT INTO hz_cust_acct_sites_all_m(
7788: customer_merge_header_id,
7789: cust_acct_site_id ,
7790: cust_account_id ,

Line 7787: INSERT INTO hz_cust_acct_sites_all_m(

7783:
7784: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_PROFILE_AMTS_M');
7785:
7786: ---------Insert into hz_cust_acct_sites_all_m--------------
7787: INSERT INTO hz_cust_acct_sites_all_m(
7788: customer_merge_header_id,
7789: cust_acct_site_id ,
7790: cust_account_id ,
7791: party_site_id ,

Line 7950: and cm.set_number = set_num ) m,hz_cust_acct_sites_all acs

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
7952: AND acs.org_id = m.org_id ;
7953:
7954: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCT_SITES_ALL_M');

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

7950: and cm.set_number = set_num ) m,hz_cust_acct_sites_all acs
7951: WHERE acs.cust_acct_site_id = duplicate_address_id
7952: AND acs.org_id = m.org_id ;
7953:
7954: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCT_SITES_ALL_M');
7955:
7956: ---------Insert into hz_cust_site_uses_all_m--------------
7957: INSERT INTO hz_cust_site_uses_all_m(
7958: customer_merge_header_id,