with iv_bot as
(select ip_address
,agent
,ted_access_log.bot_pk_id(p_ip_address => ip_address, p_user_agent => agent) as bot_pk_id
from (select distinct t.ip_address
,t.agent
from apex_workspace_activity_log t
where t.workspace = 'TEDSTRUIK'
and t.application_id = nvl(v('APP_ID'), 25384)
and t.error_message is not null)),
iv_a as
(select t.view_date
,p.last_updated_on
,t.page_id
,t.page_name
,t.log_context
,t.error_message
,t.error_on_component_type
,t.error_on_component_name
,t.agent
,t.debug_page_view_id
,iv_bot.bot_pk_id
from apex_workspace_activity_log t
join apex_application_pages p on (p.workspace = t.workspace and p.application_id = t.application_id and p.page_id = t.page_id)
left join iv_bot on (iv_bot.ip_address = t.ip_address and nvl(iv_bot.agent, 'x') = nvl(t.agent, 'x'))
where t.workspace = 'TEDSTRUIK'
and t.application_id = nvl(v('APP_ID'), 25384)
and t.page_id < 9000
and t.error_message is not null
and t.error_message not like '%Note: End users get a different error message.%'
and t.error_on_component_type not in ('APEX_APPLICATION_AUTH')
and t.error_message not like '% must have some value.'
and not (t.page_id = 2 and t.error_message = 'ORA-12725: unmatched parentheses in regular expression')
and not (t.page_id = 1021 and t.error_on_component_type in ('WWV_FLOW_SECURITY'))
and not (t.page_id = 1029 and t.error_on_component_type in ('WWV_FLOW_SECURITY'))
and not (t.page_id = 1050 and t.error_message like 'APEX - ERR-1002 Unable to find item ID for item "&#x%')
and not (t.page_id = 1098 and t.error_on_component_type = 'report')
and not (t.page_id = 1107 and t.error_message like 'Unable to populate collection.%')
and t.error_message not like 'APEX - Session state protection violation: %'
and t.error_message not like 'APEX - Checksum format error - %'
and t.error_message not like 'APEX - Checksum content error - %'
and t.error_on_component_name <> 'worksheet ACTION FILTER UPDATE'
and t.error_on_component_name <> 'worksheet ACTION FILTER ADD'
and t.error_message not like 'Highlight Name must be unique.%'
and t.error_message not like '%Ensure the page template in use on page % is of template type "Dialog page"%'
and t.error_message not like 'APEX - Access Denied - Access denied by Application security check%'
)
select max(iv_a.view_date) as date_last
,trim(abs(round((iv_a.last_updated_on - max(iv_a.view_date)) * 24 * 60))) || ' minutes ' ||
(case
when max(iv_a.view_date) > iv_a.last_updated_on then
'AFTER'
else
'before'
end) || ' last update' as last_error_occurred
,min(view_date) as date_first
,count(*) as n
,max(iv_a.debug_page_view_id) as debug_page_view_id
,iv_a.page_id
,iv_a.page_name
,iv_a.log_context
,iv_a.error_message
,iv_a.error_on_component_type
,iv_a.error_on_component_name
from iv_a
where iv_a.bot_pk_id is null
group by iv_a.last_updated_on
,iv_a.page_id
,iv_a.page_name
,iv_a.log_context
,iv_a.error_message
,iv_a.error_on_component_type
,iv_a.error_on_component_name
,iv_a.bot_pk_id