EBM-OA

SQL

查询发文

-- 查询发文
select * from T_OA_DISPATCH where NAME like '%奖%' and NUM like '%厦%房%' order by DISPATCH_ID desc

-- 查看传阅
select * from T_OA_DISPATCH_USER where DISPATCH_ID='38326'
-- 109163 DISPATCH_USER_ID
-- T_OA_DISPATCH_USER的USER_ID关联用户


-- 查用户
select * from T_EBM_USER where USER_ID='1111'
select * from T_EBM_USER where USER_NAME='xxx'

流程SQL

delete from T_EBM_WF_Inst where instance_id=224209;
delete from T_EBM_WF_Inst_Work_Participant where Instance_ID not in (select Instance_ID from T_EBM_WF_Inst);
delete from T_EBM_WF_Inst_Work_Agent where Instance_ID not in (select Instance_ID from T_EBM_WF_Inst);
delete from T_EBM_WF_Inst_Work where Instance_ID not in (select Instance_ID from T_EBM_WF_Inst);
delete from T_EBM_WF_Inst_Transition where Instance_ID not in (select Instance_ID from T_EBM_WF_Inst);
delete from T_EBM_WF_Inst_Activity_Reader where Instance_ID not in (select Instance_ID from T_EBM_WF_Inst);
delete from T_EBM_WF_Inst_Activity where Instance_ID not in (select Instance_ID from T_EBM_WF_Inst);

select count(*) from T_EBM_WF_Inst_Work_Participant where Instance_ID not in (select Instance_ID from T_EBM_WF_Inst);
select count(*) from T_EBM_WF_Inst_Work_Agent where Instance_ID not in (select Instance_ID from T_EBM_WF_Inst);
select count(*) from T_EBM_WF_Inst_Work where Instance_ID not in (select Instance_ID from T_EBM_WF_Inst);
select count(*) from T_EBM_WF_Inst_Transition where Instance_ID not in (select Instance_ID from T_EBM_WF_Inst);
select count(*) from T_EBM_WF_Inst_Activity_Reader where Instance_ID not in (select Instance_ID from T_EBM_WF_Inst);
select count(*) from T_EBM_WF_Inst_Activity where Instance_ID not in (select Instance_ID from T_EBM_WF_Inst);


select * from T_EBM_WF_Inst where Name like '%GLFDC%' and Status='2';

select * from flashback_transaction_query where table_name='T_EBM_WF_INST_WORK_AGENT';
select * from 'T_EBM_WF_INST_WORK_AGENT' as of timestamp to_timestamp('2019-05-24 11:45:00', 'yyyy-mm-dd hh24:mi:ss');



delete from T_EBM_WF_Inst
where Instance_ID in (
select Instance_ID from T_EBM_WF_Inst i
left join T_EBM_WF_Version v on i.Version_ID=v.Version_ID
where 
(v.Name like '%地产%' or 
v.Name like '%GLFDC%') and
BID is null);

select * from T_EBM_WF_INST_WORK where Instance_ID in (
select Instance_ID from T_EBM_WF_INST where version_id=186)
order by Instance_ID desc;


select * from T_EBM_WF_INST where version_id=186
order by Instance_ID desc;


select * from T_EBM_WF_INST where version_id=554
order by Instance_ID desc;

select * from T_EBM_WF_INST_WORK where Instance_ID in (
select Instance_ID from T_EBM_WF_INST where version_id=554)
order by Instance_ID desc;

select * from T_EBM_WF_INST_WORK_Participant where Instance_ID in (
select Instance_ID from T_EBM_WF_INST where version_id=554)
order by Instance_ID desc;

213438
213375
213439


select * from t_oa_receive where instance_id=213495 or instance_id=213494
 or instance_id=213439 or instance_id=213438 or instance_id=213375;
 
select * from t_oa_receive_opinion where receive_id=68269 or receive_id=68268
or receive_id=68251 or receive_id=68250 or receive_id=68221;

闪回SQL

