[Home] [Help]
PACKAGE BODY: APPS.MSC_HORIZONTAL_PLAN_SC
Source
1 PACKAGE BODY MSC_HORIZONTAL_PLAN_SC AS
2 /* $Header: MSCPHOPB.pls 120.56.12010000.2 2008/11/10 19:15:34 eychen ship $ */
3
4 SYS_YES CONSTANT INTEGER := 1;
5 SYS_NO CONSTANT INTEGER := 2;
6
7 /* plan types */
8 SRO_PLAN CONSTANT INTEGER := 4;
9
10
11 PURCHASE_ORDER CONSTANT INTEGER := 1; /* order type lookup */
12 PURCH_REQ CONSTANT INTEGER := 2;
13 WORK_ORDER CONSTANT INTEGER := 3;
14 AGG_REP_SCHEDULE CONSTANT INTEGER := 4;
15 PLANNED_ORDER CONSTANT INTEGER := 5;
16 MATERIAL_TRANSFER CONSTANT INTEGER := 6;
17 NONSTD_JOB CONSTANT INTEGER := 7;
18 RECEIPT_PURCH_ORDER CONSTANT INTEGER := 8;
19 REQUIREMENT CONSTANT INTEGER := 9;
20 FPO_SUPPLY CONSTANT INTEGER := 10;
21 SHIPMENT CONSTANT INTEGER := 11;
22 RECEIPT_SHIPMENT CONSTANT INTEGER := 12;
23 REPETITIVE_SCHEDULE CONSTANT INTEGER := 13;
24 DIS_JOB_BY CONSTANT INTEGER := 14;
25 NON_ST_JOB_BY CONSTANT INTEGER := 15;
26 REP_SCHED_BY CONSTANT INTEGER := 16;
27 PLANNED_BY CONSTANT INTEGER := 17;
28 ON_HAND_QTY CONSTANT INTEGER := 18;
29 FLOW_SCHED CONSTANT INTEGER := 27;
30 FLOW_SCHED_BY CONSTANT INTEGER := 28;
31 PAYBACK_SUPPLY CONSTANT INTEGER := 29;
32 -- Returns project is out
33 --RETURNS CONSTANT INTEGER := 32;
34
35 SALES CONSTANT INTEGER := 10; /* horizontal plan type lookup */
36 FORECAST CONSTANT INTEGER := 20;
37 PROD_FORECAST CONSTANT INTEGER := 25;
38 DEPENDENT CONSTANT INTEGER := 30;
39 SCRAP CONSTANT INTEGER := 40;
40 PB_DEMAND CONSTANT INTEGER := 45;
41 OTHER CONSTANT INTEGER := 50;
42 GROSS CONSTANT INTEGER := 70;
43 WIP CONSTANT INTEGER := 81;
44 FLOW_SCHEDULE CONSTANT INTEGER := 82;
45 PO CONSTANT INTEGER := 83;
46 REQ CONSTANT INTEGER := 85;
47 TRANSIT CONSTANT INTEGER := 87;
48 RECEIVING CONSTANT INTEGER := 89;
49 PLANNED CONSTANT INTEGER := 90;
50 PB_SUPPLY CONSTANT INTEGER := 95;
51 -- Returns Project is Out
52 --RETURN_SUP CONSTANT INTEGER := 97;
53 SUPPLY CONSTANT INTEGER := 100;
54 ON_HAND CONSTANT INTEGER := 105;
55 PAB CONSTANT INTEGER := 110;
56 SS CONSTANT INTEGER := 120;
57 SS_UNC CONSTANT INTEGER := 125;
58 ATP CONSTANT INTEGER := 130;
59 CURRENT_S CONSTANT INTEGER := 140;
60 POH CONSTANT INTEGER := 150;
61 EXP_LOT CONSTANT INTEGER := 160;
62 SS_DOS CONSTANT INTEGER := 180;
63 SS_VAL CONSTANT INTEGER := 190;
64 SSunc_DOS CONSTANT INTEGER := 210;
65 SSunc_VAL CONSTANT INTEGER := 220;
66 USS CONSTANT INTEGER := 230;
67 USS_DOS CONSTANT INTEGER := 240;
68 USS_VAL CONSTANT INTEGER := 250;
69 min_inv_lvl CONSTANT INTEGER := 175;
70 max_inv_lvl CONSTANT INTEGER := 177;
71 TARGET_SER_LVL CONSTANT INTEGER := 270;
72 ACHIEVED_SER_LVL CONSTANT INTEGER := 280;
73 NON_POOL_SS CONSTANT INTEGER := 178;
74 MANU_VARI CONSTANT INTEGER := 183;
75 MAD1 CONSTANT INTEGER := 290;
76
77
78 SALES_OFF CONSTANT INTEGER := 0; /* offsets */
79 FORECAST_OFF CONSTANT INTEGER := 1;
80 PROD_FORECAST_OFF CONSTANT INTEGER := 2; -- Prod Fcst moved up
81 DEPENDENT_OFF CONSTANT INTEGER := 3;
82 SCRAP_OFF CONSTANT INTEGER := 4;
83 PB_DEMAND_OFF CONSTANT INTEGER := 5;
84 OTHER_OFF CONSTANT INTEGER := 6;
85 GROSS_OFF CONSTANT INTEGER := 7;
86 WIP_OFF CONSTANT INTEGER := 8;
87 PO_OFF CONSTANT INTEGER := 9;
88 REQ_OFF CONSTANT INTEGER := 10;
89 TRANSIT_OFF CONSTANT INTEGER := 11;
90 RECEIVING_OFF CONSTANT INTEGER := 12;
91 PLANNED_OFF CONSTANT INTEGER := 13;
92 PB_SUPPLY_OFF CONSTANT INTEGER := 14;
93 --Returns Project is Out
94 --RETURNS_OFF CONSTANT INTEGER := 15; -- New row Returns
95 SUPPLY_OFF CONSTANT INTEGER := 15;
96 ON_HAND_OFF CONSTANT INTEGER := 16;
97 PAB_OFF CONSTANT INTEGER := 17;
98 SS_OFF CONSTANT INTEGER := 18;
99 ATP_OFF CONSTANT INTEGER := 19;
100 CURRENT_S_OFF CONSTANT INTEGER := 20;
101 POH_OFF CONSTANT INTEGER := 21;
102 EXP_LOT_OFF CONSTANT INTEGER := 22;
103 SSUNC_OFF CONSTANT INTEGER := 23;
104 min_inv_lvl_off CONSTANT INTEGER := 24;
105 max_inv_lvl_off CONSTANT INTEGER := 25;
106 SS_DOS_OFF CONSTANT INTEGER := 26;
107 SS_VAL_OFF CONSTANT INTEGER := 27;
108 SSUNC_DOS_OFF CONSTANT INTEGER := 28;
109 SSUNC_VAL_OFF CONSTANT INTEGER := 29;
110 USS_OFF CONSTANT INTEGER := 30;
111 USS_DOS_OFF CONSTANT INTEGER := 31;
112 USS_VAL_OFF CONSTANT INTEGER := 32;
113 TARGET_SER_OFF CONSTANT INTEGER := 33;
114 ACHIEVED_SER_OFF CONSTANT INTEGER := 34;
115 NON_POOL_SS_OFF CONSTANT INTEGER := 35;
116 MANF_VARI_OFF CONSTANT INTEGER := 36;
117 PURC_VARI_OFF CONSTANT INTEGER := 37;
118 TRAN_VARI_OFF CONSTANT INTEGER := 38;
119 DMND_VARI_OFF CONSTANT INTEGER := 39;
120 MAD_OFF CONSTANT INTEGER := 40;
121 MAPE_OFF CONSTANT INTEGER := 41;
122
123 NUM_OF_TYPES CONSTANT INTEGER := 42;
124
125 /* WIP job status lookups */
126 JOB_UNRELEASED CONSTANT INTEGER := 1;
127 JOB_RELEASED CONSTANT INTEGER := 3;
128 JOB_COMPLETE CONSTANT INTEGER := 4;
129 JOB_HOLD CONSTANT INTEGER := 6;
130
131 /* Schedule Level */
132 UPDATED_SCHEDULE CONSTANT INTEGER := 2;
133
134 /* Schedule supply demand */
135 SCHEDULE_DEMAND CONSTANT INTEGER := 1;
136 SCHEDULE_SUPPLY CONSTANT INTEGER := 2;
137
138 /* Independent demand types for Current Data */
139 IDT_SCHEDULE CONSTANT INTEGER := 1;
140 IDT_FORECAST CONSTANT INTEGER := 2;
141
142 /* forecast buckets */
143 DAILY_BUCKET CONSTANT INTEGER := 1;
144 WEEKLY_BUCKET CONSTANT INTEGER := 2;
145 MONTHLY_BUCKET CONSTANT INTEGER := 3;
146
147 /* MRP demand types */
148 DEMAND_PLANNED_ORDER CONSTANT INTEGER := 1;
149 DEMAND_OPTIONAL CONSTANT INTEGER := 22;
150 DEMAND_PAYBACK CONSTANT INTEGER := 27;
151
152 /* Rounding control */
153 DO_ROUND CONSTANT INTEGER := 1;
154 DO_NOT_ROUND CONSTANT INTEGER := 2;
155
156 /* safety stock code */
157 NON_MRP_PCT CONSTANT INTEGER := 1;
158
159 /* lot control */
160 FULL_LOT_CONTROL CONSTANT INTEGER := 2;
161
162 /* sub inventory type */
163 NETTABLE CONSTANT INTEGER := 1;
164
165 /*MTL_DEMAND types */
166 SALES_ORDER CONSTANT INTEGER := 2;
167
168 /* input designator type */
169 MDS_DESIGNATOR_TYPE CONSTANT INTEGER := 1;
170
171 /* global variable for number of buckets to display for the plan */
172 g_num_of_buckets NUMBER;
173
174 g_error_stmt VARCHAR2(200);
175
176 g_use_sup_req number :=0;
177
178 NODE_REGULAR_ITEM CONSTANT NUMBER :=0;
179 NODE_ITEM_SUPPLIER CONSTANT NUMBER := 1;
180 NODE_DEPT_RES CONSTANT NUMBER := 2;
181 NODE_LINE CONSTANT NUMBER := 3;
182 NODE_TRANS_RES CONSTANT NUMBER := 4;
183 NODE_PF_ITEM CONSTANT NUMBER := 5;
184 NODE_GL_FORECAST_ITEM CONSTANT NUMBER := 6;
185 NODE_RES_INSTANCE CONSTANT NUMBER := 7;
186
187 TYPE column_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
188 -- ---------------------------------------------------------
189 -- compute_daily_rate_t is only used by the current_activity
190 -- cursor so it maintains the joins to BOM tables instead
191 -- of being converted to MSC.
192 -- ---------------------------------------------------------
193 function compute_daily_rate_t
194 (var_calendar_code varchar2,
195 var_exception_set_id number,
196 var_daily_production_rate number,
197 var_quantity_completed number,
198 fucd date,
199 var_date date) return number is
200 var_tot_completed number ;
201 var_diff number ;
202 var_change number ;
203 var_prior_completed number ;
204 var_ret_val number ;
205 var_fucd_seq number ;
206 var_lucd_seq number ;
207 Begin
208 select cal.prior_seq_num
209 into var_fucd_seq
210 FROM bom_calendar_dates cal
211 WHERE cal.exception_set_id = var_exception_set_id
212 AND cal.calendar_code = var_calendar_code
213 AND cal.calendar_date = TRUNC(fucd) ;
214
215 select cal.prior_seq_num
216 into var_lucd_seq
217 FROM bom_calendar_dates cal
218 WHERE cal.exception_set_id = var_exception_set_id
219 AND cal.calendar_code = var_calendar_code
220 AND cal.calendar_date = TRUNC(var_date) ;
221
222 var_diff := ABS(var_fucd_seq - var_lucd_seq) + 1 ;
223
224 var_tot_completed := var_diff * var_daily_production_rate ;
225
226 if (var_tot_completed <= var_quantity_completed)
227 then
228 var_ret_val := 0;
229 return(var_ret_val);
230 end if;
231
232 var_prior_completed := var_daily_production_rate * (var_diff -1) ;
233
234 var_change := var_quantity_completed - var_prior_completed ;
235
236 if (var_change > 0 )
237 then
238 var_ret_val := var_daily_production_rate - var_change ;
239 else
240 var_ret_val := var_daily_production_rate ;
241 end if;
242
243 return(var_ret_val);
244 end ;
245
246
247 Procedure populate_horizontal_plan (p_agg_hzp IN NUMBER, -- can be removed
248 item_list_id IN NUMBER,
249 arg_query_id IN NUMBER,
250 arg_plan_id IN NUMBER,
251 arg_plan_organization_id IN NUMBER,
252 arg_plan_instance_id IN NUMBER,
253 arg_bucket_type IN NUMBER,
254 arg_cutoff_date IN DATE,
255 arg_current_data IN NUMBER DEFAULT 2,
256 arg_ind_demand_type IN NUMBER DEFAULT NULL,
257 arg_source_list_name IN VARCHAR2 DEFAULT NULL,
258 enterprize_view IN BOOLEAN,
259 arg_res_level IN NUMBER DEFAULT 1,
260 arg_resval1 IN VARCHAR2 DEFAULT NULL,
261 arg_resval2 IN NUMBER DEFAULT NULL,
262 arg_category_name IN VARCHAR2 DEFAULT NULL, -- can be remove
263 arg_ep_view_also IN BOOLEAN DEFAULT FALSE) IS
264
265 -- -------------------------------------------------
266 -- This cursor select number of buckets in the plan.
267 -- -------------------------------------------------
268 CURSOR plan_buckets IS
269 SELECT DECODE(arg_plan_id, -1, sysdate, trunc(curr_start_date)),
270 DECODE(arg_plan_id, -1, sysdate+365, trunc(curr_cutoff_date))
271 FROM msc_plans
272 WHERE plan_id = arg_plan_id;
273
274 -- -------------------------------------------------
275 -- This cursor selects the dates for the buckets.
276 -- -------------------------------------------------
277 CURSOR bucket_dates(p_start_date DATE, p_end_date DATE) IS
278 SELECT cal.calendar_date
279 FROM msc_calendar_dates cal,
280 msc_trading_partners tp
281 WHERE tp.sr_tp_id = arg_plan_organization_id
282 AND tp.sr_instance_id = arg_plan_instance_id
283 AND tp.calendar_exception_set_id = cal.exception_set_id
284 AND tp.partner_type = 3
285 AND tp.calendar_code = cal.calendar_code
286 AND tp.sr_instance_id = cal.sr_instance_id
287 AND cal.calendar_date BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
288 ORDER BY cal.calendar_date;
289
290 cursor c_first_date(p_st_date DATE) is
291 select period_start_date
292 from msc_analysis_aggregate
293 where plan_id = arg_plan_id
294 and record_type = 3
295 and period_type = 1
296 and period_start_date <= p_st_date
297 order by period_start_date desc;
298
299 l_plan_start_date DATE;
300 l_plan_end_date DATE;
301 l_first_date DATE;
302
303 l_bucket_number NUMBER := 0;
304 l_bucket_date DATE;
305
306 last_date DATE;
307 sid NUMBER;
308 l_plan_type NUMBER := 1;
309
310 -- --------------------------------------------
311 -- This cursor selects the snapshot activity in
312 -- MSC_DEMANDS and MSC_SUPPLIES
313 -- for the items per organizatio for a plan..
314 -- --------------------------------------------
315 CURSOR mrp_snapshot_activity IS
316 SELECT /*+ INDEX(rec, MSC_SUPPLIES_N1) */
317 list.number5 item_id,
318 list.number6 org_id,
319 list.number3 inst_id,
320 DECODE(rec.order_type,
321 PURCHASE_ORDER, PO,
322 PURCH_REQ, REQ,
323 WORK_ORDER, WIP,
324 FLOW_SCHED, WIP,
325 REPETITIVE_SCHEDULE,PLANNED,
326 PLANNED_ORDER, PLANNED,
327 NONSTD_JOB, WIP,
328 RECEIPT_PURCH_ORDER,RECEIVING,
329 SHIPMENT, TRANSIT,
330 RECEIPT_SHIPMENT, RECEIVING,
331 PAYBACK_SUPPLY, PB_SUPPLY,
332 ON_HAND_QTY, ON_HAND,
333 AGG_REP_SCHEDULE, CURRENT_S,
334 -- RETURNS, RETURN_SUP,
335 PLANNED) row_type,
336 DECODE(rec.order_type,
337 PURCHASE_ORDER, PO_OFF,
338 PURCH_REQ, REQ_OFF,
339 WORK_ORDER, WIP_OFF,
340 FLOW_SCHED, WIP_OFF,
341 REPETITIVE_SCHEDULE,PLANNED_OFF,
342 PLANNED_ORDER, PLANNED_OFF,
343 NONSTD_JOB, WIP_OFF,
344 RECEIPT_PURCH_ORDER,RECEIVING_OFF,
345 SHIPMENT, TRANSIT_OFF,
346 RECEIPT_SHIPMENT, RECEIVING_OFF,
347 DIS_JOB_BY, WIP_OFF,
348 NON_ST_JOB_BY, WIP_OFF,
349 REP_SCHED_BY, PLANNED_OFF,
350 PLANNED_BY, PLANNED_OFF,
351 FLOW_SCHED_BY, WIP_OFF,
352 PAYBACK_SUPPLY, PB_SUPPLY_OFF,
353 ON_HAND_QTY, ON_HAND_OFF,
354 AGG_REP_SCHEDULE, CURRENT_S_OFF,
355 -- RETURNS, RETURNS_OFF,
356 PLANNED_OFF) offset,
357 dates.calendar_date new_date,
358 decode(rec.order_type, PAYBACK_SUPPLY,
359 dates.calendar_date, rec.old_schedule_date) old_date,
360 SUM(DECODE(rec.disposition_status_type,
361 2, 0, DECODE(rec.last_unit_completion_date,
362 NULL, nvl(rec.firm_quantity,rec.new_order_quantity),
363 rec.daily_rate))) new_quantity,
364 SUM(NVL(rec.old_order_quantity,0)) old_quantity,
365 sum(0) dos,
366 0 cost
367 FROM msc_form_query list,
368 msc_trading_partners param,
369 msc_system_items msi,
370 msc_supplies rec,
371 msc_calendar_dates dates
372 WHERE (arg_res_level = 1
373 OR (arg_res_level = 2
374 AND rec.project_id is NULL)
375 OR (DECODE(arg_res_level,
376 3,nvl(rec.planning_group,'-23453'),
377 4,nvl(to_char(rec.project_id), '-23453'))
378 = nvl(arg_resval1,'-23453'))
379 OR (arg_res_level = 5
380 AND nvl(to_char(rec.project_id), '-23453')
381 = nvl(arg_resval1,'-23453')
382 AND nvl(rec.task_id, -23453) = nvl(arg_resval2, -23453)))
383 AND dates.sr_instance_id = rec.sr_instance_id
384 AND dates.exception_set_id = param.calendar_exception_set_id
385 AND dates.calendar_code = param.calendar_code
386 AND dates.calendar_date BETWEEN
387 trunc(nvl(rec.firm_date,rec.new_schedule_date))
388 AND NVL(rec.last_unit_completion_date,
389 trunc(nvl(rec.firm_date,rec.new_schedule_date)))
390 AND (trunc(nvl(rec.firm_date,rec.new_schedule_date)) <= last_date OR
391 trunc(rec.old_schedule_date) <= last_date)
392 AND rec.plan_id = msi.plan_id
393 AND rec.inventory_item_id = msi.inventory_item_id
394 AND rec.organization_id = msi.organization_id
395 AND rec.sr_instance_id = msi.sr_instance_id
396 AND msi.plan_id = list.number4
397 AND msi.inventory_item_id = list.number1
398 AND msi.organization_id = list.number2
399 AND msi.sr_instance_id = list.number3
400 AND param.sr_tp_id = rec.organization_id
401 AND param.sr_instance_id = rec.sr_instance_id
402 AND param.partner_type = 3
403 AND list.query_id = item_list_id
404 AND list.number7 <> NODE_GL_FORECAST_ITEM
405 GROUP BY
406 list.number5,
407 list.number6,
408 list.number3,
409 DECODE(rec.order_type,
410 PURCHASE_ORDER, PO,
411 PURCH_REQ, REQ,
412 WORK_ORDER, WIP,
413 FLOW_SCHED, WIP,
414 REPETITIVE_SCHEDULE,PLANNED,
415 PLANNED_ORDER, PLANNED,
416 NONSTD_JOB, WIP,
417 RECEIPT_PURCH_ORDER,RECEIVING,
418 SHIPMENT, TRANSIT,
419 RECEIPT_SHIPMENT, RECEIVING,
420 PAYBACK_SUPPLY, PB_SUPPLY,
421 ON_HAND_QTY, ON_HAND,
422 AGG_REP_SCHEDULE, CURRENT_S,
423 -- RETURNS, RETURN_SUP,
424 PLANNED),
425 DECODE(rec.order_type,
426 PURCHASE_ORDER, PO_OFF,
427 PURCH_REQ, REQ_OFF,
428 WORK_ORDER, WIP_OFF,
429 FLOW_SCHED, WIP_OFF,
430 REPETITIVE_SCHEDULE,PLANNED_OFF,
431 PLANNED_ORDER, PLANNED_OFF,
432 NONSTD_JOB, WIP_OFF,
433 RECEIPT_PURCH_ORDER,RECEIVING_OFF,
434 SHIPMENT, TRANSIT_OFF,
435 RECEIPT_SHIPMENT, RECEIVING_OFF,
436 DIS_JOB_BY, WIP_OFF,
437 NON_ST_JOB_BY, WIP_OFF,
438 REP_SCHED_BY, PLANNED_OFF,
439 PLANNED_BY, PLANNED_OFF,
440 FLOW_SCHED_BY, WIP_OFF,
441 PAYBACK_SUPPLY, PB_SUPPLY_OFF,
442 ON_HAND_QTY, ON_HAND_OFF,
443 AGG_REP_SCHEDULE, CURRENT_S_OFF,
444 -- RETURNS, RETURNS_OFF,
445 PLANNED_OFF),
446 dates.calendar_date,
447 decode(rec.order_type, PAYBACK_SUPPLY, dates.calendar_date,
448 rec.old_schedule_date)
449 UNION ALL
450 SELECT list.number5 item_id,
451 list.number6 org_id,
452 list.number3 inst_id,
453 DECODE(mgr.origination_type,
454 1, DEPENDENT,
455 2, DEPENDENT,
456 3, DEPENDENT,
457 4, DEPENDENT,
458 5, EXP_LOT,
459 6, SALES,
460 7, FORECAST,
461 8, OTHER,
462 9, OTHER,
463 10, OTHER,
464 11, OTHER,
465 12, OTHER,
466 15, OTHER,
467 16, SCRAP,
468 17, SCRAP,
469 18, SCRAP,
470 19, SCRAP,
471 20, SCRAP,
472 21, SCRAP,
473 22, PROD_FORECAST,
474 23, SCRAP,
475 24, DEPENDENT,
476 25, DEPENDENT,
477 26, SCRAP,
478 29, FORECAST, -- for SRO
479 30, SALES,
480 DEMAND_PAYBACK, PB_DEMAND,
481 OTHER) row_type,
482 DECODE(mgr.origination_type,
483 1, DEPENDENT_OFF,
484 2, DEPENDENT_OFF,
485 3, DEPENDENT_OFF,
486 4, DEPENDENT_OFF,
487 5, EXP_LOT_OFF,
488 6, SALES_OFF,
489 7, FORECAST_OFF,
490 8, OTHER_OFF,
491 9, OTHER_OFF,
492 10, OTHER_OFF,
493 11, OTHER_OFF,
494 12, OTHER_OFF,
495 15, OTHER_OFF,
496 16, SCRAP_OFF,
497 17, SCRAP_OFF,
498 18, SCRAP_OFF,
499 19, SCRAP_OFF,
500 20, SCRAP_OFF,
501 21, SCRAP_OFF,
502 22, PROD_FORECAST_OFF,
503 23, SCRAP_OFF,
504 24, DEPENDENT_OFF,
505 25, DEPENDENT_OFF,
506 26, SCRAP_OFF,
507 29, FORECAST_OFF,
508 30, SALES_OFF,
509 DEMAND_PAYBACK, PB_DEMAND_OFF,
510 OTHER_OFF) offset,
511 dates.calendar_date new_date,
512 dates.calendar_date old_date,
513 SUM(DECODE(mgr.assembly_demand_comp_date,
514 NULL, DECODE(mgr.origination_type,
515 29,(nvl(mgr.probability,1)*
516 nvl(mgr.firm_quantity,using_requirement_quantity)),
517 31, 0,
518 nvl(mgr.firm_quantity,using_requirement_quantity)),
519 DECODE(mgr.origination_type,
520 29,(nvl(mgr.probability,1)*daily_demand_rate),
521 31, 0,
522 daily_demand_rate)))/
523 DECODE(nvl(LEAST(SUM(DECODE(mgr.origination_type,
524 29,nvl(mgr.probability,0),
525 null)) ,1) ,1),
526 0,1,
527 nvl(LEAST(SUM(DECODE(mgr.origination_type,
528 29,nvl(mgr.probability,0),
529 null)) ,1) ,1)) new_quantity,
530 0 old_quantity,
531 0 dos,
532 0 cost
533 FROM msc_form_query list,
534 msc_trading_partners param,
535 msc_demands mgr,
536 msc_calendar_dates dates
537 WHERE (arg_res_level = 1
538 OR (arg_res_level = 2
539 AND mgr.project_id is NULL)
540 OR (DECODE(arg_res_level,
541 3,nvl(mgr.planning_group,'-23453'),
542 4,nvl(to_char(mgr.project_id), '-23453'))
543 = nvl(arg_resval1,'-23453'))
544 OR (arg_res_level = 5
545 AND nvl(to_char(mgr.project_id), '-23453')
546 = nvl(arg_resval1,'-23453')
547 AND nvl(mgr.task_id, -23453) = nvl(arg_resval2, -23453)))
548 AND dates.sr_instance_id = mgr.sr_instance_id
549 AND dates.exception_set_id = param.calendar_exception_set_id
550 AND dates.calendar_code = param.calendar_code
551 AND dates.calendar_date BETWEEN trunc(
552 nvl(mgr.firm_date,mgr.using_assembly_demand_date))
553 AND NVL(trunc(mgr.assembly_demand_comp_date),
554 trunc(nvl(mgr.firm_date,mgr.using_assembly_demand_date)))
555 AND trunc(nvl(mgr.firm_date,mgr.using_assembly_demand_date))
556 <= trunc(last_date)
557 AND mgr.plan_id = list.number4
558 AND mgr.inventory_item_id = list.number1
559 AND mgr.organization_id = list.number2
560 AND mgr.sr_instance_id = list.number3
561 AND mgr.origination_type > 0 -- bug5653263
562 AND param.sr_tp_id = mgr.organization_id
563 AND param.sr_instance_id = mgr.sr_instance_id
564 AND param.partner_type = 3
565 AND list.query_id = item_list_id
566 AND list.number7 <> NODE_GL_FORECAST_ITEM
567 AND (l_plan_type <> 4 or
568 l_plan_type = 4 and -- 5086979: IO plan don't show past due demand
569 trunc(mgr.using_assembly_demand_date) >= trunc(l_plan_start_date))
570 AND not exists (
571 select 'cancelled IR'
572 from msc_supplies mr
573 where mgr.origination_type in (30,6)
574 and mgr.disposition_id = mr.transaction_id
575 and mgr.plan_id = mr.plan_id
576 and mgr.sr_instance_id = mr.sr_instance_id
577 and mr.disposition_status_type = 2)
578 GROUP BY
579 list.number5,
580 list.number6,
581 list.number3,
582 DECODE(mgr.origination_type,
583 1, DEPENDENT,
584 2, DEPENDENT,
585 3, DEPENDENT,
586 4, DEPENDENT,
587 5, EXP_LOT,
588 6, SALES,
589 7, FORECAST,
590 8, OTHER,
591 9, OTHER,
592 10, OTHER,
593 11, OTHER,
594 12, OTHER,
595 15, OTHER,
596 16, SCRAP,
597 17, SCRAP,
598 18, SCRAP,
599 19, SCRAP,
600 20, SCRAP,
601 21, SCRAP,
602 22, PROD_FORECAST,
603 23, SCRAP,
604 24, DEPENDENT,
605 25, DEPENDENT,
606 26, SCRAP,
607 29, FORECAST,
608 30, SALES,
609 DEMAND_PAYBACK, PB_DEMAND,
610 OTHER),
611 DECODE(mgr.origination_type,
612 1, DEPENDENT_OFF,
613 2, DEPENDENT_OFF,
614 3, DEPENDENT_OFF,
615 4, DEPENDENT_OFF,
616 5, EXP_LOT_OFF,
617 6, SALES_OFF,
618 7, FORECAST_OFF,
619 8, OTHER_OFF,
620 9, OTHER_OFF,
621 10, OTHER_OFF,
622 11, OTHER_OFF,
623 12, OTHER_OFF,
624 15, OTHER_OFF,
625 16, SCRAP_OFF,
626 17, SCRAP_OFF,
627 18, SCRAP_OFF,
628 19, SCRAP_OFF,
629 20, SCRAP_OFF,
630 21, SCRAP_OFF,
631 22, PROD_FORECAST_OFF,
632 23, SCRAP_OFF,
633 24, DEPENDENT_OFF,
634 25, DEPENDENT_OFF,
635 26, SCRAP_OFF,
636 29, FORECAST_OFF,
637 30, SALES_OFF,
638 DEMAND_PAYBACK, PB_DEMAND_OFF,
639 OTHER_OFF),
640 dates.calendar_date,
641 dates.calendar_date,
642 0
643 UNION ALL
644 --- ------------------------------------
645 --- FOR MAD / MAPE
646 --- ------------------------------------
647 SELECT list.number5 item_id,
648 list.number6 org_id,
649 list.number3 inst_id,
650 MAD1 row_type,
651 MAD_OFF offset,
652 dates.calendar_date new_date,
653 dates.calendar_date old_date,
654 SQRT(SUM(DECODE(mgr.error_type, 1, mgr.forecast_MAD * mgr.forecast_MAD, 0))) new_quantity,
655 SQRT(SUM(DECODE(mgr.error_type, 2, ((mgr.forecast_MAD * mgr.using_requirement_quantity) * (mgr.forecast_MAD * mgr.using_requirement_quantity)), 0))) /
656 DECODE(SUM (NVL(mgr.using_requirement_quantity, 1)) ,0 ,1 ,
657 SUM (NVL(mgr.using_requirement_quantity, 1))) old_quantity,
658 0 dos,
659 0 cost
660 FROM msc_form_query list,
661 msc_trading_partners param,
662 msc_demands mgr,
663 msc_calendar_dates dates
664 WHERE (arg_res_level = 1
665 OR (arg_res_level = 2
666 AND mgr.project_id is NULL)
667 OR (DECODE(arg_res_level,
668 3,nvl(mgr.planning_group,'-23453'),
669 4,nvl(to_char(mgr.project_id), '-23453'))
670 = nvl(arg_resval1,'-23453'))
671 OR (arg_res_level = 5
672 AND nvl(to_char(mgr.project_id), '-23453')
673 = nvl(arg_resval1,'-23453')
674 AND nvl(mgr.task_id, -23453) = nvl(arg_resval2, -23453)))
675 AND dates.sr_instance_id = mgr.sr_instance_id
676 AND dates.exception_set_id = param.calendar_exception_set_id
677 AND dates.calendar_code = param.calendar_code
678 AND dates.calendar_date BETWEEN trunc(mgr.using_assembly_demand_date)
679 AND NVL(trunc(mgr.assembly_demand_comp_date),
680 trunc(mgr.using_assembly_demand_date))
681 AND trunc(mgr.using_assembly_demand_date) <= trunc(last_date)
682 AND mgr.plan_id = list.number4
683 AND mgr.inventory_item_id = list.number1
684 AND mgr.organization_id = list.number2
685 AND mgr.sr_instance_id = list.number3
686 AND param.sr_tp_id = mgr.organization_id
687 AND param.sr_instance_id = mgr.sr_instance_id
688 AND param.partner_type = 3
689 AND mgr.origination_type in (7, 29)
690 AND list.query_id = item_list_id
691 AND l_plan_type = 4 -- only show MAD for IO plan
692 AND list.number7 <> NODE_GL_FORECAST_ITEM
693 GROUP BY
694 list.number5,
695 list.number6,
696 list.number3,
697 MAD1, MAD_OFF,
698 dates.calendar_date,
699 dates.calendar_date,
700 0
701 UNION ALL
702 SELECT list.number5 item_id,
703 list.number6 org_id,
704 list.number3 inst_id,
705 ATP row_type,
706 ATP_OFF offset,
707 avail.schedule_date new_date,
708 avail.schedule_date old_date,
709 avail.quantity_available new_quantity,
710 0 old_quantity,
711 0 dos,
712 0 cost
713 FROM msc_form_query list,
714 msc_available_to_promise avail
715 WHERE avail.schedule_date < last_date
716 AND avail.organization_id = list.number2
717 AND avail.plan_id = list.number4
718 AND avail.inventory_item_id = list.number1
719 AND avail.sr_instance_id = list.number3
720 AND list.query_id = item_list_id
721 UNION ALL
722 SELECT list.number5 item_id,
723 list.number6 org_id,
724 list.number3 inst_id,
725 SS row_type,
726 SS_OFF offset,
727 safety.period_start_date new_date,
728 safety.period_start_date old_date,
729 sum(safety.safety_stock_quantity) new_quantity,
730 safety.organization_id old_quantity,
731 sum(safety.achieved_days_of_supply) dos,
732 sum(safety.safety_stock_quantity * item.standard_cost) cost
733 FROM msc_safety_stocks safety,
734 msc_form_query list ,
735 msc_system_items item
736 WHERE safety.period_start_date <= last_date
737 AND safety.organization_id = list.number2
738 AND safety.sr_instance_id = list.number3
739 AND safety.plan_id = list.number4
740 AND safety.inventory_item_id = list.number1
741 AND nvl(safety.project_id,1) =
742 decode(arg_res_level,4,nvl(arg_resval1,nvl(safety.project_id,1)),5,nvl(arg_resval1,nvl(safety.project_id,1)),nvl(safety.project_id,1))
743 AND nvl(safety.task_id,1) =
744 decode(arg_res_level,5,nvl(arg_resval2,nvl(safety.task_id,1)),nvl(safety.task_id,1))
745 AND list.query_id = item_list_id
746 AND list.number7 <> NODE_GL_FORECAST_ITEM
747 and safety.safety_stock_quantity is not null
748 AND safety.organization_id = item.organization_id
749 AND safety.sr_instance_id = item.sr_instance_id
750 AND safety.plan_id = item.plan_id
751 AND safety.inventory_item_id = item.inventory_item_id
752 GROUP BY list.number5,
753 list.number6,
754 list.number3,
755 SS, SS_OFF, safety.period_start_date, safety.organization_id
756 UNION ALL
757 --------------------------------------------------------------------
758 -- This will select unconstrained safety stock for sro plans
759 ---------------------------------------------------------------------
760 SELECT list.number5 item_id,
761 list.number6 org_id,
762 list.number3 inst_id,
763 SS_UNC row_type,
764 SSUNC_OFF offset,
765 safety.period_start_date new_date,
766 safety.period_start_date old_date,
767 sum(safety.TARGET_SAFETY_STOCK) new_quantity,
768 sum(safety.TOTAL_UNPOOLED_SAFETY_STOCK) old_quantity,
769 sum(safety.target_days_of_supply) dos,
770 sum(safety.TARGET_SAFETY_STOCK * item.standard_cost) cost
771 FROM msc_safety_stocks safety,
772 msc_form_query list ,
773 msc_system_items item
774 WHERE safety.period_start_date <= last_date
775 AND safety.organization_id = list.number2
776 AND safety.sr_instance_id = list.number3
777 AND safety.plan_id = list.number4
778 AND safety.inventory_item_id = list.number1
779 AND nvl(safety.project_id,1) =
780 decode(arg_res_level,4,nvl(arg_resval1,nvl(safety.project_id,1)),5,nvl(arg_resval1,nvl(safety.project_id,1)),nvl(safety.project_id,1))
781 AND nvl(safety.task_id,1) =
782 decode(arg_res_level,5,nvl(arg_resval2,nvl(safety.task_id,1)),nvl(safety.task_id,1))
783 AND list.query_id = item_list_id
784 AND list.number7 <> NODE_GL_FORECAST_ITEM
785 -- and safety.target_safety_stock is not null
786 AND safety.organization_id = item.organization_id
787 AND safety.sr_instance_id = item.sr_instance_id
788 AND safety.plan_id = item.plan_id
789 AND safety.inventory_item_id = item.inventory_item_id
790 GROUP BY list.number5,list.number6,list.number3,
791 SS_UNC, SSUNC_OFF,
792 safety.period_start_date
793 UNION ALL
794 --------------------------------------------------------------------
795 -- This will select user specified safety stocks
796 ---------------------------------------------------------------------
797 SELECT list.number5 item_id,
798 list.number6 org_id,
799 list.number3 inst_id,
800 USS row_type,
801 USS_OFF offset,
802 safety.period_start_date new_date,
803 safety.period_start_date old_date,
804 sum(safety.USER_DEFINED_SAFETY_STOCKS) new_quantity,
805 sum(0) old_quantity,
806 sum(safety.user_defined_dos) dos,
807 sum(safety.USER_DEFINED_SAFETY_STOCKS * item.standard_cost) cost
808 FROM msc_safety_stocks safety,
809 msc_form_query list,
810 msc_system_items item
811 WHERE safety.period_start_date <= last_date
812 AND safety.organization_id = list.number2
813 AND safety.sr_instance_id = list.number3
814 AND safety.plan_id = list.number4
815 AND safety.inventory_item_id = list.number1
816 AND nvl(safety.project_id,1) =
817 decode(arg_res_level,4,nvl(arg_resval1,nvl(safety.project_id,1)),5,nvl(arg_resval1,nvl(safety.project_id,1)),nvl(safety.project_id,1))
818 AND nvl(safety.task_id,1) =
819 decode(arg_res_level,5,nvl(arg_resval2,nvl(safety.task_id,1)),nvl(safety.task_id,1))
820 AND list.query_id = item_list_id
821 AND list.number7 <> NODE_GL_FORECAST_ITEM
822 and nvl(safety.user_defined_safety_stocks,safety.user_defined_dos) is not null
823 AND safety.organization_id = item.organization_id
824 AND safety.sr_instance_id = item.sr_instance_id
825 AND safety.plan_id = item.plan_id
826 AND safety.inventory_item_id = item.inventory_item_id
827 GROUP BY list.number5,list.number6,list.number3,
828 USS, USS_OFF,
829 safety.period_start_date, 0
830 UNION ALL
831 --------------------------------------------------------------------
832 -- This will select Lead Time Variability Percentages
833 ---------------------------------------------------------------------
834 SELECT list.number5 item_id,
835 list.number6 org_id,
836 list.number3 inst_id,
837 MANU_VARI row_type,
838 MANF_VARI_OFF offset,
839 safety.period_start_date new_date,
840 safety.period_start_date old_date,
841 sum(safety.MFG_LTVAR_SS_PERCENT) new_quantity,
842 sum(safety.SUP_LTVAR_SS_PERCENT) old_quantity,
843 sum(safety.TRANSIT_LTVAR_SS_PERCENT) dos,
844 sum(safety.DEMAND_VAR_SS_PERCENT) cost
845 FROM msc_safety_stocks safety,
846 msc_form_query list,
847 msc_system_items item
848 WHERE safety.period_start_date <= last_date
849 AND safety.organization_id = list.number2
850 AND safety.sr_instance_id = list.number3
851 AND safety.plan_id = list.number4
852 AND safety.inventory_item_id = list.number1
853 AND nvl(safety.project_id,1) =
854 decode(arg_res_level,4,nvl(arg_resval1,nvl(safety.project_id,1)),5,nvl(arg_resval1,nvl(safety.project_id,1)),nvl(safety.project_id,1))
855 AND nvl(safety.task_id,1) =
856 decode(arg_res_level,5,nvl(arg_resval2,nvl(safety.task_id,1)),nvl(safety.task_id,1))
857 AND list.query_id = item_list_id
858 AND list.number7 <> NODE_GL_FORECAST_ITEM
859 AND safety.organization_id = item.organization_id
860 AND safety.sr_instance_id = item.sr_instance_id
861 AND safety.plan_id = item.plan_id
862 AND safety.inventory_item_id = item.inventory_item_id
863 GROUP BY list.number5,list.number6,list.number3,
864 MANU_VARI, MANF_VARI_OFF,
865 safety.period_start_date
866 UNION ALL
867 --------------------------------------------------------------------
868 -- This will select minimum inventory levels
869 ---------------------------------------------------------------------
870 SELECT list.number5 item_id,
871 list.number6 org_id,
872 list.number3 inst_id,
873 min_inv_lvl row_type,
874 min_inv_lvl_off offset,
875 lvl.inventory_date new_date,
876 lvl.inventory_date old_date,
877 min(lvl.Min_quantity) new_quantity,
878 min(0) old_quantity,
879 min(lvl.min_quantity_dos) dos,
880 0
881 FROM msc_inventory_levels lvl,
882 msc_form_query list
883 WHERE lvl.inventory_date <= last_date
884 AND lvl.organization_id = list.number2
885 AND lvl.sr_instance_id = list.number3
886 AND lvl.plan_id = list.number4
887 AND lvl.inventory_item_id = list.number1
888 AND list.query_id = item_list_id
889 AND list.number7 <> NODE_GL_FORECAST_ITEM
890 AND nvl(lvl.min_quantity,lvl.min_quantity_dos) is not null
891 GROUP BY list.number5,list.number6,list.number3,
892 min_inv_lvl, min_inv_lvl_off,
893 lvl.inventory_date
894 UNION ALL
895 --------------------------------------------------------------------
896 -- This will select maximum inventory levels
897 ---------------------------------------------------------------------
898 SELECT list.number5 item_id,
899 list.number6 org_id,
900 list.number3 inst_id,
901 max_inv_lvl row_type,
902 max_inv_lvl_off offset,
903 lvl.inventory_date new_date,
904 lvl.inventory_date old_date,
905 max(lvl.Max_quantity) new_quantity,
906 max(0) old_quantity,
907 max(lvl.max_quantity_dos) dos,
908 0
909 FROM msc_inventory_levels lvl,
910 msc_form_query list
911 WHERE lvl.inventory_date<= last_date
912 AND lvl.organization_id = list.number2
913 AND lvl.sr_instance_id = list.number3
914 AND lvl.plan_id = list.number4
915 AND lvl.inventory_item_id = list.number1
916 AND list.query_id = item_list_id
917 AND list.number7 <> NODE_GL_FORECAST_ITEM
918 AND nvl(lvl.max_quantity,lvl.max_quantity_dos) is not null
919 GROUP BY list.number5,list.number6,list.number3,
920 max_inv_lvl, max_inv_lvl_off,
921 lvl.inventory_date
922 union all
923 --------------------------------------------------------------------
924 -- This will select Target Inventory Levels
925 ---------------------------------------------------------------------
926 SELECT list.number5 item_id,
927 list.number6 org_id,
928 list.number3 inst_id,
929 TARGET_SER_LVL row_type,
930 TARGET_SER_OFF offset,
931 nvl(lvl.week_start_date, lvl.period_start_date) new_date,
932 nvl(lvl.week_start_date, lvl.period_start_date) old_date,
933 avg(lvl.TARGET_SERVICE_LEVEL) new_quantity,
934 0 old_quantity,
935 0 dos,
936 0
937 FROM msc_analysis_aggregate lvl,
938 msc_form_query list,
939 msc_plan_buckets mpb
940 WHERE lvl.record_type = 3
941 AND lvl.period_type = 1
942 AND lvl.plan_id = list.number4
943 AND lvl.inventory_item_id = list.number1
944 AND lvl.organization_id = list.number2
945 AND lvl.sr_instance_id = list.number3
946 AND list.query_id = item_list_id
947 AND list.number7 <> NODE_GL_FORECAST_ITEM
948 AND mpb.plan_id = lvl.plan_id
949 AND ( (mpb.bucket_type = 2 and lvl.week_start_date = mpb.BKT_START_DATE) or
950 (mpb.bucket_type = 3 and lvl.period_start_date = mpb.BKT_START_DATE) )
951 GROUP BY list.number5,list.number6,list.number3,
952 TARGET_SER_LVL, TARGET_SER_OFF,
953 nvl(lvl.week_start_date, lvl.period_start_date) ,
954 nvl(lvl.week_start_date, lvl.period_start_date)
955 union all
956
957 --------------------------------------------------------------------
958 -- This will select ACHIEVED Inventory Levels
959 ---------------------------------------------------------------------
960 SELECT list.number5 item_id,
961 list.number6 org_id,
962 list.number3 inst_id,
963 ACHIEVED_SER_LVL row_type,
964 ACHIEVED_SER_OFF offset,
965 nvl(lvl.week_start_date, lvl.period_start_date) new_date,
966 nvl(lvl.week_start_date, lvl.period_start_date) old_date,
967 sum(lvl.ACHIEVED_SERVICE_LEVEL_QTY1)/sum(decode(lvl.ACHIEVED_SERVICE_LEVEL_QTY2, 0, 1, lvl.ACHIEVED_SERVICE_LEVEL_QTY2)) new_quantity,
968 0 old_quantity,
969 0 dos,
970 0
971 FROM msc_analysis_aggregate lvl,
972 msc_form_query list,
973 msc_plan_buckets mpb
974 WHERE lvl.record_type = 3
975 AND lvl.period_type = 1
976 AND lvl.plan_id = list.number4
977 AND lvl.organization_id = list.number2
978 AND lvl.sr_instance_id = list.number3
979 AND lvl.inventory_item_id = list.number1
980 AND list.query_id = item_list_id
981 AND list.number7 <> NODE_GL_FORECAST_ITEM
982 AND mpb.plan_id = lvl.plan_id
983 AND ( (mpb.bucket_type = 2 and lvl.week_start_date = mpb.BKT_START_DATE) or
984 (mpb.bucket_type = 3 and lvl.period_start_date = mpb.BKT_START_DATE) )
985 GROUP BY list.number5,list.number6,list.number3,
986 ACHIEVED_SER_LVL, ACHIEVED_SER_OFF,
987 nvl(lvl.week_start_date, lvl.period_start_date) ,
988 nvl(lvl.week_start_date, lvl.period_start_date)
989 union all
990 --------------------------------------------------------------------
991 -- This select will ensure that all selected items get into cursor
992 -- even though they do not have any activity
993 ---------------------------------------------------------------------
994 SELECT list.number5,
995 list.number6,
996 list.number3,
997 ON_HAND,
998 ON_HAND_OFF,
999 to_date(1, 'J'),
1000 to_date(1, 'J'),
1001 0,
1002 0,
1003 0,
1004 0
1005 FROM msc_form_query list
1006 WHERE list.query_id = item_list_id
1007 ORDER BY
1008 1, 2, 6, 4;
1009
1010 cursor standard_cost (p_inventory_item_id number,
1011 p_sr_instance_id number,
1012 p_organization_id number,
1013 p_plan_id number) is
1014 select nvl(standard_cost,0)
1015 from msc_system_items
1016 where inventory_item_id=p_inventory_item_id
1017 and organization_id =p_organization_id
1018 and sr_instance_id =p_sr_instance_id
1019 and plan_id =p_plan_id;
1020
1021 TYPE mrp_activity IS RECORD
1022 (item_id NUMBER,
1023 org_id NUMBER,
1024 inst_id NUMBER,
1025 row_type NUMBER,
1026 offset NUMBER,
1027 new_date DATE,
1028 old_date DATE,
1029 new_quantity NUMBER,
1030 old_quantity NUMBER,
1031 DOS NUMBER,
1032 cost number);
1033
1034 activity_rec mrp_activity;
1035
1036 TYPE calendar_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1037
1038 TYPE column_char IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
1039 TYPE number_arr IS TABLE OF number;
1040
1041 var_dates calendar_date; -- Holds the start dates of buckets
1042 bucket_cells_tab column_number; -- Holds the quantities per bucket
1043 ep_bucket_cells_tab column_number;
1044 last_item_id NUMBER := -1;
1045 last_org_id NUMBER := -1;
1046 last_inst_id NUMBER := -1;
1047
1048 prev_ss_qty number_arr := number_arr(0);
1049 prev_ss_org number_arr := number_arr(0);
1050 prev_ss_dos_arr number_arr := number_arr(0);
1051 prev_ss_cost_arr number_arr := number_arr(0);
1052 prev_ss_quantity NUMBER := -1;
1053 prev_ss_dos NUMBER := -1;
1054 prev_ss_cost number := -1;
1055
1056
1057 prev_non_pool_ss NUMBER := -1;
1058 non_pool_ss NUMBER := -1;
1059
1060 prev_target_level NUMBER := -1;
1061 prev_achieved_level NUMBER := -1;
1062 prev_mad NUMBER := -1;
1063 prev_mape NUMBER := -1;
1064 prev_min NUMBER := -1;
1065 prev_max NUMBER := -1;
1066
1067 target_level NUMBER := -1;
1068 achieved_level NUMBER := -1;
1069 mad NUMBER := -1;
1070 mape NUMBER := -1;
1071 min_lvl NUMBER := -1;
1072 max_lvl NUMBER := -1;
1073
1074 prev_manf_vari NUMBER := -1;
1075 prev_purc_vari NUMBER := -1;
1076 prev_tran_vari NUMBER := -1;
1077 prev_dmnd_vari NUMBER := -1;
1078
1079
1080 manf_vari NUMBER := -1;
1081 purc_vari NUMBER := -1;
1082 tran_vari NUMBER := -1;
1083 dmnd_vari NUMBER := -1;
1084
1085 vari_date date;
1086 prev_vari_date date;
1087
1088 prev_ssunc_q NUMBER := -1;
1089 prev_ssunc_dos NUMBER := -1;
1090 prev_ssunc_date DATE;
1091 ssunc_q NUMBER := -1;
1092 ssunc_dos NUMBER := -1;
1093 ssunc_date DATE;
1094
1095 prev_uss_q NUMBER := -1;
1096 prev_uss_dos NUMBER := -1;
1097 prev_uss_date DATE;
1098 uss_q NUMBER := -1;
1099 uss_dos NUMBER := -1;
1100 uss_date DATE;
1101 ssunc_cost number := -1;
1102 prev_ssunc_cost number := -1;
1103 uss_cost number := -1;
1104 prev_uss_cost number := -1;
1105
1106 bucket_counter BINARY_INTEGER := 0; -- Id of the current bucket
1107 old_bucket_counter BINARY_INTEGER := 0;
1108 counter BINARY_INTEGER := 0;
1109
1110 PROCEDURE init_prev_ss_qty IS
1111 v_count number;
1112 p_found_org boolean;
1113 BEGIN
1114 if activity_rec.org_id <> -1 THEN -- single org view
1115 prev_ss_quantity := activity_rec.new_quantity;
1116 prev_ss_dos := activity_rec.dos;
1117 prev_ss_cost := activity_rec.cost;
1118 return;
1119 end if;
1120 p_found_org := false;
1121 v_count := nvl(prev_ss_org.last,0);
1122 -- dbms_output.put_line(' in init '||v_count||','||activity_rec.old_quantity||','||activity_rec.new_quantity);
1123 for a in 1 .. v_count loop
1124 if prev_ss_org(a) = activity_rec.old_quantity then
1125 prev_ss_qty(a) := activity_rec.new_quantity;
1126 prev_ss_dos_arr(a) := activity_rec.dos;
1127 prev_ss_cost_arr(a) := activity_rec.cost;
1128 p_found_org := true;
1129 exit;
1130 end if;
1131 end loop;
1132
1133 -- if org_id not exists, add it
1134
1135 if not(p_found_org) then
1136 prev_ss_org.extend;
1137 prev_ss_qty.extend;
1138 prev_ss_dos_arr.extend;
1139 prev_ss_cost_arr.extend;
1140 prev_ss_org(v_count+1) := activity_rec.old_quantity;
1141 prev_ss_qty(v_count+1) := activity_rec.new_quantity;
1142 prev_ss_dos_arr(v_count+1) := activity_rec.dos;
1143 prev_ss_cost_arr(v_count+1) := activity_rec.cost;
1144 end if;
1145
1146 prev_ss_quantity := 0;
1147 prev_ss_dos := 0;
1148 prev_ss_cost := 0;
1149 for a in 1..nvl(prev_ss_org.last,0) loop
1150 prev_ss_quantity := prev_ss_quantity + prev_ss_qty(a);
1151 prev_ss_dos := prev_ss_dos + prev_ss_dos_arr(a);
1152 prev_ss_cost := prev_ss_cost + prev_ss_cost_arr(a);
1153 end loop;
1154
1155 -- dbms_output.put_line('prev = '||prev_ss_quantity||','||prev_ss_dos||','||prev_ss_cost);
1156 END init_prev_ss_qty;
1157
1158 PROCEDURE reset_prev_ss IS
1159 BEGIN
1160 prev_ss_org.delete;
1161 prev_ss_qty.delete;
1162 prev_ss_dos_arr.delete;
1163 prev_ss_cost_arr.delete;
1164 prev_ss_quantity := -1;
1165 prev_ss_dos := -1;
1166 prev_ss_cost := -1;
1167 END reset_prev_ss;
1168
1169 -- =============================================================================
1170 --
1171 -- add_to_plan add the 'quantity' to the correct cell of the current bucket.
1172 --
1173 -- =============================================================================
1174 PROCEDURE add_to_plan(bucket IN NUMBER,
1175 offset IN NUMBER,
1176 quantity IN NUMBER,
1177 p_enterprise IN boolean default false) IS
1178 location NUMBER;
1179 BEGIN
1180 g_error_stmt := 'Debug - add_to_plan - 10';
1181 if quantity = 0 then
1182 return;
1183 end if;
1184 IF p_enterprise then
1185 location := (bucket - 1) + offset;
1186 IF offset in (SSUNC_OFF,SSUNC_DOS_OFF, SSUNC_VAL_OFF, SS_OFF,SS_DOS_OFF, SS_VAL_OFF, USS_OFF , USS_DOS_OFF, USS_VAL_OFF, min_inv_lvl_off , max_inv_lvl_off )THEN
1187 ep_bucket_cells_tab(location) := quantity;
1188 ELSE
1189 ep_bucket_cells_tab(location) :=
1190 NVL(ep_bucket_cells_tab(location),0) + quantity;
1191 END IF;
1192 ELSE -- not enterprize view
1193 location := ((bucket - 1) * NUM_OF_TYPES) + offset;
1194 IF offset in (SSUNC_OFF, SSUNC_DOS_OFF, SSUNC_VAL_OFF, SS_OFF,SS_DOS_OFF, SS_VAL_OFF, USS_OFF , USS_DOS_OFF, USS_VAL_OFF, min_inv_lvl_off , max_inv_lvl_off) THEN
1195 bucket_cells_tab(location) := quantity;
1196 ELSE
1197 bucket_cells_tab(location) := NVL(bucket_cells_tab(location),0) + quantity;
1198 END IF;
1199 END IF;
1200 END;
1201
1202 -- =============================================================================
1203 --
1204 -- flush_item_plan inserts into MRP_MATERIAL_PLANS
1205 --
1206 -- =============================================================================
1207 PROCEDURE flush_item_plan(p_item_id IN NUMBER,
1208 p_org_id IN NUMBER,
1209 p_inst_id IN NUMBER) IS
1210 loop_counter BINARY_INTEGER := 1;
1211 item_name VARCHAR2(255);
1212 org_code VARCHAR2(7);
1213 atp_counter BINARY_INTEGER := 1;
1214 total_reqs NUMBER := 0;
1215 lot_quantity NUMBER := 0;
1216 expired_qty NUMBER := 0;
1217 total_supply NUMBER := 0;
1218 committed_demand NUMBER := 0;
1219 atp_qty NUMBER := 0;
1220 carried_back_atp_qty NUMBER := 0;
1221 atp_flag NUMBER :=2;
1222 l_atp_qty_net MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
1223
1224 cursor check_atp is
1225 SELECT msi.calculate_atp
1226 FROM msc_system_items msi,
1227 msc_form_query mfq
1228 WHERE msi.inventory_item_id = mfq.number1
1229 AND msi.organization_id = mfq.number2
1230 AND msi.plan_id = arg_plan_id
1231 AND msi.sr_instance_id = mfq.number3
1232 AND mfq.query_id = arg_query_id
1233 and mfq.number5 = p_item_id
1234 and mfq.number6 = p_org_id
1235 and mfq.number3 = p_inst_id;
1236
1237 TYPE bkt_data_rec IS RECORD(
1238 qty1 column_number,
1239 qty2 column_number,
1240 qty3 column_number,
1241 qty4 column_number,
1242 qty5 column_number,
1243 qty6 column_number,
1244 qty7 column_number,
1245 qty8 column_number,
1246 qty9 column_number,
1247 qty10 column_number,
1248 qty11 column_number,
1249 qty12 column_number,
1250 qty13 column_number,
1251 qty14 column_number,
1252 qty15 column_number,
1253 qty16 column_number,
1254 qty17 column_number,
1255 qty18 column_number,
1256 qty19 column_number,
1257 qty20 column_number,
1258 qty21 column_number,
1259 qty22 column_number,
1260 qty23 column_number,
1261 qty24 column_number,
1262 qty25 column_number,
1263 qty26 column_number,
1264 qty27 column_number,
1265 qty28 column_number,
1266 qty29 column_number,
1267 qty30 column_number,
1268 qty31 column_number,
1269 qty32 column_number,
1270 qty33 column_number,
1271 qty34 column_number,
1272 qty35 column_number,
1273 qty36 column_number,
1274 qty37 column_number,
1275 qty38 column_number,
1276 qty39 column_number,
1277 qty40 column_number,
1278 qty41 column_number,
1279 qty42 column_number);
1280
1281 bkt_data bkt_data_rec;
1282
1283 BEGIN
1284
1285 -- -------------------------------
1286 -- Get the item segments, atp flag
1287 -- -------------------------------
1288 g_error_stmt := 'Debug - flush_item_plan - 10';
1289 OPEN check_atp;
1290 FETCH check_atp INTO atp_flag;
1291 CLOSE check_atp;
1292
1293 IF NOT enterprize_view THEN
1294 -- -----------------------------
1295 -- Calculate gross requirements,
1296 -- Total suppy
1297 -- PAB
1298 -- POH
1299 -- -----------------------------
1300
1301 FOR loop IN 1..g_num_of_buckets LOOP
1302 ----------------------
1303 -- Gross requirements.
1304 -- -------------------
1305 g_error_stmt := 'Debug - flush_item_plan - 20 - loop'||loop;
1306 lot_quantity := bucket_cells_tab(((loop - 1) * NUM_OF_TYPES)+
1307 EXP_LOT_OFF);
1308 if lot_quantity > 0 then
1309 -- bug5223364, expire lot is other independent demand
1310 add_to_plan(loop,
1311 OTHER_OFF,
1312 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + EXP_LOT_OFF));
1313 end if;
1314
1315 add_to_plan(loop,
1316 GROSS_OFF,
1317 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SALES_OFF) +
1318 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + FORECAST_OFF) +
1319 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + DEPENDENT_OFF) +
1320 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PB_DEMAND_OFF) +
1321 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SCRAP_OFF) +
1322 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + OTHER_OFF));
1323
1324 /* 2994650, no need to apply special logic to re-calculate expired lot in ASCP
1325
1326 total_reqs := total_reqs +
1327 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SALES_OFF) +
1328 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) +
1329 FORECAST_OFF) +
1330 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) +
1331 DEPENDENT_OFF) +
1332 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) +
1333 PROD_FORECAST_OFF) +
1334 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) +
1335 PB_DEMAND_OFF) +
1336 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + OTHER_OFF);
1337
1338 --------------------
1339 -- Lot Expirations
1340 --------------------
1341 IF(lot_quantity > total_reqs and lot_quantity > 0 ) THEN
1342 expired_qty := lot_quantity - total_reqs;
1343 total_reqs := 0;
1344
1345 add_to_plan(loop,
1346 GROSS_OFF,
1347 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SALES_OFF) +
1348 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + FORECAST_OFF) +
1349 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + DEPENDENT_OFF) +
1350 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PROD_FORECAST_OFF) +
1351 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PB_DEMAND_OFF) +
1352 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SCRAP_OFF) +
1353 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + OTHER_OFF) +
1354 expired_qty);
1355
1356
1357 add_to_plan(loop,
1358 OTHER_OFF,
1359 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + OTHER_OFF) +
1360 expired_qty);
1361 ELSE
1362
1363 add_to_plan(loop,
1364 GROSS_OFF,
1365 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SALES_OFF) +
1366 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + FORECAST_OFF) +
1367 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + DEPENDENT_OFF) +
1368 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PROD_FORECAST_OFF) +
1369 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PB_DEMAND_OFF) +
1370 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SCRAP_OFF) +
1371 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + OTHER_OFF));
1372
1373 END IF;
1374 */
1375
1376 g_error_stmt := 'Debug - flush_item_plan - 30 - loop'||loop;
1377 -- -------------
1378 -- Total supply.
1379 -- -------------
1380 add_to_plan(loop,
1381 SUPPLY_OFF,
1382 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + WIP_OFF) +
1383 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PO_OFF) +
1384 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + REQ_OFF) +
1385 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + TRANSIT_OFF) +
1386 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + RECEIVING_OFF) +
1387 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PB_SUPPLY_OFF) +
1388 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PLANNED_OFF));
1389
1390 -- bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + RETURNS_OFF));
1391
1392 -- ----------------------------
1393 -- Projected available balance.
1394 -- ----------------------------
1395 g_error_stmt := 'Debug - flush_item_plan - 40 - loop'||loop;
1396 -- The first bucket is past due so we include onhand from the second
1397 -- bucket.
1398 IF loop = 1 THEN
1399 add_to_plan(loop,
1400 PAB_OFF,
1401 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + ON_HAND_OFF) +
1402 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SUPPLY_OFF) -
1403 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + GROSS_OFF));
1404 ELSE
1405 add_to_plan(loop,
1406 PAB_OFF,
1407 bucket_cells_tab(((loop - 2) * NUM_OF_TYPES) + PAB_OFF) +
1408 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + ON_HAND_OFF) +
1409 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SUPPLY_OFF) -
1410 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + GROSS_OFF));
1411 END IF;
1412
1413 -- ------------------
1414 -- Projected on hand.
1415 -- ------------------
1416 g_error_stmt := 'Debug - flush_item_plan - 50 - loop'||loop;
1417 -- The first bucket is past due so we include onhand from the second
1418 -- bucket.
1419 IF loop = 1 THEN
1420 add_to_plan(loop,
1421 POH_OFF,
1422 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + ON_HAND_OFF) +
1423 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + CURRENT_S_OFF) -
1424 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + GROSS_OFF));
1425 ELSIF loop = 2 THEN
1426 add_to_plan(loop,
1427 POH_OFF,
1428 bucket_cells_tab(((loop - 2) * NUM_OF_TYPES) + POH_OFF) +
1429 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + ON_HAND_OFF) +
1430 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + CURRENT_S_OFF) -
1431 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + GROSS_OFF));
1432 ELSE
1433 add_to_plan(loop,
1434 POH_OFF,
1435 bucket_cells_tab(((loop - 2) * NUM_OF_TYPES) + POH_OFF) +
1436 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + CURRENT_S_OFF) -
1437 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + GROSS_OFF));
1438 END IF;
1439
1440 END LOOP; -- columnd
1441 ----------------
1442 -- calculate ATP
1443 ----------------
1444 g_error_stmt := 'Debug - flush_item_plan - 60';
1445
1446 FOR atp_counter IN 1..g_num_of_buckets LOOP
1447 add_to_plan(atp_counter, ATP_OFF, 0);
1448 END LOOP;
1449
1450 if atp_flag = 1 then -- only calculate atp when atp_flag is 1
1451
1452 IF l_atp_qty_net.count = 0 THEN
1453 l_atp_qty_net.Extend(g_num_of_buckets);
1454 END IF;
1455
1456 FOR atp_counter IN 1..g_num_of_buckets LOOP
1457
1458 IF atp_counter = 2 THEN
1459 total_supply := bucket_cells_tab(((atp_counter - 1)
1460 * NUM_OF_TYPES) + SUPPLY_OFF)+
1461 bucket_cells_tab(((atp_counter - 1) *
1462 NUM_OF_TYPES) + ON_HAND_OFF);
1463 ELSE
1464 total_supply := bucket_cells_tab(((atp_counter - 1)
1465 * NUM_OF_TYPES) + SUPPLY_OFF);
1466
1467 END IF;
1468
1469 committed_demand := bucket_cells_tab(((atp_counter - 1)
1470 * NUM_OF_TYPES) + SALES_OFF) +
1471 bucket_cells_tab(((atp_counter - 1) *
1472 NUM_OF_TYPES) + DEPENDENT_OFF) +
1473 bucket_cells_tab(((atp_counter - 1) *
1474 NUM_OF_TYPES) + SCRAP_OFF);
1475
1476
1477 l_atp_qty_net(atp_counter) := total_supply - committed_demand;
1478
1479 END LOOP;
1480
1481 msc_atp_proc.atp_consume(l_atp_qty_net, g_num_of_buckets);
1482
1483 FOR atp_counter IN 1..g_num_of_buckets LOOP
1484 add_to_plan(atp_counter, ATP_OFF, l_atp_qty_net(atp_counter));
1485 END LOOP;
1486
1487 END IF;
1488
1489 FOR a IN 1..g_num_of_buckets LOOP
1490 bkt_data.qty1(a) :=
1491 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + SALES_OFF);
1492 bkt_data.qty2(a) :=
1493 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + FORECAST_OFF);
1494 bkt_data.qty3(a) :=
1495 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + PROD_FORECAST_OFF);
1496 bkt_data.qty4(a) :=
1497 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + DEPENDENT_OFF);
1498 bkt_data.qty5(a) :=
1499 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + SCRAP_OFF);
1500 bkt_data.qty6(a) :=
1501 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + PB_DEMAND_OFF);
1502 bkt_data.qty7(a) :=
1503 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + OTHER_OFF);
1504 bkt_data.qty8(a) :=
1505 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + GROSS_OFF);
1506 bkt_data.qty9(a) :=
1507 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + WIP_OFF);
1508 bkt_data.qty10(a) :=
1509 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + PO_OFF);
1510 bkt_data.qty11(a) :=
1511 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + REQ_OFF);
1512 bkt_data.qty12(a) :=
1513 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + TRANSIT_OFF);
1514 bkt_data.qty13(a) :=
1515 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + RECEIVING_OFF);
1516 bkt_data.qty14(a) :=
1517 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + PLANNED_OFF);
1518 bkt_data.qty15(a) :=
1519 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + PB_SUPPLY_OFF);
1520 bkt_data.qty16(a) :=
1521 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + SUPPLY_OFF);
1522 bkt_data.qty17(a) :=
1523 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + ON_HAND_OFF);
1524 bkt_data.qty18(a) :=
1525 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + PAB_OFF);
1526 bkt_data.qty19(a) :=
1527 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + SS_OFF);
1528 bkt_data.qty20(a) :=
1529 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + ATP_OFF);
1530 bkt_data.qty21(a) :=
1531 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + CURRENT_S_OFF);
1532 bkt_data.qty22(a) :=
1533 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + POH_OFF);
1534 bkt_data.qty23(a) :=
1535 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + EXP_LOT_OFF);
1536 bkt_data.qty24(a) :=
1537 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + SSUNC_OFF);
1538 bkt_data.qty25(a) :=
1539 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + MIN_INV_LVL_OFF);
1540 bkt_data.qty26(a) :=
1541 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + MAX_INV_LVL_OFF);
1542 bkt_data.qty27(a) :=
1543 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + SS_DOS_OFF);
1544 bkt_data.qty28(a) :=
1545 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + SS_VAL_OFF);
1546 bkt_data.qty29(a) :=
1547 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + SSUNC_DOS_OFF);
1548 bkt_data.qty30(a) :=
1549 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + SSUNC_VAL_OFF);
1550 bkt_data.qty31(a) :=
1551 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + USS_OFF);
1552 bkt_data.qty32(a) :=
1553 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + USS_DOS_OFF);
1554 bkt_data.qty33(a) :=
1555 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + USS_VAL_OFF);
1556 bkt_data.qty34(a) :=
1557 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + TARGET_SER_OFF);
1558 bkt_data.qty35(a) :=
1559 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + ACHIEVED_SER_OFF);
1560 bkt_data.qty36(a) :=
1561 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + NON_POOL_SS_OFF);
1562 bkt_data.qty37(a) :=
1563 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + MANF_VARI_OFF);
1564 bkt_data.qty38(a) :=
1565 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + PURC_VARI_OFF);
1566 bkt_data.qty39(a) :=
1567 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + TRAN_VARI_OFF);
1568 bkt_data.qty40(a) :=
1569 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + DMND_VARI_OFF);
1570 bkt_data.qty41(a) :=
1571 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + MAD_OFF);
1572 bkt_data.qty42(a) :=
1573 bucket_cells_tab(NUM_OF_TYPES * (a - 1) + MAPE_OFF);
1574
1575 END LOOP;
1576
1577 FORALL a in 1..nvl(bkt_data.qty1.last,0)
1578 INSERT INTO msc_material_plans(
1579 query_id,
1580 organization_id,
1581 sr_instance_id,
1582 plan_id,
1583 plan_organization_id,
1584 plan_instance_id,
1585 inventory_item_id,
1586 horizontal_plan_type,
1587 horizontal_plan_type_text,
1588 bucket_type,
1589 bucket_date,
1590 last_update_date,
1591 last_updated_by,
1592 creation_date,
1593 created_by,
1594 quantity1, -- SALES_OFF
1595 quantity2, -- FORECAST_OFF
1596 quantity3, -- PROD_FORECAST
1597 quantity4, -- DEPENDENT_OFF
1598 quantity5, -- SCRAP_OFF
1599 quantity6, -- PB_DEMAND_OFF CONSTANT INTEGER := 5
1600 quantity7, -- OTHER_OFF CONSTANT INTEGER := 6
1601 quantity8, -- GROSS_OFF CONSTANT INTEGER := 7
1602 quantity9, -- WIP_OFF CONSTANT INTEGER := 8
1603 quantity10, -- PO_OFF CONSTANT INTEGER := 9
1604 quantity11, -- REQ_OFF CONSTANT INTEGER := 10
1605 quantity12, -- TRANSIT_OFF CONSTANT INTEGER := 11
1606 quantity13, -- RECEIVING_OFF_OFF CONSTANT INTEGER := 12
1607 quantity14, -- PLANEED_OFF_OFF CONSTANT INTEGER := 13
1608 quantity15, -- PB_SUPPLY_OFF CONSTANT INTEGER := 14
1609 quantity16, -- SUPPLY_OFF CONSTANT INTEGER := 15
1610 quantity17, -- ON_HAND_OFF CONSTANT INTEGER := 16
1611 quantity18, -- PAB_OFF CONSTANT INTEGER := 17
1612 quantity19, -- SS_OFF CONSTANT INTEGER := 18
1613 quantity20, -- ATP_OFF CONSTANT INTEGER := 19
1614 quantity21, -- CURRENT_S_OFF CONSTANT INTEGER := 20
1615 quantity22, -- POH_OFF CONSTANT INTEGER := 21
1616 quantity23, -- EXP_LOT_OFF CONSTANT INTEGER := 22
1617 quantity24, -- SSUNC_OFF CONSTANT INTEGER := 24
1618 quantity25, -- min_inv_lvl_off CONSTANT INTEGER := 25
1619 quantity26, -- max_inv_lvl_off CONSTANT INTEGER := 26
1620 quantity27, -- SS_DOS_OFF CONSTANT INTEGER := 27
1621 quantity28, -- SS_VAL_OFF CONSTANT INTEGER := 28
1622 quantity29, -- SSUNC_DOS_OFF CONSTANT INTEGER := 29
1623 quantity30, -- SSUNC_VAL_OFF CONSTANT INTEGER := 30
1624 quantity31, -- USS_OFF CONSTANT INTEGER := 31
1625 quantity32, -- USS_DOS_OFF CONSTANT INTEGER := 32
1626 quantity33, -- USS_VAL_OFF CONSTANT INTEGER := 33
1627 quantity34, -- TAGET_OFF
1628 quantity35,
1629 quantity36, -- Non Pool
1630 quantity37, -- Manf Vari
1631 quantity38,
1632 quantity39,
1633 quantity40,
1634 quantity41,
1635 quantity42)
1636 VALUES (
1637 arg_query_id,
1638 p_org_id,
1639 p_inst_id,
1640 arg_plan_id,
1641 arg_plan_organization_id,
1642 arg_plan_instance_id,
1643 p_item_id,
1644 1,
1645 'HORIZONTAL PLAN',
1646 arg_bucket_type,
1647 var_dates(a),
1648 SYSDATE,
1649 -1,
1650 SYSDATE,
1651 -1,
1652 bkt_data.qty1(a),
1653 bkt_data.qty2(a),
1654 bkt_data.qty3(a),
1655 bkt_data.qty4(a),
1656 bkt_data.qty5(a),
1657 bkt_data.qty6(a),
1658 bkt_data.qty7(a),
1659 bkt_data.qty8(a),
1660 bkt_data.qty9(a),
1661 bkt_data.qty10(a),
1662 bkt_data.qty11(a),
1663 bkt_data.qty12(a),
1664 bkt_data.qty13(a),
1665 bkt_data.qty14(a),
1666 bkt_data.qty15(a),
1667 bkt_data.qty16(a),
1668 bkt_data.qty17(a),
1669 bkt_data.qty18(a),
1670 bkt_data.qty19(a),
1671 bkt_data.qty20(a),
1672 bkt_data.qty21(a),
1673 bkt_data.qty22(a),
1674 bkt_data.qty23(a),
1675 bkt_data.qty24(a),
1676 bkt_data.qty25(a),
1677 bkt_data.qty26(a),
1678 bkt_data.qty27(a),
1679 bkt_data.qty28(a),
1680 bkt_data.qty29(a),
1681 bkt_data.qty30(a),
1682 bkt_data.qty31(a),
1683 bkt_data.qty32(a),
1684 bkt_data.qty33(a),
1685 bkt_data.qty34(a),
1686 bkt_data.qty35(a),
1687 bkt_data.qty36(a),
1688 bkt_data.qty37(a),
1689 bkt_data.qty38(a),
1690 bkt_data.qty39(a),
1691 bkt_data.qty40(a),
1692 bkt_data.qty41(a),
1693 bkt_data.qty42(a));
1694
1695 END IF; -- not enterprize view
1696
1697 IF enterprize_view or arg_ep_view_also then -- enterprise view
1698 ep_bucket_cells_tab(OTHER_OFF) :=
1699 ep_bucket_cells_tab(OTHER_OFF)+ep_bucket_cells_tab(PB_DEMAND_OFF);
1700 ep_bucket_cells_tab(GROSS_OFF) :=
1701 ep_bucket_cells_tab(SALES_OFF)+ep_bucket_cells_tab(FORECAST_OFF)+
1702 ep_bucket_cells_tab(DEPENDENT_OFF)+ep_bucket_cells_tab(SCRAP_OFF)+
1703 ep_bucket_cells_tab(PROD_FORECAST_OFF)+ep_bucket_cells_tab(OTHER_OFF);
1704 ep_bucket_cells_tab(SUPPLY_OFF):=
1705 ep_bucket_cells_tab(WIP_OFF)+ep_bucket_cells_tab(PO_OFF)+
1706 ep_bucket_cells_tab(REQ_OFF)+ep_bucket_cells_tab(TRANSIT_OFF)+
1707 ep_bucket_cells_tab(RECEIVING_OFF)+ep_bucket_cells_tab(PLANNED_OFF)+
1708 ep_bucket_cells_tab(PB_SUPPLY_OFF);
1709
1710 INSERT INTO msc_material_plans(
1711 query_id,
1712 organization_id,
1713 sr_instance_id,
1714 plan_id,
1715 plan_organization_id,
1716 plan_instance_id,
1717 inventory_item_id,
1718 horizontal_plan_type,
1719 horizontal_plan_type_text,
1720 bucket_type,
1721 bucket_date,
1722 last_update_date,
1723 last_updated_by,
1724 creation_date,
1725 created_by,
1726 quantity1, quantity2, quantity3, quantity4,
1727 quantity5, quantity6, quantity7, quantity8,
1728 quantity9, quantity10, quantity11, quantity12,
1729 quantity13, quantity14, quantity15, quantity16,
1730 quantity17, quantity18, quantity19, quantity20,
1731 quantity21, quantity22, quantity23, quantity24, quantity25,
1732 quantity26, quantity27, quantity28, quantity29,
1733 quantity30, quantity31, quantity32, quantity33, quantity34)
1734 VALUES (
1735 arg_query_id,
1736 p_org_id,
1737 p_inst_id,
1738 arg_plan_id,
1739 arg_plan_organization_id,
1740 arg_plan_instance_id,
1741 p_item_id,
1742 10,
1743 'ENTERPRIZE_VIEW',
1744 arg_bucket_type,
1745 sysdate,
1746 SYSDATE,
1747 -1,
1748 SYSDATE,
1749 -1,
1750 ep_bucket_cells_tab(0),
1751 ep_bucket_cells_tab(1),
1752 ep_bucket_cells_tab(2),
1753 ep_bucket_cells_tab(3),
1754 ep_bucket_cells_tab(4),
1755 ep_bucket_cells_tab(5),
1756 ep_bucket_cells_tab(6),
1757 ep_bucket_cells_tab(7),
1758 ep_bucket_cells_tab(8),
1759 ep_bucket_cells_tab(9),
1760 ep_bucket_cells_tab(10),
1761 ep_bucket_cells_tab(11),
1762 ep_bucket_cells_tab(12),
1763 ep_bucket_cells_tab(13),
1764 ep_bucket_cells_tab(14),
1765 ep_bucket_cells_tab(15),
1766 ep_bucket_cells_tab(16),
1767 ep_bucket_cells_tab(17),
1768 ep_bucket_cells_tab(18),
1769 ep_bucket_cells_tab(19),
1770 ep_bucket_cells_tab(20),
1771 ep_bucket_cells_tab(21),
1772 ep_bucket_cells_tab(22),
1773 ep_bucket_cells_tab(23),
1774 ep_bucket_cells_tab(24),
1775 ep_bucket_cells_tab(25),
1776 ep_bucket_cells_tab(26),
1777 ep_bucket_cells_tab(27),
1778 ep_bucket_cells_tab(28),
1779 ep_bucket_cells_tab(29),
1780 ep_bucket_cells_tab(30),
1781 ep_bucket_cells_tab(31),
1782 ep_bucket_cells_tab(32),
1783 ep_bucket_cells_tab(33)
1784 );
1785
1786 END IF;
1787
1788 END flush_item_plan;
1789
1790 -- =============================================================================
1791 BEGIN
1792
1793 SELECT plan_type into l_plan_type
1794 FROM msc_plans
1795 WHERE plan_id = arg_plan_id;
1796
1797 g_error_stmt := 'Debug - populate_horizontal_plan - 10';
1798 OPEN plan_buckets;
1799 FETCH plan_buckets into l_plan_start_date, l_plan_end_date;
1800 CLOSE plan_buckets;
1801
1802
1803 open c_first_date(l_plan_start_date);
1804 fetch c_first_date into l_first_date;
1805 close c_first_date;
1806
1807 g_num_of_buckets := (l_plan_end_date + 1) - (l_plan_start_date - 1);
1808
1809 g_error_stmt := 'Debug - populate_horizontal_plan - 20';
1810 -- ---------------------------------
1811 -- Initialize the bucket cells to 0.
1812 -- ---------------------------------
1813 IF enterprize_view or arg_ep_view_also THEN
1814 FOR counter IN 0..NUM_OF_TYPES LOOP
1815 ep_bucket_cells_tab(counter) := 0;
1816 END LOOP;
1817 last_date := arg_cutoff_date;
1818 END IF;
1819 IF not (enterprize_view) THEN
1820 FOR counter IN 0..(NUM_OF_TYPES * g_num_of_buckets) LOOP
1821 bucket_cells_tab(counter) := 0;
1822 END LOOP;
1823
1824 g_error_stmt := 'Debug - populate_horizontal_plan - 30';
1825 -- --------------------
1826 -- Get the bucket dates
1827 -- --------------------
1828 OPEN bucket_dates(l_plan_start_date-1, l_plan_end_date+1);
1829 LOOP
1830 FETCH bucket_dates INTO l_bucket_date;
1831 EXIT WHEN BUCKET_DATES%NOTFOUND;
1832 l_bucket_number := l_bucket_number + 1;
1833 var_dates(l_bucket_number) := l_bucket_date;
1834 -- dbms_output.put_line(l_bucket_number || to_char(l_bucket_date));
1835 END LOOP;
1836 CLOSE bucket_dates;
1837
1838 last_date := arg_cutoff_date;
1839 END IF;
1840
1841 g_error_stmt := 'Debug - populate_horizontal_plan - 40';
1842 bucket_counter := 2;
1843 old_bucket_counter := 2;
1844 activity_rec.item_id := 0;
1845 activity_rec.org_id := 0;
1846 activity_rec.inst_id := 0;
1847 activity_rec.row_type := 0;
1848 activity_rec.offset := 0;
1849 activity_rec.new_date := sysdate;
1850 activity_rec.old_date := sysdate;
1851 activity_rec.new_quantity := 0;
1852 activity_rec.old_quantity := 0;
1853 activity_rec.DOS := 0;
1854 activity_rec.cost:=0;
1855
1856
1857 OPEN mrp_snapshot_activity;
1858 LOOP
1859 FETCH mrp_snapshot_activity INTO activity_rec;
1860
1861 /* if (activity_rec.row_type in (MAD1)) then
1862 dbms_output.put_line(activity_rec.offset || '**' ||
1863 activity_rec.new_date || ' ** ' ||
1864 activity_rec.item_id || '**' || activity_rec.org_id || '**' ||
1865 activity_rec.new_quantity || '**' || activity_rec.dos || '**' ||
1866 activity_rec.cost || '** ' || activity_rec.old_quantity);
1867
1868 end if;
1869 */ IF ((mrp_snapshot_activity%NOTFOUND) OR
1870 (activity_rec.item_id <> last_item_id) OR
1871 (activity_rec.org_id <> last_org_id) OR
1872 (activity_rec.inst_id <> last_inst_id)) AND
1873 last_item_id <> -1 THEN
1874
1875 -- --------------------------
1876 -- Need to flush the plan for
1877 -- the previous item.
1878 -- --------------------------
1879 IF prev_ss_quantity <> -1 AND
1880 NOT enterprize_view THEN
1881
1882 FOR k IN bucket_counter..g_num_of_buckets + 1 LOOP
1883 add_to_plan(k-1,
1884 SS_OFF,
1885 prev_ss_quantity);
1886 add_to_plan(k -1,
1887 SS_val_OFF,
1888 prev_ss_cost);
1889 add_to_plan(k -1,
1890 SS_dos_OFF,
1891 prev_ss_dos);
1892
1893
1894 IF prev_ssunc_q <> -1 AND
1895 NOT enterprize_view THEN
1896
1897 add_to_plan(k -1 ,
1898 SSUNC_OFF,
1899 prev_ssunc_q);
1900 add_to_plan(k -1 ,
1901 SSUNC_val_OFF,
1902 prev_ssunc_cost);
1903 add_to_plan(k -1 ,
1904 SSUNC_dos_OFF,
1905 prev_ssunc_dos);
1906
1907 END IF;
1908 END LOOP;
1909 END IF;
1910
1911 IF prev_non_pool_ss <> -1 AND
1912 NOT enterprize_view THEN
1913
1914 FOR k IN bucket_counter..g_num_of_buckets + 1 LOOP
1915 add_to_plan(k -1 ,
1916 NON_POOL_SS_OFF,
1917 prev_non_pool_ss);
1918 END LOOP;
1919 END IF;
1920
1921 IF prev_manf_vari <> -1 AND
1922 NOT enterprize_view THEN
1923
1924 FOR k IN bucket_counter..g_num_of_buckets + 1 LOOP
1925 add_to_plan(k-1,
1926 MANF_VARI_OFF,
1927 prev_manf_vari);
1928
1929 add_to_plan(k-1,
1930 PURC_VARI_OFF,
1931 prev_purc_vari);
1932 add_to_plan(k -1,
1933 TRAN_VARI_OFF,
1934 prev_tran_vari);
1935 add_to_plan(k -1,
1936 DMND_VARI_OFF,
1937 prev_dmnd_vari);
1938 END LOOP;
1939 END IF;
1940
1941
1942 IF prev_target_level <> -1 AND
1943 NOT enterprize_view THEN
1944
1945 FOR k IN bucket_counter..g_num_of_buckets + 1 LOOP
1946 add_to_plan(k-1,
1947 TARGET_SER_OFF,
1948 prev_target_level);
1949 END LOOP;
1950 END IF;
1951
1952 IF prev_achieved_level <> -1 AND
1953 NOT enterprize_view THEN
1954
1955 FOR k IN bucket_counter..g_num_of_buckets + 1 LOOP
1956 add_to_plan(k-1,
1957 ACHIEVED_SER_OFF,
1958 prev_achieved_level);
1959 END LOOP;
1960 END IF;
1961
1962
1963 IF prev_mad <> -1 AND
1964 NOT enterprize_view THEN
1965
1966 FOR k IN bucket_counter..g_num_of_buckets + 1 LOOP
1967
1968 add_to_plan(k-1,
1969 MAD_OFF,
1970 prev_mad);
1971 END LOOP;
1972 END IF;
1973
1974 IF prev_mape <> -1 AND
1975 NOT enterprize_view THEN
1976
1977
1978 FOR k IN bucket_counter..g_num_of_buckets + 1 LOOP
1979
1980 add_to_plan(k-1,
1981 MAPE_OFF,
1982 prev_mape);
1983 END LOOP;
1984 END IF;
1985
1986 IF prev_min <> -1 AND NOT enterprize_view THEN
1987 FOR k IN bucket_counter..g_num_of_buckets + 1 LOOP
1988 add_to_plan(k-1, min_inv_lvl_off, prev_min);
1989 END LOOP;
1990 END IF;
1991
1992
1993 IF prev_max <> -1 AND NOT enterprize_view THEN
1994 FOR k IN bucket_counter..g_num_of_buckets + 1 LOOP
1995 add_to_plan(k-1, max_inv_lvl_off, prev_max);
1996 END LOOP;
1997 END IF;
1998
1999
2000 IF prev_uss_q<> -1 AND
2001 NOT enterprize_view THEN
2002
2003 FOR k IN bucket_counter..g_num_of_buckets + 1 LOOP
2004 add_to_plan(k-1,
2005 uSS_OFF,
2006 prev_uss_q);
2007 add_to_plan(k -1,
2008 uSS_val_OFF,
2009 prev_uss_cost);
2010 add_to_plan(k -1,
2011 uSS_dos_OFF,
2012 0);
2013 END LOOP;
2014 END IF;
2015
2016 IF prev_uss_dos<> -1 AND
2017 NOT enterprize_view THEN
2018
2019 FOR k IN bucket_counter..g_num_of_buckets + 1 LOOP
2020 add_to_plan(k-1,
2021 uSS_OFF,
2022 0);
2023 add_to_plan(k -1,
2024 uSS_val_OFF,
2025 0);
2026 add_to_plan(k -1,
2027 uSS_dos_OFF,
2028 prev_uss_dos);
2029 END LOOP;
2030 END IF;
2031 flush_item_plan(last_item_id,
2032 last_org_id,
2033 last_inst_id);
2034
2035 bucket_counter := 2;
2036 old_bucket_counter := 2;
2037 reset_prev_ss;
2038 prev_ssunc_q := -1;
2039 ssunc_q := -1;
2040 prev_ssunc_dos:= -1;
2041 ssunc_dos := -1;
2042 uss_q := -1;
2043 prev_uss_q := -1;
2044 uss_dos := -1;
2045 prev_uss_dos := -1;
2046 ssunc_cost := -1;
2047 prev_ssunc_cost := -1;
2048 uss_cost := -1;
2049 prev_uss_cost := -1;
2050
2051 prev_non_pool_ss := -1;
2052 non_pool_ss := -1;
2053
2054 prev_manf_vari := -1;
2055 prev_purc_vari := -1;
2056 prev_tran_vari := -1;
2057 prev_dmnd_vari := -1;
2058
2059 prev_target_level := -1;
2060 prev_achieved_level := -1;
2061 prev_mad := -1;
2062 prev_mape := -1;
2063 prev_min := -1;
2064 prev_max := -1;
2065
2066 target_level := -1;
2067 achieved_level := -1;
2068 mad := -1;
2069 mape := -1;
2070 min_lvl := -1;
2071 max_lvl := -1;
2072
2073
2074 manf_vari := -1;
2075 purc_vari := -1;
2076 tran_vari := -1;
2077 dmnd_vari := -1;
2078
2079
2080 -- ------------------------------------
2081 -- Initialize the bucket cells to 0.
2082 -- ------------------------------------
2083 IF enterprize_view or arg_ep_view_also THEN
2084 FOR counter IN 0..NUM_OF_TYPES LOOP
2085 ep_bucket_cells_tab(counter) := 0;
2086 END LOOP;
2087 END IF;
2088 IF not (enterprize_view) then
2089 FOR counter IN 0..(NUM_OF_TYPES * g_num_of_buckets) LOOP
2090 bucket_cells_tab(counter) := 0;
2091 END LOOP;
2092 END IF;
2093 END IF; -- end of activity_rec.item_id <> last_item_id
2094
2095 EXIT WHEN mrp_snapshot_activity%NOTFOUND;
2096
2097 IF enterprize_view or arg_ep_view_also THEN
2098 IF activity_rec.row_type IN (WIP, PO, REQ, TRANSIT,
2099 RECEIVING, PB_SUPPLY) THEN
2100 add_to_plan(CURRENT_S_OFF + 1, 0, activity_rec.old_quantity,true);
2101 END IF;
2102 add_to_plan(activity_rec.offset + 1 , 0,
2103 activity_rec.new_quantity,true);
2104 END IF;
2105 IF not(enterprize_view) THEN
2106
2107 IF activity_rec.row_type = SS THEN
2108
2109 -- --------------------------
2110 -- Got a safety stock record.
2111 -- --------------------------
2112 IF (bucket_counter <= g_num_of_buckets AND
2113 activity_rec.new_date < var_dates(bucket_counter)) THEN
2114 -- ----------------------------------
2115 -- This safety stock quantity applies
2116 -- to the current bucket.
2117 -- ----------------------------------
2118 init_prev_ss_qty;
2119 END IF;
2120 END IF;
2121 IF activity_rec.row_type = SS_UNC THEN
2122 -- --------------------------
2123 -- Got a safety stock record.
2124 -- --------------------------
2125 ssunc_q := activity_rec.new_quantity;
2126 ssunc_dos := activity_rec.dos;
2127 ssunc_date := activity_rec.new_date;
2128 ssunc_cost := activity_rec.cost;
2129 non_pool_ss := activity_rec.old_quantity;
2130
2131 IF (bucket_counter <= g_num_of_buckets AND
2132 activity_rec.new_date < var_dates(bucket_counter)) THEN
2133 -- ----------------------------------
2134 -- This safety stock quantity applies
2135 -- to the current bucket.
2136 -- ----------------------------------
2137 prev_ssunc_q := activity_rec.new_quantity;
2138 prev_ssunc_dos := activity_rec.dos;
2139 prev_ssunc_date := activity_rec.new_date;
2140 prev_ssunc_cost := activity_rec.cost;
2141 prev_non_pool_ss := activity_rec.old_quantity;
2142 END IF;
2143 END IF;
2144
2145 IF activity_rec.row_type = MANU_VARI THEN
2146 -- --------------------------
2147 -- Got a safety stock record.
2148 -- --------------------------
2149 manf_vari := activity_rec.new_quantity;
2150 purc_vari := activity_rec.old_quantity;
2151 tran_vari := activity_rec.DOS;
2152 dmnd_vari := activity_rec.cost;
2153 vari_date := activity_rec.new_date;
2154
2155 IF (bucket_counter <= g_num_of_buckets AND
2156 activity_rec.new_date < var_dates(bucket_counter)) THEN
2157 -- ----------------------------------
2158 -- This safety stock quantity applies
2159 -- to the current bucket.
2160 -- ----------------------------------
2161
2162 prev_manf_vari := activity_rec.new_quantity;
2163 prev_purc_vari := activity_rec.old_quantity;
2164 prev_tran_vari := activity_rec.dos;
2165 prev_dmnd_vari := activity_rec.cost;
2166 prev_vari_date := activity_rec.new_date;
2167
2168 END IF;
2169 END IF;
2170
2171 IF activity_rec.row_type = MAD1 THEN
2172 mad := activity_rec.new_quantity;
2173 mape := activity_rec.old_quantity;
2174
2175 IF (bucket_counter <= g_num_of_buckets AND
2176 activity_rec.new_date < var_dates(bucket_counter)) THEN
2177
2178 prev_mad := activity_rec.new_quantity;
2179 prev_mape := activity_rec.old_quantity;
2180 END IF;
2181 END IF;
2182
2183 IF activity_rec.row_type = min_inv_lvl THEN
2184 min_lvl := activity_rec.new_quantity;
2185 IF (bucket_counter <= g_num_of_buckets AND
2186 activity_rec.new_date < var_dates(bucket_counter)) THEN
2187 prev_min := activity_rec.new_quantity;
2188 END IF;
2189 END IF;
2190
2191 IF activity_rec.row_type = max_inv_lvl THEN
2192 max_lvl := activity_rec.new_quantity;
2193 IF (bucket_counter <= g_num_of_buckets AND
2194 activity_rec.new_date < var_dates(bucket_counter)) THEN
2195 prev_max := activity_rec.new_quantity;
2196 END IF;
2197 END IF;
2198
2199
2200
2201 IF activity_rec.row_type = TARGET_SER_LVL THEN
2202 -- --------------------------
2203 -- Got a safety stock record.
2204 -- --------------------------
2205 target_level := activity_rec.new_quantity;
2206 -- dbms_output.put_line (' target value ' || target_level );
2207 -- dbms_output.put_line(' bkt counter ' || bucket_counter || 'no of bkts '|| g_num_of_buckets);
2208 -- dbms_output.put_line(' var dates ' || var_dates(bucket_counter));
2209
2210 IF (bucket_counter <= g_num_of_buckets AND
2211 activity_rec.new_date < var_dates(bucket_counter)) THEN
2212 -- ----------------------------------
2213 -- This safety stock quantity applies
2214 -- to the current bucket.
2215 -- ----------------------------------
2216
2217 prev_target_level := activity_rec.new_quantity;
2218 END IF;
2219 END IF;
2220
2221
2222 IF activity_rec.row_type = ACHIEVED_SER_LVL THEN
2223 -- --------------------------
2224 -- Got a safety stock record.
2225 -- --------------------------
2226 achieved_level := activity_rec.new_quantity;
2227 -- dbms_output.put_line (' target value ' || achieved_level );
2228 -- dbms_output.put_line(' bkt counter ' || bucket_counter || 'no of bkts '|| g_num_of_buckets);
2229 -- dbms_output.put_line(' var dates ' || var_dates(bucket_counter));
2230
2231 IF (bucket_counter <= g_num_of_buckets AND
2232 activity_rec.new_date < var_dates(bucket_counter)) THEN
2233 -- ----------------------------------
2234 -- This safety stock quantity applies
2235 -- to the current bucket.
2236 -- ----------------------------------
2237
2238 prev_achieved_level := activity_rec.new_quantity;
2239 END IF;
2240 END IF;
2241
2242
2243 IF activity_rec.row_type = USS
2244 and activity_rec.new_quantity is null THEN
2245 -- --------------------------
2246 -- Got a safety stock record.
2247 -- --------------------------
2248 uss_dos := activity_rec.dos;
2249 uss_date := activity_rec.new_date;
2250 uss_cost := activity_rec.cost;
2251 uss_q := -1;
2252
2253 IF activity_rec.new_date < var_dates(bucket_counter) THEN
2254 -- ----------------------------------
2255 -- This safety stock quantity applies
2256 -- to the current bucket.
2257 -- ----------------------------------
2258 prev_uss_dos := activity_rec.dos;
2259 prev_uss_date := activity_rec.new_date;
2260 prev_uss_cost:= activity_rec.cost;
2261 prev_uss_q := -1;
2262 END IF;
2263 END IF;
2264
2265 IF activity_rec.row_type = USS
2266 and activity_rec.new_quantity is not null THEN
2267 -- --------------------------
2268 -- Got a safety stock record.
2269 -- --------------------------
2270 uss_q := activity_rec.new_quantity;
2271 uss_date := activity_rec.new_date;
2272 uss_cost := activity_rec.cost;
2273 uss_dos := -1;
2274
2275 IF activity_rec.new_date < var_dates(bucket_counter) THEN
2276 -- dbms_output.put_line('activity_rec.new_date ' || activity_rec.new_date ||
2277 -- ' var_dates(bucket_counter) ' || var_dates(bucket_counter) ||
2278 -- ' bucket counter ' || bucket_counter);
2279 -- ----------------------------------
2280 -- This safety stock quantity applies
2281 -- to the current bucket.
2282 -- ----------------------------------
2283 prev_uss_q := activity_rec.new_quantity;
2284 prev_uss_date := activity_rec.new_date;
2285 prev_uss_cost := activity_rec.cost;
2286 prev_uss_dos := -1;
2287 END IF;
2288 END IF;
2289
2290 IF (bucket_counter <= g_num_of_buckets AND
2291 activity_rec.new_date >= var_dates(bucket_counter)) THEN
2292 -- -------------------------------------------------------
2293 -- We got an activity falls after the current bucket. So we
2294 -- will move the bucket counter forward until we find the
2295 -- bucket where this activity falls. Note that we should
2296 -- not advance the counter bejond g_num_of_buckets.
2297 -- --------------------------------------------------------
2298 -- dbms_output.put_line( 'off ' || activity_rec.offset ||
2299 -- 'num buckets ' || g_num_of_buckets ||
2300 -- 'var_dates date ' || var_dates(bucket_counter) ||
2301 -- 'activity_rec date ' || activity_rec.new_date);
2302
2303 WHILE (bucket_counter <= g_num_of_buckets AND
2304 activity_rec.new_date >= var_dates(bucket_counter)) LOOP
2305 -- dbms_output.put_line('in loop - ' || var_dates(bucket_counter));
2306 -- if (bucket_counter > g_num_of_buckets - 50) then
2307 -- dbms_output.put_line( bucket_counter || ' ' ||
2308 -- to_char(var_dates(bucket_counter)));
2309 -- end if;
2310 -- -----------------------------------------------------
2311 -- If the variable last_ss_quantity is not -1 then there
2312 -- is a safety stock entry that we need to add for the
2313 -- current bucket before we move the bucket counter
2314 -- forward.
2315 -- -----------------------------------------------------
2316 IF prev_ss_quantity <> -1 THEN
2317 add_to_plan(bucket_counter -1,
2318 SS_OFF,
2319 prev_ss_quantity);
2320 add_to_plan(bucket_counter -1,
2321 SS_val_OFF,
2322 prev_ss_cost);
2323 add_to_plan(bucket_counter -1,
2324 ss_dos_off,
2325 prev_ss_dos);
2326 END IF;
2327
2328 IF prev_ssunc_q <> -1 THEN
2329
2330 add_to_plan(bucket_counter -1,
2331 SSunc_OFF,
2332 prev_ssunc_q);
2333 add_to_plan(bucket_counter -1,
2334 SSunc_val_OFF,
2335 prev_ssunc_cost);
2336 add_to_plan(bucket_counter -1,
2337 ssunc_dos_off,
2338 prev_ssunc_dos);
2339 add_to_plan(bucket_counter -1,
2340 NON_POOL_SS_OFF,
2341 prev_non_pool_ss);
2342
2343 ELSIF prev_non_pool_ss <> -1 THEN
2344 add_to_plan(bucket_counter -1,
2345 NON_POOL_SS_OFF,
2346 prev_non_pool_ss);
2347 END IF;
2348
2349 IF prev_uss_q <> -1 THEN
2350
2351 add_to_plan(bucket_counter -1,
2352 uSS_OFF,
2353 prev_uss_q);
2354 add_to_plan(bucket_counter -1,
2355 uSS_val_OFF,
2356 prev_uss_cost);
2357 add_to_plan(bucket_counter -1,
2358 uss_dos_off,
2359 0);
2360 END IF;
2361
2362 IF prev_mad <> -1 THEN
2363 add_to_plan(bucket_counter -1,
2364 MAD_OFF,
2365 prev_mad);
2366 END IF;
2367
2368 IF prev_mape <> -1 THEN
2369
2370 add_to_plan(bucket_counter -1,
2371 MAPE_OFF,
2372 prev_mape);
2373 END IF;
2374
2375 IF prev_min <> -1 THEN
2376 add_to_plan(bucket_counter -1, min_inv_lvl_off, prev_min);
2377 END IF;
2378
2379 IF prev_max <> -1 THEN
2380 add_to_plan(bucket_counter -1, max_inv_lvl_off, prev_max);
2381 END IF;
2382
2383
2384 IF prev_target_level <> -1 THEN
2385
2386
2387 add_to_plan(bucket_counter -1,
2388 TARGET_SER_OFF,
2389 prev_target_level);
2390 END IF;
2391
2392 IF prev_achieved_level <> -1 THEN
2393
2394 add_to_plan(bucket_counter -1,
2395 ACHIEVED_SER_OFF,
2396 prev_achieved_level);
2397 END IF;
2398
2399 IF prev_manf_vari <> -1 THEN
2400
2401
2402 add_to_plan(bucket_counter -1,
2403 MANF_VARI_OFF,
2404 prev_manf_vari);
2405
2406 add_to_plan(bucket_counter -1,
2407 PURC_VARI_OFF,
2408 prev_purc_vari);
2409
2410 add_to_plan(bucket_counter -1,
2411 TRAN_VARI_OFF,
2412 prev_tran_vari);
2413
2414 add_to_plan(bucket_counter -1,
2415 DMND_VARI_OFF,
2416 prev_dmnd_vari);
2417 END IF;
2418
2419 IF prev_uss_dos <> -1 THEN
2420
2421 add_to_plan(bucket_counter -1,
2422 uSS_OFF,
2423 0);
2424 add_to_plan(bucket_counter -1,
2425 uSS_val_OFF,
2426 0);
2427 add_to_plan(bucket_counter -1,
2428 uss_dos_off,
2429 prev_uss_dos);
2430 END IF;
2431
2432 bucket_counter := bucket_counter + 1;
2433
2434 END LOOP;
2435
2436 IF activity_rec.row_type = SS then
2437 init_prev_ss_qty;
2438 END IF;
2439
2440 prev_ssunc_q := ssunc_q;
2441 prev_ssunc_dos := ssunc_dos;
2442 prev_ssunc_date := ssunc_date;
2443 prev_uss_q := uss_q;
2444 prev_uss_dos := uss_dos;
2445 prev_uss_date := uss_date;
2446 prev_ssunc_cost := ssunc_cost;
2447 prev_uss_cost := uss_cost;
2448
2449 prev_manf_vari := manf_vari;
2450 prev_purc_vari := purc_vari;
2451 prev_tran_vari := tran_vari;
2452 prev_dmnd_vari := dmnd_vari;
2453
2454 prev_target_level := target_level;
2455 prev_achieved_level := achieved_level;
2456
2457 prev_mad := mad;
2458 prev_mape := mape;
2459
2460 prev_min := min_lvl;
2461 prev_max := max_lvl;
2462
2463 prev_vari_date := vari_date;
2464
2465 END IF;
2466
2467 -- ---------------------------------------------------------
2468 -- Add the retrieved activity to the plan if it falls in the
2469 -- current bucket and it is not a safety stock entry.
2470 -- ---------------------------------------------------------
2471
2472 IF l_plan_type <> SRO_PLAN THEN
2473 IF (bucket_counter <= g_num_of_buckets +1 AND -- bug6757932
2474 activity_rec.new_date < var_dates(bucket_counter)) AND
2475 ( activity_rec.row_type not in (SS,SS_UNC)) THEN
2476 add_to_plan(bucket_counter - 1,
2477 activity_rec.offset,
2478 activity_rec.new_quantity);
2479 END IF;
2480 ELSE
2481 IF (bucket_counter <= g_num_of_buckets AND
2482 activity_rec.new_date < var_dates(bucket_counter)) THEN
2483 if (activity_rec.row_type <> USS and activity_rec.row_type <> SS_UNC and activity_rec.row_type <> SS and
2484 activity_rec.row_type <> MANU_VARI and activity_rec.row_type <> TARGET_SER_LVL and
2485 activity_rec.row_type <> ACHIEVED_SER_LVL) then
2486 add_to_plan(bucket_counter -1,
2487 activity_rec.offset,
2488 activity_rec.new_quantity);
2489 -- elsif activity_rec.row_type = USS then
2490 -- if (activity_rec.new_quantity is null) then
2491 -- add_to_plan(bucket_counter -1,
2492 -- uss_dos_off,
2493 -- activity_rec.dos);
2494 -- else
2495 -- add_to_plan(bucket_counter -1,
2496 -- uss_off,
2497 -- activity_rec.new_quantity);
2498 -- open standard_cost(
2499 -- last_item_id,
2500 -- last_inst_id,
2501 -- last_org_id,
2502 -- arg_plan_id);
2503 -- fetch standard_cost into l_standard_cost;
2504 -- close standard_cost;
2505 -- add_to_plan(bucket_counter-1,
2506 -- USS_val_OFF,
2507 -- activity_rec.new_quantity*l_standard_cost);
2508 --
2509 -- end if;
2510 end if;
2511 END IF;
2512 END IF;
2513
2514 -- -------------------------------------
2515 -- Add to the current schedule receipts.
2516 -- -------------------------------------
2517 IF activity_rec.row_type IN (WIP, PO, REQ, TRANSIT,
2518 RECEIVING, PB_SUPPLY) THEN
2519 WHILE activity_rec.old_date >= var_dates(old_bucket_counter) AND
2520 old_bucket_counter <= g_num_of_buckets LOOP
2521 -- ----------
2522 -- move back.
2523 -- ----------
2524 old_bucket_counter := old_bucket_counter + 1;
2525
2526 END LOOP;
2527
2528 WHILE activity_rec.old_date < var_dates(old_bucket_counter - 1) AND
2529 old_bucket_counter > 2 LOOP
2530 -- -------------
2531 -- move forward.
2532 -- -------------
2533 old_bucket_counter := old_bucket_counter - 1;
2534 END LOOP;
2535 IF activity_rec.old_date < var_dates(old_bucket_counter) THEN
2536 add_to_plan(old_bucket_counter - 1,
2537 CURRENT_S_OFF,
2538 activity_rec.old_quantity);
2539 END IF;
2540 END IF;
2541 END IF; -- if not enterprise view
2542 last_item_id := activity_rec.item_id;
2543 last_org_id := activity_rec.org_id;
2544 last_inst_id := activity_rec.inst_id;
2545 END LOOP;
2546
2547 g_error_stmt := 'Debug - populate_horizontal_plan - 50';
2548 CLOSE mrp_snapshot_activity;
2549
2550 DECLARE
2551 l_customer_prg varchar2(500);
2552 l_statement varchar2(1000);
2553
2554 BEGIN
2555 l_customer_prg := FND_PROFILE.value('MSC_HP_EXTENSION_PROGRAM');
2556 if l_customer_prg is not null then
2557 l_statement :=
2558 ' begin ' || l_customer_prg|| '(' || arg_query_id || '); end;';
2559 EXECUTE IMMEDIATE l_statement;
2560 end if;
2561 EXCEPTION WHEN others then
2562 null;
2563 END ;
2564
2565 EXCEPTION
2566
2567 WHEN OTHERS THEN
2568 null;
2569 --dbms_output.put_line(g_error_stmt);
2570 raise;
2571
2572 END populate_horizontal_plan;
2573
2574 PROCEDURE query_list(p_agg_hzp NUMBER,
2575 p_query_id IN NUMBER,
2576 p_plan_id IN NUMBER,
2577 p_instance_id NUMBER,
2578 p_org_list IN VARCHAR2,
2579 p_pf IN NUMBER, -- this org, all org, from/to org,.....
2580 p_item_list IN VARCHAR2,
2581 p_category_set IN NUMBER DEFAULT NULL,
2582 p_category_name IN VARCHAR2 DEFAULT NULL,
2583 p_display_pf_details IN BOOLEAN DEFAULT true) IS
2584
2585 sql_stmt VARCHAR2(5000);
2586 sql_stmt1 VARCHAR2(5000);
2587 p_org_id column_number;
2588 p_inst_id column_number;
2589 p_item_id column_number;
2590 p_org_seq column_number;
2591 a number :=0;
2592 b number;
2593 l_len number;
2594 one_record varchar2(100);
2595 startPos number;
2596 endPos number;
2597 p_all_org_string varchar2(80) :='All Orgs for this Plan';
2598 p_total_member_string varchar2(80) :='Member Total for ';
2599 p_node_type number;
2600 v_cat_name varchar2(200);
2601 v_org_id number;
2602 v_org_seq number;
2603 v_inst_id number;
2604 v_org_id2 number;
2605 v_inst_id2 number;
2606 v_item_id number;
2607 orig_org_count number;
2608 v_org_exist boolean :=false;
2609
2610 v_isProductFamily number :=0;
2611 cursor isProductFamily is
2612 select 1
2613 from msc_system_items
2614 where plan_id = p_plan_id
2615 and organization_id = v_org_id
2616 and sr_instance_id = v_inst_id
2617 and inventory_item_id = v_item_id
2618 and bom_item_type in (2,5);
2619
2620 cursor isProductFamily_no_org is
2621 select 1
2622 from msc_system_items
2623 where plan_id = p_plan_id
2624 and inventory_item_id = v_item_id
2625 and bom_item_type in (2,5);
2626
2627 cursor org_list_c IS
2628 select mpt.object_type, mpt.source_type, mpt.sequence_id
2629 from msc_pq_types mpt,
2630 msc_system_items msi
2631 where mpt.query_id = p_pf
2632 and mpt.object_type = msi.organization_id
2633 and mpt.source_type = msi.sr_instance_id
2634 and msi.plan_id = p_plan_id
2635 and msi.inventory_item_id = v_item_id;
2636
2637 cursor from_to_org_c IS
2638 select source_organization_id, sr_instance_id2
2639 from msc_item_sourcing
2640 where plan_id = p_plan_id
2641 and organization_id = v_org_id
2642 and inventory_item_id = v_item_id
2643 and sr_instance_id = v_inst_id
2644 and (source_organization_id <> organization_id or
2645 sr_instance_id2 <> sr_instance_id)
2646 and source_organization_id <> -1
2647 union select organization_id, sr_instance_id
2648 from msc_item_sourcing
2649 where plan_id = p_plan_id
2650 and source_organization_id = v_org_id
2651 and inventory_item_id = v_item_id
2652 and sr_instance_id2 = v_inst_id
2653 and (source_organization_id <> organization_id or
2654 sr_instance_id2 <> sr_instance_id)
2655 and organization_id <> -1;
2656
2657 p_item_limit number :=
2658 nvl(fnd_profile.value('MSC_HP_ITEM_LIMIT'),30);
2659 p_org_exist boolean;
2660
2661 BEGIN
2662
2663 if p_org_list is null or p_pf in (-1,0) then -- show all orgs
2664 p_org_id(1) :=-1;
2665 p_inst_id(1) := -1;
2666 p_org_seq(1) := -1000;
2667 elsif nvl(p_pf,-3) in (-2,-3) or p_pf > 0 then -- this org or Ship From/To org
2668 l_len := length(p_org_list);
2669 WHILE l_len > 0 LOOP
2670 a := a+1;
2671 one_record := substr(p_org_list,instr(p_org_list,'(',1,a)+1,
2672 instr(p_org_list,')',1,a)-instr(p_org_list,'(',1,a)-1);
2673
2674 p_inst_id(a) := to_number(substr(one_record,1,instr(one_record,',')-1));
2675 p_org_id(a) := to_number(substr(one_record,instr(one_record,',')+1));
2676 p_org_seq(a) :=1;
2677 l_len := l_len - length(one_record)-3;
2678 -- dbms_output.put_line(a||',org='||p_org_id(a));
2679 END LOOP;
2680 end if; -- p_org_list is null
2681
2682 a :=1;
2683 startPos :=1;
2684 endPos := instr(p_item_list||',', ',',1,a);
2685
2686 while endPos >0 and a <= p_item_limit loop
2687 l_len := endPos - startPos;
2688 p_item_id(a) := to_number(substr(p_item_list||',',startPos, l_len));
2689 v_item_id := p_item_id(a);
2690 a := a+1;
2691 startPos := endPos+1;
2692 endPos := instr(p_item_list||',', ',',1,a);
2693
2694 if p_pf is not null and p_pf not in (-1,-2, -3,0) then
2695 -- get org from org list
2696
2697 b := nvl(p_org_id.last,0);
2698 OPEN org_list_c;
2699 LOOP
2700 FETCH org_list_c INTO v_org_id, v_inst_id, v_org_seq;
2701 EXIT WHEN org_list_c%NOTFOUND;
2702 b := b+1;
2703 p_inst_id(b) := v_inst_id;
2704 p_org_id(b) := v_org_id;
2705 p_org_seq(b) := v_org_seq;
2706 END LOOP;
2707 CLOSE org_list_c;
2708
2709 end if;
2710 end loop;
2711
2712 if p_pf =-2 then -- Ship From/To org
2713 orig_org_count := p_org_id.count ;
2714 b := orig_org_count ;
2715 for a in 1..p_item_id.count loop
2716 v_item_id := p_item_id(a);
2717 for c in 1..orig_org_count loop
2718 v_org_id := p_org_id(c);
2719 v_inst_id := p_inst_id(c);
2720 OPEN from_to_org_c;
2721 LOOP
2722 FETCH from_to_org_c INTO v_org_id2, v_inst_id2;
2723 EXIT WHEN from_to_org_c%NOTFOUND;
2724 -- 5201957, verify if the org is not already added
2725 p_org_exist := false;
2726 for c in 1..b loop
2727 if p_org_id(c) = v_org_id2 then
2728 p_org_exist := true;
2729 exit;
2730 end if;
2731 end loop;
2732 if not(p_org_exist) then
2733 b := b +1;
2734 p_org_id(b) := v_org_id2;
2735 p_inst_id(b) := v_inst_id2;
2736 p_org_seq(b) :=1;
2737 -- dbms_output.put_line(b||','||p_org_id(b));
2738 end if;
2739 END LOOP;
2740 CLOSE from_to_org_c;
2741 end loop; --for b in 1..p_org_id.count loop
2742 end loop; -- for a in 1..p_item_id.count loop
2743 end if; -- if p_pf =-2
2744
2745 -- dbms_output.put_line(' before inserting into temp table ');
2746
2747 sql_stmt1 := 'INSERT INTO msc_form_query ( '||
2748 'query_id, '||
2749 'last_update_date, '||
2750 'last_updated_by, '||
2751 'creation_date, '||
2752 'created_by, '||
2753 'last_update_login, '||
2754 'number1, '|| -- item_id
2755 'number2, '|| -- org_id
2756 'number3, '|| -- inst_id
2757 'number4, '|| -- plan_id
2758 'number5, '|| -- displayed item_id
2759 'number6, '|| -- displayed org_id
2760 'number7, '|| -- node type
2761 'number8, '|| -- org sequence
2762 'char1, '||
2763 'char2) '||
2764 ' SELECT DISTINCT :p_query_id, '||
2765 'sysdate, '||
2766 '1, '||
2767 'sysdate, '||
2768 '1, '||
2769 '1, '||
2770 'msi.inventory_item_id, '||
2771 'msi.organization_id, '||
2772 'msi.sr_instance_id, '||
2773 'msi.plan_id, ';
2774
2775 -- dbms_output.put_line( ' org id count ' || p_org_id.count || ' item count ' || p_item_id.count);
2776
2777 for a in 1..p_org_id.count loop
2778
2779 for b in 1..p_item_id.count loop
2780 if p_item_id(b) = -1 then
2781 -- users select the category nodes
2782 MSC_ALLOCATION_PLAN.query_list(
2783 p_query_id, p_plan_id, p_org_id(a), p_inst_id(a),
2784 p_category_set, p_category_name);
2785 else
2786 v_org_id := p_org_id(a);
2787 v_inst_id := p_inst_id(a);
2788 v_item_id := p_item_id(b);
2789 v_org_seq := p_org_seq(a);
2790 --dbms_output.put_line(a||','||v_org_id||','||v_item_id||','||v_org_seq);
2791 v_isProductFamily :=0;
2792
2793 -- verify if item is Product family only if its enabled in the preferences
2794
2795 if (p_display_pf_details = true) then
2796
2797 if v_org_id = -1 then
2798 OPEN isProductFamily_no_org;
2799 FETCH isProductFamily_no_org into v_isProductFamily;
2800 CLOSE isProductFamily_no_org;
2801 else
2802 OPEN isProductFamily;
2803 FETCH isProductFamily into v_isProductFamily;
2804 CLOSE isProductFamily;
2805 end if;
2806 else
2807
2808 v_isProductFamily := 0;
2809 end if;
2810
2811 -- dbms_output.put_line( ' is pf ' || v_isProductFamily );
2812
2813 if v_isProductFamily = 1 then
2814 p_node_type := NODE_REGULAR_ITEM;
2815 -- insert the member of the product family
2816 sql_stmt := sql_stmt1 ||
2817 '-1*msi.product_family_id, '||
2818 'DECODE( :p_org_id,-1,-1,msi.organization_id), '||
2819 ' :NODE_REGULAR_ITEM, '||
2820 ' :org_seq, '||
2821 'DECODE( :p_org_id,-1,:p_all_org_string ,msi.organization_code), '||
2822 ':p_total_member_string || msc_get_name.item_name(msi.product_family_id,null,null,null) '||
2823 ' FROM msc_items_tree_v msi' ||
2824 ' WHERE msi.category_set_id = :p_category_set ' ||
2825 ' AND msi.product_family_id = :p_item_list ' ||
2826 ' AND msi.plan_id = :p_plan_id';
2827
2828 if p_org_id(a) <> -1 then
2829 sql_stmt := sql_stmt ||
2830 ' and msi.sr_instance_id = :p_inst_id '||
2831 ' and msi.organization_id = :p_org_list ';
2832 else -- item across all orgs
2833 sql_stmt := sql_stmt ||
2834 ' and -1 = :p_inst_id '||
2835 ' and -1 = :p_org_list ';
2836 end if;
2837
2838 if p_category_name is not null then
2839 sql_stmt := sql_stmt ||
2840 ' and msi.category_name = :p_category_name ';
2841 -- need to add category_name to msc_items_tree_v
2842 v_cat_name := p_category_name;
2843 else
2844 sql_stmt := sql_stmt ||
2845 ' and -1 = :p_category_name ';
2846 v_cat_name := '-1';
2847 end if;
2848
2849
2850 EXECUTE IMMEDIATE sql_stmt using p_query_id,v_org_id,p_node_type,
2851 v_org_seq, v_org_id,
2852 p_all_org_string, p_total_member_string,
2853 p_category_set, v_item_id,p_plan_id,
2854 v_inst_id,v_org_id,v_cat_name;
2855
2856 end if; -- v_isProductFamily = 1 then
2857
2858 if v_isProductFamily = 1 then
2859 p_node_type := NODE_PF_ITEM;
2860 else
2861 p_node_type := NODE_REGULAR_ITEM;
2862 end if;
2863
2864 if v_org_id = -1 then -- across all orgs
2865 if p_category_name is not null then
2866 sql_stmt := sql_stmt1 ||
2867 'msi.inventory_item_id,'||
2868 '-1, '||
2869 ' :NODE_REGULAR_ITEM, ' ||
2870 ' :org_seq, '||
2871 ' :p_all_org_string,'||
2872 'msi.item_name '||
2873 ' FROM msc_system_items msi, msc_item_categories mic' ||
2874 ' WHERE mic.organization_id = msi.organization_id ' ||
2875 'AND mic.sr_instance_id = msi.sr_instance_id ' ||
2876 'AND mic.inventory_item_id = msi.inventory_item_id ' ||
2877 'AND mic.category_set_id = :p_category_set '||
2878 'AND mic.category_name = :p_category_name '||
2879 'AND msi.inventory_item_id = :p_item_list ' ||
2880 'AND msi.plan_id = :p_plan_id ';
2881
2882 EXECUTE IMMEDIATE sql_stmt using p_query_id,p_node_type, v_org_seq,
2883 p_all_org_string,
2884 p_category_set, p_category_name,
2885 v_item_id,p_plan_id;
2886 else -- p_category_name is null
2887 sql_stmt := sql_stmt1 ||
2888 'msi.inventory_item_id,'||
2889 '-1, '||
2890 ' :NODE_REGULAR_ITEM, ' ||
2891 ' :org_seq, '||
2892 ' :p_all_org_string,'||
2893 ' msi.item_name '||
2894 ' FROM msc_system_items msi ' ||
2895 ' where msi.inventory_item_id = :p_item_list ' ||
2896 ' AND msi.organization_id <> -1 '||
2897 ' AND msi.plan_id = :p_plan_id ';
2898
2899 EXECUTE IMMEDIATE sql_stmt using p_query_id,p_node_type, v_org_seq,
2900 p_all_org_string,
2901 v_item_id,p_plan_id;
2902 end if; -- end p_category_name is not null
2903 else -- regular item
2904 sql_stmt := sql_stmt1 ||
2905 'msi.inventory_item_id, '||
2906 'msi.organization_id, '||
2907 ':NODE_REGULAR_ITEM, ' ||
2908 ' :org_seq, '||
2909 'msc_get_name.org_code(msi.organization_id,msi.sr_instance_id), '||
2910 'msi.item_name '||
2911 ' FROM msc_system_items msi ' ||
2912 ' where msi.sr_instance_id = :p_inst_id '||
2913 'and msi.organization_id = :p_org_list ' ||
2914 'AND msi.inventory_item_id = :p_item_list ' ||
2915 'AND msi.plan_id = :p_plan_id ';
2916 /*
2917 dbms_output.put_line('insert for org='||p_query_id||','||p_node_type||','||
2918 v_inst_id||','|| v_org_id||','||
2919 v_item_id||','||p_plan_id);
2920 */
2921 EXECUTE IMMEDIATE sql_stmt using p_query_id,p_node_type, v_org_seq,
2922 v_inst_id, v_org_id,
2923 v_item_id,p_plan_id;
2924
2925 if p_pf = -2 or p_pf > 0 then
2926 -- From/To org,org list should show summary row
2927
2928 sql_stmt := sql_stmt1 ||
2929 'msi.inventory_item_id, '||
2930 '-1, '||
2931 ':NODE_REGULAR_ITEM, ' ||
2932 ' :org_seq, '||
2933 ':p_all_org_string, '||
2934 'msi.item_name '||
2935 ' FROM msc_system_items msi ' ||
2936 ' where msi.sr_instance_id = :p_inst_id '||
2937 'and msi.organization_id = :p_org_list ' ||
2938 'AND msi.inventory_item_id = :p_item_list ' ||
2939 'AND msi.plan_id = :p_plan_id ';
2940
2941 EXECUTE IMMEDIATE sql_stmt using p_query_id,p_node_type,-1000,
2942 p_all_org_string, v_inst_id, v_org_id,
2943 v_item_id,p_plan_id;
2944
2945 end if; -- if p_pf = -2 then
2946 end if; -- if v_org_id = -1 then
2947
2948 if v_org_id = -1 and -- across all orgs
2949 p_agg_hzp =2 then -- need to show each org
2950 v_org_seq := 1; -- should be shown after all org
2951 if p_category_name is not null then
2952 sql_stmt := sql_stmt1 ||
2953 'msi.inventory_item_id,'||
2954 'msi.organization_id, '||
2955 ' :NODE_REGULAR_ITEM, ' ||
2956 ' :org_seq, '||
2957 'msc_get_name.org_code(msi.organization_id,msi.sr_instance_id),'||
2958 'msi.item_name '||
2959 ' FROM msc_system_items msi, msc_item_categories mic' ||
2960 ' WHERE mic.organization_id = msi.organization_id ' ||
2961 'AND mic.sr_instance_id = msi.sr_instance_id ' ||
2962 'AND mic.inventory_item_id = msi.inventory_item_id ' ||
2963 'AND mic.category_set_id = :p_category_set '||
2964 'AND mic.category_name = :p_category_name '||
2965 'AND msi.inventory_item_id = :p_item_list ' ||
2966 'AND msi.plan_id = :p_plan_id ';
2967
2968 EXECUTE IMMEDIATE sql_stmt using p_query_id,p_node_type, v_org_seq,
2969 p_category_set, p_category_name,
2970 v_item_id,p_plan_id;
2971 else -- p_category_name is null
2972 sql_stmt := sql_stmt1 ||
2973 'msi.inventory_item_id,'||
2974 'msi.organization_id, '||
2975 ' :NODE_REGULAR_ITEM, ' ||
2976 ' :org_seq, '||
2977 ' msc_get_name.org_code(msi.organization_id,msi.sr_instance_id),'||
2978 ' msi.item_name '||
2979 ' FROM msc_system_items msi ' ||
2980 ' where msi.inventory_item_id = :p_item_list ' ||
2981 ' AND msi.organization_id <> -1 '||
2982 ' AND msi.plan_id = :p_plan_id ';
2983
2984 EXECUTE IMMEDIATE sql_stmt using p_query_id,p_node_type,v_org_seq,
2985 v_item_id,p_plan_id;
2986 end if; -- end p_category_name is not null
2987 end if; -- v_org_id = -1 and p_agg_hzp =2 then
2988 end if; -- if p_item_id(b) = -1 then
2989 end loop; -- p_item_id.count
2990 end loop; -- p_org_id.count
2991 END query_list;
2992
2993 PROCEDURE get_detail_records(p_node_type IN NUMBER,
2994 p_plan_id IN NUMBER,
2995 p_org_id IN NUMBER,
2996 p_inst_id IN NUMBER,
2997 p_item_id IN NUMBER,
2998 p_supplier_id IN NUMBER,
2999 p_supplier_site_id IN NUMBER,
3000 p_dept_id IN NUMBER,
3001 p_res_id IN NUMBER,
3002 p_start_date IN DATE,
3003 p_end_date IN DATE,
3004 p_rowtype IN NUMBER,
3005 p_item_query_id IN NUMBER,
3006 x_trans_list OUT NOCOPY VARCHAR2,
3007 x_error OUT NOCOPY NUMBER,
3008 x_err_message OUT NOCOPY VARCHAR2,
3009 p_plan_type IN NUMBER DEFAULT 2,
3010 p_consumed_row_filter IN VARCHAR2 DEFAULT NULL,
3011 p_res_instance_id IN NUMBER DEFAULT 0,
3012 p_serial_number IN VARCHAR2 DEFAULT NULL) IS
3013
3014 l_query_id NUMBER;
3015
3016 sql_stmt VARCHAR2(5000);
3017 sql_stmt_1 varchar2(32000);
3018 sql_stmt_2 varchar2(32000);
3019 l_count NUMBER := 1;
3020 l_trans_id NUMBER;
3021
3022 l_isDrp boolean := false;
3023 l_isHp boolean := false;
3024
3025 l_filter varchar2(255);
3026
3027 CURSOR TRANS_C IS
3028 SELECT number1
3029 FROM msc_form_query
3030 WHERE query_id = l_query_id;
3031
3032 CURSOR plan_start_c IS
3033 SELECT curr_start_date,
3034 curr_plan_type,
3035 decode(daily_material_constraints,1, 1, 0) +
3036 decode(daily_resource_constraints,1, 1, 0) +
3037 decode(weekly_material_constraints,1, 1, 0) +
3038 decode(weekly_resource_constraints,1, 1, 0) +
3039 decode(period_material_constraints,1, 1, 0) +
3040 decode(period_resource_constraints,1, 1, 0),
3041 decode(enforce_sup_cap_constraints,1,1,0),
3042 decode(daily_material_constraints,1, 1, 0) --ascp_supplier_constraints
3043 FROM msc_plans
3044 WHERE plan_id = p_plan_id;
3045
3046 l_plan_start_date date;
3047 l_plan_type number;
3048 l_optimized_plan number;
3049 l_dock_date_prof number := nvl(FND_PROFILE.Value('MSC_PO_DOCK_DATE_CALC_PREF'),1);
3050
3051 cursor item_record is
3052 select distinct number2,number1
3053 from msc_form_query
3054 where query_id = p_item_query_id
3055 and number5 = p_item_id
3056 and number6 = p_org_id
3057 and number3 = p_inst_id ;
3058 v_org_id number;
3059 v_item_id number;
3060
3061 cursor item_supplier_record is
3062 select distinct number1,number8
3063 from msc_form_query
3064 where query_id = p_item_query_id;
3065
3066
3067 req_item_id NUMBER;
3068 capacity_item_id NUMBER;
3069 i NUMBER := 1;
3070 p_sub_org_id number;
3071
3072 l_constraints number:=0;
3073 l_enforce_sup_cap_constraints number:=0;
3074 l_date varchar2(100);
3075 BEGIN
3076
3077 x_error := 0;
3078
3079 if p_plan_type = 5 then
3080 l_isDrp := true;
3081 else
3082 l_isHp := true;
3083 end if;
3084
3085
3086 SELECT msc_form_query_s.nextval
3087 INTO l_query_id
3088 FROM dual;
3089
3090 --< bug5449978--if plan is drp or unconstrained ascp or any plan where enforce_supplier capacity constraints is off, fallback to msc_supplies
3091 OPEN plan_start_c;
3092 FETCH plan_start_c INTO l_plan_start_date, l_plan_type, l_optimized_plan, l_enforce_sup_cap_constraints, l_constraints;
3093 CLOSE plan_start_c;
3094
3095 --if plan is drp or unconstrained ascp or any plan where enforce_supplier capacity constraints is off, fallback to msc_supplies
3096 --BUG5609299--modified previous fix so that we use msc_supplies for unconstrained DRP or unconstrained ASCP plans.
3097 if (l_plan_type=5 ) or (l_plan_type=1 and l_constraints=0) then
3098 g_use_sup_req:=0; -- do not use msc_supplier_requirements
3099 else
3100 g_use_sup_req:=1; -- use msc_supplier_requirements
3101 end if;
3102 --bug5449978, />
3103
3104 IF l_optimized_plan >= 1 and l_plan_type <> 4 then
3105 l_optimized_plan := 1;
3106 ELSE
3107 l_optimized_plan := 0;
3108 END IF;
3109 sql_stmt := 'INSERT INTO msc_form_query ( '||
3110 'query_id, '||
3111 'last_update_date, '||
3112 'last_updated_by, '||
3113 'creation_date, '||
3114 'created_by, '||
3115 'last_update_login, '||
3116 'number1) ' ||
3117 'SELECT distinct :l_query_id,' ||
3118 ' sysdate, '||
3119 ' 1, '||
3120 ' sysdate, '||
3121 ' 1, '||
3122 ' 1, ';
3123
3124 IF p_node_type in (NODE_LINE,NODE_DEPT_RES,NODE_TRANS_RES) THEN
3125
3126 IF l_optimized_plan =1 and
3127 p_node_type in (NODE_LINE,NODE_DEPT_RES) THEN
3128 -- 4202010, only show res req which really has res req
3129 sql_stmt := sql_stmt ||
3130 ' mrr2.transaction_id '||
3131 ' FROM msc_resource_requirements mrr, ' ||
3132 'msc_resource_requirements mrr2 ' ||
3133 ' where ((trunc(decode(mrr.parent_id, null, mrr.end_date, mrr.start_date)) BETWEEN '||
3134 ':p_start_date AND :p_end_date) or '||
3135 '(trunc(decode(mrr.parent_id, null, mrr.end_date, mrr.start_date)) <= ' ||
3136 ' :p_end_date AND '||
3137 'trunc(mrr.end_date) >= ' ||
3138 ' :p_start_date )) '||
3139 ' and mrr.plan_id = :p_plan_id' ||
3140 ' AND mrr.organization_id = :p_org_id'||
3141 ' AND mrr.sr_instance_id = :p_inst_id' ||
3142 ' and mrr.department_id= :p_dept_id' ||
3143 ' and mrr.resource_id = :p_res_id' ||
3144 ' and nvl(mrr.parent_id,1) =1 '|| -- ATP req without parent id
3145 ' and mrr2.organization_id =mrr.organization_id '||
3146 ' and mrr2.sr_instance_id = mrr.sr_instance_id '||
3147 ' and mrr2.department_id = mrr.department_id '||
3148 ' and mrr2.resource_id = mrr.resource_id '||
3149 ' and mrr2.plan_id = mrr.plan_id '||
3150 ' and nvl(mrr2.parent_id,2) = 2 ' ||
3151 ' and mrr2.supply_id = mrr.supply_id '||
3152 ' and mrr2.operation_seq_num = mrr.operation_seq_num '||
3153 ' and mrr2.resource_seq_num = mrr.resource_seq_num ';
3154
3155 -- To display only Setup time
3156
3157 IF p_rowtype = 520 then
3158 sql_stmt := sql_stmt || ' and mrr2.schedule_flag = 3 ';
3159
3160 ELSIF p_rowtype = 540 then
3161 sql_stmt := sql_stmt || ' and mrr2.schedule_flag = 1 ';
3162 END IF;
3163
3164 ELSE
3165
3166 sql_stmt := sql_stmt ||
3167 ' transaction_id '||
3168 ' FROM msc_resource_requirements_v ' ||
3169 ' where ((trunc(resource_date) BETWEEN '||
3170 ':p_start_date AND :p_end_date) or '||
3171 '(trunc(resource_date) <='||
3172 ':p_end_date AND trunc(resource_end_date) >= :p_start_date ))';
3173
3174 sql_stmt := sql_stmt ||
3175 ' and plan_id = :p_plan_id' ||
3176 ' AND organization_id = :p_org_id'||
3177 ' AND sr_instance_id = :p_inst_id' ||
3178 ' and department_id= :p_dept_id' ||
3179 ' and resource_id = :p_res_id';
3180 END IF;
3181
3182
3183 EXECUTE IMMEDIATE sql_stmt using l_query_id,p_start_date,p_end_date,
3184 p_end_date, p_start_date,p_plan_id,
3185 p_org_id,p_inst_id,
3186 p_dept_id, p_res_id;
3187
3188 ELSIF p_node_type = NODE_RES_INSTANCE THEN
3189
3190 sql_stmt := sql_stmt ||
3191 ' transaction_id '||
3192 ' FROM msc_resource_requirements_v ' ||
3193 ' where ((trunc(resource_date) BETWEEN '||
3194 ':p_start_date AND :p_end_date) or '||
3195 '(trunc(resource_date) <='||
3196 ':p_end_date AND trunc(resource_end_date) >= :p_start_date ))';
3197
3198 -- To display only Setup time
3199
3200 IF p_rowtype = 520 then
3201 sql_stmt := sql_stmt || ' and schedule_type = 3 ';
3202
3203 ELSIF p_rowtype = 540 then
3204 sql_stmt := sql_stmt || ' and schedule_type = 1 ';
3205 END IF;
3206
3207 sql_stmt := sql_stmt ||
3208 ' and plan_id = :p_plan_id' ||
3209 ' AND organization_id = :p_org_id'||
3210 ' AND sr_instance_id = :p_inst_id' ||
3211 ' and department_id= :p_dept_id' ||
3212 ' and resource_id = :p_res_id' ||
3213 ' and res_instance_id = :p_res_inst_id' ||
3214 ' and serial_number = :p_serial_number' ;
3215
3216 EXECUTE IMMEDIATE sql_stmt using l_query_id,p_start_date,p_end_date,
3217 p_end_date, p_start_date,p_plan_id,
3218 p_org_id,p_inst_id,
3219 p_dept_id, p_res_id, p_res_instance_id, p_serial_number;
3220
3221
3222 ELSIF p_node_type = NODE_ITEM_SUPPLIER THEN
3223
3224 -- for capacity statement we always has
3225 -- to check the ASL of ATO model
3226
3227 -- if a user selects a standard item then
3228 -- capacity_item_id will contain the standard item
3229 -- and req_item_id will contain the standard item
3230 -- else if a user selects a model / config item
3231 -- capacity_item_id will contain the base model
3232 -- req_item_id will contain all the configs and model to
3233 -- query requirements.
3234
3235 OPEN item_supplier_record;
3236 LOOP
3237 FETCH item_supplier_record INTO req_item_id, capacity_item_id;
3238 EXIT when item_supplier_record%NOTFOUND;
3239
3240 IF p_rowtype in (5,6,7) THEN
3241 IF i = 1 THEN -- capacity is queried for one the base item only
3242
3243 sql_stmt := sql_stmt ||
3244 ' transaction_id '||
3245 ' FROM msc_supplier_capacity_v '||
3246 ' WHERE plan_id = :p_plan_id' ||
3247 -- ' AND organization_id = :p_org_id'||
3248 -- ' AND sr_instance_id = :p_inst_id'||
3249 ' and supplier_id = :p_supplier_id' ||
3250 ' and inventory_item_id = :capacity_item_id' ||
3251 ' and ((trunc(from_date) BETWEEN '||
3252 ':p_start_date AND :p_end_date) or '||
3253 '(trunc(from_date) <='||
3254 ':p_end_date AND trunc(to_date) >= :p_start_date ))';
3255
3256 if nvl(p_supplier_site_id,-1) <> -1 then
3257 sql_stmt := sql_stmt ||
3258 ' and supplier_site_id = :p_supplier_site_id';
3259 else
3260 sql_stmt := sql_stmt ||
3261 ' and -1 = :p_supplier_site_id';
3262 end if;
3263
3264 EXECUTE IMMEDIATE sql_stmt using l_query_id, p_plan_id,
3265 --p_org_id,p_inst_id,
3266 p_supplier_id,capacity_item_id,
3267 p_start_date,p_end_date,
3268 p_end_date, p_start_date,
3269 nvl(p_supplier_site_id,-1);
3270 END IF; -- only construct sql for capacity first time through cursor
3271 ELSE -- supplier requirements
3272
3273 if g_use_sup_req=0 then
3274 l_date := ' trunc(ms.new_dock_date) ';-- use msc_supplies
3275 else
3276 l_date := ' trunc(mr.consumption_date) ';-- use msc_supplier_requirements
3277 end if;
3278
3279 sql_stmt_2 := sql_stmt ||
3280 ' ms.transaction_id '||
3281 ' FROM msc_supplies ms,'||
3282 ' msc_supplier_requirements mr '||
3283 ' WHERE ms.plan_id = :p_plan_id' ||
3284 ' AND ms.inventory_item_id = :req_item_id' ||
3285 ' and nvl(ms.source_supplier_id,ms.supplier_id )= :p_supplier_id'||
3286 ' and ms.plan_id = mr.plan_id(+) '||
3287 ' and ms.sr_instance_id = mr.sr_instance_id(+) '||
3288 ' and ms.transaction_id = mr.supply_id(+) '||
3289 ' and ((' || l_date || ' BETWEEN '||
3290 ':p_start_date AND :p_end_date) or '||
3291 ' ( ' || l_date || ' <='||
3292 ':p_end_date AND trunc(last_unit_completion_date) >= :p_start_date ))';
3293
3294 -- bug 2775228
3295 IF p_rowtype = 1 THEN -- purchase orders
3296 sql_stmt_2 := sql_stmt_2||' AND ms.order_type = 1 ';
3297 ELSIF p_rowtype = 2 THEN -- requisitions
3298 sql_stmt_2 := sql_stmt_2||' AND ms.order_type = 2 ';
3299 ELSIF p_rowtype = 3 THEN -- planned orders
3300 sql_stmt_2 := sql_stmt_2||' AND ms.order_type = 5 ';
3301 ELSIF p_rowtype = 4 THEN -- Required hours
3302 IF l_dock_date_prof = 1 THEN -- promise_date
3303 sql_stmt_2 := sql_stmt_2||
3304 ' AND ms.order_type in (1,2,5) '||
3305 ' AND DECODE (ms.order_type, '||
3306 ' 1, ms.promised_date) is NULL ' ;
3307 ELSIF l_dock_date_prof = 2 THEN -- need_by_date
3308 sql_stmt_2 := sql_stmt_2||
3309 ' AND ms.order_type in (2,5) ';
3310 END IF;
3311 END IF;
3312
3313 if p_supplier_site_id is not null then
3314 sql_stmt_2 := sql_stmt_2 ||
3315 ' AND nvl(ms.source_supplier_site_id,
3316 ms.supplier_site_id) = :p_supplier_site_id ';
3317 else
3318 sql_stmt_2 := sql_stmt_2 ||
3319 ' AND -1 = :p_supplier_site_id ';
3320 end if;
3321
3322
3323 EXECUTE IMMEDIATE sql_stmt_2 using l_query_id, p_plan_id,
3324 req_item_id,p_supplier_id,
3325 p_start_date,p_end_date,
3326 p_end_date, p_start_date,
3327 nvl(p_supplier_site_id,-1);
3328
3329
3330 sql_stmt_2 := sql_stmt;
3331 END IF;
3332
3333 i := i + 1;
3334
3335 END LOOP;
3336 CLOSE item_supplier_record;
3337
3338
3339 ELSE -- an item
3340 -- need to get the real item_id, org_id from msc_form_query
3341
3342 sql_stmt_1 := sql_stmt;
3343
3344 open item_record;
3345 loop
3346 fetch item_record into v_org_id, v_item_id;
3347 exit when item_record%NOTFOUND;
3348
3349 if p_node_type = NODE_GL_FORECAST_ITEM THEN
3350 MSC_GLOBAL_FORECASTING.get_detail_records(
3351 l_query_id ,
3352 p_node_type ,
3353 p_plan_id ,
3354 v_org_id ,
3355 p_inst_id ,
3356 v_item_id ,
3357 p_rowtype ,
3358 p_supplier_id, -- store p_ship_level ,
3359 p_consumed_row_filter , -- store p_ship_id ,
3360 p_start_date ,
3361 p_end_date );
3362
3363 else -- regular item
3364 sql_stmt := '';
3365 sql_stmt := sql_stmt_1 ||
3366 ' transaction_id '||
3367 ' FROM '||msc_get_name.get_order_view(p_plan_type, p_plan_id) ||
3368 ' WHERE plan_id = :p_plan_id' ||
3369 ' AND organization_id = :p_org_id'||
3370 ' AND sr_instance_id = :p_inst_id'||
3371 ' and inventory_item_id = :p_item_id';
3372
3373 IF l_isDrp = true AND p_rowtype in (50,80,220,380) AND p_dept_id is not null then
3374 p_sub_org_id := p_dept_id;
3375
3376 IF p_rowtype in (50,80) then
3377 -- outbound shipment/requested outbound shipment
3378 sql_stmt := sql_stmt ||
3379 ' AND dest_org_id = :p_sub_org_id';
3380 ELSIF p_rowtype in (220,380) then
3381 -- inbound shipment/requested inbound shipment
3382 sql_stmt := sql_stmt ||
3383 ' AND source_organization_id = :p_sub_org_id';
3384 END IF;
3385
3386 ELSE -- IF l_isDrp = true
3387 p_sub_org_id := -1;
3388 sql_stmt := sql_stmt || ' and -1 = :p_sub_org_id';
3389 END IF;
3390
3391 IF ((l_isHp = true AND p_rowtype = 140 ) OR -- Current scheduled receipts
3392 (l_isDrp = true AND p_rowtype = 270)) THEN
3393
3394 IF trunc(p_start_date) = trunc(l_plan_start_date) THEN
3395 sql_stmt := sql_stmt || ' AND trunc(old_due_date) <= '''||p_end_date|| '''';
3396 ELSE
3397 sql_stmt := sql_stmt || ' AND trunc(old_due_date) BETWEEN '''||
3398 p_start_date||''' AND '''|| p_end_date|| '''';
3399 END IF;
3400 ELSIF ((l_isHp = true AND p_rowtype in (110,150)) OR -- POH, PAB
3401 (l_isDrp = true AND p_rowtype in (280,290,300))) THEN -- POH,PAB,UNC_PAB
3402
3403 sql_stmt := sql_stmt || ' AND trunc(nvl(firm_date,new_due_date)) <= '''||
3404 p_end_date|| '''';
3405
3406 ELSE
3407
3408 sql_stmt := sql_stmt ||
3409 ' AND ((trunc(nvl(firm_date,new_due_date)) BETWEEN '''||
3410 p_start_date||''' AND '''|| p_end_date||
3411 ''') OR (trunc(nvl(firm_date,new_due_date)) <='''||
3412 p_end_date|| ''' AND trunc(last_unit_completion_date) >= '''||
3413 p_start_date||'''))';
3414 END IF;
3415
3416 IF((l_isHp = true AND p_rowtype in (10,230)) OR -- Sales orders
3417 (l_isDrp = true AND p_rowtype = 20)) THEN
3418 sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3419 ' AND order_type in (6,30) ';
3420 ELSIF ((l_isHp = true AND p_rowtype in (20,210,270,290,300)) OR -- Forecast, MAD
3421 (l_isDrp = true AND p_rowtype = 30)) THEN
3422 sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3423 ' AND order_type in (7,29)';
3424 ELSIF (l_isHp = true AND p_rowtype = 30) THEN -- Dependent demand
3425 sql_stmt := sql_stmt ||
3426 ' AND source_table = ''MSC_DEMANDS'''||
3427 ' AND order_type in (1,2,3,4,22,24,25) ';
3428 ELSIF (l_isHp = true AND p_rowtype = 40) THEN -- Expected scrap
3429 sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3430 ' AND order_type in (16,17,18,19,20,21,23,26) ';
3431
3432 ELSIF (l_isHp = true AND p_rowtype = 45) THEN -- Payback Demand
3433 sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3434 ' AND order_type = 27 ';
3435 ELSIF (l_isHp = true AND p_rowtype = 50) THEN -- Other independent demand
3436 sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3437 ' AND order_type in (5,8,9,10,11,12,15) ';
3438 ELSIF (l_isDRP = true AND p_rowtype = 60) THEN -- Other demand
3439 sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3440 ' AND order_type in (5,8,9,10,11,12,15,16,17,18,19,20,23) ';
3441 ELSIF (p_rowtype = 70) THEN --Gross requirements
3442 sql_stmt := sql_stmt ||
3443 ' AND source_table = ''MSC_DEMANDS''';
3444 if l_isDrp then -- not include unconstr kit demand/request shipment
3445 sql_stmt := sql_stmt ||
3446 ' AND order_type not in (48,49) ';
3447 end if;
3448 ELSIF (l_isHp = true AND p_rowtype = 160) or
3449 (l_isDRP = true AND p_rowtype = 370) THEN -- expired lot
3450 sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3451 ' AND order_type =5 ';
3452 ELSIF ((l_isHp = true AND p_rowtype = 81) OR -- WIP
3453 (l_isDrp = true AND p_rowtype = 140)) THEN
3454 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3455 ' AND order_type in (3,7,14,15,27,28) ';
3456 ELSIF p_rowtype = 82 THEN -- Flow schedule
3457 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3458 ' AND order_type = 28 ';
3459 ELSIF ((l_isHp = true AND p_rowtype = 83) OR -- Purchase Orders
3460 (l_isDrp = true AND p_rowtype = 190)) THEN
3461 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3462 ' AND order_type = 1 ';
3463
3464 ELSIF ((l_isHp = true AND p_rowtype = 85) OR -- Requisitions
3465 (l_isDrp = true AND p_rowtype = 240)) THEN
3466 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3467 ' AND order_type in (2,53) ';
3468 ELSIF ((l_isHp = true AND p_rowtype = 87) OR -- In Transit
3469 (l_isDrp = true AND p_rowtype = 230)) THEN
3470 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3471 ' AND order_type = 11 ';
3472 ELSIF ((l_isHp = true AND p_rowtype = 89) OR -- In Receiving
3473 (l_isDrp = true AND p_rowtype = 150)) THEN
3474 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3475 ' AND order_type in (8,12) ';
3476
3477 ELSIF ((l_isHp = true AND p_rowtype = 90) OR
3478 -- Planned Orders: include planned arrival
3479 (l_isDrp = true AND p_rowtype = 250)) THEN
3480 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3481 ' AND order_type in (5,17,51,13) ';
3482 ELSIF p_rowtype = 95 THEN
3483 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3484 ' AND order_type = 29 ';
3485 ELSIF p_rowtype = 97 THEN
3486 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3487 ' AND order_type = 32 ';
3488
3489 ELSIF ((l_isHp = true AND p_rowtype = 100) OR
3490 -- Total Supply: should not include request arrival
3491 (l_isDrp = true AND p_rowtype = 260)) THEN
3492 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3493 ' AND order_type in (1,2,3,5,7,8,11,12,13,14,15,17,27,28,29,32,49,51,53) ';
3494 ELSIF ((l_isHp = true AND p_rowtype = 105) OR -- Beginning on Hand
3495 (l_isDrp = true AND p_rowtype = 130)) THEN
3496 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3497 ' AND order_type = 18 ';
3498 ELSIF ((l_isHp = true AND p_rowtype = 140) OR -- Current Schdld Receipts
3499 (l_isDrp = true AND p_rowtype = 270)) THEN
3500 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3501 ' AND order_type in (1,2,3,7,8,11,12,14,15,27,28,29) ';
3502
3503 -- ---------------------------
3504 -- New Row Types added for DRP
3505 -- ---------------------------
3506
3507 ELSIF (l_isDrp = true AND p_rowtype = 10) THEN -- External Demand
3508 sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3509 ' AND order_type in (6,7,29,30) ';
3510 ELSIF (l_isDrp = true AND p_rowtype = 40) THEN
3511 -- Kit Demand = constrained kit demand + discrete job demand
3512 sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3513 ' AND order_type in (3,47) ';
3514 ELSIF (l_isDrp = true AND p_rowtype = 90) THEN
3515 -- UnConstnd. Kit Demand = discrete job dmd + unconst. kit dmd
3516 sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3517 ' AND order_type in (3,48) ';
3518
3519 ELSIF (l_isDrp = true AND p_rowtype = 100) THEN
3520 -- UnConstnd. Other Demand: scrap dmd + interorg demand
3521 sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3522 ' AND order_type in (16,17,18,19,20,23,24) ';
3523 ELSIF (l_isDrp = true AND p_rowtype = 110) THEN
3524 -- UnConstnd. Total Demand= ext SO +FC + req shipment + unc kit +unc other dmd
3525 sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3526 ' AND order_type in (30,29,49,3,48,16,17,18,19,20,23) ';
3527
3528 ELSIF (l_isDrp = true AND p_rowtype = 120) THEN -- Internal Supply
3529 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3530 ' AND order_type in (3,5,7,8,12,18) ';
3531 ELSIF (l_isDrp = true AND p_rowtype = 160) THEN -- Planned Make
3532 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3533 ' AND order_type = 5 ';
3534 ELSIF (l_isDrp = true AND p_rowtype = 170) THEN -- Ext Supply
3535 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3536 ' AND order_type in (1,2) ';
3537
3538 ELSIF (l_isDrp = true AND p_rowtype = 220) THEN
3539 -- Arrivals := planned inbound shipment + IR
3540 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3541 ' AND order_type in (51,53) ';
3542 ELSIF (l_isDrp = true AND p_rowtype = 380) THEN -- Req. Arrivals
3543 sql_stmt := sql_stmt || ' AND source_table = ''MSC_SUPPLIES''' ||
3544 ' AND order_type = 52 ';
3545 ELSIF (l_isDrp = true AND p_rowtype = 80) THEN
3546 -- Requested Shipments
3547 sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3548 ' AND order_type = 49 ';
3549 ELSIF (l_isDrp = true AND p_rowtype = 50) THEN
3550 -- Shipments = planned outbound shipment + ISO
3551 sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3552 ' AND order_type in (53,54) ';
3553 ELSIF l_isDrp = true AND p_rowtype in (280, 290, 300 ) then -- poh, pab
3554 if p_rowtype = 280 then -- POH
3555 sql_stmt := sql_stmt || ' AND ((source_table = ''MSC_SUPPLIES'''||
3556 ' AND order_type in (1,2,3,7,8,11,12,14,15,17,18,27,28,32,49,53)) ';
3557 else
3558 sql_stmt := sql_stmt || ' AND ((source_table = ''MSC_SUPPLIES'''||
3559 ' AND order_type in (1,2,3,5,7,8,11,12,14,15,17,18,27,28,29,32,49,51,53)) ';
3560 end if;
3561 sql_stmt := sql_stmt ||
3562 ' OR (source_table = ''MSC_DEMANDS'' AND ';
3563 if p_rowtype in (280, 290) then -- poh, pab
3564 -- show const. total demand, not include unc kit dmd/request shipment
3565 sql_stmt := sql_stmt ||
3566 ' order_type not in (48,49) )) ';
3567 elsif p_rowtype = 300 then -- unc pab
3568 -- show unc. total demand
3569 sql_stmt := sql_stmt ||
3570 ' order_type in (30,29,49,3,48,16,17,18,19,20,23) )) ';
3571 end if;
3572
3573
3574 ELSIF (l_isDrp = true AND p_rowtype = 390) THEN -- Expired Demand
3575 sql_stmt := sql_stmt || ' AND source_table = ''MSC_DEMANDS''' ||
3576 ' AND order_type in (6,29,30) ' ||
3577 ' and unmet_quantity > 0 ';
3578
3579 END IF;
3580 /*
3581 dbms_output.put_line(substr(sql_stmt,1,240));
3582 dbms_output.put_line(substr(sql_stmt,241,240));
3583 dbms_output.put_line(substr(sql_stmt,481,240));
3584 */
3585
3586 EXECUTE IMMEDIATE sql_stmt using l_query_id,p_plan_id,
3587 v_org_id, p_inst_id,
3588 v_item_id, p_sub_org_id;
3589 end if; -- if p_node_type <> NODE_GL_FORECAST_ITEM THEN
3590 end loop;
3591 close item_record;
3592
3593 END IF; -- end IF p_node_type in (NODE_LINE,NODE_DEPT_RES,
3594
3595 IF p_node_type not in (NODE_LINE,NODE_DEPT_RES,NODE_TRANS_RES) THEN
3596 x_trans_list := l_query_id;
3597 ELSE
3598 OPEN TRANS_C;
3599 l_count := 1;
3600 LOOP
3601 FETCH TRANS_C INTO l_trans_id;
3602
3603 EXIT WHEN TRANS_C%NOTFOUND;
3604 IF x_trans_list IS NULL THEN
3605 x_trans_list := l_trans_id;
3606 ELSIF ( (length(x_trans_list) + length(l_trans_id) < 31000) AND (l_count < 201) ) THEN
3607 x_trans_list := x_trans_list || ',' || l_trans_id;
3608 ELSE
3609 x_error := 2;
3610 x_err_message := 'MSC_HP_DRILL_LIMIT';
3611 EXIT;
3612 END IF;
3613 l_count := l_count + 1;
3614 END LOOP;
3615 CLOSE TRANS_C;
3616 END IF;
3617 EXCEPTION
3618
3619 WHEN OTHERS THEN
3620
3621 x_error := 1;
3622 x_err_message := sqlerrm;
3623
3624 END get_detail_records;
3625
3626
3627 FUNCTION update_ss
3628 (p_plan_id number,
3629 p_sr_instance_id number,
3630 p_organization_id number,
3631 p_item_id number,
3632 p_from_date date,
3633 p_to_date date ,
3634 p_new_qty number ) return number is
3635 PRAGMA AUTONOMOUS_TRANSACTION;
3636 BEGIN
3637
3638 update msc_safety_stocks
3639 set SAFETY_STOCK_QUANTITY = p_new_qty
3640 where
3641 plan_id = p_plan_id
3642 and sr_instance_id = p_sr_instance_id
3643 and organization_id = p_organization_id
3644 and inventory_item_id = p_item_id
3645 and period_start_date between p_from_date and p_to_date;
3646
3647 commit;
3648 return 1;
3649 EXCEPTION
3650 WHEN NO_DATA_FOUND THEN
3651 return 1;
3652 WHEN OTHERS THEN
3653 return -1;
3654
3655 END update_ss ;
3656
3657
3658 END;