[Home] [Help]
PACKAGE BODY: APPS.MSC_ANALYSE_TABLES_PK
Source
1 PACKAGE BODY MSC_ANALYSE_TABLES_PK AS
2 /* $Header: MSCANTBB.pls 115.9 2004/02/11 11:11:27 ashaj ship $ */
3
4 SYS_YES CONSTANT NUMBER := 1;
5 SYS_NO CONSTANT NUMBER := 2;
6
7 PROCEDURE analyse IS
8
9 CURSOR tab_list(p_owner varchar2) is
10 SELECT table_name,
11 partitioned
12 FROM all_tables
13 where owner=p_owner
14 and table_name like 'MSC%'
15 and temporary <> 'Y';
16
17 var_table_name VARCHAR2(30);
18 var_partitioned VARCHAR2(3);
19
20 v_applsys_schema VARCHAR2(32);
21 lv_retval boolean;
22 lv_dummy1 varchar2(32);
23 lv_dummy2 varchar2(32);
24 BEGIN
25 lv_retval := FND_INSTALLATION.GET_APP_INFO ('MSC', lv_dummy1, lv_dummy2, v_applsys_schema);
26
27 OPEN tab_list(v_applsys_schema);
28
29 LOOP
30 FETCH tab_list INTO var_table_name,
31 var_partitioned;
32
33 EXIT WHEN tab_list%NOTFOUND;
34
35 IF var_partitioned='YES' THEN
36 fnd_stats.gather_table_stats(
37 v_applsys_schema,
38 var_table_name,
39 granularity => 'PARTITION');
40 ELSE
41 fnd_stats.gather_table_stats(v_applsys_schema, var_table_name);
42 END IF;
43
44 END LOOP;
45
46 CLOSE tab_list;
47
48 END analyse;
49
50
51 PROCEDURE analyse_table( p_table_name IN VARCHAR2,
52 p_instance_id IN NUMBER,
53 p_plan_id IN NUMBER) IS
54
55 CURSOR tab_list( cv_table_name IN VARCHAR2, cv_owner IN VARCHAR2) is
56 SELECT table_name,
57 partitioned
58 FROM all_tables
59 where owner=cv_owner
60 and table_name= UPPER(cv_table_name)
61 and temporary <> 'Y';
62
63 var_table_name VARCHAR2(30);
64 var_partitioned VARCHAR2(3);
65 var_partition_name VARCHAR2(30);
66 var_is_plan NUMBER;
67
68 var_return_status VARCHAR2(2048);
69 var_msg_data VARCHAR2(2048);
70
71 v_applsys_schema VARCHAR2(32);
72 lv_retval boolean;
73 lv_dummy1 varchar2(32);
74 lv_dummy2 varchar2(32);
75
76 BEGIN
77 lv_retval := FND_INSTALLATION.GET_APP_INFO ('MSC', lv_dummy1, lv_dummy2,v_applsys_schema);
78 OPEN tab_list( p_table_name,v_applsys_schema);
79
80 FETCH tab_list INTO var_table_name,
81 var_partitioned;
82
83 IF tab_list%NOTFOUND THEN RETURN; END IF;
84
85 IF var_partitioned='YES' THEN
86 IF p_instance_id IS NULL AND
87 p_plan_id IS NULL THEN
88
89 /* analyse all the partitions */
90 fnd_stats.gather_table_stats(
91 v_applsys_schema,
92 var_table_name,
93 granularity => 'PARTITION');
94
95 ELSE
96
97 IF p_plan_id= -1 OR p_plan_id IS NULL THEN
98 var_is_plan:= SYS_NO;
99 ELSE
100 var_is_plan:= SYS_YES;
101 END IF;
102
103 msc_manage_plan_partitions.get_partition_name
104 ( p_plan_id,
105 p_instance_id,
106 p_table_name,
107 var_is_plan,
108 var_partition_name,
109 var_return_status,
110 var_msg_data);
111
112 fnd_stats.gather_table_stats(
113 v_applsys_schema,
114 var_table_name,
115 10,
116 4,
117 var_partition_name);
118
119 END IF;
120 ELSE
121 fnd_stats.gather_table_stats(v_applsys_schema, var_table_name, 10, 4);
122 END IF;
123
124 CLOSE tab_list;
125
126 RETURN;
127
128 EXCEPTION
129
130 WHEN OTHERS THEN
131 RETURN;
132
133 END analyse_table;
134
135 END; -- package