Exceptions Active Past End

Summary/Question

With this sql, you can gather the list of exceptions (coverage, referral, page block) that should not be active, but still are.

Topic / Solution

Run this (generally as atms) in sqlplus. The "chr(124)" is the | (pipe) symbol (chr(9) is a tab, by the way).
We use the ascii code here to make this more readable. Otherwise, one would have to type '|' for the pipe, and then it can be harder to read:

name || '|' || messaging_id || '|' || listing_id

versus

name || chr(124) || messaging_id || chr(124) || listing_id

So:

   sqlplus $(get_connect atms vru.set)

   set pages 0
   set lines 4000
   set trimspool on
   set trimout on

   select 'Name|Listing ID|Messaging ID|Type' from dual;
   select el.name || chr(124) || el.listing_id || chr(124) || l.messaging_id || chr(124) || el.exception_type
     from exception_list el, listing l
    where l.listing_id = el.listing_id
          and el.end_date < sysdate
          and el.active_flag = 'T'
   order by l.name, l.messaging_id;

Alternatively, you may want to have each value on its own line for editing / addition / removal:

   select el.name           || chr(124) ||
          el.listing_id     || chr(124) ||
          l.messaging_id    || chr(124) ||
          el.exception_type
     from exception_list el,
          listing l
    where l.listing_id = el.listing_id
          and el.end_date < sysdate
          and el.active_flag = 'T'
   order by l.name, l.messaging_id;