[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_SCHEMA_UPGRADE
Source
1 PACKAGE BODY ICX_POR_SCHEMA_UPGRADE AS
2 -- $Header: ICXUPSCB.pls 115.5 2004/03/31 18:47:29 vkartik ship $
3
4 /**
5 **
6 ** Procedure: create_new_categ_descs_tables
7 ** Synopsis : Populate the icx_cat_categories_tl, icx_cat_descriptors_tl,
8 ** and icx_cat_browse_trees table. The source for the above
9 ** tables are icx_por_categories_tl, icx_Por_descriptors_tl and
10 ** icx_por_table_of_contents_tl respectively.
11 **/
12 PROCEDURE create_new_categ_descs_tables IS
13 BEGIN
14 l_loc := 100;
15
16 -- Make a replica(new table) of the categories and descriptors table
17 -- dont do a blind replica, may be section map did not exist in
18 -- the current version of the customer's odf
19 -- Bug#2830088: modified the "not exists" for category check.
20 insert into icx_cat_categories_tl
21 (
22 RT_CATEGORY_ID,
23 LANGUAGE,
24 SOURCE_LANG,
25 CATEGORY_NAME,
26 DESCRIPTION,
27 TYPE,
28 KEY,
29 TITLE,
30 ITEM_COUNT,
31 CREATED_BY,
32 CREATION_DATE,
33 LAST_UPDATED_BY,
34 LAST_UPDATE_DATE,
35 LAST_UPDATE_LOGIN,
36 REQUEST_ID,
37 PROGRAM_APPLICATION_ID,
38 PROGRAM_ID,
39 PROGRAM_UPDATE_DATE,
40 UPPER_CATEGORY_NAME,
41 REBUILD_FLAG,
42 section_map,
43 UPPER_KEY
44 )
45 (
46 select
47 ct1.RT_CATEGORY_ID,
48 ct1.LANGUAGE,
49 ct1.SOURCE_LANG,
50 ct1.CATEGORY_NAME,
51 ct1.DESCRIPTION,
52 ct1.TYPE,
53 ct1.KEY,
54 ct1.TITLE,
55 ct1.ITEM_COUNT,
56 ct1.CREATED_BY,
57 ct1.CREATION_DATE,
58 ct1.LAST_UPDATED_BY,
59 ct1.LAST_UPDATE_DATE,
60 ct1.LAST_UPDATE_LOGIN,
61 ct1.BATCH_JOB_NUM, -- batch_job_num is request id
62 ct1.PROGRAM_APPLICATION_ID,
63 ct1.PROGRAM_ID,
64 ct1.PROGRAM_UPDATE_DATE,
65 ct1.UPPER_CATEGORY_NAME,
66 REBUILD_FLAG,
67 rpad('0', 300, '0'),
68 UPPER_KEY
69 from
70 icx_por_categories_tl ct1
71 where ct1.rt_category_id > 0
72 and not exists (select null from icx_cat_categories_tl ct2
73 where ct1.key = ct2.key
74 and ct1.type = ct2.type
75 and ct1.language = ct2.language)
76 );
77
78 l_loc := 300;
79
80 -- make a replica of icx_por_descriptors_tl
81 -- ignore the validated, class, customization_level,multivalue, section_tag
82 insert into icx_cat_descriptors_tl
83 (
84 RT_DESCRIPTOR_ID,
85 LANGUAGE,
86 SOURCE_LANG,
87 TITLE,
88 DESCRIPTOR_NAME,
89 DESCRIPTION,
90 RT_CATEGORY_ID,
91 TYPE,
92 -- Bug 3092172 fixed by sosingha
93 -- ignore the hidden column as upgrade will fail for pre FPE customers due to non availability of this column. We wont honour this column and hence no need to copy the value for this.
94 -- HIDDEN,
95 SEQUENCE,
96 KEY,
97 DEFAULTVALUE,
98 MULTI_VALUE_TYPE,
99 MULTI_VALUE_KEY,
100 REQUIRED,
101 REFINABLE,
102 SEARCHABLE,
103 CREATED_BY,
104 CREATION_DATE,
105 LAST_UPDATED_BY,
106 LAST_UPDATE_DATE,
107 LAST_UPDATE_LOGIN,
108 REQUEST_ID,
109 PROGRAM_APPLICATION_ID,
110 PROGRAM_ID,
111 PROGRAM_UPDATE_DATE,
112 SEARCH_RESULTS_VISIBLE,
113 ITEM_DETAIL_VISIBLE,
114 REBUILD_FLAG,
115 CLASS
116 )
117 (
118 select
119 des1.RT_DESCRIPTOR_ID,
120 des1.LANGUAGE,
121 des1.SOURCE_LANG,
122 des1.TITLE,
123 des1.DESCRIPTOR_NAME,
124 des1.DESCRIPTION,
125 des1.RT_CATEGORY_ID,
126 des1.TYPE,
127 -- Bug 3092172 fixed by sosingha
128 -- des1.HIDDEN,
129 des1.SEQUENCE,
130 des1.KEY,
131 des1.DEFAULTVALUE,
132 des1.MULTI_VALUE_TYPE,
133 des1.MULTI_VALUE_KEY,
134 des1.REQUIRED,
135 des1.REFINABLE,
136 des1.SEARCHABLE,
137 des1.CREATED_BY,
138 des1.CREATION_DATE,
139 des1.LAST_UPDATED_BY,
140 des1.LAST_UPDATE_DATE,
141 des1.LAST_UPDATE_LOGIN,
142 des1.BATCH_JOB_NUM,
143 des1.PROGRAM_APPLICATION_ID,
144 des1.PROGRAM_ID,
145 des1.PROGRAM_UPDATE_DATE,
146 des1.SEARCH_RESULTS_VISIBLE,
147 des1.ITEM_DETAIL_VISIBLE,
148 des1.REBUILD_FLAG,
149 decode(des1.rt_category_id, 0 , 'ICX_BASE_ATTR', 'ICX_CAT_ATTR')
150 from
151 icx_por_descriptors_tl des1
152 where des1.rt_descriptor_id > 100
153 and not exists (select null from icx_cat_descriptors_tl des2
154 where des1.rt_descriptor_id = des2.rt_descriptor_id
155 and des1.language = des2.language)
156 );
157 l_loc := 400;
158
159 -- Populate the icx_cat_browse_trees with rows
160 -- from icx_por_table_of_contents_tl
161 insert into icx_cat_browse_trees (PARENT_CATEGORY_ID,
162 CHILD_CATEGORY_ID, LAST_UPDATE_LOGIN ,
163 LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE)
164 select toc1.RT_CATEGORY_ID, toc1.CHILD, 1, toc1.LAST_UPDATED_BY,
165 toc1.LAST_UPDATE_DATE, toc1.CREATED_BY, toc1.CREATION_DATE
166 from icx_por_table_of_contents_tl toc1
167 where not exists (select null from icx_cat_browse_trees toc2
168 where toc2.PARENT_CATEGORY_ID = toc1.RT_CATEGORY_ID
169 and toc2.CHILD_CATEGORY_ID = toc1.CHILD);
170
171 l_loc := 500;
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 l_return_err := 'create_new_categ_descs_tables(' ||l_loc||'): '||sqlerrm;
176 raise_application_error(-20000,l_return_err);
177 END create_new_categ_descs_tables;
178
179 /**
180 **
181 ** Procedure: assign_section_tag_and_map
182 ** Synopsis : Loop through every genus category and assign a section map
183 ** for that category. Descriptors for that category is assigned
184 ** section tags.
185 **/
186 PROCEDURE assign_section_tag_and_map IS
187 l_category_ids dbms_sql.number_table;
188 i PLS_INTEGER := 1;
189 TYPE CursorType IS REF CURSOR;
190 get_categories CursorType;
191
192 BEGIN
193
194 -- Open the cursor for getting categories that have descriptors
195 l_loc := 100;
196 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Open the get_categories cursor to get distinct categories');
197
198 open get_categories for
199 select distinct RT_CATEGORY_ID
200 from icx_cat_descriptors_tl;
201
202 l_loc := 200;
203
204 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Loop through every category to assign section tag and section map');
205
206 -- Loop through every category to assign section tag and section map.
207 LOOP
208 l_loc := 300;
209 l_category_ids.DELETE;
210
211 FETCH get_categories
212 BULK COLLECT INTO l_category_ids
213 LIMIT l_commit_size;
214 EXIT WHEN l_category_ids.COUNT = 0;
215
216 -- Loop through all the genus category
217 for i in 1..l_category_ids.COUNT loop
218 l_loc := 300+i;
219
220 ICX_POR_SCHEMA_UPLOAD.assign_all_section_tags(l_category_ids(i));
221
222 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Assigning section tag and section map for rt_category_id = '|| l_category_ids(i));
223
224 end loop;
225 l_loc := 10000;
226
227 COMMIT;
228 END LOOP;
229 l_loc := 20000;
230
231 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Close cursor...');
232 CLOSE get_categories;
233 l_loc := 20010;
234
235 EXCEPTION
236 WHEN OTHERS THEN
237 l_return_err := ' assign_section_tag_and_map(' ||l_loc||'): '||sqlerrm;
238 raise_application_error(-20000,l_return_err);
239 END assign_section_tag_and_map;
240
241 /**
242 **
243 ** Procedure: upgrade
244 ** Synopsis : This is the main procedure that need to be called to run
245 ** upgrade the schema tables. It calls methods to populate
246 ** the new schema tables with rows from the old tables and also
247 ** to assign section tags and section maps.
248 **/
249 PROCEDURE upgrade IS
250 BEGIN
251 l_loc := 100;
252
253 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Schema upgrade Start.');
254
255 -- Populate the rows in all the schema tables(Categories, Descriptors, TOC)
256 create_new_categ_descs_tables();
257 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Created the new Category, Descriptor and TOC table.');
258 l_loc := 200;
259
260 assign_section_tag_and_map();
261 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Populated the new Category and Descriptors table with section maps and section tags.');
262
263 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'End Upgrade');
264
265 EXCEPTION
266 WHEN OTHERS THEN
267
268 l_return_err := 'ICXUPSCB.pls(' ||l_loc||'): '||sqlerrm;
269 raise_application_error(-20000,l_return_err);
270 END upgrade;
271
272 END ICX_POR_SCHEMA_UPGRADE;