DBA Data[Home] [Help]

APPS.ARP_CMERGE_ARCUS dependencies on HZ_CUST_SITE_USES_ALL

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 323: update hz_cust_site_uses_all --SSUptake

319: EXCEPTION
320: WHEN NO_DATA_FOUND THEN
321: l_ra_bill_to_site_use_id := l_bill_to_site_use_id;
322: END;
323: update hz_cust_site_uses_all --SSUptake
324: set bill_to_site_use_id = l_ra_bill_to_site_use_id
325: where site_use_id = l_cust_site_id
326: and org_id = l_org_id; --SSUptake
327: END IF;

Line 329: update hz_cust_site_uses_all --SSUptake

325: where site_use_id = l_cust_site_id
326: and org_id = l_org_id; --SSUptake
327: END IF;
328: ELSIF l_site_use_code = 'BILL_TO' THEN
329: update hz_cust_site_uses_all --SSUptake
330: set bill_to_site_use_id = l_cust_site_id
331: where org_id = l_org_id --SSUptake
332: and site_use_id in (
333: SELECT site_use_id

Line 334: FROM hz_cust_site_uses_all su, --SSUptake

330: set bill_to_site_use_id = l_cust_site_id
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

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 465: UPDATE HZ_CUST_SITE_USES_ALL yt --SSUptake

461: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
462:
463: --inactivate customer account site uses.
464:
465: UPDATE HZ_CUST_SITE_USES_ALL yt --SSUptake
466: SET status = 'I',
467: last_update_date = sysdate,
468: last_updated_by =hz_utility_v2pub.user_id,-- arp_standard.profile.user_id,
469: last_update_login =hz_utility_v2pub.last_update_login,-- arp_standard.profile.last_update_login,

Line 538: UPDATE HZ_CUST_SITE_USES_ALL yt

534:
535: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
536: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
537:
538: UPDATE HZ_CUST_SITE_USES_ALL yt
539: SET status = 'D'
540: WHERE EXISTS (
541: SELECT 'Y'
542: FROM ra_customer_merges m

Line 598: p_owner_table_name =>'HZ_CUST_SITE_USES_ALL',

594: p_init_msg_list => FND_API.G_FALSE,
595: p_validation_level => FND_API.G_VALID_LEVEL_NONE,
596: p_old_owner_table_id => x_from_site_id,
597: p_new_owner_table_id => x_to_site_id,
598: p_owner_table_name =>'HZ_CUST_SITE_USES_ALL',
599: p_orig_system => null,
600: p_orig_system_reference => null,
601: p_reason_code => 'MERGED',
602: x_return_status => x_return_status,

Line 701: FROM HZ_CUST_SITE_USES_ALL su --SSUptake

697: AND m.set_number = set_num
698: AND m.delete_duplicate_flag = 'N'
699: AND NOT EXISTS (
700: SELECT 'active site uses exist'
701: FROM HZ_CUST_SITE_USES_ALL su --SSUptake
702: WHERE su.org_id = yt.org_id --SSUptake
703: AND su.cust_acct_site_id = yt.cust_acct_site_id
704: AND su.status = 'A' )
705: FOR UPDATE NOWAIT;

Line 719: FROM HZ_CUST_SITE_USES_ALL su --SSUptake

715: AND m.org_id = yt.org_id --SSUptake
716: AND yt.cust_acct_site_id = m.duplicate_address_id
717: AND NOT EXISTS (
718: SELECT 'site uses exist'
719: FROM HZ_CUST_SITE_USES_ALL su --SSUptake
720: WHERE su.cust_acct_site_id = yt.cust_acct_site_id
721: AND su.org_id = yt.org_id --SSUptake
722: AND su.status <> 'D' )
723: FOR UPDATE NOWAIT;

Line 733: FROM HZ_CUST_SITE_USES_ALL su --SSUptake

729: AND m.request_id = req_id
730: AND m.set_number = set_num
731: AND (m.delete_duplicate_flag = 'N' or m.delete_duplicate_flag = 'Y') --5571559
732: AND NOT EXISTS (SELECT 'site uses exist'
733: FROM HZ_CUST_SITE_USES_ALL su --SSUptake
734: WHERE su.org_id = m.org_id --SSUptake
735: AND su.cust_acct_site_id = m.duplicate_address_id
736: AND su.status = 'A' );
737:

Line 785: FROM HZ_CUST_SITE_USES_ALL su

781: AND m.duplicate_address_id = yt.cust_acct_site_id
782: AND m.org_id = yt.org_id) --SSUptake
783: AND NOT EXISTS (
784: SELECT 'active site uses exist'
785: FROM HZ_CUST_SITE_USES_ALL su
786: WHERE su.cust_acct_site_id = yt.cust_acct_site_id
787: AND su.org_id = yt.org_id
788: AND su.status = 'A' );
789:

Line 820: FROM HZ_CUST_SITE_USES_ALL s --SSUptake

816: AND m.org_id = yt.org_id
817: AND m.delete_duplicate_flag = 'N' )
818: AND NOT EXISTS (
819: SELECT 'no active bill to'
820: FROM HZ_CUST_SITE_USES_ALL s --SSUptake
821: WHERE s.cust_acct_site_id = yt.cust_acct_site_id
822: AND s.org_id = yt.org_id
823: AND s.site_use_code = 'BILL_TO'
824: AND status = 'A' )

Line 857: FROM HZ_CUST_SITE_USES_ALL s --SSUptake

853: AND m.duplicate_address_id = yt.cust_acct_site_id
854: AND m.org_id = yt.org_id )
855: AND NOT EXISTS (
856: SELECT 'no active ship to'
857: FROM HZ_CUST_SITE_USES_ALL s --SSUptake
858: WHERE s.cust_acct_site_id = yt.cust_acct_site_id
859: AND s.org_id = yt.org_id
860: AND s.site_use_code = 'SHIP_TO'
861: AND status = 'A' )

Line 894: FROM HZ_CUST_SITE_USES_ALL s --SSUptake

890: AND m.org_id = yt.org_id
891: AND m.duplicate_address_id = yt.cust_acct_site_id)
892: AND NOT EXISTS (
893: SELECT 'no active market site'
894: FROM HZ_CUST_SITE_USES_ALL s --SSUptake
895: WHERE s.cust_acct_site_id = yt.cust_acct_site_id
896: AND s.org_id = yt.org_id
897: AND s.site_use_code = 'MARKET'
898: AND status = 'A' )

Line 929: FROM HZ_CUST_SITE_USES_ALL su --SSUptake

925: AND m.duplicate_address_id = yt.cust_acct_site_id
926: AND m.org_id = yt.org_id)
927: AND NOT EXISTS (
928: SELECT 'site uses exist'
929: FROM HZ_CUST_SITE_USES_ALL su --SSUptake
930: WHERE su.cust_acct_site_id = yt.cust_acct_site_id
931: AND su.org_id = yt.org_id
932: AND su.status <> 'D' );
933:

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

4321: ) IS
4322:
4323: CURSOR cust_site_uses IS
4324: SELECT site_use_id
4325: FROM HZ_CUST_SITE_USES_ALL su, ra_customer_merges m --SSUptake
4326: WHERE cust_acct_site_id = m.duplicate_address_id
4327: AND m.org_id = su.org_id --SSUptake
4328: AND m.process_flag = 'N'
4329: AND m.request_id = req_id

Line 4461: owner_table_name = 'HZ_CUST_SITE_USES_ALL' and

