[Home] [Help]
PACKAGE BODY: APPS.IBC_CITEM_WORKFLOW_PVT
Source
1 PACKAGE BODY IBC_CITEM_WORKFLOW_PVT as
2 /* $Header: ibcciwfb.pls 120.10.12010000.3 2008/09/25 05:43:26 rsatyava ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBC_CITEM_WORKFLOW_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ibcciwfb.pls';
6
7
8 -- --------------------------------------------------------------
9 -- GET CONTENT TYPE
10 --
11 -- Used to check if the content item exists and to get the content
12 -- -- type code
13 --
14 -- --------------------------------------------------------------
15 FUNCTION getContentType(
16 f_content_item_id IN NUMBER
17 )
18 RETURN VARCHAR2
19 IS
20 CURSOR c_ctype IS
21 SELECT
22 content_type_code
23 FROM
24 ibc_content_items
25 WHERE
26 content_item_id = f_content_item_id;
27
28 temp IBC_CONTENT_TYPES_B.content_type_code%TYPE;
29 BEGIN
30
31 OPEN c_ctype;
32 FETCH c_ctype INTO temp;
33
34 IF(c_ctype%NOTFOUND) THEN
35 -- not found!
36 CLOSE c_ctype;
37 RETURN NULL;
38 ELSE
39 -- found!
40 CLOSE c_ctype;
41 RETURN temp;
42 END IF;
43 END;
44
45 FUNCTION get_user_description(p_user_id IN NUMBER) RETURN VARCHAR2
46 IS
47 l_result VARCHAR2(80);
48 CURSOR c_user_description(p_user_id NUMBER) IS
49 SELECT description
50 FROM fnd_user
51 WHERE user_id = p_user_id;
52 BEGIN
53 OPEN c_user_description(p_user_id);
54 FETCH c_user_description INTO l_result;
55 CLOSE c_user_description;
56 RETURN l_result;
57 END get_user_description;
58
59 -- --------------------------------------------------------------------
60 -- PROCEDURE: Create_User_List
61 -- DESCRIPTION: Given p_resource_id and p_resource_type, it returns
62 -- a comma separated list of usernames(p_user_list).
63 -- --------------------------------------------------------------------
64 PROCEDURE Create_User_List(
65 p_resource_id IN NUMBER
66 ,p_resource_type IN VARCHAR2
67 ,p_user_list IN OUT NOCOPY VARCHAR2
68 ) IS
69
70 l_resource_type VARCHAR2(30);
71 l_resource_number VARCHAR2(30);
72 l_user_name VARCHAR2(30);
73
74 CURSOR c_grp_members(p_resource_id NUMBER) IS
75 SELECT group_id group_id, resource_id group_resource_id, 'INDIVIDUAL' resource_type
76 FROM jtf_rs_group_members
77 WHERE group_id = p_resource_id
78 AND delete_flag = 'N'
79 UNION
80 SELECT rgm.group_id group_id, rgr.group_id group_resource_id, 'GROUP' resource_type
81 FROM jtf_rs_group_members rgm, jtf_rs_grp_relations rgr
82 WHERE rgm.group_id = rgr.related_group_id
83 AND rgm.group_id = p_resource_id
84 AND rgm.delete_flag = 'N'
85 AND rgr.delete_flag = 'N';
86
87 CURSOR c_user_name(p_resource_id IN NUMBER) IS
88 SELECT resource_number, user_name
89 FROM jtf_rs_resource_extns
90 WHERE resource_id = p_resource_id;
91
92 BEGIN
93
94 l_resource_type := RTRIM(p_resource_type);
95
96 IF l_resource_type IN ('GROUP', 'RS_GROUP') THEN
97 FOR rec_member IN c_grp_members(p_resource_id) LOOP
98 Create_User_List(p_resource_id => rec_member.group_resource_id,
99 p_resource_type => rec_member.resource_type,
100 p_user_list => p_user_list);
101 END LOOP;
102 ELSE
103 OPEN c_user_name(p_resource_id);
104 FETCH c_user_name INTO l_resource_number, l_user_name;
105 IF c_user_name%FOUND AND l_user_name IS NOT NULL THEN
106 IF p_user_list IS NOT NULL THEN
107 p_user_list := p_user_list || ', ' || l_user_name;
108 ELSE
109 p_user_list := l_user_name;
110 END IF;
111 END IF;
112 CLOSE c_user_name;
113 END IF;
114 -- Exception Handler Added for NOCOPY Change (11/08/2002) By ENUNEZ
115 EXCEPTION
116 WHEN OTHERS THEN
117 RAISE;
118 END Create_User_List;
119
120 FUNCTION Remove_Duplicates(p_list IN VARCHAR2)
121 RETURN VARCHAR2
122 IS
123 l_list VARCHAR2(5000);
124 l_result VARCHAR2(5000);
125 l_sep_pos NUMBER;
126 l_value VARCHAR2(80);
127 BEGIN
128
129 l_list := p_list;
130
131 LOOP
132 l_sep_pos := INSTR(l_list, ',');
133 IF l_sep_pos > 0 THEN
134 l_value := SUBSTR(l_list, 1, l_sep_pos - 1);
135 ELSE
136 l_value := l_list;
137 END IF;
138 l_value := RTRIM(LTRIM(l_value));
139 IF l_value IS NOT NULL THEN
140 IF NVL(INSTR(l_result, '[' || l_value || ']'), 0) = 0 THEN
141 IF l_result IS NULL THEN
142 l_result := '[' || l_value || ']';
143 ELSE
144 l_result := l_result || ',[' || l_value || ']';
145 END IF;
146 END IF;
147 l_list := SUBSTR(l_list, l_sep_pos + 1);
148 END IF;
149 EXIT WHEN l_value IS NULL or l_sep_pos = 0;
150 END LOOP;
151
152 l_result := REPLACE(REPLACE(l_result, '[',''), ']', '');
153
154 RETURN l_result;
155
156 -- Exception Handler Added for NOCOPY Change (11/08/2002) By ENUNEZ
157 EXCEPTION
158 WHEN OTHERS THEN
159 RAISE;
160 END Remove_Duplicates;
161
162 -- --------------------------------------------------------------------
163 -- PROCEDURE: Create_Workflow_Role
164 -- DESCRIPTION: It creates an Adhoc workflow role based on the user
165 -- list resulting from p_resource_id and p_resource_type.
166 -- The role name (p_wf_role_name) and display name
167 -- (p_wf_role_display_name) could be passed, otherwise
168 -- it will be defaulted thru WF Api.
169 -- --------------------------------------------------------------------
170 PROCEDURE Create_Workflow_Role(
171 p_resource_id IN NUMBER DEFAULT NULL
172 ,p_resource_type IN VARCHAR2 DEFAULT NULL
173 ,p_user_list IN VARCHAR2 DEFAULT NULL
174 ,px_wf_role_name IN OUT NOCOPY VARCHAR2
175 ,px_wf_role_display_name IN OUT NOCOPY VARCHAR2
176 ,p_add_to_list IN VARCHAR2 DEFAULT NULL
177 ) IS
178 l_user_list VARCHAR2(5000);
179 BEGIN
180 IF p_user_list IS NULL THEN
181 Create_User_List(p_resource_id => p_resource_id,
182 p_resource_type => p_resource_type,
183 p_user_list => l_user_list);
184 ELSE
185 l_user_list := p_user_list;
186 END IF;
187 IF l_user_list IS NOT NULL THEN
188 IF p_add_to_list IS NOT NULL THEN
189 l_user_list := l_user_list || ', ' || p_add_to_list;
190 END IF;
191 WF_DIRECTORY.CreateAdHocRole(
192 role_name => px_wf_role_name
193 ,role_display_name => px_wf_role_display_name
194 ,role_users => Remove_Duplicates(l_user_list)
195 ,notification_preference => 'MAILHTML'
196 );
197 ELSIF p_add_to_list IS NOT NULL THEN
198 l_user_list := p_add_to_list;
199 WF_DIRECTORY.CreateAdHocRole(
200 role_name => px_wf_role_name
201 ,role_display_name => px_wf_role_display_name
202 ,role_users => Remove_Duplicates(l_user_list)
203 ,notification_preference => 'MAILHTML'
204 );
205 ELSE
206 -- Nullifies output variables
207 px_wf_role_name := NULL;
208 px_wf_role_display_name := NULL;
209 END IF;
210 -- Exception Handler Added for NOCOPY Change (11/08/2002) By ENUNEZ
211 EXCEPTION
212 WHEN OTHERS THEN
213 RAISE;
214 END Create_Workflow_Role;
215
216 -- --------------------------------------------------------------
217 -- GET CONTENT ITEM ID
218 --
219 -- Used to get content item id from version id
220 --
221 -- --------------------------------------------------------------
222 FUNCTION getCitemId(
223 f_citem_version_id IN NUMBER
224 ) RETURN NUMBER IS
225 CURSOR c_item IS
226 SELECT content_item_id
227 FROM ibc_citem_versions_b
228 WHERE citem_version_id = f_citem_version_id;
229 temp NUMBER;
230 BEGIN
231 open c_item;
232 fetch c_item into temp;
233 if (c_item%NOTFOUND) then
234 close c_item;
235 RETURN null;
236 else
237 close c_item;
238 RETURN temp;
239 end if;
240 END getCitemId;
241
242 -- --------------------------------------------------------------------
243 -- PROCEDURE: Submit_For_Approval
244 -- DESCRIPTION: It launches Content Item Approval Workflow process
245 -- PARAMETERS:
246 -- p_citem_ver_id => Content Item Version ID
247 -- p_object_version_number => Content Item Object Version Number
248 -- p_notes_to_approver => Comments/Notes send to approver(s)
249 -- p_priority => WF Notification priority
250 -- p_callback_URL => URL Link to be shown in the notification
251 -- in order to access the content item
252 -- Some parameters will be replaced in the
253 -- content (parameters are prefixed with an
254 -- Ampersand and all uppercase):
255 -- CITEM_VERSION_ID => Content Item version ID
256 -- ITEM_TYPE => WF Item Type
257 -- ITEM_KEY => WF Item Key
258 -- ACTION_MODE => Action Mode (SUBMITTED,
259 -- APPROVED or REJECTED)
260 -- p_callback_url_description => Description to appear in notification
261 -- p_language => Content Item's Language
262 -- x_wf_item_key => WF item key
263 -- <Default standard API parms>
264 -- --------------------------------------------------------------------
265 PROCEDURE Submit_For_Approval(
266 p_citem_ver_id IN NUMBER
267 ,p_notes_to_approver IN VARCHAR2
268 ,p_priority IN NUMBER
269 ,p_callback_url IN VARCHAR2
270 ,p_callback_url_description IN VARCHAR2
271 ,p_language IN VARCHAR2
272 ,p_commit IN VARCHAR2
273 ,p_api_version IN NUMBER
274 ,p_init_msg_list IN VARCHAR2
275 ,px_object_version_number IN OUT NOCOPY NUMBER
276 ,x_wf_item_key OUT NOCOPY VARCHAR2
277 ,x_return_status OUT NOCOPY VARCHAR2
278 ,x_msg_count OUT NOCOPY NUMBER
279 ,x_msg_data OUT NOCOPY VARCHAR2
280 ) IS
281
282 --******** local variable for standards **********
283 l_api_name CONSTANT VARCHAR2(30) := 'Submit_For_Approval';
284 l_api_version CONSTANT NUMBER := 1.0;
285 l_dummy VARCHAR2(2);
286
287 l_owner_resource_id NUMBER;
288 l_owner_resource_type VARCHAR2(30);
289 l_owner_name VARCHAR2(30);
290 l_version_number NUMBER;
291
292 l_user_list VARCHAR2(4096);
293 l_reply_to VARCHAR2(4096);
294
295 l_creator_id NUMBER;
296 l_wf_role_name VARCHAR2(240);
297 l_wf_role_display_name VARCHAR2(80);
298 l_wf_no_approver_defined VARCHAR2(1);
299 l_already_approved VARCHAR2(1);
300
301 l_content_item_id NUMBER;
302 l_citem_name VARCHAR2(240);
303 l_submitter_name VARCHAR2(240);
304
305 l_format_callback_url VARCHAR2(2000);
306 l_callback_url_description VARCHAR2(2000);
307
308 l_ItemType VARCHAR2(30) := 'IBC_WF';
309 l_ItemKey VARCHAR2(80) := p_citem_ver_id || '@/' || p_language || '/' || TO_CHAR(SYSDATE, 'YYYYMMDD-HH24:MI:SS');
310
311 l_directory_node_id NUMBER;
312 l_directory_path VARCHAR2(4000);
313
314 l_citem_object_type NUMBER := IBC_DATA_SECURITY_PVT.get_object_id('IBC_CONTENT_ITEM');
315 l_directory_object_type NUMBER := IBC_DATA_SECURITY_PVT.get_object_id('IBC_DIRECTORY_NODE');
316
317 CURSOR c_directory(p_content_item_id NUMBER) IS
318 SELECT citem.directory_node_id, dirnode.directory_path
319 FROM ibc_content_items citem,
320 ibc_directory_nodes_b dirnode
321 WHERE citem.content_item_id = p_content_item_id
322 AND citem.directory_node_id = dirnode.directory_node_id;
323
324 -- Cursor to get resource_id and type from current logged-on user.
325 CURSOR c_resource IS
326 SELECT resource_id,
327 DECODE(category ,'EMPLOYEE', 'RS_EMPLOYEE',
328 'PARTNER','RS_PARTNER',
329 'SUPPLIER_CONTACT', 'RS_SUPPLIER' ,
330 'PARTY', 'RS_PARTY' ,
331 'OTHER','RS_OTHER',
332 'TBH', 'RS_TBH') resource_type
333 FROM jtf_rs_resource_extns
334 WHERE user_id = FND_GLOBAL.USER_ID;
335
336 CURSOR c_owner(p_citem_ver_id NUMBER) IS
337 SELECT owner_resource_id,
338 owner_resource_type,
339 civer.version_number,
340 citem.created_by
341 FROM ibc_citem_versions_b civer,
342 ibc_content_items citem
343 WHERE civer.citem_version_id = p_citem_ver_id
344 AND civer.content_item_id = citem.content_item_id;
345
346 CURSOR c_citem_name(p_citem_ver_id NUMBER) IS
347 SELECT content_item_name
348 FROM ibc_citem_versions_tl
349 WHERE citem_version_id = p_citem_ver_id
350 AND language = p_language;
351
352 CURSOR c_user_name(p_user_id IN NUMBER) IS
353 SELECT user_name
354 FROM FND_USER
355 WHERE USER_ID = p_user_id;
356
357 CURSOR c_submitter_name IS
358 SELECT INITCAP(user_name)
359 FROM fnd_user
360 WHERE USER_ID = FND_GLOBAL.USER_ID;
361
362 CURSOR c_component_not_status (p_citem_ver_id IN NUMBER,
363 p_status IN VARCHAR2)
364 IS
365 SELECT 'X'
366 FROM ibc_citem_versions_b a,
367 ibc_compound_relations b,
368 ibc_content_items c
369 WHERE a.citem_version_id = b.citem_version_id
370 AND b.content_item_id = c.content_item_id
371 AND a.citem_version_id = p_citem_ver_id
372 AND c.content_item_status <> p_status;
373
374 BEGIN
375
376 -- ******* Standard Begins ********
377
378 -- Standard call to check for call compatibility.
379 IF NOT FND_API.Compatible_API_Call (
380 l_api_version,
381 p_api_version,
382 l_api_name,
383 G_PKG_NAME)
384 THEN
385 x_return_status := FND_API.G_RET_STS_ERROR;
386 FND_MSG_PUB.ADD;
387 RAISE FND_API.G_EXC_ERROR;
388 END IF;
389 -- Initialize message list if p_init_msg_list is set to TRUE.
390 IF FND_API.to_Boolean( p_init_msg_list ) THEN
391 FND_MSG_PUB.initialize;
392 END IF;
393
394 -- Initialize API return status to success
395 x_return_status := FND_API.G_RET_STS_SUCCESS;
396
397 -- Begin
398
399 -- Validation of Content Item before submitting it for approval
400 IBC_CITEM_ADMIN_GRP.pre_validate_item(
401 p_citem_ver_id => p_citem_ver_id
402 ,x_return_status => x_return_status
403 ,x_msg_count => x_msg_count
404 ,x_msg_data => x_msg_data
405 );
406
407 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
408 RAISE FND_API.G_EXC_ERROR;
409 END IF;
410
411 -- Validation of Components (they need to be approved already)
412 OPEN c_component_not_status(p_citem_ver_id, IBC_UTILITIES_PUB.G_STV_APPROVED);
413 FETCH c_component_not_status INTO l_dummy;
414 IF (c_component_not_status%FOUND) THEN
415 CLOSE c_component_not_status;
416 x_return_status := FND_API.G_RET_STS_ERROR;
417 FND_MESSAGE.Set_Name('IBC', 'COMPONENT_APPROVAL_REQUIRED');
418 FND_MSG_PUB.ADD;
419 RAISE FND_API.G_EXC_ERROR;
420 END IF;
421 CLOSE c_component_not_status;
422
423 l_content_item_id := getCitemId(p_citem_ver_id);
424 OPEN c_directory(l_content_item_id);
425 FETCH c_directory INTO l_directory_node_id, l_directory_path;
426 CLOSE c_directory;
427
428 IF IBC_DATA_SECURITY_PVT.has_permission(
429 p_instance_object_id => l_citem_object_type
430 ,p_instance_pk1_value => l_content_item_id
431 ,p_permission_code => 'CITEM_APPROVE'
432 ,p_container_object_id => l_directory_object_type
433 ,p_container_pk1_value => l_directory_node_id) = FND_API.g_false
434 AND
435 NVL(Fnd_Profile.Value_specific('IBC_USE_ACCESS_CONTROL',-999,-999,-999), 'N') = 'Y'
436 THEN
437 IBC_DATA_SECURITY_PVT.get_grantee_usernames(
438 p_instance_object_id => l_citem_object_type
439 ,p_instance_pk1_value => l_content_item_id
440 ,p_permission_code => 'CITEM_APPROVE'
441 ,p_container_object_id => l_directory_object_type
442 ,p_container_pk1_value => l_directory_node_id
443 ,x_usernames => l_user_list
444 ,x_return_status => x_return_status
445 ,x_msg_count => x_msg_count
446 ,x_msg_data => x_msg_data
447 );
448 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
449 RAISE FND_API.G_EXC_ERROR;
450 END IF;
451 IF l_user_list IS NULL THEN
452 x_return_status := FND_API.G_RET_STS_ERROR;
453 FND_MESSAGE.Set_Name('IBC', 'IBC_NOT_APPROVER_DEFINED');
454 FND_MSG_PUB.ADD;
455 RAISE FND_API.G_EXC_ERROR;
456 ELSE
457 Create_Workflow_Role(
458 p_user_list => l_user_list
459 ,px_wf_role_name => l_wf_role_name
460 ,px_wf_role_display_name => l_wf_role_display_name
461 );
462 END IF;
463 ELSE
464 -- Submitter is Approver or No security Enabled.
465 l_wf_no_approver_defined := 'Y';
466 END IF;
467
468 -- Unlock Content Id
469 IBC_CITEM_ADMIN_GRP.unlock_item(
470 p_content_item_id => l_content_item_id
471 ,p_commit => FND_API.g_false
472 ,p_init_msg_list => FND_API.g_false
473 ,x_return_status => x_return_status
474 ,x_msg_count => x_msg_count
475 ,x_msg_data => x_msg_data
476 );
477
478 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
479 RAISE FND_API.G_EXC_ERROR;
480 END IF;
481
482 -- Approval Will go through Workflow
483 -- Creation of workflow process
484 WF_ENGINE.createProcess( ItemType => l_ItemType,
485 ItemKey => l_ItemKey,
486 process => 'IBC_CITEM_APPROVAL');
487
488 -- Set WF attribute values
489
490 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
491 itemkey => l_Itemkey,
492 aname => 'DIRECTORY_PATH',
493 avalue => l_directory_path);
494
495 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
496 itemkey => l_Itemkey,
497 aname => 'SUBMITTED_BY',
498 avalue => FND_GLOBAL.USER_NAME);
499
500 OPEN c_submitter_name;
501 FETCH c_submitter_name INTO l_submitter_name;
502 IF c_submitter_name%FOUND AND l_submitter_name IS NOT NULL THEN
503 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
504 itemkey => l_Itemkey,
505 aname => 'SUBMITTER_NAME',
506 avalue => l_submitter_name);
507 END IF;
508 CLOSE c_submitter_name;
509
510 l_already_approved := 'N';
511 IF l_wf_no_approver_defined = 'Y' AND
512 NVL(Fnd_Profile.Value_specific('IBC_CUSTOMIZED_APPROVAL_WF',-999,-999,-999), 'N') = 'N'
513 THEN
514 -- If no approver or submitter is approver
515 -- and approval workflow has not been customized
516 -- then change status directly.
517 -- Requiremente driven by PRP, but generalized.
518 IBC_CITEM_ADMIN_GRP.change_status(
519 p_citem_ver_id => p_citem_ver_id
520 ,p_new_status => IBC_UTILITIES_PUB.G_STV_APPROVED
521 ,p_language => p_language
522 ,p_commit => FND_API.g_false
523 ,p_init_msg_list => FND_API.g_false
524 ,px_object_version_number => px_object_version_number
525 ,x_return_status => x_return_status
526 ,x_msg_count => x_msg_count
527 ,x_msg_data => x_msg_data
528 );
529 -- Set ALREADY_APPROVED to Y
530 l_already_approved := 'Y'; -- Fix for bug# 3410110
531 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
532 itemkey => l_Itemkey,
533 aname => 'ALREADY_APPROVED',
534 avalue => 'Y');
535
536 ELSE
537 -- Set Status of Content Item to SUBMITTED
538 px_object_version_number := NVL(px_object_version_number,
539 IBC_CITEM_ADMIN_GRP.getObjVerNum(l_content_item_id));
540 IBC_CITEM_ADMIN_GRP.change_status(
541 p_citem_ver_id => p_citem_ver_id
542 ,p_new_status => IBC_UTILITIES_PUB.G_STV_SUBMIT_FOR_APPROVAL
543 ,p_language => p_language
544 ,p_commit => FND_API.g_false
545 ,p_init_msg_list => FND_API.g_true
546 ,px_object_version_number => px_object_version_number
547 ,x_return_status => x_return_status
548 ,x_msg_count => x_msg_count
549 ,x_msg_data => x_msg_data
550 );
551 -- Set ALREADY_APPROVED to N
552 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
553 itemkey => l_Itemkey,
554 aname => 'ALREADY_APPROVED',
555 avalue => 'N');
556 END IF;
557
558 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
559 RAISE FND_API.G_EXC_ERROR;
560 END IF;
561
562 OPEN c_owner(p_citem_ver_id);
563 FETCH c_owner INTO l_owner_resource_id, l_owner_resource_type, l_version_number, l_creator_id;
564
565 -- Functionality for Approval in case IBC_USE_ACCESS_CONTROL is set to 'N'
566 IF l_wf_no_approver_defined = 'Y' AND
567 NVL(Fnd_Profile.Value_specific('IBC_USE_ACCESS_CONTROL',-999,-999,-999), 'N') = 'N' AND
568 l_owner_resource_id IS NOT NULL AND
569 ((l_owner_resource_type IS NULL AND l_owner_resource_id <> l_creator_id) OR
570 (l_owner_resource_type IS NOT NULL AND
571 IBC_UTILITIES_PVT.check_current_user(NULL,l_owner_resource_id,l_owner_resource_type, l_creator_id) = 'FALSE'))
572 THEN
573 l_wf_no_approver_defined := 'N';
574 IF l_owner_resource_type IS NOT NULL THEN -- Owner is a resource
575 Create_Workflow_Role(
576 p_resource_id => l_owner_resource_id
577 ,p_resource_type => l_owner_resource_type
578 ,px_wf_role_name => l_wf_role_name
579 ,px_wf_role_display_name => l_wf_role_display_name
580 );
581 ELSE -- Owner is a user FND_USER
582 OPEN c_user_name(l_owner_resource_id);
583 FETCH c_user_name INTO l_owner_name;
584 CLOSE c_user_name;
585 Create_Workflow_Role(
586 p_user_list => l_owner_name
587 ,px_wf_role_name => l_wf_role_name
588 ,px_wf_role_display_name => l_wf_role_display_name
589 );
590 END IF;
591 END IF;
592
593 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
594 itemkey => l_Itemkey,
595 aname => 'NO_APPROVER_DEFINED',
596 avalue => l_wf_no_approver_defined);
597
598 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
599 itemkey => l_Itemkey,
600 aname => 'CITEM_APPROVER_ROLE',
601 avalue => l_wf_role_name);
602
603 -- Set REPLY_TO Role Attribute, and CITEM Version Number
604 IF l_already_approved <> 'Y' THEN -- Fix for bug# 3410110
605 l_wf_role_name := NULL;
606 l_wf_role_display_name := NULL;
607 Create_Workflow_Role(
608 p_resource_id => l_owner_resource_id
609 ,p_resource_type => l_owner_resource_type
610 ,px_wf_role_name => l_wf_role_name
611 ,px_wf_role_display_name => l_wf_role_display_name
612 ,p_add_to_list => FND_GLOBAL.USER_NAME
613 );
614 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
615 itemkey => l_Itemkey,
616 aname => 'REPLY_TO',
617 avalue => l_wf_role_name);
618 END IF;
619
620 CLOSE c_owner;
621
622 WF_ENGINE.SetItemAttrNumber( itemtype => l_ItemType,
623 itemkey => l_Itemkey,
624 aname => 'CITEM_VER_ID',
625 avalue => p_citem_ver_id);
626
627 WF_ENGINE.SetItemAttrNumber( itemtype => l_ItemType,
628 itemkey => l_Itemkey,
629 aname => 'CITEM_VERSION_NBR',
630 avalue => l_version_number);
631
632 WF_ENGINE.SetItemAttrNumber( itemtype => l_ItemType,
633 itemkey => l_Itemkey,
634 aname => 'CITEM_OBJECT_VERSION_NUMBER',
635 avalue => px_object_version_number);
636
637 OPEN c_citem_name(p_citem_ver_id);
638 FETCH c_citem_name INTO l_citem_name;
639 IF c_citem_name%FOUND AND l_citem_name IS NOT NULL THEN
640 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
641 itemkey => l_Itemkey,
642 aname => 'CONTENT_ITEM_NAME',
643 avalue => l_citem_name);
644 END IF;
645 CLOSE c_citem_name;
646
647 IF p_notes_to_approver IS NOT NULL THEN
648 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
649 itemkey => l_Itemkey,
650 aname => 'NOTES_TO_APPROVER',
651 avalue => p_notes_to_approver);
652 END IF;
653
654 IF p_priority IS NOT NULL THEN
655 WF_ENGINE.SetItemAttrNumber( itemtype => l_ItemType,
656 itemkey => l_Itemkey,
657 aname => 'PRIORITY',
658 avalue => p_priority);
659 END IF;
660
661 IF p_callback_url IS NOT NULL THEN
662 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
663 itemkey => l_Itemkey,
664 aname => 'UNTOUCHED_CALLBACK_URL',
665 avalue => p_callback_url);
666 -- Replace Info on Callback URL
667 l_format_callback_url := p_callback_url;
668 l_format_callback_url := REPLACE(l_format_callback_url,
669 FND_GLOBAL.Local_Chr(38) || 'CONTENT_ITEM_ID',
670 l_content_item_id);
671 l_format_callback_url := REPLACE(l_format_callback_url,
672 FND_GLOBAL.Local_Chr(38) || 'CITEM_VERSION_NBR',
673 l_version_number);
674 l_format_callback_url := REPLACE(l_format_callback_url,
675 FND_GLOBAL.Local_Chr(38) || 'CITEM_VERSION_ID',
676 p_citem_ver_id);
677 l_format_callback_url := REPLACE(l_format_callback_url,
678 FND_GLOBAL.Local_Chr(38) || 'CONTENT_TYPE_CODE',
679 getContentType(l_content_item_id));
680 l_format_callback_url := REPLACE(l_format_callback_url,
681 FND_GLOBAL.Local_Chr(38) || 'OBJECT_VERSION_NUMBER',
682 px_object_version_number);
683 l_format_callback_url := REPLACE(l_format_callback_url,
684 FND_GLOBAL.Local_Chr(38) || 'CONTENT_ITEM_LANGUAGE',
685 p_language);
686 l_format_callback_url := REPLACE(l_format_callback_url,
687 FND_GLOBAL.Local_Chr(38) || 'ITEM_TYPE',
688 l_ItemType);
689 l_format_callback_url := REPLACE(l_format_callback_url,
690 FND_GLOBAL.Local_Chr(38) || 'ITEM_KEY',
691 l_ItemKey);
692 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
693 itemkey => l_Itemkey,
694 aname => 'ORIGINAL_CALLBACK_URL',
695 avalue => l_format_callback_url);
696
697 l_format_callback_url := REPLACE(l_format_callback_url,
698 FND_GLOBAL.Local_Chr(38) || 'ACTION_MODE',
699 IBC_UTILITIES_PUB.G_STV_SUBMIT_FOR_APPROVAL);
700
701 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
702 itemkey => l_Itemkey,
703 aname => 'CALLBACK_URL',
704 avalue => l_format_callback_url);
705 l_callback_url_description := NVL(p_callback_url_description, l_format_callback_url);
706 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
707 itemkey => l_Itemkey,
708 aname => 'CALLBACK_URL_DESCRIPTION',
709 avalue => l_callback_url_description);
710 END IF;
711
712 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
713 itemkey => l_Itemkey,
714 aname => 'CITEM_LANGUAGE',
715 avalue => p_language);
716
717 -- Start WF Process
718 WF_ENGINE.StartProcess ( ItemType => l_ItemType,
719 ItemKey => l_ItemKey);
720
721 -- If everything is okay so far then set x_wf_item_key
722 x_wf_item_key := l_ItemKey;
723
724 -- COMMIT?
725 IF (p_commit = FND_API.g_true) THEN
726 COMMIT;
727 END IF;
728
729 -- Standard call to get message count and if count=1, get the message
730 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
731 p_data => x_msg_data);
732 EXCEPTION
733 WHEN FND_API.G_EXC_ERROR THEN
734 x_return_status := FND_API.G_RET_STS_ERROR;
735 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
736 p_data => x_msg_data);
737 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
738 P_API_NAME => L_API_NAME
739 ,P_PKG_NAME => G_PKG_NAME
740 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
741 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
742 ,P_SQLCODE => SQLCODE
743 ,P_SQLERRM => SQLERRM
744 ,X_MSG_COUNT => X_MSG_COUNT
745 ,X_MSG_DATA => X_MSG_DATA
746 ,X_RETURN_STATUS => X_RETURN_STATUS
747 );
748 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
750 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
751 p_data => x_msg_data);
752 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
753 P_API_NAME => L_API_NAME
754 ,P_PKG_NAME => G_PKG_NAME
755 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
756 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
757 ,P_SQLCODE => SQLCODE
758 ,P_SQLERRM => SQLERRM
759 ,X_MSG_COUNT => X_MSG_COUNT
760 ,X_MSG_DATA => X_MSG_DATA
761 ,X_RETURN_STATUS => X_RETURN_STATUS
762 );
763 WHEN OTHERS THEN
764 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
765 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
766 THEN
767 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
768 END IF;
769 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
770 p_data => x_msg_data);
771 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
772 P_API_NAME => L_API_NAME
773 ,P_PKG_NAME => G_PKG_NAME
774 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
775 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
776 ,P_SQLCODE => SQLCODE
777 ,P_SQLERRM => SQLERRM
778 ,X_MSG_COUNT => X_MSG_COUNT
779 ,X_MSG_DATA => X_MSG_DATA
780 ,X_RETURN_STATUS => X_RETURN_STATUS
781 );
782 END Submit_For_Approval;
783
784 -- --------------------------------------------------------------------
785 -- PROCEDURE: Approve_Citem_Version
786 -- DESCRIPTION: Procedure to be called from WF to actually perform the
787 -- approval process thru status change API.
788 -- If it's approved succesfully then 'COMPLETE:Y' will be
789 -- returned and callback URL updated, otherwise
790 -- 'COMPLETE:N' will be returned along with error
791 -- stack assigned to 'ERROR_MESSAGE_STACK' WF Attribute.
792 -- (Standard WF API)
793 -- --------------------------------------------------------------------
794 PROCEDURE Approve_Citem_Version(itemtype IN VARCHAR2,
795 itemkey IN VARCHAR2,
796 actid IN NUMBER,
797 funcmode IN VARCHAR2,
798 result IN OUT NOCOPY VARCHAR2) IS
799 l_callback_url VARCHAR2(2000);
800 l_citem_ver_id NUMBER;
801 l_object_version_number NUMBER;
802 l_language VARCHAR2(4);
803 l_return_status VARCHAR2(30);
804 l_msg_count NUMBER;
805 l_msg_data VARCHAR2(2000);
806 l_error_msg_stack VARCHAR2(10000);
807 BEGIN
808 result := '';
809 IF funcmode = 'RUN' THEN
810 result := 'COMPLETE:Y';
811 l_citem_ver_id := WF_ENGINE.GetItemAttrNumber(
812 itemtype => itemtype,
813 itemkey => itemkey,
814 aname => 'CITEM_VER_ID'
815 );
816 l_object_version_number := IBC_CITEM_ADMIN_GRP.getObjVerNum(getCitemId(l_citem_ver_id));
817 l_language := WF_ENGINE.GetItemAttrText(
818 itemtype => itemtype,
819 itemkey => itemkey,
820 aname => 'CITEM_LANGUAGE'
821 );
822
823 IF l_citem_ver_id IS NOT NULL THEN
824
825 IBC_CITEM_ADMIN_GRP.change_status(
826 p_citem_ver_id => l_citem_ver_id
827 ,p_new_status => IBC_UTILITIES_PUB.G_STV_APPROVED
828 ,p_language => l_language
829 ,p_commit => FND_API.g_true
830 ,p_init_msg_list => FND_API.g_true
831 ,px_object_version_number => l_object_version_number
832 ,x_return_status => l_return_status
833 ,x_msg_count => l_msg_count
834 ,x_msg_data => l_msg_data
835 );
836
837 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
838 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
839 itemkey => itemkey,
840 aname => 'APPROVER_NAME',
841 avalue => get_user_description(FND_GLOBAL.user_id));
842 l_callback_url := WF_ENGINE.GetItemAttrText(
843 itemtype => itemtype,
844 itemkey => itemkey,
845 aname => 'ORIGINAL_CALLBACK_URL'
846 );
847 IF l_callback_url IS NOT NULL THEN
848 l_callback_url := REPLACE(l_callback_url,
849 FND_GLOBAL.Local_Chr(38) || 'ACTION_MODE',
850 IBC_UTILITIES_PUB.G_STV_APPROVED);
851 WF_ENGINE.SetItemAttrText(
852 itemtype => itemtype,
853 itemkey => itemkey,
854 aname => 'CALLBACK_URL',
855 avalue => l_callback_url
856 );
857 END IF;
858
859 -- Audit Log Action
860 IBC_AUDIT_LOG_GRP.log_action(
861 p_activity => 'APPROVE'
862 ,p_object_type => IBC_AUDIT_LOG_GRP.G_CITEM_VERSION
863 ,p_object_value1 => l_citem_ver_id
864 ,p_parent_value => getCitemId(l_citem_ver_id)
865 ,p_commit => FND_API.g_true
866 ,p_init_msg_list => FND_API.g_true
867 ,x_return_status => l_return_status
868 ,x_msg_count => l_msg_count
869 ,x_msg_data => l_msg_data
870 );
871 END IF;
872
873 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
874 -- IF l_return_status not successful then return 'N'
875 result := 'COMPLETE:N';
876 IBC_UTILITIES_PVT.Get_Messages (
877 p_message_count => l_msg_count,
878 x_msgs => l_error_msg_stack
879 );
880 l_error_msg_stack := FND_GLOBAL.Newline() || 'CITEM_VER_ID:' || l_citem_ver_id ||
881 ' - Object Version Number:' || l_object_version_number ||
882 FND_GLOBAL.NewLine() || l_error_msg_stack;
883 WF_ENGINE.SetItemAttrText(
884 itemtype => itemtype,
885 itemkey => itemkey,
886 aname => 'ERROR_MESSAGE_STACK',
887 avalue => l_error_msg_stack
888 );
889 END IF;
890 END IF;
891 END IF;
892 RETURN;
893 EXCEPTION
894 WHEN OTHERS THEN
895 RAISE;
896 END Approve_Citem_Version;
897
898 -- --------------------------------------------------------------------
899 -- PROCEDURE: Process_Approval_Response
900 -- DESCRIPTION: Procedure to be called from WF to process the response
901 -- for approval notification request.
902 -- It focuses more on REJECTED response to set callback
903 -- URL
904 -- (Standard WF API)
905 -- --------------------------------------------------------------------
906 PROCEDURE Process_Approval_Response(itemtype IN VARCHAR2,
907 itemkey IN VARCHAR2,
908 actid IN NUMBER,
909 funcmode IN VARCHAR2,
910 result IN OUT NOCOPY VARCHAR2) IS
911 l_callback_url VARCHAR2(2000);
912 l_citem_ver_id NUMBER;
913 l_language VARCHAR2(4);
914 l_return_status VARCHAR2(30);
915 l_msg_count NUMBER;
916 l_msg_data VARCHAR2(2000);
917 l_object_version_number NUMBER;
918 l_response_code VARCHAR2(30);
919 l_error_msg_stack VARCHAR2(10000);
920 l_comments VARCHAR2(10000);
921 BEGIN
922 result := '';
923 IF funcmode IN ('RUN') THEN
924 l_callback_url := WF_ENGINE.GetItemAttrText(
925 itemtype => itemtype,
926 itemkey => itemkey,
927 aname => 'ORIGINAL_CALLBACK_URL'
928 );
929 l_response_code := WF_ENGINE.GetItemAttrText(
930 itemtype => itemtype,
931 itemkey => itemkey,
932 aname => 'RESULT'
933 );
934 IF l_response_code = 'N'
935 THEN
936 l_citem_ver_id := WF_ENGINE.GetItemAttrNumber(
937 itemtype => itemtype,
938 itemkey => itemkey,
939 aname => 'CITEM_VER_ID'
940 );
941 l_object_version_number := IBC_CITEM_ADMIN_GRP.getObjVerNum(getCitemId(l_citem_ver_id));
942 l_language := WF_ENGINE.GetItemAttrText(
943 itemtype => itemtype,
944 itemkey => itemkey,
945 aname => 'CITEM_LANGUAGE'
946 );
947 IBC_CITEM_ADMIN_GRP.change_status(
948 p_citem_ver_id => l_citem_ver_id
949 ,p_new_status => IBC_UTILITIES_PUB.G_STV_REJECTED
950 ,p_language => l_language
951 ,p_commit => FND_API.g_true
952 ,p_init_msg_list => FND_API.g_true
953 ,px_object_version_number => l_object_version_number
954 ,x_return_status => l_return_status
955 ,x_msg_count => l_msg_count
956 ,x_msg_data => l_msg_data
957 );
958
959 IF l_callback_url IS NOT NULL THEN
960 l_callback_url := REPLACE(l_callback_url,
961 FND_GLOBAL.Local_Chr(38) || 'ACTION_MODE',
962 IBC_UTILITIES_PUB.G_STV_REJECTED);
963 WF_ENGINE.SetItemAttrText(
964 itemtype => itemtype,
965 itemkey => itemkey,
966 aname => 'CALLBACK_URL',
967 avalue => l_callback_url
968 );
969
970 END IF;
971
972 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
973 -- Audit Log Action
974 l_comments := WF_ENGINE.GetItemAttrText(
975 itemtype => itemtype,
976 itemkey => itemkey,
977 aname => 'NOTES_TO_SUBMITTER'
978 );
979 IBC_AUDIT_LOG_GRP.log_action(
980 p_activity => 'REJECT'
981 ,p_object_type => IBC_AUDIT_LOG_GRP.G_CITEM_VERSION
982 ,p_object_value1 => l_citem_ver_id
983 ,p_parent_value => getCitemId(l_citem_ver_id)
984 ,p_extra_info1_type => IBC_AUDIT_LOG_GRP.G_EI_CONSTANT
985 ,p_extra_info1_value => l_comments
986 ,p_commit => FND_API.g_true
987 ,p_init_msg_list => FND_API.g_true
988 ,x_return_status => l_return_status
989 ,x_msg_count => l_msg_count
990 ,x_msg_data => l_msg_data
991 );
992 END IF;
993
994 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
995 IBC_UTILITIES_PVT.Get_Messages (
996 p_message_count => l_msg_count,
997 x_msgs => l_error_msg_stack
998 );
999 l_error_msg_stack := FND_GLOBAL.Newline() || 'CITEM_VER_ID:' || l_citem_ver_id ||
1000 ' - Object Version Number:' || l_object_version_number ||
1001 FND_GLOBAL.NewLine() || l_error_msg_stack;
1002 WF_ENGINE.SetItemAttrText(
1003 itemtype => itemtype,
1004 itemkey => itemkey,
1005 aname => 'ERROR_MESSAGE_STACK',
1006 avalue => l_error_msg_stack
1007 );
1008 END IF;
1009
1010 END IF;
1011 END IF;
1012 -- Exception Handler Added for NOCOPY Change (11/08/2002) By ENUNEZ
1013 EXCEPTION
1014 WHEN OTHERS THEN
1015 RAISE;
1016 END Process_Approval_Response;
1017
1018 -- --------------------------------------------------------------------
1019 -- PROCEDURE: Process_Translations
1020 -- DESCRIPTION:
1021 -- --------------------------------------------------------------------
1022 PROCEDURE Process_Translations(itemtype IN VARCHAR2,
1023 itemkey IN VARCHAR2,
1024 actid IN NUMBER,
1025 funcmode IN VARCHAR2,
1026 result IN OUT NOCOPY VARCHAR2) IS
1027 l_citem_ver_id NUMBER;
1028 l_language VARCHAR2(4);
1029 l_content_item_id NUMBER;
1030 l_directory_node_id NUMBER;
1031 l_version_number NUMBER;
1032 l_user_list VARCHAR2(4096);
1033 l_base_language VARCHAR2(4);
1034 l_translation_required VARCHAR2(1);
1035 l_ItemType VARCHAR2(30) := 'IBC_WF';
1036 l_ItemKey VARCHAR2(30);
1037 l_return_status VARCHAR2(30);
1038 l_msg_count NUMBER;
1039 l_msg_data VARCHAR2(2000);
1040 l_error_msg_stack VARCHAR2(10000);
1041 l_wf_role_name VARCHAR2(240);
1042 l_wf_role_display_name VARCHAR2(80);
1043 l_citem_name VARCHAR2(240);
1044 l_format_callback_url VARCHAR2(2000);
1045 CURSOR c_citem_info(p_citem_version_id NUMBER) IS
1046 SELECT ci.content_item_id,
1047 ci.base_language,
1048 ci.translation_required_flag,
1049 ci.directory_node_id,
1050 civ.version_number
1051 FROM ibc_citem_versions_b civ,
1052 ibc_content_items ci
1053 WHERE ci.content_item_id = civ.content_item_id
1054 AND civ.citem_version_id = p_citem_version_id;
1055 CURSOR c_citem_name(p_citem_ver_id NUMBER, p_language VARCHAR2) IS
1056 SELECT content_item_name
1057 FROM ibc_citem_versions_tl
1058 WHERE citem_version_id = p_citem_ver_id
1059 AND language = p_language;
1060
1061 CURSOR c_directory(p_content_item_id NUMBER) IS
1062 SELECT citem.directory_node_id, dirnode.directory_path
1063 FROM ibc_content_items citem,
1064 ibc_directory_nodes_b dirnode
1065 WHERE citem.content_item_id = p_content_item_id
1066 AND citem.directory_node_id = dirnode.directory_node_id;
1067 --l_directory_node_id NUMBER;
1068 l_directory_path VARCHAR2(4000);
1069
1070 BEGIN
1071 result := '';
1072 IF funcmode IN ('RUN') THEN
1073 l_citem_ver_id := WF_ENGINE.GetItemAttrNumber(
1074 itemtype => itemtype,
1075 itemkey => itemkey,
1076 aname => 'CITEM_VER_ID'
1077 );
1078 l_language := WF_ENGINE.GetItemAttrText(
1079 itemtype => itemtype,
1080 itemkey => itemkey,
1081 aname => 'CITEM_LANGUAGE'
1082 );
1083 OPEN c_citem_info(l_citem_ver_id);
1084 FETCH c_citem_info INTO l_content_item_id, l_base_language,
1085 l_translation_required, l_directory_node_id,
1086 l_version_number;
1087 IF l_translation_required = FND_API.g_true
1088 THEN
1089
1090 OPEN c_directory(l_content_item_id);
1091 FETCH c_directory INTO l_directory_node_id, l_directory_path;
1092 CLOSE c_directory;
1093
1094 IBC_DATA_SECURITY_PVT.get_grantee_usernames(
1095 p_instance_object_id => IBC_DATA_SECURITY_PVT.get_object_id('IBC_CONTENT_ITEM')
1096 ,p_instance_pk1_value => l_content_item_id
1097 ,p_permission_code => 'CITEM_TRANSLATE'
1098 ,p_container_object_id => IBC_DATA_SECURITY_PVT.get_object_id('IBC_DIRECTORY_NODE')
1099 ,p_container_pk1_value => l_directory_node_id
1100 ,x_usernames => l_user_list
1101 ,x_return_status => l_return_status
1102 ,x_msg_count => l_msg_count
1103 ,x_msg_data => l_msg_data
1104 );
1105
1106 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1107 result := 'COMPLETE:Y';
1108 IF l_user_list IS NOT NULL THEN
1109 l_ItemKey := l_citem_ver_id || '@T' || TO_CHAR(SYSDATE, 'YYYYMMDD-HH24:MI:SS');
1110
1111 Create_Workflow_Role(
1112 p_user_list => l_user_list
1113 ,px_wf_role_name => l_wf_role_name
1114 ,px_wf_role_display_name => l_wf_role_display_name
1115 );
1116
1117 -- Creation of Translations workflow process
1118 WF_ENGINE.createProcess( ItemType => l_ItemType,
1119 ItemKey => l_ItemKey,
1120 process => 'IBC_CITEM_TRANSLATE');
1121
1122 -- Set Parent Process
1123 WF_ENGINE.set_item_parent(itemtype => l_ItemType,
1124 itemkey => l_ItemKey,
1125 parent_itemtype => itemtype,
1126 parent_itemkey => itemkey,
1127 parent_context => 'Parent Process');
1128
1129 -- Set parameter CITEM_VER_ID
1130 WF_ENGINE.SetItemAttrNumber( itemtype => l_ItemType,
1131 itemkey => l_Itemkey,
1132 aname => 'CITEM_VER_ID',
1133 avalue => l_citem_ver_id);
1134
1135 -- Set parameter CITEM_VERSION_NBR
1136 WF_ENGINE.SetItemAttrNumber( itemtype => l_ItemType,
1137 itemkey => l_Itemkey,
1138 aname => 'CITEM_VERSION_NBR',
1139 avalue => l_version_number);
1140
1141 -- Set parameter CITEM_TRANSLATORS_ROLE
1142 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
1143 itemkey => l_Itemkey,
1144 aname => 'CITEM_TRANSLATORS_ROLE',
1145 avalue => l_wf_role_name);
1146
1147 -- Set parameter CONTENT_ITEM_NAME
1148 OPEN c_citem_name(l_citem_ver_id, l_language);
1149 FETCH c_citem_name INTO l_citem_name;
1150 IF c_citem_name%FOUND AND l_citem_name IS NOT NULL THEN
1151 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
1152 itemkey => l_Itemkey,
1153 aname => 'CONTENT_ITEM_NAME',
1154 avalue => l_citem_name);
1155 END IF;
1156 CLOSE c_citem_name;
1157
1158 -- Set parameter CALLBACK_URL_DESCRIPTION
1159 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
1160 itemkey => l_Itemkey,
1161 aname => 'CALLBACK_URL_DESCRIPTION',
1162 avalue => WF_ENGINE.GetItemAttrText(
1163 itemtype => itemtype,
1164 itemkey => itemkey,
1165 aname => 'CALLBACK_URL_DESCRIPTION'));
1166
1167
1168 -- Replace Info on Callback URL
1169 l_format_callback_url := WF_ENGINE.GetItemAttrText(
1170 itemtype => itemtype,
1171 itemkey => itemkey,
1172 aname => 'UNTOUCHED_CALLBACK_URL'
1173 );
1174 l_format_callback_url := REPLACE(l_format_callback_url,
1175 FND_GLOBAL.Local_Chr(38) || 'CONTENT_ITEM_ID',
1176 l_content_item_id);
1177 l_format_callback_url := REPLACE(l_format_callback_url,
1178 FND_GLOBAL.Local_Chr(38) || 'CITEM_VERSION_NBR',
1179 l_version_number);
1180 l_format_callback_url := REPLACE(l_format_callback_url,
1181 FND_GLOBAL.Local_Chr(38) || 'CONTENT_TYPE_CODE',
1182 getContentType(l_content_item_id));
1183 l_format_callback_url := REPLACE(l_format_callback_url,
1184 FND_GLOBAL.Local_Chr(38) || 'CITEM_VERSION_ID',
1185 l_citem_ver_id);
1186 l_format_callback_url := REPLACE(l_format_callback_url,
1187 FND_GLOBAL.Local_Chr(38) || 'OBJECT_VERSION_NUMBER',
1188 IBC_CITEM_ADMIN_GRP.getobjvernum(l_content_item_id));
1189 l_format_callback_url := REPLACE(l_format_callback_url,
1190 FND_GLOBAL.Local_Chr(38) || 'CONTENT_ITEM_LANGUAGE',
1191 l_language);
1192 l_format_callback_url := REPLACE(l_format_callback_url,
1193 FND_GLOBAL.Local_Chr(38) || 'ITEM_TYPE',
1194 l_ItemType);
1195 l_format_callback_url := REPLACE(l_format_callback_url,
1196 FND_GLOBAL.Local_Chr(38) || 'ITEM_KEY',
1197 l_ItemKey);
1198 l_format_callback_url := REPLACE(l_format_callback_url,
1199 FND_GLOBAL.Local_Chr(38) || 'ACTION_MODE',
1200 'TRANSLATE');
1201 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
1202 itemkey => l_Itemkey,
1203 aname => 'CALLBACK_URL',
1204 avalue => l_format_callback_url);
1205
1206 -- Set directory Path
1207 WF_ENGINE.SetItemAttrText( itemtype => l_ItemType,
1208 itemkey => l_Itemkey,
1209 aname => 'DIRECTORY_PATH',
1210 avalue => l_directory_path);
1211
1212 -- Start WF Process
1213 WF_ENGINE.StartProcess ( ItemType => l_ItemType,
1214 ItemKey => l_ItemKey);
1215
1216 NULL;
1217 END IF;
1218 ELSE
1219 -- IF l_return_status not successful then return 'N'
1220 result := 'COMPLETE:N';
1221 l_error_msg_stack := 'Error During IBC_CITEM_WORKFLOW_PVT.Process_Translations -- ' ||
1222 FND_GLOBAL.Newline() || 'CITEM_VER_ID:' || l_citem_ver_id;
1223 l_error_msg_stack := l_error_msg_stack || FND_GLOBAL.Newline() || 'Return Status: ' ||
1224 l_return_status || FND_GLOBAL.Newline() || FND_GLOBAL.Newline() ;
1225 FOR I IN 1..l_msg_count LOOP
1226 l_error_msg_stack := l_error_msg_stack || ' ... ' ||
1227 FND_MSG_PUB.get(p_encoded => FND_API.g_false) ||
1228 FND_GLOBAL.Newline();
1229 END LOOP;
1230 WF_ENGINE.SetItemAttrText(
1231 itemtype => itemtype,
1232 itemkey => itemkey,
1233 aname => 'ERROR_MESSAGE_STACK',
1234 avalue => l_error_msg_stack
1235 );
1236 END IF;
1237 ELSE
1238 -- IF Translation is not required
1239 result:='COMPLETE:Y';
1240 END IF;
1241 CLOSE c_citem_info;
1242 END IF;
1243 -- Exception Handler Added for NOCOPY Change (11/08/2002) By ENUNEZ
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246 RAISE;
1247 END Process_Translations;
1248
1249
1250 -- --------------------------------------------------------------------
1251 -- PROCEDURE: Respond_Approval_Notification
1252 -- DESCRIPTION: Responds approval notification request, and optionally
1253 -- pass notes/comments to submitter.
1254 -- PARAMETERS:
1255 -- p_item_type => WF item type
1256 -- p_item_key => WF item key
1257 -- p_activity => WF Activity
1258 -- p_response => Response to Notification (either Y or N)
1259 -- p_notes_to_submitter => Notes/Comments to Submitter.
1260 -- <Default standard API parms>
1261 -- --------------------------------------------------------------------
1262 PROCEDURE Respond_Approval_Notification(
1263 p_item_type IN VARCHAR2
1264 ,p_item_key IN VARCHAR2
1265 ,p_activity IN VARCHAR2
1266 ,p_response IN VARCHAR2
1267 ,p_notes_to_submitter IN VARCHAR2
1268 ,p_commit IN VARCHAR2
1269 ,p_api_version IN NUMBER
1270 ,p_init_msg_list IN VARCHAR2
1271 ,x_return_status OUT NOCOPY VARCHAR2
1272 ,x_msg_count OUT NOCOPY NUMBER
1273 ,x_msg_data OUT NOCOPY VARCHAR2
1274 ) IS
1275 --******** local variable for standards **********
1276 l_api_name CONSTANT VARCHAR2(30) := 'Respond_Approval_Notification';
1277 l_api_version CONSTANT NUMBER := 1.0;
1278 l_dummy VARCHAR2(2);
1279 CURSOR c_chk_notification IS
1280 SELECT 'X'
1281 FROM ibc_pending_approvals_v
1282 WHERE item_key = p_item_key;
1283 BEGIN
1284 -- ******* Standard Begins ********
1285
1286 -- Standard call to check for call compatibility.
1287 IF NOT FND_API.Compatible_API_Call (
1288 l_api_version,
1289 p_api_version,
1290 l_api_name,
1291 G_PKG_NAME)
1292 THEN
1293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1294 END IF;
1295 -- Initialize message list if p_init_msg_list is set to TRUE.
1296 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1297 FND_MSG_PUB.initialize;
1298 END IF;
1299
1300 -- Initialize API return status to success
1301 x_return_status := FND_API.G_RET_STS_SUCCESS;
1302
1303 -- Begin
1304
1305 OPEN c_chk_notification;
1306 FETCH c_chk_notification INTO l_dummy;
1307 IF c_chk_notification%NOTFOUND THEN
1308 CLOSE c_chk_notification;
1309 x_return_status := FND_API.G_RET_STS_ERROR;
1310 FND_MESSAGE.Set_Name('IBC', 'IBC_NOTIF_ALREADY_RESPONDED');
1311 FND_MSG_PUB.ADD;
1312 RAISE FND_API.G_EXC_ERROR;
1313 END IF;
1314 CLOSE c_chk_notification;
1315
1316 IF p_response IN ('Y', 'N') THEN
1317 WF_ENGINE.SetItemAttrText( itemtype => p_item_type,
1318 itemkey => p_item_key,
1319 aname => 'APPROVER_NAME',
1320 avalue => get_user_description(FND_GLOBAL.user_id));
1321 IF p_notes_to_submitter IS NOT NULL THEN
1322 WF_ENGINE.SetItemAttrText( itemtype => p_item_type,
1323 itemkey => p_item_key,
1324 aname => 'NOTES_TO_SUBMITTER',
1325 avalue => p_notes_to_submitter);
1326 END IF;
1327 WF_ENGINE.SetItemAttrText( itemtype => p_item_type,
1328 itemkey => p_item_key,
1329 aname => 'RESULT',
1330 avalue => p_response);
1331 WF_ENGINE.CompleteActivity(p_item_type,
1332 p_item_key,
1333 p_activity,
1334 p_response);
1335 ELSE
1336 x_return_status := FND_API.G_RET_STS_ERROR;
1337 FND_MESSAGE.Set_Name('IBC', 'BAD_INPUT_VALUE');
1338 FND_MESSAGE.Set_Token('INPUT', 'p_response', FALSE);
1339 FND_MSG_PUB.ADD;
1340 RAISE FND_API.G_EXC_ERROR;
1341 END IF;
1342
1343 -- COMMIT?
1344 IF (p_commit = FND_API.g_true) THEN
1345 COMMIT;
1346 END IF;
1347
1348 -- Standard call to get message count and if count=1, get the message
1349 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1350 p_data => x_msg_data);
1351 EXCEPTION
1352 WHEN FND_API.G_EXC_ERROR THEN
1353 x_return_status := FND_API.G_RET_STS_ERROR;
1354 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1355 p_data => x_msg_data);
1356 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1357 P_API_NAME => L_API_NAME
1358 ,P_PKG_NAME => G_PKG_NAME
1359 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1360 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1361 ,P_SQLCODE => SQLCODE
1362 ,P_SQLERRM => SQLERRM
1363 ,X_MSG_COUNT => X_MSG_COUNT
1364 ,X_MSG_DATA => X_MSG_DATA
1365 ,X_RETURN_STATUS => X_RETURN_STATUS
1366 );
1367 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1369 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1370 p_data => x_msg_data);
1371 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1372 P_API_NAME => L_API_NAME
1373 ,P_PKG_NAME => G_PKG_NAME
1374 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1375 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1376 ,P_SQLCODE => SQLCODE
1377 ,P_SQLERRM => SQLERRM
1378 ,X_MSG_COUNT => X_MSG_COUNT
1379 ,X_MSG_DATA => X_MSG_DATA
1380 ,X_RETURN_STATUS => X_RETURN_STATUS
1381 );
1382 WHEN OTHERS THEN
1383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1384 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1385 THEN
1386 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1387 END IF;
1388 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1389 p_data => x_msg_data);
1390 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1391 P_API_NAME => L_API_NAME
1392 ,P_PKG_NAME => G_PKG_NAME
1393 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
1394 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1395 ,P_SQLCODE => SQLCODE
1396 ,P_SQLERRM => SQLERRM
1397 ,X_MSG_COUNT => X_MSG_COUNT
1398 ,X_MSG_DATA => X_MSG_DATA
1399 ,X_RETURN_STATUS => X_RETURN_STATUS
1400 );
1401 END Respond_Approval_Notification;
1402
1403 -- --------------------------------------------------------------------
1404 -- PROCEDURE: Close_Translation_Request
1405 -- DESCRIPTION: Closes Translation Request from inbox
1406 -- PARAMETERS:
1407 -- p_item_type => WF item type
1408 -- p_item_key => WF item key
1409 -- <Default standard API parms>
1410 -- --------------------------------------------------------------------
1411 PROCEDURE Close_Translation_Request(
1412 p_item_type IN VARCHAR2
1413 ,p_item_key IN VARCHAR2
1414 ,p_commit IN VARCHAR2
1415 ,p_api_version IN NUMBER
1416 ,p_init_msg_list IN VARCHAR2
1417 ,x_return_status OUT NOCOPY VARCHAR2
1418 ,x_msg_count OUT NOCOPY NUMBER
1419 ,x_msg_data OUT NOCOPY VARCHAR2
1420 ) IS
1421 --******** local variable for standards **********
1422 l_api_name CONSTANT VARCHAR2(30) := 'Close_Translation_Request';
1423 l_api_version CONSTANT NUMBER := 1.0;
1424 BEGIN
1425 -- ******* Standard Begins ********
1426
1427 -- Standard call to check for call compatibility.
1428 IF NOT FND_API.Compatible_API_Call (
1429 l_api_version,
1430 p_api_version,
1431 l_api_name,
1432 G_PKG_NAME)
1433 THEN
1434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1435 END IF;
1436 -- Initialize message list if p_init_msg_list is set to TRUE.
1437 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1438 FND_MSG_PUB.initialize;
1439 END IF;
1440
1441 -- Initialize API return status to success
1442 x_return_status := FND_API.G_RET_STS_SUCCESS;
1443
1444 -- Begin
1445
1446 WF_ENGINE.CompleteActivity(p_item_type,
1447 p_item_key,
1448 'IBC_CITEM_TRANSLATE_REQ',
1449 NULL);
1450
1451 -- COMMIT?
1452 IF (p_commit = FND_API.g_true) THEN
1453 COMMIT;
1454 END IF;
1455
1456 -- Standard call to get message count and if count=1, get the message
1457 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1458 p_data => x_msg_data);
1459 EXCEPTION
1460 WHEN FND_API.G_EXC_ERROR THEN
1461 x_return_status := FND_API.G_RET_STS_ERROR;
1462 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1463 p_data => x_msg_data);
1464 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1465 P_API_NAME => L_API_NAME
1466 ,P_PKG_NAME => G_PKG_NAME
1467 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1468 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1469 ,P_SQLCODE => SQLCODE
1470 ,P_SQLERRM => SQLERRM
1471 ,X_MSG_COUNT => X_MSG_COUNT
1472 ,X_MSG_DATA => X_MSG_DATA
1473 ,X_RETURN_STATUS => X_RETURN_STATUS
1474 );
1475 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1477 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1478 p_data => x_msg_data);
1479 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1480 P_API_NAME => L_API_NAME
1481 ,P_PKG_NAME => G_PKG_NAME
1482 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1483 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1484 ,P_SQLCODE => SQLCODE
1485 ,P_SQLERRM => SQLERRM
1486 ,X_MSG_COUNT => X_MSG_COUNT
1487 ,X_MSG_DATA => X_MSG_DATA
1488 ,X_RETURN_STATUS => X_RETURN_STATUS
1489 );
1490 WHEN OTHERS THEN
1491 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1492 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1493 THEN
1494 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1495 END IF;
1496 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1497 p_data => x_msg_data);
1498 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1499 P_API_NAME => L_API_NAME
1500 ,P_PKG_NAME => G_PKG_NAME
1501 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
1502 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1503 ,P_SQLCODE => SQLCODE
1504 ,P_SQLERRM => SQLERRM
1505 ,X_MSG_COUNT => X_MSG_COUNT
1506 ,X_MSG_DATA => X_MSG_DATA
1507 ,X_RETURN_STATUS => X_RETURN_STATUS
1508 );
1509 END Close_Translation_Request;
1510
1511
1512 -- --------------------------------------------------------------------
1513 -- PROCEDURE: Get_Pending_Approvals
1514 -- DESCRIPTION: Fetch all notifications (for current user) associated
1515 -- to Content Manager and with the format set by Submit_for_Approval
1516 -- PARAMETERS:
1517 -- - x_citem_version_ids Table of content item version ids
1518 -- - x_wf_item_keys Table of Workflow Item Keys, these
1519 -- values can be used to respond (Approve
1520 -- or Reject) notifications calling
1521 -- Respond_Approval_Notification
1522 -- <Default standard API parms>
1523 -- --------------------------------------------------------------------
1524 PROCEDURE Get_Pending_Approvals(
1525 x_citem_version_ids OUT NOCOPY jtf_number_table
1526 ,x_wf_item_keys OUT NOCOPY jtf_varchar2_table_100
1527 ,p_api_version IN NUMBER
1528 ,p_init_msg_list IN VARCHAR2
1529 ,x_return_status OUT NOCOPY VARCHAR2
1530 ,x_msg_count OUT NOCOPY NUMBER
1531 ,x_msg_data OUT NOCOPY VARCHAR2
1532 ) IS
1533
1534 l_index NUMBER;
1535
1536 -- Cursor to obtain notifications associated to Content Manager
1537 -- for current user and which item key has the format followed by
1538 -- Submit_For_Approval proc.
1539 CURSOR c_notifications IS
1540 SELECT *
1541 FROM IBC_PENDING_APPROVALS_V
1542 WHERE USER_NAME = FND_GLOBAL.USER_NAME;
1543
1544 --******** local variable for standards **********
1545 l_api_name CONSTANT VARCHAR2(30) := 'Get_Pending_Approvals';
1546 l_api_version CONSTANT NUMBER := 1.0;
1547
1548 TYPE t_citem_version_id_tbl IS TABLE OF NUMBER
1549 INDEX BY BINARY_INTEGER;
1550 TYPE t_wf_item_key_tbl IS TABLE OF VARCHAR2(100)
1551 INDEX BY BINARY_INTEGER;
1552 l_citem_version_id_tbl t_citem_version_id_tbl;
1553 l_wf_item_key_tbl t_wf_item_key_tbl;
1554
1555 BEGIN
1556 -- ******* Standard Begins ********
1557
1558 -- Standard call to check for call compatibility.
1559 IF NOT FND_API.Compatible_API_Call (
1560 l_api_version,
1561 p_api_version,
1562 l_api_name,
1563 G_PKG_NAME)
1564 THEN
1565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1566 END IF;
1567 -- Initialize message list if p_init_msg_list is set to TRUE.
1568 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1569 FND_MSG_PUB.initialize;
1570 END IF;
1571
1572 -- Initialize API return status to success
1573 x_return_status := FND_API.G_RET_STS_SUCCESS;
1574
1575 -- Begin
1576 l_index := 0;
1577 FOR r_notification IN c_notifications LOOP
1578 l_index := l_index + 1;
1579 l_citem_version_id_tbl(l_index) := r_notification.citem_version_id;
1580 l_wf_item_key_tbl(l_index) := r_notification.item_key;
1581 END LOOP;
1582
1583 IF l_index > 0 THEN
1584 x_citem_version_ids := JTF_NUMBER_TABLE();
1585 x_citem_version_ids.EXTEND(l_index);
1586 x_wf_item_keys := JTF_VARCHAR2_TABLE_100();
1587 x_wf_item_keys.EXTEND(l_index);
1588 FOR I IN 1..l_index LOOP
1589 x_citem_version_ids(I) := l_citem_version_id_tbl(I);
1590 x_wf_item_keys(I) := l_wf_item_key_tbl(I);
1591 END LOOP;
1592 END IF;
1593
1594 -- Standard call to get message count and if count=1, get the message
1595 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1596 p_data => x_msg_data);
1597 EXCEPTION
1598 WHEN FND_API.G_EXC_ERROR THEN
1599 x_return_status := FND_API.G_RET_STS_ERROR;
1600 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1601 p_data => x_msg_data);
1602 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1603 P_API_NAME => L_API_NAME
1604 ,P_PKG_NAME => G_PKG_NAME
1605 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1606 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1607 ,P_SQLCODE => SQLCODE
1608 ,P_SQLERRM => SQLERRM
1609 ,X_MSG_COUNT => X_MSG_COUNT
1610 ,X_MSG_DATA => X_MSG_DATA
1611 ,X_RETURN_STATUS => X_RETURN_STATUS
1612 );
1613 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1614 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1615 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1616 p_data => x_msg_data);
1617 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1618 P_API_NAME => L_API_NAME
1619 ,P_PKG_NAME => G_PKG_NAME
1620 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1621 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1622 ,P_SQLCODE => SQLCODE
1623 ,P_SQLERRM => SQLERRM
1624 ,X_MSG_COUNT => X_MSG_COUNT
1625 ,X_MSG_DATA => X_MSG_DATA
1626 ,X_RETURN_STATUS => X_RETURN_STATUS
1627 );
1628 WHEN OTHERS THEN
1629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1630 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1631 THEN
1632 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1633 END IF;
1634 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1635 p_data => x_msg_data);
1636 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1637 P_API_NAME => L_API_NAME
1638 ,P_PKG_NAME => G_PKG_NAME
1639 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
1640 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1641 ,P_SQLCODE => SQLCODE
1642 ,P_SQLERRM => SQLERRM
1643 ,X_MSG_COUNT => X_MSG_COUNT
1644 ,X_MSG_DATA => X_MSG_DATA
1645 ,X_RETURN_STATUS => X_RETURN_STATUS
1646 );
1647 END Get_Pending_Approvals;
1648
1649 -- --------------------------------------------------------------------
1650 -- PROCEDURE: Get_Pending_Translations
1651 -- DESCRIPTION: Fetch all notifications (for current user) associated
1652 -- to Content Manager and for translation requests
1653 -- PARAMETERS:
1654 -- - x_citem_version_ids Table of content item version ids
1655 -- - x_wf_item_keys Table of Workflow Item Keys, these
1656 -- values can be used to close notifications
1657 -- calling close_fyi_notification
1658 -- <Default standard API parms>
1659 -- --------------------------------------------------------------------
1660 PROCEDURE Get_Pending_Translations(
1661 x_citem_version_ids OUT NOCOPY jtf_number_table
1662 ,x_wf_item_keys OUT NOCOPY jtf_varchar2_table_100
1663 ,p_api_version IN NUMBER
1664 ,p_init_msg_list IN VARCHAR2
1665 ,x_return_status OUT NOCOPY VARCHAR2
1666 ,x_msg_count OUT NOCOPY NUMBER
1667 ,x_msg_data OUT NOCOPY VARCHAR2
1668 ) IS
1669
1670 l_index NUMBER;
1671
1672 -- Cursor to obtain notifications associated to Content Manager
1673 -- for current user and which item key has the format followed by
1674 -- Submit_For_Approval proc.
1675 CURSOR c_notifications IS
1676 SELECT *
1677 FROM IBC_PENDING_TRANSLATIONS_V
1678 WHERE USER_NAME = FND_GLOBAL.USER_NAME;
1679
1680 --******** local variable for standards **********
1681 l_api_name CONSTANT VARCHAR2(30) := 'Get_Pending_Translations';
1682 l_api_version CONSTANT NUMBER := 1.0;
1683
1684 TYPE t_citem_version_id_tbl IS TABLE OF NUMBER
1685 INDEX BY BINARY_INTEGER;
1686 TYPE t_wf_item_key_tbl IS TABLE OF VARCHAR2(100)
1687 INDEX BY BINARY_INTEGER;
1688 l_citem_version_id_tbl t_citem_version_id_tbl;
1689 l_wf_item_key_tbl t_wf_item_key_tbl;
1690
1691 BEGIN
1692 -- ******* Standard Begins ********
1693
1694 -- Standard call to check for call compatibility.
1695 IF NOT FND_API.Compatible_API_Call (
1696 l_api_version,
1697 p_api_version,
1698 l_api_name,
1699 G_PKG_NAME)
1700 THEN
1701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1702 END IF;
1703 -- Initialize message list if p_init_msg_list is set to TRUE.
1704 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1705 FND_MSG_PUB.initialize;
1706 END IF;
1707
1708 -- Initialize API return status to success
1709 x_return_status := FND_API.G_RET_STS_SUCCESS;
1710
1711 -- Begin
1712 l_index := 0;
1713 FOR r_notification IN c_notifications LOOP
1714 l_index := l_index + 1;
1715 l_citem_version_id_tbl(l_index) := r_notification.citem_version_id;
1716 l_wf_item_key_tbl(l_index) := r_notification.item_key;
1717 END LOOP;
1718
1719 IF l_index > 0 THEN
1720 x_citem_version_ids := JTF_NUMBER_TABLE();
1721 x_citem_version_ids.EXTEND(l_index);
1722 x_wf_item_keys := JTF_VARCHAR2_TABLE_100();
1723 x_wf_item_keys.EXTEND(l_index);
1724 FOR I IN 1..l_index LOOP
1725 x_citem_version_ids(I) := l_citem_version_id_tbl(I);
1726 x_wf_item_keys(I) := l_wf_item_key_tbl(I);
1727 END LOOP;
1728 END IF;
1729
1730 -- Standard call to get message count and if count=1, get the message
1731 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1732 p_data => x_msg_data);
1733 EXCEPTION
1734 WHEN FND_API.G_EXC_ERROR THEN
1735 x_return_status := FND_API.G_RET_STS_ERROR;
1736 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1737 p_data => x_msg_data);
1738 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1739 P_API_NAME => L_API_NAME
1740 ,P_PKG_NAME => G_PKG_NAME
1741 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1742 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1743 ,P_SQLCODE => SQLCODE
1744 ,P_SQLERRM => SQLERRM
1745 ,X_MSG_COUNT => X_MSG_COUNT
1746 ,X_MSG_DATA => X_MSG_DATA
1747 ,X_RETURN_STATUS => X_RETURN_STATUS
1748 );
1749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1750 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1751 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1752 p_data => x_msg_data);
1753 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1754 P_API_NAME => L_API_NAME
1755 ,P_PKG_NAME => G_PKG_NAME
1756 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1757 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1758 ,P_SQLCODE => SQLCODE
1759 ,P_SQLERRM => SQLERRM
1760 ,X_MSG_COUNT => X_MSG_COUNT
1761 ,X_MSG_DATA => X_MSG_DATA
1762 ,X_RETURN_STATUS => X_RETURN_STATUS
1763 );
1764 WHEN OTHERS THEN
1765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1766 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1767 THEN
1768 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1769 END IF;
1770 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1771 p_data => x_msg_data);
1772 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1773 P_API_NAME => L_API_NAME
1774 ,P_PKG_NAME => G_PKG_NAME
1775 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
1776 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
1777 ,P_SQLCODE => SQLCODE
1778 ,P_SQLERRM => SQLERRM
1779 ,X_MSG_COUNT => X_MSG_COUNT
1780 ,X_MSG_DATA => X_MSG_DATA
1781 ,X_RETURN_STATUS => X_RETURN_STATUS
1782 );
1783 END Get_Pending_Translations;
1784
1785 -- --------------------------------------------------------------------
1786 -- PROCEDURE: IBC_CITEM_WORKFLOW_PVT.Submit_For_Trans_Approval
1787 -- DESCRIPTION: It launches Content Item Translation Approval Workflow process
1788 -- PARAMETERS:
1789 -- p_citem_ver_id => Content Item Version ID
1790 -- p_object_version_number => Content Item Object Version Number
1791 -- p_notes_to_approver => Comments/Notes send to approver(s)
1792 -- p_priority => WF Notification priority
1793 -- p_callback_URL => URL Link to be shown in the notification
1794 -- in order to access the content item
1795 -- Some parameters will be replaced in the
1796 -- content (parameters are prefixed with an
1797 -- Ampersand and all uppercase):
1798 -- CITEM_VERSION_ID => Content Item version ID
1799 -- ITEM_TYPE => WF Item Type
1800 -- ITEM_KEY => WF Item Key
1801 -- ACTION_MODE => Action Mode (SUBMITTED,
1802 -- APPROVED or REJECTED)
1803 -- p_callback_url_description => Description to appear in notification
1804 -- p_language => Content Item's Language
1805 -- x_wf_item_key => WF item key
1806 -- <Default standard API parms>
1807 -- --------------------------------------------------------------------
1808 PROCEDURE Submit_For_Trans_Approval(
1809 p_citem_ver_id IN NUMBER
1810 ,p_notes_to_approver IN VARCHAR2
1811 ,p_priority IN NUMBER
1812 ,p_callback_url IN VARCHAR2
1813 ,p_callback_url_description IN VARCHAR2
1814 ,p_language IN VARCHAR2
1815 ,p_commit IN VARCHAR2
1816 ,p_api_version IN NUMBER
1817 ,p_init_msg_list IN VARCHAR2
1818 ,px_object_version_number IN OUT NOCOPY NUMBER
1819 ,x_wf_item_key OUT NOCOPY VARCHAR2
1820 ,x_return_status OUT NOCOPY VARCHAR2
1821 ,x_msg_count OUT NOCOPY NUMBER
1822 ,x_msg_data OUT NOCOPY VARCHAR2
1823 ) IS
1824
1825 --******** local variable for standards **********
1826 l_api_name CONSTANT VARCHAR2(30) := 'Submit_For_Trans_Approval';
1827 l_api_version CONSTANT NUMBER := 1.0;
1828 l_dummy VARCHAR2(2);
1829
1830 l_owner_resource_id NUMBER;
1831 l_owner_resource_type VARCHAR2(30);
1832 l_owner_name VARCHAR2(30);
1833 l_version_number NUMBER;
1834
1835 l_user_list VARCHAR2(4096);
1836 l_reply_to VARCHAR2(4096);
1837
1838 l_creator_id NUMBER;
1839 l_wf_role_name VARCHAR2(240);
1840 l_wf_role_display_name VARCHAR2(80);
1841 l_wf_no_approver_defined VARCHAR2(1);
1842
1843 l_content_item_id NUMBER;
1844 l_citem_name VARCHAR2(240);
1845 l_submitter_name VARCHAR2(240);
1846
1847 l_format_callback_url VARCHAR2(2000);
1848 l_callback_url_description VARCHAR2(2000);
1849
1850 -- Initialize the Workflow Item Type and Key
1851 l_ItemType VARCHAR2(30) := 'IBC_WF';
1852 --l_ItemKey VARCHAR2(30) := p_citem_ver_id || '@TA' || TO_CHAR(SYSDATE, 'YYYYMMDD-HH24:MI:SS');
1853 l_ItemKey VARCHAR2(30) := p_citem_ver_id||'@TA/'||p_language||TO_CHAR(SYSDATE, '/YYYYMMDD-HH24:MI:SS');
1854
1855 l_directory_node_id NUMBER;
1856 l_directory_path VARCHAR2(4000);
1857
1858 l_citem_object_type NUMBER := IBC_DATA_SECURITY_PVT.get_object_id('IBC_CONTENT_ITEM');
1859 l_directory_object_type NUMBER := IBC_DATA_SECURITY_PVT.get_object_id('IBC_DIRECTORY_NODE');
1860
1861 CURSOR c_directory(p_content_item_id NUMBER) IS
1862 SELECT citem.directory_node_id, dirnode.directory_path
1863 FROM ibc_content_items citem,
1864 ibc_directory_nodes_b dirnode
1865 WHERE citem.content_item_id = p_content_item_id
1866 AND citem.directory_node_id = dirnode.directory_node_id;
1867
1868
1869 -- Cursor to get resource_id and type from current logged-on user.
1870 CURSOR c_resource IS
1871 SELECT resource_id
1872 ,DECODE(category ,'EMPLOYEE', 'RS_EMPLOYEE'
1873 ,'PARTNER','RS_PARTNER'
1874 ,'SUPPLIER_CONTACT', 'RS_SUPPLIER'
1875 ,'PARTY', 'RS_PARTY'
1876 ,'OTHER','RS_OTHER'
1877 ,'TBH', 'RS_TBH') resource_type
1878 FROM jtf_rs_resource_extns
1879 WHERE user_id = FND_GLOBAL.USER_ID;
1880
1881 CURSOR c_owner(p_citem_ver_id NUMBER) IS
1882 SELECT CITEM.owner_resource_id
1883 ,CITEM.owner_resource_type
1884 ,CIVER.version_number
1885 ,CITEM.created_by
1886 FROM ibc_citem_versions_b CIVER
1887 ,ibc_content_items CITEM
1888 WHERE CIVER.citem_version_id = p_citem_ver_id
1889 AND CIVER.content_item_id = CITEM.content_item_id;
1890
1891 CURSOR c_citem_name(p_citem_ver_id NUMBER) IS
1892 SELECT content_item_name
1893 FROM ibc_citem_versions_tl
1894 WHERE citem_version_id = p_citem_ver_id
1895 AND language = p_language;
1896
1897 CURSOR c_user_name(p_user_id IN NUMBER) IS
1898 SELECT user_name
1899 FROM FND_USER
1900 WHERE USER_ID = p_user_id;
1901
1902 CURSOR c_submitter_name IS
1903 SELECT INITCAP(user_name)
1904 FROM fnd_user
1905 WHERE USER_ID = FND_GLOBAL.USER_ID;
1906
1907 CURSOR c_component_not_status (p_citem_ver_id IN NUMBER,
1908 p_status IN VARCHAR2)
1909 IS
1910 SELECT 'X'
1911 FROM ibc_citem_versions_b a
1912 ,ibc_compound_relations b
1913 ,ibc_content_items c
1914 WHERE a.citem_version_id = b.citem_version_id
1915 AND b.content_item_id = c.content_item_id
1916 AND a.citem_version_id = p_citem_ver_id
1917 AND c.content_item_status <> p_status;
1918
1919 l_language_description VARCHAR2(255);
1920
1921 BEGIN
1922
1923 -- ******* Standard Begins ********
1924
1925 -- Standard call to check for call compatibility.
1926 IF NOT FND_API.Compatible_API_Call (
1927 l_api_version,
1928 p_api_version,
1929 l_api_name,
1930 G_PKG_NAME)
1931 THEN
1932 x_return_status := FND_API.G_RET_STS_ERROR;
1933 FND_MSG_PUB.ADD;
1934 RAISE FND_API.G_EXC_ERROR;
1935 END IF;
1936 -- Initialize message list if p_init_msg_list is set to TRUE.
1937 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1938 FND_MSG_PUB.initialize;
1939 END IF;
1940
1941 -- Initialize API return status to success
1942 x_return_status := FND_API.G_RET_STS_SUCCESS;
1943
1944 -- Begin
1945
1946
1947 l_content_item_id := getCitemId(p_citem_ver_id);
1948
1949 OPEN c_directory(l_content_item_id);
1950 FETCH c_directory INTO l_directory_node_id, l_directory_path;
1951 CLOSE c_directory;
1952
1953
1954 IF IBC_DATA_SECURITY_PVT.has_permission(
1955 p_instance_object_id => l_citem_object_type
1956 ,p_instance_pk1_value => l_content_item_id
1957 ,p_permission_code => 'CITEM_APPROVE_TRANSLATE'
1958 ,p_container_object_id => l_directory_object_type
1959 ,p_container_pk1_value => l_directory_node_id) = FND_API.g_false
1960 AND
1961 NVL(Fnd_Profile.Value_specific('IBC_USE_ACCESS_CONTROL',-999,-999,-999), 'N') = 'Y'
1962 THEN
1963 IBC_DATA_SECURITY_PVT.get_grantee_usernames(
1964 p_instance_object_id => l_citem_object_type
1965 ,p_instance_pk1_value => l_content_item_id
1966 ,p_permission_code => 'CITEM_APPROVE_TRANSLATE'
1967 ,p_container_object_id => l_directory_object_type
1968 ,p_container_pk1_value => l_directory_node_id
1969 ,x_usernames => l_user_list
1970 ,x_return_status => x_return_status
1971 ,x_msg_count => x_msg_count
1972 ,x_msg_data => x_msg_data
1973 );
1974 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1975 RAISE FND_API.G_EXC_ERROR;
1976 END IF;
1977
1978 IF l_user_list IS NULL THEN
1979 x_return_status := FND_API.G_RET_STS_ERROR;
1980 FND_MESSAGE.Set_Name('IBC', 'IBC_NOT_APPROVER_DEFINED');
1981 FND_MSG_PUB.ADD;
1982 RAISE FND_API.G_EXC_ERROR;
1983 ELSE
1984 Create_Workflow_Role(p_user_list => l_user_list
1985 ,px_wf_role_name => l_wf_role_name
1986 ,px_wf_role_display_name => l_wf_role_display_name
1987 );
1988 END IF;
1989 ELSE
1990 -- Current user/Submitter is approver or if security is disabled
1991 l_wf_no_approver_defined := 'Y';
1992 END IF;
1993
1994 px_object_version_number := NVL(px_object_version_number
1995 ,IBC_CITEM_ADMIN_GRP.getObjVerNum(l_content_item_id)
1996 );
1997
1998
1999 --======================================================================
2000 --======================================================================
2001 -- If no approver defined and the the WF profile is disabled then
2002 -- directly approve the version without invoking the WF.
2003 --======================================================================
2004 --======================================================================
2005
2006 IF l_wf_no_approver_defined = 'Y' AND
2007 NVL(Fnd_Profile.Value_specific('IBC_CUSTOMIZED_APPROVAL_WF',-999,-999,-999), 'N') = 'N'
2008 THEN
2009 -- Set Status of Content Item Version
2010 IBC_CITEM_ADMIN_GRP.Change_Translation_Status(
2011 p_citem_ver_id => p_citem_ver_id
2012 ,p_new_status => IBC_UTILITIES_PUB.G_STV_APPROVED -- Change the status to approve
2013 ,p_language => p_language
2014 ,p_commit => FND_API.g_false
2015 ,p_init_msg_list => FND_API.g_true
2016 ,px_object_version_number => px_object_version_number
2017 ,x_return_status => x_return_status
2018 ,x_msg_count => x_msg_count
2019 ,x_msg_data => x_msg_data
2020 );
2021
2022 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2023 RAISE FND_API.G_EXC_ERROR;
2024 END IF;
2025
2026 x_wf_item_key := -1;
2027
2028 --UI Logging
2029 IBC_AUDIT_LOG_GRP.log_action(
2030 p_activity => Ibc_Utilities_Pvt.G_ALA_APPROVE
2031 ,p_object_type => IBC_AUDIT_LOG_GRP.G_CITEM_VERSION
2032 ,p_object_value1 => p_citem_ver_id
2033 ,p_object_value2 => p_language
2034 ,p_parent_value => getCitemId(p_citem_ver_id)
2035 ,p_message_application => 'IBC'
2036 ,p_message_name => 'IBC_TRANS_LOG_MSG'
2037 ,p_extra_info2_type => IBC_AUDIT_LOG_GRP.G_EI_LOOKUP
2038 ,p_extra_info2_ref_type => 'IBC_CITEM_VERSION_STATUS'
2039 ,p_extra_info2_value => IBC_UTILITIES_PUB.G_STV_APPROVED
2040 --,p_commit => FND_API.g_true
2041 ,p_init_msg_list => FND_API.g_true
2042 ,x_return_status => x_return_status
2043 ,x_msg_count => x_msg_count
2044 ,x_msg_data => x_msg_data
2045 );
2046
2047
2048 ELSE
2049 --======================================================================
2050 --======================================================================
2051 -- Else invoke the WF.
2052 --======================================================================
2053 --======================================================================
2054
2055 -- Set Status of Content Item Version
2056 IBC_CITEM_ADMIN_GRP.Change_Translation_Status(
2057 p_citem_ver_id => p_citem_ver_id
2058 ,p_new_status => IBC_UTILITIES_PUB.G_STV_SUBMIT_FOR_APPROVAL -- Change the status to submitted
2059 ,p_language => p_language
2060 ,p_commit => FND_API.g_false
2061 ,p_init_msg_list => FND_API.g_true
2062 ,px_object_version_number => px_object_version_number
2063 ,x_return_status => x_return_status
2064 ,x_msg_count => x_msg_count
2065 ,x_msg_data => x_msg_data
2066 );
2067
2068 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2069 RAISE FND_API.G_EXC_ERROR;
2070 END IF;
2071
2072 --UI Logging
2073 IBC_AUDIT_LOG_GRP.log_action(
2074 p_activity => Ibc_Utilities_Pvt.G_ALA_SUBMIT
2075 ,p_object_type => IBC_AUDIT_LOG_GRP.G_CITEM_VERSION
2076 ,p_object_value1 => p_citem_ver_id
2077 ,p_object_value2 => p_language
2078 ,p_parent_value => getCitemId(p_citem_ver_id)
2079 ,p_message_application => 'IBC'
2080 ,p_message_name => 'IBC_TRANS_LOG_MSG'
2081 ,p_extra_info2_type => IBC_AUDIT_LOG_GRP.G_EI_LOOKUP
2082 ,p_extra_info2_ref_type => 'IBC_CITEM_VERSION_STATUS'
2083 ,p_extra_info2_value => IBC_UTILITIES_PUB.G_STV_SUBMIT_FOR_APPROVAL
2084 --,p_commit => FND_API.g_true
2085 ,p_init_msg_list => FND_API.g_true
2086 ,x_return_status => x_return_status
2087 ,x_msg_count => x_msg_count
2088 ,x_msg_data => x_msg_data
2089 );
2090
2091 -- Workflow related Code
2092 -- Creation of workflow process for Content Item Translation Approval
2093 WF_ENGINE.createProcess(itemType => l_ItemType
2094 ,itemKey => l_ItemKey
2095 ,process => 'IBC_CITEM_TRANSLATE_APPROVAL'
2096 );
2097
2098 -- Set WF attribute values
2099 WF_ENGINE.SetItemAttrText(itemtype => l_ItemType
2100 ,itemkey => l_Itemkey
2101 ,aname => 'DIRECTORY_PATH'
2102 ,avalue => l_directory_path
2103 );
2104
2105 WF_ENGINE.SetItemAttrText(itemtype => l_ItemType
2106 ,itemkey => l_Itemkey
2107 ,aname => 'SUBMITTED_BY'
2108 ,avalue => FND_GLOBAL.USER_NAME
2109 );
2110
2111 OPEN c_submitter_name;
2112 FETCH c_submitter_name INTO l_submitter_name;
2113 IF c_submitter_name%FOUND AND l_submitter_name IS NOT NULL THEN
2114 WF_ENGINE.SetItemAttrText(itemtype => l_ItemType
2115 ,itemkey => l_Itemkey
2116 ,aname => 'SUBMITTER_NAME'
2117 ,avalue => l_submitter_name
2118 );
2119 END IF;
2120 CLOSE c_submitter_name;
2121
2122
2123
2124
2125 OPEN c_owner(p_citem_ver_id);
2126 FETCH c_owner INTO l_owner_resource_id, l_owner_resource_type, l_version_number, l_creator_id;
2127
2128 -- Functionality for Approval in case IBC_USE_ACCESS_CONTROL is set to 'N'
2129 -- If the submitter is not the owner then send a notification to him by a
2130 -- creating an appripriate role.
2131
2132 IF l_wf_no_approver_defined = 'Y'
2133 AND NVL(Fnd_Profile.Value_specific('IBC_USE_ACCESS_CONTROL',-999,-999,-999), 'N') = 'N'
2134 AND l_owner_resource_id IS NOT NULL
2135 AND (( l_owner_resource_type IS NULL
2136 AND l_owner_resource_id <> l_creator_id)
2137 OR
2138 (l_owner_resource_type IS NOT NULL
2139 AND IBC_UTILITIES_PVT.check_current_user(NULL
2140 ,l_owner_resource_id
2141 ,l_owner_resource_type
2142 ,l_creator_id) = 'FALSE'))
2143 THEN
2144 l_wf_no_approver_defined := 'N';
2145 IF l_owner_resource_type IS NOT NULL THEN -- Owner is a resource
2146 Create_Workflow_Role(
2147 p_resource_id => l_owner_resource_id
2148 ,p_resource_type => l_owner_resource_type
2149 ,px_wf_role_name => l_wf_role_name
2150 ,px_wf_role_display_name => l_wf_role_display_name
2151 );
2152 ELSE -- Owner is a user FND_USER
2153 OPEN c_user_name(l_owner_resource_id);
2154 FETCH c_user_name INTO l_owner_name;
2155 CLOSE c_user_name;
2156 Create_Workflow_Role(
2157 p_user_list => l_owner_name
2158 ,px_wf_role_name => l_wf_role_name
2159 ,px_wf_role_display_name => l_wf_role_display_name
2160 );
2161 END IF;
2162 END IF;
2163
2164
2165 WF_ENGINE.SetItemAttrText(itemtype => l_ItemType
2166 ,itemkey => l_Itemkey
2167 ,aname => 'NO_APPROVER_DEFINED'
2168 ,avalue => l_wf_no_approver_defined
2169 );
2170
2171 WF_ENGINE.SetItemAttrText(itemtype => l_ItemType
2172 ,itemkey => l_Itemkey
2173 ,aname => 'CITEM_TRANS_APPROVER_ROLE'--'CITEM_APPROVER_ROLE'
2174 ,avalue => l_wf_role_name);
2175
2176 -- Set REPLY_TO Role Attribute, and CITEM Version Number
2177 l_wf_role_name := NULL;
2178 l_wf_role_display_name := NULL;
2179 Create_Workflow_Role(
2180 p_resource_id => l_owner_resource_id
2181 ,p_resource_type => l_owner_resource_type
2182 ,px_wf_role_name => l_wf_role_name
2183 ,px_wf_role_display_name => l_wf_role_display_name
2184 ,p_add_to_list => FND_GLOBAL.USER_NAME
2185 );
2186 WF_ENGINE.SetItemAttrText(itemtype => l_ItemType
2187 ,itemkey => l_Itemkey
2188 ,aname => 'REPLY_TO'
2189 ,avalue => l_wf_role_name);
2190 CLOSE c_owner;
2191
2192 WF_ENGINE.SetItemAttrNumber(itemtype => l_ItemType
2193 ,itemkey => l_Itemkey
2194 ,aname => 'CITEM_VER_ID'
2195 ,avalue => p_citem_ver_id);
2196
2197 WF_ENGINE.SetItemAttrNumber(itemtype => l_ItemType
2198 ,itemkey => l_Itemkey
2199 ,aname => 'CITEM_VERSION_NBR'
2200 ,avalue => l_version_number);
2201
2202 WF_ENGINE.SetItemAttrNumber(itemtype => l_ItemType
2203 ,itemkey => l_Itemkey
2204 ,aname => 'CITEM_OBJECT_VERSION_NUMBER'
2205 ,avalue => px_object_version_number);
2206
2207 OPEN c_citem_name(p_citem_ver_id);
2208 FETCH c_citem_name INTO l_citem_name;
2209 IF c_citem_name%FOUND AND l_citem_name IS NOT NULL THEN
2210 WF_ENGINE.SetItemAttrText(itemtype => l_ItemType
2211 ,itemkey => l_Itemkey
2212 ,aname => 'CONTENT_ITEM_NAME'
2213 ,avalue => l_citem_name);
2214 END IF;
2215 CLOSE c_citem_name;
2216
2217 IF p_notes_to_approver IS NOT NULL THEN
2218 WF_ENGINE.SetItemAttrText(itemtype => l_ItemType
2219 ,itemkey => l_Itemkey
2220 ,aname => 'NOTES_TO_APPROVER'
2221 ,avalue => p_notes_to_approver);
2222 END IF;
2223
2224 IF p_priority IS NOT NULL THEN
2225 WF_ENGINE.SetItemAttrNumber(itemtype => l_ItemType
2226 ,itemkey => l_Itemkey
2227 ,aname => 'PRIORITY'
2228 ,avalue => p_priority);
2229 END IF;
2230
2231 IF p_callback_url IS NOT NULL THEN
2232 WF_ENGINE.SetItemAttrText(itemtype => l_ItemType
2233 ,itemkey => l_Itemkey
2234 ,aname => 'UNTOUCHED_CALLBACK_URL'
2235 ,avalue => p_callback_url);
2236
2237 -- Replace Info on Callback URL
2238 l_format_callback_url := p_callback_url;
2239 l_format_callback_url := REPLACE(l_format_callback_url
2240 ,FND_GLOBAL.Local_Chr(38) || 'CITEM_VERSION_ID'
2241 ,p_citem_ver_id);
2242 l_format_callback_url := REPLACE(l_format_callback_url
2243 ,FND_GLOBAL.Local_Chr(38) || 'OBJECT_VERSION_NUMBER'
2244 ,px_object_version_number);
2245 l_format_callback_url := REPLACE(l_format_callback_url
2246 ,FND_GLOBAL.Local_Chr(38) || 'CONTENT_ITEM_LANGUAGE'
2247 ,p_language);
2248 l_format_callback_url := REPLACE(l_format_callback_url
2249 ,FND_GLOBAL.Local_Chr(38) || 'ITEM_TYPE'
2250 ,l_ItemType);
2251 l_format_callback_url := REPLACE(l_format_callback_url
2252 ,FND_GLOBAL.Local_Chr(38) || 'ITEM_KEY'
2253 ,l_ItemKey);
2254
2255 WF_ENGINE.SetItemAttrText(itemtype => l_ItemType
2256 ,itemkey => l_Itemkey
2257 ,aname => 'ORIGINAL_CALLBACK_URL'
2258 ,avalue => l_format_callback_url);
2259
2260 l_format_callback_url := REPLACE(l_format_callback_url
2261 ,FND_GLOBAL.Local_Chr(38) || 'ACTION_MODE'
2262 ,IBC_UTILITIES_PUB.G_STV_SUBMIT_FOR_APPROVAL);
2263
2264 WF_ENGINE.SetItemAttrText(itemtype => l_ItemType
2265 ,itemkey => l_Itemkey
2266 ,aname => 'CALLBACK_URL'
2267 ,avalue => l_format_callback_url);
2268
2269 l_callback_url_description := NVL(p_callback_url_description, l_format_callback_url);
2270 WF_ENGINE.SetItemAttrText(itemtype => l_ItemType
2271 ,itemkey => l_Itemkey
2272 ,aname => 'CALLBACK_URL_DESCRIPTION'
2273 ,avalue => l_callback_url_description);
2274 END IF;
2275
2276 WF_ENGINE.SetItemAttrText(itemtype => l_ItemType
2277 ,itemkey => l_Itemkey
2278 ,aname => 'CITEM_LANGUAGE'
2279 ,avalue => p_language);
2280
2281 -- Set the language description
2282 IBC_UTILITIES_PVT.Get_Language_Description(p_language_code => p_language
2283 ,p_language_description => l_language_description
2284 );
2285 --DBMS_OUTPUT.put_line('l_language_description =' || l_language_description);
2286 WF_ENGINE.SetItemAttrText(itemtype => l_ItemType
2287 ,itemkey => l_Itemkey
2288 ,aname => 'CITEM_LANGUAGE_DESCRIPTION'
2289 ,avalue => l_language_description);
2290
2291
2292 -- Start WF Process
2293 WF_ENGINE.StartProcess (ItemType => l_ItemType
2294 ,ItemKey => l_ItemKey);
2295
2296 -- If everything is okay so far then set x_wf_item_key
2297 x_wf_item_key := l_ItemKey;
2298
2299 END IF; -- End If of Conditionally invoking the WF
2300
2301 -- COMMIT?
2302 IF (p_commit = FND_API.g_true) THEN
2303 COMMIT;
2304 END IF;
2305
2306 -- Standard call to get message count and if count=1, get the message
2307 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2308 p_data => x_msg_data);
2309 EXCEPTION
2310 WHEN FND_API.G_EXC_ERROR THEN
2311 x_return_status := FND_API.G_RET_STS_ERROR;
2312 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2313 p_data => x_msg_data);
2314 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
2315 P_API_NAME => L_API_NAME
2316 ,P_PKG_NAME => G_PKG_NAME
2317 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2318 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
2319 ,P_SQLCODE => SQLCODE
2320 ,P_SQLERRM => SQLERRM
2321 ,X_MSG_COUNT => X_MSG_COUNT
2322 ,X_MSG_DATA => X_MSG_DATA
2323 ,X_RETURN_STATUS => X_RETURN_STATUS
2324 );
2325 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2327 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2328 ,p_data => x_msg_data
2329 );
2330 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
2331 P_API_NAME => L_API_NAME
2332 ,P_PKG_NAME => G_PKG_NAME
2333 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2334 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
2335 ,P_SQLCODE => SQLCODE
2336 ,P_SQLERRM => SQLERRM
2337 ,X_MSG_COUNT => X_MSG_COUNT
2338 ,X_MSG_DATA => X_MSG_DATA
2339 ,X_RETURN_STATUS => X_RETURN_STATUS
2340 );
2341 WHEN OTHERS THEN
2342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2343 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2344 THEN
2345 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2346 END IF;
2347 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2348 ,p_data => x_msg_data
2349 );
2350 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
2351 P_API_NAME => L_API_NAME
2352 ,P_PKG_NAME => G_PKG_NAME
2353 ,P_EXCEPTION_LEVEL => IBC_UTILITIES_PVT.G_EXC_OTHERS
2354 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
2355 ,P_SQLCODE => SQLCODE
2356 ,P_SQLERRM => SQLERRM
2357 ,X_MSG_COUNT => X_MSG_COUNT
2358 ,X_MSG_DATA => X_MSG_DATA
2359 ,X_RETURN_STATUS => X_RETURN_STATUS
2360 );
2361 END Submit_For_Trans_Approval;
2362
2363
2364
2365 -- --------------------------------------------------------------------
2366 -- PROCEDURE: IBC_CITEM_WORKFLOW_PVT.Process_TA_Response
2367 -- DESCRIPTION: Procedure to be called from WF to process the response
2368 -- for translation approval(TA) notification request.
2369 -- It focuses more on REJECTED response to set callback
2370 -- URL
2371 -- (Standard WF API)
2372 -- --------------------------------------------------------------------
2373 PROCEDURE Process_TA_Response(itemtype IN VARCHAR2
2374 ,itemkey IN VARCHAR2
2375 ,actid IN NUMBER
2376 ,funcmode IN VARCHAR2
2377 ,result IN OUT NOCOPY VARCHAR2
2378 ) IS
2379 l_callback_url VARCHAR2(240);
2380 l_citem_ver_id NUMBER;
2381 l_language VARCHAR2(4);
2382 l_return_status VARCHAR2(30);
2383 l_msg_count NUMBER;
2384 l_msg_data VARCHAR2(2000);
2385 l_object_version_number NUMBER;
2386 l_response_code VARCHAR2(30);
2387 l_error_msg_stack VARCHAR2(10000);
2388 l_comments VARCHAR2(10000);
2389 BEGIN
2390 result := '';
2391 IF funcmode IN ('RUN') THEN
2392 -- commented to fix 5255155
2393 /*
2394 l_callback_url := WF_ENGINE.GetItemAttrText(itemtype => itemtype
2395 ,itemkey => itemkey
2396 ,aname => 'ORIGINAL_CALLBACK_URL'
2397 );
2398 */
2399
2400 l_response_code := WF_ENGINE.GetItemAttrText(itemtype => itemtype
2401 ,itemkey => itemkey
2402 ,aname => 'RESULT'
2403 );
2404 IF l_response_code = 'N'
2405 THEN
2406 l_citem_ver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
2407 ,itemkey => itemkey
2408 ,aname => 'CITEM_VER_ID'
2409 );
2410
2411 l_object_version_number := IBC_CITEM_ADMIN_GRP.getObjVerNum(getCitemId(l_citem_ver_id));
2412
2413 l_language := WF_ENGINE.GetItemAttrText(itemtype => itemtype
2414 ,itemkey => itemkey
2415 ,aname => 'CITEM_LANGUAGE'
2416 );
2417
2418 -- Update the Translation Approval Status
2419 IBC_CITEM_ADMIN_GRP.Change_Translation_Status(
2420 p_citem_ver_id => l_citem_ver_id
2421 ,p_new_status => IBC_UTILITIES_PUB.G_STV_REJECTED
2422 ,p_language => l_language
2423 ,p_commit => FND_API.g_true
2424 ,p_init_msg_list => FND_API.g_true
2425 ,px_object_version_number => l_object_version_number
2426 ,x_return_status => l_return_status
2427 ,x_msg_count => l_msg_count
2428 ,x_msg_data => l_msg_data
2429 );
2430
2431
2432 IF l_callback_url IS NOT NULL THEN
2433 l_callback_url := REPLACE(l_callback_url
2434 ,FND_GLOBAL.Local_Chr(38) || 'ACTION_MODE'
2435 ,IBC_UTILITIES_PUB.G_STV_REJECTED
2436 );
2437
2438 WF_ENGINE.SetItemAttrText(itemtype => itemtype
2439 ,itemkey => itemkey
2440 ,aname => 'CALLBACK_URL'
2441 ,avalue => l_callback_url
2442 );
2443
2444 END IF;
2445
2446 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2447 -- Audit Log Action
2448 l_comments := WF_ENGINE.GetItemAttrText(itemtype => itemtype
2449 ,itemkey => itemkey
2450 ,aname => 'NOTES_TO_SUBMITTER'
2451 );
2452 -- Logging
2453 IBC_AUDIT_LOG_GRP.log_action(
2454 p_activity => Ibc_Utilities_Pvt.G_ALA_REJECT
2455 ,p_object_type => IBC_AUDIT_LOG_GRP.G_CITEM_VERSION
2456 ,p_object_value1 => l_citem_ver_id
2457 ,p_object_value2 => l_language
2458 ,p_parent_value => getCitemId(l_citem_ver_id)
2459 ,p_message_application => 'IBC'
2460 ,p_message_name => 'IBC_TRANS_LOG_MSG'
2461 ,p_extra_info1_type => IBC_AUDIT_LOG_GRP.G_EI_CONSTANT
2462 ,p_extra_info1_value => l_comments
2463 ,p_extra_info2_type => IBC_AUDIT_LOG_GRP.G_EI_LOOKUP
2464 ,p_extra_info2_ref_type => 'IBC_CITEM_VERSION_STATUS'
2465 ,p_extra_info2_value => IBC_UTILITIES_PUB.G_STV_REJECTED
2466 --,p_commit => FND_API.g_true
2467 ,p_init_msg_list => FND_API.g_true
2468 ,x_return_status => l_return_status
2469 ,x_msg_count => l_msg_count
2470 ,x_msg_data => l_msg_data
2471 );
2472
2473
2474 END IF;
2475
2476 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2477 IBC_UTILITIES_PVT.Get_Messages(p_message_count => l_msg_count
2478 ,x_msgs => l_error_msg_stack
2479 );
2480
2481 l_error_msg_stack := FND_GLOBAL.Newline()||'CITEM_VER_ID:'
2482 ||l_citem_ver_id
2483 ||' - Object Version Number:'
2484 ||l_object_version_number
2485 ||FND_GLOBAL.NewLine()
2486 ||l_error_msg_stack;
2487
2488 WF_ENGINE.SetItemAttrText(itemtype => itemtype
2489 ,itemkey => itemkey
2490 ,aname => 'ERROR_MESSAGE_STACK'
2491 ,avalue => l_error_msg_stack
2492 );
2493
2494 END IF;
2495
2496 END IF;
2497 END IF;
2498 -- Exception Handler Added for NOCOPY
2499 EXCEPTION
2500 WHEN OTHERS THEN
2501 RAISE;
2502 END Process_TA_Response;
2503
2504
2505 -- --------------------------------------------------------------------
2506 -- PROCEDURE: IBC_CITEM_WORKFLOW_PVT.Approve_Translation
2507 -- DESCRIPTION: Procedure to be called from WF to actually perform the
2508 -- translation approval process thru status change API.
2509 -- If it's approved succesfully then 'COMPLETE:Y' will be
2510 -- returned and callback URL updated, otherwise
2511 -- 'COMPLETE:N' will be returned along with error
2512 -- stack assigned to 'ERROR_MESSAGE_STACK' WF Attribute.
2513 -- (Standard WF API)
2514 -- --------------------------------------------------------------------
2515 PROCEDURE Approve_Translation(itemtype IN VARCHAR2
2516 ,itemkey IN VARCHAR2
2517 ,actid IN NUMBER
2518 ,funcmode IN VARCHAR2
2519 ,result IN OUT NOCOPY VARCHAR2
2520 ) IS
2521
2522 l_callback_url VARCHAR2(240);
2523 l_citem_ver_id NUMBER;
2524 l_object_version_number NUMBER;
2525 l_language VARCHAR2(4);
2526 l_return_status VARCHAR2(30);
2527 l_msg_count NUMBER;
2528 l_msg_data VARCHAR2(2000);
2529 l_error_msg_stack VARCHAR2(10000);
2530
2531 BEGIN
2532 result := '';
2533 IF funcmode = 'RUN' THEN
2534 result := 'COMPLETE:Y';
2535 l_citem_ver_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype
2536 ,itemkey => itemkey
2537 ,aname => 'CITEM_VER_ID'
2538 );
2539
2540 l_object_version_number := IBC_CITEM_ADMIN_GRP.getObjVerNum(getCitemId(l_citem_ver_id));
2541
2542 l_language := WF_ENGINE.GetItemAttrText(itemtype => itemtype
2543 ,itemkey => itemkey
2544 ,aname => 'CITEM_LANGUAGE'
2545 );
2546
2547 IF l_citem_ver_id IS NOT NULL THEN
2548 -- Update the Translation Approval Status
2549 IBC_CITEM_ADMIN_GRP.Change_Translation_Status(
2550 p_citem_ver_id => l_citem_ver_id
2551 ,p_new_status => IBC_UTILITIES_PUB.G_STV_APPROVED
2552 ,p_language => l_language
2553 ,p_commit => FND_API.g_true
2554 ,p_init_msg_list => FND_API.g_true
2555 ,px_object_version_number => l_object_version_number
2556 ,x_return_status => l_return_status
2557 ,x_msg_count => l_msg_count
2558 ,x_msg_data => l_msg_data
2559 );
2560
2561
2562 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2563 WF_ENGINE.SetItemAttrText(itemtype => itemtype
2564 ,itemkey => itemkey
2565 ,aname => 'APPROVER_NAME'
2566 ,avalue => get_user_description(FND_GLOBAL.user_id)
2567 );
2568
2569 l_callback_url := WF_ENGINE.GetItemAttrText(itemtype => itemtype
2570 ,itemkey => itemkey
2571 ,aname => 'ORIGINAL_CALLBACK_URL'
2572 );
2573
2574 IF l_callback_url IS NOT NULL THEN
2575 l_callback_url := REPLACE(l_callback_url
2576 ,FND_GLOBAL.Local_Chr(38) || 'ACTION_MODE'
2577 ,IBC_UTILITIES_PUB.G_STV_APPROVED
2578 );
2579
2580 WF_ENGINE.SetItemAttrText(itemtype => itemtype
2581 ,itemkey => itemkey
2582 ,aname => 'CALLBACK_URL'
2583 ,avalue => l_callback_url
2584 );
2585
2586 END IF;
2587
2588 -- Audit Log Action
2589 IBC_AUDIT_LOG_GRP.log_action(
2590 p_activity => Ibc_Utilities_Pvt.G_ALA_APPROVE
2591 ,p_object_type => IBC_AUDIT_LOG_GRP.G_CITEM_VERSION
2592 ,p_object_value1 => l_citem_ver_id
2593 ,p_object_value2 => l_language
2594 ,p_parent_value => getCitemId(l_citem_ver_id)
2595 ,p_message_application => 'IBC'
2596 ,p_message_name => 'IBC_TRANS_LOG_MSG'
2597 ,p_extra_info2_type => IBC_AUDIT_LOG_GRP.G_EI_LOOKUP
2598 ,p_extra_info2_ref_type => 'IBC_CITEM_VERSION_STATUS'
2599 ,p_extra_info2_value => IBC_UTILITIES_PUB.G_STV_APPROVED
2600 --,p_commit => FND_API.g_true
2601 ,p_init_msg_list => FND_API.g_true
2602 ,x_return_status => l_return_status
2603 ,x_msg_count => l_msg_count
2604 ,x_msg_data => l_msg_data
2605 );
2606
2607 END IF;
2608
2609 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2610 -- IF l_return_status not successful then return 'N'
2611 result := 'COMPLETE:N';
2612 IBC_UTILITIES_PVT.Get_Messages(p_message_count => l_msg_count
2613 ,x_msgs => l_error_msg_stack
2614 );
2615
2616 l_error_msg_stack := FND_GLOBAL.Newline()||'CITEM_VER_ID:'
2617 ||l_citem_ver_id
2618 ||' - Object Version Number:'
2619 ||l_object_version_number
2620 ||FND_GLOBAL.NewLine()
2621 || l_error_msg_stack;
2622
2623 WF_ENGINE.SetItemAttrText(itemtype => itemtype
2624 ,itemkey => itemkey
2625 ,aname => 'ERROR_MESSAGE_STACK'
2626 ,avalue => l_error_msg_stack
2627 );
2628
2629 END IF;
2630 END IF;
2631 END IF;
2632 RETURN;
2633 EXCEPTION
2634 WHEN OTHERS THEN
2635 RAISE;
2636 END Approve_Translation;
2637
2638 FUNCTION Is_Security_OK_For_Dir(p_directory_node_id IN NUMBER)
2639 RETURN BOOLEAN
2640 IS
2641 l_result BOOLEAN;
2642 CURSOR c_item_approvals_nonotif(p_directory_node_id NUMBER,
2643 p_user_pattern VARCHAR2)
2644 IS
2645 select IAS.ITEM_KEY
2646 from WF_LOOKUPS L_AT, WF_LOOKUPS L_AS, WF_ACTIVITIES_VL A, WF_PROCESS_ACTIVITIES PA,
2647 WF_ITEM_TYPES_VL IT, WF_ITEMS I, WF_ITEM_ACTIVITY_STATUSES IAS,
2648 ibc_citem_versions_b civb,
2649 ibc_content_items citem
2650 WHERE IAS.ITEM_TYPE = I.ITEM_TYPE
2651 and IAS.ITEM_KEY = I.ITEM_KEY
2652 and I.BEGIN_DATE between A.BEGIN_DATE and nvl(A.END_DATE, I.BEGIN_DATE)
2653 and I.ITEM_TYPE = IT.NAME
2654 and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
2655 and PA.ACTIVITY_NAME = A.NAME
2656 and PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE
2657 and L_AT.LOOKUP_TYPE = 'WFENG_ACTIVITY_TYPE'
2658 and L_AT.LOOKUP_CODE = A.TYPE
2659 and L_AS.LOOKUP_TYPE = 'WFENG_STATUS'
2660 and L_AS.LOOKUP_CODE = IAS.ACTIVITY_STATUS
2661 AND A.NAME = 'IBC_CITEM_APPROVE_NOTIFICATION'
2662 AND SUBSTR(IAS.ITEM_KEY ,1 ,INSTR(IAS.ITEM_KEY,'@') - 1) = civb.citem_version_id
2663 AND civb.content_item_id = citem.content_item_id
2664 AND directory_node_id = p_directory_node_id AND
2665 IAS.ITEM_TYPE = 'IBC_WF'
2666 AND IAS.ACTIVITY_STATUS = 'NOTIFIED'
2667 GROUP BY IAS.ITEM_KEY
2668 MINUS
2669 select IAS.ITEM_KEY
2670 from WF_LOOKUPS L_AT, WF_LOOKUPS L_AS, WF_ACTIVITIES_VL A, WF_PROCESS_ACTIVITIES PA,
2671 WF_ITEM_TYPES_VL IT, WF_ITEMS I, WF_ITEM_ACTIVITY_STATUSES IAS, WF_USER_ROLES U,
2672 ibc_citem_versions_b civb,
2673 ibc_content_items citem
2674 WHERE IAS.ITEM_TYPE = I.ITEM_TYPE
2675 and IAS.ITEM_KEY = I.ITEM_KEY
2676 and I.BEGIN_DATE between A.BEGIN_DATE and nvl(A.END_DATE, I.BEGIN_DATE)
2677 and I.ITEM_TYPE = IT.NAME
2678 and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
2679 and PA.ACTIVITY_NAME = A.NAME
2680 and PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE
2681 and L_AT.LOOKUP_TYPE = 'WFENG_ACTIVITY_TYPE'
2682 and L_AT.LOOKUP_CODE = A.TYPE
2683 and L_AS.LOOKUP_TYPE = 'WFENG_STATUS'
2684 and L_AS.LOOKUP_CODE = IAS.ACTIVITY_STATUS
2685 AND A.NAME = 'IBC_CITEM_APPROVE_NOTIFICATION'
2686 AND SUBSTR(IAS.ITEM_KEY ,1 ,INSTR(IAS.ITEM_KEY,'@') - 1) = civb.citem_version_id
2687 AND civb.content_item_id = citem.content_item_id
2688 AND directory_node_id = p_directory_node_id AND
2689 IAS.ITEM_TYPE = 'IBC_WF'
2690 AND IAS.ACTIVITY_STATUS = 'NOTIFIED'
2691 AND IAS.ASSIGNED_USER = U.ROLE_NAME
2692 AND NOT EXISTS(SELECT 'X'
2693 FROM ibc_pending_approvals_v pav2
2694 WHERE pav2.item_key = IAS.item_key
2695 AND p_user_pattern NOT LIKE '%[' || U.user_name || ']%'
2696 )
2697 GROUP BY IAS.ITEM_KEY;
2698
2699 l_item_key VARCHAR2(80);
2700 l_item_approve_users VARCHAR2(32767);
2701 l_citem_object_type NUMBER := IBC_DATA_SECURITY_PVT.get_object_id('IBC_CONTENT_ITEM');
2702 l_directory_object_type NUMBER := IBC_DATA_SECURITY_PVT.get_object_id('IBC_DIRECTORY_NODE');
2703 l_return_status VARCHAR2(30);
2704 l_msg_count NUMBER;
2705 l_msg_data VARCHAR2(2000);
2706
2707 BEGIN
2708 l_result := TRUE;
2709
2710 IBC_DATA_SECURITY_PVT.get_grantee_usernames(
2711 p_instance_object_id => l_citem_object_type
2712 ,p_instance_pk1_value => NULL
2713 ,p_permission_code => 'CITEM_APPROVE'
2714 ,p_container_object_id => l_directory_object_type
2715 ,p_container_pk1_value => p_directory_node_id
2716 ,x_usernames => l_item_approve_users
2717 ,x_return_status => l_return_status
2718 ,x_msg_count => l_msg_count
2719 ,x_msg_data => l_msg_data
2720 );
2721
2722 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2723
2724 IF l_item_approve_users IS NOT NULL THEN
2725 -- Preparing list of users so it can be used in Query as a pattern
2726 l_item_approve_users := '[' || REPLACE(l_item_approve_users, ',', '][') || ']';
2727 END IF;
2728
2729 OPEN c_item_approvals_nonotif(p_directory_node_id,
2730 NVL(l_item_approve_users, '[]'));
2731 FETCH c_item_approvals_nonotif INTO l_item_key;
2732 IF c_item_approvals_nonotif%FOUND THEN
2733 l_result := FALSE;
2734 END IF;
2735 ELSE
2736 -- Error from get_grantee_usernames
2737 l_result := FALSE;
2738 END IF;
2739
2740 RETURN l_result;
2741 EXCEPTION
2742 WHEN OTHERS THEN
2743 l_result := FALSE;
2744 RETURN l_result;
2745 END Is_Security_OK_For_Dir;
2746
2747 -- --------------------------------------------------------------------------------
2748 -- PROCEDURE: IBC_CITEM_WORKFLOW_PVT.Notify_Move
2749 -- DESCRIPTION: Procedure to be called from WF and a notification has to be
2750 -- sent to all users with the Read Item permission that the category
2751 -- or folder has moved to a new location.
2752 -- -------------------------------------------------------------------------------
2753
2754 PROCEDURE Notify_Move(p_object_name IN VARCHAR2
2755 ,p_content_item_id IN NUMBER
2756 ,p_source_dir_node_id IN NUMBER
2757 ,p_destination_dir_node_id IN NUMBER
2758
2759 ) IS
2760 l_return_status VARCHAR2(30);
2761 l_msg_count NUMBER;
2762 l_msg_data VARCHAR2(2000);
2763 l_error_msg_stack VARCHAR2(10000);
2764 l_directory_node_id NUMBER;
2765 l_directory_path VARCHAR2(4000);
2766 l_citem_object_type NUMBER;
2767 l_directory_object_type NUMBER;
2768 l_object VARCHAR2(30);
2769 l_name VARCHAR2(240);
2770
2771 CURSOR c_citem_info(p_content_item_id NUMBER) IS
2772 SELECT content_item_name
2773 FROM ibc_citem_versions_vl civ
2774 WHERE civ.content_item_id = p_content_item_id;
2775
2776 CURSOR c_citem_location(p_content_item_id NUMBER) IS
2777 SELECT dirnode.directory_path
2778 FROM ibc_content_items citem,
2779 ibc_directory_nodes_b dirnode
2780 WHERE citem.content_item_id = p_content_item_id
2781 AND citem.directory_node_id = dirnode.directory_node_id;
2782
2783 CURSOR c_directory(p_directory_node_id NUMBER) IS
2784 SELECT dirnode.directory_path
2785 FROM ibc_directory_nodes_b dirnode
2786 WHERE directory_node_id = p_directory_node_id;
2787
2788 ls_directory_path VARCHAR2(4000);
2789 ld_dirctory_path VARCHAR2(4000);
2790 l_subject VARCHAR2(4000);
2791 l_body VARCHAR2(4000);
2792 l_citem_name VARCHAR2(240);
2793
2794 l_ItemType VARCHAR2(30);
2795 l_ItemKey VARCHAR2(80);
2796 l_message_name VARCHAR2(30);
2797 l_notif_id NUMBER;
2798 l_user_list VARCHAR2(32000);
2799 l_wf_role_name VARCHAR2(240);
2800 l_wf_role_display_name VARCHAR2(80);
2801 l_permission_code VARCHAR2(30);
2802 x_return_status VARCHAR2(30);
2803 x_msg_count VARCHAR2(30);
2804 x_msg_data VARCHAR2(4096);
2805
2806 l_api_name CONSTANT VARCHAR2(30) := 'Notify_Move';
2807 g_pkg_name CONSTANT VARCHAR2(240) := 'IBC_CITEM_WORKFLOW_PVT';
2808
2809 BEGIN
2810
2811 l_ItemType := 'IBC_WF';
2812 l_ItemKey := TO_CHAR(SYSDATE,'YYYYMMDD-HH24:MI:SS');
2813 l_message_name := 'GEN_STDLN_MESG';
2814
2815 -- A notification will be sent to all users with the Read Item permission that the content
2816 -- folder or category has been moved to a new location. Such a notification will not be sent
2817 -- if the permissions is set to the Public Level.
2818
2819
2820 IF p_content_item_id IS NOT NULL THEN
2821 OPEN c_citem_info(p_content_item_id);
2822 FETCH c_citem_info INTO l_citem_name;
2823 CLOSE c_citem_info;
2824 END IF;
2825
2826 IF l_citem_name IS NULL THEN
2827 null;
2828 END IF;
2829
2830 IF p_source_dir_node_id IS NOT NULL THEN
2831 OPEN c_directory(p_source_dir_node_id);
2832 FETCH c_directory INTO ls_directory_path;
2833 CLOSE c_directory;
2834 END IF;
2835
2836 IF p_destination_dir_node_id IS NOT NULL THEN
2837 OPEN c_directory(p_destination_dir_node_id);
2838 FETCH c_directory INTO ld_dirctory_path;
2839 CLOSE c_directory;
2840 END IF;
2841
2842 -- Get the users list
2843 IF p_object_name = 'IBC_CATEGORY_NODE' THEN
2844 l_permission_code := 'PD_VIEW';
2845 l_citem_object_type := IBC_DATA_SECURITY_PVT.get_object_id('IBC_CATEGORY_NODE');
2846 l_directory_object_type := IBC_DATA_SECURITY_PVT.get_object_id('IBC_CATEGORY_NODE');
2847
2848 ELSE
2849 l_permission_code := 'CITEM_READ';
2850 l_citem_object_type := IBC_DATA_SECURITY_PVT.get_object_id('IBC_CONTENT_ITEM');
2851 l_directory_object_type := IBC_DATA_SECURITY_PVT.get_object_id('IBC_DIRECTORY_NODE');
2852 END IF;
2853
2854 IBC_DATA_SECURITY_PVT.get_grantee_usernames(
2855 p_instance_object_id => l_citem_object_type
2856 ,p_instance_pk1_value => p_content_item_id
2857 ,p_permission_code => l_permission_code
2858 ,p_container_object_id => l_directory_object_type
2859 ,p_container_pk1_value => p_source_dir_node_id
2860 ,x_usernames => l_user_list
2861 ,x_return_status => x_return_status
2862 ,x_msg_count => x_msg_count
2863 ,x_msg_data => x_msg_data
2864 );
2865
2866 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2867 RAISE FND_API.G_EXC_ERROR;
2868 END IF;
2869
2870 IF l_user_list IS NULL THEN
2871 --x_return_status := FND_API.G_RET_STS_ERROR;
2872 --FND_MESSAGE.Set_Name('IBC', 'IBC_NOT_APPROVER_DEFINED');
2873 --FND_MSG_PUB.ADD;
2874 --RAISE FND_API.G_EXC_ERROR;
2875 return;
2876 ELSE
2877 Create_Workflow_Role(p_user_list => l_user_list
2878 ,px_wf_role_name => l_wf_role_name
2879 ,px_wf_role_display_name => l_wf_role_display_name
2880 );
2881 END IF;
2882
2883
2884 -- set the message
2885
2886 IF p_object_name='IBC_CONTENT_ITEM' THEN
2887 l_object := 'Content Item';
2888 FND_MESSAGE.SET_NAME('IBC','IBC_MOVE_ITEM_BODY_MESSAGE');
2889 FND_MESSAGE.set_token('CITEM_NAME',l_citem_name,false);
2890 FND_MESSAGE.set_token('FROM_LOCATION',ls_directory_path,false);
2891 FND_MESSAGE.set_token('TO_LOCATION',ld_dirctory_path,false);
2892 l_name:=l_citem_name;
2893 l_body:=fnd_message.get;
2894 ELSIF p_object_name = 'IBC_DIRECTORY_NODE' THEN
2895 l_object := 'Folder';
2896 FND_MESSAGE.SET_NAME('IBC','IBC_MOVE_FOLDER_BODY_MESSAGE');
2897 FND_MESSAGE.set_token('OBJECT_NAME',l_object,false);
2898 FND_MESSAGE.set_token('FROM_LOCATION',ls_directory_path,false);
2899 FND_MESSAGE.set_token('TO_LOCATION',ld_dirctory_path,false);
2900 l_name:=ls_directory_path;
2901 l_body:=fnd_message.get;
2902 ELSE
2903 l_object := 'Category';
2904 FND_MESSAGE.SET_NAME('IBC','IBC_MOVE_FOLDER_BODY_MESSAGE');
2905 FND_MESSAGE.set_token('OBJECT_NAME',l_object,false);
2906 FND_MESSAGE.set_token('FROM_LOCATION',ls_directory_path,false);
2907 FND_MESSAGE.set_token('TO_LOCATION',ld_dirctory_path,false);
2908 l_name:=ls_directory_path;
2909 l_body:=fnd_message.get;
2910
2911 END IF;
2912
2913 FND_MESSAGE.SET_NAME('IBC','IBC_MOVE_SUBJECT_MESSAGE');
2914 FND_MESSAGE.set_token('OBJECT_TYPE',l_object,false);
2915 FND_MESSAGE.set_token('OBJECT_NAME',l_name,false);
2916 l_subject:=fnd_message.get;
2917
2918 l_notif_id := Wf_Notification.Send
2919 ( ROLE => l_wf_role_name
2920 , msg_type => l_ItemType
2921 , msg_name => l_message_name
2922 );
2923
2924 Wf_Notification.SetAttrText(l_notif_id,
2925 'GEN_MSG_SUBJECT',
2926 l_subject);
2927
2928 Wf_Notification.SetAttrText(l_notif_id,
2929 'GEN_MSG_BODY',
2930 l_body);
2931
2932 Wf_Notification.SetAttrText(l_notif_id,
2933 'GEN_MSG_SEND_TO',
2934 l_wf_role_name);
2935
2936 Wf_Notification.Denormalize_Notification(l_notif_id);
2937
2938 EXCEPTION
2939 WHEN FND_API.G_EXC_ERROR THEN
2940 x_return_status := FND_API.G_RET_STS_ERROR;
2941 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2942 p_data => x_msg_data);
2943 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
2944 P_API_NAME => L_API_NAME
2945 ,P_PKG_NAME => G_PKG_NAME
2946 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2947 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
2948 ,P_SQLCODE => SQLCODE
2949 ,P_SQLERRM => SQLERRM
2950 ,X_MSG_COUNT => X_MSG_COUNT
2951 ,X_MSG_DATA => X_MSG_DATA
2952 ,X_RETURN_STATUS => X_RETURN_STATUS
2953 );
2954 WHEN OTHERS THEN
2955 RAISE;
2956 END Notify_Move;
2957
2958 -- --------------------------------------------------------------------------------
2959 -- PROCEDURE: IBC_CITEM_WORKFLOW_PVT.Notify_Translator
2960 -- DESCRIPTION: Procedure to be called from WF and a notification has to be
2961 -- sent to all users with Translate permission when a change is
2962 -- made to the content item that has translation enabled.
2963 -- -------------------------------------------------------------------------------
2964
2965 PROCEDURE Notify_Translator(p_content_item_id IN NUMBER)
2966 IS
2967
2968 l_citem_object_type NUMBER;
2969 l_directory_object_type NUMBER;
2970 l_object VARCHAR2(30);
2971 ls_directory_path VARCHAR2(4000);
2972 l_subject VARCHAR2(4000);
2973 l_body VARCHAR2(4000);
2974 l_citem_name VARCHAR2(240);
2975 l_source_dir_node_id NUMBER;
2976
2977 l_ItemType VARCHAR2(30);
2978 l_ItemKey VARCHAR2(80);
2979 l_message_name VARCHAR2(30);
2980 l_notif_id NUMBER;
2981 l_user_list VARCHAR2(32000);
2982 l_wf_role_name VARCHAR2(240);
2983 l_wf_role_display_name VARCHAR2(80);
2984 l_permission_code VARCHAR2(30);
2985 x_return_status VARCHAR2(30);
2986 x_msg_count VARCHAR2(30);
2987 x_msg_data VARCHAR2(4096);
2988 l_appr_user_list VARCHAR2(32000);
2989
2990
2991 l_api_name CONSTANT VARCHAR2(30) := 'Notify_Translator';
2992 g_pkg_name CONSTANT VARCHAR2(240) := 'IBC_CITEM_WORKFLOW_PVT';
2993
2994 CURSOR c_citem_info(p_content_item_id NUMBER) IS
2995 SELECT content_item_name
2996 FROM ibc_citem_versions_vl civ
2997 WHERE civ.content_item_id = p_content_item_id;
2998
2999 CURSOR c_citem_location(p_content_item_id NUMBER) IS
3000 SELECT dirnode.directory_path, dirnode.directory_node_id
3001 FROM ibc_content_items citem,
3002 ibc_directory_nodes_b dirnode
3003 WHERE citem.content_item_id = p_content_item_id
3004 AND citem.directory_node_id = dirnode.directory_node_id;
3005
3006
3007 BEGIN
3008
3009 l_ItemType := 'IBC_WF';
3010 l_ItemKey := TO_CHAR(SYSDATE,'YYYYMMDD-HH24:MI:SS');
3011 l_message_name := 'GEN_STDLN_MESG';
3012
3013 -- A notification will be sent to all users with the Read Item permission that the content
3014 -- folder or category has been moved to a new location. Such a notification will not be sent
3015 -- if the permissions is set to the Public Level.
3016
3017
3018 IF p_content_item_id IS NOT NULL THEN
3019 OPEN c_citem_info(p_content_item_id);
3020 FETCH c_citem_info INTO l_citem_name;
3021 CLOSE c_citem_info;
3022
3023 OPEN c_citem_location(p_content_item_id);
3024 FETCH c_citem_location INTO ls_directory_path, l_source_dir_node_id ;
3025 CLOSE c_citem_location;
3026
3027 END IF;
3028
3029 IF l_citem_name IS NULL THEN
3030 NULL;
3031 END IF;
3032
3033 l_permission_code := 'CITEM_TRANSLATE';
3034 l_citem_object_type := IBC_DATA_SECURITY_PVT.get_object_id('IBC_CONTENT_ITEM');
3035 l_directory_object_type := IBC_DATA_SECURITY_PVT.get_object_id('IBC_DIRECTORY_NODE');
3036 l_object := 'Content Item';
3037
3038 -- Get the users list with CITEM_TRANSLATE permission
3039
3040 IBC_DATA_SECURITY_PVT.get_grantee_usernames(
3041 p_instance_object_id => l_citem_object_type
3042 ,p_instance_pk1_value => p_content_item_id
3043 ,p_permission_code => l_permission_code
3044 ,p_container_object_id => l_directory_object_type
3045 ,p_container_pk1_value => l_source_dir_node_id
3046 ,x_usernames => l_user_list
3047 ,x_return_status => x_return_status
3048 ,x_msg_count => x_msg_count
3049 ,x_msg_data => x_msg_data
3050 );
3051
3052 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3053 RAISE FND_API.G_EXC_ERROR;
3054 END IF;
3055
3056 IF l_user_list IS NULL THEN
3057 RETURN;
3058 ELSE
3059 Create_Workflow_Role(p_user_list => l_user_list
3060 ,px_wf_role_name => l_wf_role_name
3061 ,px_wf_role_display_name => l_wf_role_display_name
3062 );
3063 END IF;
3064
3065
3066 -- set the message
3067
3068 FND_MESSAGE.SET_NAME('IBC','IBC_TRANSLATE_BODY_MESSAGE');
3069 FND_MESSAGE.set_token('CITEM_NAME',l_citem_name,FALSE);
3070 FND_MESSAGE.set_token('FROM_LOCATION',ls_directory_path,FALSE);
3071
3072 l_body:=fnd_message.get;
3073
3074
3075 FND_MESSAGE.SET_NAME('IBC','IBC_TRANSLATE_SUBJECT_MESSAGE');
3076 FND_MESSAGE.set_token('OBJECT_TYPE',l_object,FALSE);
3077 FND_MESSAGE.set_token('OBJECT_NAME',l_citem_name,FALSE);
3078 l_subject:=fnd_message.get;
3079
3080 l_notif_id := Wf_Notification.Send
3081 ( ROLE => l_wf_role_name
3082 , msg_type => l_ItemType
3083 , msg_name => l_message_name
3084 );
3085
3086 Wf_Notification.SetAttrText(l_notif_id,
3087 'GEN_MSG_SUBJECT',
3088 l_subject);
3089
3090 Wf_Notification.SetAttrText(l_notif_id,
3091 'GEN_MSG_BODY',
3092 l_body);
3093
3094 Wf_Notification.SetAttrText(l_notif_id,
3095 'GEN_MSG_SEND_TO',
3096 l_wf_role_name);
3097
3098 Wf_Notification.Denormalize_Notification(l_notif_id);
3099
3100 EXCEPTION
3101 WHEN FND_API.G_EXC_ERROR THEN
3102 x_return_status := FND_API.G_RET_STS_ERROR;
3103 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3104 p_data => x_msg_data);
3105 IBC_UTILITIES_PVT.HANDLE_EXCEPTIONS(
3106 P_API_NAME => L_API_NAME
3107 ,P_PKG_NAME => G_PKG_NAME
3108 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3109 ,P_PACKAGE_TYPE => IBC_UTILITIES_PVT.G_PVT
3110 ,P_SQLCODE => SQLCODE
3111 ,P_SQLERRM => SQLERRM
3112 ,X_MSG_COUNT => X_MSG_COUNT
3113 ,X_MSG_DATA => X_MSG_DATA
3114 ,X_RETURN_STATUS => X_RETURN_STATUS
3115 );
3116 WHEN OTHERS THEN
3117 RAISE;
3118 END Notify_Translator;
3119
3120 END IBC_CITEM_WORKFLOW_PVT;