1 PACKAGE BODY CCT_SRSEC_CHECK_PUB as
2 /* $Header: cctsrseb.pls 120.1 2005/11/14 14:01:03 ibyon noship $ */
3 procedure authenticate_agents(p_srnum IN Varchar2,
4 p_agentIDs IN OUT NOCOPY Varchar2,
5 p_isServerGroupID IN Varchar2,
6 x_return_status OUT NOCOPY Varchar2)
7 --procedure TestSRSecurityT(p_table IN OUT NOCOPY System.CCT_AGENT_RESP_APP_ID_NST )
8 IS
9 i BINARY_INTEGER;
10 totalRec BINARY_INTEGER;
11 status varchar2(1);
12 agentID NUMBER(15,0);
13 respID NUMBER(15,0);
14 appID NUMBER(15,0);
15 secFlag Varchar2(1);
16 p_table System.CCT_AGENT_RESP_APP_ID_NST;
17 p_obj System.CCT_AGENT_RESP_APP_ID_OBJ;
18 l_agentIDs Varchar2(32767);
19
20 --TYPE cur_typ IS REF CURSOR;
21 --c cur_typ;
22 --query_str VARCHAR2(32767);
23
24 CURSOR l_agents_csr IS
25 SELECT agent_id, resp_id, app_id FROM cct_agent_rt_stats
26 WHERE agent_id IN (p_agentIDs) AND attribute1 = 'T';
27
28 BEGIN
29 x_return_status := FND_API.G_FALSE;
30 If (p_agentIDs is null) THEN
31 return;
32 End If;
33
34
35 /* agentID:=obj.AGENT_ID; */
36 /* respID:=obj.RESPONSIBILITY_ID; */
37 /* appID:=obj.APPLICATION_ID; */
38 /* secFlag:=obj.SECURITY_YN_FLAG; */
39
40 p_table := System.CCT_AGENT_RESP_APP_ID_NST(System.CCT_AGENT_RESP_APP_ID_OBJ(0,0,0,''));
41 i := p_table.FIRST;
42 --tbd : change "F" to "T" as that's what we want here..
43 -- If p_isServerGroupID is true, then p_agentIDs represents "Super Server Group ID".
44 -- p_agentIDs := '('||p_agentIDs||')'; Bug 4283551 - do not use literal in sql. replaced with cursor
45 --query_str := 'SELECT agent_id, resp_id, app_id FROM cct_agent_rt_stats
46 -- WHERE agent_id IN'||p_agentIDs ||' AND attribute1 = ''T'' ';
47 --p_agentIDs := '';
48
49
50 OPEN l_agents_csr;
51 LOOP
52 FETCH l_agents_csr INTO agentID,respID,appID;
53 EXIT WHEN l_agents_csr%NOTFOUND;
54 p_obj := System.CCT_AGENT_RESP_APP_ID_OBJ(TO_NUMBER(agentID),TO_NUMBER(respID),TO_NUMBER(appID),'N');
55 p_table(i) := p_obj;
56 i := i + 1;
57 p_table.EXTEND();
58 --dbms_output.put_line('Value of aID='||agentID||' resp_id='||respID||' app_id='||appID);
59 -- process row here
60 END LOOP;
61 CLOSE l_agents_csr;
62 p_agentIDs := '';
63 --Last element is the 'null' element we initialized nested table.
64 --Since atleast one valid agentID will always be passed 'p_table' won't have count==0.
65 p_table.DELETE(p_table.LAST);
66
67 --Display the Nested Table Data.
68 --i := p_table.FIRST;
69 --while (i <= p_table.LAST) LOOP
70 --p_obj := p_table(i);
71 --dbms_output.put_line (' Value of varray is ' || p_obj.AGENT_ID ||' '|| p_obj.RESPONSIBILITY_ID||' '||p_obj.APPLICATION_ID);
72 --i := p_table.NEXT(i);
73 --END LOOP;
74
75 --It could happen that, p_table has 0 count. As none of agents were logged in.
76 totalRec := p_table.COUNT;
77 --dbms_output.put_line ('Value of runtime level is '||FND_LOG.G_CURRENT_RUNTIME_LEVEL);
78 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
79 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cct.plsql.cct_srsec_check_pub.authenticate_agents', 'No of Records in Nested table p_table is = '||totalRec);
80 end if;
81
82 if (totalRec < 1) then
83 return;
84 end if;
85
86 CCT_SRSEC_CHECK_PUB.CallSRSecurityCheck(p_srnum,p_table,status);
87
88 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
89 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cct.plsql.cct_srsec_check_pub.authenticate_agents', 'Status returned from CallSRSecurityCheck() API is = '||status );
90 end if;
91
92 --Error'd out in API or No SR Security Check API found so return.
93 if (status = FND_API.G_FALSE) then
94 return;
95 end if;
96 --Build comma seperated agent list from Nested Table returned from SR SEcurity check api.
97
98 --Display the Nested Table Data.
99 -- i := p_table.FIRST;
100 -- while (i <= p_table.LAST) LOOP
101 -- p_obj := p_table(i);
102 -- dbms_output.put_line ('YYY Value of varray is ' || p_obj.AGENT_ID ||' '|| p_obj.RESPONSIBILITY_ID||' '||p_obj.APPLICATION_ID||' '||p_obj.SECURITY_YN_FLAG);
103 -- i := p_table.NEXT(i);
104 -- END LOOP;
105
106
107 i := p_table.FIRST;
108 p_obj := p_table(i);
109 if ((p_obj is not null) AND (p_obj.SECURITY_YN_FLAG = 'Y') ) THEN
110 l_agentIDs := p_obj.AGENT_ID;
111 end if;
112
113 if (totalRec=1) then
114 if (l_agentIDs is not null) then
115 x_return_status := FND_API.G_TRUE;
116 p_agentIDs := l_agentIDs;
117 return;
118 else
119 p_agentIDs := '';
120 return;
121 end if;
122 end if;
123
124 i := p_table.NEXT(i);
125
126 while (i <= p_table.LAST) LOOP
127 p_obj := p_table(i);
128 if ((p_obj is not null) AND (p_obj.SECURITY_YN_FLAG = 'Y') ) THEN
129 l_agentIDs := l_agentIDs ||',' ||p_obj.AGENT_ID;
130 end if;
131 i := p_table.NEXT(i);
132 END LOOP;
133 --dbms_output.put_line (' Value of returned AgentID is ' ||l_agentIDs);
134 p_agentIDs := l_agentIDs;
135 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
136 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cct.plsql.cct_srsec_check_pub.authenticate_agents', 'AgentIDs returned are = '||p_agentIDs );
137 end if;
138 x_return_status := FND_API.G_TRUE;
139 --dbms_output.put_line('In TestSRSecurityT Proc '||SQLERRM(STATUS));
140 EXCEPTION
141 WHEN OTHERS THEN
142 FND_MESSAGE.SET_NAME('CCT','CCT_UNEXPECTED_ERROR');
143 FND_MESSAGE.SET_TOKEN(TOKEN =>'ERROR_CODE' , VALUE=>sqlcode);
144 FND_MESSAGE.SET_TOKEN(TOKEN =>'ERROR_MESSAGE', VALUE=>sqlerrm);
145 FND_MSG_PUB.add;
146
147 IF l_agents_csr%ISOPEN THEN
148 CLOSE l_agents_csr;
149 END IF;
150 x_return_status := FND_API.G_FALSE;
151 END authenticate_agents;
152
153 procedure CallSRSecurityCheck(p_srnum IN Varchar2,p_table in out NOCOPY system.CCT_AGENT_RESP_APP_ID_NST, x_return_status out NOCOPY varchar2)
154 IS
155 l_ver NUMBER(15,0);
156 l_srKeyName Varchar2(64);
157 status NUMERIC;
158 return_status varchar2(1);
159 BEGIN
160 x_return_status := FND_API.G_FALSE;
161 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
162 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cct.plsql.cct_srsec_check_pub.CallSRSecurityCheck.begin', '' );
163 end if;
164 BEGIN
165 SELECT Count(*) INTO l_ver
166 FROM cct_security_functions
167 WHERE application_id=170 AND version=1.0 ;
168 EXCEPTION
169 When Others Then
170 status := SQLCODE;
171 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
172 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cct.plsql.cct_srsec_check_pub.CallSRSecurityCheck', 'Exception while querying cct_security_functions table. '||SQLERRM(status) );
173 end if;
174
175 --dbms_output.put_line('In CallSRSecurityCheck Proc '||SQLERRM(STATUS));
176 return;
177 END;
178 -- Logic for executing correct version of API based on SR product installation
179 -- when we get to version 2.0 is ..
180 -- If (version 2.0 is present )
181 -- Execute an API for ver2.0
182 --else if (version 1.0 ispresent)
183 -- Execute an API for ver1.0;
184 -- Same pattern can be repeated when we get to version 3.0.
185
186 --Here ORS ensures that there is valid SR Number value before making call to
187 --authenticate_agents API.
188 if (l_ver is not null) then
189 l_srKeyName := CCT_INTERACTIONKEYS_PUB.KEY_SERVICE_REQUEST_NUMBER;
190 BEGIN
191 -- CCT_SRSEC_CHECK_PUB.validate_security(:1,:2,:3,:4);
192 EXECUTE IMMEDIATE
193 'BEGIN
194 sr_uwq_integ.validate_security(:1,:2,:3,:4);
195 END;'
196 USING l_srKeyName,p_srnum,IN OUT p_table, OUT x_return_status;
197 EXCEPTION
198 WHEN OTHERS THEN
199 status := SQLCODE;
200 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
201 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cct.plsql.cct_srsec_check_pub.CallSRSecurityCheck', 'Exception was raised. '||SQLERRM(status) );
202 end if;
203
204 --dbms_output.put_line('Exception while executing validate_security() Proc '||SQLERRM(STATUS));
205 return;
206 END;
207 --raise_application_error(-20000, sqlerrm || '. Could not add column') ;
208 ELSE
209 --Couldn't find an SR Security Check API so return.
210 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
211 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cct.plsql.cct_srsec_check_pub.CallSRSecurityCheck', 'Can not find Service Request Security Check API' );
212 end if;
213 return;
214 end if;
215
216 x_return_status := FND_API.G_TRUE;
217 END CallSRSecurityCheck;
218
219 END CCT_SRSEC_CHECK_PUB;
[Home] [Help]