[Home] [Help]
PACKAGE BODY: APPS.JTM_CON_QUERY_REQUEST_PKG
Source
1 PACKAGE BODY JTM_CON_QUERY_REQUEST_PKG AS
2 /* $Header: jtmconqb.pls 120.3 2006/01/13 03:20:18 trajasek noship $ */
3
4 /*** Globals ***/
5 G_PACKAGE_NAME constant VARCHAR2(30) := 'JTM_CON_QUERY_REQUEST_PKG';
6 g_debug_level NUMBER; -- debug level
7 g_category varchar2(30);
8
9 /*** cursor retrieving query request properties ***/
10 CURSOR c_query_requests IS
11 SELECT con_query_id
12 , acc_table_name
13 , con_query
14 , last_run_date
15 FROM jtm_con_query_request_data
16 WHERE EXECUTE_FLAG='Y'
17 ORDER BY execution_order;
18
19
20 /*** cursor retriving the primary key of pub_item associated with this query ***/
21 CURSOR c_primary_key(b_con_query_id NUMBER) IS
22 SELECT distinct pubitm.primary_key_column
23 FROM asg_pub_item pubitm
24 , jtm_pub_acc pubacc
25 WHERE pubacc.publication_item_name = pubitm.name
26 AND pubacc.con_query_id = b_con_query_id
27 AND pubacc.execute_flag = 'Y'
28 AND pubitm.status = 'Y'
29 AND pubitm.enabled = 'Y';
30
31 /*** cursor retrieving list of resources subscribed to publication item ***/
32 --Bug 4924543
33 CURSOR c_item_resources( b_pub_item_name VARCHAR2 )
34 IS
35 SELECT au.resource_id
36 FROM asg_user au
37 , asg_user_pub_resps aupr
38 , asg_pub_item api
39 WHERE au.user_name = aupr.user_name
40 AND aupr.pub_name = api.pub_name
41 AND api.name = b_pub_item_name
42 AND au.enabled = 'Y'
43 AND api.ENABLED = 'Y';
44
45
46
47 PROCEDURE WorkAround is
48 l_status varchar2(80);
49 l_message varchar2(2000);
50 BEGIN
51 FIX_DFF_ACC(l_status, l_message);
52 END WorkAround;
53
54 PROCEDURE FIX_DFF_ACC(
55 P_Status OUT NOCOPY VARCHAR2,
56 P_Message OUT NOCOPY VARCHAR2) IS
57
58 Cursor get_dff_seed_date is
59 select creation_date, application_id,
60 base_application_id, descriptive_flexfield_name
61 from JTM_FND_DESCR_FLEXS_ACC;
62
63 Cursor get_old_context_acc (
64 p_creation_date date, p_appl_id number, p_dff_name in varchar2) is
65 select access_id
66 from JTM_FND_DESC_FLEX_CONTEXT_ACC
67 where creation_date < p_creation_date
68 and APPLICATION_ID = p_appl_id
69 and DESCRIPTIVE_FLEXFIELD_NAME = p_dff_name;
70
71 Cursor get_old_col_usg_acc (
72 p_creation_date date, p_appl_id number, p_dff_name in varchar2) is
73 select access_id
74 from JTM_FND_DESC_FLEX_COL_USG_ACC
75 where creation_date < p_creation_date
76 and APPLICATION_ID = p_appl_id
77 and DESCRIPTIVE_FLEXFIELD_NAME = p_dff_name;
78
79 Cursor get_old_value_acc (
80 p_creation_date date, p_appl_id number, p_dff_name in varchar2) is
81 select access_id
82 from JTM_FND_FLEX_VALUES_ACC
83 where creation_date < p_creation_date
84 AND flex_value_id IN
85 (SELECT V.flex_value_id
86 FROM fnd_descr_flex_column_usages bas, FND_FLEX_VALUES V
87 WHERE bas.application_id = p_appl_id
88 AND bas.descriptive_flexfield_name = p_dff_name
89 AND bas.FLEX_VALUE_SET_ID = V.FLEX_VALUE_SET_ID
90 );
91
92 Cursor get_old_value_set_acc (
93 p_creation_date date, p_appl_id number, p_dff_name in varchar2) is
94 select access_id
95 from jtm_fnd_flex_value_sets_acc
96 where creation_date < p_creation_date
97 and flex_value_set_id IN
98 (SELECT FLEX_VALUE_SET_ID
99 FROM fnd_descr_flex_column_usages bas
100 WHERE bas.application_id = p_appl_id
101 AND bas.descriptive_flexfield_name = p_dff_name
102 );
103 L_API_NAME constant varchar2(30) := 'FIX_DFF_ACC';
104
105 l_csl_tab_user ASG_DOWNLOAD.USER_LIST;
106 l_csm_tab_user ASG_DOWNLOAD.USER_LIST;
107 l_tab_user ASG_DOWNLOAD.USER_LIST;
108 l_pub_item varchar2(30);
109 l_dummy BOOLEAN;
110 BEGIN
111
112 P_Status := G_FINE;
113 P_Message := 'All are working.';
114
115 /*** get debug level ***/
116 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
117
118 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
119 JTM_message_log_pkg.Log_Msg
120 ( v_object_id => L_API_NAME
121 , v_object_name => G_PACKAGE_NAME
122 , v_message => 'The procedure begins execution.'
123 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
124 , v_module => 'jtm_message_log_pkg');
125 END IF;
126
127 select u.user_id BULK COLLECT INTO l_csl_tab_user
128 from asg_user u, asg_user_Pub_resps r
129 where u.user_name = r.user_name
130 and r.pub_name = 'JTM';
131
132 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
133 JTM_message_log_pkg.Log_Msg
134 ( v_object_id => L_API_NAME
135 , v_object_name => G_PACKAGE_NAME
136 , v_message => 'There are ' || l_csl_tab_user.count || ' mobile laptop users.'
137 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
138 , v_module => 'jtm_message_log_pkg');
139 END IF;
140
141 select u.user_id BULK COLLECT INTO l_csm_tab_user
142 from asg_user u, asg_user_Pub_resps r
143 where u.user_name = r.user_name
144 and r.pub_name = 'JTM_HANDHELD';
145
146 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
147 JTM_message_log_pkg.Log_Msg
148 ( v_object_id => L_API_NAME
149 , v_object_name => G_PACKAGE_NAME
150 , v_message => 'There are ' || l_csm_tab_user.count || ' mobile pocket pc users.'
151 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
152 , v_module => 'jtm_message_log_pkg');
153 END IF;
154
155 FOR c_seed_date in get_dff_seed_date LOOP
156 /* Handle the DFF context */
157 BEGIN
158 FOR c_old_context_acc in get_old_context_acc(
159 c_seed_date.creation_date,
160 c_seed_date.base_application_id,
161 c_seed_date.descriptive_flexfield_name) LOOP
162 BEGIN
163 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
164 JTM_message_log_pkg.Log_Msg
165 ( v_object_id => L_API_NAME
166 , v_object_name => G_PACKAGE_NAME
167 , v_message => 'Handling DFF '||
168 c_seed_date.descriptive_flexfield_name || ' context (acc id ='
169 || c_old_context_acc.access_id || ')'
170 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
171 , v_module => 'jtm_message_log_pkg');
172 END IF;
173 IF (c_seed_date.application_id = 883) THEN
174 l_tab_user := l_csm_tab_user;
175 l_pub_item := 'JTM_H_DESC_FLEX_CONTEXTS';
176 ELSIF (c_seed_date.application_id = 868) THEN
177 l_tab_user := l_csl_tab_user;
178 l_pub_item := 'FND_DESC_FLEX_CONTEXTS';
179 END IF;
180
181 FOR i IN l_tab_user.FIRST..l_tab_user.LAST LOOP
182 l_dummy := asg_download.mark_dirty (
183 p_pub_item => l_pub_item,
184 p_accessid => c_old_context_acc.access_id,
185 p_userid => l_tab_user(i),
186 p_dml => 'I',
187 p_timestamp => sysdate );
188 END LOOP;
189
190 Update JTM_FND_DESC_FLEX_CONTEXT_ACC
191 set creation_date = c_seed_date.creation_date
192 where access_id = c_old_context_acc.access_id;
193
194 commit;
195 EXCEPTION
196 WHEN OTHERS THEN
197 P_Status := G_ERROR;
198 P_Message := 'Exception ocurrs with DFF ' ||
199 c_seed_date.descriptive_flexfield_name || ' context (acc id ='
200 || c_old_context_acc.access_id || '): ' || sqlerrm;
201 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
202 JTM_message_log_pkg.Log_Msg
203 ( v_object_id => L_API_NAME
204 , v_object_name => G_PACKAGE_NAME
205 , v_message => P_Message
206 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
207 , v_module => 'jtm_message_log_pkg');
208 END IF;
209 END;
210 END LOOP;
211 EXCEPTION
212 WHEN OTHERS THEN
213 P_Status := G_ERROR;
214 P_Message := 'Exception ocurrs with DFF ' ||
215 c_seed_date.descriptive_flexfield_name || ' context ' || sqlerrm;
216 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
217 JTM_message_log_pkg.Log_Msg
218 ( v_object_id => L_API_NAME
219 , v_object_name => G_PACKAGE_NAME
220 , v_message => P_Message
221 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
222 , v_module => 'jtm_message_log_pkg');
223 END IF;
224 END; /* Handle the DFF context */
225
226 BEGIN /* Handle the DFF column usage */
227 FOR c_old_col_usg_acc in get_old_col_usg_acc(
228 c_seed_date.creation_date,
229 c_seed_date.base_application_id,
230 c_seed_date.descriptive_flexfield_name) LOOP
231 BEGIN
232 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
233 JTM_message_log_pkg.Log_Msg
234 ( v_object_id => L_API_NAME
235 , v_object_name => G_PACKAGE_NAME
236 , v_message => 'Handling DFF '||
237 c_seed_date.descriptive_flexfield_name || ' segment (acc id ='
238 || c_old_col_usg_acc.access_id || ')'
239 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
240 , v_module => 'jtm_message_log_pkg');
241 END IF;
242 IF (c_seed_date.application_id = 883) THEN
243 l_tab_user := l_csm_tab_user;
244 l_pub_item := 'JTM_H_DESC_FLEX_COL_USGS';
245 ELSIF (c_seed_date.application_id = 868) THEN
246 l_tab_user := l_csl_tab_user;
247 l_pub_item := 'FND_DESC_FLEX_COL_USGS';
248 END IF;
249
250 FOR i IN l_tab_user.FIRST..l_tab_user.LAST LOOP
251 l_dummy := asg_download.mark_dirty (
252 p_pub_item => l_pub_item,
253 p_accessid => c_old_col_usg_acc.access_id,
254 p_userid => l_tab_user(i),
255 p_dml => 'I',
256 p_timestamp => sysdate );
257 END LOOP;
258
259 Update JTM_FND_DESC_FLEX_COL_USG_ACC
260 set creation_date = c_seed_date.creation_date
261 where access_id = c_old_col_usg_acc.access_id;
262
263 commit;
264 EXCEPTION
265 WHEN OTHERS THEN
266 P_Status := G_ERROR;
267 P_Message := 'Exception ocurrs with DFF ' ||
268 c_seed_date.descriptive_flexfield_name || ' segment (acc id ='
269 || c_old_col_usg_acc.access_id || '): ' || sqlerrm;
270 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
271 JTM_message_log_pkg.Log_Msg
272 ( v_object_id => L_API_NAME
273 , v_object_name => G_PACKAGE_NAME
274 , v_message => P_Message
275 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
276 , v_module => 'jtm_message_log_pkg');
277 END IF;
278 END;
279 END LOOP;
280 EXCEPTION
281 WHEN OTHERS THEN
282 P_Status := G_ERROR;
283 P_Message := 'Exception ocurrs with DFF ' ||
284 c_seed_date.descriptive_flexfield_name || ' segments: ' || sqlerrm;
285 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
286 JTM_message_log_pkg.Log_Msg
287 ( v_object_id => L_API_NAME
288 , v_object_name => G_PACKAGE_NAME
289 , v_message => P_Message
290 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
291 , v_module => 'jtm_message_log_pkg');
292 END IF;
293 END; /* Handle the DFF column usage */
294
295 BEGIN /* Handle the DFF value */
296 FOR c_old_value_acc in get_old_value_acc(
297 c_seed_date.creation_date,
298 c_seed_date.base_application_id,
299 c_seed_date.descriptive_flexfield_name) LOOP
300 BEGIN
301 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
302 JTM_message_log_pkg.Log_Msg
303 ( v_object_id => L_API_NAME
304 , v_object_name => G_PACKAGE_NAME
305 , v_message => 'Handling DFF '||
306 c_seed_date.descriptive_flexfield_name || ' value (acc id ='
307 || c_old_value_acc.access_id || ')'
308 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
309 , v_module => 'jtm_message_log_pkg');
310 END IF;
311 IF (c_seed_date.application_id = 883) THEN
312 l_tab_user := l_csm_tab_user;
313 l_pub_item := 'JTM_H_FLEX_VALUES';
314 ELSIF (c_seed_date.application_id = 868) THEN
315 l_tab_user := l_csl_tab_user;
316 l_pub_item := 'FND_FLEX_VALUES';
317 END IF;
318
319 FOR i IN l_tab_user.FIRST..l_tab_user.LAST LOOP
320 l_dummy := asg_download.mark_dirty (
321 p_pub_item => l_pub_item,
322 p_accessid => c_old_value_acc.access_id,
323 p_userid => l_tab_user(i),
324 p_dml => 'I',
325 p_timestamp => sysdate );
326 END LOOP;
327
328 Update JTM_FND_FLEX_VALUES_ACC
329 set creation_date = c_seed_date.creation_date
330 where access_id = c_old_value_acc.access_id;
331
332 commit;
333 EXCEPTION
334 WHEN OTHERS THEN
335 P_Status := G_ERROR;
336 P_Message := 'Exception ocurrs with DFF ' ||
337 c_seed_date.descriptive_flexfield_name || ' values (acc id ='
338 || c_old_value_acc.access_id || '): ' || sqlerrm;
339 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
340 JTM_message_log_pkg.Log_Msg
341 ( v_object_id => L_API_NAME
342 , v_object_name => G_PACKAGE_NAME
343 , v_message => P_Message
344 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
345 , v_module => 'jtm_message_log_pkg');
346 END IF;
347 END;
348 END LOOP;
349 EXCEPTION
350 WHEN OTHERS THEN
351 P_Status := G_ERROR;
352 P_Message := 'Exception ocurrs with DFF value: ' ||
353 c_seed_date.descriptive_flexfield_name || ' values: ' || sqlerrm;
354 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
355 JTM_message_log_pkg.Log_Msg
356 ( v_object_id => L_API_NAME
357 , v_object_name => G_PACKAGE_NAME
358 , v_message => P_Message
359 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
360 , v_module => 'jtm_message_log_pkg');
361 END IF;
362 END; /* Handle the DFF value */
363
364 BEGIN /* Handle the DFF value set */
365 FOR c_old_value_set_acc in get_old_value_set_acc(
366 c_seed_date.creation_date,
367 c_seed_date.base_application_id,
368 c_seed_date.descriptive_flexfield_name) LOOP
369 BEGIN
370 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
371 JTM_message_log_pkg.Log_Msg
372 ( v_object_id => L_API_NAME
373 , v_object_name => G_PACKAGE_NAME
374 , v_message => 'Handling DFF '||
375 c_seed_date.descriptive_flexfield_name || ' value set (acc id ='
376 || c_old_value_set_acc.access_id || ')'
377 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
378 , v_module => 'jtm_message_log_pkg');
379 END IF;
380 IF (c_seed_date.application_id = 883) THEN
381 l_tab_user := l_csm_tab_user;
382 l_pub_item := 'JTM_H_FLEX_VALUE_SETS';
383 ELSIF (c_seed_date.application_id = 868) THEN
384 l_tab_user := l_csl_tab_user;
385 l_pub_item := 'FND_FLEX_VALUE_SETS';
386 END IF;
387
388 FOR i IN l_tab_user.FIRST..l_tab_user.LAST LOOP
389 l_dummy := asg_download.mark_dirty (
390 p_pub_item => l_pub_item,
391 p_accessid => c_old_value_set_acc.access_id,
392 p_userid => l_tab_user(i),
393 p_dml => 'I',
394 p_timestamp => sysdate );
395 END LOOP;
396
397 Update JTM_FND_FLEX_VALUE_SETS_ACC
398 set creation_date = c_seed_date.creation_date
399 where access_id = c_old_value_set_acc.access_id;
400
401 commit;
402 EXCEPTION
403 WHEN OTHERS THEN
404 P_Status := G_ERROR;
405 P_Message := 'Exception ocurrs with DFF ' ||
406 c_seed_date.descriptive_flexfield_name || ' value set(acc id ='
407 || c_old_value_set_acc.access_id || '): ' || sqlerrm;
408 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
409 JTM_message_log_pkg.Log_Msg
410 ( v_object_id => L_API_NAME
411 , v_object_name => G_PACKAGE_NAME
412 , v_message => P_Message
413 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
414 , v_module => 'jtm_message_log_pkg');
415 END IF;
416 END;
417 END LOOP;
418 EXCEPTION
419 WHEN OTHERS THEN
420 P_Status := G_ERROR;
421 P_Message := 'Exception ocurrs with DFF ' ||
422 c_seed_date.descriptive_flexfield_name || ' values: ' || sqlerrm;
423 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
424 JTM_message_log_pkg.Log_Msg
425 ( v_object_id => L_API_NAME
426 , v_object_name => G_PACKAGE_NAME
427 , v_message => P_Message
428 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
429 , v_module => 'jtm_message_log_pkg');
430 END IF;
431 END; /* Handle the DFF value set */
432
433 END LOOP;
434 EXCEPTION
435 WHEN OTHERS THEN
436 P_Status := G_ERROR;
437 P_Message := 'Exception ocurrs: ' || sqlerrm;
438 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
439 JTM_message_log_pkg.Log_Msg
440 ( v_object_id => L_API_NAME
441 , v_object_name => G_PACKAGE_NAME
442 , v_message => P_Message
443 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
444 , v_module => 'jtm_message_log_pkg');
445 END IF;
446
447 END FIX_DFF_ACC;
448
449 FUNCTION GET_CATEGORY RETURN VARCHAR2 IS
450 l_category varchar2(80);
451 BEGIN
452 SELECT category
453 INTO l_category
454 FROM jtm_con_request_data
455 WHERE upper(package_name) = 'JTM_CON_QUERY_REQUEST_PKG'
456 AND upper(procedure_name) = 'RUN_QUERY_REQUESTS';
457
458 return l_category;
459 EXCEPTION
460 WHEN others then
461 return null;
462 END GET_CATEGORY;
463
464 FUNCTION GET_CONDITION(p_primary_key IN VARCHAR2,
465 P_ALIAS1 IN VARCHAR2,
466 P_ALIAS2 IN VARCHAR2) RETURN VARCHAR2 IS
467 L_CONDITION VARCHAR2(4000);
468 l_column_name VARCHAR2(200);
469 l_alias1 VARCHAR2(200);
470 l_alias2 VARCHAR2(200);
471
472 l_index1 number;
473 l_index2 number;
474
475 BEGIN
476 L_CONDITION := NULL;
477 l_index1 := 1;
478 l_alias1 := LTRIM(RTRIM(P_ALIAS1));
479 l_alias2 := LTRIM(RTRIM(P_ALIAS2));
480 LOOP
481 l_index2 := INSTR(p_primary_key, ',', l_index1, 1);
482 IF (l_index2 > 0) THEN
483 l_column_name := LTRIM(RTRIM(SUBSTR(p_primary_key,l_index1,l_index2-l_index1)));
484 ELSE
485 l_column_name := LTRIM(RTRIM(SUBSTR(p_primary_key,l_index1,LENGTH(p_primary_key)+1-l_index1)));
486 END IF;
487
488 IF (l_index1 = 1) then
489 L_CONDITION := ' ' || l_alias1 || '.' || l_column_name || ' = ' ||
490 l_alias2 || '.' || l_column_name || ' ';
491 ELSE
492 L_CONDITION := L_CONDITION || 'AND ' || l_alias1 || '.' || l_column_name || ' = ' ||
493 l_alias2 || '.' || l_column_name || ' ' ;
494 END IF;
495
496 IF (l_index2 <= 0) THEN
497 EXIT;
498 END IF;
499 l_index1 := l_index2 +1;
500 END LOOP;
501 RETURN L_CONDITION;
502 END;
503
504 FUNCTION markdirty_helper(
505 p_con_query_id IN NUMBER
506 ,p_accessList IN ASG_DOWNLOAD.ACCESS_LIST
507 ,p_dml_type IN CHAR
508 ) RETURN BOOLEAN IS
509
510 TYPE pitnameTab IS TABLE OF JTM_PUB_ACC.PUBLICATION_ITEM_NAME%TYPE INDEX BY BINARY_INTEGER;
511 l_publication_item_name pitnameTab;
512
513 local_item_resources c_item_resources%ROWTYPE;
514 local_tab_resource ASG_DOWNLOAD.USER_LIST;
515 l_dummy BOOLEAN;
516 j BINARY_INTEGER;
517
518 BEGIN
519 SELECT publication_item_name BULK COLLECT INTO l_publication_item_name
520 FROM JTM_PUB_ACC WHERE CON_QUERY_ID = p_con_query_id;
521
522 IF(l_publication_item_name.COUNT >0) THEN
523 FOR j IN l_publication_item_name.FIRST..l_publication_item_name.LAST LOOP
524
525 OPEN c_item_resources(l_publication_item_name(j));
526 FETCH c_item_resources BULK COLLECT INTO local_tab_resource;
527
528 IF c_item_resources%ROWCOUNT > 0 THEN
529 l_dummy := asg_download.markDirty (
530 p_pub_item => l_publication_item_name(j)
531 ,p_accessList => p_accessList
532 ,p_resourceList => local_tab_resource
533 ,p_dml_type => p_dml_type
534 ,p_timestamp => SYSDATE
535 ,p_bulk_flag => TRUE
536 );
537 END IF;
538 CLOSE c_item_resources;
539 END LOOP;
540 END IF;
541
542 RETURN TRUE;
543
544 EXCEPTION
545 WHEN OTHERS THEN
546 CLOSE c_item_resources;
547 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
548 JTM_message_log_pkg.Log_Msg
549 ( v_object_id => l_publication_item_name(j)
550 , v_object_name => G_PACKAGE_NAME
551 , v_message => 'exception thrown in '||G_PACKAGE_NAME||'.markdirty_helper.'
552 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
553 , v_module => 'jtm_message_log_pkg');
554 END IF;
555 RAISE;
556 RETURN FALSE;
557
558 END markdirty_helper;
559
560 PROCEDURE Process_Request
561 (r_query_request c_query_requests%ROWTYPE
562 ,p_status out nocopy varchar2
563 ,p_message out nocopy varchar2) IS
564
565 l_query_start DATE;
566 l_dynamic_stmt VARCHAR2(4000);
567
568 l_primary_key VARCHAR2(4000);
569 l_original_primary_key VARCHAR2(4000);
570 l_tab_access_id dbms_sql.Number_Table;
571 m_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
572
573 l_cursor INTEGER;
574 l_count INTEGER;
575 l_index NUMBER;
576
577 l_dummy BOOLEAN;
578 l_start_log_id NUMBER;
579 l_status varchar2(1);
580 l_message varchar2(2000);
581 l_tmp_stmt varchar2(2000);
582
583 BEGIN
584 p_status := G_FINE;
585 p_message := 'OK';
586 l_query_start := sysdate;
587
588
589 JTM_MESSAGE_LOG_PKG.INSERT_CONC_STATUS_LOG
590 (v_package_name => NULL
591 ,v_procedure_name => NULL
592 ,v_con_query_id => r_query_request.con_query_id
593 ,v_query_stmt => G_CATEGORY
594 ,v_start_time => l_query_start
595 ,v_end_time => NULL
596 ,v_status => 'Running'
597 ,v_message => 'Processing for table ' ||r_query_request.acc_table_name
598 ,x_log_id => l_start_log_id
599 ,x_status => l_status
600 ,x_msg_data => l_message);
601
602 IF (l_status = 'E') THEN
603 RAISE JTM_MESSAGE_LOG_PKG.G_EXC_ERROR;
604 END IF;
605
606 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
607 JTM_message_log_pkg.Log_Msg
608 ( v_object_id => null
609 , v_object_name => G_PACKAGE_NAME
610 , v_message => 'Entering '||G_PACKAGE_NAME||'.PROCESS_REQUEST'
611 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
612 , v_module => 'jtm_message_log_pkg');
613 END IF;
614
615 /** get primary_key of corresponding publication item **/
616 OPEN c_primary_key(r_query_request.con_query_id);
617 FETCH c_primary_key into l_primary_key;
618 l_original_primary_key := l_primary_key;
619 IF (r_query_request.acc_table_name = 'JTM_FND_PROF_OPTIONS_VAL_ACC') THEN
620 begin
621 l_primary_key := replace (l_primary_key, 'LEVEL_VALUE_APPLICATION_ID',
622 'NVL(LEVEL_VALUE_APPLICATION_ID, -1)');
623 exception
624 WHEN OTHERS THEN
625 null;
626 end;
627 END IF;
628
629 IF c_primary_key%ROWCOUNT = 0 THEN
630 /*** no application subscribed -> ignore this query ***/
631 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
632 JTM_message_log_pkg.Log_Msg
633 ( v_object_id => r_query_request.acc_table_name
634 , v_object_name => G_PACKAGE_NAME
635 , v_message => 'No application subscribed to query ' ||
636 r_query_request.con_query_id || '.' ||
637 fnd_global.local_chr(10) || 'Ignoring this query.'
638 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
639 , v_module => 'jtm_message_log_pkg');
640 END IF;
641
642 p_status := G_ERROR;
643 p_message := 'No primary key found for pub item related to query id ' ||
644 r_query_request.con_query_id;
645 ELSE
646
647 /*** one or more resources subscribed -> process publication item ***/
648 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
649 JTM_message_log_pkg.Log_Msg
650 ( v_object_id => r_query_request.acc_table_name
651 , v_object_name => G_PACKAGE_NAME
652 , v_message => 'There is at lesst one application regiesterd to this query'
653 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
654 , v_module => 'jtm_message_log_pkg');
655 END IF;
656
657 /*** PROCESS UPDATES ***/
658
659 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
660 JTM_message_log_pkg.Log_Msg
661 ( v_object_id => r_query_request.acc_table_name
662 , v_object_name => G_PACKAGE_NAME
663 , v_message => 'Processing UPDATES'
664 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
665 , v_module => 'jtm_message_log_pkg');
666 END IF;
667
668 /*** Check if query ran before ***/
669 IF r_query_request.last_run_date IS NOT NULL THEN
670 /*** Yes -> Get access_id of records that were updated since last_run_date ***/
671 l_dynamic_stmt := 'SELECT ACCESS_ID FROM ' || r_query_request.acc_table_name ||
672 ' ACC WHERE (' || l_primary_key || ') IN (SELECT ' ||
673 l_primary_key || ' FROM (' || r_query_request.con_query || ') B ' ||
674 'WHERE B.LAST_UPDATE_DATE >= :last_run_date)';
675 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
676 JTM_message_log_pkg.Log_Msg
677 ( v_object_id => r_query_request.acc_table_name
678 , v_object_name => G_PACKAGE_NAME
679 , v_message => 'Executing:' || fnd_global.local_chr(10) || l_dynamic_stmt || fnd_global.local_chr(10) ||
680 'LAST_RUN_DATE = ' || to_char(r_query_request.last_run_date)
681 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL
682 , v_module => 'jtm_message_log_pkg');
683 END IF;
684 l_cursor := dbms_sql.open_cursor;
685 dbms_sql.parse( l_cursor, l_dynamic_stmt, dbms_sql.v7);
686 dbms_sql.bind_variable( l_cursor, 'last_run_date', r_query_request.last_run_date);
687 l_index := 1;
688 l_tab_access_id.DELETE;
689 dbms_sql.define_array( l_cursor, 1, l_tab_access_id, 100, l_index);
690 l_count := dbms_sql.execute( l_cursor );
691 LOOP
692 l_count := dbms_sql.fetch_rows(l_cursor);
693 dbms_sql.column_value( l_cursor, '1', l_tab_access_id);
694 EXIT WHEN l_count <> 100;
695 END LOOP;
696 dbms_sql.close_cursor( l_cursor );
697
698 IF l_tab_access_id.COUNT > 0 THEN
699 /*** 1 or more acc rows retrieved -> push to resources ***/
700 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
701 JTM_message_log_pkg.Log_Msg
702 ( v_object_id => r_query_request.acc_table_name
703 , v_object_name => G_PACKAGE_NAME
704 , v_message => 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s) to subscribed resources.'
705 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
706 , v_module => 'jtm_message_log_pkg');
707 END IF;
708
709 FOR i IN l_tab_access_id.FIRST..l_tab_access_id.LAST LOOP
710 m_tab_access_id(i) := l_tab_access_id(i);
711 END LOOP;
712
713 l_dummy := markdirty_helper(
714 p_con_query_id => r_query_request.con_query_id
715 ,p_accessList => m_tab_access_id
716 ,p_dml_type => 'U');
717 END IF;
718
719 END IF; -- process UPDATES
720
721 /*** PROCESS INSERTS ***/
722 /***
723 Insert new records to in ACC with COUNTER = 0.
724 Then select all ACCESS_IDs from ACC where COUNTER = 0.
725 Then update COUNTER to 1.
726 This is a workaround for the fact that INSERT INTO with subquery cannot be used
727 in combination with RETURNING and we need the ACCESS_IDs to push the records to the
728 mobile users.
729 ***/
730
731 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
732 JTM_message_log_pkg.Log_Msg
733 ( v_object_id => r_query_request.acc_table_name
734 , v_object_name => G_PACKAGE_NAME
735 , v_message => 'Processing INSERTS'
736 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
737 , v_module => 'jtm_message_log_pkg');
738 END IF;
739
740 IF (r_query_request.acc_table_name = 'JTM_FND_PROF_OPTIONS_VAL_ACC') THEN
741 l_dynamic_stmt :=
742 'INSERT INTO JTM_FND_PROF_OPTIONS_VAL_ACC ' ||
743 '(APPLICATION_ID,LEVEL_ID,LEVEL_VALUE, ' ||
744 'LEVEL_VALUE_APPLICATION_ID,PROFILE_OPTION_ID, ' ||
745 'ACCESS_ID, COUNTER, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
746 'CREATION_DATE, CREATED_BY) ' ||
747 'SELECT V.APPLICATION_ID,V.LEVEL_ID,V.LEVEL_VALUE, ' ||
748 'NVL(V.LEVEL_VALUE_APPLICATION_ID, -1), ' ||
749 'V.PROFILE_OPTION_ID, ' ||
750 'JTM_ACC_TABLE_S.NEXTVAL, 0, SYSDATE, 1, SYSDATE, 1 ' ||
751 'FROM FND_PROFILE_OPTION_VALUES V, ' ||
752 ' JTM_FND_PROF_OPTIONS_VAL_ACC ACC ' ||
753 'WHERE V.APPLICATION_ID IN ' ||
754 ' (0,170,178,222,401,513,523,544,690,697,868,874,689,883) ' ||
755 'AND V.APPLICATION_ID = ACC.APPLICATION_ID(+) ' ||
756 'AND V.LEVEL_ID = ACC.LEVEL_ID(+) ' ||
757 'AND V.LEVEL_VALUE = ACC.LEVEL_VALUE(+) ' ||
758 'AND NVL(V.LEVEL_VALUE_APPLICATION_ID, -1) = ' ||
759 ' ACC.LEVEL_VALUE_APPLICATION_ID(+) ' ||
760 'AND V.PROFILE_OPTION_ID = ACC.PROFILE_OPTION_ID(+) ' ||
761 'AND ACC.APPLICATION_ID IS NULL';
762 ELSE
763 l_dynamic_stmt := 'INSERT INTO ' || r_query_request.acc_table_name ||
764 '(' || l_original_primary_key || ', ACCESS_ID, COUNTER,' ||
765 ' LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY) ' ||
766 'SELECT ' || l_primary_key || ', JTM_ACC_TABLE_S.NEXTVAL, 0, SYSDATE, 1, SYSDATE, 1' ||
767 ' FROM (' || r_query_request.con_query || ') WHERE' ||
768 ' (' || l_primary_key || ') NOT IN ' ||
769 '(SELECT ' || l_primary_key || ' FROM ' || r_query_request.acc_table_name || ')';
770 END IF;
771 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
772 JTM_message_log_pkg.Log_Msg
773 ( v_object_id => r_query_request.acc_table_name
774 , v_object_name => G_PACKAGE_NAME
775 , v_message => 'Executing:' || fnd_global.local_chr(10) || l_dynamic_stmt
776 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL
777 , v_module => 'jtm_message_log_pkg');
778
779 END IF;
780 EXECUTE IMMEDIATE l_dynamic_stmt;
781
782 /*** Retrieve ACCESS_IDs for any inserted records ***/
783 l_dynamic_stmt := 'SELECT ACCESS_ID FROM ' || r_query_request.acc_table_name ||
784 ' WHERE COUNTER = 0';
785 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
786 JTM_message_log_pkg.Log_Msg
787 ( v_object_id => r_query_request.acc_table_name
788 , v_object_name => G_PACKAGE_NAME
789 , v_message => 'Executing:' || fnd_global.local_chr(10) || l_dynamic_stmt
790 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL
791 , v_module => 'jtm_message_log_pkg');
792 END IF;
793 l_cursor := dbms_sql.open_cursor;
794 dbms_sql.parse( l_cursor, l_dynamic_stmt, dbms_sql.v7);
795 l_index := 1;
796 l_tab_access_id.DELETE;
797 dbms_sql.define_array( l_cursor, 1, l_tab_access_id, 100, l_index);
798 l_count := dbms_sql.execute( l_cursor );
799 LOOP
800 l_count := dbms_sql.fetch_rows(l_cursor);
801 dbms_sql.column_value( l_cursor, '1', l_tab_access_id);
802 EXIT WHEN l_count <> 100;
803 END LOOP;
804 dbms_sql.close_cursor( l_cursor );
805
806 IF l_tab_access_id.COUNT > 0 THEN
807 /*** 1 or more acc rows retrieved -> push to resources ***/
808 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
809 JTM_message_log_pkg.Log_Msg
810 ( v_object_id => r_query_request.acc_table_name
811 , v_object_name => G_PACKAGE_NAME
812 , v_message => 'Pushing ' || l_tab_access_id.COUNT || ' inserted record(s) to subscribed resources.'
813 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
814 , v_module => 'jtm_message_log_pkg');
815 END IF;
816
817 /*** push to oLite using asg_download ***/
818
819 FOR i IN l_tab_access_id.FIRST..l_tab_access_id.LAST LOOP
820 m_tab_access_id(i) := l_tab_access_id(i);
821 END LOOP;
822
823 l_dummy := markdirty_helper(
824 p_con_query_id => r_query_request.con_query_id
825 ,p_accessList => m_tab_access_id
826 ,p_dml_type => 'I');
827
828 /*** set COUNTER to 1 in ACC table ***/
829 l_dynamic_stmt := 'UPDATE ' || r_query_request.acc_table_name || ' SET COUNTER=1 WHERE COUNTER=0';
830 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
831 JTM_message_log_pkg.Log_Msg
832 ( v_object_id => r_query_request.acc_table_name
833 , v_object_name => G_PACKAGE_NAME
834 , v_message => 'Executing:' || fnd_global.local_chr(10) || l_dynamic_stmt
835 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL
836 , v_module => 'jtm_message_log_pkg');
837 END IF;
838 EXECUTE IMMEDIATE l_dynamic_stmt;
839 END IF; -- process INSERTS
840
841
842 /*** PROCESS DELETES ***/
843 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
844 JTM_message_log_pkg.Log_Msg
845 ( v_object_id => r_query_request.acc_table_name
846 , v_object_name => G_PACKAGE_NAME
847 , v_message => 'Processing DELETES'
848 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
849 , v_module => 'jtm_message_log_pkg');
850 END IF;
851
852 IF (r_query_request.acc_table_name = 'JTM_FND_PROF_OPTIONS_VAL_ACC') THEN
853 l_dynamic_stmt :=
854 'SELECT acc.access_id ' ||
855 'FROM jtm_fnd_prof_options_val_acc acc, ' ||
856 '(SELECT application_id, level_id, level_value, ' ||
857 'nvl(level_value_application_id, -1) ' ||
858 'as level_value_application_id, profile_option_id ' ||
859 'FROM fnd_profile_option_values ' ||
860 'WHERE application_id IN ' ||
861 '(0,170,178,222,401,513,523,544,690,697,868,874,689,883) ) B ' ||
862 'WHERE acc.application_id = b.application_id(+) ' ||
863 'and acc.level_id = b.level_id(+) ' ||
864 'and acc.level_value = b.level_value(+) ' ||
865 'and acc.level_value_application_id = ' ||
866 ' b.level_value_application_id(+) ' ||
867 'and acc.profile_option_id = b.profile_option_id(+) ' ||
868 'and b.application_id is null ';
869 ELSE
870 l_dynamic_stmt := 'SELECT ACCESS_ID FROM ' || r_query_request.acc_table_name ||
871 ' WHERE (' || l_primary_key || ') NOT IN (SELECT ' ||
872 l_primary_key || ' FROM (' || r_query_request.con_query || '))';
873 END IF;
874
875 l_cursor := dbms_sql.open_cursor;
876 dbms_sql.parse( l_cursor, l_dynamic_stmt, dbms_sql.v7);
877 l_index := 1;
878 l_tab_access_id.DELETE;
879 dbms_sql.define_array( l_cursor, 1, l_tab_access_id, 100, l_index);
880 l_count := dbms_sql.execute( l_cursor );
881 LOOP
882 l_count := dbms_sql.fetch_rows(l_cursor);
883 dbms_sql.column_value( l_cursor, '1', l_tab_access_id);
884 EXIT WHEN l_count <> 100;
885 END LOOP;
886 dbms_sql.close_cursor( l_cursor );
887
888 IF l_tab_access_id.COUNT > 0 THEN
889 /*** 1 or more acc rows retrieved -> push to resources ***/
890 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
891 JTM_message_log_pkg.Log_Msg
892 ( v_object_id => r_query_request.acc_table_name
893 , v_object_name => G_PACKAGE_NAME
894 , v_message => 'Pushing ' || l_tab_access_id.COUNT || ' deleted record(s) to subscribed resources.'
895 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
896 , v_module => 'jtm_message_log_pkg');
897 END IF;
898 /*** push to oLite using asg_download ***/
899
900 FOR i IN l_tab_access_id.FIRST..l_tab_access_id.LAST LOOP
901 m_tab_access_id(i) := l_tab_access_id(i);
902 END LOOP;
903
904 l_dummy := markdirty_helper(
905 p_con_query_id => r_query_request.con_query_id
906 ,p_accessList => m_tab_access_id
907 ,p_dml_type => 'D'
908 );
909
910 /* Delete record from acc table. */
911 l_dynamic_stmt := 'DELETE ' || r_query_request.acc_table_name ||
912 ' WHERE access_id= :1';
913 FOR i IN l_tab_access_id.FIRST..l_tab_access_id.LAST LOOP
914 EXECUTE IMMEDIATE l_dynamic_stmt USING m_tab_access_id(i);
915 END LOOP;
916
917 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
918 JTM_message_log_pkg.Log_Msg
919 ( v_object_id => r_query_request.acc_table_name
920 , v_object_name => G_PACKAGE_NAME
921 , v_message => 'Executing:' || fnd_global.local_chr(10) || l_dynamic_stmt
922 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL
923 , v_module => 'jtm_message_log_pkg');
924 END IF;
925
926
927 END IF; -- process DELETES
928
929 p_message := 'Successfully processing with query id ' ||
930 r_query_request.con_query_id;
931 END IF;
932 CLOSE c_primary_key;
933
934 JTM_MESSAGE_LOG_PKG.UPDATE_CONC_STATUS_LOG
935 (v_log_id =>l_start_log_id
936 ,v_query_stmt => G_Category
937 ,v_start_time => l_query_start
938 ,v_end_time => sysdate
939 ,v_status => p_status
940 ,v_message => p_message
941 ,x_status => l_status
942 ,x_msg_data => l_message);
943
944 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
945 JTM_message_log_pkg.Log_Msg
946 ( v_object_id => r_query_request.acc_table_name
947 , v_object_name => G_PACKAGE_NAME
948 , v_message => 'Leaving '||G_PACKAGE_NAME||'.Process_Request'
949 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
950 , v_module => 'jtm_message_log_pkg');
951 END IF;
952
953 EXCEPTION
954 WHEN OTHERS THEN
955 CLOSE c_primary_key;
956 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
957 JTM_message_log_pkg.Log_Msg
958 ( v_object_id => r_query_request.acc_table_name
959 , v_object_name => G_PACKAGE_NAME
960 , v_message => 'Leaving '||G_PACKAGE_NAME||'.Process_Request after exception.'
961 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
962 , v_module => 'jtm_message_log_pkg');
963 END IF;
964
965 p_status := G_ERROR;
966 p_message := 'Exception ocurrs while processing query with id ' ||
967 r_query_request.con_query_id || ': ' || sqlerrm;
968 JTM_MESSAGE_LOG_PKG.UPDATE_CONC_STATUS_LOG
969 (v_log_id =>l_start_log_id
970 ,v_query_stmt => G_Category
971 ,v_start_time => l_query_start
972 ,v_end_time => sysdate
973 ,v_status => p_status
974 ,v_message => p_message
975 ,x_status => l_status
976 ,x_msg_data => l_message);
977 END Process_Request;
978
979 /* PWU: The version 2 of the process request. It handle the last_update_date
980 gracely, even the base table last update date is wrong.
981 New requirement: All the query statement should include the last_update_date
982 in the select clause */
983 PROCEDURE Process_Request_v2
984 (r_query_request c_query_requests%ROWTYPE
985 ,p_status out nocopy varchar2
986 ,p_message out nocopy varchar2) IS
987
988 l_query_start date;
989 l_dynamic_stmt VARCHAR2(4000);
990
991 l_primary_key VARCHAR2(2000);
992 l_tab_access_id dbms_sql.Number_Table;
993 m_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
994
995 l_cursor INTEGER;
996 l_count INTEGER;
997 l_index NUMBER;
998
999 l_dummy BOOLEAN;
1000 l_start_log_id NUMBER;
1001 l_status varchar2(1);
1002 l_message varchar2(2000);
1003 l_tmp_stmt varchar2(2000);
1004 l_update_count NUMBER;
1005
1006 TYPE RefCurType is REF CURSOR;
1007 update_cursor RefCurType;
1008 l_lud date;
1009 BEGIN
1010 p_status := G_FINE;
1011 p_message := 'OK';
1012 l_query_start := sysdate;
1013
1014 JTM_MESSAGE_LOG_PKG.INSERT_CONC_STATUS_LOG
1015 (v_package_name => NULL
1016 ,v_procedure_name => NULL
1017 ,v_con_query_id => r_query_request.con_query_id
1018 ,v_query_stmt => G_CATEGORY
1019 ,v_start_time => l_query_start
1020 ,v_end_time => NULL
1021 ,v_status => 'Running'
1022 ,v_message => 'Processing for table ' ||r_query_request.acc_table_name
1023 ,x_log_id => l_start_log_id
1024 ,x_status => l_status
1025 ,x_msg_data => l_message);
1026
1027 IF (l_status = 'E') THEN
1028 RAISE JTM_MESSAGE_LOG_PKG.G_EXC_ERROR;
1029 END IF;
1030
1031 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1032 JTM_message_log_pkg.Log_Msg
1033 ( v_object_id => null
1034 , v_object_name => G_PACKAGE_NAME
1035 , v_message => 'Entering ' || G_PACKAGE_NAME ||'.Process_Request_v2'
1036 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1037 , v_module => 'jtm_message_log_pkg');
1038 END IF;
1039
1040 /** get primary_key of corresponding publication item **/
1041 OPEN c_primary_key(r_query_request.con_query_id);
1042 FETCH c_primary_key into l_primary_key;
1043
1044 IF c_primary_key%ROWCOUNT = 0 THEN
1045 /*** no application subscribed -> ignore this query ***/
1046 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1047 JTM_message_log_pkg.Log_Msg
1048 ( v_object_id => r_query_request.acc_table_name
1049 , v_object_name => G_PACKAGE_NAME
1050 , v_message => 'No application subscribed to query ' || r_query_request.con_query_id || '.' ||
1051 fnd_global.local_chr(10) || 'Ignoring this query.'
1052 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1053 , v_module => 'jtm_message_log_pkg');
1054 END IF;
1055
1056 p_status := G_ERROR;
1057 p_message := 'No primary key found for pub item related to query id ' ||
1058 r_query_request.con_query_id;
1059 ELSE
1060
1061 /*** one or more resources subscribed -> process publication item ***/
1062 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1063 JTM_message_log_pkg.Log_Msg
1064 ( v_object_id => r_query_request.acc_table_name
1065 , v_object_name => G_PACKAGE_NAME
1066 , v_message => 'There is at lesst one application regiesterd to this query'
1067 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1068 , v_module => 'jtm_message_log_pkg');
1069 END IF;
1070
1071 /*************************** PROCESS UPDATES ***************************/
1072
1073 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1074 JTM_message_log_pkg.Log_Msg
1075 ( v_object_id => r_query_request.acc_table_name
1076 , v_object_name => G_PACKAGE_NAME
1077 , v_message => 'Processing UPDATES'
1078 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1079 , v_module => 'jtm_message_log_pkg');
1080 END IF;
1081
1082 -- Check if query ran before
1083 IF r_query_request.last_run_date IS NOT NULL THEN
1084 -- Yes -> Get access_id of records that were updated since last_run_date
1085 l_dynamic_stmt := 'SELECT ACC.ACCESS_ID, B.LAST_UPDATE_DATE FROM ' ||
1086 r_query_request.acc_table_name||' ACC, ('|| r_query_request.con_query||') B ' ||
1087 'WHERE B.LAST_UPDATE_DATE <> ACC.LAST_UPDATE_DATE ' ||
1088 'AND ' || GET_CONDITION(l_primary_key,'ACC','B');
1089
1090 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
1091 JTM_message_log_pkg.Log_Msg
1092 ( v_object_id => r_query_request.acc_table_name
1093 , v_object_name => G_PACKAGE_NAME
1094 , v_message => 'Executing:' || fnd_global.local_chr(10) || l_dynamic_stmt || fnd_global.local_chr(10) ||
1095 'LAST_RUN_DATE = ' || to_char(r_query_request.last_run_date)
1096 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL
1097 , v_module => 'jtm_message_log_pkg');
1098 END IF;
1099
1100 l_update_count := 0;
1101 OPEN update_cursor for l_dynamic_stmt;
1102 LOOP
1103 FETCH update_cursor INTO m_tab_access_id(l_update_count+1), l_lud;
1104 EXIT WHEN update_cursor%NOTFOUND;
1105 EXECUTE IMMEDIATE 'Update ' || r_query_request.acc_table_name ||
1106 ' set last_update_date = :d where access_id = :a'
1107 using l_lud, m_tab_access_id(l_update_count+1);
1108 l_update_count := l_update_count + 1;
1109 END LOOP;
1110 CLOSE update_cursor;
1111
1112 IF l_update_count > 0 THEN
1113 -- 1 or more acc rows retrieved -> push to resources
1114 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1115 JTM_message_log_pkg.Log_Msg
1116 ( v_object_id => r_query_request.acc_table_name
1117 , v_object_name => G_PACKAGE_NAME
1118 , v_message => 'Pushing ' || l_update_count || ' updated record(s) to subscribed resources.'
1119 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1120 , v_module => 'jtm_message_log_pkg');
1121 END IF;
1122
1123 l_dummy := markdirty_helper(
1124 p_con_query_id => r_query_request.con_query_id
1125 ,p_accessList => m_tab_access_id
1126 ,p_dml_type => 'U'
1127 );
1128 END IF;
1129
1130 END IF; -- process UPDATES
1131
1132 /*************************** 2. PROCESS INSERTS ***************************/
1133 /***
1134 Insert new records to in ACC with COUNTER = 0.
1135 Then select all ACCESS_IDs from ACC where COUNTER = 0.
1136 Then update COUNTER to 1.
1137 This is a workaround for the fact that INSERT INTO with subquery cannot be used
1138 in combination with RETURNING and we need the ACCESS_IDs to push the records to the
1139 mobile users.
1140 ***/
1141
1142 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1143 JTM_message_log_pkg.Log_Msg
1144 ( v_object_id => r_query_request.acc_table_name
1145 , v_object_name => G_PACKAGE_NAME
1146 , v_message => 'Processing INSERTS'
1147 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1148 , v_module => 'jtm_message_log_pkg');
1149 END IF;
1150
1151 l_dynamic_stmt := 'INSERT INTO ' || r_query_request.acc_table_name ||
1152 '(' || l_primary_key || ', ACCESS_ID, COUNTER,' ||
1153 ' LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY) ' ||
1154 'SELECT ' || l_primary_key || ', JTM_ACC_TABLE_S.NEXTVAL, 0, LAST_UPDATE_DATE, 1, sysdate, 1' ||
1155 ' FROM (' || r_query_request.con_query || ') WHERE' ||
1156 ' (' || l_primary_key || ') NOT IN ' ||
1157 '(SELECT ' || l_primary_key || ' FROM ' || r_query_request.acc_table_name || ')';
1158
1159 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
1160 JTM_message_log_pkg.Log_Msg
1161 ( v_object_id => r_query_request.acc_table_name
1162 , v_object_name => G_PACKAGE_NAME
1163 , v_message => 'Executing:' || fnd_global.local_chr(10) || l_dynamic_stmt
1164 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL
1165 , v_module => 'jtm_message_log_pkg');
1166
1167 END IF;
1168 EXECUTE IMMEDIATE l_dynamic_stmt;
1169
1170 /*** Retrieve ACCESS_IDs for any inserted records ***/
1171 l_dynamic_stmt := 'SELECT ACCESS_ID FROM ' || r_query_request.acc_table_name ||
1172 ' WHERE COUNTER = 0';
1173 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
1174 JTM_message_log_pkg.Log_Msg
1175 ( v_object_id => r_query_request.acc_table_name
1176 , v_object_name => G_PACKAGE_NAME
1177 , v_message => 'Executing:' || fnd_global.local_chr(10) || l_dynamic_stmt
1178 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL
1179 , v_module => 'jtm_message_log_pkg');
1180 END IF;
1181 l_cursor := dbms_sql.open_cursor;
1182 dbms_sql.parse( l_cursor, l_dynamic_stmt, dbms_sql.v7);
1183 l_index := 1;
1184 l_tab_access_id.DELETE;
1185 dbms_sql.define_array( l_cursor, 1, l_tab_access_id, 100, l_index);
1186 l_count := dbms_sql.execute( l_cursor );
1187 LOOP
1188 l_count := dbms_sql.fetch_rows(l_cursor);
1189 dbms_sql.column_value( l_cursor, '1', l_tab_access_id);
1190 EXIT WHEN l_count <> 100;
1191 END LOOP;
1192 dbms_sql.close_cursor( l_cursor );
1193
1194 IF l_tab_access_id.COUNT > 0 THEN
1195 /*** 1 or more acc rows retrieved -> push to resources ***/
1196 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1197 JTM_message_log_pkg.Log_Msg
1198 ( v_object_id => r_query_request.acc_table_name
1199 , v_object_name => G_PACKAGE_NAME
1200 , v_message => 'Pushing ' || l_tab_access_id.COUNT || ' inserted record(s) to subscribed resources.'
1201 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1202 , v_module => 'jtm_message_log_pkg');
1203 END IF;
1204
1205 /*** push to oLite using asg_download ***/
1206
1207 FOR i IN l_tab_access_id.FIRST..l_tab_access_id.LAST LOOP
1208 m_tab_access_id(i) := l_tab_access_id(i);
1209 END LOOP;
1210
1211 l_dummy := markdirty_helper(
1212 p_con_query_id => r_query_request.con_query_id
1213 ,p_accessList => m_tab_access_id
1214 ,p_dml_type => 'I'
1215 );
1216
1217
1218 /*** set COUNTER to 1 in ACC table ***/
1219 l_dynamic_stmt := 'UPDATE ' || r_query_request.acc_table_name || ' SET COUNTER=1 WHERE COUNTER=0';
1220 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
1221 JTM_message_log_pkg.Log_Msg
1222 ( v_object_id => r_query_request.acc_table_name
1223 , v_object_name => G_PACKAGE_NAME
1224 , v_message => 'Executing:' || fnd_global.local_chr(10) || l_dynamic_stmt
1225 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL
1226 , v_module => 'jtm_message_log_pkg');
1227 END IF;
1228 EXECUTE IMMEDIATE l_dynamic_stmt;
1229 END IF; -- process INSERTS
1230
1231 /*************************** 3. PROCESS DELETES ***************************/
1232 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1233 JTM_message_log_pkg.Log_Msg
1234 ( v_object_id => r_query_request.acc_table_name
1235 , v_object_name => G_PACKAGE_NAME
1236 , v_message => 'Processing DELETES'
1237 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1238 , v_module => 'jtm_message_log_pkg');
1239 END IF;
1240
1241 /*** Check if query ran before ***/
1242 IF r_query_request.last_run_date IS NOT NULL THEN
1243
1244 l_dynamic_stmt := 'SELECT ACCESS_ID FROM ' || r_query_request.acc_table_name ||
1245 ' WHERE (' || l_primary_key || ') NOT IN (SELECT ' ||
1246 l_primary_key || ' FROM (' || r_query_request.con_query || '))';
1247
1248 l_cursor := dbms_sql.open_cursor;
1249 dbms_sql.parse( l_cursor, l_dynamic_stmt, dbms_sql.v7);
1250 l_index := 1;
1251 l_tab_access_id.DELETE;
1252 dbms_sql.define_array( l_cursor, 1, l_tab_access_id, 100, l_index);
1253 l_count := dbms_sql.execute( l_cursor );
1254 LOOP
1255 l_count := dbms_sql.fetch_rows(l_cursor);
1256 dbms_sql.column_value( l_cursor, '1', l_tab_access_id);
1257 EXIT WHEN l_count <> 100;
1258 END LOOP;
1259 dbms_sql.close_cursor( l_cursor );
1260
1261 IF l_tab_access_id.COUNT > 0 THEN
1262 /*** 1 or more acc rows retrieved -> push to resources ***/
1263 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1264 JTM_message_log_pkg.Log_Msg
1265 ( v_object_id => r_query_request.acc_table_name
1266 , v_object_name => G_PACKAGE_NAME
1267 , v_message => 'Pushing ' || l_tab_access_id.COUNT || ' deleted record(s) to subscribed resources.'
1268 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
1269 , v_module => 'jtm_message_log_pkg');
1270 END IF;
1271 /*** push to oLite using asg_download ***/
1272
1273 FOR i IN l_tab_access_id.FIRST..l_tab_access_id.LAST LOOP
1274 m_tab_access_id(i) := l_tab_access_id(i);
1275 END LOOP;
1276
1277
1278 l_dummy := markdirty_helper(
1279 p_con_query_id => r_query_request.con_query_id
1280 ,p_accessList => m_tab_access_id
1281 ,p_dml_type => 'D'
1282 );
1283
1284 END IF;
1285
1286 /* Delete record from acc table. */
1287 l_dynamic_stmt := 'DELETE ' || r_query_request.acc_table_name ||
1288 ' WHERE (' || l_primary_key || ') NOT IN (SELECT ' ||
1289 l_primary_key || ' FROM (' || r_query_request.con_query || '))';
1290
1291 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL THEN
1292 JTM_message_log_pkg.Log_Msg
1293 ( v_object_id => r_query_request.acc_table_name
1294 , v_object_name => G_PACKAGE_NAME
1295 , v_message => 'Executing:' || fnd_global.local_chr(10) || l_dynamic_stmt
1296 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_SQL
1297 , v_module => 'jtm_message_log_pkg');
1298 END IF;
1299
1300 l_cursor := dbms_sql.open_cursor;
1301 dbms_sql.parse( l_cursor, l_dynamic_stmt, dbms_sql.v7);
1302 l_count := dbms_sql.execute( l_cursor );
1303 dbms_sql.close_cursor( l_cursor );
1304 END IF;
1305 /*************************** 3. PROCESS DELETES DONE***************************/
1306
1307 p_message := 'Successfully processing with query id ' ||
1308 r_query_request.con_query_id;
1309 END IF;
1310 CLOSE c_primary_key;
1311
1312 JTM_MESSAGE_LOG_PKG.UPDATE_CONC_STATUS_LOG
1313 (v_log_id =>l_start_log_id
1314 ,v_query_stmt => G_Category
1315 ,v_start_time => l_query_start
1316 ,v_end_time => sysdate
1317 ,v_status => p_status
1318 ,v_message => p_message
1319 ,x_status => l_status
1320 ,x_msg_data => l_message);
1321
1322 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1323 JTM_message_log_pkg.Log_Msg
1324 ( v_object_id => r_query_request.acc_table_name
1325 , v_object_name => G_PACKAGE_NAME
1326 , v_message => 'Leaving ' || G_PACKAGE_NAME ||'.Process_Request_v2'
1327 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1328 , v_module => 'jtm_message_log_pkg');
1329 END IF;
1330
1331 EXCEPTION
1332 WHEN OTHERS THEN
1333 CLOSE c_primary_key;
1334
1335 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1336 JTM_message_log_pkg.Log_Msg
1337 ( v_object_id => r_query_request.acc_table_name
1338 , v_object_name => G_PACKAGE_NAME
1339 , v_message => 'Leaving ' || G_PACKAGE_NAME ||'.Process_Request_v2 after exception.'
1340 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
1341 , v_module => 'jtm_message_log_pkg');
1342 END IF;
1343
1344 p_status := G_ERROR;
1345 p_message := 'Exception ocurrs while processing query with id ' ||
1346 r_query_request.con_query_id || ': ' || sqlerrm;
1347
1348 JTM_MESSAGE_LOG_PKG.UPDATE_CONC_STATUS_LOG
1349 (v_log_id =>l_start_log_id
1350 ,v_query_stmt => G_Category
1351 ,v_start_time => l_query_start
1352 ,v_end_time => sysdate
1353 ,v_status => p_status
1354 ,v_message => p_message
1355 ,x_status => l_status
1356 ,x_msg_data => l_message);
1357
1358 END Process_Request_v2;
1359
1360 PROCEDURE RUN_QUERY_REQUESTS IS
1361 PRAGMA AUTONOMOUS_TRANSACTION;
1362 l_Status VARCHAR2(80);
1363 l_Message VARCHAR2(2000);
1364 BEGIN
1365 RUN_QUERY_REQUESTS(l_status, l_message);
1366 END;
1367
1368
1369 PROCEDURE RUN_QUERY_REQUESTS(
1370 P_Status OUT NOCOPY VARCHAR2,
1371 P_Message OUT NOCOPY VARCHAR2) IS
1372 PRAGMA AUTONOMOUS_TRANSACTION;
1373 L_API_NAME CONSTANT VARCHAR2(30) := 'RUN_QUERY_REQUESTS';
1374 r_query_request c_query_requests%ROWTYPE;
1375 l_retcode number;
1376 run_query_error exception;
1377 l_status varchar2(30);
1378 l_message varchar2(2000);
1379
1380 BEGIN
1381 l_retcode := 0;
1382 g_category := GET_CATEGORY;
1383
1384 P_Status := G_FINE;
1385 P_Message:= 'OK';
1386
1387 /*** get debug level ***/
1388 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
1389
1390 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1391 JTM_message_log_pkg.Log_Msg
1392 ( v_object_id => null
1393 , v_object_name => G_PACKAGE_NAME
1394 , v_message => 'Entering '||G_PACKAGE_NAME||'.' || L_API_NAME
1395 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1396 , v_module => 'jtm_message_log_pkg');
1397 END IF;
1398
1399 -- loop over query requests
1400 FOR r_query_request IN c_query_requests LOOP
1401 UPDATE jtm_con_query_request_data
1402 SET LAST_TXC_START = sysdate,
1403 LAST_TXC_END = null,
1404 STATUS = 'Running',
1405 COMPLETION_TEXT = 'Processing the query with id '
1406 || r_query_request.con_query_id
1407 WHERE con_query_id = r_query_request.con_query_id;
1408 commit;
1409
1410 BEGIN
1411 /* PWU: Temporily solution. Eventually we will use only the v2 procedure
1412 and absolete the original one */
1413 if (r_query_request.con_query_id >= 53 AND
1414 r_query_request.con_query_id <= 67 ) then
1415 Process_Request_v2( r_query_request,l_status,l_message );
1416 else
1417 Process_Request( r_query_request,l_status,l_message );
1418 end if;
1419 IF (l_status = G_ERROR) then
1420 P_Status := G_ERROR;
1421 IF (P_Message = 'OK' ) THEN
1422 P_Message := l_message;
1423 ELSE
1424 P_Message := P_Message || '; ' || l_message;
1425 END IF;
1426 END IF;
1427 COMMIT;
1428
1429 EXCEPTION
1430 WHEN OTHERS THEN
1431 l_status := G_ERROR;
1432 l_message := 'Exception ocurrs while processing query with id ' ||
1433 r_query_request.con_query_id;
1434 P_Status := G_ERROR;
1435 IF (P_Message = 'OK' ) THEN
1436 P_Message:= l_message;
1437 ELSE
1438 P_Message:= P_Message || '. ' || l_message;
1439 END IF;
1440 ROLLBACK;
1441 --**** todo: return FND_NEW_MESSAGE error
1442 l_retcode := -1;
1443 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1444 JTM_message_log_pkg.Log_Msg
1445 ( v_object_id => r_query_request.acc_table_name
1446 , v_object_name => G_PACKAGE_NAME
1447 , v_message => 'Unexpected error encountered in Process_Request: ' || fnd_global.local_chr(10) || sqlerrm
1448 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
1449 , v_module => 'jtm_message_log_pkg');
1450 END IF;
1451 END;
1452
1453 IF (l_status = G_ERROR) THEN
1454 UPDATE jtm_con_query_request_data
1455 SET LAST_TXC_END = sysdate,
1456 STATUS = l_status,
1457 COMPLETION_TEXT = l_message
1458 WHERE con_query_id = r_query_request.con_query_id;
1459 ELSE
1460 UPDATE jtm_con_query_request_data
1461 SET LAST_TXC_END = sysdate,
1462 last_run_date = last_txc_start,
1463 STATUS = l_status,
1464 COMPLETION_TEXT = l_message
1465 WHERE con_query_id = r_query_request.con_query_id;
1466 END IF;
1467 COMMIT;
1468
1469 END LOOP;
1470
1471 IF (P_Message = 'OK') then
1472 P_Message:= 'The concurrent query program is working fine';
1473 END IF;
1474 UPDATE JTM_CON_REQUEST_DATA SET LAST_RUN_DATE = SYSDATE
1475 WHERE PRODUCT_CODE = 'JTM'
1476 AND PACKAGE_NAME = G_PACKAGE_NAME
1477 AND UPPER(PROCEDURE_NAME) = L_API_NAME;
1478 COMMIT;
1479
1480 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1481 JTM_message_log_pkg.Log_Msg
1482 ( v_object_id => null
1483 , v_object_name => G_PACKAGE_NAME
1484 , v_message => 'Leaving '||G_PACKAGE_NAME||'.RUN_QUERY_REQUESTS'
1485 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1486 , v_module => 'jtm_message_log_pkg');
1487 END IF;
1488
1489 if l_retcode = -1 then
1490 RAISE run_query_error;
1491 END IF;
1492
1493 EXCEPTION
1494 WHEN run_query_error THEN
1495 ROLLBACK;
1496 RAISE;
1497 WHEN OTHERS THEN
1498 ROLLBACK;
1499
1500 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1501 JTM_message_log_pkg.Log_Msg
1502 (v_object_id => null
1503 ,v_object_name => G_PACKAGE_NAME
1504 ,v_message => 'Unexpected error encountered in RUN_QUERY_REQUESTS:'
1505 || fnd_global.local_chr(10) || sqlerrm
1506 ,v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
1507 ,v_module => 'jtm_message_log_pkg');
1508 END IF;
1509
1510 END RUN_QUERY_REQUESTS;
1511
1512 END JTM_CON_QUERY_REQUEST_PKG;