Advanced Collections Multiple Strategies Open for Customer Invoice (Doc ID 2166050.1)

Last updated on AUGUST 01, 2016

Applies to:

Oracle Advanced Collections - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

User has manually changed strategy for  customer in strategy tab of collection form.

For those invoices it shows 2 strategies in Open state. Old strategy should have been cancelled/ closed and new strategy should be open for those customer invoices.

Query to fetch duplicate strategies :

SELECT st1.strategy_id ,
st1.object_id,
st1.object_type,
st1.party_id,
a.account_number,
a.cust_account_id acustacctid,
st1.cust_account_id scustacctid
FROM iex_strategies st1,
hz_cust_accounts a
WHERE st1.status_code IN ('OPEN','ONHOLD')
AND st1.object_type = 'ACCOUNT'
AND st1.cust_account_id = a.cust_account_id
--AND st1.cust_account_id = NVL('&AccountID' , st1.cust_account_id)
AND st1.object_id IN
( SELECT st3.object_id
FROM iex_strategies st3
WHERE st3.status_code IN ('OPEN','ONHOLD')
AND st3.object_type = 'BILL_TO''
AND st3.cust_account_id = st1.cust_account_id
GROUP BY st3.object_id
HAVING COUNT(*) > 1)
AND (st1.object_id, st1.strategy_id) NOT IN
( SELECT st2.object_id, max(st2.strategy_id)
FROM iex_strategies st2
WHERE st2.status_code in ('OPEN' , 'ONHOLD')
AND st2.cust_account_id = st1.cust_account_id
AND st2.object_type = 'BILL_TO''
GROUP BY st2.object_id
HAVING COUNT(*) > 1);

Check if retrieves rows.

Note : The above query is for object type 'BILL_TO''.

           If set-up is at ACCOUNT level, kindly change the column object_type as' ACCOUNT' and re-run the query

Sample data Looks as:

STRATEGY_ID  OBJECT_ID   OBJECT_TYPE  PARTY_ID   ACCOUNT_NUMBER   ACUSTACCTID   SCUSTACCTID

=========  ========  =========  ======    ============    =========     ==========

1,630,807          450,477          BILL_TO         244,047           23060                     81,629                  81,629
1,584,502          450,477          BILL_TO         244,047           23060                     81,629                  81,629
1,633,543          385,838          BILL_TO         1,136,502        146401                  471,948                471,948
1,572,520          385,838          BILL_TO         1,136,502        146401                  471,948                471,948
1,630,858          438,029          BILL_TO         1,869,778        166996                  1,614,149             1,614,149
1,630,856          438,029          BILL_TO         1,869,778        166996                  1,614,149             1,614,149

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms