[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT_POPULATE_CTXSTRING_PVT
Source
1 PACKAGE BODY ICX_CAT_POPULATE_CTXSTRING_PVT AS
2 /* $Header: ICXVPCSB.pls 120.7 2006/08/14 23:26:36 rwidjaja noship $*/
3
4 TYPE g_csr_type IS REF CURSOR;
5
6 -- Constants
7 G_PKG_NAME CONSTANT VARCHAR2(30) :='ICX_CAT_POPULATE_CTXSTRING_PVT';
8 g_metadataTblFormed BOOLEAN := FALSE;
9 g_special_metadata_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_metadata_tbl_type;
10 g_regular_nontl_metadata_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_metadata_tbl_type;
11 g_regular_tl_metadata_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_metadata_tbl_type;
12
13 -- populate Row with sequence: 1, 3, 6
14 -- Sequence 1 --> Mandatory Row
15 -- Sequence 3 --> Internal Item Number
16 -- Sequence 6 --> Shopping Category
17 -- The above are the only ones that will be populated for
18 -- all data sources including master items
19 -- Values of p_repopulate_at_seq will be one among(1, 3, 6)
20 PROCEDURE popCtxBaseSpecAttForAllSrc
21 ( p_repopulate_at_seq IN NUMBER ,
22 p_special_ctx_sql_tbl IN ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type
23 )
24 IS
25 l_api_name CONSTANT VARCHAR2(30) := 'popCtxBaseSpecAttForAllSrc';
26 l_err_loc PLS_INTEGER;
27 l_rowid_tbl DBMS_SQL.UROWID_TABLE;
28 l_csr_var g_csr_type;
29 l_ctx_sqlstring_rec ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_rec_type;
30 l_ctx_sql_string VARCHAR2(4000) := NULL;
31 l_sequence NUMBER;
32 l_csr_handle NUMBER;
33 l_status PLS_INTEGER;
34 BEGIN
35 l_err_loc := 100;
36 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
37 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
38 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
39 'p_repopulate_at_seq:' || p_repopulate_at_seq);
40 END IF;
41
42 l_err_loc := 200;
43 OPEN l_csr_var FOR
44 SELECT ROWID
45 FROM icx_cat_items_ctx_hdrs_tlp;
46
47 LOOP
48 l_err_loc := 300;
49 l_rowid_tbl.DELETE;
50
51 l_err_loc := 400;
52 FETCH l_csr_var
53 BULK COLLECT INTO l_rowid_tbl
54 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
55
56 l_err_loc := 500;
57 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
58 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
59 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
60 'fetched from getItemRowsCsr rowcount:' ||l_rowid_tbl.COUNT);
61 END IF;
62
63 l_err_loc := 600;
64 EXIT WHEN l_rowid_tbl.COUNT = 0;
65
66 l_err_loc := 700;
67 FOR i IN 1..p_special_ctx_sql_tbl.COUNT LOOP
68 l_err_loc := 800;
69 l_ctx_sqlstring_rec := p_special_ctx_sql_tbl(i);
70 l_sequence := l_ctx_sqlstring_rec.bind_sequence;
71 l_ctx_sql_string := null;
72 l_err_loc := 900;
73 IF (p_repopulate_at_seq = l_sequence) THEN
74 l_err_loc := 1000;
75 l_ctx_sql_string := l_ctx_sqlstring_rec.ctx_sql_string;
76 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
77 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
78 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
79 'l_sequence:' || l_sequence ||
80 ', l_ctx_sql_string:' || l_ctx_sql_string);
81 END IF;
82 EXIT;
83 END IF;
84 END LOOP;
85
86 l_err_loc := 1100;
87 IF (l_ctx_sql_string IS NOT NULL) THEN
88 l_err_loc := 1200;
89 l_csr_handle:=DBMS_SQL.OPEN_CURSOR;
90 l_err_loc := 1300;
91 DBMS_SQL.PARSE(l_csr_handle, l_ctx_sql_string, DBMS_SQL.NATIVE);
92 l_err_loc := 1400;
93 DBMS_SQL.BIND_VARIABLE(l_csr_handle,':B_sequence', l_sequence);
94 DBMS_SQL.BIND_ARRAY(l_csr_handle, ':B_rowid', l_rowid_tbl);
95 l_err_loc := 1500;
96 l_status := DBMS_SQL.EXECUTE(l_csr_handle);
97 l_err_loc := 1600;
98 DBMS_SQL.CLOSE_CURSOR(l_csr_handle);
99 l_err_loc := 1700;
100 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
101 l_err_loc := 1800;
102 COMMIT;
103 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
104 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
105 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
106 'Commit done.');
107 END IF;
108 ELSE
109 l_err_loc := 1900;
110 -- Must log
111 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
112 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
113 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
114 'Commit not done.');
115 END IF;
116 END IF;
117 END IF;
118
119 l_err_loc := 2000;
120 EXIT WHEN l_rowid_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
121 END LOOP;
122 l_err_loc := 2100;
123 CLOSE l_csr_var;
124
125 l_err_loc := 2200;
126 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
127 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
128 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
129 'done.');
130 END IF;
131 EXCEPTION
132 WHEN OTHERS THEN
133 ICX_CAT_UTIL_PVT.logUnexpectedException(
134 G_PKG_NAME, l_api_name,
135 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
136 RAISE;
137 END popCtxBaseSpecAttForAllSrc;
138
139 -- populate Row with sequence: 2
140 -- Sequence 2 --> Supplier
141 -- No need to populate this row for Master items.
142 PROCEDURE popCtxBaseSpecSupplierAtt
143 ( p_special_ctx_sql_tbl IN ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type
144 )
145 IS
146 l_api_name CONSTANT VARCHAR2(30) := 'popCtxBaseSpecSupplierAtt';
147 l_err_loc PLS_INTEGER;
148 l_rowid_tbl DBMS_SQL.UROWID_TABLE;
149 l_csr_var g_csr_type;
150 l_ctx_sqlstring_rec ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_rec_type;
151 l_ctx_sql_string VARCHAR2(4000) := NULL;
152 l_sequence NUMBER;
153 l_csr_handle NUMBER;
154 l_status PLS_INTEGER;
155 BEGIN
156 l_err_loc := 100;
157 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
158 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
159 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
160 'Start');
161 END IF;
162
163 l_err_loc := 200;
164 OPEN l_csr_var FOR
165 SELECT ROWID
166 FROM icx_cat_items_ctx_hdrs_tlp
167 WHERE source_type <> 'MASTER_ITEM';
168
169 LOOP
170 l_err_loc := 300;
171 l_rowid_tbl.DELETE;
172
173 l_err_loc := 400;
174 FETCH l_csr_var
175 BULK COLLECT INTO l_rowid_tbl
176 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
177
178 l_err_loc := 500;
179 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
180 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
181 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
182 'fetched from getItemRowsCsr rowcount:' ||l_rowid_tbl.COUNT);
183 END IF;
184
185 l_err_loc := 600;
186 EXIT WHEN l_rowid_tbl.COUNT = 0;
187
188 l_err_loc := 700;
189 FOR i IN 1..p_special_ctx_sql_tbl.COUNT LOOP
190 l_err_loc := 800;
191 l_ctx_sqlstring_rec := p_special_ctx_sql_tbl(i);
192 l_sequence := l_ctx_sqlstring_rec.bind_sequence;
193 l_ctx_sql_string := null;
194 l_err_loc := 900;
195 IF (ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForSupplierRow = l_sequence) THEN
196 l_err_loc := 1000;
197 l_ctx_sql_string := l_ctx_sqlstring_rec.ctx_sql_string;
198 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
199 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
200 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
201 'l_sequence:' || l_sequence ||
202 ', l_ctx_sql_string:' || l_ctx_sql_string);
203 END IF;
204 EXIT;
205 END IF;
206 END LOOP;
207
208 l_err_loc := 1100;
209 IF (l_ctx_sql_string IS NOT NULL) THEN
210 l_err_loc := 1200;
211 l_csr_handle:=DBMS_SQL.OPEN_CURSOR;
212 l_err_loc := 1300;
213 DBMS_SQL.PARSE(l_csr_handle, l_ctx_sql_string, DBMS_SQL.NATIVE);
214 l_err_loc := 1400;
215 DBMS_SQL.BIND_VARIABLE(l_csr_handle,':B_sequence', l_sequence);
216 DBMS_SQL.BIND_ARRAY(l_csr_handle, ':B_rowid', l_rowid_tbl);
217 l_err_loc := 1500;
218 l_status := DBMS_SQL.EXECUTE(l_csr_handle);
219 l_err_loc := 1600;
220 DBMS_SQL.CLOSE_CURSOR(l_csr_handle);
221 l_err_loc := 1700;
222 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
223 l_err_loc := 1800;
224 COMMIT;
225 -- Must log
226 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
227 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
228 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
229 'Commit done.');
230 END IF;
231 ELSE
232 l_err_loc := 1900;
233 -- Must log
234 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
235 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
236 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
237 'Commit not done.');
238 END IF;
239 END IF;
240 END IF;
241
242 l_err_loc := 2000;
243 EXIT WHEN l_rowid_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
244 END LOOP;
245 l_err_loc := 2100;
246 CLOSE l_csr_var;
247
248 l_err_loc :=2200;
249 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
250 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
251 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
252 'done.');
253 END IF;
254 EXCEPTION
255 WHEN OTHERS THEN
256 ICX_CAT_UTIL_PVT.logUnexpectedException(
257 G_PKG_NAME, l_api_name,
258 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
259 RAISE;
260 END popCtxBaseSpecSupplierAtt;
261
262 -- populate Row with sequence: 4 and 5
263 -- Sequence 4 --> Source
264 -- Populate this row for each source differently for
265 -- 1. Blankets/Quotations and GBPA
266 -- 2. Requisition templates
267 -- Sequence 5 --> Item Revision
268 -- Populate this row for each source differently for
269 -- 1. Blankets/Quotations and GBPA
270 -- 2. Requisition templates
271 -- Will not be populated for Master Items
272 -- Note: Calling procedure should take care of not calling this one with MASTER_ITEM source and sequence = 5
273 PROCEDURE popCtxBaseSpecSrcAndItemRevAtt
274 ( p_doc_source IN VARCHAR2 ,
275 p_special_ctx_sql_tbl IN ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type ,
276 p_repopulate_at_seq IN NUMBER
277 )
278 IS
279 l_api_name CONSTANT VARCHAR2(30) := 'popCtxBaseSpecSrcAndItemRevAtt';
280 l_err_loc PLS_INTEGER;
281 l_source_type1 icx_cat_items_ctx_hdrs_tlp.source_type%TYPE;
282 l_source_type2 icx_cat_items_ctx_hdrs_tlp.source_type%TYPE;
283 l_rowid_tbl DBMS_SQL.UROWID_TABLE;
284 l_csr_var g_csr_type;
285 l_ctx_sqlstring_rec ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_rec_type;
286 l_ctx_sql_string VARCHAR2(4000) := NULL;
287 l_sequence NUMBER;
288 l_csr_handle NUMBER;
289 l_status PLS_INTEGER;
290 BEGIN
291 l_err_loc := 100;
292 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
293 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
294 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
295 'Start; p_doc_source:' || p_doc_source ||
296 ', p_repopulate_at_seq:' || p_repopulate_at_seq);
297 END IF;
298
299 IF (p_doc_source = ICX_CAT_UTIL_PVT.g_PODoc_const) THEN
300 l_err_loc := 200;
301 l_source_type1 := 'BLANKET';
302 l_source_type2 := 'QUOTATION';
303 ELSIF (p_doc_source = ICX_CAT_UTIL_PVT.g_ReqTemplateCsr_const) THEN
304 l_err_loc := 300;
305 l_source_type1 := 'TEMPLATE';
306 l_source_type2 := 'INTERNAL_TEMPLATE';
307 END IF;
308
309 l_err_loc := 500;
310 OPEN l_csr_var FOR
311 SELECT ROWID
312 FROM icx_cat_items_ctx_hdrs_tlp
313 WHERE source_type IN (l_source_type1, l_source_type2);
314
315 l_err_loc := 600;
316
317 LOOP
318 l_err_loc := 700;
319 l_rowid_tbl.DELETE;
320
321 l_err_loc := 800;
322 FETCH l_csr_var
323 BULK COLLECT INTO l_rowid_tbl
324 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
325
326 l_err_loc := 900;
327 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
328 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
329 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
330 'fetched from getItemRowsCsr rowcount:' ||l_rowid_tbl.COUNT);
331 END IF;
332
333 l_err_loc := 1000;
334 EXIT WHEN l_rowid_tbl.COUNT = 0;
335
336 l_err_loc := 1100;
337 FOR i IN 1..p_special_ctx_sql_tbl.COUNT LOOP
338 l_err_loc := 1200;
339 l_ctx_sqlstring_rec := p_special_ctx_sql_tbl(i);
340 l_sequence := l_ctx_sqlstring_rec.bind_sequence;
341 l_ctx_sql_string := null;
342 IF (p_repopulate_at_seq = l_sequence) THEN
343 l_err_loc := 1300;
344 l_ctx_sql_string := l_ctx_sqlstring_rec.ctx_sql_string;
345 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
346 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
347 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
348 'l_sequence:' || l_sequence ||
349 ', l_ctx_sql_string:' || l_ctx_sql_string);
350 END IF;
351 EXIT;
352 END IF;
353 END LOOP;
354
355 l_err_loc := 1400;
356 IF (l_ctx_sql_string IS NOT NULL) THEN
357 l_err_loc := 1500;
358 l_csr_handle:=DBMS_SQL.OPEN_CURSOR;
359 l_err_loc := 1600;
360 DBMS_SQL.PARSE(l_csr_handle, l_ctx_sql_string, DBMS_SQL.NATIVE);
361 l_err_loc := 1700;
362 DBMS_SQL.BIND_VARIABLE(l_csr_handle,':B_sequence', l_sequence);
363 DBMS_SQL.BIND_ARRAY(l_csr_handle, ':B_rowid', l_rowid_tbl);
364 l_err_loc := 1800;
365 l_status := DBMS_SQL.EXECUTE(l_csr_handle);
366 l_err_loc := 1900;
367 DBMS_SQL.CLOSE_CURSOR(l_csr_handle);
368 l_err_loc := 2000;
369 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
370 l_err_loc := 2100;
371 COMMIT;
372 -- Must log
373 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
374 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
375 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
376 'Commit done.');
377 END IF;
378 ELSE
379 l_err_loc := 2200;
380 -- Must log
381 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
382 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
383 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
384 'Commit not done.');
385 END IF;
386 END IF;
387 END IF;
388
389 l_err_loc := 2300;
390 EXIT WHEN l_rowid_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
391 END LOOP;
392 l_err_loc := 2400;
393 CLOSE l_csr_var;
394
395 l_err_loc := 2500;
396 IF (p_doc_source = ICX_CAT_UTIL_PVT.g_PODoc_const) THEN
397 l_err_loc := 2600;
398 INSERT INTO icx_cat_items_ctx_dtls_tlp
399 (inventory_item_id, po_line_id, req_template_name,
400 req_template_line_num, org_id, language,
401 last_update_login, last_updated_by, last_update_date,
402 internal_request_id, request_id, created_by, creation_date,
403 sequence, ctx_desc)
404 SELECT dtls.inventory_item_id, dtls.po_line_id, dtls.req_template_name,
405 dtls.req_template_line_num, hdrs.org_id, dtls.language,
406 dtls.last_update_login, dtls.last_updated_by, dtls.last_update_date,
407 dtls.internal_request_id, dtls.request_id, dtls.created_by, dtls.creation_date,
408 dtls.sequence, dtls.ctx_desc
409 FROM icx_cat_items_ctx_dtls_tlp dtls, icx_cat_items_ctx_hdrs_tlp hdrs
410 WHERE hdrs.source_type = 'GLOBAL_BLANKET'
411 AND hdrs.inventory_item_id = dtls.inventory_item_id
412 AND hdrs.po_line_id = dtls.po_line_id
413 AND hdrs.req_template_name = dtls.req_template_name
414 AND hdrs.req_template_line_num = dtls.req_template_line_num
415 AND hdrs.language = dtls.language
416 AND hdrs.owning_org_id = dtls.org_id
417 AND dtls.sequence = p_repopulate_at_seq;
418
419 l_err_loc := 2700;
420 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
421 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
422 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
423 'Num. of rows inserted for ga:' || SQL%ROWCOUNT);
424 END IF;
425
426 l_err_loc := 2800;
427 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
428 l_err_loc := 2900;
429 COMMIT;
430 -- Must log
431 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
432 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
433 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
434 'Commit done after populating ctx string for ga.');
435 END IF;
436 ELSE
437 l_err_loc := 3000;
438 -- Must log
439 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
440 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
441 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
442 'Commit not done after populating ctx string for ga.');
443 END IF;
444 END IF;
445 END IF;
446
447 l_err_loc := 3100;
448 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
449 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
450 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
451 'done.');
452 END IF;
453 EXCEPTION
454 WHEN OTHERS THEN
455 ICX_CAT_UTIL_PVT.logUnexpectedException(
456 G_PKG_NAME, l_api_name,
457 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
458 RAISE;
459 END popCtxBaseSpecSrcAndItemRevAtt;
460
461 -- populate Row with sequence: between 100 and 5000
462 -- Will be same for BPAs, Quotes, GBPAs and Req Templates
463 -- Only for master items, this will populate only the description.
464 PROCEDURE popCtxBaseRegularAttributes
465 ( p_doc_source IN VARCHAR2 ,
466 p_regular_ctx_sql_tbl IN ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type
467 )
468 IS
469 l_api_name CONSTANT VARCHAR2(30) := 'popCtxBaseRegularAttributes';
470 l_err_loc PLS_INTEGER;
471 l_rowid_tbl DBMS_SQL.UROWID_TABLE;
472 l_csr_var g_csr_type;
473 l_ctx_sqlstring_rec ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_rec_type;
474 l_ctx_sql_string VARCHAR2(4000) := NULL;
475 l_sequence NUMBER;
476 l_csr_handle NUMBER;
477 l_status PLS_INTEGER;
478 BEGIN
479 l_err_loc := 100;
480 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
481 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
482 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
483 'Start; p_doc_source:' || p_doc_source);
484 END IF;
485
486 IF (p_doc_source = ICX_CAT_UTIL_PVT.g_PODoc_const) THEN
487 l_err_loc := 200;
488 OPEN l_csr_var FOR
489 SELECT ROWID
490 FROM icx_cat_items_ctx_hdrs_tlp
491 WHERE source_type NOT IN ('MASTER_ITEM', 'GLOBAL_BLANKET');
492 ELSE
493 l_err_loc := 300;
494 OPEN l_csr_var FOR
495 SELECT ROWID
496 FROM icx_cat_items_ctx_hdrs_tlp
497 WHERE source_type = 'MASTER_ITEM';
498 END IF;
499
500 l_err_loc := 400;
501 LOOP
502 l_err_loc := 500;
503 l_rowid_tbl.DELETE;
504
505 l_err_loc := 600;
506 FETCH l_csr_var
507 BULK COLLECT INTO l_rowid_tbl
508 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
509
510 l_err_loc := 700;
511 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
512 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
513 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
514 'fetched from getItemRowsCsr rowcount:' ||l_rowid_tbl.COUNT);
515 END IF;
516
517 l_err_loc := 800;
518 EXIT WHEN l_rowid_tbl.COUNT = 0;
519
520 l_err_loc := 900;
521 FOR i IN 1..p_regular_ctx_sql_tbl.COUNT LOOP
522 l_err_loc := 1000;
523 l_ctx_sqlstring_rec := p_regular_ctx_sql_tbl(i);
524 l_sequence := l_ctx_sqlstring_rec.bind_sequence;
525 l_ctx_sql_string := l_ctx_sqlstring_rec.ctx_sql_string;
526 l_err_loc := 1100;
527 l_csr_handle :=DBMS_SQL.OPEN_CURSOR;
528 l_err_loc := 1200;
529 DBMS_SQL.PARSE(l_csr_handle, l_ctx_sql_string, DBMS_SQL.NATIVE);
530 l_err_loc := 1300;
531 DBMS_SQL.BIND_VARIABLE(l_csr_handle,':B_sequence', l_sequence);
532 DBMS_SQL.BIND_ARRAY(l_csr_handle, ':B_rowid', l_rowid_tbl);
533 l_err_loc := 1400;
534 l_status := DBMS_SQL.EXECUTE(l_csr_handle);
535 l_err_loc := 1500;
536 DBMS_SQL.CLOSE_CURSOR(l_csr_handle);
537 END LOOP;
538
539 l_err_loc := 1600;
540 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
541 l_err_loc := 1700;
542 COMMIT;
543 -- Must log
544 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
545 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
546 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
547 'Commit done.');
548 END IF;
549 ELSE
550 l_err_loc := 1800;
551 -- Must log
552 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
553 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
554 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
555 'Commit not done.');
556 END IF;
557 END IF;
558
559 l_err_loc := 1900;
560 EXIT WHEN l_rowid_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
561 END LOOP;
562 l_err_loc := 2000;
563 CLOSE l_csr_var;
564
565 l_err_loc := 2100;
566 IF (p_doc_source = ICX_CAT_UTIL_PVT.g_PODoc_const) THEN
567 l_err_loc := 2200;
568 INSERT INTO icx_cat_items_ctx_dtls_tlp
569 (inventory_item_id, po_line_id, req_template_name,
570 req_template_line_num, org_id, language,
571 last_update_login, last_updated_by, last_update_date,
572 internal_request_id, request_id, created_by, creation_date,
573 sequence, ctx_desc)
574 SELECT dtls.inventory_item_id, dtls.po_line_id, dtls.req_template_name,
575 dtls.req_template_line_num, hdrs.org_id, dtls.language,
576 dtls.last_update_login, dtls.last_updated_by, dtls.last_update_date,
577 dtls.internal_request_id, dtls.request_id, dtls.created_by, dtls.creation_date,
578 dtls.sequence, dtls.ctx_desc
579 FROM icx_cat_items_ctx_dtls_tlp dtls, icx_cat_items_ctx_hdrs_tlp hdrs
580 WHERE hdrs.source_type = 'GLOBAL_BLANKET'
581 AND hdrs.inventory_item_id = dtls.inventory_item_id
582 AND hdrs.po_line_id = dtls.po_line_id
583 AND hdrs.req_template_name = dtls.req_template_name
584 AND hdrs.req_template_line_num = dtls.req_template_line_num
585 AND hdrs.language = dtls.language
586 AND hdrs.owning_org_id = dtls.org_id
587 AND dtls.sequence BETWEEN ICX_CAT_BUILD_CTX_SQL_PVT.g_seqStartReqularBaseRow
588 AND ICX_CAT_BUILD_CTX_SQL_PVT.g_seqEndReqularBaseRow;
589
590 l_err_loc := 2300;
591 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
592 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
593 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
594 'Num. of rows inserted for ga:' || SQL%ROWCOUNT);
595 END IF;
596
597 l_err_loc := 2400;
598 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
599 l_err_loc := 2500;
600 COMMIT;
601 -- Must log
602 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
603 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
604 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
605 'Commit done after populating ctx string for ga.');
606 END IF;
607 ELSE
608 l_err_loc := 2600;
609 -- Must log
610 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
611 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
612 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
613 'Commit not done after populating ctx string for ga.');
614 END IF;
615 END IF;
616 END IF;
617
618 l_err_loc := 2700;
619 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
620 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
621 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
622 'done.');
623 END IF;
624 EXCEPTION
625 WHEN OTHERS THEN
626 ICX_CAT_UTIL_PVT.logUnexpectedException(
627 G_PKG_NAME, l_api_name,
628 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
629 RAISE;
630 END popCtxBaseRegularAttributes;
631
632 PROCEDURE populateCtxBaseAtt
633 ( p_doc_source IN VARCHAR2 ,
634 p_internal_request_id IN NUMBER
635 )
636 IS
637 CURSOR getItemRowsCsr(p_source_type1 VARCHAR2,
638 p_source_type2 VARCHAR2,
639 p_internal_request_id NUMBER) IS
640 SELECT source_type, rowid
641 FROM icx_cat_items_ctx_hdrs_tlp
642 WHERE source_type IN (p_source_type1, p_source_type2)
643 AND internal_request_id = p_internal_request_id;
644
645 l_rowid_tbl DBMS_SQL.UROWID_TABLE;
646 l_source_type_tbl DBMS_SQL.VARCHAR2_TABLE;
647
648 l_api_name CONSTANT VARCHAR2(30) := 'populateCtxBaseAtt';
649 l_all_ctx_sql_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type;
650 l_special_ctx_sql_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type;
651 l_regular_ctx_sql_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type;
652 l_ctx_sqlstring_rec ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_rec_type;
653 l_err_loc PLS_INTEGER;
654 l_metadata_rec ICX_CAT_BUILD_CTX_SQL_PVT.g_metadata_rec_type;
655 l_csr_handle NUMBER;
656 l_status PLS_INTEGER;
657 l_source_type1 icx_cat_items_ctx_hdrs_tlp.source_type%TYPE;
658 l_source_type2 icx_cat_items_ctx_hdrs_tlp.source_type%TYPE;
659 BEGIN
660 l_err_loc := 100;
661 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
662 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
663 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
664 'Start; p_doc_source:' || p_doc_source ||
665 ', p_internal_request_id:' || p_internal_request_id);
666 END IF;
667
668 IF (NOT g_metadataTblFormed) THEN
669 ICX_CAT_BUILD_CTX_SQL_PVT.buildMetadataInfo
670 (0, g_special_metadata_tbl, g_regular_nontl_metadata_tbl, g_regular_tl_metadata_tbl);
671 g_metadataTblFormed := TRUE;
672 END IF;
673
674 l_err_loc := 500;
675 ICX_CAT_BUILD_CTX_SQL_PVT.buildCtxSql
676 (0, p_doc_source, 'ROWID', g_special_metadata_tbl,
677 g_regular_nontl_metadata_tbl, g_regular_tl_metadata_tbl,
678 l_all_ctx_sql_tbl, l_special_ctx_sql_tbl, l_regular_ctx_sql_tbl);
679
680 l_err_loc := 600;
681
682 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
683 FOR i in 1..l_all_ctx_sql_tbl.COUNT LOOP
684 l_ctx_sqlstring_rec := l_all_ctx_sql_tbl(i);
685 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
686 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
687 'All ctx SQLs..Value at i:' || i ||
688 ', sequence:' || l_ctx_sqlstring_rec.bind_sequence ||
689 ', sql_string:' || l_ctx_sqlstring_rec.ctx_sql_string );
690
691 END LOOP;
692 END IF;
693
694 l_err_loc := 700;
695 IF (p_doc_source = ICX_CAT_UTIL_PVT.g_PODoc_const) THEN
696 l_err_loc := 800;
697 l_source_type1 := 'BLANKET';
698 l_source_type2 := 'QUOTATION';
699 ELSIF (p_doc_source = ICX_CAT_UTIL_PVT.g_ReqTemplateCsr_const) THEN
700 l_err_loc := 900;
701 l_source_type1 := 'TEMPLATE';
702 l_source_type2 := 'INTERNAL_TEMPLATE';
703 ELSE
704 l_err_loc := 1100;
705 l_source_type1 := 'MASTER_ITEM';
706 l_source_type2 := l_source_type1;
707 END IF;
708
709 l_err_loc := 1200;
710 OPEN getItemRowsCsr(l_source_type1, l_source_type2, p_internal_request_id);
711
712 l_err_loc := 1300;
713 LOOP
714 l_err_loc := 1400;
715 l_rowid_tbl.DELETE;
716 l_source_type_tbl.DELETE;
717
718 l_err_loc := 1500;
719 FETCH getItemRowsCsr BULK COLLECT INTO
720 l_source_type_tbl, l_rowid_tbl LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
721 l_err_loc := 1600;
722 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
723 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
724 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
725 'fetched from getItemRowsCsr rowcount:' ||l_rowid_tbl.COUNT);
726 END IF;
727
728 l_err_loc := 1700;
729 EXIT WHEN l_rowid_tbl.COUNT = 0;
730
731 l_err_loc := 1800;
732 FOR i IN 1..l_all_ctx_sql_tbl.COUNT LOOP
733 l_err_loc := 1900;
734 l_ctx_sqlstring_rec := l_all_ctx_sql_tbl(i);
735 l_err_loc := 2000;
736 l_csr_handle:=DBMS_SQL.OPEN_CURSOR;
737 l_err_loc := 2100;
738 DBMS_SQL.PARSE(l_csr_handle, l_ctx_sqlstring_rec.ctx_sql_string, DBMS_SQL.NATIVE);
739 l_err_loc := 2200;
740 DBMS_SQL.BIND_VARIABLE(l_csr_handle,':B_sequence', l_ctx_sqlstring_rec.bind_sequence);
741 DBMS_SQL.BIND_ARRAY(l_csr_handle, ':B_rowid', l_rowid_tbl);
742 l_err_loc := 2300;
743 l_status := DBMS_SQL.EXECUTE(l_csr_handle);
744 l_err_loc := 2400;
745 DBMS_SQL.CLOSE_CURSOR(l_csr_handle);
746 END LOOP;
747
748 l_err_loc := 2500;
749 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
750 l_err_loc := 2600;
751 COMMIT;
752 -- Must log
753 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
754 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
755 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
756 'Commit done.');
757 END IF;
758 ELSE
759 l_err_loc := 2700;
760 -- Must log
761 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
762 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
763 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
764 'Commit not done.');
765 END IF;
766 END IF;
767
768 l_err_loc := 2800;
769 EXIT WHEN l_rowid_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
770 END LOOP;
771 l_err_loc := 2900;
772 CLOSE getItemRowsCsr;
773
774 l_err_loc := 3000;
775 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
776 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
777 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
778 'done.');
779 END IF;
780
781 EXCEPTION
782 WHEN OTHERS THEN
783 ICX_CAT_UTIL_PVT.logUnexpectedException(
784 G_PKG_NAME, l_api_name,
785 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
786 RAISE;
787 END populateCtxBaseAtt;
788
789 PROCEDURE populateCtxCatgAtt
790 ( p_internal_request_id IN NUMBER
791 )
792 IS
793 --Get all the categories that have atleast one searchable attributes
794 --and has atleast one item loaded in the current internal_request_id
795 CURSOR getCategoriesCsr(p_internal_request_id NUMBER) IS
796 SELECT cat.rt_category_id
797 FROM icx_cat_categories_tl cat
798 WHERE cat.type = 2
799 AND cat.language = (SELECT language_code FROM fnd_languages WHERE installed_flag = 'B')
800 AND EXISTS (SELECT 'atleast one searchable descriptor'
801 FROM icx_cat_attributes_tl att
802 where att.rt_category_id = cat.rt_category_id
803 and att.language = cat.language
804 and att.searchable = 1)
805 AND EXISTS (SELECT 'atleast one item loaded in the current internal_request_id'
806 FROM icx_cat_items_ctx_hdrs_tlp item
807 WHERE item.ip_category_id = cat.rt_category_id
808 AND item.internal_request_id = p_internal_request_id
809 AND item.source_type NOT IN ('MASTER ITEM', 'GLOBAL_BLANKET'));
810
811 CURSOR getItemRowsCsr(p_category_id NUMBER,
812 p_internal_request_id NUMBER) IS
813 SELECT rowid
814 FROM icx_cat_items_ctx_hdrs_tlp
815 WHERE ip_category_id = p_category_id
816 AND internal_request_id = p_internal_request_id
817 AND source_type NOT IN ('MASTER ITEM', 'GLOBAL_BLANKET');
818
819 l_rowid_tbl DBMS_SQL.UROWID_TABLE;
820 l_api_name CONSTANT VARCHAR2(30) := 'populateCtxCatgAtt';
821 l_all_ctx_sql_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type;
822 l_special_ctx_sql_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type;
823 l_regular_ctx_sql_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type;
824 l_ctx_sqlstring_rec ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_rec_type;
825 l_metadata_rec ICX_CAT_BUILD_CTX_SQL_PVT.g_metadata_rec_type;
826 l_csr_handle NUMBER;
827 l_status PLS_INTEGER;
828 l_special_metadata_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_metadata_tbl_type;
829 l_regular_nontl_metadata_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_metadata_tbl_type;
830 l_regular_tl_metadata_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_metadata_tbl_type;
831 l_start_catg_att_seq NUMBER;
832 l_end_catg_att_seq NUMBER;
833 l_err_loc PLS_INTEGER;
834 l_start_date DATE;
835 l_end_date DATE;
836 BEGIN
837 l_err_loc := 100;
838 l_start_date := sysdate;
839 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
840 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
841 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
842 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ||
843 ';(' || p_internal_request_id || ');');
844 END IF;
845
846 l_err_loc := 200;
847 FOR catgRec IN getCategoriesCsr(p_internal_request_id) LOOP
848 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
849 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
850 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
851 'for category:' || catgRec.rt_category_id);
852 END IF;
853
854 l_err_loc := 300;
855 l_special_metadata_tbl.DELETE;
856 l_regular_nontl_metadata_tbl.DELETE;
857 l_regular_tl_metadata_tbl.DELETE;
858 l_all_ctx_sql_tbl.DELETE;
859
860 l_err_loc := 400;
861 ICX_CAT_BUILD_CTX_SQL_PVT.buildMetadataInfo
862 (catgRec.rt_category_id, l_special_metadata_tbl,
863 l_regular_nontl_metadata_tbl, l_regular_tl_metadata_tbl);
864
865 l_err_loc := 500;
866 ICX_CAT_BUILD_CTX_SQL_PVT.buildCtxSql
867 (catgRec.rt_category_id, NULL, 'ROWID', l_special_metadata_tbl,
868 l_regular_nontl_metadata_tbl, l_regular_tl_metadata_tbl,
869 l_all_ctx_sql_tbl, l_special_ctx_sql_tbl, l_regular_ctx_sql_tbl);
870
871 l_err_loc := 1000;
872 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
873 FOR i in 1..l_all_ctx_sql_tbl.COUNT LOOP
874 l_ctx_sqlstring_rec := l_all_ctx_sql_tbl(i);
875 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
876 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
877 'All ctx SQLs..Value at i:' || i ||
878 ', sequence:' || l_ctx_sqlstring_rec.bind_sequence ||
879 ', sql_string:' || l_ctx_sqlstring_rec.ctx_sql_string );
880 END LOOP;
881 END IF;
882
883 l_err_loc := 1200;
884 OPEN getItemRowsCsr(catgRec.rt_category_id, p_internal_request_id);
885
886 l_err_loc := 1300;
887 LOOP
888 l_err_loc := 1400;
889 l_rowid_tbl.DELETE;
890 l_err_loc := 1500;
891 FETCH getItemRowsCsr BULK COLLECT INTO l_rowid_tbl LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
892 l_err_loc := 1600;
893 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
894 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
895 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
896 'fetched from getItemRowsCsr rowcount:' ||l_rowid_tbl.COUNT);
897 END IF;
898
899 l_err_loc := 1700;
900 EXIT WHEN l_rowid_tbl.COUNT = 0;
901
902 l_err_loc := 1800;
903 FOR i IN 1..l_all_ctx_sql_tbl.COUNT LOOP
904 l_err_loc := 1900;
905 l_ctx_sqlstring_rec := l_all_ctx_sql_tbl(i);
906 l_err_loc := 2000;
907 l_csr_handle:=DBMS_SQL.OPEN_CURSOR;
908 l_err_loc := 2100;
909 DBMS_SQL.PARSE(l_csr_handle, l_ctx_sqlstring_rec.ctx_sql_string, DBMS_SQL.NATIVE);
910 l_err_loc := 2200;
911 DBMS_SQL.BIND_VARIABLE(l_csr_handle,':B_sequence', l_ctx_sqlstring_rec.bind_sequence);
912 DBMS_SQL.BIND_ARRAY(l_csr_handle, ':B_rowid', l_rowid_tbl);
913 l_err_loc := 2300;
914 l_status := DBMS_SQL.EXECUTE(l_csr_handle);
915 l_err_loc := 2400;
916 DBMS_SQL.CLOSE_CURSOR(l_csr_handle);
917 END LOOP; -- l_all_ctx_sql_tbl LOOP
918
919 l_err_loc := 2500;
920 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
921 l_err_loc := 2600;
922 COMMIT;
923 -- Must log
924 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
925 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
926 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
927 'Commit done.');
928 END IF;
929 ELSE
930 l_err_loc := 2700;
931 -- Must log
932 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
933 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
934 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
935 'Commit not done.');
936 END IF;
937 END IF;
938
939 l_err_loc := 2800;
940 EXIT WHEN l_rowid_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
941 END LOOP; -- getItemRowsCsr LOOP
942
943 l_err_loc := 2900;
944 CLOSE getItemRowsCsr;
945
946 END LOOP; --catgRec LOOP
947
948 l_err_loc := 3000;
949 --populate category attributes For GLOBAL_BLANKET lines
950 --rows with sequence between 5001 and 9999 will be done here for GBPA rows
951 --loaded in the current internal_request_id
952 l_start_catg_att_seq := ICX_CAT_BUILD_CTX_SQL_PVT.g_seqStartRegularCatgRow + 1;
953 l_end_catg_att_seq := ICX_CAT_BUILD_CTX_SQL_PVT.g_seqEndRegularCatgRow;
954 l_err_loc := 3100;
955 INSERT INTO icx_cat_items_ctx_dtls_tlp
956 (inventory_item_id, po_line_id, req_template_name,
957 req_template_line_num, org_id, language,
958 last_update_login, last_updated_by, last_update_date,
959 internal_request_id, request_id, created_by, creation_date,
960 sequence, ctx_desc)
961 SELECT dtls.inventory_item_id, dtls.po_line_id, dtls.req_template_name,
962 dtls.req_template_line_num, hdrs.org_id, dtls.language,
963 dtls.last_update_login, dtls.last_updated_by, dtls.last_update_date,
964 dtls.internal_request_id, dtls.request_id, dtls.created_by, dtls.creation_date,
965 dtls.sequence, dtls.ctx_desc
966 FROM icx_cat_items_ctx_dtls_tlp dtls, icx_cat_items_ctx_hdrs_tlp hdrs
967 WHERE hdrs.source_type = 'GLOBAL_BLANKET'
968 AND hdrs.internal_request_id = p_internal_request_id
969 AND hdrs.inventory_item_id = dtls.inventory_item_id
970 AND hdrs.po_line_id = dtls.po_line_id
971 AND hdrs.req_template_name = dtls.req_template_name
972 AND hdrs.req_template_line_num = dtls.req_template_line_num
973 AND hdrs.owning_org_id = dtls.org_id
974 AND hdrs.language = dtls.language
975 AND dtls.sequence BETWEEN l_start_catg_att_seq AND l_end_catg_att_seq;
976
977 l_err_loc := 3200;
978 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
979 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
980 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
981 'Num. of rows inserted for ga:' || SQL%ROWCOUNT);
982 END IF;
983
984 l_err_loc := 3300;
985 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
986 l_err_loc := 3400;
987 COMMIT;
988 -- Must log
989 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
990 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
991 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
992 'Commit done after populating ctx string for ga.');
993 END IF;
994 ELSE
995 l_err_loc := 3500;
996 -- Must log
997 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
998 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
999 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1000 'Commit not done after populating ctx string for ga.');
1001 END IF;
1002 END IF;
1003
1004 l_err_loc := 3600;
1005 l_end_date := sysdate;
1006 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1007 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1008 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1009 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
1010 END IF;
1011
1012 EXCEPTION
1013 WHEN OTHERS THEN
1014 ICX_CAT_UTIL_PVT.logUnexpectedException(
1015 G_PKG_NAME, l_api_name,
1016 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1017 RAISE;
1018 END populateCtxCatgAtt;
1019
1020 PROCEDURE populateCtxOrgInfo(p_internal_request_id IN NUMBER)
1021 IS
1022 CURSOR getItemRowsCsr(p_internal_request_id NUMBER) IS
1023 SELECT rowid
1024 FROM icx_cat_items_ctx_hdrs_tlp
1025 WHERE internal_request_id = p_internal_request_id;
1026
1027 l_rowid_tbl DBMS_SQL.UROWID_TABLE;
1028 l_api_name CONSTANT VARCHAR2(30) := 'populateCtxOrgInfo';
1029 l_err_loc PLS_INTEGER;
1030 BEGIN
1031 l_err_loc := 100;
1032 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1033 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1034 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1035 'Start; p_internal_request_id:' || p_internal_request_id);
1036 END IF;
1037
1038 l_err_loc := 150;
1039 OPEN getItemRowsCsr(p_internal_request_id);
1040 LOOP
1041 l_err_loc := 200;
1042 l_rowid_tbl.DELETE;
1043
1044 l_err_loc := 300;
1045 FETCH getItemRowsCsr BULK COLLECT INTO l_rowid_tbl LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
1046
1047 l_err_loc := 400;
1048 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1049 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1050 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1051 'fetched from getItemRowsCsr rowcount:' ||l_rowid_tbl.COUNT);
1052 END IF;
1053
1054 l_err_loc := 500;
1055 EXIT WHEN l_rowid_tbl.COUNT = 0;
1056
1057 l_err_loc := 600;
1058 FORALL i IN 1..l_rowid_tbl.COUNT
1059 INSERT INTO icx_cat_items_ctx_dtls_tlp
1060 (inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
1061 last_update_login, last_updated_by, last_update_date, created_by, creation_date, sequence, ctx_desc)
1062 SELECT inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
1063 last_update_login, last_updated_by, last_update_date, created_by, creation_date, 10000, '<orgid>'
1064 FROM icx_cat_items_ctx_hdrs_tlp hdrs
1065 WHERE hdrs.rowid = l_rowid_tbl(i);
1066
1067 l_err_loc := 700;
1068 FORALL i IN 1..l_rowid_tbl.COUNT
1069 INSERT INTO icx_cat_items_ctx_dtls_tlp
1070 (inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
1071 last_update_login, last_updated_by, last_update_date, created_by, creation_date, sequence, ctx_desc)
1072 SELECT inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
1073 last_update_login, last_updated_by, last_update_date, created_by, creation_date, 10001, to_char(org_id)
1074 FROM icx_cat_items_ctx_hdrs_tlp hdrs
1075 WHERE hdrs.rowid = l_rowid_tbl(i);
1076
1077 l_err_loc := 800;
1078 FORALL i IN 1..l_rowid_tbl.COUNT
1079 INSERT INTO icx_cat_items_ctx_dtls_tlp
1080 (inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
1081 last_update_login, last_updated_by, last_update_date, created_by, creation_date, sequence, ctx_desc)
1082 SELECT inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
1083 last_update_login, last_updated_by, last_update_date, created_by, creation_date, 15000, '</orgid>'
1084 FROM icx_cat_items_ctx_hdrs_tlp hdrs
1085 WHERE hdrs.rowid = l_rowid_tbl(i);
1086
1087 l_err_loc := 900;
1088 FORALL i IN 1..l_rowid_tbl.COUNT
1089 INSERT INTO icx_cat_items_ctx_dtls_tlp
1090 (inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
1091 last_update_login, last_updated_by, last_update_date, created_by, creation_date, sequence, ctx_desc)
1092 SELECT inventory_item_id, po_line_id, req_template_name, req_template_line_num, org_id, language,
1093 last_update_login, last_updated_by, last_update_date, created_by, creation_date, 15001,
1094 '<purchorgid>' || to_char(purchasing_org_id) || '</purchorgid>'
1095 FROM icx_cat_items_ctx_hdrs_tlp hdrs
1096 WHERE hdrs.rowid = l_rowid_tbl(i);
1097
1098 l_err_loc := 1000;
1099 EXIT WHEN l_rowid_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
1100 END LOOP;
1101
1102 l_err_loc := 1100;
1103 CLOSE getItemRowsCsr;
1104
1105 l_err_loc := 1200;
1106 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1107 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1108 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1109 'done.');
1110 END IF;
1111
1112 EXCEPTION
1113 WHEN OTHERS THEN
1114 ICX_CAT_UTIL_PVT.logUnexpectedException(
1115 G_PKG_NAME, l_api_name,
1116 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1117 RAISE;
1118 END populateCtxOrgInfo;
1119
1120 /*
1121 Procedure to re-populate the dtls for a particular source and internal_request_id,
1122 The calling procedure should make sure to remove all the dtls for the source and internal_request_id
1123 Commenting out for the moment
1124
1125 Values of p_doc_source:
1126 ICX_CAT_UTIL_PVT.g_PODoc_const VARCHAR2(15) := 'PO_DOCUMENTS';
1127 ICX_CAT_UTIL_PVT.g_ReqTemplateCsr_const VARCHAR2(15) := 'ReqTemplate';
1128 ICX_CAT_UTIL_PVT.g_MasterItemCsr_const VARCHAR2(15) := 'MASTER_ITEM';
1129 Values of p_mode:
1130 ICX_CAT_UTIL_PVT.g_upgrade_const VARCHAR2(15) := 'UPGRADE';
1131 ICX_CAT_UTIL_PVT.g_online_const VARCHAR2(15) := 'ONLINE';
1132 exec ICX_CAT_POPULATE_CTXSTRING_PVT.populateCtxString(ICX_CAT_UTIL_PVT.g_PODoc_const, -12, 'UPG', 2500)
1133 PROCEDURE populateCtxString
1134 ( p_doc_source IN VARCHAR2 ,
1135 p_internal_request_id IN NUMBER ,
1136 p_mode IN VARCHAR2 ,
1137 p_batch_size IN NUMBER
1138 )
1139 IS
1140 l_api_name CONSTANT VARCHAR2(30) := 'populateCtxString';
1141 l_err_loc PLS_INTEGER;
1142 l_all_ctx_sql_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type;
1143 l_special_ctx_sql_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type;
1144 l_regular_ctx_sql_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type;
1145 l_start_date DATE;
1146 l_end_date DATE;
1147 l_log_string VARCHAR2(2000);
1148 BEGIN
1149 l_err_loc := 100;
1150 l_start_date := sysdate;
1151
1152 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1153 l_log_string := 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ;
1154 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
1155 END IF;
1156
1157 l_err_loc := 200;
1158 ICX_CAT_UTIL_PVT.setBatchSize(p_batch_size);
1159
1160 l_err_loc := 300;
1161 populateCtxBaseAtt(p_doc_source, p_internal_request_id);
1162
1163 l_err_loc := 400;
1164 populateCtxOrgInfo(p_internal_request_id);
1165
1166 --Since there is no way to load the category attributes for a master item
1167 --So, call populateCtxCatgAtt when doc source <> ICX_CAT_UTIL_PVT.g_MasterItem_const
1168 l_err_loc := 500;
1169 IF (p_doc_source <> ICX_CAT_UTIL_PVT.g_MasterItemCsr_const) THEN
1170 populateCtxCatgAtt(p_internal_request_id);
1171 END IF;
1172 l_err_loc := 600;
1173 l_end_date := sysdate;
1174 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1175 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
1176 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
1177 END IF;
1178
1179 EXCEPTION
1180 WHEN OTHERS THEN
1181 ICX_CAT_UTIL_PVT.logUnexpectedException(
1182 G_PKG_NAME, l_api_name,
1183 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1184 ROLLBACK;
1185 RAISE;
1186 END populateCtxString;
1187 */
1188
1189 PROCEDURE updateItemsCtxHdrsTlp
1190 ( p_category_id IN NUMBER ,
1191 p_internal_request_id IN OUT NOCOPY NUMBER ,
1192 p_attribute_key IN VARCHAR2
1193 )
1194 IS
1195
1196 l_api_name CONSTANT VARCHAR2(30) := 'updateItemsCtxHdrsTlp';
1197 l_err_loc PLS_INTEGER;
1198 l_internal_request_id NUMBER;
1199 l_csr_var g_csr_type;
1200 l_rowid_tbl DBMS_SQL.UROWID_TABLE;
1201 l_po_line_id_tbl DBMS_SQL.NUMBER_TABLE;
1202 l_inventory_item_id_tbl DBMS_SQL.NUMBER_TABLE;
1203 l_req_template_name_tbl DBMS_SQL.VARCHAR2_TABLE;
1204 l_req_template_line_num_tbl DBMS_SQL.NUMBER_TABLE;
1205 l_org_id_tbl DBMS_SQL.NUMBER_TABLE;
1206 l_language_tbl DBMS_SQL.VARCHAR2_TABLE;
1207 l_start_sequence NUMBER;
1208 l_end_sequence NUMBER;
1209 l_err_string VARCHAR2(4000);
1210
1211 BEGIN
1212 l_err_loc := 100;
1213 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1214 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1215 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1216 'Start; (' || p_category_id ||', ' || p_attribute_key ||', ' || ');');
1217 END IF;
1218 -- Update ctx_desc and internal_request_id for all items if ip_category_id = 0 in hdrs tlp
1219 -- otherwise only update row with ip_category_id = p_category_id
1220 -- Return internal_request_id
1221 l_internal_request_id := ICX_CAT_UTIL_PVT.getNextSequenceForWhoColumns;
1222 p_internal_request_id := l_internal_request_id;
1223 l_err_loc := 200;
1224 OPEN l_csr_var FOR
1225 SELECT ROWID, po_line_id, inventory_item_id,
1226 req_template_name, req_template_line_num,
1227 org_id, language
1228 FROM icx_cat_items_ctx_hdrs_tlp
1229 WHERE (p_category_id = 0 OR ip_category_id = p_category_id);
1230
1231 LOOP
1232 l_err_loc := 300;
1233 l_rowid_tbl.DELETE;
1234 l_po_line_id_tbl.DELETE;
1235 l_inventory_item_id_tbl.DELETE;
1236 l_req_template_name_tbl.DELETE;
1237 l_req_template_line_num_tbl.DELETE;
1238 l_org_id_tbl.DELETE;
1239 l_language_tbl.DELETE;
1240
1241 BEGIN
1242 l_err_loc := 400;
1243 FETCH l_csr_var
1244 BULK COLLECT INTO l_rowid_tbl, l_po_line_id_tbl, l_inventory_item_id_tbl,
1245 l_req_template_name_tbl, l_req_template_line_num_tbl,
1246 l_org_id_tbl, l_language_tbl
1247 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
1248
1249 l_err_loc := 500;
1250 EXIT WHEN l_rowid_tbl.COUNT = 0;
1251 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1252 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1253 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1254 'Num. of rows returned from the cursor:' || l_rowid_tbl.COUNT);
1255 END IF;
1256
1257 l_err_loc := 600;
1258 IF (p_category_id > 0) THEN
1259 l_err_loc := 700;
1260 l_start_sequence := ICX_CAT_BUILD_CTX_SQL_PVT.g_seqStartRegularCatgRow + 1;
1261 l_end_sequence := ICX_CAT_BUILD_CTX_SQL_PVT.g_seqEndRegularCatgRow;
1262 l_err_loc := 800;
1263 FORALL i IN 1..l_rowid_tbl.COUNT
1264 DELETE FROM icx_cat_items_ctx_dtls_tlp
1265 WHERE po_line_id = l_po_line_id_tbl(i)
1266 AND inventory_item_id = l_inventory_item_id_tbl(i)
1267 AND req_template_name = l_req_template_name_tbl(i)
1268 AND req_template_line_num = l_req_template_line_num_tbl(i)
1269 AND org_id = l_org_id_tbl(i)
1270 AND language = l_language_tbl(i)
1271 AND sequence BETWEEN l_start_sequence AND l_end_sequence;
1272
1273 l_err_loc := 900;
1274 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1275 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1276 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1277 ' Num. of rows deleted from dtls for category_id:' || SQL%ROWCOUNT);
1278 END IF;
1279 ELSE
1280 l_err_loc := 1000;
1281 IF p_attribute_key IN ('SUPPLIER_PART_NUM', 'SUPPLIER_PART_AUXID') THEN
1282 l_err_loc := 1100;
1283 -- Delete the row with sequence 1 for re-populate
1284 FORALL i IN 1..l_rowid_tbl.COUNT
1285 DELETE FROM icx_cat_items_ctx_dtls_tlp
1286 WHERE po_line_id = l_po_line_id_tbl(i)
1287 AND inventory_item_id = l_inventory_item_id_tbl(i)
1288 AND req_template_name = l_req_template_name_tbl(i)
1289 AND req_template_line_num = l_req_template_line_num_tbl(i)
1290 AND org_id = l_org_id_tbl(i)
1291 AND language = l_language_tbl(i)
1292 AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqMandatoryBaseRow;
1293
1294 l_err_loc := 1200;
1295 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1296 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1297 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1298 'Num. of rows deleted from dtls for partNum, auxid:' || SQL%ROWCOUNT);
1299 END IF;
1300 ELSIF (p_attribute_key = 'SUPPLIER') THEN
1301 l_err_loc := 1300;
1302 -- Delete the row with sequence 2 for re-populate
1303 FORALL i IN 1..l_rowid_tbl.COUNT
1304 DELETE FROM icx_cat_items_ctx_dtls_tlp
1305 WHERE po_line_id = l_po_line_id_tbl(i)
1306 AND inventory_item_id = l_inventory_item_id_tbl(i)
1307 AND req_template_name = l_req_template_name_tbl(i)
1308 AND req_template_line_num = l_req_template_line_num_tbl(i)
1309 AND org_id = l_org_id_tbl(i)
1310 AND language = l_language_tbl(i)
1311 AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForSupplierRow;
1312
1313 l_err_loc := 1400;
1314 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1315 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1316 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1317 'Num. of rows deleted from dtls for supp:' || SQL%ROWCOUNT);
1318 END IF;
1319 ELSIF (p_attribute_key = 'INTERNAL_ITEM_NUM') THEN
1320 l_err_loc := 1500;
1321 -- Delete the row with sequence 3 for re-populate
1322 FORALL i IN 1..l_rowid_tbl.COUNT
1323 DELETE FROM icx_cat_items_ctx_dtls_tlp
1324 WHERE po_line_id = l_po_line_id_tbl(i)
1325 AND inventory_item_id = l_inventory_item_id_tbl(i)
1326 AND req_template_name = l_req_template_name_tbl(i)
1327 AND req_template_line_num = l_req_template_line_num_tbl(i)
1328 AND org_id = l_org_id_tbl(i)
1329 AND language = l_language_tbl(i)
1330 AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForInternalItemNumRow;
1331
1332 l_err_loc := 1600;
1333 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1334 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1335 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1336 'Num. of rows deleted from dtls for intItemNum:' || SQL%ROWCOUNT);
1337 END IF;
1338 ELSIF (p_attribute_key = 'SOURCE') THEN
1339 l_err_loc := 1700;
1340 -- Delete the row with sequence 4 for re-populate
1341 FORALL i IN 1..l_rowid_tbl.COUNT
1342 DELETE FROM icx_cat_items_ctx_dtls_tlp
1343 WHERE po_line_id = l_po_line_id_tbl(i)
1344 AND inventory_item_id = l_inventory_item_id_tbl(i)
1345 AND req_template_name = l_req_template_name_tbl(i)
1346 AND req_template_line_num = l_req_template_line_num_tbl(i)
1347 AND org_id = l_org_id_tbl(i)
1348 AND language = l_language_tbl(i)
1349 AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForSourceRow;
1350
1351 l_err_loc := 1800;
1352 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1353 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1354 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1355 'Num. of rows deleted from dtls for source:' || SQL%ROWCOUNT);
1356 END IF;
1357 ELSIF (p_attribute_key = 'ITEM_REVISION') THEN
1358 l_err_loc := 1900;
1359 -- Delete the row with sequence 5 for re-populate
1360 FORALL i IN 1..l_rowid_tbl.COUNT
1361 DELETE FROM icx_cat_items_ctx_dtls_tlp
1362 WHERE po_line_id = l_po_line_id_tbl(i)
1363 AND inventory_item_id = l_inventory_item_id_tbl(i)
1364 AND req_template_name = l_req_template_name_tbl(i)
1365 AND req_template_line_num = l_req_template_line_num_tbl(i)
1366 AND org_id = l_org_id_tbl(i)
1367 AND language = l_language_tbl(i)
1368 AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForItemRevisionRow;
1369
1370 l_err_loc := 2000;
1371 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1372 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1373 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1374 'Num. of rows deleted from dtls for itemrev:' || SQL%ROWCOUNT);
1375 END IF;
1376 ELSIF (p_attribute_key = 'SHOPPING_CATEGORY') THEN
1377 l_err_loc := 2100;
1378 -- Delete the row with sequence 6 for re-populate
1379 FORALL i IN 1..l_rowid_tbl.COUNT
1380 DELETE FROM icx_cat_items_ctx_dtls_tlp
1381 WHERE po_line_id = l_po_line_id_tbl(i)
1382 AND inventory_item_id = l_inventory_item_id_tbl(i)
1383 AND req_template_name = l_req_template_name_tbl(i)
1384 AND req_template_line_num = l_req_template_line_num_tbl(i)
1385 AND org_id = l_org_id_tbl(i)
1386 AND language = l_language_tbl(i)
1387 AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForShoppingCategoryRow;
1388
1389 l_err_loc := 2200;
1390 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1391 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1392 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1393 'Num. of rows deleted from dtls for shopCatg:' || SQL%ROWCOUNT);
1394 END IF;
1395 ELSE
1396 l_err_loc := 2300;
1397 -- Delete the row with sequence between 100 and 5000 for re-populate
1398 l_start_sequence := ICX_CAT_BUILD_CTX_SQL_PVT.g_seqStartReqularBaseRow + 1;
1399 l_end_sequence := ICX_CAT_BUILD_CTX_SQL_PVT.g_seqEndReqularBaseRow;
1400 l_err_loc := 2400;
1401 FORALL i IN 1..l_rowid_tbl.COUNT
1402 DELETE FROM icx_cat_items_ctx_dtls_tlp
1403 WHERE po_line_id = l_po_line_id_tbl(i)
1404 AND inventory_item_id = l_inventory_item_id_tbl(i)
1405 AND req_template_name = l_req_template_name_tbl(i)
1406 AND req_template_line_num = l_req_template_line_num_tbl(i)
1407 AND org_id = l_org_id_tbl(i)
1408 AND language = l_language_tbl(i)
1409 AND sequence BETWEEN l_start_sequence AND l_end_sequence;
1410
1411 l_err_loc := 2500;
1412 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1413 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1414 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1415 'Num. of rows deleted from dtls for regular base attrs:' || SQL%ROWCOUNT);
1416 END IF;
1417 END IF;
1418 END IF;
1419
1420 l_err_loc := 2600;
1421 FORALL i IN 1..l_rowid_tbl.COUNT
1422 UPDATE icx_cat_items_ctx_hdrs_tlp
1423 SET ctx_desc = NULL,
1424 last_update_login = l_internal_request_id,
1425 last_updated_by = l_internal_request_id,
1426 last_update_date = sysdate,
1427 internal_request_id = l_internal_request_id
1428 WHERE rowid = l_rowid_tbl(i);
1429
1430 l_err_loc := 2800;
1431 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
1432 l_err_loc := 2900;
1433 COMMIT;
1434 -- Must log
1435 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1436 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1437 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1438 'Commit done.');
1439 END IF;
1440 ELSE
1441 l_err_loc := 3000;
1442 -- Must log
1443 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1444 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1445 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1446 'Commit not done.');
1447 END IF;
1448 END IF;
1449
1450 l_err_loc := 3100;
1451 EXIT WHEN l_rowid_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
1452 EXCEPTION
1453 WHEN ICX_CAT_UTIL_PVT.g_snap_shot_too_old THEN
1454 l_err_string := 'ICX_CAT_POPULATE_CTXSTRING_PVT.updateItemsCtxHdrsTlp' ||l_err_loc;
1455 ICX_CAT_UTIL_PVT.logAndCommitSnapShotTooOld(g_pkg_name, l_api_name, l_err_string);
1456 l_err_loc := 3200;
1457 CLOSE l_csr_var;
1458 l_err_loc := 3300;
1459 OPEN l_csr_var FOR
1460 SELECT ROWID, po_line_id, inventory_item_id,
1461 req_template_name, req_template_line_num,
1462 org_id, language
1463 FROM icx_cat_items_ctx_hdrs_tlp
1464 WHERE (p_category_id = 0 OR ip_category_id = p_category_id)
1465 AND internal_request_id <> l_internal_request_id;
1466 END;
1467 END LOOP;
1468
1469 l_err_loc := 3400;
1470 IF (l_csr_var%ISOPEN) THEN
1471 l_err_loc := 3500;
1472 CLOSE l_csr_var;
1473 END IF;
1474
1475 l_err_loc := 3600;
1476 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1477 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1478 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1479 'done.');
1480 END IF;
1481 EXCEPTION
1482 WHEN OTHERS THEN
1483 ICX_CAT_UTIL_PVT.logUnexpectedException(
1484 G_PKG_NAME, l_api_name,
1485 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1486 IF (l_csr_var%ISOPEN) THEN
1487 CLOSE l_csr_var;
1488 END IF;
1489 RAISE;
1490 END updateItemsCtxHdrsTlp;
1491
1492 PROCEDURE rePopulateCategoryAttributes
1493 ( p_category_id IN NUMBER
1494 )
1495 IS
1496 l_api_name CONSTANT VARCHAR2(30) := 'rePopulateCategoryAttributes';
1497 l_err_loc PLS_INTEGER;
1498 l_internal_request_id NUMBER;
1499 l_start_date DATE;
1500 l_end_date DATE;
1501 l_log_string VARCHAR2(2000);
1502 BEGIN
1503 l_err_loc := 100;
1504 l_start_date := sysdate;
1505
1506 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1507 l_log_string := 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ||
1508 ';(' || p_category_id ||');' ;
1509 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
1510 END IF;
1511
1512 -- Update ctx_desc and internal_request_id for all items with ip_category_id = p_category_id in hdrs tlp
1513 -- Delete all rows between 5001 and 9999 for items that has ip_category_id = p_category_id
1514 -- Call the populateCtxCatgAtt with the requestId
1515 updateItemsCtxHdrsTlp(p_category_id, l_internal_request_id, null);
1516 l_err_loc := 200;
1517 populateCtxCatgAtt(l_internal_request_id);
1518
1519 l_end_date := sysdate;
1520 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1521 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
1522 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
1523 END IF;
1524 EXCEPTION
1525 WHEN OTHERS THEN
1526 ICX_CAT_UTIL_PVT.logUnexpectedException(
1527 G_PKG_NAME, l_api_name,
1528 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1529 RAISE;
1530 END rePopulateCategoryAttributes;
1531
1532 PROCEDURE rePopulateBaseAttributes
1533 ( p_attribute_key IN VARCHAR2 ,
1534 p_searchable IN NUMBER
1535 )
1536 IS
1537 l_api_name CONSTANT VARCHAR2(30) := 'rePopulateBaseAttributes';
1538 l_err_loc PLS_INTEGER;
1539 l_special_metadata_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_metadata_tbl_type;
1540 l_regular_nontl_metadata_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_metadata_tbl_type;
1541 l_regular_tl_metadata_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_metadata_tbl_type;
1542 l_all_ctx_sql_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type;
1543 l_special_ctx_sql_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type;
1544 l_regular_ctx_sql_tbl ICX_CAT_BUILD_CTX_SQL_PVT.g_ctx_sql_tbl_type;
1545 l_internal_request_id NUMBER;
1546 l_start_date DATE;
1547 l_end_date DATE;
1548 l_log_string VARCHAR2(2000);
1549 BEGIN
1550 -- Update ctx_desc and internal_request_id for all items in hdrs tlp
1551 -- Call the buildMetaDataInfo for the ip_category_id = 0
1552 -- IF p_attribute_key is a special attribute then
1553 -- Re-populate only special attribute rows i.e. sequence between 1 and 100
1554 -- Else re-populate all rows between 100 and 5000
1555 -- Call the buildCtxSql for each source
1556
1557 l_err_loc := 100;
1558 l_start_date := sysdate;
1559
1560 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1561 l_log_string := 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ||
1562 ';(' || p_attribute_key ||', ' || p_searchable || ');' ;
1563 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
1564 END IF;
1565
1566 -- If the changed descriptor is one of the following, then just return no processing is needed
1567 l_err_loc := 200;
1568 IF ( p_attribute_key IN ('UOM', 'PRICE', 'CURRENCY',
1569 'FUNCTIONAL_PRICE', 'FUNCTIONAL_CURRENCY',
1570 'SUPPLIER_SITE', 'PURCHASING_CATEGORY',
1571 'THUMBNAIL_IMAGE', 'PICTURE', 'ATTACHMENT_URL',
1572 'SUPPLIER_URL', 'MANUFACTURER_URL'))
1573 THEN
1574 l_err_loc := 300;
1575 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1576 FND_LOG.string(FND_LOG.LEVEL_EVENT,
1577 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1578 'returning; for p_attribute_key:' || p_attribute_key);
1579 END IF;
1580 RETURN;
1581 END IF;
1582
1583 l_err_loc := 400;
1584 -- update the ctx_desc in icx_cat_items_ctx_hdrs_tlp and
1585 -- delete the appropriate row/rows from icx_cat_items_ctx_dtls_tlp
1586 updateItemsCtxHdrsTlp(0, l_internal_request_id, p_attribute_key);
1587
1588 l_err_loc := 500;
1589 -- Need to insert the appropriate row/rows back into
1590 -- icx_cat_items_ctx_dtls_tlp depending upon the source and p_attribute_key
1591 ICX_CAT_BUILD_CTX_SQL_PVT.buildMetadataInfo
1592 (0, l_special_metadata_tbl, l_regular_nontl_metadata_tbl, l_regular_tl_metadata_tbl);
1593
1594 l_err_loc := 600;
1595 ICX_CAT_BUILD_CTX_SQL_PVT.buildCtxSql
1596 (0, ICX_CAT_UTIL_PVT.g_PODoc_const, 'ROWID', l_special_metadata_tbl,
1597 l_regular_nontl_metadata_tbl, l_regular_tl_metadata_tbl,
1598 l_all_ctx_sql_tbl, l_special_ctx_sql_tbl, l_regular_ctx_sql_tbl);
1599
1600 l_err_loc := 700;
1601
1602 IF (p_attribute_key IN ('SUPPLIER_PART_NUM', 'SUPPLIER_PART_AUXID')) THEN
1603 l_err_loc := 800;
1604 -- Row with sequence 1
1605 popCtxBaseSpecAttForAllSrc(ICX_CAT_BUILD_CTX_SQL_PVT.g_seqMandatoryBaseRow,
1606 l_special_ctx_sql_tbl);
1607 END IF;
1608
1609 IF (p_searchable = 1 AND
1610 p_attribute_key IN ('SUPPLIER_PART_NUM', 'SUPPLIER_PART_AUXID', 'SUPPLIER',
1611 'INTERNAL_ITEM_NUM', 'SOURCE', 'ITEM_REVISION',
1612 'SHOPPING_CATEGORY'))
1613 THEN
1614 l_err_loc := 900;
1615 IF (p_attribute_key = 'SUPPLIER') THEN
1616 l_err_loc := 1000;
1617 -- Row with sequence 2
1618 popCtxBaseSpecSupplierAtt(l_special_ctx_sql_tbl);
1619 ELSIF (p_attribute_key = 'INTERNAL_ITEM_NUM') THEN
1620 l_err_loc := 1100;
1621 -- Row with sequence 3
1622 popCtxBaseSpecAttForAllSrc(ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForInternalItemNumRow,
1623 l_special_ctx_sql_tbl);
1624 ELSIF (p_attribute_key IN ('SOURCE', 'ITEM_REVISION')) THEN
1625 l_err_loc := 1200;
1626 -- Row with sequence 4 (source) and 5 (Item Revision)
1627 IF (p_attribute_key = 'SOURCE') THEN
1628 l_err_loc := 1300;
1629 popCtxBaseSpecSrcAndItemRevAtt(ICX_CAT_UTIL_PVT.g_PODoc_const,
1630 l_special_ctx_sql_tbl,
1631 ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForSourceRow);
1632 ELSE
1633 l_err_loc := 1400;
1634 popCtxBaseSpecSrcAndItemRevAtt(ICX_CAT_UTIL_PVT.g_PODoc_const,
1635 l_special_ctx_sql_tbl,
1636 ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForItemRevisionRow);
1637 END IF;
1638
1639 l_err_loc := 1500;
1640 l_all_ctx_sql_tbl.DELETE;
1641 l_special_ctx_sql_tbl.DELETE;
1642 l_regular_ctx_sql_tbl.DELETE;
1643
1644 l_err_loc := 1600;
1645 -- Build the ctx sqls for Req templates.
1646 ICX_CAT_BUILD_CTX_SQL_PVT.buildCtxSql
1647 (0, ICX_CAT_UTIL_PVT.g_ReqTemplateCsr_const, 'ROWID', l_special_metadata_tbl,
1648 l_regular_nontl_metadata_tbl, l_regular_tl_metadata_tbl,
1649 l_all_ctx_sql_tbl, l_special_ctx_sql_tbl, l_regular_ctx_sql_tbl);
1650 l_err_loc := 1700;
1651 IF (p_attribute_key = 'SOURCE') THEN
1652 l_err_loc := 1800;
1653 popCtxBaseSpecSrcAndItemRevAtt(ICX_CAT_UTIL_PVT.g_ReqTemplateCsr_const,
1654 l_special_ctx_sql_tbl,
1655 ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForSourceRow);
1656 ELSE
1657 l_err_loc := 1900;
1658 popCtxBaseSpecSrcAndItemRevAtt(ICX_CAT_UTIL_PVT.g_ReqTemplateCsr_const,
1659 l_special_ctx_sql_tbl,
1660 ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForItemRevisionRow);
1661 END IF;
1662
1663 l_err_loc := 2000;
1664 ELSIF (p_attribute_key = 'SHOPPING_CATEGORY') THEN
1665 l_err_loc := 2400;
1666 -- Row with sequence 6
1667 popCtxBaseSpecAttForAllSrc(ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForShoppingCategoryRow,
1668 l_special_ctx_sql_tbl);
1669 END IF;
1670 ELSE
1671 l_err_loc := 2500;
1672 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1673 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1674 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1675 'In else loop for p_attribute_key:' || p_attribute_key);
1676 END IF;
1677 -- Row with sequence between 101 - 5000
1678 l_err_loc := 2600;
1679 popCtxBaseRegularAttributes(ICX_CAT_UTIL_PVT.g_PODoc_const,
1680 l_regular_ctx_sql_tbl);
1681
1682 l_err_loc := 2700;
1683 l_all_ctx_sql_tbl.DELETE;
1684 l_special_ctx_sql_tbl.DELETE;
1685 l_regular_ctx_sql_tbl.DELETE;
1686
1687 l_err_loc := 2800;
1688 -- Build the ctx_sql_table for Master Items
1689 ICX_CAT_BUILD_CTX_SQL_PVT.buildCtxSql
1690 (0, ICX_CAT_UTIL_PVT.g_MasterItemCsr_const, 'ROWID', l_special_metadata_tbl,
1691 l_regular_nontl_metadata_tbl, l_regular_tl_metadata_tbl,
1692 l_all_ctx_sql_tbl, l_special_ctx_sql_tbl, l_regular_ctx_sql_tbl);
1693 l_err_loc := 2900;
1694 popCtxBaseRegularAttributes(ICX_CAT_UTIL_PVT.g_MasterItemCsr_const,
1695 l_regular_ctx_sql_tbl);
1696 END IF;
1697
1698 l_err_loc := 3000;
1699 l_end_date := sysdate;
1700 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1701 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
1702 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
1703 END IF;
1704 EXCEPTION
1705 WHEN OTHERS THEN
1706 ICX_CAT_UTIL_PVT.logUnexpectedException(
1707 G_PKG_NAME, l_api_name,
1708 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1709 RAISE;
1710 END rePopulateBaseAttributes;
1711
1712 PROCEDURE handleSearchableFlagChange
1713 ( p_attribute_id IN NUMBER ,
1714 p_attribute_key IN VARCHAR2 ,
1715 p_category_id IN NUMBER ,
1716 p_searchable IN NUMBER
1717 )
1718 IS
1719 l_api_name CONSTANT VARCHAR2(30) := 'handleSearchableFlagChange';
1720 l_err_loc PLS_INTEGER;
1721 l_start_date DATE;
1722 l_end_date DATE;
1723 l_log_string VARCHAR2(2000);
1724 BEGIN
1725 l_err_loc := 100;
1726 l_start_date := sysdate;
1727
1728 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1729 l_log_string := 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ;
1730 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
1731 END IF;
1732
1733 l_err_loc := 200;
1734 ICX_CAT_UTIL_PVT.setBatchSize;
1735
1736 l_err_loc := 300;
1737 ICX_CAT_UTIL_PVT.setCommitParameter(FND_API.G_TRUE);
1738
1739 l_err_loc := 400;
1740 IF (p_category_id = 0) THEN
1741 l_err_loc := 500;
1742 rePopulateBaseAttributes(p_attribute_key, p_searchable);
1743 ELSE
1744 l_err_loc := 600;
1745 -- Update ctx_desc and internal_request_id for all items with ip_category_id = p_category_id in hdrs tlp
1746 -- Delete all rows between 5001 and 9999 for items that has ip_category_id = p_category_id
1747 -- Call the populateCtxCatgAtt with the requestId
1748 rePopulateCategoryAttributes(p_category_id);
1749 END IF;
1750
1751 l_err_loc := 700;
1752 -- Call the rebuild index
1753 ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
1754
1755 l_end_date := sysdate;
1756 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1757 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
1758 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
1759 END IF;
1760 EXCEPTION
1761 WHEN OTHERS THEN
1762 ICX_CAT_UTIL_PVT.logUnexpectedException(
1763 G_PKG_NAME, l_api_name,
1764 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1765 ROLLBACK;
1766 -- Call the rebuild index
1767 ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
1768 RAISE;
1769 END handleSearchableFlagChange;
1770
1771 PROCEDURE handleCategoryRename
1772 ( p_category_id IN NUMBER ,
1773 p_category_name IN VARCHAR2 ,
1774 p_language IN VARCHAR2
1775 )
1776 IS
1777
1778 l_api_name CONSTANT VARCHAR2(30) := 'handleCategoryRename';
1779 l_err_loc PLS_INTEGER;
1780 l_internal_request_id NUMBER;
1781 l_searchable NUMBER;
1782 l_section_tag NUMBER;
1783 l_csr_var g_csr_type;
1784 l_rowid_tbl DBMS_SQL.UROWID_TABLE;
1785 l_po_line_id_tbl DBMS_SQL.NUMBER_TABLE;
1786 l_inventory_item_id_tbl DBMS_SQL.NUMBER_TABLE;
1787 l_req_template_name_tbl DBMS_SQL.VARCHAR2_TABLE;
1788 l_req_template_line_num_tbl DBMS_SQL.NUMBER_TABLE;
1789 l_org_id_tbl DBMS_SQL.NUMBER_TABLE;
1790 l_language_tbl DBMS_SQL.VARCHAR2_TABLE;
1791 l_err_string VARCHAR2(4000);
1792 l_start_date DATE;
1793 l_end_date DATE;
1794 l_log_string VARCHAR2(2000);
1795 BEGIN
1796 l_err_loc := 100;
1797 l_start_date := sysdate;
1798
1799 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1800 l_log_string := 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS') ;
1801 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
1802 END IF;
1803
1804 l_err_loc := 200;
1805 ICX_CAT_UTIL_PVT.setBatchSize;
1806
1807 l_err_loc := 300;
1808 ICX_CAT_UTIL_PVT.setCommitParameter(FND_API.G_TRUE);
1809
1810 l_err_loc := 400;
1811 -- Update ctx_desc and internal_request_id for all items belonging to p_category_id and p_language in hdrs tlp
1812 l_internal_request_id := ICX_CAT_UTIL_PVT.getNextSequenceForWhoColumns;
1813
1814 l_err_loc := 500;
1815 ICX_CAT_BUILD_CTX_SQL_PVT.checkIfAttributeIsSrchble
1816 ('SHOPPING_CATEGORY', l_searchable, l_section_tag);
1817
1818 l_err_loc := 600;
1819 OPEN l_csr_var FOR
1820 SELECT ROWID, po_line_id, inventory_item_id,
1821 req_template_name, req_template_line_num,
1822 org_id, language
1823 FROM icx_cat_items_ctx_hdrs_tlp
1824 WHERE ip_category_id = p_category_id
1825 AND language = p_language;
1826
1827 LOOP
1828 l_err_loc := 700;
1829 l_rowid_tbl.DELETE;
1830 l_po_line_id_tbl.DELETE;
1831 l_inventory_item_id_tbl.DELETE;
1832 l_req_template_name_tbl.DELETE;
1833 l_req_template_line_num_tbl.DELETE;
1834 l_org_id_tbl.DELETE;
1835 l_language_tbl.DELETE;
1836
1837 BEGIN
1838 l_err_loc := 800;
1839 FETCH l_csr_var
1840 BULK COLLECT INTO l_rowid_tbl, l_po_line_id_tbl, l_inventory_item_id_tbl,
1841 l_req_template_name_tbl, l_req_template_line_num_tbl,
1842 l_org_id_tbl, l_language_tbl
1843 LIMIT ICX_CAT_UTIL_PVT.g_batch_size;
1844
1845 l_err_loc := 900;
1846 EXIT WHEN l_rowid_tbl.COUNT = 0;
1847 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1848 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1849 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1850 'Num of rows from ctx_hdrs for category rename:' || l_rowid_tbl.COUNT);
1851 END IF;
1852
1853 l_err_loc := 1000;
1854 IF (l_searchable = 1) THEN
1855 l_err_loc := 1100;
1856 FORALL i IN 1..l_rowid_tbl.COUNT
1857 UPDATE icx_cat_items_ctx_dtls_tlp
1858 SET ctx_desc = '<' || l_section_tag || '>' ||
1859 replace(replace(p_category_name, '<', ' '), '>', ' ') ||
1860 '</' || l_section_tag || '>'
1861 WHERE po_line_id = l_po_line_id_tbl(i)
1862 AND inventory_item_id = l_inventory_item_id_tbl(i)
1863 AND req_template_name = l_req_template_name_tbl(i)
1864 AND req_template_line_num = l_req_template_line_num_tbl(i)
1865 AND org_id = l_org_id_tbl(i)
1866 AND language = l_language_tbl(i)
1867 AND sequence = ICX_CAT_BUILD_CTX_SQL_PVT.g_seqForShoppingCategoryRow;
1868
1869 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1870 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1871 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1872 'Num of rows updated in ctx_dtls for category rename:' || SQL%ROWCOUNT);
1873 END IF;
1874 ELSE
1875 l_err_loc := 1200;
1876 -- Must log
1877 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1878 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1879 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1880 'Shopping Category attribute is not searchable, ' ||
1881 'so no changes needed in icx_cat_items_ctx_dtls_tlp; ' ||
1882 'l_searchable:' || l_searchable || ', l_section_tag:' || l_section_tag );
1883 END IF;
1884 END IF; -- IF (l_searchable = 1) THEN
1885
1886 l_err_loc := 1300;
1887 FORALL i IN 1..l_rowid_tbl.COUNT
1888 UPDATE icx_cat_items_ctx_hdrs_tlp
1889 SET ctx_desc = NULL,
1890 ip_category_name = p_category_name,
1891 last_update_login = l_internal_request_id,
1892 last_updated_by = l_internal_request_id,
1893 last_update_date = sysdate,
1894 internal_request_id = l_internal_request_id
1895 WHERE rowid = l_rowid_tbl(i);
1896
1897 l_err_loc := 1400;
1898 IF (FND_API.To_Boolean(ICX_CAT_UTIL_PVT.g_COMMIT)) THEN
1899 l_err_loc := 1500;
1900 COMMIT;
1901 -- Must log
1902 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1903 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1904 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1905 'Commit done.');
1906 END IF;
1907 ELSE
1908 l_err_loc := 1600;
1909 -- Must log
1910 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1911 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1912 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1913 'Commit not done.');
1914 END IF;
1915 END IF;
1916
1917 l_err_loc := 1700;
1918 EXIT WHEN l_rowid_tbl.COUNT < ICX_CAT_UTIL_PVT.g_batch_size;
1919 EXCEPTION
1920 WHEN ICX_CAT_UTIL_PVT.g_snap_shot_too_old THEN
1921 l_err_string := 'ICX_CAT_POPULATE_CTXSTRING_PVT.handleCategoryRename' ||l_err_loc;
1922 ICX_CAT_UTIL_PVT.logAndCommitSnapShotTooOld(g_pkg_name, l_api_name, l_err_string);
1923 l_err_loc := 1800;
1924 CLOSE l_csr_var;
1925 l_err_loc := 1900;
1926 OPEN l_csr_var FOR
1927 SELECT ROWID, po_line_id, inventory_item_id,
1928 req_template_name, req_template_line_num,
1929 org_id, language
1930 FROM icx_cat_items_ctx_hdrs_tlp
1931 WHERE ip_category_id = p_category_id
1932 AND language = p_language
1933 AND internal_request_id <> l_internal_request_id;
1934 END;
1935 END LOOP;
1936
1937 l_err_loc := 2000;
1938 IF (l_csr_var%ISOPEN) THEN
1939 l_err_loc := 2100;
1940 CLOSE l_csr_var;
1941 END IF;
1942
1943 l_err_loc := 2200;
1944 -- Call the rebuild index
1945 ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
1946
1947 l_err_loc := 2300;
1948 l_end_date := sysdate;
1949 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1950 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
1951 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
1952 END IF;
1953
1954 EXCEPTION
1955 WHEN OTHERS THEN
1956 ICX_CAT_UTIL_PVT.logUnexpectedException(
1957 G_PKG_NAME, l_api_name,
1958 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
1959 ROLLBACK;
1960 -- Call the rebuild index
1961 ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
1962 RAISE;
1963 END handleCategoryRename;
1964
1965 END ICX_CAT_POPULATE_CTXSTRING_PVT;