[Home] [Help]
PACKAGE BODY: APPS.HR_EDW_WRK_RCTMNT_F_SIZING
Source
1 PACKAGE BODY hr_edw_wrk_rctmnt_f_sizing AS
2 /* $Header: hriezwrt.pkb 120.1 2005/06/08 02:52:08 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(rec.application_id) total
15 FROM hri_recruitment_stages rec
16 WHERE NVL(rec.last_update_date, to_date('01-01-2000','DD-MM-YYYY'))
17 BETWEEN p_from_date AND p_to_date;
18
19 BEGIN
20
21 OPEN row_count_cur;
22 FETCH row_count_cur INTO p_row_count;
23 CLOSE row_count_cur;
24
25 END count_source_rows;
26
27
28 /******************************************************************************/
29 /* Estimates row lengths. */
30 /******************************************************************************/
31 PROCEDURE estimate_row_length( p_from_date IN DATE,
32 p_to_date IN DATE,
33 p_avg_row_length OUT NOCOPY NUMBER )
34
35 IS
36
37 x_date NUMBER := 7;
38
39 x_total NUMBER;
40
41 x_recruitment_gain_pk NUMBER;
42
43 /****************************/
44 /* Fact Hidden Primary Keys */
45 /****************************/
46 x_application_id NUMBER := 0;
47 x_assignment_id NUMBER := 0;
48 x_business_group_id NUMBER := 0;
49 x_person_id NUMBER := 0;
50 x_instance NUMBER := 0;
51
52 /******************************/
53 /* Foreign Keys to Dimensions */
54 /******************************/
55 x_age_band_fk NUMBER := 0;
56 x_assignment_fk NUMBER := 0;
57 x_geography_fk NUMBER := 0;
58 x_grade_fk NUMBER := 0;
59 x_instance_fk NUMBER := 0;
60 x_job_fk NUMBER := 0;
61 x_organization_fk NUMBER := 0;
62 x_person_fk NUMBER := 0;
63 x_person_type_fk NUMBER := 0;
64 x_position_fk NUMBER := 0;
65 x_movement_fk NUMBER := 0;
66 x_reason_fk NUMBER := 0;
67 x_requisition_vacancy_fk NUMBER := 0;
68 x_recruitment_activity_fk NUMBER := 0;
69 x_service_band_fk NUMBER := 0;
70 x_time_fk NUMBER := 0;
71
72 /**********************/
73 /* Regular Attributes */
74 /**********************/
75 x_application_start_date NUMBER := x_date;
76 x_application_end_date NUMBER := x_date;
77 x_hire_date NUMBER := x_date;
78 x_planned_start_date NUMBER := x_date;
79 x_creation_date NUMBER := x_date;
80 x_last_update_date NUMBER := x_date;
81
82 /* Select the length of the instance code */
83 CURSOR inst_cur IS
84 SELECT avg(nvl( vsize(instance_code),0 ))
85 FROM edw_local_instance;
86
87 CURSOR asg_cur IS
88 SELECT
89 avg(nvl(vsize(application_id),0))
90 ,avg(nvl(vsize(assignment_id),0))
91 ,avg(nvl(vsize(business_group_id ),0))
92 ,avg(nvl(vsize(person_id),0))
93 FROM
94 per_all_assignments_f
95 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
96
97 BEGIN
98
99 OPEN inst_cur;
100 FETCH inst_cur INTO x_instance;
101 CLOSE inst_cur;
102
103 x_age_band_fk := hri_edw_dim_sizing.get_size_agb_pk;
104 x_assignment_fk := hri_edw_dim_sizing.get_size_asg_pk;
105 x_geography_fk := hri_edw_dim_sizing.get_size_geog_pk;
106 x_grade_fk := hri_edw_dim_sizing.get_size_grd_pk;
107 x_instance_fk := x_instance;
108 x_job_fk := hri_edw_dim_sizing.get_size_job_pk;
109 x_organization_fk := 2 * hri_edw_dim_sizing.get_size_org_pk;
110 x_person_fk := 4 * hri_edw_dim_sizing.get_size_psn_pk;
111 x_person_type_fk := hri_edw_dim_sizing.get_size_pty_pk;
112 x_position_fk := hri_edw_dim_sizing.get_size_pos_pk;
113 x_movement_fk := 8 * hri_edw_dim_sizing.get_size_mvt_pk;
114 x_reason_fk := 8 * hri_edw_dim_sizing.get_size_rsn_pk;
115 x_requisition_vacancy_fk := hri_edw_dim_sizing.get_size_vac_pk;
116 x_recruitment_activity_fk := hri_edw_dim_sizing.get_size_rec_pk;
117 x_service_band_fk := hri_edw_dim_sizing.get_size_lwb_pk;
118 x_time_fk := 8* hri_edw_dim_sizing.get_size_time_pk;
119
120 OPEN asg_cur;
121 FETCH asg_cur INTO
122 x_application_id
123 ,x_assignment_id
124 ,x_business_group_id
125 ,x_person_id;
126 CLOSE asg_cur;
127
128 x_recruitment_gain_pk := x_application_id + x_assignment_id + x_instance;
129
130 x_total := NVL(ceil(x_recruitment_gain_pk+ 1), 0)
131 + NVL(ceil(x_application_id+ 1), 0)
132 + NVL(ceil(x_assignment_id+ 1), 0)
133 + NVL(ceil(x_business_group_id+ 1), 0)
134 + NVL(ceil(x_person_id+ 1), 0)
135 + NVL(ceil(x_age_band_fk+ 1), 0)
136 + NVL(ceil(x_assignment_fk+ 1), 0)
137 + NVL(ceil(x_geography_fk+ 1), 0)
138 + NVL(ceil(x_grade_fk+ 1), 0)
139 + NVL(ceil(x_instance_fk+ 1), 0)
140 + NVL(ceil(x_job_fk+ 1), 0)
141 + NVL(ceil(x_organization_fk + 1), 0)
142 + NVL(ceil(x_person_fk + 1), 0)
143 + NVL(ceil(x_person_type_fk+ 1), 0)
144 + NVL(ceil(x_position_fk+ 1), 0)
145 + NVL(ceil(x_movement_fk+ 1), 0)
146 + NVL(ceil(x_reason_fk+ 1), 0)
147 + NVL(ceil(x_requisition_vacancy_fk+ 1), 0)
148 + NVL(ceil(x_recruitment_activity_fk+ 1), 0)
149 + NVL(ceil(x_service_band_fk+ 1), 0)
150 + NVL(ceil(x_time_fk+ 1), 0)
151 + NVL(ceil(x_application_start_date+ 1), 0)
152 + NVL(ceil(x_application_end_date+ 1), 0)
153 + NVL(ceil(x_hire_date+ 1), 0)
154 + NVL(ceil(x_planned_start_date+ 1), 0)
155 + NVL(ceil(x_creation_date+ 1), 0)
156 + NVL(ceil(x_last_update_date+ 1), 0);
157
158 p_avg_row_length := x_total;
159
160 END estimate_row_length;
161
162 END hr_edw_wrk_rctmnt_f_sizing;