[Home] [Help]
PACKAGE BODY: APPS.CSP_PLAN_DETAILS_PKG
Source
1 Package Body CSP_PLAN_DETAILS_PKG AS
2 /*$Header: csptpldb.pls 120.59 2008/06/03 00:01:33 hhaugeru noship $*/
3 l_organization_id number := 0;
4 l_inventory_item_id number := 0;
5 l_forecast_rule_id number := 0;
6 l_forecast_periods number := 0;
7 l_forecast_period_size number := 0;
8 l_forecast_method number := 0;
9 l_history_periods number := 0;
10 l_period_size number := 0;
11 l_orig_forecast_periods number := 0;
12 l_orig_period_size number := 0;
13 l_usable_assignment_set_id number := 0;
14 l_defective_assignment_set_id number := 0;
15 l_repair_assignment_set_id number := 0;
16 l_edq_multiple number := 1;
17 l_reschedule_rule_id number := null;
18 l_onhand_type_in number;
19 l_start_day_in number;
20 l_end_day_in number;
21 l_onhand_condition_in number;
22 l_periods_in number;
23 l_onhand_type_out number;
24 l_start_day_out number;
25 l_end_day_out number;
26 l_onhand_value_out number;
27 l_edq_multiple_out number;
28 l_periods_out number;
29 l_minimum_value number := 0;
30 g_retcode number := 0;
31
32 Procedure Add_Err_Msg Is
33 l_msg_index_out NUMBER;
34 x_msg_data_temp Varchar2(2000);
35 x_msg_data Varchar2(4000);
36 Begin
37 If fnd_msg_pub.count_msg > 0 Then
38 FOR i IN REVERSE 1..fnd_msg_pub.count_msg Loop
39 fnd_msg_pub.get(p_msg_index => i,
40 p_encoded => 'F',
41 p_data => x_msg_data_temp,
42 p_msg_index_out => l_msg_index_out);
43 x_msg_data := x_msg_data || x_msg_data_temp;
44 End Loop;
45 FND_FILE.put_line(FND_FILE.log,x_msg_data);
46 fnd_msg_pub.delete_msg;
47 g_retcode := 1;
48 End if;
49 End;
50
51 procedure order_automation is
52 cursor c_business_rule is
53 select cwrv.wrp_rule_id,
54 cwrv.excess_value_limit,
55 cwrv.excess_ts_min,
56 cwrv.excess_ts_max,
57 cwrv.excess_lead_time,
58 cwrv.rep_int_value_limit,
59 cwrv.rep_int_ts_min,
60 cwrv.rep_int_ts_max,
61 cwrv.rep_int_lead_time,
62 cwrv.rep_ext_value_limit,
63 cwrv.rep_ext_ts_min,
64 cwrv.rep_ext_ts_max,
65 cwrv.rep_ext_lead_time,
66 cwrv.nb_int_value_limit,
67 cwrv.nb_int_ts_min,
68 cwrv.nb_int_ts_max,
69 cwrv.nb_int_lead_time,
70 cwrv.nb_ext_value_limit,
71 cwrv.nb_ext_ts_min,
72 cwrv.nb_ext_ts_max,
73 cwrv.nb_ext_lead_time
74 from csp_wrp_rules_vl cwrv,
75 csp_planning_parameters cpp
76 where cpp.organization_id = l_organization_id
77 and cpp.organization_type = 'W'
78 and cwrv.wrp_rule_id = cpp.wrp_rule_id;
79
80 cursor c_planned_orders is
81 select cpd.inventory_item_id,
82 nvl(cpd.related_item_id, cpd.inventory_item_id) supplied_item_id,
83 cpd.plan_detail_type,
84 cpd.source_organization_id,
85 cpd.quantity,
86 cpd.plan_date,
87 nvl(cuh.tracking_signal,0) tracking_signal,
88 nvl(cpl.newbuy_lead_time,0) newbuy_lead_time,
89 nvl(cpl.repair_lead_time,0) repair_lead_time,
90 nvl(cpl.excess_lead_time,0) excess_lead_time,
91 nvl(cic.item_cost,0) item_cost
92 from csp_plan_details cpd,
93 csp_plan_leadtimes cpl,
94 cst_item_costs cic,
95 mtl_parameters mp,
96 csp_usage_headers cuh
97 where cpd.organization_id = l_organization_id
98 and cpl.organization_id = cpd.organization_id
99 and cpl.inventory_item_id = cpd.inventory_item_id
100 and cpd.plan_detail_type in ('4110','4210','4310')
101 and cic.organization_id = cpd.organization_id
102 and cic.inventory_item_id = cpd.inventory_item_id
103 and cic.cost_type_id = mp.primary_cost_method
104 and mp.organization_id = cpd.organization_id
105 and cuh.organization_id(+) = cpd.organization_id
106 and cuh.inventory_item_id(+) = cpd.inventory_item_id
107 and cuh.header_data_type(+) = '4'
108 and nvl(cic.item_cost,0) > 0;
109
110 cursor c_rep_int_ext(p_organization_id number,p_supplied_item_id number) is
111 select decode(misl.source_type,1,'INTERNAL','EXTERNAL')
112 from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp
113 where cpp.organization_id = p_organization_id
114 and misl.organization_id = cpp.organization_id
115 and misl.assignment_set_id =cpp.repair_assignment_set_id
116 and inventory_item_id = p_supplied_item_id
117 and SOURCE_TYPE in (1,3)
118 and sourcing_level = (select min(sourcing_level) from
119 MRP_ITEM_SOURCING_LEVELS_V
120 where organization_id = p_organization_id
121 and assignment_set_id = cpp.repair_assignment_set_id
122 and inventory_item_id = p_supplied_item_id
123 and sourcing_level not in (2,9))
124 order by misl.rank;
125
126 cursor c_nb_int_ext(p_organization_id number, p_supplied_item_id number) is
127 select decode(nvl(msi.source_type,mp.source_type),1,'INTERNAL','EXTERNAL')
128 from mtl_system_items msi,
129 mtl_parameters mp
130 where mp.organization_id = msi.organization_id
131 and msi.organization_id = p_organization_id
132 and msi.inventory_item_id = p_supplied_item_id;
133
134 cursor c_statistics is
135 select decode(cpd.parent_type,'8611','NewBuy Internal Inside ',
136 '8612','NewBuy Internal Outside',
137 '8613','NewBuy External Inside ',
138 '8614','NewBuy External Outside',
139 '8621','Repair Internal Inside ',
140 '8622','Repair Internal Outside',
141 '8623','Repair External Inside ',
142 '8624','Repair External Outside',
143 '8631','Excess Internal Inside ',
144 '8632','Excess Internal Outside',
145 '.......................') ||
146 lpad(to_char(count(*)),15,' ') ||
147 lpad(to_char(round(
148 sum(cpd.quantity * cic.item_cost),2)),15,' ') ||
149 lpad(to_char(sum(cpd.quantity)),15,' ') ||
150 lpad(to_char(round(avg(
151 nvl(cuh.tracking_signal,0)),2)),11,' ') statistics
152 from csp_plan_details cpd,
153 cst_item_costs cic,
154 mtl_parameters mp,
155 csp_usage_headers cuh
156 where cic.organization_id = cpd.organization_id
157 and cic.inventory_item_id = cpd.inventory_item_id
158 and cpd.plan_detail_type in ('8610','8620','8630')
159 and mp.organization_id = cpd.organization_id
160 and cic.cost_type_id = mp.primary_cost_method
161 and cuh.organization_id(+) = cpd.organization_id
162 and cuh.inventory_item_id(+) = cpd.inventory_item_id
163 and cuh.header_data_type(+) = '4'
164 group by cpd.parent_type;
165
166 l_wrp_rule_id number := null;
167 l_excess_value_limit number := null;
168 l_excess_ts_min number := null;
169 l_excess_ts_max number := null;
170 l_excess_lead_time number := null;
171 l_rep_int_value_limit number := null;
172 l_rep_int_ts_min number := null;
173 l_rep_int_ts_max number := null;
174 l_rep_int_lead_time number := null;
175 l_rep_ext_value_limit number := null;
176 l_rep_ext_ts_min number := null;
177 l_rep_ext_ts_max number := null;
178 l_rep_ext_lead_time number := null;
179 l_nb_int_value_limit number := null;
180 l_nb_int_ts_min number := null;
181 l_nb_int_ts_max number := null;
182 l_nb_int_lead_time number := null;
183 l_nb_ext_value_limit number := null;
184 l_nb_ext_ts_min number := null;
185 l_nb_ext_ts_max number := null;
186 l_nb_ext_lead_time number := null;
187 l_order number := null;
188 l_nb_int_ext varchar2(30);
189 l_rep_int_ext varchar2(30);
190 l_line_tbl CSP_PLANNED_ORDERS.Line_Tbl_Type;
191 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
192 l_msg_data VARCHAR2(2000);
193 l_msg_count NUMBER;
194 l_parent_type varchar2(30) := null;
195
196 begin
197 open c_business_rule;
198 fetch c_business_rule into
199 l_wrp_rule_id,
200 l_excess_value_limit,
201 l_excess_ts_min ,
202 l_excess_ts_max ,
203 l_excess_lead_time ,
204 l_rep_int_value_limit ,
205 l_rep_int_ts_min ,
206 l_rep_int_ts_max ,
207 l_rep_int_lead_time ,
208 l_rep_ext_value_limit ,
209 l_rep_ext_ts_min ,
210 l_rep_ext_ts_max ,
211 l_rep_ext_lead_time ,
212 l_nb_int_value_limit ,
213 l_nb_int_ts_min ,
214 l_nb_int_ts_max ,
215 l_nb_int_lead_time ,
216 l_nb_ext_value_limit ,
217 l_nb_ext_ts_min ,
218 l_nb_ext_ts_max ,
219 l_nb_ext_lead_time;
220 close c_business_rule;
221 if l_wrp_rule_id is not null then
222 if l_excess_value_limit is not null or
223 l_excess_ts_min is not null or
224 l_excess_ts_max is not null or
225 l_excess_lead_time is not null then
226 l_excess_value_limit := nvl(l_excess_value_limit,999999999999);
227 l_excess_ts_min := nvl(l_excess_ts_min,-999999999999);
228 l_excess_ts_max := nvl(l_excess_ts_max,999999999999);
229 l_excess_lead_time := nvl(l_excess_lead_time,3);
230 end if;
231 if l_rep_int_value_limit is not null or
232 l_rep_int_ts_min is not null or
233 l_rep_int_ts_max is not null or
234 l_rep_int_lead_time is not null then
235 l_rep_int_value_limit := nvl(l_rep_int_value_limit,999999999999);
236 l_rep_int_ts_min := nvl(l_rep_int_ts_min,-999999999999);
237 l_rep_int_ts_max := nvl(l_rep_int_ts_max,999999999999);
238 l_rep_int_lead_time := nvl(l_rep_int_lead_time,3);
239 end if;
240 if l_rep_ext_value_limit is not null or
241 l_rep_ext_ts_min is not null or
242 l_rep_ext_ts_max is not null or
243 l_rep_ext_lead_time is not null then
244 l_rep_ext_value_limit := nvl(l_rep_ext_value_limit,999999999999);
245 l_rep_ext_ts_min := nvl(l_rep_ext_ts_min,-999999999999);
246 l_rep_ext_ts_max := nvl(l_rep_ext_ts_max,999999999999);
247 l_rep_ext_lead_time := nvl(l_rep_ext_lead_time,3);
248 end if;
249 if l_nb_int_value_limit is not null or
250 l_nb_int_ts_min is not null or
251 l_nb_int_ts_max is not null or
252 l_nb_int_lead_time is not null then
253 l_nb_int_value_limit := nvl(l_nb_int_value_limit,999999999999);
254 l_nb_int_ts_min := nvl(l_nb_int_ts_min,-999999999999);
255 l_nb_int_ts_max := nvl(l_nb_int_ts_max,999999999999);
256 l_nb_int_lead_time := nvl(l_nb_int_lead_time,3);
257 end if;
258 if l_nb_ext_value_limit is not null or
259 l_nb_ext_ts_min is not null or
260 l_nb_ext_ts_max is not null or
261 l_nb_ext_lead_time is not null then
262 l_nb_ext_value_limit := nvl(l_nb_ext_value_limit,999999999999);
263 l_nb_ext_ts_min := nvl(l_nb_ext_ts_min,-999999999999);
264 l_nb_ext_ts_max := nvl(l_nb_ext_ts_max,999999999999);
265 l_nb_ext_lead_time := nvl(l_nb_ext_lead_time,3);
266 end if;
267 for cr in c_planned_orders loop
268 l_order := 0;
269 if cr.plan_detail_type = '4110' then
270
271 if l_excess_lead_time = 1
272 and cr.plan_date < trunc(sysdate) + cr.excess_lead_time
273 and cr.tracking_signal between l_excess_ts_min
274 and l_excess_ts_max
275 and cr.item_cost * cr.quantity < l_excess_value_limit then
276 l_parent_type := '8631';
277 l_order := 1;
278 elsif l_excess_lead_time =2
279 and cr.plan_date >= trunc(sysdate) + cr.excess_lead_time
280 and cr.tracking_signal between l_excess_ts_min
281 and l_excess_ts_max
282 and cr.item_cost * cr.quantity < l_excess_value_limit then
283 l_parent_type := '8632';
284 l_order := 1;
285 elsif l_excess_lead_time =3
286 and cr.tracking_signal between l_excess_ts_min
287 and l_excess_ts_max
288 and cr.item_cost * cr.quantity < l_excess_value_limit then
289 if cr.plan_date <= trunc(sysdate) + cr.excess_lead_time then
290 l_parent_type := '8631';
291 else
292 l_parent_type := '8632';
293 end if;
294 l_order := 1;
295 end if;
296 elsif cr.plan_detail_type = '4210' then
297
298 open c_rep_int_ext(l_organization_id,cr.supplied_item_id);
299 fetch c_rep_int_ext into l_rep_int_ext;
300 close c_rep_int_ext;
301
302 if l_rep_int_ext = 'INTERNAL' then
303 if l_rep_int_lead_time = 1
304 and cr.plan_date < trunc(sysdate) + cr.repair_lead_time
305 and cr.tracking_signal between l_rep_int_ts_min
306 and l_rep_int_ts_max
307 and cr.item_cost * cr.quantity < l_rep_int_value_limit then
308 l_parent_type := '8621';
309 l_order := 1;
310 elsif l_rep_int_lead_time = 2
311 and cr.plan_date >= trunc(sysdate) + cr.repair_lead_time
312 and cr.tracking_signal between l_rep_int_ts_min
313 and l_rep_int_ts_max
314 and cr.item_cost * cr.quantity < l_rep_int_value_limit then
315 l_parent_type := '8622';
316 l_order := 1;
317 elsif l_rep_int_lead_time = 3
318 and cr.tracking_signal between l_rep_int_ts_min
319 and l_rep_int_ts_max
320 and cr.item_cost * cr.quantity < l_rep_int_value_limit then
321 if cr.plan_date <= trunc(sysdate) + cr.repair_lead_time then
322 l_parent_type := '8621';
323 else
324 l_parent_type := '8622';
325 end if;
326 l_order := 1;
327 end if;
328 else
329 if l_rep_ext_lead_time = 1
330 and cr.plan_date < trunc(sysdate) + cr.repair_lead_time
331 and cr.tracking_signal between l_rep_ext_ts_min
332 and l_rep_ext_ts_max
333 and cr.item_cost * cr.quantity < l_rep_ext_value_limit then
334 l_parent_type := '8623';
335 l_order := 1;
336 elsif l_rep_ext_lead_time = 2
337 and cr.plan_date >= trunc(sysdate) + cr.repair_lead_time
338 and cr.tracking_signal between l_rep_ext_ts_min
339 and l_rep_ext_ts_max
340 and cr.item_cost * cr.quantity < l_rep_ext_value_limit then
341 l_parent_type := '8624';
342 l_order := 1;
343 elsif l_rep_ext_lead_time =3
344 and cr.tracking_signal between l_rep_ext_ts_min
345 and l_rep_ext_ts_max
346 and cr.item_cost * cr.quantity < l_rep_ext_value_limit then
347 if cr.plan_date <= trunc(sysdate) + cr.repair_lead_time then
348 l_parent_type := '8623';
349 else
350 l_parent_type := '8624';
351 end if;
352 l_order := 1;
353 end if;
354 end if;
355 elsif cr.plan_detail_type = '4310' then
356 open c_nb_int_ext(l_organization_id,cr.supplied_item_id);
357 fetch c_nb_int_ext into l_nb_int_ext;
358 close c_nb_int_ext;
359
360 if l_nb_int_ext = 'INTERNAL' then
361 if l_nb_int_lead_time = 1
362 and cr.plan_date < trunc(sysdate) + cr.newbuy_lead_time
363 and cr.tracking_signal between l_nb_int_ts_min
364 and l_nb_int_ts_max
365 and cr.item_cost * cr.quantity < l_nb_int_value_limit then
366 l_parent_type := '8611';
367 l_order := 1;
368 elsif l_nb_int_lead_time =2
369 and cr.plan_date >= trunc(sysdate) + cr.newbuy_lead_time
370 and cr.tracking_signal between l_nb_int_ts_min
371 and l_nb_int_ts_max
372 and cr.item_cost * cr.quantity < l_nb_int_value_limit then
373 l_parent_type := '8612';
374 l_order := 1;
375 elsif l_nb_int_lead_time = 3
376 and cr.tracking_signal between l_nb_int_ts_min
377 and l_nb_int_ts_max
378 and cr.item_cost * cr.quantity < l_nb_int_value_limit then
379 if cr.plan_date <= trunc(sysdate) + cr.newbuy_lead_time then
380 l_parent_type := '8611';
381 else
382 l_parent_type := '8612';
383 end if;
384 l_order := 1;
385 end if;
386 else
387
388 if l_nb_ext_lead_time = 1
389 and cr.plan_date < trunc(sysdate) + cr.newbuy_lead_time
390 and cr.tracking_signal between l_nb_ext_ts_min
391 and l_nb_ext_ts_max
392 and cr.item_cost * cr.quantity < l_nb_ext_value_limit then
393 l_parent_type := '8613';
394 l_order := 1;
395 elsif l_nb_ext_lead_time = 2
396 and cr.plan_date >= trunc(sysdate) + cr.newbuy_lead_time
397 and cr.tracking_signal between l_nb_ext_ts_min
398 and l_nb_ext_ts_max
399 and cr.item_cost * cr.quantity < l_nb_ext_value_limit then
400 l_parent_type := '8614';
401 l_order := 1;
402 elsif l_nb_ext_lead_time = 3
403 and cr.tracking_signal between l_nb_ext_ts_min
404 and l_nb_ext_ts_max
405 and cr.item_cost * cr.quantity < l_nb_ext_value_limit then
406 if cr.plan_date <= trunc(sysdate) + cr.newbuy_lead_time then
407 l_parent_type := '8613';
408 else
409 l_parent_type := '8614';
410 end if;
411 l_order := 1;
412 end if;
413 end if;
414 end if;
415
416 if l_order = 1 then
417 l_line_tbl(1).supplied_item_id := cr.supplied_item_id;
418 l_line_tbl(1).planned_order_type := cr.plan_detail_type;
419 l_line_tbl(1).source_organization_id := cr.source_organization_id;
420 l_line_tbl(1).quantity := cr.quantity;
421 l_line_tbl(1).plan_Date := cr.plan_date;
422
423 CSP_PLANNED_ORDERS.create_orders(
424 p_api_version => 1.0
425 , p_Init_Msg_List => FND_API.G_FALSE
426 , p_commit => FND_API.G_FALSE
427 , p_organization_id => l_organization_id
428 , p_inventory_item_id => cr.supplied_item_id
429 , px_line_tbl => l_line_tbl
430 , x_return_status => l_Return_status
431 , x_msg_count => l_msg_count
432 , x_msg_data => l_msg_data);
433
434
435 if l_return_status = FND_API.G_RET_STS_SUCCESS then
436 update csp_plan_details
437 set plan_detail_type = decode(cr.plan_detail_type,'4110','8630',
438 '4210','8620',
439 '4310','8610'),
440 parent_type = l_parent_type
441 where inventory_item_id = cr.inventory_item_id
442 and organization_id = l_organization_id
443 and plan_detail_type = cr.plan_detail_type
444 and quantity = cr.quantity
445 and plan_date = cr.plan_date;
446 else
447 add_err_msg;
448 end if;
449 end if;
450 end loop;
451 FND_FILE.put_line(FND_FILE.output,'Supply Internal Lead Planned Tracking');
452 FND_FILE.put_line(FND_FILE.output,'Type External Time Orders Value Parts Signal Avg');
453 FND_FILE.put_line(FND_FILE.output,'------ -------- ------- -------------- -------------- -------------- ----------');
454 for cr in c_statistics loop
455 FND_FILE.put_line(FND_FILE.output,cr.statistics);
456 end loop;
457 FND_FILE.put_line(FND_FILE.output,'------ -------- ------- -------------- -------------- -------------- ----------');
458 end if;
459 end;
460
461 procedure leadtimes is
462 begin
463
464 delete from csp_plan_leadtimes
465 where organization_id = l_organization_id
466 and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id);
467
468 insert into csp_plan_leadtimes(
469 inventory_item_id,
470 organization_id,
471 excess_lead_time,
472 repair_lead_time,
473 newbuy_lead_time,
474 created_by,
475 creation_date,
476 last_updated_by,
477 last_update_date,
478 last_update_login)
479 select
480 cpd.inventory_item_id,
481 cpd.organization_id,
482 (select max(nvl(mism1.intransit_time, 0))
483 from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp, mtl_interorg_ship_methods mism1
484 where mism1.to_organization_id = cpp.organization_id
485 and mism1.from_organization_id = misl.source_organization_id
486 and mism1.default_flag = 1
487 and cpp.organization_id = cpd.organization_id
488 and misl.organization_id = cpp.organization_id
489 and misl.assignment_set_id =cpp.usable_assignment_set_id
490 and misl.inventory_item_id = cpd.inventory_item_id
491 and misl.SOURCE_TYPE = 1
492 and sourcing_level = (select min(sourcing_level)
493 from MRP_ITEM_SOURCING_LEVELS_V
494 where organization_id = cpd.organization_id
495 and assignment_set_id = cpp.usable_assignment_set_id
496 and inventory_item_id = cpd.inventory_item_id
497 and sourcing_level not in (2,9))) Excess_Lead_Time,
498 (select max(nvl(mism.intransit_time, 0) +
499 nvl(msib.repair_leadtime, 0) +
500 (select nvl(max(nvl(mism2.intransit_time, 0)), 0)
501 from MRP_ITEM_SOURCING_LEVELS_V misl1,
502 csp_planning_parameters cpp,
503 mtl_interorg_ship_methods mism2
504 where mism2.to_organization_id = decode(misl.source_type, 1, misl.source_organization_id, 3, hoi.organization_id)
505 and mism2.from_organization_id = misl1.source_organization_id
506 and mism2.default_flag = 1
507 and cpp.organization_id = cpd.organization_id
508 and misl1.organization_id = cpp.organization_id
509 and misl1.assignment_set_id =cpp.defective_assignment_set_id
510 and misl1.inventory_item_id = cpd.inventory_item_id
511 and SOURCE_TYPE = 1
512 and sourcing_level = (select min(sourcing_level)
513 from MRP_ITEM_SOURCING_LEVELS_V
514 where organization_id = cpd.organization_id
515 and assignment_set_id = cpp.defective_assignment_set_id
516 and inventory_item_id = cpd.inventory_item_id
517 and sourcing_level not in (2,9))
518 ))
519 from MRP_ITEM_SOURCING_LEVELS_V misl,
520 csp_planning_parameters cpp,
521 mtl_interorg_ship_methods mism,
522 mtl_system_items_b msib,
523 hr_organization_information hoi
524 where msib.inventory_item_id = cpd.inventory_item_id
525 and msib.organization_id = decode(misl.source_type, 1, misl.source_organization_id, 3, hoi.organization_id)
526 and mism.to_organization_id = misl.organization_id
527 and mism.from_organization_id = decode(misl.source_type, 1, misl.source_organization_id, 3, hoi.organization_id)
528 and mism.default_flag = 1
529 and cpp.organization_id = cpd.organization_id
530 and misl.organization_id = cpp.organization_id
531 and misl.assignment_set_id = cpp.repair_assignment_set_id
532 and misl.inventory_item_id = cpd.inventory_item_id
533 and misl.SOURCE_TYPE in ( 1, 3)
534 and sourcing_level = (select min(sourcing_level)
535 from MRP_ITEM_SOURCING_LEVELS_V
536 where organization_id = cpd.organization_id
537 and assignment_set_id = cpp.repair_assignment_set_id
538 and inventory_item_id = cpd.inventory_item_id
539 and sourcing_level not in (2,9))
540 and hoi.ORG_INFORMATION_CONTEXT(+) = 'Customer/Supplier Association'
541 and hoi.org_information3(+) = misl.vendor_id
542 ) Repair_Lead_time,
543 (select decode(nvl(msib.preprocessing_lead_time, 0) +
544 nvl(msib.full_lead_time, 0) +
545 nvl(msib.postprocessing_lead_time,0),0,null,
546 nvl(msib.preprocessing_lead_time, 0) +
547 nvl(msib.full_lead_time, 0) +
548 nvl(msib.postprocessing_lead_time,0))
549 from mtl_system_items_b msib
550 where inventory_item_id = cpd.inventory_item_id
551 and organization_id = cpd.organization_id) NewBuy_Lead_Time,
552 fnd_global.user_id,
553 sysdate,
554 fnd_global.user_id,
555 sysdate,
556 fnd_global.login_id
557 from csp_plan_Details cpd
558 where plan_detail_type = '1'
559 and organization_id = l_organization_id
560 and inventory_item_id = nvl(l_inventory_item_id, inventory_item_id)
561 group by organization_id, inventory_item_id;
562 EXCEPTION
563 WHEN no_data_found THEN
564 null ;
565 end leadtimes;
566
567 procedure reorders(p_organization_id number,p_inventory_item_id number) is
568 begin
569
570 delete from csp_plan_reorders
571 where organization_id = p_organization_id
572 and inventory_item_id = nvl(p_inventory_item_id,inventory_item_id);
573
574 insert into csp_plan_reorders(
575 inventory_item_id,
576 organization_id,
577 excess_rop,
578 repair_rop,
579 newbuy_rop,
580 excess_edq,
581 repair_edq,
582 newbuy_edq,
583 excess_safety_stock,
584 repair_safety_stock,
585 newbuy_safety_stock,
586 created_by,
587 creation_date,
588 last_updated_by,
589 last_update_date,
590 last_update_login)
591 (select b.inventory_item_id,
592 b.organization_id,
593 (nvl(csf.safety_factor,0) * b.standard_deviation + b.excess_total_req) excess_rop,
594 (nvl(csf1.safety_factor, 0) * b.standard_deviation + b.repair_total_req) repair_rop,
595 (nvl(csf2.safety_factor, 0) * b.standard_deviation + b.newbuy_total_req) newbuy_rop,
596 b.excess_edq,
597 b.repair_edq,
598 b.newbuy_edq,
599 (nvl(csf.safety_factor,0) * nvl(b.standard_deviation, 0)) excess_safety_stock,
600 (nvl(csf1.safety_factor,0) * nvl(b.standard_deviation, 0)) repair_safety_stock,
601 (nvl(csf2.safety_factor,0) * nvl(b.standard_deviation, 0)) newbuy_safety_stock,
602 fnd_global.user_id,
603 sysdate,
604 fnd_global.user_id,
605 sysdate,
606 fnd_global.login_id
607 from csp_safety_factors csf,
608 csp_Safety_factors csf1,
609 csp_safety_factors csf2,
610 (select decode(nvl(cpp1.safety_stock_flag, 'N'), 'N', 0, decode(a.excess_awr, 0, 0 , decode(nvl(cuh.item_cost, 0), 0, 0, decode(nvl(cipp.excess_edq_factor, cpp1.excess_edq_factor), 0, 0,
611 LEAST(52, GREATEST(3, ROUND(a.excess_awr * 52/(ROUND(nvl(cipp.excess_edq_factor, cpp1.excess_edq_factor) * (SQRT(52 * a.excess_awr * cuh.item_Cost)/cuh.item_Cost),4))))))))) excess_exposures
612 , a.excess_total_req
613 , decode(cic.item_cost, 0, 0, decode(a.excess_awr, 0, 0, ROUND(nvl(cipp.excess_edq_factor, cpp1.excess_edq_factor) * (SQRT(52 * a.excess_awr * cic.item_Cost)/cic.item_Cost),4))) Excess_EDQ
614 , decode(nvl(cpp1.safety_stock_flag, 'N'), 'N', 0, decode(a.repair_awr, 0, 0, decode(nvl(cic.item_cost, 0), 0, 0, decode(nvl(cipp.repair_edq_factor, cpp1.repair_edq_factor), 0, 0,
615 LEAST(52, GREATEST(3, ROUND(a.repair_awr * 52/(ROUND(nvl(cipp.repair_Edq_factor, cpp1.repair_edq_factor) * (SQRT(52 * a.repair_awr * cic.item_cost)/cic.item_cost),4))))))))) repair_exposures
616 , a.repair_total_req
617 , decode(cic.item_cost, 0, 0, decode(a.repair_awr, 0, 0, ROUND(nvl(cipp.repair_edq_Factor, cpp1.repair_edq_factor) * (SQRT(52 * a.repair_awr * cic.item_cost)/cic.item_cost),4))) Repair_EDQ
618 , decode(nvl(cpp1.safety_stock_flag, 'N'), 'N', 0, decode(a.newbuy_awr, 0, 0, decode(nvl(cic.item_cost, 0), 0, 0, decode(nvl(cipp.newbuy_edq_factor, cpp1.newbuy_edq_factor), 0, 0,
619 LEAST(52, GREATEST(3, ROUND(a.newbuy_awr * 52/(ROUND(nvl(cipp.newbuy_edq_factor, cpp1.newbuy_edq_factor) * (SQRT(52 * a.newbuy_awr * cic.item_cost)/cic.item_cost),4))))))))) newbuy_exposures
620 , a.newbuy_total_req
621 , decode(cic.item_cost, 0, 0, decode(a.newbuy_awr, 0, 0, ROUND(nvl(cipp.newbuy_edq_factor, cpp1.newbuy_edq_factor) * (SQRT(52 * a.newbuy_awr * cic.item_cost)/cic.item_cost),4))) NewBuy_EDQ
622 , a.inventory_item_id
623 , a.organization_id
624 , nvl(nvl(cipp.excess_service_level, cpp1.excess_service_level), cpp1.service_level) excess_service_level
625 ,nvl(nvl(cipp.repair_service_level, cpp1.repair_service_level), cpp1.service_level) repair_service_level
626 ,nvl(nvl(cipp.newbuy_service_level, cpp1.newbuy_service_level), cpp1.service_level) newbuy_service_level
627 ,nvl(cuh.standard_deviation, 0) standard_deviation
628 from csp_usage_headers cuh,
629 cst_item_costs cic,
630 mtl_parameters mp,
631 csp_planning_parameters cpp1,
632 csp_item_pl_params cipp,
633 (select decode(nvl(cpl.Excess_Lead_Time, 0), 0, 0, round(sum(decode((cfrb.period_size *
634 floor(cpl.Excess_Lead_Time/cfrb.period_size)), 0, 0, decode(floor((cpd.plan_date - trunc(sysdate))/(cfrb.period_size *
635 floor(cpl.Excess_Lead_Time/cfrb.period_size))), 0, cpd.quantity, 0))) +
636 sum(decode(sign(cpl.Excess_lead_time - cfrb.period_size), -1,
637 decode(sign((cpd.plan_date - trunc(sysdate)) - cfrb.period_size), -1, cpd.quantity, 0), decode(floor((cpd.plan_date - trunc(sysdate))
638 /(cfrb.period_size *
639 floor(cpl.Excess_Lead_Time/cfrb.period_size))), 1, decode(sign(plan_date - trunc(sysdate)- cpl.excess_lead_time), -1, cpd.quantity, 0), 0)))* ((cpl.Excess_lead_time - (cfrb.period_size *
640 floor(cpl.Excess_Lead_Time/cfrb.period_size)))/cfrb.period_size), 4)) Excess_Total_Req,
641 (decode(nvl(cpl.Excess_Lead_Time, 0), 0, 0, round(sum(decode((cfrb.period_size *
642 floor(cpl.Excess_Lead_Time/cfrb.period_size)), 0, 0, decode(floor((cpd.plan_date - trunc(sysdate))/(cfrb.period_size *
643 floor(cpl.Excess_Lead_Time/cfrb.period_size))), 0, cpd.quantity, 0))) +
644 sum(decode(sign(cpl.Excess_lead_time - cfrb.period_size), -1,
645 decode(sign((cpd.plan_date - trunc(sysdate)) - cfrb.period_size), -1, cpd.quantity, 0), decode(floor((cpd.plan_date - trunc(sysdate))
646 /(cfrb.period_size *
647 floor(cpl.Excess_Lead_Time/cfrb.period_size))), 1, decode(sign(plan_date - trunc(sysdate)- cpl.excess_lead_time), -1, cpd.quantity, 0), 0)))* ((cpl.Excess_lead_time - (cfrb.period_size *
648 floor(cpl.Excess_Lead_Time/cfrb.period_size)))/cfrb.period_size) , 4))/ cpl.Excess_Lead_Time) * 7 excess_awr,
649 decode(nvl(cpl.Repair_Lead_Time, 0), 0, 0, round(sum(decode((cfrb.period_size *
650 floor(cpl.Repair_Lead_Time/cfrb.period_size)), 0, 0, decode(floor((cpd.plan_date - trunc(sysdate))/(cfrb.period_size *
651 floor(cpl.Repair_Lead_Time/cfrb.period_size))), 0, cpd.quantity, 0))) +
652 sum(decode(sign(cpl.repair_lead_time - cfrb.period_size), -1,
653 decode(sign((cpd.plan_date - trunc(sysdate)) - cfrb.period_size), -1, cpd.quantity, 0), decode(floor((cpd.plan_date - trunc(sysdate))
654 /(cfrb.period_size * floor(cpl.Repair_Lead_Time/cfrb.period_size))), 1, decode(sign(plan_date -
655 trunc(sysdate)- cpl.repair_lead_time), -1, cpd.quantity, 0), 0)))*
656 ((cpl.Repair_lead_time - (cfrb.period_size * floor(cpl.Repair_Lead_Time/cfrb.period_size)))/cfrb.period_size), 4)) Repair_Total_Req,
657 (decode(nvl(cpl.Repair_Lead_Time, 0), 0, 0, round(sum(decode((cfrb.period_size *
658 floor(cpl.Repair_Lead_Time/cfrb.period_size)), 0, 0, decode(floor((cpd.plan_date - trunc(sysdate))/(cfrb.period_size *
659 floor(cpl.Repair_Lead_Time/cfrb.period_size))), 0, cpd.quantity, 0))) +
660 sum(decode(sign(cpl.repair_lead_time - cfrb.period_size), -1,
661 decode(sign((cpd.plan_date - trunc(sysdate)) - cfrb.period_size), -1, cpd.quantity, 0), decode(floor((cpd.plan_date - trunc(sysdate))
662 /(cfrb.period_size *
663 floor(cpl.Repair_Lead_Time/cfrb.period_size))), 1, decode(sign(plan_date - trunc(sysdate)- cpl.repair_lead_time), -1, cpd.quantity, 0), 0)))*
664 ((cpl.Repair_lead_time - (cfrb.period_size *
665 floor(cpl.Repair_Lead_Time/cfrb.period_size)))/cfrb.period_size), 4))/cpl.repair_lead_time) * 7 Repair_AWR,
666 decode(nvl(cpl.NewBuy_Lead_Time, 0), 0, 0, round(sum(decode((cfrb.period_size *
667 floor(cpl.NewBuy_Lead_Time/cfrb.period_size)), 0, 0, decode(floor((cpd.plan_date - trunc(sysdate))/(cfrb.period_size *
668 floor(cpl.NewBuy_Lead_Time/cfrb.period_size))), 0, cpd.quantity, 0))) +
669 sum(decode(sign(cpl.NewBuy_lead_time - cfrb.period_size), -1,
670 decode(sign((cpd.plan_date - trunc(sysdate)) - cfrb.period_size), -1, cpd.quantity, 0), decode(floor((cpd.plan_date - trunc(sysdate))
671 /(cfrb.period_size * floor(cpl.NewBuy_Lead_Time/cfrb.period_size))), 1, decode(sign(plan_date
672 - trunc(sysdate)- cpl.newbuy_lead_time), -1, cpd.quantity, 0), 0)))*
673 ((cpl.NewBuy_lead_time - (cfrb.period_size * floor(cpl.NewBuy_Lead_Time/cfrb.period_size)))/cfrb.period_size), 4)) NewBuy_Total_Req,
674 (decode(nvl(cpl.NewBuy_Lead_Time, 0), 0, 0, round(sum(decode((cfrb.period_size *
675 floor(cpl.NewBuy_Lead_Time/cfrb.period_size)), 0, 0, decode(floor((cpd.plan_date - trunc(sysdate))/(cfrb.period_size *
676 floor(cpl.NewBuy_Lead_Time/cfrb.period_size))), 0, cpd.quantity, 0))) +
677 sum(decode(sign(cpl.NewBuy_lead_time - cfrb.period_size), -1,
678 decode(sign((cpd.plan_date - trunc(sysdate)) - cfrb.period_size), -1, cpd.quantity, 0), decode(floor((cpd.plan_date - trunc(sysdate))
679 /(cfrb.period_size *
680 floor(cpl.NewBuy_Lead_Time/cfrb.period_size))), 1, decode(sign(plan_date - trunc(sysdate)- cpl.newbuy_lead_time), -1, cpd.quantity, 0), 0)))* ((cpl.NewBuy_lead_time - (cfrb.period_size *
681 floor(cpl.NewBuy_Lead_Time/cfrb.period_size)))/cfrb.period_size),4))/ cpl.newbuy_lead_time) * 7 NewBuy_AWR,
682 cpd.inventory_item_id,
683 cpd.organization_id
684 from csp_plan_details cpd,
685 csp_plan_leadtimes cpl,
686 csp_planning_parameters cpp,
687 csp_forecast_rules_b cfrb
688 where cpd.organization_id = p_organization_id
689 and cpd.inventory_item_id = nvl(p_inventory_item_id, cpd.inventory_item_id)
690 and cpd.plan_detail_type = 1000
691 and cpd.plan_Date between trunc(sysdate)
692 and trunc(sysdate) + greatest(nvl(cpl.excess_lead_time, 0), nvl(cpl.repair_lead_time, 0), cpl.newbuy_lead_time)
693 and cpl.inventory_item_id(+) = cpd.inventory_item_id
694 and cpl.organization_id(+) = cpd.organization_id
695 and cpp.organization_id = cpd.organization_id
696 and cfrb.forecast_rule_id = cpp.forecast_rule_id
697 group by cpd.organization_id, cpd.inventory_item_id, cfrb.period_size,
698 cpl.excess_lead_time, cpl.repair_lead_time, cpl.newbuy_lead_time) a
699 where cuh.organization_id(+) = a.organization_id
700 and cuh.inventory_item_id(+) = a.inventory_item_id
701 and cuh.secondary_inventory(+) = '-'
702 and cuh.header_data_type(+) = 4
703 and cpp1.organization_type = 'W'
704 and cpp1.organization_id = a.organization_id
705 and cipp.organization_id(+) = a.organization_id
706 and cipp.inventory_item_id(+) = a.inventory_item_id
707 and cic.inventory_item_id = a.inventory_item_id(+)
708 AND cic.organization_id = mp.organization_id
709 AND cic.cost_type_id = mp.primary_cost_method
710 AND mp.organization_id = a.organization_id) b
711 where csf.exposures(+) = b.excess_exposures
712 and csf.service_level(+) = b.excess_service_level
713 and csf1.exposures(+) = b.repair_exposures
714 and csf1.service_level(+) = b.repair_Service_level
715 and csf2.exposures(+) = b.newbuy_exposures
716 and csf2.service_level(+) = b.newbuy_Service_level );
717 end reorders;
718
719 procedure return_history is
720
721 begin
722 insert into csp_plan_details(
723 plan_detail_type,
724 parent_type,
725 inventory_item_id,
726 related_item_id,
727 organization_id,
728 source_organization_id,
729 quantity,
730 plan_date,
731 created_by,
732 creation_date,
733 last_updated_by,
734 last_update_date,
735 last_update_login)
736 select decode(cpd.related_item_id,null,min('6100'),min('6200')),
737 min('6000'),
738 cpd.inventory_item_id,
739 cpd.related_item_id,
740 cpd.organization_id,
741 cpd.source_organization_id,
742 sum(mmt.primary_quantity),
743 trunc(trunc(sysdate) - round((trunc(sysdate) - trunc(mmt.transaction_date))/cfrb.period_size)*cfrb.period_size),
744 fnd_global.user_id,
745 sysdate,
746 fnd_global.user_id,
747 sysdate,
748 fnd_global.login_id
749 from csp_plan_details cpd,
750 csp_planning_parameters cpp,
751 csp_forecast_rules_b cfrb,
752 mtl_material_transactions mmt,
753 csp_usg_transaction_types cutt
754 where cpd.plan_detail_type in ('9002','9003')
755 and cpd.organization_id = l_organization_id
756 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
757 and cpp.organization_id = cpd.source_organization_id
758 and cfrb.forecast_rule_id = cpp.forecast_rule_id
759 and mmt.organization_id = cpd.source_organization_id
760 and mmt.inventory_item_id = nvl(cpd.related_item_id,cpd.inventory_item_id)
761 and cutt.forecast_rule_id = cpp.forecast_rule_id
762 and cutt.transaction_type_id = mmt.transaction_type_id
763 and mmt.transaction_date between trunc(sysdate) - (cfrb.history_periods*cfrb.period_size) and trunc(sysdate)
764 group by cpd.inventory_item_id,
765 cpd.related_item_id,
766 cpd.organization_id,
767 cpd.source_organization_id,
768 trunc(trunc(sysdate) - round((trunc(sysdate) - trunc(mmt.transaction_date))/cfrb.period_size)*cfrb.period_size);
769
770 insert into csp_plan_details(
771 plan_detail_type,
772 parent_type,
773 source_number,
774 source_organization_id,
775 quantity,
776 plan_date,
777 inventory_item_id,
778 organization_id,
779 created_by,
780 creation_date,
781 last_updated_by,
782 last_update_date,
783 last_update_login,
784 forecast_periods,
785 period_size)
786 select min('6000'),
787 null,
788 null,
789 cpd.source_organization_id,
790 sum(quantity),
791 cpd.plan_date,
792 cpd.inventory_item_id,
793 cpd.organization_id,
794 fnd_global.user_id,
795 sysdate,
796 fnd_global.user_id,
797 sysdate,
798 fnd_global.login_id,
799 max(a.history_periods),
800 max(a.period_size)
801 from csp_plan_details cpd,
802 (select round(max(cfrb.history_periods*cfrb.period_size)/max(cfrb.period_size)+0.499999) history_periods,max(cfrb.period_size) period_size,cpd.organization_id,cpd.inventory_item_id
803 from csp_forecast_rules_b cfrb,
804 csp_planning_parameters cpp,
805 csp_plan_details cpd
806 where cfrb.forecast_rule_id = cpp.forecast_rule_id
807 and cpd.plan_detail_type in ('6100','6200')
808 and cpd.source_organization_id = cpp.organization_id
809 group by cpd.organization_id, cpd.inventory_item_id) a
810 where cpd.plan_detail_type in ('6100','6200')
811 and cpd.organization_id = l_organization_id
812 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
813 and a.inventory_item_id = cpd.inventory_item_id
814 and a.organization_id = cpd.organization_id
815 group by cpd.plan_date,cpd.inventory_item_id,cpd.organization_id,cpd.source_organization_id;
816
817 -- Delete 6100 if no 6200 exists, to allow for better display of defective returns
818 delete from csp_plan_details cpd
819 where cpd.plan_detail_type = '6100'
820 and cpd.organization_id = l_organization_id
821 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
822 and not exists
823 (select 'x'
824 from csp_plan_details
825 where plan_detail_type = '6200'
826 and inventory_item_id = cpd.inventory_item_id
827 and organization_id = cpd.organization_id);
828 end return_history;
829
830 PROCEDURE return_forecast IS
831
832 l_forecast number := 0;
833 l_period number := 1;
834 l_previous_base number := 0;
835 l_trend number := 0;
836 l_item number := null;
837 l_start number := 0;
838 l_repair_lead_time number := 0;
839
840 cursor c_items is
841 select cpd.organization_id,
842 cpd.inventory_item_id,
843 l_history_periods - round((trunc(sysdate) - trunc(plan_date))/l_forecast_period_size) period,
844 quantity,
845 alpha,
846 beta,
847 nvl(cpl.repair_lead_time,0) repair_lead_time
848 from csp_plan_details cpd,
849 csp_plan_leadtimes cpl,
850 csp_forecast_rules_b cfrb
851 where cpd.organization_id = l_organization_id
852 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
853 and cpd.plan_detail_type = '6000'
854 and cfrb.forecast_rule_id = l_forecast_rule_id
855 and cpl.organization_id = cpd.organization_id
856 and cpl.inventory_item_id = cpd.inventory_item_id
857 order by cpd.organization_id,
858 cpd.inventory_item_id,
859 plan_date;
860
861 begin
862
863 insert into csp_plan_details(
864 plan_detail_type,
865 parent_type,
866 source_number,
867 source_organization_id,
868 quantity,
869 plan_date,
870 inventory_item_id,
871 organization_id,
872 created_by,
873 creation_date,
874 last_updated_by,
875 last_update_date,
876 last_update_login)
877 select '7000',
878 null,
879 null,
880 null,
881 a.quantity,
882 trunc(sysdate + cpl.repair_lead_time + (rownum-1) * l_period_size),
883 cpd.inventory_item_id,
884 cpd.organization_id,
885 fnd_global.user_id,
886 sysdate,
887 fnd_global.user_id,
888 sysdate,
889 fnd_global.login_id
890 from csp_plan_details cpd,
891 csp_plan_leadtimes cpl,
892 (select round(sum(cpd2.quantity* l_period_size /cfrb.period_size/cfrb.history_periods)) quantity,
893 cpd2.organization_id organization_id,
894 cpd2.inventory_item_id inventory_item_id
895 from csp_plan_details cpd2,
896 csp_forecast_rules_b cfrb,
897 csp_planning_parameters cpp
898 where cpd2.plan_detail_type = '6000'
899 and cpp.organization_id = cpd2.source_organization_id
900 and cfrb.forecast_rule_id = cpp.forecast_rule_id
901 and cfrb.forecast_method in (1,3,4)
902 group by cpd2.organization_id,
903 cpd2.inventory_item_id) a
904 where cpd.organization_id = l_organization_id
905 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
906 and a.organization_id = cpd.organization_id
907 and a.inventory_item_id = cpd.inventory_item_id
908 and cpl.organization_id = cpd.organization_id
909 and cpl.inventory_item_id = cpd.inventory_item_id
910 and rownum <= l_forecast_periods;
911
912 insert into csp_plan_details(
913 plan_detail_type,
914 parent_type,
915 source_number,
916 source_organization_id,
917 quantity,
918 plan_date,
919 inventory_item_id,
920 organization_id,
921 created_by,
922 creation_date,
923 last_updated_by,
924 last_update_date,
925 last_update_login)
926 select '7000',
927 null,
928 null,
929 null,
930 a.quantity,
931 trunc(sysdate + cpl.repair_lead_time + (rownum-1) * l_period_size),
932 cpd.inventory_item_id,
933 cpd.organization_id,
934 fnd_global.user_id,
935 sysdate,
936 fnd_global.user_id,
937 sysdate,
938 fnd_global.login_id
939 from csp_plan_details cpd,
940 csp_plan_leadtimes cpl,
941 (select round(sum(quantity*decode(round((trunc(sysdate)-trunc(plan_date))/l_forecast_period_size),
942 1,weighted_avg_period1,
943 2,weighted_avg_period2,
944 3,weighted_avg_period3,
945 4,weighted_avg_period4,
946 5,weighted_avg_period5,
947 6,weighted_avg_period6,
948 7,weighted_avg_period7,
949 8,weighted_avg_period8,
950 9,weighted_avg_period9,
951 10,weighted_avg_period10,
952 11,weighted_avg_period11,
953 12,weighted_avg_period12))*l_period_size/l_forecast_period_size) quantity,
954 cpd2.organization_id organization_id,
955 cpd2.inventory_item_id inventory_item_id
956 from csp_plan_details cpd2,
957 csp_forecast_rules_b cfrb,
958 csp_planning_parameters cpp
959 where cpd2.plan_detail_type = '6000'
960 and cpp.organization_id = cpd2.source_organization_id
961 and cfrb.forecast_rule_id = cpp.forecast_rule_id
962 and cfrb.forecast_method = 2
963 group by cpd2.organization_id,
964 cpd2.inventory_item_id) a
965 where cpd.organization_id = l_organization_id
966 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
967 and a.organization_id = cpd.organization_id
968 and a.inventory_item_id = cpd.inventory_item_id
969 and cpl.organization_id = cpd.organization_id
970 and cpl.inventory_item_id = cpd.inventory_item_id
971 and rownum <= l_forecast_periods;
972
973 if l_forecast_method = -3 then
974 for cr in c_items loop
975 if nvl(l_item,cr.inventory_item_id) <> cr.inventory_item_id then
976 insert into csp_plan_details(
977 plan_detail_type,
978 parent_type,
979 source_number,
980 source_organization_id,
981 quantity,
982 plan_date,
983 inventory_item_id,
984 organization_id,
985 created_by,
986 creation_date,
987 last_updated_by,
988 last_update_date,
989 last_update_login)
990 select '7000',
991 null,
992 null,
993 null,
994 l_forecast * l_period_size/l_forecast_period_size,
995 trunc(sysdate + cr.repair_lead_time + (rownum-1) * l_period_size),
996 l_item,
997 l_organization_id,
998 fnd_global.user_id,
999 sysdate,
1000 fnd_global.user_id,
1001 sysdate,
1002 fnd_global.login_id
1003 from csp_plan_details
1004 where organization_id = l_organization_id
1005 and rownum <= l_forecast_periods;
1006 l_start := 1;
1007 l_forecast := 0;
1008 end if;
1009 l_item := cr.inventory_item_id;
1010 l_repair_lead_time := cr.repair_lead_time;
1011 for l_period in l_start..cr.period loop
1012 if cr.period = 1 then
1013 l_forecast := cr.quantity;
1014 elsif l_period < cr.period then
1015 l_forecast := nvl(l_forecast,0) * (1 - cr.alpha);
1016 elsif l_period = cr.period then
1017 l_forecast := cr.quantity * cr.alpha + nvl(l_forecast,0) * (1 - cr.alpha);
1018 end if;
1019 l_start := l_period + 1;
1020 end loop;
1021 end loop;
1022 insert into csp_plan_details(
1023 plan_detail_type,
1024 parent_type,
1025 source_number,
1026 source_organization_id,
1027 quantity,
1028 plan_date,
1029 inventory_item_id,
1030 organization_id,
1031 created_by,
1032 creation_date,
1033 last_updated_by,
1034 last_update_date,
1035 last_update_login)
1036 select '7000',
1037 null,
1038 null,
1039 null,
1040 l_forecast * l_period_size/l_forecast_period_size,
1041 trunc(sysdate + l_repair_lead_time + (rownum -1) * l_period_size),
1042 l_item,
1043 l_organization_id,
1044 fnd_global.user_id,
1045 sysdate,
1046 fnd_global.user_id,
1047 sysdate,
1048 fnd_global.login_id
1049 from csp_plan_details
1050 where organization_id = l_organization_id
1051 and rownum <= l_forecast_periods;
1052 elsif l_forecast_method = -4 then
1053 for cr in c_items loop
1054 if nvl(l_item,cr.inventory_item_id) <> cr.inventory_item_id then
1055 insert into csp_plan_details(
1056 plan_detail_type,
1057 parent_type,
1058 source_number,
1059 source_organization_id,
1060 quantity,
1061 plan_date,
1062 inventory_item_id,
1063 organization_id,
1064 created_by,
1065 creation_date,
1066 last_updated_by,
1067 last_update_date,
1068 last_update_login)
1069 select '7000',
1070 null,
1071 null,
1072 null,
1073 l_forecast + l_trend * rownum * l_period_size/l_forecast_period_size,
1074 trunc(sysdate + cr.repair_lead_time + (rownum-1) * l_period_size),
1075 l_item,
1076 l_organization_id,
1077 fnd_global.user_id,
1078 sysdate,
1079 fnd_global.user_id,
1080 sysdate,
1081 fnd_global.login_id
1082 from csp_plan_details
1083 where organization_id = l_organization_id
1084 and rownum <= l_forecast_periods;
1085 l_start := 1;
1086 l_forecast := 0;
1087 l_trend := 0;
1088 l_previous_base := 0;
1089 end if;
1090
1091 l_item := cr.inventory_item_id;
1092 l_repair_lead_time := cr.repair_lead_time;
1093
1094 for l_period in l_start..cr.period loop
1095 l_previous_base := l_forecast;
1096 if cr.period = 1 then
1097 l_forecast := cr.quantity;
1098 elsif l_period < cr.period then
1099 l_forecast := nvl(l_forecast,0) * (1 - cr.alpha);
1100 elsif l_period = cr.period then
1101 l_forecast := cr.quantity * cr.alpha + nvl(l_forecast,0) * (1 - cr.alpha);
1102 end if;
1103 if l_period = 2 then
1104 l_trend := nvl(l_forecast,0) - l_previous_base;
1105 elsif l_period > 2 then
1106 l_trend := (nvl(l_forecast,0) - l_previous_base) * cr.beta + l_trend * (1 - cr.beta);
1107 end if;
1108 l_start := l_period + 1;
1109 end loop;
1110 end loop;
1111 insert into csp_plan_details(
1112 plan_detail_type,
1113 parent_type,
1114 source_number,
1115 source_organization_id,
1116 quantity,
1117 plan_date,
1118 inventory_item_id,
1119 organization_id,
1120 created_by,
1121 creation_date,
1122 last_updated_by,
1123 last_update_date,
1124 last_update_login)
1125 select '7000',
1126 null,
1127 null,
1128 null,
1129 l_forecast + l_trend * rownum * l_period_size/l_forecast_period_size,
1130 trunc(sysdate + l_repair_lead_time + (rownum-1) * l_period_size),
1131 l_item,
1132 l_organization_id,
1133 fnd_global.user_id,
1134 sysdate,
1135 fnd_global.user_id,
1136 sysdate,
1137 fnd_global.login_id
1138 from csp_plan_details
1139 where organization_id = l_organization_id
1140 and rownum <= l_forecast_periods;
1141
1142 end if;
1143
1144 end return_forecast;
1145
1146 procedure forecast is
1147 begin
1148 for l_counter in 1..l_forecast_periods loop
1149 -- Usage Forecast
1150 insert into csp_plan_details(
1151 plan_detail_type,
1152 parent_type,
1153 source_number,
1154 source_organization_id,
1155 quantity,
1156 plan_date,
1157 inventory_item_id,
1158 organization_id,
1159 created_by,
1160 creation_date,
1161 last_updated_by,
1162 last_update_date,
1163 last_update_login)
1164 select '1100',
1165 min('1000'),
1166 null,
1167 null,
1168 round(sum(quantity*l_period_size/cfrb.period_size)),
1169 trunc(sysdate) + (l_counter - 1) * l_period_size,
1170 inventory_item_id,
1171 organization_id,
1172 fnd_global.user_id,
1173 sysdate,
1174 fnd_global.user_id,
1175 sysdate,
1176 fnd_global.login_id
1177 from csp_usage_histories cuh,
1178 csp_forecast_rules_b cfrb
1179 where history_data_type = 2
1180 and period_start_date between decode(l_forecast_method,4,trunc(sysdate) + (l_counter - 1) * l_period_size,period_start_date)
1181 and decode(l_forecast_method,4,trunc(sysdate) + (l_counter - 1) * l_period_size + (l_period_size - 1),trunc(sysdate) + l_period_size * l_forecast_periods - 1)
1182 and organization_id = l_organization_id
1183 and subinventory_code = '-'
1184 and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
1185 and cfrb.forecast_rule_id = l_forecast_rule_id
1186 and cuh.quantity > 0
1187 group by decode(history_data_type,2,'1100',7,'1300',8,'1400'),
1188 trunc(sysdate) + (l_counter - 1) * l_period_size,
1189 inventory_item_id,
1190 organization_id,
1191 sysdate,
1192 fnd_global.user_id,
1193 sysdate,
1194 fnd_global.login_id;
1195 -- Manual Forecast
1196 insert into csp_plan_details(
1197 plan_detail_type,
1198 parent_type,
1199 source_number,
1200 source_organization_id,
1201 quantity,
1202 plan_date,
1203 inventory_item_id,
1204 organization_id,
1205 created_by,
1206 creation_date,
1207 last_updated_by,
1208 last_update_date,
1209 last_update_login)
1210 select decode(history_data_type,7,'1300',8,'1400'),
1211 min('1000'),
1212 null,
1213 null,
1214 sum(quantity),
1215 trunc(sysdate) + (l_counter - 1) * l_period_size,
1216 inventory_item_id,
1217 organization_id,
1218 fnd_global.user_id,
1219 sysdate,
1220 fnd_global.user_id,
1221 sysdate,
1222 fnd_global.login_id
1223 from csp_usage_histories cuh
1224 where history_data_type in (7,8)
1225 and period_start_date between trunc(sysdate) + (l_counter - 1) * l_period_size
1226 and trunc(sysdate) + l_counter * l_period_size - 1
1227 and organization_id = l_organization_id
1228 and subinventory_code = '-'
1229 and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
1230 group by decode(history_data_type,7,'1300',8,'1400'),
1231 trunc(sysdate) + (l_counter - 1) * l_period_size,
1232 inventory_item_id,
1233 organization_id,
1234 sysdate,
1235 fnd_global.user_id,
1236 sysdate,
1237 fnd_global.login_id;
1238
1239 -- Population Forecast
1240 insert into csp_plan_details(
1241 plan_detail_type,
1242 parent_type,
1243 source_number,
1244 source_organization_id,
1245 quantity,
1246 plan_date,
1247 inventory_item_id,
1248 organization_id,
1249 created_by,
1250 creation_date,
1251 last_updated_by,
1252 last_update_date,
1253 last_update_login)
1254 select '1200',
1255 '1000',
1256 null,
1257 null,
1258 round(sum(cpc.population_change*nvl(cfr.manual_failure_rate,cfr.calculated_failure_rate)/7 * l_period_size *
1259 (least(cpc.end_date,(trunc(sysdate)+(l_counter)*l_period_size)) - trunc(sysdate))/(cpc.end_date - cpc.start_date))),
1260 trunc(sysdate) + (l_counter-1) * l_period_size,
1261 cfr.inventory_item_id,
1262 cpc.organization_id,
1263 fnd_global.user_id,
1264 sysdate,
1265 fnd_global.user_id,
1266 sysdate,
1267 fnd_global.login_id
1268 from csp_failure_rates cfr,
1269 csp_population_changes cpc,
1270 csp_planning_parameters cpp
1271 where cfr.inventory_item_id = nvl(l_inventory_item_id,cfr.inventory_item_id)
1272 and cfr.product_id = cpc.product_id
1273 and cpc.organization_id = l_organization_id
1274 and cpp.organization_id = cpc.organization_id
1275 and cpp.organization_type = 'W'
1276 and cfr.planning_parameters_id = cpp.product_norm_node_id
1277 and trunc(cpc.end_date) > trunc(sysdate)
1278 and trunc(cpc.start_date) < trunc(sysdate)+(l_counter-1)*l_period_size
1279 group by
1280 cpc.organization_id,
1281 cfr.inventory_item_id;
1282 commit;
1283 end loop;
1284
1285 -- Warehouse Planned Orders
1286 insert into csp_plan_details(
1287 plan_detail_type,
1288 parent_type,
1289 source_number,
1290 source_organization_id,
1291 quantity,
1292 plan_date,
1293 inventory_item_id,
1294 organization_id,
1295 created_by,
1296 creation_date,
1297 last_updated_by,
1298 last_update_date,
1299 last_update_login)
1300 select max('1610'),
1301 max('1600'),
1302 null,
1303 cpd.organization_id,
1304 greatest(sum(cpd.quantity),0),
1305 max(trunc(trunc(sysdate) + ((cpd.plan_date - trunc(sysdate))/l_period_size)*l_period_size)),
1306 cpd.inventory_item_id,
1307 cpd.source_organization_id,
1308 max(fnd_global.user_id),
1309 max(sysdate),
1310 max(fnd_global.user_id),
1311 max(sysdate),
1312 max(fnd_global.login_id)
1313 from csp_plan_details cpd
1314 where cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
1315 and cpd.source_organization_id = l_organization_id
1316 and cpd.plan_detail_type = '4310'
1317 group by cpd.organization_id,cpd.inventory_item_id,cpd.source_organization_id,cpd.plan_date;--heh
1318 commit;
1319 insert into csp_plan_details(
1320 plan_detail_type,
1321 parent_type,
1322 source_number,
1323 source_organization_id,
1324 quantity,
1325 plan_date,
1326 inventory_item_id,
1327 organization_id,
1328 created_by,
1329 creation_date,
1330 last_updated_by,
1331 last_update_date,
1332 last_update_login)
1333 select max('1600'),
1334 max('1000'),
1335 null,
1336 null,
1337 greatest(sum(cpd.quantity),0),
1338 cpd.plan_date,
1339 cpd.inventory_item_id,
1340 cpd.organization_id,
1341 max(fnd_global.user_id),
1342 max(sysdate),
1343 max(fnd_global.user_id),
1344 max(sysdate),
1345 max(fnd_global.login_id)
1346 from csp_plan_details cpd
1347 where cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
1348 and cpd.organization_id = l_organization_id
1349 and cpd.plan_detail_type = '1610'
1350 group by
1351 cpd.organization_id,
1352 cpd.inventory_item_id,
1353 cpd.plan_date;
1354 commit;
1355
1356 end forecast;
1357
1358
1359 procedure orders is
1360 begin
1361 insert into csp_plan_details(
1362 plan_detail_type,
1363 parent_type,
1364 source_number,
1365 source_organization_id,
1366 quantity,
1367 plan_date,
1368 inventory_item_id,
1369 organization_id,
1370 created_by,
1371 creation_date,
1372 last_updated_by,
1373 last_update_date,
1374 last_update_login)
1375 select min('1500'),
1376 min('1000'),
1377 null,
1378 null,
1379 sum(nvl(oola.ordered_quantity,0) - nvl(oola.cancelled_quantity,0) - nvl(oola.shipped_quantity,0)),
1380 decode(sign(trunc(nvl(oola.schedule_ship_date,nvl(oola.promise_date,oola.request_date)))-trunc(sysdate)),
1381 -1,trunc(sysdate-l_period_size),
1382 0,trunc(sysdate),
1383 1,trunc(sysdate) + trunc((trunc(nvl(oola.schedule_ship_date,nvl(oola.promise_date,oola.request_date)))-trunc(sysdate))/l_period_size)*l_period_size),
1384 oola.inventory_item_id,
1385 oola.ship_from_org_id,
1386 fnd_global.user_id,
1387 sysdate,
1388 fnd_global.user_id,
1389 sysdate,
1390 fnd_global.login_id
1391 from oe_order_lines_all oola
1392 where oola.ship_from_org_id = l_organization_id
1393 and oola.open_flag = 'Y'
1394 and nvl(oola.order_source_id,0) <> 10
1395 and nvl(oola.schedule_ship_date,nvl(oola.promise_date,oola.request_date)) < trunc(sysdate) + l_period_size * l_forecast_periods
1396 and oola.inventory_item_id = nvl(l_inventory_item_id,oola.inventory_item_id)
1397 group by
1398 decode(sign(trunc(nvl(oola.schedule_ship_date,nvl(oola.promise_date,oola.request_date)))-trunc(sysdate)),
1399 -1,trunc(sysdate-l_period_size),
1400 0,trunc(sysdate),
1401 1,trunc(sysdate) + trunc((trunc(nvl(oola.schedule_ship_date,nvl(oola.promise_date,oola.request_date)))-trunc(sysdate))/l_period_size)*l_period_size),
1402 oola.inventory_item_id,
1403 oola.ship_from_org_id;
1404 end orders;
1405
1406 procedure supply is
1407 begin
1408 insert into csp_plan_details(
1409 plan_detail_type,
1410 parent_type,
1411 source_number,
1412 source_organization_id,
1413 quantity,
1414 plan_date,
1415 inventory_item_id,
1416 organization_id,
1417 created_by,
1418 creation_date,
1419 last_updated_by,
1420 last_update_date,
1421 last_update_login)
1422 select decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
1423 -1,'2310',
1424 ms.from_organization_id*-1,'2110',
1425 crph.requisition_header_id,'2210',
1426 cpt.req_line_id,'2210'),
1427 decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
1428 -1,'2300',
1429 ms.from_organization_id*-1,'2100',
1430 crph.requisition_header_id,'2200',
1431 cpt.req_line_id,'2200'),
1432 pv.vendor_name||'.'||pha.segment1,
1433 nvl(ms.from_organization_id,-1),
1434 sum(to_org_primary_quantity),
1435 trunc(nvl(expected_delivery_date,nvl(crph.need_by_date,nvl(ms.need_by_date,trunc(sysdate))))),
1436 ms.item_id,
1437 ms.to_organization_id,
1438 fnd_global.user_id,
1439 sysdate,
1440 fnd_global.user_id,
1441 sysdate,
1442 fnd_global.login_id
1443 from mtl_supply ms,
1444 csp_repair_po_headers crph,
1445 po_headers_all pha,
1446 po_vendors pv,
1447 csd_product_transactions cpt
1448 where ms.to_organization_id = l_organization_id
1449 and ms.item_id > 0
1450 and ms.supply_type_code <> 'REQ'
1451 and ms.item_id = nvl(l_inventory_item_id,ms.item_id)
1452 and crph.purchase_order_header_id(+) = ms.po_header_id
1453 and pha.po_header_id = ms.po_header_id
1454 and pha.vendor_id = pv.vendor_id
1455 and cpt.req_line_id(+) = ms.req_line_id
1456 group by decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
1457 -1,'2310',
1458 ms.from_organization_id*-1,'2110',
1459 crph.requisition_header_id,'2210',
1460 cpt.req_line_id,'2210'),
1461 decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
1462 -1,'2300',
1463 ms.from_organization_id*-1,'2100',
1464 crph.requisition_header_id,'2200',
1465 cpt.req_line_id,'2200'),
1466 pv.vendor_name||'.'||pha.segment1,
1467 nvl(ms.from_organization_id,-1),
1468 trunc(nvl(expected_delivery_date,nvl(crph.need_by_date,nvl(ms.need_by_date,trunc(sysdate))))),
1469 ms.item_id,
1470 ms.to_organization_id;
1471
1472 insert into csp_plan_details(
1473 plan_detail_type,
1474 parent_type,
1475 source_number,
1476 source_organization_id,
1477 quantity,
1478 plan_date,
1479 inventory_item_id,
1480 organization_id,
1481 created_by,
1482 creation_date,
1483 last_updated_by,
1484 last_update_date,
1485 last_update_login)
1486 select decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
1487 -1,'2310',
1488 ms.from_organization_id*-1,'2110',
1489 crph.requisition_header_id,'2210',
1490 cpt.req_line_id,'2210'),
1491 decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
1492 -1,'2300',
1493 ms.from_organization_id*-1,'2100',
1494 crph.requisition_header_id,'2200',
1495 cpt.req_line_id,'2200'),
1496 nvl(ooha.order_number,prha.segment1),
1497 nvl(ms.from_organization_id,-1),
1498 sum(ms.to_org_primary_quantity),
1499 trunc(nvl(ms.expected_delivery_date,nvl(crph.need_by_date,nvl(ms.need_by_date,trunc(sysdate))))),
1500 ms.item_id,
1501 ms.to_organization_id,
1502 fnd_global.user_id,
1503 sysdate,
1504 fnd_global.user_id,
1505 sysdate,
1506 fnd_global.login_id
1507 from mtl_supply ms,
1508 csp_repair_po_headers crph,
1509 po_requisition_headers_all prha,
1510 oe_order_headers_all ooha,
1511 csd_product_transactions cpt
1512 where ms.to_organization_id = l_organization_id
1513 and ms.item_id > 0
1514 and ms.supply_type_code = 'REQ'
1515 and ms.item_id = nvl(l_inventory_item_id,ms.item_id)
1516 and crph.requisition_header_id(+) = ms.req_header_id
1517 and prha.requisition_header_id = ms.req_header_id
1518 and ooha.order_source_id(+) = 10
1519 and ooha.orig_sys_document_ref(+) = prha.segment1
1520 and cpt.req_line_id(+) = ms.req_line_id
1521 group by decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
1522 -1,'2310',
1523 ms.from_organization_id*-1,'2110',
1524 crph.requisition_header_id,'2210',
1525 cpt.req_line_id,'2210'),
1526 decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
1527 -1,'2300',
1528 ms.from_organization_id*-1,'2100',
1529 crph.requisition_header_id,'2200',
1530 cpt.req_line_id,'2200'),
1531 nvl(ooha.order_number,prha.segment1),
1532 nvl(ms.from_organization_id,-1),
1533 trunc(nvl(ms.expected_delivery_date,nvl(crph.need_by_date,nvl(ms.need_by_date,trunc(sysdate))))),
1534 ms.item_id,
1535 ms.to_organization_id;
1536
1537 insert into csp_plan_details(
1538 plan_detail_type,
1539 parent_type,
1540 source_number,
1541 source_organization_id,
1542 quantity,
1543 plan_date,
1544 inventory_item_id,
1545 organization_id,
1546 created_by,
1547 creation_date,
1548 last_updated_by,
1549 last_update_date,
1550 last_update_login)
1551 select decode(pria.source_type_code,'VENDOR',decode(crph.requisition_line_id,null,'2310','2210'),'2110'),
1552 decode(pria.source_type_code,'VENDOR',decode(crph.requisition_line_id,null,'2300','2200'),'2100'),
1553 pria.req_number_segment1,
1554 nvl(pria.source_organization_id,-1),
1555 pria.quantity,
1556 trunc(nvl(pria.need_by_date,nvl(crph.need_by_date,trunc(sysdate)))),
1557 pria.item_id,
1558 pria.destination_organization_id,
1559 fnd_global.user_id,
1560 sysdate,
1561 fnd_global.user_id,
1562 sysdate,
1563 fnd_global.login_id
1564 from po_requisitions_interface_all pria,
1565 csp_repair_po_headers crph
1566 where pria.destination_organization_id = l_organization_id
1567 and pria.item_id = nvl(l_inventory_item_id,pria.item_id)
1568 and crph.requisition_line_id(+) = pria.requisition_line_id;
1569 /* Not needed
1570 insert into csp_plan_details(
1571 plan_detail_type,
1572 parent_type,
1573 source_number,
1574 source_organization_id,
1575 quantity,
1576 plan_date,
1577 inventory_item_id,
1578 organization_id,
1579 created_by,
1580 creation_date,
1581 last_updated_by,
1582 last_update_date,
1583 last_update_login)
1584 select '2310',
1585 '2300',
1586 wjsi.job_name,
1587 wjsi.organization_id,
1588 wjsi.start_quantity,
1589 trunc(nvl(wjsi.last_unit_completion_date,sysdate)),
1590 wjsi.primary_item_id,
1591 wjsi.organization_id,
1592 -1001012,--fnd_global.user_id,
1593 sysdate,
1594 fnd_global.user_id,
1595 sysdate,
1596 fnd_global.login_id
1597 from wip_job_schedule_interface wjsi
1598 where wjsi.organization_id = l_organization_id
1599 and wjsi.primary_item_id = nvl(l_inventory_item_id,wjsi.primary_item_id);
1600 */
1601 end supply;
1602
1603 procedure total_requirement is
1604 begin
1605
1606 insert into csp_plan_details(
1607 plan_detail_type,
1608 parent_type,
1609 source_number,
1610 source_organization_id,
1611 quantity,
1612 plan_date,
1613 inventory_item_id,
1614 organization_id,
1615 created_by,
1616 creation_date,
1617 last_updated_by,
1618 last_update_date,
1619 last_update_login)
1620 select parent_type,
1621 '1',
1622 null,
1623 null,
1624 greatest(sum(quantity),0),
1625 plan_date,
1626 inventory_item_id,
1627 organization_id,
1628 min(created_by),
1629 min(creation_date),
1630 min(last_updated_by),
1631 min(last_update_date),
1632 min(last_update_login)
1633 from csp_plan_details cpd
1634 where parent_type = '1000'
1635 and plan_detail_type in ('1100','1200','1300','1500','1600')
1636 and organization_id = l_organization_id
1637 and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
1638 and not exists (select 'x'
1639 from csp_plan_details
1640 where organization_id = cpd.organization_id
1641 and inventory_item_id = cpd.inventory_item_id
1642 and plan_date = cpd.plan_date
1643 and plan_detail_type = '1400')
1644 group by parent_type,'1',null,null,plan_date,inventory_item_id,organization_id;
1645
1646 insert into csp_plan_details(
1647 plan_detail_type,
1648 parent_type,
1649 source_number,
1650 source_organization_id,
1651 quantity,
1652 plan_date,
1653 inventory_item_id,
1654 organization_id,
1655 created_by,
1656 creation_date,
1657 last_updated_by,
1658 last_update_date,
1659 last_update_login)
1660 select parent_type,
1661 '1',
1662 null,
1663 null,
1664 greatest(sum(quantity),0),
1665 plan_date,
1666 inventory_item_id,
1667 organization_id,
1668 min(created_by),
1669 min(creation_date),
1670 min(last_updated_by),
1671 min(last_update_date),
1672 min(last_update_login)
1673 from csp_plan_details cpd
1674 where parent_type = '1000'
1675 and plan_detail_type in ('1400','1500','1600')
1676 and organization_id = l_organization_id
1677 and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
1678 and not exists (select 'x'
1679 from csp_plan_details
1680 where organization_id = cpd.organization_id
1681 and inventory_item_id = cpd.inventory_item_id
1682 and plan_date = cpd.plan_date
1683 and plan_detail_type in ('1000'))
1684 group by parent_type,'1',null,null,plan_date,inventory_item_id,organization_id;
1685
1686
1687 end total_requirement;
1688
1689 procedure total_on_order is
1690 begin
1691 --Total On Order
1692 insert into csp_plan_details(
1693 plan_detail_type,
1694 parent_type,
1695 source_number,
1696 source_organization_id,
1697 quantity,
1698 plan_date,
1699 inventory_item_id,
1700 organization_id,
1701 created_by,
1702 creation_date,
1703 last_updated_by,
1704 last_update_date,
1705 last_update_login)
1706 select parent_type,
1707 '2000',
1708 null,
1709 null,
1710 sum(quantity),
1711 plan_date, --hehtrunc(greatest(trunc(sysdate) + floor(((plan_date - trunc(sysdate))/l_period_size))*l_period_size,trunc(sysdate) - l_period_size)),
1712 inventory_item_id,
1713 organization_id,
1714 min(created_by),
1715 min(creation_date),
1716 min(last_updated_by),
1717 min(last_update_date),
1718 min(last_update_login)
1719 from csp_plan_details
1720 where parent_type in ('2100','2200','2300')
1721 and organization_id = l_organization_id
1722 and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
1723 group by parent_type,plan_date,--trunc(greatest(trunc(sysdate) + floor(((plan_date - trunc(sysdate))/l_period_size))*l_period_size,trunc(sysdate) - l_period_size)),
1724 inventory_item_id,organization_id;
1725
1726 insert into csp_plan_details(
1727 plan_detail_type,
1728 parent_type,
1729 source_number,
1730 source_organization_id,
1731 quantity,
1732 plan_date,
1733 inventory_item_id,
1734 organization_id,
1735 created_by,
1736 creation_date,
1737 last_updated_by,
1738 last_update_date,
1739 last_update_login)
1740 select min('2000'),
1741 min('1'),
1742 null,
1743 null,
1744 sum(quantity),
1745 plan_date,
1746 inventory_item_id,
1747 organization_id,
1748 min(created_by),
1749 min(creation_date),
1750 min(last_updated_by),
1751 min(last_update_date),
1752 min(last_update_login)
1753 from csp_plan_details
1754 where plan_detail_type in ('2100','2200','2300')
1755 and organization_id = l_organization_id
1756 and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
1757 group by organization_id,inventory_item_id,plan_date;
1758
1759 end total_on_order;
1760
1761 procedure unfilled_requirement(p_source_type varchar2) is
1762 i number := 0;
1763 begin
1764 for i in 0..l_forecast_periods loop
1765 insert into csp_plan_details(
1766 plan_detail_type,
1767 parent_type,
1768 source_number,
1769 source_organization_id,
1770 quantity,
1771 plan_date,
1772 inventory_item_id,
1773 organization_id,
1774 created_by,
1775 creation_date,
1776 last_updated_by,
1777 last_update_date,
1778 last_update_login)
1779 select
1780 min('9004'),
1781 min('1'),
1782 null,
1783 null,
1784 least(0,sum(decode(cpd.plan_detail_type,'1000',decode(sign(trunc(sysdate+6+i*l_period_size)-cpd.plan_date),-1,0,cpd.quantity*-1),
1785 '4220',decode(sign(trunc(sysdate+6+i*l_period_size)-cpd.plan_date),-1,0,cpd.quantity),
1786 '1' ,cpd.available_quantity,cpd.quantity))
1787 - decode(p_source_type,
1788 'EXCESS',min(nvl(cpr.excess_safety_stock,0)),
1789 'REPAIR',min(nvl(cpr.repair_safety_stock,0)),
1790 'REPAIR_FORECAST',min(nvl(cpr.repair_safety_stock,0)),
1791 'NEWBUY',min(nvl(cpr.newbuy_safety_stock,0)))) * -1,
1792 min(trunc(sysdate+i*l_period_size)),
1793 cpd.inventory_item_id,
1794 cpd.organization_id,
1795 fnd_global.user_id,
1796 sysdate,
1797 fnd_global.user_id,
1798 sysdate,
1799 fnd_global.login_id
1800 from csp_plan_details cpd,
1801 csp_plan_reorders cpr
1802 where cpd.plan_detail_type in ('1','1000','2000','4110','4210','4310','4220')
1803 and cpr.organization_id (+) = cpd.organization_id
1804 and cpr.inventory_item_id (+) = cpd.inventory_item_id
1805 and cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
1806 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
1807 group by cpd.organization_id,cpd.inventory_item_id;
1808 /*
1809 where cpd.plan_detail_type in ('1','4110','4210','4310','4220')
1810 and cpd2.plan_detail_type = '3000'
1811 and cpd2.plan_date = trunc(sysdate+i*l_period_size)
1812 and cpd2.organization_id = cpd.organization_id
1813 and cpd2.inventory_item_id = cpd.inventory_item_id
1814 and cpd.plan_date <= trunc(sysdate+i*l_period_size)
1815 and cpr.organization_id (+) = cpd.organization_id
1816 and cpr.inventory_item_id (+) = cpd.inventory_item_id
1817 and cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
1818 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
1819 group by cpd.organization_id,cpd.inventory_item_id;
1820 */
1821 end loop;
1822 -- delete unfilled requirement for parts that are superseded
1823 delete from csp_plan_details cpd
1824 where (organization_id,inventory_item_id) in
1825 (select csi.organization_id,csi.inventory_item_id
1826 from csp_supersede_items csi
1827 where csi.inventory_item_id = cpd.inventory_item_id
1828 and csi.organization_id = cpd.organization_id
1829 and csi.sub_inventory_code = '-'
1830 and csi.item_supplied <> csi.inventory_item_id)
1831 and cpd.organization_id = l_organization_id
1832 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
1833 and cpd.plan_detail_type = '9004';
1834
1835 end unfilled_requirement;
1836
1837 procedure newbuy_excess_onorder is
1838 begin
1839 insert into csp_plan_details(
1840 plan_detail_type,
1841 parent_type,
1842 source_number,
1843 source_organization_id,
1844 quantity,
1845 plan_date,
1846 inventory_item_id,
1847 organization_id,
1848 created_by,
1849 creation_date,
1850 last_updated_by,
1851 last_update_date,
1852 last_update_login)
1853 select
1854 min('8110'),
1855 null,
1856 null,
1857 null,
1858 greatest(0,least(sum(decode(cpd.plan_detail_type,'2300',nvl(cpd.quantity,0),0)),
1859 sum(decode(cpd.plan_detail_type,'2300',0,
1860 '1',nvl(cpd.available_quantity,0),
1861 nvl(cpd.quantity,0)))
1862 -greatest(nvl(cpr.newbuy_rop,0)+nvl(cpr.newbuy_edq,0)*nvl(min(cipp.newbuy_edq_multiple),nvl(l_edq_multiple,1)),
1863 nvl(cpr.repair_rop,0)+nvl(cpr.repair_edq,0)*nvl(min(cipp.repair_edq_multiple),nvl(l_edq_multiple,1)),
1864 nvl(cpr.excess_rop,0)+nvl(cpr.excess_edq,0)*nvl(min(cipp.excess_edq_multiple),nvl(l_edq_multiple,1)),0))) excess_cancel_newbuy,
1865 min(cpd.plan_date),
1866 cpd.inventory_item_id,
1867 cpd.organization_id,
1868 fnd_global.user_id,
1869 sysdate,
1870 fnd_global.user_id,
1871 sysdate,
1872 fnd_global.login_id
1873 from csp_plan_details cpd,
1874 csp_plan_reorders cpr,
1875 csp_item_pl_params cipp
1876 where cpd.organization_id = l_organization_id
1877 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
1878 and cpd.plan_detail_type in ('1','2000','2300')
1879 and cpd.quantity > 0
1880 and cpr.organization_id(+) = cpd.organization_id
1881 and cpr.inventory_item_id(+) = cpd.inventory_item_id
1882 and cipp.organization_id(+) = cpd.organization_id
1883 and cipp.inventory_item_id(+) = cpd.inventory_item_id
1884 group by cpr.newbuy_rop,
1885 cpr.newbuy_edq,
1886 cpr.repair_rop,
1887 cpr.repair_edq,
1888 cpr.excess_rop,
1889 cpr.excess_edq,
1890 cpd.inventory_item_id,
1891 cpd.organization_id,
1892 fnd_global.user_id,
1893 sysdate,
1894 fnd_global.user_id,
1895 sysdate,
1896 fnd_global.login_id;
1897 -- Delete new buy excess on orders that have a value less than minimum value
1898 delete from csp_plan_details
1899 where (plan_detail_type,organization_id,inventory_item_id) in
1900 (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
1901 from csp_plan_details cpd,
1902 mtl_parameters mp,
1903 cst_item_costs cict
1904 where cpd.plan_detail_type = '8110'
1905 and mp.organization_id = cict.organization_id
1906 and cict.inventory_item_id = cpd.inventory_item_id
1907 and cict.organization_id = cpd.organization_id
1908 and cict.cost_type_id = mp.primary_cost_method
1909 and cpd.organization_id = l_organization_id
1910 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
1911 and nvl(cpd.quantity * cict.item_cost,1) <= l_minimum_value);
1912 end;
1913
1914 procedure unutilized_excess is
1915 begin
1916 insert into csp_plan_details(
1917 plan_detail_type,
1918 parent_type,
1919 source_number,
1920 source_organization_id,
1921 quantity,
1922 plan_date,
1923 inventory_item_id,
1924 organization_id,
1925 created_by,
1926 creation_date,
1927 last_updated_by,
1928 last_update_date,
1929 last_update_login)
1930 select min('8210'), --Cancel new-buy
1931 null,
1932 null,
1933 null,
1934 greatest(least(sum(decode(cpd.plan_detail_type,'8110',nvl(cpd.quantity*-1,0),'2300',nvl(cpd.quantity,0),0)),
1935 sum(decode(cpd.plan_detail_type,'4110',nvl(cpd.quantity*-1,0),'9001',nvl(cpd.quantity,0),0))),0),
1936 min(cpd.plan_date),
1937 cpd.inventory_item_id,
1938 cpd.organization_id,
1939 fnd_global.user_id,
1940 sysdate,
1941 fnd_global.user_id,
1942 sysdate,
1943 fnd_global.login_id
1944 from csp_plan_details cpd,
1945 csp_plan_reorders cpr
1946 where cpd.organization_id = l_organization_id
1947 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
1948 and cpd.plan_detail_type in ('2300','8110','4110','9001')
1949 and cpd.quantity > 0
1950 and cpr.organization_id(+) = cpd.organization_id
1951 and cpr.inventory_item_id(+) = cpd.inventory_item_id
1952 group by cpr.newbuy_rop,
1953 cpr.newbuy_edq,
1954 cpr.repair_rop,
1955 cpr.repair_edq,
1956 cpr.excess_rop,
1957 cpr.excess_edq,
1958 cpd.inventory_item_id,
1959 cpd.organization_id,
1960 fnd_global.user_id,
1961 sysdate,
1962 fnd_global.user_id,
1963 sysdate,
1964 fnd_global.login_id;
1965 /*
1966 insert into csp_plan_details(
1967 plan_detail_type,
1968 parent_type,
1969 source_number,
1970 source_organization_id,
1971 quantity,
1972 plan_date,
1973 inventory_item_id,
1974 organization_id,
1975 created_by,
1976 creation_date,
1977 last_updated_by,
1978 last_update_date,
1979 last_update_login)
1980 select min('8220'), --Cancel repair
1981 null,
1982 null,
1983 null,
1984 greatest(least(sum(decode(cpd.plan_detail_type,'8120',nvl(cpd.quantity*-1,0),'2200',nvl(cpd.quantity,0),0)),
1985 sum(decode(cpd.plan_detail_type,'2100',nvl(cpd.quantity*-1,0),'8210',nvl(cpd.quantity*-1,0),'9001',nvl(cpd.quantity,0),0))),0),
1986 min(cpd.plan_date),
1987 cpd.inventory_item_id,
1988 cpd.organization_id,
1989 -10014,--fnd_global.user_id,
1990 sysdate,
1991 fnd_global.user_id,
1992 sysdate,
1993 fnd_global.login_id
1994 from csp_plan_details cpd,
1995 csp_plan_reorders cpr
1996 where cpd.organization_id = l_organization_id
1997 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
1998 and cpd.plan_detail_type in ('2100','2200','8120','8210','9001')
1999 and cpd.quantity > 0
2000 and cpr.organization_id(+) = cpd.organization_id
2001 and cpr.inventory_item_id(+) = cpd.inventory_item_id
2002 group by cpr.newbuy_rop,
2003 cpr.newbuy_edq,
2004 cpr.repair_rop,
2005 cpr.repair_edq,
2006 cpr.excess_rop,
2007 cpr.excess_edq,
2008 cpd.inventory_item_id,
2009 cpd.organization_id,
2010 -10016,--fnd_global.user_id,
2011 sysdate,
2012 fnd_global.user_id,
2013 sysdate,
2014 fnd_global.login_id;
2015 */
2016 insert into csp_plan_details(
2017 plan_detail_type,
2018 parent_type,
2019 source_number,
2020 source_organization_id,
2021 quantity,
2022 plan_date,
2023 inventory_item_id,
2024 organization_id,
2025 created_by,
2026 creation_date,
2027 last_updated_by,
2028 last_update_date,
2029 last_update_login)
2030 select min('8220'), --Cancel repair
2031 null,
2032 null,
2033 null,
2034 greatest(least(sum(decode(cpd.plan_detail_type,'8120',nvl(cpd.quantity*-1,0),'2200',nvl(cpd.quantity,0),0)),
2035 sum(decode(cpd.plan_detail_type,'4100',nvl(cpd.quantity*-1,0),'8210',nvl(cpd.quantity*-1,0),'9001',nvl(cpd.quantity,0),0))),0),
2036 min(cpd.plan_date),
2037 cpd.inventory_item_id,
2038 cpd.organization_id,
2039 fnd_global.user_id,
2040 sysdate,
2041 fnd_global.user_id,
2042 sysdate,
2043 fnd_global.login_id
2044 from csp_plan_details cpd,
2045 csp_plan_reorders cpr
2046 where cpd.organization_id = l_organization_id
2047 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2048 and cpd.plan_detail_type in ('4100','2200','8120','8210','9001')
2049 and cpd.quantity > 0
2050 and cpr.organization_id(+) = cpd.organization_id
2051 and cpr.inventory_item_id(+) = cpd.inventory_item_id
2052 group by cpr.newbuy_rop,
2053 cpr.newbuy_edq,
2054 cpr.repair_rop,
2055 cpr.repair_edq,
2056 cpr.excess_rop,
2057 cpr.excess_edq,
2058 cpd.inventory_item_id,
2059 cpd.organization_id,
2060 fnd_global.user_id,
2061 sysdate,
2062 fnd_global.user_id,
2063 sysdate,
2064 fnd_global.login_id;
2065
2066 delete from csp_plan_details
2067 where (plan_detail_type,organization_id,inventory_item_id) in
2068 (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2069 from csp_plan_details cpd,
2070 mtl_parameters mp,
2071 cst_item_costs cict
2072 where cpd.plan_detail_type in ('8210','8220')
2073 and mp.organization_id = cict.organization_id
2074 and cict.inventory_item_id = cpd.inventory_item_id
2075 and cict.organization_id = cpd.organization_id
2076 and cict.cost_type_id = mp.primary_cost_method
2077 and cpd.organization_id = l_organization_id
2078 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2079 and nvl(cpd.quantity * cict.item_cost,1) <= l_minimum_value);
2080 end;
2081
2082 procedure unutilized_repair is
2083 begin
2084 insert into csp_plan_details(
2085 plan_detail_type,
2086 parent_type,
2087 source_number,
2088 source_organization_id,
2089 quantity,
2090 plan_date,
2091 inventory_item_id,
2092 organization_id,
2093 created_by,
2094 creation_date,
2095 last_updated_by,
2096 last_update_date,
2097 last_update_login)
2098 select min('8310'), --Cancel new-buy
2099 null,
2100 null,
2101 null,
2102 greatest(least(sum(decode(cpd.plan_detail_type,'4200',nvl(cpd.quantity*-1,0),'9002',nvl(cpd.available_quantity,0),'9003',nvl(cpd.available_quantity,0),0)),
2103 sum(decode(cpd.plan_detail_type,'8110',nvl(cpd.quantity*-1,0),'8210',nvl(cpd.quantity*-1,0),'2300',nvl(cpd.quantity,0),0))),0),
2104 min(cpd.plan_date),
2105 cpd.inventory_item_id,
2106 cpd.organization_id,
2107 fnd_global.user_id,
2108 sysdate,
2109 fnd_global.user_id,
2110 sysdate,
2111 fnd_global.login_id
2112 from csp_plan_details cpd,
2113 csp_plan_reorders cpr
2114 where cpd.organization_id = l_organization_id
2115 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2116 and cpd.plan_detail_type in ('4200','2300','8110','8210','9002','9003')
2117 and cpd.quantity > 0
2118 and cpr.organization_id(+) = cpd.organization_id
2119 and cpr.inventory_item_id(+) = cpd.inventory_item_id
2120 group by cpr.newbuy_rop,
2121 cpr.newbuy_edq,
2122 cpr.repair_rop,
2123 cpr.repair_edq,
2124 cpr.excess_rop,
2125 cpr.excess_edq,
2126 cpd.inventory_item_id,
2127 cpd.organization_id,
2128 fnd_global.user_id,
2129 sysdate,
2130 fnd_global.user_id,
2131 sysdate,
2132 fnd_global.login_id;
2133
2134 delete from csp_plan_details
2135 where (plan_detail_type,organization_id,inventory_item_id) in
2136 (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2137 from csp_plan_details cpd,
2138 mtl_parameters mp,
2139 cst_item_costs cict
2140 where cpd.plan_detail_type = '8310'
2141 and mp.organization_id = cict.organization_id
2142 and cict.inventory_item_id = cpd.inventory_item_id
2143 and cict.organization_id = cpd.organization_id
2144 and cict.cost_type_id = mp.primary_cost_method
2145 and cpd.organization_id = l_organization_id
2146 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2147 and nvl(cpd.quantity * cict.item_cost,1) <= l_minimum_value);
2148 end;
2149
2150 procedure repair_excess_onorder is
2151 begin
2152 insert into csp_plan_details(
2153 plan_detail_type,
2154 parent_type,
2155 source_number,
2156 source_organization_id,
2157 quantity,
2158 plan_date,
2159 inventory_item_id,
2160 organization_id,
2161 created_by,
2162 creation_date,
2163 last_updated_by,
2164 last_update_date,
2165 last_update_login)
2166 select min('8120'),
2167 null,
2168 null,
2169 null,
2170 greatest(least(sum(decode(cpd.plan_detail_type,'2200',nvl(cpd.quantity,0),0)),
2171 sum(decode(cpd.plan_detail_type,
2172 '1',nvl(cpd.available_quantity,0),
2173 '8110',nvl(cpd.quantity*-1,0),
2174 '2200',0,
2175 nvl(cpd.quantity,0))) -
2176 greatest(nvl(cpr.newbuy_rop,0)+nvl(cpr.newbuy_edq,0)*nvl(min(cipp.newbuy_edq_multiple),nvl(l_edq_multiple,1)),
2177 nvl(cpr.repair_rop,0)+nvl(cpr.repair_edq,0)*nvl(min(cipp.repair_edq_multiple),nvl(l_edq_multiple,1)),
2178 nvl(cpr.excess_rop,0)+nvl(cpr.excess_edq,0)*nvl(min(cipp.excess_edq_multiple),nvl(l_edq_multiple,1)))),0),
2179 min(cpd.plan_date),
2180 cpd.inventory_item_id,
2181 cpd.organization_id,
2182 fnd_global.user_id,
2183 sysdate,
2184 fnd_global.user_id,
2185 sysdate,
2186 fnd_global.login_id
2187 from csp_plan_details cpd,
2188 csp_plan_reorders cpr,
2189 csp_item_pl_params cipp
2190 where cpd.organization_id = l_organization_id
2191 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2192 and cpd.plan_detail_type in ('1','2000','2200','8110')
2193 and cpd.quantity > 0
2194 and cpr.organization_id(+) = cpd.organization_id
2195 and cpr.inventory_item_id(+) = cpd.inventory_item_id
2196 and cipp.organization_id(+) = cpd.organization_id
2197 and cipp.inventory_item_id(+) = cpd.inventory_item_id
2198 group by cpr.newbuy_rop,
2199 cpr.newbuy_edq,
2200 cpr.repair_rop,
2201 cpr.repair_edq,
2202 cpr.excess_rop,
2203 cpr.excess_edq,
2204 cpd.inventory_item_id,
2205 cpd.organization_id,
2206 fnd_global.user_id,
2207 sysdate,
2208 fnd_global.user_id,
2209 sysdate,
2210 fnd_global.login_id;
2211
2212 -- Delete repair excess on orders that have a value less than minimum value
2213 delete from csp_plan_details
2214 where (plan_detail_type,organization_id,inventory_item_id) in
2215 (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2216 from csp_plan_details cpd,
2217 mtl_parameters mp,
2218 cst_item_costs cict
2219 where cpd.plan_detail_type = '8120'
2220 and mp.organization_id = cict.organization_id
2221 and cict.inventory_item_id = cpd.inventory_item_id
2222 and cict.organization_id = cpd.organization_id
2223 and cict.cost_type_id = mp.primary_cost_method
2224 and cpd.organization_id = l_organization_id
2225 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2226 and nvl(cpd.quantity * cict.item_cost,1) <= l_minimum_value);
2227 end;
2228
2229 procedure reschedule_in is
2230 begin
2231 insert into csp_plan_details(
2232 plan_detail_type,
2233 parent_type,
2234 inventory_item_id,
2235 organization_id,
2236 plan_date,
2237 quantity,
2238 created_by,
2239 creation_date,
2240 last_updated_by,
2241 last_update_date,
2242 last_update_login)
2243 select '8410',
2244 null,
2245 cpd.inventory_item_id,
2246 cpd.organization_id,
2247 cpd.plan_date,
2248 (cpd.quantity - decode(l_onhand_condition_in,
2249 0,0,
2250 1,greatest(cpr.repair_safety_stock,
2251 cpr.excess_safety_stock,
2252 cpr.newbuy_safety_stock),
2253 2,greatest(cpr.repair_rop,
2254 cpr.excess_rop,
2255 cpr.newbuy_rop)))*-1,
2256 fnd_global.user_id,
2257 sysdate,
2258 fnd_global.user_id,
2259 sysdate,
2260 fnd_global.login_id
2261 from csp_plan_details cpd,
2262 csp_plan_reorders cpr
2263 where cpd.organization_id = l_organization_id
2264 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2265 and cpd.plan_detail_type = l_onhand_type_in
2266 and cpd.quantity < decode(l_onhand_condition_in,
2267 0,0,
2268 1,greatest(cpr.repair_safety_stock,
2269 cpr.excess_safety_stock,
2270 cpr.newbuy_safety_stock),
2271 2,greatest(cpr.repair_rop,
2272 cpr.excess_rop,
2273 cpr.newbuy_rop))
2274 and cpd.plan_date between trunc(sysdate+decode(nvl(l_start_day_in,0),0,l_period_size * -1,l_start_day_in))
2275 and trunc(sysdate+l_end_day_in)
2276 and cpr.organization_id = cpd.organization_id
2277 and cpr.inventory_item_id = cpd.inventory_item_id
2278 and exists (select 'x'
2279 from csp_plan_details
2280 where organization_id = cpd.organization_id
2281 and inventory_item_id = cpd.inventory_item_id
2282 and plan_detail_type = '2000'
2283 and quantity > 0
2284 and plan_date >= cpd.plan_date)
2285 and plan_date >= trunc(sysdate);
2286
2287 delete from csp_plan_details
2288 where (plan_detail_type,organization_id,inventory_item_id) in
2289 (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2290 from csp_plan_details cpd
2291 where cpd.plan_detail_type = '8410'
2292 and cpd.organization_id = l_organization_id
2293 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2294 group by cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2295 having count(*) < l_periods_in);
2296 /*
2297 insert into csp_plan_details(
2298 plan_detail_type,
2299 parent_type,
2300 inventory_item_id,
2301 organization_id,
2302 created_by,
2303 creation_date,
2304 last_updated_by,
2305 last_update_date,
2306 last_update_login)
2307 select '8410',
2308 null,
2309 cpd.inventory_item_id,
2310 cpd.organization_id,
2311 -1001872,--fnd_global.user_id,
2312 sysdate,
2313 fnd_global.user_id,
2314 sysdate,
2315 fnd_global.login_id
2316 from csp_plan_details cpd,
2317 csp_plan_reorders cpr
2318 where cpd.organization_id = l_organization_id
2319 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2320 and cpd.plan_detail_type = l_onhand_type_in
2321 and cpd.quantity < decode(l_onhand_condition_in,
2322 0,0,
2323 1,greatest(cpr.repair_safety_stock,
2324 cpr.excess_safety_stock,
2325 cpr.newbuy_safety_stock),
2326 2,greatest(cpr.repair_rop,
2327 cpr.excess_rop,
2328 cpr.newbuy_rop))
2329 and cpd.plan_date between trunc(sysdate+l_start_day_in) and trunc(sysdate+l_end_day_in)
2330 and cpr.organization_id = cpd.organization_id
2331 and cpr.inventory_item_id = cpd.inventory_item_id
2332 group by cpd.organization_id,cpd.inventory_item_id
2333 having count(*) > l_periods_in;
2334 */
2335 end reschedule_in;
2336
2337 procedure reschedule_out is
2338 begin
2339 insert into csp_plan_details(
2340 plan_detail_type,
2341 parent_type,
2342 inventory_item_id,
2343 organization_id,
2344 plan_date,
2345 quantity,
2346 created_by,
2347 creation_date,
2348 last_updated_by,
2349 last_update_date,
2350 last_update_login)
2351 select '8420',
2352 null,
2353 cpd.inventory_item_id,
2354 cpd.organization_id,
2355 cpd.plan_date,
2356 cpd.quantity - greatest(cpr.repair_rop + cpr.repair_edq * l_edq_multiple_out,
2357 cpr.excess_rop + cpr.excess_edq * l_edq_multiple_out,
2358 cpr.newbuy_rop + cpr.newbuy_edq * l_edq_multiple_out),
2359 fnd_global.user_id,
2360 sysdate,
2361 fnd_global.user_id,
2362 sysdate,
2363 fnd_global.login_id
2364 from csp_plan_details cpd,
2365 csp_plan_reorders cpr,
2366 cst_item_costs cic,
2367 mtl_parameters mp
2368 where cpd.organization_id = l_organization_id
2369 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2370 and cpd.plan_detail_type = l_onhand_type_out
2371 and cpd.quantity > greatest(cpr.repair_rop + cpr.repair_edq * l_edq_multiple_out,
2372 cpr.excess_rop + cpr.excess_edq * l_edq_multiple_out,
2373 cpr.newbuy_rop + cpr.newbuy_edq * l_edq_multiple_out)
2374 and cpd.plan_date between trunc(sysdate+decode(nvl(l_start_day_out,0),0,l_period_size*-1,l_start_day_out)) and trunc(sysdate+l_end_day_out)
2375 and cpr.organization_id = cpd.organization_id
2376 and cpr.inventory_item_id = cpd.inventory_item_id
2377 and cic.inventory_item_id = cpd.inventory_item_id
2378 and cic.organization_id = cpd.organization_id
2379 and cic.cost_type_id = mp.primary_cost_method
2380 and mp.organization_id = cpd.organization_id
2381 and cic.item_cost * (cpd.quantity - greatest(cpr.repair_rop + cpr.repair_edq * l_edq_multiple_out,
2382 cpr.excess_rop + cpr.excess_edq * l_edq_multiple_out,
2383 cpr.newbuy_rop + cpr.newbuy_edq * l_edq_multiple_out)) > l_onhand_value_out;
2384
2385 delete from csp_plan_details
2386 where (plan_detail_type,organization_id,inventory_item_id) in
2387 (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2388 from csp_plan_details cpd
2389 where cpd.plan_detail_type = '8420'
2390 and cpd.organization_id = l_organization_id
2391 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2392 group by cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2393 having count(*) < l_periods_out);
2394 end reschedule_out;
2395
2396 procedure excess_excess_onorder is
2397 begin
2398 insert into csp_plan_details(
2399 plan_detail_type,
2400 parent_type,
2401 source_number,
2402 source_organization_id,
2403 quantity,
2404 plan_date,
2405 inventory_item_id,
2406 organization_id,
2407 created_by,
2408 creation_date,
2409 last_updated_by,
2410 last_update_date,
2411 last_update_login)
2412 select min('8130'),
2413 null,
2414 null,
2415 null,
2416 greatest(least(sum(decode(cpd.plan_detail_type,'2100',nvl(cpd.quantity,0),0)),
2417 sum(decode(cpd.plan_detail_type,
2418 '8110',nvl(cpd.quantity*-1,0),
2419 '8120',nvl(cpd.quantity*-1,0),
2420 '2100',0,
2421 '1' ,nvl(cpd.available_quantity,0),
2422 nvl(cpd.quantity,0)))
2423 - greatest(nvl(cpr.newbuy_rop,0)+nvl(cpr.newbuy_edq,0)*nvl(l_edq_multiple,1),
2424 nvl(cpr.repair_rop,0)+nvl(cpr.repair_edq,0)*nvl(l_edq_multiple,1),
2425 nvl(cpr.excess_rop,0)+nvl(cpr.excess_edq,0)*nvl(l_edq_multiple,1))),0),
2426 min(cpd.plan_date),
2427 cpd.inventory_item_id,
2428 cpd.organization_id,
2429 fnd_global.user_id,
2430 sysdate,
2431 fnd_global.user_id,
2432 sysdate,
2433 fnd_global.login_id
2434 from csp_plan_details cpd,
2435 csp_plan_reorders cpr
2436 where cpd.organization_id = l_organization_id
2437 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2438 and cpd.quantity > 0
2439 and cpd.plan_detail_type in ('1','2000','2100','8110','8120')
2440 and cpr.organization_id(+) = cpd.organization_id
2441 and cpr.inventory_item_id(+) = cpd.inventory_item_id
2442 group by cpr.newbuy_rop,
2443 cpr.newbuy_edq,
2444 cpr.repair_rop,
2445 cpr.repair_edq,
2446 cpr.excess_rop,
2447 cpr.excess_edq,
2448 cpd.inventory_item_id,
2449 cpd.organization_id,
2450 fnd_global.user_id,
2451 sysdate,
2452 fnd_global.user_id,
2453 sysdate,
2454 fnd_global.login_id;
2455
2456 -- Delete excess excess on orders that have a value less than minimum value
2457 delete from csp_plan_details
2458 where (plan_detail_type,organization_id,inventory_item_id) in
2459 (select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
2460 from csp_plan_details cpd,
2461 mtl_parameters mp,
2462 cst_item_costs cict
2463 where cpd.plan_detail_type = '8130'
2464 and mp.organization_id = cict.organization_id
2465 and cict.inventory_item_id = cpd.inventory_item_id
2466 and cict.organization_id = cpd.organization_id
2467 and cict.cost_type_id = mp.primary_cost_method
2468 and cpd.organization_id = l_organization_id
2469 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2470 and nvl(cpd.quantity * cict.item_cost,1) <= l_minimum_value);
2471 end;
2472
2473 procedure excess is
2474 begin
2475 insert into csp_plan_details(
2476 plan_detail_type,
2477 parent_type,
2478 source_number,
2479 source_organization_id,
2480 available_quantity,
2481 excess_quantity,
2482 onhand_quantity,
2483 quantity,
2484 plan_date,
2485 inventory_item_id,
2486 organization_id,
2487 created_by,
2488 creation_date,
2489 last_updated_by,
2490 last_update_date,
2491 last_update_login)
2492 select min('9001'),
2493 null,
2494 null,
2495 mislv.source_organization_id,
2496 nvl(csp_validate_pub.get_avail_qty(mislv.source_organization_id,null,null,mislv.inventory_item_id),0),
2497 greatest(0,nvl(csp_validate_pub.get_available_qty,0)
2498 - greatest(nvl(cpr.newbuy_rop,0)+nvl(cpr.newbuy_edq,0),
2499 nvl(cpr.repair_rop,0)+nvl(cpr.repair_edq,0),
2500 nvl(cpr.excess_rop,0)+nvl(cpr.excess_edq,0))),
2501 nvl(csp_validate_pub.get_onhand_qty,0),
2502 greatest(0,nvl(csp_validate_pub.get_available_qty,0)
2503 - greatest(nvl(cpr.newbuy_rop,0)+nvl(cpr.newbuy_edq,0),
2504 nvl(cpr.repair_rop,0)+nvl(cpr.repair_edq,0),
2505 nvl(cpr.excess_rop,0)+nvl(cpr.excess_edq,0))),
2506 min(trunc(sysdate)),
2507 mislv.inventory_item_id,
2508 l_organization_id,
2509 fnd_global.user_id,
2510 sysdate,
2511 fnd_global.user_id,
2512 sysdate,
2513 fnd_global.login_id
2514 from MRP_ITEM_SOURCING_LEVELS_V mislv,
2515 csp_plan_reorders cpr,
2516 csp_plan_details cpd
2517 where mislv.organization_id = l_organization_id
2518 and mislv.assignment_set_id = l_usable_assignment_set_id
2519 and mislv.inventory_item_id = cpd.inventory_item_id
2520 and mislv.sourcing_level not in (2,9)
2521 and mislv.source_organization_id <> l_organization_id
2522 and cpr.organization_id(+) = mislv.source_organization_id
2523 and cpr.inventory_item_id(+) = mislv.inventory_item_id
2524 and cpd.organization_id = l_organization_id
2525 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2526 and cpd.plan_detail_type = '1'
2527 group by cpr.newbuy_rop,
2528 cpr.newbuy_edq,
2529 cpr.repair_rop,
2530 cpr.repair_edq,
2531 cpr.excess_rop,
2532 cpr.excess_edq,
2533 mislv.inventory_item_id,
2534 mislv.source_organization_id,
2535 fnd_global.user_id,
2536 sysdate,
2537 fnd_global.user_id,
2538 sysdate,
2539 fnd_global.login_id;
2540 end;
2541
2542 procedure repair is
2543 begin
2544 insert into csp_plan_details(
2545 plan_detail_type,
2546 parent_type,
2547 source_number,
2548 source_organization_id,
2549 available_quantity,
2550 onhand_quantity,
2551 quantity,
2552 plan_date,
2553 inventory_item_id,
2554 organization_id,
2555 created_by,
2556 creation_date,
2557 last_updated_by,
2558 last_update_date,
2559 last_update_login)
2560 select '9002',
2561 null,
2562 null,
2563 mislv.source_organization_id,
2564 nvl(csp_validate_pub.get_avail_qty(mislv.source_organization_id,null,null,mislv.inventory_item_id),0),
2565 nvl(csp_validate_pub.get_onhand_qty,0),
2566 nvl(csp_validate_pub.get_available_qty,0),
2567 trunc(sysdate),
2568 mislv.inventory_item_id,
2569 cpd.organization_id,
2570 fnd_global.user_id,
2571 sysdate,
2572 fnd_global.user_id,
2573 sysdate,
2574 fnd_global.login_id
2575 from MRP_ITEM_SOURCING_LEVELS_V mislv,
2576 csp_plan_details cpd
2577 where mislv.organization_id = cpd.organization_id
2578 and mislv.assignment_set_id = l_defective_assignment_set_id
2579 and mislv.inventory_item_id = cpd.inventory_item_id
2580 and mislv.sourcing_level not in (2,9)
2581 and mislv.source_organization_id <> l_organization_id
2582 and cpd.organization_id = l_organization_id
2583 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2584 and cpd.plan_detail_type = '1';
2585
2586 insert into csp_plan_details(
2587 plan_detail_type,
2588 parent_type,
2589 source_number,
2590 source_organization_id,
2591 available_quantity,
2592 onhand_quantity,
2593 quantity,
2594 plan_date,
2595 inventory_item_id,
2596 organization_id,
2597 related_item_id,
2598 created_by,
2599 creation_date,
2600 last_updated_by,
2601 last_update_date,
2602 last_update_login)
2603 select '9003',
2604 null,
2605 null,
2606 mislv.source_organization_id,
2607 nvl(csp_validate_pub.get_avail_qty(mislv.source_organization_id,null,null,mri.inventory_item_id),0),
2608 nvl(csp_validate_pub.get_onhand_qty,0),
2609 nvl(csp_validate_pub.get_available_qty,0),
2610 trunc(sysdate),
2611 mri.related_item_id,
2612 cpd.organization_id,
2613 mri.inventory_item_id,
2614 fnd_global.user_id,
2615 sysdate,
2616 fnd_global.user_id,
2617 sysdate,
2618 fnd_global.login_id
2619 from MRP_ITEM_SOURCING_LEVELS_V mislv,
2620 csp_plan_details cpd,
2621 mtl_related_items mri,
2622 mtl_parameters mp
2623 where mislv.organization_id = cpd.organization_id
2624 and mislv.assignment_set_id = l_defective_assignment_set_id
2625 and mislv.inventory_item_id = cpd.inventory_item_id
2626 and mislv.sourcing_level not in (2,9)
2627 and mislv.source_organization_id <> cpd.organization_id
2628 and cpd.organization_id = l_organization_id
2629 and cpd.plan_detail_type = '1'
2630 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2631 and mp.organization_id = cpd.organization_id
2632 and mri.organization_id = mp.master_organization_id
2633 and mri.relationship_type_id = 18
2634 and mri.related_item_id = cpd.inventory_item_id;
2635 /*
2636 -- Delete repair information for down level parts
2637 delete from csp_plan_details cpd
2638 where cpd.plan_detail_type = '9002'
2639 and cpd.organization_id = l_organization_id
2640 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2641 and cpd.inventory_item_id in
2642 (select cpd2.related_item_id
2643 from csp_plan_details cpd2
2644 where cpd2.plan_detail_type = '9003'
2645 and cpd2.organization_id = l_organization_id
2646 and cpd2.related_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id));
2647 */
2648 end;
2649
2650 procedure planned_orders(p_source_type varchar2) is
2651 l_source_organization_id number;
2652 l__quantity number;
2653 l_order_edq number;
2654 l_excess_order_quantity number;
2655 lv_unfilled_requirement number;
2656 l_plan_date date;
2657 l_edq_quantity number := 0;
2658 l_unfilled_quantity number := 0;
2659 l_order_quantity number := 0;
2660 l_avail_quantity number := 0;
2661 l_adjusted number := 0;
2662 l_related_item_id number := null;
2663 l_rep_return_date date := null;
2664 l_source_type varchar2(15);
2665
2666 cursor c_unfilled_items is
2667 select distinct cpd.inventory_item_id,
2668 cpd.organization_id
2669 from csp_plan_details cpd
2670 where cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
2671 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2672 and cpd.plan_detail_type = '9004'
2673 and cpd.quantity > 0;
2674
2675 cursor c_unfilled_requirements(p_inventory_item_id number) is
2676 select cpd.quantity,
2677 -- greatest(nvl(cpr.excess_safety_stock,0),
2678 -- nvl(cpr.repair_safety_stock,0),
2679 -- nvl(cpr.newbuy_safety_stock,0)) +
2680 -- nvl(cpr.excess_safety_stock,0) +
2681 -- nvl(decode(p_source_type,'EXCESS',cpr.excess_safety_stock,
2682 -- 'REPAIR',cpr.repair_safety_stock,
2683 -- 'NEWBUY',cpr.newbuy_safety_stock,
2684 -- 'REPAIR_FORECAST',cpr.repair_safety_stock),0) unfilled_quantity,
2685 decode(p_source_type,'EXCESS',cpr.excess_edq,
2686 'REPAIR',cpr.repair_edq,
2687 'NEWBUY',cpr.newbuy_edq,
2688 'REPAIR_FORECAST',cpr.repair_edq) edq_quantity,
2689 trunc(cpd.plan_date) plan_date,
2690 cpr.newbuy_safety_stock - cpr.repair_safety_stock
2691 from csp_plan_details cpd,
2692 csp_plan_reorders cpr
2693 where cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
2694 and cpd.inventory_item_id = p_inventory_item_id
2695 and cpr.organization_id(+) = cpd.organization_id
2696 and cpr.inventory_item_id(+) = cpd.inventory_item_id
2697 and cpd.plan_detail_type = '9004'
2698 and cpd.quantity > 0
2699 -- and cpd.plan_date > nvl(l_rep_return_date,cpd.plan_date-1)
2700 order by cpd.organization_id,
2701 cpd.inventory_item_id,
2702 cpd.plan_date;
2703
2704 cursor c_excess(p_inventory_item_id number) is
2705 select cpd.source_organization_id,
2706 sum(decode(cpd.plan_detail_type,'4110',nvl(cpd.quantity,0)*-1,
2707 nvl(cpd.quantity,0)))
2708 from csp_plan_details cpd
2709 where cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
2710 and cpd.inventory_item_id = p_inventory_item_id
2711 and cpd.plan_detail_type in ('4110','9001')
2712 group by cpd.source_organization_id,
2713 cpd.inventory_item_id
2714 order by 2 desc;
2715
2716 cursor c_repair(p_inventory_item_id number) is
2717 select cpd.source_organization_id,
2718 sum(decode(cpd.plan_detail_type,'4210',nvl(cpd.quantity,0)*-1,
2719 nvl(cpd.available_quantity,0))),
2720 cpd.related_item_id
2721 from csp_plan_details cpd
2722 where cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
2723 and cpd.inventory_item_id = p_inventory_item_id
2724 and cpd.plan_detail_type in ('4210','9002','9003')
2725 group by cpd.source_organization_id,
2726 cpd.inventory_item_id,
2727 cpd.related_item_id
2728 order by cpd.related_item_id desc, 2 desc;
2729
2730 cursor c_repair_forecast(p_inventory_item_id number) is
2731 select null,
2732 sum(nvl(decode(cpd.plan_detail_type,'4220',cpd.quantity*-1,cpd.quantity),0)),
2733 cpr.repair_edq,
2734 null
2735 from csp_plan_reorders cpr,
2736 csp_plan_details cpd
2737 where cpr.organization_id = l_organization_id
2738 and cpr.inventory_item_id = p_inventory_item_id
2739 and cpd.organization_id = cpr.organization_id
2740 and cpd.inventory_item_id = p_inventory_item_id
2741 and cpd.plan_detail_type in ('4220','7000')
2742 and cpd.plan_date <= l_plan_date
2743 group by
2744 cpr.repair_edq
2745 order by 2 desc;
2746
2747 cursor c_newbuy(p_inventory_item_id number) is
2748 select nvl(msib.source_organization_id,mp.source_organization_id),
2749 cpr.newbuy_edq,
2750 null
2751 from mtl_system_items_b msib,
2752 mtl_parameters mp,
2753 csp_plan_reorders cpr
2754 where msib.organization_id = l_organization_id
2755 and msib.inventory_item_id = p_inventory_item_id
2756 and mp.organization_id = msib.organization_id
2757 and cpr.organization_id = msib.organization_id
2758 and cpr.inventory_item_id = msib.inventory_item_id;
2759
2760 begin
2761
2762 for cr in c_unfilled_items loop
2763 loop
2764 l_source_organization_id := null;
2765 l_avail_quantity := 0;
2766 l_order_edq := 0;
2767 l_related_item_id := null;
2768 open c_unfilled_requirements(cr.inventory_item_id);
2769 fetch c_unfilled_requirements into l_unfilled_quantity,l_edq_quantity,l_plan_date,l_adjusted;
2770 exit when c_unfilled_requirements%notfound;
2771 close c_unfilled_requirements;
2772
2773 if p_source_type = 'EXCESS' then
2774 l_source_type := p_source_type;
2775 open c_excess(cr.inventory_item_id);
2776 fetch c_excess into l_source_organization_id,l_avail_quantity;
2777 close c_excess;
2778 elsif p_source_type = 'REPAIR' then
2779 l_source_type := p_source_type;
2780 open c_repair(cr.inventory_item_id);
2781 loop
2782 fetch c_repair into l_source_organization_id,l_avail_quantity,l_related_item_id;
2783
2784 if c_repair%notfound or nvl(l_avail_quantity,0) > 0 then
2785 close c_repair;
2786 exit;
2787 end if;
2788 end loop;
2789 elsif p_source_type = 'REPAIR_FORECAST' then
2790 l_source_type := 'REPAIR_FORECAST';
2791 open c_repair_forecast(cr.inventory_item_id);
2792 fetch c_repair_forecast into l_source_organization_id,l_avail_quantity,l_edq_quantity,l_related_item_id;
2793 close c_repair_forecast;
2794
2795 if nvl(l_avail_quantity,0) <= 0 and
2796 l_plan_date < trunc(sysdate)+l_period_size*l_forecast_periods then
2797 l_source_type := 'NEWBUY';
2798 open c_newbuy(cr.inventory_item_id);
2799 fetch c_newbuy into l_source_organization_id,l_edq_quantity,l_related_item_id;
2800 close c_newbuy;
2801 l_avail_quantity := l_unfilled_quantity + l_adjusted;
2802 end if;
2803 else
2804 l_source_type := 'NEWBUY';
2805 open c_newbuy(cr.inventory_item_id);
2806 fetch c_newbuy into l_source_organization_id,l_edq_quantity,l_related_item_id;
2807 close c_newbuy;
2808 l_avail_quantity := l_unfilled_quantity;
2809 end if;
2810 /*
2811 elsif p_source_type = 'REPAIR_FORECAST' then
2812 open c_repair_forecast(cr.inventory_item_id);
2813 fetch c_repair_forecast into l_source_organization_id,l_avail_quantity,l_order_edq,l_related_item_id;
2814 close c_repair_forecast;
2815 else
2816 open c_newbuy(cr.inventory_item_id);
2817 fetch c_newbuy into l_source_organization_id,l_order_edq,l_related_item_id;
2818 close c_newbuy;
2819 l_avail_quantity := l_unfilled_quantity;
2820 end if;
2821 */
2822 if (nvl(l_avail_quantity,0) <= 0 and p_source_type = l_source_type) or l_unfilled_quantity <= 0 then
2823 exit;
2824 else
2825 if l_source_type = 'NEWBUY' then
2826 if p_source_type = 'REPAIR_FORECAST' then
2827 l_unfilled_quantity := l_unfilled_quantity + l_adjusted;
2828 end if;
2829 if nvl(l_edq_quantity,0) = 0 or l_avail_quantity > nvl(l_edq_quantity,0) then
2830 l_order_quantity := l_avail_quantity;
2831 if l_order_quantity <= 0 and p_source_type <> 'REPAIR_FORECAST' then exit; end if;
2832 else
2833 l_order_quantity := ceil(l_avail_quantity/nvl(l_edq_quantity,1))*nvl(l_edq_quantity,1);
2834 if l_order_quantity <= 0 and p_source_type <> 'REPAIR_FORECAST' then exit; end if;
2835 end if;
2836 elsif l_source_type = 'REPAIR_FORECAST' then
2837 l_order_quantity := least(l_avail_quantity,l_unfilled_quantity);
2838 l_unfilled_quantity := l_order_quantity;
2839 else
2840 if nvl(l_edq_quantity,0) = 0 then
2841 l_order_quantity := least(l_avail_quantity,l_unfilled_quantity);
2842 else
2843 if round(l_avail_quantity/l_edq_quantity) > 0 then
2844 l_edq_quantity := l_avail_quantity/round(l_avail_quantity/l_edq_quantity);
2845 else
2846 l_edq_quantity := l_avail_quantity;
2847 end if;
2848 l_order_quantity := least(l_avail_quantity,
2849 ceil(l_unfilled_quantity/nvl(l_edq_quantity,1))*nvl(l_edq_quantity,1));
2850 end if;
2851 end if;
2852 if l_order_quantity > 0 then
2853 insert into csp_plan_details(
2854 plan_detail_id,
2855 plan_detail_type,
2856 parent_type,
2857 source_number,
2858 source_organization_id,
2859 quantity,
2860 plan_date,
2861 inventory_item_id,
2862 organization_id,
2863 related_item_id,
2864 created_by,
2865 creation_date,
2866 last_updated_by,
2867 last_update_date,
2868 last_update_login)
2869 select csp_plan_details_s1.nextval,
2870 decode(l_source_type,'EXCESS','4110',
2871 'REPAIR','4210',
2872 'NEWBUY','4310',
2873 'REPAIR_FORECAST','4220'),
2874 decode(l_source_type,'EXCESS','4100',
2875 'REPAIR','4200',
2876 'NEWBUY','4300',
2877 'REPAIR_FORECAST','1'),
2878 null,
2879 l_source_organization_id,
2880 l_order_quantity,
2881 --heh greatest(decode(l_source_type,'REPAIR_FORECAST',trunc(l_plan_date),
2882 -- trunc(l_plan_date+(l_period_size-(l_unfilled_quantity/cpd.quantity)*l_period_size))),
2883 -- trunc(sysdate+1)),
2884 greatest(trunc(l_plan_date),trunc(sysdate+1)),
2885 cr.inventory_item_id,
2886 l_organization_id,
2887 l_related_item_id,
2888 fnd_global.user_id,
2889 sysdate,
2890 fnd_global.user_id,
2891 sysdate,
2892 fnd_global.login_id
2893 from csp_plan_details cpd
2894 where cpd.organization_id = l_organization_id
2895 and cpd.inventory_item_id = cr.inventory_item_id
2896 and cpd.plan_detail_type = '1000'
2897 and cpd.plan_date = l_plan_date;
2898
2899 update csp_plan_details
2900 set quantity = quantity - l_order_quantity
2901 where organization_id = l_organization_id
2902 and inventory_item_id = cr.inventory_item_id
2903 and plan_date >= l_plan_date
2904 and plan_detail_type = '9004';
2905 end if;
2906 if p_source_type = 'REPAIR_FORECAST' then
2907 update csp_plan_details
2908 set quantity = 0
2909 where organization_id = l_organization_id
2910 and inventory_item_id = cr.inventory_item_id
2911 and plan_date = l_plan_date
2912 and plan_detail_type = '9004';
2913 end if;
2914 end if;
2915 end loop;
2916 if c_unfilled_requirements%isopen then
2917 close c_unfilled_requirements;
2918 end if;
2919 end loop;
2920
2921 delete from csp_plan_details
2922 where organization_id = l_organization_id
2923 and plan_detail_type = '9004';
2924 end planned_orders;
2925
2926 procedure total_planned_orders is
2927 begin
2928
2929 insert into csp_plan_details(
2930 plan_detail_type,
2931 parent_type,
2932 source_number,
2933 source_organization_id,
2934 quantity,
2935 plan_date,
2936 inventory_item_id,
2937 organization_id,
2938 created_by,
2939 creation_date,
2940 last_updated_by,
2941 last_update_date,
2942 last_update_login)
2943 select decode(cpd.plan_detail_type,'4110','4100','4210','4200','4310','4300'),
2944 min('4000'),
2945 null,
2946 null,
2947 sum(cpd.quantity),
2948 trunc(plan_date),--trunc(trunc(sysdate) + (trunc((plan_date - trunc(sysdate))/l_period_size))*l_period_size),
2949 cpd.inventory_item_id,
2950 cpd.organization_id,
2951 fnd_global.user_id,
2952 sysdate,
2953 fnd_global.user_id,
2954 sysdate,
2955 fnd_global.login_id
2956 from csp_plan_details cpd
2957 where cpd.plan_detail_type in ('4110','4210','4310')
2958 and cpd.organization_id = l_organization_id
2959 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2960 group by cpd.organization_id,cpd.inventory_item_id,trunc(plan_date),--hehtrunc(trunc(sysdate) + (trunc((plan_date - trunc(sysdate))/l_period_size))*l_period_size)
2961 cpd.plan_detail_type;
2962
2963 insert into csp_plan_details(
2964 plan_detail_type,
2965 parent_type,
2966 source_number,
2967 source_organization_id,
2968 quantity,
2969 plan_date,
2970 inventory_item_id,
2971 organization_id,
2972 created_by,
2973 creation_date,
2974 last_updated_by,
2975 last_update_date,
2976 last_update_login)
2977 select min('4000'),
2978 min('1'),
2979 null,
2980 null,
2981 sum(cpd.quantity),
2982 cpd.plan_date,
2983 cpd.inventory_item_id,
2984 cpd.organization_id,
2985 fnd_global.user_id,
2986 sysdate,
2987 fnd_global.user_id,
2988 sysdate,
2989 fnd_global.login_id
2990 from csp_plan_details cpd
2991 where cpd.plan_detail_type in ('4100','4200','4300')
2992 and cpd.organization_id = l_organization_id
2993 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
2994 group by cpd.organization_id,cpd.inventory_item_id,cpd.plan_detail_type,cpd.plan_date;
2995 end total_planned_orders;
2996
2997 procedure projected_onhand_1 is
2998 i number;
2999 begin
3000 for i in 0..l_forecast_periods loop
3001 insert into csp_plan_details(
3002 plan_detail_type,
3003 parent_type,
3004 source_number,
3005 source_organization_id,
3006 quantity,
3007 plan_date,
3008 inventory_item_id,
3009 organization_id,
3010 created_by,
3011 creation_date,
3012 last_updated_by,
3013 last_update_date,
3014 last_update_login)
3015 select min('3000'),
3016 min('1'),
3017 null,
3018 null,
3019 min(cpd2.available_quantity)+sum(decode(cpd.plan_detail_type,'1000',cpd.quantity*-1,cpd.quantity)),
3020 trunc(max(sysdate + (i-1)*l_period_size)),
3021 cpd.inventory_item_id,
3022 cpd.organization_id,
3023 fnd_global.user_id,
3024 sysdate,
3025 fnd_global.user_id,
3026 sysdate,
3027 fnd_global.login_id
3028 from csp_plan_details cpd,csp_plan_details cpd2
3029 where cpd.plan_detail_type in ('1000','2000')
3030 and cpd2.plan_detail_type = '1'
3031 and cpd.organization_id = cpd2.organization_id
3032 and cpd.inventory_item_id = cpd2.inventory_item_id
3033 and cpd.organization_id = l_organization_id
3034 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
3035 and cpd.plan_date < trunc(sysdate+i*l_period_size)
3036 group by cpd.organization_id,cpd.inventory_item_id;
3037 end loop;
3038
3039 end projected_onhand_1;
3040
3041 procedure projected_onhand_2 is
3042 begin
3043 insert into csp_plan_details(
3044 plan_detail_type,
3045 parent_type,
3046 source_number,
3047 source_organization_id,
3048 quantity,
3049 plan_date,
3050 inventory_item_id,
3051 organization_id,
3052 created_by,
3053 creation_date,
3054 last_updated_by,
3055 last_update_date,
3056 last_update_login)
3057 select min('5000'),
3058 min('1'),
3059 null,
3060 null,
3061 min(cpd.quantity)+
3062 sum(nvl(cpd2.quantity,0)),
3063 cpd.plan_date,
3064 cpd.inventory_item_id,
3065 cpd.organization_id,
3066 fnd_global.user_id,
3067 sysdate,
3068 fnd_global.user_id,
3069 sysdate,
3070 fnd_global.login_id
3071 from csp_plan_details cpd,
3072 csp_plan_details cpd2
3073 where cpd.plan_detail_type = '3000'
3074 and cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
3075 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
3076 and cpd2.organization_id = cpd.organization_id
3077 and cpd2.inventory_item_id = cpd.inventory_item_id
3078 and cpd2.plan_date <= cpd.plan_date+6
3079 and cpd2.plan_detail_type in ('4000','4220')
3080 group by cpd.organization_id,cpd.inventory_item_id,cpd.plan_date;
3081
3082 insert into csp_plan_details(
3083 plan_detail_type,
3084 parent_type,
3085 source_number,
3086 source_organization_id,
3087 quantity,
3088 plan_date,
3089 inventory_item_id,
3090 organization_id,
3091 created_by,
3092 creation_date,
3093 last_updated_by,
3094 last_update_date,
3095 last_update_login)
3096 select '5000',
3097 '1',
3098 null,
3099 null,
3100 cpd.quantity,
3101 cpd.plan_date,
3102 cpd.inventory_item_id,
3103 cpd.organization_id,
3104 fnd_global.user_id,
3105 sysdate,
3106 fnd_global.user_id,
3107 sysdate,
3108 fnd_global.login_id
3109 from csp_plan_details cpd
3110 where cpd.plan_detail_type = '3000'
3111 and cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
3112 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
3113 and cpd.plan_date not in
3114 (select cpd2.plan_date
3115 from csp_plan_details cpd2
3116 where cpd2.organization_id = cpd.organization_id
3117 and cpd2.inventory_item_id = cpd.inventory_item_id
3118 and cpd2.plan_detail_type = '5000');
3119
3120 end projected_onhand_2;
3121
3122 procedure onhand is
3123 begin
3124 insert into csp_plan_details(
3125 plan_detail_type,
3126 parent_type,
3127 source_number,
3128 source_organization_id,
3129 available_quantity,
3130 onhand_quantity,
3131 excess_quantity,
3132 quantity,
3133 plan_date,
3134 inventory_item_id,
3135 organization_id,
3136 period_size,
3137 forecast_periods,
3138 created_by,
3139 creation_date,
3140 last_updated_by,
3141 last_update_date,
3142 last_update_login)
3143 select '1',
3144 '0',
3145 null,
3146 null,
3147 nvl(csp_validate_pub.get_avail_qty(msib.organization_id,null,null,msib.inventory_item_id),0),
3148 nvl(csp_validate_pub.get_onhand_qty,0),
3149 greatest(0,nvl(csp_validate_pub.get_available_qty,0)
3150 - greatest(nvl(cpr.newbuy_rop,0)+nvl(cpr.newbuy_edq,0),
3151 nvl(cpr.repair_rop,0)+nvl(cpr.repair_edq,0),
3152 nvl(cpr.excess_rop,0)+nvl(cpr.excess_edq,0))),
3153 nvl(csp_validate_pub.get_onhand_qty,0),
3154 trunc(sysdate),
3155 msib.inventory_item_id,
3156 l_organization_id,
3157 l_orig_period_size,
3158 l_orig_forecast_periods,
3159 fnd_global.user_id,
3160 sysdate,
3161 fnd_global.user_id,
3162 sysdate,
3163 fnd_global.login_id
3164 from mtl_system_items_b msib,
3165 csp_plan_reorders cpr
3166 where msib.organization_id = l_organization_id
3167 and msib.inventory_item_id = nvl(l_inventory_item_id,msib.inventory_item_id)
3168 and cpr.organization_id(+) = msib.organization_id
3169 and cpr.inventory_item_id(+) = msib.inventory_item_id
3170 and msib.inventory_item_id in
3171 (select l_inventory_item_id
3172 from dual
3173 union
3174 select distinct cpd2.inventory_item_id
3175 from csp_plan_details cpd2
3176 where cpd2.plan_detail_type in ('1000','2000')
3177 and cpd2.organization_id = l_organization_id
3178 and cpd2.inventory_item_id = nvl(l_inventory_item_id,cpd2.inventory_item_id));
3179 end onhand;
3180
3181 procedure review_superseded_parts is
3182 begin
3183
3184 insert into csp_plan_details(
3185 plan_detail_type,
3186 parent_type,
3187 source_number,
3188 source_organization_id,
3189 quantity,
3190 plan_date,
3191 inventory_item_id,
3192 organization_id,
3193 created_by,
3194 creation_date,
3195 last_updated_by,
3196 last_update_date,
3197 last_update_login)
3198 select min('8510'),
3199 null,
3200 null,
3201 null,
3202 null,
3203 min(cpd.plan_date),
3204 cpd.inventory_item_id,
3205 cpd.organization_id,
3206 min(cpd.created_by),
3207 min(cpd.creation_date),
3208 min(cpd.last_updated_by),
3209 min(cpd.last_update_date),
3210 min(cpd.last_update_login)
3211 from csp_plan_details cpd,
3212 csp_supersede_items csi
3213 where cpd.plan_detail_type in ('1200','1300','1400','2000')
3214 and cpd.organization_id = l_organization_id
3215 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
3216 and csi.organization_id = cpd.organization_id
3217 and csi.sub_inventory_code = '-'
3218 and csi.inventory_item_id = cpd.inventory_item_id
3219 and csi.inventory_item_id <> csi.item_supplied
3220 group by cpd.organization_id,cpd.inventory_item_id;
3221
3222 -- Delete recommendations for superseeded parts
3223 delete from csp_plan_details cpd
3224 where (organization_id,inventory_item_id) in
3225 (select csi.organization_id,csi.inventory_item_id
3226 from csp_supersede_items csi
3227 where csi.inventory_item_id = cpd.inventory_item_id
3228 and csi.organization_id = cpd.organization_id
3229 and csi.sub_inventory_code = '-'
3230 and csi.item_supplied <> csi.inventory_item_id)
3231 and cpd.organization_id = l_organization_id
3232 and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
3233 and cpd.plan_detail_type in ('8110','8120','8130','8210','8220','8310','8410','8420');
3234 end review_superseded_parts;
3235 procedure regenerate(p_organization_id in number,
3236 p_inventory_item_id in number,
3237 p_forecast_rule_id in number,
3238 p_forecast_periods in number,
3239 p_period_size in number) is
3240 errbuf varchar2(2000);
3241 retcode number;
3242 begin
3243 main(errbuf => errbuf,
3244 retcode => retcode,
3245 p_organization_id => p_organization_id,
3246 p_inventory_item_id => p_inventory_item_id,
3247 p_forecast_rule_id => p_forecast_rule_id,
3248 p_forecast_periods => p_forecast_periods,
3249 p_period_size => p_period_size);
3250 end;
3251
3252 procedure create_plan_history(p_organization_id number,
3253 p_inventory_item_id number,
3254 p_history_type varchar2) is
3255 l_history_date date := sysdate;
3256 begin
3257
3258
3259 insert into csp_plan_histories(
3260 plan_detail_type,
3261 organization_id,
3262 inventory_item_id,
3263 parent_type,
3264 plan_date,
3265 source_number,
3266 source_organization_id,
3267 created_by,
3268 creation_date,
3269 last_updated_by,
3270 last_update_date,
3271 last_update_login,
3272 related_item_id,
3273 available_quantity,
3274 excess_quantity,
3275 onhand_quantity,
3276 quantity,
3277 security_group_id,
3278 plan_detail_id,
3279 period_size,
3280 forecast_periods,
3281 history_type,
3282 history_date)
3283 select plan_detail_type,
3284 organization_id,
3285 inventory_item_id,
3286 parent_type,
3287 plan_date,
3288 source_number,
3289 source_organization_id,
3290 created_by,
3291 creation_date,
3292 last_updated_by,
3293 last_update_date,
3294 last_update_login,
3295 related_item_id,
3296 available_quantity,
3297 excess_quantity,
3298 onhand_quantity,
3299 quantity,
3300 security_group_id,
3301 plan_detail_id,
3302 period_size,
3303 forecast_periods,
3304 p_history_type,
3305 l_history_date
3306 from csp_plan_details
3307 where organization_id = p_organization_id
3308 and inventory_item_id = nvl(p_inventory_item_id,inventory_item_id);
3309 insert into csp_pl_param_histories(
3310 organization_id,
3311 inventory_item_id,
3312 excess_service_level,
3313 repair_service_level,
3314 newbuy_service_level,
3315 excess_edq_factor,
3316 repair_edq_factor,
3317 newbuy_edq_factor,
3318 excess_edq_multiple,
3319 repair_edq_multiple,
3320 newbuy_edq_multiple,
3321 excess_rop,
3322 repair_rop,
3323 newbuy_rop,
3324 excess_safety_stock,
3325 repair_safety_stock,
3326 newbuy_safety_stock,
3327 excess_edq,
3328 repair_edq,
3329 newbuy_edq,
3330 excess_lead_time,
3331 repair_lead_time,
3332 newbuy_lead_time,
3333 history_type,
3334 history_date,
3335 created_by,
3336 creation_date,
3337 last_updated_by,
3338 last_update_date,
3339 last_update_login)
3340 select cpd.organization_id,
3341 cpd.inventory_item_id,
3342 nvl(cipp.excess_service_level,cpp.excess_service_level),
3343 nvl(cipp.repair_service_level,cpp.repair_service_level),
3344 nvl(cipp.newbuy_service_level,cpp.newbuy_service_level),
3345 nvl(cipp.excess_edq_factor,cpp.excess_edq_factor),
3346 nvl(cipp.repair_edq_factor,cpp.repair_edq_factor),
3347 nvl(cipp.newbuy_edq_factor,cpp.newbuy_edq_factor),
3348 nvl(cipp.excess_edq_multiple,cpp.edq_multiple),
3349 nvl(cipp.repair_edq_multiple,cpp.edq_multiple),
3350 nvl(cipp.newbuy_edq_multiple,cpp.edq_multiple),
3351 cpr.excess_rop,
3352 cpr.repair_rop,
3353 cpr.newbuy_rop,
3354 cpr.excess_safety_stock,
3355 cpr.repair_safety_stock,
3356 cpr.newbuy_safety_stock,
3357 cpr.excess_edq,
3358 cpr.repair_edq,
3359 cpr.newbuy_edq,
3360 cpl.excess_lead_time,
3361 cpl.repair_lead_time,
3362 cpl.newbuy_lead_time,
3363 p_history_type,
3364 l_history_date,
3365 fnd_global.user_id,
3366 sysdate,
3367 fnd_global.user_id,
3368 sysdate,
3369 fnd_global.login_id
3370 from csp_plan_details cpd,
3371 csp_planning_parameters cpp,
3372 csp_plan_reorders cpr,
3373 csp_plan_leadtimes cpl,
3374 csp_item_pl_params cipp
3375 where cpd.plan_detail_type = '1'
3376 and cpd.organization_id = p_organization_id
3377 and cpd.inventory_item_id = nvl(p_inventory_item_id,cpd.inventory_item_id)
3378 and cpp.organization_id = cpd.organization_id
3379 and cpr.organization_id(+) = cpd.organization_id
3380 and cpr.inventory_item_id(+) = cpd.inventory_item_id
3381 and cpl.organization_id(+) = cpd.organization_id
3382 and cpl.inventory_item_id(+) = cpd.inventory_item_id
3383 and cipp.organization_id(+) = cpd.organization_id
3384 and cipp.inventory_item_id(+) = cpd.inventory_item_id;
3385
3386 end;
3387
3388 procedure purge_saved_plans(p_days number) is
3389 begin
3390 delete from csp_plan_histories
3391 where history_date < sysdate - p_days
3392 and organization_id = nvl(l_organization_id,organization_id);
3393 end;
3394
3395 procedure copy_plan_history(p_organization_id number,
3396 p_inventory_item_id number,
3397 p_history_date date) is
3398 begin
3399
3400 delete from csp_plan_details
3401 where organization_id = p_organization_id
3402 and inventory_item_id = p_inventory_item_id;
3403
3404 insert into csp_plan_details(
3405 plan_detail_type,
3406 organization_id,
3407 inventory_item_id,
3408 parent_type,
3409 plan_date,
3410 source_number,
3411 source_organization_id,
3412 created_by,
3413 creation_date,
3414 last_updated_by,
3415 last_update_date,
3416 last_update_login,
3417 related_item_id,
3418 available_quantity,
3419 excess_quantity,
3420 onhand_quantity,
3421 quantity,
3422 security_group_id,
3423 plan_detail_id,
3424 period_size,
3425 forecast_periods)
3426 select plan_detail_type,
3427 organization_id,
3428 inventory_item_id,
3429 parent_type,
3430 plan_date,
3431 source_number,
3432 source_organization_id,
3433 created_by,
3434 creation_date,
3435 last_updated_by,
3436 last_update_date,
3437 last_update_login,
3438 related_item_id,
3439 available_quantity,
3440 excess_quantity,
3441 onhand_quantity,
3442 quantity,
3443 security_group_id,
3444 plan_detail_id,
3445 period_size,
3446 forecast_periods
3447 from csp_plan_histories
3448 where organization_id = p_organization_id
3449 and inventory_item_id = nvl(p_inventory_item_id,inventory_item_id)
3450 and history_date = p_history_date;
3451 end;
3452
3453 procedure current_onhand(
3454 p_organization_id in number default null,
3455 p_inventory_item_id in number default null) is
3456 cursor c_forecast_rules is
3457 select cfrb.forecast_periods,
3458 cfrb.period_size
3459 from csp_forecast_rules_b cfrb,
3460 csp_planning_parameters cpp
3461 where cfrb.forecast_rule_id = cpp.forecast_rule_id
3462 and cpp.organization_id = p_organization_id
3463 and cpp.organization_type = 'W';
3464
3465 begin
3466 open c_forecast_rules;
3467 fetch c_forecast_rules into l_orig_forecast_periods, l_orig_period_size;
3468 close c_forecast_rules;
3469 l_organization_id := p_organization_id;
3470 l_inventory_item_id := p_inventory_item_id;
3471 begin
3472 delete from csp_plan_details
3473 where organization_id = p_organization_id
3474 and inventory_item_id = p_inventory_item_id
3475 and plan_detail_type in ('1','9001','9002','9003');
3476 exception
3477 when others then
3478 null;
3479 end;
3480 onhand;
3481 repair;
3482 excess;
3483 commit;
3484 end current_onhand;
3485
3486 procedure main(errbuf out nocopy varchar2,
3487 retcode out nocopy number,
3488 p_organization_id in number,
3489 p_save_system_plan in varchar2,
3490 p_save_planner_plan in varchar2,
3491 p_purge_saved_plans in number,
3492 p_inventory_item_id in number,
3493 p_forecast_rule_id in number,
3494 p_forecast_periods in number,
3495 p_period_size in number) is
3496
3497 -- errbuf varchar2(2000);
3498 -- retcode number;
3499 cursor c_forecast_method is
3500 select cfrb.forecast_rule_id,
3501 cfrb.forecast_periods,
3502 cfrb.forecast_method,
3503 cfrb.history_periods,
3504 cfrb.period_size,
3505 cpp.organization_id,
3506 cpp.usable_assignment_set_id,
3507 cpp.defective_assignment_set_id,
3508 cpp.repair_assignment_set_id,
3509 cpp.edq_multiple,
3510 cpp.level_id,
3511 cpp.reschedule_rule_id,
3512 cpp.minimum_value
3513 from csp_forecast_rules_b cfrb,
3514 csp_planning_parameters cpp
3515 where cfrb.forecast_rule_id = cpp.forecast_rule_id
3516 and cpp.organization_id = nvl(l_organization_id,cpp.organization_id)
3517 and cpp.organization_type = 'W';
3518
3519 cursor c_reschedule is
3520 select onhand_type_in,
3521 start_day_in,
3522 end_day_in,
3523 onhand_condition_in,
3524 periods_in,
3525 onhand_type_out,
3526 start_day_out,
3527 end_day_out,
3528 onhand_value_out,
3529 edq_multiple_out,
3530 periods_out
3531 from csp_reschedule_rules_vl
3532 where reschedule_rule_id = l_reschedule_rule_id;
3533
3534 cursor c_order_automation(p_organization_id number) is
3535 select inventory_item_id
3536 from csp_plan_details
3537 where organization_id = p_organization_id
3538 and plan_detail_type in ('8610','8620','8630');
3539
3540 begin
3541 l_organization_id := p_organization_id;
3542 l_inventory_item_id := p_inventory_item_id;
3543 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
3544 if p_save_planner_plan = '1' then
3545 create_plan_history(p_organization_id => l_organization_id,
3546 p_inventory_item_id => null,
3547 p_history_type => 'PLANNER');
3548 end if;
3549 if nvl(p_purge_saved_plans,0) > 0 then
3550 purge_saved_plans(p_days => p_purge_saved_plans);
3551 end if;
3552
3553 if l_inventory_item_id is null then
3554 delete from csp_plan_details
3555 where organization_id = nvl(l_organization_id,organization_id)
3556 and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id);
3557 commit;
3558 else
3559 delete from csp_plan_details
3560 where organization_id = nvl(l_organization_id,organization_id)
3561 and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
3562 and plan_detail_type not in ('8610','8620','8630');
3563 commit;
3564 end if;
3565
3566 for cr in c_forecast_method loop
3567 l_period_size := nvl(p_period_size,cr.period_size);
3568 l_orig_period_size := l_period_size;
3569 l_forecast_periods := nvl(p_forecast_periods,cr.forecast_periods);
3570 l_orig_forecast_periods := l_forecast_periods;
3571
3572 l_organization_id := cr.organization_id;
3573 l_forecast_rule_id := cr.forecast_rule_id;
3574 l_forecast_period_size := cr.period_size;
3575 l_forecast_method := cr.forecast_method;
3576 l_history_periods := cr.history_periods;
3577 l_usable_assignment_set_id := cr.usable_assignment_set_id;
3578 l_defective_assignment_set_id := cr.defective_assignment_set_id;
3579 l_repair_assignment_set_id := cr.repair_assignment_set_id;
3580 l_edq_multiple := cr.edq_multiple;
3581 l_reschedule_rule_id := cr.reschedule_rule_id;
3582 l_minimum_value := cr.minimum_value;
3583
3584 if l_period_size > 7 then
3585 l_forecast_periods := round(l_forecast_periods * l_period_size / 7);
3586 l_period_size := 7;
3587 end if;
3588
3589 if l_reschedule_rule_id is not null then
3590 open c_reschedule;
3591 fetch c_reschedule into
3592 l_onhand_type_in,
3593 l_start_day_in,
3594 l_end_day_in,
3595 l_onhand_condition_in,
3596 l_periods_in,
3597 l_onhand_type_out,
3598 l_start_day_out,
3599 l_end_day_out,
3600 l_onhand_value_out,
3601 l_edq_multiple_out,
3602 l_periods_out;
3603 close c_reschedule;
3604 end if;
3605 if nvl(l_period_size,0) <> 0 then
3606 if l_inventory_item_id is null then
3607
3608 csp_auto_aslmsl_pvt.Generate_Recommendations(retcode,errbuf,2.0,cr.level_id);
3609 forecast;commit;
3610 orders;commit;
3611 supply;commit;
3612 total_requirement;commit;
3613 total_on_order;commit;
3614 onhand;commit;
3615 leadtimes;commit;
3616 reorders(l_organization_id,l_inventory_item_id);commit;
3617 else
3618 forecast;commit;
3619 orders;commit;
3620 supply;commit;
3621 total_requirement;commit;
3622 total_on_order;commit;
3623 onhand;commit;
3624 end if;
3625 excess;commit;
3626 repair;commit;
3627 projected_onhand_1;commit;
3628 if cr.usable_assignment_set_id is not null then
3629 unfilled_requirement('EXCESS');commit;
3630 planned_orders('EXCESS');commit;
3631 end if;
3632
3633
3634 if cr.defective_assignment_set_id is not null and
3635 cr.repair_assignment_set_id is not null then
3636
3637 unfilled_requirement('REPAIR');commit;
3638 planned_orders('REPAIR');commit;
3639 return_history;commit;
3640 return_forecast;commit;
3641 unfilled_requirement('REPAIR_FORECAST');commit;
3642 planned_orders('REPAIR_FORECAST');commit;
3643
3644 end if;
3645
3646 unfilled_requirement('NEWBUY');commit;
3647
3648 planned_orders('NEWBUY');commit;
3649 total_planned_orders;commit;
3650 projected_onhand_2;commit;
3651
3652 -- Exceptions
3653 newbuy_excess_onorder;commit;
3654 repair_excess_onorder;commit;
3655 excess_excess_onorder;commit;
3656 unutilized_excess;commit;
3657 unutilized_repair;commit;
3658 reschedule_in;commit;
3659 reschedule_out;commit;
3660 review_superseded_parts;commit;
3661 -- Clean up exceptions
3662 delete from csp_plan_details
3663 where plan_detail_type in ('8110','8120','8130','8210',
3664 '8220','8310','8410','8420')
3665 and organization_id = l_organization_id
3666 and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
3667 and quantity = 0;
3668 commit;
3669
3670 -- Order Automation
3671 if l_inventory_item_id is null then
3672 order_automation;commit;--hehxx added commit
3673 for coa in c_order_automation(cr.organization_id) loop
3674 regenerate( p_organization_id => cr.organization_id,
3675 p_inventory_item_id => coa.inventory_item_id,
3676 p_forecast_rule_id => cr.forecast_rule_id,
3677 p_forecast_periods => cr.forecast_periods,
3678 p_period_size => cr.period_size);
3679 end loop;
3680 end if;
3681
3682 if p_save_system_plan = '1' then
3683 create_plan_history(p_organization_id => l_organization_id,
3684 p_inventory_item_id => null,
3685 p_history_type => 'SYSTEM');commit;
3686 end if;
3687 end if;
3688 end loop;
3689 retcode := g_retcode;
3690 end main;
3691 end;