PACKAGE BODY: APPS.EDW_OE_SLCHNL_M_SIZE

Source

1 PACKAGE BODY EDW_OE_SLCHNL_M_SIZE AS
2 /* $Header: ISCSGD3B.pls 115.2 2002/12/19 00:46:07 scheung ship $ */
3 
4    /* ------------------------------------------
5       PROCEDURE NAME   : cnt_rows
6       INPUT PARAMETERS : p_from_date, p_to_date
7       OUTPUT PARAMETERS: p_num_rows
8       DESCRIPTION      : Count the number of rows
9       ------------------------------------------- */
10 
11    PROCEDURE cnt_rows(p_from_date DATE,
12                       p_to_date DATE,
13                       p_num_rows OUT NOCOPY NUMBER) IS
14 
15    BEGIN
16 
17       SELECT count(*)
18         INTO p_num_rows
19         FROM fnd_lookup_values fnd
20        WHERE fnd.lookup_type='SALES_CHANNEL'
21 	 AND fnd.language = userenv('LANG')
22 	 AND fnd.view_application_id = 660
23 	 AND fnd.security_group_id = 0
24          AND fnd.last_update_date BETWEEN p_from_date AND p_to_date;
25 
26    Exception When others then
27       rollback;
28 
29    END;
30 
31    /* ------------------------------------------
32       PROCEDURE NAME   : est_row_len
33       INPUT PARAMETERS : p_from_date, p_to_date
34       OUTPUT PARAMETERS: p_avg_row_len
35       DESCRIPTION      : Estimate input_f
36       ------------------------------------------ */
37 
38    PROCEDURE est_row_len(p_from_date DATE,
39                          p_to_date DATE,
40                          p_avg_row_len OUT NOCOPY NUMBER) IS
41 
42     x_total                number := 0;
43     x_SALES_CHANNEL_PK		NUMBER;
44     x_SALES_CHANNEL_CODE	NUMBER;
45     x_SALES_CHANNEL_NAME	NUMBER;
46     x_ENABLED_FLAG		NUMBER;
47     x_ACTIVE_FROM_DATE		NUMBER;
48     x_ACTIVE_TO_DATE		NUMBER;
49     x_SALES_CHANNEL_DP		NUMBER;
50     x_NAME			NUMBER;
51     x_CREATION_DATE		NUMBER;
52     x_LAST_UPDATE_DATE		NUMBER;
53 
54       CURSOR c_1 IS
55          SELECT nvl(avg(nvl(vsize(lookup_code),0)),0),
56    	        nvl(avg(nvl(vsize(description),0)),0),
57 		nvl(avg(nvl(vsize(enabled_flag),0)),0),
58 	        nvl(avg(nvl(vsize(meaning),0)),0),
59 		nvl(avg(nvl(vsize(start_date_active),0)),0),
60 		nvl(avg(nvl(vsize(end_date_active),0)),0),
61 		nvl(avg(nvl(vsize(creation_date),0)),0),
62 		nvl(avg(nvl(vsize(last_update_date),0)),0)
63          FROM fnd_lookup_values
64          WHERE last_update_date BETWEEN p_from_date AND p_to_date;
65 
66    BEGIN
67 
68       OPEN c_1;
69          FETCH c_1 INTO x_SALES_CHANNEL_PK, x_SALES_CHANNEL_NAME, x_ENABLED_FLAG,
70 			x_SALES_CHANNEL_DP, x_ACTIVE_FROM_DATE, x_ACTIVE_TO_DATE,
71 			x_CREATION_DATE, x_LAST_UPDATE_DATE;
72       CLOSE c_1;
73 
74       x_SALES_CHANNEL_CODE := x_SALES_CHANNEL_PK;
75       x_NAME := x_SALES_CHANNEL_DP;
76 
77       x_total := 3 + x_total + ceil(x_SALES_CHANNEL_PK + 1) + ceil(x_SALES_CHANNEL_CODE + 1) +
78 	         ceil(x_SALES_CHANNEL_NAME + 1) + ceil(x_ENABLED_FLAG + 1) + ceil(x_ACTIVE_FROM_DATE + 1) +
79 		 ceil(x_ACTIVE_TO_DATE + 1) + ceil(x_SALES_CHANNEL_DP + 1) + ceil(x_NAME + 1) +
80 		 ceil(x_CREATION_DATE + 1) + ceil(x_LAST_UPDATE_DATE + 1);
81 
82       p_avg_row_len := x_total;
83 
84   Exception When others then
85      rollback;
86 
87   END;
88 
89 END EDW_OE_SLCHNL_M_SIZE;