1 PACKAGE BODY FII_TIME_HOOK as
2 /*$Header: FIITIMHB.pls 120.0 2002/08/24 05:02:45 appldev noship $ */
3
4 function Pre_Dim_Collect return boolean IS
5 l_cid INTEGER;
6 l_master_instance VARCHAR2(30);
7 instance_count INTEGER;
8 l_statement VARCHAR2(10000):=NULL;
9 l_table VARCHAR2(30):=NULL;
10 rows_processed VARCHAR2(10):=NULL;
11 l_count INTEGER;
12 na_count INTEGER;
13 l_max DATE;
14 l_min DATE;
15 l_ep_max DATE;
16 l_ep_min DATE;
17 retcode VARCHAR2(2000);
18 errbuf VARCHAR2(2000);
19 l_join VARCHAR2(2000);
20
21 begin
22 edw_log.put_line('Entering Time Pre Dimension Hook Procedure');
23 edw_log.put_line(' ');
24
25 -- --------------------------------------------------------------
26 -- Update calendar day
27 -- --------------------------------------------------------------
28 /* determine if data is pushed from oracle application, if not, return without updating */
29 select count(*) into l_count
30 from edw_time_cal_day_lstg where ep_cal_period_fk = 'oracle_source' and collection_status='READY';
31
32 if l_count = 0 then
33 edw_log.put_line('No data is pushed from oracle application, exit without updating any staging table.');
34 return true;
35 else
36 edw_log.put_line(to_char(l_count)||' records are ready to update.');
37 edw_log.put_line(' ');
38 end if;
39
40 /* Identify the master instance */
41 select instance_code
42 into l_master_instance
43 from edw_system_parameters;
44 edw_log.put_line('Master instance is '||l_master_instance||'.');
45
46 /* Identify the instance of the source data */
47 select count(distinct instance) into instance_count
48 from edw_time_cal_day_lstg
49 where ep_cal_period_fk = 'oracle_source' and collection_status='READY' and instance <> l_master_instance;
50
51 /* Check if data pushed from the master instance, if yes, table edw_time_ep_cal_period_lstg
52 will be used to populate ep_cal_period_fk, if no, table edw_time_ep_cal_period_ltc will
53 be used instead. This is because the LSTG table may be purged before data is pushed from
54 another source. */
55
56 if instance_count=0 then
57 l_table:='edw_time_ep_cal_period_lstg';
58 l_join:=' and ep.collection_status=''READY'' and ep.instance= '''||l_master_instance||'''';
59 select min(start_date), max(end_date)
60 into l_ep_min, l_ep_max
61 from edw_time_ep_cal_period_lstg;
62 edw_log.put_line('Data is collected from the master instance, Enterprise Calendar in the staging table will be used to update the Calendar Day foreign keys.');
63 edw_log.put_line(' ');
64 else
65 l_table:='edw_time_ep_cal_period_ltc';
66 l_join:=null;
67 select min(start_date), max(end_date)
68 into l_ep_min, l_ep_max
69 from edw_time_ep_cal_period_ltc;
70 edw_log.put_line('Data is not collected from the master instance, Enterprise Calendar in the level table will be used to update the Calendar Day foreign keys.');
71 edw_log.put_line(' ');
72 end if;
73
74 /* Populate NA_EDW if period is not defined in gl_periods for the given date */
75 l_statement:='update EDW_TIME_CAL_DAY_LSTG a set ep_cal_period_fk=nvl(
76 (select ep.cal_period_pk from '||l_table||' ep
77 where a.calendar_date between ep.start_date and ep.end_date
78 and ep.timespan=(select min(ep1.timespan) from '||l_table||' ep1
79 where a.calendar_date between ep1.start_date and ep1.end_date)
80 and rownum=1'||l_join||'),''NA_EDW'')
81 where a.ep_cal_period_fk=''oracle_source'' and a.collection_status=''READY''';
82
83 edw_log.put_line(l_statement);
84 edw_log.put_line(' ');
85
86 EXECUTE IMMEDIATE l_statement;
87
88 edw_log.put_line('Finished updating Calendar Day level with correct Enterprise Period.');
89 edw_log.put_line(' ');
90
91 select count(*), min(calendar_date), max(calendar_date)
92 into na_count, l_min, l_max
93 from edw_time_cal_day_lstg where ep_cal_period_fk = 'NA_EDW' and collection_status='READY';
94
95 if na_count > 0 then
96 edw_log.put_line('WARNING:The foreign key up the Enterprise Calendar Hierarchy is not updated for '||to_char(na_count)||' records!');
97 edw_log.put_line('The records being collected are from '||to_char(l_min, 'dd-Mon-yyyy')||
98 ' to '||to_char(l_max, 'dd-Mon-yyyy'));
99 edw_log.put_line('while the enterprise calendar defined in GL is from '||to_char(l_ep_min, 'dd-Mon-yyyy')||
100 ' to '||to_char(l_ep_max, 'dd-Mon-yyyy'));
101 edw_log.put_line('Please ensure enterprise calendar periods spans the entire time range in the warehouse.');
102 end if;
103
104 commit;
105 edw_log.put_line(to_char(l_count-na_count)||' records has been updated with the correct enterprise calendar!');
106 return true;
107
108 Exception
109 when no_data_found then
110 -- It's okay if there are no records in staging
111 null;
112 return true;
113
114 when others then
115 rollback;
116 retcode := sqlcode;
117 errbuf := sqlerrm;
118 edw_log.put_line(retcode||' : '||errbuf);
119 return false;
120
121 end Pre_Dim_Collect;
122
123 END FII_TIME_HOOK;
[Home] [Help]