[Home] [Help]
PACKAGE BODY: APPS.GMP_HORIZONTAL_PDR_PKG
Source
1 PACKAGE BODY GMP_HORIZONTAL_PDR_PKG AS
2 /* $Header: GMPHPDRB.pls 120.8.12010000.2 2008/09/05 12:32:26 vpedarla ship $ */
3
4 /* plan types */
5 SRO_PLAN CONSTANT INTEGER := 4;
6
7 PURCHASE_ORDER CONSTANT INTEGER := 1; /* order type lookup */
8 PURCH_REQ CONSTANT INTEGER := 2;
9 WORK_ORDER CONSTANT INTEGER := 3;
10 REPETITIVE_SCHEDULE CONSTANT INTEGER := 4;
11 PLANNED_ORDER CONSTANT INTEGER := 5;
12 MATERIAL_TRANSFER CONSTANT INTEGER := 6;
13 NONSTD_JOB CONSTANT INTEGER := 7;
14 RECEIPT_PURCH_ORDER CONSTANT INTEGER := 8;
15 REQUIREMENT CONSTANT INTEGER := 9;
16 FPO_SUPPLY CONSTANT INTEGER := 10;
17 SHIPMENT CONSTANT INTEGER := 11;
18 RECEIPT_SHIPMENT CONSTANT INTEGER := 12;
19 AGG_REP_SCHEDULE CONSTANT INTEGER := 13;
20 DIS_JOB_BY CONSTANT INTEGER := 14;
21 NON_ST_JOB_BY CONSTANT INTEGER := 15;
22 REP_SCHED_BY CONSTANT INTEGER := 16;
23 PLANNED_BY CONSTANT INTEGER := 17;
24 ON_HAND_QTY CONSTANT INTEGER := 18;
25 FLOW_SCHED CONSTANT INTEGER := 27;
26 FLOW_SCHED_BY CONSTANT INTEGER := 28;
27 PAYBACK_SUPPLY CONSTANT INTEGER := 29;
28
29 SALES CONSTANT INTEGER := 10; /* horizontal plan type lookup */
30 FORECAST CONSTANT INTEGER := 20;
31 PROD_FORECAST CONSTANT INTEGER := 25;
32 DEPENDENT CONSTANT INTEGER := 30;
33 SCRAP CONSTANT INTEGER := 40;
34 PB_DEMAND CONSTANT INTEGER := 45;
35 OTHER CONSTANT INTEGER := 50;
36 GROSS CONSTANT INTEGER := 70;
37 WIP CONSTANT INTEGER := 81;
38 FLOW_SCHEDULE CONSTANT INTEGER := 82;
39 PO CONSTANT INTEGER := 83;
40 REQ CONSTANT INTEGER := 85;
41 TRANSIT CONSTANT INTEGER := 87;
42 RECEIVING CONSTANT INTEGER := 89;
43 PLANNED CONSTANT INTEGER := 90;
44 PB_SUPPLY CONSTANT INTEGER := 95;
45 SUPPLY CONSTANT INTEGER := 100;
46 ON_HAND CONSTANT INTEGER := 105;
47 PAB CONSTANT INTEGER := 110;
48 SS CONSTANT INTEGER := 120;
49 SS_UNC CONSTANT INTEGER := 125;
50 ATP CONSTANT INTEGER := 130;
51 CURRENT_S CONSTANT INTEGER := 140;
52 POH CONSTANT INTEGER := 150;
53 EXP_LOT CONSTANT INTEGER := 160;
54 SS_DOS CONSTANT INTEGER := 180;
55 SS_VAL CONSTANT INTEGER := 190;
56 SSunc_DOS CONSTANT INTEGER := 210;
57 SSunc_VAL CONSTANT INTEGER := 220;
58 USS CONSTANT INTEGER := 230;
59 USS_DOS CONSTANT INTEGER := 240;
60 USS_VAL CONSTANT INTEGER := 250;
61 min_inv_lvl CONSTANT INTEGER := 175;
62 max_inv_lvl CONSTANT INTEGER := 177;
63 TARGET_SER_LVL CONSTANT INTEGER := 270;
64 ACHIEVED_SER_LVL CONSTANT INTEGER := 280;
65 NON_POOL_SS CONSTANT INTEGER := 178;
66 MANU_VARI CONSTANT INTEGER := 183;
67 MAD1 CONSTANT INTEGER := 290;
68
69 SALES_OFF CONSTANT INTEGER := 0; /* offsets */
70 FORECAST_OFF CONSTANT INTEGER := 1;
71 PROD_FORECAST_OFF CONSTANT INTEGER := 2;
72 DEPENDENT_OFF CONSTANT INTEGER := 3;
73 SCRAP_OFF CONSTANT INTEGER := 4;
74 PB_DEMAND_OFF CONSTANT INTEGER := 5;
75 OTHER_OFF CONSTANT INTEGER := 6;
76 GROSS_OFF CONSTANT INTEGER := 7;
77 WIP_OFF CONSTANT INTEGER := 8;
78 PO_OFF CONSTANT INTEGER := 9;
79 REQ_OFF CONSTANT INTEGER := 10;
80 TRANSIT_OFF CONSTANT INTEGER := 11;
81 RECEIVING_OFF CONSTANT INTEGER := 12;
82 PLANNED_OFF CONSTANT INTEGER := 13;
83 PB_SUPPLY_OFF CONSTANT INTEGER := 14;
84 SUPPLY_OFF CONSTANT INTEGER := 15;
85 ON_HAND_OFF CONSTANT INTEGER := 16;
86 PAB_OFF CONSTANT INTEGER := 17;
87 SS_OFF CONSTANT INTEGER := 18;
88 ATP_OFF CONSTANT INTEGER := 19;
89 CURRENT_S_OFF CONSTANT INTEGER := 20;
90 POH_OFF CONSTANT INTEGER := 21;
91 EXP_LOT_OFF CONSTANT INTEGER := 22;
92 SSUNC_OFF CONSTANT INTEGER := 23;
93 min_inv_lvl_off CONSTANT INTEGER := 24;
94 max_inv_lvl_off CONSTANT INTEGER := 25;
95 SS_DOS_OFF CONSTANT INTEGER := 26;
96 SS_VAL_OFF CONSTANT INTEGER := 27;
97 SSUNC_DOS_OFF CONSTANT INTEGER := 28;
98 SSUNC_VAL_OFF CONSTANT INTEGER := 29;
99 USS_OFF CONSTANT INTEGER := 30;
100 USS_DOS_OFF CONSTANT INTEGER := 31;
101 USS_VAL_OFF CONSTANT INTEGER := 32;
102 TARGET_SER_OFF CONSTANT INTEGER := 33;
103 ACHIEVED_SER_OFF CONSTANT INTEGER := 34;
104 NON_POOL_SS_OFF CONSTANT INTEGER := 35;
105 MANF_VARI_OFF CONSTANT INTEGER := 36;
106 PURC_VARI_OFF CONSTANT INTEGER := 37;
107 TRAN_VARI_OFF CONSTANT INTEGER := 38;
108 DMND_VARI_OFF CONSTANT INTEGER := 39;
109 MAD_OFF CONSTANT INTEGER := 40;
110 MAPE_OFF CONSTANT INTEGER := 41;
111
112 NUM_OF_TYPES CONSTANT INTEGER := 42;
113
114 /* MRP demand types */
115 DEMAND_PAYBACK CONSTANT INTEGER := 27;
116
117 G_inst_id NUMBER;
118 G_org_id NUMBER;
119 G_plan_id NUMBER;
120 G_day_bckt_cutoff_dt DATE;
121 G_week_bckt_cutoff_dt DATE;
122 G_period_bucket NUMBER;
123 g_num_of_buckets NUMBER;
124 g_error_stmt VARCHAR2(200);
125
126 TYPE column_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
127
128 PROCEDURE populate_horizontal_plan (
129 p_inst_id NUMBER,
130 p_org_id NUMBER,
131 p_plan_id NUMBER,
132 p_day_bckt_cutoff_dt DATE,
133 p_week_bckt_cutoff_dt DATE,
134 p_period_bucket NUMBER
135 )
136 IS
137
138 -- -------------------------------------------------
139 -- This cursor select number of buckets in the plan.
140 -- -------------------------------------------------
141 CURSOR cur_bckt_start_date IS
142 SELECT trunc(curr_start_date)
143 FROM msc_plans
144 WHERE plan_id = G_plan_id;
145
146
147
148 -- Vpedarla Bug:6784251 Modified the cursor
149 -- Vpedarla Bug:7257708 Modified the cursor. corrected the mistake done for bug: 6784251
150 CURSOR cur_bckt_end_date IS
151 SELECT MIN(trunc(mpsd.next_date))
152 FROM msc_period_start_dates mpsd,
153 msc_trading_partners mtp
154 WHERE
155 mtp.calendar_code = mpsd.calendar_code
156 AND mtp.calendar_exception_set_id = mpsd.exception_set_id
157 AND mtp.sr_tp_id = G_org_id
158 AND mtp.partner_type = 3
159 AND mtp.sr_instance_id = G_inst_id
160 AND mpsd.sr_instance_id = mtp.sr_instance_id
161 AND mpsd.period_start_date >= trunc(G_week_bckt_cutoff_dt)
162 -- AND mpsd.period_sequence_num = ((SELECT mpsd2.period_sequence_num /* bug:6784251 Vpedarla */
163 -- AND mpsd.period_sequence_num = (SELECT mod((mpsd2.period_sequence_num + mtp.sr_instance_id - 1) , 12 ) + 1 /* bug:7257708 Vpedarla */
164 AND mpsd.period_sequence_num = (SELECT mod((mpsd2.period_sequence_num + G_period_bucket - 1) , 12 ) + 1
165 FROM msc_period_start_dates mpsd2
166 WHERE mpsd2.period_start_date = trunc(G_week_bckt_cutoff_dt)
167 AND mpsd2.calendar_code = mpsd.calendar_code
168 AND mpsd2.exception_set_id = mpsd.exception_set_id
169 -- AND mpsd2.sr_instance_id = mtp.sr_instance_id) + G_period_bucket); /* bug:6784251 Vpedarla */
170 AND mpsd2.sr_instance_id = mtp.sr_instance_id) ;
171
172
173 -- -------------------------------------------------
174 -- This cursor selects the dates for the buckets.
175 -- -------------------------------------------------
176 CURSOR bucket_dates(p_start_date DATE, p_end_date DATE) IS
177 SELECT cal.calendar_date
178 FROM msc_calendar_dates cal,
179 msc_trading_partners tp
180 WHERE tp.sr_tp_id = G_org_id
181 AND tp.sr_instance_id = G_inst_id
182 AND tp.calendar_exception_set_id = cal.exception_set_id
183 AND tp.partner_type = 3
184 AND tp.calendar_code = cal.calendar_code
185 AND tp.sr_instance_id = cal.sr_instance_id
186 AND cal.calendar_date BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
187 ORDER BY cal.calendar_date;
188
189 l_bckt_start_date DATE;
190 l_bckt_end_date DATE;
191
192 l_bucket_number NUMBER := 0;
193 l_bucket_date DATE;
194
195 last_date DATE;
196 sid NUMBER;
197
198
199 -- --------------------------------------------
200 -- This cursor selects the snapshot activity in
201 -- MSC_DEMANDS and MSC_SUPPLIES
202 -- for the items per organizatio for a plan..
203 -- --------------------------------------------
204 CURSOR mrp_snapshot_activity IS
205 SELECT /*+ INDEX(rec, MSC_SUPPLIES_N1) */
206 /* list.number5 item_id,
207 list.number6 org_id,
208 list.number3 inst_id,
209 */
210 gpi.inventory_item_id,
211 gpi.organization_id,
212 DECODE(rec.order_type,
213 PURCHASE_ORDER, PO,
214 PURCH_REQ, REQ,
215 WORK_ORDER, WIP,
216 FLOW_SCHED, WIP,
217 REPETITIVE_SCHEDULE,PLANNED,
218 PLANNED_ORDER, PLANNED,
219 NONSTD_JOB, WIP,
220 RECEIPT_PURCH_ORDER,RECEIVING,
221 SHIPMENT, TRANSIT,
222 RECEIPT_SHIPMENT, RECEIVING,
223 PAYBACK_SUPPLY, PB_SUPPLY,
224 ON_HAND_QTY, ON_HAND,
225 AGG_REP_SCHEDULE, CURRENT_S,
226 -- RETURNS, RETURN_SUP,
227 PLANNED) row_type,
228 DECODE(rec.order_type,
229 PURCHASE_ORDER, PO_OFF,
230 PURCH_REQ, REQ_OFF,
231 WORK_ORDER, WIP_OFF,
232 FLOW_SCHED, WIP_OFF,
233 REPETITIVE_SCHEDULE,PLANNED_OFF,
234 PLANNED_ORDER, PLANNED_OFF,
235 NONSTD_JOB, WIP_OFF,
236 RECEIPT_PURCH_ORDER,RECEIVING_OFF,
237 SHIPMENT, TRANSIT_OFF,
238 RECEIPT_SHIPMENT, RECEIVING_OFF,
239 DIS_JOB_BY, WIP_OFF,
240 NON_ST_JOB_BY, WIP_OFF,
241 REP_SCHED_BY, PLANNED_OFF,
242 PLANNED_BY, PLANNED_OFF,
243 FLOW_SCHED_BY, WIP_OFF,
244 PAYBACK_SUPPLY, PB_SUPPLY_OFF,
245 ON_HAND_QTY, ON_HAND_OFF,
246 AGG_REP_SCHEDULE, CURRENT_S_OFF,
247 -- RETURNS, RETURNS_OFF,
248 PLANNED_OFF) offset,
249 dates.calendar_date new_date,
250 decode(rec.order_type, PAYBACK_SUPPLY,
251 dates.calendar_date, rec.old_schedule_date) old_date,
252 SUM(DECODE(gpi.base_item_id,NULL, DECODE(rec.disposition_status_type, /* nsinghi: need to get replace for base_item_id */
253 2, 0, DECODE(rec.last_unit_completion_date,
254 NULL, rec.new_order_quantity, rec.daily_rate) ),
255 DECODE(rec.last_unit_completion_date,
256 NULL, rec.new_order_quantity, rec.daily_rate) )) new_quantity,
257 SUM(NVL(rec.old_order_quantity,0)) old_quantity,
258 sum(0) dos,
259 0 cost
260 FROM --msc_form_query list,
261 gmp_pdr_items_gtmp gpi,
262 msc_trading_partners param,
263 -- msc_system_items msi,
264 msc_supplies rec,
265 msc_calendar_dates dates
266 WHERE /*(arg_res_level = 1
267 OR (arg_res_level = 2
268 AND rec.project_id is NULL)
269 OR (DECODE(arg_res_level,
270 3,nvl(rec.planning_group,'-23453'),
271 4,nvl(to_char(rec.project_id), '-23453'))
272 = nvl(arg_resval1,'-23453'))
273 OR (arg_res_level = 5
274 AND nvl(to_char(rec.project_id), '-23453')
275 = nvl(arg_resval1,'-23453')
276 AND nvl(rec.task_id, -23453) = nvl(arg_resval2, -23453)))
277 AND */ dates.sr_instance_id = rec.sr_instance_id
278 AND dates.exception_set_id = param.calendar_exception_set_id
279 AND dates.calendar_code = param.calendar_code
280 AND dates.calendar_date BETWEEN trunc(rec.new_schedule_date)
281 AND NVL(rec.last_unit_completion_date, trunc(rec.new_schedule_date))
282 AND (trunc(rec.new_schedule_date) <= last_date OR
283 trunc(rec.old_schedule_date) <= last_date)
284 /*
285 AND rec.plan_id = msi.plan_id
286 AND rec.inventory_item_id = msi.inventory_item_id
287 AND rec.organization_id = msi.organization_id
288 AND rec.sr_instance_id = msi.sr_instance_id
289 AND msi.plan_id = list.number4
290 AND msi.inventory_item_id = list.number1
291 AND msi.organization_id = list.number2
292 AND msi.sr_instance_id = list.number3
293 */
294 AND gpi.inventory_item_id = rec.inventory_item_id
295 AND gpi.organization_id = rec.organization_id
296 AND rec.plan_id = G_plan_id
297 AND rec.sr_instance_id = G_inst_id
298
299 AND param.sr_tp_id = rec.organization_id
300 AND param.sr_instance_id = rec.sr_instance_id
301 AND param.partner_type = 3
302 --AND list.query_id = item_list_id
303 GROUP BY
304 /*
305 list.number5,
306 list.number6,
307 list.number3,
308 */
309 gpi.inventory_item_id,
310 gpi.organization_id,
311 -- G_sr_instance_id, /* Will not include sr_instance_id column in gmp_material_plans table. */
312 DECODE(rec.order_type,
313 PURCHASE_ORDER, PO,
314 PURCH_REQ, REQ,
315 WORK_ORDER, WIP,
316 FLOW_SCHED, WIP,
317 REPETITIVE_SCHEDULE,PLANNED,
318 PLANNED_ORDER, PLANNED,
319 NONSTD_JOB, WIP,
320 RECEIPT_PURCH_ORDER,RECEIVING,
321 SHIPMENT, TRANSIT,
322 RECEIPT_SHIPMENT, RECEIVING,
323 PAYBACK_SUPPLY, PB_SUPPLY,
324 ON_HAND_QTY, ON_HAND,
325 AGG_REP_SCHEDULE, CURRENT_S,
326 -- RETURNS, RETURN_SUP,
327 PLANNED),
331 WORK_ORDER, WIP_OFF,
328 DECODE(rec.order_type,
329 PURCHASE_ORDER, PO_OFF,
330 PURCH_REQ, REQ_OFF,
332 FLOW_SCHED, WIP_OFF,
333 REPETITIVE_SCHEDULE,PLANNED_OFF,
334 PLANNED_ORDER, PLANNED_OFF,
335 NONSTD_JOB, WIP_OFF,
336 RECEIPT_PURCH_ORDER,RECEIVING_OFF,
337 SHIPMENT, TRANSIT_OFF,
338 RECEIPT_SHIPMENT, RECEIVING_OFF,
339 DIS_JOB_BY, WIP_OFF,
340 NON_ST_JOB_BY, WIP_OFF,
341 REP_SCHED_BY, PLANNED_OFF,
342 PLANNED_BY, PLANNED_OFF,
343 FLOW_SCHED_BY, WIP_OFF,
344 PAYBACK_SUPPLY, PB_SUPPLY_OFF,
345 ON_HAND_QTY, ON_HAND_OFF,
346 AGG_REP_SCHEDULE, CURRENT_S_OFF,
347 -- RETURNS, RETURNS_OFF,
348 PLANNED_OFF),
349 dates.calendar_date,
350 decode(rec.order_type, PAYBACK_SUPPLY, dates.calendar_date,
351 rec.old_schedule_date)
352 UNION ALL
353 SELECT /*list.number5 item_id,
354 list.number6 org_id,
355 list.number3 inst_id,
356 */
357 gpi.inventory_item_id,
358 gpi.organization_id,
359
360 DECODE(mgr.origination_type,
361 1, DEPENDENT,
362 2, DEPENDENT,
363 3, DEPENDENT,
364 4, DEPENDENT,
365 5, EXP_LOT,
366 6, SALES,
367 7, FORECAST,
368 8, OTHER,
369 9, OTHER,
370 10, OTHER,
371 11, OTHER,
372 12, OTHER,
373 15, OTHER,
374 16, SCRAP,
375 17, SCRAP,
376 18, SCRAP,
377 19, SCRAP,
378 20, SCRAP,
379 21, SCRAP,
380 22, PROD_FORECAST,
381 23, SCRAP,
382 24, DEPENDENT,
383 25, DEPENDENT,
384 26, SCRAP,
385 29, FORECAST, -- for SRO
386 30, SALES,
387 DEMAND_PAYBACK, PB_DEMAND,
388 OTHER) row_type,
389 DECODE(mgr.origination_type,
390 1, DEPENDENT_OFF,
391 2, DEPENDENT_OFF,
392 3, DEPENDENT_OFF,
393 4, DEPENDENT_OFF,
394 5, EXP_LOT_OFF,
395 6, SALES_OFF,
396 7, FORECAST_OFF,
397 8, OTHER_OFF,
398 9, OTHER_OFF,
399 10, OTHER_OFF,
400 11, OTHER_OFF,
401 12, OTHER_OFF,
402 15, OTHER_OFF,
403 16, SCRAP_OFF,
404 17, SCRAP_OFF,
405 18, SCRAP_OFF,
406 19, SCRAP_OFF,
407 20, SCRAP_OFF,
408 21, SCRAP_OFF,
409 22, PROD_FORECAST_OFF,
410 23, SCRAP_OFF,
411 24, DEPENDENT_OFF,
412 25, DEPENDENT_OFF,
413 26, SCRAP_OFF,
414 29, FORECAST_OFF,
415 30, SALES_OFF,
416 DEMAND_PAYBACK, PB_DEMAND_OFF,
417 OTHER_OFF) offset,
418 dates.calendar_date new_date,
419 dates.calendar_date old_date,
420 SUM(DECODE(mgr.assembly_demand_comp_date,
421 NULL, DECODE(mgr.origination_type,
422 29,(nvl(mgr.probability,1)*using_requirement_quantity),
423 31, 0,
424 using_requirement_quantity),
425 DECODE(mgr.origination_type,
426 29,(nvl(mgr.probability,1)*daily_demand_rate),
427 31, 0,
428 daily_demand_rate)))/
429 DECODE(nvl(LEAST(SUM(DECODE(mgr.origination_type,
430 29,nvl(mgr.probability,0),
431 null)) ,1) ,1),
432 0,1,
433 nvl(LEAST(SUM(DECODE(mgr.origination_type,
434 29,nvl(mgr.probability,0),
435 null)) ,1) ,1)) new_quantity,
436 0 old_quantity,
437 0 dos,
438 0 cost
439 FROM -- msc_form_query list,
440 gmp_pdr_items_gtmp gpi,
441 msc_trading_partners param,
442 msc_demands mgr,
443 msc_calendar_dates dates
444 WHERE /*(arg_res_level = 1
445 OR (arg_res_level = 2
446 AND mgr.project_id is NULL)
447 OR (DECODE(arg_res_level,
448 3,nvl(mgr.planning_group,'-23453'),
449 4,nvl(to_char(mgr.project_id), '-23453'))
450 = nvl(arg_resval1,'-23453'))
451 OR (arg_res_level = 5
452 AND nvl(to_char(mgr.project_id), '-23453')
453 = nvl(arg_resval1,'-23453')
454 AND nvl(mgr.task_id, -23453) = nvl(arg_resval2, -23453)))
455 AND */ dates.sr_instance_id = mgr.sr_instance_id
456 AND dates.exception_set_id = param.calendar_exception_set_id
457 AND dates.calendar_code = param.calendar_code
458 AND dates.calendar_date BETWEEN trunc(mgr.using_assembly_demand_date)
459 AND NVL(trunc(mgr.assembly_demand_comp_date),
460 trunc(mgr.using_assembly_demand_date))
461 AND trunc(mgr.using_assembly_demand_date) <= trunc(last_date)
465 AND mgr.organization_id = list.number2
462 /*
463 AND mgr.plan_id = list.number4
464 AND mgr.inventory_item_id = list.number1
466 AND mgr.sr_instance_id = list.number3
467 */
468 AND gpi.inventory_item_id = mgr.inventory_item_id
469 AND gpi.organization_id = mgr.organization_id
470 AND mgr.sr_instance_id = G_inst_id
471 AND mgr.plan_id = G_plan_id
472 AND param.sr_tp_id = mgr.organization_id
473 AND param.sr_instance_id = mgr.sr_instance_id
474 AND param.partner_type = 3
475 --AND list.query_id = item_list_id
476 AND not exists (
477 select 'cancelled IR'
478 from msc_supplies mr
479 where mgr.origination_type in (30,6)
480 and mgr.disposition_id = mr.transaction_id
481 and mgr.plan_id = mr.plan_id
482 and mgr.sr_instance_id = mr.sr_instance_id
483 and mr.disposition_status_type = 2)
484 GROUP BY
485 /*
486 list.number5,
487 list.number6,
488 list.number3,
489 */
490 gpi.inventory_item_id,
491 gpi.organization_id,
492 DECODE(mgr.origination_type,
493 1, DEPENDENT,
494 2, DEPENDENT,
495 3, DEPENDENT,
496 4, DEPENDENT,
497 5, EXP_LOT,
498 6, SALES,
499 7, FORECAST,
500 8, OTHER,
501 9, OTHER,
502 10, OTHER,
503 11, OTHER,
504 12, OTHER,
505 15, OTHER,
506 16, SCRAP,
507 17, SCRAP,
508 18, SCRAP,
509 19, SCRAP,
510 20, SCRAP,
511 21, SCRAP,
512 22, PROD_FORECAST,
513 23, SCRAP,
514 24, DEPENDENT,
515 25, DEPENDENT,
516 26, SCRAP,
517 29, FORECAST,
518 30, SALES,
519 DEMAND_PAYBACK, PB_DEMAND,
520 OTHER),
521 DECODE(mgr.origination_type,
522 1, DEPENDENT_OFF,
523 2, DEPENDENT_OFF,
524 3, DEPENDENT_OFF,
525 4, DEPENDENT_OFF,
526 5, EXP_LOT_OFF,
527 6, SALES_OFF,
528 7, FORECAST_OFF,
529 8, OTHER_OFF,
530 9, OTHER_OFF,
531 10, OTHER_OFF,
532 11, OTHER_OFF,
533 12, OTHER_OFF,
534 15, OTHER_OFF,
535 16, SCRAP_OFF,
536 17, SCRAP_OFF,
537 18, SCRAP_OFF,
538 19, SCRAP_OFF,
539 20, SCRAP_OFF,
540 21, SCRAP_OFF,
541 22, PROD_FORECAST_OFF,
542 23, SCRAP_OFF,
543 24, DEPENDENT_OFF,
544 25, DEPENDENT_OFF,
545 26, SCRAP_OFF,
546 29, FORECAST_OFF,
547 30, SALES_OFF,
548 DEMAND_PAYBACK, PB_DEMAND_OFF,
549 OTHER_OFF),
550 dates.calendar_date,
551 dates.calendar_date,
552 0
553 UNION ALL
554 --- ------------------------------------
555 --- FOR MAD / MAPE
556 --- ------------------------------------
557 SELECT /*list.number5 item_id,
558 list.number6 org_id,
559 list.number3 inst_id,
560 */
561 gpi.inventory_item_id,
562 gpi.organization_id,
563 MAD1 row_type,
564 MAD_OFF offset,
565 dates.calendar_date new_date,
566 dates.calendar_date old_date,
567 SUM(DECODE(mgr.error_type, 1, mgr.forecast_MAD, 0)) new_quantity,
568 SUM(DECODE(mgr.error_type, 2, mgr.forecast_MAD, 0)) old_quantity,
569 0 dos,
570 0 cost
571 FROM --msc_form_query list,
572 gmp_pdr_items_gtmp gpi,
573 msc_trading_partners param,
574 msc_demands mgr,
575 msc_calendar_dates dates
576 WHERE /*(arg_res_level = 1
577 OR (arg_res_level = 2
578 AND mgr.project_id is NULL)
579 OR (DECODE(arg_res_level,
580 3,nvl(mgr.planning_group,'-23453'),
581 4,nvl(to_char(mgr.project_id), '-23453'))
582 = nvl(arg_resval1,'-23453'))
583 OR (arg_res_level = 5
584 AND nvl(to_char(mgr.project_id), '-23453')
585 = nvl(arg_resval1,'-23453')
586 AND nvl(mgr.task_id, -23453) = nvl(arg_resval2, -23453)))
587 AND */ dates.sr_instance_id = mgr.sr_instance_id
588 AND dates.exception_set_id = param.calendar_exception_set_id
589 AND dates.calendar_code = param.calendar_code
590 AND dates.calendar_date BETWEEN trunc(mgr.using_assembly_demand_date)
591 AND NVL(trunc(mgr.assembly_demand_comp_date),
592 trunc(mgr.using_assembly_demand_date))
593 AND trunc(mgr.using_assembly_demand_date) <= trunc(last_date)
594 /*
595 AND mgr.plan_id = list.number4
596 AND mgr.inventory_item_id = list.number1
597 AND mgr.organization_id = list.number2
598 AND mgr.sr_instance_id = list.number3
599 */
600 AND gpi.inventory_item_id = mgr.inventory_item_id
604
601 AND gpi.organization_id = mgr.organization_id
602 AND mgr.sr_instance_id = G_inst_id
603 AND mgr.plan_id = G_plan_id
605 AND param.sr_tp_id = mgr.organization_id
606 AND param.sr_instance_id = mgr.sr_instance_id
607 AND param.partner_type = 3
608 --AND list.query_id = item_list_id
609 GROUP BY
610 /*
611 list.number5,
612 list.number6,
613 list.number3,
614 */
615 gpi.inventory_item_id,
616 gpi.organization_id,
617 MAD1, MAD_OFF,
618 dates.calendar_date,
619 dates.calendar_date,
620 0
621 UNION ALL
622 SELECT /* list.number5 item_id,
623 list.number6 org_id,
624 list.number3 inst_id,
625 */
626 gpi.inventory_item_id,
627 gpi.organization_id,
628 ATP row_type,
629 ATP_OFF offset,
630 avail.schedule_date new_date,
631 avail.schedule_date old_date,
632 avail.quantity_available new_quantity,
633 0 old_quantity,
634 0 dos,
635 0 cost
636 FROM --msc_form_query list,
637 gmp_pdr_items_gtmp gpi,
638 msc_available_to_promise avail
639 WHERE avail.schedule_date < last_date
640 /*AND avail.organization_id = list.number2
641 AND avail.plan_id = list.number4
642 AND avail.inventory_item_id = list.number1
643 AND avail.sr_instance_id = list.number3
644 AND list.query_id = item_list_id
645 */
646 AND avail.organization_id = gpi.organization_id
647 AND avail.inventory_item_id = gpi.inventory_item_id
648 AND avail.sr_instance_id = G_inst_id
649 AND avail.plan_id = G_plan_id
650 UNION ALL
651 SELECT /*list.number5 item_id,
652 list.number6 org_id,
653 list.number3 inst_id,
654 */
655 gpi.inventory_item_id,
656 gpi.organization_id,
657 SS row_type,
658 SS_OFF offset,
659 safety.period_start_date new_date,
660 safety.period_start_date old_date,
661 sum(safety.safety_stock_quantity) new_quantity,
662 safety.organization_id old_quantity,
663 sum(safety.achieved_days_of_supply) dos,
664 sum(safety.safety_stock_quantity * gpi.standard_cost) cost
665 FROM msc_safety_stocks safety,
666 -- msc_form_query list ,
667 -- msc_system_items item
668 gmp_pdr_items_gtmp gpi
669 WHERE safety.period_start_date <= last_date
670 /*AND safety.organization_id = list.number2
671 AND safety.sr_instance_id = list.number3
672 AND safety.plan_id = list.number4
673 AND safety.inventory_item_id = list.number1
674 */
675 AND safety.organization_id = gpi.organization_id
676 AND safety.inventory_item_id = gpi.inventory_item_id
677 AND safety.plan_id = G_plan_id
678 AND safety.sr_instance_id = G_inst_id
679 /*AND nvl(safety.project_id,1) =
680 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))
681 AND nvl(safety.task_id,1) =
682 decode(arg_res_level,5,nvl(arg_resval2,nvl(safety.task_id,1)),nvl(safety.task_id,1))
683 AND list.query_id = item_list_id */
684 AND safety.safety_stock_quantity IS NOT NULL
685 /*
686 AND safety.organization_id = item.organization_id
687 AND safety.sr_instance_id = item.sr_instance_id
688 AND safety.plan_id = item.plan_id
689 AND safety.inventory_item_id = item.inventory_item_id
690 */
691 GROUP BY /* list.number5,
692 list.number6,
693 list.number3,
694 */
695 gpi.inventory_item_id,
696 gpi.organization_id,
697 SS, SS_OFF, safety.period_start_date, safety.organization_id
698 UNION ALL
699 --------------------------------------------------------------------
700 -- This will select unconstrained safety stock for sro plans
701 ---------------------------------------------------------------------
702 SELECT /* list.number5 item_id,
703 list.number6 org_id,
704 list.number3 inst_id, */
705 gpi.inventory_item_id,
706 gpi.organization_id,
707 SS_UNC row_type,
708 SSUNC_OFF offset,
709 safety.period_start_date new_date,
710 safety.period_start_date old_date,
711 sum(safety.TARGET_SAFETY_STOCK) new_quantity,
712 sum(safety.TOTAL_UNPOOLED_SAFETY_STOCK) old_quantity,
713 sum(safety.target_days_of_supply) dos,
714 sum(safety.TARGET_SAFETY_STOCK * gpi.standard_cost) cost
715 FROM msc_safety_stocks safety,
716 -- msc_form_query list ,
717 -- msc_system_items item
718 gmp_pdr_items_gtmp gpi
719 WHERE safety.period_start_date <= last_date
720 /*AND safety.organization_id = list.number2
721 AND safety.sr_instance_id = list.number3
722 AND safety.plan_id = list.number4
723 AND safety.inventory_item_id = list.number1
724 AND nvl(safety.project_id,1) =
725 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))
726 AND nvl(safety.task_id,1) =
727 decode(arg_res_level,5,nvl(arg_resval2,nvl(safety.task_id,1)),nvl(safety.task_id,1))
728 AND list.query_id = item_list_id
729 */
730 -- and safety.target_safety_stock is not null
734 AND safety.sr_instance_id = G_inst_id
731 AND safety.organization_id = gpi.organization_id
732 AND safety.inventory_item_id = gpi.inventory_item_id
733 AND safety.plan_id = G_plan_id
735 /*
736 AND safety.organization_id = item.organization_id
737 AND safety.sr_instance_id = item.sr_instance_id
738 AND safety.plan_id = item.plan_id
739 AND safety.inventory_item_id = item.inventory_item_id
740 */
741 GROUP BY /* list.number5,list.number6,list.number3, */
742 gpi.inventory_item_id,
743 gpi.organization_id,
744 SS_UNC, SSUNC_OFF,
745 safety.period_start_date
746 UNION ALL
747 --------------------------------------------------------------------
748 -- This will select user specified safety stocks
749 ---------------------------------------------------------------------
750 SELECT /* list.number5 item_id,
751 list.number6 org_id,
752 list.number3 inst_id,
753 */
754 gpi.inventory_item_id,
755 gpi.organization_id,
756 USS row_type,
757 USS_OFF offset,
758 safety.period_start_date new_date,
759 safety.period_start_date old_date,
760 sum(safety.USER_DEFINED_SAFETY_STOCKS) new_quantity,
761 sum(0) old_quantity,
762 sum(safety.user_defined_dos) dos,
763 sum(safety.USER_DEFINED_SAFETY_STOCKS * gpi.standard_cost) cost
764 FROM msc_safety_stocks safety,
765 -- msc_form_query list,
766 -- msc_system_items item
767 gmp_pdr_items_gtmp gpi
768 WHERE safety.period_start_date <= last_date
769 /*
770 AND safety.organization_id = list.number2
771 AND safety.sr_instance_id = list.number3
772 AND safety.plan_id = list.number4
773 AND safety.inventory_item_id = list.number1
774 AND nvl(safety.project_id,1) =
775 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))
776 AND nvl(safety.task_id,1) =
777 decode(arg_res_level,5,nvl(arg_resval2,nvl(safety.task_id,1)),nvl(safety.task_id,1))
778 AND list.query_id = item_list_id
779 */
780 AND safety.organization_id = gpi.organization_id
781 AND safety.sr_instance_id = G_inst_id
782 AND safety.plan_id = G_plan_id
783 AND safety.inventory_item_id = gpi.inventory_item_id
784
785 AND nvl(safety.user_defined_safety_stocks,safety.user_defined_dos) IS NOT NULL
786 /*
787 AND safety.organization_id = item.organization_id
788 AND safety.sr_instance_id = item.sr_instance_id
789 AND safety.plan_id = item.plan_id
790 AND safety.inventory_item_id = item.inventory_item_id
791 */
792 GROUP BY /* list.number5,list.number6,list.number3, */
793 gpi.inventory_item_id,
794 gpi.organization_id,
795 USS, USS_OFF,
796 safety.period_start_date, 0
797 UNION ALL
798 --------------------------------------------------------------------
799 -- This will select Lead Time Variability Percentages
800 ---------------------------------------------------------------------
801 SELECT /* list.number5 item_id,
802 list.number6 org_id,
803 list.number3 inst_id,
804 */
805 gpi.inventory_item_id,
806 gpi.organization_id,
807 MANU_VARI row_type,
808 MANF_VARI_OFF offset,
809 safety.period_start_date new_date,
810 safety.period_start_date old_date,
811 sum(safety.MFG_LTVAR_SS_PERCENT) new_quantity,
812 sum(safety.SUP_LTVAR_SS_PERCENT) old_quantity,
813 sum(safety.TRANSIT_LTVAR_SS_PERCENT) dos,
814 sum(safety.DEMAND_VAR_SS_PERCENT) cost
815 FROM msc_safety_stocks safety,
816 -- msc_form_query list,
817 -- msc_system_items item
818 gmp_pdr_items_gtmp gpi
819 WHERE safety.period_start_date <= last_date
820 AND safety.organization_id = gpi.organization_id
821 AND safety.sr_instance_id = G_inst_id
822 AND safety.plan_id = G_plan_id
823 AND safety.inventory_item_id = gpi.inventory_item_id
824 /*
825 AND safety.organization_id = list.number2
826 AND safety.sr_instance_id = list.number3
827 AND safety.plan_id = list.number4
828 AND safety.inventory_item_id = list.number1
829 AND nvl(safety.project_id,1) =
830 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))
831 AND nvl(safety.task_id,1) =
832 decode(arg_res_level,5,nvl(arg_resval2,nvl(safety.task_id,1)),nvl(safety.task_id,1))
833 AND list.query_id = item_list_id
834 AND safety.organization_id = item.organization_id
835 AND safety.sr_instance_id = item.sr_instance_id
836 AND safety.plan_id = item.plan_id
837 AND safety.inventory_item_id = item.inventory_item_id
838 */
839 GROUP BY /* list.number5,list.number6,list.number3, */
840 gpi.inventory_item_id,
841 gpi.organization_id,
842 MANU_VARI, MANF_VARI_OFF,
843 safety.period_start_date
844 UNION ALL
845 --------------------------------------------------------------------
846 -- This will select minimum inventory levels
847 ---------------------------------------------------------------------
848 SELECT /*
849 list.number5 item_id,
850 list.number6 org_id,
851 list.number3 inst_id,
852 */
853 gpi.inventory_item_id,
854 gpi.organization_id,
858 lvl.inventory_date old_date,
855 min_inv_lvl row_type,
856 min_inv_lvl_off offset,
857 lvl.inventory_date new_date,
859 min(lvl.Min_quantity) new_quantity,
860 min(0) old_quantity,
861 min(lvl.min_quantity_dos) dos,
862 0
863 FROM msc_inventory_levels lvl,
864 -- msc_form_query list
865 gmp_pdr_items_gtmp gpi
866 WHERE lvl.inventory_date <= last_date
867 /*
868 AND lvl.organization_id = list.number2
869 AND lvl.sr_instance_id = list.number3
870 AND lvl.plan_id = list.number4
871 AND lvl.inventory_item_id = list.number1
872 AND list.query_id = item_list_id
873 */
874 AND lvl.organization_id = gpi.organization_id
875 AND lvl.sr_instance_id = G_inst_id
876 AND lvl.plan_id = G_plan_id
877 AND lvl.inventory_item_id = gpi.inventory_item_id
878
879 AND nvl(lvl.min_quantity,lvl.min_quantity_dos) IS NOT NULL
880 GROUP BY /* list.number5,list.number6,list.number3, */
881 gpi.inventory_item_id,
882 gpi.organization_id,
883 min_inv_lvl, min_inv_lvl_off,
884 lvl.inventory_date
885 UNION ALL
886 --------------------------------------------------------------------
887 -- This will select maximum inventory levels
888 ---------------------------------------------------------------------
889 SELECT
890 /*
891 list.number5 item_id,
892 list.number6 org_id,
893 list.number3 inst_id,
894 */
895 gpi.inventory_item_id,
896 gpi.organization_id,
897 max_inv_lvl row_type,
898 max_inv_lvl_off offset,
899 lvl.inventory_date new_date,
900 lvl.inventory_date old_date,
901 max(lvl.Max_quantity) new_quantity,
902 max(0) old_quantity,
903 max(lvl.max_quantity_dos) dos,
904 0
905 FROM msc_inventory_levels lvl,
906 -- msc_form_query list
907 gmp_pdr_items_gtmp gpi
908 WHERE lvl.inventory_date<= last_date
909 /*
910 AND lvl.organization_id = list.number2
911 AND lvl.sr_instance_id = list.number3
912 AND lvl.plan_id = list.number4
913 AND lvl.inventory_item_id = list.number1
914 AND list.query_id = item_list_id
915 */
916 AND lvl.organization_id = gpi.organization_id
917 AND lvl.sr_instance_id = G_inst_id
918 AND lvl.plan_id = G_plan_id
919 AND lvl.inventory_item_id = gpi.inventory_item_id
920
921 AND nvl(lvl.max_quantity,lvl.max_quantity_dos) IS NOT NULL
922 GROUP BY /* list.number5,list.number6,list.number3, */
923 gpi.inventory_item_id,
924 gpi.organization_id,
925 max_inv_lvl, max_inv_lvl_off,
926 lvl.inventory_date
927 union all
928 --------------------------------------------------------------------
929 -- This will select Target Inventory Levels
930 ---------------------------------------------------------------------
931 SELECT
932 /*
933 list.number5 item_id,
934 list.number6 org_id,
935 list.number3 inst_id,
936 */
937 gpi.inventory_item_id,
938 gpi.organization_id,
939 TARGET_SER_LVL row_type,
940 TARGET_SER_OFF offset,
941 lvl.period_start_date new_date,
942 lvl.period_start_date old_date,
943 sum(lvl.TARGET_SERVICE_LEVEL) new_quantity,
944 0 old_quantity,
945 0 dos,
946 0
947 FROM msc_analysis_aggregate lvl,
948 -- msc_form_query list
949 gmp_pdr_items_gtmp gpi
950 WHERE lvl.period_start_date <= last_date
951 AND lvl.period_start_date >= l_bckt_start_date -1
952 AND lvl.record_type = 1
953 AND lvl.period_type = 0
954 AND lvl.sr_instance_id IS NULL
955 AND lvl.organization_id IS NULL
956 AND lvl.category_name IS NULL
957 /*
958 AND lvl.plan_id = list.number4
959 AND lvl.inventory_item_id = list.number1
960 AND list.query_id = item_list_id
961 */
962 AND lvl.sr_instance_id = G_inst_id
963 AND lvl.plan_id = G_plan_id
964 AND lvl.inventory_item_id = gpi.inventory_item_id
965
966 GROUP BY /* list.number5,list.number6,list.number3, */
967 gpi.inventory_item_id,
968 gpi.organization_id,
969 TARGET_SER_LVL, TARGET_SER_OFF,
970 lvl.period_start_date
971 union all
972
973 --------------------------------------------------------------------
974 -- This will select ACHIEVED Inventory Levels
975 ---------------------------------------------------------------------
976 SELECT /*
977 list.number5 item_id,
978 list.number6 org_id,
979 list.number3 inst_id,
980 */
981 gpi.inventory_item_id,
982 gpi.organization_id,
983 ACHIEVED_SER_LVL row_type,
984 ACHIEVED_SER_OFF offset,
985 lvl.period_start_date new_date,
986 lvl.period_start_date old_date,
987 sum(lvl.ACHIEVED_SERVICE_LEVEL) new_quantity,
988 0 old_quantity,
989 0 dos,
990 0
991 FROM msc_analysis_aggregate lvl,
992 -- msc_form_query list
993 gmp_pdr_items_gtmp gpi
994 WHERE lvl.period_start_date <= last_date
995 AND lvl.period_start_date >= l_bckt_start_date -1
996 AND lvl.record_type = 1
997 AND lvl.period_type = 0
1001 /*
998 AND lvl.sr_instance_id is null
999 AND lvl.organization_id is null
1000 AND lvl.category_name is null
1002 AND lvl.plan_id = list.number4
1003 AND lvl.inventory_item_id = list.number1
1004 AND list.query_id = item_list_id
1005 */
1006 AND lvl.sr_instance_id = G_inst_id
1007 AND lvl.plan_id = G_plan_id
1008 AND lvl.inventory_item_id = gpi.inventory_item_id
1009 GROUP BY /* list.number5,list.number6,list.number3, */
1010 gpi.inventory_item_id,
1011 gpi.organization_id,
1012 ACHIEVED_SER_LVL, ACHIEVED_SER_OFF,
1013 lvl.period_start_date
1014 /*
1015 union all
1016 --------------------------------------------------------------------
1017 -- This select will ensure that all selected items get into cursor
1018 -- even though they do not have any activity
1019 ---------------------------------------------------------------------
1020
1021 SELECT list.number5,
1022 list.number6,
1023 list.number3,
1024 ON_HAND,
1025 ON_HAND_OFF,
1026 to_date(1, 'J'),
1027 to_date(1, 'J'),
1028 0,
1029 0,
1030 0,
1031 0
1032 FROM msc_form_query list
1033 WHERE list.query_id = item_list_id
1034 */
1035 ORDER BY
1036 1, 2, 5, 3 ;
1037 /*
1038 cursor standard_cost (p_inventory_item_id number,
1039 p_sr_instance_id number,
1040 p_organization_id number,
1041 p_plan_id number) is
1042 select nvl(standard_cost,0)
1043 from msc_system_items
1044 where inventory_item_id=p_inventory_item_id
1045 and organization_id =p_organization_id
1046 and sr_instance_id =p_sr_instance_id
1047 and plan_id =p_plan_id;
1048 */
1049
1050 TYPE mrp_activity IS RECORD
1051 (item_id NUMBER,
1052 org_id NUMBER,
1053 -- inst_id NUMBER,
1054 row_type NUMBER,
1055 offset NUMBER,
1056 new_date DATE,
1057 old_date DATE,
1058 new_quantity NUMBER,
1059 old_quantity NUMBER,
1060 DOS NUMBER,
1061 cost number);
1062
1063 activity_rec mrp_activity;
1064
1065 TYPE calendar_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1066
1067 TYPE column_char IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
1068 TYPE number_arr IS TABLE OF number;
1069
1070 var_dates calendar_date; -- Holds the start dates of buckets
1071 bucket_cells_tab column_number; -- Holds the quantities per bucket
1072 ep_bucket_cells_tab column_number;
1073 last_item_id NUMBER := -1;
1074 last_org_id NUMBER := -1;
1075 --last_inst_id NUMBER := -1;
1076
1077 prev_ss_qty number_arr := number_arr(0);
1078 prev_ss_org number_arr := number_arr(0);
1079 prev_ss_dos_arr number_arr := number_arr(0);
1080 prev_ss_cost_arr number_arr := number_arr(0);
1081 prev_ss_quantity NUMBER := -1;
1082 prev_ss_dos NUMBER := -1;
1083 prev_ss_cost number := -1;
1084
1085
1086 prev_non_pool_ss NUMBER := -1;
1087 non_pool_ss NUMBER := -1;
1088
1089 prev_target_level NUMBER := -1;
1090 prev_achieved_level NUMBER := -1;
1091 prev_mad NUMBER := -1;
1092 prev_mape NUMBER := -1;
1093
1094 target_level NUMBER := -1;
1095 achieved_level NUMBER := -1;
1096 mad NUMBER := -1;
1097 mape NUMBER := -1;
1098
1099 prev_manf_vari NUMBER := -1;
1100 prev_purc_vari NUMBER := -1;
1101 prev_tran_vari NUMBER := -1;
1102 prev_dmnd_vari NUMBER := -1;
1103
1104
1105 manf_vari NUMBER := -1;
1106 purc_vari NUMBER := -1;
1107 tran_vari NUMBER := -1;
1108 dmnd_vari NUMBER := -1;
1109
1110 vari_date date;
1111 prev_vari_date date;
1112
1113 prev_ssunc_q NUMBER := -1;
1114 prev_ssunc_dos NUMBER := -1;
1115 prev_ssunc_date DATE;
1116 ssunc_q NUMBER := -1;
1117 ssunc_dos NUMBER := -1;
1118 ssunc_date DATE;
1119
1120 prev_uss_q NUMBER := -1;
1121 prev_uss_dos NUMBER := -1;
1122 prev_uss_date DATE;
1123 uss_q NUMBER := -1;
1124 uss_dos NUMBER := -1;
1125 uss_date DATE;
1126 l_plan_type NUMBER := 1;
1127 ssunc_cost number := -1;
1128 prev_ssunc_cost number := -1;
1129 uss_cost number := -1;
1130 prev_uss_cost number := -1;
1131
1132 bucket_counter BINARY_INTEGER := 0; -- Id of the current bucket
1133 old_bucket_counter BINARY_INTEGER := 0;
1134 counter BINARY_INTEGER := 0;
1135
1136 PROCEDURE init_prev_ss_qty IS
1137 v_count number;
1138 p_found_org boolean;
1139 BEGIN
1140 if activity_rec.org_id <> -1 THEN -- single org view
1141 prev_ss_quantity := activity_rec.new_quantity;
1142 prev_ss_dos := activity_rec.dos;
1143 prev_ss_cost := activity_rec.cost;
1144 return;
1145 end if;
1146 p_found_org := false;
1147 v_count := prev_ss_org.last;
1148 -- dbms_output.put_line(' in init '||v_count||','||activity_rec.old_quantity||','||activity_rec.new_quantity);
1149 for a in 1 .. v_count loop
1150 if prev_ss_org(a) = activity_rec.old_quantity then
1151 prev_ss_qty(a) := activity_rec.new_quantity;
1152 prev_ss_dos_arr(a) := activity_rec.dos;
1156 end if;
1153 prev_ss_cost_arr(a) := activity_rec.cost;
1154 p_found_org := true;
1155 exit;
1157 end loop;
1158
1159 -- if org_id not exists, add it
1160
1161 if not(p_found_org) then
1162 prev_ss_org.extend;
1163 prev_ss_qty.extend;
1164 prev_ss_dos_arr.extend;
1165 prev_ss_cost_arr.extend;
1166 prev_ss_org(v_count+1) := activity_rec.old_quantity;
1167 prev_ss_qty(v_count+1) := activity_rec.new_quantity;
1168 prev_ss_dos_arr(v_count+1) := activity_rec.dos;
1169 prev_ss_cost_arr(v_count+1) := activity_rec.cost;
1170 end if;
1171
1172 prev_ss_quantity := 0;
1173 prev_ss_dos := 0;
1174 prev_ss_cost := 0;
1175 for a in 1..prev_ss_org.last loop
1176 prev_ss_quantity := prev_ss_quantity + prev_ss_qty(a);
1177 prev_ss_dos := prev_ss_dos + prev_ss_dos_arr(a);
1178 prev_ss_cost := prev_ss_cost + prev_ss_cost_arr(a);
1179 end loop;
1180
1181 -- dbms_output.put_line('prev = '||prev_ss_quantity||','||prev_ss_dos||','||prev_ss_cost);
1182 END init_prev_ss_qty;
1183
1184 PROCEDURE reset_prev_ss IS
1185 BEGIN
1186 prev_ss_org.delete;
1187 prev_ss_qty.delete;
1188 prev_ss_dos_arr.delete;
1189 prev_ss_cost_arr.delete;
1190 prev_ss_quantity := -1;
1191 prev_ss_dos := -1;
1192 prev_ss_cost := -1;
1193 END reset_prev_ss;
1194
1195 -- =============================================================================
1196 --
1197 -- add_to_plan add the 'quantity' to the correct cell of the current bucket.
1198 --
1199 -- =============================================================================
1200 PROCEDURE add_to_plan(bucket IN NUMBER,
1201 offset IN NUMBER,
1202 quantity IN NUMBER
1203 ) IS
1204 location NUMBER;
1205 BEGIN
1206 g_error_stmt := 'Debug - add_to_plan - 10';
1207 IF quantity = 0 THEN
1208 RETURN;
1209 END IF;
1210 /* IF p_enterprise then
1211 location := (bucket - 1) + offset;
1212 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
1213 ep_bucket_cells_tab(location) := quantity;
1214 ELSE
1215 ep_bucket_cells_tab(location) :=
1216 NVL(ep_bucket_cells_tab(location),0) + quantity;
1217 END IF;
1218 ELSE -- not enterprize view
1219 */
1220 location := ((bucket - 1) * NUM_OF_TYPES) + offset;
1221 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
1222 bucket_cells_tab(location) := quantity;
1223 ELSE
1224 /* nsinghi: Txns CURRENT_S represents txn of type schedule receipt. Since the first bucket
1225 store information abt past due data, so do not consider the scedule reciept for first bucket. */
1226
1227 IF ( bucket = 1 AND offset <> CURRENT_S_OFF )THEN
1228 bucket_cells_tab(location) := quantity;
1229 ELSE
1230 bucket_cells_tab(location) := NVL(bucket_cells_tab(location),0) + quantity;
1231 END IF;
1232 END IF;
1233 -- END IF;
1234 END;
1235
1236 -- =============================================================================
1237 --
1238 -- flush_item_plan inserts into MRP_MATERIAL_PLANS
1239 --
1240 -- =============================================================================
1241 PROCEDURE flush_item_plan(p_item_id IN NUMBER,
1242 p_org_id IN NUMBER) IS
1243 loop_counter BINARY_INTEGER := 1;
1244 item_name VARCHAR2(255);
1245 org_code VARCHAR2(7);
1246 atp_counter BINARY_INTEGER := 1;
1247 total_reqs NUMBER := 0;
1248 lot_quantity NUMBER := 0;
1249 expired_qty NUMBER := 0;
1250 total_supply NUMBER := 0;
1251 committed_demand NUMBER := 0;
1252 atp_qty NUMBER := 0;
1253 carried_back_atp_qty NUMBER := 0;
1254 atp_flag NUMBER :=2;
1255 l_atp_qty_net MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
1256
1257 sales_sum NUMBER;
1258 forecast_sum NUMBER;
1259 prod_forecast_sum NUMBER;
1260 dependent_sum NUMBER;
1261 scrap_sum NUMBER;
1262 pb_demand_sum NUMBER;
1263 other_sum NUMBER;
1264 gross_sum NUMBER;
1265 wip_sum NUMBER;
1266 po_sum NUMBER;
1267 req_sum NUMBER;
1268 transit_sum NUMBER;
1269 receiving_sum NUMBER;
1270 planned_sum NUMBER;
1271 pb_supply NUMBER;
1272 supply_sum NUMBER;
1273 on_hand_sum NUMBER;
1274 current_s_sum NUMBER;
1275 exp_lot_sum NUMBER;
1276
1277 next_week_start_date DATE;
1278 next_period_start_date DATE;
1279 curr_week_start_date DATE;
1280 curr_period_start_date DATE;
1281
1282 week_change_flag BOOLEAN;
1283 period_change_flag BOOLEAN;
1284 prev_week_loop_counter NUMBER;
1285 prev_period_loop_counter NUMBER;
1286
1287 CURSOR check_atp IS
1288 SELECT gpi.calculate_atp
1289 FROM gmp_pdr_items_gtmp gpi
1290 WHERE gpi.inventory_item_id = p_item_id
1291 AND gpi.organization_id = p_org_id;
1292
1293 BEGIN
1294
1295 -- ----------------------------------------
1296 -- get plan type to check if it is SRO plan
1297 -- add SS to gross req if plan type is SRO
1298 -- ----------------------------------------
1299
1300 SELECT plan_type INTO l_plan_type
1301 FROM msc_plans
1302 WHERE plan_id = G_plan_id;
1303 -- -------------------------------
1304 -- Get the item segments, atp flag
1308 FETCH check_atp INTO atp_flag;
1305 -- -------------------------------
1306 g_error_stmt := 'Debug - flush_item_plan - 10';
1307 OPEN check_atp;
1309 CLOSE check_atp;
1310
1311 -- IF NOT enterprize_view THEN
1312 -- -----------------------------
1313 -- Calculate gross requirements,
1314 -- Total suppy
1315 -- PAB
1316 -- POH
1317 -- -----------------------------
1318
1319 FOR LOOP IN 1..g_num_of_buckets
1320 LOOP
1321 ----------------------
1322 -- Gross requirements.
1323 -- -------------------
1324 g_error_stmt := 'Debug - flush_item_plan - 20 - loop'||loop;
1325 lot_quantity := bucket_cells_tab(((loop - 1) * NUM_OF_TYPES)+
1326 EXP_LOT_OFF);
1327 total_reqs := total_reqs +
1328 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SALES_OFF) +
1329 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) +
1330 FORECAST_OFF) +
1331 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) +
1332 DEPENDENT_OFF) +
1333 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) +
1334 PROD_FORECAST_OFF) +
1335 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) +
1336 PB_DEMAND_OFF) +
1337 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + OTHER_OFF);
1338
1339 --------------------
1340 -- Lot Expirations
1341 --------------------
1342 IF(lot_quantity > total_reqs AND lot_quantity > 0 ) THEN
1343 expired_qty := lot_quantity - total_reqs;
1344 total_reqs := 0;
1345
1346 add_to_plan(loop,
1347 GROSS_OFF,
1348 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SALES_OFF) +
1349 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + FORECAST_OFF) +
1350 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + DEPENDENT_OFF) +
1351 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PROD_FORECAST_OFF) +
1352 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PB_DEMAND_OFF) +
1353 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SCRAP_OFF) +
1354 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + OTHER_OFF) +
1355 expired_qty);
1356
1357
1358 add_to_plan(loop,
1359 DEPENDENT_OFF,
1360 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + DEPENDENT_OFF) +
1361 expired_qty);
1362 ELSE
1363
1364 add_to_plan(loop,
1365 GROSS_OFF,
1366 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SALES_OFF) +
1367 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + FORECAST_OFF) +
1368 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + DEPENDENT_OFF) +
1369 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PROD_FORECAST_OFF) +
1370 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PB_DEMAND_OFF) +
1371 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SCRAP_OFF) +
1372 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + OTHER_OFF));
1373
1374 END IF;
1375
1376
1377 g_error_stmt := 'Debug - flush_item_plan - 30 - loop'||loop;
1378 -- -------------
1379 -- Total supply.
1380 -- -------------
1381 add_to_plan(loop,
1382 SUPPLY_OFF,
1383 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + WIP_OFF) +
1384 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PO_OFF) +
1385 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + REQ_OFF) +
1386 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + TRANSIT_OFF) +
1387 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + RECEIVING_OFF) +
1388 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PB_SUPPLY_OFF) +
1389 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PLANNED_OFF));
1390
1391 -- bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + RETURNS_OFF));
1392
1393 -- ----------------------------
1394 -- Projected available balance.
1395 -- ----------------------------
1396 g_error_stmt := 'Debug - flush_item_plan - 40 - loop'||loop;
1397 -- The first bucket is past due so we include onhand from the second
1398 -- bucket.
1399 /* nsinghi: available balance. */
1400 IF loop = 1 THEN
1401 add_to_plan(loop,
1402 PAB_OFF,
1403 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + ON_HAND_OFF) +
1404 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SUPPLY_OFF) -
1405 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + GROSS_OFF));
1406 ELSE
1407 add_to_plan(loop,
1408 PAB_OFF,
1409 bucket_cells_tab(((loop - 2) * NUM_OF_TYPES) + PAB_OFF) +
1410 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + ON_HAND_OFF) +
1411 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SUPPLY_OFF) -
1412 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + GROSS_OFF));
1413 END IF;
1414
1415 -- ------------------
1416 -- Projected on hand.
1417 -- ------------------
1418 g_error_stmt := 'Debug - flush_item_plan - 50 - loop'||loop;
1419 -- The first bucket is past due so we include onhand from the second
1420 -- bucket.
1421 IF loop = 1 THEN
1422 add_to_plan(loop,
1423 POH_OFF,
1424 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + ON_HAND_OFF) +
1425 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + CURRENT_S_OFF) -
1429 POH_OFF,
1426 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + GROSS_OFF));
1427 ELSIF loop = 2 THEN
1428 add_to_plan(loop,
1430 bucket_cells_tab(((loop - 2) * NUM_OF_TYPES) + POH_OFF) +
1431 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + ON_HAND_OFF) +
1432 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + CURRENT_S_OFF) -
1433 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + GROSS_OFF));
1434 ELSE
1435 add_to_plan(loop,
1436 POH_OFF,
1437 bucket_cells_tab(((loop - 2) * NUM_OF_TYPES) + POH_OFF) +
1438 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + CURRENT_S_OFF) -
1439 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + GROSS_OFF));
1440 END IF;
1441
1442 END LOOP; -- columnd
1443 ----------------
1444 -- calculate ATP
1445 ----------------
1446 g_error_stmt := 'Debug - flush_item_plan - 60';
1447
1448 FOR atp_counter IN 1..g_num_of_buckets LOOP
1449 add_to_plan(atp_counter, ATP_OFF, 0);
1450 END LOOP;
1451
1452 IF atp_flag = 1 THEN -- only calculate atp when atp_flag is 1
1453
1454 IF l_atp_qty_net.COUNT = 0 THEN
1455 l_atp_qty_net.Extend(g_num_of_buckets);
1456 END IF;
1457
1458 FOR atp_counter IN 1..g_num_of_buckets
1459 LOOP
1460
1461 IF atp_counter = 2 THEN
1462 total_supply := bucket_cells_tab(((atp_counter - 1)
1463 * NUM_OF_TYPES) + SUPPLY_OFF)+
1464 bucket_cells_tab(((atp_counter - 1) *
1465 NUM_OF_TYPES) + ON_HAND_OFF);
1466 ELSE
1467 total_supply := bucket_cells_tab(((atp_counter - 1)
1468 * NUM_OF_TYPES) + SUPPLY_OFF);
1469
1470 END IF;
1471
1472 committed_demand := bucket_cells_tab(((atp_counter - 1)
1473 * NUM_OF_TYPES) + SALES_OFF) +
1474 bucket_cells_tab(((atp_counter - 1) *
1475 NUM_OF_TYPES) + DEPENDENT_OFF) +
1476 bucket_cells_tab(((atp_counter - 1) *
1477 NUM_OF_TYPES) + SCRAP_OFF);
1478
1479
1480 l_atp_qty_net(atp_counter) := total_supply - committed_demand;
1481
1482 END LOOP;
1483
1484 msc_atp_proc.atp_consume(l_atp_qty_net, g_num_of_buckets);
1485
1486 FOR atp_counter IN 1..g_num_of_buckets LOOP
1487 add_to_plan(atp_counter, ATP_OFF, l_atp_qty_net(atp_counter));
1488 END LOOP;
1489
1490 END IF;
1491
1492 /* nsinghi: insert logic -
1493 1) For the num of days buckets, no issue, insert the txns as it is.
1494 2) Do the looping for days buckets till var_dates(loop_counter) < G_day_bckt_cutoff_dt
1495 3) For each week bucket, get the (next week_start_date - 1). Loop the loop_counter for these many days and
1496 add the supply and demand txns. Insert a row for the week_start_date bucket.
1497 4) The loop runs till var_dates(loop_counter) < G_week_bckt_cutoff_dt
1498 5) For each period bucket, get the (next period_start_date - 1). Loop the loop_counter for these many days and
1499 add the supply and demand txns. Insert a row for the period_start_date bucket.
1500 4) The loop runs till var_dates(loop_counter) <= last_date.
1501 */
1502
1503 sales_sum := 0;
1504 forecast_sum := 0;
1505 prod_forecast_sum := 0;
1506 dependent_sum := 0;
1507 scrap_sum := 0;
1508 pb_demand_sum := 0;
1509 other_sum := 0;
1510 gross_sum := 0;
1511 wip_sum := 0;
1512 po_sum := 0;
1513 req_sum := 0;
1514 transit_sum := 0;
1515 receiving_sum := 0;
1516 planned_sum := 0;
1517 pb_supply := 0;
1518 supply_sum := 0;
1519 on_hand_sum := 0;
1520 current_s_sum := 0;
1521 exp_lot_sum := 0;
1522
1523 next_week_start_date := NULL;
1524 next_period_start_date := NULL;
1525 curr_week_start_date := NULL;
1526 curr_period_start_date := NULL;
1527
1528 week_change_flag := TRUE;
1529 period_change_flag := TRUE;
1530 prev_week_loop_counter := -1;
1531 prev_period_loop_counter := -1;
1532
1533 FOR loop_counter IN 1..g_num_of_buckets LOOP
1534 IF var_dates(loop_counter) < G_day_bckt_cutoff_dt THEN
1535
1536 INSERT INTO gmp_horizontal_pdr_gtmp
1537 (
1538 organization_id,
1539 inventory_item_id,
1540 bucket_date,
1541 quantity1, -- SALES_OFF
1542 quantity2, -- FORECAST_OFF
1543 quantity3, -- PROD_FORECAST
1544 quantity4, -- DEPENDENT_OFF
1545 quantity5, -- SCRAP_OFF
1546 quantity6, -- PB_DEMAND_OFF CONSTANT INTEGER := 5
1547 quantity7, -- OTHER_OFF CONSTANT INTEGER := 6
1548 quantity8, -- GROSS_OFF CONSTANT INTEGER := 7
1549 quantity9, -- WIP_OFF CONSTANT INTEGER := 8
1550 quantity10, -- PO_OFF CONSTANT INTEGER := 9
1551 quantity11, -- REQ_OFF CONSTANT INTEGER := 10
1552 quantity12, -- TRANSIT_OFF CONSTANT INTEGER := 11
1553 quantity13, -- RECEIVING_OFF_OFF CONSTANT INTEGER := 12
1554 quantity14, -- PLANEED_OFF_OFF CONSTANT INTEGER := 13
1555 quantity15, -- PB_SUPPLY_OFF CONSTANT INTEGER := 14
1559 quantity19, -- SS_OFF CONSTANT INTEGER := 18
1556 quantity16, -- SUPPLY_OFF CONSTANT INTEGER := 15
1557 quantity17, -- ON_HAND_OFF CONSTANT INTEGER := 16
1558 quantity18, -- PAB_OFF CONSTANT INTEGER := 17
1560 quantity20, -- ATP_OFF CONSTANT INTEGER := 19
1561 quantity21, -- CURRENT_S_OFF CONSTANT INTEGER := 20
1562 quantity22, -- POH_OFF CONSTANT INTEGER := 21
1563 quantity23, -- EXP_LOT_OFF CONSTANT INTEGER := 22
1564 quantity24, -- SSUNC_OFF CONSTANT INTEGER := 24
1565 quantity25, -- min_inv_lvl_off CONSTANT INTEGER := 25
1566 quantity26, -- max_inv_lvl_off CONSTANT INTEGER := 26
1567 quantity27, -- SS_DOS_OFF CONSTANT INTEGER := 27
1568 quantity28, -- SS_VAL_OFF CONSTANT INTEGER := 28
1569 quantity29, -- SSUNC_DOS_OFF CONSTANT INTEGER := 29
1570 quantity30, -- SSUNC_VAL_OFF CONSTANT INTEGER := 30
1571 quantity31, -- USS_OFF CONSTANT INTEGER := 31
1572 quantity32, -- USS_DOS_OFF CONSTANT INTEGER := 32
1573 quantity33, -- USS_VAL_OFF CONSTANT INTEGER := 33
1574 quantity34, -- TAGET_OFF
1575 quantity35,
1576 quantity36, -- Non Pool
1577 quantity37, -- Manf Vari
1578 quantity38,
1579 quantity39,
1580 quantity40,
1581 quantity41,
1582 quantity42
1583 )
1584 VALUES
1585 (
1586 p_org_id,
1587 p_item_id,
1588 var_dates(loop_counter),
1589 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SALES_OFF),
1590 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + FORECAST_OFF),
1591 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PROD_FORECAST_OFF),
1592 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + DEPENDENT_OFF),
1593 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SCRAP_OFF),
1594 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PB_DEMAND_OFF),
1595 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + OTHER_OFF),
1596 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + GROSS_OFF),
1597 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + WIP_OFF),
1598 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PO_OFF),
1599 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + REQ_OFF),
1600 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TRANSIT_OFF),
1601 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + RECEIVING_OFF),
1602 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PLANNED_OFF),
1603 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PB_SUPPLY_OFF),
1604 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SUPPLY_OFF),
1605 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ON_HAND_OFF),
1606 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PAB_OFF),
1607 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_OFF),
1608 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ATP_OFF),
1609 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + CURRENT_S_OFF),
1610 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + POH_OFF),
1611 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + EXP_LOT_OFF),
1612 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_OFF),
1613 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MIN_INV_LVL_OFF),
1614 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAX_INV_LVL_OFF),
1615 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_DOS_OFF),
1616 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_VAL_OFF),
1617 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_DOS_OFF),
1618 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_VAL_OFF),
1619 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_OFF),
1620 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_DOS_OFF),
1621 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_VAL_OFF),
1622 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TARGET_SER_OFF),
1623 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ACHIEVED_SER_OFF),
1624 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + NON_POOL_SS_OFF),
1625 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MANF_VARI_OFF),
1626 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PURC_VARI_OFF),
1627 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TRAN_VARI_OFF),
1628 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + DMND_VARI_OFF),
1629 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAD_OFF),
1630 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAPE_OFF)
1631 );
1632
1633
1634 END IF;
1635
1636 IF var_dates(loop_counter) >= G_day_bckt_cutoff_dt AND
1637 var_dates(loop_counter) < G_week_bckt_cutoff_dt THEN
1638 /* sum the txns. maintain the week start date. */
1639 next_week_start_date := msc_calendar.next_work_day (-1*G_org_id, G_inst_id,
1640 MSC_CALENDAR.TYPE_WEEKLY_BUCKET, var_dates(loop_counter)+1 );
1641
1642 IF curr_week_start_date <> next_week_start_date THEN
1643 week_change_flag := TRUE;
1644
1645 INSERT INTO gmp_horizontal_pdr_gtmp
1646 (
1650 quantity1, -- SALES_OFF
1647 organization_id,
1648 inventory_item_id,
1649 bucket_date,
1651 quantity2, -- FORECAST_OFF
1652 quantity3, -- PROD_FORECAST
1653 quantity4, -- DEPENDENT_OFF
1654 quantity5, -- SCRAP_OFF
1655 quantity6, -- PB_DEMAND_OFF CONSTANT INTEGER := 5
1656 quantity7, -- OTHER_OFF CONSTANT INTEGER := 6
1657 quantity8, -- GROSS_OFF CONSTANT INTEGER := 7
1658 quantity9, -- WIP_OFF CONSTANT INTEGER := 8
1659 quantity10, -- PO_OFF CONSTANT INTEGER := 9
1660 quantity11, -- REQ_OFF CONSTANT INTEGER := 10
1661 quantity12, -- TRANSIT_OFF CONSTANT INTEGER := 11
1662 quantity13, -- RECEIVING_OFF_OFF CONSTANT INTEGER := 12
1663 quantity14, -- PLANEED_OFF_OFF CONSTANT INTEGER := 13
1664 quantity15, -- PB_SUPPLY_OFF CONSTANT INTEGER := 14
1665 quantity16, -- SUPPLY_OFF CONSTANT INTEGER := 15
1666 quantity17, -- ON_HAND_OFF CONSTANT INTEGER := 16
1667 quantity18, -- PAB_OFF CONSTANT INTEGER := 17
1668 quantity19, -- SS_OFF CONSTANT INTEGER := 18
1669 quantity20, -- ATP_OFF CONSTANT INTEGER := 19
1670 quantity21, -- CURRENT_S_OFF CONSTANT INTEGER := 20
1671 quantity22, -- POH_OFF CONSTANT INTEGER := 21
1672 quantity23, -- EXP_LOT_OFF CONSTANT INTEGER := 22
1673 quantity24, -- SSUNC_OFF CONSTANT INTEGER := 24
1674 quantity25, -- min_inv_lvl_off CONSTANT INTEGER := 25
1675 quantity26, -- max_inv_lvl_off CONSTANT INTEGER := 26
1676 quantity27, -- SS_DOS_OFF CONSTANT INTEGER := 27
1677 quantity28, -- SS_VAL_OFF CONSTANT INTEGER := 28
1678 quantity29, -- SSUNC_DOS_OFF CONSTANT INTEGER := 29
1679 quantity30, -- SSUNC_VAL_OFF CONSTANT INTEGER := 30
1680 quantity31, -- USS_OFF CONSTANT INTEGER := 31
1681 quantity32, -- USS_DOS_OFF CONSTANT INTEGER := 32
1682 quantity33, -- USS_VAL_OFF CONSTANT INTEGER := 33
1683 quantity34, -- TAGET_OFF
1684 quantity35,
1685 quantity36, -- Non Pool
1686 quantity37, -- Manf Vari
1687 quantity38,
1688 quantity39,
1689 quantity40,
1690 quantity41,
1691 quantity42
1692 )
1693 VALUES
1694 (
1695 p_org_id,
1696 p_item_id,
1697 var_dates(prev_week_loop_counter),
1698 sales_sum,
1699 forecast_sum,
1700 prod_forecast_sum,
1701 dependent_sum,
1702 scrap_sum,
1703 pb_demand_sum,
1704 other_sum,
1705 gross_sum,
1706 wip_sum,
1707 po_sum,
1708 req_sum,
1709 transit_sum,
1710 receiving_sum,
1711 planned_sum,
1712 pb_supply,
1713 supply_sum,
1714 on_hand_sum,
1715 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PAB_OFF),
1716 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_OFF),
1717 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ATP_OFF),
1718 current_s_sum,
1719 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + POH_OFF),
1720 exp_lot_sum,
1721 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_OFF),
1722 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MIN_INV_LVL_OFF),
1723 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAX_INV_LVL_OFF),
1724 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_DOS_OFF),
1725 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_VAL_OFF),
1726 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_DOS_OFF),
1727 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_VAL_OFF),
1728 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_OFF),
1729 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_DOS_OFF),
1730 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_VAL_OFF),
1731 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TARGET_SER_OFF),
1732 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ACHIEVED_SER_OFF),
1733 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + NON_POOL_SS_OFF),
1734 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MANF_VARI_OFF),
1735 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PURC_VARI_OFF),
1736 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TRAN_VARI_OFF),
1737 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + DMND_VARI_OFF),
1738 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAD_OFF),
1739 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAPE_OFF)
1740 );
1741
1742 sales_sum := 0;
1743 forecast_sum := 0;
1744 prod_forecast_sum := 0;
1745 dependent_sum := 0;
1746 scrap_sum := 0;
1747 pb_demand_sum := 0;
1751 po_sum := 0;
1748 other_sum := 0;
1749 gross_sum := 0;
1750 wip_sum := 0;
1752 req_sum := 0;
1753 transit_sum := 0;
1754 receiving_sum := 0;
1755 planned_sum := 0;
1756 pb_supply := 0;
1757 supply_sum := 0;
1758 on_hand_sum := 0;
1759 current_s_sum := 0;
1760 exp_lot_sum := 0;
1761
1762 END IF;
1763 IF week_change_flag THEN
1764 curr_week_start_date := next_week_start_date;
1765 prev_week_loop_counter := loop_counter;
1766 week_change_flag := FALSE;
1767 END IF;
1768
1769 sales_sum := sales_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SALES_OFF) ;
1770 forecast_sum := forecast_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + FORECAST_OFF);
1771 prod_forecast_sum := prod_forecast_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PROD_FORECAST_OFF);
1772 dependent_sum := dependent_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + DEPENDENT_OFF);
1773 scrap_sum := scrap_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SCRAP_OFF);
1774 pb_demand_sum := pb_demand_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PB_DEMAND_OFF);
1775 other_sum := other_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + OTHER_OFF);
1776 gross_sum := gross_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + GROSS_OFF);
1777 wip_sum := wip_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + WIP_OFF);
1778 po_sum := po_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PO_OFF);
1779 req_sum := req_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + REQ_OFF);
1780 transit_sum := transit_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TRANSIT_OFF);
1781 receiving_sum := receiving_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + RECEIVING_OFF);
1782 planned_sum := planned_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PLANNED_OFF);
1783 pb_supply := pb_supply + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PB_SUPPLY_OFF);
1784 supply_sum := supply_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SUPPLY_OFF);
1785 on_hand_sum := on_hand_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ON_HAND_OFF);
1786 current_s_sum := current_s_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + CURRENT_S_OFF);
1787 exp_lot_sum := exp_lot_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + EXP_LOT_OFF);
1788
1789 END IF;
1790
1791 IF var_dates(loop_counter) >= G_week_bckt_cutoff_dt AND
1792 var_dates(loop_counter) <= last_date THEN
1793 /* sum the txns. maintain the week start date. */
1794 next_period_start_date := msc_calendar.next_work_day (-1*G_org_id, G_inst_id,
1795 MSC_CALENDAR.TYPE_MONTHLY_BUCKET, var_dates(loop_counter)+1 );
1796
1797 IF curr_period_start_date <> next_period_start_date THEN
1798 period_change_flag := TRUE;
1799
1800 INSERT INTO gmp_horizontal_pdr_gtmp
1801 (
1802 organization_id,
1803 inventory_item_id,
1804 bucket_date,
1805 quantity1, -- SALES_OFF
1806 quantity2, -- FORECAST_OFF
1807 quantity3, -- PROD_FORECAST
1808 quantity4, -- DEPENDENT_OFF
1809 quantity5, -- SCRAP_OFF
1810 quantity6, -- PB_DEMAND_OFF CONSTANT INTEGER := 5
1811 quantity7, -- OTHER_OFF CONSTANT INTEGER := 6
1812 quantity8, -- GROSS_OFF CONSTANT INTEGER := 7
1813 quantity9, -- WIP_OFF CONSTANT INTEGER := 8
1814 quantity10, -- PO_OFF CONSTANT INTEGER := 9
1815 quantity11, -- REQ_OFF CONSTANT INTEGER := 10
1816 quantity12, -- TRANSIT_OFF CONSTANT INTEGER := 11
1817 quantity13, -- RECEIVING_OFF_OFF CONSTANT INTEGER := 12
1818 quantity14, -- PLANEED_OFF_OFF CONSTANT INTEGER := 13
1819 quantity15, -- PB_SUPPLY_OFF CONSTANT INTEGER := 14
1820 quantity16, -- SUPPLY_OFF CONSTANT INTEGER := 15
1821 quantity17, -- ON_HAND_OFF CONSTANT INTEGER := 16
1822 quantity18, -- PAB_OFF CONSTANT INTEGER := 17
1823 quantity19, -- SS_OFF CONSTANT INTEGER := 18
1824 quantity20, -- ATP_OFF CONSTANT INTEGER := 19
1825 quantity21, -- CURRENT_S_OFF CONSTANT INTEGER := 20
1826 quantity22, -- POH_OFF CONSTANT INTEGER := 21
1827 quantity23, -- EXP_LOT_OFF CONSTANT INTEGER := 22
1828 quantity24, -- SSUNC_OFF CONSTANT INTEGER := 24
1829 quantity25, -- min_inv_lvl_off CONSTANT INTEGER := 25
1830 quantity26, -- max_inv_lvl_off CONSTANT INTEGER := 26
1831 quantity27, -- SS_DOS_OFF CONSTANT INTEGER := 27
1832 quantity28, -- SS_VAL_OFF CONSTANT INTEGER := 28
1833 quantity29, -- SSUNC_DOS_OFF CONSTANT INTEGER := 29
1834 quantity30, -- SSUNC_VAL_OFF CONSTANT INTEGER := 30
1835 quantity31, -- USS_OFF CONSTANT INTEGER := 31
1836 quantity32, -- USS_DOS_OFF CONSTANT INTEGER := 32
1837 quantity33, -- USS_VAL_OFF CONSTANT INTEGER := 33
1838 quantity34, -- TAGET_OFF
1842 quantity38,
1839 quantity35,
1840 quantity36, -- Non Pool
1841 quantity37, -- Manf Vari
1843 quantity39,
1844 quantity40,
1845 quantity41,
1846 quantity42
1847 )
1848 VALUES
1849 (
1850 p_org_id,
1851 p_item_id,
1852 var_dates(prev_period_loop_counter),
1853 sales_sum,
1854 forecast_sum,
1855 prod_forecast_sum,
1856 dependent_sum,
1857 scrap_sum,
1858 pb_demand_sum,
1859 other_sum,
1860 gross_sum,
1861 wip_sum,
1862 po_sum,
1863 req_sum,
1864 transit_sum,
1865 receiving_sum,
1866 planned_sum,
1867 pb_supply,
1868 supply_sum,
1869 on_hand_sum,
1870 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PAB_OFF),
1871 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_OFF),
1872 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ATP_OFF),
1873 current_s_sum,
1874 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + POH_OFF),
1875 exp_lot_sum,
1876 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_OFF),
1877 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MIN_INV_LVL_OFF),
1878 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAX_INV_LVL_OFF),
1879 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_DOS_OFF),
1880 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SS_VAL_OFF),
1881 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_DOS_OFF),
1882 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SSUNC_VAL_OFF),
1883 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_OFF),
1884 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_DOS_OFF),
1885 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + USS_VAL_OFF),
1886 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TARGET_SER_OFF),
1887 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ACHIEVED_SER_OFF),
1888 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + NON_POOL_SS_OFF),
1889 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MANF_VARI_OFF),
1890 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PURC_VARI_OFF),
1891 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TRAN_VARI_OFF),
1892 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + DMND_VARI_OFF),
1893 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAD_OFF),
1894 bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + MAPE_OFF)
1895 );
1896
1897 sales_sum := 0;
1898 forecast_sum := 0;
1899 prod_forecast_sum := 0;
1900 dependent_sum := 0;
1901 scrap_sum := 0;
1902 pb_demand_sum := 0;
1903 other_sum := 0;
1904 gross_sum := 0;
1905 wip_sum := 0;
1906 po_sum := 0;
1907 req_sum := 0;
1908 transit_sum := 0;
1909 receiving_sum := 0;
1910 planned_sum := 0;
1911 pb_supply := 0;
1912 supply_sum := 0;
1913 on_hand_sum := 0;
1914 current_s_sum := 0;
1915 exp_lot_sum := 0;
1916
1917 END IF;
1918 IF period_change_flag THEN
1919 curr_period_start_date := next_period_start_date;
1920 prev_period_loop_counter := loop_counter;
1921 period_change_flag := FALSE;
1922 END IF;
1923
1924 sales_sum := sales_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SALES_OFF) ;
1925 forecast_sum := forecast_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + FORECAST_OFF);
1926 prod_forecast_sum := prod_forecast_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PROD_FORECAST_OFF);
1927 dependent_sum := dependent_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + DEPENDENT_OFF);
1928 scrap_sum := scrap_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SCRAP_OFF);
1929 pb_demand_sum := pb_demand_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PB_DEMAND_OFF);
1930 other_sum := other_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + OTHER_OFF);
1931 gross_sum := gross_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + GROSS_OFF);
1932 wip_sum := wip_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + WIP_OFF);
1933 po_sum := po_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PO_OFF);
1934 req_sum := req_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + REQ_OFF);
1935 transit_sum := transit_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + TRANSIT_OFF);
1936 receiving_sum := receiving_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + RECEIVING_OFF);
1937 planned_sum := planned_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PLANNED_OFF);
1938 pb_supply := pb_supply + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + PB_SUPPLY_OFF);
1939 supply_sum := supply_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + SUPPLY_OFF);
1940 on_hand_sum := on_hand_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + ON_HAND_OFF);
1944 END IF;
1941 current_s_sum := current_s_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + CURRENT_S_OFF);
1942 exp_lot_sum := exp_lot_sum + bucket_cells_tab(NUM_OF_TYPES * (loop_counter - 1) + EXP_LOT_OFF);
1943
1945
1946 END LOOP;
1947
1948 END flush_item_plan;
1949
1950 -- =============================================================================
1951 BEGIN
1952
1953 G_inst_id := p_inst_id;
1954 G_org_id := p_org_id;
1955 G_plan_id := p_plan_id;
1956 G_day_bckt_cutoff_dt := p_day_bckt_cutoff_dt ;
1957 G_week_bckt_cutoff_dt := p_week_bckt_cutoff_dt;
1958 G_period_bucket := NVL(p_period_bucket,0);
1959 g_num_of_buckets := 0;
1960 g_error_stmt := NULL;
1961
1962 SELECT plan_type INTO l_plan_type
1963 FROM msc_plans
1964 WHERE plan_id = G_plan_id;
1965
1966 g_error_stmt := 'Debug - populate_horizontal_plan - 10';
1967 FND_FILE.PUT_LINE ( FND_FILE.LOG,g_error_stmt);
1968 OPEN cur_bckt_start_date;
1969 FETCH cur_bckt_start_date INTO l_bckt_start_date;
1970 CLOSE cur_bckt_start_date;
1971
1972 /* nsinghi :
1973 1) get the week_cutoff_date.
1974 2) find the seq number from msc_period_start_date table.
1975 3) add the period seq number as entered by the user.
1976 4) get the next_date column - 1 as the period cutoff date
1977 5) subtract the period_cutoff_date - curr_start_date (bucket start date from msc_plans)
1978 6) this gives the number of days. */
1979
1980 OPEN cur_bckt_end_date;
1981 FETCH cur_bckt_end_date INTO l_bckt_end_date;
1982 CLOSE cur_bckt_end_date;
1983
1984
1985 /* nsinghi: since the next_date column in msc_period_start_dates already give end_date + 1,
1986 so no need to add 1. */
1987 -- g_num_of_buckets := (l_bckt_end_date + 1) - (l_bckt_start_date - 1);
1988
1989 -- Rajesh Patangya, We have to see if the end date is coming as null
1990 IF l_bckt_end_date IS NULL THEN
1991 g_num_of_buckets := 1 ;
1992 FND_FILE.PUT_LINE ( FND_FILE.LOG,' Enter Correct Number of periods to find the Number of Buckets');
1993 ELSE
1994 g_num_of_buckets := (l_bckt_end_date) - (l_bckt_start_date - 1);
1995 END IF;
1996
1997 g_error_stmt := 'Debug - populate_horizontal_plan - 20';
1998 FND_FILE.PUT_LINE ( FND_FILE.LOG,g_error_stmt);
1999 -- ---------------------------------
2000 -- Initialize the bucket cells to 0.
2001 -- ---------------------------------
2002 /* IF enterprize_view or arg_ep_view_also THEN
2003 FOR counter IN 0..NUM_OF_TYPES LOOP
2004 ep_bucket_cells_tab(counter) := 0;
2005 END LOOP;
2006 last_date := arg_cutoff_date;
2007 END IF;
2008 */
2009 -- IF not (enterprize_view) THEN
2010 FOR counter IN 0..(NUM_OF_TYPES * g_num_of_buckets) LOOP
2011 bucket_cells_tab(counter) := 0;
2012 END LOOP;
2013
2014 g_error_stmt := 'Debug - populate_horizontal_plan - 30';
2015 FND_FILE.PUT_LINE ( FND_FILE.LOG,g_error_stmt);
2016 -- --------------------
2017 -- Get the bucket dates
2018 -- --------------------
2019 -- OPEN bucket_dates(l_bckt_start_date-1, l_bckt_end_date+1);
2020 OPEN bucket_dates(l_bckt_start_date-1, l_bckt_end_date);
2021 LOOP
2022 FETCH bucket_dates INTO l_bucket_date;
2023 EXIT WHEN BUCKET_DATES%NOTFOUND;
2024 l_bucket_number := l_bucket_number + 1;
2025 var_dates(l_bucket_number) := l_bucket_date;
2026 END LOOP;
2027 CLOSE bucket_dates;
2028
2029 -- last_date := arg_cutoff_date;
2030 last_date := l_bckt_end_date;
2031
2032 -- END IF;
2033
2034 g_error_stmt := 'Debug - populate_horizontal_plan - 40';
2035 FND_FILE.PUT_LINE ( FND_FILE.LOG,g_error_stmt);
2036
2037 bucket_counter := 2;
2038 old_bucket_counter := 2;
2039 activity_rec.item_id := 0;
2040 activity_rec.org_id := 0;
2041 -- activity_rec.inst_id := 0;
2042 activity_rec.row_type := 0;
2043 activity_rec.offset := 0;
2044 activity_rec.new_date := sysdate;
2045 activity_rec.old_date := sysdate;
2046 activity_rec.new_quantity := 0;
2047 activity_rec.old_quantity := 0;
2048 activity_rec.DOS := 0;
2049 activity_rec.cost:=0;
2050
2051 /* nsinghi:
2052 Logic:
2053 Here the logic from start of this procedure to the point where data is inserted in GMP_Material_Plans is explained.
2054
2055 1) Initially get the number of days that the horiz report needs to consider based
2056 on the days, weeks and periods entered in the conc window.
2057 2) Each row in GMP_Material_Plans will correspond to one day.
2058 3) PL/SQL Table bucket_cells_tab contains (num of report days) * (num of txns type) num of rows. Thus for each transaction on each day has one row in bucket_cells_tab. Each txn is given an offset as defined by
2059 the constants in the procedure. Thus all multiples of the offset will store information of that type of txns.
2060 4) Every time there is a change in the item, the data is inserted in GMP_Material_plans table.
2061 5) For each activity row, get the txn qty. Insert the qty in bucket_cells_tab. The location of the qty in
2062 bucket_cells_tab will depend on the txn type offset and the day. The bucket day is retrieved as follow:
2063 a) Get the dates for all the days of the report and store the dates in PL/SQL date table var_dates
2064 b) Get the row number from var_dates table where the activity row date < date in var_dates
2065 6) For txn of type safety stock, everytime a safety stock activity is retrieved, associate that safety
2069 7) Same thing is true for some of the other txns like Manufacturing variation, Demand variation,
2066 stock to all the days. This is cause, same safety stock is valid for all the days. If a new safety stock
2067 activity row is later retrieved, replace the new safety stock for all the days after the bucket day
2068 of retrieving the safety stock.
2070 Purchase variation. Do not know what all these txns mean.
2071 8) But it is not true for txns like Sales Order, Forecast, Planned Order etc. Obviously, these txns
2072 are only for that specific bucket day and not for all the days after the bucket.
2073
2074 */
2075
2076 OPEN mrp_snapshot_activity;
2077 LOOP
2078 FETCH mrp_snapshot_activity INTO activity_rec;
2079
2080 -- dbms_output.put_line(activity_rec.offset || '**' ||
2081 -- activity_rec.new_date || ' ** ' ||
2082 -- activity_rec.item_id || '**' || activity_rec.org_id || '**' ||
2083 -- activity_rec.new_quantity || '**' || activity_rec.dos || '**' ||
2084 -- activity_rec.cost || '** ' || activity_rec.old_quantity);
2085
2086 IF ((mrp_snapshot_activity%NOTFOUND) OR
2087 (activity_rec.item_id <> last_item_id) OR
2088 (activity_rec.org_id <> last_org_id)) AND
2089 last_item_id <> -1 THEN
2090
2091 -- --------------------------
2092 -- Need to flush the plan for
2093 -- the previous item.
2094 -- --------------------------
2095 IF prev_ss_quantity <> -1
2096 -- AND NOT enterprize_view
2097 THEN
2098
2099 /* nsinghi: In the loops below, same safety stock is associated to all the bucket days
2100 after the current bucket day. */
2101
2102 FOR k IN bucket_counter..g_num_of_buckets + 1
2103 LOOP
2104 add_to_plan(k-1,
2105 SS_OFF,
2106 prev_ss_quantity);
2107 add_to_plan(k -1,
2108 SS_val_OFF,
2109 prev_ss_cost);
2110 add_to_plan(k -1,
2111 SS_dos_OFF,
2112 prev_ss_dos);
2113
2114 IF prev_ssunc_q <> -1
2115 -- AND NOT enterprize_view
2116 THEN
2117
2118 add_to_plan(k -1 ,
2119 SSUNC_OFF,
2120 prev_ssunc_q);
2121 add_to_plan(k -1 ,
2122 SSUNC_val_OFF,
2123 prev_ssunc_cost);
2124 add_to_plan(k -1 ,
2125 SSUNC_dos_OFF,
2126 prev_ssunc_dos);
2127
2128 END IF;
2129 END LOOP;
2130 END IF;
2131
2132 IF prev_non_pool_ss <> -1
2133 -- AND NOT enterprize_view
2134 THEN
2135
2136 FOR k IN bucket_counter..g_num_of_buckets + 1 LOOP
2137 add_to_plan(k -1 ,
2138 NON_POOL_SS_OFF,
2139 prev_non_pool_ss);
2140 END LOOP;
2141 END IF;
2142
2143 IF prev_manf_vari <> -1
2144 -- AND NOT enterprize_view
2145 THEN
2146
2147 FOR k IN bucket_counter..g_num_of_buckets + 1
2148 LOOP
2149 add_to_plan(k-1,
2150 MANF_VARI_OFF,
2151 prev_manf_vari);
2152
2153 add_to_plan(k-1,
2154 PURC_VARI_OFF,
2155 prev_purc_vari);
2156 add_to_plan(k -1,
2157 TRAN_VARI_OFF,
2158 prev_tran_vari);
2159 add_to_plan(k -1,
2160 DMND_VARI_OFF,
2161 prev_dmnd_vari);
2162 END LOOP;
2163 END IF;
2164
2165
2166 IF prev_target_level <> -1
2167 -- AND NOT enterprize_view
2168 THEN
2169
2170 FOR k IN bucket_counter..g_num_of_buckets + 1
2171 LOOP
2172 add_to_plan(k-1,
2173 TARGET_SER_OFF,
2174 prev_target_level);
2175 END LOOP;
2176 END IF;
2177
2178 IF prev_achieved_level <> -1
2179 -- AND NOT enterprize_view
2180 THEN
2181
2182 FOR k IN bucket_counter..g_num_of_buckets + 1
2183 LOOP
2184 add_to_plan(k-1,
2185 ACHIEVED_SER_OFF,
2186 prev_achieved_level);
2187 END LOOP;
2188 END IF;
2189
2190
2191 IF prev_mad <> -1
2192 -- AND NOT enterprize_view
2193 THEN
2194
2195 FOR k IN bucket_counter..g_num_of_buckets + 1
2196 LOOP
2197
2198 add_to_plan(k-1,
2199 MAD_OFF,
2200 prev_mad);
2201 add_to_plan(k-1,
2202 MAPE_OFF,
2203 prev_mape);
2204 END LOOP;
2205 END IF;
2206
2207 IF prev_uss_q<> -1
2208 -- AND NOT enterprize_view
2209 THEN
2210
2214 uSS_OFF,
2211 FOR k IN bucket_counter..g_num_of_buckets + 1
2212 LOOP
2213 add_to_plan(k-1,
2215 prev_uss_q);
2216 add_to_plan(k -1,
2217 uSS_val_OFF,
2218 prev_uss_cost);
2219 add_to_plan(k -1,
2220 uSS_dos_OFF,
2221 0);
2222 END LOOP;
2223 END IF;
2224
2225 IF prev_uss_dos<> -1
2226 -- AND NOT enterprize_view
2227 THEN
2228
2229 FOR k IN bucket_counter..g_num_of_buckets + 1
2230 LOOP
2231 add_to_plan(k-1,
2232 uSS_OFF,
2233 0);
2234 add_to_plan(k -1,
2235 uSS_val_OFF,
2236 0);
2237 add_to_plan(k -1,
2238 uSS_dos_OFF,
2239 prev_uss_dos);
2240 END LOOP;
2241 END IF;
2242 flush_item_plan(last_item_id,
2243 last_org_id);
2244
2245 bucket_counter := 2;
2246 old_bucket_counter := 2;
2247 reset_prev_ss;
2248 prev_ssunc_q := -1;
2249 ssunc_q := -1;
2250 prev_ssunc_dos:= -1;
2251 ssunc_dos := -1;
2252 uss_q := -1;
2253 prev_uss_q := -1;
2254 uss_dos := -1;
2255 prev_uss_dos := -1;
2256 ssunc_cost := -1;
2257 prev_ssunc_cost := -1;
2258 uss_cost := -1;
2259 prev_uss_cost := -1;
2260 prev_non_pool_ss := -1;
2261 non_pool_ss := -1;
2262
2263 prev_manf_vari := -1;
2264 prev_purc_vari := -1;
2265 prev_tran_vari := -1;
2266 prev_dmnd_vari := -1;
2267
2268 prev_target_level := -1;
2269 prev_achieved_level := -1;
2270 prev_mad := -1;
2271 prev_mape := -1;
2272
2273 target_level := -1;
2274 achieved_level := -1;
2275 mad := -1;
2276 mape := -1;
2277
2278 manf_vari := -1;
2279 purc_vari := -1;
2280 tran_vari := -1;
2281 dmnd_vari := -1;
2282
2283 -- ------------------------------------
2284 -- Initialize the bucket cells to 0.
2285 -- ------------------------------------
2286 /* IF enterprize_view or arg_ep_view_also THEN
2287 FOR counter IN 0..NUM_OF_TYPES LOOP
2288 ep_bucket_cells_tab(counter) := 0;
2289 END LOOP;
2290 END IF;
2291 */
2292 -- IF not (enterprize_view) then
2293
2294 /* nsinghi: Since the item has changed and all the data for the prev item
2295 has been flushed to GMP_Material_Plan table, so no longer need that
2296 data. clear the bucket_cells_tab so that the old item's qty do not
2297 get added to new item txn qty. */
2298
2299 FOR counter IN 0..(NUM_OF_TYPES * g_num_of_buckets) LOOP
2300 bucket_cells_tab(counter) := 0;
2301 END LOOP;
2302 -- END IF;
2303 END IF; -- end of activity_rec.item_id <> last_item_id
2304
2305 EXIT WHEN mrp_snapshot_activity%NOTFOUND;
2306
2307 /*
2308 IF enterprize_view or arg_ep_view_also THEN
2309 IF activity_rec.row_type IN (WIP, PO, REQ, TRANSIT,
2310 RECEIVING, PB_SUPPLY) THEN
2311 add_to_plan(CURRENT_S_OFF + 1, 0, activity_rec.old_quantity,true);
2312 END IF;
2313 add_to_plan(activity_rec.offset + 1 , 0,
2314 activity_rec.new_quantity,true);
2315 END IF;
2316 */
2317 -- IF not(enterprize_view) THEN
2318
2319 IF activity_rec.row_type = SS THEN
2320
2321 -- --------------------------
2322 -- Got a safety stock record.
2323 -- --------------------------
2324 IF (bucket_counter <= g_num_of_buckets AND
2325 activity_rec.new_date < var_dates(bucket_counter)) THEN
2326 -- ----------------------------------
2327 -- This safety stock quantity applies
2328 -- to the current bucket.
2329 -- ----------------------------------
2330 -- init_prev_ss_qty;
2331 /* For single org, the procedure init_prev_ss_qty is doing only the following steps.
2332 This call will always be for single org. So removing the procedure. */
2333 prev_ss_quantity := activity_rec.new_quantity;
2334 prev_ss_dos := activity_rec.dos;
2335 prev_ss_cost := activity_rec.cost;
2336
2337 END IF;
2338 END IF;
2339 IF activity_rec.row_type = SS_UNC THEN
2340 -- --------------------------
2341 -- Got a safety stock record.
2342 -- --------------------------
2343 ssunc_q := activity_rec.new_quantity;
2344 ssunc_dos := activity_rec.dos;
2345 ssunc_date := activity_rec.new_date;
2346 ssunc_cost := activity_rec.cost;
2347 non_pool_ss := activity_rec.old_quantity;
2348
2349 IF (bucket_counter <= g_num_of_buckets AND
2350 activity_rec.new_date < var_dates(bucket_counter)) THEN
2351 -- ----------------------------------
2352 -- This safety stock quantity applies
2353 -- to the current bucket.
2357 prev_ssunc_date := activity_rec.new_date;
2354 -- ----------------------------------
2355 prev_ssunc_q := activity_rec.new_quantity;
2356 prev_ssunc_dos := activity_rec.dos;
2358 prev_ssunc_cost := activity_rec.cost;
2359 prev_non_pool_ss := activity_rec.old_quantity;
2360 END IF;
2361 END IF;
2362
2363 IF activity_rec.row_type = MANU_VARI THEN
2364 -- --------------------------
2365 -- Got a safety stock record.
2366 -- --------------------------
2367 manf_vari := activity_rec.new_quantity;
2368 purc_vari := activity_rec.old_quantity;
2369 tran_vari := activity_rec.DOS;
2370 dmnd_vari := activity_rec.cost;
2371 vari_date := activity_rec.new_date;
2372
2373 IF (bucket_counter <= g_num_of_buckets AND
2374 activity_rec.new_date < var_dates(bucket_counter)) THEN
2375 -- ----------------------------------
2376 -- This safety stock quantity applies
2377 -- to the current bucket.
2378 -- ----------------------------------
2379
2380 prev_manf_vari := activity_rec.new_quantity;
2381 prev_purc_vari := activity_rec.old_quantity;
2382 prev_tran_vari := activity_rec.dos;
2383 prev_dmnd_vari := activity_rec.cost;
2384 prev_vari_date := activity_rec.new_date;
2385
2386 END IF;
2387 END IF;
2388
2389 IF activity_rec.row_type = MAD THEN
2390 mad := activity_rec.new_quantity;
2391 mape := activity_rec.old_quantity;
2392
2393 IF (bucket_counter <= g_num_of_buckets AND
2394 activity_rec.new_date < var_dates(bucket_counter)) THEN
2395
2396 prev_mad := activity_rec.new_quantity;
2397 prev_mape := activity_rec.old_quantity;
2398 END IF;
2399 END IF;
2400
2401
2402 IF activity_rec.row_type = TARGET_SER_LVL THEN
2403 -- --------------------------
2404 -- Got a safety stock record.
2405 -- --------------------------
2406 target_level := activity_rec.new_quantity;
2407 /* dbms_output.put_line (' target value ' || target_level );
2408 dbms_output.put_line(' bkt counter ' || bucket_counter || 'no of bkts '|| g_num_of_buckets);
2409 dbms_output.put_line(' var dates ' || var_dates(bucket_counter));
2410 */
2411 IF (bucket_counter <= g_num_of_buckets AND
2412 activity_rec.new_date < var_dates(bucket_counter)) THEN
2413 -- ----------------------------------
2414 -- This safety stock quantity applies
2415 -- to the current bucket.
2416 -- ----------------------------------
2417
2418 prev_target_level := activity_rec.new_quantity;
2419 END IF;
2420 END IF;
2421
2422
2423 IF activity_rec.row_type = ACHIEVED_SER_LVL THEN
2424 -- --------------------------
2425 -- Got a safety stock record.
2426 -- --------------------------
2427 achieved_level := activity_rec.new_quantity;
2428 /* dbms_output.put_line (' target value ' || achieved_level );
2429 dbms_output.put_line(' bkt counter ' || bucket_counter || 'no of bkts '|| g_num_of_buckets);
2430 dbms_output.put_line(' var dates ' || var_dates(bucket_counter));
2431 */
2432 IF (bucket_counter <= g_num_of_buckets AND
2433 activity_rec.new_date < var_dates(bucket_counter)) THEN
2434 -- ----------------------------------
2435 -- This safety stock quantity applies
2436 -- to the current bucket.
2437 -- ----------------------------------
2438
2439 prev_achieved_level := activity_rec.new_quantity;
2440 END IF;
2441 END IF;
2442
2443
2444 IF activity_rec.row_type = USS
2445 and activity_rec.new_quantity is null THEN
2446 -- --------------------------
2447 -- Got a safety stock record.
2448 -- --------------------------
2449 uss_dos := activity_rec.dos;
2450 uss_date := activity_rec.new_date;
2451 uss_cost := activity_rec.cost;
2452 uss_q := -1;
2453
2454 IF activity_rec.new_date < var_dates(bucket_counter) THEN
2455 -- ----------------------------------
2456 -- This safety stock quantity applies
2457 -- to the current bucket.
2458 -- ----------------------------------
2459 prev_uss_dos := activity_rec.dos;
2460 prev_uss_date := activity_rec.new_date;
2461 prev_uss_cost:= activity_rec.cost;
2462 prev_uss_q := -1;
2463 END IF;
2464 END IF;
2465
2466 IF activity_rec.row_type = USS
2467 and activity_rec.new_quantity is not null THEN
2468 -- --------------------------
2469 -- Got a safety stock record.
2470 -- --------------------------
2471 uss_q := activity_rec.new_quantity;
2472 uss_date := activity_rec.new_date;
2473 uss_cost := activity_rec.cost;
2474 uss_dos := -1;
2475
2476 IF activity_rec.new_date < var_dates(bucket_counter) THEN
2477 -- dbms_output.put_line('activity_rec.new_date ' || activity_rec.new_date ||
2478 -- ' var_dates(bucket_counter) ' || var_dates(bucket_counter) ||
2482 -- to the current bucket.
2479 -- ' bucket counter ' || bucket_counter);
2480 -- ----------------------------------
2481 -- This safety stock quantity applies
2483 -- ----------------------------------
2484 prev_uss_q := activity_rec.new_quantity;
2485 prev_uss_date := activity_rec.new_date;
2486 prev_uss_cost := activity_rec.cost;
2487 prev_uss_dos := -1;
2488 END IF;
2489 END IF;
2490
2491 IF (bucket_counter <= g_num_of_buckets AND
2492 activity_rec.new_date >= var_dates(bucket_counter)) THEN
2493 -- -------------------------------------------------------
2494 -- We got an activity falls after the current bucket. So we
2495 -- will move the bucket counter forward until we find the
2496 -- bucket where this activity falls. Note that we should
2497 -- not advance the counter bejond g_num_of_buckets.
2498 -- --------------------------------------------------------
2499 -- dbms_output.put_line( 'off ' || activity_rec.offset ||
2500 -- 'num buckets ' || g_num_of_buckets ||
2501 -- 'var_dates date ' || var_dates(bucket_counter) ||
2502 -- 'activity_rec date ' || activity_rec.new_date);
2503
2504 WHILE (bucket_counter <= g_num_of_buckets AND
2505 activity_rec.new_date >= var_dates(bucket_counter)) LOOP
2506 -- dbms_output.put_line('in loop - ' || var_dates(bucket_counter));
2507 -- if (bucket_counter > g_num_of_buckets - 50) then
2508 -- dbms_output.put_line( bucket_counter || ' ' ||
2509 -- to_char(var_dates(bucket_counter)));
2510 -- end if;
2511 -- -----------------------------------------------------
2512 -- If the variable last_ss_quantity is not -1 then there
2513 -- is a safety stock entry that we need to add for the
2514 -- current bucket before we move the bucket counter
2515 -- forward.
2516 -- -----------------------------------------------------
2517
2518 /* nsinghi: Once a safety stock value is got, that value needs to be put for all the remaining bucket
2519 days for that item. Suppose we find safety stock type txn at bucket 10. Suppose that there is
2520 one more txn remaining for the current item at bucket 14 and the report is for 30 days
2521 altogether. For bucket 10, the safety stock would already have been inserted. Bucket counter will now be
2522 at 10. Now when we get another txn for the item at bucket 14, we start moving bucket counter forward.
2523 But before we move forward, we need to insert safety stock value for buckets 11,12,13 and 14. Inserting
2524 safety stock for bucket 11,12,13 and 14 taken care by code below. Once after txn for item at bucket 14
2525 is inserted and the item changes, the code above will insert the safety stock from bucket 14 to 30.
2526 Whenever safety stock is mentioned, it means any of the txn of safety stock type like SS_OFF, SSunc_OFF,
2527 NON_POOL_SS_OFF etc which are valid for each day.
2528 */
2529
2530 IF prev_ss_quantity <> -1 THEN
2531 add_to_plan(bucket_counter -1,
2532 SS_OFF,
2533 prev_ss_quantity);
2534 add_to_plan(bucket_counter -1,
2535 SS_val_OFF,
2536 prev_ss_cost);
2537 add_to_plan(bucket_counter -1,
2538 ss_dos_off,
2539 prev_ss_dos);
2540 END IF;
2541
2542 IF prev_ssunc_q <> -1 THEN
2543
2544 add_to_plan(bucket_counter -1,
2545 SSunc_OFF,
2546 prev_ssunc_q);
2547 add_to_plan(bucket_counter -1,
2548 SSunc_val_OFF,
2549 prev_ssunc_cost);
2550 add_to_plan(bucket_counter -1,
2551 ssunc_dos_off,
2552 prev_ssunc_dos);
2553 add_to_plan(bucket_counter -1,
2554 NON_POOL_SS_OFF,
2555 prev_non_pool_ss);
2556 END IF;
2557
2558
2559 IF prev_non_pool_ss <> -1 THEN
2560 add_to_plan(bucket_counter -1,
2561 NON_POOL_SS_OFF,
2562 prev_non_pool_ss);
2563 END IF;
2564
2565 IF prev_uss_q <> -1 THEN
2566
2567 add_to_plan(bucket_counter -1,
2568 uSS_OFF,
2569 prev_uss_q);
2570 add_to_plan(bucket_counter -1,
2571 uSS_val_OFF,
2572 prev_uss_cost);
2573 add_to_plan(bucket_counter -1,
2574 uss_dos_off,
2575 0);
2576 END IF;
2577
2578 IF prev_mad <> -1 THEN
2579
2580
2581 add_to_plan(bucket_counter -1,
2582 MAD_OFF,
2583 prev_mad);
2584 add_to_plan(bucket_counter -1,
2585 MAPE_OFF,
2586 prev_mape);
2587 END IF;
2588
2589 IF prev_target_level <> -1 THEN
2590
2591
2595 END IF;
2592 add_to_plan(bucket_counter -1,
2593 TARGET_SER_OFF,
2594 prev_target_level);
2596
2597 IF prev_achieved_level <> -1 THEN
2598
2599 add_to_plan(bucket_counter -1,
2600 ACHIEVED_SER_OFF,
2601 prev_achieved_level);
2602 END IF;
2603
2604 IF prev_manf_vari <> -1 THEN
2605
2606
2607 add_to_plan(bucket_counter -1,
2608 MANF_VARI_OFF,
2609 prev_manf_vari);
2610
2611 add_to_plan(bucket_counter -1,
2612 PURC_VARI_OFF,
2613 prev_purc_vari);
2614
2615 add_to_plan(bucket_counter -1,
2616 TRAN_VARI_OFF,
2617 prev_tran_vari);
2618
2619 add_to_plan(bucket_counter -1,
2620 DMND_VARI_OFF,
2621 prev_dmnd_vari);
2622 END IF;
2623
2624 IF prev_uss_dos <> -1 THEN
2625
2626 add_to_plan(bucket_counter -1,
2627 uSS_OFF,
2628 0);
2629 add_to_plan(bucket_counter -1,
2630 uSS_val_OFF,
2631 0);
2632 add_to_plan(bucket_counter -1,
2633 uss_dos_off,
2634 prev_uss_dos);
2635 END IF;
2636
2637 bucket_counter := bucket_counter + 1;
2638
2639 END LOOP;
2640
2641 IF activity_rec.row_type = SS THEN
2642 -- init_prev_ss_qty;
2643 prev_ss_quantity := activity_rec.new_quantity;
2644 prev_ss_dos := activity_rec.dos;
2645 prev_ss_cost := activity_rec.cost;
2646 END IF;
2647
2648 prev_ssunc_q := ssunc_q;
2649 prev_ssunc_dos := ssunc_dos;
2650 prev_ssunc_date := ssunc_date;
2651 prev_uss_q := uss_q;
2652 prev_uss_dos := uss_dos;
2653 prev_uss_date := uss_date;
2654 prev_ssunc_cost := ssunc_cost;
2655 prev_uss_cost := uss_cost;
2656
2657 prev_manf_vari := manf_vari;
2658 prev_purc_vari := purc_vari;
2659 prev_tran_vari := tran_vari;
2660 prev_dmnd_vari := dmnd_vari;
2661
2662 prev_target_level := target_level;
2663 prev_achieved_level := achieved_level;
2664
2665 prev_mad := mad;
2666 prev_mape := mape;
2667
2668 prev_vari_date := vari_date;
2669
2670 END IF;
2671
2672 -- ---------------------------------------------------------
2673 -- Add the retrieved activity to the plan if it falls in the
2674 -- current bucket and it is not a safety stock entry.
2675 -- ---------------------------------------------------------
2676
2677 /* Since the safety stock is already entered, so no need to enter again. For a non SRO plan (guess it means
2678 optimized plan) there are only two types of safety stock viz., SS,SS_UNC. For SRO type plan, safety stock
2679 txn can be all types mentioned below. USS, SS_UNC etc. */
2680
2681 IF l_plan_type <> SRO_PLAN THEN
2682 IF (bucket_counter <= g_num_of_buckets AND
2683 activity_rec.new_date < var_dates(bucket_counter)) AND
2684 ( activity_rec.row_type NOT IN (SS,SS_UNC)) THEN
2685 add_to_plan(bucket_counter - 1,
2686 activity_rec.offset,
2687 activity_rec.new_quantity);
2688 END IF;
2689 ELSE
2690 IF (bucket_counter <= g_num_of_buckets AND
2691 activity_rec.new_date < var_dates(bucket_counter)) THEN
2692 IF (activity_rec.row_type <> USS AND activity_rec.row_type <> SS_UNC AND activity_rec.row_type <> SS AND
2693 activity_rec.row_type <> MANU_VARI AND activity_rec.row_type <> TARGET_SER_LVL AND
2694 activity_rec.row_type <> ACHIEVED_SER_LVL) THEN
2695
2696 add_to_plan(bucket_counter -1,
2697 activity_rec.offset,
2698 activity_rec.new_quantity);
2699 -- elsif activity_rec.row_type = USS then
2700 -- if (activity_rec.new_quantity is null) then
2701 -- add_to_plan(bucket_counter -1,
2702 -- uss_dos_off,
2703 -- activity_rec.dos);
2704 -- else
2705 -- add_to_plan(bucket_counter -1,
2706 -- uss_off,
2707 -- activity_rec.new_quantity);
2708 -- open standard_cost(
2709 -- last_item_id,
2710 -- last_inst_id,
2711 -- last_org_id,
2712 -- G_plan_id);
2713 -- fetch standard_cost into l_standard_cost;
2714 -- close standard_cost;
2715 -- add_to_plan(bucket_counter-1,
2716 -- USS_val_OFF,
2717 -- activity_rec.new_quantity*l_standard_cost);
2718 --
2719 -- end if;
2720 END IF;
2721 END IF;
2722 END IF;
2723
2724 -- -------------------------------------
2725 -- Add to the current schedule receipts.
2726 -- -------------------------------------
2727 /* nsinghi: everytime a supply type txn is recieved, add it to the schedule recipt list.
2728 Txns CURRENT_S will store information regarding any scheduled recipt. Since the txns query is
2729 not sorted based on old_date, so old_date can be greater than or less than bucket_date. So move
2730 the buckets either forward or backward to get the bucket corresponding to old_date. Hence two loops
2731 are present, which move the bucket forward and backward. Based on current bucket_date, either of the
2732 two loops will be the driving loop.
2733
2734 Txns CURRENT_S will contain the sum of all recipts for that bucket day. */
2735
2736 IF activity_rec.row_type IN (WIP, PO, REQ, TRANSIT,
2737 RECEIVING, PB_SUPPLY) THEN
2738 WHILE activity_rec.old_date >= var_dates(old_bucket_counter) AND
2739 old_bucket_counter <= g_num_of_buckets
2740 LOOP
2741 -- ----------
2742 -- move back.
2743 -- ----------
2744 old_bucket_counter := old_bucket_counter + 1;
2745
2746 END LOOP;
2747
2748 WHILE activity_rec.old_date < var_dates(old_bucket_counter - 1) AND
2749 old_bucket_counter > 2
2750 LOOP
2751 -- -------------
2752 -- move forward.
2753 -- -------------
2754 old_bucket_counter := old_bucket_counter - 1;
2755 END LOOP;
2756 IF activity_rec.old_date < var_dates(old_bucket_counter) THEN
2757 add_to_plan(old_bucket_counter - 1,
2758 CURRENT_S_OFF,
2759 activity_rec.old_quantity);
2760 END IF;
2761 END IF;
2762 -- END IF; -- if not enterprise_view
2763 last_item_id := activity_rec.item_id;
2764 last_org_id := activity_rec.org_id;
2765 -- last_inst_id := activity_rec.inst_id;
2766 END LOOP;
2767
2768 g_error_stmt := 'Debug - populate_horizontal_plan - 50';
2769 CLOSE mrp_snapshot_activity;
2770
2771 -- INSERT INTO temp_gmp_horizontal_pdr_gtmp SELECT * FROM gmp_horizontal_pdr_gtmp;
2772
2773 EXCEPTION
2774
2775 WHEN OTHERS THEN
2776 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in package GMP_HORIZONTAL_PDR_PKG '||sqlerrm);
2777
2778 END populate_horizontal_plan;
2779
2780 END GMP_HORIZONTAL_PDR_PKG;