[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
parts_loop_id,
hierarchy_node_id,
subinventory_code,
planning_parameters_id,
level_id,
onhand_bad,
onhand_good)
select
moq.inventory_item_id,
moq.organization_id,
csi.parts_loop_id,
csi.hierarchy_node_id,
moq.subinventory_code,
cpp.planning_parameters_id,
cpp.level_id,
sum(decode(csi.condition_type,'B',moq.transaction_quantity,null)),
sum(decode(csi.condition_type,'G',moq.transaction_quantity,null))
from mtl_onhand_quantities moq,
csp_sec_inventories csi,
csp_planning_parameters cpp
where csi.organization_id = moq.organization_id
and csi.secondary_inventory_name = moq.subinventory_code
and moq.inventory_item_id > 0
and csi.organization_id = cpp.organization_id (+)
and csi.secondary_inventory_name = cpp.secondary_inventory (+)
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by
moq.organization_id,
moq.subinventory_code,
moq.inventory_item_id,
csi.parts_loop_id,
csi.hierarchy_node_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
parts_loop_id,
hierarchy_node_id,
subinventory_code,
planning_parameters_id,
level_id,
onhand_bad,
onhand_good)
select
mmtt.inventory_item_id,
mmtt.organization_id,
cssdt.parts_loop_id,
cssdt.hierarchy_node_id,
mmtt.subinventory_code,
cssdt.planning_parameters_id,
cssdt.level_id,
sum(decode(cssdt.onhand_good,null,mmtt.primary_quantity,0)),
sum(decode(cssdt.onhand_bad,null,mmtt.primary_quantity,0))
from mtl_material_transactions_temp mmtt,
csp_sup_dem_sub_temp cssdt,
csp_planning_parameters cpp
where mmtt.inventory_item_id = cssdt.inventory_item_id
and mmtt.organization_id = cssdt.organization_id
and mmtt.subinventory_code = cssdt.subinventory_code
and mmtt.posting_flag = 'Y'
and mmtt.subinventory_code IS NOT NULL
and nvl(mmtt.transaction_status,0) <> 2
and mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
and cpp.organization_id (+) = mmtt.organization_id
and cpp.secondary_inventory (+) = mmtt.subinventory_code
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by
mmtt.inventory_item_id,
mmtt.organization_id,
cssdt.parts_loop_id,
cssdt.hierarchy_node_id,
mmtt.subinventory_code,
cssdt.planning_parameters_id,
cssdt.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
subinventory_code,
planning_parameters_id,
level_id,
onhand_bad,
onhand_good)
select
moq.inventory_item_id,
moq.organization_id,
moq.subinventory_code,
cpp.planning_parameters_id,
cpp.level_id,
sum(decode(csi.condition_type,'B',moq.transaction_quantity,null)),
sum(decode(csi.condition_type,'G',moq.transaction_quantity,null))
from mtl_onhand_quantities moq,
csp_sec_inventories csi,
csp_planning_parameters cpp
where csi.organization_id = moq.organization_id
and csi.secondary_inventory_name = moq.subinventory_code
and moq.inventory_item_id > 0
and csi.organization_id = cpp.organization_id
and csi.secondary_inventory_name = cpp.secondary_inventory
and cpp.level_id like g_level_id||'%'
group by
moq.organization_id,
moq.subinventory_code,
moq.inventory_item_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
subinventory_code,
planning_parameters_id,
level_id,
onhand_bad,
onhand_good)
select
mmtt.inventory_item_id,
mmtt.organization_id,
mmtt.subinventory_code,
cpp.planning_parameters_id,
cpp.level_id,
sum(decode(csi.condition_type,'B',mmtt.primary_quantity,0)),
sum(decode(csi.condition_type,'G',mmtt.primary_quantity,0))
from mtl_material_transactions_temp mmtt,
csp_sec_inventories csi,
csp_planning_parameters cpp
where mmtt.organization_id = csi.organization_id
and mmtt.subinventory_code = csi.secondary_inventory_name
and mmtt.posting_flag = 'Y'
and mmtt.subinventory_code IS NOT NULL
and nvl(mmtt.transaction_status,0) <> 2
and mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
and cpp.organization_id = mmtt.organization_id
and cpp.secondary_inventory = mmtt.subinventory_code
and cpp.level_id like g_level_id||'%'
group by
mmtt.inventory_item_id,
mmtt.organization_id,
mmtt.subinventory_code,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
onhand_good)
select
moq.inventory_item_id,
moq.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(moq.transaction_quantity)
from mtl_onhand_quantities moq,
csp_planning_parameters cpp
where moq.inventory_item_id > 0
and cpp.organization_id = moq.organization_id
and cpp.organization_type = 'W'
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by
moq.inventory_item_id,
moq.organization_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
onhand_good)
select
mmtt.inventory_item_id,
mmtt.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(nvl(mmtt.primary_quantity,0))
from mtl_material_transactions_temp mmtt,
csp_planning_parameters cpp
where mmtt.organization_id = cpp.organization_id
and cpp.organization_type = 'W'
and mmtt.posting_flag = 'Y'
and mmtt.subinventory_code IS NOT NULL
and nvl(mmtt.transaction_status,0) <> 2
and mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by
mmtt.inventory_item_id,
mmtt.organization_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
onhand_good)
select
moq.inventory_item_id,
moq.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(moq.transaction_quantity)
from mtl_onhand_quantities moq,
csp_planning_parameters cpp
where cpp.organization_id = moq.organization_id
and cpp.node_type = 'ORGANIZATION_WH'
and cpp.organization_type = 'W'
and cpp.level_id like g_level_id||'%'
group by
moq.inventory_item_id,
moq.organization_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
onhand_good)
select
mmtt.inventory_item_id,
mmtt.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(nvl(mmtt.primary_quantity,0))
from mtl_material_transactions_temp mmtt,
csp_planning_parameters cpp
where mmtt.organization_id = cpp.organization_id
and cpp.node_type = 'ORGANIZATION_WH'
and cpp.organization_type = 'W'
and mmtt.posting_flag = 'Y'
and mmtt.subinventory_code IS NOT NULL
and nvl(mmtt.transaction_status,0) <> 2
and mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
and cpp.level_id like g_level_id||'%'
group by
mmtt.inventory_item_id,
mmtt.organization_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
onhand_bad,
onhand_good)
select
moq.inventory_item_id,
moq.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(moq.transaction_quantity),
sum(moq.transaction_quantity)*-1
from mtl_onhand_quantities moq,
csp_sec_inventories csi,
csp_planning_parameters cpp
where csi.organization_id = moq.organization_id
and csi.secondary_inventory_name = moq.subinventory_code
and csi.condition_type = 'B'
and moq.inventory_item_id > 0
and csi.organization_id = cpp.organization_id
and cpp.organization_type = 'W'
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by
moq.organization_id,
moq.inventory_item_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
onhand_bad,
onhand_good)
select
mmtt.inventory_item_id,
mmtt.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(nvl(mmtt.primary_quantity,0)),
sum(nvl(mmtt.primary_quantity,0)) * -1
from mtl_material_transactions_temp mmtt,
csp_sec_inventories csi,
csp_planning_parameters cpp
where mmtt.organization_id = cpp.organization_id
and cpp.organization_type = 'W'
and csi.condition_type = 'B'
and csi.organization_id = cpp.organization_id
and mmtt.posting_flag = 'Y'
and mmtt.subinventory_code = csi.secondary_inventory_name
and nvl(mmtt.transaction_status,0) <> 2
and mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by
mmtt.inventory_item_id,
mmtt.organization_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
onhand_bad,
onhand_good)
select
moq.inventory_item_id,
moq.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(moq.transaction_quantity),
sum(moq.transaction_quantity)*-1
from mtl_onhand_quantities moq,
csp_sec_inventories csi,
csp_planning_parameters cpp
where csi.organization_id = moq.organization_id
and csi.secondary_inventory_name = moq.subinventory_code
and csi.condition_type = 'B'
and moq.inventory_item_id > 0
and csi.organization_id = cpp.organization_id
and cpp.organization_type = 'W'
and cpp.node_type = 'ORGANIZATION_WH'
and cpp.level_id like g_level_id||'%'
group by
moq.organization_id,
moq.inventory_item_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
onhand_bad,
onhand_good)
select
mmtt.inventory_item_id,
mmtt.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(nvl(mmtt.primary_quantity,0)),
sum(nvl(mmtt.primary_quantity,0)) * -1
from mtl_material_transactions_temp mmtt,
csp_sec_inventories csi,
csp_planning_parameters cpp
where mmtt.organization_id = cpp.organization_id
and mmtt.subinventory_code = csi.secondary_inventory_name
and csi.organization_id = cpp.organization_id
and csi.condition_type = 'B'
and cpp.organization_type = 'W'
and cpp.node_type = 'ORGANIZATION_WH'
and mmtt.posting_flag = 'Y'
and nvl(mmtt.transaction_status,0) <> 2
and mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by
mmtt.inventory_item_id,
mmtt.organization_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
parts_loop_id,
hierarchy_node_id,
subinventory_code,
planning_parameters_id,
level_id,
purchase_orders,
interorg_transf_in,
requisitions,
intransit_move_orders)
select
ms.item_id,
ms.to_organization_id,
csi.parts_loop_id,
csi.hierarchy_node_id,
ms.to_subinventory,
cpp.planning_parameters_id,
cpp.level_id,
sum(decode(ms.supply_type_code,'PO',to_org_primary_quantity,'RECEIVING',to_org_primary_quantity,0)) purchase_orders,
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,
sum(decode(ms.supply_type_code,'REQ',decode(nvl(prha.transferred_to_oe_flag,'N'),'N',to_org_primary_quantity,0),0)) requisitions,
sum(decode(ms.supply_type_code,'SHIPMENT',to_org_primary_quantity,0)) interorg_transfer
from mtl_supply ms,
po_requisition_headers_all prha,
csp_sec_inventories csi,
csp_planning_parameters cpp
where ms.req_header_id = prha.requisition_header_id(+)
and ms.to_organization_id = csi.organization_id
and ms.to_subinventory = csi.secondary_inventory_name
and cpp.organization_id (+) = csi.organization_id
and cpp.secondary_inventory (+) = csi.secondary_inventory_name
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by
ms.item_id,
ms.to_organization_id,
ms.to_subinventory,
csi.parts_loop_id,
csi.hierarchy_node_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
subinventory_code,
planning_parameters_id,
level_id,
purchase_orders,
interorg_transf_in,
requisitions,
intransit_move_orders)
select
ms.item_id,
ms.to_organization_id,
ms.to_subinventory,
cpp.planning_parameters_id,
cpp.level_id,
sum(decode(ms.supply_type_code,'PO',to_org_primary_quantity,'RECEIVING',to_org_primary_quantity,0)) purchase_orders,
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,
sum(decode(ms.supply_type_code,'REQ',decode(nvl(prha.transferred_to_oe_flag,'N'),'N',to_org_primary_quantity,0),0)) requisitions,
sum(decode(ms.supply_type_code,'SHIPMENT',to_org_primary_quantity,0)) interorg_transfer
from mtl_supply ms,
po_requisition_headers_all prha,
csp_planning_parameters cpp
where ms.req_header_id = prha.requisition_header_id(+)
and ms.to_organization_id = cpp.organization_id
and ms.to_subinventory = cpp.secondary_inventory
and cpp.level_id like g_level_id||'%'
group by
ms.item_id,
ms.to_organization_id,
ms.to_subinventory,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
purchase_orders,
interorg_transf_in,
requisitions,
intransit_move_orders)
select
ms.item_id,
ms.to_organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(decode(ms.supply_type_code,'PO',to_org_primary_quantity,'RECEIVING',to_org_primary_quantity,0)) purchase_orders,
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,
sum(decode(ms.supply_type_code,'REQ',decode(nvl(prha.transferred_to_oe_flag,'N'),'N',to_org_primary_quantity,0),0)) requisitions,
sum(decode(ms.supply_type_code,'SHIPMENT',to_org_primary_quantity,0)) interorg_transfer
from mtl_supply ms,
po_requisition_headers_all prha,
csp_planning_parameters cpp
where ms.req_header_id = prha.requisition_header_id(+)
and cpp.organization_id = ms.to_organization_id
and cpp.organization_type = 'W'
and ms.item_id > 0
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by
ms.item_id,
ms.to_organization_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
purchase_orders,
interorg_transf_in,
requisitions,
intransit_move_orders)
select
ms.item_id,
ms.to_organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(decode(ms.supply_type_code,'PO',to_org_primary_quantity,'RECEIVING',to_org_primary_quantity,0)) purchase_orders,
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,
sum(decode(ms.supply_type_code,'REQ',decode(nvl(prha.transferred_to_oe_flag,'N'),'N',to_org_primary_quantity,0),0)) requisitions,
sum(decode(ms.supply_type_code,'SHIPMENT',to_org_primary_quantity,0)) interorg_transfer
from mtl_supply ms,
po_requisition_headers_all prha,
csp_planning_parameters cpp
where ms.req_header_id = prha.requisition_header_id(+)
and ms.to_organization_id = cpp.organization_id
and ms.item_id > 0
and cpp.organization_type = 'W'
and cpp.node_type = 'ORGANIZATION_WH'
and cpp.level_id like g_level_id||'%'
group by
ms.item_id,
ms.to_organization_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
interorg_transf_out)
select
ms.item_id,
ms.from_organization_id,
cpp.planning_parameters_id,
cpp.level_id,
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))
from mtl_supply ms,
po_requisition_headers_all prha,
csp_planning_parameters cpp
where ms.req_header_id = prha.requisition_header_id
and cpp.organization_id = ms.from_organization_id
and cpp.organization_type = 'W'
and ms.item_id > 0
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by
ms.item_id,
ms.from_organization_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
interorg_transf_out)
select
ms.item_id,
ms.from_organization_id,
cpp.planning_parameters_id,
cpp.level_id,
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))
from mtl_supply ms,
po_requisition_headers_all prha,
csp_planning_parameters cpp
where prha.requisition_header_id = ms.req_header_id
and prha.transferred_to_oe_flag= 'Y'
and prha.type_lookup_code = 'INTERNAL'
and ms.from_organization_id = cpp.organization_id
and ms.supply_type_code = 'REQ'
and cpp.organization_type = 'W'
and cpp.node_type = 'ORGANIZATION_WH'
and cpp.level_id like g_level_id||'%'
group by
ms.item_id,
ms.from_organization_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
parts_loop_id,
hierarchy_node_id,
subinventory_code,
planning_parameters_id,
level_id,
work_orders)
select
wdj.primary_item_id,
wdj.organization_id,
csi.parts_loop_id,
csi.hierarchy_node_id,
csi.secondary_inventory_name,
cpp.planning_parameters_id,
cpp.level_id,
sum(start_quantity - nvl(quantity_completed,0) - nvl(quantity_scrapped, 0))
from wip_discrete_jobs wdj,
wip_entities we,
csp_sec_inventories csi,
csp_planning_parameters cpp
where wdj.status_type = 3
and we.wip_entity_id = wdj.wip_entity_id
and we.entity_type <> 6
and wdj.completion_subinventory = csi.secondary_inventory_name
and wdj.organization_id = csi.organization_id
and csi.organization_id = cpp.organization_id (+)
and csi.secondary_inventory_name = cpp.secondary_inventory (+)
and nvl(cpp.level_id,'%') like g_level_id||'%'
and wdj.primary_item_id > 0
group by
wdj.primary_item_id,
wdj.organization_id,
csi.secondary_inventory_name,
csi.parts_loop_id,
csi.hierarchy_node_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
subinventory_code,
planning_parameters_id,
level_id,
work_orders)
select
wdj.primary_item_id,
wdj.organization_id,
cpp.secondary_inventory,
cpp.planning_parameters_id,
cpp.level_id,
sum(start_quantity - nvl(quantity_completed,0) - nvl(quantity_scrapped, 0))
from wip_discrete_jobs wdj,
wip_entities we,
csp_planning_parameters cpp
where wdj.status_type = 3
and we.wip_entity_id = wdj.wip_entity_id
and we.entity_type <> 6
and wdj.completion_subinventory = cpp.secondary_inventory
and wdj.organization_id = cpp.organization_id
and cpp.level_id like g_level_id||'%'
and wdj.primary_item_id > 0
group by
wdj.primary_item_id,
wdj.organization_id,
cpp.secondary_inventory,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
work_orders)
select
wdj.primary_item_id,
wdj.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(start_quantity - nvl(quantity_completed,0) - nvl(quantity_scrapped, 0))
from wip_discrete_jobs wdj,
wip_entities we,
csp_planning_parameters cpp
where wdj.status_type = 3
and we.wip_entity_id = wdj.wip_entity_id
and we.entity_type <> 6
and wdj.organization_id = cpp.organization_id
and cpp.organization_type = 'W'
and nvl(cpp.level_id,'%') like g_level_id||'%'
and wdj.primary_item_id > 0
group by
wdj.primary_item_id,
wdj.organization_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
work_orders)
select
wdj.primary_item_id,
wdj.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(start_quantity - nvl(quantity_completed,0) - nvl(quantity_scrapped, 0))
from wip_discrete_jobs wdj,
wip_entities we,
csp_planning_parameters cpp
where wdj.status_type = 3
and we.wip_entity_id = wdj.wip_entity_id
and we.entity_type <> 6
and wdj.organization_id = cpp.organization_id
and cpp.organization_type = 'W'
and cpp.node_type = 'ORGANIZATION_WH'
and cpp.level_id like g_level_id||'%'
and wdj.primary_item_id > 0
group by
wdj.primary_item_id,
wdj.organization_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
parts_loop_id,
hierarchy_node_id,
subinventory_code,
planning_parameters_id,
level_id,
move_orders_in)
select
mtrl.inventory_item_id,
csi.organization_id,
csi.parts_loop_id,
csi.hierarchy_node_id,
csi.secondary_inventory_name,
cpp.planning_parameters_id,
cpp.level_id,
sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
null,
greatest(greatest(nvl(mtrl.quantity_detailed,0),nvl(mtrl.quantity,0))-nvl(mtrl.quantity_delivered,0),0),
mtrl.uom_code,
msib.primary_uom_code,
null,
null))
from mtl_txn_request_lines mtrl,
csp_sec_inventories csi,
mtl_system_items_b msib,
csp_planning_parameters cpp
where mtrl.organization_id = csi.organization_id
and mtrl.organization_id = msib.organization_id
and mtrl.inventory_item_id = msib.inventory_item_id
and mtrl.to_subinventory_code = csi.secondary_inventory_name
and mtrl.line_status in (3,7)
and csi.organization_id = cpp.organization_id (+)
and csi.secondary_inventory_name = cpp.secondary_inventory (+)
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by csi.organization_id,
csi.secondary_inventory_name,
mtrl.inventory_item_id,
csi.parts_loop_id,
csi.hierarchy_node_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
move_orders_in)
select
mtrl.inventory_item_id,
cpp.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
null,
greatest(greatest(nvl(mtrl.quantity_detailed,0),nvl(mtrl.quantity,0))-nvl(mtrl.quantity_delivered,0),0),
mtrl.uom_code,
msib.primary_uom_code,
null,
null))
from mtl_txn_request_lines mtrl,
mtl_system_items_b msib,
csp_planning_parameters cpp
where mtrl.to_organization_id = cpp.organization_id
and mtrl.to_subinventory_code = cpp.secondary_inventory
and mtrl.line_status in (3,7)
and msib.organization_id = cpp.organization_id
and msib.inventory_item_id = mtrl.inventory_item_id
and cpp.level_id like g_level_id||'%'
group by cpp.organization_id,
cpp.secondary_inventory,
mtrl.inventory_item_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
parts_loop_id,
hierarchy_node_id,
subinventory_code,
planning_parameters_id,
level_id,
move_orders_out)
select
mtrl.inventory_item_id,
csi.organization_id,
csi.parts_loop_id,
csi.hierarchy_node_id,
csi.secondary_inventory_name,
cpp.planning_parameters_id,
cpp.level_id,
sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
null,
greatest(greatest(nvl(mtrl.quantity_detailed,0),nvl(mtrl.quantity,0))-nvl(mtrl.quantity_delivered,0),0),
mtrl.uom_code,
msib.primary_uom_code,
null,
null))
from mtl_txn_request_lines mtrl,
csp_sec_inventories csi,
mtl_system_items_b msib,
csp_planning_parameters cpp
where mtrl.organization_id = csi.organization_id
and mtrl.organization_id = msib.organization_id
and mtrl.inventory_item_id = msib.inventory_item_id
and mtrl.from_subinventory_code = csi.secondary_inventory_name
and mtrl.line_status in (3,7)
and csi.organization_id = cpp.organization_id (+)
and csi.secondary_inventory_name = cpp.secondary_inventory (+)
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by csi.organization_id,
csi.secondary_inventory_name,
mtrl.inventory_item_id,
csi.parts_loop_id,
csi.hierarchy_node_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
subinventory_code,
planning_parameters_id,
level_id,
move_orders_out)
select
mtrl.inventory_item_id,
cpp.organization_id,
cpp.secondary_inventory,
cpp.planning_parameters_id,
cpp.level_id,
sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
null,
greatest(greatest(nvl(mtrl.quantity_detailed,0),nvl(mtrl.quantity,0))-nvl(mtrl.quantity_delivered,0),0),
mtrl.uom_code,
msib.primary_uom_code,
null,
null))
from mtl_txn_request_lines mtrl,
mtl_system_items_b msib,
csp_planning_parameters cpp
where mtrl.organization_id = cpp.organization_id
and mtrl.organization_id = msib.organization_id
and mtrl.inventory_item_id = msib.inventory_item_id
and mtrl.from_subinventory_code = cpp.secondary_inventory
and mtrl.line_status in (3,7)
and cpp.level_id like g_level_id||'%'
group by cpp.organization_id,
cpp.secondary_inventory,
mtrl.inventory_item_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
move_orders_in,
move_orders_out)
select
mtrl.inventory_item_id,
cpp.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
null,
nvl(mtrl.quantity,0)-nvl(mtrl.quantity_delivered,0),
mtrl.uom_code,
msib.primary_uom_code,
null,
null)),
sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
null,
nvl(mtrl.quantity,0)-nvl(mtrl.quantity_delivered,0),
mtrl.uom_code,
msib.primary_uom_code,
null,
null))
from mtl_txn_request_lines mtrl,
mtl_system_items_b msib,
csp_planning_parameters cpp
where mtrl.organization_id = cpp.organization_id
and mtrl.organization_id = msib.organization_id
and mtrl.inventory_item_id = msib.inventory_item_id
and mtrl.line_status in (3,7)
and cpp.organization_type = 'W'
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by cpp.organization_id,
mtrl.inventory_item_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
move_orders_in,
move_orders_out)
select
mtrl.inventory_item_id,
cpp.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
null,
nvl(mtrl.quantity,0)-nvl(mtrl.quantity_delivered,0),
mtrl.uom_code,
msib.primary_uom_code,
null,
null)),
sum(inv_convert.inv_um_convert(mtrl.inventory_item_id,
null,
nvl(mtrl.quantity,0)-nvl(mtrl.quantity_delivered,0),
mtrl.uom_code,
msib.primary_uom_code,
null,
null))
from mtl_txn_request_lines mtrl,
mtl_system_items_b msib,
csp_planning_parameters cpp
where mtrl.organization_id = cpp.organization_id
and mtrl.organization_id = msib.organization_id
and mtrl.inventory_item_id = msib.inventory_item_id
and mtrl.line_status in (3,7)
and cpp.node_type = 'ORGANIZATION_WH'
and cpp.organization_type = 'W'
and cpp.level_id like g_level_id||'%'
group by cpp.organization_id,
mtrl.inventory_item_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
parts_loop_id,
hierarchy_node_id,
subinventory_code,
planning_parameters_id,
level_id,
sales_orders,
interorg_transf_out)
select
ola.inventory_item_id,
csi.organization_id,
csi.parts_loop_id,
csi.hierarchy_node_id,
csi.secondary_inventory_name,
cpp.planning_parameters_id,
cpp.level_id,
decode(ola.order_source_id,10,0,
sum(nvl(ola.ordered_quantity,0) -
nvl(ola.cancelled_quantity,0) -
nvl(ola.shipped_quantity,0))) sales_orders,
decode(ola.order_source_id,10,
sum(nvl(ola.ordered_quantity,0) -
nvl(ola.cancelled_quantity,0) -
nvl(ola.shipped_quantity,0)),0) internal_orders_out
from oe_order_lines_all ola,
csp_sec_inventories csi,
csp_planning_parameters cpp
where ola.ship_from_org_id = csi.organization_id
and ola.subinventory = csi.secondary_inventory_name
and ola.open_flag = 'Y'
and csi.organization_id = cpp.organization_id (+)
and csi.secondary_inventory_name = cpp.secondary_inventory (+)
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by
csi.organization_id,
csi.secondary_inventory_name,
csi.parts_loop_id,
csi.hierarchy_node_id,
ola.inventory_item_id,
cpp.planning_parameters_id,
cpp.level_id,
ola.order_source_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
subinventory_code,
planning_parameters_id,
level_id,
sales_orders,
interorg_transf_out)
select
ola.inventory_item_id,
cpp.organization_id,
cpp.secondary_inventory,
cpp.planning_parameters_id,
cpp.level_id,
decode(ola.order_source_id,10,0,
sum(nvl(ola.ordered_quantity,0) -
nvl(ola.cancelled_quantity,0) -
nvl(ola.shipped_quantity,0))) sales_orders,
decode(ola.order_source_id,10,
sum(nvl(ola.ordered_quantity,0) -
nvl(ola.cancelled_quantity,0) -
nvl(ola.shipped_quantity,0)),0) internal_orders_out
from oe_order_lines_all ola,
csp_planning_parameters cpp
where ola.ship_from_org_id = cpp.organization_id
and ola.subinventory = cpp.secondary_inventory
and ola.open_flag = 'Y'
and cpp.level_id like g_level_id||'%'
group by
cpp.organization_id,
cpp.secondary_inventory,
ola.inventory_item_id,
cpp.planning_parameters_id,
cpp.level_id,
ola.order_source_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
parts_loop_id,
hierarchy_node_id,
subinventory_code,
planning_parameters_id,
level_id,
excess_quantity)
select
cel.inventory_item_id,
cel.organization_id,
csi.parts_loop_id,
csi.hierarchy_node_id,
cel.subinventory_code,
cpp.planning_parameters_id,
cpp.level_id,
sum(nvl(excess_quantity,0))
from csp_excess_lists cel,
csp_sec_inventories csi,
csp_planning_parameters cpp
where cel.condition_code = 'G'
and cel.excess_status = 'O'
and cel.subinventory_code = csi.secondary_inventory_name
and cel.organization_id = csi.organization_id
and csi.organization_id = cpp.organization_id (+)
and csi.secondary_inventory_name = cpp.secondary_inventory (+)
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by
cel.inventory_item_id,
cel.organization_id,
cel.subinventory_code,
csi.parts_loop_id,
csi.hierarchy_node_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
excess_quantity)
select
cel.inventory_item_id,
cel.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(cel.excess_quantity)
from csp_excess_lists cel,
csp_planning_parameters cpp
where cel.condition_code = 'G'
and cel.excess_status = 'O'
and cel.subinventory_code is null
and cel.organization_id = cpp.organization_id
and cpp.organization_type = 'W'
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by
cel.inventory_item_id,
cel.organization_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
sales_orders)
select
ola.inventory_item_id,
cpp.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(nvl(ola.ordered_quantity,0) -
nvl(ola.cancelled_quantity,0) -
nvl(ola.shipped_quantity,0))
from oe_order_lines_all ola,
csp_planning_parameters cpp
where ola.ship_from_org_id = cpp.organization_id
and cpp.organization_type = 'W'
and ola.open_flag = 'Y'
and nvl(ola.order_source_id,0) <> 10
and nvl(cpp.level_id,'%') like g_level_id||'%'
group by
cpp.organization_id,
ola.inventory_item_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
sales_orders)
select
ola.inventory_item_id,
cpp.organization_id,
cpp.planning_parameters_id,
cpp.level_id,
sum(nvl(ola.ordered_quantity,0) -
nvl(ola.cancelled_quantity,0) -
nvl(ola.shipped_quantity,0))
from oe_order_lines_all ola,
csp_planning_parameters cpp
where ola.ship_from_org_id = cpp.organization_id
and cpp.organization_type = 'W'
and cpp.node_type = 'ORGANIZATION_WH'
and ola.open_flag = 'Y'
and nvl(ola.order_source_id,0) <> 10
and cpp.level_id like g_level_id||'%'
group by
cpp.organization_id,
ola.inventory_item_id,
cpp.planning_parameters_id,
cpp.level_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
parts_loop_id,
hierarchy_node_id,
subinventory_code,
planning_parameters_id,
level_id,
purchase_orders,
sales_orders,
requisitions,
interorg_transf_in,
onhand_good,
onhand_bad,
intransit_move_orders,
interorg_transf_out,
move_orders_in,
move_orders_out,
work_orders,
excess_quantity)
select
inventory_item_id,
organization_id,
parts_loop_id,
hierarchy_node_id,
subinventory_code,
planning_parameters_id,
level_id,
purchase_orders,
sales_orders,
requisitions,
interorg_transf_in,
onhand_good,
onhand_bad,
intransit_move_orders,
interorg_transf_out,
move_orders_in,
move_orders_out,
work_orders,
excess_quantity
from csp_sup_dem_subs
where nvl(level_id,'a') not like g_level_id||'%';
/* update_hierarchy */
--------------------------------------------------------------------
PROCEDURE update_hierarchy IS
l_level number := 1;
insert into csp_sup_dem_rh_temp(
level_id,
organization_id,
inventory_item_id,
hierarchy_node_id,
purchase_orders,
sales_orders,
requisitions,
interorg_transf_in,
onhand_good,
onhand_bad,
intransit_move_orders,
interorg_transf_out,
move_orders_in,
move_orders_out,
work_orders)
select l_level,
min(organization_id),
inventory_item_id,
hierarchy_node_id,
sum(nvl(purchase_orders,0)),
sum(nvl(sales_orders,0)),
sum(nvl(requisitions,0)),
sum(nvl(interorg_transf_in,0)),
sum(nvl(onhand_good,0)),
sum(nvl(onhand_bad,0)),
sum(nvl(intransit_move_orders,0)),
sum(nvl(interorg_transf_out,0)),
sum(nvl(move_orders_in,0)),
sum(nvl(move_orders_out,0)),
sum(nvl(work_orders,0))
from csp_sup_dem_subs
where hierarchy_node_id > 0
group by
inventory_item_id,
hierarchy_node_id;
insert into csp_sup_dem_rh_temp(
level_id,
organization_id,
inventory_item_id,
hierarchy_node_id,
purchase_orders,
sales_orders,
requisitions,
interorg_transf_in,
onhand_good,
onhand_bad,
intransit_move_orders,
interorg_transf_out,
move_orders_in,
move_orders_out,
work_orders)
select l_level+1,
min(ctsd.organization_id),
inventory_item_id,
parent_node_id,
sum(nvl(purchase_orders,0)),
sum(nvl(sales_orders,0)),
sum(nvl(requisitions,0)),
sum(nvl(interorg_transf_in,0)),
sum(nvl(onhand_good,0)),
sum(nvl(onhand_bad,0)),
sum(nvl(intransit_move_orders,0)),
sum(nvl(interorg_transf_out,0)),
sum(nvl(move_orders_in,0)),
sum(nvl(move_orders_out,0)),
sum(nvl(work_orders,0))
from csp_sup_dem_rh_temp ctsd,
csp_rep_hierarchies crh
where level_id = l_level
and crh.hierarchy_node_id = ctsd.hierarchy_node_id
and crh.parent_node_id > 0
group by
inventory_item_id,
parent_node_id;
insert into csp_sup_dem_rhs(
inventory_item_id,
organization_id,
hierarchy_node_id,
node_name,
parent_node_id,
purchase_orders,
sales_orders,
requisitions,
interorg_transf_in,
onhand_good,
onhand_bad,
intransit_move_orders,
interorg_transf_out,
move_orders_in,
move_orders_out,
work_orders)
select crsdt.inventory_item_id,
min(crsdt.organization_id) organization_id,
crsdt.hierarchy_node_id,
crh.node_name,
crh.parent_node_id,
sum(nvl(crsdt.purchase_orders,0)) purchase_orders,
sum(nvl(crsdt.sales_orders,0)) sales_orders,
sum(nvl(crsdt.requisitions,0)) requisitions,
sum(nvl(crsdt.interorg_transf_in,0)) interorg_transf_in,
sum(nvl(greatest(crsdt.onhand_good,0),0)) onhand_good,
sum(nvl(greatest(crsdt.onhand_bad,0),0)) onhand_bad,
sum(nvl(crsdt.intransit_move_orders,0)) intransit_move_orders,
sum(nvl(crsdt.interorg_transf_out,0)) interorg_transf_out,
sum(nvl(crsdt.move_orders_in,0)) move_orders_in,
sum(nvl(crsdt.move_orders_out,0)) move_orders_out,
sum(nvl(crsdt.work_orders,0)) work_orders
from csp_sup_dem_rh_temp crsdt,
csp_rep_hierarchies crh
where crh.hierarchy_node_id = crsdt.hierarchy_node_id
group by
crsdt.inventory_item_id,
crsdt.hierarchy_node_id,
crh.node_name,
crh.parent_node_id;
end update_hierarchy;
insert into csp_sup_dem_subs(
inventory_item_id,
organization_id,
parts_loop_id,
hierarchy_node_id,
planning_parameters_id,
level_id,
subinventory_code,
purchase_orders,
sales_orders,
requisitions,
interorg_transf_in,
onhand_good,
onhand_bad,
intransit_move_orders,
interorg_transf_out,
move_orders_in,
move_orders_out,
work_orders,
excess_quantity)
select
inventory_item_id,
organization_id,
parts_loop_id,
hierarchy_node_id,
planning_parameters_id,
level_id,
subinventory_code,
sum(nvl(purchase_orders,0)) purchase_orders,
sum(nvl(sales_orders,0)) sales_orders,
sum(nvl(requisitions,0)) requisitions,
sum(nvl(interorg_transf_in,0)) interorg_transf_in,
sum(nvl(onhand_good,0)) onhand_good,
sum(nvl(onhand_bad,0)) onhand_bad,
sum(nvl(intransit_move_orders,0)) intransit_move_orders,
sum(nvl(interorg_transf_out,0)) interorg_transf_out,
sum(nvl(move_orders_in,0)) move_orders_in,
sum(nvl(move_orders_out,0)) move_orders_out,
sum(nvl(work_orders,0)) work_orders,
sum(nvl(excess_quantity,0)) excess_quantity
from csp_sup_dem_sub_temp
group by
inventory_item_id,
organization_id,
parts_loop_id,
hierarchy_node_id,
planning_parameters_id,
level_id,
subinventory_code;
PROCEDURE update_parts_loop IS
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUP_DEM_PLS';
insert into csp_sup_dem_pls(
inventory_item_id,
organization_id,
parts_loop_id,
purchase_orders,
sales_orders,
requisitions,
interorg_transf_in,
onhand_good,
onhand_bad,
intransit_move_orders,
interorg_transf_out,
move_orders_in,
move_orders_out,
work_orders)
select
inventory_item_id,
min(organization_id) organization_id,
parts_loop_id,
sum(nvl(purchase_orders,0)) purchase_orders,
sum(nvl(sales_orders,0)) sales_orders,
sum(nvl(requisitions,0)) requisitions,
sum(nvl(interorg_transf_in,0)) interorg_transf_in,
sum(nvl(greatest(onhand_good,0),0)) onhand_good,
sum(nvl(greatest(onhand_bad,0),0)) onhand_bad,
sum(nvl(intransit_move_orders,0)) intransit_move_orders,
sum(nvl(interorg_transf_out,0)) interorg_transf_out,
sum(nvl(move_orders_in,0)) move_orders_in,
sum(nvl(move_orders_out,0)) move_orders_out,
sum(nvl(work_orders,0)) work_orders
from csp_sup_dem_subs
where parts_loop_id > 0
group by
inventory_item_id,
parts_loop_id;
end update_parts_loop;
procedure update_planning_nodes is
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUP_DEM_PNS';
insert into csp_sup_dem_pns(
inventory_item_id,
organization_id,
planning_parameters_id,
level_id,
node_name,
parent_node_id,
purchase_orders,
sales_orders,
requisitions,
interorg_transf_in,
onhand_good,
onhand_bad,
intransit_move_orders,
interorg_transf_out,
move_orders_in,
move_orders_out,
work_orders,
excess_quantity)
select
csdsm.inventory_item_id,
min(csdsm.organization_id) organization_id,
cpp.planning_parameters_id,
cpp.level_id,
cpp.node_name,
cpp.parent_node_id,
sum(nvl(csdsm.purchase_orders,0)) purchase_orders,
sum(nvl(csdsm.sales_orders,0)) sales_orders,
sum(nvl(csdsm.requisitions,0)) requisitions,
sum(nvl(csdsm.interorg_transf_in,0)) interorg_transf_in,
sum(nvl(greatest(csdsm.onhand_good,0),0)) onhand_good,
sum(nvl(greatest(csdsm.onhand_bad,0),0)) onhand_bad,
sum(nvl(csdsm.intransit_move_orders,0)) intransit_move_orders,
sum(nvl(csdsm.interorg_transf_out,0)) interorg_transf_out,
sum(nvl(csdsm.move_orders_in,0)) move_orders_in,
sum(nvl(csdsm.move_orders_out,0)) move_orders_out,
sum(nvl(csdsm.work_orders,0)) work_orders,
sum(nvl(csdsm.excess_quantity,0)) excess_quantity
from csp_sup_dem_subs csdsm,
csp_planning_parameters cpp
where csdsm.level_id like cpp.level_id||'%'
group by
csdsm.inventory_item_id,
cpp.planning_parameters_id,
cpp.level_id,
cpp.node_name,
cpp.parent_node_id;
end update_planning_nodes;
update_parts_loop;
update_hierarchy;
update_planning_nodes;
update_planning_nodes;