[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_GRADE_M_SIZING
Source
1 PACKAGE BODY edw_hr_grade_m_sizing AS
2 /* $Header: hriezgrd.pkb 120.1 2005/06/08 02:45:28 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(gra.grade_id) total
15 FROM per_grades gra
16 WHERE NVL(gra.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_grade NUMBER;
39
40 /* Grade Level */
41 x_grade_pk NUMBER :=0;
42 x_instance NUMBER :=0;
43 x_name NUMBER :=0;
44 x_grade_dp NUMBER :=0;
45 x_grade_date_from NUMBER :=x_date;
46 x_grade_date_to NUMBER :=x_date;
47 x_grade_sequence NUMBER :=0;
48 x_business_group NUMBER :=0;
49 x_grade_id NUMBER :=0;
50 x_business_group_id NUMBER :=0;
51 x_grade_definition_id NUMBER :=0;
52 x_last_update_date NUMBER := x_date;
53 x_creation_date NUMBER := x_date;
54
55 l_grade_name NUMBER :=0;
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 grade_cur IS
63 SELECT
64 avg(nvl(vsize(sequence),0))
65 ,avg(nvl(vsize(name),0))
66 ,avg(nvl(vsize(grade_id),0))
67 ,avg(nvl(vsize(business_group_id),0))
68 FROM per_grades
69 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
70
71 CURSOR bus_cur IS
72 SELECT
73 avg(nvl(vsize(name),0))
74 FROM hr_all_organization_units
75 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
76
77 CURSOR gra_def_cur IS
78 SELECT
79 avg(nvl(vsize(grade_definition_id),0))
80 FROM per_grade_definitions
81 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
82
83 BEGIN
84
85 OPEN inst_cur;
86 FETCH inst_cur INTO x_instance;
87 CLOSE inst_cur;
88
89 OPEN grade_cur;
90 FETCH grade_cur INTO x_grade_sequence, l_grade_name, x_grade_id, x_business_group_id;
91 CLOSE grade_cur;
92
93 OPEN bus_cur;
94 FETCH bus_cur INTO x_business_group;
95 CLOSE bus_cur;
96
97 OPEN gra_def_cur;
98 FETCH gra_def_cur INTO x_grade_definition_id;
99 CLOSE gra_def_cur;
100
101 /* Grade Level */
102
103 x_grade_pk := x_grade_id + x_instance;
104 x_name := l_grade_name + x_instance;
105 x_grade_dp := x_name;
106
107 x_total_grade := NVL(ceil(x_grade_pk + 1), 0 )
108 + NVL(ceil(x_instance + 1), 0 )
109 + NVL(ceil(x_name + 1), 0 )
110 + NVL(ceil(x_grade_dp + 1), 0 )
111 + NVL(ceil(x_grade_date_from + 1), 0 )
112 + NVL(ceil(x_grade_date_to + 1), 0 )
113 + NVL(ceil(x_grade_sequence + 1), 0 )
114 + NVL(ceil(x_business_group + 1), 0 )
115 + NVL(ceil(x_grade_id + 1), 0 )
116 + NVL(ceil(x_business_group_id + 1), 0 )
117 + NVL(ceil(x_grade_definition_id + 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_grade;
124
125 END estimate_row_length;
126
127 END edw_hr_grade_m_sizing;