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,
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,
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
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
519: end get_supply_wh;
520:
521: PROCEDURE get_supply_wh2 IS
522: begin
523: insert into csp_sup_dem_sub_temp(
524: inventory_item_id,
525: organization_id,
526: planning_parameters_id,
527: 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,
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,
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,
659: END get_open_work_orders;
660:
661: PROCEDURE get_open_work_orders2 IS
662: begin
663: insert into csp_sup_dem_sub_temp(
664: inventory_item_id,
665: organization_id,
666: subinventory_code,
667: planning_parameters_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,
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,
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,
803: END get_move_orders_in;
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,