1 PACKAGE BODY ICX_CAT_SQE_PVT AS
2 /* $Header: ICXVSQEB.pls 120.5.12020000.4 2013/02/08 22:32:00 bpulivar ship $*/
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 --Bug 13527354
361 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
362 l_log_string := 'End construct_exprs_for_zone: Internal String:'
363 || x_int_intermedia_expression;
364 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name,l_api_name,l_log_string);
365 l_log_string:= ' Purchase String: ' || x_purch_intermedia_expression;
366 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name,l_api_name,l_log_string);
367 l_log_string:= ' Both String: ' || x_both_intermedia_expression;
368 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
369 END IF;
370 EXCEPTION
371 WHEN OTHERS THEN
372 ROLLBACK;
373 RAISE_APPLICATION_ERROR
374 (-20000,
375 'Exception at ICX_CAT_SQE_PVT.construct_exprs_for_zone(' ||
376 l_err_loc || '), ' || SQLERRM);
377 END construct_exprs_for_zone;
378
379 -- procedure to constuct the supplier and site expression for a given zone
380 -- takes in the required parameters and returns the supplier and site expression
381 PROCEDURE construct_supp_and_site_expr
382 (
383 p_supplier_attr_action_flag IN VARCHAR2,
384 p_supplier_ids IN ICX_TBL_NUMBER,
385 p_supplier_site_ids IN ICX_TBL_NUMBER,
386 p_items_without_supplier_flag IN VARCHAR2,
387 x_supplier_and_site_expr OUT NOCOPY VARCHAR2
388 )
389 IS
390 l_api_name CONSTANT VARCHAR2(30) := 'construct_supp_and_site_expr';
391 l_log_string VARCHAR2(32000);
392 l_err_loc PLS_INTEGER;
393 l_supplier_id_list VARCHAR2(32000);
394 l_supplier_site_id_list VARCHAR2(32000);
395 l_exclude_expr VARCHAR2(100);
396 l_supplier_and_site_expr VARCHAR2(32000);
397 BEGIN
398 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
399 l_log_string := 'Starting construct_supp_and_site_expr';
400 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
401 END IF;
402 l_err_loc := 100;
403
404 -- initialize the expressions to ''
405 l_supplier_id_list := '';
406 l_supplier_site_id_list := '';
407 x_supplier_and_site_expr := '';
408 l_exclude_expr := '((''BOTH'',''INTERNAL'',''PURCHASE'') within item_type)';
409
410 l_err_loc := 200;
411 -- we only need the supplier and site expr
412 -- if it is either include some or exclude some
413 IF (p_supplier_attr_action_flag in ('INCLUDE', 'EXCLUDE')) THEN
414 l_err_loc := 300;
415 -- now loop through the lists and construct the expr
416 FOR i in 1..p_supplier_ids.COUNT LOOP
417 l_err_loc := 400;
418 IF (p_supplier_site_ids(i) IS NULL) THEN
419 l_err_loc := 500;
420 -- supplier site not provided append supplier_id to the supplier id list
421 IF (l_supplier_id_list IS NULL) THEN
422 l_err_loc := 600;
423 l_supplier_id_list := p_supplier_ids(i);
424 ELSE
425 l_err_loc := 700;
426 l_supplier_id_list := l_supplier_id_list || ',' || p_supplier_ids(i);
427 END IF;
428 ELSE
429 l_err_loc := 800;
430 -- supplier site id provided so we just use the site id and append to its list
431 IF (l_supplier_site_id_list IS NULL) THEN
432 l_err_loc := 900;
433 l_supplier_site_id_list := p_supplier_site_ids(i);
434 ELSE
435 l_err_loc := 1000;
436 l_supplier_site_id_list := l_supplier_site_id_list || ',' || p_supplier_site_ids(i);
437 END IF;
438 END IF;
439 END LOOP;
440 END IF;
441
442 l_err_loc := 1100;
443 IF (p_supplier_attr_action_flag = 'INCLUDE_ALL') THEN
444 IF (p_items_without_supplier_flag = 'N') THEN
445 -- if we include all but don't check the checkbox then we want to exclude -2
446 l_err_loc := 1200;
447 l_supplier_id_list := '{-2}';
448 l_err_loc := 1300;
449 -- supplier id list is -2 here and supplier site id list is null
450 x_supplier_and_site_expr := l_exclude_expr || ' ~ ((' || l_supplier_id_list || ') within supid)';
451 END IF;
452 END IF;
453
454 l_err_loc := 1400;
455 IF (p_supplier_attr_action_flag = 'INCLUDE') THEN
456 l_err_loc := 1500;
457 IF (p_items_without_supplier_flag = 'Y') THEN
458 -- if we include some and check the checkbox then we want to include -2
459 IF (l_supplier_id_list IS NULL) THEN
460 l_err_loc := 1600;
461 l_supplier_id_list := '{-2}';
462 ELSE
463 l_err_loc := 1700;
464 l_supplier_id_list := l_supplier_id_list || ',{-2}';
465 END IF;
466 END IF;
467
468 l_err_loc := 1800;
469 IF (l_supplier_site_id_list IS NULL) THEN
470 l_err_loc := 1900;
471 -- in this case we will have a supplier_id_list
472 x_supplier_and_site_expr := '(' || l_supplier_id_list || ') within supid';
473 ELSE
474 -- here we may or may not have a supplier_id_list
475 IF (l_supplier_id_list IS NULL) THEN
476 l_err_loc := 1950;
477 x_supplier_and_site_expr := '(' || l_supplier_site_id_list || ') within siteid';
478 ELSE
479 l_err_loc := 2000;
480 x_supplier_and_site_expr := '((' || l_supplier_id_list || ') within supid),(('
481 || l_supplier_site_id_list || ') within siteid)';
482 END IF;
483 END IF;
484
485 END IF;
486
487 l_err_loc := 2100;
488 IF (p_supplier_attr_action_flag = 'EXCLUDE') THEN
489 IF (p_items_without_supplier_flag = 'N') THEN
490 -- now if it is exclude and we don't check the checkbox then we want to exclude -2
491 l_err_loc := 2200;
492 IF (l_supplier_id_list IS NULL) THEN
493 l_err_loc := 2300;
494 l_supplier_id_list := '{-2}';
495 ELSE
496 l_err_loc := 2400;
497 l_supplier_id_list := l_supplier_id_list || ',{-2}';
498 END IF;
499 END IF;
500
501 l_err_loc := 2500;
502 IF (l_supplier_site_id_list IS NULL) THEN
503 -- in this case we will have a supplier_id_list
504 l_err_loc := 2600;
505 x_supplier_and_site_expr := '(' || l_supplier_id_list || ') within supid';
506 ELSE
507 -- here we may or may not have a supplier_id_list
508 IF (l_supplier_id_list IS NULL) THEN
509 l_err_loc := 2650;
510 x_supplier_and_site_expr := '(' || l_supplier_site_id_list || ') within siteid';
511 ELSE
512 l_err_loc := 2700;
513 x_supplier_and_site_expr := '((' || l_supplier_id_list || ') within supid),(('
514 || l_supplier_site_id_list || ') within siteid)';
515 END IF;
516 END IF;
517
518 l_err_loc := 2800;
519 -- finally we negate it using the fixed exclude expr
520 x_supplier_and_site_expr := l_exclude_expr || ' ~ (' || x_supplier_and_site_expr || ')';
521 END IF;
522
523 l_err_loc := 2900;
524 IF (p_supplier_attr_action_flag = 'EXCLUDE_ALL') THEN
525 IF (p_items_without_supplier_flag = 'Y') THEN
526 -- if we exclude all and check the checkbox then we want to include -2
527 l_err_loc := 3000;
528 l_supplier_id_list := '{-2}';
529 l_err_loc := 3100;
530 -- supplier id list is -2 here and supplier site id list is null
531 x_supplier_and_site_expr := '(' || l_supplier_id_list || ') within supid';
532 END IF;
533 END IF;
534
535 l_err_loc := 3200;
536 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
537 l_log_string := 'End construct_supp_and_site_expr: Supplier and site expr is:'
538 || x_supplier_and_site_expr;
539 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
540 END IF;
541 EXCEPTION
542 WHEN OTHERS THEN
543 ROLLBACK;
544 RAISE_APPLICATION_ERROR
545 (-20000,
546 'Exception at ICX_CAT_SQE_PVT.construct_supp_and_site_expr(' ||
547 l_err_loc || '), ' || SQLERRM);
548 END construct_supp_and_site_expr;
549
550
551 -- procedure to constuct the category expression for a given zone
552 -- takes in the required parameters and returns the category expression
553 PROCEDURE construct_category_expr
554 (
555 p_category_attr_action_flag IN VARCHAR2,
556 p_category_ids IN ICX_TBL_NUMBER,
557 p_items_without_shop_catg_flag IN VARCHAR2,
558 x_category_expr OUT NOCOPY VARCHAR2
559 )
560 IS
561 l_api_name CONSTANT VARCHAR2(30) := 'construct_category_expr';
562 l_log_string VARCHAR2(32000);
563 l_err_loc PLS_INTEGER;
564 l_exclude_expr VARCHAR2(100);
565 l_category_ids ICX_TBL_NUMBER;
566 l_category_id_list VARCHAR2(32000);
567 l_current_category_id_list VARCHAR2(32000);
568 BEGIN
569 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
570 l_log_string := 'Starting construct_category_expr';
571 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
572 END IF;
573 l_err_loc := 100;
574 -- initialize the expressions to ''
575 l_category_id_list := '';
576 x_category_expr := '';
577 l_exclude_expr := '((''BOTH'',''INTERNAL'',''PURCHASE'') within item_type)';
578
579 l_err_loc := 200;
580 IF (p_category_attr_action_flag = 'INCLUDE_ALL') THEN
581 l_err_loc := 300;
582 IF (p_items_without_shop_catg_flag IS NULL OR p_items_without_shop_catg_flag = 'N') THEN
583 l_err_loc := 400;
584 -- we need to exclude the -2 categories since the checkbox is not checked and it is
585 -- include all
586 x_category_expr := l_exclude_expr || ' ~ ({-2} within ipcatid)';
587 END IF;
588 ELSE
589 l_err_loc := 500;
590 -- now loop through the list and construct the category id list
591 FOR i in 1..p_category_ids.COUNT LOOP
592 l_err_loc := 600;
593 --reset value
594 -- bug no 13543091
595 l_current_category_id_list := NULL;
596 -- now for each category, we have to either get the category itself (if it is an item category)
597 -- or get the list of item categories under it
598 SELECT distinct rt_category_id
599 BULK COLLECT INTO l_category_ids
600 FROM icx_cat_categories_tl
601 WHERE type = 2
602 AND rt_category_id in (SELECT child_category_id
603 FROM icx_cat_browse_trees
604 START WITH parent_category_id = p_category_ids(i)
605 CONNECT BY NOCYCLE PRIOR child_category_id = parent_category_id
606 UNION ALL
607 SELECT p_category_ids(i)
608 FROM dual);
609
610 l_err_loc := 650;
611 -- now construct the list for that browsing category or for item category it is just itself
612 FOR j in 1..l_category_ids.COUNT LOOP
613 IF (j = 1) THEN
614 l_err_loc := 700;
615 -- if it is the first one then the list is just that
616 l_current_category_id_list := l_category_ids(j);
617 ELSE
618 l_err_loc := 750;
619 -- else we append it to the previous one
620 l_current_category_id_list := l_current_category_id_list || ',' || l_category_ids(j);
621 END IF;
622 END LOOP;
623
624 -- now we append the current list to the full list
625 l_err_loc := 770;
626 -- bug no 13543091
627 -- bug no 13542211
628 IF (l_category_id_list IS NULL OR l_category_id_list = '') THEN
629 l_err_loc := 800;
630 -- if it is the first one then the list is just that
631 l_category_id_list := l_current_category_id_list;
632 ELSE
633 l_err_loc := 850;
634 -- else we append it to the previous one
635 IF( l_current_category_id_list IS NOT NULL) THEN
636 l_category_id_list := l_category_id_list || ',' || l_current_category_id_list;
637 END IF;
638 END IF;
639 END LOOP;
640
641 l_err_loc := 900;
642 IF (p_category_attr_action_flag = 'INCLUDE') THEN
643 l_err_loc := 1000;
644 IF (p_items_without_shop_catg_flag = 'Y') THEN
645 l_err_loc := 1100;
646 -- if it is include and we have checked the checkbox, then we include -2 as well
647 IF (l_category_id_list IS NULL) THEN
648 l_category_id_list := '{-2}';
649 ELSE
650 l_category_id_list := l_category_id_list || ',{-2}';
651 END IF;
652 END IF;
653 l_err_loc := 1200;
654 -- if category id list is empty then that means we exclude everything
655 -- so we put some invalid value into the category id expr, so we put -1
656 IF (l_category_id_list IS NULL) THEN
657 l_err_loc := 1230;
658 l_category_id_list := '{-1}';
659 END IF;
660 l_err_loc := 1250;
661 x_category_expr := '(' || l_category_id_list || ') within ipcatid';
662 END IF;
663
664 l_err_loc := 1300;
665 IF (p_category_attr_action_flag = 'EXCLUDE') THEN
666 l_err_loc := 1400;
667 IF (p_items_without_shop_catg_flag IS NULL OR p_items_without_shop_catg_flag = 'N') THEN
668 l_err_loc := 1500;
669 -- if it is exclude and we have not checked the checkbox, then we exclude -2 as well
670 IF (l_category_id_list IS NULL) THEN
671 l_category_id_list := '{-2}';
672 ELSE
673 l_category_id_list := l_category_id_list || ',{-2}';
674 END IF;
675 END IF;
676 l_err_loc := 1600;
677 -- if category id list is empty then that means we include everything
678 -- so we leave the category expr as null
679 IF (l_category_id_list IS NULL) THEN
680 l_err_loc := 1630;
681 x_category_expr := '';
682 ELSE
683 l_err_loc := 1650;
684 x_category_expr := l_exclude_expr || ' ~ ((' || l_category_id_list || ') within ipcatid)';
685 END IF;
686 END IF;
687 l_err_loc := 1700;
688 END IF;
689 l_err_loc := 1800;
690 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
691 l_log_string := 'End construct_category_expr: Category expr is:'
692 || x_category_expr;
693 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
694 END IF;
695 EXCEPTION
696 WHEN OTHERS THEN
697 ROLLBACK;
698 RAISE_APPLICATION_ERROR
699 (-20000,
700 'Exception at ICX_CAT_SQE_PVT.construct_category_expr(' ||
701 l_err_loc || '), ' || SQLERRM);
702 END construct_category_expr;
703
704 -- procedure to purge the deleted sqes
705 -- this purges all sqes that have been deleted more than a day ago
706 PROCEDURE purge_deleted_sqes
707 IS
708 l_api_name CONSTANT VARCHAR2(30) := 'purge_deleted_sqes';
709 l_log_string VARCHAR2(32000);
710 l_err_loc PLS_INTEGER;
711 l_sqe_sequences ICX_TBL_NUMBER;
712 BEGIN
713 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
714 l_log_string := 'Starting purge_deleted_sqes';
715 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
716 END IF;
717
718 l_err_loc := 100;
719
720 -- first select all the sqe sequences that have been deleted more
721 -- than a day ago
722 SELECT sqe_sequence
723 BULK COLLECT INTO l_sqe_sequences
724 FROM icx_cat_deleted_sqes
725 WHERE creation_date < sysdate - 1;
726
727 l_err_loc := 200;
728
729 -- now delete the three sqes for each of them
730 -- we put this in a separate begin end block
731 -- and catch the exception since the remove sqe automatically commits
732 -- so we may have removed the sqes but not committed the delete from the
733 -- deleted sqes table. in this case the next time around we will try to remove
734 -- the same sqes which will throw an exception. to avoid this we catch the exception
735 -- and do nothing
736 BEGIN
737 FOR i in 1..l_sqe_sequences.COUNT LOOP
738 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
739 l_log_string := 'Removing sqes for sqe_sequence: ' || l_sqe_sequences(i);
740 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
741 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
742 END IF;
743 l_err_loc := 300;
744 ctx_query.remove_sqe('icxzi' || l_sqe_sequences(i));
745 ctx_query.remove_sqe('icxzp' || l_sqe_sequences(i));
746 ctx_query.remove_sqe('icxzb' || l_sqe_sequences(i));
747 END LOOP;
748 EXCEPTION
749 WHEN OTHERS THEN
750 NULL;
751 END;
752
753 l_err_loc := 400;
754 -- now remove the sqe sequences from the table
755 FORALL i in 1..l_sqe_sequences.COUNT
756 DELETE FROM icx_cat_deleted_sqes
757 WHERE sqe_sequence = l_sqe_sequences(i);
758
759 l_err_loc := 500;
760 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
761 l_log_string := 'End purge_deleted_sqes';
762 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
763 END IF;
764
765 EXCEPTION
766 WHEN OTHERS THEN
767 ROLLBACK;
768 RAISE_APPLICATION_ERROR
769 (-20000,
770 'Exception at ICX_CAT_SQE_PVT.purge_deleted_sqes(' ||
771 l_err_loc || '), ' || SQLERRM);
772 END purge_deleted_sqes;
773
774 -- procedure to sync sqes for all content zones
775 -- called for hierarchy changes from a concurrent program
776 -- or from the schema loader
777 -- this will recreate sqes for all the content zones
778 -- if some content zones have errors since the expression is too long
779 -- the job will be errored out with a message specifying which zones failed
780 -- the successful zones will however be updated
781 PROCEDURE sync_sqes_hier_change_internal
782 (
783 x_return_status OUT NOCOPY VARCHAR2,
784 x_errored_zone_name_list OUT NOCOPY VARCHAR2
785 )
786 IS
787 l_api_name CONSTANT VARCHAR2(30) := 'sync_sqes_hier_change_internal';
788 l_log_string VARCHAR2(32000);
789 l_err_loc PLS_INTEGER;
790 l_content_zone_ids ICX_TBL_NUMBER;
791 l_content_zone_names ICX_TBL_VARCHAR240;
792 l_supplier_attr_action_flags ICX_TBL_VARCHAR40;
793 l_supplier_ids ICX_TBL_NUMBER;
794 l_supplier_site_ids ICX_TBL_NUMBER;
795 l_items_without_supplier_flags ICX_TBL_FLAG;
796 l_category_attr_action_flags ICX_TBL_VARCHAR40;
797 l_category_ids ICX_TBL_NUMBER;
798 l_items_without_shop_cat_flags ICX_TBL_FLAG;
799 l_sqe_sequences ICX_TBL_NUMBER;
800 l_return_status VARCHAR2(1);
801 l_current_sqe_sequence NUMBER;
802 l_first_errored_zone PLS_INTEGER;
803 BEGIN
804 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
805 l_log_string := 'Starting sync_sqes_hier_change_internal';
806 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
807 END IF;
808 l_first_errored_zone := 1;
809 x_return_status := 'S';
810
811 l_err_loc := 100;
812 -- first query all the content zones for the basic information
813 -- we only query those that are local
814 -- and have either include or exclude for category
815 SELECT zones.zone_id, zonestl.name, supplier_attribute_action_flag, category_attribute_action_flag,
816 items_without_supplier_flag, items_without_shop_catg_flag, sqe_sequence
817 BULK COLLECT INTO l_content_zone_ids, l_content_zone_names, l_supplier_attr_action_flags,
818 l_category_attr_action_flags, l_items_without_supplier_flags, l_items_without_shop_cat_flags, l_sqe_sequences
819 FROM icx_cat_content_zones_b zones, icx_cat_content_zones_tl zonestl
820 WHERE zones.type = 'LOCAL'
821 AND zones.category_attribute_action_flag IN ('INCLUDE', 'EXCLUDE')
822 AND zones.zone_id = zonestl.zone_id
823 AND zonestl.language = USERENV('LANG');
824
825 l_err_loc := 200;
826 -- now loop
827 FOR i in 1..l_content_zone_ids.COUNT LOOP
828 l_err_loc := 300;
829 -- now get all the categories for that zone
830 SELECT ip_category_id
831 BULK COLLECT INTO l_category_ids
832 FROM icx_cat_zone_secure_attributes
833 WHERE zone_id = l_content_zone_ids(i)
834 AND securing_attribute = 'CATEGORY';
835
836 l_err_loc := 400;
837 -- now get all the suppliers and sites for that zone
838 SELECT supplier_id, supplier_site_id
839 BULK COLLECT INTO l_supplier_ids, l_supplier_site_ids
840 FROM icx_cat_zone_secure_attributes
841 WHERE zone_id = l_content_zone_ids(i)
842 AND securing_attribute = 'SUPPLIER';
843
844 l_err_loc := 450;
845 -- set the current sqe sequence
846 l_current_sqe_sequence := l_sqe_sequences(i);
847
848 l_err_loc := 500;
849 -- now call the API to create sqes for the zone
850 create_sqes_for_zone (l_content_zone_ids(i), l_supplier_attr_action_flags(i), l_supplier_ids,
851 l_supplier_site_ids, l_items_without_supplier_flags(i), l_category_attr_action_flags(i),
852 l_category_ids, l_items_without_shop_cat_flags(i), l_return_status, l_current_sqe_sequence);
853
854 IF (l_return_status = 'S') THEN
855 l_err_loc := 600;
856 -- success, update the sqe sequence
857 l_sqe_sequences(i) := l_current_sqe_sequence;
858 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
859 l_log_string := 'Succesfully updating the sequence for content_zone: ' || l_content_zone_ids(i)
860 || ' to sqe_sequence: ' || l_sqe_sequences(i);
861 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
862 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
863 END IF;
864 ELSE
865 l_err_loc := 700;
866 x_return_status := 'E';
867 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
868 l_log_string := 'Error for content_zone: ' || l_content_zone_ids(i) || ' sqe_sequence: ' || l_sqe_sequences(i);
869 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
870 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
871 END IF;
872 -- error
873 IF (l_first_errored_zone = 1) THEN
874 l_first_errored_zone := 0;
875 x_errored_zone_name_list := l_content_zone_names(i);
876 ELSE
877 IF (length(x_errored_zone_name_list) + length(l_content_zone_names(i)) < 3950) THEN
878 x_errored_zone_name_list := x_errored_zone_name_list || ', ' || l_content_zone_names(i);
879 END IF;
880 END IF;
881 END IF;
882 END LOOP;
883
884 l_err_loc := 800;
885 -- now update all the content zones with the new sqe sequences
886 FORALL i IN 1..l_content_zone_ids.COUNT
887 UPDATE icx_cat_content_zones_b
888 SET sqe_sequence = l_sqe_sequences(i)
889 WHERE zone_id = l_content_zone_ids(i);
890
891 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
892 l_log_string := 'End sync_sqes_hier_change_internal';
893 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
894 END IF;
895 EXCEPTION
896 WHEN OTHERS THEN
897 ROLLBACK;
898 RAISE_APPLICATION_ERROR
899 (-20000,
900 'Exception at ICX_CAT_SQE_PVT.sync_sqes_hier_change_internal(' ||
901 l_err_loc || '), ' || SQLERRM);
902
903 END sync_sqes_hier_change_internal;
904
905
906 -- procedure to sync sqes for all content zones
907 -- called for hierarchy changes from a concurrent program
908 -- this will call the main api which does the actual sync
909 -- this api in addition updates the failed line messages
910 -- and the job status
911 PROCEDURE sync_sqes_for_hierarchy_change
912 (
913 x_errbuf OUT NOCOPY VARCHAR2,
914 x_retcode OUT NOCOPY NUMBER
915 )
916 IS
917 l_api_name CONSTANT VARCHAR2(30) := 'sync_sqes_for_hierarchy_change';
918 l_log_string VARCHAR2(32000);
919 l_err_loc PLS_INTEGER;
920 l_request_id NUMBER;
921 l_return_status VARCHAR2(1);
922 l_errored_zone_name_list VARCHAR2(4000);
923 l_token_list VARCHAR2(4000);
924 BEGIN
925 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
926 l_log_string := 'Starting sync_sqes_for_hierarchy_change';
927 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
928 END IF;
929 l_err_loc := 100;
930
931 -- first start off with no error
932 x_retcode := 0;
933 x_errbuf := '';
934
935 l_err_loc := 200;
936 -- get the concurrent request Id
937 l_request_id := fnd_global.conc_request_id;
938
939 l_err_loc := 300;
940 -- update the job status to running
941 ICX_CAT_SCHEMA_UPLOAD_PVT.update_job_status(l_request_id, 'RUNNING');
942
943 l_err_loc := 400;
944 -- call the main API to do the sync
945 sync_sqes_hier_change_internal(l_return_status, l_errored_zone_name_list);
946
947 l_err_loc := 500;
948 -- set job status depending on whether there is an error on not
949 IF (l_return_status = 'E') THEN
950 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
951 l_log_string := 'Could not sync sqes for some zones. Updating the job to error.';
952 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
953 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
954 END IF;
955 l_err_loc := 600;
956 -- update the job status to error
957 x_retcode := 1;
958 ICX_CAT_SCHEMA_UPLOAD_PVT.update_job_status(l_request_id, 'ERROR');
959
960 l_err_loc := 700;
961 l_token_list := 'ZONE_NAMES:' || l_errored_zone_name_list;
962
963 l_err_loc := 800;
964 INSERT INTO icx_por_failed_line_messages
965 (job_number, descriptor_key, message_name, token_list, line_number, request_id, program_id,
966 program_application_id, program_login_id)
967 VALUES (l_request_id, 'OTHER', 'ICX_CAT_CANNOT_UPDATE_CZS_ERR', l_token_list, 1, l_request_id,
968 fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id);
969 ELSE
970 l_err_loc := 900;
971 -- update the job status to completed
972 ICX_CAT_SCHEMA_UPLOAD_PVT.update_job_status(l_request_id, 'COMPLETED');
973 END IF;
974
975 l_err_loc := 1000;
976
977 COMMIT;
978
979 l_err_loc := 1100;
980 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
981 l_log_string := 'End sync_sqes_for_hierarchy_change';
982 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
983 END IF;
984
985 EXCEPTION
986 WHEN OTHERS THEN
987 ROLLBACK;
988
989 x_retcode := 2;
990 x_errbuf := 'Exception at ICX_CAT_SQE_PVT.sync_sqes_for_hierarchy_change(' ||
991 l_err_loc || '), ' || SQLERRM;
992
993 -- update the job status to error
994 ICX_CAT_SCHEMA_UPLOAD_PVT.update_job_status(l_request_id, 'ERROR');
995
996 INSERT INTO icx_por_failed_line_messages
997 (job_number, descriptor_key, message_name, token_list, line_number, request_id, program_id,
998 program_application_id, program_login_id)
999 VALUES (l_request_id, 'OTHER', 'ICX_CAT_HIER_UNEXPECTED_ERR', l_token_list, 1, l_request_id,
1000 fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id);
1001
1002 COMMIT;
1003
1004 END sync_sqes_for_hierarchy_change;
1005
1006 -- procedure to sync sqes for all content zones
1007 -- called for hierarchy changes from the schema loader
1008 -- this will call the main api which does the actual sync
1009 -- this api in addition updates the failed line messages
1010 -- and the failed lines table
1011 PROCEDURE sync_sqes_for_hierarchy_change
1012 (
1013 p_request_id IN NUMBER,
1014 p_line_number IN NUMBER,
1015 p_action IN VARCHAR2,
1016 x_return_status OUT NOCOPY VARCHAR2
1017 )
1018 IS
1019 l_api_name CONSTANT VARCHAR2(30) := 'sync_sqes_for_hierarchy_change';
1020 l_log_string VARCHAR2(32000);
1021 l_err_loc PLS_INTEGER;
1022 l_request_id NUMBER;
1023 l_errored_zone_name_list VARCHAR2(4000);
1024 BEGIN
1025 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1026 l_log_string := 'Starting sync_sqes_hier_change';
1027 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
1028 END IF;
1029 l_err_loc := 100;
1030 -- call the main API to do the sync
1031 sync_sqes_hier_change_internal(x_return_status, l_errored_zone_name_list);
1032
1033 l_err_loc := 500;
1034 -- if there is an error then we insert into failed lines and failed line messages
1035 IF (x_return_status = 'E') THEN
1036 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1037 l_log_string := 'Could not sync sqes for some zones. Returning error.';
1038 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1039 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
1040 END IF;
1041 l_err_loc := 800;
1042 INSERT INTO icx_por_failed_line_messages
1043 (job_number, descriptor_key, message_name, token_list, line_number, request_id, program_id,
1044 program_application_id, program_login_id)
1045 VALUES (p_request_id, 'ICX_CAT_CONTENT_ZONES', 'ICX_CAT_CANNOT_UPDATE_ZONES', null, p_line_number, p_request_id,
1046 fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id);
1047
1048 l_err_loc := 900;
1049 INSERT INTO icx_por_failed_lines
1050 (job_number, line_number, action, row_type, descriptor_key, descriptor_value,
1051 request_id, program_id, program_application_id, program_login_id)
1052 VALUES (p_request_id, p_line_number, p_action, 'RELATIONSHIP', 'ICX_CAT_CONTENT_ZONES',
1053 l_errored_zone_name_list, p_request_id, fnd_global.conc_program_id,
1054 fnd_global.prog_appl_id, fnd_global.conc_login_id);
1055 END IF;
1056
1057 l_err_loc := 1000;
1058 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1059 l_log_string := 'End sync_sqes_hier_change';
1060 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
1061 END IF;
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064 ROLLBACK;
1065 RAISE_APPLICATION_ERROR
1066 (-20000,
1067 'Exception at ICX_CAT_SQE_PVT.sync_sqes_for_hierarchy_change(' ||
1068 l_err_loc || '), ' || SQLERRM);
1069 END sync_sqes_for_hierarchy_change;
1070
1071 -- procedure to sync the sqes for all the zones
1072 -- this will only be called during upgrade
1073 -- this is also useful for testing purposes and also useful if we
1074 -- want to re-sync all zones on any instance
1075 PROCEDURE sync_sqes_for_all_zones
1076 IS
1077 l_api_name CONSTANT VARCHAR2(30) := 'sync_sqes_for_all_zones';
1078 l_log_string VARCHAR2(32000);
1079 l_err_loc PLS_INTEGER;
1080 l_content_zone_ids ICX_TBL_NUMBER;
1081 l_content_zone_names ICX_TBL_VARCHAR240;
1082 l_supplier_attr_action_flags ICX_TBL_VARCHAR40;
1083 l_supplier_ids ICX_TBL_NUMBER;
1084 l_supplier_site_ids ICX_TBL_NUMBER;
1085 l_items_without_supplier_flags ICX_TBL_FLAG;
1086 l_category_attr_action_flags ICX_TBL_VARCHAR40;
1087 l_category_ids ICX_TBL_NUMBER;
1088 l_items_without_shop_cat_flags ICX_TBL_FLAG;
1089 l_sqe_sequences ICX_TBL_NUMBER;
1090 l_return_status VARCHAR2(1);
1091 l_current_sqe_sequence NUMBER;
1092 l_request_id NUMBER;
1093 l_errored_zone_name_list VARCHAR2(4000);
1094 l_first_errored_zone PLS_INTEGER;
1095 l_token_list VARCHAR2(4000);
1096 BEGIN
1097 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1098 l_log_string := 'Starting sync_sqes_for_all_zones';
1099 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
1100 END IF;
1101 -- first start off with no error
1102 l_first_errored_zone := 1;
1103
1104 l_err_loc := 100;
1105 -- first query all the content zones for the basic information
1106 -- we only query those that are local
1107 -- and have either include or exclude for category
1108 SELECT zones.zone_id, zonestl.name, supplier_attribute_action_flag, category_attribute_action_flag,
1109 items_without_supplier_flag, items_without_shop_catg_flag, sqe_sequence
1110 BULK COLLECT INTO l_content_zone_ids, l_content_zone_names, l_supplier_attr_action_flags,
1111 l_category_attr_action_flags, l_items_without_supplier_flags, l_items_without_shop_cat_flags, l_sqe_sequences
1112 FROM icx_cat_content_zones_b zones, icx_cat_content_zones_tl zonestl
1113 WHERE zones.type = 'LOCAL'
1114 AND zones.zone_id = zonestl.zone_id
1115 AND zonestl.language = USERENV('LANG');
1116
1117 l_err_loc := 200;
1118 -- now loop
1119 FOR i in 1..l_content_zone_ids.COUNT LOOP
1120 l_err_loc := 300;
1121 -- now get all the categories for that zone
1122 SELECT ip_category_id
1123 BULK COLLECT INTO l_category_ids
1124 FROM icx_cat_zone_secure_attributes
1125 WHERE zone_id = l_content_zone_ids(i)
1126 AND securing_attribute = 'CATEGORY';
1127
1128 l_err_loc := 400;
1129 -- now get all the suppliers and sites for that zone
1130 SELECT supplier_id, supplier_site_id
1131 BULK COLLECT INTO l_supplier_ids, l_supplier_site_ids
1132 FROM icx_cat_zone_secure_attributes
1133 WHERE zone_id = l_content_zone_ids(i)
1134 AND securing_attribute = 'SUPPLIER';
1135
1136 l_err_loc := 450;
1137 -- set the current sqe sequence
1138 l_current_sqe_sequence := l_sqe_sequences(i);
1139
1140 l_err_loc := 500;
1141 -- now call the API to create sqes for the zone
1142 create_sqes_for_zone (l_content_zone_ids(i), l_supplier_attr_action_flags(i), l_supplier_ids,
1143 l_supplier_site_ids, l_items_without_supplier_flags(i), l_category_attr_action_flags(i),
1144 l_category_ids, l_items_without_shop_cat_flags(i), l_return_status, l_current_sqe_sequence);
1145
1146 IF (l_return_status = 'S') THEN
1147 l_err_loc := 600;
1148 -- success, update the sqe sequence
1149 l_sqe_sequences(i) := l_current_sqe_sequence;
1150 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1151 l_log_string := 'Succesfully updating the sequence for content_zone: ' || l_content_zone_ids(i)
1152 || ' to sqe_sequence: ' || l_sqe_sequences(i);
1153 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1154 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
1155 END IF;
1156 ELSE
1157 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1158 l_log_string := 'Error for content_zone: ' || l_content_zone_ids(i) || ' sqe_sequence: ' || l_sqe_sequences(i);
1159 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1160 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name), l_log_string);
1161 END IF;
1162 l_err_loc := 700;
1163 -- error
1164 IF (l_first_errored_zone = 1) THEN
1165 l_first_errored_zone := 0;
1166 l_errored_zone_name_list := l_content_zone_names(i);
1167 ELSE
1168 IF (length(l_errored_zone_name_list) + length(l_content_zone_names(i)) < 3950) THEN
1169 l_errored_zone_name_list := l_errored_zone_name_list || ', ' || l_content_zone_names(i);
1170 END IF;
1171 END IF;
1172 END IF;
1173 END LOOP;
1174
1175 l_err_loc := 800;
1176 -- now update all the content zones with the new sqe sequences
1177 FORALL i IN 1..l_content_zone_ids.COUNT
1178 UPDATE icx_cat_content_zones_b
1179 SET sqe_sequence = l_sqe_sequences(i)
1180 WHERE zone_id = l_content_zone_ids(i);
1181
1182 l_err_loc := 900;
1183
1184 COMMIT;
1185
1186 l_err_loc := 1000;
1187 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1188 l_log_string := 'End sync_sqes_for_all_zones';
1189 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, l_log_string);
1190 END IF;
1191
1192 EXCEPTION
1193 WHEN OTHERS THEN
1194 ROLLBACK;
1195 RAISE_APPLICATION_ERROR
1196 (-20000,
1197 'Exception at ICX_CAT_SQE_PVT.sync_sqes_for_all_zones(' ||
1198 l_err_loc || '), ' || SQLERRM);
1199
1200 END sync_sqes_for_all_zones;
1201
1202 END ICX_CAT_SQE_PVT;
[Home] [Help]