[Home] [Help]
3070: | there exists any other Inactive accounts AND doesnot exist any active accounts associated
3071: | with the merge-from-party AND there is no other customer usage associated
3072: | 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
3073: | customer usages associated with that party.
3074: | Also updated the who columns in the HZ_PARTY_USG_ASSIGNMENTS table
3075: | while inactivating the party usg assignment.
3076: |
3077: |
3078: +===========================================================================*/
3085:
3086: CURSOR c1 IS
3087:
3088: SELECT party_usg_assignment_id
3089: FROM hz_party_usg_assignments pu
3090: WHERE party_usage_code = 'CUSTOMER'
3091: AND EXISTS (SELECT 'Y'
3092: FROM ra_customer_merges m, hz_cust_accounts c1
3093: WHERE m.duplicate_id = c1.cust_account_id
3105: /* locking tables by opening and closing cursors */
3106: IF process_mode = 'LOCK' THEN
3107:
3108: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
3109: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
3110:
3111: OPEN c1;
3112: CLOSE c1;
3113:
3114: ELSE
3115: /*************** 'inactivate' mode ***************/
3116:
3117: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
3118: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
3119:
3120: --inactivate active 'CUSTOMER' usage of merge-from party if the merge-from account is the only active account
3121:
3122:
3119:
3120: --inactivate active 'CUSTOMER' usage of merge-from party if the merge-from account is the only active account
3121:
3122:
3123: UPDATE hz_party_usg_assignments pu
3124: SET effective_end_date = trunc(sysdate),
3125: status_flag = 'I',
3126: --Bug No. 4558392
3127: last_update_date=sysdate,
3156:
3157: /*************** 'delete' mode ***************/
3158: ---Bug No. 4558392
3159: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
3160: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
3161:
3162: --inactivate active 'CUSTOMER' usage of merge-from party when delete after merge is checked if
3163: -- There exists any other Inactive accounts AND doesnot exist any active accounts associated with the merge-from
3164: --party AND there is no other customer usage associated with that party.
3163: -- There exists any other Inactive accounts AND doesnot exist any active accounts associated with the merge-from
3164: --party AND there is no other customer usage associated with that party.
3165:
3166:
3167: UPDATE hz_party_usg_assignments pu
3168: SET effective_end_date = trunc(sysdate),
3169: status_flag = 'I',
3170:
3171: last_update_date=sysdate,
3189: AND rownum=1
3190: )
3191:
3192: AND NOT EXISTS (SELECT 'Y'
3193: from hz_party_usg_assignments
3194: where party_id=pu.party_id
3195: and party_usage_code='CUSTOMER'
3196: and party_usg_assignment_id <> pu.party_usg_assignment_id
3197: and rownum=1)
3218:
3219:
3220:
3221: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE' );
3222: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
3223:
3224: --Delete all 'CUSTOMER' usage of merge-from party
3225: --if the merge-from account is the only account for merge-from party OR there are any other customer usages associated with that party.
3226:
3223:
3224: --Delete all 'CUSTOMER' usage of merge-from party
3225: --if the merge-from account is the only account for merge-from party OR there are any other customer usages associated with that party.
3226:
3227: UPDATE hz_party_usg_assignments pu
3228: SET effective_end_date = trunc(sysdate),
3229: status_flag = 'D'
3230: WHERE pu.party_usage_code = 'CUSTOMER'
3231:
3237: )
3238: --Bug No.4558392
3239: OR ( pu.status_flag='A' AND nvl(pu.effective_end_date,sysdate+1)>=sysdate AND
3240: exists (SELECT 'Y'
3241: from hz_party_usg_assignments
3242: where party_id=pu.party_id
3243: and party_usage_code='CUSTOMER'
3244: and party_usg_assignment_id <> pu.party_usg_assignment_id
3245: and rownum=1)
4718:
4719: --bug 4307679
4720: CURSOR cust_usage IS
4721: SELECT party_usg_assignment_id
4722: FROM hz_party_usg_assignments u
4723: WHERE party_usage_code = 'CUSTOMER'
4724: AND status_flag = 'D'
4725: AND party_id in (SELECT DISTINCT c.party_id from hz_cust_accounts c, ra_customer_merges m
4726: WHERE c.cust_account_id = m.duplicate_id
4736: /*****************************************************/
4737:
4738: --4307679
4739: arp_message.set_name( 'AR', 'AR_DELETING_TABLE' );
4740: arp_message.set_token( 'TABLE_NAME', 'HZ_PARTY_USG_ASSIGNMENTS', FALSE );
4741:
4742: --lock rows
4743: OPEN cust_usage;
4744: CLOSE cust_usage;
4742: --lock rows
4743: OPEN cust_usage;
4744: CLOSE cust_usage;
4745:
4746: DELETE FROM hz_party_usg_assignments u
4747: WHERE party_usage_code = 'CUSTOMER'
4748: AND status_flag = 'D'
4749: AND party_id in (SELECT DISTINCT c.party_id from hz_cust_accounts c, ra_customer_merges m
4750: WHERE c.cust_account_id = m.duplicate_id