[Home] [Help]
PACKAGE BODY: APPS.CZ_BASE_MGR
Source
1 package body cz_base_mgr as
2 /* $Header: czbsmgrb.pls 120.4 2008/02/06 15:43:04 lamrute ship $ */
3
4 RECORD_COUNTER INTEGER:=0;
5
6 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
7 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
8
9
10 PROCEDURE exec_plsql_block
11 (
12 p_table_name IN VARCHAR2,
13 p_where IN VARCHAR2,
14 p_pk_col1_type IN VARCHAR2 DEFAULT NULL,
15 p_pk_col2_type IN VARCHAR2 DEFAULT NULL,
16 p_pk_col3_type IN VARCHAR2 DEFAULT NULL,
17 p_pk_col4_type IN VARCHAR2 DEFAULT NULL,
18 p_pk_cols_str IN VARCHAR2,
19 p_pk_cols_tbl_str IN VARCHAR2,
20 p_update_where_str IN VARCHAR2,
21 p_delete IN BOOLEAN DEFAULT NULL
22 ) IS
23
24 BEGIN
25
26 IF p_delete IS NULL OR p_delete=FALSE THEN
27 EXECUTE IMMEDIATE
28 'DECLARE ' ||
29 ' TYPE pk_col1_tbl_type is table of '||p_pk_col1_type||' index by binary_integer; ' ||
30 ' TYPE pk_col2_tbl_type is table of '||p_pk_col2_type||' index by binary_integer; ' ||
31 ' TYPE pk_col3_tbl_type is table of '||p_pk_col3_type||' index by binary_integer; ' ||
32 ' TYPE pk_col4_tbl_type is table of '||p_pk_col4_type||' index by binary_integer; ' ||
33 ' pk_col1_tbl pk_col1_tbl_type; ' ||
34 ' pk_col2_tbl pk_col2_tbl_type; ' ||
35 ' pk_col3_tbl pk_col3_tbl_type; ' ||
36 ' pk_col4_tbl pk_col4_tbl_type; ' ||
37 ' cursor tab_cursor is ' ||
38 ' select '||p_pk_cols_str||' from '||p_table_name||' '||p_where||'; ' ||
39 'BEGIN ' ||
40 ' OPEN tab_cursor; ' ||
41 ' LOOP ' ||
42 ' pk_col1_tbl.delete; pk_col2_tbl.delete; pk_col3_tbl.delete; pk_col4_tbl.delete; ' ||
43 ' fetch tab_cursor bulk collect into '||p_pk_cols_tbl_str||' limit '||TO_CHAR(BATCH_SIZE)||'; ' ||
44 ' exit when (tab_cursor%NOTFOUND and pk_col1_tbl.COUNT = 0); ' ||
45 ' FORALL j in pk_col1_tbl.FIRST..pk_col1_tbl.LAST ' ||
46 ' update '||p_table_name||' set deleted_flag = ''1'' where '||p_update_where_str||'; ' ||
47 ' COMMIT; ' ||
48 ' END LOOP; ' ||
49 'END;';
50
51 ELSE
52
53 EXECUTE IMMEDIATE
54 'DECLARE ' ||
55 ' TYPE pk_col1_tbl_type is table of '||p_pk_col1_type||' index by binary_integer; ' ||
56 ' TYPE pk_col2_tbl_type is table of '||p_pk_col2_type||' index by binary_integer; ' ||
57 ' TYPE pk_col3_tbl_type is table of '||p_pk_col3_type||' index by binary_integer; ' ||
58 ' TYPE pk_col4_tbl_type is table of '||p_pk_col4_type||' index by binary_integer; ' ||
59 ' pk_col1_tbl pk_col1_tbl_type; ' ||
60 ' pk_col2_tbl pk_col2_tbl_type; ' ||
61 ' pk_col3_tbl pk_col3_tbl_type; ' ||
62 ' pk_col4_tbl pk_col4_tbl_type; ' ||
63 ' cursor tab_cursor is ' ||
64 ' select '||p_pk_cols_str||' from '||p_table_name||' '||p_where||'; ' ||
65 'BEGIN ' ||
66 ' OPEN tab_cursor; ' ||
67 ' LOOP ' ||
68 ' pk_col1_tbl.delete; pk_col2_tbl.delete; pk_col3_tbl.delete; pk_col4_tbl.delete; ' ||
69 ' fetch tab_cursor bulk collect into '||p_pk_cols_tbl_str||' limit '||TO_CHAR(BATCH_SIZE)||'; ' ||
70 ' exit when (tab_cursor%NOTFOUND and pk_col1_tbl.COUNT = 0); ' ||
71 ' FORALL j in pk_col1_tbl.FIRST..pk_col1_tbl.LAST ' ||
72 ' delete from '||p_table_name||' where '||p_update_where_str||'; ' ||
73 ' COMMIT; ' ||
74 ' END LOOP; ' ||
75 'END;';
76
77 END IF;
78
79 END exec_plsql_block;
80
81 PROCEDURE exec_it
82 (
83 p_table_name IN VARCHAR2,
84 p_where IN VARCHAR2,
85 p_pk_col1 IN VARCHAR2,
86 p_pk_col2 IN VARCHAR2 DEFAULT NULL,
87 p_pk_col3 IN VARCHAR2 DEFAULT NULL,
88 p_pk_col4 IN VARCHAR2 DEFAULT NULL,
89 p_delete IN BOOLEAN DEFAULT NULL) IS
90
91 l_pk_cols_str VARCHAR2(32000);
92 l_pk_cols_tbl_str VARCHAR2(32000);
93 l_update_where_str VARCHAR2(32000);
94 l_pk_col1_type VARCHAR2(255);
95 l_pk_col2_type VARCHAR2(255);
96 l_pk_col3_type VARCHAR2(255);
97 l_pk_col4_type VARCHAR2(255);
98
99 BEGIN
100
101 l_pk_cols_str := p_pk_col1;
102 l_pk_cols_tbl_str := 'pk_col1_tbl';
103 l_update_where_str := ' '||p_pk_col1||'=pk_col1_tbl(j) ';
104
105 l_pk_col1_type := p_table_name||'.'||p_pk_col1||'%TYPE';
106 l_pk_col2_type := 'NUMBER';
107 l_pk_col3_type := 'NUMBER';
108 l_pk_col4_type := 'NUMBER';
109
110 IF p_pk_col2 IS NOT NULL THEN
111 l_pk_cols_str := l_pk_cols_str||','||p_pk_col2;
112 l_pk_cols_tbl_str := 'pk_col1_tbl,pk_col2_tbl';
113 l_update_where_str := l_update_where_str||' and '||p_pk_col2||'=pk_col2_tbl(j) ';
114 l_pk_col2_type := p_table_name||'.'||p_pk_col2||'%TYPE';
115 END IF;
116
117 IF p_pk_col3 IS NOT NULL THEN
118 l_pk_cols_str := l_pk_cols_str||','||p_pk_col3;
119 l_pk_cols_tbl_str := 'pk_col1_tbl,pk_col2_tbl,pk_col3_tbl';
120 l_update_where_str := l_update_where_str||' and '||p_pk_col3||'=pk_col3_tbl(j) ';
121 l_pk_col3_type := p_table_name||'.'||p_pk_col3||'%TYPE';
122 END IF;
123
124 IF p_pk_col4 IS NOT NULL THEN
125 l_pk_cols_str := l_pk_cols_str||','||p_pk_col4;
126 l_pk_cols_tbl_str := 'pk_col1_tbl,pk_col2_tbl,pk_col3_tbl,pk_col4_tbl';
127 l_update_where_str := l_update_where_str||' and '||p_pk_col4||'=pk_col4_tbl(j) ';
128 l_pk_col4_type := p_table_name||'.'||p_pk_col4||'%TYPE';
129 END IF;
130
131 exec_plsql_block
132 (
133 p_table_name => p_table_name,
134 p_where => p_where,
135 p_pk_col1_type => l_pk_col1_type,
136 p_pk_col2_type => l_pk_col2_type,
137 p_pk_col3_type => l_pk_col3_type,
138 p_pk_col4_type => l_pk_col4_type,
139 p_pk_cols_str => l_pk_cols_str,
140 p_pk_cols_tbl_str => l_pk_cols_tbl_str,
141 p_update_where_str => l_update_where_str,
142 p_delete => p_delete
143 );
144
145 EXCEPTION
146 WHEN OTHERS THEN
147 insert into cz_db_logs (caller, message, logtime) values ('cz_base_mgr', 'exec_it failure: '|| p_table_name, sysdate);
148 raise;
149 END exec_it;
150
151 PROCEDURE exec
152 (
153 p_table_name IN VARCHAR2,
154 p_where IN VARCHAR2,
155 p_pk_col1 IN VARCHAR2,
156 p_pk_col2 IN VARCHAR2,
157 p_pk_col3 IN VARCHAR2,
158 p_pk_col4 IN VARCHAR2,
159 p_delete IN BOOLEAN) IS
160
161 BEGIN
162 exec_it(
163 p_table_name => p_table_name,
164 p_where => p_where,
165 p_pk_col1 => p_pk_col1,
166 p_pk_col2 => p_pk_col2,
167 p_pk_col3 => p_pk_col3,
168 p_pk_col4 => p_pk_col4,
169 p_delete => p_delete);
170 END exec;
171
172 PROCEDURE exec
173 (
174 p_table_name IN VARCHAR2,
175 p_where IN VARCHAR2,
176 p_pk_col1 IN VARCHAR2,
177 p_pk_col2 IN VARCHAR2,
178 p_pk_col3 IN VARCHAR2,
179 p_delete IN BOOLEAN) IS
180
181 BEGIN
182 exec_it(
183 p_table_name => p_table_name,
184 p_where => p_where,
185 p_pk_col1 => p_pk_col1,
186 p_pk_col2 => p_pk_col2,
187 p_pk_col3 => p_pk_col3,
188 p_delete => p_delete);
189 END exec;
190
191 PROCEDURE exec
192 (
193 p_table_name IN VARCHAR2,
194 p_where IN VARCHAR2,
195 p_pk_col1 IN VARCHAR2,
196 p_pk_col2 IN VARCHAR2,
197 p_delete IN BOOLEAN) IS
198
199 BEGIN
200 exec_it(
201 p_table_name => p_table_name,
202 p_where => p_where,
203 p_pk_col1 => p_pk_col1,
204 p_pk_col2 => p_pk_col2,
205 p_delete => p_delete);
206 END exec;
207
208 PROCEDURE exec
209 (
210 p_table_name IN VARCHAR2,
211 p_where IN VARCHAR2,
212 p_pk_col1 IN VARCHAR2,
213 p_delete IN BOOLEAN ) IS
214
215 BEGIN
216 exec_it(
217 p_table_name => p_table_name,
218 p_where => p_where,
219 p_pk_col1 => p_pk_col1,
220 p_delete => p_delete);
221 END exec;
222
223 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
224 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
225
226 function canCreate_Sequence return boolean is
227 ret boolean:=FALSE;
228 begin
229
230 dsql('create sequence CZ.tmp_$ start with 1000 increment by 10 nocache');
231 if DSQL_ERROR=1 then
232 ret:=FALSE;
233 else
234 dsql('drop sequence CZ.tmp_$');
235 ret:=TRUE;
236 end if;
237 return ret;
238 exception
239 when OTHERS then
240 return ret;
241 end;
242
243 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
244 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
245
246 function Redo_StartValue
247 (Table_Name in Table_Record) return integer is
248 cur integer;
249 TableName varchar2(30);
250 PKeyName varchar2(30);
251 var_key integer:=-1;
252 var_row integer;
253 sqlText varchar2(90);
254 begin
255 begin
256 TableName:=Table_Name.name;
257 PKeyName:=Table_Name.pk_name;
258 if PKeyName is not null then
259 cur:=dbms_sql.open_cursor;
260 if TableName = 'CZ_RP_ENTRIES' then
261 sqlText := 'select max(' ||PKeyName|| ') from ' || TableName || ' where object_type = ''FLD''';
262 else
263 sqlText := 'select max(' ||PKeyName|| ') from ' || TableName ;
264 end if;
265 dbms_sql.parse(cur,sqlText,dbms_sql.native);
266 dbms_sql.define_column(cur,1,var_key);
267 var_row:=dbms_sql.execute(cur);
268 if dbms_sql.fetch_rows(cur)>0 then
269 dbms_sql.column_value(cur,1,var_key);
270 end if;
271 dbms_sql.close_cursor(cur);
272 end if;
273 exception
274 when OTHERS then
275 LOG_REPORT('<MGR>.Redo_StartValue',SQLERRM);
276 end;
277 --
278 -- As sequence CZ_XFR_RUN_INFOS_S is used by both CZ_XFR_RUN_INFOS or DB_LOGS_RUN_ID
279 -- tables, derive the MAX from both the tables and return the greater of them
280 --
281 if (TableName = 'CZ_XFR_RUN_INFOS') then
282 declare
283 db_logs_run_id integer;
284 begin
285 select nvl(max(run_id), 0) into db_logs_run_id from cz_db_logs;
286 if (db_logs_run_id > var_key) then
287 var_key := db_logs_run_id;
288 end if;
289 end;
290 end if;
291 --
292 return var_key;
293 end;
294
295 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
296 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
297
298 procedure REDO_STATISTICS(Subschema_Name in varchar2) is
299 Proc_Name varchar2(50);
300 Tables Table_List;
301
302 begin
303 get_TABLE_NAMES(Subschema_Name,Tables);
304 Proc_Name:='CZ_'||Subschema_Name||'_MGR.REDO_STATISTICS';
305
306 for i in Tables.First..Tables.Last loop
307 FND_STATS.GATHER_TABLE_STATS(CZ_SCHEMA,Tables(i).name);
308 end loop;
309 exception
310 when OTHERS then
311 LOG_REPORT(Proc_Name,SQLERRM);
312 end;
313
314 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
315 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
316
317 procedure TRIGGERS_ENABLED
318 (Subschema_Name in varchar2,Switch in varchar2) is
319
320 currUser varchar2(30);
321 var_do varchar2(10);
322 Flag boolean;
323 Trigger_Name varchar2(30);
324 WRONG_SWITCH exception;
325 Proc_Name varchar2(50);
326 Tables Table_List;
327
328 begin
329 get_TABLE_NAMES(Subschema_Name,Tables);
330 Proc_Name:='CZ_'||Subschema_Name||'_MGR.TRIGGERS_ENABLED';
331
332 Flag:=TRUE;
333 if upper(Switch)='ON' or Switch='1' or upper(Switch)='Y' or upper(Switch)='YES' or upper(Switch)='TRUE' then
334 var_do:='enable';
335 elsif upper(Switch)='OFF' or Switch='0' or upper(Switch)='N' or upper(Switch)='NO' or upper(Switch)='FALSE' then
336 var_do:='disable';
337 else
338 raise WRONG_SWITCH;
339 end if;
340 for i in Tables.First..Tables.Last loop
341 begin
342 Trigger_Name:=Tables(i).name||'_T1';
343 dsql('alter trigger '||Trigger_Name||' '||var_do);
344 exception
345 when OTHERS then
346 LOG_REPORT(Proc_Name,SQLERRM||' : Error for trigger '||Trigger_Name||' ( table '||Tables(i).name||' )');
347 Flag:=FALSE;
348 end;
349 end loop;
350 if Flag=TRUE and var_do='disable' then
351 LOG_REPORT(Proc_Name,'All triggers were disabled ...');
352 elsif Flag=TRUE and var_do='enable' then
353 LOG_REPORT(Proc_Name,'All triggers were enabled ...');
354 elsif Flag=FALSE and var_do='disable' then
355 LOG_REPORT(Proc_Name,'Error. Triggers were disabled with errors ...');
356 elsif Flag=FALSE and var_do='enable' then
357 LOG_REPORT(Proc_Name,'Error. Triggers were enabled with errors ...');
358 end if;
359 exception
360 when WRONG_SWITCH then
361 LOG_REPORT(Proc_Name,'Error. You should use On/Off ; 1/0 ; Y/N ; Yes/No; True/False as a parameter.');
362 when OTHERS then
363 LOG_REPORT(Proc_Name,SQLERRM);
364 end;
365
366 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
367 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
368
369 procedure CONSTRAINTS_ENABLED
370 (Subschema_Name in varchar2,
371 Switch in varchar2) is
372
373 currUser varchar2(30);
374 var_do varchar2(10);
375 Flag boolean;
376 WRONG_SWITCH exception;
377 Tables Table_List;
378 cursor c1(par_name varchar2) is
379 select * from user_constraints
380 where CONSTRAINT_TYPE='R' and table_name=par_name;
381 var1 c1%rowtype;
382 Proc_Name varchar2(50);
383
384 begin
385 get_TABLE_NAMES(Subschema_Name,Tables);
386 Proc_Name:='CZ_'||Subschema_Name||'_MGR.CONSTRAINTS_ENABLED';
387
388 Flag:=TRUE;
389 if upper(Switch)='ON' or Switch='1' or upper(Switch)='Y' or upper(Switch)='YES' or upper(Switch)='TRUE' then
390 var_do:='enable';
391 elsif upper(Switch)='OFF' or Switch='0' or upper(Switch)='N' or upper(Switch)='NO' or upper(Switch)='FALSE' then
392 var_do:='disable';
393 else
394 raise WRONG_SWITCH;
395 end if;
396
397 for i in Tables.First..Tables.Last loop
398 open c1(Tables(i).name);
399 loop
400 fetch c1 into var1;
401 exit when c1%notfound;
402 begin
403 dsql('alter table '||CZ_SCHEMA||'.'||var1.table_name||' '||var_do||' constraint '||var1.constraint_name);
404 exception
405 when OTHERS then
406 LOG_REPORT('CZ_'||Subschema_Name||'_MGR.Constraints_Enabled','Error for constraint '||var1.constraint_name||' ( table '||var1.table_name);
407 Flag:=FALSE;
408 end;
409 end loop;
410 close c1;
411 end loop;
412
413 if Flag=TRUE and var_do='disable' then
414 LOG_REPORT(Proc_Name,'All FK constraints were disabled ...');
415 elsif Flag=TRUE and var_do='enable' then
416 LOG_REPORT(Proc_Name,'All FK constraints were enabled ...');
417 elsif Flag=FALSE and var_do='disable' then
418 LOG_REPORT(Proc_Name,'FK constraints were disabled with errors ...');
419 elsif Flag=FALSE and var_do='enable' then
420 LOG_REPORT(Proc_Name,'FK constraints were enabled with errors ...');
421 end if;
422 exception
423 when WRONG_SWITCH then
424 LOG_REPORT(Proc_Name,'You should use On/Off ; 1/0 ; Y/N ; Yes/No ; True/False as a parameter.');
425 when OTHERS then
426 LOG_REPORT(Proc_Name,SQLERRM);
427 end;
428
429
430 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
431 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
432
433 procedure REDO_SEQUENCE
434 (SequenceTable in Table_Record,
435 RedoStart_Flag in varchar2,
436 var_incr in varchar2,
440 Sequence_Name varchar2(30);
437 Status_flag OUT NOCOPY varchar2,
438 Proc_Name in varchar2) is
439
441 new_pkey integer;
442
443 begin
444 Sequence_Name:=SequenceTable.name||'_S';
445 if RedoStart_Flag='1' then
446 begin
447 new_pkey:=Redo_StartValue(SequenceTable);
448 if new_pkey <> -1 then
449 begin
450 if canCreate_Sequence then
451 dsql('drop sequence '||CZ_SCHEMA||'.'||Sequence_Name);
452 dsql('create sequence '||CZ_SCHEMA||'.'||Sequence_Name||' start with '||
453 to_char(new_pkey+var_incr)||' increment by '||var_incr||' nocache');
454 end if;
455 end;
456 end if;
457 end;
458 else
459 dsql('alter sequence '||CZ_SCHEMA||'.'||Sequence_Name||' increment by '||var_incr);
460 end if;
461 Status_flag:='0';
462
463 exception
464 when OTHERS then
465 LOG_REPORT(Proc_Name,SQLERRM||' : Error for sequence '||Sequence_Name);
466 Status_flag:='1';
467 end REDO_SEQUENCE;
468
469
470 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
471 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
472
473 procedure REDO_SEQUENCES
474 (Subschema_Name in varchar2,
475 RedoStart_Flag in varchar2,
476 incr in integer default null) is
477
478 cursor c0 is
479 select value from CZ_DB_SETTINGS
480 where setting_id='OracleSequenceIncr' and section_name='SCHEMA';
481 var_value integer;
482 var_incr varchar2(10);
483 Flag varchar2(1);
484 Sequence_Name varchar2(30);
485 Proc_Name varchar2(50);
486 Tables Table_List;
487 new_pkey integer;
488 WRONG_INCR exception;
489
490 begin
491
492 Tables.Delete;
493 get_TABLE_NAMES(Subschema_Name,Tables);
494 Proc_Name:='CZ_'||Subschema_Name||'_MGR.REDO_SEQUENCES';
495
496 dsql('drop sequence CZ.tmp_$');
497
498 Flag:='0';
499 if incr is null then
500 open c0;
501 fetch c0 into var_value;
502 if c0%notfound then
503 close c0;
504 raise WRONG_INCR;
505 else
506 var_incr:=to_char(var_value);
507 close c0;
508 end if;
509 end if;
510 if incr is not null then
511 var_incr:=to_char(incr);
512 end if;
513
514 for i in Tables.First..Tables.Last loop
515 REDO_SEQUENCE(Tables(i), RedoStart_Flag, var_incr, Flag, Proc_Name);
516 end loop;
517
518 if Flag='0' then
519 LOG_REPORT(Proc_Name,'<'||Subschema_Name||'> sequences have increment '||var_incr);
520 else
521 LOG_REPORT(Proc_Name,'New increment is '||var_incr||'. But <'||Subschema_Name||'> sequences were altered with the errors.');
522 end if;
523
524
525 exception
526 when WRONG_INCR then
527 LOG_REPORT(Proc_Name,'Wrong value OracleSequenceIncr in CZ_DB_SETTINGS. ');
528 when OTHERS then
529 LOG_REPORT(Proc_Name,SQLERRM);
530 end REDO_SEQUENCES;
531
532 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
533 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
534
535 procedure CheckSize
536 (num_rec in integer) is
537 begin
538
539 RECORD_COUNTER:=RECORD_COUNTER+num_rec;
540
541 if RECORD_COUNTER>=BATCH_SIZE then
542 commit;
543 RECORD_COUNTER:=0;
544 end if;
545
546 end CheckSize;
547
548 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
549 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
550
551 procedure PURGE
552 (Subschema_Name in varchar2) is
553
554 TYPE col_name_tbl_type is table of varchar2(30) index by binary_integer;
555
556 var_deleted_records integer;
557 cur integer;
558 Tables Table_List;
559 Proc_Name varchar2(50);
560 SKIP_IT exception;
561
562 l_where VARCHAR2(2000) := ' WHERE DELETED_FLAG=''1'' ';
563 l_pk_col_tbl col_name_tbl_type;
564
565 PROCEDURE get_tab_pks (p_table_name IN VARCHAR2,
566 p_pk_col_tbl OUT NOCOPY col_name_tbl_type)
567
568 IS
569
570 CURSOR c1 IS
571 SELECT column_name
572 FROM dba_cons_columns
573 WHERE table_name = p_table_name
574 AND owner='CZ'
575 AND constraint_name LIKE '%PK'
576 ORDER BY position;
577
578 CURSOR c2 IS
579 SELECT column_name
580 FROM dba_ind_columns
581 WHERE table_name = p_table_name
582 AND table_owner='CZ'
583 AND index_name LIKE '%PK'
584 ORDER BY column_position;
585
586 BEGIN
587
588 OPEN c1;
589 FETCH c1 BULK COLLECT INTO p_pk_col_tbl;
590 CLOSE c1;
591
592 IF p_pk_col_tbl.COUNT=0 THEN
593 OPEN c2;
594 FETCH c2 BULK COLLECT INTO p_pk_col_tbl;
595 CLOSE c2;
596 END IF;
597
598 END get_tab_pks;
599
600
601 begin
602 RECORD_COUNTER:=0;
603
604 begin
608 when no_data_found then
605 select TO_NUMBER(VALUE) into CZ_BASE_MGR.BATCH_SIZE from CZ_DB_SETTINGS
606 where upper(setting_id)='BATCHSIZE';
607 exception
609 null;
610 end;
611
612 get_TABLE_NAMES(Subschema_Name,Tables);
613 Proc_Name:='CZ_'||Subschema_Name||'_MGR.PURGE';
614 for i in Tables.First..Tables.Last loop
615 begin
616
617 if Tables(i).name IN ( 'CZ_ATP_REQUESTS'
618 ,'CZ_DB_LOGS'
619 ,'CZ_DB_SETTINGS'
620 ,'CZ_DB_SIZES'
621 ,'CZ_DES_CHART_COLUMNS'
622 ,'CZ_EXP_TMP_LINES'
623 ,'CZ_LCE_TEXTS'
624 ,'CZ_MODEL_USAGES'
625 ,'CZ_PB_CLIENT_APPS'
626 ,'CZ_PB_LANGUAGES'
627 ,'CZ_PB_MODEL_EXPORTS'
628 ,'CZ_PB_TEMP_IDS'
629 ,'CZ_PRICING_STRUCTURES'
630 ,'CZ_PUBLICATION_USAGES'
631 ,'CZ_SERVERS'
632 ,'CZ_TERMINATE_MSGS'
633 ,'CZ_XFR_FIELDS'
634 ,'CZ_XFR_FIELD_REQUIRES'
635 ,'CZ_XFR_RUN_INFOS'
636 ,'CZ_XFR_RUN_RESULTS'
637 ,'CZ_XFR_STATUS_CODES'
638 ,'CZ_XFR_TABLES'
639 ,'CZ_COMBO_FEATURES'
640 ,'CZ_GRID_DEFS'
641 ,'CZ_GRID_COLS'
642 ,'CZ_GRID_CELLS') then
643 raise SKIP_IT;
644 end if;
645 if Tables(i).name='CZ_PS_NODES' then
646 --Resolve problems with logically deleted ps nodes
647
648 -- NOTES:
649 -- 1. This change relies on ps_nodes to be done after devl_projects
650 -- 2. With this change ps_nodes will only be deleted if they belong to
651 -- deleted projects. Other logically deleted ps_nodes will be left around.
652 exec('cz_ps_nodes', 'where deleted_flag = ''1'' and not exists (select 1 from '||
653 'cz_devl_projects where '||
654 'devl_project_id = cz_ps_nodes.devl_project_id and deleted_flag = ''0'')',
655 'ps_node_id', TRUE);
656
657 /*
658 exec('delete from cz_ps_nodes a where a.deleted_flag = ''1'' and not exists (select NULL from cz_expression_nodes b where a.ps_node_id = b.ps_node_id '||
659 'and b.deleted_flag = ''0'') and a.deleted_flag = ''1'' and '||
660 ' ps_node_id not in'||
661 '(select distinct ps_node_id from '||
662 ' (select PRIMARY_OPT_ID as ps_node_id from CZ_DES_CHART_CELLS '||
663 ' where deleted_flag=''0'' '||
664 ' UNION '||
665 ' select SECONDARY_OPT_ID as ps_node_id from CZ_DES_CHART_CELLS '||
666 ' where deleted_flag=''0'' '||
667 ' UNION '||
668 ' select SECONDARY_FEAT_EXPL_ID as ps_node_id from CZ_DES_CHART_CELLS '||
669 ' where deleted_flag=''0'' '||
670 ' UNION '||
671 ' select FEATURE_ID as ps_node_id from CZ_DES_CHART_FEATURES '||
672 ' where deleted_flag=''0'' '||
673 ' UNION '||
674 ' select PS_NODE_ID as ps_node_id from CZ_GRID_CELLS '||
675 ' where deleted_flag=''0''))');
676
677 for k in (select ps_node_id from CZ_EXPRESSION_NODES where deleted_flag='0') loop
678 update cz_ps_nodes set parent_id=0 where ps_node_id=k.ps_node_id and deleted_flag='1';
679 CheckSize(SQL%ROWCOUNT);
680 end loop; */
681
682 else
683
684 get_tab_pks(Tables(i).name, l_pk_col_tbl);
685
686 If l_pk_col_tbl.count = 0 AND Tables(i).name <> 'CZ_PSNODE_PROPCOMPAT_GENS' THEN
687 RAISE_APPLICATION_ERROR (-20001,'Table '||Tables(i).name||' has no PK defined.');
688 elsif l_pk_col_tbl.count = 1 THEN
689 exec(
690 p_table_name => Tables(i).name,
691 p_where => l_where,
692 p_pk_col1 => l_pk_col_tbl(1),
693 p_delete => TRUE);
694
695 elsif l_pk_col_tbl.count = 2 THEN
696 exec(
697 p_table_name => Tables(i).name,
698 p_where => l_where,
699 p_pk_col1 => l_pk_col_tbl(1),
700 p_pk_col2 => l_pk_col_tbl(2),
701 p_delete => TRUE);
702
703 elsif l_pk_col_tbl.count = 3 THEN
704 exec(
705 p_table_name => Tables(i).name,
706 p_where => l_where,
707 p_pk_col1 => l_pk_col_tbl(1),
708 p_pk_col2 => l_pk_col_tbl(2),
709 p_pk_col3 => l_pk_col_tbl(3),
710 p_delete => TRUE);
711
712 elsif l_pk_col_tbl.count = 4 THEN
713 exec(
714 p_table_name => Tables(i).name,
718 p_pk_col3 => l_pk_col_tbl(3),
715 p_where => l_where,
716 p_pk_col1 => l_pk_col_tbl(1),
717 p_pk_col2 => l_pk_col_tbl(2),
719 p_pk_col4 => l_pk_col_tbl(4),
720 p_delete => TRUE);
721
722 end if;
723
724 end if;
725 exception
726 when SKIP_IT then
727 null;
728 end;
729 commit;
730 end loop;
731 commit;
732 exception
733 when NO_DATA_FOUND then
734 LOG_REPORT(Proc_Name,SQLERRM);
735 end;
736
737 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
738 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
739
740 procedure RESET_CLEAR
741 (Subschema_Name in varchar2) is
742
743 i integer;
744 Tables Table_List;
745 Proc_Name varchar2(50);
746
747 begin
748 get_TABLE_NAMES(Subschema_Name,Tables);
749 Proc_Name:='CZ_'||Subschema_Name||'_MGR.RESET_CLEAR';
750 delete from CZ_PS_NODES where ps_node_id>0;
751 delete from CZ_DEVL_PROJECTS where devl_project_id>0;
752 i:=1;
753 loop
754 if Tables(i).name not in ('CZ_PS_NODES','CZ_DEVL_PROJECTS') then
755 dsql('DELETE FROM '||Tables(i).name);
756 end if;
757 i:=i+1;
758 if i>Tables.Last then
759 exit;
760 end if;
761 end loop;
762 commit;
763 exception
764 when NO_DATA_FOUND then
765 LOG_REPORT(Proc_Name,SQLERRM);
766 when OTHERs then
767 LOG_REPORT(Proc_Name,SQLERRM);
768 end;
769
770 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
771 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
772
773 procedure MODIFIED
774 (Subschema_Name in varchar2,
775 AS_OF IN OUT NOCOPY date) is
776
777 cur integer;
778 var_date date;
779 var_row integer;
780 max_date date;
781 Proc_Name varchar2(50);
782 Tables Table_List;
783
784 begin
785 get_TABLE_NAMES(Subschema_Name,Tables);
786 Proc_Name:='CZ_'||Subschema_Name||'_MGR.MODIFIED';
787 for i in Tables.First..Tables.Last loop
788 begin
789 cur:=dbms_sql.open_cursor;
790 dbms_sql.parse(cur,'select max(LAST_UPDATE_DATE) from '||Tables(i).name,dbms_sql.native);
791 dbms_sql.define_column(cur,1,var_date);
792 var_row:=dbms_sql.execute(cur);
793 if dbms_sql.fetch_rows(cur)>0 then
794 dbms_sql.column_value(cur,1,var_date);
795 end if;
796 dbms_sql.close_cursor(cur);
797 if var_date>max_date then
798 max_date:=var_date;
799 end if;
800 exception
801 when OTHERS then
802 if dbms_sql.is_open(cur) then
803 dbms_sql.close_cursor(cur);
804 end if;
805 end;
806 end loop;
807 AS_OF:=max_date;
808 exception
809 when NO_DATA_FOUND then
810 LOG_REPORT(Proc_Name,SQLERRM);
811 when OTHERs then
812 LOG_REPORT(Proc_Name,SQLERRM);
813 end;
814
815 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
816 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
817
818 procedure dsql
819 (stmt in varchar2) is
820 cur integer;
821 var_tempo integer;
822 begin
823 cur:=dbms_sql.open_cursor;
824 dbms_sql.parse(cur,stmt,dbms_sql.native);
825 var_tempo:=dbms_sql.execute(cur);
826 DSQL_ERROR:=0;
827 dbms_sql.close_cursor(cur);
828 exception
829 when OTHERS then
830 LOG_REPORT('<MGR>.dsql',SQLERRM);
831 DSQL_ERROR:=1;
832 end;
833
834 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
835 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
836
837 procedure exec
838 (stmt in varchar2) is
839 cur integer;
840 var_tempo integer;
841 var_stmt varchar2(10000);
842 begin
843 var_stmt:=stmt||' AND rownum<'||to_char(BATCH_SIZE);
844 cur:=dbms_sql.open_cursor;
845 dbms_sql.parse(cur,var_stmt,dbms_sql.native);
846
847 loop
848 var_tempo:=dbms_sql.execute(cur);
849 if var_tempo>0 then
850 commit;
851 else
852 exit;
853 end if;
854 end loop;
855 dbms_sql.close_cursor(cur);
856 exception
857 when OTHERS then
858 LOG_REPORT('CZ_BASE_MGR.exec','Error : '||SQLERRM||' : statement : "'||var_stmt||'"');
859 end;
860
861 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
862 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
863
864 procedure LOG_REPORT
865 (err in varchar2,
866 str in varchar2) is
867 begin
868 -- ret:=CZ_UTILS.REPORT(str,1,err,11276);
869 cz_utils.log_report(err, null, null, str, fnd_log.LEVEL_ERROR);
870 end;
871
872 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
873 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
874
875 procedure get_TABLE_NAMES
876 (SubSchema in varchar2,
880
877 Tables IN OUT NOCOPY Table_List) is
878
879 begin
881 if upper(SubSchema)='PS' then
882 Tables(1).name:='CZ_DEVL_PROJECTS'; Tables(1).pk_name:='DEVL_PROJECT_ID';
883 Tables(2).name:='CZ_DEVL_PRJ_USER_GROUPS'; Tables(2).pk_name:=NULL;
884 Tables(3).name:='CZ_FUNC_COMP_SPECS'; Tables(3).pk_name:='FUNC_COMP_ID';
885 Tables(4).name:='CZ_FUNC_COMP_REFS'; Tables(4).pk_name:=NULL;
886 Tables(5).name:='CZ_PS_PROP_VALS'; Tables(5).pk_name:=NULL;
887 Tables(6).name:='CZ_PSNODE_PROPCOMPAT_GENS'; Tables(6).pk_name:='COMPAT_RUN';
888 Tables(7).name:='CZ_INTL_TEXTS'; Tables(7).pk_name:='INTL_TEXT_ID';
889 Tables(8).name:='CZ_LOCALIZED_TEXTS'; Tables(8).pk_name:='INTL_TEXT_ID';
890 Tables(9).name:='CZ_LOCALES'; Tables(9).pk_name:='LOCALE_ID';
891 Tables(10).name:='CZ_RULES'; Tables(10).pk_name:='RULE_ID';
892 Tables(11).name:='CZ_POPULATORS'; Tables(11).pk_name:='POPULATOR_ID';
893 Tables(12).name:='CZ_FILTER_SETS'; Tables(12).pk_name:='FILTER_SET_ID';
894 Tables(13).name:='CZ_EXPRESSIONS'; Tables(13).pk_name:='EXPRESS_ID';
895 Tables(14).name:='CZ_EXPRESSION_NODES'; Tables(14).pk_name:='EXPR_NODE_ID';
896 Tables(15).name:='CZ_COMBO_FEATURES'; Tables(15).pk_name:='FEATURE_ID';
897 Tables(16).name:='CZ_GRID_DEFS'; Tables(16).pk_name:='GRID_ID';
898 Tables(17).name:='CZ_GRID_COLS'; Tables(17).pk_name:='GRID_COL_ID';
899 Tables(18).name:='CZ_GRID_CELLS'; Tables(18).pk_name:='GRID_CELL_ID';
900 Tables(19).name:='CZ_SUB_CON_SETS'; Tables(19).pk_name:='SUB_CONS_ID';
901 Tables(20).name:='CZ_POPULATOR_MAPS'; Tables(20).pk_name:='POP_MAP_ID';
902 Tables(21).name:='CZ_RULE_FOLDERS'; Tables(21).pk_name:='RULE_FOLDER_ID';
903 Tables(22).name:='CZ_DES_CHART_CELLS'; Tables(22).pk_name:=NULL;
904 Tables(23).name:='CZ_DES_CHART_FEATURES'; Tables(23).pk_name:=NULL;
905 Tables(24).name:='CZ_PS_NODES'; Tables(24).pk_name:='PS_NODE_ID';
906 Tables(25).name:='CZ_MODEL_REF_EXPLS'; Tables(25).pk_name:='MODEL_REF_EXPL_ID';
907 Tables(26).name:='CZ_ARCHIVES'; Tables(26).pk_name:=NULL;
908 Tables(27).name:='CZ_ARCHIVE_REFS'; Tables(27).pk_name:=NULL;
909 Tables(28).name:='CZ_RP_ENTRIES'; Tables(28).pk_name:=NULL;
910 end if;
911
912 if upper(SubSchema)='PB' then
913 Tables(1).name:='CZ_MODEL_PUBLICATIONS'; Tables(1).pk_name:='PUBLICATION_ID';
914 Tables(2).name:='CZ_PB_CLIENT_APPS'; Tables(2).pk_name:=NULL;
915 Tables(3).name:='CZ_PUBLICATION_USAGES'; Tables(3).pk_name:=NULL;
916 Tables(4).name:='CZ_SERVERS'; Tables(4).pk_name:='SERVER_LOCAL_ID';
917 Tables(5).name:='CZ_PB_MODEL_EXPORTS'; Tables(5).pk_name:='EXPORT_ID';
918 Tables(6).name:='CZ_EFFECTIVITY_SETS'; Tables(6).pk_name:='EFFECTIVITY_SET_ID';
919 Tables(7).name:='CZ_MODEL_USAGES'; Tables(7).pk_name:='MODEL_USAGE_ID';
920 end if;
921
922 if upper(SubSchema)='GN' then
923 Tables(1).name:='CZ_DB_LOGS';Tables(1).pk_name:=null;
924 Tables(2).name:='CZ_DB_SETTINGS';Tables(2).pk_name:=null;
925 end if;
926
927 if upper(SubSchema)='XF' then
928 --Tables(1).name:='CZ_XFR_PROJECT_BILLS'; Tables(1).pk_name:=null;
929 -- Tables(1).name:='CZ_XFR_PRICE_LISTS'; Tables(1).pk_name:=null;
930 Tables(1).name:='CZ_XFR_TABLES'; Tables(1).pk_name:=null;
931 Tables(2).name:='CZ_XFR_FIELDS'; Tables(2).pk_name:=null;
932 Tables(3).name:='CZ_XFR_RUN_INFOS'; Tables(3).pk_name:='RUN_ID';
933 Tables(4).name:='CZ_XFR_RUN_RESULTS'; Tables(4).pk_name:=null;
934 Tables(5).name:='CZ_XFR_STATUS_CODES'; Tables(5).pk_name:=null;
935 Tables(6).name:='CZ_XFR_FIELD_REQUIRES'; Tables(6).pk_name:=null;
936 end if;
937 if upper(SubSchema)='PR' then
938 Tables(1).name:='CZ_PRICE_GROUPS'; Tables(1).pk_name:='PRICE_GROUP_ID';
939 Tables(2).name:='CZ_PRICES'; Tables(2).pk_name:='PRICE_GROUP_ID';
940 end if;
941 if upper(SubSchema)='OM' then
942 Tables(1).name:='CZ_OPPORTUNITY_HDRS'; Tables(1).pk_name:='OPPORTUNITY_HDR_ID';
943 Tables(2).name:='CZ_OPP_HDR_CONTACTS'; Tables(2).pk_name:=NULL;
944 Tables(3).name:='CZ_CONTACTS'; Tables(3).pk_name:='CONTACT_ID';
945 Tables(4).name:='CZ_CUSTOMERS'; Tables(4).pk_name:='CUSTOMER_ID';
946 Tables(5).name:='CZ_ADDRESSES'; Tables(5).pk_name:='ADDRESS_ID';
947 Tables(6).name:='CZ_ADDRESS_USES'; Tables(6).pk_name:='ADDRESS_USE_ID';
948 Tables(7).name:='CZ_CUSTOMER_END_USERS'; Tables(7).pk_name:=NULL;
949 Tables(8).name:='CZ_END_USERS'; Tables(8).pk_name:='END_USER_ID';
950 Tables(9).name:='CZ_END_USER_GROUPS'; Tables(9).pk_name:=NULL;
951 Tables(10).name:='CZ_USER_GROUPS'; Tables(10).pk_name:='USER_GROUP_ID';
952 end if;
953 if upper(SubSchema)='LC' then
954 Tables(1).name:='CZ_LCE_HEADERS'; Tables(1).pk_name:='LCE_HEADER_ID';
955 Tables(2).name:='CZ_LCE_LOAD_SPECS'; Tables(2).pk_name:= null;
956 Tables(3).name:='CZ_LCE_LINES'; Tables(3).pk_name:='LCE_LINE_ID';
957 Tables(4).name:='CZ_LCE_OPERANDS'; Tables(4).pk_name:='OPERAND_SEQ';
961 Tables(1).name:='CZ_UI_DEFS'; Tables(1).pk_name:='UI_DEF_ID';
958 Tables(5).name:='CZ_LCE_TEXTS'; Tables(5).pk_name:='LCE_HEADER_ID';
959 end if;
960 if upper(SubSchema)='UI' then
962 Tables(2).name:='CZ_UI_NODES'; Tables(2).pk_name:='UI_NODE_ID';
963 Tables(3).name:='CZ_UI_PROPERTIES'; Tables(3).pk_name:=null;
964 Tables(4).name:='CZ_UI_NODE_PROPS'; Tables(4).pk_name:=null;
965 Tables(5).name:='CZ_UI_PAGES'; Tables(5).pk_name:=null;
966 Tables(6).name:='CZ_UI_PAGE_REFS'; Tables(6).pk_name:=null;
967 Tables(7).name:='CZ_UI_PAGE_SETS'; Tables(7).pk_name:=null;
968 Tables(8).name:='CZ_UI_PAGE_ELEMENTS'; Tables(8).pk_name:=null;
969 Tables(9).name:='CZ_UI_REFS'; Tables(9).pk_name:=null;
970 Tables(10).name:='CZ_UI_ACTIONS'; Tables(10).pk_name:=null;
971 Tables(11).name:='CZ_UI_TEMPLATES'; Tables(11).pk_name:=null;
972 Tables(12).name:='CZ_UI_REF_TEMPLATES'; Tables(12).pk_name:=null;
973 Tables(13).name:='CZ_UI_IMAGES'; Tables(13).pk_name:=null;
974 Tables(14).name:='CZ_UI_CONT_TYPE_TEMPLS'; Tables(14).pk_name:=null;
975 end if;
976 if upper(SubSchema)='QC' then
977 Tables(1).name:='CZ_PROPOSAL_HDRS'; Tables(1).pk_name:='PROPOSAL_HDR_ID';
978 Tables(2).name:='CZ_PROP_QUOTE_HDRS'; Tables(2).pk_name:=NULL;
979 Tables(3).name:='CZ_QUOTE_HDRS'; Tables(3).pk_name:='QUOTE_HDR_ID';
980 Tables(4).name:='CZ_QUOTE_ORDERS'; Tables(4).pk_name:=NULL;
981 Tables(5).name:='CZ_QUOTE_MAIN_ITEMS'; Tables(5).pk_name:=NULL;
982 Tables(6).name:='CZ_QUOTE_SPARES'; Tables(6).pk_name:='SEQ_NUMBER';
983 Tables(7).name:='CZ_QUOTE_SPECIAL_ITEMS'; Tables(7).pk_name:='SEQ_NUMBER';
984 Tables(8).name:='CZ_SPARES_SPECIALS'; Tables(8).pk_name:='PACKAGE_SEQ';
985 Tables(9).name:='CZ_DRILL_DOWN_ITEMS'; Tables(9).pk_name:='DD_SEQ_NBR';
986 Tables(10).name:='CZ_CONFIG_HDRS'; Tables(10).pk_name:='CONFIG_HDR_ID';
987 Tables(11).name:='CZ_CONFIG_INPUTS'; Tables(11).pk_name:='CONFIG_INPUT_ID';
988 Tables(12).name:='CZ_CONFIG_ITEMS'; Tables(12).pk_name:='CONFIG_ITEM_ID';
989 Tables(13).name:='CZ_CONFIG_MESSAGES'; Tables(13).pk_name:='NULL';
990 Tables(14).name:='CZ_CONFIG_ATTRIBUTES'; Tables(14).pk_name:='NULL';
991 Tables(15).name:='CZ_CONFIG_EXT_ATTRIBUTES'; Tables(15).pk_name:='NULL';
992 end if;
993 if upper(SubSchema)='IM' then
994 Tables(1).name:='CZ_ITEM_MASTERS'; Tables(1).pk_name:='ITEM_ID';
995 Tables(2).name:='CZ_ITEM_TYPES'; Tables(2).pk_name:='ITEM_TYPE_ID';
996 Tables(3).name:='CZ_PROPERTIES'; Tables(3).pk_name:='PROPERTY_ID';
997 Tables(4).name:='CZ_ITEM_TYPE_PROPERTIES'; Tables(4).pk_name:=NULL;
998 Tables(5).name:='CZ_ITEM_PROPERTY_VALUES'; Tables(5).pk_name:=NULL;
999 Tables(6).name:='CZ_REL_TYPES'; Tables(6).pk_name:='REL_TYPE_ID';
1000 Tables(7).name:='CZ_ITEM_PARENTS'; Tables(7).pk_name:='PARENT_ITEM_ID';
1001 end if;
1002
1003 end;
1004 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1005 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1006 begin
1007 select TABLE_OWNER into CZ_SCHEMA from user_synonyms
1008 where SYNONYM_NAME='CZ_DEVL_PROJECTS';
1009 exception
1010 when NO_DATA_FOUND then
1011 CZ_SCHEMA:=USER;
1012 when OTHERS then
1013 LOG_REPORT('<MGR>',SQLERRM);
1014 end;