[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_selected_fields inv_label.label_field_variable_tbl_type;
l_selected_fields_count NUMBER;
SELECT
para.ORGANIZATION_CODE,
msi.Concatenated_segments,
gsmp.sample_id,
para.organization_code,
gsmp.sample_no,
gsmp.sample_desc,
gsmp.inventory_item_id,
gsmp.sample_qty,
gsmp.sample_qty_uom,
gsmp.retain_as,
gsmp.priority,
(select meaning from gem_lookups where lookup_type='GMD_QC_SOURCE' and lookup_code=gsmp.source) source,
gsmp.subinventory,
gsmp.parent_lot_number,
gsmp.lot_number,
FND_DATE.DATE_TO_DISPLAYDT(mln.expiration_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) lot_expiry,
FND_DATE.DATE_TO_DISPLAYDT(mln.creation_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) lot_manufacturing_date,
FND_DATE.DATE_TO_DISPLAYDT(gsmp.expiration_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) sample_expirt,
para2.organization_code qc_lab_orgn_code,
gsmp.instance_id sample_instant,
batchheader.batch_no,
formulamst.formula_no,
operations.oprn_no,
recipes.recipe_no,
routings.routing_no ,
gsmp.organization_id,
msi.description item_description,
(select lkups.meaning from gem_lookups lkups, gmd_sample_spec_disp disp where disp.sample_id=gsmp.sample_id and lookup_type='GMD_QC_SAMPLE_DISP' and lookup_code=disp.disposition) sample_disposition,
FND_DATE.DATE_TO_DISPLAYDT(gsmp.date_drawn, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) date_drawn,
gsmp.sample_instance
from gmd_samples gsmp, mtl_lot_numbers mln,
fm_form_mst_b formulamst , gme_batch_header batchheader ,
gmd_operations operations , gmd_recipes_b recipes ,
gmd_routings_b routings , mtl_parameters para,
mtl_system_items_vl msi, mtl_parameters para2
where
gsmp.sample_id = p_transaction_id AND
gsmp.inventory_item_id = mln.inventory_item_id(+) and
gsmp.organization_id = mln.organization_id (+)and
gsmp.organization_id = para.organization_id AND
gsmp.lot_number = mln.lot_number(+) and
gsmp.inventory_item_id = msi.inventory_item_id AND
gsmp.organization_id = msi.organization_id AND
gsmp.lab_organization_id = para2.organization_id AND
gsmp.batch_id = batchheader.batch_id(+) and
gsmp.formula_id = formulamst.formula_id(+) and
gsmp.oprn_id = operations.oprn_id(+) and
gsmp.recipe_id = recipes.recipe_id(+) and
gsmp.routing_id = routings.routing_id(+);
x_variables => l_selected_fields
, x_variables_count => l_selected_fields_count
, p_format_id => p_label_type_info.default_format_id
);
IF (l_selected_fields_count = 0)
OR (l_selected_fields.COUNT = 0) THEN
IF (l_debug = 1) THEN
TRACE(
'no fields defined for this format: '
|| p_label_type_info.default_format_id
|| ','
|| p_label_type_info.default_format_name
);
|| l_selected_fields_count
);
, p_last_update_date => SYSDATE
, p_last_updated_by => fnd_global.user_id
, p_creation_date => SYSDATE
, p_created_by => fnd_global.user_id
, p_printer_name => l_printer
, x_return_status => l_return_status
, x_label_format_id => l_label_format_id
, x_label_format => l_label_format
, x_label_request_id => l_label_request_id
);
x_variables => l_selected_fields
, x_variables_count => l_selected_fields_count
, p_format_id => l_label_format_id
);
IF (l_selected_fields_count = 0)
OR (l_selected_fields.COUNT = 0) THEN
IF (l_debug = 1) THEN
TRACE(
'no fields defined for this format: '
|| l_label_format
|| ','
|| l_label_format_id
);
' Found selected_fields for format '
|| l_label_format
|| ', num='
|| l_selected_fields_count
);
FOR i IN 1 .. l_selected_fields.COUNT LOOP
IF (l_debug = 1) THEN
l_column_name_list := l_column_name_list || ',' || l_selected_fields(i).column_name;
IF (l_selected_fields(i).SQL_STMT IS NOT NULL AND l_selected_fields(i).column_name = 'sql_stmt') THEN
IF (l_debug = 1) THEN
trace('Custom Labels Trace [INVLA15B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
trace('Custom Labels Trace [INVLA15B.pls]: LABEL_FIELD_ID : ' || l_selected_fields(i).label_field_id);
trace('Custom Labels Trace [INVLA15B.pls]: FIELD_VARIABLE_NAME : ' || l_selected_fields(i).variable_name);
trace('Custom Labels Trace [INVLA15B.pls]: COLUMN_NAME : ' || l_selected_fields(i).column_name);
trace('Custom Labels Trace [INVLA15B.pls]: SQL_STMT : ' || l_selected_fields(i).sql_stmt);
l_sql_stmt := l_selected_fields(i).sql_stmt;
|| l_selected_fields(i).variable_name
|| '">'
|| l_sql_stmt_result
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'batch_no' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_no
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_no' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_sample_no
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_desc' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_sample_desc
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'orgn_code' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_organization
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_code' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_subinventory
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_item
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_number' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_lot_number
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'parent_lot_number' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_parent_lot_number
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_qty' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_sample_qty
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_uom' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_sample_uom
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'retain_as' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_retain_as
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'priority' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_priority
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'source' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_source
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_expiry' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_lot_expiry
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_manufacturing_date' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_lot_manufacturing_dat
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_expiry' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_sample_expirt
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'qc_lab_orgn' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_qc_lab_orgn_code
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_instant' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_sample_instant
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'formula_no' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_formula_no
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'oprn_no' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_oprn_no
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'recipe_no' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_recipe_no
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'routing_no' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_routing_no
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_id' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_sample_id
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_desc' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_item_description
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'disposition' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_sample_disposition
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'date_drawn' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_date_drawn
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_instance' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_sample_instance
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'print_count' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_print_count
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'reprint' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_reprint
|| variable_e;
END LOOP; -- l_selected_fields.COUNT