[Home] [Help]
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 (+)
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'
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
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
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||'%'
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
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||'%'
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'
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
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
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
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'
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
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||'%'
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
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
542: from mtl_supply ms,
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'
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
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
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
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
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
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
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
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
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
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
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)
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)
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 (+)
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||'%'
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'
1126: and cel.subinventory_code = csi.secondary_inventory_name
1127: and cel.organization_id = csi.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
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
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'
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,