[Home] [Help]
PACKAGE BODY: APPS.MTH_EQUIPMENT_PKG
Source
1 PACKAGE BODY MTH_EQUIPMENT_PKG AS
2 /*$Header: mtheqipb.pls 120.12.12020000.5 2012/12/03 11:43:49 mgijare noship $*/
3
4 /*******************************************************************************
5 * Procedure :MTH_LOAD_EQUIP_HIER_DENORM *
6 * Description :This procedure is used for populating equipment *
7 * denorm table *
8 * File Name :---- *
9 * Visibility :Public *
10 * Parameters : *
11 * *
12 * Modification log : *
13 * Author Date Change *
14 * Sanjeev Vellore Ramani 04-Apr-2012 Initial Creation*
15 *******************************************************************************/
16
17 /*This is the parent procedure. This loads data into the hierarchy table and calls functions to validate the data. If the validations
18 are successful, this then calls the populate denorm procedure.*/
19
20 PROCEDURE MTH_LOAD_EQUIP_HIER_DENORM IS
21
22 v_log_date DATE;
23 v_unassigned_val VARCHAR2(30);
24 v_error_code_1 VARCHAR2(4000) DEFAULT NULL;
25 v_error_code_2 VARCHAR2(4000) DEFAULT NULL;
26 v_error_code_3 VARCHAR2(1);
27
28 BEGIN
29 mth_util_pkg.log_msg('MTH_LOAD_EQUIP_HIER_DENORM start', mth_util_pkg.G_DBG_PROC_FUN_START);
30
31 -- Initialize default parameters
32 v_log_date := sysdate;
33 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
34
35 -- validate the group id
36 v_error_code_1 := VALIDATE_GROUP_ID();
37
38 -- validate the parent-child relationship
39
40 v_error_code_2 := VALIDATE_CHILD_PARENT_REL();
41
42 v_error_code_3 := VALIDATE_PARENT_MOVE_DATE('Y');
43
44 IF (v_error_code_3 = 'Y') THEN
45 FND_MESSAGE.SET_NAME('MTH', 'MTH_EQP_DENORM_FORCE_INIT');
46 RAISE_APPLICATION_ERROR(-20002, FND_MESSAGE.GET);
47 END IF;
48
49
50 IF (v_error_code_1 IS NULL AND v_error_code_2 IS NULL ) THEN
51
52 -- Call mth_run_log_pre_load
53 -- mth_util_pkg.mth_run_log_pre_load('MTH_EQUIPMENT_DENORM_D',v_unassigned_val,'INCR',NULL,0,v_log_date);
54 -- Moved to call to inside MTH_POPULATE_EQUIP_DENORM
55
56 -- call the procedure to populate entries into denorm table if the validations are successful
57 MTH_POPULATE_EQUIP_DENORM();
58
59 -- Call mth_run_log_post_load
60 mth_util_pkg.mth_run_log_post_load('MTH_EQUIPMENT_DENORM_D',v_unassigned_val);
61
62 ELSE
63 mth_util_pkg.log_msg('Validations failed. Check log for more details',mth_util_pkg.G_DBG_EXCEPTION);
64 END IF;
65
66 mth_util_pkg.log_msg('MTH_LOAD_EQUIP_HIER_DENORM end', mth_util_pkg.G_DBG_PROC_FUN_END);
67
68 COMMIT;
69 EXCEPTION
70 WHEN OTHERS THEN
71 --Call logging API and then throw exception
72 mth_util_pkg.log_msg('Exception OTHERS in MTH_LOAD_EQUIP_HIER_DENORM', mth_util_pkg.G_DBG_EXCEPTION);
73 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
74 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
75 mth_util_pkg.log_msg('MTH_LOAD_EQUIP_HIER_DENORM end', mth_util_pkg.G_DBG_PROC_FUN_END);
76 ROLLBACK;
77 RAISE;
78 END;
79
80 /*******************************************************************************
81 * Fuction :VALIDATE_GROUP_ID *
82 * Description :This function is used for validating group ID *
83 * File Name :---- *
84 * Visibility :Public *
85 * Parameters : *
86 * *
87 * Modification log : *
88 * Author Date Change *
89 * Sanjeev Vellore Ramani 10-Mar-2012 Initial Creation*
90 *******************************************************************************/
91
92 /* Validates if, for a entity within the hierarchy, the group ids are increasing in increasing order of effective dates*/
93
94 FUNCTION VALIDATE_GROUP_ID RETURN VARCHAR2 IS
95 v_error_code VARCHAR2(4000) DEFAULT NULL;
96 v_error_flag NUMBER DEFAULT 0;
97 c_equip_hierarchy SYS_REFCURSOR ;
98
99 TYPE equip_hierarchy_num_type IS TABLE OF NUMBER;
100 TYPE equip_hierarchy_date_type IS TABLE OF DATE;
101 TYPE equip_hierarchy_rec_type IS RECORD (GROUP_ID equip_hierarchy_num_type,
102 LEVEL_FK_KEY equip_hierarchy_num_type,
103 HIERARCHY_ID equip_hierarchy_num_type,
104 LEVEL_NUM equip_hierarchy_num_type,
105 EFFECTIVE_DATE equip_hierarchy_date_type);
106 r_equip_hierarchy equip_hierarchy_rec_type ;
107
108 v_limit NUMBER :=5000;
109
110 BEGIN
111
112 mth_util_pkg.log_msg('VALIDATE_GROUP_ID start', mth_util_pkg.G_DBG_PROC_FUN_START);
113 OPEN c_equip_hierarchy FOR 'Select NVL(GROUP_ID, 1) GROUP_ID, LEVEL_FK_KEY, HIERARCHY_ID, LEVEL_NUM, EFFECTIVE_DATE
114 FROM MTH_EQUIP_HIERARCHY
115 ORDER BY HIERARCHY_ID, LEVEL_NUM, LEVEL_FK_KEY, EFFECTIVE_DATE ';
116 FETCH c_equip_hierarchy BULK COLLECT INTO r_equip_hierarchy.GROUP_ID,
117 r_equip_hierarchy.LEVEL_FK_KEY,
118 r_equip_hierarchy.HIERARCHY_ID,
119 r_equip_hierarchy.LEVEL_NUM,
120 r_equip_hierarchy.EFFECTIVE_DATE
121 LIMIT v_limit ;
122 FOR i IN r_equip_hierarchy.GROUP_ID.FIRST..r_equip_hierarchy.GROUP_ID.LAST-1 LOOP
123 IF(r_equip_hierarchy.HIERARCHY_ID(i+1) = r_equip_hierarchy.HIERARCHY_ID(i)
124 AND r_equip_hierarchy.LEVEL_NUM(i+1) = r_equip_hierarchy.LEVEL_NUM(i)
125 AND r_equip_hierarchy.LEVEL_FK_KEY(i+1) = r_equip_hierarchy.LEVEL_FK_KEY(i)
126 AND r_equip_hierarchy.EFFECTIVE_DATE(i+1) > r_equip_hierarchy.EFFECTIVE_DATE(i)
127 AND r_equip_hierarchy.GROUP_ID(i+1) < r_equip_hierarchy.GROUP_ID(i))THEN
128 mth_util_pkg.log_msg(r_equip_hierarchy.LEVEL_FK_KEY(i) || ' has failed the validation for group id for the pair ' ||
129 '(' ||r_equip_hierarchy.GROUP_ID(i) || ' , ' ||r_equip_hierarchy.EFFECTIVE_DATE(i) || ')' || ' , ' ||
130 ' (' ||r_equip_hierarchy.GROUP_ID(i+1) || ' , ' ||r_equip_hierarchy.EFFECTIVE_DATE(i+1) || ') ',mth_util_pkg.G_DBG_EXCEPTION);
131 v_error_flag := 1;
132 END IF;
133 END LOOP;
134 CLOSE c_equip_hierarchy ;
135 IF(v_error_flag = 1) THEN
136 v_error_code := v_error_code || ' Data contains invalid group id(s). Please check the log for more details.';
137 END IF;
138 mth_util_pkg.log_msg('VALIDATE_GROUP_ID end', mth_util_pkg.G_DBG_PROC_FUN_END);
139 RETURN v_error_code ;
140
141 --COMMIT;
142 EXCEPTION
143 WHEN OTHERS THEN
144 --Call logging API and then throw exception
145 mth_util_pkg.log_msg('Exception OTHERS in VALIDATE_GROUP_ID', mth_util_pkg.G_DBG_EXCEPTION);
146 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
147 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
148 mth_util_pkg.log_msg('VALIDATE_GROUP_ID end', mth_util_pkg.G_DBG_PROC_FUN_END);
149 ROLLBACK;
150 RAISE;
151
152 END ;
153
154 /*******************************************************************************
155 * Fuction :VALIDATE_CHILD_PARENT_REL *
156 * Description :This function is used for validating parent-child *
157 * relationship *
158 * File Name :---- *
159 * Visibility :Public *
160 * Parameters : *
161 * *
162 * Modification log : *
163 * Author Date Change *
164 * Sanjeev Vellore Ramani 17-Mar-2012 Initial Creation*
165 *******************************************************************************/
166
167 /*Validates the parent child relationship within the hierarchy. If a child has a missing parent, or if the effective dates of the children
168 do not fall within the effective date range of the parent, then these erroneous entries are captured in the log*/
169
170 FUNCTION VALIDATE_CHILD_PARENT_REL RETURN VARCHAR2 IS
171
172 v_error_code VARCHAR2(4000) DEFAULT NULL;
173 v_error_flag_mp NUMBER DEFAULT 0 ;
174 v_error_flag_nep NUMBER DEFAULT 0 ;
175 CURSOR c_equip_hierarchy IS
176 SELECT parent_fk_key,child_level_fk_key,parent_level_fk_key,child_date,parent_date,child_level_num,parent_level_num,parent_level_num_incr
177 FROM (SELECT level_num child_level_num,level_fk_key child_level_fk_key,Min(effective_date) child_date,hierarchy_id,parent_fk_key FROM mth_equip_hierarchy
178 WHERE level_num >1
179 GROUP BY level_num,level_fk_key,hierarchy_id,parent_fk_key ) h_child,
180 (SELECT level_num parent_level_num, (level_num+1) parent_level_num_incr,level_fk_key parent_level_fk_key,Min(effective_date) parent_date ,hierarchy_id FROM mth_equip_hierarchy
181 WHERE level_num <10
182 GROUP BY level_num,level_fk_key,hierarchy_id ) h_parent
183 WHERE
184 h_child.parent_fk_key = h_parent.parent_level_fk_key(+)
185 AND h_child.hierarchy_id = h_parent.hierarchy_id(+)
186 AND h_child.child_level_num = h_parent.parent_level_num_incr(+) ;
187
188 r_equip_hierarchy c_equip_hierarchy%ROWTYPE ;
189
190 BEGIN
191
192 mth_util_pkg.log_msg('VALIDATE_CHILD_PARENT_REL start', mth_util_pkg.G_DBG_PROC_FUN_START);
193
194 OPEN c_equip_hierarchy ;
195 LOOP
196 FETCH c_equip_hierarchy INTO r_equip_hierarchy ;
197 EXIT WHEN c_equip_hierarchy%NOTFOUND ;
198 IF (r_equip_hierarchy.parent_level_fk_key IS NULL)THEN
199 mth_util_pkg.log_msg(' Child ' || r_equip_hierarchy.child_level_fk_key || ' is missing parent ' || r_equip_hierarchy.parent_fk_key,mth_util_pkg.G_DBG_EXCEPTION);
200 v_error_flag_mp := 1;
201 END IF;
202
203 IF (r_equip_hierarchy.parent_date > r_equip_hierarchy.child_date )THEN
204 mth_util_pkg.log_msg(' Child ' || r_equip_hierarchy.child_level_fk_key || ' has non existent parent : ' || r_equip_hierarchy.parent_fk_key,mth_util_pkg.G_DBG_EXCEPTION);
205 v_error_flag_nep := 1;
206 END IF;
207
208 END LOOP;
209 CLOSE c_equip_hierarchy ;
210 IF(v_error_flag_mp = 1) THEN
211 v_error_code := v_error_code || ' Child entities are missing the corresponding parents. Please check the log for more details.';
212 END IF;
213 IF(v_error_flag_nep = 1) THEN
214 v_error_code := v_error_code || ' Child entities are having non existent parents. Please check the log for more details.';
215 END IF;
216 mth_util_pkg.log_msg('VALIDATE_CHILD_PARENT_REL end', mth_util_pkg.G_DBG_PROC_FUN_END);
217 RETURN v_error_code ;
218
219
220 --COMMIT;
221 EXCEPTION
222 WHEN OTHERS THEN
223 --Call logging API and then throw exception
224 mth_util_pkg.log_msg('Exception OTHERS in VALIDATE_CHILD_PARENT_REL', mth_util_pkg.G_DBG_EXCEPTION);
225 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
226 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
227 mth_util_pkg.log_msg('VALIDATE_CHILD_PARENT_REL end', mth_util_pkg.G_DBG_PROC_FUN_END);
228 ROLLBACK;
229 RAISE;
230
231 END ;
232
233 /*******************************************************************************
234 * Fuction :VALIDATE_PARENT_MOVE_DATE *
235 * Description :This function is used for validating the date on *
236 * which an entity is moved from one parent to *
237 * another where such a move is effective on a date *
238 * earlier than or equal to the effective date of one*
239 * or more of its children. *
240 * relationship *
241 * File Name :mtheqipb.pls *
242 * Visibility :Public *
243 * Parameters :p_print_message varchar2 *
244 * *
245 * Modification log : *
246 * Author Date Change *
247 * Saurabh Surendra 05-Nov-2012 Initial Creation *
248 *******************************************************************************/
249
250 FUNCTION VALIDATE_PARENT_MOVE_DATE (p_print_message IN VARCHAR2 DEFAULT NULL)
251 RETURN VARCHAR2 IS
252
253 CURSOR c_equip_hierarchy IS
254 SELECT Count(*) record_count
255 FROM mth_equip_hierarchy h,
256 mth_equipment_denorm_d d,
257 (SELECT Min(from_date) from_date
258 FROM mth_run_log
259 WHERE fact_table = 'MTH_EQUIPMENT_DENORM_D') r
260 WHERE h.creation_date > r.from_date
261 AND h.level_num <= 10
262 AND d.level_num >= h.level_num
263 AND h.level_fk_key = Decode(h.level_num,2,d.level2_level_key,
264 3,d.level3_level_key,
265 4,d.level4_level_key,
266 5,d.level5_level_key,
267 6,d.level6_level_key,
268 7,d.level7_level_key,
269 8,d.level8_level_key,
270 9,d.level9_level_key)
271 AND h.hierarchy_id = Decode(h.level_num,2,d.level2_hierarchy_key,
272 3,d.level3_hierarchy_key,
273 4,d.level4_hierarchy_key,
274 5,d.level5_hierarchy_key,
275 6,d.level6_hierarchy_key,
276 7,d.level7_hierarchy_key,
277 8,d.level8_hierarchy_key,
278 9,d.level9_hierarchy_key)
279 AND h.effective_date <= Decode(d.level_num,
280 3, d.level3_effective_date,
281 4, d.level4_effective_date,
282 5, d.level5_effective_date,
283 6, d.level6_effective_date,
284 7, d.level7_effective_date,
285 8, d.level8_effective_date,
286 9, d.level9_effective_date,
287 10,d.equipment_effective_date);
288
289 CURSOR c_equip_hierarchy_dtl IS
290 SELECT Decode(h.level_num,2,d.level2_hierarchy_key,
291 3,d.level3_hierarchy_key,
295 7,d.level7_hierarchy_key,
292 4,d.level4_hierarchy_key,
293 5,d.level5_hierarchy_key,
294 6,d.level6_hierarchy_key,
296 8,d.level8_hierarchy_key,
297 9,d.level9_hierarchy_key,
298 10,d.equipment_hierarchy_key) hierarchy_id,
299 h.level_num,
300 h.level_fk_key,
301 Decode(h.level_num,2,d.level2_level_name,
302 3,d.level3_level_name,
303 4,d.level4_level_name,
304 5,d.level5_level_name,
305 6,d.level6_level_name,
306 7,d.level7_level_name,
307 8,d.level8_level_name,
308 9,d.level9_level_name,
309 9,d.equipment_level_name) level_name,
310 h.PARENT_FK_KEY,
311 h.effective_date,
312 d.level_num child_level_num,
313 Decode(d.level_num,2,d.level2_level_name,
314 3,d.level3_level_name,
315 4,d.level4_level_name,
316 5,d.level5_level_name,
317 6,d.level6_level_name,
318 7,d.level7_level_name,
319 8,d.level8_level_name,
320 9,d.level9_level_name,
321 10,d.equipment_level_name) child_level_name,
322 Decode(d.level_num,2,d.level2_effective_date,
323 3,d.level3_effective_date,
324 4,d.level4_effective_date,
325 5,d.level5_effective_date,
326 6,d.level6_effective_date,
327 7,d.level7_effective_date,
328 8,d.level8_effective_date,
329 9,d.level9_effective_date,
330 10,d.equipment_effective_date) child_effective_date
331 FROM mth_equip_hierarchy h,
332 mth_equipment_denorm_d d,
333 (SELECT Min(from_date) from_date
334 FROM mth_run_log
335 WHERE fact_table = 'MTH_EQUIPMENT_DENORM_D') r
336 WHERE h.creation_date > r.from_date
337 AND h.level_num <= 10
338 AND d.level_num >= h.level_num
339 AND h.level_fk_key = Decode(h.level_num,2,d.level2_level_key,
340 3,d.level3_level_key,
341 4,d.level4_level_key,
342 5,d.level5_level_key,
343 6,d.level6_level_key,
344 7,d.level7_level_key,
345 8,d.level8_level_key,
346 9,d.level9_level_key)
347 AND h.hierarchy_id = Decode(h.level_num,2,d.level2_hierarchy_key,
348 3,d.level3_hierarchy_key,
349 4,d.level4_hierarchy_key,
350 5,d.level5_hierarchy_key,
351 6,d.level6_hierarchy_key,
352 7,d.level7_hierarchy_key,
353 8,d.level8_hierarchy_key,
354 9,d.level9_hierarchy_key)
355 AND h.effective_date <= Decode(d.level_num,
356 3, d.level3_effective_date,
357 4, d.level4_effective_date,
358 5, d.level5_effective_date,
359 6, d.level6_effective_date,
360 7, d.level7_effective_date,
361 8, d.level8_effective_date,
362 9, d.level9_effective_date,
363 10,d.equipment_effective_date);
364
365 r_equip_hierarchy c_equip_hierarchy%ROWTYPE ;
366 r_equip_hierarchy_dtl c_equip_hierarchy_dtl%ROWTYPE ;
367 is_invalid VARCHAR2(1);
368
369 BEGIN
370
371 mth_util_pkg.log_msg('VALIDATE_PARENT_MOVE_DATE start', mth_util_pkg.G_DBG_PROC_FUN_START);
372 mth_util_pkg.log_msg('p_print_message = ' || p_print_message , mth_util_pkg.G_DBG_PARAM_VAL);
373
374 is_invalid := 'N';
375
376 IF nvl(p_print_message,'N') = 'N' THEN
377 OPEN c_equip_hierarchy;
378 LOOP
379 FETCH c_equip_hierarchy INTO r_equip_hierarchy;
380 EXIT WHEN c_equip_hierarchy%NOTFOUND;
381 IF r_equip_hierarchy.record_count <> 0 THEN
382 is_invalid := 'Y';
383 END IF;
384 END LOOP;
385 ELSE
386 OPEN c_equip_hierarchy_dtl;
387 LOOP
388 FETCH c_equip_hierarchy_dtl INTO r_equip_hierarchy_dtl;
389 EXIT WHEN c_equip_hierarchy_dtl%NOTFOUND;
390 is_invalid := 'Y';
391 mth_util_pkg.log_msg('In hierarhcy with ID ' || r_equip_hierarchy_dtl.HIERARCHY_ID || ' level_num ' ||
392 r_equip_hierarchy_dtl.LEVEL_NUM || ' ' || r_equip_hierarchy_dtl.LEVEL_NAME || '(' ||
393 r_equip_hierarchy_dtl.LEVEL_FK_KEY || ') is moved to parent with key ' || r_equip_hierarchy_dtl.PARENT_FK_KEY ||
394 ' as on ' || to_char(r_equip_hierarchy_dtl.EFFECTIVE_DATE,'DD-MON-YYYY HH24:MI:SS') ||
395 ' while it has a child - ' || r_equip_hierarchy_dtl.CHILD_LEVEL_NAME || ' - at level_num ' ||
396 r_equip_hierarchy_dtl.CHILD_LEVEL_NUM || ' with a later effective date (' ||
397 to_char(r_equip_hierarchy_dtl.CHILD_EFFECTIVE_DATE,'DD-MON-YYYY HH24:MI:SS') || ').', mth_util_pkg.G_DBG_EXCEPTION);
398 END LOOP;
399 END IF;
400
401 mth_util_pkg.log_msg('VALIDATE_PARENT_MOVE_DATE end', mth_util_pkg.G_DBG_PROC_FUN_END);
405 --COMMIT;
402 RETURN is_invalid ;
403
404
406 EXCEPTION
407 WHEN OTHERS THEN
408 --Call logging API and then throw exception
409 mth_util_pkg.log_msg('Exception OTHERS in VALIDATE_PARENT_MOVE_DATE', mth_util_pkg.G_DBG_EXCEPTION);
410 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
411 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
412 mth_util_pkg.log_msg('VALIDATE_PARENT_MOVE_DATE end', mth_util_pkg.G_DBG_PROC_FUN_END);
413 ROLLBACK;
414 RAISE;
415
416 END ;
417
418 /*******************************************************************************
419 * Procedure :MTH_POPULATE_EQUIP_DENORM *
420 * Description :This procedure is used for populating equipment *
421 * denorm table *
422 * File Name :---- *
423 * Visibility :Public *
424 * Parameters : *
425 * *
426 * Modification log : *
427 * Author Date Change *
428 * Sanjeev Vellore Ramani 26-Mar-2012 Initial Creation*
429 *******************************************************************************/
430
431 /*This procedure uses a heuristic query to check if the entities from the hierarchy table need to be moved/inserted. If the previous effective
432 date is NULL, this emplies, this entry does not exist on the Equipment Denorm table, and hence the Insert procedure is called to insert
433 new rows into the denorm table. Else, the Move procedure is called to move the existing entities. */
434
435 PROCEDURE MTH_POPULATE_EQUIP_DENORM IS
436
437 v_log_from_date DATE;
438 v_log_to_date DATE;
439 v_unassigned_val VARCHAR2(30);
440
441
442
443 -- heuristic query to check the previous effective date.
444
445 CURSOR c_equip_hierarchy (p_from_date date, p_to_date date) IS
446 SELECT * from
447 (SELECT hierarchy_id,level_num,level_fk_key,parent_fk_key,level_name,group_id,creation_date,effective_date,Lag(effective_date)
448 over (PARTITION BY hierarchy_id,level_num,level_fk_key
449 ORDER BY group_id ) previous_effective_date,
450 (Lead(effective_date)
451 over (PARTITION BY hierarchy_id,level_num,level_fk_key
452 ORDER BY group_id )) - 1/(24*60*60) expiration_date
453 FROM mth_equip_hierarchy) equip_hierarchy /*,
454 (SELECT
455 DISTINCT
456 MTH_RUN_LOG.FROM_DATE,
457 MTH_RUN_LOG.TO_DATE
458 FROM
459 MTH_RUN_LOG
460 WHERE
461 (MTH_RUN_LOG.FACT_TABLE = 'MTH_EQUIPMENT_DENORM_D')) denorm_run_log */
462 WHERE equip_hierarchy.creation_date > p_from_date
463 AND equip_hierarchy.creation_date <= p_to_date
464 --ORDER BY HIERARCHY_ID, GROUP_ID, LEVEL_NUM ;
465 ORDER BY HIERARCHY_ID, EFFECTIVE_DATE, LEVEL_NUM, GROUP_ID ;
466
467
468 r_equip_hierarchy c_equip_hierarchy%ROWTYPE ;
469
470 BEGIN
471 mth_util_pkg.log_msg('MTH_POPULATE_EQUIP_DENORM start', mth_util_pkg.G_DBG_PROC_FUN_START);
472
473 -- Initialize default parameters
474 v_log_to_date := sysdate;
475 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
476
477 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIPMENT_DENORM_D',v_unassigned_val,'INCR',NULL,0,v_log_to_date);
478 mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIPMENT_DENORM_D',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
479
480
481 open c_equip_hierarchy(v_log_from_date,v_log_to_date);
482 loop
483 fetch c_equip_hierarchy into r_equip_hierarchy ;
484 exit when c_equip_hierarchy%NOTFOUND ;
485 IF(r_equip_hierarchy.previous_effective_date IS NULL)THEN
486
487
488 -- call the procedure to insert entries into the denorm table ,if the previous effective date is null (first entry into the denorm table)
489 MTH_INSERT_EQUIP_DENORM(r_equip_hierarchy.level_fk_key,r_equip_hierarchy.parent_fk_key,r_equip_hierarchy.hierarchy_id,r_equip_hierarchy.level_num,r_equip_hierarchy.effective_date,r_equip_hierarchy.level_name);
490 ELSE
491
492 -- call the procedure to move entries in the denorm table
493 MTH_MOVE_EQUIP_DENORM(r_equip_hierarchy.level_fk_key,r_equip_hierarchy.parent_fk_key,r_equip_hierarchy.hierarchy_id,r_equip_hierarchy.level_num,r_equip_hierarchy.effective_date,r_equip_hierarchy.level_name);
494
495 END IF ;
496 end loop;
497 close c_equip_hierarchy ;
498 mth_util_pkg.log_msg('MTH_POPULATE_EQUIP_DENORM end', mth_util_pkg.G_DBG_PROC_FUN_END);
499
500 --COMMIT;
501 EXCEPTION
502 WHEN OTHERS THEN
503 --Call logging API and then throw exception
504 mth_util_pkg.log_msg('Exception OTHERS in MTH_POPULATE_EQUIP_DENORM', mth_util_pkg.G_DBG_EXCEPTION);
505 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
506 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
507 mth_util_pkg.log_msg('MTH_POPULATE_EQUIP_DENORM end', mth_util_pkg.G_DBG_PROC_FUN_END);
508 ROLLBACK;
509 RAISE;
510
511 END ;
512
513 /*******************************************************************************
514 * Procedure :MTH_INSERT_EQUIP_DENORM *
515 * Description :This procedure is used for populating equipment *
516 * denorm table *
517 * File Name :---- *
518 * Visibility :Public *
519 * Parameters :p_level_fk_key *
523 * p_effective_date *
520 * p_parent_fk_key *
521 * p_hierarchy_id *
522 * p_level_num *
524 * p_level_name *
525 * *
526 * Modification log : *
527 * Author Date Change *
528 * Sanjeev Vellore Ramani 30-Mar-2012 Initial Creation*
529 *******************************************************************************/
530
531 /*This procedure inserts entries into the equipment denorm table. First, the ancestor information already existing for an entity
532 in the denorm table is determined. Then a new row is inserted into the Equipment Denorm table, complete with the ancestor information.*/
533
534 PROCEDURE MTH_INSERT_EQUIP_DENORM(
535 p_level_fk_key IN NUMBER,
536 p_parent_fk_key IN NUMBER,
537 p_hierarchy_id IN NUMBER,
538 p_level_num IN NUMBER,
539 p_effective_date IN DATE,
540 p_level_name IN VARCHAR2
541 ) IS
542
543 v_log_date DATE;
544 v_unassigned_val VARCHAR2(30);
545 v_count NUMBER ;
546 v_dimension_key NUMBER;
547
548 /* pick the ancestors for the specified entry to be inserted into the denorm table. This might pick more than one ancestor row,
549 if the current row's effective date is between "effective date - non null expiration date" range of one of the parent rows*/
550
551 CURSOR c_equip_denorm IS
552 SELECT *
553 FROM MTH_EQUIPMENT_DENORM_D
554 WHERE level_num = p_level_num - 1
555 and level1_hierarchy_key = p_hierarchy_id
556 and ((p_level_num =2 and level1_level_key = p_parent_fk_key and level1_hierarchy_key = p_hierarchy_id)
557
558 OR
559 (p_level_num =3 and level2_level_key = p_parent_fk_key and level2_hierarchy_key = p_hierarchy_id
560 AND p_effective_date <=Nvl(level2_expiration_date,p_effective_date))
561 OR
562 (p_level_num =4 and level3_level_key = p_parent_fk_key and level3_hierarchy_key = p_hierarchy_id
563 AND p_effective_date <=Nvl(level3_expiration_date,p_effective_date))
564
565 OR
566 (p_level_num =5 and level4_level_key = p_parent_fk_key and level4_hierarchy_key = p_hierarchy_id
567 AND p_effective_date <=Nvl(level4_expiration_date,p_effective_date))
568 OR
569 (p_level_num =6 and level5_level_key = p_parent_fk_key and level5_hierarchy_key = p_hierarchy_id
570 AND p_effective_date <=Nvl(level5_expiration_date,p_effective_date))
571
572 OR
573 (p_level_num =7 and level6_level_key = p_parent_fk_key and level6_hierarchy_key = p_hierarchy_id
574 AND p_effective_date <=Nvl(level6_expiration_date,p_effective_date))
575
576 OR
577 (p_level_num =8 and level7_level_key = p_parent_fk_key and level7_hierarchy_key = p_hierarchy_id
578 AND p_effective_date <=Nvl(level7_expiration_date,p_effective_date))
579
580 OR
581 (p_level_num =9 and level8_level_key = p_parent_fk_key and level8_hierarchy_key = p_hierarchy_id
582 AND p_effective_date <=Nvl(level8_expiration_date,p_effective_date))
583
584 OR
585 (p_level_num =10 and level9_level_key = p_parent_fk_key and level9_hierarchy_key = p_hierarchy_id
586 AND p_effective_date <=Nvl(level9_expiration_date,p_effective_date))
587
588 );
589
590 r_equip_denorm c_equip_denorm%ROWTYPE ;
591 v_row MTH_EQUIPMENT_DENORM_D%ROWTYPE ;
592
593
594 BEGIN
595
596 mth_util_pkg.log_msg('MTH_INSERT_EQUIP_DENORM start', mth_util_pkg.G_DBG_PROC_FUN_START);
597 mth_util_pkg.log_msg('p_level_fk_key = ' || p_level_fk_key , mth_util_pkg.G_DBG_PARAM_VAL);
598 mth_util_pkg.log_msg('p_parent_fk_key = ' || p_parent_fk_key , mth_util_pkg.G_DBG_PARAM_VAL);
599 mth_util_pkg.log_msg('p_hierarchy_id = ' || p_hierarchy_id , mth_util_pkg.G_DBG_PARAM_VAL);
600 mth_util_pkg.log_msg('p_level_num = ' || p_level_num , mth_util_pkg.G_DBG_PARAM_VAL);
601 mth_util_pkg.log_msg('p_effective_date = ' || to_char(p_effective_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
602 mth_util_pkg.log_msg('p_level_name = ' || p_level_name , mth_util_pkg.G_DBG_PARAM_VAL);
603
604 -- Initialize default parameters
605 v_log_date := sysdate;
606 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
607 v_count := 0;
608
609 -- load v_row with the ancestors for the specified entry to be inserted into the denorm table
610
611 open c_equip_denorm;
612 loop
613 fetch c_equip_denorm into r_equip_denorm ;
614 /*Ensure that an insert happens even when denorm table is empty and the parent cursor does not return any row*/
615 exit when (c_equip_denorm%NOTFOUND AND v_count > 0) ;
616 if (c_equip_denorm%NOTFOUND) then
617 mth_util_pkg.log_msg('-- c_equip_denorm%NOTFOUND',mth_util_pkg.G_DBG_VAR_VAL);
618 end if;
619
620 IF (p_level_num > 1) THEN
621 v_row.level1_hierarchy_key := r_equip_denorm.level1_hierarchy_key;
622 v_row.level1_id := r_equip_denorm.level1_id;
623 v_row.level1_level_key := r_equip_denorm.level1_level_key;
624 v_row.level1_level_name := r_equip_denorm.level1_level_name;
625 END IF;
626
627 IF (p_level_num > 2) THEN
628 v_row.level2_hierarchy_key := r_equip_denorm.level2_hierarchy_key;
629 v_row.level2_effective_date := r_equip_denorm.level2_effective_date;
630 v_row.level2_expiration_date := r_equip_denorm.level2_expiration_date;
631 v_row.level2_id := r_equip_denorm.level2_id;
632 v_row.level2_level_key := r_equip_denorm.level2_level_key;
633 v_row.level2_parent_key := r_equip_denorm.level2_parent_key;
634 v_row.level2_level_name := r_equip_denorm.level2_level_name;
635
636 END IF;
637
638 IF (p_level_num > 3) THEN
642 v_row.level3_id := r_equip_denorm.level3_id;
639 v_row.level3_hierarchy_key := r_equip_denorm.level3_hierarchy_key;
640 v_row.level3_effective_date := r_equip_denorm.level3_effective_date;
641 v_row.level3_expiration_date := r_equip_denorm.level3_expiration_date;
643 v_row.level3_level_key := r_equip_denorm.level3_level_key;
644 v_row.level3_parent_key := r_equip_denorm.level3_parent_key;
645 v_row.level3_level_name := r_equip_denorm.level3_level_name;
646
647 END IF;
648
649 IF (p_level_num > 4) THEN
650 v_row.level4_hierarchy_key := r_equip_denorm.level4_hierarchy_key;
651 v_row.level4_effective_date := r_equip_denorm.level4_effective_date;
652 v_row.level4_expiration_date := r_equip_denorm.level4_expiration_date;
653 v_row.level4_id := r_equip_denorm.level4_id;
654 v_row.level4_level_key := r_equip_denorm.level4_level_key;
655 v_row.level4_parent_key := r_equip_denorm.level4_parent_key;
656 v_row.level4_level_name := r_equip_denorm.level4_level_name;
657
658 END IF;
659
660 IF (p_level_num > 5) THEN
661 v_row.level5_hierarchy_key := r_equip_denorm.level5_hierarchy_key;
662 v_row.level5_effective_date := r_equip_denorm.level5_effective_date;
663 v_row.level5_expiration_date := r_equip_denorm.level5_expiration_date;
664 v_row.level5_id := r_equip_denorm.level5_id;
665 v_row.level5_level_key := r_equip_denorm.level5_level_key;
666 v_row.level5_parent_key := r_equip_denorm.level5_parent_key;
667 v_row.level5_level_name := r_equip_denorm.level5_level_name;
668 END IF;
669
670 IF (p_level_num > 6) THEN
671 v_row.level6_hierarchy_key := r_equip_denorm.level6_hierarchy_key;
672 v_row.level6_effective_date := r_equip_denorm.level6_effective_date;
673 v_row.level6_expiration_date := r_equip_denorm.level6_expiration_date;
674 v_row.level6_id := r_equip_denorm.level6_id;
675 v_row.level6_level_key := r_equip_denorm.level6_level_key;
676 v_row.level6_parent_key := r_equip_denorm.level6_parent_key;
677 v_row.level6_level_name := r_equip_denorm.level6_level_name;
678
679 END IF;
680
681 IF (p_level_num > 7) THEN
682 v_row.level7_hierarchy_key := r_equip_denorm.level7_hierarchy_key;
683 v_row.level7_effective_date := r_equip_denorm.level7_effective_date;
684 v_row.level7_expiration_date := r_equip_denorm.level7_expiration_date;
685 v_row.level7_id := r_equip_denorm.level7_id;
686 v_row.level7_level_key := r_equip_denorm.level7_level_key;
687 v_row.level7_parent_key := r_equip_denorm.level7_parent_key;
688 v_row.level7_level_name := r_equip_denorm.level7_level_name;
689
690 END IF;
691
692 IF (p_level_num > 8) THEN
693 v_row.level8_hierarchy_key := r_equip_denorm.level8_hierarchy_key;
694 v_row.level8_effective_date := r_equip_denorm.level8_effective_date;
695 v_row.level8_expiration_date := r_equip_denorm.level8_expiration_date;
696 v_row.level8_id := r_equip_denorm.level8_id;
697 v_row.level8_level_key := r_equip_denorm.level8_level_key;
698 v_row.level8_parent_key := r_equip_denorm.level8_parent_key;
699 v_row.level8_level_name := r_equip_denorm.level8_level_name;
700
701 END IF;
702
703 IF (p_level_num > 9) THEN
704 v_row.level9_hierarchy_key := r_equip_denorm.level9_hierarchy_key;
705 v_row.level9_effective_date := r_equip_denorm.level9_effective_date;
706 v_row.level9_expiration_date := r_equip_denorm.level9_expiration_date;
707 v_row.level9_id := r_equip_denorm.level9_id;
708 v_row.level9_level_key := r_equip_denorm.level9_level_key;
709 v_row.level9_parent_key := r_equip_denorm.level9_parent_key;
710 v_row.level9_level_name := r_equip_denorm.level9_level_name;
711
712 END IF;
713
714 -- if the entry is at the equipment level alone, make the dimension key positive
715 IF(p_level_num =10) THEN
716 v_row.dimension_key := MTH_EQUIPMENT_DIM_S. NEXTVAL();
717 ELSE
718 v_row.dimension_key := -MTH_EQUIPMENT_DIM_S. NEXTVAL();
719 END IF;
720
721 -- load v_row with the current values for the specified entry to be inserted into the denorm table
722
723 IF (p_level_num = 1) THEN
724 v_row.level1_hierarchy_key := p_hierarchy_id;
725 v_row.level1_id := v_row.dimension_key;
726 v_row.level1_level_key := p_level_fk_key;
727 v_row.level1_level_name := p_level_name;
728
729 ELSIF (p_level_num = 2) THEN
730 v_row.level2_hierarchy_key := p_hierarchy_id;
731 v_row.level2_effective_date := p_effective_date;
732 v_row.level2_expiration_date := NULL;
733 v_row.level2_id := v_row.dimension_key;
734 v_row.level2_level_key := p_level_fk_key;
735 v_row.level2_parent_key := p_parent_fk_key;
736 v_row.level2_level_name := p_level_name;
737
738 ELSIF (p_level_num = 3) THEN
739 v_row.level3_hierarchy_key := p_hierarchy_id;
740 -- if the effective date is greater than effective date of parent, then retain this effective date, else write the parent's effective date
741 CASE WHEN p_effective_date >= v_row.level2_effective_date THEN
742 v_row.level3_effective_date := p_effective_date;
743 ELSE v_row.level3_effective_date := v_row.level2_effective_date ;
744 END CASE;
745 v_row.level3_expiration_date := NULL;
746 v_row.level3_id := v_row.dimension_key;
747 v_row.level3_level_key := p_level_fk_key;
748 v_row.level3_parent_key := p_parent_fk_key;
749 v_row.level3_level_name := p_level_name;
750 -- if the parent has an expiration date, then then carry it over to the child entry
751 CASE WHEN v_row.level2_expiration_date IS NOT NULL THEN
752 v_row.level3_expiration_date := v_row.level2_expiration_date ;
753 ELSE v_row.level3_expiration_date := NULL ;
754 END CASE;
755
756 ELSIF (p_level_num = 4) THEN
757 v_row.level4_hierarchy_key := p_hierarchy_id;
758 CASE WHEN p_effective_date >= v_row.level3_effective_date THEN
762 v_row.level4_expiration_date := NULL;
759 v_row.level4_effective_date := p_effective_date;
760 ELSE v_row.level4_effective_date := v_row.level3_effective_date ;
761 END CASE;
763 v_row.level4_id := v_row.dimension_key;
764 v_row.level4_level_key := p_level_fk_key;
765 v_row.level4_parent_key := p_parent_fk_key;
766 v_row.level4_level_name := p_level_name;
767 CASE WHEN v_row.level3_expiration_date IS NOT NULL THEN
768 v_row.level4_expiration_date := v_row.level3_expiration_date ;
769 ELSE v_row.level4_expiration_date := NULL ;
770 END CASE;
771
772 ELSIF (p_level_num = 5) THEN
773 v_row.level5_hierarchy_key := p_hierarchy_id;
774 CASE WHEN p_effective_date >= v_row.level4_effective_date THEN
775 v_row.level5_effective_date := p_effective_date;
776 ELSE v_row.level5_effective_date := v_row.level4_effective_date ;
777 END CASE;
778 v_row.level5_expiration_date := NULL;
779 v_row.level5_id := v_row.dimension_key;
780 v_row.level5_level_key := p_level_fk_key;
781 v_row.level5_parent_key := p_parent_fk_key;
782 v_row.level5_level_name := p_level_name;
783 CASE WHEN v_row.level4_expiration_date IS NOT NULL THEN
784 v_row.level5_expiration_date := v_row.level4_expiration_date ;
785 ELSE v_row.level5_expiration_date := NULL ;
786 END CASE ;
787
788 ELSIF (p_level_num = 6) THEN
789 v_row.level6_hierarchy_key := p_hierarchy_id;
790 CASE WHEN p_effective_date >= v_row.level5_effective_date THEN
791 v_row.level6_effective_date := p_effective_date;
792 ELSE v_row.level6_effective_date := v_row.level5_effective_date ;
793 END CASE;
794 v_row.level6_expiration_date := NULL;
795 v_row.level6_id := v_row.dimension_key;
796 v_row.level6_level_key := p_level_fk_key;
797 v_row.level6_parent_key := p_parent_fk_key;
798 v_row.level6_level_name := p_level_name;
799 CASE WHEN v_row.level5_expiration_date IS NOT NULL THEN
800 v_row.level6_expiration_date := v_row.level5_expiration_date ;
801 ELSE v_row.level6_expiration_date := NULL ;
802 END CASE ;
803
804
805 ELSIF (p_level_num = 7) THEN
806 v_row.level7_hierarchy_key := p_hierarchy_id;
807 CASE WHEN p_effective_date >= v_row.level6_effective_date THEN
808 v_row.level7_effective_date := p_effective_date;
809 ELSE v_row.level7_effective_date := v_row.level6_effective_date ;
810 END CASE;
811 v_row.level7_expiration_date := NULL;
812 v_row.level7_id := v_row.dimension_key;
813 v_row.level7_level_key := p_level_fk_key;
814 v_row.level7_parent_key := p_parent_fk_key;
815 v_row.level7_level_name := p_level_name;
816 CASE WHEN v_row.level6_expiration_date IS NOT NULL THEN
817 v_row.level7_expiration_date := v_row.level6_expiration_date ;
818 ELSE v_row.level7_expiration_date := NULL ;
819 END CASE ;
820
821
822 ELSIF (p_level_num = 8) THEN
823 v_row.level8_hierarchy_key := p_hierarchy_id;
824 CASE WHEN p_effective_date >= v_row.level7_effective_date THEN
825 v_row.level8_effective_date := p_effective_date;
826 ELSE v_row.level8_effective_date := v_row.level7_effective_date ;
827 END CASE;
828 v_row.level8_expiration_date := NULL;
829 v_row.level8_id := v_row.dimension_key;
830 v_row.level8_level_key := p_level_fk_key;
831 v_row.level8_parent_key := p_parent_fk_key;
832 v_row.level8_level_name := p_level_name;
833 CASE WHEN v_row.level7_expiration_date IS NOT NULL THEN
834 v_row.level8_expiration_date := v_row.level7_expiration_date ;
835 ELSE v_row.level8_expiration_date := NULL ;
836 END CASE;
837
838 ELSIF (p_level_num = 9) THEN
839 v_row.level9_hierarchy_key := p_hierarchy_id;
840 CASE WHEN p_effective_date >= v_row.level8_effective_date THEN
841 v_row.level9_effective_date := p_effective_date;
842 ELSE v_row.level9_effective_date := v_row.level8_effective_date ;
843 END CASE;
844 v_row.level9_expiration_date := NULL;
845 v_row.level9_id := v_row.dimension_key;
846 v_row.level9_level_key := p_level_fk_key;
847 v_row.level9_parent_key := p_parent_fk_key;
848 v_row.level9_level_name := p_level_name;
849 CASE WHEN v_row.level8_expiration_date IS NOT NULL THEN
850 v_row.level9_expiration_date := v_row.level8_expiration_date ;
851 ELSE v_row.level9_expiration_date := NULL ;
852 END CASE ;
853
854 ELSIF (p_level_num = 10) THEN
855 v_row.equipment_hierarchy_key := p_hierarchy_id;
856 CASE WHEN p_effective_date >= v_row.level9_effective_date THEN
857 v_row.equipment_effective_date := p_effective_date;
858 ELSE v_row.equipment_effective_date := v_row.level9_effective_date ;
859 END CASE;
860 v_row.equipment_expiration_date := NULL;
861 v_row.equipment_id := v_row.dimension_key;
862 v_row.equipment_fk_key := p_level_fk_key;
863 v_row.equipment_parent_key := p_parent_fk_key;
864 v_row.equipment_level_name := p_level_name;
865 CASE WHEN v_row.level9_expiration_date IS NOT NULL THEN
866 v_row.equipment_expiration_date := v_row.level9_expiration_date ;
867 ELSE v_row.equipment_expiration_date := NULL ;
868 END CASE ;
869
870
871 END IF;
872
873
874 v_row.level_num := p_level_num;
875 v_row.CREATION_DATE := v_log_date;
876 v_row.LAST_UPDATE_DATE := v_log_date;
877 v_row.CREATED_BY := v_unassigned_val;
878 v_row.LAST_UPDATE_LOGIN := v_unassigned_val;
879 v_row.LAST_UPDATED_BY := v_unassigned_val;
880
881 INSERT INTO mth_equipment_denorm_d(dimension_key,
882 level1_hierarchy_key,
883 level1_id,
884 level1_level_key,
885 level1_level_name,
886 level2_hierarchy_key,
890 level2_level_key,
887 level2_effective_date,
888 level2_expiration_date,
889 level2_id,
891 level2_parent_key,
892 level2_level_name,
893 level3_hierarchy_key,
894 level3_effective_date,
895 level3_expiration_date,
896 level3_id,
897 level3_level_key,
898 level3_parent_key,
899 level3_level_name,
900 level4_hierarchy_key,
901 level4_effective_date,
902 level4_expiration_date,
903 level4_id,
904 level4_level_key,
905 level4_parent_key,
906 level4_level_name,
907 level5_hierarchy_key,
908 level5_effective_date,
909 level5_expiration_date,
910 level5_id,
911 level5_level_key,
912 level5_parent_key,
913 level5_level_name,
914 level6_hierarchy_key,
915 level6_effective_date,
916 level6_expiration_date,
917 level6_id,
918 level6_level_key,
919 level6_parent_key,
920 level6_level_name,
921 level7_hierarchy_key,
922 level7_effective_date,
923 level7_expiration_date,
924 level7_id,
925 level7_level_key,
926 level7_parent_key,
927 level7_level_name,
928 level8_hierarchy_key,
929 level8_effective_date,
930 level8_expiration_date,
931 level8_id,
932 level8_level_key,
933 level8_parent_key,
934 level8_level_name,
935 level9_hierarchy_key,
936 level9_effective_date,
937 level9_expiration_date,
938 level9_id,
939 level9_level_key,
940 level9_parent_key,
941 level9_level_name,
942 equipment_hierarchy_key,
943 equipment_effective_date,
944 equipment_expiration_date,
945 equipment_id,
946 equipment_fk_key,
947 equipment_parent_key,
948 equipment_level_name,
949 creation_date,
950 last_update_date,
951 created_by,
952 last_updated_by,
953 last_update_login,
954 level_num)
955 VALUES (v_row.dimension_key,
956 v_row.level1_hierarchy_key,
957 v_row.level1_id,
958 v_row.level1_level_key,
959 v_row.level1_level_name,
960 v_row.level2_hierarchy_key,
961 v_row.level2_effective_date,
962 v_row.level2_expiration_date,
963 v_row.level2_id,
964 v_row.level2_level_key,
965 v_row.level2_parent_key,
966 v_row.level2_level_name,
967 v_row.level3_hierarchy_key,
968 v_row.level3_effective_date,
969 v_row.level3_expiration_date,
970 v_row.level3_id,
971 v_row.level3_level_key,
972 v_row.level3_parent_key,
973 v_row.level3_level_name,
974 v_row.level4_hierarchy_key,
975 v_row.level4_effective_date,
976 v_row.level4_expiration_date,
977 v_row.level4_id,
978 v_row.level4_level_key,
979 v_row.level4_parent_key,
980 v_row.level4_level_name,
981 v_row.level5_hierarchy_key,
982 v_row.level5_effective_date,
983 v_row.level5_expiration_date,
984 v_row.level5_id,
985 v_row.level5_level_key,
986 v_row.level5_parent_key,
987 v_row.level5_level_name,
988 v_row.level6_hierarchy_key,
992 v_row.level6_level_key,
989 v_row.level6_effective_date,
990 v_row.level6_expiration_date,
991 v_row.level6_id,
993 v_row.level6_parent_key,
994 v_row.level6_level_name,
995 v_row.level7_hierarchy_key,
996 v_row.level7_effective_date,
997 v_row.level7_expiration_date,
998 v_row.level7_id,
999 v_row.level7_level_key,
1000 v_row.level7_parent_key,
1001 v_row.level7_level_name,
1002 v_row.level8_hierarchy_key,
1003 v_row.level8_effective_date,
1004 v_row.level8_expiration_date,
1005 v_row.level8_id,
1006 v_row.level8_level_key,
1007 v_row.level8_parent_key,
1008 v_row.level8_level_name,
1009 v_row.level9_hierarchy_key,
1010 v_row.level9_effective_date,
1011 v_row.level9_expiration_date,
1012 v_row.level9_id,
1013 v_row.level9_level_key,
1014 v_row.level9_parent_key,
1015 v_row.level9_level_name,
1016 v_row.equipment_hierarchy_key,
1017 v_row.equipment_effective_date,
1018 v_row.equipment_expiration_date,
1019 v_row.equipment_id,
1020 v_row.equipment_fk_key,
1021 v_row.equipment_parent_key,
1022 v_row.equipment_level_name,
1023 v_row.creation_date,
1024 v_row.last_update_date,
1025 v_row.created_by,
1026 v_row.last_updated_by,
1027 v_row.last_update_login,
1028 v_row.level_num
1029 )
1030 RETURNING dimension_key INTO v_dimension_key;
1031 mth_util_pkg.log_msg('Dimension_key inserted - ' || v_dimension_key, mth_util_pkg.G_DBG_VAR_VAL);
1032 v_count := 1;
1033 end loop;
1034 close c_equip_denorm ;
1035
1036 mth_util_pkg.log_msg('MTH_INSERT_EQUIP_DENORM end', mth_util_pkg.G_DBG_PROC_FUN_END);
1037
1038 --COMMIT;
1039 EXCEPTION
1040 WHEN OTHERS THEN
1041 --Call logging API and then throw exception
1042 mth_util_pkg.log_msg('Exception OTHERS in MTH_INSERT_EQUIP_DENORM', mth_util_pkg.G_DBG_EXCEPTION);
1043 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1044 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1045 mth_util_pkg.log_msg('MTH_INSERT_EQUIP_DENORM end', mth_util_pkg.G_DBG_PROC_FUN_END);
1046 ROLLBACK;
1047 RAISE;
1048
1049 END ;
1050
1051 /*******************************************************************************
1052 * Procedure :MTH_NOVE_EQUIP_DENORM *
1053 * Description :This procedure is used for populating equipment *
1054 * denorm table *
1055 * File Name :---- *
1056 * Visibility :Public *
1057 * Parameters :p_level_fk_key *
1058 * p_parent_fk_key *
1059 * p_hierarchy_id *
1060 * p_level_num *
1061 * p_effective_date *
1062 * p_level_name *
1063 * Modification log : *
1064 * Author Date Change *
1065 * Sanjeev Vellore Ramani 10-Apr-2012 Initial Creation*
1066 *******************************************************************************/
1067
1068 /*This procedure is used to move the existing entities in the equipment denorm table. First the entity, along with its children to be moved,
1069 are determined. Then expiration dates are set for these entries and corresponding fresh entries are made in the equipment denorm table for
1070 these entities.*/
1071
1072 PROCEDURE MTH_MOVE_EQUIP_DENORM(
1073 p_level_fk_key IN NUMBER,
1074 p_parent_fk_key IN NUMBER,
1075 p_hierarchy_id IN NUMBER,
1076 p_level_num IN NUMBER,
1077 p_effective_date IN DATE,
1078 p_level_name IN VARCHAR2
1079 ) IS
1080
1081 v_log_date DATE;
1082 v_unassigned_val VARCHAR2(30);
1083 c_move_equip_denorm SYS_REFCURSOR;
1084 r_move_equip_denorm MTH_EQUIPMENT_DENORM_D%ROWTYPE;
1085 v_row MTH_EQUIPMENT_DENORM_D%ROWTYPE;
1086
1087 BEGIN
1088 mth_util_pkg.log_msg('MTH_MOVE_EQUIP_DENORM start', mth_util_pkg.G_DBG_PROC_FUN_START);
1089 mth_util_pkg.log_msg('p_level_fk_key = ' || p_level_fk_key , mth_util_pkg.G_DBG_PARAM_VAL);
1090 mth_util_pkg.log_msg('p_parent_fk_key = ' || p_parent_fk_key , mth_util_pkg.G_DBG_PARAM_VAL);
1091 mth_util_pkg.log_msg('p_hierarchy_id = ' || p_hierarchy_id , mth_util_pkg.G_DBG_PARAM_VAL);
1095
1092 mth_util_pkg.log_msg('p_level_num = ' || p_level_num , mth_util_pkg.G_DBG_PARAM_VAL);
1093 mth_util_pkg.log_msg('p_effective_date = ' || to_char(p_effective_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
1094 mth_util_pkg.log_msg('p_level_name = ' || p_level_name , mth_util_pkg.G_DBG_PARAM_VAL);
1096 -- Initialize default parameters
1097 v_log_date := sysdate;
1098 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
1099
1100 /*Select the entry at given level to be moved, along with its children*/
1101
1102
1103 IF(p_level_num =2)THEN
1104 OPEN c_move_equip_denorm FOR
1105 SELECT *
1106 FROM MTH_EQUIPMENT_DENORM_D
1107 WHERE
1108 LEVEL_NUM >= p_level_num AND
1109 LEVEL2_HIERARCHY_KEY = p_hierarchy_id AND
1110 LEVEL2_LEVEL_KEY = p_level_fk_key AND
1111 LEVEL2_EXPIRATION_DATE IS NULL
1112 ORDER BY LEVEL_NUM;
1113
1114 ELSIF(p_level_num =3)THEN
1115 OPEN c_move_equip_denorm FOR
1116 SELECT *
1117 FROM MTH_EQUIPMENT_DENORM_D
1118 WHERE
1119 LEVEL_NUM >= p_level_num AND
1120 LEVEL3_HIERARCHY_KEY = p_hierarchy_id AND
1121 LEVEL3_LEVEL_KEY = p_level_fk_key AND
1122 LEVEL3_EXPIRATION_DATE IS NULL
1123 ORDER BY LEVEL_NUM;
1124
1125 ELSIF(p_level_num =4)THEN
1126 OPEN c_move_equip_denorm FOR
1127 SELECT *
1128 FROM MTH_EQUIPMENT_DENORM_D
1129 WHERE
1130 LEVEL_NUM >= p_level_num AND
1131 LEVEL4_HIERARCHY_KEY = p_hierarchy_id AND
1132 LEVEL4_LEVEL_KEY = p_level_fk_key AND
1133 LEVEL4_EXPIRATION_DATE IS NULL
1134 ORDER BY LEVEL_NUM;
1135
1136 ELSIF(p_level_num =5)THEN
1137 OPEN c_move_equip_denorm FOR
1138 SELECT *
1139 FROM MTH_EQUIPMENT_DENORM_D
1140 WHERE
1141 LEVEL_NUM >= p_level_num AND
1142 LEVEL5_HIERARCHY_KEY = p_hierarchy_id AND
1143 LEVEL5_LEVEL_KEY = p_level_fk_key AND
1144 LEVEL5_EXPIRATION_DATE IS NULL
1145 ORDER BY LEVEL_NUM;
1146
1147 ELSIF(p_level_num =6)THEN
1148 OPEN c_move_equip_denorm FOR
1149 SELECT *
1150 FROM MTH_EQUIPMENT_DENORM_D
1151 WHERE
1152 LEVEL_NUM >= p_level_num AND
1153 LEVEL6_HIERARCHY_KEY = p_hierarchy_id AND
1154 LEVEL6_LEVEL_KEY = p_level_fk_key AND
1155 LEVEL6_EXPIRATION_DATE IS NULL
1156 ORDER BY LEVEL_NUM;
1157
1158 ELSIF(p_level_num =7)THEN
1159 OPEN c_move_equip_denorm FOR
1160 SELECT *
1161 FROM MTH_EQUIPMENT_DENORM_D
1162 WHERE
1163 LEVEL_NUM >= p_level_num AND
1164 LEVEL7_HIERARCHY_KEY = p_hierarchy_id AND
1165 LEVEL7_LEVEL_KEY = p_level_fk_key AND
1166 LEVEL7_EXPIRATION_DATE IS NULL
1167 ORDER BY LEVEL_NUM;
1168
1169 ELSIF(p_level_num =8)THEN
1170 OPEN c_move_equip_denorm FOR
1171 SELECT *
1172 FROM MTH_EQUIPMENT_DENORM_D
1173 WHERE
1174 LEVEL_NUM >= p_level_num AND
1175 LEVEL8_HIERARCHY_KEY = p_hierarchy_id AND
1176 LEVEL8_LEVEL_KEY = p_level_fk_key AND
1177 LEVEL8_EXPIRATION_DATE IS NULL
1178
1179 ORDER BY LEVEL_NUM;
1180
1181 ELSIF(p_level_num =9)THEN
1182 OPEN c_move_equip_denorm FOR
1183 SELECT *
1184 FROM MTH_EQUIPMENT_DENORM_D
1185 WHERE
1186 LEVEL_NUM >= p_level_num AND
1187 LEVEL9_HIERARCHY_KEY = p_hierarchy_id AND
1188 LEVEL9_LEVEL_KEY = p_level_fk_key AND
1189 LEVEL9_EXPIRATION_DATE IS NULL
1190 ORDER BY LEVEL_NUM;
1191
1192 ELSIF(p_level_num =10)THEN
1193 OPEN c_move_equip_denorm FOR
1194 SELECT *
1195 FROM MTH_EQUIPMENT_DENORM_D
1196 WHERE
1197 LEVEL_NUM >= p_level_num AND
1198 EQUIPMENT_HIERARCHY_KEY = p_hierarchy_id AND
1199 EQUIPMENT_FK_KEY = p_level_fk_key AND
1200 EQUIPMENT_EXPIRATION_DATE IS NULL
1201 ORDER BY LEVEL_NUM;
1202 END IF;
1203
1204
1205
1206 LOOP
1207 fetch c_move_equip_denorm into r_move_equip_denorm ;
1208 exit when c_move_equip_denorm%NOTFOUND ;
1209
1210 mth_util_pkg.log_msg('r_move_equip_denorm.level_num = ' || r_move_equip_denorm.level_num,mth_util_pkg.G_DBG_VAR_VAL);
1211
1212 /*Check if the parent entry is to be moved- usually the first row from the above result set*/
1213 IF(r_move_equip_denorm.level_num = p_level_num)THEN
1214
1215 /* Set the expiration date for each entity*/
1216 MTH_SET_EXPIRATION_DATE(r_move_equip_denorm.dimension_key,r_move_equip_denorm.level_num,p_effective_date);
1217
1218 /*Insert a new row with parent information passed from the calling procedure*/
1219 MTH_INSERT_EQUIP_DENORM(p_level_fk_key,p_parent_fk_key,p_hierarchy_id,p_level_num,p_effective_date,p_level_name);
1220
1221 ELSE
1222 /*Set the expiration date and insert a new row for one or more children of the newly moved parent*/
1223
1224 IF (r_move_equip_denorm.level_num =3 AND r_move_equip_denorm.level3_expiration_date IS NULL)THEN
1225 MTH_SET_EXPIRATION_DATE(r_move_equip_denorm.dimension_key,r_move_equip_denorm.level_num,p_effective_date);
1226 MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.level3_level_key,r_move_equip_denorm.level2_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.level3_level_name);
1227 END IF;
1228
1229 IF (r_move_equip_denorm.level_num =4 AND r_move_equip_denorm.level4_expiration_date IS NULL)THEN
1230 MTH_SET_EXPIRATION_DATE(r_move_equip_denorm.dimension_key,r_move_equip_denorm.level_num,p_effective_date);
1231 MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.level4_level_key,r_move_equip_denorm.level3_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.level4_level_name);
1232 END IF;
1233
1234 IF (r_move_equip_denorm.level_num =5 AND r_move_equip_denorm.level5_expiration_date IS NULL)THEN
1235 MTH_SET_EXPIRATION_DATE(r_move_equip_denorm.dimension_key,r_move_equip_denorm.level_num,p_effective_date);
1236 MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.level5_level_key,r_move_equip_denorm.level4_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.level5_level_name);
1237 END IF;
1238
1239 IF (r_move_equip_denorm.level_num =6 AND r_move_equip_denorm.level6_expiration_date IS NULL)THEN
1243
1240 MTH_SET_EXPIRATION_DATE(r_move_equip_denorm.dimension_key,r_move_equip_denorm.level_num,p_effective_date);
1241 MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.level6_level_key,r_move_equip_denorm.level5_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.level6_level_name);
1242 END IF;
1244 IF (r_move_equip_denorm.level_num =7 AND r_move_equip_denorm.level7_expiration_date IS NULL)THEN
1245 MTH_SET_EXPIRATION_DATE(r_move_equip_denorm.dimension_key,r_move_equip_denorm.level_num,p_effective_date);
1246 MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.level7_level_key,r_move_equip_denorm.level6_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.level7_level_name);
1247 END IF;
1248
1249 IF (r_move_equip_denorm.level_num =8 AND r_move_equip_denorm.level8_expiration_date IS NULL)THEN
1250 MTH_SET_EXPIRATION_DATE(r_move_equip_denorm.dimension_key,r_move_equip_denorm.level_num,p_effective_date);
1251 MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.level8_level_key,r_move_equip_denorm.level7_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.level8_level_name);
1252 END IF;
1253
1254 IF (r_move_equip_denorm.level_num =9 AND r_move_equip_denorm.level9_expiration_date IS NULL)THEN
1255 MTH_SET_EXPIRATION_DATE(r_move_equip_denorm.dimension_key,r_move_equip_denorm.level_num,p_effective_date);
1256 MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.level9_level_key,r_move_equip_denorm.level8_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.level9_level_name);
1257 END IF;
1258
1259 IF (r_move_equip_denorm.level_num =10 AND r_move_equip_denorm.equipment_expiration_date IS NULL)THEN
1260 MTH_SET_EXPIRATION_DATE(r_move_equip_denorm.dimension_key,r_move_equip_denorm.level_num,p_effective_date);
1261 MTH_INSERT_EQUIP_DENORM(r_move_equip_denorm.equipment_fk_key,r_move_equip_denorm.level9_level_key,p_hierarchy_id,r_move_equip_denorm.level_num,p_effective_date,r_move_equip_denorm.equipment_level_name);
1262 END IF;
1263
1264
1265 END IF;
1266 END LOOP;
1267
1268 CLOSE c_move_equip_denorm ;
1269
1270
1271
1272 mth_util_pkg.log_msg('MTH_MOVE_EQUIP_DENORM end', mth_util_pkg.G_DBG_PROC_FUN_END);
1273
1274 --COMMIT;
1275 EXCEPTION
1276 WHEN OTHERS THEN
1277 --Call logging API and then throw exception
1278 mth_util_pkg.log_msg('Exception OTHERS in MTH_MOVE_EQUIP_DENORM', mth_util_pkg.G_DBG_EXCEPTION);
1279 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1280 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1281 mth_util_pkg.log_msg('MTH_MOVE_EQUIP_DENORM end', mth_util_pkg.G_DBG_PROC_FUN_END);
1282 ROLLBACK;
1283 RAISE;
1284 END;
1285 /*******************************************************************************
1286 * Procedure :MTH_SET_EXPIRATION_DATE *
1287 * Description :This procedure is used for setting expiration date*
1288 * in the equipment denorm table *
1289 * File Name :---- *
1290 * Visibility :Public *
1291 * Parameters :p_dimension_key *
1292 * p_level_num *
1293 * p_effective_date *
1294 * Modification log : *
1295 * Author Date Change *
1296 * Sanjeev Vellore Ramani 17-Apr-2012 Initial Creation*
1297 *******************************************************************************/
1298
1299 /*This procedure sets the expiration date, based on the dimension and level number*/
1300
1301 PROCEDURE MTH_SET_EXPIRATION_DATE(
1302 p_dimension_key IN NUMBER,
1303 p_level_num IN NUMBER,
1304 p_effective_date IN DATE
1305 ) IS
1306
1307 v_log_date DATE;
1308 v_unassigned_val VARCHAR2(30);
1309 r_expire_denorm mth_equipment_denorm_d%ROWTYPE;
1310 v_expiration_date DATE;
1311
1312 BEGIN
1313 mth_util_pkg.log_msg('MTH_SET_EXPIRATION_DATE start', mth_util_pkg.G_DBG_PROC_FUN_START);
1314 mth_util_pkg.log_msg('p_dimension_key = ' || p_dimension_key , mth_util_pkg.G_DBG_PARAM_VAL);
1315 mth_util_pkg.log_msg('p_level_num = ' || p_level_num , mth_util_pkg.G_DBG_PARAM_VAL);
1316 mth_util_pkg.log_msg('p_effective_date = ' || to_char(p_effective_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
1317
1318 -- Initialize default parameters
1319 v_log_date := sysdate;
1320 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
1321 v_expiration_date := p_effective_date - 1/86400;
1322
1323 /*For the levels 2-10, set the expiration date based on the dimension key.Also pick the parent row information for filling in the
1324 expiration dates for the rest of the parent levels */
1325
1326 IF(p_level_num = 2)THEN
1327 SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
1328 (SELECT level1_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
1329
1330 UPDATE mth_equipment_denorm_d
1331 SET level2_expiration_date = v_expiration_date,
1332 last_update_date = v_log_date
1333 WHERE dimension_key = p_dimension_key
1334 AND level_num = 2;
1335
1336
1337 ELSIF(p_level_num = 3)THEN
1338 SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
1339 (SELECT level2_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
1340
1341 UPDATE mth_equipment_denorm_d
1342 SET level3_expiration_date = v_expiration_date,
1343 level2_expiration_date = r_expire_denorm.level2_expiration_date,
1344 last_update_date = v_log_date
1345 WHERE dimension_key = p_dimension_key
1346 AND level_num = 3;
1347
1348
1352 (SELECT level3_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
1349
1350 ELSIF(p_level_num = 4)THEN
1351 SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
1353
1354 UPDATE mth_equipment_denorm_d
1355 SET level4_expiration_date = v_expiration_date,
1356 level3_expiration_date = r_expire_denorm.level3_expiration_date,
1357 level2_expiration_date = r_expire_denorm.level2_expiration_date,
1358 last_update_date = v_log_date
1359 WHERE dimension_key = p_dimension_key
1360 AND level_num = 4;
1361
1362
1363 ELSIF(p_level_num = 5)THEN
1364 SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
1365 (SELECT level4_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
1366
1367 UPDATE mth_equipment_denorm_d
1368 SET level5_expiration_date = v_expiration_date,
1369 level4_expiration_date = r_expire_denorm.level4_expiration_date,
1370 level3_expiration_date = r_expire_denorm.level3_expiration_date,
1371 level2_expiration_date = r_expire_denorm.level2_expiration_date,
1372 last_update_date = v_log_date
1373 WHERE dimension_key = p_dimension_key
1374 AND level_num = 5;
1375
1376
1377 ELSIF(p_level_num = 6)THEN
1378 SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
1379 (SELECT level5_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
1380
1381 UPDATE mth_equipment_denorm_d
1382 SET level6_expiration_date = v_expiration_date,
1383 level5_expiration_date = r_expire_denorm.level5_expiration_date,
1384 level4_expiration_date = r_expire_denorm.level4_expiration_date,
1385 level3_expiration_date = r_expire_denorm.level3_expiration_date,
1386 level2_expiration_date = r_expire_denorm.level2_expiration_date,
1387 last_update_date = v_log_date
1388 WHERE dimension_key = p_dimension_key
1389 AND level_num = 6;
1390
1391
1392 ELSIF(p_level_num = 7)THEN
1393 SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
1394 (SELECT level6_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
1395
1396 UPDATE mth_equipment_denorm_d
1397 SET level7_expiration_date = v_expiration_date,
1398 level6_expiration_date = r_expire_denorm.level6_expiration_date,
1399 level5_expiration_date = r_expire_denorm.level5_expiration_date,
1400 level4_expiration_date = r_expire_denorm.level4_expiration_date,
1401 level3_expiration_date = r_expire_denorm.level3_expiration_date,
1402 level2_expiration_date = r_expire_denorm.level2_expiration_date,
1403 last_update_date = v_log_date
1404 WHERE dimension_key = p_dimension_key
1405 AND level_num = 7;
1406
1407
1408 ELSIF(p_level_num = 8)THEN
1409 SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
1410 (SELECT level7_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
1411
1412 UPDATE mth_equipment_denorm_d
1413 SET level8_expiration_date = v_expiration_date,
1414 level7_expiration_date = r_expire_denorm.level7_expiration_date,
1415 level6_expiration_date = r_expire_denorm.level6_expiration_date,
1416 level5_expiration_date = r_expire_denorm.level5_expiration_date,
1417 level4_expiration_date = r_expire_denorm.level4_expiration_date,
1418 level3_expiration_date = r_expire_denorm.level3_expiration_date,
1419 level2_expiration_date = r_expire_denorm.level2_expiration_date,
1420 last_update_date = v_log_date
1421 WHERE dimension_key = p_dimension_key
1422 AND level_num = 8;
1423
1424 ELSIF(p_level_num = 9)THEN
1425 SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
1426 (SELECT level8_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
1427
1428 UPDATE mth_equipment_denorm_d
1429 SET level9_expiration_date = v_expiration_date,
1430 level8_expiration_date = r_expire_denorm.level8_expiration_date,
1431 level7_expiration_date = r_expire_denorm.level7_expiration_date,
1432 level6_expiration_date = r_expire_denorm.level6_expiration_date,
1433 level5_expiration_date = r_expire_denorm.level5_expiration_date,
1434 level4_expiration_date = r_expire_denorm.level4_expiration_date,
1435 level3_expiration_date = r_expire_denorm.level3_expiration_date,
1436 level2_expiration_date = r_expire_denorm.level2_expiration_date,
1437 last_update_date = v_log_date
1438 WHERE dimension_key = p_dimension_key
1439 AND level_num = 9;
1440
1441 ELSIF(p_level_num = 10)THEN
1442 SELECT * INTO r_expire_denorm FROM mth_equipment_denorm_d WHERE dimension_key IN
1443 (SELECT level9_id FROM mth_equipment_denorm_d WHERE dimension_key = p_dimension_key AND level_num = p_level_num);
1444
1445 UPDATE mth_equipment_denorm_d
1446 SET equipment_expiration_date = v_expiration_date,
1447 level9_expiration_date = r_expire_denorm.level9_expiration_date,
1448 level8_expiration_date = r_expire_denorm.level8_expiration_date,
1449 level7_expiration_date = r_expire_denorm.level7_expiration_date,
1450 level6_expiration_date = r_expire_denorm.level6_expiration_date,
1451 level5_expiration_date = r_expire_denorm.level5_expiration_date,
1452 level4_expiration_date = r_expire_denorm.level4_expiration_date,
1453 level3_expiration_date = r_expire_denorm.level3_expiration_date,
1454 level2_expiration_date = r_expire_denorm.level2_expiration_date,
1455 last_update_date = v_log_date
1456 WHERE dimension_key = p_dimension_key
1457 AND level_num = 10;
1458
1459 END IF;
1460 mth_util_pkg.log_msg('Number of rows updated - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1461 mth_util_pkg.log_msg('MTH_SET_EXPIRATION_DATE end', mth_util_pkg.G_DBG_PROC_FUN_END);
1462
1463 --COMMIT;
1464 EXCEPTION
1465 WHEN OTHERS THEN
1466 --Call logging API and then throw exception
1467 mth_util_pkg.log_msg('Exception OTHERS in MTH_SET_EXPIRATION_DATE', mth_util_pkg.G_DBG_EXCEPTION);
1471 ROLLBACK;
1468 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1469 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1470 mth_util_pkg.log_msg('MTH_SET_EXPIRATION_DATE end', mth_util_pkg.G_DBG_PROC_FUN_END);
1472 RAISE;
1473 END;
1474
1475 /* *****************************************************************************
1476 * Procedure :MTH_EQUIP_HRCHY_UA_MAP *
1477 * Description :*
1478 * File Name :MTHEQIPB.PLS *
1479 * Visibility :Public *
1480 * Parameters :fact table name *
1481 * Modification log : *
1482 * Author Date Change *
1483 * Amrit Kaur 30-Mar-2012 Initial Creation *
1484 ****************************************************************************** */
1485 PROCEDURE MTH_EQUIP_HRCHY_UA_MAP IS
1486 v_log_from_date DATE;
1487 v_log_to_date DATE;
1488 v_unassigned_val VARCHAR2(30);
1489
1490 BEGIN
1491
1492 -- Initialize default parameters
1493 v_log_to_date := sysdate;
1494 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
1495
1496 mth_util_pkg.log_msg('MTH_EQUIP_HRCHY_UA_MAP start', mth_util_pkg.G_DBG_PROC_FUN_START);
1497
1498 -- Call mth_run_log_pre_load
1499 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_HRCHY_UA_MAP',v_unassigned_val,NULL,NULL,0,v_log_to_date);
1500
1501 -- Call GET_RUN_LOG_DATES
1502 mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIP_HRCHY_UA_MAP',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
1503
1504 MERGE INTO MTH_EQUIP_HIERARCHY MEQU
1505 USING
1506 (SELECT MDM.HIERARCHY_ID,
1507 10 LEVEL_NUM,
1508 ME.EQUIPMENT_PK_KEY ,
1509 v_unassigned_val PARENT_FK_KEY,
1510 To_Date('01-01-1900','DD-MM-YYYY') EFFECTIVE_DATE,
1511 ME.EQUIPMENT_NAME EQUIPMENT_NAME,
1512 MTH_UTIL_PKG.MTH_UA_GET_MEANING() PARENT_NAME
1513 FROM MTH_EQUIPMENTS_D ME,MTH_DIM_HIERARCHY MDM
1514 WHERE MDM.DIMENSION_NAME = 'EQUIPMENT'
1515 )CATCH_ALL
1516
1517 ON (
1518 MEQU.HIERARCHY_ID = CATCH_ALL.HIERARCHY_ID AND
1519 MEQU.LEVEL_NUM = CATCH_ALL.LEVEL_NUM AND
1520 MEQU.LEVEL_FK_KEY = CATCH_ALL.EQUIPMENT_PK_KEY AND
1521 MEQU.EFFECTIVE_DATE = CATCH_ALL.EFFECTIVE_DATE
1522 )
1523
1524 WHEN MATCHED THEN UPDATE
1525 SET
1526 LAST_UPDATE_SYSTEM_ID = v_unassigned_val,
1527 GROUP_ID = 1
1528
1529 WHEN NOT MATCHED THEN
1530 INSERT
1531 (MEQU.HIERARCHY_ID,
1532 MEQU.LEVEL_NUM,
1533 MEQU.LEVEL_FK_KEY,
1534 MEQU.PARENT_FK_KEY,
1535 MEQU.SYSTEM_FK_KEY,
1536 MEQU.EFFECTIVE_DATE,
1537 MEQU.CREATION_DATE,
1538 MEQU.LAST_UPDATE_DATE,
1539 MEQU.CREATION_SYSTEM_ID,
1540 MEQU.LAST_UPDATE_SYSTEM_ID,
1541 MEQU.LEVEL_NAME,
1542 MEQU.PARENT_NAME,
1543 MEQU.GROUP_ID)
1544 VALUES
1545 (CATCH_ALL.HIERARCHY_ID,
1546 CATCH_ALL.LEVEL_NUM,
1547 CATCH_ALL.EQUIPMENT_PK_KEY,
1548 CATCH_ALL.PARENT_FK_KEY,
1549 v_unassigned_val,
1550 CATCH_ALL.EFFECTIVE_DATE,
1551 v_log_to_date,
1552 v_log_to_date,
1553 v_unassigned_val,
1554 v_unassigned_val,
1555 CATCH_ALL.EQUIPMENT_NAME,
1556 CATCH_ALL.PARENT_NAME,
1557 1)
1558 ;
1559 mth_util_pkg.log_msg('Number of rows merged in MTH_EQUIP_HIERARCHY - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1560
1561 MERGE
1562 INTO
1563 MTH_EQUIP_HIERARCHY MEQ
1564 USING
1565 (
1566 WITH cntr AS
1567
1568 ( SELECT LEVEL AS n
1569 FROM dual
1570 CONNECT BY LEVEL <= 9
1571 )
1572 SELECT c.n AS LEVEL_NUM
1573 , To_Date ('01-01-1900','DD-MM-YYYY') AS EFFECTIVE_DATE
1574 , -99999 AS LEVEL_KEY
1575 , 'Unassigned' AS LEVEL_NAME
1576 , -99999 AS PARENT_KEY
1577 , 'Unassigned' AS PARENT_NAME
1578 , MDH.HIERARCHY_ID
1579 FROM MTH_DIM_HIERARCHY MDH
1580 CROSS JOIN cntr c
1581 WHERE MDH.CREATION_DATE >= v_log_from_date
1582 AND MDH.DIMENSION_NAME = 'EQUIPMENT')UN_ALL
1583 ON (
1584 MEQ.HIERARCHY_ID = UN_ALL.HIERARCHY_ID AND
1585 MEQ.LEVEL_NUM = UN_ALL.LEVEL_NUM AND
1586 MEQ.LEVEL_FK_KEY = UN_ALL.LEVEL_KEY AND
1587 MEQ.EFFECTIVE_DATE = UN_ALL.EFFECTIVE_DATE
1588 )
1589
1590 WHEN MATCHED THEN
1591 UPDATE
1592 SET
1593 PARENT_FK_KEY =UN_ALL.PARENT_KEY,
1594 SYSTEM_FK_KEY = v_unassigned_val,
1595 CREATION_SYSTEM_ID =v_unassigned_val ,
1596 LEVEL_NAME = UN_ALL.LEVEL_NAME,
1597 GROUP_ID =1
1598
1599 WHEN NOT MATCHED THEN
1600 INSERT
1601 (HIERARCHY_ID,
1602 LEVEL_NUM,
1603 LEVEL_FK_KEY,
1604 PARENT_FK_KEY,
1605 SYSTEM_FK_KEY,
1606 EFFECTIVE_DATE,
1607 CREATION_DATE,
1608 LAST_UPDATE_DATE,
1609 CREATION_SYSTEM_ID,
1610 LAST_UPDATE_SYSTEM_ID,
1611 LEVEL_NAME,
1612 PARENT_NAME,
1613 GROUP_ID)
1614 VALUES
1615 (UN_ALL.HIERARCHY_ID,
1616 UN_ALL.LEVEL_NUM,
1617 UN_ALL.LEVEL_KEY,
1618 UN_ALL.PARENT_KEY,
1619 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
1620 UN_ALL.EFFECTIVE_DATE,
1621 v_log_to_date,
1622 v_log_to_date,
1623 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
1624 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
1625 UN_ALL.LEVEL_NAME,
1626 UN_ALL.PARENT_NAME,
1627 1)
1628 ;
1629 mth_util_pkg.log_msg('Number of rows merged in MTH_EQUIP_HIERARCHY - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1630
1631 MERGE
1632 INTO
1636 distinct --Added to avoid MTH_EQUIP_HIERARCHY_U1 violation in case of
1633 MTH_EQUIP_HIERARCHY MEQB
1634 USING
1635 (SELECT
1637 --reassignments of same level to different parent (sasuren)
1638 MEH.HIERARCHY_ID,
1639 MEH.LEVEL_NUM,
1640 MEH.LEVEL_FK_KEY,
1641 --MEH.SYSTEM_FK_KEY ,
1642 --SF interface populates this as valid value while hierarchy UA populates as
1643 -- -99999. Hence has to be taken out from here
1644 To_Date('01-01-1900','DD-MM-YYYY') CATCH_ALL_EFF_DATE,
1645 --MEH.LAST_UPDATE_DATE LAST_UPDATE_DATE,
1646 --Commented to ensure distinct works fine in case reassignment happens on a
1647 --different date
1648 MEH.LEVEL_NAME,
1649 MTH_UTIL_PKG.MTH_UA_GET_MEANING UNASSIGNED_PARENT_NAME
1650 FROM MTH_EQUIP_HIERARCHY MEH
1651 WHERE
1652 MEH.LAST_UPDATE_DATE >= v_log_from_date AND
1653 MEH.LEVEL_FK_KEY <> -99999
1654
1655 )MERGE_QUERY
1656 ON (MEQB.HIERARCHY_ID = MERGE_QUERY.HIERARCHY_ID AND
1657 MEQB.LEVEL_NUM = MERGE_QUERY.LEVEL_NUM AND
1658 MEQB.LEVEL_FK_KEY = MERGE_QUERY.LEVEL_FK_KEY AND
1659 MEQB.EFFECTIVE_DATE = MERGE_QUERY.CATCH_ALL_EFF_DATE
1660 )
1661
1662 WHEN MATCHED THEN
1663 UPDATE
1664 SET
1665 SYSTEM_FK_KEY = v_unassigned_val, --MERGE_QUERY.SYSTEM_FK_KEY,
1666 LEVEL_NAME = MERGE_QUERY.LEVEL_NAME,
1667 GROUP_ID = 1
1668
1669 WHEN NOT MATCHED THEN
1670 INSERT
1671 (MEQB.HIERARCHY_ID,
1672 MEQB.LEVEL_NUM,
1673 MEQB.LEVEL_FK_KEY,
1674 MEQB.PARENT_FK_KEY,
1675 MEQB.SYSTEM_FK_KEY,
1676 MEQB.EFFECTIVE_DATE,
1677 MEQB.CREATION_DATE,
1678 MEQB.LAST_UPDATE_DATE,
1679 MEQB.CREATION_SYSTEM_ID,
1680 MEQB.LAST_UPDATE_SYSTEM_ID,
1681 MEQB.LEVEL_NAME,
1682 MEQB.PARENT_NAME,
1683 MEQB.GROUP_ID)
1684 VALUES
1685 (MERGE_QUERY.HIERARCHY_ID,
1686 MERGE_QUERY.LEVEL_NUM,
1687 MERGE_QUERY.LEVEL_FK_KEY,
1688 -99999 ,
1689 v_unassigned_val, --MERGE_QUERY.SYSTEM_FK_KEY,
1690 MERGE_QUERY.CATCH_ALL_EFF_DATE,
1691 v_log_to_date, --MERGE_QUERY.LAST_UPDATE_DATE,
1692 v_log_to_date, --MERGE_QUERY.LAST_UPDATE_DATE,
1693 v_unassigned_val, --MERGE_QUERY.SYSTEM_FK_KEY,
1694 v_unassigned_val, --MERGE_QUERY.SYSTEM_FK_KEY,
1695 MERGE_QUERY.LEVEL_NAME,
1696 MERGE_QUERY.UNASSIGNED_PARENT_NAME,
1697 1)
1698 ;
1699 mth_util_pkg.log_msg('Number of rows merged in MTH_EQUIP_HIERARCHY - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1700
1701 ----Call mth_run_log_post_load
1702 mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_HRCHY_UA_MAP',v_unassigned_val);
1703
1704 mth_util_pkg.log_msg('MTH_EQUIP_HRCHY_UA_MAP end', mth_util_pkg.G_DBG_PROC_FUN_END);
1705 COMMIT;
1706 EXCEPTION
1707 WHEN OTHERS THEN
1708 mth_util_pkg.log_msg('Exception OTHERS in MTH_EQUIP_HRCHY_UA_MAP', mth_util_pkg.G_DBG_EXCEPTION);
1709 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1710 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1711 mth_util_pkg.log_msg('MTH_EQUIP_HRCHY_UA_MAP end', mth_util_pkg.G_DBG_PROC_FUN_END);
1712 ROLLBACK;
1713 RAISE;
1714
1715 END MTH_EQUIP_HRCHY_UA_MAP;
1716
1717
1718 END MTH_EQUIPMENT_PKG;