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