DELIMITER //
CREATE or replace PROCEDURE "bga_execute_"(pRNK number)
    SQL SECURITY INVOKER
AS
jbga_name bga.bga_name%TYPE;
jsql VARCHAR2(16377);
jsql0 VARCHAR2(4000);
jfound_irregular NUMBER(1);
jbga_running NUMBER(1);
jbga_id bga.bga_id%TYPE;
jbga_type_id bga_type.bga_type_id%TYPE;
jbgao_id bgao.bgao_id%TYPE;
pGR_ID bga.gr_id%TYPE;
pBGA_TYPE_DECLARE bga_type.BGA_TYPE_DECLARE%type;
pBGA_TYPE_PRE_ACTION bga_type.BGA_TYPE_PRE_ACTION%type;
pBGA_TYPE_SELECT bga_type.BGA_TYPE_SELECT%type;
pBGA_TYPE_REC_ACTION bga_type.BGA_TYPE_REC_ACTION%type;
pBGA_TYPE_POST_ACTION bga_type.BGA_TYPE_POST_ACTION%type;
ts DATETIME;
i number(4):=0;
aff NUMBER(1):=0;
prmcnt NUMBER(2);
oth NUMBER(1);
-- wapp                  NUMBER(1);
    bSes_id number(15,0);
    bJob_id number(10,0);
    bUser_id number(5,0);
    bSec_cnt number;
CURSOR cur IS
SELECT
a.bgao_id,
a.bgao_status,
a.bga_id
-- b.bga_type_id,
-- b.bga_name,
-- b.gr_id,
-- b.bga_enabled,
-- b.sch_id,
-- t.bga_type_code,
-- t.bga_type_name,
-- t.bga_type_desc,
-- t.bga_type_declare,
-- t.bga_type_pre_action,
-- t.bga_type_select,
-- t.bga_type_rec_action,
-- t.bga_type_post_action
FROM
bgao a
WHERE bgao_status = 1
AND EXISTS (SELECT 0 FROM 
bga b 
join bga_type t ON (b.bga_type_id=t.bga_type_id)
WHERE a.bga_id = b.bga_id
AND NOT t.bga_type_code = 'ANY_FROM_RP'
AND NOT EXISTS (SELECT 0 FROM bga_inferior i, bgao s, bga_state e
WHERE a.bga_id=i.bga_id and s.bga_id = i.superior_bga_id 
AND s.bga_id=e.bga_id
and s.bgao_status IN (1,2)
AND Decode(i.bga_id,NULL,1,/*is_bgao_locked(s.bgao_id)*/e.bga_running)=1
)
AND (b.bgaq_id IS NULL OR
NOT EXISTS (SELECT 0 FROM bga i, bgao s, bga_state e
WHERE s.bga_id=i.bga_id and i.bgaq_id = b.bgaq_id 
AND s.bga_id=e.bga_id
and s.bgao_status IN (1,2)
AND Decode(i.bga_id,NULL,1,/*is_bgao_locked(s.bgao_id)*/e.bga_running)=1
)
)
)
ORDER BY bgao_id
FOR UPDATE SKIP LOCKED;
CURSOR cul IS
WITH b AS
(SELECT g.bga_id,g.sch_id
,d.bga_last_exec_end
 FROM 
bga g
join bga_type t ON (g.bga_type_id=t.bga_type_id)
left join bga_state d ON (d.bga_id =g.bga_id)
WHERE g.bga_enabled = 1 AND
NOT t.bga_type_code = 'ANY_FROM_RP'
AND CASE WHEN g.bgaq_id IS NULL THEN 1 WHEN EXISTS (SELECT /*+no_unnest*/0 FROM bga b JOIN bga_state c ON (b.bga_id=c.bga_id) WHERE b.bgaq_id=g.bgaq_id AND b.bga_priority<g.bga_priority AND /*is_bga_locked(b.bga_id)=1*/c.bga_running=1) then 0 ELSE 1 END =1
)
,y AS 
(WITH RECURSIVE r AS (
SELECT 1 dn
UNION ALL
SELECT dn + 1 FROM r WHERE dn + 1 <=to_char(last_day(SYSDATE),'DD')
)
SELECT * FROM r)
,f AS
(WITH RECURSIVE r AS (
SELECT bga_id,sch_id,schi_nr,sch_per_id,sch_per_value,sch_start_per_id,sch_start_per_value,sch_start_day_per_value,wd_specific
,wd1,wd2,wd3,wd4,wd5,wd6,wd7,wd8,hld_id
,mo_specific,mo1,mo2,mo3,mo4,mo5,mo6,mo7,mo8,mo9,mo10,mo11,mo12
,hh_specific,hh1,hh2,hh3,hh4,hh5,hh6,hh7,hh8,hh9,hh10,hh11,hh12,hh13,hh14,hh15,hh16,hh17,hh18,hh19,hh20,hh21,hh22,hh23,hh24
, 1 column_value
,bga_last_exec_end 
FROM b join st_schi USING (sch_id)
WHERE
-- case 
-- WHEN sch_per_id=5 then truncmm(SYSDATE) 
-- WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) then trunchh(SYSDATE)
-- ELSE DATE(SYSDATE) END
-- + INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
-- >=Nvl(nullif(bga_last_exec_end,date'1899-12-30') + interval 1 second,Truncmm(SYSDATE - interval 40 day))
-- AND
case 
WHEN sch_per_id=5 then truncmm(SYSDATE) 
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) then trunchh(SYSDATE)
ELSE DATE(SYSDATE) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
-- + INTERVAL ROUND ((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) SECOND 
<=sysdate
UNION ALL
SELECT bga_id,sch_id,schi_nr,sch_per_id,sch_per_value,sch_start_per_id,sch_start_per_value,sch_start_day_per_value,wd_specific
,wd1,wd2,wd3,wd4,wd5,wd6,wd7,wd8,hld_id
,mo_specific,mo1,mo2,mo3,mo4,mo5,mo6,mo7,mo8,mo9,mo10,mo11,mo12
,hh_specific,hh1,hh2,hh3,hh4,hh5,hh6,hh7,hh8,hh9,hh10,hh11,hh12,hh13,hh14,hh15,hh16,hh17,hh18,hh19,hh20,hh21,hh22,hh23,hh24
, column_value+1
,bga_last_exec_end 
FROM r 
-- WHERE CASE WHEN cp_per_id=5 THEN Add_Months(da,1) WHEN da_per_id=5 THEN truncmm(da+ interval 1 day) ELSE da+ interval 1 day END<het
WHERE case 
WHEN sch_per_id=5 then truncmm(SYSDATE) 
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) then trunchh(SYSDATE)
ELSE DATE(SYSDATE) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND ((column_value/*-1*/)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) SECOND 
<=sysdate

-- Trunc(SYSDATE,CASE
-- WHEN sch_per_id=5 THEN 'MM'
-- WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN 'HH'
-- ELSE 'DD' END)
-- +sch_start_per_value/Decode(sch_start_per_id,1,86400,2,1440,3,24,1)
-- +(LEVEL-1)*sch_per_value/Decode(sch_per_id,1,86400,2,1440,3,24,1)
-- <=sysdate
-- 
)
SELECT * FROM r
WHERE
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
>=Nvl(nullif(bga_last_exec_end,date'1899-12-30') + interval 1 second,Truncmm(SYSDATE - interval 40 DAY))
and  
(NOT sch_per_id = 5 OR sch_start_day_per_value = cast(To_Char(
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
,'DD') AS int) OR wd_specific=1)
AND (wd_specific=0 OR
CASE
 CASE WHEN NOT hld_id IS NULL AND NOT sch_per_id = 5 THEN
  coalesce((SELECT wd FROM st_hld_sp_data WHERE hld_id=r.hld_id AND hday=date(
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
  )),(SELECT 8 FROM st_hld_data WHERE hld_id=r.hld_id AND mm=cast(To_Char(
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
  ,'MM') AS int) AND dd=cast(To_Char(
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
  ,'DD') AS int)),(weekday(
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
)+1))
 ELSE (weekday(
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
 )+1)
 END
WHEN 1 THEN wd1
WHEN 2 THEN wd2
WHEN 3 THEN wd3
WHEN 4 THEN wd4
WHEN 5 THEN wd5
WHEN 6 THEN wd6
WHEN 7 THEN wd7
WHEN 8 THEN wd8
END = 1
AND
(NOT sch_per_id = 5
OR sch_start_day_per_value>0 AND
-- iki cia
(sch_start_day_per_value = (SELECT Count(*) FROM y
WHERE
CASE
 (weekday(truncmm(
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
 )+ INTERVAL (dn-1) DAY)+1)
WHEN 1 THEN wd1
WHEN 2 THEN wd2
WHEN 3 THEN wd3
WHEN 4 THEN wd4
WHEN 5 THEN wd5
WHEN 6 THEN wd6
WHEN 7 THEN wd7
WHEN 8 THEN wd8
END = 1
AND dn<=To_Char(
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
,'DD')))
OR sch_start_day_per_value<0 AND
(-sch_start_day_per_value = (SELECT Count(*) FROM y
WHERE
CASE
 (weekday(Add_Months(truncmm(
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
),1) - interval dn DAY)+1)
WHEN 1 THEN wd1
WHEN 2 THEN wd2
WHEN 3 THEN wd3
WHEN 4 THEN wd4
WHEN 5 THEN wd5
WHEN 6 THEN wd6
WHEN 7 THEN wd7
WHEN 8 THEN wd8
END = 1
AND dn<=To_Char(Last_Day(
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
),'DD')-To_Char(
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
,'DD')+1))
)
)
AND (mo_specific=0
OR mo_specific IN (1,2) AND mo_specific=CASE WHEN
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
>=truncd(add_months(truncyy(
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
),3)) - INTERVAL 1 DAY + interval 3 hour AND
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
<truncd(add_months(truncyy(
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
),10)) - INTERVAL 1 DAY + interval 3 hour THEN 2 ELSE 1 END
OR mo_specific = 3 AND
CASE
 cast(To_Char(
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
 ,'MM') AS int)
WHEN  1 THEN mo1
WHEN  2 THEN mo2
WHEN  3 THEN mo3
WHEN  4 THEN mo4
WHEN  5 THEN mo5
WHEN  6 THEN mo6
WHEN  7 THEN mo7
WHEN  8 THEN mo8
WHEN  9 THEN mo9
WHEN 10 THEN mo10
WHEN 11 THEN mo11
WHEN 12 THEN mo12
END = 1
)
AND (hh_specific=0 OR
CASE cast(To_Char(
/*sch_time begin*/
CASE WHEN sch_per_id=5 THEN truncmm(sysdate)
WHEN sch_start_per_id=1 AND sch_start_per_value<3600 AND (sch_per_id IN (1,2) AND sch_per_value<60 OR sch_per_id=3 AND sch_per_value=1) THEN trunchh(SYSDATE)
ELSE DATE(sysdate) END
+ INTERVAL ROUND(sch_start_per_value*Decode(sch_start_per_id,1,1,2,60,3,3600,86400)) SECOND
+ INTERVAL ROUND((column_value-1)*sch_per_value*Decode(sch_per_id,1,1,2,60,3,3600,86400)) second
/*sch_time end*/
,'HH24') AS int)+1
when  1 then HH1
when  2 then HH2
when  3 then HH3
when  4 then HH4
when  5 then HH5
when  6 then HH6
when  7 then HH7
when  8 then HH8
when  9 then HH9
when 10 then HH10
when 11 then HH11
when 12 then HH12
when 13 then HH13
when 14 then HH14
when 15 then HH15
when 16 then HH16
when 17 then HH17
when 18 then HH18
when 19 then HH19
when 20 then HH20
when 21 then HH21
when 22 then HH22
when 23 then HH23
when 24 then HH24
END = 1
)
)
SELECT a.bga_id,bga_last_exec_start,bga_curr_exec_start
FROM bga_chamber a
LEFT JOIN bga_state s ON (a.bga_id=s.bga_id)
WHERE
EXISTS (
SELECT 0 FROM 
b
WHERE b.bga_id=a.bga_id
AND EXISTS (SELECT 0 FROM f WHERE bga_id=b.bga_id)
) 
-- ORDER BY g.bga_priority,d.bga_last_exec_end nulls first,g.bga_id
 FOR UPDATE SKIP LOCKED;

