[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lot_attribute_category
INTO context_value
FROM mtl_lot_numbers
WHERE lot_number = p_lot_serial_number
AND inventory_item_id = item_id
AND organization_id = org_id;
SELECT serial_attribute_category
INTO context_value
FROM mtl_serial_numbers
WHERE serial_number = p_lot_serial_number
AND inventory_item_id = item_id
AND current_organization_id = org_id;
SELECT descriptive_flex_context_code
INTO context_value
FROM mtl_flex_context
WHERE organization_id = -1
AND context_column_name = l_context_column_name
AND descriptive_flexfield_name = flex_name
AND context_column_value_id = item_id;
SELECT descriptive_flex_context_code
INTO context_value
FROM mtl_flex_context
WHERE organization_id = org_id
AND context_column_name = l_context_column_name
AND descriptive_flexfield_name = flex_name
AND context_column_value_id = item_id;
SELECT descriptive_flex_context_code
INTO context_value
FROM mtl_flex_context mfc,
mtl_item_categories mic
WHERE mfc.organization_id = -1
AND mic.organization_id = org_id
AND mfc.category_set_id = mic.category_set_id
AND mfc.context_column_value_id = mic.category_id
AND mfc.descriptive_flexfield_name = flex_name
AND mic.inventory_item_id = item_id
AND mfc.context_column_name = l_context_column_name;
SELECT descriptive_flex_context_code
INTO context_value
FROM mtl_flex_context mfc,
mtl_item_categories mic
WHERE mfc.organization_id = org_id
AND mfc.organization_id = mic.organization_id
AND mfc.category_set_id = mic.category_set_id
AND mfc.context_column_value_id = mic.category_id
AND mfc.descriptive_flexfield_name = flex_name
AND mic.inventory_item_id = item_id
AND mfc.context_column_name = l_context_column_name;
SELECT serial_attribute_category
INTO l_context_value
FROM mtl_serial_numbers
WHERE serial_number = p_lot_serial
AND inventory_item_id = item_id
AND current_organization_id = org_id;
SELECT lot_attribute_category
INTO l_context_value
FROM mtl_lot_numbers
WHERE lot_number = p_lot_serial
AND inventory_item_id = item_id
AND organization_id = org_id;
/** Bug 2600351 -- selecting from all_Tab_columns causes performance issue.
It takes about 171 second from 173 thousand rows and the only rows we need is
only 10 rows **/
/** Instead of selecting from all_tab_columns, we will just use PL/SQL processing to
find out the column type, anyway, it is only 3 return types **/
l_retVarchar VARCHAR2(15) := 'VARCHAR2';
SELECT df.context_required_flag
INTO v_dflex_context_flag
FROM fnd_application_vl a, fnd_descriptive_flexs_vl df
WHERE a.application_short_name = p_application_short_name
AND df.application_id = a.application_id
AND df.descriptive_flexfield_name = p_flex_name
AND a.application_id = df.table_application_id;
SELECT df.context_user_override_flag
INTO l_context_override_flag
FROM fnd_application_vl a, fnd_descriptive_flexs_vl df
WHERE a.application_short_name = p_application_short_name
AND df.application_id = a.application_id
AND df.descriptive_flexfield_name = p_flex_name
AND a.application_id = df.table_application_id;
SELECT lot_number
INTO l_lot_number
FROM mtl_lot_numbers
WHERE lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id;
l_sel_stmt VARCHAR2(32067):= 'SELECT ' ;
/* select column_name, data_type, data_length
from all_tab_columns
where table_name = p_table_name
and owner = p_owner
order by column_id; */
select col.column_name, col.data_type, col.data_length
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 col.column_id;
/*bug #2474713 insert the context column.. This was added because the descriptive flexfield window
expects the context_value also*/
IF(NOT(l_contexts_info.is_global(i))) THEN
l_rec_index := l_rec_index + 1;
SELECT SYSDATE
INTO l_date
FROM dual;
l_sel_stmt := 'SELECT ' ;
SELECT application_Table_name , value_column_name , id_column_name, additional_where_clause
INTO l_appl_table_name , l_value_column_name , l_id_column_name, l_additional_where_clause --14828695 add l_additional_where_clause
FROM fnd_flex_validation_Tables
WHERE flex_value_set_id = l_value_set_id(x);
SELECT number_precision INTO l_precision
FROM FND_FLEX_VALUE_SETS
WHERE flex_value_set_id = l_segments.value_set(l_index);
SELECT round(x_lot_serial_attributes(x).column_value, l_precision)
INTO x_lot_serial_attributes(x).column_value
FROM DUAL;
EXECUTE IMMEDIATE 'SELECT distinct ' ||l_value_Column_name || ' FROM ' ||l_appl_table_name || ' WHERE '
|| l_id_column_name||' = ' || x_lot_serial_attributes(x).COLUMN_VALUE INTO l_final_value_date ;
EXECUTE IMMEDIATE 'SELECT distinct ' ||l_value_Column_name || ' FROM ' ||l_appl_table_name || ' WHERE '
|| l_id_column_name||' = ' || x_lot_serial_attributes(x).COLUMN_VALUE INTO l_final_value_char ;
EXECUTE IMMEDIATE 'SELECT distinct ' ||l_value_Column_name || ' FROM ' ||l_appl_table_name || ' WHERE '
|| l_id_column_name||' = ''' || x_lot_serial_attributes(x).COLUMN_VALUE || ''''
|| ' and ' || l_additional_where_clause
INTO l_final_value_char ;
SELECT descriptive_flex_context_code, descriptive_flex_context_name,
global_flag, enabled_flag
FROM fnd_descr_flex_contexts_vl
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_flex_name;
SELECT end_user_column_name, application_column_name, enabled_flag,
required_flag, default_type, default_value
FROM fnd_descr_flex_col_usage_vl
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_flex_name
AND descriptive_flex_context_code = p_flex_context_code;
SELECT attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
FROM mtl_lot_numbers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number;
SELECT attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
FROM mtl_SERIAL_numbers
WHERE inventory_item_id = p_inventory_item_id
AND SERIAL_number = p_serial_number;
SELECT nvl(msn.attribute1, msnt.attribute1) attribute1
, nvl(msn.attribute2, msnt.attribute2) attribute2
, nvl(msn.attribute3, msnt.attribute3) attribute3
, nvl(msn.attribute4, msnt.attribute4) attribute4
, nvl(msn.attribute5, msnt.attribute5) attribute5
, nvl(msn.attribute6, msnt.attribute6) attribute6
, nvl(msn.attribute7, msnt.attribute7) attribute7
, nvl(msn.attribute8, msnt.attribute8) attribute8
, nvl(msn.attribute9, msnt.attribute9) attribute9
, nvl(msn.attribute10, msnt.attribute10) attribute10
, nvl(msn.attribute11, msnt.attribute11) attribute11
, nvl(msn.attribute12, msnt.attribute12) attribute12
, nvl(msn.attribute13, msnt.attribute13) attribute13
, nvl(msn.attribute14, msnt.attribute14) attribute14
, nvl(msn.attribute15, msnt.attribute15) attribute15
FROM mtl_serial_numbers msn, mtl_serial_numbers_temp msnt
WHERE msn.inventory_item_id = p_inventory_item_id
AND msn.serial_number = p_serial_number
AND msnt.transaction_temp_id = p_transaction_temp_id
AND msnt.fm_serial_number = p_serial_number
AND msn.serial_number = msnt.fm_serial_number;
SELECT msnt.attribute1 attribute1
,msnt.attribute2 attribute2
,msnt.attribute3 attribute3
,msnt.attribute4 attribute4
,msnt.attribute5 attribute5
,msnt.attribute6 attribute6
,msnt.attribute7 attribute7
,msnt.attribute8 attribute8
,msnt.attribute9 attribute9
,msnt.attribute10 attribute10
,msnt.attribute11 attribute11
,msnt.attribute12 attribute12
,msnt.attribute13 attribute13
,msnt.attribute14 attribute14
,msnt.attribute15 attribute15
FROM mtl_serial_numbers msn, mtl_serial_numbers_temp msnt
WHERE msn.inventory_item_id = p_inventory_item_id
AND msn.serial_number = p_serial_number
AND msnt.transaction_temp_id = p_transaction_temp_id
AND msnt.fm_serial_number = p_serial_number
AND msn.serial_number = msnt.fm_serial_number;
SELECT 1
, attribute_category
INTO l_lot_exist
, l_attribute_category
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 1
, lot_attribute_category
INTO l_lot_exist
, l_attribute_category
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 df.context_required_flag
INTO l_context_req_flag
FROM fnd_application_vl a, fnd_descriptive_flexs_vl df
WHERE a.application_short_name = 'INV'
AND df.application_id = a.application_id
AND df.descriptive_flexfield_name = p_flex_name
AND a.application_id = df.table_application_id;
l_where_clause := 'SELECT 1 FROM mtl_lot_numbers ' ||
' WHERE organization_id = :org_id ' ||
' AND inventory_item_id = :item_id ' ||
' AND lot_number = :lot ' || l_where_clause;
SELECT 'N' BULK COLLECT
INTO loc_tab
FROM mtl_system_items_b
WHERE auto_lot_alpha_prefix = p_lot_prefix
AND lot_control_code = 2
FOR UPDATE NOWAIT;
SELECT 'N'
INTO l_already_locked
FROM mtl_system_items_b
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id
FOR UPDATE NOWAIT;