I believe GE's working on this for a patch...here's the scripts I created for use on Oracle, table by table. If you set it as a stored procedure or scheduled task it could update automatically. Normally we only have a few Charts a day. Users are encouraged to sign clinical lists within the update.
As-is; change dates as are appropriate:
--run this just to get an idea of whether there are documents or not (this is optional).
select *
from ml.obs
where change = 6
and sdid in (select sdid from ml.document where db_create_date >= '18-JAN-16'
and status = 'S');
---------STEP 1
create global temporary table jimedoccontb
on commit preserve rows
as
select distinct CONTBID,sdid,USRID
FROM ML.DOCCONTB
where contb_action = 7 and db_create_date >= '18-JAN-16'
AND SDID IN (SELECT SDID FROM ML.DOCUMENT WHERE DB_CREATE_DATE >= '18-JAN-16' AND STATUS = 'S');
---------STEP 2
update ml.obs
set change = 2,
pubuser = (SELECT USRID FROM jimedoccontb WHERE obs.sdid=jimedoccontb.sdid),
pubtime = (SELECT contbid FROM jimedoccontb WHERE obs.sdid=jimedoccontb.sdid)
where change = 6
and sdid = (SELECT SDID FROM jimedoccontb where obs.sdid=jimedoccontb.sdid)
and db_create_date >= '18-JAN-16';
---------STEP 3
update ml.medicate
set change = 2,
pubuser = (SELECT USRID FROM jimedoccontb WHERE medicate.sdid=jimedoccontb.sdid),
pubtime = (SELECT contbid FROM jimedoccontb WHERE medicate.sdid=jimedoccontb.sdid)
where change = 6
and sdid = (SELECT SDID FROM jimedoccontb where medicate.sdid=jimedoccontb.sdid);
---------STEP 4
update ml.prescrib
set change = 2,
pubuser = (SELECT USRID FROM jimedoccontb WHERE prescrib.sdid=jimedoccontb.sdid),
pubtime = (SELECT contbid FROM jimedoccontb WHERE prescrib.sdid=jimedoccontb.sdid)
where change = 6
and sdid = (SELECT SDID FROM jimedoccontb where prescrib.sdid=jimedoccontb.sdid);
---------STEP 5
update ml.allergy
set change = 2,
pubuser = (SELECT USRID FROM jimedoccontb WHERE allergy.sdid=jimedoccontb.sdid),
pubtime = (SELECT contbid FROM jimedoccontb WHERE allergy.sdid=jimedoccontb.sdid)
where change = 6
and sdid = (SELECT SDID FROM jimedoccontb where allergy.sdid=jimedoccontb.sdid);
---------STEP 6
update ml.directiv
set change = 2,
pubuser = (SELECT USRID FROM jimedoccontb WHERE directiv.sdid=jimedoccontb.sdid),
pubtime = (SELECT contbid FROM jimedoccontb WHERE directiv.sdid=jimedoccontb.sdid)
where change = 6
and sdid = (SELECT SDID FROM jimedoccontb where directiv.sdid=jimedoccontb.sdid);
---------STEP 7
update ml.orders
set change = 2,
pubuser = (SELECT USRID FROM jimedoccontb WHERE orders.sdid=jimedoccontb.sdid),
pubtime = (SELECT contbid FROM jimedoccontb WHERE orders.sdid=jimedoccontb.sdid)
where change = 6
and sdid = (SELECT SDID FROM jimedoccontb where orders.sdid=jimedoccontb.sdid);
---------STEP 8
--services
update ml.orders
set change = 0,
pubuser = (SELECT USRID FROM jimedoccontb WHERE orders.sdid=jimedoccontb.sdid),
pubtime = (SELECT contbid FROM jimedoccontb WHERE orders.sdid=jimedoccontb.sdid),
status = 'C',
newrowonchange = 'Y'
where change = 2
and sdid = (SELECT SDID FROM jimedoccontb where orders.sdid=jimedoccontb.sdid)
and ordertype = 'S'
and status = 'U';
---------STEP 9
--referrals
update ml.orders
set --change = 0,
pubuser = (SELECT USRID FROM jimedoccontb WHERE orders.sdid=jimedoccontb.sdid),
pubtime = (SELECT contbid FROM jimedoccontb WHERE orders.sdid=jimedoccontb.sdid),
status = 'S',
newrowonchange = 'Y'
where change = 2
and sdid = (SELECT SDID FROM jimedoccontb where orders.sdid=jimedoccontb.sdid)
and ordertype = 'R'
and status = 'U';
---------STEP 10
update ml.assess
set change = 2,
pubuser = (SELECT USRID FROM jimedoccontb WHERE assess.sdid=jimedoccontb.sdid),
pubtime = (SELECT contbid FROM jimedoccontb WHERE assess.sdid=jimedoccontb.sdid)
where change = 6
and sdid = (SELECT SDID FROM jimedoccontb where assess.sdid=jimedoccontb.sdid);
---------STEP 11
update ml.problem
set change = 2,
pubuser = (SELECT USRID FROM jimedoccontb WHERE problem.sdid=jimedoccontb.sdid),
pubtime = (SELECT contbid FROM jimedoccontb WHERE problem.sdid=jimedoccontb.sdid)
where change = 6
and sdid = (SELECT SDID FROM jimedoccontb where problem.sdid=jimedoccontb.sdid);
---------STEP 12
commit;
---------STEP 13
truncate table jimedoccontb;
---------STEP 14
drop table jimedoccontb;
Posted : January 22, 2016 5:56 am