[Home] [Help]
PACKAGE BODY: APPS.MSC_UI_PEG_UTIL
Source
1 PACKAGE BODY MSC_UI_PEG_UTIL AS
2 /* $Header: MSCPEGUB.pls 120.5 2007/12/06 01:34:59 cnazarma ship $ */
3
4 procedure get_disposition_id(p_demand_id IN NUMBER,
5 x_disposition_id OUT NOCOPY NUMBER,
6 x_origination_type OUT NOCOPY NUMBER,
7 p_sr_instance_id IN NUMBER,
8 p_organization_id IN NUMBER,
9 p_plan_id IN NUMBER) IS
10
11 cursor origination_22 is
12 select origination_type, disposition_id
13 from msc_demands
14 where demand_id = p_demand_id
15 and plan_id = p_plan_id
16 and organization_id = p_organization_id
17 and sr_instance_id = p_sr_instance_id;
18
19
20 begin
21
22 open origination_22;
23 fetch origination_22 into x_origination_type, x_disposition_id;
24 close origination_22;
25
26 exception when others THEN
27 raise;
28 end get_disposition_id;
29
30 Procedure get_suptree_sup_values(p_plan_id IN NUMBER,
31 p_demand_id IN NUMBER,
32 p_sr_instance_id IN NUMBER,
33 p_organization_id IN NUMBER,
34 p_prev_peg_id IN NUMBER,
35 x_itemorg_pegnode_rec OUT NOCOPY MSC_UI_PEG_UTIL.peg_node_rec_values_table,
36 p_supply_pegging IN NUMBER DEFAULT 0 , -- demand pegging ( peg up)
37 p_show_item_desc IN NUMBER DEFAULT 2)
38 IS
39 l_disposition_id NUMBER;
40 l_origination_type NUMBER;
41 l_prev_peg_id NUMBER;
42 i NUMBER;
43
44 cursor cur1(p_disposition_id_1 number , l_show_item_desc number) is
45 select decode(l_show_item_desc, 1, substrb(nvl(mis.description,mis.item_name),1,80)||'/'||msc_get_name.org_code(ms.organization_id,ms.sr_instance_id)
46 ,substrb(mis.item_name,1,80)||'/'||msc_get_name.org_code(ms.organization_id,ms.sr_instance_id) ) item_org,
47 ms.new_order_quantity supply_qty,
48 ms.new_schedule_date supply_date,
49 msc_get_name.lookup_meaning( 'MRP_ORDER_TYPE',
50 ms.order_type) order_name,
51 null pegging_id,
52 null prev_pegging_id,
53 ms.transaction_id,
54 p_demand_id demand_id,
55 ms.new_order_quantity pegged_qty,
56 ms.inventory_item_id,
57 ms.order_type,
58 null disposition
59 from msc_system_items mis,
60 msc_supplies ms
61 where mis.inventory_item_id = ms.inventory_item_id
62 and mis.sr_instance_id = ms.sr_instance_id
63 and mis.organization_id = ms.organization_id
64 and mis.plan_id = ms.plan_id
65 and ms.transaction_id = p_disposition_id_1
66 and ms.plan_id = p_plan_id
67 and ms.organization_id = p_organization_id
68 and ms.sr_instance_id = p_sr_instance_id;
69 --5523978 bugfix, msc_items changed to msc_system_items
70
71 cursor cur2(l_show_item_desc number) is
72 select distinct
73 decode(l_show_item_desc ,1,item_desc_org,item_org) item_org,
74 supply_qty,
75 supply_date,
76 order_name,
77 pegging_id,
78 prev_pegging_id,
79 transaction_id,
80 demand_id,
81 pegged_qty,
82 inventory_item_id,
83 order_type,
84 disposition
85 from msc_flp_demand_supply_v3
86 where plan_id = p_plan_id
87 and pegging_id = p_prev_peg_id
88 order by item_org, supply_date;
89
90 cursor cur3(l_prev_peg_id_1 number , l_show_item_desc number) is
91 select distinct decode(l_show_item_desc ,1,item_desc_org,item_org) item_org ,
92 supply_qty,
93 supply_date,
94 order_name,
95 pegging_id,
96 prev_pegging_id,
97 transaction_id,
98 demand_id,
99 pegged_qty,
100 inventory_item_id,
101 order_type,
102 disposition
103 from msc_flp_demand_supply_v3
104 where plan_id = p_plan_id
105 and prev_pegging_id = l_prev_peg_id_1
106 and demand_id = p_demand_id
107 and order_type not in (15,16,28)
108 order by item_org, supply_date;
109
110 BEGIN
111 get_disposition_id(p_demand_id,
112 l_disposition_id,
113 l_origination_type,
114 p_sr_instance_id,
115 p_organization_id,
116 p_plan_id);
117 -- this case is for Demand Pegging ( peg up from children to parents)
118 IF p_supply_pegging = 0 THEN -- Demand Pegging ( peg up)
119 -- This case is when you are on Production Forecast and you expand a Demand Tree meaning
120 -- u are pegging up so the next node should be Product Family Planned Order
121 IF l_origination_type = 22 THEN
122 i := 1;
123 open cur1(l_disposition_id , p_show_item_desc);
124 loop
125 exit when cur1%notfound;
126 fetch cur1 into
127 x_itemorg_pegnode_rec(i).Item_Org,
128 x_itemorg_pegnode_rec(i).Qty,
129 x_itemorg_pegnode_rec(i).Peg_Date,
130 x_itemorg_pegnode_rec(i).Order_name,
131 x_itemorg_pegnode_rec(i).Pegging_id,
132 x_itemorg_pegnode_rec(i).Prev_pegging_id,
133 x_itemorg_pegnode_rec(i).Transaction_id,
134 x_itemorg_pegnode_rec(i).Demand_id,
135 x_itemorg_pegnode_rec(i).Pegged_qty,
136 x_itemorg_pegnode_rec(i).Item_id,
137 x_itemorg_pegnode_rec(i).Order_type,
138 x_itemorg_pegnode_rec(i).Disposition;
139 i := i + 1;
140 end loop;
141 close cur1;
142 i := 0;
143 ELSE
144 i := 1;
145 open cur2(p_show_item_desc);
146 loop
147 exit when cur2%notfound;
148 fetch cur2 into
149 x_itemorg_pegnode_rec(i).Item_Org,
150 x_itemorg_pegnode_rec(i).Qty,
151 x_itemorg_pegnode_rec(i).Peg_Date,
152 x_itemorg_pegnode_rec(i).Order_name,
153 x_itemorg_pegnode_rec(i).Pegging_id,
154 x_itemorg_pegnode_rec(i).Prev_pegging_id,
155 x_itemorg_pegnode_rec(i).Transaction_id,
156 x_itemorg_pegnode_rec(i).Demand_id,
157 x_itemorg_pegnode_rec(i).Pegged_qty,
158 x_itemorg_pegnode_rec(i).Item_id,
159 x_itemorg_pegnode_rec(i).Order_type,
160 x_itemorg_pegnode_rec(i).Disposition;
161 i := i + 1;
162 end loop;
163 close cur2;
164 i := 0;
165 END IF;
166
167 -- This case if for Supply Pegging ( peg down from demand node of parent item to supply node of child)
168 ELSIF p_supply_pegging = 1 THEN
169 -- This case if when you are on Production Forecast Node and it is Supply Pegging
170 -- so the next node should be Planned order of the Member item
171 IF l_origination_type = 22 and p_prev_peg_id is NULL THEN
172 l_prev_peg_id := -1 ;
173 ELSE
174 l_prev_peg_id := p_prev_peg_id;
175 END IF;
176 i := 1;
177 open cur3(l_prev_peg_id , p_show_item_desc);
178 loop
179 exit when cur3%notfound;
180 fetch cur3 into
181 x_itemorg_pegnode_rec(i).Item_Org,
182 x_itemorg_pegnode_rec(i).Qty,
183 x_itemorg_pegnode_rec(i).Peg_Date,
184 x_itemorg_pegnode_rec(i).Order_name,
185 x_itemorg_pegnode_rec(i).Pegging_id,
186 x_itemorg_pegnode_rec(i).Prev_pegging_id,
187 x_itemorg_pegnode_rec(i).Transaction_id,
188 x_itemorg_pegnode_rec(i).Demand_id,
189 x_itemorg_pegnode_rec(i).Pegged_qty,
190 x_itemorg_pegnode_rec(i).Item_id,
191 x_itemorg_pegnode_rec(i).Order_type,
192 x_itemorg_pegnode_rec(i).Disposition;
193 i := i + 1;
194 end loop;
195 close cur3;
196 i := 0;
197 END IF;
198
199 END get_suptree_sup_values;
200
201 Procedure get_suptree_dem_values(p_plan_id IN NUMBER,
202 p_transaction_id IN NUMBER,
203 x_itemorg_pegnode_rec OUT NOCOPY MSC_UI_PEG_UTIL.peg_node_rec_values_table,
204 p_item_id IN NUMBER,
205 p_pegging_id IN NUMBER,
206 p_instance_id IN NUMBER,
207 p_trigger_node_type IN NUMBER DEFAULT 2,
208 p_condense_supply_oper IN NUMBER DEFAULT 0,
209 p_hide_oper IN NUMBER DEFAULT 0,
210 p_organization_id IN NUMBER DEFAULT NULL,
211 p_supply_pegging IN NUMBER DEFAULT 0 ,
212 p_show_item_desc IN NUMBER DEFAULT 2) IS
213
214 cursor bom_item_type (p_inventory_item_id in NUMBER) IS
215 select bom_item_type
216 from msc_system_items
217 where inventory_item_id = p_inventory_item_id
218 and plan_id = p_plan_id
219 and sr_instance_id = p_instance_id;
220
221 -- Sometimes after button Supply Pegging is clicked pld is not sending item_id
222 cursor inventory_item_id IS
223 select inventory_item_id,
224 decode(order_type, 13, disposition_id, transaction_id)
225 from msc_supplies
226 where transaction_id = p_transaction_id
227 and plan_id = p_plan_id
228 and sr_instance_id = p_instance_id
229 and organization_id = p_organization_id;
230
231 l_bom_item_type NUMBER;
232 PARENT NUMBER :=1 ;
233 l_item_id NUMBER;
234 i NUMBER;
235 l_transaction_id NUMBER;
236
237 cursor cur1(l_show_item_desc number) is
238 select decode(l_show_item_desc, 1, item_desc_org, item_org) item_org,
239 demand_qty,
240 demand_date,
241 origination_name,
242 prev_pegging_id,
243 demand_id,
244 sum(pegged_qty) pegged_qty,
245 item_id,
246 order_type,
247 end_disposition order_number,
248 null pegging_id ,
249 null transaction_id,
250 null end_demand_class
251 from msc_flp_supply_demand_v3
252 where plan_id = p_plan_id
253 and prev_pegging_id in ( select pegging_id
254 from msc_full_pegging
255 where plan_id = p_plan_id
256 and transaction_id = l_transaction_id
257 and sr_instance_id = p_instance_id
258 and allocated_quantity > 0) ------ = p_pegging_id
259 and item_id not in (SELECT msi.inventory_item_id
260 FROM msc_resource_requirements req,
261 msc_routings rout,
262 msc_routing_operations op,
263 msc_operation_components moc,
264 msc_bom_components mbc,
265 msc_system_items msi
266 WHERE req.plan_id = rout.plan_id
267 AND req.sr_instance_id = rout.sr_instance_id
268 AND nvl(req.routing_sequence_id,-23453) = decode(nvl(req.routing_sequence_id,-23453), -23453,-23453, rout.routing_sequence_id)
269 AND req.plan_id = op.plan_id
270 AND req.sr_instance_id = op.sr_instance_id
271 AND nvl(req.routing_sequence_id,-23453) = decode(nvl(req.routing_sequence_id,-23453), -23453, -23453, op.routing_sequence_id)
272 AND req.operation_sequence_id = op.operation_sequence_id
273 AND nvl(req.parent_id,2) = 2
274 and moc.plan_id = req.plan_id
275 and moc.sr_instance_id = req.sr_instance_id
276 and moc.operation_sequence_id = req.operation_sequence_id
277 and nvl(req.routing_sequence_id,-23453) = decode(nvl(req.routing_sequence_id,-23453), -23453, -23453, moc.routing_sequence_id)
278 and moc.plan_id = mbc.plan_id
279 and moc.sr_instance_id = mbc.sr_instance_id
280 and moc.organization_id = mbc.organization_id
281 and moc.component_sequence_id = mbc.component_sequence_id
282 and moc.bill_sequence_id = mbc.bill_sequence_id
283 and mbc.plan_id = msi.plan_id
284 and mbc.sr_instance_id = msi.sr_instance_id
285 and mbc.organization_id = msi.organization_id
286 and mbc.inventory_item_id = msi.inventory_item_id
287 and req.plan_id = p_plan_id
288 and req.sr_instance_id = p_instance_id
289 and req.organization_id = p_organization_id
290 and req.supply_id = p_transaction_id
291 and 0 = nvl(p_condense_supply_oper,0)
292 and 0 = nvl(p_hide_oper,0))
293 group by demand_qty, demand_date,
294 origination_name, prev_pegging_id, demand_id,
295 item_id, item_org,item_desc_org, order_type, end_disposition
296 order by item_org, demand_date;
297
298 cursor cur2(l_show_item_desc number) is
299 select decode(l_show_item_desc, 1, substrb(nvl(mis.description,mis.item_name),1,80)||'/'||msc_get_name.org_code(dm.organization_id,dm.sr_instance_id)
300 ,substrb(mis.item_name,1,80)||'/'||msc_get_name.org_code(dm.organization_id,dm.sr_instance_id) ) item_org,
301 dm.using_requirement_quantity demand_qty,
302 dm.using_assembly_demand_date demand_date,
303 msc_get_name.lookup_meaning( 'MSC_DEMAND_ORIGINATION',
304 dm.origination_type) origination_name,
305 null prev_pegging_id,
306 dm.demand_id demand_id,
307 dm.using_requirement_quantity pegged_qty,
308 mis.inventory_item_id item_id,
309 dm.origination_type,
310 null order_number,
311 null pegging_id,
312 null transaction_id,
313 null end_demand_class
314 from msc_system_items mis,
315 msc_demands dm
316 where mis.inventory_item_id = dm.inventory_item_id
317 and mis.sr_instance_id = dm.sr_instance_id
318 and mis.organization_id = dm.organization_id
319 and mis.plan_id = dm.plan_id
320 and dm.disposition_id = p_transaction_id
321 and dm.plan_id = p_plan_id
322 and dm.organization_id = p_organization_id
323 and dm.sr_instance_id = p_instance_id;
324
325 cursor cur3(l_show_item_desc number) is
326 select distinct
327 decode(l_show_item_desc, 1, item_desc_org, item_org) item_org,
328 pegging_id,
329 prev_pegging_id,
330 demand_qty,
331 demand_date,
332 origination_name,
333 demand_id,
334 transaction_id,
335 item_id,
336 pegged_qty,
337 end_disposition order_number,
338 order_type,
339 end_demand_class
340 from msc_flp_supply_demand_v3
341 where plan_id = p_plan_id
342 and transaction_id = p_transaction_id
343 order by item_org, demand_date;
344
345 cursor cur4(l_show_item_desc number) is
346 select distinct
347 decode(l_show_item_desc, 1, item_desc_org, item_org) item_org,
348 pegging_id,
349 prev_pegging_id,
350 demand_qty,
351 demand_date,
352 origination_name,
353 demand_id,
354 transaction_id,
355 item_id,
356 pegged_qty,
357 end_disposition order_number,
358 order_type,
359 end_demand_class
360 from msc_flp_supply_demand_v3
361 where plan_id = p_plan_id
362 and pegging_id = p_pegging_id
363 order by item_org, demand_date;
364
365
366 BEGIN
367 l_transaction_id := p_transaction_id;
368 if p_item_id is NULL then
369 open inventory_item_id;
370 fetch inventory_item_id INTO l_item_id, l_transaction_id;
371 close inventory_item_id;
372 else
373 l_item_id := p_item_id;
374 end if;
375
376 open bom_item_type(l_item_id);
377 fetch bom_item_type INTO l_bom_item_type;
378 close bom_item_type;
379
380 -- A user is on Supply Tree and button Supply Pegging has been used
381 IF p_supply_pegging = 1 and l_bom_item_type <> 5 then
382 i := 1;
383 open cur1(p_show_item_desc);
384 loop
385 exit when cur1%notfound;
386 fetch cur1 into
387 x_itemorg_pegnode_rec(i).Item_Org,
388 x_itemorg_pegnode_rec(i).Qty,
389 x_itemorg_pegnode_rec(i).Peg_date,
390 x_itemorg_pegnode_rec(i).Order_name,
391 x_itemorg_pegnode_rec(i).Prev_pegging_id,
392 x_itemorg_pegnode_rec(i).Demand_id,
393 x_itemorg_pegnode_rec(i).Pegged_qty,
394 x_itemorg_pegnode_rec(i).Item_id,
395 x_itemorg_pegnode_rec(i).order_type,
396 x_itemorg_pegnode_rec(i).order_number,
397 x_itemorg_pegnode_rec(i).Pegging_id,
398 x_itemorg_pegnode_rec(i).Transaction_id,
399 x_itemorg_pegnode_rec(i).end_demand_class;
400 i := i + 1;
401 end loop;
402 close cur1;
403 i := 0;
404
405 ELSIF p_supply_pegging = 1 and l_bom_item_type = 5 then
406 -- this case is for Product Family , we need to display the Product Forecast nodes when node is expanded
407
408 i := 1;
409 open cur2(p_show_item_desc);
410 loop
411 exit when cur2%notfound;
412 fetch cur2 into
413 x_itemorg_pegnode_rec(i).Item_Org,
414 x_itemorg_pegnode_rec(i).Qty,
415 x_itemorg_pegnode_rec(i).Peg_date,
419 x_itemorg_pegnode_rec(i).Pegged_qty,
416 x_itemorg_pegnode_rec(i).Order_name,
417 x_itemorg_pegnode_rec(i).Prev_pegging_id,
418 x_itemorg_pegnode_rec(i).Demand_id,
420 x_itemorg_pegnode_rec(i).Item_id,
421 x_itemorg_pegnode_rec(i).order_type,
422 x_itemorg_pegnode_rec(i).order_number,
423 x_itemorg_pegnode_rec(i).Pegging_id,
424 x_itemorg_pegnode_rec(i).Transaction_id,
425 x_itemorg_pegnode_rec(i).end_demand_class;
426 i := i + 1;
427 end loop;
428 close cur2;
429 i := 0;
430 ELSIF p_supply_pegging <> 1 then
431
432 IF l_bom_item_type = 5 OR p_trigger_node_type = PARENT THEN
433
434 i := 1;
435 open cur3(p_show_item_desc);
436 loop
437 exit when cur3%notfound;
438 fetch cur3 into
439 x_itemorg_pegnode_rec(i).Item_Org,
440 x_itemorg_pegnode_rec(i).Pegging_id,
441 x_itemorg_pegnode_rec(i).Prev_pegging_id,
442 x_itemorg_pegnode_rec(i).Qty,
443 x_itemorg_pegnode_rec(i).Peg_Date,
444 x_itemorg_pegnode_rec(i).Order_name,
445 x_itemorg_pegnode_rec(i).demand_id,
446 x_itemorg_pegnode_rec(i).transaction_id,
447 x_itemorg_pegnode_rec(i).item_id,
448 x_itemorg_pegnode_rec(i).Pegged_qty,
449 x_itemorg_pegnode_rec(i).order_number,
450 x_itemorg_pegnode_rec(i).order_type,
451 x_itemorg_pegnode_rec(i).end_demand_class;
452 i := i + 1;
453 end loop;
454 close cur3;
455 i := 0;
456
457 ELSE
458
459 i := 1;
460 open cur4(p_show_item_desc);
461 loop
462 exit when cur4%notfound;
463 fetch cur4 into
464 x_itemorg_pegnode_rec(i).Item_Org,
465 x_itemorg_pegnode_rec(i).Pegging_id,
466 x_itemorg_pegnode_rec(i).Prev_pegging_id,
467 x_itemorg_pegnode_rec(i).Qty,
468 x_itemorg_pegnode_rec(i).Peg_date,
469 x_itemorg_pegnode_rec(i).Order_name,
470 x_itemorg_pegnode_rec(i).Demand_id,
471 x_itemorg_pegnode_rec(i).transaction_id,
472 x_itemorg_pegnode_rec(i).item_id,
473 x_itemorg_pegnode_rec(i).Pegged_qty,
474 x_itemorg_pegnode_rec(i).order_number,
475 x_itemorg_pegnode_rec(i).order_type,
476 x_itemorg_pegnode_rec(i).end_demand_class;
477 i := i + 1;
478 end loop;
479 close cur4;
480 i := 0;
481 END IF;
482 end if;
483
484 EXCEPTION WHEN others THEN
485 raise;
486 END get_suptree_dem_values;
487
488
489 Procedure get_suptree_dem_values_rep(p_plan_id IN NUMBER,
490 p_transaction_id IN NUMBER,
491 x_itemorg_pegnode_rec OUT NOCOPY MSC_UI_PEG_UTIL.peg_node_rec_values_table,
492 p_instance_id IN NUMBER,
493 p_supply_pegging IN NUMBER DEFAULT 0 ,
494 p_show_item_desc IN NUMBER DEFAULT 2,
495 p_show_ss_demands IN NUMBER DEFAULT 1) IS
496 cursor cur1 is
497 select decode(p_show_item_desc, 1, item_desc_org, item_org) item_org,
498 demand_qty,
499 demand_date,
500 origination_name,
501 prev_pegging_id,
502 demand_id,
503 sum(pegged_qty) pegged_qty,
504 item_id,
505 order_type,
506 end_disposition order_number,
507 null pegging_id ,
508 null transaction_id,
509 null end_demand_class
510 from msc_flp_supply_demand_v3
511 where plan_id = p_plan_id
512 and prev_pegging_id in ( select mfp.pegging_id
513 from msc_full_pegging mfp,
514 msc_supplies ms
515 where ms.plan_id = p_plan_id
516 and ms.transaction_id = p_transaction_id
517 and ms.sr_instance_id = p_instance_id
518 and mfp.plan_id = ms.plan_id
519 and mfp.transaction_id = ms.disposition_id
520 and mfp.allocated_quantity > 0 )
521 group by demand_qty, demand_date,
522 origination_name, prev_pegging_id, demand_id,
523 item_id, item_org,item_desc_org, order_type, end_disposition
524 order by item_org, demand_date;
525
526 cursor cur3 is
527 select distinct
528 decode(p_show_item_desc, 1, item_desc_org, item_org) item_org,
529 pegging_id,
530 prev_pegging_id,
531 demand_qty,
532 demand_date,
533 origination_name,
534 demand_id,
535 transaction_id,
536 item_id,
537 pegged_qty,
538 end_disposition order_number,
539 order_type,
540 end_demand_class
541 from msc_flp_supply_demand_rep_v
542 where plan_id = p_plan_id
543 and transaction_id = p_transaction_id
544 order by item_org, demand_date;
545
546 i number;
547 BEGIN
548 i := 1;
549 IF p_supply_pegging = 1 then
550 open cur1;
551 loop
552 exit when cur1%notfound;
553 fetch cur1 into
554 x_itemorg_pegnode_rec(i).Item_Org,
555 x_itemorg_pegnode_rec(i).Qty,
556 x_itemorg_pegnode_rec(i).Peg_date,
557 x_itemorg_pegnode_rec(i).Order_name,
558 x_itemorg_pegnode_rec(i).Prev_pegging_id,
559 x_itemorg_pegnode_rec(i).Demand_id,
560 x_itemorg_pegnode_rec(i).Pegged_qty,
561 x_itemorg_pegnode_rec(i).Item_id,
565 x_itemorg_pegnode_rec(i).Transaction_id,
562 x_itemorg_pegnode_rec(i).order_type,
563 x_itemorg_pegnode_rec(i).order_number,
564 x_itemorg_pegnode_rec(i).Pegging_id,
566 x_itemorg_pegnode_rec(i).end_demand_class;
567 i := i + 1;
568 end loop;
569 close cur1;
570 else
571 open cur3;
572 loop
573 exit when cur3%notfound;
574 fetch cur3 into
575 x_itemorg_pegnode_rec(i).Item_Org,
576 x_itemorg_pegnode_rec(i).Pegging_id,
577 x_itemorg_pegnode_rec(i).Prev_pegging_id,
578 x_itemorg_pegnode_rec(i).Qty,
579 x_itemorg_pegnode_rec(i).Peg_Date,
580 x_itemorg_pegnode_rec(i).Order_name,
581 x_itemorg_pegnode_rec(i).demand_id,
582 x_itemorg_pegnode_rec(i).transaction_id,
583 x_itemorg_pegnode_rec(i).item_id,
584 x_itemorg_pegnode_rec(i).Pegged_qty,
585 x_itemorg_pegnode_rec(i).order_number,
586 x_itemorg_pegnode_rec(i).order_type,
587 x_itemorg_pegnode_rec(i).end_demand_class;
588 i := i + 1;
589 end loop;
590 close cur3;
591 end if;
592
593 END get_suptree_dem_values_rep;
594
595 Procedure get_demtree_dem_values( p_plan_id IN NUMBER,
596 p_transaction_id IN NUMBER,
597 x_itemorg_pegnode_rec OUT NOCOPY MSC_UI_PEG_UTIL.peg_node_rec_values_table,
598 p_instance_id IN NUMBER,
599 p_organization_id IN NUMBER,
600 p_bom_item_type IN NUMBER,
601 p_show_item_desc IN NUMBER DEFAULT 2) IS
602 i number;
603
604 Cursor cur1(l_show_item_desc number) is
605 select decode(l_show_item_desc, 1, substrb(nvl(mis.description,mis.item_name),1,80)||'/'||msc_get_name.org_code(dm.organization_id,dm.sr_instance_id)
606 ,substrb(mis.item_name,1,80)||'/'||msc_get_name.org_code(dm.organization_id,dm.sr_instance_id)) item_org,
607 dm.using_requirement_quantity demand_qty,
608 dm.using_assembly_demand_date demand_date,
609 msc_get_name.lookup_meaning( 'MSC_DEMAND_ORIGINATION',
610 dm.origination_type) origination_name,
611 null prev_pegging_id,
612 dm.demand_id demand_id,
613 dm.using_requirement_quantity pegged_qty,
614 mis.inventory_item_id item_id,
615 dm.origination_type,
616 null order_number,
617 null pegging_id,
618 null transaction_id,
619 null end_demand_class
620 from msc_system_items mis,
621 msc_demands dm
622 where mis.inventory_item_id = dm.inventory_item_id
623 and mis.sr_instance_id = dm.sr_instance_id
624 and mis.organization_id = dm.organization_id
625 and mis.plan_id = dm.plan_id
626 and dm.disposition_id = p_transaction_id
627 and dm.plan_id = p_plan_id
628 and dm.organization_id = p_organization_id
629 and dm.sr_instance_id = p_instance_id
630 and dm.origination_type = 22;
631
632 BEGIN
633 IF p_bom_item_type in (1, 2) THEN -- this means we just want to get Prod.Forecast of Options when we
634
635 -- drill down from ATO Model with Forecast control set to NONE
636
637 i := 1;
638 open cur1(p_show_item_desc);
639 loop
640 exit when cur1%notfound;
641 fetch cur1 into
642 x_itemorg_pegnode_rec(i).Item_Org,
643 x_itemorg_pegnode_rec(i).Qty,
644 x_itemorg_pegnode_rec(i).Peg_date,
645 x_itemorg_pegnode_rec(i).Order_name,
646 x_itemorg_pegnode_rec(i).Prev_pegging_id,
647 x_itemorg_pegnode_rec(i).Demand_id,
648 x_itemorg_pegnode_rec(i).Pegged_qty,
649 x_itemorg_pegnode_rec(i).Item_id,
650 x_itemorg_pegnode_rec(i).order_type,
651 x_itemorg_pegnode_rec(i).order_number,
655 i := i + 1;
652 x_itemorg_pegnode_rec(i).Pegging_id,
653 x_itemorg_pegnode_rec(i).Transaction_id,
654 x_itemorg_pegnode_rec(i).end_demand_class;
656 end loop;
657 close cur1;
658 i := 0;
659 END IF;
660 END get_demtree_dem_values;
661
662
663 Procedure get_label_and_nodevalue(Item_org IN VARCHAR2,
664 Qty IN NUMBER,
665 Pegged_qty IN NUMBER,
666 Peg_date IN DATE,
667 Order_name IN VARCHAR2,
668 end_demand_class IN VARCHAR2,
669 order_type IN NUMBER,
670 Disposition IN NUMBER,
671 Pegging_id IN NUMBER,
672 Prev_pegging_id IN NUMBER,
673 Demand_id IN NUMBER,
674 Transaction_id IN NUMBER,
675 Item_id IN NUMBER,
676 x_node_value OUT NOCOPY VARCHAR2,
677 x_node_label OUT NOCOPY VARCHAR2,
678 p_tmp IN NUMBER,
679 p_supply_org_id IN NUMBER,
680 pvt_so_number IN VARCHAR2,
681 pvt_l_node_number IN NUMBER,
682 p_constr_label IN BOOLEAN default FALSE,
683 p_node_type IN NUMBER default 1,
684 p_calling_module IN NUMBER default 1 ,
685 p_prev_pegging_value IN NUMBER default null )
686
687 -- p_calling_modele : 1- Planner WB, 2 - Allocation WB.
688
689 -- p_type_node 1 - Demand, 2-Supply
690 IS
691 BEGIN
692
693
694 IF p_constr_label THEN
695 -- Constructing the label for demand nodes
696
697 fnd_message.set_name('MSC','MSC_PEGGING_LABEL2');
698 fnd_message.set_token('ITEM_ORG',Item_org);
699 fnd_message.set_token('QTY',nvl(Qty,0));
700 fnd_message.set_token('PEGGED_QTY',nvl(Pegged_qty,0));
701 fnd_message.set_token('DATE',fnd_date.date_to_chardt(Peg_date));
702
703 null;
704 if p_node_type = 1 then -- Demand
705 if ( p_tmp in (6,30) ) then
706 -- 6 sales orders mds
707 -- 30 sales order
708 if p_calling_module = 1 then
709 fnd_message.set_token('ORDER_TYPE',Order_name||' '||
710 pvt_so_number);
711 else
712 null;
713 fnd_message.set_token('ORDER_TYPE',Order_name||' '||
714 pvt_so_number||end_demand_class);
715 end if;
716 elsif ( p_tmp in (8,29) ) then
717 -- 8 manual mds
718 -- 29 forecast
719 if p_calling_module = 1 then
720
721 fnd_message.set_token('ORDER_TYPE',Order_name||' '||
722 pvt_so_number);
723 else
724
725 fnd_message.set_token('ORDER_TYPE',Order_name||' '||
726 pvt_so_number||end_demand_class);
727 end if;
728
729 else
730 fnd_message.set_token('ORDER_TYPE',Order_name);
731 end if;
732 else -- Supply
733 if order_type in ( 1,2,3, 73,74,86,75) THEN
734 fnd_message.set_token('ORDER_TYPE',Order_name||' '||
735 pvt_so_number);
736 else
737 fnd_message.set_token('ORDER_TYPE',Order_name);
738 end if;
739 end if;
740 x_node_label := fnd_message.get;
741 ELSE
742 x_node_label := NULL;
743 END IF;
744 IF p_calling_module = 1 then
745 -- Constructing the node value
746
747 x_node_value := to_char(nvl(Pegging_id,-111))
748 ||fnd_global.local_chr(58)||to_char(nvl(Prev_pegging_id,-111))
749 ||fnd_global.local_chr(58)||to_char(nvl(Demand_id,-111))
750 ||fnd_global.local_chr(58)||to_char(nvl(Transaction_id,-111))
751 ||fnd_global.local_chr(58)||to_char(nvl(p_supply_org_id,-111))
752 ||fnd_global.local_chr(58)|| to_char(nvl(Pegging_id,-111))
753 ||fnd_global.local_chr(58)||to_char(nvl(Prev_pegging_id,-111));
754
755
756 if p_node_type = 1 then -- demand
757
758 x_node_value := x_node_value
759 ||fnd_global.local_chr(58)||to_char(nvl(Demand_id,-111))
760 ||fnd_global.local_chr(58)||to_char(nvl(Item_id,-111))
761 ||fnd_global.local_chr(58)||' -111 '||fnd_global.local_chr(58)||to_char(pvt_l_node_number + 1);
762
763
764 else
765 x_node_value := x_node_value
766 ||fnd_global.local_chr(58)||to_char(nvl(Transaction_id,-111))
767 ||fnd_global.local_chr(58)||to_char(nvl(Item_id,-111))
768 ||fnd_global.local_chr(58)||to_char(nvl(p_prev_pegging_value ,-111))||fnd_global.local_chr(58)||to_char(pvt_l_node_number + 1);
769 ---bug #3556405
770 end if;
771 ELSE
772 if p_node_type = 1 then -- expanding to demands nodes
773 x_node_value := to_char(nvl(pegging_id,-111))
774 ||' '||to_char(nvl(prev_pegging_id,-111))
775 ||' '||to_char(nvl(demand_id,-111))
776 ||' '||to_char(nvl(item_id,-111))
777 ||' -111 '||pvt_l_node_number;
778 else
779 x_node_value := to_char(nvl(pegging_id,-111))||' '||
780 to_char(nvl(prev_pegging_id,-111))||' '||
781 to_char(nvl(transaction_id,-111))||' '||
782 to_char(nvl(item_id,-111))||' ';
783 end if;
784
785 END IF;
786
787 end get_label_and_nodevalue;
788
789 END MSC_UI_PEG_UTIL ;