PACKAGE BODY: APPS.EDW_MRP_DM_CLS_M_SIZE

Source

1 PACKAGE BODY EDW_MRP_DM_CLS_M_SIZE AS
2 /* $Header: ISCSGD0B.pls 115.3 2002/12/19 00:44:59 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='DEMAND_CLASS'
21          AND fnd.language = userenv('LANG')
22          AND fnd.view_application_id = 3
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_DEMAND_CLASS_PK		NUMBER;
44     x_ALL_FK			NUMBER;
45     x_DEMAND_CLASS		NUMBER;
46     x_DESCRIPTION		NUMBER;
47     x_DEMAND_CLASS_DP		NUMBER;
48     x_NAME			NUMBER;
49 
50       CURSOR c_1 IS
51          SELECT nvl(avg(nvl(vsize(lookup_code),0)),0),
52    	        nvl(avg(nvl(vsize(description),0)),0),
53 	        nvl(avg(nvl(vsize(meaning),0)),0)
54          FROM fnd_lookup_values
55          WHERE last_update_date BETWEEN p_from_date AND p_to_date;
56 
57    BEGIN
58 
59       OPEN c_1;
60          FETCH c_1 INTO x_DEMAND_CLASS_PK, x_DESCRIPTION, x_DEMAND_CLASS_DP;
61       CLOSE c_1;
62 
63       x_DEMAND_CLASS := x_DEMAND_CLASS_PK;
64       x_NAME := x_DEMAND_CLASS_DP;
65 
66       x_total := 3 + x_total + ceil(x_DEMAND_CLASS_PK + 1) + ceil( x_DEMAND_CLASS + 1) +
67 	         ceil(x_DESCRIPTION + 1) + ceil(x_DEMAND_CLASS_DP + 1) + ceil(x_NAME + 1);
68 
69       p_avg_row_len := x_total;
70 
71    Exception When others then
72       rollback;
73 
74    END;
75 
76 END EDW_MRP_DM_CLS_M_SIZE;