[Home] [Help]
PACKAGE BODY: APPS.IEX_CASE_OWNER_PUB
Source
1 PACKAGE BODY IEX_CASE_OWNER_PUB AS
2 /* $Header: iexpcalb.pls 120.4.12000000.1 2007/01/17 23:20:23 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_CASE_OWNER_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexpcalb.pls';
6
7 /* this will be the outside wrapper for the concurrent program to call the "creation" in batch
8 */
9 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
10 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
11
12 PROCEDURE IEX_CASE_OWNER_CONCUR(ERRBUF OUT NOCOPY VARCHAR2,
13 RETCODE OUT NOCOPY VARCHAR2,
14 p_list_name IN VARCHAR2)
15
16 IS
17
18 l_api_version NUMBER := 1.0;
19 l_return_status VARCHAR2(1);
20 l_msg_count NUMBER;
21 l_msg_data VARCHAR2(2000);
22 l_list_name VARCHAR2(50) := p_list_name;
23
24 BEGIN
25
26 -- IF PG_DEBUG < 10 THEN
27 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
28 iex_debug_pub.LogMessage ('Starting IEX_CASE_OWNER_CONCUR');
29 END IF;
30
31 IF p_list_name IS NULL THEN
32
33 -- IF PG_DEBUG < 10 THEN
34 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
35 iex_debug_pub.LogMessage ('IEX_CASE_OWNER_CONCUR: ' || 'p_list_name is null and calling RUN_LOAD_BALANCE');
36 END IF;
37
38 IEX_CASE_OWNER_PUB.Run_Load_Balance(p_api_version => 1.0,
39 p_commit => FND_API.G_TRUE,
40 p_init_msg_list => FND_API.G_FALSE,
41 x_return_status => l_return_status,
42 x_msg_count => l_msg_count,
43 x_msg_data => l_msg_data);
44 -- IF PG_DEBUG < 10 THEN
45 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
46 iex_debug_pub.LogMessage ('IEX_CASE_OWNER_CONCUR: ' || 'after RUN_LOAD_BALANCE:return_status=' || l_return_status);
47 END IF;
48
49 END IF;
50
51 RETCODE := l_return_status;
52
53 END IEX_CASE_OWNER_CONCUR;
54
55 /* this procedure will run load balance on table iex_cases_all_b
56 */
57 PROCEDURE Run_Load_Balance(p_api_version IN NUMBER,
58 p_commit IN VARCHAR2,
59 p_init_msg_list IN VARCHAR2,
60 x_return_status OUT NOCOPY VARCHAR2,
61 x_msg_count OUT NOCOPY NUMBER,
62 x_msg_data OUT NOCOPY VARCHAR2)
63 IS
64 l_api_name VARCHAR2(50) := 'Run_Load_Balance';
65 l_RETURN_STATUS VARCHAR2(30) ;
66 l_MSG_COUNT NUMBER ;
67 l_MSG_DATA VARCHAR2(100) ;
68 l_api_version NUMBER := 1.0;
69
70 l_login number:= fnd_global.login_id;
71 l_user NUMBER := FND_GLOBAL.USER_ID;
72
73 CURSOR c_parties IS
74 SELECT DISTINCT party_id
75 FROM iex_cases_all_b;
76
77 --Begin bug#5246309 schekuri 29-Jun-2006
78 --Changed the query to get the resource from hz_customer_profiles
79 CURSOR c_party_resource(p_party_id NUMBER) IS
80 SELECT ac.resource_id,0
81 FROM hz_customer_profiles hp, jtf_rs_resource_extns rs,ar_collectors ac
82 WHERE hp.party_id = p_party_id
83 and rs.resource_id = ac.resource_id
84 and hp.collector_id = ac.collector_id
85 and hp.cust_account_id=-1
86 and hp.site_use_id is null
87 and trunc(nvl(rs.end_date_active,sysdate)) >= trunc(sysdate)
88 and rs.user_id is not null
89 and ac.employee_id is not null
90 and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
91 and nvl(ac.status,'A') = 'A'
92 and nvl(hp.status,'A') = 'A'
93 group by ac.resource_id;
94
95 /*CURSOR c_party_resource(p_party_id NUMBER) IS
96 SELECT DISTINCT rs.resource_id, 0
97 FROM as_rpt_managers_v m, as_accesses acc, jtf_rs_resource_extns rs
98 WHERE m.person_id = acc.person_id
99 AND m.manager_person_id = rs.source_id
100 AND acc.customer_id = p_party_id
101 AND rs.start_date_active <= sysdate
102 AND rs.end_date_active > sysdate;*/
103
104 --End bug#5246309 schekuri 29-Jun-2006
105
106 CURSOR c_party_case_count(p_party_id NUMBER) IS
107 SELECT count(1)
108 FROM iex_cases_all_b
109 WHERE party_id = p_party_id;
110
111 CURSOR c_party_case_id(p_party_id NUMBER) IS
112 SELECT cas_id
113 FROM iex_cases_all_b
114 WHERE party_id = p_party_id;
115
116 TYPE number_tab_type IS TABLE OF NUMBER;
117 l_p_rs_id_tab number_tab_type;
118 l_p_rs_cnt_tab number_tab_type;
119 l_p_case_id_tab number_tab_type;
120 l_p_case_cnt NUMBER;
121
122 l_idx NUMBER := 0;
123 l_avg_cnt NUMBER := 0;
124
125 l_errmsg varchar2(1000);
126 l_count number;
127 BEGIN
128
129 -- IF PG_DEBUG < 10 THEN
130 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
131 iex_debug_pub.LogMessage ('starting RUN_LOAD_BALANCE');
132 END IF;
133
134 -- Standard Start of API savepoint
135 SAVEPOINT Run_Load_Balance_PVT;
136
137
138 -- Standard call to check for call compatibility.
139 IF NOT FND_API.Compatible_API_Call (l_api_version,
140 p_api_version,
141 l_api_name,
142 G_PKG_NAME)
143 THEN
144 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
145 END IF;
146
147 -- Initialize API return status to SUCCESS
148 l_return_status := FND_API.G_RET_STS_SUCCESS;
149
150 -- Api body
151 --
152
153 FOR r_party IN c_parties LOOP
154 -- IF PG_DEBUG < 10 THEN
155 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
156 iex_debug_pub.LogMessage('Run_Load_Balance: ' || 'in C_PARTIES LOOP:party_id=' || r_party.party_id);
157 END IF;
158
159
160 OPEN c_party_resource(r_party.party_id);
161 FETCH c_party_resource BULK COLLECT INTO l_p_rs_id_tab, l_p_rs_cnt_tab;
162
163 l_count := l_p_rs_id_tab.count;
164 -- IF PG_DEBUG < 10 THEN
165 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
166 iex_debug_pub.LogMessage('Run_Load_Balance: ' || 'after fetch:c_party_resource:count=' || l_count);
167 END IF;
168
169 CLOSE c_party_resource;
170 IF l_p_rs_id_tab.count > 0 THEN
171 OPEN c_party_case_count(r_party.party_id);
172 FETCH c_party_case_count INTO l_p_case_cnt;
173
174 -- IF PG_DEBUG < 10 THEN
175 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
176 iex_debug_pub.LogMessage('Run_Load_Balance: ' || 'after fetch of c_party_case_count:count=' || l_p_case_cnt);
177 END IF;
178
179 CLOSE c_party_case_count;
180
181 OPEN c_party_case_id(r_party.party_id);
182
183 l_avg_cnt := trunc(l_p_case_cnt / l_p_rs_id_tab.count);
184 -- IF PG_DEBUG < 10 THEN
185 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
186 iex_debug_pub.LogMessage('Run_Load_Balance: ' || 'average count=' || l_avg_cnt);
187 END IF;
188
189
190 l_idx := 1;
191 FETCH c_party_case_id BULK COLLECT INTO l_p_case_id_tab;
192 CLOSE c_party_case_id;
193
194 l_count := l_p_case_id_tab.count;
195 -- IF PG_DEBUG < 10 THEN
196 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
197 iex_debug_pub.LogMessage('Run_Load_Balance: ' || 'after fetch c_party_case_id:count=' || l_count);
198 END IF;
199
200 IF l_p_case_id_tab.count > 0 THEN
201 -- IF PG_DEBUG < 10 THEN
202 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
203 iex_debug_pub.LogMessage('Run_Load_Balance: ' || 'c_party_case_id found');
204 END IF;
205
206 FOR i in 1..l_p_case_cnt LOOP
207 -- IF PG_DEBUG < 10 THEN
208 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
209 iex_debug_pub.LogMessage('Run_Load_Balance: ' || 'before update:index_of_resource=' || l_idx || ':resource=' || l_p_rs_id_tab(l_idx)
210 || ':assigned_count=' || l_p_rs_cnt_tab(l_idx)
211 || ':case_id=' || l_p_case_id_tab(i));
212 END IF;
213
214 UPDATE iex_cases_all_b
215 SET owner_resource_id = l_p_rs_id_tab(l_idx)
216 WHERE cas_id = l_p_case_id_tab(i);
217
218 l_p_rs_cnt_tab(l_idx) := l_p_rs_cnt_tab(l_idx) + 1;
219
220 -- IF PG_DEBUG < 10 THEN
221 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
222 iex_debug_pub.LogMessage('Run_Load_Balance: ' || 'after update:index_of_resource=' || l_idx || ':resource=' || l_p_rs_id_tab(l_idx)
223 || ':assigned_count=' || l_p_rs_cnt_tab(l_idx)
224 || ':case_id=' || l_p_case_id_tab(i));
225 END IF;
226
227 IF l_p_rs_cnt_tab(l_idx) >= l_avg_cnt THEN
228 l_idx := l_idx + 1;
229 -- IF PG_DEBUG < 10 THEN
230 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
231 iex_debug_pub.LogMessage('Run_Load_Balance: ' || 'after increase index:idx=' || l_idx);
232 END IF;
233 END IF;
234
235 IF l_idx > l_p_rs_cnt_tab.count THEN
236 l_idx := 1;
237 l_avg_cnt := l_avg_cnt + 1;
238 -- IF PG_DEBUG < 10 THEN
239 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
240 iex_debug_pub.LogMessage('Run_Load_Balance: ' || 'after reset index:idx=' || l_idx || ':average_count=' || l_avg_cnt);
241 END IF;
242 END IF;
243
244 END LOOP; -- FOR i
245 END IF;
246 END IF;
247 END LOOP; -- FOR r_party
248
249 --
250 -- End of API body.
251 --
252
253 -- Standard check for p_commit
254 IF FND_API.to_Boolean(p_commit) THEN
255 COMMIT WORK;
256 END IF;
257
258 x_return_status := l_return_status ;
259 x_msg_Count := l_msg_count ;
260 x_msg_data := l_msg_data ;
261
262
263 -- IF PG_DEBUG < 10 THEN
264 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
265 iex_debug_pub.LogMessage ('end of RUN_LOAD_BALANCE main block');
266 END IF;
267
268 -- Standard call to get message count and if count is 1, get message info.
269 FND_MSG_PUB.Count_And_Get
270 ( p_count => x_msg_count,
271 p_data => x_msg_data
272 );
273
274 EXCEPTION
275 WHEN FND_API.G_EXC_ERROR THEN
276 l_errmsg := SQLERRM;
277 -- IF PG_DEBUG < 10 THEN
278 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
279 iex_debug_pub.LogMessage('Run_Load_Balance: ' || 'EXCEPTION:FND_API.G_EXC_ERROR:' || l_errmsg);
280 END IF;
281
282 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(P_API_NAME => L_API_NAME
283 ,P_PKG_NAME => G_PKG_NAME
284 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
285 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
286 ,X_MSG_COUNT => X_MSG_COUNT
287 ,X_MSG_DATA => X_MSG_DATA
288 ,X_RETURN_STATUS => X_RETURN_STATUS);
289
290 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
291 l_errmsg := SQLERRM;
292 -- IF PG_DEBUG < 10 THEN
293 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
294 iex_debug_pub.LogMessage('Run_Load_Balance: ' || 'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR:' || l_errmsg);
295 END IF;
296
297 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(P_API_NAME => L_API_NAME
298 ,P_PKG_NAME => G_PKG_NAME
299 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
300 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
301 ,X_MSG_COUNT => X_MSG_COUNT
302 ,X_MSG_DATA => X_MSG_DATA
303 ,X_RETURN_STATUS => X_RETURN_STATUS);
304 WHEN OTHERS THEN
305 l_errmsg := SQLERRM;
306 -- IF PG_DEBUG < 10 THEN
307 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
308 iex_debug_pub.LogMessage('Run_Load_Balance: ' || 'EXCEPTION:OTHERS:' || l_errmsg);
309 END IF;
310
311 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(P_API_NAME => L_API_NAME
312 ,P_PKG_NAME => G_PKG_NAME
313 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
314 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
315 ,X_MSG_COUNT => X_MSG_COUNT
316 ,X_MSG_DATA => X_MSG_DATA
317 ,X_RETURN_STATUS => X_RETURN_STATUS);
318
319 END Run_Load_Balance;
320
321 END IEX_CASE_OWNER_PUB;