[Home] [Help]
PACKAGE BODY: APPS.MRP_HORIZONTAL_PLAN_SC
Source
1 PACKAGE BODY MRP_HORIZONTAL_PLAN_SC AS
2 /* $Header: MRPPHOPB.pls 120.9 2008/01/13 23:34:05 schaudha ship $ */
3
4 SYS_YES CONSTANT INTEGER := 1;
5 SYS_NO CONSTANT INTEGER := 2;
6
7 PURCHASE_ORDER CONSTANT INTEGER := 1; /* order type lookup */
8 PURCH_REQ CONSTANT INTEGER := 2;
9 WORK_ORDER CONSTANT INTEGER := 3;
10 REPETITIVE_SCHEDULE CONSTANT INTEGER := 4;
11 PLANNED_ORDER CONSTANT INTEGER := 5;
12 MATERIAL_TRANSFER CONSTANT INTEGER := 6;
13 NONSTD_JOB CONSTANT INTEGER := 7;
14 RECEIPT_PURCH_ORDER CONSTANT INTEGER := 8;
15 REQUIREMENT CONSTANT INTEGER := 9;
16 FPO_SUPPLY CONSTANT INTEGER := 10;
17 SHIPMENT CONSTANT INTEGER := 11;
18 RECEIPT_SHIPMENT CONSTANT INTEGER := 12;
19 DIS_JOB_BY CONSTANT INTEGER := 14;
20 NON_ST_JOB_BY CONSTANT INTEGER := 15;
21 REP_SCHED_BY CONSTANT INTEGER := 16;
22 PLANNED_BY CONSTANT INTEGER := 17;
23 FLOW_SCHED CONSTANT INTEGER := 27;
24 FLOW_SCHED_BY CONSTANT INTEGER := 28;
25 PAYBACK_SUPPLY CONSTANT INTEGER :=29;
26
27
28 SALES CONSTANT INTEGER := 10; /* horizontal plan type lookup */
29 FORECAST CONSTANT INTEGER := 20;
30 DEPENDENT CONSTANT INTEGER := 30;
31 SCRAP CONSTANT INTEGER := 40;
32 PB_DEMAND CONSTANT INTEGER := 45;
33 OTHER CONSTANT INTEGER := 50;
34 GROSS CONSTANT INTEGER := 70;
35 WIP CONSTANT INTEGER := 81;
36 FLOW_SCHEDULE CONSTANT INTEGER := 82;
37 PO CONSTANT INTEGER := 83;
38 REQ CONSTANT INTEGER := 85;
39 TRANSIT CONSTANT INTEGER := 87;
40 RECEIVING CONSTANT INTEGER := 89;
41 PLANNED CONSTANT INTEGER := 90;
42 PB_SUPPLY CONSTANT INTEGER := 95;
43 SUPPLY CONSTANT INTEGER := 100;
44 ON_HAND CONSTANT INTEGER := 105;
45 PAB CONSTANT INTEGER := 110;
46 SS CONSTANT INTEGER := 120;
47 ATP CONSTANT INTEGER := 130;
48 CURRENT_S CONSTANT INTEGER := 140;
49 POH CONSTANT INTEGER := 150;
50 EXP_LOT CONSTANT INTEGER := 160;
51 TARGET_SS CONSTANT INTEGER := 125;
52
53 SALES_OFF CONSTANT INTEGER := 1; /* offsets */
54 FORECAST_OFF CONSTANT INTEGER := 2;
55 DEPENDENT_OFF CONSTANT INTEGER := 3;
56 SCRAP_OFF CONSTANT INTEGER := 4;
57 PB_DEMAND_OFF CONSTANT INTEGER := 5;
58 OTHER_OFF CONSTANT INTEGER := 6;
59 GROSS_OFF CONSTANT INTEGER := 7;
60 WIP_OFF CONSTANT INTEGER := 8;
61 PO_OFF CONSTANT INTEGER := 9;
62 REQ_OFF CONSTANT INTEGER := 10;
63 TRANSIT_OFF CONSTANT INTEGER := 11;
64 RECEIVING_OFF CONSTANT INTEGER := 12;
65 PLANNED_OFF CONSTANT INTEGER := 13;
66 PB_SUPPLY_OFF CONSTANT INTEGER := 14;
67 SUPPLY_OFF CONSTANT INTEGER := 15;
68 ON_HAND_OFF CONSTANT INTEGER := 16;
69 PAB_OFF CONSTANT INTEGER := 17;
70 SS_OFF CONSTANT INTEGER := 18;
71 ATP_OFF CONSTANT INTEGER := 19;
72 CURRENT_S_OFF CONSTANT INTEGER := 20;
73 POH_OFF CONSTANT INTEGER := 21;
74 EXP_LOT_OFF CONSTANT INTEGER := 22;
75
76 NUM_OF_TYPES CONSTANT INTEGER := 22;
77 NUM_OF_COLUMNS CONSTANT INTEGER := 36;
78
79 /* WIP job status lookups */
80 JOB_UNRELEASED CONSTANT INTEGER := 1;
81 JOB_RELEASED CONSTANT INTEGER := 3;
82 JOB_COMPLETE CONSTANT INTEGER := 4;
83 JOB_HOLD CONSTANT INTEGER := 6;
84
85 /* Schedule Level */
86 UPDATED_SCHEDULE CONSTANT INTEGER := 2;
87
88 /* Schedule supply demand */
89 SCHEDULE_DEMAND CONSTANT INTEGER := 1;
90 SCHEDULE_SUPPLY CONSTANT INTEGER := 2;
91
92 /* Independent demand types for Current Data */
93 IDT_SCHEDULE CONSTANT INTEGER := 1;
94 IDT_FORECAST CONSTANT INTEGER := 2;
95
96 /* forecast buckets */
97 DAILY_BUCKET CONSTANT INTEGER := 1;
98 WEEKLY_BUCKET CONSTANT INTEGER := 2;
99 MONTHLY_BUCKET CONSTANT INTEGER := 3;
100
101 /* MRP demand types */
102 DEMAND_PLANNED_ORDER CONSTANT INTEGER := 1;
103 DEMAND_OPTIONAL CONSTANT INTEGER := 22;
104 DEMAND_PAYBACK CONSTANT INTEGER := 27;
105
106 /* Rounding control */
107 DO_ROUND CONSTANT INTEGER := 1;
108 DO_NOT_ROUND CONSTANT INTEGER := 2;
109
110 /* safety stock code */
111 NON_MRP_PCT CONSTANT INTEGER := 1;
112
113 /* lot control */
114 FULL_LOT_CONTROL CONSTANT INTEGER := 2;
115
116 /* sub inventory type */
117 NETTABLE CONSTANT INTEGER := 1;
118
119 /*MTL_DEMAND types */
120 SALES_ORDER CONSTANT INTEGER := 2;
121
122 /* input designator type */
123 MDS_DESIGNATOR_TYPE CONSTANT INTEGER := 1;
124
125
126 function compute_daily_rate_t
127 (var_calendar_code varchar2,
128 var_exception_set_id number,
129 var_daily_production_rate number,
130 var_quantity_completed number,
131 fucd date,
132 var_date date) return number is
133 var_tot_completed number ;
134 var_diff number ;
135 var_change number ;
136 var_prior_completed number ;
137 var_ret_val number ;
138 var_fucd_seq number ;
139 var_lucd_seq number ;
140 Begin
141 select cal.prior_seq_num
142 into var_fucd_seq
143 FROM bom_calendar_dates cal
144 WHERE cal.exception_set_id = var_exception_set_id
145 AND cal.calendar_code = var_calendar_code
146 AND cal.calendar_date = TRUNC(fucd) ;
147
148 select cal.prior_seq_num
149 into var_lucd_seq
150 FROM bom_calendar_dates cal
151 WHERE cal.exception_set_id = var_exception_set_id
152 AND cal.calendar_code = var_calendar_code
153 AND cal.calendar_date = TRUNC(var_date) ;
154
155 var_diff := ABS(var_fucd_seq - var_lucd_seq) + 1 ;
156
157 var_tot_completed := var_diff * var_daily_production_rate ;
158
159 if (var_tot_completed <= var_quantity_completed)
160 then
161 var_ret_val := 0;
162 return(var_ret_val);
163 end if;
164
165 var_prior_completed := var_daily_production_rate * (var_diff -1) ;
166
167 var_change := var_quantity_completed - var_prior_completed ;
168
169 if (var_change > 0 )
170 then
171 var_ret_val := var_daily_production_rate - var_change ;
172 else
173 var_ret_val := var_daily_production_rate ;
174 end if;
175
176 return(var_ret_val);
177 end ;
178
179 /* 2663505 - Removed defaulting of arg_current_data, arg_res_level
180 according to PL/SQL stds. since we always pass these parameters */
181
182 Procedure populate_horizontal_plan (item_list_id IN NUMBER,
183 arg_plan_id IN NUMBER,
184 arg_organization_id IN NUMBER,
185 arg_compile_designator IN VARCHAR2,
186 arg_plan_organization_id IN NUMBER,
187 arg_bucket_type IN NUMBER,
188 arg_cutoff_date IN DATE,
189 arg_current_data IN NUMBER,
190 arg_ind_demand_type IN NUMBER DEFAULT NULL,
191 arg_source_list_name IN VARCHAR2 DEFAULT NULL,
192 enterprize_view IN BOOLEAN,
193 arg_res_level IN NUMBER,
194 arg_resval1 IN VARCHAR2 DEFAULT NULL,
195 arg_resval2 IN NUMBER DEFAULT NULL) IS
196 -- -----------------------------------------
197 -- This cursor selects row type information.
198 -- -----------------------------------------
199 /* Added TARGET_SS in where clause for bug 1976800 */
200 /* Added lookup_code 25,97 for bug 3565869 */
201 CURSOR row_types IS
202 SELECT meaning,
203 lookup_code
204 FROM mfg_lookups
205 WHERE lookup_type = 'MRP_HORIZONTAL_PLAN_TYPE_SC'
206 AND lookup_code NOT IN (FLOW_SCHEDULE,TARGET_SS,25,97) /* Flow Schedules shown under WIP at
207 present. However there is an order
208 type defined for flow schedules.
209 This condition needs to be removed
210 in the future when flow schedules
211 will be shown separately in the
212 Horizontal Plan. */
213 ORDER BY lookup_code;
214
215 row_type_rec row_types%ROWTYPE;
216
217 last_date DATE;
218 sid NUMBER;
219 l_oe_install varchar2(5):='OE';
220
221
222 -- ------------------------------------------------------------
223 -- BUG # 2792927
224 -- Declare Global Variable to store the value
225 -- of the profile option - Inventory Supplier Consigned Enabled.
226 -- -------------------------------------------------------------
227
228 CURSOR mrp_current_activity_ont_I IS
229 ------------------------
230 --- WIP Discrete Jobs
231 -----------------------
232 SELECT
233 items.inventory_item_id item_id,
234 items.organization_id org_id,
235 WIP row_type,
236 WIP_OFF offset,
237 jobs.scheduled_completion_date new_date,
238 jobs.scheduled_completion_date old_date,
239 SUM(GREATEST( 0, (jobs.net_quantity - jobs.quantity_completed
240 - jobs.quantity_scrapped))) new_quantity,
241 SUM(GREATEST( 0, (jobs.net_quantity - jobs.quantity_completed
242 - jobs.quantity_scrapped))) old_quantity
243 FROM mrp_form_query list,
244 wip_discrete_jobs jobs,
245 mrp_system_items items,
246 mrp_sub_inventories msi
247 WHERE (arg_res_level = 1
248 OR (arg_res_level = 2
249 AND jobs.project_id is NULL)
250 OR (DECODE(arg_res_level,
251 3,nvl(mrp_get_project.planning_group(jobs.project_id),'-23453'),
252 4,nvl(to_char(jobs.project_id), '-23453'))
253 = nvl(arg_resval1,'-23453'))
254 OR (arg_res_level = 5
255 AND nvl(to_char(jobs.project_id), '-23453')
256 = nvl(arg_resval1,'-23453')
257 AND nvl(jobs.task_id, -23453) = nvl(arg_resval2, -23453)))
258 and jobs.organization_id = items.organization_id
259 and jobs.primary_item_id = items.inventory_item_id
260 and jobs.status_type IN (JOB_UNRELEASED,
261 JOB_RELEASED, JOB_COMPLETE, JOB_HOLD)
262 and jobs.scheduled_completion_date < last_date
263 and jobs.net_quantity > 0
264 and items.inventory_item_id = list.number1
265 and items.organization_id = list.number2
266 and items.compile_designator = list.char1
267 AND list.query_id = item_list_id
268 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
269 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
270 AND jobs.completion_subinventory = msi.sub_inventory_code(+)
271 AND NVL(msi.netting_type,1) = 1
272 GROUP BY
273 items.inventory_item_id,
274 items.organization_id,
275 WIP,
276 WIP_OFF,
277 jobs.scheduled_completion_date,
278 jobs.scheduled_completion_date
279 UNION ALL
280 --------------------------
281 --- Discrete Job Scrap
282 --------------------------
283 SELECT
284 items.inventory_item_id item_id,
285 items.organization_id org_id,
286 SCRAP row_type,
287 SCRAP_OFF offset,
288 jobs.scheduled_completion_date new_date,
289 jobs.scheduled_completion_date old_date,
290 SUM(GREATEST(0, (jobs.net_quantity - jobs.quantity_completed
291 - jobs.quantity_scrapped))*NVL(items.shrinkage_rate, 0)) new_quantity,
292 0 old_quantity
293 FROM mrp_form_query list,
294 wip_discrete_jobs jobs,
295 mrp_system_items items,
296 mrp_sub_inventories msi
297 WHERE (arg_res_level = 1
298 OR (arg_res_level = 2
299 AND jobs.project_id is NULL)
300 OR (DECODE(arg_res_level,
301 3,nvl(mrp_get_project.planning_group(jobs.project_id),'-23453'),
302 4,nvl(to_char(jobs.project_id), '-23453'))
303 = nvl(arg_resval1,'-23453'))
304 OR (arg_res_level = 5
305 AND nvl(to_char(jobs.project_id), '-23453')
306 = nvl(arg_resval1,'-23453')
307 AND nvl(jobs.task_id, -23453) = nvl(arg_resval2, -23453)))
308 and jobs.organization_id = items.organization_id
309 and jobs.primary_item_id = items.inventory_item_id
310 and jobs.status_type IN (JOB_UNRELEASED,
311 JOB_RELEASED, JOB_COMPLETE, JOB_HOLD)
312 and jobs.scheduled_completion_date < last_date
313 and jobs.net_quantity > 0
314 and items.inventory_item_id = list.number1
315 and items.organization_id = list.number2
316 and items.compile_designator = list.char1
317 AND list.query_id = item_list_id
318 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
319 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
320 AND jobs.completion_subinventory = msi.sub_inventory_code(+)
321 AND NVL(msi.netting_type,1) = 1
322 GROUP BY
323 items.inventory_item_id,
324 items.organization_id,
325 SCRAP,
326 SCRAP_OFF,
327 jobs.scheduled_completion_date,
328 jobs.scheduled_completion_date,
329 0
330 UNION ALL
331 -----------------------------
332 --- Discrete Job Requirements
333 -----------------------------
334 SELECT
335 items.inventory_item_id item_id,
336 items.organization_id org_id,
337 DEPENDENT row_type,
338 DEPENDENT_OFF offset,
339 ops.date_required new_date,
340 ops.date_required old_date,
341 SUM(ops.required_quantity - ops.quantity_issued -
342 (NVL(wo.cumulative_scrap_quantity,0)*NVL(ops.quantity_per_assembly, 1))
343 ) new_quantity,
344 0 old_quantity
345 FROM mrp_form_query list,
346 wip_operations wo,
347 wip_requirement_operations ops,
348 wip_discrete_jobs jobs,
349 mrp_system_items items,
350 mrp_sub_inventories msi
351 WHERE (arg_res_level = 1
352 OR (arg_res_level = 2
353 AND jobs.project_id is NULL)
354 OR (DECODE(arg_res_level,
355 3,nvl(mrp_get_project.planning_group(jobs.project_id),'-23453'),
356 4,nvl(to_char(jobs.project_id), '-23453'))
357 = nvl(arg_resval1,'-23453'))
358 OR (arg_res_level = 5
359 AND nvl(to_char(jobs.project_id), '-23453')
360 = nvl(arg_resval1,'-23453')
361 AND nvl(jobs.task_id, -23453) = nvl(arg_resval2, -23453)))
362 and ops.organization_id = items.organization_id
363 and ops.inventory_item_id = items.inventory_item_id
364 and ( NVL(ops.quantity_issued, 0) <
365 NVL(ops.mps_required_quantity, 0)
366 OR (ops.mps_required_quantity < 0 ))
367 and jobs.organization_id = ops.organization_id
368 and jobs.wip_entity_id = ops.wip_entity_id
369 and jobs.status_type IN (JOB_UNRELEASED,
370 JOB_RELEASED, JOB_COMPLETE, JOB_HOLD)
371 and ops.date_required < last_date
372 and ops.mrp_net_flag = SYS_YES
373 and ops.wip_supply_type <> 6
374 and items.inventory_item_id = list.number1
375 and items.organization_id = list.number2
376 and items.compile_designator = list.char1
377 AND list.query_id = item_list_id
378 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
379 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
380 AND jobs.completion_subinventory = msi.sub_inventory_code(+)
381 AND NVL(msi.netting_type,1) = 1
382 AND ops.wip_entity_id = wo.wip_entity_id (+)
383 AND ops.organization_id = wo.organization_id (+)
384 AND ops.operation_seq_num =wo.operation_seq_num (+)
385 AND NVL(ops.repetitive_schedule_id,0) = NVL(wo.repetitive_schedule_id (+) ,0)
386 GROUP BY
387 items.inventory_item_id,
388 items.organization_id,
389 DEPENDENT,
390 DEPENDENT_OFF,
391 ops.date_required,
392 ops.date_required,
393 0
394 UNION ALL
395 ---------------------------------
396 --- Flow Schedules
397 ---------------------------------
398 SELECT
399 items.inventory_item_id item_id,
400 items.organization_id org_id,
401 WIP row_type,
402 WIP_OFF offset,
403 flow_sched.scheduled_completion_date new_date,
404 flow_sched.scheduled_completion_date old_date,
405 SUM(GREATEST( 0, (flow_sched.planned_quantity -
406 flow_sched.quantity_completed))) new_quantity,
407 SUM(GREATEST( 0, (flow_sched.planned_quantity -
408 flow_sched.quantity_completed))) old_quantity
409 FROM mrp_form_query list,
410 wip_flow_schedules flow_sched,
411 mrp_system_items items,
412 mrp_sub_inventories msi
413 WHERE (arg_res_level = 1
414 OR (arg_res_level = 2
415 AND flow_sched.project_id is NULL)
416 OR (DECODE(arg_res_level,
417 3,nvl(mrp_get_project.planning_group(flow_sched.project_id),'-23453'),
418 4,nvl(to_char(flow_sched.project_id), '-23453'))
419 = nvl(arg_resval1,'-23453'))
420 OR (arg_res_level = 5
421 AND nvl(to_char(flow_sched.project_id), '-23453')
422 = nvl(arg_resval1,'-23453')
423 AND nvl(flow_sched.task_id, -23453) = nvl(arg_resval2, -23453)))
424 and flow_sched.organization_id = items.organization_id
425 and flow_sched.primary_item_id = items.inventory_item_id
426 and flow_sched.scheduled_completion_date < last_date
427 and flow_sched.scheduled_completion_date >= trunc(sysdate)
428 and flow_sched.planned_quantity > 0
429 and items.inventory_item_id = list.number1
430 and items.organization_id = list.number2
431 and items.compile_designator = list.char1
432 AND list.query_id = item_list_id
433 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
434 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
435 AND flow_sched.completion_subinventory = msi.sub_inventory_code(+)
436 AND NVL(msi.netting_type,1) = 1
437 GROUP BY
438 items.inventory_item_id,
439 items.organization_id,
440 WIP,
441 WIP_OFF,
442 flow_sched.scheduled_completion_date,
443 flow_sched.scheduled_completion_date
444 UNION ALL
445 -----------------------------------
446 ---- Flow schedule demand
447 ----------------------------------
448 SELECT
449 items.inventory_item_id item_id,
450 items.organization_id org_id,
451 DEPENDENT type,
452 DEPENDENT_OFF offset,
453 dates.calendar_date new_date,
454 dates.calendar_date old_date,
455 SUM(GREATEST(0, ((nvl(fs.planned_quantity, 0) -
456 nvl(fs.quantity_completed, 0)) *
457 (bic.component_quantity/bic.component_yield_factor)
458 * bic.planning_factor/100))) new_quantity,
459 SUM(GREATEST(0, ((nvl(fs.planned_quantity, 0) -
460 nvl(fs.quantity_completed, 0)) *
461 (bic.component_quantity/bic.component_yield_factor)
462 * bic.planning_factor/100))) old_quantity
463 FROM bom_calendar_dates dates,
464 mtl_parameters mp,
465 wip_flow_schedules fs,
466 mrp_system_items msi_assy,
467 bom_bill_of_materials bbm,
468 bom_inventory_components bic,
469 mrp_system_items items,
470 mrp_form_query list,
471 mrp_sub_inventories msi
472 WHERE (arg_res_level = 1
473 OR (arg_res_level = 2
474 AND fs.project_id is NULL)
475 OR (DECODE(arg_res_level,
476 3,nvl(mrp_get_project.planning_group(fs.project_id),'-23453'),
477 4,nvl(to_char(fs.project_id), '-23453'))
478 = nvl(arg_resval1,'-23453'))
479 OR (arg_res_level = 5
480 AND nvl(to_char(fs.project_id), '-23453')
481 = nvl(arg_resval1,'-23453')
482 AND nvl(fs.task_id, -23453) = nvl(arg_resval2, -23453)))
483 AND dates.seq_num is not null
484 AND dates.calendar_date < last_date
485 AND dates.seq_num =
486 (select c2.prior_seq_num -
487 ceil((1- nvl(bic.operation_lead_time_percent, 0)/100) *
488 (nvl(msi_assy.fixed_lead_time, 0) +
489 ((fs.planned_quantity - fs.quantity_completed) *
490 nvl(msi_assy.variable_lead_time, 0))))
491 from bom_calendar_dates c2
492 where c2.calendar_code = mp.calendar_code
493 and c2.exception_set_id = mp.calendar_exception_set_id
494 and c2.calendar_date =
495 trunc(fs.scheduled_completion_date)
496 )
497 AND dates.exception_set_id = mp.calendar_exception_set_id
498 AND dates.calendar_code = mp.calendar_code
499 AND mp.organization_id = msi_assy.organization_id
500 AND fs.planned_quantity > 0
501 AND fs.scheduled_completion_date >= TRUNC(SYSDATE)
502 AND nvl(fs.alternate_bom_designator, '-23453') =
503 nvl(bbm.alternate_bom_designator, '-23453')
504 AND fs.organization_id = msi_assy.organization_id
505 AND fs.primary_item_id = msi_assy.inventory_item_id
506 AND msi_assy.compile_designator = items.compile_designator
507 AND msi_assy.organization_id = bbm.organization_id
508 AND msi_assy.inventory_item_id = bbm.assembly_item_id
509 AND bbm.common_bill_sequence_id = bic.bill_sequence_id
510 AND bic.effectivity_date <= last_date
511 AND bic.component_item_id = items.inventory_item_id
512 AND items.inventory_item_id = list.number1
513 AND items.organization_id = list.number2
514 AND items.compile_designator = list.char1
515 AND list.query_id = item_list_id
516 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
517 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
518 AND fs.completion_subinventory = msi.sub_inventory_code(+)
519 AND NVL(msi.netting_type,1) = 1
520 GROUP BY
521 items.inventory_item_id,
522 items.organization_id,
523 DEPENDENT,
524 DEPENDENT_OFF,
525 dates.calendar_date,
526 dates.calendar_date
527 UNION ALL
528 ----------------------------------
529 ---- Flow Schedule By Product
530 ----------------------------------
531 SELECT
532 items.inventory_item_id item_id,
533 items.organization_id org_id,
534 DEPENDENT type,
535 DEPENDENT_OFF offset,
536 dates.calendar_date new_date,
537 dates.calendar_date old_date,
538 SUM(((nvl(fs.planned_quantity, 0) -
539 nvl(fs.quantity_completed, 0)) *
540 (bic.component_quantity/bic.component_yield_factor)
541 * bic.planning_factor/100)) new_quantity,
542 SUM(((nvl(fs.planned_quantity, 0) -
543 nvl(fs.quantity_completed, 0)) *
544 (bic.component_quantity/bic.component_yield_factor)
545 * bic.planning_factor/100)) old_quantity
546 FROM bom_calendar_dates dates,
547 mtl_parameters mp,
548 wip_flow_schedules fs,
549 mrp_system_items msi_assy,
550 bom_bill_of_materials bbm,
551 bom_inventory_components bic,
552 mrp_system_items items,
553 mrp_form_query list,
554 mrp_sub_inventories msi
555 WHERE (arg_res_level = 1
556 OR (arg_res_level = 2
557 AND fs.project_id is NULL)
558 OR (DECODE(arg_res_level,
559 3,nvl(mrp_get_project.planning_group(fs.project_id),'-23453'),
560 4,nvl(to_char(fs.project_id), '-23453'))
561 = nvl(arg_resval1,'-23453'))
562 OR (arg_res_level = 5
563 AND nvl(to_char(fs.project_id), '-23453')
564 = nvl(arg_resval1,'-23453')
565 AND nvl(fs.task_id, -23453) = nvl(arg_resval2, -23453)))
566 AND dates.seq_num is not null
567 AND dates.calendar_date < last_date
568 AND dates.seq_num =
569 (select c2.prior_seq_num -
570 ceil((1- nvl(bic.operation_lead_time_percent, 0)/100) *
571 (nvl(msi_assy.fixed_lead_time, 0) +
572 ((fs.planned_quantity - fs.quantity_completed) *
573 nvl(msi_assy.variable_lead_time, 0))))
574 from bom_calendar_dates c2
575 where c2.calendar_code = mp.calendar_code
576 and c2.exception_set_id = mp.calendar_exception_set_id
577 and c2.calendar_date =
578 trunc(fs.scheduled_completion_date)
579 )
580 AND dates.exception_set_id = mp.calendar_exception_set_id
581 AND dates.calendar_code = mp.calendar_code
582 AND mp.organization_id = msi_assy.organization_id
583 AND fs.planned_quantity > 0
584 AND fs.scheduled_completion_date >= TRUNC(SYSDATE)
585 AND nvl(fs.alternate_bom_designator, '-23453') =
586 nvl(bbm.alternate_bom_designator, '-23453')
587 AND fs.organization_id = msi_assy.organization_id
588 AND fs.primary_item_id = msi_assy.inventory_item_id
589 AND msi_assy.compile_designator = items.compile_designator
590 AND msi_assy.organization_id = bbm.organization_id
591 AND msi_assy.inventory_item_id = bbm.assembly_item_id
592 AND bic.component_quantity < 0
593 AND bbm.common_bill_sequence_id = bic.bill_sequence_id
594 AND bic.effectivity_date <= last_date
595 AND bic.component_item_id = items.inventory_item_id
596 AND items.inventory_item_id = list.number1
597 AND items.organization_id = list.number2
598 AND items.compile_designator = list.char1
599 AND list.query_id = item_list_id
600 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
601 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
602 AND fs.completion_subinventory = msi.sub_inventory_code(+)
603 AND NVL(msi.netting_type,1) = 1
604 GROUP BY
605 items.inventory_item_id,
606 items.organization_id,
607 DEPENDENT,
608 DEPENDENT_OFF,
609 dates.calendar_date,
610 dates.calendar_date
611 UNION ALL
612 ---------------------------------
613 --- Flow Schedule Scrap
614 ---------------------------------
615 SELECT
616 items.inventory_item_id item_id,
617 items.organization_id org_id,
618 SCRAP row_type,
619 SCRAP_OFF offset,
620 flow_sched.scheduled_completion_date new_date,
621 flow_sched.scheduled_completion_date old_date,
622 SUM(GREATEST( 0, ((flow_sched.planned_quantity -
623 flow_sched.quantity_completed) *
624 nvl(items.shrinkage_rate, 0)))) new_quantity,
625 SUM(GREATEST( 0, ((flow_sched.planned_quantity -
626 flow_sched.quantity_completed) *
627 nvl(items.shrinkage_rate, 0)))) old_quantity
628 FROM wip_flow_schedules flow_sched,
629 mrp_system_items items,
630 mrp_form_query list,
631 mrp_sub_inventories msi
632 WHERE (arg_res_level = 1
633 OR (arg_res_level = 2
634 AND flow_sched.project_id is NULL)
635 OR (DECODE(arg_res_level,
636 3,nvl(mrp_get_project.planning_group(flow_sched.project_id),'-23453'),
637 4,nvl(to_char(flow_sched.project_id), '-23453'))
638 = nvl(arg_resval1,'-23453'))
639 OR (arg_res_level = 5
640 AND nvl(to_char(flow_sched.project_id), '-23453')
641 = nvl(arg_resval1,'-23453')
642 AND nvl(flow_sched.task_id, -23453) = nvl(arg_resval2, -23453)))
643 AND flow_sched.organization_id = items.organization_id
644 AND flow_sched.primary_item_id = items.inventory_item_id
645 AND flow_sched.scheduled_completion_date < last_date
646 AND flow_sched.scheduled_completion_date >= TRUNC(sysdate)
647 AND flow_sched.planned_quantity > 0
648 AND items.inventory_item_id = list.number1
649 AND items.organization_id = list.number2
650 AND items.compile_designator = list.char1
651 AND list.query_id = item_list_id
652 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
653 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
654 AND flow_sched.completion_subinventory = msi.sub_inventory_code(+)
655 AND NVL(msi.netting_type,1) = 1
656 GROUP BY
657 items.inventory_item_id,
658 items.organization_id,
659 SCRAP,
660 SCRAP_OFF,
661 flow_sched.scheduled_completion_date,
662 flow_sched.scheduled_completion_date
663 UNION ALL
664 ----------------------------------
665 --- Current repetitive schedules
666 ----------------------------------
667 SELECT
668 items.inventory_item_id item_id,
669 items.organization_id org_id,
670 CURRENT_S row_type,
671 CURRENT_S_OFF offset,
672 dates.calendar_date new_date,
673 dates.calendar_date old_date,
674 SUM(MRP_HORIZONTAL_PLAN_SC.compute_daily_rate_t(dates.calendar_code, dates.exception_set_id,
675 sched.daily_production_rate, sched.quantity_completed,
676 sched.first_unit_completion_date, dates.calendar_date )) new_quantity ,
677 SUM(MRP_HORIZONTAL_PLAN_SC.compute_daily_rate_t(dates.calendar_code, dates.exception_set_id,
678 sched.daily_production_rate, sched.quantity_completed,
679 sched.first_unit_completion_date, dates.calendar_date )) old_quantity
680 FROM mrp_form_query list,
681 bom_calendar_dates dates,
682 mtl_parameters param,
683 wip_repetitive_schedules sched,
684 wip_repetitive_items rep_items,
685 mrp_system_items items,
686 mrp_sub_inventories msi
687 WHERE rep_items.primary_item_id = items.inventory_item_id
688 and rep_items.organization_id = items.organization_id
689 and rep_items.wip_entity_id = sched.wip_entity_id
690 and rep_items.line_id = sched.line_id
691 and sched.organization_id = items.organization_id
692 and sched.status_type IN (JOB_UNRELEASED,
693 JOB_RELEASED, JOB_COMPLETE, JOB_HOLD)
694 and dates.seq_num is not null
695 and TRUNC(dates.calendar_date)
696 >= TRUNC(sched.first_unit_completion_date)
697 and TRUNC(dates.calendar_date)
698 <= (select trunc(cal.calendar_date - 1)
699 from bom_calendar_dates cal
700 where cal.exception_set_id = dates.exception_set_id
701 and cal.calendar_code = dates.calendar_code
702 and cal.seq_num = (select cal1.prior_seq_num + ceil(sched.processing_work_days)
703 from bom_calendar_dates cal1
704 where cal1.exception_set_id = dates.exception_set_id
705 and cal1.calendar_code = dates.calendar_code
706 and cal1.calendar_date = TRUNC(sched.first_unit_completion_date)) )
707 and dates.calendar_date < last_date
708 and dates.exception_set_id = param.calendar_exception_set_id
709 and dates.calendar_code = param.calendar_code
710 and param.organization_id = items.organization_id
711 and items.inventory_item_id = list.number1
712 and items.organization_id = list.number2
713 and items.compile_designator = list.char1
714 AND list.query_id = item_list_id
715 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
716 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
717 AND rep_items.completion_subinventory = msi.sub_inventory_code(+)
718 AND NVL(msi.netting_type,1) = 1
719 GROUP BY
720 items.inventory_item_id,
721 items.organization_id,
722 CURRENT_S,
723 CURRENT_S_OFF,
724 dates.calendar_date,
725 dates.calendar_date
726 UNION ALL
727 --------------------------------------
728 --- Current repetitive schedule scrap
729 --------------------------------------
730 SELECT
731 items.inventory_item_id item_id,
732 items.organization_id org_id,
733 SCRAP row_type,
734 SCRAP_OFF offset,
735 dates.calendar_date new_date,
736 dates.calendar_date old_date,
737 SUM(sched.daily_production_rate*NVL(items.shrinkage_rate, 0))
738 new_quantity,
739 0 old_quantity
740 FROM mrp_form_query list,
741 bom_calendar_dates dates,
742 mtl_parameters param,
743 wip_repetitive_schedules sched,
744 wip_repetitive_items rep_items,
745 mrp_system_items items,
746 mrp_sub_inventories msi
747 WHERE rep_items.primary_item_id = items.inventory_item_id
748 and rep_items.organization_id = items.organization_id
749 and rep_items.wip_entity_id = sched.wip_entity_id
750 and rep_items.line_id = sched.line_id
751 and sched.organization_id = items.organization_id
752 and sched.status_type IN (JOB_UNRELEASED,
753 JOB_RELEASED, JOB_COMPLETE, JOB_HOLD)
754 and dates.seq_num is not null
755 and TRUNC(dates.calendar_date)
756 >= TRUNC(sched.first_unit_completion_date)
757 and TRUNC(dates.calendar_date)
758 <= (select trunc(cal.calendar_date - 1)
759 from bom_calendar_dates cal
760 where cal.exception_set_id = dates.exception_set_id
761 and cal.calendar_code = dates.calendar_code
762 and cal.seq_num = (select cal1.prior_seq_num + ceil(sched.processing_work_days)
763 from bom_calendar_dates cal1
764 where cal1.exception_set_id = dates.exception_set_id
765 and cal1.calendar_code = dates.calendar_code
766 and cal1.calendar_date = TRUNC(sched.first_unit_completion_date)) )
767 and dates.calendar_date < last_date
768 and dates.exception_set_id = param.calendar_exception_set_id
769 and dates.calendar_code = param.calendar_code
770 and param.organization_id = items.organization_id
771 and items.inventory_item_id = list.number1
772 and items.organization_id = list.number2
773 and items.compile_designator = list.char1
774 AND list.query_id = item_list_id
775 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
776 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
777 AND rep_items.completion_subinventory = msi.sub_inventory_code(+)
778 AND NVL(msi.netting_type,1) = 1
779 GROUP BY
780 items.inventory_item_id,
781 items.organization_id,
782 SCRAP,
783 SCRAP_OFF,
784 dates.calendar_date,
785 dates.calendar_date,
786 0
787 UNION ALL
788 ----------------------------------------------
789 --- Current Repetitive Schedule requirements
790 ----------------------------------------------
791 SELECT
792 items.inventory_item_id item_id,
793 items.organization_id org_id,
794 DEPENDENT row_type,
795 DEPENDENT_OFF offset,
796 ops.date_required new_date,
797 ops.date_required old_date,
798 SUM(ops.required_quantity - ops.quantity_issued) new_quantity,
799 0 old_quantity
800 FROM mrp_form_query list,
801 wip_requirement_operations ops,
802 wip_repetitive_schedules sched,
803 mrp_system_items items,
804 WIP_REPETITIVE_ITEMS rep_items,
805 mrp_sub_inventories msi
806 WHERE ops.organization_id = items.organization_id
807 and ops.inventory_item_id = items.inventory_item_id
808 and ops.mrp_net_flag = SYS_YES
809 and ops.wip_supply_type <> 6
810 and NVL(ops.quantity_issued, 0) <
811 NVL(ops.required_quantity, 0)
812 and sched.organization_id = ops.organization_id
813 and sched.wip_entity_id = ops.wip_entity_id
814 and sched.repetitive_schedule_id = ops.repetitive_schedule_id
815 and sched.organization_id = items.organization_id
816 and sched.status_type IN (JOB_UNRELEASED,
817 JOB_RELEASED, JOB_COMPLETE, JOB_HOLD)
818 and ops.date_required < last_date
819 and items.inventory_item_id = list.number1
820 and items.organization_id = list.number2
821 and items.compile_designator = list.char1
822 AND list.query_id = item_list_id
823 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
824 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
825 AND rep_items.completion_subinventory = msi.sub_inventory_code(+)
826 AND NVL(msi.netting_type,1) = 1
827 GROUP BY
828 items.inventory_item_id,
829 items.organization_id,
830 DEPENDENT,
831 DEPENDENT_OFF,
832 ops.date_required,
833 ops.date_required,
834 0
835 UNION ALL
836 --------------------------
837 --- MDS
838 -------------------------
839 SELECT
840 items.inventory_item_id item_id,
841 items.organization_id org_id,
842 DECODE(sched.schedule_origination_type,
843 1, OTHER,
844 2, FORECAST,
845 3, SALES,
846 4, OTHER,
847 6, OTHER,
848 7, OTHER,
849 8, FORECAST,
850 11, DEPENDENT) row_type,
851 DECODE(sched.schedule_origination_type,
852 1, OTHER_OFF,
853 2, FORECAST_OFF,
854 3, SALES_OFF,
855 4, OTHER_OFF,
856 6, OTHER_OFF,
857 7, OTHER_OFF,
858 8, FORECAST_OFF,
859 11, DEPENDENT_OFF) offset,
860 dates.calendar_date new_date,
861 dates.calendar_date old_date,
862 SUM(DECODE(sched.rate_end_date, NULL, sched.schedule_quantity,
863 sched.repetitive_daily_rate)) new_quantity,
864 0 old_quantity
865 FROM mrp_form_query list,
866 bom_calendar_dates dates,
867 mtl_parameters param,
868 mrp_schedule_dates sched,
869 mrp_plan_schedules_v plan_sched,
870 mrp_plans plans,
871 mrp_system_items items
872 WHERE (arg_res_level = 1
873 OR (arg_res_level = 2
874 AND sched.project_id is NULL)
875 OR (DECODE(arg_res_level,
876 3,nvl(mrp_get_project.planning_group(sched.project_id),'-23453'),
877 4,nvl(to_char(sched.project_id), '-23453'))
878 = nvl(arg_resval1,'-23453'))
879 OR (arg_res_level = 5
880 AND nvl(to_char(sched.project_id), '-23453')
881 = nvl(arg_resval1,'-23453')
882 AND nvl(sched.task_id, -23453) = nvl(arg_resval2, -23453)))
883 AND items.organization_id = NVL(plan_sched.input_organization_id,
884 plans.organization_id)
885 AND items.compile_designator = plans.compile_designator
886 AND items.inventory_item_id = sched.inventory_item_id
887 AND plan_sched.input_designator_type (+) = MDS_DESIGNATOR_TYPE
888 AND NVL(plan_sched.input_organization_id, plans.organization_id) =
889 sched.organization_id
890 AND NVL(plan_sched.input_designator_name, plans.curr_schedule_designator) =
891 sched.schedule_designator
892 AND plans.organization_id = plan_sched.organization_id (+)
893 AND plans.compile_designator = plan_sched.compile_designator (+)
894 AND sched.schedule_level = UPDATED_SCHEDULE
895 AND sched.supply_demand_type = SCHEDULE_DEMAND
896 AND dates.exception_set_id = param.calendar_exception_set_id
897 AND dates.calendar_code = param.calendar_code
898 --AND dates.seq_num is not null
899 AND (( sched.rate_end_date IS NOT NULL /* Repetitively planned item */
900 AND dates.seq_num IS NOT NULL )
901 OR
902 ( sched.rate_end_date IS NULL ))
903 AND dates.calendar_date BETWEEN sched.schedule_workdate
904 AND NVL(sched.rate_end_date, sched.schedule_workdate)
905 AND dates.calendar_date < last_date
906 AND param.organization_id = items.organization_id
907 AND items.inventory_item_id = list.number1
908 AND items.organization_id = list.number2
909 AND items.compile_designator = list.char1
910 AND arg_ind_demand_type = IDT_SCHEDULE
911 AND list.query_id = item_list_id
912 GROUP BY
913 items.inventory_item_id,
914 items.organization_id,
915 DECODE(sched.schedule_origination_type,
916 1, OTHER,
917 2, FORECAST,
918 3, SALES,
919 4, OTHER,
920 6, OTHER,
921 7, OTHER,
922 8, FORECAST,
923 11, DEPENDENT),
924 DECODE(sched.schedule_origination_type,
925 1, OTHER_OFF,
926 2, FORECAST_OFF,
927 3, SALES_OFF,
928 4, OTHER_OFF,
929 6, OTHER_OFF,
930 7, OTHER_OFF,
931 8, FORECAST_OFF,
932 11, DEPENDENT_OFF),
933 dates.calendar_date,
934 dates.calendar_date,
935 0
936 UNION ALL
937 -----------------------
938 --- Forecast Demand
939 -----------------------
940 SELECT
941 items.inventory_item_id item_id,
942 items.organization_id org_id,
943 FORECAST row_type,
944 FORECAST_OFF offset,
945 dates.calendar_date new_date,
946 dates.calendar_date old_date,
947 SUM(fcst.current_forecast_quantity) new_quantity,
948 0 old_quantity
949 FROM mrp_form_query list,
950 bom_calendar_dates dates,
951 mtl_parameters param,
952 mrp_forecast_dates fcst,
953 mrp_forecast_designators desig,
954 mrp_load_parameters load,
955 mrp_system_items items
956 WHERE (arg_res_level = 1
957 OR (arg_res_level = 2
958 AND fcst.project_id is NULL)
959 OR (DECODE(arg_res_level,
960 3,nvl(mrp_get_project.planning_group(fcst.project_id),'-23453'),
961 4,nvl(to_char(fcst.project_id), '-23453'))
962 = nvl(arg_resval1,'-23453'))
963 OR (arg_res_level = 5
964 AND nvl(to_char(fcst.project_id), '-23453')
965 = nvl(arg_resval1,'-23453')
966 AND nvl(fcst.task_id, -23453) = nvl(arg_resval2, -23453)))
967 AND fcst.organization_id = items.organization_id
968 AND fcst.inventory_item_id = items.inventory_item_id
969 AND dates.exception_set_id = param.calendar_exception_set_id
970 AND dates.calendar_code = param.calendar_code
971 --AND dates.seq_num is not null
972 AND (( fcst.rate_end_date IS NOT NULL /* Repetitively planned item */
973 AND dates.seq_num IS NOT NULL )
974 OR
975 ( fcst.rate_end_date IS NULL ))
976 AND ((dates.calendar_date BETWEEN fcst.forecast_date
977 AND NVL(fcst.rate_end_date, forecast_date)
978 AND fcst.bucket_type = DAILY_BUCKET)
979 OR
980 (dates.calendar_date BETWEEN fcst.forecast_date
981 AND NVL(fcst.rate_end_date, forecast_date)
982 AND fcst.bucket_type = WEEKLY_BUCKET
983 AND dates.calendar_date IN
984 (select week_start_date
985 from bom_cal_week_start_dates
986 where calendar_code = param.calendar_code
987 and exception_set_id = param.calendar_exception_set_id
988 and param.organization_id = list.number2 ))
989 OR
990 (dates.calendar_date BETWEEN fcst.forecast_date
991 AND NVL(fcst.rate_end_date, forecast_date)
992 AND fcst.bucket_type = MONTHLY_BUCKET
993 AND dates.calendar_date IN
994 (select period_start_date
995 from bom_period_start_dates
996 where calendar_code = param.calendar_code
997 and exception_set_id = param.calendar_exception_set_id
998 and param.organization_id = list.number2)))
999 AND dates.calendar_date < last_date
1000 AND param.organization_id = items.organization_id
1001 AND items.inventory_item_id = list.number1
1002 AND items.organization_id = list.number2
1003 AND items.compile_designator = list.char1
1004 AND arg_ind_demand_type = IDT_FORECAST
1005 AND fcst.current_forecast_quantity > 0
1006 AND fcst.organization_id = desig.organization_id
1007 AND fcst.forecast_designator = desig.forecast_designator
1008 AND ((desig.forecast_designator = load.source_forecast_designator)
1009 OR (desig.forecast_set = load.source_forecast_designator))
1010 AND desig.organization_id = load.source_organization_id
1011 AND load.selection_list_name = arg_source_list_name
1012 AND list.query_id = item_list_id
1013 GROUP BY
1014 items.inventory_item_id,
1015 items.organization_id,
1016 FORECAST,
1017 FORECAST_OFF,
1018 dates.calendar_date,
1019 dates.calendar_date,
1020 0
1021 UNION ALL
1022 ---------------
1023 --- PO Supply
1024 ---------------
1025 --------------------
1026 -- Purchase Orders
1027 --------------------
1028 SELECT
1029 ms.item_id item_id,
1030 ms.to_organization_id org_id,
1031 PO row_type,
1032 PO_OFF offset,
1033 mrp_calendar.date_offset(items.organization_id,1,ms.need_by_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) new_date,
1034 mrp_calendar.date_offset(items.organization_id,1,ms.need_by_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) old_date,
1035 SUM(ms.to_org_primary_quantity) new_quantity,
1036 SUM(ms.to_org_primary_quantity) old_quantity
1037 FROM po_distributions_all pd,
1038 mtl_supply ms,
1039 mrp_system_items items,
1040 mrp_form_query list,
1041 mrp_sub_inventories msi
1042 WHERE (arg_res_level = 1
1043 OR (arg_res_level = 2
1044 AND pd.project_id is NULL)
1045 OR (DECODE(arg_res_level,
1046 3,nvl(mrp_get_project.planning_group(pd.project_id),'-23453'),
1047 4,nvl(to_char(pd.project_id), '-23453'))
1048 = nvl(arg_resval1,'-23453'))
1049 OR (arg_res_level = 5
1050 AND nvl(to_char(pd.project_id), '-23453')
1051 = nvl(arg_resval1,'-23453')
1052 AND nvl(pd.task_id, -23453) = nvl(arg_resval2, -23453)))
1053 AND ms.expected_delivery_date < last_date
1054 AND pd.po_distribution_id = ms.po_distribution_id
1055 AND ms.destination_type_code = 'INVENTORY'
1056 AND ms.po_line_id is not null
1057 AND ms.item_id is not null
1058 AND ms.to_org_primary_quantity > 0
1059 AND ( ms.supply_type_code = 'PO' or
1060 ms.supply_type_code = 'ASN')
1061 AND items.inventory_item_id = ms.item_id
1062 AND items.organization_id = ms.to_organization_id
1063 AND ms.item_id = list.number1
1064 AND ms.to_organization_id = list.number2
1065 AND items.compile_designator = list.char1
1066 AND list.query_id = item_list_id
1067 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
1068 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
1069 AND pd.destination_subinventory = msi.sub_inventory_code(+)
1070 AND NVL(msi.netting_type,1) = 1
1071 AND NOT EXISTS (select 'y' FROM OE_DROP_SHIP_SOURCES ODSS
1072 WHERE ms.po_line_location_id = ODSS.line_location_id)
1073 GROUP BY
1074 ms.item_id,
1075 ms.to_organization_id,
1076 PO,
1077 PO_OFF,
1078 mrp_calendar.date_offset(items.organization_id,1,ms.need_by_date, CEIL(items.POSTPROCESSING_LEAD_TIME))
1079 UNION ALL
1080 ---------------------
1081 -- Intransit Shipment
1082 ----------------------
1083 SELECT
1084 ms.item_id item_id,
1085 ms.to_organization_id org_id,
1086 TRANSIT row_type,
1087 TRANSIT_OFF offset,
1088 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) new_date,
1089 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) old_date,
1090 SUM(nvl(ms.to_org_primary_quantity, 0) * pd.req_line_quantity/pl.quantity)
1091 new_quantity,
1092 SUM(nvl(ms.to_org_primary_quantity, 0) * pd.req_line_quantity/pl.quantity)
1093 old_quantity
1094 FROM po_req_distributions_all pd,
1095 po_requisition_lines_all pl,
1096 mrp_system_items items,
1097 mtl_supply ms,
1098 mrp_form_query list,
1099 mrp_sub_inventories msi
1100 WHERE (arg_res_level = 1
1101 OR (arg_res_level = 2
1102 AND pd.project_id is NULL)
1103 OR (DECODE(arg_res_level,
1104 3,nvl(mrp_get_project.planning_group(pd.project_id),'-23453'),
1105 4,nvl(to_char(pd.project_id), '-23453'))
1106 = nvl(arg_resval1,'-23453'))
1107 OR (arg_res_level = 5
1108 AND nvl(to_char(pd.project_id), '-23453')
1109 = nvl(arg_resval1,'-23453')
1110 AND nvl(pd.task_id, -23453) = nvl(arg_resval2, -23453)))
1111 AND pd.requisition_line_id = pl.requisition_line_id
1112 AND pl.quantity > 0
1113 AND pl.requisition_line_id = ms.req_line_id
1114 AND ms.expected_delivery_date < last_date
1115 AND ms.req_line_id is not null
1116 AND ms.shipment_line_id is not null
1117 AND ms.item_id is not null
1118 AND ms.supply_type_code = 'SHIPMENT'
1119 AND ms.destination_type_code = 'INVENTORY'
1120 AND ms.to_org_primary_quantity > 0
1121 AND items.inventory_item_id = ms.item_id
1122 AND items.organization_id = ms.to_organization_id
1123 AND ms.item_id = list.number1
1124 AND ms.to_organization_id = list.number2
1125 AND items.compile_designator = list.char1
1126 AND list.query_id = item_list_id
1127 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
1128 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
1129 AND pl.destination_subinventory = msi.sub_inventory_code(+)
1130 AND NVL(msi.netting_type,1) = 1
1131 GROUP BY ms.item_id,
1132 ms.to_organization_id,
1133 TRANSIT,
1134 TRANSIT_OFF,
1135 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME))
1136 UNION ALL
1137 SELECT
1138 ms.item_id item_id,
1139 ms.to_organization_id org_id,
1140 TRANSIT row_type,
1141 TRANSIT_OFF offset,
1142 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) new_date,
1143 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) old_date,
1144 SUM(ms.to_org_primary_quantity)new_quantity,
1145 SUM(ms.to_org_primary_quantity) old_quantity
1146 FROM mtl_secondary_inventories msub,
1147 mrp_system_items items,
1148 mtl_supply ms,
1149 mrp_form_query list,
1150 mrp_sub_inventories msi
1151 WHERE (arg_res_level = 1
1152 OR (arg_res_level = 2
1153 AND msub.project_id is NULL)
1154 OR (DECODE(arg_res_level,
1155 3,nvl(mrp_get_project.planning_group(msub.project_id),'-23453'),
1156 4,nvl(to_char(msub.project_id), '-23453'))
1157 = nvl(arg_resval1,'-23453'))
1158 OR (arg_res_level = 5
1159 AND nvl(to_char(msub.project_id), '-23453')
1160 = nvl(arg_resval1,'-23453')
1161 AND nvl(msub.task_id, -23453) = nvl(arg_resval2, -23453)))
1162 AND ms.to_organization_id = msub.organization_id(+)
1163 AND ms.to_subinventory = msub.secondary_inventory_name(+)
1164 AND ms.expected_delivery_date < last_date
1165 AND ms.req_line_id is null
1166 AND ms.shipment_line_id is not null
1167 AND ms.item_id is not null
1168 AND ms.supply_type_code = 'SHIPMENT'
1169 AND ms.destination_type_code = 'INVENTORY'
1170 AND ms.to_org_primary_quantity > 0
1171 AND items.inventory_item_id = ms.item_id
1172 AND items.organization_id = ms.to_organization_id
1173 AND ms.item_id = list.number1
1174 AND ms.to_organization_id = list.number2
1175 AND items.compile_designator = list.char1
1176 AND list.query_id = item_list_id
1177 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
1178 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
1179 AND ms.to_subinventory = msi.sub_inventory_code(+)
1180 AND NVL(msi.netting_type,1) = 1
1181 GROUP BY
1182 ms.item_id,
1183 ms.to_organization_id,
1184 TRANSIT,
1185 TRANSIT_OFF,
1186 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME))
1187 UNION ALL
1188 ------------------------
1189 -- Purchase Requisitions
1190 ------------------------
1191 SELECT
1192 ms.item_id item_id,
1193 ms.to_organization_id org_id,
1194 REQ row_type,
1195 REQ_OFF offset,
1196 mrp_calendar.date_offset(items.organization_id,1,ms.need_by_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) new_date,
1197 mrp_calendar.date_offset(items.organization_id,1,ms.need_by_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) old_date,
1198 SUM(nvl(ms.to_org_primary_quantity,0) * pd.req_line_quantity/
1199 prl.quantity) new_quantity,
1200 SUM(nvl(ms.to_org_primary_quantity,0) * pd.req_line_quantity/
1201 prl.quantity) old_quantity
1202 FROM mrp_system_items items,
1203 po_req_distributions_all pd,
1204 po_requisition_lines_all prl,
1205 mtl_supply ms,
1206 mrp_form_query list,
1207 mrp_sub_inventories msi
1208 WHERE (arg_res_level = 1
1209 OR (arg_res_level = 2
1210 AND pd.project_id is NULL)
1211 OR (DECODE(arg_res_level,
1212 3,nvl(mrp_get_project.planning_group(pd.project_id),'-23453'),
1213 4,nvl(to_char(pd.project_id), '-23453'))
1214 = nvl(arg_resval1,'-23453'))
1215 OR (arg_res_level = 5
1216 AND nvl(to_char(pd.project_id), '-23453')
1217 = nvl(arg_resval1,'-23453')
1218 AND nvl(pd.task_id, -23453) = nvl(arg_resval2, -23453)))
1219 AND pd.requisition_line_id = prl.requisition_line_id
1220 AND prl.requisition_line_id = ms.req_line_id
1221 AND ms.to_org_primary_quantity > 0
1222 AND (prl.destination_subinventory is NULL OR
1223 ( exists (SELECT NULL from mtl_secondary_inventories
1224 where organization_id = prl.destination_organization_id AND
1225 secondary_inventory_name = prl.destination_subinventory AND
1226 nvl(availability_type,2) = SYS_YES)))
1227 AND ms.destination_type_code = 'INVENTORY'
1228 AND ms.expected_delivery_date < last_date
1229 AND ms.req_line_id is not null
1230 AND ms.item_id is not null
1231 AND ms.supply_type_code = 'REQ'
1232 AND items.inventory_item_id = ms.item_id
1233 AND items.organization_id = ms.to_organization_id
1234 AND ms.item_id = list.number1
1235 AND ms.to_organization_id = list.number2
1236 AND items.compile_designator = list.char1
1237 AND list.query_id = item_list_id
1238 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
1239 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
1240 AND prl.destination_subinventory = msi.sub_inventory_code(+)
1241 AND NVL(msi.netting_type,1) = 1
1242 AND NOT EXISTS (select 'y' FROM OE_DROP_SHIP_SOURCES ODSS
1243 WHERE ms.req_line_id = ODSS.requisition_line_id)
1244 GROUP BY
1245 ms.item_id,
1246 ms.to_organization_id,
1247 REQ,
1248 REQ_OFF,
1249 mrp_calendar.date_offset(items.organization_id,1,ms.need_by_date, CEIL(items.POSTPROCESSING_LEAD_TIME))
1250 UNION ALL
1251 ----------------------
1252 -- Intransit Receipts
1253 ----------------------
1254 SELECT
1255 ms.item_id item_id,
1256 ms.to_organization_id org_id,
1257 RECEIVING row_type,
1258 RECEIVING_OFF offset,
1259 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) new_date,
1260 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) old_date,
1261 SUM(nvl(ms.TO_ORG_PRIMARY_QUANTITY, 0) * pd.req_line_quantity /
1262 pl.quantity) new_quantity,
1263 SUM(nvl(ms.TO_ORG_PRIMARY_QUANTITY, 0) * pd.req_line_quantity /
1264 pl.quantity) old_quantity
1265 FROM po_requisition_lines_all pl,
1266 po_req_distributions_all pd,
1267 mrp_system_items items,
1268 mtl_supply ms,
1269 mrp_form_query list,
1270 mrp_sub_inventories msi
1271 WHERE (arg_res_level = 1
1272 OR (arg_res_level = 2
1273 AND pd.project_id is NULL)
1274 OR (DECODE(arg_res_level,
1275 3,nvl(mrp_get_project.planning_group(pd.project_id),'-23453'),
1276 4,nvl(to_char(pd.project_id), '-23453'))
1277 = nvl(arg_resval1,'-23453'))
1278 OR (arg_res_level = 5
1279 AND nvl(to_char(pd.project_id), '-23453')
1280 = nvl(arg_resval1,'-23453')
1281 AND nvl(pd.task_id, -23453) = nvl(arg_resval2, -23453)))
1282 AND pd.requisition_line_id = pl.requisition_line_id
1283 AND pl.quantity > 0
1284 AND ms.req_line_id = pl.requisition_line_id
1285 AND ms.expected_delivery_date < last_date
1286 AND ms.po_distribution_id is null
1287 AND ms.item_id is not null
1288 AND ms.supply_type_code = 'RECEIVING'
1289 AND ms.destination_type_code = 'INVENTORY'
1290 AND ms.to_org_primary_quantity > 0
1291 AND items.inventory_item_id = ms.item_id
1292 AND items.organization_id = ms.to_organization_id
1293 AND ms.item_id = list.number1
1294 AND ms.to_organization_id = list.number2
1295 AND items.compile_designator = list.char1
1296 AND list.query_id = item_list_id
1297 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
1298 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
1299 AND pl.destination_subinventory = msi.sub_inventory_code(+)
1300 AND NVL(msi.netting_type,1) = 1
1301 AND NOT EXISTS (select 'y' FROM OE_DROP_SHIP_SOURCES ODSS
1302 WHERE ms.req_line_id = ODSS.requisition_line_id)
1303 GROUP BY
1304 ms.item_id,
1305 ms.to_organization_id,
1306 RECEIVING,
1307 RECEIVING_OFF,
1308 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME))
1309 UNION ALL
1310 SELECT
1311 ms.item_id item_id,
1312 ms.to_organization_id org_id,
1313 RECEIVING row_type,
1314 RECEIVING_OFF offset,
1315 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) new_date,
1316 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) old_date,
1317 SUM(ms.to_org_primary_quantity)new_quantity,
1318 SUM(ms.to_org_primary_quantity) old_quantity
1319 FROM mtl_secondary_inventories msub,
1320 mrp_system_items items,
1321 mtl_supply ms,
1322 mrp_form_query list,
1323 mrp_sub_inventories msi
1324 WHERE (arg_res_level = 1
1325 OR (arg_res_level = 2
1326 AND msub.project_id is NULL)
1327 OR (DECODE(arg_res_level,
1328 3,nvl(mrp_get_project.planning_group(msub.project_id),'-23453'),
1329 4,nvl(to_char(msub.project_id), '-23453'))
1330 = nvl(arg_resval1,'-23453'))
1331 OR (arg_res_level = 5
1332 AND nvl(to_char(msub.project_id), '-23453')
1333 = nvl(arg_resval1,'-23453')
1334 AND nvl(msub.task_id, -23453) = nvl(arg_resval2, -23453)))
1335 AND ms.to_organization_id = msub.organization_id(+)
1336 AND ms.to_subinventory = msub.secondary_inventory_name(+)
1337 AND ms.expected_delivery_date < last_date
1338 AND ms.req_line_id is null
1339 AND ms.po_distribution_id is null
1340 AND ms.item_id is not null
1341 AND ms.supply_type_code = 'RECEIVING'
1342 AND ms.destination_type_code = 'INVENTORY'
1343 AND ms.to_org_primary_quantity > 0
1344 AND items.inventory_item_id = ms.item_id
1345 AND items.organization_id = ms.to_organization_id
1346 AND ms.item_id = list.number1
1347 AND ms.to_organization_id = list.number2
1348 AND items.compile_designator = list.char1
1349 AND list.query_id = item_list_id
1350 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
1351 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
1352 AND ms.to_subinventory = msi.sub_inventory_code(+)
1353 AND NVL(msi.netting_type,1) = 1
1354 GROUP BY
1355 ms.item_id,
1356 ms.to_organization_id,
1357 RECEIVING,
1358 RECEIVING_OFF,
1359 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME))
1360 UNION ALL
1361 -------------------
1362 -- PO in Receiving
1363 -------------------
1364 SELECT
1365 ms.item_id item_id,
1366 ms.to_organization_id org_id,
1367 RECEIVING row_type,
1368 RECEIVING_OFF offset,
1369 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) new_date,
1370 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) old_date,
1371 SUM(ms.to_org_primary_quantity) new_quantity,
1372 SUM(ms.to_org_primary_quantity) old_quantity
1373 FROM po_distributions_all pd,
1374 mrp_system_items items,
1375 mtl_supply ms,
1376 mrp_form_query list,
1377 mrp_sub_inventories msi
1378 WHERE (arg_res_level = 1
1379 OR (arg_res_level = 2
1380 AND pd.project_id is NULL)
1381 OR (DECODE(arg_res_level,
1382 3,nvl(mrp_get_project.planning_group(pd.project_id),'-23453'),
1383 4,nvl(to_char(pd.project_id), '-23453'))
1384 = nvl(arg_resval1,'-23453'))
1385 OR (arg_res_level = 5
1386 AND nvl(to_char(pd.project_id), '-23453')
1387 = nvl(arg_resval1,'-23453')
1388 AND nvl(pd.task_id, -23453) = nvl(arg_resval2, -23453)))
1389 AND pd.po_distribution_id = ms.po_distribution_id
1390 AND ms.expected_delivery_date < last_date
1391 AND ms.destination_type_code = 'INVENTORY'
1392 and ms.item_id is not null
1393 AND ms.to_org_primary_quantity > 0
1394 AND ms.supply_type_code = 'RECEIVING'
1395 AND items.inventory_item_id = ms.item_id
1396 AND items.organization_id = ms.to_organization_id
1397 AND ms.item_id = list.number1
1398 AND ms.to_organization_id = list.number2
1399 AND items.compile_designator = list.char1
1400 AND list.query_id = item_list_id
1401 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
1402 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
1403 AND pd.destination_subinventory = msi.sub_inventory_code(+)
1404 AND NVL(msi.netting_type,1) = 1
1405 AND NOT EXISTS (select 'y' FROM OE_DROP_SHIP_SOURCES ODSS
1406 WHERE ms.po_line_location_id = ODSS.line_location_id)
1407 GROUP BY
1408 ms.item_id,
1409 ms.to_organization_id,
1410 RECEIVING,
1411 RECEIVING_OFF,
1412 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME))
1413 UNION ALL
1414 -------------------
1415 --- PO SUPPLY Scrap
1416 -------------------
1417 -----------------------
1418 -- Purchase Order Scrap
1419 -----------------------
1420 SELECT
1421 ms.item_id item_id,
1422 ms.to_organization_id org_id,
1423 SCRAP row_type,
1424 SCRAP_OFF offset,
1425 mrp_calendar.date_offset(items.organization_id,1,ms.need_by_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) new_date,
1426 mrp_calendar.date_offset(items.organization_id,1,ms.need_by_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) old_date,
1427 SUM(ms.to_org_primary_quantity * DECODE(SIGN(ITEMS.SHRINKAGE_RATE),
1428 -1, 0, (NVL(ITEMS.SHRINKAGE_RATE,0)))) new_quantity,
1429 0 old_quantity
1430 FROM po_distributions_all pd,
1431 mrp_system_items items,
1432 mtl_supply ms,
1433 mrp_form_query list,
1434 mrp_sub_inventories msi
1435 WHERE (arg_res_level = 1
1436 OR (arg_res_level = 2
1437 AND pd.project_id is NULL)
1438 OR (DECODE(arg_res_level,
1439 3,nvl(mrp_get_project.planning_group(pd.project_id),'-23453'),
1440 4,nvl(to_char(pd.project_id), '-23453'))
1441 = nvl(arg_resval1,'-23453'))
1442 OR (arg_res_level = 5
1443 AND nvl(to_char(pd.project_id), '-23453')
1444 = nvl(arg_resval1,'-23453')
1445 AND nvl(pd.task_id, -23453) = nvl(arg_resval2, -23453)))
1446 AND ms.expected_delivery_date < last_date
1447 AND pd.po_distribution_id = ms.po_distribution_id
1448 AND ms.destination_type_code = 'INVENTORY'
1449 AND ms.po_line_id is not null
1450 and ms.item_id is not null
1451 AND ms.to_org_primary_quantity > 0
1452 AND ( ms.supply_type_code = 'PO' or
1453 ms.supply_type_code = 'ASN')
1454 AND items.inventory_item_id = ms.item_id
1455 AND items.organization_id = ms.to_organization_id
1456 AND ms.item_id = list.number1
1457 AND ms.to_organization_id = list.number2
1458 AND items.compile_designator = list.char1
1459 AND list.query_id = item_list_id
1460 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
1461 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
1462 AND pd.destination_subinventory = msi.sub_inventory_code(+)
1463 AND NVL(msi.netting_type,1) = 1
1464 AND NOT EXISTS (select 'y' FROM OE_DROP_SHIP_SOURCES ODSS
1465 WHERE ms.po_line_location_id = ODSS.line_location_id)
1466 GROUP BY
1467 ms.item_id,
1468 ms.to_organization_id,
1469 SCRAP,
1470 SCRAP_OFF,
1471 mrp_calendar.date_offset(items.organization_id,1,ms.need_by_date, CEIL(items.POSTPROCESSING_LEAD_TIME))
1472 UNION ALL
1473 ---------------------------
1474 -- Intransit Shipment Scrap
1475 ---------------------------
1476 SELECT
1477 ms.item_id item_id,
1478 ms.to_organization_id org_id,
1479 SCRAP row_type,
1480 SCRAP_OFF offset,
1481 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) new_date,
1482 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) old_date,
1483 SUM((nvl(ms.to_org_primary_quantity,0)* pd.req_line_quantity/pl.quantity) * DECODE(SIGN(ITEMS.SHRINKAGE_RATE),
1484 -1, 0, (NVL(ITEMS.SHRINKAGE_RATE,0)))) new_quantity,
1485 0 old_quantity
1486 FROM po_req_distributions_all pd,
1487 po_requisition_lines_all pl,
1488 mrp_system_items items,
1489 mtl_supply ms,
1490 mrp_form_query list,
1491 mrp_sub_inventories msi
1492 WHERE (arg_res_level = 1
1493 OR (arg_res_level = 2
1494 AND pd.project_id is NULL)
1495 OR (DECODE(arg_res_level,
1496 3,nvl(mrp_get_project.planning_group(pd.project_id),'-23453'),
1497 4,nvl(to_char(pd.project_id), '-23453'))
1498 = nvl(arg_resval1,'-23453'))
1499 OR (arg_res_level = 5
1500 AND nvl(to_char(pd.project_id), '-23453')
1501 = nvl(arg_resval1,'-23453')
1502 AND nvl(pd.task_id, -23453) = nvl(arg_resval2, -23453)))
1503 AND pl.requisition_line_id = pd.requisition_line_id
1504 AND pl.quantity > 0
1505 AND ms.req_line_id = pl.requisition_line_id
1506 AND ms.expected_delivery_date < last_date
1507 AND ms.req_line_id is not null
1508 AND ms.shipment_line_id is not null
1509 AND ms.item_id is not null
1510 AND ms.supply_type_code = 'SHIPMENT'
1511 AND ms.destination_type_code = 'INVENTORY'
1512 AND ms.to_org_primary_quantity > 0
1513 AND items.inventory_item_id = ms.item_id
1514 AND items.organization_id = ms.to_organization_id
1515 AND ms.item_id = list.number1
1516 AND ms.to_organization_id = list.number2
1517 AND items.compile_designator = list.char1
1518 AND list.query_id = item_list_id
1519 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
1520 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
1521 AND pl.destination_subinventory = msi.sub_inventory_code(+)
1522 AND NVL(msi.netting_type,1) = 1
1523 GROUP BY ms.item_id,
1524 ms.to_organization_id,
1525 SCRAP,
1526 SCRAP_OFF,
1527 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME))
1528 UNION ALL
1529 SELECT
1530 ms.item_id item_id,
1531 ms.to_organization_id org_id,
1532 SCRAP row_type,
1533 SCRAP_OFF offset,
1534 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) new_date,
1535 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) old_date,
1536 SUM(nvl(ms.to_org_primary_quantity,0) * DECODE(SIGN(ITEMS.SHRINKAGE_RATE),
1537 -1, 0, (NVL(ITEMS.SHRINKAGE_RATE,0)))) new_quantity,
1538 0 old_quantity
1539 FROM mtl_secondary_inventories msub,
1540 mrp_system_items items,
1541 mtl_supply ms,
1542 mrp_form_query list,
1543 mrp_sub_inventories msi
1544 WHERE (arg_res_level = 1
1545 OR (arg_res_level = 2
1546 AND msub.project_id is NULL)
1547 OR (DECODE(arg_res_level,
1548 3,nvl(mrp_get_project.planning_group(msub.project_id),'-23453'),
1549 4,nvl(to_char(msub.project_id), '-23453'))
1550 = nvl(arg_resval1,'-23453'))
1551 OR (arg_res_level = 5
1552 AND nvl(to_char(msub.project_id), '-23453')
1553 = nvl(arg_resval1,'-23453')
1554 AND nvl(msub.task_id, -23453) = nvl(arg_resval2, -23453)))
1555 AND ms.to_organization_id = msub.organization_id(+)
1556 AND ms.to_subinventory = msub.secondary_inventory_name(+)
1557 AND ms.expected_delivery_date < last_date
1558 AND ms.req_line_id is null
1559 AND ms.shipment_line_id is not null
1560 AND ms.item_id is not null
1561 AND ms.supply_type_code = 'SHIPMENT'
1562 AND ms.destination_type_code = 'INVENTORY'
1563 AND ms.to_org_primary_quantity > 0
1564 AND items.inventory_item_id = ms.item_id
1565 AND items.organization_id = ms.to_organization_id
1566 AND ms.item_id = list.number1
1567 AND ms.to_organization_id = list.number2
1568 AND items.compile_designator = list.char1
1569 AND list.query_id = item_list_id
1570 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
1571 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
1572 AND ms.to_subinventory = msi.sub_inventory_code(+)
1573 AND NVL(msi.netting_type,1) = 1
1574 GROUP BY
1575 ms.item_id,
1576 ms.to_organization_id,
1577 SCRAP,
1578 SCRAP_OFF,
1579 mrp_calendar.date_offset(items.organization_id,1,ms.receipt_date, CEIL(items.POSTPROCESSING_LEAD_TIME))
1580 UNION ALL
1581 -------------------------------
1582 -- Purchase Requisitions Scrap
1583 -------------------------------
1584 SELECT
1585 ms.item_id item_id,
1586 ms.to_organization_id org_id,
1587 SCRAP row_type,
1588 SCRAP_OFF offset,
1589 mrp_calendar.date_offset(items.organization_id,1,ms.need_by_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) new_date,
1590 mrp_calendar.date_offset(items.organization_id,1,ms.need_by_date, CEIL(items.POSTPROCESSING_LEAD_TIME)) old_date,
1591 SUM(nvl(ms.to_org_primary_quantity,0) *DECODE(SIGN(ITEMS.SHRINKAGE_RATE),
1592 -1, 0, (NVL(ITEMS.SHRINKAGE_RATE,0)))) new_quantity,
1593 0 old_quantity
1594 FROM mrp_system_items items,
1595 po_req_distributions_all pd,
1596 mtl_supply ms,
1597 mrp_form_query list,
1598 mrp_sub_inventories msi
1599 WHERE (arg_res_level = 1
1600 OR (arg_res_level = 2
1601 AND pd.project_id is NULL)
1602 OR (DECODE(arg_res_level,
1603 3,nvl(mrp_get_project.planning_group(pd.project_id),'-23453'),
1604 4,nvl(to_char(pd.project_id), '-23453'))
1605 = nvl(arg_resval1,'-23453'))
1606 OR (arg_res_level = 5
1607 AND nvl(to_char(pd.project_id), '-23453')
1608 = nvl(arg_resval1,'-23453')
1609 AND nvl(pd.task_id, -23453) = nvl(arg_resval2, -23453)))
1610 AND ms.to_org_primary_quantity > 0
1611 AND pd.req_line_quantity > 0
1612 AND ms.destination_type_code = 'INVENTORY'
1613 AND ms.expected_delivery_date < last_date
1614 AND pd.requisition_line_id = ms.req_line_id
1615 AND ms.req_line_id is not null
1616 AND ms.item_id is not null
1617 AND ms.supply_type_code = 'REQ'
1618 AND items.inventory_item_id = ms.item_id
1619 AND items.organization_id = ms.to_organization_id
1620 AND ms.item_id = list.number1
1621 AND ms.to_organization_id = list.number2
1622 AND items.compile_designator = list.char1
1623 AND list.query_id = item_list_id
1624 AND NVL(msi.compile_designator,items.compile_designator) = items.compile_designator
1625 AND NVL(msi.organization_id,items.organization_id) = items.organization_id
1626 AND ms.to_subinventory = msi.sub_inventory_code(+)
1627 AND NVL(msi.netting_type,1) = 1
1628 AND NOT EXISTS (select 'y' FROM OE_DROP_SHIP_SOURCES ODSS
1629 WHERE ms.req_line_id = ODSS.requisition_line_id)
1630 GROUP BY
1631 ms.item_id,
1632 ms.to_organization_id,
1633 SCRAP,
1634 SCRAP_OFF,
1635 mrp_calendar.date_offset(items.organization_id,1,ms.need_by_date, CEIL(items.POSTPROCESSING_LEAD_TIME))
1636
1637 UNION ALL
1638 ---------------------------------------------
1639 --- Planned order, Model /* Modified in 1402080 */
1640 ---------------------------------------------
1641
1642 SELECT
1643 items.inventory_item_id item_id,
1644 items.organization_id org_id,
1645 DEPENDENT row_type,
1646 DEPENDENT_OFF offset,
1647 dates.calendar_date new_date,
1648 dates.calendar_date old_date,
1649 DECODE(SIGN(SUM(DECODE(reqs.assembly_demand_comp_date, NULL,
1650 reqs.using_requirements_quantity, daily_demand_rate)*
1651 ((NVL(recom.new_order_quantity, 0)
1652 - NVL(implemented_quantity, 0))/NVL(recom.new_order_quantity,1)))),-1,0,
1653 SUM(DECODE(reqs.assembly_demand_comp_date, NULL,
1654 reqs.using_requirements_quantity, daily_demand_rate)*
1655 ((NVL(recom.new_order_quantity, 0)
1656 - NVL(implemented_quantity, 0))/NVL(recom.new_order_quantity,1)))) new_quantity,
1657 0 old_quantity
1658 FROM mrp_form_query list,
1659 bom_calendar_dates dates,
1660 mtl_parameters param,
1661 mrp_recommendations recom,
1662 mrp_gross_requirements reqs,
1663 mrp_system_items items,
1664 mrp_plan_organizations_v mpov /*1402080*/
1665 WHERE (arg_res_level = 1
1666 OR (arg_res_level = 2
1667 AND reqs.project_id is NULL)
1668 OR (DECODE(arg_res_level,
1669 3,nvl(reqs.planning_group,'-23453'),
1670 4,nvl(to_char(reqs.project_id), '-23453'))
1671 = nvl(arg_resval1,'-23453'))
1672 OR (arg_res_level = 5
1673 AND nvl(to_char(reqs.project_id), '-23453')
1674 = nvl(arg_resval1,'-23453')
1675 AND nvl(reqs.task_id, -23453) = nvl(arg_resval2, -23453)))
1676 AND reqs.origination_type in (DEMAND_PLANNED_ORDER)
1677 AND reqs.organization_id = items.organization_id
1678 AND reqs.compile_designator = items.compile_designator
1679 AND reqs.inventory_item_id = items.inventory_item_id
1680 --AND recom.organization_id = reqs.organization_id /*1402080*/
1681 AND recom.inventory_item_id = reqs.using_assembly_item_id
1682 AND recom.compile_designator = reqs.compile_designator
1683 AND recom.transaction_id = reqs.disposition_id
1684 AND recom.order_type = 5
1685 AND dates.exception_set_id = param.calendar_exception_set_id
1686 AND dates.calendar_code = param.calendar_code
1687 AND dates.seq_num is not null
1688 AND dates.calendar_date BETWEEN reqs.using_assembly_demand_date
1689 AND NVL(reqs.assembly_demand_comp_date, reqs.using_assembly_demand_date)
1690 AND reqs.using_assembly_demand_date < last_date
1691 AND param.organization_id = items.organization_id
1692 AND items.inventory_item_id = list.number1
1693 AND items.organization_id = list.number2
1694 AND items.compile_designator = list.char1
1695 AND list.query_id = item_list_id
1696 AND mpov.compile_designator = reqs.compile_designator/*1402080*/
1697 AND mpov.planned_organization = reqs.organization_id /*1402080*/
1698 --AND mpov.organization_id = recom.organization_id /*1475470*/
1699 GROUP BY
1700 items.inventory_item_id,
1701 items.organization_id,
1702 DEPENDENT,
1703 DEPENDENT_OFF,
1704 dates.calendar_date,
1705 dates.calendar_date,
1706 0
1707 UNION ALL
1708 ----------------------------------------------------------
1709 --Option Class Demand/*Added separately in 1402080 for option class demand*/
1710 ----------------------------------------------------------
1711 SELECT
1712 reqs.inventory_item_id item_id,
1713 reqs.organization_id org_id,
1714 DEPENDENT row_type,
1715 DEPENDENT_OFF offset,
1716 dates.calendar_date new_date,
1717 dates.calendar_date old_date,
1718 SUM(DECODE(reqs.assembly_demand_comp_date,
1719 NULL, using_requirements_quantity,
1720 daily_demand_rate)) new_quantity,
1721 0 old_quantity
1722 FROM mrp_form_query list,
1723 mtl_parameters param,
1724 mrp_gross_requirements reqs,
1725 bom_calendar_dates dates
1726 WHERE (arg_res_level = 1
1727 OR (arg_res_level = 2
1728 AND reqs.project_id is NULL)
1729 OR (DECODE(arg_res_level,
1730 3,nvl(reqs.planning_group,'-23453'),
1731 4,nvl(to_char(reqs.project_id), '-23453'))
1732 = nvl(arg_resval1,'-23453'))
1733 OR (arg_res_level = 5
1734 AND nvl(to_char(reqs.project_id), '-23453')
1735 = nvl(arg_resval1,'-23453')
1736 AND nvl(reqs.task_id, -23453)
1737 = nvl(arg_resval2, -23453)))
1738 AND origination_type in (DEMAND_OPTIONAL)
1739 AND dates.exception_set_id = param.calendar_exception_set_id
1740 AND dates.calendar_code = param.calendar_code
1741 AND dates.seq_num IS NOT NULL
1742 AND dates.calendar_date BETWEEN reqs.using_assembly_demand_date
1743 AND NVL(reqs.assembly_demand_comp_date, reqs.using_assembly_demand_date)
1744 AND reqs.using_assembly_demand_date < last_date
1745 AND reqs.compile_designator = list.char1
1746 AND reqs.inventory_item_id = list.number1
1747 AND reqs.organization_id = list.number2
1748 AND param.organization_id = list.number2
1749 AND list.query_id = item_list_id
1750 GROUP BY
1751 reqs.inventory_item_id,
1752 reqs.organization_id,
1753 DEPENDENT,
1754 DEPENDENT_OFF,
1755 dates.calendar_date,
1756 dates.calendar_date,
1757 0
1758 UNION ALL
1759 ------------------------------------------------------------
1760 --payback demand
1761 ------------------------------------------------------------
1762 SELECT
1763 mipv.inventory_item_id item_id,
1764 mipv.organization_id org_id,
1765 PB_DEMAND row_type,
1766 PB_DEMAND_OFF offset,
1767 mipv.payback_date new_date,
1768 mipv.payback_date old_date,
1769 sum(mipv.quantity) new_quantity,
1770 0 old_quantity
1771 FROM mrp_form_query list,
1772 mrp_project_parameters pa,
1773 mtl_parameters param,
1774 mrp_item_borrow_payback_qty_v mipv
1775 WHERE (arg_res_level = 1
1776 OR (arg_res_level = 2
1777 AND mipv.borrow_project_id is NULL)
1778 OR (DECODE(arg_res_level,
1779 3,nvl(pa.planning_group,'-23453'),
1780 4,nvl(to_char(mipv.borrow_project_id), '-23453'))
1781 = nvl(arg_resval1,'-23453'))
1782 OR (arg_res_level = 5
1783 AND nvl(to_char(mipv.borrow_project_id), '-23453')
1784 = nvl(arg_resval1,'-23453')
1785 AND nvl(mipv.borrow_task_id, -23453) =
1786 nvl(arg_resval2, -23453)))
1787 AND pa.project_id(+)=mipv.borrow_project_id
1788 AND pa.organization_id(+)=mipv.organization_id
1789 AND param.project_reference_enabled = 1
1790 AND param.organization_id = mipv.organization_id
1791 AND trunc(mipv.payback_date) < last_date
1792 AND mipv.inventory_item_id = list.number1
1793 AND mipv.owning_organization_id = list.number2
1794 AND mipv.compile_designator = list.char1
1795 AND list.query_id = item_list_id
1796 GROUP BY
1797 mipv.inventory_item_id,
1798 mipv.organization_id,
1799 PB_DEMAND,
1800 PB_DEMAND_OFF,
1801 mipv.payback_date,
1802 mipv.payback_date,
1803 0
1804 UNION ALL
1805 SELECT
1806 mubq.inventory_item_id item_id,
1807 mubq.organization_id org_id,
1808 PB_DEMAND row_type,
1809 PB_DEMAND_OFF offset,
1810 mubq.payback_date new_date,
1811 mubq.payback_date old_date,
1812 sum(mubq.quantity-nvl(mupq.quantity, 0)) new_quantity,
1813 0 old_quantity
1814 FROM mrp_form_query list,
1815 mrp_project_parameters pa,
1816 mtl_parameters param,
1817 mrp_unit_borrow_qty_v mubq,
1818 mrp_unit_payback_qty_v mupq
1819 WHERE (arg_res_level = 1
1820 OR (arg_res_level = 2
1821 AND mubq.borrow_project_id is NULL)
1822 OR (DECODE(arg_res_level,
1823 3,nvl(pa.planning_group,'-23453'),
1824 4,nvl(to_char(mubq.borrow_project_id), '-23453'))
1825 = nvl(arg_resval1,'-23453'))
1826 OR (arg_res_level = 5
1827 AND nvl(to_char(mubq.borrow_project_id), '-23453')
1828 = nvl(arg_resval1,'-23453')
1829 AND nvl(mubq.borrow_task_id, -23453) =
1830 nvl(arg_resval2, -23453)))
1831 AND pa.project_id(+)=mubq.borrow_project_id
1832 AND pa.organization_id(+)=mubq.organization_id
1833 AND mubq.borrow_transaction_id = mupq.borrow_transaction_id(+)
1834 AND mubq.compile_designator = mupq.compile_designator(+)
1835 AND mubq.organization_id = mupq.organization_id(+)
1836 AND mubq.inventory_item_id = mupq.inventory_item_id(+)
1837 AND param.project_reference_enabled = 1
1838 AND param.organization_id = mubq.organization_id
1839 AND trunc(mubq.payback_date) < last_date
1840 AND mubq.inventory_item_id = list.number1
1841 AND mubq.owning_org_id = list.number2
1842 AND mubq.compile_designator = list.char1
1843 AND list.query_id = item_list_id
1844 GROUP BY
1845 mubq.inventory_item_id,
1846 mubq.organization_id,
1847 PB_DEMAND,
1848 PB_DEMAND_OFF,
1849 mubq.payback_date,
1850 mubq.payback_date,
1851 0
1852 UNION ALL
1853 ------------------------------------------------------------
1854 --payback supply
1855 ------------------------------------------------------------
1856 SELECT
1857 mipv.inventory_item_id item_id,
1858 mipv.organization_id org_id,
1859 PB_SUPPLY row_type,
1860 PB_SUPPLY_OFF offset,
1861 mipv.payback_date new_date,
1862 mipv.payback_date old_date,
1863 sum(mipv.quantity) new_quantity,
1864 sum(mipv.quantity) old_quantity
1865 FROM mrp_form_query list,
1866 mrp_project_parameters pa,
1867 mtl_parameters param,
1868 mrp_item_borrow_payback_qty_v mipv
1869 WHERE (arg_res_level = 1
1870 OR (arg_res_level = 2
1871 AND mipv.borrow_project_id is NULL)
1872 OR (DECODE(arg_res_level,
1873 3,nvl(pa.planning_group,'-23453'),
1874 4,nvl(to_char(mipv.borrow_project_id), '-23453'))
1875 = nvl(arg_resval1,'-23453'))
1876 OR (arg_res_level = 5
1877 AND nvl(to_char(mipv.borrow_project_id), '-23453')
1878 = nvl(arg_resval1,'-23453')
1879 AND nvl(mipv.borrow_task_id, -23453) =
1880 nvl(arg_resval2, -23453)))
1881 AND pa.project_id(+)=mipv.borrow_project_id
1882 AND pa.organization_id(+)=mipv.organization_id
1883 AND param.project_reference_enabled = 1
1884 AND param.organization_id = mipv.organization_id
1885 AND trunc(mipv.payback_date) < last_date
1886 AND mipv.inventory_item_id = list.number1
1887 AND mipv.owning_organization_id = list.number2
1888 AND mipv.compile_designator = list.char1
1889 AND list.query_id = item_list_id
1890 GROUP BY
1891 mipv.inventory_item_id,
1892 mipv.organization_id,
1893 PB_SUPPLY,
1894 PB_SUPPLY_OFF,
1895 mipv.payback_date,
1896 mipv.payback_date
1897 UNION ALL
1898 SELECT
1899 mubq.inventory_item_id item_id,
1900 mubq.organization_id org_id,
1901 PB_SUPPLY row_type,
1902 PB_SUPPLY_OFF offset,
1903 mubq.payback_date new_date,
1904 mubq.payback_date old_date,
1905 sum(mubq.quantity-nvl(mupq.quantity, 0)) new_quantity,
1906 sum(mubq.quantity-nvl(mupq.quantity, 0)) old_quantity
1907 FROM mrp_form_query list,
1908 mrp_project_parameters pa,
1909 mtl_parameters param,
1910 mrp_unit_borrow_qty_v mubq,
1911 mrp_unit_payback_qty_v mupq
1912 WHERE (arg_res_level = 1
1913 OR (arg_res_level = 2
1914 AND mubq.borrow_project_id is NULL)
1915 OR (DECODE(arg_res_level,
1916 3,nvl(pa.planning_group,'-23453'),
1917 4,nvl(to_char(mubq.borrow_project_id), '-23453'))
1918 = nvl(arg_resval1,'-23453'))
1919 OR (arg_res_level = 5
1920 AND nvl(to_char(mubq.borrow_project_id), '-23453')
1921 = nvl(arg_resval1,'-23453')
1922 AND nvl(mubq.borrow_task_id, -23453) =
1923 nvl(arg_resval2, -23453)))
1924 AND pa.project_id(+)=mubq.borrow_project_id
1925 AND pa.organization_id(+)=mubq.organization_id
1926 AND mubq.borrow_transaction_id = mupq.borrow_transaction_id(+)
1927 AND mubq.compile_designator = mupq.compile_designator(+)
1928 AND mubq.organization_id = mupq.organization_id(+)
1929 AND mubq.inventory_item_id = mupq.inventory_item_id(+)
1930 AND param.project_reference_enabled = 1
1931 AND param.organization_id = mubq.organization_id
1932 AND trunc(mubq.payback_date) < last_date
1933 AND mubq.inventory_item_id = list.number1
1934 AND mubq.owning_org_id = list.number2
1935 AND mubq.compile_designator = list.char1
1936 AND list.query_id = item_list_id
1937 GROUP BY
1938 mubq.inventory_item_id,
1939 mubq.organization_id,
1940 PB_SUPPLY,
1941 PB_SUPPLY_OFF,
1942 mubq.payback_date,
1943 mubq.payback_date
1944 UNION ALL
1945 ---------------------
1946 --- Planned Orders
1947 ---------------------
1948 SELECT
1949 rec.inventory_item_id item_id,
1950 rec.organization_id org_id,
1951 PLANNED row_type,
1952 PLANNED_OFF offset,
1953 --rec.new_schedule_date new_date,
1954 /* Bug 1888531 */
1955 NVL(rec.firm_date, rec.new_schedule_date) new_date,
1956 rec.old_schedule_date old_date,
1957 SUM(GREATEST(0, nvl(rec.firm_quantity, rec.new_order_quantity) -
1958 (nvl(rec.implemented_quantity, 0)
1959 + nvl(rec.quantity_in_process, 0)))) new_quantity,
1960 0 old_quantity
1961 FROM mrp_form_query list,
1962 mrp_recommendations rec
1963 WHERE (arg_res_level = 1
1964 OR (arg_res_level = 2
1965 AND rec.project_id is NULL)
1966 OR (DECODE(arg_res_level,
1967 3,nvl(rec.planning_group,'-23453'),
1968 4,nvl(to_char(rec.project_id), '-23453'))
1969 = nvl(arg_resval1,'-23453'))
1970 OR (arg_res_level = 5
1971 AND nvl(to_char(rec.project_id), '-23453')
1972 = nvl(arg_resval1,'-23453')
1973 AND nvl(rec.task_id, -23453) = nvl(arg_resval2, -23453)))
1974 AND rec.order_type = PLANNED_ORDER
1975 AND rec.inventory_item_id = list.number1
1976 /* Bug 1888531 */
1977 AND nvl(rec.firm_date,rec.new_schedule_date) < last_date
1978 AND rec.organization_id = list.number2
1979 AND rec.compile_designator = list.char1
1980 AND list.query_id = item_list_id
1981 GROUP BY
1982 rec.inventory_item_id,
1983 rec.organization_id,
1984 PLANNED,
1985 PLANNED_OFF,
1986 /* Bug 1888531 */
1987 nvl(rec.firm_date,rec.new_schedule_date),
1988 rec.old_schedule_date,
1989 0
1990 UNION ALL
1991 ----------------------------------------------
1992 ----- Planned Order By Product
1993 ----------------------------------------------
1994 SELECT
1995 rec.inventory_item_id item_id,
1996 rec.organization_id org_id,
1997 DEPENDENT row_type,
1998 DEPENDENT_OFF offset,
1999 rec.new_schedule_date new_date,
2000 rec.old_schedule_date old_date,
2001 SUM(-1 * rec.new_order_quantity),
2002 0 old_quantity
2003 FROM mrp_form_query list,
2004 mrp_recommendations rec
2005 WHERE (arg_res_level = 1
2006 OR (arg_res_level = 2
2007 AND rec.project_id is NULL)
2008 OR (DECODE(arg_res_level,
2009 3,nvl(rec.planning_group,'-23453'),
2010 4,nvl(to_char(rec.project_id), '-23453'))
2011 = nvl(arg_resval1,'-23453'))
2012 OR (arg_res_level = 5
2013 AND nvl(to_char(rec.project_id), '-23453')
2014 = nvl(arg_resval1,'-23453')
2015 AND nvl(rec.task_id, -23453) = nvl(arg_resval2, -23453)))
2016 AND rec.order_type = PLANNED_BY
2017 AND rec.inventory_item_id = list.number1
2018 AND rec.new_schedule_date < last_date
2019 AND rec.organization_id = list.number2
2020 AND rec.compile_designator = list.char1
2021 AND list.query_id = item_list_id
2022 GROUP BY
2023 rec.inventory_item_id,
2024 rec.organization_id,
2025 DEPENDENT,
2026 DEPENDENT_OFF,
2027 rec.new_schedule_date,
2028 rec.old_schedule_date,
2029 0
2030 UNION ALL
2031 ---------------------------
2032 --- Planned orders scrap
2033 ---------------------------
2034 SELECT
2035 rec.inventory_item_id item_id,
2036 rec.organization_id org_id,
2037 SCRAP row_type,
2038 SCRAP_OFF offset,
2039 rec.new_schedule_date new_date,
2040 rec.old_schedule_date old_date,
2041 SUM(rec.new_order_quantity*NVL(items.shrinkage_rate, 0)) new_quantity,
2042 0 old_quantity
2043 FROM mrp_form_query list,
2044 mrp_recommendations rec,
2045 mrp_system_items items
2046 WHERE (arg_res_level = 1
2047 OR (arg_res_level = 2
2048 AND rec.project_id is NULL)
2049 OR (DECODE(arg_res_level,
2050 3,nvl(rec.planning_group,'-23453'),
2051 4,nvl(to_char(rec.project_id), '-23453'))
2052 = nvl(arg_resval1,'-23453'))
2053 OR (arg_res_level = 5
2054 AND nvl(to_char(rec.project_id), '-23453')
2055 = nvl(arg_resval1,'-23453')
2056 AND nvl(rec.task_id, -23453) = nvl(arg_resval2, -23453)))
2057 AND rec.order_type = PLANNED_ORDER
2058 AND rec.new_schedule_date < last_date
2059 AND rec.organization_id = items.organization_id
2060 AND rec.compile_designator = items.compile_designator
2061 AND rec.inventory_item_id = items.inventory_item_id
2062 AND rec.inventory_item_id = list.number1
2063 AND rec.organization_id = list.number2
2064 AND rec.compile_designator = list.char1
2065 AND list.query_id = item_list_id
2066 GROUP BY
2067 rec.inventory_item_id,
2068 rec.organization_id,
2069 SCRAP,
2070 SCRAP_OFF,
2071 rec.new_schedule_date,
2072 rec.old_schedule_date,
2073 0
2074 UNION ALL
2075 ---------------------
2076 -- Sales Orders
2077 ------------------
2078 SELECT
2079 items.inventory_item_id item_id,
2080 items.organization_id org_id,
2081 SALES row_type,
2082 SALES_OFF offset,
2083 demand.requirement_date new_date,
2084 demand.requirement_date old_date,
2085 SUM(demand.primary_uom_quantity - GREATEST(demand.completed_quantity,
2086 demand.reservation_quantity) ) new_quantity,
2087 0 old_quantity
2088 FROM oe_order_lines_all sl,
2089 mtl_demand_omoe demand,
2090 mrp_system_items items,
2091 mrp_form_query list
2092 WHERE (arg_res_level = 1
2093 OR (arg_res_level = 2
2094 AND sl.project_id is NULL)
2095 OR (DECODE(arg_res_level,
2096 3,nvl(mrp_get_project.planning_group(sl.project_id),'-23453'),
2097 4,nvl(to_char(sl.project_id), '-23453'))
2098 = nvl(arg_resval1,'-23453'))
2099 OR (arg_res_level = 5
2100 AND nvl(to_char(sl.project_id), '-23453')
2101 = nvl(arg_resval1,'-23453')
2102 AND nvl(sl.task_id, -23453) = nvl(arg_resval2, -23453)))
2103 and to_number(demand.demand_source_line) = sl.line_id (+)
2104 and demand.demand_source_type in (2, 8)
2105 and items.organization_id = demand.organization_id
2106 and items.inventory_item_id = demand.inventory_item_id
2107 and demand.demand_source_type = SALES_ORDER
2108 and demand.available_to_mrp = SYS_YES
2109 and demand.primary_uom_quantity >
2110 NVL(demand.completed_quantity, 0)
2111 AND items.inventory_item_id = list.number1
2112 AND items.organization_id = list.number2
2113 AND items.compile_designator = list.char1
2114 AND list.query_id = item_list_id
2115 AND arg_ind_demand_type = IDT_FORECAST--bug3211478
2116 GROUP BY
2117 items.inventory_item_id,
2118 items.organization_id,
2119 SALES,
2120 SALES_OFF,
2121 demand.requirement_date,
2122 demand.requirement_date,
2123 0
2124 UNION ALL
2125 ------------------
2126 --- Safety stock
2127 ------------------
2128 SELECT
2129 items.inventory_item_id item_id,
2130 items.organization_id org_id,
2131 SS row_type,
2132 SS_OFF offset,
2133 safety.effectivity_date new_date,
2134 safety.effectivity_date old_date,
2135 DECODE (NVL(items.rounding_control_type, DO_NOT_ROUND),
2136 DO_ROUND, CEIL(NVL(safety.safety_stock_quantity, 0)),
2137 NVL(safety.safety_stock_quantity, 0)),
2138 0 old_quantity
2139 FROM mrp_form_query list,
2140 mtl_safety_stocks safety,
2141 mrp_system_items items
2142 WHERE TRUNC(safety.effectivity_date) < last_date
2143 AND TRUNC(safety.effectivity_date) >=
2144 (SELECT NVL(TRUNC(max(effectivity_date)), TRUNC(SYSDATE))
2145 FROM mtl_safety_stocks
2146 WHERE organization_id = items.organization_id
2147 AND inventory_item_id = items.inventory_item_id
2148 AND effectivity_date <= TRUNC(SYSDATE))
2149 AND safety.organization_id = items.organization_id
2150 AND safety.inventory_item_id = items.inventory_item_id
2151 AND items.safety_stock_code = NON_MRP_PCT
2152 AND items.inventory_item_id = list.number1
2153 AND items.organization_id = list.number2
2154 AND items.compile_designator = list.char1
2155 AND list.query_id = item_list_id
2156 UNION ALL
2157 ------------------
2158 --- Expired Lots
2159 ------------------
2160 SELECT
2161 items.inventory_item_id item_id,
2162 items.organization_id org_id,
2163 EXP_LOT row_type,
2164 EXP_LOT_OFF offset,
2165 lots.expiration_date new_date,
2166 lots.expiration_date old_date,
2167 NVL(SUM(moq.primary_transaction_quantity), 0) new_quantity,
2168 0 old_quantity
2169 FROM
2170 mtl_item_locations mil,
2171 mtl_lot_numbers lots,
2172 mtl_onhand_quantities_detail moq,
2173 mrp_sub_inventories sub,
2174 mrp_system_items items,
2175 mrp_plan_organizations_v orgs,
2176 mrp_form_query list
2177 WHERE (arg_res_level = 1
2178 OR (arg_res_level = 2
2179 AND mil.project_id is NULL)
2180 OR (DECODE(arg_res_level,
2181 3,nvl(mrp_get_project.planning_group(mil.project_id),'-23453'),
2182 4,nvl(to_char(mil.project_id), '-23453'))
2183 = nvl(arg_resval1,'-23453'))
2184 OR (arg_res_level = 5
2185 AND nvl(to_char(mil.project_id), '-23453')
2186 = nvl(arg_resval1,'-23453')
2187 AND nvl(mil.task_id, -23453) = nvl(arg_resval2, -23453)))
2188 AND (moq.organization_id = mil.organization_id(+)
2189 AND moq.locator_id = mil.inventory_location_id(+))
2190 AND moq.inventory_item_id = lots.inventory_item_id
2191 AND moq.organization_id = lots.organization_id
2192 AND moq.lot_number = lots.lot_number
2193 AND lots.expiration_date < last_date
2194 AND NVL(lots.expiration_date,trunc(sysdate)) >= trunc(sysdate)
2195 AND moq.inventory_item_id = items.inventory_item_id
2196 AND moq.organization_id = sub.organization_id
2197 AND moq.subinventory_code = sub.sub_inventory_code
2198 AND items.lot_control_code = 2
2199 AND items.organization_id = orgs.planned_organization
2200 AND items.compile_designator = orgs.compile_designator
2201 AND sub.organization_id = orgs.planned_organization
2202 AND sub.compile_designator = orgs.compile_designator
2203 AND sub.netting_type = NETTABLE
2204 AND orgs.organization_id = list.number2
2205 AND orgs.compile_designator = list.char1
2206 AND items.inventory_item_id = list.number1
2207 AND list.query_id = item_list_id
2208 GROUP BY items.inventory_item_id,
2209 items.organization_id,
2210 moq.subinventory_code,
2211 moq.revision,
2212 moq.locator_id,
2213 moq.lot_number,
2214 lots.expiration_date,
2215 mil.project_id,
2216 mil.task_id,
2217 sub.netting_type,
2218 orgs.organization_id,
2219 items.compile_designator
2220 UNION ALL
2221 -----------------
2222 --- On hand
2223 -----------------
2224 --start of bug 3332404 On hand sql
2225 SELECT
2226 moq.Inventory_Item_ID item_id,
2227 moq.Organization_ID org_id,
2228 ON_HAND row_type,
2229 ON_HAND_OFF offset,
2230 to_date(1, 'J') new_date,
2231 to_date(1, 'J') old_date,
2232 NVL(SUM(moq.primary_transaction_quantity),0) new_quantity,
2233 0 old_quantity
2234 FROM MTL_Onhand_Quantities_detail moq,
2235 mtl_parameters param,
2236 mtl_material_statuses mms,
2237 mrp_system_items masis,
2238 MTL_LOT_NUMBERS mln,
2239 MTL_ITEM_LOCATIONS mil,
2240 PJM_PROJECT_PARAMETERS mpp,
2241 mrp_sub_inventories sub,
2242 mrp_form_query list
2243 WHERE (arg_res_level = 1
2244 OR (arg_res_level = 2
2245 AND mil.project_id is NULL)
2246 OR (DECODE(arg_res_level,
2247 3,nvl(mrp_get_project.planning_group(mil.project_id),'-23453'),
2248 4,nvl(to_char(mil.project_id), '-23453')) = nvl(arg_resval1,'-23453'))
2249 OR (arg_res_level = 5
2250 AND nvl(to_char(mil.project_id), '-23453')
2251 = nvl(arg_resval1,'-23453')
2252 AND nvl(mil.task_id, -23453) = nvl(arg_resval2, -23453)))
2253 AND param.organization_id = moq.organization_id
2254 AND moq.status_id = mms.status_id(+)
2255 AND moq.Inventory_Item_ID= masis.Inventory_Item_ID
2256 AND moq.Organization_ID= masis.Organization_ID
2257 AND masis.Effectivity_Control= 1
2258 AND mln.Organization_ID(+)= moq.Organization_ID
2259 AND mln.Inventory_Item_ID(+)= moq.Inventory_Item_ID
2260 AND mln.Lot_Number(+)= moq.Lot_Number
2261 AND mil.Organization_ID(+)= moq.Organization_ID
2262 AND NVL(mln.expiration_date, trunc(sysdate)) >= trunc(sysdate)
2263 AND mil.Inventory_Location_ID(+)= moq.Locator_ID
2264 AND mpp.Project_ID(+)= mil.Project_ID
2265 AND mpp.Organization_ID(+)= mil.Organization_ID
2266 AND sub.organization_id = masis.organization_id
2267 AND sub.compile_designator = masis.compile_designator
2268 AND sub.sub_inventory_code = moq.subinventory_code
2269 AND moq.organization_id = sub.organization_id
2270 AND sub.netting_type = NETTABLE
2271 AND ((param.default_status_id is not null
2272 and mms.availability_type = 1 )
2273 OR
2274 (param.default_status_id is null
2275 AND NVL(mln.availability_type,1) = 1
2276 AND NVL(mil.availability_type,1) = 1 ))
2277 AND masis.organization_id = list.number2
2278 AND masis.compile_designator = list.char1
2279 AND masis.inventory_item_id = list.number1
2280 AND list.query_id = item_list_id
2281 GROUP BY
2282 moq.inventory_item_id,
2283 moq.organization_id,
2284 mil.project_id,
2285 mil.task_id,
2286 sub.sub_inventory_code,
2287 masis.compile_designator,
2288 masis.organization_id,
2289 sub.netting_type
2290 UNION ALL
2291 SELECT /*+ ORDERED */
2292 x.Inventory_Item_ID,
2293 x.Organization_ID,
2294 ON_HAND row_type,
2295 ON_HAND_OFF offset,
2296 to_date(1, 'J') new_date,
2297 to_date(1, 'J') old_date,
2298 NVL(SUM(x.Lot_Quantity) , 0) new_quantity,
2299 0 old_quantity
2300 FROM
2301 ( SELECT
2302 msn.Current_Organization_ID Organization_ID,
2303 msn.Inventory_Item_ID,
2304 msn.Current_Subinventory_Code Subinventory_Code,
2305 1 Lot_Quantity,
2306 masis.compile_designator,
2307 msn.Current_Locator_ID Locator_ID,
2308 msn.Lot_Number
2309 FROM MTL_SERIAL_NUMBERS msn,
2310 mrp_system_items masis
2311 WHERE msn.Current_Status IN ( 3,5)
2312 AND masis.Organization_ID= msn.Current_Organization_ID
2313 AND masis.Inventory_Item_ID= msn.Inventory_Item_ID
2314 AND masis.Effectivity_Control= 2 ) x,
2315 MTL_LOT_NUMBERS mln,
2316 MTL_ITEM_LOCATIONS mil,
2317 PJM_PROJECT_PARAMETERS mpp,
2318 mrp_sub_inventories sub,
2319 mrp_form_query list
2320 WHERE
2321 (arg_res_level = 1
2322 OR (arg_res_level = 2
2323 AND mil.project_id is NULL)
2324 OR (DECODE(arg_res_level,
2325 3,nvl(mrp_get_project.planning_group(mil.project_id),'-23453'),
2326 4,nvl(to_char(mil.project_id), '-23453')) = nvl(arg_resval1,'-23453'))
2327 OR (arg_res_level = 5
2328 AND nvl(to_char(mil.project_id), '-23453')
2329 = nvl(arg_resval1,'-23453')
2330 AND nvl(mil.task_id, -23453) = nvl(arg_resval2, -23453)))
2331 AND mln.Organization_ID(+)= x.Organization_ID
2332 AND mln.Inventory_Item_ID(+)= x.Inventory_Item_ID
2333 AND mln.Lot_Number(+)= x.Lot_Number
2334 AND NVL(mln.expiration_date, trunc(sysdate)) >= trunc(sysdate)
2335 AND mil.Organization_ID(+)= x.Organization_ID
2336 AND mil.Inventory_Location_ID(+)= x.Locator_ID
2337 AND mpp.Project_ID(+)= mil.Project_ID
2338 AND mpp.Organization_ID(+)= mil.Organization_ID
2339 AND sub.organization_id = x.Organization_ID
2340 AND sub.compile_designator = x.compile_designator
2341 AND sub.sub_inventory_code = x.subinventory_code
2342 AND sub.netting_type = NETTABLE
2343 AND NVL(mln.availability_type,1) = 1
2344 AND NVL(mil.availability_type,1) = 1
2345 AND x.organization_id = list.number2
2346 AND x.compile_designator = list.char1
2347 AND x.inventory_item_id = list.number1
2348 AND list.query_id = item_list_id
2349 GROUP BY
2350 x.inventory_item_id,
2351 x.organization_id,
2352 mil.project_id,
2353 mil.task_id,
2354 sub.sub_inventory_code,
2355 x.compile_designator,
2356 x.organization_id,
2357 sub.netting_type
2358 UNION ALL
2359 SELECT
2360 mmtt.Inventory_Item_ID item_id,
2361 mmtt.Organization_ID org_id,
2362 ON_HAND row_type,
2363 ON_HAND_OFF offset,
2364 to_date(1, 'J') new_date,
2365 to_date(1, 'J') old_date,
2366 NVL(SUM(mmtt.Primary_Quantity) , 0) new_quantity,
2367 0 old_quantity
2368 FROM PJM_PROJECT_PARAMETERS mpp,
2369 mrp_system_items masis,
2370 MTL_Material_Transactions_Temp mmtt,
2371 MTL_ITEM_LOCATIONS mil,
2372 mrp_sub_inventories sub,
2373 mrp_form_query list
2374 WHERE
2375 (arg_res_level = 1
2376 OR (arg_res_level = 2
2377 AND mmtt.project_id is NULL)
2378 OR (DECODE(arg_res_level,
2379 3,nvl(mrp_get_project.planning_group(mmtt.project_id),'-23453'),
2380 4,nvl(to_char(mmtt.project_id), '-23453'))
2381 = nvl(arg_resval1,'-23453'))
2382 OR (arg_res_level = 5
2383 AND nvl(to_char(mmtt.project_id), '-23453')
2384 = nvl(arg_resval1,'-23453')
2385 AND nvl(mmtt.task_id, -23453) = nvl(arg_resval2, -23453)))
2386 AND EXISTS
2387 (SELECT 'x'
2388 FROM mtl_material_statuses mms
2389 WHERE
2390 mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(
2391 mmtt.organization_id,
2392 mmtt.inventory_item_id,
2393 mmtt.subinventory_code,
2394 mmtt.locator_id,
2395 mmtt.lot_number,
2396 mmtt.lpn_id,
2397 mmtt.transaction_action_id),
2398 mms.status_id)
2399 AND mms.availability_type =1)
2400 AND mpp.Organization_ID(+)= mmtt.Organization_ID
2401 AND mpp.Project_ID(+)= mmtt.Project_ID
2402 AND mmtt.Posting_Flag= 'Y'
2403 AND masis.Organization_ID= mmtt.Organization_ID
2404 AND masis.Inventory_Item_ID= mmtt.Inventory_Item_ID
2405 AND masis.Effectivity_Control= 1
2406 AND NVL(mmtt.transaction_status,0) <> 2
2407 AND mil.Organization_ID(+)= mmtt.Organization_ID
2408 AND mil.Inventory_Location_ID(+)= mmtt.Locator_ID
2409 AND sub.organization_id = mmtt.Organization_ID
2410 AND sub.compile_designator = masis.compile_designator
2411 AND sub.sub_inventory_code = mmtt.subinventory_code
2412 AND sub.netting_type = NETTABLE
2413 AND NVL(mil.availability_type,1) = 1
2414 AND masis.organization_id = list.number2
2415 AND masis.compile_designator = list.char1
2416 AND masis.inventory_item_id = list.number1
2417 AND list.query_id = item_list_id
2418 group by
2419 mmtt.inventory_item_id,
2420 mmtt.organization_id,
2421 mmtt.project_id,
2422 mmtt.task_id,
2423 sub.sub_inventory_code,
2424 masis.compile_designator,
2425 masis.organization_id,
2426 sub.netting_type
2427 UNION ALL
2428 SELECT
2429 mmtt.Inventory_Item_ID item_id,
2430 mmtt.Organization_ID org_id,
2431 ON_HAND row_type,
2432 ON_HAND_OFF offset,
2433 to_date(1, 'J') new_date,
2434 to_date(1, 'J') old_date,
2435 NVL(SUM(mmtt.Primary_Quantity ) , 0) new_quantity,
2436 0 old_quantity
2437 FROM PJM_PROJECT_PARAMETERS mpp,
2438 mrp_system_items masis,
2439 MTL_Material_Transactions_Temp mmtt,
2440 MTL_ITEM_LOCATIONS mil,
2441 mrp_sub_inventories sub,
2442 mrp_form_query list
2443 WHERE
2444 (arg_res_level = 1
2445 OR(arg_res_level = 2 AND mmtt.project_id is NULL)
2446 OR(DECODE(arg_res_level,3,nvl(mrp_get_project.planning_group(mmtt.project_id),'-23453'),
2447 4,nvl(to_char(mmtt.project_id), '-23453')) = nvl(arg_resval1,'-23453'))
2448 OR (arg_res_level = 5 AND nvl(to_char(mmtt.project_id), '-23453') = nvl(arg_resval1,'-23453')
2449 AND nvl(mmtt.task_id, -23453) = nvl(arg_resval2, -23453)))
2450 AND EXISTS
2451 (SELECT 'x'
2452 FROM mtl_material_statuses mms
2453 WHERE
2454 mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(
2455 mmtt.organization_id,
2456 mmtt.inventory_item_id,
2457 mmtt.subinventory_code,
2458 mmtt.locator_id,
2459 mmtt.lot_number,
2460 mmtt.lpn_id,
2461 mmtt.transaction_action_id),
2462 mms.status_id)
2463 AND mms.availability_type =1)
2464 AND mpp.Organization_ID(+)= mmtt.Organization_ID
2465 AND mpp.Project_ID(+)= mmtt.Project_ID
2466 AND mmtt.Posting_Flag= 'Y'
2467 AND mmtt.Transaction_Action_ID= 1
2468 AND mmtt.Transaction_Source_Type_ID in (1,5)
2469 AND masis.Organization_ID= mmtt.Organization_ID
2470 AND masis.Inventory_Item_ID= mmtt.Inventory_Item_ID
2471 AND masis.Effectivity_Control= 2
2472 AND NVL(mmtt.transaction_status,0) <> 2
2473 AND mil.Organization_ID(+)= mmtt.Organization_ID
2474 AND mil.Inventory_Location_ID(+)= mmtt.Locator_ID
2475 AND sub.organization_id = mmtt.Organization_ID
2476 AND sub.compile_designator = masis.compile_designator
2477 AND sub.sub_inventory_code = mmtt.subinventory_code
2478 AND sub.netting_type = NETTABLE
2479 AND NVL(mil.availability_type,1) = 1
2480 AND masis.organization_id = list.number2
2481 AND masis.compile_designator = list.char1
2482 AND masis.inventory_item_id = list.number1
2483 AND list.query_id = item_list_id
2484 group by
2485 mmtt.inventory_item_id,
2486 mmtt.organization_id,
2487 mmtt.project_id,
2488 mmtt.task_id,
2489 sub.sub_inventory_code,
2490 masis.compile_designator,
2491 masis.organization_id,
2492 sub.netting_type
2493 UNION ALL
2494 SELECT
2495 mmtt.Inventory_Item_ID item_id,
2496 mmtt.Organization_ID org_id,
2497 ON_HAND row_type,
2498 ON_HAND_OFF offset,
2499 to_date(1, 'J') new_date,
2500 to_date(1, 'J') old_date,
2501 NVL(SUM(mmtt.Primary_Quantity ), 0) new_quantity,
2502 0 old_quantity
2503 FROM PJM_PROJECT_PARAMETERS mpp,
2504 MTL_Serial_Numbers_Temp msnt,
2505 MTL_SERIAL_NUMBERS msn,
2506 mrp_system_items masis,
2507 MTL_Material_Transactions_Temp mmtt,
2508 MTL_ITEM_LOCATIONS mil,
2509 mrp_sub_inventories sub,
2510 mrp_form_query list
2511 WHERE
2512 (arg_res_level = 1
2513 OR(arg_res_level = 2 AND mmtt.project_id is NULL)
2514 OR(DECODE(arg_res_level,3,nvl(mrp_get_project.planning_group(mmtt.project_id),'-23453'),
2515 4,nvl(to_char(mmtt.project_id), '-23453')) = nvl(arg_resval1,'-23453'))
2516 OR (arg_res_level = 5 AND nvl(to_char(mmtt.project_id), '-23453') = nvl(arg_resval1,'-23453')
2517 AND nvl(mmtt.task_id, -23453) = nvl(arg_resval2, -23453)))
2518 AND EXISTS
2519 (SELECT 'x'
2520 FROM mtl_material_statuses mms
2521 WHERE
2522 mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(
2523 mmtt.organization_id,
2524 mmtt.inventory_item_id,
2525 mmtt.subinventory_code,
2526 mmtt.locator_id,
2527 mmtt.lot_number,
2528 mmtt.lpn_id,
2529 mmtt.transaction_action_id),
2530 mms.status_id)
2531 AND mms.availability_type =1)
2532 AND mpp.Organization_ID(+)= mmtt.Organization_ID
2533 AND mpp.Project_ID(+)= mmtt.Project_ID
2534 AND msnt.Transaction_Temp_ID= mmtt.Transaction_Temp_ID
2535 AND msnt.Fm_Serial_Number= msn.Serial_Number
2536 AND msn.Inventory_Item_ID= mmtt.Inventory_Item_ID
2537 AND mmtt.Posting_Flag= 'Y'
2538 AND NOT( mmtt.Transaction_Action_ID= 1
2539 AND mmtt.Transaction_Source_Type_ID in (1,5))
2540 AND masis.Organization_ID= mmtt.Organization_ID
2541 AND masis.Inventory_Item_ID= mmtt.Inventory_Item_ID
2542 AND masis.Effectivity_Control= 2
2543 AND NVL(mmtt.transaction_status,0) <> 2
2544 AND mil.Organization_ID(+)= mmtt.Organization_ID
2545 AND mil.Inventory_Location_ID(+)= mmtt.Locator_ID
2546 AND sub.organization_id = mmtt.Organization_ID
2547 AND sub.compile_designator = masis.compile_designator
2548 AND sub.sub_inventory_code = mmtt.subinventory_code
2549 AND sub.netting_type = NETTABLE
2550 AND NVL(mil.availability_type,1) = 1
2551 AND masis.organization_id = list.number2
2552 AND masis.compile_designator = list.char1
2553 AND masis.inventory_item_id = list.number1
2554 AND list.query_id = item_list_id
2555 group by
2556 mmtt.inventory_item_id,
2557 mmtt.organization_id,
2558 mmtt.project_id,
2559 mmtt.task_id,
2560 sub.sub_inventory_code,
2561 masis.compile_designator,
2562 masis.organization_id,
2563 sub.netting_type
2564
2565 --end of bug 3332404 On hand sql
2566 UNION ALL
2567 SELECT MTRL.Inventory_Item_ID item_id,
2568 MTRL.Organization_ID org_id,
2569 ON_HAND row_type,
2570 ON_HAND_OFF offset,
2571 to_date(1, 'J') new_date,
2572 to_date(1, 'J') old_date,
2573 SUM(QUANTITY - NVL(QUANTITY_DELIVERED,0)) new_quantity,
2574 0 old_quantity
2575 FROM MTL_TXN_REQUEST_LINES MTRL,
2576 MTL_TXN_REQUEST_HEADERS MTRH,
2577 MTL_TRANSACTION_TYPES MTT,
2578 PJM_PROJECT_PARAMETERS mpp,
2579 MRP_PLAN_ORGANIZATIONS_V ORG,
2580 MRP_SYSTEM_ITEMS ITEMS,
2581 MRP_FORM_QUERY list
2582 where
2583 (arg_res_level = 1
2584 OR(arg_res_level = 2 AND MTRL.project_id is NULL)
2585 OR(DECODE(arg_res_level,3,nvl(mrp_get_project.planning_group(MTRL.project_id),'-23453'),
2586 4,nvl(to_char(MTRL.project_id), '-23453')) = nvl(arg_resval1,'-23453'))
2587 OR (arg_res_level = 5 AND nvl(to_char(MTRL.project_id), '-23453') = nvl(arg_resval1,'-23453')
2588 AND nvl(MTRL.task_id, -23453) = nvl(arg_resval2, -23453)))
2589 AND ITEMS.ORGANIZATION_ID = ORG.PLANNED_ORGANIZATION
2590 AND mpp.Organization_ID(+)= MTRL.Organization_ID
2591 AND mpp.Project_ID(+)= MTRL.Project_ID
2592 AND ITEMS.COMPILE_DESIGNATOR = ORG.COMPILE_DESIGNATOR
2593 AND MTRL.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
2594 AND MTRL.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
2595 AND MTRH.MOVE_ORDER_TYPE = 6
2596 AND MTRL.TRANSACTION_SOURCE_TYPE_ID = 5
2597 AND MTT.TRANSACTION_ACTION_ID = 31
2598 AND MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID
2599 AND MTRL.LINE_STATUS = 7
2600 AND MTRL.LPN_ID IS NOT NULL
2601 AND MTRH.HEADER_ID = MTRL.HEADER_ID
2602 AND ITEMS.organization_id = list.number2
2603 AND ITEMS.compile_designator = list.char1
2604 AND ITEMS.inventory_item_id = list.number1
2605 AND list.query_id = item_list_id
2606 GROUP BY
2607 MTRL.inventory_item_id,
2608 MTRL.organization_id,
2609 MTRL.project_id,
2610 MTRL.task_id,
2611 ITEMS.compile_designator,
2612 ITEMS.organization_id
2613 UNION ALL
2614 --------------------------------------------------------------------
2615 -- This select will ensure that all selected items get into cursor
2616 -- even though they do not have any activity
2617 ---------------------------------------------------------------------
2618 SELECT list.number1,
2619 list.number2,
2620 ON_HAND,
2621 ON_HAND_OFF,
2622 to_date(1, 'J'),
2623 to_date(1, 'J'),
2624 0,
2625 0
2626 FROM mrp_form_query list
2627 WHERE list.query_id = item_list_id
2628 ORDER BY
2629 1, 2, 5, 3;
2630
2631 -- --------------------------------------------
2632 -- This cursor selects the snapshot activity in
2633 -- MRP_GROSS_REQUIREMENTS and
2634 -- MRP_RECOMMENDATIONS for the items in the
2635 -- item list.
2636 -- --------------------------------------------
2637 CURSOR mrp_snapshot_activity IS
2638 SELECT rec.inventory_item_id item_id,
2639 rec.organization_id org_id,
2640 DECODE(rec.order_type,
2641 PURCHASE_ORDER, PO,
2642 PURCH_REQ, REQ,
2643 WORK_ORDER, WIP,
2644 FLOW_SCHED, WIP,
2645 REPETITIVE_SCHEDULE,PLANNED,
2646 PLANNED_ORDER, PLANNED,
2647 NONSTD_JOB, WIP,
2648 RECEIPT_PURCH_ORDER,RECEIVING,
2649 SHIPMENT, TRANSIT,
2650 RECEIPT_SHIPMENT, RECEIVING,
2651 PAYBACK_SUPPLY, PB_SUPPLY,
2652 PLANNED) row_type,
2653 DECODE(rec.order_type,
2654 PURCHASE_ORDER, PO_OFF,
2655 PURCH_REQ, REQ_OFF,
2656 WORK_ORDER, WIP_OFF,
2657 FLOW_SCHED, WIP_OFF,
2658 REPETITIVE_SCHEDULE,PLANNED_OFF,
2659 PLANNED_ORDER, PLANNED_OFF,
2660 NONSTD_JOB, WIP_OFF,
2661 RECEIPT_PURCH_ORDER,RECEIVING_OFF,
2662 SHIPMENT, TRANSIT_OFF,
2663 RECEIPT_SHIPMENT, RECEIVING_OFF,
2664 DIS_JOB_BY, DEPENDENT_OFF,
2665 NON_ST_JOB_BY, DEPENDENT_OFF,
2666 REP_SCHED_BY, DEPENDENT_OFF,
2667 PLANNED_BY, DEPENDENT_OFF,
2668 FLOW_SCHED_BY, DEPENDENT_OFF,
2669 PAYBACK_SUPPLY, PB_SUPPLY_OFF,
2670 PLANNED_OFF) offset,
2671 dates.calendar_date new_date,
2672 decode(rec.order_type, PAYBACK_SUPPLY,
2673 dates.calendar_date, rec.old_schedule_date) old_date,
2674 SUM(DECODE(rec.disposition_status_type,
2675 1, DECODE(rec.last_unit_completion_date,
2676 NULL, rec.new_order_quantity,
2677 rec.daily_rate) *
2678 DECODE(rec.order_type,
2679 DIS_JOB_BY, -1,
2680 NON_ST_JOB_BY, -1,
2681 REP_SCHED_BY, -1,
2682 PLANNED_BY, -1,
2683 FLOW_SCHED_BY, -1,
2684 1)
2685 , 0)) new_quantity,
2686 SUM(NVL(rec.old_order_quantity,0)) old_quantity
2687 FROM mrp_form_query list,
2688 mtl_parameters param,
2689 mrp_recommendations rec,
2690 bom_calendar_dates dates
2691 WHERE (arg_res_level = 1
2692 OR (arg_res_level = 2
2693 AND rec.project_id is NULL)
2694 OR (DECODE(arg_res_level,
2695 3,nvl(rec.planning_group,'-23453'),
2696 4,nvl(to_char(rec.project_id), '-23453'))
2697 = nvl(arg_resval1,'-23453'))
2698 OR (arg_res_level = 5
2699 AND nvl(to_char(rec.project_id), '-23453')
2700 = nvl(arg_resval1,'-23453')
2701 AND nvl(rec.task_id, -23453) = nvl(arg_resval2, -23453)))
2702 AND dates.exception_set_id = param.calendar_exception_set_id
2703 AND dates.calendar_code = param.calendar_code
2704 --AND dates.seq_num IS NOT NULL
2705 AND (( rec.last_unit_completion_date IS NOT NULL /* Repetitively planned item */
2706 AND dates.seq_num IS NOT NULL )
2707 OR
2708 ( rec.last_unit_completion_date IS NULL ))
2709 AND dates.calendar_date BETWEEN rec.new_schedule_date
2710 AND NVL(rec.last_unit_completion_date, rec.new_schedule_date)
2711 AND (rec.new_schedule_date < last_date OR
2712 rec.old_schedule_date < last_date)
2713 AND rec.compile_designator = list.char1
2714 AND rec.inventory_item_id = list.number1
2715 AND rec.organization_id = list.number2
2716 AND param.organization_id = list.number2
2717 AND list.query_id = item_list_id
2718 GROUP BY
2719 rec.inventory_item_id,
2720 rec.organization_id,
2721 DECODE(rec.order_type,
2722 PURCHASE_ORDER, PO,
2723 PURCH_REQ, REQ,
2724 WORK_ORDER, WIP,
2725 FLOW_SCHED, WIP,
2726 REPETITIVE_SCHEDULE,PLANNED,
2727 PLANNED_ORDER, PLANNED,
2728 NONSTD_JOB, WIP,
2729 RECEIPT_PURCH_ORDER,RECEIVING,
2730 SHIPMENT, TRANSIT,
2731 RECEIPT_SHIPMENT, RECEIVING,
2732 PAYBACK_SUPPLY, PB_SUPPLY,
2733 PLANNED),
2734 DECODE(rec.order_type,
2735 PURCHASE_ORDER, PO_OFF,
2736 PURCH_REQ, REQ_OFF,
2737 WORK_ORDER, WIP_OFF,
2738 FLOW_SCHED, WIP_OFF,
2739 REPETITIVE_SCHEDULE,PLANNED_OFF,
2740 PLANNED_ORDER, PLANNED_OFF,
2741 NONSTD_JOB, WIP_OFF,
2742 RECEIPT_PURCH_ORDER,RECEIVING_OFF,
2743 SHIPMENT, TRANSIT_OFF,
2744 RECEIPT_SHIPMENT, RECEIVING_OFF,
2745 DIS_JOB_BY, DEPENDENT_OFF,
2746 NON_ST_JOB_BY, DEPENDENT_OFF,
2747 REP_SCHED_BY, DEPENDENT_OFF,
2748 PLANNED_BY, DEPENDENT_OFF,
2749 FLOW_SCHED_BY, DEPENDENT_OFF,
2750 PAYBACK_SUPPLY, PB_SUPPLY_OFF,
2751 PLANNED_OFF),
2752 dates.calendar_date,
2753 decode(rec.order_type, PAYBACK_SUPPLY, dates.calendar_date,
2754 rec.old_schedule_date)
2755 UNION ALL
2756 SELECT mgr.inventory_item_id item_id,
2757 mgr.organization_id org_id,
2758 DECODE(mgr.origination_type,
2759 1, DEPENDENT,
2760 2, DEPENDENT,
2761 3, DEPENDENT,
2762 4, DEPENDENT,
2763 5, EXP_LOT,
2764 6, SALES,
2765 7, FORECAST,
2766 8, OTHER,
2767 9, OTHER,
2768 10, OTHER,
2769 11, OTHER,
2770 12, OTHER,
2771 15, OTHER,
2772 16, SCRAP,
2773 17, SCRAP,
2774 18, SCRAP,
2775 19, SCRAP,
2776 20, SCRAP,
2777 21, SCRAP,
2778 22, DEPENDENT,
2779 23, SCRAP,
2780 24, DEPENDENT,
2781 25, DEPENDENT,
2782 26, SCRAP,
2783 DEMAND_PAYBACK, PB_DEMAND,
2784 OTHER) row_type,
2785 DECODE(mgr.origination_type,
2786 1, DEPENDENT_OFF,
2787 2, DEPENDENT_OFF,
2788 3, DEPENDENT_OFF,
2789 4, DEPENDENT_OFF,
2790 5, EXP_LOT_OFF,
2791 6, SALES_OFF,
2792 7, FORECAST_OFF,
2793 8, OTHER_OFF,
2794 9, OTHER_OFF,
2795 10, OTHER_OFF,
2796 11, OTHER_OFF,
2797 12, OTHER_OFF,
2798 15, OTHER_OFF,
2799 16, SCRAP_OFF,
2800 17, SCRAP_OFF,
2801 18, SCRAP_OFF,
2802 19, SCRAP_OFF,
2803 20, SCRAP_OFF,
2804 21, SCRAP_OFF,
2805 22, DEPENDENT_OFF,
2806 23, SCRAP_OFF,
2807 24, DEPENDENT_OFF,
2808 25, DEPENDENT_OFF,
2809 26, SCRAP_OFF,
2810 DEMAND_PAYBACK, PB_DEMAND_OFF,
2811 OTHER_OFF) offset,
2812 dates.calendar_date new_date,
2813 dates.calendar_date old_date,
2814 SUM(DECODE(mgr.assembly_demand_comp_date,
2815 NULL, using_requirements_quantity,
2816 daily_demand_rate)) new_quantity,
2817 0 old_quantity
2818 FROM mrp_form_query list,
2819 mtl_parameters param,
2820 mrp_gross_requirements mgr,
2821 bom_calendar_dates dates
2822 WHERE (arg_res_level = 1
2823 OR (arg_res_level = 2
2824 AND mgr.project_id is NULL)
2825 OR (DECODE(arg_res_level,
2826 3,nvl(mgr.planning_group,'-23453'),
2827 4,nvl(to_char(mgr.project_id), '-23453'))
2828 = nvl(arg_resval1,'-23453'))
2829 OR (arg_res_level = 5
2830 AND nvl(to_char(mgr.project_id), '-23453')
2831 = nvl(arg_resval1,'-23453')
2832 AND nvl(mgr.task_id, -23453) = nvl(arg_resval2, -23453)))
2833 AND dates.exception_set_id = param.calendar_exception_set_id
2834 AND dates.calendar_code = param.calendar_code
2835 --AND dates.seq_num IS NOT NULL
2836 AND (( mgr.assembly_demand_comp_date IS NOT NULL /* Repetitively planned item */
2837 AND dates.seq_num IS NOT NULL )
2838 OR
2839 ( mgr.assembly_demand_comp_date IS NULL ))
2840 AND dates.calendar_date BETWEEN mgr.using_assembly_demand_date
2841 AND NVL(mgr.assembly_demand_comp_date, mgr.using_assembly_demand_date)
2842 AND mgr.using_assembly_demand_date < last_date
2843 AND mgr.compile_designator = list.char1
2844 AND mgr.inventory_item_id = list.number1
2845 AND mgr.organization_id = list.number2
2846 AND param.organization_id = list.number2
2847 AND list.query_id = item_list_id
2848 GROUP BY
2849 mgr.inventory_item_id,
2850 mgr.organization_id,
2851 DECODE(mgr.origination_type,
2852 1, DEPENDENT,
2853 2, DEPENDENT,
2854 3, DEPENDENT,
2855 4, DEPENDENT,
2856 5, EXP_LOT,
2857 6, SALES,
2858 7, FORECAST,
2859 8, OTHER,
2860 9, OTHER,
2861 10, OTHER,
2862 11, OTHER,
2863 12, OTHER,
2864 15, OTHER,
2865 16, SCRAP,
2866 17, SCRAP,
2867 18, SCRAP,
2868 19, SCRAP,
2869 20, SCRAP,
2870 21, SCRAP,
2871 22, DEPENDENT,
2872 23, SCRAP,
2873 24, DEPENDENT,
2874 25, DEPENDENT,
2875 26, SCRAP,
2876 DEMAND_PAYBACK, PB_DEMAND,
2877 OTHER),
2878 DECODE(mgr.origination_type,
2879 1, DEPENDENT_OFF,
2880 2, DEPENDENT_OFF,
2881 3, DEPENDENT_OFF,
2882 4, DEPENDENT_OFF,
2883 5, EXP_LOT_OFF,
2884 6, SALES_OFF,
2885 7, FORECAST_OFF,
2886 8, OTHER_OFF,
2887 9, OTHER_OFF,
2888 10, OTHER_OFF,
2889 11, OTHER_OFF,
2890 12, OTHER_OFF,
2891 15, OTHER_OFF,
2892 16, SCRAP_OFF,
2893 17, SCRAP_OFF,
2894 18, SCRAP_OFF,
2895 19, SCRAP_OFF,
2896 20, SCRAP_OFF,
2897 21, SCRAP_OFF,
2898 22, DEPENDENT_OFF,
2899 23, SCRAP_OFF,
2900 24, DEPENDENT_OFF,
2901 25, DEPENDENT_OFF,
2902 26, SCRAP_OFF,
2903 DEMAND_PAYBACK, PB_DEMAND_OFF,
2904 OTHER_OFF),
2905 dates.calendar_date,
2906 dates.calendar_date,
2907 0
2908 UNION ALL
2909 SELECT avail.inventory_item_id item_id,
2910 avail.organization_id org_id,
2911 ATP row_type,
2912 ATP_OFF offset,
2913 avail.schedule_date new_date,
2914 avail.schedule_date old_date,
2915 avail.quantity_available new_quantity,
2916 0 old_quantity
2917 FROM mrp_form_query list,
2918 mrp_available_to_promise avail
2919 WHERE avail.schedule_date < last_date
2920 AND avail.organization_id = list.number2
2921 AND avail.compile_designator = list.char1
2922 AND avail.inventory_item_id = list.number1
2923 AND list.query_id = item_list_id
2924 UNION ALL
2925 SELECT items.inventory_item_id item_id,
2926 items.organization_id org_id,
2927 ON_HAND row_type,
2928 ON_HAND_OFF offset,
2929 to_date(1, 'J') new_date,
2930 to_date(1, 'J') old_date,
2931 items.nettable_quantity new_quantity,
2932 0 old_quantity
2933 FROM mrp_plans plans,
2934 mrp_onhand_quantities items,
2935 mrp_form_query list
2936 WHERE ((arg_res_level = 1 )
2937 OR (arg_res_level = 2
2938 AND items.project_id is null)
2939 OR (DECODE(arg_res_level, 3, nvl(items.planning_group,'-23453'),
2940 4, nvl(to_char(items.project_id),'-23453'))
2941 = nvl(arg_resval1,'-23453'))
2942 OR ( arg_res_level = 5
2943 AND nvl(to_char(items.project_id),'-23453')
2944 = nvl(arg_resval1,'-23453')
2945 AND nvl(items.task_id,-23453) = nvl(arg_resval2,-23453)))
2946 AND plans.curr_reservation_level in (1, 2, 3)
2947 AND plans.organization_id = arg_plan_organization_id
2948 AND plans.compile_designator = arg_compile_designator
2949 AND items.organization_id = list.number2
2950 AND items.compile_designator = list.char1
2951 AND items.inventory_item_id = list.number1
2952 AND list.query_id = item_list_id
2953 UNION ALL
2954 SELECT items.inventory_item_id item_id,
2955 items.organization_id org_id,
2956 ON_HAND row_type,
2957 ON_HAND_OFF offset,
2958 to_date(1, 'J') new_date,
2959 to_date(1, 'J') old_date,
2960 items.nettable_inventory_quantity new_quantity,
2961 0 old_quantity
2962 FROM mrp_plans plans,
2963 mrp_system_items items,
2964 mrp_form_query list
2965 WHERE arg_res_level = 1
2966 AND (plans.curr_reservation_level = 4 OR
2967 plans.curr_reservation_level is NULL)
2968 AND plans.organization_id = arg_plan_organization_id
2969 AND plans.compile_designator = arg_compile_designator
2970 AND items.organization_id = list.number2
2971 AND items.compile_designator = list.char1
2972 AND items.inventory_item_id = list.number1
2973 AND list.query_id = item_list_id
2974 UNION ALL
2975 SELECT safety.inventory_item_id item_id,
2976 safety.organization_id org_id,
2977 SS row_type,
2978 SS_OFF offset,
2979 safety.period_start_date new_date,
2980 safety.period_start_date old_date,
2981 safety.safety_stock_quantity new_quantity,
2982 0 old_quantity
2983 FROM mrp_safety_stock safety,
2984 mrp_form_query list
2985 WHERE safety.period_start_date < last_date
2986 AND safety.organization_id = list.number2
2987 AND safety.compile_designator = list.char1
2988 AND safety.inventory_item_id = list.number1
2989 AND list.query_id = item_list_id
2990 UNION ALL
2991 SELECT sched.inventory_item_id item_id,
2992 sched.organization_id org_id,
2993 CURRENT_S row_type,
2994 CURRENT_S_OFF offset,
2995 dates.calendar_date new_date,
2996 dates.calendar_date old_date,
2997 sched.daily_rate new_quantity,
2998 0 old_quantity
2999 FROM mrp_form_query list,
3000 mtl_parameters param,
3001 mrp_aggregate_rates sched,
3002 bom_calendar_dates dates
3003 WHERE dates.exception_set_id = param.calendar_exception_set_id
3004 AND dates.calendar_code = param.calendar_code
3005 AND dates.seq_num IS NOT NULL
3006 AND dates.calendar_date BETWEEN sched.first_unit_completion_date
3007 AND sched.last_unit_completion_date
3008 AND sched.first_unit_completion_date < last_date
3009 AND sched.compile_designator = list.char1
3010 AND sched.inventory_item_id = list.number1
3011 AND sched.organization_id = param.organization_id
3012 AND param.organization_id = list.number2
3013 AND list.query_id = item_list_id
3014 UNION ALL
3015 --------------------------------------------------------------------
3016 -- This select will ensure that all selected items get into cursor
3017 -- even though they do not have any activity
3018 ---------------------------------------------------------------------
3019 SELECT list.number1,
3020 list.number2,
3021 ON_HAND,
3022 ON_HAND_OFF,
3023 to_date(1, 'J'),
3024 to_date(1, 'J'),
3025 0,
3026 0
3027 FROM mrp_form_query list
3028 WHERE list.query_id = item_list_id
3029
3030 ORDER BY
3031 1, 2, 5, 3;
3032
3033 TYPE mrp_activity IS RECORD
3034 (item_id NUMBER,
3035 org_id NUMBER,
3036 row_type NUMBER,
3037 offset NUMBER,
3038 new_date DATE,
3039 old_date DATE,
3040 new_quantity NUMBER,
3041 old_quantity NUMBER);
3042
3043 activity_rec mrp_activity;
3044
3045 TYPE calendar_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
3046 TYPE column_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3047 TYPE column_char IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
3048
3049 var_dates calendar_date; -- Holds the start dates of buckets
3050 bucket_cells_tab column_number; -- Holds the quantities per bucket
3051 bucket_type_tab column_number;
3052 bucket_type_txt_tab column_char;
3053 last_item_id NUMBER := -1;
3054 last_org_id NUMBER := -1;
3055 prev_ss_quantity NUMBER := -1;
3056 prev_ss_date DATE;
3057 ss_quantity NUMBER := -1;
3058 ss_date DATE;
3059
3060 bucket_counter BINARY_INTEGER := 0; -- Id of the current bucket
3061 old_bucket_counter BINARY_INTEGER := 0;
3062 counter BINARY_INTEGER := 0;
3063
3064 -- =============================================================================
3065 --
3066 -- add_to_plan add the 'quantity' to the correct cell of the current bucket.
3067 --
3068 -- =============================================================================
3069 PROCEDURE add_to_plan(bucket IN NUMBER,
3070 offset IN NUMBER,
3071 quantity IN NUMBER) IS
3072 location NUMBER;
3073 BEGIN
3074 IF enterprize_view THEN
3075 location := (bucket - 1) + offset;
3076 ELSE
3077 location := ((bucket - 1) * NUM_OF_TYPES) + offset;
3078 END IF;
3079 bucket_cells_tab(location) := NVL(bucket_cells_tab(location),0) + quantity;
3080 END;
3081
3082 -- =============================================================================
3083 --
3084 -- flush_item_plan inserts into MRP_MATERIAL_PLANS
3085 --
3086 -- =============================================================================
3087 PROCEDURE flush_item_plan(inv_item_id IN NUMBER,
3088 org_id IN NUMBER) IS
3089 loop_counter BINARY_INTEGER := 1;
3090 item_name VARCHAR2(255);
3091 org_code VARCHAR2(3);
3092 atp_counter BINARY_INTEGER := 1;
3093 total_reqs NUMBER := 0;
3094 lot_quantity NUMBER := 0;
3095 expired_qty NUMBER := 0;
3096 total_supply NUMBER := 0;
3097 committed_demand NUMBER := 0;
3098 atp_qty NUMBER := 0;
3099 carried_back_atp_qty NUMBER := 0;
3100 atp_flag NUMBER;
3101
3102 BEGIN
3103 -- ---------------------
3104 -- Get the item segments
3105 -- ---------------------
3106 SELECT item_number,
3107 param.organization_code
3108 INTO item_name,
3109 org_code
3110 FROM mtl_item_flexfields items,
3111 mtl_parameters param
3112 WHERE param.organization_id = items.organization_id
3113 AND items.inventory_item_id = inv_item_id
3114 AND items.organization_id = org_id;
3115
3116 -- -------------------------
3117 -- get the calculate_atp flag
3118 -- -------------------------
3119
3120 SELECT calculate_atp
3121 INTO atp_flag
3122 FROM mrp_system_items
3123 WHERE compile_designator = arg_compile_designator
3124 AND inventory_item_id = inv_item_id
3125 AND organization_id = org_id;
3126
3127 IF NOT enterprize_view THEN
3128 -- -----------------------------
3129 -- Calculate gross requirements,
3130 -- Total suppy
3131 -- PAB
3132 -- POH
3133 -- -----------------------------
3134
3135 FOR loop IN 1..NUM_OF_COLUMNS LOOP
3136 ----------------------
3137 -- Gross requirements.
3138 -- -------------------
3139 -- dbms_output.put_line('r 101');
3140 lot_quantity := bucket_cells_tab(((loop - 1) * NUM_OF_TYPES)+
3141 EXP_LOT_OFF);
3142 total_reqs := total_reqs +
3143 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SALES_OFF) +
3144 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) +
3145 FORECAST_OFF) +
3146 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) +
3147 DEPENDENT_OFF) +
3148 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) +
3149 PB_DEMAND_OFF) +
3150 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + OTHER_OFF);
3151 --------------------
3152 -- Lot Expirations
3153 --------------------
3154 -- dbms_output.put_line('r 102');
3155 IF(lot_quantity > total_reqs and lot_quantity > 0
3156 and arg_current_data = 1) THEN
3157 expired_qty := lot_quantity - total_reqs;
3158 total_reqs := 0;
3159 add_to_plan(loop,
3160 GROSS_OFF,
3161 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SALES_OFF) +
3162 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + FORECAST_OFF) +
3163 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + DEPENDENT_OFF) +
3164 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PB_DEMAND_OFF) +
3165 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SCRAP_OFF) +
3166 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + OTHER_OFF) +
3167 expired_qty);
3168 ELSE
3169 -- dbms_output.put_line('r 103');
3170 IF arg_current_data = 1 THEN
3171 -- Exclude lot expiration in the current view.
3172 add_to_plan(loop,
3173 GROSS_OFF,
3174 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SALES_OFF) +
3175 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + FORECAST_OFF) +
3176 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + DEPENDENT_OFF) +
3177 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PB_DEMAND_OFF) +
3178 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SCRAP_OFF) +
3179 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + OTHER_OFF));
3180 ELSE
3181 --Include Expired lot quantity in the snapshot view.
3182 add_to_plan(loop,
3183 GROSS_OFF,
3184 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SALES_OFF) +
3185 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + FORECAST_OFF) +
3186 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + DEPENDENT_OFF) +
3187 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PB_DEMAND_OFF) +
3188 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SCRAP_OFF) +
3189 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + EXP_LOT_OFF) +
3190 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + OTHER_OFF));
3191 END IF;
3192 END IF;
3193
3194
3195 -- -------------
3196 -- Total supply.
3197 -- -------------
3198 -- dbms_output.put_line('r 104');
3199 add_to_plan(loop,
3200 SUPPLY_OFF,
3201 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + WIP_OFF) +
3202 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PO_OFF) +
3203 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + REQ_OFF) +
3204 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + TRANSIT_OFF) +
3205 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + RECEIVING_OFF) +
3206 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PB_SUPPLY_OFF) +
3207 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + PLANNED_OFF));
3208
3209 -- ----------------------------
3210 -- Projected available balance.
3211 -- ----------------------------
3212 IF loop = 1 THEN
3213 -- dbms_output.put_line('r 105');
3214 add_to_plan(loop,
3215 PAB_OFF,
3216 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + ON_HAND_OFF) +
3217 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SUPPLY_OFF) -
3218 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + GROSS_OFF));
3219 ELSE
3220 -- dbms_output.put_line('r 106');
3221 add_to_plan(loop,
3222 PAB_OFF,
3223 bucket_cells_tab(((loop - 2) * NUM_OF_TYPES) + PAB_OFF) +
3224 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + SUPPLY_OFF) -
3225 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + GROSS_OFF));
3226 END IF;
3227
3228 -- ------------------
3229 -- Projected on hand.
3230 -- ------------------
3231 IF loop = 1 THEN
3232 -- dbms_output.put_line('r 107');
3233 add_to_plan(loop,
3234 POH_OFF,
3235 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + ON_HAND_OFF) +
3236 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + CURRENT_S_OFF) -
3237 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + GROSS_OFF));
3238 ELSE
3239 -- dbms_output.put_line('r 108');
3240 add_to_plan(loop,
3241 POH_OFF,
3242 bucket_cells_tab(((loop - 2) * NUM_OF_TYPES) + POH_OFF) +
3243 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + CURRENT_S_OFF) -
3244 bucket_cells_tab(((loop - 1) * NUM_OF_TYPES) + GROSS_OFF));
3245 END IF;
3246
3247 END LOOP; -- columnd
3248 ----------------
3249 -- calculate ATP
3250 ----------------
3251 if atp_flag = 1 then -- only calculate atp when atp_flag is 1
3252
3253 IF arg_current_data = 1 THEN
3254 FOR atp_counter IN REVERSE 1..NUM_OF_COLUMNS LOOP
3255 IF atp_counter = 1 THEN
3256 total_supply := bucket_cells_tab(((atp_counter - 1)
3257 * NUM_OF_TYPES) + SUPPLY_OFF)+
3258 bucket_cells_tab(((atp_counter - 1) *
3259 NUM_OF_TYPES) + ON_HAND_OFF);
3260 ELSE
3261 total_supply := bucket_cells_tab(((atp_counter - 1)
3262 * NUM_OF_TYPES) + SUPPLY_OFF);
3263 END IF;
3264 committed_demand := bucket_cells_tab(((atp_counter - 1)
3265 * NUM_OF_TYPES) + SALES_OFF) +
3266 bucket_cells_tab(((atp_counter - 1) *
3267 NUM_OF_TYPES) + DEPENDENT_OFF) +
3268 bucket_cells_tab(((atp_counter - 1) *
3269 NUM_OF_TYPES) + SCRAP_OFF);
3270 atp_qty := total_supply - committed_demand -
3271 carried_back_atp_qty;
3272 IF(atp_qty >= 0) THEN
3273 add_to_plan(atp_counter, ATP_OFF, atp_qty);
3274 carried_back_atp_qty := 0;
3275 atp_qty := 0;
3276 ELSE
3277 add_to_plan(atp_counter, ATP_OFF, 0);
3278 carried_back_atp_qty := -atp_qty;
3279 atp_qty := 0;
3280 END IF;
3281
3282 END LOOP;
3283 END IF;
3284 END IF;
3285
3286 FOR loop_counter IN 1..NUM_OF_TYPES LOOP
3287 INSERT INTO mrp_material_plans(
3288 plan_id,
3289 organization_id,
3290 compile_designator,
3291 plan_organization_id,
3292 inventory_item_id,
3293 item_segments,
3294 organization_code,
3295 horizontal_plan_type,
3296 horizontal_plan_type_text,
3297 bucket_type,
3298 last_update_date,
3299 last_updated_by,
3300 creation_date,
3301 created_by,
3302 quantity1, quantity2, quantity3, quantity4,
3303 quantity5, quantity6, quantity7, quantity8,
3304 quantity9, quantity10, quantity11, quantity12,
3305 quantity13, quantity14, quantity15, quantity16,
3306 quantity17, quantity18, quantity19, quantity20,
3307 quantity21, quantity22, quantity23, quantity24,
3308 quantity25, quantity26, quantity27, quantity28,
3309 quantity29, quantity30, quantity31, quantity32,
3310 quantity33, quantity34, quantity35, quantity36)
3311 VALUES (
3312 arg_plan_id,
3313 org_id,
3314 arg_compile_designator,
3315 arg_plan_organization_id,
3316 inv_item_id,
3317 item_name,
3318 org_code,
3319 bucket_type_tab(loop_counter),
3320 bucket_type_txt_tab(loop_counter),
3321 --arg_bucket_type,--3468984
3322 DECODE(arg_current_data, SYS_YES, DECODE(arg_bucket_type, 1, -4, 2, -5, 3, -6),
3323 decode(arg_bucket_type,1,4,
3324 2,5,
3325 3,6)),
3326 SYSDATE,
3327 -1,
3328 SYSDATE,
3329 -1,
3330 bucket_cells_tab((NUM_OF_TYPES * 0) + loop_counter),
3331 bucket_cells_tab((NUM_OF_TYPES * 1) + loop_counter),
3332 bucket_cells_tab((NUM_OF_TYPES * 2) + loop_counter),
3333 bucket_cells_tab((NUM_OF_TYPES * 3) + loop_counter),
3334 bucket_cells_tab((NUM_OF_TYPES * 4) + loop_counter),
3335 bucket_cells_tab((NUM_OF_TYPES * 5) + loop_counter),
3336 bucket_cells_tab((NUM_OF_TYPES * 6) + loop_counter),
3337 bucket_cells_tab((NUM_OF_TYPES * 7) + loop_counter),
3338 bucket_cells_tab((NUM_OF_TYPES * 8) + loop_counter),
3339 bucket_cells_tab((NUM_OF_TYPES * 9) + loop_counter),
3340 bucket_cells_tab((NUM_OF_TYPES * 10) + loop_counter),
3341 bucket_cells_tab((NUM_OF_TYPES * 11) + loop_counter),
3342 bucket_cells_tab((NUM_OF_TYPES * 12) + loop_counter),
3343 bucket_cells_tab((NUM_OF_TYPES * 13) + loop_counter),
3344 bucket_cells_tab((NUM_OF_TYPES * 14) + loop_counter),
3345 bucket_cells_tab((NUM_OF_TYPES * 15) + loop_counter),
3346 bucket_cells_tab((NUM_OF_TYPES * 16) + loop_counter),
3347 bucket_cells_tab((NUM_OF_TYPES * 17) + loop_counter),
3348 bucket_cells_tab((NUM_OF_TYPES * 18) + loop_counter),
3349 bucket_cells_tab((NUM_OF_TYPES * 19) + loop_counter),
3350 bucket_cells_tab((NUM_OF_TYPES * 20) + loop_counter),
3351 bucket_cells_tab((NUM_OF_TYPES * 21) + loop_counter),
3352 bucket_cells_tab((NUM_OF_TYPES * 22) + loop_counter),
3353 bucket_cells_tab((NUM_OF_TYPES * 23) + loop_counter),
3354 bucket_cells_tab((NUM_OF_TYPES * 24) + loop_counter),
3355 bucket_cells_tab((NUM_OF_TYPES * 25) + loop_counter),
3356 bucket_cells_tab((NUM_OF_TYPES * 26) + loop_counter),
3357 bucket_cells_tab((NUM_OF_TYPES * 27) + loop_counter),
3358 bucket_cells_tab((NUM_OF_TYPES * 28) + loop_counter),
3359 bucket_cells_tab((NUM_OF_TYPES * 29) + loop_counter),
3360 bucket_cells_tab((NUM_OF_TYPES * 30) + loop_counter),
3361 bucket_cells_tab((NUM_OF_TYPES * 31) + loop_counter),
3362 bucket_cells_tab((NUM_OF_TYPES * 32) + loop_counter),
3363 bucket_cells_tab((NUM_OF_TYPES * 33) + loop_counter),
3364 bucket_cells_tab((NUM_OF_TYPES * 34) + loop_counter),
3365 bucket_cells_tab((NUM_OF_TYPES * 35) + loop_counter));
3366 END LOOP;
3367 ELSE
3368 -- apatanka
3369 bucket_cells_tab(OTHER_OFF) :=
3370 bucket_cells_tab(OTHER_OFF)+bucket_cells_tab(PB_DEMAND_OFF);
3371 bucket_cells_tab(GROSS_OFF) :=
3372 bucket_cells_tab(SALES_OFF)+bucket_cells_tab(FORECAST_OFF)+
3373 bucket_cells_tab(DEPENDENT_OFF)+bucket_cells_tab(SCRAP_OFF)+
3374 bucket_cells_tab(OTHER_OFF);
3375 bucket_cells_tab(SUPPLY_OFF):=
3376 bucket_cells_tab(WIP_OFF)+bucket_cells_tab(PO_OFF)+
3377 bucket_cells_tab(REQ_OFF)+bucket_cells_tab(TRANSIT_OFF)+
3378 bucket_cells_tab(RECEIVING_OFF)+bucket_cells_tab(PLANNED_OFF)+
3379 bucket_cells_tab(PB_SUPPLY_OFF);
3380
3381 INSERT INTO mrp_material_plans(
3382 plan_id,
3383 organization_id,
3384 compile_designator,
3385 plan_organization_id,
3386 inventory_item_id,
3387 item_segments,
3388 organization_code,
3389 horizontal_plan_type,
3390 horizontal_plan_type_text,
3391 bucket_type,
3392 last_update_date,
3393 last_updated_by,
3394 creation_date,
3395 created_by,
3396 quantity1, quantity2, quantity3, quantity4,
3397 quantity5, quantity6, quantity7, quantity8,
3398 quantity9, quantity10, quantity11, quantity12,
3399 quantity13, quantity14, quantity15, quantity16,
3400 quantity17, quantity18, quantity19, quantity20,
3401 quantity21, quantity22, quantity23, quantity24,
3402 quantity25, quantity26, quantity27, quantity28,
3403 quantity29, quantity30, quantity31, quantity32,
3404 quantity33, quantity34, quantity35, quantity36)
3405 VALUES (
3406 arg_plan_id,
3407 org_id,
3408 arg_compile_designator,
3409 arg_plan_organization_id,
3410 inv_item_id,
3411 item_name,
3412 org_code,
3413 10,
3414 'ENTERPRIZE_VIEW',
3415 arg_bucket_type,
3416 SYSDATE,
3417 -1,
3418 SYSDATE,
3419 -1,
3420 bucket_cells_tab(1),
3421 bucket_cells_tab(2),
3422 bucket_cells_tab(3),
3423 bucket_cells_tab(4),
3424 bucket_cells_tab(5),
3425 bucket_cells_tab(6),
3426 bucket_cells_tab(7),
3427 bucket_cells_tab(8),
3428 bucket_cells_tab(9),
3429 bucket_cells_tab(10),
3430 bucket_cells_tab(11),
3431 bucket_cells_tab(12),
3432 bucket_cells_tab(13),
3433 bucket_cells_tab(14),
3434 bucket_cells_tab(15),
3435 bucket_cells_tab(16),
3436 bucket_cells_tab(17),
3437 bucket_cells_tab(18),
3438 bucket_cells_tab(19),
3439 bucket_cells_tab(20),
3440 bucket_cells_tab(21),
3441 bucket_cells_tab(22),
3442 bucket_cells_tab(23),
3443 bucket_cells_tab(24),
3444 bucket_cells_tab(25),
3445 bucket_cells_tab(26),
3446 bucket_cells_tab(27),
3447 bucket_cells_tab(28),
3448 bucket_cells_tab(29),
3449 bucket_cells_tab(30),
3450 bucket_cells_tab(31),
3451 bucket_cells_tab(32),
3452 bucket_cells_tab(33),
3453 bucket_cells_tab(34),
3454 bucket_cells_tab(35),
3455 bucket_cells_tab(36));
3456 -- apatanka
3457 -- update mrp_material_plans set quantity6 = quantity1+quantity2+quantity3
3458 -- +quantity4+quantity5
3459 -- where inventory_item_id = inv_item_id and
3460 -- organization_id = arg_plan_organization_id and
3461 -- compile_designator = arg_compile_designator and
3462 -- horizontal_plan_type_text = 'ENTERPRIZE_VIEW';
3463
3464
3465 END IF;
3466
3467 END flush_item_plan;
3468
3469 -- =============================================================================
3470 BEGIN
3471 --- fnd_global.initialize (sid, 0, 101, 1, 0, 0, 0, 0, 0, 0);
3472
3473 SELECT OE_INSTALL.Get_Active_Product
3474 INTO l_oe_install
3475 FROM DUAL;
3476
3477 -- --------------------------
3478 -- Setup the row type tables.
3479 -- --------------------------
3480 counter := 1;
3481
3482 IF NOT enterprize_view THEN
3483 OPEN row_types;
3484
3485 LOOP
3486 FETCH row_types
3487 INTO row_type_rec;
3488
3489 EXIT WHEN row_types%NOTFOUND;
3490
3491 bucket_type_tab(counter) := row_type_rec.lookup_code;
3492 bucket_type_txt_tab(counter) := row_type_rec.meaning;
3493 counter := counter + 1;
3494 END LOOP;
3495 CLOSE row_types;
3496 END IF;
3497
3498 -- ---------------------------------
3499 -- Initialize the bucket cells to 0.
3500 -- ---------------------------------
3501 IF enterprize_view THEN
3502 FOR counter IN 1..NUM_OF_COLUMNS LOOP
3503 bucket_cells_tab(counter) := 0;
3504 END LOOP;
3505 last_date := arg_cutoff_date;
3506 ELSE
3507 FOR counter IN 1..(NUM_OF_TYPES * NUM_OF_COLUMNS) LOOP
3508 bucket_cells_tab(counter) := 0;
3509 END LOOP;
3510
3511 -- --------------------
3512 -- Bug 3468984 bucket_type clause changed for 4,5,6
3513 -- Get the bucket dates
3514 -- --------------------
3515 SELECT
3516 date1, date2, date3, date4,
3517 date5, date6, date7, date8,
3518 date9, date10, date11, date12,
3519 date13, date14, date15, date16,
3520 date17, date18, date19, date20,
3521 date21, date22, date23, date24,
3522 date25, date26, date27, date28,
3523 date29, date30, date31, date32,
3524 date33, date34, date35, date36,
3525 date37
3526 INTO
3527 var_dates(1), var_dates(2), var_dates(3), var_dates(4),
3528 var_dates(5), var_dates(6), var_dates(7), var_dates(8),
3529 var_dates(9), var_dates(10), var_dates(11), var_dates(12),
3530 var_dates(13), var_dates(14), var_dates(15), var_dates(16),
3531 var_dates(17), var_dates(18), var_dates(19), var_dates(20),
3532 var_dates(21), var_dates(22), var_dates(23), var_dates(24),
3533 var_dates(25), var_dates(26), var_dates(27), var_dates(28),
3534 var_dates(29), var_dates(30), var_dates(31), var_dates(32),
3535 var_dates(33), var_dates(34), var_dates(35), var_dates(36),
3536 var_dates(37)
3537 FROM mrp_workbench_bucket_dates
3538 WHERE NVL(planned_organization, organization_id) = arg_organization_id
3539 AND organization_id = arg_plan_organization_id
3540 AND compile_designator = arg_compile_designator
3541 AND bucket_type = DECODE(arg_current_data, SYS_YES,
3542 DECODE(arg_bucket_type, 1, -4,
3543 2, -5,
3544 3, -6),
3545 decode(arg_bucket_type,1,4,
3546 2,5,
3547 3,6));
3548 last_date := LEAST(var_dates(37), arg_cutoff_date);
3549 END IF;
3550
3551 bucket_counter := 2;
3552 old_bucket_counter := 2;
3553
3554 IF arg_current_data = 1 THEN
3555 OPEN mrp_current_activity_ont_I;
3556 ELSE
3557 OPEN mrp_snapshot_activity;
3558 END IF;
3559
3560 LOOP
3561
3562 IF arg_current_data = 1 THEN
3563 FETCH mrp_current_activity_ont_I
3564 INTO activity_rec;
3565 -- dbms_output.put_line('item_id:' ||activity_rec.item_id );
3566 IF ( mrp_current_activity_ont_I%NOTFOUND OR
3567 (activity_rec.item_id <> last_item_id) OR
3568 ( activity_rec.org_id <> last_org_id)) AND
3569 last_item_id <> -1 THEN
3570
3571 -- --------------------------
3572 -- Need to flush the plan for
3573 -- the previous item.
3574 -- --------------------------
3575 IF prev_ss_quantity <> -1 AND
3576 NOT enterprize_view THEN
3577 FOR k IN bucket_counter..NUM_OF_COLUMNS LOOP
3578 add_to_plan(k - 1,
3579 SS_OFF,
3580 prev_ss_quantity);
3581 END LOOP;
3582 END IF;
3583 flush_item_plan(last_item_id,
3584 last_org_id);
3585
3586 bucket_counter := 2;
3587 old_bucket_counter := 2;
3588 prev_ss_quantity := -1;
3589 ss_quantity :=-1;
3590 -- ------------------------------------
3591 -- Initialize the bucket cells to 0.
3592 -- ------------------------------------
3593 IF enterprize_view THEN
3594 FOR counter IN 1..NUM_OF_COLUMNS LOOP
3595 bucket_cells_tab(counter) := 0;
3596 END LOOP;
3597 ELSE
3598 FOR counter IN 1..(NUM_OF_TYPES * NUM_OF_COLUMNS) LOOP
3599 bucket_cells_tab(counter) := 0;
3600 END LOOP;
3601 END IF;
3602 END IF;
3603
3604 EXIT WHEN mrp_current_activity_ont_I%NOTFOUND;
3605
3606 ELSE
3607 FETCH mrp_snapshot_activity
3608 INTO activity_rec;
3609 -- dbms_output.put_line('item_id:' ||activity_rec.item_id );
3610 IF ((mrp_snapshot_activity%NOTFOUND) OR
3611 (activity_rec.item_id <> last_item_id) OR
3612 ( activity_rec.org_id <> last_org_id)) AND
3613 last_item_id <> -1 THEN
3614
3615 -- --------------------------
3616 -- Need to flush the plan for
3617 -- the previous item.
3618 -- --------------------------
3619 IF prev_ss_quantity <> -1 AND
3620 NOT enterprize_view THEN
3621 FOR k IN bucket_counter..NUM_OF_COLUMNS LOOP
3622 add_to_plan(k - 1,
3623 SS_OFF,
3624 prev_ss_quantity);
3625 END LOOP;
3626 END IF;
3627 flush_item_plan(last_item_id,
3628 last_org_id);
3629
3630 bucket_counter := 2;
3631 old_bucket_counter := 2;
3632 prev_ss_quantity := -1;
3633 ss_quantity :=-1;
3634 -- ------------------------------------
3635 -- Initialize the bucket cells to 0.
3636 -- ------------------------------------
3637 IF enterprize_view THEN
3638 FOR counter IN 1..NUM_OF_COLUMNS LOOP
3639 bucket_cells_tab(counter) := 0;
3640 END LOOP;
3641 ELSE
3642 FOR counter IN 1..(NUM_OF_TYPES * NUM_OF_COLUMNS) LOOP
3643 bucket_cells_tab(counter) := 0;
3644 END LOOP;
3645 END IF;
3646 END IF;
3647
3648 EXIT WHEN mrp_snapshot_activity%NOTFOUND;
3649 END IF;
3650
3651 IF enterprize_view THEN
3652 IF activity_rec.row_type IN (WIP, PO, REQ, TRANSIT,
3653 RECEIVING, PB_SUPPLY) THEN
3654 add_to_plan(CURRENT_S_OFF + 1, 0, activity_rec.old_quantity);
3655 END IF;
3656 add_to_plan(activity_rec.offset + 1 , 0,
3657 activity_rec.new_quantity);
3658 ELSE
3659 IF activity_rec.row_type = SS THEN
3660
3661 -- --------------------------
3662 -- Got a safety stock record.
3663 -- --------------------------
3664 ss_quantity := activity_rec.new_quantity;
3665 ss_date := activity_rec.new_date;
3666
3667 IF activity_rec.new_date < var_dates(bucket_counter) THEN
3668 -- ----------------------------------
3669 -- This safety stock quantity applies
3670 -- to the current bucket.
3671 -- ----------------------------------
3672 prev_ss_quantity := activity_rec.new_quantity;
3673 prev_ss_date := activity_rec.new_date;
3674 END IF;
3675 END IF;
3676
3677 IF activity_rec.new_date >= var_dates(bucket_counter) THEN
3678
3679 -- -------------------------------------------------------
3680 -- We got an activity falls after the current bucket. So we
3681 -- will move the bucket counter forward until we find the
3682 -- bucket where this activity falls. Note that we should
3683 -- not advance the counter bejond NUM_OF_COLUMNS.
3684 -- --------------------------------------------------------
3685 WHILE activity_rec.new_date >= var_dates(bucket_counter) AND
3686 bucket_counter <= NUM_OF_COLUMNS LOOP
3687
3688 -- -----------------------------------------------------
3689 -- If the variable last_ss_quantity is not -1 then there
3690 -- is a safety stock entry that we need to add for the
3691 -- current bucket before we move the bucket counter
3692 -- forward.
3693 -- -----------------------------------------------------
3694 IF prev_ss_quantity <> -1 THEN
3695 add_to_plan(bucket_counter - 1,
3696 SS_OFF,
3697 prev_ss_quantity);
3698 -- prev_ss_quantity := -1;
3699 END IF;
3700
3701 bucket_counter := bucket_counter + 1;
3702
3703 END LOOP;
3704 prev_ss_quantity := ss_quantity;
3705 prev_ss_date := ss_date;
3706 END IF;
3707
3708 -- ---------------------------------------------------------
3709 -- Add the retrieved activity to the plan if it falls in the
3710 -- current bucket and it is not a safety stock entry.
3711 -- ---------------------------------------------------------
3712 IF activity_rec.new_date < var_dates(bucket_counter) AND
3713 activity_rec.row_type <> SS THEN
3714 add_to_plan(bucket_counter - 1,
3715 activity_rec.offset,
3716 activity_rec.new_quantity);
3717 END IF;
3718
3719 -- -------------------------------------
3720 -- Add to the current schedule receipts.
3721 -- -------------------------------------
3722 IF activity_rec.row_type IN (WIP, PO, REQ, TRANSIT,
3723 RECEIVING, PB_SUPPLY) THEN
3724 WHILE activity_rec.old_date >= var_dates(old_bucket_counter) AND
3725 old_bucket_counter <= NUM_OF_COLUMNS LOOP
3726 -- ----------
3727 -- move back.
3728 -- ----------
3729 old_bucket_counter := old_bucket_counter + 1;
3730
3731 END LOOP;
3732
3733 WHILE activity_rec.old_date < var_dates(old_bucket_counter - 1) AND
3734 /* old_bucket_counter < 2 LOOP 2159997 */
3735
3736 old_bucket_counter > 2 LOOP /*Bug 2159997*/
3737 -- -------------
3738 -- move forward.
3739 -- -------------
3740 old_bucket_counter := old_bucket_counter - 1;
3741 END LOOP;
3742 IF activity_rec.old_date < var_dates(old_bucket_counter) THEN
3743 add_to_plan(old_bucket_counter - 1,
3744 CURRENT_S_OFF,
3745 activity_rec.old_quantity);
3746 END IF;
3747 END IF;
3748 END IF;
3749 last_item_id := activity_rec.item_id;
3750 last_org_id := activity_rec.org_id;
3751 END LOOP;
3752
3753 IF arg_current_data = 1 THEN
3754 CLOSE mrp_current_activity_ont_I;
3755 ELSE
3756 CLOSE mrp_snapshot_activity;
3757 END IF;
3758
3759 END;
3760 END;