[Home] [Help]
PACKAGE BODY: APPS.CN_SCA_UTL_PVT
Source
1 PACKAGE BODY CN_SCA_UTL_PVT AS
2 -- $Header: cnvscaub.pls 120.1 2005/09/15 14:46:56 rchenna noship $
3 -- +======================================================================+
4 -- | Copyright (c) 1994 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +======================================================================+
8 --
9 -- Package Name
10 -- CN_SCA_UTL_PVT
11 -- Purpose
12 -- This package has utilities and being used by other Rules Engine
13 -- PL/SQL packages.
14 -- History
15 -- 06/23/03 Rao.Chenna Created
16
17 --+
18 --+ Global Variables
19 --+
20
21 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SCA_UTL_PVT';
22 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvscaub.pls';
23 g_cn_debug VARCHAR2(1) := fnd_profile.value('CN_DEBUG');
24
25 PROCEDURE debugmsg(msg VARCHAR2) IS
26 BEGIN
27
28 IF g_cn_debug = 'Y' THEN
29 cn_message_pkg.debug(substr(msg,1,254));
30 END IF;
31
32 END debugmsg;
33
34 FUNCTION get_valuset_query (l_valueset_id NUMBER) RETURN VARCHAR2 IS
35 l_valueset_r fnd_vset.valueset_r;
36 l_table_r fnd_vset.table_r;
37 l_valueset_dr fnd_vset.valueset_dr;
38 l_select_stmt VARCHAR2(4000);
39 l_select VARCHAR2(4000);
40 l_from VARCHAR2(4000);
41 l_where VARCHAR2(4000);
42 BEGIN
43 -- get the SQL statement for the record qroup
44 fnd_vset.get_valueset(l_valueset_id, l_valueset_r, l_valueset_dr);
45 l_select := l_valueset_r.table_info.value_column_name ||' column_name, ' ||
46 NVL(l_valueset_r.table_info.id_column_name, 'null') || ' column_id, ' ||
47 NVL(l_valueset_r.table_info.meaning_column_name, 'null') || ' column_meaning';
48
49 l_from := l_valueset_r.table_info.table_name;
50
51 IF l_valueset_r.table_info.where_clause IS NULL THEN
52 l_where := ' ';
53 ELSE
54 l_where := l_valueset_r.table_info.where_clause;
55 END IF;
56
57 l_select_stmt := 'Select ' || l_select || ' from ' || l_from || ' ' || l_where ;
58
59 return l_select_stmt;
60 END;
61 --
62 PROCEDURE manage_indexes(
63 p_transaction_source IN VARCHAR2,
64 p_org_id IN NUMBER,
65 x_return_status OUT NOCOPY VARCHAR2) IS
66
67 --+
68 --+ Local Variables Section
69 --+
70
71 v_statement VARCHAR2(2000);
72 s_statement VARCHAR2(2000);
73 l_table_tablespace VARCHAR2(100);
74 l_idx_tablespace VARCHAR2(100);
75 l_ora_username VARCHAR2(100);
76 l_app_short_name VARCHAR2(20) := 'CN';
77 l_trans_idx_name VARCHAR2(30);
78 l_cn_schema VARCHAR2(200);
79 l_schema_return BOOLEAN;
80 l_status VARCHAR2(2);
81 l_industry VARCHAR2(2);
82 l_oracle_schema VARCHAR2(32);
83
84
85 --+
86 --+ Cursor Definition
87 --+
88
89 CURSOR attr_cur IS
90 SELECT /*+ ORDERED INDEX(b) */ a.src_column_name
91 FROM cn_sca_rule_attributes_all_b a,
92 cn_sca_conditions b
93 WHERE a.sca_rule_attribute_id = b.sca_rule_attribute_id
94 AND a.transaction_source = p_transaction_source
95 AND a.enabled_flag = 'Y'
96 AND a.org_id = p_org_id
97 GROUP BY a.src_column_name;
98
99 CURSOR index_cur(l_table_owner VARCHAR2) IS
100 SELECT aidx.owner, aidx.index_name
101 FROM all_indexes aidx
102 WHERE aidx.table_name = 'CN_SCA_HEADERS_INTERFACE_ALL'
103 AND aidx.table_owner = l_table_owner
104 AND aidx.index_name LIKE 'CN_SCA_HEADERS_INTERFACE_A%';
105
106 BEGIN
107
108 x_return_status := FND_API.G_RET_STS_SUCCESS;
109
110 SELECT application_short_name
111 INTO l_cn_schema
112 FROM fnd_application
113 WHERE application_id = 283;
114
115 l_schema_return := fnd_installation.get_app_info(
116 l_cn_schema,
117 l_status,
118 l_industry,
119 l_oracle_schema);
120 debugmsg('l_oracle_schema value: '||l_oracle_schema);
121
122 --+
123 --+ Delete existing indexes from cn_sca_headers_interface_all table based
124 --+ on the cursor.
125 --+
126
127 debugmsg('dropping existing indexes ...');
128
129 BEGIN
130
131 FOR idx IN index_cur(l_oracle_schema)
132 LOOP
133
134 EXECUTE IMMEDIATE 'DROP INDEX '||idx.owner||'.'|| idx.index_name;
135
136 debugmsg('Dropped index :'||idx.index_name);
137
138 END LOOP;
139
140 EXCEPTION
141 WHEN OTHERS THEN
142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
143 debugmsg('Error while dropping the existing indexes');
144 debugmsg('Oracle Error: '||SQLERRM);
145 RAISE;
146 END;
147
148 debugmsg('Dropped all indexes ...');
149
150 --+
151 --+ Creating new indexes based on the Rule Attributes used in a given
152 --+ Transaction Source
153 --+
154
155 SELECT i.tablespace, i.index_tablespace, u.oracle_username
156 INTO l_table_tablespace, l_idx_tablespace, l_ora_username
157 FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
158 WHERE a.application_short_name = l_cn_schema
159 AND a.application_id = i.application_id
160 AND u.oracle_id = i.oracle_id;
161
162 s_statement := s_statement || ' TABLESPACE ' || l_idx_tablespace ;
163 s_statement := s_statement || ' STORAGE(INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED ';
164 s_statement := s_statement || ' PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT) ';
165 s_statement := s_statement || ' PCTFREE 10 INITRANS 10 MAXTRANS 255 NOLOGGING PARALLEL ';
166 s_statement := s_statement || ' COMPUTE STATISTICS ';
167
168 debugmsg('Creating new indexes ... ');
169 debugmsg('Schema Name: '||l_ora_username);
170
171 BEGIN
172 FOR rec IN attr_cur
173 LOOP
174
175 l_trans_idx_name := 'CN_SCA_HEADERS_INTERFACE_A'||SUBSTR(rec.src_column_name,10);
176 v_statement := ' CREATE INDEX '||l_ora_username||'.'||l_trans_idx_name ||
177 ' ON CN_SCA_HEADERS_INTERFACE_ALL('||rec.src_column_name||') ';
178 v_statement := v_statement || s_statement;
179
180 EXECUTE IMMEDIATE v_statement;
181
182 EXECUTE IMMEDIATE 'ALTER INDEX '||l_ora_username||'.'||l_trans_idx_name||' LOGGING NOPARALLEL';
183
184 debugmsg('Created index :'||l_trans_idx_name);
185
186 END LOOP;
187
188 debugmsg('Created new indexes ... ');
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
193 debugmsg('Error while creating new indexes on Attribute columns');
194 debugmsg('Oracle Error :'||SQLERRM);
195 RAISE;
196 END;
197
198 --+
199 --+ Analyze CN_SCA_HEADERS_INTERFACE_ALL table and associated indexes
200 --+
201
202 EXCEPTION
203 WHEN OTHERS THEN
204 debugmsg('Error in manage_indexes subprogram');
205 END;
206 --
207 END CN_SCA_UTL_PVT;