[Home] [Help]
PACKAGE BODY: APPS.MRP_EPI
Source
1 PACKAGE BODY mrp_epi AS
2 /* $Header: MRPCINVB.pls 120.3 2005/09/12 08:31:26 gmalhotr noship $ */
3 PROCEDURE inventory_turns(errbuf OUT NOCOPY VARCHAR2, --2663505
4 retcode OUT NOCOPY NUMBER, --2663505
5 p_owning_org_id IN NUMBER,
6 p_designator IN VARCHAR2) IS
7
8 CURSOR ORG_TYPE_C IS
9 SELECT distinct code
10 FROM mrp_srs_org_select_plan_v
11 WHERE designator = p_designator
12 AND ((code=2 AND org_id = p_owning_org_id)
13 OR (code=1 AND planned_org=p_owning_org_id));
14
15 CURSOR PLAN_BOUNDS_C(p_org_type IN NUMBER) IS
16 SELECT trunc(plan.data_start_date), trunc(plan.cutoff_date)
17 FROM mrp_plans plan,
18 mrp_plan_organizations_v org
19 WHERE DECODE(p_org_type, 1, org.planned_organization,
20 org.organization_id) = p_owning_org_id
21 AND org.compile_designator = p_designator
22 AND org.organization_id = plan.organization_id
23 AND org.compile_designator = plan.compile_designator;
24
25 CURSOR PLAN_DATES_C(p_plan_start_date IN DATE, p_plan_end_date IN DATE) IS
26 SELECT DECODE(LEAST(start_date,p_plan_start_date),start_date,
27 p_plan_start_date,start_date) start_date,
28 DECODE(GREATEST(end_date,p_plan_end_date),end_date,
29 p_plan_end_date,end_date) end_date
30 FROM gl_periods cal,
31 org_organization_definitions org,
32 gl_sets_of_books sb
33 WHERE org.set_of_books_id = sb.set_of_books_id
34 AND sb.period_set_name = cal.period_set_name
35 AND sb.accounted_period_type = cal.period_type
36 AND cal.adjustment_period_flag = 'N'
37 AND org.organization_id = p_owning_org_id
38 AND cal.end_date >= p_plan_start_date
39 AND cal.start_date <= p_plan_end_date;
40
41 CURSOR PLAN_ORGS_C(p_org_type IN NUMBER) IS
42 SELECT pln_sched.input_designator_name,
43 org_v.planned_organization
44 FROM mrp_plan_organizations_v org_v,
45 mrp_plan_schedules_v pln_sched
46 WHERE pln_sched.input_organization_id = org_v.planned_organization
47 AND pln_sched.compile_designator = org_v.compile_designator
48 AND DECODE(p_org_type, 1, org_v.planned_organization,
49 org_v.organization_id) = p_owning_org_id
50 AND org_v.compile_designator = p_designator
51 AND pln_sched.input_designator_type = 1;
52
53 l_cursor VARCHAR2(30);
54
55 l_org_type NUMBER;
56 l_plan_start_date DATE;
57 l_plan_end_date DATE;
58 l_old_start_date DATE;
59 l_old_end_date DATE;
60 l_sched_name VARCHAR2(30);
61 l_org_id NUMBER;
62
63 l_count NUMBER := 1;
64
65 BEGIN
66
67 l_cursor := 'ORG_TYPE_C';
68 OPEN ORG_TYPE_C;
69 FETCH ORG_TYPE_C INTO l_org_type;
70 CLOSE ORG_TYPE_C;
71
72 l_cursor := 'PLAN_BOUNDS_C';
73 OPEN PLAN_BOUNDS_C(l_org_type);
74 FETCH PLAN_BOUNDS_C INTO l_plan_start_date, l_plan_end_date;
75 CLOSE PLAN_BOUNDS_C;
76
77 l_cursor := 'DELETE STATEMENTS';
78 DELETE FROM mrp_bis_inv_detail
79 WHERE compile_designator = p_designator;
80
81 DELETE FROM mrp_bis_plan_profit
82 WHERE compile_designator = p_designator;
83
84 DELETE FROM mrp_bis_res_summary
85 WHERE compile_designator = p_designator;
86
87 l_cursor := 'INSERT STATEMENTS';
88 -- Insert a row to store the beginning inventory values
89 INSERT INTO mrp_bis_inv_detail
90 (compile_designator,
91 owning_org_id,
92 organization_id,
93 schedule_designator,
94 detail_date,
95 inventory_item_id,
96 project_id,
97 task_id,
98 mds_quantity,
99 inventory_quantity,
100 snapshot_cost,
101 last_update_date,
102 last_updated_by,
103 creation_date,
104 created_by)
105 SELECT
106 org.compile_designator,
107 org.organization_id,
108 org.planned_organization,
109 pln_sched.input_designator_name,
110 l_plan_start_date - 1,
111 sys.inventory_item_id,
112 NULL,
113 NULL,
114 NVL(past_due_mds(pln_sched.input_designator_name,
115 org.planned_organization,
116 sys.inventory_item_id,
117 l_plan_start_date),0) *
118 NVL(mrp_item_cost(sys.inventory_item_id,
119 sys.organization_id),0),
120 ((NVL(sys.nettable_inventory_quantity,0) +
121 NVL(sys.nonnettable_inventory_quantity,0) +
122 NVL(issued_values(
123 org.compile_designator,
124 org.planned_organization,
125 sys.inventory_item_id),0)) *
126 NVL(mrp_item_cost(sys.inventory_item_id,
127 sys.organization_id),0)) -
128 (NVL(past_due_mds(pln_sched.input_designator_name,
129 org.planned_organization,
130 sys.inventory_item_id,
131 l_plan_start_date),0) *
132 NVL(mrp_item_cost(sys.inventory_item_id,
133 sys.organization_id),0)),
134 NVL(mrp_item_cost(sys.inventory_item_id,
135 sys.organization_id),0),
136 sysdate,
137 1,
138 sysdate,
139 1
140 FROM mrp_plan_schedules_v pln_sched,
141 mrp_system_items sys,
142 mrp_plan_organizations_v org
143 WHERE sys.compile_designator = org.compile_designator
144 AND sys.organization_id = org.planned_organization
145 AND org.planned_organization = pln_sched.input_organization_id(+)
146 AND org.compile_designator = pln_sched.compile_designator(+)
147 AND pln_sched.input_designator_type(+) = 1
148 AND DECODE(l_org_type,1,org.planned_organization,org.organization_id)
149 = p_owning_org_id
150 AND org.compile_designator = p_designator;
151
152 l_old_start_date := l_plan_start_date-1;
153 l_old_end_date := l_plan_start_date-1;
154
155 commit work;
156 FOR plan_dates_rec IN plan_dates_c(l_plan_start_date, l_plan_end_date)
157 LOOP
158 INSERT INTO mrp_bis_inv_detail
159 (compile_designator,
160 owning_org_id,
161 organization_id,
162 schedule_designator,
163 detail_date,
164 inventory_item_id,
165 project_id,
166 task_id,
167 mds_quantity,
168 inventory_quantity,
169 snapshot_cost,
170 last_update_date,
171 last_updated_by,
172 creation_date,
173 created_by)
174 SELECT
175 inv.compile_designator,
176 inv.owning_org_id,
177 inv.organization_id,
178 inv.schedule_designator,
179 plan_dates_rec.start_date,
180 inv.inventory_item_id,
181 inv.project_id,
182 inv.task_id,
183 NVL(SUM(NVL(dates.schedule_quantity,0)*inv.snapshot_cost),0),
184 (NVL(inv.inventory_quantity,0) +
185 NVL(inv_values(inv.compile_designator,
186 inv.organization_id, inv.inventory_item_id,
187 plan_dates_rec.start_date,plan_dates_rec.end_date),0) ),
188 inv.snapshot_cost,
189 sysdate,
190 1,
191 sysdate,
192 1
193 FROM mrp_schedule_dates dates,
194 mrp_system_items sys,
195 mrp_bis_inv_detail inv
196 WHERE dates.rate_end_date IS NULL
197 AND dates.schedule_date(+)
198 between plan_dates_rec.start_date and plan_dates_rec.end_date
199 AND dates.schedule_level(+) = 3
200 AND dates.schedule_designator(+) = inv.schedule_designator
201 AND dates.organization_id(+) = inv.organization_id
202 AND dates.inventory_item_id(+) = inv.inventory_item_id
203 AND sys.inventory_item_id = inv.inventory_item_id
204 AND sys.organization_id = inv.organization_id
205 AND sys.compile_designator = inv.compile_designator
206 AND sys.repetitive_type = 1
207 AND inv.detail_date between l_old_start_date and l_old_end_date
208 AND inv.owning_org_id = p_owning_org_id
209 AND inv.compile_designator = p_designator
210 GROUP BY inv.compile_designator, inv.owning_org_id, inv.organization_id,
211 inv.schedule_designator, inv.detail_date, inv.inventory_item_id,
212 inv.project_id, inv.task_id, inv.snapshot_cost, inv.inventory_quantity
213 UNION
214 SELECT
215 inv.compile_designator,
216 inv.owning_org_id,
217 inv.organization_id,
218 inv.schedule_designator,
219 cal.calendar_date,
220 inv.inventory_item_id,
221 inv.project_id,
222 inv.task_id,
223 NVL(SUM(NVL(dates.schedule_quantity,0)*inv.snapshot_cost),0),
224 (NVL(inv.inventory_quantity,0) +
225 NVL(inv_values(inv.compile_designator,
226 inv.organization_id, inv.inventory_item_id,
227 plan_dates_rec.start_date,plan_dates_rec.end_date),0)),
228 inv.snapshot_cost,
229 sysdate,
230 1,
231 sysdate,
232 1
233 FROM bom_calendar_dates cal,
234 mtl_parameters mtl,
235 mrp_schedule_dates dates,
236 mrp_bis_inv_detail inv
237 WHERE cal.calendar_date BETWEEN dates.schedule_date AND dates.rate_end_date
238 AND dates.rate_end_date is not null
239 AND cal.calendar_date
240 between plan_dates_rec.start_date and plan_dates_rec.end_date
241 AND cal.seq_num IS NOT NULL
242 AND mtl.organization_id = inv.organization_id
243 AND mtl.calendar_exception_set_id = cal.exception_set_id
244 AND mtl.calendar_code = cal.calendar_code
245 AND dates.organization_id = inv.organization_id
246 AND dates.inventory_item_id = inv.inventory_item_id
247 AND dates.schedule_level = 3
248 AND dates.schedule_designator = inv.schedule_designator
249 AND inv.detail_date
250 between l_old_start_date and l_old_end_date
251 AND inv.owning_org_id = p_owning_org_id
252 AND inv.compile_designator = p_designator
253 GROUP BY inv.compile_designator, inv.owning_org_id, inv.organization_id,
254 inv.schedule_designator, cal.calendar_date, inv.inventory_item_id,
255 inv.project_id, inv.task_id, inv.snapshot_cost, inv.inventory_quantity;
256
257 l_old_start_date := plan_dates_rec.start_date;
258 l_old_end_date := plan_dates_rec.end_date;
259
260 commit work;
261 END LOOP;
262 -- ------------------------
263 -- Populate margin table
264 -- ------------------------
265 l_cursor := 'PLAN_ORGS_C';
266 OPEN PLAN_ORGS_C(l_org_type);
267 LOOP
268 FETCH PLAN_ORGS_C into l_sched_name, l_org_id;
269 EXIT WHEN PLAN_ORGS_C%NOTFOUND;
270
271 mrp_calculate_revenue(p_designator, l_sched_name, l_org_id,
272 p_owning_org_id, l_plan_start_date,l_plan_end_date);
273
274 -- ------------------------
275 -- Populate resource table
276 -- ------------------------
277 mrp_resource_util(p_designator, l_org_id, l_plan_start_date,
278 l_plan_end_date);
279
280 END LOOP;
281 CLOSE PLAN_ORGS_C;
282
283 COMMIT WORK;
284
285 retcode := G_SUCCESS;
286
287 EXCEPTION
288 WHEN OTHERS THEN
289 errbuf := 'Error in mrp_epi.inventory_turns function' ||
290 ' Cursor: ' || l_cursor ||
291 ' SQL error: ' || sqlerrm;
292 retcode := G_ERROR;
293
294 END inventory_turns;
295
296 FUNCTION mrp_item_selling_price(arg_item_id in number,
297 arg_org_id in number,
298 arg_price_list_id in number default null,
299 arg_currency in varchar2 default null)
300 RETURN NUMBER
301 IS
302 arg_price number;
303 BEGIN
304 select round(list_price *
305 (1 - (NVL(FND_PROFILE.Value_Specific('MRP_BIS_AV_DISCOUNT'),0)/100)),
306 NVL(-spl.rounding_factor,2))
307 into arg_price
308 from oe_price_list_lines sopl,
309 mtl_system_items msi,
310 oe_price_lists spl
311 where spl.price_list_id = FND_PROFILE.Value_Specific('MRP_BIS_PRICE_LIST')
312 and sopl.price_list_id = spl.price_list_id
313 and sopl.inventory_item_id = arg_item_id
314 and msi.inventory_item_id = arg_item_id
315 and msi.organization_id = arg_org_id
316 and nvl(sopl.unit_code,' ') = nvl(msi.primary_uom_code,' ')
317 and sysdate between nvl(sopl.start_date_active, sysdate-1)
318 and nvl(sopl.end_date_active, sysdate+1)
319 and rownum = 1;
320
321 return arg_price;
322 EXCEPTION
323 WHEN NO_DATA_FOUND THEN
324 return mrp_epi.mrp_item_cost(arg_item_id, arg_org_id);
325 END mrp_item_selling_price;
326
327 -- New function for the APS
328 -- It's a copy from mrp_item_selling_price without the discount.
329 -- We want to make this function callable from a remote database,
330 -- so we don't use the profile option MRP_BIS_PRICE_LIST.
331 FUNCTION mrp_item_list_price(arg_item_id in number,
332 arg_org_id in number,
333 arg_price_list_id in number default null,
334 arg_currency in varchar2 default null)
335 RETURN NUMBER
336 IS
337 arg_price number;
338 BEGIN
339
340 select round(list_price,NVL(spl.rounding_factor,2))
341 into arg_price
342 from oe_price_list_lines sopl,
343 mtl_system_items msi,
344 oe_price_lists spl
345 where spl.price_list_id = arg_price_list_id
346 and sopl.price_list_id = spl.price_list_id
347 and sopl.inventory_item_id = arg_item_id
348 and msi.inventory_item_id = arg_item_id
349 and msi.organization_id = arg_org_id
350 and nvl(sopl.unit_code,' ') = nvl(msi.primary_uom_code,' ')
351 and sysdate between nvl(sopl.start_date_active, sysdate-1)
352 and nvl(sopl.end_date_active, sysdate+1)
353 and rownum = 1;
354
355 return arg_price;
356 EXCEPTION
357 WHEN NO_DATA_FOUND THEN
358 return mrp_epi.mrp_item_cost(arg_item_id, arg_org_id);
359 END mrp_item_list_price;
360
361 FUNCTION mrp_item_cost(p_item_id in number,
362 p_org_id in number)
363 RETURN NUMBER IS
364
365 CURSOR COST_C IS
366 SELECT NVL(cst.item_cost,0)
367 FROM cst_cost_types cct,
368 mtl_parameters mtl,
369 cst_item_costs cst
370 WHERE (cst.cost_type_id = cct.cost_type_id
371 OR (cst.cost_type_id = cct.default_cost_type_id
372 AND (NOT EXISTS (SELECT 'Primary Cost Type Row'
373 FROM cst_item_costs cst1
374 WHERE cst1.inventory_item_id = cst.inventory_item_id
375 AND cst1.organization_id = cst.organization_id
376 AND cst1.cost_type_id = cct.cost_type_id))))
377 AND cct.costing_method_type = mtl.primary_cost_method
378 AND cct.cost_type_id = DECODE(mtl.primary_cost_method,1,1,2,2,1)
379 AND mtl.organization_id = cst.organization_id
380 AND cst.inventory_item_id = p_item_id
381 AND cst.organization_id = p_org_id;
382
383 l_cost NUMBER;
384
385 BEGIN
386
387 OPEN COST_C;
388 FETCH COST_C into l_cost;
389 CLOSE COST_C;
390
391 RETURN(l_cost);
392
393 END mrp_item_cost;
394
395 FUNCTION mrp_resource_cost(p_item_id in number,
396 p_org_id in number)
397 RETURN NUMBER IS
398
399 CURSOR COST_C IS
400 SELECT NVL(cst.tl_resource,0)
401 + NVL(cst.tl_overhead,0)
402 + NVL(cst.tl_material_overhead,0)
403 + NVL(cst.tl_outside_processing,0)
404 FROM cst_cost_types cct,
405 mtl_parameters mtl,
406 cst_item_costs cst
407 WHERE (cst.cost_type_id = cct.cost_type_id
408 OR (cst.cost_type_id = cct.default_cost_type_id
409 AND (NOT EXISTS (SELECT 'Primary Cost Type Row'
410 FROM cst_item_costs cst1
411 WHERE cst1.inventory_item_id = cst.inventory_item_id
412 AND cst1.organization_id = cst.organization_id
413 AND cst1.cost_type_id = cct.cost_type_id))))
414 AND cct.costing_method_type = mtl.primary_cost_method
415 AND cct.cost_type_id = DECODE(mtl.primary_cost_method,1,1,2,2,1)
416 AND mtl.organization_id = cst.organization_id
417 AND cst.inventory_item_id = p_item_id
418 AND cst.organization_id = p_org_id;
419
420 l_cost NUMBER;
421
422 BEGIN
423
424 OPEN COST_C;
425 FETCH COST_C into l_cost;
426 CLOSE COST_C;
427
428 RETURN(l_cost);
429
430 END mrp_resource_cost;
431
432 /*--------------------------------------+
433 | Calculate value of master schedule |
434 +---------------------------------------*/
435 PROCEDURE mrp_calculate_revenue(plan_name in varchar2,
436 sched_name in varchar2,
437 p_org_id in number,
438 p_owning_org_id in number,
439 p_start_date in date,
440 p_complete_date in date)
441 IS
442 revenue number;
443 cost number;
444
445 BEGIN
446
447 /*------------------------------------------------------+
448 | Calculate value of master schedule, discrete items |
449 +------------------------------------------------------*/
450 SELECT NVL(SUM(NVL(dates.schedule_quantity,0) *
451 mrp_item_selling_price(
452 dates.inventory_item_id,
453 dates.organization_id)), 0)
454 / 1000,
455 NVL(SUM(NVL(dates.schedule_quantity,0) *
456 mrp_item_cost(dates.inventory_item_id,
457 dates.organization_id)), 0)
458 / 1000
459 INTO revenue, cost
460 FROM mrp_schedule_dates dates,
461 mrp_system_items sys
462 WHERE dates.organization_id = sys.organization_id
463 AND dates.inventory_item_id = sys.inventory_item_id
464 AND dates.schedule_level = 3
465 AND dates.schedule_designator = sched_name
466 AND sys.repetitive_type = 1
467 AND sys.compile_designator = plan_name
468 AND sys.organization_id = p_org_id;
469
470 /*------------------------------------------------------+
471 | Calculate value of master schedule, repetitive items |
472 +------------------------------------------------------*/
473 SELECT revenue + NVL(SUM(dates.repetitive_daily_rate *
474 mrp_item_selling_price(
475 dates.inventory_item_id,
476 dates.organization_id)), 0)
477 / 1000,
478 cost + NVL(SUM(NVL(dates.repetitive_daily_rate,0) *
479 mrp_item_cost(dates.inventory_item_id,
480 dates.organization_id)), 0)
481 / 1000
482 INTO revenue, cost
483 FROM bom_calendar_dates cal,
484 mrp_schedule_dates dates,
485 mrp_system_items sys,
486 mtl_parameters param
487 WHERE dates.organization_id = sys.organization_id
488 AND dates.inventory_item_id = sys.inventory_item_id
489 AND dates.schedule_level = 3
490 AND dates.schedule_designator = sched_name
491 AND cal.calendar_date between
492 GREATEST(dates.schedule_date,p_start_date)
493 AND LEAST(dates.rate_end_date,p_complete_date -1)
494 AND cal.calendar_code = param.calendar_code
495 AND cal.exception_set_id = param.calendar_exception_set_id
496 AND param.organization_id = sys.organization_id
497 AND sys.repetitive_type = 2
498 AND sys.compile_designator = plan_name
499 AND sys.organization_id = p_org_id;
500
501 INSERT into mrp_bis_plan_profit
502 (compile_designator,
503 organization_id,
504 owning_org_id,
505 schedule_designator,
506 plan_cost,
507 plan_revenue,
508 last_update_date,
509 last_updated_by,
510 creation_date,
511 created_by)
512 VALUES (
513 plan_name,
514 p_org_id,
515 p_owning_org_id,
516 sched_name,
517 cost,
518 revenue,
519 sysdate,
520 1,
521 sysdate,
522 1);
523
524 END mrp_calculate_revenue;
525
526 PROCEDURE mrp_populate_fc_sum(ERRBUF OUT NOCOPY varchar2, --2653505
527 RETCODE OUT NOCOPY number, --2663505
528 p_organization_id number,
529 p_from_forecast varchar2,
530 p_to_forecast varchar2,
531 p_from_date DATE,
532 p_to_date DATE) IS
533 BEGIN
534
535 -- bug2384395
536 -- before delete analyse mrp_bis_forecast
537 BEGIN
538 DBMS_STATS.gather_table_stats('MRP','MRP_BIS_FORECAST_WB', estimate_percent
539 => 10, degree => 2, granularity => 'GLOBAL', cascade =>TRUE);
540 EXCEPTION
541 WHEN OTHERS THEN
542 FND_FILE.put_line(fnd_file.log,'MRP_BIS_FORECAST_WB : '||sqlerrm(sqlcode));
543 END;
544
545 /*----------------------------------------------------------------+
546 | Each run of the concurrent program repopulates the summary |
547 | table so delete the existing records. |
548 +----------------------------------------------------------------*/
549 DELETE FROM mrp_bis_forecast_wb
550 WHERE organization_id = p_organization_id
551 AND forecast_set between p_from_forecast and p_to_forecast
552 AND forecast_date between p_from_date and p_to_date;
553
554 -- bug2384395
555 -- Drop indexes before insertion
556
557 BEGIN
558 ad_ddl.do_ddl( applsys_schema => 'APPLSYS',
559 application_short_name => 'MRP',
560 statement_type => AD_DDL.DROP_INDEX,
561 statement =>
562 'drop index MRP_BIS_FORECAST_WB_N1',
563 object_name => 'MRP_BIS_FORECAST_WB');
564 --
565 EXCEPTION
566 WHEN OTHERS THEN
567 FND_FILE.put_line(fnd_file.log,'Drop Index 1 Error : '||SQLERRM(sqlcode));
568 END;
569 --
570 BEGIN
571 ad_ddl.do_ddl( applsys_schema => 'APPLSYS',
572 application_short_name => 'MRP',
573 statement_type => AD_DDL.DROP_INDEX,
574 statement =>
575 'drop index MRP_BIS_FORECAST_WB_N2',
576 object_name => 'MRP_BIS_FORECAST_WB');
577
578 --
579 EXCEPTION
580 WHEN OTHERS THEN
581 FND_FILE.put_line(fnd_file.log,'Drop Index 2 Error : '||SQLERRM(sqlcode));
582 END;
583
584 /*----------------------------------------------------------------+
585 | Insert forecast records this select statement retrieves the |
586 | all forecast records. If there are orders on the day of the |
587 | forecast then it shows the orders against that forecast |
588 +----------------------------------------------------------------*/
589
590 /* 2169811 - SVAIDYAN : commented the cond. parend_demand_id is not null
591 since this will be null in mtl_demand_omoe */
592
593 INSERT INTO MRP_BIS_FORECAST_WB (
594 organization_id,
595 organization_code,
596 organization_name,
597 forecast_set,
598 forecast,
599 forecast_description,
600 update_type,
601 demand_class,
602 category_set_id,
603 category_set,
604 category_id,
605 category,
606 category_description,
607 product_family_id,
608 product_family_number,
609 product_family_desc,
610 inventory_item_id,
611 item_number,
612 item_description,
613 customer_class,
614 customer_id,
615 customer,
616 ship_id,
617 ship_to_address,
618 ship_to_city,
619 ship_to_state,
620 ship_to_zip,
621 bill_id,
622 bill_to_address,
623 bill_to_city,
624 bill_to_state,
625 bill_to_zip,
626 forecast_date,
627 forecast_quantity,
628 order_quantity,
629 shipped_quantity,
630 forecast_amount,
631 order_amount,
632 shipped_amount,
633 confidence_percentage)
634 SELECT dates.organization_id,
635 org.organization_code,
636 org.organization_name,
637 desig.forecast_set,
638 NULL, -- forecast
639 NULL, -- forecast description
640 desig.update_type,
641 desig.demand_class,
642 scat.category_set_id,
643 scat.category_set_name,
644 cat.category_id,
645 cat.concatenated_segments,
646 vcat.description,
647 DECODE(sys.bom_item_type,5,sys.inventory_item_id,
648 NVL(sys2.inventory_item_id,sys3.inventory_item_id)),
649 DECODE(sys.bom_item_type,5,sys.concatenated_segments,
650 NVL(sys2.concatenated_segments,sys3.concatenated_segments)),
651 DECODE(sys.bom_item_type,5,sys.description,
652 DECODE(sys2.inventory_item_id,NULL,sys3.description,
653 sys2.description)),
654 sys.inventory_item_id,
655 sys.concatenated_segments,
656 sys.description,
657 ar.meaning,
658 desig.customer_id,
659 PART.party_name,
660 desig.ship_id,
661 LOC2.address1,
662 LOC2.city,
663 LOC2.state,
664 LOC2.postal_code,
665 desig.bill_id,
666 LOC1.address1,
667 LOC1.city,
668 LOC1.state,
669 LOC1.postal_code,
670 cal.calendar_date,
671 dates.original_forecast_quantity,
672 sum(md.primary_uom_quantity),
673 sum(md.completed_quantity),
674 dates.original_forecast_quantity *
675 mrp_epi.mrp_item_selling_price(dates.inventory_item_id,
676 dates.organization_id),
677 sum(md.primary_uom_quantity) *
678 mrp_epi.mrp_item_selling_price(dates.inventory_item_id,
679 dates.organization_id),
680 sum(md.completed_quantity) *
681 mrp_epi.mrp_item_selling_price(dates.inventory_item_id,
682 dates.organization_id),
683 avg(dates.confidence_percentage)
684 FROM org_organization_definitions org,
685 fnd_lookup_values ar,
686 --ar_lookups ar, --bug2384395
687 HZ_PARTIES PART, /*bug4434875*/
688 HZ_CUST_ACCOUNTS CA,
689 HZ_CUST_ACCT_SITES_ALL AS1,
690 HZ_CUST_ACCT_SITES_ALL AS2,
691 HZ_PARTY_SITES PS1,
692 HZ_PARTY_SITES PS2,
693 HZ_LOCATIONS LOC1 ,
694 HZ_LOCATIONS LOC2 ,
695 HZ_CUST_SITE_USES_ALL SU1 ,
696 HZ_CUST_SITE_USES_ALL SU2 ,
697 mtl_category_sets scat,
698 mtl_categories_kfv cat,
699 mtl_categories_vl vcat,
700 mtl_item_categories icat,
701 bom_calendar_dates cal,
702 mtl_parameters mp,
703 mtl_system_items_kfv sys3,
704 mtl_system_items_kfv sys2,
705 mtl_system_items_kfv sys,
706 (SELECT inventory_item_id,
707 ship_from_org_id organization_id,
708 schedule_ship_date requirement_date,
709 sold_to_org_id customer_id,
710 demand_class_code demand_class,
711 ship_to_org_id ship_to_site_use_id,
712 invoice_to_org_id bill_to_site_use_id,
713 SUM(DECODE(ool.ordered_quantity,
714 NULL, 0,
715 inv_decimals_pub.get_primary_quantity(ool.ship_from_org_id,
716 ool.inventory_item_id,
717 ool.order_quantity_uom,
718 ool.ordered_quantity))) primary_uom_quantity,
719 SUM(DECODE(OOL.SHIPPED_QUANTITY,
720 NULL, 0,
721 inv_decimals_pub.get_primary_quantity(ool.ship_from_org_id,
722 ool.inventory_item_id,
723 ool.order_quantity_uom,
724 ool.shipped_quantity))) completed_quantity
725 FROM oe_order_lines_all ool
726 WHERE DECODE(ool.source_document_type_id,
727 10, 8, DECODE(ool.line_category_code, 'ORDER', 2, 12)) in (2,8)
728 GROUP BY inventory_item_id,ship_from_org_id,schedule_ship_date,
729 sold_to_org_id,demand_class_code, ship_to_org_id,invoice_to_org_id) md,
730 mrp_forecast_designators desig,
731 ( SELECT
732 forecast_designator,
733 organization_id,
734 sum(original_forecast_quantity) original_forecast_quantity,
735 inventory_item_id,
736 confidence_percentage,
737 bucket_type,
738 forecast_date,
739 rate_end_date,
740 ship_id
741 FROM mrp_forecast_dates
742 GROUP BY forecast_designator, organization_id,
743 inventory_item_id,
744 confidence_percentage,
745 bucket_type,
746 forecast_date,
747 rate_end_date,
748 ship_id) dates
749 WHERE PART.party_id (+) = CA.party_id
750 AND org.organization_id = desig.organization_id
751 AND ar.lookup_type(+) = 'CUSTOMER CLASS'
752 AND ar.lookup_code(+) = CA.customer_class_code
753 AND ar.LANGUAGE(+) = userenv('LANG')
754 and ar.VIEW_APPLICATION_ID(+) = 222
755 and ar.SECURITY_GROUP_ID(+) = fnd_global.lookup_security_group('CUSTOMER CLASS', 222)
756 AND CA.cust_account_id(+) = desig.customer_id
757 AND SU1.cust_acct_site_id = AS1.cust_acct_site_id(+)
758 AND AS1.party_site_id = PS1.party_site_id(+)
759 AND PS1.location_id = LOC1.location_id(+)
760 AND SU1.site_use_code(+) = 'BILL_TO'
761 AND SU1.site_use_id(+) = desig.bill_id
762 AND SU2.cust_acct_site_id = AS2.cust_acct_site_id(+)
763 AND AS2.party_site_id = PS2.party_site_id(+)
764 AND PS2.location_id = LOC2.location_id(+)
765 AND SU2.site_use_code(+) = 'SHIP_TO'
766 AND SU2.site_use_id(+) = desig.ship_id
767 AND cat.category_id = icat.category_id
768 AND vcat.category_id = icat.category_id
769 AND vcat.structure_id=scat.structure_id
770 AND scat.category_set_id = (SELECT category_set_id
771 FROM mtl_default_category_sets
772 WHERE functional_area_id = 1)
773 AND icat.category_set_id = scat.category_set_id
774 AND icat.inventory_item_id = dates.inventory_item_id
775 AND icat.organization_id = dates.organization_id
776 AND ((dates.bucket_type = 1)
777 OR (dates.bucket_type = 2 AND cal.calendar_date IN
778 (SELECT week_start_date
779 FROM bom_cal_week_start_dates
780 WHERE week_start_date BETWEEN dates.forecast_date AND
781 NVL(dates.rate_end_date,dates.forecast_date)
782 AND calendar_code = cal.calendar_code
783 AND exception_set_id = cal.exception_set_id))
784 OR (dates.bucket_type = 3 AND cal.calendar_date IN
785 (SELECT period_start_date
786 FROM bom_period_start_dates
787 WHERE period_start_date BETWEEN dates.forecast_date AND
788 NVL(dates.rate_end_date,dates.forecast_date)
789 AND calendar_code = cal.calendar_code
790 AND exception_set_id = cal.exception_set_id)))
791 AND ((dates.bucket_type = 1 AND cal.calendar_date =
792 md.requirement_date)
793 OR (dates.bucket_type = 2 AND to_char(md.requirement_date,'WWYYYY') =
794 to_char(calendar_date,'WWYYYY'))
795 OR (dates.bucket_type = 3 AND to_char(md.requirement_date,'MMYYYY') =
796 to_char(calendar_date,'MMYYYY')))
797 AND cal.calendar_date BETWEEN dates.forecast_date
798 AND NVL(dates.rate_end_date,dates.forecast_date)
799 AND cal.seq_num IS NOT NULL
800 AND cal.exception_set_id = mp.calendar_exception_set_id
801 AND cal.calendar_code = mp.calendar_code
802 AND mp.organization_id = sys.organization_id
803 AND sys3.organization_id(+) = sys.organization_id
804 AND sys3.inventory_item_id(+) = sys.base_item_id
805 AND sys2.organization_id(+) = sys.organization_id
806 AND sys2.inventory_item_id(+) = sys.product_family_item_id
807 AND sys.organization_id = dates.organization_id
808 AND sys.inventory_item_id = dates.inventory_item_id
809 AND DECODE(desig.update_type,2,
810 NVL(md.ship_to_site_use_id,NVL(dates.ship_id,-1)),-1)
811 = NVL(dates.ship_id,-1)
812 AND DECODE(desig.update_type,3,
813 NVL(md.bill_to_site_use_id,NVL(desig.bill_id,-1)),-1)
814 = NVL(desig.bill_id,-1)
815 AND DECODE(desig.update_type,4,
816 NVL(md.customer_id,NVL(desig.customer_id,-1)),-1)
817 = NVL(desig.customer_id,-1)
818 AND DECODE(desig.demand_class,NULL,NVL(desig.demand_class,'@@@'),
819 NVL(md.demand_class,mp.default_demand_class))
820 = NVL(desig.demand_class,'@@@')
821 AND md.inventory_item_id = dates.inventory_item_id
822 AND md.organization_id = dates.organization_id
823 AND desig.forecast_set IS NOT NULL
824 AND dates.organization_id = desig.organization_id
825 AND dates.forecast_designator = desig.forecast_designator
826 AND desig.organization_id = p_organization_id
827 AND desig.forecast_set between p_from_forecast and p_to_forecast
828 AND dates.forecast_date between p_from_date and p_to_date
829 AND NVL(dates.rate_end_date,p_from_date)
830 between p_from_date and p_to_date
831 AND NVL(desig.disable_date,p_to_date + 1) > p_to_date /*2560013*/
832 GROUP BY dates.organization_id, org.organization_code, org.organization_name,
833 desig.forecast_set, desig.update_type, desig.demand_class,
834 scat.category_set_id, scat.category_set_name, cat.category_id,
835 cat.concatenated_segments, vcat.description, sys.bom_item_type,
836 NVL(sys2.inventory_item_id,sys3.inventory_item_id),
837 NVL(sys2.concatenated_segments,sys3.concatenated_segments),
838 sys2.description, sys3.description, sys.inventory_item_id,
839 sys.concatenated_segments, dates.inventory_item_id,
840 DECODE(sys.bom_item_type,5,sys.inventory_item_id,
841 NVL(sys2.inventory_item_id,sys3.inventory_item_id)),
842 DECODE(sys.bom_item_type,5,sys.concatenated_segments,
843 NVL(sys2.concatenated_segments,sys3.concatenated_segments)),
844 DECODE(sys.bom_item_type,5,sys.description,
845 DECODE(sys2.inventory_item_id,NULL,sys3.description,
846 sys2.description)),
847 sys.description, ar.meaning, desig.customer_id, PART.party_name,
848 desig.ship_id, LOC2.address1, LOC2.city, LOC2.state, LOC2.postal_code,
849 desig.bill_id, LOC1.address1, LOC1.city, LOC1.state, LOC1.postal_code,
850 cal.calendar_date,dates.original_forecast_quantity, dates.original_forecast_quantity *
851 mrp_epi.mrp_item_selling_price(dates.inventory_item_id,
852 dates.organization_id);
853
854 -- bug2384395
855 -- Create Indexes after first insert
856 BEGIN
857 ad_ddl.do_ddl( applsys_schema => 'APPLSYS',
858 application_short_name => 'MRP',
859 statement_type => AD_DDL.CREATE_INDEX,
860 statement =>
861 'create index MRP_BIS_FORECAST_WB_N1'
862 ||' on MRP_BIS_FORECAST_WB '
863 ||'(ORGANIZATION_ID, INVENTORY_ITEM_ID,FORECAST_DATE)'
864 ||' STORAGE (INITIAL 40K NEXT 2520K PCTINCREASE 50) ',
865 object_name =>'MRP_BIS_FORECAST_WB');
866 EXCEPTION
867 WHEN OTHERS THEN
868 FND_FILE.put_line(fnd_file.log,'Create Index 1 Error : '||SQLERRM(sqlcode))
869 ;
870 END;
871
872 BEGIN
873 ad_ddl.do_ddl( applsys_schema => 'APPLSYS',
874 application_short_name => 'MRP',
875 statement_type => AD_DDL.CREATE_INDEX,
876 statement =>
877 'create index MRP_BIS_FORECAST_WB_N2'
878 ||' on MRP_BIS_FORECAST_WB '
879 ||'(ORGANIZATION_NAME, FORECAST_SET)'
880 ||' STORAGE (INITIAL 40K NEXT 3784K PCTINCREASE 50) ',
881 object_name =>'MRP_BIS_FORECAST_WB');
882 EXCEPTION
883 WHEN OTHERS THEN
884 FND_FILE.put_line(fnd_file.log,'Create Index 2 Error : '||SQLERRM(sqlcode));
885 END;
886
887 /*---------------------------------------------------------------------+
888 | Corner-case to catch records that match with order on everything |
889 | except demand_class or customer info. This statement gets the |
890 | forecast records that are excluded because the join to mtl_demand |
891 | is false. |
892 +---------------------------------------------------------------------*/
893
894 INSERT INTO MRP_BIS_FORECAST_WB (
895 organization_id,
896 organization_code,
897 organization_name,
898 forecast_set,
899 forecast,
900 forecast_description,
901 update_type,
902 demand_class,
903 category_set_id,
904 category_set,
905 category_id,
906 category,
907 category_description,
908 product_family_id,
909 product_family_number,
910 product_family_desc,
911 inventory_item_id,
912 item_number,
913 item_description,
914 customer_class,
915 customer_id,
916 customer,
917 ship_id,
918 ship_to_address,
919 ship_to_city,
920 ship_to_state,
921 ship_to_zip,
922 bill_id,
923 bill_to_address,
924 bill_to_city,
925 bill_to_state,
926 bill_to_zip,
927 forecast_date,
928 forecast_quantity,
929 order_quantity,
930 shipped_quantity,
931 forecast_amount,
932 order_amount,
933 shipped_amount,
934 confidence_percentage)
935 SELECT org.organization_id,
936 org.organization_code,
937 org.organization_name,
938 desig.forecast_set,
939 NULL, -- forecast
940 NULL, -- forecast description
941 desig.update_type,
942 desig.demand_class,
943 scat.category_set_id,
944 scat.category_set_name,
945 cat.category_id,
946 cat.concatenated_segments,
947 vcat.description,
948 DECODE(sys.bom_item_type,5,sys.inventory_item_id,
949 NVL(sys2.inventory_item_id,sys3.inventory_item_id)),
950 DECODE(sys.bom_item_type,5,sys.concatenated_segments,
951 NVL(sys2.concatenated_segments,sys3.concatenated_segments)),
952 DECODE(sys.bom_item_type,5,sys.description,
953 DECODE(sys2.inventory_item_id,NULL,sys3.description,
954 sys2.description)),
955 sys.inventory_item_id,
956 sys.concatenated_segments,
957 sys.description,
958 ar.meaning,
959 desig.customer_id,
960 PART.party_name,
961 desig.ship_id,
962 LOC2.address1,
963 LOC2.city,
964 LOC2.state,
965 LOC2.postal_code,
966 desig.bill_id,
967 LOC1.address1,
968 LOC1.city,
969 LOC1.state,
970 LOC1.postal_code,
971 cal.calendar_date,
972 dates.original_forecast_quantity,
973 0,
974 0,
975 dates.original_forecast_quantity *
976 mrp_epi.mrp_item_selling_price(dates.inventory_item_id,
977 dates.organization_id),
978 0,
979 0,
980 dates.confidence_percentage
981 FROM org_organization_definitions org,
982 fnd_lookup_values ar,
983 --ar_lookups ar, --bug2384395
984 HZ_PARTIES PART, /*bug4434875*/
985 HZ_CUST_ACCOUNTS CA,
986 HZ_CUST_ACCT_SITES_ALL AS1,
987 HZ_CUST_ACCT_SITES_ALL AS2,
988 HZ_PARTY_SITES PS1,
989 HZ_PARTY_SITES PS2,
990 HZ_LOCATIONS LOC1 ,
991 HZ_LOCATIONS LOC2 ,
992 HZ_CUST_SITE_USES_ALL SU1 ,
993 HZ_CUST_SITE_USES_ALL SU2 ,
994 mtl_category_sets scat,
995 mtl_categories_kfv cat,
996 mtl_categories_vl vcat,
997 mtl_item_categories icat,
998 bom_calendar_dates cal,
999 mtl_parameters mp,
1000 mtl_system_items_kfv sys3,
1001 mtl_system_items_kfv sys2,
1002 mtl_system_items_kfv sys,
1003 mrp_forecast_designators desig,
1004 mrp_forecast_dates dates
1005 WHERE PART.party_id (+) = CA.party_id
1006 AND org.organization_id = desig.organization_id
1007 AND ar.lookup_type(+) = 'CUSTOMER CLASS'
1008 AND ar.lookup_code(+) = CA.customer_class_code
1009 -- bug2384395
1010 AND ar.LANGUAGE(+) = userenv('LANG')
1011 and ar.VIEW_APPLICATION_ID(+) = 222
1012 and ar.SECURITY_GROUP_ID(+) = fnd_global.lookup_security_group('CUSTOMER CLASS', 222)
1013 AND CA.cust_account_id(+) = desig.customer_id
1014 AND SU1.cust_acct_site_id = AS1.cust_acct_site_id(+)
1015 AND AS1.party_site_id = PS1.party_site_id(+)
1016 AND PS1.location_id = LOC1.location_id(+)
1017 AND SU1.site_use_code(+) = 'BILL_TO'
1018 AND SU1.site_use_id(+) = desig.bill_id
1019 AND SU2.cust_acct_site_id = AS2.cust_acct_site_id(+)
1020 AND AS2.party_site_id = PS2.party_site_id(+)
1021 AND PS2.location_id = LOC2.location_id(+)
1022 AND SU2.site_use_code(+) = 'SHIP_TO'
1023 AND SU2.site_use_id(+) = desig.ship_id
1024 AND cat.category_id = icat.category_id
1025 AND vcat.category_id=icat.category_id
1026 AND vcat.structure_id=scat.structure_id
1027 AND scat.category_set_id = (SELECT category_set_id
1028 FROM mtl_default_category_sets
1029 WHERE functional_area_id = 1)
1030 AND icat.category_set_id = scat.category_set_id
1031 AND icat.inventory_item_id = dates.inventory_item_id
1032 AND icat.organization_id = dates.organization_id
1033 AND sys3.organization_id(+) = sys.organization_id
1034 AND sys3.inventory_item_id(+) = sys.base_item_id
1035 AND sys2.organization_id(+) = sys.organization_id
1036 AND sys2.inventory_item_id(+) = sys.product_family_item_id
1037 AND sys.organization_id = dates.organization_id
1038 AND sys.inventory_item_id = dates.inventory_item_id
1039 AND ((dates.bucket_type = 1)
1040 OR (dates.bucket_type = 2 AND cal.calendar_date IN
1041 (SELECT week_start_date
1042 FROM bom_cal_week_start_dates
1043 WHERE week_start_date BETWEEN dates.forecast_date AND
1044 NVL(dates.rate_end_date,dates.forecast_date)
1045 AND calendar_code = cal.calendar_code
1046 AND exception_set_id = cal.exception_set_id))
1047 OR (dates.bucket_type = 3 AND cal.calendar_date IN
1048 (SELECT period_start_date
1049 FROM bom_period_start_dates
1050 WHERE period_start_date BETWEEN dates.forecast_date AND
1051 NVL(dates.rate_end_date,dates.forecast_date)
1052 AND calendar_code = cal.calendar_code
1053 AND exception_set_id = cal.exception_set_id)))
1054 AND cal.calendar_date BETWEEN dates.forecast_date
1055 AND NVL(dates.rate_end_date,dates.forecast_date)
1056 AND cal.seq_num IS NOT NULL
1057 AND cal.exception_set_id = mp.calendar_exception_set_id
1058 AND cal.calendar_code = mp.calendar_code
1059 AND mp.organization_id = dates.organization_id
1060 AND desig.forecast_set IS NOT NULL
1061 AND dates.organization_id = desig.organization_id
1062 AND dates.forecast_designator = desig.forecast_designator
1063 AND desig.organization_id = p_organization_id
1064 AND desig.forecast_set between p_from_forecast and p_to_forecast
1065 AND dates.forecast_date between p_from_date and p_to_date
1066 AND NVL(dates.rate_end_date,p_from_date)
1067 between p_from_date and p_to_date
1068 AND NVL(desig.disable_date,p_to_date + 1) > p_to_date /*2560013*/
1069 AND NOT EXISTS (SELECT 'x'
1070 FROM mrp_bis_forecast_wb
1071 WHERE inventory_item_id = dates.inventory_item_id
1072 AND organization_id = dates.organization_id
1073 AND forecast_set = desig.forecast_set
1074 AND DECODE(update_type,2, NVL(desig.ship_id,-1),-1)
1075 = DECODE(update_type,2,NVL(ship_id,-1),-1)
1076 AND DECODE(update_type,3, NVL(desig.bill_id,-1),-1)
1077 = DECODE(update_type,3,NVL(bill_id,-1),-1)
1078 AND DECODE(update_type,4, NVL(desig.customer_id,-1),-1)
1079 = DECODE(update_type,4, NVL(customer_id,-1),-1)
1080 AND NVL(demand_class,'@@@') = NVL(desig.demand_class,'@@@')
1081 AND forecast_date = cal.calendar_date);
1082
1083 /*-------------------------------------------------------------------+
1084 | Insert a record for orders that were not forecast |
1085 +-------------------------------------------------------------------*/
1086
1087 /* 2169811 - SVAIDYAN : commented the cond. parend_demand_id is not null
1088 since this will be null in mtl_demand_omoe. Also added the select of
1089 inventory_item_id for wb and the cond.
1090 md.inventory_item_id = wb.inventory_item_id Without this change, it will
1091 show the actual quantity as the sum of actual quantity of all sales orders
1092 for all items in all forecasts against each forecast. */
1093
1094 INSERT INTO MRP_BIS_FORECAST_WB (
1095 organization_id,
1096 organization_code,
1097 organization_name,
1098 forecast_set,
1099 forecast,
1100 forecast_description,
1101 update_type,
1102 demand_class,
1103 category_set_id,
1104 category_set,
1105 category_id,
1106 category,
1107 category_description,
1108 product_family_id,
1109 product_family_number,
1110 product_family_desc,
1111 inventory_item_id,
1112 item_number,
1113 item_description,
1114 customer_class,
1115 customer_id,
1116 customer,
1117 ship_id,
1118 ship_to_address,
1119 ship_to_city,
1120 ship_to_state,
1121 ship_to_zip,
1122 bill_id,
1123 bill_to_address,
1124 bill_to_city,
1125 bill_to_state,
1126 bill_to_zip,
1127 forecast_date,
1128 forecast_quantity,
1129 order_quantity,
1130 shipped_quantity,
1131 forecast_amount,
1132 order_amount,
1133 shipped_amount,
1134 confidence_percentage)
1135 SELECT wb.organization_id,
1136 wb.organization_code,
1137 wb.organization_name,
1138 wb.forecast_set,
1139 NULL, -- forecast
1140 NULL, -- forecast description
1141 wb.update_type,
1142 md.demand_class,
1143 scat.category_set_id,
1144 scat.category_set_name,
1145 cat.category_id,
1146 cat.concatenated_segments,
1147 vcat.description,
1148 DECODE(sys.bom_item_type,5,sys.inventory_item_id,
1149 NVL(sys2.inventory_item_id,sys3.inventory_item_id)),
1150 DECODE(sys.bom_item_type,5,sys.concatenated_segments,
1151 NVL(sys2.concatenated_segments,sys3.concatenated_segments)),
1152 DECODE(sys.bom_item_type,5,sys.description,
1153 DECODE(sys2.inventory_item_id,NULL,sys3.description,
1154 sys2.description)),
1155 sys.inventory_item_id,
1156 sys.concatenated_segments,
1157 sys.description,
1158 ar.meaning,
1159 DECODE(wb.update_type,4,md.customer_id,NULL),
1160 PART.party_name,
1161 DECODE(wb.update_type,4,md.ship_to_site_use_id,2,md.ship_to_site_use_id,NULL),
1162 LOC2.address1,
1163 LOC2.city,
1164 LOC2.state,
1165 LOC2.postal_code,
1166 DECODE(wb.update_type,4,md.bill_to_site_use_id,2,md.bill_to_site_use_id,NULL),
1167 LOC1.address1,
1168 LOC1.city,
1169 LOC1.state,
1170 LOC1.postal_code,
1171 md.requirement_date,
1172 0,
1173 md.primary_uom_quantity,
1174 md.completed_quantity,
1175 0,
1176 md.primary_uom_quantity *
1177 mrp_epi.mrp_item_selling_price(md.inventory_item_id,
1178 md.organization_id),
1179 md.completed_quantity *
1180 mrp_epi.mrp_item_selling_price(md.inventory_item_id,
1181 md.organization_id),
1182 0
1183 FROM
1184 fnd_lookup_values ar,
1185 --ar_lookups ar, --bug2384395
1186 HZ_PARTIES PART,
1187 HZ_CUST_ACCOUNTS CA,
1188 HZ_CUST_ACCT_SITES_ALL AS1,
1189 HZ_CUST_ACCT_SITES_ALL AS2,
1190 HZ_PARTY_SITES PS1,
1191 HZ_PARTY_SITES PS2,
1192 HZ_LOCATIONS LOC1 ,
1193 HZ_LOCATIONS LOC2 ,
1194 HZ_CUST_SITE_USES_ALL SU1 ,
1195 HZ_CUST_SITE_USES_ALL SU2 ,
1196 mtl_category_sets scat,
1197 mtl_categories_kfv cat,
1198 mtl_categories_vl vcat,
1199 mtl_item_categories icat,
1200 mtl_system_items_kfv sys3,
1201 mtl_system_items_kfv sys2,
1202 mtl_system_items_kfv sys,
1203 mtl_parameters param,
1204 (SELECT distinct organization_id, organization_code, organization_name,
1205 update_type, forecast_set, inventory_item_id
1206 FROM mrp_bis_forecast_wb
1207 WHERE organization_id = p_organization_id
1208 AND forecast_set between p_from_forecast and p_to_forecast
1209 AND forecast_date between p_from_date and p_to_date) wb,
1210 mtl_demand_omoe md
1211 WHERE PART.party_id (+)= CA.party_id
1212 AND ar.lookup_type(+) = 'CUSTOMER_CLASS'
1213 AND ar.lookup_code(+) = CA.customer_class_code
1214 -- bug2384395
1215 AND ar.LANGUAGE(+) = userenv('LANG')
1216 and ar.VIEW_APPLICATION_ID(+) = 222
1217 and ar.SECURITY_GROUP_ID(+) = fnd_global.lookup_security_group('CUSTOMER CLASS', 222)
1218 AND CA.cust_account_id(+) = md.customer_id
1219 AND SU1.cust_acct_site_id = AS1.cust_acct_site_id(+)
1220 AND AS1.party_site_id = PS1.party_site_id(+)
1221 AND PS1.location_id = LOC1.location_id(+)
1222 AND SU1.site_use_code(+) = 'BILL_TO'
1223 AND SU1.site_use_id(+) = md.bill_to_site_use_id
1224 AND SU2.cust_acct_site_id = AS2.cust_acct_site_id(+)
1225 AND AS2.party_site_id = PS2.party_site_id(+)
1226 AND PS2.location_id = LOC2.location_id(+)
1227 AND SU2.site_use_code(+) = 'SHIP_TO'
1228 AND SU2.site_use_id(+) = md.ship_to_site_use_id
1229 AND cat.category_id = icat.category_id
1230 AND vcat.category_id = icat.category_id
1231 AND vcat.structure_id=scat.structure_id
1232 AND scat.category_set_id = (SELECT category_set_id
1233 FROM mtl_default_category_sets
1234 WHERE functional_area_id = 1)
1235 AND icat.category_set_id = scat.category_set_id
1236 AND icat.inventory_item_id = md.inventory_item_id
1237 AND icat.organization_id = md.organization_id
1238 AND sys3.organization_id(+) = sys.organization_id
1239 AND sys3.inventory_item_id(+) = sys.base_item_id
1240 AND sys2.organization_id(+) = sys.organization_id
1241 AND sys2.inventory_item_id(+) = sys.product_family_item_id
1242 AND sys.organization_id = md.organization_id
1243 AND sys.inventory_item_id = md.inventory_item_id
1244 AND param.organization_id = md.organization_id
1245 AND md.reservation_type = 1
1246 AND md.demand_source_type in (2,8)
1247 AND md.primary_uom_quantity <> 0
1248 AND md.organization_id = wb.organization_id
1249 AND md.inventory_item_id = wb.inventory_item_id
1250 AND md.requirement_date between p_from_date and p_to_date
1251 AND NOT EXISTS (SELECT 'x'
1252 FROM mrp_bis_forecast_wb
1253 WHERE inventory_item_id = md.inventory_item_id
1254 AND organization_id = md.organization_id
1255 AND forecast_set = wb.forecast_set
1256 AND organization_id = wb.organization_id
1257 AND DECODE(update_type,2,
1258 NVL(md.ship_to_site_use_id,NVL(ship_id,-1)),-1)
1259 = NVL(ship_id,-1)
1260 AND DECODE(update_type,3,
1261 NVL(md.bill_to_site_use_id,NVL(bill_id,-1)),-1)
1262 = NVL(bill_id,-1)
1263 AND DECODE(update_type,4,
1264 NVL(md.customer_id,NVL(customer_id,-1)),-1)
1265 = NVL(customer_id,-1)
1266 AND DECODE(demand_class,NULL,NVL(demand_class,'@@@'),
1267 NVL(md.demand_class,param.default_demand_class))
1268 = NVL(demand_class,'@@@')
1269 AND forecast_date = md.requirement_date);
1270
1271 COMMIT WORK;
1272
1273 RETCODE := G_SUCCESS;
1274
1275 EXCEPTION
1276 WHEN OTHERS THEN
1277 ROLLBACK;
1278 ERRBUF := 'Error: unable to update forecast workbook summary table' ||
1279 ' SQL error: ' || sqlerrm;
1280 RETCODE := G_ERROR;
1281
1282 END mrp_populate_fc_sum;
1283
1284 FUNCTION past_due_mds(p_designator IN VARCHAR2, p_org_id IN NUMBER,
1285 p_item_id IN NUMBER, p_date IN DATE) RETURN NUMBER IS
1286
1287 CURSOR MDS_C IS
1288 SELECT SUM(NVL(schedule_quantity,0))
1289 FROM mrp_schedule_dates
1290 WHERE schedule_designator = p_designator
1291 AND schedule_level = 3
1292 AND organization_id = p_org_id
1293 AND inventory_item_id = p_item_id
1294 AND schedule_date < p_date;
1295
1296 l_mds NUMBER;
1297
1298 BEGIN
1299
1300 OPEN MDS_C;
1301 FETCH MDS_C into l_mds;
1302 CLOSE MDS_C;
1303
1304 RETURN l_mds;
1305
1306 END past_due_mds;
1307
1308 FUNCTION issued_values(p_designator IN VARCHAR2, p_org_id IN NUMBER,
1309 p_item_id IN NUMBER) RETURN NUMBER IS
1310
1311 CURSOR ISSUED_VALUES_C IS
1312 SELECT NVL(SUM(NVL(wip.quantity_issued,0)),0)
1313 FROM mrp_wip_components wip
1314 WHERE wip.compile_designator = p_designator
1315 AND wip.organization_id = p_org_id
1316 AND wip.wip_entity_type in (1,3)
1317 AND DECODE(wip.wip_entity_type,1,1,wip.supply_demand_type) =
1318 DECODE(wip.wip_entity_type,1,1,1)
1319 AND wip.inventory_item_id = p_item_id;
1320
1321 l_issued NUMBER;
1322
1323 BEGIN
1324
1325 OPEN ISSUED_VALUES_C;
1326 FETCH ISSUED_VALUES_C INTO l_issued;
1327 CLOSE ISSUED_VALUES_C;
1328
1329 RETURN l_issued;
1330
1331 END issued_values;
1332
1333 FUNCTION inv_values(p_designator IN VARCHAR2, p_org_id IN NUMBER,
1334 p_item_id IN NUMBER, p_start_date IN DATE,
1335 p_end_date IN DATE) RETURN NUMBER IS
1336
1337 CURSOR INV_VALUES_C1 IS
1338 SELECT NVL(SUM(NVL(rec.new_order_quantity,0)),0) *
1339 mrp_item_cost(rec.inventory_item_id, rec.organization_id)
1340 FROM mrp_system_items msi,
1341 mrp_recommendations rec
1342 WHERE rec.disposition_status_type = 1
1343 AND TRUNC(rec.new_schedule_date) between p_start_date and p_end_date
1344 AND rec.order_type in (1,2,5,8)
1345 AND msi.planning_make_buy_code = 2
1346 AND msi.organization_id = rec.organization_id
1347 AND msi.inventory_item_id = rec.inventory_item_id
1348 AND msi.compile_designator = rec.compile_designator
1349 AND rec.compile_designator = p_designator
1350 AND rec.organization_id = p_org_id
1351 AND rec.inventory_item_id = p_item_id
1352 GROUP BY rec.inventory_item_id, rec.organization_id;
1353
1354 /** Bug 2416975 : Replaced the function mrp_resource_cost with
1355 mrp_item_cost **/
1356 CURSOR INV_VALUES_C2 IS
1357 SELECT NVL(SUM(DECODE(order_type,4,NVL(rec.daily_rate,0),
1358 NVL(rec.new_order_quantity,0))),0) *
1359 mrp_item_cost(rec.inventory_item_id, rec.organization_id)
1360 FROM bom_calendar_dates cal,
1361 mtl_parameters mtl,
1362 mrp_system_items msi,
1363 mrp_recommendations rec
1364 WHERE rec.disposition_status_type = 1
1365 AND ((calendar_date = TRUNC(rec.new_wip_start_date)
1366 AND rec.order_type in (3,27))
1367 OR (calendar_date BETWEEN TRUNC(rec.first_unit_start_date)
1368 AND TRUNC(rec.last_unit_start_date)
1369 AND rec.order_type = 4)
1370 OR (calendar_date = TRUNC(rec.new_schedule_date)
1371 AND rec.order_type = 5))
1372 AND cal.calendar_date between p_start_date and p_end_date
1373 AND cal.calendar_code = mtl.calendar_code
1374 AND cal.exception_set_id = mtl.calendar_exception_set_id
1375 AND mtl.organization_id = rec.organization_id
1376 AND msi.planning_make_buy_code = 1
1377 AND msi.organization_id = rec.organization_id
1378 AND msi.inventory_item_id = rec.inventory_item_id
1379 AND msi.compile_designator = rec.compile_designator
1380 AND rec.compile_designator = p_designator
1381 AND rec.organization_id = p_org_id
1382 AND rec.inventory_item_id = p_item_id
1383 GROUP BY rec.inventory_item_id, rec.organization_id;
1384
1385 /** Bug 2756660 **/
1386 CURSOR INV_VALUES_C3 IS
1387 SELECT NVL(SUM(NVL(req.using_requirements_quantity,0)),0) *
1388 mrp_item_cost(req.inventory_item_id, req.organization_id)
1389 FROM mrp_gross_requirements req
1390 WHERE TRUNC(req.using_assembly_demand_date) between p_start_date and p_end_date
1391 AND req.compile_designator = p_designator
1392 AND req.organization_id = p_org_id
1393 AND req.inventory_item_id = p_item_id
1394 GROUP BY req.inventory_item_id, req.organization_id;
1395
1396 l_inv_value1 NUMBER;
1397 l_inv_value2 NUMBER;
1398 l_inv_value3 NUMBER;
1399
1400 BEGIN
1401
1402 OPEN INV_VALUES_C1;
1403 FETCH INV_VALUES_C1 INTO l_inv_value1;
1404 CLOSE INV_VALUES_C1;
1405
1406 OPEN INV_VALUES_C2;
1407 FETCH INV_VALUES_C2 INTO l_inv_value2;
1408 CLOSE INV_VALUES_C2;
1409
1410 OPEN INV_VALUES_C3;
1411 FETCH INV_VALUES_C3 INTO l_inv_value3;
1412 CLOSE INV_VALUES_C3;
1413
1414 RETURN (NVL(l_inv_value1,0) + NVL(l_inv_value2,0)) - NVL(l_inv_value3,0);
1415
1416 EXCEPTION
1417
1418 WHEN OTHERS THEN
1419
1420 RETURN 0;
1421
1422 END inv_values;
1423
1424 PROCEDURE mrp_resource_util(p_designator varchar2,
1425 p_org_id number,
1426 p_start_date date,
1427 p_end_date date) IS
1428
1429 l_query_id NUMBER;
1430 BEGIN
1431
1432 -- ---------------------------------------------
1433 -- Use mrp_form_query to gather resource data to
1434 -- be summarized
1435 -- ---------------------------------------------
1436 SELECT mrp_form_query_s.nextval
1437 INTO l_query_id
1438 FROM dual;
1439
1440 INSERT INTO mrp_form_query
1441 (query_id,
1442 number1,
1443 char1,
1444 number2,
1445 number3,
1446 number4,
1447 date1,
1448 number5,
1449 number6,
1450 last_update_date,
1451 last_updated_by,
1452 creation_date,
1453 created_by)
1454 SELECT l_query_id,
1455 avail.organization_id,
1456 avail.compile_designator,
1457 avail.department_id,
1458 avail.resource_id,
1459 avail.line_id,
1460 cal.calendar_date,
1461 0,
1462 avail.total_availability,
1463 sysdate,
1464 1,
1465 sysdate,
1466 1
1467 FROM bom_calendar_dates cal,
1468 mtl_parameters param,
1469 crp_available_resources_v avail
1470 WHERE cal.calendar_date BETWEEN avail.resource_start_date
1471 AND NVL(avail.resource_end_date,avail.resource_start_date)
1472 AND cal.calendar_date BETWEEN p_start_date AND p_end_date
1473 AND cal.calendar_code = param.calendar_code
1474 AND cal.exception_set_id = param.calendar_exception_set_id
1475 AND cal.seq_num IS NOT NULL
1476 AND param.organization_id = avail.organization_id
1477 AND avail.compile_designator = p_designator
1478 AND avail.organization_id = p_org_id
1479 UNION ALL
1480 SELECT l_query_id,
1481 req.organization_id,
1482 req.compile_designator,
1483 DECODE(req.resource_id,-1,to_number(NULL),req.department_id),
1484 req.resource_id,
1485 DECODE(req.resource_id,-1,req.department_id,to_number(NULL)),
1486 cal.calendar_date,
1487 req.resource_hours,
1488 0,
1489 sysdate,
1490 1,
1491 sysdate,
1492 1
1493 FROM bom_calendar_dates cal,
1494 mtl_parameters param,
1495 crp_resource_requirements_v req
1496 WHERE cal.calendar_date BETWEEN req.resource_date
1497 AND NVL(req.resource_end_date,req.resource_date)
1498 AND cal.calendar_date BETWEEN p_start_date AND p_end_date
1499 AND cal.calendar_code = param.calendar_code
1500 AND cal.exception_set_id = param.calendar_exception_set_id
1501 AND cal.seq_num IS NOT NULL
1502 AND param.organization_id = req.organization_id
1503 AND req.compile_designator = p_designator
1504 AND req.organization_id = p_org_id;
1505
1506 -- ------------------------------------------------
1507 -- Insert summary records into mrp_bis_res_summary
1508 -- ------------------------------------------------
1509 INSERT INTO mrp_bis_res_summary
1510 (organization_id,
1511 compile_designator,
1512 department_id,
1513 resource_id,
1514 line_id,
1515 resource_date,
1516 required_hours,
1517 available_hours,
1518 utilization,
1519 last_update_date,
1520 last_updated_by,
1521 creation_date,
1522 created_by)
1523 SELECT
1524 number1,
1525 char1,
1526 number2,
1527 number3,
1528 number4,
1529 date1,
1530 sum(number5),
1531 sum(number6),
1532 decode(nvl(sum(number6),0),0,0,sum(number5)/sum(number6)),
1533 sysdate,
1534 1,
1535 sysdate,
1536 1
1537 FROM mrp_form_query
1538 WHERE query_id = l_query_id
1539 GROUP BY number1, char1, number2, number3, number4, date1;
1540
1541 -- -----------------------------------
1542 -- Calculate the resource utilization
1543 -- If available hours is zero, then
1544 -- utilization already shows as zero
1545 -- -----------------------------------
1546 /***
1547 UPDATE mrp_bis_res_summary
1548 SET utilization = required_hours/available_hours
1549 WHERE available_hours <> 0;
1550 ***/
1551
1552 delete from MRP_FORM_QUERY where query_id = l_query_id;
1553
1554 END mrp_resource_util;
1555
1556 END mrp_epi;