[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_SERVICE_M_SIZING
Source
1 PACKAGE BODY edw_hr_service_m_sizing AS
2 /* $Header: hriezlwb.pkb 120.1 2005/06/08 02:46:39 anmajumd noship $ */
3 /******************************************************************************/
4 /* Sets p_row_count to the number of rows which would be collected between */
5 /* the given dates */
6 /******************************************************************************/
7 PROCEDURE count_source_rows( p_from_date IN DATE,
8 p_to_date IN DATE,
9 p_row_count OUT NOCOPY NUMBER )
10 IS
11
12 /* Cursor description */
13 CURSOR row_count_cur IS
14 SELECT count(*) total
15 FROM hri_service_bands bnds
16 WHERE NVL(last_update_date, to_date('01-01-2000','DD-MM-YYYY'))
17 BETWEEN p_from_date AND p_to_date
18 AND bnds.days_to_month IS NULL;
19
20
21 BEGIN
22
23 OPEN row_count_cur;
24 FETCH row_count_cur INTO p_row_count;
25 CLOSE row_count_cur;
26
27 END count_source_rows;
28
29 /******************************************************************************/
30 /* Estimates row lengths. */
31 /******************************************************************************/
32 PROCEDURE estimate_row_length( p_from_date IN DATE,
33 p_to_date IN DATE,
34 p_avg_row_length OUT NOCOPY NUMBER )
35
36 IS
37
38 x_date NUMBER :=7;
39
40 x_total_service NUMBER;
41
42 x_service_band_pk NUMBER:=0;
43 x_instance NUMBER:=0;
44 x_name NUMBER:=0;
45 x_service_band_dp NUMBER:=0;
46 x_service_length_max_year NUMBER:=0;
47 x_service_length_max_month NUMBER:=0;
48 x_service_length_max_week NUMBER:=0;
49 x_service_length_max_day NUMBER:=0;
50 x_service_length_min_year NUMBER:=0;
51 x_service_length_min_month NUMBER:=0;
52 x_service_length_min_week NUMBER:=0;
53 x_service_length_min_day NUMBER:=0;
54 x_last_update_date NUMBER:=x_date;
55 x_creation_date NUMBER:=x_date;
56
57 /* Select the length of the instance code */
58 CURSOR inst_cur IS
59 SELECT avg(nvl( vsize(instance_code),0 ))
60 FROM edw_local_instance;
61
62 CURSOR bnd_cur IS
63 SELECT
64 avg(nvl(vsize(bnds.band_max_total_years),0))
65 ,avg(nvl(vsize(bnds.band_max_total_months),0))
66 ,avg(nvl(vsize(bnds.band_max_total_weeks),0))
67 ,avg(nvl(vsize(bnds.band_max_total_days),0))
68 ,avg(nvl(vsize(bnds.band_min_total_years),0))
69 ,avg(nvl(vsize(bnds.band_min_total_months),0))
70 ,avg(nvl(vsize(bnds.band_min_total_weeks),0))
71 ,avg(nvl(vsize(bnds.band_min_total_days),0))
72 FROM
73 hri_service_bands bnds
74 WHERE bnds.last_update_date BETWEEN p_from_date AND p_to_date;
75
76 BEGIN
77
78 OPEN inst_cur;
79 FETCH inst_cur INTO x_instance;
80 CLOSE inst_cur;
81
82 OPEN bnd_cur;
83 FETCH bnd_cur INTO
84 x_service_length_max_year
85 ,x_service_length_max_month
86 ,x_service_length_max_week
87 ,x_service_length_max_day
88 ,x_service_length_min_year
89 ,x_service_length_min_month
90 ,x_service_length_min_week
91 ,x_service_length_min_day;
92 CLOSE bnd_cur;
93
94 x_name := x_service_length_max_year
95 + x_service_length_max_month
96 + x_service_length_max_week
97 + x_service_length_max_day
98 + x_service_length_min_year
99 + x_service_length_min_month
100 + x_service_length_min_week
101 + x_service_length_min_day;
102
103 x_service_band_dp := x_name;
104 x_service_band_pk := x_name + x_instance;
105
106 x_total_service := NVL(ceil(x_service_band_pk + 1), 0)
107 + NVL(ceil(x_instance + 1), 0)
108 + NVL(ceil(x_name + 1), 0)
109 + NVL(ceil(x_service_band_dp + 1), 0)
110 + NVL(ceil(x_service_length_max_year + 1), 0)
111 + NVL(ceil(x_service_length_max_month + 1), 0)
112 + NVL(ceil(x_service_length_max_week + 1), 0)
113 + NVL(ceil(x_service_length_max_day + 1), 0)
114 + NVL(ceil(x_service_length_min_year + 1), 0)
115 + NVL(ceil(x_service_length_min_month + 1), 0)
116 + NVL(ceil(x_service_length_min_week + 1), 0)
117 + NVL(ceil(x_service_length_min_day + 1), 0)
118 + NVL(ceil(x_last_update_date + 1), 0)
119 + NVL(ceil(x_creation_date + 1), 0);
120
121 /* TOTAL */
122
123 p_avg_row_length := x_total_service;
124
125 END estimate_row_length;
126
127 END edw_hr_service_m_sizing;