[Home] [Help]
PACKAGE BODY: APPS.HZ_CUST_CLASS_DENORM
Source
1 PACKAGE BODY HZ_CUST_CLASS_DENORM AS
2 /* $Header: ARHCLDPB.pls 120.11 2005/10/30 04:17:50 appldev noship $ */
3
4 --
5 -- HISTORY
6 -- 05/10/2002 AWU Created
7 -- 06/20/2002 AWU Changed nvl statement into two seperate statements
8 -- for better performance
9 -- 12/23/2003 Ramesh Ch Bug No:3335143.Removed the trace procedure and its calls.
10 -- 01/22/2004 Rajib R B Bug No:3330144.Modified procedure insert_class_codes.
11 -- Removed the check for ALLOW_MULTI_PARENT_FLAG='N'.
12 -- 01/07/2004 V.Ravichan Bug No:3735880. Modified Main() to comply with GSCC standards.
13 -- 07/27/2004 Rajib R B Bug No:2657352. Modified the sequels which update
14 -- selectable_flag of hz_class_code_denorm in procedure
15 -- insert_class_codes.
16
17 PROCEDURE Debug_Message(
18 p_msg_level IN NUMBER,
19 -- p_app_name IN VARCHAR2 := 'AR',
20 p_msg IN VARCHAR2)
21 IS
22 l_length NUMBER;
23 l_start NUMBER := 1;
24 l_substring VARCHAR2(50);
25 BEGIN
26 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level)
27 THEN
28 /*
29 l_length := lengthb(p_msg);
30
31 -- FND_MESSAGE doesn't allow message name to be over 30 chars
32 -- chop message name if length > 30
33 WHILE l_length > 30 LOOP
34 l_substring := substrb(p_msg, l_start, 30);
35
36 FND_MESSAGE.Set_Name('AR', l_substring);
37 -- FND_MESSAGE.Set_Name(p_app_name, l_substring);
38 l_start := l_start + 30;
39 l_length := l_length - 30;
40 FND_MSG_PUB.Add;
41 END LOOP;
42
43 l_substring := substrb(p_msg, l_start);
44 FND_MESSAGE.Set_Name('AR', l_substring);
45 -- dbms_output.put_line('l_substring: ' || l_substring);
46 -- FND_MESSAGE.Set_Name(p_app_name, p_msg);
47 FND_MSG_PUB.Add;
48 */
49 l_length := lengthb(p_msg);
50
51 -- FND_MESSAGE doesn't allow application name to be over 30 chars
52 -- chop message name if length > 30
53 IF l_length > 30
54 THEN
55 l_substring := substrb(p_msg, l_start, 30);
56 FND_MESSAGE.Set_Name('AR', l_substring);
57 -- FND_MESSAGE.Set_Name(l_substring, '');
58 ELSE
59 FND_MESSAGE.Set_Name('AR', p_msg);
60 -- FND_MESSAGE.Set_Name(p_msg, '');
61 END IF;
62
63 FND_MSG_PUB.Add;
64 END IF;
65 END Debug_Message;
66
67
68 PROCEDURE write_log(p_debug_source NUMBER, p_fpt number, p_mssg varchar2) IS
69 BEGIN
70 IF p_debug_source = G_DEBUG_CONCURRENT THEN
71 -- p_fpt (1,2)?(log : output)
72 FND_FILE.put(p_fpt, p_mssg);
73 FND_FILE.NEW_LINE(p_fpt, 1);
74 -- If p_fpt == 2 and debug flag then also write to log file
75 IF p_fpt = 2 And G_Debug THEN
76 FND_FILE.put(1, p_mssg);
77 FND_FILE.NEW_LINE(1, 1);
78 END IF;
79 END IF;
80
81 IF G_Debug AND p_debug_source = G_DEBUG_TRIGGER THEN
82 -- Write debug message to message stack
83 Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, p_mssg);
84 END IF; -- G_Debug
85
86 EXCEPTION
87 WHEN OTHERS THEN
88 NULL;
89 END Write_Log;
90
91 PROCEDURE rebuild_intermedia_index
92 is
93 l_bool BOOLEAN;
94 l_status VARCHAR2(255);
95 l_index_owner VARCHAR2(255);
96 l_tmp VARCHAR2(2000);
97 begin
98 l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_index_owner);
99 if l_bool then
100 Write_Log(G_DEBUG_CONCURRENT, 1,'Intermedia index being re-built...');
101 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||'.hz_class_code_denorm_t1 REBUILD online parameters (''sync'')';
102 Write_Log(G_DEBUG_CONCURRENT, 1,'Intermedia index rebuilt on column concat_class_code_meaning for table HZ_CLASS_CODE_DENORM.');
103 end if;
104 end rebuild_intermedia_index;
105
106
107 procedure insert_class_codes(ERRBUF OUT NOCOPY Varchar2,
108 RETCODE OUT NOCOPY Varchar2,
109 p_class_category in varchar2) IS
110
111 BEGIN
112
113 RETCODE := 0;
114
115 if p_class_category is null
116 then
117 -- insert first level nodes
118
119 INSERT INTO HZ_CLASS_CODE_DENORM (
120 CLASS_CATEGORY,
121 CLASS_CODE,
122 CLASS_CODE_MEANING,
123 CLASS_CODE_DESCRIPTION,
124 LANGUAGE,
125 CONCAT_CLASS_CODE,
126 CONCAT_CLASS_CODE_MEANING,
127 CODE_LEVEL,
128 START_DATE_ACTIVE,
129 END_DATE_ACTIVE,
130 ENABLED_FLAG,
131 SELECTABLE_FLAG,
132 CREATED_BY,
133 CREATION_DATE,
134 LAST_UPDATED_BY,
135 LAST_UPDATE_LOGIN,
136 LAST_UPDATE_DATE,
137 REQUEST_ID,
138 PROGRAM_ID,
139 PROGRAM_APPLICATION_ID,
140 PROGRAM_UPDATE_DATE
141 )
142 SELECT
143 CC.CLASS_CATEGORY,
144 LV.LOOKUP_CODE,
145 LV.MEANING ,
146 LV.DESCRIPTION,
147 LT.LANGUAGE,
148 LV.LOOKUP_CODE,
149 LV.MEANING ,
150 1,
151 LV.START_DATE_ACTIVE,
152 LV.END_DATE_ACTIVE,
153 LV.ENABLED_FLAG,
154 'Y',
155 NVL(FND_GLOBAL.USER_ID,-1),
156 SYSDATE,
157 NVL(FND_GLOBAL.USER_ID,-1),
158 NVL(FND_GLOBAL.LOGIN_ID,-1),
159 SYSDATE,
160 FND_GLOBAL.CONC_REQUEST_ID,
161 FND_GLOBAL.CONC_PROGRAM_ID,
162 FND_GLOBAL.PROG_APPL_ID,
163 SYSDATE
164 FROM
165 FND_LOOKUP_TYPES_TL LT,
166 FND_LOOKUP_VALUES LV,
167 HZ_CLASS_CATEGORIES CC,
168 HZ_CLASS_CATEGORY_USES CCU
169 WHERE LT.LOOKUP_TYPE = CC.CLASS_CATEGORY
170 AND LT.VIEW_APPLICATION_ID = 222
171 AND LV.VIEW_APPLICATION_ID = 222
172 AND CC.CLASS_CATEGORY = CCU.CLASS_CATEGORY
173 AND LV.LOOKUP_TYPE = LT.LOOKUP_TYPE
174 AND LT.LANGUAGE = LV.LANGUAGE
175 --AND CC.ALLOW_MULTI_PARENT_FLAG = 'N'
176 AND CCU.OWNER_TABLE='HZ_PARTIES'
177 AND NOT EXISTS(
178 SELECT 'X'
179 FROM HZ_CLASS_CODE_RELATIONS CCR
180 WHERE LV.LOOKUP_CODE = CCR.SUB_CLASS_CODE
181 AND CCR.CLASS_CATEGORY = LT.LOOKUP_TYPE
182 AND sysdate between ccr.start_date_active and nvl(ccr.end_date_active,sysdate));
183
184 --Loop insert nodes in increasing level order starting from level 2 exit when no data found
185 FOR I IN 2 ..HZ_CUST_CLASS_DENORM.G_CODE_LEVEL LOOP
186 BEGIN
187
188 INSERT INTO HZ_CLASS_CODE_DENORM (
189 CLASS_CATEGORY,
190 CLASS_CODE,
191 CLASS_CODE_MEANING,
192 CLASS_CODE_DESCRIPTION,
193 LANGUAGE,
194 CONCAT_CLASS_CODE,
195 CONCAT_CLASS_CODE_MEANING,
196 CODE_LEVEL,
197 START_DATE_ACTIVE,
198 END_DATE_ACTIVE,
199 ENABLED_FLAG,
200 SELECTABLE_FLAG,
201 CREATED_BY,
202 CREATION_DATE,
203 LAST_UPDATED_BY,
204 LAST_UPDATE_LOGIN,
205 LAST_UPDATE_DATE,
206 REQUEST_ID,
207 PROGRAM_ID,
208 PROGRAM_APPLICATION_ID,
209 PROGRAM_UPDATE_DATE
210 )
211 SELECT
212 CC.CLASS_CATEGORY,
213 CCR.SUB_CLASS_CODE,
214 LV.MEANING,
215 LV.DESCRIPTION,
216 LT.LANGUAGE,
217 DENORM.CONCAT_CLASS_CODE||NVL(CC.DELIMITER,'/')||CCR.SUB_CLASS_CODE,
218 DENORM.CONCAT_CLASS_CODE_MEANING||NVL(CC.DELIMITER,'/')||LV.MEANING,
219 i,
220 LV.START_DATE_ACTIVE,
221 LV.END_DATE_ACTIVE,
222 LV.ENABLED_FLAG,
223 'Y',
224 NVL(FND_GLOBAL.USER_ID,-1),
225 SYSDATE,
226 NVL(FND_GLOBAL.USER_ID,-1),
227 NVL(FND_GLOBAL.LOGIN_ID,-1),
228 SYSDATE,
229 FND_GLOBAL.CONC_REQUEST_ID,
230 FND_GLOBAL.CONC_PROGRAM_ID,
231 FND_GLOBAL.PROG_APPL_ID,
232 SYSDATE
233 FROM
234 FND_LOOKUP_TYPES_TL LT,
235 FND_LOOKUP_VALUES LV,
236 HZ_CLASS_CATEGORIES CC,
237 HZ_CLASS_CATEGORY_USES CCU,
238 HZ_CLASS_CODE_RELATIONS CCR,
239 HZ_CLASS_CODE_DENORM DENORM
240 WHERE LT.LOOKUP_TYPE = CC.CLASS_CATEGORY
241 AND LT.VIEW_APPLICATION_ID = 222
242 AND LV.VIEW_APPLICATION_ID = 222
243 AND CC.CLASS_CATEGORY = CCU.CLASS_CATEGORY
244 AND DENORM.CLASS_CATEGORY = CCR.CLASS_CATEGORY
245 AND CCU.CLASS_CATEGORY = CCR.CLASS_CATEGORY
246 AND LV.LOOKUP_TYPE = LT.LOOKUP_TYPE
247 AND LT.LANGUAGE = LV.LANGUAGE
248 AND DENORM.LANGUAGE = LT.LANGUAGE
249 --AND CC.ALLOW_MULTI_PARENT_FLAG = 'N'
250 AND CCU.OWNER_TABLE='HZ_PARTIES'
251 AND DENORM.CLASS_CODE = CCR.CLASS_CODE
252 AND CCR.SUB_CLASS_CODE = LV.LOOKUP_CODE
253 AND sysdate between ccr.start_date_active and nvl(ccr.end_date_active,sysdate)
254 AND DENORM.CODE_LEVEL = i-1
255 UNION
256 SELECT
257 CC.CLASS_CATEGORY,
258 CCR.SUB_CLASS_CODE,
259 LV.MEANING,
260 LV.DESCRIPTION,
261 LT.LANGUAGE,
262 DENORM.CONCAT_CLASS_CODE||NVL(CC.DELIMITER,'/')||CCR.SUB_CLASS_CODE,
263 DENORM.CONCAT_CLASS_CODE_MEANING||NVL(CC.DELIMITER,'/')||LV.MEANING,
264 i,
265 LV.START_DATE_ACTIVE,
266 LV.END_DATE_ACTIVE,
267 LV.ENABLED_FLAG,
268 'Y',
269 NVL(FND_GLOBAL.USER_ID,-1),
270 SYSDATE,
271 NVL(FND_GLOBAL.USER_ID,-1),
272 NVL(FND_GLOBAL.LOGIN_ID,-1),
273 SYSDATE,
274 FND_GLOBAL.CONC_REQUEST_ID,
275 FND_GLOBAL.CONC_PROGRAM_ID,
276 FND_GLOBAL.PROG_APPL_ID,
277 SYSDATE
278 FROM
279 FND_LOOKUP_TYPES_TL LT,
280 FND_LOOKUP_VALUES LV,
281 HZ_CLASS_CATEGORIES CC,
282 HZ_CLASS_CATEGORY_USES CCU,
283 HZ_CLASS_CODE_RELATIONS CCR,
284 HZ_CLASS_CODE_DENORM DENORM
285 WHERE LT.LOOKUP_TYPE = CC.CLASS_CATEGORY
286 AND LT.VIEW_APPLICATION_ID = 222
287 AND LV.VIEW_APPLICATION_ID = 222
288 AND CC.CLASS_CATEGORY = CCU.CLASS_CATEGORY
289 AND DENORM.CLASS_CATEGORY = CCR.CLASS_CATEGORY
290 AND CCU.CLASS_CATEGORY = CCR.CLASS_CATEGORY
291 AND LV.LOOKUP_TYPE = LT.LOOKUP_TYPE
292 AND LT.LANGUAGE = LV.LANGUAGE
293 AND DENORM.LANGUAGE = LT.LANGUAGE
294 --AND CC.ALLOW_MULTI_PARENT_FLAG = 'N'
295 AND CCU.OWNER_TABLE='HZ_PARTIES'
296 AND DENORM.CLASS_CODE = CCR.CLASS_CODE
297 AND CCR.SUB_CLASS_CODE = LV.LOOKUP_CODE
298 AND sysdate between ccr.start_date_active and nvl(ccr.end_date_active,sysdate)
299 AND DENORM.CODE_LEVEL = i -1;
300
301
302 EXCEPTION
303 WHEN NO_DATA_FOUND THEN EXIT;
304 END;
305 END LOOP;
306
307
308 -- set selectable_flag based on allow_leaf_node_only_flag
309
310 /* Bug 2657352. Removed hz_class_code_relations rel1 and fnd_lookup_values lv from the join.
311 * Furthermore considered the date range of the relationships in
312 * hz_class_code_relations
313 *
314 * update hz_class_code_denorm denorm
315 * set selectable_flag ='N'
316 * where exists (select 'x'
317 * from hz_class_code_relations rel1,
318 * hz_class_code_relations rel2,
319 * fnd_lookup_values lv,
320 * hz_class_categories cc
321 * where lv.lookup_type = denorm.class_category
322 * and lv.VIEW_APPLICATION_ID = 222
323 * and denorm.class_category= rel1.class_category
324 * and denorm.class_category= rel2.class_category
325 * and cc.class_category = denorm.class_category
326 * and (rel1.sub_class_code = denorm.class_code
327 * or lv.lookup_code = rel1.sub_class_code)
328 * and rel2.class_code = denorm.class_code
329 * and cc.allow_leaf_node_only_flag = 'Y');
330 */
331
332 UPDATE hz_class_code_denorm denorm
333 SET selectable_flag ='N'
334 WHERE EXISTS
335 (SELECT 'X'
336 FROM hz_class_code_relations rel1,
337 hz_class_categories cc
338 WHERE denorm.class_category = rel1.class_category AND
339 cc.class_category = denorm.class_category AND
340 rel1.class_code = denorm.class_code AND
341 SYSDATE >= rel1.start_date_active AND
342 SYSDATE <= NVL( rel1.end_date_active , SYSDATE + 1) AND
343 cc.allow_leaf_node_only_flag = 'Y');
344
345
346 -- Set frozen_flag to 'Y' - means no dirty data
347 update hz_class_categories
348 set frozen_flag = 'Y'
349 where (frozen_flag = 'N' or frozen_flag is null);
350
351 else -- p_class_category is passed in
352
353 -- insert first level nodes
354
355 INSERT INTO HZ_CLASS_CODE_DENORM (
356 CLASS_CATEGORY,
357 CLASS_CODE,
358 CLASS_CODE_MEANING,
359 CLASS_CODE_DESCRIPTION,
360 LANGUAGE,
361 CONCAT_CLASS_CODE,
362 CONCAT_CLASS_CODE_MEANING,
363 CODE_LEVEL,
364 START_DATE_ACTIVE,
365 END_DATE_ACTIVE,
366 ENABLED_FLAG,
367 SELECTABLE_FLAG,
368 CREATED_BY,
369 CREATION_DATE,
370 LAST_UPDATED_BY,
371 LAST_UPDATE_LOGIN,
372 LAST_UPDATE_DATE,
373 REQUEST_ID,
374 PROGRAM_ID,
375 PROGRAM_APPLICATION_ID,
376 PROGRAM_UPDATE_DATE
377 )
378 SELECT
379 CC.CLASS_CATEGORY,
380 LV.LOOKUP_CODE,
381 LV.MEANING ,
382 LV.DESCRIPTION,
383 LT.LANGUAGE,
384 LV.LOOKUP_CODE,
385 LV.MEANING ,
386 1,
387 LV.START_DATE_ACTIVE,
388 LV.END_DATE_ACTIVE,
389 LV.ENABLED_FLAG,
390 'Y',
391 NVL(FND_GLOBAL.USER_ID,-1),
392 SYSDATE,
393 NVL(FND_GLOBAL.USER_ID,-1),
394 NVL(FND_GLOBAL.LOGIN_ID,-1),
395 SYSDATE,
396 FND_GLOBAL.CONC_REQUEST_ID,
397 FND_GLOBAL.CONC_PROGRAM_ID,
398 FND_GLOBAL.PROG_APPL_ID,
399 SYSDATE
400 FROM
401 FND_LOOKUP_TYPES_TL LT,
402 FND_LOOKUP_VALUES LV,
403 HZ_CLASS_CATEGORIES CC,
404 HZ_CLASS_CATEGORY_USES CCU
405 WHERE LT.LOOKUP_TYPE = CC.CLASS_CATEGORY
406 AND LT.VIEW_APPLICATION_ID = 222
407 AND LV.VIEW_APPLICATION_ID = 222
408 AND CC.CLASS_CATEGORY = CCU.CLASS_CATEGORY
409 AND LV.LOOKUP_TYPE = LT.LOOKUP_TYPE
410 AND LT.LANGUAGE = LV.LANGUAGE
411 --AND CC.ALLOW_MULTI_PARENT_FLAG = 'N'
412 AND CCU.OWNER_TABLE='HZ_PARTIES'
413 AND CC.CLASS_CATEGORY = P_CLASS_CATEGORY
414 AND NOT EXISTS(
415 SELECT 'X'
416 FROM HZ_CLASS_CODE_RELATIONS CCR
417 WHERE LV.LOOKUP_CODE = CCR.SUB_CLASS_CODE
418 AND CCR.CLASS_CATEGORY = LT.LOOKUP_TYPE
419 AND sysdate between ccr.start_date_active and nvl(ccr.end_date_active,sysdate));
420
421
422 --Loop insert nodes in increasing level order starting from level 2 exit when no data found
423 FOR I IN 2 ..HZ_CUST_CLASS_DENORM.G_CODE_LEVEL LOOP
424 BEGIN
425
426 INSERT INTO HZ_CLASS_CODE_DENORM (
427 CLASS_CATEGORY,
428 CLASS_CODE,
429 CLASS_CODE_MEANING,
430 CLASS_CODE_DESCRIPTION,
431 LANGUAGE,
432 CONCAT_CLASS_CODE,
433 CONCAT_CLASS_CODE_MEANING,
434 CODE_LEVEL,
435 START_DATE_ACTIVE,
436 END_DATE_ACTIVE,
437 ENABLED_FLAG,
438 SELECTABLE_FLAG,
439 CREATED_BY,
440 CREATION_DATE,
441 LAST_UPDATED_BY,
442 LAST_UPDATE_LOGIN,
443 LAST_UPDATE_DATE,
444 REQUEST_ID,
445 PROGRAM_ID,
446 PROGRAM_APPLICATION_ID,
447 PROGRAM_UPDATE_DATE
448 )
449 SELECT
450 CC.CLASS_CATEGORY,
451 CCR.SUB_CLASS_CODE,
452 LV.MEANING,
453 LV.DESCRIPTION,
454 LT.LANGUAGE,
455 DENORM.CONCAT_CLASS_CODE||NVL(CC.DELIMITER,'/')||CCR.SUB_CLASS_CODE,
456 DENORM.CONCAT_CLASS_CODE_MEANING||NVL(CC.DELIMITER,'/')||LV.MEANING,
457 i,
458 LV.START_DATE_ACTIVE,
459 LV.END_DATE_ACTIVE,
460 LV.ENABLED_FLAG,
461 'Y',
462 NVL(FND_GLOBAL.USER_ID,-1),
463 SYSDATE,
464 NVL(FND_GLOBAL.USER_ID,-1),
465 NVL(FND_GLOBAL.LOGIN_ID,-1),
466 SYSDATE,
467 FND_GLOBAL.CONC_REQUEST_ID,
468 FND_GLOBAL.CONC_PROGRAM_ID,
469 FND_GLOBAL.PROG_APPL_ID,
470 SYSDATE
471 FROM
472 FND_LOOKUP_TYPES_TL LT,
473 FND_LOOKUP_VALUES LV,
474 HZ_CLASS_CATEGORIES CC,
475 HZ_CLASS_CATEGORY_USES CCU,
476 HZ_CLASS_CODE_RELATIONS CCR,
477 HZ_CLASS_CODE_DENORM DENORM
478 WHERE LT.LOOKUP_TYPE = CC.CLASS_CATEGORY
479 AND LT.VIEW_APPLICATION_ID = 222
480 AND LV.VIEW_APPLICATION_ID = 222
481 AND CC.CLASS_CATEGORY = CCU.CLASS_CATEGORY
482 AND DENORM.CLASS_CATEGORY = CCR.CLASS_CATEGORY
483 AND CCU.CLASS_CATEGORY = CCR.CLASS_CATEGORY
484 AND LV.LOOKUP_TYPE = LT.LOOKUP_TYPE
485 AND LT.LANGUAGE = LV.LANGUAGE
486 AND DENORM.LANGUAGE = LT.LANGUAGE
487 --AND CC.ALLOW_MULTI_PARENT_FLAG = 'N'
488 AND CCU.OWNER_TABLE='HZ_PARTIES'
489 AND DENORM.CLASS_CODE = CCR.CLASS_CODE
490 AND CCR.SUB_CLASS_CODE = LV.LOOKUP_CODE
491 AND CC.CLASS_CATEGORY = P_CLASS_CATEGORY
492 AND sysdate between ccr.start_date_active and nvl(ccr.end_date_active,sysdate)
493 AND DENORM.CODE_LEVEL = i-1
494 UNION
495 SELECT
496 CC.CLASS_CATEGORY,
497 CCR.SUB_CLASS_CODE,
498 LV.MEANING,
499 LV.DESCRIPTION,
500 LT.LANGUAGE,
501 DENORM.CONCAT_CLASS_CODE||NVL(CC.DELIMITER,'/')||CCR.SUB_CLASS_CODE,
502 DENORM.CONCAT_CLASS_CODE_MEANING||NVL(CC.DELIMITER,'/')||LV.MEANING,
503 i,
504 LV.START_DATE_ACTIVE,
505 LV.END_DATE_ACTIVE,
506 LV.ENABLED_FLAG,
507 'Y',
508 NVL(FND_GLOBAL.USER_ID,-1),
509 SYSDATE,
510 NVL(FND_GLOBAL.USER_ID,-1),
511 NVL(FND_GLOBAL.LOGIN_ID,-1),
512 SYSDATE,
513 FND_GLOBAL.CONC_REQUEST_ID,
514 FND_GLOBAL.CONC_PROGRAM_ID,
515 FND_GLOBAL.PROG_APPL_ID,
516 SYSDATE
517 FROM
518 FND_LOOKUP_TYPES_TL LT,
519 FND_LOOKUP_VALUES LV,
520 HZ_CLASS_CATEGORIES CC,
521 HZ_CLASS_CATEGORY_USES CCU,
522 HZ_CLASS_CODE_RELATIONS CCR,
523 HZ_CLASS_CODE_DENORM DENORM
524 WHERE LT.LOOKUP_TYPE = CC.CLASS_CATEGORY
525 AND LT.VIEW_APPLICATION_ID = 222
526 AND LV.VIEW_APPLICATION_ID = 222
527 AND CC.CLASS_CATEGORY = CCU.CLASS_CATEGORY
528 AND DENORM.CLASS_CATEGORY = CCR.CLASS_CATEGORY
529 AND CCU.CLASS_CATEGORY = CCR.CLASS_CATEGORY
530 AND LV.LOOKUP_TYPE = LT.LOOKUP_TYPE
531 AND LT.LANGUAGE = LV.LANGUAGE
532 AND DENORM.LANGUAGE = LT.LANGUAGE
533 --AND CC.ALLOW_MULTI_PARENT_FLAG = 'N'
534 AND CCU.OWNER_TABLE='HZ_PARTIES'
535 AND DENORM.CLASS_CODE = CCR.CLASS_CODE
536 AND CCR.SUB_CLASS_CODE = LV.LOOKUP_CODE
537 AND CC.CLASS_CATEGORY = P_CLASS_CATEGORY
538 AND sysdate between ccr.start_date_active and nvl(ccr.end_date_active,sysdate)
539 AND DENORM.CODE_LEVEL = i -1;
540
541
542
543 EXCEPTION
544 WHEN NO_DATA_FOUND THEN EXIT;
545 END;
546 END LOOP;
547
548
549 -- set selectable_flag based on allow_leaf_node_only_flag
550
551 /* Bug 2657352.Removed joins to hz_class_code_relations rel1, fnd_lookup_values lv,
552 * hz_class_categories.
553 * Considered date range in hz_class_code_relations
554 * Performed the update only if allow_leaf_node_flag is 'Y'.
555 * Used an anonymous block so that resources for variable
556 * l_allow_leaf_node_only_flag are released after this block.
557 *
558 * update hz_class_code_denorm denorm
559 * set selectable_flag ='N'
560 * where denorm.class_category = p_class_category
561 * and exists (select 'x'
562 * from hz_class_code_relations rel1,
563 * hz_class_code_relations rel2,
564 * fnd_lookup_values lv,
565 * hz_class_categories cc
566 * where lv.lookup_type = denorm.class_category
567 * and lv.VIEW_APPLICATION_ID = 222
568 * and denorm.class_category= rel1.class_category
569 * and denorm.class_category= rel2.class_category
570 * and cc.class_category = denorm.class_category
571 * AND CC.CLASS_CATEGORY = P_CLASS_CATEGORY
572 * and (rel1.sub_class_code = denorm.class_code
573 * or lv.lookup_code = rel1.sub_class_code)
574 * and rel2.class_code = denorm.class_code
575 * and cc.allow_leaf_node_only_flag = 'Y');
576 */
577
578 DECLARE
579 l_allow_leaf_node_only_flag HZ_CLASS_CATEGORIES.ALLOW_LEAF_NODE_ONLY_FLAG%TYPE;
580 BEGIN
581 SELECT allow_leaf_node_only_flag
582 INTO l_allow_leaf_node_only_flag
583 FROM HZ_CLASS_CATEGORIES
584 WHERE class_category = p_class_category;
585
586 IF (l_allow_leaf_node_only_flag = 'Y')
587 THEN
588 -- set selectable_flag based on allow_leaf_node_only_flag
592 EXISTS
589 UPDATE hz_class_code_denorm denorm
590 SET selectable_flag ='N'
591 WHERE denorm.class_category = p_class_category AND
593 (SELECT 'x'
594 FROM hz_class_code_relations rel1
595 WHERE denorm.class_category = rel1.class_category AND
596 rel1.class_code = denorm.class_code AND
597 SYSDATE >= rel1.start_date_active AND
598 SYSDATE <= NVL(rel1.end_date_active, SYSDATE + 1)
599 );
600 END IF;
601 END;
602
603 -- Set frozen_flag to 'Y' - means no dirty data
604 update hz_class_categories
605 set frozen_flag = 'Y'
606 where class_category = p_class_category
607 and (frozen_flag = 'N' or frozen_flag is null);
608
609 end if;
610
611 IF (RETCODE = 0) THEN
612 COMMIT;
613 rebuild_intermedia_index;
614 END IF;
615
616 EXCEPTION WHEN OTHERS THEN
617 ERRBUF := ERRBUF||sqlerrm;
618 RETCODE := '1';
619 --Write_Log(G_DEBUG_CONCURRENT, 1, 'Error in insert_class_codes: '||SQLCODE);
620 --Write_Log(G_DEBUG_CONCURRENT, 1,substrb(sqlerrm,1,700));
621
622 END insert_class_codes;
623
624
625 Procedure Main(ERRBUF OUT NOCOPY Varchar2,
626 RETCODE OUT NOCOPY Varchar2,
627 p_class_category IN Varchar2,
628 p_debug_mode IN Varchar2,
629 p_trace_mode IN Varchar2) is
630
631 l_count number:=0;
632 l_status Boolean;
633 l_table_name varchar2(30);
634 -- Code added for Bug 3735880 starts here
635 l_bool BOOLEAN;
636 l_status_owner VARCHAR2(255);
637 l_table_owner VARCHAR2(255);
638 l_tmp VARCHAR2(2000);
639 -- Code added for Bug 3735880 ends here
640 begin
641 -- start of savepoint
642 SAVEPOINT main;
643 IF p_debug_mode = 'Y' THEN G_Debug := TRUE; ELSE G_Debug := FALSE; END IF;
644
645 Write_Log(G_DEBUG_CONCURRENT, 1, 'Process began @: ' || to_char(sysdate,'DD-MON-RRRR:HH:MI:SS'));
646
647 RETCODE := 0;
648
649 if p_class_category is null
650 then
651 -- full refresh
652 -- Code added for Bug 3735880 starts here
653 l_bool := fnd_installation.GET_APP_INFO('AR',l_status_owner,l_tmp,l_table_owner);
654 if l_bool then
655 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_table_owner||'.HZ_CLASS_CODE_DENORM REUSE STORAGE';
656 end if;
657 -- Code added for Bug 3735880 ends here
658 else
659 -- only refresh passing in class_category
660 delete from hz_class_code_denorm where class_category = p_class_category;
661 end if;
662
663 IF (RETCODE = 0) THEN
664 insert_class_codes(ERRBUF, RETCODE, p_class_category);
665 COMMIT;
666 END IF;
667
668 IF (nvl(RETCODE,0) <> 0) THEN
669 l_status := fnd_concurrent.set_completion_status('ERROR',ERRBUF);
670 IF l_status = TRUE THEN
671 Write_Log(G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program');
672 END IF;
673 END IF;
674
675 Write_Log(G_DEBUG_CONCURRENT, 1, 'Process Completed @: '||to_char(sysdate,'DD-MON-RRRR:HH:MI:SS'));
676 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
677 ERRBUF := ERRBUF||'Error in HZ_CUST_CLASS_DENORM.Main:'||to_char(sqlcode)||sqlerrm;
678 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR ;
679 Write_Log(G_DEBUG_CONCURRENT, 1,'Error in HZ_CUST_CLASS_DENORM.Main');
680 Write_Log(G_DEBUG_CONCURRENT, 1,sqlerrm);
681 ROLLBACK to main;
682 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
683 IF l_status = TRUE THEN
684 Write_Log(G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program') ;
685 END IF;
686 WHEN OTHERS THEN
687 ERRBUF := ERRBUF||'Error in HZ_CUST_CLASS_DENORM.Main:'||to_char(sqlcode)||sqlerrm;
688 RETCODE := '2';
689 Write_Log(G_DEBUG_CONCURRENT, 1,'Error in HZ_CUST_CLASS_DENORM.Main');
690 Write_Log(G_DEBUG_CONCURRENT, 1,sqlerrm);
691 ROLLBACK to main;
692 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
693 IF l_status = TRUE THEN
694 Write_Log(G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program') ;
695 END IF;
696
697 end Main;
698
699
700 End HZ_CUST_CLASS_DENORM;