[Home] [Help]
PACKAGE BODY: APPS.BIM_EDW_OPRNTIES_F_SIZE
Source
1 PACKAGE BODY BIM_EDW_OPRNTIES_F_SIZE AS
2 /* $Header: bimszfob.pls 115.0 2001/03/14 12:02:38 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
11 CURSOR c_cnt_rows IS
12 select sum(cnt)
13 from (
14 select count(*) cnt
15 FROM AS_LEAD_LINES_ALL ASLL,
16 AS_LEADS_ALL ASL,
17 EDW_BIM_SOURCE_CODE_DETAILS BSCD,
18 BIM_EDW_OPRNTIES_INC INC,
19 EDW_LOCAL_INSTANCE INST
20 WHERE ASL.LEAD_ID = ASLL.LEAD_ID
21 AND ASLL.LEAD_LINE_ID = INC.PRIMARY_KEY
22 AND NVL(ASLL.SOURCE_PROMOTION_ID,
23 -999) = BSCD.SOURCE_CODE_ID
24 AND ( ( ASL.LAST_UPDATE_DATE > TO_DATE( '1000/01/01', 'YYYY/MM/DD' ) )
25 OR ( ASLL.LAST_UPDATE_DATE > TO_DATE( '1000/01/01','YYYY/MM/DD' ) ) )
26 and
27 ASL.last_update_date between
28 p_from_date and p_to_date
29 );
30
31 BEGIN
32
33 dbms_output.enable(1000000);
34
35 OPEN c_cnt_rows;
36 FETCH c_cnt_rows INTO p_num_rows;
37 CLOSE c_cnt_rows;
38
39
40 dbms_output.put_line('The number of rows is: ' || to_char(p_num_rows));
41 END; -- procedure cnt_rows.
42
43
44 PROCEDURE est_row_len(p_from_date DATE,
45 p_to_date DATE,
46 p_avg_row_len OUT NUMBER) IS
47
48 x_date number := 7;
49 x_total number := 0;
50 x_constant number := 6;
51
52 x_LEAD_LINE_ID NUMBER;
53 x_OFFER_ID NUMBER;
54 x_ORG_ID NUMBER;
55 x_TOTAL_AMOUNT NUMBER;
56 x_QUANTITY NUMBER;
57
58 x_CAMPAIGN_FK NUMBER;
59 x_EVENT_FK NUMBER;
60 x_MEDIA_CHANNEL_FK NUMBER;
61
62 x_STATUS_CODE NUMBER;
63 x_CUSTOMER_ID NUMBER;
64 x_CURRENCY_CODE NUMBER;
65 x_CHANNEL_CODE NUMBER;
66 x_ADRESS_ID NUMBER;
67
68 x_INSTANCE NUMBER;
69
70
71 CURSOR c_1 IS
72 SELECT
73 avg(nvl(vsize( LEAD_LINE_ID ), 0)),
74 avg(nvl(vsize( OFFER_ID ), 0)),
75 avg(nvl(vsize( ORG_ID ), 0)),
76 avg(nvl(vsize( TOTAL_AMOUNT ), 0)),
77 avg(nvl(vsize( QUANTITY ), 0))
78 FROM AS_LEAD_LINES_ALL
79 where last_update_date between
80 p_from_date and p_to_date;
81
82 CURSOR c_2 IS
83 select
84 avg(nvl(vsize(CAMPAIGN_FK), 0)),
85 avg(nvl(vsize(EVENT_FK), 0)),
86 avg(nvl(vsize(MEDIA_CHANNEL_FK), 0))
87 from EDW_BIM_SOURCE_CODE_DETAILS ;
88
89
90 CURSOR c_3 IS
91 select
92 avg(nvl(vsize(STATUS_CODE), 0)),
93 avg(nvl(vsize(CUSTOMER_ID), 0)),
94 avg(nvl(vsize(CURRENCY_CODE), 0)),
95 avg(nvl(vsize(CHANNEL_CODE), 0)),
96 avg(nvl(vsize(ADDRESS_ID), 0))
97 from AS_SALES_LEADS
98 where last_update_date between
99 p_from_date and p_to_date;
100
101
102 CURSOR c_4 IS
103 select
104 avg(nvl(vsize(INSTANCE_CODE), 0))
105 from EDW_LOCAL_INSTANCE ;
106
107
108
109 BEGIN
110
111 dbms_output.enable(1000000);
112
113 OPEN c_1;
114 FETCH c_1 INTO
115 x_LEAD_LINE_ID,
116 x_OFFER_ID,
117 x_ORG_ID,
118 x_TOTAL_AMOUNT,
119 x_QUANTITY;
120
121 CLOSE c_1;
122
123 x_total := 100 +
124 2*ceil( x_LEAD_LINE_ID +1) +
125 ceil( x_OFFER_ID +1) +
126 ceil( x_ORG_ID +1) +
127 ceil( x_TOTAL_AMOUNT +1) +
128 ceil( x_QUANTITY +1) ;
129
130 OPEN c_2;
131 FETCH c_2 INTO
132 x_CAMPAIGN_FK,
133 x_EVENT_FK,
134 x_MEDIA_CHANNEL_FK;
135 CLOSE c_2;
136
137 x_total := x_total +
138 ceil(x_CAMPAIGN_FK + 1) +
139 ceil(x_EVENT_FK + 1) +
140 ceil(x_MEDIA_CHANNEL_FK + 1) ;
141
142 OPEN c_3;
143 FETCH c_3 INTO
144 x_STATUS_CODE,
145 x_CUSTOMER_ID,
146 x_CURRENCY_CODE,
147 x_CHANNEL_CODE,
148 x_ADRESS_ID;
149 CLOSE c_3;
150
151 x_total := x_total +
152 ceil(x_STATUS_CODE + 1) +
153 ceil(x_CUSTOMER_ID + 1) +
154 ceil(x_CURRENCY_CODE + 1) +
155 ceil(x_CHANNEL_CODE + 1) +
156 2*ceil(x_ADRESS_ID + 1) ;
157
158
159 OPEN c_4;
160 FETCH c_4 INTO x_INSTANCE;
161 CLOSE c_4;
162
163 x_total := x_total + 16*ceil(x_INSTANCE + 1);
164
165 x_total := x_total + 15*(x_constant + 1);
166
167 -- dbms_output.put_line(' ');
168 dbms_output.put_line('The average row length is : ' || to_char(x_total));
169
170 p_avg_row_len := x_total;
171
172 END; -- procedure est_row_len.
173
174 END; -- package body BIM_EDW_OPRNTIES_F_SIZE