[Home] [Help]
PACKAGE BODY: APPS.ICX_CAT_BUILD_CTX_SQL_PVT
Source
1 PACKAGE BODY ICX_CAT_BUILD_CTX_SQL_PVT AS
2 /* $Header: ICXVBCSB.pls 120.12 2011/07/25 06:14:36 rkandima ship $*/
3
4 -- Global Constants
5 G_PKG_NAME CONSTANT VARCHAR2(30) :='ICX_CAT_BUILD_CTX_SQL_PVT';
6
7 -- Default max length for each row in icx_por_ctx_tl, set to 3600, leaving
8 -- 400 bytes for section tags
9 g_default_max_length CONSTANT NUMBER := 3600;
10
11 PROCEDURE checkIfAttributeIsSrchble(p_attribute_key IN VARCHAR2,
12 p_searchable OUT NOCOPY NUMBER,
13 p_section_tag OUT NOCOPY NUMBER)
14 IS
15 l_api_name CONSTANT VARCHAR2(30) := 'checkIfAttributeIsSrchble';
16 l_err_loc PLS_INTEGER;
17 BEGIN
18 l_err_loc := 100;
19 SELECT searchable, section_tag
20 INTO p_searchable, p_section_tag
21 FROM icx_cat_attributes_tl
22 WHERE key = p_attribute_key
23 AND rownum = 1;
24
25 l_err_loc := 200;
26 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
27 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
28 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
29 'For p_attribute_key:' || p_attribute_key ||
30 ', p_searchable:' || p_searchable ||
31 ', p_section_tag:' || p_section_tag );
32 END IF;
33 EXCEPTION
34 WHEN OTHERS THEN
35 ICX_CAT_UTIL_PVT.logUnexpectedException(
36 G_PKG_NAME, l_api_name,
37 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
38 p_searchable := 0;
39 p_section_tag:= -1;
40 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
41 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
42 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
43 'Exception caught, So for p_attribute_key:' || p_attribute_key ||
44 ', p_searchable:' || p_searchable ||
45 ', p_section_tag:' || p_section_tag );
46 END IF;
47 END checkIfAttributeIsSrchble;
48
49 PROCEDURE buildMetadataInfo(p_category_id IN NUMBER,
50 p_special_metadata_tbl IN OUT NOCOPY g_metadata_tbl_type,
51 p_regular_nontl_metadata_tbl IN OUT NOCOPY g_metadata_tbl_type,
52 p_regular_tl_metadata_tbl IN OUT NOCOPY g_metadata_tbl_type)
53 IS
54 CURSOR getSearchableMetadataCsr(p_category_id NUMBER) IS
55 SELECT attribute_id, key, type,
56 section_tag, stored_in_table, stored_in_column
57 FROM icx_cat_attributes_tl
58 WHERE rt_category_id = p_category_id
59 AND language = ( SELECT language_code FROM fnd_languages WHERE installed_flag = 'B')
60 AND searchable = 1
61 ORDER BY attribute_id;
62
63 ----- Start of declaring columns selected in the cursor -----
64
65 l_attribute_id_tbl DBMS_SQL.NUMBER_TABLE;
66 l_key_tbl DBMS_SQL.VARCHAR2_TABLE;
67 l_type_tbl DBMS_SQL.NUMBER_TABLE;
68 l_section_tag_tbl DBMS_SQL.NUMBER_TABLE;
69 l_stored_in_table_tbl DBMS_SQL.VARCHAR2_TABLE;
70 l_stored_in_column_tbl DBMS_SQL.VARCHAR2_TABLE;
71
72 ------ End of declaring columns selected in the cursor ------
73
74 l_api_name CONSTANT VARCHAR2(30) := 'buildMetadataInfo';
75 l_err_loc PLS_INTEGER := 100;
76 l_special_index NUMBER := 0;
77 l_regular_nontl_index NUMBER := 0;
78 l_regular_tl_index NUMBER := 0;
79 l_category_index NUMBER := 0;
80 l_metadata_rec g_metadata_rec_type;
81 BEGIN
82 l_err_loc := 100;
83 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
84 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
85 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
86 ', p_category_id:' || p_category_id);
87 END IF;
88
89 OPEN getSearchableMetadataCsr(p_category_id);
90
91 l_err_loc := 200;
92 l_attribute_id_tbl.DELETE;
93 l_key_tbl.DELETE;
94 l_type_tbl.DELETE;
95 l_section_tag_tbl.DELETE;
96 l_stored_in_table_tbl.DELETE;
97 l_stored_in_column_tbl.DELETE;
98
99 l_err_loc := 250;
100 FETCH getSearchableMetadataCsr BULK COLLECT INTO
101 l_attribute_id_tbl, l_key_tbl, l_type_tbl,
102 l_section_tag_tbl, l_stored_in_table_tbl, l_stored_in_column_tbl;
103
104 l_err_loc := 300;
105 CLOSE getSearchableMetadataCsr;
106
107 l_err_loc := 400;
108 FOR i IN 1..l_attribute_id_tbl.COUNT LOOP
109
110 l_err_loc := 500;
111 l_metadata_rec.attribute_id := l_attribute_id_tbl(i);
112 l_metadata_rec.key := l_key_tbl(i);
113 l_metadata_rec.type := l_type_tbl(i);
114 l_metadata_rec.section_tag := l_section_tag_tbl(i);
115 l_metadata_rec.stored_in_table := l_stored_in_table_tbl(i);
116 l_metadata_rec.stored_in_column := l_stored_in_column_tbl(i);
117
118 l_err_loc := 600;
119 IF (l_type_tbl(i) IN (0,2)) THEN
120 l_err_loc := 700;
121 IF (l_key_tbl(i) = 'LONG_DESCRIPTION' AND p_category_id = 0) THEN
122 l_err_loc := 800;
123 l_metadata_rec.attribute_length := 2000;
124 ELSIF (l_key_tbl(i) = 'DESCRIPTION' AND p_category_id = 0) THEN
125 l_err_loc := 900;
126 l_metadata_rec.attribute_length := 240;
127 ELSE
128 l_err_loc := 1000;
129 l_metadata_rec.attribute_length := 700;
130 END IF;
131 ELSE
132 l_err_loc := 1100;
133 l_metadata_rec.attribute_length := 100;
134 END IF;
135
136 l_err_loc := 1200;
137 IF (p_category_id = 0 AND
138 l_key_tbl(i) IN ('SUPPLIER', 'INTERNAL_ITEM_NUM', 'SOURCE',
139 'ITEM_REVISION', 'SHOPPING_CATEGORY',
140 'SUPPLIER_PART_NUM', 'SUPPLIER_PART_AUXID'))
141 THEN
142 l_err_loc := 1300;
143 l_special_index := l_special_index + 1;
144 p_special_metadata_tbl(l_special_index) := l_metadata_rec;
145 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
146 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
147 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
148 'specialIndex:' || l_special_index ||
149 ', key:' || l_metadata_rec.key ||
150 ', type:' || l_metadata_rec.type ||
151 ', p_category_id:' || p_category_id);
152 END IF;
153 ELSIF (l_stored_in_table_tbl(i) IS NOT NULL AND
154 l_stored_in_column_tbl(i) IS NOT NULL)
155 THEN
156 l_err_loc := 1400;
157 IF (l_type_tbl(i) <> 2) THEN
158 l_err_loc := 1500;
159 l_regular_nontl_index := l_regular_nontl_index + 1;
160 p_regular_nontl_metadata_tbl(l_regular_nontl_index) := l_metadata_rec;
161 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
162 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
163 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
164 'l_regular_nontl_index:' || l_regular_nontl_index ||
165 ', key:' || l_metadata_rec.key ||
166 ', type:' || l_metadata_rec.type ||
167 ', length:' || l_metadata_rec.attribute_length ||
168 ', p_category_id:' || p_category_id);
169 END IF;
170 ELSE
171 l_err_loc := 1600;
172 l_regular_tl_index := l_regular_tl_index + 1;
173 p_regular_tl_metadata_tbl(l_regular_tl_index) := l_metadata_rec;
174 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
175 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
176 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
177 'l_regular_tl_index:' || l_regular_tl_index ||
178 ', key:' || l_metadata_rec.key ||
179 ', type:' || l_metadata_rec.type ||
180 ', length:' || l_metadata_rec.attribute_length ||
181 ', p_category_id:' || p_category_id);
182 END IF;
183 END IF;
184 END IF;
185 END LOOP;
186
187 l_err_loc := 1700;
188 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
189 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
190 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
191 'done.');
192 END IF;
193 EXCEPTION
194 WHEN OTHERS THEN
195 ICX_CAT_UTIL_PVT.logUnexpectedException(
196 G_PKG_NAME, l_api_name,
197 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
198 RAISE;
199 END buildMetadataInfo;
200
201 PROCEDURE getAttributeDetails(p_special_metadata_tbl IN g_metadata_tbl_type,
202 p_attribute_key IN VARCHAR2,
203 p_attribute_searchable IN OUT NOCOPY VARCHAR2,
204 p_metadata_rec IN OUT NOCOPY g_metadata_rec_type)
205 IS
206 l_api_name CONSTANT VARCHAR2(30) := 'getAttributeDetails';
207 l_err_loc PLS_INTEGER;
208 l_metadata_rec g_metadata_rec_type;
209 BEGIN
210 l_err_loc := 100;
211 p_attribute_searchable := 'N';
212 FOR i IN 1..p_special_metadata_tbl.COUNT LOOP
213 l_err_loc := 200;
214 l_metadata_rec := p_special_metadata_tbl(i);
215 IF (l_metadata_rec.key = p_attribute_key) THEN
216 l_err_loc := 300;
217 p_attribute_searchable := 'Y';
218 p_metadata_rec := l_metadata_rec;
219 EXIT;
220 END IF;
221 END LOOP;
222
223 l_err_loc := 400;
224 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
225 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
226 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
227 'For p_attribute_key:' || p_attribute_key ||
228 ', p_attribute_searchable:' || p_attribute_searchable ||
229 ', p_metadata_rec.section_tag:' || p_metadata_rec.section_tag );
230 END IF;
231 EXCEPTION
232 WHEN OTHERS THEN
233 ICX_CAT_UTIL_PVT.logUnexpectedException(
234 G_PKG_NAME, l_api_name,
235 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
236 RAISE;
237 END getAttributeDetails;
238
239 /*
240 Values of p_doc_source:
241
242 ICX_CAT_UTIL_PVT.g_PODoc_const VARCHAR2(15) := 'PO_DOCUMENTS';
243 ICX_CAT_UTIL_PVT.g_ReqTemplateCsr_const VARCHAR2(15) := 'ReqTemplate';
244 ICX_CAT_UTIL_PVT.g_MasterItemCsr_const VARCHAR2(15) := 'MASTER_ITEM';
245 Values of p_where_clause:
246 ROWID / NOTROWID
247 p_where_clause = NOTROWID; when we are dealing with only one source i.e. BLANKETS, GBPAs, Quotes,
248 ReqTemplates and Master Items
249 Because these are the only cases when we need to create the records
250 in icx_cat_items_ctx_dtls_tlp for a certain set of records.
251 For all other cases we need to create the dtls records for all the rows,
252 so it is better to passp_where_clause as ROWID
253 these include vendor changes, online changes to descriptors etc.
254 */
255 PROCEDURE buildCtxSql(p_category_id IN NUMBER,
256 p_doc_source IN VARCHAR2,
257 p_where_clause IN VARCHAR2 DEFAULT 'ROWID',
258 p_special_metadata_tbl IN g_metadata_tbl_type,
259 p_regular_nontl_metadata_tbl IN g_metadata_tbl_type,
260 p_regular_tl_metadata_tbl IN g_metadata_tbl_type,
261 p_all_ctx_sql_tbl IN OUT NOCOPY g_ctx_sql_tbl_type,
262 p_special_ctx_sql_tbl IN OUT NOCOPY g_ctx_sql_tbl_type,
263 p_regular_ctx_sql_tbl IN OUT NOCOPY g_ctx_sql_tbl_type)
264 IS
265 l_api_name CONSTANT VARCHAR2(30) := 'buildCtxSql';
266 l_err_loc PLS_INTEGER;
267 l_ctxsql_prefixStr VARCHAR2(4000) := NULL;
268 l_ctxfspsql_prefixStr VARCHAR2(4000) := NULL;
269 l_ctxsql_mandatoryBaseRowStr VARCHAR2(4000) := NULL;
270 l_ctxsql_suffixStr VARCHAR2(4000) := NULL;
271 l_ctxsql_string VARCHAR2(32000) := NULL;
272 l_searchable VARCHAR2(1) := NULL;
273 l_metadata_rec g_metadata_rec_type;
274 l_ctx_sql_rec g_ctx_sql_rec_type;
275 l_all_ctx_sql_index NUMBER := 0;
276 l_special_ctx_sql_index NUMBER := 0;
277 l_regular_ctx_sql_index NUMBER := 0;
278 l_ctx_sql_next_sequence NUMBER := 0;
279 l_current_length NUMBER := 0;
280 BEGIN
281 l_err_loc := 100;
282 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
283 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
284 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
285 ' In parameters; categoryId:' || p_category_id ||
286 ', p_doc_source:' || p_doc_source ||
287 ', p_where_clause:' || p_where_clause ||
288 ', p_special_metadata_tbl.COUNT:' || p_special_metadata_tbl.COUNT ||
289 ', p_regular_nontl_metadata_tbl.COUNT:' || p_regular_nontl_metadata_tbl.COUNT ||
290 ', p_regular_tl_metadata_tbl.COUNT:' || p_regular_tl_metadata_tbl.COUNT || ';');
291 END IF;
292
293 l_err_loc := 150;
294 l_ctxsql_prefixStr :=
295 'INSERT INTO icx_cat_items_ctx_dtls_tlp ' ||
296 '(inventory_item_id, po_line_id, req_template_name, ' ||
297 'req_template_line_num, org_id, language, ' ||
298 'last_update_login, last_updated_by, last_update_date, ' ||
299 'internal_request_id, request_id, created_by, creation_date, ' ||
300 'sequence, ctx_desc) ' ||
301 'SELECT hdrs.inventory_item_id, hdrs.po_line_id, hdrs.req_template_name, ' ||
302 'hdrs.req_template_line_num, hdrs.org_id, hdrs.language, ' ||
303 'hdrs.last_update_login, hdrs.last_updated_by, hdrs.last_update_date, ' ||
304 'hdrs.internal_request_id, hdrs.request_id, hdrs.created_by, hdrs.creation_date, ' ||
305 ':B_sequence, null ';
306
307 l_err_loc := 160;
308 l_ctxfspsql_prefixStr :=
309 'INSERT INTO icx_cat_items_ctx_dtls_tlp ' ||
310 '(inventory_item_id, po_line_id, req_template_name, ' ||
311 'req_template_line_num, org_id, language, ' ||
312 'last_update_login, last_updated_by, last_update_date, ' ||
313 'internal_request_id, request_id, created_by, creation_date, ' ||
314 'sequence, ctx_desc) ' ||
315 'SELECT /*+ LEADING(doc) */ doc.inventory_item_id, doc.po_line_id, doc.req_template_name, ' ||
316 'doc.req_template_line_num, doc.org_id, doc.language, ' ||
317 'doc.last_update_login, doc.last_updated_by, doc.last_update_date, ' ||
318 'doc.internal_request_id, doc.request_id, doc.created_by, doc.creation_date, ' ||
319 ':B_sequence, null ';
320
321 IF (p_where_clause = 'ROWID') THEN
322 l_err_loc := 200;
323 l_ctxsql_suffixStr := ' hdrs.rowid = :B_ROWID ';
324 ELSE
325 l_err_loc := 300;
326 l_ctxsql_suffixStr := ' hdrs.inventory_item_id = :B_INVENTORY_ITEM_ID ' ||
327 ' AND hdrs.po_line_id = :B_PO_LINE_ID ' ||
328 ' AND hdrs.req_template_name = :B_REQ_TEMPLATE_NAME ' ||
329 ' AND hdrs.req_template_line_num = :B_REQ_TEMPLATE_LINE_NUM ' ||
330 ' AND hdrs.org_id = :B_ORG_ID ' ||
331 ' AND hdrs.language = :B_LANGUAGE ';
332 END IF;
333
334 l_err_loc := 400;
335 IF (p_category_id = 0) THEN
336
337 l_err_loc := 500;
338 l_ctxsql_mandatoryBaseRowStr :=
339 ' || ''<language>'' || hdrs.language || ''</language><source_type>'' || hdrs.source_type ||
340 ''</source_type><supid>'' || hdrs.supplier_id || ''</supid><siteid>'' || hdrs.supplier_site_id ||
341 ''</siteid><ipcatid>'' || hdrs.ip_category_id || ''</ipcatid><pocatid>'' || hdrs.po_category_id ||
342 ''</pocatid><item_type>'' || hdrs.item_type || ''</item_type>''';
343
344 -- Check the special attributes in special metadata table, to form the special sqls.
345 -- First check if supplier_part_auxid is searchable
346
347 l_err_loc := 600;
348 l_ctxsql_string := l_ctxsql_prefixStr || l_ctxsql_mandatoryBaseRowStr;
349 l_searchable := NULL;
350 l_metadata_rec := NULL;
351 l_err_loc := 620;
352 getAttributeDetails(p_special_metadata_tbl, 'SUPPLIER_PART_AUXID', l_searchable, l_metadata_rec);
353 IF (l_searchable = 'Y') THEN
354 l_err_loc := 640;
355 l_ctxsql_string := l_ctxsql_string ||
356 ' || ''<' || to_char(l_metadata_rec.section_tag) || '>'' ||
357 ' || 'replace(replace(SUPPLIER_PART_AUXID,' || '''<''' || ',' || ''' ''' || ')' || ',' || '''>''' || ',' || ''' ''' || ')' ||
358 ' || ''</' || to_char(l_metadata_rec.section_tag) || '>''';
359 END IF;
360
361 l_err_loc := 700;
362 l_searchable := NULL;
363 l_metadata_rec := NULL;
364 getAttributeDetails(p_special_metadata_tbl, 'SUPPLIER_PART_NUM', l_searchable, l_metadata_rec);
365 IF (l_searchable = 'Y') THEN
366 l_err_loc := 800;
367 l_ctxsql_string := l_ctxsql_string ||
368 ' || ''<' || to_char(l_metadata_rec.section_tag) || '>'' ||
369 ' || 'replace(replace(SUPPLIER_PART_NUM,' || '''<''' || ',' || ''' ''' || ')' || ',' || '''>''' || ',' || ''' ''' || ')' ||
370 ' || ''</' || to_char(l_metadata_rec.section_tag) || '>''';
371 END IF;
372
373 l_err_loc := 850;
374 l_ctxsql_string := l_ctxsql_string ||
375 ' FROM icx_cat_items_ctx_hdrs_tlp hdrs WHERE ' || l_ctxsql_suffixStr;
376
377 l_err_loc := 900;
378 l_ctx_sql_rec.ctx_sql_string := l_ctxsql_string;
379 l_ctx_sql_rec.bind_sequence := g_seqMandatoryBaseRow;
380 l_all_ctx_sql_index := l_all_ctx_sql_index + 1;
381 p_all_ctx_sql_tbl(l_all_ctx_sql_index) := l_ctx_sql_rec;
382 l_special_ctx_sql_index := l_special_ctx_sql_index + 1;
383 p_special_ctx_sql_tbl(l_special_ctx_sql_index) := l_ctx_sql_rec;
384
385 l_err_loc := 1000;
386 IF (p_doc_source <> ICX_CAT_UTIL_PVT.g_MasterItemCsr_const) THEN
387 l_err_loc := 1100;
388 l_searchable := NULL;
389 l_metadata_rec := NULL;
390 getAttributeDetails(p_special_metadata_tbl, 'SUPPLIER', l_searchable, l_metadata_rec);
391 IF (l_searchable = 'Y') THEN
392 l_err_loc := 1200;
393 l_ctxsql_string := l_ctxsql_prefixStr;
394 l_ctxsql_string := l_ctxsql_string ||
395 ' || ''<' || to_char(l_metadata_rec.section_tag) || '>'' ||
396 ' || 'replace(replace(aps.vendor_name,' || '''<''' || ',' || ''' ''' || ')' || ',' || '''>''' || ',' || ''' ''' || ')' ||
397 ' || ''</' || to_char(l_metadata_rec.section_tag) || '>''';
398 l_ctxsql_string := l_ctxsql_string ||
399 ' FROM icx_cat_items_ctx_hdrs_tlp hdrs, AP_SUPPLIERS aps ' ||
400 ' WHERE hdrs.supplier_id = aps.vendor_id (+) AND ' || l_ctxsql_suffixStr;
401
402 l_err_loc := 1300;
403 l_ctx_sql_rec.ctx_sql_string := l_ctxsql_string;
404 l_ctx_sql_rec.bind_sequence := g_seqForSupplierRow;
405 l_all_ctx_sql_index := l_all_ctx_sql_index + 1;
406 p_all_ctx_sql_tbl(l_all_ctx_sql_index) := l_ctx_sql_rec;
407 l_special_ctx_sql_index := l_special_ctx_sql_index + 1;
408 p_special_ctx_sql_tbl(l_special_ctx_sql_index) := l_ctx_sql_rec;
409 END IF;
410 END IF;
411
412 l_err_loc := 1400;
413 l_searchable := NULL;
414 l_metadata_rec := NULL;
415 getAttributeDetails(p_special_metadata_tbl, 'INTERNAL_ITEM_NUM', l_searchable, l_metadata_rec);
416 IF (l_searchable = 'Y') THEN
417 l_err_loc := 1500;
418 IF ( ICX_CAT_UTIL_PVT.g_mi_concat_seg_clause IS NULL) THEN
419 ICX_CAT_UTIL_PVT.getMIConcatSegmentClause;
420 END IF;
421 l_ctxsql_string := l_ctxfspsql_prefixStr;
422 l_ctxsql_string := l_ctxsql_string ||
423 ' || ''<' || to_char(l_metadata_rec.section_tag) || '>'' ||
424 ' || 'replace(replace(' || ICX_CAT_UTIL_PVT.g_mi_concat_seg_clause || ',' || '''<''' || ',' || ''' ''' || ')' || ',' || '''>''' || ',' || ''' ''' || ')' ||
425 ' || ''</' || to_char(l_metadata_rec.section_tag) || '>''';
426 l_ctxsql_string := l_ctxsql_string ||
427 ' FROM (SELECT hdrs.inventory_item_id, hdrs.po_line_id, hdrs.req_template_name, ' ||
428 ' hdrs.req_template_line_num, hdrs.org_id, hdrs.language, ' ||
429 ' hdrs.last_update_login, hdrs.last_updated_by, hdrs.last_update_date, ' ||
430 ' hdrs.internal_request_id, hdrs.request_id, hdrs.created_by, ' ||
431 ' hdrs.creation_date, fsp.inventory_organization_id ' ||
432 ' FROM icx_cat_items_ctx_hdrs_tlp hdrs, financials_system_params_all fsp ' ||
433 ' WHERE hdrs.org_id = fsp.org_id (+) ' ||
434 ' AND ' || l_ctxsql_suffixStr ||
435 ' ) doc, mtl_system_items_b mi ' ||
436 ' WHERE doc.inventory_item_id = mi.inventory_item_id (+) ' ||
437 ' AND doc.inventory_organization_id = mi.organization_id (+) ';
438
439 l_err_loc := 1600;
440 l_ctx_sql_rec.ctx_sql_string := l_ctxsql_string;
441 l_ctx_sql_rec.bind_sequence := g_seqForInternalItemNumRow;
442 l_all_ctx_sql_index := l_all_ctx_sql_index + 1;
443 p_all_ctx_sql_tbl(l_all_ctx_sql_index) := l_ctx_sql_rec;
444 l_special_ctx_sql_index := l_special_ctx_sql_index + 1;
445 p_special_ctx_sql_tbl(l_special_ctx_sql_index) := l_ctx_sql_rec;
446 END IF;
447
448 l_err_loc := 1700;
449 IF (p_doc_source <> ICX_CAT_UTIL_PVT.g_MasterItemCsr_const) THEN
450 l_err_loc := 1800;
451 l_searchable := NULL;
452 l_metadata_rec := NULL;
453 getAttributeDetails(p_special_metadata_tbl, 'SOURCE', l_searchable, l_metadata_rec);
454 IF (l_searchable = 'Y') THEN
455 l_err_loc := 1900;
456 l_ctxsql_string := l_ctxsql_prefixStr;
457 IF (p_doc_source = ICX_CAT_UTIL_PVT.g_PODoc_const) THEN
458 l_err_loc := 2000;
459 l_ctxsql_string := l_ctxsql_string ||
460 ' || ''<' || to_char(l_metadata_rec.section_tag) || '>'' ||
461 ' || 'replace(replace(ph.clm_document_number, ''<'', '' ''), ''>'', '' '')' ||
462 ' || ''</' || to_char(l_metadata_rec.section_tag) || '>''';
463 l_ctxsql_string := l_ctxsql_string ||
464 ' FROM icx_cat_items_ctx_hdrs_tlp hdrs, po_headers_all ph, po_lines_all pl ' ||
465 ' WHERE hdrs.po_line_id = pl.po_line_id ' ||
466 ' AND hdrs.org_id = pl.org_id ' ||
467 ' AND pl.po_header_id = ph.po_header_id ' ||
468 ' AND ' || l_ctxsql_suffixStr;
469 ELSIF (p_doc_source = ICX_CAT_UTIL_PVT.g_ReqTemplateCsr_const) THEN
470 l_err_loc := 2100;
471 l_ctxsql_string := l_ctxsql_string ||
472 ' || ''<' || to_char(l_metadata_rec.section_tag) || '>'' ||
473 ' || 'replace(replace(hdrs.req_template_name, ''<'', '' ''), ''>'', '' '')' ||
474 ' || ''</' || to_char(l_metadata_rec.section_tag) || '>''';
475 l_ctxsql_string := l_ctxsql_string ||
476 ' FROM icx_cat_items_ctx_hdrs_tlp hdrs ' ||
477 ' WHERE ' || l_ctxsql_suffixStr;
478 ELSE
479 l_err_loc := 2200;
480 -- do nothing
481 END IF;
482 l_err_loc := 2300;
483 l_ctx_sql_rec.ctx_sql_string := l_ctxsql_string;
484 l_ctx_sql_rec.bind_sequence := g_seqForSourceRow;
485 l_all_ctx_sql_index := l_all_ctx_sql_index + 1;
486 p_all_ctx_sql_tbl(l_all_ctx_sql_index) := l_ctx_sql_rec;
487 l_special_ctx_sql_index := l_special_ctx_sql_index + 1;
488 p_special_ctx_sql_tbl(l_special_ctx_sql_index) := l_ctx_sql_rec;
489 END IF;
490 END IF;
491
492 l_err_loc := 2400;
493 IF (p_doc_source IN (ICX_CAT_UTIL_PVT.g_PODoc_const,
494 ICX_CAT_UTIL_PVT.g_ReqTemplateCsr_const))
495 THEN
496 l_err_loc := 2500;
497 l_searchable := NULL;
498 l_metadata_rec := NULL;
499 getAttributeDetails(p_special_metadata_tbl, 'ITEM_REVISION', l_searchable, l_metadata_rec);
500 IF (l_searchable = 'Y') THEN
501 l_err_loc := 2600;
502 l_ctxsql_string := l_ctxsql_prefixStr;
503 l_ctxsql_string := l_ctxsql_string ||
504 ' || ''<' || to_char(l_metadata_rec.section_tag) || '>'' ||
505 ' || 'replace(replace(pl.item_revision,' || '''<''' || ',' || ''' ''' || ')' || ',' || '''>''' || ',' || ''' ''' || ')' ||
506 ' || ''</' || to_char(l_metadata_rec.section_tag) || '>''';
507 IF (p_doc_source = ICX_CAT_UTIL_PVT.g_PODoc_const) THEN
508 l_err_loc := 2700;
509 l_ctxsql_string := l_ctxsql_string ||
510 ' FROM icx_cat_items_ctx_hdrs_tlp hdrs, po_headers_all ph, po_lines_all pl ' ||
511 ' WHERE hdrs.po_line_id = pl.po_line_id ' ||
512 ' AND pl.po_header_id = ph.po_header_id ';
513 ELSE
514 l_err_loc := 2800;
515 l_ctxsql_string := l_ctxsql_string ||
516 ' FROM icx_cat_items_ctx_hdrs_tlp hdrs, po_reqexpress_lines_all pl ' ||
517 ' WHERE hdrs.req_template_name = pl.express_name ' ||
518 ' AND hdrs.req_template_line_num = pl.sequence_num ' ||
519 ' AND hdrs.org_id = pl.org_id ';
520 END IF;
521 l_ctxsql_string := l_ctxsql_string ||
522 ' AND ' || l_ctxsql_suffixStr ;
523
524 l_err_loc := 2900;
525 l_ctx_sql_rec.ctx_sql_string := l_ctxsql_string;
526 l_ctx_sql_rec.bind_sequence := g_seqForItemRevisionRow;
527 l_all_ctx_sql_index := l_all_ctx_sql_index + 1;
528 p_all_ctx_sql_tbl(l_all_ctx_sql_index) := l_ctx_sql_rec;
529 l_special_ctx_sql_index := l_special_ctx_sql_index + 1;
530 p_special_ctx_sql_tbl(l_special_ctx_sql_index) := l_ctx_sql_rec;
531 END IF;
532 END IF;
533
534 l_err_loc := 3000;
535 l_searchable := NULL;
536 l_metadata_rec := NULL;
537 getAttributeDetails(p_special_metadata_tbl, 'SHOPPING_CATEGORY', l_searchable, l_metadata_rec);
538 IF (l_searchable = 'Y') THEN
539 l_err_loc := 3100;
540 l_ctxsql_string := l_ctxsql_prefixStr;
541 l_ctxsql_string := l_ctxsql_string ||
542 ' || ''<' || to_char(l_metadata_rec.section_tag) || '>'' ||
543 ' || 'replace(replace(ip_category_name,' || '''<''' || ',' || ''' ''' || ')' || ',' || '''>''' || ',' || ''' ''' || ')' ||
544 ' || ''</' || to_char(l_metadata_rec.section_tag) || '>''';
545 l_ctxsql_string := l_ctxsql_string ||
546 ' FROM icx_cat_items_ctx_hdrs_tlp hdrs WHERE ' || l_ctxsql_suffixStr;
547
548 l_err_loc := 3200;
549 l_ctx_sql_rec.ctx_sql_string := l_ctxsql_string;
550 l_ctx_sql_rec.bind_sequence := g_seqForShoppingCategoryRow;
551 l_all_ctx_sql_index := l_all_ctx_sql_index + 1;
552 p_all_ctx_sql_tbl(l_all_ctx_sql_index) := l_ctx_sql_rec;
553 l_special_ctx_sql_index := l_special_ctx_sql_index + 1;
554 p_special_ctx_sql_tbl(l_special_ctx_sql_index) := l_ctx_sql_rec;
555 END IF;
556 END IF; -- IF (p_category_id = 0) THEN
557
558 l_err_loc := 3300;
559 IF (p_category_id = 0) THEN
560 l_err_loc := 3400;
561 l_ctx_sql_next_sequence := g_seqStartReqularBaseRow;
562 ELSE
563 l_err_loc := 3500;
564 l_ctx_sql_next_sequence := g_seqStartRegularCatgRow;
565 END IF;
566
567 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
568 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
569 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
570 'l_ctx_sql_next_sequence:' ||l_ctx_sql_next_sequence);
571 END IF;
572
573 l_err_loc := 3600;
574 -- For master items we only have to create the ctx sql for description. The rest of the base and
575 -- local descriptors are not available for master item. So we differentiate between master items
576 -- and the rest of the sources. First, take care of all other sources in the IF statement and take
577 -- care of the master items source in the ELSE statement.
578 -- For category attributes, the ctx sqls are only created for all sources except Master Items.
579 -- While online populate of master items, we donot call the buildCtxSQl for category attributes
580 -- For all other sources, to build ctx sqls for category attributes, the p_doc_source is passed as null.
581 /*BUG 6599217: commented the if clause to by pass the check on master item cursor constant
582 IF (p_doc_source IS NULL OR p_doc_source <> ICX_CAT_UTIL_PVT.g_MasterItemCsr_const) THEN*/
583
584 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
585 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
586 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
587 'For mi also :not done earlier' );
588 END IF;
589
590 -- Loop thru the regular non-tl metadata table and form the sqls
591 -- p_regular_nontl_metadata_tbl
592 l_err_loc := 3700;
593 l_ctxsql_string := l_ctxsql_prefixStr;
594 l_current_length := 0;
595 FOR i in 1..p_regular_nontl_metadata_tbl.COUNT LOOP
596 l_err_loc := 3800;
597 l_metadata_rec := p_regular_nontl_metadata_tbl(i);
598 IF (l_current_length + l_metadata_rec.attribute_length >= g_default_max_length OR
599 i = p_regular_nontl_metadata_tbl.COUNT) THEN
600 l_err_loc := 3900;
601 l_ctxsql_string := l_ctxsql_string ||
602 ' || decode(po_attr.'|| l_metadata_rec.stored_in_column || ', NULL, NULL, ' ||
603 ' ''<' || to_char(l_metadata_rec.section_tag) || '>'' ||
604 ' || 'replace(replace(po_attr.'|| l_metadata_rec.stored_in_column || ',' || '''<''' || ',' || ''' ''' || ')' || ',' || '''>''' || ',' || ''' ''' || ')' ||
605 ' || ''</' || to_char(l_metadata_rec.section_tag) || '>'')';
606
607 l_err_loc := 4000;
608 l_ctxsql_string := l_ctxsql_string ||
609 ' FROM icx_cat_items_ctx_hdrs_tlp hdrs, po_attribute_values po_attr ' ||
610 ' WHERE hdrs.po_line_id = po_attr.po_line_id ' ||
611 ' AND hdrs.req_template_name = po_attr.req_template_name ' ||
612 ' AND hdrs.req_template_line_num = po_attr.req_template_line_num ' ||
613 ' AND hdrs.inventory_item_id = po_attr.inventory_item_id ' ||
614 ' AND hdrs.org_id = po_attr.org_id AND ' || l_ctxsql_suffixStr;
615
616 l_err_loc := 4100;
617 l_ctx_sql_next_sequence := l_ctx_sql_next_sequence + 1;
618 l_ctx_sql_rec.ctx_sql_string := l_ctxsql_string;
619 l_ctx_sql_rec.bind_sequence := l_ctx_sql_next_sequence;
620 l_all_ctx_sql_index := l_all_ctx_sql_index + 1;
621 p_all_ctx_sql_tbl(l_all_ctx_sql_index) := l_ctx_sql_rec;
622 l_regular_ctx_sql_index := l_regular_ctx_sql_index + 1;
623 p_regular_ctx_sql_tbl(l_regular_ctx_sql_index) := l_ctx_sql_rec;
624 l_ctxsql_string := l_ctxsql_prefixStr;
625 l_current_length := 0;
626 ELSE
627 l_err_loc := 4200;
628 l_ctxsql_string := l_ctxsql_string ||
629 ' || decode(po_attr.'|| l_metadata_rec.stored_in_column || ', NULL, NULL, ' ||
630 ' ''<' || to_char(l_metadata_rec.section_tag) || '>'' ||
631 ' || 'replace(replace(po_attr.'|| l_metadata_rec.stored_in_column || ',' || '''<''' || ',' || ''' ''' || ')' || ',' || '''>''' || ',' || ''' ''' || ')' ||
632 ' || ''</' || to_char(l_metadata_rec.section_tag) || '>'')';
633 l_current_length := l_current_length + l_metadata_rec.attribute_length;
634 END IF;
635 END LOOP; -- p_regular_nontl_metadata_tbl.COUNT
636
637 -- Loop thru the regular tl metadata table and form the sqls
638 -- p_regular_tl_metadata_tbl
639 l_err_loc := 4300;
640 l_ctxsql_string := l_ctxsql_prefixStr;
641 l_current_length := 0;
642 FOR i in 1..p_regular_tl_metadata_tbl.COUNT LOOP
643 l_err_loc := 4400;
644 l_metadata_rec := p_regular_tl_metadata_tbl(i);
645 IF (l_current_length + l_metadata_rec.attribute_length >= g_default_max_length OR
646 i = p_regular_tl_metadata_tbl.COUNT) THEN
647 l_err_loc := 4500;
648 l_ctxsql_string := l_ctxsql_string ||
649 ' || decode(po_attr.'|| l_metadata_rec.stored_in_column || ', NULL, NULL, ' ||
650 ' ''<' || to_char(l_metadata_rec.section_tag) || '>'' ||
651 ' || 'replace(replace(po_attr.'|| l_metadata_rec.stored_in_column || ',' || '''<''' || ',' || ''' ''' || ')' || ',' || '''>''' || ',' || ''' ''' || ')' ||
652 ' || ''</' || to_char(l_metadata_rec.section_tag) || '>'')';
653
654 l_err_loc := 4600;
655 l_ctxsql_string := l_ctxsql_string ||
656 ' FROM icx_cat_items_ctx_hdrs_tlp hdrs, po_attribute_values_tlp po_attr ' ||
657 ' WHERE hdrs.po_line_id = po_attr.po_line_id ' ||
658 ' AND hdrs.req_template_name = po_attr.req_template_name ' ||
659 ' AND hdrs.req_template_line_num = po_attr.req_template_line_num ' ||
660 ' AND hdrs.inventory_item_id = po_attr.inventory_item_id ' ||
661 ' AND hdrs.org_id = po_attr.org_id ' ||
662 ' AND hdrs.language = po_attr.language AND ' || l_ctxsql_suffixStr ;
663
664 l_err_loc := 4700;
665 l_ctx_sql_next_sequence := l_ctx_sql_next_sequence + 1;
666 l_ctx_sql_rec.ctx_sql_string := l_ctxsql_string;
667 l_ctx_sql_rec.bind_sequence := l_ctx_sql_next_sequence;
668 l_all_ctx_sql_index := l_all_ctx_sql_index + 1;
669 p_all_ctx_sql_tbl(l_all_ctx_sql_index) := l_ctx_sql_rec;
670 l_regular_ctx_sql_index := l_regular_ctx_sql_index + 1;
671 p_regular_ctx_sql_tbl(l_regular_ctx_sql_index) := l_ctx_sql_rec;
672 l_ctxsql_string := l_ctxsql_prefixStr;
673 l_current_length := 0;
674 ELSE
675 l_err_loc := 4800;
676 l_ctxsql_string := l_ctxsql_string ||
677 ' || decode(po_attr.'|| l_metadata_rec.stored_in_column || ', NULL, NULL, ' ||
678 ' ''<' || to_char(l_metadata_rec.section_tag) || '>'' ||
679 ' || 'replace(replace(po_attr.'|| l_metadata_rec.stored_in_column || ',' || '''<''' || ',' || ''' ''' || ')' || ',' || '''>''' || ',' || ''' ''' || ')' ||
680 ' || ''</' || to_char(l_metadata_rec.section_tag) || '>'')';
681 l_current_length := l_current_length + l_metadata_rec.attribute_length;
682 END IF;
683 END LOOP; -- p_regular_tl_metadata_tbl.COUNT
684 /* ELSE Bug 6599217 : commented if clauseand hence the else block
685 l_err_loc := 4900;
686 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
687 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
688 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
689 'continue for MI case ' );
690 END IF;
691 -- i.e. (p_doc_source = ICX_CAT_UTIL_PVT.g_MasterItemCsr_const)
692 -- Only need to populate description if it is searchable.
693 -- Other attributes are not available for master items
694 IF (p_category_id = 0) THEN
695 l_err_loc := 5000;
696 l_searchable := NULL;
697 l_metadata_rec := NULL;
698 getAttributeDetails(p_regular_tl_metadata_tbl, 'DESCRIPTION', l_searchable, l_metadata_rec);
699 IF (l_searchable = 'Y') THEN
700 l_err_loc := 5100;
701 l_ctxsql_string := l_ctxfspsql_prefixStr;
702 l_ctxsql_string := l_ctxsql_string ||
703 ' || ''<' || to_char(l_metadata_rec.section_tag) || '>'' ||
704 ' || 'replace(replace(mitl.description,' || '''<''' || ',' || ''' ''' || ')' || ',' || '''>''' || ',' || ''' ''' || ')' ||
705 ' || ''</' || to_char(l_metadata_rec.section_tag) || '>''';
706 l_ctxsql_string := l_ctxsql_string ||
707 ' FROM (SELECT hdrs.inventory_item_id, hdrs.po_line_id, hdrs.req_template_name, ' ||
708 ' hdrs.req_template_line_num, hdrs.org_id, hdrs.language, ' ||
709 ' hdrs.last_update_login, hdrs.last_updated_by, hdrs.last_update_date, ' ||
710 ' hdrs.internal_request_id, hdrs.request_id, hdrs.created_by, ' ||
711 ' hdrs.creation_date, fsp.inventory_organization_id ' ||
712 ' FROM icx_cat_items_ctx_hdrs_tlp hdrs, financials_system_params_all fsp ' ||
713 ' WHERE hdrs.org_id = fsp.org_id (+) ' ||
714 ' AND ' || l_ctxsql_suffixStr ||
715 ' ) doc, mtl_system_items_tl mitl ' ||
716 ' WHERE doc.inventory_item_id = mitl.inventory_item_id (+) ' ||
717 ' AND doc.language = mitl.language (+) ' ||
718 ' AND doc.inventory_organization_id = mitl.organization_id (+) ';
719
720 l_err_loc := 5200;
721 l_ctx_sql_next_sequence := l_ctx_sql_next_sequence + 1;
722 l_ctx_sql_rec.ctx_sql_string := l_ctxsql_string;
723 l_ctx_sql_rec.bind_sequence := l_ctx_sql_next_sequence;
724 l_all_ctx_sql_index := l_all_ctx_sql_index + 1;
725 p_all_ctx_sql_tbl(l_all_ctx_sql_index) := l_ctx_sql_rec;
726 l_regular_ctx_sql_index := l_regular_ctx_sql_index + 1;
727 p_regular_ctx_sql_tbl(l_regular_ctx_sql_index) := l_ctx_sql_rec;
728 l_ctxsql_string := l_ctxsql_prefixStr;
729 l_current_length := 0;
730 END IF;
731 END IF;
732 --bug 6599217 : if clause commented END IF; -- IF (p_doc_source <> ICX_CAT_UTIL_PVT.g_MasterItemCsr_const)*/
733 l_err_loc := 5300;
734
735 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
736 FOR i IN 1..p_special_ctx_sql_tbl.COUNT LOOP
737 l_ctx_sql_rec := p_special_ctx_sql_tbl(i);
738 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
739 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
740 'Special ctx SQLs..Value at i:' || i ||
741 ', sequence:' || l_ctx_sql_rec.bind_sequence ||
742 ', sql_string:' || substr(l_ctx_sql_rec.ctx_sql_string, 1, 3600) );
743 END LOOP;
744
745 FOR i IN 1..p_regular_ctx_sql_tbl.COUNT LOOP
746 l_ctx_sql_rec := p_regular_ctx_sql_tbl(i);
747 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
748 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
749 'Regular ctx SQLs..Value at i:' || i ||
750 ', sequence:' || l_ctx_sql_rec.bind_sequence ||
751 ', sql_string:' || substr(l_ctx_sql_rec.ctx_sql_string, 1, 3600) );
752 END LOOP;
753 END IF;
754
755 EXCEPTION
756 WHEN OTHERS THEN
757 ICX_CAT_UTIL_PVT.logUnexpectedException(
758 G_PKG_NAME, l_api_name,
759 ' --> l_err_loc:' ||l_err_loc ||' '|| SQLERRM);
760 RAISE;
761 END;
762
763 END ICX_CAT_BUILD_CTX_SQL_PVT;