1 PACKAGE ICX_CAT_SCHEMA_UPLOAD_PVT AS
2 /* $Header: ICXVSULS.pls 120.9 2006/07/12 01:15:28 kaholee noship $*/
3
4 -- Number of seeded descriptors
5 -- xxx why?
6 g_NUM_SEEDED_DESCRIPTORS CONSTANT NUMBER := 300;
7
8 -- Category type
9 g_ITEM_CATEGORY CONSTANT NUMBER := 2;
10 g_BROWSING_CATEORY CONSTANT NUMBER := 1;
11
12 -- Schema Attribute Ids
13 g_ROOT_ATTRIB_ID CONSTANT NUMBER := 0;
14 g_LOCAL_ATTRIB_ID CONSTANT NUMBER := 1;
15
16 -- Descriptor type
17 g_TEXT_TYPE CONSTANT VARCHAR2(1) := '0';
18 g_NUMERIC_TYPE CONSTANT VARCHAR2(1) := '1';
19 g_TRANSLATABLE_TEXT_TYPE CONSTANT VARCHAR2(1) := '2';
20
21 -- YES and NO
22 g_YES CONSTANT VARCHAR2(1) := '1';
23 g_NO CONSTANT VARCHAR2(1) := '0';
24
25 -----------------------sudsubra----------
26
27
28 -- Procedure the get the nullify values for the different datatypes
29 -- this returns the values for varchar, number and date
30 PROCEDURE get_nullify_values
31 (
32 x_nullify_char OUT NOCOPY VARCHAR2,
33 x_nullify_num OUT NOCOPY NUMBER,
34 x_nullify_date OUT NOCOPY DATE
35 );
36
37 -- Procedure to validate the descriptor before it gets created
38 -- this method is only called from item load
39 PROCEDURE validate_descriptor_for_create
40 (
41 p_key IN VARCHAR2,
42 p_name IN VARCHAR2,
43 p_type IN VARCHAR2,
44 p_cat_id IN NUMBER,
45 p_language IN VARCHAR2,
46 x_is_valid OUT NOCOPY VARCHAR2,
47 x_error OUT NOCOPY VARCHAR2
48 );
49
50 -- procedure to save the failed category into the failed lines table
51 PROCEDURE save_failed_category
52 (
53 p_request_id IN NUMBER,
54 p_line_number IN NUMBER,
55 p_action IN VARCHAR2,
56 p_key IN VARCHAR2,
57 p_name IN VARCHAR2,
58 p_type IN VARCHAR2,
59 p_description IN VARCHAR2
60 );
61
62 -- procedure to save the failed descriptor into the failed lines table
63 PROCEDURE save_failed_descriptor
64 (
65 p_request_id IN NUMBER,
66 p_line_number IN NUMBER,
67 p_action IN VARCHAR2,
68 p_key IN VARCHAR2,
69 p_name IN VARCHAR2,
70 p_type IN VARCHAR2,
71 p_description IN VARCHAR2,
72 p_owner_key IN VARCHAR2,
73 p_owner_name IN VARCHAR2,
74 p_sequence IN VARCHAR2,
75 p_searchable IN VARCHAR2,
76 p_item_detail_visible IN VARCHAR2,
77 p_search_results_visible IN VARCHAR2
78 );
79
80 -- procedure to save the failed relationships into the failed lines table
81 PROCEDURE save_failed_relationship
82 (
83 p_request_id IN NUMBER,
84 p_line_number IN NUMBER,
85 p_action IN VARCHAR2,
86 p_parent_key IN VARCHAR2,
87 p_parent_name IN VARCHAR2,
88 p_child_key IN VARCHAR2,
89 p_child_name IN VARCHAR2
90 );
91
92 -- inserts a row into the failed lines table
93 PROCEDURE insert_failed_line
94 (
95 p_request_id IN NUMBER,
96 p_line_number IN NUMBER,
97 p_action IN VARCHAR2,
98 p_row_type IN VARCHAR2,
99 p_descriptor_key IN VARCHAR2,
100 p_descriptor_value IN VARCHAR2
101 );
102
103 -- inserts a row into the failed messages table
104 PROCEDURE insert_failed_message
105 (
106 p_request_id IN NUMBER,
107 p_descriptor_key IN VARCHAR2,
108 p_message_name IN VARCHAR2,
109 p_line_number IN NUMBER
110 );
111
112 -- procedure to create a category
113 -- assumes that the parameters are valid
114 -- called from schema load
115 PROCEDURE create_category
116 (
117 x_category_id OUT NOCOPY NUMBER,
118 p_key IN VARCHAR2,
119 p_name IN VARCHAR2,
120 p_description IN VARCHAR2,
121 p_type IN NUMBER,
122 p_language IN VARCHAR2,
123 p_request_id IN NUMBER,
124 p_user_id IN NUMBER,
125 p_login_id IN NUMBER
126 );
127
128 -- procedure to update a category
129 -- assumes that the parameters are valid
130 -- called from schema load
131 PROCEDURE update_category
132 (
133 p_category_id IN NUMBER,
134 p_language IN VARCHAR2,
135 p_name IN VARCHAR2,
136 p_description IN VARCHAR2,
137 p_type IN NUMBER,
138 p_request_id IN NUMBER,
139 p_user_id IN NUMBER,
140 p_login_id IN NUMBER
141 );
142
143 -- procedure to delete a category
144 -- assumes that the parameters are valid
145 -- called from schema load
146 PROCEDURE delete_category
147 (
148 p_category_id IN NUMBER,
149 p_language IN VARCHAR2,
150 p_request_id IN NUMBER,
151 p_user_id IN NUMBER,
152 p_login_id IN NUMBER
153 );
154
155 -- procedure to validate a category
156 PROCEDURE validate_category
157 (
158 p_key IN VARCHAR2,
159 p_name IN VARCHAR2,
160 p_type IN VARCHAR2,
161 p_language IN VARCHAR2,
162 p_request_id IN NUMBER,
163 p_line_number IN NUMBER,
164 p_user_action IN VARCHAR2,
165 x_is_valid OUT NOCOPY VARCHAR2,
166 x_system_action OUT NOCOPY VARCHAR2,
167 x_category_id OUT NOCOPY NUMBER,
168 x_converted_type OUT NOCOPY VARCHAR2
169 );
170
171
172 -- function to check if category can be deleted
173 -- a category can be deleted if it is not referenced on any documents and master items
174 FUNCTION can_category_be_deleted
175 (
176 p_ip_category_id IN NUMBER
177 )
178 RETURN NUMBER;
179
180 -- procedure to add a relationship
181 -- assumes that the categories to be related are valid
182 PROCEDURE add_relationship
183 (
184 p_parent_id IN NUMBER,
185 p_child_id IN NUMBER,
186 p_user_id IN NUMBER,
187 p_login_id IN NUMBER,
188 p_request_id IN NUMBER,
189 p_line_number IN NUMBER,
190 p_action IN VARCHAR2
191 );
192
193 -- procedure to delete a relationship
194 -- assumes that the categories to be related are valid
195 PROCEDURE delete_relationship
196 (
197 p_parent_id IN NUMBER,
198 p_child_id IN NUMBER,
199 p_request_id IN NUMBER,
200 p_line_number IN NUMBER,
201 p_action IN VARCHAR2
202 );
203
204 -- procedure to delete a relationship
205 -- assumes that the categories to be related are valid
206 PROCEDURE validate_relationship
207 (
208 p_parent_key IN VARCHAR2,
209 p_parent_name IN VARCHAR2,
210 p_child_key IN VARCHAR2,
211 p_child_name IN VARCHAR2,
212 p_language IN VARCHAR2,
213 p_request_id IN NUMBER,
214 p_line_number IN NUMBER,
215 p_user_action IN VARCHAR2,
216 x_is_valid OUT NOCOPY VARCHAR2,
217 x_system_action OUT NOCOPY VARCHAR2,
218 x_parent_id OUT NOCOPY NUMBER,
219 x_child_id OUT NOCOPY NUMBER
220 );
221
222
223 -- procedure to create a descriptr
224 -- this assumes that everything has been validated
225 PROCEDURE create_descriptor
226 (
227 p_key IN VARCHAR2,
228 p_name IN VARCHAR2,
229 p_description IN VARCHAR2,
230 p_type IN VARCHAR2,
231 p_sequence IN NUMBER,
232 p_search_results_visible IN VARCHAR2,
233 p_item_detail_visible IN VARCHAR2,
234 p_searchable IN VARCHAR2,
235 p_language IN VARCHAR2,
236 p_category_id IN NUMBER,
237 p_request_id IN NUMBER,
238 p_user_id IN NUMBER,
239 p_login_id IN NUMBER,
240 x_descriptor_id OUT NOCOPY NUMBER,
241 x_stored_in_table OUT NOCOPY VARCHAR2,
242 x_stored_in_column OUT NOCOPY VARCHAR2,
243 x_section_tag OUT NOCOPY NUMBER
244 );
245
246 -- procedure to increment the schema version
247 PROCEDURE inc_schema_change_version
248 (
249 p_category_id IN NUMBER,
250 p_request_id IN NUMBER,
251 p_user_id IN NUMBER,
252 p_login_id IN NUMBER
253 );
254
255 -- Procedure to assign the section tag to a given descriptor
256 -- If the descriptor is already assigned a section tag it will be returne
257 PROCEDURE assign_section_tag
258 (
259 p_category_id IN NUMBER,
260 p_descriptor_id IN NUMBER,
261 p_type IN VARCHAR2,
262 p_section_tag OUT NOCOPY NUMBER,
263 p_stored_in_table OUT NOCOPY VARCHAR2,
264 p_stored_in_column OUT NOCOPY VARCHAR2,
265 p_request_id IN NUMBER
266 );
267
268 -- Procedure to release the section tag to a given descriptor
269 -- should be called before the descriptor is actually deleted
270 PROCEDURE release_section_tag
271 (
272 p_category_id IN NUMBER,
273 p_descriptor_id IN NUMBER,
274 p_request_id IN NUMBER
275 );
276
277 -- procedure to update a descriptr
278 -- this assumes that everything has been validated
279 PROCEDURE update_descriptor
280 (
281 p_descriptor_id IN NUMBER,
282 p_name IN VARCHAR2,
283 p_description IN VARCHAR2,
284 p_category_id IN VARCHAR2,
285 p_sequence IN NUMBER,
286 p_search_results_visible IN VARCHAR2,
287 p_item_detail_visible IN VARCHAR2,
288 p_searchable IN VARCHAR2,
289 p_language IN VARCHAR2,
290 p_request_id IN NUMBER,
291 p_user_id IN NUMBER,
292 p_login_id IN NUMBER
293 );
294
295
296 -- procedure to delete a descriptr
297 -- this assumes that everything has been validated
298 PROCEDURE delete_descriptor
299 (
300 p_descriptor_id IN NUMBER,
301 p_request_id IN NUMBER,
302 p_user_id IN NUMBER,
303 p_login_id IN NUMBER
304 );
305
306 -- function to check if descriptor can be deleted
307 -- a descriptor can be deleted if it has no values for any documents
308 FUNCTION can_descriptor_be_deleted
309 (
310 p_descriptor_id IN NUMBER
311 )
312 RETURN NUMBER;
313
314 -- procedure to validate a descriptor
315 PROCEDURE validate_descriptor
316 (
317 p_key IN VARCHAR2,
318 p_name IN VARCHAR2,
319 p_description IN VARCHAR2,
320 p_type IN VARCHAR2,
321 p_owner_key IN VARCHAR2,
322 p_owner_name IN VARCHAR2,
323 p_language IN VARCHAR2,
324 p_sequence IN VARCHAR2,
325 p_searchable IN VARCHAR2,
326 p_search_results_visible IN VARCHAR2,
327 p_item_detail_visible IN VARCHAR2,
328 p_request_id IN NUMBER,
329 p_line_number IN NUMBER,
330 p_user_action IN VARCHAR2,
331 x_is_valid OUT NOCOPY VARCHAR2,
332 x_system_action OUT NOCOPY VARCHAR2,
333 x_descriptor_id OUT NOCOPY NUMBER,
334 x_owner_id OUT NOCOPY NUMBER
335 );
336
337 -- procedure to delete old jobs from the tables
338 -- (icx_por_batch_jobs, icx_cat_batch_jobs, icx_por_failed_line_messages,
339 -- icx_por_failed_lines, icx_por_contract_references, icx_cat_parse_errors)
340 PROCEDURE purge_loader_tables;
341
342 -- procedure to populate the ctx desc for schema load
343 -- this will handle the following cases
344 -- 1. category name change
345 -- 2. Change of descriptor searchability
346 -- 3. Deletion of a descriptor
347 PROCEDURE populate_ctx_desc
348 (
349 p_request_id IN NUMBER
350 );
351
352 -- methods for online schema
353 -- submitted through concurrent programs
354
355 -- method to populate the ctx desc for category rename
356 PROCEDURE populate_for_cat_rename
357 (
358 x_errbuf OUT NOCOPY VARCHAR2,
359 x_retcode OUT NOCOPY NUMBER,
360 p_category_id IN NUMBER,
361 p_category_name IN VARCHAR2,
362 p_language IN VARCHAR2
363 );
364
365 -- method to populate the ctx_desc for a searchability change
366 PROCEDURE populate_for_searchable_change
367 (
368 x_errbuf OUT NOCOPY VARCHAR2,
369 x_retcode OUT NOCOPY NUMBER,
370 p_attribute_id IN NUMBER,
371 p_attribute_key IN VARCHAR2,
372 p_category_id IN NUMBER,
373 p_searchable IN NUMBER
374 );
375
376 -- method to update the status of a job
377 PROCEDURE update_job_status
378 (
379 p_job_number IN NUMBER,
380 p_job_status IN VARCHAR2
381 );
382
383 END ICX_CAT_SCHEMA_UPLOAD_PVT;
[Home] [Help]