[Home] [Help]
PACKAGE BODY: APPS.EDW_OPI_OPRN_MOPM_SZ
Source
1 PACKAGE BODY EDW_OPI_OPRN_MOPM_SZ AS
2 /* $Header: OPIPOPZB.pls 120.1 2005/06/09 16:01:11 appldev $*/
3
4 -- procedure to count Operation Dimension rows.
5
6 PROCEDURE cnt_rows (p_from_date IN DATE,
7 p_to_date IN DATE,
8 p_num_rows OUT NOCOPY NUMBER) IS
9 BEGIN
10 SELECT sum(cnt) into p_num_rows
11 FROM (SELECT count(*) cnt
12 FROM FM_OPRN_CLS
13 WHERE last_update_date between p_from_date and p_to_date
14 UNION ALL
15 SELECT count(*) cnt
16 FROM FM_OPRN_MST
17 WHERE last_update_date between p_from_date and p_to_date);
18 -- dbms_output.put_line ('Number of rows : '||p_num_rows);
19 EXCEPTION
20 WHEN OTHERS THEN
21 p_num_rows := 0;
22 END cnt_rows;
23
24 -- procedure to get average row length Operation Dimension rows.
25
26 PROCEDURE est_row_len (p_from_date IN DATE,
27 p_to_date IN DATE,
28 p_avg_row_len OUT NOCOPY NUMBER) IS
29 x_date number := 7;
30 x_total number := 0;
31 x_constant number := 6;
32 x_INSTANCE NUMBER := 0;
33 x_last_update_date NUMBER := x_date;
34 x_creation_date NUMBER := x_date;
35
36 -- Operation Class Level Attributes
37
38 x_OPRC_PK NUMBER := 0;
39 x_OPRC_DP NUMBER := 0;
40 x_OPRC_NAME NUMBER := 0;
41 x_OPRC_DESCRIPTION NUMBER := 0;
42 x_total_op_calss NUMBER := 0;
43
44
45 -- Operation Level Attributes
46
47 x_OPRN_PK NUMBER := 0;
48 x_OPRC_FK NUMBER := 0;
49 x_OPRN_DP NUMBER := 0;
50 x_NAME NUMBER := 0;
51 x_OPRN_NAME NUMBER := 0;
52 x_DESCRIPTION NUMBER := 0;
53 x_ORGN_CODE NUMBER := 0;
54 x_DEPARTMENT NUMBER := 0;
55 x_PROCESS_QTY_UOM NUMBER := 0;
56 x_total_operations NUMBER := 0;
57
58 -- Cursor to get instance code size
59
60 cursor CUR_INSTANCE_SIZE is
61 select avg(nvl(vsize(instance_code),0))
62 from edw_local_instance;
63
64 -- Cursor to Operation Class Level Attribute Sizes
65
66 CURSOR CUR_OPM_OPRN_CLS_SIZES IS
67 SELECT
68 avg(nvl(vsize(OPRN_CLASS||'-'||'-OPM'),0)),
69 avg(nvl(vsize('OPRC'),0)),
70 avg(nvl(vsize(OPRN_CLASS_DESC||'('||OPRN_CLASS||')'),0)),
71 avg(nvl(vsize(OPRN_CLASS_DESC),0))
72 FROM FM_OPRN_CLS
73 WHERE LAST_UPDATE_DATE BETWEEN P_FROM_DATE AND P_TO_DATE;
74
75 -- Cursor to get Operation Level Attribute sizes
76
77 CURSOR CUR_OPM_OPRN_SIZES IS
78 SELECT avg(nvl(vsize(OPRN_ID||'-'||'-OPM'),0)),
79 avg(nvl(vsize('OPRN'),0)),
80 avg(nvl(vsize(OPRN_NO),0)),
81 avg(nvl(vsize(OPRN_DESC),0)),
82 avg(nvl(vsize(PROCESS_QTY_UM),0))
83 FROM FM_OPRN_MST
84 WHERE LAST_UPDATE_DATE BETWEEN P_FROM_DATE AND P_TO_DATE;
85
86
87 BEGIN
88 OPEN CUR_INSTANCE_SIZE;
89 FETCH CUR_INSTANCE_SIZE INTO x_INSTANCE;
90 CLOSE CUR_INSTANCE_SIZE;
91 OPEN CUR_OPM_OPRN_CLS_SIZES;
92 FETCH CUR_OPM_OPRN_CLS_SIZES INTO x_OPRC_PK,x_OPRC_DP,x_OPRC_NAME,x_OPRC_DESCRIPTION;
93 CLOSE CUR_OPM_OPRN_CLS_SIZES;
94 x_total_op_calss := NVL(ceil(x_INSTANCE + 1), 0) +
95 NVL(ceil(x_OPRC_PK + 1), 0) +
96 NVL(ceil(x_OPRC_DP + 1), 0) +
97 NVL(ceil(x_OPRC_NAME+ 1), 0) +
98 NVL(ceil(x_OPRC_DESCRIPTION + 1), 0) +
99 NVL(ceil(x_creation_date + 1), 0) +
100 NVL(ceil(x_creation_date + 1), 0);
101 -- dbms_output.put_line ('Average Row Length for Operation Class IS : '||x_total_op_calss);
102
103 OPEN CUR_OPM_OPRN_SIZES;
104 FETCH CUR_OPM_OPRN_SIZES INTO x_OPRN_PK,x_OPRN_DP,x_NAME,x_DESCRIPTION,x_PROCESS_QTY_UOM;
105 CLOSE CUR_OPM_OPRN_SIZES;
106 x_OPRC_FK := x_OPRC_PK;
107 x_total_operations := 2 * NVL(ceil(x_INSTANCE + 1), 0) +
108 NVL(ceil(x_OPRC_FK + 1), 0) +
109 NVL(ceil(x_OPRN_PK + 1), 0) +
110 NVL(ceil(x_OPRN_DP + 1), 0) +
111 2 * NVL(ceil(x_NAME + 1), 0) +
112 NVL(ceil(x_DESCRIPTION + 1), 0) +
113 NVL(ceil(x_PROCESS_QTY_UOM + 1), 0) +
114 NVL(ceil(x_creation_date + 1), 0) +
115 NVL(ceil(x_creation_date + 1), 0);
116
117 p_avg_row_len := x_total_op_calss + x_total_operations;
118 -- dbms_output.put_line ('Average Row Length for Operation IS : '||x_total_operations);
119 -- dbms_output.put_line ('Average Row Length for Operation Dimension IS : '||p_avg_row_len);
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 p_avg_row_len := 0;
124 END est_row_len;
125 END EDW_OPI_OPRN_MOPM_SZ;
126