PACKAGE BODY: APPS.OPI_EDW_OPI_ACTV_M_SZ

Source

1 PACKAGE BODY OPI_EDW_OPI_ACTV_M_SZ AS
2 /* $Header: OPIOACZB.pls 120.1 2005/06/08 18:33:02 appldev  $*/
3 
4 PROCEDURE cnt_rows(p_from_date DATE,
5                    p_to_date DATE,
6                    p_num_rows OUT NOCOPY NUMBER) IS
7 CURSOR c_cnt_rows IS
8 	select count(*) cnt
9 	FROM
10 	CST_ACTIVITIES
11 	where  last_update_date between p_from_date and p_to_date ;
12 BEGIN
13 
14   OPEN c_cnt_rows;
15        FETCH c_cnt_rows INTO p_num_rows;
16   CLOSE c_cnt_rows;
17 
18 END;  -- procedure cnt_rows.
19 
20 
21 PROCEDURE est_row_len(p_from_date DATE,
22                    p_to_date DATE,
23                    p_est_row_len OUT NOCOPY NUMBER) IS
24 
25 	x_ACTV_PK NUMBER ;
26 	x_ACTV_DP NUMBER ;
27 	x_ACTV_CODE NUMBER ;
28 	x_NAME NUMBER ;
29 	x_ACTV_NAME NUMBER ;
30 	x_DESCRIPTION NUMBER ;
31 	x_VALUE_ADDED NUMBER ;
32 	x_LAST_UPDATE_DATE NUMBER ;
33 	x_CREATION_DATE NUMBER ;
34 	x_INSTANCE NUMBER ;
35 
36 	x_total NUMBER := 0;
37 
38 CURSOR c_1  IS
39 	SELECT
40 	-- ACTV_PK need to add instance_code
41 	avg(nvl(vsize(ACTIVITY_ID), 0)),
42 	-- EDW_LOT_DP
43 	-- ACTV_CODE
44 	avg(nvl(vsize(ACTIVITY), 0)),
45 	-- Name
46 	avg(nvl(vsize(ACTIVITY), 0)) ,
47 	-- ACTV_NAME
48 	avg(nvl(vsize(ACTIVITY), 0)),
49 	-- DESCRIPTION
50 	avg(nvl(vsize(DESCRIPTION), 0)) ,
51 	-- COST_ANALYSIS_CODE
52 	-- VALUE_ADDED ??
53 	avg(nvl(vsize(VALUE_ADDED_ACTIVITY_FLAG), 0)) ,
54 	-- LAST_UPDATE_DATE
55 	avg(nvl(vsize(LAST_UPDATE_DATE), 0)),
56 	-- CREATION_DATE
57 	avg(nvl(vsize(CREATION_DATE), 0))
58 	FROM
59 	CST_ACTIVITIES
60 	WHERE
61 	last_update_date between p_from_date and p_to_date ;
62 
63   CURSOR c_2 IS
64 	SELECT
65 		avg(nvl(vsize(instance_code), 0))
66 	FROM	EDW_LOCAL_INSTANCE ;
67         -- WHERE last_update_date between
68        --  p_from_date  and  p_to_date;
69 
70 BEGIN
71 
72   OPEN c_1;
73        FETCH c_1 INTO
74 	x_ACTV_PK ,
75 	x_ACTV_CODE ,
76 	x_NAME  ,
77 	x_ACTV_NAME ,
78 	x_DESCRIPTION ,
79 	x_VALUE_ADDED ,
80 	x_LAST_UPDATE_DATE,
81 	x_CREATION_DATE  ;
82 
83   CLOSE c_1;
84 
85     x_total := 3 +
86 	    x_total +
87 	ceil(x_ACTV_PK + 1) +
88 	ceil(x_ACTV_CODE + 1) +
89 	ceil(x_NAME + 1) +
90 	ceil(x_ACTV_NAME + 1) +
91 	ceil(x_VALUE_ADDED + 1 ) +
92 	ceil(x_DESCRIPTION+ 1) +
93 	ceil(x_LAST_UPDATE_DATE + 1) +
94 	ceil(x_CREATION_DATE+ 1)   ;
95 
96   OPEN c_2;
97        FETCH c_2 INTO
98 	x_INSTANCE ;
99   CLOSE c_2;
100 
101   x_total := x_total +
102 	ceil(x_INSTANCE + 1) ;
103 
104 	p_est_row_len := x_total ;
105 
106 END ;
107 
108 END OPI_EDW_OPI_ACTV_M_SZ ;  -- procedure est_row_len.