[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_JOB_RSRC_F_SZ
Source
1 PACKAGE BODY OPI_EDW_JOB_RSRC_F_SZ AS
2 /* $Header: OPIOJRZB.pls 120.1 2005/06/07 02:15:15 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(*) from
10 (
11 select to_char(wor.organization_id)||inst.instance_code
12 FROM
13 WIP_OPERATION_RESOURCES wor,
14 WIP_OPERATIONS wo,
15 WIP_ENTITIES we,
16 BOM_DEPARTMENTS bd,
17 HR_ORGANIZATION_INFORMATION hoi,
18 GL_SETS_OF_BOOKS gsob,
19 WIP_DISCRETE_JOBS wdj,
20 WIP_REPETITIVE_SCHEDULES wrs,
21 EDW_LOCAL_INSTANCE inst,
22 MTL_SYSTEM_ITEMS msi
23 WHERE
24 wor.organization_id = wo.organization_id
25 and wor.wip_entity_id = wo.wip_entity_id
26 and wor.operation_seq_num = wo.operation_seq_num
27 and nvl(wor.repetitive_schedule_id,-99) = nvl(wo.repetitive_schedule_id,-99)
28 and wo.organization_id = bd.organization_id
29 and wo.department_id = bd.department_id
30 and wo.organization_id = we.organization_id
31 and wo.wip_entity_id = we.wip_entity_id
32 and hoi.organization_id = wor.organization_id
33 and gsob.set_of_books_id = hoi.ORG_INFORMATION1
34 and hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
35 and wdj.wip_entity_id (+) = wor.wip_entity_id
36 and wdj.organization_id (+) = wor.organization_id
37 and wrs.repetitive_schedule_id (+)= nvl(wor.repetitive_schedule_id,-99)
38 and wrs.organization_id (+) = wor.organization_id
39 and (wrs.status_type in (4,5,7,12) or wdj.status_type in (4,5,7,12))
40 and msi.organization_id = wor.organization_id
41 and msi.inventory_item_id = we.primary_item_id
42 and wor.last_update_date between
43 p_from_date and p_to_date
44 group by wor.organization_id,wor.wip_entity_id,
45 wor.operation_seq_num,wor.resource_id,
46 wor.repetitive_schedule_id,wor.activity_id,
47 wor.uom_code,wor.basis_type,we.wip_entity_name,
48 bd.department_code,
49 wo.first_unit_start_date,wo.first_unit_completion_date,
50 wo.operation_sequence_id,wo.department_id,
51 gsob.set_of_books_id, msi.primary_uom_code,
52 we.primary_item_id ,inst.instance_code
53 UNION ALL
54 select
55 to_char(wt.organization_id)||inst.instance_code
56 FROM
57 WIP_ENTITIES we,
58 WIP_TRANSACTIONS wt,
59 WIP_TRANSACTION_ACCOUNTS wta,
60 BOM_DEPARTMENTS bd,
61 HR_ORGANIZATION_INFORMATION hoi,
62 GL_SETS_OF_BOOKS gsob,
63 WIP_FLOW_SCHEDULES wfs,
64 BOM_OPERATIONAL_ROUTINGS bor,
65 BOM_OPERATION_SEQUENCES bos,
66 EDW_LOCAL_INSTANCE inst,
67 MTL_SYSTEM_ITEMS msi
68 WHERE
69 wt.transaction_type in (1,3)
70 and wfs.status = 2
71 and wt.wip_entity_id = wfs.wip_entity_id
72 and wt.organization_id = wfs.organization_id
73 and wt.organization_id = wta.organization_id
74 and wt.wip_entity_id = wta.wip_entity_id
75 and wt.transaction_id = wta.transaction_id
76 and wta.accounting_line_type = 7
77 and wt.wip_entity_id = we.wip_entity_id
78 and wt.organization_id = we.organization_id
79 and wt.organization_id = bd.organization_id
80 and wt.department_id = bd.department_id
81 and hoi.organization_id = wt.organization_id
82 and hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
83 and gsob.set_of_books_id = hoi.ORG_INFORMATION1
84 and msi.organization_id = wt.organization_id
85 and msi.inventory_item_id = we.primary_item_id
86 and wfs.organization_id = bor.organization_id
87 and nvl(wfs.alternate_routing_designator,-99) = nvl(bor.alternate_routing_designator,-99)
88 and wfs.primary_item_id = bor.assembly_item_id
89 and bor.routing_sequence_id = bos.routing_sequence_id
90 and wt.operation_seq_num = bos.operation_seq_num
91 and bos.operation_type = 1
92 and wt.last_update_date between
93 p_from_date and p_to_date
94 group by wt.organization_id, wt.wip_entity_id,
95 wt.operation_seq_num, wt.resource_id, wt.activity_id,
96 wfs.quantity_completed, wfs.date_closed,
97 wfs.scheduled_start_date, wfs.scheduled_completion_date,
98 wt.basis_type, wt.transaction_uom,wt.primary_uom,
99 wt.department_id,we.wip_entity_name,bd.department_code,
100 gsob.set_of_books_id,bos.operation_sequence_id,
101 msi.primary_uom_code , we.primary_item_id,inst.instance_code ) ;
102
103 BEGIN
104
105 OPEN c_cnt_rows;
106 FETCH c_cnt_rows INTO p_num_rows;
107 CLOSE c_cnt_rows;
108
109 END; -- procedure cnt_rows.
110
111
112 PROCEDURE est_row_len(p_from_date DATE,
113 p_to_date DATE,
114 p_avg_row_len OUT NOCOPY NUMBER) IS
115 x_date number := 7;
116 x_total number := 0;
117 x_constant number := 6;
118
119 x_JOB_RSRC_PK NUMBER ;
120 x_ACT_RSRC_COUNT NUMBER ;
121 x_PLN_RSRC_COUNT NUMBER ;
122 x_ACT_RSRC_QTY NUMBER ;
123 x_ACT_RSRC_VAL_B NUMBER ;
124 x_ACT_RSRC_VAL_G NUMBER ;
125 x_PLN_RSRC_QTY NUMBER ;
126 x_PLN_RSRC_VAL_B NUMBER ;
127 x_PLN_RSRC_VAL_G NUMBER ;
128 x_ACT_RSRC_USAGE NUMBER ;
129 x_PLN_RSRC_USAGE NUMBER ;
130 x_STND_RSRC_USAGE NUMBER ;
131 x_ACT_RSRC_USAGE_VAL_B NUMBER ;
132 x_ACT_RSRC_USAGE_VAL_G NUMBER ;
133 x_PLN_RSRC_USAGE_VAL_B NUMBER ;
134 x_PLN_RSRC_USAGE_VAL_G NUMBER ;
135 x_EXTD_RSRC_COST NUMBER ;
136 x_JOB_NO NUMBER ;
137 x_OPERATION_SEQ_NUM NUMBER ;
138 x_DEPARTMENT NUMBER ;
139 x_ACT_STRT_DATE NUMBER ;
140 x_ACT_CMPL_DATE NUMBER ;
141 x_PLN_STRT_DATE NUMBER ;
142 x_PLN_CMPL_DATE NUMBER ;
143 x_SOB_CURRENCY_FK NUMBER ;
144 x_QTY_UOM_FK NUMBER ;
145 x_INSTANCE_FK NUMBER ;
146 x_LOCATOR_FK NUMBER ;
147 x_ACTIVITY_FK NUMBER ;
148 x_TRX_DATE_FK NUMBER ;
149 x_OPRN_FK NUMBER ;
150 x_RSRC_FK NUMBER ;
151 x_ITEM_FK NUMBER ;
152 x_USAGE_UOM_FK NUMBER ;
153 x_USER_ATTRIBUTE1 NUMBER;
154 x_USER_ATTRIBUTE2 NUMBER;
155 x_USER_ATTRIBUTE3 NUMBER;
156 x_USER_ATTRIBUTE4 NUMBER;
157 x_USER_ATTRIBUTE5 NUMBER;
158 x_USER_ATTRIBUTE6 NUMBER;
159 x_USER_ATTRIBUTE7 NUMBER;
160 x_USER_ATTRIBUTE8 NUMBER;
161 x_USER_ATTRIBUTE9 NUMBER;
162 x_USER_ATTRIBUTE10 NUMBER;
163 x_USER_ATTRIBUTE11 NUMBER;
164 x_USER_ATTRIBUTE12 NUMBER;
165 x_USER_ATTRIBUTE13 NUMBER;
166 x_USER_ATTRIBUTE14 NUMBER;
167 x_USER_ATTRIBUTE15 NUMBER;
168 x_USER_FK1 NUMBER;
169 x_USER_FK2 NUMBER;
170 x_USER_FK3 NUMBER;
171 x_USER_FK4 NUMBER;
172 x_USER_FK5 NUMBER;
173 x_USER_MEASURE1 NUMBER;
174 x_USER_MEASURE2 NUMBER;
175 x_USER_MEASURE3 NUMBER;
176 x_USER_MEASURE4 NUMBER;
177 x_USER_MEASURE5 NUMBER;
178
179 --------
180 CURSOR c_1 IS
181 SELECT
182 -- JOB_RSRC_PK (need to add inst.instance_code)
183 avg(nvl(vsize(organization_id||wip_entity_id||repetitive_schedule_id||operation_seq_num||resource_id),0)),
184 -- ACT_RSRC_COUNT (dummy)
185 avg(nvl(vsize(applied_resource_units), 0)),
186 -- PLN_RSRC_COUNT (dummy)
187 avg(nvl(vsize(applied_resource_units), 0)),
188 -- ACT_RSRC_QTY (dummy)
189 avg(nvl(vsize(applied_resource_units), 0)),
190 -- PLN_RSRC_QTY (dummy)
191 avg(nvl(vsize(applied_resource_units), 0)),
192 -- ACT_RSRC_VAL_B (dummy)
193 avg(nvl(vsize(usage_rate_or_amount), 0)),
194 -- PLN_RSRC_VAL_B (dummy)
195 avg(nvl(vsize(usage_rate_or_amount), 0)),
196 -- ACT_RSRC_VAL_G (dummy)
197 avg(nvl(vsize(usage_rate_or_amount), 0)),
198 -- PLN_RSRC_VAL_G (dummy)
199 avg(nvl(vsize(usage_rate_or_amount), 0)),
200 -- ACT_RSRC_USAGE
201 avg(nvl(vsize(applied_resource_units), 0)),
202 -- PLN_RSRC_USAGE
203 avg(nvl(vsize(usage_rate_or_amount), 0)),
204 -- STND_RSRC_USAGE
205 avg(nvl(vsize(usage_rate_or_amount), 0)),
206 -- ACT_RSRC_USAGE_VAL_B
207 avg(nvl(vsize(applied_resource_value), 0)),
208 -- PLN_RSRC_USAGE_VAL_B
209 avg(nvl(vsize(applied_resource_value), 0)),
210 -- ACT_RSRC_USAGE_VAL_G
211 avg(nvl(vsize(applied_resource_value), 0)),
212 -- PLN_RSRC_USAGE_VAL_G
213 avg(nvl(vsize(applied_resource_value), 0)),
214 -- EXTD_RSRC_COST (dummy)
215 avg(nvl(vsize(applied_resource_units), 0)),
216 -- OPERATION_SEQ_NUM
217 avg(nvl(vsize(operation_seq_num), 0)),
218 -- JOB_NO we.wip_entity_name
219 -- DEPARTMENT bd.department_code
220 -- ACT_STRT_DATE
221 -- ACT_CMPL_DATE
222 -- PLN_STRT_DATE
223 -- PLN_CMPL_DATE
224 -- SOB_CURRENCY_FK
225 -- QTY_UOM_FK same as USAGE_UOM_FK
226 -- INSTANCE_FK from inst
227 -- LOCATOR_FK need to add inst.instance_code
228 -- ACTIVITY_FK Need to take add inst.instance_code
229 avg(nvl(vsize(activity_id), 0)),
230 -- TRX_DATE_FK wmt.transaction_date
231 -- OPRN_FK wo.operation_seq_id
232 -- RSRC_FK
233 avg(nvl(vsize(resource_id), 0)),
234 -- ITEM_FK we.primary_item_id, add instance_code
235 -- USAGE_UOM_FK
236 avg(nvl(vsize(uom_code), 0))
237 FROM WIP_OPERATION_RESOURCES
238 WHERE last_update_date between
239 p_from_date and p_to_date;
240
241 CURSOR c_2 IS
242 SELECT
243 -- OPRN_FK wo.operation_seq_id (Need to add inst.instance_code)
244 avg(nvl(vsize(operation_sequence_id||organization_id),0))
245 FROM WIP_OPERATIONS
246 WHERE last_update_date between
247 p_from_date and p_to_date;
248
249 CURSOR c_3 IS
250 SELECT
251 avg(nvl(vsize(wip_entity_name), 0)),
252 avg(nvl(vsize(primary_item_id), 0))
253 FROM WIP_ENTITIES
254 WHERE last_update_date between
255 p_from_date and p_to_date;
256
257
258 CURSOR c_4 IS
259 SELECT
260 avg(nvl(vsize(department_code), 0))
261 FROM BOM_DEPARTMENTS
262 WHERE last_update_date between
263 p_from_date and p_to_date;
264
265 CURSOR c_5 IS
266 SELECT
267 avg(nvl(vsize(transaction_date), 0))
268 FROM WIP_MOVE_TRANSACTIONS
269 WHERE last_update_date between
270 p_from_date and p_to_date;
271
272 CURSOR c_6 IS
273 SELECT
274 avg(nvl(vsize(instance_code), 0))
275 FROM EDW_LOCAL_INSTANCE ;
276 -- WHERE last_update_date between
277 -- p_from_date and p_to_date;
278
279 CURSOR c_7 is
280 SELECT avg(nvl(vsize(gsob.currency_code), 0))
281 FROM hr_all_organization_units hou,
282 hr_organization_information hoi,
283 gl_sets_of_books gsob
284 WHERE hou.organization_id = hoi.organization_id
285 AND ( hoi.org_information_context || '') ='Accounting Information'
286 AND hoi.org_information1 = to_char(gsob.set_of_books_id) ;
287 --WHERE hou.last_update_date between
288 --p_from_date and p_to_date;
289
290 CURSOR c_8 is
291 SELECT avg(nvl(vsize(organization_code), 0))
292 FROM mtl_parameters ;
293 -- WHERE last_update_date between
294 -- p_from_date and p_to_date;
295
296 BEGIN
297
298 OPEN c_1;
299 FETCH c_1 INTO
300 x_JOB_RSRC_PK ,
301 x_ACT_RSRC_COUNT,
302 x_PLN_RSRC_COUNT,
303 x_ACT_RSRC_QTY,
304 x_PLN_RSRC_QTY,
305 x_ACT_RSRC_VAL_B,
306 x_PLN_RSRC_VAL_B,
307 x_ACT_RSRC_VAL_G,
308 x_PLN_RSRC_VAL_G,
309 x_ACT_RSRC_USAGE,
310 x_PLN_RSRC_USAGE,
311 x_STND_RSRC_USAGE,
312 x_ACT_RSRC_USAGE_VAL_B,
313 x_PLN_RSRC_USAGE_VAL_B,
314 x_ACT_RSRC_USAGE_VAL_G,
315 x_PLN_RSRC_USAGE_VAL_G,
316 x_OPERATION_SEQ_NUM,
317 x_EXTD_RSRC_COST,
318 x_ACTIVITY_FK ,
319 x_RSRC_FK,
320 x_USAGE_UOM_FK ;
321 CLOSE c_1;
322
323 x_ACT_STRT_DATE := x_date ;
324 x_ACT_CMPL_DATE := x_date ;
325 x_PLN_STRT_DATE := x_date ;
326 x_PLN_CMPL_DATE := x_date ;
327 -- x_LAST_UPDATE_DATE := x_date;
328 x_QTY_UOM_FK := x_USAGE_UOM_FK;
329
330 x_total := 3 +
331 x_total +
332 ceil(x_JOB_RSRC_PK + 1) +
333 ceil(x_ACT_RSRC_COUNT + 1) +
334 ceil(x_PLN_RSRC_COUNT + 1) +
335 ceil(x_ACT_RSRC_VAL_B + 1) +
336 ceil(x_PLN_RSRC_VAL_B + 1) +
337 ceil(x_ACT_RSRC_VAL_G + 1) +
338 ceil(x_PLN_RSRC_VAL_G + 1) +
339 ceil(x_ACT_RSRC_USAGE + 1) +
340 ceil(x_PLN_RSRC_USAGE + 1) +
341 ceil(x_STND_RSRC_USAGE + 1) +
342 ceil(x_ACT_RSRC_USAGE_VAL_B + 1) +
343 ceil(x_PLN_RSRC_USAGE_VAL_B + 1) +
344 ceil(x_ACT_RSRC_USAGE_VAL_G + 1) +
345 ceil(x_PLN_RSRC_USAGE_VAL_G + 1) +
346 ceil(x_OPERATION_SEQ_NUM + 1) +
347 ceil(x_ACTIVITY_FK + 1) +
348 ceil(x_RSRC_FK + 1) +
349 ceil(x_USAGE_UOM_FK + 1) +
350 ceil(x_ACT_STRT_DATE + 1) +
351 ceil(x_ACT_CMPL_DATE + 1) +
352 ceil(x_PLN_STRT_DATE + 1) +
353 ceil(x_PLN_CMPL_DATE + 1) +
354 ceil(x_QTY_UOM_FK + 1) ;
355
356 OPEN c_2;
357 FETCH c_2 INTO x_OPRN_FK;
358 CLOSE c_2;
359 x_total := x_total + ceil(x_OPRN_FK + 1);
360
361 OPEN c_3;
362 FETCH c_3 INTO x_JOB_NO,
363 x_ITEM_FK ;
364 CLOSE c_3;
365 x_total := x_total + ceil(x_JOB_NO + 1) + ceil(x_ITEM_FK + 1) ;
366
367 OPEN c_4;
368 FETCH c_4 INTO x_DEPARTMENT;
369 CLOSE c_4;
370 x_total := x_total + ceil(x_DEPARTMENT + 1);
371
372 OPEN c_5;
373 FETCH c_5 INTO x_TRX_DATE_FK;
374 CLOSE c_5;
375 x_total := x_total + ceil(x_TRX_DATE_FK + 1);
376
377 OPEN c_6;
378 FETCH c_6 INTO x_INSTANCE_FK;
379 CLOSE c_6;
380 x_total := x_total + ceil(x_INSTANCE_FK + 1);
381
382 OPEN c_7 ;
383 FETCH c_7 INTO x_SOB_CURRENCY_FK;
384 CLOSE c_7 ;
385 x_total := x_total + ceil(x_SOB_CURRENCY_FK + 1);
386
387
388 -- Miscellaneous
389 x_total := x_total + 4 * ceil(x_INSTANCE_FK + 1);
390
391 p_avg_row_len := x_total;
392
393 END; -- procedure est_row_len.
394
395 END; -- package body OPI_EDW_JOB_RSRC_F_SZ