[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_RES_UTIL_FOPM_SZ
Source
1 PACKAGE BODY OPI_EDW_RES_UTIL_FOPM_SZ AS
2 /* $Header: OPIPRUZB.pls 120.1 2005/06/09 16:21:49 appldev $*/
3
4 PROCEDURE CNT_ROWS(p_from_date DATE,
5 p_to_date DATE,
6 p_num_rows OUT NOCOPY NUMBER) IS
7
8 CURSOR c_cnt_rows IS
9 select count(*)
10 FROM
11
12 (
13 select
14 ORGN_CODE,
15 trunc(trans_date) trans_date ,
16 resources,sum(resource_usage) RSRC_USAGE
17 FROM
18 PC_TRAN_PND
19 WHERE completed_ind=1
20 GROUP BY
21 ORGN_CODE,trunc(trans_date),resources
22 ) PCPND,
23 (
24 select
25 a.ORGN_CODE,
26 a.resources,
27 trunc(trans_date) trans_date,
28 AVG(DAILY_AVAIL_USE) DAILY_AVAIL,
29 MAX(a.USAGE_UM) USAGE_UM,
30 MAX(GREATEST(a.LAST_UPDATE_DATE,TRANS_DATE)) LAST_UPDATE_DATE
31 FROM
32 (
33 SELECT
34 RS.ORGN_CODE,
35 RS.RESOURCES,
36 RD.DAILY_AVAIL_USE,
37 decode(RD.USAGE_UM,NULL,RS.STD_USAGE_UM,RD.USAGE_UM) USAGE_UM,
38 RS.LAST_UPDATE_DATE
39 FROM
40 (SELECT ORG.ORGN_CODE,
41 RSRC.RESOURCES,
42 RSRC.STD_USAGE_UM,
43 GREATEST(ORG.LAST_UPDATE_DATE,RSRC.LAST_UPDATE_DATE) LAST_UPDATE_DATE
44 FROM SY_ORGN_MST ORG,
45 CR_RSRC_MST RSRC
46 ) RS,
47 CR_RSRC_DTL RD
48 WHERE RS.RESOURCES = RD.RESOURCES(+)
49 AND RS.ORGN_CODE = RD.ORGN_CODE(+)
50 ) a,
51 PC_TRAN_PND b
52 WHERE b.completed_ind=1
53 GROUP BY
54 A.ORGN_CODE,a.resources,trunc(trans_date)
55 ) RSRC,
56 EDW_LOCAL_INSTANCE inst,
57 SY_ORGN_MST SY,
58 GL_PLCY_MST GPM,
59 OPI_PMI_UOMS_MST UOM
60 WHERE PCPND.orgn_code(+)= RSRC.ORGN_CODE and
61 PCPND.trans_date(+)=RSRC.trans_date and
62 PCPND.resources(+) = RSRC.resources and
63 RSRC.ORGN_CODE = SY.ORGN_CODE AND
64 SY.CO_CODE = GPM.CO_CODE AND
65 UOM.UM_CODE = RSRC.USAGE_UM
66 AND RSRC.TRANS_DATE between p_from_date and p_to_date;
67 BEGIN
68
69 OPEN c_cnt_rows;
70 FETCH c_cnt_rows INTO p_num_rows;
71 CLOSE c_cnt_rows;
72
73 END CNT_ROWS;
74
75
76 PROCEDURE EST_ROW_LEN(p_from_date DATE,
77 p_to_date DATE,
78 p_avg_row_len OUT NOCOPY NUMBER) IS
79 x_total number := 0;
80 x_constant number := 6;
81 x_date number :=7;
82
83 X_RES_UTIL_PK NUMBER;
84 X_LOCATOR_FK NUMBER;
85 X_RSRC_FK NUMBER;
86 X_TRX_DATE_FK NUMBER;
87 X_UOM_FK NUMBER;
88 X_INSTANCE_FK NUMBER;
89 X_ACT_RES_USAGE NUMBER;
90 X_AVAIL_RES NUMBER;
91
92
93
94 CURSOR RES_UTIL IS
95 SELECT
96 avg(nvl(vsize(RSRC.TRANS_DATE||'-'||rsrc.ORGN_CODE||'-'||RSRC.RESOURCES||'-OPM'),0)) RES_UTIL_PK,
97 avg(nvl(vsize(RESOURCE_USAGE),0))
98 FROM
99 PC_TRAN_PND RSRC
100 WHERE
101 last_update_date between
102 p_from_date and p_to_date;
103
104
105 CURSOR INST_PK IS
106 SELECT
107 avg(nvl(vsize(instance_code), 0))
108 FROM EDW_LOCAL_INSTANCE ;
109
110
111 CURSOR RSRC_PK is
112 SELECT avg(nvl(vsize(RESOURCES||'-OPM'), 0))
113 FROM CR_RSRC_MST;
114
115 CURSOR UOM_PK is
116 SELECT avg(nvl(vsize(UOM_CODE), 0))
117 FROM OPI_PMI_UOMS_MST;
118 CURSOR LOC_PK is
119 SELECT avg(nvl(vsize(ORGN_CODE), 0))
120 FROM SY_ORGN_MST;
121
122 CURSOR TRX_DATE_PK is
123 SELECT avg(nvl(vsize(substr(edw_time_pkg.cal_day_fk
124 (BH.TRANS_DATE,SOB.SET_OF_BOOKS_ID),1,120)),0))
125 FROM
126 PC_TRAN_PND BH,
127 SY_ORGN_MST OM,
128 GL_PLCY_MST PM,
129 GL_SETS_OF_BOOKS SOB
130 WHERE
131 BH.ORGN_CODE = OM.ORGN_CODE
132 AND OM.CO_CODE = PM.co_code
133 AND PM.SET_OF_BOOKS_NAME=SOB.name;
134
135
136
137 BEGIN
138
139 OPEN RES_UTIL;
140 FETCH RES_UTIL INTO
141 X_RES_UTIL_PK,
142 X_ACT_RES_USAGE;
143 CLOSE RES_UTIL;
144
145 x_total := x_date +
146 x_total +
147 ceil(X_RES_UTIL_PK+1) +
148 2*ceil(X_ACT_RES_USAGE+1);
149
150
151 OPEN TRX_DATE_PK;
152 FETCH TRX_DATE_PK INTO x_TRX_DATE_FK;
153 CLOSE TRX_DATE_PK;
154 x_total := x_total + ceil(x_TRX_DATE_FK + 1);
155
156 OPEN INST_PK;
157 FETCH INST_PK INTO x_INSTANCE_FK;
158 CLOSE INST_PK;
159 x_total := x_total + ceil(x_INSTANCE_FK + 1);
160
161 OPEN UOM_PK ;
162 FETCH UOM_PK INTO x_UOM_FK;
163 CLOSE UOM_PK ;
164 x_total := x_total + ceil(x_UOM_FK + 1);
165
166 OPEN RSRC_PK ;
167 FETCH RSRC_PK INTO x_RSRC_FK;
168 CLOSE RSRC_PK ;
169 x_total := x_total + ceil(x_RSRC_FK + 1);
170
171 OPEN LOC_PK ;
172 FETCH LOC_PK INTO x_LOCATOR_FK;
173 CLOSE LOC_PK ;
174 x_total := x_total + ceil(x_LOCATOR_FK + 1);
175
176 -- Miscellaneous
177 x_total := x_total + 3 * ceil(x_INSTANCE_FK + 1);
178
179 p_avg_row_len := x_total;
180
181 END; -- procedure est_row_len.
182
183 END OPI_EDW_RES_UTIL_FOPM_SZ;