[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Nvl(reservable_type,1) INTO l_reservable_type
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code;
SELECT NVL(reservable_type,1) INTO l_reservable_type
FROM mtl_item_locations
WHERE organization_id = p_organization_id
AND inventory_location_id = p_inventory_location_id;
SELECT NVL(reservable_type,1) INTO l_reservable_type
FROM mtl_lot_numbers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number;
SELECT NVL(mms.reservable_type, 1) INTO l_reservable_type
FROM mtl_onhand_quantities_detail moqd, mtl_material_statuses mms
WHERE moqd.status_id = mms.status_id
AND moqd.status_id is not null
AND moqd.inventory_item_id = p_inventory_item_id
AND moqd.organization_id = p_organization_id
AND moqd.subinventory_code = p_sub_code
AND nvl(moqd.locator_id, -9999) = nvl(p_loc_id, -9999)
AND nvl(moqd.lot_number, '@@@@') = nvl(p_lot_number, '@@@@')
AND nvl(moqd.lpn_id, -9999) = nvl(p_lpn_id, -9999)
AND rownum = 1;
SELECT Nvl(reservable_type,2) INTO l_reservable_type
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT NVL(msinv.reservable_type, 1)
FROM mtl_secondary_inventories msinv
WHERE msinv.organization_id = org_id
AND msinv.secondary_inventory_name = subinv;
SELECT NVL(mil.reservable_type, 1)
FROM mtl_item_locations mil
WHERE mil.organization_id = org_id
AND mil.inventory_location_id = loct_id;
SELECT NVL(mln.reservable_type, 1)
FROM mtl_lot_numbers mln
WHERE mln.inventory_item_id = item_id
AND mln.organization_id = org_id
AND mln.lot_number = lot;
SELECT NVL(mms.reservable_type, 1)
FROM mtl_onhand_quantities_detail moqd, mtl_material_statuses mms
WHERE moqd.status_id = mms.status_id
and moqd.status_id is not null
AND moqd.inventory_item_id = item_id
AND moqd.organization_id = org_id
AND moqd.subinventory_code = subinv
and nvl(moqd.locator_id, -9999) = nvl(loct_id, -9999)
AND nvl(moqd.lot_number, '@@@@') = nvl(lot, '@@@@')
AND nvl(moqd.lpn_id, -9999) = nvl(p_lpn_id, -9999)
AND rownum = 1;*/
l_update_quantity NUMBER;
l_update_quantity2 NUMBER;
l_atr_update_quantity NUMBER:=0; -- Bug 9644285
l_atr_update_quantity2 NUMBER:=0; -- Bug 9644285
l_update_quantity:= p_primary_quantity;
l_update_quantity2 := p_secondary_quantity;
l_update_quantity := l_old_factor;
l_update_quantity2 := l_old_factor2;
l_update_quantity:= 0.0 - l_new_factor;
l_update_quantity2 := 0.0 - l_new_factor2;
l_update_quantity:= 0;
l_update_quantity2 := 0;
l_atr_update_quantity:=0;
l_atr_update_quantity:= l_atr_old_factor - l_atr_new_factor ;
l_atr_update_quantity := l_atr_old_factor;
l_atr_update_quantity:= 0.0 - l_atr_new_factor;
l_atr_update_quantity:= 0;
l_atr_update_quantity2:=0;
l_atr_update_quantity2:= l_atr_old_factor2 - l_atr_new_factor2 ;
l_atr_update_quantity2 := l_atr_old_factor2;
l_atr_update_quantity2:= 0.0 - l_atr_new_factor2;
l_atr_update_quantity2:= 0;
g_nodes(l_loop_index).att := g_nodes(l_loop_index).att + l_update_quantity;
g_nodes(l_loop_index).satt := NVL(g_nodes(l_loop_index).satt, 0) + l_update_quantity2;
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_atr_update_quantity; -- Bug 9644285
g_nodes(l_loop_index).satr := g_nodes(l_loop_index).satr + l_atr_update_quantity2; -- Bug 9644285
l_update_quantity:= 0.0 - p_primary_quantity;
l_update_quantity2:= 0.0 - p_secondary_quantity;
l_update_quantity := l_old_factor;
l_update_quantity2 := l_old_factor2;
l_update_quantity:= 0.0 - l_new_factor;
l_update_quantity2:= 0.0 - l_new_factor2;
l_update_quantity:= 0;
l_update_quantity2:= 0;
l_atr_update_quantity:= 0;
l_atr_update_quantity:= 0.0 - p_primary_quantity;
l_atr_update_quantity := l_atr_old_factor;
l_atr_update_quantity:= 0.0 - l_atr_new_factor;
l_atr_update_quantity:= 0;
l_atr_update_quantity2:= 0;
l_atr_update_quantity2:= 0.0 - p_secondary_quantity;
l_atr_update_quantity2 := l_atr_old_factor2;
l_atr_update_quantity2:= 0.0 - l_atr_new_factor2;
l_atr_update_quantity2:= 0;
print_debug('in add_qty,X rsv=TRUE, newATR='||g_nodes(l_loop_index).atr||' + '||l_update_quantity);
print_debug('in add_qty,X rsv=FALSE, newATR='||g_nodes(l_loop_index).atr||' + '||l_update_quantity);
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_atr_update_quantity; -- Bug 9644285
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_atr_update_quantity2; -- Bug 9644285
g_nodes(l_loop_index).att := g_nodes(l_loop_index).att + l_update_quantity;
g_nodes(l_loop_index).satt := NVL(g_nodes(l_loop_index).satt, 0) + l_update_quantity2;
l_update_quantity:= 0.0 - p_primary_quantity;
l_update_quantity2:= 0.0 - p_secondary_quantity;
l_update_quantity := l_old_factor;
l_update_quantity2 := l_old_factor2;
l_update_quantity:= 0.0 - l_new_factor;
l_update_quantity2:= 0.0 - l_new_factor2;
l_update_quantity:= 0;
l_update_quantity2:= 0;
l_atr_update_quantity:= 0;
l_atr_update_quantity:= l_atr_old_factor - l_atr_new_factor; -- Bug 9644285
l_atr_update_quantity := l_atr_old_factor;
l_atr_update_quantity:= 0.0 - l_atr_new_factor;
l_atr_update_quantity:= 0;
l_atr_update_quantity2:= 0;
l_atr_update_quantity2:= l_atr_old_factor2 - l_atr_new_factor2; -- Bug 9644285
l_atr_update_quantity2 := l_atr_old_factor2;
l_atr_update_quantity2:= 0.0 - l_atr_new_factor2;
l_atr_update_quantity2:= 0;
g_nodes(l_loop_index).att := g_nodes(l_loop_index).att + l_update_quantity;
g_nodes(l_loop_index).satt := NVL(g_nodes(l_loop_index).satt, 0) + l_update_quantity2;
print_debug('in add_qty,3 newATR='||g_nodes(l_loop_index).atr||' + '||l_atr_update_quantity); -- Bug 9644285
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_atr_update_quantity; -- Bug 9644285
print_debug('in add_qty,3 newSATR='||g_nodes(l_loop_index).satr||' + '||l_atr_update_quantity2); -- Bug 9644285 invConv change
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_atr_update_quantity2; -- Bug 9644285 invConv change
g_nodes(l_loop_index).att := g_nodes(l_loop_index).att + l_update_quantity;
g_nodes(l_loop_index).satt := NVL(g_nodes(l_loop_index).satt, 0) + l_update_quantity2;
print_debug('in add_qty,3 newATR='||g_nodes(l_loop_index).atr||' + '||l_atr_update_quantity); -- Bug 9644285
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_atr_update_quantity; -- Bug 9644285
print_debug('in add_qty,3 newSATR='||g_nodes(l_loop_index).satr||' + '||l_atr_update_quantity2); -- Bug 9644285 invConv change
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_atr_update_quantity2; -- Bug 9644285 invConv change
g_nodes(l_loop_index).att := g_nodes(l_loop_index).att + l_update_quantity;
g_nodes(l_loop_index).satt := NVL(g_nodes(l_loop_index).satt, 0) + l_update_quantity2;
print_debug('in add_qty,3 newATR='||g_nodes(l_loop_index).atr||' + '||l_atr_update_quantity); -- Bug 9644285
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_atr_update_quantity; -- Bug 9644285
print_debug('in add_qty,3 newSATR='||g_nodes(l_loop_index).satr||' + '||l_atr_update_quantity2); -- Bug 9644285 invConv change
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_atr_update_quantity2; -- Bug 9644285 invConv change
g_nodes(l_loop_index).att := g_nodes(l_loop_index).att + l_update_quantity;
g_nodes(l_loop_index).satt := NVL(g_nodes(l_loop_index).satt, 0) + l_update_quantity2;
print_debug('in add_qty,3 newATR='||g_nodes(l_loop_index).atr||' + '||l_atr_update_quantity); -- Bug 9644285
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_atr_update_quantity; -- Bug 9644285
print_debug('in add_qty,3 newSATR='||g_nodes(l_loop_index).satr||' + '||l_atr_update_quantity2); -- Bug 9644285 invConv change
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_atr_update_quantity2; -- Bug 9644285 invConv change
g_nodes(l_loop_index).att := g_nodes(l_loop_index).att + l_update_quantity;
g_nodes(l_loop_index).satt := NVL(g_nodes(l_loop_index).satt, 0) + l_update_quantity2;
print_debug('in add_qty,3 newATR='||g_nodes(l_loop_index).atr||' + '||l_atr_update_quantity); -- Bug 9644285
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_atr_update_quantity; -- Bug 9644285
print_debug('in add_qty,3 newSATR='||g_nodes(l_loop_index).satr||' + '||l_atr_update_quantity2); -- Bug 9644285 invConv change
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_atr_update_quantity2; -- Bug 9644285 invConv change
g_nodes(l_loop_index).att := g_nodes(l_loop_index).att + l_update_quantity;
g_nodes(l_loop_index).satt := NVL(g_nodes(l_loop_index).satt, 0) + l_update_quantity2;
print_debug('in add_qty,3 newATR='||g_nodes(l_loop_index).atr||' + '||l_atr_update_quantity); -- Bug 9644285
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_atr_update_quantity; -- Bug 9644285
print_debug('in add_qty,3 newSATR='||g_nodes(l_loop_index).satr||' + '||l_atr_update_quantity2); -- Bug 9644285 invConv change
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_atr_update_quantity2; -- Bug 9644285 invConv change
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, x.revision revision
, NULL lot_number
, NULL lot_expiration_date
, x.subinventory_code subinventory_code
, sub.reservable_type reservable_type
, x.locator_id locator_id
, SUM(x.primary_quantity) primary_quantity
, SUM(x.secondary_quantity) secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
, NULL is_reservable_lot --Bug#8713821
FROM (
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, decode(l_revision_control, 2, NULL, x.revision) revision
, NULL lot_number
, x.subinventory_code subinventory_code
, x.locator_id locator_id
, SUM(x.primary_quantity) primary_quantity
, SUM(x.secondary_quantity) secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
FROM (
-- reservations
SELECT
mr.organization_id organization_id
, mr.inventory_item_id inventory_item_id
, mr.revision revision
, mr.lot_number lot_number
, mr.subinventory_code subinventory_code
, mr.locator_id locator_id
, mr.primary_reservation_quantity - Nvl(mr.detailed_quantity,0) primary_quantity
, mr.secondary_reservation_quantity - Nvl(mr.secondary_detailed_quantity,0) secondary_quantity
, 3 quantity_type
, to_number(NULL) cost_group_id
, lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM mtl_reservations mr
WHERE
Nvl(mr.supply_source_type_id, 13) = 13
AND mr.primary_reservation_quantity > Nvl(mr.detailed_quantity,0)
AND ((l_no_lpn_reservations <>1) OR (l_no_lpn_reservations = 1 AND mr.lpn_id IS NULL))
AND (l_tree_mode <> 3 OR
(l_tree_mode = 3
AND NOT ( l_demand_source_type_id = mr.demand_source_type_id
AND l_demand_source_header_id = mr.demand_source_header_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mr.demand_source_line_id,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mr.demand_source_name,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mr.demand_source_delivery,-9999)
)
)
)
UNION ALL
-- onhand quantities
SELECT
moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.revision revision
, moq.lot_number lot_number
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, moq.primary_transaction_quantity
, moq.secondary_transaction_quantity
, 1 quantity_type
, moq.cost_group_id cost_group_id
, moq.lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_onhand_quantities_detail moq
UNION ALL
-- pending transactions in mmtt
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, NULL lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status)
, 2, 1, Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,Sign(mmtt.primary_quantity)))
* round(Abs(mmtt.primary_quantity),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status)
, 2, 1, Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,Sign(mmtt.secondary_transaction_quantity)))
* round(Abs(mmtt.secondary_transaction_quantity),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
--, NVL(mmtt.allocated_lpn_id,NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt,
mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34))
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6,24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
-- receiving side of transfers
-- added 5/23/00
-- if quantity is in an lpn, then it is containerized
-- Bug 7658493, If wms task is in loaded status, consider allocation like pending transaction.
SELECT
Decode(mmtt.transaction_action_id, 3, mmtt.transfer_organization, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, NULL lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs(mmtt.primary_quantity),5)
, round(Abs(mmtt.secondary_transaction_quantity),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt
WHERE
mmtt.posting_flag = 'Y'
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
--bug 3581133
AND mmtt.wip_supply_type IS NULL
) x
WHERE x.organization_id = l_organization_id
AND x.inventory_item_id = l_inventory_item_id
GROUP BY
x.organization_id, x.inventory_item_id, x.revision
, x.lot_number, x.subinventory_code, x.locator_id
, x.quantity_type, x.cost_group_id, x.lpn_id
, x.transaction_action_id, x.transfer_subinventory_code
, x.transfer_locator_id
) x
, mtl_secondary_inventories sub
WHERE
x.organization_id = sub.organization_id (+)
AND x.subinventory_code = sub.secondary_inventory_name (+)
AND (l_asset_subs_only = 2 OR NVL(sub.asset_inventory,1) = 1)
AND ( (l_onhand_source = 1 AND Nvl(sub.inventory_atp_code, 1) = 1)
OR (l_onhand_source = 2 AND Nvl(sub.availability_type, 1) = 1 )
OR l_onhand_source =3
OR (l_onhand_source = 4 AND (nvl(sub.inventory_atp_code,1) = 1 AND nvl(sub.availability_type,1)=1))
)
GROUP BY
x.organization_id
, x.inventory_item_id
, x.revision
, NULL
, NULL
, sub.reservable_type
, x.subinventory_code
, NULL
, x.locator_id
, x.quantity_type
, x.cost_group_id
, x.lpn_id
, x.transaction_action_id
, x.transfer_subinventory_code
, x.transfer_locator_id;
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, x.revision revision
, NULL lot_number
, NULL lot_expiration_date
, x.subinventory_code subinventory_code
, sub.reservable_type reservable_type
, x.locator_id locator_id
, SUM(x.primary_quantity) primary_quantity
, SUM(x.secondary_quantity) secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
, x.status_id
, NULL is_reservable_lot --Bug#8713821
FROM (
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, decode(l_revision_control, 2, NULL, x.revision) revision
, NULL lot_number
, x.subinventory_code subinventory_code
, x.locator_id locator_id
, SUM(x.primary_quantity) primary_quantity
, SUM(x.secondary_quantity) secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
, x.status_id
FROM (
-- reservations
SELECT
mr.organization_id organization_id
, mr.inventory_item_id inventory_item_id
, mr.revision revision
, mr.lot_number lot_number
, mr.subinventory_code subinventory_code
, mr.locator_id locator_id
, mr.primary_reservation_quantity - Nvl(mr.detailed_quantity,0) primary_quantity
, mr.secondary_reservation_quantity - Nvl(mr.secondary_detailed_quantity,0) secondary_quantity
, 3 quantity_type
, to_number(NULL) cost_group_id
, lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
, to_number(NULL) status_id -- Onhand Material Status Support
FROM mtl_reservations mr
WHERE
Nvl(mr.supply_source_type_id, 13) = 13
AND mr.primary_reservation_quantity >
Nvl(mr.detailed_quantity,0)
AND ((l_no_lpn_reservations <>1)
OR (l_no_lpn_reservations = 1 AND mr.lpn_id IS NULL))
AND (l_tree_mode <> 3 OR
(l_tree_mode = 3
AND NOT (l_demand_source_type_id = mr.demand_source_type_id
AND l_demand_source_header_id = mr.demand_source_header_id
AND Nvl(l_demand_source_line_id, -9999) =
Nvl(mr.demand_source_line_id,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') =
Nvl(mr.demand_source_name,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) =
Nvl(mr.demand_source_delivery,-9999)
)
))
UNION ALL
-- onhand quantities
SELECT
moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.revision revision
, moq.lot_number lot_number
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, moq.primary_transaction_quantity
, moq.secondary_transaction_quantity
, 1 quantity_type
, moq.cost_group_id cost_group_id
, moq.lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
, moq.status_id -- Onhand Material Status Support
FROM
mtl_onhand_quantities_detail moq
UNION ALL
-- pending transactions in mmtt
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, NULL lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity)))
* round(Abs(mmtt.primary_quantity),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity)))
* round(Abs(mmtt.secondary_transaction_quantity),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
--NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
, inv_material_status_grp.get_default_status(mmtt.organization_id
,mmtt.inventory_item_id
,mmtt.subinventory_code
,mmtt.locator_id
,null -- lot_number
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id))
) status_id -- Onhand Material Status Support
FROM
mtl_material_transactions_temp mmtt,
mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6,24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
-- receiving side of transfers
-- added 5/23/00
-- if quantity is in an lpn, then it is containerized
-- Bug 7658493, If wms task is in loaded status, consider allocation like pending transaction.
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, NULL lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs(mmtt.primary_quantity),5)
, round(Abs(mmtt.secondary_transaction_quantity),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
, inv_material_status_grp.get_default_status(Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id)
,mmtt.inventory_item_id
,mmtt.transfer_subinventory
,mmtt.transfer_to_location
,null -- lot_number
,NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id)
,mmtt.transaction_action_id
,inv_material_status_grp.get_default_status(mmtt.organization_id
,mmtt.inventory_item_id
,mmtt.subinventory_code
,mmtt.locator_id
,null -- lot_number
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id))
)
) status_id -- Onhand Material Status Support
FROM
mtl_material_transactions_temp mmtt
WHERE
mmtt.posting_flag = 'Y'
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
--bug 3581133
AND mmtt.wip_supply_type IS NULL
) x
WHERE x.organization_id = l_organization_id
AND x.inventory_item_id = l_inventory_item_id
GROUP BY
x.organization_id, x.inventory_item_id, x.revision
, x.lot_number,x.subinventory_code, x.locator_id
, x.quantity_type, x.cost_group_id, x.lpn_id
, x.transaction_action_id, x.transfer_subinventory_code
, x.transfer_locator_id, x.status_id -- Onhand Material Status Support
) x
, mtl_secondary_inventories sub
, mtl_item_locations loc
, mtl_parameters mp -- Onhand Material Status Support
, mtl_material_statuses_b mms -- Onhand Material Status Support
WHERE
x.organization_id = loc.organization_id (+)
AND x.locator_id = loc.inventory_location_id (+)
AND x.organization_id = sub.organization_id (+)
AND x.subinventory_code = sub.secondary_inventory_name (+)
AND x.organization_id = mp.organization_id (+) -- Onhand Material Status Support
AND x.status_id = mms.status_id (+) -- Onhand Material Status Support
AND (l_asset_subs_only = 2 OR
NVL(sub.asset_inventory,1) = 1)
AND (
(mp.default_status_id is null and
( (l_onhand_source =1 AND
Nvl(sub.inventory_atp_code, 1) = 1
AND Nvl(loc.inventory_atp_code, 1) = 1
)
OR (l_onhand_source = 2 AND
Nvl(sub.availability_type, 1) = 1
AND Nvl(loc.availability_type, 1) = 1
)
OR l_onhand_source =3
OR (l_onhand_source = 4 AND
(nvl(sub.inventory_atp_code,1) = 1
AND Nvl(loc.inventory_atp_code, 1) = 1
AND Nvl(loc.availability_type, 1) = 1
AND nvl(sub.availability_type,1)=1
)
)
)
)
OR
(
mp.default_status_id is not null and
( (l_onhand_source =1 AND
Nvl(mms.inventory_atp_code, 1) = 1
)
OR (l_onhand_source = 2 AND
Nvl(mms.availability_type, 1) = 1
)
OR l_onhand_source =3
OR (l_onhand_source = 4 AND
(nvl(mms.inventory_atp_code,1) = 1
AND nvl(mms.availability_type,1)=1
)
)
)
)
)
GROUP BY
x.organization_id
, x.inventory_item_id
, x.revision
, NULL
, NULL
, x.subinventory_code
, sub.reservable_type
, x.locator_id
, x.quantity_type
, x.cost_group_id
, x.lpn_id
, x.transaction_action_id
, x.transfer_subinventory_code
, x.transfer_locator_id
, x.status_id; -- Onhand Material Status Support
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, x.revision revision
, NULL lot_number
, lot.expiration_date lot_expiration_date
, x.subinventory_code subinventory_code
, sub.reservable_type reservable_type
, x.locator_id locator_id
, x.primary_quantity primary_quantity
, x.secondary_quantity secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
, lot.reservable_type lot_reservable_type --Bug#8713821 to check reservable type
FROM (
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, decode(l_revision_control, 2, NULL, x.revision) revision
, x.lot_number lot_number
, x.subinventory_code subinventory_code
, x.locator_id locator_id
, SUM(x.primary_quantity) primary_quantity
, SUM(x.secondary_quantity) secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
FROM (
-- reservations
SELECT
mr.organization_id organization_id
, mr.inventory_item_id inventory_item_id
, mr.revision revision
, mr.lot_number lot_number
, mr.subinventory_code subinventory_code
, mr.locator_id locator_id
, mr.primary_reservation_quantity
- Nvl(mr.detailed_quantity,0) primary_quantity
, mr.secondary_reservation_quantity
- Nvl(mr.secondary_detailed_quantity,0) secondary_quantity
, 3 quantity_type
, to_number(NULL) cost_group_id
, lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM mtl_reservations mr
WHERE
Nvl(mr.supply_source_type_id, 13) = 13
AND mr.primary_reservation_quantity >
Nvl(mr.detailed_quantity,0)
AND ((l_no_lpn_reservations <>1)
OR (l_no_lpn_reservations = 1 AND mr.lpn_id IS NULL))
AND (l_tree_mode <> 3 OR
(l_tree_mode = 3
AND NOT
( l_demand_source_type_id = mr.demand_source_type_id
AND l_demand_source_header_id = mr.demand_source_header_id
AND Nvl(l_demand_source_line_id, -9999) =
Nvl(mr.demand_source_line_id,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') =
Nvl(mr.demand_source_name,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) =
Nvl(mr.demand_source_delivery,-9999)
)
))
UNION ALL
-- onhand quantities
SELECT
moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.revision revision
, moq.lot_number lot_number
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, moq.primary_transaction_quantity
, moq.secondary_transaction_quantity
, 1 quantity_type
, moq.cost_group_id cost_group_id
, moq.lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_onhand_quantities_detail moq
UNION ALL
-- pending transactions in mmtt, lot in MMTT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity)))
* round(Abs(mmtt.primary_quantity),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity)))
* round(Abs(mmtt.secondary_transaction_quantity),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt,
mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NOT NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6,24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
--MMTT records, lot in MTLT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity)))
* round(Abs( mtlt.primary_quantity ),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity)))
* round(Abs( mtlt.secondary_quantity ),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt,
mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mmtt.lot_number IS NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6,24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
--bug 9241240: MMTT with no MTLT
-- Fix for incorrect ATT/ATR shown in WIP comp issue (MMTT inserted without lot number)
UNION ALL
--MMTT records, no lot in MTLT or MMTT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
, round(mmtt.primary_quantity,5)
, round(mmtt.secondary_transaction_quantity,5) -- invConv change
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type --FlexiLotAlloc
, mmtt.cost_group_id cost_group_id
--14688297
-- , NVL(mmtt.allocated_lpn_id, NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NULL
AND mmtt.subinventory_code IS NOT NULL
AND (mmtt.transaction_status IS NULL OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,27,28)) --FlexiLotAlloc
AND mmtt.transaction_action_id in (1,2,27,28) --FlexiLotAlloc and BUG 10070839
AND mmtt.transaction_source_type_id in (2,4,5,8) --FlexiLotAlloc
AND nvl(mmtt.wip_entity_type,-1) NOT IN (9,10)
AND NOT EXISTS (SELECT 1 FROM mtl_transaction_lots_temp mtlt
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id)
UNION ALL
-- receiving side of transfers, lot in MMTT
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs( mmtt.primary_quantity),5)
, round(Abs( mmtt.secondary_transaction_quantity),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NOT NULL
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
UNION ALL
-- receiving side of transfers, lot in MTLT
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs( mtlt.primary_quantity ),5)
, round(Abs( mtlt.secondary_quantity ),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NULL
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
) x
WHERE x.organization_id = l_organization_id
AND x.inventory_item_id = l_inventory_item_id
GROUP BY
x.organization_id, x.inventory_item_id, x.revision
, x.lot_number,x.subinventory_code, x.locator_id
, x.quantity_type, x.cost_group_id, x.lpn_id
, x.transaction_action_id, x.transfer_subinventory_code
, x.transfer_locator_id
) x
, mtl_secondary_inventories sub
, mtl_lot_numbers lot
WHERE
x.organization_id = sub.organization_id (+)
AND x.subinventory_code = sub.secondary_inventory_name (+)
AND x.organization_id = lot.organization_id (+)
AND x.inventory_item_id = lot.inventory_item_id (+)
AND x.lot_number = lot.lot_number (+)
AND (l_asset_subs_only = 2 OR
NVL(sub.asset_inventory,1) = 1)
AND ((l_onhand_source = 1 AND
Nvl(sub.inventory_atp_code, 1) = 1
) OR
(l_onhand_source = 2 AND
Nvl(sub.availability_type, 1) = 1
) OR
l_onhand_source =3
OR
(l_onhand_source = 4 AND
Nvl(sub.inventory_atp_code, 1) = 1 AND
Nvl(sub.availability_type, 1) = 1)
)
;
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, x.revision revision
, NULL lot_number
, lot.expiration_date lot_expiration_date
, x.subinventory_code subinventory_code
, sub.reservable_type reservable_type
, x.locator_id locator_id
, x.primary_quantity primary_quantity
, x.secondary_quantity secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
, lot.reservable_type lot_reservable_type --Bug#8713821 to check reservable type
FROM (
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, decode(l_revision_control, 2, NULL
, x.revision) revision
, x.lot_number lot_number
, x.subinventory_code subinventory_code
, x.locator_id locator_id
, SUM(x.primary_quantity) primary_quantity
, SUM(x.secondary_quantity) secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
FROM (
-- reservations
SELECT
mr.organization_id organization_id
, mr.inventory_item_id inventory_item_id
, mr.revision revision
, mr.lot_number lot_number
, mr.subinventory_code subinventory_code
, mr.locator_id locator_id
, mr.primary_reservation_quantity
- Nvl(mr.detailed_quantity,0) primary_quantity
, mr.secondary_reservation_quantity
- Nvl(mr.secondary_detailed_quantity,0) secondary_quantity
, 3 quantity_type
, to_number(NULL) cost_group_id
, lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM mtl_reservations mr
WHERE
Nvl(mr.supply_source_type_id, 13) = 13
AND mr.primary_reservation_quantity >
Nvl(mr.detailed_quantity,0)
AND ((l_no_lpn_reservations <>1)
OR (l_no_lpn_reservations = 1 AND mr.lpn_id IS NULL))
AND (l_tree_mode <> 3 OR
(l_tree_mode = 3
AND NOT (l_demand_source_type_id = mr.demand_source_type_id
AND l_demand_source_header_id = mr.demand_source_header_id
AND Nvl(l_demand_source_line_id, -9999) =
Nvl(mr.demand_source_line_id,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') =
Nvl(mr.demand_source_name,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) =
Nvl(mr.demand_source_delivery,-9999)
)
))
UNION ALL
-- onhand quantities
SELECT
moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.revision revision
, moq.lot_number lot_number
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, decode(l_demand_source_line_id,
NULL, sum(moq.primary_transaction_quantity),
pjm_ueff_onhand.onhand_quantity(
l_demand_source_line_id
,moq.inventory_item_id
,moq.organization_id
,moq.revision
,moq.subinventory_code
,moq.locator_id
,moq.lot_number
,moq.lpn_id
,moq.cost_group_id)
)
, decode(l_demand_source_line_id,
NULL, sum(moq.secondary_transaction_quantity),
pjm_ueff_onhand.onhand_quantity(
l_demand_source_line_id
,moq.inventory_item_id
,moq.organization_id
,moq.revision
,moq.subinventory_code
,moq.locator_id
,moq.lot_number
,moq.lpn_id
,moq.cost_group_id)
)
, 1 quantity_type
, moq.cost_group_id cost_group_id
, moq.lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_onhand_quantities_detail moq
GROUP BY moq.organization_id,moq.inventory_item_id,moq.revision,
moq.subinventory_code,moq.locator_id,moq.lot_number,
moq.lpn_id,moq.cost_group_id
UNION ALL
-- pending transactions in mmtt, lot in MMTT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity))) *
round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.primary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.primary_quantity)
),mmtt.primary_quantity
)
)),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity))) *
round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.secondary_transaction_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.secondary_transaction_quantity)
),mmtt.secondary_transaction_quantity
)
)),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt,
mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NOT NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6, 24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
--MMTT records, lot in MTLT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity))) *
round(Abs(decode(l_demand_source_line_id,
NULL, mtlt.primary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mtlt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.primary_quantity)
),mtlt.primary_quantity)
)),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity))) *
round(Abs(decode(l_demand_source_line_id,
NULL, mtlt.secondary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mtlt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.secondary_transaction_quantity)
),mtlt.secondary_quantity)
)),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt,
mtl_parameters mp -- bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mmtt.lot_number IS NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6, 24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
--bug 9241240: MMTT with no MTLT
-- Fix for incorrect ATT/ATR shown in WIP comp issue (MMTT inserted without lot number)
UNION ALL
--MMTT records, no lot in MTLT or MMTT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
, -1 * round(Abs(decode (l_demand_source_line_id,
NULL, mmtt.primary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity
(l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.primary_quantity)
)
,mmtt.primary_quantity)
)
),5
)
, -1 * round(Abs(decode (l_demand_source_line_id,
NULL, mmtt.secondary_transaction_quantity,
Nvl(pjm_ueff_onhand.txn_quantity
(l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.secondary_transaction_quantity)
)
,mmtt.secondary_transaction_quantity)
)
),5
) -- invConv change
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type --FlexiLotAlloc
, mmtt.cost_group_id cost_group_id
--14688297
-- , NVL(mmtt.allocated_lpn_id, NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
, to_number(NULL) transaction_action_id --5698945
, to_char(NULL) transfer_subinventory_code --5698945
, to_number(NULL) transfer_locator_id --5698945
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NULL
AND mmtt.subinventory_code IS NOT NULL
AND (mmtt.transaction_status IS NULL OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,27,28)) --FlexiLotAlloc
AND mmtt.transaction_action_id in (1,2,27,28) --FlexiLotAlloc and BUG 10070839
AND mmtt.transaction_source_type_id in (2,4,5,8) --FlexiLotAlloc
AND nvl(mmtt.wip_entity_type,-1) NOT IN (9,10) --FlexiLotAlloc
AND NOT EXISTS (SELECT 1 FROM mtl_transaction_lots_temp mtlt
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id)
UNION ALL
-- receiving side of transfers lot in MMTT
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.primary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.primary_quantity)
),mmtt.primary_quantity)
)),5)
, round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.secondary_transaction_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.secondary_transaction_quantity)
),mmtt.secondary_transaction_quantity)
)),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NOT NULL
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
UNION ALL
-- receiving side of transfers lot in MTLT
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs(decode(l_demand_source_line_id,
NULL, mtlt.primary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mtlt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.primary_quantity)
),mtlt.primary_quantity)
)),5)
, round(Abs(decode(l_demand_source_line_id,
NULL, mtlt.secondary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mtlt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.secondary_transaction_quantity)
),mtlt.secondary_quantity)
)),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt
,mtl_transaction_lots_temp mtlt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NULL
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
) x
WHERE x.organization_id = l_organization_id
AND x.inventory_item_id = l_inventory_item_id
GROUP BY
x.organization_id, x.inventory_item_id, x.revision
, x.lot_number,x.subinventory_code, x.locator_id
, x.quantity_type, x.cost_group_id, x.lpn_id
, x.transaction_action_id, x.transfer_subinventory_code
, x.transfer_locator_id
) x
, mtl_secondary_inventories sub
, mtl_lot_numbers lot
WHERE
x.organization_id = sub.organization_id (+)
AND x.subinventory_code = sub.secondary_inventory_name (+)
AND x.organization_id = lot.organization_id (+)
AND x.inventory_item_id = lot.inventory_item_id (+)
AND x.lot_number = lot.lot_number (+)
AND (l_asset_subs_only = 2 OR
NVL(sub.asset_inventory,1) = 1)
AND ((l_onhand_source = 1 AND
Nvl(sub.inventory_atp_code, 1) = 1
) OR
(l_onhand_source = 2 AND
Nvl(sub.availability_type, 1) = 1
) OR
l_onhand_source =3
OR
(l_onhand_source = 4 AND
Nvl(sub.inventory_atp_code, 1) = 1 AND
Nvl(sub.availability_type, 1) = 1
)
)
;
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, x.revision revision
, x.lot_number lot_number
, lot.expiration_date lot_expiration_date
, x.subinventory_code subinventory_code
, sub.reservable_type reservable_type
, x.locator_id locator_id
, x.primary_quantity primary_quantity
, x.secondary_quantity secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
, lot.reservable_type lot_reservable_type --Bug#8713821 to check reservable type
FROM (
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, decode(l_revision_control, 2, NULL, x.revision) revision
, x.lot_number lot_number
, x.subinventory_code subinventory_code
, x.locator_id locator_id
, SUM(x.primary_quantity) primary_quantity
, SUM(x.secondary_quantity) secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
FROM (
-- reservations
SELECT
mr.organization_id organization_id
, mr.inventory_item_id inventory_item_id
, mr.revision revision
, mr.lot_number lot_number
, mr.subinventory_code subinventory_code
, mr.locator_id locator_id
, mr.primary_reservation_quantity
- Nvl(mr.detailed_quantity,0) primary_quantity
, mr.secondary_reservation_quantity
- Nvl(mr.secondary_detailed_quantity,0) secondary_quantity
, 3 quantity_type
, to_number(NULL) cost_group_id
, lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM mtl_reservations mr
WHERE
Nvl(mr.supply_source_type_id, 13) = 13
AND mr.primary_reservation_quantity >
Nvl(mr.detailed_quantity,0)
AND ((l_no_lpn_reservations <>1)
OR (l_no_lpn_reservations = 1 AND mr.lpn_id IS NULL))
AND (l_tree_mode <> 3 OR
(l_tree_mode = 3
AND NOT
( l_demand_source_type_id = mr.demand_source_type_id
AND l_demand_source_header_id = mr.demand_source_header_id
AND Nvl(l_demand_source_line_id, -9999) =
Nvl(mr.demand_source_line_id,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') =
Nvl(mr.demand_source_name,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) =
Nvl(mr.demand_source_delivery,-9999)
)
))
UNION ALL
-- onhand quantities
SELECT
moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.revision revision
, moq.lot_number lot_number
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, moq.primary_transaction_quantity
, moq.secondary_transaction_quantity
, 1 quantity_type
, moq.cost_group_id cost_group_id
, moq.lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_onhand_quantities_detail moq
UNION ALL
-- pending transactions in mmtt, lot in MMTT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity)))
* round(Abs(mmtt.primary_quantity),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity)))
* round(Abs(mmtt.secondary_transaction_quantity),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt,
mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NOT NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6,24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
--MMTT records, lot in MTLT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity)))
* round(Abs( mtlt.primary_quantity ),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity)))
* round(Abs( mtlt.secondary_quantity ),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt,
mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mmtt.lot_number IS NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6,24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
-- receiving side of transfers, lot in MMTT
-- Bug 7658493, If wms task is in loaded status, consider allocation like pending transaction.
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs( mmtt.primary_quantity),5)
, round(Abs( mmtt.secondary_transaction_quantity),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NOT NULL
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
UNION ALL
-- receiving side of transfers, lot in MTLT
-- Bug 7658493, If wms task is in loaded status, consider allocation like pending transaction.
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs( mtlt.primary_quantity ),5)
, round(Abs( mtlt.secondary_quantity ),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NULL
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
) x
WHERE x.organization_id = l_organization_id
AND x.inventory_item_id = l_inventory_item_id
GROUP BY
x.organization_id, x.inventory_item_id, x.revision
, x.lot_number,x.subinventory_code, x.locator_id
, x.quantity_type, x.cost_group_id, x.lpn_id
, x.transaction_action_id, x.transfer_subinventory_code
, x.transfer_locator_id
) x
, mtl_secondary_inventories sub
, mtl_lot_numbers lot
WHERE
x.organization_id = sub.organization_id (+)
AND x.subinventory_code = sub.secondary_inventory_name (+)
AND x.organization_id = lot.organization_id (+)
AND x.inventory_item_id = lot.inventory_item_id (+)
AND x.lot_number = lot.lot_number (+)
AND (l_asset_subs_only = 2 OR
NVL(sub.asset_inventory,1) = 1)
AND ((l_onhand_source = 1 AND
Nvl(sub.inventory_atp_code, 1) = 1
) OR
(l_onhand_source = 2 AND
Nvl(sub.availability_type, 1) = 1
) OR
l_onhand_source =3
OR
(l_onhand_source = 4 AND
Nvl(sub.inventory_atp_code, 1) = 1 AND
Nvl(sub.availability_type, 1) = 1)
)
;
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, x.revision revision
, x.lot_number lot_number
, lot.expiration_date lot_expiration_date
, x.subinventory_code subinventory_code
, sub.reservable_type reservable_type
, x.locator_id locator_id
, x.primary_quantity primary_quantity
, x.secondary_quantity secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
, x.status_id -- Onhand Material Status Support
--, lot.reservable_type lot_reservable_type --Bug#8713821 to check reservable type
, decode(mp.default_status_id, null, lot.reservable_type, mms.reservable_type) lot_reservable_type --Bug 13387319
FROM (
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, decode(l_revision_control, 2, NULL, x.revision) revision
, x.lot_number lot_number
, x.subinventory_code subinventory_code
, x.locator_id locator_id
, SUM(x.primary_quantity) primary_quantity
, SUM(x.secondary_quantity) secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
, x.status_id -- Onhand Material Status Support
FROM (
-- reservations
SELECT
mr.organization_id organization_id
, mr.inventory_item_id inventory_item_id
, mr.revision revision
, mr.lot_number lot_number
, mr.subinventory_code subinventory_code
, mr.locator_id locator_id
, mr.primary_reservation_quantity
- Nvl(mr.detailed_quantity,0) primary_quantity
, mr.secondary_reservation_quantity
- Nvl(mr.secondary_detailed_quantity,0) secondary_quantity
, 3 quantity_type
, to_number(NULL) cost_group_id
, lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
, to_number(NULL) status_id -- Onhand Material Status Support
FROM mtl_reservations mr
WHERE
Nvl(mr.supply_source_type_id, 13) = 13
AND mr.primary_reservation_quantity >
Nvl(mr.detailed_quantity,0)
AND ((l_no_lpn_reservations <>1)
OR (l_no_lpn_reservations = 1 AND mr.lpn_id IS NULL))
AND (l_tree_mode <> 3 OR
(l_tree_mode = 3
AND NOT
( l_demand_source_type_id = mr.demand_source_type_id
AND l_demand_source_header_id = mr.demand_source_header_id
AND Nvl(l_demand_source_line_id, -9999) =
Nvl(mr.demand_source_line_id,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') =
Nvl(mr.demand_source_name,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) =
Nvl(mr.demand_source_delivery,-9999)
)
))
UNION ALL
-- onhand quantities
SELECT
moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.revision revision
, moq.lot_number lot_number
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, moq.primary_transaction_quantity
, moq.secondary_transaction_quantity
, 1 quantity_type
, moq.cost_group_id cost_group_id
, moq.lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
, moq.status_id -- Onhand Material Status Support
FROM
mtl_onhand_quantities_detail moq
UNION ALL
-- pending transactions in mmtt, lot in MMTT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity)))
* round(Abs(mmtt.primary_quantity),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity)))
* round(Abs(mmtt.secondary_transaction_quantity),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
, inv_material_status_grp.get_default_status(mmtt.organization_id
,mmtt.inventory_item_id
,mmtt.subinventory_code
,mmtt.locator_id
,mmtt.lot_number -- lot_number
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id))
) status_id -- Onhand Material Status Support
FROM
mtl_material_transactions_temp mmtt
, mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
-- invConv bug 4074394 removed the fix in v115.141, because it only works
-- when the ingredient item has no inventory.
AND mmtt.lot_number IS NOT NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6,24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
--MMTT records, lot in MTLT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity)))
* round(Abs( mtlt.primary_quantity ),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity)))
* round(Abs( mtlt.secondary_quantity ),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
--,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
, inv_material_status_grp.get_default_status(mmtt.organization_id
,mmtt.inventory_item_id
,mmtt.subinventory_code
,mmtt.locator_id
,mtlt.lot_number -- lot_number in MTLT
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id))
) status_id -- Onhand Material Status Support
FROM
mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
, mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mmtt.lot_number IS NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6,24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
--bug 9241240: MMTT with no MTLT
-- Fix for incorrect ATT/ATR shown in WIP comp issue (MMTT inserted without lot number)
UNION ALL
-- pending transactions in mmtt, no lot in MMTT and no MTLT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
, round(mmtt.primary_quantity,5)
, round(mmtt.secondary_transaction_quantity,5) -- invConv change
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type --FlexiLotAlloc
, mmtt.cost_group_id cost_group_id
, NVL(mmtt.allocated_lpn_id, NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
, inv_material_status_grp.get_default_status(mmtt.organization_id
,mmtt.inventory_item_id
,mmtt.subinventory_code
,mmtt.locator_id
,NULL --lot number
,NVL(mmtt.allocated_lpn_id,NVL(mmtt.content_lpn_id, mmtt.lpn_id))
) status_id -- Onhand Material Status Support
FROM
mtl_material_transactions_temp mmtt
WHERE mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NULL
AND mmtt.subinventory_code IS NOT NULL
AND (mmtt.transaction_status IS NULL OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,27,28))--FlexiLotAlloc
AND mmtt.transaction_action_id in (1,2,27,28) --FlexiLotAlloc and BUG 10070839
AND mmtt.transaction_source_type_id in (2,4,5,8) --FlexiLotAlloc
AND nvl(mmtt.wip_entity_type,-1) NOT IN (9,10) --FlexiLotAlloc
AND NOT EXISTS (SELECT 1 FROM mtl_transaction_lots_temp mtlt
WHERE transaction_temp_id = mmtt.transaction_temp_id)
UNION ALL
-- receiving side of transfers, lot in MMTT
-- Bug 7658493, If wms task is in loaded status, consider allocation like pending transaction.
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs( mmtt.primary_quantity),5)
, round(Abs( mmtt.secondary_transaction_quantity),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
, inv_material_status_grp.get_default_status(Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id)
,mmtt.inventory_item_id
,mmtt.transfer_subinventory
,mmtt.transfer_to_location
,mmtt.lot_number -- lot_number
,NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id)
,mmtt.transaction_action_id
,inv_material_status_grp.get_default_status(mmtt.organization_id
,mmtt.inventory_item_id
,mmtt.subinventory_code
,mmtt.locator_id
,mmtt.lot_number -- lot_number
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id))
)
) status_id -- Onhand Material Status Support
FROM
mtl_material_transactions_temp mmtt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NOT NULL
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
UNION ALL
-- receiving side of transfers, lot in MTLT
-- Bug 7658493, If wms task is in loaded status, consider allocation like pending transaction.
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs( mtlt.primary_quantity ),5)
, round(Abs( mtlt.secondary_quantity ),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
, inv_material_status_grp.get_default_status(Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id)
,mmtt.inventory_item_id
,mmtt.transfer_subinventory
,mmtt.transfer_to_location
,mtlt.lot_number -- lot_number
,NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id)
,mmtt.transaction_action_id
,inv_material_status_grp.get_default_status(mmtt.organization_id
,mmtt.inventory_item_id
,mmtt.subinventory_code
,mmtt.locator_id
,mtlt.lot_number -- lot_number
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id))
)
) status_id -- Onhand Material Status Support
FROM
mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NULL
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
) x
WHERE x.organization_id = l_organization_id
AND x.inventory_item_id = l_inventory_item_id
GROUP BY
x.organization_id, x.inventory_item_id, x.revision
, x.lot_number,x.subinventory_code, x.locator_id
, x.quantity_type, x.cost_group_id, x.lpn_id
, x.transaction_action_id, x.transfer_subinventory_code
, x.transfer_locator_id, x.status_id -- Onhand Material Status Support
) x
, mtl_secondary_inventories sub
, mtl_item_locations loc
, mtl_lot_numbers lot
, mtl_parameters mp -- Onhand Material Status Support
, mtl_material_statuses_b mms -- Onhand Material Status Support
WHERE
x.inventory_item_id = lot.inventory_item_id (+)
AND x.organization_id = lot.organization_id (+)
AND x.lot_number = lot.lot_number (+)
AND x.organization_id = loc.organization_id (+)
AND x.locator_id = loc.inventory_location_id (+)
AND x.organization_id = sub.organization_id (+)
AND x.subinventory_code = sub.secondary_inventory_name (+)
AND x.organization_id = lot.organization_id (+)
AND x.inventory_item_id = lot.inventory_item_id (+)
AND x.lot_number = lot.lot_number (+)
AND x.organization_id = mp.organization_id (+) -- Onhand Material Status Support
AND x.status_id = mms.status_id (+) -- Onhand Material Status Support
AND (l_asset_subs_only = 2 OR
NVL(sub.asset_inventory,1) = 1)
AND (
(mp.default_status_id is null and
((l_onhand_source = 1 AND
Nvl(sub.inventory_atp_code, 1) = 1
AND Nvl(loc.inventory_atp_code, 1) = 1
AND Nvl(lot.inventory_atp_code, 1) = 1
) OR
(l_onhand_source = 2 AND
Nvl(sub.availability_type, 1) = 1
AND Nvl(loc.availability_type, 1) = 1
AND Nvl(lot.availability_type, 1) = 1
) OR
l_onhand_source =3
OR
(l_onhand_source = 4 AND
Nvl(sub.inventory_atp_code, 1) = 1
AND Nvl(loc.inventory_atp_code, 1) = 1
AND Nvl(lot.inventory_atp_code, 1) = 1
AND Nvl(loc.availability_type, 1) = 1
AND Nvl(lot.availability_type, 1) = 1
AND Nvl(sub.availability_type, 1) = 1
)
)
)
or
(
mp.default_status_id is not null and
((l_onhand_source =1 AND
Nvl(mms.inventory_atp_code, 1) = 1
)
OR (l_onhand_source = 2 AND
Nvl(mms.availability_type, 1) = 1
)
OR l_onhand_source =3
OR (l_onhand_source = 4 AND
(nvl(mms.inventory_atp_code,1) = 1
AND nvl(mms.availability_type,1)=1
)
)
)
)
)
;
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, x.revision revision
, x.lot_number lot_number
, lot.expiration_date lot_expiration_date
, x.subinventory_code subinventory_code
, sub.reservable_type reservable_type
, x.locator_id locator_id
, x.primary_quantity primary_quantity
, x.secondary_quantity secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
, lot.reservable_type lot_reservable_type --Bug#8713821 to check reservable type
FROM (
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, decode(l_revision_control, 2, NULL, x.revision) revision
, x.lot_number lot_number
, x.subinventory_code subinventory_code
, x.locator_id locator_id
, SUM(x.primary_quantity) primary_quantity
, SUM(x.secondary_quantity) secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
FROM (
-- reservations
SELECT
mr.organization_id organization_id
, mr.inventory_item_id inventory_item_id
, mr.revision revision
, mr.lot_number lot_number
, mr.subinventory_code subinventory_code
, mr.locator_id locator_id
, mr.primary_reservation_quantity
- Nvl(mr.detailed_quantity,0) primary_quantity
, mr.secondary_reservation_quantity
- Nvl(mr.secondary_detailed_quantity,0) secondary_quantity
, 3 quantity_type
, to_number(NULL) cost_group_id
, lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM mtl_reservations mr
WHERE
Nvl(mr.supply_source_type_id, 13) = 13
AND mr.primary_reservation_quantity >
Nvl(mr.detailed_quantity,0)
AND ((l_no_lpn_reservations <>1)
OR (l_no_lpn_reservations = 1 AND mr.lpn_id IS NULL))
AND (l_tree_mode <> 3 OR
(l_tree_mode = 3
AND NOT
( l_demand_source_type_id = mr.demand_source_type_id
AND l_demand_source_header_id = mr.demand_source_header_id
AND Nvl(l_demand_source_line_id, -9999) =
Nvl(mr.demand_source_line_id,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') =
Nvl(mr.demand_source_name,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) =
Nvl(mr.demand_source_delivery,-9999)
)
))
UNION ALL
-- onhand quantities
SELECT
moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.revision revision
, moq.lot_number lot_number
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, moq.primary_transaction_quantity
, moq.secondary_transaction_quantity
, 1 quantity_type
, moq.cost_group_id cost_group_id
, moq.lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_onhand_quantities_detail moq
UNION ALL
-- pending transactions in mmtt, lot in MMTT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity)))
* round(Abs(mmtt.primary_quantity),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity)))
* round(Abs(mmtt.secondary_transaction_quantity),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt
, mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NOT NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6,24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
--MMTT records, lot in MTLT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity)))
* round(Abs( mtlt.primary_quantity ),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity)))
* round(Abs( mtlt.secondary_quantity ),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
, mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mmtt.lot_number IS NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6,24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
-- receiving side of transfers, lot in MMTT
-- Bug 7658493, If wms task is in loaded status, consider allocation like pending transaction.
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs( mmtt.primary_quantity),5)
, round(Abs( mmtt.secondary_transaction_quantity),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NOT NULL
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
UNION ALL
-- receiving side of transfers, lot in MTLT
-- Bug 7658493, If wms task is in loaded status, consider allocation like pending transaction.
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs( mtlt.primary_quantity ),5)
, round(Abs( mtlt.secondary_quantity ),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NULL
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
) x
WHERE x.organization_id = l_organization_id
AND x.inventory_item_id = l_inventory_item_id
GROUP BY
x.organization_id, x.inventory_item_id, x.revision
, x.lot_number,x.subinventory_code, x.locator_id
, x.quantity_type, x.cost_group_id, x.lpn_id
, x.transaction_action_id, x.transfer_subinventory_code
, x.transfer_locator_id
) x
, mtl_secondary_inventories sub
, mtl_lot_numbers lot
WHERE
x.organization_id = sub.organization_id (+)
AND x.subinventory_code = sub.secondary_inventory_name (+)
AND x.organization_id = lot.organization_id (+)
AND x.inventory_item_id = lot.inventory_item_id (+)
AND x.lot_number = lot.lot_number (+)
AND l_grade_code = lot.grade_code
AND (l_asset_subs_only = 2 OR
NVL(sub.asset_inventory,1) = 1)
AND ((l_onhand_source = 1 AND
Nvl(sub.inventory_atp_code, 1) = 1
) OR
(l_onhand_source = 2 AND
Nvl(sub.availability_type, 1) = 1
) OR
l_onhand_source =3
OR
(l_onhand_source = 4 AND
Nvl(sub.inventory_atp_code, 1) = 1 AND
Nvl(sub.availability_type, 1) = 1)
)
;
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, x.revision revision
, x.lot_number lot_number
, lot.expiration_date lot_expiration_date
, x.subinventory_code subinventory_code
, sub.reservable_type reservable_type
, x.locator_id locator_id
, x.primary_quantity primary_quantity
, x.secondary_quantity secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
, x.status_id -- Onhand Material Status Support
--, lot.reservable_type lot_reservable_type --Bug#8713821 to check reservable type
, decode(mp.default_status_id, null, lot.reservable_type, mms.reservable_type) lot_reservable_type --Bug 13387319
FROM (
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, decode(l_revision_control, 2, NULL, x.revision) revision
, x.lot_number lot_number
, x.subinventory_code subinventory_code
, x.locator_id locator_id
, SUM(x.primary_quantity) primary_quantity
, SUM(x.secondary_quantity) secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
, x.status_id -- Onhand Material Status Support
FROM (
-- reservations
SELECT
mr.organization_id organization_id
, mr.inventory_item_id inventory_item_id
, mr.revision revision
, mr.lot_number lot_number
, mr.subinventory_code subinventory_code
, mr.locator_id locator_id
, mr.primary_reservation_quantity
- Nvl(mr.detailed_quantity,0) primary_quantity
, mr.secondary_reservation_quantity
- Nvl(mr.secondary_detailed_quantity,0) secondary_quantity
, 3 quantity_type
, to_number(NULL) cost_group_id
, lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
, to_number(NULL) status_id -- Onhand Material Status Support
FROM mtl_reservations mr
WHERE
Nvl(mr.supply_source_type_id, 13) = 13
AND mr.primary_reservation_quantity >
Nvl(mr.detailed_quantity,0)
AND ((l_no_lpn_reservations <>1)
OR (l_no_lpn_reservations = 1 AND mr.lpn_id IS NULL))
AND (l_tree_mode <> 3 OR
(l_tree_mode = 3
AND NOT
( l_demand_source_type_id = mr.demand_source_type_id
AND l_demand_source_header_id = mr.demand_source_header_id
AND Nvl(l_demand_source_line_id, -9999) =
Nvl(mr.demand_source_line_id,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') =
Nvl(mr.demand_source_name,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) =
Nvl(mr.demand_source_delivery,-9999)
)
))
UNION ALL
-- onhand quantities
SELECT
moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.revision revision
, moq.lot_number lot_number
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, moq.primary_transaction_quantity
, moq.secondary_transaction_quantity
, 1 quantity_type
, moq.cost_group_id cost_group_id
, moq.lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
, moq.status_id -- Onhand Material Status Support
FROM
mtl_onhand_quantities_detail moq
UNION ALL
-- pending transactions in mmtt, lot in MMTT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity)))
* round(Abs(mmtt.primary_quantity),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity)))
* round(Abs(mmtt.secondary_transaction_quantity),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
, inv_material_status_grp.get_default_status(mmtt.organization_id
,mmtt.inventory_item_id
,mmtt.subinventory_code
,mmtt.locator_id
,mmtt.lot_number -- lot_number
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id))
) status_id -- Onhand Material Status Support
FROM
mtl_material_transactions_temp mmtt
, mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NOT NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6,24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
--MMTT records, lot in MTLT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity)))
* round(Abs( mtlt.primary_quantity ),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity)))
* round(Abs( mtlt.secondary_quantity ),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
, inv_material_status_grp.get_default_status(mmtt.organization_id
,mmtt.inventory_item_id
,mmtt.subinventory_code
,mmtt.locator_id
,mtlt.lot_number -- lot_number in MTLT
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id))
) status_id -- Onhand Material Status Support
FROM
mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
, mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mmtt.lot_number IS NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6,24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
-- receiving side of transfers, lot in MMTT
-- Bug 7658493, If wms task is in loaded status, consider allocation like pending transaction.
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs( mmtt.primary_quantity),5)
, round(Abs( mmtt.secondary_transaction_quantity),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
, inv_material_status_grp.get_default_status(Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id)
,mmtt.inventory_item_id
,mmtt.transfer_subinventory
,mmtt.transfer_to_location
,mmtt.lot_number -- lot_number
,NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id)
,mmtt.transaction_action_id
,inv_material_status_grp.get_default_status(mmtt.organization_id
,mmtt.inventory_item_id
,mmtt.subinventory_code
,mmtt.locator_id
,mmtt.lot_number -- lot_number
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id))
)
) status_id -- Onhand Material Status Support
FROM
mtl_material_transactions_temp mmtt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NOT NULL
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
UNION ALL
-- receiving side of transfers, lot in MTLT
-- Bug 7658493, If wms task is in loaded status, consider allocation like pending transaction.
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs( mtlt.primary_quantity ),5)
, round(Abs( mtlt.secondary_quantity ),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
, inv_material_status_grp.get_default_status(Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id)
,mmtt.inventory_item_id
,mmtt.transfer_subinventory
,mmtt.transfer_to_location
,mtlt.lot_number -- lot_number
,NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id)
,mmtt.transaction_action_id
,inv_material_status_grp.get_default_status(mmtt.organization_id
,mmtt.inventory_item_id
,mmtt.subinventory_code
,mmtt.locator_id
,mtlt.lot_number -- lot_number
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id))
)
) status_id -- Onhand Material Status Support
FROM
mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NULL
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
) x
WHERE x.organization_id = l_organization_id
AND x.inventory_item_id = l_inventory_item_id
GROUP BY
x.organization_id, x.inventory_item_id, x.revision
, x.lot_number,x.subinventory_code, x.locator_id
, x.quantity_type, x.cost_group_id, x.lpn_id
, x.transaction_action_id, x.transfer_subinventory_code
, x.transfer_locator_id, x.status_id -- Onhand Material Status Support
) x
, mtl_secondary_inventories sub
, mtl_item_locations loc
, mtl_lot_numbers lot
, mtl_parameters mp -- Onhand Material Status Support
, mtl_material_statuses_b mms -- Onhand Material Status Support
WHERE
x.inventory_item_id = lot.inventory_item_id (+)
AND x.organization_id = lot.organization_id (+)
AND x.lot_number = lot.lot_number (+)
AND x.organization_id = loc.organization_id (+)
AND x.locator_id = loc.inventory_location_id (+)
AND x.organization_id = sub.organization_id (+)
AND x.subinventory_code = sub.secondary_inventory_name (+)
AND x.organization_id = lot.organization_id (+)
AND x.inventory_item_id = lot.inventory_item_id (+)
AND x.lot_number = lot.lot_number (+)
AND x.organization_id = mp.organization_id (+) -- Onhand Material Status Support
AND x.status_id = mms.status_id (+) -- Onhand Material Status Support
AND l_grade_code = lot.grade_code
AND (l_asset_subs_only = 2 OR
NVL(sub.asset_inventory,1) = 1)
AND
(
(mp.default_status_id is null and
((l_onhand_source = 1 AND
Nvl(sub.inventory_atp_code, 1) = 1
AND Nvl(loc.inventory_atp_code, 1) = 1
AND Nvl(lot.inventory_atp_code, 1) = 1
) OR
(l_onhand_source = 2 AND
Nvl(sub.availability_type, 1) = 1
AND Nvl(loc.availability_type, 1) = 1
AND Nvl(lot.availability_type, 1) = 1
) OR
l_onhand_source =3
OR
(l_onhand_source = 4 AND
Nvl(sub.inventory_atp_code, 1) = 1
AND Nvl(loc.inventory_atp_code, 1) = 1
AND Nvl(lot.inventory_atp_code, 1) = 1
AND Nvl(loc.availability_type, 1) = 1
AND Nvl(lot.availability_type, 1) = 1
AND Nvl(sub.availability_type, 1) = 1)
)
)
OR -- Onhand Material Status Support
(
mp.default_status_id is not null and
((l_onhand_source =1 AND
Nvl(mms.inventory_atp_code, 1) = 1
)
OR (l_onhand_source = 2 AND
Nvl(mms.availability_type, 1) = 1
)
OR l_onhand_source =3
OR (l_onhand_source = 4 AND
(nvl(mms.inventory_atp_code,1) = 1
AND nvl(mms.availability_type,1)=1
)
)
)
)
)
;
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, x.revision revision
, NULL lot_number
, NULL lot_expiration_date
, x.subinventory_code subinventory_code
, sub.reservable_type reservable_type
, x.locator_id locator_id
, x.primary_quantity primary_quantity
, x.secondary_quantity secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
, NULL is_reservable_lot --Bug#8713821
FROM (
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, decode(l_revision_control, 2, NULL
, x.revision) revision
, NULL lot_number
, x.subinventory_code subinventory_code
, x.locator_id locator_id
, SUM(x.primary_quantity) primary_quantity
, SUM(x.secondary_quantity) secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
FROM (
-- reservations
SELECT
mr.organization_id organization_id
, mr.inventory_item_id inventory_item_id
, mr.revision revision
, mr.lot_number lot_number
, mr.subinventory_code subinventory_code
, mr.locator_id locator_id
, mr.primary_reservation_quantity
- Nvl(mr.detailed_quantity,0) primary_quantity
, mr.secondary_reservation_quantity
- Nvl(mr.secondary_detailed_quantity,0) secondary_quantity
, 3 quantity_type
, to_number(NULL) cost_group_id
, lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM mtl_reservations mr
WHERE
Nvl(mr.supply_source_type_id, 13) = 13
AND mr.primary_reservation_quantity > Nvl(mr.detailed_quantity,0)
AND ((l_no_lpn_reservations <>1)
OR (l_no_lpn_reservations = 1 AND mr.lpn_id IS NULL))
AND (l_tree_mode <> 3 OR
(l_tree_mode = 3
AND NOT (l_demand_source_type_id = mr.demand_source_type_id
AND l_demand_source_header_id = mr.demand_source_header_id
AND Nvl(l_demand_source_line_id, -9999) =
Nvl(mr.demand_source_line_id,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') =
Nvl(mr.demand_source_name,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) =
Nvl(mr.demand_source_delivery,-9999)
)
))
UNION ALL
-- onhand quantities
SELECT
moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.revision revision
, moq.lot_number lot_number
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, decode(l_demand_source_line_id,
NULL, sum(moq.primary_transaction_quantity),
pjm_ueff_onhand.onhand_quantity(
l_demand_source_line_id
,moq.inventory_item_id
,moq.organization_id
,moq.revision
,moq.subinventory_code
,moq.locator_id
,moq.lot_number
,moq.lpn_id
,moq.cost_group_id)
)
, decode(l_demand_source_line_id,
NULL, sum(moq.secondary_transaction_quantity),
pjm_ueff_onhand.onhand_quantity(
l_demand_source_line_id
,moq.inventory_item_id
,moq.organization_id
,moq.revision
,moq.subinventory_code
,moq.locator_id
,moq.lot_number
,moq.lpn_id
,moq.cost_group_id)
)
, 1 quantity_type
, moq.cost_group_id cost_group_id
, moq.lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_onhand_quantities_detail moq
GROUP BY moq.organization_id,moq.inventory_item_id,moq.revision,
moq.subinventory_code,moq.locator_id,moq.lot_number,
moq.lpn_id,moq.cost_group_id
UNION ALL
-- pending transactions in mmtt
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, NULL lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity))) *
round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.primary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.primary_quantity)
),mmtt.primary_quantity
)
)),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity))) *
round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.secondary_transaction_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.secondary_transaction_quantity)
),mmtt.secondary_transaction_quantity
)
)),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt
, mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6, 24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
-- receiving side of transfers
-- added 5/23/00
-- if quantity is in an lpn, then it is containerized
-- Bug 7658493, If wms task is in loaded status, consider allocation like pending transaction.
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, NULL lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.primary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.primary_quantity)
),mmtt.primary_quantity
)
)),5)
, round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.secondary_transaction_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.secondary_transaction_quantity)
),mmtt.secondary_transaction_quantity
)
)),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt
WHERE
mmtt.posting_flag = 'Y'
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
) x
WHERE x.organization_id = l_organization_id
AND x.inventory_item_id = l_inventory_item_id
GROUP BY
x.organization_id, x.inventory_item_id, x.revision
, x.lot_number, x.subinventory_code, x.locator_id
, x.quantity_type, x.cost_group_id, x.lpn_id
, x.transaction_action_id, x.transfer_subinventory_code
, x.transfer_locator_id
) x
, mtl_secondary_inventories sub
WHERE
x.organization_id = sub.organization_id (+)
AND x.subinventory_code = sub.secondary_inventory_name (+)
AND (l_asset_subs_only = 2 OR
NVL(sub.asset_inventory,1) = 1)
AND ((l_onhand_source = 1 AND
Nvl(sub.inventory_atp_code, 1) = 1
) OR
(l_onhand_source = 2 AND
Nvl(sub.availability_type, 1) = 1
) OR
l_onhand_source =3
OR
(l_onhand_source = 4 AND
Nvl(sub.inventory_atp_code, 1) = 1 AND
Nvl(sub.availability_type, 1) = 1)
);
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, x.revision revision
, x.lot_number lot_number
, lot.expiration_date lot_expiration_date
, x.subinventory_code subinventory_code
, sub.reservable_type reservable_type
, x.locator_id locator_id
, x.primary_quantity primary_quantity
, x.secondary_quantity secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
, lot.reservable_type lot_reservable_type --Bug#8713821 to check reservable type
FROM (
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, decode(l_revision_control, 2, NULL
, x.revision) revision
, x.lot_number lot_number
, x.subinventory_code subinventory_code
, x.locator_id locator_id
, SUM(x.primary_quantity) primary_quantity
, SUM(x.secondary_quantity) secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
FROM (
-- reservations
SELECT
mr.organization_id organization_id
, mr.inventory_item_id inventory_item_id
, mr.revision revision
, mr.lot_number lot_number
, mr.subinventory_code subinventory_code
, mr.locator_id locator_id
, mr.primary_reservation_quantity
- Nvl(mr.detailed_quantity,0) primary_quantity
, mr.secondary_reservation_quantity
- Nvl(mr.secondary_detailed_quantity,0) secondary_quantity
, 3 quantity_type
, to_number(NULL) cost_group_id
, lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM mtl_reservations mr
WHERE
Nvl(mr.supply_source_type_id, 13) = 13
AND mr.primary_reservation_quantity >
Nvl(mr.detailed_quantity,0)
AND ((l_no_lpn_reservations <>1)
OR (l_no_lpn_reservations = 1 AND mr.lpn_id IS NULL))
AND (l_tree_mode <> 3 OR
(l_tree_mode = 3
AND NOT (l_demand_source_type_id = mr.demand_source_type_id
AND l_demand_source_header_id = mr.demand_source_header_id
AND Nvl(l_demand_source_line_id, -9999) =
Nvl(mr.demand_source_line_id,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') =
Nvl(mr.demand_source_name,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) =
Nvl(mr.demand_source_delivery,-9999)
)
))
UNION ALL
-- onhand quantities
SELECT
moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.revision revision
, moq.lot_number lot_number
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, decode(l_demand_source_line_id,
NULL, sum(moq.primary_transaction_quantity),
pjm_ueff_onhand.onhand_quantity(
l_demand_source_line_id
,moq.inventory_item_id
,moq.organization_id
,moq.revision
,moq.subinventory_code
,moq.locator_id
,moq.lot_number
,moq.lpn_id
,moq.cost_group_id)
)
, decode(l_demand_source_line_id,
NULL, sum(moq.secondary_transaction_quantity),
pjm_ueff_onhand.onhand_quantity(
l_demand_source_line_id
,moq.inventory_item_id
,moq.organization_id
,moq.revision
,moq.subinventory_code
,moq.locator_id
,moq.lot_number
,moq.lpn_id
,moq.cost_group_id)
)
, 1 quantity_type
, moq.cost_group_id cost_group_id
, moq.lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_onhand_quantities_detail moq
GROUP BY moq.organization_id,moq.inventory_item_id,moq.revision,
moq.subinventory_code,moq.locator_id,moq.lot_number,
moq.lpn_id,moq.cost_group_id
UNION ALL
-- pending transactions in mmtt, lot in MMTT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity))) *
round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.primary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.primary_quantity)
),mmtt.primary_quantity
)
)),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity))) *
round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.secondary_transaction_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.secondary_transaction_quantity)
),mmtt.secondary_transaction_quantity
)
)),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt
, mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NOT NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6, 24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
--MMTT records, lot in MTLT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity))) *
round(Abs(decode(l_demand_source_line_id,
NULL, mtlt.primary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mtlt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.primary_quantity)
),mtlt.primary_quantity)
)),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity))) *
round(Abs(decode(l_demand_source_line_id,
NULL, mtlt.secondary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mtlt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.secondary_transaction_quantity)
),mtlt.secondary_quantity)
)),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
, mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mmtt.lot_number IS NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6, 24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
-- receiving side of transfers lot in MMTT
-- Bug 7658493, If wms task is in loaded status, consider allocation like pending transaction.
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.primary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.primary_quantity)
),mmtt.primary_quantity)
)),5)
, round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.secondary_transaction_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.secondary_transaction_quantity)
),mmtt.secondary_transaction_quantity)
)),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NOT NULL
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
UNION ALL
-- receiving side of transfers lot in MTLT
-- Bug 7658493, If wms task is in loaded status, consider allocation like pending transaction.
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs(decode(l_demand_source_line_id,
NULL, mtlt.primary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mtlt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.primary_quantity)
),mtlt.primary_quantity)
)),5)
, round(Abs(decode(l_demand_source_line_id,
NULL, mtlt.secondary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mtlt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.secondary_transaction_quantity)
),mtlt.secondary_quantity)
)),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt
,mtl_transaction_lots_temp mtlt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NULL
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
) x
WHERE x.organization_id = l_organization_id
AND x.inventory_item_id = l_inventory_item_id
GROUP BY
x.organization_id, x.inventory_item_id, x.revision
, x.lot_number,x.subinventory_code, x.locator_id
, x.quantity_type, x.cost_group_id, x.lpn_id
, x.transaction_action_id, x.transfer_subinventory_code
, x.transfer_locator_id
) x
, mtl_secondary_inventories sub
, mtl_lot_numbers lot
WHERE
x.organization_id = sub.organization_id (+)
AND x.subinventory_code = sub.secondary_inventory_name (+)
AND x.organization_id = lot.organization_id (+)
AND x.inventory_item_id = lot.inventory_item_id (+)
AND x.lot_number = lot.lot_number (+)
AND (l_asset_subs_only = 2 OR
NVL(sub.asset_inventory,1) = 1)
AND ((l_onhand_source = 1 AND
Nvl(sub.inventory_atp_code, 1) = 1
) OR
(l_onhand_source = 2 AND
Nvl(sub.availability_type, 1) = 1
) OR
l_onhand_source =3
OR
(l_onhand_source = 4 AND
Nvl(sub.inventory_atp_code, 1) = 1 AND
Nvl(sub.availability_type, 1) = 1
)
)
;
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, x.revision revision
, x.lot_number lot_number
, lot.expiration_date lot_expiration_date
, x.subinventory_code subinventory_code
, sub.reservable_type reservable_type
, x.locator_id locator_id
, x.primary_quantity primary_quantity
, x.secondary_quantity secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
, lot.reservable_type lot_reservable_type --Bug#8713821 to check reservable type
FROM (
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, decode(l_revision_control, 2, NULL
, x.revision) revision
, x.lot_number lot_number
, x.subinventory_code subinventory_code
, x.locator_id locator_id
, SUM(x.primary_quantity) primary_quantity
, SUM(x.secondary_quantity) secondary_quantity
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.lpn_id lpn_id
, x.transaction_action_id transaction_action_id
, x.transfer_subinventory_code transfer_subinventory_code
, x.transfer_locator_id transfer_locator_id
FROM (
-- reservations
SELECT
mr.organization_id organization_id
, mr.inventory_item_id inventory_item_id
, mr.revision revision
, mr.lot_number lot_number
, mr.subinventory_code subinventory_code
, mr.locator_id locator_id
, mr.primary_reservation_quantity
- Nvl(mr.detailed_quantity,0) primary_quantity
, mr.secondary_reservation_quantity
- Nvl(mr.secondary_detailed_quantity,0) secondary_quantity
, 3 quantity_type
, to_number(NULL) cost_group_id
, lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM mtl_reservations mr
WHERE
Nvl(mr.supply_source_type_id, 13) = 13
AND mr.primary_reservation_quantity >
Nvl(mr.detailed_quantity,0)
AND ((l_no_lpn_reservations <>1)
OR (l_no_lpn_reservations = 1 AND mr.lpn_id IS NULL))
AND (l_tree_mode <> 3 OR
(l_tree_mode = 3
AND NOT (l_demand_source_type_id = mr.demand_source_type_id
AND l_demand_source_header_id = mr.demand_source_header_id
AND Nvl(l_demand_source_line_id, -9999) =
Nvl(mr.demand_source_line_id,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') =
Nvl(mr.demand_source_name,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) =
Nvl(mr.demand_source_delivery,-9999)
)
))
UNION ALL
-- onhand quantities
SELECT
moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.revision revision
, moq.lot_number lot_number
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, decode(l_demand_source_line_id,
NULL, sum(moq.primary_transaction_quantity),
pjm_ueff_onhand.onhand_quantity(
l_demand_source_line_id
,moq.inventory_item_id
,moq.organization_id
,moq.revision
,moq.subinventory_code
,moq.locator_id
,moq.lot_number
,moq.lpn_id
,moq.cost_group_id)
)
, decode(l_demand_source_line_id,
NULL, sum(moq.secondary_transaction_quantity),
pjm_ueff_onhand.onhand_quantity(
l_demand_source_line_id
,moq.inventory_item_id
,moq.organization_id
,moq.revision
,moq.subinventory_code
,moq.locator_id
,moq.lot_number
,moq.lpn_id
,moq.cost_group_id)
)
, 1 quantity_type
, moq.cost_group_id cost_group_id
, moq.lpn_id lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_onhand_quantities_detail moq
GROUP BY moq.organization_id,moq.inventory_item_id,moq.revision,
moq.subinventory_code,moq.locator_id,moq.lot_number,
moq.lpn_id,moq.cost_group_id
UNION ALL
-- pending transactions in mmtt, lot in MMTT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity))) *
round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.primary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.primary_quantity)
),mmtt.primary_quantity
)
)),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity))) *
round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.secondary_transaction_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.secondary_transaction_quantity)
),mmtt.secondary_transaction_quantity
)
)),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
--,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt
, mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NOT NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6, 24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
--MMTT records, lot in MTLT
SELECT
mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.subinventory_code subinventory_code
, mmtt.locator_id locator_id
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity))) *
round(Abs(decode(l_demand_source_line_id,
NULL, mtlt.primary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mtlt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.primary_quantity)
),mtlt.primary_quantity)
)),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 1,
, Decode(Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,-1,2), mmtt.transaction_status), 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.secondary_transaction_quantity))) *
round(Abs(decode(l_demand_source_line_id,
NULL, mtlt.secondary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mtlt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.secondary_transaction_quantity)
),mtlt.secondary_quantity)
)),5)
--Bug 4185621
--, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, Decode(mmtt.transaction_status, 2, decode(nvl(mmtt.wms_task_status,-1),4,1,5), 1) quantity_type
, mmtt.cost_group_id cost_group_id
--14688297
-- ,NVL(mmtt.allocated_lpn_id,
-- NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, decode(nvl(mmtt.wms_task_status,-1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) lpn_id
/* Bug 9938149, Decode(mmtt.transaction_status, 2 , mmtt.transaction_action_id,
* to_number(NULL)) transaction_action_id */
, Decode(mmtt.transaction_status, 2 , decode (mp.process_enabled_flag, 'Y',
decode ((select move_order_type
from mtl_txn_request_headers mtrh
where header_id = mmtt.move_order_header_id
and rownum = 1),5,28,mmtt.transaction_action_id),
mmtt.transaction_action_id), to_number(NULL)) transaction_action_id
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_subinventory, to_char(NULL)) transfer_subinventory_code
, Decode(mmtt.transaction_status, 2 , mmtt.transfer_to_location, to_number(NULL)) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
, mtl_parameters mp -- Bug 9938149
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mmtt.lot_number IS NULL
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
-- dont look at scrap and costing txns
-- Bug 3396558 fix. Ignore ownership xfr,planning xfr transactions
AND mmtt.transaction_action_id NOT IN (5,6, 24,30)
AND mmtt.organization_id = mp.organization_id -- Bug 9938149
UNION ALL
-- receiving side of transfers lot in MMTT
-- Bug 7658493, If wms task is in loaded status, consider allocation like pending transaction.
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mmtt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.primary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.primary_quantity)
),mmtt.primary_quantity)
)),5)
, round(Abs(decode(l_demand_source_line_id,
NULL, mmtt.secondary_transaction_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mmtt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.secondary_transaction_quantity)
),mmtt.secondary_transaction_quantity)
)),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NOT NULL
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
UNION ALL
-- receiving side of transfers lot in MTLT
-- Bug 7658493, If wms task is in loaded status, consider allocation like pending transaction.
SELECT
Decode(mmtt.transaction_action_id
, 3, mmtt.transfer_organization
, mmtt.organization_id) organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, mtlt.lot_number lot_number
, mmtt.transfer_subinventory subinventory_code
, mmtt.transfer_to_location locator_id
, round(Abs(decode(l_demand_source_line_id,
NULL, mtlt.primary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mtlt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.primary_quantity)
),mtlt.primary_quantity)
)),5)
, round(Abs(decode(l_demand_source_line_id,
NULL, mtlt.secondary_quantity,
Nvl(pjm_ueff_onhand.txn_quantity(
l_demand_source_line_id
,mmtt.transaction_temp_id
,mtlt.lot_number
,'Y'
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.transaction_source_type_id
,mmtt.transaction_source_id
,mmtt.rcv_transaction_id
,sign(mmtt.secondary_transaction_quantity)
),mtlt.secondary_quantity)
)),5)
, 1 quantity_type
, mmtt.transfer_cost_group_id cost_group_id
, NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) lpn_id
, to_number(NULL) transaction_action_id
, to_char(NULL) transfer_subinventory_code
, to_number(NULL) transfer_locator_id
FROM
mtl_material_transactions_temp mmtt
,mtl_transaction_lots_temp mtlt
WHERE
mmtt.posting_flag = 'Y'
AND mmtt.lot_number IS NULL
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND Decode( Nvl(mmtt.transaction_status,0),
2, decode(nvl(mmtt.wms_task_status,-1), 4, 1, 2),
1 ) <> 2
AND (mmtt.transaction_action_id IN (2,3) -- Bug 9595634: Removed 28
-- Bug 9595634: Added condition to match demand source if action = 28
OR (mmtt.transaction_action_id = 28
AND l_demand_source_header_id = mmtt.transaction_source_id
AND Nvl(l_demand_source_line_id, -9999) = Nvl(mmtt.TRX_SOURCE_LINE_ID,-9999)
AND Nvl(l_demand_source_name, '@@@###@@#') = Nvl(mmtt.TRANSACTION_SOURCE_NAME,'@@@###@@#')
AND Nvl(l_demand_source_delivery,-9999) = Nvl(mmtt.TRX_SOURCE_DELIVERY_ID,-9999)
)
)
) x
WHERE x.organization_id = l_organization_id
AND x.inventory_item_id = l_inventory_item_id
GROUP BY
x.organization_id, x.inventory_item_id, x.revision
, x.lot_number,x.subinventory_code, x.locator_id
, x.quantity_type, x.cost_group_id, x.lpn_id
, x.transaction_action_id, x.transfer_subinventory_code
, x.transfer_locator_id
) x
, mtl_secondary_inventories sub
, mtl_lot_numbers lot
WHERE
x.organization_id = sub.organization_id (+)
AND x.subinventory_code = sub.secondary_inventory_name (+)
AND x.organization_id = lot.organization_id (+)
AND x.inventory_item_id = lot.inventory_item_id (+)
AND x.lot_number = lot.lot_number (+)
AND l_grade_code = lot.grade_code
AND (l_asset_subs_only = 2 OR
NVL(sub.asset_inventory,1) = 1)
AND ((l_onhand_source = 1 AND
Nvl(sub.inventory_atp_code, 1) = 1
) OR
(l_onhand_source = 2 AND
Nvl(sub.availability_type, 1) = 1
) OR
l_onhand_source =3
OR
(l_onhand_source = 4 AND
Nvl(sub.inventory_atp_code, 1) = 1 AND
Nvl(sub.availability_type, 1) = 1
)
)
;
g_rsv_info.DELETE;
SELECT lot_control_code
INTO lot_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id;
SELECT revision
, lot_number
, subinventory_code
, locator_id
, lpn_id
, primary_reservation_quantity - NVL(detailed_quantity, 0)
, NVL(secondary_reservation_quantity, 0) - NVL(secondary_detailed_quantity, 0)
FROM mtl_reservations
WHERE organization_id = g_rootinfos(l_root_id).organization_id
AND inventory_item_id = g_rootinfos(l_root_id).inventory_item_id
AND demand_source_type_id = g_demand_info(p_tree_id).demand_source_type_id
AND demand_source_header_id = g_demand_info(p_tree_id).demand_source_header_id
AND demand_source_line_id = g_demand_info(p_tree_id).demand_source_line_id
AND NVL(demand_source_name, '@@@###@@#') = NVL(g_demand_info(p_tree_id).demand_source_name, '@@@###@@#')
AND NVL(demand_source_delivery, -99999) = NVL(g_demand_info(p_tree_id).demand_source_delivery, -99999)
AND demand_source_line_detail IS NULL
AND nvl(supply_source_type_id,13) = 13 ; -- Bug 4194323
SELECT revision
,lot_number
,subinventory_code
,locator_id
,lpn_id
,primary_reservation_quantity - NVL(detailed_quantity, 0)
,NVL(secondary_reservation_quantity, 0) - NVL(secondary_detailed_quantity, 0)
FROM mtl_reservations
WHERE organization_id = g_rootinfos(l_root_id).organization_id
AND inventory_item_id = g_rootinfos(l_root_id).inventory_item_id
AND demand_source_type_id = g_demand_info(p_tree_id).demand_source_type_id
AND demand_source_header_id = g_demand_info(p_tree_id).demand_source_header_id
AND demand_source_name = g_demand_info(p_tree_id).demand_source_name
AND demand_source_line_id IS NULL
AND NVL(demand_source_delivery, -99999) = NVL(g_demand_info(p_tree_id).demand_source_delivery, -99999)
AND demand_source_line_detail IS NULL
AND nvl(supply_source_type_id,13) = 13 ; -- Bug 4194323
SELECT revision
,lot_number
,subinventory_code
,locator_id
,lpn_id
,primary_reservation_quantity - NVL(detailed_quantity, 0)
,NVL(secondary_reservation_quantity, 0) - NVL(secondary_detailed_quantity, 0)
FROM mtl_reservations
WHERE organization_id = g_rootinfos(l_root_id).organization_id
AND inventory_item_id = g_rootinfos(l_root_id).inventory_item_id
AND demand_source_type_id = g_demand_info(p_tree_id).demand_source_type_id
AND demand_source_header_id = g_demand_info(p_tree_id).demand_source_header_id
AND demand_source_line_id IS NULL
AND demand_source_name IS NULL
AND NVL(demand_source_delivery, -99999) = NVL(g_demand_info(p_tree_id).demand_source_delivery, -99999)
AND demand_source_line_detail IS NULL
AND nvl(supply_source_type_id,13) = 13 ; -- Bug 4194323
SELECT revision
,lot_number
,subinventory_code
,locator_id
,lpn_id
,primary_reservation_quantity - NVL(detailed_quantity, 0)
,NVL(secondary_reservation_quantity, 0) - NVL(secondary_detailed_quantity, 0)
FROM mtl_reservations
WHERE demand_source_line_id = g_demand_info(p_tree_id).demand_source_line_id
AND NVL(organization_id, 0) = g_rootinfos(l_root_id).organization_id
AND NVL(inventory_item_id,0) = g_rootinfos(l_root_id).inventory_item_id
AND demand_source_type_id = g_demand_info(p_tree_id).demand_source_type_id
AND demand_source_header_id = g_demand_info(p_tree_id).demand_source_header_id
AND NVL(demand_source_name, '@@@###@@#') = NVL(g_demand_info(p_tree_id).demand_source_name, '@@@###@@#')
AND NVL(demand_source_delivery, -99999) = NVL(g_demand_info(p_tree_id).demand_source_delivery, -99999)
AND NVL(staged_flag, 'N') = 'N'
AND demand_source_line_detail IS NULL
AND nvl(supply_source_type_id,13) = 13 ; -- Bug 4194323
SELECT revision
,lot_number
,subinventory_code
,locator_id
,lpn_id
,primary_reservation_quantity - NVL(detailed_quantity, 0)
,NVL(secondary_reservation_quantity, 0) - NVL(secondary_detailed_quantity, 0)
FROM mtl_reservations
WHERE organization_id = g_rootinfos(l_root_id).organization_id
AND inventory_item_id = g_rootinfos(l_root_id).inventory_item_id
AND demand_source_type_id = g_demand_info(p_tree_id).demand_source_type_id
AND demand_source_header_id = g_demand_info(p_tree_id).demand_source_header_id
AND demand_source_name = g_demand_info(p_tree_id).demand_source_name
AND demand_source_line_id IS NULL
AND NVL(demand_source_delivery, -99999) = NVL(g_demand_info(p_tree_id).demand_source_delivery, -99999)
AND NVL(staged_flag, 'N') = 'N'
AND demand_source_line_detail IS NULL
AND nvl(supply_source_type_id,13) = 13 ; -- Bug 4194323
SELECT revision
,lot_number
,subinventory_code
,locator_id
,lpn_id
,primary_reservation_quantity - NVL(detailed_quantity, 0)
,NVL(secondary_reservation_quantity, 0) - NVL(secondary_detailed_quantity, 0)
FROM mtl_reservations
WHERE organization_id = g_rootinfos(l_root_id).organization_id
AND inventory_item_id = g_rootinfos(l_root_id).inventory_item_id
AND demand_source_type_id = g_demand_info(p_tree_id).demand_source_type_id
AND demand_source_header_id = g_demand_info(p_tree_id).demand_source_header_id
AND demand_source_line_id IS NULL
AND demand_source_name = NULL
AND NVL(demand_source_delivery, -99999) = NVL(g_demand_info(p_tree_id).demand_source_delivery, -99999)
AND NVL(staged_flag, 'N') = 'N'
AND demand_source_line_detail IS NULL
AND nvl(supply_source_type_id,13) = 13 ; -- Bug 4194323
g_rsv_info.DELETE;
g_rootinfos.DELETE;
g_nodes.DELETE;
g_org_item_trees.DELETE;
g_all_roots.DELETE;
g_saveroots.DELETE;
g_nodes.DELETE;
g_demand_info.DELETE;
g_rsv_info.DELETE;
g_rsv_info.DELETE;
g_rsv_info.DELETE;
SELECT NVL(grade_control_flag, 'N')
, NVL(lot_control_code, 1)
, tracking_quantity_ind
, lot_status_enabled
FROM mtl_system_items
WHERE inventory_item_id = item_id
AND organization_id = org_id;
SELECT grade_code
FROM mtl_lot_numbers
WHERE organization_id = org_id
AND inventory_item_id = item_id
AND lot_number = lot;
DELETE FROM MTL_RSV_QUANTITIES_TEMP;
g_rsv_qty_node_level.delete;
g_rsv_qty_revision.delete;
g_rsv_qty_lot_number.delete;
g_rsv_qty_subinventory_code.delete;
g_rsv_qty_locator_id.delete;
g_rsv_qty_cost_group_id.delete;
g_rsv_qty_lpn_id.delete;
g_rsv_qty_qoh.delete;
g_rsv_qty_atr.delete;
g_rsv_qty_sqoh.delete;
g_rsv_qty_satr.delete;
print_debug('insert into mtl_rsv_quantities_temp, item='||g_rootinfos(l_root_id).inventory_item_id);
INSERT INTO MTL_RSV_QUANTITIES_TEMP (
organization_id
,inventory_item_id
,node_level
,revision
,lot_number
,subinventory_code
,locator_id
,grade_code
,cost_group_id
,lpn_id
,qoh
,atr
,sqoh
,satr
) VALUES (
g_rootinfos(l_root_id).organization_id
,g_rootinfos(l_root_id).inventory_item_id
,g_rsv_qty_node_level(i)
,g_rsv_qty_revision(i)
,g_rsv_qty_lot_number(i)
,g_rsv_qty_subinventory_code(i)
,g_rsv_qty_locator_id(i)
,g_rsv_qty_grade_code(i) -- l_grade_code --g_rootinfos(l_root_id).grade_code
,g_rsv_qty_cost_group_id(i)
,g_rsv_qty_lpn_id(i)
,g_rsv_qty_qoh(i)
,g_rsv_qty_atr(i)
,g_rsv_qty_sqoh(i)
,g_rsv_qty_satr(i)
);
SELECT root_id
FROM mtl_do_check_temp
ORDER BY organization_id, inventory_item_id;
insert into mtl_do_check_temp
( ROOT_ID
,ORGANIZATION_ID
,INVENTORY_ITEM_ID
,LOT_CONTROL
,LINE_ID)
values
( l_root_id
,l_org_id
,l_item_id
,l_lot_ctrl
,l_line_id);
PROCEDURE update_quantities
( p_api_version_number IN NUMBER
, p_init_msg_lst IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_tree_id IN INTEGER
, p_revision IN VARCHAR2
, p_lot_number IN VARCHAR2
, p_subinventory_code IN VARCHAR2
, p_locator_id IN NUMBER
, p_primary_quantity IN NUMBER
, p_quantity_type IN INTEGER
, x_qoh OUT NOCOPY NUMBER
, x_rqoh OUT NOCOPY NUMBER
, x_qr OUT NOCOPY NUMBER
, x_qs OUT NOCOPY NUMBER
, x_att OUT NOCOPY NUMBER
, x_atr OUT NOCOPY NUMBER
, p_transfer_subinventory_code IN VARCHAR2
, p_cost_group_id IN NUMBER
, p_containerized IN NUMBER
, p_lpn_id IN NUMBER
, p_transfer_locator_id IN NUMBER
) IS
l_secondary_quantity NUMBER;
inv_quantity_tree_pvt.update_quantities
( p_api_version_number => p_api_version_number
, p_init_msg_lst => p_init_msg_lst
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_tree_id => p_tree_id
, p_revision => p_revision
, p_lot_number => p_lot_number
, p_subinventory_code => p_subinventory_code
, p_locator_id => p_locator_id
, p_primary_quantity => p_primary_quantity
, p_secondary_quantity => l_secondary_quantity
, p_quantity_type => p_quantity_type
, x_qoh => x_qoh
, x_rqoh => x_rqoh
, x_qr => x_qr
, x_qs => x_qs
, x_att => x_att
, x_atr => x_atr
, x_sqoh => l_sqoh
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
, p_transfer_subinventory_code => p_transfer_subinventory_code
, p_cost_group_id => p_cost_group_id
, p_containerized => p_containerized
, p_lpn_id => p_lpn_id
, p_transfer_locator_id => p_transfer_locator_id);
END update_quantities;
PROCEDURE update_quantities
( p_api_version_number IN NUMBER
, p_init_msg_lst IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_tree_id IN INTEGER
, p_revision IN VARCHAR2
, p_lot_number IN VARCHAR2
, p_subinventory_code IN VARCHAR2
, p_locator_id IN NUMBER
, p_primary_quantity IN NUMBER
, p_secondary_quantity IN NUMBER
, p_quantity_type IN INTEGER
, x_qoh OUT NOCOPY NUMBER
, x_rqoh OUT NOCOPY NUMBER
, x_qr OUT NOCOPY NUMBER
, x_qs OUT NOCOPY NUMBER
, x_att OUT NOCOPY NUMBER
, x_atr OUT NOCOPY NUMBER
, x_sqoh OUT NOCOPY NUMBER
, x_srqoh OUT NOCOPY NUMBER
, x_sqr OUT NOCOPY NUMBER
, x_sqs OUT NOCOPY NUMBER
, x_satt OUT NOCOPY NUMBER
, x_satr OUT NOCOPY NUMBER
, p_transfer_subinventory_code IN VARCHAR2
, p_cost_group_id IN NUMBER
, p_containerized IN NUMBER
, p_lpn_id IN NUMBER
, p_transfer_locator_id IN NUMBER
) IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_QUANTITIES';
print_debug('Entering update_quantities. primQty='||p_primary_quantity||', secQty='||p_secondary_quantity);
fnd_message.set_token('ROUTINE', 'Update_Quantities');
print_debug('in update_quantities, calling add_quantities qty1='||p_primary_quantity||', qty2='||p_secondary_quantity||'.');
END update_quantities;
PROCEDURE update_quantities_for_form
( p_api_version_number IN NUMBER
, p_init_msg_lst IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_tree_id IN INTEGER
, p_revision IN VARCHAR2
, p_lot_number IN VARCHAR2
, p_subinventory_code IN VARCHAR2
, p_locator_id IN NUMBER
, p_primary_quantity IN NUMBER
, p_quantity_type IN INTEGER
, x_qoh OUT NOCOPY NUMBER
, x_rqoh OUT NOCOPY NUMBER
, x_qr OUT NOCOPY NUMBER
, x_qs OUT NOCOPY NUMBER
, x_att OUT NOCOPY NUMBER
, x_atr OUT NOCOPY NUMBER
, p_transfer_subinventory_code IN VARCHAR2
, p_cost_group_id IN NUMBER
, p_containerized IN NUMBER
, p_call_for_form IN VARCHAR2
, p_lpn_id IN NUMBER DEFAULT NULL --added for bug7038890
) IS
l_secondary_quantity NUMBER := NULL;
inv_quantity_tree_pvt.update_quantities_for_form
( p_api_version_number => p_api_version_number
, p_init_msg_lst => p_init_msg_lst
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_tree_id => p_tree_id
, p_revision => p_revision
, p_lot_number => p_lot_number
, p_subinventory_code => p_subinventory_code
, p_locator_id => p_locator_id
, p_primary_quantity => p_primary_quantity
, p_secondary_quantity => l_secondary_quantity
, p_quantity_type => p_quantity_type
, x_qoh => x_qoh
, x_rqoh => x_rqoh
, x_qr => x_qr
, x_qs => x_qs
, x_att => x_att
, x_atr => x_atr
, x_sqoh => l_sqoh
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
, p_transfer_subinventory_code => p_transfer_subinventory_code
, p_cost_group_id => p_cost_group_id
, p_containerized => p_containerized
, p_call_for_form => p_call_for_form
, p_lpn_id => p_lpn_id); --added for bug7038890
END update_quantities_for_form;
PROCEDURE update_quantities_for_form
( p_api_version_number IN NUMBER
, p_init_msg_lst IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_tree_id IN INTEGER
, p_revision IN VARCHAR2
, p_lot_number IN VARCHAR2
, p_subinventory_code IN VARCHAR2
, p_locator_id IN NUMBER
, p_primary_quantity IN NUMBER
, p_secondary_quantity IN NUMBER
, p_quantity_type IN INTEGER
, x_qoh OUT NOCOPY NUMBER
, x_rqoh OUT NOCOPY NUMBER
, x_qr OUT NOCOPY NUMBER
, x_qs OUT NOCOPY NUMBER
, x_att OUT NOCOPY NUMBER
, x_atr OUT NOCOPY NUMBER
, x_sqoh OUT NOCOPY NUMBER
, x_srqoh OUT NOCOPY NUMBER
, x_sqr OUT NOCOPY NUMBER
, x_sqs OUT NOCOPY NUMBER
, x_satt OUT NOCOPY NUMBER
, x_satr OUT NOCOPY NUMBER
, p_transfer_subinventory_code IN VARCHAR2
, p_cost_group_id IN NUMBER
, p_containerized IN NUMBER
, p_call_for_form IN VARCHAR2
, p_lpn_id IN NUMBER DEFAULT NULL --added for bug7038890
) IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_QUANTITIES_FOR_FORM';
fnd_message.set_token('ROUTINE', 'Update_Quantities');
END update_quantities_for_form;
g_saversvnode.DELETE;
g_rsv_info.DELETE;
cursor C1 is select organization_id,inventory_item_id
from mtl_do_check_temp;