[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_RES_OPM_PKG
Source
1 PACKAGE BODY opi_dbi_res_opm_pkg AS
2 /* $Header: OPIDREOB.pls 115.6 2004/07/19 05:17:41 vganeshk noship $ */
3
4 g_sysdate DATE := SYSDATE;
5 g_user_id NUMBER := nvl(fnd_global.user_id, -1);
6 g_login_id NUMBER := nvl(fnd_global.login_id, -1);
7 g_global_start_date DATE := SYSDATE;
8 g_global_currency_code VARCHAR2(10);
9 g_last_collection_date DATE;
10 g_number_max_value NUMBER;
11 g_degree NUMBER := 1;
12 g_ok NUMBER(1) := 0;
13 g_warning NUMBER(1) := 1;
14 g_error NUMBER(1) := -1;
15 g_hr_uom sy_uoms_mst.um_code%TYPE;
16
17 /*
18 PROCEDURE put_line(p_msg VARCHAR2)
19 IS
20 PRAGMA AUTONOMOUS_TRANSACTION;
21 BEGIN
22 insert into pdong_debug_tbl(msg_seq, msg, msg_date)
23 values (pdong_sequence.nextval, p_msg, sysdate);
24 commit;
25 END;
26 */
27
28 PROCEDURE check_setup_globals(errbuf IN OUT NOCOPY VARCHAR2 , retcode IN OUT NOCOPY VARCHAR2) IS
29
30 l_list dbms_sql.varchar2_table;
31
32 l_from_date DATE;
33 l_to_date DATE;
34 l_missing_day_flag BOOLEAN := FALSE;
35 l_err_num NUMBER;
36 l_err_msg VARCHAR2(255);
37 l_min_miss_date DATE;
38 l_max_miss_date DATE;
39 BEGIN
40
41 --dbms_output.put_line('starting opi_dbi_res_OPM_pkg.check_setup_globals');
42
43 retcode := 0;
44 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
45 l_list(2) := 'BIS_GLOBAL_START_DATE';
46
47 IF (bis_common_parameters.check_global_parameters(l_list)) THEN
48 SELECT Trunc(bis_common_parameters.get_global_start_date)
49 INTO g_global_start_date FROM dual;
50
51 SELECT bis_common_parameters.get_currency_code
52 INTO g_global_currency_code FROM dual;
53
54 select sysdate into l_to_date from dual;
55
56 -- check_missing_date
57 /*
58 fii_time_api.check_missing_date( l_from_date, l_to_date, l_missing_day_flag,
59 l_min_miss_date, l_max_miss_date);
60 */
61 IF l_missing_day_flag THEN
62 retcode := 2;
63 errbuf := 'Please check log file for details. ';
64 BIS_COLLECTION_UTILITIES.PUT_LINE('There are missing date in Time Dimension.');
65
66 BIS_COLLECTION_UTILITIES.PUT_LINE('The range is from ' || l_min_miss_date
67 ||' to ' || l_max_miss_date );
68 END IF;
69 ELSE
70 retcode := 2;
71 errbuf := 'Please check log file for details. ';
72 BIS_COLLECTION_UTILITIES.PUT_LINE('Global Parameters are not setup.');
73
74 BIS_COLLECTION_UTILITIES.put_line('Please check that the profile options: BIS_PRIMARY_CURRENCY_CODE and BIS_GLOBAL_START_DATE are setup.');
75
76 END IF;
77
78 g_hr_uom := fnd_profile.value('SY$UOM_HOURS');
79
80 EXCEPTION
81 WHEN OTHERS THEN
82 retcode := SQLCODE;
83 errbuf := 'ERROR in OPI_DBI_RES_PKG.CHECK_SETUP_GLOBALS '
84 || substr(SQLERRM, 1,200);
85
86 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || retcode);
87 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || errbuf);
88
89 --dbms_output.put_line('check_setup_globals ' || errbuf);
90
91 END check_setup_globals;
92
93
94 PROCEDURE initial_opm_res_avail (errbuf in out NOCOPY varchar2,
95 retcode in out NOCOPY varchar2)
96 IS
97 l_stmt_num NUMBER;
98 l_rowcount NUMBER;
99 BEGIN
100
101 --dbms_output.put_line('starting initial_opm_res_avail');
102
103 retcode := g_ok;
104
105 l_stmt_num := 10;
106
107 -- populate availability for opm resource
108
109 --dbms_output.put_line('Preparing to Insert OPM Resource Availability into opi_dbi_res_avail_stg');
110 --dbms_output.put_line('g_hr_uom := ' || g_hr_uom);
111 --dbms_output.put_line('g_global_start_date = ' || to_char(g_global_start_date));
112
113 INSERT /*+ APPEND */ INTO opi_dbi_res_avail_stg
114 ( resource_id, organization_id, department_id, transaction_date,
115 uom, avail_qty, avail_qty_g, avail_val_b, source,
116 creation_date, last_update_date, created_by,
117 last_updated_by, last_update_login)
118 SELECT /*+ ORDERED */
119 r.resource_id resource_id,
120 r.organization_id organization_id,
121 r.department_id department_id,
122 r.shift_date transaction_date,
123 r.usage_um uom,
124 SUM(r.shift_hours * hruom.std_factor / ruom.std_factor)
125 avail_qty,
126 SUM(r.shift_hours) avail_qty_g, -- availability in hours
127 SUM(r.shift_hours * hruom.std_factor / rcostuom.std_factor * rcost.nominal_cost)
128 avail_val_b,
129 2 source,
130 SYSDATE creation_date,
131 SYSDATE last_update_date,
132 g_user_id created_by,
133 g_user_id last_updated_by,
134 g_login_id last_update_login
135 FROM
136 (
137 SELECT /*+ ORDERED */
138 rdtl.orgn_code,
139 rdtl.resources,
140 rdtl.resource_id,
141 rmst.resource_class department_id,
142 rdtl.usage_um,
143 plant.co_code,
144 pol.gl_cost_mthd cost_mthd_code,
145 whse.mtl_organization_id organization_id,
146 ravail.shift_date,
147 SUM((ravail.to_time - ravail.from_time)/3600) shift_hours
148 FROM
149 cr_rsrc_dtl rdtl,
150 cr_rsrc_mst_b rmst,
151 sy_orgn_mst_b plant,
152 gl_plcy_mst pol,
153 ic_whse_mst whse,
154 gmp_resource_avail ravail
155 WHERE
156 rmst.resources = rdtl.resources
157 AND plant.orgn_code = rdtl.orgn_code
158 AND pol.co_code = plant.co_code
159 AND whse.whse_code = plant.resource_whse_code
160 AND ravail.plant_code = rdtl.orgn_code
161 AND ravail.resource_id = rdtl.resource_id
162 AND NVL(ravail.resource_instance_id,0) = 0 -- exclude individual resource instances
163 AND ravail.shift_date BETWEEN g_global_start_date AND SYSDATE
164 AND ravail.shift_date >= trunc(rdtl.creation_date)
165 GROUP BY
166 rdtl.orgn_code,
167 rdtl.resources,
168 rdtl.resource_id,
169 rmst.resource_class,
170 rdtl.usage_um,
171 plant.co_code,
172 pol.gl_cost_mthd,
173 whse.mtl_organization_id,
174 ravail.shift_date
175 ) r,
176 sy_uoms_mst ruom,
177 (
178 SELECT
179 hdr.cost_mthd_code,
180 dtl.calendar_code,
181 dtl.period_code,
182 dtl.start_date,
183 dtl.end_date
184 FROM
185 cm_cldr_hdr_b hdr,
186 cm_cldr_dtl dtl
187 WHERE
188 hdr.calendar_code = dtl.calendar_code
189 AND dtl.end_date >= g_global_start_date
190 AND dtl.start_date <= sysdate
191 ) cal,
192 cm_rsrc_dtl rcost,
193 sy_uoms_mst rcostuom,
194 sy_uoms_mst hruom
195 WHERE
196 r.cost_mthd_code = cal.cost_mthd_code
197 AND r.shift_date BETWEEN cal.start_date AND cal.end_date
198 AND rcost.orgn_code = r.orgn_code
199 AND rcost.resources = r.resources
200 AND rcost.cost_mthd_code = cal.cost_mthd_code
201 AND rcost.calendar_code = cal.calendar_code
202 AND rcost.period_code = cal.period_code
203 AND hruom.um_code = g_hr_uom
204 AND ruom.um_code = r.usage_um
205 AND rcostuom.um_code = rcost.usage_um
206 GROUP BY
207 r.resource_id,
208 r.organization_id,
209 r.department_id,
210 r.shift_date,
211 r.usage_um;
212
213 l_rowcount := sql%rowcount;
214
215 COMMIT;
216
217 BIS_COLLECTION_UTILITIES.put_line('OPM Resource Availability: ' ||
218 to_char(l_rowcount) || ' rows initially collected into staging table at '||
219 to_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
220
221 EXCEPTION WHEN OTHERS THEN
222
223 Errbuf:= Sqlerrm;
224 Retcode:= SQLCODE;
225
226 ROLLBACK;
227
228 BIS_COLLECTION_UTILITIES.PUT_LINE('Exception in initial_opm_res_avail ' || errbuf );
229
230 --dbms_output.put_line('initial_opm_res_avail ' || errbuf);
231 END initial_opm_res_avail;
232
233
234 PROCEDURE initial_opm_res_actual (errbuf in out NOCOPY varchar2,
235 retcode in out NOCOPY varchar2) IS
236 l_stmt_num NUMBER;
237 l_rowcount NUMBER;
238
239 BEGIN
240
241 retcode := g_ok;
242 check_setup_globals(errbuf, retcode);
243
244 --dbms_output.put_line('Preparing to insert into opi_dbi_res_actual_stg');
245 --dbms_output.put_line('g_hr_uom = ' || g_hr_uom);
246 --dbms_output.put_line('g_global_start_date = ' || to_char(g_global_start_date));
247
248
249 INSERT /*+ APPEND */ INTO opi_dbi_res_actual_stg
250 ( resource_id, organization_id, transaction_date,
251 actual_qty, uom, actual_qty_g, actual_val_b, source,
252 job_id, job_type, assembly_item_id, department_id,
253 creation_date, last_update_date, created_by,
254 last_updated_by, last_update_login )
255 SELECT
256 r.resource_id resource_id,
257 r.organization_id organization_id,
258 r.transaction_date transaction_date,
259 r.actual_qty actual_qty,
260 r.uom uom,
261 r.actual_qty_g actual_qty_g,
262 r.actual_qty
263 * rcost.nominal_cost actual_qty_b,
264 2 source,
265 r.job_id job_id,
266 4 job_type,
267 r.assembly_item_id assembly_item_id,
268 r.department_id department_id,
269 SYSDATE creation_date,
270 SYSDATE last_update_date,
271 g_user_id created_by,
272 g_user_id last_updated_by,
273 g_login_id last_update_login
274 FROM
275 (
276 SELECT /*+ ORDERED */
277 msi.inventory_item_id assembly_item_id,
278 rtran.doc_id job_id,
279 rdtl.resource_id resource_id,
280 rdtl.resources resources,
281 rmst.resource_class department_id,
282 whse.mtl_organization_id organization_id,
283 TRUNC(rtran.trans_date) transaction_date,
284 SUM(rtran.resource_usage * prod.cost_alloc) actual_qty,
285 rtran.trans_um uom,
286 rtran.orgn_code orgn_code,
287 SUM(rtran.resource_usage * prod.cost_alloc * hruom.std_factor / ruom.std_factor) actual_qty_g,
288 pol.gl_cost_mthd cost_mthd_code,
289 pol.co_code co_code
290 FROM
291 sy_uoms_mst hruom,
292 opi_dbi_run_log_curr rlc,
293 gme_resource_txns rtran,
294 sy_uoms_mst ruom,
295 cr_rsrc_dtl rdtl,
296 cr_rsrc_mst_b rmst,
297 sy_orgn_mst_b plant,
298 gl_plcy_mst pol,
299 ic_whse_mst whse,
300 gme_material_details prod,
301 ic_item_mst_b item,
302 mtl_system_items_b msi
303 WHERE
304 hruom.um_code = g_hr_uom
305 AND rlc.etl_id = 4
306 AND rlc.source = 2
307 AND rtran.poc_trans_id >= rlc.start_txn_id
308 AND rtran.poc_trans_id < rlc.next_start_txn_id
309 AND rtran.completed_ind = 1
310 AND ruom.um_code = rtran.trans_um
311 AND rdtl.orgn_code = rtran.orgn_code
312 AND rdtl.resources = rtran.resources
313 AND rmst.resources = rdtl.resources
314 AND plant.orgn_code = rdtl.orgn_code
315 AND pol.co_code = plant.co_code
316 AND whse.whse_code = plant.resource_whse_code
317 AND prod.batch_id = rtran.doc_id
318 AND prod.line_type = 1
319 AND item.item_id = prod.item_id
320 AND msi.organization_id = whse.mtl_organization_id
321 AND msi.segment1 = item.item_no
322 GROUP BY
323 msi.inventory_item_id,
324 rtran.doc_id,
325 rdtl.resource_id,
326 rdtl.resources,
327 rmst.resource_class,
328 whse.mtl_organization_id,
329 TRUNC(rtran.trans_date),
330 rtran.trans_um,
331 rtran.orgn_code,
332 pol.gl_cost_mthd,
333 pol.co_code
334 ) r,
335 (
336 SELECT
337 hdr.co_code,
338 hdr.cost_mthd_code,
339 dtl.calendar_code,
340 dtl.period_code,
341 dtl.start_date,
342 dtl.end_date
343 FROM
344 gl_plcy_mst pol,
345 cm_cldr_hdr_b hdr,
346 cm_cldr_dtl dtl
347 WHERE
348 hdr.co_code = pol.co_code
349 AND hdr.cost_mthd_code = pol.gl_cost_mthd
350 AND hdr.calendar_code = dtl.calendar_code
351 AND dtl.end_date >= g_global_start_date
352 AND dtl.start_date <= sysdate
353 ) cal,
354 cm_rsrc_dtl rcost
355 WHERE
356 r.co_code = cal.co_code
357 AND r.cost_mthd_code = cal.cost_mthd_code
358 AND r.transaction_date BETWEEN cal.start_date AND cal.end_date
359 AND rcost.orgn_code = r.orgn_code
360 AND rcost.resources = r.resources
361 AND rcost.cost_mthd_code = cal.cost_mthd_code
362 AND rcost.calendar_code = cal.calendar_code
363 AND rcost.period_code = cal.period_code
364 ;
365
366 l_rowcount := sql%rowcount;
367
368 COMMIT;
369
370 BIS_COLLECTION_UTILITIES.put_line('OPM resource actuals: ' ||
371 TO_CHAR(l_rowcount) || ' rows initially collected into staging table at ' ||
372 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
373
374 EXCEPTION WHEN OTHERS THEN
375
376 Errbuf:= Sqlerrm;
377 Retcode:= SQLCODE;
378
379 ROLLBACK;
380
381 BIS_COLLECTION_UTILITIES.PUT_LINE('Exception in initial_opm_res_actual ' || errbuf );
382
383 --dbms_output.put_line('initial_opm_res_actual ' || errbuf);
384
385 END initial_opm_res_actual;
386
387
388 PROCEDURE initial_opm_res_std (errbuf in out NOCOPY varchar2,
389 retcode in out NOCOPY VARCHAR2,
390 p_degree IN NUMBER ) IS
391 l_stmt_num NUMBER;
392 l_rowcount NUMBER;
393 l_err_num NUMBER;
394 l_err_msg VARCHAR2(255);
395 l_error_flag BOOLEAN := FALSE;
396
397 l_opi_schema VARCHAR2(30);
398 l_status VARCHAR2(30);
399 l_industry VARCHAR2(30);
400
401 BEGIN
402
403 DECLARE
404 lv_errbuf varchar2(1024);
405 lv_retcode NUMBER;
406 BEGIN
407 check_setup_globals(lv_errbuf,lv_retcode);
408 END;
409
410 --dbms_output.put_line('before insert into opi_dbi_res_std_f');
411
412 --dbms_output.put_line('g_hr_uom = ' || g_hr_uom);
413 --dbms_output.put_line('g_global_start_date = ' || to_char(g_global_start_date));
414
415 INSERT INTO opi_dbi_res_std_f
416 (resource_id,
417 organization_id,
418 transaction_date,
419 std_usage_qty,
420 uom,
421 std_usage_qty_g,
422 std_usage_val_b,
423 std_usage_val_g,
424 job_id,
425 job_type,
426 assembly_item_id,
427 department_id,
428 source,
429 creation_date,
430 last_update_date,
431 created_by,
432 last_updated_by,
433 last_update_login)
434 select
435 jobres.resource_id resource_id,
436 jobitem.organization_id organization_id,
437 jobitem.completion_date transaction_date,
438 sum(DECODE(jobres.scale_type,
439 0, jobres.plan_rsrc_usage * jobitem.cost_alloc,
440 ((jobres.plan_rsrc_usage * jobitem.cost_alloc) / jobitem.plan_qty) * jobitem.actual_qty
441 )) std_usage_qty,
442 jobres.usage_um uom,
443 sum(DECODE(jobres.scale_type,
444 0, jobres.plan_rsrc_usage_g * jobitem.cost_alloc,
445 ((jobres.plan_rsrc_usage_g * jobitem.cost_alloc) / jobitem.plan_qty) * jobitem.actual_qty
446 )) std_usage_qty_g,
447 sum(DECODE(jobres.scale_type,
448 0, jobres.plan_rsrc_usage_g * jobitem.cost_alloc,
449 ((jobres.plan_rsrc_usage_g * jobitem.cost_alloc) / jobitem.plan_qty) * jobitem.actual_qty
450 ) * jobres_uom.std_factor / rescost_uom.std_factor * rescost.nominal_cost)
451 std_usage_val_b,
452 sum(DECODE(jobres.scale_type,
453 0, jobres.plan_rsrc_usage_g * jobitem.cost_alloc,
454 ((jobres.plan_rsrc_usage_g * jobitem.cost_alloc) / jobitem.plan_qty) * jobitem.actual_qty
455 ) * jobres_uom.std_factor / rescost_uom.std_factor * rescost.nominal_cost
456 * jobitem.conversion_rate) std_usage_val_g,
457 jobitem.job_id job_id,
458 jobitem.job_type job_type,
459 jobitem.assembly_item_id assembly_item_id,
460 jobres.department_id department_id,
461 jobitem.source source,
462 SYSDATE creation_date,
463 SYSDATE last_update_date,
464 g_user_id created_by,
465 g_user_id last_updated_by,
466 g_login_id last_update_login
467 FROM
468 (
469 SELECT
470 job.organization_id,
471 job.assembly_item_id,
472 bmatl.plan_qty,
473 bmatl.actual_qty,
474 bmatl.cost_alloc,
475 job.job_id,
476 job.completion_date,
477 job.conversion_rate,
478 job.job_type,
479 job.source
480 FROM
481 opi_dbi_jobs_f job,
482 mtl_system_items_b msi,
483 ic_item_mst_b i,
484 gme_material_details bmatl
485 WHERE
486 job.job_type = 4
487 AND job.std_res_flag = 1
488 AND bmatl.batch_id = job.job_id
489 AND bmatl.line_type = 1 -- coproducts
490 AND msi.inventory_item_id = job.assembly_item_id
491 AND msi.organization_id = job.organization_id
492 AND i.item_no = msi.segment1
493 AND bmatl.item_id = i.item_id
494 ) jobitem,
495 (
496 SELECT /*+ ORDERED */
497 job.job_id,
498 job.assembly_item_id,
499 bres.scale_type,
500 resdtl.usage_um,
501 resdtl.resource_id,
502 resdtl.orgn_code,
503 resdtl.resources,
504 resmst.resource_class department_id,
505 bres.plan_rsrc_usage * bresuom.std_factor / ruom.std_factor plan_rsrc_usage,
506 bres.plan_rsrc_usage * bresuom.std_factor / hruom.std_factor plan_rsrc_usage_g,
507 pol.gl_cost_mthd
508 FROM
509 opi_dbi_jobs_f job,
510 gme_batch_header bhdr,
511 gme_batch_steps bstep,
512 gme_batch_step_resources bres,
513 cr_rsrc_dtl resdtl,
514 cr_rsrc_mst_b resmst,
515 sy_orgn_mst_b o,
516 gl_plcy_mst pol,
517 sy_uoms_mst bresuom,
518 sy_uoms_mst ruom,
519 sy_uoms_mst hruom
520 WHERE
521 job.std_res_flag = 1
522 AND job.job_type = 4
523 AND bhdr.batch_id = job.job_id
524 AND o.orgn_code = bhdr.plant_code
525 AND pol.co_code = o.co_code
526 AND bstep.batch_id = job.job_id
527 AND bres.batchstep_id = bstep.batchstep_id
528 AND resdtl.orgn_code = bhdr.plant_code
529 AND resdtl.resources = bres.resources
530 AND resmst.resources = resdtl.resources
531 AND bresuom.um_code = bres.usage_uom
532 AND ruom.um_code = resdtl.usage_um
533 AND hruom.um_code = g_hr_uom
534 ) jobres,
535 (
536 SELECT
537 hdr.cost_mthd_code,
538 dtl.calendar_code,
539 dtl.period_code,
540 dtl.start_date,
541 dtl.end_date
542 FROM
543 cm_cldr_hdr_b hdr,
544 cm_cldr_dtl dtl
545 WHERE
546 hdr.calendar_code = dtl.calendar_code
547 AND dtl.end_date >= g_global_start_date
548 AND dtl.start_date <= sysdate
549 ) cal,
550 cm_rsrc_dtl rescost,
551 sy_uoms_mst jobres_uom,
552 sy_uoms_mst rescost_uom
553 WHERE
554 jobres.job_id = jobitem.job_id -- combine all batch resources with all batch coproducts
555 AND jobres.assembly_item_id = jobitem.assembly_item_id
556 AND cal.cost_mthd_code = jobres.gl_cost_mthd
557 AND jobitem.completion_date BETWEEN cal.start_date AND cal.end_date
558 AND rescost.resources = jobres.resources
559 AND rescost.orgn_code = jobres.orgn_code
560 AND rescost.calendar_code = cal.calendar_code
561 AND rescost.period_code = cal.period_code
562 AND jobres_uom.um_code = jobres.usage_um
563 AND rescost_uom.um_code = rescost.usage_um
564 group by
565 jobitem.organization_id,
566 jobres.department_id,
567 jobitem.job_id,
568 jobitem.job_type,
569 jobitem.assembly_item_id,
570 jobres.usage_um,
571 jobres.resource_id,
572 jobitem.completion_date,
573 jobitem.source;
574
575 l_rowcount := SQL%ROWCOUNT;
576
577 -- update JOb master's flag, for source 2
578 UPDATE opi_dbi_jobs_f SET std_res_flag = 0,
579 creation_date = sysdate,
580 last_update_date = sysdate,
581 created_by = g_user_id,
582 last_updated_by = g_user_id,
583 last_update_login = g_login_id
584 WHERE std_res_flag = 1
585 AND source = 2;
586
587 COMMIT;
588
589 BIS_COLLECTION_UTILITIES.put_line('OPM resource std: ' ||
590 TO_CHAR(l_rowcount) || ' rows initially collected into staging table at ' ||
591 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
592
593 EXCEPTION WHEN OTHERS THEN
594
595 Errbuf:= Sqlerrm;
596 Retcode:= SQLCODE;
597
598 ROLLBACK;
599 bis_collection_utilities.wrapup(p_status => FALSE,
600 p_count => 0,
601 p_message => 'failed in complete_refresh_margin.'
602 );
603
604 RAISE_APPLICATION_ERROR(-20000,errbuf);
605
606 END initial_opm_res_std;
607
608
609 PROCEDURE incremental_opm_res_avail (errbuf in out NOCOPY varchar2,
610 retcode in out NOCOPY varchar2)
611 IS
612 l_stmt_num NUMBER;
613 l_last_collection_date DATE;
614 l_rowcount NUMBER;
615 BEGIN
616
617 --dbms_output.put_line('starting incremental_opm_res_avail');
618
619 retcode := g_ok;
620
621 l_stmt_num := 10;
622
623 -- get boundary
624 SELECT Trunc(last_collection_date)
625 INTO l_last_collection_date
626 FROM opi_dbi_run_log_curr
627 WHERE etl_id = 5
628 AND source = 2;
629
630 -- if not sysdate, start from the day after last_collection_date
631 IF l_last_collection_date <> Trunc(Sysdate) THEN
632 l_last_collection_date := l_last_collection_date + 1;
633 END IF;
634
635 --dbms_output.put_line('l_last_collection_date ' || l_last_collection_date );
636
637 -- populate availability for opm resource
638
639 --dbms_output.put_line('Preparing to Insert OPM Resource Availability into opi_dbi_res_avail_stg');
640 --dbms_output.put_line('g_hr_uom := ' || g_hr_uom);
641 --dbms_output.put_line('g_global_start_date = ' || to_char(g_global_start_date));
642
643 INSERT INTO opi_dbi_res_avail_stg
644 ( resource_id, organization_id, department_id, transaction_date,
645 uom, avail_qty, avail_qty_g, avail_val_b, source,
646 creation_date, last_update_date, created_by,
647 last_updated_by, last_update_login)
648 SELECT
649 r.resource_id resource_id,
650 r.organization_id organization_id,
651 r.department_id department_id,
652 r.shift_date transaction_date,
653 r.usage_um uom,
654 SUM(r.shift_hours * hruom.std_factor / ruom.std_factor)
655 avail_qty,
656 SUM(r.shift_hours) avail_qty_g, -- availability in hours
657 SUM(r.shift_hours * hruom.std_factor / rcostuom.std_factor * rcost.nominal_cost)
658 avail_val_b,
659 2 source,
660 SYSDATE creation_date,
661 SYSDATE last_update_date,
662 g_user_id created_by,
663 g_user_id last_updated_by,
664 g_login_id last_update_login
665 FROM
666 sy_uoms_mst hruom,
667 sy_uoms_mst ruom,
668 sy_uoms_mst rcostuom,
669 (
670 SELECT
671 rdtl.orgn_code,
672 rdtl.resources,
673 rdtl.resource_id,
674 rmst.resource_class department_id,
675 rdtl.usage_um,
676 plant.co_code,
677 pol.gl_cost_mthd cost_mthd_code,
678 whse.mtl_organization_id organization_id,
679 ravail.shift_date,
680 SUM((ravail.to_time - ravail.from_time)/3600) shift_hours
681 FROM
682 cr_rsrc_dtl rdtl,
683 cr_rsrc_mst_b rmst,
684 sy_orgn_mst_b plant,
685 gl_plcy_mst pol,
686 ic_whse_mst whse,
687 gmp_resource_avail ravail
688 WHERE
689 rmst.resources = rdtl.resources
690 AND plant.orgn_code = rdtl.orgn_code
691 AND pol.co_code = plant.co_code
692 AND whse.whse_code = plant.resource_whse_code
693 AND ravail.plant_code = rdtl.orgn_code
694 AND ravail.resource_id = rdtl.resource_id
695 AND NVL(ravail.resource_instance_id,0) = 0 -- exclude individual resource instances
696 AND ravail.shift_date BETWEEN l_last_collection_date AND SYSDATE
697 AND ravail.shift_date >= trunc(rdtl.creation_date)
698 GROUP BY
699 rdtl.orgn_code,
700 rdtl.resources,
701 rdtl.resource_id,
702 rmst.resource_class,
703 rdtl.usage_um,
704 plant.co_code,
705 pol.gl_cost_mthd,
706 whse.mtl_organization_id,
707 ravail.shift_date
708 ) r,
709 (
710 SELECT
711 hdr.cost_mthd_code,
712 dtl.calendar_code,
713 dtl.period_code,
714 dtl.start_date,
715 dtl.end_date
716 FROM
717 cm_cldr_hdr_b hdr,
718 cm_cldr_dtl dtl
719 WHERE
720 hdr.calendar_code = dtl.calendar_code
721 AND dtl.end_date >= g_global_start_date
722 AND dtl.start_date <= sysdate
723 ) cal,
724 cm_rsrc_dtl rcost
725 WHERE
726 r.cost_mthd_code = cal.cost_mthd_code
727 AND r.shift_date BETWEEN cal.start_date AND cal.end_date
728 AND rcost.orgn_code = r.orgn_code
729 AND rcost.resources = r.resources
730 AND rcost.cost_mthd_code = cal.cost_mthd_code
731 AND rcost.calendar_code = cal.calendar_code
732 AND rcost.period_code = cal.period_code
733 AND hruom.um_code = g_hr_uom
734 AND ruom.um_code = r.usage_um
735 AND rcostuom.um_code = rcost.usage_um
736 GROUP BY
737 r.resource_id,
738 r.organization_id,
739 r.department_id,
740 r.shift_date,
741 r.usage_um;
742
743 l_rowcount := sql%rowcount;
744
745 COMMIT;
746
747 BIS_COLLECTION_UTILITIES.put_line('OPM resource availability: ' ||
748 TO_CHAR(l_rowcount) || ' rows incrementally collected into staging table at ' ||
749 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
750
751 EXCEPTION WHEN OTHERS THEN
752
753 Errbuf:= Sqlerrm;
754 Retcode:= SQLCODE;
755
756 ROLLBACK;
757
758 BIS_COLLECTION_UTILITIES.PUT_LINE('Exception in incremental_opm_res_avail ' || errbuf );
759
760 --dbms_output.put_line('incremental_opm_res_avail ' || errbuf);
761 END incremental_opm_res_avail;
762
763
764
765 PROCEDURE incremental_opm_res_actual (errbuf in out NOCOPY varchar2,
766 retcode in out NOCOPY varchar2) IS
767 l_stmt_num NUMBER;
768 l_rowcount NUMBER;
769
770 BEGIN
771
772 retcode := g_ok;
773
774 --dbms_output.put_line('Preparing to insert into opi_dbi_res_actual_stg');
775 --dbms_output.put_line('g_hr_uom = ' || g_hr_uom);
776 --dbms_output.put_line('g_global_start_date = ' || to_char(g_global_start_date));
777
778
779 INSERT INTO opi_dbi_res_actual_stg
780 ( resource_id, organization_id, transaction_date,
781 actual_qty, uom, actual_qty_g, actual_val_b, source,
782 job_id, job_type, assembly_item_id, department_id,
783 creation_date, last_update_date, created_by,
784 last_updated_by, last_update_login )
785 SELECT
786 r.resource_id resource_id,
787 r.organization_id organization_id,
788 r.transaction_date transaction_date,
789 r.actual_qty actual_qty,
790 r.uom uom,
791 r.actual_qty_g actual_qty_g,
792 r.actual_qty
793 * rcost.nominal_cost actual_qty_b,
794 2 source,
795 r.job_id job_id,
796 4 job_type,
797 r.assembly_item_id assembly_item_id,
798 r.department_id department_id,
799 SYSDATE creation_date,
800 SYSDATE last_update_date,
801 g_user_id created_by,
802 g_user_id last_updated_by,
803 g_login_id last_update_login
804 FROM
805 (
806 SELECT
807 msi.inventory_item_id assembly_item_id,
808 rtran.doc_id job_id,
809 rdtl.resource_id resource_id,
810 rdtl.resources resources,
811 rmst.resource_class department_id,
812 whse.mtl_organization_id organization_id,
813 TRUNC(rtran.trans_date) transaction_date,
814 SUM(rtran.resource_usage * prod.cost_alloc) actual_qty,
815 rtran.trans_um uom,
816 rtran.orgn_code orgn_code,
817 SUM(rtran.resource_usage * prod.cost_alloc * hruom.std_factor / ruom.std_factor) actual_qty_g,
818 pol.gl_cost_mthd cost_mthd_code,
819 pol.co_code co_code
820 FROM
821 cr_rsrc_dtl rdtl,
822 cr_rsrc_mst_b rmst,
823 sy_orgn_mst_b plant,
824 ic_whse_mst whse,
825 gme_resource_txns rtran,
826 gme_material_details prod,
827 ic_item_mst_b item,
828 mtl_system_items_b msi,
829 gl_plcy_mst pol,
830 opi_dbi_run_log_curr rlc,
831 sy_uoms_mst hruom,
832 sy_uoms_mst ruom
833 WHERE
834 rlc.etl_id = 4
835 AND rlc.source = 2
836 AND rtran.poc_trans_id >= rlc.start_txn_id
837 AND rtran.poc_trans_id < rlc.next_start_txn_id
838 AND rtran.completed_ind = 1
839 AND prod.batch_id = rtran.doc_id
840 AND prod.line_type = 1
841 AND item.item_id = prod.item_id
842 AND msi.organization_id = whse.mtl_organization_id
843 AND msi.segment1 = item.item_no
844 AND rdtl.orgn_code = rtran.orgn_code
845 AND rdtl.resources = rtran.resources
846 AND rmst.resources = rdtl.resources
847 AND plant.orgn_code = rdtl.orgn_code
848 AND whse.whse_code = plant.resource_whse_code
849 AND pol.co_code = plant.co_code
850 AND hruom.um_code = g_hr_uom
851 AND ruom.um_code = rtran.trans_um
852 GROUP BY
853 msi.inventory_item_id,
854 rtran.doc_id,
855 rdtl.resource_id,
856 rdtl.resources,
857 rmst.resource_class,
858 whse.mtl_organization_id,
859 TRUNC(rtran.trans_date),
860 rtran.trans_um,
861 rtran.orgn_code,
862 pol.gl_cost_mthd,
863 pol.co_code
864 ) r,
865 (
866 SELECT
867 hdr.co_code,
868 hdr.cost_mthd_code,
869 dtl.calendar_code,
870 dtl.period_code,
871 dtl.start_date,
872 dtl.end_date
873 FROM
874 gl_plcy_mst pol,
875 cm_cldr_hdr_b hdr,
876 cm_cldr_dtl dtl
877 WHERE
878 hdr.co_code = pol.co_code
879 AND hdr.cost_mthd_code = pol.gl_cost_mthd
880 AND hdr.calendar_code = dtl.calendar_code
881 AND dtl.end_date >= g_global_start_date
882 AND dtl.start_date <= sysdate
883 ) cal,
884 cm_rsrc_dtl rcost
885 WHERE
886 r.co_code = cal.co_code
887 AND r.cost_mthd_code = cal.cost_mthd_code
888 AND r.transaction_date BETWEEN cal.start_date AND cal.end_date
889 AND rcost.orgn_code = r.orgn_code
890 AND rcost.resources = r.resources
891 AND rcost.cost_mthd_code = cal.cost_mthd_code
892 AND rcost.calendar_code = cal.calendar_code
893 AND rcost.period_code = cal.period_code
894 ;
895
896 l_rowcount := sql%rowcount;
897
898 COMMIT;
899
900 BIS_COLLECTION_UTILITIES.put_line('OPM resource actuals: ' ||
901 TO_CHAR(l_rowcount) || ' rows incrementally collected into staging table at ' ||
902 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
903
904 EXCEPTION WHEN OTHERS THEN
905
906 Errbuf:= Sqlerrm;
907 Retcode:= SQLCODE;
908
909 ROLLBACK;
910
911 BIS_COLLECTION_UTILITIES.PUT_LINE('Exception in incremental_opm_res_actual ' || errbuf );
912
913 --dbms_output.put_line('incremental_opm_res_actual ' || errbuf);
914
915 END incremental_opm_res_actual;
916
917
918
919 PROCEDURE incremental_opm_res_std (errbuf in out NOCOPY varchar2,
920 retcode in out NOCOPY VARCHAR2 ) IS
921 l_stmt_num NUMBER;
922 l_rowcount NUMBER;
923 l_err_num NUMBER;
924 l_err_msg VARCHAR2(255);
925 l_error_flag BOOLEAN := FALSE;
926
927 l_opi_schema VARCHAR2(30);
928 l_status VARCHAR2(30);
929 l_industry VARCHAR2(30);
930
931 BEGIN
932
933 DECLARE
934 lv_errbuf varchar2(1024);
935 lv_retcode NUMBER;
936 BEGIN
937 check_setup_globals(lv_errbuf,lv_retcode);
938 END;
939
940 --dbms_output.put_line('before insert into opi_dbi_res_std_f');
941
942 --dbms_output.put_line('g_hr_uom = ' || g_hr_uom);
943 --dbms_output.put_line('g_global_start_date = ' || to_char(g_global_start_date));
944
945 DELETE opi_dbi_res_std_f std
946 WHERE (job_id, job_type)
947 IN ( SELECT job_id, job_type
948 FROM opi_dbi_jobs_f
949 WHERE std_res_flag = 1
950 AND job_type = 4 -- need to extract again
951 );
952
953 INSERT INTO opi_dbi_res_std_f
954 (resource_id,
955 organization_id,
956 transaction_date,
957 std_usage_qty,
958 uom,
959 std_usage_qty_g,
960 std_usage_val_b,
961 std_usage_val_g,
962 job_id,
963 job_type,
964 assembly_item_id,
965 department_id,
966 source,
967 creation_date,
968 last_update_date,
969 created_by,
970 last_updated_by,
971 last_update_login)
972 select
973 jobres.resource_id resource_id,
974 jobitem.organization_id organization_id,
975 jobitem.completion_date transaction_date,
976 sum(DECODE(jobres.scale_type,
977 0, jobres.plan_rsrc_usage * jobitem.cost_alloc,
978 ((jobres.plan_rsrc_usage * jobitem.cost_alloc) / jobitem.plan_qty) * jobitem.actual_qty
979 )) std_usage_qty,
980 jobres.usage_um uom,
981 sum(DECODE(jobres.scale_type,
982 0, jobres.plan_rsrc_usage_g * jobitem.cost_alloc,
983 ((jobres.plan_rsrc_usage_g * jobitem.cost_alloc) / jobitem.plan_qty) * jobitem.actual_qty
984 )) std_usage_qty_g,
985 sum(DECODE(jobres.scale_type,
986 0, jobres.plan_rsrc_usage_g * jobitem.cost_alloc,
987 ((jobres.plan_rsrc_usage_g * jobitem.cost_alloc) / jobitem.plan_qty) * jobitem.actual_qty
988 ) * jobres_uom.std_factor / rescost_uom.std_factor * rescost.nominal_cost)
989 std_usage_val_b,
990 sum(DECODE(jobres.scale_type,
991 0, jobres.plan_rsrc_usage_g * jobitem.cost_alloc,
992 ((jobres.plan_rsrc_usage_g * jobitem.cost_alloc) / jobitem.plan_qty) * jobitem.actual_qty
993 ) * jobres_uom.std_factor / rescost_uom.std_factor * rescost.nominal_cost
994 * jobitem.conversion_rate) std_usage_val_g,
995 jobitem.job_id job_id,
996 jobitem.job_type job_type,
997 jobitem.assembly_item_id assembly_item_id,
998 jobres.department_id department_id,
999 jobitem.source source,
1000 SYSDATE creation_date,
1001 SYSDATE last_update_date,
1002 g_user_id created_by,
1003 g_user_id last_updated_by,
1004 g_login_id last_update_login
1005 FROM
1006 (
1007 SELECT
1008 job.organization_id,
1009 job.assembly_item_id,
1010 bmatl.plan_qty,
1011 bmatl.actual_qty,
1012 bmatl.cost_alloc,
1013 job.job_id,
1014 job.completion_date,
1015 job.conversion_rate,
1016 job.job_type,
1017 job.source
1018 FROM
1019 opi_dbi_jobs_f job,
1020 mtl_system_items_b msi,
1021 ic_item_mst_b i,
1022 gme_material_details bmatl
1023 WHERE
1024 job.job_type = 4
1025 AND job.std_res_flag = 1
1026 AND bmatl.batch_id = job.job_id
1027 AND bmatl.line_type = 1 -- coproducts
1028 AND msi.inventory_item_id = job.assembly_item_id
1029 AND msi.organization_id = job.organization_id
1030 AND i.item_no = msi.segment1
1031 AND bmatl.item_id = i.item_id
1032 ) jobitem,
1033 (
1034 SELECT /*+ ORDERED */
1035 job.job_id,
1036 job.assembly_item_id,
1037 bres.scale_type,
1038 resdtl.usage_um,
1039 resdtl.resource_id,
1040 resdtl.orgn_code,
1041 resdtl.resources,
1042 resmst.resource_class department_id,
1043 bres.plan_rsrc_usage * bresuom.std_factor / ruom.std_factor plan_rsrc_usage,
1044 bres.plan_rsrc_usage * bresuom.std_factor / hruom.std_factor plan_rsrc_usage_g,
1045 pol.gl_cost_mthd
1046 FROM
1047 opi_dbi_jobs_f job,
1048 gme_batch_header bhdr,
1049 gme_batch_steps bstep,
1050 gme_batch_step_resources bres,
1051 cr_rsrc_dtl resdtl,
1052 cr_rsrc_mst_b resmst,
1053 sy_orgn_mst_b o,
1054 gl_plcy_mst pol,
1055 sy_uoms_mst bresuom,
1056 sy_uoms_mst ruom,
1057 sy_uoms_mst hruom
1058 WHERE
1059 job.std_res_flag = 1
1060 AND job.job_type = 4
1061 AND bhdr.batch_id = job.job_id
1062 AND o.orgn_code = bhdr.plant_code
1063 AND pol.co_code = o.co_code
1064 AND bstep.batch_id = job.job_id
1065 AND bres.batchstep_id = bstep.batchstep_id
1066 AND resdtl.orgn_code = bhdr.plant_code
1067 AND resdtl.resources = bres.resources
1068 AND resmst.resources = resdtl.resources
1069 AND bresuom.um_code = bres.usage_uom
1070 AND ruom.um_code = resdtl.usage_um
1071 AND hruom.um_code = g_hr_uom
1072 ) jobres,
1073 (
1074 SELECT
1075 hdr.cost_mthd_code,
1076 dtl.calendar_code,
1077 dtl.period_code,
1078 dtl.start_date,
1079 dtl.end_date
1080 FROM
1081 cm_cldr_hdr_b hdr,
1082 cm_cldr_dtl dtl
1083 WHERE
1084 hdr.calendar_code = dtl.calendar_code
1085 AND dtl.end_date >= g_global_start_date
1086 AND dtl.start_date <= sysdate
1087 ) cal,
1088 cm_rsrc_dtl rescost,
1089 sy_uoms_mst jobres_uom,
1090 sy_uoms_mst rescost_uom
1091 WHERE
1092 jobres.job_id = jobitem.job_id -- combine all batch resources with all batch coproducts
1093 AND jobres.assembly_item_id = jobitem.assembly_item_id
1094 AND cal.cost_mthd_code = jobres.gl_cost_mthd
1095 AND jobitem.completion_date BETWEEN cal.start_date AND cal.end_date
1096 AND rescost.resources = jobres.resources
1097 AND rescost.orgn_code = jobres.orgn_code
1098 AND rescost.calendar_code = cal.calendar_code
1099 AND rescost.period_code = cal.period_code
1100 AND jobres_uom.um_code = jobres.usage_um
1101 AND rescost_uom.um_code = rescost.usage_um
1102 group by
1103 jobitem.organization_id,
1104 jobres.department_id,
1105 jobitem.job_id,
1106 jobitem.job_type,
1107 jobitem.assembly_item_id,
1108 jobres.usage_um,
1109 jobres.resource_id,
1110 jobitem.completion_date,
1111 jobitem.source;
1112
1113 l_rowcount := SQL%ROWCOUNT;
1114
1115 -- update JOb master's flag, for source 2
1116 UPDATE opi_dbi_jobs_f SET std_res_flag = 0,
1117 creation_date = sysdate,
1118 last_update_date = sysdate,
1119 created_by = g_user_id,
1120 last_updated_by = g_user_id,
1121 last_update_login = g_login_id
1122 WHERE std_res_flag = 1
1123 AND source = 2;
1124
1125 BIS_COLLECTION_UTILITIES.put_line('OPM resource std: ' ||
1126 TO_CHAR(l_rowcount) || ' rows incrementally collected into staging table at ' ||
1127 To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1128
1129 EXCEPTION WHEN OTHERS THEN
1130
1131 Errbuf:= Sqlerrm;
1132 Retcode:= SQLCODE;
1133
1134 ROLLBACK;
1135 bis_collection_utilities.wrapup(p_status => FALSE,
1136 p_count => 0,
1137 p_message => 'failed in complete_refresh_margin.'
1138 );
1139
1140 RAISE_APPLICATION_ERROR(-20000,errbuf);
1141
1142 END incremental_opm_res_std;
1143
1144
1145
1146 END opi_dbi_res_opm_pkg;