View: ALR_PERIODIC_ALERTS_VIEW

Product:	ALR - Alert
Description:	Enabled periodic alerts and their next scheduled check
Implementation/DBA Data:	APPS.ALR_PERIODIC_ALERTS_VIEW

View Text

SELECT APPLICATION_ID
, ALERT_ID
, ALERT_NAME
, DECODE(FREQUENCY_TYPE
, 'C'
, /* START EVERY 'N' CALENDAR DAYS DECODE */ DECODE(TRUNC(NVL(DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE ) ) + NVL ( DAYS_BETWEEN_CHECKS
, 1 )
, LEAST ( TRUNC ( SYSDATE )
, TRUNC(NVL(DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE))+ NVL ( DAYS_BETWEEN_CHECKS
, 1 ) )
, 'T'
, TO_CHAR (TRUNC (NVL (DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE))+ NVL ( DAYS_BETWEEN_CHECKS
, 1 )) )
, 'W'
, /* START WEEKLY DECODE */ DECODE ( NEXT_DAY ( TRUNC(NVL(DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE))
, TO_CHAR((TRUNC(SYSDATE
, 'IW')-1)+FNDLK.LOOKUP_CODE-1
, 'DAY'))
, LEAST ( TRUNC ( SYSDATE )
, NEXT_DAY ( TRUNC ( NVL ( DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE))
, TO_CHAR((TRUNC(SYSDATE
, 'IW')-1)+ FNDLK.LOOKUP_CODE-1
, 'DAY')) )
, 'T'
, TO_CHAR (NEXT_DAY ( TRUNC ( NVL ( DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE))
, TO_CHAR((TRUNC(SYSDATE
, 'IW')-1)+ FNDLK.LOOKUP_CODE-1
, 'DAY')) ) )
, 'M'
, /* START MONTHLY DECODE */ REPLACE( TO_CHAR( GREATEST( SYSDATE
, ADD_MONTHS( TO_DATE( MONTHLY_CHECK_DAY_NUM || '-01-' || TO_CHAR( NVL(DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE)
, 'RR')
, 'DD-MM-RR' )
, TRUNC( MONTHS_BETWEEN( TRUNC( NVL(DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE)
, 'MON')
, TRUNC( NVL(DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE)
, 'RR') ) + DECODE( LEAST( LPAD(MONTHLY_CHECK_DAY_NUM
, 2
, '0')
, TO_CHAR( LAST_DAY(NVL( DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE))
, 'DD' ) )
, LEAST( LPAD(MONTHLY_CHECK_DAY_NUM
, 2
, '0')
, TO_CHAR( NVL(DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE)
, 'DD') )
, 1
, 0 ) ) ) )
, 'DD-MON-RR' )
, TO_CHAR(SYSDATE
, 'DD-MON-RR')
, 'T' )
, 'B'
, /* EVERY 'N' BUSSINESS DAYS DECODE */ DECODE ( TRUNC (NVL(DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE)) + TRUNC ( DAYS_BETWEEN_CHECKS/5 ) * 7 + DECODE( TO_CHAR(NVL(DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE)
, 'D')
, '2'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 1
, 2
, 2
, 3
, 3
, 4
, 4
, 5
, 7
, 6
, 8
, 0 )
, '3'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 1
, 2
, 2
, 3
, 3
, 4
, 6
, 5
, 7
, 6
, 8
, 0 )
, '4'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 1
, 2
, 2
, 3
, 5
, 4
, 6
, 5
, 7
, 6
, 8
, 0 )
, '5'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 1
, 2
, 4
, 3
, 5
, 4
, 6
, 5
, 7
, 6
, 8
, 0 )
, '6'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 3
, 2
, 4
, 3
, 5
, 4
, 6
, 5
, 7
, 6
, 10
, 0 )
, '7'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 2
, 2
, 3
, 3
, 4
, 4
, 5
, 5
, 6
, 6
, 9
, 0 )
, '1'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 1
, 2
, 2
, 3
, 3
, 4
, 4
, 5
, 5
, 6
, 8
, 0 )
, 0)
, LEAST( TRUNC (SYSDATE)
, TRUNC ( NVL ( DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE))+ TRUNC ( DAYS_BETWEEN_CHECKS/5 )* 7 + DECODE(TO_CHAR( NVL (DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE )
, 'D')
, '2'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 1
, 2
, 2
, 3
, 3
, 4
, 4
, 5
, 7
, 6
, 8
, 0 )
, '3'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 1
, 2
, 2
, 3
, 3
, 4
, 6
, 5
, 7
, 6
, 8
, 0 )
, '4'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 1
, 2
, 2
, 3
, 5
, 4
, 6
, 5
, 7
, 6
, 8
, 0 )
, '5'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 1
, 2
, 4
, 3
, 5
, 4
, 6
, 5
, 7
, 6
, 8
, 0 )
, '6'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 3
, 2
, 4
, 3
, 5
, 4
, 6
, 5
, 7
, 6
, 10
, 0 )
, '7'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 2
, 2
, 3
, 3
, 4
, 4
, 5
, 5
, 6
, 6
, 9
, 0 )
, '1'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 1
, 2
, 2
, 3
, 3
, 4
, 4
, 5
, 5
, 6
, 8
, 0 )
, 0))
, 'T'
, TRUNC ( NVL ( DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE))+ TRUNC ( DAYS_BETWEEN_CHECKS/5 ) * 7 + DECODE( TO_CHAR( NVL ( DATE_LAST_CHECKED
, ALRALR.START_DATE_ACTIVE )
, 'D')
, '2'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 1
, 2
, 2
, 3
, 3
, 4
, 4
, 5
, 7
, 6
, 8
, 0 )
, '3'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 1
, 2
, 2
, 3
, 3
, 4
, 6
, 5
, 7
, 6
, 8
, 0 )
, '4'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 1
, 2
, 2
, 3
, 5
, 4
, 6
, 5
, 7
, 6
, 8
, 0 )
, '5'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 1
, 2
, 4
, 3
, 5
, 4
, 6
, 5
, 7
, 6
, 8
, 0)
, '6'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 3
, 2
, 4
, 3
, 5
, 4
, 6
, 5
, 7
, 6
, 10
, 0 )
, '7'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 2
, 2
, 3
, 3
, 4
, 4
, 5
, 5
, 6
, 6
, 9
, 0 )
, '1'
, DECODE ( MOD ( DAYS_BETWEEN_CHECKS
, 5)
, 1
, 1
, 2
, 2
, 3
, 3
, 4
, 4
, 5
, 5
, 6
, 8
, 0 )
, 0))
, 'O'
, 'O'
, 'O' ) NEXT_SCHEDULED_CHECK
, 'A' TYPE
, CHECK_ONCE_DAILY_FLAG
, DECODE( CHECK_ONCE_DAILY_FLAG
, 'Y'
, LPAD(TO_CHAR( TRUNC(TO_NUMBER(NVL(CHECK_START_TIME
, 0 ) ) / ( 60*60)))
, 2
, '0' ) || ':' || LPAD(TO_CHAR( TRUNC(MOD(TO_NUMBER(NVL(CHECK_START_TIME
, 0))
, (60*60)) / 60) )
, 2
, '0' ) || ':' || LPAD(TO_CHAR( MOD(MOD( TO_NUMBER(NVL(CHECK_START_TIME
, 0) )
, ( 60 * 60 ) )
, 60 ) )
, 2
, '0' )
, NULL ) CHECK_TIME
, DECODE( CHECK_ONCE_DAILY_FLAG
, 'N'
, TRUNC( TO_NUMBER( NVL( CHECK_START_TIME
, 0 ) ) / ( 60 * 60 ) )
, 0 ) START_HOURS
, DECODE( CHECK_ONCE_DAILY_FLAG
, 'N'
, TRUNC( MOD( TO_NUMBER(NVL( CHECK_START_TIME
, 0))
, (60*60))/60)
, 0 ) START_MINUTES
, DECODE( CHECK_ONCE_DAILY_FLAG
, 'N'
, TRUNC( TO_NUMBER( NVL( CHECK_END_TIME
, 86399 ) )/ ( 60 * 60 ) )
, 23 ) END_HOURS
, DECODE( CHECK_ONCE_DAILY_FLAG
, 'N'
, TRUNC( MOD( TO_NUMBER(NVL(CHECK_END_TIME
, 86399))
, (60*60)) / 60 )
, 59 ) END_MINUTES
, DECODE( CHECK_ONCE_DAILY_FLAG
, 'N'
, TRUNC(TO_NUMBER(NVL(SECONDS_BETWEEN_CHECKS
, (60*60))) / 60 )
, 0 ) INTERVAL_MINUTES
FROM ALR_ALERTS ALRALR
, FND_LOOKUPS FNDLK
WHERE ALERT_CONDITION_TYPE = 'P'
AND ALRALR.ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(ALRALR.START_DATE_ACTIVE)
AND TRUNC(NVL(ALRALR.END_DATE_ACTIVE
, SYSDATE+1))
AND FNDLK.LOOKUP_TYPE='DAY_OF_WEEK'
AND FNDLK.LOOKUP_CODE = NVL(WEEKLY_CHECK_DAY
, '1') UNION ALL SELECT APPLICATION_ID
, PERIODIC_SET_ID ALERT_ID
, NAME ALERT_NAME
, 'O' NEXT_SCHEDULED_CHECK
, 'S' TYPE
, NULL
, NULL
, 0
, 0
, 0
, 0
, 0
FROM ALR_PERIODIC_SETS

Columns