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