Classification Based on Values in a String

This is somewhat of a puzzler where I can't find the right combination of
Oracle functions to deliver the correct result.

Oracle 10.2.0.4 on RHEL 5.7

Claims coming into our system can be rejected for a variety of reasons which
appear as comma separated values in the 'rejection_list' column of the claim
table.  Basically, there are two broad classes of rejects that need to be
separated.  The classes can be broken out as those rejected solely because
the provider is not set up in the system and everything else.

Here's how it works:

To go to the Provider Setup Queue:



Criteria #1:  The claim has a status code of ‘R’
Criteria #2:  If reject code list contains one or more values, all of which
are a ‘09’, a ‘07’ or a ‘22’  then it goes to the Provider Setup Queue


To go to the Reject Queue:



Criteria #1:  The claim has a status of ‘R’

Criteria #2:  If the reject code list contains multiple values, and if any
one of the values in the list is not a ‘09’, a ‘07’ or a ‘22’, then it goes
to the Reject Queue

Criteria #3:  If the reject code list contains only one value, and that
value is not a ‘09’, a ‘07’ or a ‘22’  then it goes to the Reject Queue


The codes can be in any order.  There are a lot of codes.  We do have a
vendor-supplied function that determines if 09,07,22 is in the string, but
it doesn't tell if any other codes are in the string as well.

It works okay if there aren't any provider reject codes in the string.  The
function returns a '0' and the claim goes to the generic reject queue.

'Select claim_id from claim
  where FN_GET_INSTR(CLAIM.REJECTION_LIST,'07,09,22') <> 1'

It also works okay if we specify the code list only contains a single value
and the value is a provider set up indicator.  We get back a '1' and the
claim goes to the set up queue.

'Select claim_id from claim
  where length(rejection_list) = 2
    and FN_GET_INSTR(CLAIM.REJECTION_LIST,'07,09,22') = 1'

Where the problem lies is determining if a rejection_list having multiple
values consists solely of the provider reject codes, or contains other
values.

Given a list like 04,01,09,22,53 - the function is going to return a '1'.
But so is a list like 09 or 09,22.  The first should go to the generic
queue, the second and third to the provider set up queue.

I've tried a variety of combinations of regexp_ and old-timey Oracle
functions (substr, instr, replace, etc.) but can't come up with a solution
that will enable us to route claims with multiple codes in the rejection
list to the proper queue.  I probably need to use this function for the two
cases in which it returns a valid result and just go ahead and write
something totally new, but I was wondering if anybody has any ideas.

Other related posts: