[Home] [Help]
249: | PROCEDURE
250: | ra_bill_to_location
251: |
252: | DESCRIPTION
253: | merge in HZ_CUST_SITE_USES.
254: |
255: | SCOPE - PRIVATE
256: |
257: | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
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
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;
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
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
369: | PROCEDURE
370: | ra_su
371: |
372: | DESCRIPTION
373: | merge in HZ_CUST_SITE_USES.
374: |
375: | SCOPE - PRIVATE
376: |
377: | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
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
447: /* locking tables by opening and closing cursors */
448: IF process_mode = 'LOCK' THEN
449:
450: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
451: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
452:
453: OPEN c1;
454: CLOSE c1;
455:
457:
458: /*************** 'inactivate' mode ***************/
459:
460: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
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
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,
492: --Inactivate non-transaction site uses if no active ship_to,
493: --bill_to or market site uses remain for the account site.
494:
495: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
496: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
497:
498: UPDATE HZ_CUST_SITE_USES su1
499: SET status = 'I',
500: last_update_date = sysdate,
494:
495: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
496: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
497:
498: UPDATE HZ_CUST_SITE_USES su1
499: SET status = 'I',
500: last_update_date = sysdate,
501: last_updated_by = arp_standard.profile.user_id,
502: last_update_login = arp_standard.profile.last_update_login,
513: AND m.delete_duplicate_flag = 'N' )
514: AND site_use_code NOT IN ('BILL_TO', 'SHIP_TO', 'MARKET' )
515: AND NOT EXISTS (
516: SELECT 'active bill/ship/market site uses exist'
517: FROM HZ_CUST_SITE_USES su
518: WHERE su.cust_acct_site_id = su1.cust_acct_site_id
519: AND su.site_use_code IN
520: ( 'BILL_TO','SHIP_TO', 'MARKET' )
521: AND status = 'A' );
532: --Bug 1535542: Mark the rows need to be deleted by setting status to 'D'.
533: --Physically delete them after merge.
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 (
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
558: --'Delete' non-transaction site uses if no ship_to,
559: --bill_to, market site uses remain for the account site.
560:
561: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
562: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
563:
564: UPDATE HZ_CUST_SITE_USES su1
565: SET status = 'D'
566: WHERE cust_acct_site_id IN (
560:
561: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
562: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
563:
564: UPDATE HZ_CUST_SITE_USES su1
565: SET status = 'D'
566: WHERE cust_acct_site_id IN (
567: SELECT m.duplicate_address_id
568: FROM ra_customer_merges m
572: AND m.delete_duplicate_flag = 'Y' )
573: AND site_use_code NOT IN ('BILL_TO', 'SHIP_TO', 'MARKET' )
574: AND NOT EXISTS (
575: SELECT 'bill/ship/market site uses exist'
576: FROM HZ_CUST_SITE_USES su
577: WHERE su.cust_acct_site_id = su1.cust_acct_site_id
578: AND su.site_use_code IN
579: ( 'BILL_TO', 'SHIP_TO', 'MARKET' )
580: AND status <> 'D' );
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,
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;
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;
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:
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:
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' )
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' )
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' )
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:
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
4450:
4451: --4397679
4452:
4453: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4454: arp_message.set_token( 'TABLE_NAME', 'HZ_CUST_SITE_USES', FALSE );
4455:
4456: --lock rows
4457: OPEN cust_site_uses;
4458: CLOSE cust_site_uses;
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'
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
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
4817: | Jyoti Pandey 20-MAY-02 Bug:2376975 create site use,profiles, amts etc. only
4818: | is dup_site_use_id <> -99 . Form sets to -99 if
4819: | there is no site use for a given site.
4820: | P.Suresh 13-JUN-02 Bug No : 2403263. Added contact_id to
4821: | hz_cust_site_uses.
4822: | Rajeshwari P 10-OCT-02 Bug No:2529143.Added another parameter 'Credit_classification'
4823: | during creation of Profile for a customer
4824: | in create_same_sites procedure.
4825: | Dhaval Mehta 28-JUL-03 Bug 2971149. Added the code back to copy
5034: x_customer_site_id NUMBER;
5035: l_count NUMBER;
5036: l_actual_cont_source VARCHAR2(30);
5037:
5038: ---to get the value from hz_cust_acct_site and hz_cust_site_uses
5039: ---create a new one with same org_id
5040:
5041: l_org_id NUMBER := NULL ;
5042: site_use_org_id NUMBER := NULL;
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:
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:
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
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
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
6225: l_dun_exists:='N';
6226: IF (l_duplicate_site_code = 'DUN' or l_duplicate_site_code = 'STMTS') and cust_site_use_rec.status = 'A' THEN
6227: BEGIN
6228: SELECT 'Y' INTO l_dun_exists
6229: FROM hz_cust_acct_sites as1, hz_cust_site_uses asu
6230: WHERE as1.cust_account_id = l_customer_id
6231: AND asu.cust_acct_site_id = as1.cust_acct_site_id
6232: AND asu.site_use_code = l_duplicate_site_code
6233: AND asu.status = 'A'
6520: if l_customer_site_id is not null then
6521: --Select primary flag and location
6522: SELECT primary_flag, location
6523: INTO l_customer_primary_flag, l_customer_location
6524: FROM HZ_CUST_SITE_USES
6525: WHERE site_use_id = l_customer_site_id;
6526: end if;
6527: --bug 3959776
6528: SELECT party_site_number INTO l_party_site_number
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 ,
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 ,
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');
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,