pAnr NUMBER(6,0);-- #33146
pDb_time date;-- #33146
pValue number;-- #33146
pBgaStaSpty varchar2(1);

-- PROCEDURE log_state(pAnr in out number, pDb_time in date, pValue in number default null) is
-- pragma AUTONOMOUS_TRANSACTION;
--  BEGIN
--   MERGE INTO bga_state y
--      USING (
--      SELECT jBGA_ID bga_id, jBGA_RUNNING bga_running, jBGAO_ID bgao_id,jFOUND_IRREGULAR fi, aff suc FROM dual
--      ) u
--      ON (y.bga_id = u.bga_id)
--   WHEN MATCHED THEN UPDATE SET
--    y.bga_running=u.bga_running
--    ,y.bga_last_exec_start=CASE WHEN y.bga_running>u.bga_running AND u.fi=0 THEN y.bga_curr_exec_start ELSE y.bga_last_exec_start END
--    ,y.bga_last_exec_end=CASE WHEN y.bga_running>u.bga_running AND u.fi=0 THEN sysdate ELSE y.bga_last_exec_end END
--    ,y.bga_last_suc_exec_end=CASE WHEN y.bga_running>u.bga_running AND u.fi=0 AND u.suc=1 THEN sysdate ELSE y.bga_last_exec_end END
--    ,y.bga_curr_exec_start=CASE WHEN y.bga_running>u.bga_running THEN NULL WHEN y.bga_running<u.bga_running THEN sysdate ELSE y.bga_curr_exec_start END
--    ,y.bgao_id=CASE WHEN y.bga_running>u.bga_running THEN NULL WHEN y.bga_running<u.bga_running THEN u.bgao_id ELSE y.bgao_id END
--   WHEN NOT MATCHED THEN INSERT
--    (
--    y.bga_id, y.bga_running, y.bga_last_exec_start, y.bga_last_exec_end, y.bga_curr_exec_start, y.bgao_id
--   ) values (
--    u.bga_id, u.bga_running, sysdate, sysdate, Decode(u.bga_running,1,sysdate), u.bgao_id
--   );
--   -- S.J.2020.06.14 #33146 bga_sta realizacija
--   declare
--     bSes_id number(15,0);
--     bJob_id number(10,0);
--     bUser_id number(5,0);
--     bSec_cnt number;
--   begin
--     if pBgaStaSpty = '1' then
-- --      if pAnr is null then
-- --        -- 1. log_state autonominėje tranzakcijoje gali užpildyti bga_sta lentą ir turėti bga statistikos PK sequensą ANR ir db_time kaip outputą
-- --        -- įrodymui kad įrašas bga_sta jau sukurtas.
-- --        -- sesija(Interaktyvaus vykdymo atveju išsaugojamas ses_id iš st_curr_user.
-- --        -- ar job'as, koks job'as Konkretų job nuskaityti naudojant sys_context('USERENV','BG_JOB_ID').),
-- --        FOR t IN (SELECT SubStr(banner,InStr(banner,' ',1,2)+1,2)+0 vrs FROM v$version WHERE ROWNUM=1) LOOP
-- --        IF t.vrs<19 THEN
-- --        select BGA_STA_ANR_SEQ.nextval,ses_id, user_id, sys_context('USERENV','BG_JOB_ID')  into pAnr, bSes_id, bUser_id, bJob_id from st_curr_user;
-- --        else
-- --        select BGA_STA_ANR_SEQ.nextval,ses_id, user_id, (select  replace(object_name,'DBMS_JOB$_') job FROM user_objects WHERE object_id=sys_context('USERENV','BG_JOB_ID'))  into pAnr, bSes_id, bUser_id, bJob_id from st_curr_user;
-- --        END IF;
-- --        END LOOP;
-- --      end if;
--       if pAnr is null then
--         -- 1. log_state autonominėje tranzakcijoje gali užpildyti bga_sta lentą ir turėti bga statistikos PK sequensą ANR ir db_time kaip outputą
--         -- įrodymui kad įrašas bga_sta jau sukurtas.
--         -- sesija(Interaktyvaus vykdymo atveju išsaugojamas ses_id iš st_curr_user.
--         -- ar job'as, koks job'as Konkretų job nuskaityti naudojant sys_context('USERENV','BG_JOB_ID').),
--         select BGA_STA_ANR_SEQ.nextval,ses_id, user_id, sys_context('USERENV','BG_JOB_ID')  into pAnr, bSes_id, bUser_id, bJob_id from st_curr_user;
-- 
-- 
--         INSERT INTO bga_sta (db_time, anr, bga_id, user_id, sec_cnt, ses_id, job_id, bgao_id, status)
--         VALUES (pDb_time, panr, jBga_id, buser_id, 0, bSes_id, bJob_id, jBgao_id, 1);
--       else
--         UPDATE bga_sta SET sec_cnt=(sysdate - pDb_time)*24*60*60,status=Decode(aff,-1,-1),obj_cnt=pValue WHERE db_time = pdb_time and anr=panr;
--       end if;
-- --    bSec_cnt := (sysdate - pDb_time)*24*60*60;
-- --    MERGE INTO bga_sta y
-- --      USING (
-- --        SELECT pDb_time db_time, pAnr anr FROM dual
-- --       ) u
-- --      ON (y.db_time = u.db_time and y.anr=u.anr)
-- --    -- Antrą kartą užejus į log_state procedūrą daryti joje bga_sta atnaujinimą panaudojant bga_sta PK iš pirmo žingsnio ir kažkokią reikšmę iš 2 žingsnio
-- --    WHEN MATCHED THEN UPDATE SET
-- --      y.sec_cnt = bSec_cnt,-- sekundžių kiekį nuo bga_curr_exec_start iki sysdate
-- --      y.status = case when aff = -1 then -1 else null end ,-- klaida = -1
-- --      y.obj_cnt = pValue
-- --    WHEN NOT MATCHED THEN
-- --    INSERT (
-- --      y.db_time,
-- --      y.anr,
-- --      y.bga_id,
-- --      y.user_id,
-- --      y.sec_cnt,
-- --      y.ses_id,
-- --      y.job_id,
-- --      y.bgao_id,
-- --      y.status,
-- --      y.obj_cnt )
-- --    VALUES
-- --      ( u.db_time,
-- --        u.anr,
-- --        jBga_id,
-- --        buser_id,
-- --        0/*pradinis*/,
-- --        bSes_id,
-- --        bJob_id,
-- --        jBgao_id,
-- --        1/*pradinis*/,
-- --        NULL );
--     end if;
--   end;
-- 
-- 
-- 
--   COMMIT;
--  END;
-- PROCEDURE do_stuff (pGR_ID NUMBER,pBGA_TYPE_DECLARE VARCHAR2,pBGA_TYPE_PRE_ACTION VARCHAR2,pBGA_TYPE_SELECT VARCHAR2,pBGA_TYPE_REC_ACTION VARCHAR2,pBGA_TYPE_POST_ACTION VARCHAR2
--   , pValue out number) is
-- pragma AUTONOMOUS_TRANSACTION;
-- BEGIN
-- -- parameters
-- DELETE gttp;
-- PRMCNT:=0;
-- IF pGR_ID IS NULL THEN
-- jsql := 'DECLARE'||Chr(10)||'u T7.CRR;'||Chr(10);
-- IF NOT pBGA_TYPE_DECLARE IS NULL THEN
-- jsql := jsql||pBGA_TYPE_DECLARE||Chr(10);
-- END IF;
-- jsql := jsql||'BEGIN'||Chr(10);
-- jsql := jsql||'select c1 bulk collect into u from gttp;'||Chr(10);
-- IF NOT pBGA_TYPE_PRE_ACTION IS NULL THEN
-- jsql := jsql||pBGA_TYPE_PRE_ACTION||Chr(10);
-- END IF;
-- IF NOT pBGA_TYPE_SELECT IS NULL AND NOT pBGA_TYPE_REC_ACTION IS NULL THEN
-- jsql := jsql||'FOR c in ('||pBGA_TYPE_SELECT||') LOOP'||Chr(10);
-- jsql := jsql||pBGA_TYPE_REC_ACTION||Chr(10);
-- jsql := jsql||'END LOOP;'||Chr(10);
-- END IF;
-- IF NOT pBGA_TYPE_POST_ACTION IS NULL THEN
-- jsql := jsql||pBGA_TYPE_POST_ACTION||Chr(10);
-- END IF;
-- -- jsql := jsql||'COMMIT; END;';
-- jsql := jsql||'END;'; -- nereikia, nes yra COMMIT veliau vis tiek, zr. zemiau
-- -- parametru ikisimas
-- -- :01 parametrai
-- -- FOR p IN (SELECT * FROM bga_param WHERE bga_id = j.bga_id ORDER BY bga_param_nr) LOOP
-- FOR p IN (SELECT
-- a.bga_type_param_nr bga_param_nr
-- ,coalesce(b.bga_param_value,c.bga_param_value,nullif(Decode(a.bga_type_param_regular,1,a.bga_param_default_value),Decode(a.bga_type_param_key,'N:=:FORCED','0'))) bga_param_value
-- ,a.bga_type_param_key
-- ,a.bga_type_param_regular
-- ,a.bga_param_default_value
-- ,Rank() over (ORDER BY a.bga_type_param_nr) nr
--  FROM
-- bga_type_param a
-- left join bgao_param b ON (jbgao_id=b.bgao_id AND b.bga_param_nr =a.bga_type_param_nr)
-- left join bga_param  c ON (jbga_id =c.bga_id  AND c.bga_param_nr =a.bga_type_param_nr AND (a.bga_type_param_regular=1))
-- WHERE a.bga_type_id = jbga_type_id
-- ORDER BY 1) LOOP
-- prmcnt:=prmcnt+1;
-- IF p.bga_type_param_regular = 0 AND NOT p.bga_param_value IS NULL THEN
-- jfound_irregular:=1;
-- END IF;
-- INSERT INTO gttp (c1) VALUES (p.bga_param_value);
-- IF Upper(p.bga_type_param_key) LIKE 'D%' THEN
-- jsql := REPLACE(jsql,':'||LTrim(To_char(p.bga_param_nr,'00')),'to_date(u('||p.nr||'),''YYYY.MM.DD HH24:MI:SS'')');
-- ELSE
-- jsql := REPLACE(jsql,':'||LTrim(To_char(p.bga_param_nr,'00')),'u('||p.nr||')');
-- END IF;
-- IF NOT p.bga_param_value IS NULL THEN
-- IF p.bga_param_value =p.bga_param_default_value THEN
-- NULL;
-- else
-- jsql := REPLACE(jsql,'-- \'||LTrim(To_char(p.bga_param_nr,'00'))||'/');
-- END IF;
-- ELSE
-- jsql := REPLACE(jsql,'-- /'||LTrim(To_char(p.bga_param_nr,'00'))||'\');
-- END IF;
-- END LOOP;
-- -- :1 parametrai
-- FOR p IN (SELECT
-- a.bga_type_param_nr bga_param_nr
-- ,coalesce(b.bga_param_value,c.bga_param_value,nullif(Decode(a.bga_type_param_regular,1,a.bga_param_default_value),Decode(a.bga_type_param_key,'N:=:FORCED','0'))) bga_param_value
-- ,a.bga_type_param_key
-- ,a.bga_type_param_regular
-- ,a.bga_param_default_value
-- ,Rank() over (ORDER BY a.bga_type_param_nr) nr
--  FROM
-- bga_type_param a
-- left join bgao_param b ON (jbgao_id=b.bgao_id AND b.bga_param_nr =a.bga_type_param_nr)
-- left join bga_param  c ON (jbga_id =c.bga_id  AND c.bga_param_nr =a.bga_type_param_nr AND (a.bga_type_param_regular=1))
-- WHERE a.bga_type_id = jbga_type_id
-- ORDER BY 1) LOOP
-- IF p.bga_type_param_regular = 0 AND NOT p.bga_param_value IS NULL THEN
-- jfound_irregular:=1;
-- END IF;
-- IF Upper(p.bga_type_param_key) LIKE 'D%' THEN
-- jsql := REPLACE(jsql,':'||To_char(p.bga_param_nr),'to_date(u('||p.nr||'),''YYYY.MM.DD HH24:MI:SS'')');
-- ELSE
-- jsql := REPLACE(jsql,':'||To_char(p.bga_param_nr),'u('||p.nr||')');
-- END IF;
-- IF NOT p.bga_param_value IS NULL THEN
-- IF p.bga_param_value=p.bga_param_default_value THEN
-- NULL;
-- else
-- jsql := REPLACE(jsql,'-- \'||To_char(p.bga_param_nr)||'/');
-- END IF;
-- ELSE
-- jsql := REPLACE(jsql,'-- /'||To_char(p.bga_param_nr)||'\');
-- END IF;
-- END LOOP;
-- INSERT INTO gttp (c1) VALUES (To_Char(jbga_id));
-- jsql := REPLACE(jsql,':0','u('||(prmcnt+1)||')');
-- -- action
-- EXECUTE immediate jsql;
-- -- Dbms_Output.put_line(jsql);
-- ELSE
-- -- grupes atnaujinimas
-- jsql:=pBGA_TYPE_SELECT;
-- -- :01 parametrai
-- FOR p IN (SELECT
-- a.bga_type_param_nr bga_param_nr
-- ,coalesce(b.bga_param_value,c.bga_param_value,nullif(Decode(a.bga_type_param_regular,1,a.bga_param_default_value),Decode(a.bga_type_param_key,'N:=:FORCED','0'))) bga_param_value
-- ,a.bga_type_param_key
-- ,a.bga_type_param_regular
-- ,a.bga_param_default_value
-- ,Rank() over (ORDER BY a.bga_type_param_nr) nr
--  FROM
-- bga_type_param a
-- left join bgao_param b ON (jbgao_id=b.bgao_id AND b.bga_param_nr =a.bga_type_param_nr)
-- left join bga_param  c ON (jbga_id =c.bga_id  AND c.bga_param_nr =a.bga_type_param_nr AND (a.bga_type_param_regular=1))
-- WHERE a.bga_type_id = jbga_type_id
-- ORDER BY 1) LOOP
-- prmcnt:=prmcnt+1;
-- INSERT INTO gttp (c1) VALUES (p.bga_param_value);
-- IF Upper(p.bga_type_param_key) LIKE 'D%' THEN
-- jsql := REPLACE(jsql,':'||LTrim(To_char(p.bga_param_nr,'00')),'to_date(u('||p.nr||'),''YYYY.MM.DD HH24:MI:SS'')');
-- ELSE
-- jsql := REPLACE(jsql,':'||LTrim(To_char(p.bga_param_nr,'00')),'u('||p.nr||')');
-- END IF;
-- IF NOT p.bga_param_value IS NULL THEN
-- IF p.bga_param_value=p.bga_param_default_value THEN
-- NULL;
-- else
-- jsql := REPLACE(jsql,'-- \'||LTrim(To_char(p.bga_param_nr,'00'))||'/');
-- END IF;
-- ELSE
-- jsql := REPLACE(jsql,'-- /'||LTrim(To_char(p.bga_param_nr,'00'))||'\');
-- END IF;
-- END LOOP;
-- -- :1 parametrai
-- FOR p IN (SELECT
-- a.bga_type_param_nr bga_param_nr
-- ,coalesce(b.bga_param_value,c.bga_param_value,nullif(Decode(a.bga_type_param_regular,1,a.bga_param_default_value),Decode(a.bga_type_param_key,'N:=:FORCED','0'))) bga_param_value
-- ,a.bga_type_param_key
-- ,a.bga_type_param_regular
-- ,a.bga_param_default_value
-- ,Rank() over (ORDER BY a.bga_type_param_nr) nr
--  FROM
-- bga_type_param a
-- left join bgao_param b ON (jbgao_id=b.bgao_id AND b.bga_param_nr =a.bga_type_param_nr)
-- left join bga_param  c ON (jbga_id =c.bga_id  AND c.bga_param_nr =a.bga_type_param_nr AND (a.bga_type_param_regular=1))
-- WHERE a.bga_type_id = jbga_type_id
-- ORDER BY 1) LOOP
-- IF Upper(p.bga_type_param_key) LIKE 'D%' THEN
-- jsql := REPLACE(jsql,':'||To_char(p.bga_param_nr),'to_date(u('||p.nr||'),''YYYY.MM.DD HH24:MI:SS'')');
-- ELSE
-- jsql := REPLACE(jsql,':'||To_char(p.bga_param_nr),'u('||p.nr||')');
-- END IF;
-- IF NOT p.bga_param_value IS NULL THEN
-- IF p.bga_param_value=p.bga_param_default_value THEN
-- NULL;
-- else
-- jsql := REPLACE(jsql,'-- \'||To_char(p.bga_param_nr)||'/');
-- END IF;
-- ELSE
-- jsql := REPLACE(jsql,'-- /'||To_char(p.bga_param_nr)||'\');
-- END IF;
-- 
-- END LOOP;
-- INSERT INTO gttp (c1) VALUES (To_Char(jbga_id));
-- jsql := REPLACE(jsql,':0','u('||(prmcnt+1)||')');
-- wapp:= CASE WHEN jsql LIKE '%@%' THEN 0 ELSE 1 END;
-- DELETE gttu;
-- oth:=0;
-- begin
-- EXECUTE IMMEDIATE 'declare u T7.CRR; begin select c1 bulk collect into u from gttp; insert '||CASE WHEN wapp=1 THEN '/*+append*/' END||' into gttu (id1) select * from ('||jsql||'); end;';
-- EXCEPTION
-- WHEN OTHERS THEN
-- IF SQLERRM LIKE '%ORA-00913%' THEN
-- oth:=1;
-- else
-- RAISE;
-- END IF;
-- END;
-- IF oth=0 THEN
-- COMMIT;
-- EXECUTE IMMEDIATE 'begin delete grp WHERE (gr_id,point_id) in (select :1,point_id from (select point_id from grp where gr_id=:1 minus (select id1 from gttu where id1>0))); end;' USING pGR_ID;
-- EXECUTE IMMEDIATE 'begin INSERT INTO grp (gr_id,point_id) SELECT :1,o.* FROM (select id1 from gttu where id1>0) o MINUS (SELECT gr_id,point_id FROM grp WHERE gr_id=:1); end;' USING pGR_ID;
-- EXECUTE IMMEDIATE 'begin delete grc WHERE (cont_id,comp_id) in (select :1,comp_id from (select comp_id from grc where cont_id=:1 minus (select -id1 from gttu where id1<0))); end;' USING pGR_ID;
-- EXECUTE IMMEDIATE 'begin INSERT INTO grc (cont_id,comp_id) SELECT :1,o.* FROM (select -id1 from gttu where id1<0) o MINUS (SELECT cont_id,comp_id FROM grc WHERE cont_id=:1); end;' USING pGR_ID;
-- ELSE
-- -- other syntax
-- EXECUTE IMMEDIATE 'declare u T7.CRR; begin select c1 bulk collect into u from gttp; insert '||CASE WHEN wapp=1 THEN '/*+append*/' END||' into gttu (id1,d1,d2,id2,c1) select * from ('||jsql||'); end;';
-- --
-- COMMIT;
-- EXECUTE IMMEDIATE 'begin delete grp WHERE (gr_id,point_id,grp_bt) in (select :1,point_id,grp_bt from (select point_id,grp_bt,grp_et,grp_nr,grp_desc from grp where gr_id=:1 minus (select id1,nvl(d1,date''1899-12-30''),d2,id2,c1 from gttu where id1>0))); end;' USING pGR_ID;
-- EXECUTE IMMEDIATE 'begin INSERT INTO grp (gr_id,point_id,grp_bt,grp_et,grp_nr,grp_desc) SELECT :1,o.* FROM (select id1,nvl(d1,date''1899-12-30''),d2,id2,c1 from gttu where id1>0) o MINUS (SELECT gr_id,point_id,grp_bt,grp_et,grp_nr,grp_desc FROM grp WHERE gr_id=:1); end;' USING pGR_ID;
-- EXECUTE IMMEDIATE 'begin delete grc WHERE (cont_id,comp_id,grc_bt) in (select :1,comp_id,grc_bt from (select comp_id,grc_bt,grc_et,grc_nr,grc_desc from grc where cont_id=:1 minus (select -id1,nvl(d1,date''1899-12-30''),d2,id2,c1 from gttu where id1<0))); end;' USING pGR_ID;
-- EXECUTE IMMEDIATE 'begin INSERT INTO grc (cont_id,comp_id,grc_bt,grc_et,grc_nr,grc_desc) SELECT :1,o.* FROM (select -id1,nvl(d1,date''1899-12-30''),d2,id2,c1 from gttu where id1<0) o MINUS (SELECT cont_id,comp_id,grc_bt,grc_et,grc_nr,grc_desc FROM grc WHERE cont_id=:1); end;' USING pGR_ID;
-- END IF;
-- END IF;
-- -- S.J.2020.06.14 #33146 do_stuff procedūra autonominėje tranzakcijoje įvykdo užduotį,
-- -- kurioje buvo tarkim insert into gttt (c1,id1) values ('BGA_VAL',to_char(sysdate,'HH24'));
-- -- po užduoties įvykdymo toje pačioje autonominėje sesijoje ištraukia iš gttt ką reikia ir gražiną kaip outputą
-- if pBgaStaSpty = '1' then
--   select
--     (select id1 from gttt where c1='BGA_VAL' and rownum=1)
--   into pValue
--   from dual;
-- end if;
-- 
-- COMMIT;
-- aff:=1;
-- exception
-- when others THEN
-- aff:=-1;
-- ROLLBACK;
-- event_i(1,3,0,1,sysdate
-- ,trim(SubStr(jbga_name||': '||geterrm(SQLERRM),1,250))
-- ,3);
-- jsql0:=SubStr(jsql,1,4000);
-- UPDATE bga_debug SET sql_block =jsql0;
-- COMMIT;
-- END;
-- the real begin
BEGIN
if GET_LOCK('BGA_EXECUTE_'||pRNK,1)=1 then
reg_e('BGA_EXECUTE_'||pRNK);
  -- S.J.2020.06.14 #33146 bga_sta realizacija
  SELECT
    Max(Decode(spty_id,95,Nvl(spty_value,spty_default)))
  INTO
    pBgaStaSpty
  FROM spty WHERE spty_id IN (95);
  -- pBgaStaSpty:=1;-- laikinai testavimui
-- fill missing bga_chamber
INSERT INTO bga_chamber (bga_id)
SELECT a.bga_id FROM 
bga a
LEFT JOIN bga_chamber b ON (a.bga_id=b.bga_id)
WHERE bga_enabled=1 AND NOT sch_id IS NULL 
AND b.bga_id IS NULL;
COMMIT;
-- skubantiems -- greita kasa iki 5 prekiu
ts:=SYSDATE;
i:=0;
WHILE i<3000 AND SYSDATE<ts + interval 2 minute LOOP
aff:=0;
FOR r IN cur LOOP
-- iki cia
-- paimti jbga laukus kartu
-- patikrinti, kad bga_state nėra bgao_id
 SELECT Decode(bgao_status,DECODE(a.bgao_id,s.bgao_id,NULL,1),0,1), 
a.bgao_id,
-- a.bgao_status,
a.bga_id,
b.bga_type_id,
b.bga_name,
b.gr_id,
-- b.bga_enabled,
-- b.sch_id,
-- t.bga_type_code,
-- t.bga_type_name,
-- t.bga_type_desc,
t.bga_type_declare,
t.bga_type_pre_action,
t.bga_type_select,
t.bga_type_rec_action,
t.bga_type_post_action
 INTO aff
,jbgao_id
,jbga_id
,jbga_type_id
,jbga_name
,pGR_ID
,pBGA_TYPE_DECLARE
,pBGA_TYPE_PRE_ACTION
,pBGA_TYPE_SELECT
,pBGA_TYPE_REC_ACTION
,pBGA_TYPE_POST_ACTION
  FROM bgao a 
  JOIN bga b ON (a.bga_id=b.bga_id)
  JOIN bga_type t ON (b.bga_type_id=t.bga_type_id)
  LEFT JOIN bga_state s ON (a.bga_id=s.bga_id)
   WHERE a.bgao_id=r.bgao_id;
IF aff>0 THEN
  EXIT;
END IF;

-- jbga_id:=r.bga_id;
-- jbga_name:=r.bga_name;
-- jbga_type_id:=r.bga_type_id;
-- jbgao_id:=r.bgao_id;
jbga_running:=1;
jfound_irregular:=0;
-- nes antra karta gali uzeiti
pAnr:=null;
pValue:=null;
pDb_time:=SYSDATE;
-- iki cia
-- log_state(pAnr, pDb_time);
 update bga_state Y
,     (
     SELECT jBGA_ID bga_id, jBGA_RUNNING bga_running, jBGAO_ID bgao_id,jFOUND_IRREGULAR fi, aff suc 
     ) u
SET
   y.bga_running=u.bga_running
   ,y.bga_last_exec_start=CASE WHEN y.bga_running>u.bga_running AND u.fi=0 THEN y.bga_curr_exec_start ELSE y.bga_last_exec_start END
   ,y.bga_last_exec_end=CASE WHEN y.bga_running>u.bga_running AND u.fi=0 THEN sysdate ELSE y.bga_last_exec_end END
   ,y.bga_last_suc_exec_end=CASE WHEN y.bga_running>u.bga_running AND u.fi=0 AND u.suc=1 THEN sysdate ELSE y.bga_last_exec_end END
   ,y.bga_curr_exec_start=CASE WHEN y.bga_running>u.bga_running THEN NULL WHEN y.bga_running<u.bga_running THEN sysdate ELSE y.bga_curr_exec_start END
   ,y.bgao_id=CASE WHEN y.bga_running>u.bga_running THEN NULL WHEN y.bga_running<u.bga_running THEN u.bgao_id ELSE y.bgao_id END
WHERE y.bga_id = u.bga_id;

if SQL%rowcount=0 then
INSERT INTO bga_state
   (
   bga_id, bga_running, bga_last_exec_start, bga_last_exec_end, bga_curr_exec_start, bgao_id
  ) SELECT 
   jBGA_ID, jBGA_RUNNING, sysdate, sysdate, Decode(jBGA_RUNNING,1,sysdate), jBGAO_ID
   FROM DUAL WHERE NOT EXISTS (SELECT 0 FROM bga_state WHERE bga_id=jBGA_ID);

END if;
  -- S.J.2020.06.14 #33146 bga_sta realizacija
    if pBgaStaSpty = '1' then
      if pAnr is null then
        select BGA_STA_ANR_SEQ.nextval,ses_id, user_id/*, sys_context('USERENV','BG_JOB_ID')*/  into pAnr, bSes_id, bUser_id/*, bJob_id*/ from st_curr_user;
        



        INSERT INTO bga_sta (db_time, anr, bga_id, user_id, sec_cnt, ses_id, job_id, bgao_id, status)
        VALUES (pDb_time, panr, jBga_id, buser_id, 0, bSes_id, bJob_id, jBgao_id, 1);
      else
        UPDATE bga_sta SET sec_cnt=timestampdiff(second,pDb_time,SYSDATE),status=Decode(aff,-1,-1),obj_cnt=pValue WHERE db_time = pdb_time and anr=panr;
      end if;
    end if;
-- end log_state    
COMMIT;
-- STUFF
-- do_stuff(r.gr_id,r.bga_type_declare,r.bga_type_pre_action,r.bga_type_select,r.bga_type_rec_action,r.bga_type_post_action, pValue);
BEGIN
-- parameters
-- DELETE gttp;
PRMCNT:=0;
CREATE OR REPLACE TEMPORARY TABLE gttv (
  id1 INT(15) signed
  );
  jsql:='';
IF pGR_ID IS NULL THEN
IF NOT pBGA_TYPE_DECLARE IS NULL THEN
jsql := 'DECLARE'||Chr(10)||pBGA_TYPE_DECLARE||Chr(10);
END IF;
jsql := jsql||'BEGIN'||Chr(10);
IF NOT pBGA_TYPE_PRE_ACTION IS NULL THEN
jsql := jsql||pBGA_TYPE_PRE_ACTION||Chr(10);
END IF;
IF NOT pBGA_TYPE_SELECT IS NULL AND NOT pBGA_TYPE_REC_ACTION IS NULL THEN
jsql := jsql||'FOR c in ('||pBGA_TYPE_SELECT||') LOOP'||Chr(10);
jsql := jsql||pBGA_TYPE_REC_ACTION||Chr(10);
jsql := jsql||'END LOOP;'||Chr(10);
END IF;
IF NOT pBGA_TYPE_POST_ACTION IS NULL THEN
jsql := jsql||pBGA_TYPE_POST_ACTION||Chr(10);
END IF;
-- jsql := jsql||'COMMIT; END;';
jsql := jsql||'END'; -- nereikia, nes yra COMMIT veliau vis tiek, zr. zemiau
-- parametru ikisimas
-- :01 parametrai
-- FOR p IN (SELECT * FROM bga_param WHERE bga_id = j.bga_id ORDER BY bga_param_nr) LOOP
FOR p IN (SELECT
a.bga_type_param_nr bga_param_nr
,coalesce(b.bga_param_value,c.bga_param_value,nullif(Decode(a.bga_type_param_regular,1,a.bga_param_default_value),Decode(a.bga_type_param_key,'N:=:FORCED','0'))) bga_param_value
,a.bga_type_param_key
,a.bga_type_param_regular
,a.bga_param_default_value
,Rank() over (ORDER BY a.bga_type_param_nr) nr
 FROM
bga_type_param a
left join bgao_param b ON (jbgao_id=b.bgao_id AND b.bga_param_nr =a.bga_type_param_nr)
left join bga_param  c ON (jbga_id =c.bga_id  AND c.bga_param_nr =a.bga_type_param_nr AND (a.bga_type_param_regular=1))
WHERE a.bga_type_id = jbga_type_id
ORDER BY 1) LOOP
prmcnt:=prmcnt+1;
IF p.bga_type_param_regular = 0 AND NOT p.bga_param_value IS NULL THEN
jfound_irregular:=1;
END IF;
-- INSERT INTO gttp (c1) VALUES (p.bga_param_value);
execute immediate 'SET @u'||prmcnt||'='||case when p.bga_param_value IS NULL then 'null' ELSE '\''||p.bga_param_value||'\'' END;
-- iki cia
IF Upper(p.bga_type_param_key) LIKE 'D%' THEN
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,2,0),'cast(@u'||p.nr||' as DATETIME)');
ELSE
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,2,0),'@u'||p.nr);
END IF;
IF NOT p.bga_param_value IS NULL THEN
IF p.bga_param_value =p.bga_param_default_value THEN
NULL;
else
jsql := REPLACE(jsql,'-- \\'||lpad(p.bga_param_nr,2,0)||'/',\N);
END IF;
ELSE
jsql := REPLACE(jsql,'-- /'||lpad(p.bga_param_nr,2,0)||'\\',\N);
END IF;
END LOOP;
-- :1 parametrai
FOR p IN (SELECT
a.bga_type_param_nr bga_param_nr
,coalesce(b.bga_param_value,c.bga_param_value,nullif(Decode(a.bga_type_param_regular,1,a.bga_param_default_value),Decode(a.bga_type_param_key,'N:=:FORCED','0'))) bga_param_value
,a.bga_type_param_key
,a.bga_type_param_regular
,a.bga_param_default_value
,Rank() over (ORDER BY a.bga_type_param_nr) nr
 FROM
bga_type_param a
left join bgao_param b ON (jbgao_id=b.bgao_id AND b.bga_param_nr =a.bga_type_param_nr)
left join bga_param  c ON (jbga_id =c.bga_id  AND c.bga_param_nr =a.bga_type_param_nr AND (a.bga_type_param_regular=1))
WHERE a.bga_type_id = jbga_type_id
ORDER BY 1) LOOP
IF p.bga_type_param_regular = 0 AND NOT p.bga_param_value IS NULL THEN
jfound_irregular:=1;
END IF;
IF Upper(p.bga_type_param_key) LIKE 'D%' THEN
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,1,0),'cast(@u'||p.nr||' as DATETIME)');
ELSE
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,1,0),'@u'||p.nr);
END IF;
IF NOT p.bga_param_value IS NULL THEN
IF p.bga_param_value=p.bga_param_default_value THEN
NULL;
else
jsql := REPLACE(jsql,'-- \\'||lpad(p.bga_param_nr,1,0)||'/',\N);
END IF;
ELSE
jsql := REPLACE(jsql,'-- /'||lpad(p.bga_param_nr,1,0)||'\\',\N);
END IF;
END LOOP;
-- INSERT INTO gttp (c1) VALUES (To_Char(jbga_id));
execute immediate 'SET @u'||(prmcnt+1)||'='||jbga_id;
jsql := REPLACE(jsql,':0','@u'||(prmcnt+1));
-- jsql := REPLACE(jsql,'''','''''');
-- action
EXECUTE immediate jsql;
-- Dbms_Output.put_line(jsql);
ELSE
-- grupes atnaujinimas
jsql:=pBGA_TYPE_SELECT;
-- :01 parametrai
FOR p IN (SELECT
a.bga_type_param_nr bga_param_nr
,coalesce(b.bga_param_value,c.bga_param_value,nullif(Decode(a.bga_type_param_regular,1,a.bga_param_default_value),Decode(a.bga_type_param_key,'N:=:FORCED','0'))) bga_param_value
,a.bga_type_param_key
,a.bga_type_param_regular
,a.bga_param_default_value
,Rank() over (ORDER BY a.bga_type_param_nr) nr
 FROM
bga_type_param a
left join bgao_param b ON (jbgao_id=b.bgao_id AND b.bga_param_nr =a.bga_type_param_nr)
left join bga_param  c ON (jbga_id =c.bga_id  AND c.bga_param_nr =a.bga_type_param_nr AND (a.bga_type_param_regular=1))
WHERE a.bga_type_id = jbga_type_id
ORDER BY 1) LOOP
prmcnt:=prmcnt+1;
-- INSERT INTO gttp (c1) VALUES (p.bga_param_value);
execute immediate 'SET @u'||prmcnt||'='||case when p.bga_param_value IS NULL then 'null' ELSE '\''||p.bga_param_value||'\'' END;
IF Upper(p.bga_type_param_key) LIKE 'D%' THEN
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,2,0),'cast(@u'||p.nr||' as DATETIME)');
ELSE
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,2,0),'@u'||p.nr);
END IF;
IF NOT p.bga_param_value IS NULL THEN
IF p.bga_param_value=p.bga_param_default_value THEN
NULL;
else
jsql := REPLACE(jsql,'-- \\'||lpad(p.bga_param_nr,2,0)||'/',\N);
END IF;
ELSE
jsql := REPLACE(jsql,'-- /'||lpad(p.bga_param_nr,2,0)||'\\',\N);
END IF;
END LOOP;
-- :1 parametrai
FOR p IN (SELECT
a.bga_type_param_nr bga_param_nr
,coalesce(b.bga_param_value,c.bga_param_value,nullif(Decode(a.bga_type_param_regular,1,a.bga_param_default_value),Decode(a.bga_type_param_key,'N:=:FORCED','0'))) bga_param_value
,a.bga_type_param_key
,a.bga_type_param_regular
,a.bga_param_default_value
,Rank() over (ORDER BY a.bga_type_param_nr) nr
 FROM
bga_type_param a
left join bgao_param b ON (jbgao_id=b.bgao_id AND b.bga_param_nr =a.bga_type_param_nr)
left join bga_param  c ON (jbga_id =c.bga_id  AND c.bga_param_nr =a.bga_type_param_nr AND (a.bga_type_param_regular=1))
WHERE a.bga_type_id = jbga_type_id
ORDER BY 1) LOOP
IF Upper(p.bga_type_param_key) LIKE 'D%' THEN
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,1,0),'cast(@u'||p.nr||' as DATETIME)');
ELSE
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,1,0),'@u'||p.nr);
END IF;
IF NOT p.bga_param_value IS NULL THEN
IF p.bga_param_value=p.bga_param_default_value THEN
NULL;
else
jsql := REPLACE(jsql,'-- \\'||lpad(p.bga_param_nr,1,0)||'/',\N);
END IF;
ELSE
jsql := REPLACE(jsql,'-- /'||lpad(p.bga_param_nr,1,0)||'\\',\N);
END IF;

END LOOP;
-- INSERT INTO gttp (c1) VALUES (To_Char(jbga_id));
execute immediate 'SET @u'||(prmcnt+1)||'='||jbga_id;
jsql := REPLACE(jsql,':0','@u'||(prmcnt+1));
-- jsql := REPLACE(jsql,'''','''''');
-- wapp:= CASE WHEN jsql LIKE '%@%' THEN 0 ELSE 1 END;
CREATE OR REPLACE TEMPORARY TABLE gttu (
  id1 INT(15) signed NOT NULL,
  d1  DATETIME,
  d2  DATETIME,
  id2 int(10) unsigned,
  c1  VARCHAR(120)
);
-- DELETE gttu;
oth:=0;
begin
EXECUTE IMMEDIATE 'begin insert into gttu (id1) select * from ('||jsql||'); end';
EXCEPTION
when 1136 then 
oth:=1;
WHEN OTHERS THEN
RAISE;
END;
IF oth=0 THEN
COMMIT;
EXECUTE IMMEDIATE 'begin delete from grp WHERE (gr_id,point_id) in (select :1,point_id from (select point_id from grp where gr_id=:1 minus (select id1 from gttu where id1>0))); end' USING pGR_ID,pGR_ID;
EXECUTE IMMEDIATE 'begin INSERT INTO grp (gr_id,point_id) SELECT :1,o.* FROM (select id1 from gttu where id1>0) o MINUS (SELECT gr_id,point_id FROM grp WHERE gr_id=:1); end' USING pGR_ID,pGR_ID;
EXECUTE IMMEDIATE 'begin delete from grc WHERE (cont_id,comp_id) in (select :1,comp_id from (select comp_id from grc where cont_id=:1 minus (select -id1 from gttu where id1<0))); end' USING pGR_ID,pGR_ID;
EXECUTE IMMEDIATE 'begin INSERT INTO grc (cont_id,comp_id) SELECT :1,o.* FROM (select -id1 from gttu where id1<0) o MINUS (SELECT cont_id,comp_id FROM grc WHERE cont_id=:1); end' USING pGR_ID,pGR_ID;
ELSE
-- other syntax
EXECUTE IMMEDIATE 'begin insert into gttu (id1,d1,d2,id2,c1) select * from ('||jsql||'); end';
--
COMMIT;
EXECUTE IMMEDIATE 'begin delete from grp WHERE (gr_id,point_id,grp_bt) in (select :1,point_id,grp_bt from (select point_id,grp_bt,grp_et,grp_nr,grp_desc from grp where gr_id=:1 minus (select id1,nvl(d1,date''1899-12-30''),d2,id2,c1 from gttu where id1>0))); end' USING pGR_ID,pGR_ID;
EXECUTE IMMEDIATE 'begin INSERT INTO grp (gr_id,point_id,grp_bt,grp_et,grp_nr,grp_desc) SELECT :1,o.* FROM (select id1,nvl(d1,date''1899-12-30''),d2,id2,c1 from gttu where id1>0) o MINUS (SELECT gr_id,point_id,grp_bt,grp_et,grp_nr,grp_desc FROM grp WHERE gr_id=:1); end' USING pGR_ID,pGR_ID;
EXECUTE IMMEDIATE 'begin delete from grc WHERE (cont_id,comp_id,grc_bt) in (select :1,comp_id,grc_bt from (select comp_id,grc_bt,grc_et,grc_nr,grc_desc from grc where cont_id=:1 minus (select -id1,nvl(d1,date''1899-12-30''),d2,id2,c1 from gttu where id1<0))); end' USING pGR_ID,pGR_ID;
EXECUTE IMMEDIATE 'begin INSERT INTO grc (cont_id,comp_id,grc_bt,grc_et,grc_nr,grc_desc) SELECT :1,o.* FROM (select -id1,nvl(d1,date''1899-12-30''),d2,id2,c1 from gttu where id1<0) o MINUS (SELECT cont_id,comp_id,grc_bt,grc_et,grc_nr,grc_desc FROM grc WHERE cont_id=:1); end' USING pGR_ID,pGR_ID;
END IF;
END IF;
-- S.J.2020.06.14 #33146 do_stuff procedūra autonominėje tranzakcijoje įvykdo užduotį,
-- kurioje buvo tarkim insert into gttt (c1,id1) values ('BGA_VAL',to_char(sysdate,'HH24'));
-- po užduoties įvykdymo toje pačioje autonominėje sesijoje ištraukia iš gttt ką reikia ir gražiną kaip outputą
if pBgaStaSpty = '1' then
  select
    -- (select id1 from gttt where c1='BGA_VAL' and rownum=1)
   (select id1 from gttv where rownum=1)
  into pValue
  from dual;
end if;

COMMIT;
aff:=1;
exception
when others THEN
aff:=-1;
ROLLBACK;
event_i(
1,3,0,1,sysdate
,trim(SubStr(jbga_name||': '||geterrm(SQLERRM),1,250))
,\N
,\N
,\N
,\N
,\N
,\N
,\N
,\N
,\N
,\N
,\N
,\N
,\N
);

jsql0:=SubStr(jsql,1,4000);
UPDATE bga_debug SET sql_block =jsql0;
COMMIT;
END;
-- END STUFF
jbga_running:=0;
-- log_state(pAnr, pDb_time);
 update bga_state Y
,     (
     SELECT jBGA_ID bga_id, jBGA_RUNNING bga_running, jBGAO_ID bgao_id,jFOUND_IRREGULAR fi, aff suc 
     ) u
SET
   y.bga_running=u.bga_running
   ,y.bga_last_exec_start=CASE WHEN y.bga_running>u.bga_running AND u.fi=0 THEN y.bga_curr_exec_start ELSE y.bga_last_exec_start END
   ,y.bga_last_exec_end=CASE WHEN y.bga_running>u.bga_running AND u.fi=0 THEN sysdate ELSE y.bga_last_exec_end END
   ,y.bga_last_suc_exec_end=CASE WHEN y.bga_running>u.bga_running AND u.fi=0 AND u.suc=1 THEN sysdate ELSE y.bga_last_exec_end END
   ,y.bga_curr_exec_start=CASE WHEN y.bga_running>u.bga_running THEN NULL WHEN y.bga_running<u.bga_running THEN sysdate ELSE y.bga_curr_exec_start END
   ,y.bgao_id=CASE WHEN y.bga_running>u.bga_running THEN NULL WHEN y.bga_running<u.bga_running THEN u.bgao_id ELSE y.bgao_id END
WHERE y.bga_id = u.bga_id;

if SQL%rowcount=0 then
INSERT INTO bga_state
   (
   bga_id, bga_running, bga_last_exec_start, bga_last_exec_end, bga_curr_exec_start, bgao_id
  ) SELECT 
   jBGA_ID, jBGA_RUNNING, sysdate, sysdate, Decode(jBGA_RUNNING,1,sysdate), jBGAO_ID
   FROM DUAL WHERE NOT EXISTS (SELECT 0 FROM bga_state WHERE bga_id=jBGA_ID);
END if;
  -- S.J.2020.06.14 #33146 bga_sta realizacija
    if pBgaStaSpty = '1' then
      if pAnr is null then
        select BGA_STA_ANR_SEQ.nextval,ses_id, user_id/*, sys_context('USERENV','BG_JOB_ID')*/  into pAnr, bSes_id, bUser_id/*, bJob_id*/ from st_curr_user;
        



        INSERT INTO bga_sta (db_time, anr, bga_id, user_id, sec_cnt, ses_id, job_id, bgao_id, status)
        VALUES (pDb_time, panr, jBga_id, buser_id, 0, bSes_id, bJob_id, jBgao_id, 1);
      else
        UPDATE bga_sta SET sec_cnt=timestampdiff(second,pDb_time,SYSDATE),status=Decode(aff,-1,-1),obj_cnt=pValue WHERE db_time = pdb_time and anr=panr;
      end if;
    end if;
-- end log_state 
UPDATE bgao SET bgao_status = Decode(aff,-1,3) WHERE bgao_id=r.bgao_id;
EXIT;
END LOOP;
IF aff=0 THEN
EXIT;
END IF;
COMMIT;
i:=i+1;
END LOOP;
ROLLBACK;
-- end be eiles
-- reset dead bga
UPDATE bga_state SET bga_running = 0, bga_curr_exec_start = NULL, bgao_id = null where bga_running=1 AND bga_curr_exec_start<SYSDATE - INTERVAL 3 HOUR;
-- S.J.2020.06.24 #33146 uzdarom senų pradėtų ir nebaigtų bga įrašų statistika
UPDATE bga_sta SET status = -1, sec_cnt = timestampdiff(second,pDb_time,SYSDATE) where status = 1 AND db_time < SYSDATE - INTERVAL 3 HOUR;
COMMIT;
-- iprastiniai
ts:=SYSDATE;
i:=0;
WHILE i<3000 AND SYSDATE<ts + INTERVAL 5 minute LOOP
aff:=0;
FOR r IN cul LOOP
--  SELECT Decode(bga_last_exec_start,r.bga_last_exec_start,0,1)+Decode(bga_curr_exec_start,r.bga_curr_exec_start,0,1) INTO aff
--   FROM dual left join bga_state ON (bga_id=r.bga_id);
 SELECT Decode(bga_last_exec_start,r.bga_last_exec_start,0,1)+Decode(bga_curr_exec_start,r.bga_curr_exec_start,0,1)+nvl(s.bga_running,0), 
-- a.bgao_id,
-- a.bgao_status,
b.bga_id,
b.bga_type_id,
b.bga_name,
b.gr_id,
-- b.bga_enabled,
-- b.sch_id,
-- t.bga_type_code,
-- t.bga_type_name,
-- t.bga_type_desc,
t.bga_type_declare,
t.bga_type_pre_action,
t.bga_type_select,
t.bga_type_rec_action,
t.bga_type_post_action
 INTO aff
-- ,jbgao_id
,jbga_id
,jbga_type_id
,jbga_name
,pGR_ID
,pBGA_TYPE_DECLARE
,pBGA_TYPE_PRE_ACTION
,pBGA_TYPE_SELECT
,pBGA_TYPE_REC_ACTION
,pBGA_TYPE_POST_ACTION
  FROM bga b 
  JOIN bga_type t ON (b.bga_type_id=t.bga_type_id)
  LEFT JOIN bga_state s ON (b.bga_id=s.bga_id)
   WHERE b.bga_id=r.bga_id;
 IF aff>0 THEN
  EXIT;
 END IF;
-- jbga_id:=r.bga_id;
-- jbga_name:=r.bga_name;
-- jbga_type_id:=r.bga_type_id;
jbgao_id:=cast(\N as int);
jbga_running:=1;
jfound_irregular:=0;
-- nes antra karta gali uzeiti
pAnr:=null;
pValue:=null;
pDb_time:=SYSDATE;
-- log_state(pAnr, pDb_time);
 update bga_state Y
,     (
     SELECT jBGA_ID bga_id, jBGA_RUNNING bga_running, jBGAO_ID bgao_id,jFOUND_IRREGULAR fi, aff suc 
     ) u
SET
   y.bga_running=u.bga_running
   ,y.bga_last_exec_start=CASE WHEN y.bga_running>u.bga_running AND u.fi=0 THEN y.bga_curr_exec_start ELSE y.bga_last_exec_start END
   ,y.bga_last_exec_end=CASE WHEN y.bga_running>u.bga_running AND u.fi=0 THEN sysdate ELSE y.bga_last_exec_end END
   ,y.bga_last_suc_exec_end=CASE WHEN y.bga_running>u.bga_running AND u.fi=0 AND u.suc=1 THEN sysdate ELSE y.bga_last_exec_end END
   ,y.bga_curr_exec_start=CASE WHEN y.bga_running>u.bga_running THEN NULL WHEN y.bga_running<u.bga_running THEN sysdate ELSE y.bga_curr_exec_start END
   ,y.bgao_id=CASE WHEN y.bga_running>u.bga_running THEN NULL WHEN y.bga_running<u.bga_running THEN u.bgao_id ELSE y.bgao_id END
WHERE y.bga_id = u.bga_id;

if SQL%rowcount=0 then
INSERT INTO bga_state
   (
   bga_id, bga_running, bga_last_exec_start, bga_last_exec_end, bga_curr_exec_start, bgao_id
  ) SELECT 
   jBGA_ID, jBGA_RUNNING, sysdate, sysdate, Decode(jBGA_RUNNING,1,sysdate), jBGAO_ID
   FROM DUAL WHERE NOT EXISTS (SELECT 0 FROM bga_state WHERE bga_id=jBGA_ID);
END if;
  -- S.J.2020.06.14 #33146 bga_sta realizacija
    if pBgaStaSpty = '1' then
      if pAnr is null then
        select BGA_STA_ANR_SEQ.nextval,ses_id, user_id/*, sys_context('USERENV','BG_JOB_ID')*/  into pAnr, bSes_id, bUser_id/*, bJob_id*/ from st_curr_user;
        



        INSERT INTO bga_sta (db_time, anr, bga_id, user_id, sec_cnt, ses_id, job_id, bgao_id, status)
        VALUES (pDb_time, panr, jBga_id, buser_id, 0, bSes_id, bJob_id, jBgao_id, 1);
      else
        UPDATE bga_sta SET sec_cnt=timestampdiff(second,pDb_time,SYSDATE),status=Decode(aff,-1,-1),obj_cnt=pValue WHERE db_time = pdb_time and anr=panr;
      end if;
    end if;
-- end log_state
COMMIT;
-- STUFF
-- do_stuff(r.gr_id,r.bga_type_declare,r.bga_type_pre_action,r.bga_type_select,r.bga_type_rec_action,r.bga_type_post_action, pValue);
BEGIN
-- parameters
-- DELETE gttp;
PRMCNT:=0;
CREATE OR REPLACE TEMPORARY TABLE gttv (
  id1 INT(15) signed
  );
  jsql:='';
IF pGR_ID IS NULL THEN
IF NOT pBGA_TYPE_DECLARE IS NULL THEN
jsql := 'DECLARE'||Chr(10)||pBGA_TYPE_DECLARE||Chr(10);
END IF;
jsql := jsql||'BEGIN'||Chr(10);
IF NOT pBGA_TYPE_PRE_ACTION IS NULL THEN
jsql := jsql||pBGA_TYPE_PRE_ACTION||Chr(10);
END IF;
IF NOT pBGA_TYPE_SELECT IS NULL AND NOT pBGA_TYPE_REC_ACTION IS NULL THEN
jsql := jsql||'FOR c in ('||pBGA_TYPE_SELECT||') LOOP'||Chr(10);
jsql := jsql||pBGA_TYPE_REC_ACTION||Chr(10);
jsql := jsql||'END LOOP;'||Chr(10);
END IF;
IF NOT pBGA_TYPE_POST_ACTION IS NULL THEN
jsql := jsql||pBGA_TYPE_POST_ACTION||Chr(10);
END IF;
-- jsql := jsql||'COMMIT; END;';
jsql := jsql||'END'; -- nereikia, nes yra COMMIT veliau vis tiek, zr. zemiau
-- parametru ikisimas
-- :01 parametrai
-- FOR p IN (SELECT * FROM bga_param WHERE bga_id = j.bga_id ORDER BY bga_param_nr) LOOP
FOR p IN (SELECT
a.bga_type_param_nr bga_param_nr
,coalesce(b.bga_param_value,c.bga_param_value,nullif(Decode(a.bga_type_param_regular,1,a.bga_param_default_value),Decode(a.bga_type_param_key,'N:=:FORCED','0'))) bga_param_value
,a.bga_type_param_key
,a.bga_type_param_regular
,a.bga_param_default_value
,Rank() over (ORDER BY a.bga_type_param_nr) nr
 FROM
bga_type_param a
left join bgao_param b ON (jbgao_id=b.bgao_id AND b.bga_param_nr =a.bga_type_param_nr)
left join bga_param  c ON (jbga_id =c.bga_id  AND c.bga_param_nr =a.bga_type_param_nr AND (a.bga_type_param_regular=1))
WHERE a.bga_type_id = jbga_type_id
ORDER BY 1) LOOP
prmcnt:=prmcnt+1;
IF p.bga_type_param_regular = 0 AND NOT p.bga_param_value IS NULL THEN
jfound_irregular:=1;
END IF;
-- INSERT INTO gttp (c1) VALUES (p.bga_param_value);
execute immediate 'SET @u'||prmcnt||'='||case when p.bga_param_value IS NULL then 'null' ELSE '\''||p.bga_param_value||'\'' END;
-- iki cia
IF Upper(p.bga_type_param_key) LIKE 'D%' THEN
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,2,0),'cast(@u'||p.nr||' as DATETIME)');
ELSE
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,2,0),'@u'||p.nr);
END IF;
IF NOT p.bga_param_value IS NULL THEN
IF p.bga_param_value =p.bga_param_default_value THEN
NULL;
else
jsql := REPLACE(jsql,'-- \\'||lpad(p.bga_param_nr,2,0)||'/',\N);
END IF;
ELSE
jsql := REPLACE(jsql,'-- /'||lpad(p.bga_param_nr,2,0)||'\\',\N);
END IF;
END LOOP;
-- :1 parametrai
FOR p IN (SELECT
a.bga_type_param_nr bga_param_nr
,coalesce(b.bga_param_value,c.bga_param_value,nullif(Decode(a.bga_type_param_regular,1,a.bga_param_default_value),Decode(a.bga_type_param_key,'N:=:FORCED','0'))) bga_param_value
,a.bga_type_param_key
,a.bga_type_param_regular
,a.bga_param_default_value
,Rank() over (ORDER BY a.bga_type_param_nr) nr
 FROM
bga_type_param a
left join bgao_param b ON (jbgao_id=b.bgao_id AND b.bga_param_nr =a.bga_type_param_nr)
left join bga_param  c ON (jbga_id =c.bga_id  AND c.bga_param_nr =a.bga_type_param_nr AND (a.bga_type_param_regular=1))
WHERE a.bga_type_id = jbga_type_id
ORDER BY 1) LOOP
IF p.bga_type_param_regular = 0 AND NOT p.bga_param_value IS NULL THEN
jfound_irregular:=1;
END IF;
IF Upper(p.bga_type_param_key) LIKE 'D%' THEN
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,1,0),'cast(@u'||p.nr||' as DATETIME)');
ELSE
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,1,0),'@u'||p.nr);
END IF;
IF NOT p.bga_param_value IS NULL THEN
IF p.bga_param_value=p.bga_param_default_value THEN
NULL;
else
jsql := REPLACE(jsql,'-- \\'||lpad(p.bga_param_nr,1,0)||'/',\N);
END IF;
ELSE
jsql := REPLACE(jsql,'-- /'||lpad(p.bga_param_nr,1,0)||'\\',\N);
END IF;
END LOOP;
-- INSERT INTO gttp (c1) VALUES (To_Char(jbga_id));
execute immediate 'SET @u'||(prmcnt+1)||'='||jbga_id;
jsql := REPLACE(jsql,':0','@u'||(prmcnt+1));
-- jsql := REPLACE(jsql,'''','''''');
-- action
EXECUTE immediate jsql;
-- Dbms_Output.put_line(jsql);
ELSE
-- grupes atnaujinimas
jsql:=pBGA_TYPE_SELECT;
-- :01 parametrai
FOR p IN (SELECT
a.bga_type_param_nr bga_param_nr
,coalesce(b.bga_param_value,c.bga_param_value,nullif(Decode(a.bga_type_param_regular,1,a.bga_param_default_value),Decode(a.bga_type_param_key,'N:=:FORCED','0'))) bga_param_value
,a.bga_type_param_key
,a.bga_type_param_regular
,a.bga_param_default_value
,Rank() over (ORDER BY a.bga_type_param_nr) nr
 FROM
bga_type_param a
left join bgao_param b ON (jbgao_id=b.bgao_id AND b.bga_param_nr =a.bga_type_param_nr)
left join bga_param  c ON (jbga_id =c.bga_id  AND c.bga_param_nr =a.bga_type_param_nr AND (a.bga_type_param_regular=1))
WHERE a.bga_type_id = jbga_type_id
ORDER BY 1) LOOP
prmcnt:=prmcnt+1;
-- INSERT INTO gttp (c1) VALUES (p.bga_param_value);
execute immediate 'SET @u'||prmcnt||'='||case when p.bga_param_value IS NULL then 'null' ELSE '\''||p.bga_param_value||'\'' END;
IF Upper(p.bga_type_param_key) LIKE 'D%' THEN
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,2,0),'cast(@u'||p.nr||' as DATETIME)');
ELSE
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,2,0),'@u'||p.nr);
END IF;
IF NOT p.bga_param_value IS NULL THEN
IF p.bga_param_value=p.bga_param_default_value THEN
NULL;
else
jsql := REPLACE(jsql,'-- \\'||lpad(p.bga_param_nr,2,0)||'/',\N);
END IF;
ELSE
jsql := REPLACE(jsql,'-- /'||lpad(p.bga_param_nr,2,0)||'\\',\N);
END IF;
END LOOP;
-- :1 parametrai
FOR p IN (SELECT
a.bga_type_param_nr bga_param_nr
,coalesce(b.bga_param_value,c.bga_param_value,nullif(Decode(a.bga_type_param_regular,1,a.bga_param_default_value),Decode(a.bga_type_param_key,'N:=:FORCED','0'))) bga_param_value
,a.bga_type_param_key
,a.bga_type_param_regular
,a.bga_param_default_value
,Rank() over (ORDER BY a.bga_type_param_nr) nr
 FROM
bga_type_param a
left join bgao_param b ON (jbgao_id=b.bgao_id AND b.bga_param_nr =a.bga_type_param_nr)
left join bga_param  c ON (jbga_id =c.bga_id  AND c.bga_param_nr =a.bga_type_param_nr AND (a.bga_type_param_regular=1))
WHERE a.bga_type_id = jbga_type_id
ORDER BY 1) LOOP
IF Upper(p.bga_type_param_key) LIKE 'D%' THEN
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,1,0),'cast(@u'||p.nr||' as DATETIME)');
ELSE
jsql := REPLACE(jsql,':'||lpad(p.bga_param_nr,1,0),'@u'||p.nr);
END IF;
IF NOT p.bga_param_value IS NULL THEN
IF p.bga_param_value=p.bga_param_default_value THEN
NULL;
else
jsql := REPLACE(jsql,'-- \\'||lpad(p.bga_param_nr,1,0)||'/',\N);
END IF;
ELSE
jsql := REPLACE(jsql,'-- /'||lpad(p.bga_param_nr,1,0)||'\\',\N);
END IF;

END LOOP;
-- INSERT INTO gttp (c1) VALUES (To_Char(jbga_id));
execute immediate 'SET @u'||(prmcnt+1)||'='||jbga_id;
jsql := REPLACE(jsql,':0','@u'||(prmcnt+1));
-- jsql := REPLACE(jsql,'''','''''');
-- wapp:= CASE WHEN jsql LIKE '%@%' THEN 0 ELSE 1 END;
CREATE OR REPLACE TEMPORARY TABLE gttu (
  id1 INT(15) signed NOT NULL,
  d1  DATETIME,
  d2  DATETIME,
  id2 int(10) unsigned,
  c1  VARCHAR(120)
);
-- DELETE gttu;
oth:=0;
begin
EXECUTE IMMEDIATE 'begin insert into gttu (id1) select * from ('||jsql||'); end';
EXCEPTION
when 1136 then 
oth:=1;
WHEN OTHERS THEN
RAISE;
END;
IF oth=0 THEN
COMMIT;
EXECUTE IMMEDIATE 'begin delete from grp WHERE (gr_id,point_id) in (select :1,point_id from (select point_id from grp where gr_id=:1 minus (select id1 from gttu where id1>0))); end' USING pGR_ID,pGR_ID;
EXECUTE IMMEDIATE 'begin INSERT INTO grp (gr_id,point_id) SELECT :1,o.* FROM (select id1 from gttu where id1>0) o MINUS (SELECT gr_id,point_id FROM grp WHERE gr_id=:1); end' USING pGR_ID,pGR_ID;
EXECUTE IMMEDIATE 'begin delete from grc WHERE (cont_id,comp_id) in (select :1,comp_id from (select comp_id from grc where cont_id=:1 minus (select -id1 from gttu where id1<0))); end' USING pGR_ID,pGR_ID;
EXECUTE IMMEDIATE 'begin INSERT INTO grc (cont_id,comp_id) SELECT :1,o.* FROM (select -id1 from gttu where id1<0) o MINUS (SELECT cont_id,comp_id FROM grc WHERE cont_id=:1); end' USING pGR_ID,pGR_ID;
ELSE
-- other syntax
EXECUTE IMMEDIATE 'begin insert into gttu (id1,d1,d2,id2,c1) select * from ('||jsql||'); end';
--
COMMIT;
EXECUTE IMMEDIATE 'begin delete from grp WHERE (gr_id,point_id,grp_bt) in (select :1,point_id,grp_bt from (select point_id,grp_bt,grp_et,grp_nr,grp_desc from grp where gr_id=:1 minus (select id1,nvl(d1,date''1899-12-30''),d2,id2,c1 from gttu where id1>0))); end' USING pGR_ID,pGR_ID;
EXECUTE IMMEDIATE 'begin INSERT INTO grp (gr_id,point_id,grp_bt,grp_et,grp_nr,grp_desc) SELECT :1,o.* FROM (select id1,nvl(d1,date''1899-12-30''),d2,id2,c1 from gttu where id1>0) o MINUS (SELECT gr_id,point_id,grp_bt,grp_et,grp_nr,grp_desc FROM grp WHERE gr_id=:1); end' USING pGR_ID,pGR_ID;
EXECUTE IMMEDIATE 'begin delete from grc WHERE (cont_id,comp_id,grc_bt) in (select :1,comp_id,grc_bt from (select comp_id,grc_bt,grc_et,grc_nr,grc_desc from grc where cont_id=:1 minus (select -id1,nvl(d1,date''1899-12-30''),d2,id2,c1 from gttu where id1<0))); end' USING pGR_ID,pGR_ID;
EXECUTE IMMEDIATE 'begin INSERT INTO grc (cont_id,comp_id,grc_bt,grc_et,grc_nr,grc_desc) SELECT :1,o.* FROM (select -id1,nvl(d1,date''1899-12-30''),d2,id2,c1 from gttu where id1<0) o MINUS (SELECT cont_id,comp_id,grc_bt,grc_et,grc_nr,grc_desc FROM grc WHERE cont_id=:1); end' USING pGR_ID,pGR_ID;
END IF;
END IF;
-- S.J.2020.06.14 #33146 do_stuff procedūra autonominėje tranzakcijoje įvykdo užduotį,
-- kurioje buvo tarkim insert into gttt (c1,id1) values ('BGA_VAL',to_char(sysdate,'HH24'));
-- po užduoties įvykdymo toje pačioje autonominėje sesijoje ištraukia iš gttt ką reikia ir gražiną kaip outputą
if pBgaStaSpty = '1' then
  select
    -- (select id1 from gttt where c1='BGA_VAL' and rownum=1)
   (select id1 from gttv where rownum=1)
  into pValue
  from dual;
end if;

COMMIT;
aff:=1;
exception
when others THEN
aff:=-1;
ROLLBACK;
event_i(
1,3,0,1,sysdate
,trim(SubStr(jbga_name||': '||geterrm(SQLERRM),1,250))
,\N
,\N
,\N
,\N
,\N
,\N
,\N
,\N
,\N
,\N
,\N
,\N
,\N
);

jsql0:=SubStr(jsql,1,4000);
UPDATE bga_debug SET sql_block =jsql0;
COMMIT;
END;
-- END STUFF
jbga_running:=0;
-- log_state(pAnr, pDb_time, pValue);
-- log_state(pAnr, pDb_time);
 update bga_state Y
,     (
     SELECT jBGA_ID bga_id, jBGA_RUNNING bga_running, jBGAO_ID bgao_id,jFOUND_IRREGULAR fi, aff suc 
     ) u
SET
   y.bga_running=u.bga_running
   ,y.bga_last_exec_start=CASE WHEN y.bga_running>u.bga_running AND u.fi=0 THEN y.bga_curr_exec_start ELSE y.bga_last_exec_start END
   ,y.bga_last_exec_end=CASE WHEN y.bga_running>u.bga_running AND u.fi=0 THEN sysdate ELSE y.bga_last_exec_end END
   ,y.bga_last_suc_exec_end=CASE WHEN y.bga_running>u.bga_running AND u.fi=0 AND u.suc=1 THEN sysdate ELSE y.bga_last_exec_end END
   ,y.bga_curr_exec_start=CASE WHEN y.bga_running>u.bga_running THEN NULL WHEN y.bga_running<u.bga_running THEN sysdate ELSE y.bga_curr_exec_start END
   ,y.bgao_id=CASE WHEN y.bga_running>u.bga_running THEN NULL WHEN y.bga_running<u.bga_running THEN u.bgao_id ELSE y.bgao_id END
WHERE y.bga_id = u.bga_id;

if SQL%rowcount=0 then
INSERT INTO bga_state
   (
   bga_id, bga_running, bga_last_exec_start, bga_last_exec_end, bga_curr_exec_start, bgao_id
  ) SELECT 
   jBGA_ID, jBGA_RUNNING, sysdate, sysdate, Decode(jBGA_RUNNING,1,sysdate), jBGAO_ID
   FROM DUAL WHERE NOT EXISTS (SELECT 0 FROM bga_state WHERE bga_id=jBGA_ID);
END if;
  -- S.J.2020.06.14 #33146 bga_sta realizacija
    if pBgaStaSpty = '1' then
      if pAnr is null then
        select BGA_STA_ANR_SEQ.nextval,ses_id, user_id/*, sys_context('USERENV','BG_JOB_ID')*/  into pAnr, bSes_id, bUser_id/*, bJob_id*/ from st_curr_user;
        



        INSERT INTO bga_sta (db_time, anr, bga_id, user_id, sec_cnt, ses_id, job_id, bgao_id, status)
        VALUES (pDb_time, panr, jBga_id, buser_id, 0, bSes_id, bJob_id, jBgao_id, 1);
      else
        UPDATE bga_sta SET sec_cnt=timestampdiff(second,pDb_time,SYSDATE),status=Decode(aff,-1,-1),obj_cnt=pValue WHERE db_time = pdb_time and anr=panr;
      end if;
    end if;
-- end log_state
COMMIT;
EXIT;
END LOOP;
IF aff=0 THEN
EXIT;
END IF;
COMMIT;
i:=i+1;
END LOOP;
ROLLBACK;
END if;
END//
DELIMITER ;
