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