Oracle Workflow Queries

最新推荐文章于 2021-04-08 01:11:27 发布

cnvdkx2837

最新推荐文章于 2021-04-08 01:11:27 发布

阅读量116

点赞数

文章标签：数据库

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\',
     \'ssss\',
     p_parameters => NULL,
     p_send_date => NULL );

   COMMIT;

END;