[Home] [Help]
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 (+)
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'
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
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
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||'%'
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
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||'%'
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'
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
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
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
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'
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
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||'%'
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
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'
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
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
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
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
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
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
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
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
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
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
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)
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)
1039: nvl(ola.cancelled_quantity,0) -
1040: nvl(ola.shipped_quantity,0)),0) internal_orders_out
1041: from oe_order_lines_all ola,
1042: csp_sec_inventories csi,
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 (+)
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||'%'
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
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
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
1213: sum(nvl(ola.ordered_quantity,0) -
1214: nvl(ola.cancelled_quantity,0) -
1215: nvl(ola.shipped_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'