[Home] [Help]
2762: | there exists any other Inactive accounts AND doesnot exist any active accounts associated
2763: | with the merge-from-party AND there is no other customer usage associated
2764: | with that party. Also changed the where clause of the third update of the | same procedure so that it deletes the customer usage there are any other
2765: | customer usages associated with that party.
2766: | Also updated the who columns in the HZ_PARTY_USG_ASSIGNMENTS table
2767: | while inactivating the party usg assignment.
2768: |
2769: |
2770: +===========================================================================*/
2777:
2778: CURSOR c1 IS
2779:
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
2797: /* locking tables by opening and closing cursors */
2798: IF process_mode = 'LOCK' THEN
2799:
2800: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
2801: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
2802:
2803: OPEN c1;
2804: CLOSE c1;
2805:
2806: ELSE
2807: /*************** 'inactivate' mode ***************/
2808:
2809: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2810: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
2811:
2812: --inactivate active 'CUSTOMER' usage of merge-from party if the merge-from account is the only active account
2813:
2814:
2811:
2812: --inactivate active 'CUSTOMER' usage of merge-from party if the merge-from account is the only active account
2813:
2814:
2815: UPDATE hz_party_usg_assignments pu
2816: SET effective_end_date = trunc(sysdate),
2817: status_flag = 'I',
2818: --Bug No. 4558392
2819: last_update_date=sysdate,
2848:
2849: /*************** 'delete' mode ***************/
2850: ---Bug No. 4558392
2851: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2852: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
2853:
2854: --inactivate active 'CUSTOMER' usage of merge-from party when delete after merge is checked if
2855: -- There exists any other Inactive accounts AND doesnot exist any active accounts associated with the merge-from
2856: --party AND there is no other customer usage associated with that party.
2855: -- There exists any other Inactive accounts AND doesnot exist any active accounts associated with the merge-from
2856: --party AND there is no other customer usage associated with that party.
2857:
2858:
2859: UPDATE hz_party_usg_assignments pu
2860: SET effective_end_date = trunc(sysdate),
2861: status_flag = 'I',
2862:
2863: last_update_date=sysdate,
2881: AND rownum=1
2882: )
2883:
2884: AND NOT EXISTS (SELECT 'Y'
2885: from hz_party_usg_assignments
2886: where party_id=pu.party_id
2887: and party_usage_code='CUSTOMER'
2888: and party_usg_assignment_id <> pu.party_usg_assignment_id
2889: and rownum=1)
2910:
2911:
2912:
2913: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
2914: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
2915:
2916: --Delete all 'CUSTOMER' usage of merge-from party
2917: --if the merge-from account is the only account for merge-from party OR there are any other customer usages associated with that party.
2918:
2915:
2916: --Delete all 'CUSTOMER' usage of merge-from party
2917: --if the merge-from account is the only account for merge-from party OR there are any other customer usages associated with that party.
2918:
2919: UPDATE hz_party_usg_assignments pu
2920: SET effective_end_date = trunc(sysdate),
2921: status_flag = 'D'
2922: WHERE pu.party_usage_code = 'CUSTOMER'
2923:
2929: )
2930: --Bug No.4558392
2931: OR ( pu.status_flag='A' AND nvl(pu.effective_end_date,sysdate+1)>=sysdate AND
2932: exists (SELECT 'Y'
2933: from hz_party_usg_assignments
2934: where party_id=pu.party_id
2935: and party_usage_code='CUSTOMER'
2936: and party_usg_assignment_id <> pu.party_usg_assignment_id
2937: and rownum=1)
4408:
4409: --bug 4307679
4410: CURSOR cust_usage IS
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
4426: /*****************************************************/
4427:
4428: --4307679
4429: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4430: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
4431:
4432: --lock rows
4433: OPEN cust_usage;
4434: CLOSE cust_usage;
4432: --lock rows
4433: OPEN cust_usage;
4434: CLOSE cust_usage;
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