[Home] [Help]
PACKAGE BODY: APPS.IEX_METRIC_CONCUR_PVT
Source
1 PACKAGE BODY IEX_METRIC_CONCUR_PVT AS
2 /* $Header: iexvmtcb.pls 120.4.12010000.2 2008/08/04 13:38:43 snuthala ship $ */
3
4 PG_DEBUG NUMBER;
5
6 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_METRIC_CONCUR_PVT';
7 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexvmtcb.pls';
8 G_METRIC_BATCH VARCHAR2(30):='BATCH';
9 G_LOGIN_ID NUMBER;
10 G_PROGRAM_ID NUMBER;
11 G_USER_ID NUMBER;
12
13 /* this will be called by the concurrent program to metric caculation in batch
14 */
15 Procedure Refresh_All(ERRBUF OUT NOCOPY VARCHAR2,
16 RETCODE OUT NOCOPY VARCHAR2,
17 P_ORG_ID IN NUMBER)
18
19 IS
20 l_return_status VARCHAR2(10);
21 l_msg_data VARCHAR2(32767);
22 l_msg_count NUMBER;
23
24 CURSOR c_party IS
25 SELECT distinct p.party_id
26 FROM hz_parties p, hz_cust_accounts ca
27 WHERE p.status = 'A'
28 AND ca.status = 'A'
29 AND ca.party_id = p.party_id;
30
31 CURSOR c_account IS
32 SELECT ca.cust_account_id, p.party_id
33 FROM hz_parties p, hz_cust_accounts ca
34 WHERE p.status = 'A'
35 AND ca.status = 'A'
36 AND ca.party_id = p.party_id;
37
38 CURSOR c_billto IS
39 SELECT site_uses.site_use_id site_use_id, ca.cust_account_id cust_account_id, p.party_id,
40 acct_site.org_id
41 FROM hz_cust_accounts ca, hz_parties p,
42 hz_cust_acct_sites acct_site,hz_cust_site_uses site_uses
43 WHERE acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
44 AND acct_site.cust_account_id = ca.cust_account_id
45 AND ca.party_id = p.party_id
46 AND p.status = 'A'
47 AND ca.status = 'A'
48 AND acct_site.status = 'A'
49 AND site_uses.status = 'A';
50
51 CURSOR c_del IS
52 SELECT delinquency_id, customer_site_use_id, cust_account_id, party_cust_id,org_id
53 FROM iex_delinquencies
54 WHERE status IN ('DELINQUENT', 'PREDELINQUENT');
55
56 --Moac Changes. Define Cursor for org_id. Start.
57
58 CURSOR c_org IS
59 SELECT organization_id from hr_operating_units where
60 mo_global.check_access(organization_id) = 'Y'
61 AND organization_id = nvl(P_ORG_ID,organization_id);
62
63 --Moac Changes. Define Cursor for org_id. End.
64
65 l_metric_id_tbl iex_metric_pvt.metric_id_tbl_type;
66 l_metric_name_tbl iex_metric_pvt.metric_name_tbl_type;
67 l_metric_value_tbl iex_metric_pvt.metric_value_tbl_type;
68 l_metric_rating_tbl iex_metric_pvt.metric_rating_tbl_type;
69
70 CURSOR c_total_cnt IS
71 SELECT count(1)
72 FROM iex_metric_summaries;
73
74 l_total_cnt NUMBER;
75 i NUMBER;
76 l_del_sql VARCHAR2(4000) := 'DELETE FROM iex_metric_summaries';
77
78 TYPE Object_ID_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
79 l_party_id_tbl Object_ID_Tbl_Type;
80 l_cust_account_id_tbl Object_ID_Tbl_Type;
81 l_site_use_id_tbl Object_ID_Tbl_Type;
82 l_delinquency_id_tbl Object_ID_Tbl_Type;
83 l_org_id_tbl Object_ID_Tbl_Type;
84 l_batch_size NUMBER;
85
86 BEGIN
87
88 RETCODE := 0;
89
90 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
91 IEX_DEBUG_PUB.logMessage('Metric_Concur: ' || 'IEX_METRIC: metricConcur: Refresh started');
92 IEX_DEBUG_PUB.logMessage('Metric_Concur: ' || 'IEX_METRIC: metricConcur: Start time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
93 END IF;
94
95 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Metric Calculation Method=' || G_METRIC_BATCH);
96 l_batch_size := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '1000'));
97 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch size=' || l_batch_size);
98
99 --Moac Changes. Commented the profile value. Start.
100 --l_org_id := TO_NUMBER(NVL(FND_PROFILE.VALUE('ORG_ID'), -1));
101 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'MO: Operating Unit=' || l_org_id);
102 --Moac Changes. Commented the profile value. End.
103
104 --Moac Changes. Initilize set the Policy. Start
105
106 MO_GLOBAL.INIT('IEX');
107 IF P_ORG_ID IS NOT NULL THEN
108 MO_GLOBAL.SET_POLICY_CONTEXT('S',P_ORG_ID); -- Single Org.
109 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MO: Operating Unit=' || p_org_id);
110 ELSE
111 MO_GLOBAL.SET_POLICY_CONTEXT('M',NULL); -- Multi Org.
112 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MO: Operating Unit=' || 'All');
113 END IF;
114
115 --Moac Changes. Initilize,set the Policy. End.
116
117
118 IF G_METRIC_BATCH = 'BATCH' THEN
119 -- Begin fix bug #4941239-jypark-delete records only of current operating unit
120
121 -- Moac Changes . Delete from synonym iex_metric_summaries. Start.
122 -- DELETE FROM iex_metric_summaries_all;
123 -- DELETE FROM iex_metric_summaries;
124 -- Moac Changes . Delete from synonym iex_metric_summaries. End.
125
126 l_del_sql := l_del_sql || ' WHERE rownum >= 0 and rownum < ' || l_batch_size;
127
128 OPEN c_total_cnt;
129 FETCH c_total_cnt INTO l_total_cnt;
130 CLOSE c_total_cnt;
131
132 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Purging old metric records(' || l_total_cnt || ')');
133 i := 0;
134
135 LOOP
136 EXECUTE IMMEDIATE l_del_sql;
137 COMMIT;
138
139 i := i + l_batch_size;
140 EXIT WHEN i > l_total_cnt;
141 END LOOP;
142
143 EXECUTE IMMEDIATE l_del_sql;
144 COMMIT;
145
146 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Purge complete>>>>>');
147 -- End fix bug #5009901-JYPARK-delete records by batch size for performance
148
149 FOR I_ORG IN C_ORG LOOP -- Moac Changes. Loop through for Party.
150 MO_GLOBAL.SET_POLICY_CONTEXT('S',I_ORG.organization_id); -- Moac Changes. Set Org.
151 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside Party Loop, Operating Unit Set =' ||I_ORG.organization_id);
152
153 i := 0;
154 FOR r_party IN c_party LOOP
155 i := i + 1;
156 l_party_id_tbl(i) := r_party.party_id;
157 END LOOP;
158
159 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total Party Count=' || i);
160
161 FOR idx1 IN l_party_id_tbl.FIRST..l_party_id_tbl.LAST LOOP
162
163 iex_metric_pvt.get_metric_info(p_api_version => 1.0,
164 p_init_msg_list => FND_API.G_TRUE,
165 p_commit => FND_API.G_TRUE,
166 p_validation_level => 100,
167 x_return_status => l_return_status,
168 x_msg_count => l_msg_count,
169 x_msg_data => l_msg_data,
170 p_party_id => l_party_id_tbl(idx1),
171 p_cust_account_id => TO_NUMBER(''),
172 p_customer_site_use_id => TO_NUMBER(''),
173 p_delinquency_id => TO_NUMBER(''),
174 x_metric_id_tbl => l_metric_id_tbl,
175 p_filter_by_object => 'PARTY',
176 x_metric_name_tbl => l_metric_name_tbl,
177 x_metric_value_tbl => l_metric_value_tbl,
178 x_metric_rating_tbl => l_metric_rating_tbl);
179
180 IF l_metric_id_tbl.count > 0 THEN
181
182 FORALL idx2 IN l_metric_name_tbl.FIRST..l_metric_name_tbl.LAST
183 INSERT INTO iex_metric_summaries_all
184 (object_id, object_type, org_id, score_comp_type_id, metric_value,
185 creation_date, created_by, last_update_date, last_updated_by,
186 last_update_login, metric_rating)
187 VALUES(l_party_id_tbl(idx1), 'PARTY',
188 --l_org_id, Moac Changes. Insert the Org_id from current set.
189 I_ORG.organization_id,
190 l_metric_id_tbl(idx2), l_metric_value_tbl(idx2), SYSDATE, G_USER_ID,
191 SYSDATE, G_USER_ID, G_LOGIN_ID, l_metric_rating_tbl(idx2));
192
193 COMMIT;
194 END IF;
195
196 END LOOP;
197 END LOOP; -- Moac Changes. Org Loop.
198
199 --Moac Changes. Re-Initialize after the Party Loop is complete. Start.
200 IF P_ORG_ID IS NOT NULL THEN
201 MO_GLOBAL.SET_POLICY_CONTEXT('S',P_ORG_ID); -- Single Org.
202 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Reset after party loop, Operating Unit=' || p_org_id);
203 ELSE
204 MO_GLOBAL.SET_POLICY_CONTEXT('M',NULL); -- Multi Org.
205 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Reset after party loop, Operating Unit=' || 'All');
206 END IF;
207 --Moac Changes. Re-Initialize after the Party Loop is complete. Start.
208
209 FOR I_ORG IN C_ORG LOOP -- Moac Changes. Loop through for Party.
210 MO_GLOBAL.SET_POLICY_CONTEXT('S',I_ORG.organization_id); -- Moac Changes. Set Org.
211 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside Account Loop, Operating Unit Set =' ||I_ORG.organization_id);
212 i := 0;
213 l_party_id_tbl.DELETE;
214
215 FOR r_account IN c_account LOOP
216 i := i + 1;
217 l_cust_account_id_tbl(i) := r_account.cust_account_id;
218 l_party_id_tbl(i) := r_account.party_id;
219 END LOOP;
220 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total Account Count=' || i);
221
222 FOR idx1 IN l_cust_account_id_tbl.FIRST..l_cust_account_id_tbl.LAST LOOP
223
224 iex_metric_pvt.get_metric_info(p_api_version => 1.0,
225 p_init_msg_list => FND_API.G_TRUE,
226 p_commit => FND_API.G_TRUE,
227 p_validation_level => 100,
228 x_return_status => l_return_status,
229 x_msg_count => l_msg_count,
230 x_msg_data => l_msg_data,
231 p_party_id => l_party_id_tbl(idx1),
232 p_cust_account_id => l_cust_account_id_tbl(idx1),
233 p_customer_site_use_id => '',
234 p_delinquency_id => '',
235 p_filter_by_object => 'IEX_ACCOUNT',
236 x_metric_id_tbl => l_metric_id_tbl,
237 x_metric_name_tbl => l_metric_name_tbl,
238 x_metric_value_tbl => l_metric_value_tbl,
239 x_metric_rating_tbl => l_metric_rating_tbl);
240
241 IF l_metric_id_tbl.count > 0 THEN
242 FORALL idx2 IN l_metric_name_tbl.FIRST..l_metric_name_tbl.LAST
243 INSERT INTO iex_metric_summaries_all(object_id, object_type,
244 org_id, score_comp_type_id, metric_value, creation_date,
245 created_by, last_update_date, last_updated_by,
246 last_update_login, metric_rating)
247 VALUES(l_cust_account_id_tbl(idx1), 'ACCOUNT',
248 --l_org_id, Moac Changes. Insert Org_id from current set.
249 I_ORG.organization_id,
250 l_metric_id_tbl(idx2),
251 l_metric_value_tbl(idx2), SYSDATE, G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID,
252 l_metric_rating_tbl(idx2));
253
254 COMMIT;
255 END IF;
256
257 END LOOP;
258
259 END LOOP; --Moac Change. Org Loop.
260
261 --Moac Changes. Re-Initilize,set the Policy. Start
262 IF P_ORG_ID IS NOT NULL THEN
263 MO_GLOBAL.SET_POLICY_CONTEXT('S',P_ORG_ID); -- Single Org.
264 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Reset after Account loop, Operating Unit=' || p_org_id);
265 ELSE
266 MO_GLOBAL.SET_POLICY_CONTEXT('M',NULL); -- Multi Org.
267 END IF;
268 --Moac Changes. Initilize, set the Policy. End.
269
270
271 i := 0 ;
272 l_cust_account_id_tbl.DELETE;
273 l_party_id_tbl.DELETE;
274
275 FOR r_billto IN c_billto LOOP
276 i := i + 1;
277 l_site_use_id_tbl(i) := r_billto.site_use_id;
278 l_org_id_tbl(i) := r_billto.org_id;
279
280 -- Begin bug#7304169 snuthala 04-Aug-2008
281 l_cust_account_id_tbl(i) := r_billto.cust_account_id;
282 l_party_id_tbl(i) := r_billto.party_id;
283 -- End bug#7304169 snuthala 04-Aug-2008
284 END LOOP;
285 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total BillTo Count=' || i);
286
287 FOR idx1 IN l_site_use_id_tbl.FIRST..l_site_use_id_tbl.LAST LOOP
288
289 iex_metric_pvt.get_metric_info(p_api_version => 1.0,
290 p_init_msg_list => FND_API.G_TRUE,
291 p_commit => FND_API.G_TRUE,
292 p_validation_level => 100,
293 x_return_status => l_return_status,
294 x_msg_count => l_msg_count,
295 x_msg_data => l_msg_data,
296 p_party_id => l_party_id_tbl(idx1),
297 p_cust_account_id => l_cust_account_id_tbl(idx1),
298 p_customer_site_use_id => l_site_use_id_tbl(idx1),
299 p_delinquency_id => '',
300 p_filter_by_object => 'IEX_BILLTO',
301 x_metric_id_tbl => l_metric_id_tbl,
302 x_metric_name_tbl => l_metric_name_tbl,
303 x_metric_value_tbl => l_metric_value_tbl,
304 x_metric_rating_tbl => l_metric_rating_tbl);
305
306 IF l_metric_id_tbl.count > 0 THEN
307 FORALL idx2 IN l_metric_name_tbl.FIRST..l_metric_name_tbl.LAST
308 INSERT INTO iex_metric_summaries_all(object_id, object_type, org_id,
309 score_comp_type_id, metric_value, creation_date, created_by, last_update_date,
310 last_updated_by,last_update_login, metric_rating)
311 VALUES(l_site_use_id_tbl(idx1), 'BILL_TO',
312 --l_org_id, Moac Changes. Insert Org_id from cursor.
313 l_org_id_tbl(idx1),
314 l_metric_id_tbl(idx2), l_metric_value_tbl(idx2), SYSDATE,
315 G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID, l_metric_rating_tbl(idx2));
316
317 COMMIT;
318 END IF;
319
320 END LOOP;
321
322 i := 0;
323 l_site_use_id_tbl.DELETE;
324 l_cust_account_id_tbl.DELETE;
325 l_party_id_tbl.DELETE;
326
327 FOR r_del IN c_del LOOP
328 i := i + 1;
329 l_delinquency_id_tbl(i) := r_del.delinquency_id;
330 l_site_use_id_tbl(i) := r_del.customer_site_use_id;
331 l_cust_account_id_tbl(i) := r_del.cust_account_id;
332 l_party_id_tbl(i) := r_del.party_cust_id;
333 l_org_id_tbl(i) := r_del.org_id;
334
335 END LOOP;
336 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total Delinquency Count=' || i);
337
338 FOR idx1 IN l_delinquency_id_tbl.FIRST..l_delinquency_id_tbl.LAST LOOP
339
340 iex_metric_pvt.get_metric_info(p_api_version => 1.0,
341 p_init_msg_list => FND_API.G_TRUE,
342 p_commit => FND_API.G_TRUE,
343 p_validation_level => 100,
344 x_return_status => l_return_status,
345 x_msg_count => l_msg_count,
346 x_msg_data => l_msg_data,
347 p_party_id => l_party_id_tbl(idx1),
348 p_cust_account_id => l_cust_account_id_tbl(idx1),
349 p_customer_site_use_id => l_site_use_id_tbl(idx1),
350 p_delinquency_id => l_delinquency_id_tbl(idx1),
351 p_filter_by_object => 'IEX_DELINQUENCY',
352 x_metric_id_tbl => l_metric_id_tbl,
353 x_metric_name_tbl => l_metric_name_tbl,
354 x_metric_value_tbl => l_metric_value_tbl,
355 x_metric_rating_tbl => l_metric_rating_tbl);
356
357
358
359 IF l_metric_id_tbl.count > 0 THEN
360 FORALL idx2 IN l_metric_name_tbl.FIRST..l_metric_name_tbl.LAST
361 INSERT INTO iex_metric_summaries_all(object_id, object_type, org_id,
362 score_comp_type_id, metric_value, creation_date, created_by,
363 last_update_date, last_updated_by, last_update_login, metric_rating)
364 VALUES(l_delinquency_id_tbl(idx1), 'DELINQUENCY',
365 --l_org_id, Moac Changes. Insert Org_id from cursor.
366 l_org_id_tbl(idx1),
367 l_metric_id_tbl(idx2), l_metric_value_tbl(idx2), SYSDATE,
368 G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID, l_metric_rating_tbl(idx2));
369
370 COMMIT;
371 END IF;
372
373 END LOOP;
374
375
376 END IF;
377
378 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
379 IEX_DEBUG_PUB.logMessage('Metric_Concur: ' || 'IEX_METRIC: metricConcur: End time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
380 END IF;
381 /*
382 Exception
383 WHEN FND_API.G_EXC_ERROR THEN
384 RETCODE := -1;
385 ERRBUF := l_msg_data;
386 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
387 IEX_DEBUG_PUB.logMessage('IEX_METRIC: metricConcur: Expected Error ' || sqlerrm);
388 END IF;
389 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: ' || sqlerrm);
390
391 WHEN OTHERS THEN
392 RETCODE := -1;
393 ERRBUF := l_msg_data;
394 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
395 IEX_DEBUG_PUB.logMessage('IEX_METRIC: metricConcur: Unexpected Error ' || sqlerrm);
396 END IF;
397 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CONCUR: ' || sqlerrm);
398 */
399 Exception
400 WHEN OTHERS THEN
401 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR : ' || sqlerrm);
402 END Refresh_All;
403 BEGIN
404 G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
405 G_USER_ID := FND_GLOBAL.User_Id;
406 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
407 G_METRIC_BATCH := NVL(fnd_profile.value('IEX_METRIC_ALLOW_BATCH'), 'REALTIME');
408 END IEX_METRIC_CONCUR_PVT;