Oracle Workflow Queries

最新推荐文章于 2021-09-26 18:27:57 发布

iteye_21199

最新推荐文章于 2021-09-26 18:27:57 发布

阅读量169

点赞数

文章标签：数据库 runtime

Workflow Definition

--Workflow Item Type的定义信息
select * from wf_item_types where name = 'TPLEAVE'
select * from wf_item_types_tl where name = 'TPLEAVE'

--工作流里各个节点信息
select * from wf_activities where item_type = 'TPLEAVE';
--End_Date没数据的那个版本，为当前有效版本
--Version有多个，是因为你UPLOAD了多次，每次UPLOAD都会新增一个版本
--Function的节点，那么Function中就会有对应的package.procedure的内容，比如ptleave.find_approver
--Function节点，可能还会涉及到Result_Type字段

--Message
select * from wf_messages where type = 'TPLEAVE';
select * from wf_messages_tl where type = 'TPLEAVE';

--Lookup Type
select * from wf_lookup_types_tl where lookup_type like 'OKNO';
--Lookup Code
select * from wf_lookups_tl where lookup_type like 'OKNO';

--Attributes
--Item Type Level Attributes
select * from wf_item_attributes where item_type = 'TPLEAVE';
--Activity Level Attributes
select * from wf_activity_attributes where activity_item_type = 'WFSTD'--'TPLEAVE';
--Message Level Attributes
select * from wf_message_attributes where message_type = 'TPLEAVE';

--Process上各个节点的信息
select * from wf_process_activities where process_item_type = 'TPLEAVE' and process_version = 6 and process_name = 'DEFAULT_PROCESS' ;

--Process上各个节点是如何连接的（from_process_activity，to_process_activity即Process节点的instance_id）
select * from wf_activity_transitions where from_process_activity =817499;

select * from wf_activity_transitions
start with from_process_activity = 817499
connect by prior to_process_activity = from_process_activity and result_code <> '#TIMEOUT';

Workflow Runtime Data Flow

select * from wf_items where item_type='TPLEAVE';
--wf_engine.createprocess首先会插这个表
--Dev Studio会插这个表

--Activity History(工作流的流程图)
select * from wf_item_activity_statuses
where item_type='TPLEAVE' and item_key = 4088307
order by begin_date desc
--Process_Activity为Process的Instance_ID.

--通知节点上消息（只能看通知的Subject，无法看到Message的Body）
select * from wf_notifications where notification_id = 8972914
--如果想看Message Body，还得回到Message的定义
select * from wf_messages_tl where type = 'TPLEAVE' and name = 'LEAVE_APPROVAL';
--通知节点上消息的Attribute
select * from wf_notification_attributes where notification_id = 8972914

API Launch Workflow

declare
v_itemtype varchar2(30):='TPLEAVE';
v_process varchar2(30):='DEFAULT_PROCESS';
v_itemkey varchar2(30);
begin
select rcv_transactions_s.nextval into v_itemkey from dual;

wf_engine.createprocess(v_itemtype, v_itemkey,v_process);

wf_engine.SetItemAttrText(v_itemtype ,v_itemkey,'EMPLOYEE','OPERATIONS');
wf_engine.SetItemAttrText(v_itemtype ,v_itemkey,'MANAGER','CBROWN');
wf_engine.SetItemAttrDate(v_itemtype ,v_itemkey,'START_DATE',sysdate);
wf_engine.SetItemAttrDate(v_itemtype ,v_itemkey,'END_DATE',SYSDATE+1);

wf_engine.StartProcess(v_itemtype, v_itemkey);
commit;
end;

Business Event

SELECT * FROM dba_queues WHERE name = 'WF_DEFERRED';

SELECT * FROM WF_DEFERRED WHERE corrid LIKE '%oracle.apps.inv.ptian.agent%';

SELECT * FROM wf_systems;
--Name:MC3YD213.CN.ORACLE.COM
--GUID:C0C6E583B29F8AC2E040B60A214A1414
--Display Name:LA5095

SELECT * FROM wf_agents;

--Event定义表
SELECT * FROM wf_events WHERE name = 'oracle.apps.inv.ptian.agent';

SELECT * FROM wf_event_subscriptions WHERE event_filter_guid =
(SELECT guid FROM wf_events WHERE name = 'oracle.apps.inv.ptian.agent');
--Action_Code:SEND_AGENT_RG

--API to Raise EVENT
BEGIN
wf_event.RAISE(
'oracle.apps.po.standardpo.approved',
'109',
'<test>ssss<test>',
p_parameters => NULL,
p_send_date => NULL );

COMMIT;

END;