[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wsm_enabled_flag
INTO x_wsm_enabled
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT lot_control_code
, serial_number_control_code
INTO l_st_lot_control_code
, l_st_serial_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_st_item_id_tbl (1)
AND organization_id = p_st_org_id_tbl (1);
SELECT lot_control_code
, serial_number_control_code
INTO l_rs_lot_control_code
, l_rs_serial_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_rs_item_id_tbl (1)
AND organization_id = p_rs_org_id_tbl (1);
SELECT msik.lot_split_enabled
INTO l_validation_status
FROM mtl_system_items_b msik, mtl_lot_numbers mln
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_inventory_item_id
AND mln.lot_number = p_lot_number
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id;
SELECT msik.lot_split_enabled
INTO l_validation_status
FROM mtl_system_items_b msik
, mtl_transaction_lots_temp mtlt
, mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mtlt.lot_number = p_lot_number
AND mmtt.organization_id = msik.organization_id
AND mmtt.inventory_item_id = msik.inventory_item_id;
SELECT msik.lot_merge_enabled
INTO l_validation_status
FROM mtl_system_items_b msik, mtl_lot_numbers mln
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_inventory_item_id
AND mln.lot_number = p_lot_number
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id;
SELECT msik.lot_merge_enabled
INTO l_validation_status
FROM mtl_system_items_b msik
, mtl_transaction_lots_temp mln
, mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.transaction_temp_id = mln.transaction_temp_id
AND mln.lot_number = p_lot_number
AND mmtt.organization_id = msik.organization_id
AND mmtt.inventory_item_id = msik.inventory_item_id;
SELECT DECODE (msik.lot_control_code, 2, 'Y', 'N')
INTO l_validation_status
FROM mtl_system_items_b msik, mtl_lot_numbers mln
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_inventory_item_id
AND mln.lot_number = p_lot_number
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id;
SELECT DECODE (msik.lot_control_code, 2, 'Y', 'N')
INTO l_validation_status
FROM mtl_system_items_b msik
, mtl_transaction_lots_temp mln
, mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.transaction_temp_id = mln.transaction_temp_id
AND mln.lot_number = p_lot_number
AND mmtt.organization_id = msik.organization_id
AND mmtt.inventory_item_id = msik.inventory_item_id;
SELECT lot_number_uniqueness
INTO l_lot_uniqueness
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT COUNT (1)
INTO l_lot_count
FROM mtl_lot_numbers
WHERE inventory_item_id <> p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_rs_lot_num_tbl (i);
SELECT COUNT (1)
INTO l_lot_count
FROM mtl_transaction_lots_temp mtlt
, mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id <> p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mtlt.lot_number = p_rs_lot_num_tbl (i);
SELECT COUNT (1)
INTO l_lot_count
FROM mtl_lot_numbers
WHERE inventory_item_id <> p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_rs_lot_num_tbl (1);
SELECT COUNT (1)
INTO l_lot_count
FROM mtl_transaction_lots_temp mtlt
, mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id <> p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mtlt.lot_number = p_rs_lot_num_tbl (1);
SELECT COUNT (1)
INTO l_lot_count
FROM mtl_lot_numbers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_rs_lot_num_tbl (i);
SELECT COUNT (1)
INTO l_lot_count
FROM wip_entities
WHERE wip_entity_name = p_rs_lot_num_tbl (i)
AND organization_id = p_organization_id;
SELECT COUNT (1)
INTO l_lot_count
FROM mtl_transaction_lots_temp mtlt
, mtl_material_transactions_temp mmtt
WHERE mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mtlt.lot_number = p_rs_lot_num_tbl (i);
SELECT COUNT (1)
INTO l_lot_count
FROM wip_entities
WHERE organization_id = p_organization_id
AND wip_entity_name = p_rs_lot_num_tbl (1);
SELECT count(1)
INTO l_lot_count
FROM mtl_lot_numbers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_rs_lot_num_tbl(1);
SELECT count(1)
INTO l_lot_count
FROM WIP_ENTITIES
WHERE organization_id = p_organization_id
AND wip_entity_name = p_rs_lot_num_tbl(1);
SELECT COUNT(1)
INTO l_lot_count
FROM MTL_TRANSACTION_LOTS_TEMP MTLT, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
WHERE mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND Mmtt.transaction_temp_id = MTLT.transaction_temp_id
AND mtlt.lot_number = p_rs_lot_num_tbl(1);
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = p_st_lpn_id_tbl(i);
SELECT lpn_context
,subinventory_code
,locator_id
,organization_id
INTO l_lpn_context
,l_sub_code
,l_locator_id
,l_org_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_rs_lpn_id_tbl(i);
SELECT status_id
INTO l_status_id
FROM mtl_lot_numbers
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number;
SELECT status_id
INTO l_status_id
FROM mtl_serial_numbers
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND serial_number = p_serial_number;
/* SELECT column_name
, data_type
FROM all_tab_columns
WHERE table_name = p_table_name AND owner = p_owner
AND column_id > 22
ORDER BY column_id; */
SELECT col.column_name
, col.data_type
FROM user_synonyms syn , all_tab_columns col
WHERE syn.synonym_name = p_table_name AND col.owner = p_owner
and col.owner = syn.table_owner
and col.table_name = syn.table_name
and col.column_id > 22
ORDER BY column_id;
/* SELECT column_name
, data_type
FROM all_tab_columns
WHERE table_name = p_table_name AND owner = p_owner */
/*Bug:4724150. Commented the following condition 1 as the attribute
columns becomes out of range of 20 to 91 when some extraneous attributes are added*/
--AND column_id BETWEEN 20 AND 91
-- ORDER BY column_id;
SELECT col.column_name
, col.data_type
FROM user_synonyms syn , all_tab_columns col
WHERE syn.synonym_name = p_table_name AND col.owner = p_owner
and col.owner = syn.table_owner
and col.table_name = syn.table_name
ORDER BY column_id;
* get cost group by calling inv_cost_group_update.proc_get_costgroup;
* call INV_COST_GROUP_UPDATE.PROC_GET_COSTGROUP to get cost group
* end if;
print_debug ('calling inv_cost_group_update.proc_get_costgroup'
, 'Validate_Cost_Group'
);
inv_cost_group_update.proc_get_costgroup
(p_organization_id => l_organization_id
, p_inventory_item_id => l_inventory_item_id
, p_subinventory_code => l_subinventory_code
, p_locator_id => l_locator_id
, p_revision => l_revision
, p_lot_number => l_lot_number
, p_serial_number => NULL
, p_containerized_flag => l_containerized_flag
, p_lpn_id => l_lpn_id
, p_transaction_action_id => p_transaction_action_id
, x_cost_group_id => v_cost_group_id
, x_return_status => l_return_status
);
print_debug ('error from inv_cost_group_update.proc_get_costgroup'
, 'Validate_cost_group'
);
inv_cost_group_update.proc_get_costgroup
(p_organization_id => l_organization_id
, p_inventory_item_id => l_inventory_item_id
, p_subinventory_code => l_subinventory_code
, p_locator_id => l_locator_id
, p_revision => l_revision
, p_lot_number => l_lot_number
, p_serial_number => NULL
, p_containerized_flag => l_containerized_flag
, p_lpn_id => l_lpn_id
, p_transaction_action_id => p_transaction_action_id
, x_cost_group_id => l_current_cost_group_id
, x_return_status => l_return_status
);
SELECT primary_uom_code
, DECODE (revision_qty_control_code, 1, 'FALSE', 'TRUE')
INTO l_start_primary_uom
, l_revision_control
FROM mtl_system_items
WHERE organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id;
SELECT serial_number_control_code
INTO l_serial_code
FROM mtl_system_items
WHERE organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id;
shelf life code and depending on the type of lot transaction update
the interface table with the correct shelf life code and shelf life dates
*********************************************************************************************/
PROCEDURE compute_lot_expiration (
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_parent_id IN NUMBER
, p_transaction_type_id IN NUMBER
, p_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_st_lot_num IN VARCHAR2
, p_rs_lot_num_tbl IN lot_number_table
, p_rs_lot_exp_tbl IN OUT NOCOPY date_table
)
IS
l_shelf_life_code NUMBER;
l_update BOOLEAN;
SELECT shelf_life_code
, shelf_life_days
INTO l_shelf_life_code
, l_shelf_life_days
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT fnd_date.date_to_canonical (expiration_date)
INTO l_lotexpdate
FROM mtl_lot_numbers
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND lot_number = p_st_lot_num;
UPDATE mtl_transaction_lots_interface mtli
SET lot_expiration_date =
fnd_date.canonical_to_date (l_lotexpdate)
WHERE transaction_interface_id IN (
SELECT transaction_interface_id
FROM mtl_transactions_interface mti
WHERE mti.parent_id = p_parent_id
AND mti.transaction_interface_id =
mtli.transaction_interface_id);
fnd_message.set_name ('INV', 'INV_UPDATE_ERROR');
SELECT fnd_date.date_to_canonical (expiration_date)
INTO l_lotexpdate
FROM mtl_lot_numbers
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND lot_number = p_rs_lot_num_tbl (1);
SELECT fnd_date.date_to_canonical (expiration_date)
INTO l_lotexpdate
FROM mtl_lot_numbers
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND lot_number = p_st_lot_num; -- We only pass one
UPDATE mtl_transaction_lots_interface mtli
SET lot_expiration_date =
fnd_date.canonical_to_date (l_lotexpdate)
WHERE transaction_interface_id IN (
SELECT transaction_interface_id
FROM mtl_transactions_interface mti
WHERE mti.parent_id = p_parent_id
AND mti.parent_id = mti.transaction_interface_id
AND mti.transaction_interface_id =
mtli.transaction_interface_id);
fnd_message.set_name ('INV', 'INV_UPDATE_ERROR');
print_debug ('Lot exp date update merge2 ' || l_lotexpdate
, 'Compute Lot Exp'
);
SELECT fnd_date.date_to_canonical (expiration_date)
INTO l_lotexpdate
FROM mtl_lot_numbers
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND lot_number = p_st_lot_num;
UPDATE mtl_transaction_lots_interface mtli
SET lot_expiration_date =
fnd_date.canonical_to_date (l_lotexpdate)
WHERE transaction_interface_id IN (
SELECT transaction_interface_id
FROM mtl_transactions_interface mti
WHERE mti.parent_id = p_parent_id
AND mti.transaction_interface_id =
mtli.transaction_interface_id);
fnd_message.set_name ('INV', 'INV_UPDATE_ERROR');
print_debug ('Lot exp date update translate2 ' || l_lotexpdate
, 'Compute Lot Exp'
);
SELECT fnd_date.date_to_canonical (expiration_date)
INTO l_lotexpdate
FROM mtl_lot_numbers
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND lot_number = p_st_lot_num;
UPDATE mtl_transaction_lots_interface mtli
SET lot_expiration_date =
fnd_date.canonical_to_date (l_lotexpdate)
WHERE transaction_interface_id IN (
SELECT transaction_interface_id
FROM mtl_transactions_interface mti
WHERE mti.parent_id = p_parent_id
AND mti.transaction_interface_id =
mtli.transaction_interface_id
AND mtli.lot_expiration_date IS NULL);
fnd_message.set_name ('INV', 'INV_UPDATE_ERROR');
print_debug ( 'Lot exp date user defined : after update split3 '
|| l_lotexpdate
, 'Compute Lot Exp'
);
l_update := TRUE;
SELECT fnd_date.date_to_canonical (expiration_date)
INTO l_lotexpdate
FROM mtl_lot_numbers
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND lot_number = p_rs_lot_num_tbl (1);
SELECT fnd_date.date_to_canonical (expiration_date)
INTO l_lotexpdate
FROM mtl_lot_numbers
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND lot_number = p_st_lot_num;
l_update := FALSE;
IF l_update
THEN
IF (l_lotexpdate IS NULL)
THEN
fnd_message.set_name ('INV', 'INV_INVALID_LOT_EXP');
UPDATE mtl_transaction_lots_interface mtli
SET lot_expiration_date =
fnd_date.canonical_to_date (l_lotexpdate)
WHERE transaction_interface_id IN (
SELECT transaction_interface_id
FROM mtl_transactions_interface mti
WHERE mti.parent_id = p_parent_id
AND mti.parent_id = mti.transaction_interface_id
AND mti.transaction_interface_id =
mtli.transaction_interface_id);
fnd_message.set_name ('INV', 'INV_UPDATE_ERROR');
print_debug ( 'Lot exp date user defined : after update Merge6 '
|| l_lotexpdate
, 'Compute Lot Exp'
);
SELECT fnd_date.date_to_canonical (expiration_date)
INTO l_lotexpdate
FROM mtl_lot_numbers
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND lot_number = p_st_lot_num;
UPDATE mtl_transaction_lots_interface mtli
SET lot_expiration_date =
fnd_date.canonical_to_date (l_lotexpdate)
WHERE transaction_interface_id IN (
SELECT transaction_interface_id
FROM mtl_transactions_interface mti
WHERE mti.parent_id = p_parent_id
AND mti.transaction_interface_id =
mtli.transaction_interface_id
AND mtli.lot_expiration_date IS NULL);
fnd_message.set_name ('INV', 'INV_UPDATE_ERROR');
( 'Lot exp date user defined : after update Translate'
|| l_lotexpdate
, 'Compute Lot Exp'
);
PROCEDURE update_item_serial (
x_msg_count OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_validation_status OUT NOCOPY VARCHAR2
, p_org_id IN NUMBER
, p_item_id IN NUMBER
, p_to_item_id IN NUMBER DEFAULT NULL
, p_wip_entity_id IN NUMBER
, p_to_wip_entity_id IN NUMBER DEFAULT NULL
, p_to_operation_sequence IN NUMBER DEFAULT NULL
, p_intraoperation_step_type IN NUMBER DEFAULT NULL
)
IS
l_restrict_serial_rcpt NUMBER;
rollback_serial_update EXCEPTION;
l_update_attr BOOLEAN := FALSE;
, 'update_item_serial'
);
, 'update_item_serial'
);
, 'update_item_serial'
);
, 'update_item_serial'
);
, 'update_item_serial'
);
, 'update_item_serial'
);
, 'update_item_serial'
);
, 'update_item_serial'
);
SELECT serial_number
BULK COLLECT INTO l_ser_number_tbl
FROM mtl_serial_numbers msn
WHERE msn.inventory_item_id = p_item_id
AND msn.wip_entity_id = p_wip_entity_id
AND ( msn.intraoperation_step_type IS NULL
OR msn.intraoperation_step_type <> 5
)
AND ( (msn.current_status IN (1, 6))
OR ( l_restrict_serial_rcpt = 2
AND msn.current_status = 4
AND msn.last_txn_source_id = p_wip_entity_id
AND NVL (msn.last_txn_source_type_id, -9999) = 5
)
);
, 'update_item_serial'
);
print_debug ('returning..', 'update_item_serial');
SELECT descriptive_flex_context_code
INTO l_context_value_item
FROM mtl_flex_context
WHERE organization_id = p_org_id
AND context_column_name = 'ITEM'
AND descriptive_flexfield_name = 'Serial Attributes'
AND context_column_value_id = p_item_id;
SELECT descriptive_flex_context_code
INTO l_context_value_to_item
FROM mtl_flex_context
WHERE organization_id = p_org_id
AND context_column_name = 'ITEM'
AND descriptive_flexfield_name = 'Serial Attributes'
AND context_column_value_id = p_to_item_id;
, 'update_item_serial'
);
l_update_attr := TRUE;
, 'update_item_serial'
);
, 'update_item_serial'
);
, 'update_item_serial'
);
IF (l_update_attr) THEN
BEGIN
IF (l_debug)
THEN
print_debug ('Null out the attributes and update the MSN'
, 'update_item_serial'
);
UPDATE mtl_serial_numbers
SET inventory_item_id = NVL (p_to_item_id, inventory_item_id)
, wip_entity_id = NVL (p_to_wip_entity_id, wip_entity_id)
, operation_seq_num = p_to_operation_sequence
, intraoperation_step_type = p_intraoperation_step_type
, serial_attribute_category = NULL
, c_attribute1 = NULL
, c_attribute2 = NULL
, c_attribute3 = NULL
, c_attribute4 = NULL
, c_attribute5 = NULL
, c_attribute6 = NULL
, c_attribute7 = NULL
, c_attribute8 = NULL
, c_attribute9 = NULL
, c_attribute10 = NULL
, c_attribute11 = NULL
, c_attribute12 = NULL
, c_attribute13 = NULL
, c_attribute14 = NULL
, c_attribute15 = NULL
, c_attribute16 = NULL
, c_attribute17 = NULL
, c_attribute18 = NULL
, c_attribute19 = NULL
, c_attribute20 = NULL
, d_attribute1 = NULL
, d_attribute2 = NULL
, d_attribute3 = NULL
, d_attribute4 = NULL
, d_attribute5 = NULL
, d_attribute6 = NULL
, d_attribute7 = NULL
, d_attribute8 = NULL
, d_attribute9 = NULL
, d_attribute10 = NULL
, n_attribute1 = NULL
, n_attribute2 = NULL
, n_attribute3 = NULL
, n_attribute4 = NULL
, n_attribute5 = NULL
, n_attribute6 = NULL
, n_attribute7 = NULL
, n_attribute8 = NULL
, n_attribute9 = NULL
, n_attribute10 = NULL
, attribute_category = NULL
, attribute1 = NULL
, attribute2 = NULL
, attribute3 = NULL
, attribute4 = NULL
, attribute5 = NULL
, attribute6 = NULL
, attribute7 = NULL
, attribute8 = NULL
, attribute9 = NULL
, attribute10 = NULL
, attribute11 = NULL
, attribute12 = NULL
, attribute13 = NULL
, attribute14 = NULL
, attribute15 = NULL
, territory_code = NULL
, time_since_new = NULL
, cycles_since_new = NULL
, time_since_overhaul = NULL
, cycles_since_overhaul = NULL
, time_since_repair = NULL
, cycles_since_repair = NULL
, time_since_visit = NULL
, cycles_since_visit = NULL
, time_since_mark = NULL
, cycles_since_mark = NULL
, number_of_repairs = NULL
WHERE inventory_item_id = p_item_id
AND current_organization_id = p_org_id
AND wip_entity_id = p_wip_entity_id
AND serial_number = l_ser_number_tbl (i);
RAISE rollback_serial_update;
print_debug ('Update MSN when p_to_item_id <> p_item_id', 'update_item_serial');
UPDATE mtl_serial_numbers
SET inventory_item_id = NVL (p_to_item_id, inventory_item_id)
, wip_entity_id = NVL (p_to_wip_entity_id, wip_entity_id)
, operation_seq_num = p_to_operation_sequence
, intraoperation_step_type = p_intraoperation_step_type
WHERE inventory_item_id = p_item_id
AND current_organization_id = p_org_id
AND wip_entity_id = p_wip_entity_id
AND serial_number = l_ser_number_tbl (i);
RAISE rollback_serial_update;
END IF; --END IF (l_update_attr)
print_debug ('Update MSN when p_to_item_id = p_item_id', 'update_item_serial');
UPDATE mtl_serial_numbers
SET inventory_item_id = NVL (p_to_item_id, inventory_item_id)
, wip_entity_id = NVL (p_to_wip_entity_id, wip_entity_id)
, operation_seq_num = p_to_operation_sequence
, intraoperation_step_type = p_intraoperation_step_type
WHERE inventory_item_id = p_item_id
AND current_organization_id = p_org_id
AND wip_entity_id = p_wip_entity_id
AND serial_number = l_ser_number_tbl (i);
RAISE rollback_serial_update;
print_debug ('All updations done, Exitting the procedure', 'update_item_serial');
WHEN rollback_serial_update
THEN
ROLLBACK TO initial_state_svpt;
fnd_msg_pub.add_exc_msg (g_pkg_name, 'update_item_serial');