Below is a query that shows in a fairly accurate way what the scheduled data flows are today. You just need to determine in which schema the SNP_PLAN_AGENT table is located and replace ODI_EXEC_REPO with the name of that schema..
The query returns the following columns:
- SCEN_NAME - scenario name
- SCEN_START_DATE - scenario start date (second resolution)
- DATE_FROM - start of the time window in which scenarios are started
- DATE_TO - end of the time window in which scenarios are started
- S_BEGIN_DATE - start of schedule activation period
- S_END_DATE - end of schedule activation period
- S_WEEK_DAY - on which day of the week scenarios are started
- S_EX_DAYS_MONTH - which days of the week are excluded
- S_EX_DAYS_WEEK - which days of the week are excluded
- LAGENT_NAME - ODI agent name
WITH FUNCTION FDb_IsDayOfMonthExcluded(
pc_S_EX_DAYS_MONTH VARCHAR2,
pn_Day NUMBER)
RETURN NUMBER DETERMINISTIC
--- 1 DAY is excluded, 0 ELSE
IS
BEGIN
IF pc_S_EX_DAYS_MONTH IS NULL THEN
RETURN 0;
END IF;
FOR EL IN
(
SELECT LP,
TOKEN,
CASE
WHEN TOKEN LIKE '%-%'
THEN
REGEXP_SUBSTR (token,
'[[:digit:]]{1,2}',1,1)
ELSE
NULL
END AS START_PERIOD,
CASE
WHEN TOKEN LIKE '%-%'
THEN
REGEXP_SUBSTR (token,
'[[:digit:]]{1,2}', 1, 2)
ELSE
NULL
END AS END_PERIOD,
CASE
WHEN TOKEN NOT LIKE '%-%' THEN
TO_NUMBER( TOKEN)
ELSE
NULL
END AS DAY_OF_MONTH
FROM ( SELECT LEVEL LP,
REGEXP_SUBSTR (pc_S_EX_DAYS_MONTH,
'([[:digit:]]{1,2}((-)[[:digit:]]{1,2}){0,1})',
1,
LEVEL) TOKEN
FROM DUAL
CONNECT BY LEVEL <=
REGEXP_COUNT (
pc_S_EX_DAYS_MONTH,
'([[:digit:]]{1,2}((-)[[:digit:]]{1,2}){0,1})',
1))
)
LOOP
IF EL.DAY_OF_MONTH IS NOT NULL THEN
IF EL.DAY_OF_MONTH = pn_Day THEN
RETURN 1;
END IF;
ELSIF
pn_Day BETWEEN EL.START_PERIOD AND EL.END_PERIOD THEN
RETURN 1;
END IF;
END LOOP;
RETURN 0;
END;
SELECT *
FROM (SELECT SCEN_NAME,
SCEN_START_DATE
+ NUMTODSINTERVAL (S_MINUTE, 'minute')
+ NUMTODSINTERVAL (S_SECOND, 'second')
SCEN_START_DATE,
S_BEGIN_HOUR + (TRUNC (SYSDATE) - TRUNC (S_BEGIN_HOUR))
DATE_FROM,
S_END_HOUR + (TRUNC (SYSDATE) - TRUNC (S_BEGIN_HOUR))
DATE_TO,
S_BEGIN_DATE,
S_END_DATE,
S_WEEK_DAY,
S_EX_DAYS_MONTH,
S_EX_DAYS_WEEK,
LAGENT_NAME
FROM ( SELECT TRUNC (SYSTIMESTAMP)
+ NUMTODSINTERVAL (LEVEL - 1, 'hour') SCEN_START_DATE
FROM DUAL
CONNECT BY LEVEL <= 24),
ODI_EXEC_REPO.SNP_PLAN_AGENT PA
WHERE S_TYPE = 'H' AND STAT_PLAN != 'D'
UNION ALL
SELECT PA.SCEN_NAME,
TO_DATE (
EXTRACT (YEAR FROM SYSDATE)
|| '-'
|| LPAD (EXTRACT (MONTH FROM SYSDATE), 2, '0')
|| '-'
|| LPAD (EXTRACT (DAY FROM SYSDATE), 2, '0')
|| ' '
|| LPAD (S_HOUR, 2, '0')
|| ':'
|| LPAD (S_MINUTE, 2, '0')
|| ':'
|| LPAD (S_SECOND, 2, '0'),
'YYYY-MM-DD HH24:MI:SS')
AS SCEN_START_DATE,
S_BEGIN_HOUR + (TRUNC (SYSDATE) - TRUNC (S_BEGIN_HOUR))
DATE_FROM,
S_END_HOUR + (TRUNC (SYSDATE) - TRUNC (S_BEGIN_HOUR))
DATE_TO,
S_BEGIN_DATE,
S_END_DATE,
S_WEEK_DAY,
S_EX_DAYS_MONTH,
S_EX_DAYS_WEEK,
LAGENT_NAME
FROM ODI_EXEC_REPO.SNP_PLAN_AGENT PA
WHERE S_TYPE IN ('D', 'W') AND STAT_PLAN != 'D'
UNION ALL
SELECT PA.SCEN_NAME,
TO_DATE (
EXTRACT (YEAR FROM SYSDATE)
|| '-'
|| LPAD (EXTRACT (MONTH FROM SYSDATE), 2, '0')
|| '-'
|| LPAD (S_MONTH_DAY, 2, '0')
|| ' '
|| LPAD (S_HOUR, 2, '0')
|| ':'
|| LPAD (S_MINUTE, 2, '0')
|| ':'
|| LPAD (S_SECOND, 2, '0'),
'YYYY-MM-DD HH24:MI:SS')
AS SCEN_START_DATE,
S_BEGIN_HOUR + (TRUNC (SYSDATE) - TRUNC (S_BEGIN_HOUR))
DATE_FROM,
S_END_HOUR + (TRUNC (SYSDATE) - TRUNC (S_BEGIN_HOUR))
DATE_TO,
S_BEGIN_DATE,
S_END_DATE,
S_WEEK_DAY,
S_EX_DAYS_MONTH,
S_EX_DAYS_WEEK,
LAGENT_NAME
FROM ODI_EXEC_REPO.SNP_PLAN_AGENT PA
WHERE S_TYPE = 'M'
AND STAT_PLAN != 'D'
AND EXTRACT (DAY FROM SYSDATE) = S_MONTH_DAY
UNION ALL
SELECT PA.SCEN_NAME,
TO_DATE (
S_YEAR
|| '-'
|| LPAD (S_MONTH, 2, '0')
|| '-'
|| LPAD (S_DAY, 2, '0')
|| ' '
|| LPAD (S_HOUR, 2, '0')
|| ':'
|| LPAD (S_MINUTE, 2, '0')
|| ':'
|| LPAD (S_SECOND, 2, '0'),
'YYYY-MM-DD HH24:MI:SS')
AS SCEN_START_DATE,
S_BEGIN_HOUR + (TRUNC (SYSDATE) - TRUNC (S_BEGIN_HOUR))
DATE_FROM,
S_END_HOUR + (TRUNC (SYSDATE) - TRUNC (S_BEGIN_HOUR))
DATE_TO,
S_BEGIN_DATE,
S_END_DATE,
S_WEEK_DAY,
S_EX_DAYS_MONTH,
S_EX_DAYS_WEEK,
LAGENT_NAME
FROM ODI_EXEC_REPO.SNP_PLAN_AGENT PA
WHERE S_TYPE = 'S' AND STAT_PLAN != 'D')
WHERE (SCEN_START_DATE >= DATE_FROM OR DATE_FROM IS NULL)
AND (SCEN_START_DATE <= DATE_TO OR DATE_TO IS NULL)
AND (SCEN_START_DATE >= S_BEGIN_DATE OR S_BEGIN_DATE IS NULL)
AND ( SCEN_START_DATE <= S_END_DATE
OR S_END_DATE IS NULL
AND ( S_WEEK_DAY LIKE '%' || TO_CHAR (SYSDATE, 'd') || '%'
OR S_WEEK_DAY IS NULL))
AND SCEN_START_DATE < SYSDATE
AND FDb_IsDayOfMonthExcluded(
pc_S_EX_DAYS_MONTH => S_EX_DAYS_MONTH,
pn_Day => EXTRACT( DAY FROM SYSDATE)
) = 0
ORDER BY SCEN_START_DATE ;