4457: OPEN cust_site_uses;
4458: CLOSE cust_site_uses;
4459:
4460: delete from hz_orig_sys_references where
4461: owner_table_name = 'HZ_CUST_SITE_USES_ALL' and
4462: owner_table_id in (
4463: select site_use_id from hz_cust_site_uses_all su
4464: where status = 'D'
4465: and exists ( SELECT 'Y'

Line 4463: select site_use_id from hz_cust_site_uses_all su

4459:
4460: delete from hz_orig_sys_references where
4461: owner_table_name = 'HZ_CUST_SITE_USES_ALL' and
4462: owner_table_id in (
4463: select site_use_id from hz_cust_site_uses_all su
4464: where status = 'D'
4465: and exists ( SELECT 'Y'
4466: FROM ra_customer_merges m
4467: WHERE su.cust_acct_site_id = m.duplicate_address_id

Line 4476: DELETE FROM HZ_CUST_SITE_USES_ALL su

4472: AND m.delete_duplicate_flag = 'Y'
4473: )
4474: );
4475:
4476: DELETE FROM HZ_CUST_SITE_USES_ALL su
4477: WHERE EXISTS (
4478: SELECT 'Y'
4479: FROM ra_customer_merges m
4480: WHERE m.duplicate_address_id = su.cust_acct_site_id

Line 5532: FROM HZ_CUST_SITE_USES_ALL csu --SSUptake

5528: WHERE location_id = l_location_id
5529: AND party_id = l_merge_to_party_id )
5530: AND EXISTS ( --'same site usage'
5531: SELECT site_use_id
5532: FROM HZ_CUST_SITE_USES_ALL csu --SSUptake
5533: WHERE cas.cust_acct_site_id = csu.cust_acct_site_id
5534: AND site_use_code = l_duplicate_site_code
5535: AND csu.org_id = cas.org_id ); --SSUptake
5536:

Line 5558: FROM HZ_CUST_SITE_USES_ALL --SSUptake

5554:
5555: --In case of multiple usages exist, select the one
5556: --with mininum id.
5557: SELECT MIN(site_use_id) INTO l_customer_site_id
5558: FROM HZ_CUST_SITE_USES_ALL --SSUptake
5559: WHERE cust_acct_site_id = l_customer_address_id
5560: AND org_id = m_org_id --SSUptake
5561: AND site_use_code = l_duplicate_site_code;
5562:

Line 6139: FROM HZ_CUST_SITE_USES_ALL --SSUptake

6135: cust_site_use_rec.gl_id_remittance,
6136: cust_site_use_rec.gl_id_factor,
6137: cust_site_use_rec.tax_classification,
6138: site_use_org_id
6139: FROM HZ_CUST_SITE_USES_ALL --SSUptake
6140: WHERE site_use_id = l_duplicate_site_id
6141: AND org_id = m_org_id; --SSUptake
6142:
6143: ----Bug 5211233

Line 6147: from hz_cust_site_uses_all

6143: ----Bug 5211233
6144: IF cust_site_use_rec.primary_flag = 'Y' THEN
6145: BEGIN
6146: Select NULL INTO cust_site_use_rec.primary_flag
6147: from hz_cust_site_uses_all
6148: where CUST_ACCT_SITE_ID in (select CUST_ACCT_SITE_ID from hz_cust_acct_sites_all
6149: Where cust_account_id = l_customer_id
6150: AND org_id = site_use_org_id)
6151: AND SITE_USE_CODE = cust_site_use_rec.site_use_code

Line 6169: from HZ_CUST_SITE_USES_ALL

6165: -- Check whether the bill_to_site_use_id is valid.
6166: BEGIN
6167: select 1
6168: into l_count
6169: from HZ_CUST_SITE_USES_ALL
6170: where site_use_id = cust_site_use_rec.bill_to_site_use_id
6171: and site_use_code = 'BILL_TO';
6172: EXCEPTION
6173: WHEN NO_DATA_FOUND THEN

Line 7603: ---------Insert into hz_cust_site_uses_all_m--------------

7599: AND acs.org_id = m.org_id ;
7600:
7601: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCT_SITES_ALL_M');
7602:
7603: ---------Insert into hz_cust_site_uses_all_m--------------
7604: INSERT INTO hz_cust_site_uses_all_m(
7605: customer_merge_header_id,
7606: site_use_id ,
7607: cust_acct_site_id ,

Line 7604: INSERT INTO hz_cust_site_uses_all_m(

7600:
7601: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_ACCT_SITES_ALL_M');
7602:
7603: ---------Insert into hz_cust_site_uses_all_m--------------
7604: INSERT INTO hz_cust_site_uses_all_m(
7605: customer_merge_header_id,
7606: site_use_id ,
7607: cust_acct_site_id ,
7608: last_update_date ,

Line 7849: and cm.set_number = set_num ) m,hz_cust_site_uses_all su --SSUptake

7845: FROM (select distinct duplicate_site_id,customer_merge_header_id,org_id
7846: from ra_customer_merges cm
7847: where cm.process_flag = 'N'
7848: and cm.request_id = req_id
7849: and cm.set_number = set_num ) m,hz_cust_site_uses_all su --SSUptake
7850: WHERE su.site_use_id = duplicate_site_id
7851: AND su.org_id = m.org_id; --SSUptake
7852:
7853: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_SITE_USES_ALL_M');

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

7849: and cm.set_number = set_num ) m,hz_cust_site_uses_all su --SSUptake
7850: WHERE su.site_use_id = duplicate_site_id
7851: AND su.org_id = m.org_id; --SSUptake
7852:
7853: arp_message.set_line(SQL%ROWCOUNT||' '|| 'Row(s) inserted in HZ_CUST_SITE_USES_ALL_M');
7854:
7855: ---------Insert into hz_cust_acct_relate_all_m--------------
7856: INSERT INTO hz_cust_acct_relate_all_m(
7857: customer_merge_header_id,