1 PACKAGE BODY ENI_DENORM_HRCHY AS
2 /* $Header: ENIDENHB.pls 120.5 2011/06/27 22:31:19 sreharih ship $ */
3
4 g_func_area_id NUMBER := 11; -- Variable To Hold Functional Area For Product Functional Area
5 g_catset_id NUMBER := ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID; -- Variable To Hold Product Catalog Category Set
6 g_tab_schema VARCHAR2(20) := 'ENI';
7
8 -- This Public Function will return the Default Category Set Associated with
9 -- Product Reporting Functional Area
10 FUNCTION GET_CATEGORY_SET_ID RETURN NUMBER IS
11 l_catset_id NUMBER;
12 BEGIN
13 SELECT CATEGORY_SET_ID INTO l_catset_id
14 FROM MTL_DEFAULT_CATEGORY_SETS
15 WHERE FUNCTIONAL_AREA_ID = g_func_area_id;
16
17 RETURN l_catset_id;
18 EXCEPTION
19 WHEN NO_DATA_FOUND THEN
20 RETURN NULL;
21 WHEN OTHERS THEN
22 RAISE;
23 END GET_CATEGORY_SET_ID;
24
25 -- This is a Private function, which will return 'Y', if DBI is installed, else 'N'
26 FUNCTION IS_DBI_INSTALLED RETURN VARCHAR2 IS
27 l_count NUMBER;
28 BEGIN
29 SELECT 1 INTO l_count
30 FROM ALL_OBJECTS
31 WHERE OBJECT_NAME = 'ENI_OLTP_ITEM_STAR'
32 AND OBJECT_TYPE = 'TABLE'
33 AND OWNER = g_tab_schema;
34
35 RETURN 'Y';
36 EXCEPTION WHEN NO_DATA_FOUND THEN
37 RETURN 'N';
38 END IS_DBI_INSTALLED;
39
40 -- ER: 3154516
41 -- This Public Function will return the last updated date for Product Catalog from de-norm table
42 FUNCTION GET_LAST_CATALOG_UPDATE_DATE RETURN DATE IS
43 l_date DATE;
44 BEGIN
45 SELECT MAX(LAST_UPDATE_DATE) INTO l_date
46 FROM ENI_DENORM_HIERARCHIES
47 WHERE OBJECT_ID = g_catset_id
48 AND OBJECT_TYPE = 'CATEGORY_SET';
49
50 RETURN l_date;
51 END GET_LAST_CATALOG_UPDATE_DATE;
52
53 -- This Public Procedure is used to insert records in the Staging Table
54 -- The staging table will be used in the Incremental Load of Denorm Table.
55 -- All the deleted/modified/new records in the Product Catalog Hierarchy has to be
56 -- there in the Staging table, which has to be done by calling this procedure.
57 PROCEDURE INSERT_INTO_STAGING(
58 p_object_type IN VARCHAR2,
59 p_object_id IN NUMBER,
60 p_child_id IN NUMBER,
61 p_parent_id IN NUMBER,
62 p_mode_flag IN VARCHAR2,
63 x_return_status OUT NOCOPY VARCHAR2,
64 x_msg_count OUT NOCOPY NUMBER,
65 x_msg_data OUT NOCOPY VARCHAR2,
66 p_language_code IN VARCHAR2 DEFAULT NULL) IS
67
68 l_language_code VARCHAR2(4);
69 l_count NUMBER;
70 BEGIN
71 -- validating parameters bug 3134719
72 IF p_mode_flag NOT IN ('A', 'M', 'D', 'C', 'S', 'E') THEN
73 x_return_status := 'E';
74 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
75 FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Mode Flag');
76 END IF;
77 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
78 RETURN;
79 END IF;
80
81 IF p_object_type <> 'CATEGORY_SET' THEN
82 x_return_status := 'E';
83 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
84 FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Object Type. Must be CATEGORY_SET');
85 END IF;
86 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
87 RETURN;
88 END IF;
89
90 IF p_object_id IS NULL THEN
91 x_return_status := 'E';
92 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
93 FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Object ID can not be NULL.');
94 END IF;
95 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
96 RETURN;
97 END IF;
98
99 IF p_child_id IS NULL THEN
100 x_return_status := 'E';
101 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
102 FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Child Category ID can not be NULL.');
103 END IF;
104 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
105 RETURN;
106 END IF;
107
108 -- for sales and marketing enhancement
109 -- if changes in description, skip flag, effective date then inserting separate record
110 IF p_mode_flag IN ('A', 'M', 'D') THEN
111 UPDATE ENI_DENORM_HRCHY_STG
112 SET PARENT_ID = p_parent_id,
113 MODE_FLAG = DECODE(p_mode_flag, 'A', DECODE(MODE_FLAG, 'D', 'M', 'A'), p_mode_flag)
114 WHERE OBJECT_TYPE = p_object_type
115 AND OBJECT_ID = p_object_id
116 AND CHILD_ID = p_child_id
117 AND BATCH_FLAG = 'NEXT_BATCH';
118
119 IF SQL%NOTFOUND THEN
120 INSERT INTO ENI_DENORM_HRCHY_STG (
121 OBJECT_TYPE,
122 OBJECT_ID,
123 CHILD_ID,
124 PARENT_ID,
125 MODE_FLAG,
126 BATCH_FLAG)
127 VALUES (
128 p_object_type,
129 p_object_id,
130 p_child_id,
131 p_parent_id,
132 p_mode_flag,
133 'NEXT_BATCH');
134 END IF;
135 ELSIF p_mode_flag IN ('S', 'U', 'E') THEN
136 -- S - EXCLUDE_USER_VIEW column set to 'Y'
137 -- U - EXCLUDE_USER_VIEW column set to 'N'
138 -- E - Change in DISABLE_DATE column of a category
139 -- mode flag value 'S' will override the values 'C', 'E'
143 MODE_FLAG = DECODE(MODE_FLAG, 'S', DECODE(p_mode_flag, 'E', 'S', 'S', 'U', p_mode_flag),
140 -- if mode flag 'S' is sent twice, we will toggle mode flag to 'S', 'U'
141 UPDATE ENI_DENORM_HRCHY_STG
142 SET PARENT_ID = p_parent_id,
144 'U', DECODE(p_mode_flag, 'E', 'U', 'S', 'S', p_mode_flag), p_mode_flag)
145 WHERE OBJECT_TYPE = p_object_type
146 AND OBJECT_ID = p_object_id
147 AND CHILD_ID = p_child_id
148 AND MODE_FLAG IN ('S', 'U', 'E', 'C')
149 AND BATCH_FLAG = 'NEXT_BATCH';
150
151 IF SQL%NOTFOUND THEN
152 INSERT INTO ENI_DENORM_HRCHY_STG (
153 OBJECT_TYPE,
154 OBJECT_ID,
155 CHILD_ID,
156 PARENT_ID,
157 MODE_FLAG,
158 BATCH_FLAG)
159 VALUES (
160 p_object_type,
161 p_object_id,
162 p_child_id,
163 p_parent_id,
164 p_mode_flag,
165 'NEXT_BATCH');
166 END IF;
167 ELSIF p_mode_flag = 'C' THEN
168 -- C - change in Category Description
169 -- mode flag 'C' can only override itself. i.e. if mode flag is already 'S', 'E' then no changes in mode flag
170 -- if more than one record's source_lang is = language code then language code will be updated to NULL.
171 -- this is because actually more than one record is updated for description in this case
172 -- also if user updates the description of same category in more than one language
173 -- then the language code will be updated to NULL.
174 IF p_language_code IS NOT NULL THEN
175 BEGIN
176 SELECT 1 INTO l_count
177 FROM MTL_CATEGORIES_TL
178 WHERE CATEGORY_ID = p_child_id
179 AND SOURCE_LANG = p_language_code;
180
181 l_language_code := p_language_code;
182 EXCEPTION
183 WHEN TOO_MANY_ROWS THEN
184 l_language_code := NULL;
185 WHEN NO_DATA_FOUND THEN
186 x_return_status := 'E';
187 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
188 FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', 'Invalid Category ID, Language combination');
189 END IF;
190 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
191 RETURN;
192 END;
193 ELSE
194 -- Selecting userenv('LANG') into language code based on
195 -- the discussion with Wasi and advised by him.
196
197 select userenv('LANG') into l_language_code from dual;
198 END IF;
199
200 UPDATE ENI_DENORM_HRCHY_STG
201 SET PARENT_ID = p_parent_id,
202 MODE_FLAG = DECODE(MODE_FLAG, 'S', 'S', 'U', 'U', 'E', 'E', p_mode_flag),
203 LANGUAGE_CODE = DECODE(MODE_FLAG, 'C', DECODE(LANGUAGE_CODE, l_language_code, l_language_code, NULL), NULL)
204 WHERE OBJECT_TYPE = p_object_type
205 AND OBJECT_ID = p_object_id
206 AND CHILD_ID = p_child_id
207 AND MODE_FLAG IN ('S', 'U', 'E', 'C')
208 AND BATCH_FLAG = 'NEXT_BATCH';
209
210 IF SQL%NOTFOUND THEN
211 INSERT INTO ENI_DENORM_HRCHY_STG (
212 OBJECT_TYPE,
213 OBJECT_ID,
214 CHILD_ID,
215 PARENT_ID,
216 MODE_FLAG,
217 BATCH_FLAG,
218 LANGUAGE_CODE)
219 VALUES (
220 p_object_type,
221 p_object_id,
222 p_child_id,
223 p_parent_id,
224 p_mode_flag,
225 'NEXT_BATCH',
226 l_language_code);
227 END IF;
228 END IF;
229
230 x_return_status := 'S';
231 x_msg_count := 0;
232 x_msg_data := null;
233 EXCEPTION WHEN OTHERS THEN
234 x_return_status := 'U';
235 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
236 FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'INSERT_INTO_STAGING', SQLERRM);
237 END IF;
238 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => x_msg_count, P_DATA => x_msg_data);
239 END INSERT_INTO_STAGING;
240
241 -- for sales and marketing enhancement
242 -- This procedure loads the denorm parents table
243 -- this procedure is called from the main procedure if sales and marketing is installed.
244 PROCEDURE LOAD_DENORM_PARENTS_PROD_HRCHY(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS
245 CURSOR C1 IS
246 SELECT TL.LANGUAGE_CODE, C.CATEGORY_ID, B.DISABLE_DATE
247 FROM MTL_CATEGORY_SET_VALID_CATS C, MTL_CATEGORIES_B B, FND_LANGUAGES TL
248 WHERE C.CATEGORY_SET_ID = g_catset_id
249 AND TL.INSTALLED_FLAG IN ('I', 'B')
250 AND B.CATEGORY_ID = C.CATEGORY_ID
251 AND NOT EXISTS (SELECT NULL FROM EGO_PROD_CAT_SALES_MARKET_AGV A
252 WHERE A.CATEGORY_SET_ID = g_catset_id
253 AND A.CATEGORY_ID = C.CATEGORY_ID
254 AND NVL(A.EXCLUDE_USER_VIEW, 'N') = 'Y');
255
256 CURSOR C3(l_child_id NUMBER, l_language VARCHAR2) IS
257 SELECT TL.DESCRIPTION CHILD_DESC, C.CATEGORY_ID CHILD_ID
258 FROM MTL_CATEGORIES_TL TL,
259 (SELECT
260 CATEGORY_ID, LEVEL hrchy
261 FROM MTL_CATEGORY_SET_VALID_CATS
262 START WITH CATEGORY_ID = l_child_id AND CATEGORY_SET_ID = g_catset_id
263 CONNECT BY PRIOR PARENT_CATEGORY_ID = CATEGORY_ID AND CATEGORY_SET_ID = g_catset_id) C
264 WHERE C.CATEGORY_ID = TL.CATEGORY_ID
265 AND TL.LANGUAGE = l_language
266 AND NOT EXISTS (SELECT NULL FROM EGO_PROD_CAT_SALES_MARKET_AGV A
267 WHERE A.CATEGORY_SET_ID = g_catset_id
268 AND A.CATEGORY_ID = C.CATEGORY_ID
269 AND NVL(A.EXCLUDE_USER_VIEW, 'N') = 'Y')
270 ORDER BY hrchy ASC; -- Bug 4749088
271
272 l_concat_desc VARCHAR2(4001);
273 l_desc VARCHAR2(240);
274 l_attr_grp_id NUMBER;
275 l_eff_level NUMBER;
279
276 l_imm_par_id NUMBER;
277 l_length NUMBER;
278 l_count NUMBER;
280 l_user_id NUMBER := FND_GLOBAL.USER_ID;
281 l_conc_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
282 l_prog_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
283 l_conc_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
284
285 BEGIN
286 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Initial Load of Denorm Hierarchy Parents table begining');
287 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
288 -- getting attribute group id for sales and marketing
289 -- this id will be inserted into the denorm hrchy parents table
290 BEGIN
291 SELECT ATTR_GROUP_ID INTO l_attr_grp_id
292 FROM EGO_FND_DSC_FLX_CTX_EXT
293 WHERE APPLICATION_ID = 431
294 AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_PRODUCT_CATEGORY_SET'
295 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'SalesAndMarketing';
296 EXCEPTION
297 WHEN NO_DATA_FOUND THEN
298 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: Attribute Group not found for Sales and Marketing');
299 RAISE;
300 END;
301
302 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting Denorm Hierarchy Parents table');
303 DELETE ENI_DENORM_HRCHY_PARENTS;
304
305 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Populating Denorm Hierarchy Parents table');
306 -- for each language installed and each category in hierarchy
307 l_count := 0;
308 FOR i IN C1 LOOP
309 -- initializing all variables
310 l_concat_desc := NULL;
311 l_desc := NULL;
312 l_eff_level := 0;
313 l_imm_par_id := null;
314 l_length := 0;
315
316 -- for each node in the hierarchy upto the top_node
317 -- preparing the concatenated descriptions
318 FOR k IN C3(i.CATEGORY_ID, i.LANGUAGE_CODE) LOOP
319 -- to trim from the start of the string onwards to accommodate more descriptions towards the end of the string.
320 -- trim upto 4000 chars
321 l_length := LENGTH(k.CHILD_DESC || '/' || l_concat_desc);
322
323 IF l_length > 4001 THEN
324 l_length := -4001;
325 ELSE
326 l_length := 0;
327 END IF;
328
329 -- concatenating the descriptions
330 l_concat_desc := SUBSTR(k.CHILD_DESC || '/' || l_concat_desc, l_length, 4001);
331 -- incrementing the effective level
332 l_eff_level := l_eff_level + 1;
333
334 IF i.CATEGORY_ID = k.CHILD_ID THEN
335 l_desc := k.CHILD_DESC;
336 ELSIF l_imm_par_id IS NULL THEN
337 l_imm_par_id := k.CHILD_ID;
338 END IF;
339 END LOOP;
340
341 INSERT INTO ENI_DENORM_HRCHY_PARENTS (
342 OBJECT_TYPE,
343 OBJECT_ID,
344 ATTRIBUTE_GROUP_ID,
345 CATEGORY_ID,
346 LANGUAGE,
347 CATEGORY_DESC,
348 CONCAT_CAT_PARENTAGE,
349 CATEGORY_LEVEL_NUM,
350 DISABLE_DATE,
351 CATEGORY_PARENT_ID,
352 CREATED_BY,
353 CREATION_DATE,
354 LAST_UPDATED_BY,
355 LAST_UPDATE_DATE,
356 LAST_UPDATE_LOGIN,
357 REQUEST_ID,
358 PROGRAM_APPLICATION_ID,
359 PROGRAM_UPDATE_DATE,
360 PROGRAM_ID)
361 VALUES (
362 'CATEGORY_SET',
363 g_catset_id,
364 l_attr_grp_id,
365 i.CATEGORY_ID,
366 i.LANGUAGE_CODE,
367 l_desc,
368 RTRIM(l_concat_desc, '/'),
369 l_eff_level,
370 i.DISABLE_DATE,
371 l_imm_par_id,
372 l_user_id,
373 SYSDATE,
374 l_user_id,
375 SYSDATE,
376 l_user_id,
377 l_conc_request_id,
378 l_prog_appl_id,
379 SYSDATE,
380 l_conc_program_id);
381
382 l_count := l_count + 1;
383 END LOOP;
384
385 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' records inserted into Denorm Hierarchy Parents table.');
386
387 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gathering statistics on table: ENI_DENORM_HRCHY_PARENTS ');
388 FND_STATS.gather_table_stats (ownname=>'ENI', tabname=>'ENI_DENORM_HRCHY_PARENTS');
389
390 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Initial Load of Denorm Hierarchy Parents table Complete.');
391 EXCEPTION
392 WHEN NO_DATA_FOUND THEN
393 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: No Data Found. Transaction will be rolled back');
394 errbuf := 'No data found ' || sqlerrm;
395 retcode := 2;
396 ROLLBACK;
397 RAISE;
398 WHEN OTHERS THEN
399 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
400 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.LOAD_DENORM_PARENTS_PROD_HRCHY', 'Error: ' ||
401 sqlerrm || ' .Transaction will be rolled back');
402 end if;
403 errbuf := 'Error :' || sqlerrm;
404 retcode := 2;
405 ROLLBACK;
406 RAISE;
407 END LOAD_DENORM_PARENTS_PROD_HRCHY;
408
409 -- for sales and marketing enhancement
410 -- incremental load of denorm hierarchy parents table
411 -- this procedure is called from the incremental load of denorm table procedure only if
412 -- sales and marketing is installed
413 PROCEDURE SYNC_DENORM_PARENTS_PROD_HRCHY(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS
414 CURSOR C1 IS
415 SELECT TL.LANGUAGE_CODE, S.CHILD_ID, S.MODE_FLAG, S.LANGUAGE_CODE STG_LANG
416 FROM ENI_DENORM_HRCHY_STG S, FND_LANGUAGES TL
417 WHERE S.OBJECT_ID = g_catset_id
418 AND S.OBJECT_TYPE = 'CATEGORY_SET'
419 AND TL.INSTALLED_FLAG IN ('I', 'B')
420 AND S.MODE_FLAG <> 'D'
421 AND S.BATCH_FLAG <> 'NEXT_BATCH';
422
423 CURSOR C2(p_child NUMBER) IS
424 SELECT D.CATEGORY_ID, B.DISABLE_DATE FROM
425 (SELECT C.CATEGORY_ID
426 FROM MTL_CATEGORY_SET_VALID_CATS C
430 AND NVL(A.EXCLUDE_USER_VIEW, 'N') = 'Y')
427 WHERE NOT EXISTS (SELECT NULL FROM EGO_PROD_CAT_SALES_MARKET_AGV A
428 WHERE A.CATEGORY_SET_ID = g_catset_id
429 AND A.CATEGORY_ID = C.CATEGORY_ID
431 START WITH CATEGORY_ID = p_child AND CATEGORY_SET_ID = g_catset_id
432 CONNECT BY PARENT_CATEGORY_ID = PRIOR CATEGORY_ID AND CATEGORY_SET_ID = g_catset_id) D, MTL_CATEGORIES_B B
433 WHERE B.CATEGORY_ID = D.CATEGORY_ID;
434
435 CURSOR C3(l_child_id NUMBER, l_language VARCHAR2) IS
436 SELECT TL.DESCRIPTION CHILD_DESC, C.CATEGORY_ID CHILD_ID
437 FROM MTL_CATEGORIES_TL TL,
438 (SELECT
439 CATEGORY_ID, LEVEL hrchy
440 FROM MTL_CATEGORY_SET_VALID_CATS
441 START WITH CATEGORY_ID = l_child_id AND CATEGORY_SET_ID = g_catset_id
442 CONNECT BY PRIOR PARENT_CATEGORY_ID = CATEGORY_ID AND CATEGORY_SET_ID = g_catset_id) C
443 WHERE C.CATEGORY_ID = TL.CATEGORY_ID
444 AND TL.LANGUAGE = l_language
445 AND NOT EXISTS (SELECT NULL FROM EGO_PROD_CAT_SALES_MARKET_AGV A
446 WHERE A.CATEGORY_SET_ID = g_catset_id
447 AND A.CATEGORY_ID = C.CATEGORY_ID
448 AND NVL(A.EXCLUDE_USER_VIEW, 'N') = 'Y')
449 ORDER BY hrchy ASC; -- Bug 4749088
450
451 l_concat_desc VARCHAR2(4001);
452 l_desc VARCHAR2(240);
453 l_attr_grp_id NUMBER;
454 l_include_flag VARCHAR2(1);
455 l_eff_level NUMBER;
456 l_imm_par_id NUMBER;
457 l_length NUMBER;
458 l_count NUMBER := 0;
459
460 l_user_id NUMBER := FND_GLOBAL.USER_ID;
461 l_conc_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
462 l_prog_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
463 l_conc_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
464
465 BEGIN
466 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Incremental Load of Denorm Hierarchy Parents table begining.');
467
468 BEGIN
469 SELECT ATTR_GROUP_ID INTO l_attr_grp_id
470 FROM EGO_FND_DSC_FLX_CTX_EXT
471 WHERE APPLICATION_ID = 431
472 AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_PRODUCT_CATEGORY_SET'
473 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'SalesAndMarketing';
474
475 EXCEPTION
476 WHEN NO_DATA_FOUND THEN
477 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: Attribute Group not found for Sales and Marketing');
478 RAISE;
479 END;
480
481 /* Bug : 5233230
482 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting records of all disabled languages from Denorm Hierarchy Parents table');
483 -- Deleting all languages, which are being deactivated
484 DELETE FROM ENI_DENORM_HRCHY_PARENTS B
485 WHERE NOT EXISTS (SELECT NULL FROM FND_LANGUAGES L
486 WHERE L.INSTALLED_FLAG IN ('I', 'B')
487 AND B.LANGUAGE = L.LANGUAGE_CODE);
488
489 l_count := SQL%ROWCOUNT;
490 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' records deleted.');
491 Bug 5233230 */
492
493 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting all categories which are deleted from hierarchy OR excluded from user view.');
494 -- Deleting all deleted nodes
495 DELETE FROM ENI_DENORM_HRCHY_PARENTS B
496 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
497 AND B.OBJECT_ID = g_catset_id
498 AND EXISTS (SELECT NULL FROM ENI_DENORM_HRCHY_STG S
499 WHERE S.OBJECT_TYPE = 'CATEGORY_SET'
500 AND S.OBJECT_ID = g_catset_id
501 AND S.CHILD_ID = B.CATEGORY_ID
502 AND S.MODE_FLAG IN ('D', 'S')
503 AND S.BATCH_FLAG <> 'NEXT_BATCH');
504
505 l_count := SQL%ROWCOUNT;
506 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' records deleted.');
507
508 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting/Updating into Denorm Hierarchy Parents table');
509
510 l_count := 0;
511 FOR i IN C1 LOOP
512
513 -- description only modified in a particular language so do not update other language records
514 IF i.MODE_FLAG = 'C' AND i.LANGUAGE_CODE <> i.STG_LANG THEN
515 NULL;
516 ELSE
517 FOR j IN C2(i.CHILD_ID) LOOP
518 l_concat_desc := NULL;
519 l_desc := NULL;
520 l_eff_level := 0;
521 l_imm_par_id := null;
522 l_length := 0;
523
524 FOR k IN C3(j.CATEGORY_ID, i.LANGUAGE_CODE) LOOP
525 -- to trim from the start of the string onwards to accommodate more descriptions towards the end of the string.
526 -- trim upto 4000 chars
527 l_length := LENGTH(k.CHILD_DESC || '/' || l_concat_desc);
528
529 IF l_length > 4001 THEN
530 l_length := -4001;
531 ELSE
532 l_length := 0;
533 END IF;
534
535 -- concatenating the descriptions
536 l_concat_desc := SUBSTR(k.CHILD_DESC || '/' || l_concat_desc, l_length, 4001);
537 -- incrementing the effective level
538 l_eff_level := l_eff_level + 1;
539 IF j.CATEGORY_ID = k.CHILD_ID THEN
540 l_desc := k.CHILD_DESC;
541 ELSIF l_imm_par_id IS NULL THEN
542 l_imm_par_id := k.CHILD_ID;
543 END IF;
544 END LOOP;
545
546 UPDATE ENI_DENORM_HRCHY_PARENTS B
547 SET CATEGORY_DESC = l_desc,
548 CONCAT_CAT_PARENTAGE = RTRIM(l_concat_desc, '/'),
549 CATEGORY_LEVEL_NUM = l_eff_level,
550 DISABLE_DATE = j.DISABLE_DATE,
551 CATEGORY_PARENT_ID = l_imm_par_id,
552 LAST_UPDATED_BY = l_user_id,
553 LAST_UPDATE_DATE = SYSDATE,
554 LAST_UPDATE_LOGIN = l_user_id,
555 REQUEST_ID = l_conc_request_id,
559 WHERE OBJECT_TYPE = 'CATEGORY_SET'
556 PROGRAM_APPLICATION_ID = l_prog_appl_id,
557 PROGRAM_UPDATE_DATE = SYSDATE,
558 PROGRAM_ID = l_conc_program_id
560 AND OBJECT_ID = g_catset_id
561 AND CATEGORY_ID = j.CATEGORY_ID
562 AND LANGUAGE = i.LANGUAGE_CODE;
563
564 IF SQL%NOTFOUND THEN
565 INSERT INTO ENI_DENORM_HRCHY_PARENTS (
566 OBJECT_TYPE,
567 OBJECT_ID,
568 ATTRIBUTE_GROUP_ID,
569 CATEGORY_ID,
570 LANGUAGE,
571 CATEGORY_DESC,
572 CONCAT_CAT_PARENTAGE,
573 CATEGORY_LEVEL_NUM,
574 DISABLE_DATE,
575 CATEGORY_PARENT_ID,
576 CREATED_BY,
577 CREATION_DATE,
578 LAST_UPDATED_BY,
579 LAST_UPDATE_DATE,
580 LAST_UPDATE_LOGIN,
581 REQUEST_ID,
582 PROGRAM_APPLICATION_ID,
583 PROGRAM_UPDATE_DATE,
584 PROGRAM_ID)
585 VALUES (
586 'CATEGORY_SET',
587 g_catset_id,
588 l_attr_grp_id,
589 j.CATEGORY_ID,
590 i.LANGUAGE_CODE,
591 l_desc,
592 RTRIM(l_concat_desc, '/'),
593 l_eff_level,
594 j.DISABLE_DATE,
595 l_imm_par_id,
596 l_user_id,
597 SYSDATE,
598 l_user_id,
599 SYSDATE,
600 l_user_id,
601 l_conc_request_id,
602 l_prog_appl_id,
603 SYSDATE,
604 l_conc_program_id);
605 END IF;
606 l_count := l_count + 1;
607 END LOOP; -- end C2
608 END IF;
609 END LOOP; -- end C1
610
611 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count || ' records inserted/updated into Denorm Hierarchy Parents table');
612 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Incremental Load of Denorm Hierarchy Parents table Complete.');
613 EXCEPTION
614 WHEN OTHERS THEN
615 ROLLBACK;
616 UPDATE ENI_DENORM_HRCHY_STG
617 SET BATCH_FLAG = 'NEXT_BATCH'
618 WHERE BATCH_FLAG <> 'NEXT_BATCH'
619 AND OBJECT_TYPE = 'CATEGORY_SET'
620 AND OBJECT_ID = g_catset_id;
621 COMMIT;
622 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
623 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.SYNC_DENORM_PARENTS_PROD_HRCHY', 'Error: ' ||
624 sqlerrm || ' .Transaction will be rolled back');
625 end if;
626 errbuf := 'Error :' || sqlerrm;
627 retcode := 2;
628 RAISE;
629 END SYNC_DENORM_PARENTS_PROD_HRCHY;
630
631 -- This Procedure Denormalizes the Product Catalog Hierarchy into Denorm Table
632 -- This is an Initial Load Procedure, so the Denorm Table will be Truncated first.
633 -- This procedure will be called from LOAD_PRODUCT_HIERARCHY procedure
634 PROCEDURE LOAD_HIERARCHY(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS
635
636 CURSOR c1 IS
637 SELECT
638 T.PARENT_CATEGORY_ID PARENT_ID,
639 T.CATEGORY_ID CHILD_ID,
640 D.TOP_NODE_FLAG,
641 D1.LEAF_NODE_FLAG
642 FROM MTL_CATEGORY_SET_VALID_CATS T, ENI_DENORM_HIERARCHIES D, ENI_DENORM_HIERARCHIES D1
643 WHERE T.CATEGORY_SET_ID = g_catset_id
644 AND T.PARENT_CATEGORY_ID IS NOT NULL
645 AND D.OBJECT_TYPE = 'CATEGORY_SET'
646 AND D.OBJECT_ID = g_catset_id
647 AND D.PARENT_ID = T.PARENT_CATEGORY_ID
648 AND D.CHILD_ID = T.PARENT_CATEGORY_ID
649 AND D1.OBJECT_TYPE = 'CATEGORY_SET'
650 AND D1.OBJECT_ID = g_catset_id
651 AND D1.PARENT_ID = T.CATEGORY_ID
652 AND D1.CHILD_ID = T.CATEGORY_ID;
653
654
655 l_count NUMBER := 0;
656 l_dbi_installed VARCHAR2(1) := IS_DBI_INSTALLED; -- variable to hold installation flag for DBI
657 l_user_id NUMBER := FND_GLOBAL.USER_ID;
658 l_conc_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
659 l_prog_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
660 l_conc_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
661 l_hrchy_enabled VARCHAR2(1);
662 l_sql VARCHAR2(32000);
663 l_validate_flag VARCHAR2(1); -- Bug# 3306212
664 l_struct_id NUMBER; -- Bug# 3306212
665
666 BEGIN
667 FND_FILE.PUT_LINE(FND_FILE.LOG,'Denorm table Initial Load Start');
668
669 -- Finding whether Hierarchy is enabled or not
670 BEGIN -- Bug# 3013192, Bug# 3306212
671 SELECT HIERARCHY_ENABLED, VALIDATE_FLAG, STRUCTURE_ID INTO l_hrchy_enabled, l_validate_flag, l_struct_id
672 FROM MTL_CATEGORY_SETS_B
673 WHERE CATEGORY_SET_ID = g_catset_id;
674 EXCEPTION WHEN NO_DATA_FOUND THEN
675 FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Category Set associated with Product Reporting functional area');
676 RAISE;
677 END;
678
679 -- Deleting records from Denorm Table for object_type = CATEGORY_SET
680 FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleting Records from Denorm Table for Product Catalog');
681
682 DELETE FROM ENI_DENORM_HIERARCHIES
683 WHERE OBJECT_TYPE = 'CATEGORY_SET';
684
685 -- Bug# 3047381, moved delete of staging table from last to begining. So that any changes in hierarchy, during Load is running
686 -- will be captured in next incremental load.
687 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting records from Staging table');
688 -- deleting records from staging table, since all the changes are already there in Denorm table
689 DELETE FROM ENI_DENORM_HRCHY_STG
693 l_count := SQL%ROWCOUNT;
690 WHERE OBJECT_TYPE = 'CATEGORY_SET'
691 AND OBJECT_ID = g_catset_id;
692
694 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' records deleted from Staging table');
695
696 -- Inserting Self-referencing Nodes
697 IF (NVL(l_validate_flag, 'N') = 'N' AND NVL(l_hrchy_enabled, 'N') = 'N') THEN
698 -- Inserting Self-referencing Nodes from mtl_categories
699 -- since enforce list of valid categories is not true and
700 -- hierarchy is not enabled
701 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting Self Referencing Nodes');
702
703 INSERT INTO ENI_DENORM_HIERARCHIES(
704 PARENT_ID,
705 IMM_CHILD_ID,
706 CHILD_ID,
707 OBJECT_TYPE,
708 OBJECT_ID,
709 TOP_NODE_FLAG,
710 LEAF_NODE_FLAG,
711 ITEM_ASSGN_FLAG,
712 DBI_FLAG,
713 OLTP_FLAG,
714 CREATED_BY,
715 CREATION_DATE,
716 LAST_UPDATED_BY,
717 LAST_UPDATE_DATE,
718 LAST_UPDATE_LOGIN,
719 REQUEST_ID,
720 PROGRAM_APPLICATION_ID,
721 PROGRAM_UPDATE_DATE,
722 PROGRAM_ID)
723 SELECT
724 CATEGORY_ID,
725 CATEGORY_ID,
726 CATEGORY_ID,
727 'CATEGORY_SET',
728 g_catset_id,
729 'Y' TOP_NODE_FLAG,
730 'Y' LEAF_NODE_FLAG,
731 'N' ITEM_ASSGN_FLAG,
732 'Y' DBI_FLAG,
733 'Y' OLTP_FLAG,
734 l_user_id,
735 SYSDATE,
736 l_user_id,
737 SYSDATE,
738 l_user_id,
739 l_conc_request_id,
740 l_prog_appl_id,
741 SYSDATE,
742 l_conc_program_id
743 FROM MTL_CATEGORIES_B
744 WHERE STRUCTURE_ID = l_struct_id;
745 ELSE
746 -- Inserting Self-referencing Nodes
747 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting Self Referencing Nodes');
748 -- Using execute immediate to set ITEM_ASSGN_FLAG and LEAF_NODE_FLAG
749 -- the same insert statement doesn't works without execute immediate
750 l_sql :=
751 'INSERT INTO ENI_DENORM_HIERARCHIES(
752 PARENT_ID,
753 IMM_CHILD_ID,
754 CHILD_ID,
755 OBJECT_TYPE,
756 OBJECT_ID,
757 TOP_NODE_FLAG,
758 LEAF_NODE_FLAG,
759 ITEM_ASSGN_FLAG,
760 DBI_FLAG,
761 OLTP_FLAG,
762 CREATED_BY,
763 CREATION_DATE,
764 LAST_UPDATED_BY,
765 LAST_UPDATE_DATE,
766 LAST_UPDATE_LOGIN,
767 REQUEST_ID,
768 PROGRAM_APPLICATION_ID,
769 PROGRAM_UPDATE_DATE,
770 PROGRAM_ID)
771 SELECT
772 T.CATEGORY_ID,
773 T.CATEGORY_ID,
774 T.CATEGORY_ID,
775 ''CATEGORY_SET'',
776 :g_catset_id,
777 DECODE(:l_hrchy_enabled, ''Y'', DECODE(T.PARENT_CATEGORY_ID, NULL, ''Y'', ''N''), ''Y''),
778 NVL((SELECT ''N'' FROM MTL_CATEGORY_SET_VALID_CATS X
779 WHERE X.CATEGORY_SET_ID = T.CATEGORY_SET_ID
780 AND X.PARENT_CATEGORY_ID = T.CATEGORY_ID
781 AND ROWNUM = 1), ''Y'') LEAF_NODE_FLAG,
782 ''N'',
783 ''Y'',
784 ''Y'',
785 :l_user_id,
786 SYSDATE,
787 :l_user_id,
788 SYSDATE,
789 :l_user_id,
790 :l_conc_request_id,
791 :l_prog_appl_id,
792 SYSDATE,
793 :l_conc_program_id
794 FROM MTL_CATEGORY_SET_VALID_CATS T
795 WHERE T.CATEGORY_SET_ID = :g_catset_id';
796
797 EXECUTE IMMEDIATE l_sql USING g_catset_id, l_hrchy_enabled, l_user_id, l_user_id, l_user_id
798 , l_conc_request_id, l_prog_appl_id, l_conc_program_id, g_catset_id;
799
800 END IF;
801
802 l_count := SQL%ROWCOUNT;
803 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserted '||l_count||' Self-referencing records');
804
805 IF NVL(l_hrchy_enabled, 'N') = 'Y' THEN -- Bug# 3013192
806 l_count := 0;
807 -- For Inserting Parent, Immchild, Child Relationships
808 FND_FILE.PUT_LINE(FND_FILE.LOG,'Hierarchy is enabled'); -- Bug# 3013192
809 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting Hierarchical records');
810
811 FOR i in c1 LOOP
812 INSERT INTO ENI_DENORM_HIERARCHIES (
813 PARENT_ID,
814 IMM_CHILD_ID,
815 CHILD_ID,
816 OBJECT_TYPE,
817 OBJECT_ID,
818 TOP_NODE_FLAG,
819 LEAF_NODE_FLAG,
820 ITEM_ASSGN_FLAG,
821 DBI_FLAG,
822 OLTP_FLAG,
823 CREATED_BY,
824 CREATION_DATE,
825 LAST_UPDATED_BY,
826 LAST_UPDATE_DATE,
827 LAST_UPDATE_LOGIN,
828 REQUEST_ID,
829 PROGRAM_APPLICATION_ID,
830 PROGRAM_UPDATE_DATE,
831 PROGRAM_ID)
832 SELECT
833 i.PARENT_ID,
834 i.CHILD_ID,
835 A.CATEGORY_ID,
836 'CATEGORY_SET',
837 g_catset_id,
838 i.TOP_NODE_FLAG,
839 i.LEAF_NODE_FLAG,
840 'N',
841 'Y',
842 'Y',
843 l_user_id,
844 SYSDATE,
845 l_user_id,
846 SYSDATE,
847 l_user_id,
848 l_conc_request_id,
849 l_prog_appl_id,
850 SYSDATE,
851 l_conc_program_id
852 FROM MTL_CATEGORY_SET_VALID_CATS A
853 START WITH A.CATEGORY_ID = i.CHILD_ID AND A.CATEGORY_SET_ID = g_catset_id
857 END LOOP;
854 CONNECT BY A.PARENT_CATEGORY_ID = PRIOR A.CATEGORY_ID AND A.CATEGORY_SET_ID = g_catset_id;
855
856 l_count := l_count + SQL%ROWCOUNT;
858
859 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserted '||l_count||' Hierarchical records');
860 END IF;
861
862 -- IF l_dbi_installed = 'Y' THEN -- ER# 3185516, updating Item Assignment Flag even in non-DBI env.
863 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Item Assignment Flag');
864 -- updating Item Assignment flag for all categories, which have items attached to it
865 UPDATE ENI_DENORM_HIERARCHIES B
866 SET ITEM_ASSGN_FLAG = 'Y'
867 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
868 AND B.OBJECT_ID = g_catset_id
869 AND EXISTS (SELECT NULL
870 FROM MTL_ITEM_CATEGORIES C
871 WHERE C.CATEGORY_SET_ID = g_catset_id
872 AND C.CATEGORY_ID = B.CHILD_ID);
873
874 l_count := SQL%ROWCOUNT;
875
876 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Item Assignment Flag');
877
878 FND_FILE.PUT_LINE(FND_FILE.LOG,'Checking Item Assignments for Unassigned');
879 -- Checking Item assignment flag for Unassigned category
880 -- if all items are attached to some categories within this category set then
881 -- Item assignment flag for Unassigned node will be 'N'
882 BEGIN
883 SELECT 1 INTO l_count
884 FROM MTL_SYSTEM_ITEMS_B IT
885 WHERE ROWNUM = 1
886 AND NOT EXISTS (SELECT NULL FROM MTL_ITEM_CATEGORIES C
887 WHERE C.CATEGORY_SET_ID = g_catset_id
888 AND C.INVENTORY_ITEM_ID = IT.INVENTORY_ITEM_ID
889 AND C.ORGANIZATION_ID = IT.ORGANIZATION_ID);
890 EXCEPTION WHEN NO_DATA_FOUND THEN
891 FND_FILE.PUT_LINE(FND_FILE.LOG,'All Items are assigned to Product Catalog');
892 l_count := 0;
893 END;
894 /* ER# 3185516, updating Item Assignment Flag even in non-DBI env.
895 ELSE
896 l_count := 0;
897 END IF;
898 */
899
900 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting Unassigned Node');
901 INSERT INTO ENI_DENORM_HIERARCHIES (
902 PARENT_ID,
903 IMM_CHILD_ID,
904 CHILD_ID,
905 OBJECT_TYPE,
906 OBJECT_ID,
907 TOP_NODE_FLAG,
908 LEAF_NODE_FLAG,
909 ITEM_ASSGN_FLAG,
910 DBI_FLAG,
911 OLTP_FLAG,
912 CREATED_BY,
913 CREATION_DATE,
914 LAST_UPDATED_BY,
915 LAST_UPDATE_DATE,
916 LAST_UPDATE_LOGIN,
917 REQUEST_ID,
918 PROGRAM_APPLICATION_ID,
919 PROGRAM_UPDATE_DATE,
920 PROGRAM_ID)
921 VALUES(
922 -1,
923 -1,
924 -1,
925 'CATEGORY_SET',
926 g_catset_id,
927 'Y',
928 'Y',
929 DECODE(l_count, 1, 'Y', 'N'),
930 'Y',
931 'N',
932 l_user_id,
933 SYSDATE,
934 l_user_id,
935 SYSDATE,
936 l_user_id,
937 l_conc_request_id,
938 l_prog_appl_id,
939 SYSDATE,
940 l_conc_program_id);
941
942 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gathering statistics on table: ENI_DENORM_HIERARCHIES ');
943 FND_STATS.gather_table_stats (ownname=>'ENI', tabname=>'ENI_DENORM_HIERARCHIES');
944
945 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Denorm table Initial Load completed successfully');
946 EXCEPTION
947 WHEN NO_DATA_FOUND THEN
948 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: No Data Found. Transaction will be rolled back');
949 errbuf := 'No data found ' || sqlerrm;
950 retcode := 2;
951 ROLLBACK;
952 RAISE;
953 WHEN OTHERS THEN
954 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
955 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.LOAD_HIERARCHY', 'Error: ' ||
956 sqlerrm || ' .Transaction will be rolled back');
957 end if;
958 errbuf := 'Error :' || sqlerrm;
959 retcode := 2;
960 ROLLBACK;
961 RAISE;
962 END LOAD_HIERARCHY;
963
964 -- This Procedure Updates The Denorm Table In Incremental Mode, Wrt To Changes Made In Product Catalog Hierarchy
965 -- This Procedure picks up records to be added/modified from Staging table
966 PROCEDURE SYNC_HIERARCHY(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS
967
968 CURSOR top_nodes IS
969 SELECT DISTINCT TOP_NODE_ID
970 FROM ENI_DENORM_HRCHY_STG
971 WHERE BATCH_FLAG <> 'NEXT_BATCH'
972 AND OBJECT_TYPE = 'CATEGORY_SET'
973 AND OBJECT_ID = g_catset_id
974 AND MODE_FLAG IN ('A', 'M'); -- modified for sales and marketing enhancement
975
976 l_affected_child NUMBER;
977 l_affected_level NUMBER;
978 l_count NUMBER := 0;
979 l_dbi_installed VARCHAR2(1) := IS_DBI_INSTALLED; -- variable to hold installation flag for DBI
980 l_user_id NUMBER := FND_GLOBAL.USER_ID;
981 l_conc_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
982 l_prog_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
983 l_conc_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
984 l_sql VARCHAR2(32000);
985 l_validate_flag VARCHAR2(1); -- Bug# 3306212
986 l_struct_id NUMBER; -- Bug# 3306212
987
988 BEGIN
989 BEGIN -- Bug# 3306212
990 SELECT VALIDATE_FLAG, STRUCTURE_ID INTO l_validate_flag, l_struct_id
991 FROM MTL_CATEGORY_SETS_B
992 WHERE CATEGORY_SET_ID = g_catset_id;
993 EXCEPTION WHEN NO_DATA_FOUND THEN
994 FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Category Set associated with Product Reporting functional area');
998 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Denorm table Incremental Load begining');
995 RAISE;
996 END;
997
999
1000 IF NVL(l_validate_flag, 'N') = 'N' THEN -- Bug# 3306212
1001 INSERT INTO ENI_DENORM_HIERARCHIES(
1002 PARENT_ID,
1003 IMM_CHILD_ID,
1004 CHILD_ID,
1005 OBJECT_TYPE,
1006 OBJECT_ID,
1007 TOP_NODE_FLAG,
1008 LEAF_NODE_FLAG,
1009 ITEM_ASSGN_FLAG,
1010 DBI_FLAG,
1011 OLTP_FLAG,
1012 CREATED_BY,
1013 CREATION_DATE,
1014 LAST_UPDATED_BY,
1015 LAST_UPDATE_DATE,
1016 LAST_UPDATE_LOGIN,
1017 REQUEST_ID,
1018 PROGRAM_APPLICATION_ID,
1019 PROGRAM_UPDATE_DATE,
1020 PROGRAM_ID)
1021 SELECT
1022 B.CATEGORY_ID,
1023 B.CATEGORY_ID,
1024 B.CATEGORY_ID,
1025 'CATEGORY_SET',
1026 g_catset_id,
1027 'Y' TOP_NODE_FLAG,
1028 'Y' LEAF_NODE_FLAG,
1029 'N' ITEM_ASSGN_FLAG,
1030 'Y' DBI_FLAG,
1031 'Y' OLTP_FLAG,
1032 l_user_id,
1033 SYSDATE,
1034 l_user_id,
1035 SYSDATE,
1036 l_user_id,
1040 l_conc_program_id
1037 l_conc_request_id,
1038 l_prog_appl_id,
1039 SYSDATE,
1041 FROM MTL_CATEGORIES_B B
1042 WHERE B.STRUCTURE_ID = l_struct_id
1043 AND NOT EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES H
1044 WHERE H.OBJECT_TYPE = 'CATEGORY_SET'
1045 AND H.OBJECT_ID = g_catset_id
1046 AND H.PARENT_ID = B.CATEGORY_ID
1047 AND H.CHILD_ID = B.CATEGORY_ID);
1048 ELSE -- Bug# 3306212 end
1049 -- To Get The Top Node And Child Level In Temp Table
1050 UPDATE ENI_DENORM_HRCHY_STG T
1051 SET (TOP_NODE_ID, CHILD_LEVEL)=
1052 (SELECT X.CATEGORY_ID, LEVEL
1053 FROM MTL_CATEGORY_SET_VALID_CATS X
1054 WHERE X.PARENT_CATEGORY_ID IS NULL
1055 START WITH X.CATEGORY_ID = T.CHILD_ID AND X.CATEGORY_SET_ID = g_catset_id
1056 CONNECT BY X.CATEGORY_ID = PRIOR X.PARENT_CATEGORY_ID AND X.CATEGORY_SET_ID = g_catset_id),
1057 BATCH_FLAG = 'CURRENT_BATCH'
1058 WHERE OBJECT_TYPE = 'CATEGORY_SET'
1059 AND T.OBJECT_ID = g_catset_id;
1060
1064 -- commiting to release Lock on ENI_DENORM_HRCHY_STG table. If any exception occurs then
1061 l_count := SQL%ROWCOUNT;
1062 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records found in Staging Table for Incremental Load');
1063
1065 -- batch_flag will be updated back to 'NEXT_BATCH' and commited.
1066 COMMIT;
1067
1068 -- Deleting Nodes from Denorm Table, which are deleted i.e. MODE_FLAG='D'
1069 -- If a parent node is deleted from hierarchy, then all its children are also deleted from hierarchy
1070 -- and all the nodes actually deleted from hierarchy will be inserted into the staging table with
1071 -- mode_flag = 'D'. So we need to delete all records from denorm table, where child_id = child_id in staging table
1072 -- and mode_flag = 'D'
1073 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting Nodes from Denorm Table, which are deleted from Hierarchy');
1074
1075 -- Bug# 3047381 , removed use of ROWID , instead using PK columns
1076 DELETE FROM ENI_DENORM_HIERARCHIES B -- changed the statement due to performance reasons
1077 WHERE OBJECT_TYPE = 'CATEGORY_SET'
1078 AND OBJECT_ID = g_catset_id
1079 AND EXISTS (SELECT NULL
1080 FROM ENI_DENORM_HRCHY_STG S
1081 WHERE S.OBJECT_TYPE = B.OBJECT_TYPE
1082 AND S.OBJECT_ID = B.OBJECT_ID
1083 AND S.CHILD_ID = B.CHILD_ID
1084 AND S.MODE_FLAG = 'D'
1085 AND S.BATCH_FLAG = 'CURRENT_BATCH');
1086
1087 l_count := SQL%ROWCOUNT;
1088 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records deleted from denorm table');
1089
1090 -- Inserting Self Referencing Nodes For New Nodes Into Denorm Table
1091 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting Self-referencing nodes for new nodes');
1092 INSERT INTO ENI_DENORM_HIERARCHIES(
1093 PARENT_ID,
1094 IMM_CHILD_ID,
1095 CHILD_ID,
1096 OBJECT_TYPE,
1097 OBJECT_ID,
1098 TOP_NODE_FLAG,
1099 LEAF_NODE_FLAG,
1100 ITEM_ASSGN_FLAG,
1101 DBI_FLAG,
1102 OLTP_FLAG,
1103 CREATED_BY,
1104 CREATION_DATE,
1105 LAST_UPDATED_BY,
1106 LAST_UPDATE_DATE,
1107 LAST_UPDATE_LOGIN,
1108 REQUEST_ID,
1109 PROGRAM_APPLICATION_ID,
1110 PROGRAM_UPDATE_DATE,
1111 PROGRAM_ID)
1112 SELECT
1113 S.CHILD_ID,
1114 CHILD_ID,
1115 CHILD_ID,
1116 'CATEGORY_SET',
1117 g_catset_id,
1118 DECODE(CHILD_ID, TOP_NODE_ID, 'Y', 'N'), -- Bug# 3047381, removed use of SIGN function
1119 'N',
1120 'N',
1121 'Y',
1122 'Y',
1123 l_user_id,
1124 SYSDATE,
1125 l_user_id,
1126 SYSDATE,
1127 l_user_id,
1128 l_conc_request_id,
1129 l_prog_appl_id,
1130 SYSDATE,
1131 l_conc_program_id
1132 FROM ENI_DENORM_HRCHY_STG S
1133 WHERE S.OBJECT_TYPE = 'CATEGORY_SET'
1134 AND S.OBJECT_ID = g_catset_id
1135 AND S.MODE_FLAG = 'A'
1136 AND S.BATCH_FLAG = 'CURRENT_BATCH';
1137
1138 l_count := SQL%ROWCOUNT;
1139 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records inserted as Self-referencing nodes');
1140
1141 l_count := 0;
1142 -- Deleting all rows, which will no longer be a part of Hierarchy
1143 -- whenever a node is moved i.e. MODE_FLAG='M', there will be some records in denorm table
1144 -- which needs to be deleted, as they will no longer will be a part of the hierarhcy
1145 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleting records, which are no longer required, due to movement in Hierarchy');
1146 FOR i IN (SELECT * FROM ENI_DENORM_HRCHY_STG
1147 WHERE OBJECT_TYPE = 'CATEGORY_SET'
1148 AND OBJECT_ID = g_catset_id
1149 AND MODE_FLAG = 'M'
1150 AND BATCH_FLAG = 'CURRENT_BATCH'
1151 ORDER BY CHILD_LEVEL DESC) LOOP -- Bug# 3047381, removed TOP_NODE_ID ASC from order by clause
1152
1153 DELETE FROM ENI_DENORM_HIERARCHIES B
1154 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1155 AND B.OBJECT_ID = g_catset_id
1156 AND EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES T -- all records with child = i.child_id or children of i.child_id
1157 WHERE T.OBJECT_TYPE = B.OBJECT_TYPE
1158 AND T.OBJECT_ID = B.OBJECT_ID
1159 AND B.CHILD_ID = T.CHILD_ID
1160 AND T.PARENT_ID = i.CHILD_ID)
1161 AND NOT EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES D -- Hierarchy below the i.child_id must not be deleted
1162 WHERE D.OBJECT_TYPE = B.OBJECT_TYPE
1163 AND D.OBJECT_ID = B.OBJECT_ID
1164 AND B.PARENT_ID = D.CHILD_ID
1165 AND D.PARENT_ID = i.CHILD_ID)
1166 AND NOT EXISTS (-- Find New Parents, All Records Which Are A Part Of New Hierarchy
1167 SELECT NULL
1168 FROM MTL_CATEGORY_SET_VALID_CATS C
1169 WHERE C.PARENT_CATEGORY_ID IS NOT NULL
1173 CONNECT BY C.CATEGORY_ID = PRIOR C.PARENT_CATEGORY_ID AND C.CATEGORY_SET_ID = g_catset_id);
1170 AND C.PARENT_CATEGORY_ID = B.PARENT_ID
1171 AND C.CATEGORY_ID = B.IMM_CHILD_ID
1172 START WITH C.CATEGORY_ID = i.PARENT_ID AND C.CATEGORY_SET_ID = g_catset_id
1174
1175 l_count := l_count + SQL%ROWCOUNT;
1176 END LOOP;
1177
1178 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records deleted');
1179
1180 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting new relations');
1181 l_count := 0;
1182 -- Creating Records For Changes In The Hierarchy
1183 FOR i IN top_nodes LOOP
1184 LOOP
1185 -- selecting lowest level child first
1186 BEGIN
1187 SELECT CHILD_ID , CHILD_LEVEL
1188 INTO l_affected_child , l_affected_level
1189 FROM
1190 (SELECT CHILD_ID, CHILD_LEVEL
1191 FROM ENI_DENORM_HRCHY_STG T
1192 WHERE OBJECT_TYPE = 'CATEGORY_SET'
1193 AND OBJECT_ID = g_catset_id
1194 AND TOP_NODE_ID = i.TOP_NODE_ID
1195 AND BATCH_FLAG = 'CURRENT_BATCH'
1196 AND MODE_FLAG IN ('A', 'M') -- modified for sales and marketing enhancement
1197 ORDER BY CHILD_LEVEL DESC)
1198 WHERE ROWNUM=1;
1199 EXCEPTION
1200 WHEN NO_DATA_FOUND THEN
1201 l_affected_child := NULL;
1202 l_affected_level := NULL;
1203 EXIT;
1204 END;
1205
1206 -- Inserting records due to change in hierarchy
1207 INSERT INTO ENI_DENORM_HIERARCHIES (
1208 PARENT_ID,
1209 IMM_CHILD_ID,
1210 CHILD_ID,
1211 OBJECT_TYPE,
1212 OBJECT_ID,
1213 TOP_NODE_FLAG,
1214 LEAF_NODE_FLAG,
1215 ITEM_ASSGN_FLAG,
1216 DBI_FLAG,
1217 OLTP_FLAG,
1218 CREATED_BY,
1219 CREATION_DATE,
1220 LAST_UPDATED_BY,
1221 LAST_UPDATE_DATE,
1222 LAST_UPDATE_LOGIN,
1223 REQUEST_ID,
1224 PROGRAM_APPLICATION_ID,
1225 PROGRAM_UPDATE_DATE,
1226 PROGRAM_ID)
1227 SELECT
1228 A.PARENT_ID,
1229 A.IMM_CHILD_ID,
1230 B.CHILD_ID,
1231 'CATEGORY_SET',
1232 g_catset_id,
1233 DECODE(A.PARENT_ID, i.TOP_NODE_ID, 'Y', 'N'), -- Bug# 3047381, removed use of SIGN function
1234 'N',
1235 'N',
1236 'Y',
1237 'Y',
1238 l_user_id,
1239 SYSDATE,
1240 l_user_id,
1241 SYSDATE,
1242 l_user_id,
1243 l_conc_request_id,
1244 l_prog_appl_id,
1245 SYSDATE,
1246 l_conc_program_id
1247 FROM
1248 (SELECT PARENT_CATEGORY_ID PARENT_ID, CATEGORY_ID IMM_CHILD_ID
1249 FROM MTL_CATEGORY_SET_VALID_CATS
1250 WHERE PARENT_CATEGORY_ID IS NOT NULL
1251 START WITH CATEGORY_ID = l_affected_child AND CATEGORY_SET_ID = g_catset_id
1252 CONNECT BY CATEGORY_ID = PRIOR PARENT_CATEGORY_ID AND CATEGORY_SET_ID = g_catset_id) A,
1253 (SELECT CATEGORY_ID CHILD_ID
1254 FROM MTL_CATEGORY_SET_VALID_CATS A
1255 START WITH CATEGORY_ID = l_affected_child AND A.CATEGORY_SET_ID = g_catset_id
1256 CONNECT BY PARENT_CATEGORY_ID = PRIOR CATEGORY_ID AND A.CATEGORY_SET_ID = g_catset_id) B
1257 WHERE NOT EXISTS (SELECT NULL FROM ENI_DENORM_HIERARCHIES P
1258 WHERE P.OBJECT_TYPE = 'CATEGORY_SET'
1259 AND P.OBJECT_ID = g_catset_id
1260 AND P.PARENT_ID = A.PARENT_ID
1261 AND P.IMM_CHILD_ID = A.IMM_CHILD_ID
1262 AND P.CHILD_ID = B.CHILD_ID);
1263
1264 l_count := l_count + SQL%ROWCOUNT;
1265
1266 -- Updating STG Table, making current child as PROCESSED, so that it will not be picked up again
1267 UPDATE ENI_DENORM_HRCHY_STG SET BATCH_FLAG = 'PROCESSED'
1268 WHERE OBJECT_TYPE = 'CATEGORY_SET'
1269 AND OBJECT_ID = g_catset_id
1270 AND CHILD_ID = l_affected_child
1271 AND BATCH_FLAG = 'CURRENT_BATCH';
1272
1273 END LOOP;
1274 END LOOP; -- End Loop For Top Nodes
1275
1276 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records inserted');
1277
1278 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Leaf Node Flag');
1279 -- updating leaf node flag for all records where new leaf_node_flag <> current leaf_node_flag
1280 -- Using Execute Immediate because the same code doesn't compiles without Execute immediate
1281 -- Bug# 3045649, added WHO columns in update statements
1282 l_sql :=
1283 'UPDATE ENI_DENORM_HIERARCHIES B
1284 SET LEAF_NODE_FLAG = DECODE(B.LEAF_NODE_FLAG, ''N'', ''Y'', ''N'') ,
1285 LAST_UPDATE_DATE = SYSDATE,
1286 LAST_UPDATED_BY = :l_user_id,
1287 LAST_UPDATE_LOGIN = :l_user_id,
1288 REQUEST_ID = :l_conc_request_id,
1289 PROGRAM_APPLICATION_ID = :l_prog_appl_id,
1290 PROGRAM_UPDATE_DATE = SYSDATE,
1291 PROGRAM_ID = :l_conc_program_id
1292 WHERE B.OBJECT_TYPE = ''CATEGORY_SET''
1293 AND B.OBJECT_ID = :g_catset_id
1294 AND B.CHILD_ID <> -1
1295 AND B.LEAF_NODE_FLAG <> NVL((SELECT ''N''
1296 FROM MTL_CATEGORY_SET_VALID_CATS C
1297 WHERE C.CATEGORY_SET_ID = :g_catset_id
1298 AND B.IMM_CHILD_ID = C.PARENT_CATEGORY_ID
1299 AND ROWNUM = 1), ''Y'')';
1300
1301 EXECUTE IMMEDIATE l_sql USING l_user_id, l_user_id, l_conc_request_id, l_prog_appl_id, l_conc_program_id, g_catset_id, g_catset_id;
1302
1303 l_count := SQL%ROWCOUNT;
1304
1305 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Leaf Node Flag');
1306
1307 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Top Node Flag');
1308 -- updating top node flag for all records where new top_node_flag <> current top_node_flag
1309 -- using a inline view to improve the performance
1310 -- Bug# 3045649, added WHO columns in update statements
1311
1312 --
1313 -- Bug 12615896. Inline view update statement failing
1314 -- after online patching changes. So modified it to use
1315 -- merge.
1316 -- sreharih. Mon Jun 27 12:36:38 PDT 2011
1317 --
1318
1319 MERGE INTO ENI_DENORM_HIERARCHIES B
1320 USING ( SELECT category_id, category_set_id, DECODE(PARENT_CATEGORY_ID, null, 'Y', 'N') NEW_TOP_NODE
1321 FROM MTL_CATEGORY_SET_VALID_CATS) C
1322 ON ( B.OBJECT_ID = C.CATEGORY_SET_ID
1323 AND B.PARENT_ID = C.CATEGORY_ID
1324 AND B.OBJECT_TYPE = 'CATEGORY_SET'
1325 AND B.OBJECT_ID = g_catset_id
1326 )
1327 WHEN matched THEN
1328 UPDATE SET TOP_NODE_FLAG = DECODE(TOP_NODE_FLAG, 'N', 'Y', 'N'),--C.new_top_node,
1329 LAST_UPDATE_DATE = SYSDATE,
1330 LAST_UPDATED_BY = l_user_id,
1331 LAST_UPDATE_LOGIN = l_user_id,
1332 REQUEST_ID = l_conc_request_id,
1333 PROGRAM_APPLICATION_ID = l_prog_appl_id,
1334 PROGRAM_UPDATE_DATE = SYSDATE,
1335 PROGRAM_ID = l_conc_program_id
1336 WHERE C.new_top_node <> B.TOP_NODE_FLAG;
1337
1338 /* Commented out for 12615896
1339 UPDATE (
1340 SELECT B.PARENT_ID, DECODE(C.PARENT_CATEGORY_ID, NULL, 'Y', 'N') NEW_TOP_NODE, B.TOP_NODE_FLAG,
1341 B.LAST_UPDATE_DATE, B.LAST_UPDATED_BY, B.LAST_UPDATE_LOGIN, B.REQUEST_ID, B.PROGRAM_APPLICATION_ID,
1342 B.PROGRAM_UPDATE_DATE, B.PROGRAM_ID
1343 FROM ENI_DENORM_HIERARCHIES B, MTL_CATEGORY_SET_VALID_CATS C
1347 AND C.CATEGORY_ID = B.PARENT_ID)
1344 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1345 AND B.OBJECT_ID = g_catset_id
1346 AND C.CATEGORY_SET_ID = B.OBJECT_ID
1348 SET
1349 TOP_NODE_FLAG = DECODE(TOP_NODE_FLAG, 'N', 'Y', 'N'),
1350 LAST_UPDATE_DATE = SYSDATE,
1351 LAST_UPDATED_BY = l_user_id,
1352 LAST_UPDATE_LOGIN = l_user_id,
1353 REQUEST_ID = l_conc_request_id,
1354 PROGRAM_APPLICATION_ID = l_prog_appl_id,
1355 PROGRAM_UPDATE_DATE = SYSDATE,
1356 PROGRAM_ID = l_conc_program_id
1357 WHERE NEW_TOP_NODE <> TOP_NODE_FLAG;
1358 */
1359
1360 l_count := SQL%ROWCOUNT;
1361 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Top Node Flag');
1362 END IF; -- Bug# 3306212
1363
1364 -- IF l_dbi_installed = 'Y' THEN -- ER# 3185516, updating Item Assignment Flag even in non-DBI env.
1365 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Item Assignment Flag');
1366 -- updating Item Assignment flag to 'Y', if item assignment is present and current Item assgn flag is not 'Y'
1367 -- Bug# 3045649, added WHO columns in update statements
1368 UPDATE ENI_DENORM_HIERARCHIES B
1369 SET
1370 ITEM_ASSGN_FLAG = 'Y',
1371 LAST_UPDATE_DATE = SYSDATE,
1372 LAST_UPDATED_BY = l_user_id,
1373 LAST_UPDATE_LOGIN = l_user_id,
1374 REQUEST_ID = l_conc_request_id,
1375 PROGRAM_APPLICATION_ID = l_prog_appl_id,
1376 PROGRAM_UPDATE_DATE = SYSDATE,
1377 PROGRAM_ID = l_conc_program_id
1378 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1379 AND B.OBJECT_ID = g_catset_id
1380 AND B.CHILD_ID <> -1
1381 AND B.ITEM_ASSGN_FLAG <> 'Y'
1382 AND EXISTS (SELECT NULL
1383 FROM MTL_ITEM_CATEGORIES C
1384 WHERE C.CATEGORY_SET_ID = g_catset_id
1385 AND C.CATEGORY_ID = B.CHILD_ID);
1386
1387 l_count := SQL%ROWCOUNT;
1388
1389 -- updating Item Assignment flag to 'N', if item assignment is not present and current Item assgn flag is not 'N'
1390 UPDATE ENI_DENORM_HIERARCHIES B
1391 SET
1392 ITEM_ASSGN_FLAG = 'N',
1393 LAST_UPDATE_DATE = SYSDATE,
1394 LAST_UPDATED_BY = l_user_id,
1395 LAST_UPDATE_LOGIN = l_user_id,
1396 REQUEST_ID = l_conc_request_id,
1397 PROGRAM_APPLICATION_ID = l_prog_appl_id,
1398 PROGRAM_UPDATE_DATE = SYSDATE,
1399 PROGRAM_ID = l_conc_program_id
1400 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1401 AND B.OBJECT_ID = g_catset_id
1402 AND B.CHILD_ID <> -1
1403 AND B.ITEM_ASSGN_FLAG <> 'N'
1404 AND NOT EXISTS (SELECT NULL
1405 FROM MTL_ITEM_CATEGORIES C
1406 WHERE C.CATEGORY_SET_ID = g_catset_id
1407 AND C.CATEGORY_ID = B.CHILD_ID);
1408
1409 l_count := l_count + SQL%ROWCOUNT;
1410 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Item Assignment Flag');
1411 -- END IF; -- ER# 3185516, updating Item Assignment Flag even in non-DBI env.
1412
1413 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Incremental Load of Denorm table complete');
1414 EXCEPTION
1415 WHEN OTHERS THEN
1416 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1417 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.SYNC_HIERARCHY', 'Error: ' ||
1418 sqlerrm || ' .Transaction will be rolled back');
1419 end if;
1420 errbuf := 'Error :' || sqlerrm;
1421 retcode := 2;
1422 ROLLBACK;
1423 -- if any error occurs, then updating staging table's batch_flag back to 'NEXT_BATCH'
1424 -- so that it can be picked up in next incremental load.
1425 UPDATE ENI_DENORM_HRCHY_STG
1426 SET BATCH_FLAG = 'NEXT_BATCH'
1427 WHERE BATCH_FLAG <> 'NEXT_BATCH'
1428 AND OBJECT_TYPE = 'CATEGORY_SET'
1429 AND OBJECT_ID = g_catset_id;
1430 COMMIT;
1431 RAISE;
1432 END SYNC_HIERARCHY;
1433
1434 -- This Procedure Denormalizes the Product Catalog Hierarchy into Denorm Table
1435 -- This will accept the parameter as 'FULL' or 'PARTIAL', depending on which, Initial or
1436 -- Incremental Load will be called
1437 PROCEDURE LOAD_PRODUCT_HIERARCHY(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, p_refresh_mode IN VARCHAR2) IS
1438 err VARCHAR2(2000);
1439 ret VARCHAR2(100);
1440 l_cnt NUMBER; -- Bug# 3057568
1441 l_AS_installed BOOLEAN := FALSE; -- for Oracle Sales
1442 l_AMS_installed BOOLEAN := FALSE; -- for Oracle Marketing Online
1443 l_OZF_installed BOOLEAN := FALSE; -- for Oracle Trade Management
1444 l_ASN_installed BOOLEAN := FALSE; --Bug 4728981
1445 l_installed BOOLEAN := FALSE;
1446 l_status VARCHAR2(1) := 'N';
1447 l_industry VARCHAR2(1) := NULL;
1448
1449 BEGIN
1450 -- checking installation of 'AS' (Oracle Sales) , 'AMS' (Oracle Marketing Online), 'OZF' (Oracle Trade Management)
1451 -- AS - 279
1452 -- AMS - 530
1453 -- OZF - 682
1454
1455 -- checking installation of 'AS' (Oracle Sales)
1456 l_status := NULL;
1457 l_installed := fnd_installation.get(appl_id => 279, dep_appl_id => 279, status => l_status, industry => l_industry );
1458 IF NVL(l_status, 'N') = 'I' THEN
1459 l_AS_installed := TRUE;
1460 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Oracle Sales (AS) is installed.');
1461 END IF;
1462
1463 -- checking installation of 'AMS' (Oracle Marketing Online)
1464 l_status := NULL;
1465 l_installed := fnd_installation.get(appl_id => 530, dep_appl_id => 530, status => l_status, industry => l_industry );
1466 IF NVL(l_status, 'N') = 'I' THEN
1467 l_AMS_installed := TRUE;
1468 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Oracle Marketing Online (AMS) is installed.');
1469 END IF;
1470
1471 -- checking installation of 'OZF' (Oracle Trade Management)
1472 l_status := NULL;
1473 l_installed := fnd_installation.get(appl_id => 682, dep_appl_id => 682, status => l_status, industry => l_industry );
1474 IF NVL(l_status, 'N') = 'I' THEN
1475 l_OZF_installed := TRUE;
1476 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Oracle Trade Management (OZF) is installed.');
1477 END IF;
1478
1479 -- checking installation of 'ASN'
1480 l_status := NULL;
1481 l_installed := fnd_installation.get(appl_id => 280, dep_appl_id => 280, status => l_status, industry => l_industry );
1482 IF NVL(l_status, 'N') = 'I' THEN
1483 l_ASN_installed := TRUE;
1484 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ASN is installed.');
1485 END IF;
1486
1487 -- if any of the above applications are installed then load de-norm parents table
1488 IF l_AS_installed OR l_AMS_installed OR l_OZF_installed OR l_ASN_installed THEN
1489 l_installed := TRUE;
1490 ELSE
1491 l_installed := FALSE;
1492 END IF;
1493
1494 IF p_refresh_mode = 'FULL' THEN
1495 LOAD_HIERARCHY(err, ret);
1496 errbuf := err;
1497 retcode := ret;
1498
1499 -- if sales and marketing is installed then call loading of denorm hierarchy parents table
1500 IF NVL(retcode, 0) <> 2 AND l_installed THEN
1501 err := null;
1502 ret := null;
1503
1504 LOAD_DENORM_PARENTS_PROD_HRCHY(err, ret);
1505 errbuf := err;
1506 retcode := ret;
1507 END IF;
1508
1509 ELSIF p_refresh_mode = 'PARTIAL' THEN
1510 BEGIN -- Bug# 3057568 Start
1511 -- Checking if De-norm table is empty then calling Initial Load, else Incr. Load
1512 SELECT 1 INTO l_cnt
1513 FROM ENI_DENORM_HIERARCHIES
1514 WHERE OBJECT_TYPE = 'CATEGORY_SET'
1515 AND ROWNUM = 1;
1516
1517 SYNC_HIERARCHY(err, ret);
1518 errbuf := err;
1519 retcode := ret;
1520
1521 -- if sales and marketing is installed then call loading of denorm hierarchy parents table
1522 IF NVL(retcode, 0) <> 2 AND l_installed THEN
1523 err := null;
1524 ret := null;
1525 -- if de-norm parents table is empty then call full load of only de-norm parents table
1526 BEGIN
1527 SELECT 1 INTO l_cnt
1528 FROM ENI_DENORM_HRCHY_PARENTS
1529 WHERE OBJECT_TYPE = 'CATEGORY_SET'
1530 AND ROWNUM = 1;
1531
1532 SYNC_DENORM_PARENTS_PROD_HRCHY(err, ret);
1533 errbuf := err;
1534 retcode := ret;
1535 EXCEPTION WHEN NO_DATA_FOUND THEN
1536 FND_FILE.PUT_LINE(FND_FILE.LOG, 'De-norm parents table is empty, calling Initial Load for de-norm parents table...');
1537 LOAD_DENORM_PARENTS_PROD_HRCHY(err, ret);
1538 errbuf := err;
1539 retcode := ret;
1540 END;
1541 END IF;
1542
1543 -- deleting all nodes from staging table, where batch_flag is not 'NEXT_BATCH', since the batch_flag can be
1544 -- CURRENT_BATCH or PROCESSED
1545 DELETE FROM ENI_DENORM_HRCHY_STG
1546 WHERE BATCH_FLAG <> 'NEXT_BATCH'
1547 AND OBJECT_TYPE = 'CATEGORY_SET'
1548 AND OBJECT_ID = g_catset_id;
1549
1550 EXCEPTION WHEN NO_DATA_FOUND THEN
1551 FND_FILE.PUT_LINE(FND_FILE.LOG, 'De-norm table is empty, calling Initial Load...');
1552 LOAD_HIERARCHY(err, ret);
1553 errbuf := err;
1554 retcode := ret;
1555
1556 -- if sales and marketing is installed then call loading of denorm hierarchy parents table
1557 IF NVL(retcode, 0) <> 2 AND l_installed THEN
1558 err := null;
1559 ret := null;
1560
1561 LOAD_DENORM_PARENTS_PROD_HRCHY(err, ret);
1562 errbuf := err;
1563 retcode := ret;
1564 END IF;
1565 END; -- Bug# 3057568 end
1566 END IF;
1567
1568 -- synchronizing intermedia index
1569 BEGIN
1570 AD_CTX_DDL.SYNC_INDEX(g_tab_schema || '.ENI_DEN_HRCHY_PAR_IM1');
1571 EXCEPTION WHEN OTHERS THEN
1572 NULL;
1573 END;
1574 EXCEPTION
1575 WHEN OTHERS THEN
1576 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1577 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.LOAD_PRODUCT_HIERARCHY', 'Error: ' ||
1578 sqlerrm || ' .Transaction will be rolled back');
1579 end if;
1580 errbuf := 'Error :' || sqlerrm;
1581 retcode := 2;
1582 ROLLBACK;
1583 -- synchronizing intermedia index even if error occurs
1584 BEGIN
1585 AD_CTX_DDL.SYNC_INDEX(g_tab_schema || '.ENI_DEN_HRCHY_PAR_IM1');
1586 EXCEPTION WHEN OTHERS THEN
1587 NULL;
1588 END;
1589 RAISE;
1590 END LOAD_PRODUCT_HIERARCHY;
1591
1592 -- ER# 3185516
1593 -- This is a wrapper procedure, which will be called whenever there is a change in item assignment
1594 -- this in turn determines whether DBI is installed or not and calls the star pkg. if installed.
1595 PROCEDURE SYNC_CATEGORY_ASSIGNMENTS(
1596 p_api_version NUMBER,
1597 p_init_msg_list VARCHAR2 := 'F',
1598 p_inventory_item_id NUMBER,
1599 p_organization_id NUMBER,
1600 x_return_status OUT NOCOPY VARCHAR2,
1601 x_msg_count OUT NOCOPY NUMBER,
1602 x_msg_data OUT NOCOPY VARCHAR2,
1603 p_category_set_id NUMBER,
1604 p_old_category_id NUMBER,
1605 p_new_category_id NUMBER) IS
1606
1607 l_dbi_installed VARCHAR2(1) := IS_DBI_INSTALLED;
1608 l_return_status VARCHAR2(10);
1609 l_msg_count NUMBER;
1610 l_msg_data VARCHAR2(4000);
1611
1612 BEGIN
1613 IF l_dbi_installed = 'Y' THEN
1614 -- calling the star package to Sync up the category assignments
1615 EXECUTE IMMEDIATE
1616 'BEGIN '||
1617 ' ENI_ITEMS_STAR_PKG.SYNC_CATEGORY_ASSIGNMENTS( '||
1618 ' p_api_version => :p_api_version, '||
1619 ' p_init_msg_list => :p_init_msg_list, '||
1620 ' p_inventory_item_id => :p_inventory_item_id, '||
1621 ' p_organization_id => :p_organization_id, '||
1622 ' x_return_status => :l_return_status, '||
1623 ' x_msg_count => :l_msg_count, '||
1624 ' x_msg_data => :l_msg_data); '||
1625 'END; '
1626 USING
1627 IN p_api_version,
1628 IN p_init_msg_list,
1629 IN p_inventory_item_id,
1630 IN p_organization_id,
1631 OUT l_return_status,
1632 OUT l_msg_count,
1633 OUT l_msg_data;
1634
1635 IF l_return_status = 'U' THEN
1636 x_return_status := l_return_status;
1637 x_msg_count := l_msg_count;
1638 x_msg_data := l_msg_data;
1639 RETURN;
1640 ELSE
1641 x_return_status := 'S';
1642 END IF;
1643
1644 l_return_status := NULL;
1645 l_msg_count := NULL;
1646 l_msg_data := NULL;
1647 END IF;
1648
1649 -- if there is item assignment change for Product category set, then
1650 -- update the item assignments flag in de-norm table
1651 IF (p_category_set_id = g_catset_id
1652 AND NVL(p_old_category_id, -1) <> NVL(p_new_category_id, -1)) THEN
1653 ENI_UPD_ASSGN.UPDATE_ASSGN_FLAG(
1654 p_new_category_id => p_new_category_id,
1655 p_old_category_id => p_old_category_id,
1656 x_return_status => l_return_status,
1657 x_msg_count => l_msg_count,
1658 x_msg_data => l_msg_data);
1659
1660 IF l_return_status = 'U' THEN
1661 x_return_status := l_return_status;
1662 x_msg_count := l_msg_count;
1663 x_msg_data := l_msg_data;
1664 RETURN;
1665 ELSE
1666 x_return_status := 'S';
1667 END IF;
1668 END IF;
1669
1670 EXCEPTION
1671 WHEN OTHERS THEN
1672 x_return_status := 'U';
1673 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1674 FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'SYNC_CATEGORY_ASSIGNMENTS', SQLERRM);
1675 END IF;
1676 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
1677 END SYNC_CATEGORY_ASSIGNMENTS;
1678
1679 -- ER: 3185516
1680 -- This is a wrapper procedure, which will be called after import items
1681 -- This in calls the star pkg and updates the Item Assignment Flag in De-norm table
1682 PROCEDURE SYNC_STAR_ITEMS_FROM_IOI(
1683 p_api_version NUMBER,
1684 p_init_msg_list VARCHAR2 := 'F',
1685 p_set_process_id NUMBER,
1686 x_return_status OUT NOCOPY VARCHAR2,
1687 x_msg_count OUT NOCOPY NUMBER,
1688 x_msg_data OUT NOCOPY VARCHAR2) IS
1689
1690 l_dbi_installed VARCHAR2(1) := IS_DBI_INSTALLED;
1691 l_return_status VARCHAR2(10);
1692 l_msg_count NUMBER;
1693 l_msg_data VARCHAR2(4000);
1694 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1695 l_conc_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
1696 l_prog_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
1697 l_conc_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
1698 l_count NUMBER;
1699 BEGIN
1700 IF l_dbi_installed = 'Y' THEN
1701 EXECUTE IMMEDIATE
1702 'BEGIN '||
1703 ' ENI_ITEMS_STAR_PKG.SYNC_STAR_ITEMS_FROM_IOI( '||
1704 ' p_api_version => :p_api_version, '||
1705 ' p_init_msg_list => :p_init_msg_list, '||
1706 ' p_set_process_id => :p_set_process_id, '||
1707 ' x_return_status => :l_return_status, '||
1708 ' x_msg_count => :l_msg_count, '||
1709 ' x_msg_data => :l_msg_data); '||
1710 'END;'
1711 USING
1712 IN p_api_version,
1713 IN p_init_msg_list,
1714 IN p_set_process_id,
1715 OUT l_return_status,
1716 OUT l_msg_count,
1717 OUT l_msg_data;
1718
1719 IF l_return_status = 'U' THEN
1720 x_return_status := l_return_status;
1721 x_msg_count := l_msg_count;
1722 x_msg_data := l_msg_data;
1723 RETURN;
1724 ELSE
1725 x_return_status := 'S';
1726 END IF;
1727 END IF;
1728
1729 -- updating Item Assignment flag for all categories, which have items attached to it
1730 UPDATE ENI_DENORM_HIERARCHIES B
1731 SET
1732 ITEM_ASSGN_FLAG = 'Y',
1733 LAST_UPDATE_DATE = SYSDATE,
1734 LAST_UPDATED_BY = l_user_id,
1735 LAST_UPDATE_LOGIN = l_user_id,
1736 REQUEST_ID = l_conc_request_id,
1737 PROGRAM_APPLICATION_ID = l_prog_appl_id,
1738 PROGRAM_UPDATE_DATE = SYSDATE,
1739 PROGRAM_ID = l_conc_program_id
1740 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1741 AND B.OBJECT_ID = g_catset_id
1742 AND B.ITEM_ASSGN_FLAG = 'N'
1743 AND EXISTS (SELECT NULL
1744 FROM MTL_ITEM_CATEGORIES C
1745 WHERE C.CATEGORY_SET_ID = g_catset_id
1746 AND C.CATEGORY_ID = B.CHILD_ID);
1747
1748 -- updating Item Assignment flag for all categories, which does not have items attached to it
1749 UPDATE ENI_DENORM_HIERARCHIES B
1750 SET
1751 ITEM_ASSGN_FLAG = 'N',
1752 LAST_UPDATE_DATE = SYSDATE,
1753 LAST_UPDATED_BY = l_user_id,
1754 LAST_UPDATE_LOGIN = l_user_id,
1755 REQUEST_ID = l_conc_request_id,
1756 PROGRAM_APPLICATION_ID = l_prog_appl_id,
1757 PROGRAM_UPDATE_DATE = SYSDATE,
1758 PROGRAM_ID = l_conc_program_id
1759 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1760 AND B.OBJECT_ID = g_catset_id
1761 AND B.ITEM_ASSGN_FLAG = 'Y'
1762 AND B.CHILD_ID <> -1
1763 AND NOT EXISTS (SELECT NULL
1764 FROM MTL_ITEM_CATEGORIES C
1765 WHERE C.CATEGORY_SET_ID = g_catset_id
1766 AND C.CATEGORY_ID = B.CHILD_ID);
1767
1768 -- Checking Item assignment flag for Unassigned category
1769 -- if all items are attached to some categories within this category set then
1770 -- Item assignment flag for Unassigned node will be 'N'
1771
1772 l_count := 0;
1773
1774 BEGIN
1775 SELECT 1 INTO l_count
1776 FROM MTL_SYSTEM_ITEMS_B IT
1777 WHERE ROWNUM = 1
1778 AND NOT EXISTS (SELECT NULL FROM MTL_ITEM_CATEGORIES C
1779 WHERE C.CATEGORY_SET_ID = g_catset_id
1780 AND C.INVENTORY_ITEM_ID = IT.INVENTORY_ITEM_ID
1781 AND C.ORGANIZATION_ID = IT.ORGANIZATION_ID);
1782 EXCEPTION WHEN NO_DATA_FOUND THEN
1783 l_count := 0;
1784 END;
1785
1786 UPDATE ENI_DENORM_HIERARCHIES B
1787 SET
1788 ITEM_ASSGN_FLAG = DECODE(l_count, 0, 'N', 'Y'),
1789 LAST_UPDATE_DATE = SYSDATE,
1790 LAST_UPDATED_BY = l_user_id,
1791 LAST_UPDATE_LOGIN = l_user_id,
1792 REQUEST_ID = l_conc_request_id,
1793 PROGRAM_APPLICATION_ID = l_prog_appl_id,
1794 PROGRAM_UPDATE_DATE = SYSDATE,
1795 PROGRAM_ID = l_conc_program_id
1796 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1797 AND B.OBJECT_ID = g_catset_id
1798 AND B.ITEM_ASSGN_FLAG = DECODE(l_count, 0, 'Y', 'N')
1799 AND B.CHILD_ID = -1
1800 AND B.PARENT_ID = -1;
1801
1802 x_return_status := 'S';
1803 EXCEPTION
1804 WHEN OTHERS THEN
1805 x_return_status := 'U';
1806 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1807 FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'SYNC_STAR_ITEMS_FROM_IOI', SQLERRM);
1808 END IF;
1809 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
1810 END SYNC_STAR_ITEMS_FROM_IOI;
1811
1812 -- This Function returns nth occurence of string value separated by delimiter parameter
1813 -- The occurence is determined by paramter p_level
1814 FUNCTION split_category_codes(
1815 p_str VARCHAR2
1816 ,p_level NUMBER
1817 ,p_delim VARCHAR2 DEFAULT g_delimiter)
1818 RETURN VARCHAR2 IS
1819
1820 l_str VARCHAR2(2000);
1821 l_srch_width NUMBER := Length(p_delim);
1822 l_st NUMBER;
1823 l_end NUMBER;
1824 BEGIN
1825 l_str := Trim(p_str);
1826 IF l_str IS NULL THEN RETURN NULL; END IF;
1827
1828 l_st := InStr(l_str,p_delim);
1829
1830 IF l_st = 0 THEN
1831 RETURN l_str;
1832 ELSE
1833 l_st := InStr(l_str,p_delim,1,p_level);
1834 IF l_st = 0 THEN -- Searching beyond
1835 l_st := InStr(l_str,p_delim,-1,2);
1836 l_end := InStr(l_str,p_delim,-1,1);
1837 RETURN SubStr(l_str,l_st + l_srch_width, l_end - l_st - l_srch_width);
1838 ELSE -- First delimiter is found
1839 l_end := InStr(l_str,p_delim,1,p_level + 1);
1840 IF l_end = 0 THEN --We are the end of string return the last node
1841 l_end := l_st;
1842 l_st := InStr(l_str,p_delim,1,p_level-1);
1843 RETURN SubStr(l_str,l_st + l_srch_width, l_end - l_st - l_srch_width);
1844 ELSE
1845 RETURN SubStr(l_str,l_st + l_srch_width, l_end - l_st - l_srch_width);
1846 END IF;
1847 END IF;
1848 END IF;
1849 END split_category_codes;
1850
1851 -- This Procedure Denormalizes the Product Catalog Hierarchy into a separate denorm table
1852 -- [ENI_ICAT_CDENORM_HIERARCHIES]. It is designed to support SBA/OBIEE requirements.
1853 -- The program is designed to flatten the hierarchy for levels raning between 5 and 10.
1854 -- The number of levels to denormalize is dynamic and is governed by a profile value.
1855 -- Currently it only supports FULL REFRESH.
1856 PROCEDURE LOAD_OBIEE_HIERARCHY(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS
1857 err VARCHAR2(2000);
1858 ret VARCHAR2(100);
1859 l_count NUMBER := 0;
1860 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1861 l_conc_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
1862 l_prog_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
1863 l_conc_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
1864 l_hrchy_enabled VARCHAR2(1);
1865 l_struct_id NUMBER;
1866 l_levels_to_flatten NUMBER := 5;
1867 l_sql VARCHAR2(4000);
1868 l_product_catalog MTL_CATEGORY_SETS_TL.CATEGORY_SET_NAME%TYPE;
1869
1870 BEGIN
1871 retcode := 0;
1872 FND_FILE.PUT_LINE(FND_FILE.LOG,'OBIEE Denorm table Initial Load Start');
1873
1874 -- Finding whether Hierarchy is enabled or not
1875 BEGIN
1876 SELECT HIERARCHY_ENABLED, STRUCTURE_ID INTO l_hrchy_enabled, l_struct_id
1877 FROM MTL_CATEGORY_SETS_B
1878 WHERE CATEGORY_SET_ID = g_catset_id
1879 AND HIERARCHY_ENABLED = 'Y';
1880 EXCEPTION WHEN NO_DATA_FOUND THEN
1881 FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Category Set associated with Product Reporting functional area');
1882 FND_FILE.PUT_LINE(FND_FILE.LOG,'- Or the Category set is not enabled for hierarchies');
1883 RAISE;
1884 END;
1885
1886 BEGIN
1887 SELECT csvl.category_set_name
1888 INTO l_product_catalog
1889 FROM mtl_default_category_sets mdcs
1890 ,mtl_category_sets_vl csvl
1891 WHERE csvl.category_set_id = mdcs.category_set_id AND mdcs.functional_area_id=11;
1892 EXCEPTION WHEN OTHERS THEN
1893 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception while searching for functional area 11');
1894 RAISE;
1895 END;
1896
1897 --Truncate the table first [INITIAL LOAD]
1898 FND_FILE.PUT_LINE(FND_FILE.LOG,'Truncating the DENORM table');
1899
1900 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_tab_schema || '.ENI_ICAT_CDENORM_HIERARCHIES';
1901
1902 --Fetch the profile value for No of levels to flatten
1903 fnd_profile.get('ENI_ICAT_DENORM_LEVEL', l_levels_to_flatten);
1904
1905 IF l_levels_to_flatten IS NULL or l_levels_to_flatten > 10 or l_levels_to_flatten < 5 THEN
1906 FND_FILE.PUT_LINE(FND_FILE.LOG,'Profile [ENI: SBA/OBIEE LEVELS IN HIERARCHY] has incorrect value of: [' || l_levels_to_flatten || ']');
1907 FND_FILE.PUT_LINE(FND_FILE.LOG,'Setting the denormalisation to default level of 5');
1908 l_levels_to_flatten := 5;
1909 END IF;
1910
1911 --Start populating the denorm table
1912 FND_FILE.PUT_LINE(FND_FILE.LOG,'Denormalizing catalog [' || l_product_catalog || '] to ' || l_levels_to_flatten || ' levels.');
1913 FND_FILE.PUT_LINE(FND_FILE.LOG,'Denormalization is Row + Column flattening');
1914
1915 l_sql :=
1916 'INSERT INTO eni_icat_cdenorm_hierarchies ( ' ||
1917 ' category_id_level1 ' ||
1918 ' ,category_id_level2 ' ||
1919 ' ,category_id_level3 ' ||
1920 ' ,category_id_level4 ' ||
1921 ' ,category_id_level5 ';
1922
1923 --Add category_id_level* columns according to the profile value selected
1924 FOR i IN 6..l_levels_to_flatten
1925 LOOP
1926 l_sql := l_sql || ' ,category_id_level' || i;
1927 END LOOP;
1928
1929
1930 l_sql := l_sql ||
1931 ' ,leaf_category_id ' ||
1932 ' ,created_by ' ||
1933 ' ,creation_date ' ||
1934 ' ,last_updated_by ' ||
1935 ' ,last_update_date ' ||
1936 ' ,last_update_login ' ||
1937 ' ,request_id ' ||
1938 ' ,program_application_id ' ||
1939 ' ,program_update_date' ||
1940 ' ,program_id) ';
1941
1942 --Start of SELECT clause
1943 l_sql := l_sql ||
1944 '( SELECT ' ||
1945 ' ENI_DENORM_HRCHY.split_category_codes(catstr,1,''' || g_delimiter || ''') ' ||
1946 ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,2,''' || g_delimiter || ''') ' ||
1947 ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,3,''' || g_delimiter || ''') ' ||
1948 ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,4,''' || g_delimiter || ''') ' ||
1949 ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,5,''' || g_delimiter || ''') ';
1950
1951 --Add category_id_level* columns according to the profile value selected
1952 FOR i IN 6..l_levels_to_flatten
1953 LOOP
1954 l_sql := l_sql || ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,' || i || ',''' || g_delimiter || ''') ';
1955 END LOOP;
1956
1957 l_sql := l_sql ||
1958 ' ,category_id ' ||
1959 ' ,:cr_by ' ||
1960 ' ,:cr_date ' ||
1961 ' ,:upd_by ' ||
1962 ' ,:l_upd_date ' ||
1963 ' ,:upd_login ' ||
1964 ' ,:l_conc_request_id ' ||
1965 ' ,:l_prog_appl_id ' ||
1966 ' ,:p_upd_date ' ||
1967 ' ,:l_conc_program_id ' ||
1968 ' FROM ' ||
1969 ' (SELECT (sys_connect_by_path(vcats.category_id,''' || g_delimiter ||
1970 ''') ||''' || g_delimiter || ''') catstr ' ||
1971 ' , vcats.category_id' ||
1972 ' FROM MTL_CATEGORY_SET_VALID_CATS vcats ' ||
1973 ' WHERE CATEGORY_SET_ID = :g_catset_id ' ||
1974 '/* AND CATEGORY_ID NOT IN (SELECT ' ||
1975 ' Nvl(vcats1.PARENT_CATEGORY_ID,-99) FROM MTL_CATEGORY_SET_VALID_CATS vcats1 WHERE vcats1.CATEGORY_SET_ID = :g_catset_id2) */' ||
1976 ' START WITH PARENT_CATEGORY_ID IS NULL ' ||
1977 ' AND CATEGORY_SET_ID = :g_catset_id3 ' ||
1978 ' CONNECT BY PRIOR CATEGORY_ID = PARENT_CATEGORY_ID ' ||
1979 ' AND PRIOR CATEGORY_SET_ID = CATEGORY_SET_ID ))' ;
1980 -- The NOT IN condition eliminates the row flattened rows from the result
1981 -- Bug 5525229 - commented the NOT IN condition
1982
1983 EXECUTE IMMEDIATE l_sql
1984 USING l_user_id, SYSDATE, l_user_id, SYSDATE, l_user_id, l_conc_request_id ,l_prog_appl_id ,SYSDATE ,l_conc_program_id
1985 ,g_catset_id, g_catset_id;
1986
1987 IF SQL%ROWCOUNT > 0 THEN
1988 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserted [' || SQL%ROWCOUNT ||'] rows into denorm table.');
1989 ELSE
1990 RAISE NO_DATA_FOUND;
1991 END IF;
1992
1993 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert the UNASSIGNED product category row.');
1994
1995 INSERT INTO eni_icat_cdenorm_hierarchies (
1996 category_id_level1
1997 ,category_id_level2
1998 ,category_id_level3
1999 ,category_id_level4
2000 ,category_id_level5
2001 ,category_id_level6
2002 ,category_id_level7
2003 ,category_id_level8
2004 ,category_id_level9
2005 ,category_id_level10
2006 ,leaf_category_id
2007 ,created_by
2008 ,creation_date
2009 ,last_updated_by
2010 ,last_update_date
2011 ,last_update_login
2012 ,request_id
2013 ,program_application_id
2014 ,program_update_date
2015 ,program_id)
2016 VALUES (
2017 -1,-1,-1,-1,-1,-1,-1,-1,-1,-1
2018 ,-1
2019 ,l_user_id
2020 ,SYSDATE
2021 ,l_user_id
2022 ,SYSDATE
2023 ,l_user_id
2024 ,l_conc_request_id
2025 ,l_prog_appl_id
2026 ,SYSDATE
2027 ,l_conc_program_id
2028 );
2029
2030 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gathering statistics on table: ENI_ICAT_CDENORM_HIERARCHIES ');
2031 FND_STATS.gather_table_stats (ownname=>g_tab_schema, tabname=>'ENI_ICAT_CDENORM_HIERARCHIES');
2032
2033 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Denorm table Initial Load completed successfully');
2034
2035 COMMIT;
2036 EXCEPTION
2037 WHEN NO_DATA_FOUND THEN
2038
2039 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: No Data Found. Transaction will be rolled back');
2040 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Catalog [' || l_product_catalog || '] does not have any categories associated to it.');
2041 errbuf := SQLERRM || ' Catalog [' || l_product_catalog || '] does not have any categories associated to it.';
2042 retcode := 1;
2043 ROLLBACK;
2044 WHEN OTHERS THEN
2045 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2046 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.LOAD_OBIEE_HIERARCHY', 'Error: ' ||
2047 sqlerrm || ' .Transaction will be rolled back');
2048 end if;
2049 errbuf := 'Error :' || sqlerrm;
2050 retcode := 2;
2051 ROLLBACK;
2052 END LOAD_OBIEE_HIERARCHY;
2053
2054 END ENI_DENORM_HRCHY;
[Home] [Help]