189	OA-Prod	3	DELETE FROM "EBM"."T_EBM_WF_INST" WHERE ROWID = ? AND ORA_ROWSCN = ? and ( "INSTANCE_ID" is null or "INSTANCE_ID" is not null )	1="AAAi7pAAIAABM2XABS", 2="85495545047"
186	OA-Prod	3	DELETE FROM "EBM"."T_EBM_WF_INST" WHERE ROWID = ? AND ORA_ROWSCN = ? and ( "INSTANCE_ID" is null or "INSTANCE_ID" is not null )	1="AAAi7pAAIAABM2XABE", 2="85495544987"
183	OA-Prod	3	DELETE FROM "EBM"."T_EBM_WF_INST" WHERE ROWID = ? AND ORA_ROWSCN = ? and ( "INSTANCE_ID" is null or "INSTANCE_ID" is not null )	1="AAAi7pAAIAABM2XABG", 2="85495544588"
180	OA-Prod	2	DELETE FROM "EBM"."T_EBM_WF_INST" WHERE ROWID = ? AND ORA_ROWSCN = ? and ( "INSTANCE_ID" is null or "INSTANCE_ID" is not null )	1="AAAi7pAAIAABM5RAAE", 2="85495542956"
179	OA-Prod	3	DELETE FROM "EBM"."T_EBM_WF_INST" WHERE ROWID = ? AND ORA_ROWSCN = ? and ( "INSTANCE_ID" is null or "INSTANCE_ID" is not null )	1="AAAi7pAAIAABMzsAA/", 2="85495540710"
178	OA-Prod	2	DELETE FROM "EBM"."T_EBM_WF_INST" WHERE ROWID = ? AND ORA_ROWSCN = ? and ( "INSTANCE_ID" is null or "INSTANCE_ID" is not null )	1="AAAi7pAAIAABMx+AAC", 2="85495544124"
177	OA-Prod	2	DELETE FROM "EBM"."T_EBM_WF_INST" WHERE ROWID = ? AND ORA_ROWSCN = ? and ( "INSTANCE_ID" is null or "INSTANCE_ID" is not null )	1="AAAi7pAAIAABMzsABA", 2="85495540710"
176	OA-Prod	9	DELETE FROM "EBM"."T_EBM_WF_INST" WHERE ROWID = ? AND ORA_ROWSCN = ? and ( "INSTANCE_ID" is null or "INSTANCE_ID" is not null )	1="AAAi7pAAIAABM5RAAH", 2="85495542956"


'234416','234415','234414','234412','234388'

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;

insert into T_EBM_WF_INST
select * from T_EBM_WF_INST as of scn 85495542956 where ROWID in (
'AAAi7pAAIAABM2XABS',
'AAAi7pAAIAABM2XABE',
'AAAi7pAAIAABM2XABG',
'AAAi7pAAIAABM5RAAE',
'AAAi7pAAIAABMzsAA/',
'AAAi7pAAIAABMx+AAC',
'AAAi7pAAIAABMzsABA',
'AAAi7pAAIAABM5RAAH');


select * from flashback_transaction_query where table_name='T_EBM_WF_INST';

insert into T_EBM_WF_INST33 select * from T_EBM_WF_INST of timestamp to_timestamp('时间点', 'yyyy-mm-dd hh24:mi:ss')

发文SQL

SELECT
	d.DISPATCH_ID,
	d.NUM,
	d.NAME,
	d.FILE_ID F1_ID,
	df.FILE_ID F2_ID,
	f1.ORIGINAL_NAME,
	f1.ABS_PATH,
	f2.ORIGINAL_NAME,
	f2.ABS_PATH 
FROM
	T_OA_DISPATCH d
	LEFT JOIN T_OA_DISPATCH_FILE df ON df.DISPATCH_ID = d.DISPATCH_ID
	LEFT JOIN T_EBM_FILE f1 ON f1.FILE_ID = d.FILE_ID
	LEFT JOIN T_EBM_FILE f2 ON f2.FILE_ID = df.FILE_ID 
WHERE
	NUM IN ( '厦XXX[2022]11号');

misc

wf

/app/oa/office/TSD/f_wf_dispatch.html
/app/oa/office/glfdc/f_wf_dispatch.html

/app/oa/office/dsgs/f_wf_dispatch.html