1 PACKAGE jtf_rs_wf_integration_pub AUTHID CURRENT_USER AS
2 /* $Header: jtfrswfs.pls 120.0 2005/05/11 08:23:25 appldev ship $ */
3 /*#
4 * Workflow Integration API
5 * This API integrates resources, groups, teams, group members and team members to workflow roles
6 * This API contains the procedures which can be called while create/update/delete of Resources,
7 * create/update of Groups/Teams and create/delete of group/team members.
8 * All the below Procedures will create/update records in wf_local_roles and wf_local_user_roles
9 * @rep:scope private
10 * @rep:product JTF
11 * @rep:displayname Workflow Integration API
12 * @rep:category BUSINESS_ENTITY JTF_RS_WF_ROLE
13 * @rep:category BUSINESS_ENTITY JTF_RS_WF_USER_ROLE
14 */
15 /*****************************************************************************************
16 ******************************************************************************************/
17
18 /* This procedure returns the workflow role_name, Orig System
19 and Orig System Id of the resource_id passed. */
20
21 /*#
22 * Get workflow role informations for a given resouurce
23 * This procedure returns the workflow role informations for a given resource.
24 * @param p_resource_id Resource Id
25 * @param x_role_name Out parameter for Workflow Role Name
26 * @param x_orig_system Out parameter for Workflow Role Orig System
27 * @param x_orig_system_id Out parameter for Workflow Role Orig System Id
28 * @rep:scope private
29 * @rep:displayname Get Workflow Role informations for a Resource
30 */
31 PROCEDURE get_wf_role
32 (p_resource_id IN number,
33 x_role_name OUT NOCOPY varchar2,
34 x_orig_system OUT NOCOPY varchar2,
35 x_orig_system_id OUT NOCOPY number);
36
37 /* This overloaded procedure returns the workflow role_name, Orig System
38 and Orig System Id of the resource_id/user_id passed. */
39
40 /*#
41 * Get workflow role informations for a given Resouurce and User
42 * This procedure returns the workflow role informations for a given resource and a user.
43 * @param p_resource_id Resource Id
44 * @param p_user_id User Id
45 * @param x_role_name Out parameter for Workflow Role Name
46 * @param x_orig_system Out parameter for Workflow Role Orig System
47 * @param x_orig_system_id Out parameter for Workflow Role Orig System Id
48 * @rep:scope private
49 * @rep:displayname Get Workflow Role informations for a Resource and User
50 */
51 PROCEDURE get_wf_role
52 (p_resource_id IN number,
53 p_user_id IN number,
54 x_role_name OUT NOCOPY varchar2,
55 x_orig_system OUT NOCOPY varchar2,
56 x_orig_system_id OUT NOCOPY number);
57
58 /* This function returns the workflow role_name
59 of the resource_id passed. */
60
61 /*#
62 * Get workflow role for a given resouurce
63 * This function returns the workflow role for a given resource.
64 * @param p_resource_id Resource Id
65 * @return Workflow Role
66 * @rep:scope private
67 * @rep:displayname Get Workflow Role for a Resource
68 */
69 FUNCTION get_wf_role
70 (p_resource_id IN number)
71 RETURN varchar2;
72
73 /*
74 AddParameterToList - adds name and value to wf_parameter_list_t
75 If the list is null, will initialize, otherwise just adds to the end of list
76 */
77
78 /*#
79 * Add Parameters to the List
80 * This procedure is used to add the parameters to the list, wf_parameter_list_t
81 * @param p_name Name
82 * @param p_value Value
83 * @param p_parameterlist Workflow Parameter List
84 * @rep:scope private
85 * @rep:displayname Get Workflow Role for a Resource
86 */
87 PROCEDURE AddParameterToList(p_name in varchar2,
88 p_value in varchar2,
89 p_parameterlist in out nocopy wf_parameter_list_t);
90
91 /* This procedure creates a record in wf_local_roles table
92 and a self relate record in wf_local_user_roles table
93 for the redource_id passed. */
94 /*#
95 * API to populate Workflow local tables while creating a resource
96 * This API will insert records to wf_local_role and wf_local_user_role,
97 * if the resource is active / future active at the time of creation
98 * and does not have an fnd_user attached to it.
99 * @param p_api_version API version
100 * @param p_init_msg_list Initialization of the message list
101 * @param p_commit Commit
102 * @param p_resource_id Resource Identifier
103 * @param p_resource_name Name of the Resource
104 * @param p_category Category of the Resource
105 * @param p_user_id User Id of the Resource
106 * @param p_email_address Email Address
107 * @param p_start_date_active Date on which the resource becomes active.
108 * @param p_end_date_active Date on which the resource is no longer active.
109 * @param x_return_status Output parameter for return status
110 * @param x_msg_count Output parameter for number of user messages from this procedure
111 * @param x_msg_data Output parameter containing last user message from this procedure
112 * @rep:scope private
113 * @rep:displayname Populate Workflow local tables while creating a resource
114 */
115 PROCEDURE create_resource
116 (P_API_VERSION IN NUMBER,
117 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT NULL,
118 P_COMMIT IN VARCHAR2 DEFAULT NULL,
119 P_RESOURCE_ID IN NUMBER,
120 P_RESOURCE_NAME IN VARCHAR2,
121 P_CATEGORY IN VARCHAR2,
122 P_USER_ID IN NUMBER,
123 P_EMAIL_ADDRESS IN VARCHAR2,
124 P_START_DATE_ACTIVE IN DATE,
125 P_END_DATE_ACTIVE IN DATE,
126 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
127 X_MSG_COUNT OUT NOCOPY NUMBER,
128 X_MSG_DATA OUT NOCOPY VARCHAR2
129 );
130
131 /* This procedure creates/updates records in wf_local_roles,
132 if there is a change in jtf_rs_resource_extns table and this
133 will create a mismatch b/w resource_table and workflow tables.
134 This also moves the workflow user role, if the user_id of the
135 passed resource is changed. */
136 /*#
137 * API to populate Workflow local tables while updating a resource
138 * This procedure creates/updates records in wf_local_roles and wf_local_user_roles,
139 * if there is a change in jtf_rs_resource_extns table and this
140 * will create a mismatch b/w resource table and workflow tables.
141 * This also moves the workflow user role, if the user_id of the
142 * passed resource is changed.
143 * @param p_api_version API version
144 * @param p_init_msg_list Initialization of the message list
145 * @param p_commit Commit
146 * @param p_resource_id Resource Identifier
147 * @param p_resource_name Name of the Resource
148 * @param p_user_id User Id of the Resource
149 * @param p_email_address Email Address
150 * @param p_start_date_active Date on which the resource becomes active.
151 * @param p_end_date_active Date on which the resource is no longer active.
152 * @param x_return_status Output parameter for return status
153 * @param x_msg_count Output parameter for number of user messages from this procedure
154 * @param x_msg_data Output parameter containing last user message from this procedure
155 * @rep:scope private
156 * @rep:displayname Populate Workflow local tables while updating a resource
157 */
158 PROCEDURE update_resource
159 (P_API_VERSION IN NUMBER,
160 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT NULL,
161 P_COMMIT IN VARCHAR2 DEFAULT NULL,
162 P_RESOURCE_ID IN NUMBER,
163 P_RESOURCE_NAME IN VARCHAR2,
164 P_USER_ID IN NUMBER,
165 P_EMAIL_ADDRESS IN VARCHAR2,
166 P_START_DATE_ACTIVE IN DATE,
167 P_END_DATE_ACTIVE IN DATE,
168 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
169 X_MSG_COUNT OUT NOCOPY NUMBER,
170 X_MSG_DATA OUT NOCOPY VARCHAR2
171 );
172
173 /* This procedure deletes the record from wf_local_roles table
174 for the Resource_id passed. */
175 /*#
176 * API to populate Workflow local tables while deleting a resource
177 * This procedure updates records in wf_local_roles and wf_local_user_roles to inactive,
178 * @param p_api_version API version
179 * @param p_init_msg_list Initialization of the message list
180 * @param p_commit Commit
181 * @param p_resource_id Resource Identifier
182 * @param x_return_status Output parameter for return status
183 * @param x_msg_count Output parameter for number of user messages from this procedure
184 * @param x_msg_data Output parameter containing last user message from this procedure
185 * @rep:scope private
186 * @rep:displayname Populate Workflow local tables while deleting a resource
187 */
188 PROCEDURE delete_resource
189 (P_API_VERSION IN NUMBER,
190 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT NULL,
191 P_COMMIT IN VARCHAR2 DEFAULT NULL,
192 P_RESOURCE_ID IN NUMBER,
193 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
194 X_MSG_COUNT OUT NOCOPY NUMBER,
195 X_MSG_DATA OUT NOCOPY VARCHAR2
196 ) ;
197
198
199 /* If called for an active group, this procedure creates a record
200 in wf_local_roles table and a self relate record in wf_local_user_roles
201 table */
202 /*#
203 * API to populate Workflow local tables while creating a group
204 * This API will insert records to wf_local_role and wf_local_user_role,
205 * if the group is active / future active at the time of creation
206 * @param p_api_version API version
207 * @param p_init_msg_list Initialization of the message list
208 * @param p_commit Commit
209 * @param p_group_id Group Identifier
210 * @param p_group_name Name of the Group
211 * @param p_email_address Email Address of the Group
212 * @param p_start_date_active Date on which the group becomes active.
213 * @param p_end_date_active Date on which the group is no longer active.
214 * @param x_return_status Output parameter for return status
215 * @param x_msg_count Output parameter for number of user messages from this procedure
216 * @param x_msg_data Output parameter containing last user message from this procedure
217 * @rep:scope private
218 * @rep:displayname Populate Workflow local tables while creating a group
219 */
220 PROCEDURE create_resource_group
221 (P_API_VERSION IN NUMBER,
222 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT NULL,
223 P_COMMIT IN VARCHAR2 DEFAULT NULL,
224 P_GROUP_ID IN NUMBER,
225 P_GROUP_NAME IN VARCHAR2,
226 P_EMAIL_ADDRESS IN VARCHAR2,
227 P_START_DATE_ACTIVE IN DATE,
228 P_END_DATE_ACTIVE IN DATE,
229 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
230 X_MSG_COUNT OUT NOCOPY NUMBER,
231 X_MSG_DATA OUT NOCOPY VARCHAR2
232 ) ;
233
234 /* 1.If called for an active group,
235 1.1 having no record in wf_local_roles table,
236 this procedure creates a record in wf_local_roles table and
237 a self relate record in wf_local_user_roles table.
238 1.2 having changes in start/end date, group name or email address,
239 applies these changes to wf_local_roles.
240 2.If called for an inactive group,
241 2.1 having a record in wf_local_roles table,
242 2.1.1 group to be active in future
243 updates the wf_local_roles record if group start date change from
244 past to future.
245 2.1.2 group was active in the past
246 this procedure updates the wf_local_roles record, if any of the
247 start date/end date/email/group name changed.
248 */
249 /*#
250 * API to populate Workflow local tables while updating a group
251 * This procedure creates/updates records in wf_local_roles and wf_local_user_roles,
252 * if there is a change in jtf_rs_groups_vl and this
253 * will create a mismatch b/w resource group table and workflow tables.
254 * @param p_api_version API version
255 * @param p_init_msg_list Initialization of the message list
256 * @param p_commit Commit
257 * @param p_group_id Group Identifier
258 * @param p_group_name Name of the Group
259 * @param p_email_address Email Address of the Group
260 * @param p_start_date_active Date on which the group becomes active.
261 * @param p_end_date_active Date on which the group is no longer active.
262 * @param x_return_status Output parameter for return status
263 * @param x_msg_count Output parameter for number of user messages from this procedure
264 * @param x_msg_data Output parameter containing last user message from this procedure
265 * @rep:scope private
266 * @rep:displayname Populate Workflow local tables while updating a group
267 */
268 PROCEDURE update_resource_group
269 (P_API_VERSION IN NUMBER,
270 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT NULL,
271 P_COMMIT IN VARCHAR2 DEFAULT NULL,
272 P_GROUP_ID IN NUMBER,
273 P_GROUP_NAME IN VARCHAR2,
274 P_EMAIL_ADDRESS IN VARCHAR2,
275 P_START_DATE_ACTIVE IN DATE,
276 P_END_DATE_ACTIVE IN DATE,
277 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
278 X_MSG_COUNT OUT NOCOPY NUMBER,
279 X_MSG_DATA OUT NOCOPY VARCHAR2
280 ) ;
281
282 /* Creates record in wf_local_user_roles table for the group member
283 if group and resource both are active. */
284 /*#
285 * API to populate Workflow local tables while creating a group member
286 * This API will insert records to wf_local_user_role,
287 * if the group and resource both are active / future active at the time of creation
288 * @param p_api_version API version
289 * @param p_init_msg_list Initialization of the message list
290 * @param p_commit Commit
291 * @param p_resource_id Resource Identifier
292 * @param p_group_id Group Identifier
293 * @param x_return_status Output parameter for return status
294 * @param x_msg_count Output parameter for number of user messages from this procedure
295 * @param x_msg_data Output parameter containing last user message from this procedure
296 * @rep:scope private
297 * @rep:displayname Populate Workflow local tables while creating a group member
298 */
299 PROCEDURE create_resource_group_members
300 (P_API_VERSION IN NUMBER,
301 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT NULL,
302 P_COMMIT IN VARCHAR2 DEFAULT NULL,
303 P_RESOURCE_ID IN NUMBER,
304 P_GROUP_ID IN NUMBER,
305 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
306 X_MSG_COUNT OUT NOCOPY NUMBER,
307 X_MSG_DATA OUT NOCOPY VARCHAR2
308 );
309
310 /* deletes record in wf_local_user_roles table for the group
311 member. */
312 /*#
313 * API to populate Workflow local tables while deleting a group member
314 * This API will update records in wf_local_user_role to inactive
315 * @param p_api_version API version
316 * @param p_init_msg_list Initialization of the message list
317 * @param p_commit Commit
318 * @param p_resource_id Resource Identifier
319 * @param p_group_id Group Identifier
320 * @param x_return_status Output parameter for return status
321 * @param x_msg_count Output parameter for number of user messages from this procedure
322 * @param x_msg_data Output parameter containing last user message from this procedure
323 * @rep:scope private
324 * @rep:displayname Populate Workflow local tables while deleting a group member
325 */
326 PROCEDURE delete_resource_group_members
327 (P_API_VERSION IN NUMBER,
328 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT NULL,
329 P_COMMIT IN VARCHAR2 DEFAULT NULL,
330 P_RESOURCE_ID IN NUMBER,
331 P_GROUP_ID IN NUMBER,
332 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
336
333 X_MSG_COUNT OUT NOCOPY NUMBER,
334 X_MSG_DATA OUT NOCOPY VARCHAR2
335 );
337 /*#
338 * API to populate Workflow local tables while creating a team
339 * This API will insert records to wf_local_role and wf_local_user_role,
340 * if the team is active / future active at the time of creation
341 * @param p_api_version API version
342 * @param p_init_msg_list Initialization of the message list
343 * @param p_commit Commit
344 * @param p_team_id Team Identifier
345 * @param p_team_name Name of the Team
346 * @param p_email_address Email Address of the Team
347 * @param p_start_date_active Date on which the team becomes active.
348 * @param p_end_date_active Date on which the team is no longer active.
349 * @param x_return_status Output parameter for return status
350 * @param x_msg_count Output parameter for number of user messages from this procedure
351 * @param x_msg_data Output parameter containing last user message from this procedure
352 * @rep:scope private
353 * @rep:displayname Populate Workflow local tables while creating a team
354 */
355 PROCEDURE create_resource_team
356 (P_API_VERSION IN NUMBER,
357 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT NULL,
358 P_COMMIT IN VARCHAR2 DEFAULT NULL,
359 P_TEAM_ID IN NUMBER,
360 P_TEAM_NAME IN VARCHAR2,
361 P_EMAIL_ADDRESS IN VARCHAR2,
362 P_START_DATE_ACTIVE IN DATE,
363 P_END_DATE_ACTIVE IN DATE,
364 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
365 X_MSG_COUNT OUT NOCOPY NUMBER,
366 X_MSG_DATA OUT NOCOPY VARCHAR2
367 );
368
369 /*#
370 * API to populate Workflow local tables while updating a team
371 * This procedure creates/updates records in wf_local_roles and wf_local_user_roles,
372 * if there is a change in jtf_rs_teams_vl and this
373 * will create a mismatch b/w resource team table and workflow tables.
374 * @param p_api_version API version
375 * @param p_init_msg_list Initialization of the message list
376 * @param p_commit Commit
377 * @param p_team_id Team Identifier
378 * @param p_team_name Name of the Team
379 * @param p_email_address Email Address of the Team
380 * @param p_start_date_active Date on which the team becomes active.
381 * @param p_end_date_active Date on which the team is no longer active.
382 * @param x_return_status Output parameter for return status
383 * @param x_msg_count Output parameter for number of user messages from this procedure
384 * @param x_msg_data Output parameter containing last user message from this procedure
385 * @rep:scope private
386 * @rep:displayname Populate Workflow local tables while updating a team
387 */
388 PROCEDURE update_resource_team
389 (P_API_VERSION IN NUMBER,
390 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT NULL,
391 P_COMMIT IN VARCHAR2 DEFAULT NULL,
392 P_TEAM_ID IN NUMBER,
393 P_TEAM_NAME IN VARCHAR2,
394 P_EMAIL_ADDRESS IN VARCHAR2,
395 P_START_DATE_ACTIVE IN DATE,
396 P_END_DATE_ACTIVE IN DATE,
397 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
398 X_MSG_COUNT OUT NOCOPY NUMBER,
399 X_MSG_DATA OUT NOCOPY VARCHAR2
400 );
401
402 /*#
403 * API to populate Workflow local tables while creating a team member
404 * This API will insert records to wf_local_user_role,
405 * if the team and resource/group both are active / future active at the time of creation
406 * @param p_api_version API version
407 * @param p_init_msg_list Initialization of the message list
408 * @param p_commit Commit
409 * @param p_resource_id Resource Identifier
410 * @param p_group_id Group Identifier
411 * @param p_team_id Team Identifier
412 * @param x_return_status Output parameter for return status
413 * @param x_msg_count Output parameter for number of user messages from this procedure
414 * @param x_msg_data Output parameter containing last user message from this procedure
415 * @rep:scope private
416 * @rep:displayname Populate Workflow local tables while creating a team member
417 */
418 PROCEDURE create_resource_team_members
419 (P_API_VERSION IN NUMBER,
420 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT NULL,
421 P_COMMIT IN VARCHAR2 DEFAULT NULL,
422 P_RESOURCE_ID IN NUMBER,
423 P_GROUP_ID IN NUMBER,
424 P_TEAM_ID IN NUMBER,
425 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
426 X_MSG_COUNT OUT NOCOPY NUMBER,
427 X_MSG_DATA OUT NOCOPY VARCHAR2
428 );
429
430 /*#
431 * API to populate Workflow local tables while deleting a team member
432 * This API will update records in wf_local_user_role to inactive
433 * @param p_api_version API version
434 * @param p_init_msg_list Initialization of the message list
435 * @param p_commit Commit
436 * @param p_resource_id Resource Identifier
437 * @param p_group_id Group Identifier
438 * @param p_team_id Team Identifier
439 * @param x_return_status Output parameter for return status
440 * @param x_msg_count Output parameter for number of user messages from this procedure
441 * @param x_msg_data Output parameter containing last user message from this procedure
442 * @rep:scope private
443 * @rep:displayname Populate Workflow local tables while deleting a team member
444 */
445 PROCEDURE delete_resource_team_members
446 (P_API_VERSION IN NUMBER,
447 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT NULL,
448 P_COMMIT IN VARCHAR2 DEFAULT NULL,
449 P_RESOURCE_ID IN NUMBER,
450 P_GROUP_ID IN NUMBER,
451 P_TEAM_ID IN NUMBER,
452 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
453 X_MSG_COUNT OUT NOCOPY NUMBER,
454 X_MSG_DATA OUT NOCOPY VARCHAR2
455 );
456
457 END jtf_rs_wf_integration_pub;
[Home] [Help]