1 PACKAGE hr_workflow_service as
2 /* $Header: hrwfserv.pkh 120.1 2005/09/23 16:08:20 svittal noship $ */
3 --
4 g_hr_activity_type varchar2(30) := 'HR_ACTIVITY_TYPE';
5 g_hr_activity_type_value varchar2(30) := 'HR_ACTIVITY_TYPE_VALUE';
6 g_hr_web_page_code varchar2(4) := 'HTML';
7 g_window_title varchar2(30) := 'WINDOW_TITLE';
8 g_wf_root_process varchar2(4) := 'ROOT';
9 g_wf_root_process_active varchar2(6) := 'ACTIVE';
10 g_wf_function varchar2(8) := 'FUNCTION';
11 g_wf_activity_notified varchar2(8) := 'NOTIFIED';
12 g_token_tickler varchar2(18) := '$$TICKLER_VALUE$$';
13 g_tickler_unordered varchar2(9) := 'UNORDERED';
14 g_tickler_ascending varchar2(9) := 'ASCENDING';
15 g_tickler_descending varchar2(10) := 'DESCENDING';
16 --
17 g_invalid_responsibility EXCEPTION;
18 TYPE g_varchar2_tab_type IS TABLE OF varchar2(2000) INDEX BY BINARY_INTEGER;
19 g_varchar2_tab_default g_varchar2_tab_type;
20 --
21 TYPE active_wf_items_rec IS RECORD (
22 active_item_key wf_items.item_key%type,
23 activity_result_code wf_item_activity_statuses_v.activity_result_code%type,
24 activity_id wf_item_activity_statuses_v.activity_id%type
25 );
26 --
27 TYPE active_wf_items_list IS TABLE OF active_wf_items_rec INDEX BY BINARY_INTEGER;
28 --
29 TYPE active_wf_trans_items_rec IS RECORD (
30 active_item_key wf_items.item_key%type
31 ,activity_id wf_item_activity_statuses_v.activity_id%type
32 ,trans_step_id hr_api_transaction_steps.transaction_step_id %type
33 ,activity_result_code
34 wf_item_activity_statuses_v.activity_result_code%type
35 );
36 --
37 TYPE active_wf_trans_items_list IS TABLE OF active_wf_trans_items_rec
38 INDEX BY BINARY_INTEGER;
39 --
40
41 -- ----------------------------------------------------------------------------
42 -- |--------------------< check_usernm_exists_subj_aprv >---------------------|
43 -- ----------------------------------------------------------------------------
44 -- Bug #788954 Fix: This procedure is used in workflow Approved Process. This
45 -- function is invoked to check that if the subject-of-approval
46 -- person has an apps login username. If not, then workflow
47 -- will not send out a notification to the subject person.
48 -- ----------------------------------------------------------------------------
49 PROCEDURE check_usernm_exists_subj_aprv
50 (itemtype IN VARCHAR2
51 ,itemkey IN VARCHAR2
52 ,actid IN NUMBER
53 ,funcmode IN VARCHAR2
54 ,resultout OUT NOCOPY VARCHAR2);
55 --
56 -- ----------------------------------------------------------------------------
57 -- |------------------------< create_hr_directory_services >------------------|
58 -- ----------------------------------------------------------------------------
59 procedure create_hr_directory_services
60 (p_item_type in wf_items.item_type%type
61 ,p_item_key in wf_items.item_key%type
62 ,p_service_name in varchar2
63 ,p_service_person_id in per_all_people_f.person_id%type);
64 --
65 -- ----------------------------------------------------------------------------
66 -- |-------------------------< get_item_attr_expanded_info >------------------|
67 -- ----------------------------------------------------------------------------
68 procedure get_item_attr_expanded_info
69 (p_item_type in wf_items.item_type%type
70 ,p_item_key in wf_items.item_key%type
71 ,p_name in wf_item_attributes.name%type
72 ,p_exists out nocopy boolean
73 ,p_subtype out nocopy wf_item_attributes.subtype%type
74 ,p_type out nocopy wf_item_attributes.type%type
75 ,p_format out nocopy wf_item_attributes.format%type
76 ,p_date_value out nocopy wf_item_attribute_values.date_value%type
77 ,p_number_value out nocopy wf_item_attribute_values.number_value%type
78 ,p_text_value out nocopy wf_item_attribute_values.text_value%type);
79 -- ----------------------------------------------------------------------------
80 -- |-------------------------< get_act_attr_expanded_info >------------------|
81 -- ----------------------------------------------------------------------------
82 procedure get_act_attr_expanded_info
83 (p_item_type in wf_items.item_type%type
84 ,p_item_key in wf_items.item_key%type
85 ,p_actid in wf_activity_attr_values.process_activity_id%type
86 ,p_name in wf_activity_attributes.name%type
87 ,p_exists out nocopy boolean
88 ,p_subtype out nocopy wf_activity_attributes.subtype%type
89 ,p_type out nocopy wf_activity_attributes.type%type
90 ,p_format out nocopy wf_activity_attributes.format%type
91 ,p_date_value out nocopy wf_activity_attr_values.date_value%type
92 ,p_number_value out nocopy wf_activity_attr_values.number_value%type
93 ,p_text_value out nocopy wf_activity_attr_values.text_value%type);
94 -- ----------------------------------------------------------------------------
95 -- |-------------------------< check_activity_type_attrs >--------------------|
96 -- ----------------------------------------------------------------------------
97 procedure check_activity_type_attrs
98 (p_item_type in wf_items.item_type%type
99 ,p_item_key in wf_items.item_key%type
100 ,p_actid in wf_activity_attr_values.process_activity_id%type);
101 -- ----------------------------------------------------------------------------
102 -- |-------------------------------< hr_web_page >----------------------------|
103 -- ----------------------------------------------------------------------------
104 procedure hr_web_page
105 (itemtype in varchar2
106 ,itemkey in varchar2
107 ,actid in number
108 ,funcmode in varchar2
109 ,resultout out nocopy varchar2);
110 -- ----------------------------------------------------------------------------
111 -- |-------------------------< check_hr_window_title >------------------------|
112 -- ----------------------------------------------------------------------------
113 function check_hr_window_title
114 (p_item_type in wf_items.item_type%type
115 ,p_item_key in wf_items.item_key%type
116 ,p_actid in wf_activity_attr_values.process_activity_id%type)
117 return boolean;
118 -- ----------------------------------------------------------------------------
119 -- |---------------------------< get_hr_window_title >------------------------|
120 -- ----------------------------------------------------------------------------
121 function get_hr_window_title
122 (p_item_type in wf_items.item_type%type
123 ,p_item_key in wf_items.item_key%type
124 ,p_actid in wf_activity_attr_values.process_activity_id%type)
125 return varchar2;
126 -- ----------------------------------------------------------------------------
127 -- |-------------------------< check_web_page_code >--------------------------|
128 -- ----------------------------------------------------------------------------
129 function check_web_page_code
130 (p_item_type in wf_items.item_type%type
131 ,p_item_key in wf_items.item_key%type
132 ,p_actid in wf_activity_attr_values.process_activity_id%type
133 ,p_web_page_section_code in wf_activity_attributes.name%type)
134 return boolean;
135 -- ----------------------------------------------------------------------------
136 -- |-----------------------------< get_web_page_code >------------------------|
137 -- ----------------------------------------------------------------------------
138 function get_web_page_code
139 (p_item_type in wf_items.item_type%type
140 ,p_item_key in wf_items.item_key%type
141 ,p_actid in wf_activity_attr_values.process_activity_id%type
142 ,p_web_page_section_code in wf_activity_attributes.name%type)
143 return varchar2;
144 -- ----------------------------------------------------------------------------
145 -- |-------------------------< check_activity_reentry >-----------------------|
146 -- ----------------------------------------------------------------------------
147 function check_activity_reentry
148 (p_item_type in wf_items.item_type%type
149 ,p_item_key in wf_items.item_key%type
150 ,p_actid in wf_activity_attr_values.process_activity_id%type)
151 return boolean;
152 -- ----------------------------------------------------------------------------
153 -- |-------------------------< get_activity_reentry_value >-------------------|
154 -- ----------------------------------------------------------------------------
155 function get_activity_reentry_value
156 (p_item_type in wf_items.item_type%type
157 ,p_item_key in wf_items.item_key%type
158 ,p_actid in wf_activity_attr_values.process_activity_id%type)
159 return wf_item_activity_statuses_v.activity_result_code%type;
160 -- ----------------------------------------------------------------------------
161 -- |------------------------------< create_process >--------------------------|
162 -- ----------------------------------------------------------------------------
163 -- Purpose: This procedure is overloaded so that it will accept p_person_id
164 -- and p_called_from as parameters.
165 -- ----------------------------------------------------------------------------
166 procedure create_process
167 (p_process_name in wf_process_activities.process_name%type
168 ,p_item_type in wf_items.item_type%type
169 ,p_person_id in varchar2 default null
170 ,p_called_from in varchar2 default null
171 ,p_item_attribute in g_varchar2_tab_type
172 default g_varchar2_tab_default
173 ,p_item_attribute_value in g_varchar2_tab_type
174 default g_varchar2_tab_default
175 ,p_number_of_attributes_in in number default 0);
176 --
177 -- ----------------------------------------------------------------------------
178 -- |---------------------------< continue_process >---------------------------|
179 -- ----------------------------------------------------------------------------
180 procedure continue_process
181 (p_item_type in wf_items.item_type%type
182 ,p_item_key in wf_items.item_key%type);
183 -- ----------------------------------------------------------------------------
184 -- |------------------------< transition_activity >---------------------------|
185 -- ----------------------------------------------------------------------------
186 procedure transition_activity
187 (p_item_type in wf_items.item_type%type
188 ,p_item_key in wf_items.item_key%type
189 ,p_actid in wf_activity_attr_values.process_activity_id%type
190 ,p_result_code in wf_item_activity_statuses_v.activity_result_code%type);
191 -- ----------------------------------------------------------------------------
192 -- |-------------------------< check_active_wf_items >-- ---------------------|
193 -- ----------------------------------------------------------------------------
194 -- Purpose: This function is overloaded. It will only return the pending
195 -- approval workflow items for a section of a page, ie. by
196 -- activity_result_code to a table.
197 -- ----------------------------------------------------------------------------
198 function check_active_wf_items
199 (p_item_type in wf_items.item_type%type
200 ,p_process_name in wf_process_activities.process_name%type
201 ,p_current_person_id in per_people_f.person_id%type
202 ,p_activity_name in wf_item_activity_statuses_v.activity_name%type
203 ,p_activity_result_code in varchar2
204 )
205 return active_wf_items_list;
206 --
207 --
208 function check_active_wf_items
209 (p_item_type in wf_items.item_type%type
210 ,p_process_name in wf_process_activities.process_name%type
211 ,p_current_person_id in per_people_f.person_id%type
212 ,p_activity_name in wf_item_activity_statuses_v.activity_name%type
213 )
214 return active_wf_items_list;
215 --
216 -- ----------------------------------------------------------------------------
217 -- |---------------------------< get_active_wf_items >-------------------------|
218 -- ----------------------------------------------------------------------------
219 -- Purpose: This function will return all the pending approval workflow items
220 -- for a page for a given item_type, item_key and the api_name. If a
221 -- page has many sections, the caller can pass the result code to
222 -- find out if a particular section has active pending approval
223 -- items by comparing the activity result code.
224 -- This function is designed specifically for use in Personal
225 -- Information to look for pending approval items which contain an
226 -- acitivity with a specific result code equals to the input parameter.
227 -- For Address Section in Personal Information, it needs to pass
228 -- a value of either "PRIMARY" or "SECONDARY" in the parameter
229 -- p_address_context because Primary Address and Secondary Address
230 -- share the same api name, which is hr_process_address_ss.
231 -- ----------------------------------------------------------------------------
232 FUNCTION get_active_wf_items
233 (p_item_type in wf_items.item_type%type
234 ,p_process_name in wf_process_activities.process_name%type
235 ,p_current_person_id in per_people_f.person_id%type
236 ,p_api_name in hr_api_transaction_steps.api_name%type
237 ,p_activity_result_code in varchar2 default null
238 ,p_address_context in varchar2 default null
239 )
240 return active_wf_trans_items_list;
241
242 --
243 procedure remove_defunct_process
244 (itemtype in varchar2
245 ,itemkey in varchar2
246 ,actid in number
247 ,funcmode in varchar2
248 ,resultout out nocopy varchar2);
249 --
250 /*procedure start_cleanup_process
251 (p_process_name in wf_process_activities.process_name%type
252 ,p_item_type in wf_items.item_type%type
253 );
254 */
255
256 procedure start_cleanup_process(
257 p_item_type in wf_items.item_type%type
258 ,p_transaction_age in wf_item_attribute_values.number_value%type
259 ,p_process_name in wf_process_activities.process_name%type default 'HR_BACKGROUND_CLEANUP_PRC',
260 p_transaction_status in varchar2 default 'ALL'
261 ) ;
262 --
263
264
265 -- Block
266 -- Stop and wait for external completion
267 -- IN
268 -- itemtype - item type
269 -- itemkey - item key
270 -- actid - process activity instance id
271 -- funcmode - execution mode
272 -- OUT
273 -- result - NOTIFIED
274 procedure Block(itemtype in varchar2,
275 itemkey in varchar2,
276 actid in number,
277 funcmode in varchar2,
278 resultout in out nocopy varchar2);
279
280
281 --
282 --
283 -- TotalConcurrent
284 -- Concurrent Program version
285 -- IN:
286 -- errbuf - CPM error message
287 -- retcode - CPM return code (0 = success, 1 = warning, 2 = error)
288 -- itemtype - Item type to delete, or null for all itemtypes
289 -- age - Minimum age of data to purge (in days)
290 procedure TotalConcurrent(
291 errbuf out nocopy varchar2,
292 retcode out nocopy varchar2,
293 itemtype in varchar2 default null,
294 age in varchar2 default '0',
295 p_process_name in varchar2 default 'HR_BACKGROUND_CLEANUP_PRC',
296 transaction_status in varchar2 default 'ALL');
297
298 --
299 function getItemType(p_transaction_id in hr_api_transactions.transaction_id%type)
300 return wf_items.item_type%type;
301
302 function getItemKey(p_transaction_id in hr_api_transactions.transaction_id%type)
303 return wf_items.item_key%type;
304
305 function item_attribute_exists
306 (p_item_type in wf_items.item_type%type
307 ,p_item_key in wf_items.item_key%type
308 ,p_name in wf_item_attribute_values.name%type)
309 return boolean;
310
311 END hr_workflow_service;