[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_PRSN_TYP_M_SIZING
Source
1 PACKAGE BODY edw_hr_prsn_typ_m_sizing AS
2 /* $Header: hriezpty.pkb 120.1 2005/06/08 02:49:16 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(person_type_pk) total
15 FROM hri_person_type_cmbns
16 WHERE NVL(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 /* Estimates row lengths. */
29 /******************************************************************************/
30 PROCEDURE estimate_row_length( p_from_date IN DATE,
31 p_to_date IN DATE,
32 p_avg_row_length OUT NOCOPY NUMBER )
33
34 IS
35
36 x_date NUMBER :=7;
37
38 x_total_pty NUMBER;
39
40 x_person_type_pk NUMBER:=0;
41 x_name NUMBER:=0;
42 x_person_type_dp NUMBER:=0;
43 x_instance NUMBER:=0;
44 x_pw_employee_type NUMBER:=0;
45 x_dh_permanent_type NUMBER:=0;
46 x_dh_intern_type NUMBER:=0;
47 x_dh_fxd_trm_lower_type NUMBER:=0;
48 x_dh_fxd_trm_upper_type NUMBER:=0;
49 x_cw_agncy_cntrctr_type NUMBER:=0;
50 x_cw_self_employed_type NUMBER:=0;
51 x_cw_consultant_type NUMBER:=0;
52 x_pw_ex_employee_type NUMBER:=0;
53 x_pw_applicant_type NUMBER:=0;
54 x_nw_dependent_type NUMBER:=0;
55 x_nw_retiree_type NUMBER:=0;
56 x_nw_beneficiary_type NUMBER:=0;
57 x_srviving_fmly_mbr_type NUMBER:=0;
58 x_srviving_spouse_type NUMBER:=0;
59 x_ex_applicant_type NUMBER:=0;
60 x_other_type NUMBER:=0;
61 x_participant_type NUMBER:=0;
62 x_former_spouse_type NUMBER:=0;
63 x_former_fmly_mbr_type NUMBER:=0;
64 x_last_update_date NUMBER:=x_date;
65 x_creation_date NUMBER:=x_date;
66
67 x_avg_flag NUMBER:=0;
68
69 x_yes NUMBER :=0;
70 x_no NUMBER :=0;
71
72 /* Select the length of the instance code */
73 CURSOR inst_cur IS
74 SELECT avg(nvl( vsize(instance_code),0 ))
75 FROM edw_local_instance;
76
77
78 /* Get lengths of person type attributes */
79 CURSOR pty_cur IS
80 SELECT
81 avg(nvl(vsize(person_type_pk),0))
82 ,avg(nvl(vsize(employee),0))
83 ,avg(nvl(vsize(permanent),0))
84 ,avg(nvl(vsize(intern),0))
85 ,avg(nvl(vsize(fixed_term_lower),0))
86 ,avg(nvl(vsize(fixed_term_upper),0))
87 ,avg(nvl(vsize(agency),0))
88 ,avg(nvl(vsize(self_employed),0))
89 ,avg(nvl(vsize(consultant),0))
90 ,avg(nvl(vsize(ex_employee),0))
91 ,avg(nvl(vsize(applicant),0))
92 ,avg(nvl(vsize(dependent),0))
93 ,avg(nvl(vsize(retiree),0))
94 ,avg(nvl(vsize(beneficiary),0))
95 ,avg(nvl(vsize(surviving_family_member),0))
96 ,avg(nvl(vsize(surviving_spouse),0))
97 ,avg(nvl(vsize(ex_applicant),0))
98 ,avg(nvl(vsize(other),0))
99 ,avg(nvl(vsize(participant),0))
100 ,avg(nvl(vsize(former_spouse),0))
101 ,avg(nvl(vsize(former_family_member),0))
102 FROM hri_person_type_cmbns
103 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
104
105 CURSOR yes_no_cur IS
106 SELECT
107 avg(nvl(vsize(hr_general.decode_lookup('YES_NO','Y')),0))
108 ,avg(nvl(vsize(hr_general.decode_lookup('YES_NO','N')),0))
109 FROM dual;
110
111 BEGIN
112
113 OPEN inst_cur;
114 FETCH inst_cur INTO x_instance;
115 CLOSE inst_cur;
116
117 OPEN pty_cur;
118 FETCH pty_cur INTO
119 x_person_type_pk
120 ,x_pw_employee_type
121 ,x_dh_permanent_type
122 ,x_dh_intern_type
123 ,x_dh_fxd_trm_lower_type
124 ,x_dh_fxd_trm_upper_type
125 ,x_cw_agncy_cntrctr_type
126 ,x_cw_self_employed_type
127 ,x_cw_consultant_type
128 ,x_pw_ex_employee_type
129 ,x_pw_applicant_type
130 ,x_nw_dependent_type
131 ,x_nw_retiree_type
132 ,x_nw_beneficiary_type
133 ,x_srviving_fmly_mbr_type
134 ,x_srviving_spouse_type
135 ,x_ex_applicant_type
136 ,x_other_type
137 ,x_participant_type
138 ,x_former_spouse_type
139 ,x_former_fmly_mbr_type;
140 CLOSE pty_cur;
141
142 OPEN yes_no_cur;
143 FETCH yes_no_cur INTO x_yes, x_no;
144 CLOSE yes_no_cur;
145
146 x_person_type_pk := x_person_type_pk + x_instance;
147 x_name := x_person_type_pk;
148 x_person_type_dp := x_person_type_pk;
149
150 x_avg_flag := ((x_yes+x_no)/2);
151
152 x_total_pty:=
153
154 NVL(ceil(x_person_type_pk + 1), 0)
155 + NVL(ceil(x_name + 1), 0)
156 + NVL(ceil(x_person_type_dp + 1), 0)
157 + NVL(ceil(x_instance + 1), 0)
158 + NVL(ceil(x_pw_employee_type + 1), 0)
159 + NVL(ceil(x_dh_permanent_type + 1), 0)
160 + NVL(ceil(x_dh_intern_type + 1), 0)
161 + NVL(ceil(x_dh_fxd_trm_lower_type + 1), 0)
162 + NVL(ceil(x_dh_fxd_trm_upper_type + 1), 0)
163 + NVL(ceil(x_cw_agncy_cntrctr_type + 1), 0)
164 + NVL(ceil(x_cw_self_employed_type + 1), 0)
165 + NVL(ceil(x_cw_consultant_type + 1), 0)
166 + NVL(ceil(x_pw_ex_employee_type + 1), 0)
167 + NVL(ceil(x_pw_applicant_type + 1), 0)
168 + NVL(ceil(x_nw_dependent_type + 1), 0)
169 + NVL(ceil(x_nw_retiree_type + 1), 0)
170 + NVL(ceil(x_nw_beneficiary_type + 1), 0)
171 + NVL(ceil(x_srviving_fmly_mbr_type + 1), 0)
172 + NVL(ceil(x_srviving_spouse_type + 1), 0)
173 + NVL(ceil(x_ex_applicant_type + 1), 0)
174 + NVL(ceil(x_other_type + 1), 0)
175 + NVL(ceil(x_participant_type + 1), 0)
176 + NVL(ceil(x_former_spouse_type + 1), 0)
177 + NVL(ceil(x_former_fmly_mbr_type + 1), 0)
178 + NVL(ceil(x_last_update_date + 1), 0)
179 + NVL(ceil(x_creation_date + 1), 0)
180 + (26 * NVL(ceil(x_avg_flag + 1), 0));
181
182 /* TOTAL */
183
184 p_avg_row_length := x_total_pty;
185
186 END estimate_row_length;
187
188 END edw_hr_prsn_typ_m_sizing;