[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT tracking_quantity_ind
, lot_control_code
, grade_control_flag
FROM mtl_system_items
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id;
SELECT 1 INTO l_table_count FROM dual
WHERE EXISTS (SELECT 1 FROM mtl_consigned_qty_temp
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id);
SELECT Nvl(sum(primary_quantity),0)
, Nvl(sum(secondary_quantity),0)
INTO l_qoh, l_sqoh
FROM mtl_consigned_qty_temp
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND
DECODE(p_tree_mode,INV_Quantity_Tree_PUB.g_loose_only_mode,containerized,'-1')=
DECODE(p_tree_mode,inv_quantity_tree_pub.g_loose_only_mode,0,'-1')
AND Nvl(planning_organization_id, -999) =
NVL(p_planning_org_id,Nvl(planning_organization_id, -999))
AND NVL(owning_organization_id, -999) =
NVL(p_owning_org_id,Nvl(owning_organization_id, -999));
SELECT Nvl(sum(primary_quantity),0)
, Nvl(sum(secondary_quantity),0)
INTO l_qoh, l_sqoh
FROM mtl_consigned_qty_temp
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND
DECODE(p_tree_mode,INV_Quantity_Tree_PUB.g_loose_only_mode,containerized,'-1')=
DECODE(p_tree_mode,inv_quantity_tree_pub.g_loose_only_mode,0,'-1')
AND NVL(planning_organization_id, -999) =
NVL(p_planning_org_id,Nvl(planning_organization_id, -999))
AND Nvl(owning_organization_id, -999) =
NVL(p_owning_org_id,Nvl(owning_organization_id, -999))
AND revision = p_revision;
SELECT Nvl(sum(primary_quantity),0)
, Nvl(sum(secondary_quantity),0)
INTO l_qoh, l_sqoh
FROM mtl_consigned_qty_temp
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND Nvl(planning_organization_id, -999) =
NVL(p_planning_org_id,Nvl(planning_organization_id, -999))
AND Nvl(owning_organization_id, -999) =
NVL(p_owning_org_id,Nvl(owning_organization_id, -999))
AND
DECODE(p_tree_mode,INV_Quantity_Tree_PUB.g_loose_only_mode,containerized,'-1')=
DECODE(p_tree_mode,inv_quantity_tree_pub.g_loose_only_mode,0,'-1')
AND Nvl(revision,'@@@') = Nvl(p_revision,Nvl(revision,'@@@'))
AND Nvl(lot_number,'@@@')=Nvl(p_lot_number,Nvl(lot_number,'@@@'));
SELECT Nvl(sum(primary_quantity),0)
, Nvl(sum(secondary_quantity),0)
INTO l_qoh, l_sqoh
FROM mtl_consigned_qty_temp
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND Nvl(planning_organization_id, -999) =
NVL(p_planning_org_id,Nvl(planning_organization_id, -999))
AND Nvl(owning_organization_id, -999) =
NVL(p_owning_org_id,Nvl(owning_organization_id, -999))
AND
DECODE(p_tree_mode,INV_Quantity_Tree_PUB.g_loose_only_mode,containerized,'-1')=
DECODE(p_tree_mode,inv_quantity_tree_pub.g_loose_only_mode,0,'-1')
AND Nvl(revision,'@@@') = Nvl(p_revision,Nvl(revision,'@@@'))
AND Nvl(lot_number,'@@@')=Nvl(p_lot_number,Nvl(lot_number,'@@@'))
AND subinventory_code = p_subinventory_code;
SELECT Nvl(sum(primary_quantity),0)
, Nvl(sum(secondary_quantity),0)
INTO l_qoh, l_sqoh
FROM mtl_consigned_qty_temp
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND Nvl(planning_organization_id, -999) =
Nvl(p_planning_org_id,Nvl(planning_organization_id, -999))
AND Nvl(owning_organization_id, -999) =
Nvl(p_owning_org_id,Nvl(owning_organization_id, -999))
AND
DECODE(p_tree_mode,INV_Quantity_Tree_PUB.g_loose_only_mode,containerized,'-1')=
DECODE(p_tree_mode,inv_quantity_tree_pub.g_loose_only_mode,0,'-1')
AND Nvl(revision,'@@@') = Nvl(p_revision,Nvl(revision,'@@@'))
AND Nvl(lot_number,'@@@')=Nvl(p_lot_number,Nvl(lot_number,'@@@'))
AND subinventory_code = p_subinventory_code
AND locator_id = p_locator_id ;
SELECT Nvl(sum(primary_quantity),0)
, Nvl(sum(secondary_quantity),0)
INTO l_qoh, l_sqoh
FROM mtl_consigned_qty_temp
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND Nvl(planning_organization_id, -999) =
Nvl(p_planning_org_id,Nvl(planning_organization_id, -999))
AND Nvl(owning_organization_id, -999) =
Nvl(p_owning_org_id,Nvl(owning_organization_id, -999))
AND
Decode(p_tree_mode,INV_Quantity_Tree_PUB.g_loose_only_mode,containerized,'-1')=
Decode(p_tree_mode,inv_quantity_tree_pub.g_loose_only_mode,0,'-1')
AND Nvl(revision,'@@@') = Nvl(p_revision,Nvl(revision,'@@@'))
AND Nvl(lot_number,'@@@')=Nvl(p_lot_number,Nvl(lot_number,'@@@'))
AND subinventory_code = p_subinventory_code
AND locator_id = p_locator_id
AND cost_group_id = p_cost_group_id;
l_revision_select long;
l_lot_select long;
l_lot_select2 long;
-- To improve performance, avoid using select from dual;
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 || ' primary_quantity
, ' || l_onhand_sqty_part || ' secondary_quantity
, nvl(moq.orig_date_received,
moq.date_received) date_received
, 1 quantity_type
, moq.cost_group_id cost_group_id
, decode(moq.containerized_flag,
1, 1, 0) containerized
, moq.planning_organization_id planning_organization_id
, moq.owning_organization_id owning_organization_id
FROM
mtl_onhand_quantities_detail moq
WHERE moq.organization_id <> Nvl(moq.planning_organization_id,moq.organization_id)
OR moq.organization_id <> nvl(moq.owning_organization_id, moq.organization_id) ';
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, 5,-1,-1, Sign(mmtt.primary_quantity))
)
* Abs('|| l_mmtt_qty_part || ')
, Decode (mmtt.transaction_status, 2, 1
, Decode(mmtt.transaction_action_id
, 1, -1, 2, -1, 28, -1, 3, 5,-1,-1, Sign(mmtt.secondary_transaction_quantity))
)
* Abs('|| l_mmtt_sqty_part || ')
, Decode(mmtt.transaction_action_id
, 1, To_date(NULL)
, 2, To_date(NULL)
, 28, To_date(NULL)
, 3, 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
, least(1,NVL(mmtt.lpn_id,0)+NVL(mmtt.content_lpn_id,0))
containerized
, planning_organization_id planning_organziation_id
, owning_organization_id owning_organization_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,5,21,29,32,34)
)
-- dont look at scrap and costing txns
AND mmtt.transaction_action_id NOT IN (24,30)
AND( (mmtt.organization_id <> Nvl(mmtt.planning_organization_id,mmtt.organization_id))
OR(mmtt.organization_id <> Nvl(mmtt.owning_organization_id,mmtt.organization_id)))
UNION ALL
-- pending transactions and suggestions in mmtt with lot numbers in lots_temp
--added 1 to decode statement so that we make sure the
--issue qtys in mmtt are seen as negative.
-- if quantity is in an lpn, then it is containerized.
-- 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, 5,-1,-1, Sign(mmtt.transaction_quantity))
)
* Abs('||l_mtlt_qty_part||')
, Decode(mmtt.transaction_status, 2, 1
, Decode(mmtt.transaction_action_id
, 1, -1, 2, -1, 28, -1, 3, 5,-1,-1, Sign(mmtt.secondary_transaction_quantity))
)
* Abs('||l_mtlt_sqty_part||')
, Decode(mmtt.transaction_action_id
, 1, To_date(NULL)
, 2, To_date(NULL)
, 28, To_date(NULL)
, 3, 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
, least(1,NVL(mmtt.lpn_id,0)+NVL(mmtt.content_lpn_id,0))
containerized
, mmtt.planning_organization_id planning_organization_id
, mmtt.owning_organization_id owning_organization_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,5,21,29,32,34)
)
-- dont look at scrap and costing txns
AND mmtt.transaction_action_id NOT IN (24,30)
AND ( (mmtt.organization_id <>Nvl(mmtt.planning_organization_id,mmtt.organization_id))
OR(mmtt.organization_id <>Nvl(mmtt.owning_organization_id,mmtt.organization_id))) ';
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, 5,-1,-1, Sign(mmtt.primary_quantity))
)
* Abs('|| l_mmtt_qty_part || ')
, Decode(mmtt.transaction_status, 2, 1
, Decode(mmtt.transaction_action_id
, 1, -1, 2, -1, 28, -1, 3, 5,-1,-1, Sign(mmtt.secondary_transaction_quantity))
)
* Abs('|| l_mmtt_sqty_part || ')
, Decode(mmtt.transaction_action_id
, 1, To_date(NULL)
, 2, To_date(NULL)
, 28, To_date(NULL)
, 3, 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
, least(1,NVL(mmtt.lpn_id,0)+NVL(mmtt.content_lpn_id,0))
containerized
, mmtt.planning_organization_id planning_organization_id
, mmtt.owning_organization_id owning_organization_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,5,21,29,32,34)
)
-- dont look at scrap and costing txns
AND mmtt.transaction_action_id NOT IN (24,30)
AND ( (mmtt.organization_id <> Nvl(mmtt.planning_organization_id,mmtt.organization_id))
OR(mmtt.organization_id <> Nvl(mmtt.owning_organization_id,mmtt.organization_id))) ';
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.secondary_quantity secondary_quantity
, x.date_received date_received
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.containerized containerized
, x.planning_organization_id planning_organization_id
, x.owning_organization_id owning_organization_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
, SUM(x.secondary_quantity) secondary_quantity
, MIN(x.date_received) date_received
, x.quantity_type quantity_type
, x.cost_group_id cost_group_id
, x.containerized containerized
, x.planning_organization_id planning_organization_id
, x.owning_organization_id owning_organization_id
FROM ('
|| l_onhand_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.containerized
, x.planning_organization_id, x.owning_organization_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 ;
INSERT INTO mtl_consigned_qty_temp (organization_id,
inventory_item_id,
revision,
lot_number,
lot_expiration_date,
subinventory_code,
reservable_type,
locator_id,
grade_code, -- invConv change
primary_quantity,
secondary_quantity, -- invConv change
transactable_vmi,
transactable_secondary_vmi, -- invConv change
date_received,
quantity_type,
cost_group_id,
containerized,
planning_organization_id,
owning_organization_id)
VALUES
(
ll_organization_id,
ll_inventory_item_id,
ll_revision,
ll_lot_number,
ll_lot_expiration_date,
ll_subinventory_code,
ll_reservable_type,
ll_locator_id,
p_grade_code, -- invConv change
ll_primary_quantity,
ll_secondary_quantity, -- invConv change
ll_transactable_vmi,
ll_transactable_secondary_vmi, -- invConv change
ll_date_received,
ll_quantity_type,
ll_cost_group_id,
ll_containerized,
ll_planning_organization_id,
ll_owning_organization_id);
inv_log_util.trace('When others Ex. in Inserting in temp table','CONSIGNED_VALIDATIONS',9);
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 reservable_type
FROM mtl_secondary_inventories
WHERE organization_id = org_id
AND secondary_inventory_name = subinv;
SELECT '1'
FROM mtl_item_locations mil
WHERE mil.status_id IN
(SELECT mms.status_id
FROM mtl_material_statuses mms
WHERE NVL(mms.attribute1, '1') = '1'
AND mms.locator_control = 1)
AND mil.organization_id = org_id
AND mil.inventory_location_id = loct_id;
SELECT '1'
FROM mtl_lot_numbers mln
WHERE mln.status_id IN
(SELECT mms.status_id
FROM mtl_material_statuses mms
WHERE NVL(mms.attribute1, '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;
DELETE FROM mtl_consigned_qty_temp;
SELECT Nvl(sum(primary_transaction_quantity),0)
, Nvl(sum(secondary_transaction_quantity),0)
INTO x_att
, x_satt
FROM mtl_onhand_quantities_detail
WHERE owning_organization_id = organization_id
AND organization_id = p_organization_id
AND owning_organization_id <> p_owning_org_id
AND inventory_item_id = p_inventory_item_id
AND nvl(revision,'@@@') = nvl(p_revision, nvl(revision,'@@@'))
AND nvl(lot_number, '@@@') = nvl(p_lot_number, nvl(lot_number, '@@@'))
AND subinventory_code = nvl(p_subinventory_code, subinventory_code)
AND nvl(locator_id, -999) = nvl(p_locator_id, nvl(locator_id, -999))
AND nvl(cost_group_id, -999) = nvl(p_cost_group_id, nvl(cost_group_id, -999));
--SELECT COUNT(*)INTO l_table_count FROM mtl_consigned_qty_temp
--WHERE inventory_item_id = p_inventory_item_id
--AND organization_id = p_organization_id;
SELECT 1 INTO l_table_count FROM dual
WHERE EXISTS (SELECT 1 FROM mtl_consigned_qty_temp
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id);
PROCEDURE update_consigned_quantities
( x_return_status OUT NOCOPY varchar2
, x_msg_count OUT NOCOPY varchar2
, x_msg_data OUT NOCOPY varchar2
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_revision IN VARCHAR2
, p_lot_number IN VARCHAR
, p_subinventory_code IN VARCHAR2
, p_locator_id IN NUMBER
, p_grade_code IN VARCHAR2 DEFAULT NULL -- invConv change
, p_primary_quantity IN NUMBER
, p_secondary_quantity IN NUMBER DEFAULT NULL -- invConv change
, p_cost_group_id IN NUMBER
, p_containerized IN NUMBER
, p_planning_organization_id IN NUMBER
, p_owning_organization_id IN number
) IS
-- l_is_reservable_sub BOOLEAN; -- invConv change : not used anymore
inv_log_util.trace('in update_consigned_quantities is_rsv=TRUE', 'CONSIGNED_VALIDATIONS',9);
inv_log_util.trace('in update_consigned_quantities is_rsv=FALSE', 'CONSIGNED_VALIDATIONS',9);
INSERT INTO mtl_consigned_qty_temp ( organization_id,
inventory_item_id,
revision,
lot_number,
lot_expiration_date,
subinventory_code,
reservable_type,
locator_id,
grade_code, -- invConv change
primary_quantity,
secondary_quantity, -- invConv change
transactable_vmi,
transactable_secondary_vmi, -- invConv change
date_received,
quantity_type,
cost_group_id,
containerized,
planning_organization_id,
owning_organization_id)
VALUES
(p_organization_id,
p_inventory_item_id,
p_revision,
p_lot_number,
NULL,
p_subinventory_code,
l_reservable_type,
p_locator_id,
p_grade_code, -- invConv change
p_primary_quantity,
p_secondary_quantity, -- invConv change
p_primary_quantity,
p_secondary_quantity, -- invConv change
NULL,
1,
p_cost_group_id,
p_containerized,
p_planning_organization_id,
p_owning_organization_id);
inv_log_util.trace('Ex in update_vmi_quantities','CONSIGNED_VALIDATIONS',9);
END update_consigned_quantities;
SELECT decode(consume_consigned_flag,'Y',1,0), decode(consume_vmi_flag,'Y',1,0),weight
INTO x_consume_consigned, x_consume_vmi,l_weight from
(SELECT nvl(consume_consigned_flag,'N') consume_consigned_flag, nvl(consume_vmi_flag,'N') consume_vmi_flag,weight
FROM MTL_CONSUMPTION_DEFINITION
WHERE TRANSACTION_TYPE_ID = P_TRANSACTION_TYPE_ID
and nvl(ORGANIZATION_ID, nvl(P_ORGANIZATION_ID,-999) )= nvl(P_ORGANIZATION_ID,-999)
and nvl(SUBINVENTORY_CODE, nvl(P_SUBINVENTORY_CODE,-999) ) = nvl(P_SUBINVENTORY_CODE,-999)
and nvl( XFER_SUBINVENTORY_CODE, nvl(P_XFER_SUBINVENTORY_CODE, -999) )
= nvl(P_XFER_SUBINVENTORY_CODE, -999)
and nvl( FROM_LOCATOR_ID, nvl(P_FROM_LOCATOR_ID, -999) ) = nvl(P_FROM_LOCATOR_ID, -999)
and nvl( TO_LOCATOR_ID, nvl(P_TO_LOCATOR_ID, -999) ) = nvl(P_TO_LOCATOR_ID, -999)
and nvl( INVENTORY_ITEM_ID , nvl( P_INVENTORY_ITEM_ID ,-999)) = nvl( P_INVENTORY_ITEM_ID , -999)
and nvl(OWNING_ORGANIZATION_ID, nvl(P_OWNING_ORGANIZATION_ID, -999) ) = nvl(P_OWNING_ORGANIZATION_ID, -999)
and nvl(PLANNING_ORGANIZATION_ID, nvl( P_PLANNING_ORGANIZATION_ID, -999))
= nvl( P_PLANNING_ORGANIZATION_ID, -999)
ORDER BY Nvl(weight,-1) DESC )
where ROWNUM < 2;
SELECT 1 INTO l_pending_txn_cnt FROM dual
WHERE exists (select 1 from mtl_material_transactions_temp
where organization_id = P_ORGANIZATION_ID
and Nvl(transaction_status,1) in (1,3) --pending txn
AND inventory_item_id = p_item_id
and SUBINVENTORY_CODE = P_SUBINVENTORY_CODE
and LOCATOR_ID = p_locator_id);
SELECT 1 INTO l_pending_txn_cnt FROM dual
WHERE exists (select 1 from mtl_material_transactions_temp
where organization_id = P_ORGANIZATION_ID
and Nvl(transaction_status,1) in (1,3) --pending txn
AND ((transfer_lpn_id = p_lpn_id)
OR (content_lpn_id = p_lpn_id)
OR (lpn_id = p_lpn_id)
OR (allocated_lpn_id = p_lpn_id))
and SUBINVENTORY_CODE = P_SUBINVENTORY_CODE
and LOCATOR_ID = p_locator_id);
SELECT 1 INTO l_pending_txn_cnt FROM dual
WHERE exists (select 1 from mtl_material_transactions_temp
where organization_id = P_ORGANIZATION_ID
and Nvl(transaction_status,1) in (1,3) --pending txn
AND inventory_item_id = p_item_id
and SUBINVENTORY_CODE = P_SUBINVENTORY_CODE);
SELECT 1 INTO l_pending_txn_cnt FROM dual
WHERE exists (select 1 from mtl_material_transactions_temp
where organization_id = P_ORGANIZATION_ID
and Nvl(transaction_status,1) in (1,3) --pending txn
AND ((transfer_lpn_id = p_lpn_id)
OR (content_lpn_id = p_lpn_id)
OR (lpn_id = p_lpn_id)
OR (allocated_lpn_id = p_lpn_id))
and SUBINVENTORY_CODE = P_SUBINVENTORY_CODE);
SELECT tracking_quantity_ind
, lot_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id;
SELECT SUM(moq.primary_transaction_quantity)
, SUM( NVL(moq.secondary_transaction_quantity, 0))
INTO l_moq_qty
, l_moq_sqty
FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln, mtl_secondary_inventories msi
WHERE moq.organization_id = p_org_id
AND moq.inventory_item_id = p_item_id
AND msi.organization_id = moq.organization_id
AND msi.secondary_inventory_name = moq.subinventory_code
AND moq.organization_id = nvl(moq.planning_organization_id, moq.organization_id)
AND moq.lot_number = mln.lot_number(+)
AND moq.organization_id = mln.organization_id(+)
AND moq.inventory_item_id = mln.inventory_item_id(+)
AND trunc(nvl(mln.expiration_date, sysdate+1)) > trunc(sysdate)
AND nvl(moq.planning_tp_type,2) = 2;
SELECT SUM(mon.primary_transaction_quantity)
, SUM( NVL(mon.secondary_transaction_quantity, 0))
INTO l_moq_qty
, l_moq_sqty
FROM mtl_onhand_net mon, mtl_lot_numbers mln
WHERE mon.organization_id = p_org_id
AND mon.inventory_item_id = p_item_id
AND mon.organization_id = nvl(mon.planning_organization_id, mon.organization_id)
AND mon.lot_number = mln.lot_number(+)
AND mon.organization_id = mln.organization_id(+)
AND mon.inventory_item_id = mln.inventory_item_id(+)
AND trunc(nvl(mln.expiration_date, sysdate+1)) > trunc(sysdate)
AND nvl(mon.planning_tp_type,2) = 2;
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
, SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.secondary_transaction_quantity)) * Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
INTO l_mmtt_qty_src
, l_mmtt_sqty_src
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id NOT IN (24,30)
AND EXISTS (SELECT 'x' FROM mtl_secondary_inventories msi
WHERE msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.subinventory_code
AND msi.availability_type = decode(p_include_nonnet,1,msi.availability_type,1))
AND mmtt.planning_organization_id IS NULL
AND EXISTS (SELECT 'x' FROM mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = mln.lot_number(+)
AND p_org_id = mln.organization_id(+)
AND p_item_id = mln.inventory_item_id(+)
/* nsinghi MIN-MAX INVCONV start */
AND nvl(mln.availability_type,2) = decode(p_include_nonnet,1,nvl(mln.availability_type,2),1)
AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),SYSDATE+1))> trunc(sysdate))
AND (mmtt.locator_id IS NULL OR
(mmtt.locator_id IS NOT NULL AND
EXISTS (SELECT 'x' FROM mtl_item_locations mil
WHERE mmtt.organization_id = mil.organization_id
AND mmtt.locator_id = mil.inventory_location_id
AND mmtt.subinventory_code = mil.subinventory_code
AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1))))
/* nsinghi MIN-MAX INVCONV end */
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Abs(mmtt.primary_quantity))
, SUM(Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
INTO l_mmtt_qty_dest
, l_mmtt_sqty_dest
FROM mtl_material_transactions_temp mmtt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3)
AND
(
(mmtt.transfer_subinventory IS NULL)
OR
(
mmtt.transfer_subinventory IS NOT NULL
AND EXISTS
(
SELECT 'x' FROM mtl_secondary_inventories msi
WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id)
AND msi.secondary_inventory_name = mmtt.transfer_subinventory
AND msi.availability_type = decode(p_include_nonnet,1,msi.availability_type,1)
)
)
)
AND mmtt.planning_organization_id IS NULL
AND EXISTS
(
SELECT 'x' FROM mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = mln.lot_number (+)
AND decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id) = mln.organization_id(+)
AND p_item_id = mln.inventory_item_id(+)
/* nsinghi MIN-MAX INVCONV start */
AND nvl(mln.availability_type,2) = decode(p_include_nonnet,1,nvl(mln.availability_type,2),1)
AND trunc(nvl(nvl(mtlt.lot_expiration_Date,mln.expiration_date),sysdate+1))> trunc(sysdate)
)
AND
(
mmtt.transfer_to_location IS NULL OR
(
mmtt.transfer_to_location IS NOT NULL AND
EXISTS
(
SELECT 'x' FROM mtl_item_locations mil
WHERE decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id) = mil.organization_id
AND mmtt.transfer_to_location = mil.inventory_location_id
AND mmtt.transfer_subinventory = mil.subinventory_code
AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)
)
)
)
/* nsinghi MIN-MAX INVCONV end */
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mtlt.primary_quantity)) * Abs( mtlt.primary_quantity ))
, SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mtlt.secondary_quantity)) * Abs( NVL(mtlt.secondary_quantity, 0) ))
INTO l_mmtt_qty_src
, l_mmtt_sqty_src
FROM mtl_material_transactions_temp mmtt,mtl_transaction_lots_temp mtlt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mmtt.subinventory_code IS NOT NULL
AND mmtt.subinventory_code IS NOT NULL
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id NOT IN (24,30)
AND EXISTS (SELECT 'x' FROM mtl_secondary_inventories msi
WHERE msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.subinventory_code
)
AND mmtt.planning_organization_id IS NULL
AND EXISTS (SELECT 'x' FROM mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = mln.lot_number(+)
AND p_org_id = mln.organization_id(+)
AND p_item_id = mln.inventory_item_id(+)
AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),SYSDATE+1))> trunc(sysdate))
AND (mmtt.locator_id IS NULL OR
(mmtt.locator_id IS NOT NULL AND
EXISTS (SELECT 'x' FROM mtl_item_locations mil
WHERE mmtt.organization_id = mil.organization_id
AND mmtt.locator_id = mil.inventory_location_id
AND mmtt.subinventory_code = mil.subinventory_code)))
AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.subinventory_code,
mmtt.locator_id,
mtlt.lot_number,
mmtt.lpn_id, mmtt.transaction_action_id), mms.status_id)
AND mms.availability_type =1)
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Abs(mtlt.primary_quantity))
, SUM(Abs( NVL(mtlt.secondary_quantity, 0) ))
INTO l_mmtt_qty_dest
, l_mmtt_sqty_dest
FROM mtl_material_transactions_temp mmtt,mtl_transaction_lots_temp mtlt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3)
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND
(
(mmtt.transfer_subinventory IS NULL)
OR
(
mmtt.transfer_subinventory IS NOT NULL
AND EXISTS
(
SELECT 'x' FROM mtl_secondary_inventories msi
WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id)
AND msi.secondary_inventory_name = mmtt.transfer_subinventory
)
)
)
AND mmtt.planning_organization_id IS NULL
AND EXISTS
(
SELECT 'x' FROM mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = mln.lot_number (+)
AND decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id) = mln.organization_id(+)
AND p_item_id = mln.inventory_item_id(+)
/* nsinghi MIN-MAX INVCONV start */
AND trunc(nvl(nvl(mtlt.lot_expiration_Date,mln.expiration_date),sysdate+1))> trunc(sysdate)
)
AND
(
mmtt.transfer_to_location IS NULL OR
(
mmtt.transfer_to_location IS NOT NULL AND
EXISTS
(
SELECT 'x' FROM mtl_item_locations mil
WHERE decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id) = mil.organization_id
AND mmtt.transfer_to_location = mil.inventory_location_id
AND mmtt.transfer_subinventory = mil.subinventory_code
)
)
)
AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
WHERE mms.status_id= nvl(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,
mmtt.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,
mmtt.lpn_id, mmtt.transaction_action_id)), mms.status_id)
AND mms.availability_type =1)
/* nsinghi MIN-MAX INVCONV end */
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
, SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.secondary_transaction_quantity)) * Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
INTO l_mmtt_qty_src
, l_mmtt_sqty_src
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id NOT IN (24,30)
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.subinventory_code
AND msi.availability_type = decode(p_include_nonnet,1,msi.availability_type,1))
AND mmtt.planning_organization_id IS NULL
/* nsinghi MIN-MAX INVCONV start */
AND (mmtt.locator_id IS NULL OR
(mmtt.locator_id IS NOT NULL AND
EXISTS (select 'x' from mtl_item_locations mil
WHERE mmtt.organization_id = mil.organization_id
AND mmtt.locator_id = mil.inventory_location_id
AND mmtt.subinventory_code = mil.subinventory_code
AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1))))
/* nsinghi MIN-MAX INVCONV end */
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Abs(mmtt.primary_quantity))
, SUM(Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
INTO l_mmtt_qty_dest
, l_mmtt_sqty_dest
FROM mtl_material_transactions_temp mmtt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3)
AND ((mmtt.transfer_subinventory IS NULL) OR
(mmtt.transfer_subinventory IS NOT NULL
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id)
AND msi.secondary_inventory_name = mmtt.transfer_subinventory
AND msi.availability_type = decode(p_include_nonnet,1,msi.availability_type,1))))
AND mmtt.planning_organization_id IS NULL
/* nsinghi MIN-MAX INVCONV start */
AND (mmtt.transfer_to_location IS NULL OR
(mmtt.transfer_to_location IS NOT NULL AND
EXISTS (select 'x' from mtl_item_locations mil
WHERE decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id) = mil.organization_id
AND mmtt.transfer_to_location = mil.inventory_location_id
AND mmtt.transfer_subinventory = mil.subinventory_code
AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1))))
/* nsinghi MIN-MAX INVCONV end */
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
, SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.secondary_transaction_quantity)) * Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
INTO l_mmtt_qty_src
, l_mmtt_sqty_src
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id NOT IN (24,30)
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.subinventory_code)
AND mmtt.planning_organization_id IS NULL
/* nsinghi MIN-MAX INVCONV start */
AND (mmtt.locator_id IS NULL OR
(mmtt.locator_id IS NOT NULL AND
EXISTS (select 'x' from mtl_item_locations mil
WHERE mmtt.organization_id = mil.organization_id
AND mmtt.locator_id = mil.inventory_location_id
AND mmtt.subinventory_code = mil.subinventory_code)))
/* nsinghi MIN-MAX INVCONV end */
AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.subinventory_code,
mmtt.locator_id,
mmtt.lot_number,
mmtt.lpn_id, mmtt.transaction_action_id), mms.status_id)
AND mms.availability_type =1)
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Abs(mmtt.primary_quantity))
, SUM(Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
INTO l_mmtt_qty_dest
, l_mmtt_sqty_dest
FROM mtl_material_transactions_temp mmtt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3)
AND ((mmtt.transfer_subinventory IS NULL) OR
(mmtt.transfer_subinventory IS NOT NULL
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id)
AND msi.secondary_inventory_name = mmtt.transfer_subinventory)))
AND mmtt.planning_organization_id IS NULL
/* nsinghi MIN-MAX INVCONV start */
AND (mmtt.transfer_to_location IS NULL OR
(mmtt.transfer_to_location IS NOT NULL AND
EXISTS (select 'x' from mtl_item_locations mil
WHERE decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id) = mil.organization_id
AND mmtt.transfer_to_location = mil.inventory_location_id
AND mmtt.transfer_subinventory = mil.subinventory_code)))
/* nsinghi MIN-MAX INVCONV end */
AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
WHERE mms.status_id= nvl(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,
mmtt.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,
mmtt.lpn_id, mmtt.transaction_action_id)), mms.status_id)
AND mms.availability_type =1)
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(inv_decimals_pub.get_primary_quantity( p_org_id
,p_item_id
,mtrl.uom_code
,mtrl.quantity - NVL(mtrl.quantity_delivered,0))
)
INTO l_lpn_qty
FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh, mtl_transaction_types mtt
where mtrl.organization_id = p_org_id
AND mtrl.inventory_item_id = p_item_id
AND mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = 6 -- Putaway Move Order
AND mtrl.transaction_source_type_id = 5 -- Wip
AND mtt.transaction_action_id = 31 -- WIP Assembly Completion
AND mtt.transaction_type_id = mtrl.transaction_type_id
AND mtrl.line_status = 7 -- Pre Approved
AND mtrl.lpn_id is not null;
SELECT SUM(moq.primary_transaction_quantity)
, SUM( NVL(moq.secondary_transaction_quantity, 0))
INTO l_moq_qty
, l_moq_sqty
FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln
WHERE moq.organization_id = p_org_id
AND moq.inventory_item_id = p_item_id
AND moq.subinventory_code = p_subinv
AND moq.lot_number = mln.lot_number(+)
AND moq.organization_id = mln.organization_id(+)
AND moq.inventory_item_id = mln.inventory_item_id(+)
AND trunc(nvl(mln.expiration_date, sysdate+1)) > trunc(sysdate);
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
, SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.secondary_transaction_quantity)) * Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
INTO l_mmtt_qty_src
, l_mmtt_sqty_src
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.subinventory_code = p_subinv
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND Nvl(mmtt.transaction_status,0) <> 2
AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = mln.lot_number (+)
AND p_org_id = mln.organization_id(+)
AND p_item_id = mln.inventory_item_id(+)
AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),sysdate+1))> trunc(sysdate))
AND mmtt.transaction_action_id NOT IN (24,30);
SELECT SUM(Abs(mmtt.primary_quantity))
, SUM(Abs( NVL(mmtt.secondary_transaction_quantity, 0)))
INTO l_mmtt_qty_dest
, l_mmtt_sqty_dest
FROM mtl_material_transactions_temp mmtt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.transfer_subinventory = p_subinv
AND mmtt.posting_flag = 'Y'
AND Nvl(mmtt.transaction_status,0) <> 2
AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = mln.lot_number (+)
AND decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = mln.organization_id(+)
AND p_item_id = mln.inventory_item_id(+)
AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),sysdate+1))> trunc(sysdate))
AND mmtt.transaction_action_id in (2,28,3);
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
, SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.secondary_transaction_quantity)) * Abs( NVL(mmtt.secondary_transaction_quantity, 0) ))
INTO l_mmtt_qty_src
, l_mmtt_sqty_src
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.subinventory_code = p_subinv
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id NOT IN (24,30);
SELECT SUM(Abs(mmtt.primary_quantity))
, SUM(Abs( NVL(mmtt.secondary_transaction_quantity, 0)))
INTO l_mmtt_qty_dest
, l_mmtt_sqty_dest
FROM mtl_material_transactions_temp mmtt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.transfer_subinventory = p_subinv
AND mmtt.posting_flag = 'Y'
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3);
SELECT lot_control_code
into l_lot_control
from mtl_system_items_b
where inventory_item_id = p_item_id
and organization_id = p_org_id;
SELECT SUM(moq.primary_transaction_quantity)
INTO l_moq_qty
FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln
WHERE moq.organization_id = p_org_id
AND moq.inventory_item_id = p_item_id
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = moq.organization_id and
msi.secondary_inventory_name = moq.subinventory_code
AND nvl(msi.inventory_atp_code,1) = 1)
AND moq.organization_id = nvl(moq.planning_organization_id, moq.organization_id)
AND moq.lot_number = mln.lot_number(+)
AND moq.organization_id = mln.organization_id(+)
AND moq.inventory_item_id = mln.inventory_item_id(+)
AND trunc(nvl(mln.expiration_date, sysdate+1)) > trunc(sysdate)
AND nvl(moq.planning_tp_type,2) = 2;
SELECT SUM(moq.primary_transaction_quantity)
INTO l_moq_qty
FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln
WHERE moq.organization_id = p_org_id
AND moq.inventory_item_id = p_item_id
AND EXISTS
(select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = moq.organization_id and
msi.secondary_inventory_name = moq.subinventory_code
AND msi.availability_type = decode(p_onhand_source,g_all_subs,msi.availability_type,1))
AND moq.organization_id = nvl(moq.planning_organization_id, moq.organization_id)
AND moq.lot_number = mln.lot_number(+)
AND moq.organization_id = mln.organization_id(+)
AND moq.inventory_item_id = mln.inventory_item_id(+)
AND trunc(nvl(mln.expiration_date, sysdate+1)) > trunc(sysdate)
AND nvl(moq.planning_tp_type,2) = 2;
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
INTO l_mmtt_qty_src
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id NOT IN (24,30)
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.subinventory_code
AND nvl(msi.inventory_atp_code,1) = 1)
AND mmtt.planning_organization_id IS NULL
AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = mln.lot_number(+)
AND p_org_id = mln.organization_id(+)
AND p_item_id = mln.inventory_item_id(+)
AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),sysdate+1))> trunc(sysdate))
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
INTO l_mmtt_qty_src
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id NOT IN (24,30)
AND EXISTS
(select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.subinventory_code
AND msi.availability_type = decode(p_onhand_source,g_all_subs,msi.availability_type,1))
AND mmtt.planning_organization_id IS NULL
AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = mln.lot_number(+)
AND p_org_id = mln.organization_id(+)
AND p_item_id = mln.inventory_item_id(+)
AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),sysdate+1))> trunc(sysdate))
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Abs(mmtt.primary_quantity))
INTO l_mmtt_qty_dest
FROM mtl_material_transactions_temp mmtt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3)
AND ((mmtt.transfer_subinventory IS NULL) OR
(mmtt.transfer_subinventory IS NOT NULL
AND EXISTS
(select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id)
AND msi.secondary_inventory_name = mmtt.transfer_subinventory
AND nvl(msi.inventory_atp_code,1) = 1)))
AND mmtt.planning_organization_id IS NULL
AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = mln.lot_number (+)
AND decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id) = mln.organization_id(+)
AND p_item_id = mln.inventory_item_id(+)
AND trunc(nvl(nvl(mtlt.lot_expiration_Date,mln.expiration_date),sysdate+1))> trunc(sysdate))
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Abs(mmtt.primary_quantity))
INTO l_mmtt_qty_dest
FROM mtl_material_transactions_temp mmtt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3)
AND ((mmtt.transfer_subinventory IS NULL) OR
(mmtt.transfer_subinventory IS NOT NULL
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id)
AND msi.secondary_inventory_name = mmtt.transfer_subinventory
AND msi.availability_type = decode(p_onhand_source,g_all_subs,msi.availability_type,1))))
AND mmtt.planning_organization_id IS NULL
AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = mln.lot_number (+)
AND decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id) = mln.organization_id(+)
AND p_item_id = mln.inventory_item_id(+)
AND trunc(nvl(nvl(mtlt.lot_expiration_Date,mln.expiration_date),sysdate+1))> trunc(sysdate))
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
INTO l_mmtt_qty_src
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id NOT IN (24,30)
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.subinventory_code
AND nvl(msi.inventory_atp_code,1) = 1)
AND mmtt.planning_organization_id IS NULL
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
INTO l_mmtt_qty_src
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id NOT IN (24,30)
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.subinventory_code
AND msi.availability_type = decode(p_onhand_source,g_all_subs,msi.availability_type,1))
AND mmtt.planning_organization_id IS NULL
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Abs(mmtt.primary_quantity))
INTO l_mmtt_qty_dest
FROM mtl_material_transactions_temp mmtt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3)
AND ((mmtt.transfer_subinventory IS NULL) OR
(mmtt.transfer_subinventory IS NOT NULL
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id)
AND msi.secondary_inventory_name = mmtt.transfer_subinventory
AND nvl(msi.inventory_atp_code,1) = 1)))
AND mmtt.planning_organization_id IS NULL
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Abs(mmtt.primary_quantity))
INTO l_mmtt_qty_dest
FROM mtl_material_transactions_temp mmtt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3)
AND ((mmtt.transfer_subinventory IS NULL) OR
(mmtt.transfer_subinventory IS NOT NULL
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id)
AND msi.secondary_inventory_name = mmtt.transfer_subinventory
AND msi.availability_type = decode(p_onhand_source,g_all_subs,msi.availability_type,1))))
AND mmtt.planning_organization_id IS NULL
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(moq.primary_transaction_quantity)
INTO l_moq_qty
FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln
WHERE moq.organization_id = p_org_id
AND moq.inventory_item_id = p_item_id
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = moq.organization_id and
msi.secondary_inventory_name = moq.subinventory_code
)
AND moq.organization_id = nvl(moq.planning_organization_id, moq.organization_id)
AND moq.lot_number = mln.lot_number(+)
AND moq.organization_id = mln.organization_id(+)
AND moq.inventory_item_id = mln.inventory_item_id(+)
AND trunc(nvl(mln.expiration_date, sysdate+1)) > trunc(sysdate)
AND nvl(moq.planning_tp_type,2) = 2
AND ((moq.status_id IS NOT NULL
AND EXISTS (SELECT 1 FROM mtl_material_statuses mms
WHERE status_id = moq.status_id
and mms.inventory_atp_code = 1
)
)
OR
moq.status_id IS NULL
);
SELECT SUM(moq.primary_transaction_quantity)
INTO l_moq_qty
FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln
WHERE moq.organization_id = p_org_id
AND moq.inventory_item_id = p_item_id
AND EXISTS
(select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = moq.organization_id and
msi.secondary_inventory_name = moq.subinventory_code)
AND moq.organization_id = nvl(moq.planning_organization_id, moq.organization_id)
AND moq.lot_number = mln.lot_number(+)
AND moq.organization_id = mln.organization_id(+)
AND moq.inventory_item_id = mln.inventory_item_id(+)
AND trunc(nvl(mln.expiration_date, sysdate+1)) > trunc(sysdate)
AND nvl(moq.planning_tp_type,2) = 2
AND ((moq.status_id IS NOT NULL
AND EXISTS (SELECT 1 FROM mtl_material_statuses mms
WHERE status_id = moq.status_id
and mms.availability_type = decode(p_onhand_source,g_all_subs,mms.availability_type,1)
)
)
OR
moq.status_id IS NULL
);
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
INTO l_mmtt_qty_src
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id NOT IN (24,30)
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.subinventory_code)
AND mmtt.planning_organization_id IS NULL
AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = mln.lot_number(+)
AND p_org_id = mln.organization_id(+)
AND p_item_id = mln.inventory_item_id(+)
AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),sysdate+1))> trunc(sysdate))
AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.subinventory_code,
mmtt.locator_id,
mtlt.lot_number,
mmtt.lpn_id, mmtt.transaction_action_id), mms.status_id)
AND mms.inventory_atp_code =1)
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
INTO l_mmtt_qty_src
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id NOT IN (24,30)
AND EXISTS
(select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.subinventory_code)
AND mmtt.planning_organization_id IS NULL
AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = mln.lot_number(+)
AND p_org_id = mln.organization_id(+)
AND p_item_id = mln.inventory_item_id(+)
AND trunc(nvl(nvl(mtlt.lot_expiration_date,mln.expiration_Date),sysdate+1))> trunc(sysdate))
AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.subinventory_code,
mmtt.locator_id,
mtlt.lot_number,
mmtt.lpn_id, mmtt.transaction_action_id), mms.status_id)
AND mms.availability_type = decode(p_onhand_source,g_all_subs,mms.availability_type,1))
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Abs(mmtt.primary_quantity))
INTO l_mmtt_qty_dest
FROM mtl_material_transactions_temp mmtt , mtl_transaction_lots_temp mtlt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3)
AND ((mmtt.transfer_subinventory IS NULL) OR
(mmtt.transfer_subinventory IS NOT NULL
AND EXISTS
(select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id)
AND msi.secondary_inventory_name = mmtt.transfer_subinventory )))
AND mmtt.planning_organization_id IS NULL
AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = mln.lot_number (+)
AND decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id) = mln.organization_id(+)
AND p_item_id = mln.inventory_item_id(+)
AND trunc(nvl(nvl(mtlt.lot_expiration_Date,mln.expiration_date),sysdate+1))> trunc(sysdate))
AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
WHERE mms.status_id= nvl(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,
mmtt.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,
mmtt.lpn_id, mmtt.transaction_action_id)), mms.status_id)
AND mms.inventory_atp_code =1)
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Abs(mmtt.primary_quantity))
INTO l_mmtt_qty_dest
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3)
AND ((mmtt.transfer_subinventory IS NULL) OR
(mmtt.transfer_subinventory IS NOT NULL
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id)
AND msi.secondary_inventory_name = mmtt.transfer_subinventory)))
AND mmtt.planning_organization_id IS NULL
AND EXISTS (select 'x' from mtl_transaction_lots_temp mtlt, mtl_lot_numbers mln
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.lot_number = mln.lot_number (+)
AND decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id) = mln.organization_id(+)
AND p_item_id = mln.inventory_item_id(+)
AND trunc(nvl(nvl(mtlt.lot_expiration_Date,mln.expiration_date),sysdate+1))> trunc(sysdate))
AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
WHERE mms.status_id= nvl(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,
mmtt.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,
mmtt.lpn_id, mmtt.transaction_action_id)), mms.status_id)
AND mms.availability_type = decode(p_onhand_source,g_all_subs,mms.availability_type,1))
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
INTO l_mmtt_qty_src
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id NOT IN (24,30)
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.subinventory_code)
AND mmtt.planning_organization_id IS NULL
AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.subinventory_code,
mmtt.locator_id,
null,
mmtt.lpn_id, mmtt.transaction_action_id), mms.status_id)
AND mms.inventory_atp_code =1)
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
INTO l_mmtt_qty_src
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id NOT IN (24,30)
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.subinventory_code)
AND mmtt.planning_organization_id IS NULL
AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
WHERE mms.status_id= nvl(INV_MATERIAL_STATUS_GRP.get_default_status(mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.subinventory_code,
mmtt.locator_id,
null,
mmtt.lpn_id, mmtt.transaction_action_id), mms.status_id)
AND mms.availability_type = decode(p_onhand_source,g_all_subs,mms.availability_type,1))
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Abs(mmtt.primary_quantity))
INTO l_mmtt_qty_dest
FROM mtl_material_transactions_temp mmtt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3)
AND ((mmtt.transfer_subinventory IS NULL) OR
(mmtt.transfer_subinventory IS NOT NULL
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id)
AND msi.secondary_inventory_name = mmtt.transfer_subinventory)))
AND mmtt.planning_organization_id IS NULL
AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
WHERE mms.status_id= nvl(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,
mmtt.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,
mmtt.lpn_id, mmtt.transaction_action_id)), mms.status_id)
AND mms.inventory_atp_code =1)
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Abs(mmtt.primary_quantity))
INTO l_mmtt_qty_dest
FROM mtl_material_transactions_temp mmtt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3)
AND ((mmtt.transfer_subinventory IS NULL) OR
(mmtt.transfer_subinventory IS NOT NULL
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id)
AND msi.secondary_inventory_name = mmtt.transfer_subinventory)))
AND mmtt.planning_organization_id IS NULL
AND EXISTS (SELECT 'x' FROM mtl_material_statuses mms
WHERE mms.status_id= nvl(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,
mmtt.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,
mmtt.lpn_id, mmtt.transaction_action_id)), mms.status_id)
AND mms.availability_type = decode(p_onhand_source,g_all_subs,mms.availability_type,1))
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(inv_decimals_pub.get_primary_quantity( p_org_id
,p_item_id
,mtrl.uom_code
,mtrl.quantity - NVL(mtrl.quantity_delivered,0))
)
INTO l_lpn_qty
FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh, mtl_transaction_types mtt
where mtrl.organization_id = p_org_id
AND mtrl.inventory_item_id = p_item_id
AND mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = 6 -- Putaway Move Order
AND mtrl.transaction_source_type_id = 5 -- Wip
AND mtt.transaction_action_id = 31 -- WIP Assembly Completion
AND mtt.transaction_type_id = mtrl.transaction_type_id
AND mtrl.line_status = 7 -- Pre Approved
AND mtrl.lpn_id is not null;
l_revision_select long;
l_lot_select long;
l_lot_select2 long;
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 || ' primary_quantity
, nvl(moq.orig_date_received,
moq.date_received) date_received
, 1 quantity_type
, moq.cost_group_id cost_group_id
, decode(moq.containerized_flag,
1, 1, 0) containerized
, moq.planning_organization_id planning_organization_id
, moq.owning_organization_id owning_organization_id
, moq.lpn_id lpn_id
FROM
mtl_onhand_quantities_detail moq
WHERE moq.organization_id <> Nvl(moq.planning_organization_id,moq.organization_id)
OR moq.organization_id <> nvl(moq.owning_organization_id, moq.organization_id) ';
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.containerized containerized
, x.planning_organization_id planning_organization_id
, x.owning_organization_id owning_organization_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.containerized containerized
, x.planning_organization_id planning_organization_id
, x.owning_organization_id owning_organization_id
FROM ('
|| l_onhand_stmt || '
) x
WHERE x.organization_id = :organization_id
AND x.inventory_item_id = :inventory_item_id
AND x.lpn_id = :lpn_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.containerized
, x.planning_organization_id, x.owning_organization_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 ;
INSERT INTO mtl_consigned_qty_temp (organization_id,
inventory_item_id,
revision,
lot_number,
lot_expiration_date,
subinventory_code,
reservable_type,
locator_id,
primary_quantity,
transactable_vmi,
date_received,
quantity_type,
cost_group_id,
containerized,
planning_organization_id,
owning_organization_id)
VALUES
(
ll_organization_id,
ll_inventory_item_id,
ll_revision,
ll_lot_number,
ll_lot_expiration_date,
ll_subinventory_code,
ll_reservable_type,
ll_locator_id,
ll_primary_quantity,
ll_transactable_vmi,
ll_date_received,
ll_quantity_type,
ll_cost_group_id,
ll_containerized,
ll_planning_organization_id,
ll_owning_organization_id);
inv_log_util.trace('#of records inserted into mtl_consigned_qty_temp :'||l_count,'CONSIGNED_VALIDATIONS',9);
SELECT Nvl(sum(primary_transaction_quantity),0) INTO x_att
FROM mtl_onhand_quantities_detail
WHERE owning_organization_id = organization_id
AND organization_id = p_organization_id
AND owning_organization_id <> p_owning_org_id
AND inventory_item_id = p_inventory_item_id
AND nvl(revision,'@@@') = nvl(p_revision, nvl(revision,'@@@'))
AND nvl(lot_number, '@@@') = nvl(p_lot_number, nvl(lot_number, '@@@'))
AND subinventory_code = nvl(p_subinventory_code, subinventory_code)
AND nvl(locator_id, -999) = nvl(p_locator_id, nvl(locator_id, -999))
AND nvl(lpn_id , -999) = nvl(p_lpn_id , -999)
AND nvl(cost_group_id, -999) = nvl(p_cost_group_id, nvl(cost_group_id, -999));
SELECT 1 INTO l_table_count FROM dual
WHERE EXISTS (SELECT 1 FROM mtl_consigned_qty_temp
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id);
SELECT Nvl(sum(primary_quantity),0) INTO l_vcoh
FROM mtl_consigned_qty_temp
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND Nvl(planning_organization_id, -999) = Nvl(p_planning_org_id,Nvl(planning_organization_id, -999))
AND Nvl(owning_organization_id, -999) = Nvl(p_owning_org_id,Nvl(owning_organization_id, -999))
AND containerized = 1
AND Nvl(revision,'@@@') = Nvl(p_revision,'@@@')
AND Nvl(lot_number,'@@@')=Nvl(p_lot_number,'@@@')
AND subinventory_code = p_subinventory_code
AND locator_id = p_locator_id ;