Oracle Analytics Cloud and Server
BI publisher variable not getting updated
Summary
Variable PROCESSSTATUS is not getting updated,This is a BI stored procedure-to check the child jobs process status
Content
This is a BI stored procedure which is to check the overall status - the child job status and respond, the initial query is to check for the status and in while condition the loop needs to be set as long as status is in running status.
The query and logic is working as expected only issues in with PROCESSSTATUS which is not getting updated, please advise
DECLARE
type refcursor is REF CURSOR;
xdo_cursor refcursor;
p_request_id varchar2(150):=:p_request_id;
Counter number(2):=0;
PROCESSSTATUS varchar2(150):=null;
BEGIN
DBMS_LOCK.Sleep(10);
OPEN:xdo_cursor FOR
select
CASE WHEN RUNNING>0 THEN 'RUNNING'
WHEN ERROR>0 THEN 'NO'
WHEN SUCCESS=1 THEN 'RUNNING'
WHEN SUCCESS>0 THEN 'YES'
ELSE 'NO' END
STATUS INTO PROCESSSTATUS
from(
select COUNT(CASE WHEN STATE IN (4,12,17) THEN 1 ELSE NULL END) SUCCESS,
COUNT(CASE WHEN STATE IN (1,2,3,13,14) THEN 1 ELSE NULL END) RUNNING,
COUNT(CASE WHEN STATE IN (5,6,7,8,9,10,11,15,16,18,19) THEN 1 ELSE NULL END) ERROR
from
ess_request_history r
where r.requestid in
(
select REQUEST_ID from (
WITH DATA AS
(select value request_id from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'apps.fin.xla.xlafsnrpt.childRequests'
and requestid in (select requestid from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'submit.argument23'
and value = 'XLA_LOAD_'||:p_request_id
))
SELECT trim(regexp_substr(request_id, '[^,]+', 1, LEVEL)) request_id
FROM DATA
CONNECT BY instr(request_id, ',', 1, LEVEL - 1) > 0
UNION ALL
select TO_CHAR(requestid) from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'submit.argument1'
and value in
(select to_CHAR(requestid) from
FUSION.ESS_REQUEST_PROPERTY
where name = 'submit.argument19'
and value in
(select 'FUSION.XLA_'||requestid||'_GLT_Q' from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'submit.argument23'
and value = 'XLA_LOAD_'||:p_request_id
))
UNION ALL
select to_CHAR(requestid) from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'submit.argument23'
and value = 'XLA_LOAD_'||:p_request_id
UNION ALL
select to_CHAR(requestid) from
FUSION.ESS_REQUEST_PROPERTY
where name = 'submit.argument19'
and value in
(select 'FUSION.XLA_'||requestid||'_GLT_Q' from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'submit.argument23'
and value = 'XLA_LOAD_'||:p_request_id
)
UNION ALL
select to_char(requestid ) from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'submit.argument1'
and value in
(select to_CHAR(requestid) from
FUSION.ESS_REQUEST_PROPERTY
where name = 'submit.argument19'
and value in
(select 'FUSION.XLA_'||requestid||'_GLT_Q' from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'submit.argument23'
and value = 'XLA_LOAD_'||:p_request_id
)
)
UNION ALL
select :p_request_id from dual
UNION ALL
select to_char(requestid ) from
FUSION.ess_request_history
where
definition = 'JobDefinition://oracle/apps/ess/financials/subledgerAccounting/shared/XLARPMPA'
AND trunc(submission) = trunc(sysdate)
UNION ALL
SELECT distinct to_char(requestid )
FROM fusion_ora_ess.request_property_view WHERE value in (
SELECT value FROM fusion_ora_ess.request_property_view WHERE
requestid in ( SELECT requestid FROM fusion_ora_ess.request_property_view WHERE NAME='submit.argument17'
AND value in ( SELECT TO_CHAR(requestid) FROM fusion_ora_ess.request_property_view WHERE NAME='submit.argument23'
AND value = 'XLA_LOAD_'||:p_request_id ) ) AND NAME='submit.argument18' )
AND NAME='submit.argument3'
))
);
while PROCESSSTATUS='RUNNING' LOOP
Counter:=Counter+1;
DBMS_LOCK.Sleep(100);
select
CASE WHEN RUNNING>0 THEN 'RUNNING'
WHEN ERROR>0 THEN 'NO'
WHEN SUCCESS=1 THEN 'RUNNING'
WHEN SUCCESS>0 THEN 'YES'
ELSE 'NO' END
STATUS INTO PROCESSSTATUS
from(
select COUNT(CASE WHEN STATE IN (4,12,17) THEN 1 ELSE NULL END) SUCCESS,
COUNT(CASE WHEN STATE IN (1,2,3,13,14) THEN 1 ELSE NULL END) RUNNING,
COUNT(CASE WHEN STATE IN (5,6,7,8,9,10,11,15,16,18,19) THEN 1 ELSE NULL END) ERROR
from
ess_request_history r
where r.requestid in
(
select REQUEST_ID from (
WITH DATA AS
(select value request_id from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'apps.fin.xla.xlafsnrpt.childRequests'
and requestid in (select requestid from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'submit.argument23'
and value = 'XLA_LOAD_'||:p_request_id
))
SELECT trim(regexp_substr(request_id, '[^,]+', 1, LEVEL)) request_id
FROM DATA
CONNECT BY instr(request_id, ',', 1, LEVEL - 1) > 0
UNION ALL
select TO_CHAR(requestid) from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'submit.argument1'
and value in
(select to_CHAR(requestid) from
FUSION.ESS_REQUEST_PROPERTY
where name = 'submit.argument19'
and value in
(select 'FUSION.XLA_'||requestid||'_GLT_Q' from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'submit.argument23'
and value = 'XLA_LOAD_'||:p_request_id
))
UNION ALL
select to_CHAR(requestid) from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'submit.argument23'
and value = 'XLA_LOAD_'||:p_request_id
UNION ALL
select to_CHAR(requestid) from
FUSION.ESS_REQUEST_PROPERTY
where name = 'submit.argument19'
and value in
(select 'FUSION.XLA_'||requestid||'_GLT_Q' from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'submit.argument23'
and value = 'XLA_LOAD_'||:p_request_id
)
UNION ALL
select to_char(requestid ) from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'submit.argument1'
and value in
(select to_CHAR(requestid) from
FUSION.ESS_REQUEST_PROPERTY
where name = 'submit.argument19'
and value in
(select 'FUSION.XLA_'||requestid||'_GLT_Q' from
FUSION.ESS_REQUEST_PROPERTY
where 1=1
and name = 'submit.argument23'
and value = 'XLA_LOAD_'||:p_request_id
)
)
UNION ALL
select :p_request_id from dual
UNION ALL
select to_char(requestid ) from
FUSION.ess_request_history
where
definition = 'JobDefinition://oracle/apps/ess/financials/subledgerAccounting/shared/XLARPMPA'
AND trunc(submission) = trunc(sysdate)
UNION ALL
SELECT distinct to_char(requestid )
FROM fusion_ora_ess.request_property_view WHERE value in (
SELECT value FROM fusion_ora_ess.request_property_view WHERE
requestid in ( SELECT requestid FROM fusion_ora_ess.request_property_view WHERE NAME='submit.argument17'
AND value in ( SELECT TO_CHAR(requestid) FROM fusion_ora_ess.request_property_view WHERE NAME='submit.argument23'
AND value = 'XLA_LOAD_'||:p_request_id ) ) AND NAME='submit.argument18' )
AND NAME='submit.argument3'
))
);
END LOOP;
END;