PACKAGE BODY: APPS.EDW_HR_REASON_M_SIZING

Source

1 PACKAGE BODY edw_hr_reason_m_sizing AS
2 /* $Header: hriezrsn.pkb 120.1 2005/06/08 02:50:11 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(hrl.meaning) total
15   FROM hr_lookups          hrl
16   ,(select evt.reason_type from hri_edw_event_hrchys evt
17   where not exists (select 1 from hri_edw_event_hrchys dummy
18                     where dummy.rowid > evt.rowid
19                     and evt.reason_type = dummy.reason_type)) types
20   WHERE NVL(hrl.last_update_date, to_date('01-01-2000','DD-MM-YYYY'))
21   BETWEEN p_from_date AND p_to_date
22   AND hrl.lookup_type = types.reason_type;
23 
24 
25 
26 BEGIN
27 
28   OPEN row_count_cur;
29   FETCH row_count_cur INTO p_row_count;
30   CLOSE row_count_cur;
31 
32 END count_source_rows;
33 
34 /******************************************************************************/
35 /* Estimates row lengths.                                                     */
36 /******************************************************************************/
37 PROCEDURE estimate_row_length( p_from_date        IN  DATE,
38 			       p_to_date          IN  DATE,
39 			       p_avg_row_length   OUT NOCOPY NUMBER )
40 
41 IS
42 
43   x_date		NUMBER :=7;
44 
45   x_total_reason        NUMBER;
46 
47 /* Reason Level */
48   x_reason_pk           NUMBER :=0;
49   x_instance            NUMBER :=0;
50   x_name                NUMBER :=0;
51   x_reason_dp           NUMBER :=0;
52   x_lookup_type         NUMBER :=0;
53   x_lookup_code         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 
63  CURSOR rsn_cur IS
64   SELECT
65    avg(nvl(vsize(hrl.meaning),0))
66   ,avg(nvl(vsize(hrl.meaning),0))
67   ,avg(nvl(vsize(hrl.lookup_type),0))
68   ,avg(nvl(vsize(hrl.lookup_code),0))
69 FROM hr_lookups          hrl
70   ,(select evt.reason_type from hri_edw_event_hrchys evt
71   where not exists (select 1 from hri_edw_event_hrchys dummy
72                     where dummy.rowid > evt.rowid
73                     and evt.reason_type = dummy.reason_type)) types
74   WHERE NVL(hrl.last_update_date, to_date('01-01-2000','DD-MM-YYYY'))
75   BETWEEN p_from_date AND p_to_date
76   AND hrl.lookup_type = types.reason_type;
77 
78 BEGIN
79 
80   OPEN inst_cur;
81   FETCH inst_cur INTO x_instance;
82   CLOSE inst_cur;
83 
84   OPEN rsn_cur;
85   FETCH rsn_cur INTO
86    x_name
87   ,x_reason_dp
88   ,x_lookup_type
89   ,x_lookup_code;
90   CLOSE rsn_cur;
91 
92 /* Reason Level */
93 
94   x_reason_pk := x_lookup_type + x_lookup_code + x_instance;
95 
96 
97   x_total_reason :=  NVL(ceil(x_reason_pk + 1), 0)
98 		     + NVL(ceil(x_instance + 1), 0)
99 		     + NVL(ceil(x_name + 1), 0)
100 		     + NVL(ceil(x_reason_dp + 1), 0)
101 		     + NVL(ceil(x_lookup_type + 1), 0)
102 		     + NVL(ceil(x_lookup_code + 1), 0)
103 		     + NVL(ceil(x_last_update_date + 1), 0)
104  	 	     + NVL(ceil(x_creation_date + 1), 0);
105 
106 /* TOTAL */
107 
108   p_avg_row_length :=  x_total_reason;
109 
110 END estimate_row_length;
111 
112 END edw_hr_reason_m_sizing;