[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
mp.ORGANIZATION_CODE,
d.dispense_id,
d.dispense_source,
d.dispense_number,
d.dispense_type,
bh.batch_no,
bs.batchstep_no,
gop.oprn_no,
msi.concatenated_segments dispense_item,
d.organization_id,
d.subinventory_code,
d.security_code,
d.dispense_uom,
FND_DATE.DATE_TO_DISPLAYDT(d.dispensed_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE,calendar_aware=>FND_DATE.calendar_aware_alt) dispensed_date,
d.dispensing_mode,
d.required_qty,
d.dispensed_qty,
d.gross_required_weight,
d.source_container_tare,
d.source_container_weight,
d.source_container_reweight,
d.actual_gross_weight,
d.target_container_tare,
d.source_container_item_id,
d.target_container_item_id,
d.lot_number,
hzc.hazard_class,
d.inventory_item_id
from
gmo_material_dispenses d, gme_batch_header bh,
gme_batch_steps bs, mtl_parameters mp, gmd_operations gop,
mtl_system_items_vl msi, po_hazard_classes hzc
where d.dispense_id = p_transaction_id AND
d.batch_id = bh.batch_id AND
d.organization_id = mp.organization_id AND
d.batch_step_id = bs.batchstep_id(+) AND
d.inventory_item_id = msi.inventory_item_id AND
d.organization_id = msi.organization_id AND
bs.oprn_id = gop.oprn_id(+) AND
msi.hazard_class_id = hzc.hazard_class_id (+);
SELECT
mp.ORGANIZATION_CODE,
d.dispense_id,
d.dispense_source,
und.undispense_number,
und.undispense_type,
bh.batch_no,
bs.batchstep_no,
gop.oprn_no,
msi.concatenated_segments dispense_item,
und.organization_id,
und.subinventory_code,
und.security_code,
und.dispense_uom,
FND_DATE.DATE_TO_DISPLAYDT(und.undispensed_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE,calendar_aware=>FND_DATE.calendar_aware_alt) undispensed_date,
und.undispensing_mode,
d.required_qty,
und.dispensed_qty,
und.gross_required_weight,
und.source_container_tare,
und.source_container_weight,
und.source_container_reweight,
und.actual_gross_weight,
und.target_container_tare,
und.source_container_item_id,
und.target_container_item_id,
und.lot_number,
hzc.hazard_class,
und.undispense_id,
(nvl(und.undispensed_qty,0) + nvl(und.material_loss,0)) undispensed_qty,
und.inventory_item_id
from
gmo_material_dispenses d, gme_batch_header bh,
gme_batch_steps bs, mtl_parameters mp, gmd_operations gop,
mtl_system_items_vl msi, po_hazard_classes hzc , gmo_material_undispenses und
where und.undispense_id = p_transaction_id AND
und.dispense_id = d.dispense_id AND
und.batch_id = bh.batch_id AND
und.organization_id = mp.organization_id AND
und.batch_step_id = bs.batchstep_id(+) AND
und.inventory_item_id = msi.inventory_item_id AND
und.organization_id = msi.organization_id AND
bs.oprn_id = gop.oprn_id(+) AND
msi.hazard_class_id = hzc.hazard_class_id (+);
cursor c_get_mode_meaning is select meaning from fnd_lookups where lookup_type='GMO_DISPENSE_MODE' and lookup_code=l_mode;
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
);
Select concatenated_segments
INTO l_source_container_item
from mtl_system_items_vl
where organization_id = l_organization_id AND
inventory_item_id = l_source_container_item_id;
Select concatenated_segments
INTO l_target_container_item
from mtl_system_items_vl
where organization_id = l_organization_id AND
inventory_item_id = l_target_container_item_id;
SELECT parent_lot_number
into l_parent_lot_number
FROM mtl_lot_numbers
WHERE organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id
AND lot_number = l_lot_number;
, 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 [INVLA11B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
trace('Custom Labels Trace [INVLA11B.pls]: LABEL_FIELD_ID : ' || l_selected_fields(i).label_field_id);
trace('Custom Labels Trace [INVLA11B.pls]: FIELD_VARIABLE_NAME : ' || l_selected_fields(i).variable_name);
trace('Custom Labels Trace [INVLA11B.pls]: COLUMN_NAME : ' || l_selected_fields(i).column_name);
trace('Custom Labels Trace [INVLA11B.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) = 'oprn_no' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_line
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'dispensing_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) = 'organization_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) = 'dispense_source' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_dispense_source
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'dispense_number' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_dispense_number
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'dispense_type' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_dispense_type
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'dispense_id' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_dispense_id
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'dispense_uom' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_dispense_uom
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'dispensed_date' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_dispensed_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'dispensing_mode' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_dispensing_mode
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'required_qty' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_required_Qty
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'dispensed_qty' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_dispensed_qty
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'gross_required_weight' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_gross_required_weight
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'source_container_tare' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_source_container_tare
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'source_container_weight' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_source_container_weight
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'source_container_reweight' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_source_container_reweight
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'actual_gross_weight' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_actual_gross_weight
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'target_container_tare' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_target_Container_tare
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'source_container_item' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_source_container_item
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'target_container_item' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_target_container_item
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'hazard_class' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_hazard_class
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'dispensed_container' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_dispensed_container
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'dispense_container' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_dispensed_container
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'material_container' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_material_container
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'material_quantity' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_material_qty
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'undispense_number' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_undispense_number
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'undispense_type' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_undispense_type
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'undispensed_date' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_undispensed_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'undispensed_qty' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_undispensed_qty
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'undispensing_mode' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_undispensing_mode
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'undispense_uom' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_undispense_uom
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'undispense_id' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_undispense_id
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'undispense_source' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_undispense_source
|| 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) = '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; ELSIF LOWER(l_selected_fields(i).column_name) = 'security_code' THEN
|| l_selected_fields(i).variable_name
|| '">'
||l_security_code
|| variable_e;
END LOOP; -- l_selected_fields.COUNT