1 PACKAGE BODY CS_KB_WF_PKG AS
2 /* $Header: cskbwfb.pls 120.3.12010000.2 2008/09/10 04:52:05 mmaiya ship $ */
3
4
5 /****************************************************
6 -------------PACKAGE VARIABLES ----------------------
7 ****************************************************/
8 BAD_INFORMATION_SUPPLIED EXCEPTION;
9 LOCK_SET_ERROR EXCEPTION;
10 PERMISSION_ERROR EXCEPTION;
11
12
13 /****************************************************
14 -------------FUNCTIONS-------------------------------
15 ****************************************************/
16
17
18 /**************************** Get Action *************************/
19 -- This function is used to get the action code of a flow_details_id
20 --
21 -- VARIABLES
22 -- g_flow_details_id
23 -- OUT: ActionCode or NULL
24 /*******************************************************************/
25
26 FUNCTION getAction(
27 g_flow_details_id IN NUMBER
28 )
29 RETURN VARCHAR2
30 IS
31 returnValue VARCHAR2(10);
32 BEGIN
33 -- return NULL if g_flow_details_id doesn't match
34 SELECT
35 MAX(action)
36 INTO
37 returnValue
38 FROM
39 cs_kb_wf_flow_details
40 WHERE
41 flow_details_id = g_flow_details_id;
42 RETURN returnValue;
43 END;
44
45
46 /**************************** Get Action Name *************************/
47 -- This function is used to get the action name given action lookup code
48 --
49 -- VARIABLES
50 -- IN: g_action, action code
51 -- OUT: action meaning
52 /**********************************************************************/
53
54 FUNCTION getActionName(
55 g_action IN VARCHAR2
56 )
57 RETURN VARCHAR2
58 IS
59 returnValue CS_LOOKUPS.MEANING%TYPE;
60 BEGIN
61 SELECT
62 MAX(MEANING)
63 INTO
64 returnValue
65 FROM
66 CS_LOOKUPS
67 WHERE
68 lookup_code = g_action
69 AND
70 lookup_type = 'CS_KB_INTERNAL_CODES';
71
72 RETURN returnValue;
73 END;
74
75 /**************************** Get Flow Id *************************/
76 -- This function is used to get the flow id of a flow_details_id
77 --
78 --
79 -- VARIABLES
80 -- g_flow_details_id
81 /*******************************************************************/
82
83 FUNCTION getFlowId(
84 g_flow_details_id IN NUMBER
85 )
86 RETURN NUMBER
87 IS
88 returnValue NUMBER;
89 BEGIN
90 SELECT
91 MAX(flow_id)
92 INTO
93 returnValue
94 FROM
95 cs_kb_wf_flow_details
96 WHERE
97 flow_details_id = g_flow_details_id;
98 RETURN returnValue;
99 END;
100
101
102 /**************************** Get Status *****************/
103 -- This function is to get a status for display. It will display
104 -- a translated sets_b.status or flow_details.step, according
105 -- to whether a step is available
106 --
107 --
108 -- VARIABLES
109 -- g_setId -- set Id
110 /*******************************************************************/
111
112 FUNCTION getStatus(
113 g_setId IN NUMBER
114 )
115 RETURN VARCHAR2
116 IS
117 returnValue CS_LOOKUPS.MEANING%TYPE;
118 BEGIN
119 SELECT
120 MAX(DECODE(B.STATUS,'NOT',LU2.MEANING,LU1.MEANING))
121 INTO
122 returnValue
123 FROM
124 cs_kb_sets_b B,
125 cs_kb_wf_flow_details D,
126 CS_LOOKUPS LU1,
127 CS_LOOKUPS LU2
128 WHERE
129 B.flow_details_id = D.flow_details_id (+)
130 AND
131 B.status = LU1.lookup_code
132 AND
133 D.STEP = LU2.lookup_code(+)
134 AND
135 LU1.lookup_type = 'CS_KB_INTERNAL_CODES'
136 AND
137 LU2.lookup_type(+) = 'CS_KB_STATUS'
138 AND
139 B.set_id = g_setId;
140
141 RETURN returnValue;
142 END;
143
144
145 /**************************** Get Step Group *****************/
146 -- This function is used to get the group id associated with a
147 -- specific step.
148 --
149 -- VARIABLES
150 -- g_flow_details_id
151 /*******************************************************************/
152
153 FUNCTION getStepGroup(
154 g_flow_details_id IN NUMBER
155 )
156 RETURN NUMBER
157 IS
158 return_number NUMBER := 0;
159 BEGIN
160 -- MIN() is used to force a value to return and avoid NDF error
161 SELECT
162 MIN(GROUP_ID)
163 INTO
164 return_number
165 FROM
166 CS_KB_WF_FLOW_DETAILS
167 WHERE
168 FLOW_DETAILS_ID = g_flow_details_id;
169
170 RETURN return_number;
171 EXCEPTION
172 WHEN OTHERS THEN
173 return null;
174 END getStepGroup;
175
176
177 /**************************** HAS PERMISSION *************************/
178 -- This function is used to determine if a specified user has permissions
179 -- to a specific Step.
180 --
181 -- This one does NOT have profile checking and is used internally mainly.
182 --
183 -- VARIABLES
184 -- h_Step = Step to check
185 -- h_set_id
186 /*******************************************************************/
187 FUNCTION hasPermission(
188 h_flow_details_id IN NUMBER,
189 h_user_id IN NUMBER
190 )
191 RETURN NUMBER
192 IS
193 gid NUMBER;
194 BEGIN
195 gid := getStepGroup(h_flow_details_id);
196 IF (isMember(h_user_id,gid) = 1) THEN
197 RETURN 1;
198 END IF;
199 RETURN 0;
200 END hasPermission;
201
202
203 /**************************** HAS PERMISSION *************************/
204 -- This function is used to determine if a specified user has permissions
205 -- to a specific Step.
206 --
207 -- This one DOES have profile checking.
208 --
209 -- VARIABLES
210 -- h_Step = Step to check
211 -- h_user_id = the fnd_user user id
212 -- h_set_id
213 /*******************************************************************/
214 --FUNCTION hasPermission(
215 -- h_flow_details_id IN NUMBER,
216 -- h_user_id IN NUMBER,
217 -- h_set_id IN NUMBER
218 -- )
219 -- RETURN NUMBER
220 -- IS
221 -- gid NUMBER;
222 -- BEGIN
223 -- gid := getStepGroup(h_flow_details_id);
224 -- IF (isMember(h_user_id,gid) = 1) THEN
225 -- IF ((inCategory(h_user_id,h_set_id) = 1) OR (inProduct(h_user_id,h_set_id) = 1)) THEN
226 -- RETURN 1;
227 -- END IF;
228 -- END IF;
229 -- RETURN 0;
230 -- END hasPermission;
231
232
233 /**************************** IN CATEGORY *************************/
234 -- This function is used to determine if a specified user has permissions
235 -- to a specific Step.
236 --
237 -- VARIABLES
238 -- h_Step = Step to check
239 -- h_user_id = the fnd_user user id
240 --
241 /*******************************************************************/
242 FUNCTION inCategory(
243 c_user_id IN NUMBER,
244 c_set_id IN NUMBER
245 )
246 RETURN NUMBER
247 IS
248 CURSOR cats IS
249 SELECT category_id
250 FROM CS_KB_SET_CATEGORIES
251 WHERE set_id = c_set_id;
252
253 catId NUMBER;
254 returnValue NUMBER := 0;
255 BEGIN
256 OPEN cats;
257 LOOP
258 -- Get values from cursor.
259 FETCH cats INTO catId;
260 EXIT WHEN cats%NOTFOUND;
261 IF (CS_KB_PROFILES_PKG.isCategoryMember(c_user_id,catId) = 1) THEN
262 returnValue := 1;
263 EXIT;
264 END IF;
265 END LOOP;
266 CLOSE cats;
267 RETURN returnValue;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 CLOSE cats;
272 RAISE;
273 END inCategory;
274
275
276 /**************************** IN PRODUCT *************************/
277 -- This function is used to determine if a user belongs to same
278 -- product as solution.
279 --
280 -- VARIABLES
281 -- c_user_id = Step to check
282 -- c_set_id = the fnd_user user id
283 --
284 /*******************************************************************/
285 FUNCTION inProduct(
286 c_user_id IN NUMBER,
287 c_set_id IN NUMBER
288 )
289 RETURN NUMBER
290 IS
291 CURSOR prods IS SELECT
292 product_id, product_org_id
293 FROM
294 cs_kb_set_products
295 WHERE
296 set_id = c_set_id;
297 prodId NUMBER;
298 prodOrgId NUMBER;
299 returnValue NUMBER := 0;
300 BEGIN
301 OPEN prods;
302 LOOP
303 FETCH prods INTO prodId,prodOrgId;
304 EXIT WHEN prods%NOTFOUND;
305
306 IF (CS_KB_PROFILES_PKG.isProductMember(c_user_id,prodId,prodOrgId) = 1) THEN
307 returnValue := 1;
308 EXIT;
309 END IF;
310 END LOOP;
311 CLOSE prods;
312 RETURN returnValue;
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 CLOSE prods;
317 RAISE;
318 END inProduct;
319
320
321 /**************************** isMember ********************************/
322 -- This function is used to determine if a specified user belongs
323 -- to a specified notification group.
324 --
325 -- INTERNAL USE MAINLY
326 --
327 -- VARIABLES
328 -- user_id = the fnd_user user id
329 -- group_id =
330 --
331 /*******************************************************************/
332 FUNCTION isMember(
333 m_user_id IN NUMBER,
334 m_group_id IN NUMBER
335 )
336 RETURN NUMBER
337 IS
338 m_temp NUMBER;
339 return_value NUMBER;
340 BEGIN
341 SELECT
342 min(fnd_user.user_id)
343 INTO
344 m_temp
345 FROM
346 fnd_user,
347 jtf_rs_resource_extns,
348 jtf_rs_group_members
349 WHERE
350 jtf_rs_group_members.resource_id=jtf_rs_resource_extns.resource_id
351 AND
352 fnd_user.user_id=m_user_id
353 AND
354 jtf_rs_group_members.group_id=m_group_id
355 AND jtf_rs_group_members.DELETE_FLAG <>'Y'
356 AND jtf_rs_resource_extns.START_DATE_ACTIVE <= sysdate
357 AND NVL(jtf_rs_resource_extns.END_DATE_ACTIVE, sysdate) >= sysdate
358 AND (
359 (jtf_rs_resource_extns.source_id = fnd_user.employee_id
360 AND
361 jtf_rs_resource_extns.category ='EMPLOYEE')
362 OR
363 (jtf_rs_resource_extns.source_id = fnd_user.customer_id
364 AND
365 jtf_rs_resource_extns.category ='PARTY'));
366
367
368 -- set return value to TRUE if something is found.
369 if (m_temp = m_user_id) THEN
370 return_value := 1;
371 ELSE
372 return_value := 0;
373 END IF;
374
375 RETURN return_value;
376 END isMember;
377
378 /**************************** CREATE WF PROCESS ********************************/
379 -- This is an internal procedure to create a process.
380 /*******************************************************************************/
381
382 PROCEDURE Create_Wf_Process(
383 p_set_id IN NUMBER,
384 p_set_number IN VARCHAR2,
385 p_command IN VARCHAR2,
386 p_flow_details_id IN NUMBER,
387 p_group_id IN NUMBER,
388 p_solution_title IN VARCHAR2
389 ) IS
390
391 p_itemtype VARCHAR2(20) := 'CS_KB_W1';
392 wf_process VARCHAR2(20) := 'WFPROCESS';
393 p_itemkey VARCHAR(20) := p_set_id;
394 x_step_code varchar2(30);
395 x_step_meaning varchar2(80);
396 l_pub VARCHAR2(2000);
397 l_obs VARCHAR2(2000);
398
399
400 BEGIN
401
402 -- Create workflow process.
403 WF_ENGINE.CreateProcess(p_itemtype, p_itemkey, wf_process);
404
405 -- Set Attributes.
406 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey, 'SETNO', p_set_number);
407 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey, 'SETID', p_set_id);
408 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey, 'COMMAND', p_command);
409 IF (p_command = 'PUB') THEN
410 l_pub := fnd_message.GET_STRING('CS','CS_KB_WF_PUB');
411 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey,
412 'COMMAND_DISPLAY', l_pub); -- 'Published');
413 ELSIF (p_command = 'OBS') THEN
414 l_obs := fnd_message.GET_STRING('CS','CS_KB_WF_OBS');
415 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey,
416 'COMMAND_DISPLAY', l_obs); --'Obsoleted');
417 END IF;
418 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey, 'LANGUAGE', FND_GLOBAL.CURRENT_LANGUAGE);
419
420 IF (p_flow_details_id is not null) THEN
421 WF_ENGINE.SetItemAttrNumber(p_itemtype, p_itemkey, 'FLOW_DETAILS_ID', p_flow_details_id);
422 select step into x_step_code
423 from cs_kb_wf_flow_details
424 where flow_details_id = p_flow_details_id;
425
426 SELECT MAX(MEANING)
427 INTO x_step_meaning
428 FROM CS_LOOKUPS
429 WHERE lookup_code = x_step_code
430 AND lookup_type = 'CS_KB_STATUS';
431
432 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey, 'FLOWSTEP', x_step_meaning);
433
434 END IF;
435
436 IF (p_group_id is not null) THEN
437 WF_ENGINE.SetItemAttrNumber(p_itemtype, p_itemkey, 'GROUPID', p_group_id);
438 END IF;
439
440 IF (p_solution_title is not null) THEN
441 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey, 'SOLTITLE', p_solution_title);
442 END IF;
443
444 -- Start workflow process.
445 wf_engine.StartProcess(p_itemtype, p_itemkey);
446
447 EXCEPTION
448 WHEN OTHERS THEN
449 RAISE;
450 END Create_Wf_Process;
451
452 /**************************** CREATE REJECT TO AUTHOR PROCESS ********************************/
453 -- This is an internal procedure to create a process to reject to author.
454 /*******************************************************************************/
455
456 PROCEDURE Create_Reject_Process(
457 p_set_id IN NUMBER,
458 p_set_number IN VARCHAR2,
459 p_solution_title IN VARCHAR2,
460 p_author IN VARCHAR2
461 ) IS
462
463 p_itemtype VARCHAR2(20) := 'CS_KB_W1';
464 wf_process VARCHAR2(20) := 'REJPROCESS';
465 p_itemkey VARCHAR(200) := to_char(p_set_id) || '-' || p_author; --7117561, 7047779
466
467 BEGIN
468 -- Create workflow process.
469 WF_ENGINE.CreateProcess(p_itemtype, p_itemkey, wf_process);
470
471 -- Set Attributes.
472 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey, 'SETNO', p_set_number);
473 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey, 'SETID', p_set_id);
474 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey, 'LANGUAGE', FND_GLOBAL.CURRENT_LANGUAGE);
475 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey, 'AUTHOR', p_author);
476
477 -- Set the From field:
478 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey, 'NOTFROM', FND_GLOBAL.User_Name);
479
480 IF (p_solution_title is not null) THEN
481 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey, 'SOLTITLE', p_solution_title);
482 END IF;
483
484 -- Start workflow process.
485 wf_engine.StartProcess(p_itemtype, p_itemkey);
486
487 EXCEPTION
488 WHEN OTHERS THEN
489 RAISE;
490 END Create_Reject_Process;
491
492 /**************************** END WF *******************************************/
493 -- This procedure is basically an empty space to place any actions that are
494 -- desired to happen when a workflow step is complete.
495 -- Called within workflow
496 /*******************************************************************************/
497 PROCEDURE End_Wf(
498 p_itemtype IN VARCHAR2,
499 p_itemkey IN VARCHAR2,
500 p_actid IN NUMBER,
501 p_funcmode IN VARCHAR2,
502 p_result OUT NOCOPY VARCHAR2
503 ) IS
504
505 BEGIN
506 -- Place anything here
507 --COMMIT;
508 null;
509 END;
510
511 /**************************** Expire Detail ********************/
512 -- This Procedure is used to "soft-delete" a detail line from the
513 -- cs_kb_wf_flow_details table
514 --
515 -- VARIABLES
516 -- p_flow_details_id
517 -- p_result: p_flow_details_id = completed successfully, -1 = error
518 /*******************************************************************/
519 PROCEDURE Expire_Detail(
520 p_flow_details_id IN NUMBER,
521 p_result OUT NOCOPY NUMBER
522 )
523 IS
524 uid NUMBER := fnd_global.user_id;
525 dt DATE := SYSDATE;
526 BEGIN
527
528 UPDATE CS_KB_WF_FLOW_DETAILS
529 SET end_date = SYSDATE-1,
530 last_updated_by = uid,
531 last_update_date = dt
532 WHERE
533 flow_details_id = p_flow_details_id;
534
535 --COMMIT;
536 p_result := p_flow_details_id;
537 EXCEPTION
538 WHEN OTHERS THEN
539 p_result := -1;
540 END Expire_Detail;
541
542 /**************************** Expire Flow ********************/
543 -- This Procedure is used to "soft-delete" a flow from the
544 -- cs_kb_wf_flows_b table
545 --
546 -- VARIABLES
547 -- p_flow_id
548 -- p_result: 1 = completed successfully, -1 = error
549 /*******************************************************************/
550 PROCEDURE Expire_Flow(
551 p_flow_id IN NUMBER,
552 p_result OUT NOCOPY NUMBER
553 )
554 IS
555 uid NUMBER := fnd_global.user_id;
556 dt DATE := SYSDATE;
557
558 CURSOR CHECK_FLOW_EXITS (flowId NUMBER) IS
559 SELECT count(*)
560 FROM CS_KB_WF_FLOWS_B
561 WHERE flow_id = flowId;
562
563 CURSOR CHECK_DEFAULT_FLOWS (flowId NUMBER) IS
564 SELECT count(*)
565 FROM fnd_profile_options o,
566 fnd_profile_option_values ov
567 WHERE o.profile_option_name = 'CS_KB_DEFAULT_FLOW'
568 AND o.profile_option_id = ov.profile_option_id
569 AND ov.PROFILE_OPTION_VALUE = to_char(flowId)
570 AND ov.application_id = 170;
571
572 v_def_count NUMBER := 0;
573 v_count NUMBER := 0;
574
575 BEGIN
576 OPEN CHECK_FLOW_EXITS(p_flow_id);
577 FETCH CHECK_FLOW_EXITS INTO v_count;
578 CLOSE CHECK_FLOW_EXITS;
579
580 IF v_count = 1 THEN
581
582 OPEN CHECK_DEFAULT_FLOWS(p_flow_id);
583 FETCH CHECK_DEFAULT_FLOWS INTO v_def_count;
584 CLOSE CHECK_DEFAULT_FLOWS;
585
586 IF v_def_count = 0 THEN
587
588 UPDATE CS_KB_WF_FLOWS_B
589 SET end_date = dt,
590 last_updated_by = uid,
591 last_update_date = dt
592 WHERE
593 flow_id = p_flow_id;
594
595 p_result := 1;
596 ELSE --Flow is set against a Prof Option, so cant end date
597 p_result := -3;
598 END IF;
599 ELSE -- Invalid Flow id passed to api
600 p_result := -1;
601 END IF;
602
603 EXCEPTION
604 WHEN OTHERS THEN
605 p_result := -2;
606 END Expire_Flow;
607
608
609 /**************************** Enable Flow ********************/
610 -- This Procedure is used to re-activate an End-Dated Flow
611 -- in the cs_kb_wf_flows_b table
612 --
613 -- VARIABLES
614 -- p_flow_id
615 -- p_result: 1 = completed successfully, -1 = error
616 /*******************************************************************/
617 PROCEDURE Enable_Flow( p_flow_id IN NUMBER,
618 p_result OUT NOCOPY NUMBER ) IS
619 uid NUMBER := fnd_global.user_id;
620 dt DATE := SYSDATE;
621
622 CURSOR CHECK_FLOW_EXITS (flowId NUMBER) IS
623 SELECT count(*)
624 FROM CS_KB_WF_FLOWS_B
625 WHERE flow_id = flowId;
626
627 v_count NUMBER := 0;
628
629 BEGIN
630
631 OPEN CHECK_FLOW_EXITS(p_flow_id);
632 FETCH CHECK_FLOW_EXITS INTO v_count;
633 CLOSE CHECK_FLOW_EXITS;
634
635 IF v_count = 1 THEN
636
637 UPDATE CS_KB_WF_FLOWS_B
638 SET end_date = null,
639 last_updated_by = uid,
640 last_update_date = dt
641 WHERE flow_id = p_flow_id;
642
643 p_result := 1;
644 ELSE -- Invalid Flow id passed to api
645 p_result := -1;
646 END IF;
647
648 EXCEPTION
649 WHEN OTHERS THEN
650 p_result := -2;
651 END Enable_Flow;
652
653 /**************************** Get Actions ********************/
654 -- This Procedure is used to get a list of actions
655 --
656 -- VARIABLES
657 -- p_action_code = group ids
658 -- p_action_name = group names
659 /*******************************************************************/
660 PROCEDURE Get_Actions(
661 p_action_code OUT NOCOPY JTF_VARCHAR2_TABLE_100,
662 p_action_name OUT NOCOPY JTF_VARCHAR2_TABLE_100)
663 IS
664 l_resp_id number;
665 l_application_id number := 170;
666 l_region_code varchar2(30) := 'CS_KB_WF_ACTION';
667 l_user_id number := fnd_global.user_id;
668 region_name varchar2(50);
669 empty_str varchar2(30) := ' ';
670 items_table jtf_region_pub.ak_region_items_table;
671 code JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
672 name JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
673 BEGIN
674
675 l_resp_id := FND_PROFILE.VALUE_SPECIFIC(
676 'JTF_PROFILE_DEFAULT_RESPONSIBILITY',
677 l_user_id,
678 null,
679 null);
680
681 jtf_region_pub.get_region(l_region_code,
682 l_application_id,
683 l_resp_id,
684 empty_str,
685 region_name,
686 empty_str,
687 items_table);
688 FOR l in 1..items_table.count LOOP
689 name.extend;
690 name(l) := jtf_region_pub.get_region_item_name(
691 items_table(l).attribute_code,
692 l_region_code);
693 END LOOP;
694
695 code.extend;
696 code(1) := 'NOT';
697 code.extend;
698 code(2) := 'PUB';
699 code.extend;
700 code(3) := 'OBS';
701
702 p_action_code := code;
703 p_action_name := name;
704 EXCEPTION
705 when others then
706 -- return empty array
707 p_action_code := code;
708 p_action_name := name;
709
710 END Get_Actions;
711
712
713
714 /**************************** Get All Groups ********************/
715 -- This Procedure is used to get all groups ids and names so that
716 -- the GUI can populate a drop down list.
717 --
718 -- VARIABLES
719 -- p_group_id = group ids
720 -- p_group_name = group names
721 /*******************************************************************/
722 PROCEDURE Get_All_Groups(
723 p_group_id OUT NOCOPY JTF_NUMBER_TABLE,
724 p_group_name OUT NOCOPY JTF_VARCHAR2_TABLE_100
725 )
726 IS
727
728 -- Create the table variables to hold returnable info.
729 t_group_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
730 t_group_name JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
731
732 -- Temp variables to use to populate table variables
733 gi NUMBER;
734 gn JTF_RS_GROUPS_TL.GROUP_NAME%TYPE;
735
736 counter NUMBER := 1;
737
738 CURSOR All_Groups IS
739 SELECT g.GROUP_ID,
740 g.GROUP_NAME
741 FROM JTF_RS_GROUPS_VL g,
742 JTF_RS_GROUP_USAGES u
743 WHERE g.GROUP_ID = u.GROUP_ID
744 AND u.USAGE = 'ISUPPORT'
745 AND NVL(g.END_DATE_ACTIVE, sysdate) >= sysdate
746 ORDER BY g.GROUP_NAME;
747
748 BEGIN
749 OPEN All_Groups;
750 LOOP
751
752 -- Get values from cursor.
753 FETCH All_Groups INTO gi,gn;
754 EXIT WHEN All_Groups%NOTFOUND;
755
756 -- Extending tables one.
757 t_group_id.extend;
758 t_group_name.extend;
759
760
761 -- Setting table variables to value of temp variables
762 t_group_id(counter) := gi;
763 t_group_name(counter) := gn;
764
765 -- Increment counter.
766 counter := counter + 1;
767 END LOOP;
768 CLOSE All_Groups;
769
770 p_group_id := t_group_id;
771 p_group_name := t_group_name;
772
773 EXCEPTION
774 WHEN OTHERS THEN
775 CLOSE All_Groups;
776 RAISE;
777 END;
778
779 /**************************** Get All Steps *****************/
780 -- This Procedure is used to get a list of all the available
781 -- steps. This procedure is intended for use with the admin
782 -- pages used to set up flows.
783 --
784 -- WRAPPER FOR GET_STEP_LIST
785 --
786 -- VARIABLES
787 -- p_step = a list of all of the available step codes
788 -- p_step_names = a list of all the names that correspond to the
789 -- above list of codes.
790 /*******************************************************************/
791
792 PROCEDURE Get_All_Steps(
793 p_step OUT NOCOPY JTF_VARCHAR2_TABLE_100,
794 p_step_names OUT NOCOPY JTF_VARCHAR2_TABLE_100
795 )
796 IS
797 BEGIN
798 Get_Step_List(0,p_step,p_step_names);
799 END Get_All_Steps;
800
801 /**************************** GET FLOW DETAILS *******************/
802 -- This procedure provides the details of a specific flow based on
803 -- the input. This is used in the cskstat.jsp page.
804 -- VARIABLES:
805 -- p_flow_id (input to get results for)
806 -- p_flow_details_id - id that matches the sets_b table
807 -- p_order_num
808 -- p_step - this is the fnd lookup code
809 -- p_group_id - id of the jtf_group this is assigned to
810 -- p_action - NOT(NOTIFY),PUB(PUBLISH),OBS(OBSOLETE)
811 /*******************************************************************/
812 PROCEDURE Get_Flow_Details(
813 p_flow_id IN NUMBER,
814 p_flow_details_id OUT NOCOPY JTF_NUMBER_TABLE,
815 p_order_num OUT NOCOPY JTF_NUMBER_TABLE,
816 p_step OUT NOCOPY JTF_VARCHAR2_TABLE_100,
817 p_group_id OUT NOCOPY JTF_NUMBER_TABLE,
818 p_action OUT NOCOPY JTF_VARCHAR2_TABLE_100
819 )
820 IS
821 --tables to return
822 t_flow_details_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
823 t_order_num JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
824 t_step JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
825 t_group_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
826 t_action JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
827
828
829 CURSOR DETAILS_TABLE IS
830 SELECT
831 FD.FLOW_DETAILS_ID,
832 FD.ORDER_NUM,
833 FD.STEP,
834 FD.GROUP_ID,
835 FD.ACTION
836 FROM
837 CS_KB_WF_FLOW_DETAILS FD
838 WHERE
839 FLOW_ID = p_flow_id
840 AND
841 (BEGIN_DATE <= SYSDATE OR BEGIN_DATE IS NULL)
842 AND
843 (END_DATE >= SYSDATE OR END_DATE IS NULL)
844 ORDER BY
845 ORDER_NUM ASC;
846
847 -- Temp variables.
848 did CS_KB_WF_FLOW_DETAILS.flow_details_id%TYPE; -- temp flow details id.
849 orn NUMBER; -- temp order number
850 sta CS_KB_WF_FLOW_DETAILS.step%TYPE; -- temp step
851 gid NUMBER; -- temp group id
852 act CS_KB_WF_FLOW_DETAILS.action%TYPE; -- temp action
853 counter NUMBER := 1;
854 BEGIN
855 OPEN DETAILS_TABLE;
856 LOOP
857
858 -- Get values from cursor.
859 FETCH DETAILS_TABLE INTO did,orn,sta,gid,act;
860 EXIT WHEN DETAILS_TABLE%NOTFOUND;
861
862
863 -- Extending tables one.
864 t_flow_details_id.extend;
865 t_order_num.extend;
866 t_step.extend;
867 t_group_id.extend;
868 t_action.extend;
869
870
871 -- Setting table variables to value of temp variables
872 t_flow_details_id(counter) := did;
873 t_order_num(counter) := orn;
874 t_step(counter) := sta;
875 t_group_id(counter) := gid;
876 t_action(counter) := act;
877
878
879 counter := counter + 1;
880 END LOOP;
881 CLOSE DETAILS_TABLE;
882
883 p_flow_details_id := t_flow_details_id;
884 p_order_num := t_order_num;
885 p_step := t_step;
886 p_group_id := t_group_id;
887 p_action := t_action;
888
889 EXCEPTION
890 WHEN OTHERS THEN
891 CLOSE DETAILS_TABLE;
892 RAISE;
893 END Get_Flow_Details;
894
895 /**************************** GET FLOWS *************************/
896 -- This procedure provides the list of possible flows for the use
897 -- of the UI creating/editting flows
898 --
899 -- VARIABLES
900 -- p_flow_id (id that is needed when updates are made)
901 -- p_flow_name (name to display)
902 /*******************************************************************/
903 PROCEDURE Get_Flows(
904 p_flow_id OUT NOCOPY JTF_NUMBER_TABLE,
905 p_flow_name OUT NOCOPY JTF_VARCHAR2_TABLE_100
906 )
907 IS
908 --tables to return
909 t_flow_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
910 t_flow_name JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
911
912 CURSOR FLOWS_TABLE IS
913 select b.flow_id, t.name
914 from cs_kb_wf_flows_b b,
915 cs_kb_wf_flows_tl t
916 where b.flow_id = t.flow_id
917 and t.language = userenv('LANG')
918 and exists (select flow_id from cs_kb_wf_flow_details d
919 where d.flow_id = b.flow_id
920 and sysdate between nvl(d.begin_date, sysdate-1)
921 and nvl(d.end_date, sysdate+1)
922 )
923 and sysdate < nvl(end_date, sysdate+1)
924 ORDER BY t.NAME ASC;
925
926 -- Temp variables.
927 fid CS_KB_WF_FLOWS_TL.flow_id%TYPE; -- temp step.
928 fna CS_KB_WF_FLOWS_TL.name%TYPE; -- temp name
929 counter NUMBER := 1;
930 BEGIN
931 OPEN FLOWS_TABLE;
932 LOOP
933
934 -- Get values from cursor.
935 FETCH FLOWS_TABLE INTO fid,fna;
936 EXIT WHEN FLOWS_TABLE%NOTFOUND;
937
938
939 -- Extending tables one.
940 t_flow_id.extend;
941 t_flow_name.extend;
942
943 -- Setting table variables to value of temp variables
944 t_flow_id(counter) := fid;
945 t_flow_name(counter) := fna;
946
947
948 counter := counter + 1;
949 END LOOP;
950 CLOSE FLOWS_TABLE;
951
952 p_flow_id := t_flow_id;
953 p_flow_name := t_flow_name;
954
955 EXCEPTION
956 WHEN OTHERS THEN
957 CLOSE FLOWS_TABLE;
958 RAISE;
959 END Get_Flows;
960
961 /**************************** GET PERMISSIONS *****************/
962 -- Given a user_id and set_id, determine the level of permissions
963 -- that a user has to the solution. This is mainly used with the
964 -- manual accept ability.
965 --
966 -- Results are as follows:
967 -- 0 = no permissions
968 -- 1 = can accept solution task (currently in wf)
969 -- 2 = user owns it.
970 -- VARIABLES
971 -- p_set_id.
972 -- p_user_id.
973 -- p_results.
974 /*******************************************************************/
975
976 PROCEDURE Get_Permissions(
977 p_set_id IN NUMBER,
978 p_user_id IN NUMBER,
979 p_results OUT NOCOPY NUMBER
980 )
981 IS
982 lockedBy NUMBER;
983 status VARCHAR2(10);
984 fdid NUMBER; -- flow details id
985 setNo VARCHAR2(30);
986 BEGIN
987 -- Getting information about the record
988 CS_KB_SOLUTION_PVT.Get_Set_Details(p_set_id,setNo,status,fdid,lockedBy);
989
990 -- If this user owns the item
991 IF (lockedBy = p_user_id) THEN
992 p_results := 2;
993 -- If workflow owns this item
994 ELSIF (lockedBy = -1) THEN
995 -- IF (hasPermission(fdid, p_user_id, p_set_id) = 1) THEN
996 -- lyao:remove p_set_id param so that it skipps the profile check
997 IF (hasPermission(fdid, p_user_id) = 1) THEN
998 p_results := 1;
999 ELSE
1000 p_results := 0;
1001 END IF;
1002 ELSE
1003 p_results := 0;
1004 END IF;
1005 END Get_Permissions;
1006
1007 /*********************************************************************
1008 for a published soln, it may have gone thru multiple workflows.
1009 Only people who belong to any of the resource groups of those
1010 workflows can see a CHECKOUT button. When they click on this button,
1011 lock will be chcked. (p_results=1)
1012 Otherwise, no button. (p_results=0)
1013 If already locked_by this user, still show CHECK OUT.
1014 ************************************************************************/
1015 PROCEDURE Get_Permissions_for_PUB_soln(
1016 p_set_number IN VARCHAR2,
1017 p_user_id IN NUMBER,
1018 p_results OUT NOCOPY NUMBER ) IS
1019
1020 -- cursor for all distinct groups for all flows this solns has gone thru
1021 CURSOR groups IS
1022 SELECT DISTINCT details2.GROUP_ID
1023 FROM cs_kb_wf_flow_details details1,
1024 cs_kb_wf_flow_details details2,
1025 cs_kb_wf_flows_b flows,
1026 cs_kb_sets_b sets
1027 WHERE
1028 sets.SET_NUMBER = p_set_number
1029 AND sets.FLOW_DETAILS_ID = details1.FLOW_DETAILS_ID
1030 AND details1.FLOW_ID = flows.FLOW_ID
1031 AND flows.FLOW_ID = details2.FLOW_ID;
1032
1033 x_group_id number;
1034
1035 BEGIN
1036
1037 p_results := 0;
1038
1039 OPEN groups;
1040 LOOP
1041 FETCH groups INTO x_group_id;
1042 EXIT WHEN groups%NOTFOUND;
1043 IF (isMember(p_user_id, x_group_id) = 1) THEN
1044 p_results := 1;
1045 EXIT;
1046 END IF;
1047 END LOOP;
1048 CLOSE groups;
1049
1050 EXCEPTION
1051 WHEN OTHERS THEN
1052 p_results :=0;
1053
1054 END Get_Permissions_for_PUB_soln;
1055
1056
1057 /**************************** Get Step List *****************/
1058 -- This Procedure is used to get a list of steps to be displayed
1059 -- as drop-down list.
1060 --
1061 -- This procedure is for internal use... see wrappers
1062 --
1063 -- VARIABLES
1064 -- p_restriction = 0: no restriction, 1: no actions besides NOT
1065 -- p_step = a list of all of the available step codes
1066 -- p_step_names = a list of all the names that correspond to the
1067 -- above list of codes.
1068 /*******************************************************************/
1069
1070 PROCEDURE Get_Step_List(
1071 p_restriction IN NUMBER,
1072 p_step OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1073 p_step_names OUT NOCOPY JTF_VARCHAR2_TABLE_100
1074 )
1075 IS
1076
1077 -- Create the table variables to hold returnable info.
1078 t_step JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1079 t_step_names JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1080
1081 steps INTEGER;
1082 cursorReturn INTEGER;
1083
1084 SQL1 VARCHAR2(1000) := 'SELECT DISTINCT CS_LOOKUPS.LOOKUP_CODE, CS_LOOKUPS.MEANING FROM CS_LOOKUPS ';
1085 SQL2 VARCHAR2(1000) := ' WHERE CS_LOOKUPS.LOOKUP_TYPE = :T AND TRUNC(SYSDATE) BETWEEN Trunc(NVL(start_date_active,sysdate)) AND Trunc(NVL(end_date_active,sysdate)) AND enabled_flag = ''Y'''; --5198112
1086 SQL3 VARCHAR2(100) := ' ORDER BY CS_LOOKUPS.MEANING ';
1087
1088 -- Temp variables.
1089 st CS_LOOKUPS.LOOKUP_CODE%TYPE; -- temp step.
1090 na CS_LOOKUPS.MEANING%TYPE; -- temp names
1091
1092 -- Counter variable.
1093 counter NUMBER := 1;
1094
1095 BEGIN
1096
1097 IF(p_restriction = 1) THEN
1098 SQL1 := SQL1 || ' ,CS_KB_WF_FLOW_DETAILS ';
1099 SQL2 := SQL2 || ' AND CS_KB_WF_FLOW_DETAILS.STEP = CS_LOOKUPS.LOOKUP_CODE AND CS_KB_WF_FLOW_DETAILS.ACTION = :N ';
1100 END IF;
1101 -- open and parse statement
1102 steps := DBMS_SQL.OPEN_CURSOR;
1103 DBMS_SQL.PARSE(steps, SQL1||SQL2||SQL3, DBMS_SQL.V7);
1104 DBMS_SQL.BIND_VARIABLE(steps,':T', 'CS_KB_STATUS');
1105
1106 IF(p_restriction = 1) THEN
1107 DBMS_SQL.BIND_VARIABLE(steps,':N', 'NOT');
1108 END IF;
1109
1110 -- Define output variables
1111 DBMS_SQL.DEFINE_COLUMN(steps, 1, st,100);
1112 DBMS_SQL.DEFINE_COLUMN(steps, 2, na,100);
1113
1114
1115 --Execute cursor
1116 cursorReturn := DBMS_SQL.EXECUTE(steps);
1117
1118
1119 /******* LOOPING ********************/
1120
1121 LOOP
1122 IF DBMS_SQL.FETCH_ROWS(steps) = 0 THEN
1123 EXIT;
1124 END IF;
1125 -- Loading variables with column values
1126 DBMS_SQL.COLUMN_VALUE(steps, 1, st);
1127 DBMS_SQL.COLUMN_VALUE(steps, 2, na);
1128
1129 -- Extending tables one.
1130 t_step.extend;
1131 t_step_names.extend;
1132
1133 -- Setting table variables to value of temp variables
1134 t_step(counter) := st;
1135 t_step_names(counter) := na;
1136
1137 -- Increment counter.
1138 counter := counter + 1;
1139 END LOOP;
1140 DBMS_SQL.CLOSE_CURSOR(steps);
1141
1142 IF (p_restriction = 1) THEN
1143 t_step.extend;
1144 t_step_names.extend;
1145 t_step(counter) := 'REJ';
1146 t_step_names(counter) := getActionName('REJ');
1147 counter := counter + 1;
1148
1149 t_step.extend;
1150 t_step_names.extend;
1151 t_step(counter) := 'SAV';
1152 t_step_names(counter) := getActionName('SAV');
1153 END IF;
1154
1155 -- set OUT variables.
1156 p_step := t_step;
1157 p_step_names := t_step_names;
1158 EXCEPTION
1159 WHEN OTHERS THEN
1160 IF (steps is not null) THEN
1161 DBMS_SQL.CLOSE_CURSOR(steps);
1162 END IF;
1163 RAISE;
1164 END Get_Step_List;
1165
1166 PROCEDURE Get_Next_Step(
1167 p_flow_details_id IN NUMBER,
1168 p_next_details_id OUT NOCOPY NUMBER
1169 ) IS
1170 x_flow_id NUMBER;
1171 x_order_num NUMBER;
1172 x_next_order_num NUMBER;
1173
1174 BEGIN
1175 x_flow_id := getFlowId(p_flow_details_id);
1176
1177 select order_num
1178 into x_order_num
1179 from CS_KB_WF_FLOW_DETAILS
1180 where FLOW_DETAILS_ID = p_flow_details_id;
1181
1182
1183 select MIN(order_num)
1184 into x_next_order_num
1185 from CS_KB_WF_FLOW_DETAILS
1186 where flow_id = x_flow_id
1187 and (BEGIN_DATE <= SYSDATE OR BEGIN_DATE IS NULL)
1188 and (END_DATE >= SYSDATE OR END_DATE IS NULL)
1189 and order_num > x_order_num;
1190
1191 select flow_details_id
1192 into p_next_details_id
1193 from CS_KB_WF_FLOW_DETAILS
1194 where flow_id = x_flow_id
1195 and (BEGIN_DATE <= SYSDATE OR BEGIN_DATE IS NULL)
1196 and (END_DATE >= SYSDATE OR END_DATE IS NULL)
1197 and order_num = x_next_order_num;
1198
1199
1200 EXCEPTION
1201 WHEN OTHERS THEN
1202 p_next_details_id := -1;
1203
1204 END Get_Next_Step;
1205
1206 FUNCTION Is_Step_Disabled ( P_FLOW_ID NUMBER,
1207 P_FLOW_DETAILS_ID NUMBER,
1208 P_FLOW_DETAILS_ORDER NUMBER,
1209 P_CURRENT_FLOW_DETAILS_ID NUMBER) RETURN VARCHAR2
1210 IS
1211
1212 CURSOR Get_First_Step IS
1213 SELECT Flow_Details_Id
1214 FROM CS_KB_WF_FLOW_DETAILS
1215 WHERE Flow_id = P_FLOW_ID
1216 AND sysdate between nvl(begin_date, sysdate-1)
1217 and nvl(end_date, sysdate+1)
1218 AND Order_Num = ( SELECT min(order_num)
1219 FROM CS_KB_WF_FLOW_DETAILS
1220 WHERE Flow_id = P_FLOW_ID
1221 AND sysdate between nvl(begin_date, sysdate-1)
1222 and nvl(end_date, sysdate+1) );
1223
1224 CURSOR Get_Current_Detail_Order IS
1225 SELECT order_num
1226 FROM CS_KB_WF_FLOW_DETAILS
1227 WHERE FLOW_DETAILS_ID = P_CURRENT_FLOW_DETAILS_ID
1228 AND (BEGIN_DATE <= SYSDATE OR BEGIN_DATE IS NULL)
1229 AND (END_DATE >= SYSDATE OR END_DATE IS NULL);
1230
1231
1232 l_current_step NUMBER;
1233 l_current_order NUMBER;
1234 l_next_step NUMBER;
1235 l_first_step NUMBER;
1236
1237 Disabled VARCHAR2(1) := 'Y';
1238 Enabled VARCHAR2(1) := 'N';
1239 l_return VARCHAR2(1);
1240
1241 BEGIN
1242 l_return := Disabled;
1243
1244 IF P_CURRENT_FLOW_DETAILS_ID is not null THEN
1245 --dbms_output.put_line('Current Step Id is not null');
1246 -- Check if current step is still active, if Yes return Order Number
1247 OPEN Get_Current_Detail_Order;
1248 FETCH Get_Current_Detail_Order INTO l_current_order;
1249 CLOSE Get_Current_Detail_Order;
1250
1251 --dbms_output.put_line('Current Step Order='||l_current_order);
1252 IF l_current_order IS NOT NULL THEN
1253 -- Current Step is still active
1254
1255 IF l_current_order >= P_FLOW_DETAILS_ORDER THEN
1256
1257 -- Grant this step as submitable
1258 RETURN Enabled;
1259
1260 ELSE -- Step is beyond current current flow position
1261
1262 -- If next step then it is submitable
1263 CS_KB_WF_PKG.Get_Next_Step( p_flow_details_id => P_CURRENT_FLOW_DETAILS_ID,
1264 p_next_details_id => l_next_step);
1265
1266 IF l_next_step = P_FLOW_DETAILS_ID THEN
1267 -- Grant this step as submitable
1268 RETURN Enabled;
1269 ELSE
1270 RETURN Disabled;
1271 END IF;
1272
1273 END IF;
1274
1275 ELSE -- The Current Flow Step is Inactive
1276
1277 -- If this is the first Step then enable
1278 OPEN Get_First_Step;
1279 FETCH Get_First_Step INTO l_first_step;
1280 CLOSE Get_First_Step;
1281
1282 IF P_FLOW_DETAILS_ID = l_first_step THEN
1283 -- Grant this step as submitable
1284 RETURN Enabled;
1285 ELSE
1286 RETURN Disabled;
1287 END IF;
1288
1289 END IF;
1290
1291 ELSE --current step is null
1292 --dbms_output.put_line('Current Step Id is null');
1293 RETURN Disabled;
1294 END IF;
1295
1296 RETURN l_return;
1297
1298 END Is_Step_Disabled;
1299
1300
1301 PROCEDURE Get_Prev_Step(
1302 p_flow_details_id IN NUMBER,
1303 p_next_details_id OUT NOCOPY NUMBER
1304 ) IS
1305 x_flow_id NUMBER;
1306 x_order_num NUMBER;
1307 x_prev_order_num NUMBER;
1308
1309 BEGIN
1310 x_flow_id := getFlowId(p_flow_details_id);
1311
1312 select order_num
1313 into x_order_num
1314 from CS_KB_WF_FLOW_DETAILS
1315 where FLOW_DETAILS_ID = p_flow_details_id;
1316
1317
1318 select MAX(order_num)
1319 into x_prev_order_num
1320 from CS_KB_WF_FLOW_DETAILS
1321 where flow_id = x_flow_id
1322 and (BEGIN_DATE <= SYSDATE OR BEGIN_DATE IS NULL)
1323 and (END_DATE >= SYSDATE OR END_DATE IS NULL)
1324 and order_num < x_order_num;
1325
1326 select flow_details_id
1327 into p_next_details_id
1328 from CS_KB_WF_FLOW_DETAILS
1329 where flow_id = x_flow_id
1330 and order_num = x_prev_order_num;
1331
1332
1333 EXCEPTION
1334 WHEN OTHERS THEN
1335 p_next_details_id := -1;
1336
1337 END Get_Prev_Step;
1338
1339 /**************************** INSERT DETAIL **********************/
1340 -- This procedure provides the ability to add a new flow
1341 --
1342 -- VARIABLES
1343 -- p_flow_id
1344 -- p_order_num
1345 -- p_step
1346 -- p_group_id
1347 -- p_action
1348 -- p_flow_details_id: flow_details_id or -1 if failed.
1349 /*******************************************************************/
1350
1351 PROCEDURE Insert_Detail(
1352 p_flow_id IN NUMBER,
1353 p_order_num IN NUMBER,
1354 p_step IN VARCHAR2,
1355 p_group_id IN NUMBER,
1356 p_action IN VARCHAR2,
1357 p_flow_details_id OUT NOCOPY NUMBER
1358 )
1359 IS
1360 --temp vars
1361 uid NUMBER := fnd_global.user_id;
1362 dt DATE := SYSDATE;
1363 BEGIN
1364
1365 -- Get next available details id number
1366 SELECT
1367 cs_kb_wf_flow_details_s.NextVal
1368 INTO
1369 p_flow_details_id
1370 FROM
1371 DUAL;
1372
1373 -- Insert data
1374 INSERT INTO CS_KB_WF_FLOW_DETAILS(flow_details_id,
1375 flow_id,
1376 step,
1377 order_num,
1378 action,
1379 group_id,
1380 created_by,
1381 creation_date,
1382 last_updated_by,
1383 last_update_date)
1384 VALUES(p_flow_details_id,
1385 p_flow_id,
1386 p_step,
1387 p_order_num,
1388 p_action,
1389 p_group_id,
1390 uid,
1391 dt,
1392 uid,
1393 dt);
1394
1395 --COMMIT;
1396
1397 EXCEPTION
1398 WHEN OTHERS THEN
1399 p_flow_details_id := -1;
1400 END Insert_Detail;
1401
1402 /**************************** INSERT FLOW *************************/
1403 -- This procedure provides the ability to add a new flow
1404 --
1405 -- VARIABLES
1406 -- p_flow_name (name to display)
1407 -- p_flow_id (the new id, -1 if already taken or other error)
1408 -- bug 1966494: -3 for duplicated flow name
1409 /*******************************************************************/
1410 PROCEDURE Insert_Flow(
1411 p_flow_name IN VARCHAR2,
1412 p_flow_id OUT NOCOPY NUMBER
1413 ) IS
1414 --temp vars
1415 uid NUMBER := fnd_global.user_id;
1416 dt DATE := SYSDATE;
1417 x_count number;
1418 BEGIN
1419
1420 SELECT count(1)
1421 INTO x_count
1422 FROM CS_KB_WF_FLOWS_B b, CS_KB_WF_FLOWS_TL tl
1423 WHERE b.FLOW_ID = tl.FLOW_ID
1424 AND tl.NAME = p_flow_name
1425 AND tl.LANGUAGE = FND_GLOBAL.CURRENT_LANGUAGE;
1426
1427 --check for duplicated flow name
1428 IF (x_count > 0) THEN
1429 p_flow_id := -3;
1430 ELSE
1431 SELECT
1432 cs_kb_wf_flows_s.NextVal
1433 INTO
1434 p_flow_id
1435 FROM
1436 DUAL;
1437
1438 INSERT INTO CS_KB_WF_FLOWS_B(flow_id,created_by,creation_date,
1439 last_updated_by,last_update_date)
1440 VALUES(p_flow_id,uid,dt,uid,dt);
1441
1442 INSERT INTO CS_KB_WF_FLOWS_TL (flow_id,
1443 name,
1444 creation_date,
1445 created_by,
1446 last_update_date,
1447 last_update_login,
1448 last_updated_by,
1449 language,
1450 source_lang)
1451 SELECT p_flow_id,
1452 p_flow_name,
1453 dt,
1454 uid,
1455 dt,
1456 uid,
1457 uid,
1458 l.language_code,
1459 USERENV('LANG')
1460 FROM fnd_languages l
1461 WHERE l.installed_flag IN ('I', 'B')
1462 AND NOT EXISTS
1463 (SELECT NULL
1464 FROM CS_KB_WF_FLOWS_TL t
1465 WHERE t.flow_id = p_flow_id
1466 AND t.language = l.language_code);
1467
1468
1469 END IF;
1470
1471 EXCEPTION
1472 WHEN OTHERS THEN
1473 p_flow_id := -1;
1474 END Insert_Flow;
1475
1476 /**************************** START WF ***************************/
1477 -- This procedure is used start the workflow process. It is used
1478 -- regarless if the solution is in the wf process yet or ready to be
1479 -- published.
1480 --
1481 -- THIS ONE IS CALLED EXTERNALLY!
1482 --
1483 -- NOTES
1484 -- If users want to skip any workflow and want to publish the solutions
1485 -- directly, we go go directly to publish step, two conditions must be present:
1486 -- 1) p_set_id must have a valid value (max set_id for set_number)
1487 -- 2) p_new_step must be null (nowhere to send it to)
1488 -- If both values are null, it will throw an error. If there is a value
1489 -- for p_set_id when p_new_step is not null, it will be ignored.
1490 --
1491 -- VARIABLES
1492 -- p_set_number
1493 -- p_set_id: NULL unless need to go directly to publish
1494 -- p_new_step: flow details id of new step to move to. NULL if need
1495 -- to go directly to publish
1496 -- p_result: 1 if all fine, 0 if no permissions, -1 if general error,
1497 -- -2 bad input information, -3 general error in Post_pub_obs
1498 -- INTERNAL VARS
1499 -- p_command = cs_kb_wf_flow_details.action
1500 --
1501 /*******************************************************************/
1502 PROCEDURE Start_wf(
1503 p_set_number IN VARCHAR2,
1504 p_set_id IN NUMBER ,
1505 p_new_step IN NUMBER ,
1506 p_results OUT NOCOPY NUMBER,
1507 p_errormsg OUT NOCOPY VARCHAR2
1508 ) IS
1509
1510 solution_title CS_KB_SETS_TL.NAME%TYPE;
1511 p_group_id NUMBER;
1512 p_command VARCHAR2(30);
1513 p_locked_by NUMBER;
1514 current_id NUMBER;
1515 user_id NUMBER := fnd_global.user_id;
1516 set_id NUMBER := p_set_id;
1517 x_original_author_id FND_USER.USER_ID%TYPE;
1518 x_author FND_USER.USER_NAME%TYPE;
1519 x_new_step number;
1520
1521 BEGIN
1522
1523 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1524 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_WF_PKG.Start_Wf.begin',
1525 ' Current User='||user_id||' Step='||p_new_step);
1526 END IF;
1527
1528 IF (p_new_step is null) THEN
1529 IF (p_set_id is null) THEN
1530 RAISE BAD_INFORMATION_SUPPLIED;
1531 ELSE
1532
1533 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1534 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.CS_KB_WF_PKG.Start_Wf',
1535 'Direct Publish - '||p_set_id);
1536 END IF;
1537
1538 -- no step, intepreted as workflow disabled. Publish directly.
1539 -- do NOT kick off workflow
1540 p_command := 'PUB';
1541 p_locked_by := null;
1542 set_id := CS_KB_SOLUTION_PVT.clone_solution(p_set_number,
1543 p_command, p_new_step, p_locked_by);
1544
1545 END IF;
1546 ELSE
1547
1548 current_id := CS_KB_SOLUTION_PVT.locked_by(p_set_id);
1549
1550 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1551 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.CS_KB_WF_PKG.Start_Wf',
1552 'Standard Flow Submission. Current Lock - '||current_id);
1553 END IF;
1554
1555 -- If lock is not held by anyone, by workflow, by the current user,
1556 IF ((current_id is null) or (current_id = -1) or (current_id = user_id)) THEN
1557
1558 -- check whether it is Rejected to Author
1559 IF (p_new_step = -999) THEN
1560 p_command := 'REJ';
1561 x_new_step := null;
1562 ELSE
1563 p_command := getAction(p_new_step);
1564 p_group_id := getstepGroup(p_new_step);
1565 x_new_step := p_new_step;
1566 END IF;
1567
1568 -- Determine locked_by for each command
1569 IF (p_command = 'NOT') THEN
1570 p_locked_by := -1;
1571 ELSIF (p_command = 'REJ') THEN
1572 -- set locked by last updater
1573 -- 7117561
1574 SELECT created_by
1575 INTO x_original_author_id
1576 FROM cs_kb_sets_b
1577 WHERE set_id = ( SELECT MAX(set_id) -- Bug fix: 7159784 - made it max(set_id) to get the last updater
1578 FROM cs_kb_sets_b
1579 WHERE set_number = p_set_number
1580 AND status = 'SAV' --Bugfix7228667 - Added the Status to change the Locked by
1581 ) ;
1582 -- 7117561
1583 p_locked_by := x_original_author_id;
1584 ELSE
1585 -- PUB/OBS
1586 p_locked_by := null;
1587 END IF;
1588
1589 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1590 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.CS_KB_WF_PKG.Start_Wf',
1591 'Before Clone - '||p_set_number||' - '
1592 ||p_command||' - '
1593 ||x_new_step||' - '
1594 ||p_locked_by);
1595 END IF;
1596
1597 -- clone solution
1598 set_id := CS_KB_SOLUTION_PVT.clone_solution(p_set_number,
1599 p_command, x_new_step, p_locked_by);
1600
1601 ELSE
1602 -- someone forced a lock on it
1603 RAISE PERMISSION_ERROR;
1604 END IF;
1605
1606 -- Send notifications
1607 solution_title := CS_KB_SOLUTION_PVT.get_solution_title(p_set_id);
1608
1609 IF (p_command <> 'REJ') THEN
1610 Create_Wf_Process(set_id,p_set_number, p_command, p_new_step,
1611 p_group_id, solution_title );
1612 ELSE
1613 /*select user_name
1614 into x_author
1615 from fnd_user
1616 where user_id = x_original_author_id;*/
1617 FOR get_user IN --Bugfix7117561 - get the users to whom we need to send notifications
1618 (
1619 SELECT DISTINCT user_id, user_name
1620 FROM
1621 (
1622 SELECT user_id, user_name
1623 FROM cs_kb_sets_b a, fnd_user b
1624 WHERE set_id IN (
1625 SELECT MIN(set_id)
1626 FROM cs_kb_sets_b
1627 WHERE set_number = p_set_number
1628 )
1629 AND a.created_by = b.user_id
1630 UNION
1631 SELECT user_id, user_name
1632 FROM cs_kb_sets_b a, fnd_user b
1633 WHERE set_id IN (
1634 SELECT MAX(set_id) --Bug fix:7159784 - made it max(set_id) to get the last updater
1635 FROM cs_kb_sets_b
1636 WHERE set_number = p_set_number AND
1637 status = 'SAV' --Bug fix:7228667
1638 )
1639 AND a.created_by = b.user_id
1640 )
1641 )
1642 LOOP
1643
1644 IF (CS_KB_SECURITY_PVT.IS_COMPLETE_SOLUTION_VISIBLE(x_original_author_id,set_id)= 'TRUE') THEN
1645
1646 Create_Reject_Process(set_id,p_set_number, solution_title, get_user.user_name);
1647 END IF;
1648 END LOOP;
1649 END IF;
1650
1651 END IF;
1652
1653 p_results := 1;
1654 p_errormsg := null;
1655
1656 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1657 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_WF_PKG.Start_Wf.end',
1658 'Status=Success');
1659 END IF;
1660
1661 EXCEPTION
1662 WHEN PERMISSION_ERROR THEN
1663
1664 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1665 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_WF_PKG.Start_Wf.EXCEPTION.Permission_Error',
1666 ' Locking User='||Current_id||
1667 ' Current User='||User_Id);
1668 END IF;
1669
1670 p_results := 0;
1671 p_errormsg := 'No permission error';
1672 WHEN BAD_INFORMATION_SUPPLIED THEN
1673
1674 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1675 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_WF_PKG.Start_Wf.EXCEPTION.Bad_Info',
1676 ' New Step='||p_new_step||
1677 ' Set Id='||p_set_id);
1678 END IF;
1679
1680 p_results := -2;
1681 p_errormsg := 'Bad information supplied';
1682 WHEN OTHERS THEN
1683
1684 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1685 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_WF_PKG.Start_Wf.UNEXPECTED',
1686 ' Error= '||sqlerrm);
1687 END IF;
1688
1689 p_results := -1;
1690 p_errormsg := SQLERRM;
1691 END Start_wf;
1692
1693
1694
1695 /**************************** START WF PROCESSING ******************/
1696 -- This procedure is used start the workflow process. It is used
1697 -- regarless if the solution is in the wf process yet or ready to be
1698 -- published. Always called by workflow node
1699 /*******************************************************************/
1700 PROCEDURE Start_wf_processing(
1701 p_itemtype IN VARCHAR2,
1702 p_itemkey IN VARCHAR2,
1703 p_actid IN NUMBER,
1704 p_funcmode IN VARCHAR2,
1705 p_result OUT NOCOPY VARCHAR2
1706 ) IS
1707
1708 command VARCHAR2(30) := WF_ENGINE.GetItemAttrText(p_itemtype,p_itemkey,'COMMAND');
1709 set_id NUMBER := WF_ENGINE.GetItemAttrNumber(p_itemtype,p_itemkey,'SETID');
1710 l_group_id NUMBER := WF_ENGINE.GetItemAttrNumber(p_itemtype,p_itemkey,'GROUPID');
1711
1712 adhoc_role VARCHAR2(320);
1713 adhoc_role_name VARCHAR2(360);
1714
1715 CURSOR GET_GROUP_NAME (v_group_id NUMBER) IS
1716 SELECT GROUP_NAME
1717 FROM JTF_RS_GROUPS_VL
1718 WHERE GROUP_ID = v_group_id;
1719
1720
1721 CURSOR Get_Group_Members (v_group_id IN NUMBER) IS
1722 SELECT DISTINCT
1723 fnd_user.user_name,
1724 fnd_user.user_id
1725 FROM fnd_user,
1726 jtf_rs_resource_extns,
1727 jtf_rs_group_members
1728 WHERE jtf_rs_group_members.resource_id=jtf_rs_resource_extns.resource_id
1729 AND jtf_rs_group_members.group_id = v_group_id
1730 AND jtf_rs_group_members.DELETE_FLAG <> 'Y'
1731 AND jtf_rs_resource_extns.START_DATE_ACTIVE <= sysdate
1732 AND NVL(jtf_rs_resource_extns.END_DATE_ACTIVE, sysdate) >= sysdate
1733 AND( ( jtf_rs_resource_extns.source_id = fnd_user.employee_id
1734 AND jtf_rs_resource_extns.category = 'EMPLOYEE' )
1735 OR (jtf_rs_resource_extns.source_id = fnd_user.customer_id
1736 AND jtf_rs_resource_extns.category = 'PARTY' )
1737 );
1738
1739 BEGIN
1740
1741 -- Loading process type name
1742 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey, 'PROCTYPENAME', getActionName(command));
1743
1744 OPEN GET_GROUP_NAME (l_group_id);
1745 FETCH GET_GROUP_NAME INTO adhoc_role_name;
1746 CLOSE GET_GROUP_NAME;
1747
1748 IF adhoc_role_name IS NULL THEN
1749 adhoc_role := NULL;
1750 ELSE
1751 adhoc_role := 'SOLNID'||to_char(set_id)||'-'||TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS');
1752 --Bug 3588397 - USE OF SYSTIMESTAMP IS NOT COMPATABLE WITH 8.1.7
1753 --adhoc_role := 'SOLNID'||to_char(set_id)||'-'||TO_CHAR(SYSTIMESTAMP,'DDMMYYYYHH24MISSFF1');
1754 END IF;
1755
1756 WF_DIRECTORY.CREATEADHOCROLE(adhoc_role,adhoc_role_name,null,null,null,'MAILHTML',null,null,null,'ACTIVE',sysdate+365);
1757
1758 WF_ENGINE.SetItemAttrText(p_itemtype,p_itemkey,'RECIPIENTGROUP',adhoc_role);
1759
1760 FOR Users IN Get_Group_Members (l_group_id) LOOP
1761
1762 IF ((inCategory(Users.user_id,set_id) = 1) OR
1763 (inProduct(Users.user_id,set_id) = 1)) THEN
1764 -- Check if user can view the 'Complete' Solution
1765 IF (CS_KB_SECURITY_PVT.IS_COMPLETE_SOLUTION_VISIBLE(Users.user_id,set_id)= 'TRUE') THEN
1766 WF_DIRECTORY.ADDUSERSTOADHOCROLE(adhoc_role, Users.user_name);
1767 END IF;
1768
1769 END IF;
1770
1771 END LOOP;
1772
1773 -- Set the From field:
1774 WF_ENGINE.SetItemAttrText(p_itemtype, p_itemkey, 'NOTFROM', FND_GLOBAL.User_Name);
1775
1776 -- Send workflow down correct path according to command determined.
1777 IF (command = 'PUB') OR (command = 'OBS') THEN
1778 p_result := 'COMPLETE:PUB_OBS';
1779 ELSE
1780 p_result := 'COMPLETE:NOTIFY';
1781 END IF;
1782
1783 EXCEPTION
1784 WHEN BAD_INFORMATION_SUPPLIED THEN
1785 wf_engine.SetItemAttrNumber(p_itemtype,p_itemkey,'TRACKING','NO VALID COMMAND FOUND');
1786 RAISE;
1787 WHEN OTHERS THEN
1788 wf_engine.SetItemAttrNumber(p_itemtype,p_itemkey,'TRACKING','Unspecified error in Start_wf_processing procedure');
1789 RAISE;
1790 END Start_wf_processing;
1791
1792
1793 /**************************** UPDATE DETAIL *************************/
1794 -- This procedure provides the ability to add a new flow
1795 --
1796 -- VARIABLES
1797 -- p_flow_details_id - provided when a list of details is requested
1798 -- p_order_num
1799 -- p_step
1800 -- p_group_id
1801 -- p_action
1802 -- p_result: p_flow_details_id = completed successfully, 0 = completed but nothing was
1803 -- updated, -1 = error
1804 /*******************************************************************/
1805 PROCEDURE Update_Detail(
1806 p_flow_details_id IN NUMBER,
1807 p_order_num IN NUMBER,
1808 p_step IN VARCHAR2,
1809 p_group_id IN NUMBER,
1810 p_action IN VARCHAR2,
1811 p_result OUT NOCOPY NUMBER
1812 )
1813 IS
1814 uid NUMBER := fnd_global.user_id;
1815 dt DATE := SYSDATE;
1816 temp NUMBER; --temp variable to see if id exists
1817
1818 BEGIN
1819 -- MIN() is used to force a value to return and avoid NDF error
1820 SELECT
1821 MIN(flow_details_id)
1822 INTO
1823 temp
1824 FROM
1825 cs_kb_wf_flow_details
1826 WHERE
1827 flow_details_id = p_flow_details_id;
1828
1829 IF(temp is not null) THEN
1830 UPDATE CS_KB_WF_FLOW_DETAILS
1831 SET order_num = p_order_num,
1832 step = p_step,
1833 group_id = p_group_id,
1834 action = p_action,
1835 last_updated_by = uid,
1836 last_update_date = dt
1837 WHERE
1838 flow_details_id = p_flow_details_id;
1839
1840 p_result := p_flow_details_id;
1841 ELSE
1842 p_result := 0;
1843 END IF;
1844 EXCEPTION
1845 WHEN OTHERS THEN
1846 p_result := -1;
1847 END Update_Detail;
1848
1849
1850
1851
1852
1853
1854 /**************************** UPDATE DETAIL ADMIN ******************/
1855 -- This procedure is a wrapper for both update and insert procedures
1856 -- to be used by the admin pages.
1857 --
1858 -- VARIABLES
1859 -- p_flow_details_id - provided when a list of details is requested
1860 -- p_order_num
1861 -- p_step
1862 -- p_group_id
1863 -- p_action
1864 -- p_result: flow_details_id OR -1 = error
1865 /*******************************************************************/
1866
1867 PROCEDURE Update_Detail_Admin(
1868 p_flow_details_id IN NUMBER,
1869 p_flow_id IN NUMBER,
1870 p_order_num IN NUMBER,
1871 p_step IN VARCHAR2,
1872 p_group_id IN NUMBER,
1873 p_action IN VARCHAR2,
1874 p_flag IN VARCHAR2,
1875 p_result OUT NOCOPY NUMBER
1876 ) IS
1877 result NUMBER;
1878 BEGIN
1879
1880 -- DELETE
1881 IF (p_flag = 'DELETE') THEN
1882 Expire_Detail(p_flow_details_id, result);
1883 ELSE
1884 -- IF NOT A DELETE THEN LOOK FOR OTHER ACTION
1885 -- INSERT
1886 IF (p_flow_details_id = -2 ) THEN
1887 Insert_Detail(p_flow_id, p_order_num, p_step, p_group_id, p_action, result);
1888 -- UPDATE
1889 ELSE
1890 Update_Detail(p_flow_details_id, p_order_num, p_step, p_group_id, p_action, result);
1891 END IF;
1892 END IF;
1893
1894 p_result := result;
1895 END Update_Detail_Admin;
1896
1897
1898
1899
1900
1901
1902 /**************************** UPDATE FLOW *************************/
1903 -- This procedure provides the ability to add a new flow
1904 --
1905 -- VARIABLES
1906 -- p_flow_id
1907 -- p_flow_name (name to display)
1908 -- p_result: 1 = completed successfully, 0 = completed but nothing was
1909 -- updated, -1 = error, -3=duplicated file name
1910 /*******************************************************************/
1911 PROCEDURE Update_Flow(
1912 p_flow_id IN NUMBER,
1913 p_flow_name IN VARCHAR2,
1914 p_result OUT NOCOPY NUMBER
1915 ) IS
1916 uid NUMBER := fnd_global.user_id;
1917 dt DATE := SYSDATE;
1918 temp NUMBER;
1919 x_count number;
1920 BEGIN
1921
1922 SELECT count(1)
1923 INTO x_count
1924 FROM CS_KB_WF_FLOWS_B b, CS_KB_WF_FLOWS_TL tl
1925 WHERE b.FLOW_ID = tl.FLOW_ID
1926 AND b.FLOW_ID <> p_flow_id
1927 AND tl.NAME = p_flow_name
1928 AND tl.LANGUAGE = FND_GLOBAL.CURRENT_LANGUAGE;
1929
1930 --check for duplicated flow name
1931 IF (x_count > 0) THEN
1932 p_result := -3;
1933 ELSE
1934 -- MIN() is used to force a value to return and avoid NDF error
1935 SELECT
1936 MIN(flow_id)
1937 INTO
1938 temp
1939 FROM
1940 cs_kb_wf_flows_tl
1941 WHERE
1942 flow_id = p_flow_id;
1943
1944 IF(temp is not null) THEN
1945 UPDATE CS_KB_WF_FLOWS_TL
1946 SET name = p_flow_name,
1947 last_updated_by = uid,
1948 last_update_date = dt,
1949
1950 source_lang = USERENV('LANG')
1951
1952 WHERE
1953 flow_id = p_flow_id
1954 AND USERENV('LANG') IN (language, source_lang);
1955
1956 --language = FND_GLOBAL.CURRENT_LANGUAGE;
1957
1958 UPDATE CS_KB_WF_FLOWS_B
1959 SET last_updated_by = uid,
1960 last_update_date = dt
1961 WHERE
1962 flow_id = p_flow_id;
1963
1964 p_result := 1;
1965 ELSE
1966 p_result := 0;
1967 END IF;
1968 END IF;
1969 EXCEPTION
1970 WHEN OTHERS THEN
1971 RAISE;
1972 p_result := -1;
1973 END Update_Flow;
1974
1975 /**************************** UPDATE FLOW ADMIN ********************/
1976 -- This procedure is a wrapper of both insert and update functionality
1977 -- designed for the admin pages
1978 --
1979 -- VARIABLES
1980 -- p_flow_id
1981 -- p_flow_name (name to display)
1982 -- p_result: flow_id OR -1 = error, OR -3 = duplicated flow name
1983 /*******************************************************************/
1984
1985 PROCEDURE Update_Flow_Admin(
1986 p_flow_id IN NUMBER,
1987 p_flow_name IN VARCHAR2,
1988 p_result OUT NOCOPY NUMBER
1989 )
1990 IS
1991 result NUMBER;
1992 BEGIN
1993 -- INSERT, result is new flow_id or -1/-3
1994 IF (p_flow_id is null) THEN
1995 Insert_Flow(p_flow_name, result);
1996
1997 -- UPDATE, result is 0/1/-1/-3
1998 ELSE
1999 Update_Flow(p_flow_id, p_flow_name, result);
2000 END IF;
2001
2002 IF ((result = 0) OR (result = 1)) THEN
2003 p_result := p_flow_id;
2004 ELSE
2005 p_result := result;
2006 END IF;
2007 END Update_Flow_admin;
2008
2009
2010 PROCEDURE Add_Language IS
2011
2012 BEGIN
2013
2014 DELETE FROM CS_KB_WF_FLOWS_TL t
2015 WHERE NOT EXISTS
2016 (SELECT NULL
2017 FROM CS_KB_WF_FLOWS_B b
2018 WHERE b.flow_id = t.flow_id
2019 );
2020
2021 UPDATE CS_KB_WF_FLOWS_TL T SET (
2022 name,
2023 description
2024 ) = (SELECT
2025 b.name,
2026 b.description
2027 FROM CS_KB_WF_FLOWS_TL b
2028 WHERE b.flow_id = t.flow_id
2029 AND b.language = t.source_lang)
2030 WHERE (
2031 t.flow_id,
2032 t.language
2033 ) IN (SELECT
2034 subt.flow_id,
2035 subt.language
2036 FROM CS_KB_WF_FLOWS_TL subb, CS_KB_WF_FLOWS_TL subt
2037 WHERE subb.flow_id = subt.flow_id
2038 AND subb.language = subt.source_lang
2039 AND (subb.name <> subt.name
2040 OR (subb.name IS NULL AND subt.name IS NOT NULL)
2041 OR (subb.name IS not NULL AND subt.name IS NULL)
2042 OR subb.description <> subt.description
2043 OR (subb.description IS NULL AND subt.description IS NOT NULL)
2044 OR (subb.description IS NOT NULL AND subt.description IS NULL)
2045 ));
2046
2047 INSERT INTO CS_KB_WF_FLOWS_TL (
2048 flow_id,
2049 name,
2050 description,
2051 creation_date,
2052 created_by,
2053 last_update_date,
2054 last_update_login,
2055 last_updated_by,
2056 language,
2057 source_lang
2058 ) SELECT
2059 b.flow_id,
2060 b.name,
2061 b.description,
2062 b.creation_date,
2063 b.created_by,
2064 b.last_update_date,
2065 b.last_update_login,
2066 b.last_updated_by,
2067 l.language_code,
2068 b.source_lang
2069 FROM CS_KB_WF_FLOWS_TL b, fnd_languages l
2070 WHERE l.installed_flag IN ('I', 'B')
2071 AND b.language = USERENV('LANG')
2072 AND NOT EXISTS
2073 (SELECT NULL
2074 FROM CS_KB_WF_FLOWS_TL t
2075 WHERE t.flow_id = b.flow_id
2076 AND t.language = l.language_code);
2077
2078 END Add_Language;
2079
2080 -- Package Body CS_KB_WF
2081 END;
[Home] [Help]