1 PACKAGE BODY ENI_DENORM_HRCHY AS
2 /* $Header: ENIDENHB.pls 120.4 2007/03/13 08:52:48 lparihar 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'
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,
143 MODE_FLAG = DECODE(MODE_FLAG, 'S', DECODE(p_mode_flag, 'E', 'S', 'S', 'U', p_mode_flag),
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;
276 l_imm_par_id NUMBER;
277 l_length NUMBER;
278 l_count NUMBER;
279
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
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
430 AND NVL(A.EXCLUDE_USER_VIEW, 'N') = 'Y')
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,
556 PROGRAM_APPLICATION_ID = l_prog_appl_id,
557 PROGRAM_UPDATE_DATE = SYSDATE,
558 PROGRAM_ID = l_conc_program_id
559 WHERE OBJECT_TYPE = 'CATEGORY_SET'
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
690 WHERE OBJECT_TYPE = 'CATEGORY_SET'
691 AND OBJECT_ID = g_catset_id;
692
693 l_count := SQL%ROWCOUNT;
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
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;
857 END LOOP;
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');
995 RAISE;
996 END;
997
998 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Denorm table Incremental Load begining');
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,
1037 l_conc_request_id,
1038 l_prog_appl_id,
1039 SYSDATE,
1040 l_conc_program_id
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
1061 l_count := SQL%ROWCOUNT;
1062 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records found in Staging Table for Incremental Load');
1063
1064 -- commiting to release Lock on ENI_DENORM_HRCHY_STG table. If any exception occurs then
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
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
1173 CONNECT BY C.CATEGORY_ID = PRIOR C.PARENT_CATEGORY_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 UPDATE (
1312 SELECT B.PARENT_ID, DECODE(C.PARENT_CATEGORY_ID, NULL, 'Y', 'N') NEW_TOP_NODE, B.TOP_NODE_FLAG,
1313 B.LAST_UPDATE_DATE, B.LAST_UPDATED_BY, B.LAST_UPDATE_LOGIN, B.REQUEST_ID, B.PROGRAM_APPLICATION_ID,
1314 B.PROGRAM_UPDATE_DATE, B.PROGRAM_ID
1315 FROM ENI_DENORM_HIERARCHIES B, MTL_CATEGORY_SET_VALID_CATS C
1316 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1317 AND B.OBJECT_ID = g_catset_id
1318 AND C.CATEGORY_SET_ID = B.OBJECT_ID
1319 AND C.CATEGORY_ID = B.PARENT_ID)
1320 SET
1321 TOP_NODE_FLAG = DECODE(TOP_NODE_FLAG, 'N', 'Y', 'N'),
1322 LAST_UPDATE_DATE = SYSDATE,
1323 LAST_UPDATED_BY = l_user_id,
1324 LAST_UPDATE_LOGIN = l_user_id,
1325 REQUEST_ID = l_conc_request_id,
1326 PROGRAM_APPLICATION_ID = l_prog_appl_id,
1327 PROGRAM_UPDATE_DATE = SYSDATE,
1328 PROGRAM_ID = l_conc_program_id
1329 WHERE NEW_TOP_NODE <> TOP_NODE_FLAG;
1330
1331 l_count := SQL%ROWCOUNT;
1332 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Top Node Flag');
1333 END IF; -- Bug# 3306212
1334
1335 -- IF l_dbi_installed = 'Y' THEN -- ER# 3185516, updating Item Assignment Flag even in non-DBI env.
1336 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Item Assignment Flag');
1337 -- updating Item Assignment flag to 'Y', if item assignment is present and current Item assgn flag is not 'Y'
1338 -- Bug# 3045649, added WHO columns in update statements
1339 UPDATE ENI_DENORM_HIERARCHIES B
1340 SET
1341 ITEM_ASSGN_FLAG = 'Y',
1342 LAST_UPDATE_DATE = SYSDATE,
1343 LAST_UPDATED_BY = l_user_id,
1344 LAST_UPDATE_LOGIN = l_user_id,
1345 REQUEST_ID = l_conc_request_id,
1346 PROGRAM_APPLICATION_ID = l_prog_appl_id,
1347 PROGRAM_UPDATE_DATE = SYSDATE,
1348 PROGRAM_ID = l_conc_program_id
1349 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1350 AND B.OBJECT_ID = g_catset_id
1351 AND B.CHILD_ID <> -1
1352 AND B.ITEM_ASSGN_FLAG <> 'Y'
1353 AND EXISTS (SELECT NULL
1354 FROM MTL_ITEM_CATEGORIES C
1355 WHERE C.CATEGORY_SET_ID = g_catset_id
1356 AND C.CATEGORY_ID = B.CHILD_ID);
1357
1358 l_count := SQL%ROWCOUNT;
1359
1360 -- updating Item Assignment flag to 'N', if item assignment is not present and current Item assgn flag is not 'N'
1361 UPDATE ENI_DENORM_HIERARCHIES B
1362 SET
1363 ITEM_ASSGN_FLAG = 'N',
1364 LAST_UPDATE_DATE = SYSDATE,
1365 LAST_UPDATED_BY = l_user_id,
1366 LAST_UPDATE_LOGIN = l_user_id,
1367 REQUEST_ID = l_conc_request_id,
1368 PROGRAM_APPLICATION_ID = l_prog_appl_id,
1369 PROGRAM_UPDATE_DATE = SYSDATE,
1370 PROGRAM_ID = l_conc_program_id
1371 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1372 AND B.OBJECT_ID = g_catset_id
1373 AND B.CHILD_ID <> -1
1374 AND B.ITEM_ASSGN_FLAG <> 'N'
1375 AND NOT EXISTS (SELECT NULL
1376 FROM MTL_ITEM_CATEGORIES C
1377 WHERE C.CATEGORY_SET_ID = g_catset_id
1378 AND C.CATEGORY_ID = B.CHILD_ID);
1379
1380 l_count := l_count + SQL%ROWCOUNT;
1381 FND_FILE.PUT_LINE(FND_FILE.LOG, l_count||' Records Updated for Item Assignment Flag');
1382 -- END IF; -- ER# 3185516, updating Item Assignment Flag even in non-DBI env.
1383
1384 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Incremental Load of Denorm table complete');
1385 EXCEPTION
1386 WHEN OTHERS THEN
1387 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1388 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.SYNC_HIERARCHY', 'Error: ' ||
1389 sqlerrm || ' .Transaction will be rolled back');
1390 end if;
1391 errbuf := 'Error :' || sqlerrm;
1392 retcode := 2;
1393 ROLLBACK;
1394 -- if any error occurs, then updating staging table's batch_flag back to 'NEXT_BATCH'
1395 -- so that it can be picked up in next incremental load.
1396 UPDATE ENI_DENORM_HRCHY_STG
1397 SET BATCH_FLAG = 'NEXT_BATCH'
1398 WHERE BATCH_FLAG <> 'NEXT_BATCH'
1399 AND OBJECT_TYPE = 'CATEGORY_SET'
1400 AND OBJECT_ID = g_catset_id;
1401 COMMIT;
1402 RAISE;
1403 END SYNC_HIERARCHY;
1404
1405 -- This Procedure Denormalizes the Product Catalog Hierarchy into Denorm Table
1406 -- This will accept the parameter as 'FULL' or 'PARTIAL', depending on which, Initial or
1407 -- Incremental Load will be called
1408 PROCEDURE LOAD_PRODUCT_HIERARCHY(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, p_refresh_mode IN VARCHAR2) IS
1409 err VARCHAR2(2000);
1410 ret VARCHAR2(100);
1411 l_cnt NUMBER; -- Bug# 3057568
1412 l_AS_installed BOOLEAN := FALSE; -- for Oracle Sales
1413 l_AMS_installed BOOLEAN := FALSE; -- for Oracle Marketing Online
1414 l_OZF_installed BOOLEAN := FALSE; -- for Oracle Trade Management
1415 l_ASN_installed BOOLEAN := FALSE; --Bug 4728981
1416 l_installed BOOLEAN := FALSE;
1417 l_status VARCHAR2(1) := 'N';
1418 l_industry VARCHAR2(1) := NULL;
1419
1420 BEGIN
1421 -- checking installation of 'AS' (Oracle Sales) , 'AMS' (Oracle Marketing Online), 'OZF' (Oracle Trade Management)
1422 -- AS - 279
1423 -- AMS - 530
1424 -- OZF - 682
1425
1426 -- checking installation of 'AS' (Oracle Sales)
1427 l_status := NULL;
1428 l_installed := fnd_installation.get(appl_id => 279, dep_appl_id => 279, status => l_status, industry => l_industry );
1429 IF NVL(l_status, 'N') = 'I' THEN
1430 l_AS_installed := TRUE;
1431 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Oracle Sales (AS) is installed.');
1432 END IF;
1433
1434 -- checking installation of 'AMS' (Oracle Marketing Online)
1435 l_status := NULL;
1436 l_installed := fnd_installation.get(appl_id => 530, dep_appl_id => 530, status => l_status, industry => l_industry );
1437 IF NVL(l_status, 'N') = 'I' THEN
1438 l_AMS_installed := TRUE;
1439 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Oracle Marketing Online (AMS) is installed.');
1440 END IF;
1441
1442 -- checking installation of 'OZF' (Oracle Trade Management)
1443 l_status := NULL;
1444 l_installed := fnd_installation.get(appl_id => 682, dep_appl_id => 682, status => l_status, industry => l_industry );
1445 IF NVL(l_status, 'N') = 'I' THEN
1446 l_OZF_installed := TRUE;
1447 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Oracle Trade Management (OZF) is installed.');
1448 END IF;
1449
1450 -- checking installation of 'ASN'
1451 l_status := NULL;
1452 l_installed := fnd_installation.get(appl_id => 280, dep_appl_id => 280, status => l_status, industry => l_industry );
1453 IF NVL(l_status, 'N') = 'I' THEN
1454 l_ASN_installed := TRUE;
1455 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ASN is installed.');
1456 END IF;
1457
1458 -- if any of the above applications are installed then load de-norm parents table
1459 IF l_AS_installed OR l_AMS_installed OR l_OZF_installed OR l_ASN_installed THEN
1460 l_installed := TRUE;
1461 ELSE
1462 l_installed := FALSE;
1463 END IF;
1464
1465 IF p_refresh_mode = 'FULL' THEN
1466 LOAD_HIERARCHY(err, ret);
1467 errbuf := err;
1468 retcode := ret;
1469
1470 -- if sales and marketing is installed then call loading of denorm hierarchy parents table
1471 IF NVL(retcode, 0) <> 2 AND l_installed THEN
1472 err := null;
1473 ret := null;
1474
1475 LOAD_DENORM_PARENTS_PROD_HRCHY(err, ret);
1476 errbuf := err;
1477 retcode := ret;
1478 END IF;
1479
1480 ELSIF p_refresh_mode = 'PARTIAL' THEN
1481 BEGIN -- Bug# 3057568 Start
1482 -- Checking if De-norm table is empty then calling Initial Load, else Incr. Load
1483 SELECT 1 INTO l_cnt
1484 FROM ENI_DENORM_HIERARCHIES
1485 WHERE OBJECT_TYPE = 'CATEGORY_SET'
1486 AND ROWNUM = 1;
1487
1488 SYNC_HIERARCHY(err, ret);
1489 errbuf := err;
1490 retcode := ret;
1491
1492 -- if sales and marketing is installed then call loading of denorm hierarchy parents table
1493 IF NVL(retcode, 0) <> 2 AND l_installed THEN
1494 err := null;
1495 ret := null;
1496 -- if de-norm parents table is empty then call full load of only de-norm parents table
1497 BEGIN
1498 SELECT 1 INTO l_cnt
1499 FROM ENI_DENORM_HRCHY_PARENTS
1500 WHERE OBJECT_TYPE = 'CATEGORY_SET'
1501 AND ROWNUM = 1;
1502
1503 SYNC_DENORM_PARENTS_PROD_HRCHY(err, ret);
1504 errbuf := err;
1505 retcode := ret;
1506 EXCEPTION WHEN NO_DATA_FOUND THEN
1507 FND_FILE.PUT_LINE(FND_FILE.LOG, 'De-norm parents table is empty, calling Initial Load for de-norm parents table...');
1508 LOAD_DENORM_PARENTS_PROD_HRCHY(err, ret);
1509 errbuf := err;
1510 retcode := ret;
1511 END;
1512 END IF;
1513
1514 -- deleting all nodes from staging table, where batch_flag is not 'NEXT_BATCH', since the batch_flag can be
1515 -- CURRENT_BATCH or PROCESSED
1516 DELETE FROM ENI_DENORM_HRCHY_STG
1517 WHERE BATCH_FLAG <> 'NEXT_BATCH'
1518 AND OBJECT_TYPE = 'CATEGORY_SET'
1519 AND OBJECT_ID = g_catset_id;
1520
1521 EXCEPTION WHEN NO_DATA_FOUND THEN
1522 FND_FILE.PUT_LINE(FND_FILE.LOG, 'De-norm table is empty, calling Initial Load...');
1523 LOAD_HIERARCHY(err, ret);
1524 errbuf := err;
1525 retcode := ret;
1526
1527 -- if sales and marketing is installed then call loading of denorm hierarchy parents table
1528 IF NVL(retcode, 0) <> 2 AND l_installed THEN
1529 err := null;
1530 ret := null;
1531
1532 LOAD_DENORM_PARENTS_PROD_HRCHY(err, ret);
1533 errbuf := err;
1534 retcode := ret;
1535 END IF;
1536 END; -- Bug# 3057568 end
1537 END IF;
1538
1539 -- synchronizing intermedia index
1540 BEGIN
1541 AD_CTX_DDL.SYNC_INDEX(g_tab_schema || '.ENI_DEN_HRCHY_PAR_IM1');
1542 EXCEPTION WHEN OTHERS THEN
1543 NULL;
1544 END;
1545 EXCEPTION
1546 WHEN OTHERS THEN
1547 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1548 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.LOAD_PRODUCT_HIERARCHY', 'Error: ' ||
1549 sqlerrm || ' .Transaction will be rolled back');
1550 end if;
1551 errbuf := 'Error :' || sqlerrm;
1552 retcode := 2;
1553 ROLLBACK;
1554 -- synchronizing intermedia index even if error occurs
1555 BEGIN
1556 AD_CTX_DDL.SYNC_INDEX(g_tab_schema || '.ENI_DEN_HRCHY_PAR_IM1');
1557 EXCEPTION WHEN OTHERS THEN
1558 NULL;
1559 END;
1560 RAISE;
1561 END LOAD_PRODUCT_HIERARCHY;
1562
1563 -- ER# 3185516
1564 -- This is a wrapper procedure, which will be called whenever there is a change in item assignment
1565 -- this in turn determines whether DBI is installed or not and calls the star pkg. if installed.
1566 PROCEDURE SYNC_CATEGORY_ASSIGNMENTS(
1567 p_api_version NUMBER,
1568 p_init_msg_list VARCHAR2 := 'F',
1569 p_inventory_item_id NUMBER,
1570 p_organization_id NUMBER,
1571 x_return_status OUT NOCOPY VARCHAR2,
1572 x_msg_count OUT NOCOPY NUMBER,
1573 x_msg_data OUT NOCOPY VARCHAR2,
1574 p_category_set_id NUMBER,
1575 p_old_category_id NUMBER,
1576 p_new_category_id NUMBER) IS
1577
1578 l_dbi_installed VARCHAR2(1) := IS_DBI_INSTALLED;
1579 l_return_status VARCHAR2(10);
1580 l_msg_count NUMBER;
1581 l_msg_data VARCHAR2(4000);
1582
1583 BEGIN
1584 IF l_dbi_installed = 'Y' THEN
1585 -- calling the star package to Sync up the category assignments
1586 EXECUTE IMMEDIATE
1587 'BEGIN '||
1588 ' ENI_ITEMS_STAR_PKG.SYNC_CATEGORY_ASSIGNMENTS( '||
1589 ' p_api_version => :p_api_version, '||
1590 ' p_init_msg_list => :p_init_msg_list, '||
1591 ' p_inventory_item_id => :p_inventory_item_id, '||
1592 ' p_organization_id => :p_organization_id, '||
1593 ' x_return_status => :l_return_status, '||
1594 ' x_msg_count => :l_msg_count, '||
1595 ' x_msg_data => :l_msg_data); '||
1596 'END; '
1597 USING
1598 IN p_api_version,
1599 IN p_init_msg_list,
1600 IN p_inventory_item_id,
1601 IN p_organization_id,
1602 OUT l_return_status,
1603 OUT l_msg_count,
1604 OUT l_msg_data;
1605
1606 IF l_return_status = 'U' THEN
1607 x_return_status := l_return_status;
1608 x_msg_count := l_msg_count;
1609 x_msg_data := l_msg_data;
1610 RETURN;
1611 ELSE
1612 x_return_status := 'S';
1613 END IF;
1614
1615 l_return_status := NULL;
1616 l_msg_count := NULL;
1617 l_msg_data := NULL;
1618 END IF;
1619
1620 -- if there is item assignment change for Product category set, then
1621 -- update the item assignments flag in de-norm table
1622 IF (p_category_set_id = g_catset_id
1623 AND NVL(p_old_category_id, -1) <> NVL(p_new_category_id, -1)) THEN
1624 ENI_UPD_ASSGN.UPDATE_ASSGN_FLAG(
1625 p_new_category_id => p_new_category_id,
1626 p_old_category_id => p_old_category_id,
1627 x_return_status => l_return_status,
1628 x_msg_count => l_msg_count,
1629 x_msg_data => l_msg_data);
1630
1631 IF l_return_status = 'U' THEN
1632 x_return_status := l_return_status;
1633 x_msg_count := l_msg_count;
1634 x_msg_data := l_msg_data;
1635 RETURN;
1636 ELSE
1637 x_return_status := 'S';
1638 END IF;
1639 END IF;
1640
1641 EXCEPTION
1642 WHEN OTHERS THEN
1643 x_return_status := 'U';
1644 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1645 FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'SYNC_CATEGORY_ASSIGNMENTS', SQLERRM);
1646 END IF;
1647 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
1648 END SYNC_CATEGORY_ASSIGNMENTS;
1649
1650 -- ER: 3185516
1651 -- This is a wrapper procedure, which will be called after import items
1652 -- This in calls the star pkg and updates the Item Assignment Flag in De-norm table
1653 PROCEDURE SYNC_STAR_ITEMS_FROM_IOI(
1654 p_api_version NUMBER,
1655 p_init_msg_list VARCHAR2 := 'F',
1656 p_set_process_id NUMBER,
1657 x_return_status OUT NOCOPY VARCHAR2,
1658 x_msg_count OUT NOCOPY NUMBER,
1659 x_msg_data OUT NOCOPY VARCHAR2) IS
1660
1661 l_dbi_installed VARCHAR2(1) := IS_DBI_INSTALLED;
1662 l_return_status VARCHAR2(10);
1663 l_msg_count NUMBER;
1664 l_msg_data VARCHAR2(4000);
1665 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1666 l_conc_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
1667 l_prog_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
1668 l_conc_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
1669 l_count NUMBER;
1670 BEGIN
1671 IF l_dbi_installed = 'Y' THEN
1672 EXECUTE IMMEDIATE
1673 'BEGIN '||
1674 ' ENI_ITEMS_STAR_PKG.SYNC_STAR_ITEMS_FROM_IOI( '||
1675 ' p_api_version => :p_api_version, '||
1676 ' p_init_msg_list => :p_init_msg_list, '||
1677 ' p_set_process_id => :p_set_process_id, '||
1678 ' x_return_status => :l_return_status, '||
1679 ' x_msg_count => :l_msg_count, '||
1680 ' x_msg_data => :l_msg_data); '||
1681 'END;'
1682 USING
1683 IN p_api_version,
1684 IN p_init_msg_list,
1685 IN p_set_process_id,
1686 OUT l_return_status,
1687 OUT l_msg_count,
1688 OUT l_msg_data;
1689
1690 IF l_return_status = 'U' THEN
1691 x_return_status := l_return_status;
1692 x_msg_count := l_msg_count;
1693 x_msg_data := l_msg_data;
1694 RETURN;
1695 ELSE
1696 x_return_status := 'S';
1697 END IF;
1698 END IF;
1699
1700 -- updating Item Assignment flag for all categories, which have items attached to it
1701 UPDATE ENI_DENORM_HIERARCHIES B
1702 SET
1703 ITEM_ASSGN_FLAG = 'Y',
1704 LAST_UPDATE_DATE = SYSDATE,
1705 LAST_UPDATED_BY = l_user_id,
1706 LAST_UPDATE_LOGIN = l_user_id,
1707 REQUEST_ID = l_conc_request_id,
1708 PROGRAM_APPLICATION_ID = l_prog_appl_id,
1709 PROGRAM_UPDATE_DATE = SYSDATE,
1710 PROGRAM_ID = l_conc_program_id
1711 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1712 AND B.OBJECT_ID = g_catset_id
1713 AND B.ITEM_ASSGN_FLAG = 'N'
1714 AND EXISTS (SELECT NULL
1715 FROM MTL_ITEM_CATEGORIES C
1716 WHERE C.CATEGORY_SET_ID = g_catset_id
1717 AND C.CATEGORY_ID = B.CHILD_ID);
1718
1719 -- updating Item Assignment flag for all categories, which does not have items attached to it
1720 UPDATE ENI_DENORM_HIERARCHIES B
1721 SET
1722 ITEM_ASSGN_FLAG = 'N',
1723 LAST_UPDATE_DATE = SYSDATE,
1724 LAST_UPDATED_BY = l_user_id,
1725 LAST_UPDATE_LOGIN = l_user_id,
1726 REQUEST_ID = l_conc_request_id,
1727 PROGRAM_APPLICATION_ID = l_prog_appl_id,
1728 PROGRAM_UPDATE_DATE = SYSDATE,
1729 PROGRAM_ID = l_conc_program_id
1730 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1731 AND B.OBJECT_ID = g_catset_id
1732 AND B.ITEM_ASSGN_FLAG = 'Y'
1733 AND B.CHILD_ID <> -1
1734 AND NOT EXISTS (SELECT NULL
1735 FROM MTL_ITEM_CATEGORIES C
1736 WHERE C.CATEGORY_SET_ID = g_catset_id
1737 AND C.CATEGORY_ID = B.CHILD_ID);
1738
1739 -- Checking Item assignment flag for Unassigned category
1740 -- if all items are attached to some categories within this category set then
1741 -- Item assignment flag for Unassigned node will be 'N'
1742
1743 l_count := 0;
1744
1745 BEGIN
1746 SELECT 1 INTO l_count
1747 FROM MTL_SYSTEM_ITEMS_B IT
1748 WHERE ROWNUM = 1
1749 AND NOT EXISTS (SELECT NULL FROM MTL_ITEM_CATEGORIES C
1750 WHERE C.CATEGORY_SET_ID = g_catset_id
1751 AND C.INVENTORY_ITEM_ID = IT.INVENTORY_ITEM_ID
1752 AND C.ORGANIZATION_ID = IT.ORGANIZATION_ID);
1753 EXCEPTION WHEN NO_DATA_FOUND THEN
1754 l_count := 0;
1755 END;
1756
1757 UPDATE ENI_DENORM_HIERARCHIES B
1758 SET
1759 ITEM_ASSGN_FLAG = DECODE(l_count, 0, 'N', 'Y'),
1760 LAST_UPDATE_DATE = SYSDATE,
1761 LAST_UPDATED_BY = l_user_id,
1762 LAST_UPDATE_LOGIN = l_user_id,
1763 REQUEST_ID = l_conc_request_id,
1764 PROGRAM_APPLICATION_ID = l_prog_appl_id,
1765 PROGRAM_UPDATE_DATE = SYSDATE,
1766 PROGRAM_ID = l_conc_program_id
1767 WHERE B.OBJECT_TYPE = 'CATEGORY_SET'
1768 AND B.OBJECT_ID = g_catset_id
1769 AND B.ITEM_ASSGN_FLAG = DECODE(l_count, 0, 'Y', 'N')
1770 AND B.CHILD_ID = -1
1771 AND B.PARENT_ID = -1;
1772
1773 x_return_status := 'S';
1774 EXCEPTION
1775 WHEN OTHERS THEN
1776 x_return_status := 'U';
1777 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1778 FND_MSG_PUB.ADD_EXC_MSG('ENI_DENORM_HRCHY', 'SYNC_STAR_ITEMS_FROM_IOI', SQLERRM);
1779 END IF;
1780 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count, p_data => x_msg_data);
1781 END SYNC_STAR_ITEMS_FROM_IOI;
1782
1783 -- This Function returns nth occurence of string value separated by delimiter parameter
1784 -- The occurence is determined by paramter p_level
1785 FUNCTION split_category_codes(
1786 p_str VARCHAR2
1787 ,p_level NUMBER
1788 ,p_delim VARCHAR2 DEFAULT g_delimiter)
1789 RETURN VARCHAR2 IS
1790
1791 l_str VARCHAR2(2000);
1792 l_srch_width NUMBER := Length(p_delim);
1793 l_st NUMBER;
1794 l_end NUMBER;
1795 BEGIN
1796 l_str := Trim(p_str);
1797 IF l_str IS NULL THEN RETURN NULL; END IF;
1798
1799 l_st := InStr(l_str,p_delim);
1800
1801 IF l_st = 0 THEN
1802 RETURN l_str;
1803 ELSE
1804 l_st := InStr(l_str,p_delim,1,p_level);
1805 IF l_st = 0 THEN -- Searching beyond
1806 l_st := InStr(l_str,p_delim,-1,2);
1807 l_end := InStr(l_str,p_delim,-1,1);
1808 RETURN SubStr(l_str,l_st + l_srch_width, l_end - l_st - l_srch_width);
1809 ELSE -- First delimiter is found
1810 l_end := InStr(l_str,p_delim,1,p_level + 1);
1811 IF l_end = 0 THEN --We are the end of string return the last node
1812 l_end := l_st;
1813 l_st := InStr(l_str,p_delim,1,p_level-1);
1814 RETURN SubStr(l_str,l_st + l_srch_width, l_end - l_st - l_srch_width);
1815 ELSE
1816 RETURN SubStr(l_str,l_st + l_srch_width, l_end - l_st - l_srch_width);
1817 END IF;
1818 END IF;
1819 END IF;
1820 END split_category_codes;
1821
1822 -- This Procedure Denormalizes the Product Catalog Hierarchy into a separate denorm table
1823 -- [ENI_ICAT_CDENORM_HIERARCHIES]. It is designed to support SBA/OBIEE requirements.
1824 -- The program is designed to flatten the hierarchy for levels raning between 5 and 10.
1825 -- The number of levels to denormalize is dynamic and is governed by a profile value.
1826 -- Currently it only supports FULL REFRESH.
1827 PROCEDURE LOAD_OBIEE_HIERARCHY(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2) IS
1828 err VARCHAR2(2000);
1829 ret VARCHAR2(100);
1830 l_count NUMBER := 0;
1831 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1832 l_conc_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
1833 l_prog_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
1834 l_conc_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
1835 l_hrchy_enabled VARCHAR2(1);
1836 l_struct_id NUMBER;
1837 l_levels_to_flatten NUMBER := 5;
1838 l_sql VARCHAR2(4000);
1839 l_product_catalog MTL_CATEGORY_SETS_TL.CATEGORY_SET_NAME%TYPE;
1840
1841 BEGIN
1842 retcode := 0;
1843 FND_FILE.PUT_LINE(FND_FILE.LOG,'OBIEE Denorm table Initial Load Start');
1844
1845 -- Finding whether Hierarchy is enabled or not
1846 BEGIN
1847 SELECT HIERARCHY_ENABLED, STRUCTURE_ID INTO l_hrchy_enabled, l_struct_id
1848 FROM MTL_CATEGORY_SETS_B
1849 WHERE CATEGORY_SET_ID = g_catset_id
1850 AND HIERARCHY_ENABLED = 'Y';
1851 EXCEPTION WHEN NO_DATA_FOUND THEN
1852 FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Category Set associated with Product Reporting functional area');
1853 FND_FILE.PUT_LINE(FND_FILE.LOG,'- Or the Category set is not enabled for hierarchies');
1854 RAISE;
1855 END;
1856
1857 BEGIN
1858 SELECT csvl.category_set_name
1859 INTO l_product_catalog
1860 FROM mtl_default_category_sets mdcs
1861 ,mtl_category_sets_vl csvl
1862 WHERE csvl.category_set_id = mdcs.category_set_id AND mdcs.functional_area_id=11;
1863 EXCEPTION WHEN OTHERS THEN
1864 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception while searching for functional area 11');
1865 RAISE;
1866 END;
1867
1868 --Truncate the table first [INITIAL LOAD]
1869 FND_FILE.PUT_LINE(FND_FILE.LOG,'Truncating the DENORM table');
1870
1871 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_tab_schema || '.ENI_ICAT_CDENORM_HIERARCHIES';
1872
1873 --Fetch the profile value for No of levels to flatten
1874 fnd_profile.get('ENI_ICAT_DENORM_LEVEL', l_levels_to_flatten);
1875
1876 IF l_levels_to_flatten IS NULL or l_levels_to_flatten > 10 or l_levels_to_flatten < 5 THEN
1877 FND_FILE.PUT_LINE(FND_FILE.LOG,'Profile [ENI: SBA/OBIEE LEVELS IN HIERARCHY] has incorrect value of: [' || l_levels_to_flatten || ']');
1878 FND_FILE.PUT_LINE(FND_FILE.LOG,'Setting the denormalisation to default level of 5');
1879 l_levels_to_flatten := 5;
1880 END IF;
1881
1882 --Start populating the denorm table
1883 FND_FILE.PUT_LINE(FND_FILE.LOG,'Denormalizing catalog [' || l_product_catalog || '] to ' || l_levels_to_flatten || ' levels.');
1884 FND_FILE.PUT_LINE(FND_FILE.LOG,'Denormalization is Row + Column flattening');
1885
1886 l_sql :=
1887 'INSERT INTO eni_icat_cdenorm_hierarchies ( ' ||
1888 ' category_id_level1 ' ||
1889 ' ,category_id_level2 ' ||
1890 ' ,category_id_level3 ' ||
1891 ' ,category_id_level4 ' ||
1892 ' ,category_id_level5 ';
1893
1894 --Add category_id_level* columns according to the profile value selected
1895 FOR i IN 6..l_levels_to_flatten
1896 LOOP
1897 l_sql := l_sql || ' ,category_id_level' || i;
1898 END LOOP;
1899
1900
1901 l_sql := l_sql ||
1902 ' ,leaf_category_id ' ||
1903 ' ,created_by ' ||
1904 ' ,creation_date ' ||
1905 ' ,last_updated_by ' ||
1906 ' ,last_update_date ' ||
1907 ' ,last_update_login ' ||
1908 ' ,request_id ' ||
1909 ' ,program_application_id ' ||
1910 ' ,program_update_date' ||
1911 ' ,program_id) ';
1912
1913 --Start of SELECT clause
1914 l_sql := l_sql ||
1915 '( SELECT ' ||
1916 ' ENI_DENORM_HRCHY.split_category_codes(catstr,1,''' || g_delimiter || ''') ' ||
1917 ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,2,''' || g_delimiter || ''') ' ||
1918 ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,3,''' || g_delimiter || ''') ' ||
1919 ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,4,''' || g_delimiter || ''') ' ||
1920 ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,5,''' || g_delimiter || ''') ';
1921
1922 --Add category_id_level* columns according to the profile value selected
1923 FOR i IN 6..l_levels_to_flatten
1924 LOOP
1925 l_sql := l_sql || ' ,ENI_DENORM_HRCHY.split_category_codes(catstr,' || i || ',''' || g_delimiter || ''') ';
1926 END LOOP;
1927
1928 l_sql := l_sql ||
1929 ' ,category_id ' ||
1930 ' ,:cr_by ' ||
1931 ' ,:cr_date ' ||
1932 ' ,:upd_by ' ||
1933 ' ,:l_upd_date ' ||
1934 ' ,:upd_login ' ||
1935 ' ,:l_conc_request_id ' ||
1936 ' ,:l_prog_appl_id ' ||
1937 ' ,:p_upd_date ' ||
1938 ' ,:l_conc_program_id ' ||
1939 ' FROM ' ||
1940 ' (SELECT (sys_connect_by_path(vcats.category_id,''' || g_delimiter ||
1941 ''') ||''' || g_delimiter || ''') catstr ' ||
1942 ' , vcats.category_id' ||
1943 ' FROM MTL_CATEGORY_SET_VALID_CATS vcats ' ||
1944 ' WHERE CATEGORY_SET_ID = :g_catset_id ' ||
1945 '/* AND CATEGORY_ID NOT IN (SELECT ' ||
1946 ' Nvl(vcats1.PARENT_CATEGORY_ID,-99) FROM MTL_CATEGORY_SET_VALID_CATS vcats1 WHERE vcats1.CATEGORY_SET_ID = :g_catset_id2) */' ||
1947 ' START WITH PARENT_CATEGORY_ID IS NULL ' ||
1948 ' AND CATEGORY_SET_ID = :g_catset_id3 ' ||
1949 ' CONNECT BY PRIOR CATEGORY_ID = PARENT_CATEGORY_ID ' ||
1950 ' AND PRIOR CATEGORY_SET_ID = CATEGORY_SET_ID ))' ;
1951 -- The NOT IN condition eliminates the row flattened rows from the result
1952 -- Bug 5525229 - commented the NOT IN condition
1953
1954 EXECUTE IMMEDIATE l_sql
1955 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
1956 ,g_catset_id, g_catset_id;
1957
1958 IF SQL%ROWCOUNT > 0 THEN
1959 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserted [' || SQL%ROWCOUNT ||'] rows into denorm table.');
1960 ELSE
1961 RAISE NO_DATA_FOUND;
1962 END IF;
1963
1964 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert the UNASSIGNED product category row.');
1965
1966 INSERT INTO eni_icat_cdenorm_hierarchies (
1967 category_id_level1
1968 ,category_id_level2
1969 ,category_id_level3
1970 ,category_id_level4
1971 ,category_id_level5
1972 ,category_id_level6
1973 ,category_id_level7
1974 ,category_id_level8
1975 ,category_id_level9
1976 ,category_id_level10
1977 ,leaf_category_id
1978 ,created_by
1979 ,creation_date
1980 ,last_updated_by
1981 ,last_update_date
1982 ,last_update_login
1983 ,request_id
1984 ,program_application_id
1985 ,program_update_date
1986 ,program_id)
1987 VALUES (
1988 -1,-1,-1,-1,-1,-1,-1,-1,-1,-1
1989 ,-1
1990 ,l_user_id
1991 ,SYSDATE
1992 ,l_user_id
1993 ,SYSDATE
1994 ,l_user_id
1995 ,l_conc_request_id
1996 ,l_prog_appl_id
1997 ,SYSDATE
1998 ,l_conc_program_id
1999 );
2000
2001 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Gathering statistics on table: ENI_ICAT_CDENORM_HIERARCHIES ');
2002 FND_STATS.gather_table_stats (ownname=>g_tab_schema, tabname=>'ENI_ICAT_CDENORM_HIERARCHIES');
2003
2004 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Denorm table Initial Load completed successfully');
2005
2006 COMMIT;
2007 EXCEPTION
2008 WHEN NO_DATA_FOUND THEN
2009
2010 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: No Data Found. Transaction will be rolled back');
2011 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Catalog [' || l_product_catalog || '] does not have any categories associated to it.');
2012 errbuf := SQLERRM || ' Catalog [' || l_product_catalog || '] does not have any categories associated to it.';
2013 retcode := 1;
2014 ROLLBACK;
2015 WHEN OTHERS THEN
2016 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2017 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'ENI_DENORM_HRCHY.LOAD_OBIEE_HIERARCHY', 'Error: ' ||
2018 sqlerrm || ' .Transaction will be rolled back');
2019 end if;
2020 errbuf := 'Error :' || sqlerrm;
2021 retcode := 2;
2022 ROLLBACK;
2023 END LOAD_OBIEE_HIERARCHY;
2024
2025 END ENI_DENORM_HRCHY;