[Home] [Help]
PACKAGE BODY: APPS.QP_JAVA_ENGINE_CACHE_PVT
Source
1 PACKAGE BODY QP_JAVA_ENGINE_CACHE_PVT AS
2 /* $Header: QPXJCCVB.pls 120.2 2006/03/09 16:09:52 hwong noship $ */
3
4 l_debug VARCHAR2(3);
5
6 PROCEDURE UPDATE_CACHE_STATS
7 (
8 err_buff OUT NOCOPY VARCHAR2,
9 retcode OUT NOCOPY NUMBER
10 )
11 IS
12
13 /*****************************************************************
14 Cursors for permanent full data objects
15 *****************************************************************/
16
17 CURSOR pattern_csr IS
18 SELECT count(*)
19 FROM qp_patterns;
20
21 CURSOR segment_csr IS
22 SELECT count(*)
23 FROM qp_segments_b qps,
24 qp_prc_contexts_b qpc
25 WHERE qps.prc_context_id = qpc.prc_context_id;
26
27 CURSOR request_source_csr IS
28 SELECT count(*)
29 FROM qp_price_req_sources;
30
31 CURSOR event_phase_csr IS
32 SELECT count(*)
33 FROM qp_pricing_phases qpp,
34 qp_event_phases qpe
35 WHERE qpe.pricing_phase_id = qpp.pricing_phase_id;
36
37 CURSOR profile_csr IS
38 SELECT count(*)
39 FROM FND_PROFILE_OPTIONS o, FND_PROFILE_OPTION_VALUES ov
40 WHERE o.APPLICATION_ID = 661 and o.PROFILE_OPTION_ID = ov.PROFILE_OPTION_ID (+) and o.APP_ENABLED_FLAG = 'N' and o.RESP_ENABLED_FLAG = 'N' and o.USER_ENABLED_FLAG = 'N';
41
42 CURSOR cache_do_size_csr IS
43 SELECT count(*)
44 FROM qp_cache_do_sizes;
45
46 CURSOR cache_stat_csr IS
47 SELECT count(*)
48 FROM qp_cache_stats;
49
50 CURSOR header_csr IS
51 SELECT count(*)
52 FROM qp_list_headers_b
53 WHERE active_flag = 'Y';
54
55 CURSOR second_price_list_csr IS
56 SELECT count(*)
57 FROM qp_qualifiers qpq, qp_list_headers_b qph1, qp_list_headers_b qph2
58 WHERE qualifier_context = 'MODLIST' and qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
59 and qph1.active_flag = 'Y' and qph2.active_flag = 'Y'
60 and qph1.list_header_id = qpq.list_header_id
61 and to_char(qph2.list_header_id) = qpq.qualifier_attr_value
62 and qph1.list_type_code = 'PRL';
63
64 CURSOR header_attr_csr IS
65 SELECT count(*) FROM (
66 SELECT 1
67 FROM qp_attribute_groups qp, qp_list_headers_b qph
68 WHERE qp.list_line_id = -1 and qph.active_flag = 'Y' and qp.list_header_id(+) = qph.list_header_id UNION ALL
69 SELECT 1
70 FROM qp_list_headers_b qp
71 WHERE qp.active_flag = 'Y' and NOT EXISTS (SELECT * FROM qp_attribute_groups qpg WHERE qpg.list_line_id=-1 and qpg.list_header_id = qp.list_header_id));
72
73 CURSOR header_ph_csr IS
74 SELECT count(*)
75 FROM (select /*+ ordered index(qph qp_list_headers_b_n7) use_nl(qpl) */
76 distinct qpl.pricing_phase_id, qpl.list_header_id, qph.currency_code
77 FROM qp_list_headers_b qph, qp_list_lines qpl
78 WHERE qph.active_flag = 'Y'
79 and qph.ask_for_flag <> 'Y'
80 and qph.list_header_id = qpl.list_header_id);
81
82 CURSOR pattern_ph_csr IS
83 SELECT count(*)
84 FROM qp_pattern_phases qpph, qp_patterns qpp
85 WHERE qpp.pattern_type = 'HP' and qpp.pattern_id = qpph.pattern_id;
86
87 CURSOR okc_uom_csr IS
88 SELECT count(*)
89 FROM okx_units_of_measure_v;
90
91 CURSOR okc_time_unit_csr IS
92 SELECT count(*)
93 FROM okc_time_code_units_b
94 WHERE active_flag = 'Y';
95
96 /*****************************************************************
97 Cursors for permanent partial data objects
98 *****************************************************************/
99
100 CURSOR line_perm_csr IS
101 SELECT count(*)
102 FROM qp_list_headers_b qph, qp_list_lines qpl
103 WHERE qpl.qualification_ind in (0, 2)
104 and qpl.list_header_id = qph.list_header_id
105 and qph.active_flag = 'Y';
106
107 CURSOR uom_perm_csr IS
108 SELECT count(*)
109 FROM mtl_uom_conversions
110 WHERE (disable_date is null or disable_date > sysdate)
111 and inventory_item_id = 0;
112
113 /*****************************************************************
114 Cursors for cache-keys
115 *****************************************************************/
116
117 CURSOR modifier_cache_key_cnt_csr IS
118 SELECT /*+ ordered use_nl(qpl) index(qph qp_list_headers_b_n7) */ count(DISTINCT
119 qpl.cache_key)
120 FROM qp_list_headers_b qph, qp_list_lines qpl
121 WHERE qph.active_flag = 'Y'
122 and qph.list_type_code not in ('PML', 'PRL', 'AGR')
123 and qpl.list_header_id = qph.list_header_id;
124
125 CURSOR price_cache_key_cnt_csr IS
126 SELECT /*+ ordered use_nl(qpl) index(qph qp_list_headers_b_n7) */ count(DISTINCT
127 qpl.cache_key)
128 FROM qp_list_headers_b qph, qp_list_lines qpl
129 WHERE qph.active_flag = 'Y'
130 and qph.list_type_code = 'PRL'
131 and qpl.list_header_id = qph.list_header_id;
132
133 CURSOR formula_cache_key_cnt_csr IS
134 SELECT count(*)
135 FROM qp_price_formulas qpf;
136
137 CURSOR factor_cache_key_cnt_csr IS
138 SELECT count(*)
139 FROM qp_list_headers_b qlh
140 WHERE qlh.list_type_code = 'PML';
141
142 CURSOR uom_cache_key_cnt_csr IS
143 SELECT count(*)
144 FROM mtl_uom_conversions
145 WHERE (disable_date IS null or disable_date > sysdate)
146 and inventory_item_id <> 0;
147
148 CURSOR currency_cache_key_cnt_csr IS
149 SELECT count(DISTINCT qpc.currency_header_id)
150 FROM qp_currency_lists_b qpc, qp_list_headers_b qph
151 WHERE qph.active_flag = 'Y'
152 and qph.currency_code = qpc.base_currency_code;
153
154 CURSOR modifier_cache_key_csr IS
155 SELECT /*+ ordered use_nl(qpl) index(qph qp_list_headers_b_n7) */ DISTINCT
156 qpl.cache_key
157 FROM qp_list_headers_b qph, qp_list_lines qpl
158 WHERE qph.active_flag = 'Y'
159 and qph.list_type_code not in ('PML', 'PRL', 'AGR')
160 and qpl.list_header_id = qph.list_header_id
161 and ROWNUM < 200;
162
163 CURSOR price_cache_key_csr IS
164 SELECT /*+ ordered use_nl(qpl) index(qph qp_list_headers_b_n7) */ DISTINCT
165 qpl.cache_key
166 FROM qp_list_headers_b qph, qp_list_lines qpl
167 WHERE qph.active_flag = 'Y'
168 and qph.list_type_code = 'PRL'
169 and qpl.list_header_id = qph.list_header_id
170 and ROWNUM < 200;
171
172 CURSOR formula_cache_key_csr IS
173 SELECT
174 qpf.price_formula_id
175 FROM qp_price_formulas qpf
176 WHERE ROWNUM < 200;
177
178 CURSOR factor_cache_key_csr IS
179 SELECT
180 qlh.list_header_id
181 FROM qp_list_headers_b qlh
182 WHERE qlh.list_type_code = 'PML'
183 and ROWNUM < 200;
184
185 CURSOR uom_cache_key_csr IS
186 SELECT
187 inventory_item_id
188 FROM mtl_uom_conversions
189 WHERE (disable_date IS null or disable_date > sysdate)
190 and inventory_item_id <> 0
191 and ROWNUM < 200;
192
193 CURSOR currency_cache_key_csr IS
194 SELECT DISTINCT
195 qpc.currency_header_id
196 FROM qp_currency_lists_b qpc, qp_list_headers_b qph
197 WHERE qph.active_flag = 'Y'
198 and qph.currency_code = qpc.base_currency_code
199 and ROWNUM < 200;
200
201 /*****************************************************************
202 Cursors for on-demand data objects
203 *****************************************************************/
204
205 CURSOR line_csr(p_cache_key varchar2) IS
206 SELECT sum(c) FROM (
207 SELECT /*+ ordered use_nl(qph, qpr) index(qph qp_list_headers_b_pk) index(qpa qp_pricing_attributes_n2) */ count(*) c
208 FROM qp_list_lines qpl, qp_list_headers_b qph, qp_pricing_attributes qpa
209 WHERE qph.active_flag = 'Y' and qpl.list_header_id = qph.list_header_id and qpl.cache_key = p_cache_key and qpl.list_line_id = qpa.list_line_id(+)
210 UNION ALL
211 SELECT /*+ ordered use_nl(qph, qpr) index(qph qp_list_headers_b_pk) index(qpa qp_pricing_attributes_n2) */ count(*) c
212 FROM qp_list_lines qpl2, qp_rltd_modifiers qpr, qp_list_lines qpl, qp_list_headers_b qph, qp_pricing_attributes qpa
213 WHERE qph.active_flag = 'Y' and qpl.list_header_id = qph.list_header_id and qpl2.cache_key = p_cache_key and qpl2.list_line_id = qpr.from_rltd_modifier_id and qpl.list_line_id = qpr.to_rltd_modifier_id and qpl.list_line_id = qpa.list_line_id(+)
214 );
215
216 CURSOR line_attr_grp_csr(p_cache_key varchar2) IS
217 SELECT sum(c) FROM (
218 SELECT count(*) c
219 FROM qp_attribute_groups qp, qp_list_headers_b qph
220 WHERE qp.pricing_phase_id <> -1 and qph.active_flag = 'Y' and qp.cache_key = p_cache_key and qp.list_header_id = qph.list_header_id
221 UNION ALL
222 SELECT /*+ ordered use_nl(qph) */ count(*) c
223 FROM qp_list_lines qp, qp_list_headers_b qph
224 WHERE qph.active_flag = 'Y' and qp.cache_key = p_cache_key and (qp.pattern_id IS not null or (qp.pattern_id is null and qp.qualification_ind in (0, 2))) and qp.list_header_id = qph.list_header_id
225 );
226
227 CURSOR non_eq_attr_csr(p_cache_key varchar2) IS
228 SELECT sum(c) FROM (
229 SELECT /*+ ordered use_nl(qpr, qph, qpaq) */ count(*) c
230 FROM qp_list_lines qplag, qp_list_headers_b qph, qp_pricing_attributes qpaq
231 WHERE qph.active_flag = 'Y' and qph.list_header_id = qplag.list_header_id and qpaq.list_line_id = qplag.list_line_id and qpaq.pricing_segment_id is not null and qplag.cache_key = p_cache_key and qpaq.comparison_operator_code <> '='
232 UNION ALL
233 SELECT /*+ ordered use_nl(qpr, qph, qpaq) */ count(*) c
234 FROM qp_list_lines qplag, qp_list_headers_b qph, qp_qualifiers qpaq
235 WHERE qph.active_flag = 'Y' and qph.list_header_id = qplag.list_header_id and qpaq.list_line_id = qplag.list_line_id and qpaq.segment_id is not null and qplag.cache_key = p_cache_key and qpaq.comparison_operator_code <> '='
236 UNION ALL
237 SELECT /*+ ordered use_nl(qpr, qph, qpaq) */ count(*) c
238 FROM qp_list_lines qpl, qp_rltd_modifiers qpr, qp_list_lines qplag, qp_list_headers_b qph, qp_pricing_attributes qpaq
239 WHERE qph.active_flag = 'Y' and qph.list_header_id = qplag.list_header_id and qpaq.list_line_id = qplag.list_line_id and qpaq.pricing_segment_id is not null and qpl.cache_key = p_cache_key
240 and qpl.list_line_id = qpr.from_rltd_modifier_id and qplag.list_line_id = qpr.to_rltd_modifier_id and qpaq.comparison_operator_code <> '='
241 UNION ALL
242 SELECT /*+ ordered use_nl(qpr, qph, qpaq) */ count(*) c
243 FROM qp_list_lines qpl, qp_rltd_modifiers qpr, qp_list_lines qplag, qp_list_headers_b qph, qp_qualifiers qpaq
244 WHERE qph.active_flag = 'Y' and qph.list_header_id = qplag.list_header_id and qpaq.list_line_id = qplag.list_line_id and qpaq.segment_id is not null and qpl.cache_key = p_cache_key
245 and qpl.list_line_id = qpr.from_rltd_modifier_id and qplag.list_line_id = qpr.to_rltd_modifier_id and qpaq.comparison_operator_code <> '='
246 );
247
248 CURSOR uom_csr (p_cache_key varchar2) IS
249 SELECT count(*)
250 FROM mtl_uom_conversions
251 WHERE (disable_date is null or disable_date > sysdate) and inventory_item_id = p_cache_key;
252
253 CURSOR uom_class_csr (p_cache_key varchar2) IS
254 SELECT count(*)
255 FROM mtl_uom_class_conversions
256 WHERE (disable_date is null or disable_date > sysdate) and inventory_item_id = p_cache_key;
257
258 CURSOR formula_csr(p_cache_key varchar2) IS
259 SELECT count(*)
260 FROM qp_price_formulas
261 WHERE price_formula_id = p_cache_key;
262
263 CURSOR formula_line_csr(p_cache_key varchar2) IS
264 SELECT count(*)
265 FROM qp_price_formula_lines
266 WHERE price_formula_id = p_cache_key;
267
268 CURSOR currency_csr(p_cache_key varchar2) IS
269 SELECT count(*)
270 FROM qp_list_headers_b qph, qp_currency_lists_b qpc
271 WHERE qph.active_flag = 'Y' and qpc.currency_header_id = p_cache_key and qph.currency_code = qpc.base_currency_code;
272
273 -- cursor rewritten for sql repository exercise
274 CURSOR currency_line_csr(p_cache_key varchar2) IS
275 SELECT count(*)
276 FROM qp_currency_details qpd
277 WHERE exists
278 (select 'X' from qp_list_headers_b
279 where list_type_code in ('AGR', 'PRL')
280 and active_flag = 'Y'
281 and currency_header_id = p_cache_key)
282 AND qpd.currency_header_id = p_cache_key;
283
284 CURSOR do_size_csr IS
285 SELECT
286 do_name,
287 do_size
288 FROM qp_cache_do_sizes
289 order by do_name;
290
291 l_cache_key_header_tbl number_tbl_type;
292 l_cache_key_attr_tbl varchar1000_tbl_type;
293 l_cache_key_val_tbl varchar1000_tbl_type;
294 l_do_type_tbl varchar30_tbl_type;
295 l_do_size_tbl number_tbl_type;
296 l_cache_key_size_tbl number_tbl_type;
297
298 l_cache_type_tbl varchar30_tbl_type;
299 l_cache_size_tbl number_tbl_type;
300 l_creation_date_final_tbl date_tbl_type;
301 l_created_by_final_tbl number_tbl_type;
302 l_last_update_date_final_tbl date_tbl_type;
303 l_last_updated_by_final_tbl number_tbl_type;
304 l_last_update_login_final_tbl number_tbl_type;
305 l_program_appl_id_final_tbl number_tbl_type;
306 l_program_id_final_tbl number_tbl_type;
307 l_program_upd_date_final_tbl date_tbl_type;
308 l_request_id_final_tbl number_tbl_type;
309
310 l_total_cache_key_size number_tbl_type;
311 l_perm_cache_key_size number := 0;
312
313 l_max_cache_key_size number_tbl_type;
314 l_max_cache_key_header number_tbl_type;
315 l_max_cache_key_val varchar240_tbl_type;
316
317 l_ondemand_size number;
318 l_full_size number;
319 l_perm_full_size number;
320 l_perm_partial_size number;
321 l_perm_size number;
322 l_min_size number;
323
324 l_avg_cache_key_size number_tbl_type;
325
326 l_cache_key_cnt number;
327 l_cache_key_pool_cnt number;
328 l_cache_key_increment number;
329 l_cache_key_max number := 50;
330 l_i number := 1;
331 l_calc_cnt number;
332 l_cnt number;
333
334 -- cache-key types
335 L_MODIFIER_CACHE_KEY CONSTANT NUMBER := 0;
336 L_PRICE_CACHE_KEY CONSTANT NUMBER := 1;
337 L_FORMULA_CACHE_KEY CONSTANT NUMBER := 2;
338 L_FACTOR_CACHE_KEY CONSTANT NUMBER := 3;
339 L_UOM_CACHE_KEY CONSTANT NUMBER := 4;
340 L_CURRENCY_CACHE_KEY CONSTANT NUMBER := 5;
341 L_LAST_CACHE_KEY CONSTANT NUMBER := 5;
342
343 -- data object sizes
344 l_pattern_size number;
345 l_segment_size number;
346 l_request_source_size number;
347 l_event_phase_size number;
348 l_profile_size number;
349 l_cache_do_size_size number;
350 l_cache_stat_size number;
351 l_header_size number;
352 l_second_price_list_size number;
353 l_header_attr_size number;
354 l_header_ph_size number;
355 l_pattern_ph_size number;
356 l_okc_uom_size number;
357 l_okc_time_unit_size number;
358 l_cache_key_stat_size number;
359 l_line_size number;
360 l_line_attr_grp_size number;
361 l_non_eq_attr_size number;
362 l_uom_size number;
363 l_uom_class_size number;
364 l_formula_size number;
365 l_formula_line_size number;
366 l_currency_size number;
367 l_currency_line_size number;
368
369 /* misc debug
370 l_line_attr_grp_cache_key_cnt number;
371 l_line_cnt number;
372 l_line_attr_grp_cnt number;
373 l_uom_cnt number;
374 */
375
376 BEGIN
377 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
378 err_buff := '';
379 retcode := 0;
380
381 /*****************************************************************
382 Retrieve data object sizes
383 *****************************************************************/
384
385 OPEN do_size_csr;
386 FETCH do_size_csr bulk collect INTO
387 l_do_type_tbl,
388 l_do_size_tbl;
389 CLOSE do_size_csr;
390
391 for i in 1..l_do_type_tbl.last loop
392
393 if (l_do_type_tbl(i) = 'PatternDO') then
394 l_pattern_size := l_do_size_tbl(i);
395 elsif (l_do_type_tbl(i) = 'SegmentDO') then
396 l_segment_size := l_do_size_tbl(i);
397 elsif (l_do_type_tbl(i) = 'RequestSourceDO') then
398 l_request_source_size := l_do_size_tbl(i);
399 elsif (l_do_type_tbl(i) = 'EventPhaseDO') then
400 l_event_phase_size := l_do_size_tbl(i);
401 elsif (l_do_type_tbl(i) = 'ProfileDO') then
402 l_profile_size := l_do_size_tbl(i);
403 elsif (l_do_type_tbl(i) = 'CacheDOSizeDO') then
404 l_cache_do_size_size := l_do_size_tbl(i);
405 elsif (l_do_type_tbl(i) = 'CacheStatDO') then
406 l_cache_stat_size := l_do_size_tbl(i);
407 elsif (l_do_type_tbl(i) = 'HeaderDO') then
408 l_header_size := l_do_size_tbl(i);
409 elsif (l_do_type_tbl(i) = 'SecondPriceListDO') then
410 l_second_price_list_size := l_do_size_tbl(i);
411 elsif (l_do_type_tbl(i) = 'HeaderAttrGrpDO') then
412 l_header_attr_size := l_do_size_tbl(i);
413 elsif (l_do_type_tbl(i) = 'HeaderPhDO') then
414 l_header_ph_size := l_do_size_tbl(i);
415 elsif (l_do_type_tbl(i) = 'PatternPhDO') then
416 l_pattern_ph_size := l_do_size_tbl(i);
417 elsif (l_do_type_tbl(i) = 'OKCUOMDO') then
418 l_okc_uom_size := l_do_size_tbl(i);
419 elsif (l_do_type_tbl(i) = 'OKCTimeUnitDO') then
420 l_okc_time_unit_size := l_do_size_tbl(i);
421
422 elsif (l_do_type_tbl(i) = 'LineDO') then
423 l_line_size := l_do_size_tbl(i);
424 elsif (l_do_type_tbl(i) = 'UOMDO') then
425 l_uom_size := l_do_size_tbl(i);
426 elsif (l_do_type_tbl(i) = 'UOMClassDO') then
427 l_uom_class_size := l_do_size_tbl(i);
428
429 elsif (l_do_type_tbl(i) = 'NonEqAttrDO') then
430 l_non_eq_attr_size := l_do_size_tbl(i);
431
432 elsif (l_do_type_tbl(i) = 'CacheKeyStatDO') then
433 l_cache_key_stat_size := l_do_size_tbl(i);
434 elsif (l_do_type_tbl(i) = 'LineAttrGrpDO') then
435 l_line_attr_grp_size := l_do_size_tbl(i);
436
437 elsif (l_do_type_tbl(i) = 'FormulaDO') then
438 l_formula_size := l_do_size_tbl(i);
439 elsif (l_do_type_tbl(i) = 'FormulaLineDO') then
440 l_formula_line_size := l_do_size_tbl(i);
441
442 elsif (l_do_type_tbl(i) = 'CurrencyDO') then
443 l_currency_size := l_do_size_tbl(i);
444 elsif (l_do_type_tbl(i) = 'CurrencyLineDO') then
445 l_currency_line_size := l_do_size_tbl(i);
446 END if;
447 END loop;
448
449 /*****************************************************************
450 Calculate on-demand cache statistics
451 - populate l_avg_cache_key_size(ck_type)
452 - populate l_max_cache_key_size(ck_type)
453 - populate l_total_cache_key_size(ck_type)
454 *****************************************************************/
455
456 for ck_type in 0..L_LAST_CACHE_KEY loop
457 l_total_cache_key_size(ck_type) := 0;
458 l_max_cache_key_size(ck_type) := 0;
459 l_max_cache_key_header(ck_type) := 0;
460 l_max_cache_key_val(ck_type) := 'none';
461 l_avg_cache_key_size(ck_type) := 0;
462 END loop;
463
464 for ck_type in 0..l_max_cache_key_size.last loop
465
466 l_total_cache_key_size(ck_type) := 0;
467 l_cache_key_val_tbl.delete;
468
469 -- get pool of cache-keys
470 if (ck_type = L_MODIFIER_CACHE_KEY) then
471 OPEN modifier_cache_key_csr;
472 FETCH modifier_cache_key_csr bulk collect INTO
473 l_cache_key_val_tbl;
474 CLOSE modifier_cache_key_csr;
475 elsif (ck_type = L_PRICE_CACHE_KEY) then
476 OPEN price_cache_key_csr;
477 FETCH price_cache_key_csr bulk collect INTO
478 l_cache_key_val_tbl;
479 CLOSE price_cache_key_csr;
480 elsif (ck_type = L_FORMULA_CACHE_KEY) then
481 OPEN formula_cache_key_csr;
482 FETCH formula_cache_key_csr bulk collect INTO
483 l_cache_key_val_tbl;
484 CLOSE formula_cache_key_csr;
485 elsif (ck_type = L_FACTOR_CACHE_KEY) then
486 OPEN factor_cache_key_csr;
487 FETCH factor_cache_key_csr bulk collect INTO
488 l_cache_key_val_tbl;
489 CLOSE factor_cache_key_csr;
490 elsif (ck_type = L_UOM_CACHE_KEY) then
491 OPEN uom_cache_key_csr;
492 FETCH uom_cache_key_csr bulk collect INTO
493 l_cache_key_val_tbl;
494 CLOSE uom_cache_key_csr;
495 elsif (ck_type = L_CURRENCY_CACHE_KEY) then
496 OPEN currency_cache_key_csr;
497 FETCH currency_cache_key_csr bulk collect INTO
498 l_cache_key_val_tbl;
499 CLOSE currency_cache_key_csr;
500 END if;
501
502 -- get count
503 if (ck_type = L_MODIFIER_CACHE_KEY) then
504 OPEN modifier_cache_key_cnt_csr;
505 FETCH modifier_cache_key_cnt_csr INTO
506 l_cache_key_cnt;
507 CLOSE modifier_cache_key_cnt_csr;
508 elsif (ck_type = L_PRICE_CACHE_KEY) then
509 OPEN price_cache_key_cnt_csr;
510 FETCH price_cache_key_cnt_csr INTO
511 l_cache_key_cnt;
512 CLOSE price_cache_key_cnt_csr;
513 elsif (ck_type = L_FORMULA_CACHE_KEY) then
514 OPEN formula_cache_key_cnt_csr;
515 FETCH formula_cache_key_cnt_csr INTO
516 l_cache_key_cnt;
517 CLOSE formula_cache_key_cnt_csr;
518 elsif (ck_type = L_FACTOR_CACHE_KEY) then
519 OPEN factor_cache_key_cnt_csr;
520 FETCH factor_cache_key_cnt_csr INTO
521 l_cache_key_cnt;
522 CLOSE factor_cache_key_cnt_csr;
523 elsif (ck_type = L_UOM_CACHE_KEY) then
524 OPEN uom_cache_key_cnt_csr;
525 FETCH uom_cache_key_cnt_csr INTO
526 l_cache_key_cnt;
527 CLOSE uom_cache_key_cnt_csr;
528 elsif (ck_type = L_CURRENCY_CACHE_KEY) then
529 OPEN currency_cache_key_cnt_csr;
530 FETCH currency_cache_key_cnt_csr INTO
531 l_cache_key_cnt;
532 CLOSE currency_cache_key_cnt_csr;
533 END if;
534
535 l_cache_key_pool_cnt := l_cache_key_val_tbl.count;
536 l_cache_key_increment := round(l_cache_key_pool_cnt / l_cache_key_max, 0);
537 if (l_cache_key_increment < 1) then
538 l_cache_key_increment := 1;
539 END if;
540 l_calc_cnt := 0;
541
542 l_cache_key_size_tbl.delete;
543
544 if (l_cache_key_pool_cnt > 0) then
545
546 l_i := 1;
547 while (l_i <= l_cache_key_val_tbl.last) loop
548 --dbms_output.put_line('i: ' || l_i);
549 l_cache_key_size_tbl(l_i) := 0;
550 l_calc_cnt := l_calc_cnt + 1;
551
552 if (ck_type in (L_MODIFIER_CACHE_KEY, L_PRICE_CACHE_KEY, L_FACTOR_CACHE_KEY)) then
553 OPEN line_csr(l_cache_key_val_tbl(l_i));
554 FETCH line_csr INTO l_cnt;
555 CLOSE line_csr;
556 --dbms_output.put_line('a l_cnt: ('||l_cache_key_val_tbl(l_i)||') '||l_cnt);
557 l_cache_key_size_tbl(l_i) := l_cache_key_size_tbl(l_i) + (l_cnt*l_line_size);
558
559 OPEN line_attr_grp_csr(l_cache_key_val_tbl(l_i));
560 FETCH line_attr_grp_csr INTO l_cnt;
561 CLOSE line_attr_grp_csr;
562 l_cache_key_size_tbl(l_i) := l_cache_key_size_tbl(l_i) + (l_cnt*l_line_attr_grp_size);
563
564 OPEN non_eq_attr_csr(l_cache_key_val_tbl(l_i));
565 FETCH non_eq_attr_csr INTO l_cnt;
566 CLOSE non_eq_attr_csr;
567 l_cache_key_size_tbl(l_i) := l_cache_key_size_tbl(l_i) + (l_cnt*l_non_eq_attr_size);
568
569 -- julin: debug
570 /*if (l_cache_key_header_tbl(l_i) = 1000 and l_cache_key_val_tbl(l_i) = '474') then
571 dbms_output.put_line('l_line_cnt: ' || l_line_cnt);
572 dbms_output.put_line('l_line_attr_grp_cnt: ' || l_line_attr_grp_cnt);
573 dbms_output.put_line('l_line_attr_grp_cache_key_cnt: ' || l_line_attr_grp_cache_key_cnt);
574 dbms_output.put_line('l_uom_cnt: ' || l_uom_cnt);
575 end if;
576 if (l_cache_key_val_tbl(l_i) = '1000|PRICING_ATTRIBUTE1|149') then
577 dbms_output.put_line('l_cache_key_size_tbl(l_i): ' || l_cache_key_size_tbl(l_i));
578 end if;
579 */
580
581 -- julin: why null?
582 /*if (l_cache_key_val_tbl(l_i) is null or
583 (l_cache_key_attr_tbl(l_i) = 'PRICING_ATTRIBUTE3' and l_cache_key_val_tbl(l_i) = 'ALL')) then
584 l_perm_cache_key_size := l_perm_cache_key_size + l_cache_key_size_tbl(l_i);
585 l_perm_cache_key_cnt := l_perm_cache_key_cnt + 1;
586 else
587 l_total_cache_key_size(ck_type) := l_total_cache_key_size(ck_type) + l_cache_key_size_tbl(l_i);
588 if (l_cache_key_size_tbl(l_i) > l_max_cache_key_size(ck_type)) then
589 l_max_cache_key_size(ck_type) := l_cache_key_size_tbl(l_i);
590 l_max_cache_key_header(ck_type) := l_cache_key_header_tbl(l_i);
591 l_max_cache_key_val(ck_type) := l_cache_key_val_tbl(l_i);
592 end if;
593 end if;
594 */
595
596 elsif (ck_type = L_FORMULA_CACHE_KEY) then
597 OPEN formula_csr(l_cache_key_val_tbl(l_i));
598 FETCH formula_csr INTO l_cnt;
599 CLOSE formula_csr;
600 l_cache_key_size_tbl(l_i) := l_cache_key_size_tbl(l_i) + (l_cnt*l_formula_size);
601
602 OPEN formula_line_csr(l_cache_key_val_tbl(l_i));
603 FETCH formula_line_csr INTO l_cnt;
604 CLOSE formula_line_csr;
605 l_cache_key_size_tbl(l_i) := l_cache_key_size_tbl(l_i) + (l_cnt*l_formula_line_size);
606
607 elsif (ck_type = L_UOM_CACHE_KEY) then
608 OPEN uom_csr(l_cache_key_val_tbl(l_i));
609 FETCH uom_csr INTO l_cnt;
610 CLOSE uom_csr;
611 l_cache_key_size_tbl(l_i) := l_cache_key_size_tbl(l_i) + (l_cnt*l_uom_size);
612
613 OPEN uom_class_csr(l_cache_key_val_tbl(l_i));
614 FETCH uom_class_csr INTO l_cnt;
615 CLOSE uom_class_csr;
616 l_cache_key_size_tbl(l_i) := l_cache_key_size_tbl(l_i) + (l_cnt*l_uom_class_size);
617
618 elsif (ck_type = L_CURRENCY_CACHE_KEY) then
619
620 OPEN currency_csr(l_cache_key_val_tbl(l_i));
621 FETCH currency_csr INTO l_cnt;
622 CLOSE currency_csr;
623 l_cache_key_size_tbl(l_i) := l_cache_key_size_tbl(l_i) + (l_cnt*l_currency_size);
624
625 OPEN currency_line_csr(l_cache_key_val_tbl(l_i));
626 FETCH currency_line_csr INTO l_cnt;
627 CLOSE currency_line_csr;
628 l_cache_key_size_tbl(l_i) := l_cache_key_size_tbl(l_i) + (l_cnt*l_currency_line_size);
629
630 END if;
631
632 l_total_cache_key_size(ck_type) := l_total_cache_key_size(ck_type) + l_cache_key_size_tbl(l_i);
633 if (l_cache_key_size_tbl(l_i) > l_max_cache_key_size(ck_type)) then
634 l_max_cache_key_size(ck_type) := l_cache_key_size_tbl(l_i);
635 l_max_cache_key_val(ck_type) := l_cache_key_val_tbl(l_i);
636 END if;
637
638 l_i := l_i + l_cache_key_increment;
639 END loop; -- cache-keys
640
641 l_avg_cache_key_size(ck_type) := round(l_total_cache_key_size(ck_type)/l_calc_cnt);
642 l_total_cache_key_size(ck_type) := l_avg_cache_key_size(ck_type) * l_cache_key_cnt;
643
644 /* julin: debug
645 IF l_debug = FND_API.G_TRUE THEN
646 dbms_output.put_line('ck_type: ' || ck_type);
647 dbms_output.put_line('l_cache_key_stat_cnt: ' || l_cache_key_stat_cnt);
648 dbms_output.put_line('l_cache_key_increment: ' || l_cache_key_increment);
649 dbms_output.put_line('l_max_cache_key_size(ck_type): ' || l_max_cache_key_size(ck_type));
650 dbms_output.put_line('l_max_cache_key_val(ck_type): ' || l_max_cache_key_val(ck_type));
651 dbms_output.put_line('l_avg_cache_key_size(ck_type): ' || l_avg_cache_key_size(ck_type));
652 END IF;
653 */
654
655 END if;
656
657 END loop; -- ck_type
658
659 /*****************************************************************
660 Calculate permanent full cache statistics
661 - populate l_perm_full_size
662 *****************************************************************/
663
664 l_perm_full_size := 0;
665
666 OPEN pattern_csr;
667 FETCH pattern_csr INTO l_cnt;
668 CLOSE pattern_csr;
669 l_perm_full_size := l_perm_full_size + (l_cnt * l_pattern_size);
670
671 OPEN segment_csr;
672 FETCH segment_csr INTO l_cnt;
673 CLOSE segment_csr;
674 l_perm_full_size := l_perm_full_size + (l_cnt * l_segment_size);
675
676 OPEN request_source_csr;
677 FETCH request_source_csr INTO l_cnt;
678 CLOSE request_source_csr;
679 l_perm_full_size := l_perm_full_size + (l_cnt * l_request_source_size);
680
681 OPEN event_phase_csr;
682 FETCH event_phase_csr INTO l_cnt;
683 CLOSE event_phase_csr;
684 l_perm_full_size := l_perm_full_size + (l_cnt * l_event_phase_size);
685
686 OPEN profile_csr;
687 FETCH profile_csr INTO l_cnt;
688 CLOSE profile_csr;
689 l_perm_full_size := l_perm_full_size + (l_cnt * l_profile_size);
690
691 OPEN cache_do_size_csr;
692 FETCH cache_do_size_csr INTO l_cnt;
693 CLOSE cache_do_size_csr;
694 l_perm_full_size := l_perm_full_size + (l_cnt * l_cache_do_size_size);
695
696 OPEN cache_stat_csr;
697 FETCH cache_stat_csr INTO l_cnt;
698 CLOSE cache_stat_csr;
699 l_perm_full_size := l_perm_full_size + (l_cnt * l_cache_stat_size);
700
701 OPEN header_csr;
702 FETCH header_csr INTO l_cnt;
703 CLOSE header_csr;
704 l_perm_full_size := l_perm_full_size + (l_cnt * l_header_size);
705
706 OPEN second_price_list_csr;
707 FETCH second_price_list_csr INTO l_cnt;
708 CLOSE second_price_list_csr;
709 l_perm_full_size := l_perm_full_size + (l_cnt * l_second_price_list_size);
710
711 OPEN header_attr_csr;
712 FETCH header_attr_csr INTO l_cnt;
713 CLOSE header_attr_csr;
714 l_perm_full_size := l_perm_full_size + (l_cnt * l_header_attr_size);
715
716 OPEN header_ph_csr;
717 FETCH header_ph_csr INTO l_cnt;
718 CLOSE header_ph_csr;
719 l_perm_full_size := l_perm_full_size + (l_cnt * l_header_ph_size);
720
721 OPEN pattern_ph_csr;
722 FETCH pattern_ph_csr INTO l_cnt;
723 CLOSE pattern_ph_csr;
724 l_perm_full_size := l_perm_full_size + (l_cnt * l_pattern_ph_size);
725
726 OPEN okc_uom_csr;
727 FETCH okc_uom_csr INTO l_cnt;
728 CLOSE okc_uom_csr;
729 l_perm_full_size := l_perm_full_size + (l_cnt * l_okc_uom_size);
730
731 OPEN okc_time_unit_csr;
732 FETCH okc_time_unit_csr INTO l_cnt;
733 CLOSE okc_time_unit_csr;
734 l_perm_full_size := l_perm_full_size + (l_cnt * l_okc_time_unit_size);
735
736 /*****************************************************************
737 Calculate permanent partial cache statistics
738 - populate l_perm_full_size
739 *****************************************************************/
740
741 l_perm_partial_size := 0;
742
743 OPEN line_perm_csr;
744 FETCH line_perm_csr INTO l_cnt;
745 CLOSE line_perm_csr;
746 l_perm_partial_size := l_perm_partial_size + (l_cnt * l_line_size);
747
748 OPEN uom_perm_csr;
749 FETCH uom_perm_csr INTO l_cnt;
750 CLOSE uom_perm_csr;
751 l_perm_partial_size := l_perm_partial_size + (l_cnt * l_uom_size);
752
753
754 /*****************************************************************
755 Calculate aggregate statistics
756 - populate l_perm_size
757 - populate l_full_size
758 - populate l_min_size
759 *****************************************************************/
760
761 l_perm_size := l_perm_full_size + l_perm_partial_size;
762
763 l_ondemand_size := l_total_cache_key_size(L_MODIFIER_CACHE_KEY) +
764 l_total_cache_key_size(L_PRICE_CACHE_KEY) +
765 l_total_cache_key_size(L_FORMULA_CACHE_KEY) +
766 l_total_cache_key_size(L_FACTOR_CACHE_KEY) +
767 l_total_cache_key_size(L_UOM_CACHE_KEY) +
768 l_total_cache_key_size(L_CURRENCY_CACHE_KEY);
769
770 l_full_size := l_perm_size + l_ondemand_size;
771 l_min_size := l_perm_size + greatest(l_max_cache_key_size(L_MODIFIER_CACHE_KEY), l_max_cache_key_size(L_PRICE_CACHE_KEY), l_max_cache_key_size(L_FORMULA_CACHE_KEY), l_max_cache_key_size(L_FACTOR_CACHE_KEY));
772
773
774 /*****************************************************************
775 Insert into qp_cache_stats;
776 *****************************************************************/
777
778 l_cache_type_tbl(1) := 'FULL_SIZE';
779 l_cache_size_tbl(1) := l_full_size;
780
781 l_cache_type_tbl(2) := 'MIN_SIZE';
782 l_cache_size_tbl(2) := l_min_size;
783
784 l_cache_type_tbl(3) := 'PERM_SIZE';
785 l_cache_size_tbl(3) := l_perm_size;
786
787 l_cache_type_tbl(4) := 'PERM_FULL_SIZE';
788 l_cache_size_tbl(4) := l_perm_full_size;
789
790 l_cache_type_tbl(5) := 'ON_DEMAND_SIZE';
791 l_cache_size_tbl(5) := l_ondemand_size;
792
793 l_cache_type_tbl(6) := 'CACHE_KEY_MODIFIER_MAX_SIZE';
794 l_cache_size_tbl(6) := l_max_cache_key_size(L_MODIFIER_CACHE_KEY);
795
796 l_cache_type_tbl(7) := 'CACHE_KEY_MODIFIER_AVG_SIZE';
797 l_cache_size_tbl(7) := l_avg_cache_key_size(L_MODIFIER_CACHE_KEY);
798
799 l_cache_type_tbl(8) := 'CACHE_KEY_PRICE_MAX_SIZE';
800 l_cache_size_tbl(8) := l_max_cache_key_size(L_PRICE_CACHE_KEY);
801
802 l_cache_type_tbl(9) := 'CACHE_KEY_PRICE_AVG_SIZE';
803 l_cache_size_tbl(9) := l_avg_cache_key_size(L_PRICE_CACHE_KEY);
804
805 l_cache_type_tbl(10) := 'CACHE_KEY_FORMULA_MAX_SIZE';
806 l_cache_size_tbl(10) := l_max_cache_key_size(L_FORMULA_CACHE_KEY);
807
808 l_cache_type_tbl(11) := 'CACHE_KEY_FORMULA_AVG_SIZE';
809 l_cache_size_tbl(11) := l_avg_cache_key_size(L_FORMULA_CACHE_KEY);
810
811 l_cache_type_tbl(12) := 'CACHE_KEY_FACTOR_MAX_SIZE';
812 l_cache_size_tbl(12) := l_max_cache_key_size(L_FACTOR_CACHE_KEY);
813
814 l_cache_type_tbl(13) := 'CACHE_KEY_FACTOR_AVG_SIZE';
815 l_cache_size_tbl(13) := l_avg_cache_key_size(L_FACTOR_CACHE_KEY);
816
817 l_cache_type_tbl(14) := 'CACHE_KEY_UOM_MAX_SIZE';
818 l_cache_size_tbl(14) := l_max_cache_key_size(L_UOM_CACHE_KEY);
819
820 l_cache_type_tbl(15) := 'CACHE_KEY_UOM_AVG_SIZE';
821 l_cache_size_tbl(15) := l_avg_cache_key_size(L_UOM_CACHE_KEY);
822
823 l_cache_type_tbl(16) := 'CACHE_KEY_CURRENCY_MAX_SIZE';
824 l_cache_size_tbl(16) := l_max_cache_key_size(L_CURRENCY_CACHE_KEY);
825
826 l_cache_type_tbl(17) := 'CACHE_KEY_CURRENCY_AVG_SIZE';
827 l_cache_size_tbl(17) := l_avg_cache_key_size(L_CURRENCY_CACHE_KEY);
828
829 l_cache_type_tbl(18) := 'CACHE_KEY_MODIFIER_TOTAL_SIZE';
830 l_cache_size_tbl(18) := l_total_cache_key_size(L_MODIFIER_CACHE_KEY);
831
832 -- populate WHO columns
833 FOR i IN 1..l_cache_type_tbl.last loop
834 l_creation_date_final_tbl(i) := sysdate;
835 l_created_by_final_tbl(i) := FND_GLOBAL.USER_ID;
836 l_last_update_date_final_tbl(i) := sysdate;
837 l_last_updated_by_final_tbl(i) := FND_GLOBAL.USER_ID;
838 l_last_update_login_final_tbl(i) := FND_GLOBAL.LOGIN_ID;
839 l_program_appl_id_final_tbl(i) := FND_GLOBAL.PROG_APPL_ID;
840 l_program_id_final_tbl(i) := FND_GLOBAL.CONC_PROGRAM_ID;
841 l_program_upd_date_final_tbl(i) := sysdate;
842 l_request_id_final_tbl(i) := FND_GLOBAL.CONC_REQUEST_ID;
843 END LOOP;
844
845 DELETE FROM qp_cache_stats;
846
847 forall i in 1..l_cache_type_tbl.last
848 INSERT INTO qp_cache_stats (
849 name,
850 value,
851 CREATION_DATE,
852 CREATED_BY,
853 LAST_UPDATE_DATE,
854 LAST_UPDATED_BY,
855 LAST_UPDATE_LOGIN,
856 PROGRAM_APPLICATION_ID,
857 PROGRAM_ID,
858 PROGRAM_UPDATE_DATE,
859 REQUEST_ID)
860 VALUES (
861 l_cache_type_tbl(i),
862 l_cache_size_tbl(i),
863 l_creation_date_final_tbl(i),
864 l_created_by_final_tbl(i),
865 l_last_update_date_final_tbl(i),
866 l_last_updated_by_final_tbl(i),
867 l_last_update_login_final_tbl(i),
868 l_program_appl_id_final_tbl(i),
869 l_program_id_final_tbl(i),
870 l_program_upd_date_final_tbl(i),
871 l_request_id_final_tbl(i)
872 );
873 commit;
874
875 EXCEPTION
876 WHEN OTHERS THEN
877 err_buff := sqlerrm;
878 retcode := 2;
879 END UPDATE_CACHE_STATS;
880
881 /*
882 PROCEDURE UPDATE_CAT_NO_PROD_PRICING IS
883
884 CURSOR cat_no_prod_mod_csr IS
885 SELECT DISTINCT 'M', category_id
886 FROM mtl_item_categories ic
887 WHERE NOT EXISTS (SELECT 1
888 FROM qp_pricing_attributes pa, qp_list_lines ll
889 WHERE pa.product_attribute_context = 'ITEM' and
890 pa.product_attribute = 'PRICING_ATTRIBUTE1' and
891 pa.product_attr_value = to_char(ic.inventory_item_id) and
892 ll.list_line_id = pa.list_line_id and
893 ll.list_line_type_code not in ('PLL'));
894
895 CURSOR cat_no_prod_price_csr IS
896 SELECT DISTINCT 'P', category_id
897 FROM mtl_item_categories ic
898 WHERE NOT EXISTS (SELECT 1
899 FROM qp_pricing_attributes pa, qp_list_lines ll
900 WHERE pa.product_attribute_context = 'ITEM' and
901 pa.product_attribute = 'PRICING_ATTRIBUTE1' and
902 pa.product_attr_value = to_char(ic.inventory_item_id) and
903 ll.list_line_id = pa.list_line_id and
904 ll.list_line_type_code in ('PLL'));
905
906 l_cat_no_prod_mod_tbl varchar240_tbl_type;
907 l_cat_no_prod_price_tbl varchar240_tbl_type;
908 l_cat_no_prod_type_tbl varchar240_tbl_type;
909 l_cat_no_prod_cat_tbl number_tbl_type;
910
911 BEGIN
912
913 IF l_debug = FND_API.G_TRUE THEN
914 dbms_output.put_line('qp_java_engine_cache_pub.update_cat_no_prod_pricing');
915 END IF;
916
917 DELETE FROM qp_cache_cat_no_prod_pricing;
918
919 OPEN cat_no_prod_mod_csr;
920 FETCH cat_no_prod_mod_csr bulk collect INTO
921 l_cat_no_prod_type_tbl,
922 l_cat_no_prod_cat_tbl;
923 CLOSE cat_no_prod_mod_csr;
924
925 IF l_debug = FND_API.G_TRUE THEN
926 dbms_output.put_line('l_cat_no_prod_type_tbl: ' || l_cat_no_prod_type_tbl.count);
927 END IF;
928
929 forall i in 1..l_cat_no_prod_type_tbl.last
930 INSERT INTO qp_cache_cat_no_prod_pricing (
931 cache_type,
932 category_id)
933 VALUES (
934 l_cat_no_prod_type_tbl(i),
935 l_cat_no_prod_cat_tbl(i)
936 );
937
938 OPEN cat_no_prod_price_csr;
939 FETCH cat_no_prod_price_csr bulk collect INTO
940 l_cat_no_prod_type_tbl,
941 l_cat_no_prod_cat_tbl;
942 CLOSE cat_no_prod_price_csr;
943
944 IF l_debug = FND_API.G_TRUE THEN
945 dbms_output.put_line('l_cat_no_prod_type_tbl: ' || l_cat_no_prod_type_tbl.count);
946 END IF;
947
948 forall i in 1..l_cat_no_prod_type_tbl.last
949 INSERT INTO qp_cache_cat_no_prod_pricing (
950 cache_type,
951 category_id)
952 VALUES (
953 l_cat_no_prod_type_tbl(i),
954 l_cat_no_prod_cat_tbl(i)
955 );
956
957 commit;
958
959 END UPDATE_CAT_NO_PROD_PRICING;
960 */
961
962 PROCEDURE WARM_UP
963 (
964 err_buff OUT NOCOPY VARCHAR2,
965 retcode OUT NOCOPY NUMBER
966 )
967 is
968 l_routine VARCHAR2(240):='QP_JAVA_ENGINE_CACHE_PVT.WARM_UP';
969 l_output_file VARCHAR2(240);
970 l_debug VARCHAR2(3);
971 l_url_param_string VARCHAR2(240);
972 l_return_status VARCHAR2(240);
973 l_return_status_text VARCHAR2(240);
974 JAVA_ENGINE_NOT_RUNNING_ERROR EXCEPTION;
975 E_ROUTINE_ERRORS EXCEPTION;
976
977 BEGIN
978
979 synchronize(err_buff => err_buff,
980 retcode => retcode,
981 p_list_header_id => -1,
982 p_price_formula_id => -1,
983 p_currency_header_id => -1,
984 p_all_others => 'N',
985 p_full_cache => 'N' );
986
987 IF (retcode = 0) THEN
988 FND_MESSAGE.SET_NAME('QP','QP_JAVA_ENGINE_WARMUP_SUCCESS');
989 err_buff := FND_MESSAGE.GET;
990 END IF;
991
992 EXCEPTION
993 WHEN OTHERS THEN
994 FND_MESSAGE.SET_NAME('QP','QP_JAVA_ENGINE_CACHE_ERROR');
995 err_buff := err_buff || FND_MESSAGE.GET;
996 FND_FILE.PUT_LINE( FND_FILE.LOG, l_routine);
997 retcode := 2;
998 END WARM_UP;
999
1000 PROCEDURE SYNCHRONIZE
1001 (
1002 err_buff OUT NOCOPY VARCHAR2,
1003 retcode OUT NOCOPY NUMBER,
1004 p_list_header_id NUMBER,
1005 p_price_formula_id NUMBER,
1006 p_currency_header_id NUMBER,
1007 p_all_others VARCHAR2,
1008 p_full_cache VARCHAR2
1009 )
1010 IS
1011 L_MAX_STATUS_REQUESTS NUMBER:=240;
1012 L_STATUS_REQUEST_INTERVAL NUMBER:=15; -- seconds
1013 L_TRANSFER_TIMEOUT NUMBER:=3600; -- seconds
1014 l_routine VARCHAR2(240):='[QP_JAVA_ENGINE_CACHE_PVT.SYNCHRONIZE]';
1015 l_output_file VARCHAR2(240);
1016 l_debug VARCHAR2(3);
1017 l_url_param_string VARCHAR2(240);
1018 l_return_status VARCHAR2(240);
1019 l_return_status_text VARCHAR2(2000);
1020 l_status_request_cnt NUMBER;
1021 NO_PARAMS_ERROR EXCEPTION;
1022 E_ROUTINE_ERRORS EXCEPTION;
1023 JAVA_ENGINE_NOT_RUNNING_ERROR EXCEPTION;
1024 MAX_STATUS_REQUESTS_REACHED EXCEPTION;
1025
1026 BEGIN
1027
1028 QP_PREQ_GRP.Set_QP_Debug;
1029 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1030 IF l_debug = FND_API.G_TRUE THEN
1031 l_output_file := OE_DEBUG_PUB.SET_DEBUG_MODE('FILE');
1032 FND_FILE.PUT_LINE( FND_FILE.LOG, 'The output file is : ' || l_output_file );
1033 END IF;
1034
1035 IF (QP_JAVA_ENGINE_UTIL_PUB.JAVA_ENGINE_RUNNING <> 'Y') THEN
1036 RAISE JAVA_ENGINE_NOT_RUNNING_ERROR;
1037 END IF;
1038
1039 IF (p_list_header_id IS NULL AND
1040 p_price_formula_id IS NULL AND
1041 p_currency_header_id IS NULL AND
1042 p_all_others IS NULL AND
1043 p_full_cache IS NULL) THEN
1044 RAISE NO_PARAMS_ERROR;
1045 END IF;
1046
1047 --update_cache_stats;
1048 update_cache_stats(
1049 err_buff,
1050 retcode
1051 );
1052 if (err_buff <> '' or retcode <> 0) then
1053 return;
1054 end if;
1055 --update_cat_no_prod_pricing;
1056
1057 l_url_param_string := 'Action=synchronize'||
1058 qp_java_engine_util_pub.G_HARD_CHAR||'concRequestId='||nvl(FND_GLOBAL.CONC_REQUEST_ID, -1)||
1059 qp_java_engine_util_pub.G_HARD_CHAR||'listHeaderId='||nvl(p_list_header_id, -1)||
1060 qp_java_engine_util_pub.G_HARD_CHAR||'priceFormulaId='||nvl(p_price_formula_id, -1)||
1061 qp_java_engine_util_pub.G_HARD_CHAR||'currencyHeaderId='||nvl(p_currency_header_id, -1)||
1062 qp_java_engine_util_pub.G_HARD_CHAR||'allOthers='||nvl(p_all_others, 'N')||
1063 qp_java_engine_util_pub.G_HARD_CHAR||'fullCache='||nvl(p_full_cache, 'N');
1064 qp_java_engine_util_pub.send_java_engine_request(l_url_param_string,
1065 l_return_status,
1066 l_return_status_text,
1067 L_TRANSFER_TIMEOUT,
1068 FND_API.G_TRUE);
1069
1070 l_status_request_cnt := 0;
1071 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1072 IF (l_return_status_text = 'UTL_TCP.END_OF_INPUT') THEN
1073 LOOP
1074 DBMS_LOCK.SLEEP(L_STATUS_REQUEST_INTERVAL);
1075 l_url_param_string := 'Action=synchronize'||
1076 qp_java_engine_util_pub.G_HARD_CHAR||'concRequestId='||nvl(FND_GLOBAL.CONC_REQUEST_ID, -1)||
1077 qp_java_engine_util_pub.G_HARD_CHAR||'statusRequest=Y';
1078 qp_java_engine_util_pub.send_java_engine_request(l_url_param_string,
1079 l_return_status,
1080 l_return_status_text);
1081 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1082 RAISE E_ROUTINE_ERRORS;
1083 END IF;
1084 EXIT WHEN l_return_status_text = 'COMPLETED';
1085 IF l_status_request_cnt > L_MAX_STATUS_REQUESTS THEN
1086 RAISE MAX_STATUS_REQUESTS_REACHED;
1087 END IF;
1088 l_status_request_cnt := l_status_request_cnt + 1;
1089 END LOOP;
1090 ELSE
1091 RAISE E_ROUTINE_ERRORS;
1092 END IF;
1093 END IF;
1094
1095 FND_MESSAGE.SET_NAME('QP','QP_JAVA_ENGINE_SYNC_SUCCESS');
1096 err_buff := FND_MESSAGE.GET;
1097 retcode := 0;
1098
1099 EXCEPTION
1100 WHEN JAVA_ENGINE_NOT_RUNNING_ERROR THEN
1101 FND_MESSAGE.SET_NAME('QP','QP_JAVA_ENGINE_NOT_RUNNING');
1102 err_buff := FND_MESSAGE.GET;
1103 retcode := 2;
1104 WHEN NO_PARAMS_ERROR THEN
1105 FND_MESSAGE.SET_NAME('QP','QP_JAVA_ENGINE_SYNC_PARAM_REQD');
1106 err_buff := FND_MESSAGE.GET;
1107 retcode := 2;
1108 WHEN E_ROUTINE_ERRORS THEN
1109 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
1110 QP_PREQ_GRP.engine_debug(l_routine||'l_return_status_text:'||l_return_status_text);
1111 QP_PREQ_GRP.engine_debug(l_routine||'SQLERRM:'||SQLERRM);
1112 END IF;
1113 FND_MESSAGE.SET_NAME('QP','QP_JAVA_ENGINE_CACHE_ERROR');
1114 err_buff := '(' || l_return_status || ') ';
1115 err_buff := err_buff || FND_MESSAGE.GET;
1116 FND_FILE.PUT_LINE( FND_FILE.LOG, l_return_status_text );
1117 retcode := 2;
1118 WHEN MAX_STATUS_REQUESTS_REACHED THEN
1119 err_buff := 'Request has exceeded '||(L_MAX_STATUS_REQUESTS*L_STATUS_REQUEST_INTERVAL)||' seconds.';
1120 retcode := 1;
1121 WHEN OTHERS THEN
1122 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
1123 QP_PREQ_GRP.engine_debug(l_routine||'l_return_status_text:'||l_return_status_text);
1124 QP_PREQ_GRP.engine_debug(l_routine||'SQLERRM:'||SQLERRM);
1125 END IF;
1126 FND_MESSAGE.SET_NAME('QP','QP_JAVA_ENGINE_CACHE_ERROR');
1127 err_buff := '(' || l_return_status || ') ';
1128 err_buff := err_buff || FND_MESSAGE.GET;
1129 FND_FILE.PUT_LINE( FND_FILE.LOG, l_return_status_text );
1130 retcode := 2;
1131 END SYNCHRONIZE;
1132
1133 PROCEDURE MANAGE
1134 (
1135 err_buff OUT NOCOPY VARCHAR2,
1136 retcode OUT NOCOPY NUMBER,
1137 p_manage_action VARCHAR2,
1138 p_dump_type VARCHAR2,
1139 p_dump_input1 VARCHAR2,
1140 p_dump_input2 VARCHAR2,
1141 p_dump_input3 VARCHAR2
1142 )
1143 IS
1144 l_routine VARCHAR2(240):='QP_JAVA_ENGINE_CACHE_PVT.MANAGE';
1145 l_output_file VARCHAR2(240);
1146 l_debug VARCHAR2(3);
1147 l_url_param_string VARCHAR2(240);
1148 l_return_status VARCHAR2(240);
1149 l_return_status_text VARCHAR2(2000);
1150 l_cr VARCHAR2(1);
1151 JAVA_ENGINE_NOT_RUNNING_ERROR EXCEPTION;
1152 E_ROUTINE_ERRORS EXCEPTION;
1153 l_return_details UTL_HTTP.HTML_PIECES;
1154 BEGIN
1155
1156 retcode := 0;
1157
1158 IF (p_manage_action = 'WARM_UP') THEN
1159 synchronize(err_buff => err_buff,
1160 retcode => retcode,
1161 p_list_header_id => -1,
1162 p_price_formula_id => -1,
1163 p_currency_header_id => -1,
1164 p_all_others => 'N',
1165 p_full_cache => 'N' );
1166 ELSE
1167 QP_PREQ_GRP.Set_QP_Debug;
1168 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1169 IF l_debug = FND_API.G_TRUE THEN
1170 l_output_file := OE_DEBUG_PUB.SET_DEBUG_MODE('FILE');
1171 FND_FILE.PUT_LINE( FND_FILE.LOG, 'The output file is : ' || l_output_file );
1172 END IF;
1173
1174 IF (QP_JAVA_ENGINE_UTIL_PUB.JAVA_ENGINE_RUNNING <> 'Y') THEN
1175 RAISE JAVA_ENGINE_NOT_RUNNING_ERROR;
1176 END IF;
1177
1178 l_url_param_string := 'Action=manage'||
1179 qp_java_engine_util_pub.G_HARD_CHAR||'manageAction='||nvl(p_manage_action, '-1')||
1180 qp_java_engine_util_pub.G_HARD_CHAR||'dumpType='||nvl(p_dump_type, '-1')||
1181 qp_java_engine_util_pub.G_HARD_CHAR||'dumpInput1='||nvl(p_dump_input1, '-1')||
1182 qp_java_engine_util_pub.G_HARD_CHAR||'dumpInput2='||nvl(p_dump_input2, '-1')||
1183 qp_java_engine_util_pub.G_HARD_CHAR||'dumpInput3='||nvl(p_dump_input3, '-1');
1184 qp_java_engine_util_pub.send_java_engine_request(l_url_param_string,
1185 l_return_status,
1186 l_return_status_text,
1187 l_return_details,
1188 TRUE);
1189
1190 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1191 RAISE E_ROUTINE_ERRORS;
1192 END IF;
1193
1194 FND_FILE.NEW_LINE( FND_FILE.LOG );
1195 FOR i IN 1 .. l_return_details.count loop
1196 FND_FILE.PUT( FND_FILE.LOG, l_return_details(i) );
1197 END LOOP;
1198 FND_FILE.NEW_LINE( FND_FILE.LOG );
1199
1200 END IF;
1201
1202 IF (retcode = 0) THEN
1203 FND_MESSAGE.SET_NAME('QP','QP_JAVA_ENGINE_MANAGE_SUCCESS');
1204 err_buff := FND_MESSAGE.GET;
1205 retcode := 0;
1206 END IF;
1207
1208 EXCEPTION
1209 WHEN JAVA_ENGINE_NOT_RUNNING_ERROR THEN
1210 FND_MESSAGE.SET_NAME('QP','QP_JAVA_ENGINE_NOT_RUNNING');
1211 err_buff := FND_MESSAGE.GET;
1212 retcode := 2;
1213 WHEN E_ROUTINE_ERRORS THEN
1214 FND_MESSAGE.SET_NAME('QP','QP_JAVA_ENGINE_CACHE_ERROR');
1215 err_buff := '(' || l_return_status || ') ';
1216 err_buff := err_buff || FND_MESSAGE.GET;
1217 --err_buff := err_buff || ' ' || l_return_status_text;
1218 FND_FILE.PUT_LINE( FND_FILE.LOG, l_return_status_text );
1219 retcode := 2;
1220 WHEN OTHERS THEN
1221 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
1222 QP_PREQ_GRP.engine_debug(l_routine||' '||l_return_status_text);
1223 END IF;
1224 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM);
1225 FND_MESSAGE.SET_NAME('QP','QP_JAVA_ENGINE_CACHE_ERROR');
1226 err_buff := '(' || l_return_status || ') ';
1227 err_buff := err_buff || FND_MESSAGE.GET;
1228 --err_buff := err_buff || ' ' || l_return_status_text;
1229 FND_FILE.PUT_LINE( FND_FILE.LOG, l_return_status_text );
1230 retcode := 2;
1231 END MANAGE;
1232
1233 END QP_JAVA_ENGINE_CACHE_PVT;
1234