[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_revision_select long;
l_lot_select long;
l_lot_select2 long;
SELECT pjm_unit_eff.enabled INTO g_unit_eff_enabled FROM dual;
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
, To_date(NULL) date_received
, 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) '
|| l_reservation_where;
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
, ' || l_onhand_qty_part || '
, To_date(NULL) date_received
, 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 ';
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
, Decode (mmtt.transaction_status, 2, 1
, Decode(mmtt.transaction_action_id
, 1, -1, 2, -1, 28, -1, 3, -1, 5, -1,
Sign(mmtt.primary_quantity))
)
* round(Abs('|| l_mmtt_qty_part || '), 5)
, Decode(mmtt.transaction_action_id
, 1, To_date(NULL)
, 2, To_date(NULL)
, 28, To_date(NULL)
, 3, To_date(NULL)
, 5, To_date(NULL)
, Decode(Sign(mmtt.primary_quantity)
, -1, To_date(NULL)
, mmtt.transaction_date)) date_received
, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, mmtt.cost_group_id cost_group_id
, NVL(mmtt.allocated_lpn_id,NVL(mmtt.content_lpn_id,mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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 -- pending txns
-- only picking side of the suggested transactions are used
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
AND mmtt.transaction_action_id NOT IN (24,30)
UNION ALL
-- pending transactions and suggestions in mmtt with lot numbers in lots_temp
--changed by jcearley on 12/8/99
--added 1 to decode statement so that we make sure the
--issue qtys in mmtt are seen as negative.
--This problem arose because create_suggestions stores
--the suggested transactions in mmtt as a positive number.
-- added 5/23/00
-- if quantity is in an lpn, then it is containerized.
-- Bug 2127112 - packed mmtt recs can have either lpn_id or
-- content lpn_id populated. To handle this, changed
-- how containerized is determined for MMTT recs. Assuming
-- that lpn_Id and content_lpn_id are always positive,
-- the existence of either causes containerized to be 1 (since
-- lpn_id will be greater than 1). If both are null,
-- containerized will be 0 (0 is less than 1).
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
, Decode(mmtt.transaction_status, 2, 1
, Decode(mmtt.transaction_action_id
, 1, -1, 2, -1, 28, -1, 3, -1, 5, -1,
Sign(mmtt.transaction_quantity))
)
* round(abs('||l_mtlt_qty_part||'),5)
, Decode(mmtt.transaction_action_id
, 1, To_date(NULL)
, 2, To_date(NULL)
, 28, To_date(NULL)
, 3, To_date(NULL)
, 5, To_date(NULL)
, Decode(Sign(mmtt.primary_quantity)
, -1, To_date(NULL)
, mmtt.transaction_date)) date_received
, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, mmtt.cost_group_id cost_group_id
, NVL(mmtt.allocated_lpn_id,NVL(mmtt.content_lpn_id,mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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 -- pending txns
-- only picking side of the suggested transactions are used
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
AND mmtt.transaction_action_id NOT IN (24,30)
UNION ALL
-- receiving side of transfers with lot numbers in lots_temp
-- added 5/23/00
-- if quantity is in an lpn, then it is containerized
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('||l_mtlt_qty_part||') ,5)
, mmtt.transaction_date date_received
, 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.transaction_action_id IN (2,28,3,5)
AND mmtt.lot_number IS NULL
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND Nvl(mmtt.transaction_status,0) <> 2 -- pending txns only
-- dont look at scrap and costing txns
AND mmtt.transaction_action_id NOT IN (24,30)
-- receiving side of transfers with lot number in MMTT
-- added 5/23/00
-- if quantity is in an lpn, then it is containerized
UNION ALL
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('||l_mmtt_qty_part||'),5)
, mmtt.transaction_date date_received
, 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.transaction_action_id IN (2,28,3,5)
AND Nvl(mmtt.transaction_status,0) <> 2 -- pending txns only
-- dont look at scrap and costing txns
AND mmtt.transaction_action_id NOT IN (24,30)
AND mmtt.lot_number IS NOT NULL ';
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
, Decode(mmtt.transaction_status, 2, 1
, Decode(mmtt.transaction_action_id
, 1, -1, 2, -1, 28, -1, 3, -1, 5, -1,
Sign(mmtt.primary_quantity))
)
* round(Abs('|| l_mmtt_qty_part || '),5)
, Decode(mmtt.transaction_action_id
, 1, To_date(NULL)
, 2, To_date(NULL)
, 28, To_date(NULL)
, 3, To_date(NULL)
, 5, To_date(NULL)
, Decode(Sign(mmtt.primary_quantity)
, -1, To_date(NULL)
, mmtt.transaction_date)) date_received
, Decode(mmtt.transaction_status, 2, 5, 1) quantity_type
, mmtt.cost_group_id cost_group_id
, NVL(mmtt.allocated_lpn_id,NVL(mmtt.content_lpn_id,mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
WHERE
mmtt.posting_flag = ''Y''
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR -- pending txns
-- only picking side of the suggested transactions are used
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
AND mmtt.transaction_action_id NOT IN (24,30)
UNION ALL
-- receiving side of transfers
-- added 5/23/00
-- if quantity is in an lpn, then it is containerized
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('||l_mmtt_qty_part||'),5)
, mmtt.transaction_date date_received
, 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 Nvl(mmtt.transaction_status,0) <> 2 -- pending txns only
AND mmtt.transaction_action_id IN (2,28,3,5) ';
l_lot_select := '
, x.lot_number lot_number ';
l_lot_select2 := '
, lot.expiration_date lot_expiration_date';
l_lot_select := '
, NULL lot_number';
l_lot_select2 := '
, To_date(NULL) lot_expiration_date';
l_revision_select := '
, x.revision revision';
l_revision_select := '
, NULL revision';
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id
, x.revision revision
, x.lot_number lot_number '
|| l_lot_select2 || '
, x.subinventory_code subinventory_code
, sub.reservable_type reservable_type
, x.locator_id locator_id
, x.primary_quantity primary_quantity
, x.date_received date_received
, 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 (
SELECT
x.organization_id organization_id
, x.inventory_item_id inventory_item_id '
|| l_revision_select || l_lot_select || '
, x.subinventory_code subinventory_code
, x.locator_id locator_id
, SUM(x.primary_quantity) primary_quantity
, MIN(x.date_received) date_received
, 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 (' || l_reservation_stmt
|| l_onhand_stmt
|| l_pending_txn_stmt
|| l_suggestion_stmt
|| l_temp_sugg_txn_stmt || '
) x
WHERE x.organization_id = :organization_id
AND x.inventory_item_id = :inventory_item_id
GROUP BY
x.organization_id, x.inventory_item_id, x.revision '
|| l_lot_group || '
, 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 '
|| l_lot_from || '
WHERE
x.organization_id = sub.organization_id (+)
AND x.subinventory_code = sub.secondary_inventory_name (+) '
|| l_lot_where || l_lot_expiration_where || l_asset_sub_where
|| l_onhand_source_where || l_reservable_where ;
SELECT reservable_type 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,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 '1'
FROM mtl_secondary_inventories msinv
WHERE msinv.status_id IN
(SELECT mms.status_id
FROM mtl_material_statuses mms
WHERE NVL(mms.reservable_type, 1) = 1
AND mms.zone_control = 1)
AND 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 '1'
FROM mtl_item_locations mil
WHERE mil.status_id IN
(SELECT mms.status_id
FROM mtl_material_statuses mms
WHERE NVL(mms.reservable_type, 1) = 1
AND mms.locator_control = 1)
AND 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 '1'
FROM mtl_lot_numbers mln
WHERE mln.status_id IN
(SELECT mms.status_id
FROM mtl_material_statuses mms
WHERE NVL(mms.reservable_type, 1) = 1
AND mms.lot_control = 1)
AND 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(lpn_id, -9999)
AND rownum = 1;
l_update_quantity NUMBER;
l_update_quantity2 NUMBER; -- invConv change
l_update_quantity:= p_primary_quantity;
l_update_quantity2 := p_secondary_quantity; -- invConv change
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; -- invConv change
l_update_quantity:= 0;
l_update_quantity2 := 0; -- 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; -- invConv change
print_debug('in add_qty,1 rsv=TRUE, newATR='||g_nodes(l_loop_index).atr||' + '||l_update_quantity);
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_update_quantity;
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_update_quantity2; -- invConv change
l_update_quantity:= 0.0 - p_primary_quantity;
l_update_quantity2:= 0.0 - p_secondary_quantity; -- invConv change
l_update_quantity := l_old_factor;
l_update_quantity2 := l_old_factor2; -- invConv change
l_update_quantity:= 0.0 - l_new_factor;
l_update_quantity2:= 0.0 - l_new_factor2; -- invConv change
l_update_quantity:= 0;
l_update_quantity2:= 0; -- invConv change
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_update_quantity;
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_update_quantity2; -- 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; -- invConv change
l_update_quantity:= 0.0 - p_primary_quantity;
l_update_quantity2:= 0.0 - p_secondary_quantity; -- invConv change
l_update_quantity := l_old_factor;
l_update_quantity2 := l_old_factor2; -- invConv change
l_update_quantity:= 0.0 - l_new_factor;
l_update_quantity2:= 0.0 - l_new_factor2; -- invConv change
l_update_quantity:= 0;
l_update_quantity2:= 0; -- 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; -- invConv change
print_debug('in add_qty,3 rsv=TRUE, newATR='||g_nodes(l_loop_index).atr||' + '||l_update_quantity);
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_update_quantity;
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_update_quantity2; -- 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; -- invConv change
print_debug('in add_qty,3 rsv=TRUE, newATR='||g_nodes(l_loop_index).atr||' + '||l_update_quantity);
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_update_quantity;
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_update_quantity2; -- 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; -- invConv change
print_debug('in add_qty,3 rsv=TRUE, newATR='||g_nodes(l_loop_index).atr||' + '||l_update_quantity);
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_update_quantity;
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_update_quantity2; -- 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; -- invConv change
print_debug('in add_qty,3 rsv=TRUE, newATR='||g_nodes(l_loop_index).atr||' + '||l_update_quantity);
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_update_quantity;
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_update_quantity2; -- 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; -- invConv change
print_debug('in add_qty,3 rsv=TRUE, newATR='||g_nodes(l_loop_index).atr||' + '||l_update_quantity);
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_update_quantity;
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_update_quantity2; -- 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; -- invConv change
print_debug('in add_qty,3 rsv=TRUE, newATR='||g_nodes(l_loop_index).atr||' + '||l_update_quantity);
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_update_quantity;
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_update_quantity2; -- 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; -- invConv change
print_debug('in add_qty,3 rsv=TRUE, newATR='||g_nodes(l_loop_index).atr||' + '||l_update_quantity);
g_nodes(l_loop_index).atr := g_nodes(l_loop_index).atr + l_update_quantity;
g_nodes(l_loop_index).satr := NVL(g_nodes(l_loop_index).satr, 0) + l_update_quantity2; -- 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 -- invConv change (added SUM)
, SUM(x.secondary_quantity) secondary_quantity -- invConv change (+added SUM)
, 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 (
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 -- invConv change
, 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 -- invConv change
, 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) -- invConv change
--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
, NVL(mmtt.allocated_lpn_id,NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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)
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) -- invConv change
, 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,28,3)
--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 -- invConv change : GROUP BY because of SUM.
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 -- invConv change (added SUM)
, SUM(x.secondary_quantity) secondary_quantity -- invConv change (+added SUM)
, 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 (
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 -- invConv change
, 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 -- invConv change
, 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) -- invConv change
--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
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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)
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) -- invConv change
, 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,28,3)
--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 -- invConv change
, mtl_parameters mp -- Onhand Material Status Support
, mtl_material_statuses_b mms -- Onhand Material Status Support
WHERE
x.organization_id = loc.organization_id (+) -- invConv change
AND x.locator_id = loc.inventory_location_id (+) -- invConv change
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 -- invConv change
)
OR (l_onhand_source = 2 AND
Nvl(sub.availability_type, 1) = 1
AND Nvl(loc.availability_type, 1) = 1 -- invConv change
)
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 -- invConv change
AND Nvl(loc.availability_type, 1) = 1 -- invConv change
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 -- invConv change : GROUP BY because of SUM.
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
, 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 -- invConv change
, 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 (
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 -- invConv change
, 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 -- invConv change
, 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 -- invConv change
, 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) -- invConv change
--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
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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)
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) -- invConv change
--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
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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)
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) -- invConv change
, 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,28,3)
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) -- invConv change
, 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,28,3)
) 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)
)
AND (l_lot_expiration_control = 2 OR
lot.expiration_date IS NULL OR
l_lot_expiration_date IS NULL OR
lot.expiration_date > l_lot_expiration_date);
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 -- invConv change
, 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 (
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 -- invConv change
, 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 -- invConv change
, 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 -- invConv change
, 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) -- invConv change
--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
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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)
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) -- invConv change
--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
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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)
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) -- invConv change
, 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,28,3)
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) -- invConv change
, 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,28,3)
) 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 -- invConv change
, 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 (+) -- invConv change
AND x.organization_id = lot.organization_id (+) -- invConv change
AND x.lot_number = lot.lot_number (+) -- invConv change
AND x.organization_id = loc.organization_id (+) -- invConv change
AND x.locator_id = loc.inventory_location_id (+) -- invConv change
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 -- invConv change
AND Nvl(lot.inventory_atp_code, 1) = 1 -- invConv change
) OR
(l_onhand_source = 2 AND
Nvl(sub.availability_type, 1) = 1
AND Nvl(loc.availability_type, 1) = 1 -- invConv change
AND Nvl(lot.availability_type, 1) = 1 -- invConv change
) 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 -- invConv change
AND Nvl(lot.inventory_atp_code, 1) = 1 -- invConv change
AND Nvl(loc.availability_type, 1) = 1 -- invConv change
AND Nvl(lot.availability_type, 1) = 1 -- invConv change
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
)
)
)
)
)
AND (l_lot_expiration_control = 2 OR
lot.expiration_date IS NULL OR
l_lot_expiration_date IS NULL OR
lot.expiration_date > l_lot_expiration_date);
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 -- invConv change
, 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 (
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 -- invConv change
, 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 -- invConv change
, 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 -- invConv change
, 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) -- invConv change
--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
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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)
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) -- invConv change
--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
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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)
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) -- invConv change
, 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,28,3)
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) -- invConv change
, 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,28,3)
) 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 -- invConv change
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)
)
AND (l_lot_expiration_control = 2 OR
lot.expiration_date IS NULL OR
l_lot_expiration_date IS NULL OR
lot.expiration_date > l_lot_expiration_date);
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 -- invConv change
, 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 (
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 -- invConv change
, 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 -- invConv change
, 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 -- invConv change
, 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) -- invConv change
--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
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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)
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) -- invConv change
--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
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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)
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) -- invConv change
, 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,28,3)
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) -- invConv change
, 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,28,3)
) 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 -- invConv change
, 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 (+) -- invConv change
AND x.organization_id = lot.organization_id (+) -- invConv change
AND x.lot_number = lot.lot_number (+) -- invConv change
AND x.organization_id = loc.organization_id (+) -- invConv change
AND x.locator_id = loc.inventory_location_id (+) -- invConv change
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 -- invConv change
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 -- invConv change
AND Nvl(lot.inventory_atp_code, 1) = 1 -- invConv change
) OR
(l_onhand_source = 2 AND
Nvl(sub.availability_type, 1) = 1
AND Nvl(loc.availability_type, 1) = 1 -- invConv change
AND Nvl(lot.availability_type, 1) = 1 -- invConv change
) 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 -- invConv change
AND Nvl(lot.inventory_atp_code, 1) = 1 -- invConv change
AND Nvl(loc.availability_type, 1) = 1 -- invConv change
AND Nvl(lot.availability_type, 1) = 1 -- invConv change
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
)
)
)
)
)
AND (l_lot_expiration_control = 2 OR
lot.expiration_date IS NULL OR
l_lot_expiration_date IS NULL OR
lot.expiration_date > l_lot_expiration_date);
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 -- invConv change
, 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 (
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 -- invConv change
, 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 -- invConv change
, 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)
) -- invConv change
, 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) -- invConv change
--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
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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)
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) -- invConv change
, 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,28,3)
) 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 -- invConv change
, 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 (
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 -- invConv change
, 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 -- invConv change
, 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) -- invConv change
--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
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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)
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) -- invConv change
--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
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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)
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) -- invConv change
, 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,28,3)
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) -- invConv change
, 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,28,3)
) 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
)
)
AND (l_lot_expiration_control = 2 OR
lot.expiration_date IS NULL OR
l_lot_expiration_date IS NULL OR
lot.expiration_date > l_lot_expiration_date);
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 -- invConv change
, 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 (
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 -- invConv change
, 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 -- invConv change
, 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) -- invConv change
--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
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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)
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) -- invConv change
--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
,NVL(mmtt.allocated_lpn_id,
NVL(mmtt.content_lpn_id, mmtt.lpn_id)) lpn_id
, Decode(mmtt.transaction_status, 2 , 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
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)
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) -- invConv change
, 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,28,3)
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) -- invConv change
, 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,28,3)
) 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 -- invConv change
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
)
)
AND (l_lot_expiration_control = 2 OR
lot.expiration_date IS NULL OR
l_lot_expiration_date IS NULL OR
lot.expiration_date > l_lot_expiration_date);
g_rsv_info.DELETE;
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) -- invConv change
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) -- invConv change
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) -- invConv change
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) -- invConv change
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) -- invConv change
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) -- invConv change
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; -- invConv change
g_rsv_qty_satr.delete; -- invConv change
print_debug(' insert into mtl_rsv_quantities_temp, item='||g_rootinfos(l_root_id).inventory_item_id||', revision='||g_rsv_qty_revision(i)||'...');
INSERT INTO MTL_RSV_QUANTITIES_TEMP (
organization_id
,inventory_item_id
,node_level
,revision
,lot_number
,subinventory_code
,locator_id
,grade_code -- invConv change
,cost_group_id
,lpn_id
,qoh
,atr
,sqoh -- invConv change
,satr -- invConv change
) 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_rootinfos(l_root_id).grade_code -- invConv change
,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) -- invConv change
,g_rsv_qty_satr(i) -- invConv change
);
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; -- invConv change
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 -- invConv change
, 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 -- invConv change
, x_srqoh => l_srqoh -- invConv change
, x_sqr => l_sqr -- invConv change
, x_sqs => l_sqs -- invConv change
, x_satt => l_satt -- invConv change
, x_satr => l_satr -- invConv change
, 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 -- invConv change
, 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 -- invConv change
, x_srqoh OUT NOCOPY NUMBER -- invConv change
, x_sqr OUT NOCOPY NUMBER -- invConv change
, x_sqs OUT NOCOPY NUMBER -- invConv change
, x_satt OUT NOCOPY NUMBER -- invConv change
, x_satr OUT NOCOPY NUMBER -- invConv change
, 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
) IS
l_secondary_quantity NUMBER := NULL; -- invConv change
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 -- invConv change
, 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 -- invConv change
, x_srqoh => l_srqoh -- invConv change
, x_sqr => l_sqr -- invConv change
, x_sqs => l_sqs -- invConv change
, x_satt => l_satt -- invConv change
, x_satr => l_satr -- invConv change
, 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);
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 -- invConv change
, 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 -- invConv change
, x_srqoh OUT NOCOPY NUMBER -- invConv change
, x_sqr OUT NOCOPY NUMBER -- invConv change
, x_sqs OUT NOCOPY NUMBER -- invConv change
, x_satt OUT NOCOPY NUMBER -- invConv change
, x_satr OUT NOCOPY NUMBER -- invConv change
, p_transfer_subinventory_code IN VARCHAR2
, p_cost_group_id IN NUMBER
, p_containerized IN NUMBER
, p_call_for_form IN VARCHAR2
) 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;