[Home] [Help]
PACKAGE BODY: APPS.CSP_SUPPLY_DEMAND_PVT
Source
1 PACKAGE BODY CSP_SUPPLY_DEMAND_PVT AS
2 /* $Header: cspvpsdb.pls 120.6 2006/11/08 00:18:33 hhaugeru noship $ */
3 /* $Header: cspvpsdb.pls 120.6 2006/11/08 00:18:33 hhaugeru noship $ */
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CSP_SUPPLY_DEMAND_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(30):='cspvpsdb.pls';
6
7 PROCEDURE get_onhand IS
8 begin
9 insert into csp_sup_dem_sub_temp(
10 inventory_item_id,
11 organization_id,
12 parts_loop_id,
13 hierarchy_node_id,
14 subinventory_code,
15 planning_parameters_id,
16 level_id,
17 onhand_bad,
18 onhand_good)
19 select
20 moq.inventory_item_id,
21 moq.organization_id,
22 csi.parts_loop_id,
23 csi.hierarchy_node_id,
24 moq.subinventory_code,
25 cpp.planning_parameters_id,
26 cpp.level_id,
27 sum(decode(csi.condition_type,'B',moq.transaction_quantity,null)),
28 sum(decode(csi.condition_type,'G',moq.transaction_quantity,null))
29 from mtl_onhand_quantities moq,
30 csp_sec_inventories csi,
31 csp_planning_parameters cpp
32 where csi.organization_id = moq.organization_id
33 and csi.secondary_inventory_name = moq.subinventory_code
34 and moq.inventory_item_id > 0
35 and csi.organization_id = cpp.organization_id (+)
36 and csi.secondary_inventory_name = cpp.secondary_inventory (+)
37 and nvl(cpp.level_id,'%') like g_level_id||'%'
38 group by
39 moq.organization_id,
40 moq.subinventory_code,
41 moq.inventory_item_id,
42 csi.parts_loop_id,
43 csi.hierarchy_node_id,
44 cpp.planning_parameters_id,
45 cpp.level_id;
46
47 insert into csp_sup_dem_sub_temp(
48 inventory_item_id,
49 organization_id,
50 parts_loop_id,
51 hierarchy_node_id,
52 subinventory_code,
53 planning_parameters_id,
54 level_id,
55 onhand_bad,
56 onhand_good)
57 select
58 mmtt.inventory_item_id,
59 mmtt.organization_id,
60 cssdt.parts_loop_id,
61 cssdt.hierarchy_node_id,
62 mmtt.subinventory_code,
63 cssdt.planning_parameters_id,
64 cssdt.level_id,
65 sum(decode(cssdt.onhand_good,null,mmtt.primary_quantity,0)),
66 sum(decode(cssdt.onhand_bad,null,mmtt.primary_quantity,0))
67 from mtl_material_transactions_temp mmtt,
68 csp_sup_dem_sub_temp cssdt,
69 csp_planning_parameters cpp
70 where mmtt.inventory_item_id = cssdt.inventory_item_id
71 and mmtt.organization_id = cssdt.organization_id
72 and mmtt.subinventory_code = cssdt.subinventory_code
73 and mmtt.posting_flag = 'Y'
74 and mmtt.subinventory_code IS NOT NULL
75 and nvl(mmtt.transaction_status,0) <> 2
76 and mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
77 and cpp.organization_id (+) = mmtt.organization_id
78 and cpp.secondary_inventory (+) = mmtt.subinventory_code
79 and nvl(cpp.level_id,'%') like g_level_id||'%'
80 group by
81 mmtt.inventory_item_id,
82 mmtt.organization_id,
83 cssdt.parts_loop_id,
84 cssdt.hierarchy_node_id,
85 mmtt.subinventory_code,
86 cssdt.planning_parameters_id,
87 cssdt.level_id;
88
89 END get_onhand;
90
91 PROCEDURE get_onhand2 IS
92 begin
93 insert into csp_sup_dem_sub_temp(
94 inventory_item_id,
95 organization_id,
96 subinventory_code,
97 planning_parameters_id,
98 level_id,
99 onhand_bad,
100 onhand_good)
101 select
102 moq.inventory_item_id,
103 moq.organization_id,
104 moq.subinventory_code,
105 cpp.planning_parameters_id,
106 cpp.level_id,
107 sum(decode(csi.condition_type,'B',moq.transaction_quantity,null)),
108 sum(decode(csi.condition_type,'G',moq.transaction_quantity,null))
109 from mtl_onhand_quantities moq,
110 csp_sec_inventories csi,
111 csp_planning_parameters cpp
112 where csi.organization_id = moq.organization_id
113 and csi.secondary_inventory_name = moq.subinventory_code
114 and moq.inventory_item_id > 0
115 and csi.organization_id = cpp.organization_id
116 and csi.secondary_inventory_name = cpp.secondary_inventory
117 and cpp.level_id like g_level_id||'%'
118 group by
119 moq.organization_id,
120 moq.subinventory_code,
121 moq.inventory_item_id,
122 cpp.planning_parameters_id,
123 cpp.level_id;
124
125 insert into csp_sup_dem_sub_temp(
126 inventory_item_id,
127 organization_id,
128 subinventory_code,
129 planning_parameters_id,
130 level_id,
131 onhand_bad,
132 onhand_good)
133 select
134 mmtt.inventory_item_id,
135 mmtt.organization_id,
136 mmtt.subinventory_code,
137 cpp.planning_parameters_id,
138 cpp.level_id,
139 sum(decode(csi.condition_type,'B',mmtt.primary_quantity,0)),
140 sum(decode(csi.condition_type,'G',mmtt.primary_quantity,0))
141 from mtl_material_transactions_temp mmtt,
142 csp_sec_inventories csi,
143 csp_planning_parameters cpp
144 where mmtt.organization_id = csi.organization_id
145 and mmtt.subinventory_code = csi.secondary_inventory_name
146 and mmtt.posting_flag = 'Y'
147 and mmtt.subinventory_code IS NOT NULL
148 and nvl(mmtt.transaction_status,0) <> 2
149 and mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
150 and cpp.organization_id = mmtt.organization_id
151 and cpp.secondary_inventory = mmtt.subinventory_code
152 and cpp.level_id like g_level_id||'%'
153 group by
154 mmtt.inventory_item_id,
155 mmtt.organization_id,
156 mmtt.subinventory_code,
157 cpp.planning_parameters_id,
158 cpp.level_id;
159
160 END get_onhand2;
161
162 PROCEDURE get_onhand_wh IS
163 begin
164 insert into csp_sup_dem_sub_temp(
165 inventory_item_id,
166 organization_id,
167 planning_parameters_id,
168 level_id,
169 onhand_good)
170 select
171 moq.inventory_item_id,
172 moq.organization_id,
173 cpp.planning_parameters_id,
174 cpp.level_id,
175 sum(moq.transaction_quantity)
176 from mtl_onhand_quantities moq,
177 csp_planning_parameters cpp
178 where moq.inventory_item_id > 0
179 and cpp.organization_id = moq.organization_id
180 and cpp.organization_type = 'W'
181 and nvl(cpp.level_id,'%') like g_level_id||'%'
182 group by
183 moq.inventory_item_id,
184 moq.organization_id,
185 cpp.planning_parameters_id,
186 cpp.level_id;
187
188 insert into csp_sup_dem_sub_temp(
189 inventory_item_id,
190 organization_id,
191 planning_parameters_id,
192 level_id,
193 onhand_good)
194 select
195 mmtt.inventory_item_id,
196 mmtt.organization_id,
197 cpp.planning_parameters_id,
198 cpp.level_id,
199 sum(nvl(mmtt.primary_quantity,0))
200 from mtl_material_transactions_temp mmtt,
201 csp_planning_parameters cpp
202 where mmtt.organization_id = cpp.organization_id
203 and cpp.organization_type = 'W'
204 and mmtt.posting_flag = 'Y'
205 and mmtt.subinventory_code IS NOT NULL
206 and nvl(mmtt.transaction_status,0) <> 2
207 and mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
208 and nvl(cpp.level_id,'%') like g_level_id||'%'
209 group by
210 mmtt.inventory_item_id,
211 mmtt.organization_id,
212 cpp.planning_parameters_id,
213 cpp.level_id;
214 END get_onhand_wh;
215
216 PROCEDURE get_onhand_wh2 IS
217 begin
218 insert into csp_sup_dem_sub_temp(
219 inventory_item_id,
220 organization_id,
221 planning_parameters_id,
222 level_id,
223 onhand_good)
224 select
225 moq.inventory_item_id,
226 moq.organization_id,
227 cpp.planning_parameters_id,
228 cpp.level_id,
229 sum(moq.transaction_quantity)
230 from mtl_onhand_quantities moq,
231 csp_planning_parameters cpp
232 where cpp.organization_id = moq.organization_id
233 and cpp.node_type = 'ORGANIZATION_WH'
234 and cpp.organization_type = 'W'
235 and cpp.level_id like g_level_id||'%'
236 group by
237 moq.inventory_item_id,
238 moq.organization_id,
239 cpp.planning_parameters_id,
240 cpp.level_id;
241
242 insert into csp_sup_dem_sub_temp(
243 inventory_item_id,
244 organization_id,
245 planning_parameters_id,
246 level_id,
247 onhand_good)
248 select
249 mmtt.inventory_item_id,
250 mmtt.organization_id,
251 cpp.planning_parameters_id,
252 cpp.level_id,
253 sum(nvl(mmtt.primary_quantity,0))
254 from mtl_material_transactions_temp mmtt,
255 csp_planning_parameters cpp
256 where mmtt.organization_id = cpp.organization_id
257 and cpp.node_type = 'ORGANIZATION_WH'
258 and cpp.organization_type = 'W'
259 and mmtt.posting_flag = 'Y'
260 and mmtt.subinventory_code IS NOT NULL
261 and nvl(mmtt.transaction_status,0) <> 2
262 and mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
263 and cpp.level_id like g_level_id||'%'
264 group by
265 mmtt.inventory_item_id,
266 mmtt.organization_id,
267 cpp.planning_parameters_id,
268 cpp.level_id;
269 END get_onhand_wh2;
270
271 PROCEDURE get_defective_wh IS
272 begin
273 insert into csp_sup_dem_sub_temp(
274 inventory_item_id,
275 organization_id,
276 planning_parameters_id,
277 level_id,
278 onhand_bad,
279 onhand_good)
280 select
281 moq.inventory_item_id,
282 moq.organization_id,
283 cpp.planning_parameters_id,
284 cpp.level_id,
285 sum(moq.transaction_quantity),
286 sum(moq.transaction_quantity)*-1
287 from mtl_onhand_quantities moq,
288 csp_sec_inventories csi,
289 csp_planning_parameters cpp
290 where csi.organization_id = moq.organization_id
291 and csi.secondary_inventory_name = moq.subinventory_code
292 and csi.condition_type = 'B'
293 and moq.inventory_item_id > 0
294 and csi.organization_id = cpp.organization_id
295 and cpp.organization_type = 'W'
296 and nvl(cpp.level_id,'%') like g_level_id||'%'
297 group by
298 moq.organization_id,
299 moq.inventory_item_id,
300 cpp.planning_parameters_id,
301 cpp.level_id;
302
303 insert into csp_sup_dem_sub_temp(
304 inventory_item_id,
305 organization_id,
306 planning_parameters_id,
307 level_id,
308 onhand_bad,
309 onhand_good)
310 select
311 mmtt.inventory_item_id,
312 mmtt.organization_id,
313 cpp.planning_parameters_id,
314 cpp.level_id,
315 sum(nvl(mmtt.primary_quantity,0)),
316 sum(nvl(mmtt.primary_quantity,0)) * -1
317 from mtl_material_transactions_temp mmtt,
318 csp_sec_inventories csi,
319 csp_planning_parameters cpp
320 where mmtt.organization_id = cpp.organization_id
321 and cpp.organization_type = 'W'
322 and csi.condition_type = 'B'
323 and csi.organization_id = cpp.organization_id
324 and mmtt.posting_flag = 'Y'
325 and mmtt.subinventory_code = csi.secondary_inventory_name
326 and nvl(mmtt.transaction_status,0) <> 2
327 and mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
328 and nvl(cpp.level_id,'%') like g_level_id||'%'
329 group by
330 mmtt.inventory_item_id,
331 mmtt.organization_id,
332 cpp.planning_parameters_id,
333 cpp.level_id;
334 END get_defective_wh;
335
336 PROCEDURE get_defective_wh2 IS
337 begin
338 insert into csp_sup_dem_sub_temp(
339 inventory_item_id,
340 organization_id,
341 planning_parameters_id,
342 level_id,
343 onhand_bad,
344 onhand_good)
345 select
346 moq.inventory_item_id,
347 moq.organization_id,
348 cpp.planning_parameters_id,
349 cpp.level_id,
350 sum(moq.transaction_quantity),
351 sum(moq.transaction_quantity)*-1
352 from mtl_onhand_quantities moq,
353 csp_sec_inventories csi,
354 csp_planning_parameters cpp
355 where csi.organization_id = moq.organization_id
356 and csi.secondary_inventory_name = moq.subinventory_code
357 and csi.condition_type = 'B'
358 and moq.inventory_item_id > 0
359 and csi.organization_id = cpp.organization_id
360 and cpp.organization_type = 'W'
361 and cpp.node_type = 'ORGANIZATION_WH'
362 and cpp.level_id like g_level_id||'%'
363 group by
364 moq.organization_id,
365 moq.inventory_item_id,
366 cpp.planning_parameters_id,
367 cpp.level_id;
368
369 insert into csp_sup_dem_sub_temp(
370 inventory_item_id,
371 organization_id,
372 planning_parameters_id,
373 level_id,
374 onhand_bad,
375 onhand_good)
376 select
377 mmtt.inventory_item_id,
378 mmtt.organization_id,
379 cpp.planning_parameters_id,
380 cpp.level_id,
381 sum(nvl(mmtt.primary_quantity,0)),
382 sum(nvl(mmtt.primary_quantity,0)) * -1
383 from mtl_material_transactions_temp mmtt,
384 csp_sec_inventories csi,
385 csp_planning_parameters cpp
386 where mmtt.organization_id = cpp.organization_id
387 and mmtt.subinventory_code = csi.secondary_inventory_name
388 and csi.organization_id = cpp.organization_id
389 and csi.condition_type = 'B'
390 and cpp.organization_type = 'W'
391 and cpp.node_type = 'ORGANIZATION_WH'
392 and mmtt.posting_flag = 'Y'
396 group by
393 and nvl(mmtt.transaction_status,0) <> 2
394 and mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
395 and nvl(cpp.level_id,'%') like g_level_id||'%'
397 mmtt.inventory_item_id,
398 mmtt.organization_id,
399 cpp.planning_parameters_id,
400 cpp.level_id;
401 END get_defective_wh2;
402
403 PROCEDURE get_supply IS
404 begin
405 insert into csp_sup_dem_sub_temp(
406 inventory_item_id,
407 organization_id,
408 parts_loop_id,
409 hierarchy_node_id,
410 subinventory_code,
411 planning_parameters_id,
412 level_id,
413 purchase_orders,
414 interorg_transf_in,
415 requisitions,
416 intransit_move_orders)
417 select
418 ms.item_id,
419 ms.to_organization_id,
420 csi.parts_loop_id,
421 csi.hierarchy_node_id,
422 ms.to_subinventory,
423 cpp.planning_parameters_id,
424 cpp.level_id,
425 sum(decode(ms.supply_type_code,'PO',to_org_primary_quantity,'RECEIVING',to_org_primary_quantity,0)) purchase_orders,
426 sum(decode(ms.supply_type_code,'REQ',decode(prha.transferred_to_oe_flag,'Y',decode(prha.type_lookup_code,'INTERNAL',to_org_primary_quantity),0),0)) internal_orders,
427 sum(decode(ms.supply_type_code,'REQ',decode(nvl(prha.transferred_to_oe_flag,'N'),'N',to_org_primary_quantity,0),0)) requisitions,
428 sum(decode(ms.supply_type_code,'SHIPMENT',to_org_primary_quantity,0)) interorg_transfer
429 from mtl_supply ms,
430 po_requisition_headers_all prha,
431 csp_sec_inventories csi,
432 csp_planning_parameters cpp
433 where ms.req_header_id = prha.requisition_header_id(+)
434 and ms.to_organization_id = csi.organization_id
435 and ms.to_subinventory = csi.secondary_inventory_name
436 and cpp.organization_id (+) = csi.organization_id
437 and cpp.secondary_inventory (+) = csi.secondary_inventory_name
438 and nvl(cpp.level_id,'%') like g_level_id||'%'
439 group by
440 ms.item_id,
441 ms.to_organization_id,
442 ms.to_subinventory,
443 csi.parts_loop_id,
444 csi.hierarchy_node_id,
445 cpp.planning_parameters_id,
446 cpp.level_id;
447 end get_supply;
448
449 PROCEDURE get_supply2 IS
450 begin
451 insert into csp_sup_dem_sub_temp(
452 inventory_item_id,
453 organization_id,
454 subinventory_code,
455 planning_parameters_id,
456 level_id,
457 purchase_orders,
458 interorg_transf_in,
459 requisitions,
460 intransit_move_orders)
461 select
462 ms.item_id,
463 ms.to_organization_id,
464 ms.to_subinventory,
465 cpp.planning_parameters_id,
466 cpp.level_id,
467 sum(decode(ms.supply_type_code,'PO',to_org_primary_quantity,'RECEIVING',to_org_primary_quantity,0)) purchase_orders,
468 sum(decode(ms.supply_type_code,'REQ',decode(prha.transferred_to_oe_flag,'Y',decode(prha.type_lookup_code,'INTERNAL',to_org_primary_quantity),0),0)) internal_orders,
469 sum(decode(ms.supply_type_code,'REQ',decode(nvl(prha.transferred_to_oe_flag,'N'),'N',to_org_primary_quantity,0),0)) requisitions,
470 sum(decode(ms.supply_type_code,'SHIPMENT',to_org_primary_quantity,0)) interorg_transfer
471 from mtl_supply ms,
472 po_requisition_headers_all prha,
473 csp_planning_parameters cpp
474 where ms.req_header_id = prha.requisition_header_id(+)
475 and ms.to_organization_id = cpp.organization_id
476 and ms.to_subinventory = cpp.secondary_inventory
477 and cpp.level_id like g_level_id||'%'
478 group by
479 ms.item_id,
480 ms.to_organization_id,
481 ms.to_subinventory,
482 cpp.planning_parameters_id,
483 cpp.level_id;
484 end get_supply2;
485
486 PROCEDURE get_supply_wh IS
487 begin
488 insert into csp_sup_dem_sub_temp(
489 inventory_item_id,
490 organization_id,
491 planning_parameters_id,
492 level_id,
493 purchase_orders,
494 interorg_transf_in,
495 requisitions,
496 intransit_move_orders)
497 select
498 ms.item_id,
499 ms.to_organization_id,
500 cpp.planning_parameters_id,
501 cpp.level_id,
502 sum(decode(ms.supply_type_code,'PO',to_org_primary_quantity,'RECEIVING',to_org_primary_quantity,0)) purchase_orders,
503 sum(decode(ms.supply_type_code,'REQ',decode(prha.transferred_to_oe_flag,'Y',decode(prha.type_lookup_code,'INTERNAL',to_org_primary_quantity),0),0)) internal_orders,
504 sum(decode(ms.supply_type_code,'REQ',decode(nvl(prha.transferred_to_oe_flag,'N'),'N',to_org_primary_quantity,0),0)) requisitions,
505 sum(decode(ms.supply_type_code,'SHIPMENT',to_org_primary_quantity,0)) interorg_transfer
506 from mtl_supply ms,
507 po_requisition_headers_all prha,
508 csp_planning_parameters cpp
509 where ms.req_header_id = prha.requisition_header_id(+)
510 and cpp.organization_id = ms.to_organization_id
511 and cpp.organization_type = 'W'
512 and ms.item_id > 0
513 and nvl(cpp.level_id,'%') like g_level_id||'%'
514 group by
515 ms.item_id,
516 ms.to_organization_id,
517 cpp.planning_parameters_id,
518 cpp.level_id;
519 end get_supply_wh;
520
521 PROCEDURE get_supply_wh2 IS
525 organization_id,
522 begin
523 insert into csp_sup_dem_sub_temp(
524 inventory_item_id,
526 planning_parameters_id,
527 level_id,
528 purchase_orders,
529 interorg_transf_in,
530 requisitions,
531 intransit_move_orders)
532 select
533 ms.item_id,
534 ms.to_organization_id,
535 cpp.planning_parameters_id,
536 cpp.level_id,
537 sum(decode(ms.supply_type_code,'PO',to_org_primary_quantity,'RECEIVING',to_org_primary_quantity,0)) purchase_orders,
538 sum(decode(ms.supply_type_code,'REQ',decode(prha.transferred_to_oe_flag,'Y',decode(prha.type_lookup_code,'INTERNAL',to_org_primary_quantity),0),0)) internal_orders,
539 sum(decode(ms.supply_type_code,'REQ',decode(nvl(prha.transferred_to_oe_flag,'N'),'N',to_org_primary_quantity,0),0)) requisitions,
540 sum(decode(ms.supply_type_code,'SHIPMENT',to_org_primary_quantity,0)) interorg_transfer
541 from mtl_supply ms,
542 po_requisition_headers_all prha,
543 csp_planning_parameters cpp
544 where ms.req_header_id = prha.requisition_header_id(+)
545 and ms.to_organization_id = cpp.organization_id
546 and ms.item_id > 0
547 and cpp.organization_type = 'W'
548 and cpp.node_type = 'ORGANIZATION_WH'
549 and cpp.level_id like g_level_id||'%'
550 group by
551 ms.item_id,
552 ms.to_organization_id,
553 cpp.planning_parameters_id,
554 cpp.level_id;
555 end get_supply_wh2;
556
557 PROCEDURE get_internal_orders_out_wh IS
558 begin
559 insert into csp_sup_dem_sub_temp(
560 inventory_item_id,
561 organization_id,
562 planning_parameters_id,
563 level_id,
564 interorg_transf_out)
565 select
566 ms.item_id,
567 ms.from_organization_id,
568 cpp.planning_parameters_id,
569 cpp.level_id,
570 sum(decode(ms.supply_type_code,'REQ',decode(prha.transferred_to_oe_flag,'Y',decode(prha.type_lookup_code,'INTERNAL',to_org_primary_quantity),0),0))
571 from mtl_supply ms,
572 po_requisition_headers_all prha,
573 csp_planning_parameters cpp
574 where ms.req_header_id = prha.requisition_header_id
575 and cpp.organization_id = ms.from_organization_id
576 and cpp.organization_type = 'W'
577 and ms.item_id > 0
578 and nvl(cpp.level_id,'%') like g_level_id||'%'
579 group by
580 ms.item_id,
581 ms.from_organization_id,
582 cpp.planning_parameters_id,
583 cpp.level_id;
584 end get_internal_orders_out_wh;
585
586 PROCEDURE get_internal_orders_out_wh2 IS
587 begin
588 insert into csp_sup_dem_sub_temp(
589 inventory_item_id,
590 organization_id,
591 planning_parameters_id,
592 level_id,
593 interorg_transf_out)
594 select
595 ms.item_id,
596 ms.from_organization_id,
597 cpp.planning_parameters_id,
598 cpp.level_id,
599 sum(decode(ms.supply_type_code,'REQ',decode(prha.transferred_to_oe_flag,'Y',decode(prha.type_lookup_code,'INTERNAL',to_org_primary_quantity),0),0))
600 from mtl_supply ms,
601 po_requisition_headers_all prha,
602 csp_planning_parameters cpp
603 where prha.requisition_header_id = ms.req_header_id
604 and prha.transferred_to_oe_flag= 'Y'
605 and prha.type_lookup_code = 'INTERNAL'
606 and ms.from_organization_id = cpp.organization_id
607 and ms.supply_type_code = 'REQ'
608 and cpp.organization_type = 'W'
609 and cpp.node_type = 'ORGANIZATION_WH'
610 and cpp.level_id like g_level_id||'%'
611 group by
612 ms.item_id,
613 ms.from_organization_id,
614 cpp.planning_parameters_id,
615 cpp.level_id;
616 end get_internal_orders_out_wh2;
617
618 PROCEDURE get_open_work_orders IS
619 begin
620 insert into csp_sup_dem_sub_temp(
621 inventory_item_id,
622 organization_id,
623 parts_loop_id,
624 hierarchy_node_id,
625 subinventory_code,
626 planning_parameters_id,
627 level_id,
628 work_orders)
629 select
630 wdj.primary_item_id,
631 wdj.organization_id,
632 csi.parts_loop_id,
633 csi.hierarchy_node_id,
634 csi.secondary_inventory_name,
635 cpp.planning_parameters_id,
636 cpp.level_id,
637 sum(start_quantity - nvl(quantity_completed,0) - nvl(quantity_scrapped, 0))
638 from wip_discrete_jobs wdj,
639 wip_entities we,
640 csp_sec_inventories csi,
641 csp_planning_parameters cpp
642 where wdj.status_type = 3
643 and we.wip_entity_id = wdj.wip_entity_id
644 and we.entity_type <> 6
645 and wdj.completion_subinventory = csi.secondary_inventory_name
646 and wdj.organization_id = csi.organization_id
647 and csi.organization_id = cpp.organization_id (+)
648 and csi.secondary_inventory_name = cpp.secondary_inventory (+)
649 and nvl(cpp.level_id,'%') like g_level_id||'%'
650 and wdj.primary_item_id > 0
651 group by
652 wdj.primary_item_id,
653 wdj.organization_id,
654 csi.secondary_inventory_name,
655 csi.parts_loop_id,
656 csi.hierarchy_node_id,
657 cpp.planning_parameters_id,
658 cpp.level_id;
662 begin
659 END get_open_work_orders;
660
661 PROCEDURE get_open_work_orders2 IS
663 insert into csp_sup_dem_sub_temp(
664 inventory_item_id,
665 organization_id,
666 subinventory_code,
667 planning_parameters_id,
668 level_id,
669 work_orders)
670 select
671 wdj.primary_item_id,
672 wdj.organization_id,
673 cpp.secondary_inventory,
674 cpp.planning_parameters_id,
675 cpp.level_id,
676 sum(start_quantity - nvl(quantity_completed,0) - nvl(quantity_scrapped, 0))
677 from wip_discrete_jobs wdj,
678 wip_entities we,
679 csp_planning_parameters cpp
680 where wdj.status_type = 3
681 and we.wip_entity_id = wdj.wip_entity_id
682 and we.entity_type <> 6
683 and wdj.completion_subinventory = cpp.secondary_inventory
684 and wdj.organization_id = cpp.organization_id
685 and cpp.level_id like g_level_id||'%'
686 and wdj.primary_item_id > 0
687 group by
688 wdj.primary_item_id,
689 wdj.organization_id,
690 cpp.secondary_inventory,
691 cpp.planning_parameters_id,
692 cpp.level_id;
693 END get_open_work_orders2;
694
695 PROCEDURE get_open_work_orders_wh IS
696 begin
697 insert into csp_sup_dem_sub_temp(
698 inventory_item_id,
699 organization_id,
700 planning_parameters_id,
701 level_id,
702 work_orders)
703 select
704 wdj.primary_item_id,
705 wdj.organization_id,
706 cpp.planning_parameters_id,
707 cpp.level_id,
708 sum(start_quantity - nvl(quantity_completed,0) - nvl(quantity_scrapped, 0))
709 from wip_discrete_jobs wdj,
710 wip_entities we,
711 csp_planning_parameters cpp
712 where wdj.status_type = 3
713 and we.wip_entity_id = wdj.wip_entity_id
714 and we.entity_type <> 6
715 and wdj.organization_id = cpp.organization_id
716 and cpp.organization_type = 'W'
717 and nvl(cpp.level_id,'%') like g_level_id||'%'
718 and wdj.primary_item_id > 0
719 group by
720 wdj.primary_item_id,
721 wdj.organization_id,
722 cpp.planning_parameters_id,
723 cpp.level_id;
724 END get_open_work_orders_wh;
725
726 PROCEDURE get_open_work_orders_wh2 IS
727 begin
728 insert into csp_sup_dem_sub_temp(
729 inventory_item_id,
730 organization_id,
731 planning_parameters_id,
732 level_id,
733 work_orders)
734 select
735 wdj.primary_item_id,
736 wdj.organization_id,
737 cpp.planning_parameters_id,
738 cpp.level_id,
739 sum(start_quantity - nvl(quantity_completed,0) - nvl(quantity_scrapped, 0))
740 from wip_discrete_jobs wdj,
741 wip_entities we,
742 csp_planning_parameters cpp
743 where wdj.status_type = 3
744 and we.wip_entity_id = wdj.wip_entity_id
745 and we.entity_type <> 6
746 and wdj.organization_id = cpp.organization_id
747 and cpp.organization_type = 'W'
748 and cpp.node_type = 'ORGANIZATION_WH'
749 and cpp.level_id like g_level_id||'%'
750 and wdj.primary_item_id > 0
751 group by
752 wdj.primary_item_id,
753 wdj.organization_id,
754 cpp.planning_parameters_id,
755 cpp.level_id;
756 END get_open_work_orders_wh2;
757
758 PROCEDURE get_move_orders_in IS
759 begin
760 insert into csp_sup_dem_sub_temp(
761 inventory_item_id,
762 organization_id,
763 parts_loop_id,
764 hierarchy_node_id,
765 subinventory_code,
766 planning_parameters_id,
767 level_id,
768 move_orders_in)
769 select
770 mtrl.inventory_item_id,
771 csi.organization_id,
772 csi.parts_loop_id,
773 csi.hierarchy_node_id,
774 csi.secondary_inventory_name,
775 cpp.planning_parameters_id,
776 cpp.level_id,
777 sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
778 null,
779 greatest(greatest(nvl(mtrl.quantity_detailed,0),nvl(mtrl.quantity,0))-nvl(mtrl.quantity_delivered,0),0),
780 mtrl.uom_code,
781 msib.primary_uom_code,
782 null,
783 null))
784 from mtl_txn_request_lines mtrl,
785 csp_sec_inventories csi,
786 mtl_system_items_b msib,
787 csp_planning_parameters cpp
788 where mtrl.organization_id = csi.organization_id
789 and mtrl.organization_id = msib.organization_id
790 and mtrl.inventory_item_id = msib.inventory_item_id
791 and mtrl.to_subinventory_code = csi.secondary_inventory_name
792 and mtrl.line_status in (3,7)
793 and csi.organization_id = cpp.organization_id (+)
794 and csi.secondary_inventory_name = cpp.secondary_inventory (+)
795 and nvl(cpp.level_id,'%') like g_level_id||'%'
796 group by csi.organization_id,
797 csi.secondary_inventory_name,
798 mtrl.inventory_item_id,
799 csi.parts_loop_id,
803 END get_move_orders_in;
800 csi.hierarchy_node_id,
801 cpp.planning_parameters_id,
802 cpp.level_id;
804
805 PROCEDURE get_move_orders_in2 IS
806 begin
807 insert into csp_sup_dem_sub_temp(
808 inventory_item_id,
809 organization_id,
810 planning_parameters_id,
811 level_id,
812 move_orders_in)
813 select
814 mtrl.inventory_item_id,
815 cpp.organization_id,
816 cpp.planning_parameters_id,
817 cpp.level_id,
818 sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
819 null,
820 greatest(greatest(nvl(mtrl.quantity_detailed,0),nvl(mtrl.quantity,0))-nvl(mtrl.quantity_delivered,0),0),
821 mtrl.uom_code,
822 msib.primary_uom_code,
823 null,
824 null))
825 from mtl_txn_request_lines mtrl,
826 mtl_system_items_b msib,
827 csp_planning_parameters cpp
828 where mtrl.to_organization_id = cpp.organization_id
829 and mtrl.to_subinventory_code = cpp.secondary_inventory
830 and mtrl.line_status in (3,7)
831 and msib.organization_id = cpp.organization_id
832 and msib.inventory_item_id = mtrl.inventory_item_id
833 and cpp.level_id like g_level_id||'%'
834 group by cpp.organization_id,
835 cpp.secondary_inventory,
836 mtrl.inventory_item_id,
837 cpp.planning_parameters_id,
838 cpp.level_id;
839 END get_move_orders_in2;
840
841 PROCEDURE get_move_orders_out IS
842 begin
843 insert into csp_sup_dem_sub_temp(
844 inventory_item_id,
845 organization_id,
846 parts_loop_id,
847 hierarchy_node_id,
848 subinventory_code,
849 planning_parameters_id,
850 level_id,
851 move_orders_out)
852 select
853 mtrl.inventory_item_id,
854 csi.organization_id,
855 csi.parts_loop_id,
856 csi.hierarchy_node_id,
857 csi.secondary_inventory_name,
858 cpp.planning_parameters_id,
859 cpp.level_id,
860 sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
861 null,
862 greatest(greatest(nvl(mtrl.quantity_detailed,0),nvl(mtrl.quantity,0))-nvl(mtrl.quantity_delivered,0),0),
863 mtrl.uom_code,
864 msib.primary_uom_code,
865 null,
866 null))
867 from mtl_txn_request_lines mtrl,
868 csp_sec_inventories csi,
869 mtl_system_items_b msib,
870 csp_planning_parameters cpp
871 where mtrl.organization_id = csi.organization_id
872 and mtrl.organization_id = msib.organization_id
873 and mtrl.inventory_item_id = msib.inventory_item_id
874 and mtrl.from_subinventory_code = csi.secondary_inventory_name
875 and mtrl.line_status in (3,7)
876 and csi.organization_id = cpp.organization_id (+)
877 and csi.secondary_inventory_name = cpp.secondary_inventory (+)
878 and nvl(cpp.level_id,'%') like g_level_id||'%'
879 group by csi.organization_id,
880 csi.secondary_inventory_name,
881 mtrl.inventory_item_id,
882 csi.parts_loop_id,
883 csi.hierarchy_node_id,
884 cpp.planning_parameters_id,
885 cpp.level_id;
886 END get_move_orders_out;
887
888 PROCEDURE get_move_orders_out2 IS
889 begin
890 insert into csp_sup_dem_sub_temp(
891 inventory_item_id,
892 organization_id,
893 subinventory_code,
894 planning_parameters_id,
895 level_id,
896 move_orders_out)
897 select
898 mtrl.inventory_item_id,
899 cpp.organization_id,
900 cpp.secondary_inventory,
901 cpp.planning_parameters_id,
902 cpp.level_id,
903 sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
904 null,
905 greatest(greatest(nvl(mtrl.quantity_detailed,0),nvl(mtrl.quantity,0))-nvl(mtrl.quantity_delivered,0),0),
906 mtrl.uom_code,
907 msib.primary_uom_code,
908 null,
909 null))
910 from mtl_txn_request_lines mtrl,
911 mtl_system_items_b msib,
912 csp_planning_parameters cpp
913 where mtrl.organization_id = cpp.organization_id
914 and mtrl.organization_id = msib.organization_id
915 and mtrl.inventory_item_id = msib.inventory_item_id
916 and mtrl.from_subinventory_code = cpp.secondary_inventory
917 and mtrl.line_status in (3,7)
918 and cpp.level_id like g_level_id||'%'
919 group by cpp.organization_id,
920 cpp.secondary_inventory,
921 mtrl.inventory_item_id,
922 cpp.planning_parameters_id,
923 cpp.level_id;
924 END get_move_orders_out2;
925
926 PROCEDURE get_move_orders_wh IS
927 begin
928 insert into csp_sup_dem_sub_temp(
929 inventory_item_id,
930 organization_id,
931 planning_parameters_id,
932 level_id,
933 move_orders_in,
934 move_orders_out)
935 select
936 mtrl.inventory_item_id,
937 cpp.organization_id,
941 null,
938 cpp.planning_parameters_id,
939 cpp.level_id,
940 sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
942 nvl(mtrl.quantity,0)-nvl(mtrl.quantity_delivered,0),
943 mtrl.uom_code,
944 msib.primary_uom_code,
945 null,
946 null)),
947 sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
948 null,
949 nvl(mtrl.quantity,0)-nvl(mtrl.quantity_delivered,0),
950 mtrl.uom_code,
951 msib.primary_uom_code,
952 null,
953 null))
954 from mtl_txn_request_lines mtrl,
955 mtl_system_items_b msib,
956 csp_planning_parameters cpp
957 where mtrl.organization_id = cpp.organization_id
958 and mtrl.organization_id = msib.organization_id
959 and mtrl.inventory_item_id = msib.inventory_item_id
960 and mtrl.line_status in (3,7)
961 and cpp.organization_type = 'W'
962 and nvl(cpp.level_id,'%') like g_level_id||'%'
963 group by cpp.organization_id,
964 mtrl.inventory_item_id,
965 cpp.planning_parameters_id,
966 cpp.level_id;
967 END get_move_orders_wh;
968
969 PROCEDURE get_move_orders_wh2 IS
970 begin
971 insert into csp_sup_dem_sub_temp(
972 inventory_item_id,
973 organization_id,
974 planning_parameters_id,
975 level_id,
976 move_orders_in,
977 move_orders_out)
978 select
979 mtrl.inventory_item_id,
980 cpp.organization_id,
981 cpp.planning_parameters_id,
982 cpp.level_id,
983 sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
984 null,
985 nvl(mtrl.quantity,0)-nvl(mtrl.quantity_delivered,0),
986 mtrl.uom_code,
987 msib.primary_uom_code,
988 null,
989 null)),
990 sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
991 null,
992 nvl(mtrl.quantity,0)-nvl(mtrl.quantity_delivered,0),
993 mtrl.uom_code,
994 msib.primary_uom_code,
995 null,
996 null))
997 from mtl_txn_request_lines mtrl,
998 mtl_system_items_b msib,
999 csp_planning_parameters cpp
1000 where mtrl.organization_id = cpp.organization_id
1001 and mtrl.organization_id = msib.organization_id
1002 and mtrl.inventory_item_id = msib.inventory_item_id
1003 and mtrl.line_status in (3,7)
1004 and cpp.node_type = 'ORGANIZATION_WH'
1005 and cpp.organization_type = 'W'
1006 and cpp.level_id like g_level_id||'%'
1007 group by cpp.organization_id,
1008 mtrl.inventory_item_id,
1009 cpp.planning_parameters_id,
1010 cpp.level_id;
1011 END get_move_orders_wh2;
1012
1013 PROCEDURE get_orders_out IS
1014 begin
1015 insert into csp_sup_dem_sub_temp(
1016 inventory_item_id,
1017 organization_id,
1018 parts_loop_id,
1019 hierarchy_node_id,
1020 subinventory_code,
1021 planning_parameters_id,
1022 level_id,
1023 sales_orders,
1024 interorg_transf_out)
1025 select
1026 ola.inventory_item_id,
1027 csi.organization_id,
1028 csi.parts_loop_id,
1029 csi.hierarchy_node_id,
1030 csi.secondary_inventory_name,
1031 cpp.planning_parameters_id,
1032 cpp.level_id,
1033 decode(ola.order_source_id,10,0,
1034 sum(nvl(ola.ordered_quantity,0) -
1035 nvl(ola.cancelled_quantity,0) -
1036 nvl(ola.shipped_quantity,0))) sales_orders,
1037 decode(ola.order_source_id,10,
1038 sum(nvl(ola.ordered_quantity,0) -
1042 csp_sec_inventories csi,
1039 nvl(ola.cancelled_quantity,0) -
1040 nvl(ola.shipped_quantity,0)),0) internal_orders_out
1041 from oe_order_lines_all ola,
1043 csp_planning_parameters cpp
1044 where ola.ship_from_org_id = csi.organization_id
1045 and ola.subinventory = csi.secondary_inventory_name
1046 and ola.open_flag = 'Y'
1047 and csi.organization_id = cpp.organization_id (+)
1048 and csi.secondary_inventory_name = cpp.secondary_inventory (+)
1049 and nvl(cpp.level_id,'%') like g_level_id||'%'
1050 group by
1051 csi.organization_id,
1052 csi.secondary_inventory_name,
1053 csi.parts_loop_id,
1054 csi.hierarchy_node_id,
1055 ola.inventory_item_id,
1056 cpp.planning_parameters_id,
1057 cpp.level_id,
1058 ola.order_source_id;
1059 END get_orders_out;
1060
1061 PROCEDURE get_orders_out2 IS
1062 begin
1063 insert into csp_sup_dem_sub_temp(
1064 inventory_item_id,
1065 organization_id,
1066 subinventory_code,
1067 planning_parameters_id,
1068 level_id,
1069 sales_orders,
1070 interorg_transf_out)
1071 select
1072 ola.inventory_item_id,
1073 cpp.organization_id,
1074 cpp.secondary_inventory,
1075 cpp.planning_parameters_id,
1076 cpp.level_id,
1077 decode(ola.order_source_id,10,0,
1078 sum(nvl(ola.ordered_quantity,0) -
1079 nvl(ola.cancelled_quantity,0) -
1080 nvl(ola.shipped_quantity,0))) sales_orders,
1081 decode(ola.order_source_id,10,
1082 sum(nvl(ola.ordered_quantity,0) -
1083 nvl(ola.cancelled_quantity,0) -
1084 nvl(ola.shipped_quantity,0)),0) internal_orders_out
1085 from oe_order_lines_all ola,
1086 csp_planning_parameters cpp
1087 where ola.ship_from_org_id = cpp.organization_id
1088 and ola.subinventory = cpp.secondary_inventory
1089 and ola.open_flag = 'Y'
1090 and cpp.level_id like g_level_id||'%'
1091 group by
1092 cpp.organization_id,
1093 cpp.secondary_inventory,
1094 ola.inventory_item_id,
1095 cpp.planning_parameters_id,
1096 cpp.level_id,
1097 ola.order_source_id;
1098 END get_orders_out2;
1099
1100 PROCEDURE get_excess IS
1101 begin
1102 insert into csp_sup_dem_sub_temp(
1103 inventory_item_id,
1104 organization_id,
1105 parts_loop_id,
1106 hierarchy_node_id,
1107 subinventory_code,
1108 planning_parameters_id,
1109 level_id,
1110 excess_quantity)
1111 select
1112 cel.inventory_item_id,
1113 cel.organization_id,
1114 csi.parts_loop_id,
1115 csi.hierarchy_node_id,
1116 cel.subinventory_code,
1117 cpp.planning_parameters_id,
1118 cpp.level_id,
1119 sum(nvl(excess_quantity,0))
1120 from csp_excess_lists cel,
1121 csp_sec_inventories csi,
1122 csp_planning_parameters cpp
1123 where cel.condition_code = 'G'
1124 and cel.excess_status = 'O'
1125 and cel.subinventory_code = csi.secondary_inventory_name
1126 and cel.organization_id = csi.organization_id
1127 and csi.organization_id = cpp.organization_id (+)
1128 and csi.secondary_inventory_name = cpp.secondary_inventory (+)
1129 and nvl(cpp.level_id,'%') like g_level_id||'%'
1130 group by
1131 cel.inventory_item_id,
1132 cel.organization_id,
1133 cel.subinventory_code,
1134 csi.parts_loop_id,
1135 csi.hierarchy_node_id,
1136 cpp.planning_parameters_id,
1137 cpp.level_id;
1138 END get_excess;
1139
1140 PROCEDURE get_excess_wh IS
1141 begin
1142 insert into csp_sup_dem_sub_temp(
1143 inventory_item_id,
1144 organization_id,
1145 planning_parameters_id,
1146 level_id,
1147 excess_quantity)
1148 select
1149 cel.inventory_item_id,
1150 cel.organization_id,
1151 cpp.planning_parameters_id,
1152 cpp.level_id,
1153 sum(cel.excess_quantity)
1154 from csp_excess_lists cel,
1155 csp_planning_parameters cpp
1156 where cel.condition_code = 'G'
1157 and cel.excess_status = 'O'
1158 and cel.subinventory_code is null
1159 and cel.organization_id = cpp.organization_id
1160 and cpp.organization_type = 'W'
1161 and nvl(cpp.level_id,'%') like g_level_id||'%'
1162 group by
1163 cel.inventory_item_id,
1164 cel.organization_id,
1165 cpp.planning_parameters_id,
1166 cpp.level_id;
1167 END get_excess_wh;
1168
1169
1170 PROCEDURE get_open_sales_orders_wh IS
1171 begin
1172 insert into csp_sup_dem_sub_temp(
1173 inventory_item_id,
1174 organization_id,
1175 planning_parameters_id,
1176 level_id,
1177 sales_orders)
1178 select
1179 ola.inventory_item_id,
1180 cpp.organization_id,
1181 cpp.planning_parameters_id,
1182 cpp.level_id,
1183 sum(nvl(ola.ordered_quantity,0) -
1184 nvl(ola.cancelled_quantity,0) -
1185 nvl(ola.shipped_quantity,0))
1186 from oe_order_lines_all ola,
1187 csp_planning_parameters cpp
1188 where ola.ship_from_org_id = cpp.organization_id
1189 and cpp.organization_type = 'W'
1190 and ola.open_flag = 'Y'
1191 and nvl(ola.order_source_id,0) <> 10
1192 and nvl(cpp.level_id,'%') like g_level_id||'%'
1193 group by
1194 cpp.organization_id,
1195 ola.inventory_item_id,
1196 cpp.planning_parameters_id,
1197 cpp.level_id;
1198 END get_open_sales_orders_wh;
1199
1200 PROCEDURE get_open_sales_orders_wh2 IS
1201 begin
1202 insert into csp_sup_dem_sub_temp(
1203 inventory_item_id,
1204 organization_id,
1205 planning_parameters_id,
1206 level_id,
1207 sales_orders)
1208 select
1209 ola.inventory_item_id,
1210 cpp.organization_id,
1211 cpp.planning_parameters_id,
1215 nvl(ola.shipped_quantity,0))
1212 cpp.level_id,
1213 sum(nvl(ola.ordered_quantity,0) -
1214 nvl(ola.cancelled_quantity,0) -
1216 from oe_order_lines_all ola,
1217 csp_planning_parameters cpp
1218 where ola.ship_from_org_id = cpp.organization_id
1219 and cpp.organization_type = 'W'
1220 and cpp.node_type = 'ORGANIZATION_WH'
1221 and ola.open_flag = 'Y'
1222 and nvl(ola.order_source_id,0) <> 10
1223 and cpp.level_id like g_level_id||'%'
1224 group by
1225 cpp.organization_id,
1226 ola.inventory_item_id,
1227 cpp.planning_parameters_id,
1228 cpp.level_id;
1229 END get_open_sales_orders_wh2;
1230
1231 PROCEDURE mv_to_temp IS
1232 begin
1233 insert into csp_sup_dem_sub_temp(
1234 inventory_item_id,
1235 organization_id,
1236 parts_loop_id,
1237 hierarchy_node_id,
1238 subinventory_code,
1239 planning_parameters_id,
1240 level_id,
1241 purchase_orders,
1242 sales_orders,
1243 requisitions,
1244 interorg_transf_in,
1245 onhand_good,
1246 onhand_bad,
1247 intransit_move_orders,
1248 interorg_transf_out,
1249 move_orders_in,
1250 move_orders_out,
1251 work_orders,
1252 excess_quantity)
1253 select
1254 inventory_item_id,
1255 organization_id,
1256 parts_loop_id,
1257 hierarchy_node_id,
1258 subinventory_code,
1259 planning_parameters_id,
1260 level_id,
1261 purchase_orders,
1262 sales_orders,
1263 requisitions,
1264 interorg_transf_in,
1265 onhand_good,
1266 onhand_bad,
1267 intransit_move_orders,
1268 interorg_transf_out,
1269 move_orders_in,
1270 move_orders_out,
1271 work_orders,
1272 excess_quantity
1273 from csp_sup_dem_sub_mv
1274 where nvl(level_id,'a') not like g_level_id||'%';
1275 end mv_to_temp;
1276
1277 --------------------------------------------------------------------
1278
1279 /* update_hierarchy */
1280
1281 --------------------------------------------------------------------
1282
1283 PROCEDURE update_hierarchy IS
1284 l_level number := 1;
1285 begin
1286 l_level := 1;
1287 --subinventories to parent
1288 insert into csp_sup_dem_rh_temp(
1289 level_id,
1290 organization_id,
1291 inventory_item_id,
1292 hierarchy_node_id,
1293 purchase_orders,
1294 sales_orders,
1295 requisitions,
1296 interorg_transf_in,
1297 onhand_good,
1298 onhand_bad,
1299 intransit_move_orders,
1300 interorg_transf_out,
1301 move_orders_in,
1302 move_orders_out,
1303 work_orders)
1304 select l_level,
1305 min(organization_id),
1306 inventory_item_id,
1307 hierarchy_node_id,
1308 sum(nvl(purchase_orders,0)),
1309 sum(nvl(sales_orders,0)),
1310 sum(nvl(requisitions,0)),
1311 sum(nvl(interorg_transf_in,0)),
1312 sum(nvl(onhand_good,0)),
1313 sum(nvl(onhand_bad,0)),
1314 sum(nvl(intransit_move_orders,0)),
1315 sum(nvl(interorg_transf_out,0)),
1316 sum(nvl(move_orders_in,0)),
1317 sum(nvl(move_orders_out,0)),
1318 sum(nvl(work_orders,0))
1319 from csp_sup_dem_sub_mv
1320 where hierarchy_node_id > 0
1321 group by
1322 inventory_item_id,
1323 hierarchy_node_id;
1324 loop
1325 -- node to parent
1326 insert into csp_sup_dem_rh_temp(
1327 level_id,
1328 organization_id,
1329 inventory_item_id,
1330 hierarchy_node_id,
1331 purchase_orders,
1332 sales_orders,
1333 requisitions,
1334 interorg_transf_in,
1335 onhand_good,
1336 onhand_bad,
1337 intransit_move_orders,
1338 interorg_transf_out,
1339 move_orders_in,
1340 move_orders_out,
1341 work_orders)
1342 select l_level+1,
1343 min(ctsd.organization_id),
1344 inventory_item_id,
1345 parent_node_id,
1346 sum(nvl(purchase_orders,0)),
1347 sum(nvl(sales_orders,0)),
1348 sum(nvl(requisitions,0)),
1349 sum(nvl(interorg_transf_in,0)),
1350 sum(nvl(onhand_good,0)),
1351 sum(nvl(onhand_bad,0)),
1352 sum(nvl(intransit_move_orders,0)),
1353 sum(nvl(interorg_transf_out,0)),
1354 sum(nvl(move_orders_in,0)),
1355 sum(nvl(move_orders_out,0)),
1356 sum(nvl(work_orders,0))
1357 from csp_sup_dem_rh_temp ctsd,
1358 csp_rep_hierarchies crh
1359 where level_id = l_level
1360 and crh.hierarchy_node_id = ctsd.hierarchy_node_id
1361 and crh.parent_node_id > 0
1362 group by
1363 inventory_item_id,
1364 parent_node_id;
1365 if sql%notfound then
1366 exit;
1367 end if;
1368 l_level := l_level+1;
1369 end loop;
1370 dbms_snapshot.refresh('csp_sup_dem_rh_mv','C');
1371
1372 end update_hierarchy;
1373
1374 procedure summarize_information is
1375 begin
1376 dbms_snapshot.refresh('csp_sup_dem_sub_mv','C');
1377 end summarize_information;
1378
1379 PROCEDURE update_parts_loop IS
1380 begin
1381 dbms_snapshot.refresh('csp_sup_dem_pl_mv','C',atomic_refresh => false);
1382 end update_parts_loop;
1383
1384 procedure update_planning_nodes is
1385 begin
1386 dbms_snapshot.refresh('csp_sup_dem_pn_mv','C',atomic_refresh => false);
1387 end update_planning_nodes;
1388
1389
1390 -----------------------------------------------------------------
1391 PROCEDURE main
1392 (
1393 errbuf OUT nocopy VARCHAR2,
1394 retcode OUT nocopy NUMBER,
1395 p_api_version IN NUMBER,
1396 p_organization_id IN NUMBER,
1397 p_level_id IN VARCHAR2 default null
1398 ) IS
1399
1400 l_api_name CONSTANT VARCHAR2(30) := 'main';
1401 l_api_version CONSTANT NUMBER := 1.0;
1402 l_cursor NUMBER;
1403 l_ddl_string VARCHAR2(100);
1404
1405 BEGIN
1406 g_level_id := p_level_id;
1407
1408 if g_level_id is null then
1409 -- get_excess;
1410 -- get_excess_wh;
1411 get_onhand;
1412 get_onhand_wh;
1413 get_defective_wh;
1414 get_supply;
1415 get_supply_wh;
1416 get_internal_orders_out_wh;
1417 get_move_orders_in;
1418 get_move_orders_out;
1419 get_move_orders_wh;
1420 get_orders_out;
1421 get_open_sales_orders_wh;
1422 get_open_work_orders;
1423 get_open_work_orders_wh;
1424 summarize_information;
1425 update_parts_loop;
1426 update_hierarchy;
1427 update_planning_nodes;
1428 else
1429 mv_to_temp;
1430 get_onhand2;
1431 get_onhand_wh2;
1432 get_defective_wh2;
1433 get_supply2;
1434 get_supply_wh2;
1435 get_internal_orders_out_wh2;
1436 get_move_orders_in2;
1437 get_move_orders_out2;
1438 get_move_orders_wh2;
1439 get_orders_out2;
1440 get_open_sales_orders_wh2;
1441 get_open_work_orders2;
1442 get_open_work_orders_wh2;
1443 summarize_information;
1444 update_planning_nodes;
1445 end if;
1446
1447 commit;
1448
1449 EXCEPTION
1450 WHEN FND_API.G_EXC_ERROR THEN
1451 null;
1452 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1453 null;
1454 WHEN OTHERS THEN
1455 retcode := 3;
1456 errbuf := sqlerrm;
1457 IF FND_MSG_PUB.Check_Msg_Level
1458 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1459 THEN
1460 FND_MSG_PUB.Add_Exc_Msg
1461 ( G_PKG_NAME ,
1462 l_api_name
1463 );
1464 END IF;
1465 END main;
1466 END CSP_SUPPLY_DEMAND_PVT;