[Home] [Help]
PACKAGE BODY: APPS.BEN_CACHE
Source
1 package body ben_cache as
2 /* $Header: bencache.pkb 115.11 2002/12/24 15:43:59 bmanyam ship $ */
3 -- ---------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 History
12 Date Who Version What?
13 -- -- --- ------- -----
14 19 Nov 98 mhoyes 115.0 created
15 10 Nov 98 mhoyes 115.1 Split into separate packages
16 09 Mar 99 G Perry 115.2 IS to AS.
17 06 Jul 99 mhoyes 115.3 Added cache write routines.
18 08 Jul 99 mhoyes 115.4 - Modified p_effective_date datatype
19 from varchar2 to date on
20 Write_BGP_Cache.
21 - Added debug messages in
22 Write_MastDet_Cache to be used
23 for debugging the cache.
24 01 Sep 99 mhoyes 115.5 - Modified Write_BGP_Cache to build
25 an in rather than an exists within
26 the lookup sub-query.
27 - Added lookup subquery hint
28 parameter.
29 - Fixed hashing problems.
30 - Added temporary debug messages.
31 04 Oct 99 mhoyes 115.6 - Added context cache to avoid all
32 selected instance query values
33 being assigned to the instance
34 cache.
35 17 May 99 mhoyes 115.7 - Added p_lkup_query to Write_BGP_Cache.
36 - PLSQL tuning. Removed g_package.
37 22 May 00 mhoyes 115.8 - Added parameter p_lkup_query
38 to Write_BGP_Cache.
39 28 Jun 00 mhoyes 115.8 - Upgraded for multiple value hashing.
40 13 Jul 00 mhoyes 115.9 - Upgraded for non mandatory hash values.
41 19 Sep 01 mhoyes 115.10 - Added check_list_duplicate.
42 24-Dec-02 bmanyam 115.4 NOCOPY Changes
43 */
44 -- ------------------------------------------------------------------------------
45 --
46 -- Declare globals
47 --
48 procedure Write_MastDet_Cache
49 (p_mastercol_name in varchar2
50 ,p_detailcol_name in varchar2
51 ,p_masterfkcol_name in varchar2 default null
52 ,p_masterfk1col_name in varchar2 default null
53 ,p_masterfk2col_name in varchar2 default null
54 ,p_masterfk3col_name in varchar2 default null
55 ,p_masterfk4col_name in varchar2 default null
56 ,p_masterfk5col_name in varchar2 default null
57 ,p_lkup_name in varchar2
58 ,p_inst_name in varchar2
59 ,p_lkup_query in varchar2
60 ,p_inst_query in varchar2
61 ,p_nonmand_hv in boolean default false
62 ,p_coninst_query in varchar2 default null
63 ,p_conlkup_name in varchar2 default null
64 ,p_dtconlkup_ccolnm in varchar2 default null
65 ,p_dtconlkup_value in date default null
66 ,p_instcolnm_set in ben_cache.InstColNmType
67 default ben_cache.g_instcolnm_set
68 ,p_curparm_set in ben_cache.CurParmType
69 default ben_cache.g_curparm_set
70 )
71
72 is
73 --
74 l_proc varchar2(72) := 'Write_MastDet_Cache';
75 --
76 l_errcol_num number;
77 l_v2errcol_num long;
78 l_codeerrreg_str long;
79 --
80 l_asgcolval_num pls_integer;
81 l_curparm_num pls_integer;
82 --
83 l_plsql_str long;
84 l_asgcolval_str long;
85 l_colnum_errstr long;
86 l_lkpcurpmint_str long;
87 l_lkpcurpmcall_str long;
88 l_coninstcurpmint_str long;
89 l_coninstcurpmcall_str long;
90 l_instcurpmint_str long;
91 l_instcurpmcall_str long;
92 l_lkcacpkcmp_str long;
93 l_lkup_curdecstr long;
94 l_coninst_curdecstr long;
95 l_inst_curdecstr long;
96 l_lkup_loopstr long;
97 l_coninst_loopstr long;
98 l_inst_loopstr long;
99 l_err_colnum number;
100 --
101 l_todate_str varchar2(200);
102 l_dclvtodate_str varchar2(1000);
103 l_dclv_vardecstr varchar2(1000);
104 l_dclv_varassstr varchar2(1000);
105 l_lkcacass_str varchar2(1000);
106 l_sqlerrm long;
107 l_conhv pls_integer;
108 l_curparmval_str varchar2(1000);
109 l_coninst_curname varchar2(1000);
110 l_coninst_curref varchar2(1000);
111 l_lkuphv_str long;
112 l_insthv_str long;
113 --
114 begin
115 -- hr_utility.set_location (l_proc||' Entering ',10);
116 --
117 -- Set the context instance cursor name
118 --
119 if p_coninst_query is not null then
120 --
121 l_coninst_curname := ' c_coninst';
122 l_coninst_curref := ' objconinst';
123 --
124 else
125 --
126 l_coninst_curname := ' c_instance';
127 l_coninst_curref := ' objinst';
128 --
129 end if;
130 --
131 -- Build the parameterised cursor
132 --
133 if p_curparm_set.count > 0 then
134 --
135 l_lkpcurpmint_str := '(';
136 l_lkpcurpmcall_str := '(';
137 l_coninstcurpmint_str := '(';
138 l_coninstcurpmcall_str := '(';
139 l_instcurpmint_str := '(';
140 l_instcurpmcall_str := '(';
141 --
142 -- hr_utility.set_location (l_proc||' cursor parm loop ',20);
143 --
144 for l_curparm_num in p_curparm_set.first .. p_curparm_set.last loop
145 --
146 -- Build lookup cursor parameter interface string
147 --
148 if l_curparm_num = 0 then
149 --
150 l_lkpcurpmint_str := l_lkpcurpmint_str||p_curparm_set(l_curparm_num).name
151 ||' '||p_curparm_set(l_curparm_num).datatype;
152 --
153 else
154 --
155 l_lkpcurpmint_str := l_lkpcurpmint_str||','||p_curparm_set(l_curparm_num).name
156 ||' '||p_curparm_set(l_curparm_num).datatype;
157 --
158 end if;
159 --
160 -- Build lookup cursor call string
161 --
162 if l_curparm_num > 0 then
163 --
164 l_lkpcurpmcall_str := l_lkpcurpmcall_str||',';
165 --
166 end if;
167 --
168 if p_curparm_set(l_curparm_num).datatype = 'DATE' then
169 --
170 l_todate_str := ' to_date('||''''
171 ||fnd_date.date_to_canonical(p_curparm_set(l_curparm_num).dateval)
172 ||''''||', '||''''||fnd_date.canonical_DT_mask||''''||') ';
173 --
174 l_lkpcurpmcall_str := l_lkpcurpmcall_str||l_todate_str;
175 --
176 elsif p_curparm_set(l_curparm_num).datatype = 'NUMBER' then
177 --
178 -- Check for a null cursor parameter value
179 --
180 if p_curparm_set(l_curparm_num).numval is not null then
181 --
182 l_curparmval_str := p_curparm_set(l_curparm_num).numval;
183 --
184 else
185 --
186 l_curparmval_str := ' null ';
187 --
188 end if;
189 --
190 l_lkpcurpmcall_str := l_lkpcurpmcall_str||' '
191 ||l_curparmval_str;
192 --
193 elsif p_curparm_set(l_curparm_num).datatype = 'VARCHAR2' then
194 --
195 l_lkpcurpmcall_str := l_lkpcurpmcall_str||' '
196 ||''''||p_curparm_set(l_curparm_num).v2val||'''';
197 --
198 end if;
199 --
200 -- Build context instance cursor parameter interface string
201 --
202 if l_curparm_num = 0 then
203 --
204 l_coninstcurpmint_str := l_coninstcurpmint_str
205 ||p_curparm_set(l_curparm_num).name
206 ||' '||p_curparm_set(l_curparm_num).datatype;
207 --
208 else
209 --
210 l_coninstcurpmint_str := l_coninstcurpmint_str
211 ||','||p_curparm_set(l_curparm_num).name
212 ||' '||p_curparm_set(l_curparm_num).datatype;
213 --
214 end if;
215 --
216 -- Build instance cursor call string
217 --
218 if l_curparm_num > 0 then
219 --
220 l_coninstcurpmcall_str := l_coninstcurpmcall_str||',';
221 --
222 end if;
223 --
224 if p_curparm_set(l_curparm_num).datatype = 'DATE' then
225 --
226 l_todate_str := ' to_date('||''''
227 ||fnd_date.date_to_canonical(p_curparm_set(l_curparm_num).dateval)
228 ||''''||', '||''''||fnd_date.canonical_DT_mask||''''||') ';
229 --
230 l_coninstcurpmcall_str := l_coninstcurpmcall_str||l_todate_str;
231 --
232 elsif p_curparm_set(l_curparm_num).datatype = 'NUMBER' then
233 --
234 -- Check for a null cursor parameter value
235 --
236 if p_curparm_set(l_curparm_num).numval is not null then
237 --
238 l_curparmval_str := p_curparm_set(l_curparm_num).numval;
239 --
240 else
241 --
242 l_curparmval_str := ' null ';
243 --
244 end if;
245 --
246 l_coninstcurpmcall_str := l_coninstcurpmcall_str
247 ||' '||l_curparmval_str;
248 --
249 elsif p_curparm_set(l_curparm_num).datatype = 'VARCHAR2' then
250 --
251 l_coninstcurpmcall_str := l_coninstcurpmcall_str
252 ||' '||''''||p_curparm_set(l_curparm_num).v2val||'''';
253 --
254 end if;
255 --
256 -- Build instance cursor parameter interface string
257 --
258 if l_curparm_num = 0 then
259 --
260 l_instcurpmint_str := l_instcurpmint_str
261 ||p_curparm_set(l_curparm_num).name
262 ||' '||p_curparm_set(l_curparm_num).datatype;
263 --
264 else
265 --
266 l_instcurpmint_str := l_instcurpmint_str
267 ||','||p_curparm_set(l_curparm_num).name
268 ||' '||p_curparm_set(l_curparm_num).datatype;
269 --
270 end if;
271 --
272 -- Build instance cursor call string
273 --
274 if l_curparm_num > 0 then
275 --
276 l_instcurpmcall_str := l_instcurpmcall_str||',';
277 --
278 end if;
279 --
280 if p_curparm_set(l_curparm_num).datatype = 'DATE' then
281 --
282 l_todate_str := ' to_date('||''''
283 ||fnd_date.date_to_canonical(p_curparm_set(l_curparm_num).dateval)
284 ||''''||', '||''''||fnd_date.canonical_DT_mask||''''||') ';
285 --
286 l_instcurpmcall_str := l_instcurpmcall_str||l_todate_str;
287 --
288 elsif p_curparm_set(l_curparm_num).datatype = 'NUMBER' then
289 --
290 -- Check for a null cursor parameter value
291 --
292 if p_curparm_set(l_curparm_num).numval is not null then
293 --
294 l_curparmval_str := p_curparm_set(l_curparm_num).numval;
295 --
296 else
297 --
298 l_curparmval_str := ' null ';
299 --
300 end if;
301 --
302 l_instcurpmcall_str := l_instcurpmcall_str
303 ||' '||l_curparmval_str;
304 --
305 elsif p_curparm_set(l_curparm_num).datatype = 'VARCHAR2' then
306 --
307 l_instcurpmcall_str := l_instcurpmcall_str
308 ||' '||''''||p_curparm_set(l_curparm_num).v2val||'''';
309 --
310 end if;
311 --
312 end loop;
313 -- hr_utility.set_location (l_proc||' Dn cursor parm loop ',20);
314 --
315 l_lkpcurpmint_str := l_lkpcurpmint_str||') ';
316 l_lkpcurpmcall_str := l_lkpcurpmcall_str||') ';
317 l_coninstcurpmint_str := l_coninstcurpmint_str||')';
318 l_coninstcurpmcall_str := l_coninstcurpmcall_str||') ';
319 l_instcurpmint_str := l_instcurpmint_str||')';
320 l_instcurpmcall_str := l_instcurpmcall_str||') ';
321 --
322 else
323 --
324 l_lkpcurpmint_str := ' ';
325 l_lkpcurpmcall_str := ' ';
326 l_coninstcurpmint_str := ' ';
327 l_coninstcurpmcall_str := ' ';
328 l_instcurpmint_str := ' ';
329 l_instcurpmcall_str := ' ';
330 --
331 end if;
332 -- hr_utility.set_location (l_proc||' Dn parm cursor ',20);
333 --
334 -- Build the instance column value assignment string
335 --
336 if p_instcolnm_set.count > 0 then
337 --
338 -- Assign specified column values in instance cache
339 --
340 l_asgcolval_str := null;
341 --
342 for l_asgcolval_num in p_instcolnm_set.first .. p_instcolnm_set.last loop
343 --
344 -- Check for alternative assignment column name
345 --
346 if p_instcolnm_set(l_asgcolval_num).asscol_name is not null then
347 --
348 l_asgcolval_str := l_asgcolval_str||' '||p_inst_name||'(l_torrwnum).'
349 ||p_instcolnm_set(l_asgcolval_num).caccol_name
350 ||' := objinst.'
351 ||p_instcolnm_set(l_asgcolval_num).asscol_name||'; ';
352 --
353 else
354 --
355 l_asgcolval_str := l_asgcolval_str||' '||p_inst_name||'(l_torrwnum).'
359 --
356 ||p_instcolnm_set(l_asgcolval_num).caccol_name
357 ||' := objinst.'
358 ||p_instcolnm_set(l_asgcolval_num).col_name||'; ';
360 end if;
361 --
362 end loop;
363 --
364 else
365 --
366 -- Assign all column values in instance cache
367 --
368 -- Check for a context instance cache
369 --
370 if p_coninst_query is not null then
371 --
372 -- Assign value to the instance cursor
373 --
374 l_asgcolval_str := ' fetch c_instance into '||p_inst_name||'(l_torrwnum); ';
375 --
376 else
377 --
378 l_asgcolval_str := ' '||p_inst_name||'(l_torrwnum) := objinst; ';
379 --
380 end if;
381 --
382 end if;
383 -- hr_utility.set_location (l_proc||' Dn instance assignment string ',30);
384 --
385 -- Build the date context lookup value str
386 --
387 if p_conlkup_name is not null then
388 --
389 -- Check context lookup details are set
390 --
391 if p_dtconlkup_value is null then
392 --
393 fnd_message.set_name('BEN','BEN_?????_CONDATELKVALNULL');
394 fnd_message.raise_error;
395 --
396 end if;
397 --
398 if p_dtconlkup_ccolnm is null then
399 --
400 fnd_message.set_name('BEN','BEN_?????_CONDATELKCCNULL');
401 fnd_message.raise_error;
402 --
403 end if;
404 --
405 -- Hash the date
406 --
407 l_conhv := p_dtconlkup_value-hr_api.g_sot;
408 --
409 -- Build the date context value conversion string
410 --
411 l_dclvtodate_str := ' to_date('||''''
412 ||fnd_date.date_to_canonical(p_dtconlkup_value)
413 ||''''||', '||''''||fnd_date.canonical_DT_mask||''''||') ';
414 --
415 -- Build the date context value declaration string
416 --
417 l_dclv_vardecstr := ' l_dtconlkup_value date; ';
418 --
419 -- Build the date context lookup value assignment string
420 --
421 l_dclv_varassstr := ' '||p_conlkup_name||'('||l_conhv||').'||p_dtconlkup_ccolnm
422 ||' := '||l_dclvtodate_str||'; ';
423 --
424 -- Build the lookup cache assignment string
425 --
426 l_lkcacass_str := ' '||p_lkup_name||'(l_hv).'||p_dtconlkup_ccolnm
427 ||' := '||l_dclvtodate_str||';';
428 --
429 -- Build the lookup cache primary key comparison string
430 --
431 l_lkcacpkcmp_str := ' if '||p_lkup_name||'(l_hv).id <> '||l_coninst_curref||'.'||p_detailcol_name
432 ||' or ('||p_lkup_name||'(l_hv).id = '||l_coninst_curref||'.'||p_detailcol_name
433 ||' and '||p_lkup_name||'(l_hv).datevalue_1 <> '||l_dclvtodate_str
434 ||' ) '
435 ||' then ';
436 --
437 else
438 --
439 l_dclvtodate_str := ' ';
440 l_dclv_vardecstr := ' ';
441 l_dclv_varassstr := ' ';
442 l_lkcacass_str := ' ';
443 l_lkcacpkcmp_str := ' if '||p_lkup_name||'(l_hv).id <> '||l_coninst_curref
444 ||'.'||p_detailcol_name;
445 --
446 -- Check if the fk col names are set
447 --
448 if p_masterfkcol_name is not null then
449 --
450 l_lkcacpkcmp_str := l_lkcacpkcmp_str||' or nvl('||p_lkup_name||'(l_hv).fk_id,-1) <> nvl('||l_coninst_curref
451 ||'.'||p_masterfkcol_name||',-1) ';
452 --
453 end if;
454 --
455 if p_masterfk1col_name is not null then
456 --
457 l_lkcacpkcmp_str := l_lkcacpkcmp_str||' or nvl('||p_lkup_name||'(l_hv).fk1_id,-1) <> nvl('||l_coninst_curref
458 ||'.'||p_masterfk1col_name||',-1) ';
459 --
460 end if;
461 --
462 if p_masterfk2col_name is not null then
463 --
464 l_lkcacpkcmp_str := l_lkcacpkcmp_str||' or nvl('||p_lkup_name||'(l_hv).fk2_id,-1) <> nvl('||l_coninst_curref
465 ||'.'||p_masterfk2col_name||',-1) ';
466 --
467 end if;
468 --
469 if p_masterfk3col_name is not null then
470 --
471 l_lkcacpkcmp_str := l_lkcacpkcmp_str||' or nvl('||p_lkup_name||'(l_hv).fk3_id,-1) <> nvl('||l_coninst_curref
472 ||'.'||p_masterfk3col_name||',-1) ';
473 --
474 end if;
475 --
476 if p_masterfk4col_name is not null then
477 --
478 l_lkcacpkcmp_str := l_lkcacpkcmp_str||' or nvl('||p_lkup_name||'(l_hv).fk4_id,-1) <> nvl('||l_coninst_curref
479 ||'.'||p_masterfk4col_name||',-1) ';
480 --
481 end if;
482 --
483 if p_masterfk5col_name is not null then
484 --
485 l_lkcacpkcmp_str := l_lkcacpkcmp_str||' or nvl('||p_lkup_name||'(l_hv).fk5_id,-1) <> nvl('||l_coninst_curref
486 ||'.'||p_masterfk5col_name||',-1) ';
487 --
488 end if;
489 --
490 l_lkcacpkcmp_str := l_lkcacpkcmp_str||' then ';
491 --
492 end if;
493 --
494 -- Check for foreign key lookup values
495 --
496 if p_masterfkcol_name is not null then
497 --
498 l_lkcacass_str := l_lkcacass_str||' '
502 --
499 ||p_lkup_name||'(l_hv).fk_id := objlook.'||p_masterfkcol_name||'; ';
500 --
501 end if;
503 if p_masterfk1col_name is not null then
504 --
505 l_lkcacass_str := l_lkcacass_str||' '
506 ||p_lkup_name||'(l_hv).fk1_id := objlook.'||p_masterfk1col_name||'; ';
507 --
508 end if;
509 --
510 if p_masterfk2col_name is not null then
511 --
512 l_lkcacass_str := l_lkcacass_str||' '
513 ||p_lkup_name||'(l_hv).fk2_id := objlook.'||p_masterfk2col_name||'; ';
514 --
515 end if;
516 --
517 if p_masterfk3col_name is not null then
518 --
519 l_lkcacass_str := l_lkcacass_str||' '
520 ||p_lkup_name||'(l_hv).fk3_id := objlook.'||p_masterfk3col_name||'; ';
521 --
522 end if;
523 --
524 if p_masterfk4col_name is not null then
525 --
526 l_lkcacass_str := l_lkcacass_str||' '
527 ||p_lkup_name||'(l_hv).fk4_id := objlook.'||p_masterfk4col_name||'; ';
528 --
529 end if;
530 --
531 if p_masterfk5col_name is not null then
532 --
533 l_lkcacass_str := l_lkcacass_str||' '
534 ||p_lkup_name||'(l_hv).fk5_id := objlook.'||p_masterfk5col_name||'; ';
535 --
536 end if;
537 --
538 -- hr_utility.set_location (l_proc||' Start PLSQL block ',30);
539 --
540 -- Build cursor declaration strings
541 --
542 l_lkup_curdecstr := ' cursor c_lookup '
543 ||l_lkpcurpmint_str||' '
544 ||' is '
545 ||p_lkup_query||' ';
546 --
547 l_inst_curdecstr := ' cursor c_instance '
548 ||l_instcurpmint_str||' '
549 ||' is '
550 ||p_inst_query||' ';
551 --
552 if p_coninst_query is not null then
553 --
554 l_coninst_curdecstr := ' cursor c_coninst '
555 ||l_coninstcurpmint_str||' '
556 ||' is '
557 ||p_coninst_query||' ';
558 --
559 else
560 --
561 l_coninst_curdecstr := ' ';
562 --
563 end if;
564 --
565 -- Build hash value strings
566 --
567 l_lkuphv_str := 'objlook.'||p_mastercol_name;
568 --
569 if p_masterfkcol_name is not null then
570 --
571 l_lkuphv_str := 'nvl('||l_lkuphv_str||',1)+nvl(objlook.'||p_masterfkcol_name||',1) ';
572 --
573 end if;
574 --
575 if p_masterfk1col_name is not null then
576 --
577 l_lkuphv_str := l_lkuphv_str||'+nvl(objlook.'||p_masterfk1col_name||',1) ';
578 --
579 end if;
580 --
581 if p_masterfk2col_name is not null then
582 --
583 l_lkuphv_str := l_lkuphv_str||'+nvl(objlook.'||p_masterfk2col_name||',1) ';
584 --
585 end if;
586 --
587 if p_masterfk3col_name is not null then
588 --
589 l_lkuphv_str := l_lkuphv_str||'+nvl(objlook.'||p_masterfk3col_name||',1) ';
590 --
591 end if;
592 --
593 if p_masterfk4col_name is not null then
594 --
595 l_lkuphv_str := l_lkuphv_str||'+nvl(objlook.'||p_masterfk4col_name||',1) ';
596 --
597 end if;
598 --
599 if p_masterfk5col_name is not null then
600 --
601 l_lkuphv_str := l_lkuphv_str||'+nvl(objlook.'||p_masterfk5col_name||',1) ';
602 --
603 end if;
604 --
605 l_lkuphv_str := ' l_hv := mod('||l_lkuphv_str||',ben_hash_utility.get_hash_key); ';
606 --
607 -- Instance
608 --
609 l_insthv_str := 'objinst.'||p_detailcol_name;
610 --
611 if p_masterfkcol_name is not null then
612 --
613 l_insthv_str := 'nvl('||l_insthv_str||',1)+nvl(objinst.'||p_masterfkcol_name||',1) ';
614 --
615 end if;
616 --
617 if p_masterfk1col_name is not null then
618 --
619 l_insthv_str := l_insthv_str||'+nvl(objinst.'||p_masterfk1col_name||',1) ';
620 --
621 end if;
622 --
623 if p_masterfk2col_name is not null then
624 --
625 l_insthv_str := l_insthv_str||'+nvl(objinst.'||p_masterfk2col_name||',1) ';
626 --
627 end if;
628 --
629 if p_masterfk3col_name is not null then
630 --
631 l_insthv_str := l_insthv_str||'+nvl(objinst.'||p_masterfk3col_name||',1) ';
632 --
633 end if;
634 --
635 if p_masterfk4col_name is not null then
636 --
637 l_insthv_str := l_insthv_str||'+nvl(objinst.'||p_masterfk4col_name||',1) ';
638 --
639 end if;
640 --
641 if p_masterfk5col_name is not null then
642 --
643 l_insthv_str := l_insthv_str||'+nvl(objinst.'||p_masterfk5col_name||',1) ';
644 --
645 end if;
646 --
647 l_insthv_str := ' l_hv := mod('||l_insthv_str||',ben_hash_utility.get_hash_key); ';
648 --
649 -- Build loop strings
650 --
651 -- Build lookup loop string
652 --
653 l_lkup_loopstr := ' for objlook in c_lookup '
654 ||l_lkpcurpmcall_str||' '
655 ||' loop '
656 /*
657 --
658 -- Temporary: Debugging statements
659 --
663 ||' if '||p_lkup_name||'.exists(l_hv) then'
660 ||' hr_utility.set_location('||''''||'Lookup loop '||''''||',10); '
661 */
662 ||l_lkuphv_str
664 ||' l_not_hash_found := false;'
665 ||' while not l_not_hash_found loop'
666 ||' l_hv := ben_hash_utility.get_next_hash_index(p_hash_index => l_hv);'
667 ||' if not '||p_lkup_name||'.exists(l_hv) then'
668 ||' l_not_hash_found := true;'
669 ||' exit;'
670 ||' else'
671 ||' l_not_hash_found := false;'
672 ||' end if;'
673 ||' end loop;'
674 ||' end if;'
675 ||' '||p_lkup_name||'(l_hv).id := objlook.'||p_mastercol_name||'; '
676 ||l_lkcacass_str||' '
677 /*
678 --
679 -- Temporary: Debugging statements
680 --
681 ||' hr_utility.set_location('||''''||'objlook.'||p_mastercol_name||' '||''''
682 ||'||objlook.'||p_mastercol_name||',10); '
683 */
684 ||' end loop; ';
685
686 --
687 -- Build instance loop string
688 --
689 if p_coninst_query is not null then
690 --
691 l_inst_loopstr := ' l_torrwnum := 0; '
692 ||' l_prev_hv := -1; '
693 ||' if '||p_lkup_name||'.count > 0 then '
694 ||' open c_instance '||l_instcurpmcall_str||'; '
695 ||' for objconinst in c_coninst '
696 ||l_coninstcurpmcall_str||' '
697 ||' loop '
698 /*
699 --
700 -- Temporary: Debugging statements
701 --
702 ||' hr_utility.set_location('||''''||'Context Instance loop '||''''||',10); '
703 */
704 ||' l_hv := mod(objconinst.'||p_detailcol_name
705 ||',ben_hash_utility.get_hash_key); '
706 /*
707 --
708 -- Temporary: Debugging statements
709 --
710 ||' hr_utility.set_location('||''''||'objconinst.'||p_detailcol_name||' '||''''
711 ||'||objconinst.'||p_detailcol_name||',10); '
712 */
713
714 ||' if '||p_lkup_name||'.exists(l_hv) then'
715 ||l_lkcacpkcmp_str||' '
716 ||' l_not_hash_found := false; '
717 ||' while not l_not_hash_found loop'
718 ||' l_hv := ben_hash_utility.get_next_hash_index(p_hash_index => l_hv); '
719 /*
720 --
721 -- Temporary: Debugging statements
722 --
723 ||' hr_utility.set_location('||''''||' new l_hv: '||''''
724 ||'||l_hv,10); '
725 */
726 ||' if '||p_lkup_name||'.exists(l_hv) then '
727 ||l_lkcacpkcmp_str||' '
728 ||' l_not_hash_found := false; '
729 ||' else '
730 ||' l_not_hash_found := true; '
731 ||' exit; '
732 ||' end if; '
733 ||' end if; '
734 ||' end loop; '
735 ||' end if; '
736 ||' else '
737 ||' fnd_message.set_name('||''''||'BEN'||''''||','||''''
738 ||'BEN_?????_MISS_HASH_LOOK'||''''||'); '
739 ||' fnd_message.raise_error; '
740 ||' end if; '
741 /*
742 --
743 -- Temporary: Debugging statements
744 --
745 ||' hr_utility.set_location('||''''||' l_prev_hv: '||''''
746 ||'||l_prev_hv,10); '
747 */
748 ||' if l_prev_hv = -1 then '
749 ||' '||p_lkup_name||'(l_hv).starttorele_num := l_torrwnum; '
750 ||' elsif l_hv <> l_prev_hv then '
751 ||' '||p_lkup_name||'(l_prev_hv).endtorele_num := l_torrwnum-1; '
752 ||' '||p_lkup_name||'(l_hv).starttorele_num := l_torrwnum; '
753 /*
754 --
755 -- Temporary: Debugging statements
756 --
757 ||' hr_utility.set_location('||''''||' l_prev_hv: '||''''
758 ||'||l_prev_hv,10); '
759 */
760 ||' end if; '
761 ||' '||l_asgcolval_str
762 ||' l_torrwnum := l_torrwnum+1; '
763 ||' l_prev_hv := l_hv; '
764 ||' end loop; '
765 ||' close c_instance; '
766 ||' '||p_lkup_name||'(l_prev_hv).endtorele_num := l_torrwnum-1; '
767 ||' end if; ';
768 --
769 else
770 --
771 l_inst_loopstr := ' l_torrwnum := 0; '
772 ||' l_prev_hv := -1; '
773 ||' if '||p_lkup_name||'.count > 0 then '
774 ||' for objinst in c_instance '
775 ||l_instcurpmcall_str||' '
776 ||' loop '
777 /*
778 --
779 -- Temporary: Debugging statements
780 --
781 ||' hr_utility.set_location('||''''||'Instance loop '||''''||',10); '
782 */
783 ||l_insthv_str
784 /*
785 --
786 -- Temporary: Debugging statements
787 --
788 ||' hr_utility.set_location('||''''||'objinst.'||p_detailcol_name||' '||''''
789 ||'||objinst.'||p_detailcol_name||',10); '
790 */
791 /*
792 --
793 -- Temporary: Debugging statements
794 --
798 ||' if '||p_lkup_name||'.exists(l_hv) then'
795 ||' dbms_output.put_line('||''''||' l_hv: '||''''
796 ||'||l_hv); '
797 */
799 ||l_lkcacpkcmp_str||' '
800 ||' l_not_hash_found := false; '
801 ||' while not l_not_hash_found loop'
802 ||' l_hv := ben_hash_utility.get_next_hash_index(p_hash_index => l_hv); '
803 ||' if '||p_lkup_name||'.exists(l_hv) then '
804 ||l_lkcacpkcmp_str||' '
805 ||' l_not_hash_found := false; '
806 ||' else '
807 ||' l_not_hash_found := true; '
808 ||' exit; '
809 ||' end if; '
810 ||' end if; '
811 ||' end loop; '
812 ||' end if; '
813 ||' else '
814 ||' fnd_message.set_name('||''''||'BEN'||''''||','||''''
815 ||'BEN_?????_MISS_HASH_LOOK'||''''||'); '
816 ||' fnd_message.raise_error; '
817 ||' end if; '
818 /*
819 --
820 -- Temporary: Debugging statements
821 --
822 ||' dbms_output.put_line('||''''||' l_prev_hv: '||''''
823 ||'||l_prev_hv); '
824 */
825 ||' if l_prev_hv = -1 then '
826 ||' '||p_lkup_name||'(l_hv).starttorele_num := l_torrwnum; '
827 ||' elsif l_hv <> l_prev_hv then '
828 ||' '||p_lkup_name||'(l_prev_hv).endtorele_num := l_torrwnum-1; '
829 ||' '||p_lkup_name||'(l_hv).starttorele_num := l_torrwnum; '
830 /*
831 --
832 -- Temporary: Debugging statements
833 --
834 ||' hr_utility.set_location('||''''||' l_prev_hv: '||''''
835 ||'||l_prev_hv,10); '
836 */
837
838 ||' end if; '
839 ||' '||l_asgcolval_str
840 ||' l_torrwnum := l_torrwnum+1; '
841 ||' l_prev_hv := l_hv; '
842 ||' end loop; '
843 ||' '||p_lkup_name||'(l_prev_hv).endtorele_num := l_torrwnum-1; '
844 ||' end if; ';
845 --
846 end if;
847 --
848 l_plsql_str := 'DECLARE '
849 ||' e_num_val_err exception; '
850 ||' PRAGMA EXCEPTION_INIT(e_num_val_err, -6502); '
851 ||' l_torrwnum pls_integer; '
852 ||' l_prev_hv pls_integer; '
853 ||' l_hv pls_integer; '
854 ||' l_not_hash_found boolean; '
855 ||l_dclv_vardecstr||' '
856 ||l_lkup_curdecstr
857 ||l_coninst_curdecstr
858 ||l_inst_curdecstr
859 ||'begin '
860 ||l_dclv_varassstr||' '
861 /*
862 --
863 -- Temporary: Debugging statements
864 --
865 ||' hr_utility.set_location('||''''||'Entering BENCACHE PLSBLK '||''''||',10); '
866 */
867 ||l_lkup_loopstr
868 /*
869 --
870 -- Temporary: Debugging statements
871 --
872 ||' hr_utility.set_location('||''''||'Dn Lookup loop '||''''||',10); '
873 */
874 ||l_inst_loopstr
875 /*
876 --
877 -- Temporary: Debugging statements
878 --
879 ||' hr_utility.set_location ('||''''||'Write cache, '||p_lkup_name||'.count: '||''''
880 ||'||'||p_lkup_name||'.count,10); '
881 --
882 ||' hr_utility.set_location ('||''''||'Write cache, '||p_inst_name||'.count: '||''''
883 ||'||'||p_lkup_name||'.count,10); '
884 --
885 */
886 ||' exception '
887 ||' when others then '
888 ||' hr_utility.set_location('||''''||' BENCACHE PLSBLK Others Exc: '||''''||',10); '
889 /*
890 --
891 -- Temporary: Debugging statements
892 --
893 ||' dbms_output.put_line('||''''||' SQLERRM: '||''''
894 ||'||SQLERRM); '
895 */
896 ||' raise; '
897 ||' end; ';
898 --
899 -- hr_utility.set_location (l_proc||' Exe DSQL ',90);
900 --
901 EXECUTE IMMEDIATE l_plsql_str;
902 --
903 -- hr_utility.set_location (l_proc||' Leaving ',100);
904 exception
905 when others then
906 hr_utility.set_location (l_proc||' Others Exc ',100);
907 raise;
908 end Write_MastDet_Cache;
909 --
910 procedure Write_BGP_Cache
911 (p_mastertab_name in varchar2
912 ,p_masterpkcol_name in varchar2
913 ,p_masterfkcol_name in varchar2 default null
914 ,p_masterfk1col_name in varchar2 default null
915 ,p_masterfk2col_name in varchar2 default null
916 ,p_masterfk3col_name in varchar2 default null
917 ,p_masterfk4col_name in varchar2 default null
918 ,p_masterfk5col_name in varchar2 default null
919 ,p_masid_set in ben_cache.IdType default ben_cache.g_id_set
920 ,p_tabdet_set in ben_cache.TabDetType default ben_cache.g_tabdet_set
921 ,p_table1_name in varchar2
922 ,p_tab1jncol_name in varchar2 default null
923 ,p_table2_name in varchar2 default null
924 ,p_tab2jncol_name in varchar2 default null
925 ,p_table3_name in varchar2 default null
926 ,p_business_group_id in number
927 ,p_effective_date in date default null
928 ,p_context1_colname in varchar2 default null
929 ,p_context1_id in number default null
933 ,p_inst_frclause in varchar2 default null
930 ,p_nonmand_hv in boolean default false
931 ,p_lkup_name in varchar2
932 ,p_inst_name in varchar2
934 ,p_lkup_whclause in varchar2 default null
935 ,p_inst_whclause in varchar2 default null
936 ,p_inst_queryorby in varchar2 default null
937 ,p_lkup_subqyhint in varchar2 default null
938 ,p_lkup_query in varchar2 default null
939 ,p_instcolnm_set in ben_cache.InstColNmType
940 default ben_cache.g_instcolnm_set
941 ,p_curparm_set in ben_cache.CurParmType
942 default ben_cache.g_curparm_set
943 )
944
945 is
946 --
947 l_proc varchar2(72) := 'Write_BGP_Cache';
948 --
949 l_tabdet_set ben_cache.TabDetType;
950 l_curparm_set ben_cache.CurParmType;
951 l_selinstcolnm_set ben_cache.InstColNmType;
952 l_selinstcol_num pls_integer;
953 l_instcolnm_num pls_integer;
954 --
955 l_torrw_num pls_integer;
956 l_curparmele_num pls_integer;
957 l_restinstcolnm_count pls_integer;
958 --
959 l_lkup_selclause long;
960 l_lkup_query long;
961 l_lkup_subfromclause long;
962 l_lkup_subwhclause long;
963 l_lkup_subquery long;
964 l_inst_selclause long;
965 l_inst_query long;
966 l_lkup_fromclause long;
967 l_lkup_whereclause long;
968 l_lkup_queryorby long;
969 l_inst_fromclause long;
970 l_inst_whereclause long;
971 l_inst_queryorby long;
972 l_masid_whereclause long;
973 l_master_tabname varchar2(100);
974 l_master_tabalias varchar2(100);
975 --
976 l_bgpcurpm_name varchar2(100);
977 l_effdatecurpm_name varchar2(100);
978 l_con1idcurpm_name varchar2(100);
979 --
980 begin
981 --
982 -- Define table details
983 --
984 if p_tabdet_set.count = 0 then
985 --
986 if p_table1_name is not null then
987 --
988 l_tabdet_set(0).tab_name := p_table1_name;
989 l_tabdet_set(0).tab_jncolnm := p_tab1jncol_name;
990 --
991 end if;
992 --
993 if p_table2_name is not null then
994 --
995 l_tabdet_set(1).tab_name := p_table2_name;
996 l_tabdet_set(1).tab_jncolnm := p_tab2jncol_name;
997 --
998 end if;
999 --
1000 if p_table3_name is not null then
1001 --
1002 l_tabdet_set(2).tab_name := p_table3_name;
1003 --
1004 end if;
1005 --
1006 else
1007 --
1008 l_tabdet_set := p_tabdet_set;
1009 --
1010 end if;
1011 --
1012 -- Define cursor parameters
1013 --
1014 l_bgpcurpm_name := 'c_business_group_id';
1015 l_effdatecurpm_name := 'c_effective_date';
1016 l_con1idcurpm_name := 'c_con1_id';
1017 --
1018 l_curparm_set.delete;
1019 l_curparmele_num := 0;
1020 --
1021 l_curparm_set(l_curparmele_num).name := l_bgpcurpm_name;
1022 l_curparm_set(l_curparmele_num).datatype := 'NUMBER';
1023 l_curparm_set(l_curparmele_num).numval := p_business_group_id;
1024 l_curparmele_num := l_curparmele_num+1;
1025 --
1026 if p_effective_date is not null then
1027 --
1028 l_curparm_set(l_curparmele_num).name := l_effdatecurpm_name;
1029 l_curparm_set(l_curparmele_num).datatype := 'DATE';
1030 l_curparm_set(l_curparmele_num).dateval := p_effective_date;
1031 l_curparmele_num := l_curparmele_num+1;
1032 --
1033 end if;
1034 --
1035 -- Check if the context value is set
1036 --
1037 if p_context1_id is not null then
1038 --
1039 l_curparm_set(l_curparmele_num).name := l_con1idcurpm_name;
1040 l_curparm_set(l_curparmele_num).datatype := 'NUMBER';
1041 l_curparm_set(l_curparmele_num).numval := p_context1_id;
1042 l_curparmele_num := l_curparmele_num+1;
1043 --
1044 end if;
1045 --
1046 -- Build the lookup query
1047 --
1048 -- Select clause
1049 --
1050 l_lkup_selclause := ' select master.'||p_masterpkcol_name||', '
1051 ||' master.business_group_id ';
1052 --
1053 -- Check for master fk col name
1054 --
1055 if p_masterfkcol_name is not null then
1056 --
1057 l_lkup_selclause := l_lkup_selclause||', master.'||p_masterfkcol_name;
1058 --
1059 end if;
1060 --
1061 -- Check for master fk col 1 name
1062 --
1063 if p_masterfk1col_name is not null then
1064 --
1065 l_lkup_selclause := l_lkup_selclause||', master.'||p_masterfk1col_name;
1066 --
1067 end if;
1068 --
1069 -- Check for master fk col 2 name
1070 --
1071 if p_masterfk2col_name is not null then
1072 --
1073 l_lkup_selclause := l_lkup_selclause||', master.'||p_masterfk2col_name;
1074 --
1075 end if;
1076 --
1077 if p_masterfk3col_name is not null then
1078 --
1079 l_lkup_selclause := l_lkup_selclause||', master.'||p_masterfk3col_name;
1080 --
1081 end if;
1082 --
1086 --
1083 if p_masterfk4col_name is not null then
1084 --
1085 l_lkup_selclause := l_lkup_selclause||', master.'||p_masterfk4col_name;
1087 end if;
1088 --
1089 if p_masterfk5col_name is not null then
1090 --
1091 l_lkup_selclause := l_lkup_selclause||', master.'||p_masterfk5col_name;
1092 --
1093 end if;
1094 --
1095 -- From clause
1096 --
1097 l_lkup_fromclause := ' from '||p_mastertab_name||' master ';
1098 --
1099 -- Where clause
1100 --
1101 l_lkup_whereclause := ' where master.business_group_id = '
1102 ||l_bgpcurpm_name;
1103 --
1104 -- Append master id where clause
1105 --
1106 if l_masid_whereclause is not null then
1107 --
1108 l_lkup_whereclause := l_lkup_whereclause||' and '||l_masid_whereclause;
1109 --
1110 end if;
1111 --
1112 if p_effective_date is not null then
1113 --
1114 l_lkup_whereclause := l_lkup_whereclause||' and '||l_effdatecurpm_name
1115 ||' between master.effective_start_date '
1116 ||' and master.effective_end_date ';
1117 --
1118 end if;
1119 --
1120 -- Restrict by the sub context id
1121 --
1122 if p_context1_id is not null then
1123 --
1124 l_lkup_whereclause := l_lkup_whereclause||' and '||p_context1_colname
1125 ||' = '||l_con1idcurpm_name;
1126 --
1127 end if;
1128 --
1129 -- Check for a custom restriction for the lookup query
1130 --
1131 if p_lkup_whclause is not null then
1132 --
1133 l_lkup_whereclause := l_lkup_whereclause||' '||p_lkup_whclause;
1134 --
1135 end if;
1136 --
1137 if l_tabdet_set.count > 0 then
1138 --
1139 l_lkup_subfromclause := ' from ';
1140 l_lkup_subwhclause := ' where table1.'
1141 ||p_masterpkcol_name
1142 ||' = master.'||p_masterpkcol_name;
1143 --
1144 l_inst_fromclause := ' from ';
1145 --
1146 -- Check for non mandatory hash values
1147 --
1148 if not p_nonmand_hv then
1149 --
1150 l_inst_whereclause := ' where table1.'||p_masterpkcol_name||' is not null '
1151 ||' and table1.business_group_id = '||l_bgpcurpm_name;
1152 --
1153 else
1154 --
1155 l_inst_whereclause := ' where table1.business_group_id = '||l_bgpcurpm_name;
1156 --
1157 end if;
1158 --
1159 -- Append master id where clause
1160 --
1161 if l_masid_whereclause is not null then
1162 --
1163 l_inst_whereclause := l_inst_whereclause||' and '||l_masid_whereclause;
1164 --
1165 end if;
1166 --
1167 -- Restrict by the sub context id
1168 --
1169 if p_context1_id is not null then
1170 --
1171 l_inst_whereclause := l_inst_whereclause||' and '||p_context1_colname
1172 ||' = '||l_con1idcurpm_name;
1173 --
1174 end if;
1175 --
1176 -- Check for a custom restriction for the instance query
1177 --
1178 if p_inst_whclause is not null then
1179 --
1180 l_inst_whereclause := l_inst_whereclause||' '||p_inst_whclause;
1181 --
1182 end if;
1183 --
1184 for l_torrw_num in l_tabdet_set.first .. l_tabdet_set.last loop
1185 --
1186 -- Build lookup sub from clause
1187 --
1188 if l_tabdet_set.first = l_torrw_num then
1189 --
1190 l_lkup_subfromclause := l_lkup_subfromclause||' '||l_tabdet_set(l_torrw_num).tab_name||' table'||to_char(l_torrw_num+1);
1191 --
1192 else
1193 --
1194 l_lkup_subfromclause := l_lkup_subfromclause||', '||l_tabdet_set(l_torrw_num).tab_name||' table'||to_char(l_torrw_num+1);
1195 --
1196 end if;
1197 --
1198 -- Build lookup sub where clause
1199 --
1200 if l_tabdet_set(l_torrw_num).tab_jncolnm is not null then
1201 --
1202 l_lkup_subwhclause := l_lkup_subwhclause||' and table'||to_char(l_torrw_num+1)||'.'
1203 ||l_tabdet_set(l_torrw_num).tab_jncolnm
1204 ||' = table'||to_char(l_torrw_num+2)
1205 ||'.'||l_tabdet_set(l_torrw_num).tab_jncolnm;
1206 --
1207 end if;
1208 --
1209 -- Build lookup date restrictions
1210 --
1211 if p_effective_date is not null
1212 and l_tabdet_set(l_torrw_num).tab_datetype is null
1213 then
1214 --
1215 l_lkup_subwhclause := l_lkup_subwhclause||' and '||l_effdatecurpm_name
1216 ||' between table'||to_char(l_torrw_num+1)||'.effective_start_date '
1217 ||' and table'||to_char(l_torrw_num+1)||'.effective_end_date';
1218 --
1219 end if;
1220 --
1221 -- Build instance from clause
1222 --
1223 if l_tabdet_set.first = l_torrw_num then
1224 --
1225 l_inst_fromclause := l_inst_fromclause||' '||l_tabdet_set(l_torrw_num).tab_name||' table'||to_char(l_torrw_num+1);
1226 --
1227 else
1228 --
1232 --
1229 l_inst_fromclause := l_inst_fromclause||', '||l_tabdet_set(l_torrw_num).tab_name||' table'||to_char(l_torrw_num+1);
1230 --
1231 end if;
1233 -- Build instance join conditions
1234 --
1235 if l_tabdet_set(l_torrw_num).tab_jncolnm is not null then
1236 --
1237 l_inst_whereclause := l_inst_whereclause||' and table'||to_char(l_torrw_num+1)||'.'
1238 ||l_tabdet_set(l_torrw_num).tab_jncolnm
1239 ||' = table'||to_char(l_torrw_num+2)
1240 ||'.'||l_tabdet_set(l_torrw_num).tab_jncolnm;
1241 --
1242 end if;
1243 --
1244 -- Build instance date restrictions
1245 --
1246 if p_effective_date is not null
1247 and l_tabdet_set(l_torrw_num).tab_datetype is null
1248 then
1249 --
1250 l_inst_whereclause := l_inst_whereclause
1251 ||' and '||l_effdatecurpm_name
1252 ||' between table'||to_char(l_torrw_num+1)||'.effective_start_date '
1253 ||' and table'||to_char(l_torrw_num+1)||'.effective_end_date';
1254 --
1255 end if;
1256 --
1257 end loop;
1258 --
1259 -- Append custom instance from clause
1260 --
1261 if p_inst_frclause is not null then
1262 --
1263 l_inst_fromclause := l_inst_fromclause||', '||p_inst_frclause;
1264 --
1265 end if;
1266 --
1267 -- Build the lookup sub query
1268 --
1269 l_lkup_subquery := ' and master.'||p_masterpkcol_name||' in(select ';
1270 --
1271 -- Append sub query hint
1272 --
1273 if p_lkup_subqyhint is not null then
1274 --
1275 l_lkup_subquery := l_lkup_subquery
1276 ||'/*+ '||p_lkup_subqyhint||' */ ';
1277
1278 --
1279 end if;
1280 --
1281 -- Append sub query
1282 --
1283 l_lkup_subquery := l_lkup_subquery||' table1.'
1284 ||p_masterpkcol_name||' '
1285 ||l_lkup_subfromclause||' '
1286 ||l_lkup_subwhclause||')';
1287 --
1288 end if;
1289 --
1290 -- Build where clause
1291 --
1292 l_lkup_whereclause := l_lkup_whereclause||' '||l_lkup_subquery||' ';
1293 --
1294 -- Order by clause
1295 --
1296 l_lkup_queryorby := 'order by master.'||p_masterpkcol_name||'; ';
1297 --
1298 -- Check if lookup query is set
1299 --
1300 if p_lkup_query is not null then
1301 --
1302 l_lkup_query := p_lkup_query;
1303 --
1304 else
1305 --
1306 l_lkup_query := l_lkup_selclause
1307 ||l_lkup_fromclause
1308 ||l_lkup_whereclause
1309 ||' '||l_lkup_queryorby;
1310 --
1311 end if;
1312 --
1313 -- Build the instance query
1314 --
1315 -- Select clause
1316 --
1317 if p_instcolnm_set.count > 0 then
1318 --
1319 l_inst_selclause := 'select ';
1320 --
1321 for l_torrw_num in p_instcolnm_set.first .. p_instcolnm_set.last loop
1322 --
1323 if l_torrw_num = 0 then
1324 --
1325 l_inst_selclause := l_inst_selclause
1326 ||' '||p_instcolnm_set(0).col_alias
1327 ||'.'||p_instcolnm_set(0).col_name;
1328 --
1329 else
1330 --
1331 l_inst_selclause := l_inst_selclause
1332 ||', '||p_instcolnm_set(l_torrw_num).col_alias
1333 ||'.'||p_instcolnm_set(l_torrw_num).col_name;
1334 --
1335 end if;
1336 --
1337 end loop;
1338 --
1339 else
1340 --
1341 l_inst_selclause := 'select * ';
1342 --
1343 end if;
1344 --
1345 -- Build the instance order by clause
1346 --
1347 if p_instcolnm_set.count > 0 then
1348 --
1349 l_inst_queryorby := ' order by ';
1350 l_selinstcol_num := 0;
1351 --
1352 for l_torrw_num in p_instcolnm_set.first .. p_instcolnm_set.last loop
1353 --
1354 if p_instcolnm_set(l_torrw_num).col_type = 'MASTER'
1355 then
1356 --
1357 l_inst_queryorby := l_inst_queryorby
1358 ||' '||p_instcolnm_set(l_torrw_num).col_alias
1359 ||'.'||p_instcolnm_set(l_torrw_num).col_name;
1360 --
1361 elsif p_instcolnm_set(l_torrw_num).col_type = 'RESTRICT'
1362 then
1363 --
1364 l_inst_queryorby := l_inst_queryorby
1365 ||', '||p_instcolnm_set(l_torrw_num).col_alias
1366 ||'.'||p_instcolnm_set(l_torrw_num).col_name;
1367 --
1368 elsif p_instcolnm_set(l_torrw_num).col_type = 'TABLE'
1369 then
1370 --
1371 null;
1372 --
1373 else
1374 --
1375 -- Populate selection instance column name set
1376 --
1377 l_selinstcolnm_set(l_selinstcol_num) := p_instcolnm_set(l_torrw_num);
1381 --
1378 l_selinstcol_num := l_selinstcol_num+1;
1379 --
1380 end if;
1382 end loop;
1383 --
1384 -- Append extra order by clause
1385 --
1386 if p_inst_queryorby is not null then
1387 --
1388 l_inst_queryorby := l_inst_queryorby||', '||p_inst_queryorby;
1389 --
1390 end if;
1391 --
1392 l_inst_queryorby := l_inst_queryorby||'; ';
1393 --
1394 end if;
1395 --
1396 l_inst_query := l_inst_selclause
1397 ||' '||l_inst_fromclause
1398 ||' '||l_inst_whereclause
1399 ||' '||l_inst_queryorby;
1400 --
1401 ben_cache.Write_MastDet_Cache
1402 (p_mastercol_name => p_masterpkcol_name
1403 ,p_detailcol_name => p_masterpkcol_name
1404 ,p_masterfkcol_name => p_masterfkcol_name
1405 ,p_masterfk1col_name => p_masterfk1col_name
1406 ,p_masterfk2col_name => p_masterfk2col_name
1407 ,p_masterfk3col_name => p_masterfk3col_name
1408 ,p_masterfk4col_name => p_masterfk4col_name
1409 ,p_masterfk5col_name => p_masterfk5col_name
1410 ,p_lkup_name => p_lkup_name
1411 ,p_inst_name => p_inst_name
1412 ,p_lkup_query => l_lkup_query
1413 ,p_inst_query => l_inst_query
1414 ,p_nonmand_hv => p_nonmand_hv
1415 ,p_instcolnm_set => p_instcolnm_set
1416 ,p_curparm_set => l_curparm_set
1417 );
1418 --
1419 end Write_BGP_Cache;
1420 --
1421 function check_list_duplicate
1422 (p_list in out nocopy ben_cache.IdType
1423 ,p_id in number
1424 )
1425 return boolean
1426 is
1427
1428 l_hv pls_integer;
1429 l_clash_count pls_integer;
1430
1431 begin
1432 --
1433 if p_id is null then
1434 --
1435 return true;
1436 --
1437 end if;
1438 --
1439 l_hv := mod(p_id,ben_hash_utility.get_hash_key);
1440 --
1441 if p_list.exists(l_hv)
1442 then
1443 --
1444 -- Validate that the ids match
1445 --
1446 if p_list(l_hv).id = p_id
1447 then
1448 --
1449 return true;
1450 --
1451 else
1452 --
1453 -- Clash found. Skip until correct value is found
1454 --
1455 l_hv := l_hv+ben_hash_utility.get_hash_jump;
1456 l_clash_count := 0;
1457 --
1458 loop
1459 --
1460 if p_list(l_hv).id = p_id
1461 then
1462 --
1463 return true;
1464 exit;
1465 --
1466 else
1467 --
1468 -- One in a billion case of different ids getting
1469 -- same hash value. Increment for 1000 skips
1470 --
1471 if l_clash_count < 1000 then
1472 --
1473 l_hv := l_hv+ben_hash_utility.get_hash_jump;
1474 l_clash_count := l_clash_count+1;
1475 --
1476 else
1477 --
1478 -- Value does not exist in list so assign to the next
1479 -- free hash value+jump element
1480 --
1481 l_hv := mod(p_id,ben_hash_utility.get_hash_key)+ben_hash_utility.get_hash_jump;
1482 --
1483 loop
1484 --
1485 if p_list.exists(l_hv) then
1486 --
1487 l_hv := l_hv+ben_hash_utility.get_hash_jump;
1488 --
1489 else
1490 --
1491 p_list(l_hv).id := p_id;
1492 return false;
1493 exit;
1494 --
1495 end if;
1496 --
1497 end loop;
1498 --
1499 end if;
1500 --
1501 end if;
1502 --
1503 end loop;
1504 --
1505 end if;
1506 --
1507 else
1508 --
1509 p_list(l_hv).id := p_id;
1510 return false;
1511 --
1512 end if;
1513 --
1514 end;
1515 --
1516 end ben_cache;