1 package body EDW_NAEDW_PUSH AS
2 /*$Header: EDWNAEDB.pls 115.29 2004/02/13 05:08:51 smulye ship $*/
3
4 g_debug boolean:=false;
5
6 cursor c0 is
7 select dim.dim_name,
8 dim.dim_id
9 from edw_dimensions_md_v dim
10 where dim_name not like 'EDW_GL_ACCT%_M'
11 and dim_name <> 'EDW_NA'
12 order by dim.dim_name;
13
14 cursor c0_0(p_dim varchar2) is
15 select dim.dim_name,dim.dim_id
16 from
17 edw_dimensions_md_v dim
18 where dim_long_name=p_dim;
19
20 cursor c00(p_dim_id number) is
21 select level_name||'_LTC'
22 from edw_levels_md_v
23 where dim_id=p_dim_id;
24
25 cursor c1(p_dim_id number) is
26 select distinct
27 lvl_child.level_name||'_LTC',
28 lvl_parent.level_name||'_LTC'
29 from
30 edw_levels_md_v lvl_child,
31 edw_levels_md_v lvl_parent,
32 edw_pvt_level_relation_md_v lvl_rel,
33 edw_hierarchies_md_v hier
34 where
35 hier.dim_id=p_dim_id
36 and lvl_rel.hierarchy_id=hier.hier_id
37 and lvl_child.level_id=lvl_rel.child_level_id
38 and lvl_parent.level_id=lvl_rel.parent_level_id;
39
40 cursor c2(p_dim_id number) is
41 select
42 relation.name,
43 item.column_name,
44 item.data_type,
45 'FK',
46 parent.name
47 from
48 edw_levels_md_v lvl,
49 edw_tables_md_v relation,
50 edw_pvt_columns_md_v item,
51 edw_foreign_keys_md_v fk,
52 edw_pvt_key_columns_md_v isu,
53 edw_unique_keys_md_v pk,
54 edw_tables_md_v parent
55 where
56 lvl.dim_id=p_dim_id
57 and relation.name=lvl.level_name||'_LTC'
58 and item.parent_object_id=relation.elementid
59 and fk.entity_id=relation.elementid
60 and isu.key_id=fk.foreign_key_id
61 and item.column_id=isu.column_id
62 and pk.key_id=fk.key_id
63 and parent.elementid=pk.entity_id
64 union all
65 select
66 relation.name,
67 item.column_name,
68 item.data_type,
69 'PK',
70 null
71 from
72 edw_levels_md_v lvl,
73 edw_tables_md_v relation,
74 edw_pvt_columns_md_v item,
75 edw_unique_keys_md_v pk,
76 edw_pvt_key_columns_md_v isu
77 where
78 lvl.dim_id=p_dim_id
79 and relation.name=lvl.level_name||'_LTC'
80 and item.parent_object_id=relation.elementid
81 and pk.entity_id=relation.elementid
82 and isu.key_id=pk.key_id
83 and item.column_id=isu.column_id;
84
85 --all other cols
86 cursor c3(p_dim_id number,p_owner varchar2) is
87 select
88 relation.name,
89 item.column_name,
90 item.length,
91 item.data_type
92 from
93 edw_tables_md_v relation,
94 edw_pvt_columns_md_v item
95 where
96 relation.name in (select level_name||'_LTC' from edw_levels_md_v where dim_id=p_dim_id)
97 and item.parent_object_id=relation.elementid
98 and item.column_name in ('NAME');
99
100 cursor c3_II(p_dim_id number,p_owner varchar2) is
101 select
102 all_tab.table_name,
103 all_tab.column_name,
104 all_tab.data_length,
105 all_tab.data_type
106 from
107 all_tab_columns all_tab,
108 all_tables tab
109 where
110 all_tab.nullable in ('N')
111 and all_tab.table_name in (select level_name||'_LTC' from edw_levels_md_v where dim_id=p_dim_id)
112 and tab.table_name=all_tab.table_name
113 and tab.owner=p_owner
114 and all_tab.owner= p_owner
115
116 and all_tab.column_name not in ('NAME');
117
118 PROCEDURE PUSH (Errbuf out NOCOPY varchar2,
119 retcode out NOCOPY varchar2) IS
120
121 Begin
122
123 retcode:='0';
124 g_debug:=null;
125 Init_all;
126 write_to_log_file_n('In PUSH for all Dimensions');
127 if g_status=false then
128 errbuf:=g_status_message;
129 retcode:='2';
130 return;
131 end if;
132
133 write_to_log_file_n('START PUSH NAEDW for all dimensions, Start time '||get_time);
134
135 Read_Metadata;
136 if g_status=false then
137 write_to_log_file_n('Error in Read Metadata '||g_status_message||get_time);
138 errbuf:=g_status_message;
139 retcode:='2';
140 return;
141 end if;
142 write_to_log_file_n('Read Metadata done...'||get_time);
143 Execute_insert_stmt;
144 write_to_log_file_n('Executed all insert '||get_time);
145
146 --special case for edw_na
147
148 write_to_log_file_n('Executed all populate_edw_na..End of NAEDW push '||get_time);
149 if g_all_dims_ok=false or g_status=false then
150 retcode:='1';
151 errbuf:='Please check log file for problems.';
152 end if;
153 finish_all(true);
154
155 Exception when others then
156 g_status_message:=sqlerrm;
157 write_to_log_file_n(g_status_message);
158 errbuf:=g_status_message;
159 retcode:='2';
160 finish_all(false);
161 End;--PROCEDURE PUSH
162
163 /*
164 called from the conc manager for a specific dimension
165 */
166 PROCEDURE PUSH(Errbuf out NOCOPY varchar2,
167 retcode out NOCOPY varchar2,
168 p_dim_string in varchar2) IS
169 Begin
170 retcode:='0';
171 g_debug:=null;
172 Init_all;
173 write_to_log_file_n('In PUSH with dim name passed');
174 if g_status=false then
175 errbuf:=g_status_message;
176 retcode:='2';
177 return;
178 end if;
179 if g_debug then
180 write_to_log_file_n('Debug flag turned ON');
181 else
182 write_to_log_file_n('Debug flag turned OFF');
183 end if;
184 if g_debug then
185 write_to_log_file_n('p_dim_string ='||p_dim_string);
186 end if;
187
188 if p_dim_string is null then
189 write_to_log_file_n('NA_EDW for all dimensions'||get_time);
190 PUSH(errbuf, retcode);
191 return;
192 else
193 write_to_log_file_n('NAEDW for specific dimension '||p_dim_string||get_time);
194 g_dim_string_flag:=true;
195 --Parse_dim_names(p_dim_string);--instead of read metadata
196 if get_one_dim_name(p_dim_string,'LONGNAME') = false then
197 errbuf:=g_status_message;
198 retcode:='2';
199 return;
200 end if;
201 Execute_insert_stmt;
202 if g_all_dims_ok=false or g_status=false then
203 retcode:='2';
204 errbuf:=g_status_message;
205 finish_all(false);
206 return;
207 end if;
208 finish_all(true);
209 end if;
210 Exception when others then
211 g_status_message:=sqlerrm;
212 write_to_log_file_n(g_status_message);
213 errbuf:=g_status_message;
214 retcode:='2';
215 finish_all(false);
216 End;--PROCEDURE PUSH with dim string
217
218 /*
219 called from collection engine for a specific dimension
220 */
221 PROCEDURE PUSH(Errbuf out NOCOPY varchar2,
222 retcode out NOCOPY varchar2,
223 p_dim_string in varchar2,
224 p_debug boolean) IS
225 Begin
226 retcode:='0';
227 g_debug:=p_debug;
228 Init_all;
229 g_coll_engine_call:=true;
230 write_to_log_file_n('In PUSH with dim name passed');
231 if g_status=false then
232 errbuf:=g_status_message;
233 retcode:='2';
234 return;
235 end if;
236 if g_debug then
237 write_to_log_file_n('Debug flag turned ON');
238 else
239 write_to_log_file_n('Debug flag turned OFF');
240 end if;
241 if g_debug then
242 write_to_log_file_n('p_dim_string ='||p_dim_string);
243 end if;
244 if get_dim_pk(p_dim_string) =false then
245 errbuf:=g_status_message;
246 retcode:='2';
247 return;
248 end if;
249 --if there is a row in the star table, no need to check the levels either
250 if naedw_in_star(p_dim_string)=true then
251 g_naedw_in :=true;
252 write_to_log_file('NA_EDW row already in star table.');
253 else
254 g_naedw_in :=false;
255 write_to_log_file('NA_EDW row not present in star table');
256 end if;
257 if err_in_star(p_dim_string)=true then
258 g_err_in :=true;
259 write_to_log_file('ERR row already in star table.');
260 else
261 g_err_in :=false;
262 write_to_log_file('ERR row not present in star table');
263 end if;
264 if g_err_in=true and g_naedw_in=true then
265 write_to_log_file_n('Both NAEDW and ERROR rows in the star table');
266 return;
267 end if;
268
269 g_dim_string_flag:=true;
270 --Parse_dim_names(p_dim_string);--instead of read metadata
271 if get_one_dim_name(p_dim_string,'NAME') = false then
272 errbuf:=g_status_message;
273 retcode:='2';
274 return;
275 end if;
276 Execute_insert_stmt;
277 if g_all_dims_ok=false or g_status=false then
278 retcode:='2';
279 errbuf:=g_status_message;
280 finish_all(false);
281 return;
282 end if;
283 finish_all(true);
284 Exception when others then
285 g_status_message:=sqlerrm;
286 write_to_log_file_n(g_status_message);
287 errbuf:=g_status_message;
288 retcode:='2';
289 finish_all(false);
290 End;
291
292
293 function get_status_message return varchar2 is
294 begin
295 return g_status_message;
296 End;--function get_status_message return varchar2 is
297
298 PROCEDURE Read_Metadata IS
299
300 Begin
301 open c0;
302 g_number_dims:=1;
303 loop
304 fetch c0 into g_dim_name(g_number_dims),
305 g_dim_id(g_number_dims);
306 exit when c0%NOTFOUND;
307 g_number_dims:=g_number_dims+1;
308 end loop;
309 g_number_dims:=g_number_dims-1;
310
311 close c0;
312 --dbms_output.put_line('number of dime='||g_number_dims);
313
314 Exception when others then
315 if c0%isopen then
316 close c0;
317 end if;
318 g_status_message:=sqlerrm;
319 write_to_log_file_n(g_status_message);
320 g_status:=false;
321 End;--PROCEDURE Read_Metadata IS
322
323 function get_one_dim_name(p_dim_string varchar2,p_type varchar2) return boolean IS
324 TYPE CurTyp IS REF CURSOR;
325 cv CurTyp;
326 l_stmt varchar2(1000);
327 begin
328 if g_debug then
329 write_to_log_file_n('In get_one_dim_name, dim ='||p_dim_string);
330 end if;
331 g_number_dims:=1;
332 g_dim_name(g_number_dims):=null;
333 g_dim_id(g_number_dims):=null;
334 if p_type='LONGNAME' then
335 l_stmt:='select dim_name,dim_id from edw_dimensions_md_v where dim_long_name=:s';
336 else
337 l_stmt:='select dim_name,dim_id from edw_dimensions_md_v where dim_name=:s';
338 end if;
339 if g_debug then
340 write_to_log_file_n('Going to execute ');
341 write_to_log_file(l_stmt);
342 end if;
343 open cv for l_stmt using p_dim_string;
344 fetch cv into g_dim_name(g_number_dims),g_dim_id(g_number_dims);
345 close cv;
346 write_to_log_file_n('Dimension='||g_dim_name(g_number_dims)||' and id='||g_dim_id(g_number_dims));
347 if g_dim_id(g_number_dims) is null then
348 return false;
349 else
350 return true;
351 end if;
352 Exception when others then
353 write_to_log_file_n('Error in get_one_dim_name '||sqlerrm||get_time);
354 g_status_message:=sqlerrm;
355 g_status:=false;
356 return false;
357 End;--PROCEDURE parse_dim_names(p_dim_string varchar2) IS
358
359
360 PROCEDURE parse_dim_names(p_dim_string varchar2) IS
361 l_start number;
362 l_end number;
363 l_str varchar2(300);
364 l_len number;
365 l_dim_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
366 l_dim_id EDW_OWB_COLLECTION_UTIL.numberTableType;
367 l_dim_number number:=1;
368 Begin
369 l_start:=1;
370 l_end:=1;
371 l_len:=length(p_dim_string);
372 loop
373 l_end:=instr(p_dim_string,':',l_start);
374 if l_end = -1 then
375 exit;
376 end if;
377 g_number_dims:=g_number_dims+1;
378 g_dim_name(g_number_dims):=substr(p_dim_string,l_start,l_end-l_start);
379 l_start:=l_end+1;
380 if l_start >= l_len then
381 exit;
382 end if;
383 end loop;
384 --get the ids
385 open c0;
386 loop
387 fetch c0 into
388 l_dim_name(l_dim_number),
389 l_dim_id(l_dim_number);
390 exit when c0%notfound;
391 l_dim_number:=l_dim_number+1;
392 end loop;
393 close c0;
394 l_dim_number:=l_dim_number-1;
395 for i in 1..g_number_dims loop
396 for j in 1..l_dim_number loop
397 if g_dim_name(i)=l_dim_name(j) then
398 g_dim_id(i):=l_dim_id(j);
399 exit;
400 end if;
401 end loop;
402 end loop;
403 Exception when others then
404 if c0%isopen then
405 close c0;
406 end if;
407 write_to_log_file_n('Error in parse dim names '||sqlerrm||get_time);
408 g_status_message:=sqlerrm;
409 g_status:=false;
410 End;--PROCEDURE parse_dim_names(p_dim_string varchar2) IS
411
412 PROCEDURE Parse_Metadata(p_dim_index number) IS
413
414 l_parent EDW_OWB_COLLECTION_UTIL.varcharTableType;
415 l_child EDW_OWB_COLLECTION_UTIL.varcharTableType;
416 l_parent_level varchar2(400);
417 l_number number:=1;
418 l_found boolean;
419 Begin
420 g_all_level:=null;
421 Begin
422 g_number_levels:=1;
423 open c00(g_dim_id(p_dim_index));
424 loop
425 fetch c00 into g_levels(g_number_levels);
426 exit when c00%NOTFOUND;
427 g_number_levels:=g_number_levels+1;
428 end loop;
429 close c00;
430 g_number_levels:=g_number_levels-1;
431 Exception when others then
432 if c00%isopen then
433 close c00;
434 end if;
435 g_status_message:=sqlerrm;
436 write_to_log_file_n(g_status_message);
437 g_status:=false;
438 return;
439 end;
440
441 Begin
442 open c1(g_dim_id(p_dim_index));
443 loop
444 fetch c1 into
445 l_child(l_number), l_parent(l_number);
446 exit when c1%NOTFOUND;
447 l_number:=l_number+1;
448 end loop;
449 l_number:=l_number-1;
450 close c1;
451 Exception when others then
452 if c1%isopen then
453 close c1;
454 end if;
455 g_status_message:=sqlerrm;
456 write_to_log_file_n(g_status_message);
457 g_status:=false;
458 end;
459
460 --process the results here. first get the parent
461 for i in 1..g_number_levels loop
462 l_found:=false;
463 for j in 1..l_number loop
464 if g_levels(i)=l_child(j) then
465 l_found:=true;
466 exit;
467 end if;
468 end loop;
472 g_level_status(i):='P';
469 if l_found=false then
470 if g_levels(i) = substr(g_dim_name(p_dim_index),1,instr(g_dim_name(p_dim_index),'_M',-1)-1)||'_A_LTC' then
471 l_parent_level:=g_levels(i);
473 g_all_level:=g_levels(i);
474 else
475 g_level_status(i):='C';
476 end if;
477 exit;
478 end if;
479 end loop;
480
481 --assign the child status
482 for i in 1..g_number_levels loop
483 for j in 1..l_number loop
484 if l_child(j)=g_levels(i) then
485 if l_parent(j)=l_parent_level then
486 g_level_status(i):='CP';
487 else
488 g_level_status(i):='C';
489 end if;
490 exit;
491 end if;
492 end loop;
493 end loop;
494
495 Exception when others then
496 g_status_message:=sqlerrm;
497 write_to_log_file_n(g_status_message);
498 g_status:=false;
499 End;--PROCEDURE Parse_Metadata IS
500
501 PROCEDURE Make_insert_stmt(p_level_index number) IS
502 Begin
503 g_check_stmt:='select 1 from '||g_levels(p_level_index)||' where '||
504 g_level_pk(g_varchar_pk_index)||' = '||''''||g_naedw_varchar2||''''||' or '||
505 g_level_pk(g_varchar_pk_index)||' = '||''''||g_all_varchar2||'''';
506
507 g_err_check_stmt:='select 1 from '||g_levels(p_level_index)||' where '||
508 g_level_pk(g_varchar_pk_index)||' = '||''''||g_err_varchar2||'''';
509
510 g_insert_stmt:='insert into '||g_levels(p_level_index)||' ( ';
511 make_body_insert_update_stmt(p_level_index,true);
512 if g_status=false then
513 return;
514 end if;
515 g_insert_stmt:=g_insert_stmt||g_body_insert_update_stmt;
516 g_err_insert_stmt:='insert into '||g_levels(p_level_index)||' ( ';
517 make_err_body_insert_stmt(p_level_index,true);
518 if g_status=false then
519 return;
520 end if;
521 g_err_insert_stmt:=g_err_insert_stmt||g_err_body_insert_update_stmt;
522 Exception when others then
523 g_status_message:=sqlerrm;
524 write_to_log_file_n('Error in make_insert_stmt for level '||g_levels(p_level_index)||' '||
525 g_status_message||get_time);
526 g_status:=false;
527 End;--PROCEDURE Make_insert_stmt IS
528
529 PROCEDURE Make_Update_Stmt(p_level_index number) IS
530 first_find boolean :=true;
531 l_pk_stmt varchar2(4000);
532 l_fk_stmt varchar2(4000);
533 Begin
534 g_update_stmt:='update '||g_levels(p_level_index)||' set ( ';
535 make_body_insert_update_stmt(p_level_index,false);
536 if g_status=false then
537 return;
538 end if;
539 g_update_stmt:=g_update_stmt||g_body_insert_update_stmt;
540 g_err_update_stmt:='update '||g_levels(p_level_index)||' set ( ';
541 make_err_body_insert_stmt(p_level_index,false);
542 if g_status=false then
543 return;
544 end if;
545 g_err_update_stmt:=g_err_update_stmt||g_err_body_insert_update_stmt;
546 Exception when others then
547 g_status_message:=sqlerrm;
548 write_to_log_file_n('Error in make_update_stmt for level '||g_levels(p_level_index)||' '||
549 g_status_message||get_time);
550 g_status:=false;
551 End;--PROCEDURE Make_Update_Stmt(p_level_index number) IS
552
553 PROCEDURE make_body_insert_update_stmt(p_level_index number, p_insert_flag boolean) IS
554 first_find boolean:=true;
555 l_pk_stmt varchar2(4000);
556 l_fk_stmt varchar2(4000);
557 l_pk_num number;--which is the number pk
558 Begin
559 l_pk_num:=1;
560 g_body_insert_update_stmt:='';
561 for i in 1..g_level_pk_number loop
562 if first_find then
563 g_body_insert_update_stmt:=g_body_insert_update_stmt||' '||g_level_pk(i);
564 first_find:=false;
565 else
566 g_body_insert_update_stmt:=g_body_insert_update_stmt||','||g_level_pk(i);
567 end if;
568 end loop;
569
570 for i in 1..g_level_fk_number loop
571 g_body_insert_update_stmt:=g_body_insert_update_stmt||','||g_level_fk(i);
572 end loop;
573
574 for i in 1..g_level_col_number loop
575 if g_level_cols_datatype(i) = 'VARCHAR2' then
576 if g_level_status(p_level_index)='P' then
577 if g_level_cols_length(i) > g_all_varchar2_mesg_length then
578 g_body_insert_update_stmt:=g_body_insert_update_stmt||','||g_level_cols(i);
579 end if;
580 else
581 if g_level_cols_length(i) > g_unassigned_length then
582 g_body_insert_update_stmt:=g_body_insert_update_stmt||','||g_level_cols(i);
583 end if;
584 end if;
585 else
586 if g_level_cols(i) <> 'CREATION_DATE' and g_level_cols(i) <> 'LAST_UPDATE_DATE' then
587 g_body_insert_update_stmt:=g_body_insert_update_stmt||','||g_level_cols(i);
588 end if;
589 end if;
590 end loop;
591 if p_insert_flag then
592 --if g_level_status(p_level_index)<>'P' then
593 g_body_insert_update_stmt:=g_body_insert_update_stmt||',CREATION_DATE, LAST_UPDATE_DATE)';
594 --else
595 --g_body_insert_update_stmt:=g_body_insert_update_stmt||')';
596 --end if;
597 g_body_insert_update_stmt:=g_body_insert_update_stmt||' values (';
598 else
599 --if g_level_status(p_level_index)<>'P' then
600 g_body_insert_update_stmt:=g_body_insert_update_stmt||',LAST_UPDATE_DATE)';
601 --else
605 end if;
602 --g_body_insert_update_stmt:=g_body_insert_update_stmt||')';
603 --end if;
604 g_body_insert_update_stmt:=g_body_insert_update_stmt||'= (select ';
606 first_find:=true;
607
608 for i in 1..g_level_pk_number loop
609 if g_level_pk_datatype(i)='VARCHAR2' then
610 if g_level_status(p_level_index)='P' then
611 l_pk_stmt:=''''||g_all_varchar2||'''';
612 else
613 l_pk_stmt:=''''||g_naedw_varchar2||'''';
614 end if;
615 elsif g_level_pk_datatype(i)='NUMBER' then
616 l_pk_num:=i;
617 if g_level_status(p_level_index)='P' then
618 l_pk_stmt:=g_all_number;
619 else
620 l_pk_stmt:=g_naedw_number;
621 end if;
622 elsif g_level_pk_datatype(i)='DATE' then
623 l_pk_stmt:=g_naedw_date;
624 else
625 l_pk_stmt:=''''||g_naedw_varchar2||'''';
626 end if;
627 if first_find then
628 g_body_insert_update_stmt:=g_body_insert_update_stmt||' '||l_pk_stmt;
629 first_find:=false;
630 else
631 g_body_insert_update_stmt:=g_body_insert_update_stmt||','||l_pk_stmt;
632 end if;
633 end loop;
634 for i in 1..g_level_fk_number loop
635 --if g_level_status(p_level_index)='P' OR g_level_status(p_level_index)='CP' then
636 if g_level_fk_parent(i)=g_all_level then
637 if g_level_fk_datatype(i)='VARCHAR2' then
638 l_fk_stmt:=''''||g_all_varchar2||'''';
639 elsif g_level_fk_datatype(i)='DATE' then
640 l_fk_stmt:=''''||g_all_date||'''';
641 elsif g_level_fk_datatype(i)='NUMBER' then
642 l_fk_stmt:=g_all_number;
643 else
644 l_fk_stmt:=''''||g_all_varchar2||'''';
645 end if;
646 else
647 if g_level_fk_datatype(i)='VARCHAR2' then
648 l_fk_stmt:=''''||g_naedw_varchar2||'''';
649 elsif g_level_fk_datatype(i)='DATE' then
650 l_fk_stmt:=''''||g_naedw_date||'''';
651 elsif g_level_fk_datatype(i)='NUMBER' then
652 l_fk_stmt:=g_naedw_number;
653 else
654 l_fk_stmt:=''''||g_naedw_varchar2||'''';
655 end if;
656 end if;
657 g_body_insert_update_stmt:=g_body_insert_update_stmt||','||l_fk_stmt;
658 end loop;
659
660 for i in 1..g_level_col_number loop
661 if g_level_cols_datatype(i) = 'VARCHAR2' then
662 if g_level_status(p_level_index)='P' then
663 if g_level_cols_length(i) > g_all_varchar2_mesg_length then
664 g_body_insert_update_stmt:=g_body_insert_update_stmt||','||''''||g_all_varchar2_mesg||'''';
665 end if;
666 else
667 if g_level_cols_length(i) > g_unassigned_length then
668 g_body_insert_update_stmt:=g_body_insert_update_stmt||','||''''||g_unassigned||'''';
669 end if;
670 end if;
671 elsif g_level_cols_datatype(i) = 'DATE' then
672 if g_level_cols(i) <> 'CREATION_DATE' and g_level_cols(i) <> 'LAST_UPDATE_DATE' then
673 g_body_insert_update_stmt:=g_body_insert_update_stmt||','||'null';
674 end if;
675 elsif g_level_cols_datatype(i) = 'NUMBER' then
676 g_body_insert_update_stmt:=g_body_insert_update_stmt||','||g_naedw_number;
677 end if;
678 end loop;
679 if p_insert_flag then
680 --if g_level_status(p_level_index)<>'P' then
681 g_body_insert_update_stmt:=g_body_insert_update_stmt||',SYSDATE,SYSDATE)';
682 --else
683 --g_body_insert_update_stmt:=g_body_insert_update_stmt||')';
684 --end if;
685 else
686 --if g_level_status(p_level_index)<>'P' then
687 g_body_insert_update_stmt:=g_body_insert_update_stmt||',SYSDATE ';
688 --end if;
689 g_body_insert_update_stmt:=g_body_insert_update_stmt||' from dual) where '||
690 g_level_pk(l_pk_num)||'=';
691 if g_level_status(p_level_index)='P' then
692 g_body_insert_update_stmt:=g_body_insert_update_stmt||g_all_number||' ';
693 else
694 g_body_insert_update_stmt:=g_body_insert_update_stmt||g_naedw_number||' ';
695 end if;
696 end if;
697 Exception when others then
698 g_status_message:=sqlerrm;
699 write_to_log_file_n('Error in make_body_insert_update_stmt for level '||g_levels(p_level_index)||' '||
700 g_status_message||get_time);
701 g_status:=false;
702 End;--make_body_insert_update_stmt(p_level_index number) IS
703
704 PROCEDURE make_err_body_insert_stmt(p_level_index number, p_insert_flag boolean) IS
705 first_find boolean:=true;
706 l_pk_stmt varchar2(4000);
707 l_fk_stmt varchar2(4000);
708 l_pk_num number;--which is the number pk
709 Begin
710 l_pk_num:=1;
711 g_err_body_insert_update_stmt:='';
712 for i in 1..g_level_pk_number loop
713 if first_find then
714 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||' '||g_level_pk(i);
715 first_find:=false;
716 else
717 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||g_level_pk(i);
718 end if;
719 end loop;
720
721 for i in 1..g_level_fk_number loop
722 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||g_level_fk(i);
723 end loop;
724
725 for i in 1..g_level_col_number loop
726 if g_level_cols_datatype(i) = 'VARCHAR2' then
727 if g_level_status(p_level_index)='P' then
728 if g_level_cols_length(i) > g_all_varchar2_mesg_length then
732 if g_level_cols_length(i) > g_invalid_length then
729 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||g_level_cols(i);
730 end if;
731 else
733 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||g_level_cols(i);
734 end if;
735 end if;
736 else
737 if g_level_cols(i) <> 'CREATION_DATE' and g_level_cols(i) <> 'LAST_UPDATE_DATE' then
738 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||g_level_cols(i);
739 end if;
740 end if;
741 end loop;
742 if p_insert_flag then
743 --if g_level_status(p_level_index)<>'P' then
744 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||',CREATION_DATE, LAST_UPDATE_DATE)';
745 --else
746 --g_body_insert_update_stmt:=g_body_insert_update_stmt||')';
747 --end if;
748 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||' values (';
749 else
750 --if g_level_status(p_level_index)<>'P' then
751 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||',LAST_UPDATE_DATE)';
752 --else
753 --g_body_insert_update_stmt:=g_body_insert_update_stmt||')';
754 --end if;
755 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||'= (select ';
756 end if;
757 first_find:=true;
758
759 for i in 1..g_level_pk_number loop
760 if g_level_pk_datatype(i)='VARCHAR2' then
761 if g_level_status(p_level_index)='P' then
762 l_pk_stmt:=''''||g_all_varchar2||'''';
763 else
764 l_pk_stmt:=''''||g_err_varchar2||'''';
765 end if;
766 elsif g_level_pk_datatype(i)='NUMBER' then
767 l_pk_num:=i;
768 if g_level_status(p_level_index)='P' then
769 l_pk_stmt:=g_all_number;
770 else
771 l_pk_stmt:=g_err_number;
772 end if;
773 elsif g_level_pk_datatype(i)='DATE' then
774 l_pk_stmt:=g_naedw_date;
775 else
776 l_pk_stmt:=''''||g_err_varchar2||'''';
777 end if;
778 if first_find then
779 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||' '||l_pk_stmt;
780 first_find:=false;
781 else
782 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||l_pk_stmt;
783 end if;
784 end loop;
785 for i in 1..g_level_fk_number loop
786 --if g_level_status(p_level_index)='P' OR g_level_status(p_level_index)='CP' then
787 if g_level_fk_parent(i)=g_all_level then
788 if g_level_fk_datatype(i)='VARCHAR2' then
789 l_fk_stmt:=''''||g_all_varchar2||'''';
790 elsif g_level_fk_datatype(i)='DATE' then
791 l_fk_stmt:=''''||g_all_date||'''';
792 elsif g_level_fk_datatype(i)='NUMBER' then
793 l_fk_stmt:=g_all_number;
794 else
795 l_fk_stmt:=''''||g_all_varchar2||'''';
796 end if;
797 else
798 if g_level_fk_datatype(i)='VARCHAR2' then
799 l_fk_stmt:=''''||g_err_varchar2||'''';
800 elsif g_level_fk_datatype(i)='DATE' then
801 l_fk_stmt:=''''||g_naedw_date||'''';
802 elsif g_level_fk_datatype(i)='NUMBER' then
803 l_fk_stmt:=g_err_number;
804 else
805 l_fk_stmt:=''''||g_err_varchar2||'''';
806 end if;
807 end if;
808 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||l_fk_stmt;
809 end loop;
810
811 for i in 1..g_level_col_number loop
812 if g_level_cols_datatype(i) = 'VARCHAR2' then
813 if g_level_status(p_level_index)='P' then
814 if g_level_cols_length(i) > g_all_varchar2_mesg_length then
815 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||''''||g_all_varchar2_mesg||'''';
816 end if;
817 else
818 if g_level_cols_length(i) > g_invalid_length then
819 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||''''||g_invalid||'''';
820 end if;
821 end if;
822 elsif g_level_cols_datatype(i) = 'DATE' then
823 if g_level_cols(i) <> 'CREATION_DATE' and g_level_cols(i) <> 'LAST_UPDATE_DATE' then
824 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||'null';
825 end if;
826 elsif g_level_cols_datatype(i) = 'NUMBER' then
827 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||','||g_err_number;
828 end if;
829 end loop;
830 if p_insert_flag then
831 --if g_level_status(p_level_index)<>'P' then
832 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||',SYSDATE,SYSDATE)';
833 --else
834 --g_body_insert_update_stmt:=g_body_insert_update_stmt||')';
835 --end if;
836 else
837 --if g_level_status(p_level_index)<>'P' then
838 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||',SYSDATE ';
839 --end if;
840 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||' from dual) where '||
841 g_level_pk(l_pk_num)||'=';
842 if g_level_status(p_level_index)='P' then
843 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||g_all_number||' ';
844 else
845 g_err_body_insert_update_stmt:=g_err_body_insert_update_stmt||g_err_number||' ';
846 end if;
847 end if;
848 Exception when others then
849 g_status_message:=sqlerrm;
853 End;
850 write_to_log_file_n('Error in make_err_body_insert_update_stmt for level '||g_levels(p_level_index)||' '||
851 g_status_message||get_time);
852 g_status:=false;
854
855 PROCEDURE Execute_insert_stmt_level(p_level_index number) IS
856 TYPE CurTyp IS REF CURSOR;
857 cv CurTyp;
858 v_int number:=null;
859 l_insert boolean:=true;
860 l_stmt varchar2(10000);
861 Begin
862 if g_exec_flag= false then
863 return;
864 end if;
865 Begin
866 if g_debug then
867 write_to_log_file_n('Going to execute ');
868 write_to_log_file(g_check_stmt);
869 end if;
870 open cv for g_check_stmt;
871 fetch cv into v_int;
872 close cv;
873 if v_int is null then
874 l_insert:=true;
875 write_to_log_file_n('Need to insert NAEDW or ALL for '||g_levels(p_level_index));
876 else
877 l_insert:=false;
878 write_to_log_file_n('NAEDW or ALL already present for '||g_levels(p_level_index));
879 return ;
880 end if;
881 Exception when others then
882 g_status_message:=sqlerrm;
883 write_to_log_file_n(g_status_message||get_time);
884 write_to_log_file('Problem statement '||g_check_stmt);
885 g_status:=false;
886 return;
887 end;
888 if l_insert then
889 l_stmt:=g_insert_stmt;
890 else
891 if g_coll_engine_call=false then
892 l_stmt:=g_update_stmt;
893 else
894 return ;
895 end if;
896 end if;
897 if g_debug then
898 write_to_log_file_n('Going to execute ');
899 write_to_log_file(l_stmt);
900 end if;
901 Begin
902 execute immediate l_stmt;
903 write_to_log_file_n('Processed '||sql%rowcount||' rows');
904 Exception when others then
905 g_status_message:=sqlerrm;
906 write_to_log_file_n(g_status_message||get_time);
907 write_to_log_file('Problem stmt '||l_stmt);
908 g_status:=false;
909 end;
910 Exception when others then
911 g_status_message:=sqlerrm;
912 write_to_log_file_n(g_status_message||get_time);
913 g_status:=false;
914 End;--PROCEDURE Execute_insert_stmt_level(p_level_index number) IS
915
916 PROCEDURE Execute_err_insert_stmt_level(p_level_index number) IS
917 TYPE CurTyp IS REF CURSOR;
918 cv CurTyp;
919 v_int number:=null;
920 l_insert boolean:=true;
921 l_stmt varchar2(10000);
922 Begin
923 if g_exec_flag= false then
924 return;
925 end if;
926 Begin
927 if g_debug then
928 write_to_log_file_n('Going to execute ');
929 write_to_log_file(g_err_check_stmt);
930 end if;
931 open cv for g_err_check_stmt;
932 fetch cv into v_int;
933 close cv;
934 if v_int is null then
935 l_insert:=true;
936 write_to_log_file_n('Need to insert ERR for '||g_levels(p_level_index));
937 else
938 l_insert:=false;
939 write_to_log_file_n('ERR already present for '||g_levels(p_level_index));
940 return ;
941 end if;
942 Exception when others then
943 g_status_message:=sqlerrm;
944 write_to_log_file_n(g_status_message||get_time);
945 write_to_log_file('Problem statement '||g_err_check_stmt);
946 g_status:=false;
947 return;
948 end;
949 if l_insert then
950 l_stmt:=g_err_insert_stmt;
951 else
952 if g_coll_engine_call=false then
953 l_stmt:=g_err_update_stmt;
954 else
955 return;
956 end if;
957 end if;
958 if g_debug then
959 write_to_log_file_n('Going to execute ');
960 write_to_log_file(l_stmt);
961 end if;
962 Begin
963 execute immediate l_stmt;
964 write_to_log_file_n('Processed '||sql%rowcount||' rows');
965 Exception when others then
966 g_status_message:=sqlerrm;
967 write_to_log_file_n(g_status_message||get_time);
968 write_to_log_file('Problem stmt '||l_stmt);
969 g_status:=false;
970 end;
971 Exception when others then
972 g_status_message:=sqlerrm;
973 write_to_log_file_n(g_status_message||get_time);
974 g_status:=false;
975 End;
976
977 PROCEDURE Get_all_cols(p_index number) IS
978 l_relation_key EDW_OWB_COLLECTION_UTIL.varcharTableType;
979 l_item_key EDW_OWB_COLLECTION_UTIL.varcharTableType;
980 l_data_key EDW_OWB_COLLECTION_UTIL.varcharTableType;
981 l_type_key EDW_OWB_COLLECTION_UTIL.varcharTableType;
982 l_number_key number:=1;
983 l_relation EDW_OWB_COLLECTION_UTIL.varcharTableType;
984 l_item EDW_OWB_COLLECTION_UTIL.varcharTableType;
985 l_item_length EDW_OWB_COLLECTION_UTIL.numberTableType;
986 l_data EDW_OWB_COLLECTION_UTIL.varcharTableType;
987 l_type EDW_OWB_COLLECTION_UTIL.varcharTableType;
988 l_parent EDW_OWB_COLLECTION_UTIL.varcharTableType;
989 l_number number:=1;
990 l_found boolean;
991 l_owner varchar2(400);
992 Begin
993
994 Begin
995 open c2(g_dim_id(p_index));
996 loop
997 fetch c2 into
998 l_relation_key(l_number_key),
999 l_item_key(l_number_key),
1000 l_data_key(l_number_key),
1001 l_type_key(l_number_key),
1005 end loop;
1002 l_parent(l_number_key);
1003 exit when c2%notfound;
1004 l_number_key:=l_number_key+1;
1006 l_number_key:=l_number_key-1;
1007 close c2;
1008 exception when others then
1009 if c2%isopen then
1010 close c2;
1011 end if;
1012 g_status_message:=sqlerrm;
1013 write_to_log_file_n('Error in cursor c2 Get_all_cols for '||g_dim_name(p_index)||' '||
1014 g_status_message||get_time);
1015 g_status:=false;
1016 return;
1017 end;
1018 Begin
1019 --get the table owner
1020 l_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_dim_name(p_index));
1021 if g_debug then
1022 write_to_log_file_n('The owner for '||g_dim_name(p_index)||' is '||l_owner);
1023 end if;
1024 if l_owner is null then
1025 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1026 g_status:=false;
1027 return;
1028 end if;
1029 open c3(g_dim_id(p_index),l_owner);
1030 loop
1031 fetch c3 into
1032 l_relation(l_number),
1033 l_item(l_number),
1034 l_item_length(l_number),
1035 l_data(l_number);
1036 exit when c3%notfound;
1037 l_number:=l_number+1;
1038 end loop;
1039 --l_number:=l_number-1;
1040 close c3;
1041 exception when others then
1042 if c3%isopen then
1043 close c3;
1044 end if;
1045 g_status_message:=sqlerrm;
1046 write_to_log_file_n('Error in cursor c3 Get_all_cols for '||g_dim_name(p_index)||' '||
1047 g_status_message||get_time);
1048 g_status:=false;
1049 end;
1050 begin
1051 open c3_II(g_dim_id(p_index),l_owner);
1052 loop
1053 fetch c3_II into
1054 l_relation(l_number),
1055 l_item(l_number),
1056 l_item_length(l_number),
1057 l_data(l_number);
1058 exit when c3_II%notfound;
1059 l_number:=l_number+1;
1060 end loop;
1061 l_number:=l_number-1;
1062 close c3_II;
1063 exception when others then
1064 g_status_message:=sqlerrm;
1065 write_to_log_file_n('Error in cursor c3_II Get_all_cols for '||g_dim_name(p_index)||' '||
1066 g_status_message||get_time);
1067 g_status:=false;
1068 end ;
1069 --assign the pk and fk to the levels
1070 --for each level, we also make the dml stmt
1071 for i in 1..g_number_levels loop
1072 g_level_fk_number:=0;
1073 g_level_pk_number:=0;
1074 g_level_col_number:=0;
1075 g_varchar_pk_index:=0;
1076 for j in 1..l_number_key loop
1077 if l_relation_key(j)=g_levels(i) then
1078 if l_type_key(j)='PK' then
1079 g_level_pk_number:=g_level_pk_number+1;
1080 g_level_pk(g_level_pk_number):=l_item_key(j);
1081 g_level_pk_datatype(g_level_pk_number):=l_data_key(j);
1082 if l_data_key(j) = 'VARCHAR2' then
1083 g_varchar_pk_index:=g_level_pk_number;
1084 end if;
1085 elsif l_type_key(j)='FK' then
1086 g_level_fk_number:=g_level_fk_number+1;
1087 g_level_fk(g_level_fk_number):=l_item_key(j);
1088 g_level_fk_datatype(g_level_fk_number):=l_data_key(j);
1089 g_level_fk_parent(g_level_fk_number):=l_parent(j);
1090 end if;
1091 end if;
1092 end loop;
1093 if g_debug then
1094 write_to_log_file_n('Level '||g_levels(i)||' , the unique keys and data type ');
1095 for j in 1..g_level_pk_number loop
1096 write_to_log_file(g_level_pk(j)||' '||g_level_pk_datatype(j));
1097 end loop;
1098 write_to_log_file_n('Level '||g_levels(i)||' , the foreign keys and data type ');
1099 for j in 1..g_level_fk_number loop
1100 write_to_log_file(g_level_fk(j)||' '||g_level_fk_datatype(j)||' '||g_level_fk_parent(j));
1101 end loop;
1102 end if;
1103 for j in 1..l_number loop
1104 if l_relation(j)=g_levels(i) then
1105 l_found:=false;
1106 for k in 1..g_level_pk_number loop
1107 if g_level_pk(k)=l_item(j) then
1108 l_found:=true;
1109 exit;
1110 end if;
1111 end loop;
1112 if l_found=false then
1113 for k in 1..g_level_fk_number loop
1114 if g_level_fk(k)=l_item(j) then
1115 l_found:=true;
1116 exit;
1117 end if;
1118 end loop;
1119 if l_found=false then
1120 g_level_col_number:=g_level_col_number+1;
1121 g_level_cols(g_level_col_number):=l_item(j);
1122 g_level_cols_length(g_level_col_number):=l_item_length(j);
1123 g_level_cols_datatype(g_level_col_number):=l_data(j);
1124 end if;
1125 end if;
1126 end if;
1127 end loop;
1128 if g_debug then
1129 write_to_log_file_n('Level '||g_levels(i)||', the columns and datatype and length ');
1130 for j in 1..g_level_col_number loop
1131 write_to_log_file(g_level_cols(j)||' '||
1132 g_level_cols_datatype(j)||' '||g_level_cols_length(j));
1133 end loop;
1134 end if;
1135 --make the stmt
1136 Make_insert_stmt(i); --pass the level index
1137 --EDW_OWB_COLLECTION_UTIL.print_stmt(g_insert_stmt);
1138 if g_status = false then
1139 return;
1140 end if;
1144 if g_status = false then
1141 --we dont need to make update anymore. no updates at all..if the row is there, just skip the level
1142 if g_coll_engine_call=false then
1143 Make_update_stmt(i); --pass the level index
1145 return;
1146 end if;
1147 end if;
1148 Execute_insert_stmt_level(i);
1149 if g_status = false then
1150 return;
1151 end if;
1152 if g_level_status(i) <> 'P' then --no need for all level
1153 Execute_err_insert_stmt_level(i);
1154 if g_status = false then
1155 return;
1156 end if;
1157 end if;
1158 end loop;
1159 Exception when others then
1160 g_status_message:=sqlerrm;
1161 write_to_log_file_n('Error in Get_all_cols for '||g_dim_name(p_index)||' '||
1162 g_status_message||get_time);
1163 g_status:=false;
1164 End;--PROCEDURE Get_all_cols(p_index number)
1165
1166 PROCEDURE Execute_insert_stmt IS
1167 Begin
1168
1169 for i in 1..g_number_dims loop
1170 g_status:=true;
1171 Parse_Metadata(i);
1172 if g_status then
1173 Get_all_cols(i);
1174 else
1175 g_all_dims_ok:=false;
1176 write_to_log_file_n('Error in Dimension '||g_dim_name(i)||' '||
1177 g_status_message||get_time);
1178 end if;
1179 if g_status=true then
1180 write_to_log_file_n('Finished Dimension '||g_dim_name(i)||get_time);
1181 else
1182 write_to_log_file_n('Error in Dimension '||g_dim_name(i)||' '||
1183 g_status_message||get_time);
1184 g_all_dims_ok:=false;
1185 end if;
1186 end loop;
1187
1188 End;--PROCEDURE Execute_insert_stmt IS
1189
1190 PROCEDURE Init_All IS
1191 Begin
1192 g_conc_program_name:='EDW_NAEDW_PUSH';
1193 EDW_OWB_COLLECTION_UTIL.init_all(g_conc_program_name,null,'bis.edw.loader');
1194 write_to_log_file_n('Finished setting up the log file');
1195 if g_debug is null then
1196 if fnd_profile.value('EDW_DEBUG')='Y' then
1197 g_debug:=true;--look at the profile value for this
1198 else
1199 g_debug:=false;
1200 end if;
1201 end if;
1202 g_coll_engine_call:=false;
1203 g_status:=true;
1204 g_status_message:=' ';
1205 g_exec_flag:=true;
1206 g_all_dims_ok:=true;
1207 g_number_dims:=0;
1208 g_dim_string_flag:=false;--user specifies the list of dims
1209 g_level_fk_number:=0;
1210 g_level_col_number:=0;
1211 FND_MESSAGE.SET_NAME('BIS','EDW_UNASSIGNED');
1212 g_unassigned:=FND_MESSAGE.GET;
1213 g_unassigned:=replace(g_unassigned,'''','''''');
1214 FND_MESSAGE.SET_NAME('BIS','EDW_INVALID');
1215 g_invalid:=FND_MESSAGE.GET;
1216 g_invalid:=replace(g_invalid,'''','''''');
1217 if g_debug then
1218 write_to_log_file_n('Unassigned is '||g_unassigned);
1219 write_to_log_file_n('Invalid Record is '||g_invalid);
1220 end if;
1221 g_unassigned_length:=length(g_invalid);
1222 g_invalid_length:=length(g_unassigned);
1223 write_to_log_file_n('Finished assigning g_unassigned');
1224 g_naedw_varchar2:='NA_EDW';
1225 g_err_varchar2:='NA_ERR';
1226 g_naedw_date:='to_date(''01/01/1000'',''MM/DD/YYYY'')';
1227 g_naedw_number:='0';
1228 g_err_number:='-1';
1229 g_all_varchar2:='ALL';
1230 FND_MESSAGE.SET_NAME('BIS','EDW_ALL');
1231 g_all_varchar2_mesg:=FND_MESSAGE.GET;
1232 g_all_varchar2_mesg:=replace(g_all_varchar2_mesg,'''','''''');
1233 g_all_varchar2_mesg_length:=length(g_all_varchar2_mesg);
1234 if g_debug then
1235 write_to_log_file_n('All is '||g_all_varchar2_mesg);
1236 end if;
1237 write_to_log_file_n('Finished assigning g_all');
1238 g_all_number:='1';
1239 g_all_date:='to_date(''01/01/1000'',''MM/DD/YYYY'')';
1240 g_conc_program_id:=FND_GLOBAL.Conc_request_id;--my conc id
1241 write_to_log_file_n('Concurrent request ID '||g_conc_program_id);
1242 Exception when others then
1243 write_to_log_file_n('Error in Init '||sqlerrm||get_time);
1244 g_status_message:=sqlerrm;
1245 g_status:=false;
1246 End;--PROCEDURE Init_All;
1247
1248 procedure finish_all(p_flag boolean) is
1249 begin
1250 if p_flag=true then
1251 EDW_OWB_COLLECTION_UTIL.commit_conc_program_log;--this issues a commit
1252 else
1253 rollback;
1254 EDW_OWB_COLLECTION_UTIL.commit_conc_program_log;
1255 end if;
1256 End;--procedure finish_all(p_flag boolean) is
1257
1258
1259 function get_time return varchar2 is
1260 begin
1261 return ' '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
1262 Exception when others then
1263 write_to_log_file_n('Error in get_time '||sqlerrm);
1264
1265 End;
1266
1267 procedure write_to_log_file(p_message varchar2) is
1268 begin
1269 EDW_OWB_COLLECTION_UTIL.write_to_log_file(p_message);
1270 Exception when others then
1271 null;
1272 End;
1273
1274 procedure write_to_log_file_n(p_message varchar2) is
1275 begin
1276 write_to_log_file(' ');
1277 write_to_log_file(p_message);
1278 Exception when others then
1279 null;
1280 End;
1281
1282 function get_dim_pk(p_dim varchar2) return boolean is
1283 l_stmt varchar2(4000);
1284 TYPE CurTyp IS REF CURSOR;
1285 cv CurTyp;
1286 Begin
1287 l_stmt:='select pk_item.column_name from edw_dimensions_md_v rel , edw_unique_keys_md_v pk, '||
1288 'edw_pvt_key_columns_md_v isu,edw_pvt_columns_md_v pk_item where rel.dim_name=:a '||
1289 'and pk.entity_id=rel.dim_id and pk.primarykey=1 '||
1290 'and isu.key_id=pk.key_id and pk_item.column_id=isu.column_id ';
1291 if g_debug then
1292 write_to_log_file_n('Going to execute '||l_stmt);
1293 end if;
1294 open cv for l_stmt using p_dim;
1295 fetch cv into g_dim_pk;
1296 close cv;
1297 if g_debug then
1298 write_to_log_file_n('Dim pk is '||g_dim_pk);
1299 end if;
1300 return true;
1301 Exception when others then
1302 g_status_message:=sqlerrm;
1303 g_status:=false;
1304 write_to_log_file_n(g_status_message);
1305 return false;
1306 End;
1307
1308 function naedw_in_star(p_dim varchar2) return boolean is
1309 Begin
1310 if EDW_OWB_COLLECTION_UTIL.does_table_have_data(p_dim,g_dim_pk||'=0')=2 then
1311 return true;
1312 else
1313 return false;
1314 end if;
1315 Exception when others then
1316 g_status_message:=sqlerrm;
1317 g_status:=false;
1318 write_to_log_file_n(g_status_message);
1319 return false;
1320 End;
1321
1322 function err_in_star(p_dim varchar2) return boolean is
1323 Begin
1324 if EDW_OWB_COLLECTION_UTIL.does_table_have_data(p_dim,g_dim_pk||'=-1')=2 then
1325 return true;
1326 else
1327 return false;
1328 end if;
1329 Exception when others then
1330 g_status_message:=sqlerrm;
1331 g_status:=false;
1332 write_to_log_file_n(g_status_message);
1333 return false;
1334 End;
1335
1336
1337 END EDW_NAEDW_PUSH;
[Home] [Help]