[Home] [Help]
PACKAGE BODY: APPS.HR_EDW_WRK_CMPSTN_F_SIZING
Source
1 PACKAGE BODY hr_edw_wrk_cmpstn_f_sizing AS
2 /* $Header: hriezwcp.pkb 120.1 2005/06/08 02:51:46 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_edw_cmpstn_snpsht_dts;
16
17 BEGIN
18
19 OPEN row_count_cur;
20 FETCH row_count_cur INTO p_row_count;
21 CLOSE row_count_cur;
22
23 END count_source_rows;
24
25 /******************************************************************************/
26 /* Estimates row lengths. */
27 /******************************************************************************/
28 PROCEDURE estimate_row_length( p_from_date IN DATE,
29 p_to_date IN DATE,
30 p_avg_row_length OUT NOCOPY NUMBER )
31
32 IS
33
34 x_date NUMBER :=7;
35
36 x_total_wrk_cmpstn NUMBER;
37
38 x_composition_pk NUMBER:=0;
39
40 x_age_band_fk NUMBER:=0;
41 x_service_band_fk NUMBER:=0;
42 x_assignment_fk NUMBER:=0;
43 x_geography_fk NUMBER:=0;
44 x_grade_fk NUMBER:=0;
45 x_instance_fk NUMBER:=0;
46 x_job_fk NUMBER:=0;
47 x_organization_fk NUMBER:=0;
48 x_person_fk NUMBER:=0;
49 x_person_type_fk NUMBER:=0;
50 x_position_fk NUMBER:=0;
51 x_time_fk NUMBER:=0;
52
53 x_asg_assignment_id NUMBER:=0;
54 x_asg_business_group_id NUMBER:=0;
55 x_asg_grade_id NUMBER:=0;
56 x_asg_job_id NUMBER:=0;
57 x_asg_location_id NUMBER:=0;
58 x_asg_organization_id NUMBER:=0;
59 x_asg_person_id NUMBER:=0;
60 x_asg_position_id NUMBER:=0;
61
62 x_snapshot_date NUMBER:=x_date;
63 x_assignment_start_date NUMBER:=x_date;
64 x_date_of_birth NUMBER:=x_date;
65 x_last_update_date NUMBER:=x_date;
66 x_creation_date NUMBER:=x_date;
67
68 /* Select the length of the instance code */
69 CURSOR inst_cur IS
70 SELECT avg(nvl( vsize(instance_code),0 ))
71 FROM edw_local_instance;
72
73 CURSOR asg_cur IS
74 SELECT
75 avg(nvl(vsize(asg.assignment_id ),0))
76 ,avg(nvl(vsize(asg.business_group_id ),0))
77 ,avg(nvl(vsize(asg.grade_id),0))
78 ,avg(nvl(vsize(asg.job_id),0))
79 ,avg(nvl(vsize(asg.location_id ),0))
80 ,avg(nvl(vsize(asg.organization_id),0))
81 ,avg(nvl(vsize(asg.person_id ),0))
82 ,avg(nvl(vsize(asg.position_id ),0))
83 FROM per_all_assignments_f asg
84 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
85
86 BEGIN
87
88 OPEN inst_cur;
89 FETCH inst_cur INTO x_instance_fk;
90 CLOSE inst_cur;
91
92 OPEN asg_cur;
93 FETCH asg_cur INTO
94 x_asg_assignment_id
95 ,x_asg_business_group_id
96 ,x_asg_grade_id
97 ,x_asg_job_id
98 ,x_asg_location_id
99 ,x_asg_organization_id
100 ,x_asg_person_id
101 ,x_asg_position_id;
102 CLOSE asg_cur;
103
104 x_composition_pk := x_asg_assignment_id + x_snapshot_date + x_instance_fk ;
105
106 x_age_band_fk := hri_edw_dim_sizing.get_size_agb_pk;
107 x_service_band_fk := hri_edw_dim_sizing.get_size_lwb_pk;
108 x_assignment_fk := hri_edw_dim_sizing.get_size_asg_pk;
109 x_geography_fk := hri_edw_dim_sizing.get_size_geog_pk;
110 x_grade_fk := hri_edw_dim_sizing.get_size_grd_pk;
111 x_job_fk := hri_edw_dim_sizing.get_size_job_pk;
112 x_organization_fk := hri_edw_dim_sizing.get_size_org_pk;
113 x_person_fk := hri_edw_dim_sizing.get_size_psn_pk;
114 x_person_type_fk := hri_edw_dim_sizing.get_size_pty_pk;
115 x_position_fk := hri_edw_dim_sizing.get_size_pos_pk;
116 x_time_fk := hri_edw_dim_sizing.get_size_time_pk;
117
118
119 x_total_wrk_cmpstn := NVL(ceil(x_composition_pk + 1), 0)
120 + NVL(ceil(x_composition_pk + 1), 0)
121 + NVL(ceil(x_age_band_fk + 1), 0)
122 + NVL(ceil(x_service_band_fk + 1), 0)
123 + NVL(ceil(x_assignment_fk + 1), 0)
124 + NVL(ceil(x_geography_fk + 1), 0)
125 + NVL(ceil(x_grade_fk + 1), 0)
126 + NVL(ceil(x_instance_fk + 1), 0)
127 + NVL(ceil(x_job_fk + 1), 0)
128 + NVL(ceil(x_organization_fk + 1), 0)
129 + NVL(ceil(x_person_fk + 1), 0)
130 + NVL(ceil(x_person_type_fk + 1), 0)
131 + NVL(ceil(x_position_fk + 1), 0)
132 + NVL(ceil(x_time_fk + 1), 0)
133 + NVL(ceil(x_asg_assignment_id + 1), 0)
134 + NVL(ceil(x_asg_business_group_id + 1), 0)
135 + NVL(ceil(x_asg_grade_id + 1), 0)
136 + NVL(ceil(x_asg_job_id + 1), 0)
137 + NVL(ceil(x_asg_location_id + 1), 0)
138 + NVL(ceil(x_asg_organization_id + 1), 0)
139 + NVL(ceil(x_asg_person_id + 1), 0)
140 + NVL(ceil(x_asg_position_id + 1), 0)
141 + NVL(ceil(x_snapshot_date + 1), 0)
142 + NVL(ceil(x_assignment_start_date + 1), 0)
143 + NVL(ceil(x_date_of_birth + 1), 0)
144 + NVL(ceil(x_last_update_date + 1), 0)
145 + NVL(ceil(x_creation_date + 1), 0);
146
147 /* TOTAL */
148
149 p_avg_row_length := x_total_wrk_cmpstn;
150
151 END estimate_row_length;
152
153 END hr_edw_wrk_cmpstn_f_sizing;