[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_id org_id,
mp.organization_code organization,
bh.batch_id,
bh.batch_no batch_no,
bh.formula_no formula_no,
bh.routing_no routing_no,
FND_DATE.DATE_TO_DISPLAYDT(bh.creation_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) creation_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.plan_start_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) planned_start_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.actual_start_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) actual_start_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.due_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) due_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.plan_cmplt_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) planned_completion_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.actual_cmplt_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) actual_completion_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.batch_close_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) batch_close_date,
msi.CONCATENATED_SEGMENTS item,
bl.inventory_item_id item_id,
bl.material_detail_id,
decode(bh.batch_status, 1, bl.plan_qty, -1, bl.plan_qty, bl.wip_plan_qty) planned_quantity,
bl.dtl_um item_uom,
bl.actual_qty actual_quantity,
(select batchstep_no || '-' || go.oprn_no
from gme_batch_steps gbs, gme_batch_step_items gbsi, gmd_operations go
where gbs.batch_id = bh.batch_id
and gbsi.batchstep_id = gbs.batchstep_id
and gbsi.material_detail_id = bl.material_detail_id
and go.oprn_id = gbs.oprn_id) batch_line
FROM gme_batch_header_vw bh, mtl_parameters mp, gme_material_details bl,
mtl_system_items_vl msi
where bh.batch_id = p_batch_id AND -- 121706
bl.organization_id = mp.organization_id AND
bh.batch_id = bl.batch_id AND
bl.line_type = 1 AND
bl.line_no =1 AND
--bl.inventory_item_id=p_inventory_item_id AND
bl.organization_id = msi.organization_id AND
bl.inventory_item_id = msi.inventory_item_id;
SELECT
mp.organization_code organization,
mmt.subinventory_code,
milk.concatenated_segments LOCATOR,
msi.concatenated_segments item,
bh.batch_no batch_no,
abs(mmt.transaction_quantity) quantity,
abs(mtln.transaction_quantity) lot_quantity,
mmt.transaction_uom,
abs(mmt.secondary_transaction_quantity) secondary_quantity,
abs(mtln.secondary_transaction_quantity) lot_quantity2,
mmt.secondary_uom_code uom2 ,
mtr.reason_name ,
mmt.organization_id org_id,
mmt.inventory_item_id item_id,
mmt.locator_id,
mmt.reason_id,
bh.batch_id,
bh.formula_no formula_no,
bh.routing_no routing_no,
FND_DATE.DATE_TO_DISPLAYDT(bh.creation_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) creation_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.plan_start_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) planned_start_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.actual_start_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) actual_start_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.due_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) due_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.plan_cmplt_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) planned_completion_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.actual_cmplt_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) actual_completion_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.batch_close_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) batch_close_date,
bl.material_detail_id,
bl.plan_qty planned_quantity,
bl.dtl_um item_uom,
bl.actual_qty actual_quantity ,
hzc.hazard_class hazard_class,
mtln.lot_number,
(select batchstep_no || '-' || go.oprn_no
from gme_batch_steps gbs, gme_batch_step_items gbsi, gmd_operations go
where gbs.batch_id = bh.batch_id
and gbsi.batchstep_id = gbs.batchstep_id
and gbsi.material_detail_id = bl.material_detail_id
and go.oprn_id = gbs.oprn_id) batch_line
FROM gme_batch_header_vw bh,
mtl_parameters mp,
gme_material_details bl,
mtl_system_items_vl msi,
mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln,
mtl_item_locations_kfv milk,
mtl_transaction_reasons mtr,
po_hazard_classes hzc
where mmt.transaction_id = p_transaction_id and -- 12409734 and --
bh.batch_id = mmt.TRANSACTION_SOURCE_ID AND -- 121706
bl.material_detail_id = mmt.TRX_SOURCE_LINE_ID and
mmt.organization_id = mp.organization_id AND
bh.batch_id = bl.batch_id AND
mmt.transaction_id = mtln.transaction_id(+) and
bl.organization_id = msi.organization_id AND
bl.inventory_item_id = msi.inventory_item_id and
mmt.organization_id = milk.organization_id(+) and
mmt.subinventory_code = milk.subinventory_code(+) AND
mmt.locator_id = milk.inventory_location_id(+) and
mmt.reason_id = mtr.reason_id (+) and
msi.hazard_class_id = hzc.hazard_class_id (+);
SELECT
mp.organization_code organization,
'N/A' subinventory_code,
'N/A' LOCATOR,
msi.concatenated_segments item,
bh.batch_no batch_no,
abs(gppl.quantity) quantity,
abs(gppl.quantity) lot_quantity,
bl.dtl_um transaction_uom,
abs(gppl.secondary_quantity) secondary_quantity,
abs(gppl.secondary_quantity) lot_quantity2,
msi.secondary_uom_code uom2 ,
mtr.reason_name ,
bl.organization_id org_id,
bl.inventory_item_id item_id,
NULL locator_id,
gppl.reason_id,
bh.batch_id,
bh.formula_no formula_no,
bh.routing_no routing_no,
FND_DATE.DATE_TO_DISPLAYDT(bh.creation_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) creation_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.plan_start_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) planned_start_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.actual_start_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) actual_start_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.due_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) due_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.plan_cmplt_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) planned_completion_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.actual_cmplt_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) actual_completion_date,
FND_DATE.DATE_TO_DISPLAYDT(bh.batch_close_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) batch_close_date,
bl.material_detail_id,
bl.plan_qty planned_quantity,
bl.dtl_um item_uom,
bl.actual_qty actual_quantity ,
hzc.hazard_class hazard_class,
gppl.lot_number,
(select batchstep_no || '-' || go.oprn_no
from gme_batch_steps gbs, gme_batch_step_items gbsi, gmd_operations go
where gbs.batch_id = bh.batch_id
and gbsi.batchstep_id = gbs.batchstep_id
and gbsi.material_detail_id = bl.material_detail_id
and go.oprn_id = gbs.oprn_id) batch_line
FROM gme_batch_header_vw bh,
mtl_parameters mp,
gme_material_details bl,
mtl_system_items_vl msi,
gme_pending_product_lots gppl,
po_hazard_classes hzc,
mtl_transaction_reasons mtr
where gppl.PENDING_PRODUCT_LOT_ID= p_transaction_id and -- 12409734 and --
bh.batch_id = gppl.batch_id AND -- 121706
bl.material_detail_id = gppl.material_detail_id and
bh.organization_id = mp.organization_id AND
bh.batch_id = bl.batch_id AND
bl.organization_id = msi.organization_id AND
bl.inventory_item_id = msi.inventory_item_id and
msi.hazard_class_id = hzc.hazard_class_id (+) and
gppl.reason_id = mtr.reason_id (+);
SELECT dispense_id
from gmo_material_dispenses
where batch_id = l_transaction_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
);
select batch_id,inventory_item_id
into l_transaction_id,l_inventory_item_id
from gmo_material_dispenses
where dispense_id = p_transaction_id;
SELECT mln.parent_lot_number, mln.grade_code,
FND_DATE.DATE_TO_DISPLAYDT(mln.creation_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) creation_date,
FND_DATE.DATE_TO_DISPLAYDT(mln.expiration_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) expiration_date,
mms.status_code
INTO l_parent_lot_number, l_grade_code, l_lot_creation_date,
l_lot_expiration_date, l_status
FROM mtl_lot_numbers mln, MTL_MATERIAL_STATUSES mms
WHERE mln.organization_id = l_batch_table_type(j).org_id AND
mln.inventory_item_id = l_batch_table_type(j).item_id AND
mln.lot_number = l_batch_table_type(j).lot_number and
mln.status_id = mms.status_id(+);
SELECT mln.parent_lot_number, mln.grade_code,
FND_DATE.DATE_TO_DISPLAYDT(mln.creation_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) creation_date,
FND_DATE.DATE_TO_DISPLAYDT(mln.expiration_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) expiration_date,
mms.status_code
INTO l_parent_lot_number, l_grade_code, l_lot_creation_date,
l_lot_expiration_date, l_status
FROM mtl_lot_numbers mln, MTL_MATERIAL_STATUSES mms
WHERE mln.organization_id = l_batch_table_type(j).org_id AND
mln.inventory_item_id = l_batch_table_type(j).item_id AND
mln.lot_number = l_batch_table_type(j).lot_number and
mln.status_id = mms.status_id(+);
, 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 [INVLA13B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
trace('Custom Labels Trace [INVLA13B.pls]: LABEL_FIELD_ID : ' || l_selected_fields(i).label_field_id);
trace('Custom Labels Trace [INVLA13B.pls]: FIELD_VARIABLE_NAME : ' || l_selected_fields(i).variable_name);
trace('Custom Labels Trace [INVLA13B.pls]: COLUMN_NAME : ' || l_selected_fields(i).column_name);
trace('Custom Labels Trace [INVLA13B.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_table_type(k).batch_no
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'batch_id' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).batch_id
|| 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_batch_table_type(k).item
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'transaction_quantity' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).quantity
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'transaction_uom' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).transaction_uom
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'secondary_transaction_quantity' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).secondary_quantity
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'secondary_uom' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).uom2
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_quantity' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).lot_quantity
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_quantity2' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).lot_quantity2
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_status' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).status
|| 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_batch_table_type(k).parent_lot_number
|| 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_batch_table_type(k).lot_number
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'organization' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).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_batch_table_type(k).subinventory_code
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'locator' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).locator
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'reason' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).reason_name
|| 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_batch_table_type(k).routing_no
|| 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_batch_table_type(k).formula_no
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'creation_date' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).creation_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'planned_start_date' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).planned_start_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'actual_start_date' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).actual_start_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'due_date' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).due_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'planned_completion_date' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).planned_completion_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'actual_completion_date' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).actual_completion_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'batch_close_date' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).batch_close_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'actual_quantity' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).actual_quantity
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'planned_quantity' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).planned_quantity
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'item_uom' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).item_uom
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_expiration_date' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).lot_expiration_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_creation_date' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).lot_creation_date
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'grade_code' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).grade_code
|| 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_batch_table_type(k).hazard_class
|| variable_e;
ELSIF LOWER(l_selected_fields(i).column_name) = 'batch_line' THEN
l_content_item_data := l_content_item_data
|| variable_b
|| l_selected_fields(i).variable_name
|| '">'
|| l_batch_table_type(k).batch_line
|| 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