PACKAGE BODY: APPS.BIM_EDW_CMPFRCST_F_SIZE

Source

1 PACKAGE BODY BIM_EDW_CMPFRCST_F_SIZE AS
2 /* $Header: bimszfcb.pls 115.0 2001/03/14 12:02:05 pkm ship       $*/
3 
4 PROCEDURE cnt_rows(p_from_date DATE,
5                    p_to_date DATE,
6                    p_num_rows OUT NUMBER) IS
7 
8 
9  -- v_num_rows        NUMBER := 0;
10 last_date DATE := TO_DATE( '1000/01/01','YYYY/MM/DD' );
11 
12 CURSOR c_cnt_rows IS
13    select sum(cnt)
14    from (
15         select count(*) cnt
16 	FROM AMS_CAMPAIGNS_ALL_B AMC ,
17 	 AMS_ACT_METRICS_ALL AAM ,
18 	 AMS_METRICS_ALL_B AMT ,
19 	 BIM_EDW_CMPFRCST_INC INC ,
20 	 EDW_LOCAL_INSTANCE INST
21 	WHERE AMC.CAMPAIGN_ID = AAM.ACT_METRIC_USED_BY_ID
22 	 AND AAM.ARC_ACT_METRIC_USED_BY = 'CAMP'
23 	 AND AAM.METRIC_ID = AMT.METRIC_ID
24 	 AND AMT.SUMMARY_METRIC_ID IS NULL AND AMT.METRIC_CATEGORY = 901
25 	 AND AMC.STATUS_CODE NOT IN ( 'NEW',
26 	 'PLANNING' ) AND AMC.SHOW_CAMPAIGN_FLAG = 'Y'
27 	 AND AMC.CAMPAIGN_ID = INC.PRIMARY_KEY
28 	 AND  ( AMC.LAST_UPDATE_DATE > last_date
29 	 OR  AAM.LAST_UPDATE_DATE > last_date
30 	 )  and
31 	AMC.last_update_date between
32         p_from_date  and  p_to_date
33 	UNION ALL
34 	select count(*) cnt
35 	FROM AMS_CAMPAIGNS_ALL_B AMC ,
36 	 AMS_ACT_METRICS_ALL AAM ,
37 	 AMS_METRICS_ALL_B AMT ,
38 	 BIM_EDW_CMPFRCST_INC INC ,
39 	 EDW_LOCAL_INSTANCE INST
40 	WHERE AMC.CAMPAIGN_ID = AAM.ACT_METRIC_USED_BY_ID
41 	 AND AAM.ARC_ACT_METRIC_USED_BY = 'CAMP'
42 	 AND AAM.METRIC_ID = AMT.METRIC_ID AND AMT.SUMMARY_METRIC_ID IS NULL
43 	 AND AMT.METRIC_CATEGORY = 902 AND AMC.STATUS_CODE NOT IN ( 'NEW',
44 	 'PLANNING' )
45 	 AND AMC.SHOW_CAMPAIGN_FLAG = 'Y' AND AMC.CAMPAIGN_ID = INC.PRIMARY_KEY
46 	 AND (  AMC.LAST_UPDATE_DATE > last_date
47 	 OR  AAM.LAST_UPDATE_DATE > last_date )
48         );
49 
50 
51 BEGIN
52 
53   dbms_output.enable(1000000);
54 
55   OPEN c_cnt_rows;
56        FETCH c_cnt_rows INTO p_num_rows;
57   CLOSE c_cnt_rows;
58 
59 
60     dbms_output.put_line('The number of rows is: ' || to_char(p_num_rows));
61 END;  -- procedure cnt_rows.
62 
63 
64 PROCEDURE est_row_len(p_from_date DATE,
65                       p_to_date DATE,
66                       p_avg_row_len OUT NUMBER) IS
67 
68  x_date                 number := 7;
69  x_total                number := 0;
70  x_constant             number := 6;
71  x_CAMPAIGN_ID NUMBER;
72  x_CHANNEL_ID NUMBER;
73  x_TRANSACTION_CURRENCY_CODE NUMBER;
74  x_ORG_ID NUMBER;
75 
76  x_INSTANCE NUMBER;
77 
78 
79   CURSOR c_1 IS
80 	SELECT
81 	avg(nvl(vsize( CAMPAIGN_ID), 0)),
82 	avg(nvl(vsize( CHANNEL_ID), 0)),
83 	avg(nvl(vsize( TRANSACTION_CURRENCY_CODE), 0)),
84 	avg(nvl(vsize( ORG_ID), 0))
85 	FROM AMS_CAMPAIGNS_ALL_B
86         where last_update_date between
87         p_from_date  and  p_to_date;
88 
89 
90 
91 
92   CURSOR c_2 IS
93 	select
94 	 avg(nvl(vsize(INSTANCE_CODE), 0))
95 	 from EDW_LOCAL_INSTANCE ;
96 
97 
98 
99   BEGIN
100 
101     dbms_output.enable(1000000);
102 
103     OPEN c_1;
104       FETCH c_1 INTO
105 	 x_CAMPAIGN_ID,
106 	 x_CHANNEL_ID,
107 	 x_TRANSACTION_CURRENCY_CODE,
108 	 x_ORG_ID;
109 
110 
111     CLOSE c_1;
112 
113     x_total := 150  +
114 		ceil(	 x_CAMPAIGN_ID +1) +
115 		ceil(	 x_CHANNEL_ID +1) +
116 		ceil(	 x_TRANSACTION_CURRENCY_CODE +1) +
117 		ceil(	 x_ORG_ID +1);
118 
119 
120 
121 
122 
123     OPEN c_2;
124       FETCH c_2 INTO  x_INSTANCE;
125     CLOSE c_2;
126 
127     x_total := x_total + 8*ceil(x_INSTANCE + 1);
128 
129     x_total := x_total + 20*(x_constant + 1);
130 
131     -- dbms_output.put_line('     ');
132     dbms_output.put_line('The average row length is : ' || to_char(x_total));
133 
134   p_avg_row_len := x_total;
135 
136   END;  -- procedure est_row_len.
137 
138 END;  -- package body BIM_EDW_CMPFRCST_F_SIZE