[Home] [Help]
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,
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,
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
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,
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,
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,
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,
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,
239: moq.organization_id,
240: cpp.planning_parameters_id,
241: cpp.level_id;
242:
243: insert into csp_sup_dem_sub_temp(
244: inventory_item_id,
245: organization_id,
246: planning_parameters_id,
247: 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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
976: 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,
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,
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,
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,
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,
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,
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,
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,