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