CREATE SEQUENCE xx_event_key_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; /******************************************************************************/ CREATE OR REPLACE FUNCTION GetNextEventKey RETURN NUMBER IS CURSOR c_eventkey IS SELECT xx_event_key_seq.nextval FROM DUAL; v_eventkey PLS_INTEGER; BEGIN OPEN c_eventkey; FETCH c_eventkey INTO v_eventkey; CLOSE c_eventkey; RETURN(v_EventKey); END GetNextEventKey; /******************************************************************************/ CREATE OR REPLACE PROCEDURE enqueue_event ( p_event_name IN VARCHAR2 , p_event_key IN VARCHAR2 , p_event_body IN CLOB DEFAULT NULL , p_event_parameters IN WF_PARAMETER_LIST_T , p_agent_name IN VARCHAR2 DEFAULT 'WF_IN' , p_priority IN PLS_INTEGER DEFAULT 10 , p_msg_id OUT RAW ) IS v_msg_id RAW(16); v_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T; v_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T; v_event WF_EVENT_T; v_recipient_list DBMS_AQ.AQ$_RECIPIENT_LIST_T; v_queue_name WF_AGENTS.QUEUE_NAME%TYPE; v_queue_address WF_AGENTS.ADDRESS%TYPE; CURSOR c_get_agent ( cp_agent_name IN VARCHAR2 ) IS SELECT wfa.address , wfa.queue_name FROM WF_AGENTS wfa WHERE wfa.name = cp_agent_name; BEGIN -- Fetch required values from Workflow BES tables OPEN c_get_agent (cp_agent_name => p_agent_name); FETCH c_get_agent INTO v_queue_address, v_queue_name; CLOSE c_get_agent; -- Initialise empty event wf_event_t.initialize(v_event); IF (DBMS_LOB.getLength(p_event_body) > 0) THEN -- Event data has been passed through, so need to set this as part of the -- event body v_event.SetEventData ( p_event_body ); END IF; -- Set EVENT properties v_event.SetEventKey ( p_event_key ); v_event.SetParameterList ( p_event_parameters ); v_event.SetFromAgent ( wf_agent_t(p_agent_name,v_queue_address) ); v_event.SetPriority ( p_priority ); v_event.SetEventName ( p_event_name ); v_event.SetSendDate ( SYSDATE ); -- Build recipient list for multi-consumer queue v_recipient_list(1) := SYS.AQ$_AGENT(p_agent_name,NULL,NULL); -- Set MESSAGE properties for the enqueue v_message_properties.priority := p_priority; v_message_properties.correlation := p_event_name; v_message_properties.recipient_list := v_recipient_list; -- Enqueue the message DBMS_AQ.ENQUEUE ( queue_name => v_queue_name , enqueue_options => v_enqueue_options , message_properties => v_message_properties , payload => v_event , msgid => v_msg_id ); -- Return the message ID of the message just enqueued to calling application p_msg_id := v_msg_id; END enqueue_event; /******************************************************************************/ CREATE OR REPLACE TRIGGER xx_my_trigger AFTER UPDATE ON BOM.CST_INV_LAYERS FOR EACH ROW DECLARE v_event_key WF_ITEMS.ITEM_KEY%TYPE; v_inventory_item_id VARCHAR2(50); v_date_and_time VARCHAR2(25); v_org_id NUMBER; v_item_description MTL_SYSTEM_ITEMS.DESCRIPTION%TYPE; v_parameter_list wf_parameter_list_t DEFAULT wf_parameter_list_t(); v_parameter_t wf_parameter_t DEFAULT wf_parameter_t(NULL, NULL); v_message_id RAW(16); v_temp NUMBER := 0; v_segment1 MTL_SYSTEM_ITEMS.SEGMENT1%TYPE; v_number_of_recipients PLS_INTEGER; v_event_name WF_EVENTS.NAME%TYPE; -- Fetch the notification recipient from the WF_ROLES table, based on the -- position which has been specified in DFF1 for the set of books, and also -- linking to ap_system_parameters to get the current set of books. CURSOR c_get_recipient_role ( cp_org_id IN NUMBER ) IS SELECT FU.user_name FROM per_people_f PPF , per_assignments_f PAF , per_positions_v PPV , fnd_user FU , gl_sets_of_books GLB , ap_system_parameters_all ASP WHERE PPF.person_id = PAF.person_id AND PAF.position_id = PPV.position_id AND PPF.business_group_id = PAF.business_group_id AND PPF.business_group_id = PPV.business_group_id AND PPV.organization_id = PAF.organization_id AND FU.employee_id = PPF.person_id AND PPV.POSITION_ID = GLB.attribute1 AND GLB.SET_OF_BOOKS_ID = ASP.set_of_books_id AND ASP.org_id = cp_org_id AND SYSDATE BETWEEN NVL(PPV.date_effective, SYSDATE) AND NVL(PPV.date_end, SYSDATE) AND SYSDATE BETWEEN NVL(PAF.effective_start_date, SYSDATE) AND NVL(PAF.effective_end_date, SYSDATE); CURSOR c_count_recipients ( cp_org_id IN NUMBER ) IS SELECT COUNT(*) FROM per_people_f PPF , per_assignments_f PAF , per_positions_v PPV , fnd_user FU , gl_sets_of_books GLB , ap_system_parameters_all ASP WHERE PPF.person_id = PAF.person_id AND PAF.position_id = PPV.position_id AND PPF.business_group_id = PAF.business_group_id AND PPF.business_group_id = PPV.business_group_id AND PPV.organization_id = PAF.organization_id AND FU.employee_id = PPF.person_id AND PPV.POSITION_ID = GLB.attribute1 AND GLB.SET_OF_BOOKS_ID = ASP.set_of_books_id AND ASP.org_id = cp_org_id AND SYSDATE BETWEEN NVL(PPV.date_effective, SYSDATE) AND NVL(PPV.date_end, SYSDATE) AND SYSDATE BETWEEN NVL(PAF.effective_start_date, SYSDATE) AND NVL(PAF.effective_end_date, SYSDATE); BEGIN v_event_name := 'xx.matt.searle.test.event'; v_date_and_time := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'); v_inventory_item_id := TO_CHAR(:NEW.INVENTORY_ITEM_ID); -- Add event parameters to the paramaeter list v_parameter_t.name := 'ORG_ID'; v_parameter_t.value := v_org_id; v_parameter_list.extend; v_parameter_list(1) := v_parameter_t; v_parameter_t.name := 'ITEM_NO'; v_parameter_t.value := v_segment1; v_parameter_list.extend; v_parameter_list(2) := v_parameter_t; v_parameter_t.name := 'DATE_AND_TIME'; v_parameter_t.value := v_date_and_time; v_parameter_list.extend; v_parameter_list(3) := v_parameter_t; -- Extend the parameter list to take another entry for recipient role v_parameter_list.extend; OPEN c_count_recipients ( cp_org_id => ln_org_id ); FETCH c_count_recipients INTO v_number_of_recipients; CLOSE c_count_recipients; IF v_number_of_recipients = 0 THEN v_parameter_t.name := 'RECIPIENT_ROLE'; v_parameter_t.value := 'SYSADMIN'; v_parameter_list(4) := v_parameter_t; v_parameter_t.name := 'SYSADMIN_MESSAGE'; v_parameter_t.value := 'You are receiving this notification as there are no users associated ' ||'with the role specified in Attribute1 on the Set of Books.'; v_parameter_list.extend; v_parameter_list(5) := v_parameter_t; v_event_key := :NEW.LAYER_ID||'-'||TO_CHAR(v_inv_layer_id)||'-'||TO_CHAR(GetNextEventKey); -- Raise the event externally via an advanced queue -- This is the optimum method of raising a business event from a trigger -- but does rely on an inbound listener running to process the event. enqueue_event ( p_event_name => v_event_name , p_event_key => v_event_key , p_event_parameters => v_parameter_list , p_msg_id => v_message_id ); ELSE -- Fetch each recipient of the notification, i.e. each person assigned to -- the position specified in DFF1 for the set of books. FOR i IN c_get_recipient_role ( cp_org_id => ln_org_id ) LOOP -- Set recipient role parameter = current person found in the role v_parameter_t.name := 'RECIPIENT_ROLE'; v_parameter_t.value := i.user_name; v_parameter_list(4) := v_parameter_t; -- Build event key in the format Layer ID- -- This will create an individual instance of the workflow per person -- in the position hierarchy. This is easier to track down than having -- to create an ad-hoc role, populate it, notify the role, then purge -- the role at some point in the future. v_event_key := :NEW.LAYER_ID||'-'||TO_CHAR(ln_inv_layer_id)||'-'||TO_CHAR(GetNextEventKey); -- Raise the event externally via an advanced queue -- This is the optimum method of raising a business event from a trigger -- but does rely on an inbound listener running to process the event. enqueue_event ( p_event_name => v_event_name , p_event_key => v_event_key , p_event_parameters => v_parameter_list , p_msg_id => v_message_id ); END LOOP; END; /