[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_JOB_RSRC_FOPM_SZ
Source
1 PACKAGE BODY OPI_EDW_JOB_RSRC_FOPM_SZ AS
2 /* $Header: OPIPJRZB.pls 120.1 2005/06/07 03:37:59 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 PM_BTCH_HDR BH,
12 PM_MATL_DTL BD,
13 IC_ITEM_MST IM,
14 (SELECT
15 POD.BATCH_ID,
16 PBH.PLANT_CODE,
17 POD.ACTIVITY,
18 POD.RESOURCES,
19 POD.OPRN_LINE_ID,
20 POD.BATCHSTEP_NO,
21 POD.BATCHSTEPLINE_ID,
22 POD.USAGE_UM,
23 POD.ACTUAL_CMPLT_DATE,
24 POD.ACTUAL_RSRC_COUNT,
25 POD.ACTUAL_RSRC_QTY,
26 POD.ACTUAL_RSRC_USAGE,
27 POD.ACTUAL_START_DATE,
28 POD.PLAN_CMPLT_DATE,
29 POD.PLAN_RSRC_COUNT,
30 POD.PLAN_RSRC_QTY,
31 POD.PLAN_RSRC_USAGE,
32 POD.PLAN_START_DATE,
33 POD.LAST_UPDATE_DATE
34 FROM
35 PM_OPRN_DTL POD,
36 PM_BTCH_HDR PBH
37 WHERE POD.BATCH_ID=PBH.BATCH_ID
38 ) BR,
39 CR_RSRC_DTL CR,
40 SY_ORGN_MST OM,
41 GL_PLCY_MST PM,
42 FM_OPRN_MST OPRM,
43 FM_OPRN_DTL OPRD,
44 MTL_SYSTEM_ITEMS ITEM_FK_V,
45 IC_WHSE_MST IW,
46 GL_SETS_OF_BOOKS SOB,
47 EDW_LOCAL_INSTANCE inst,
48 OPI_PMI_UOMS_MST UOM
49 WHERE
50 BH.BATCH_ID = BR.BATCH_ID
51 AND BH.BATCH_ID = BD.BATCH_ID
52 AND BH.PLANT_CODE = OM.ORGN_CODE
53 AND BR.PLANT_CODE = CR.ORGN_CODE(+)
54 AND BR.RESOURCES = CR.RESOURCES(+)
55 AND BR.OPRN_LINE_ID = OPRD.OPRN_LINE_ID
56 AND OPRD.OPRN_ID = OPRM.OPRN_ID
57 AND OM.CO_CODE = PM.co_code
58 AND PM.SET_OF_BOOKS_NAME=SOB.name
59 AND BD.ITEM_ID = IM.ITEM_ID
60 AND BH.BATCH_STATUS in (3,4)
61 AND BD.LINE_TYPE=1 and BD.LINE_NO=1
62 AND ITEM_FK_V.SEGMENT1= IM.ITEM_NO
63 AND ITEM_FK_V.ORGANIZATION_ID = IW.MTL_ORGANIZATION_ID
64 AND IW.WHSE_CODE = BH.WIP_WHSE_CODE
65 AND UOM.UM_CODE = BR.USAGE_UM
66 AND BR.LAST_UPDATE_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
82 x_JOB_RSRC_PK NUMBER ;
83 x_ACT_RSRC_COUNT NUMBER ;
84 x_PLN_RSRC_COUNT NUMBER ;
85 x_ACT_RSRC_QTY NUMBER ;
86 x_PLN_RSRC_QTY NUMBER ;
87 x_ACT_RSRC_USAGE NUMBER ;
88 x_PLN_RSRC_USAGE NUMBER ;
89 x_STND_RSRC_USAGE NUMBER ;
90 x_JOB_NO NUMBER ;
91 x_OPERATION_SEQ_NUM NUMBER ;
92 x_ACT_STRT_DATE NUMBER ;
93 x_ACT_CMPL_DATE NUMBER ;
94 x_PLN_STRT_DATE NUMBER ;
95 x_PLN_CMPL_DATE NUMBER ;
96 x_SOB_CURRENCY_FK NUMBER ;
97 x_UOM_FK NUMBER ;
98 x_INSTANCE_FK NUMBER ;
99 x_LOCATOR_FK NUMBER ;
100 x_ACTIVITY_FK NUMBER ;
101 x_TRX_DATE_FK NUMBER ;
102 x_OPRN_FK NUMBER ;
103 x_RSRC_FK NUMBER ;
104 x_ITEM_FK NUMBER ;
105
106
107 CURSOR JOB_RSRC IS
108 SELECT avg(nvl(vsize(POD.BATCH_ID||'-'||POD.Batchstep_no||'-'||
109 POD.Resources||'-'||POD.Activity||'-'||POD.BATCHSTEPLINE_ID||'-OPM'),0)) JOB_RSRC_PK,
110 avg(nvl(vsize(POD.ACTUAL_CMPLT_DATE),0)) ACT_CMPL_DATE,
111 avg(nvl(vsize(POD.ACTUAL_RSRC_COUNT),0)) ACT_RSRC_COUNT,
112 avg(nvl(vsize(POD.ACTUAL_RSRC_QTY),0)) ACT_RSRC_QTY,
113 avg(nvl(vsize(POD.ACTUAL_RSRC_USAGE),0)) ACT_RSRC_USAGE,
114 avg(nvl(vsize(POD.ACTUAL_START_DATE),0)) ACT_START_DATE,
115 avg(nvl(vsize(POD.PLAN_CMPLT_DATE),0)) PLN_CMPLT_DATE,
116 avg(nvl(vsize(POD.PLAN_RSRC_COUNT),0)) PLN_RSRC_COUNT,
117 avg(nvl(vsize(POD.PLAN_RSRC_QTY),0)) PLN_RSRC_QTY,
118 avg(nvl(vsize(POD.PLAN_RSRC_USAGE),0)) PLN_RSRC_USAGE,
119 avg(nvl(vsize(POD.PLAN_START_DATE),0)) PLN_STRT_DATE,
120 avg(nvl(vsize(POD.BATCHSTEP_NO),0)) OPERATION_SEQ_NO,
121 avg(nvl(vsize(PBH.BATCH_NO),0)) JOB_NO,
122 avg(nvl(vsize(((POD.PLAN_RSRC_USAGE
123 /POD.PLAN_RSRC_QTY)
124 *POD.ACTUAL_RSRC_QTY)),0)) STND_RSRC_USAGE
125 FROM
126 PM_OPRN_DTL POD,
127 PM_BTCH_HDR PBH
128 WHERE POD.BATCH_ID=PBH.BATCH_ID AND
129 POD.last_update_date between
130 p_from_date and p_to_date;
131
132 CURSOR OPRN_PK IS
133 SELECT
134 /* OPRN_FK */
135 avg(nvl(vsize(oprn_id),0))
136 FROM FM_OPRN_MST;
137
138
139 CURSOR ITEM_PK IS
140 /* ITEM_FK */
141 SELECT
142 avg(nvl(vsize(EDW_ITEMS_PKG.ITEM_ORG_FK(ITEM_FK_V.INVENTORY_ITEM_ID,
143 IW.MTL_ORGANIZATION_ID,NULL,TO_NUMBER(NULL),NULL)), 0))
144 FROM MTL_SYSTEM_ITEMS ITEM_FK_V,
145 IC_WHSE_MST IW,
146 IC_ITEM_MST IM
147 WHERE ITEM_FK_V.SEGMENT1= IM.ITEM_NO
148 AND ITEM_FK_V.ORGANIZATION_ID = IW.MTL_ORGANIZATION_ID;
149
150 CURSOR INST_PK IS
151 SELECT
152 avg(nvl(vsize(instance_code), 0))
153 FROM EDW_LOCAL_INSTANCE ;
154
155
156 CURSOR CURR_PK is
157 SELECT avg(nvl(vsize(BASE_CURRENCY_CODE), 0))
158 FROM gl_plcy_mst;
159
160 CURSOR ACT_PK is
161 SELECT avg(nvl(vsize(ACTIVITY), 0))
162 FROM FM_ACTV_MST;
163
164 CURSOR RSRC_PK is
165 SELECT avg(nvl(vsize(RESOURCES||'-OPM'), 0))
166 FROM CR_RSRC_MST;
167
168 CURSOR UOM_PK is
169 SELECT avg(nvl(vsize(UOM_CODE), 0))
170 FROM OPI_PMI_UOMS_MST;
171 CURSOR LOC_PK is
172 SELECT avg(nvl(vsize(ORGN_CODE), 0))
173 FROM SY_ORGN_MST;
174
175 CURSOR TRX_DATE_PK is
176 SELECT avg(nvl(vsize(substr(edw_time_pkg.cal_day_fk
177 (POD.ACTUAL_CMPLT_DATE,SOB.SET_OF_BOOKS_ID),1,120)),0))
178 FROM
179 PM_OPRN_DTL POD,
180 PM_BTCH_HDR BH,
181 SY_ORGN_MST OM,
182 GL_PLCY_MST PM,
183 GL_SETS_OF_BOOKS SOB
184 WHERE
185 BH.BATCH_ID=POD.BATCH_ID
186 AND BH.PLANT_CODE = OM.ORGN_CODE
187 AND OM.CO_CODE = PM.co_code
188 AND PM.SET_OF_BOOKS_NAME=SOB.name;
189
190
191
192 BEGIN
193
194 OPEN JOB_RSRC;
195 FETCH JOB_RSRC INTO
196 X_JOB_RSRC_PK,
197 X_ACT_CMPL_DATE,
198 X_ACT_RSRC_COUNT,
199 X_ACT_RSRC_QTY,
200 X_ACT_RSRC_USAGE,
201 X_ACT_STRT_DATE,
202 X_PLN_CMPL_DATE,
203 X_PLN_RSRC_COUNT,
204 X_PLN_RSRC_QTY,
205 X_PLN_RSRC_USAGE,
206 X_PLN_STRT_DATE,
207 X_OPERATION_SEQ_NUM,
208 X_JOB_NO,
209 X_STND_RSRC_USAGE;
210 CLOSE JOB_RSRC;
211
212 x_total := 3 +
213 x_total +
214 ceil(x_JOB_RSRC_PK + 1) +
215 ceil(x_ACT_RSRC_COUNT + 1) +
216 ceil(x_PLN_RSRC_COUNT + 1) +
217 ceil(x_ACT_RSRC_USAGE + 1) +
218 ceil(x_PLN_RSRC_USAGE + 1) +
219 ceil(x_STND_RSRC_USAGE + 1) +
220 ceil(x_OPERATION_SEQ_NUM + 1) +
221 ceil(x_ACT_STRT_DATE + 1) +
222 ceil(x_ACT_CMPL_DATE + 1) +
223 ceil(x_PLN_STRT_DATE + 1) +
224 ceil(x_PLN_CMPL_DATE + 1) +
225 ceil(x_job_NO+1) +
226 ceil(x_ACT_RSRC_QTY+1)+
227 6*ceil(x_ACT_RSRC_QTY+2);
228
229
230 OPEN OPRN_PK;
231 FETCH OPRN_PK INTO x_OPRN_FK;
232 CLOSE OPRN_PK;
233 x_total := x_total + ceil(x_OPRN_FK + 1);
234
235 OPEN ITEM_PK;
236 FETCH ITEM_PK INTO x_ITEM_FK ;
237 CLOSE ITEM_PK;
238 x_total := x_total + ceil(x_ITEM_FK + 1) ;
239
240 OPEN TRX_DATE_PK;
241 FETCH TRX_DATE_PK INTO x_TRX_DATE_FK;
242 CLOSE TRX_DATE_PK;
243 x_total := x_total + ceil(x_TRX_DATE_FK + 1);
244
245 OPEN INST_PK;
246 FETCH INST_PK INTO x_INSTANCE_FK;
247 CLOSE INST_PK;
248 x_total := x_total + ceil(x_INSTANCE_FK + 1);
249
250 OPEN CURR_PK ;
251 FETCH CURR_PK INTO x_SOB_CURRENCY_FK;
252 CLOSE CURR_PK ;
253 x_total := x_total + ceil(x_SOB_CURRENCY_FK + 1);
254
255 OPEN UOM_PK ;
256 FETCH UOM_PK INTO x_UOM_FK;
257 CLOSE UOM_PK ;
258 x_total := x_total + ceil(x_UOM_FK + 1);
259
260 OPEN RSRC_PK ;
261 FETCH RSRC_PK INTO x_RSRC_FK;
262 CLOSE RSRC_PK ;
263 x_total := x_total + ceil(x_RSRC_FK + 1);
264 OPEN ACT_PK ;
265 FETCH ACT_PK INTO x_ACTIVITY_FK;
266 CLOSE ACT_PK ;
267 x_total := x_total + ceil(x_ACTIVITY_FK + 1);
268
269 OPEN LOC_PK ;
270 FETCH LOC_PK INTO x_LOCATOR_FK;
271 CLOSE LOC_PK ;
272 x_total := x_total + ceil(x_LOCATOR_FK + 1);
273
274 -- Miscellaneous
275 x_total := x_total + 5 * ceil(x_INSTANCE_FK + 1);
276
277 p_avg_row_len := x_total;
278
279 END; -- procedure est_row_len.
280
281 END OPI_EDW_JOB_RSRC_FOPM_SZ;