1 PACKAGE BODY CS_KB_SOLN_IN_PROGRESS_PKG AS
2 /* $Header: cskbsipb.pls 115.2 2003/11/14 00:53:09 mkettle noship $ */
3
4 --ERRBUF = err messages
5
6 --RETCODE = 0 success, 1 = warning, 2=error
7
8
9 PROCEDURE CHECK_SOLN_IN_PROGRESS (
10 ERRBUF OUT NOCOPY VARCHAR2,
11 RETCODE OUT NOCOPY VARCHAR2 )
12 IS
13
14 CURSOR GET_IN_PROGRESS_SOLUTIONS IS
15 SELECT Soln.Set_id, Soln.Set_Number, Soln.Locked_By, Soln.FLow_Details_Id, Soln.Name,
16 FlowDetail.Group_id
17 FROM CS_KB_SETS_VL Soln,
18 CS_KB_WF_FLOW_DETAILS FlowDetail
19 WHERE Soln.STATUS IN ('SAV','NOT','REJ')
20 AND Soln.LATEST_VERSION_FLAG = 'Y'
21 AND Soln.Flow_Details_id = FlowDetail.Flow_Details_Id (+)
22 AND Soln.Locked_BY > -1;
23
24 l_complete_soln VARCHAR2(10);
25 l_user NUMBER := FND_GLOBAL.User_Id;
26 l_login NUMBER := FND_GLOBAL.Login_Id;
27
28 BEGIN
29
30 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Starting Concurrent Program to unlock Solutions at: '||sysdate);
31
32 /*
33 FND_FILE.PUT_LINE(FND_FILE.LOG,
34 'Solutions will be unlocked if the locking User nolonger has access
35 to the complete Solution. Access to the complete solution invloves
36 the User having the necessary Visibility to the Solution and all its
37 associated statements. The Solution must also reside in a Category
38 that has been associated to the users Category Group. ');
39 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
40 */
41
42 FOR Solutions IN GET_IN_PROGRESS_SOLUTIONS LOOP
43
44 IF ( CS_KB_SECURITY_PVT.IS_COMPLETE_SOLUTION_VISIBLE( Solutions.Locked_By,
45 Solutions.Set_id) = 'FALSE') THEN
46 -- If current Locking User Does Not have Complete access to the Solution,
47 -- then UnLock the Solution
48
49 UPDATE CS_KB_SETS_B
50 SET Locked_By = -1,
51 Lock_Date = NULL,
52 Last_Update_Date = sysdate,
53 Last_Updated_By = l_user,
54 Last_Update_Login = l_login
55 WHERE Set_Id = Solutions.Set_id;
56
57 IF Solutions.FLow_Details_Id IS NOT NULL THEN
58 -- For Solutions that are currently in a Flow we will create
59 -- a new WF process to re-notify the Resource Group that the
60 -- Solution is back in the queue and needs reassigning
61
62 BEGIN
63 CS_KB_WF_PKG.Create_Wf_Process( p_set_id => Solutions.Set_id,
64 p_set_number => Solutions.Set_Number,
65 p_command => 'NOT',
66 p_flow_details_id => Solutions.FLow_Details_Id,
67 p_group_id => Solutions.Group_Id,
68 p_solution_title => Solutions.Name );
69 EXCEPTION WHEN OTHERS THEN
70 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.GET_STRING('CS','CS_KB_NOT_RESEND_ERR')||
71 Solutions.Set_Number||' -'||substrb(sqlerrm,1,200));
72 END;
73
74 END IF;
75
76 FND_FILE.PUT_LINE(FND_FILE.LOG, Solutions.Set_Number||fnd_message.GET_STRING('CS','CS_KB_SOLN_UNLOCK'));
77
78 END IF;
79
80 END LOOP;
81
82 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Finished Concurrent Program to unlock Solutions at: '||sysdate);
83
84 COMMIT;
85
86 --ERRBUF := 'Success';
87
88 RETCODE := 0;
89
90
91 EXCEPTION
92 WHEN OTHERS THEN
93
94 RETCODE := 2;
95
96 ERRBUF := fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||SQLERRM;
97 FND_FILE.PUT_LINE(FND_FILE.LOG, ERRBUF);
98
99 END CHECK_SOLN_IN_PROGRESS;
100
101 END CS_KB_SOLN_IN_PROGRESS_PKG;
[Home] [Help]