SQL & PL/SQL

Oracle 19c

Hello Team,

kindly advise how to get notified before 365. We have to get notified 30 days before 17-FEB-25. the password expiry for one account is 17-FEB-25

select sum(SEVERITY * SIGN(COUNT)) as RESULT_CODE from (
  select 1 as SEVERITY, count(*) as COUNT
  from dba_users
  where account_status = 'OPEN' and (expiry_date > sysdate+7) and (expiry_date <= sysdate+14)
  union
  select  2 as SEVERITY, count(*) as COUNT
  from dba_users
  where (account_status = 'OPEN' and (expiry_date <= sysdate+7)) or (account_status = 'EXPIRED(GRACE)') or (account_status = 'EXPIRED')
)

of note, the grace time is 7 days.

Regards,

Roshan