[Home] [Help]
PACKAGE BODY: APPS.MRP_REPORT_INV_TURNS
Source
1 PACKAGE BODY mrp_report_inv_turns AS
2 /* $Header: MRPPRINB.pls 115.11 2002/07/15 06:03:33 sdgupta ship $ */
3
4 /*--------------------------- PUBLIC ROUTINES --------------------------------*/
5
6 PROCEDURE mrp_calculate_inventory_turns(
7 arg_query_id IN NUMBER,
8 arg_org_id IN NUMBER,
9 arg_compile_desig IN VARCHAR2,
10 arg_sched_desig IN VARCHAR2,
11 arg_cost_type IN NUMBER,
12 arg_def_cost_type IN NUMBER) IS
13 /*----------------------+
14 | Variable declarations |
15 +----------------------*/
16 arg_calendar_code VARCHAR2(10);
17 arg_exception_set_id NUMBER;
18 begin_inv NUMBER;
19 issued_inv NUMBER;
20 start_date_this_period DATE;
21 start_date_next_period DATE;
22 start_date_report DATE;
23 begin_inv_report NUMBER;
24 purchase_orders NUMBER;
25 purchase_reqs NUMBER;
26 planned_orders_buy NUMBER;
27 discrete_jobs NUMBER;
28 repetitive_schedules NUMBER;
29 planned_orders_make NUMBER;
30 master_schedule NUMBER;
31 past_due_master_schedule NUMBER;
32 cum_master_schedule NUMBER := 0;
33 first_period BOOLEAN := TRUE;
34 end_inv_prev_period NUMBER := 0;
35 end_inv_this_period NUMBER := 0;
36 period_turns NUMBER := 0;
37 cum_turns NUMBER := 0;
38 my_rowid ROWID;
39 select_on_hand EXCEPTION;
40 select_wip EXCEPTION;
41 update_beginning_inv EXCEPTION;
42 update_open_purchase_orders EXCEPTION;
43 update_open_purchase_reqs EXCEPTION;
44 update_mrp_purchase_orders EXCEPTION;
45 update_open_discrete_jobs EXCEPTION;
46 update_mrp_repetitive_scheds EXCEPTION;
47 update_mrp_discrete_jobs EXCEPTION;
48 update_master_sched_discrete EXCEPTION;
49 update_master_sched_repetitive EXCEPTION;
50 select_past_due_mds EXCEPTION;
51 update_past_due_mds EXCEPTION;
52 select_inventory_values EXCEPTION;
53 update_inventory_values EXCEPTION;
54
55 /*------------------------+
56 | Inventory values cursor |
57 +------------------------*/
58 CURSOR inv_values_cur IS
59 SELECT rowid,
60 date1,
61 date2,
62 number1,
63 number2,
64 number3,
65 number4,
66 number5,
67 number6,
68 number7,
69 number8
70 FROM mrp_form_query
71 WHERE query_id = arg_query_id
72 ORDER BY date1;
73 BEGIN
74 /*--------------------------+
75 | Select calendar defaults |
76 +--------------------------*/
77 mrp_calendar.select_calendar_defaults(
78 arg_org_id,
79 arg_calendar_code,
80 arg_exception_set_id);
81
82 /*-----------------------------------------------------------+
83 | Write org_id, compile_designator, and schedule_designator |
84 | for each row of the current query_id |
85 +-----------------------------------------------------------*/
86 update mrp_form_query
87 set char1 = arg_compile_desig,
88 char2 = arg_sched_desig,
89 number12 = arg_org_id
90 where query_id = arg_query_id;
91
92 /*----------------------------------+
93 | Calculate on-hand inventory value |
94 +----------------------------------*/
95 /*SELECT NVL(SUM((sys.nettable_inventory_quantity
96 + sys.nonnettable_inventory_quantity)
97 * NVL(cst.item_cost, 0)), 0)
98 / 1000*//*2417274*/
99 SELECT NVL(SUM((sys.nettable_inventory_quantity)
100 * NVL(cst.item_cost, 0)), 0)
101 / 1000
102 INTO begin_inv
103 FROM cst_item_costs cst,
104 mrp_system_items sys
105 WHERE cst.organization_id = sys.organization_id
106 AND cst.inventory_item_id = sys.inventory_item_id
107 AND ( cst.cost_type_id = arg_cost_type
108 OR
109 (
110 (cst.cost_type_id = arg_def_cost_type)
111 AND
112 (NOT EXISTS
113 (SELECT 'Primary Cost Type Row'
114 FROM cst_item_costs cst1
115 WHERE cst1.inventory_item_id = cst.inventory_item_id
116 AND cst1.organization_id = arg_org_id
117 AND cst1.cost_type_id = arg_cost_type)
118 )
119 )
120 )
121 AND sys.compile_designator = arg_compile_desig
122 AND sys.organization_id = arg_org_id;
123
124 IF SQL%NOTFOUND THEN
125 RAISE select_on_hand;
126 END IF;
127 /*------------------------------+
128 | Calculate WIP inventory value |
129 +------------------------------*/
130 SELECT NVL(SUM(NVL(wip.net_quantity, 0)
131 * NVL(cst.item_cost, 0)), 0)
132 / 1000
133 INTO issued_inv
134 FROM cst_item_costs cst,
135 mrp_wip_components wip
136 WHERE cst.organization_id = wip.organization_id
137 AND cst.inventory_item_id = wip.inventory_item_id
138 AND ( cst.cost_type_id = arg_cost_type
139 OR
140 (
141 (cst.cost_type_id = arg_def_cost_type)
142 AND
143 (NOT EXISTS
144 (SELECT 'Primary Cost Type Row'
145 FROM cst_item_costs cst1
146 WHERE cst1.inventory_item_id = cst.inventory_item_id
147 AND cst1.organization_id = arg_org_id
148 AND cst1.cost_type_id = arg_cost_type)
149 )
150 )
151 )
152 AND wip.compile_designator = arg_compile_desig
153 AND wip.organization_id = arg_org_id
154 AND wip.wip_entity_type IN (1, 3)
155 AND DECODE(wip.wip_entity_type,
156 1, 1, wip.supply_demand_type) =
157 DECODE(wip.wip_entity_type, 1, 1, 1);
158
159 IF SQL%NOTFOUND THEN
160 RAISE select_wip;
161 END IF;
162 /*----------------------------+
163 | Write sum to mrp_form_query |
164 +----------------------------*/
165 UPDATE mrp_form_query
166 SET number1 = begin_inv + issued_inv
167 WHERE query_id = arg_query_id
168 AND date2 IS NOT NULL;
169
170 IF SQL%NOTFOUND THEN
171 RAISE update_beginning_inv;
172 END IF;
173 /*-----------------------------------------+
174 | Calculate value of open purchase orders |
175 +-----------------------------------------*/
176 UPDATE mrp_form_query query
177 SET number2 =
178 (SELECT NVL(SUM(rec.new_order_quantity
179 * NVL(cst.item_cost, 0)), 0)
180 / 1000
181 FROM cst_item_costs cst,
182 mrp_recommendations rec
183 WHERE cst.organization_id = rec.organization_id
184 AND cst.inventory_item_id = rec.inventory_item_id
185 AND ( cst.cost_type_id = arg_cost_type
186 OR
187 (
188 (cst.cost_type_id = arg_def_cost_type)
189 AND
190 (NOT EXISTS
191 (SELECT 'Primary Cost Type Row'
192 FROM cst_item_costs cst1
193 WHERE cst1.inventory_item_id = cst.inventory_item_id
194 AND cst1.organization_id = arg_org_id
195 AND cst1.cost_type_id = arg_cost_type)
196 )
197 )
198 )
199 AND rec.new_schedule_date >= query.date1
200 AND rec.new_schedule_date < query.date2
201 AND rec.disposition_status_type = 1
202 AND rec.order_type IN (1, 8)
203 AND rec.compile_designator = arg_compile_desig
204 AND rec.organization_id = arg_org_id)
205 WHERE query_id = arg_query_id;
206
207 IF SQL%NOTFOUND THEN
208 RAISE update_open_purchase_orders;
209 END IF;
210 /*-----------------------------------------------+
211 | Calculate value of open purchase requisitions |
212 +-----------------------------------------------*/
213 UPDATE mrp_form_query query
214 SET number3 =
215 (SELECT NVL(SUM(rec.new_order_quantity
216 * NVL(cst.item_cost,0)), 0)
217 / 1000
218 FROM cst_item_costs cst,
219 mrp_recommendations rec
220 WHERE cst.organization_id = rec.organization_id
221 AND cst.inventory_item_id = rec.inventory_item_id
222 AND ( cst.cost_type_id = arg_cost_type
223 OR
224 (
225 (cst.cost_type_id = arg_def_cost_type)
226 AND
227 (NOT EXISTS
228 (SELECT 'Primary Cost Type Row'
229 FROM cst_item_costs cst1
230 WHERE cst1.inventory_item_id = cst.inventory_item_id
231 AND cst1.organization_id = arg_org_id
232 AND cst1.cost_type_id = arg_cost_type)
233 )
234 )
235 )
236 AND rec.new_schedule_date >= query.date1
237 AND rec.new_schedule_date < query.date2
238 AND rec.disposition_status_type = 1
239 AND rec.order_type = 2
240 AND rec.compile_designator = arg_compile_desig
241 AND rec.organization_id = arg_org_id)
242 WHERE query_id = arg_query_id;
243
244 IF SQL%NOTFOUND THEN
245 RAISE update_open_purchase_reqs;
246 END IF;
247 /*------------------------------------------------+
248 | Calculate value of MRP planned purchase orders |
249 +------------------------------------------------*/
250 UPDATE mrp_form_query query
251 SET number4 =
252 (SELECT NVL(SUM(rec.new_order_quantity
253 * NVL(cst.item_cost, 0)), 0)
254 / 1000
255 FROM cst_item_costs cst,
256 mrp_system_items sys,
257 mrp_recommendations rec
258 WHERE cst.organization_id = sys.organization_id
259 AND cst.inventory_item_id = sys.inventory_item_id
260 AND ( cst.cost_type_id = arg_cost_type
261 OR
262 (
263 (cst.cost_type_id = arg_def_cost_type)
264 AND
265 (NOT EXISTS
266 (SELECT 'Primary Cost Type Row'
267 FROM cst_item_costs cst1
268 WHERE cst1.inventory_item_id = cst.inventory_item_id
269 AND cst1.organization_id = arg_org_id
270 AND cst1.cost_type_id = arg_cost_type)
271 )
272 )
273 )
274 AND sys.inventory_item_id = rec.inventory_item_id
275 AND sys.compile_designator = rec.compile_designator
276 AND sys.organization_id = rec.organization_id
277 AND sys.planning_make_buy_code = 2
278 AND rec.new_schedule_date >= query.date1
279 AND rec.new_schedule_date < query.date2
280 AND rec.disposition_status_type = 1
281 AND rec.order_type = 5
282 AND rec.compile_designator = arg_compile_desig
283 AND rec.organization_id = arg_org_id)
284 WHERE query_id = arg_query_id;
285
286 IF SQL%NOTFOUND THEN
287 RAISE update_mrp_purchase_orders;
288 END IF;
289 /*-----------------------------------------------------------------+
290 | Calculate value of open discrete jobs and open flow schedules, |
291 | excluding material costs. Note that we include flow schedule |
292 | costs under the heading of discrete jobs in the report. |
293 +-----------------------------------------------------------------*/
294 UPDATE mrp_form_query query
295 SET number5 =
296 (SELECT NVL(SUM(rec.new_order_quantity
297 * (NVL(cst.tl_resource, 0)
298 + NVL(cst.tl_overhead, 0)
299 + NVL(cst.tl_material_overhead, 0)
300 + NVL(cst.tl_outside_processing, 0))), 0)
301 / 1000
302 FROM cst_item_costs cst,
303 mrp_recommendations rec
304 WHERE cst.inventory_item_id = rec.inventory_item_id
305 AND cst.organization_id = rec.organization_id
306 AND ( cst.cost_type_id = arg_cost_type
307 OR
308 (
309 (cst.cost_type_id = arg_def_cost_type)
310 AND
311 (NOT EXISTS
312 (SELECT 'Primary Cost Type Row'
313 FROM cst_item_costs cst1
314 WHERE cst1.inventory_item_id = cst.inventory_item_id
315 AND cst1.organization_id = arg_org_id
316 AND cst1.cost_type_id = arg_cost_type)
317 )
318 )
319 )
320 AND rec.new_wip_start_date >= query.date1
321 AND rec.new_wip_start_date < query.date2
322 AND rec.disposition_status_type = 1
323 AND rec.order_type in (3, 27)
324 AND rec.compile_designator = arg_compile_desig
325 AND rec.organization_id = arg_org_id)
326 WHERE query_id = arg_query_id;
327
328 IF SQL%NOTFOUND THEN
329 RAISE update_open_discrete_jobs;
330 END IF;
331 /*------------------------------------------------------------------------+
332 | Calculate value of suggested repetitive schedules, excluding material |
333 | costs |
334 +------------------------------------------------------------------------*/
335 UPDATE mrp_form_query query
336 SET number6 =
337 (SELECT NVL(SUM(NVL(rec.daily_rate, 0)
338 * (NVL(cst.tl_resource, 0)
339 + NVL(cst.tl_overhead, 0)
340 + NVL(cst.tl_material_overhead, 0)
341 + NVL(cst.tl_outside_processing, 0))), 0)
342 / 1000
343 FROM cst_item_costs cst,
344 bom_calendar_dates cal,
345 mrp_recommendations rec
346 WHERE cst.inventory_item_id = rec.inventory_item_id
347 AND cst.organization_id = rec.organization_id
348 AND ( cst.cost_type_id = arg_cost_type
349 OR
350 (
351 (cst.cost_type_id = arg_def_cost_type)
352 AND
353 (NOT EXISTS
354 (SELECT 'Primary Cost Type Row'
355 FROM cst_item_costs cst1
356 WHERE cst1.inventory_item_id = cst.inventory_item_id
357 AND cst1.organization_id = arg_org_id
358 AND cst1.cost_type_id = arg_cost_type)
359 )
360 )
361 )
362 AND rec.last_unit_start_date >= query.date1
363 AND rec.first_unit_start_date < query.date2
364 AND cal.calendar_date BETWEEN
365 GREATEST(rec.first_unit_start_date,
366 query.date1)
367 AND
368 LEAST(rec.last_unit_start_date,
369 (query.date2 - 1))
370 AND cal.calendar_code = arg_calendar_code
371 AND cal.exception_set_id = arg_exception_set_id
372 AND cal.seq_num IS NOT NULL
373 AND rec.disposition_status_type = 1
374 AND rec.order_type = 4
375 AND rec.compile_designator = arg_compile_desig
376 AND rec.organization_id = arg_org_id)
377 WHERE query_id = arg_query_id;
378
379 IF SQL%NOTFOUND THEN
380 RAISE update_mrp_repetitive_scheds;
381 END IF;
382 /*------------------------------------------------------------------------+
383 | Calculate value of MRP planned discrete jobs, excluding material costs |
384 +------------------------------------------------------------------------*/
385 UPDATE mrp_form_query query
386 SET number7 =
387 (SELECT NVL(SUM(rec.new_order_quantity
388 * (NVL(cst.tl_resource, 0)
389 + NVL(cst.tl_overhead, 0)
390 + NVL(cst.tl_material_overhead, 0)
391 + NVL(cst.tl_outside_processing, 0))), 0)
392 / 1000
393 FROM cst_item_costs cst,
394 mrp_system_items sys,
395 mrp_recommendations rec
396 WHERE cst.inventory_item_id = sys.inventory_item_id
397 AND cst.organization_id = sys.organization_id
398 AND ( cst.cost_type_id = arg_cost_type
399 OR
400 (
401 (cst.cost_type_id = arg_def_cost_type)
402 AND
403 (NOT EXISTS
404 (SELECT 'Primary Cost Type Row'
405 FROM cst_item_costs cst1
406 WHERE cst1.inventory_item_id = cst.inventory_item_id
407 AND cst1.organization_id = arg_org_id
408 AND cst1.cost_type_id = arg_cost_type)
409 )
410 )
411 )
412 AND sys.inventory_item_id = rec.inventory_item_id
413 AND sys.compile_designator = rec.compile_designator
414 AND sys.organization_id = rec.organization_id
415 AND sys.planning_make_buy_code = 1
416 AND rec.new_schedule_date >= query.date1
417 AND rec.new_schedule_date < query.date2
418 AND rec.disposition_status_type = 1
419 AND rec.order_type = 5
420 AND rec.compile_designator = arg_compile_desig
421 AND rec.organization_id = arg_org_id)
422 WHERE query_id = arg_query_id;
423
424 IF SQL%NOTFOUND THEN
425 RAISE update_mrp_discrete_jobs;
426 END IF;
427 /*----------------------------------------------------+
428 | Calculate value of master schedule, discrete items |
429 +----------------------------------------------------*/
430 UPDATE mrp_form_query query
431 SET number8 =
432 (SELECT NVL(SUM(dates.schedule_quantity * cst.item_cost), 0)
433 / 1000
434 FROM mrp_schedule_dates dates,
435 cst_item_costs cst,
436 mrp_system_items sys,
437 mrp_schedule_designators sched
438 WHERE cst.organization_id = sys.organization_id
439 AND cst.inventory_item_id = sys.inventory_item_id
440 AND ( cst.cost_type_id = arg_cost_type
441 OR
442 (
443 (cst.cost_type_id = arg_def_cost_type)
444 AND
445 (NOT EXISTS
446 (SELECT 'Primary Cost Type Row'
447 FROM cst_item_costs cst1
448 WHERE cst1.inventory_item_id = cst.inventory_item_id
449 AND cst1.organization_id = arg_org_id
450 AND cst1.cost_type_id = arg_cost_type)
451 )
452 )
453 )
454 AND dates.organization_id = sys.organization_id
455 AND dates.inventory_item_id = sys.inventory_item_id
456 AND dates.schedule_date >= query.date1
457 AND dates.schedule_date < query.date2
458 AND dates.schedule_level = 3
459 AND dates.schedule_designator = arg_sched_desig
460 AND sched.schedule_designator = arg_sched_desig
461 AND sched.organization_id = arg_org_id
462 AND sys.repetitive_type = 1
463 AND sys.compile_designator = arg_compile_desig
464 AND sys.organization_id = arg_org_id)
465 WHERE query_id = arg_query_id;
466
467 IF SQL%NOTFOUND THEN
468 RAISE update_master_sched_discrete;
469 END IF;
470 /*------------------------------------------------------+
471 | Calculate value of master schedule, repetitive items |
472 +------------------------------------------------------*/
473 UPDATE mrp_form_query query
474 SET number8 =
475 (SELECT query.number8 +
476 NVL(SUM(cst.item_cost
477 * dates.repetitive_daily_rate), 0)
478 / 1000
479 FROM bom_calendar_dates cal,
480 mrp_schedule_dates dates,
481 cst_item_costs cst,
482 mrp_system_items sys,
483 mrp_schedule_designators sched
484 WHERE cst.organization_id = sys.organization_id
485 AND cst.inventory_item_id = sys.inventory_item_id
486 AND ( cst.cost_type_id = arg_cost_type
487 OR
488 (
489 (cst.cost_type_id = arg_def_cost_type)
490 AND
491 (NOT EXISTS
492 (SELECT 'Primary Cost Type Row'
493 FROM cst_item_costs cst1
494 WHERE cst1.inventory_item_id = cst.inventory_item_id
495 AND cst1.organization_id = arg_org_id
496 AND cst1.cost_type_id = arg_cost_type)
497 )
498 )
499 )
500 AND dates.organization_id = sys.organization_id
501 AND dates.inventory_item_id = sys.inventory_item_id
502 AND dates.rate_end_date >= query.date1
503 AND dates.schedule_date < query.date2
504 AND dates.schedule_level = 3
505 AND dates.schedule_designator = arg_sched_desig
506 AND sched.schedule_designator = arg_sched_desig
507 AND sched.organization_id = arg_org_id
508 AND cal.calendar_date BETWEEN
509 GREATEST(dates.schedule_date,
510 query.date1)
511 AND
512 LEAST(dates.rate_end_date,
513 (query.date2 - 1))
514 AND cal.calendar_code = arg_calendar_code
515 AND cal.exception_set_id = arg_exception_set_id
516 AND cal.seq_num IS NOT NULL
517 AND sys.repetitive_type = 2
518 AND sys.compile_designator = arg_compile_desig
519 AND sys.organization_id = arg_org_id)
520 WHERE query_id = arg_query_id;
521
522 IF SQL%NOTFOUND THEN
523 RAISE update_master_sched_repetitive;
524 END IF;
525 /*--------------------------------------------------------------+
526 | Calculate ending inventory, period turns and cumulative turns |
527 +--------------------------------------------------------------*/
528 OPEN inv_values_cur;
529 LOOP
530 FETCH inv_values_cur INTO
531 my_rowid,
532 start_date_this_period,
533 start_date_next_period,
534 begin_inv_report,
535 purchase_orders,
536 purchase_reqs,
537 planned_orders_buy,
538 discrete_jobs,
539 repetitive_schedules,
540 planned_orders_make,
541 master_schedule;
542
546 RAISE select_inventory_values;
543 EXIT WHEN inv_values_cur%NOTFOUND;
544
545 IF SQL%NOTFOUND THEN
547 END IF;
548 /*--------------------------------------------------------+
549 | Initialize inventory values for report and first period |
550 +--------------------------------------------------------*/
551 IF first_period = TRUE THEN
552 first_period := FALSE;
553 start_date_report := start_date_this_period;
554 end_inv_prev_period := begin_inv_report;
555 /* following code added for bug fix 399945 */
556 /*---------------------------------------------+
557 | Calculate value of past due master schedule |
558 +---------------------------------------------*/
559 SELECT NVL(SUM(cst.item_cost
560 * NVL(dates.repetitive_daily_rate,
561 dates.schedule_quantity)), 0) / 1000
562 INTO past_due_master_schedule
563 FROM bom_calendar_dates cal,
564 mrp_schedule_dates dates,
565 cst_item_costs cst,
566 mrp_system_items sys,
567 mrp_schedule_designators sched
568 WHERE cst.organization_id = sys.organization_id
569 AND cst.inventory_item_id = sys.inventory_item_id
570 AND ( cst.cost_type_id = arg_cost_type
571 OR
572 (
573 (cst.cost_type_id = arg_def_cost_type)
574 AND
575 (NOT EXISTS
576 (SELECT 'Primary Cost Type Row'
577 FROM cst_item_costs cst1
578 WHERE cst1.inventory_item_id = cst.inventory_item_id
579 AND cst1.organization_id = arg_org_id
580 AND cst1.cost_type_id = arg_cost_type)
581 )
582 )
583 )
584 AND dates.organization_id = sys.organization_id
585 AND dates.inventory_item_id = sys.inventory_item_id
586 AND NVL(dates.rate_end_date, dates.schedule_date)
587 < start_date_this_period
588 AND dates.schedule_level = 3
589 AND dates.schedule_designator = arg_sched_desig
590 AND sched.schedule_designator = arg_sched_desig
591 AND sched.organization_id = arg_org_id
592 AND cal.calendar_date BETWEEN dates.schedule_date
593 AND NVL(dates.rate_end_date, dates.schedule_date)
594 AND cal.calendar_code = arg_calendar_code
595 AND cal.exception_set_id = arg_exception_set_id
596 AND cal.seq_num IS NOT NULL
597 AND sys.compile_designator = arg_compile_desig
598 AND sys.organization_id = arg_org_id;
599 IF SQL%NOTFOUND THEN
600 RAISE select_past_due_mds;
601 END IF;
602 master_schedule := master_schedule + past_due_master_schedule;
603 UPDATE mrp_form_query
604 SET number8 = master_schedule
605 WHERE rowid = my_rowid;
606 IF SQL%NOTFOUND THEN
607 RAISE update_past_due_mds;
608 END IF;
609 /* End of code added for bug fix 399945 */
610 ELSE
611 end_inv_prev_period := end_inv_this_period;
612 period_turns := 0;
613 cum_turns := 0;
614 END IF;
615 /*----------------------------------+
616 | Update cumulative master schedule |
617 +----------------------------------*/
618 cum_master_schedule := cum_master_schedule + master_schedule;
619 /*----------------------------------------------------+
620 | Calculate ending inventory value for current period |
621 +----------------------------------------------------*/
622 end_inv_this_period := ((end_inv_prev_period
623 + purchase_orders
624 + purchase_reqs
625 + planned_orders_buy
626 + discrete_jobs
627 + repetitive_schedules
628 + planned_orders_make)
629 - master_schedule);
630 /*---------------------------------------------+
631 | Calculate inventory turns for current period |
632 +---------------------------------------------*/
633 IF ((end_inv_prev_period + end_inv_this_period)) = 0 THEN
634 period_turns := null;
635 ELSE
636 period_turns := (master_schedule
637 / ((end_inv_prev_period + end_inv_this_period) / 2))
638 * (365 / (start_date_next_period -
639 start_date_this_period));
640 END IF;
641 /*-------------------------------------+
642 | Calculate cumulative inventory turns |
643 +-------------------------------------*/
644 IF ((begin_inv_report + end_inv_this_period) = 0) THEN
645 cum_turns := null;
646 ELSE
647 cum_turns := (cum_master_schedule
648 / ((begin_inv_report + end_inv_this_period) / 2))
649 * (365 / (start_date_next_period -
650 start_date_report));
651 END IF;
652 /*-------------------------------------------------------------+
653 | Write ending inventory, period turns and cumulative turns to |
654 | mrp_form_query |
655 +-------------------------------------------------------------*/
656 UPDATE mrp_form_query q
657 SET number9 = end_inv_this_period,
658 number10 = period_turns,
659 number11 = cum_turns
660 WHERE rowid = my_rowid;
661
662 IF SQL%NOTFOUND THEN
663 RAISE update_inventory_values;
664 END IF;
665 END LOOP;
666 CLOSE inv_values_cur;
667 commit;
668 EXCEPTION
669 WHEN select_on_hand THEN
670 raise_application_error(-20000,
671 'Cannot select on-hand beginning inventory value');
672
673 WHEN select_wip THEN
674 raise_application_error(-20000,
675 'Cannot select WIP beginning inventory value');
676
677 WHEN update_beginning_inv THEN
678 raise_application_error(-20000,
679 'Cannot update beginning inventory value');
680
681 WHEN update_open_purchase_orders THEN
682 raise_application_error(-20000,
683 'Cannot update open purchase order values');
684
685 WHEN update_open_purchase_reqs THEN
686 raise_application_error(-20000,
687 'Cannot update open purchase requisition values');
688
689 WHEN update_mrp_purchase_orders THEN
690 raise_application_error(-20000,
691 'Cannot update MRP planned purchase order values');
692
693 WHEN update_open_discrete_jobs THEN
694 raise_application_error(-20000,
695 'Cannot update open discrete job values');
696
697 WHEN update_mrp_repetitive_scheds THEN
698 raise_application_error(-20000,
699 'Cannot update suggested repetitive schedule values');
700
701 WHEN update_mrp_discrete_jobs THEN
702 raise_application_error(-20000,
703 'Cannot update MRP planned discrete job values');
704
705 WHEN update_master_sched_discrete THEN
706 raise_application_error(-20000,
707 'Cannot update master schedule, discrete item values');
708
709 WHEN update_master_sched_repetitive THEN
710 raise_application_error(-20000,
711 'Cannot update master schedule, discrete item values');
712
713 WHEN select_inventory_values THEN
714 raise_application_error(-20000,
715 'Cannot select inventory values');
716
717 WHEN update_inventory_values THEN
718 raise_application_error(-20000,
719 'Cannot update inventory values');
720
721 WHEN select_past_due_mds THEN
722 raise_application_error(-2000,
723 'Cannot select past due MDS');
724 WHEN update_past_due_mds THEN
725 raise_application_error(-2000,
726 'Cannot update past due MDS');
727
728 END mrp_calculate_inventory_turns;
729 END mrp_report_inv_turns;