eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.ALR_PERIODIC_ALERTS_VIEW

Source

View Text - Preformatted


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

View Text - HTML Formatted

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