1 PACKAGE BODY ICX_CAT_SQE_PVT AS
2 /* $Header: ICXVSQEB.pls 120.5 2006/08/29 02:30:29 sudsubra noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'ICX_CAT_SQE_PVT';
5
6 -- procedure to create sqes for a given content zone
7 -- this constructs the intermedia expressions for the content zone, puts
8 -- them into sqes and returns the sqe sequence
9 -- we create three expressions, one for each of the allowed item types PURCHASE, INTERNAL, BOTH
10 -- if any of the expressions is too long, it is an error and x_return_status
11 -- will be set to 'E'
12 PROCEDURE create_sqes_for_zone
13 (
14 p_content_zone_id IN NUMBER,
15 p_supplier_attr_action_flag IN VARCHAR2,
16 p_supplier_ids IN ICX_TBL_NUMBER,
17 p_supplier_site_ids IN ICX_TBL_NUMBER,
18 p_items_without_supplier_flag IN VARCHAR2,
19 p_category_attr_action_flag IN VARCHAR2,
20 p_category_ids IN ICX_TBL_NUMBER,
21 p_items_without_shop_catg_flag IN VARCHAR2,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_sqe_sequence IN OUT NOCOPY NUMBER
24 )
25 IS
26 l_api_name CONSTANT VARCHAR2(30) := 'create_sqes_for_zone';
27 l_log_string VARCHAR2(32000);
28 l_err_loc PLS_INTEGER;
29 l_int_intermedia_expression VARCHAR2(32000);
30 l_purch_intermedia_expression VARCHAR2(32000);
31 l_both_intermedia_expression VARCHAR2(32000);
32 l_int_sqe_name VARCHAR2(15);
33 l_purch_sqe_name VARCHAR2(15);
34 l_both_sqe_name VARCHAR2(15);
35 l_sqe_sequence NUMBER;
36 BEGIN
37
38 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
39 l_log_string := 'Starting create_sqes_for_zone: ' || p_content_zone_id;
40 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
41 END IF;
42 l_err_loc := 100;
43
44 -- now we will construct the intermedia expressions for the zone
45 construct_exprs_for_zone(p_supplier_attr_action_flag, p_supplier_ids,
46 p_supplier_site_ids, p_items_without_supplier_flag, p_category_attr_action_flag,
47 p_category_ids, p_items_without_shop_catg_flag, l_int_intermedia_expression,
48 l_purch_intermedia_expression, l_both_intermedia_expression);
49 l_err_loc := 300;
50
51 -- check to see if the intermedia expression is > 2000 bytes
52 IF (length(l_int_intermedia_expression) > 2000 OR
53 length(l_purch_intermedia_expression) > 2000 OR
54 length(l_both_intermedia_expression) > 2000) THEN
55 l_err_loc := 400;
56 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
57 l_log_string := 'One of the intermedia expressions for this zone is > 2000 bytes.';
58 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
59 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
60 END IF;
61 -- length exceeded, cannot create sqes, return status is error(E)
62 x_return_status := 'E';
63 ELSE
64 l_err_loc := 500;
65 -- we can fit the expressions into sqes, go ahead and create the sqes
66 -- for this we first get the next sequence
67 SELECT icx_cat_sqe_name_s.nextval
68 INTO l_sqe_sequence
69 FROM dual;
70
71 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
72 l_log_string := 'New sqe sequence is: ' || l_sqe_sequence;
73 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
74 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
75 END IF;
76 l_err_loc := 600;
77 -- and form the sqe names
78 l_int_sqe_name := 'icxzi' || l_sqe_sequence;
79 l_purch_sqe_name := 'icxzp' || l_sqe_sequence;
80 l_both_sqe_name := 'icxzb' || l_sqe_sequence;
81
82 l_err_loc := 700;
83 -- now store the sqes
84 ctx_query.store_sqe(l_int_sqe_name, l_int_intermedia_expression);
85 ctx_query.store_sqe(l_purch_sqe_name, l_purch_intermedia_expression);
86 ctx_query.store_sqe(l_both_sqe_name, l_both_intermedia_expression);
87
88 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
89 l_log_string := 'Finished Storing sqes';
90 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
91 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
92 END IF;
93
94 l_err_loc := 800;
95 -- now if the original content zone had an sqe_sequence, store it in deleted sqes
96 IF (x_sqe_sequence IS NOT NULL) THEN
97 l_err_loc := 850;
98 INSERT INTO icx_cat_deleted_sqes (sqe_sequence, created_by, creation_date,
99 last_updated_by, last_update_date, last_update_login)
100 VALUES (x_sqe_sequence, fnd_global.user_id, sysdate, fnd_global.user_id,
101 sysdate, fnd_global.login_id);
102
103 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
104 l_log_string := 'Inserted old sqe sequence into deleted sqes. Old sqe sequence: ' || x_sqe_sequence;
105 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
106 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
107 END IF;
108 END IF;
109
110 l_err_loc := 900;
111 x_sqe_sequence := l_sqe_sequence;
112
113 l_err_loc := 950;
114 -- finally we will purge the deleted sqes if any
115 purge_deleted_sqes;
116
117 l_err_loc := 1000;
118 -- success!
119 x_return_status := 'S';
120 END IF;
121
122 l_err_loc := 1100;
123 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
124 l_log_string := 'End create_sqes_for_zone: ' || p_content_zone_id || ' return status is ' || x_return_status;
125 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
126 END IF;
127
128 EXCEPTION
129 WHEN OTHERS THEN
130 ROLLBACK;
131 RAISE_APPLICATION_ERROR
132 (-20000,
133 'Exception at ICX_CAT_SQE_PVT.create_sqes_for_zone(' ||
134 l_err_loc || '), ' || SQLERRM);
135 END create_sqes_for_zone;
136
137
138 -- procedure to combine three expressions with the '&' operator
139 -- depending on which are not null
140 PROCEDURE combine_exprs
141 (
142 p_expr1 IN VARCHAR2,
143 p_expr2 IN VARCHAR2,
144 p_expr3 IN VARCHAR2,
145 x_result_expr OUT NOCOPY VARCHAR2
146 )
147 IS
148 l_api_name CONSTANT VARCHAR2(30) := 'combine_exprs';
149 l_log_string VARCHAR2(32000);
150 l_err_loc PLS_INTEGER;
151 l_first PLS_INTEGER;
152 BEGIN
153 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
154 l_log_string := 'Starting combine_exprs: expr1 = ' || p_expr1 || ' expr2 = ' || p_expr2
155 || ' expr3 = ' || p_expr3;
156 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
157 END IF;
158 l_err_loc := 100;
159 -- first set first to 1 i.e true
160 l_first := 1;
161
162 IF (p_expr1 IS NOT NULL) THEN
163 IF (l_first = 1) THEN
164 l_first := 0;
165 x_result_expr := '(' || p_expr1 || ')';
166 ELSE
167 x_result_expr := x_result_expr || ' & (' || p_expr1 || ')';
168 END IF;
169 END IF;
170
171 l_err_loc := 200;
172 IF (p_expr2 IS NOT NULL) THEN
173 IF (l_first = 1) THEN
174 l_first := 0;
175 x_result_expr := '(' || p_expr2 || ')';
176 ELSE
177 x_result_expr := x_result_expr || ' & (' || p_expr2 || ')';
178 END IF;
179 END IF;
180
181 l_err_loc := 300;
182 IF (p_expr3 IS NOT NULL) THEN
183 IF (l_first = 1) THEN
184 l_first := 0;
185 x_result_expr := '(' || p_expr3 || ')';
186 ELSE
187 x_result_expr := x_result_expr || ' & (' || p_expr3 || ')';
188 END IF;
189 END IF;
190
191 l_err_loc := 400;
192 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
193 l_log_string := 'End combine_exprs: result expr is: ' || x_result_expr;
194 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
195 END IF;
196
197 EXCEPTION
198 WHEN OTHERS THEN
199 ROLLBACK;
200 RAISE_APPLICATION_ERROR
201 (-20000,
202 'Exception at ICX_CAT_SQE_PVT.combine_exprs(' ||
203 l_err_loc || '), ' || SQLERRM);
204 END combine_exprs;
205
206 -- procedure to construct the intermedia expressions for a given zone
207 -- takes in all the required parameters and returns the intermedia expressions
208 -- it returns one intermedia expression for internal only items
209 -- one for purchasable only items and one for both
210 PROCEDURE construct_exprs_for_zone
211 (
212 p_supplier_attr_action_flag IN VARCHAR2,
213 p_supplier_ids IN ICX_TBL_NUMBER,
214 p_supplier_site_ids IN ICX_TBL_NUMBER,
215 p_items_without_supplier_flag IN VARCHAR2,
216 p_category_attr_action_flag IN VARCHAR2,
217 p_category_ids IN ICX_TBL_NUMBER,
218 p_items_without_shop_catg_flag IN VARCHAR2,
219 x_int_intermedia_expression OUT NOCOPY VARCHAR2,
220 x_purch_intermedia_expression OUT NOCOPY VARCHAR2,
221 x_both_intermedia_expression OUT NOCOPY VARCHAR2
222 )
223 IS
224 l_api_name CONSTANT VARCHAR2(30) := 'construct_exprs_for_zone';
225 l_log_string VARCHAR2(32000);
226 l_err_loc PLS_INTEGER;
227 l_category_expr VARCHAR2(32000);
228 l_supplier_and_site_expr VARCHAR2(32000);
229 l_purch_item_type_expr VARCHAR2(50);
230 l_int_item_type_expr VARCHAR2(50);
231 l_invalid_item_type_expr VARCHAR2(50);
232 l_item_type_expr VARCHAR2(2000);
233 l_everything_expr VARCHAR2(2000);
234 BEGIN
235 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
236 l_log_string := 'Starting construct_exprs_for_zone';
237 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
238 END IF;
239 l_err_loc := 100;
240
241 -- first we initialize the fixed sqes
242 l_purch_item_type_expr := '(''BOTH'',''PURCHASE'') within item_type';
243 l_int_item_type_expr := '(''BOTH'',''INTERNAL'') within item_type';
244 l_invalid_item_type_expr := '(''INVALID_ITEM_TYPE'') within item_type';
245 l_everything_expr := '((''BOTH'',''INTERNAL'',''PURCHASE'') within item_type)';
246 x_int_intermedia_expression := '';
247 x_purch_intermedia_expression := '';
248 x_both_intermedia_expression := '';
249
250 l_err_loc := 200;
251 -- then construct intermedia expression for supplier and site
252 construct_supp_and_site_expr(p_supplier_attr_action_flag,
253 p_supplier_ids, p_supplier_site_ids, p_items_without_supplier_flag,
254 l_supplier_and_site_expr);
255
256 l_err_loc := 300;
257 -- next construct intermedia expression for category
258 construct_category_expr(p_category_attr_action_flag,
259 p_category_ids, p_items_without_shop_catg_flag, l_category_expr);
260
261 l_err_loc := 400;
262 -- now we will combine the expressions for the three cases
263
264 -- first we will construct the expression for internal
265 IF (p_items_without_supplier_flag = 'Y') THEN
266 l_err_loc := 500;
267 l_item_type_expr := l_int_item_type_expr;
268 ELSE
269 l_err_loc := 600;
270 l_item_type_expr := l_invalid_item_type_expr;
271 END IF;
272
273 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
274 l_log_string := 'Item Type expr for Internal: ' || l_item_type_expr;
275 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
276 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
277 END IF;
278 l_err_loc := 700;
279 -- now we combine the expressions for internal
280 -- note that we pass '' for supplier since this is not needed
281 combine_exprs('', l_category_expr, l_item_type_expr, x_int_intermedia_expression);
282
283 l_err_loc := 750;
284 -- we cannot have the intermedia expression as null so if it is null we make it everything
285 IF (x_int_intermedia_expression IS NULL) THEN
286 x_int_intermedia_expression := l_everything_expr;
287 END IF;
288
289 l_err_loc := 800;
290 -- now we construct the expression for purchase
291 IF (p_supplier_attr_action_flag = 'EXCLUDE_ALL') THEN
292 l_err_loc := 900;
293 IF (p_items_without_supplier_flag = 'Y') THEN
294 l_err_loc := 1000;
295 l_item_type_expr := l_purch_item_type_expr;
296 ELSE
297 l_err_loc := 1100;
298 l_item_type_expr := l_invalid_item_type_expr;
299 END IF;
300 ELSE
301 l_err_loc := 1200;
302 IF (p_items_without_supplier_flag = 'Y') THEN
303 l_err_loc := 1300;
304 l_item_type_expr := l_purch_item_type_expr;
305 ELSE
306 l_err_loc := 1400;
307 l_item_type_expr := '';
308 END IF;
309 END IF;
310
311 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
312 l_log_string := 'Item Type expr for Purchase: ' || l_item_type_expr;
313 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
314 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
315 END IF;
316
317 l_err_loc := 1500;
318 -- now we combine the expressions for purchase
319 combine_exprs(l_supplier_and_site_expr, l_category_expr, l_item_type_expr, x_purch_intermedia_expression);
320
321 l_err_loc := 1600;
322 -- we cannot have the intermedia expression as null so if it is null we make it everything
323 IF (x_purch_intermedia_expression IS NULL) THEN
324 l_err_loc := 1700;
325 x_purch_intermedia_expression := l_everything_expr;
326 END IF;
327
328 l_err_loc := 1800;
329 -- now we construct the expression for both
330 IF (p_supplier_attr_action_flag = 'EXCLUDE_ALL') THEN
331 l_err_loc := 1900;
332 IF (p_items_without_supplier_flag = 'Y') THEN
333 l_err_loc := 2000;
334 l_item_type_expr := '';
335 ELSE
336 l_err_loc := 2100;
337 l_item_type_expr := l_invalid_item_type_expr;
338 END IF;
339 ELSE
340 l_err_loc := 2200;
341 l_item_type_expr := '';
342 END IF;
343 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
344 l_log_string := 'Item Type expr for both: ' || l_item_type_expr;
345 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
346 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
347 END IF;
348 l_err_loc := 2500;
349 -- now we combine the expressions for purchase
350 combine_exprs(l_supplier_and_site_expr, l_category_expr, l_item_type_expr, x_both_intermedia_expression);
351
352 l_err_loc := 2600;
353 -- we cannot have the intermedia expression as null so if it is null we make it everything
354 IF (x_both_intermedia_expression IS NULL) THEN
355 l_err_loc := 2700;
356 x_both_intermedia_expression := l_everything_expr;
357 END IF;
358
359 l_err_loc := 2800;
360 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
361 l_log_string := 'End construct_exprs_for_zone: Internal String:'
362 || x_int_intermedia_expression || ' Purchase String: ' || x_purch_intermedia_expression
363 || ' Both String: ' || x_both_intermedia_expression;
364 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
365 END IF;
366 EXCEPTION
367 WHEN OTHERS THEN
368 ROLLBACK;
369 RAISE_APPLICATION_ERROR
370 (-20000,
371 'Exception at ICX_CAT_SQE_PVT.construct_exprs_for_zone(' ||
375 -- procedure to constuct the supplier and site expression for a given zone
372 l_err_loc || '), ' || SQLERRM);
373 END construct_exprs_for_zone;
374
376 -- takes in the required parameters and returns the supplier and site expression
377 PROCEDURE construct_supp_and_site_expr
378 (
379 p_supplier_attr_action_flag IN VARCHAR2,
380 p_supplier_ids IN ICX_TBL_NUMBER,
381 p_supplier_site_ids IN ICX_TBL_NUMBER,
382 p_items_without_supplier_flag IN VARCHAR2,
383 x_supplier_and_site_expr OUT NOCOPY VARCHAR2
384 )
385 IS
386 l_api_name CONSTANT VARCHAR2(30) := 'construct_supp_and_site_expr';
387 l_log_string VARCHAR2(32000);
388 l_err_loc PLS_INTEGER;
389 l_supplier_id_list VARCHAR2(32000);
390 l_supplier_site_id_list VARCHAR2(32000);
391 l_exclude_expr VARCHAR2(100);
392 l_supplier_and_site_expr VARCHAR2(32000);
393 BEGIN
394 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
395 l_log_string := 'Starting construct_supp_and_site_expr';
396 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
397 END IF;
398 l_err_loc := 100;
399
400 -- initialize the expressions to ''
401 l_supplier_id_list := '';
402 l_supplier_site_id_list := '';
403 x_supplier_and_site_expr := '';
404 l_exclude_expr := '((''BOTH'',''INTERNAL'',''PURCHASE'') within item_type)';
405
406 l_err_loc := 200;
407 -- we only need the supplier and site expr
408 -- if it is either include some or exclude some
409 IF (p_supplier_attr_action_flag in ('INCLUDE', 'EXCLUDE')) THEN
410 l_err_loc := 300;
411 -- now loop through the lists and construct the expr
412 FOR i in 1..p_supplier_ids.COUNT LOOP
413 l_err_loc := 400;
414 IF (p_supplier_site_ids(i) IS NULL) THEN
415 l_err_loc := 500;
416 -- supplier site not provided append supplier_id to the supplier id list
417 IF (l_supplier_id_list IS NULL) THEN
418 l_err_loc := 600;
419 l_supplier_id_list := p_supplier_ids(i);
420 ELSE
421 l_err_loc := 700;
422 l_supplier_id_list := l_supplier_id_list || ',' || p_supplier_ids(i);
423 END IF;
424 ELSE
425 l_err_loc := 800;
426 -- supplier site id provided so we just use the site id and append to its list
427 IF (l_supplier_site_id_list IS NULL) THEN
428 l_err_loc := 900;
429 l_supplier_site_id_list := p_supplier_site_ids(i);
430 ELSE
431 l_err_loc := 1000;
432 l_supplier_site_id_list := l_supplier_site_id_list || ',' || p_supplier_site_ids(i);
433 END IF;
434 END IF;
435 END LOOP;
436 END IF;
437
438 l_err_loc := 1100;
439 IF (p_supplier_attr_action_flag = 'INCLUDE_ALL') THEN
440 IF (p_items_without_supplier_flag = 'N') THEN
441 -- if we include all but don't check the checkbox then we want to exclude -2
442 l_err_loc := 1200;
443 l_supplier_id_list := '{-2}';
444 l_err_loc := 1300;
445 -- supplier id list is -2 here and supplier site id list is null
446 x_supplier_and_site_expr := l_exclude_expr || ' ~ ((' || l_supplier_id_list || ') within supid)';
447 END IF;
448 END IF;
449
450 l_err_loc := 1400;
451 IF (p_supplier_attr_action_flag = 'INCLUDE') THEN
452 l_err_loc := 1500;
453 IF (p_items_without_supplier_flag = 'Y') THEN
454 -- if we include some and check the checkbox then we want to include -2
455 IF (l_supplier_id_list IS NULL) THEN
456 l_err_loc := 1600;
457 l_supplier_id_list := '{-2}';
458 ELSE
459 l_err_loc := 1700;
460 l_supplier_id_list := l_supplier_id_list || ',{-2}';
461 END IF;
462 END IF;
463
464 l_err_loc := 1800;
465 IF (l_supplier_site_id_list IS NULL) THEN
466 l_err_loc := 1900;
467 -- in this case we will have a supplier_id_list
468 x_supplier_and_site_expr := '(' || l_supplier_id_list || ') within supid';
469 ELSE
470 -- here we may or may not have a supplier_id_list
471 IF (l_supplier_id_list IS NULL) THEN
472 l_err_loc := 1950;
473 x_supplier_and_site_expr := '(' || l_supplier_site_id_list || ') within siteid';
474 ELSE
475 l_err_loc := 2000;
476 x_supplier_and_site_expr := '((' || l_supplier_id_list || ') within supid),(('
477 || l_supplier_site_id_list || ') within siteid)';
478 END IF;
479 END IF;
480
481 END IF;
482
483 l_err_loc := 2100;
484 IF (p_supplier_attr_action_flag = 'EXCLUDE') THEN
485 IF (p_items_without_supplier_flag = 'N') THEN
486 -- now if it is exclude and we don't check the checkbox then we want to exclude -2
487 l_err_loc := 2200;
488 IF (l_supplier_id_list IS NULL) THEN
489 l_err_loc := 2300;
490 l_supplier_id_list := '{-2}';
491 ELSE
492 l_err_loc := 2400;
493 l_supplier_id_list := l_supplier_id_list || ',{-2}';
497 l_err_loc := 2500;
494 END IF;
495 END IF;
496
498 IF (l_supplier_site_id_list IS NULL) THEN
499 -- in this case we will have a supplier_id_list
500 l_err_loc := 2600;
501 x_supplier_and_site_expr := '(' || l_supplier_id_list || ') within supid';
502 ELSE
503 -- here we may or may not have a supplier_id_list
504 IF (l_supplier_id_list IS NULL) THEN
505 l_err_loc := 2650;
506 x_supplier_and_site_expr := '(' || l_supplier_site_id_list || ') within siteid';
507 ELSE
508 l_err_loc := 2700;
509 x_supplier_and_site_expr := '((' || l_supplier_id_list || ') within supid),(('
510 || l_supplier_site_id_list || ') within siteid)';
511 END IF;
512 END IF;
513
514 l_err_loc := 2800;
515 -- finally we negate it using the fixed exclude expr
516 x_supplier_and_site_expr := l_exclude_expr || ' ~ (' || x_supplier_and_site_expr || ')';
517 END IF;
518
519 l_err_loc := 2900;
520 IF (p_supplier_attr_action_flag = 'EXCLUDE_ALL') THEN
521 IF (p_items_without_supplier_flag = 'Y') THEN
522 -- if we exclude all and check the checkbox then we want to include -2
523 l_err_loc := 3000;
524 l_supplier_id_list := '{-2}';
525 l_err_loc := 3100;
526 -- supplier id list is -2 here and supplier site id list is null
527 x_supplier_and_site_expr := '(' || l_supplier_id_list || ') within supid';
528 END IF;
529 END IF;
530
531 l_err_loc := 3200;
532 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
533 l_log_string := 'End construct_supp_and_site_expr: Supplier and site expr is:'
534 || x_supplier_and_site_expr;
535 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
536 END IF;
537 EXCEPTION
538 WHEN OTHERS THEN
539 ROLLBACK;
540 RAISE_APPLICATION_ERROR
541 (-20000,
542 'Exception at ICX_CAT_SQE_PVT.construct_supp_and_site_expr(' ||
543 l_err_loc || '), ' || SQLERRM);
544 END construct_supp_and_site_expr;
545
546
547 -- procedure to constuct the category expression for a given zone
548 -- takes in the required parameters and returns the category expression
549 PROCEDURE construct_category_expr
550 (
551 p_category_attr_action_flag IN VARCHAR2,
552 p_category_ids IN ICX_TBL_NUMBER,
553 p_items_without_shop_catg_flag IN VARCHAR2,
554 x_category_expr OUT NOCOPY VARCHAR2
555 )
556 IS
557 l_api_name CONSTANT VARCHAR2(30) := 'construct_category_expr';
558 l_log_string VARCHAR2(32000);
559 l_err_loc PLS_INTEGER;
560 l_exclude_expr VARCHAR2(100);
561 l_category_ids ICX_TBL_NUMBER;
562 l_category_id_list VARCHAR2(32000);
563 l_current_category_id_list VARCHAR2(32000);
564 BEGIN
565 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
566 l_log_string := 'Starting construct_category_expr';
567 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
568 END IF;
569 l_err_loc := 100;
570 -- initialize the expressions to ''
571 l_category_id_list := '';
572 x_category_expr := '';
573 l_exclude_expr := '((''BOTH'',''INTERNAL'',''PURCHASE'') within item_type)';
574
575 l_err_loc := 200;
576 IF (p_category_attr_action_flag = 'INCLUDE_ALL') THEN
577 l_err_loc := 300;
578 IF (p_items_without_shop_catg_flag IS NULL OR p_items_without_shop_catg_flag = 'N') THEN
579 l_err_loc := 400;
580 -- we need to exclude the -2 categories since the checkbox is not checked and it is
581 -- include all
582 x_category_expr := l_exclude_expr || ' ~ ({-2} within ipcatid)';
583 END IF;
584 ELSE
585 l_err_loc := 500;
586 -- now loop through the list and construct the category id list
587 FOR i in 1..p_category_ids.COUNT LOOP
588 l_err_loc := 600;
589 -- now for each category, we have to either get the category itself (if it is an item category)
590 -- or get the list of item categories under it
591 SELECT distinct rt_category_id
592 BULK COLLECT INTO l_category_ids
593 FROM icx_cat_categories_tl
594 WHERE type = 2
595 AND rt_category_id in (SELECT child_category_id
596 FROM icx_cat_browse_trees
597 START WITH parent_category_id = p_category_ids(i)
598 CONNECT BY NOCYCLE PRIOR child_category_id = parent_category_id
599 UNION ALL
600 SELECT p_category_ids(i)
601 FROM dual);
602
603 l_err_loc := 650;
604 -- now construct the list for that browsing category or for item category it is just itself
605 FOR j in 1..l_category_ids.COUNT LOOP
606 IF (j = 1) THEN
607 l_err_loc := 700;
608 -- if it is the first one then the list is just that
609 l_current_category_id_list := l_category_ids(j);
610 ELSE
611 l_err_loc := 750;
612 -- else we append it to the previous one
613 l_current_category_id_list := l_current_category_id_list || ',' || l_category_ids(j);
614 END IF;
615 END LOOP;
616
617 -- now we append the current list to the full list
618 l_err_loc := 770;
619 IF (i = 1) THEN
620 l_err_loc := 800;
621 -- if it is the first one then the list is just that
622 l_category_id_list := l_current_category_id_list;
623 ELSE
624 l_err_loc := 850;
628 END LOOP;
625 -- else we append it to the previous one
626 l_category_id_list := l_category_id_list || ',' || l_current_category_id_list;
627 END IF;
629
630 l_err_loc := 900;
631 IF (p_category_attr_action_flag = 'INCLUDE') THEN
632 l_err_loc := 1000;
633 IF (p_items_without_shop_catg_flag = 'Y') THEN
634 l_err_loc := 1100;
635 -- if it is include and we have checked the checkbox, then we include -2 as well
636 IF (l_category_id_list IS NULL) THEN
637 l_category_id_list := '{-2}';
638 ELSE
639 l_category_id_list := l_category_id_list || ',{-2}';
640 END IF;
641 END IF;
642 l_err_loc := 1200;
643 -- if category id list is empty then that means we exclude everything
644 -- so we put some invalid value into the category id expr, so we put -1
645 IF (l_category_id_list IS NULL) THEN
646 l_err_loc := 1230;
647 l_category_id_list := '{-1}';
648 END IF;
649 l_err_loc := 1250;
650 x_category_expr := '(' || l_category_id_list || ') within ipcatid';
651 END IF;
652
653 l_err_loc := 1300;
654 IF (p_category_attr_action_flag = 'EXCLUDE') THEN
655 l_err_loc := 1400;
656 IF (p_items_without_shop_catg_flag IS NULL OR p_items_without_shop_catg_flag = 'N') THEN
657 l_err_loc := 1500;
658 -- if it is exclude and we have not checked the checkbox, then we exclude -2 as well
659 IF (l_category_id_list IS NULL) THEN
660 l_category_id_list := '{-2}';
661 ELSE
662 l_category_id_list := l_category_id_list || ',{-2}';
663 END IF;
664 END IF;
665 l_err_loc := 1600;
666 -- if category id list is empty then that means we include everything
667 -- so we leave the category expr as null
668 IF (l_category_id_list IS NULL) THEN
669 l_err_loc := 1630;
670 x_category_expr := '';
671 ELSE
672 l_err_loc := 1650;
673 x_category_expr := l_exclude_expr || ' ~ ((' || l_category_id_list || ') within ipcatid)';
674 END IF;
675 END IF;
676 l_err_loc := 1700;
677 END IF;
678 l_err_loc := 1800;
679 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
680 l_log_string := 'End construct_category_expr: Category expr is:'
681 || x_category_expr;
682 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
683 END IF;
684 EXCEPTION
685 WHEN OTHERS THEN
686 ROLLBACK;
687 RAISE_APPLICATION_ERROR
688 (-20000,
689 'Exception at ICX_CAT_SQE_PVT.construct_category_expr(' ||
690 l_err_loc || '), ' || SQLERRM);
691 END construct_category_expr;
692
693 -- procedure to purge the deleted sqes
694 -- this purges all sqes that have been deleted more than a day ago
695 PROCEDURE purge_deleted_sqes
696 IS
697 l_api_name CONSTANT VARCHAR2(30) := 'purge_deleted_sqes';
698 l_log_string VARCHAR2(32000);
699 l_err_loc PLS_INTEGER;
700 l_sqe_sequences ICX_TBL_NUMBER;
701 BEGIN
702 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
703 l_log_string := 'Starting purge_deleted_sqes';
704 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
705 END IF;
706
707 l_err_loc := 100;
708
709 -- first select all the sqe sequences that have been deleted more
710 -- than a day ago
711 SELECT sqe_sequence
712 BULK COLLECT INTO l_sqe_sequences
713 FROM icx_cat_deleted_sqes
714 WHERE creation_date < sysdate - 1;
715
716 l_err_loc := 200;
717
718 -- now delete the three sqes for each of them
719 -- we put this in a separate begin end block
720 -- and catch the exception since the remove sqe automatically commits
721 -- so we may have removed the sqes but not committed the delete from the
722 -- deleted sqes table. in this case the next time around we will try to remove
723 -- the same sqes which will throw an exception. to avoid this we catch the exception
724 -- and do nothing
725 BEGIN
726 FOR i in 1..l_sqe_sequences.COUNT LOOP
727 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
728 l_log_string := 'Removing sqes for sqe_sequence: ' || l_sqe_sequences(i);
729 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
730 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
731 END IF;
732 l_err_loc := 300;
733 ctx_query.remove_sqe('icxzi' || l_sqe_sequences(i));
734 ctx_query.remove_sqe('icxzp' || l_sqe_sequences(i));
735 ctx_query.remove_sqe('icxzb' || l_sqe_sequences(i));
736 END LOOP;
737 EXCEPTION
738 WHEN OTHERS THEN
739 NULL;
740 END;
741
742 l_err_loc := 400;
743 -- now remove the sqe sequences from the table
744 FORALL i in 1..l_sqe_sequences.COUNT
745 DELETE FROM icx_cat_deleted_sqes
746 WHERE sqe_sequence = l_sqe_sequences(i);
747
748 l_err_loc := 500;
749 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
750 l_log_string := 'End purge_deleted_sqes';
751 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
752 END IF;
753
754 EXCEPTION
755 WHEN OTHERS THEN
756 ROLLBACK;
757 RAISE_APPLICATION_ERROR
758 (-20000,
759 'Exception at ICX_CAT_SQE_PVT.purge_deleted_sqes(' ||
760 l_err_loc || '), ' || SQLERRM);
761 END purge_deleted_sqes;
762
763 -- procedure to sync sqes for all content zones
764 -- called for hierarchy changes from a concurrent program
765 -- or from the schema loader
766 -- this will recreate sqes for all the content zones
770 PROCEDURE sync_sqes_hier_change_internal
767 -- if some content zones have errors since the expression is too long
768 -- the job will be errored out with a message specifying which zones failed
769 -- the successful zones will however be updated
771 (
772 x_return_status OUT NOCOPY VARCHAR2,
773 x_errored_zone_name_list OUT NOCOPY VARCHAR2
774 )
775 IS
776 l_api_name CONSTANT VARCHAR2(30) := 'sync_sqes_hier_change_internal';
777 l_log_string VARCHAR2(32000);
778 l_err_loc PLS_INTEGER;
779 l_content_zone_ids ICX_TBL_NUMBER;
780 l_content_zone_names ICX_TBL_VARCHAR240;
781 l_supplier_attr_action_flags ICX_TBL_VARCHAR40;
782 l_supplier_ids ICX_TBL_NUMBER;
783 l_supplier_site_ids ICX_TBL_NUMBER;
784 l_items_without_supplier_flags ICX_TBL_FLAG;
785 l_category_attr_action_flags ICX_TBL_VARCHAR40;
786 l_category_ids ICX_TBL_NUMBER;
787 l_items_without_shop_cat_flags ICX_TBL_FLAG;
788 l_sqe_sequences ICX_TBL_NUMBER;
789 l_return_status VARCHAR2(1);
790 l_current_sqe_sequence NUMBER;
791 l_first_errored_zone PLS_INTEGER;
792 BEGIN
793 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
794 l_log_string := 'Starting sync_sqes_hier_change_internal';
795 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
796 END IF;
797 l_first_errored_zone := 1;
798 x_return_status := 'S';
799
800 l_err_loc := 100;
801 -- first query all the content zones for the basic information
802 -- we only query those that are local
803 -- and have either include or exclude for category
804 SELECT zones.zone_id, zonestl.name, supplier_attribute_action_flag, category_attribute_action_flag,
805 items_without_supplier_flag, items_without_shop_catg_flag, sqe_sequence
806 BULK COLLECT INTO l_content_zone_ids, l_content_zone_names, l_supplier_attr_action_flags,
807 l_category_attr_action_flags, l_items_without_supplier_flags, l_items_without_shop_cat_flags, l_sqe_sequences
808 FROM icx_cat_content_zones_b zones, icx_cat_content_zones_tl zonestl
809 WHERE zones.type = 'LOCAL'
810 AND zones.category_attribute_action_flag IN ('INCLUDE', 'EXCLUDE')
811 AND zones.zone_id = zonestl.zone_id
812 AND zonestl.language = USERENV('LANG');
813
814 l_err_loc := 200;
815 -- now loop
816 FOR i in 1..l_content_zone_ids.COUNT LOOP
817 l_err_loc := 300;
818 -- now get all the categories for that zone
819 SELECT ip_category_id
820 BULK COLLECT INTO l_category_ids
821 FROM icx_cat_zone_secure_attributes
822 WHERE zone_id = l_content_zone_ids(i)
823 AND securing_attribute = 'CATEGORY';
824
825 l_err_loc := 400;
826 -- now get all the suppliers and sites for that zone
827 SELECT supplier_id, supplier_site_id
828 BULK COLLECT INTO l_supplier_ids, l_supplier_site_ids
829 FROM icx_cat_zone_secure_attributes
830 WHERE zone_id = l_content_zone_ids(i)
831 AND securing_attribute = 'SUPPLIER';
832
833 l_err_loc := 450;
834 -- set the current sqe sequence
835 l_current_sqe_sequence := l_sqe_sequences(i);
836
837 l_err_loc := 500;
838 -- now call the API to create sqes for the zone
839 create_sqes_for_zone (l_content_zone_ids(i), l_supplier_attr_action_flags(i), l_supplier_ids,
840 l_supplier_site_ids, l_items_without_supplier_flags(i), l_category_attr_action_flags(i),
841 l_category_ids, l_items_without_shop_cat_flags(i), l_return_status, l_current_sqe_sequence);
842
843 IF (l_return_status = 'S') THEN
844 l_err_loc := 600;
845 -- success, update the sqe sequence
846 l_sqe_sequences(i) := l_current_sqe_sequence;
847 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
848 l_log_string := 'Succesfully updating the sequence for content_zone: ' || l_content_zone_ids(i)
849 || ' to sqe_sequence: ' || l_sqe_sequences(i);
850 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
851 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
852 END IF;
853 ELSE
854 l_err_loc := 700;
855 x_return_status := 'E';
856 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
857 l_log_string := 'Error for content_zone: ' || l_content_zone_ids(i) || ' sqe_sequence: ' || l_sqe_sequences(i);
858 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
859 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
860 END IF;
861 -- error
862 IF (l_first_errored_zone = 1) THEN
863 l_first_errored_zone := 0;
864 x_errored_zone_name_list := l_content_zone_names(i);
865 ELSE
866 IF (length(x_errored_zone_name_list) + length(l_content_zone_names(i)) < 3950) THEN
867 x_errored_zone_name_list := x_errored_zone_name_list || ', ' || l_content_zone_names(i);
868 END IF;
869 END IF;
870 END IF;
871 END LOOP;
872
873 l_err_loc := 800;
874 -- now update all the content zones with the new sqe sequences
875 FORALL i IN 1..l_content_zone_ids.COUNT
876 UPDATE icx_cat_content_zones_b
877 SET sqe_sequence = l_sqe_sequences(i)
878 WHERE zone_id = l_content_zone_ids(i);
879
880 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
881 l_log_string := 'End sync_sqes_hier_change_internal';
882 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
883 END IF;
884 EXCEPTION
885 WHEN OTHERS THEN
886 ROLLBACK;
887 RAISE_APPLICATION_ERROR
888 (-20000,
889 'Exception at ICX_CAT_SQE_PVT.sync_sqes_hier_change_internal(' ||
890 l_err_loc || '), ' || SQLERRM);
891
892 END sync_sqes_hier_change_internal;
893
894
895 -- procedure to sync sqes for all content zones
896 -- called for hierarchy changes from a concurrent program
900 PROCEDURE sync_sqes_for_hierarchy_change
897 -- this will call the main api which does the actual sync
898 -- this api in addition updates the failed line messages
899 -- and the job status
901 (
902 x_errbuf OUT NOCOPY VARCHAR2,
903 x_retcode OUT NOCOPY NUMBER
904 )
905 IS
906 l_api_name CONSTANT VARCHAR2(30) := 'sync_sqes_for_hierarchy_change';
907 l_log_string VARCHAR2(32000);
908 l_err_loc PLS_INTEGER;
909 l_request_id NUMBER;
910 l_return_status VARCHAR2(1);
911 l_errored_zone_name_list VARCHAR2(4000);
912 l_token_list VARCHAR2(4000);
913 BEGIN
914 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
915 l_log_string := 'Starting sync_sqes_for_hierarchy_change';
916 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
917 END IF;
918 l_err_loc := 100;
919
920 -- first start off with no error
921 x_retcode := 0;
922 x_errbuf := '';
923
924 l_err_loc := 200;
925 -- get the concurrent request Id
926 l_request_id := fnd_global.conc_request_id;
927
928 l_err_loc := 300;
929 -- update the job status to running
930 ICX_CAT_SCHEMA_UPLOAD_PVT.update_job_status(l_request_id, 'RUNNING');
931
932 l_err_loc := 400;
933 -- call the main API to do the sync
934 sync_sqes_hier_change_internal(l_return_status, l_errored_zone_name_list);
935
936 l_err_loc := 500;
937 -- set job status depending on whether there is an error on not
938 IF (l_return_status = 'E') THEN
939 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
940 l_log_string := 'Could not sync sqes for some zones. Updating the job to error.';
941 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
942 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
943 END IF;
944 l_err_loc := 600;
945 -- update the job status to error
946 x_retcode := 1;
947 ICX_CAT_SCHEMA_UPLOAD_PVT.update_job_status(l_request_id, 'ERROR');
948
949 l_err_loc := 700;
950 l_token_list := 'ZONE_NAMES:' || l_errored_zone_name_list;
951
952 l_err_loc := 800;
953 INSERT INTO icx_por_failed_line_messages
954 (job_number, descriptor_key, message_name, token_list, line_number, request_id, program_id,
955 program_application_id, program_login_id)
956 VALUES (l_request_id, 'OTHER', 'ICX_CAT_CANNOT_UPDATE_CZS_ERR', l_token_list, 1, l_request_id,
957 fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id);
958 ELSE
959 l_err_loc := 900;
960 -- update the job status to completed
961 ICX_CAT_SCHEMA_UPLOAD_PVT.update_job_status(l_request_id, 'COMPLETED');
962 END IF;
963
964 l_err_loc := 1000;
965
966 COMMIT;
967
968 l_err_loc := 1100;
969 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
970 l_log_string := 'End sync_sqes_for_hierarchy_change';
971 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
972 END IF;
973
974 EXCEPTION
975 WHEN OTHERS THEN
976 ROLLBACK;
977
978 x_retcode := 2;
979 x_errbuf := 'Exception at ICX_CAT_SQE_PVT.sync_sqes_for_hierarchy_change(' ||
980 l_err_loc || '), ' || SQLERRM;
981
982 -- update the job status to error
983 ICX_CAT_SCHEMA_UPLOAD_PVT.update_job_status(l_request_id, 'ERROR');
984
985 INSERT INTO icx_por_failed_line_messages
986 (job_number, descriptor_key, message_name, token_list, line_number, request_id, program_id,
987 program_application_id, program_login_id)
988 VALUES (l_request_id, 'OTHER', 'ICX_CAT_HIER_UNEXPECTED_ERR', l_token_list, 1, l_request_id,
989 fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id);
990
991 COMMIT;
992
993 END sync_sqes_for_hierarchy_change;
994
995 -- procedure to sync sqes for all content zones
996 -- called for hierarchy changes from the schema loader
997 -- this will call the main api which does the actual sync
998 -- this api in addition updates the failed line messages
999 -- and the failed lines table
1000 PROCEDURE sync_sqes_for_hierarchy_change
1001 (
1002 p_request_id IN NUMBER,
1003 p_line_number IN NUMBER,
1004 p_action IN VARCHAR2,
1005 x_return_status OUT NOCOPY VARCHAR2
1006 )
1007 IS
1008 l_api_name CONSTANT VARCHAR2(30) := 'sync_sqes_for_hierarchy_change';
1009 l_log_string VARCHAR2(32000);
1010 l_err_loc PLS_INTEGER;
1011 l_request_id NUMBER;
1012 l_errored_zone_name_list VARCHAR2(4000);
1013 BEGIN
1014 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1015 l_log_string := 'Starting sync_sqes_hier_change';
1016 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
1017 END IF;
1018 l_err_loc := 100;
1022 l_err_loc := 500;
1019 -- call the main API to do the sync
1020 sync_sqes_hier_change_internal(x_return_status, l_errored_zone_name_list);
1021
1023 -- if there is an error then we insert into failed lines and failed line messages
1024 IF (x_return_status = 'E') THEN
1025 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1026 l_log_string := 'Could not sync sqes for some zones. Returning error.';
1027 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1028 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
1029 END IF;
1030 l_err_loc := 800;
1031 INSERT INTO icx_por_failed_line_messages
1032 (job_number, descriptor_key, message_name, token_list, line_number, request_id, program_id,
1033 program_application_id, program_login_id)
1034 VALUES (p_request_id, 'ICX_CAT_CONTENT_ZONES', 'ICX_CAT_CANNOT_UPDATE_ZONES', null, p_line_number, p_request_id,
1035 fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id);
1036
1037 l_err_loc := 900;
1038 INSERT INTO icx_por_failed_lines
1039 (job_number, line_number, action, row_type, descriptor_key, descriptor_value,
1040 request_id, program_id, program_application_id, program_login_id)
1041 VALUES (p_request_id, p_line_number, p_action, 'RELATIONSHIP', 'ICX_CAT_CONTENT_ZONES',
1042 l_errored_zone_name_list, p_request_id, fnd_global.conc_program_id,
1043 fnd_global.prog_appl_id, fnd_global.conc_login_id);
1044 END IF;
1045
1046 l_err_loc := 1000;
1047 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1048 l_log_string := 'End sync_sqes_hier_change';
1049 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
1050 END IF;
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 ROLLBACK;
1054 RAISE_APPLICATION_ERROR
1055 (-20000,
1056 'Exception at ICX_CAT_SQE_PVT.sync_sqes_for_hierarchy_change(' ||
1057 l_err_loc || '), ' || SQLERRM);
1058 END sync_sqes_for_hierarchy_change;
1059
1060 -- procedure to sync the sqes for all the zones
1061 -- this will only be called during upgrade
1062 -- this is also useful for testing purposes and also useful if we
1063 -- want to re-sync all zones on any instance
1064 PROCEDURE sync_sqes_for_all_zones
1065 IS
1066 l_api_name CONSTANT VARCHAR2(30) := 'sync_sqes_for_all_zones';
1067 l_log_string VARCHAR2(32000);
1068 l_err_loc PLS_INTEGER;
1069 l_content_zone_ids ICX_TBL_NUMBER;
1070 l_content_zone_names ICX_TBL_VARCHAR240;
1071 l_supplier_attr_action_flags ICX_TBL_VARCHAR40;
1072 l_supplier_ids ICX_TBL_NUMBER;
1073 l_supplier_site_ids ICX_TBL_NUMBER;
1074 l_items_without_supplier_flags ICX_TBL_FLAG;
1075 l_category_attr_action_flags ICX_TBL_VARCHAR40;
1076 l_category_ids ICX_TBL_NUMBER;
1077 l_items_without_shop_cat_flags ICX_TBL_FLAG;
1078 l_sqe_sequences ICX_TBL_NUMBER;
1079 l_return_status VARCHAR2(1);
1080 l_current_sqe_sequence NUMBER;
1081 l_request_id NUMBER;
1082 l_errored_zone_name_list VARCHAR2(4000);
1083 l_first_errored_zone PLS_INTEGER;
1084 l_token_list VARCHAR2(4000);
1085 BEGIN
1086 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1087 l_log_string := 'Starting sync_sqes_for_all_zones';
1088 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
1089 END IF;
1090 -- first start off with no error
1091 l_first_errored_zone := 1;
1092
1093 l_err_loc := 100;
1094 -- first query all the content zones for the basic information
1095 -- we only query those that are local
1096 -- and have either include or exclude for category
1097 SELECT zones.zone_id, zonestl.name, supplier_attribute_action_flag, category_attribute_action_flag,
1098 items_without_supplier_flag, items_without_shop_catg_flag, sqe_sequence
1099 BULK COLLECT INTO l_content_zone_ids, l_content_zone_names, l_supplier_attr_action_flags,
1100 l_category_attr_action_flags, l_items_without_supplier_flags, l_items_without_shop_cat_flags, l_sqe_sequences
1101 FROM icx_cat_content_zones_b zones, icx_cat_content_zones_tl zonestl
1102 WHERE zones.type = 'LOCAL'
1103 AND zones.zone_id = zonestl.zone_id
1104 AND zonestl.language = USERENV('LANG');
1105
1106 l_err_loc := 200;
1107 -- now loop
1108 FOR i in 1..l_content_zone_ids.COUNT LOOP
1109 l_err_loc := 300;
1110 -- now get all the categories for that zone
1111 SELECT ip_category_id
1112 BULK COLLECT INTO l_category_ids
1113 FROM icx_cat_zone_secure_attributes
1114 WHERE zone_id = l_content_zone_ids(i)
1115 AND securing_attribute = 'CATEGORY';
1116
1117 l_err_loc := 400;
1118 -- now get all the suppliers and sites for that zone
1119 SELECT supplier_id, supplier_site_id
1120 BULK COLLECT INTO l_supplier_ids, l_supplier_site_ids
1121 FROM icx_cat_zone_secure_attributes
1122 WHERE zone_id = l_content_zone_ids(i)
1123 AND securing_attribute = 'SUPPLIER';
1124
1125 l_err_loc := 450;
1126 -- set the current sqe sequence
1127 l_current_sqe_sequence := l_sqe_sequences(i);
1128
1129 l_err_loc := 500;
1130 -- now call the API to create sqes for the zone
1131 create_sqes_for_zone (l_content_zone_ids(i), l_supplier_attr_action_flags(i), l_supplier_ids,
1132 l_supplier_site_ids, l_items_without_supplier_flags(i), l_category_attr_action_flags(i),
1133 l_category_ids, l_items_without_shop_cat_flags(i), l_return_status, l_current_sqe_sequence);
1134
1135 IF (l_return_status = 'S') THEN
1136 l_err_loc := 600;
1137 -- success, update the sqe sequence
1138 l_sqe_sequences(i) := l_current_sqe_sequence;
1139 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1140 l_log_string := 'Succesfully updating the sequence for content_zone: ' || l_content_zone_ids(i)
1141 || ' to sqe_sequence: ' || l_sqe_sequences(i);
1142 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1143 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
1144 END IF;
1145 ELSE
1146 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1147 l_log_string := 'Error for content_zone: ' || l_content_zone_ids(i) || ' sqe_sequence: ' || l_sqe_sequences(i);
1148 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1149 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
1150 END IF;
1151 l_err_loc := 700;
1152 -- error
1153 IF (l_first_errored_zone = 1) THEN
1154 l_first_errored_zone := 0;
1155 l_errored_zone_name_list := l_content_zone_names(i);
1156 ELSE
1157 IF (length(l_errored_zone_name_list) + length(l_content_zone_names(i)) < 3950) THEN
1158 l_errored_zone_name_list := l_errored_zone_name_list || ', ' || l_content_zone_names(i);
1159 END IF;
1160 END IF;
1161 END IF;
1162 END LOOP;
1163
1164 l_err_loc := 800;
1165 -- now update all the content zones with the new sqe sequences
1166 FORALL i IN 1..l_content_zone_ids.COUNT
1167 UPDATE icx_cat_content_zones_b
1168 SET sqe_sequence = l_sqe_sequences(i)
1169 WHERE zone_id = l_content_zone_ids(i);
1170
1171 l_err_loc := 900;
1172
1173 COMMIT;
1174
1175 l_err_loc := 1000;
1176 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1177 l_log_string := 'End sync_sqes_for_all_zones';
1178 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
1179 END IF;
1180
1181 EXCEPTION
1182 WHEN OTHERS THEN
1183 ROLLBACK;
1184 RAISE_APPLICATION_ERROR
1185 (-20000,
1186 'Exception at ICX_CAT_SQE_PVT.sync_sqes_for_all_zones(' ||
1187 l_err_loc || '), ' || SQLERRM);
1188
1189 END sync_sqes_for_all_zones;
1190
1191 END ICX_CAT_SQE_PVT;
[Home] [Help]