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