[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT action_code, Description
FROM mtl_actions
WHERE NVL(disable_flag,'N') = 'N'
AND action_code like (p_code);
SELECT action_code, Description
FROM mtl_actions
WHERE NVL(disable_flag,'N') = 'N';
SELECT 'YES' FROM DUAL
UNION
SELECT 'NO' FROM DUAL;
SELECT 'YES' FROM DUAL WHERE 'YES' LIKE upper(p_option)
UNION
SELECT 'NO' FROM DUAL WHERE 'NO' LIKE upper(p_option);
SELECT grade_code
, description
FROM mtl_grades
WHERE grade_code LIKE (p_grade_code)
AND disable_flag <> 'Y';
SELECT grade_code
, description
FROM mtl_grades
WHERE disable_flag <> 'Y';
* These attributes are selected from dual and passed back as a ref cursor to the client.
**/
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
SELECT
---- Added for Bug #3952081 + #4093379
nvl(mln.parent_lot_number,'') parent_lot_number
, nvl(mln.grade_code,'') grade_code
, nvl(mln.origination_type,'') origination_type
, nvl(TO_CHAR(mln.origination_date,'YYYY-MM-DD'),'') origination_date --YYYY-MM-DD
, nvl(TO_CHAR(mln.expiration_action_date,'YYYY-MM-DD'),'') expiration_action_date
, nvl(mln.expiration_action_code,'') expiration_action_code
, nvl(TO_CHAR(mln.retest_date,'YYYY-MM-DD'),'') retest_date
, nvl(TO_CHAR(mln.hold_date,'YYYY-MM-DD'),'') hold_date
, nvl(TO_CHAR(mln.maturity_date,'YYYY-MM-DD'),'') maturity_date
, nvl(mln.supplier_lot_number,'') supplier_lot_number
FROM mtl_lot_numbers mln
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number;
SELECT
---- Added for Bug #3952081 + #4093379
nvl(mln.parent_lot_number,'') parent_lot_number
, nvl(mln.grade_code,'') grade_code
, nvl(mln.origination_type,'') origination_type
, nvl(TO_CHAR(mln.origination_date,'YYYY-MM-DD'),'') origination_date --YYYY-MM-DD
, nvl(TO_CHAR(mln.expiration_action_date,'YYYY-MM-DD'),'') expiration_action_date
, nvl(mln.expiration_action_code,'') expiration_action_code
, nvl(TO_CHAR(mln.retest_date,'YYYY-MM-DD'),'') retest_date
, nvl(TO_CHAR(mln.hold_date,'YYYY-MM-DD'),'') hold_date
, nvl(TO_CHAR(mln.maturity_date,'YYYY-MM-DD'),'') maturity_date
, nvl(mln.supplier_lot_number,'') supplier_lot_number
FROM mtl_lot_numbers mln
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number
AND nvl(parent_lot_number,' ') = nvl(p_parent_lot_number, ' ');
SELECT tracking_quantity_ind
, secondary_default_ind
, secondary_uom_code
, dual_uom_deviation_high
, dual_uom_deviation_low
, grade_control_flag
, default_grade
, child_lot_flag
, retest_interval
, expiration_action_interval
, expiration_action_code
, maturity_days
, hold_days
, copy_lot_attribute_flag
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT 1
INTO l_dummy
FROM MTL_RESERVATIONS
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number;
SELECT copy_lot_attribute_flag,
lot_number_generation
FROM mtl_parameters
WHERE organization_id = cp_organization_id;
SELECT copy_lot_attribute_flag INTO l_copy_lot_attribute_flag
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT copy_lot_attribute_flag INTO l_copy_lot_attribute_flag
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT
GRADE_CODE , DESCRIPTION
FROM MTL_GRADES;
* These attributes are selected from dual and passed back as a ref cursor to the client.
**/
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
SELECT
nvl(mln.grade_code,'') grade_code
, nvl(mln.origination_type,'') origination_type
, nvl(TO_CHAR(mln.origination_date,'YYYY-MM-DD'),'') origination_date --YYYY-MM-DD
, nvl(mln.expiration_action_code,'') expiration_action_code
, nvl(TO_CHAR(mln.expiration_action_date,'YYYY-MM-DD'),'') expiration_action_date
, nvl(TO_CHAR(mln.retest_date,'YYYY-MM-DD'),'') retest_date
, nvl(TO_CHAR(mln.hold_date,'YYYY-MM-DD'),'') hold_date
, nvl(TO_CHAR(mln.maturity_date,'YYYY-MM-DD'),'') maturity_date
, nvl(mln.supplier_lot_number,'') supplier_lot_number
-- nsinghi bug#5209065 rework. Fetch exp date also, to default it.
, nvl(TO_CHAR(mln.expiration_date,'YYYY-MM-DD'),'') expiration_date
FROM mtl_lot_numbers mln
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number;
SELECT grade_control_flag
, default_grade
, shelf_life_code
, shelf_life_days
, expiration_action_code
, expiration_action_interval
, retest_interval
, maturity_days
, hold_days
FROM mtl_system_items_b
WHERE organization_id = cp_organization_id
AND inventory_item_id = cp_inventory_item_id;
SELECT grade_code
, expiration_date
, expiration_action_code
, expiration_action_date
, origination_date
, retest_date
, maturity_date
, hold_date
FROM mtl_lot_numbers
WHERE organization_id = cp_organization_id
AND inventory_item_id = cp_inventory_item_id
AND lot_number = cp_lot_number;
SELECT
x_grade_code,
x_origination_date,
x_exp_action_date,
x_exp_action_code,
x_hold_date,
x_maturity_date,
x_retest_date,
x_expiration_date
FROM dual ;
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, mms.status_code status_code
, mms.status_id
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_split_enabled = 'Y'
AND mln.lot_number = p_lot_number
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
UNION
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, NULL status_code
, msik.default_lot_status_id -- Bug#2267947
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_split_enabled = 'Y'
AND mln.lot_number = p_lot_number
UNION
SELECT nvl(mln.parent_lot_number,mln.lot_number) lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, mms.status_code status_code
, mms.status_id
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_split_enabled = 'Y'
AND mln.lot_number = p_lot_number
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
UNION
SELECT nvl(mln.parent_lot_number,mln.lot_number) lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, NULL status_code
, msik.default_lot_status_id -- Bug#2267947
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_split_enabled = 'Y'
AND mln.lot_number = p_lot_number
ORDER BY lot_number, concatenated_segments;
SELECT DISTINCT moq.lot_number
, moq.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, mms.status_code
, mms.status_id
FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms, mtl_item_locations mil
WHERE moq.organization_id = p_organization_id
AND moq.lot_number IS NOT NULL
AND moq.organization_id = mil.organization_id
AND moq.organization_id = mln.organization_id
AND moq.organization_id = msik.organization_id
AND mil.segment19 = p_project_id
AND (mil.segment20 = p_task_id
OR (mil.segment20 IS NULL
AND p_task_id IS NULL
)
)
AND mln.lot_number = moq.lot_number
AND mms.status_id = msik.default_lot_status_id -- Bug#2267947
AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_merge_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id,
msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
UNION ALL
SELECT DISTINCT moq.lot_number
, moq.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, NULL status_code
, msik.default_lot_status_id -- Bug#2267947
FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms, mtl_item_locations mil
WHERE moq.organization_id = p_organization_id
AND moq.lot_number IS NOT NULL
AND moq.organization_id = mil.organization_id
AND moq.organization_id = mln.organization_id
AND moq.organization_id = msik.organization_id
AND mil.segment19 = p_project_id
AND (mil.segment20 = p_task_id
OR (mil.segment20 IS NULL
AND p_task_id IS NULL
)
)
AND mln.lot_number = moq.lot_number
AND msik.default_lot_status_id IS NULL -- Bug#2267947
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_merge_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id,
msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
ORDER BY 1, concatenated_segments;
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, mms.status_code
, mms.status_id
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_merge_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
UNION ALL
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, NULL status_code
, msik.default_lot_status_id -- Bug#2267947
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_merge_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id,
p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
ORDER BY lot_number, concatenated_segments;
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, mms.status_code
, mms.status_id
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND msik.lot_control_code = 2
AND mln.inventory_item_id LIKE l_inventory_item_id
AND mln.lot_number LIKE (p_lot_number)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id,
p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
UNION ALL
SELECT mln.lot_number LN
, mln.inventory_item_id
, msik.concatenated_segments cs
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, NULL status_code
, msik.default_lot_status_id -- Bug#2267947
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND msik.lot_control_code = 2
AND mln.inventory_item_id LIKE l_inventory_item_id
AND mln.lot_number LIKE (p_lot_number)
ORDER BY lot_number, concatenated_segments;
SELECT create_lot_uom_conversion
INTO l_create_lot_uom_conv
FROM mtl_parameters
WHERE organization_id = l_org_id;
SELECT unit_of_measure_tl, uom_class
INTO l_from_unit_of_measure, l_from_uom_class
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CODE = l_from_uom_Code;
SELECT unit_of_measure_tl, uom_class
INTO l_to_unit_of_measure, l_to_uom_class
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CODE = l_to_uom_Code;
l_qty_update_tbl MTL_LOT_UOM_CONV_PUB.quantity_update_rec_type; -- for uom conv
SELECT primary_uom_code
, secondary_uom_code
, secondary_default_ind
, copy_lot_attribute_flag
, tracking_quantity_ind
FROM mtl_system_items
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id ;
SELECT copy_lot_attribute_flag,
lot_number_generation
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT 1
FROM mtl_lot_numbers
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number ;
/* Step 1 ...preparing to insert lot in MLN by calling CREATE_INV_LOT
* This will also take care of copying Parent's UOM Conv record for child lot
*/
l_primary_uom := NULL; --p_primary_uom ;
SELECT count('1')
INTO l_exists
FROM mtl_lot_numbers
WHERE inventory_item_id = P_inventory_item_id
AND organization_id = p_org_id
AND lot_number = p_parent_lot_number
AND ROWNUM = 1;
SELECT create_lot_uom_conversion
INTO l_create_lot_uom_conv
FROM mtl_parameters
WHERE organization_id = l_org_id;
SELECT unit_of_measure_tl, uom_class
INTO l_from_unit_of_measure, l_from_uom_class
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CODE = l_from_uom_Code;
SELECT unit_of_measure_tl, uom_class
INTO l_to_unit_of_measure, l_to_uom_class
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CODE = l_to_uom_Code;
l_lot_uom_conv_rec.last_updated_by := FND_GLOBAL.user_id;
l_lot_uom_conv_rec.last_update_date := SYSDATE;
l_lot_uom_conv_rec.last_update_login := FND_GLOBAL.login_id;
l_lot_uom_conv_rec.program_update_date := NULL;
, p_update_type_indicator => 5
, p_reason_id => P_REASON_ID
, p_batch_id => 0
, p_process_data => 'Y'
, p_lot_uom_conv_rec => l_lot_uom_conv_rec
, p_qty_update_tbl => l_qty_update_tbl
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_sequence => l_sequence
);